How to recognize your oracle database architecture #JoelKallmanDay

I’m very happy to notice Tim Hall‘s blog note – Joel Kallman Day 2022 : Announcement on Twitter and LinkedIn on Sep 27th (at the end of last month). To be honest I’ve participated the commemoration day twice resptectively on 2019 and 2021 (2020 – “called off it” due to the pandemic). Of course I would like to join it this year for sharing my oracle database knowledge (probably I have no more chance to use Oracle APEX).

Initial Linux SHELL script

How to recognize your oracle database architecture is an interesting topic that I discovered recently. Initially I took a few time finishing writing a Linux SHELL script oracle_dg_arch_recognize.sh and published it on my GitHub repo but the script only uses to recognize oracle Data Guard architecture (not includes Single Instance and RAC).

It’s easy to execute the SHELL script on your target oracle database server like this:

-- the approach running this shell script: switching to root user of target server and executing the script, e.g.
./oracle_dg_arch.recognize.sh password (password is a parameter called the shell script and it is a password by user system within oracle database) 

[root@testdb ~]# ./oracle_dg_arch.recognize.sh password
**********The Local Oracle Database Info**********
IP ADDRESS: 192.168.12.11
LISTENER PORT: 1521
DATABASE NAME: orcl11
DATABASE UNIQUE NAME: orcl11dg49
DATABASE TYPE: Single Instance
DATABASE ROLE: PRIMARY
**************************************************

**********The Remote Oracle Database Info**********
IP ADDRESS: 192.168.12.12
LISTENER PORT: 1521
DATABASE NAME: orcl11
DATABASE UNIQUE NAME: orcl11
DATABASE TYPE: Single Instance
DATABASE ROLE: PHYSICAL STANDBY
***************************************************

A New Thought of Oracle SQL and PLSQL

Always ready to challenge myself is one of my favorites. Hence I decide to merely write (depend) a few oracle SQL and PLSQL statements to recognize oracle architecture (Single Instance, Data Guard and RAC) on my target oracle database server.

Recognizing Single Instance and RAC

Yes, recognizing Single Instance and RAC is more easier than Data Guard. We can check oracle parameter cluster_database whose value is TRUE or FALSE. The related SQL is below:

SELECT value FROM v$parameter WHERE name = 'cluster_database';

Recognizing Data Guard

Usually we can check oracle parameter log_archive_config and log_archive_dest_n (n stands for 1 to 31) for Data Guard’s configuration info. The corresponding SQLs are as follows:

SELECT SUBSTR(LOWER(value), INSTR(LOWER(value), 'dg_config=')+11, LENGTH(LOWER(value))-12) value
FROM v$parameter WHERE name = 'log_archive_config';

SELECT SUBSTR(LOWER(value), 9, INSTR(LOWER(value), ' ', 1, 1)-9) remote_tns_name,
       SUBSTR(LOWER(value), INSTR(LOWER(value), 'db_unique_name=')+15) remote_db_unq_name
FROM v$parameter
WHERE name NOT LIKE 'log_archive_dest_state_%'
AND name LIKE 'log_archive_dest_%'
AND value IS NOT NULL
AND LOWER(value) LIKE 'service%';

Checking Basic Info

Apart from recognizing oracle database architecture we can also check basic info from oracle dynamic performance view v$instance and v$database.

SELECT version,
       host_name,
       utl_inaddr.get_host_address(host_name) ip_addr,
       instance_name
FROM v$instance;

SELECT name,
       db_unique_name,
       platform_name,
       open_mode,
       log_mode,
       database_role,
       protection_mode,
       switchover_status
FROM v$database;

It notices that you should check gv$instance and gv$database if your oracle database is RAC architecture.

Acquiring Oracle Listener Port

As you can see from the previous SQL as we’re able to acquire the IP address of oracle database server by oracle internal package utl_inaddr and its subprogram function get_host_address but it seems to have a little difficult for acquiring oracle listener port using SQL statement. As well-known as the best method checking listener port is hitting keyboard to enter OS command “lsnrctl status listener_name” on your Command Line Interface. Next I use awk to extract “PORT=” to get listener port eventually. For example, the following is simple linux shell command:

lsnrctl status | grep "Connecting to" | awk -F 'PORT=' '{print $2}' | awk -F ')' '{print $1}'

Because linux command hasn’t run directly on SQL*Plus or SQL statement you should remember putting “!” or “host” before the preceding linux command. Although SQL statement supports the OS command unfortunately PLSQL doesn’t support it.

Afterwards I have to search the keyword v$listener on the search box of Oracle Database Documentation, wow I found out a useful view v$dispatcher_config (it might have been able to help me to get oracle listener port). The following two screenshots indicates my searching process.

