【恢复】基于用户管理的热备份对非系统表空间的损坏进行恢复

本文给出基于用户管理的热备份对非系统表空间的损坏进行恢复实例。
备份恢复前提：
①数据库运行在归档模式
②具有可用的用户管理的备份

1.针对表空间TBS_SEC_D进行模拟损坏和恢复
sys@ora10g> col tablespace_name format a30
sys@ora10g> col file_name for a66
sys@ora10g> select tablespace_name,file_name,bytes/1024/1024 MB from dba_data_files where tablespace_name='TBS_SEC_D';

TABLESPACE_NAME FILE_NAME                                           MB
--------------- ------------------------------------------------ -----
TBS_SEC_D       /oracle/ora10gR2/oradata/ora10g/tbs_sec_d_01.dbf  2048

2.完成用户管理的热备份
sys@ora10g> alter tablespace tbs_sec_d begin backup;

Tablespace altered.

sys@ora10g> ! cp /oracle/ora10gR2/oradata/ora10g/tbs_sec_d_01.dbf /home/oracle

sys@ora10g> alter tablespace tbs_sec_d end backup;

Tablespace altered.

3.验证备份信息
1）操作系统上备份文件信息
sys@ora10g> ! ls -l /home/oracle/tbs_sec_d_01.dbf
-rw-r----- 1 oracle oinstall 2147491840 Apr 30 22:36 /home/oracle/tbs_sec_d_01.dbf

2）数据库中查询备份信息
sys@ora10g> alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';

Session altered.

sys@ora10g> col STATUS for a30
sys@ora10g> select * from v$backup;

     FILE# STATUS           CHANGE# TIME
---------- ------------- ---------- -------------------
         1 NOT ACTIVE             0
         2 NOT ACTIVE             0
         3 NOT ACTIVE             0
         4 NOT ACTIVE             0
         5 NOT ACTIVE        925678 2011-04-30 22:34:51

4.在sec用户下模拟用户操作
1）连接到sec用户
sys@ora10g> conn sec/sec
Connected.

2）确定sec用户下未包含数据库对象
sec@ora10g> select * from obj;

no rows selected

这里是为了保证测试用户的纯净性，用户后续恢复后的对比。

3）创建测试表T并初始化数据
sec@ora10g> create table t (x varchar2(8));

Table created.

sec@ora10g> insert into t values('secooler');

1 row created.

sec@ora10g> commit;

Commit complete.

sec@ora10g> select * from t;

X
--------
secooler

4）确认表T所属的表空间是TBS_SEC_D
sec@ora10g> select SEGMENT_NAME,SEGMENT_TYPE,TABLESPACE_NAME from user_segments where SEGMENT_NAME='T';

SEGMENT_NAME   SEGMENT_TYPE       TABLESPACE_NAME
-------------- ------------------ --------------------
T              TABLE              TBS_SEC_D

也可以在创建表的过程中指定表空间。

5）切换日志文件
sys@ora10g> alter system switch logfile;

System altered.

5.人为损坏表空间TBS_SEC_D
这里通过损坏TBS_SEC_D对应的数据文件达到目的。
sec@ora10g> ! ls -l /oracle/ora10gR2/oradata/ora10g/tbs_sec_d_01.dbf
-rw-r----- 1 oracle oinstall 2147491840 Apr 30 22:37 /oracle/ora10gR2/oradata/ora10g/tbs_sec_d_01.dbf

sec@ora10g> ! echo > /oracle/ora10gR2/oradata/ora10g/tbs_sec_d_01.dbf

sec@ora10g> ! ls -l /oracle/ora10gR2/oradata/ora10g/tbs_sec_d_01.dbf
-rw-r----- 1 oracle oinstall 1 Apr 30 22:44 /oracle/ora10gR2/oradata/ora10g/tbs_sec_d_01.dbf

OK，此时数据文件已为空文件！

6.查看故障现象
1）注意，此时还是可以查询到T表中的数据，因为此时T表数据还残留在内存中。
sec@ora10g> select * from t;

X
--------
secooler

2）尝试完成几次日志切换。
sys@ora10g> alter system switch logfile;

System altered.

sys @ora10g> alter system switch logfile;

System altered.

sys @ora10g> alter system switch logfile;

System altered.

3）再次查询T表数据的报错信息
sec@ora10g> select * from t;
select * from t
*
ERROR at line 1:
ORA-00376: file 5 cannot be read at this time
ORA-01110: data file 5: '/oracle/ora10gR2/oradata/ora10g/tbs_sec_d_01.dbf'

此时提示数据文件已经不可用！

4）尝试将表空间TBS_SEC_D的online操作
sys @ora10g> alter tablespace tbs_sec_d online;
alter tablespace tbs_sec_d online
*
ERROR at line 1:
ORA-01122: database file 5 failed verification check
ORA-01110: data file 5: '/oracle/ora10gR2/oradata/ora10g/tbs_sec_d_01.dbf'
ORA-01251: Unknown File Header Version read for file number 5

这里给出了进一步的报错信息。

7.使用备份对表空间进行恢复
1）是问题数据文件脱机
sys @ora10g> alter database datafile '/oracle/ora10gR2/oradata/ora10g/tbs_sec_d_01.dbf' offline;

Database altered.

