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: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
!

Related posts

How to deal with ORA-00020: maximum number of processes (%s) exceeded

How to resolve ORA-01111 ORA-01110 ORA-01157 in a physical standby database 

Công cụ tự động khai báo datafile – Oracle