oracle 控制文件恢复

• 如果只丢失了一个控制文件，那么可以把另一个控制文件拷贝一份来恢复
• 控制文件恢复主要分为两大步奏，原始文件恢复和数据库同步scn号

1.sql语句恢复

在数据库还未崩溃时可以使用alter database backup controlfile to trace; 命令输出创建控制文件命令。

编辑其中内容删除注释和空格，修改为.sql文件，关闭数据库在sqlplus下面执行.sql文件恢复。

1.备份sql

alter database backup controlfile to trace as   '/opt/oracle/control.sql';

2.编辑sql文本

示例

STARTUP NOMOUNT
CREATE CONTROLFILE REUSE DATABASE "ORCLCDB" NORESETLOGS  ARCHIVELOG
    MAXLOGFILES 16
    MAXLOGMEMBERS 3
    MAXDATAFILES 1024
    MAXINSTANCES 8
    MAXLOGHISTORY 292
LOGFILE
  GROUP 1 '/opt/oracle/oradata/ORCLCDB/redo01.log'  SIZE 200M BLOCKSIZE 512,
  GROUP 2 '/opt/oracle/oradata/ORCLCDB/redo02.log'  SIZE 200M BLOCKSIZE 512,
  GROUP 3 '/opt/oracle/oradata/ORCLCDB/redo03.log'  SIZE 200M BLOCKSIZE 512
DATAFILE
  '/opt/oracle/oradata/ORCLCDB/system01.dbf',
  '/opt/oracle/oradata/ORCLCDB/sysaux01.dbf',
  '/opt/oracle/oradata/ORCLCDB/undotbs01.dbf',
  '/opt/oracle/oradata/ORCLCDB/pdbseed/system01.dbf',
  '/opt/oracle/oradata/ORCLCDB/pdbseed/sysaux01.dbf',
  '/opt/oracle/oradata/ORCLCDB/users01.dbf',
  '/opt/oracle/oradata/ORCLCDB/pdbseed/undotbs01.dbf',
  '/opt/oracle/oradata/ORCLCDB/ORCLPDB1/system01.dbf',
  '/opt/oracle/oradata/ORCLCDB/ORCLPDB1/sysaux01.dbf',
  '/opt/oracle/oradata/ORCLCDB/ORCLPDB1/undotbs01.dbf',
  '/opt/oracle/oradata/ORCLCDB/ORCLPDB1/users01.dbf',
  '/opt/oracle/oradata/ORCLCDB/ORCLCDB/datafile/o1_mf_test_hdjmr3x1_.dbf',
  '/opt/oracle/oradata/ORCLCDB/ORCLCDB/datafile/o1_mf_how2java_hhptd4c3_.dbf',
  '/opt/oracle/oradata/ORCLCDB/ORCLCDB/datafile/o1_mf_fixf_hk13ffnd_.dbf',
  '/opt/oracle/oradata/ORCLCDB/ORCLCDB/ACA89A0A9F103B53E055F8163E1B9FA5/datafile/o1_mf_system_hm704xwz_.dbf',
  '/opt/oracle/oradata/ORCLCDB/ORCLCDB/ACA89A0A9F103B53E055F8163E1B9FA5/datafile/o1_mf_sysaux_hm704xx0_.dbf',
  '/opt/oracle/oradata/ORCLCDB/ORCLCDB/ACA89A0A9F103B53E055F8163E1B9FA5/datafile/o1_mf_undotbs1_hm704xx1_.dbf'
