HelloDBA [中文]
Search Internet Search HelloDBA
  Oracle Technology Site. email: fuyuncat@gmail.com  MSN: fuyuncat@hotmail.com Add to circle  acoug  acoug 

Rescue data in the worst situation (1)

[中文]

Author:  fuyuncat

Source:  www.HelloDBA.com

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

Share to  Twitter Facebook GMail Blogger Orkut Google Bookmarks Sina Weibo QQ Renren Tieba Kaixin Douban Taobao

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.

 

  1.   In the similar environment, create a new database with same name
  2.   Replace new system files with old system files;
  3.   modify the file header of the old systme files to align with the new database;
  4.   mount and open database, add the old user data files;
  5.   rename user data file and change it online;
  6.   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代码
  1. _allow_resetlogs_corruption = true  
  2. control_files              = (E:\ora11gr1\oradata\ORA11R2\control01.ctl,  
  3.                               E:\ora11gr1\oradata\ORA11R2\control02.ctl,  
  4.                               E:\ora11gr1\oradata\ORA11R2\control03.ctl)  
  5. db_name                    = ORA11R2  
  6. db_domain                  = ""  
  7. db_block_size              = 8192  
  8. undo_management            = manual  
  9. undo_tablespace            = ''  
  10. UNDO_RETENTION             = 900  
  11. nls_language               = "AMERICAN"  
  12. nls_territory              = "AMERICA"  

Then configure SID, password and start the windows service.

