Tag Archives: SID

12c, Administration, CDB, Multitenant

Determining SID(s) from Database Server

Leave a comment

Oracle Databases implemented on Unix and Linux have a file /etc/oratab file which determines the current SID, Oracle Homes, or
active status which exist on the server, if a proper installation is done. This procedure uses the file to extract
current SID(s).

1. Logon to your Oracle Server as the Oracle software owner.

[root@linux2 ~]# su – oracle
Last login: Wed Oct 5 15:16:55 EDT 2016 on pts/2
[oracle@linux2 ~]$

2. Execute the command: egrep -E -v ‘^(#)’ /etc/oratab | awk /./ | awk -F: ‘{print $1}’

[oracle@linux2 ~]$ egrep -E -v ‘^(#)’ /etc/oratab | awk /./ | awk -F: ‘{print $1}’
cdb1
[oracle@linux2 ~]$

3. For this system there is only one database with a SID of ‘cdb1’.

Larry Catt
OCP

12c, Administration

Determining ORACLE_HOME(s) from Database Server

Leave a comment

Oracle Databases implemented on Unix and Linux have a file /etc/oratab file which determines the current SID, Oracle Homes, or
active status which exist on the server, if a proper installation is done. This procedure uses the file to extract
current ORACLE_HOME(s).

1. Logon to your Oracle Server as the Oracle software owner.

[root@linux2 ~]# su – oracle
Last login: Wed Oct 5 15:16:55 EDT 2016 on pts/2
[oracle@linux2 ~]$

2. Execute the command: egrep -E -v ‘^(#)’ /etc/oratab | awk /./ | awk -F: ‘{print $2}’

[oracle@linux2 ~]$ egrep -E -v ‘^(#)’ /etc/oratab | awk /./ | awk -F: ‘{print $2}’
/opt/app/oracle/product/12.1.0.2/db_1
[oracle@linux2 ~]$

3. For this system there is only one database with a ORACLE_HOME of ‘/opt/app/oracle/product/12.1.0.2/db_1’.

Larry Catt
OCP

11g, 12c, Administration

Current sid and serial# of a session from SQL*PLUS

Leave a comment

1. From within Oracle SQL*Plus session.

2. Using the userenv(‘SESSIONID’) to retrieve current sid and serial#.

select sid, serial# from v$session where audsid=userenv(‘SESSIONID’);

SQL> select sid, serial# from v$session where audsid=userenv(‘SESSIONID’);

SID SERIAL#
———- ———-
408 16328

SQL>

3. Note: That logging out of SQL*Plus session or reconnecting will change your SID and SERIAL#.

Larry Catt
OCP

hp-ux, itanium, linux, Oracle, Oracle 10g, oracle 9i

ORACLE SID not defined in the OS layer:

Leave a comment

This article covers the error which will occur in a Linux or UNIX environment if the OS parameter ORACLE_SID is not properly set and how to resolve this error.

1. With the ORACLE_SID OS parameter set to null, attempt to start SQLPLUS as sysdba. You will receive the following error – ORA-12162.

mylinux:> sqlplus ‘/ as sysdba’

SQL*Plus: Release 10.2.0.4.0 – Production on Wed Jan 6 14:55:56 2010

Copyright (c) 1982, 2007, Oracle. All Rights Reserved.

ERROR:
ORA-12162: TNS:net service name is incorrectly specified

Enter user-name:

2. In the OS layer define your database sid with the OS parameter ORACLE_SID.

mylinux:> export ORACLE_SID=orcl
mylinux:>

3. Attempt to start SQLPLUS as sysdba.

mylinux:> sqlplus ‘/ as sysdba’

SQL*Plus: Release 10.2.0.4.0 – Production on Wed Jan 6 14:57:54 2010

Copyright (c) 1982, 2007, Oracle. All Rights Reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 – 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL>

4. Oracle SQLPLUS starts normally after the ORACLE_SID parameter is set at the OS layer correctly.

Larry J Catt, OCP 9i, 10g
oracle@allcompute.com
www.allcompute.com