Controlfile Autobackup and RMAN-06172

Controlfile AUTOBACKUP is one of the best option provide by Oracle for backup through RMAN method.

In this post i will show how to restore CONTROLFILE & SPFILE in different senario

1. when AUTOBACKUP controlfile option is enable/ON and AUTOBACKUP location is also set.

means we are set following two parameter before perform database backup

RMAN>configure controlfile autobackup ON;

RMAN>configure controlfile autobackup format for device type disk to 'F:\B_%F';

NOTE: 'F:\' is a backup location where AUTOBACKUP controlfile or spfile created and "%F" is format.

RMAN> backup database;

Starting Control File and SPFILE Autobackup at 19-JUL-08

piece handle=F:\B_C-1958649224-20080719-03 comment=NONE

Finished Control File and SPFILE Autobackup at 19-JUL-08

NOTE: above underline and blue colored number is DBID for the database

Now I deleted ALL CONTROLFILES & SPFILE

C:\>del F:\oracle\product\10.1.0\oradata\test\*.CTL

C:\>del F:\oracle\product\10.1.0\Db_1\database\SPFILETEST.ora


1. Restoring SPFILE

RMAN> set dbid=1958649224

RMAN> startup nomount force;

RMAN> set controlfile autobackup format for device type disk to 'F:\B_%F';

executing command: SET CONTROLFILE AUTOBACKUP FORMATRMAN> restore SPFILE from AUTOBACKUP;

Starting restore at 19-JUL-08

using channel ORA_DISK_1channel ORA_DISK_1: looking for autobackup on day: 20080719
channel ORA_DISK_1: autobackup found: F:\B_c-1958649224-20080719-03

channel ORA_DISK_1: SPFILE restore from autobackup complete

Finished restore at 19-JUL-08


2. Restoring CONTROLFILE

RMAN> restore controlfile from 'F:\B_C-1958649224-20080719-03';Starting restore at 19-JUL-08

using channel ORA_DISK_1channel ORA_DISK_1: restoring controlfile

channel ORA_DISK_1: restore complete

output filename=F:\ORACLE\PRODUCT\10.1.0\ORADATA\TEST\CONTROL01.CTL
output filename=F:\ORACLE\PRODUCT\10.1.0\ORADATA\TEST\CONTROL02.CTL
output filename=F:\ORACLE\PRODUCT\10.1.0\ORADATA\TEST\CONTROL03.CTL

Finished restore at 19-JUL-08

NOTE: It is always recommended to set CONTROLFILE autobackup ON and also set AUTOBACKUP LOCATION AND FOMRAT MASK "%F".


2. Second condition when controlfile autobackup is ON/ENABLE but AUTOBACKUP location and format mask it not set.

means the following parameter is not set.

CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F'; # default

NOTE: flashback_recovery_area is set. so controlfile autobackup is created in flashback_recovery_area.

First enable the autobackup controlfile option

RMAN> configure controlfile autobackup on;
RMAN> backup database;

Starting Control File and SPFILE Autobackup at 19-JUL-08

piece handle=F:\ORACLE\PRODUCT\10.1.0\FLASH_RECOVERY_AREA\TEST\AUTOBACKUP\2008_07_19\O1_MF_S_660509348_483ZQ4MF_.BKP comment=NONE

Finished Control File and SPFILE Autobackup at 19-JUL-08

NOTE: This time controlfile autobackup is created in FRA.

Now I deleted all controlfile & spfile

RMAN> restore spfile from autobackup;

Starting restore at 19-JUL-08
using target database controlfile instead of recovery catalog

allocated channel: ORA_DISK_1channel ORA_DISK_1: sid=29 devtype=DISK

RMAN-00571: ===========================================================
RMAN-00569: ===============
ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================

RMAN-03002: failure of restore command at 07/19/2008 18:54:49

RMAN-06495: must explicitly specify DBID with SET DBID command

First disadvantage is we can't get DBID becuase controlfile autobackup option is enable for format we are not defined. In this case if it is unix platform we can get DBID from database backupset through backupset location grep MAXVALUE

but I don't know how to find in windows. but here i also keep DBID.

RMAN> set dbid=1958649224

executing command: SET DBID

RMAN> restore spfile from autobackup;
Starting restore at 19-JUL-08

using channel ORA_DISK_1channel ORA_DISK_1: looking for autobackup on day: 20080719
channel ORA_DISK_1: looking for autobackup on day: 20080718
channel ORA_DISK_1: looking for autobackup on day: 20080717
channel ORA_DISK_1: looking for autobackup on day: 20080716
channel ORA_DISK_1: looking for autobackup on day: 20080715
channel ORA_DISK_1: looking for autobackup on day: 20080714
channel ORA_DISK_1: looking for autobackup on day: 20080713
channel ORA_DISK_1: no autobackup in 7 days found

