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