Steps to create a Heterogeneous Dataguard between Windows and Linux
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:\UCASNEW\UCASNEW\ H:\UCASNEW\ORADATA_2\ | G:\UCASNEW\ORADATA_1\ H:\UCASNEW\ORADATA_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:\UCASNEW\UCASNEW\SYSTEM01.DBF
G:\UCASNEW\UCASNEW\SYSAUX01.DBF
G:\UCASNEW\UCASNEW\UNDOTBS01.DBF
G:\UCASNEW\UCASNEW\USERS01.DBF
H:\UCASNEW\ORADATA_2\UCAS_01.DBF
H:\UCASNEW\ORADATA_2\UCAS_DATA_01.DBF
H:\UCASNEW\ORADATA_2\UCAS_INDEX_01.DBF
H:\UCASNEW\ORADATA_2\GGS_DATA_01.DBF
H:\UCASNEW\ORADATA_2\TELEX_01.DBF
H:\UCASNEW\ORADATA_2\UCAS_02.DBF
H:\UCASNEW\ORADATA_2\UCAS_DATA_02.DBF
H:\UCASNEW\ORADATA_2\UCAS_INDEX_02.DBF
12 rows selected.
SQL>
SQL> select member from v$logfile;
MEMBER
----------------------------------------
H:\UCASNEW\ORADATA_1\REDO04.LOG
H:\UCASNEW\ORADATA_1\REDO05.LOG
G:\UCASNEW\ORADATA_1\REDO06.LOG
G:\UCASNEW\ORADATA_1\REDO07.LOG
4 rows selected.
SQL>
################ Primary DB Init Parameters #################
*.log_archive_config='DG_CONFIG=(ucas,ucasdr)'
*.log_archive_dest_1='LOCATION=I:\FLASH_RECOVERY_AREA\UCAS\ARCHIVELOG 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:\UCASNEW\ORADATA_1\ ','/data02/oradata_1/','H:\UCASNEW\ORADATA_1\'
*.db_file_name_convert='/data01/oradata_2/ ','G:\UCASNEW\UCASNEW\','/data02/oradata_2/','H:\UCASNEW\ORADATA_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:\Users\jkhan>
C:\Users\jkhan>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:\Oracle\app\product\11.2.0.4\db_1\network\admin\sqlnet.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:\Users\jkhan>
C:\Users\jkhan>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:\Oracle\app\product\11.2.0.4\db_1\network\admin\sqlnet.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:\Users\jkhan>
############ Enable Force Logging in Primary DB ############
C:\Users\jkhan>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:\UCASNEW\ORADATA_1\STDBY_REDO08.LOG' size 524288000;
ALTER DATABASE ADD STANDBY LOGFILE group 9 'H:\UCASNEW\ORADATA_1\STDBY_REDO09.LOG' size 524288000;
ALTER DATABASE ADD STANDBY LOGFILE group 10 'H:\UCASNEW\ORADATA_1\STDBY_REDO09.LOG' size 524288000;
ALTER DATABASE ADD STANDBY LOGFILE group 11 'G:\UCASNEW\ORADATA_1\STDBY_REDO11.LOG' size 524288000;
ALTER DATABASE ADD STANDBY LOGFILE group 12 'G:\UCASNEW\ORADATA_1\STDBY_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:\UCASNEW\ORADATA_1\REDO04.LOG NO
5 ONLINE H:\UCASNEW\ORADATA_1\REDO05.LOG NO
6 ONLINE G:\UCASNEW\ORADATA_1\REDO06.LOG NO
7 ONLINE G:\UCASNEW\ORADATA_1\REDO07.LOG NO
8 STANDBY H:\UCASNEW\ORADATA_1\STDBY_REDO08.LOG NO
9 STANDBY H:\UCASNEW\ORADATA_1\STDBY_REDO09.LOG NO
10 STANDBY G:\UCASNEW\ORADATA_1\STDBY_REDO10.LOG NO
11 STANDBY G:\UCASNEW\ORADATA_1\STDBY_REDO11.LOG NO
12 STANDBY G:\UCASNEW\ORADATA_1\STDBY_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:\UCASNEW\UCASNEW\','/data01/oradata_2/','H:\UCASNEW\ORADATA_2\','/data02/oradata_2/'
*.log_file_name_convert='G:\UCASNEW\ORADATA_1\','/data01/oradata_1/','H:\UCASNEW\ORADATA_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:\Users\jkhan>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_Backup\backup_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_Backup\BACKUP_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_Backup\BACKUP_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_AREA\UCAS\ARCHIVELOG 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:\Oracle\app\product\11.2.0.4\db_1\database>
F:\Oracle\app\product\11.2.0.4\db_1\database>orapwd file=PWDucas.ora password=pass123 entries=5 ignorecase=Y
F:\Oracle\app\product\11.2.0.4\db_1\database>
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:\Users\jkhan>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_Backup\archive_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_BACKUP\ARCHIVE_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_AREA\UCAS\ARCHIVELOG 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!
Please share your valuable feedback/comments/subscribe and follow us below and don’t forget to click on the bell icon to get the most recent update. Click here to understand more about our pursuit.
Related Articles
- Steps to create a Heterogeneous Dataguard between Windows and Linux
- How to Fix ORA-10458 & ORA-01196 in Standby 11g (Roll Forward a Physical Standby Database)
- Add & Drop Standby Redolog Files on Standby (Dataguard) in Oracle
- Steps to Create Active Physical Standby Dataguard Using RMAN Duplicate in Oracle
- Step by Step Physical Standby Database Creation in Oracle
