using md_backup & md_restore

RMAN

Oracle backup utility RMAN only provide the backup for oracle data file but it wonít back up the ASM meta data. Thus in loss asm disk group, rman wonít help to restore the asm disk group.

Oracle has provided mb_backup and md_restore commands to backup and restore the asm meta data.

We will practice how it works.

So now, connecting with the asm instance and creating new disk group.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 – Production
With the Automatic Storage Management option

SQL> CREATE DISKGROUP DATA_REPORTING NORMAL REDUNDANCY DISK
2 ‘/dev/raw/raw7’ name disk5 ,
3 ‘/dev/raw/raw8’ name disk6 force;
Diskgroup created.
SQL>

SQL> select NAME, TOTAL_MB, FREE_MB from V$ASM_DISKGROUP;

NAME TOTAL_MB FREE_MB
—————————— ———- ———-
DATA 4000 1068
INDX 0 0
DATA_REPORTING 2000 1898

Now connecting with the database instance, we will create tablespace on this newly created disk group and also create new table on this tablespace.

SQL> create tablespace data_reporting datafile ‘+DATA_REPORTING’;

Tablespace created.

SQL> select NAME from v$datafile;

NAME

+DATA/oraw/datafile/system.256.744936879
+DATA/oraw/datafile/sysaux.257.744936887
+DATA/oraw/datafile/undotbs1.258.744936889
+DATA/oraw/datafile/users.259.744936891
+DATA_REPORTING/oraw/datafile/data_reporting.256.744952855

SQL> create table reporting(ID NUMBER , REP_DATE DATE) TABLESPACE DATA_REPORTING;

Table created.

SQL> insert into reporting values (1, sysdate);

1 row created.

SQL> insert into reporting values (2, sysdate);

1 row created.

SQL> commit;

Commit complete.

Elapsed: 00:00:00.57
SQL> select * from reporting;

    ID REP_DATE

     1 29-03-2012 18:55:04
     2 29-03-2012 18:56:22

Elapsed: 00:00:00.36
SQL>

Now connecting with the asmcmd (it is the command line utility to manage asm), we will do backup for this newly created diskgroup.

ASMCMD>
ASMCMD> md_backup -b /tmp/asmbkp_data_reporting -G ‘DATA_REPORTING’
WARNING:option ‘b’ is deprecated for ‘md_backup’
Disk group metadata to be backed up: DATA_REPORTING
Current alias directory path: ORAW/DATAFILE
Current alias directory path: ORAW
ASMCMD>

Also to perform complete recovery, we are taking full rman backup.

[[email protected] ~]$ rman

Recovery Manager: Release 11.2.0.1.0 – Production on Sat Mar 5 19:13:05 2012

Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.

RMAN> connect target sys

target database Password:
connected to target database: ORAW (DBID=3426823299)

RMAN> BACKUP AS COMPRESSED BACKUPSET DATABASE PLUS ARCHIVELOG;

Starting backup at 05-MAR-11
current log archived
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=24 device type=DISK
channel ORA_DISK_1: starting compressed archived log backup set
channel ORA_DISK_1: specifying archived log(s) in backup set
input archived log thread=1 sequence=3 RECID=1 STAMP=744953697
channel ORA_DISK_1: starting piece 1 at 29-MAR-12
channel ORA_DISK_1: finished piece 1 at 29-MAR-12
piece handle=/u01/app/oracle/admin/ORAW/fra/ORAW/backupset/2011_03_05/o1_mf_annnn_TAG20110305T033502_6q38brxq_.bkp tag=TAG20110305T033502 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:16
Finished backup at 29-MAR-12

Starting backup at 29-MAR-12
using channel ORA_DISK_1
channel ORA_DISK_1: starting compressed full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00001 name=+DATA/oraw/datafile/system.256.744936879
input datafile file number=00002 name=+DATA/oraw/datafile/sysaux.257.744936887
input datafile file number=00005 name=+DATA_REPORTING/oraw/datafile/data_reporting.256.744952855
input datafile file number=00003 name=+DATA/oraw/datafile/undotbs1.258.744936889
input datafile file number=00004 name=+DATA/oraw/datafile/users.259.744936891
channel ORA_DISK_1: starting piece 1 at 29-MAR-12
channel ORA_DISK_1: finished piece 1 at 29-MAR-12
piece handle=/u01/app/oracle/admin/ORAW/fra/ORAW/backupset/2011_03_05/o1_mf_nnndf_TAG20110305T033522_6q38chho_.bkp tag=TAG20110305T033522 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:07:54
Finished backup at 29-MAR-12

Starting backup at 29-MAR-12
current log archived
using channel ORA_DISK_1
channel ORA_DISK_1: starting compressed archived log backup set
channel ORA_DISK_1: specifying archived log(s) in backup set
input archived log thread=1 sequence=4 RECID=2 STAMP=744954215
channel ORA_DISK_1: starting piece 1 at 29-MAR-12
channel ORA_DISK_1: finished piece 1 at 29-MAR-12
piece handle=/u01/app/oracle/admin/ORAW/fra/ORAW/backupset/2011_03_05/o1_mf_annnn_TAG20110305T034337_6q38tto8_.bkp tag=TAG20110305T034337 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 29-MAR-12

