Rescue data in the worst situation (1)

Date: 2013-03-19 09:45:41

Share to

To guarantee the safety of our data, we need to prepare a good backup/restore plan as well as Disaster Recovery solutions. However, for some reasons, many database systems are not maintained very well, and may encounter some fatal issues, e.g. Control files and Online REDO log fils are lost. In such situation, we need use some un-normal methods, such as hidden parameters, BBED and even Dul, to rescue the data. Now, my question is that, what is the worst situation having hope to rescue data. Theoretically, the data is comprised 2 important parts, data definition (also known as meta data) and data records. The data records are stored in the user data files, and the meta data is stored in system data files. Let's say, if there are only system files and key user data files (not all of data files) are un-corrupted, is it possible to rescue the data? Of course, we are not discussing using Dul to rescue the data.

Regarding this question, I have done 2 tests to rescue the data under such situation. One was completed in Oracle 11.2.0.1 in windows, the other was completed in Oracle 10.2.0.1 in Linux.

First of all, to rescue the data, we need to know what kinds of information we should get. I listed them as below,

RDBMS version and the platform, CPU bit size

database name

data block size

character sets

original data file position and size

We can gain this information via other ways, for example, the configuration of the clients.

With this prepared, I planned the procedure of the data rescuing.

In the similar environment, create a new database with same name

Replace new system files with old system files;

modify the file header of the old systme files to align with the new database;

mount and open database, add the old user data files;

rename user data file and change it online;

export the data in the file using EXP;

The key steps is definitely the 3rd step. If old system files are injected in the new database successfully, the object meta data are also migrated into this database.

Below is the demonstration of my test in 11.2.0.1 in windows.

First, we need configure the parameter file. Please note that the UNDO management is set as manual, because the old one is lost. And _allow_resetlogs_corruption is set as TRUE, because the new online redo logs cannot match to old data files.

SQL代码

_allow_resetlogs_corruption = true

control_files              = (E:\ora11gr1\oradata\ORA11R2\control01.ctl,

                              E:\ora11gr1\oradata\ORA11R2\control02.ctl,

                              E:\ora11gr1\oradata\ORA11R2\control03.ctl)

db_name                    = ORA11R2

db_domain                  = ""

db_block_size              = 8192

undo_management            = manual

undo_tablespace            = ''

UNDO_RETENTION             = 900

nls_language               = "AMERICAN"

nls_territory              = "AMERICA"

Then configure SID, password and start the windows service.

SQL代码

set ORACLE_SID=ora11r2



E:\>cd E:\ora11gr1\product\11.2.0\dbhome_1\database



E:\ora11gr1\product\11.2.0\dbhome_1\database>orapwd password=sys file=PWDORA11R2.ORA



E:\ora11gr1\product\11.2.0\dbhome_1\database>oradim -startup -sid ORA11R2

And we can create the database now, the file size should be same as the old file:

SQL代码

HELLODBA.COM>startup nomount

ORACLE instance started.



Total System Global Area  150667264 bytes

Fixed Size                  1373152 bytes

Variable Size              92277792 bytes

Database Buffers           50331648 bytes

Redo Buffers                6684672 bytes

HELLODBA.COM>CREATE DATABASE ORA11R2

  2     USER SYS IDENTIFIED BY sys

  3     USER SYSTEM IDENTIFIED BY oracle

  4     LOGFILE GROUP 1 ('E:\ora11gr1\oradata\ORA11R2\redo01.log') SIZE 20M,

  5             GROUP 2 ('E:\ora11gr1\oradata\ORA11R2\redo02.log') SIZE 20M,

  6             GROUP 3 ('E:\ora11gr1\oradata\ORA11R2\redo03.log') SIZE 20M

  7     MAXLOGFILES 5

  8     MAXLOGMEMBERS 5

  9     MAXLOGHISTORY 1

10     MAXDATAFILES 100

11     MAXINSTANCES 1

12     CHARACTER SET US7ASCII

13     NATIONAL CHARACTER SET AL16UTF16

14     DATAFILE 'E:\ora11gr1\oradata\ORA11R2\system01.dbf' SIZE 740M REUSE

15     EXTENT MANAGEMENT LOCAL

16     SYSAUX DATAFILE 'E:\ora11gr1\oradata\ORA11R2\sysaux01.dbf' SIZE 100M

17     DEFAULT TEMPORARY TABLESPACE tempts1

18        TEMPFILE 'E:\ora11gr1\oradata\ORA11R2\temp01.dbf'

19        SIZE 20M

20  ;



Database created.



HELLODBA.COM>shutdown

Database closed.

Database dismounted.

ORACLE instance shut down.

HELLODBA.COM>exit

Rename the new system files, because we need the file header; copy the old data files to current data file folder

SQL代码

E:\ora11gr1\oradata\ORA11R2>rename SYSTEM01.DBF SYSTEM01_NEW.DBF



E:\ora11gr1\oradata\ORA11R2>copy ..\ORA11R2_COPY\SYSTEM01.DBF

