11gR2 ASM – Inside story – Part2 - 2 (Diskgroup restore)

11gR2 ASM – Inside story – Part2 - 2 (Diskgroup restore)

Using RMAN, we backup the database ORAW:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82

[oracle@wissem ~]$ rman Recovery Manager: Release 11.2.0.1.0 - Production on Sat Mar 5 03:32:44 2011 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> show all; using target database control file instead of recovery catalog RMAN configuration parameters for database with db_unique_name ORAW are: CONFIGURE RETENTION POLICY TO REDUNDANCY 1; # default CONFIGURE BACKUP OPTIMIZATION OFF; # default CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default CONFIGURE CONTROLFILE AUTOBACKUP OFF; # default CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F'; # default CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO BACKUPSET; # default CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default CONFIGURE MAXSETSIZE TO UNLIMITED; # default CONFIGURE ENCRYPTION FOR DATABASE OFF; # default CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default CONFIGURE COMPRESSION ALGORITHM 'BASIC' AS OF RELEASE 'DEFAULT' OPTIMIZE FOR LOAD TRUE ; # default CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/u01/app/oracle/product/11.2.0/orawiss/dbs/snapcf_ORAW.f'; # default RMAN> CONFIGURE CONTROLFILE AUTOBACKUP ON; new RMAN configuration parameters: CONFIGURE CONTROLFILE AUTOBACKUP ON; new RMAN configuration parameters are successfully stored 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 05-MAR-11 channel ORA_DISK_1: finished piece 1 at 05-MAR-11 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 05-MAR-11 Starting backup at 05-MAR-11 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 05-MAR-11 channel ORA_DISK_1: finished piece 1 at 05-MAR-11 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 05-MAR-11 Starting backup at 05-MAR-11 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 05-MAR-11 channel ORA_DISK_1: finished piece 1 at 05-MAR-11 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 05-MAR-11 Starting Control File and SPFILE Autobackup at 05-MAR-11 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 05-MAR-11 RMAN>

Now, we silmulate a loss of the diskgroup DATA_REPORTING. From the ASM instance, we execute the following commands:

1 2 3 4 5 6 7 8 9

SQL> alter diskgroup DATA_REPORTING DISMOUNT FORCE; Diskgroup altered. SQL> drop diskgroup DATA_REPORTING FORCE INCLUDING CONTENTS; Diskgroup dropped. SQL>

Back to the database instance and me make sure the the database is on error:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17

SQL> insert into reporting values (3, sysdate); insert into reporting values (3, sysdate) * ERROR at line 1: ORA-03135: connection lost contact Process ID: 26825 Session ID: 23 Serial number: 86 SQL> SQL> select * from reporting; select * from reporting               * ERROR at line 1: ORA-01219: database not open: queries allowed on fixed tables/views only SQL>

Now, using the md_restore command we restore the diskgroup:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32

