Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 228
  • Last Modified:

managing an Oracle db at home - hiding system schemas; setting up a superuser

I am just starting to use my home environment, Oracle 11g on Windows 7. Everything seems to be ok, I've got Sql Developer working, PL/SQL Developer, Sql Plus, etc.

In PL/SQL Developer, I've got all these schemas that I have no idea what they are for. Are they all SYS type schemas ? At one point I tried to create a repository for Oracle Developer / Designer, that might explain some of this.
All schemas on PL/SQL Developer
In Sql Developer, it's a little cleaner, as all of the unfamiliar users are listed under "System - Other Users", but this includes the schemas I created (Books, HR, Order Entry, Scott, etc.)
System users via Sql Developer
So I'd like to do a couple of things:
- set up a superuser, who has access to all schemas, so I can just log on and work in any functional schema
- hide all the System schemas in PL/Sql Developer

Also, I've never done any DBA work, so any newbie tips for managing my home Oracle d.b. ? Should I install O.E.M. ?
0
Gadsden Consulting
Asked:
Gadsden Consulting
  • 30
  • 21
5 Solutions
 
slightwv (䄆 Netminder) Commented:
Some are sample schemas.  Some are related to different options you have installed.

I cannot find the list in the 11g docs but was able to find it in the 10g docs.  most haven't changed:
http://docs.oracle.com/cd/B16351_01/doc/server.102/b14196/users_secure001.htm

Also found this:
http://www.orafaq.com/wiki/List_of_default_database_users

>>- set up a superuser, who has access to all schemas, so I can just log on and work in any functional schema

To set up a DBA account:
create user bob identified by bob;
grant create ssession, DBA to bob;

Now bob can access everything.

Never used this much but it is an alter session command to 'switch' between them:
http://docs.oracle.com/cd/E11882_01/server.112/e25494/general.htm#ADMIN02101

Personally I think it is pretty dangerous.  Best to just connect directly  to the schema you want to work in.

>>Also, I've never done any DBA work, so any newbie tips for managing my home Oracle d.b. ? Should I install O.E.M. ?

Most installs come with dbConsole (up to 11g) that is a stripped down version of OEM.

12c comes with DB Express built in.   It is the dbConsole replacement.

Most 'DBA's these days cannot survive without a GUI.  I used quotes because I don't consider them DBA's if they cannot use sqlplus command line to do most things.
0
 
Gadsden ConsultingIT SpecialistAuthor Commented:
slightwv, great ! thanks

>>I cannot find the list in the 11g docs but was able to find it in the 10g docs
-- ok, most of them I see in your links

>>To set up a DBA account:
-- done, great !

>>Never used this much but it is an alter session command to 'switch' between them:
-- interesting, I'll keep this in the back of my mind

>>Most installs come with dbConsole (up to 11g) that is a stripped down version of OEM.
-- I opened "Database Control", this opened via Chrome, but it can't connect
OEM 11g
>>Most 'DBA's these days cannot survive without a GUI.
-- I'm afraid I would be one of them . . . why not let the computer do the work ??? (We had a similar discussion a while back on using PL/Sql Developer - - - before I went incognito . . .
0
 
slightwv (䄆 Netminder) Commented:
>>I opened "Database Control", this opened via Chrome, but it can't connect

Sure the database is up and running?

>>why not let the computer do the work ???

Would you call yourself a mechanic if all you ever do is turn the key and put gas in the car?  What happens when you turn the key and the car doesn't start?  If you call AAA then you aren't a mechanic...

A real DBA needs to know the architecture and what actions need to be performed.  Other than, well when this thingy here is all blinky, I click here and it stops.

I have no problems with the GUI once you KNOW what it is doing under the sheets.

There will come a day when you find yourself without a GUI and command line is your only option.  Then what do you tell your boss as he stands over your shoulder when the database is down?

Besides, most commands can be executed MUCH faster with sqlplus.

Task:
You need to increase open_cursors from 500 to 1000.

I bet you all the beer I can drink (and its a LOT) that I can have it done, open my beer and have a couple of drinks before you can using a GUI.
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
Gadsden ConsultingIT SpecialistAuthor Commented:
>> >>I opened "Database Control", this opened via Chrome, but it can't connect
>> Sure the database is up and running?
-- for sure, that's how I got the screen shots above . . .

