Pages

Thursday, 19 April 2018

After standby was setup, recovery failed with error ORA-00308,ORA-17503 and ORA-15045

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.

No comments:

Post a Comment