[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>

Back to the ASM instance and make sure the diskgroup is restored:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23

SQL> select GROUP_NUMBER, NAME from V$ASm_DISK; GROUP_NUMBER NAME ------------ ------------------------------            0            1 DISK4            1 DISK3            1 DISK2            1 DISK1            2 DISK6            2 DISK5 7 rows selected. 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>

The data are not restored yet, only the diskgroup metadata are now restored:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39

[oracle@wissem ~]$ source .bash_profile [oracle@wissem ~]$ sqlplus /nolog SQL*Plus: Release 11.2.0.1.0 Production on Sat Mar 5 03:50:18 2011 Copyright (c) 1982, 2009, Oracle.  All rights reserved. SQL> connect sys as sysdba Enter password: Connected. SQL> select * from reporting; select * from reporting               * ERROR at line 1: ORA-01219: database not open: queries allowed on fixed tables/views only SQL> shutdown immediate; Database closed. Database dismounted. ORACLE instance shut down. SQL> startup ORACLE instance started. Total System Global Area  313860096 bytes Fixed Size                  1336232 bytes Variable Size             255855704 bytes Database Buffers           50331648 bytes Redo Buffers                6336512 bytes Database mounted. Database opened. SQL> select * from reporting; select * from reporting               * ERROR at line 1: ORA-00376: file 5 cannot be read at this time ORA-01110: data file 5: '+DATA_REPORTING/oraw/datafile/data_reporting.256.744952855' SQL>

Now let’s restore and recover the database:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53

SQL> startup force mount; ORACLE instance started. Total System Global Area  313860096 bytes Fixed Size                  1336232 bytes Variable Size             260050008 bytes Database Buffers           46137344 bytes Redo Buffers                6336512 bytes Database mounted. 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 [oracle@wissem ~]$ rman Recovery Manager: Release 11.2.0.1.0 - Production on Sat Mar 5 04:14:50 2011 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 05-MAR-11 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 05-MAR-11 RMAN> recover database; Starting recover at 05-MAR-11 using channel ORA_DISK_1 starting media recovery media recovery complete, elapsed time: 00:00:29 Finished recover at 05-MAR-11

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:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39

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 DATAFILE '+DATA/oraw/datafile/data_reporting.266.744955249' ONLINE; Database altered. SQL> select * from reporting;         ID REP_DATE ---------- ---------          1 05-MAR-11          2 05-MAR-11 SQL>

Now, let’s restore the DATA_REORTING datafie to +DATA_REPORTING diskgroup as it was before the failure.
from the database instance, make the datafile offline:

1 2	SQL> ALTER DATABASE DATAFILE '+DATA/oraw/datafile/data_reporting.266.744955249' OFFLINE;

Using RMAN, execute the following sequence of commands:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41

RMAN> COPY DATAFILE '+DATA/oraw/datafile/data_reporting.266.744955249' TO '+DATA_REPORTING'; Starting backup at 05-MAR-11 using channel ORA_DISK_1 channel ORA_DISK_1: starting datafile copy input datafile file number=00005 name=+DATA/oraw/datafile/data_reporting.266.744955249 output file name=+DATA_REPORTING/oraw/datafile/data_reporting.259.744959803 tag=TAG20110305T051638 RECID=4 STAMP=744959835 channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:44 Finished backup at 05-MAR-11 Starting Control File and SPFILE Autobackup at 05-MAR-11 piece handle=/u01/app/oracle/admin/ORAW/fra/ORAW/autobackup/2011_03_05/o1_mf_s_744959848_6q3gbtvk_.bkp comment=NONE Finished Control File and SPFILE Autobackup at 05-MAR-11 RMAN> run{ 2> set newname for datafile '+DATA/oraw/datafile/data_reporting.266.744955249' 3> to '+DATA_REPORTING/oraw/datafile/data_reporting.259.744959803'; 4> switch datafile all; 5> } executing command: SET NEWNAME datafile 5 switched to datafile copy input datafile copy RECID=4 STAMP=744959835 file  name=+DATA_REPORTING/oraw/datafile/data_reporting.259.744959803 RMAN> RMAN> recover datafile '+DATA_REPORTING/oraw/datafile/data_reporting.259.744959803'; Starting recover at 05-MAR-11 using channel ORA_DISK_1 starting media recovery media recovery complete, elapsed time: 00:00:03 Finished recover at 05-MAR-11 RMAN>

Now, back to the oracle instance, execute the following commands in order to back online the datafile and check the reporting table.

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27

SQL> ALTER DATABASE DATAFILE '+DATA_REPORTING/oraw/datafile/data_reporting.259.744959803'   2  ONLINE; Database altered. SQL> select FILE_NAME from DBA_DATA_FILES; FILE_NAME -------------------------------------------------------------------------------- +DATA/oraw/datafile/users.259.744936891 +DATA/oraw/datafile/undotbs1.258.744936889 +DATA/oraw/datafile/sysaux.257.744936887 +DATA/oraw/datafile/system.256.744936879 +DATA_REPORTING/oraw/datafile/data_reporting.259.744959803 SQL> alter session set NLS_DATE_FORMAT='DD-MM-YYYY HH24:MI:SS'; Session altered. SQL> select * from reporting;         ID REP_DATE ---------- -------------------          1 05-03-2011 03:25:04          2 05-03-2011 03:25:22 SQL>

Nice reading,