Monday, April 21, 2014

ASM DiskGroup Migration

Step Wise Disk-Group Migration from DG01 to DG02

Scenario:

DBTST is an Oracle11g single instance database on ASM which holds complete database in Disk Group named DG01 spread across 4*50 GB of Disks. Objective is to migrate complete databases from DG01 to DG02 Disk Group. Here DG02 is a new Disk Group on 500 GB disk (/dev/mapper/mpathe)

col NAME for a20
col PATH  for a40
select GROUP_NUMBER,DISK_NUMBER,MOUNT_STATUS,STATE,OS_MB,TOTAL_MB,FREE_MB,NAME,PATH from v$asm_disk;


GROUP_NUMBER DISK_NUMBER MOUNT_S STATE OS_MB   TOTAL_MB  FREE_MB NAME       PATH
------- -------- ------- -------- ---------- ---------- -------- --------------- ----------------------------------------
           1           2 CACHED  NORMAL                 51200    51200         43 DG01_0002    /dev/mapper/mpathc
           1           1 CACHED  NORMAL                 51200    51200         41 DG01_0001    /dev/mapper/mpathb
           1           3 CACHED  NORMAL                 51200    51200         45 DG01_0003    /dev/mapper/mpathd
           1           0 CACHED  NORMAL                 51200    51200         46 DG01_0000    /dev/mapper/mpatha

           0           0 CLOSED  NORMAL                 512000          0          0                      /dev/mapper/mpathe

Migration Steps:

1. Create a New Disk Group DG02

SQL> create diskgroup DG02 external redundancy disk '/dev/mapper/mpathe' attribute 'compatible.asm'='11.2';

Diskgroup created.



SQL> select GROUP_NUMBER,DISK_NUMBER,MOUNT_STATUS,STATE,OS_MB,TOTAL_MB,FREE_MB,NAME,PATH from v$asm_disk;

GROUP_NUMBER DISK_NUMBER MOUNT_S STATE OS_MB  TOTAL_MB   FREE_MB NAME      PATH
-------- ----------- ------- -------- ---------- ---------- ---------- -------------------- -------------------------
           1           2 CACHED  NORMAL        51200      51200         43 DG01_0002      /dev/mapper/mpathc
           1           1 CACHED  NORMAL        51200      51200         41 DG01_0001      /dev/mapper/mpathb
           1           3 CACHED  NORMAL        51200      51200         45 DG01_0003      /dev/mapper/mpathd
           1           0 CACHED  NORMAL        51200      51200         46 DG01_0000      /dev/mapper/mpatha
           2           0 CACHED  NORMAL       512000     512000     511944 DG02_0000   /dev/mapper/mpathe

 

 

2. Identify existing schema & DB file locations on DG01

RMAN> report schema;
using target database control file instead of recovery catalog
Report of database schema for database with db_unique_name DBTST

List of Permanent Datafiles
===========================
File Size(MB) Tablespace           RB segs Datafile Name
---- -------- -------------------- ------- ------------------------
1    2048     SYSTEM               ***     +DG01/DBTST/datafile/system.256.797379227
2    820       SYSAUX               ***     +DG01/DBTST/datafile/sysaux.257.797379227
3    3715     UNDOTBS1           ***     +DG01/DBTST/datafile/undotbs1.258.797379227
4    2048     USERS                 ***     +DG01/DBTST/datafile/users.259.797379227
5    30720    ARSYS                ***     +DG01/DBTST/datafile/arsys.276.812153865
6    30720    ARSYS                ***     +DG01/DBTST/datafile/arsys.275.812153995
7    30720    ARSYS                ***     +DG01/DBTST/datafile/arsys.274.812154063
8    30720    ARSYS                ***     +DG01/DBTST/datafile/arsys.273.812154191
9    10000    ARSYS                ***     +DG01/DBTST/datafile/arsys.266.821749103
10   500       ARSYS                ***     +DG01/DBTST/datafile/arsys.272.828469977
16   7168     SYSAUX               ***     +DG01/DBTST/datafile/sysaux.278.801406735
17   5120     UNDOTBS1           ***     +DG01/DBTST/datafile/undotbs1.279.801406757
18   2048     USERS                 ***     +DG01/DBTST/datafile/users.280.801406775
19   2048     SYSTEM               ***     +DG01/DBTST/datafile/system.281.801406799

