|
一个oracle数据库的恢复测试 1,关闭数据库完全备份 SQL> shutdown immediate Database closed. Database dismounted. ORACLE instance shut down.
$ cp *.dbf backup1 $cp control01.ctl backup1 2,创建表并插入数据 SQL> startup ORACLE instance started.
Total System Global Area 1511484856 bytes Fixed Size 736696 bytes Variable Size 553648128 bytes Database Buffers 956301312 bytes Redo Buffers 798720 bytes Database mounted. Database opened.
SQL> create table mytable (f1 varchar2(2));
Table created.
SQL> alter system switch logfile;
System altered.
查看当前sequence和归档的sequence
SQL>select l.RECID,l.SEQUENCE#,l.NAME
SEQUENCE# NAME ------------------------------------------------------------------------------------------ 1 /oradata/backup/archivelogbackup/1_1.dbf
SQL> insert into mytable 2 select '01' from dual;
1 row created.
SQL> commit;
Commit complete.
SQL> alter system switch logfile;
System altered.
SQL> insert into mytable 2 select '02' from dual;
1 row created.
SQL> commit;
Commit complete.
SQL> alter system switch logfile;
System altered.
select group#,sequence#,status,first_change# from v$log; 1 1 2 INACTIVE 6612478016 2 2 3 ACTIVE 6612478685 3 3 4 CURRENT 6612480148
select l.RECID,l.SEQUENCE#,l.NAME 1 5 1 /oradata/backup/archivelogbackup/1_1.dbf 2 6 2 /oradata/backup/archivelogbackup/1_2.dbf 3 7 3 /oradata/backup/archivelogbackup/1_3.dbf
SQL> insert into mytable 2 select '03' from dual;
1 row created.
SQL> commit;
Commit complete.
SQL> alter system switch logfile;
System altered.
SQL> insert into mytable 2 select '04' from dual;
1 row created.
SQL> commit;
Commit complete.
SQL> alter system switch logfile;
System altered.
SQL> SQL> SQL> insert into mytable 2 select '05' from dual;
1 row created.
SQL> commit;
Commit complete.
SQL> alter system switch logfile;
System altered.
SQL> insert into mytable 2 select '06' from dual;
1 row created.
SQL> commit;
Commit complete.
SQL> alter system switch logfile;
System altered.
select l.group#,l.sequence#,l.status,l.first_change# ,lf.MEMBER
1 1 8 CURRENT 6612481053 /oradata/REDO1_01.LOG 2 1 8 CURRENT 6612481053 /oradata/REDO1_02.LOG 3 2 6 INACTIVE 6612480890 /oradata/REDO2_01.LOG 4 2 6 INACTIVE 6612480890 /oradata/REDO2_02.LOG 5 3 7 ACTIVE 6612480971 /oradata/REDO3_01.LOG 6 3 7 ACTIVE 6612480971 /oradata/REDO3_02.LOG
2 6 2 /oradata/backup/archivelogbackup/1_2.dbf 3 7 3 /oradata/backup/archivelogbackup/1_3.dbf 4 8 4 /oradata/backup/archivelogbackup/1_4.dbf 5 9 5 /oradata/backup/archivelogbackup/1_5.dbf 6 10 6 /oradata/backup/archivelogbackup/1_6.dbf 7 11 7 /oradata/backup/archivelogbackup/1_7.dbf
3,关闭数据库,并用备份的数据文件恢复 SQL> shutdown immediate Database closed. Database dismounted. ORACLE instance shut down.
$ rm *.dbf $ rm *.ctl $ cd backup1 $ cp *.dbf /oradata $ cp *.ctl /oradata $ cd .. $ pwd /oradata $ ls CTL01.dbf REDO1_0101.LOG REDO3_02.LOG backup1 directory.out system01.dbf FBI.dbf REDO1_0102.LOG RPTD01.dbf backup_datafile drsys01.dbf temp01.dbf HAPPYTREE.dbf REDO1_02.LOG RPTI01.dbf bea example01.dbf tmp HTEC.dbf REDO2_01.LOG TEMP1_01.dbf bea80 file tools01.dbf OD01.dbf REDO2_02.LOG ant control01.ctl indx01.dbf undotbs03.dbf REDO1_01.LOG REDO3_01.LOG backup cwmlite01.dbf lost+found users01.dbf $ cp control01.ctl control02.ctl $ cp control01.ctl control03.ctl
SQL> startup mount ORACLE instance started.
Total System Global Area 1511484856 bytes Fixed Size 736696 bytes Variable Size 553648128 bytes Database Buffers 956301312 bytes Redo Buffers 798720 bytes Database mounted. SQL> SQL> alter database open; alter database open * ERROR at line 1: ORA-00338: log 1 of thread 1 is more recent than controlfile ORA-00312: online log 1 thread 1: '/oradata/REDO1_01.LOG' ORA-00312: online log 1 thread 1: '/oradata/REDO1_02.LOG'
SQL> recover database using backup controlfile until cancel; ORA-00279: change 6612477360 generated at 02/26/2009 15:51:32 needed for thread 1 ORA-00289: suggestion : /oradata/backup/archivelogbackup/1_1.dbf ORA-00280: change 6612477360 for thread 1 is in sequence #1
Specify log: {<RET>=suggested | filename | AUTO | CANCEL} auto ORA-00279: change 6612478016 generated at 02/26/2009 15:57:20 needed for thread 1 ORA-00289: suggestion : /oradata/backup/archivelogbackup/1_2.dbf ORA-00280: change 6612478016 for thread 1 is in sequence #2 ORA-00278: log file '/oradata/backup/archivelogbackup/1_1.dbf' no longer needed for this recovery
ORA-00279: change 6612478685 generated at 02/26/2009 16:01:38 needed for thread 1 ORA-00289: suggestion : /oradata/backup/archivelogbackup/1_3.dbf ORA-00280: change 6612478685 for thread 1 is in sequence #3 ORA-00278: log file '/oradata/backup/archivelogbackup/1_2.dbf' no longer needed for this recovery
ORA-00279: change 6612480148 generated at 02/26/2009 16:10:36 needed for thread 1 ORA-00289: suggestion : /oradata/backup/archivelogbackup/1_4.dbf ORA-00280: change 6612480148 for thread 1 is in sequence #4 ORA-00278: log file '/oradata/backup/archivelogbackup/1_3.dbf' no longer needed for this recovery
ORA-00279: change 6612480791 generated at 02/26/2009 16:14:28 needed for thread 1 ORA-00289: suggestion : /oradata/backup/archivelogbackup/1_5.dbf ORA-00280: change 6612480791 for thread 1 is in sequence #5 ORA-00278: log file '/oradata/backup/archivelogbackup/1_4.dbf' no longer needed for this recovery
ORA-00279: change 6612480890 generated at 02/26/2009 16:14:55 needed for thread 1 ORA-00289: suggestion : /oradata/backup/archivelogbackup/1_6.dbf ORA-00280: change 6612480890 for thread 1 is in sequence #6 ORA-00278: log file '/oradata/backup/archivelogbackup/1_5.dbf' no longer needed for this recovery
ORA-00279: change 6612480971 generated at 02/26/2009 16:15:20 needed for thread 1 ORA-00289: suggestion : /oradata/backup/archivelogbackup/1_7.dbf ORA-00280: change 6612480971 for thread 1 is in sequence #7 ORA-00278: log file '/oradata/backup/archivelogbackup/1_6.dbf' no longer needed for this recovery
ORA-00279: change 6612481053 generated at 02/26/2009 16:15:45 needed for thread 1 ORA-00289: suggestion : /oradata/backup/archivelogbackup/1_8.dbf ORA-00280: change 6612481053 for thread 1 is in sequence #8 ORA-00278: log file '/oradata/backup/archivelogbackup/1_7.dbf' no longer needed for this recovery
ORA-00328: archived log ends at change 6612393963, need later change 6612481053 ORA-00334: archived log: '/oradata/backup/archivelogbackup/1_8.dbf'
提示说当前恢复到scn: 6612393963,需要更多的恢复直到scn 6612481053 SQL> select l.group#,l.sequence#,l.status,l.first_change# ,lf.MEMBER from v$log l,v$logfile lf where l.GROUP# = lf.GROUP#; 2 3
GROUP# SEQUENCE# STATUS FIRST_CHANGE# ---------- ---------- -------------------------------- ------------- MEMBER -------------------------------------------------------------------------------- 1 0 UNUSED 0 /oradata/REDO1_01.LOG
2 0 UNUSED 0 /oradata/REDO2_01.LOG
3 1 CURRENT 6612476308 /oradata/REDO3_01.LOG
3 rows selected.
我们看到当前文档为group 3,使用当前文件恢复. SQL> recover database using backup controlfile until cancel; ORA-00279: change 6612481053 generated at 02/26/2009 16:15:45 needed for thread 1 ORA-00289: suggestion : /oradata/backup/archivelogbackup/1_8.dbf ORA-00280: change 6612481053 for thread 1 is in sequence #8
Specify log: {<RET>=suggested | filename | AUTO | CANCEL} /oradata/REDO3_01.LOG ORA-00310: archived log contains sequence 7; sequence 8 required ORA-00334: archived log: '/oradata/REDO3_01.LOG'
提示说还差一步了,使用归档日志1恢复.
SQL> recover database using backup controlfile until cancel; ORA-00279: change 6612481053 generated at 02/26/2009 16:15:45 needed for thread 1 ORA-00289: suggestion : /oradata/backup/archivelogbackup/1_8.dbf ORA-00280: change 6612481053 for thread 1 is in sequence #8
Specify log: {<RET>=suggested | filename | AUTO | CANCEL} /oradata/REDO1_01.LOG Log applied. Media recovery complete.
SQL> alter database open resetlogs;
Database altered.
SQL> select * from mytable;
F1 ---- 01 02 03 04 05 06
6 rows selected. SQL> alter database open resetlogs;
6 rows selected.
/*小结*/ 在出现介质错误时,如果存在一致的完全备份和备份之后的所有归档,则可以考虑使用这种方法恢复. 恢复分为三个步骤. 1,文件覆盖; 2,recover database using backup controlfile until cancel选择auto,自动使用已经备份的东东恢复. 3,查询v$log,从status为current的日志开始(其他应该为unused,因为其他的应该已经归档,并在上一步中成功恢复). 4,使用current的下一个要归档的日志文件完全恢复过程. (责任编辑：IT) |