RMAN-00571: ===========================================================
RMAN-00569:
===============
ERROR MESSAGE STACK FOLLOWS
===============
RMAN-00571: ===========================================================

RMAN-03002: failure of restore command at 07/19/2008 19:17:17

RMAN-06172: no autobackup found or specified handle is not a valid copy or piece

Note: above error occur becuase we are not set autobackup location so we need to first set controlfile or spfile backup location, in this case it is FRA.

RMAN> restore spfile from autobackup db_recovery_file_dest='F:\oracle\product\10.1.0\flash_recovery_area' db_name='TEST';Starting restore at 19-JUL-08using channel ORA_DISK_1recovery area destination: F:\oracle\product\10.1.0\flash_recovery_area

database name (or lock name space) used for search: TEST

channel ORA_DISK_1: autobackup found in the recovery area

channel ORA_DISK_1: autobackup found: F:\ORACLE\PRODUCT\10.1.0\FLASH_RECOVERY_AREA\TEST\AUTOBACKUP\2008_07_19\O1_MF_S_660509348_483ZQ4MF_.BKP

channel ORA_DISK_1: SPFILE restore from autobackup complete

Finished restore at 19-JUL-08

shutdown the database and startup nomout with restored spfile

RMAN> restore controlfile from autobackup;

Starting restore at 19-JUL-08allocated channel: ORA_DISK_1

channel ORA_DISK_1: sid=160 devtype=DISKrecovery area destination: F:\oracle\product\10.1.0\flash_recovery_area

database name (or lock name space) used for search: TEST

channel ORA_DISK_1: autobackup found in the recovery area
channel ORA_DISK_1: autobackup found: F:\ORACLE\PRODUCT\10.1.0\FLASH_RECOVERY_AREA\TEST\AUTOBACKUP\2008_07_19\O1_MF_S_660509348_483ZQ4MF_.BKP

channel ORA_DISK_1: controlfile restore from autobackup complete

output filename=F:\ORACLE\PRODUCT\10.1.0\ORADATA\TEST\CONTROL01.CTL
output filename=F:\ORACLE\PRODUCT\10.1.0\ORADATA\TEST\CONTROL02.CTL
output filename=F:\ORACLE\PRODUCT\10.1.0\ORADATA\TEST\CONTROL03.CTL

Finished restore at 19-JUL-08


3. when FRA is not configured or autobackup controlfile location & format is not set.

rman target /

Recovery Manager: Release 10.1.0.2.0 - Production
Copyright (c) 1995, 2004, Oracle. All rights reserved.connected to target database: TEST (DBID=1958649224)RMAN> CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK CLEAR;using target database controlfile instead of recovery catalog

RMAN configuration parameters are successfully reset to default value

RMAN> configure controlfile autobackup on;

old RMAN configuration parameters:

CONFIGURE CONTROLFILE AUTOBACKUP ON;

new RMAN configuration parameters:

CONFIGURE CONTROLFILE AUTOBACKUP ON;

new RMAN configuration parameters are successfully stored

RMAN> backup database;Starting backup at 19-JUL-08

allocated channel: ORA_DISK_1

channel ORA_DISK_1: sid=141 devtype=DISK
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset

input datafile fno=00001 name=F:\ORACLE\PRODUCT\10.1.0\ORADATA\TEST\SYSTEM01.DBFinput datafile fno=00003 name=F:\ORACLE\PRODUCT\10.1.0\ORADATA\TEST\SYSAUX01.DBFinput datafile fno=00002 name=F:\ORACLE\PRODUCT\10.1.0\ORADATA\TEST\UNDOTBS01.DBF

input datafile fno=00004 name=F:\ORACLE\PRODUCT\10.1.0\ORADATA\TEST\USERS01.DBF

channel ORA_DISK_1: starting piece 1 at 19-JUL-08
channel ORA_DISK_1: finished piece 1 at 19-JUL-08

piece handle=C:\WINDOWS\SYSTEM32\05JLT74O_1_1 comment=NONE

channel ORA_DISK_1: backup set complete, elapsed time: 00:00:15

Finished backup at 19-JUL-08Starting Control File and SPFILE Autobackup at 19-JUL-08
piece handle=C:\WINDOWS\SYSTEM32\C-1958649224-20080719-03 comment=NONE
Finished Control File and SPFILE Autobackup at 19-JUL-08

NOTE: This time backupset and autobackup controlfile is created in default windows location: "c:\windows\system32\", interesting autobackup controlfile is created with DBID.