Copied         1 File



E:\ora11gr1\oradata\ORA11R2>copy ..\ORA11R2_COPY\DEMO_DATA.DBF

Copied         1 File

Copy the file header of new system file to old system file using tool. I used BBED (in windows, bbed recognizes the 2nd blocks as 1st block in 11g)

SQL代码

D:\OracleDoc\bbed9i>bbed parfile=bbed.par



BBED: Release 2.0.0.0.0 - Limited Production on Fri Mar 8 14:30:31 2013



Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.



************* !!! For Oracle Internal Use only !!! ***************



BBED> info

File#  Name                                                        Size(blks)

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

    17  E:\Ora11gr1\Oradata\ORA11R2\SYSTEM01_NEW.DBF                     94721

    18  E:\Ora11gr1\Oradata\ORA11R2\SYSTEM01.DBF                         94721

BBED> copy file 17 block 2 file 18 block 2

Warning: contents of previous BIFILE will be lost. Proceed? (Y/N) y

File: E:\Ora11gr1\Oradata\ORA11R2\CONTROL01.CTL (1)

Block: 1                Offsets:    0 to  511           Dba:0x00400001

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

0ba20000 01004000 00000000 00000104 d4540000 00000000 0000200b bda06372

Then modify the database ID using NID

SQL代码

HELLODBA.COM>startup mount

ORACLE instance started.



Total System Global Area  150667264 bytes

Fixed Size                  1373152 bytes

Variable Size              92277792 bytes

Database Buffers           50331648 bytes

Redo Buffers                6684672 bytes

Database mounted.

HELLODBA.COM>exit



C:\Users\huanged>nid target=sys/sys

Please note that we may encourter ORA-00600 when first change the DB ID.

SQL代码

ORA-00600: internal error code, arguments: [krbnpdf_wrngdbid_3], [E:\ORA11GR1\ORADATA\ORA11R

01.DBF], [1919131837], [1935277929], [1935311342], [], [], [], [], [], [], []

ORA-06512: at "SYS.X$DBMS_BACKUP_RESTORE", line 6848

ORA-06512: at line 1

In this case, run NID again

SQL代码

C:\Users\huanged>nid target=sys/sys



DBNEWID: Release 11.2.0.1.0 - Production on Fri Mar 8 13:56:35 2013

... ...

Database ID for database ORA11R2 changed to 1919131837.

All previous backups and archived redo logs for this database are unusable.

Database has been shutdown, open database with RESETLOGS option.

Succesfully changed database ID.

DBNEWID - Completed succesfully.

We may do a verification to confirm no corrupted data in the file

SQL代码

C:\Users\huanged>dbv file=E:\Ora11gr1\Oradata\ORA11R2\SYSTEM01.DBF



DBVERIFY: Release 11.2.0.1.0 - Production on Fri Mar 8 13:59:46 2013



Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.



DBVERIFY - Verification starting : FILE = E:\ORA11GR1\ORADATA\ORA11R2\SYSTEM01.DBF





DBVERIFY - Verification complete



Total Pages Examined         : 94720

Total Pages Processed (Data) : 64118

Total Pages Failing   (Data) : 0

Total Pages Processed (Index): 13493

Total Pages Failing   (Index): 0

Total Pages Processed (Other): 3870

Total Pages Processed (Seg)  : 1

Total Pages Failing   (Seg)  : 0

Total Pages Empty            : 13239

Total Pages Marked Corrupt   : 0

Total Pages Influx           : 0

Total Pages Encrypted        : 0

Highest block SCN            : 4072482757 (0.4072482757)

Then re-create control files, and open database RESETLOGS

SQL代码

HELLODBA.COM>startup nomount

ORACLE instance started.



Total System Global Area  150667264 bytes

Fixed Size                  1373152 bytes

Variable Size              92277792 bytes

Database Buffers           50331648 bytes

Redo Buffers                6684672 bytes



HELLODBA.COM>CREATE CONTROLFILE REUSE DATABASE ORA11R2 RESETLOGS NOARCHIVELOG

  2      MAXLOGFILES 5

  3      MAXLOGMEMBERS 5

  4      MAXDATAFILES 100

  5      MAXINSTANCES 1

  6      MAXLOGHISTORY 292

  7  LOGFILE

  8    GROUP 1 'E:\ORA11GR1\ORADATA\ORA11R2\REDO01.LOG' SIZE 20M BLOCKSIZE 512,

  9    GROUP 2 'E:\ORA11GR1\ORADATA\ORA11R2\REDO02.LOG' SIZE 20M BLOCKSIZE 512,

10    GROUP 3 'E:\ORA11GR1\ORADATA\ORA11R2\REDO03.LOG' SIZE 20M BLOCKSIZE 512

11  DATAFILE

12    'E:\ORA11GR1\ORADATA\ORA11R2\SYSTEM01.DBF' SIZE 740M

13    --, 'E:\ORA11GR1\ORADATA\ORA11R2\SYSAUX01.DBF' size 100M

