溫馨提示×

溫馨提示×

您好，登錄后才能下訂單哦！

密碼登錄×

忘記密碼？

登錄注冊(cè)×

獲取短信驗(yàn)證碼

其他方式登錄

點(diǎn)擊登錄注冊(cè) 即表示同意《億速云用戶(hù)服務(wù)條款》

用戶(hù)登錄×

賬戶(hù)密碼登錄

請(qǐng)使用微信掃描上方二維碼

使用幫助

請(qǐng)求超時(shí)！

請(qǐng)點(diǎn)擊重新獲取二維碼

無(wú)備份有完全歸檔日志情況下恢復(fù)數(shù)據(jù)文件

發(fā)布時(shí)間：2020-06-21 12:34:55 來(lái)源：網(wǎng)絡(luò) 閱讀：1741 作者：striong 欄目：關(guān)系型數(shù)據(jù)庫(kù)

如果一個(gè)數(shù)據(jù)文件創(chuàng)建時(shí)到當(dāng)前所有的歸檔日志都存在的話，那么可以通過(guò)offline datafile，recover datafile，online datafile的方式直接恢復(fù)數(shù)據(jù)文件，此種恢復(fù)屬于介質(zhì)恢復(fù)。

下面我們做一個(gè)實(shí)驗(yàn)：

查看當(dāng)前所有的歸檔日志的初始scn以及終止scn：（status 為 A 表示available，D表示已刪除）

SQL> col name for a100

SQL> select sequence#,name,first_change#,next_change#,status from v$archived_log;

SEQUENCE# NAME FIRST_CHANGE# NEXT_CHANGE# S

---------- ---------------------------------------------------------------------------------------------------- ------------- ------------ -----

5 /u01/app/oracle/fast_recovery_area/ORA11G/archivelog/2016_07_09/o1_mf_1_5_cqztt6no_.arc 990220 1001021 A

6 /u01/app/oracle/fast_recovery_area/ORA11G/archivelog/2016_07_09/o1_mf_1_6_cqzwnzqb_.arc 1001021 1006241 A

7 /u01/app/oracle/fast_recovery_area/ORA11G/archivelog/2016_07_09/o1_mf_1_7_cqzwz0z5_.arc 1006241 1006650 A

8 /u01/app/oracle/fast_recovery_area/ORA11G/archivelog/2016_07_09/o1_mf_1_8_cqzybtrv_.arc 1006650 1009513 A

9 /u01/app/oracle/fast_recovery_area/ORA11G/archivelog/2016_07_09/o1_mf_1_9_cqzybvt8_.arc 1009513 1009663 A

10 /u01/app/oracle/fast_recovery_area/ORA11G/archivelog/2016_07_09/o1_mf_1_10_cqzycr5z_.arc 1009663 1009860 A

11 /u01/app/oracle/fast_recovery_area/ORA11G/archivelog/2016_07_09/o1_mf_1_11_cqzycw9l_.arc 1009860 1010001 A

12 /u01/app/oracle/fast_recovery_area/ORA11G/archivelog/2016_07_09/o1_mf_1_12_cqzycxn6_.arc 1010001 1010136 A

13 /u01/app/oracle/fast_recovery_area/ORA11G/archivelog/2016_07_09/o1_mf_1_13_cqzycyyl_.arc 1010136 1010268 A

9 rows selected.

查看各數(shù)據(jù)文件的創(chuàng)建SCN，以及checkpoint SCN：

SQL> select file#,name,status,fuzzy,creation_change#,checkpoint_change# from v$datafile_header;

FILE# NAME STATUS FUZ CREATION_CHANGE# CHECKPOINT_CHANGE#

---------- ---------------------------------------------------------------------------------------------------- ------- --- ---------------- ------------------

1 /u01/app/oracle/oradata/ora11g/system01.dbf ONLINE YES 7 1010268

2 /u01/app/oracle/oradata/ora11g/sysaux01.dbf ONLINE YES 1834 1010268

3 /u01/app/oracle/oradata/ora11g/undotbs01.dbf ONLINE YES 923328 1010268

4 /u01/app/oracle/oradata/ora11g/users01.dbf ONLINE YES 16143 1010268

5 /u01/app/oracle/oradata/ora11g/example01.dbf ONLINE YES 952919 1010268

6 /u01/app/oracle/oradata/ora11g/users02.dbf ONLINE YES 1005138 1010268

6 rows selected.

