Backup archivelog to disk – An Oldie

This is long pending post though it’s not unique :), you may get 1 million hit with archivelog to disk keyword on google. However this post is for those who have faced this kind of situation like ME on CRITICAL production database where you need to act quickly rather than googling around to get hold of the syntax.

Situation: Netbackup Master server had some issues, archivelog backup failed to initiate. ETA for resolution 12-14 hours. HIGH transaction critical database not enough ASM storage to withhold archivelogs. DBA has to act to avoid database hangs !!

Environment: DB Version – 10.2.0.5, Every 2 hours archivelog backup with delete input to tapes.

First check the archivelog asm diskgroup usage.

DG_ARC diskgroup is for archivelog. It has crossed 50 % usage, normally in our environment this should never cross 50 % as every 2 hours archivelog backup has been schedule, this is clear indication that archive backup has some issues. Normally you analyse the RMAN backup logs to find out the exact errors.

In our case we were notified by netbackup team on netbackup master server issue.

As a DBA you need to backup the archive logs that are not backed up to avoid DB hang.

First:

Check ASM diskgroup usage.


sys@PRD2> COL "Free %" FORMAT 99.0

sys@PRD2> SELECT name, free_mb, total_mb, free_mb/total_mb*100 "Free %" FROM v$asm_diskgroup;

NAME FREE_MB TOTAL_MB Free %
------------------------------ ---------- ---------- ------
DG_D1 25611 457031 5.6
DG_D3 36190 457031 7.9
DG_D7 11215 487500 2.3
DG_ARC 237805 487500 48.8
DG_D9 91313 91406 99.9

Elapsed: 00:00:00.47

Second:

Check the count of archivelogs not BACKED UP and DELETED (since we are using delete input with archivelog backup, DELETED=’NO’ will give the count of archivelogs)


sys@PRD1> alter session set nls_date_format='DD-MON-YYYY HH24:MI:SS';

sys@PRD1> select count(*) from v$archived_log where DELETED='NO';

COUNT(*)
----------
133

Elapsed: 00:00:00.52

Third:

Query v$archived_log to find out the name,seq#,thread# of the archivelogs that are not BACKED UP and DELETED from arch destination.

Below is partial output.

01:24:52 sys@PRD1>

select NAME,SEQUENCE#,THREAD#,FIRST_TIME,NEXT_TIME,ARCHIVED,DELETED,STATUS,COMPLETION_TIME from
v$archived_log where DELETED='NO' order by SEQUENCE# asc;

NAME
--------------------------------------------------------------------------------------
 SEQUENCE#    THREAD# FIRST_TIME           NEXT_TIME            ARC DEL S COMPLETION_TIME
---------- ---------- -------------------- -------------------- --- --- - -----------
+DG_ARC/prd/archivelog/2012_06_06/thread_2_seq_688259.5573.785262691
    688259          2 06-JUN-2012 16:19:35 06-JUN-2012 16:31:30 YES NO  A 06-JUN-2012 16:31:51

+DG_ARC/prd/archivelog/2012_06_06/thread_2_seq_688260.4794.785262879
    688260          2 06-JUN-2012 16:31:30 06-JUN-2012 16:34:39 YES NO  A 06-JUN-2012 16:34:40

+DG_ARC/prd/archivelog/2012_06_06/thread_2_seq_688261.538.785263249
    688261          2 06-JUN-2012 16:34:39 06-JUN-2012 16:40:48 YES NO  A 06-JUN-2012 16:40:55

....
....
....
....

+DG_ARC/prd/archivelog/2012_06_07/thread_1_seq_821524.4557.785291125
    821524          1 07-JUN-2012 00:24:49 07-JUN-2012 00:25:24 YES NO  A 07-JUN-2012 00:25:47

+DG_ARC/prd/archivelog/2012_06_07/thread_1_seq_821525.2601.785291175
    821525          1 07-JUN-2012 00:25:24 07-JUN-2012 00:26:14 YES NO  A 07-JUN-2012 00:26:51

+DG_ARC/prd/archivelog/2012_06_07/thread_1_seq_821526.1888.785291241
    821526          1 07-JUN-2012 00:26:14 07-JUN-2012 00:27:20 YES NO  A 07-JUN-2012 00:27:50

+DG_ARC/prd/archivelog/2012_06_07/thread_1_seq_821527.986.785291431
    821527          1 07-JUN-2012 00:27:20 07-JUN-2012 00:30:30 YES NO  A 07-JUN-2012 00:31:24

+DG_ARC/prd/archivelog/2012_06_07/thread_1_seq_821528.895.785291577
    821528          1 07-JUN-2012 00:30:30 07-JUN-2012 00:32:56 YES NO  A 07-JUN-2012 00:33:44

+DG_ARC/prd/archivelog/2012_06_07/thread_1_seq_821529.2421.785295401
    821529          1 07-JUN-2012 00:32:56 07-JUN-2012 01:36:41 YES NO  A 07-JUN-2012 01:37:19
135 rows selected.

Elapsed: 00:00:01.60  

Fourth:

(A) Backup the seq# with thread# (Since i had the time to test with 2 archivelogs and see how its working WITHOUT delete input option, if time permits you can also check with few archivelogs WITHOUT delete input option)

RMAN> rman target /

RMAN> backup format ‘/export/dump/archivebkp_june7/%d_%s_%p_%c_%t.arc.rman’ archivelog from sequence 688259 until sequence 688260 thread 2;

OR

(B) Backup all archivelog not backed up.

To speed up the backup process 4 channels have been used, i have explicitly used compressed backupset so as to avoid any space issue at the mount point. Using compressed backupset has it own pros and cons.