List of Temporary Files
=======================
File Size(MB) Tablespace           Maxsize(MB) Tempfile Name
---- -------- -------------------- ----------- --------------------
1    32767    TEMP                 32767       +DG01/DBTST/tempfile/temp.264.797379307
2    10240    TEMP                 10240       +DG01/DBTST/tempfile/temp.277.801406709
3    10       TEMP                 10          +DG01/DBTST/tempfile/temp.282.802955967
4    10       TEMP                 10          +DG01/DBTST/tempfile/temp.285.802956051


 3. Perform DB file movement to new disk group DG02

Method 1: Copy each db file

e.g. Shown for single datafile

RMAN> backup as copy datafile 19 format '+DG02';

Starting backup at 17-DEC-13
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=10 device type=DISK
channel ORA_DISK_1: starting datafile copy
input datafile file number=00019 name=+DG01/DBTST/datafile/system.281.801406799
output file name=+DG02/DBTST/datafile/system.256.834409133 tag=TAG20131217T121852 RECID=13 STAMP=834409138
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:07
Finished backup at 17-DEC-13
 


Method 2: Copy complete database (preferred)

RMAN> backup as copy database format '+DG02';

Starting backup at 17-DEC-13
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile copy
input datafile file number=00005 name=+DG01/DBTST/datafile/arsys.276.812153865

output file name=+DG02/DBTST/datafile/arsys.257.834409167 tag=TAG20131217T121927 RECID=14 STAMP=834409255
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:01:35
channel ORA_DISK_1: starting datafile copy
input datafile file number=00006 name=+DG01/DBTST/datafile/arsys.275.812153995

output file name=+DG02/DBTST/datafile/arsys.258.834409263 tag=TAG20131217T121927 RECID=15 STAMP=834409350
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:01:35
channel ORA_DISK_1: starting datafile copy
input datafile file number=00007 name=+DG01/DBTST/datafile/arsys.274.812154063

output file name=+DG02/DBTST/datafile/arsys.259.834409359 tag=TAG20131217T121927 RECID=16 STAMP=834409447
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:01:35
channel ORA_DISK_1: starting datafile copy
input datafile file number=00008 name=+DG01/DBTST/datafile/arsys.273.812154191

output file name=+DG02/DBTST/datafile/arsys.260.834409453 tag=TAG20131217T121927 RECID=17 STAMP=834409553
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:01:45
channel ORA_DISK_1: starting datafile copy
input datafile file number=00009 name=+DG01/DBTST/datafile/arsys.266.821749103

output file name=+DG02/DBTST/datafile/arsys.261.834409559 tag=TAG20131217T121927 RECID=18 STAMP=834409589
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:35
channel ORA_DISK_1: starting datafile copy
input datafile file number=00016 name=+DG01/DBTST/datafile/sysaux.278.801406735

output file name=+DG02/DBTST/datafile/sysaux.262.834409593 tag=TAG20131217T121927 RECID=19 STAMP=834409614
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:25
channel ORA_DISK_1: starting datafile copy
input datafile file number=00017 name=+DG01/DBTST/datafile/undotbs1.279.801406757

output file name=+DG02/DBTST/datafile/undotbs1.263.834409619 tag=TAG20131217T121927 RECID=20 STAMP=834409633
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:15
channel ORA_DISK_1: starting datafile copy
input datafile file number=00003 name=+DG01/DBTST/datafile/undotbs1.258.797379227

output file name=+DG02/DBTST/datafile/undotbs1.264.834409633 tag=TAG20131217T121927 RECID=21 STAMP=834409647
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:15
channel ORA_DISK_1: starting datafile copy
input datafile file number=00001 name=+DG01/DBTST/datafile/system.256.797379227

output file name=+DG02/DBTST/datafile/system.265.834409649 tag=TAG20131217T121927 RECID=22 STAMP=834409656
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:15
channel ORA_DISK_1: starting datafile copy
input datafile file number=00004 name=+DG01/DBTST/datafile/users.259.797379227

output file name=+DG02/DBTST/datafile/users.266.834409663 tag=TAG20131217T121927 RECID=23 STAMP=834409670
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:07
channel ORA_DISK_1: starting datafile copy
input datafile file number=00018 name=+DG01/DBTST/datafile/users.280.801406775

output file name=+DG02/DBTST/datafile/users.267.834409671 tag=TAG20131217T121927 RECID=24 STAMP=834409677
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:07
channel ORA_DISK_1: starting datafile copy
input datafile file number=00019 name=+DG01/DBTST/datafile/system.281.801406799

output file name=+DG02/DBTST/datafile/system.268.834409677 tag=TAG20131217T121927 RECID=25 STAMP=834409683
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:08
channel ORA_DISK_1: starting datafile copy
input datafile file number=00002 name=+DG01/DBTST/datafile/sysaux.257.797379227

