How to manage more than one database?

[don9999]

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:

SPFILE=h:\....\spfiledb1.ora

and using the command:

STARTUP PFILE=h:\....\temp.ora

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!

Cheers,
Don

[waitecj]

Do exactly as you have been doing, but each time to connect to an idle instance using sys or whatever, make sure you specify:

sys/pwd@ORACLE_SID

for each database you want to open .

[bazza]

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.

[davey23uk]

or

set ORACLE_SID=XXX

[don9999]

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....

Cheers,
Don

[waitecj]

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.

[JMac]

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.

[JMac]

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.

SQLPLUS \nolog

> connect sys/pwd as sysdba