After standby was setup, recovery failed with error ORA-00308,ORA-17503 and ORA-15045.
SQL> recover standby database;
ORA-00279: change 78501137 generated at needed for thread 1
ORA-00289: suggestion : +TESTPDB_DATA
ORA-00280: change 78501137 for thread 1 is in sequence #38114
Specify log: {=suggested | filename | AUTO | CANCEL}
auto
ORA-00308: cannot open archived log '+TESTPDB_DATA'
ORA-17503: ksfdopn:2 Failed to open file +TESTPDB_DATA
ORA-15045: ASM file name '+TESTPDB_DATA' is not in reference form
ORA-00308: cannot open archived log '+TESTPDB_DATA'
ORA-17503: ksfdopn:2 Failed to open file +TESTPDB_DATA
ORA-15045: ASM file name '+TESTPDB_DATA' is not in reference form
ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below
ORA-01152: file 1 was not restored from a sufficiently old backup
ORA-01110: data file 1: '+GUYSDDD6_DATA/SANGAMDP/DATAFILE/system.279.960261095'
1. Check the status v$managed_standby on DR, only arch process was running
SQL> select PROCESS,PID,STATUS,THREAD#,SEQUENCE# from v$managed_standby;
PROCESS PID STATUS THREAD# SEQUENCE#
--------- ------------------------ ------------ ---------- ----------
ARCH 112962 CLOSING 1 38124
ARCH 112965 CONNECTED 0 0
ARCH 112967 CONNECTED 0 0
ARCH 112969 CONNECTED 0 0
ARCH 112971 CONNECTED 0 0
ARCH 112973 CONNECTED 0 0
2. Checked the v$dataguard_status on DR, it gave the below information.
SQL> select dest_id,message from v$dataguard_status;
DEST_ID
----------
MESSAGE
--------------------------------------------------------------------------------
0
ARC0: Archival started
0
ARC1: Archival started
0
ARC2: Archival started
...
...
...
ARC2: Becoming the active heartbeat ARCH
0
ARCj: Archival started
0
Error 12154 received logging on to the standby
DEST_ID
----------
MESSAGE
--------------------------------------------------------------------------------
0
FAL[client, ARC5]: Error 12154 connecting to sangamdp for fetching gap sequence
25 rows selected.
Solution:
sangamdp is the standby database here. As per above message, primary is failing to connect to standby database.so possible reason, tns entry is missing in the primary nodes. so copy standby connection string on all nodes if it's RAC.
Standby TNS entry:
sangamdp =(DESCRIPTION =(ADDRESS_LIST =(ADDRESS=(PROTOCOL= TCP)(HOST=scan-sangam-db.com)(PORT = 1521)))(CONNECT_DATA =(SERVER = DEDICATED)(SERVICE_NAME = sangamdp)))
Once done, check the v$dataguard_status and v$managed_standby views.
SQL> select PROCESS,PID,STATUS,THREAD#,SEQUENCE# from v$managed_standby;
PROCESS PID STATUS THREAD# SEQUENCE#
--------- ------------------------ ------------ ---------- ----------
ARCH 112962 CLOSING 1 38124
ARCH 112965 CONNECTED 0 0
ARCH 112967 CONNECTED 0 0
ARCH 112969 CONNECTED 0 0
....
....
ARCH 113000 CONNECTED 0 0
ARCH 113002 CONNECTED 0 0
RFS 138923 IDLE 0 0
RFS 138727 IDLE 0 0
PROCESS PID STATUS THREAD# SEQUENCE#
--------- ------------------------ ------------ ---------- ----------
RFS 138729 IDLE 0 0
RFS 138731 IDLE 0 0
SQL> select dest_id,message from v$dataguard_status;
DEST_ID
----------
MESSAGE
--------------------------------------------------------------------------------
0
ARC0: Archival started
0
ARC1: Archival started
0
ARC2: Archival started
....
....
ARC0: Beginning to archive thread 1 sequence 38124 (78521200-78522852)
DEST_ID
----------
MESSAGE
--------------------------------------------------------------------------------
0
ARC0: Completed archiving thread 1 sequence 38124 (0-0)
0
RFS[13]: Assigned to RFS process (PID:3960)
0
RFS[14]: Assigned to RFS process (PID:3964)
....
....
RFS[20]: Assigned to RFS process (PID:4119)
0
RFS[21]: Assigned to RFS process (PID:4186)
0
ARC1: Beginning to archive thread 2 sequence 38155 (78521195-78523909)
DEST_ID
----------
MESSAGE
--------------------------------------------------------------------------------
0
ARC1: Completed archiving thread 2 sequence 38155 (0-0)
52 rows selected.
.....
Now RFS process is working.. Now try manual recovery
SQL> recover standby database;
ORA-00279: change 78501137 generated at 11/17/2017 03:04:46 needed for thread 1
ORA-00289: suggestion :
+TESTPDB_DATA/SANGAMDP/ARCHIVELOG/2017_11_17/thread_1_seq_38114.1957.960267717
ORA-00280: change 78501137 for thread 1 is in sequence #38114
Specify log: {=suggested | filename | AUTO | CANCEL}
auto
ORA-00279: change 78501137 generated at 11/17/2017 03:04:44 needed for thread 2
ORA-00289: suggestion :
+TESTPDB_DATA/SANGAMDP/ARCHIVELOG/2017_11_17/thread_2_seq_38148.1969.960268039
ORA-00280: change 78501137 for thread 2 is in sequence #38148
ORA-00279: change 78503518 generated at 11/17/2017 03:21:21 needed for thread 1
ORA-00289: suggestion :
+TESTPDB_DATA/SANGAMDP/ARCHIVELOG/2017_11_17/thread_1_seq_38115.1959.960267717
ORA-00280: change 78503518 for thread 1 is in sequence #38115
ORA-00278: log file
'+TESTPDB_DATA/SANGAMDP/ARCHIVELOG/2017_11_17/thread_1_seq_38114.1957.960267717'
no longer needed for this recovery
...
....
ORA-00279: change 78522852 generated at 11/17/2017 05:01:53 needed for thread 1
ORA-00289: suggestion : +TESTPDB_DATA
ORA-00280: change 78522852 for thread 1 is in sequence #38125
ORA-00278: log file
'+TESTPDB_DATA/SANGAMDP/ARCHIVELOG/2017_11_17/thread_1_seq_38124.1964.960267723'
no longer needed for this recovery
ORA-16145: archival for thread# 1 sequence# 38125 in progress
Now it's working fine.
Once manual recovery is done, keep the database in management mode.
Recover managed standby database disconnect from session;
SQL> SQL> recover managed standby database disconnect from session;
Media recovery complete.
SQL>
SQL> select PROCESS,PID,STATUS,THREAD#,SEQUENCE# from v$managed_standby;
PROCESS PID STATUS THREAD# SEQUENCE#
--------- ------------------------ ------------ ---------- ----------
ARCH 112962 CLOSING 1 38124
ARCH 112965 CLOSING 2 38155
ARCH 112967 CONNECTED 0 0
....
....
....
RFS 4119 IDLE 2 38156
RFS 4186 IDLE 0 0
MRP0 43199 APPLYING_LOG 2 38156
44 rows selected.
SQL> recover standby database;
ORA-00279: change 78501137 generated at needed for thread 1
ORA-00289: suggestion : +TESTPDB_DATA
ORA-00280: change 78501137 for thread 1 is in sequence #38114
Specify log: {
auto
ORA-00308: cannot open archived log '+TESTPDB_DATA'
ORA-17503: ksfdopn:2 Failed to open file +TESTPDB_DATA
ORA-15045: ASM file name '+TESTPDB_DATA' is not in reference form
ORA-00308: cannot open archived log '+TESTPDB_DATA'
ORA-17503: ksfdopn:2 Failed to open file +TESTPDB_DATA
ORA-15045: ASM file name '+TESTPDB_DATA' is not in reference form
ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below
ORA-01152: file 1 was not restored from a sufficiently old backup
ORA-01110: data file 1: '+GUYSDDD6_DATA/SANGAMDP/DATAFILE/system.279.960261095'
1. Check the status v$managed_standby on DR, only arch process was running
SQL> select PROCESS,PID,STATUS,THREAD#,SEQUENCE# from v$managed_standby;
PROCESS PID STATUS THREAD# SEQUENCE#
--------- ------------------------ ------------ ---------- ----------
ARCH 112962 CLOSING 1 38124
ARCH 112965 CONNECTED 0 0
ARCH 112967 CONNECTED 0 0
ARCH 112969 CONNECTED 0 0
ARCH 112971 CONNECTED 0 0
ARCH 112973 CONNECTED 0 0
2. Checked the v$dataguard_status on DR, it gave the below information.
SQL> select dest_id,message from v$dataguard_status;
DEST_ID
----------
MESSAGE
--------------------------------------------------------------------------------
0
ARC0: Archival started
0
ARC1: Archival started
0
ARC2: Archival started
...
...
...
ARC2: Becoming the active heartbeat ARCH
0
ARCj: Archival started
0
Error 12154 received logging on to the standby
DEST_ID
----------
MESSAGE
--------------------------------------------------------------------------------
0
FAL[client, ARC5]: Error 12154 connecting to sangamdp for fetching gap sequence
25 rows selected.
Solution:
sangamdp is the standby database here. As per above message, primary is failing to connect to standby database.so possible reason, tns entry is missing in the primary nodes. so copy standby connection string on all nodes if it's RAC.
Standby TNS entry:
sangamdp =(DESCRIPTION =(ADDRESS_LIST =(ADDRESS=(PROTOCOL= TCP)(HOST=scan-sangam-db.com)(PORT = 1521)))(CONNECT_DATA =(SERVER = DEDICATED)(SERVICE_NAME = sangamdp)))
Once done, check the v$dataguard_status and v$managed_standby views.
SQL> select PROCESS,PID,STATUS,THREAD#,SEQUENCE# from v$managed_standby;
PROCESS PID STATUS THREAD# SEQUENCE#
--------- ------------------------ ------------ ---------- ----------
ARCH 112962 CLOSING 1 38124
ARCH 112965 CONNECTED 0 0
ARCH 112967 CONNECTED 0 0
ARCH 112969 CONNECTED 0 0
....
....
ARCH 113000 CONNECTED 0 0
ARCH 113002 CONNECTED 0 0
RFS 138923 IDLE 0 0
RFS 138727 IDLE 0 0
PROCESS PID STATUS THREAD# SEQUENCE#
--------- ------------------------ ------------ ---------- ----------
RFS 138729 IDLE 0 0
RFS 138731 IDLE 0 0
SQL> select dest_id,message from v$dataguard_status;
DEST_ID
----------
MESSAGE
--------------------------------------------------------------------------------
0
ARC0: Archival started
0
ARC1: Archival started
0
ARC2: Archival started
....
....
ARC0: Beginning to archive thread 1 sequence 38124 (78521200-78522852)
DEST_ID
----------
MESSAGE
--------------------------------------------------------------------------------
0
ARC0: Completed archiving thread 1 sequence 38124 (0-0)
0
RFS[13]: Assigned to RFS process (PID:3960)
0
RFS[14]: Assigned to RFS process (PID:3964)
....
....
RFS[20]: Assigned to RFS process (PID:4119)
0
RFS[21]: Assigned to RFS process (PID:4186)
0
ARC1: Beginning to archive thread 2 sequence 38155 (78521195-78523909)
DEST_ID
----------
MESSAGE
--------------------------------------------------------------------------------
0
ARC1: Completed archiving thread 2 sequence 38155 (0-0)
52 rows selected.
.....
Now RFS process is working.. Now try manual recovery
SQL> recover standby database;
ORA-00279: change 78501137 generated at 11/17/2017 03:04:46 needed for thread 1
ORA-00289: suggestion :
+TESTPDB_DATA/SANGAMDP/ARCHIVELOG/2017_11_17/thread_1_seq_38114.1957.960267717
ORA-00280: change 78501137 for thread 1 is in sequence #38114
Specify log: {
auto
ORA-00279: change 78501137 generated at 11/17/2017 03:04:44 needed for thread 2
ORA-00289: suggestion :
+TESTPDB_DATA/SANGAMDP/ARCHIVELOG/2017_11_17/thread_2_seq_38148.1969.960268039
ORA-00280: change 78501137 for thread 2 is in sequence #38148
ORA-00279: change 78503518 generated at 11/17/2017 03:21:21 needed for thread 1
ORA-00289: suggestion :
+TESTPDB_DATA/SANGAMDP/ARCHIVELOG/2017_11_17/thread_1_seq_38115.1959.960267717
ORA-00280: change 78503518 for thread 1 is in sequence #38115
ORA-00278: log file
'+TESTPDB_DATA/SANGAMDP/ARCHIVELOG/2017_11_17/thread_1_seq_38114.1957.960267717'
no longer needed for this recovery
...
....
ORA-00279: change 78522852 generated at 11/17/2017 05:01:53 needed for thread 1
ORA-00289: suggestion : +TESTPDB_DATA
ORA-00280: change 78522852 for thread 1 is in sequence #38125
ORA-00278: log file
'+TESTPDB_DATA/SANGAMDP/ARCHIVELOG/2017_11_17/thread_1_seq_38124.1964.960267723'
no longer needed for this recovery
ORA-16145: archival for thread# 1 sequence# 38125 in progress
Now it's working fine.
Once manual recovery is done, keep the database in management mode.
Recover managed standby database disconnect from session;
SQL> SQL> recover managed standby database disconnect from session;
Media recovery complete.
SQL>
SQL> select PROCESS,PID,STATUS,THREAD#,SEQUENCE# from v$managed_standby;
PROCESS PID STATUS THREAD# SEQUENCE#
--------- ------------------------ ------------ ---------- ----------
ARCH 112962 CLOSING 1 38124
ARCH 112965 CLOSING 2 38155
ARCH 112967 CONNECTED 0 0
....
....
....
RFS 4119 IDLE 2 38156
RFS 4186 IDLE 0 0
MRP0 43199 APPLYING_LOG 2 38156
44 rows selected.
