How to Find the Number of Runs of a Specific SQL_ID Day by Day?

SELECT A.INSTANCE_NUMBER,
TO_DATE (TO_CHAR (BEGIN_INTERVAL_TIME, 'DD.MM.YYYY'), 'DD.MM.YYYY')
DAY,
SUM (EXECUTIONS_DELTA)
EXECUTIONS
FROM DBA_HIST_SQLSTAT A, DBA_HIST_SNAPSHOT B
WHERE A.SNAP_ID = B.SNAP_ID
AND A.INSTANCE_NUMBER = B.INSTANCE_NUMBER
AND SQL_ID = '&SQL_ID'
GROUP BY A.INSTANCE_NUMBER,
TO_DATE (TO_CHAR (BEGIN_INTERVAL_TIME, 'DD.MM.YYYY'), 'DD.MM.YYYY')
ORDER BY 2

How to Find Oracle SQL_IDs & SQL_TEXTs Which Have Lob Data According to Number of Runs?

Below sql is used to find table names which have lob data in a single line in order to use where clause.

-- TABLE_NAMES_WHICH_HAVE_LOB_COLUMNS_FOR_WHERE_CLAUSE
SELECT LISTAGG (
'DBMS_LOB.SUBSTR (SQ.SQL_TEXT, 4000) LIKE '
|| '''%'
|| DC.TABLE_NAME
|| '%'''
|| ' OR ')
WITHIN GROUP (ORDER BY DC.TABLE_NAME)
TABLE_NAMES_WHICH_HAVE_LOB_COLUMNS_FOR_WHERE_CLAUSE
FROM DBA_TAB_COLS DC
WHERE DC.OWNER IN ('TYPE_YOUR_SCHEMA_NAMES')
AND DC.DATA_TYPE IN ('CLOB', 'LOB', 'BLOB');

After the first sql, below sql is used to find needed information. The information is for the last month. The reason is that statistics for the last month are kept in the database for me. It depends on your statistic kept configuration.

SELECT DISTINCT
AA.EXECUTIONS EXECUTION_NUMS,
AA.SQL_ID,
DBMS_LOB.SUBSTR (SQ.SQL_TEXT, 4000) SQL_TEXT
FROM (SELECT *
FROM ( SELECT A.SQL_ID, SUM (EXECUTIONS_DELTA) EXECUTIONS
FROM DBA_HIST_SQLSTAT A, DBA_HIST_SNAPSHOT B
WHERE A.SNAP_ID = B.SNAP_ID
AND A.INSTANCE_NUMBER = B.INSTANCE_NUMBER
GROUP BY A.SQL_ID
ORDER BY 2 DESC)) AA,
DBA_HIST_SQLTEXT SQ
WHERE AA.SQL_ID = SQ.SQL_ID
AND ('TABLE_NAMES_WHICH_HAVE_LOB_COLUMNS_FOR_WHERE_CLAUSE_FROM_FIRST_SQL')
ORDER BY AA.EXECUTIONS DESC

Step by Step Iptables (Firewall) Configuration on Linux

You can find sample iptables configuration and explanations in this article.

Since these rules may differ on each server, port analysis should be done well. Port connection analysis can be done netstat command as follows. Detected connections are processed into rules.

[root@serhatcelik ~]# netstat -n -a -p | grep ESTABLISHED

Authorizations are done by editing the server’s /etc/sysconfig/iptables file.

For the changes to take effect, service iptables restart command is run.

[root@serhatcelik ~]# service iptables restart

PROCEDURES TO BE TAKEN TO REDIRECT THE LOG FILE TO THE CUSTOM LOG FILE

If the following operations are not performed, iptables log information is written to the /var/log/messages file.

In order for IPTABLES log information to be written to the log file we specified, the following operations must be performed.

1. If the rsyslog rpm is not installed, it will be installed.

[root@serhatcelik ~]# rpm -qa | grep rsyslog
rsyslog-5.8.10-10.0.1.el6_6.x86_64
[root@serhatcelik ~]#

2. rsyslog conf file is created in the following directory and the following 2 lines are entered.

[root@serhatcelik ~]# vi /etc/rsyslog.d/iptables_log.conf
:msg, contains, "IPTables-Dropped:" -/var/log/iptables.log
& ~

3. rsyslog service is restarted.

[root@serhatcelik ~]# service rsyslog restart
Shutting down system logger: [ OK ]
Starting system logger: [ OK ]
[root@serhatcelik ~]#

4. IPTABLES log information can be checked with the tail -444f /var/log/iptables.log command.

SAMPLE IPTABLES CONFIGURATION

[root@serhatcelik ~]# more /etc/sysconfig/iptables
# Firewall configuration written by system-config-firewall
# Manual customization of this file is not recommended.

*filter

### LOOPBACK ### PING, LSNRCTL STATUS, ETC ###
-A INPUT -i lo -j ACCEPT -m comment --comment "LOOPBACK PING, LSNRCTL STATUS, ETC"

### DNS NSLOOKUP ###
-A INPUT -p udp -s TYPE_YOUR_DNS_SERVER_NAME_OR_IP --sport 53 -j ACCEPT -m comment --comment "IN ORDER TO ALLOW NSLOOKUP"

### SMTP ###
-A INPUT -p tcp -s TYPE_YOUR_SMTP_SERVER_NAME_OR_IP --sport 25 -j ACCEPT -m comment --comment "IN ORDER TO ALLOW SENDING EMAIL FROM SERVER"

### NTP ###
-A INPUT -p udp -s TYPE_YOUR_NTP_SERVER_NAME_OR_IP --sport 123 -j ACCEPT -m comment --comment "IN ORDER TO TAKE DATE/TIME INFORMATION FROM SERVER"

### DBA SSH ###
-A INPUT -p tcp -s TYPE_GRANTED_USER_IP  --dport 22 -j ACCEPT -m comment --comment "SERHAT CELIK SSH GRANT"

### DBA VNC ###
-A INPUT -p tcp -s TYPE_GRANTED_USER_IP --dport 5900:5910 -j ACCEPT -m comment --comment "SERHAT CELIK VNC GRANT"

### DBA ICMP (PING) ###
-A INPUT -p icmp --icmp-type 8 -s TYPE_GRANTED_USER_IP -j ACCEPT -m comment --comment "SERHAT CELIK PING GRANT"

### RSH ###
-A INPUT -p tcp -s TYPE_GRANTED_USER_IP --dport 514 -j ACCEPT -m comment --comment "TYPE_GRANTED_USER_IP RSH GRANT"
-A INPUT -p tcp -s TYPE_GRANTED_USER_IP --sport 514:1023 -j ACCEPT -m comment --comment "TYPE_GRANTED_USER_IP RSH GRANT"

### NFS ###
-A INPUT -p tcp -s TYPE_GRANTED_USER_IP --sport 2049 -j ACCEPT -m comment --comment "NFS GRANT"

### ENTERPRISE MANAGER ###
-A INPUT -p tcp -s TYPE_EM_CLOUD_MANAGER_NAME_OR_IP --sport 4903 -j ACCEPT -m comment --comment "ENTERPRISE MANAGER GRANT"

### SAMBA SHARING ###
-A INPUT -s 10.0.0.0/0 -p udp -m udp --dport 137 -j ACCEPT -m comment --comment "SAMBA SHARING GRANT"
-A INPUT -s 10.0.0.0/0 -p udp -m udp --dport 138 -j ACCEPT -m comment --comment "SAMBA SHARING GRANT"
-A INPUT -s 10.0.0.0/0 -p tcp -m tcp --dport 139 -j ACCEPT -m comment --comment "SAMBA SHARING GRANT"
-A INPUT -s 10.0.0.0/0 -p tcp -m tcp --dport 445 -j ACCEPT -m comment --comment "SAMBA SHARING GRANT"

### INCOMING DB CONNECTIONS ###
-A INPUT -p tcp -s TYPE_GRANTED_USER_IP --dport 1521 -j ACCEPT -m comment --comment "SERHAT CELIK DB CONNECTION GRANT"

### DBLINK CONNECTIONS ###
-A INPUT -p tcp -s TYPE_GRANTED_SERVER_IP_OR_NAME --sport 1521 -j ACCEPT -m comment --comment "XXXDB DBLINK CONNECTION GRANT"

### SOLARWINDS ###
-A INPUT -p tcp -s TYPE_YOUR_SOLARWINDS_IP_OR_NAME --dport 161 -j ACCEPT -m comment --comment "SOLARWINDS GRANT"
-A INPUT -p udp -s TYPE_YOUR_SOLARWINDS_IP_OR_NAME --dport 161 -j ACCEPT -m comment --comment "SOLARWINDS GRANT"
-A INPUT -p udp -s TYPE_YOUR_SOLARWINDS_IP_OR_NAME --dport 162 -j ACCEPT -m comment --comment "SOLARWINDS GRANT"
-A INPUT -p icmp --icmp-type 8 -s TYPE_YOUR_SOLARWINDS_IP_OR_NAME -j ACCEPT -m comment --comment "SOLARWINDS GRANT"

### APPLICATION GRANTS ###
-A INPUT -p tcp -s TYPE_YOUR_SERVER_IP_OR_NAME --sport 80 -j ACCEPT -m comment --comment "XXX APPLICATION GRANT"
-A INPUT -p tcp -s TYPE_YOUR_SERVER_IP_OR_NAME --sport 443 -j ACCEPT -m comment --comment "XXX APPLICATION GRANT"

### THE FOLLOWING LINES MUST BE AT THE BOTTOM
### 1. ONLY DROPED PACKAGES ARE WRITTEN TO /var/log/iptables.log > tail -444f /var/log/iptables.log
### 2. With the --limit parameter, the logging time to the /var/log/iptables.log file can be selected as 2/second, 2/minute, 2/hour, 2/day as needed.

-N LOGGING
-A INPUT -j LOGGING
-A LOGGING -m limit --limit 2/minute -j LOG --log-prefix "IPTables-Dropped: " --log-level 4

-A INPUT -m state --state ESTABLISHED,RELATED -j ACCEPT
-A INPUT -j REJECT --reject-with icmp-host-prohibited
-A FORWARD -j REJECT --reject-with icmp-host-prohibited

-A LOGGING -j DROP
COMMIT
### THE LINES ABOVE MUST BE AT THE BOTTOM
[root@serhatcelik ~]#

Step By Step Oracle 19c Data Guard Installation on Oracle Linux 7.9

Oracle Data Guard ensures high availability, data protection, and disaster recovery for enterprise data. Oracle Data Guard provides a comprehensive set of services that create, maintain, manage, and monitor one or more standby databases to enable production Oracle databases to survive disasters and data corruptions. Oracle Data Guard maintains these standby databases as copies of the production database.Then, if the production database becomes unavailable because of a planned or an unplanned outage, Oracle Data Guard can switch any standby database to the production role, minimizing the downtime associated with the outage. Oracle Data Guard can be used with traditional backup, restoration, and cluster techniques to provide a high level of data protection and data availability.

With Oracle Data Guard, administrators can optionally improve production database performance by offloading resource-intensive backup and reporting operations to standby systems.

Types of standby databases are Physical standby database, Logical standby database and Snapshot standby database.

I will demonstrate Physical standby database as working real time apply at this document. Most used type is Physical standby database type. You can investigate other types of standby database from Oracle docs.

I used Oracle RAC database (on node1 and node2) as primary side and I used single instance database (on node-1) with asm file system as secondary side (Data Guard).

You can find primary side (Oracle 19c RAC) installation steps from below. I will use this infrastructure as primary/standby side.

