Forum Stats

  • 3,758,076 Users
  • 2,251,327 Discussions
  • 7,870,016 Comments

Discussions

two databases on one computer

ANNEC
ANNEC Member Posts: 294 Bronze Badge

I installed oracle software on my windows 10 for learning purpose.

Then I used DBCA created devOrcl database instance. (no container).

then I run DBCA again created another database instance called testOrcl. (no container db)

the I used sqlplus / as sysdba logged in, I tried to see which database I logged in, using select name from V$database; it is testOrcl.

Then how can I log in to devOrcl, that also install on this local machine?


Thanks,

Best Answers

  • EdStevens
    EdStevens Member Posts: 28,456 Gold Crown
    Accepted Answer

    The syntax 'sqlplus / as sysdba' relies on the setting of the environment variable ORACLE_SID to determine which database. Actually, any syntax that does not specify, such as

    sqlplus scott/tiger
    

    If you specify a tns address:

    sqlplus scott/[email protected] 
    

    Then it will look for an entry 'mydb' in the tnsnames.ora, and use the information found there to initiate a network connection.

    So before attempting to connect you should

    set ORACLE_SID=testOrcl
    sqlplus / as sysdba
    

    of course, change testOrcl to devOrcl, as needed.

    ANNEC
  • EdStevens
    EdStevens Member Posts: 28,456 Gold Crown
    Accepted Answer

    "but the connection is failed. not sure what is wrong."

    Totally devoid of actionable or diagnostic information.

    Yes you need to start a listener. All SQL Dev connections are via the network, even if on the same machine.

    You should only start on listener. I've often said to others, "burn this into your brain. One single listener, running from one ORACLE_HOME, is quite capable of -- indeed, was designed to -- service multiple databases of multiple versions running from multiple homes." The only caveat is that if you do have multiple versions, that the listener be running from the ORACLE_HOME of the highest version. If you are using Grid Infrastructure, the listener should run from the GI home.

    When you say "installed two databases", I hope you actually mean you created two databases under one single oracle software installation - one ORACLE_HOME.

    ANNEC

Answers

  • EdStevens
    EdStevens Member Posts: 28,456 Gold Crown
    Accepted Answer

    The syntax 'sqlplus / as sysdba' relies on the setting of the environment variable ORACLE_SID to determine which database. Actually, any syntax that does not specify, such as

    sqlplus scott/tiger
    

    If you specify a tns address:

    sqlplus scott/[email protected] 
    

    Then it will look for an entry 'mydb' in the tnsnames.ora, and use the information found there to initiate a network connection.

    So before attempting to connect you should

    set ORACLE_SID=testOrcl
    sqlplus / as sysdba
    

    of course, change testOrcl to devOrcl, as needed.

    ANNEC
  • ANNEC
    ANNEC Member Posts: 294 Bronze Badge
    edited Sep 24, 2021 4:02AM

    Thanks, I figured that out later too, It has to use set oracle_sid first then use sqlplus.

    The reason I tried to create two database instances, is that i want to practice restore database ,and export and import tool between two databases instances like dev and test environment.

    I tried to use oracle sql developer to connect to the two databases on the local computer I installed the two databases , use system as username, but the connection is failed. not sure what is wrong.

    Do I need to install listener too even the databases are on the same local machine I run oracle developer?

    If install listener, do I install two listeners each for each database, or I can just use one listener for two databases?

    Thanks for advice

  • EdStevens
    EdStevens Member Posts: 28,456 Gold Crown
    Accepted Answer

    "but the connection is failed. not sure what is wrong."

    Totally devoid of actionable or diagnostic information.

    Yes you need to start a listener. All SQL Dev connections are via the network, even if on the same machine.

    You should only start on listener. I've often said to others, "burn this into your brain. One single listener, running from one ORACLE_HOME, is quite capable of -- indeed, was designed to -- service multiple databases of multiple versions running from multiple homes." The only caveat is that if you do have multiple versions, that the listener be running from the ORACLE_HOME of the highest version. If you are using Grid Infrastructure, the listener should run from the GI home.

    When you say "installed two databases", I hope you actually mean you created two databases under one single oracle software installation - one ORACLE_HOME.

    ANNEC
  • ANNEC
    ANNEC Member Posts: 294 Bronze Badge

    Yes, I meant created two database under one single oracle software installation, one oracle home.

    thank you for the answers, it is very helpful