output file name=+DG02/DBTST/datafile/sysaux.269.834409685 tag=TAG20131217T121927 RECID=26 STAMP=834409687
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:03
channel ORA_DISK_1: starting datafile copy
input datafile file number=00010 name=+DG01/DBTST/datafile/arsys.272.828469977

output file name=+DG02/DBTST/datafile/arsys.270.834409689 tag=TAG20131217T121927 RECID=27 STAMP=834409690
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:03
channel ORA_DISK_1: starting datafile copy
copying current control file

output file name=+DG02/DBTST/controlfile/backup.271.834409691 tag=TAG20131217T121927 RECID=28 STAMP=834409692
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:03
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
including current SPFILE in backup set
channel ORA_DISK_1: starting piece 1 at 17-DEC-13
channel ORA_DISK_1: finished piece 1 at 17-DEC-13
piece handle=+DG02/DBTST/backupset/2013_12_17/nnsnf0_tag20131217t121927_0.272.834409695 tag=TAG20131217T121927 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 17-DEC-13

RMAN>

 4. Switch database to New disk groups


RMAN> switch database to copy;

datafile 1 switched to datafile copy "+DG02/DBTST/datafile/system.265.834409649"
datafile 2 switched to datafile copy "+DG02/DBTST/datafile/sysaux.269.834409685"
datafile 3 switched to datafile copy "+DG02/DBTST/datafile/undotbs1.264.834409633"
datafile 4 switched to datafile copy "+DG02/DBTST/datafile/users.266.834409663"
datafile 5 switched to datafile copy "+DG02/DBTST/datafile/arsys.257.834409167"
datafile 6 switched to datafile copy "+DG02/DBTST/datafile/arsys.258.834409263"
datafile 7 switched to datafile copy "+DG02/DBTST/datafile/arsys.259.834409359"
datafile 8 switched to datafile copy "+DG02/DBTST/datafile/arsys.260.834409453"
datafile 9 switched to datafile copy "+DG02/DBTST/datafile/arsys.261.834409559"
datafile 10 switched to datafile copy "+DG02/DBTST/datafile/arsys.270.834409689"
datafile 16 switched to datafile copy "+DG02/DBTST/datafile/sysaux.262.834409593"
datafile 17 switched to datafile copy "+DG02/DBTST/datafile/undotbs1.263.834409619"
datafile 18 switched to datafile copy "+DG02/DBTST/datafile/users.267.834409671"
datafile 19 switched to datafile copy "+DG02/DBTST/datafile/system.268.834409677"


RMAN> report schema;

using target database control file instead of recovery catalog
Report of database schema for database with db_unique_name DBTST

List of Permanent Datafiles
===========================
File Size(MB) Tablespace           RB segs Datafile Name
---- -------- -------------------- ------- ------------------------
1    2048     SYSTEM               ***     +DG02/DBTST/datafile/system.265.834409649
2    820       SYSAUX               ***     +DG02/DBTST/datafile/sysaux.269.834409685
3    3715     UNDOTBS1           ***     +DG02/DBTST/datafile/undotbs1.264.834409633
4    2048     USERS                 ***     +DG02/DBTST/datafile/users.266.834409663
5    30720    ARSYS                ***     +DG02/DBTST/datafile/arsys.257.834409167
6    30720    ARSYS                ***     +DG02/DBTST/datafile/arsys.258.834409263
7    30720    ARSYS                ***     +DG02/DBTST/datafile/arsys.259.834409359
8    30720    ARSYS                ***     +DG02/DBTST/datafile/arsys.260.834409453
9    10000    ARSYS                ***     +DG02/DBTST/datafile/arsys.261.834409559
10   500       ARSYS                ***     +DG02/DBTST/datafile/arsys.270.834409689
16   7168     SYSAUX               ***     +DG02/DBTST/datafile/sysaux.262.834409593
17   5120     UNDOTBS1           ***     +DG02/DBTST/datafile/undotbs1.263.834409619
18   2048     USERS                 ***     +DG02/DBTST/datafile/users.267.834409671
19   2048     SYSTEM               ***     +DG02/DBTST/datafile/system.268.834409677

List of Temporary Files
=======================
File Size(MB) Tablespace           Maxsize(MB) Tempfile Name
---- -------- -------------------- ----------- --------------------
1    32767    TEMP                 32767       +DG01/DBTST/tempfile/temp.264.797379307
2    10240    TEMP                 10240       +DG01/DBTST/tempfile/temp.277.801406709
3    10       TEMP                 10          +DG01/DBTST/tempfile/temp.282.802955967
4    10       TEMP                 10          +DG01/DBTST/tempfile/temp.285.802956051
 