Starting Control File and SPFILE Autobackup at 29-MAR-12
piece handle=/u01/app/oracle/admin/ORAW/fra/ORAW/autobackup/2011_03_05/o1_mf_s_744954221_6q38v2xk_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 29-MAR-12

RMAN>

Now, we have disk group metadata backup (md_backup) and database backup (rman). So now connecting with the asm instance, we will drop this disk group.

SQL> alter diskgroup DATA_REPORTING DISMOUNT FORCE;

Diskgroup altered.

SQL> drop diskgroup DATA_REPORTING FORCE INCLUDING CONTENTS;

Diskgroup dropped.

Dropping if the disk group, crash the instance as it contains the datafile for tablespace data_reporting.

SQL>

Database instance:

SQL> select * from reporting;
select * from reporting
*
ERROR at line 1:
ORA-01219: database not open: queries allowed on fixed tables/views only

SQL>

So now, starting the recovery phase we will restore the diskgroup metadata using md_restore command.

[[email protected] ~]$ asmcmd
ASMCMD> md_restore /tmp/asmbkp_data_reporting -t full -g DATA_REPORTING
WARNING:option ‘g’ is deprecated for ‘md_restore’
please use ‘G’

WARNING:option ‘t’ is deprecated for ‘md_restore’
please use ‘full|nodg|newdg’

Current Diskgroup metadata being restored: DATA_REPORTING
Diskgroup DATA_REPORTING created!
System template ONLINELOG modified!
System template AUTOBACKUP modified!
System template ASMPARAMETERFILE modified!
System template OCRFILE modified!
System template ASM_STALE modified!
System template OCRBACKUP modified!
System template PARAMETERFILE modified!
System template ASMPARAMETERBAKFILE modified!
System template FLASHFILE modified!
System template XTRANSPORT modified!
System template DATAGUARDCONFIG modified!
System template TEMPFILE modified!
System template ARCHIVELOG modified!
System template CONTROLFILE modified!
System template DUMPSET modified!
System template BACKUPSET modified!
System template FLASHBACK modified!
System template DATAFILE modified!
System template CHANGETRACKING modified!
Directory +DATA_REPORTING/ORAW re-created!
Directory +DATA_REPORTING/ORAW/DATAFILE re-created!
ASMCMD>

Confirming the restoration.
SQL> select NAME, TOTAL_MB, FREE_MB from V$ASM_DISKGROUP;

NAME TOTAL_MB FREE_MB
—————————— ———- ———-
DATA 4000 1068
INDX 0 0
DATA_REPORTING 2000 1898

SQL>

SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 – Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options

Now since disk group is restore, we can use RMAN to restore the datafile.

[[email protected] ~]$ rman

Recovery Manager: Release 11.2.0.1.0 – Production on Mar 29 19:29:31 2012

Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.

RMAN> connect target sys

target database Password:
connected to target database: ORAW (DBID=3426823299, not open)

RMAN> restore database;

Starting restore at 29-MAR-12
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=25 device type=DISK

channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00001 to +DATA/oraw/datafile/system.256.744936879
channel ORA_DISK_1: restoring datafile 00002 to +DATA/oraw/datafile/sysaux.257.744936887
channel ORA_DISK_1: restoring datafile 00003 to +DATA/oraw/datafile/undotbs1.258.744936889
channel ORA_DISK_1: restoring datafile 00004 to +DATA/oraw/datafile/users.259.744936891
channel ORA_DISK_1: restoring datafile 00005 to +DATA/oraw/datafile/data_reporting.266.744955249
channel ORA_DISK_1: reading from backup piece /u01/app/oracle/admin/ORAW/fra/ORAW/backupset/2011_03_05/o1_mf_nnndf_TAG20110305T033522_6q38chho_.bkp
channel ORA_DISK_1: piece handle=/u01/app/oracle/admin/ORAW/fra/ORAW/backupset/2011_03_05/o1_mf_nnndf_TAG20110305T033522_6q38chho_.bkp tag=TAG20110305T033522
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:07:42
Finished restore at 29-MAR-12

RMAN> recover database;

Starting recover at 29-MAR-12
using channel ORA_DISK_1

starting media recovery
media recovery complete, elapsed time: 00:00:29

Finished recover at 29-MAR-12
From above, note that RMAN restored the DATA_REORTING datafie to +DATA diskgroup.
But, we would like to restore the DATA_REORTING datafie to +DATA_REPORTING diskgroup as it was before the failure.
Also note that the restored datafile is in offline state:

SQL> SELECT FILE_NAME, TABLESPACE_NAME, ONLINE_STATUS from DBa_DATA_FILES;

FILE_NAME

TABLESPACE_NAME ONLINE_


+DATA/oraw/datafile/users.259.744936891
USERS ONLINE

+DATA/oraw/datafile/undotbs1.258.744936889
UNDOTBS1 ONLINE

+DATA/oraw/datafile/sysaux.257.744936887
SYSAUX ONLINE

FILE_NAME

TABLESPACE_NAME ONLINE_


+DATA/oraw/datafile/system.256.744936879
SYSTEM SYSTEM

+DATA/oraw/datafile/data_reporting.266.744955249
DATA_REPORTING OFFLINE

SQL> ALTER DATABASE OPEN;

Database altered.
Confirming for the data restoration by querying the newly created table.

SQL> select * from reporting;

    ID REP_DATE

     1 29-MAR-12
     2 29-MAR-12

SQL>