控制文件丢失恢复实战操作

控制文件丢失恢复

查看数据库基本信息:

cd /u02/backup/    控制文件自动备份目录

/u01/app/oracle/oradata/CDB1/control01.ctl, 
/u01/app/oracle/oradata/CDB1/control02.ctl

tail -f  /u01/app/oracle/diag/rdbms/cdb1/cdb1/trace/alert_cdb1.log  --查看数据库日志

select open_mode,database_role from v$database;  --查看启动状态
select * from v$diag_info;  --查看日志路径

为什么要用resetlogs打开数据库呢？

因为用了备份控制文件，recover命令只能修复截止到 控制文件备份时的数据库物理结构信息，而无法修改控制文件中的当前重做日志的序列号等信息，recover命令执行完毕后，控制文件中当前在线日志序列号还是陈旧的（是当初备份时的）。若按常规方式打开数据库，将报错，Oracle采用重设日志功能，日志序列号就从1重新开始。

情形一：如果损坏了全部控制文件，那么需要重新创建控制文件或从备份恢复

RMAN>startup nomount

RMAN> restore controlfile from autobackup;
--恢复报错：because DBID was not set
Starting restore at 15-MAR-22
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=13 device type=DISK

recovery area destination: /u02/oradata
database name (or database unique name) used for search: CDB1
channel ORA_DISK_1: no AUTOBACKUPS found in the recovery area
AUTOBACKUP search with format "%F" not attempted because DBID was not set
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of restore command at 03/15/2022 13:48:19
RMAN-06172: no AUTOBACKUP found or specified handle is not a valid copy or piece

如果能找到DBID设置后继续恢复
CDB1 (DBID=983951798)

RMAN> set dbid 983951798

RMAN> restore controlfile from autobackup;      此时仍未找到自动备份文件--可能因为在RMAN的CONFIGURE中未设置控制文件为自动备份也没设置备份目录。


未找到7天内的控制文件：
RMAN> restore controlfile from autobackup;

Starting restore at 15-MAR-22
using channel ORA_DISK_1

recovery area destination: /u02/oradata
database name (or database unique name) used for search: CDB1
channel ORA_DISK_1: no AUTOBACKUPS found in the recovery area
channel ORA_DISK_1: looking for AUTOBACKUP on day: 20220315
channel ORA_DISK_1: looking for AUTOBACKUP on day: 20220314
channel ORA_DISK_1: looking for AUTOBACKUP on day: 20220313
channel ORA_DISK_1: looking for AUTOBACKUP on day: 20220312
channel ORA_DISK_1: looking for AUTOBACKUP on day: 20220311
channel ORA_DISK_1: looking for AUTOBACKUP on day: 20220310
channel ORA_DISK_1: looking for AUTOBACKUP on day: 20220309
channel ORA_DISK_1: no AUTOBACKUP in 7 days found
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of restore command at 03/15/2022 14:26:04
RMAN-06172: no AUTOBACKUP found or specified handle is not a valid copy or piece

通过指定的备份目录进行恢复：

RMAN> restore controlfile from '/u02/backup/c-983951798-20220314-00.CTL';

*注意：控制文件自动备份被更改到以下目录，所以上述操作未找到备份（非默认恢复目录）：

cd /u02/backup/    控制文件自动备份目录

**查看默认恢复目录（nomount无法查看备份集信息，可能被更改）：

SQL> show parameter recover;
NAME				     TYPE	 VALUE
db_recovery_file_dest		     string	 /u02/oradata
db_recovery_file_dest_size	     big integer 10G
db_unrecoverable_scn_tracking	     boolean	 TRUE
recovery_parallelism		     integer	 0
remote_recovery_file_dest	     string

启动到mount状态，recover恢复，再开启到open

RMAN> alter database mount;
released channel: ORA_DISK_1
Statement processed

RMAN> recover database;

RMAN> alter database open resetlogs;

建议平时收集数据库台账信息，因为启动到nomount下，无法查看DBID和控制文件自动备份位置等信息

数据库台账信息：
1）list backup;      --备份的文件种类及位置
2）DBID信息；      --DBID=983951798
3）..

情形二：如果控制文件有多个，而只损坏了单个控制文件，那么只需要关闭数据库，拷贝其它好的控制文件覆盖掉坏的控制文件即可

复制控制文件: cp control02.ctl control01.ctl 

更改文件权限：
chown -R grid:oinstall /u02/backup/c-983951798-20220314-00.CTL