Expectedly I get a critical string with listener port when runnning this SQL (SELECT listener FROM v$dispatcher_config;) separately on oracle environment single instance and rac.

[oracle@rhel76 ~]$ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Fri Oct 7 16:32:42 2022
Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle.  All rights reserved.


Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0

SQL> SELECT listener FROM v$dispatcher_config;

LISTENER
--------------------------------------------------------------------------------
(ADDRESS=(PROTOCOL=TCP)(HOST=rhel76)(PORT=1521))


[oracle@yyds02 ~]$ sqlplus / as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on Fri Oct 7 16:15:31 2022

Copyright (c) 1982, 2016, Oracle.  All rights reserved.


Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

SQL> SELECT listener FROM v$dispatcher_config;

LISTENER
--------------------------------------------------------------------------------------------------------------------------------------------------------------------
(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.1.8)(PORT=1521)), (DESCRIPTION=(CONNECT_DATA=(SERVICE_NAME=))(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.1.18)(PORT=1521)))

As you can see from the prior SQL query now we’ll use two different SQLs to extract listener port, the former is for single instance and the latter is for rac.

SELECT SUBSTR(listener, INSTR(listener, 'PORT=')+5, LENGTH(SUBSTR(listener, INSTR(listener, 'PORT=')+5))-2) listener_port
FROM v$dispatcher_config;

SELECT SUBSTR(listener, INSTR(listener, 'PORT=', 1, 2)+5, LENGTH(SUBSTR(listener, INSTR(listener, 'PORT=', 1, 2)+5))-3) listener_port
FROM v$dispatcher_config;

Recognizing Multi-tenant and Lists all of PDBs

The multi-tenant (containers database, also called CDB), as everyone knows, has been supported from oracle version 12.1, commonly the SQL (primarily anonymous PLSQL block) script need to run each different oracle version (at least 10.2 and later) nevertheless v$pdbs has been added from 12.1 so we must use conditional compilation function of PLSQL (supported it from 10.2) to adapt/adjust selecting v$pdbs only after version 12.1. The example of plsql code looks like this:

$IF ...... $THEN
......
$ELSIF ...... $THEN
......
$ELSE
......
$END

Testing SQL/PLSQL script on different oracle version and architecture

10.2 (Single Instance)

SQL> @recognize_oracle_db_arch.sql;  
*************************************************************************
Oracle Database Architecture: Single Instance
Oracle Database Version: 10.2.0.1.0
Oracle Database Host Name: oraDoc-004
Oracle Database IP Address: 172.16.0.5
Oracle Database Instance Name: orcl
Oracle Database Database Name: ORCL
Oracle Database Database Unique Name: orcl
Oracle Database Platform Name: Linux 64-bit for AMD
Oracle Database Open Mode: READ WRITE
Oracle Database Log Mode: NOARCHIVELOG
Oracle Database Role: PRIMARY
Oracle Database Protection Mode: MAXIMUM PERFORMANCE
Oracle Database Switchover Status: SESSIONS ACTIVE
Oracle Database Service Names: orcl
Oracle Database Listener Port: 1521
Oracle Database Multi-tenant: NONE
*************************************************************************

PL/SQL procedure successfully completed.

11.2 (Data Guard with Primay and Physical Standby are both Single Instance)

SYS@orcl>@recognize_oracle_db_arch.sql;
*************************************************************************
Oracle Database Remote TNS Name: orcldg
Oracle Database Remote Unique Name: orcldg
*************************************************************************
*************************************************************************
Oracle Database Architecture: Data Guard
Oracle Database Version: 11.2.0.4.0
Oracle Database Host Name: demo11
Oracle Database IP Address: 192.168.1.11
Oracle Database Instance Name: orcl
Oracle Database Database Name: ORCL
Oracle Database Database Unique Name: orcl
Oracle Database Platform Name: Linux x86 64-bit
Oracle Database Open Mode: READ WRITE
Oracle Database Log Mode: ARCHIVELOG
Oracle Database Role: PRIMARY
Oracle Database Data Guard Primary Architecture: Single Instance
Oracle Database Protection Mode: MAXIMUM AVAILABILITY
Oracle Database Switchover Status: UNRESOLVABLE GAP
Oracle Database Service Names: orcl
Oracle Database Listener Port: 1521
Oracle Database Multi-tenant: NONE
*************************************************************************

PL/SQL procedure successfully completed.

