oracle用户管理的完全恢复4：在ARCHIVELOG 模式(恢复打开的数据库)
场景描述：恢复打开的数据库
此恢复方法一般在以下情况下使用：
a.未导致数据库关闭的文件损坏、文件意外丢失或介质故障。
b.数据库全天候（每周7 天、每天24 小时）运行。必须最大限度地减少数据库的停机时间。
c.受到影响的文件不属于系统表空间或还原/回退段表空间。
1.查看环境
#此时数据库实在打开的情况下
1 SQL> archive log list; 2 Database log mode Archive Mode 3 Automatic archival Enabled 4 Archive destination /u01/admin/sun/arch 5 Oldest online log sequence 5 6 Next log sequence to archive 7 7 Current log sequence 7 8 SQL>
2.模拟生产环境
2.1 插入数据
1 SQL> conn user1/user1 2 Connected. 3 SQL> insert into t values(3,'oracle'); 4 5 1 row created. 6 7 SQL> commit; 8 9 Commit complete. 10 11 SQL> select * from t; 12 13 ID NAME 14 ---------- ---------------- 15 1 oracle 16 2 oracle 17 0 oracle 18 3 oracle 19 20 SQL> 21 22 #手动切换日志 产生归档日志 23 SQL> alter system switch logfile; 24 25 System altered. 26 27 SQL> / 28 29 System altered. 30 31 SQL> / 32 33 System altered. 34 35 SQL>
#sun01_1.dbf文件在上一节已经进行热备
2.2 损坏表空间sun01的数据文件sun01_1.dbf
1 bash-3.00$ ls -l 2 total 2162512 3 -rw-r----- 1 oracle oinstall 7061504 Jan 23 00:01 control01.ctl 4 -rw-r----- 1 oracle oinstall 7061504 Jan 23 00:01 control02.ctl 5 -rw-r----- 1 oracle oinstall 7061504 Jan 23 00:01 control03.ctl 6 -rw-r----- 1 oracle oinstall 104865792 Jan 22 23:54 example01.dbf 7 -rw-r----- 1 oracle oinstall 52429312 Jan 22 23:54 redo01.log 8 -rw-r----- 1 oracle oinstall 52429312 Jan 23 00:00 redo02.log 9 -rw-r----- 1 oracle oinstall 52429312 Jan 22 23:54 redo03.log 10 -rw-r----- 1 oracle oinstall 10493952 Jan 22 23:54 sun01_1.dbf 11 -rw-r----- 1 oracle oinstall 251666432 Jan 23 00:00 sysaux01.dbf 12 -rw-r----- 1 oracle oinstall 503324672 Jan 23 00:00 system01.dbf 13 -rw-r----- 1 oracle oinstall 20979712 Jan 22 23:24 temp01.dbf 14 -rw-r----- 1 oracle oinstall 31465472 Jan 23 00:00 undotbs01.dbf 15 -rw-r----- 1 oracle oinstall 5251072 Jan 22 23:54 users01.dbf 16 bash-3.00$ rm -f sun01_1.dbf 17 bash-3.00$
3.恢复场景：数据库在open的情况下做完全恢复情况
3.1 查看需要恢复信息
1 #此时oracle并未发现有数据文件被损坏 2 SQL> select * from v$recover_file; 3 4 no rows selected 5 6 SQL>
3.2 继续向t表插入数据，仍然可以插入（此时数据在redo中）
1 SQL> insert into t values ( 4,'oracle'); 2 3 1 row created. 4 5 SQL> commit; 6 7 Commit complete. 8 9 SQL>
3.3 手动执行CKPT,使redo中的数据插入数据文件
1 SQL> alter system checkpoint local;--检查点分为三类：local,global,文件检查点 2 3 System altered. 4 5 SQL> 6 7 #查看alter日志发现报错信息 8 Wed Jan 23 00:16:33 2013 9 Errors in file /u01/admin/sun/udump/sun_ora_1227.trc: 10 ORA-01110: data file 6: '/u01/oradata/sunbak/sun01_1.dbf' 11 ORA-01116: error in opening database file 6 12 ORA-01110: data file 6: '/u01/oradata/sunbak/sun01_1.dbf' 13 ORA-27041: unable to open file 14 Intel SVR4 UNIX Error: 2: No such file or directory 15 Additional information: 3 16 Wed Jan 23 00:16:33 2013 17 Errors in file /u01/admin/sun/udump/sun_ora_1227.trc: 18 ORA-01171: datafile 6 going offline due to error advancing checkpoint 19 ORA-01116: error in opening database file 6 20 ORA-01110: data file 6: '/u01/oradata/sunbak/sun01_1.dbf' 21 ORA-27041: unable to open file 22 Intel SVR4 UNIX Error: 2: No such file or directory 23 Additional information: 3
3.4查看需要恢复信息
1 SQL> select file#,ONLINE_STATUS,ERROR from v$recover_file; 2 3 FILE# ONLINE_ ERROR 4 ---------- ------- -------------------- 5 6 OFFLINE FILE NOT FOUND 6 7 SQL>
3.5 把被损坏文件离线
1 SQL> alter database datafile 6 offline; 2 3 Database altered. 4 5 SQL>
3.6 把上次备份的sun01_1.dbf文件拷过来
1 bash-3.00$ ls -l /u01/backup/hot 2 total 20512 3 -rw-r----- 1 oracle oinstall 10493952 Jan 22 22:00 sun01_1.dbf 4 bash-3.00$ cp /u01/backup/hot/sun01_1.dbf . 5 bash-3.00$
3.7 恢复
SQL> recover datafile 6; ORA-00279: change 574756 generated at 01/22/2013 21:58:34 needed for thread 1 ORA-00289: suggestion : /u01/admin/sun/arch/1_2_805319563.dbf ORA-00280: change 574756 for thread 1 is in sequence #2 Specify log: {<RET>=suggested | filename | AUTO | CANCEL} ORA-00279: change 604184 generated at 01/22/2013 23:54:27 needed for thread 1 ORA-00289: suggestion : /u01/admin/sun/arch/1_8_805319563.dbf ORA-00280: change 604184 for thread 1 is in sequence #8 ORA-00278: log file '/u01/admin/sun/arch/1_7_805319563.dbf' no longer needed for this recovery Specify log: {<RET>=suggested | filename | AUTO | CANCEL} Log applied. Media recovery complete. SQL> #恢复完成 SQL> select * from v$recover_file; no rows selected SQL>
3.8 将offline文件online
1 SQL> desc v$datafile; 2 Name Null? Type 3 ----------------------------------------- -------- ---------------------------- 4 FILE# NUMBER 5 CREATION_CHANGE# NUMBER 6 CREATION_TIME DATE 7 TS# NUMBER 8 RFILE# NUMBER 9 STATUS VARCHAR2(7) 10 ENABLED VARCHAR2(10) 11 CHECKPOINT_CHANGE# NUMBER 12 CHECKPOINT_TIME DATE 13 UNRECOVERABLE_CHANGE# NUMBER 14 UNRECOVERABLE_TIME DATE 15 LAST_CHANGE# NUMBER 16 LAST_TIME DATE 17 OFFLINE_CHANGE# NUMBER 18 ONLINE_CHANGE# NUMBER 19 ONLINE_TIME DATE 20 BYTES NUMBER 21 BLOCKS NUMBER 22 CREATE_BYTES NUMBER 23 BLOCK_SIZE NUMBER 24 NAME VARCHAR2(513) 25 PLUGGED_IN NUMBER 26 BLOCK1_OFFSET NUMBER 27 AUX_NAME VARCHAR2(513) 28 FIRST_NONLOGGED_SCN NUMBER 29 FIRST_NONLOGGED_TIME DATE 30 31 SQL> col name format a30 32 SQL> select file#,status,name from v$datafile; 33 34 FILE# STATUS NAME 35 ---------- ------- ------------------------------ 36 1 SYSTEM /u01/oradata/sunbak/system01.dbf 37 38 2 ONLINE /u01/oradata/sunbak/undotbs01.dbf 39 40 3 ONLINE /u01/oradata/sunbak/sysaux01.dbf 41 42 4 ONLINE /u01/oradata/sunbak/users01.dbf 43 44 FILE# STATUS NAME 45 ---------- ------- ------------------------------ 46 5 ONLINE /u01/oradata/sunbak/example01.dbf 47 48 6 OFFLINE /u01/oradata/sunbak/sun01_1.dbf 49 50 SQL> 51 SQL> alter database datafile 6 online; 52 53 Database altered. 54 55 SQL>
4.查看恢复数据
1 SQL> select * from t; 2 3 ID NAME 4 ---------- ---------------- 5 1 oracle 6 2 oracle 7 0 oracle 8 3 oracle 9 4 oracle 10 11 SQL>