CHARACTER SET AL32UTF8
;
VARIABLE RECNO NUMBER;
EXECUTE :RECNO := SYS.DBMS_BACKUP_RESTORE.SETCONFIG('BACKUP OPTIMIZATION','ON');
VARIABLE RECNO NUMBER;
EXECUTE :RECNO := SYS.DBMS_BACKUP_RESTORE.SETCONFIG('RETENTION POLICY','TO REDUNDANCY 7');
ALTER DATABASE REGISTER LOGFILE '/opt/oracle/fast_recovery_area/ORCLCDB/archivelog/2020_09_14/o1_mf_1_1_%u_.arc';
ALTER DATABASE REGISTER LOGFILE '/opt/oracle/fast_recovery_area/ORCLCDB/archivelog/2020_09_14/o1_mf_1_1_%u_.arc';
RECOVER DATABASE
ALTER SYSTEM ARCHIVE LOG ALL;
ALTER DATABASE OPEN;
ALTER PLUGGABLE DATABASE ALL OPEN;
ALTER TABLESPACE TEMP ADD TEMPFILE '/opt/oracle/oradata/ORCLCDB/temp01.dbf'
     SIZE 130023424  REUSE AUTOEXTEND ON NEXT 655360  MAXSIZE 32767M;
ALTER SESSION SET CONTAINER = PDB$SEED;
ALTER TABLESPACE TEMP ADD TEMPFILE '/opt/oracle/oradata/ORCLCDB/pdbseed/temp012020-03-02_10-33-28-431-AM.dbf'
     SIZE 65011712  REUSE AUTOEXTEND ON NEXT 655360  MAXSIZE 32767M;
ALTER SESSION SET CONTAINER = ORCLPDB1;
ALTER TABLESPACE TEMP ADD TEMPFILE '/opt/oracle/oradata/ORCLCDB/ORCLPDB1/temp01.dbf' REUSE;
ALTER SESSION SET CONTAINER = PDB1;
ALTER TABLESPACE TEMP ADD TEMPFILE '/opt/oracle/oradata/ORCLCDB/ORCLCDB/ACA89A0A9F103B53E055F8163E1B9FA5/datafile/o1_mf_temp_hm704xx1_.dbf' REUSE;
ALTER SESSION SET CONTAINER = CDB$ROOT;

3.执行恢复

 sqlplus / as sysdba
 SQL> shutdown abort;
ORACLE instance shut down.
SQL> @controlfile.sql
ORACLE instance started.
Total System Global Area  339737680 bytes
Fixed Size            8895568 bytes
Variable Size          272629760 bytes
Database Buffers       50331648 bytes
Redo Buffers            7880704 bytes
Control file created.
PL/SQL procedure successfully completed.
PL/SQL procedure successfully completed.

2.文件拷贝恢复

在数据库尚未关闭的情况下，使用alter database backup controlfile to ‘/路径/controlfile_name.bak’ 备份控制文件，而后cp控制文件到原目录下重新命名为原来的名称，在执行数据库恢复。

1.备份控制文件

alter database backup controlfile to  '/opt/oracle/controlfile.bak';

2.数据库恢复

(1)关闭数据库并重新启动到mount状态。
shutdown abort, startup mount.
(2)尝试alter database open;打开数据库

（3）尝试alter database open resetlogs 打开数据库

（4）先使用recover database;无法使用则使用recover database using backup controlfile恢复数据库，同步数据库scn号，
之后尝试输入所有redo.log 文件路径，直到提示media recovery complete.

（5）alter database open resetlogs;打开数据库.

3.rman 自动备份恢复

在有rman备份并且之后没有改变过表空间结构的情况下，可以使用rman恢复控制文件，之后进行数据库恢复

[root@instance-ej1hspdt ORCLCDB]# rm -rf control02.ctl 
RMAN> restore   controlfile from autobackup;从自动备份恢复 
RMAN> alter database mount;    修改数据库为mount                                                                                                  
released channel: ORA_DISK_1                                                                                                     
Statement processed  
RMAN> recover database;  数据库恢复同步scn
RMAN> alter database  open resetlogs;

4.rman 自动诊断工具恢复

list failure;
advise failure;
repair failure preview;
repair failure;

5.控制文件快照恢复

restore controlfile from '/opt/oracle/product/19c/dbhome_1/dbs/snapcf_ORCLCDB.f';
-------------------------------------------------------------------------------
list failure;
advise failure;
repair failure preview;
repair failure;