怎么進行oracle控制文件的恢復

這篇文章將為大家詳細講解有關(guān)怎么進行oracle控制文件的恢復，文章內(nèi)容質(zhì)量較高，因此小編分享給大家做個參考，希望大家閱讀完這篇文章后對相關(guān)知識有一定的了解。

1重建控制文件

SYS@hyyk> alter database backup controlfile to '/home/oracle/controlfile01.ctl';

2.刪除當前使用的控制文件

SYS@hyyk> ho rm -rf /u01/app/oracle/oradata/hyyk/control01.ctl

3.從啟數(shù)據(jù)庫

SYS@hyyk> startup force

ORACLE instance started.

Total System Global Area  776646656 bytes

Fixed Size                  2217384 bytes

Variable Size           578816600 bytes

Database Buffers     188743680 bytes

Redo Buffers             6868992 bytes

ORA-00205: error in identifying control file, check alert log for more info

4.拷貝備份的控住文件

SYS@hyyk>ho cp /home/oracle/controlfile01.ctl /u01/app/oracle/oradata/hyyk/control01.ctl

SYS@hyyk> alter database mount;

Database altered.

SYS@hyyk> alter database open;

alter database open

*

ERROR at line 1:

ORA-01589: must use RESETLOGS or NORESETLOGS option for database open

SYS@hyyk> alter database open resetlogs;

alter database open resetlogs

*

ERROR at line 1:

ORA-01194: file 1 needs more recovery to be consistent

ORA-01110: data file 1: '/u01/app/oracle/oradata/hyyk/system01.dbf'

SYS@hyyk> recover database using backup controlfile;

ORA-00279: change 1324612 generated at 11/03/2017 11:03:43 needed for thread 1

ORA-00289: suggestion : /u01/app/oracle/archive/arch_1_943793507_23.log

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

這里查看/u01/app/oracle/archive/arch_1_943793507_23.log是否存在

如果不存在說明沒有歸檔，

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

/u01/app/oracle/oradata/hyyk/redo02.log

Log applied.

Media recovery complete.

//以 RESETLOGS 的方式啟動數(shù)據(jù)庫

SYS@hyyk> alter database open resetlogs;

Database altered.

//及時進行全庫備份

實驗2 修改數(shù)據(jù)庫結(jié)構(gòu)后丟所有的控制文件

實驗思路：1.備份當前的控制文件

2.建立一個表空間和表

          3.刪除當前的控制文件

          4.從啟數(shù)據(jù)庫

          5.發(fā)現(xiàn)控制文件和數(shù)據(jù)文件不一致

備份控制文件

SYS@hyyk> alter database backup controlfile to '/home/oracle/control01.ctl';

創(chuàng)建表空間和表

SYS@hyyk> create tablespace t2 datafile '/u01/app/oracle/oradata/hyyk/t2.dbf' size 10m;

SYS@hyyk> create table b(id number) tablespace t2;

SYS@hyyk> insert into b values(2);

SYS@hyyk> commit;

刪除當前的控制文件

SYS@hyyk> ho cp /home/oracle/control01.ctl /u01/app/oracle/oradata/hyyk/control01.ctl

SYS@hyyk> ho cp /home/oracle/control01.ctl /u01/app/oracle/oradata/hyyk/control02.ctl

將數(shù)據(jù)庫mount階段

SYS@hyyk> alter database mount;

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

SYS@hyyk> alter database open;

alter database open

*

ERROR at line 1:

ORA-01589: must use RESETLOGS or NORESETLOGS option for database open

SYS@hyyk> alter database open resetlogs;

alter database open resetlogs

*

ERROR at line 1:

ORA-01194: file 1 needs more recovery to be consistent

ORA-01110: data file 1: '/u01/app/oracle/oradata/hyyk/system01.dbf'

查看當前正在使用的日志

SYS@hyyk>  select * from v$log;

    GROUP#    THREAD#  SEQUENCE#      BYTES  BLOCKSIZE    MEMBERS ARC

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

STATUS           FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME

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

         1          1          1   52428800        512          1 YES

INACTIVE               1347611 05-NOV-17      1354710 05-NOV-17

         3          1          3   52428800        512          1 NO

CURRENT                1358125 05-NOV-17   2.8147E+14

         2          1          2   52428800        512          1 YES

INACTIVE               1354710 05-NOV-17      1358125 05-NOV-17

恢復當前的數(shù)據(jù)庫

SYS@hyyk> recover database using backup controlfile;

ORA-00279: change 1358125 generated at 11/05/2017 12:37:33 needed for thread 1

ORA-00289: suggestion : /u01/app/oracle/archive/arch_1_959256089_3.log

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

//這里查看是否存在歸檔日志，如果存在下面選擇auto，如果不存在就輸入當前的rodo log

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

 /u01/app/oracle/oradata/hyyk/redo03.log

ORA-00283: recovery session canceled due to errors

ORA-01244: unnamed datafile(s) added to control file by media recovery

ORA-01110: data file 7: '/u01/app/oracle/oradata/hyyk/t2.dbf'

ORA-01112: media recovery not started

SYS@hyyk> select name from v$datafile;

NAME

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

/u01/app/oracle/oradata/hyyk/system01.dbf

/u01/app/oracle/oradata/hyyk/sysaux01.dbf

/u01/app/oracle/oradata/hyyk/undotbs01.dbf

/u01/app/oracle/oradata/hyyk/users01.dbf

/u01/app/oracle/oradata/hyyk/example01.dbf

/u01/app/oracle/oradata/hyyk/t1.dbf

/u01/app/oracle/product/11.2.0/dbhome_1/dbs/UNNAMED00007

數(shù)據(jù)文件改名

SYS@hyyk> alter database create datafile'/u01/app/oracle/product/11.2.0/dbhome_1/dbs/UNNAMED00007' as

  2  '/u01/app/oracle/oradata/hyyk/t2.dbf';

9.恢復數(shù)據(jù)庫

SYS@hyyk> recover database using backup controlfile;

ORA-00279: change 1364363 generated at 11/05/2017 13:51:22 needed for thread 1

ORA-00289: suggestion : /u01/app/oracle/archive/arch_1_959256089_3.log

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

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

/u01/app/oracle/oradata/hyyk/redo03.log

Log applied.

Media recovery complete.

SYS@hyyk> alter database open resetlogs;

Database altered.

查看數(shù)據(jù)文件

SYS@hyyk> select name from v$datafile;

NAME

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

/u01/app/oracle/oradata/hyyk/system01.dbf

/u01/app/oracle/oradata/hyyk/sysaux01.dbf

/u01/app/oracle/oradata/hyyk/undotbs01.dbf

/u01/app/oracle/oradata/hyyk/users01.dbf

/u01/app/oracle/oradata/hyyk/example01.dbf

/u01/app/oracle/oradata/hyyk/t1.dbf

/u01/app/oracle/oradata/hyyk/t2.dbf

7 rows selected.

SYS@hyyk> select * from b;

        ID

----------

關(guān)于怎么進行oracle控制文件的恢復就分享到這里了，希望以上內(nèi)容可以對大家有一定的幫助，可以學到更多知識。如果覺得文章不錯，可以把它分享出去讓更多的人看到。