Oracle用户管理的不完全恢复2：基于取消的恢复
模拟生产库场景
1.1关闭数据库
1 SQL> shutdown immediate 2 Database closed. 3 Database dismounted. 4 ORACLE instance shut down. 5 SQL>
1.2.冷备
1 -bash-3.00$ cp /u01/oradata/sunbak/* . 2 -bash-3.00$ ls -l 3 total 2214344 4 -rw-r----- 1 oracle oinstall 7389184 Jan 25 23:36 control01.ctl 5 -rw-r----- 1 oracle oinstall 7389184 Jan 25 23:37 control02.ctl 6 -rw-r----- 1 oracle oinstall 7389184 Jan 25 23:37 control03.ctl 7 -rw-r----- 1 oracle oinstall 104865792 Jan 25 23:37 example01.dbf 8 -rw-r----- 1 oracle oinstall 52429312 Jan 25 23:37 redo01.log 9 -rw-r----- 1 oracle oinstall 52429312 Jan 25 23:38 redo02.log 10 -rw-r----- 1 oracle oinstall 52429312 Jan 25 23:38 redo03.log 11 -rw-r----- 1 oracle oinstall 10493952 Jan 25 23:38 sun01_1.dbf 12 -rw-r----- 1 oracle oinstall 10493952 Jan 25 23:38 sun02_1.dbf 13 -rw-r----- 1 oracle oinstall 5251072 Jan 25 23:38 sun03_1.dbf 14 -rw-r----- 1 oracle oinstall 262152192 Jan 25 23:38 sysaux01.dbf 15 -rw-r----- 1 oracle oinstall 503324672 Jan 25 23:39 system01.dbf 16 -rw-r----- 1 oracle oinstall 20979712 Jan 25 23:39 temp01.dbf 17 -rw-r----- 1 oracle oinstall 31465472 Jan 25 23:39 undotbs01.dbf 18 -rw-r----- 1 oracle oinstall 5251072 Jan 25 23:39 users01.dbf 19 -bash-3.00$
1.3.开启数据库插入数据
1 SQL> startup 2 ORACLE instance started. 3 4 Total System Global Area 289406976 bytes 5 Fixed Size 1279820 bytes 6 Variable Size 79694004 bytes 7 Database Buffers 205520896 bytes 8 Redo Buffers 2912256 bytes 9 Database mounted. 10 Database opened. 11 SQL>
1.4 插入数据
1 SQL> select * from t order by 1; 2 3 ID NAME 4 ---------- ---------------- 5 0 oracle 6 1 oracle 7 2 oracle 8 3 oracle 9 4 oracle 10 5 oracle 11 6 oracle 12 13 7 rows selected. 14 15 SQL> insert into t values (8,'mysql'); 16 17 1 row created. 18 19 SQL> commit; 20 21 Commit complete. 22 23 SQL>
1.5“mysql”写入数据文件
1 SQL> alter system checkpoint; 2 3 System altered. 4 5 SQL> 6 -bash-3.00$ strings /u01/oradata/sunbak/sun01_1.dbf |grep mysql 7 mysql, 8 -bash-3.00$
1.6 归档（“mysql”已经被归档）
1 SQL> alter system switch logfile; 2 3 System altered. 4 5 SQL> 6 -bash-3.00$ strings 1_4_805672882.dbf|grep mysql 7 mysql 8 -bash-3.00$ 9 10 此时"mysql"存在两种类型文件中
1.7 再插入数据（"mysql01"）
1 SQL> insert into t values(9,'mysql01'); 2 3 1 row created. 4 5 SQL> commit; 6 7 Commit complete. 8 9 SQL>
1.8 将数据写入数据文件
1 SQL> alter system checkpoint; 2 3 System altered. 4 5 SQL> 6 -bash-3.00$ strings sun01_1.dbf|grep mysql01 7 myslq01, 8 -bash-3.00$ 9 10 #此时mysql01并未写入归档日志文件中
1.9 备份控制文件
1 SQL> alter database backup controlfile to trace as '/u01/admin/sun/udump/ctl20130129.sql'; 2 3 Database altered. 4 5 SQL>
2.模拟破坏场景
2.1 删除数据文件，控制文件，日志文件，归档日志文件保留（"myslq"还存在，"mysql01"已不存在）
1 -bash-3.00$ pwd 2 /u01/oradata/sunbak 3 -bash-3.00$ ls -l 4 total 2215712 5 -rw-r----- 1 oracle oinstall 7389184 Jan 26 00:14 control01.ctl 6 -rw-r----- 1 oracle oinstall 7389184 Jan 26 00:14 control02.ctl 7 -rw-r----- 1 oracle oinstall 7389184 Jan 26 00:14 control03.ctl 8 -rw-r----- 1 oracle oinstall 104865792 Jan 26 00:02 example01.dbf 9 -rw-r----- 1 oracle oinstall 52429312 Jan 26 00:14 redo01.log 10 -rw-r----- 1 oracle oinstall 52429312 Jan 25 23:57 redo02.log 11 -rw-r----- 1 oracle oinstall 52429312 Jan 25 23:53 redo03.log 12 -rw-r----- 1 oracle oinstall 10493952 Jan 26 00:02 sun01_1.dbf 13 -rw-r----- 1 oracle oinstall 10493952 Jan 26 00:02 sun02_1.dbf 14 -rw-r----- 1 oracle oinstall 5251072 Jan 26 00:02 sun03_1.dbf 15 -rw-r----- 1 oracle oinstall 262152192 Jan 26 00:14 sysaux01.dbf 16 -rw-r----- 1 oracle oinstall 503324672 Jan 26 00:14 system01.dbf 17 -rw-r----- 1 oracle oinstall 20979712 Jan 25 22:02 temp01.dbf 18 -rw-r----- 1 oracle oinstall 31465472 Jan 26 00:09 undotbs01.dbf 19 -rw-r----- 1 oracle oinstall 5251072 Jan 26 00:02 users01.dbf 20 -bash-3.00$ rm -f * 21 -bash-3.00$
2.2 关闭数据库
1 SQL> shutdown abort 2 ORACLE instance shut down. 3 SQL>
2.3 将备份数据库文件拷贝过来
1 -bash-3.00$ pwd 2 /u01/oradata/sunbak 3 -bash-3.00$ 4 -bash-3.00$ cp /tmp/cold/* . 5 -bash-3.00$
2.4将数据库开到mount
1 SQL> startup mount 2 ORACLE instance started. 3 4 Total System Global Area 289406976 bytes 5 Fixed Size 1279820 bytes 6 Variable Size 79694004 bytes 7 Database Buffers 205520896 bytes 8 Redo Buffers 2912256 bytes 9 Database mounted. 10 SQL>
2.5 恢复
1 SQL> recover database using backup controlfile until cancel; 2 ORA-00279: change 697462 generated at 01/25/2013 23:34:43 needed for thread 1 3 ORA-00289: suggestion : /u01/admin/sun/arch/1_3_805672882.dbf 4 ORA-00280: change 697462 for thread 1 is in sequence #3 5 6 #注 查看 7 #-bash-3.00$ ls -l /u01/admin/sun/arch/1_3_805672882.dbf 8 #-rw-r----- 1 oracle oinstall 299520 Jan 25 23:53 /u01/admin/sun/arch/1_3_805672882.dbf 9 #-bash-3.00$ 10 #Specify log: {<RET>=suggested | filename | AUTO | CANCEL} 11 12 ORA-00279: change 698002 generated at 01/25/2013 23:53:32 needed for thread 1 13 ORA-00289: suggestion : /u01/admin/sun/arch/1_4_805672882.dbf 14 ORA-00280: change 698002 for thread 1 is in sequence #4 15 ORA-00278: log file '/u01/admin/sun/arch/1_3_805672882.dbf' no longer needed 16 for this recovery 17 18 #此时 /u01/admin/sun/arch/1_4_805672882.dbf 文件不存在，故cancel 19 Specify log: {<RET>=suggested | filename | AUTO | CANCEL} 20 cancel 21 Media recovery cancelled. 22 SQL>
3.开启数据库
1 SQL> alter database open resetlogs; 2 3 Database altered. 4 5 SQL>
4.查看结果
1 SQL> select * from user1.t order by 1; 2 3 ID NAME 4 ---------- ---------------- 5 0 oracle 6 1 oracle 7 2 oracle 8 3 oracle 9 4 oracle 10 5 oracle 11 6 oracle 12 7 oracle 13 8 mysql 14 15 8 rows selected. 16 17 SQL>
