这里只说控制文件全部损坏,如果只损坏一部分控制文件,只要把正常的控制文件复制过去就可以了
一.有以前控制文件的备份
1.把以前备份的控制文件拷贝到相应的控制文件目录
cp control01.ctl.bak control01.ctl
cp control01.ctl /u01/app/oracle/admin/center/control03.ctl
cp control01.ctl /u02/oradata/flash_recovery_area/center/control02.ctl
如果是rman备份的控制文件
rman> restore controlfile from autobackup;
启动时报错
SYS@center> startup
ORACLE instance started.
Total System Global Area 359124992 bytes
Fixed Size 2228304 bytes
Variable Size 301993904 bytes
Database Buffers 50331648 bytes
Redo Buffers 4571136 bytes
Database mounted.
ORA-01122: database file 1 failed verification check
ORA-01110: data file 1: '/u02/oradata/center/system.259.854978309'
ORA-01207: file is more recent than control file - old control file
查看数据文件头和数据文件的scn号
数据文件头scn(记录在数据文件中):
SYS@center> select file#,checkpoint_change# from v$datafile_header;
FILE# CHECKPOINT_CHANGE#
---------- ------------------
1 2581304
2 2581304
3 2581304
4 2581304
5 2581304
6 2581304
7 2581304
7 rows selected.
数据文件scn(记录在控制文件中):
SYS@center> select file#,checkpoint_change# from v$datafile;
FILE# CHECKPOINT_CHANGE#
---------- ------------------
1 2567475
2 2567475
3 2567475
4 2567475
5 2567475
6 2567475
7 2567475
7 rows selected.
可以看出数据文件scn号要比控制文件中记录的scn号大,说明控制文件是久的
2.这时候只要执行recover database using backup controlfile;即可。
SYS@center> recover database using backup controlfile;
ORA-00279: change 2580994 generated at 09/22/2014 08:00:50 needed for thread 1
ORA-00289: suggestion : /u02/oradata/center/archive/standy/1_71_857903213.dbf
ORA-00280: change 2580994 for thread 1 is in sequence #71
Specify log: {=suggested | filename | AUTO | CANCEL}
auto
ORA-00279: change 2581231 generated at 09/22/2014 11:38:12 needed for thread 1
ORA-00289: suggestion : /u02/oradata/center/archive/standy/1_72_857903213.dbf
ORA-00280: change 2581231 for thread 1 is in sequence #72
ORA-00278: log file '/u02/oradata/center/archive/standy/1_71_857903213.dbf' no
longer needed for this recovery
ORA-00279: change 2581238 generated at 09/22/2014 11:38:16 needed for thread 1
ORA-00289: suggestion : /u02/oradata/center/archive/standy/1_73_857903213.dbf
ORA-00280: change 2581238 for thread 1 is in sequence #73
ORA-00278: log file '/u02/oradata/center/archive/standy/1_72_857903213.dbf' no
longer needed for this recovery
ORA-00279: change 2581241 generated at 09/22/2014 11:38:17 needed for thread 1
ORA-00289: suggestion : /u02/oradata/center/archive/standy/1_74_857903213.dbf
ORA-00280: change 2581241 for thread 1 is in sequence #74
ORA-00278: log file '/u02/oradata/center/archive/standy/1_73_857903213.dbf' no
longer needed for this recovery
ORA-00308: cannot open archived log
'/u02/oradata/center/archive/standy/1_74_857903213.dbf'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
这里恢复的时候有一个报错
ORA-00308: cannot open archived log
'/u02/oradata/center/archive/standy/1_74_857903213.dbf'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
查看系统最近的归档知道73号,74好应该是还没有归档的redo文件。
[oracle@cheat standy]$ ll -t
总用量 2702960
-rw-r----- 1 oracle oinstall 1024 9月 22 11:38 1_73_857903213.dbf
-rw-r----- 1 oracle oinstall 4096 9月 22 11:38 1_72_857903213.dbf
3.再次执行recover database using backup controlfile;手动指定redo文件位置
SYS@center> recover database using backup controlfile;
ORA-00279: change 2581241 generated at 09/22/2014 11:38:17 needed for thread 1
ORA-00289: suggestion : /u02/oradata/center/archive/standy/1_74_857903213.dbf
ORA-00280: change 2581241 for thread 1 is in sequence #74
Specify log: {=suggested | filename | AUTO | CANCEL}
/u02/oradata/center/group_1.257.854978301
ORA-00310: archived log contains sequence 73; sequence 74 required
ORA-00334: archived log: '/u02/oradata/center/group_1.257.854978301'
可以看出group_1的日志序列是73不是74,继续执行。
SYS@center> recover database using backup controlfile;
ORA-00279: change 2581241 generated at 09/22/2014 11:38:17 needed for thread 1
ORA-00289: suggestion : /u02/oradata/center/archive/standy/1_74_857903213.dbf
ORA-00280: change 2581241 for thread 1 is in sequence #74
Specify log: {=suggested | filename | AUTO | CANCEL}
/u02/oradata/center/group_2.258.854978307
Log applied.
Media recovery complete.
4.用RESETLOGS选项打开数据库
SYS@center> alter database open;
alter database open
*
ERROR at line 1:
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open
SYS@center> alter database open RESETLOGS;
Database altered.
查看数据文件头和数据文件的scn号
SYS@center> select file#,checkpoint_change# from v$datafile_header;
FILE# CHECKPOINT_CHANGE#
---------- ------------------
1 2581309
2 2581309
3 2581309
4 2581309
5 2581309
6 2581309
7 2581309
7 rows selected.
SYS@center> select file#,checkpoint_change# from v$datafile;
FILE# CHECKPOINT_CHANGE#
---------- ------------------
1 2581309
2 2581309
3 2581309
4 2581309
5 2581309
6 2581309
7 2581309
7 rows selected.
现在两个scn号一致了。
二.没有以前控制文件的备份
查看oracle日志文件中是否存在控制文件的创建语法
如果存在用创建语法重新创建即可:
SYS@center> startup nomount
SYS@center> CREATE CONTROLFILE REUSE DATABASE "CENTER" NORESETLOGS FORCE LOGGING ARCHIVELOG
2 MAXLOGFILES 192
3 MAXLOGMEMBERS 3
4 MAXDATAFILES 1024
5 MAXINSTANCES 32
6 MAXLOGHISTORY 292
7 LOGFILE
8 GROUP 1 '/u02/oradata/center/group_1.257.854978301' SIZE 50M BLOCKSIZE 512,
9 GROUP 2 '/u02/oradata/center/group_2.258.854978307' SIZE 50M BLOCKSIZE 512
10 -- STANDBY LOGFILE
11 DATAFILE
12 '/u02/oradata/center/system.259.854978309',
13 '/u02/oradata/center/sysaux.260.854978321',
14 '/u02/oradata/center/undotbs1.261.854978329',
15 '/u02/oradata/center/undotbs2.263.854978391',
16 '/u02/oradata/center/users.264.854978395',
17 '/u02/oradata/center/test.dbf',
18 '/home/oracle/datafile/myth01.dbf'
19 CHARACTER SET ZHS16GBK
20 ;
Control file created.
SYS@center> alter database open;
Database altered.
如果不存在根据上面创建控制文件的格式,按照具体的文件位置自己写个创建语法即可;
平时可以经常使用alter database backup controlfile to trace; 生成控制文件创建语法。
一.有以前控制文件的备份
1.把以前备份的控制文件拷贝到相应的控制文件目录
cp control01.ctl.bak control01.ctl
cp control01.ctl /u01/app/oracle/admin/center/control03.ctl
cp control01.ctl /u02/oradata/flash_recovery_area/center/control02.ctl
如果是rman备份的控制文件
rman> restore controlfile from autobackup;
启动时报错
SYS@center> startup
ORACLE instance started.
Total System Global Area 359124992 bytes
Fixed Size 2228304 bytes
Variable Size 301993904 bytes
Database Buffers 50331648 bytes
Redo Buffers 4571136 bytes
Database mounted.
ORA-01122: database file 1 failed verification check
ORA-01110: data file 1: '/u02/oradata/center/system.259.854978309'
ORA-01207: file is more recent than control file - old control file
查看数据文件头和数据文件的scn号
数据文件头scn(记录在数据文件中):
SYS@center> select file#,checkpoint_change# from v$datafile_header;
FILE# CHECKPOINT_CHANGE#
---------- ------------------
1 2581304
2 2581304
3 2581304
4 2581304
5 2581304
6 2581304
7 2581304
7 rows selected.
数据文件scn(记录在控制文件中):
SYS@center> select file#,checkpoint_change# from v$datafile;
FILE# CHECKPOINT_CHANGE#
---------- ------------------
1 2567475
2 2567475
3 2567475
4 2567475
5 2567475
6 2567475
7 2567475
7 rows selected.
可以看出数据文件scn号要比控制文件中记录的scn号大,说明控制文件是久的
2.这时候只要执行recover database using backup controlfile;即可。
SYS@center> recover database using backup controlfile;
ORA-00279: change 2580994 generated at 09/22/2014 08:00:50 needed for thread 1
ORA-00289: suggestion : /u02/oradata/center/archive/standy/1_71_857903213.dbf
ORA-00280: change 2580994 for thread 1 is in sequence #71
Specify log: {=suggested | filename | AUTO | CANCEL}
auto
ORA-00279: change 2581231 generated at 09/22/2014 11:38:12 needed for thread 1
ORA-00289: suggestion : /u02/oradata/center/archive/standy/1_72_857903213.dbf
ORA-00280: change 2581231 for thread 1 is in sequence #72
ORA-00278: log file '/u02/oradata/center/archive/standy/1_71_857903213.dbf' no
longer needed for this recovery
ORA-00279: change 2581238 generated at 09/22/2014 11:38:16 needed for thread 1
ORA-00289: suggestion : /u02/oradata/center/archive/standy/1_73_857903213.dbf
ORA-00280: change 2581238 for thread 1 is in sequence #73
ORA-00278: log file '/u02/oradata/center/archive/standy/1_72_857903213.dbf' no
longer needed for this recovery
ORA-00279: change 2581241 generated at 09/22/2014 11:38:17 needed for thread 1
ORA-00289: suggestion : /u02/oradata/center/archive/standy/1_74_857903213.dbf
ORA-00280: change 2581241 for thread 1 is in sequence #74
ORA-00278: log file '/u02/oradata/center/archive/standy/1_73_857903213.dbf' no
longer needed for this recovery
ORA-00308: cannot open archived log
'/u02/oradata/center/archive/standy/1_74_857903213.dbf'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
这里恢复的时候有一个报错
ORA-00308: cannot open archived log
'/u02/oradata/center/archive/standy/1_74_857903213.dbf'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
查看系统最近的归档知道73号,74好应该是还没有归档的redo文件。
[oracle@cheat standy]$ ll -t
总用量 2702960
-rw-r----- 1 oracle oinstall 1024 9月 22 11:38 1_73_857903213.dbf
-rw-r----- 1 oracle oinstall 4096 9月 22 11:38 1_72_857903213.dbf
3.再次执行recover database using backup controlfile;手动指定redo文件位置
SYS@center> recover database using backup controlfile;
ORA-00279: change 2581241 generated at 09/22/2014 11:38:17 needed for thread 1
ORA-00289: suggestion : /u02/oradata/center/archive/standy/1_74_857903213.dbf
ORA-00280: change 2581241 for thread 1 is in sequence #74
Specify log: {=suggested | filename | AUTO | CANCEL}
/u02/oradata/center/group_1.257.854978301
ORA-00310: archived log contains sequence 73; sequence 74 required
ORA-00334: archived log: '/u02/oradata/center/group_1.257.854978301'
可以看出group_1的日志序列是73不是74,继续执行。
SYS@center> recover database using backup controlfile;
ORA-00279: change 2581241 generated at 09/22/2014 11:38:17 needed for thread 1
ORA-00289: suggestion : /u02/oradata/center/archive/standy/1_74_857903213.dbf
ORA-00280: change 2581241 for thread 1 is in sequence #74
Specify log: {=suggested | filename | AUTO | CANCEL}
/u02/oradata/center/group_2.258.854978307
Log applied.
Media recovery complete.
4.用RESETLOGS选项打开数据库
SYS@center> alter database open;
alter database open
*
ERROR at line 1:
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open
SYS@center> alter database open RESETLOGS;
Database altered.
查看数据文件头和数据文件的scn号
SYS@center> select file#,checkpoint_change# from v$datafile_header;
FILE# CHECKPOINT_CHANGE#
---------- ------------------
1 2581309
2 2581309
3 2581309
4 2581309
5 2581309
6 2581309
7 2581309
7 rows selected.
SYS@center> select file#,checkpoint_change# from v$datafile;
FILE# CHECKPOINT_CHANGE#
---------- ------------------
1 2581309
2 2581309
3 2581309
4 2581309
5 2581309
6 2581309
7 2581309
7 rows selected.
现在两个scn号一致了。
二.没有以前控制文件的备份
查看oracle日志文件中是否存在控制文件的创建语法
如果存在用创建语法重新创建即可:
SYS@center> startup nomount
SYS@center> CREATE CONTROLFILE REUSE DATABASE "CENTER" NORESETLOGS FORCE LOGGING ARCHIVELOG
2 MAXLOGFILES 192
3 MAXLOGMEMBERS 3
4 MAXDATAFILES 1024
5 MAXINSTANCES 32
6 MAXLOGHISTORY 292
7 LOGFILE
8 GROUP 1 '/u02/oradata/center/group_1.257.854978301' SIZE 50M BLOCKSIZE 512,
9 GROUP 2 '/u02/oradata/center/group_2.258.854978307' SIZE 50M BLOCKSIZE 512
10 -- STANDBY LOGFILE
11 DATAFILE
12 '/u02/oradata/center/system.259.854978309',
13 '/u02/oradata/center/sysaux.260.854978321',
14 '/u02/oradata/center/undotbs1.261.854978329',
15 '/u02/oradata/center/undotbs2.263.854978391',
16 '/u02/oradata/center/users.264.854978395',
17 '/u02/oradata/center/test.dbf',
18 '/home/oracle/datafile/myth01.dbf'
19 CHARACTER SET ZHS16GBK
20 ;
Control file created.
SYS@center> alter database open;
Database altered.
如果不存在根据上面创建控制文件的格式,按照具体的文件位置自己写个创建语法即可;
平时可以经常使用alter database backup controlfile to trace; 生成控制文件创建语法。
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/26794255/viewspace-1277059/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/26794255/viewspace-1277059/