5. Control file movement to new disk group DG02

   i. Backup controlfile to new DG02

RMAN> backup as copy current controlfile format '+DG02';

Starting backup at 17-DEC-13
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile copy
copying current control file
output file name=+DG02/DBTST/controlfile/backup.273.834409791 tag=TAG20131217T122951 RECID=43 STAMP=834409791
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01
Finished backup at 17-DEC-13


 

 ii. Change DB parameters to point to new DG02

SQL>  show parameter file (o/p truncated)
NAME                     TYPE                             VALUE
-------------  ------------------ ------------------------------
control_files             string           +DG01/DBTST/controlfile/current.260.797379301
db_create_file_dest   string                           +DG01



SQL> alter system set db_create_file_dest='+DG02' scope=spfile;

System altered.


SQL> alter system set control_files='+DG02/DBTST/controlfile/Backup.273.834409791' scope=spfile;

System altered.


SQL> shut immediate;
ORA-01507: database not mounted
ORACLE instance shut down.
SQL> startup nomount;
ORACLE instance started.
Total System Global Area 1720328192 bytes
Fixed Size                  2229064 bytes
Variable Size            1224740024 bytes
Database Buffers          335544320 bytes
Redo Buffers              157814784 bytes

SQL>  show parameter file (o/p truncated)
NAME                     TYPE                             VALUE
-------------  ------------------ ------------------------------
control_files             string         
+DG02/DBTST/controlfile/Backup.273.834409791  
db_create_file_dest   string                           +DG02

SQL> SQL> alter database mount;

Database altered.


6. Perform DB Recovery & Open DB

SQL> recover database using backup controlfile;
ORA-00279: change 7857026695002 generated at 12/17/2013 12:10:29 needed for thread 1
ORA-00289: suggestion : /u01/arc_dest/1_11552_797379303.dbf
ORA-00280: change 7857026695002 for thread 1 is in sequence #11552
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
+DG01/DBTST/onlinelog/redo03.log
Log applied.
Media recovery complete.


SQL> select status, checkpoint_change#, fuzzy,
  2  to_char(checkpoint_time, 'DD-MON-YYYY HH24:MI:SS') as checkpoint_time,
  3  count(*)
  4  from v$datafile_header
group by status, checkpoint_change#, fuzzy, checkpoint_time
  5    6  order by status, checkpoint_change#, fuzzy, checkpoint_time;

STATUS       CHECKPOINT_CHANGE# FUZ CHECKPOINT_TIME                                    COUNT(*)
------- ---------------------- --- ---------------------------------------------------------- -------------
ONLINE         7857026695003 NO  17-DEC-2013 12:10:29                                   14

1 row selected.

SQL> alter database open resetlogs;

Database altered.
7. Re-create Redo Log files

SQL>  select * from v$logfile;


       GROUP# STATUS  TYPE    MEMBER                               IS_
------------- ------- ------- ------------------------------------------- ---
        3          ONLINE  +DG01/DBTST/onlinelog/redo03.log                NO
        2          ONLINE  +DG01/DBTST/onlinelog/redo02.log                NO
        1          ONLINE  +DG01/DBTST/onlinelog/redo01.log                NO

3 rows selected.

SQL> select * from v$log;

GROUP#   THREAD# SEQUENCE#  BYTES     BLOCKSIZE    MEMBERS  ARC STATUS  FIRST_CHANGE# FIRST_TIME          NEXT_CHANGE#  NEXT_TIME
------- ------- ---------- ---------- -------- --------- --- ------- ------------- ------------------- ------------- -------------------
 1      1          1    1073741824       512      1      YES ACTIVE  7857026695004 17-DEC-2013 12:59:03 7857026695398 17-DEC-2013 12:59:52
 3      1          0    1073741824       512      1      YES UNUSED         0                     0
 2      1          2    1073741824       512      1      NO  CURRENT 7857026695398 17-DEC-2013 12:59:52          0

3 rows selected.

SQL> alter database add logfile group 4 ('+DG02') size 1G;

Database altered.

SQL>  select * from v$log;

       GROUP#        THREAD#    SEQUENCE#      BYTES     BLOCKSIZE  MEMBERS ARC STATUS  FIRST_CHANGE# FIRST_TIME NEXT_CHANGE#  NEXT_TIME
