一個數(shù)據(jù)庫完全一致恢復的測試實例
一個oracle數(shù)據(jù)庫的恢復測試
1,關(guān)閉數(shù)據(jù)庫完全備份
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
?
$ cp *.dbf backup1
$cp control01.ctl backup1
2,創(chuàng)建表并插入數(shù)據(jù)
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
from v$database d,v$archived_log l
where d.RESETLOGS_CHANGE# = l.RESETLOGS_CHANGE#
?
?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
from v$database d,v$archived_log l
where d.RESETLOGS_CHANGE# = l.RESETLOGS_CHANGE#
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
from v$log l,v$logfile lf
where l.GROUP# = lf.GROUP#;
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
select l.RECID,l.SEQUENCE#,l.NAME
from v$database d,v$archived_log l
where d.RESETLOGS_CHANGE# = l.RESETLOGS_CHANGE#
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
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,關(guān)閉數(shù)據(jù)庫,并用備份的數(shù)據(jù)文件恢復
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: {
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: {
/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: {
/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.
?
/*小結(jié)*/
在出現(xiàn)介質(zhì)錯誤時,如果存在一致的完全備份和備份之后的所有歸檔,則可以考慮使用這種方法恢復.
恢復分為三個步驟.
1,文件覆蓋;
2,recover database using backup controlfile until cancel選擇auto,自動使用已經(jīng)備份的東東恢復.
3,查詢v$log,從status為current的日志開始(其他應(yīng)該為unused,因為其他的應(yīng)該已經(jīng)歸檔,并在上一步中成功恢復).
4,使用current的下一個要歸檔的日志文件完全恢復過程.
下載文檔
