一個(gè)數(shù)據(jù)庫完全一致恢復(fù)的測試實(shí)例

時(shí)間：2018-11-15 15:34:01

關(guān)鍵字：數(shù)據(jù)庫測試

手機(jī)看文章

掃描二維碼
隨時(shí)隨地手機(jī)看文章

[導(dǎo)讀]一個(gè)oracle數(shù)據(jù)庫的恢復(fù)測試 1,關(guān)閉數(shù)據(jù)庫完全備份 SQL> shutdown immediate Database closed. Database dismounted. ORACLE in

一個(gè)oracle數(shù)據(jù)庫的恢復(fù)測試

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.

查看當(dāng)前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> 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ù)文件恢復(fù)

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

ORA-00289: suggestion : /oradata/backup/archivelogbackup/1_1.dbf

ORA-00280: change 6612477360 for thread 1 is in sequence #1

Specify log: {=suggested | filename | AUTO | CANCEL}

auto

ORA-00279: change 6612478016 generated at 02/26/2009 15:57:20 needed for thread

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

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

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

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

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

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

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'

提示說當(dāng)前恢復(fù)到scn: 6612393963,需要更多的恢復(fù)直到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.

我們看到當(dāng)前文檔為group 3,使用當(dāng)前文件恢復(fù).

SQL> recover database using backup controlfile until cancel;

ORA-00279: change 6612481053 generated at 02/26/2009 16:15:45 needed for thread

ORA-00289: suggestion : /oradata/backup/archivelogbackup/1_8.dbf

ORA-00280: change 6612481053 for thread 1 is in sequence #8

Specify log: {=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恢復(fù).

SQL> recover database using backup controlfile until cancel;

ORA-00279: change 6612481053 generated at 02/26/2009 16:15:45 needed for thread

ORA-00289: suggestion : /oradata/backup/archivelogbackup/1_8.dbf

ORA-00280: change 6612481053 for thread 1 is in sequence #8

Specify log: {=suggested | filename | AUTO | CANCEL}

/oradata/REDO1_01.LOG

Log applied.

Media recovery complete.

SQL> alter database open resetlogs;

Database altered.

SQL> select * from mytable;

----

6 rows selected. SQL> alter database open resetlogs;

6 rows selected.

/*小結(jié)*/

在出現(xiàn)介質(zhì)錯(cuò)誤時(shí),如果存在一致的完全備份和備份之后的所有歸檔,則可以考慮使用這種方法恢復(fù).

恢復(fù)分為三個(gè)步驟.

1,文件覆蓋;

2,recover database using backup controlfile until cancel選擇auto,自動(dòng)使用已經(jīng)備份的東東恢復(fù).

3,查詢v$log,從status為current的日志開始(其他應(yīng)該為unused,因?yàn)槠渌膽?yīng)該已經(jīng)歸檔,并在上一步中成功恢復(fù)).

4,使用current的下一個(gè)要?dú)w檔的日志文件完全恢復(fù)過程.

一個(gè)數(shù)據(jù)庫完全一致恢復(fù)的測試實(shí)例

阿維塔、賽力斯已入股！華為引望可能成“中國博世”

Trianz與AWS達(dá)成戰(zhàn)略合作協(xié)議，徹底改變云采用和管理方式

人工智能驅(qū)動(dòng)工具SODA V將顛覆汽車市場，使汽車開發(fā)時(shí)間和成本降低90%

從容應(yīng)對未知風(fēng)險(xiǎn)----解密亞馬遜云科技的韌性之道

中國游戲市場開始復(fù)蘇！騰訊、網(wǎng)易等巨頭縮減在日本投資

獨(dú)立自主！華為董事：致力打造不依賴西方的技術(shù)

華為張平安：數(shù)字世界話語權(quán)最終由生態(tài)繁榮決定！

中國通信服務(wù)公布2024年中期業(yè)績

NVI技術(shù)創(chuàng)新聯(lián)盟成立！自主生態(tài)將帶動(dòng)產(chǎn)業(yè)鏈高速發(fā)展

軟通動(dòng)力與長三角投資達(dá)成戰(zhàn)略合作共謀數(shù)字生態(tài)新發(fā)展

海南區(qū)6家凱悅系酒店與嵐圖達(dá)成戰(zhàn)略合作，共同推動(dòng)新能源出行體驗(yàn)

安嵐攜手妮可?巴菲特開啟療愈之旅在秋日紅葉的浪漫中療愈身心

不懼美國封鎖！華為：我們給大家提供系統(tǒng)、存儲(chǔ)等

尼爾森IQ深耕中國四十載，共繪未來新篇章

第二十二屆跨盈年度B2B營銷高管峰會(huì)2025聚焦"營銷競?cè)?，打破市場?nèi)卷實(shí)現(xiàn)認(rèn)知進(jìn)化"

恒久動(dòng)力馳騁天地美孚1號攜手周冠宇邀您縱擎馳騁，勁享駕趣體驗(yàn)

美通社母公司Cision發(fā)布CisionOne平臺(tái)，進(jìn)軍亞太地區(qū)媒體監(jiān)測市場

移遠(yuǎn)通信推出大模型解決方案，重塑千行百業(yè)智能邊界

高途公布2024年第二季度未經(jīng)審計(jì)業(yè)績

華為發(fā)布AI百校計(jì)劃：培養(yǎng)AI人才每年獲最高100萬支持