Home > Oracle > 正文

有备份有归档模式下控制文件丢失故障处理

故障类别
控制文件-11-10
归档、有备份、意外删除
归档、无备份、意外删除
无归档、意外删除
控制文件简介
使用:
    在数据库从started状态启动到mount状态的时候读取控制文件。
作用:
    记录创建数据库的时间戳
    数据文件的位置
    联机日志文件位置
    表空间信息
    日志历史记录(log history)
    归档日志信息
    备份信息
    当前日志序列号(log sequence number)
    检查点信息(checkpoint)
更改
    当数据库增加、重命名、删除一个数据文件或一个联机日志文件时,
    oracle服务器进程(server process)会立即更新控制文件,
    日志写进程(LGWR)会把当前日志序列号记录到控制文件中,
    检查点进程(CKPT)把检查点信息记录到控制文件中,

    归档进程(ArchN)把归档信息记录到控制文件中。

controlfile error
%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
--------------------------有归档,有备份,意外删除----------------------

查看数据库归档模式

SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 12
Next log sequence to archive 14
Current log sequence 14

配置RMAN

RMAN> configure controlfile autobackup format for device type disk to '/home/oracle/rmanbak/%F.ctl';

new RMAN configuration parameters:
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '/home/oracle/rmanbak/%F.ctl';
new RMAN configuration parameters are successfully stored

RMAN> configure channel device type disk format '/home/oracle/rmanbak/%d_%I_%s_%p_%T.bkp';

new RMAN configuration parameters:
CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT '/home/oracle/rmanbak/%d_%I_%s_%p_%T.bkp';
new RMAN configuration parameters are successfully stored

查看RMAN配置

RMAN> show all;

RMAN configuration parameters for database with db_unique_name ORCL are:
CONFIGURE RETENTION POLICY TO REDUNDANCY 1; # default
CONFIGURE BACKUP OPTIMIZATION OFF; # default
CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default
CONFIGURE CONTROLFILE AUTOBACKUP ON;
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '/home/oracle/rmanbak/%F.ctl';
CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO BACKUPSET; # default
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT '/home/oracle/rmanbak/%d_%I_%s_%p_%T.bkp';
CONFIGURE MAXSETSIZE TO UNLIMITED; # default
CONFIGURE ENCRYPTION FOR DATABASE OFF; # default
CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default
CONFIGURE COMPRESSION ALGORITHM 'BASIC' AS OF RELEASE 'DEFAULT' OPTIMIZE FOR LOAD TRUE ; # default
CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default
CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/u01/app/oracle/product/11.2.0/dbhome_1/dbs/snapcf_orcl.f'; # default

备份整个数据库和控制文件

RMAN> backup as compressed backupset database;

Starting backup at 09-NOV-2016 15:42:23
using channel ORA_DISK_1
channel ORA_DISK_1: starting compressed full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00001 name=/u01/app/oracle/oradata/orcl/system01.dbf
input datafile file number=00002 name=/u01/app/oracle/oradata/orcl/sysaux01.dbf
input datafile file number=00005 name=/u01/app/oracle/oradata/orcl/example01.dbf
input datafile file number=00003 name=/u01/app/oracle/oradata/orcl/undotbs01.dbf
input datafile file number=00004 name=/u01/app/oracle/oradata/orcl/users01.dbf
input datafile file number=00006 name=/u01/app/oracle/oradata/orcl/tbs_ctrl.dbf
channel ORA_DISK_1: starting piece 1 at 09-NOV-2016 15:42:23
channel ORA_DISK_1: finished piece 1 at 09-NOV-2016 15:43:08
piece handle=/home/oracle/rmanbak/ORCL_1454751289_2_1_20161109.bkp tag=TAG20161109T154223 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:45
Finished backup at 09-NOV-2016 15:43:08

Starting Control File and SPFILE Autobackup at 09-NOV-2016 15:43:08
piece handle=/home/oracle/rmanbak/c-1454751289-20161109-00.ctl comment=NONE
Finished Control File and SPFILE Autobackup at 09-NOV-2016 15:43:09

查看备份


RMAN> list backup;

List of Backup Sets
===================

BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ --------------------
1 Full 293.66M DISK 00:00:43 09-NOV-2016 15:43:06
BP Key: 1 Status: AVAILABLE Compressed: YES Tag: TAG20161109T154223
Piece Name: /home/oracle/rmanbak/ORCL_1454751289_2_1_20161109.bkp
List of Datafiles in backup set 1
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- -------------------- ----
1 Full 1017159 09-NOV-2016 15:42:23 /u01/app/oracle/oradata/orcl/system01.dbf
2 Full 1017159 09-NOV-2016 15:42:23 /u01/app/oracle/oradata/orcl/sysaux01.dbf
3 Full 1017159 09-NOV-2016 15:42:23 /u01/app/oracle/oradata/orcl/undotbs01.dbf
4 Full 1017159 09-NOV-2016 15:42:23 /u01/app/oracle/oradata/orcl/users01.dbf
5 Full 1017159 09-NOV-2016 15:42:23 /u01/app/oracle/oradata/orcl/example01.dbf
6 Full 1017159 09-NOV-2016 15:42:23 /u01/app/oracle/oradata/orcl/tbs_ctrl.dbf

BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ --------------------
2 Full 9.36M DISK 00:00:01 09-NOV-2016 15:43:09
BP Key: 2 Status: AVAILABLE Compressed: NO Tag: TAG20161109T154308
Piece Name: /home/oracle/rmanbak/c-1454751289-20161109-00.ctl
SPFILE Included: Modification time: 09-NOV-2016 15:04:13
SPFILE db_unique_name: ORCL
Control File Included: Ckp SCN: 1017180 Ckp time: 09-NOV-2016 15:43:08

RMAN>

[oracle@oracle rmanbak]$ ls -l
total 310312
-rw-r----- 1 oracle oinstall 9830400 Nov 9 15:43 c-1454751289-20161109-00.ctl
-rw-r----- 1 oracle oinstall 307929088 Nov 9 15:43 ORCL_1454751289_2_1_20161109.bkp
[oracle@oracle rmanbak]$

删除控制文件模拟控制文件丢失


SQL> select name from v$controlfile;

NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/orcl/control01.ctl
/u01/app/oracle/fast_recovery_area/orcl/control02.ctl

SQL>

SQL> !mv /u01/app/oracle/oradata/orcl/control01.ctl /u01/app/oracle/oradata/orcl/control01.ctl.bak

SQL> !mv /u01/app/oracle/fast_recovery_area/orcl/control02.ctl /u01/app/oracle/fast_recovery_area/orcl/control02.ctl.bak


查看数据库日志,已经开始报错了

tail -f /u01/app/oracle/diag/rdbms/orcl/orcl/trace/alert_orcl.log

Wed Nov 09 15:51:33 2016
Errors in file /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_m001_43325.trc:
ORA-00210: cannot open the specified control file
ORA-00202: control file: '/u01/app/oracle/oradata/orcl/control01.ctl'
ORA-27041: unable to open file
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
Wed Nov 09 16:00:33 2016
Errors in file /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_m001_43414.trc:
ORA-00210: cannot open the specified control file
ORA-00202: control file: '/u01/app/oracle/oradata/orcl/control01.ctl'
ORA-27041: unable to open file
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3

因为控制文件丢失,只能强制关闭数据库,然后启动到nomount状态

SQL> shutdown abort;
ORACLE instance shut down.
SQL> startup nomount;
ORACLE instance started.

Total System Global Area 1064763392 bytes
Fixed Size 2260040 bytes
Variable Size 666895288 bytes
Database Buffers 390070272 bytes
Redo Buffers 5537792 bytes

在RMAN里恢复先前备份的控制文件

RMAN> restore controlfile from autobackup;

Starting restore at 09-NOV-2016 16:30:47
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=10 device type=DISK

recovery area destination: /u01/app/oracle/fast_recovery_area
database name (or database unique name) used for search: ORCL
channel ORA_DISK_1: AUTOBACKUP /u01/app/oracle/fast_recovery_area/ORCL/autobackup/2016_11_08/o1_mf_s_927409085_d23o9xdk_.bkp found in the recovery area
AUTOBACKUP search with format "%F" not attempted because DBID was not set
channel ORA_DISK_1: restoring control file from AUTOBACKUP /u01/app/oracle/fast_recovery_area/ORCL/autobackup/2016_11_08/o1_mf_s_927409085_d23o9xdk_.bkp
channel ORA_DISK_1: control file restore from AUTOBACKUP complete
output file name=/u01/app/oracle/oradata/orcl/control01.ctl
output file name=/u01/app/oracle/fast_recovery_area/orcl/control02.ctl
Finished restore at 09-NOV-2016 16:30:50

使用控制文件恢复数据库

因为数据库关闭之前最后一个日志序列是7,数据库关闭之前没有进行归档,所以试试在线日志

SQL> recover database using backup controlfile;
ORA-00279: change 1017159 generated at 11/09/2016 15:42:23 needed for thread 1
ORA-00289: suggestion :
/u01/app/oracle/fast_recovery_area/ORCL/archivelog/2016_11_09/o1_mf_1_7_%u_.arc
ORA-00280: change 1017159 for thread 1 is in sequence #7

Specify log: {=suggested | filename | AUTO | CANCEL}
/u01/app/oracle/oradata/orcl/redo01.log
Log applied.
Media recovery complete.

因为使用了备份的控制文件,打开数据库使用resetlogs选项

SQL> alter database open resetlogs;

Database altered.

SQL> select instance_name,status,to_char(startup_time,'yyyy-mm-dd hh24:mi:ss') from v$instance;

INSTANCE_NAME STATUS TO_CHAR(STARTUP_TIM
---------------- ------------ -------------------
orcl OPEN 2016-11-09 16:29:56

以上,数据库控制文件重建完成。

上一篇:无备份有归档模式下控制文件丢失故障处理
下一篇:kernel.shmmax参数详解
XX平台数据库无法启动CRS-2674  HAIP

XX平台数据库无法启动CRS-2674 HAIP

无备份有归档模式下控制文件丢失故障处理

无备份有归档模式下控制文件丢失故障处理

无备份无归档模式下控制文件丢失故障处理

无备份无归档模式下控制文件丢失故障处理

Oracle 热备份与恢复实验

Oracle 热备份与恢复实验

Oracle闪回查询/闪回恢复

Oracle闪回查询/闪回恢复

About Me

About Me

Oracle 常用表

Oracle 常用表

发表评论

昵称
邮箱
网址

沙发空闲中,快来抢!