------------- ------------- ------------- ------------- ------------- ------------- --- ---------- ------------- ------------ --------- -----------
        1          1        1    1073741824       512      1    YES ACTIVE  7857026695004 17-DEC-2013 12:59:03     7857026695398 17-DEC-2013 12:59:52
        4          1        0    1073741824       512      1    YES UNUSED                 0                         0
        3          1        0    1073741824       512      1    YES UNUSED                 0                         0
        2          1        2    1073741824       512      1    NO  CURRENT      7857026695398 17-DEC-2013 12:59:52         0

4 rows selected.

SQL> select * from v$logfile;

       GROUP# STATUS  TYPE    MEMBER                               IS_
------------- ------- ------- ------------------------------------------------------------ ---
        3          ONLINE  +DG01/DBTST/onlinelog/redo03.log                NO
        2          ONLINE  +DG01/DBTST/onlinelog/redo02.log                NO
        1          ONLINE  +DG01/DBTST/onlinelog/redo01.log                NO
        4          ONLINE  +DG02/DBTST/onlinelog/group_4.274.834411679           NO

4 rows selected.

SQL> alter system checkpoint;

System altered.

SQL> alter database drop logfile group 3;

Database altered.

SQL> select * from v$log;

GROUP#        THREAD#    SEQUENCE#      BYTES     BLOCKSIZE        MEMBERS ARC STATUS    FIRST_CHANGE#   FIRST_TIME    NEXT_CHANGE#  NEXT_TIME
-------- ------------- ------------- ------------- ------------- ------------- --- ---------------- ------------- ------------------- -------------
1          1        1    1073741824       512          1 YES INACTIVE     7857026695004 17-DEC-2013 12:59:03         7857026695398 17-DEC-2013 12:59:52
4          1        0    1073741824       512          1 YES UNUSED                 0                         0
2          1        2    1073741824       512          1 NO    CURRENT      7857026695398 17-DEC-2013 12:59:52         2.8147498E+14


3 rows selected.

SQL> alter database drop logfile group 1;

Database altered.

SQL> select * from v$log;

GROUP#        THREAD#    SEQUENCE#      BYTES     BLOCKSIZE        MEMBERS ARC STATUS    FIRST_CHANGE#   FIRST_TIME    NEXT_CHANGE#  NEXT_TIME
-------- ------------- ------------- ------------- ------------- ------------- --- ---------------- ------------- ------------------- -------------
   4          1        0    1073741824       512          1 YES UNUSED                 0                         0
   2          1        2    1073741824       512          1 NO    CURRENT      7857026695398 17-DEC-2013 12:59:52         2.8147498E+14



2 rows selected.

SQL> alter system switch logfile;

System altered.

SQL> alter system checkpoint;

System altered.

SQL> alter database add logfile group 1 ('+DG02') size 1G;

Database altered.

SQL>  select * from v$log;


GROUP#        THREAD#    SEQUENCE#      BYTES     BLOCKSIZE        MEMBERS ARC STATUS    FIRST_CHANGE#   FIRST_TIME    NEXT_CHANGE#  NEXT_TIME
-------- ------------- ------------- ------------- ------------- ------------- --- ---------------- ------------- ------------------- -------------
  1          1        0    1073741824       512          1 YES UNUSED         0             0
  4          1        3    1073741824       512          1 NO    CURRENT  7857026695467 17-DEC-2013 13:02:28         0
  2          1        2    1073741824       512          1 YES INACTIVE 7857026695398 17-DEC-2013 12:59:52       7857026695467 17-DEC-2013 13:02:28

3 rows selected.

SQL>  alter database drop logfile group 2;

Database altered.

SQL> select * from v$log;

GROUP#        THREAD#    SEQUENCE#      BYTES     BLOCKSIZE        MEMBERS ARC STATUS    FIRST_CHANGE#   FIRST_TIME    NEXT_CHANGE#  NEXT_TIME
-------- ------------- ------------- ------------- ------------- ------------- --- ---------------- ------------- ------------------- -------------
   4          1        3    1073741824       512          1 NO    CURRENT  7857026695467 17-DEC-2013 13:02:28         0
   1          1        0    1073741824       512          1 YES UNUSED                 0             0



2 rows selected.

SQL>  alter database add logfile group2  ('+DG02') size 1G;

Database altered.

SQL> alter database add logfile group 3 ('+DG02') size 1G;

Database altered.

SQL> select * from v$log;

