Solved

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

Posted on 2014-10-16
51
208 Views
Last Modified: 2014-10-21
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
Comment
Question by:Gadsden Consulting
  • 30
  • 21
51 Comments
 
LVL 76

Accepted Solution

by:
slightwv (䄆 Netminder) earned 500 total points
ID: 40385608
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
 

Author Comment

by:Gadsden Consulting
ID: 40385651
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
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 40385669
>>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
 

Author Comment

by:Gadsden Consulting
ID: 40385701
>> >>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
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 40385718
>>-- 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
 

Author Comment

by:Gadsden Consulting
ID: 40385732
>>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
 
LVL 76

Assisted Solution

by:slightwv (䄆 Netminder)
slightwv (䄆 Netminder) earned 500 total points
ID: 40385734
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
 

Author Comment

by:Gadsden Consulting
ID: 40385749
-- can't start OracleDBConsole-ordl -
OEM cannot start
>>manually recreate it from a CMD prompt:
- it's working on the drop now.
0
 

Author Comment

by:Gadsden Consulting
ID: 40385766
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
 
LVL 76

Assisted Solution

by:slightwv (䄆 Netminder)
slightwv (䄆 Netminder) earned 500 total points
ID: 40385771
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
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 40385774
>>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
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 40385776
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
 

Author Comment

by:Gadsden Consulting
ID: 40385782
>>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
 

Author Comment

by:Gadsden Consulting
ID: 40385792
>>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
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 40385793
>>- 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
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 40385794
I was really looking for the log file: emca_2014_10_16_20_07_36.log
0
 

Author Comment

by:Gadsden Consulting
ID: 40385802
>>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
 

Author Comment

by:Gadsden Consulting
ID: 40385805
>>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
 

Author Comment

by:Gadsden Consulting
ID: 40385827
I have to call it a night . . . thanks a lot !
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 40385834
>>-- 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
 
LVL 76

Assisted Solution

by:slightwv (䄆 Netminder)
slightwv (䄆 Netminder) earned 500 total points
ID: 40385838
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
 

Author Comment

by:Gadsden Consulting
ID: 40386376
ok, I'll check the recent posts again tonight at home. Thx.
0
 

Author Comment

by:Gadsden Consulting
ID: 40387924
>>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
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 40387925
try:
https://localhost:1158/em/

I also see you are still adding 'as sysdba' to the username field. Don't.
0
 

Author Comment

by:Gadsden Consulting
ID: 40387929
>>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
Free Gift Card with Acronis Backup Purchase!

Backup any data in any location: local and remote systems, physical and virtual servers, private and public clouds, Macs and PCs, tablets and mobile devices, & more! For limited time only, buy any Acronis backup products and get a FREE Amazon/Best Buy gift card worth up to $200!

 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 40387930
>>>>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
 

Author Comment

by:Gadsden Consulting
ID: 40388643
>> >> >>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
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 40389420
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
 

Author Comment

by:Gadsden Consulting
ID: 40390051
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
 

Author Comment

by:Gadsden Consulting
ID: 40390100
I was able to connect by entering the new IP address, will work on connecting by PC name later.

dropping dbconsole now . . .
0
 

Author Comment

by:Gadsden Consulting
ID: 40390174
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
 

Author Comment

by:Gadsden Consulting
ID: 40390185
0
 

Author Comment

by:Gadsden Consulting
ID: 40390390
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
 

Author Comment

by:Gadsden Consulting
ID: 40390601
set permissions on e-moms.properties, same result . . . :-(

will have to pick this up later tonight . . .
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 40390982
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
 

Author Comment

by:Gadsden Consulting
ID: 40391758
goo idea about running as Administrator, I'll try that tonight, thx.
0
 

Author Comment

by:Gadsden Consulting
ID: 40393301
(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
 

Author Comment

by:Gadsden Consulting
ID: 40393336
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
 

Author Comment

by:Gadsden Consulting
ID: 40393342
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
 
LVL 76

Assisted Solution

by:slightwv (䄆 Netminder)
slightwv (䄆 Netminder) earned 500 total points
ID: 40393355
>>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
 

Author Comment

by:Gadsden Consulting
ID: 40393388
I set the environment variable but still get "Environment variable ORACLE_UNQNAME not defined"
Oracle UNQNAME
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 40393392
Not a 'system' environment variable.

I believe I stated: From a cmd prompt issue:
set ORACLE_UNQNAME=ORCL
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 40393396
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
 

Author Comment

by:Gadsden Consulting
ID: 40393398
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
 

Author Comment

by:Gadsden Consulting
ID: 40393403
>>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
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 40393406
>>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
 

Author Comment

by:Gadsden Consulting
ID: 40393416
>>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
 

Author Closing Comment

by:Gadsden Consulting
ID: 40393423
excellent help through some thick woods !
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 40393439
No problem.

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

It is, after all, the only 'real' database...  ;)
0
 

Author Comment

by:Gadsden Consulting
ID: 40394365
>>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
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 40394372
>>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

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

Introduction A previously published article on Experts Exchange ("Joins in Oracle", http://www.experts-exchange.com/Database/Oracle/A_8249-Joins-in-Oracle.html) makes a statement about "Oracle proprietary" joins and mixes the join syntax with gen…
When you start your Windows 10 PC and got an "Operating system not found" error or just saw  "Auto repair for startup". After a while, you have entered a loop for Auto repair which does not fix anything and you will be in a  panic as all your work w…
This Micro Tutorial will teach you the basics of configuring your computer to improve its speed. It will also teach you how to disable programs that are running in the background simultaneously. This will be demonstrated using Windows 7 operating…
This Micro Tutorial will teach you how to change your appearance and customize your Windows 7 interface to your unique preference. This will be demonstrated using Windows 7 operating system.

706 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now