數(shù)據(jù)庫(kù)中數(shù)據(jù)文件不一致如何恢復(fù)-創(chuàng)新互聯(lián)

小編給大家分享一下數(shù)據(jù)庫(kù)中數(shù)據(jù)文件不一致如何恢復(fù)，相信大部分人都還不怎么了解，因此分享這篇文章給大家參考一下，希望大家閱讀完這篇文章后大有收獲，下面讓我們一起去了解一下吧！

創(chuàng)新互聯(lián)公司是一家專業(yè)提供清遠(yuǎn)企業(yè)網(wǎng)站建設(shè),專注與成都做網(wǎng)站、網(wǎng)站建設(shè)、H5高端網(wǎng)站建設(shè)、小程序制作等業(yè)務(wù)。10年已為清遠(yuǎn)眾多企業(yè)、政府機(jī)構(gòu)等服務(wù)。創(chuàng)新互聯(lián)專業(yè)網(wǎng)站設(shè)計(jì)公司優(yōu)惠進(jìn)行中。

還原數(shù)據(jù)文件無(wú)法recover 成功的場(chǎng)景：

1、數(shù)據(jù)庫(kù)時(shí)在非歸檔的模式下運(yùn)行：

SQL> archive log list

Database log mode No Archive Mode

Automatic archival Disabled

Archive destination /u01/archivelog

Oldest online log sequence 1

Current log sequence 2

2、移動(dòng)其中的一個(gè)數(shù)據(jù)文件，然后將其 offline，具體操作如下：

col file_name for a50;

select file_name,ONLINE_STATUS from dba_data_files;

SQL> col file_name for a50;

SQL> select file_name,ONLINE_STATUS from dba_data_files;

FILE_NAME ONLINE_

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

/u01/app/oradata/orclpri/users01.dbf ONLINE

/u01/app/oradata/orclpri/undotbs01.dbf ONLINE

/u01/app/oradata/orclpri/sysaux01.dbf ONLINE

/u01/app/oradata/orclpri/system01.dbf SYSTEM

/u01/app/oradata/orclpri/datafileep_scs_idx.dbf ONLINE

/u01/app/oradata/orclpri/bpep_scs.dbf ONLINE

/u01/bpep_caweb.dbf ONLINE

/u01/app/oradata/orclpri/bpep_caweb_idx.dbf ONLINE

/u01/app/oradata/orclpri/readonly01.dbf ONLINE

/u01/app/oradata/orclpri/readwrite01.dbf ONLINE

/u01/test.dbf ONLINE

FILE_NAME ONLINE_

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

/u01/app/oradata/orclpri/test2.dbf ONLINE

/u01/app/oradata/orclpri/test3.dbf ONLINE

我們還是以 test數(shù)據(jù)文件來(lái)做實(shí)驗(yàn)。

先做一個(gè)MV操作：

SQL> !mv /u01/test.dbf /u02/

然后將MV過(guò)之后的數(shù)據(jù)文件 offline

SQL> alter database datafile '/u01/test.dbf' offline drop;

Database altered.

切換日志文件：

SQL> alter system switch logfile;

System altered.

多切換幾次日志文件。

關(guān)閉數(shù)據(jù)庫(kù)：

SQL> shutdown immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

將數(shù)據(jù)庫(kù)啟動(dòng)到mount 狀態(tài)：

SQL> startup mount;

ORACLE instance started.

Total System Global Area 849530880 bytes

Fixed Size 1339824 bytes

Variable Size 566234704 bytes

Database Buffers 276824064 bytes

Redo Buffers 5132288 bytes

Database mounted.

將MV過(guò)的數(shù)據(jù)文件做rename 操作：

SQL> alter database rename file '/u01/test.dbf' to '/u02/test.dbf';

Database altered.

將數(shù)據(jù)庫(kù)打開：

SQL> alter database open;

查看各個(gè)數(shù)據(jù)文件的狀態(tài)：

SQL> select file_name,ONLINE_STATUS from dba_data_files;

FILE_NAME ONLINE_

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

/u01/app/oradata/orclpri/users01.dbf ONLINE

/u01/app/oradata/orclpri/undotbs01.dbf ONLINE

/u01/app/oradata/orclpri/sysaux01.dbf ONLINE

/u01/app/oradata/orclpri/system01.dbf SYSTEM

/u01/app/oradata/orclpri/datafileep_scs_idx.dbf ONLINE

/u01/app/oradata/orclpri/bpep_scs.dbf ONLINE

/u01/bpep_caweb.dbf ONLINE

/u01/app/oradata/orclpri/bpep_caweb_idx.dbf ONLINE

/u01/app/oradata/orclpri/readonly01.dbf ONLINE

/u01/app/oradata/orclpri/readwrite01.dbf ONLINE

/u02/test.dbf RECOVER

FILE_NAME ONLINE_

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

/u01/app/oradata/orclpri/test2.dbf ONLINE

/u01/app/oradata/orclpri/test3.dbf ONLINE

將數(shù)據(jù)文件做 online 操作，會(huì)產(chǎn)生如下操作：

SQL> alter database datafile '/u02/test.dbf' online;

alter database datafile '/u02/test.dbf' online

ERROR at line 1:

ORA-01113: file 11 needs media recovery

ORA-01110: data file 11: '/u02/test.dbf'

嘗試做recover數(shù)據(jù)文件操作：