>>I have no problems with the GUI once you KNOW what it is doing under the sheets.
-- ok, I see the logic with that . . .

>>I bet you all the beer I can drink (and its a LOT) that I can have it done, open my beer and have a couple of drinks before you can using a GUI.
-- work hard and play hard . . .
0
 
slightwv (䄆 Netminder) Commented:
>>-- for sure, that's how I got the screen shots above . . .

I just saw the 'Startup' button on the dbConsole screen shot.

Do you have more than 1 instance on the box?

Check your Services.
Look for OracleService<SERVICE>
and OracleDBConsole<SERVICE>

Make sure they match up.
0
 
Gadsden ConsultingIT SpecialistAuthor Commented:
>>Do you have more than 1 instance on the box?
- just 1 instance (best as I can tell)
Oracle services
I re-routed to 192.168.1.6 for OEM, but can't connect. I'm sure the passwords are correct.
OEM connect error
0
 
slightwv (䄆 Netminder) Commented:
OracleDBConsoleorcl isn't started.

Try starting it and see what happens.

If all else fails, manually recreate it from a CMD prompt:
emca -deconfig dbcontrol db -repos drop
emca -config dbcontrol db -repos create

P.S.
No 'as sysdba' on the username field.  the 'Connect As' takes care of that.
0
 
Gadsden ConsultingIT SpecialistAuthor Commented:
-- can't start OracleDBConsole-ordl -
OEM cannot start
>>manually recreate it from a CMD prompt:
- it's working on the drop now.
0
 
Gadsden ConsultingIT SpecialistAuthor Commented:
the drop and create seemed to go ok, but still the console won't start, either from services or the OEM page.

attached are the log files. Sometimes there are issues writing to the E:\ drive . . .
emca-2014-10-16-20-07-36.log
emca-repos-config-2014-10-16-20-21-06.lo
emca-repos-create-2014-10-16-20-10-24.lo
emca-2014-10-16-20-01-59.log
emca-repos-drop-2014-10-16-20-03-04.log
0
 
slightwv (䄆 Netminder) Commented:
By chance, has the machine changed IP addresses?

Oracle HATES that.  dbConsole hates it even more.

You should get in the habit of using a fixed IP Address when messing around with Oracle.

I've never had to do this but check out the following link:
https://community.oracle.com/thread/311223?tstart=0

the solution is simple. In the directory ORACLE_HOME\oc4j\j2ee there is a directory OC4J_DBConsole_localhost_orcl or OC4J_DBConsole_HOSTNAME_orcl.
make a copy (or set a link) from the directory and rename it to the specified name. So you have two directory
OC4J_DBConsole_localhost_orcl
OC4J_DBConsole_HOSTNAME_orcl
Then it will works fine
0
 
slightwv (䄆 Netminder) Commented:
>>the drop and create seemed to go ok, but still the console won't start, either from services or the OEM page.

We cross posted.  Give me a few to go through the files but try the solution from my last post.
0
 
slightwv (䄆 Netminder) Commented:
What is in:

SEVERE: Error updating E:\app\Stephen\product\11.2.0\dbhome_1\sysman\config\emoms.properties
Refer to the log file at E:\app\Stephen\cfgtoollogs\emca\orcl\emca_2014_10_16_20_07_36.log for more details.
0
 
Gadsden ConsultingIT SpecialistAuthor Commented:
>>By chance, has the machine changed IP addresses?
- yes. I was beside myself the other day, but dusted off some old posts (with your explanation) and changed the IP address in tnsnames and listener, and I was back in business . . . (except for OEM).

>>You should get in the habit of using a fixed IP Address when messing around with Oracle.
-- ok, I'll have to look that up . . .

>>OC4J_DBConsole_HOSTNAME_orcl
-- so should it be exactly that or OC4J_DBConsole_192.168.1.6_orcl ?
0
 
Gadsden ConsultingIT SpecialistAuthor Commented:
>>What is in:
SEVERE: Error updating E:\app\Stephen\product\11.2.0\dbhome_1\sysman\config\emoms.properties
Refer to the log file at E:\app\Stephen\cfgtoollogs\emca\orcl\emca_2014_10_16_20_07_36.log for more details.
-- see attached
emoms.properties.txt
emoms.properties.emca.txt
0
 
