这篇文章主要为大家展示了“Oracle如何使用备份控制文件”,内容简而易懂,条理清晰,希望能够帮助大家解决疑惑,下面让小编带领大家一起研究并学习一下“Oracle如何使用备份控制文件”这篇文章吧。
示例一:冷备份所有数据文件--->新建表空间--->备份控制文件(日志文件完好)
实验环境:
当前的控制文件损坏,新创建的表空间损坏,冷备的数据文件中没有该数据文件的备份,但是控制文件和联机日志中有相关的记录;由于控制文件损坏,只能使用备份的控制文件来做恢复。
--查看数据库中已有的表空间
SYS@seiang11g>select * from v$tablespace;
TS# NAME INC BIG FLA ENC
---------- -------------------------------------------------- --- --- --- ---
0 SYSTEM YES NO YES
1 SYSAUX YES NO YES
2 UNDOTBS1 YES NO YES
4 USERS YES NO YES
3 TEMP NO NO YES
6 EXAMPLE YES NO YES
7 RMAN_CATALOG YES NO YES
8 SEIANG YES NO YES
9 WJQ YES NO YES
10 WJQBEST YES NO YES
--查看当前日志的序列号为3
SYS@seiang11g>select group#,sequence#,status from v$log;
GROUP# SEQUENCE# STATUS
---------- ---------- ----------------
1 1 INACTIVE
2 2 INACTIVE
3 3 CURRENT
--新创建一个表空间test
SYS@seiang11g>create tablespace test datafile '/u01/app/oracle/oradata/OraDB11g/test01.dbf' size 5M;
Tablespace created.
--表空间创建完成之后,备份控制文件
SYS@seiang11g>alter database backup controlfile to '/u01/app/oracle/oradata/OraDB11g/control.bak';
Database altered.
--查看数据库中控制文件的多元化路径
SYS@seiang11g>show parameter control
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
control_file_record_keep_time integer 14
control_files string /u01/app/oracle/oradata/OraDB1
1g/control01.ctl, /u01/app/ora
cle/fast_recovery_area/OraDB11
g/control02.ctl
control_management_pack_access string DIAGNOSTIC+TUNING
--4在seiang用户下创建一张表test4,隶属于test表空间
SYS@seiang11g>create table seiang.test4(ID number,name varchar2(30)) tablespace test;
Table created.
--在test4表中插入两条数据,并提交
SYS@seiang11g>insert into seiang.test4 values(1001,'wjq');
1 row created.
SYS@seiang11g>insert into seiang.test4 values(1002,'seiang');
1 row created.
SYS@seiang11g>commit;
Commit complete.
--执行日志切换,刚插入的表中的记录信息已归档
SYS@seiang11g>alter system switch logfile;
System altered.
--查看当前的日志序列号为4
SYS@seiang11g>select group#,sequence#,status from v$log;
GROUP# SEQUENCE# STATUS
---------- ---------- ----------------
1 4 CURRENT
2 2 INACTIVE
3 3 ACTIVE
--再在test4表中插入两条数据,但后两条插入的数据记录在当前日志文件1中
SYS@seiang11g>insert into seiang.test4 values(1003,'wjqgood');
1 row created.
SYS@seiang11g>insert into seiang.test4 values(1004,'wjqbest');
1 row created.
SYS@seiang11g>commit;
Commit complete.
--查看test4表中数据的内容
SYS@seiang11g>select * from seiang.test4;
ID NAME
---------- --------------------------------------------------
1001 wjq
1002 seiang
1003 wjqgood
1004 wjqbest
--模拟test表空间中数据文件损坏或丢失,以及控制文件损坏
SYS@seiang11g>host rm /u01/app/oracle/oradata/OraDB11g/test01.dbf
SYS@seiang11g>host rm /u01/app/oracle/oradata/OraDB11g/control01.ctl
SYS@seiang11g>host rm /u01/app/oracle/fast_recovery_area/OraDB11g/control02.ctl
--数据库已经宕机,无法访问
SYS@seiang11g>select * from seiang.test4;
select * from seiang.test4
*
ERROR at line 1:
ORA-03135: connection lost contact
Process ID: 17679
Session ID: 34 Serial number: 531
--还原所有的数据文件和控制文件,准备做不完全恢复
SYS@seiang11g>host cp /u01/app/oracle/UMAN_Backup/*.dbf /u01/app/oracle/oradata/OraDB11g/
SYS@seiang11g>host cp /u01/app/oracle/oradata/OraDB11g/control.bak /u01/app/oracle/oradata/OraDB11g/control01.ctl
SYS@seiang11g>host cp /u01/app/oracle/oradata/OraDB11g/control.bak /u01/app/oracle/fast_recovery_area/OraDB11g/control02.ctl
--查看控制文件和数据文件头所记录的SCN,发现test01.dbf数据文件头没有记录
SYS@seiang11g>select file#,checkpoint_change#,name from v$datafile;
FILE# CHECKPOINT_CHANGE# NAME
---------- ------------------ --------------------------------------------------
1 1981768 /u01/app/oracle/oradata/OraDB11g/system01.dbf
2 1981768 /u01/app/oracle/oradata/OraDB11g/sysaux01.dbf
3 1981768 /u01/app/oracle/oradata/OraDB11g/undotbs01.dbf
4 1981768 /u01/app/oracle/oradata/OraDB11g/users01.dbf
5 1981768 /u01/app/oracle/oradata/OraDB11g/example01.dbf
6 1981768 /u01/app/oracle/oradata/OraDB11g/rman01.dbf
7 1981768 /u01/app/oracle/oradata/OraDB11g/seiang01.dbf
8 1981768 /u01/app/oracle/oradata/OraDB11g/wjq01.dbf
9 1981768 /u01/app/oracle/oradata/OraDB11g/wjqbest01.dbf
10 1986000 /u01/app/oracle/oradata/OraDB11g/test01.dbf
SYS@seiang11g>
SYS@seiang11g>select file#,checkpoint_change#,name from v$datafile_header;
FILE# CHECKPOINT_CHANGE# NAME
---------- ------------------ --------------------------------------------------
1 1913765 /u01/app/oracle/oradata/OraDB11g/system01.dbf
2 1913765 /u01/app/oracle/oradata/OraDB11g/sysaux01.dbf
3 1913765 /u01/app/oracle/oradata/OraDB11g/undotbs01.dbf
4 1913765 /u01/app/oracle/oradata/OraDB11g/users01.dbf
5 1913765 /u01/app/oracle/oradata/OraDB11g/example01.dbf
6 1913765 /u01/app/oracle/oradata/OraDB11g/rman01.dbf
7 1913765 /u01/app/oracle/oradata/OraDB11g/seiang01.dbf
8 1913765 /u01/app/oracle/oradata/OraDB11g/wjq01.dbf
9 1913765 /u01/app/oracle/oradata/OraDB11g/wjqbest01.dbf
10 0
可以看出:
① file10在控制文件里记录是test01.dbf,而与之对应的数据文件10是不存在的,
② 备份的数据备份的SCN比控制文件SCN还老。
--查看需要恢复的数据文件
SYS@seiang11g>select * from v$recover_file;
FILE# ONLINE ONLINE_ ERROR CHANGE# TIME
---------- ------- ------- ----------------------------------------------------------------- ---------- ---------
1 ONLINE ONLINE UNKNOWN ERROR 1913765 02-AUG-17
2 ONLINE ONLINE UNKNOWN ERROR 1913765 02-AUG-17
3 ONLINE ONLINE UNKNOWN ERROR 1913765 02-AUG-17
4 ONLINE ONLINE UNKNOWN ERROR 1913765 02-AUG-17
5 ONLINE ONLINE UNKNOWN ERROR 1913765 02-AUG-17
6 ONLINE ONLINE UNKNOWN ERROR 1913765 02-AUG-17
7 ONLINE ONLINE UNKNOWN ERROR 1913765 02-AUG-17
8 ONLINE ONLINE UNKNOWN ERROR 1913765 02-AUG-17
9 ONLINE ONLINE UNKNOWN ERROR 1913765 02-AUG-17
10 ONLINE ONLINE FILE NOT FOUND 0
--尝试做完全恢复,提示使用备份的控制文件来恢复
SYS@seiang11g>recover database;
ORA-00283: recovery session canceled due to errors
ORA-01610: recovery using the BACKUP CONTROLFILE option must be done
--使用备份的控制文件来做恢复,出现报错
SYS@seiang11g>recover database using backup controlfile;
ORA-00283: recovery session canceled due to errors
ORA-01110: data file 10: '/u01/app/oracle/oradata/OraDB11g/test01.dbf'
ORA-01157: cannot identify/lock data file 10 - see DBWR trace file
ORA-01110: data file 10: '/u01/app/oracle/oradata/OraDB11g/test01.dbf'
此错是因为老备份里没有abcd表空间,但只要控制文件里记录了abcd就好办,方法是建一个datafile的空文件,而其中内容可由日志文件recover(前滚)时填补出来。
--新建一个数据文件
SYS@seiang11g>alter database create datafile '/u01/app/oracle/oradata/OraDB11g/test01.dbf';
Database altered.
--再次查看控制文件和数据文件头中做记录的SCN
SYS@seiang11g>select file#,checkpoint_change#,name from v$datafile;
FILE# CHECKPOINT_CHANGE# NAME
---------- ------------------ --------------------------------------------------
1 1981768 /u01/app/oracle/oradata/OraDB11g/system01.dbf
2 1981768 /u01/app/oracle/oradata/OraDB11g/sysaux01.dbf
3 1981768 /u01/app/oracle/oradata/OraDB11g/undotbs01.dbf
4 1981768 /u01/app/oracle/oradata/OraDB11g/users01.dbf
5 1981768 /u01/app/oracle/oradata/OraDB11g/example01.dbf
6 1981768 /u01/app/oracle/oradata/OraDB11g/rman01.dbf
7 1981768 /u01/app/oracle/oradata/OraDB11g/seiang01.dbf
8 1981768 /u01/app/oracle/oradata/OraDB11g/wjq01.dbf
9 1981768 /u01/app/oracle/oradata/OraDB11g/wjqbest01.dbf
10 1986000 /u01/app/oracle/oradata/OraDB11g/test01.dbf
10 rows selected.
SYS@seiang11g>
SYS@seiang11g>select file#,checkpoint_change#,name from v$datafile_header;
FILE# CHECKPOINT_CHANGE# NAME
---------- ------------------ --------------------------------------------------
1 1913766 /u01/app/oracle/oradata/OraDB11g/system01.dbf
2 1913766 /u01/app/oracle/oradata/OraDB11g/sysaux01.dbf
3 1913766 /u01/app/oracle/oradata/OraDB11g/undotbs01.dbf
4 1913766 /u01/app/oracle/oradata/OraDB11g/users01.dbf
5 1913766 /u01/app/oracle/oradata/OraDB11g/example01.dbf
6 1913766 /u01/app/oracle/oradata/OraDB11g/rman01.dbf
7 1913766 /u01/app/oracle/oradata/OraDB11g/seiang01.dbf
8 1913766 /u01/app/oracle/oradata/OraDB11g/wjq01.dbf
9 1913766 /u01/app/oracle/oradata/OraDB11g/wjqbest01.dbf
10 1985999 /u01/app/oracle/oradata/OraDB11g/test01.dbf
--再次使用备份的控制文件来做恢复
SYS@seiang11g>recover database using backup controlfile;
ORA-00279: change 1913766 generated at 08/02/2017 11:12:22 needed for thread 1
ORA-00289: suggestion : /u01/app/oracle/arch/arch_1_950971495_1.log
ORA-00280: change 1913766 for thread 1 is in sequence #1
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
auto (因为需要的日志已经归档,所以选择auto)
ORA-00279: change 1914386 generated at 08/02/2017 14:48:59 needed for thread 1
ORA-00289: suggestion : /u01/app/oracle/arch/arch_1_950971495_2.log
ORA-00280: change 1914386 for thread 1 is in sequence #2
ORA-00278: log file '/u01/app/oracle/arch/arch_1_950971495_1.log' no longer needed for this recovery
ORA-00279: change 1914402 generated at 08/02/2017 14:59:56 needed for thread 1
ORA-00289: suggestion : /u01/app/oracle/arch/arch_1_950972396_1.log
ORA-00280: change 1914402 for thread 1 is in sequence #1
ORA-00279: change 1936446 generated at 08/02/2017 15:39:52 needed for thread 1
ORA-00289: suggestion : /u01/app/oracle/arch/arch_1_950972396_2.log
ORA-00280: change 1936446 for thread 1 is in sequence #2
ORA-00278: log file '/u01/app/oracle/arch/arch_1_950972396_1.log' no longer needed for this recovery
ORA-00279: change 1937042 generated at 08/02/2017 15:49:42 needed for thread 1
ORA-00289: suggestion : /u01/app/oracle/arch/arch_1_950972396_3.log
ORA-00280: change 1937042 for thread 1 is in sequence #3
ORA-00278: log file '/u01/app/oracle/arch/arch_1_950972396_2.log' no longer needed for this recovery
ORA-00279: change 1937100 generated at 08/02/2017 15:51:34 needed for thread 1
ORA-00289: suggestion : /u01/app/oracle/arch/arch_1_950972396_4.log
ORA-00280: change 1937100 for thread 1 is in sequence #4
ORA-00278: log file '/u01/app/oracle/arch/arch_1_950972396_3.log' no longer needed for this recovery
ORA-00279: change 1937111 generated at 08/02/2017 16:23:53 needed for thread 1
ORA-00289: suggestion : /u01/app/oracle/arch/arch_1_950977433_1.log
ORA-00280: change 1937111 for thread 1 is in sequence #1
ORA-00279: change 1955524 generated at 08/02/2017 22:00:32 needed for thread 1
ORA-00289: suggestion : /u01/app/oracle/arch/arch_1_950977433_2.log
ORA-00280: change 1955524 for thread 1 is in sequence #2
ORA-00278: log file '/u01/app/oracle/arch/arch_1_950977433_1.log' no longer needed for this recovery
ORA-00279: change 1981768 generated at 08/03/2017 08:19:54 needed for thread 1
ORA-00289: suggestion : /u01/app/oracle/arch/arch_1_950977433_3.log
ORA-00280: change 1981768 for thread 1 is in sequence #3
ORA-00278: log file '/u01/app/oracle/arch/arch_1_950977433_2.log' no longer needed for this recovery
ORA-00279: change 1986580 generated at 08/03/2017 10:00:19 needed for thread 1
ORA-00289: suggestion : /u01/app/oracle/arch/arch_1_950977433_4.log
ORA-00280: change 1986580 for thread 1 is in sequence #4
ORA-00278: log file '/u01/app/oracle/arch/arch_1_950977433_3.log' no longer needed for this recovery
ORA-00308: cannot open archived log '/u01/app/oracle/arch/arch_1_950977433_4.log'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
出现此错误,因为当前的当前的日志文件尚未归档,所以出现错误,所以接下来使用当前的日志文件来做恢复
SYS@seiang11g>recover database using backup controlfile;
ORA-00279: change 1986580 generated at 08/03/2017 10:00:19 needed for thread 1
ORA-00289: suggestion : /u01/app/oracle/arch/arch_1_950977433_4.log
ORA-00280: change 1986580 for thread 1 is in sequence #4
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
/u01/app/oracle/oradata/OraDB11g/redo01.log (当前日志文件)
Log applied.
Media recovery complete.
SYS@seiang11g>
--恢复完成,使用resetlogs打开数据库
SYS@seiang11g>alter database open resetlogs;
Database altered.
--查看控制文件和数据文件头记录的SCN一致
SYS@seiang11g>select file#,checkpoint_change# from v$datafile;
FILE# CHECKPOINT_CHANGE#
---------- ------------------
1 1986883
2 1986883
3 1986883
4 1986883
5 1986883
6 1986883
7 1986883
8 1986883
9 1986883
10 1986883
SYS@seiang11g>select file#,checkpoint_change# from v$datafile_header;
FILE# CHECKPOINT_CHANGE#
---------- ------------------
1 1986883
2 1986883
3 1986883
4 1986883
5 1986883
6 1986883
7 1986883
8 1986883
9 1986883
10 1986883
--确认test4表中的数据全部恢复成功
SYS@seiang11g>select * from seiang.test4;
ID NAME
---------- --------------------------------------------------
1001 wjq
1002 seiang
1003 wjqgood
1004 wjqbest
示例二:冷备份所有数据文件--->备份控制文件--->新建表空间(日志文件完好)
实验环境: 当前的控制文件损坏,新创建的表空间损坏,冷备的数据文件中没有该数据文件的备份,控制文件中也没有该表空间的记录,但是联机日志中有相关的记录;由于控制文件损坏,只能使用备份的控制文件来做恢复。 --查看数据库中已存在的表空间
SYS@seiang11g>select * from v$tablespace;
TS# NAME INC BIG FLA ENC ---------- -------------------------------------------------- --- --- --- ---
0 SYSTEM YES NO YES
1 SYSAUX YES NO YES
2 UNDOTBS1 YES NO YES
4 USERS YES NO YES
3 TEMP NO NO YES
6 EXAMPLE YES NO YES
7 RMAN_CATALOG YES NO YES
8 SEIANG YES NO YES
9 WJQ YES NO YES
10 WJQBEST YES NO YES --备份控制文件
SYS@seiang11g>alter database backup controlfile to '/u01/app/oracle/oradata/OraDB11g/control.bak1'; Database altered. -创建表空间comsys该表空间记录在当前的日志redo01.log中
SYS@seiang11g>create tablespace comsys datafile '/u01/app/oracle/oradata/OraDB11g/comsys01.dbf' size 5M; Tablespace created. --在seiang用户下创建一张表test4,隶属于comsys表空间
SYS@seiang11g>create table seiang.test4(age number,address varchar2(10)) tablespace comsys; Table created. --在test4表中插入两条数据,并提交
SYS@seiang11g>insert into seiang.test4 values(23,'beijing');
1 row created.
SYS@seiang11g>insert into seiang.test4 values(25,'shanghai');
1 row created.
SYS@seiang11g>commit;
Commit complete.
SYS@seiang11g>select * from seiang.test4;
AGE ADDRESS ---------- ----------
23 beijing
25 shanghai --查看当前日志的序列号为1
SYS@seiang11g>select group#,sequence#,status from v$log;
GROUP# SEQUENCE# STATUS ---------- ---------- ----------------
1 1 CURRENT
2 0 UNUSED
3 0 UNUSED --模拟comsys01.dbf数据文件丢失或损坏,控制文件损坏
SYS@seiang11g>host rm /u01/app/oracle/oradata/OraDB11g/comsys01.dbf
SYS@seiang11g>shutdown abort
ORACLE instance shut down. --从备份的文件中还原控制文件和数据文件
SYS@seiang11g>host cp /u01/app/oracle/oradata/OraDB11g/control.bak1 /u01/app/oracle/oradata/OraDB11g/control01.ctl
SYS@seiang11g>host cp /u01/app/oracle/oradata/OraDB11g/control.bak1 /u01/app/oracle/fast_recovery_area/OraDB11g/control02.ctl
SYS@seiang11g>host cp
/u01/app/oracle/UMAN_Backup/*.dbf /u01/app/oracle/oradata/OraDB11g SYS@seiang11g>startup
ORACLE instance started.
Total System Global Area 1252663296 bytes
Fixed Size 2252824 bytes Variable Size 788533224 bytes Database Buffers 452984832 bytes
Redo Buffers 8892416 bytes Database mounted.
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open --查看控制文件和数据文件头,发现并没有comsys表空间的相关记录
SYS@seiang11g>select file#,checkpoint_change#,name from v$datafile;
FILE# CHECKPOINT_CHANGE# NAME ---------- ------------------ --------------------------------------------------
1 1986883 /u01/app/oracle/oradata/OraDB11g/system01.dbf
2 1986883 /u01/app/oracle/oradata/OraDB11g/sysaux01.dbf
3 1986883 /u01/app/oracle/oradata/OraDB11g/undotbs01.dbf
4 1986883 /u01/app/oracle/oradata/OraDB11g/users01.dbf
5 1986883 /u01/app/oracle/oradata/OraDB11g/example01.dbf
6 1986883 /u01/app/oracle/oradata/OraDB11g/rman01.dbf
7 1986883 /u01/app/oracle/oradata/OraDB11g/seiang01.dbf
8 1986883 /u01/app/oracle/oradata/OraDB11g/wjq01.dbf
9 1986883 /u01/app/oracle/oradata/OraDB11g/wjqbest01.dbf
SYS@seiang11g>select file#,checkpoint_change#,name from v$datafile_header;
FILE# CHECKPOINT_CHANGE# NAME ---------- ------------------ --------------------------------------------------
1 1913765 /u01/app/oracle/oradata/OraDB11g/system01.dbf
2 1913765 /u01/app/oracle/oradata/OraDB11g/sysaux01.dbf
3 1913765 /u01/app/oracle/oradata/OraDB11g/undotbs01.dbf
4 1913765 /u01/app/oracle/oradata/OraDB11g/users01.dbf
5 1913765 /u01/app/oracle/oradata/OraDB11g/example01.dbf
6 1913765 /u01/app/oracle/oradata/OraDB11g/rman01.dbf
7 1913765 /u01/app/oracle/oradata/OraDB11g/seiang01.dbf
8 1913765 /u01/app/oracle/oradata/OraDB11g/wjq01.dbf
9 1913765 /u01/app/oracle/oradata/OraDB11g/wjqbest01.dbf --尝试完全恢复,提示使用备份的控制文件做恢复
SYS@seiang11g>recover database;
ORA-00283: recovery session canceled due to errors
ORA-01610: recovery using the BACKUP CONTROLFILE option must be done --使用备份的控制文件做恢复
SYS@seiang11g>recover database using backup controlfile;
ORA-00279: change 1913766 generated at 08/02/2017 11:12:22 needed for thread 1
ORA-00289: suggestion : /u01/app/oracle/arch/arch_1_950971495_1.log
ORA-00280: change 1913766 for thread 1 is in sequence #1
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
auto (该日志已归档,所以选择auto)
ORA-00279: change 1914386 generated at 08/02/2017 14:48:59 needed for thread 1
ORA-00289: suggestion : /u01/app/oracle/arch/arch_1_950971495_2.log
ORA-00280: change 1914386 for thread 1 is in sequence #2
ORA-00278: log file '/u01/app/oracle/arch/arch_1_950971495_1.log' no longer needed for this recovery
ORA-00279: change 1914402 generated at 08/02/2017 14:59:56 needed for thread 1
ORA-00289: suggestion : /u01/app/oracle/arch/arch_1_950972396_1.log
ORA-00280: change 1914402 for thread 1 is in sequence #1
ORA-00279: change 1936446 generated at 08/02/2017 15:39:52 needed for thread 1
ORA-00289: suggestion : /u01/app/oracle/arch/arch_1_950972396_2.log
ORA-00280: change 1936446 for thread 1 is in sequence #2
ORA-00278: log file '/u01/app/oracle/arch/arch_1_950972396_1.log' no longer needed for this recovery
ORA-00279: change 1937042 generated at 08/02/2017 15:49:42 needed for thread 1
ORA-00289: suggestion : /u01/app/oracle/arch/arch_1_950972396_3.log
ORA-00280: change 1937042 for thread 1 is in sequence #3
ORA-00278: log file '/u01/app/oracle/arch/arch_1_950972396_2.log' no longer needed for this recovery
ORA-00279: change 1937100 generated at 08/02/2017 15:51:34 needed for thread 1
ORA-00289: suggestion : /u01/app/oracle/arch/arch_1_950972396_4.log
ORA-00280: change 1937100 for thread 1 is in sequence #4
ORA-00278: log file '/u01/app/oracle/arch/arch_1_950972396_3.log' no longer needed for this recovery
ORA-00279: change 1937111 generated at 08/02/2017 16:23:53 needed for thread 1
ORA-00289: suggestion : /u01/app/oracle/arch/arch_1_950977433_1.log
ORA-00280: change 1937111 for thread 1 is in sequence #1
ORA-00279: change 1955524 generated at 08/02/2017 22:00:32 needed for thread 1
ORA-00289: suggestion : /u01/app/oracle/arch/arch_1_950977433_2.log
ORA-00280: change 1955524 for thread 1 is in sequence #2
ORA-00278: log file '/u01/app/oracle/arch/arch_1_950977433_1.log' no longer needed for this recovery
ORA-00279: change 1981768 generated at 08/03/2017 08:19:54 needed for thread 1
ORA-00289: suggestion : /u01/app/oracle/arch/arch_1_950977433_3.log
ORA-00280: change 1981768 for thread 1 is in sequence #3
ORA-00278: log file '/u01/app/oracle/arch/arch_1_950977433_2.log' no longer needed for this recovery
ORA-00279: change 1986580 generated at 08/03/2017 10:00:19 needed for thread 1
ORA-00289: suggestion : /u01/app/oracle/arch/arch_1_950977433_4.log
ORA-00280: change 1986580 for thread 1 is in sequence #4
ORA-00278: log file '/u01/app/oracle/arch/arch_1_950977433_3.log' no longer needed for this recovery
ORA-00279: change 1986880 generated at 08/03/2017 10:33:47 needed for thread 1
ORA-00289: suggestion : /u01/app/oracle/arch/arch_1_951042827_1.log
ORA-00280: change 1986880 for thread 1 is in sequence #1
ORA-00308: cannot open archived log '/u01/app/oracle/arch/arch_1_951042827_1.log'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
出现此错误,因为当前的日志文件尚未归档,所以出现错误,所以接下来使用当前的日志文件来做恢复
SYS@seiang11g>recover database using backup controlfile;
ORA-00279: change 1986880 generated at 08/03/2017 10:33:47 needed for thread 1
ORA-00289: suggestion : /u01/app/oracle/arch/arch_1_951042827_1.log
ORA-00280: change 1986880 for thread 1 is in sequence #1
Specify log: {<RET>=suggested | filename | AUTO | CANCEL} /u01/app/oracle/oradata/OraDB11g/redo01.log (当前的日志文件)
ORA-00283: recovery session canceled due to errors
ORA-01244: unnamed datafile(s) added to control file by media recovery
ORA-01110: data file 10: '/u01/app/oracle/oradata/OraDB11g/comsys01.dbf' (从当前的日志文件中,我们发现了关于comsys表空间的相关记录)
ORA-01112: media recovery not started
当再次使用备份的控制文件做恢复时,出现如下的错误提示
SYS@seiang11g>recover database using backup controlfile;
ORA-00283: recovery session canceled due to errors
ORA-01111: name for data file 10 is unknown - rename to correct file
ORA-01110: data file 10: '/u01/app/oracle/product/11.2.0/dbhome_1/dbs/UNNAMED00010'
ORA-01157: cannot identify/lock data file 10 - see DBWR trace file
ORA-01111: name for data file 10 is unknown - rename to correct file
ORA-01110: data file 10: '/u01/app/oracle/product/11.2.0/dbhome_1/dbs/UNNAMED00010' --查看控制文件和数据文件头,有了关于comsys表空间的相关记录
SYS@seiang11g>select file#,checkpoint_change#,name from v$datafile;
FILE# CHECKPOINT_CHANGE# NAME ---------- ------------------ --------------------------------------------------
1 1986883 /u01/app/oracle/oradata/OraDB11g/system01.dbf
2 1986883 /u01/app/oracle/oradata/OraDB11g/sysaux01.dbf
3 1986883 /u01/app/oracle/oradata/OraDB11g/undotbs01.dbf
4 1986883 /u01/app/oracle/oradata/OraDB11g/users01.dbf
5 1986883 /u01/app/oracle/oradata/OraDB11g/example01.dbf
6 1986883 /u01/app/oracle/oradata/OraDB11g/rman01.dbf
7 1986883 /u01/app/oracle/oradata/OraDB11g/seiang01.dbf
8 1986883 /u01/app/oracle/oradata/OraDB11g/wjq01.dbf
9 1986883 /u01/app/oracle/oradata/OraDB11g/wjqbest01.dbf
10 1988334 /u01/app/oracle/product/11.2.0/dbhome_1/dbs/UNNAME
D00010
SYS@seiang11g>select file#,checkpoint_change#,name from v$datafile_header;
FILE# CHECKPOINT_CHANGE# NAME ---------- ------------------ --------------------------------------------------
1 1988336 /u01/app/oracle/oradata/OraDB11g/system01.dbf
2 1988336 /u01/app/oracle/oradata/OraDB11g/sysaux01.dbf
3 1988336 /u01/app/oracle/oradata/OraDB11g/undotbs01.dbf
4 1988336 /u01/app/oracle/oradata/OraDB11g/users01.dbf
5 1988336 /u01/app/oracle/oradata/OraDB11g/example01.dbf
6 1988336 /u01/app/oracle/oradata/OraDB11g/rman01.dbf
7 1988336 /u01/app/oracle/oradata/OraDB11g/seiang01.dbf
8 1988336 /u01/app/oracle/oradata/OraDB11g/wjq01.dbf
9 1988336 /u01/app/oracle/oradata/OraDB11g/wjqbest01.dbf
10 0 --创建数据文件,并对控制文件中记录未知的数据文件重命名
SYS@seiang11g>alter database create datafile '/u01/app/oracle/product/11.2.0/dbhome_1/dbs/UNNAMED00010'
2 as '/u01/app/oracle/oradata/OraDB11g/comsys01.dbf'; Database altered. (当前的日志文件)
SYS@seiang11g>select file#,checkpoint_change#,name from v$datafile;
FILE# CHECKPOINT_CHANGE# NAME ---------- ------------------ --------------------------------------------------
1 1986883 /u01/app/oracle/oradata/OraDB11g/system01.dbf
2 1986883 /u01/app/oracle/oradata/OraDB11g/sysaux01.dbf
3 1986883 /u01/app/oracle/oradata/OraDB11g/undotbs01.dbf
4 1986883 /u01/app/oracle/oradata/OraDB11g/users01.dbf
5 1986883 /u01/app/oracle/oradata/OraDB11g/example01.dbf
6 1986883 /u01/app/oracle/oradata/OraDB11g/rman01.dbf
7 1986883 /u01/app/oracle/oradata/OraDB11g/seiang01.dbf
8 1986883 /u01/app/oracle/oradata/OraDB11g/wjq01.dbf
9 1986883 /u01/app/oracle/oradata/OraDB11g/wjqbest01.dbf
10 1988334 /u01/app/oracle/oradata/OraDB11g/comsys01.dbf --再次查看控制文件和数据文件头
SYS@seiang11g>select file#,checkpoint_change#,name from v$datafile_header;
FILE# CHECKPOINT_CHANGE# NAME ---------- ------------------ --------------------------------------------------
1 1988336 /u01/app/oracle/oradata/OraDB11g/system01.dbf
2 1988336 /u01/app/oracle/oradata/OraDB11g/sysaux01.dbf
3 1988336 /u01/app/oracle/oradata/OraDB11g/undotbs01.dbf
4 1988336 /u01/app/oracle/oradata/OraDB11g/users01.dbf
5 1988336 /u01/app/oracle/oradata/OraDB11g/example01.dbf
6 1988336 /u01/app/oracle/oradata/OraDB11g/rman01.dbf
7 1988336 /u01/app/oracle/oradata/OraDB11g/seiang01.dbf
8 1988336 /u01/app/oracle/oradata/OraDB11g/wjq01.dbf
9 1988336 /u01/app/oracle/oradata/OraDB11g/wjqbest01.dbf
10 1988334 /u01/app/oracle/oradata/OraDB11g/comsys01.dbf --再次使用备份的控制文件和当前日志做恢复
SYS@seiang11g>recover database using backup controlfile;
ORA-00279: change 1988334 generated at 08/03/2017 10:53:39 needed for thread 1
ORA-00289: suggestion : /u01/app/oracle/arch/arch_1_951042827_1.log
ORA-00280: change 1988334 for thread 1 is in sequence #1
Specify log: {<RET>=suggested | filename | AUTO | CANCEL} /u01/app/oracle/oradata/OraDB11g/redo01.log Log applied.
Media recovery complete. --恢复完成后,使用resetlogs打开数据库
SYS@seiang11g>alter database open resetlogs; Database altered. --查看控制文件和数据文件头SCN一致
SYS@seiang11g>select file#,checkpoint_change#,name from v$datafile;
FILE# CHECKPOINT_CHANGE# NAME ---------- ------------------ --------------------------------------------------
1 1989738 /u01/app/oracle/oradata/OraDB11g/system01.dbf
2 1989738 /u01/app/oracle/oradata/OraDB11g/sysaux01.dbf
3 1989738 /u01/app/oracle/oradata/OraDB11g/undotbs01.dbf
4 1989738 /u01/app/oracle/oradata/OraDB11g/users01.dbf
5 1989738 /u01/app/oracle/oradata/OraDB11g/example01.dbf
6 1989738 /u01/app/oracle/oradata/OraDB11g/rman01.dbf
7 1989738 /u01/app/oracle/oradata/OraDB11g/seiang01.dbf
8 1989738 /u01/app/oracle/oradata/OraDB11g/wjq01.dbf
9 1989738 /u01/app/oracle/oradata/OraDB11g/wjqbest01.dbf
10 1989738 /u01/app/oracle/oradata/OraDB11g/comsys01.dbf
SYS@seiang11g>select file#,checkpoint_change#,name from v$datafile_header;
FILE# CHECKPOINT_CHANGE# NAME ---------- ------------------ --------------------------------------------------
1 1989738 /u01/app/oracle/oradata/OraDB11g/system01.dbf
2 1989738 /u01/app/oracle/oradata/OraDB11g/sysaux01.dbf
3 1989738 /u01/app/oracle/oradata/OraDB11g/undotbs01.dbf
4 1989738 /u01/app/oracle/oradata/OraDB11g/users01.dbf
5 1989738 /u01/app/oracle/oradata/OraDB11g/example01.dbf
6 1989738 /u01/app/oracle/oradata/OraDB11g/rman01.dbf
7 1989738 /u01/app/oracle/oradata/OraDB11g/seiang01.dbf
8 1989738 /u01/app/oracle/oradata/OraDB11g/wjq01.dbf
9 1989738 /u01/app/oracle/oradata/OraDB11g/wjqbest01.dbf
10 1989738 /u01/app/oracle/oradata/OraDB11g/comsys01.dbf --查看已恢复test4表中的数据记录
SYS@seiang11g>select * from seiang.test4;
AGE ADDRESS ---------- ----------
23 beijing
25 shanghai
以上是“Oracle如何使用备份控制文件”这篇文章的所有内容,感谢各位的阅读!相信大家都有了一定的了解,希望分享的内容对大家有所帮助,如果还想学习更多知识,欢迎关注亿速云行业资讯频道!
免责声明:本站发布的内容(图片、视频和文字)以原创、转载和分享为主,文章观点不代表本网站立场,如果涉及侵权请联系站长邮箱:is@yisu.com进行举报,并提供相关证据,一经查实,将立刻删除涉嫌侵权内容。