当前位置:  数据库>oracle

Oracle数据库丢失控制文件的恢复四则

    来源: 互联网  发布时间：2017-06-24

    本文导语: 下文介绍了Oracle数据库中丢失控制文件的几种处理方法。 丢失单个控制文件 报错信息： 2013-05-08 03:00:29.678000 +08:00 Errors in file/u01/apps/oracle/diag/rdbms/bkt/bkt/trace/bkt_m000_5204.trc: ORA-00210: cannot open the specifiedcontrol file ORA-00202: control file:'...

下文介绍了Oracle数据库中丢失控制文件的几种处理方法。

丢失单个控制文件

报错信息：

2013-05-08 03:00:29.678000 +08:00

Errors in file/u01/apps/oracle/diag/rdbms/bkt/bkt/trace/bkt_m000_5204.trc:

ORA-00210: cannot open the specifiedcontrol file

ORA-00202: control file:'/u02/oradat/bkt/control01.ctl'

ORA-27041: unable to open file

Linux-x86_64 Error: 2: No such file ordirectory

Additional information: 3

Errors in file/u01/apps/oracle/diag/rdbms/bkt/bkt/trace/bkt_m001_5289.trc:

ORA-00210: cannot open the specifiedcontrol file

ORA-00202: control file:'/u02/oradat/bkt/control01.ctl'

ORA-27041: unable to open file

Linux-x86_64 Error: 2: No such file ordirectory

1、直接手动关闭了，其实数据库会自动的关闭。

shutdown abort ;

2、以下有两种方法

2.1、拷贝控制文件到原来的目录

cp/u02/flash_recovery_area/bkt/control02.ctl /u02/oradat/bkt/control01.ctl

2.2、启动到nomount后设置control_files的位置，将丢失的控制文件路径去掉

alter system setcontrol_files='/u02/flash_recovery_area/bkt/control02.ctl' scope=spfile ;

3.启动数据库即可

startup

下面介绍丢失所有控制文件的时候应该怎么做

使用冷备份的控制文件恢复

以下为详细的示例：

1. backup controlfile

show controlfile

copy

rman target /

backup controlfile current format '' ;

output :

sys@BKT> show parameter control

NAME                                TYPE        VALUE

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

control_file_record_keep_time        integer    7

control_files                        string      /u02/oradat/bkt/control01.ctl,

                                                 /u02/flash_recovery_area/bkt/

                                                control02.ctl

control_management_pack_access      string      DIAGNOSTIC+TUNING

--instance still running ...

[oracle@master ~]$ cp/u02/oradat/bkt/control01.ctl /tmp/control01.ctl

2. create tablespace

conn / as sysdba

define tbsname1='tbs1'

define tbsname2='tbs2'

define dfpath1='/u02/oradat/bkt/tbs101.dbf'

define dfpath2='/u02/oradat/bkt/tbs201.dbf'

create tablespace &tbsname1 datafile'&dfpath1' size 100m ;

create tablespace &tbsname2 datafile'&dfpath2' size 100m ;

create table &tbsname1 tablespace&tbsname1 as select * from all_objects ;

create table &tbsname2 tablespace&tbsname2 as select * from all_objects ;

select count(*) from &tbsname1 ;

select count(*) from &tbsname2 ;

alter tablespace &tbsname1 read only ;

output :

sys@BKT> conn / as sysdba

Connected.

sys@BKT>

sys@BKT> define tbsname1='tbs1'

sys@BKT> define tbsname2='tbs2'

sys@BKT>

sys@BKT> definedfpath1='/u02/oradat/bkt/tbs101.dbf'

sys@BKT> definedfpath2='/u02/oradat/bkt/tbs201.dbf'

sys@BKT> create tablespace &tbsname1datafile '&dfpath1' size 100m ;

old  1: create tablespace &tbsname1 datafile '&dfpath1' size 100m

new  1: create tablespace tbs1 datafile '/u02/oradat/bkt/tbs101.dbf' size100m

Tablespace created.

sys@BKT> create tablespace &tbsname2datafile '&dfpath2' size 100m ;

old  1: create tablespace &tbsname2 datafile '&dfpath2' size 100m

new  1: create tablespace tbs2 datafile '/u02/oradat/bkt/tbs201.dbf' size100m

Tablespace created.

sys@BKT> create table &tbsname1tablespace &tbsname1 as select * from all_objects ;

old  1: create table &tbsname1 tablespace &tbsname1 as select * fromall_objects

new  1: create table tbs1 tablespace tbs1 as select * from all_objects

Table created.

sys@BKT> create table &tbsname2tablespace &tbsname2 as select * from all_objects ;

old  1: create table &tbsname2 tablespace &tbsname2 as select * fromall_objects

new  1: create table tbs2 tablespace tbs2 as select * from all_objects

Table created.

sys@BKT> select count(*) from&tbsname1 ;

old  1: select count(*) from &tbsname1

new  1: select count(*) from tbs1

 COUNT(*)

----------

    72780

