Monday, November 12, 2007

Resync RAC standby database using ASM

If a RAC standby database using ASM (and also standby logfiles in our case) stays down for a little while, it will likely go behind the primary and under ASM of the standby cluster you'll see these "gaps" in sequence numbers --
ASMCMD [+DATA/PROD/ARCHIVELOG/2007_11_12] > ls
thread_1_seq_652.618.638445679
thread_1_seq_654.619.638466159
thread_1_seq_656.620.638466307
thread_1_seq_658.623.638467267
thread_1_seq_659.624.638467413
thread_1_seq_660.625.638467431
thread_2_seq_262.616.638416837
thread_2_seq_264.617.638445669
thread_2_seq_265.621.638467091
thread_2_seq_266.622.638467229
ASMCMD [+DATA/PROD/ARCHIVELOG/2007_11_12]


Notice that thread 1 (node 1) is missing sequences 653, 655 and 657. Similarly thread 2 is missing sequence#263.
Question is how to bring these missing archivelogs from ASM@primary cluster (they are still sitting on disk @primary)?

Solution: I use a 3-step process there
step 1: Use RMAN "copy" command to copy these to a cooked filesystem on the primary cluster
step 2: Scp the restored (copied) archivelogs to the standby cluster
step 3: Recover (automatic) standby database and supply the scp'ed archivelog under cooked FS.

First, query the ASM@primary to get the exact name/location of these archives --
ASMCMD [+FRA/PROD/ARCHIVELOG/2007_11_12] > ls
thread_1_seq_650.768.638416833
thread_1_seq_651.771.638416841
thread_1_seq_652.781.638445663
thread_1_seq_653.796.638465819
thread_1_seq_654.797.638466159
thread_1_seq_655.798.638466189
thread_1_seq_656.800.638466307
thread_1_seq_657.801.638466455
thread_2_seq_262.769.638416835
thread_2_seq_263.770.638416841
thread_2_seq_264.782.638445665
thread_2_seq_265.799.638466191


step 1: Execute the RMAN copy command (primary cluster). Set the env to the database first.
RMAN> copy archivelog '+FRA/prod/archivelog/2007_11_12/thread_2_seq_263.770.638416841' to '/u02/backup/PROD/thread_2_seq_263.770.638416841';

Starting backup at 12-NOV-07
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=1055 instance=PROD1 devtype=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: sid=1057 instance=PROD1 devtype=DISK
channel ORA_DISK_1: starting archive copy
input archive log thread=2 sequence=263 recid=1412 stamp=638416840
output filename=/u02/backup/PROD/thread_2_seq_263.770.638416841 recid=1426 stamp=638465672
channel ORA_DISK_1: archivelog copy complete, elapsed time: 00:00:02
Finished backup at 12-NOV-07

Starting Control File and SPFILE Autobackup at 12-NOV-07
piece handle=+FRA/prod/autobackup/2007_11_12/s_638465673.795.638465673 comment=NONE
Finished Control File and SPFILE Autobackup at 12-NOV-07

RMAN> copy archivelog '+FRA/PROD/ARCHIVELOG/2007_11_12/thread_1_seq_653.796.638465819' to '/u02/backup/PROD/thread_1_seq_653.796.638465819';

Starting backup at 12-NOV-07
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=1055 instance=PROD1 devtype=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: sid=1081 instance=PROD1 devtype=DISK
channel ORA_DISK_1: starting archive copy
input archive log thread=1 sequence=653 recid=1427 stamp=638465819
output filename=/u02/backup/PROD/thread_1_seq_653.796.638465819 recid=1435 stamp=638466707
channel ORA_DISK_1: archivelog copy complete, elapsed time: 00:00:02
Finished backup at 12-NOV-07

Starting Control File and SPFILE Autobackup at 12-NOV-07
piece handle=+FRA/prod/autobackup/2007_11_12/s_638466708.802.638466709 comment=NONE
Finished Control File and SPFILE Autobackup at 12-NOV-07

RMAN> copy archivelog '+FRA/PROD/ARCHIVELOG/2007_11_12/thread_1_seq_655.798.638466189' to '/u02/backup/PROD/thread_1_seq_655.798.638466189';

Starting backup at 12-NOV-07
using channel ORA_DISK_1
using channel ORA_DISK_2
channel ORA_DISK_1: starting archive copy
input archive log thread=1 sequence=655 recid=1430 stamp=638466189
output filename=/u02/backup/PROD/thread_1_seq_655.798.638466189 recid=1436 stamp=638466745
channel ORA_DISK_1: archivelog copy complete, elapsed time: 00:00:02
Finished backup at 12-NOV-07