GROUP#        THREAD#    SEQUENCE#      BYTES     BLOCKSIZE        MEMBERS ARC STATUS    FIRST_CHANGE#   FIRST_TIME    NEXT_CHANGE#  NEXT_TIME
-------- ------------- ------------- ------------- ------------- ------------- --- ---------------- ------------- ------------------- -------------
   1          1        0    1073741824       512          1 YES UNUSED                 0                 0
   4          1        3    1073741824       512          1 NO    CURRENT  7857026695467 17-DEC-2013 13:02:28         0
   3          1        0    1073741824       512          1 YES UNUSED                 0                     0
   2          1        0    1073741824       512          1 YES UNUSED                 0             0



4 rows selected.

SQL> alter system switch logfile;

System altered.

SQL> /

System altered.

SQL> select * from v$logfile;

       GROUP# STATUS  TYPE    MEMBER                               IS_
------------- ------- ------- ------------------------------------------------------------ ---
        1          ONLINE  +DG02/DBTST/onlinelog/group_1.275.834411781           NO
        2          ONLINE  +DG02/DBTST/onlinelog/group_2.276.834411805           NO
        3          ONLINE  +DG02/DBTST/onlinelog/group_3.277.834411811           NO
        4          ONLINE  +DG02/DBTST/onlinelog/group_4.274.834411679           NO

4 rows selected.


8. Re-create Temporary Tablespace

SQL> select * from dba_temp_files;

FILE_NAME                                ILE_ID TABLESPACE_NAME       BYTES      BLOCKS STATUS   RELATIVE_FNO AUT    MAXBYTES     MAXBLOCKS    INCREMENT_BY    USER_BYTES   USER_BLOCKS
---------------------------------------- ------ ---------------- ------------- ------------- ------- ------------- --- ------------- ------------- ------------- ------------- -------------
+DG01/DBTST/tempfile/temp.264.797379307   1      TEMP           34358689792     4194176 ONLINE          1 YES   34359721984       4194302          80   34357641216     4194048
+DG01/DBTST/tempfile/temp.277.801406709   2      TEMP          10737418240     1310720 ONLINE          2 NO           0         0           0   10736369664     1310592
+DG01/DBTST/tempfile/temp.282.802955967   3      TEMP          10485760        1280 ONLINE          3 NO           0         0           0       9437184        1152
+DG01/DBTST/tempfile/temp.285.802956051   4      TEMP          10485760        1280 ONLINE          4 NO           0         0           0       9437184        1152


4 rows selected.

SQL> create temporary tablespace TEMP1 tempfile '+DG02' size 20G;

Tablespace created.

SQL> alter database default temporary tablespace TEMP1;

Database altered.

SQL> shut immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
Total System Global Area    1720328192 bytes
Fixed Size               2229064 bytes
Variable Size            1224740024 bytes
Database Buffers         335544320 bytes
Redo Buffers             157814784 bytes
Database mounted.
Database opened.

SQL> select * from dba_temp_files;

FILE_NAME                           FILE_ID     TABLESPACE_NAME   BYTES      BLOCKS STATUS   RELATIVE_FNO AUT    MAXBYTES     MAXBLOCKS    INCREMENT_BY    USER_BYTES   USER_BLOCKS
-------------------------------------------- ---------- ------------------ ------------- ------------- ------- ------------- --- ------------- ------------- ------------- ------------- -------------
+DG01/DBTST/tempfile/temp.264.797379307       1         TEMP           34358689792     4194176 ONLINE          1 YES   34359721984       4194302          80   34357641216     4194048
+DG02/DBTST/tempfile/temp1.278.834411957      5         TEMP1           21474836480     2621440 ONLINE          1 NO           0         0           0   21473787904     2621312
+DG01/DBTST/tempfile/temp.277.801406709       2         TEMP           10737418240     1310720 ONLINE          2 NO           0         0           0   10736369664     1310592
+DG01/DBTST/tempfile/temp.282.802955967       3         TEMP           10485760        1280 ONLINE          3 NO           0         0           0       9437184        1152
+DG01/DBTST/tempfile/temp.285.802956051       4         TEMP           10485760        1280 ONLINE          4 NO           0         0           0       9437184        1152


5 rows selected.

SQL> drop tablespace TEMP including contents and datafiles;

Tablespace dropped.

SQL>  select * from dba_temp_files;

FILE_NAME                   FILE_ID       TABLESPACE_NAME                 BYTES      BLOCKS STATUS   RELATIVE_FNO AUT    MAXBYTES     MAXBLOCKS    INCREMENT_BY    USER_BYTES   USER_BLOCKS
----------------------------------------- ------------- ------------------------------ ------------- ------------- ------- ------------- --- ------------- ------------- ------------- ------------- -------------
+DG02/DBTST/tempfile/temp1.278.834411957    5          TEMP1                   21474836480     2621440 ONLINE          1 NO           0         0           0   21473787904     2621312


