oracle用户管理的完全恢复4：在ARCHIVELOG 模式（恢复打开的数据库--数据库最初是关闭的）
场景描述：恢复打开的数据库（数据库最初是关闭的）
此恢复方法一般在以下情况下使用：
a.介质或硬件故障导致系统关闭。
b.数据库全天候（每周7 天、每天24 小时）运行。必须最大限度地减少数据库的停机时间。
c.损坏的文件不属于系统表空间或还原段表空间
1.模拟生产环境
1.1 插入数据
1 SQL> insert into t values(5,'oracle'); 2 3 1 row created. 4 5 SQL> commit; 6 7 Commit complete. 8 9 SQL>
1.2 将数据写入数据文件sun01_1.dbf
1 SQL> alter system checkpoint local;--是否实现将日志文件内容写入数据文件有待检验 2 3 System altered. 4 5 SQL>
1.3 关闭数据库
1 SQL> shutdown immediate 2 Database closed. 3 Database dismounted. 4 ORACLE instance shut down. 5 SQL>
1.4 损坏sun01_1.dbf文件
1 bash-3.00$ cd /u01/oradata/sunbak 2 bash-3.00$ ls -l 3 total 2162512 4 -rw-r----- 1 oracle oinstall 7061504 Jan 23 01:08 control01.ctl 5 -rw-r----- 1 oracle oinstall 7061504 Jan 23 01:08 control02.ctl 6 -rw-r----- 1 oracle oinstall 7061504 Jan 23 01:08 control03.ctl 7 -rw-r----- 1 oracle oinstall 104865792 Jan 23 01:08 example01.dbf 8 -rw-r----- 1 oracle oinstall 52429312 Jan 23 01:06 redo01.log 9 -rw-r----- 1 oracle oinstall 52429312 Jan 23 01:06 redo02.log 10 -rw-r----- 1 oracle oinstall 52429312 Jan 23 01:08 redo03.log 11 -rw-r----- 1 oracle oinstall 10493952 Jan 23 01:08 sun01_1.dbf 12 -rw-r----- 1 oracle oinstall 251666432 Jan 23 01:08 sysaux01.dbf 13 -rw-r----- 1 oracle oinstall 503324672 Jan 23 01:08 system01.dbf 14 -rw-r----- 1 oracle oinstall 20979712 Jan 22 23:24 temp01.dbf 15 -rw-r----- 1 oracle oinstall 31465472 Jan 23 01:08 undotbs01.dbf 16 -rw-r----- 1 oracle oinstall 5251072 Jan 23 01:08 users01.dbf 17 bash-3.00$ rm -f sun01_1.dbf 18 bash-3.00$
1.5 将数据库打开（实际只能打开到mount）
1 SQL> startup 2 ORACLE instance started. 3 4 Total System Global Area 289406976 bytes 5 Fixed Size 1279820 bytes 6 Variable Size 92276916 bytes 7 Database Buffers 192937984 bytes 8 Redo Buffers 2912256 bytes 9 Database mounted. 10 ORA-01157: cannot identify/lock data file 6 - see DBWR trace file 11 ORA-01110: data file 6: '/u01/oradata/sunbak/sun01_1.dbf' 12 13 14 SQL> 15 #查看休要恢复文件 16 SQL> select * from v$recover_file; 17 18 FILE# ONLINE ONLINE_ ERROR CHANGE# TIME 19 ---------- ------- ------- -------------------- ---------- --------- 20 6 ONLINE ONLINE FILE NOT FOUND 0 21 22 SQL>
1.6 将需恢复文件下线
1 SQL> alter database datafile 6 offline; 2 3 Database altered. 4 5 SQL>
1.7 打开数据库
1 SQL> alter database open; 2 3 Database altered. 4 5 SQL>
2.开始恢复（利用冷备文件 热备文件皆可）
2.1把上次备份的sun01_1.dbf文件拷过来
1 bash-3.00$ cd /u01/oradata/sunbak 2 bash-3.00$ ls -l /u01/backup/hot 3 total 20512 4 -rw-r----- 1 oracle oinstall 10493952 Jan 22 22:00 sun01_1.dbf 5 bash-3.00$ cp /u01/backup/hot/sun01_1.dbf . 6 bash-3.00$ 7 8 #此时可把文件恢复到新的目录 9 bash-3.00$ ls -l /u01/backup/hot 10 total 20512 11 -rw-r----- 1 oracle oinstall 10493952 Jan 22 22:00 sun01_1.dbf 12 bash-3.00$ cp /u01/backup/hot/sun01_1.dbf /u01/oradata/sun 13 bash-3.00$ 14 #通过控制文件中文件的位置 15 SQL> alter database rename file '/u01/oradata/sunbak/sun01_1.dbf' to '/u01/oradata/sun/sun01_1.dbf'
2.2 恢复文件
1 SQL> recover datafile 6; 2 ORA-00279: change 574756 generated at 01/22/2013 21:58:34 needed for thread 1 3 ORA-00289: suggestion : /u01/admin/sun/arch/1_2_805319563.dbf 4 ORA-00280: change 574756 for thread 1 is in sequence #2 5 6 7 Specify log: {<RET>=suggested | filename | AUTO | CANCEL} 8 9 ORA-00279: change 581083 generated at 01/22/2013 22:04:42 needed for thread 1 10 ORA-00289: suggestion : /u01/admin/sun/arch/1_3_805319563.dbf 11 ORA-00280: change 581083 for thread 1 is in sequence #3 12 ORA-00278: log file '/u01/admin/sun/arch/1_2_805319563.dbf' no longer needed 13 for this recovery 14 15 Specify log: {<RET>=suggested | filename | AUTO | CANCEL} 16 17 Log applied. 18 Media recovery complete. 19 SQL>
2.3将离线文件拉起
1 SQL> alter database datafile 6 online; 2 3 Database altered. 4 5 SQL>
3.查看恢复结果
SQL> select * from user1.t; ID NAME ---------- ------------------------------ 1 oracle 2 oracle 0 oracle 3 oracle 4 oracle 5 oracle 6 rows selected. SQL>