Starting Control File and SPFILE Autobackup at 12-NOV-07
piece handle=+FRA/prod/autobackup/2007_11_12/s_638466746.803.638466747 comment=NONE
Finished Control File and SPFILE Autobackup at 12-NOV-07

RMAN> copy archivelog '+FRA/PROD/ARCHIVELOG/2007_11_12/thread_1_seq_657.801.638466455' to '/u02/backup/PROD/thread_1_seq_657.801.638466455';

Starting backup at 12-NOV-07
using channel ORA_DISK_1
using channel ORA_DISK_2
channel ORA_DISK_1: starting archive copy
input archive log thread=1 sequence=657 recid=1434 stamp=638466454
output filename=/u02/backup/PROD/thread_1_seq_657.801.638466455 recid=1437 stamp=638466777
channel ORA_DISK_1: archivelog copy complete, elapsed time: 00:00:02
Finished backup at 12-NOV-07

Starting Control File and SPFILE Autobackup at 12-NOV-07
piece handle=+FRA/prod/autobackup/2007_11_12/s_638466778.804.638466779 comment=NONE
Finished Control File and SPFILE Autobackup at 12-NOV-07

RMAN> quit


Recovery Manager complete.

/u02/backup/PROD: ls -lt|more
total 47463696
-rw-r----- 1 oracle oinstall 32768 Nov 12 15:52 thread_1_seq_657.801.638466455
-rw-r----- 1 oracle oinstall 17920 Nov 12 15:52 thread_1_seq_655.798.638466189
-rw-r----- 1 oracle oinstall 15824384 Nov 12 15:51 thread_1_seq_653.796.638465819
-rw-r----- 1 oracle oinstall 1024 Nov 12 15:50 thread_2_seq_263.770.638416841
...
...


step2: Scp the restored archivelogs to the standby server (bcp1 is node 1 of our standby cluster)
/u02/backup/PROD: scp thread_2_seq_263.770.638416841 bcp1:/u02/backup/PROD/.
thread_2_seq_263.770.638416841
/u02/backup/PROD: scp thread_1_seq_653.796.638465819 bcp1:/u02/backup/PROD/.
thread_1_seq_653.796.638465819 100% 15MB 15.1MB/s 00:01
/u02/backup/PROD: scp thread_1_seq_655.798.638466189 bcp1:/u02/backup/PROD/.
thread_1_seq_655.798.638466189 100% 18KB 17.5KB/s 00:00
/u02/backup/PROD: scp thread_1_seq_657.801.638466455 bcp1:/u02/backup/PROD/.
thread_1_seq_657.801.638466455

step 3: Recover the standby database using the scp'ed archive logs.
PROD1_SQL > recover automatic standby database;
ORA-00279: change 98980021976 generated at 11/12/2007 02:00:34 needed for thread 2
ORA-00289: suggestion : +DATA/prod/2_263_631792716.dbf
ORA-00280: change 98980021976 for thread 2 is in sequence #263
ORA-00278: log file '+DATA/prod/2_262_631792716.dbf' no longer needed for this recovery

Specify log: {=suggested | filename | AUTO | CANCEL}
/u02/backup/PROD/thread_2_seq_263.770.638416841

RA-00279: change 98980621191 generated at 11/12/2007 10:01:02 needed for thread 1
ORA-00289: suggestion : +DATA/prod/1_653_631792716.dbf
ORA-00280: change 98980621191 for thread 1 is in sequence #653
ORA-00278: log file '+DATA/prod/1_653_631792716.dbf' no longer needed for this recovery
ORA-00308: cannot open archived log '+DATA/prod/1_653_631792716.dbf'
ORA-17503: ksfdopn:2 Failed to open file +DATA/prod/1_653_631792716.dbf
ORA-15173: entry '1_653_631792716.dbf' does not exist in directory 'prod'


Specify log: {=suggested | filename | AUTO | CANCEL}
/u02/backup/PROD/thread_1_seq_653.796.638465819
ORA-00279: change 98980621191 generated at 11/12/2007 02:00:40 needed for thread 2
ORA-00289: suggestion : +DATA/prod/2_264_631792716.dbf
ORA-00280: change 98980621191 for thread 2 is in sequence #264


Specify log: {=suggested | filename | AUTO | CANCEL}

ORA-00279: change 98980621195 generated at 11/12/2007 10:01:04 needed for thread 2
ORA-00289: suggestion : +DATA/prod/2_265_631792716.dbf
ORA-00280: change 98980621195 for thread 2 is in sequence #265
ORA-00278: log file '+DATA/prod/2_264_631792716.dbf' no longer needed for this recovery