2）还原备份的数据文件
sys@ora10g> ! cp /oracle/ora10gR2/oradata/ora10g/tbs_sec_d_01.dbf /home/oracle/tbs_sec_d_01.dbf

sec@ora10g> ! rm -f /oracle/ora10gR2/oradata/ora10g/tbs_sec_d_01.dbf

sec@ora10g> ! cp /home/oracle/tbs_sec_d_01.dbf /oracle/ora10gR2/oradata/ora10g/tbs_sec_d_01.dbf

3）尝试对故障表空间online
sys @ora10g> alter tablespace tbs_sec_d online;
alter tablespace tbs_sec_d online
*
ERROR at line 1:
ORA-01113: file 5 needs media recovery
ORA-01110: data file 5: '/oracle/ora10gR2/oradata/ora10g/tbs_sec_d_01.dbf'

这里提示需要介质恢复，这个提示信息是友好的。

4）获取归档文件的信息
sys @ora10g> select * from v$recovery_log;
   THREAD#  SEQUENCE# TIME      ARCHIVE_NAME
---------- ---------- --------- -------------------------------------------------------------------------------------------
         1         53 30-APR-11 /oracle/ora10gR2/flash_recovery_area/ORA10G/archivelog/2011_04_30/o1_mf_1_53_6vr7z4q4_.arc
         1         54 30-APR-11 /oracle/ora10gR2/flash_recovery_area/ORA10G/archivelog/2011_04_30/o1_mf_1_54_6vr87f2n_.arc
         1         55 30-APR-11 /oracle/ora10gR2/flash_recovery_area/ORA10G/archivelog/2011_04_30/o1_mf_1_55_6vr87l2t_.arc
         1         56 30-APR-11 /oracle/ora10gR2/flash_recovery_area/ORA10G/archivelog/2011_04_30/o1_mf_1_56_6vr87q7k_.arc


5）对故障数据文件进行恢复
sys @ora10g> recover datafile 5;
ORA-00279: change 925678 generated at 04/30/2011 22:34:51 needed for thread 1
ORA-00289: suggestion : /oracle/ora10gR2/flash_recovery_area/ORA10G/archivelog/2011_04_30/o1_mf_1_53_%u_.arc
ORA-00280: change 925678 for thread 1 is in sequence #53


Specify log: {=suggested | filename | AUTO | CANCEL}
/oracle/ora10gR2/flash_recovery_area/ORA10G/archivelog/2011_04_30/o1_mf_1_53_6vr7z4q4_.arc
ORA-00279: change 925868 generated at 04/30/2011 22:42:44 needed for thread 1
ORA-00289: suggestion : /oracle/ora10gR2/flash_recovery_area/ORA10G/archivelog/2011_04_30/o1_mf_1_54_%u_.arc
ORA-00280: change 925868 for thread 1 is in sequence #54
ORA-00278: log file '/oracle/ora10gR2/flash_recovery_area/ORA10G/archivelog/2011_04_30/o1_mf_1_53_6vr7z4q4_.arc' no longer needed for this recovery


Specify log: {=suggested | filename | AUTO | CANCEL}
/oracle/ora10gR2/flash_recovery_area/ORA10G/archivelog/2011_04_30/o1_mf_1_54_6vr87f2n_.arc
ORA-00279: change 925961 generated at 04/30/2011 22:47:08 needed for thread 1
ORA-00289: suggestion : /oracle/ora10gR2/flash_recovery_area/ORA10G/archivelog/2011_04_30/o1_mf_1_55_%u_.arc
ORA-00280: change 925961 for thread 1 is in sequence #55
ORA-00278: log file '/oracle/ora10gR2/flash_recovery_area/ORA10G/archivelog/2011_04_30/o1_mf_1_54_6vr87f2n_.arc' no longer needed for this recovery


Specify log: {=suggested | filename | AUTO | CANCEL}
/oracle/ora10gR2/flash_recovery_area/ORA10G/archivelog/2011_04_30/o1_mf_1_55_6vr87l2t_.arc
ORA-00279: change 925964 generated at 04/30/2011 22:47:13 needed for thread 1
ORA-00289: suggestion : /oracle/ora10gR2/flash_recovery_area/ORA10G/archivelog/2011_04_30/o1_mf_1_56_%u_.arc
ORA-00280: change 925964 for thread 1 is in sequence #56
ORA-00278: log file '/oracle/ora10gR2/flash_recovery_area/ORA10G/archivelog/2011_04_30/o1_mf_1_55_6vr87l2t_.arc' no longer needed for this recovery


Specify log: {=suggested | filename | AUTO | CANCEL}
/oracle/ora10gR2/flash_recovery_area/ORA10G/archivelog/2011_04_30/o1_mf_1_56_6vr87q7k_.arc
Log applied.
Media recovery complete.

这里选择手工指定归档文件。

6）尝试将表空间online
sys @ora10g> alter tablespace tbs_sec_d online;

Tablespace altered.

恢复使命初战告捷。

7）最后的数据验证
sec@ora10g> select * from t;

X
--------
secooler

整个恢复过程成功结束。

8.小结
注意整个恢复过程的前提是存在有效的备份！有备份便有了希望!

Good luck.

secooler
11.04.30

-- The End --

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/519536/viewspace-694201/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/519536/viewspace-694201/