[English]
作者:
fuyuncat
来源:
www.HelloDBA.com
要保证数据安全,需要做好完整的数据备份恢复计划及容灾方案。在实际环境中,还是有很多数据库环境没有做到这些,并且出现一些灾难性的错误,例如Online Redo或者控制文件等重要文件损坏或丢失。发生这种错误时,往往常规的恢复手段无法找回数据,可能需要借助隐含参数、BBED甚至Dul工具进行数据挽救。那么我们就面临这样的问题:在什么样的最极端情况下,我们还能挽救回数据呢?理论上讲,我们的数据包含两个最基本的部分:即数据定义(也就是元数据)部分及数据主体部分。我们的用户数据存储在相应表空间的数据文件当中,而元数据部分则存储在系统表空间的数据文件当中。那么,假如进存在系统表空间及重要数据文件(而非所有数据文件)的情况下还有希望恢复数据吗?当然,这里不讨论使用Dul的方式进行回复。
就这个问题,我做了两个测试,一个是在Windows的11.2.0.1的环境中,一个是在Linux的10.2.0.1的环境当中。
首先,我们需要知道,除了数据本身之外,我们还需要知道那些信息才能进行恢复:
- 数据库版本及其所处平台、CPU位数
- 数据库名称
- 数据块大小
- 字符集
- 原始的数据文件位置及大小
以上信息基本上我们可以通过其他途径获取,例如客户端的配置。
获取到以上信息后,我们可以理清一下恢复的思路:
- 在与原环境基本一致的新环境中,创建一个同名的新数据库;
- 将新的系统文件替换为旧的系统文件;
- 修改旧的系统文件元数据,使其与新数据库匹配;
- 重新加载数据库后,再将用户数据文件植入
- 重命名数据文件,并使其ONLINE
- 用EXP方式导出数据
可以看到,以上几个步骤当中,最关键的是第三步。如果旧的系统文件被“偷梁换柱”置入新数据库中,那么旧数据库中的对象元数据也被带入了新的数据库当中。
以下过程为在Windows的11.2.0.1的环境中测试过程:
首先,配置参数文件。注意UNDO管理方式设为手动,因为旧数据库中UNDO文件已经丢失;_allow_resetlogs_corruption也要打开,因为新数据库中ONLINE REDO信息无法与旧数据库中的数据文件匹配:
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"
然后需要配置SID,设置密码,并启动window服务
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
现在可以建立新库:
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
重命名新库的系统文件,因为我们还需要它的文件头信息。拷贝旧库中文件到当前文件路径下:
SQL代码
- E:\ora11gr1\oradata\ORA11R2>rename SYSTEM01.DBF SYSTEM01_NEW.DBF
- E:\ora11gr1\oradata\ORA11R2>copy ..\ORA11R2_COPY\SYSTEM01.DBF
- 已复制 1 个文件。
- E:\ora11gr1\oradata\ORA11R2>copy ..\ORA11R2_COPY\DEMO_DATA.DBF
- 已复制 1 个文件。
利用工具,将新库系统文件的文件头部分拷贝到旧库的文件头。这里我们利用BBED拷贝(11g当中,bbed的2号数据块实际为1号数据块)
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
然后利用NID修改数据库ID
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
注意:第一次执行时,可能会出现ORA-00600错误
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
这种情况下,再次执行nid
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.
我们可以做一次verification,确认系统文件没有错误
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)
然后重建控制文件,并且OPEN 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.
如果此时数据库OPEN成功,那离我们的目标就非常接近了
查询用户数据文件在旧库中的文件号,此时应该可以读取到旧中的数据字典了(注意,我们建立新库时,并未创建相关视图及存储过程,这里读取的视图就是旧库中的视图)。从文件名可以猜测出其表空间的名称:
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
重命名数据文件,并且将其重新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.
创建一个临时表空间,并修改各个用户的临时表空间。因为我们在用EXP导出时,可能会用到临时表空间
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。现在可以用EXP将数据导出了
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.
这个环境中的恢复过程比较顺利。
而在下一个测试当中,我遇到更加复杂的情况:系统表空间保护两个不连续的系统文件;恢复过程中出现ORA错误及数据字典不匹配的情况。我借助了一些非常规的手段规避这些问题,达到数据拯救的目的。这一过程将在下一篇文章中演示。
--- Fuyuncat ---