Specify log: {=suggested | filename | AUTO | CANCEL}
/u02/backup/PROD/thread_2_seq_265.799.638466191
ORA-00279: change 98981521197 generated at 11/12/2007 15:36:59 needed for thread 1
ORA-00289: suggestion : +DATA/prod/1_654_631792716.dbf
ORA-00280: change 98981521197 for thread 1 is in sequence #654
ORA-00278: log file '/u02/backup/PROD/thread_1_seq_653.796.638465819' no longer needed for this recovery

Specify log: {=suggested | filename | AUTO | CANCEL}

ORA-00279: change 98981522036 generated at 11/12/2007 15:42:38 needed for thread 1
ORA-00289: suggestion : +DATA/prod/1_655_631792716.dbf
ORA-00280: change 98981522036 for thread 1 is in sequence #655
ORA-00278: log file '+DATA/prod/1_654_631792716.dbf' no longer needed for this recovery

Specify log: {=suggested | filename | AUTO | CANCEL}
/u02/backup/PROD/thread_1_seq_655.798.638466189
ORA-00279: change 98981522129 generated at 11/12/2007 15:43:09 needed for thread 1
ORA-00289: suggestion : +DATA/prod/1_656_631792716.dbf
ORA-00280: change 98981522129 for thread 1 is in sequence #656
ORA-00278: log file '/u02/backup/PROD/thread_1_seq_655.798.638466189' no longer needed for this recovery

Specify log: {=suggested | filename | AUTO | CANCEL}

ORA-00279: change 98981522138 generated at 11/12/2007 15:43:11 needed for thread 2
ORA-00289: suggestion : +DATA/prod/2_266_631792716.dbf
ORA-00280: change 98981522138 for thread 2 is in sequence #266
ORA-00278: log file '/u02/backup/PROD/thread_2_seq_265.799.638466191' no longer needed for this recovery


Specify log: {=suggested | filename | AUTO | CANCEL}
/u02/backup/PROD/thread_2_seq_266.806.638467227
ORA-00279: change 98981577931 generated at 11/12/2007 15:45:07 needed for thread 1
ORA-00289: suggestion : +DATA/prod/1_657_631792716.dbf
ORA-00280: change 98981577931 for thread 1 is in sequence #657
ORA-00278: log file '+DATA/prod/1_656_631792716.dbf' no longer needed for this recovery

Specify log: {=suggested | filename | AUTO | CANCEL}
/u02/backup/PROD/thread_1_seq_657.801.638466455
ORA-00279: change 98981578237 generated at 11/12/2007 15:47:34 needed for thread 1
ORA-00289: suggestion : +DATA/prod/1_658_631792716.dbf
ORA-00280: change 98981578237 for thread 1 is in sequence #658
ORA-00278: log file '/u02/backup/PROD/thread_1_seq_657.801.638466455' no longer needed for this recovery

Specify log: {=suggested | filename | AUTO | CANCEL}
cancel
Media recovery cancelled.
PROD1_SQL > recover managed standby database using current logfile disconnect from session;
Media recovery complete.


This completes the recovery process for the standby database and fills in the gaps.

8 comments:

Anand said...

Hi Ravi, Thanks for the useful information.
we are having a RAC primary and Single instance standby, Is this method holds good for the same? bcos , in single instance only 1 thread and log, were as in RAC 2 threads and two different logs.

Monitor LCD said...

Hello. This post is likeable, and your blog is very interesting, congratulations :-). I will add in my blogroll =). If possible gives a last there on my blog, it is about the Monitor de LCD, I hope you enjoy. The address is http://monitor-de-lcd.blogspot.com. A hug.

Ravi Gaur said...

Anand,
Yes, that is correct -- I've used that several times. Copy command will work as long as those archives are available in asm. You may also try the "thread thread#" clause during copy.

Ravi Gaur said...

Thanks Monitor lcd,
Your feedback is much appreciated.
BTW, I can't get to you blogspot -- it says page not found
- Ravi

ahirudin said...

Hi Ravi,

You may also try the "thread thread#" clause during

Can you show me on how you do this?

Ravi Gaur said...

Ahiruddin,
I'm not getting your question. Can you be a bit more clear on that please?

Thanks,

sreekanth Bobbala said...

Wat's the solution if your Primary database got crashed and if you have archive log gap in standby?

Will Rman copy work even if your Primary is down?

Meera said...

If control file is still available, you can start database in mount state and use the same steps.

http://mollywoodactress.blogspot.com