slightwv (䄆 Netminder) Commented:
>>- so should it be exactly that or OC4J_DBConsole_192.168.1.6_orcl ?

I would try OC4J_DBConsole_localhost_orcl.

You may need to install the Microsoft loopback adapter and make it the first NIC on the machine:
https://social.technet.microsoft.com/Forums/windows/en-US/259c7ef2-3770-4212-8fca-c58936979851/how-to-install-microsoft-loopback-adapter

Get in the habit of using IP address over hostname in just about every step.

Oracle has always been pretty dumb when it comes to machines with multiple NIC's.  By default it uses the hostname and when it needs to connect, it asks "Hey hostname, what is your IP address" and typically it grabs the first NIC it comes to which may not be the correct one.  This can give it the incorrect IP.
0
 
slightwv (䄆 Netminder) Commented:
I was really looking for the log file: emca_2014_10_16_20_07_36.log
0
 
Gadsden ConsultingIT SpecialistAuthor Commented:
>>I would try OC4J_DBConsole_localhost_orcl.
-- I have that file but it's saying to create "OC4J_DBConsole_HOSTNAME_orcl"

>>Get in the habit of using IP address over hostname in just about every step.
-- I've done that on tnsnames and listener, that's all I know about.

>>You may need to install the Microsoft loopback adapter and make it the first NIC on the machine:
>>Oracle has always been pretty dumb when it comes to machines with multiple NIC's.
-- so you're saying I have multiple NIC's ?
-- for the Loopback adapter - I'm not sure what that is . . . and not sure about the multiple NIC's . . .
0
 
Gadsden ConsultingIT SpecialistAuthor Commented:
>>I was really looking for the log file: emca_2014_10_16_20_07_36.log
-- that's already posted . . . log file 1  . . . the comment is self-referring . . .
0
 
Gadsden ConsultingIT SpecialistAuthor Commented:
I have to call it a night . . . thanks a lot !
0
 
slightwv (䄆 Netminder) Commented:
>>-- so you're saying I have multiple NIC's ?

Pointing out how 'dumb' Oracle is when it comes to networking.  They love to mix hostname and IP address in different places.

>>-- that's already posted . . . log file 1  . . . the comment is self-referring . . .

Searched for the '_' in the name.  Missed the one with dashes.  Not sure why the same log file would tell you to refer to itself.  Seems odd to me.

>>-- for the Loopback adapter - I'm not sure what that is

You may not need it.  I've had to use it on some installs but don't have it installed on my Home PC.  Then again, I'm also not using dbConsole on it.  Maybe that was the reason I used it in other places.

It's a Windows thing.  Basically it creates a dummy NIC that is used for all things local but still allows for normal IP address activity.

The reason it has to be first NIC in the list is what I mentioned earlier:  Oracle grabs the first NIC if can find when it needs to do network stuff and uses that NICs info.

Maybe this link will help explain it more:
http://technet.microsoft.com/en-us/library/cc708341%28v=ws.10%29.aspx
0
 
slightwv (䄆 Netminder) Commented:
Forgot this:
>>-- I have that file but it's saying to create "OC4J_DBConsole_HOSTNAME_orcl"

If you have the file with the word 'localhost', see if you have localhost in your hosts file:
C:\Windows\System32\drivers\etc\hosts

Uncomment the entry:
 127.0.0.1       localhost
0
 
Gadsden ConsultingIT SpecialistAuthor Commented:
ok, I'll check the recent posts again tonight at home. Thx.
0
 
Gadsden ConsultingIT SpecialistAuthor Commented:
>>Uncomment the entry: 127.0.0.1       localhost
-- did that, but still OEM won't start, either from services or here

should I make the \etc\hosts entry 192.168.1.6 ?

I'll hitting the sack now, will be out of the house all day Sat, so will probably just pick this up Monday night.
0
 
slightwv (䄆 Netminder) Commented:
try:
https://localhost:1158/em/

I also see you are still adding 'as sysdba' to the username field. Don't.
0
 
Gadsden ConsultingIT SpecialistAuthor Commented:
>>I also see you are still adding 'as sysdba' to the username field. Don't.
-- sorry that was the original image. I removed "as sysdba".

>>try https://localhost:1158/em/
-- added this in /etc/hosts, still no luck from Services or Chrome.