sys@BKT> select count(*) from&tbsname2 ;

old  1: select count(*) from &tbsname2

new  1: select count(*) from tbs2

 COUNT(*)

----------

    72781

sys@BKT> alter tablespace &tbsname1read only ;

old  1: alter tablespace &tbsname1 read only

new  1: alter tablespace tbs1 read only

Tablespace altered.

sys@BKT>

3. switch logfile

alter system switch logfile  ;

alter system switch logfile  ;

alter system switch logfile  ;

alter system switch logfile  ;

alter system switch logfile  ;

output :

sys@BKT> alter system switchlogfile  ;

alter system switch logfile  ;

alter system switch logfile  ;

alter system switch logfile  ;

System altered.

sys@BKT>

System altered.

sys@BKT>

System altered.

sys@BKT>

System altered.

4. remove controlfile

rm /u02/oradat/bkt/control01.ctl

rm/u02/flash_recovery_area/bkt/control02.ctl

output :

[oracle@master ~]$ rm/u02/oradat/bkt/control01.ctl

[oracle@master ~]$ rm/u02/flash_recovery_area/bkt/control02.ctl

--from alert .

Errors in file/u01/apps/oracle/diag/rdbms/bkt/bkt/trace/bkt_m000_6172.trc:

ORA-00210: cannot open the specifiedcontrol file

ORA-00202: control file:'/u02/oradat/bkt/control01.ctl'

ORA-27041: unable to open file

Linux-x86_64 Error: 2: No such file ordirectory

Additional information: 3

5. shutdown database

shutdown abort

output :

sys@BKT> shutdown abort ;

ORACLE instance shut down.

6. using backup controlfile

--copy backup controlfile to thecontrolfile path which defined in the parameter .

cp /tmp/control01.ctl/u02/oradat/bkt/control01.ctl

cp /tmp/control01.ctl/u02/flash_recovery_area/bkt/control02.ctl

recover

output :

cp /tmp/control01.ctl /u02/oradat/bkt/control01.ctl

cp /tmp/control01.ctl/u02/flash_recovery_area/bkt/control02.ctl

sys@BKT> startup

ORACLE instance started.

Total System Global Area  417546240 bytes

Fixed Size                  2228944 bytes

Variable Size            318770480 bytes

Database Buffers          88080384 bytes

Redo Buffers                8466432 bytes

Database mounted.

ORA-01122: database file 1 failedverification check

ORA-01110: data file 1:'/u02/oradat/bkt/system01.dbf'

ORA-01207: file is more recent than controlfile - old control file

--from alert

ALTER DATABASE OPEN

Errors in file/u01/apps/oracle/diag/rdbms/bkt/bkt/trace/bkt_ora_6327.trc:

ORA-01122: database file 1 failedverification check

ORA-01110: data file 1:'/u02/oradat/bkt/system01.dbf'

ORA-1122 signalled during: ALTER DATABASEOPEN...

Errors in file/u01/apps/oracle/diag/rdbms/bkt/bkt/trace/bkt_m000_6331.trc:

ORA-00338: log 1 of thread 1 is more recentthan control file

ORA-00312: online log 1 thread 1:'/u02/oradat/bkt/redo01.log'

Errors in file/u01/apps/oracle/diag/rdbms/bkt/bkt/trace/bkt_m000_6331.trc:

ORA-00338: log 1 of thread 1 is more recentthan control file

ORA-00312: online log 1 thread 1:'/u02/oradat/bkt/redo01.log'

Errors in file/u01/apps/oracle/diag/rdbms/bkt/bkt/trace/bkt_m000_6331.trc:

ORA-00338: log 2 of thread 1 is more recentthan control file

ORA-00312: online log 2 thread 1:'/u02/oradat/bkt/redo02.log'

Errors in file /u01/apps/oracle/diag/rdbms/bkt/bkt/trace/bkt_m000_6331.trc:

ORA-00338: log 2 of thread 1 is more recentthan control file

ORA-00312: online log 2 thread 1:'/u02/oradat/bkt/redo02.log'

Errors in file/u01/apps/oracle/diag/rdbms/bkt/bkt/trace/bkt_m000_6331.trc:

ORA-00338: log 3 of thread 1 is more recentthan control file

ORA-00312: online log 3 thread 1:'/u02/oradat/bkt/redo03.log'

Errors in file/u01/apps/oracle/diag/rdbms/bkt/bkt/trace/bkt_m000_6331.trc:

ORA-00338: log 3 of thread 1 is more recentthan control file

ORA-00312: online log 3 thread 1:'/u02/oradat/bkt/redo03.log'

Checker run found 1 new persistent datafailures

[oracle@master ~]$ oerr ora 01207

01207, 00000, "file is more recentthan control file - old control file"

// *Cause: The control file change sequence number in the data file is

//        greater than the number in the control file. This implies that

//        the wrong control file is being used. Note that repeatedly causing

//        this error can make it stop happening without correcting the real

//        problem. Every attempt to open the database will advance the

//        control file change sequence number until it is great enough.

