How to manage more than one database?
I have created two databases on my Windows XP Pro PC.
Each has it's own SPFILE (and PFILE created from the SPFILES).
I can startup and mount the last created database, but I don't know how to startup/mount the first.
I shutdown the second, and try starting up the first, specifying the relevant PFILE
eg. STARTUP PFILE=h:\....\initdb1.ora
I've even tried to start it up with the relevant SPFILE, by creating a dummy PFILE called temp.ora, containing the single line:
and using the command:
However, whichever method I use, I get error "ORA-1102 cannot mount database in EXCLUSIVE mode".
Is it possoble for me to open different databases on my PC? I 'thought' I would only have to specify the relevant parameter file to direct Oracle to the appropriate database. It seems there is more to it than that!
Do exactly as you have been doing, but each time to connect to an idle instance using sys or whatever, make sure you specify:
for each database you want to open .
Thats if you have the TNS set up. If you're logging in direct to the box itself, you need to make sure your ORACLE_SID environment variable is set.
Thanks. What quick responses!
Based on waitecj's first reply, I realised my problem.
I was connected with sys as sysdba to the second instance.
To open up the first database, I had to re-connect with
connect sys/pwd1@db1 as sysdba
I could then open up the first instance/database.
I could even switch back to the second with:
connect sys/pwd2@db2 as sysdba
and open up the second instance/database simultaneously!
It's obvious that I'm quite new to this, but I've learnt a great lesson!
I still don't know the implication of setting the ORACLE_SID environment variable as bazza suggests, since I didn't do that, and it still seemed to work. Likewise I don't yet understand the TNS set up and the significance that has. However.....there's all that still to learn....
setting the environment variable is like saying "this is my deafult db". So if you don't specify a database, you will connect to whatever ORACLE_SID is set to.
Have a look at your TNSNAMES.ORA file and you will see TNS entries for your two databases, which is why my suggestion worked.
Its the greatest argument in the world for making sure your SYS and SYSTEM and similar passwords are unique.
I had a horrible time when I first started with ORACLE back in 1823 by keeping the same SYS and SYSTEM pwd, issuing a startup and only discovered that my ORACLE_SID was wrong and I was in the wrong DB.
The TNSNAMES @ALIAS connection makes it all transparent.
Also watch out for the NTS authentication on Windows servers. If set you can mistakenly connect to the wrong database using connect 'sys/pwd as sysdba' as it'll autheticate using your NT logon and NOT your SYS password. Again - confusion that way reigns.
If you're using WINDOZE the ORACLE_SID can be set 3 ways:
(1) In the registry in the HOMEn folder.
(2) As an environment variable's tab in SYSTEM Properties in the Control Panel.
(If both set - (2) overrides (1) ... I think! Can't remember ... try it and see ... )
(3) Open a CMD window and type ORACLE_SID=sid
and then anything within the context of this CMD window uses this local SID ... e.g.
> connect sys/pwd as sysdba
Click Here to Expand Forum to Full Width