Recover Oracle lost/deleted System01.dbf tablespace using PRM-DUL

Case 4: Deleted SYSTEM tablespace by mistake

 

A System Administrator of company D who deleted SYSTEM tablespace by mistake and make DB can not be open. Unfortunately, there is no RMAN backup available. Therefore, for company D try to use PRM-DUL to recover all data.

 

In this circumstance, run PRM-DUL and go into Recovery Wizard. Select “Non-Dictionary mode”:

 

In No-dictionary mode, we have to select DB Character Set and DB National Character Set. Because of while losing SYSTEM tablespace, database cannot find character set information.

 

Similarly as case 1, select all data (not including temp file), and correct Block Size and OFFSET

 

 

Then click scan button. Then PRM-DUL will scan all segment header and extents in datafile, and record it into SEG$.DAT and EXT$.DAT. In Oracle, each partition table or non-partition table has a segment header. Once we find segment header, we could find the whole table extent map information. Via extent map, we can get all record.

 

There is one exception, for example, there is one non-partition table that is stored in two database files. The segment header and half data are stored in datafile A, and the others are on datafile B.  While system tablespace and datafile A are lost, PRM-DUL couldn’t find segment header associated with problem table, but it can scan datafile B and get the rest extent map.

In order to recover data via segment header and extent map in no-dictionary mode.

PRM-DUL will create two files: SEG$.DAT(stores segment header info) and EXT$.DAT(stores extent info) ,which is also recorded in PRM-DUL embedded database.

 

 

After scan, there is database icon on the left.

 

Meanwhile, there are 2 option:

 

• Scan Tables From Segments:
  • System tablespace lost, but user tablespace datafiles are there
• Scan Tables From Extents
  • Only used when truncated data can not be recovered by Dictionary-Mode
  • Both system tablespace and segment header are lost

 

It is not necessary to use mode “Scan Tables From Extents” at the first time, unless you can’t find your data by “Scan Tables From Segment”.
Scan tables From segments should be your first choice.

 

 

After scanning tables from segments, there will be a tree diagram on the left.

 

 

 

Scan Tables is for constructing the data based on segment header in SEG$. The name of each node in the diagram is named by obj+ DATA OBJECT ID.

Click on node and check right side:

 

 

 

 

 

Intelligence on Data Analysis

 

Because of SYSTEM tablespace lost, there is not data structure information available in NO-Dictionary mode. The column information includes column name and data type. All these are storage in dictionary but not in table. Therefore, PRM-DUL need to guess the data type. PRM-DUL has a JAVA pre analysis algorithm, and has the ability to analysis more than 10 kinds of types.、

 

Intelligence analysis can successfully guess 90% of columns in most of circumstances

 

On the right side, the meaning of columns:

 

• Col1 no
• Seen Count
• MAX SIZE
• PCT NULL
• String Nice
• Number Nice
• Date Nice
• Timestamp Nice
• Timestamp with timezone Nice

 

 

Sample Data Analysis:

 

 

 

 

Intelligence Analysis will analyze 10 records and display the results. These results will help client to know the column information.

 

As in the picture, the there are 10 records which had been displayed all.

 

 

TRY TO ANALYZE UNKNOWN column type:

 

 

 

 

If PRM-DUL cannot recognize the column’s data type , you can specify data type by yourself.

 

So far, PRM-DUL does not support below types:

XDB.XDB$RAW_LIST_T、XMLTYPE、Customized TYPE

 

Unload Statement:

PRM-DUL generated unload scripts, and these scripts can be only used by PRM-DUL support engineers.

 

 

 

 

 

In “Non-Dictionary Mode”, Data Bridge is also applicable. Comparing ” Dictionary Mode”, the manger difference that the user can define the type in data transferring. As below picture, the column type is UNKNOW. These types might be PRM-DUL unsupported types for example: XML and etc.

 

If the user know the data type in this table (from schema design documents), it is necessary to specify the correct types manually.

 

 

CASE 5:deleted System Tablespace and Part of User tablespace datafile by mistake

 

User D deleted the system tablespace and part of user tablespace datafile by mistake.

In this circumstance, part of user data table was deleted, and this might includes datafile which stored segment header. Therefore it is better to use “Scan Tables From Extents” than” Scan Tables From Segment Header”.

 

Steps as Below:

 

1. Go to Recovery Wizard, select No-Dictionary mode，and added all usable data file. Then process them to scan database.
2. Select database, and right click Scan Tables From Extents
3. Analyze the data and implement data extraction and Data Bright
4. Following steps are the same with Case 4

 