SQL代码
  1. set ORACLE_SID=ora11r2  
  2.   
  3. E:\>cd E:\ora11gr1\product\11.2.0\dbhome_1\database  
  4.   
  5. E:\ora11gr1\product\11.2.0\dbhome_1\database>orapwd password=sys file=PWDORA11R2.ORA  
  6.   
  7. 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代码
  1. HELLODBA.COM>startup nomount  
  2. ORACLE instance started.  
  3.   
  4. Total System Global Area  150667264 bytes  
  5. Fixed Size                  1373152 bytes  
  6. Variable Size              92277792 bytes  
  7. Database Buffers           50331648 bytes  
  8. Redo Buffers                6684672 bytes  
  9. HELLODBA.COM>CREATE DATABASE ORA11R2  
  10.   2     USER SYS IDENTIFIED BY sys  
  11.   3     USER SYSTEM IDENTIFIED BY oracle  
  12.   4     LOGFILE GROUP 1 ('E:\ora11gr1\oradata\ORA11R2\redo01.log'SIZE 20M,  
  13.   5             GROUP 2 ('E:\ora11gr1\oradata\ORA11R2\redo02.log'SIZE 20M,  
  14.   6             GROUP 3 ('E:\ora11gr1\oradata\ORA11R2\redo03.log'SIZE 20M  
  15.   7     MAXLOGFILES 5  
  16.   8     MAXLOGMEMBERS 5  
  17.   9     MAXLOGHISTORY 1  
  18.  10     MAXDATAFILES 100  
  19.  11     MAXINSTANCES 1  
  20.  12     CHARACTER SET US7ASCII  
  21.  13     NATIONAL CHARACTER SET AL16UTF16  
  22.  14     DATAFILE 'E:\ora11gr1\oradata\ORA11R2\system01.dbf' SIZE 740M REUSE  
  23.  15     EXTENT MANAGEMENT LOCAL  
  24.  16     SYSAUX DATAFILE 'E:\ora11gr1\oradata\ORA11R2\sysaux01.dbf' SIZE 100M  
  25.  17     DEFAULT TEMPORARY TABLESPACE tempts1  
  26.  18        TEMPFILE 'E:\ora11gr1\oradata\ORA11R2\temp01.dbf'  
  27.  19        SIZE 20M  
  28.  20  ;  
  29.   
  30. Database created.  
  31.   
  32. HELLODBA.COM>shutdown  
  33. Database closed.  
  34. Database dismounted.  
  35. ORACLE instance shut down.  
  36. 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代码
  1. E:\ora11gr1\oradata\ORA11R2>rename SYSTEM01.DBF SYSTEM01_NEW.DBF  
  2.   
  3. E:\ora11gr1\oradata\ORA11R2>copy ..\ORA11R2_COPY\SYSTEM01.DBF  
  4. Copied         1 File  
  5.   
  6. E:\ora11gr1\oradata\ORA11R2>copy ..\ORA11R2_COPY\DEMO_DATA.DBF  
  7. 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代码
  1. D:\OracleDoc\bbed9i>bbed parfile=bbed.par  
  2.   
  3. BBED: Release 2.0.0.0.0 - Limited Production on Fri Mar 8 14:30:31 2013  
  4.   
  5. Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.  
  6.   
  7. ************* !!! For Oracle Internal Use only !!! ***************  
  8.   
  9. BBED> info  
  10.  File#  Name                                                        Size(blks)  
  11.  -----  ----                                                        ----------  
  12.     17  E:\Ora11gr1\Oradata\ORA11R2\SYSTEM01_NEW.DBF                     94721  
  13.     18  E:\Ora11gr1\Oradata\ORA11R2\SYSTEM01.DBF                         94721  
  14. BBED> copy file 17 block 2 file 18 block 2  
  15. Warning: contents of previous BIFILE will be lost. Proceed? (Y/N) y  
  16.  File: E:\Ora11gr1\Oradata\ORA11R2\CONTROL01.CTL (1)  
  17.  Block: 1                Offsets:    0 to  511           Dba:0x00400001  
  18. ------------------------------------------------------------------------  
  19.  0ba20000 01004000 00000000 00000104 d4540000 00000000 0000200b bda06372  

Then modify the database ID using NID

SQL代码
  1. HELLODBA.COM>startup mount  
  2. ORACLE instance started.  
  3.   
  4. Total System Global Area  150667264 bytes  
  5. Fixed Size                  1373152 bytes  
  6. Variable Size              92277792 bytes  
  7. Database Buffers           50331648 bytes  
  8. Redo Buffers                6684672 bytes  
  9. Database mounted.  
  10. HELLODBA.COM>exit  
  11.   
  12. C:\Users\huanged>nid target=sys/sys   

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

SQL代码
  1. ORA-00600: internal error code, arguments: [krbnpdf_wrngdbid_3], [E:\ORA11GR1\ORADATA\ORA11R  
  2. 01.DBF], [1919131837], [1935277929], [1935311342], [], [], [], [], [], [], []  
  3. ORA-06512: at "SYS.X$DBMS_BACKUP_RESTORE", line 6848  
  4. ORA-06512: at line 1  

In this case, run NID again

SQL代码
  1. C:\Users\huanged>nid target=sys/sys   
  2.   
  3. DBNEWID: Release 11.2.0.1.0 - Production on Fri Mar 8 13:56:35 2013  
  4. ... ...  
  5. Database ID for database ORA11R2 changed to 1919131837.  
  6. All previous backups and archived redo logs for this database are unusable.  
  7. Database has been shutdown, open database with RESETLOGS option.  
  8. Succesfully changed database ID.  
  9. DBNEWID - Completed succesfully.  

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

SQL代码
  1. C:\Users\huanged>dbv file=E:\Ora11gr1\Oradata\ORA11R2\SYSTEM01.DBF  
  2.   
  3. DBVERIFY: Release 11.2.0.1.0 - Production on Fri Mar 8 13:59:46 2013  
  4.   
  5. Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.  
  6.   
  7. DBVERIFY - Verification starting : FILE = E:\ORA11GR1\ORADATA\ORA11R2\SYSTEM01.DBF  
  8.   
  9.   
  10. DBVERIFY - Verification complete  
  11.   
  12. Total Pages Examined         : 94720  
  13. Total Pages Processed (Data) : 64118  
  14. Total Pages Failing   (Data) : 0  
  15. Total Pages Processed (Index): 13493  
  16. Total Pages Failing   (Index): 0  
  17. Total Pages Processed (Other): 3870  
  18. Total Pages Processed (Seg)  : 1  
  19. Total Pages Failing   (Seg)  : 0  
  20. Total Pages Empty            : 13239  
  21. Total Pages Marked Corrupt   : 0  
  22. Total Pages Influx           : 0  
  23. Total Pages Encrypted        : 0  
  24. Highest block SCN            : 4072482757 (0.4072482757)  

Then re-create control files, and open database RESETLOGS

SQL代码
  1. HELLODBA.COM>startup nomount  
  2. ORACLE instance started.  
  3.   
  4. Total System Global Area  150667264 bytes  
  5. Fixed Size                  1373152 bytes  
  6. Variable Size              92277792 bytes  
  7. Database Buffers           50331648 bytes  
  8. Redo Buffers                6684672 bytes  
  9.   
  10. HELLODBA.COM>CREATE CONTROLFILE REUSE DATABASE ORA11R2 RESETLOGS NOARCHIVELOG  
  11.   2      MAXLOGFILES 5  
  12.   3      MAXLOGMEMBERS 5  
  13.   4      MAXDATAFILES 100  
  14.   5      MAXINSTANCES 1  
  15.   6      MAXLOGHISTORY 292  
  16.   7  LOGFILE  
  17.   8    GROUP 1 'E:\ORA11GR1\ORADATA\ORA11R2\REDO01.LOG' SIZE 20M BLOCKSIZE 512,  
  18.   9    GROUP 2 'E:\ORA11GR1\ORADATA\ORA11R2\REDO02.LOG' SIZE 20M BLOCKSIZE 512,  
  19.  10    GROUP 3 'E:\ORA11GR1\ORADATA\ORA11R2\REDO03.LOG' SIZE 20M BLOCKSIZE 512  
  20.  11  DATAFILE  
  21.  12    'E:\ORA11GR1\ORADATA\ORA11R2\SYSTEM01.DBF' SIZE 740M  
  22.  13    --, 'E:\ORA11GR1\ORADATA\ORA11R2\SYSAUX01.DBF' size 100M  
  23.  14    --, 'E:\ORA11GR1\ORADATA\ORA11R2\UNDOTBS01.DBF'  
  24.  15  CHARACTER SET US7ASCII  
  25.  16  ;  
  26.   
  27. Control file created.  
  28.   
  29. HELLODBA.COM>alter session set events '10046 trace name context forever, level 12';  
  30.   
  31. Session altered.  
  32.   
  33. HELLODBA.COM>recover database using backup controlfile until cancel;  
  34. ORA-00279: change 4072482761 generated at 03/07/2013 09:28:18 needed for thread 1  
  35. ORA-00289: suggestion : E:\ORA11GR1\PRODUCT\11.2.0\DBHOME_1\RDBMS\ARC0000000215_0793385284.0001  
  36. ORA-00280: change 4072482761 for thread 1 is in sequence #215  
  37.   
  38.   
  39. Specify log: {<RET>=suggested | filename | AUTO | CANCEL}  
  40. cancel  
  41. Media recovery cancelled.  
  42. HELLODBA.COM>alter database open resetlogs;  
  43.   
  44. 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代码
  1. HELLODBA.COM>select file_id, tablespace_name from dba_data_files where tablespace_name like '%DEMO%';  
  2.   
  3.    FILE_ID TABLESPACE_NAME  
  4. ---------- ------------------------------  
  5.          5 DEMO_DATA  
  6.          6 DEMO_INDX  
  7.   
  8. HELLODBA.COM>select file#, name from v$datafile where file#=5;  
  9.   
  10.      FILE# NAME  
  11. ---------- -----------------------------------------------------------------  
  12.          5 E:\ORA11GR1\PRODUCT\11.2.0\DBHOME_1\DATABASE\MISSING00005  

Rename the datafile, and alter it online.

SQL代码
  1. HELLODBA.COM>ALTER DATABASE RENAME FILE 'E:\ORA11GR1\PRODUCT\11.2.0\DBHOME_1\DATABASE\MISSING00005' TO 'E:\ORA11GR1\ORADATA\ORA11R2\DEMO_DATA.DBF';  
  2.   
  3. Database altered.  
  4.   
  5. HELLODBA.COM>shutdown  
  6. Database closed.  
  7. Database dismounted.  
  8. ORACLE instance shut down.  
  9. HELLODBA.COM>startup mount  
  10. ORACLE instance started.  
  11.   
  12. Total System Global Area  150667264 bytes  
  13. Fixed Size                  1373152 bytes  
  14. Variable Size              92277792 bytes  
  15. Database Buffers           50331648 bytes  
  16. Redo Buffers                6684672 bytes  
  17. Database mounted.  
  18.   
  19. HELLODBA.COM>recover database until cancel;  
  20. Media recovery complete.  
  21. HELLODBA.COM>ALTER DATABASE DATAFILE 'E:\ORA11GR1\ORADATA\ORA11R2\DEMO_DATA.DBF' ONLINE;  
  22.   
  23. Database altered.  
  24.   
  25. HELLODBA.COM>alter database open resetlogs;  
  26.   
  27. 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代码
  1. HELLODBA.COM>CREATE TEMPORARY TABLESPACE temp2 TEMPFILE 'E:\ORA11GR1\ORADATA\ORA11R2\TEMP2.DBF' size 1G;  
  2.   
  3. Tablespace created.  
  4.   
  5. HELLODBA.COM>select 'ALTER USER '||username||' TEMPORARY TABLESPACE TEMP2;' from dba_users;  
  6.   
  7. 'ALTERUSER'||USERNAME||'TEMPORARYTABLESPACETEMP2;'  
  8. ---------------------------------------------------------------------  
  9. ALTER USER OUTLN TEMPORARY TABLESPACE TEMP2;  
  10. ... ...  
  11.   
  12. HELLODBA.COM>ALTER USER OUTLN TEMPORARY TABLESPACE TEMP2;  
  13.   
  14. User altered.  
  15.   
  16. ... ...  
  17.   
  18. HELLODBA.COM>exit  

OK. We can export the data now.

SQL代码
  1. D:\OracleDoc\bbed9i>exp system/oracle tablespaces=DEMO_DATA file=resecue.dat log=resecue.log  
  2. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production  
  3. With the Partitioning, OLAP, Data Mining and Real Application Testing options  
  4. Export done in AL32UTF8 character set and AL16UTF16 NCHAR character set  
  5.   
  6. About to export selected tablespaces ...  
  7. For tablespace DEMO_DATA ...  
  8. . exporting cluster definitions  
  9. . exporting table definitions  
  10. . . exporting table                             AA          1 rows exported  
  11. ... ...  
  12. . . exporting table              TRANSACTIONS_BACK         10 rows exported  
  13. . . exporting table                        TSOURCE       9517 rows exported  
  14. . . exporting table                   TSOURCE_BACK       9517 rows exported  
  15. . . exporting table                          USERS         13 rows exported  
  16. . exporting referential integrity constraints  
  17. EXP-00112: Index T1_IDX_7 is of XMLType and could not be exported  
  18. EXP-00078: Error exporting metadata for index T1_IDX_7. Index creation will be skipped  
  19. EXP-00112: Index T_XML_TAB1_IDX_1 is of XMLType and could not be exported  
  20. EXP-00078: Error exporting metadata for index T_XML_TAB1_IDX_1. Index creation will be skipped  
  21. . exporting triggers  
  22. 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 ---

Top

Copyright ©2005, HelloDBA.Com All reseverd.

Declaration
by fuyuncat