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_homePrimary 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.oraCopy 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:NStartup 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.
2 thoughts on “Oracle 19c 2 node RAC to single instance standby database setup”