(signing off now).
0
 
slightwv (䄆 Netminder) Commented:
>>>>try https://localhost:1158/em/
>>-- added this in /etc/hosts

You added what to /etc/hosts?

>>(signing off now).

You said that the last time....   ;)
0
 
Gadsden ConsultingIT SpecialistAuthor Commented:
>> >> >>try https://localhost:1158/em/
>> >>-- added this in /etc/hosts
>> You added what to /etc/hosts?
Oh, oops, mis-read . . . I tried  https://localhost:1158/em/ back in the beginning with same results. Then switched to https://192.168.1.6:1158/em/console/, still no luck . . .

re-booted with same results :-(

off to Grandma's . . .
0
 
slightwv (䄆 Netminder) Commented:
When you tried  https://localhost:1158/em/ had you done the following:
Added  127.0.0.1       localhost  to the hosts file.
Installed the loopback adapter.

If you don't want to go through all that:
Configure the machine with a static IP address then manually drop and recreate dbconsole.
0
 
Gadsden ConsultingIT SpecialistAuthor Commented:
ahhh, grrrr

I set up a static ip address (using ipv4) (192.168.1.55), but then I couldn't RDC into it - - - which I had been doing previously . . . both PC's are on the same homegroup and I can access PC # 2 from PC # 1 via the network, so I can read files on PC # 2.

I thought this would be easier than the loopback adapter (and more understandable).

I sort through the the static IP address first before trying the loopback, see new post if you want.
0
 
Gadsden ConsultingIT SpecialistAuthor Commented:
I was able to connect by entering the new IP address, will work on connecting by PC name later.

dropping dbconsole now . . .
0
 
Gadsden ConsultingIT SpecialistAuthor Commented:
re-created dbconsole but still no luck. See the errors .
dbconsole create error
I think the errors are caused by not having privileges to write to E:\ drive. oracle is there and seems to be fine but for many items I can't write there - such as tnsnames, I have to create elsewhere then copy back in. This is a long standing issue that I've been able to deal with, but maybe I need to get back to that.

I hate to waste your time on this, I think we've exhausted this, and I need to get the permissions worked out and then try again.
emca-repos-drop-2014-10-19-11-17-49.log
emca-2014-10-19-11-17-19.log
emca-repos-create-2014-10-19-11-27-50.lo
0
 
Gadsden ConsultingIT SpecialistAuthor Commented:
0
 
Gadsden ConsultingIT SpecialistAuthor Commented:
set permission on E:\ drive to full control for all, same result . . . :-(

for emoms.properties where there is a severe error, I still can't write to it - not sure what to do .
0
 
Gadsden ConsultingIT SpecialistAuthor Commented:
set permissions on e-moms.properties, same result . . . :-(

will have to pick this up later tonight . . .
0
 
slightwv (䄆 Netminder) Commented:
I know you won't believe this but Oracle isn't all that complicated.  It just isn't "run setup.exe and done".

>>I thought this would be easier than the loopback adapter (and more understandable).

Probably but the loopback adapter just creates a virtual NIC and assigns it the loopback IP address 127.0.0.1.

I've not had to use the loopback adapter for a long time and going from memory it was only when I had multiple active NICs in the box.

For the permission issues:
When running the commands did you make sure to "Run as administrator"?

>>both PC's are on the same homegroup

Let;s stick to troubleshooting one thing at a time.  As it relates to this question, I don't really care about another PC.
0
 
Gadsden ConsultingIT SpecialistAuthor Commented:
goo idea about running as Administrator, I'll try that tonight, thx.
0
 
Gadsden ConsultingIT SpecialistAuthor Commented:
(almost) total success !
I ran the drop/create as administrator, and that was successful, and the service started ! woo-hoo.
dbConsole created successfully
Now I have a different error when trying to connect via Chrome:
Oracle console error
0
 
Gadsden ConsultingIT SpecialistAuthor Commented:
same error when connecting to https://localhost:1158/em/ . . . I see this here so I'm looking at that.

I un-commented 127.0.0.1 localhost in \etc\hosts . . .
0
 
Gadsden ConsultingIT SpecialistAuthor Commented:
I ran "emctl status dbconsole" and got "Environment variable ORACLE_UNQNAME not defined. Please set ORACLE_UNQNAME to database unique name".

Same with "emctl upload dbconsole"
0
 
slightwv (䄆 Netminder) Commented:
>>Please set ORACLE_UNQNAME to database unique name".

From a cmd prompt:
set ORACLE_UNQNAME=ORCL
emctl status

As far as the Java error goes, I've never seen that one.

Have to go with Google:
http://docs.oracle.com/javase/7/docs/api/javax/net/ssl/SSLPeerUnverifiedException.html

Not sure how to 'fix' that one.
0
 
Gadsden ConsultingIT SpecialistAuthor Commented:
I set the environment variable but still get "Environment variable ORACLE_UNQNAME not defined"
Oracle UNQNAME
0
 
slightwv (䄆 Netminder) Commented:
Not a 'system' environment variable.

I believe I stated: From a cmd prompt issue:
set ORACLE_UNQNAME=ORCL
0
 
slightwv (䄆 Netminder) Commented:
My local DB is also the default ORCL.

Here is my output:
C:\>emctl status
Environment variable ORACLE_UNQNAME not defined. Please set ORACLE_UNQNAME to da
tabase unique name.

C:\>set ORACLE_UNQNAME=ORCL

C:\>emctl status
Oracle Enterprise Manager 11g Database Control Release 11.2.0.3.0
Copyright (c) 1996, 2011 Oracle Corporation.  All rights reserved.
   Oracle Enterprise Manager 10g Database Control commands:

Open in new window



Also the status command you want is:
emctl status dbconsole
0
 
Gadsden ConsultingIT SpecialistAuthor Commented:
uh - it's running :-)

I set the ORACLE_UNQNAME (thx), then ran "emctl status dbconsole", saw the URL, and (almost voila) - thar she blows !!!
Oracle Enterprise Mgr orcl
I really don't know what to do with it, but it's running :-)
0
 
Gadsden ConsultingIT SpecialistAuthor Commented:
>>Not a 'system' environment variable.
- cross-posted, I got that from Oracle Forums and tried it out (and it didn't work)

Here's where I got the URL:
emctl status orcl
0
 
slightwv (䄆 Netminder) Commented:
>>thar she blows !!!

WOO HOO!!!  Now don't EVER change IP addresses!!!!!!

>>I really don't know what to do with it.

Nor do I.  It's why I don't use it...

The advisers are supposed to be really good.  But on a 'dev' system it is next to impossible to set up workloads in such a way to play with them.

I would focus on familiarization with what is where and spend a LOT of time in the docs reading up on what each item is supposed to show you.

Then you can feel more comfortable clicking on things in production without fear of an 'accident'.
0
 
Gadsden ConsultingIT SpecialistAuthor Commented:
>>Now don't EVER change IP addresses!!!!!!
- for sure . . .

>>I would focus on familiarization with what is where and spend a LOT of time in the docs reading up on what each item is supposed to show you.
-- I was thinking I might need to be a DBA for things like space issues, performance, etc. I'm sure I can survive with one instance for a good while.

But this is great, I've got an Oracle environment at home, this will really help (me stay employable) . . .

Thanks a lot !
0
 
Gadsden ConsultingIT SpecialistAuthor Commented:
excellent help through some thick woods !
0
 
slightwv (䄆 Netminder) Commented:
No problem.

We'll eventually convert you to an Oracle person!!!

It is, after all, the only 'real' database...  ;)
0
 
Gadsden ConsultingIT SpecialistAuthor Commented:
>>We'll eventually convert you to an Oracle person!!!
>>It is, after all, the only 'real' database...  ;)
-- hey, I'm with you on both accounts . . . but I did notice all your Microsoft credentials, Sql Server, Access, etc. do you work in those areas as well ?
0
 
slightwv (䄆 Netminder) Commented:
>>do you work in those areas as well ?

I do a little Access when I absolutely have to.  No SQL Server.

You collect points in other areas when things are cross posted in different Zones.
0

Featured Post

[Webinar On Demand] Database Backup and Recovery

Does your company store data on premises, off site, in the cloud, or a combination of these? If you answered “yes”, you need a data backup recovery plan that fits each and every platform. Watch now as as Percona teaches us how to build agile data backup recovery plan.

  • 30
  • 21
Tackle projects and never again get stuck behind a technical roadblock.
Join Now