1 row selected.
 

Wednesday, February 19, 2014

oracle database corrupt block recovery

Error:

Corrupt block relative dba: 0x1a0975d9 (file 104, block 619993)
Bad check value found during user buffer read
Data in bad block:
 type: 6 format: 2 rdba: 0x1a0975d9
 last change scn: 0x0726.9a3a3fc5 seq: 0x1 flg: 0x06
 spare1: 0x0 spare2: 0x0 spare3: 0x0
 consistency value in tail: 0x3fc50601
 check value in block header: 0x287e
 computed block checksum: 0xfe81
Reread of rdba: 0x1a0975d9 (file 104, block 619993) found same corrupted data
Tue Feb 18 20:17:59 IST 2014
Corrupt Block Found
         TSN = 47, TSNAME = EVENT_TAB
         RFN = 104, BLK = 619993, RDBA = 436827609
         OBJN = 141322, OBJD = 141322, OBJECT = EVENT_PK, SUBOBJECT =
         SEGMENT OWNER = EVA_ADMIN, SEGMENT TYPE = Index Segment


Background:

Oracle 10g database sizing over 1 TB with no RMAN backup. 
Backup being taken using legacy hot backup image copy to disk/tape.
Block being corrupted belongs to IOT index wherein data resides itself inside index sizing over 500 GB.
Alert log has provided enough information about corruption like tablespace_name  , object_name , object_id , object_type , corrupted block number and more to initiate corruption recovery plan.

Verification:

Important step for any type of corruption in database and should include integrity check for complete database to identify if corruption list includes any more blocks other than that mentioned in alert log

1. Identify possible objects impacted due to corruption based on corrupted block id, 619993 is the corrupted block id appeared in view or alert log as BLK = 619993

SELECT e.file_id,
 e.block_id,
 e.owner,
 e.segment_name,
 e.segment_type
 FROM dba_extents e
 WHERE
 file_id=104 and 619993 BETWEEN 619993 AND 619993 + blocks - 1
 /

2. Perform logical database / datafile integrity check which will populate  v$database_block_corruption


RMAN> run {backup validate datafile 104;}

Starting backup at 19-FEB-14
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=2190 devtype=DISK
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
input datafile fno=00104 name=/data02/EVACP/event_tab_90.dbf
channel ORA_DISK_1: backup set complete, elapsed time: 00:01:15
Finished backup at 19-FEB-14

RMAN>

Get the progress of rman validate command using below ,

select MBYTES_PROCESSED,STATUS,OPERATION from V_$RMAN_STATUS where OPERATION like '%VALIDATE%';

MBYTES_PROCESSED STATUS                  OPERATION
---------------- ----------------------- ---------------------------------
            9216 COMPLETED               BACKUP VALIDATE



Similarly , complete database integrity can also be validated using below rman command.

 backup validate check logical database ;

 3. Corrupt block detected from RMAN validate integrity check will get populated in v$database_block_corruption as well as in alert log

select * from v$database_block_corruption ;
     FILE#     BLOCK#     BLOCKS CORRUPTION_CHANGE# CORRUPTIO
---------- ---------- ---------- ------------------ ---------
       104     619993          1                  0 CHECKSUM 

select file#, block# as blk#, corruption_type as type from v$database_block_corruption;
     FILE#       BLK# TYPE
---------- ---------- ---------
       104     619993 CHECKSUM

4. Offline datafile physical corruption check 

dbv FILE=/corp/data02/EVACP/event_tab_90.dbf FEEDBACK=100


DBVERIFY - Verification complete
Total Pages Examined         : 1179648
Total Pages Processed (Data) : 134144
Total Pages Failing   (Data) : 0
Total Pages Processed (Index): 536576
Total Pages Failing   (Index): 0
Total Pages Processed (Other): 8
Total Pages Processed (Seg)  : 0
Total Pages Failing   (Seg)  : 0
Total Pages Empty            : 508920
Total Pages Marked Corrupt   : 0
Total Pages Influx           : 0
Highest block SCN            : 378599356 (1831.378599356)
 

Resolution:

Corruption fixes plan depends on type of the recent DB backup being taken before first corruption incident time.

Here , db backup image is a part of hot backup & not RMAN backupset
 
1. Restore all the archivelog files since end backup time to a staging location (/tempfs) or in the same location of current archivelog destination

2. Restore backup of corrupted datafile id 104 to staging location (/tempfs)

3. Catalog archivelog restored to make control file aware of their existence.