Step By Step Oracle 19C RAC Installation on Oracle Linux 7.9 Part-1 OS

Step By Step Oracle 19C RAC Installation on Oracle Linux 7.9 Part-2 GRID

Step By Step Oracle 19C RAC Installation on Oracle Linux 7.9 Part-3 DATABASE

PRIMARY SIDE NODE-1

ADD NEW LISTENERS FOR DATA GUARD CONNECTION

[root@node1 ~]# su – oracle
Last login: Mon Jun 28 13:06:34 +03 2021 on pts/0
[oracle@node1 ~]$
[oracle@node1 ~]$ cd $ORACLE_HOME/network/admin
[oracle@node1 admin]$
[oracle@node1 admin]$ vi listener.ora

ADD BELOW ENTRIES TO THE END OF FILE.

# NODE1 PRIMARY DATABASE DATAGUARD LISTENER
LISTENER_FOR_PRIMARY_DATAGUARD=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(Host=node1-vip)(Port=1570)))
SID_LIST_LISTENER_FOR_PRIMARY_DATAGUARD=(SID_LIST=(SID_DESC=(SID_NAME=CDBTEST1)(ORACLE_HOME=/u01/app/oracle/database/19.3.0/dbhome_1)))

# NODE1 STANDBY DATABASE DATAGUARD LISTENER
LISTENER_FOR_STANDBY_DATAGUARD=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(Host=node1-vip)(Port=1580)))
SID_LIST_LISTENER_FOR_STANDBY_DATAGUARD=(SID_LIST=(SID_DESC=(SID_NAME=CDBTESTDG)(ORACLE_HOME=/u01/app/oracle/database/19.3.0/dbhome_1)))

START LISTENERS

START PRIMARY DATABASE DATAGUARD LISTENER

[oracle@node1 admin]$ lsnrctl start LISTENER_FOR_PRIMARY_DATAGUARD

LSNRCTL for Linux: Version 19.0.0.0.0 - Production on 28-JUN-2021 13:10:19

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

Starting /u01/app/oracle/database/19.3.0//dbhome_1/bin/tnslsnr: please wait...

TNSLSNR for Linux: Version 19.0.0.0.0 - Production
System parameter file is /u01/app/oracle/database/19.3.0//dbhome_1/network/admin/listener.ora
Log messages written to /u01/app/oracle/database/19.3.0/diag/tnslsnr/node1/listener_for_primary_dataguard/alert/log.xml
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=10.5.0.102)(PORT=1570)))

Connecting to (ADDRESS=(PROTOCOL=TCP)(Host=node1-vip)(Port=1570))
STATUS of the LISTENER

Alias                     LISTENER_FOR_PRIMARY_DATAGUARD
Version                   TNSLSNR for Linux: Version 19.0.0.0.0 - Production
Start Date                28-JUN-2021 13:10:19
Uptime                    0 days 0 hr. 0 min. 0 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/oracle/database/19.3.0//dbhome_1/network/admin/listener.ora
Listener Log File         /u01/app/oracle/database/19.3.0/diag/tnslsnr/node1/listener_for_primary_dataguard/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=10.5.0.102)(PORT=1570)))
Services Summary...
Service "CDBTEST1" has 1 instance(s).
  Instance "CDBTEST1", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully
[oracle@node1 admin]$

START STANDBY DATABASE DATAGUARD LISTENER

[oracle@node1 admin]$ lsnrctl start LISTENER_FOR_STANDBY_DATAGUARD

LSNRCTL for Linux: Version 19.0.0.0.0 - Production on 28-JUN-2021 13:11:16

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

Starting /u01/app/oracle/database/19.3.0//dbhome_1/bin/tnslsnr: please wait...

TNSLSNR for Linux: Version 19.0.0.0.0 - Production
System parameter file is /u01/app/oracle/database/19.3.0//dbhome_1/network/admin/listener.ora
Log messages written to /u01/app/oracle/database/19.3.0/diag/tnslsnr/node1/listener_for_standby_dataguard/alert/log.xml
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=10.5.0.102)(PORT=1580)))

Connecting to (ADDRESS=(PROTOCOL=TCP)(Host=node1-vip)(Port=1580))
STATUS of the LISTENER

Alias                     LISTENER_FOR_STANDBY_DATAGUARD
Version                   TNSLSNR for Linux: Version 19.0.0.0.0 - Production
Start Date                28-JUN-2021 13:11:16
Uptime                    0 days 0 hr. 0 min. 0 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/oracle/database/19.3.0//dbhome_1/network/admin/listener.ora
Listener Log File         /u01/app/oracle/database/19.3.0/diag/tnslsnr/node1/listener_for_standby_dataguard/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=10.5.0.102)(PORT=1580)))
Services Summary...
Service "CDBTESTDG" has 1 instance(s).
  Instance "CDBTESTDG", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully
[oracle@node1 admin]$

CONFIGURE TNSNAMES.ORA FOR DATA GUARD CONNECTION

[root@node1 ~]# su – oracle
Last login: Fri Jun 25 15:10:13 +03 2021
[oracle@node1 ~]$
[oracle@node1 ~]$ cd $ORACLE_HOME/network/admin
[oracle@node1 admin]$
[oracle@node1 admin]$ vi tnsnames.ora

ADD BELOW ENTRIES TO THE END OF FILE

PRIMARY_CDBTEST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = node1-vip)(PORT = 1570))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = CDBTEST1)
)
)

STANDBY_CDBTEST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = node1-vip)(PORT = 1580))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = CDBTESTDG)
)
)

PRIMARY SIDE NODE-2

[root@node2 ~]# su – oracle
Last login: Thu Jul 1 16:35:54 +03 2021 on pts/0
[oracle@node2 ~]$
[oracle@node2 ~]$ cd $ORACLE_HOME/network/admin
[oracle@node2 admin]$
[oracle@node2 admin]$ vi listener.ora

ADD BELOW ENTRIES TO THE END OF FILE

# NODE2 PRIMARY DATABASE DATAGUARD LISTENER
LISTENER_FOR_PRIMARY_DATAGUARD=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(Host=node2-vip)(Port=1570)))
SID_LIST_LISTENER_FOR_PRIMARY_DATAGUARD=(SID_LIST=(SID_DESC=(SID_NAME=CDBTEST2)(ORACLE_HOME=/u01/app/oracle/database/19.3.0/dbhome_1)))

START PRIMARY DATABASE DATAGUARD LISTENER

[oracle@node2 admin]$ lsnrctl start LISTENER_FOR_PRIMARY_DATAGUARD

LSNRCTL for Linux: Version 19.0.0.0.0 - Production on 01-JUL-2021 16:39:11

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

Starting /u01/app/oracle/database/19.3.0//dbhome_1/bin/tnslsnr: please wait...

TNSLSNR for Linux: Version 19.0.0.0.0 - Production
System parameter file is /u01/app/oracle/database/19.3.0//dbhome_1/network/admin/listener.ora
Log messages written to /u01/app/oracle/database/19.3.0/diag/tnslsnr/node2/listener_for_primary_dataguard/alert/log.xml
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=10.5.0.103)(PORT=1570)))

Connecting to (ADDRESS=(PROTOCOL=TCP)(Host=node2-vip)(Port=1570))
STATUS of the LISTENER

Alias                     LISTENER_FOR_PRIMARY_DATAGUARD
Version                   TNSLSNR for Linux: Version 19.0.0.0.0 - Production
Start Date                01-JUL-2021 16:39:11
Uptime                    0 days 0 hr. 0 min. 0 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/oracle/database/19.3.0//dbhome_1/network/admin/listener.ora
Listener Log File         /u01/app/oracle/database/19.3.0/diag/tnslsnr/node2/listener_for_primary_dataguard/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=10.5.0.103)(PORT=1570)))
Services Summary...
Service "CDBTEST2" has 1 instance(s).
  Instance "CDBTEST2", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully
[oracle@node2 admin]$

CONFIGURE TNSNAMES.ORA FOR DATA GUARD CONNECTION

[root@node2 ~]# su – oracle
Last login: Thu Jul 1 16:36:25 +03 2021 on pts/0
[oracle@node2 ~]$
[oracle@node2 ~]$ cd $ORACLE_HOME/network/admin
[oracle@node2 admin]$
[oracle@node2 admin]$ vi tnsnames.ora

ADD BELOW ENTRIES TO THE END OF FILE

PRIMARY_CDBTEST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = node2-vip)(PORT = 1570))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = CDBTEST2)
)
)

STANDBY_CDBTEST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = node1-vip)(PORT = 1580))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = CDBTESTDG)
)
)

CONFIGURE PARAMETERS ON PRIMARY SIDE

[oracle@node1 ~]$ sqlplus sys/welcome1234@CDBTEST as sysdba

SQL*Plus: Release 19.0.0.0.0 – Production on Fri Jun 25 17:18:06 2021
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 log_mode from gv$database;

LOG_MODE
ARCHIVELOG
ARCHIVELOG

SQL> alter database force logging;

Database altered.

SQL> alter system set db_file_name_convert=’+DATA/CDBTESTDG/DATAFILE’,’+DATA/CDBTEST/DATAFILE’,’+FRA/CDBTESTDG/DATAFILE’,’+FRA/CDBTEST/DATAFILE’ scope=spfile;

System altered.

SQL> alter system set log_file_name_convert=’+DATA/CDBTESTDG/ONLINELOG’,’+DATA/CDBTEST/ONLINELOG’,’+FRA/CDBTESTDG/ONLINELOG’,’+FRA/CDBTEST/ONLINELOG’ scope=spfile;

System altered.

SQL> alter system set log_archive_config=’DG_CONFIG=(CDBTEST,CDBTESTDG)’;

System altered.

SQL> alter system set log_archive_dest_1=’LOCATION=USE_DB_RECOVERY_FILE_DEST VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=CDBTEST’;

System altered.

SQL> alter system set log_archive_dest_2=’SERVICE=STANDBY_CDBTEST VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=CDBTESTDG’;

System altered.

SQL> alter system set log_archive_dest_state_2=’ENABLE’;

System altered.

SQL> alter system set log_archive_format=’%t_%s_%r.arc’ scope=spfile;

System altered.

SQL> alter system set fal_client=’PRIMARY_CDBTEST’;

System altered.

SQL> alter system set fal_server=’STANDBY_CDBTEST’;

System altered.

SQL> alter system set standby_file_management=’AUTO’;

System altered.

SQL> alter system set remote_login_passwordfile=’exclusive’ scope=spfile;

System altered.

SQL> alter database flashback on;

Database altered.

Create standby redo logs on the RAC primary database to support the standby role. Standby Redo Log (SRL) files must be the same size as your online redo log (ORL) files, and you also need to have the same number of SRL files as you do ORL files, plus one. If you have a RAC primary, you need “plus one” per RAC instance. These files need to be created on your standby as well as on your primary in preparation for switchover. We must consider having single member in each group so that avoid waits with commit for each transaction in each member. We no need to create standby redo log files on standby and Oracle take cares of it during RMAN duplicate.

CHECK ONLINE REDO LOGS

SQL> select group#,thread#,bytes from v$log;

    GROUP#    THREAD#      BYTES
         1          1  209715200
         2          1  209715200
         3          2  209715200
         4          2  209715200

CHECK STANDBY REDO LOGS

SQL> select group#,thread#,bytes from v$standby_log;

no rows selected

ADD STANDBY REDO LOGS

ALTER DATABASE ADD STANDBY LOGFILE THREAD 1 (‘+FRA’) SIZE 200M;
ALTER DATABASE ADD STANDBY LOGFILE THREAD 1 (‘+FRA’) SIZE 200M;
ALTER DATABASE ADD STANDBY LOGFILE THREAD 1 (‘+FRA’) SIZE 200M;
ALTER DATABASE ADD STANDBY LOGFILE THREAD 2 (‘+FRA’) SIZE 200M;
ALTER DATABASE ADD STANDBY LOGFILE THREAD 2 (‘+FRA’) SIZE 200M;
ALTER DATABASE ADD STANDBY LOGFILE THREAD 2 (‘+FRA’) SIZE 200M;

CHECK STANDBY REDO LOGS

SQL> select group#,thread#,bytes from v$standby_log;

    GROUP#    THREAD#      BYTES
         5          1  209715200
         6          1  209715200
         7          1  209715200
         8          2  209715200
         9          2  209715200
        10          2  209715200

6 rows selected.

CREATE PFILE FROM SPFILE

SQL> create pfile=’/tmp/pfile.ora’ from spfile;

File created.

[oracle@node1 ~]$ more /tmp/pfile.ora
CDBTEST2.__data_transfer_cache_size=0
CDBTEST1.__data_transfer_cache_size=0
CDBTEST2.__db_cache_size=2550136832
CDBTEST1.__db_cache_size=1979711488
CDBTEST2.__inmemory_ext_roarea=0
CDBTEST1.__inmemory_ext_roarea=0
CDBTEST2.__inmemory_ext_rwarea=0
CDBTEST1.__inmemory_ext_rwarea=0
CDBTEST2.__java_pool_size=0
CDBTEST1.__java_pool_size=0
CDBTEST2.__large_pool_size=16777216
CDBTEST1.__large_pool_size=16777216
CDBTEST1.__oracle_base='/u01/app/oracle/database/19.3.0'#ORACLE_BASE set from environment
CDBTEST2.__oracle_base='/u01/app/oracle/database/19.3.0'#ORACLE_BASE set from environment
CDBTEST2.__pga_aggregate_target=1275068416
CDBTEST1.__pga_aggregate_target=1275068416
CDBTEST2.__sga_target=3791650816
CDBTEST1.__sga_target=3791650816
CDBTEST2.__shared_io_pool_size=134217728
CDBTEST1.__shared_io_pool_size=134217728
CDBTEST2.__shared_pool_size=1073741824
CDBTEST1.__shared_pool_size=1644167168
CDBTEST2.__streams_pool_size=0
CDBTEST1.__streams_pool_size=0
CDBTEST2.__unified_pga_pool_size=0
CDBTEST1.__unified_pga_pool_size=0
*.audit_file_dest='/u01/app/oracle/database/19.3.0/admin/CDBTEST/adump'
*.audit_trail='db'
*.cluster_database=true
*.compatible='19.0.0'
*.control_files='+DATA/CDBTEST/CONTROLFILE/current.269.1065044601','+FRA/CDBTEST/CONTROLFILE/current.256.1065044603'
*.db_block_size=8192
*.db_create_file_dest='+DATA'
*.db_file_name_convert='+DATA/CDBTESTDG/DATAFILE','+DATA/CDBTEST/DATAFILE','+FRA/CDBTESTDG/DATAFILE','+FRA/CDBTEST/DATAFILE'
*.db_name='CDBTEST'
*.db_recovery_file_dest='+FRA'
*.db_recovery_file_dest_size=21474836480
*.diagnostic_dest='/u01/app/oracle/database/19.3.0'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=CDBTESTXDB)'
*.enable_pluggable_database=true
*.fal_client='PRIMARY_CDBTEST'
*.fal_server='STANDBY_CDBTEST'
family:dw_helper.instance_mode='read-only'
CDBTEST2.instance_number=2
CDBTEST1.instance_number=1
*.local_listener='(ADDRESS=(PROTOCOl=TCP)(HOST=192.168.63.34)(PORT=1521))'
CDBTEST1.local_listener='(DESCRIPTION= (ADDRESS_LIST= (ADDRESS=(PROTOCOL=TCP)(HOST=node1-vip)(PORT=1550))))'
CDBTEST2.local_listener='(DESCRIPTION= (ADDRESS_LIST= (ADDRESS=(PROTOCOL=TCP)(HOST=node2-vip)(PORT=1550))))'
*.log_archive_config='DG_CONFIG=(CDBTEST,CDBTESTDG)'
*.log_archive_dest_1='LOCATION=USE_DB_RECOVERY_FILE_DEST VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=CDBTEST'
*.log_archive_dest_2='SERVICE=STANDBY_CDBTEST VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=CDBTESTDG'
*.log_archive_dest_state_2='ENABLE'
*.log_archive_format='%t_%s_%r.arc'
*.log_file_name_convert='+DATA/CDBTESTDG/ONLINELOG','+DATA/CDBTEST/ONLINELOG','+FRA/CDBTESTDG/ONLINELOG','+FRA/CDBTEST/ONLINELOG'
*.nls_language='AMERICAN'
*.nls_territory='AMERICA'
*.open_cursors=300
*.pga_aggregate_target=1204m
*.processes=300
*.remote_login_passwordfile='exclusive'
*.sga_target=3612m
*.standby_file_management='AUTO'
CDBTEST2.thread=2
CDBTEST1.thread=1
CDBTEST2.undo_tablespace='UNDOTBS2'
CDBTEST1.undo_tablespace='UNDOTBS1'
[oracle@node1 ~]$

CREATE PASSWORD FILE

[oracle@node1 ~]$ orapwd file=$ORACLE_HOME/dbs/orapwCDBTEST
Enter password for SYS:
[oracle@node1 ~]$

CONFIGURE PARAMETERS ON STANDBY SIDE

CREATE PASSWORD FILE

[oracle@node1 ~]$ cd $ORACLE_HOME/dbs
[oracle@node1 dbs]$
[oracle@node1 dbs]$ ls -lrt
total 21312
-rw-r--r-- 1 oracle oinstall 3079 May 14 2015 init.ora
-rw-r----- 1 oracle asmadmin 19709952 Feb 20 22:22 snapcf_CDBTEST1.f
-rw-rw---- 1 oracle asmadmin 1544 Jun 27 13:12 hc_CDBTEST1.dat
-rw-r----- 1 oracle oinstall 6144 Jun 27 13:19 orapwCDBTEST
-rw-r----- 1 oracle asmadmin 2097152 Jun 27 13:23 id_CDBTEST1.dat
[oracle@node1 dbs]$
[oracle@node1 dbs]$ cp orapwCDBTEST orapwCDBTESTDG
[oracle@node1 dbs]$
[oracle@node1 dbs]$ ls -lrt
total 21320
-rw-r--r-- 1 oracle oinstall 3079 May 14 2015 init.ora
-rw-r----- 1 oracle asmadmin 19709952 Feb 20 22:22 snapcf_CDBTEST1.f
-rw-rw---- 1 oracle asmadmin 1544 Jun 27 13:12 hc_CDBTEST1.dat
-rw-r----- 1 oracle oinstall 6144 Jun 27 13:19 orapwCDBTEST
-rw-r----- 1 oracle asmadmin 2097152 Jun 27 13:23 id_CDBTEST1.dat
-rw-r----- 1 oracle oinstall 6144 Jun 27 13:23 orapwCDBTESTDG
[oracle@node1 dbs]$

EDIT PFILE LIKE BELOW

CHANGE *.audit_file_dest AS *.audit_file_dest='/u01/app/oracle/database/19.3.0/admin/CDBTESTDG/adump'
CHANGE *.cluster_database AS *.cluster_database=false
COMMENT *.control_files PARAMETER
CHANGE *.control_files AS *.control_files='+DATA/CDBTESTDG/CONTROLFILE/current.269.1065044601','+FRA/CDBTESTDG/CONTROLFILE/current.256.1065044603'
CHANGE *.db_file_name_convert AS *.db_file_name_convert='+DATA/CDBTEST/DATAFILE','+DATA/CDBTESTDG/DATAFILE','+FRA/CDBTEST/DATAFILE','+FRA/CDBTESTDG/DATAFILE'
ADD *.db_unique_name after *.db_name AS *.db_unique_name='CDBTESTDG'
CHANGE *.dispatchers AS *.dispatchers='(PROTOCOL=TCP) (SERVICE=CDBTESTDGXDB)'
CHANGE *.fal_client AS *.fal_client='STANDBY_CDBTEST'
CHANGE *.fal_server AS *.fal_server='PRIMARY_CDBTEST'
CHANGE *.log_archive_dest_1 AS *.log_archive_dest_1='LOCATION=USE_DB_RECOVERY_FILE_DEST VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=CDBTESTDG'
CHANGE *.log_archive_dest_2 AS *.log_archive_dest_2='SERVICE=PRIMARY_CDBTEST VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=CDBTEST'
CHANGE *.log_file_name_convert AS *.log_file_name_convert='+DATA/CDBTEST/ONLINELOG','+DATA/CDBTESTDG/ONLINELOG','+FRA/CDBTEST/ONLINELOG','+FRA/CDBTESTDG/ONLINELOG'
CHANGE *.undo_tablespace AS *.undo_tablespace='UNDOTBS1'

NOTE: REMOVE TWO INITIALIZATION PARAMETERS (CONTROL_FILES & CLUSTER_DATABASE PARAMETERS)

[oracle@node1 ~]$ vi /tmp/pfile.ora

*.__data_transfer_cache_size=0
*.__db_cache_size=2550136832
*.__inmemory_ext_roarea=0
*.__inmemory_ext_rwarea=0
*.__java_pool_size=0
*.__large_pool_size=16777216
*.__oracle_base='/u01/app/oracle/database/19.3.0'#ORACLE_BASE set from environment
*.__pga_aggregate_target=1275068416
*.__sga_target=3791650816
*.__shared_io_pool_size=134217728
*.__shared_pool_size=1073741824
*.__streams_pool_size=0
*.__unified_pga_pool_size=0
*.audit_file_dest='/u01/app/oracle/database/19.3.0/admin/CDBTESTDG/adump'
*.audit_trail='db'
*.cluster_database=false
*.compatible='19.0.0'
*.db_block_size=8192
*.db_create_file_dest='+DATA'
*.db_file_name_convert='+DATA/CDBTEST/DATAFILE','+DATA/CDBTESTDG/DATAFILE','+FRA/CDBTEST/DATAFILE','+FRA/CDBTESTDG/DATAFILE'
*.db_name='CDBTEST'
*.db_unique_name='CDBTESTDG'
*.db_recovery_file_dest='+FRA'
*.db_recovery_file_dest_size=21474836480
*.diagnostic_dest='/u01/app/oracle/database/19.3.0'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=CDBTESTDGXDB)'
*.enable_pluggable_database=true
*.fal_client='STANDBY_CDBTEST'
*.fal_server='PRIMARY_CDBTEST'
family:dw_helper.instance_mode='read-only'
*.local_listener='(ADDRESS=(PROTOCOl=TCP)(HOST=192.168.63.34)(PORT=1521))'
*.log_archive_config='DG_CONFIG=(CDBTEST,CDBTESTDG)'
*.log_archive_dest_1='LOCATION=USE_DB_RECOVERY_FILE_DEST VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=CDBTESTDG'
*.log_archive_dest_2='SERVICE=PRIMARY_CDBTEST VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=CDBTEST'
*.log_archive_dest_state_2='ENABLE'
*.log_archive_format='%t_%s_%r.arc'
*.log_file_name_convert='+DATA/CDBTEST/ONLINELOG','+DATA/CDBTESTDG/ONLINELOG','+FRA/CDBTEST/ONLINELOG','+FRA/CDBTESTDG/ONLINELOG'
*.nls_language='AMERICAN'
*.nls_territory='AMERICA'
*.open_cursors=300
*.pga_aggregate_target=1204m
*.processes=300
*.remote_login_passwordfile='exclusive'
*.sga_target=3612m
*.standby_file_management='AUTO'
*.undo_tablespace='UNDOTBS1'

CREATE AUDIT_FILE_DEST DIRECTORIES ON BOTH NODE

[oracle@node1 ~]$ mkdir -p /u01/app/oracle/database/19.3.0/admin/CDBTESTDG/adump
[oracle@node1 ~]$

[oracle@node2 ~]$ mkdir -p /u01/app/oracle/database/19.3.0/admin/CDBTESTDG/adump
[oracle@node2 ~]$

CREATE SPFILE FROM PFILE AND NOMOUNT THE DATABASE

[oracle@node1 ~]$ export ORACLE_SID=CDBTESTDG
[oracle@node1 ~]$ export ORACLE_BASE=/u01/app/oracle/database/19.3.0/
[oracle@node1 ~]$ export ORACLE_HOME=/u01/app/oracle/database/19.3.0/dbhome_1
[oracle@node1 ~]$ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Mon Jun 28 14:41:17 2021
Version 19.3.0.0.0

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

Connected to an idle instance.

SQL> create spfile from pfile='/tmp/pfile.ora';

File created.

SQL> startup nomount;
ORACLE instance started.

Total System Global Area 3791650128 bytes
Fixed Size                  9141584 bytes
Variable Size            1090519040 bytes
Database Buffers         2684354560 bytes
Redo Buffers                7634944 bytes
SQL>

TEST PRIMARY CONNECTION

[oracle@node1 ~]$ sqlplus sys/welcome1234@PRIMARY_CDBTEST as sysdba

SQL*Plus: Release 19.0.0.0.0 – Production on Mon Jun 28 14:18:07 2021
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

TEST STANDBY CONNECTION

[oracle@node1 ~]$ sqlplus sys/welcome1234@STANDBY_CDBTEST as sysdba

SQL*Plus: Release 19.0.0.0.0 – Production on Mon Jun 28 14:19:52 2021
Version 19.3.0.0.0

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

Connected to an idle instance.

INITIALIZE DATA GUARD

[oracle@node1 ~]$ rman target sys@PRIMARY_CDBTEST auxiliary sys@STANDBY_CDBTEST

Recovery Manager: Release 19.0.0.0.0 – Production on Mon Jun 28 14:46:45 2021
Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.

target database Password:
connected to target database: CDBTEST (DBID=807098105)
auxiliary database Password:
connected to auxiliary database: CDBTEST (not mounted)

RMAN> duplicate target database for standby from active database nofilenamecheck;

Starting Duplicate Db at 28-JUN-21
using target database control file instead of recovery catalog
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=265 device type=DISK

contents of Memory Script:
{
   backup as copy reuse
   passwordfile auxiliary format  '/u01/app/oracle/database/19.3.0/dbhome_1/dbs/orapwCDBTESTDG'   ;
}
executing Memory Script

Starting backup at 28-JUN-21
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=60 instance=CDBTEST1 device type=DISK
Finished backup at 28-JUN-21

contents of Memory Script:
{
   sql clone "create spfile from memory";
   shutdown clone immediate;
   startup clone nomount;
   restore clone from service  'PRIMARY_CDBTEST' standby controlfile;
}
executing Memory Script

sql statement: create spfile from memory

Oracle instance shut down

connected to auxiliary database (not started)
Oracle instance started

Total System Global Area    3791650128 bytes

Fixed Size                     9141584 bytes
Variable Size               1090519040 bytes
Database Buffers            2684354560 bytes
Redo Buffers                   7634944 bytes

Starting restore at 28-JUN-21
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=241 device type=DISK

channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: using network backup set from service PRIMARY_CDBTEST
channel ORA_AUX_DISK_1: restoring control file
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:04
output file name=+DATA/CDBTESTDG/CONTROLFILE/current.289.1076424217
output file name=+FRA/CDBTESTDG/CONTROLFILE/current.373.1076424217
Finished restore at 28-JUN-21

contents of Memory Script:
{
   sql clone 'alter database mount standby database';
}
executing Memory Script

sql statement: alter database mount standby database
RMAN-05529: warning: DB_FILE_NAME_CONVERT resulted in invalid ASM names; names changed to disk group only.
RMAN-05158: WARNING: auxiliary (datafile) file name +DATA/CDBTEST/86B637B62FE07A65E053F706E80A27CA/DATAFILE/system.273.1065045091 conflicts with a file used by the target database
RMAN-05158: WARNING: auxiliary (datafile) file name +DATA/CDBTEST/86B637B62FE07A65E053F706E80A27CA/DATAFILE/sysaux.274.1065045091 conflicts with a file used by the target database
RMAN-05158: WARNING: auxiliary (datafile) file name +DATA/CDBTEST/86B637B62FE07A65E053F706E80A27CA/DATAFILE/undotbs1.275.1065045091 conflicts with a file used by the target database
RMAN-05158: WARNING: auxiliary (datafile) file name +DATA/CDBTEST/BBCA5CB2D7B645F0E0536500050AD2E1/DATAFILE/system.282.1065046403 conflicts with a file used by the target database
RMAN-05158: WARNING: auxiliary (datafile) file name +DATA/CDBTEST/BBCA5CB2D7B645F0E0536500050AD2E1/DATAFILE/sysaux.283.1065046403 conflicts with a file used by the target database
RMAN-05158: WARNING: auxiliary (datafile) file name +DATA/CDBTEST/BBCA5CB2D7B645F0E0536500050AD2E1/DATAFILE/undotbs1.281.1065046403 conflicts with a file used by the target database
RMAN-05158: WARNING: auxiliary (datafile) file name +DATA/CDBTEST/BBCA5CB2D7B645F0E0536500050AD2E1/DATAFILE/undo_2.285.1065046437 conflicts with a file used by the target database
RMAN-05158: WARNING: auxiliary (datafile) file name +DATA/CDBTEST/BBCA5CB2D7B645F0E0536500050AD2E1/DATAFILE/users.286.1065046441 conflicts with a file used by the target database
RMAN-05158: WARNING: auxiliary (tempfile) file name +DATA/CDBTEST/TEMPFILE/temp.272.1065044625 conflicts with a file used by the target database
RMAN-05158: WARNING: auxiliary (tempfile) file name +DATA/CDBTEST/BBCA0E021130536CE0536400050A9CDC/TEMPFILE/temp.276.1065045131 conflicts with a file used by the target database
RMAN-05158: WARNING: auxiliary (tempfile) file name +DATA/CDBTEST/BBCA5CB2D7B645F0E0536500050AD2E1/TEMPFILE/temp.284.1065046413 conflicts with a file used by the target database

contents of Memory Script:
{
   set newname for tempfile  1 to
 "+DATA";
   set newname for tempfile  2 to
 "+DATA";
   set newname for tempfile  3 to
 "+DATA";
   switch clone tempfile all;
   set newname for datafile  1 to
 "+DATA";
   set newname for datafile  3 to
 "+DATA";
   set newname for datafile  4 to
 "+DATA";
   set newname for datafile  5 to
 "+DATA";
   set newname for datafile  6 to
 "+DATA";
   set newname for datafile  7 to
 "+DATA";
   set newname for datafile  8 to
 "+DATA";
   set newname for datafile  9 to
 "+DATA";
   set newname for datafile  10 to
 "+DATA";
   set newname for datafile  11 to
 "+DATA";
   set newname for datafile  12 to
 "+DATA";
   set newname for datafile  13 to
 "+DATA";
   set newname for datafile  14 to
 "+DATA";
   restore
   from  nonsparse   from service
 'PRIMARY_CDBTEST'   clone database
   ;
   sql 'alter system archive log current';
}
executing Memory Script

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

renamed tempfile 1 to +DATA in control file
renamed tempfile 2 to +DATA in control file
renamed tempfile 3 to +DATA in control file

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

Starting restore at 28-JUN-21
using channel ORA_AUX_DISK_1

channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: using network backup set from service PRIMARY_CDBTEST
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00001 to +DATA
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:26
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: using network backup set from service PRIMARY_CDBTEST
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00003 to +DATA
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:35
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: using network backup set from service PRIMARY_CDBTEST
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00004 to +DATA
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:08
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: using network backup set from service PRIMARY_CDBTEST
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00005 to +DATA
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:08
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: using network backup set from service PRIMARY_CDBTEST
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00006 to +DATA
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:07
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: using network backup set from service PRIMARY_CDBTEST
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00007 to +DATA
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:02
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: using network backup set from service PRIMARY_CDBTEST
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00008 to +DATA
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:03
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: using network backup set from service PRIMARY_CDBTEST
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00009 to +DATA
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:04
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: using network backup set from service PRIMARY_CDBTEST
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00010 to +DATA
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:07
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: using network backup set from service PRIMARY_CDBTEST
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00011 to +DATA
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:07
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: using network backup set from service PRIMARY_CDBTEST
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00012 to +DATA
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:04
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: using network backup set from service PRIMARY_CDBTEST
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00013 to +DATA
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:03
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: using network backup set from service PRIMARY_CDBTEST
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00014 to +DATA
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:02
Finished restore at 28-JUN-21

sql statement: alter system archive log current

contents of Memory Script:
{
   switch clone datafile all;
}
executing Memory Script

datafile 1 switched to datafile copy
input datafile copy RECID=17 STAMP=1076424354 file name=+DATA/CDBTESTDG/DATAFILE/system.288.1076424231
datafile 3 switched to datafile copy
input datafile copy RECID=18 STAMP=1076424354 file name=+DATA/CDBTESTDG/DATAFILE/sysaux.258.1076424257
datafile 4 switched to datafile copy
input datafile copy RECID=19 STAMP=1076424354 file name=+DATA/CDBTESTDG/DATAFILE/undotbs1.292.1076424293
datafile 5 switched to datafile copy
input datafile copy RECID=20 STAMP=1076424354 file name=+DATA/CDBTESTDG/BBCA0E021130536CE0536400050A9CDC/DATAFILE/system.293.1076424301
datafile 6 switched to datafile copy
input datafile copy RECID=21 STAMP=1076424355 file name=+DATA/CDBTESTDG/BBCA0E021130536CE0536400050A9CDC/DATAFILE/sysaux.294.1076424309
datafile 7 switched to datafile copy
input datafile copy RECID=22 STAMP=1076424355 file name=+DATA/CDBTESTDG/DATAFILE/users.295.1076424317
datafile 8 switched to datafile copy
input datafile copy RECID=23 STAMP=1076424355 file name=+DATA/CDBTESTDG/BBCA0E021130536CE0536400050A9CDC/DATAFILE/undotbs1.296.1076424319
datafile 9 switched to datafile copy
input datafile copy RECID=24 STAMP=1076424355 file name=+DATA/CDBTESTDG/DATAFILE/undotbs2.297.1076424321
datafile 10 switched to datafile copy
input datafile copy RECID=25 STAMP=1076424355 file name=+DATA/CDBTESTDG/BBCA5CB2D7B645F0E0536500050AD2E1/DATAFILE/system.298.1076424325
datafile 11 switched to datafile copy
input datafile copy RECID=26 STAMP=1076424355 file name=+DATA/CDBTESTDG/BBCA5CB2D7B645F0E0536500050AD2E1/DATAFILE/sysaux.299.1076424333
datafile 12 switched to datafile copy
input datafile copy RECID=27 STAMP=1076424355 file name=+DATA/CDBTESTDG/BBCA5CB2D7B645F0E0536500050AD2E1/DATAFILE/undotbs1.300.1076424341
datafile 13 switched to datafile copy
input datafile copy RECID=28 STAMP=1076424355 file name=+DATA/CDBTESTDG/BBCA5CB2D7B645F0E0536500050AD2E1/DATAFILE/undo_2.301.1076424343
datafile 14 switched to datafile copy
input datafile copy RECID=29 STAMP=1076424355 file name=+DATA/CDBTESTDG/BBCA5CB2D7B645F0E0536500050AD2E1/DATAFILE/users.302.1076424347
Finished Duplicate Db at 28-JUN-21

RMAN> alter system register;

Statement processed

RMAN> exit

Recovery Manager complete.
[oracle@node1 ~]$

START DATA GUARD AS ACTIVE DATA GUARD

[oracle@node1 ~]$ export ORACLE_SID=CDBTESTDG
[oracle@node1 ~]$ export ORACLE_BASE=/u01/app/oracle/database/19.3.0/
[oracle@node1 ~]$ export ORACLE_HOME=/u01/app/oracle/database/19.3.0/dbhome_1
[oracle@node1 ~]$ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 – Production on Tue Jun 29 10:55:58 2021
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> alter database open read only;

Database altered.

SQL> alter database recover managed standby database disconnect from session;

Database altered.

SQL> select name, open_mode, database_role, log_mode from v$database;

NAME      OPEN_MODE            DATABASE_ROLE    LOG_MODE
CDBTEST   READ ONLY WITH APPLY PHYSICAL STANDBY ARCHIVELOG

NOTE: In order to cancel MRP (Media Recovery Process), below command can be used.

SQL> alter database recover managed standby database cancel;

CHECK PRIMARY DATABASE ARCHIVELOG

[oracle@node1 ~]$ sqlplus sys/welcome1234@PRIMARY_CDBTEST as sysdba

SQL*Plus: Release 19.0.0.0.0 – Production on Tue Jun 29 08:21:22 2021
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 thread#,max(sequence#) from v$archived_log group by thread#;

   THREAD# MAX(SEQUENCE#)
         1            107
         2            100

CREATE MANUALLY ARCHIVE LOG.

SQL> alter system archive log current;

System altered.

SQL> /

System altered.

SQL> /

System altered.

SQL> /

System altered.

SQL> select thread#,max(sequence#) from v$archived_log group by thread#;

   THREAD# MAX(SEQUENCE#)
---------- --------------
         1            111
         2            104

CHECK STANDBY DATABASE ARCHIVELOG

[oracle@node1 ~]$ export ORACLE_SID=CDBTESTDG
[oracle@node1 ~]$ export ORACLE_BASE=/u01/app/oracle/database/19.3.0/
[oracle@node1 ~]$ export ORACLE_HOME=/u01/app/oracle/database/19.3.0/dbhome_1
[oracle@node1 ~]$ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 – Production on Tue Jun 29 10:55:58 2021
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 thread#,max(sequence#) from v$archived_log where applied=’YES’ group by thread#;

   THREAD# MAX(SEQUENCE#)
---------- --------------
         1            111
         2            104

TESTING THE DATA GUARD CONFIGURATION BETWEEN PRIMARY DATABASE AND PHYSICAL STANDBY DATABASE.

Create objects from primary database instance and check those objects in physical standby database.

CREATE OBJECTS IN PRIMARY DATABASE

[oracle@node1 ~]$ sqlplus sys/welcome1234@PRIMARY_CDBTEST as sysdba

SQL*Plus: Release 19.0.0.0.0 – Production on Tue Jun 29 11:08:07 2021
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> CREATE TABLE SYS.TEST_NODE1_CDBTEST1 (ID NUMBER, NAME VARCHAR2(100));

Table created.

SQL> INSERT INTO SYS.TEST_NODE1_CDBTEST1 VALUES (1, ‘NODE1_CDBTEST1’);

1 row created.

SQL> COMMIT;

Commit complete.

SQL> set linesize 999;
SQL>

SQL> SELECT * FROM SYS.TEST_NODE1_CDBTEST1;

        ID NAME
---------- ----------------------------------------------------------------------------------------------------
         1 NODE1_CDBTEST1

!!! Create manually archive log in order to see the row on physical standby database. Because just one row is inserted !!!

SQL> alter system archive log current;

System altered.

CHECKING THE OBJECT FROM PHYSICAL STANDBY DATABASE

[oracle@node1 ~]$ export ORACLE_SID=CDBTESTDG
[oracle@node1 ~]$ export ORACLE_BASE=/u01/app/oracle/database/19.3.0/
[oracle@node1 ~]$ export ORACLE_HOME=/u01/app/oracle/database/19.3.0/dbhome_1
[oracle@node1 ~]$ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 – Production on Tue Jun 29 10:55:58 2021
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 name, open_mode, database_role, log_mode from v$database;

NAME      OPEN_MODE            DATABASE_ROLE    LOG_MODE
--------- -------------------- ---------------- ------------
CDBTEST   READ ONLY WITH APPLY PHYSICAL STANDBY ARCHIVELOG

SQL> SELECT * FROM SYS.TEST_NODE1_CDBTEST1;

        ID NAME
---------- ----------------------------------------------------------------------------------------------------
         1 NODE1_CDBTEST1

CREATE DATAGUARD BROKER CONFIGURATION

PRIMARY DATABASE

SET DG_BROKER_START AS FALSE

[oracle@node1 ~]$ sqlplus sys/welcome1234@PRIMARY_CDBTEST as sysdba

SQL*Plus: Release 19.0.0.0.0 – Production on Wed Jun 30 10:02:24 2021
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> show parameter dg_broker;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
dg_broker_config_file1               string      /u01/app/oracle/database/19.3.
                                                 0/dbhome_1/dbs/dr1CDBTEST.dat
dg_broker_config_file2               string      /u01/app/oracle/database/19.3.
                                                 0/dbhome_1/dbs/dr2CDBTEST.dat
dg_broker_start                      boolean     TRUE
SQL>
SQL> alter system set dg_broker_start=false scope=both;

System altered.

SQL>
SQL> show parameter dg_broker;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
dg_broker_config_file1               string      /u01/app/oracle/database/19.3.
                                                 0/dbhome_1/dbs/dr1CDBTEST.dat
dg_broker_config_file2               string      /u01/app/oracle/database/19.3.
                                                 0/dbhome_1/dbs/dr2CDBTEST.dat
dg_broker_start                      boolean     FALSE
SQL>

CREATE DIRECTORIES FOR DATAGUARD BROKER ON ASM

[root@node1 ~]# su – grid
Last login: Wed Jun 30 10:19:14 +03 2021
[grid@node1 ~]$
[grid@node1 ~]$ asmcmd
ASMCMD>
ASMCMD> mkdir +DATA/CDBTEST/DATAGUARDCONFIG
ASMCMD>
ASMCMD> mkdir +FRA/CDBTEST/DATAGUARDCONFIG

SET CONFIG FILE PARAMETERS

SQL> ALTER SYSTEM SET DG_BROKER_CONFIG_FILE1=’+DATA/CDBTEST/DATAGUARDCONFIG/dgb_config01.ora’ SCOPE=BOTH sid=’*’;

System altered.

SQL> ALTER SYSTEM SET DG_BROKER_CONFIG_FILE2=’+FRA/CDBTEST/DATAGUARDCONFIG/dgb_config02.ora’ SCOPE=BOTH sid=’*’;

System altered.

SQL> show parameter dg_broker;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
dg_broker_config_file1               string      +DATA/CDBTEST/DATAGUARDCONFIG/
                                                 dgb_config01.ora
dg_broker_config_file2               string      +FRA/CDBTEST/DATAGUARDCONFIG/d
                                                 gb_config02.ora
dg_broker_start                      boolean     FALSE

SET DG_BROKER_START AS TRUE

SQL> alter system set dg_broker_start=true scope=both;

System altered.

SQL> show parameter dg_broker;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
dg_broker_config_file1               string      +DATA/CDBTEST/DATAGUARDCONFIG/
                                                 dgb_config01.ora
dg_broker_config_file2               string      +FRA/CDBTEST/DATAGUARDCONFIG/d
                                                 gb_config02.ora
dg_broker_start                      boolean     TRUE

PHYSICAL STANDBY DATABASE

[oracle@node1 ~]$ export ORACLE_SID=CDBTESTDG
[oracle@node1 ~]$ export ORACLE_BASE=/u01/app/oracle/database/19.3.0/
[oracle@node1 ~]$ export ORACLE_HOME=/u01/app/oracle/database/19.3.0/dbhome_1
[oracle@node1 ~]$ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 – Production on Wed Jun 30 12:21:39 2021
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> show parameter dg_broker;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
dg_broker_config_file1               string      /u01/app/oracle/database/19.3.
                                                 0/dbhome_1/dbs/dr1CDBTESTDG.da
                                                 t
dg_broker_config_file2               string      /u01/app/oracle/database/19.3.
                                                 0/dbhome_1/dbs/dr2CDBTESTDG.da
                                                 t
dg_broker_start                      boolean     TRUE


SQL> alter system set dg_broker_start=false scope=both;

System altered.

SQL> show parameter dg_broker;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
dg_broker_config_file1               string      /u01/app/oracle/database/19.3.
                                                 0/dbhome_1/dbs/dr1CDBTESTDG.da
                                                 t
dg_broker_config_file2               string      /u01/app/oracle/database/19.3.
                                                 0/dbhome_1/dbs/dr2CDBTESTDG.da
                                                 t
dg_broker_start                      boolean     FALSE

CREATE DIRECTORIES FOR DATAGUARD BROKER ON ASM

[root@node1 ~]# su – grid
Last login: Wed Jun 30 10:19:14 +03 2021
[grid@node1 ~]$
[grid@node1 ~]$ asmcmd
ASMCMD>
ASMCMD> mkdir +DATA/CDBTESTDG/DATAGUARDCONFIG
ASMCMD>
ASMCMD> mkdir +FRA/CDBTESTDG/DATAGUARDCONFIG

SET CONFIG FILE PARAMETERS

SQL> ALTER SYSTEM SET DG_BROKER_CONFIG_FILE1=’+DATA/CDBTESTDG/DATAGUARDCONFIG/dgb_config01.ora’ SCOPE=BOTH sid=’*’;

System altered.

SQL> ALTER SYSTEM SET DG_BROKER_CONFIG_FILE2=’+FRA/CDBTESTDG/DATAGUARDCONFIG/dgb_config02.ora’ SCOPE=BOTH sid=’*’;

System altered.

SQL> show parameter dg_broker;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
dg_broker_config_file1               string      +DATA/CDBTESTDG/DATAGUARDCONFI
                                                 G/dgb_config01.ora
dg_broker_config_file2               string      +FRA/CDBTESTDG/DATAGUARDCONFIG
                                                 /dgb_config02.ora
dg_broker_start                      boolean     FALSE

SET DG_BROKER_START AS TRUE

SQL> alter system set dg_broker_start=true scope=both;

System altered.

SQL> show parameter dg_broker;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
dg_broker_config_file1               string      +DATA/CDBTESTDG/DATAGUARDCONFI
                                                 G/dgb_config01.ora
dg_broker_config_file2               string      +FRA/CDBTESTDG/DATAGUARDCONFIG
                                                 /dgb_config02.ora
dg_broker_start                      boolean     TRUE

CREATE CONFIGURATION FOR DATAGUARD BROKER USING DGMGRL ON PRIMARY

[oracle@node1 ~]$ dgmgrl
DGMGRL for Linux: Release 19.0.0.0.0 – Production on Wed Jun 30 11:54:05 2021
Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.

Welcome to DGMGRL, type “help” for information.
DGMGRL>
DGMGRL> connect sys/welcome1234@PRIMARY_CDBTEST;
Connected to “CDBTEST”
Connected as SYSDBA.
DGMGRL>

DGMGRL> remove configuration;

DGMGRL> create configuration CDBTEST_DGB as primary database is CDBTEST connect identifier is PRIMARY_CDBTEST;
Configuration “cdbtest_dgb” created with primary database “cdbtest”

DGMGRL> show configuration;

Configuration – cdbtest_dgb

Protection Mode: MaxPerformance
Members:
cdbtest – Primary database

Fast-Start Failover: Disabled

Configuration Status:
DISABLED

DGMGRL> add database CDBTESTDG as connect identifier is STANDBY_CDBTEST maintained as physical;
Database “cdbtestdg” added

DGMGRL> show configuration;

Configuration – cdbtest_dgb

Protection Mode: MaxPerformance
Members:
cdbtest – Primary database
cdbtestdg – Physical standby database

Fast-Start Failover: Disabled

Configuration Status:
DISABLED

DGMGRL> enable configuration;
Enabled.

DGMGRL> show configuration;

Configuration – cdbtest_dgb

Protection Mode: MaxPerformance
Members:
cdbtest – Primary database
cdbtestdg – Physical standby database

Fast-Start Failover: Disabled

Configuration Status:
SUCCESS (status updated 34 seconds ago)

PERFORMING SWITCHOVER ACTIVITY FROM PRIMARY DATABASE TO PHYSICAL STANDBY DATABASE USING DGMGRL PROMPT. LOGIN TO PRIMARY DATABASE AND CHECK THE VALIDITY OF THE CDBTEST AND CDBTESTDG INSTANCES FOR SWITCHOVER ACTIVITY.

[oracle@node1 ~]$ dgmgrl
DGMGRL for Linux: Release 19.0.0.0.0 – Production on Fri Jul 2 09:01:01 2021
Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.

Welcome to DGMGRL, type “help” for information.
DGMGRL>
DGMGRL> connect sys/welcome1234@PRIMARY_CDBTEST;
Connected to “CDBTEST”
Connected as SYSDBA.
DGMGRL>
DGMGRL> show configuration;

Configuration – cdbtest_dgb

Protection Mode: MaxPerformance
Members:
cdbtest – Primary database
cdbtestdg – Physical standby database

Fast-Start Failover: Disabled

Configuration Status:
SUCCESS (status updated 28 seconds ago)

DGMGRL> validate database cdbtest;

Database Role: Primary database

Ready for Switchover: Yes

Managed by Clusterware:
cdbtest: YES

DGMGRL>
DGMGRL> validate database cdbtestdg;

Database Role: Physical standby database
Primary Database: cdbtest

Ready for Switchover: Yes
Ready for Failover: Yes (Primary Running)

Flashback Database Status:
cdbtest : On
cdbtestdg: Off

Capacity Information:
Database Instances Threads
cdbtest 2 2
cdbtestdg 1 2
Warning: the target standby has fewer instances than the
primary database, this may impact application performance

Managed by Clusterware:
cdbtest : YES
cdbtestdg: NO

PERFORMING SWITCHOVER FROM CDBTEST TO CDBTESTDG

DGMGRL> switchover to CDBTESTDG;
Performing switchover NOW, please wait…
Operation requires a connection to database “cdbtestdg”
Connecting …
Connected to “CDBTESTDG”
Connected as SYSDBA.
New primary database “cdbtestdg” is opening…
Oracle Clusterware is restarting database “cdbtest” …
Connected to an idle instance.
Connected to an idle instance.
Connected to an idle instance.
Connected to an idle instance.
Connected to an idle instance.
Connected to an idle instance.
Connected to “CDBTEST”
Connected to “CDBTEST”
Switchover succeeded, new primary is “cdbtestdg”

LOGIN TO NEW PRIMARY DATABASE (CDBTESTDG) AND CHECK THE CONFIGURATION STATUS FROM DGMGRL PROMPT.

[oracle@node1 ~]$ export ORACLE_SID=CDBTESTDG
[oracle@node1 ~]$ export ORACLE_BASE=/u01/app/oracle/database/19.3.0/
[oracle@node1 ~]$ export ORACLE_HOME=/u01/app/oracle/database/19.3.0/dbhome_1
[oracle@node1 ~]$
[oracle@node1 ~]$ dgmgrl
DGMGRL for Linux: Release 19.0.0.0.0 – Production on Fri Jul 2 10:55:01 2021
Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.

Welcome to DGMGRL, type “help” for information.
DGMGRL>
DGMGRL> connect sys/welcome1234@STANDBY_CDBTEST;
Connected to “CDBTESTDG”
Connected as SYSDBA.
DGMGRL>
DGMGRL> show configuration;

Configuration – cdbtest_dgb

Protection Mode: MaxPerformance
Members:
cdbtestdg – Primary database
cdbtest – Physical standby database

Fast-Start Failover: Disabled

Configuration Status:
SUCCESS (status updated 43 seconds ago)

PERFORM SWITCHOVER ACTIVITY FROM NEW PRIMARY DATABASE (CDBTESTDG) TO NEW PHYSICAL STANDBY DATABASE (CDBTEST) BEFORE THAT CHECK THE VALIDITY OF THE CDBTESTDG AND CDBTEST INSTANCES FOR SWITCHOVER ACTIVITY.

[oracle@node1 ~]$ export ORACLE_SID=CDBTESTDG
[oracle@node1 ~]$ export ORACLE_BASE=/u01/app/oracle/database/19.3.0/
[oracle@node1 ~]$ export ORACLE_HOME=/u01/app/oracle/database/19.3.0/dbhome_1
[oracle@node1 ~]$
[oracle@node1 ~]$ dgmgrl
DGMGRL for Linux: Release 19.0.0.0.0 – Production on Fri Jul 2 12:34:03 2021
Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.

Welcome to DGMGRL, type “help” for information.
DGMGRL>
DGMGRL> connect sys/welcome1234@STANDBY_CDBTEST;
Connected to “CDBTESTDG”
Connected as SYSDBA.
DGMGRL>
DGMGRL> show configuration;

Configuration – cdbtest_dgb

Protection Mode: MaxPerformance
Members:
cdbtestdg – Primary database
cdbtest – Physical standby database

Fast-Start Failover: Disabled

Configuration Status:
SUCCESS (status updated 42 seconds ago)

DGMGRL> validate database cdbtestdg;

Database Role: Primary database

Ready for Switchover: Yes

Flashback Database Status:
cdbtestdg: Off

Managed by Clusterware:
cdbtestdg: NO
Validating static connect identifier for the primary database cdbtestdg…
Unable to connect to database using (DESCRIPTION=(ADDRESS=(PROTOCOl=TCP)(HOST=192.168.63.34)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=CDBTESTDG_DGMGRL)(INSTANCE_NAME=CDBTESTDG)(SERVER=DEDICATED)(STATIC_SERVICE=TRUE)))
ORA-12543: TNS:destination host unreachable

Failed.
Warning: Ensure primary database’s StaticConnectIdentifier property
is configured properly so that the primary database can be restarted
by DGMGRL after switchover

DGMGRL>
DGMGRL> validate database cdbtest;

Database Role: Physical standby database
Primary Database: cdbtestdg

Ready for Switchover: Yes
Ready for Failover: Yes (Primary Running)

Flashback Database Status:
cdbtestdg: Off
cdbtest : On

Managed by Clusterware:
cdbtestdg: NO
cdbtest : YES
Validating static connect identifier for the primary database cdbtestdg…
Unable to connect to database using (DESCRIPTION=(ADDRESS=(PROTOCOl=TCP)(HOST=192.168.63.34)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=CDBTESTDG_DGMGRL)(INSTANCE_NAME=CDBTESTDG)(SERVER=DEDICATED)(STATIC_SERVICE=TRUE)))
ORA-12543: TNS:destination host unreachable

Failed.
Warning: Ensure primary database’s StaticConnectIdentifier property
is configured properly so that the primary database can be restarted
by DGMGRL after switchover

IN ORDER TO GET RID OF ABOVE ERROR

DGMGRL> edit database ‘CDBTESTDG’ set property StaticConnectIdentifier='(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=node1-vip)(PORT=1580))(CONNECT_DATA=(SERVICE_NAME=CDBTESTDG)(INSTANCE_NAME=CDBTESTDG)(SERVER=DEDICATED)))’;
Property “staticconnectidentifier” updated

DGMGRL> validate database CDBTESTDG;

Database Role: Primary database

Ready for Switchover: Yes

Flashback Database Status:
cdbtestdg: Off

Managed by Clusterware:
cdbtestdg: NO
Validating static connect identifier for the primary database cdbtestdg…
The static connect identifier allows for a connection to database “cdbtestdg”.

DGMGRL> validate database CDBTEST;

Database Role: Physical standby database
Primary Database: cdbtestdg

Ready for Switchover: Yes
Ready for Failover: Yes (Primary Running)

Flashback Database Status:
cdbtestdg: Off
cdbtest : On

Managed by Clusterware:
cdbtestdg: NO
cdbtest : YES
Validating static connect identifier for the primary database cdbtestdg…
The static connect identifier allows for a connection to database “cdbtestdg”.

DGMGRL> switchover to CDBTEST;
Performing switchover NOW, please wait…
Operation requires a connection to database “cdbtest”
Connecting …
Connected to “CDBTEST”
Connected as SYSDBA.
New primary database “cdbtest” is opening…
Operation requires start up of instance “CDBTESTDG” on database “cdbtestdg”
Starting instance “CDBTESTDG”…
Connected to an idle instance.
ORACLE instance started.
Connected to “CDBTESTDG”
Database mounted.
Database opened.
Connected to “CDBTESTDG”
Switchover succeeded, new primary is “cdbtest”

DGMGRL> show configuration;

Configuration – cdbtest_dgb

Protection Mode: MaxPerformance
Members:
cdbtest – Primary database
cdbtestdg – Physical standby database

Fast-Start Failover: Disabled

Configuration Status:
SUCCESS (status updated 8 seconds ago)

How to Configure Two Network Cards for Different Subnets on Linux?

Following this tutorial, you can run two network interface cards (NIC) that each have an IP address in a different network or subnet in the same time.

If you do not complete below steps, 10.10.1.100 and 10.10.2.100 ip addresses will not be accessible in the same time.

NETWORK INTERFACE CARDS INFORMATION AND CONFIGURATION

em1 > 10.10.1.0 NETWORK > 10.10.1.100 IPADDR > 255.255.255.0 NETMASK > 10.10.1.1 GATEWAY

em2 > 10.10.2.0 NETWORK > 10.10.2.100 IPADDR > 255.255.255.0 NETMASK > 10.10.2.1 GATEWAY

[root@serhatcelik ~]# more /etc/sysconfig/network-scripts/ifcfg-em1
DEVICE=em1
HWADDR=00:11:22:33:44:55
TYPE=Ethernet
UUID=a2cggh87-h6k8-4jk3-87i6-f1g7b7f5g743
ONBOOT=yes
NM_CONTROLLED=no
BOOTPROTO=none
IPADDR=10.10.1.100
NETMASK=255.255.255.0
GATEWAY=10.10.1.1
DNS1=”type_your_dns_server_1″
DNS2=”type_your_dns_server_2″
DOMAIN=”type_your_domain_name”
[root@serhatcelik ~]#

[root@serhatcelik ~]# more /etc/sysconfig/network-scripts/ifcfg-em2
DEVICE=em2
HWADDR=66:77:88:99:00:11
TYPE=Ethernet
UUID=9j86887s-j842-45ll-8u1o-f1h6d20b2m18
ONBOOT=yes
NM_CONTROLLED=no
BOOTPROTO=no
IPADDR=10.10.2.100
NETMASK=255.255.255.0
DNS1=”type_your_dns_server_1″
DNS2=”type_your_dns_server_2″
DOMAIN=”type_your_domain_name”
[root@serhatcelik ~]#

NOTE: IN THE SAMETIME, ONE DEFAULT GATEWAY CAN BE SET ON SERVER. SO, DO NOT ENTER DEFAULT GATEWAY FOR EM2 INTERFACE.

THE QUICK AND DIRTY SOLUTION – ASYMMETRIC ROUTING

NOTE: Default Value for net.ipv4.conf.default.rp_filter is 1.

TEMPORARY

Set net.ipv4.conf.all.rp_filter parameter as 2.

[root@serhatcelik ~]# sysctl net.ipv4.conf.all.rp_filter=2
net.ipv4.conf.all.rp_filter = 2
[root@serhatcelik ~]#

PERMANENT

Edit /etc/sysctl.conf and change the value for rp_filter:

[root@serhatcelik network-scripts]# more /etc/sysctl.conf | grep rp_filter
net.ipv4.conf.default.rp_filter = 2

THE BEST SOLUTION – SYMMETRIC ROUTING

TEMPORARY

[root@serhatcelik ~]# ip route add 10.10.1.0/24 dev em1 tab 1
[root@serhatcelik ~]# ip route add 10.10.2.0/24 dev em2 tab 2
[root@serhatcelik ~]# ip route add default via 10.10.1.1 dev em1 tab 1
[root@serhatcelik ~]# ip route add default via 10.10.2.1 dev em2 tab 2

[root@serhatcelik ~]# ip rule add from 10.10.1.0/24 tab 1 priority 100
[root@serhatcelik ~]# ip rule add from 10.10.2.0/24 tab 2 priority 200
[root@serhatcelik ~]# ip route flush cache

PERMANENT

[root@serhatcelik ~]# cat /etc/sysconfig/network-scripts/route-em1
10.10.1.0/24 dev em1 tab 1
default via 10.10.1.1 dev em1 tab 1
[root@serhatcelik ~]#

[root@serhatcelik ~]# cat /etc/sysconfig/network-scripts/route-em2
10.10.2.0/24 dev em2 tab 2
default via 10.10.2.1 dev em2 tab 2
[root@serhatcelik ~]#

[root@serhatcelik ~]# cat /etc/sysconfig/network-scripts/rule-em1
from 10.10.1.0/24 tab 1 priority 100
[root@serhatcelik ~]#

[root@serhatcelik ~]# cat /etc/sysconfig/network-scripts/rule-em2
from 10.10.2.0/24 tab 2 priority 200
[root@serhatcelik ~]#

Finally, 10.10.1.100 and 10.10.2.100 ip addresses are accessible in the same time :):):)

How to Find Biggest Objects in Oracle Database?

SELECT DS.OWNER,
DS.SEGMENT_TYPE,
DS.SEGMENT_NAME,
CASE DS.SEGMENT_TYPE
WHEN ‘TABLE’ THEN DS.SEGMENT_NAME
WHEN ‘INDEX’ THEN DI.TABLE_NAME
WHEN ‘LOBSEGMENT’ THEN DL.TABLE_NAME
WHEN ‘LOBINDEX’ THEN DI.TABLE_NAME
END
TABLE_NAME,
NVL (DL.COLUMN_NAME, ‘—–‘)
COLUMN_NAME,
NVL (DI.INDEX_NAME, ‘—–‘)
INDEX_NAME,
DS.BYTES / 1024 / 1024 / 1024
SIZE_IN_GB
FROM DBA_SEGMENTS DS
LEFT JOIN DBA_LOBS DL
ON DS.OWNER || DS.SEGMENT_NAME = DL.OWNER || DL.SEGMENT_NAME
LEFT JOIN DBA_INDEXES DI
ON DS.OWNER || DS.SEGMENT_NAME = DI.OWNER || DI.INDEX_NAME
WHERE DS.SEGMENT_TYPE IN (‘TABLE’,
‘INDEX’,
‘LOBSEGMENT’,
‘LOBINDEX’)
ORDER BY DS.BYTES DESC

How to Recover an Expired Oracle User Account?

/* CHECK EXPIRED ORACLE ACCOUNT */
SELECT USERNAME, ACCOUNT_STATUS, EXPIRY_DATE FROM DBA_USERS WHERE USERNAME = ‘TYPE_YOUR_EXPIRED_ORACLE_ACCOUNT’;

/* TAKE EXPIRED ORACLE ACCOUNT CREATE SCRIPT */
SELECT DBMS_METADATA.GET_DDL (‘USER’, ‘TYPE_YOUR_EXPIRED_ORACLE_ACCOUNT’) FROM DUAL;

/* CONVERT CREATE COMMAND TO ALTER COMMAND AND RUN ALTER COMMAND */

/* CREATE COMMAND */
CREATE USER “TYPE_YOUR_EXPIRED_ORACLE_ACCOUNT” IDENTIFIED BY VALUES ‘XXXXXXXYYYYYYYYYYYYZZZZZZZZZZZZZ’ DEFAULT TABLESPACE “TYPE_USER_DEFAULT_TABLESPACE” TEMPORARY TABLESPACE “TYPE_USER_TEMP_TABLESPACE”

/* ALTER COMMAND */
ALTER USER “TYPE_YOUR_EXPIRED_ORACLE_ACCOUNT” IDENTIFIED BY VALUES ‘XXXXXXXYYYYYYYYYYYYZZZZZZZZZZZZZ’

/* TO ALTER THE PASSWORD EXPIRY POLICY FOR A CERTAIN USER PROFILE IN ORACLE FIRST CHECK WHICH PROFILE THE USER IS USING */
SELECT PROFILE FROM DBA_USERS WHERE USERNAME = ‘TYPE_YOUR_EXPIRED_ORACLE_ACCOUNT’;

/* CHECK AND NOTE THE CURRENT PASSWORD_LIFE_TIME LIMIT */
SELECT RESOURCE_NAME,LIMIT FROM DBA_PROFILES WHERE PROFILE=’TYPE_USER_PROFILE_NAME’ AND RESOURCE_NAME=’PASSWORD_LIFE_TIME’;

/* FINALLY YOU CAN CHANGE PASSWORD_LIFE_TIME LIMIT TO NEVER EXPIRE USING
THIS CAN BE DONE TEST OR DEV ENVIRONMENT. BE CAREFULL ON PRODUCTION ENVIRONMENT*/

ALTER PROFILE LIMIT PASSWORD_LIFE_TIME UNLIMITED;

How to Find Why and When a Object Became Invalid in Oracle Database?

In a typical running application, you would not expect to see views or stored procedures become invalid, because applications typically do not change table structures or change view or stored procedure definitions during normal execution. Changes to tables, views, or PL/SQL units typically occur when an application is patched or upgraded using a patch script or ad-hoc DDL statements. Dependent objects might be left invalid after a patch has been applied to change a set of referenced objects.

The following are some general rules for the invalidation of schema objects:

i) Between a referenced object and each of its dependent objects, the database tracks the elements of the referenced object that are involved in the dependency. For example, if a single-table view selects only a subset of columns in a table, only those columns are involved in the dependency. For each dependent of an object, if a change is made to the definition of any element involved in the dependency (including dropping the element), the dependent object is invalidated. Conversely, if changes are made only to definitions of elements that are not involved in the dependency, the dependent object remains valid.

ii)  In many cases, therefore, developers can avoid invalidation of dependent objects and unnecessary extra work for the database if they exercise care when changing schema objects.

iii) Dependent objects are cascade invalidated. If any object becomes invalid for any reason, all of that object’s dependent objects are immediately invalidated.

iv) If you revoke any object privileges on a schema object, dependent objects are cascade invalidated.

TO KNOW WHY AND WHEN OBJECT BECAME INVALID

DBA_DEPENDENCIES view can be used to check which objects depend on, then query DBA_OBJECTS to check the last_ddl_time for each of these dependant objects – that should give you an indication of which objects have changed and caused to be invalid as a result. Below SQLs can be used for investigation.

/* FIND ALL DEPENDENCY OF SPECIFIC OBJECT */
SELECT *
FROM DBA_DEPENDENCIES DP
WHERE DP.REFERENCED_OWNER NOT IN (‘SYS’, ‘SYSTEM’, ‘PUBLIC’)
AND DP.REFERENCED_TYPE != ‘NON-EXISTENT’
AND DP.NAME = ‘TYPE_YOUR_INVALIDATED_OBJECT_NAME’

/* FIND ALL DEPENDENCY OF SPECIFIC OBJECT HAS NOT DBLINK */
SELECT *
FROM DBA_DEPENDENCIES DP
WHERE DP.REFERENCED_OWNER NOT IN (‘SYS’, ‘SYSTEM’, ‘PUBLIC’)
AND DP.REFERENCED_TYPE != ‘NON-EXISTENT’
AND DP.REFERENCED_LINK_NAME IS NULL
AND DP.NAME = ‘TYPE_YOUR_INVALIDATED_OBJECT_NAME’

/* CHECK LAST_DDL_TIME OF DEPENDENT OBJECTS BELONG TO SPECIFIC OBJECT HAS NOT DBLINK */
SELECT DISTINCT DO.OWNER,
DO.OBJECT_TYPE,
DO.OBJECT_NAME,
DO.CREATED,
DO.LAST_DDL_TIME
FROM DBA_DEPENDENCIES DP, DBA_OBJECTS DO
WHERE DP.REFERENCED_OWNER = DO.OWNER
AND DP.REFERENCED_NAME = DO.OBJECT_NAME
AND DP.REFERENCED_OWNER NOT IN (‘SYS’, ‘SYSTEM’, ‘PUBLIC’)
AND DP.REFERENCED_TYPE != ‘NON-EXISTENT’
AND DP.REFERENCED_LINK_NAME IS NULL
AND DP.NAME = ‘TYPE_YOUR_INVALIDATED_OBJECT_NAME’
ORDER BY DO.LAST_DDL_TIME DESC

/* CHECK DBA_AUDIT_TRAIL TO FIND WHO RUN DDL COMMAND ON DEPENDENT OBJECTS */
SELECT *
FROM DBA_AUDIT_TRAIL DT
WHERE TIMESTAMP BETWEEN TO_DATE (’24/05/2021 23:00:00′,
‘DD/MM/YYYY HH24:MI:SS’)
AND TO_DATE (’24/05/2021 23:59:00′,
‘DD/MM/YYYY HH24:MI:SS’)
AND DT.OBJ_NAME IN
(‘TYPE_YOUR_OBJECT_NAME_RESULT_OF_ABOVE_SQL’)
ORDER BY 5

/* FIND ALL DEPENDENCY OF SPECIFIC OBJECT HAS DBLINK */
SELECT *
FROM DBA_DEPENDENCIES DP
WHERE DP.REFERENCED_OWNER NOT IN (‘SYS’, ‘SYSTEM’, ‘PUBLIC’)
AND DP.REFERENCED_TYPE != ‘NON-EXISTENT’
AND DP.REFERENCED_LINK_NAME IS NOT NULL
AND DP.NAME = ‘TYPE_YOUR_INVALIDATED_OBJECT_NAME’