14    --, 'E:\ORA11GR1\ORADATA\ORA11R2\UNDOTBS01.DBF'

15  CHARACTER SET US7ASCII

16  ;



Control file created.



HELLODBA.COM>alter session set events '10046 trace name context forever, level 12';



Session altered.



HELLODBA.COM>recover database using backup controlfile until cancel;

ORA-00279: change 4072482761 generated at 03/07/2013 09:28:18 needed for thread 1

ORA-00289: suggestion : E:\ORA11GR1\PRODUCT\11.2.0\DBHOME_1\RDBMS\ARC0000000215_0793385284.0001

ORA-00280: change 4072482761 for thread 1 is in sequence #215





Specify log: {<RET>=suggested | filename | AUTO | CANCEL}

cancel

Media recovery cancelled.

HELLODBA.COM>alter database open resetlogs;



Database altered.

If the database is opened successfully, it means we are close to the final objective.

And we can query the meta data via data dictionary in old database. Please note that we did not create views and SPs when we creating the new database, we are querying the old views now.

We can guess the tablespace name from the name of the data file.

SQL代码

HELLODBA.COM>select file_id, tablespace_name from dba_data_files where tablespace_name like '%DEMO%';



   FILE_ID TABLESPACE_NAME

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

         5 DEMO_DATA

         6 DEMO_INDX



HELLODBA.COM>select file#, name from v$datafile where file#=5;



     FILE# NAME

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

         5 E:\ORA11GR1\PRODUCT\11.2.0\DBHOME_1\DATABASE\MISSING00005

Rename the datafile, and alter it online.

SQL代码

HELLODBA.COM>ALTER DATABASE RENAME FILE 'E:\ORA11GR1\PRODUCT\11.2.0\DBHOME_1\DATABASE\MISSING00005' TO 'E:\ORA11GR1\ORADATA\ORA11R2\DEMO_DATA.DBF';



Database altered.



HELLODBA.COM>shutdown

Database closed.

Database dismounted.

ORACLE instance shut down.

HELLODBA.COM>startup mount

ORACLE instance started.



Total System Global Area  150667264 bytes

Fixed Size                  1373152 bytes

Variable Size              92277792 bytes

Database Buffers           50331648 bytes

Redo Buffers                6684672 bytes

Database mounted.



HELLODBA.COM>recover database until cancel;

Media recovery complete.

HELLODBA.COM>ALTER DATABASE DATAFILE 'E:\ORA11GR1\ORADATA\ORA11R2\DEMO_DATA.DBF' ONLINE;



Database altered.



HELLODBA.COM>alter database open resetlogs;



Database altered.

Create a temporary tablespace and modify the user temp tablespace to the new one, because it may use temp tablespace when exporting data.

SQL代码

HELLODBA.COM>CREATE TEMPORARY TABLESPACE temp2 TEMPFILE 'E:\ORA11GR1\ORADATA\ORA11R2\TEMP2.DBF' size 1G;



Tablespace created.



HELLODBA.COM>select 'ALTER USER '||username||' TEMPORARY TABLESPACE TEMP2;' from dba_users;



'ALTERUSER'||USERNAME||'TEMPORARYTABLESPACETEMP2;'

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

ALTER USER OUTLN TEMPORARY TABLESPACE TEMP2;

... ...



HELLODBA.COM>ALTER USER OUTLN TEMPORARY TABLESPACE TEMP2;



User altered.



... ...



HELLODBA.COM>exit

OK. We can export the data now.

SQL代码

D:\OracleDoc\bbed9i>exp system/oracle tablespaces=DEMO_DATA file=resecue.dat log=resecue.log

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

Export done in AL32UTF8 character set and AL16UTF16 NCHAR character set



About to export selected tablespaces ...

For tablespace DEMO_DATA ...

. exporting cluster definitions

. exporting table definitions

. . exporting table                             AA          1 rows exported

... ...

. . exporting table              TRANSACTIONS_BACK         10 rows exported

. . exporting table                        TSOURCE       9517 rows exported

. . exporting table                   TSOURCE_BACK       9517 rows exported

. . exporting table                          USERS         13 rows exported

. exporting referential integrity constraints

EXP-00112: Index T1_IDX_7 is of XMLType and could not be exported

EXP-00078: Error exporting metadata for index T1_IDX_7. Index creation will be skipped

EXP-00112: Index T_XML_TAB1_IDX_1 is of XMLType and could not be exported

EXP-00078: Error exporting metadata for index T_XML_TAB1_IDX_1. Index creation will be skipped

. exporting triggers

Export terminated successfully with warnings.

This test was ran smoothly.

While the situation is more complicated in the following test in 10.2.0.1 in Linux. The system tablespace has 2 data files whose file IDs are not in sequence; and I encountered ORA errors and other issues. I used unnormal methods to complete the resuse procedure. I will demonstrate it in the following blog.

--- Wei Huang ---

Previous：Understand Buffer Cache in further --- Build up queryable Buffer Cache Chain data

Next：Rescue data in the worst situation (2)

Return To this Category