How to manage more than one database?
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 8 of 8

Thread: How to manage more than one database?

  1. #1
    Join Date
    May 2002
    Posts
    15

    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:

    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

  2. #2
    Join Date
    Jul 2002
    Location
    Northampton, England
    Posts
    612
    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 .

  3. #3
    Join Date
    Jul 2002
    Posts
    335
    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.

  4. #4
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,333
    or

    set ORACLE_SID=XXX

  5. #5
    Join Date
    May 2002
    Posts
    15
    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

  6. #6
    Join Date
    Jul 2002
    Location
    Northampton, England
    Posts
    612
    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.

  7. #7
    Join Date
    Jan 2000
    Location
    Chester, England.
    Posts
    818
    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.

  8. #8
    Join Date
    Jan 2000
    Location
    Chester, England.
    Posts
    818
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width