位置：首页-资讯-数据库

Oracle手工不完全恢复（一）：使用当前控制文件

2024-04-02 19:55

短信预约 -IT技能 免费直播动态提醒

实验环境

操作系统：CentOS 7.1

数据库：Oracle 11.2.0.4

示例一：基于SCN或时间点的恢复----恢复过去某个时间误删除的表

示例二：当前日志组损坏，造成数据库崩溃

示例三：归档日志丢失或损坏

示例一：基于SCN或时间点的恢复----恢复过去某个时间误删除的表

环境：

（1）提前对数据库做一次全库冷备份；

（2）在seiang用户下有一张test1表隶属于seiang表空间；

定位错误操作发生的时间或SCN：logminer

恢复原理：

1.冷备份的数据文件*.dbf比现在要旧，那么数据文件的scn肯定就比现在的小；

2.使用日志挖掘方法，在日志中找出删表时的scn或时间戳；

3.在数据库shutdown的状态下，用冷备份的数据文件*.dbf，覆盖现在的*.dbf文件；保证数据文件完整性；

4.启动数据库到mount下,恢复到日志挖掘的SCN或时间点（利用日志对数据文件重做一次）

5.以resetlogs方式打开数据库，以前的日志就被覆盖了；

示例二：当前日志组损坏，造成数据库崩溃

环境：

（1）提前对数据库做一次全库冷备份；

（2）在seiang用户下有一张test1表隶属于seiang表空间；

示例三：归档日志丢失或损坏

环境：

（1）提前有一套完成的数据文件的冷备份；

（2）在seiang用户下有一张test1表隶属于seiang表空间；

