2

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:
Listener CTL Status Window

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.

tnsnames.ora for dbname1 path

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

tnsnames.ora for dbname2 path

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 enter image description here

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

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

8
  • Utilize . oraenv <<< "SID" (note the dot) to set the different ORACLE_HOME environments. From there you can operate the different databases/listeners. Nov 9, 2022 at 11:34
  • Thank you @BjarteBrandt but I'm not sure how can I start PRDTST now. Could yu please advise?
    – Peung
    Nov 9, 2022 at 11:39
  • . oraenv <<< "PRDTST" -> sqlplus / as sysdba -> SQL>startup . Since you have GI installed you can also utilize the tool "srvctl start|status|stop database -d PRDTST" Nov 9, 2022 at 13:09
  • Thank you so much @BjarteBrandt. After I ran srvctl start database -d PRDTST , If found error. CRS-2632 there are no more servers to try to place resource. Checked trace file and found error below. 2022-11-09 21:29:07.773 :CLSDYNAM:922261248: [ora.prdtst.db]{1:7972:47464} [check] InstConnection::connectInt 020 server not attached 2022-11-09 21:29:07.792 :CLSDYNAM:922261248: [ora.prdtst.db]{1:7972:47464} [check] ORA-01034: ORACLE not available ORA-27101: shared memory realm does not exist Linux-x86_64 Error: 2: No such file or directory Any suggest on this please?
    – Peung
    Nov 9, 2022 at 15:22
  • You have to set the correct environment before you fire off "srvctl start". Make sure you execute as user "oracle". Make sure environment is correct. "env | grep ORA". Verify environment looking at file "cat /etc/oratab" Nov 9, 2022 at 20:16

1 Answer 1

2

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.

4
  • Thank you @John K. N. for your suggestion. However, I used .oraenv to change environment to dbhome2. But after typed lsnrctl status. PRDTST is still not showing. $ORACLE_HOME $ORACLE_SID is already pointed to PRDTST but not showing when type lsnrctl status. Could you please suggest on this?
    – Peung
    Nov 20, 2022 at 14:19
  • @Peung I have added an additional section explaining how an instance can be registered with the listener.
    – John K. N.
    Nov 21, 2022 at 14:12
  • Thank you for patient with me. I'm quite new to oracle. I changed . oraenv to PRDTST then I typed sqlplus sys@PRDTST as sysdba as you suggested but I got ERROR: ORA-12514: TNS:listener does not currently know of service requested in connect descriptor. Then I put username only as sys and my password like xxxx as sysdba and I can login. I don't quite understand PDB/CDB quite well. But next step I need to set local listener right as after sho command , output is no listener I got error ORA-01034: ORACLE not available.
    – Peung
    Nov 23, 2022 at 3:49
  • Try performing the steps I outlined in the Checking the Database State section. If that fails, then you possibly have a different issue, which would possibly require a database consultant.
    – John K. N.
    Nov 23, 2022 at 9:38

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.