CASE 6: rescue datafile from damaged diskgroup which can’t be mounted

 

User D chooses ASM instead of other filesystem. Since there are many bugs in version 11.2.0.1, it may happen that ASM DISKGROUP cannot be mounted or it does not work after repairing ASM Disk Header.

In this circumstance, user can use ASM Files Clone feature of PRM-DUL to rescue datafile from damaged ASM DiskGroup directly.

 

1. Open main interface, and select ASM File(s) Clone:

 

 

1. Enter ASM Disks Window, and click SELECt…to add ASM Disks. For example: /dev/asm-disk5(linux). And click ASM analyze.

 

 

 

 

 

ASM Files Clone feature will analyze ASM Disk header, in order to finding Disk group file and File Extent Map. All the information is recorded into PRM-DUL embedded database.    PRM-DUL can collect all Metadata, and analyze to show diagram.

 

 

 

1. After analysis of ASM Analyze, PRM-DUL will find the file list in Disk groups. Users can select the datafile/archivelog which need to be cloned to destination folder.

 

Click ASM Clone to start…

 

 

There is progress bar while file cloning.

 

 

 

ASM File Clone log as below:

 

 

Preparing selected files…Cloning +DATA2/ASMDB1/DATAFILE/TBS2.256.839732369:……………………..1024MB………………………………..2048MB………………………………..3072MB………………………………….4096MB ………………………………..5120MB ………………………………….6144MB ……………………………….7168MB …………………………………8192MB …………………………………9216MB …………………………………10240MB …………………………………11264MB …………………………………..12288MB …………………………………….13312MB …………………………….14336MB ……………………………………..15360MB ……………………………….16384MB …………………………………17408MB …………………………………18432MB …………………………………………………………………………………………….19456MB …………………………………… Cloned size for this file (in byte): 21475885056   Cloned successfully!     Cloning +DATA2/ASMDB1/ARCHIVELOG/2014_02_17/thread_1_seq_47.257.839732751: …… Cloned size for this file (in byte): 29360128   Cloned successfully!     Cloning +DATA2/ASMDB1/ARCHIVELOG/2014_02_17/thread_1_seq_48.258.839732751: …… Cloned size for this file (in byte): 1048576   Cloned successfully!         All selected files were cloned done.

 

 

 

 

 

1. It is necessary to validate cloned data via “dbv” or “rman validate”, for example:

 

rman target /RMAN> catalog datafilecopy ‘/home/oracle/asm_clone/TBS2.256.839732369.dbf’;cataloged datafile copydatafile copy file name=/home/oracle/asm_clone/TBS2.256.839732369.dbf RECID=2 STAMP=839750901   RMAN> validate datafilecopy ‘/home/oracle/asm_clone/TBS2.256.839732369.dbf’;   Starting validate at 17-FEB-14 using channel ORA_DISK_1 channel ORA_DISK_1: starting validation of datafile channel ORA_DISK_1: including datafile copy of datafile 00016 in backup set input file name=/home/oracle/asm_clone/TBS2.256.839732369.dbf channel ORA_DISK_1: validation complete, elapsed time: 00:03:35 List of Datafile Copies ======================= File Status Marked Corrupt Empty Blocks Blocks Examined High SCN —- —— ————– ———— ————— ———- 16   OK     0              2621313      2621440         1945051 File Name: /home/oracle/asm_clone/TBS2.256.839732369.dbf Block Type Blocks Failing Blocks Processed ———- ————– —————- Data       0              0 Index      0              0 Other      0              127   Finished validate at 17-FEB-14

 

When using PRM-DUL in ASM of ASMLIB?

Simple and Clear: asmlib related ASM DISK is stored in OS as ll /dev/oracleasm/disks. For example: Add files of /dev/oracleasm/disks into PRM-DUL ASM DISK

 

$ll /dev/oracleasm/diskstotal 0brw-rw—-  1 oracle dba 8,  97 Apr 28 15:20 VOL001brw-rw—-  1 oracle dba 8,  81 Apr 28 15:20 VOL002brw-rw—-  1 oracle dba 8,  65 Apr 28 15:20 VOL003brw-rw—-  1 oracle dba 8,  49 Apr 28 15:20 VOL004brw-rw—-  1 oracle dba 8,  33 Apr 28 15:20 VOL005 brw-rw—-  1 oracle dba 8,  17 Apr 28 15:20 VOL006 brw-rw—-  1 oracle dba 8, 129 Apr 28 15:20 VOL007 brw-rw—-  1 oracle dba 8, 113 Apr 28 15:20 VOL008