Extract Datafile From Oracle ASM corrupted diskgroup

Amdu

Amdu是从oracle 11g开始加入到Oracle发布版本中的ASM转储工具。除了转储ASM元数据外，amdu的-extract选项还可以从ASM diskgroup中抽取文件。

实际amdu的功能，PRM都具备了，PRM会将ASM元数据分析- ASM Analyze并存入到其内置的数据库中，之后还可以从图形界面上选择哪些文件需要被抽取出来。

以上为PRM的ASM FILE CLONE功能，可以直接从受损的ASM DISKGROUP中克隆出数据文件和归档日志文件。

PRM也支持直接从ASM上的Oracle Database DB数据库中直接拯救表上的数据，对于PRM而言ASM是透明的。

对于ASM上的数据库，根据数据库受损的实际情况考虑使用字典Dictionary(ASM)模式或者非字典Non-Dictionary(ASM)模式。

具体恢复流程为： Tools=>Recovery Wizard=>选择ASM下的字典或非字典模式=>加入所有的ASM Disk后点击ASM analyze

一般ASM下不需要填选block size和offset信息，点击Next，出现Data File列表，勾选SELECT ALL代表所有的数据文件

如果是多个DiskGROUP上有多个Database的情况，需要用户自己去选择对应的数据库的数据文件，之后点选Load，会进入和普通的字典模式没有区别的Schema=>table界面：

ORACLE PRM的GUI图形化交互界面让数据恢复变得很简单！

PRM is designed for Enterprise Database Recovery, which includes all Oracle DUL data recovery functionalities, and also easy-to-use GUI.

PRM For Oracle Database 3.1 GUI:

PRM almost can recovery Oracle database on all operating system, including:

Linux (RedHat, Centos, Oracle Linux, Suse)，AIX, Solaris, HPUX, Windows 2003 Server/XP/2000/NT。

PRM Support Oracle database which starts from 9i, 10g, 11g to 12c.

Case 1, if there is no backup, on disk segment header is corrupted

SQL> create table prm_test tablespace users as select * from dba_objects;

Table created.

SQL> alter system checkpoint;

System altered.

SQL> select count(*) from prm_test;

  COUNT(*)
----------
     77940

SQL> 
SQL> select header_file,header_block from dba_segments where segment_name='PRM_TEST';

HEADER_FILE HEADER_BLOCK
----------- ------------
          4      1298483

SQL> select name from v$datafile where file#=4;

NAME
--------------------------------------------------------------------------------
/s01/oradata/G10R25/datafile/o1_mf_users_8nx5srgb_.dbf




BBED> set mode edit
        MODE            Edit

BBED> set blocksize 8192
        BLOCKSIZE       8192

BBED> set block 1298483
        BLOCK#          1298483

BBED> set offset 999
        OFFSET          999

BBED>  modify /x 0xffff
Warning: contents of previous BIFILE will be lost. Proceed? (Y/N) y
 File: /s01/oradata/G10R25/datafile/o1_mf_users_8nx5srgb_.dbf (0)
 Block: 1298483           Offsets:  999 to 1510           Dba:0x00000000
------------------------------------------------------------------------
 ffff0000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 
 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 
 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 
 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 
 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 
 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 
 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 
 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 
 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 
 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 
 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 
 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 
 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 
 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 
 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 
 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 

 

BBED> sum
Check value for File 0, Block 1298483:
current = 0x18cc, required = 0xe733

SQL> select count(*) from prm_test;
select count(*) from prm_test
                     *
