关于控制文件的丢失

----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

恢复控制文件的大致思路

控制文件的丢失，我们需要确定是否开始可控制文件的自动备份，RMAN> show controlfile autobackup;如果开启了自动备份，将数据库起到nomunt状态，rman下restore controlfile from autobackup，再起到mount状态，并进行数据库恢复recover database;，最后开启数据库即可alter database open resetlogs;，如果控制文件转储到trace文件中，我们需要找到相对应的trc文件，按照上面的进行恢复操作，直至open后，再挂载临时表空间数据文件alter tablespace temp add tempfile'/u01/app/oracle/oradata/PROD/temp01.dbf'; 如果是备份控制文件到指定的位置，丢失控制文件，我们需要把原来的备份capy到控制文件的相对应的路径，路径可从参数文件得知，拷贝后把数据库起到mount状态，恢复数据库时，会提醒我们使用backup controlfile，因此我们在mount后 recover database using BACKUP CONTROLFILE until cancel;，执行后需要指定下相对应current状态的日志组文件的位置，操作完后，以resetlogs方式打开数据库即可alter database open resetlogs;，

----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

控制文件自动备份

先查看是否开启自动备份，如果未开启，将手动开启

RMAN> show controlfile autobackup;

RMAN> CONFIGURE CONTROLFILE AUTOBACKUP on;

RMAN> show controlfile autobackup;

手动触发控制文件自动备份

SQL> create tablespace ts_ctl datafile '/u01/app/oracle/oradata/PROD/ts_ctl01.dbf'size 5m;

查看是否触发了自动备份,如为触发，备份下系统system表空间即可，新建表空间触发会有延迟。

cd /u01/app/oracle/fast_recovery_area/PROD/autobackup/2016_01_17

查看相对应的控制文件位置，并模拟删除

SQL> show parameter control_file;

数据库启到nomount阶段

RMAN>restore controlfile from autobackup

RMAN>alter database mount;

RMAN>recover database;

RMAN>alter database open resetlogs;

trace中的控制文件备份执行恢复

SQL> alter database backup controlfile to trace;

找到转储trace文件。

SQL> show parameter dump;

[oracle@Practice 2016_01_17]$ cd /u01/app/oracle/diag/rdbms/prod/PROD/trace

找到最新的trace文件

[oracle@Practice trace]$ ls -lrt

[oracle@Practice trace]$ more PROD_ora_9618.trc

筛选出重要信息

STARTUP NOMOUNT

CREATE CONTROLFILE REUSE DATABASE "PROD" NORESETLOGS ARCHIVELOG

MAXLOGFILES 16

MAXLOGMEMBERS 3

MAXDATAFILES 100

MAXINSTANCES 8

MAXLOGHISTORY 292

LOGFILE

GROUP 1 '/u01/app/oracle/oradata/PROD/redo01.log' SIZE 50M BLOCKSIZE 512,

GROUP 2 '/u01/app/oracle/oradata/PROD/redo02.log' SIZE 50M BLOCKSIZE 512,

GROUP 3 '/u01/app/oracle/oradata/PROD/redo03.log' SIZE 50M BLOCKSIZE 512

-- STANDBY LOGFILE

DATAFILE

'/u01/app/oracle/oradata/PROD/system01.dbf',

'/u01/app/oracle/oradata/PROD/sysaux01.dbf',

'/u01/app/oracle/oradata/PROD/undotbs01.dbf',

'/u01/app/oracle/oradata/PROD/users01.dbf',

'/u01/app/oracle/oradata/PROD/ts_ctl01.dbf'

CHARACTER SET AL32UTF8

;

-- Configure RMAN configuration record 1

VARIABLE RECNO NUMBER;