SQL> recover datafile '/u02/test.dbf';

ORA-00279: change 2684134 generated at 12/04/2016 21:49:15 needed for thread 1

ORA-00289: suggestion : /u01/archivelog/1_2_929742548.dbf

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

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

auto

ORA-00308: cannot open archived log '/u01/archivelog/1_2_929742548.dbf'

ORA-27037: unable to obtain file status

Linux Error: 2: No such file or directory

Additional information: 3

ORA-00308: cannot open archived log '/u01/archivelog/1_2_929742548.dbf'

ORA-27037: unable to obtain file status

Linux Error: 2: No such file or directory

Additional information: 3

發(fā)現(xiàn) 做recover操作的時(shí)候報(bào)上面的錯(cuò)誤。

這個(gè)時(shí)候想要正常的將數(shù)據(jù)文件online，可能就需要放棄數(shù)據(jù)的一致性，需要用到 _allow_resetlogs_corruption參數(shù)。

將 _allow_resetlogs_corruption 參數(shù)設(shè)置為 true ，然后可以將數(shù)據(jù)文件online

修改這個(gè)參數(shù)：

SQL> alter system set "_allow_resetlogs_corruption" =true scope=spfile;

SQL> show parameter allow

NAME TYPE VALUE

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

_allow_resetlogs_corruption boolean TRUE

將數(shù)據(jù)庫(kù)重啟到 mount狀態(tài)，然后使用RMAN進(jìn)入，查看incarnation

RMAN> list incarnation;

List of Database Incarnations

DB Key Inc Key DB Name DB ID STATUS Reset SCN Reset Time

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

1 1 ORCLPRI 1094561153 PARENT 1 13-AUG-09

2 2 ORCLPRI 1094561153 PARENT 754488 13-JUL-16

3 3 ORCLPRI 1094561153 PARENT 2663124 04-DEC-16

4 4 ORCLPRI 1094561153 PARENT 2683818 04-DEC-16

5 5 ORCLPRI 1094561153 CURRENT 2684130 04-DEC-16

我們將數(shù)據(jù)庫(kù)reset到上個(gè)還原點(diǎn)：

RMAN> reset database to Incarnation 4;

然后重新將庫(kù)啟動(dòng)到mount狀態(tài)：

將數(shù)據(jù)文件online ：

alter database datafile '/u02/test.dbf' online;

這個(gè)時(shí)候不能直接打開數(shù)據(jù)庫(kù)：

SQL> alter database open;

alter database open

ERROR at line 1:

ORA-01190: control file or data file 11 is from before the last RESETLOGS

ORA-01110: data file 11: '/u02/test.dbf'

SQL> alter database open resetlogs;

alter database open resetlogs

ERROR at line 1:

ORA-01139: RESETLOGS option only valid after an incomplete database recovery

我們可以recover database：

使用下面的兩個(gè)命令：

SQL> recover database until cancel;

SQL> recover database using backup controlfile until cancel;

然后可以打開數(shù)據(jù)庫(kù)：

SQL> alter database open resetlogs;

Database altered.

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

SQL> select file_name,ONLINE_STATUS from dba_data_files;

FILE_NAME ONLINE_

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

/u01/app/oradata/orclpri/users01.dbf ONLINE

/u01/app/oradata/orclpri/undotbs01.dbf ONLINE

/u01/app/oradata/orclpri/sysaux01.dbf ONLINE

/u01/app/oradata/orclpri/system01.dbf SYSTEM

/u01/app/oradata/orclpri/datafileep_scs_idx.dbf ONLINE

/u01/app/oradata/orclpri/bpep_scs.dbf ONLINE

/u01/bpep_caweb.dbf ONLINE

/u01/app/oradata/orclpri/bpep_caweb_idx.dbf ONLINE

/u01/app/oradata/orclpri/readonly01.dbf ONLINE

/u01/app/oradata/orclpri/readwrite01.dbf ONLINE

/u02/test.dbf ONLINE

FILE_NAME ONLINE_

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

/u01/app/oradata/orclpri/test2.dbf ONLINE

/u01/app/oradata/orclpri/test3.dbf ONLINE

13 rows selected.

以上是“數(shù)據(jù)庫(kù)中數(shù)據(jù)文件不一致如何恢復(fù)”這篇文章的所有內(nèi)容，感謝各位的閱讀！相信大家都有了一定的了解，希望分享的內(nèi)容對(duì)大家有所幫助，如果還想學(xué)習(xí)更多知識(shí)，歡迎關(guān)注創(chuàng)新互聯(lián)-成都網(wǎng)站建設(shè)公司行業(yè)資訊頻道！

名稱欄目：數(shù)據(jù)庫(kù)中數(shù)據(jù)文件不一致如何恢復(fù)-創(chuàng)新互聯(lián)
文章轉(zhuǎn)載：http://weahome.cn/article/ccjojd.html

真实的国产乱ⅩXXX66竹夫人,五月香六月婷婷激情综合,亚洲日本VA一区二区三区,亚洲精品一区二区三区麻豆

數(shù)據(jù)庫(kù)中數(shù)據(jù)文件不一致如何恢復(fù)-創(chuàng)新互聯(lián)

其他資訊

網(wǎng)站制作

企業(yè)服務(wù)

網(wǎng)站建設(shè)

服務(wù)器托管