two databases on one computer

ANNEC

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,

EdStevens

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.

EdStevens

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

EdStevens

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

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

"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

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

thank you for the answers, it is very helpful