Oracle Learning: using md_backup & md

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.

[oracle@wissem ~]$ rman

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

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.

[oracle@wissem ~]$ 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.

[oracle@wissem ~]$ rman

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

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>

Regards,
Jay Vardhan

jayvardhancs@gmail.com

Oracle Learning

Thursday, March 29, 2012

using md_backup & md_restore

3 comments:

About Me

Blog Archive