Problem:-
Standby DB not applying logs.
+++++++
Primary DB setup details:-
+++++++
SQL> sho parameter log_archive_dest_;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_1 string LOCATION=USE_DB_RECOVERY_FILE_
DEST VALID_FOR=(ALL_LOGFILES,A
LL_ROLES) DB_UNIQUE_NAME=odevd
bp1
log_archive_dest_2 string service=TO_odevdbs1 LGWR ASYNC
VALID_FOR=(ALL_LOGFILES,PRIMA
RY_ROLE) DB_UNIQUE_NAME=odevdb
s1
SQL> sho parameter name;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
cdb_cluster_name string
cell_offloadgroup_name string
db_file_name_convert string
db_name string odevdb
db_unique_name string odevdbp1
global_names boolean FALSE
instance_name string odevdb
lock_name_space string
log_file_name_convert string
pdb_file_name_convert string
processor_group_name string
service_names string odevdbp1
SQL> sho parameter FAL;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
fal_client string odevdbp1
fal_server string odevdbs1
[oracle@oracledbvm1 admin]$ cat listener.ora
# listener.ora Network Configuration File: /u01/orasoft/app/oracle/product/oradb_19/db_1/network/admin/listener.or a
# Generated by Oracle configuration tools.
SID_LIST_LISTENER_odevdb =
(SID_LIST =
(SID_DESC =
(SID_NAME = odevdb)
(ORACLE_HOME = /u01/orasoft/app/oracle/product/oradb_19/db_1)
)
)
LISTENER_odevdb =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = oracledbvm1)(PORT = 1522))
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1522))
)
)
[oracle@oracledbvm1 admin]$
[oracle@oracledbvm1 admin]$
[oracle@oracledbvm1 admin]$ cat tnsnames.ora
TO_odevdbs1 =
(description =
(address = (protocol = TCP)(host = oracledbvm2)(port = 1522))
(CONNECT_DATA =
(SERVICE_NAME = odevdbs1)
(SERVER = DEDICATED)
)
)
TO_odevdbp1 =
(description =
(address = (protocol = TCP)(host = oracledbvm1)(port = 1522))
(CONNECT_DATA =
(SERVICE_NAME = odevdbp1)
(SERVER = DEDICATED)
)
)
alter system set log_archive_dest_1='LOCATION=USE_DB_RECOVERY_FILE_DEST VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=odevdbp1' SCOPE=both;
alter system set log_archive_dest_2='service=TO_odevdbs1 LGWR ASYNC VALID_FOR=(ALL_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=odevdbs1' SCOPE=both;
alter system set log_archive_config='DG_CONFIG=(odevdbp1,odevdbs1)';
+++++++
Standby DB setup details:-
+++++++
SQL> sho parameter log_archive_dest_;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_1 string LOCATION=USE_DB_RECOVERY_FILE_
DEST VALID_FOR=(ALL_LOGFILES,A
LL_ROLES) DB_UNIQUE_NAME=odevd
bs1
log_archive_dest_2 string service=TO_odevdbp1 LGWR ASYNC
VALID_FOR=(ALL_LOGFILES,PRIMA
RY_ROLE) DB_UNIQUE_NAME=odevdb
p1
SQL> sho parameter name;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
cdb_cluster_name string
cell_offloadgroup_name string
db_file_name_convert string
db_name string odevdb
db_unique_name string odevdbs1
global_names boolean FALSE
instance_name string odevdb
lock_name_space string
log_file_name_convert string
pdb_file_name_convert string
processor_group_name string
service_names string odevdbs1
SQL> sho parameter FAL;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
fal_client string odevdbs1
fal_server string odevdbp1
[oracle@oracledbvm2 admin]$ cat listener.ora
# listener.ora Network Configuration File: /u01/orasoft/app/oracle/product/oradb_19/db_1/network/admin/listener.or a
# Generated by Oracle configuration tools.
SID_LIST_LISTENER_odevdb =
(SID_LIST =
(SID_DESC =
(SID_NAME = odevdb)
(ORACLE_HOME = /u01/orasoft/app/oracle/product/oradb_19/db_1)
)
)
LISTENER_odevdb =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = oracledbvm2)(PORT = 1522))
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1522))
)
)
[oracle@oracledbvm2 admin]$
[oracle@oracledbvm2 admin]$
[oracle@oracledbvm2 admin]$ cat tnsnames.ora
TO_odevdbs1 =
(description =
(address = (protocol = TCP)(host = oracledbvm2)(port = 1522))
(CONNECT_DATA =
(SERVICE_NAME = odevdbs1)
(SERVER = DEDICATED)
)
)
TO_odevdbp1 =
(description =
(address = (protocol = TCP)(host = oracledbvm1)(port = 1522))
(CONNECT_DATA =
(SERVICE_NAME = odevdbp1)
(SERVER = DEDICATED)
)
)
alter system set log_archive_dest_1='LOCATION=USE_DB_RECOVERY_FILE_DEST VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=odevdbs1' SCOPE=both;
alter system set log_archive_dest_2='service=TO_odevdbp1 LGWR ASYNC VALID_FOR=(ALL_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=odevdbp1' SCOPE=both;
alter system set log_archive_config='DG_CONFIG=(odevdbs1,odevdbp1)';
+++++++
Queries run on Standby DB showing output:
+++++++
SQL> select * from v$archive_gap;
THREAD# LOW_SEQUENCE# HIGH_SEQUENCE# CON_ID
---------- ------------- -------------- ----------
1 48 70 1
SQL> select process, status from v$managed_standby;
PROCESS STATUS
--------- ------------
ARCH CONNECTED
DGRD ALLOCATED
DGRD ALLOCATED
ARCH CONNECTED
ARCH CLOSING
ARCH CONNECTED
ARCH CONNECTED
ARCH CONNECTED
ARCH CONNECTED
ARCH CONNECTED
ARCH CONNECTED
PROCESS STATUS
--------- ------------
ARCH CONNECTED
RFS IDLE
RFS IDLE
14 rows selected.
SQL> SELECT THREAD#, SEQUENCE#, APPLIED FROM V$ARCHIVED_LOG;
THREAD# SEQUENCE# APPLIED
---------- ---------- ---------
1 46 YES
1 47 YES
1 71 NO
1 72 NO
1 73 NO
1 74 NO
1 75 NO
1 76 NO
1 78 NO
9 rows selected.
SQL> select message from v$dataguard_status;
MESSAGE
--------------------------------------------------------------------------------
Redo network throttle feature is disabled at mount time
STARTING ARCH PROCESSES
ARC0: Archival started
STARTING ARCH PROCESSES COMPLETE
Gap Manager starting
Becoming a 'no FAL' ARCH
STARTING ARCH PROCESSES
ARC1: Archival started
ARC2: Archival started
ARC3: Archival started
ARC4: Archival started
MESSAGE
--------------------------------------------------------------------------------
ARC5: Archival started
ARC6: Archival started
ARC7: Archival started
ARC8: Archival started
ARC9: Archival started
STARTING ARCH PROCESSES COMPLETE
Archiving previously deferred ORLs
Primary database is in MAXIMUM PERFORMANCE mode
Selected LNO:4 for T-1.S-78 dbid 1386340627 branch 1134067091
Beginning to archive T-1.S-78 (SCN:0x00000000000eb8f3-SCN:0x00000000000ebba2)
Completed archiving T-1.S-78 (SCN:0x0000000000000000-SCN:0x0000000000000000)
MESSAGE
--------------------------------------------------------------------------------
Primary database is in MAXIMUM PERFORMANCE mode
Selected LNO:4 for T-1.S-79 dbid 1386340627 branch 1134067091
24 rows selected.
KINDLY ADVISE!