ERROR at line 1:
ORA-01578: ORACLE data block corrupted (file # 4, block # 1298483)
ORA-01110: data file 4:
'/s01/oradata/G10R25/datafile/o1_mf_users_8nx5srgb_.dbf'


SQL> alter session set events '10231 trace name context forever ,level 10';

Session altered.

SQL> select count(*) from prm_test;
select count(*) from prm_test
*
ERROR at line 1:
ORA-01578: ORACLE data block corrupted (file # 4, block # 1298483)
ORA-01110: data file 4:
'/s01/oradata/G10R25/datafile/o1_mf_users_8nx5srgb_.dbf'


Start PRM

./prm.sh

Recovery Wizard=》Dictionary Mode, if ASM, then choose Dictionary Mode(ASM) =>select data files (at least including system tablespace datafile and datafile where data located), and click load.

Attention: on AIX HPUX Sparc Solaris, and UNIX platform, choose Big Endian. Linux, Windows, X86 Solaris choose Little Endian

Find your table on the left and right click View

If we PRM show correct data, we recommend you to use DataBridge mode for inserting data back. Right click DataBridge and input connection info, and click DataBridge

As above, it has been successfully extract 77940 rows

CASE 2, No Archive Mode, a datafile has been offline, and related redo log has been overwritten. User can’t online this datafile again.

SQL> archive log list

Database log mode              No Archive Mode

Automatic archival             Disabled

Archive destination            /s01/arch

Oldest online log sequence     343

Current log sequence           348



SQL> select file_name,file_id from dba_data_files where tablespace_name='TBS5';



FILE_NAME                                   FILE_ID

---------------------------------------- ----------

/oracleasm/TBS5.256.844175363.dbf                 6







SQL> alter database datafile 6 offline drop;



Database altered.



SQL> alter system switch logfile;



System altered.



SQL> /



System altered.



SQL> /



System altered.



SQL> /



System altered.



SQL> /



System altered.



SQL> /



System altered.





SQL> recover datafile 6;

ORA-00279: change 34421776 generated at 04/19/2014 19:33:32 needed for thread 1
ORA-00289: suggestion : /s01/arch/1_354_831398352.dbf
ORA-00280: change 34421776 for thread 1 is in sequence #354





Specify log: {=suggested | filename | AUTO | CANCEL}
cancel
Media recovery cancelled.





[oracle@vrh8 ~]$ rman target /



Recovery Manager: Release 10.2.0.5.0 - Production on Sat Apr 19 19:49:22 2014
Copyright (c) 1982, 2007, Oracle.  All rights reserved.
connected to target database: G10R25 (DBID=2696593743)



RMAN> recover datafile 6;



Starting recover at 19-APR-14
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=147 devtype=DISK



starting media recovery



unable to find archive log
archive log thread=1 sequence=354
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 04/19/2014 19:49:29
RMAN-06054: media recovery requesting unknown log: thread 1 seq 354 lowscn 34421776





Here we can use PRM to extract all data which can not be online



SQL> select OWNER,SEGMENT_NAME,SEGMENT_TYPE from dba_segments where tablespace_name='TBS5';

OWNER SEGMENT_NAME                   SEGMENT_TYPE

----- ------------------------------ ------------------
SYS   VVVV                           TABLE
SYS   AVE2                           TABLE
SYS   AVE                            TABLE
SYS   ATEST1                         TABLE
SYS   BIN$2KcAMnnvGg/gQKjAvwEbRg==$0 TABLE
hu     TAB1                           TABLE







Start PRM



./prm.sh

Recovery Wizard=》Dictionary Mode, if ASM, then choose Dictionary Mode(ASM) =>select data files (at least including SYSTEM.DBF and data file where data located), and click load.

Attention: on AIX HPUX Sparc Solaris, and UNIX platform, choose Big Endian. Linux, Windows, X86 Solaris choose Little Endian

\

PRM-DUL  For Oracle Database 3.1 GUI

PRM almost can recovery Oracle database on all operating system, including:

Linux (RedHat, Centos, Oracle Linux, Sus,etc)，AIX, Solaris, HPUX, Windows 2003 Server/XP/2000/NT。

PRM Support Oracle database which starts from 9i, 10g, 11g to 12c.

PRM has full GUI wizard for data recovery processes:

ParnassusData Inc. also provides professional service for database recovery.

ParnassusData service guarantees your data 100% back.

PRM Highlight:

• Support Oracle 7.3, 8i, 9i, 10g, 11g, 12c all database recovery
• Work with all main operating system
• Unique DataBridge technology, and direct transition from source to destination
• Support Oracle LOB: CLOB、NCLOB、LOB, and LOB DataBridge (Source to Destination)
• Support Oracle Cluster, Partition, Sub-Partition, Complex Partition
• Integrated multi-language, character set, and compatible with nvarchar
• Support Oracle ASM, and provide free function for ASM clone
• Provide professional product service. Engineers are previous Oracle employee

PRM fit for below circumstance

• Truncate/Drop table without backup
• Oracle can not be open due to data dictionary damage
• Oracle can not be open due to part of data lost
• System tablespace lost
• Logical or physical disk damage
• ASM metadata is corrupted , DiskGroup can not be mounted