Please note this amdu is undocumented . Without oracle's confirmation dont try this options
amdu is a utlity comes with 11g in default ( for 10g we need to download from metalink and install it).It has various options like to compare the mirrors,print the block contents,fullscan on the diskgroup,extract the files from a diskgroup.This utility works even if the diskgroups are in unmounted stage.
Suppose one or more diskgroups are not able to mount due to various reason and mirror copy is also not working, we need to extract few files from the diskgroup to bring the database to consistent mode. For example some datafiles/archive files/redo log files etc… This utility is very much useful in this scenario.
If there is a physical/media error on the files we are trying extract then the whole process may not work as expected.
A case study has been made to restore all data files from an unmounted diskgroups and build the database in the non-asm filesystem.
1. Created a sample database into the ASM diskgroups
create database TEST
MAXINSTANCES 32
MAXLOGHISTORY 1000
MAXLOGFILES 64
MAXLOGMEMBERS 5
MAXDATAFILES 2056
datafile '+DATA_01/TEST/system01.dbf' size 300M EXTENT MANAGEMENT
LOCAL
SYSAUX DATAFILE '+DATA_01/TEST/sysaux01.dbf' size 300M
UNDO TABLESPACE "UNDOTBS1" DATAFILE
'+DATA_01/TEST/undotbs01.dbf' size 200M
LOGFILE GROUP 1 ('+REDO_01/TEST/redo01.log') SIZE 51200K,
GROUP 2 ('+REDO_02/TEST/redo02.log') SIZE 51200K,
GROUP 3 ('+REDO_01/TEST/redo03.log') SIZE 51200K ,
GROUP 4 ('+REDO_02/TEST/redo04.log') SIZE 51200K
DEFAULT TEMPORARY TABLESPACE TEMP TEMPFILE
'+DATA_01/TEST/temp01.dbf' size 500m
CHARACTER SET WE8ISO8859P1
NATIONAL CHARACTER SET AL16UTF16;
@?/rdbms/admin/catalog
@?/rdbms/admin/catproc
@?/rdbms/admin/dbmspool.sql
2. Identify the file# against the database files.
set pagesize 0
set linesize 200
col full_alias_path format a80
select x.gnum,x.filnum,x.full_alias_path,f.ftype from (
SELECT gnum,filnum,concat('+'||gname, sys_connect_by_path(aname, '/')) full_alias_path
FROM (SELECT g.name gname, a.parent_index pindex, a.name aname,
a.reference_index rindex,a.group_number gnum,a.file_number filnum
FROM v$asm_alias a, v$asm_diskgroup g
WHERE a.group_number = g.group_number)
START WITH (mod(pindex, power(2, 24))) = 0 CONNECT BY PRIOR rindex = pindex) x,
(select group_number gnum,file_number filnum, type ftype from v$asm_file order by group_number,file_number) f
where x.filnum != 4294967295
and x.gnum=f.gnum and x.filnum=f.filnum
/
1 256 +DATA_01/JEYSERVER/control01.ctl CONTROLFILE
1 256 +DATA_01/JEYSERVER/CONTROLFILE/Current.256.691068219 CONTROLFILE
1 257 +DATA_01/JEYSERVER/CONTROLFILE/Current.257.691068219 CONTROLFILE
1 257 +DATA_01/JEYSERVER/control02.ctl CONTROLFILE
1 258 +DATA_01/JEYSERVER/CONTROLFILE/Current.258.691068219 CONTROLFILE
1 258 +DATA_01/JEYSERVER/control03.ctl CONTROLFILE
1 259 +DATA_01/JEYSERVER/system01.dbf DATAFILE
1 259 +DATA_01/JEYSERVER/DATAFILE/SYSTEM.259.691068223 DATAFILE
1 260 +DATA_01/JEYSERVER/DATAFILE/UNDOTBS1.260.691068239 DATAFILE
1 260 +DATA_01/JEYSERVER/undotbs01.dbf DATAFILE
1 261 +DATA_01/JEYSERVER/DATAFILE/SYSAUX.261.691068243 DATAFILE
1 261 +DATA_01/JEYSERVER/sysaux01.dbf DATAFILE
1 262 +DATA_01/JEYSERVER/temp01.dbf TEMPFILE
1 262 +DATA_01/JEYSERVER/TEMPFILE/TEMP.262.691068249 TEMPFILE
1 263 +DATA_01/test_asm.dmp DUMPSET
1 263 +DATA_01/JEYSERVER/DUMPSET/SIVASYS_EXPORT_TRANSPORTABLE_0_231115_1.263.75217 DUMPSET
0035
1 264 +DATA_01/JEYSERVER/DATAFILE/TEST_ASM.264.752168641 DATAFILE
1 265 +DATA_01/TEST/CONTROLFILE/Current.265.759845783 CONTROLFILE
1 265 +DATA_01/TEST/control01.ctl CONTROLFILE
1 266 +DATA_01/TEST/CONTROLFILE/Current.266.759845783 CONTROLFILE
1 266 +DATA_01/TEST/control02.ctl CONTROLFILE
1 267 +DATA_01/TEST/CONTROLFILE/Current.267.759845783 CONTROLFILE
1 267 +DATA_01/TEST/control03.ctl CONTROLFILE
1 268 +DATA_01/TEST/DATAFILE/SYSTEM.268.759845785 DATAFILE
1 268 +DATA_01/TEST/system01.dbf DATAFILE
1 269 +DATA_01/TEST/DATAFILE/SYSAUX.269.759845791 DATAFILE
1 269 +DATA_01/TEST/sysaux01.dbf DATAFILE
1 270 +DATA_01/TEST/DATAFILE/UNDOTBS1.270.759845795 DATAFILE
1 270 +DATA_01/TEST/undotbs01.dbf DATAFILE
1 271 +DATA_01/TEST/TEMPFILE/TEMP.271.759845795 TEMPFILE
1 271 +DATA_01/TEST/temp01.dbf TEMPFILE
2 256 +DATA_02/JEYSERVER/XTRANSPORT/TEST_ASM.256.752170417 XTRANSPORT
4 256 +REDO_01/JEYSERVER/redo01.log ONLINELOG
4 256 +REDO_01/JEYSERVER/ONLINELOG/group_1.256.691068219 ONLINELOG
4 257 +REDO_01/JEYSERVER/redo03.log ONLINELOG
4 257 +REDO_01/JEYSERVER/ONLINELOG/group_3.257.691068221 ONLINELOG
4 258 +REDO_01/TEST/redo01.log ONLINELOG
4 258 +REDO_01/TEST/ONLINELOG/group_1.258.759845783 ONLINELOG
4 259 +REDO_01/TEST/redo03.log ONLINELOG
4 259 +REDO_01/TEST/ONLINELOG/group_3.259.759845783 ONLINELOG
5 256 +REDO_02/JEYSERVER/ONLINELOG/group_2.256.691068221 ONLINELOG
5 256 +REDO_02/JEYSERVER/redo02.log ONLINELOG
5 257 +REDO_02/JEYSERVER/redo04.log ONLINELOG
5 257 +REDO_02/JEYSERVER/ONLINELOG/group_4.257.691068221 ONLINELOG
5 258 +REDO_02/TEST/redo02.log ONLINELOG
5 258 +REDO_02/TEST/ONLINELOG/group_2.258.759845783 ONLINELOG
5 259 +REDO_02/TEST/redo04.log ONLINELOG
5 259 +REDO_02/TEST/ONLINELOG/group_4.259.759845785 ONLINELOG
48 rows selected.
3. Shutdown the databases
4. Dismount the diskgroups
SQL> alter diskgroup DATA_01 dismount;
alter diskgroup REDO_01 dismount;
alter diskgroup REDO_02 dismount;
5. Extract the files from diskgroups
amdu -dis "/dev/vx_wa/rdsk/hostnamedg/" -extract DATA_01.265
amdu -dis "/dev/vx_wa/rdsk/hostnamedg/" -extract DATA_01.266
amdu -dis "/dev/vx_wa/rdsk/hostnamedg/" -extract DATA_01.267
amdu -dis "/dev/vx_wa/rdsk/hostnamedg/" -extract DATA_01.268
amdu -dis "/dev/vx_wa/rdsk/hostnamedg/" -extract DATA_01.269
amdu -dis "/dev/vx_wa/rdsk/hostnamedg/" -extract DATA_01.270
amdu -dis "/dev/vx_wa/rdsk/hostnamedg/" -extract DATA_01.271
amdu -dis "/dev/vx_wa/rdsk/hostnamedg/" -extract REDO_01.258
amdu -dis "/dev/vx_wa/rdsk/hostnamedg/" -extract REDO_02.258
amdu -dis "/dev/vx_wa/rdsk/hostnamedg/" -extract REDO_01.259
amdu -dis "/dev/vx_wa/rdsk/hostnamedg/" -extract REDO_02.259
It creates a folder and extract the file and report.txt into that folder.
drwxr-xr-x 2 oracle orainst 96 Aug 22 16:29 amdu_2011_08_22_16_22_15
drwxr-xr-x 2 oracle orainst 96 Aug 22 16:29 amdu_2011_08_22_16_22_28
drwxr-xr-x 2 oracle orainst 96 Aug 22 16:29 amdu_2011_08_22_16_22_42
drwxr-xr-x 2 oracle orainst 96 Aug 22 16:29 amdu_2011_08_22_16_22_55
drwxr-xr-x 2 oracle orainst 96 Aug 22 16:29 amdu_2011_08_22_16_24_09
drwxr-xr-x 2 oracle orainst 96 Aug 22 16:29 amdu_2011_08_22_16_25_21
drwxr-xr-x 2 oracle orainst 96 Aug 22 16:29 amdu_2011_08_22_16_26_25
drwxr-xr-x 2 oracle orainst 96 Aug 22 17:12 amdu_2011_08_22_16_58_18
drwxr-xr-x 2 oracle orainst 96 Aug 22 17:12 amdu_2011_08_22_17_06_57
drwxr-xr-x 2 oracle orainst 96 Aug 22 17:12 amdu_2011_08_22_17_10_31
drwxr-xr-x 2 oracle orainst 96 Aug 22 17:13 amdu_2011_08_22_17_04_04
6. Rename the files to the relevant name
-rw-r--r-- 1 oracle orainst 524296192 Aug 22 16:28 temp01.dbf
-rw-r--r-- 1 oracle orainst 52429312 Aug 22 17:15 redo02.log
-rw-r--r-- 1 oracle orainst 52429312 Aug 22 17:15 redo03.log
-rw-r--r-- 1 oracle orainst 52429312 Aug 22 17:15 redo04.log
-rw-r--r-- 1 oracle orainst 209723392 Aug 22 17:46 undotbs01.dbf
-rw-r--r-- 1 oracle orainst 314580992 Aug 22 17:46 system01.dbf
-rw-r--r-- 1 oracle orainst 314580992 Aug 22 17:46 sysaux01.dbf
-rw-r--r-- 1 oracle orainst 52429312 Aug 22 17:47 redo01.log
7. Start the instance with control file pointing to filesystem or available diskgroup
*.control_files='/data/oracle/JEYSERVER/backup/export/control01.ctl','/data/oracle/JEYSERVER/backup/export/control02.ctl'
8. create control file using the below
CREATE CONTROLFILE SET DATABASE "TEST" RESETLOGS NOARCHIVELOG
MAXLOGFILES 64
MAXLOGMEMBERS 5
MAXDATAFILES 2056
MAXINSTANCES 32
MAXLOGHISTORY 1168
LOGFILE
GROUP 1 '/data/oracle/JEYSERVER/backup/DB/redo01.log' SIZE 50M,
GROUP 2 '/data/oracle/JEYSERVER/backup/DB/redo02.log' SIZE 50M,
GROUP 3 '/data/oracle/JEYSERVER/backup/DB/redo03.log' SIZE 50M,
GROUP 4 '/data/oracle/JEYSERVER/backup/DB/redo04.log' SIZE 50M
-- STANDBY LOGFILE
DATAFILE
'/data/oracle/JEYSERVER/backup/DB/system01.dbf',
'/data/oracle/JEYSERVER/backup/DB/sysaux01.dbf',
'/data/oracle/JEYSERVER/backup/DB/undotbs01.dbf'
CHARACTER SET WE8ISO8859P1
;
--RECOVER DATABASE USING BACKUP CONTROLFILE
-- Database can now be opened zeroing the online logs.
ALTER DATABASE OPEN RESETLOGS;
-- Commands to add tempfiles to temporary tablespaces.
-- Online tempfiles have complete space information.
-- Other tempfiles may require adjustment.
ALTER TABLESPACE TEMP ADD TEMPFILE '/data/oracle/JEYSERVER/backup/DB/temp01.dbf'
SIZE 524288000 REUSE AUTOEXTEND OFF;
-- End of tempfile additions.
--
8. Recover the database and If required provide the redo log files/archive log files for the recovery
SQL> recover database using backup controlfile;
ORA-00279: change 238825 generated at 08/22/2011 16:32:54 needed for thread 1
ORA-00289: suggestion : /data/oracle/TEST/archive1_13_759845780.dbf
ORA-00280: change 238825 for thread 1 is in sequence #13
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
/data/oracle/JEYSERVER/backup/DB/redo01.log
Log applied.
Media recovery complete.
SQL> alter database open resetlogs;
Database altered.
SQL> select name from v$database;
NAME
---------
TEST
SQL> select name from v$datafile;
NAME
--------------------------------------------------------------------------------
/data/oracle/JEYSERVER/backup/DB/system01.dbf
/data/oracle/JEYSERVER/backup/DB/sysaux01.dbf
/data/oracle/JEYSERVER/backup/DB/undotbs01.dbf
No comments:
Post a Comment