可以發(fā)現(xiàn)當(dāng)前只有datafile 6是在第一個(gè)歸檔日志后創(chuàng)建的，因此，如果這個(gè)文件corruption的話，可以直接通過(guò)介質(zhì)恢復(fù)的方式來(lái)恢復(fù)該數(shù)據(jù)文件，而不需要使用備份。

SQL> select file#,name,status,fuzzy,creation_change#,checkpoint_change# from v$datafile_header where creation_change# > 990220;

FILE# NAME STATUS FUZ CREATION_CHANGE# CHECKPOINT_CHANGE#

---------- ---------------------------------------------------------------------------------------------------- ------- --- ---------------- ------------------

6 /u01/app/oracle/oradata/ora11g/users02.dbf ONLINE YES 1005138 1010268

刪除datafile 6文件

[oracle@ora11g scripts]$ rm -f /u01/app/oracle/oradata/ora11g/users02.dbf

重啟數(shù)據(jù)庫(kù)暴露數(shù)據(jù)文件丟失問(wèn)題（生產(chǎn)環(huán)境切勿操作）

SQL> startup force

ORACLE instance started.

Total System Global Area 730714112 bytes

Fixed Size 2256832 bytes

Variable Size 486539328 bytes

Database Buffers 239075328 bytes

Redo Buffers 2842624 bytes

Database mounted.

ORA-01157: cannot identify/lock data file 6 - see DBWR trace file

ORA-01110: data file 6: '/u01/app/oracle/oradata/ora11g/users02.dbf'

查看數(shù)據(jù)文件狀態(tài)，可以發(fā)現(xiàn)數(shù)據(jù)庫(kù)無(wú)法識(shí)別數(shù)據(jù)文件6了

SQL> select file#,name,status,fuzzy,creation_change#,checkpoint_change# from v$datafile_header;

FILE# NAME STATUS FUZ CREATION_CHANGE# CHECKPOINT_CHANGE#

---------- ---------------------------------------------------------------------------------------------------- ------- --- ---------------- ------------------

1 /u01/app/oracle/oradata/ora11g/system01.dbf ONLINE YES 7 1013668

2 /u01/app/oracle/oradata/ora11g/sysaux01.dbf ONLINE YES 1834 1013668

3 /u01/app/oracle/oradata/ora11g/undotbs01.dbf ONLINE YES 923328 1013668

4 /u01/app/oracle/oradata/ora11g/users01.dbf ONLINE YES 16143 1013668

5 /u01/app/oracle/oradata/ora11g/example01.dbf ONLINE YES 952919 1013668

6 ONLINE 0 0

先offline datafile 6來(lái)打開(kāi)數(shù)據(jù)庫(kù)

SQL> alter database datafile 6 offline;

Database altered.

SQL> alter database open;

Database altered.

創(chuàng)建數(shù)據(jù)文件6

SQL> alter database create datafile '/u01/app/oracle/oradata/ora11g/users02.dbf';

Database altered.

恢復(fù)數(shù)據(jù)庫(kù)文件6

SQL> recover datafile 6;

ORA-00279: change 1005138 generated at 07/09/2016 02:33:49 needed for thread 1

ORA-00289: suggestion : /u01/app/oracle/fast_recovery_area/ORA11G/archivelog/2016_07_09/o1_mf_1_6_cqzwnzqb_.arc

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

Specify log: {<RET>=suggested | filename | AUTO | CANCEL}

ORA-00279: change 1006241 generated at 07/09/2016 02:36:15 needed for thread 1

ORA-00289: suggestion : /u01/app/oracle/fast_recovery_area/ORA11G/archivelog/2016_07_09/o1_mf_1_7_cqzwz0z5_.arc

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

Specify log: {<RET>=suggested | filename | AUTO | CANCEL}

ORA-00279: change 1006650 generated at 07/09/2016 02:41:36 needed for thread 1

ORA-00289: suggestion : /u01/app/oracle/fast_recovery_area/ORA11G/archivelog/2016_07_09/o1_mf_1_8_cqzybtrv_.arc

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

Specify log: {<RET>=suggested | filename | AUTO | CANCEL}

ORA-00279: change 1009513 generated at 07/09/2016 03:04:58 needed for thread 1

ORA-00289: suggestion : /u01/app/oracle/fast_recovery_area/ORA11G/archivelog/2016_07_09/o1_mf_1_9_cqzybvt8_.arc

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

Specify log: {<RET>=suggested | filename | AUTO | CANCEL}

ORA-00279: change 1009663 generated at 07/09/2016 03:04:59 needed for thread 1

