Oracle如何使用备份控制文件-关系型数据库-互联网-天达云

Oracle如何使用备份控制文件
更新：HHH 时间：2023-1-7

这篇文章主要为大家展示了“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如何使用备份控制文件”这篇文章的所有内容，感谢各位的阅读！相信大家都有了一定的了解，希望分享的内容对大家有所帮助，如果还想学习更多知识，欢迎关注天达云行业资讯频道！


返回关系型数据库教程...

新手上路

产品管理

支付方式

关于我们