EXECUTE :RECNO := SYS.DBMS_BACKUP_RESTORE.SETCONFIG('CONTROLFILE AUTOBACKUP','ON

');

-- Commands to re-create incarnation table

-- Below log names MUST be changed to existing filenames on

-- disk. Any one log file from each branch can be used to

-- re-create incarnation records.

-- ALTER DATABASE REGISTER LOGFILE '/u01/app/oracle/fast_recovery_area/PROD/arch

ivelog/2016_01_17/o1_mf_1_1_%u_.arc';

-- ALTER DATABASE REGISTER LOGFILE '/u01/app/oracle/fast_recovery_area/PROD/arch

ivelog/2016_01_17/o1_mf_1_1_%u_.arc';

-- ALTER DATABASE REGISTER LOGFILE '/u01/app/oracle/fast_recovery_area/PROD/arch

ivelog/2016_01_17/o1_mf_1_1_%u_.arc';

-- Recovery is required if any of the datafiles are restored backups,

-- or if the last shutdown was not normal or immediate.

RECOVER DATABASE

-- All logs need archiving and a log switch is needed.

ALTER SYSTEM ARCHIVE LOG ALL;

-- Database can now be opened normally.

ALTER DATABASE OPEN;

查找并删除相对应的控制文件

SQL> show parameter control_files

SQL> !rm /u01/app/oracle/oradata/PROD/control01.ctl;

SQL> !rm /u01/app/oracle/fast_recovery_area/PROD/control02.ctl

根据上面rtrace文件的内容进行恢复即可

open后挂载临时表空间数据文件就完成了

SQL> !ls /u01/app/oracle/oradata/PROD/temp01.dbf

/u01/app/oracle/oradata/PROD/temp01.dbf

SQL> select file_name from dba_temp_files;

SQL> alter tablespace temp add tempfile'/u01/app/oracle/oradata/PROD/temp01.dbf';

备份控制文件到指定目录控制文件恢复

备份当前的控制文件到指定目录

SQL> alter database backup controlfile to '/home/oracle/control_bak.ctl';

SQL> !ls /home/oracle/con*;

/home/oracle/control_bak.ctl

删除控制文件

SQL> !rm /u01/app/oracle/oradata/PROD/control01.ctl;

SQL> !rm /u01/app/oracle/fast_recovery_area/PROD/control02.ctl

SQL> shutdown abort;

ORACLE instance shut down.

复制备份到相应的控制文件目录下

SQL> !cp /home/oracle/control_bak.ctl /u01/app/oracle/oradata/PROD/control01.ctl;

SQL> !cp /home/oracle/control_bak.ctl /u01/app/oracle/fast_recovery_area/PROD/control02.ctl

起到mount

SQL> startup mount;

恢复数据库提示使用BACKUP CONTROLFILE

SQL> recover database;

ORA-00283: recovery session canceled due to errors

ORA-01610: recovery using the BACKUP CONTROLFILE option must be done

如果采取默认放回回复，那么会提示无法获取文件状态，系统会告诉你无法打开归档文件，这时候就需要找出相对应的current的redo,并在恢复的时候指定路径

SQL> recover database using BACKUP CONTROLFILE until cancel;

指定当前redo路径进行恢复成功

SQL> select group#,status from v$log where status='CURRENT';

GROUP# STATUS

---------- ----------------

3 CURRENT

SQL> select group#,member from v$logfile where group#=3;

GROUP#

----------

MEMBER

--------------------------------------------------------------------------------

3

/u01/app/oracle/oradata/PROD/redo03.log #为需要制定路径

根据路径进行恢复，成功

SQL> recover database using BACKUP CONTROLFILE until cancel;

ORA-00279: change 1011480 generated at 01/17/2016 21:44:43 needed for thread 1

ORA-00289: suggestion :

/u01/app/oracle/fast_recovery_area/PROD/archivelog/2016_01_17/o1_mf_1_3_%u_.arc

ORA-00280: change 1011480 for thread 1 is in sequence #3

Specify log: {=suggested | filename | AUTO | CANCEL}

/u01/app/oracle/oradata/PROD/redo03.log

Log applied.

Media recovery complete.

打开数据库

SQL> alter database open resetlogs;

ITPub博客

控制文件的恢复初步整理

最新文章