RMAN> run {
allocate channel ch001 device type disk;
allocate channel ch002 device type disk;
allocate channel ch003 device type disk;
allocate channel ch004 device type disk;
backup as compressed backupset archivelog all not backed up 1 times delete input
TAG 'archbkp_june7_full_arc' FORMAT '/export/dump/archivebkp_june7/%d_%s_%p_%c_%t.arc.rman';
backup current controlfile
TAG 'archbkp_june7_ctl' FORMAT '/export/dump/archivebkp_june7/%d_%s_%p_%c_%t.ctl.rman';
backup spfile
TAG 'archbkp_june7_spfile' FORMAT '/export/dump/archivebkp_june7/%d_%s_%p_%c_%t.spfile.rman';
release channel ch001;
release channel ch002;
release channel ch003;
release channel ch004;
}

Keep the archivelog backup piece on the FS until you have full DB backup OR  Restore archives from backup piece to ASM diskgroup, so that RMAN picks up these archives and backup to Tapes(user discretion advised, think and plan, if its only few archives do it)

Case

What if you don’t have enough space on one mount point say /export to backup all archivelog even though its compressed ? Well you can instruct RMAN to backup archivelogs to different mount points, as …


RMAN> rman target /

RMAN> run {
allocate channel ch001 device type disk FORMAT 'F:\oracle\product\10.2.0\flash_recovery_area\ORCL\backups\%d_%s_%p_%c_%t.arc.rman';
allocate channel ch002 device type disk FORMAT 'F:\oracle\product\10.2.0\flash_recovery_area\ORCL\backup1\%d_%s_%p_%c_%t.arc.rman';
allocate channel ch003 device type disk FORMAT 'F:\oracle\product\10.2.0\flash_recovery_area\ORCL\backups\%d_%s_%p_%c_%t.arc.rman';
allocate channel ch004 device type disk FORMAT 'F:\oracle\product\10.2.0\flash_recovery_area\ORCL\backup1\%d_%s_%p_%c_%t.arc.rman';
backup as compressed backupset archivelog all not backed up 1 times delete input
TAG 'archbkp_june7_full_arc';
backup current controlfile
TAG 'archbkp_june7_ctl' FORMAT 'F:\oracle\product\10.2.0\flash_recovery_area\ORCL\backups\%d_%s_%p_%c_%t.ctl.rman';
backup spfile
TAG 'archbkp_june7_spfile' FORMAT 'F:\oracle\product\10.2.0\flash_recovery_area\ORCL\backups\%d_%s_%p_%c_%t.spfile.rman';
release channel ch001;
release channel ch002;
release channel ch003;
release channel ch004;
}

Concentrate on allocate channel .. syntax with FORMAT option. You can specify different mount points to instruct RMAN to write backup pieces to it. In the above case i have used different directories ‘backups’ and ‘backup1’ under F drive.

Partial output below:


............

channel ch002: finished piece 1 at 26-AUG-12
piece handle=F:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA\ORCL\BACKUP1\ORCL_24_1_1_792371018.ARC.RMAN tag=ARCHBKP_JUNE7_FULL_ARC comment=NONE
channel ch002: backup set complete, elapsed time: 00:01:42
........

channel ch001: finished piece 1 at 26-AUG-12
piece handle=F:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA\ORCL\BACKUPS\ORCL_23_1_1_792371018.ARC.RMAN tag=ARCHBKP_JUNE7_FULL_ARC comment=NONE
......

channel ch003: finished piece 1 at 26-AUG-12
piece handle=F:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA\ORCL\BACKUPS\ORCL_25_1_1_792371018.ARC.RMAN tag=ARCHBKP_JUNE7_FULL_ARC comment=NONE
channel ch003: backup set complete, elapsed time: 00:02:18
........

channel ch004: finished piece 1 at 26-AUG-12
piece handle=F:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA\ORCL\BACKUP1\ORCL_26_1_1_792371018.ARC.RMAN tag=ARCHBKP_JUNE7_FULL_ARC comment=NONE
channel ch004: backup set complete, elapsed time: 00:02:21
........

channel ch002: finished piece 1 at 26-AUG-12
piece handle=F:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA\ORCL\BACKUP1\ORCL_27_1_1_792371150.ARC.RMAN tag=ARCHBKP_JUNE7_FULL_ARC comment=NONE
channel ch002: backup set complete, elapsed time: 00:00:12

From the above, 3 backup pieces in ‘backup1‘ directory and 2 backup pieces in ‘backups‘ directory

Note: I have tested this on my local PC not on PROD database.

Scenario: Not related to this post but useful.

Archivelogs are backed up daily and deleted only after 2 days from the arch destination (i.e completed before sysdate-2). How to avoid repeated archivelog backups on a daily basis ?

This is how you can do this ….


run {
allocate channel ch001 device type disk FORMAT 'F:\oracle\product\10.2.0\flash_recovery_area\ORCL\backups\%d_%s_%p_%c_%t.arc.rman';
allocate channel ch002 device type disk FORMAT 'F:\oracle\product\10.2.0\flash_recovery_area\ORCL\backup1\%d_%s_%p_%c_%t.arc.rman';
allocate channel ch003 device type disk FORMAT 'F:\oracle\product\10.2.0\flash_recovery_area\ORCL\backups\%d_%s_%p_%c_%t.arc.rman';
allocate channel ch004 device type disk FORMAT 'F:\oracle\product\10.2.0\flash_recovery_area\ORCL\backup1\%d_%s_%p_%c_%t.arc.rman';
backup archivelog all not backed up 1 times;
delete archivelog all completed before 'sysdate - 2';
release channel ch001;
release channel ch002;
release channel ch003;
release channel ch004;
}

Cheers !!

Raheel Syed

Advertisements

About Raheel Syed

Oracle DBA
This entry was posted in Archivelog Backup, RMAN and tagged , , , , , , , . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s