ORA-00289: suggestion : /u01/app/oracle/fast_recovery_area/ORA11G/archivelog/2016_07_09/o1_mf_1_10_cqzycr5z_.arc

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

Specify log: {<RET>=suggested | filename | AUTO | CANCEL}

ORA-00279: change 1009860 generated at 07/09/2016 03:05:28 needed for thread 1

ORA-00289: suggestion : /u01/app/oracle/fast_recovery_area/ORA11G/archivelog/2016_07_09/o1_mf_1_11_cqzycw9l_.arc

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

Specify log: {<RET>=suggested | filename | AUTO | CANCEL}

ORA-00279: change 1010001 generated at 07/09/2016 03:05:32 needed for thread 1

ORA-00289: suggestion : /u01/app/oracle/fast_recovery_area/ORA11G/archivelog/2016_07_09/o1_mf_1_12_cqzycxn6_.arc

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

Specify log: {<RET>=suggested | filename | AUTO | CANCEL}

Log applied.

Media recovery complete.

SQL> SQL>

恢復(fù)完成

查看數(shù)據(jù)文件狀態(tài)

SQL> select file#,name,status,fuzzy,creation_change#,checkpoint_change# from v$datafile_header;

FILE# NAME STATUS FUZ CREATION_CHANGE# CHECKPOINT_CHANGE#

---------- ---------------------------------------------------------------------------------------------------- ------- --- ---------------- ------------------

1 /u01/app/oracle/oradata/ora11g/system01.dbf ONLINE YES 7 1034000

2 /u01/app/oracle/oradata/ora11g/sysaux01.dbf ONLINE YES 1834 1034000

3 /u01/app/oracle/oradata/ora11g/undotbs01.dbf ONLINE YES 923328 1034000

4 /u01/app/oracle/oradata/ora11g/users01.dbf ONLINE YES 16143 1034000

5 /u01/app/oracle/oradata/ora11g/example01.dbf ONLINE YES 952919 1034000

6 /u01/app/oracle/oradata/ora11g/users02.dbf OFFLINE NO 1005138 1034000

online數(shù)據(jù)文件6

SQL> alter database datafile 6 online;

Database altered.

恢復(fù)完成，確認(rèn)數(shù)據(jù)文件狀態(tài)

SQL> select file#,name,status,fuzzy,creation_change#,checkpoint_change# from v$datafile_header;

FILE# NAME STATUS FUZ CREATION_CHANGE# CHECKPOINT_CHANGE#

---------- ---------------------------------------------------------------------------------------------------- ------- --- ---------------- ------------------

1 /u01/app/oracle/oradata/ora11g/system01.dbf ONLINE YES 7 1034000

2 /u01/app/oracle/oradata/ora11g/sysaux01.dbf ONLINE YES 1834 1034000

3 /u01/app/oracle/oradata/ora11g/undotbs01.dbf ONLINE YES 923328 1034000

4 /u01/app/oracle/oradata/ora11g/users01.dbf ONLINE YES 16143 1034000

5 /u01/app/oracle/oradata/ora11g/example01.dbf ONLINE YES 952919 1034000

6 /u01/app/oracle/oradata/ora11g/users02.dbf ONLINE YES 1005138 1034717

向AI問(wèn)一下細(xì)節(jié)

推薦閱讀：

免責(zé)聲明：本站發(fā)布的內(nèi)容（圖片、視頻和文字）以原創(chuàng)、轉(zhuǎn)載和分享為主，文章觀點(diǎn)不代表本網(wǎng)站立場(chǎng)，如果涉及侵權(quán)請(qǐng)聯(lián)系站長(zhǎng)郵箱：is@yisu.com進(jìn)行舉報(bào)，并提供相關(guān)證據(jù)，一經(jīng)查實(shí)，將立刻刪除涉嫌侵權(quán)內(nèi)容。

上一篇新聞：
解密“CDO-首席數(shù)據(jù)官”的價(jià)值、挑戰(zhàn)及發(fā)展
下一篇新聞：
HP ProLiant SL170S 事件WHEA-Logger&ID47

猜你喜歡

AI
助
手

產(chǎn)品服務(wù)

地區(qū)劃分

專(zhuān)題活動(dòng)

幫助支持

關(guān)于我們

售后咨詢(xún)

7*24小時(shí)在線電話：400-100-2938

7*24小時(shí)在線 QQ：800811969

關(guān)注億速云

億速云公眾號(hào)

手機(jī)網(wǎng)站二維碼

<strong id="hacjv"><sup id="hacjv"></sup></strong>

<progress id="hacjv"><menuitem id="hacjv"></menuitem></progress>