SYS@seiang11g>select owner,table_name,tablespace_name from dba_tables where owner='SEIANG';
OWNER TABLE_NAME TABLESPACE_NAME
------------------------------ ------------------------------ ------------------------------
SEIANG TEST1 SEIANG
SEIANG TEST2 WJQ
SEIANG TEST3 WJQBEST
SYS@seiang11g>select * from seiang.test1;
ID NAME AGE
---------- ------------------------------ ----------
1 wjq 23
2 seiang 24
3 wjqdood 25
4 wjqbest 30
--查看当前日志序号为2
SYS@seiang11g>select group#,sequence#,status from v$log;
GROUP# SEQUENCE# STATUS
---------- ---------- ----------------
1 1 INACTIVE
2 2 CURRENT
3 0 UNUSED
--修改数据并提交(sequence号2)
SYS@seiang11g>update seiang.test1 set age=100 where id=1;
1 row updated.
SYS@seiang11g>commit;
Commit complete.
SYS@seiang11g>alter system switch logfile;
System altered.
--修改数据并提交(sequence号3)
SYS@seiang11g>update seiang.test1 set age=200 where id=1;
1 row updated.
SYS@seiang11g>commit;
Commit complete.
SYS@seiang11g>alter system switch logfile;
System altered.
--修改数据并提交(sequence号4)
SYS@seiang11g>update seiang.test1 set age=300 where id=1;
1 row updated.
SYS@seiang11g>commit;
Commit complete.
SYS@seiang11g>alter system switch logfile;
System altered.
--修改数据并提交(sequence号5)
SYS@seiang11g>update seiang.test1 set age=400 where id=1;
1 row updated.
SYS@seiang11g>commit;
Commit complete.
SYS@seiang11g>alter system switch logfile;
System altered.
--修改数据并提交(sequence号6)
SYS@seiang11g>update seiang.test1 set age=500 where id=1;
1 row updated.
SYS@seiang11g>commit;
Commit complete.
SYS@seiang11g>alter system switch logfile;
System altered.
--修改数据并提交(sequence号7)
SYS@seiang11g>update seiang.test1 set age=600 where id=1;
1 row updated.
SYS@seiang11g>commit;
Commit complete.
SYS@seiang11g>alter system switch logfile;
System altered.
--当前联机日志序号为8
SYS@seiang11g>select group#,sequence#,status from v$log;
GROUP# SEQUENCE# STATUS
---------- ---------- ----------------
1 7 ACTIVE
2 8 CURRENT
3 6 ACTIVE
--查看相应的归档日志
SYS@seiang11g>select name from v$archived_log;
NAME
------------------------------------------------------
/u01/app/oracle/arch/arch_1_950962051_1.log
/u01/app/oracle/arch/arch_1_950962051_2.log
/u01/app/oracle/arch/arch_1_950971495_1.log
/u01/app/oracle/arch/arch_1_950971495_2.log
/u01/app/oracle/arch/arch_1_950972396_1.log
/u01/app/oracle/arch/arch_1_950972396_2.log
/u01/app/oracle/arch/arch_1_950972396_3.log
/u01/app/oracle/arch/arch_1_950972396_4.log
/u01/app/oracle/arch/arch_1_950972396_5.log
/u01/app/oracle/arch/arch_1_950972396_6.log
/u01/app/oracle/arch/arch_1_950972396_7.log
--关闭数据库
SYS@seiang11g >shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
--模拟数据文件seiang损坏
SYS@seiang11g>host rm /u01/app/oracle/oradata/OraDB11g/seiang01.dbf
--重新打开数据库，由于数据文件的丢死，数据库无法open
SYS@seiang11g>startup
ORACLE instance started.
Total System Global Area 1252663296 bytes
Fixed Size 2252824 bytes
Variable Size 788533224 bytes
Database Buffers 452984832 bytes
Redo Buffers 8892416 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 7 - see DBWR trace file
ORA-01110: data file 7: '/u01/app/oracle/oradata/OraDB11g/seiang01.dbf'
SYS@seiang11g>select * from v$recover_file;
FILE# ONLINE ONLINE_ ERROR CHANGE# TIME
---------- ------- ------- ----------------------------------------------------------------- ---------- ---------
7 ONLINE ONLINE FILE NOT FOUND 0
--还原备份的数据文件
SYS@seiang11g>host cp /u01/app/oracle/UMAN_Backup/seiang01.dbf /u01/app/oracle/oradata/OraDB11g/
SYS@seiang11g>select * from v$recover_file;
FILE# ONLINE ONLINE_ ERROR CHANGE# TIME
---------- ------- ------- ----------------------------------------------------------------- ---------- ---------
7 ONLINE ONLINE UNKNOWN ERROR 1913765 02-AUG-17
--查看一下归档日志的详细信息
SYS@seiang11g>select sequence#, name, first_change#, next_change# from v$archived_log;
SEQUENCE# NAME FIRST_CHANGE# NEXT_CHANGE#
---------- -------------------------------------------------- ------------- ------------
1 /u01/app/oracle/arch/arch_1_950962051_1.log 1914744 1921854
2 /u01/app/oracle/arch/arch_1_950962051_2.log 1921854 1921883
1 /u01/app/oracle/arch/arch_1_950971495_1.log 1913766 1914386
2 /u01/app/oracle/arch/arch_1_950971495_2.log 1914386 1914401
1 /u01/app/oracle/arch/arch_1_950972396_1.log 1914402 1936446
2 /u01/app/oracle/arch/arch_1_950972396_2.log 1936446 1937042
3 /u01/app/oracle/arch/arch_1_950972396_3.log 1937042 1937100
4 /u01/app/oracle/arch/arch_1_950972396_4.log 1937100 1937110
5 /u01/app/oracle/arch/arch_1_950972396_5.log 1937110 1937123
6 /u01/app/oracle/arch/arch_1_950972396_6.log 1937123 1937139
7 /u01/app/oracle/arch/arch_1_950972396_7.log 1937139 1937148
--模拟归档日志5丢失或者损坏
SYS@seiang11g>host rm /u01/app/oracle/arch/arch_1_950972396_5.log
[oracle@seiang11g OraDB11g]$ ll /u01/app/oracle/arch/arch_1_950972396_5.log
ls: cannot access /u01/app/oracle/arch/arch_1_950972396_5.log: No such file or directory
--执行恢复操作，当在需要归档日志5的时候出现错误
SYS@seiang11g>recover datafile 7;
ORA-00279: change 1913766 generated at 08/02/2017 11:12:22 needed for thread 1
ORA-00289: suggestion : /u01/app/oracle/arch/arch_1_950971495_1.log
ORA-00280: change 1913766 for thread 1 is in sequence #1
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
auto
ORA-00279: change 1914386 generated at 08/02/2017 14:48:59 needed for thread 1
ORA-00289: suggestion : /u01/app/oracle/arch/arch_1_950971495_2.log
ORA-00280: change 1914386 for thread 1 is in sequence #2
ORA-00279: change 1914402 generated at 08/02/2017 14:59:56 needed for thread 1
ORA-00289: suggestion : /u01/app/oracle/arch/arch_1_950972396_1.log
ORA-00280: change 1914402 for thread 1 is in sequence #1
ORA-00279: change 1936446 generated at 08/02/2017 15:39:52 needed for thread 1
ORA-00289: suggestion : /u01/app/oracle/arch/arch_1_950972396_2.log
ORA-00280: change 1936446 for thread 1 is in sequence #2
ORA-00279: change 1937042 generated at 08/02/2017 15:49:42 needed for thread 1
ORA-00289: suggestion : /u01/app/oracle/arch/arch_1_950972396_3.log
ORA-00280: change 1937042 for thread 1 is in sequence #3
ORA-00279: change 1937100 generated at 08/02/2017 15:51:34 needed for thread 1
ORA-00289: suggestion : /u01/app/oracle/arch/arch_1_950972396_4.log
ORA-00280: change 1937100 for thread 1 is in sequence #4
ORA-00279: change 1937110 generated at 08/02/2017 15:51:54 needed for thread 1
ORA-00289: suggestion : /u01/app/oracle/arch/arch_1_950972396_5.log
ORA-00280: change 1937110 for thread 1 is in sequence #5
ORA-00308: cannot open archived log '/u01/app/oracle/arch/arch_1_950972396_5.log'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
--查看数据文件头，发现检查点不一致
SYS@seiang11g>select file#,name,checkpoint_change# from v$datafile_header;
FILE# NAME CHECKPOINT_CHANGE#
---------- -------------------------------------------------- ------------------
1 /u01/app/oracle/oradata/OraDB11g/system01.dbf 1937617
2 /u01/app/oracle/oradata/OraDB11g/sysaux01.dbf 1937617
3 /u01/app/oracle/oradata/OraDB11g/undotbs01.dbf 1937617
4 /u01/app/oracle/oradata/OraDB11g/users01.dbf 1937617
5 /u01/app/oracle/oradata/OraDB11g/example01.dbf 1937617
6 /u01/app/oracle/oradata/OraDB11g/rman01.dbf 1937617
7 /u01/app/oracle/oradata/OraDB11g/seiang01.dbf 1937110
8 /u01/app/oracle/oradata/OraDB11g/wjq01.dbf 1937617
9 /u01/app/oracle/oradata/OraDB11g/wjqbest01.dbf 1937617