模拟生产库场景
1.1关闭数据库
1 SQL> shutdown immediate2 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.log10 -rw-r----- 1 oracle oinstall 52429312 Jan 25 23:38 redo03.log11 -rw-r----- 1 oracle oinstall 10493952 Jan 25 23:38 sun01_1.dbf12 -rw-r----- 1 oracle oinstall 10493952 Jan 25 23:38 sun02_1.dbf13 -rw-r----- 1 oracle oinstall 5251072 Jan 25 23:38 sun03_1.dbf14 -rw-r----- 1 oracle oinstall 262152192 Jan 25 23:38 sysaux01.dbf15 -rw-r----- 1 oracle oinstall 503324672 Jan 25 23:39 system01.dbf16 -rw-r----- 1 oracle oinstall 20979712 Jan 25 23:39 temp01.dbf17 -rw-r----- 1 oracle oinstall 31465472 Jan 25 23:39 undotbs01.dbf18 -rw-r----- 1 oracle oinstall 5251072 Jan 25 23:39 users01.dbf19 -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 oracle10 5 oracle11 6 oracle12 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 mysql7 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.log10 -rw-r----- 1 oracle oinstall 52429312 Jan 25 23:57 redo02.log11 -rw-r----- 1 oracle oinstall 52429312 Jan 25 23:53 redo03.log12 -rw-r----- 1 oracle oinstall 10493952 Jan 26 00:02 sun01_1.dbf13 -rw-r----- 1 oracle oinstall 10493952 Jan 26 00:02 sun02_1.dbf14 -rw-r----- 1 oracle oinstall 5251072 Jan 26 00:02 sun03_1.dbf15 -rw-r----- 1 oracle oinstall 262152192 Jan 26 00:14 sysaux01.dbf16 -rw-r----- 1 oracle oinstall 503324672 Jan 26 00:14 system01.dbf17 -rw-r----- 1 oracle oinstall 20979712 Jan 25 22:02 temp01.dbf18 -rw-r----- 1 oracle oinstall 31465472 Jan 26 00:09 undotbs01.dbf19 -rw-r----- 1 oracle oinstall 5251072 Jan 26 00:02 users01.dbf20 -bash-3.00$ rm -f *21 -bash-3.00$
2.2 关闭数据库
1 SQL> shutdown abort2 ORACLE instance shut down.3 SQL>
2.3 将备份数据库文件拷贝过来
1 -bash-3.00$ pwd2 /u01/oradata/sunbak3 -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: { =suggested | filename | AUTO | CANCEL}11 12 ORA-00279: change 698002 generated at 01/25/2013 23:53:32 needed for thread 113 ORA-00289: suggestion : /u01/admin/sun/arch/1_4_805672882.dbf14 ORA-00280: change 698002 for thread 1 is in sequence #415 ORA-00278: log file '/u01/admin/sun/arch/1_3_805672882.dbf' no longer needed16 for this recovery17 18 #此时 /u01/admin/sun/arch/1_4_805672882.dbf 文件不存在，故cancel19 Specify log: { =suggested | filename | AUTO | CANCEL}20 cancel21 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 oracle10 5 oracle11 6 oracle12 7 oracle13 8 mysql14 15 8 rows selected.16 17 SQL>