SYS@orcl>conn sys@orcldg as sysdba;
Enter password: **********                                                                                                                 
Connected.
SYS@orcldg>@recognize_oracle_db_arch.sql;
*************************************************************************
Oracle Database Remote TNS Name: orcl
Oracle Database Remote Unique Name: orcl
*************************************************************************
*************************************************************************
Oracle Database Architecture: Data Guard
Oracle Database Version: 11.2.0.4.0
Oracle Database Host Name: demo12
Oracle Database IP Address: 192.168.1.12
Oracle Database Instance Name: orcl
Oracle Database Database Name: ORCL
Oracle Database Database Unique Name: orcldg
Oracle Database Platform Name: Linux x86 64-bit
Oracle Database Open Mode: READ ONLY WITH APPLY
Oracle Database Log Mode: ARCHIVELOG
Oracle Database Role: PHYSICAL STANDBY
Oracle Database Data Guard Physical Standby Architecture: Single Instance
Oracle Database Protection Mode: MAXIMUM AVAILABILITY
Oracle Database Switchover Status: NOT ALLOWED
Oracle Database Service Names: orcldg
Oracle Database Listener Port: 1521
Oracle Database Multi-tenant: NONE
*************************************************************************

PL/SQL procedure successfully completed.

As you can see from the prior query result (if it is Data Guard) it will show section Oracle Database Remote TNS Name and Unique Name firstly whatever it is on oracle data guard primary or physical standby, so we need to continue checking info of remote database on local database (just inputting “conn sys@remote_tns_name as sysdba” and then inputting password of SYS schema to connect to remote database, you know, the password of SYS schema is same on primary database and physical standby database).

12.2 (Single Instance, Data Guard with Primay is RAC and Physical Standby is Single Instance)

SYS@orcl>@recognize_oracle_db_arch.sql;
*************************************************************************
Oracle Database Architecture: Single Instance
Oracle Database Version: 12.2.0.1.0
Oracle Database Host Name: k8s215
Oracle Database IP Address: 192.168.1.215
Oracle Database Instance Name: orcl12cPDB
Oracle Database Database Name: ORCL12CPDB
Oracle Database Database Unique Name: orcl12cPDB
Oracle Database Platform Name: Linux x86 64-bit
Oracle Database Open Mode: READ WRITE
Oracle Database Log Mode: NOARCHIVELOG
Oracle Database Role: PRIMARY
Oracle Database Protection Mode: MAXIMUM PERFORMANCE
Oracle Database Switchover Status: NOT ALLOWED
Oracle Database Service Names: orcl12cPDB
Oracle Database Listener Port: 1521
Oracle Database Multi-tenant: TRUE
Oracle PDB Name: CMDBPDB Open Mode: READ WRITE
Oracle PDB Name: PDB$SEED Open Mode: READ ONLY
Oracle PDB Name: PDBORCL Open Mode: READ WRITE
Oracle PDB Name: TESTPDB Open Mode: READ WRITE
*************************************************************************

PL/SQL procedure successfully completed.
SQL> @recognize_oracle_db_arch.sql;
*************************************************************************
Oracle Database Remote TNS Name: yydsdbdg23
Oracle Database Remote Unique Name: yydsdbdg23
*************************************************************************
*************************************************************************
Oracle Database Architecture: Data Guard
Oracle Database Version: 12.2.0.1.0
Oracle Database Host Name: yyds02
Oracle Database IP Address: 192.168.1.45
Oracle Database Instance Name: yydsdb2
Oracle Database Database Name: YYDSDB
Oracle Database Database Unique Name: yydsdb
Oracle Database Platform Name: Linux x86 64-bit
Oracle Database Open Mode: READ WRITE
Oracle Database Log Mode: ARCHIVELOG
Oracle Database Role: PRIMARY
Oracle Database Data Guard Primary Architecture: RAC
Oracle Database Protection Mode: MAXIMUM PERFORMANCE
Oracle Database Switchover Status: TO STANDBY
Oracle Database Service Names: yydsdb
Oracle Database Listener Port: 1521
Oracle Database Multi-tenant: TRUE
Oracle PDB Name: PDB$SEED Open Mode: READ ONLY
Oracle PDB Name: YYDSPDB Open Mode: READ WRITE
*************************************************************************

PL/SQL procedure successfully completed.