RMAN> catalog archivelog '/tempfs/1_54763_668965414.arc';
cataloged archive log
archive log filename=/tempfs/1_54763_668965414.arc recid=70182 stamp=839895943

Make sure all archivelog files are restored since end backup & cataloged to avoid below error during block media recovery

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of blockrecover command at 02/19/2014 00:30:13
RMAN-06053: unable to perform media recovery because of missing log
RMAN-06025: no backup of log thread 1 seq 54769 lowscn 7864522937984 found to restore



4. Catalog backup datafile restored to make control file aware of their existence.


RMAN> catalog datafilecopy '/tempfs/event_tab_90.dbf';
cataloged datafile copy
datafile copy filename=/tempfs/event_tab_90.dbf recid=22055 stamp=839895977



5. Re-verify the current datafile to get corrupted block list

RMAN> run {backup validate datafile 104;}
Starting backup at 19-FEB-14
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=2190 devtype=DISK
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
input datafile fno=00104 name=/data02/EVACP/event_tab_90.dbf
channel ORA_DISK_1: backup set complete, elapsed time: 00:01:15
Finished backup at 19-FEB-14

RMAN>

6. perform block recovery

RMAN> RUN
{
  BLOCKRECOVER DATAFILE 104 BLOCK 619993
  FROM DATAFILECOPY;
}
2> 3> 4> 5>

Starting blockrecover at 19-FEB-14
using channel ORA_DISK_1

channel ORA_DISK_1: restoring block(s) from datafile copy /tempfs/event_tab_90.dbf

starting media recovery

archive log thread 1 sequence 54744 is already on disk as file /tempfs/1_54744_668965414.arc
archive log thread 1 sequence 54745 is already on disk as file /tempfs/1_54745_668965414.arc
archive log thread 1 sequence 54746 is already on disk as file /tempfs/1_54746_668965414.arc
archive log thread 1 sequence 54747 is already on disk as file /tempfs/1_54747_668965414.arc
archive log thread 1 sequence 54748 is already on disk as file /tempfs/1_54748_668965414.arc
archive log thread 1 sequence 54749 is already on disk as file /tempfs/1_54749_668965414.arc
archive log thread 1 sequence 54750 is already on disk as file /tempfs/1_54750_668965414.arc
archive log thread 1 sequence 54751 is already on disk as file /tempfs/1_54751_668965414.arc
archive log thread 1 sequence 54752 is already on disk as file /tempfs/1_54752_668965414.arc
archive log thread 1 sequence 54753 is already on disk as file /tempfs/1_54753_668965414.arc
archive log thread 1 sequence 54754 is already on disk as file /tempfs/1_54754_668965414.arc
archive log thread 1 sequence 54755 is already on disk as file /tempfs/1_54755_668965414.arc
archive log thread 1 sequence 54756 is already on disk as file /tempfs/1_54756_668965414.arc
archive log thread 1 sequence 54757 is already on disk as file /tempfs/1_54757_668965414.arc
archive log thread 1 sequence 54758 is already on disk as file /tempfs/1_54758_668965414.arc
archive log thread 1 sequence 54759 is already on disk as file /tempfs/1_54759_668965414.arc
archive log thread 1 sequence 54760 is already on disk as file /tempfs/1_54760_668965414.arc
archive log thread 1 sequence 54761 is already on disk as file /tempfs/1_54761_668965414.arc
archive log thread 1 sequence 54762 is already on disk as file /tempfs/1_54762_668965414.arc
archive log thread 1 sequence 54763 is already on disk as file /tempfs/1_54763_668965414.arc
archive log thread 1 sequence 54764 is already on disk as file /tempfs/1_54764_668965414.arc
archive log thread 1 sequence 54765 is already on disk as file /tempfs/1_54765_668965414.arc
archive log thread 1 sequence 54766 is already on disk as file /tempfs/1_54766_668965414.arc
archive log thread 1 sequence 54767 is already on disk as file /tempfs/1_54767_668965414.arc
archive log thread 1 sequence 54768 is already on disk as file /tempfs/1_54768_668965414.arc
archive log thread 1 sequence 54769 is already on disk as file /tempfs/1_54769_668965414.arc

media recovery complete, elapsed time: 00:04:06
Finished blockrecover at 19-FEB-14

RMAN>

  

7. Verify the corrupted block status by re-running the verification step 2 & 3. Note immediately querying v$database_block_corruption post block media recovery will reflect the old corrupt block.integrity check needs to be performed again to refresh the corruption view.


SQL> select file#, block# as blk#, corruption_type as type from v$database_block_corruption;

no rows selected