How to start some instance in Oracle if there are 2 homes path?

Question

On Oracle, we have 2 database one is Production and one is Test. Production is lcoated in dbhome1 and Test is located in dbhome2.

We recently had a power outage after which the Production instance (PRDACT) came up automatically, but the Test instance (PRDTST) was not showing when I typed lsnrctl status, it showed only PRDACT.

Please see the following screenshot:

When I login with username/password for PRDTST I received the following error:

ORA-12514 TNS:listener does not currently know of service requested in connect descriptor.

Then I checked tnsname.ora in the dbname1 path I found no service name on connected_data for PRDTST.

After that I checked tnsnames.ora in dbname2 path I found service name is showing normally.

How do I start up the Oracle instance PRDTST? Could you please help me for this case?

Additional Information After Answer

Thank you for providing us POC for ODA.

However, I used .oraenv to change environment to dbhome2

But after typed lsnrctl status. PRDTST is still not showing. See result below.

I'm not sure we need to config for 4 variables or not. Could you please guide on this?

Answer 1

If you are running Oracle with multiple homes, then you have to change certain variables before you can interact with the corresponding instance. I had the opportunity to work with ODA in a Proof Of Concept and encountered similar issues when trying to interact with certain database instances running in multiple homes, and even more so with PDBs (Pluggable Databases) and CDBs (Multitennant Container Databases). An overview of CDBs and PDBs can be found in the article CDBs and PDBs (Oracle | Docs).

When in a Linux environment, you can view the current settings for various ORACLE "variables" by typing export | grep ORA. This will provide you with a liste of variables that are valid for the system (and not just in your session).

E.g.

...
declare -x ORACLE_BASE="/u00/app/oracle"
declare -x ORACLE_DOC="/u00/app/oracle/product/rdbms19/doc"
declare -x ORACLE_HOME="/u00/app/oracle/product/rdbms19"
declare -x ORACLE_PDB_SID="MYPDB"
declare -x ORACLE_SID="SOMESID"
declare -x ORATAB="/u00/app/oracle/etc/oratab"
declare -x ORA_MODULE=""
...

Switching Oracle Homes

To switch between the different environments, you will have to set the variables for at least the following Oracle variables by changing them at the bash prompt:

Option 1

Manually changing the variables using the bash shell and the export command.

export ORACLE_SID="PRDTST"
export ORACLE_BASE="/u01/app/oracle/"
export ORACLE_HOME="/u01/app/oracle/product/12.2.0.1/dbhome2"
export TNS_ADMIN="/u01/app/oracle/product/12.2.0.1/dbhome2/network/admin"

Option 2

Using the oraenv utility.

# oraenv
ORACLE_SID = [CURRENT_SID] ? <Enter new SID>

Database States

You should be able to view the state of the database in the current scope (after setting all the ORACLE... variables) of the current listener by typing the listener commands:

lsnrctl status
lsnrctl services

Connecting to SID or CDB

Once you have the service names (e.g. SERVICE_NAME=SRV_PROD_CDB_01, you can then connect either directly to the database using the SID if the oracle database is not a non-PDB:

# sqlplus sys@PRDTST as sysdba

...or if your database is inside a PDB connecting via service:

# sqlplus sys@<SERVICE_NAME> as sysdba

e.g.

# sqlplus sys@SRV_PROD_CDB_01 as sysdba

Checking the Database State

To check the database state with a non-pluggable database instance run:

SQL> select dbid, name, open_mode from v$database;

With a CDB/PDB execute the following first:

SQL> ALTER SESSION SET CONTAINER = CDB$ROOT;
SQL> SHOW PDBS

Once you have the PDB name, run (with the name you provided in your question):

SQL> ALTER SESSION SET CONTAINER=PRDTST;
SQL> select dbid, name, open_mode from v$database;

Mounting / Opening the Oracle Database or PDB

Once you know the state of your database, run either of the following commands:

Database Not Started

SQL> STARTUP

Database Started but not Mounted

SQL> ATLER DATABASE OPEN;

Database Mounted

SQL> ALTER DATABASE OPEN;

---

Additional Information after question was redacted

As mentioned in your comment and edited question, you seem to be having an issue with the Oracle instance not having been registered correctly with the corresponding listener.

Check Listener Configuration in Oracle Instance

Log in to your Oracle instance with the following command:

# sqlplus sys@PRDTST as sysdba /nolog

Once inside the instance query the parameter local_listener:

SQL> sho parameter local_listener;

This should output something similar to the following:

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
local_listener                       string      (ADDRESS=(PROTOCOL=TCP)(HOST=y
                                                 ourhost.domain.tld)>     (PORT=152
                                                 1))
SQL>

If the output isn't correct then execute the following command to change the value to correspond with your current listener:

SQL> alter system set local_listener='(ADDRESS=(PROTOCOL=TCP)(HOST=correcthost.domain.tld)(PORT=1521))';

_{Change correcthost.domain.tld to match your configuration}

Registering Database Instance with Listener

Once you have set the correct value or verified the current value, then execute the following command to add the Oracle instance to your current listener:

SQL> alter system register;

Once the Oracle instance has been registered with the current listener, execute one of the lsnrctl commands to check that the instance is now detected by your current listener.

---

Afterword

Because I don't know the exact configuration of your environment I can only provide possible solutions. Your mileage may vary. Sadly I no longer have any output from my ODA POC with which I could have provided more examples. Good luck.