SQL> conn sys@yydsdbdg23 as sysdba
Enter password: *********
Connected.
SQL> 
SQL> @recognize_oracle_db_arch.sql;
*************************************************************************
Oracle Database Remote TNS Name: yydsdb
Oracle Database Remote Unique Name: yydsdb
*************************************************************************
*************************************************************************
Oracle Database Architecture: Data Guard
Oracle Database Version: 12.2.0.1.0
Oracle Database Host Name: yyds03
Oracle Database IP Address: 192.168.1.48
Oracle Database Instance Name: yydsdb
Oracle Database Database Name: YYDSDB
Oracle Database Database Unique Name: yydsdbdg23
Oracle Database Platform Name: Linux x86 64-bit
Oracle Database Open Mode: READ ONLY WITH APPLY
Oracle Database Log Mode: ARCHIVELOG
Oracle Database Role: PHYSICAL STANDBY
Oracle Database Data Guard Physical Standby Architecture: Single Instance
Oracle Database Protection Mode: MAXIMUM PERFORMANCE
Oracle Database Switchover Status: NOT ALLOWED
Oracle Database Service Names: yydsdbdg23
Oracle Database Listener Port: 1521
Oracle Database Multi-tenant: TRUE
Oracle PDB Name: PDB$SEED Open Mode: READ ONLY
Oracle PDB Name: YYDSPDB Open Mode: READ ONLY
*************************************************************************

PL/SQL procedure successfully completed.

19.3 (Single Instance, RAC)

SQL> @recognize_oracle_db_arch.sql;
*************************************************************************
Oracle Database Architecture: Single Instance
Oracle Database Version: 19.0.0.0.0
Oracle Database Host Name: rhel76
Oracle Database IP Address: 192.168.10.50
Oracle Database Instance Name: orcl
Oracle Database Database Name: ORCL
Oracle Database Database Unique Name: orcl
Oracle Database Platform Name: Linux x86 64-bit
Oracle Database Open Mode: READ WRITE
Oracle Database Log Mode: ARCHIVELOG
Oracle Database Role: PRIMARY
Oracle Database Protection Mode: MAXIMUM PERFORMANCE
Oracle Database Switchover Status: NOT ALLOWED
Oracle Database Service Names: orcl
Oracle Database Listener Port: 1521
Oracle Database Multi-tenant: TRUE
Oracle PDB Name: EMCCPDB Open Mode: READ WRITE
Oracle PDB Name: PDB Open Mode: MOUNTED
Oracle PDB Name: PDB$SEED Open Mode: READ ONLY
*************************************************************************

PL/SQL procedure successfully completed.
SQL> @recognize_oracle_db_arch.sql;
*************************************************************************
Oracle Database Architecture: RAC
Oracle Database Version: 19.0.0.0.0
Oracle Database Host Name: ensrac01
Oracle Database IP Address: 192.168.10.161
Oracle Database Instance Name: ensdb1
Oracle Database Database Name: ENSDB
Oracle Database Database Unique Name: ensdb
Oracle Database Platform Name: Linux x86 64-bit
Oracle Database Open Mode: READ WRITE
Oracle Database Log Mode: NOARCHIVELOG
Oracle Database Role: PRIMARY
Oracle Database Protection Mode: MAXIMUM PERFORMANCE
Oracle Database Switchover Status: NOT ALLOWED
Oracle Database Service Names: ensdb
Oracle Database Listener Port: 1521
Oracle Database Multi-tenant: TRUE
Oracle PDB Name: ENSPDB Open Mode: READ WRITE
Oracle PDB Name: PDB$SEED Open Mode: READ ONLY
*************************************************************************
*************************************************************************
Oracle Database Architecture: RAC
Oracle Database Version: 19.0.0.0.0
Oracle Database Host Name: ensrac02
Oracle Database IP Address: 192.168.10.162
Oracle Database Instance Name: ensdb2
Oracle Database Database Name: ENSDB
Oracle Database Database Unique Name: ensdb
Oracle Database Platform Name: Linux x86 64-bit
Oracle Database Open Mode: READ WRITE
Oracle Database Log Mode: NOARCHIVELOG
Oracle Database Role: PRIMARY
Oracle Database Protection Mode: MAXIMUM PERFORMANCE
Oracle Database Switchover Status: NOT ALLOWED
Oracle Database Service Names: ensdb
Oracle Database Listener Port: 1521
Oracle Database Multi-tenant: TRUE
Oracle PDB Name: ENSPDB Open Mode: READ WRITE
Oracle PDB Name: PDB$SEED Open Mode: READ ONLY
*************************************************************************

PL/SQL procedure successfully completed.

How to get my SQL/PLSQL script

I’ve uploaded this script into my GitHub, you can see here, always welcome to star it if you think it’s really valuable and benefit you.

By Quanwen Zhao

I’m Quanwen Zhao. Currently I’ve been hiring at Beijing ShinData Tech Co., Ltd and am a Leader Oracle DBA with more than 9 years experiences. At the same time I am responsible for some customers' Oracle Database in China and and enhance/improve company's DBAAS product that focuses on oracle database. In the meantime I’m pretty lucky to award as an ACE by Oracle Corp. on Jun 17th, 2020.

Leave a comment

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

%d bloggers like this: