Oracle 19c 2 node RAC to single instance standby database setup

Oracle 19c 2 node RAC to single instance standby database setup: In this article we will discuss about how to setup Oracle 19c 2 node RAC to single instance standby database step by step.

Environment Details for both (Primary and Standby):

Primary:

	Node-1:
	
	Platform	: Oracle Enterprise Linux Server release 8.5
	Server Name	: primary1.localdomain, IP: 192.168.0.112
	DB Version	: Oracle 19.16 2 node RAC
	File system     : +ASM1 (Automatic Storage Management)
	ORACLE_SID	: ORCL1
	Database Name	: ORCL
        DB_UNIQUE_NAME  : ORCL
	Oracle Home Path: /u01/app/oracle/product/19c/dbhome_1
	GRID Home Path	: /u02/app/grid/19c/grid_home


	Node-2:
	
	Platform	: Oracle Enterprise Linux Server release 8.5
	Server Name	: primary2.localdomain, IP: 192.168.0.113
	DB Version	: Oracle 19.16 2 node RAC
	File system     : +ASM2 (Automatic Storage Management)
	ORACLE_SID	: ORCL2
	Database Name	: ORCL
        DB_UNIQUE_NAME  : ORCL
	Oracle Home Path: /u01/app/oracle/product/19c/dbhome_1
	GRID Home Path	: /u02/app/grid/19c/grid_home

Standby:
		
	Platform	: Oracle Enterprise Linux Server release 8.5
	Server Name	: standby.localdomain, IP: 192.168.0.114
	DB Version	: Oracle 19.16 Standalone ASM
	File system     : +ASM (Automatic Storage Management)
	ORACLE_SID	: ORCLDR
	Database Name	: ORCL
        DB_UNIQUE_NAME  : ORCLDR
	Oracle Home Path: /u01/app/oracle/product/19c/dbhome_1
	GRID Home Path	: /u02/app/grid/19c/grid_home

Primary Server side Configurations:-

Make sure primary is in archivelog mode:

SQL> archive log list
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     748
Next log sequence to archive   749
Current log sequence           749
SQL>

Check FORCE LOGGING is enabled :

SQL> select force_logging from v$database;

FORCE_LOGGING
---------------------------------------
NO

SQL> alter database force logging;

Database altered.

SQL> select force_logging from v$database;

FORCE_LOGGING
---------------------------------------
YES

SQL>

Set Primary Database Initialization Parameters :

SQL>
SQL> show parameter name

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
cdb_cluster_name                     string
cell_offloadgroup_name               string
db_file_name_convert                 string
db_name                              string      ORCL
db_unique_name                       string      ORCL
global_names                         boolean     FALSE
instance_name                        string      ORCL1
lock_name_space                      string
log_file_name_convert                string
pdb_file_name_convert                string
processor_group_name                 string
service_names                        string      ORCL

SQL>
SQL>
SQL> archive log list
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     748
Next log sequence to archive   749
Current log sequence           749
SQL>
SQL> show parameter reco

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
control_file_record_keep_time        integer     7
db_recovery_file_dest                string      +RECO
db_recovery_file_dest_size           big integer 150G
db_unrecoverable_scn_tracking        boolean     TRUE
recovery_parallelism                 integer     0
remote_recovery_file_dest            string
SQL>

SQL> alter system set log_archive_config='dg_config=(ORCLDR,ORCL)' scope=both sid='*';

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=ORCL' scope=both;

System altered.

SQL> alter system set log_archive_dest_2='service=ORCLDR LGWR ASYNC valid_for=(online_logfiles,primary_role) db_unique_name=ORCLDR' sid='*';

System altered.

SQL> alter system set log_archive_dest_state_1='ENABLE' scope=both sid='*';

System altered.

SQL> alter system set log_archive_dest_state_2='ENABLE' scope=both sid='*';

System altered.

SQL> alter system set fal_server='ORCLDR' scope=both sid='*';

System altered.

SQL> alter system set fal_client='ORCL' scope=both sid='*';

System altered.

SQL> alter system set standby_file_management='AUTO' scope=both sid='*';

System altered.

SQL> alter system set remote_login_passwordfile=exclusive scope=spfile sid='*';

System altered.

SQL>

Configure Standby Redo Log on Primary :

SQL> select GROUP#, THREAD#, BYTES/1024/1024 from v$log;

    GROUP#    THREAD# BYTES/1024/1024
---------- ---------- ---------------
         1          1             200
         2          1             200
         3          2             200
         4          2             200

SQL> select member from v$logfile;

MEMBER
--------------------------------------------------------------------------------
+REDO01/ORCL/ONLINELOG/group_2.259.1109959635
+REDO02/ORCL/ONLINELOG/group_2.259.1109959635
+REDO01/ORCL/ONLINELOG/group_1.260.1109959635
+REDO02/ORCL/ONLINELOG/group_1.260.1109959635
+REDO01/ORCL/ONLINELOG/group_3.258.1109960109
+REDO02/ORCL/ONLINELOG/group_3.258.1109960109
+REDO01/ORCL/ONLINELOG/group_4.257.1109960109
+REDO02/ORCL/ONLINELOG/group_4.257.1109960109

8 rows selected.

SQL> alter database add standby logfile THREAD 1 group 5 ('+REDO01','+REDO02') size 200m;

Database altered.

SQL> alter database add standby logfile THREAD 1 group 6 ('+REDO01','+REDO02') size 200m;

Database altered.

SQL> alter database add standby logfile THREAD 1 group 7 ('+REDO01','+REDO02') size 200m;

Database altered.

SQL> alter database add standby logfile THREAD 2 group 8 ('+REDO01','+REDO02') size 200m;

Database altered.

SQL> alter database add standby logfile THREAD 2 group 9 ('+REDO01','+REDO02') size 200m;

Database altered.

SQL> alter database add standby logfile THREAD 2 group 10 ('+REDO01','+REDO02') size 200m;

Database altered.

SQL> select GROUP#, STATUS, TYPE, MEMBER from v$logfile;

    GROUP# STATUS  TYPE    MEMBER
---------- ------- ------- ------------------------------------------------------------
         2         ONLINE  +REDO01/ORCL/ONLINELOG/group_2.259.1109959635
         2         ONLINE  +REDO02/ORCL/ONLINELOG/group_2.259.1109959635
         1         ONLINE  +REDO01/ORCL/ONLINELOG/group_1.260.1109959635
         1         ONLINE  +REDO02/ORCL/ONLINELOG/group_1.260.1109959635
         3         ONLINE  +REDO01/ORCL/ONLINELOG/group_3.258.1109960109
         3         ONLINE  +REDO02/ORCL/ONLINELOG/group_3.258.1109960109
         4         ONLINE  +REDO01/ORCL/ONLINELOG/group_4.257.1109960109
         4         ONLINE  +REDO02/ORCL/ONLINELOG/group_4.257.1109960109
         5         STANDBY +REDO01/ORCL/ONLINELOG/group_5.261.1116416249
         5         STANDBY +REDO02/ORCL/ONLINELOG/group_5.261.1116416249
         6         STANDBY +REDO01/ORCL/ONLINELOG/group_6.262.1116416375
         6         STANDBY +REDO02/ORCL/ONLINELOG/group_6.262.1116416377
         7         STANDBY +REDO01/ORCL/ONLINELOG/group_7.263.1116416383
         7         STANDBY +REDO02/ORCL/ONLINELOG/group_7.263.1116416383
         8         STANDBY +REDO01/ORCL/ONLINELOG/group_8.264.1116416393
         8         STANDBY +REDO02/ORCL/ONLINELOG/group_8.264.1116416393
         9         STANDBY +REDO01/ORCL/ONLINELOG/group_9.265.1116416399
         9         STANDBY +REDO02/ORCL/ONLINELOG/group_9.265.1116416399
        10         STANDBY +REDO01/ORCL/ONLINELOG/group_10.266.1116416405
        10         STANDBY +REDO02/ORCL/ONLINELOG/group_10.266.1116416405

20 rows selected.

SQL>
SQL> select GROUP#, THREAD#, BYTES/1024/1024 from v$log;

    GROUP#    THREAD# BYTES/1024/1024
---------- ---------- ---------------
         1          1             200
         2          1             200
         3          2             200
         4          2             200

SQL>
SQL> select GROUP#, THREAD#, BYTES/1024/1024 from v$standby_log;

    GROUP#    THREAD# BYTES/1024/1024
---------- ---------- ---------------
         5          1             200
         6          1             200
         7          1             200
         8          2             200
         9          2             200
        10          2             200

6 rows selected.

SQL>

Take Primary database full RMAN backup plus archivelog and controlfile for standby format using below script:

run
{
allocate channel ch1 device type disk;
crosscheck archivelog all;
delete expired archivelog all;
crosscheck archivelog all;
backup as compressed backupset database format '/u02/backup/rman/Fullback_%T_%U'
plus archivelog format '/u02/backup/rman/Archive_%T_%U';
BACKUP CURRENT CONTROLFILE FOR STANDBY FORMAT '/u02/backup/rman/Controlback_%T_%U';
release channel ch1;
}
exit;

Configure network files for Primary:

Do the same tns entry for both node.

[oracle@primary1 admin]$
[oracle@primary1 admin]$ cat tnsnames.ora
# tnsnames.ora Network Configuration File: /u01/app/oracle/product/19c/dbhome_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.

ORCL =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = ora19c-scan)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = ORCL)
    )
  )

ORCLDR =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = standby.localdomain)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = ORCLDR)
    )
  )


-----------------------------------
Listener Entry:
-----------------------------------

[oracle@primary1 admin]$
[oracle@primary1 admin]$ cat /u02/app/grid/19c/grid_home/network/admin/listener.ora
LISTENER=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER))))            # line added by Agent
LISTENER_SCAN3=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER_SCAN3))))                # line added by Agent
LISTENER_SCAN2=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER_SCAN2))))                # line added by Agent
LISTENER_SCAN1=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER_SCAN1))))                # line added by Agent
ASMNET1LSNR_ASM=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=IPC)(KEY=ASMNET1LSNR_ASM))))              # line added by Agent
ENABLE_GLOBAL_DYNAMIC_ENDPOINT_ASMNET1LSNR_ASM=ON               # line added by Agent
VALID_NODE_CHECKING_REGISTRATION_ASMNET1LSNR_ASM=SUBNET         # line added by Agent
ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER_SCAN1=ON                # line added by Agent
VALID_NODE_CHECKING_REGISTRATION_LISTENER_SCAN1=OFF             # line added by Agent - Disabled by Agent because REMOTE_REGISTRATION_ADDRESS is set
ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER_SCAN2=ON                # line added by Agent
VALID_NODE_CHECKING_REGISTRATION_LISTENER_SCAN2=OFF             # line added by Agent - Disabled by Agent because REMOTE_REGISTRATION_ADDRESS is set
ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER_SCAN3=ON                # line added by Agent
VALID_NODE_CHECKING_REGISTRATION_LISTENER_SCAN3=OFF             # line added by Agent - Disabled by Agent because REMOTE_REGISTRATION_ADDRESS is set
ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER=ON              # line added by Agent
VALID_NODE_CHECKING_REGISTRATION_LISTENER=SUBNET                # line added by Agent
[oracle@primary1 admin]$

Copy password file and pfile from primary to standby server:

Copy password file from prod asm to filesystem:

ASMCMD [+DATA/ORCL/PASSWORD] >
ASMCMD [+DATA/ORCL/PASSWORD] > pwcopy pwdorcl.256.1109959525 /tmp/orapwORCLDR
copying +DATA/ORCL/PASSWORD/pwdorcl.256.1109959525 -> /tmp/orapwORCL
ASMCMD [+DATA/ORCL/PASSWORD] >
ASMCMD [+DATA/ORCL/PASSWORD] >

Transfer the password file to standby server:

scp /tmp/orapwORCLDR oracle@192.168.0.114:/tmp/


Copy password file from filesyatem to asm

ASMCMD [+DATA/ORCLDR/PASSWORD] >
ASMCMD [+DATA/ORCLDR/PASSWORD] > pwd
+DATA/ORCLDR/PASSWORD
ASMCMD [+DATA/ORCLDR/PASSWORD] >
ASMCMD [+DATA/ORCLDR/PASSWORD] > ls
ASMCMD [+DATA/ORCLDR/PASSWORD] >
ASMCMD [+DATA/ORCLDR/PASSWORD] > pwcopy /tmp/orapwORCLDR +DATA/ORCLDR/PASSWORD/pwdORCLDR
copying /tmp/orapwORCL -> +DATA/ORCLDR/PASSWORD/pwdORCLDR
ASMCMD [+DATA/ORCLDR/PASSWORD] >
ASMCMD [+DATA/ORCLDR/PASSWORD] > ls
pwdorcldr
ASMCMD [+DATA/ORCLDR/PASSWORD] >
ASMCMD [+DATA/ORCLDR/PASSWORD] >


Copy pfile from primary to standby server:

scp /tmp/initORCL.ora oracle@192.168.0.114:/u01/app/oracle/product/19c/dbhome_1/dbs/initORCLDR.ora

Copy backup pieces from primary to standby server:

cd /u01/rman_bkp/
scp * oracle@192.168.0.114:/u01/rman_bkp/

Standby Server side Configurations:-

Configure network files for standby:

[oracle@standby admin]$
[oracle@standby admin]$ cat tnsnames.ora
# tnsnames.ora Network Configuration File: /u01/app/oracle/product/19c/dbhome_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.

ORCL =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = ora19c-scan)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = ORCL)
    )
  )

ORCLDR =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = standby.localdomain)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = ORCLDR)
    )
  )


[oracle@standby admin]$
[oracle@standby admin]$
[oracle@standby admin]$
[oracle@standby admin]$ cat /u02/app/grid/19c/grid_home/network/admin/listener.ora
# listener.ora Network Configuration File: /u02/app/grid/19c/grid_home/network/admin/listener.ora
# Generated by Oracle configuration tools.

#Backup file is  /u02/app/grid/crsdata/standby/output/listener.ora.bak.standby.grid line added by Agent

VALID_NODE_CHECKING_REGISTRATION_LISTENER = ON

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = standby.localdomain)(PORT = 1521))
    )
  )

SID_LIST_LISTENER =
  (SID_LIST =
   (SID_DESC =
     (ORACLE_HOME=/u01/app/oracle/product/19c/dbhome_1)
      (SID_NAME=ORCLDR)
   )
  )


ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER = ON

[oracle@standby admin]$ 

Configure init parameter for standby:

DR pfile configuration:
=====================================

[oracle@standby dbs]$ cat /u01/app/oracle/product/19c/dbhome_1/dbs/initORCLDR.ora

*.audit_file_dest='/u01/app/oracle/admin/ORCLDR/adump'
*.cluster_database=true
*.compatible='19.0.0'
*.control_files='+REDO01/ORCLDR/CONTROLFILE/current.256.1109959633','+REDO02/ORCLDR/CONTROLFILE/current.256.1109959633'
*.db_block_size=8192
*.db_create_file_dest='+DATA'
*.db_create_online_log_dest_1='+REDO01'
*.db_create_online_log_dest_2='+REDO02'
*.db_name='ORCL'
*.db_unique_name='ORCLDR'
*.log_archive_max_processes=20
*.db_recovery_file_dest='+RECO'
*.db_recovery_file_dest_size=150g
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=ORCLDRXDB)'
*.fal_client='ORCLDR'
*.fal_server='ORCL'
family:dw_helper.instance_mode='read-only'
*.local_listener='-oraagent-dummy-'
*.log_archive_config='dg_config=(ORCLDR,ORCL)'
*.log_archive_dest_2='service=ORCL LGWR ASYNC valid_for=(online_logfiles,primary_role) db_unique_name=ORCL'
*.log_archive_dest_state_1='ENABLE'
*.log_archive_dest_state_2='ENABLE'
*.log_archive_format='%t_%s_%r.arc'
*.nls_language='AMERICAN'
*.nls_territory='AMERICA'
*.open_cursors=300
*.pga_aggregate_target=4096m
*.processes=1000
*.remote_login_passwordfile='EXCLUSIVE'
*.sga_target=6144m
*.standby_file_management='AUTO'
ORCLDR.undo_tablespace='UNDOTBS1'

SQL>

Create required directories and Add /etc/oratab entry:

[oracle@standby ~]$ mkdir -p /u01/app/oracle/admin/ORCLDR/adump/
[oracle@standby ~]$ 
[oracle@standby ~]$ vi /etc/oratab
[oracle@standby ~]$
[oracle@standby ~]$ cat /etc/oratab
#



# This file is used by ORACLE utilities.  It is created by root.sh
# and updated by either Database Configuration Assistant while creating
# a database or ASM Configuration Assistant while creating ASM instance.

# A colon, ':', is used as the field terminator.  A new line terminates
# the entry.  Lines beginning with a pound sign, '#', are comments.
#
# Entries are of the form:
#   $ORACLE_SID:$ORACLE_HOME:<N|Y>:
#
# The first and second fields are the system identifier and home
# directory of the database respectively.  The third field indicates
# to the dbstart utility that the database should , "Y", or should not,
# "N", be brought up at system boot time.
#
# Multiple entries with the same $ORACLE_SID are not allowed.
#
#
ORCLDR:/u01/app/oracle/product/19c/dbhome_1:N

Startup standby DB in Nomount stage using edited pfile :

[oracle@standby ~]$ cd /u01/app/oracle/product/19c/dbhome_1/dbs
[oracle@standby dbs]$ ls -ltrh
total 28K
-rw-r--r--. 1 oracle oinstall 3.1K May 14  2015 init.ora
-rw-r-----. 1 oracle oinstall 6.0K Oct  9 03:11 orapwORCLDR
-rw-r-----. 1 oracle dba        24 Oct 18 15:59 lkORCLDR
-rw-r--r--. 1 oracle oinstall 1.1K Oct 18 16:24 initORCLDR.ora
-rw-rw----. 1 oracle dba      1.6K Oct 18 17:09 hc_ORCLDR.dat
[oracle@standby dbs]$
[oracle@standby dbs]$ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Tue Oct 18 17:09:35 2022
Version 19.16.0.0.0

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

Connected to an idle instance.

SQL> def
DEFINE _DATE           = "18-OCT-22" (CHAR)
DEFINE _CONNECT_IDENTIFIER = "ORCLDR" (CHAR)
DEFINE _USER           = "SYS" (CHAR)
DEFINE _PRIVILEGE      = "AS SYSDBA" (CHAR)
DEFINE _SQLPLUS_RELEASE = "1916000000" (CHAR)
DEFINE _EDITOR         = "vi" (CHAR)
DEFINE _O_VERSION      = "" (CHAR)
DEFINE _O_RELEASE      = "" (CHAR)
SQL>
SQL> startup nomount;
ORACLE instance started.

Total System Global Area  859829936 bytes
Fixed Size                  8902320 bytes
Variable Size             230686720 bytes
Database Buffers          616562688 bytes
Redo Buffers                3678208 bytes
SQL> exit

Restore controlfile from backup and mount the database:

[oracle@standby dbs]$ rman target /

Recovery Manager: Release 19.0.0.0.0 - Production on Tue Oct 18 16:16:33 2022
Version 19.16.0.0.0

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

connected to target database: ORCL (not mounted)

RMAN> restore standby controlfile from '/u01/rman_bkp/Controlback_20221009_0419pvsp_4_1_1';

Starting restore at 18-OCT-22
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=433 device type=DISK

channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:06
output file name=+REDO01/ORCLDR/CONTROLFILE/current.257.1118420213
output file name=+REDO01/ORCLDR/CONTROLFILE/current.256.1118419121
Finished restore at 18-OCT-22

RMAN> alter database mount;

released channel: ORA_DISK_1
Statement processed

RMAN> select name, database_role, open_mode from v$database;

NAME      DATABASE_ROLE    OPEN_MODE
--------- ---------------- --------------------
ORCL      PHYSICAL STANDBY MOUNTED

RMAN>

Create spfile from pfile:

When we restore controlfile in ASM storage sometime restored controlfile name will be changed. Therefore we will check restored controlfile name and update the same in pfile and then we create a spfile from the same pfile. In my case controlfile restored with new name.

Follow the below steps :

1. Shutdown the database.
2. Change the controlfile name in pfile.
3. startup the database using pfile.
4. create spfile from pfile.
5. Shutdown the database.
6. startup the database.
7. Check and confirm database is up using spfile.


SQL> shut immediate;
ORA-01109: database not open


Database dismounted.
ORACLE instance shut down.
SQL>
SQL> startup mount;
ORACLE instance started.

Total System Global Area  859829936 bytes
Fixed Size                  8902320 bytes
Variable Size             230686720 bytes
Database Buffers          616562688 bytes
Redo Buffers                3678208 bytes
Database mounted.
SQL>
SQL> create spfile='+DATA' from pfile='/u01/app/oracle/product/19c/dbhome_1/dbs/initORCLDR.ora';

File created.

SQL>
SQL> shut immediate;
ORA-01109: database not open


Database dismounted.
ORACLE instance shut down.
SQL>
SQL> startup mount;
ORACLE instance started.

Total System Global Area  859829936 bytes
Fixed Size                  8902320 bytes
Variable Size             230686720 bytes
Database Buffers          616562688 bytes
Redo Buffers                3678208 bytes
Database mounted.
SQL>
SQL> show parameter spfile;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
spfile                               string      +DATA/ORCLDR/PARAMETERFILE/spf
                                                 ile.258.1118420769
SQL>

Restore and recover the database on standby server:

[oracle@standby rman_bkp]$
[oracle@standby rman_bkp]$ rman target /


Recovery Manager: Release 19.0.0.0.0 - Production on Tue Oct 18 17:22:31 2022
Version 19.16.0.0.0

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

connected to target database: ORCL (DBID=1645354742, not open)

RMAN>
RMAN> run
2> {
3> allocate channel c1 type disk;
4> allocate channel c2 type disk;
5> restore database;
6> switch datafile all;
7> switch tempfile all;
8> recover database;
9> release channel c1;
10> release channel c2;
11> }

using target database control file instead of recovery catalog
allocated channel: c1
channel c1: SID=431 device type=DISK

allocated channel: c2
channel c2: SID=458 device type=DISK

Starting restore at 18-OCT-22
Starting implicit crosscheck backup at 18-OCT-22
Crosschecked 1 objects
Crosschecked 2 objects
Finished implicit crosscheck backup at 18-OCT-22

Starting implicit crosscheck copy at 18-OCT-22
Finished implicit crosscheck copy at 18-OCT-22

searching for all files in the recovery area
cataloging files...
no files cataloged


channel c1: starting datafile backup set restore
channel c1: specifying datafile(s) to restore from backup set
channel c1: restoring datafile 00001 to +DATA/ORCL/DATAFILE/system.257.1117573871
channel c1: restoring datafile 00003 to +DATA/ORCL/DATAFILE/sysaux.259.1117573927
channel c1: restoring datafile 00004 to +DATA/ORCL/DATAFILE/undotbs1.258.1117573951
channel c1: restoring datafile 00007 to +DATA/ORCL/DATAFILE/users.260.1117573953
channel c1: reading from backup piece /u01/rman_bkp/Fullback_20221009_0219pvoc_2_1_1
channel c1: piece handle=/u01/rman_bkp/Fullback_20221009_0219pvoc_2_1_1 tag=TAG20221009T001923
channel c1: restored backup piece 1
channel c1: restore complete, elapsed time: 00:01:56
Finished restore at 18-OCT-22



Starting recover at 18-OCT-22

starting media recovery

channel c1: starting archived log restore to default destination
channel c1: restoring archived log
archived log thread=1 sequence=16
channel c1: reading from backup piece /u01/rman_bkp/Archive_20221009_0319pvsm_3_1_1
channel c1: piece handle=/u01/rman_bkp/Archive_20221009_0319pvsm_3_1_1 tag=TAG20221009T002141
channel c1: restored backup piece 1
channel c1: restore complete, elapsed time: 00:00:01
archived log file name=+RECO/ORCLDR/ARCHIVELOG/2022_10_18/thread_1_seq_16.257.1118424445 thread=1 sequence=16
channel default: deleting archived log(s)
archived log file name=+RECO/ORCLDR/ARCHIVELOG/2022_10_18/thread_1_seq_16.257.1118424445 RECID=1 STAMP=1118424444
media recovery complete, elapsed time: 00:00:01
Finished recover at 18-OCT-22

released channel: c1

released channel: c2

RMAN>

RMAN> exit


Recovery Manager complete.
[oracle@standby rman_bkp]$

Recreate redo logs:

SQL> 
SQL> select * from v$logfile;

    GROUP# STATUS  TYPE    MEMBER                                             IS_     CON_ID
---------- ------- ------- -------------------------------------------------- --- ----------
         2         ONLINE  +REDO01/ORCLDR/ONLINELOG/group_2.256.1116486127   NO           0
         2         ONLINE  +REDO02/ORCLDR/ONLINELOG/group_2.256.1116486127   NO           0
         1         ONLINE  +REDO01/ORCLDR/ONLINELOG/group_1.257.1116486127   NO           0
         1         ONLINE  +REDO02/ORCLDR/ONLINELOG/group_1.257.1116486127   NO           0
         3         ONLINE  +REDO01/ORCLDR/ONLINELOG/group_3.259.1116486127   NO           0
         3         ONLINE  +REDO02/ORCLDR/ONLINELOG/group_3.259.1116486127   NO           0
         4         ONLINE  +REDO01/ORCLDR/ONLINELOG/group_4.260.1116486127   NO           0
         4         ONLINE  +REDO02/ORCLDR/ONLINELOG/group_4.260.1116486127   NO           0
         5         STANDBY +REDO01/ORCL/ONLINELOG/group_5.261.1116416249   NO           0
         5         STANDBY +REDO02/ORCL/ONLINELOG/group_5.261.1116416249   NO           0
         6         STANDBY +REDO01/ORCL/ONLINELOG/group_6.262.1116416375   NO           0
         6         STANDBY +REDO02/ORCL/ONLINELOG/group_6.262.1116416377   NO           0
         7         STANDBY +REDO01/ORCL/ONLINELOG/group_7.263.1116416383   NO           0
         7         STANDBY +REDO02/ORCL/ONLINELOG/group_7.263.1116416383   NO           0
         8         STANDBY +REDO01/ORCL/ONLINELOG/group_8.264.1116416393   NO           0
         8         STANDBY +REDO02/ORCL/ONLINELOG/group_8.264.1116416393   NO           0
         9         STANDBY +REDO01/ORCL/ONLINELOG/group_9.265.1116416399   NO           0
         9         STANDBY +REDO02/ORCL/ONLINELOG/group_9.265.1116416399   NO           0
        10         STANDBY +REDO01/ORCL/ONLINELOG/group_10.266.1116416405  NO           0
        10         STANDBY +REDO02/ORCL/ONLINELOG/group_10.266.1116416405  NO           0

20 rows selected.

SQL> select * from v$log;

    GROUP#    THREAD#  SEQUENCE#      BYTES  BLOCKSIZE    MEMBERS ARC STATUS           FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME     CON_ID
---------- ---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- --------- ------------ --------- ----------
         1          1          0  209715200        512          2 YES UNUSED              1777116595 18-OCT-22   9.2954E+18                    0
         2          1          0  209715200        512          2 YES UNUSED              1777112286 18-OCT-22   1777116595 18-OCT-22          0
         3          2          0  209715200        512          2 YES UNUSED              1777116606 18-OCT-22   9.2954E+18                    0
         4          2          0  209715200        512          2 YES UNUSED              1777112281 18-OCT-22   1777116606 18-OCT-22          0

SQL> select GROUP#, THREAD#, BYTES/1024/1024 from v$standby_log;

    GROUP#    THREAD# BYTES/1024/1024
---------- ---------- ---------------
         5          1             200
         6          1             200
         7          1             200
         8          2             200
         9          2             200
        10          2             200

6 rows selected.

SQL>


======================================
Recreate redologs:
======================================

SQL> alter system set standby_file_management='MANUAL' scope=both;

System altered.

SQL> alter database drop logfile group 1;

Database altered.

SQL> alter database drop logfile group 2;

Database altered.

SQL> alter database drop logfile group 3;

Database altered.

SQL> alter database drop logfile group 4;

Database altered.

SQL> alter database add logfile THREAD 1 group 1 ('+REDO01','+REDO02') size 200m;

Database altered.

SQL> alter database add logfile THREAD 1 group 2 ('+REDO01','+REDO02') size 200m;

Database altered.

SQL> alter database add logfile THREAD 2 group 3 ('+REDO01','+REDO02') size 200m;

Database altered.

SQL> alter database add logfile THREAD 2 group 4 ('+REDO01','+REDO02') size 200m;

Database altered.

SQL> alter database drop logfile group 5;

Database altered.

SQL> alter database drop logfile group 6;

Database altered.

SQL> alter database drop logfile group 7;

Database altered.

SQL> alter database drop logfile group 8;

Database altered.

SQL> alter database drop logfile group 9;

Database altered.

SQL> alter database drop logfile group 10;

Database altered.

SQL> select * from v$logfile;

    GROUP# STATUS  TYPE    MEMBER                                             IS_     CON_ID
---------- ------- ------- -------------------------------------------------- --- ----------
         1         ONLINE  +REDO01/ORCLDR/ONLINELOG/group_1.256.1116487069   NO           0
         1         ONLINE  +REDO02/ORCLDR/ONLINELOG/group_1.256.1116487069   NO           0
         2         ONLINE  +REDO01/ORCLDR/ONLINELOG/group_2.257.1116487085   NO           0
         2         ONLINE  +REDO02/ORCLDR/ONLINELOG/group_2.257.1116487085   NO           0
         3         ONLINE  +REDO01/ORCLDR/ONLINELOG/group_3.260.1116487179   NO           0
         3         ONLINE  +REDO02/ORCLDR/ONLINELOG/group_3.260.1116487179   NO           0
         4         ONLINE  +REDO01/ORCLDR/ONLINELOG/group_4.259.1116487181   NO           0
         4         ONLINE  +REDO02/ORCLDR/ONLINELOG/group_4.259.1116487181   NO           0

8 rows selected.

SQL> alter database add standby logfile THREAD 1 group 5 ('+REDO01','+REDO02') size 200m;

Database altered.

SQL> alter database add standby logfile THREAD 1 group 6 ('+REDO01','+REDO02') size 200m;

Database altered.

SQL> alter database add standby logfile THREAD 1 group 7 ('+REDO01','+REDO02') size 200m;

Database altered.

SQL> alter database add standby logfile THREAD 2 group 8 ('+REDO01','+REDO02') size 200m;

Database altered.

SQL> alter database add standby logfile THREAD 2 group 9 ('+REDO01','+REDO02') size 200m;

Database altered.

SQL> alter database add standby logfile THREAD 2 group 10 ('+REDO01','+REDO02') size 200m;

Database altered.

SQL>
SQL>
SQL>
SQL> select * from v$logfile;

    GROUP# STATUS  TYPE    MEMBER                                             IS_     CON_ID
---------- ------- ------- -------------------------------------------------- --- ----------
         1         ONLINE  +REDO01/ORCLDR/ONLINELOG/group_1.256.1116487069   NO           0
         1         ONLINE  +REDO02/ORCLDR/ONLINELOG/group_1.256.1116487069   NO           0
         2         ONLINE  +REDO01/ORCLDR/ONLINELOG/group_2.257.1116487085   NO           0
         2         ONLINE  +REDO02/ORCLDR/ONLINELOG/group_2.257.1116487085   NO           0
         3         ONLINE  +REDO01/ORCLDR/ONLINELOG/group_3.260.1116487179   NO           0
         3         ONLINE  +REDO02/ORCLDR/ONLINELOG/group_3.260.1116487179   NO           0
         4         ONLINE  +REDO01/ORCLDR/ONLINELOG/group_4.259.1116487181   NO           0
         4         ONLINE  +REDO02/ORCLDR/ONLINELOG/group_4.259.1116487181   NO           0
         5         STANDBY +REDO01/ORCLDR/ONLINELOG/group_5.261.1116487273   NO           0
         5         STANDBY +REDO02/ORCLDR/ONLINELOG/group_5.261.1116487273   NO           0
         6         STANDBY +REDO01/ORCLDR/ONLINELOG/group_6.262.1116487273   NO           0
         6         STANDBY +REDO02/ORCLDR/ONLINELOG/group_6.262.1116487275   NO           0
         7         STANDBY +REDO01/ORCLDR/ONLINELOG/group_7.263.1116487275   NO           0
         7         STANDBY +REDO02/ORCLDR/ONLINELOG/group_7.263.1116487275   NO           0
         8         STANDBY +REDO01/ORCLDR/ONLINELOG/group_8.264.1116487291   NO           0
         8         STANDBY +REDO02/ORCLDR/ONLINELOG/group_8.264.1116487291   NO           0
         9         STANDBY +REDO01/ORCLDR/ONLINELOG/group_9.265.1116487291   NO           0
         9         STANDBY +REDO02/ORCLDR/ONLINELOG/group_9.265.1116487293   NO           0
        10         STANDBY +REDO01/ORCLDR/ONLINELOG/group_10.266.1116487293  NO           0
        10         STANDBY +REDO02/ORCLDR/ONLINELOG/group_10.266.1116487293  NO           0

20 rows selected.

SQL>
SQL> alter system set standby_file_management='AUTO' scope=both;

System altered.

SQL> 

In the case of Physical Standby database Check the database status and start MRP process:

SQL> select status,instance_name,database_role,open_mode,log_mode,STARTUP_TIME,VERSION,SWITCHOVER_STATUS from v$database,gv$Instance;

STATUS       INSTANCE_NAME    DATABASE_ROLE    OPEN_MODE            LOG_MODE     STARTUP_T VERSION           SWITCHOVER_STATUS
------------ ---------------- ---------------- -------------------- ------------ --------- ----------------- --------------------
OPEN         ORCLDR           PHYSICAL STANDBY MOUNTED              ARCHIVELOG   18-OCT-22 19.0.0.0.0        NOT ALLOWED

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

Database altered.

SQL>

In the case of Active Dataguard Check the database status, open the database and then start MRP process:

SQL> select status,instance_name,database_role,open_mode,log_mode,STARTUP_TIME,VERSION,SWITCHOVER_STATUS from v$database,gv$Instance;

STATUS       INSTANCE_NAME    DATABASE_ROLE    OPEN_MODE            LOG_MODE     STARTUP_T VERSION           SWITCHOVER_STATUS
------------ ---------------- ---------------- -------------------- ------------ --------- ----------------- --------------------
OPEN         ORCLDR           PHYSICAL STANDBY MOUNTED              ARCHIVELOG   18-OCT-22 19.0.0.0.0        NOT ALLOWED

SQL>
SQL> alter database open read only;

Database altered.

SQL> 
SQL> select status,instance_name,database_role,open_mode,log_mode,STARTUP_TIME,VERSION,SWITCHOVER_STATUS from v$database,gv$Instance;

STATUS       INSTANCE_NAME    DATABASE_ROLE    OPEN_MODE            LOG_MODE     STARTUP_T VERSION           SWITCHOVER_STATUS
------------ ---------------- ---------------- -------------------- ------------ --------- ----------------- --------------------
OPEN         ORCLDR           PHYSICAL STANDBY READ ONLY            ARCHIVELOG   18-OCT-22 19.0.0.0.0        NOT ALLOWED

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

Database altered.

SQL> select status,instance_name,database_role,open_mode,log_mode,STARTUP_TIME,VERSION,SWITCHOVER_STATUS from v$database,gv$Instance;

STATUS       INSTANCE_NAME    DATABASE_ROLE    OPEN_MODE            LOG_MODE     STARTUP_T VERSION           SWITCHOVER_STATUS
------------ ---------------- ---------------- -------------------- ------------ --------- ----------------- --------------------
OPEN         ORCLDR           PHYSICAL STANDBY READ ONLY WITH APPLY ARCHIVELOG   18-OCT-22 19.0.0.0.0        NOT ALLOWED

SQL>

Check sync status on both side:

PRODUCTION side:
-------------------------------

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

   THREAD# MAX(SEQUENCE#)
---------- --------------
         1            773
         2            312


DR side:
-------------------------------

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

   THREAD# MAX(SEQUENCE#)
---------- --------------
         1            773
         2            312

Your Physical Standby or Active Dataguard is ready.

Some Useful queries for Monitoring Dataguard:

To start the MRP process:
=========================================

alter database recover managed standby database disconnect from session;


To stop the MRP process:
=========================================

alter database recover managed standby database cancel;


To check the MRP process status:
=========================================

select process, status, sequence# from v$managed_standby;


To check the Archive logs Applied status:
=========================================

set lines 400 pages 4000
alter session set nls_date_format='dd-mon-yyyy hh24:mi:ss';

select THREAD#, SEQUENCE#, APPLIED, STATUS, STAMP, COMPLETION_TIME from v$archived_log;

How to convert physical standby to active dataguard in Oracle.

Leave a Comment