// *Action: Use the current control file ordo backup control file recovery to

//        make the control file current. Be sure to follow all restrictions

//        on doing a backup control file recovery.

--显然没有我们最近创建的两个表空间

sys@BKT> select name from v$datafile ;

NAME

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

/u02/oradat/bkt/system01.dbf

/u02/oradat/bkt/sysaux01.dbf

/u02/oradat/bkt/undotbs01.dbf

/u02/oradat/bkt/users01.dbf

/u02/oradat/bkt/example01.dbf

/u02/oradat/bkt/test1_01.dbf

6 rows selected.

--controlfile_change# low rba ,checkpoint_change#

sys@BKT> select checkpoint_change#,CONTROLFILE_SEQUENCE# ,CONTROLFILE_CHANGE#  fromv$database ;

CHECKPOINT_CHANGE# CONTROLFILE_SEQUENCE#CONTROLFILE_CHANGE#

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

          1281710                  3287            1293684

sys@BKT> select min(checkpoint_change#)from V$datafile_header ;

MIN(CHECKPOINT_CHANGE#)

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

                1270438

sys@BKT> selectgroup#,first_change#,next_change# from v$log ;

   GROUP# FIRST_CHANGE# NEXT_CHANGE#

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

        1      1273500      1273646

        3      1274338  2.8147E+14

        2      1273646      1274338

sys@BKT> recover database;

ORA-00283: recovery session canceled due toerrors

ORA-01122: database file 1 failedverification check

ORA-01110: data file 1:'/u02/oradat/bkt/system01.dbf'

ORA-01207: file is more recent than controlfile - old control file

sys@BKT> recover database using backup controlfile ;

ORA-00279: change 1293684 generated at05/08/2013 14:29:38 needed for thread 1

ORA-00289: suggestion :/u02/flash_recovery_area/BKT/archivelog/2013_05_13/o1_mf_1_93_8s2qoprf_.arc

ORA-00280: change 1293684 for thread 1 isin sequence #93

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

auto

ORA-00283: recovery session canceled due toerrors

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

ORA-01110: data file 7:'/u02/oradat/bkt/tbs101.dbf'

ORA-01112: media recovery not started

这时候第七号文件已经加回来了。

sys@BKT> recover database using backupcontrolfile ;

ORA-00283: recovery session canceled due toerrors

ORA-01111: name for data file 7 is unknown- rename to correct file

ORA-01110: data file 7:'/u01/apps/oracle/product/11gr2/db_1/dbs/UNNAMED00007'

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

ORA-01111: name for data file 7 is unknown- rename to correct file

ORA-01110: data file 7: '/u01/apps/oracle/product/11gr2/db_1/dbs/UNNAMED00007'

sys@BKT> select name from v$datafile ;

NAME

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

/u02/oradat/bkt/system01.dbf

/u02/oradat/bkt/sysaux01.dbf

/u02/oradat/bkt/undotbs01.dbf

/u02/oradat/bkt/users01.dbf

/u02/oradat/bkt/example01.dbf

/u02/oradat/bkt/test1_01.dbf

/u01/apps/oracle/product/11gr2/db_1/dbs/UNNAMED00007

7 rows selected.

                   

offline以下再恢复

sys@BKT> alter database datafile 7offline ;

Database altered.

sys@BKT> recover database using backupcontrolfile ;

ORA-00279: change 1293752 generated at05/13/2013 14:42:44 needed for thread 1

ORA-00289: suggestion :/u02/flash_recovery_area/BKT/archivelog/2013_05_13/o1_mf_1_93_8s2qoprf_.arc

ORA-00280: change 1293752 for thread 1 isin sequence #93

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

auto

ORA-00283: recovery session canceled due toerrors

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

ORA-01110: data file 8: '/u02/oradat/bkt/tbs201.dbf'

ORA-01112: media recovery not started

这时候最后一个数据文件也加回来了

sys@BKT> select name from v$datafile ;

NAME

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

/u02/oradat/bkt/system01.dbf

/u02/oradat/bkt/sysaux01.dbf

/u02/oradat/bkt/undotbs01.dbf

/u02/oradat/bkt/users01.dbf

/u02/oradat/bkt/example01.dbf

/u02/oradat/bkt/test1_01.dbf

/u01/apps/oracle/product/11gr2/db_1/dbs/UNNAMED00007

/u01/apps/oracle/product/11gr2/db_1/dbs/UNNAMED00008

8 rows selected.

因为是表空间是readonly的，数据文件这里需要修改一下名称

sys@BKT> alter database rename file'/u01/apps/oracle/product/11gr2/db_1/dbs/UNNAMED00008' to'/u02/oradat/bkt/tbs201.dbf' ;

Database altered.

sys@BKT> alter database rename file'/u01/apps/oracle/product/11gr2/db_1/dbs/UNNAMED00007' to '/u02/oradat/bkt/tbs101.dbf';

 

Database altered.

sys@BKT> alter database datafile 7online ;

Database altered.

再次恢复

sys@BKT> recover database using backup controlfile ;

ORA-00279: change 1294029 generated at05/13/2013 14:42:58 needed for thread 1

ORA-00289: suggestion :/u02/flash_recovery_area/BKT/archivelog/2013_05_13/o1_mf_1_93_8s2qoprf_.arc

ORA-00280: change 1294029 for thread 1 isin sequence #93

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

auto

ORA-00279: change 1294568 generated at05/13/2013 14:44:22 needed for thread 1

ORA-00289: suggestion :/u02/flash_recovery_area/BKT/archivelog/2013_05_13/o1_mf_1_94_8s2qoqjj_.arc

ORA-00280: change 1294568 for thread 1 isin sequence #94

ORA-00278: log file'/u02/flash_recovery_area/BKT/archivelog/2013_05_13/o1_mf_1_93_8s2qoprf_.arc'

no longer needed for this recovery

ORA-00279: change 1294571 generated at05/13/2013 14:44:22 needed for thread 1

ORA-00289: suggestion :/u02/flash_recovery_area/BKT/archivelog/2013_05_13/o1_mf_1_95_8s2qovq1_.arc

ORA-00280: change 1294571 for thread 1 isin sequence #95

ORA-00278: log file'/u02/flash_recovery_area/BKT/archivelog/2013_05_13/o1_mf_1_94_8s2qoqjj_.arc'

no longer needed for this recovery

ORA-00279: change 1294574 generated at05/13/2013 14:44:27 needed for thread 1

ORA-00289: suggestion :/u02/flash_recovery_area/BKT/archivelog/2013_05_13/o1_mf_1_96_8s2qovsh_.arc

ORA-00280: change 1294574 for thread 1 isin sequence #96

ORA-00278: log file'/u02/flash_recovery_area/BKT/archivelog/2013_05_13/o1_mf_1_95_8s2qovq1_.arc'

no longer needed for this recovery

ORA-00279: change 1294577 generated at05/13/2013 14:44:27 needed for thread 1

ORA-00289: suggestion : /u02/flash_recovery_area/BKT/archivelog/2013_05_13/o1_mf_1_97_8s2qow0z_.arc

ORA-00280: change 1294577 for thread 1 isin sequence #97

ORA-00278: log file'/u02/flash_recovery_area/BKT/archivelog/2013_05_13/o1_mf_1_96_8s2qovsh_.arc'

no longer needed for this recovery

ORA-00279: change 1294580 generated at05/13/2013 14:44:27 needed for thread 1

ORA-00289: suggestion :/u02/flash_recovery_area/BKT/archivelog/2013_05_13/o1_mf_1_98_8s2qs51d_.arc

ORA-00280: change 1294580 for thread 1 isin sequence #98

ORA-00278: log file '/u02/flash_recovery_area/BKT/archivelog/2013_05_13/o1_mf_1_97_8s2qow0z_.arc'

no longer needed for this recovery

ORA-00279: change 1294620 generated at05/13/2013 14:46:13 needed for thread 1

ORA-00289: suggestion :/u02/flash_recovery_area/BKT/archivelog/2013_05_13/o1_mf_1_99_8s2qs62m_.arc

ORA-00280: change 1294620 for thread 1 isin sequence #99

ORA-00278: log file'/u02/flash_recovery_area/BKT/archivelog/2013_05_13/o1_mf_1_98_8s2qs51d_.arc'

no longer needed for this recovery

ORA-00279: change 1294623 generated at05/13/2013 14:46:14 needed for thread 1

ORA-00289: suggestion :/u02/flash_recovery_area/BKT/archivelog/2013_05_13/o1_mf_1_100_%u_.arc

ORA-00280: change 1294623 for thread 1 isin sequence #100

ORA-00278: log file'/u02/flash_recovery_area/BKT/archivelog/2013_05_13/o1_mf_1_99_8s2qs62m_.arc'

no longer needed for this recovery

ORA-00308: cannot open archived log

'/u02/flash_recovery_area/BKT/archivelog/2013_05_13/o1_mf_1_100_%u_.arc'

ORA-27037: unable to obtain file status

Linux-x86_64 Error: 2: No such file ordirectory

Additional information: 3

[oracle@master 2013_05_13]$ ls -ltr

total 31744

-rw-r----- 1 oracle oinstall    1024 May 13 14:44 o1_mf_1_94_8s2qoqjj_.arc

-rw-r----- 1 oracle oinstall 32441344 May13 14:44 o1_mf_1_93_8s2qoprf_.arc

-rw-r----- 1 oracle oinstall    1024 May 13 14:44 o1_mf_1_96_8s2qovsh_.arc

-rw-r----- 1 oracle oinstall    3072 May 13 14:44 o1_mf_1_95_8s2qovq1_.arc

-rw-r----- 1 oracle oinstall    1024 May 13 14:44 o1_mf_1_97_8s2qow0z_.arc

-rw-r----- 1 oracle oinstall    1536 May 13 14:46 o1_mf_1_98_8s2qs51d_.arc

-rw-r----- 1 oracle oinstall    1024 May 13 14:46 o1_mf_1_99_8s2qs62m_.arc

idle> select sequence# , group# , statusfrom v$log ;

 SEQUENCE#    GROUP# STATUS

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

       91          1 INACTIVE

       93          3 CURRENT

       92          2 INACTIVE

idle> select group#,member fromv$logfile ;

   GROUP# MEMBER

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

        2 /u02/oradat/bkt/redo02.log

        1 /u02/oradat/bkt/redo01.log

        3 /u02/oradat/bkt/redo03.log

sys@BKT> recover database using backupcontrolfile ;

ORA-00279: change 1294623 generated at05/13/2013 14:46:14 needed for thread 1

ORA-00289: suggestion :/u02/flash_recovery_area/BKT/archivelog/2013_05_13/o1_mf_1_100_%u_.arc

ORA-00280: change 1294623 for thread 1 isin sequence #100

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

/u02/oradat/bkt/redo03.log

ORA-00310: archived log contains sequence99; sequence 100 required

ORA-00334: archived log:'/u02/oradat/bkt/redo03.log'

sys@BKT> recover database using backupcontrolfile ;

ORA-00279: change 1294623 generated at 05/13/201314:46:14 needed for thread 1

ORA-00289: suggestion :/u02/flash_recovery_area/BKT/archivelog/2013_05_13/o1_mf_1_100_%u_.arc

ORA-00280: change 1294623 for thread 1 isin sequence #100

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

/u02/oradat/bkt/redo01.log

Log applied.

Media recovery complete.

sys@BKT> alter database open resetlogs ;

Database altered.

因为使用了备份的控制文件，所以必须resetlogs

无备份直接重建控制文件

sys@BKT> show parameter control

NAME                                TYPE        VALUE

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

control_file_record_keep_time        integer    7

control_files                        string      /u02/oradat/bkt/control01.ctl,/u02/flash_recovery_area/bkt/control02.ctl

[root@master ~]# rm /u02/oradat/bkt/control01.ctl

rm: remove regular file`/u02/oradat/bkt/control01.ctl'? y

[root@master ~]# rm/u02/flash_recovery_area/bkt/control02.ctl

rm: remove regular file`/u02/flash_recovery_area/bkt/control02.ctl'? y

sys@BKT> alter system switch logfile ;

System altered.

sys@BKT> alter system archive logcurrent ;

System altered.

看来不是立刻写入控制文件中。

Errors in file/u01/apps/oracle/diag/rdbms/bkt/bkt/trace/bkt_m000_6382.trc:

ORA-00210: cannot open the specified controlfile

ORA-00202: control file:'/u02/oradat/bkt/control01.ctl'

ORA-27041: unable to open file

Linux-x86_64 Error: 2: No such file ordirectory

Additional information: 3

sys@BKT> create tablespace test1datafile '/u02/oradat/bkt/test1_01.dbf' size 10m ;

Tablespace created.

如果及时的发现数据库未关闭，可以使用下面的命令将重建控制文件的脚本输出到trace文件中，方便后面的控制文件重建操作。

sys@BKT> alter database backupcontrolfile to trace ;

Database altered.

alter :

Backup controlfile written to trace file/u01/apps/oracle/diag/rdbms/bkt/bkt/trace/bkt_ora_5933.trc

Completed: alter database backupcontrolfile to trace

如果没有及时的发现已经丢失了所有的控制文件，这里重建控制文件需要知道一些额外的信息，包括redolog的位置、数据文件的位置还有字符集。（这里也可以使用snapshot控制文件来生成trace文件）

sys@BKT> shutdown immediate

Database closed.

ORA-00210: cannot open the specifiedcontrol file

ORA-00202: control file:'/u02/oradat/bkt/control01.ctl'

ORA-27041: unable to open file

Linux-x86_64 Error: 2: No such file ordirectory

Additional information: 3

sys@BKT> shutdown abort ;

sys@BKT> alter database mount ;

alter database mount

*

ERROR at line 1:

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

如果这里重做日志没有损坏，则可以以noresetlogs的方式重建控制文件

CREATE CONTROLFILE REUSE DATABASE"BKT" NORESETLOGS  ARCHIVELOG

   MAXLOGFILES 16

   MAXLOGMEMBERS 3

   MAXDATAFILES 100

   MAXINSTANCES 8

   MAXLOGHISTORY 292

LOGFILE

 GROUP 1 '/u02/oradat/bkt/redo01.log' SIZE 50M BLOCKSIZE 512,

 GROUP 2 '/u02/oradat/bkt/redo02.log' SIZE 50M BLOCKSIZE 512,

 GROUP 3 '/u02/oradat/bkt/redo03.log' SIZE 50M BLOCKSIZE 512

-- STANDBY LOGFILE

DATAFILE

 '/u02/oradat/bkt/system01.dbf',

 '/u02/oradat/bkt/sysaux01.dbf',

 '/u02/oradat/bkt/undotbs01.dbf',

 '/u02/oradat/bkt/test1_01.dbf',

 '/u02/oradat/bkt/users01.dbf',

 '/u02/oradat/bkt/example01.dbf'

CHARACTER SET ZHS16GBK

;

这里改变了数据文件的位置

sys@BKT> CREATE CONTROLFILE REUSEDATABASE "BKT" NORESETLOGS ARCHIVELOG

 2      MAXLOGFILES 16

 3      MAXLOGMEMBERS 3

 4      MAXDATAFILES 100

 5      MAXINSTANCES 8

 6      MAXLOGHISTORY 292

 7  LOGFILE

 8    GROUP 1'/u02/oradat/bkt/redo01.log'  SIZE 50MBLOCKSIZE 512,

 9    GROUP 2'/u02/oradat/bkt/redo02.log'  SIZE 50MBLOCKSIZE 512,

 10   GROUP 3 '/u02/oradat/bkt/redo03.log' SIZE 50M BLOCKSIZE 512

 11  --STANDBY LOGFILE

 12 DATAFILE

 13   '/u02/oradat/bkt/system01.dbf',

 14   '/u02/oradat/bkt/sysaux01.dbf',

 15   '/u02/oradat/bkt/undotbs01.dbf',

 16   '/u02/oradat/bkt/test1_01.dbf',

 17   '/u02/oradat/bkt/users01.dbf',

 18   '/u02/oradat/bkt/example01.dbf'

 19 CHARACTER SET ZHS16GBK

 20  ;

Control file created.

sys@BKT> select status from v$instance ;

STATUS

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

MOUNTED

也可能需要手动的recover一下

recover database

sys@BKT> alter database open ;

Database altered.

添加临时文件

sys@BKT> ALTER TABLESPACE TEMP ADDTEMPFILE '/u02/oradat/bkt/temp01.dbf';

RMAN> list backup ;

specification does not match any backup inthe repository

RMAN> catalog db_recovery_file_dest ;

RMAN> list backup ;

List of Backup Sets

===================

BS Key Type LV Size      Device TypeElapsed Time Completion Time

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

1      Full    1.07G      DISK        00:00:00    18-APR-13     

       BP Key: 1  Status: AVAILABLE  Compressed: NO  Tag: TAG20130418T223159

       Piece Name:/u02/flash_recovery_area/BKT/backupset/2013_04_18/o1_mf_nnndf_TAG20130418T223159_8q00yzs1_.bkp

 List of Datafiles in backup set 1

 File LV Type Ckp SCN    CkpTime  Name

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

 1      Full 1095500    18-APR-13 /u02/oradat/bkt/system01.dbf

 2      Full 1095500    18-APR-13 /u02/oradat/bkt/sysaux01.dbf

 3      Full 1095500    18-APR-13 /u02/oradat/bkt/undotbs01.dbf

  4       Full 1095500    18-APR-13 /u02/oradat/bkt/users01.dbf

 5      Full 1095500    18-APR-13 /u02/oradat/bkt/example01.dbf

BS Key Type LV Size      Device TypeElapsed Time Completion Time

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

2      Full    9.36M      DISK        00:00:00    18-APR-13     

       BP Key: 2  Status: AVAILABLE  Compressed: NO  Tag: TAG20130418T223159

       Piece Name:/u02/flash_recovery_area/BKT/backupset/2013_04_18/o1_mf_ncsnf_TAG20130418T223159_8q016gmf_.bkp

 SPFILE Included: Modification time: 18-APR-13

 SPFILE db_unique_name: BKT

 Control File Included: Ckp SCN: 1095500      Ckp time: 18-APR-13

RMAN> list incarnation

2> ;

List of Database Incarnations

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

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

1      1      BKT      536381303        CURRENT 995548    18-APR-13

RMAN>

Continue applying redo log files until thelast log has been applied to the restored

data files, then cancel recovery byexecuting the following command:

CANCEL

The database indicates whether recovery issuccessful. If you cancel before all the

data files have been recovered to a consistentSCN and then try to open the

database, then you get an ORA-1113error ifmore recovery is necessary. You can

query V$RECOVER_FILEto determine whethermore recovery is needed, or if a

backup of a data file was notrestoredbefore starting incomplete recovery.

无备份使用snap控制文件

RMAN> show all

2> ;

using target database control file insteadof recovery catalog

RMAN configuration parameters for databasewith db_unique_name BKT are:

CONFIGURE RETENTION POLICY TO REDUNDANCY 1;# default

CONFIGURE BACKUP OPTIMIZATION OFF; #default

CONFIGURE DEFAULT DEVICE TYPE TO DISK; #default

CONFIGURE CONTROLFILE AUTOBACKUP OFF; #default

CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FORDEVICE TYPE DISK TO '%F'; # default

CONFIGURE DEVICE TYPE DISK PARALLELISM 1BACKUP TYPE TO BACKUPSET; # default

CONFIGURE DATAFILE BACKUP COPIES FOR DEVICETYPE DISK TO 1; # default

CONFIGURE ARCHIVELOG BACKUP COPIES FORDEVICE TYPE DISK TO 1; # default

CONFIGURE MAXSETSIZE TO UNLIMITED; # default

CONFIGURE ENCRYPTION FOR DATABASE OFF; #default

CONFIGURE ENCRYPTION ALGORITHM 'AES128'; #default

CONFIGURE COMPRESSION ALGORITHM 'BASIC' ASOF RELEASE 'DEFAULT' OPTIMIZE FOR LOAD TRUE ; # default

CONFIGURE ARCHIVELOG DELETION POLICY TONONE; # default

CONFIGURE SNAPSHOT CONTROLFILE NAME TO'/u01/apps/oracle/product/11gr2/db_1/dbs/snapcf_bkt.f'; # default

删除后alert日志报错：

Errors in file/u01/apps/oracle/diag/rdbms/bkt/bkt/trace/bkt_m000_15066.trc:

ORA-00210: cannot open the specifiedcontrol file

ORA-00202: control file:'/u02/oradat/bkt/control01.ctl'

ORA-27041: unable to open file

Linux-x86_64 Error: 2: No such file ordirectory

Additional information: 3

sys@BKT> shutdown immediate

Database closed.

ORA-00210: cannot open the specifiedcontrol file

ORA-00202: control file:'/u02/oradat/bkt/control01.ctl'

ORA-27041: unable to open file

Linux-x86_64 Error: 2: No such file ordirectory

Additional information: 3

sys@BKT> shutdown abort ;

ORACLE instance shut down.

sys@BKT> startup

ORACLE instance started.

Total System Global Area  417546240 bytes

Fixed Size                  2228944 bytes

Variable Size            322964784 bytes

Database Buffers          83886080 bytes

Redo Buffers                8466432 bytes

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

[root@master ~]# cp/u01/apps/oracle/product/11gr2/db_1/dbs/snapcf_bkt.f/u02/oradat/bkt/control01.ctl

[root@master ~]# cp/u01/apps/oracle/product/11gr2/db_1/dbs/snapcf_bkt.f/u02/flash_recovery_area/bkt/control02.ctl

[root@master ~]# chown oracle:oinstall/u02/oradat/bkt/control01.ctl

[root@master ~]# chown oracle:oinstall/u02/flash_recovery_area/bkt/control02.ctl

sys@BKT> recover database using backupcontrolfile ;

ORA-00279: change 1266893 generated at05/09/2013 03:27:56 needed for thread 1

ORA-00289: suggestion :/u02/flash_recovery_area/BKT/archivelog/2013_05_08/o1_mf_1_28_%u_.arc

ORA-00280: change 1266893 for thread 1 isin sequence #28

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

因为是老的controlfile，所以sequence都是老旧的，

dle> select group#,sequence#,status fromv$log ;

   GROUP#  SEQUENCE# STATUS

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

        1        25 INACTIVE

        3        24 INACTIVE

        2        26 CURRENT

idle> archive log list ;

Database log mode              Archive Mode

Automatic archival            Enabled

Archive destination            USE_DB_RECOVERY_FILE_DEST

Oldest online log sequence    24

Next log sequence to archive  26

Current log sequence          26

查看相应的目录

[oracle@master ~]$ ll/u02/flash_recovery_area/BKT/archivelog/2013_05_08/

total 62224

-rw-r----- 1 oracle oinstall  177152 May 7 12:10 o1_mf_1_10_8rlng20m_.arc

-rw-r----- 1 oracle oinstall    1024 May 7 12:12 o1_mf_1_11_8rlnkd1g_.arc

-rw-r----- 1 oracle oinstall    1024 May 7 12:12 o1_mf_1_12_8rlnkfdr_.arc

-rw-r----- 1 oracle oinstall    10752 May 7 12:17 o1_mf_1_13_8rlnt65r_.arc

-rw-r----- 1 oracle oinstall    1024 May 7 12:17 o1_mf_1_14_8rlnt6r7_.arc

-rw-r----- 1 oracle oinstall    1536 May 7 12:17 o1_mf_1_15_8rlnt7b1_.arc

-rw-r----- 1 oracle oinstall    1024 May 7 12:17 o1_mf_1_16_8rlnt7x7_.arc

-rw-r----- 1 oracle oinstall    1024 May 7 12:17 o1_mf_1_17_8rlnt8g7_.arc

-rw-r----- 1 oracle oinstall    1024 May 7 12:17 o1_mf_1_18_8rlnt8wk_.arc

-rw-r----- 1 oracle oinstall    1024 May 7 12:17 o1_mf_1_19_8rlnt9bn_.arc

-rw-r----- 1 oracle oinstall    1024 May 7 12:17 o1_mf_1_20_8rlnt9p8_.arc

-rw-r----- 1 oracle oinstall  184832 May 7 12:30 o1_mf_1_21_8rlokyy2_.arc

-rw-r----- 1 oracle oinstall  259584 May 7 12:55 o1_mf_1_22_8rlq1j4j_.arc

-rw-r----- 1 oracle oinstall    1024 May 7 12:55 o1_mf_1_23_8rlq1tck_.arc

-rw-r----- 1 oracle oinstall    1024 May 7 13:29 o1_mf_1_23_8rls13lv_.arc

-rw-r----- 1 oracle oinstall    2048 May 7 12:56 o1_mf_1_24_8rlq3pxx_.arc

-rw-r----- 1 oracle oinstall    2048 May 7 13:29 o1_mf_1_24_8rls13lj_.arc

-rw-r----- 1 oracle oinstall  243712 May 7 13:29 o1_mf_1_25_8rls13pd_.arc

-rw-r----- 1 oracle oinstall 42203648May  8 07:01 o1_mf_1_26_8rnpoljr_.arc

-rw-r----- 1 oracle oinstall 10391552May  8 12:28 o1_mf_1_27_8ro9sxgq_.arc

-rw-r----- 1 oracle oinstall 10078720May  7 11:58 o1_mf_1_9_8rlmpf3o_.arc

idle> select * from v$logfile ;

   GROUP# STATUS  TYPE    MEMBER                         IS_

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

        1 STALE  ONLINE  /u02/oradat/bkt/redo01.log    NO

        3 STALE  ONLINE  /u02/oradat/bkt/redo03.log    NO

        2        ONLINE  /u02/oradat/bkt/redo02.log    NO

顺序3、1、2

sys@BKT> recover database using backupcontrolfile ;

ORA-00279: change 1266893 generated at05/09/2013 03:27:56 needed for thread 1

ORA-00289: suggestion :/u02/flash_recovery_area/BKT/archivelog/2013_05_08/o1_mf_1_28_%u_.arc

ORA-00280: change 1266893 for thread 1 isin sequence #28

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

 /u02/oradat/bkt/redo03.log

ORA-00310: archived log contains sequence27; sequence 28 required

ORA-00334: archived log:'/u02/oradat/bkt/redo03.log'

这里必须resetlogs

sys@BKT> alter database open noresetlogs;

alter database open noresetlogs

*

ERROR at line 1:

ORA-01588: must use RESETLOGS option fordatabase open

sys@BKT> alter database open resetlogs ;

sys@BKT> ALTER TABLESPACE TEMP ADDTEMPFILE '/u02/oradat/bkt/temp01.dbf' REUSE;

Tablespace altered.

RMAN> catalog db_recovery_file_dest ;

using target database control file insteadof recovery catalog

searching for all files in the recoveryarea

：


    
 
 
 
本站(WWW.)旨在分享和传播互联网科技相关的资讯和技术，将尽最大努力为读者提供更好的信息聚合和浏览方式。
本站(WWW.)站内文章除注明原创外，均为转载、整理或搜集自网络。欢迎任何形式的转载，转载请注明出处。












  • 相关文章推荐
  • Oracle数据库恢复后心得
  • 关于系统重装后Oracle数据库完全恢复的解决办法
  • oracle drop table(表)数据恢复方法
  • Oracle数据库备份和恢复工具 RMan
  • oracle备份恢复的具体方法
  • oracle中误删除表后恢复语句(FLASHBACK)
  • oracle冷备份恢复和oracle异机恢复使用方法
  • 在Oracle 10gR2中设定指定的恢复点实现轻松闪回
  • Oracle11g备份和恢复功能的提高
  • 实战Oracle数据库备份与恢复
  • Oracle数据库冷备份的异地恢复
  • 详解通过Backup Exec来实施Oracle灾难恢复
  • Oracle的恢复管理器及DBMS_JOB包分析
  • Oracle数据库文件恢复以及备份思路
  • Oracle数据库备份恢复最佳实践
  • oracle如何恢复被覆盖的存储过程
  • rman恢复方案和oracle异机恢复
  • oracle数据库创建备份与恢复脚本整理
  • ORACLE 数据库RMAN备份恢复
  • 一次误操作引起的Oracle数据库大恢复
  • Oracle 12c发布简单介绍及官方下载地址
  • 在linux下安装oracle,如何设置让oracle自动启动！也就是让oracle那个服务自动启动，不是手动的
  • oracle 11g最新版官方下载地址
  • 请问su oracle 和su - oracle有什么不同？
  • Oracle 数据库(oracle Database)Select 多表关联查询方式
  • 虚拟机装Oracle R12与Oracle10g
  • Oracle数据库(Oracle Database)体系结构及基本组成介绍
  • Oracle 数据库开发工具 Oracle SQL Developer
  • 如何设置让Oracle SQL Developer显示的时间包含时分秒
  • Oracle EBS R12 支持 Oracle Database 11g
  • Oracle 10g和Oracle 11g网格技术介绍


    • 站内导航:


    特别声明:169IT网站部分信息来自互联网,如果侵犯您的权利,请及时告知,本站将立即删除！

    ©2012-2021,,E-mail:www_#163.com(请将#改为@)

    浙ICP备11055608号-3