共计 26049 个字符，预计需要花费 66 分钟才能阅读完成。
下文介绍了 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-01207: file is more recentthan control file – old control file>>>
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: {<RET>=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: {<RET>=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: {<RET>=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: {<RET>=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: {<RET>=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: {<RET>=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: {<RET>=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
更多 Oracle 相关信息见 Oracle 专题页面 http://www.linuxidc.com/topicnews.aspx?tid=12
本文永久更新链接地址 ：http://www.linuxidc.com/Linux/2016-08/134075.htm
