Heterogenous Dataguard is a best use case, when we need to perform DB migration from Windows to Linux with minimum downtime as the actual downtime comprises only DB switch. As both Windows and Linux are Little Endian so no conversion is required at the target side. In this article, we are going to configure Oracle Heterogeneous Standby with Primary in Windows & Standby in Linux.
To view, the full log of below demo Click here.
Below are the high-level steps we will follow to configure Dataguard
1. Configure Primary DB
2. Configure Standby DB
3. Take Primary DB backup
4. Transfer to Standby Server
5. Initiate restore in Standby Server
6. Validate Standby DB
7. Start MRP in Standby Server
Below are setup details and the same will be used in this demonstration.
OS | Hostname | Datafile Location | Logfile Location | Role | TNS Entry | DB Unique Name | DB Name | |
Windows | test-machine01 | G:UCASNEWUCASNEW H:UCASNEWORADATA_2 | G:UCASNEWORADATA_1 H:UCASNEWORADATA_1 | Primary | ucas | ucas | ucas | |
Oracle Linux | test-machine02 | /data01/oradata_2/ /data02/oradata_2/ | /data01/oradata_1/ /data02/oradata_1/ | Standby | ucasdr | ucasdr | ucas |
Step 1. Configure Primary DB: Modify the below parameters in Primary DB. Check the Primary DB setting for the DB_NAME and DB_UNIQUE_NAME parameters it should be the same in our case we have ucas. The DB_NAME of the standby database will be the same as that of the primary, but it must have a different DB_UNIQUE_NAME value. The DB_UNIQUE_NAME values of the primary and standby database should be used in the DG_CONFIG setting of the LOG_ARCHIVE_CONFIG parameter. Set suitable local and remote archive log destinations. Parameter log_archive_dest_2 refers to remote standby location set proper SERVICE and the DB_UNIQUE_NAME in our case we are using the same name i.e. ucasdr for SERVICE and DB_UNIQUE_NAME. Set log_file_name_convert & db_file_name_convert to account for your filename and path differences between the source and target servers. Also, make sure Force Logging and Archive log mode are enabled in Primary DB. In addition to the online redo logs, we should create standby redo logs. The standby redo logs should be the same size as online redo logfiles and should have one extra group per thread compared to the online redo logs.
################ Datafile & Logfile location Primary DB ################# SQL> SQL> set lines 300 SQL> set pages 300 SQL> col member for a40 SQL> select name from v$datafile; NAME ----------------------------------------------------------------------- G:UCASNEWUCASNEWSYSTEM01.DBF G:UCASNEWUCASNEWSYSAUX01.DBF G:UCASNEWUCASNEWUNDOTBS01.DBF G:UCASNEWUCASNEWUSERS01.DBF H:UCASNEWORADATA_2UCAS_01.DBF H:UCASNEWORADATA_2UCAS_DATA_01.DBF H:UCASNEWORADATA_2UCAS_INDEX_01.DBF H:UCASNEWORADATA_2GGS_DATA_01.DBF H:UCASNEWORADATA_2TELEX_01.DBF H:UCASNEWORADATA_2UCAS_02.DBF H:UCASNEWORADATA_2UCAS_DATA_02.DBF H:UCASNEWORADATA_2UCAS_INDEX_02.DBF 12 rows selected. SQL> SQL> select member from v$logfile; MEMBER ---------------------------------------- H:UCASNEWORADATA_1REDO04.LOG H:UCASNEWORADATA_1REDO05.LOG G:UCASNEWORADATA_1REDO06.LOG G:UCASNEWORADATA_1REDO07.LOG 4 rows selected. SQL> ################ Primary DB Init Parameters ################# *.log_archive_config='DG_CONFIG=(ucas,ucasdr)' *.log_archive_dest_1='LOCATION=I:FLASH_RECOVERY_AREAUCASARCHIVELOG VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=ucas' *.log_archive_dest_2='SERVICE=ucasdr LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) db_unique_name='ucasdr' *.log_archive_format='arch_%t_%s_%r.log' *.log_archive_max_processes=5 *.log_file_name_convert='/data01/oradata_1/ ','G:UCASNEWORADATA_1 ','/data02/oradata_1/','H:UCASNEWORADATA_1' *.db_file_name_convert='/data01/oradata_2/ ','G:UCASNEWUCASNEW','/data02/oradata_2/','H:UCASNEWORADATA_2' *.fal_client='ucas' *.fal_server='ucasdr' *.standby_file_management='AUTO' ############ Add TNS Entry for Standby and Primary ############ UCAS = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = test-machine01)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = ucas) (UR = A) ) ) UCASDR = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = test-machine02)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = ucasdr) (UR = A) ) ) C:Usersjkhan> C:Usersjkhan>tnsping ucas TNS Ping Utility for 64-bit Windows: Version 11.2.0.4.0 - Production on 19-APR-2022 10:50:05 Copyright (c) 1997, 2013, Oracle. All rights reserved. Used parameter files: F:Oracleappproduct11.2.0.4db_1networkadminsqlnet.ora Used TNSNAMES adapter to resolve the alias Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = test-machine01)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = ucas) (UR = A))) OK (30 msec) C:Usersjkhan> C:Usersjkhan>tnsping ucasdr TNS Ping Utility for 64-bit Windows: Version 11.2.0.4.0 - Production on 19-APR-2022 10:50:07 Copyright (c) 1997, 2013, Oracle. All rights reserved. Used parameter files: F:Oracleappproduct11.2.0.4db_1networkadminsqlnet.ora Used TNSNAMES adapter to resolve the alias Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = test-machine02)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = ucasdr) (UR = A))) OK (10 msec) C:Usersjkhan> ############ Enable Force Logging in Primary DB ############ C:Usersjkhan>sqlplus sys as sysdba SQL*Plus: Release 11.2.0.4.0 Production on Mon Apr 18 15:35:32 2022 Copyright (c) 1982, 2013, Oracle. All rights reserved. Enter password: Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options SQL> SQL> show parameter db_name NAME TYPE VALUE ------------------------------------ -------------------------------- ------------------------------ db_name string ucas SQL> SQL> show parameter db_unique_name NAME TYPE VALUE ------------------------------------ -------------------------------- ------------------------------ db_unique_name string ucas SQL> SQL> SQL> select LOG_MODE, OPEN_MODE,FLASHBACK_ON,FORCE_LOGGING from v$database; LOG_MODE OPEN_MODE FLASHBACK_ON FOR ------------ ---------- ------------------ --- ARCHIVELOG READ WRITE YES NO SQL> alter database force logging; Database altered. SQL> SQL> SQL> select LOG_MODE, OPEN_MODE,FLASHBACK_ON,FORCE_LOGGING from v$database; LOG_MODE OPEN_MODE FLASHBACK_ON FOR ------------ -------------------- ------------------ --- ARCHIVELOG READ WRITE NO YES SQL> ############ Add Standby Logfile in Primary DB ############ ALTER DATABASE ADD STANDBY LOGFILE group 8 'H:UCASNEWORADATA_1STDBY_REDO08.LOG' size 524288000; ALTER DATABASE ADD STANDBY LOGFILE group 9 'H:UCASNEWORADATA_1STDBY_REDO09.LOG' size 524288000; ALTER DATABASE ADD STANDBY LOGFILE group 10 'H:UCASNEWORADATA_1STDBY_REDO09.LOG' size 524288000; ALTER DATABASE ADD STANDBY LOGFILE group 11 'G:UCASNEWORADATA_1STDBY_REDO11.LOG' size 524288000; ALTER DATABASE ADD STANDBY LOGFILE group 12 'G:UCASNEWORADATA_1STDBY_REDO12.LOG' size 524288000; SQL> set lines 300 SQL> set pages 300 SQL> col member for a50 SQL> select * from v$logfile order by 1; GROUP# STATUS TYPE MEMBER IS_ ---------- ------- ------- -------------------------------------------------- --- 4 ONLINE H:UCASNEWORADATA_1REDO04.LOG NO 5 ONLINE H:UCASNEWORADATA_1REDO05.LOG NO 6 ONLINE G:UCASNEWORADATA_1REDO06.LOG NO 7 ONLINE G:UCASNEWORADATA_1REDO07.LOG NO 8 STANDBY H:UCASNEWORADATA_1STDBY_REDO08.LOG NO 9 STANDBY H:UCASNEWORADATA_1STDBY_REDO09.LOG NO 10 STANDBY G:UCASNEWORADATA_1STDBY_REDO10.LOG NO 11 STANDBY G:UCASNEWORADATA_1STDBY_REDO11.LOG NO 12 STANDBY G:UCASNEWORADATA_1STDBY_REDO12.LOG NO 9 rows selected. SQL>
Step 2. Configure Standby DB: Set the below parameters in Standby init parameter. Add primary and standby TNS entry. When using duplicate command the standby server requires static listener configuration in a “listener.ora” file.
################ Standby Init Parameters ################# *.log_archive_config='DG_CONFIG=(ucas,ucasdr)' *.log_archive_dest_1='LOCATION=/backup/fast_recovery_area/ucas/archivelog VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=ucasdr' *.log_archive_dest_2='SERVICE=ucas LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=ucas' *.log_archive_format='arch_%t_%s_%r.log' *.log_archive_max_processes=5 *.db_file_name_convert='G:UCASNEWUCASNEW','/data01/oradata_2/','H:UCASNEWORADATA_2','/data02/oradata_2/' *.log_file_name_convert='G:UCASNEWORADATA_1','/data01/oradata_1/','H:UCASNEWORADATA_1','/data02/oradata_1/' *.fal_client='ucasdr' *.fal_server='ucas' *.standby_file_management='AUTO' ############ TNS Entry for both Standby and Primary ############ UCAS = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = test-machine01)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = ucas) (UR = A) ) ) UCASDR = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = test-machine02)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = ucasdr) (UR = A) ) ) [oracle@test-machine02 ~]$ [oracle@test-machine02 ~]$ tnsping ucas TNS Ping Utility for Linux: Version 11.2.0.4.0 - Production on 19-APR-2022 11:19:03 Copyright (c) 1997, 2013, Oracle. All rights reserved. Used parameter files: Used TNSNAMES adapter to resolve the alias Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = test-machine01)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = ucas) (UR = A))) OK (60 msec) [oracle@test-machine02 ~]$ [oracle@test-machine02 ~]$ tnsping ucasdr TNS Ping Utility for Linux: Version 11.2.0.4.0 - Production on 19-APR-2022 11:19:06 Copyright (c) 1997, 2013, Oracle. All rights reserved. Used parameter files: Used TNSNAMES adapter to resolve the alias Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = test-machine02)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SID = ucas) (UR = A))) OK (0 msec) [oracle@test-machine02 ~]$ ############ Add static entry in Standby Listener ############ SID_LIST_LISTENER = (SID_LIST = (SID_DESC = (SID_NAME = PLSExtProc) (ORACLE_HOME = /u01/app/oracle/product/11.2.0.4/db_1) (PROGRAM = extproc) ) (SID_DESC = (SID_NAME = ucas) (ORACLE_HOME = /u01/app/oracle/product/11.2.0.4/db_1) ) ) LISTENER = (DESCRIPTION_LIST = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = test-machine02)(PORT = 1521)) (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521)) ) )
Step 3. Take Primary DB backup: Run all the below commands in the RMAN prompt to take backup of Primary DB. Please note we are taking Full DB backup, Standby Controlfile backup, and archivelog backup.
C:Usersjkhan>rman target sys/pass123 Recovery Manager: Release 11.2.0.4.0 - Production on Mon Apr 18 10:29:18 2022 Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved. connected to target database: UCAS (DBID=2026517590) RMAN> run { allocate channel ch1 device type disk; allocate channel ch2 device type disk; allocate channel ch3 device type disk; backup tag 'FULL BACKUP' format 'F:UCAS_Backupbackup_db_%d_S_%s_P_%p_T_%t' as compressed backupset database; release channel ch1; release channel ch2; release channel ch3; } backup current controlfile for standby format='F:UCAS_BackupBACKUP_stby_cfile.%U'; sql "alter system switch logfile"; sql "alter system switch logfile"; sql "alter system switch logfile"; run { allocate channel ch1 device type disk; allocate channel ch2 device type disk; allocate channel ch3 device type disk; backup tag 'ARCHIVE BACKUP' format 'F:UCAS_BackupBACKUP_archive_db_%d_S_%s_P_%p_T_%t' as compressed backupset archivelog all; release channel ch1; release channel ch2; release channel ch3; }
Step 4. Transfer to Standby Server: Once all backups are done. Transfer all backups to Standby Server. Please note we are using here pscp.exe tool to transfer files from Windows to Linux Server along with transferring all Primary DB backups in Standby Server location: /u01/db_pump.
F:UCAS_Backup> F:UCAS_Backup>dir Volume in drive F is DB GG Soft Volume Serial Number is 089F-8206 Directory of F:UCAS_Backup 04/17/2022 05:49 AM <DIR> . 04/17/2022 05:49 AM <DIR> .. 04/17/2022 05:45 AM 489,657,856 BACKUP_ARCHIVE_DB_UCAS_S_8711_P_1_T_1102225478 04/17/2022 05:45 AM 516,775,424 BACKUP_ARCHIVE_DB_UCAS_S_8712_P_1_T_1102225478 04/17/2022 05:45 AM 395,673,600 BACKUP_ARCHIVE_DB_UCAS_S_8713_P_1_T_1102225478 04/17/2022 05:45 AM 183,844,864 BACKUP_ARCHIVE_DB_UCAS_S_8714_P_1_T_1102225478 04/17/2022 05:45 AM 31,232 BACKUP_ARCHIVE_DB_UCAS_S_8715_P_1_T_1102225514 04/17/2022 05:40 AM 2,394,677,248 BACKUP_DB_UCAS_S_8701_P_1_T_1102224961 04/17/2022 05:40 AM 2,142,011,392 BACKUP_DB_UCAS_S_8702_P_1_T_1102224961 04/17/2022 05:38 AM 1,397,006,336 BACKUP_DB_UCAS_S_8703_P_1_T_1102224961 04/17/2022 05:38 AM 1,409,024 BACKUP_DB_UCAS_S_8704_P_1_T_1102225117 04/17/2022 05:38 AM 98,304 BACKUP_DB_UCAS_S_8705_P_1_T_1102225119 04/17/2022 05:42 AM 13,991,936 BACKUP_STBY_CFILE.G20R57TL_1_1 18 File(s) 7,535,819,370 bytes 2 Dir(s) 186,392,072,192 bytes free F:UCAS_Backup>pscp.exe BACKUP* oracle@test-machine02:/u01/db_pump oracle@test-machine02's password: BACKUP_ARCHIVE_DB_UCAS_S_ | 478181 kB | 469.3 kB/s | ETA: 00:00:00 | 100% BACKUP_ARCHIVE_DB_UCAS_S_ | 504663 kB | 452.2 kB/s | ETA: 00:00:00 | 100% BACKUP_ARCHIVE_DB_UCAS_S_ | 386400 kB | 517.3 kB/s | ETA: 00:00:00 | 100% BACKUP_ARCHIVE_DB_UCAS_S_ | 179536 kB | 473.7 kB/s | ETA: 00:00:00 | 100% BACKUP_ARCHIVE_DB_UCAS_S_ | 30 kB | 30.5 kB/s | ETA: 00:00:00 | 100% BACKUP_DB_UCAS_S_8701_P_1 | 2338552 kB | 450.5 kB/s | ETA: 00:00:00 | 100% BACKUP_DB_UCAS_S_8702_P_1 | 2091808 kB | 469.2 kB/s | ETA: 00:00:00 | 100% BACKUP_DB_UCAS_S_8703_P_1 | 1364264 kB | 439.7 kB/s | ETA: 00:00:00 | 100% BACKUP_DB_UCAS_S_8704_P_1 | 1376 kB | 275.2 kB/s | ETA: 00:00:00 | 100% BACKUP_DB_UCAS_S_8705_P_1 | 96 kB | 96.0 kB/s | ETA: 00:00:00 | 100% BACKUP_STBY_CFILE.G20R57T | 13664 kB | 290.7 kB/s | ETA: 00:00:00 | 100% F:UCAS_Backup> F:UCAS_Backup> F:UCAS_Backup>
Step 5. Initiate restore in Standby Server: Start standby instance in nomount mode and connect to RMAN using the below command. Execute duplicate database for standby backup location command. We can also use command “duplicate target database for standby from active database nofilenamecheck“. This command will take backup on fly and transfer to standby db but this active process will impact network bandwidth.
[oracle@test-machine02 ~]$ [oracle@test-machine02 ~]$ sqlplus sys as sysdba SQL*Plus: Release 11.2.0.4.0 Production on Tue Apr 19 11:27:06 2022 Copyright (c) 1982, 2013, Oracle. All rights reserved. Enter password: Connected to an idle instance. SQL> startup nomount ORACLE instance started. Total System Global Area 1.0456E+10 bytes Fixed Size 2262368 bytes Variable Size 2248149664 bytes Database Buffers 8187281408 bytes Redo Buffers 17948672 bytes SQL> SQL> exit Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options [oracle@test-machine02 ~]$ [oracle@test-machine02 ~]$ [oracle@test-machine02 ~]$ rman auxiliary sys/pass123@ucasdr Recovery Manager: Release 11.2.0.4.0 - Production on Tue Apr 19 11:28:12 2022 Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved. connected to auxiliary database: UCAS (not mounted) RMAN> run { allocate auxiliary channel prmy1 type disk; allocate auxiliary channel prmy2 type disk; allocate auxiliary channel prmy3 type disk; duplicate database 'ucas' for standby backup location '/u01/db_pump' NOFILENAMECHECK; }
Step 6. Validate Standby DB: After the duplicate command is finished. Validate standby DB.
[oracle@test-machine02 db_pump]$ [oracle@test-machine02 db_pump]$ sqlplus sys as sysdba SQL*Plus: Release 11.2.0.4.0 Production on Sun Apr 17 14:46:57 2022 Copyright (c) 1982, 2013, Oracle. All rights reserved. Enter password: Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options SQL> select status from v$instance; STATUS ------------ MOUNTED SQL> SQL> SQL> select name from v$controlfile; NAME -------------------------------------------------------------------------------- /data01/oradata_1/control01.ctl /data02/oradata_1/control02.ctl SQL> SQL> set lines 300 SQL> set pages 300 SQL> select name from v$datafile; NAME ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ /data01/oradata_2/SYSTEM01.DBF /data01/oradata_2/SYSAUX01.DBF /data01/oradata_2/UNDOTBS01.DBF /data01/oradata_2/USERS01.DBF /data02/oradata_2/UCAS_01.DBF /data02/oradata_2/UCAS_DATA_01.DBF /data02/oradata_2/UCAS_INDEX_01.DBF /data02/oradata_2/GGS_DATA_01.DBF /data02/oradata_2/TELEX_01.DBF /data02/oradata_2/UCAS_02.DBF /data02/oradata_2/UCAS_DATA_02.DBF /data02/oradata_2/UCAS_INDEX_02.DBF 12 rows selected. SQL> select name from v$tempfile; NAME ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ /data01/oradata_2/TEMP01.DBF SQL> set lines 300 SQL> set pages 300 SQL> col member for a50 SQL> select * from v$log; GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME ---------- ---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- --------- ------------ --------- 4 1 0 524288000 512 1 YES UNUSED 1.4961E+10 16-APR-22 1.4963E+10 17-APR-22 7 1 0 524288000 512 1 NO CURRENT 1.4963E+10 17-APR-22 2.8147E+14 6 1 0 524288000 512 1 YES UNUSED 1.4963E+10 17-APR-22 1.4963E+10 17-APR-22 5 1 0 524288000 512 1 YES UNUSED 1.4963E+10 17-APR-22 1.4963E+10 17-APR-22 SQL> SQL> select * from v$logfile order by 1; GROUP# STATUS TYPE MEMBER IS_ ---------- ------- ------- -------------------------------------------------- --- 4 ONLINE /data02/oradata_1/REDO04.LOG NO 5 ONLINE /data02/oradata_1/REDO05.LOG NO 6 ONLINE /data01/oradata_1/REDO06.LOG NO 7 ONLINE /data01/oradata_1/REDO07.LOG NO 8 STANDBY /data02/oradata_1/STDBY_REDO08.LOG NO 9 STANDBY /data02/oradata_1/STDBY_REDO09.LOG NO 10 STANDBY /data01/oradata_1/STDBY_REDO10.LOG NO 11 STANDBY /data01/oradata_1/STDBY_REDO11.LOG NO 12 STANDBY /data01/oradata_1/STDBY_REDO12.LOG NO 9 rows selected. SQL> SQL>
Step 7. Start MRP in Standby Server: Start MRP process in Standby DB and monitor redo transfer using the below command. We notice the redo transfer was not working and after checking the logfile we had ORA-16191 error. We tried to enable and disable parameter log_archive_dest_state_2 in the primary DB. And also restarted the MRP process but didn’t work. So finally recreated the password file in Primary and Standby Server fixed the issue.
SQL> SQL> SQL> alter database recover managed standby database using current logfile disconnect from session; Database altered. SQL> set lines 200 set pages 200 select inst_id,process,status,client_process,THREAD#,sequence#,block#,active_agents,known_agents from gv$managed_standby where PROCESS like 'MRP%' or client_process like 'LGWR'; SQL> SQL> INST_ID PROCESS STATUS CLIENT_P THREAD# SEQUENCE# BLOCK# ACTIVE_AGENTS KNOWN_AGENTS ---------- --------- ------------ -------- ---------- ---------- ---------- ------------- ------------ 1 MRP0 WAIT_FOR_LOG N/A 1 10998 0 5 5 SQL> SQL> / INST_ID PROCESS STATUS CLIENT_P THREAD# SEQUENCE# BLOCK# ACTIVE_AGENTS KNOWN_AGENTS ---------- --------- ------------ -------- ---------- ---------- ---------- ------------- ------------ 1 MRP0 WAIT_FOR_LOG N/A 1 10998 0 5 5 SQL>exit [oracle@test-machine02 ~]$ [oracle@test-machine02 ~]$ cd /u01/app/oracle/diag/rdbms/ucasdr/ucas/trace [oracle@test-machine02 trace]$ pwd /u01/app/oracle/diag/rdbms/ucasdr/ucas/trace [oracle@test-machine02 trace]$ [oracle@test-machine02trace]$ vi alert_ucas.log Parallel Media Recovery started with 4 slaves Waiting for all non-current ORLs to be archived... All non-current ORLs have been archived. Media Recovery Waiting for thread 1 sequence 10998 Completed: alter database recover managed standby database using current logfile disconnect from session Sun Apr 17 14:56:40 2022 Error 1017 received logging on to the standby ------------------------------------------------------------ Check that the primary and standby are using a password file and remote_login_passwordfile is set to SHARED or EXCLUSIVE, and that the SYS password is same in the password files. returning error ORA-16191 ------------------------------------------------------------ FAL[client, USER]: Error 16191 connecting to ucas for fetching gap sequence "alert_ucas.log" 1114L, 48509C [oracle@test-machine02 trace]$
Primary DB
SQL> SQL> alter system set log_archive_dest_state_2=defer; System altered. SQL> set lines 300 SQL> col destination for a40 SQL> col error for a20 SQL> select inst_id, dest_id "ID",destination,status,error,target, 2 schedule,process,mountid mid 3 from gv$archive_dest 4 where dest_id < 4 5 order by dest_id; INST_ID ID DESTINATION STATUS ERROR TARGET SCHEDULE PROCESS MID ---------- ---------- ---------------------------------------- --------- -------------------- ------- -------- ---------- ---------- 1 1 I:FLASH_RECOVERY_AREAUCASARCHIVELOG VALID PRIMARY ACTIVE ARCH 0 1 2 ucasdr DEFERRED STANDBY ACTIVE LGWR 0 1 3 INACTIVE PRIMARY INACTIVE ARCH 0 SQL> SQL> alter system set log_archive_dest_state_2=enable; System altered. SQL>
Standby DB
SQL> SQL> alter database recover managed standby database cancel; Database altered. SQL> alter database recover managed standby database using current logfile disconnect from session; Database altered. SQL> set lines 200 set pages 200 select inst_id,process,status,client_process,THREAD#,sequence#,block#,active_agents,known_agents from gv$managed_standby where PROCESS like 'MRP%' or client_process like 'LGWR'; SQL> SQL> INST_ID PROCESS STATUS CLIENT_P THREAD# SEQUENCE# BLOCK# ACTIVE_AGENTS KNOWN_AGENTS ---------- --------- ------------ -------- ---------- ---------- ---------- ------------- ------------ 1 MRP0 WAIT_FOR_LOG N/A 1 10998 0 5 5 SQL>
Primary DB
F:Oracleappproduct11.2.0.4db_1database> F:Oracleappproduct11.2.0.4db_1database>orapwd file=PWDucas.ora password=pass123 entries=5 ignorecase=Y F:Oracleappproduct11.2.0.4db_1database>
Primary DB
SQL> SQL> alter database recover managed standby database cancel; Database altered. SQL> oracle@test-machine02 dbs]$ [oracle@test-machine02 dbs]$ orapwd file=orapwucas password=pass123 entries=5 ignorecase=Y [oracle@test-machine02 dbs]$ SQL> alter database recover managed standby database using current logfile disconnect from session; Database altered. SQL> [oracle@test-machine02 archivelog]$ [oracle@test-machine02 archivelog]$ cd /u01/app/oracle/diag/rdbms/ucasdr/ucas/trace [oracle@test-machine02 trace]$ [oracle@test-machine02 trace]$ vi alert_ucas.log Waiting for all non-current ORLs to be archived... All non-current ORLs have been archived. Media Recovery Waiting for thread 1 sequence 10998 Completed: alter database recover managed standby database using current logfile disconnect from session Sun Apr 17 15:12:50 2022 RFS[1]: Assigned to RFS process 148293 RFS[1]: Opened log for thread 1 sequence 11001 dbid 2026517590 branch 1019388628 Archived Log entry 1 added for thread 1 sequence 11001 rlc 1019388628 ID 0x78ca2e07 dest 2: Sun Apr 17 15:12:51 2022 RFS[2]: Assigned to RFS process 148295 RFS[2]: Opened log for thread 1 sequence 11000 dbid 2026517590 branch 1019388628 Sun Apr 17 15:12:51 2022 RFS[3]: Assigned to RFS process 148299 RFS[3]: Opened log for thread 1 sequence 10998 dbid 2026517590 branch 1019388628 Sun Apr 17 15:12:51 2022 RFS[4]: Assigned to RFS process 148297 RFS[4]: Opened log for thread 1 sequence 10999 dbid 2026517590 branch 1019388628 RFS[1]: Opened log for thread 1 sequence 11002 dbid 2026517590 branch 1019388628 Archived Log entry 2 added for thread 1 sequence 11000 rlc 1019388628 ID 0x78ca2e07 dest 2: Archived Log entry 3 added for thread 1 sequence 10999 rlc 1019388628 ID 0x78ca2e07 dest 2: RFS[2]: Opened log for thread 1 sequence 11003 dbid 2026517590 branch 1019388628 Archived Log entry 4 added for thread 1 sequence 11002 rlc 1019388628 ID 0x78ca2e07 dest 2: RFS[4]: Opened log for thread 1 sequence 11004 dbid 2026517590 branch 1019388628 Archived Log entry 5 added for thread 1 sequence 11003 rlc 1019388628 ID 0x78ca2e07 dest 2: RFS[1]: Opened log for thread 1 sequence 11005 dbid 2026517590 branch 1019388628 RFS[2]: Selected log 8 for thread 1 sequence 11006 dbid 2026517590 branch 1019388628 Sun Apr 17 15:12:53 2022 Primary database is in MAXIMUM PERFORMANCE mode RFS[5]: Assigned to RFS process 148303 RFS[5]: Selected log 9 for thread 1 sequence 11007 dbid 2026517590 branch 1019388628 Archived Log entry 6 added for thread 1 sequence 11005 rlc 1019388628 ID 0x78ca2e07 dest 2: Sun Apr 17 15:13:06 2022 Archived Log entry 7 added for thread 1 sequence 11006 ID 0x78ca2e07 dest 1: "alert_ucas.log" 1203L, 53377C [oracle@test-machine02 archivelog] [oracle@test-machine02 ~]$ [oracle@test-machine02 ~]$ sqlplus sys as sysdba SQL*Plus: Release 11.2.0.4.0 Production on Mon Apr 18 16:48:10 2022 Copyright (c) 1982, 2013, Oracle. All rights reserved. Enter password: Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options SQL> SQL> set lines 200 set pages 200 select inst_id,process,status,client_process,THREAD#,sequence#,block#,active_agents,known_agents from gv$managed_standby where PROCESS like 'MRP%' or client_process like 'LGWR'; SQL> SQL> SQL> INST_ID PROCESS STATUS CLIENT_P THREAD# SEQUENCE# BLOCK# ACTIVE_AGENTS KNOWN_AGENTS ---------- --------- ------------ -------- ---------- ---------- ---------- ------------- ------------ 1 MRP0 WAIT_FOR_LOG N/A 1 10998 0 5 5 1 RFS IDLE LGWR 1 11007 22 0 0 SQL> SQL> / INST_ID PROCESS STATUS CLIENT_P THREAD# SEQUENCE# BLOCK# ACTIVE_AGENTS KNOWN_AGENTS ---------- --------- ------------ -------- ---------- ---------- ---------- ------------- ------------ 1 MRP0 WAIT_FOR_LOG N/A 1 10998 0 5 5 1 RFS IDLE LGWR 1 11007 26 0 0 SQL> / INST_ID PROCESS STATUS CLIENT_P THREAD# SEQUENCE# BLOCK# ACTIVE_AGENTS KNOWN_AGENTS ---------- --------- ------------ -------- ---------- ---------- ---------- ------------- ------------ 1 MRP0 WAIT_FOR_LOG N/A 1 10998 0 5 5 1 RFS IDLE LGWR 1 11007 29 0 0 SQL>
Even the redo transfer service was working fine. Standby DB was unable to resolve the archive log gap. So we decided to take archive log backup manually and transfer it to the Standby server. And registered all logfiles with Standby DB manually and started the MRP process with this standby DB was able to apply all missing archivelog and was in sync with the primary.
Primary DB
C:Usersjkhan>rman target sys/pass123 Recovery Manager: Release 11.2.0.4.0 - Production on Sun Apr 17 16:18:12 2022 Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved. connected to target database: UCAS (DBID=2026517590) RMAN> RMAN> backup as compressed backupset archivelog from sequence 10998 until sequence 11014 format 'F:UCAS_Backuparchive_db_%d_S_%s_P_%p_T_%t'; Starting backup at 17-APR-22 using target database control file instead of recovery catalog allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=122 device type=DISK channel ORA_DISK_1: starting compressed archived log backup set channel ORA_DISK_1: specifying archived log(s) in backup set input archived log thread=1 sequence=10998 RECID=22075 STAMP=1102225346 input archived log thread=1 sequence=10999 RECID=22077 STAMP=1102225382 input archived log thread=1 sequence=11000 RECID=22079 STAMP=1102225419 input archived log thread=1 sequence=11001 RECID=22081 STAMP=1102225420 input archived log thread=1 sequence=11002 RECID=22083 STAMP=1102225420 input archived log thread=1 sequence=11003 RECID=22085 STAMP=1102225478 input archived log thread=1 sequence=11004 RECID=22088 STAMP=1102258201 input archived log thread=1 sequence=11005 RECID=22089 STAMP=1102258812 input archived log thread=1 sequence=11006 RECID=22096 STAMP=1102259392 input archived log thread=1 sequence=11007 RECID=22099 STAMP=1102259762 input archived log thread=1 sequence=11008 RECID=22101 STAMP=1102260518 input archived log thread=1 sequence=11009 RECID=22103 STAMP=1102260924 input archived log thread=1 sequence=11010 RECID=22105 STAMP=1102261675 input archived log thread=1 sequence=11011 RECID=22107 STAMP=1102262044 input archived log thread=1 sequence=11012 RECID=22109 STAMP=1102262432 input archived log thread=1 sequence=11013 RECID=22111 STAMP=1102262801 input archived log thread=1 sequence=11014 RECID=22113 STAMP=1102263246 channel ORA_DISK_1: starting piece 1 at 17-APR-22 channel ORA_DISK_1: finished piece 1 at 17-APR-22 piece handle=F:UCAS_BACKUPARCHIVE_DB_UCAS_S_8716_P_1_T_1102263513 tag=TAG20220417T161833 comment=NONE channel ORA_DISK_1: backup set complete, elapsed time: 00:00:07 Finished backup at 17-APR-22 RMAN> exit Recovery Manager complete. F:UCAS_Backup> F:UCAS_Backup> F:UCAS_Backup>pscp.exe ARCHIVE* oracle@test-machine02:/u01/db_pump oracle@meducas2db1's password: ARCHIVE_DB_UCAS_S_8716_P_ | 44656 kB | 388.3 kB/s | ETA: 00:00:00 | 100% F:UCAS_Backup> F:UCAS_Backup>
Standby DB
oracle@test-machine02 archive]$ pwd /u01/db_pump/archive [oracle@test-machine02 archive]$ rman target / Recovery Manager: Release 11.2.0.4.0 - Production on Sun Apr 17 16:27:26 2022 Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved. connected to target database: UCAS (DBID=2026517590, not open) RMAN> RMAN> CATALOG BACKUPPIECE '/u01/db_pump/ARCHIVE_DB_UCAS_S_8716_P_1_T_1102263513'; Starting implicit crosscheck backup at 17-APR-22 using target database control file instead of recovery catalog allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=391 device type=DISK Crosschecked 11 objects Finished implicit crosscheck backup at 17-APR-22 Starting implicit crosscheck copy at 17-APR-22 using channel ORA_DISK_1 Finished implicit crosscheck copy at 17-APR-22 searching for all files in the recovery area cataloging files... no files cataloged cataloged backup piece backup piece handle=/u01/db_pump/ARCHIVE_DB_UCAS_S_8716_P_1_T_1102263513 RECID=12 STAMP=1102264055 RMAN> run { set archivelog destination to '/u01/db_pump/archive'; restore archivelog from logseq=10998 until logseq=11014 thread=1; }2> 3> 4> executing command: SET ARCHIVELOG DESTINATION Starting restore at 17-APR-22 using channel ORA_DISK_1 archived log for thread 1 with sequence 10999 is already on disk as file /backup/fast_recovery_area/ucas/archivelog/log_10999_1_1019388628.arc archived log for thread 1 with sequence 11000 is already on disk as file /backup/fast_recovery_area/ucas/archivelog/log_11000_1_1019388628.arc archived log for thread 1 with sequence 11001 is already on disk as file /backup/fast_recovery_area/ucas/archivelog/log_11001_1_1019388628.arc archived log for thread 1 with sequence 11002 is already on disk as file /backup/fast_recovery_area/ucas/archivelog/log_11002_1_1019388628.arc archived log for thread 1 with sequence 11003 is already on disk as file /backup/fast_recovery_area/ucas/archivelog/log_11003_1_1019388628.arc archived log for thread 1 with sequence 11005 is already on disk as file /backup/fast_recovery_area/ucas/archivelog/log_11005_1_1019388628.arc archived log for thread 1 with sequence 11006 is already on disk as file /backup/fast_recovery_area/ucas/archivelog/log_11006_1_1019388628.arc archived log for thread 1 with sequence 11007 is already on disk as file /backup/fast_recovery_area/ucas/archivelog/log_11007_1_1019388628.arc archived log for thread 1 with sequence 11008 is already on disk as file /backup/fast_recovery_area/ucas/archivelog/log_11008_1_1019388628.arc archived log for thread 1 with sequence 11009 is already on disk as file /backup/fast_recovery_area/ucas/archivelog/log_11009_1_1019388628.arc archived log for thread 1 with sequence 11010 is already on disk as file /backup/fast_recovery_area/ucas/archivelog/log_11010_1_1019388628.arc archived log for thread 1 with sequence 11011 is already on disk as file /backup/fast_recovery_area/ucas/archivelog/log_11011_1_1019388628.arc archived log for thread 1 with sequence 11012 is already on disk as file /backup/fast_recovery_area/ucas/archivelog/log_11012_1_1019388628.arc archived log for thread 1 with sequence 11013 is already on disk as file /backup/fast_recovery_area/ucas/archivelog/log_11013_1_1019388628.arc archived log for thread 1 with sequence 11014 is already on disk as file /backup/fast_recovery_area/ucas/archivelog/log_11014_1_1019388628.arc channel ORA_DISK_1: starting archived log restore to user-specified destination archived log destination=/u01/db_pump/archive channel ORA_DISK_1: restoring archived log archived log thread=1 sequence=10998 channel ORA_DISK_1: restoring archived log archived log thread=1 sequence=11004 channel ORA_DISK_1: reading from backup piece /u01/db_pump/ARCHIVE_DB_UCAS_S_8716_P_1_T_1102263513 channel ORA_DISK_1: piece handle=/u01/db_pump/ARCHIVE_DB_UCAS_S_8716_P_1_T_1102263513 tag=TAG20220417T161833 channel ORA_DISK_1: restored backup piece 1 channel ORA_DISK_1: restore complete, elapsed time: 00:00:07 Finished restore at 17-APR-22 RMAN> run { set archivelog destination to '/u01/db_pump/archive'; restore archivelog from logseq=10998 until logseq=11014 thread=1 force; }2> 3> 4> executing command: SET ARCHIVELOG DESTINATION Starting restore at 17-APR-22 using channel ORA_DISK_1 channel ORA_DISK_1: starting archived log restore to user-specified destination archived log destination=/u01/db_pump/archive channel ORA_DISK_1: restoring archived log archived log thread=1 sequence=10998 channel ORA_DISK_1: restoring archived log archived log thread=1 sequence=10999 channel ORA_DISK_1: restoring archived log archived log thread=1 sequence=11000 channel ORA_DISK_1: restoring archived log archived log thread=1 sequence=11001 channel ORA_DISK_1: restoring archived log archived log thread=1 sequence=11002 channel ORA_DISK_1: restoring archived log archived log thread=1 sequence=11003 channel ORA_DISK_1: restoring archived log archived log thread=1 sequence=11004 channel ORA_DISK_1: restoring archived log archived log thread=1 sequence=11005 channel ORA_DISK_1: restoring archived log archived log thread=1 sequence=11006 channel ORA_DISK_1: restoring archived log archived log thread=1 sequence=11007 channel ORA_DISK_1: restoring archived log archived log thread=1 sequence=11008 channel ORA_DISK_1: restoring archived log archived log thread=1 sequence=11009 channel ORA_DISK_1: restoring archived log archived log thread=1 sequence=11010 channel ORA_DISK_1: restoring archived log archived log thread=1 sequence=11011 channel ORA_DISK_1: restoring archived log archived log thread=1 sequence=11012 channel ORA_DISK_1: restoring archived log archived log thread=1 sequence=11013 channel ORA_DISK_1: restoring archived log archived log thread=1 sequence=11014 channel ORA_DISK_1: reading from backup piece /u01/db_pump/ARCHIVE_DB_UCAS_S_8716_P_1_T_1102263513 channel ORA_DISK_1: piece handle=/u01/db_pump/ARCHIVE_DB_UCAS_S_8716_P_1_T_1102263513 tag=TAG20220417T161833 channel ORA_DISK_1: restored backup piece 1 channel ORA_DISK_1: restore complete, elapsed time: 00:00:15 Finished restore at 17-APR-22 RMAN>
SQL> SQL> alter database recover managed standby database cancel; Database altered. SQL> alter database register logfile '/u01/db_pump/archive/log_11010_1_1019388628.arc'; alter database register logfile '/u01/db_pump/archive/log_11010_1_1019388628.arc' * ERROR at line 1: ORA-16089: archive log has already been registered SQL> alter database register or replace logfile '/u01/db_pump/archive/log_11010_1_1019388628.arc'; alter database register or replace logfile '/u01/db_pump/archive/log_11014_1_1019388628.arc'; alter database register or replace logfile '/u01/db_pump/archive/log_11005_1_1019388628.arc'; alter database register or replace logfile '/u01/db_pump/archive/log_11009_1_1019388628.arc'; alter database register or replace logfile '/u01/db_pump/archive/log_11012_1_1019388628.arc'; alter database register or replace logfile '/u01/db_pump/archive/log_11011_1_1019388628.arc'; alter database register or replace logfile '/u01/db_pump/archive/log_11007_1_1019388628.arc'; alter database register or replace logfile '/u01/db_pump/archive/log_11003_1_1019388628.arc'; alter database register or replace logfile '/u01/db_pump/archive/log_11013_1_1019388628.arc'; alter database register or replace logfile '/u01/db_pump/archive/log_11000_1_1019388628.arc'; alter database register or replace logfile '/u01/db_pump/archive/log_10999_1_1019388628.arc'; alter database register or replace logfile '/u01/db_pump/archive/log_11001_1_1019388628.arc'; alter database register or replace logfile '/u01/db_pump/archive/log_11006_1_1019388628.arc'; alter database register or replace logfile '/u01/db_pump/archive/log_11008_1_1019388628.arc'; alter database register or replace logfile '/u01/db_pump/archive/log_11002_1_1019388628.arc'; alter database register or replace logfile '/u01/db_pump/archive/log_10998_1_1019388628.arc'; alter database register or replace logfile '/u01/db_pump/archive/log_11004_1_1019388628.arc'; Database altered. SQL> Database altered. SQL> Database altered. SQL> Database altered. SQL> Database altered. SQL> Database altered. SQL> Database altered. SQL> Database altered. SQL> Database altered. SQL> Database altered. SQL> Database altered. SQL> Database altered. SQL> Database altered. SQL> Database altered. SQL> Database altered. SQL> Database altered. SQL> Database altered. SQL> SQL> SQL> SQL> SQL> SQL> alter database recover managed standby database using current logfile disconnect from session; Database altered. SQL> set lines 200 set pages 200 select inst_id,process,status,client_process,THREAD#,sequence#,block#,active_agents,known_agents from gv$managed_standby where PROCESS like 'MRP%' or client_process like 'LGWR'; SQL> SQL> INST_ID PROCESS STATUS CLIENT_P THREAD# SEQUENCE# BLOCK# ACTIVE_AGENTS KNOWN_AGENTS ---------- --------- ------------ -------- ---------- ---------- ---------- ------------- ------------ 1 MRP0 APPLYING_LOG N/A 1 11005 1576 5 5 1 RFS IDLE LGWR 1 11015 1326 0 0 SQL> / INST_ID PROCESS STATUS CLIENT_P THREAD# SEQUENCE# BLOCK# ACTIVE_AGENTS KNOWN_AGENTS ---------- --------- ------------ -------- ---------- ---------- ---------- ------------- ------------ 1 MRP0 APPLYING_LOG N/A 1 11005 1576 5 5 1 RFS IDLE LGWR 1 11015 1329 0 0 SQL> / INST_ID PROCESS STATUS CLIENT_P THREAD# SEQUENCE# BLOCK# ACTIVE_AGENTS KNOWN_AGENTS ---------- --------- ------------ -------- ---------- ---------- ---------- ------------- ------------ 1 MRP0 APPLYING_LOG N/A 1 11005 1576 5 5 1 RFS IDLE LGWR 1 11015 1330 0 0 SQL> / SQL> SQL> SELECT PROCESS, STATUS, THREAD#, SEQUENCE#, BLOCK#, BLOCKS FROM V$MANAGED_STANDBY; PROCESS STATUS THREAD# SEQUENCE# BLOCK# BLOCKS --------- ------------ ---------- ---------- ---------- ---------- ARCH CLOSING 1 11013 2048 1312 ARCH CONNECTED 0 0 0 0 ARCH CLOSING 1 11015 1 1429 ARCH CLOSING 1 11011 1 398 ARCH CLOSING 1 11012 1 434 MRP0 APPLYING_LOG 1 11016 58 1024000 RFS IDLE 0 0 0 0 RFS IDLE 1 11016 58 1 8 rows selected. SQL>
Primary DB
SQL> SQL> SQL> set lines 300 SQL> col destination for a40 SQL> col error for a20 SQL> select inst_id, dest_id "ID",destination,status,error,target, 2 schedule,process,mountid mid 3 from gv$archive_dest 4 where dest_id < 4 5 order by dest_id; INST_ID ID DESTINATION STATUS ERROR TARGET SCHEDULE PROCESS MID ---------- ---------- ---------------------------------------- --------- -------------------- ------- -------- ---------- ---------- 1 1 I:FLASH_RECOVERY_AREAUCASARCHIVELOG VALID PRIMARY ACTIVE ARCH 0 1 2 ucasdr VALID STANDBY ACTIVE LGWR 0 1 3 INACTIVE PRIMARY INACTIVE ARCH 0 SQL> SQL> SQL> SQL> SQL> SQL> set lines 200 SQL> set pages 200 SQL> select inst_id,process,status,client_process,THREAD#,sequence#,block#,active_agents,known_agents from gv$managed_standby where PROCESS like '%LNS %' or client_process like 'LGWR'; INST_ID PROCESS STATUS CLIENT_P THREAD# SEQUENCE# BLOCK# ACTIVE_AGENTS KNOWN_AGENTS ---------- --------- ------------ -------- ---------- ---------- ---------- ------------- ------------ 1 LNS WRITING LNS 1 11016 113 0 0 SQL> SQL>
This document is just for learning purposes and always validate in the LAB environment first before applying in the LIVE environment.
Hope so you like this article!