控制文件的备份:
1.文本格式:alter database backup controlfile to trace as '/home/oracle/ctl.bak';
2.二进制格式:alter database backup controlfile to '/home/oracle/ctl.bak';
3.使用rman备份:backup current controlfile;
控制文件的恢复方法:
1.将镜像的文件恢复;
2.使用老的控制文件恢复;
3.建立新的控制文件,使用noresetlogs(日志是好的)
4.建立新的控制文件,使用resetlogs(日志损坏)
实验1:使用noresetlogs创建控制文件
alter database backup controlfile to '/u01/bak/ct.ctl';--备成二进制的
update scott.emp set sal=sal+1;
commit;
update scott.emp set sal=sal+1;
select name from v$controlfile;
! cp /etc/passwd /stu200/control03.ctl--损坏控制文件,但在内存中还能查看
alter system checkpoint;--使彻底损坏,内存也无法查看
! ps aux | grep ora_//查看损坏的状态时没有实例了
conn / as sysdba
startup--会报错
! cp /u01/bak/ct.ctl /stu200/control01.ctl
! cp /u01/bak/ct.ctl /stu200/control02.ctl
! cp /u01/bak/ct.ctl /stu200/control03.ctl
alter database mount;
查看SCN,此时CLT_SCN的值较低,LAST_SCN没有值,DBF_SCN日志切换时完成时产生的值,所以它的值不是最新的
select a.name,
a.checkpoint_change# ctl_scn,
a.last_change# last_scn,
b.checkpoint_change# dbf_scn
from v$datafile a,v$datafile_header b
where a.name=b.name;
(如果dbf_scn=0说明文件头无法读取;ctl_scn=值,文件头好的时候,写文件头的最后一次;last_scn=值,代表控制文件发现数据文件损坏的时刻)
recover database using backup controlfile;
回车,若最后回车报错,重新执行下面的命令,手动来提交CURRENT的日志
recover database using backup controlfile;
select name from v$logfile;——查看当前日志
此时通过redo来提供CURRENT的日志
alter database open;打开失败,
重建控制文件 ,不用resetlogs来打开:
alter database backup controlfile to trace as '/u01/bak/ctl.sql';
shut immediate
startup nomount
创建控制文件用noresetlogs创建
查看SCN,LAST_SCN没值,
继续恢复:recover datafile;
打开:alter database open;
===============================================================================
实验2:使用resetlogs建立控制文件
先备份控制文件 文本格式
SQL> alter database backup controlfile to trace as '/u01/oracle/ctl.sql';
Database altered.
SQL> select * from v$controlfile;
STATUS NAME IS_ BLOCK_SIZE FILE_SIZE_BLKS
---------- -------------------------------------------------- --- ---------- --------------
/db254/control01.ctl NO 16384 450
/db254/control02.ctl NO 16384 450
/db254/control03.ctl NO 16384 450
SQL> select * from v$logfile;
GROUP# STATUS TYPE MEMBER IS_
---------- ---------- ------- --------------------------------------------- ---
3 ONLINE /db254/redo03.log NO
2 ONLINE /db254/redo02.log NO
1 ONLINE /db254/redo01.log NO
一致停库
SQL> shut immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>
删除redo和control文件
SQL> ! rm /db254/redo*
SQL> ! rm /db254/contro*
SQL>
启动数据库 没有控制文件 只能到nomount 报205
SQL> startup
ORACLE instance started.
Total System Global Area 285212672 bytes
Fixed Size 1218992 bytes
Variable Size 83887696 bytes
Database Buffers 197132288 bytes
Redo Buffers 2973696 bytes
ORA-00205: error in identifying control file, check alert log for more info
SQL>
拿备份的文本中 使用resetlog模式创建控制文件 resetlog会在open时重建redo
CREATE CONTROLFILE REUSE DATABASE "ORA10G" RESETLOGS ARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 292
LOGFILE
GROUP 1 '/db254/redo01.log' SIZE 50M,
GROUP 2 '/db254/redo02.log' SIZE 50M,
GROUP 3 '/db254/redo03.log' SIZE 50M
DATAFILE
'/db254/system01.dbf',
'/db254/undotbs01.dbf',
'/db254/sysaux01.dbf',
'/db254/users01.dbf',
'/db254/mytbs01.dbf',
'/db254/ind_tbs01.dbf',
'/db254/wb.dbf'
CHARACTER SET AL32UTF8
;
Control file created.
SQL> select status from v$instance;
STATUS
----------
MOUNTED
SQL> 控制文件已建好 日志文件会在open时重新创建
SQL> ! ls /db254/control*
/db254/control01.ctl /db254/control02.ctl /db254/control03.ctl
SQL> ! ls /db254/redo*
ls: /db254/redo*: 没有那个文件或目录
SQL>
因为数据文件是一致的.所以不需要恢复
SQL> select name,FUZZY,CHECKPOINT_CHANGE#,CHECKPOINT_COUNT from v$datafile_header;
NAME FUZ CHECKPOINT_CHANGE# CHECKPOINT_COUNT
-------------------------------------------------- --- ------------------ ----------------
/db254/system01.dbf NO 1079164 247
/db254/undotbs01.dbf NO 1079164 207
/db254/sysaux01.dbf NO 1079164 244
/db254/users01.dbf NO 1079164 257
/db254/mytbs01.dbf NO 1079164 120
/db254/ind_tbs01.dbf NO 1079164 54
/db254/wb.dbf NO 1079164 11
7 rows selected.
SQL>
SQL> alter database open resetlogs;
Database altered.
SQL>
SQL> ! ls /db254/redo*
/db254/redo01.log /db254/redo02.log /db254/redo03.log
=================================================================================
实验3:使用旧的控制文件来恢复
场景表述:
归档的数据库
1.有控制文件的二进制文件备份
2.控制文件全部损坏,数据库正在交易崩溃
3.取回老的二进制控制文件
4.能起到mount 但控制文件是以前的备份(旧的),数据文件因为是不一致性停库,需要恢复
5.因为控制文件是旧的 就不能以控制文件内的信息为基准去恢复了.
告诉数据库控制文件是老的,只能以归档和在线日志为准取恢复(不完全恢复)
SQL> alter database backup controlfile to '/u01/oracle/control.bk';
Database altered.
SQL>
产生交易
update scott.emp set sal=sal+1;
commit;
alter system switch logfile;
非一致性停库 或者将所有控制文件破坏
SQL> shut abort
ORACLE instance shut down.
SQL>
SQL>
如果是破坏了控制文件 切换一次日志 数据就会崩溃
SQL> alter system switch logfile;
ERROR:
ORA-03114: not connected to ORACLE
alter system switch logfile
*
ERROR at line 1:
ORA-03113: end-of-file on communication channel
SQL>
SQL> conn / as sysdba
Connected to an idle instance.
SQL> startup
ORACLE instance started.
Total System Global Area 285212672 bytes
Fixed Size 1218992 bytes
Variable Size 113247824 bytes
Database Buffers 167772160 bytes
Redo Buffers 2973696 bytes
ORA-00205: error in identifying control file, check alert log for more info
恢复备份的二进制控制文件
SQL> ! cp /u01/oracle/myctl.ctl /u01/oracle/oradata/db20/control01.ctl
SQL> ! cp /u01/oracle/myctl.ctl /u01/oracle/oradata/db20/control02.ctl
SQL> ! cp /u01/oracle/myctl.ctl /u01/oracle/oradata/db20/control03.ctl
SQL>
SQL> alter database mount;
Database altered.
SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open
在做resetlogs启数据库 也会因为数据文件内部不一致而中止,需要恢复
SQL>
SQL>
SQL> 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: '/db254/system01.dbf'
SQL>
SQL> recover database;
ORA-00283: recovery session canceled due to errors
ORA-01610: recovery using the BACKUP CONTROLFILE option must be done
数据库检测到控制文件和数据文件SCN的对比,发现控制文件是老的
数据库如何发现控制文件是老的?
SQL>
SQL> select name,CHECKPOINT_CHANGE#,LAST_CHANGE# from v$datafile;
NAME CHECKPOINT_CHANGE# LAST_CHANGE#
-------------------------------------------------- ------------------ ------------
/db254/system01.dbf 1079166
/db254/undotbs01.dbf 1079166
/db254/sysaux01.dbf 1079166
/db254/users01.dbf 1079166
/db254/mytbs01.dbf 1079166
/db254/ind_tbs01.dbf 1079166
/db254/wb.dbf 1079166
7 rows selected.
SQL> 控制文件中记录的stopSCN即last_change#是空 说明上一次没有一致性停库
控制文件中的记录比实际文件头中的记录要晚
SQL> select file#,RECOVER,FUZZY,CHECKPOINT_CHANGE# from v$datafile_header;
FILE# REC FUZ CHECKPOINT_CHANGE#
---------- --- --- ------------------
1 YES 1079549
2 YES 1079549
3 YES 1079549
4 YES 1079549
5 YES 1079549
6 YES 1079549
7 YES 1079549
7 rows selected.
SQL>
告诉数据库,控制文件是老
SQL> recover database using backup controlfile;
ORA-00279: change 1079347 generated at 03/06/2011 17:14:34 needed for thread 1
ORA-00289: suggestion : /home/oracle/arc1/1_1_745089274.arc
ORA-00280: change 1079347 for thread 1 is in sequence #1
Specify log: {=suggested | filename | AUTO | CANCEL}
auto
ORA-00279: change 1079526 generated at 03/06/2011 17:20:02 needed for thread 1
ORA-00289: suggestion : /home/oracle/arc1/2_1_745089274.arc
ORA-00280: change 1079526 for thread 1 is in sequence #2
ORA-00278: log file '/home/oracle/arc1/1_1_745089274.arc' no longer needed for this recovery
ORA-00279: change 1079528 generated at 03/06/2011 17:20:03 needed for thread 1
ORA-00289: suggestion : /home/oracle/arc1/3_1_745089274.arc
ORA-00280: change 1079528 for thread 1 is in sequence #3
ORA-00278: log file '/home/oracle/arc1/2_1_745089274.arc' no longer needed for this recovery
ORA-00279: change 1079536 generated at 03/06/2011 17:20:11 needed for thread 1
ORA-00289: suggestion : /home/oracle/arc1/4_1_745089274.arc
ORA-00280: change 1079536 for thread 1 is in sequence #4
ORA-00278: log file '/home/oracle/arc1/3_1_745089274.arc' no longer needed for this recovery
ORA-00279: change 1079538 generated at 03/06/2011 17:20:16 needed for thread 1
ORA-00289: suggestion : /home/oracle/arc1/5_1_745089274.arc
ORA-00280: change 1079538 for thread 1 is in sequence #5
ORA-00278: log file '/home/oracle/arc1/4_1_745089274.arc' no longer needed for this recovery
ORA-00279: change 1079541 generated at 03/06/2011 17:20:20 needed for thread 1
ORA-00289: suggestion : /home/oracle/arc1/6_1_745089274.arc
ORA-00280: change 1079541 for thread 1 is in sequence #6
ORA-00278: log file '/home/oracle/arc1/5_1_745089274.arc' no longer needed for this recovery
ORA-00279: change 1079543 generated at 03/06/2011 17:20:20 needed for thread 1
ORA-00289: suggestion : /home/oracle/arc1/7_1_745089274.arc
ORA-00280: change 1079543 for thread 1 is in sequence #7
ORA-00278: log file '/home/oracle/arc1/6_1_745089274.arc' no longer needed for this recovery
ORA-00279: change 1079545 generated at 03/06/2011 17:20:20 needed for thread 1
ORA-00289: suggestion : /home/oracle/arc1/8_1_745089274.arc
ORA-00280: change 1079545 for thread 1 is in sequence #8
ORA-00278: log file '/home/oracle/arc1/7_1_745089274.arc' no longer needed for this recovery
ORA-00279: change 1079547 generated at 03/06/2011 17:20:25 needed for thread 1
ORA-00289: suggestion : /home/oracle/arc1/9_1_745089274.arc
ORA-00280: change 1079547 for thread 1 is in sequence #9
ORA-00278: log file '/home/oracle/arc1/8_1_745089274.arc' no longer needed for this recovery
ORA-00279: change 1079549 generated at 03/06/2011 17:20:25 needed for thread 1
ORA-00289: suggestion : /home/oracle/arc1/10_1_745089274.arc
ORA-00280: change 1079549 for thread 1 is in sequence #10
ORA-00278: log file '/home/oracle/arc1/9_1_745089274.arc' no longer needed for this recovery
ORA-00308: cannot open archived log '/home/oracle/arc1/10_1_745089274.arc'
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3
SQL>
这个文件还没归档 因为是current组 还没来得及切换日志(切换日志才能归档)
此时可以手动提供redo给它 ---> 可一组组尝试
SQL> recover database using backup controlfile;
ORA-00279: change 1079549 generated at 03/06/2011 17:20:25 needed for thread 1
ORA-00289: suggestion : /home/oracle/arc1/10_1_745089274.arc
ORA-00280: change 1079549 for thread 1 is in sequence #10
Specify log: {=suggested | filename | AUTO | CANCEL}
/db254/redo03.log//手动提供current时刻的redo日志
Log applied.
Media recovery complete.
SQL> alter database open resetlogs;
Database altered.
=======补充========================
create controlfile ...resetlogs;
此方式用在:联机日志损坏了,从数据文件头上获取SCN信息,必须使用alter database open resetlogs来打开数据库,
此时,日志序列会重置,incarnamtion(版本)信息会改变,控制文件中的历史记录全部丢失,对数据库的损伤很大。
create controlfile ....noresetlogs;
此方式中的SCN信息来自于日志中,控制文件中的历史记录全部丢失。
注:
1 创建控制文件的二进制备份
2 产生交易,切换日志--重复多次
3 把控制文件其中之一损坏,则全部都会受到影响
4 这时实例会直接崩溃
5 如果是多元化管理的控制文件,把好的覆盖坏的就能解决。
6 如果所有的控制文件都损坏:
a 可以直接重建
b 有历史的二进制的备份,这时取回备份,复制到control_files参数中的指针位置,写几份就会复制几份
然后在mount下进行恢复:recover database using backup controlfile--这是使用控制文件的备份,不知道备份后数据库运行到哪里,
所以使用redo去恢复( 叫不完全恢复),恢复时只使用归档,不会找redo。
当需要的归档不存在时,那么这个归档实际是current log,需要手动提供你的redo文件,
这时可以直接打开库:alter database open resetlogs;
也可以避免resetlogs打开库,避免的方法:只有把redo全部应用了,才可以是此方法,这时不要打开库,去做控制文件的文本备份,
关闭实例,启动到nomount,使用noresetlogs建立控制文件,建立完成后直接恢复库:recover database;然后:alter database open;
但此时控制文件中的历史丢失,可以使用rman将备份历史和归档历史找回:
rman>catalog start with '文件目录';==>yes 此命令将扫描文件目录下的所有备份文件集的头,将信息注册到控制文件
如果采用resetlog打开数据库,控制文件是新的,原来的rman备份信息都没有了。因为rman的信息在控制文件里,新建控制文件没有rman备份信息
将原来备份可以注册到新的控制文件:catalog start with '/home/oracle/rman_bak/'
1.文本格式:alter database backup controlfile to trace as '/home/oracle/ctl.bak';
2.二进制格式:alter database backup controlfile to '/home/oracle/ctl.bak';
3.使用rman备份:backup current controlfile;
控制文件的恢复方法:
1.将镜像的文件恢复;
2.使用老的控制文件恢复;
3.建立新的控制文件,使用noresetlogs(日志是好的)
4.建立新的控制文件,使用resetlogs(日志损坏)
实验1:使用noresetlogs创建控制文件
alter database backup controlfile to '/u01/bak/ct.ctl';--备成二进制的
update scott.emp set sal=sal+1;
commit;
update scott.emp set sal=sal+1;
select name from v$controlfile;
! cp /etc/passwd /stu200/control03.ctl--损坏控制文件,但在内存中还能查看
alter system checkpoint;--使彻底损坏,内存也无法查看
! ps aux | grep ora_//查看损坏的状态时没有实例了
conn / as sysdba
startup--会报错
! cp /u01/bak/ct.ctl /stu200/control01.ctl
! cp /u01/bak/ct.ctl /stu200/control02.ctl
! cp /u01/bak/ct.ctl /stu200/control03.ctl
alter database mount;
查看SCN,此时CLT_SCN的值较低,LAST_SCN没有值,DBF_SCN日志切换时完成时产生的值,所以它的值不是最新的
select a.name,
a.checkpoint_change# ctl_scn,
a.last_change# last_scn,
b.checkpoint_change# dbf_scn
from v$datafile a,v$datafile_header b
where a.name=b.name;
(如果dbf_scn=0说明文件头无法读取;ctl_scn=值,文件头好的时候,写文件头的最后一次;last_scn=值,代表控制文件发现数据文件损坏的时刻)
recover database using backup controlfile;
回车,若最后回车报错,重新执行下面的命令,手动来提交CURRENT的日志
recover database using backup controlfile;
select name from v$logfile;——查看当前日志
此时通过redo来提供CURRENT的日志
alter database open;打开失败,
重建控制文件 ,不用resetlogs来打开:
alter database backup controlfile to trace as '/u01/bak/ctl.sql';
shut immediate
startup nomount
创建控制文件用noresetlogs创建
查看SCN,LAST_SCN没值,
继续恢复:recover datafile;
打开:alter database open;
===============================================================================
实验2:使用resetlogs建立控制文件
先备份控制文件 文本格式
SQL> alter database backup controlfile to trace as '/u01/oracle/ctl.sql';
Database altered.
SQL> select * from v$controlfile;
STATUS NAME IS_ BLOCK_SIZE FILE_SIZE_BLKS
---------- -------------------------------------------------- --- ---------- --------------
/db254/control01.ctl NO 16384 450
/db254/control02.ctl NO 16384 450
/db254/control03.ctl NO 16384 450
SQL> select * from v$logfile;
GROUP# STATUS TYPE MEMBER IS_
---------- ---------- ------- --------------------------------------------- ---
3 ONLINE /db254/redo03.log NO
2 ONLINE /db254/redo02.log NO
1 ONLINE /db254/redo01.log NO
一致停库
SQL> shut immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>
删除redo和control文件
SQL> ! rm /db254/redo*
SQL> ! rm /db254/contro*
SQL>
启动数据库 没有控制文件 只能到nomount 报205
SQL> startup
ORACLE instance started.
Total System Global Area 285212672 bytes
Fixed Size 1218992 bytes
Variable Size 83887696 bytes
Database Buffers 197132288 bytes
Redo Buffers 2973696 bytes
ORA-00205: error in identifying control file, check alert log for more info
SQL>
拿备份的文本中 使用resetlog模式创建控制文件 resetlog会在open时重建redo
CREATE CONTROLFILE REUSE DATABASE "ORA10G" RESETLOGS ARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 292
LOGFILE
GROUP 1 '/db254/redo01.log' SIZE 50M,
GROUP 2 '/db254/redo02.log' SIZE 50M,
GROUP 3 '/db254/redo03.log' SIZE 50M
DATAFILE
'/db254/system01.dbf',
'/db254/undotbs01.dbf',
'/db254/sysaux01.dbf',
'/db254/users01.dbf',
'/db254/mytbs01.dbf',
'/db254/ind_tbs01.dbf',
'/db254/wb.dbf'
CHARACTER SET AL32UTF8
;
Control file created.
SQL> select status from v$instance;
STATUS
----------
MOUNTED
SQL> 控制文件已建好 日志文件会在open时重新创建
SQL> ! ls /db254/control*
/db254/control01.ctl /db254/control02.ctl /db254/control03.ctl
SQL> ! ls /db254/redo*
ls: /db254/redo*: 没有那个文件或目录
SQL>
因为数据文件是一致的.所以不需要恢复
SQL> select name,FUZZY,CHECKPOINT_CHANGE#,CHECKPOINT_COUNT from v$datafile_header;
NAME FUZ CHECKPOINT_CHANGE# CHECKPOINT_COUNT
-------------------------------------------------- --- ------------------ ----------------
/db254/system01.dbf NO 1079164 247
/db254/undotbs01.dbf NO 1079164 207
/db254/sysaux01.dbf NO 1079164 244
/db254/users01.dbf NO 1079164 257
/db254/mytbs01.dbf NO 1079164 120
/db254/ind_tbs01.dbf NO 1079164 54
/db254/wb.dbf NO 1079164 11
7 rows selected.
SQL>
SQL> alter database open resetlogs;
Database altered.
SQL>
SQL> ! ls /db254/redo*
/db254/redo01.log /db254/redo02.log /db254/redo03.log
=================================================================================
实验3:使用旧的控制文件来恢复
场景表述:
归档的数据库
1.有控制文件的二进制文件备份
2.控制文件全部损坏,数据库正在交易崩溃
3.取回老的二进制控制文件
4.能起到mount 但控制文件是以前的备份(旧的),数据文件因为是不一致性停库,需要恢复
5.因为控制文件是旧的 就不能以控制文件内的信息为基准去恢复了.
告诉数据库控制文件是老的,只能以归档和在线日志为准取恢复(不完全恢复)
SQL> alter database backup controlfile to '/u01/oracle/control.bk';
Database altered.
SQL>
产生交易
update scott.emp set sal=sal+1;
commit;
alter system switch logfile;
非一致性停库 或者将所有控制文件破坏
SQL> shut abort
ORACLE instance shut down.
SQL>
SQL>
如果是破坏了控制文件 切换一次日志 数据就会崩溃
SQL> alter system switch logfile;
ERROR:
ORA-03114: not connected to ORACLE
alter system switch logfile
*
ERROR at line 1:
ORA-03113: end-of-file on communication channel
SQL>
SQL> conn / as sysdba
Connected to an idle instance.
SQL> startup
ORACLE instance started.
Total System Global Area 285212672 bytes
Fixed Size 1218992 bytes
Variable Size 113247824 bytes
Database Buffers 167772160 bytes
Redo Buffers 2973696 bytes
ORA-00205: error in identifying control file, check alert log for more info
恢复备份的二进制控制文件
SQL> ! cp /u01/oracle/myctl.ctl /u01/oracle/oradata/db20/control01.ctl
SQL> ! cp /u01/oracle/myctl.ctl /u01/oracle/oradata/db20/control02.ctl
SQL> ! cp /u01/oracle/myctl.ctl /u01/oracle/oradata/db20/control03.ctl
SQL>
SQL> alter database mount;
Database altered.
SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open
在做resetlogs启数据库 也会因为数据文件内部不一致而中止,需要恢复
SQL>
SQL>
SQL> 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: '/db254/system01.dbf'
SQL>
SQL> recover database;
ORA-00283: recovery session canceled due to errors
ORA-01610: recovery using the BACKUP CONTROLFILE option must be done
数据库检测到控制文件和数据文件SCN的对比,发现控制文件是老的
数据库如何发现控制文件是老的?
SQL>
SQL> select name,CHECKPOINT_CHANGE#,LAST_CHANGE# from v$datafile;
NAME CHECKPOINT_CHANGE# LAST_CHANGE#
-------------------------------------------------- ------------------ ------------
/db254/system01.dbf 1079166
/db254/undotbs01.dbf 1079166
/db254/sysaux01.dbf 1079166
/db254/users01.dbf 1079166
/db254/mytbs01.dbf 1079166
/db254/ind_tbs01.dbf 1079166
/db254/wb.dbf 1079166
7 rows selected.
SQL> 控制文件中记录的stopSCN即last_change#是空 说明上一次没有一致性停库
控制文件中的记录比实际文件头中的记录要晚
SQL> select file#,RECOVER,FUZZY,CHECKPOINT_CHANGE# from v$datafile_header;
FILE# REC FUZ CHECKPOINT_CHANGE#
---------- --- --- ------------------
1 YES 1079549
2 YES 1079549
3 YES 1079549
4 YES 1079549
5 YES 1079549
6 YES 1079549
7 YES 1079549
7 rows selected.
SQL>
告诉数据库,控制文件是老
SQL> recover database using backup controlfile;
ORA-00279: change 1079347 generated at 03/06/2011 17:14:34 needed for thread 1
ORA-00289: suggestion : /home/oracle/arc1/1_1_745089274.arc
ORA-00280: change 1079347 for thread 1 is in sequence #1
Specify log: {=suggested | filename | AUTO | CANCEL}
auto
ORA-00279: change 1079526 generated at 03/06/2011 17:20:02 needed for thread 1
ORA-00289: suggestion : /home/oracle/arc1/2_1_745089274.arc
ORA-00280: change 1079526 for thread 1 is in sequence #2
ORA-00278: log file '/home/oracle/arc1/1_1_745089274.arc' no longer needed for this recovery
ORA-00279: change 1079528 generated at 03/06/2011 17:20:03 needed for thread 1
ORA-00289: suggestion : /home/oracle/arc1/3_1_745089274.arc
ORA-00280: change 1079528 for thread 1 is in sequence #3
ORA-00278: log file '/home/oracle/arc1/2_1_745089274.arc' no longer needed for this recovery
ORA-00279: change 1079536 generated at 03/06/2011 17:20:11 needed for thread 1
ORA-00289: suggestion : /home/oracle/arc1/4_1_745089274.arc
ORA-00280: change 1079536 for thread 1 is in sequence #4
ORA-00278: log file '/home/oracle/arc1/3_1_745089274.arc' no longer needed for this recovery
ORA-00279: change 1079538 generated at 03/06/2011 17:20:16 needed for thread 1
ORA-00289: suggestion : /home/oracle/arc1/5_1_745089274.arc
ORA-00280: change 1079538 for thread 1 is in sequence #5
ORA-00278: log file '/home/oracle/arc1/4_1_745089274.arc' no longer needed for this recovery
ORA-00279: change 1079541 generated at 03/06/2011 17:20:20 needed for thread 1
ORA-00289: suggestion : /home/oracle/arc1/6_1_745089274.arc
ORA-00280: change 1079541 for thread 1 is in sequence #6
ORA-00278: log file '/home/oracle/arc1/5_1_745089274.arc' no longer needed for this recovery
ORA-00279: change 1079543 generated at 03/06/2011 17:20:20 needed for thread 1
ORA-00289: suggestion : /home/oracle/arc1/7_1_745089274.arc
ORA-00280: change 1079543 for thread 1 is in sequence #7
ORA-00278: log file '/home/oracle/arc1/6_1_745089274.arc' no longer needed for this recovery
ORA-00279: change 1079545 generated at 03/06/2011 17:20:20 needed for thread 1
ORA-00289: suggestion : /home/oracle/arc1/8_1_745089274.arc
ORA-00280: change 1079545 for thread 1 is in sequence #8
ORA-00278: log file '/home/oracle/arc1/7_1_745089274.arc' no longer needed for this recovery
ORA-00279: change 1079547 generated at 03/06/2011 17:20:25 needed for thread 1
ORA-00289: suggestion : /home/oracle/arc1/9_1_745089274.arc
ORA-00280: change 1079547 for thread 1 is in sequence #9
ORA-00278: log file '/home/oracle/arc1/8_1_745089274.arc' no longer needed for this recovery
ORA-00279: change 1079549 generated at 03/06/2011 17:20:25 needed for thread 1
ORA-00289: suggestion : /home/oracle/arc1/10_1_745089274.arc
ORA-00280: change 1079549 for thread 1 is in sequence #10
ORA-00278: log file '/home/oracle/arc1/9_1_745089274.arc' no longer needed for this recovery
ORA-00308: cannot open archived log '/home/oracle/arc1/10_1_745089274.arc'
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3
SQL>
这个文件还没归档 因为是current组 还没来得及切换日志(切换日志才能归档)
此时可以手动提供redo给它 ---> 可一组组尝试
SQL> recover database using backup controlfile;
ORA-00279: change 1079549 generated at 03/06/2011 17:20:25 needed for thread 1
ORA-00289: suggestion : /home/oracle/arc1/10_1_745089274.arc
ORA-00280: change 1079549 for thread 1 is in sequence #10
Specify log: {=suggested | filename | AUTO | CANCEL}
/db254/redo03.log//手动提供current时刻的redo日志
Log applied.
Media recovery complete.
SQL> alter database open resetlogs;
Database altered.
=======补充========================
create controlfile ...resetlogs;
此方式用在:联机日志损坏了,从数据文件头上获取SCN信息,必须使用alter database open resetlogs来打开数据库,
此时,日志序列会重置,incarnamtion(版本)信息会改变,控制文件中的历史记录全部丢失,对数据库的损伤很大。
create controlfile ....noresetlogs;
此方式中的SCN信息来自于日志中,控制文件中的历史记录全部丢失。
注:
1 创建控制文件的二进制备份
2 产生交易,切换日志--重复多次
3 把控制文件其中之一损坏,则全部都会受到影响
4 这时实例会直接崩溃
5 如果是多元化管理的控制文件,把好的覆盖坏的就能解决。
6 如果所有的控制文件都损坏:
a 可以直接重建
b 有历史的二进制的备份,这时取回备份,复制到control_files参数中的指针位置,写几份就会复制几份
然后在mount下进行恢复:recover database using backup controlfile--这是使用控制文件的备份,不知道备份后数据库运行到哪里,
所以使用redo去恢复( 叫不完全恢复),恢复时只使用归档,不会找redo。
当需要的归档不存在时,那么这个归档实际是current log,需要手动提供你的redo文件,
这时可以直接打开库:alter database open resetlogs;
也可以避免resetlogs打开库,避免的方法:只有把redo全部应用了,才可以是此方法,这时不要打开库,去做控制文件的文本备份,
关闭实例,启动到nomount,使用noresetlogs建立控制文件,建立完成后直接恢复库:recover database;然后:alter database open;
但此时控制文件中的历史丢失,可以使用rman将备份历史和归档历史找回:
rman>catalog start with '文件目录';==>yes 此命令将扫描文件目录下的所有备份文件集的头,将信息注册到控制文件
如果采用resetlog打开数据库,控制文件是新的,原来的rman备份信息都没有了。因为rman的信息在控制文件里,新建控制文件没有rman备份信息
将原来备份可以注册到新的控制文件:catalog start with '/home/oracle/rman_bak/'
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/29101923/viewspace-1433337/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/29101923/viewspace-1433337/