/* CHECK LAST_DDL_TIME OF DEPENDENT OBJECTS BELONG TO SPECIFIC OBJECT HAS DBLINK */
SELECT DISTINCT DO.OWNER,
DO.OBJECT_TYPE,
DO.OBJECT_NAME,
DO.CREATED,
DO.LAST_DDL_TIME
FROM DBA_DEPENDENCIES DP, DBA_OBJECTS@WRITE_YOUR_DB_LINK_NAME.WORLD DO
WHERE DP.REFERENCED_OWNER = DO.OWNER
AND DP.REFERENCED_NAME = DO.OBJECT_NAME
AND DP.REFERENCED_OWNER NOT IN (‘SYS’, ‘SYSTEM’, ‘PUBLIC’)
AND DP.REFERENCED_TYPE != ‘NON-EXISTENT’
AND DP.REFERENCED_LINK_NAME IS NOT NULL
AND DP.NAME = ‘TYPE_YOUR_INVALIDATED_OBJECT_NAME’
ORDER BY DO.LAST_DDL_TIME DESC

/* CHECK DBA_AUDIT_TRAIL TO FIND WHO RUN DDL COMMAND ON DEPENDENT OBJECTS.
RUN THIS COMMAND ON THE DBLINK SOURCE DB */

SELECT *
FROM DBA_AUDIT_TRAIL DT
WHERE TIMESTAMP BETWEEN TO_DATE (’24/05/2021 23:00:00′,
‘DD/MM/YYYY HH24:MI:SS’)
AND TO_DATE (’24/05/2021 23:59:00′,
‘DD/MM/YYYY HH24:MI:SS’)
AND DT.OBJ_NAME IN
(‘TYPE_YOUR_OBJECT_NAME_RESULT_OF_ABOVE_SQL’)
ORDER BY 5

Useful Oracle 19c RAC Commands

WHAT IS THE DIFFERENCE BETWEEN SRVCTL AND CRSCTL?

SERVER CONTROL UTILITY (SRVCTL)

Server Control Utility (SRVCTL): It is used to administer Oracle Real Application Clusters (Oracle RAC) databases and instances.

Use SRVCTL to manage Oracle supplied resources such as:

  • Database
  • Listener
  • Instances
  • Disk groups
  • Networks

If resource name begins with ora then use SRVCTL. Oracle suggest DBAs to use srvctl command to stop/start the databases.

We can use the SRVCTL to manage configuration information.  Use SRVCTL commands to add, remove, start, stop, modify, enable, and disable a number of entities, such as Databases, instances, listeners, SCAN listeners, services, grid naming Service (GNS), and Oracle ASM.

CLUSTERWARE CONTROL UTILITY (CRSCTL)

Clusterware Control Utility (CRSCTL): It is used to managed oracle clusterware resources and components.

Use CRSCTL for managing Oracle Clusterware and its resources. CRSCTL command is used to manage the elements of the clusterware like crs, css, evm.

CRSCTL manages Oracle Clusterware related operations like:

  • Starting and stopping Oracle Clusterware
  • Enabling and disabling Oracle Clusterware daemons
  • Checking the health of the cluster
  • Registering cluster resources
SERVER CONTROL UTILITY (SRVCTL) – DATABASE RELATED COMMANDS

srvctl -help >>> List All Commands With Syntax

srvctl status listener -n node1 >>> Check Listener Status Of Specific Node
srvctl status listener >>> Check Listener Status Of All Nodes

srvctl start listener -n node1 >>> Start Listener On Specific Node
srvctl start listener >>> Start Listener On All Nodes

srvctl stop listener -n node1 >>> Stop Listener On Specific Node
srvctl stop listener >>> Stop Listener On All Nodes

srvctl status instance -d CDBTEST -i CDBTEST1 >>> Check Status Of Specific Instance
srvctl status database -d CDBTEST >>> Check Status Of All Instances

srvctl start instance -d CDBTEST -i CDBTEST1 >>> Starts Specific Instance
srvctl start database -d CDBTEST >>> Starts All Instances (+Open Database)

srvctl start database -d CDBTEST -o nomount >>> Starts Service As Nomount
srvctl start database -d CDBTEST -o mount >>> Starts Service As Mount
srvctl start database -d CDBTEST -o open >>> Starts Service As Open

srvctl stop instance -d CDBTEST -i CDBTEST1 >>> Stops Specific Instance
srvctl stop database -d CDBTEST >>> Stops All Instances (+Shutdown Database)

srvctl stop database -d CDBTEST -o normal >>> Stops Service As Normal
srvctl stop database -d CDBTEST -o immediate >>> Stops Service As Immediate
srvctl stop database -d CDBTEST -o transactional >>> Stops Service As Transactional
srvctl stop database -d CDBTEST -o abort >>> Stops Service As Abort

srvctl config database >>> Check Registered Databases In The Repository
srvctl config database -d CDBTEST >>> Check Specific Database Configuration

CLUSTERWARE CONTROL UTILITY (CRSCTL) – CLUSTER RELATED COMMANDS

crsctl config has >>> Check Oracle HAS (High Availabilty Services) Config
crsctl disable has >>> Disable Automatic Startup Of The Oracle High Availability Services
crsctl enable has >>> Enable Automatic Startup Of The Oracle High Availability Services

crsctl check crs >>> Check Oracle High Availability Services On The Local Server.
crsctl stop crs >>> Stop Oracle High Availability Services On The Local Server.
crsctl start crs >>> Start Oracle High Availability Services On The Local Server.

crsctl query crs softwareversion >>> Check Oracle Clusterware Software Version
crsctl query crs activeversion >>> Check Oracle Clusterware Acitve Version
crsctl query crs releaseversion >>> Check Oracle Clusterware Release Version

OTHER USEFUL COMMANDS

show parameter CLUSTER_DATABASE; >>> Check Database Running As Cluster Or Not.

select instance_name, host_name, archiver, thread#, status from gv$instance; >>> Check RAC Status.

SHUTDOWN SEQUENCE OF ONLY ONE NODE IN A CLUSTER

Check Database And ASM Instance
[root@node1 ~]# ps -ef | grep pmon | grep -v grep
grid 9501 1 0 May24 ? 00:00:04 asm_pmon_+ASM1
oracle 14761 1 0 08:54 ? 00:00:00 ora_pmon_CDBTEST1
[root@node1 ~]#

Check And Stop Instance
[root@node1 ~]# srvctl status instance -d CDBTEST -i CDBTEST1
[root@node1 ~]# srvctl stop instance -d CDBTEST -i CDBTEST1
[root@node1 ~]# srvctl status instance -d CDBTEST -i CDBTEST1

Check And Stop Oracle High Availability Services On The Local Server
[root@node1 ~]# crsctl check crs
[root@node1 ~]# crsctl stop crs
[root@node1 ~]# crsctl check crs

Check Database And ASM Instance
[root@node1 ~]# ps -ef | grep pmon | grep -v grep
[root@node1 ~]#

STARTUP SEQUENCE OF ONLY ONE NODE IN A CLUSTER

Check And Scan ASM Discs
[root@node1 ~]# ll /dev/oracleasm/disks/
[root@node1 ~]# oracleasm scandisks
[root@node1 ~]# ll /dev/oracleasm/disks/

Check And Start Oracle High Availability Services On The Local Server
[root@node1 ~]# crsctl check crs
[root@node1 ~]# crsctl start crs
[root@node1 ~]# crsctl check crs

Check And Start Instance
[root@node1 ~]# srvctl status instance -d CDBTEST -i CDBTEST1
[root@node1 ~]# srvctl start instance -d CDBTEST -i CDBTEST1
[root@node1 ~]# srvctl status instance -d CDBTEST -i CDBTEST1

Check Database And ASM Instance
[root@node1 ~]# ps -ef | grep pmon | grep -v grep
grid 9501 1 0 May24 ? 00:00:04 asm_pmon_+ASM1
oracle 14761 1 0 08:54 ? 00:00:00 ora_pmon_CDBTEST1
[root@node1 ~]#

ORA-19815: WARNING: db_recovery_file_dest_size is 100.00% used, and has 0 remaining bytes available.

One day when you want to open database, you can see ORA-19815 error in alert log and database can not be opened in rac environment. As a solution, database should be in mounted mode and db_recovery_file_dest_size should be increased.

CHECK AVAILABLE DISC SPACE ON ASM.

In order to check free disc space run below command.

DATABASE SHOULD BE IN MOUNT MODE.

[oracle@node1 ~]$ srvctl start database -d CDBTEST -o mount

INCREASE DB_RECOVERY_FILE_DEST_SIZE ACCORDING TO SITUATION.

SQL> ALTER SYSTEM SET DB_RECOVERY_FILE_DEST_SIZE = 20G SCOPE=BOTH SID=’*’;

After db_recovery_file_dest_size is increased, database can be opened normally. According to the situation, reclaim space on Flash Recovery Area can be done with below commands.

CHECK FLASH RECOVERY AREA USAGE.

SELECT * FROM V$FLASH_RECOVERY_AREA_USAGE;

SELECT NAME,
(SPACE_LIMIT / 1024 / 1024 / 1024) SPACE_LIMIT_GB,
((SPACE_LIMIT – SPACE_USED + SPACE_RECLAIMABLE) / 1024 / 1024 / 1024) AS SPACE_AVAILABLE_GB,
ROUND((SPACE_USED – SPACE_RECLAIMABLE) / SPACE_LIMIT * 100, 1) AS PERCENT_FULL
FROM V$RECOVERY_FILE_DEST;

CONNECT TO RMAN.

[oracle@node1 ~]$ rman

Recovery Manager: Release 19.0.0.0.0 – Production on Fri May 21 13:47:21 2021
Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.

RMAN> connect target sys/welcome123@CDBTEST

connected to target database: CDBTEST (DBID=807098105)

RMAN>

CHECK AND DELETE OBSOLETE BACKUPS.

RMAN> crosscheck backup;

using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=286 instance=CDBTEST2 device type=DISK
crosschecked backup piece: found to be ‘AVAILABLE’
backup piece handle=+FRA/CDBTEST/AUTOBACKUP/2021_02_20/s_1065046948.263.1065046949 RECID=1 STAMP=1065046948
Crosschecked 1 objects

RMAN> delete obsolete;

RMAN retention policy will be applied to the command
RMAN retention policy is set to redundancy 1
using channel ORA_DISK_1
no obsolete backups found

RMAN>

RECLAIM SPACE OF FILE TYPE IMAGE COPY

RMAN> delete noprompt copy of database;

released channel: ORA_DISK_1
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=286 instance=CDBTEST2 device type=DISK
specification does not match any datafile copy in the repository

RMAN>

RECLAIM SPACE OF FILE TYPE BACKUP PIECE

RMAN> delete noprompt backup of database;

using channel ORA_DISK_1
specification does not match any backup in the repository

RMAN>

RECLAIM SPACE OF ARCHIVE LOG.

It deletes all archive log without checking if backed up or not.

RMAN> delete noprompt archivelog all;

released channel: ORA_DISK_1
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=286 instance=CDBTEST2 device type=DISK
specification does not match any archived log in the repository

RMAN>

RECLAIM SPACE OF CONTROL FILE.

RMAN> crosscheck backup of controlfile;

using channel ORA_DISK_1
crosschecked backup piece: found to be ‘AVAILABLE’
backup piece handle=+FRA/CDBTEST/AUTOBACKUP/2021_02_20/s_1065046948.263.1065046949 RECID=1 STAMP=1065046948
Crosschecked 1 objects

RMAN> delete noprompt expired backup of controlfile;

using channel ORA_DISK_1
specification does not match any backup in the repository

RMAN>