Information Technology: August 2015

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

Using RMAN, we backup the database ORAW:

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

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:

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:

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

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:

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

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:

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:

SQL> ALTER DATABASE DATAFILE '+DATA/oraw/datafile/data_reporting.266.744955249' 

OFFLINE;

Using RMAN, execute the following sequence of commands:

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.

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,

Information Technology

Friday, August 28, 2015

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

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

My Blog List

Report Abuse