控制文件丢失

测试环境oracle版本：19.3

场景1：控制文件有冗余

1、构造故障场景

（1）查看当前控制文件配置

SQL> show parameter control_files

（2）构造冗余的控制文件

SQL> alter system set control_files='/oradata/CC/control01.ctl','/oracle/oradata/control02.ctl' scope=spfile;

SQL> shutdown immediate

$ cp /oradata/CC/control01.ctl /oracle/oradata/control02.ctl

SQL> startup

SQL> show parameter control_files

（3）构造故障

$ rm -f /oracle/oradata/control02.ctl

SQL> shutdown immediate

2、解决思路

由于一个控制文件存在，我们可以采取操作系统层拷贝的方式恢复；也可以修改spfile为一个控制文件的配置

3、解决步骤

方法1： 拷贝剩余控制文件到另一个目录

SQL> shutdown abort

$ cp /oradata/CC/control01.ctl /oracle/oradata/control02.ctl

SQL> startup

检查alert日志，确保没有控制文件相关报错

此方法较为直观简单；分钟级即可恢复，无风险

方法2：修改control_files参数为剩余的控制文件配置

SQL> shutdown abort

SQL> startup nomount

SQL> alter system set control_files='/oradata/CC/control01.ctl' scope=spfile;

SQL> shutdown immediate

SQL> startup

SQL> show parameter control_files

4、风险预估

此方法涉及修改spfile，比方法1稍复杂；分钟级即可恢复，无风险

场景2：控制文件无冗余有备份（归档开启）

若控制文件全部丢失，但是有备份，可以利用备份恢复控制文件

1、构造故障场景

（1）备份控制文件

RMAN> backup current controlfile;

上面的是控制文件备份集，下面的是自动备份，两个都可以作为备份集恢复控制文件

或者查看所有的控制文件备份：

RMAN> crosscheck backup of controlfile;

（2）构造故障

SQL> show parameter control_files

$ rm -f /oradata/CC/control01.ctl

SQL> shutdown immediate

2、解决思路：

需要检查是否有控制文件的最新备份，将备份恢复，再恢复数据库即可

3、解决步骤

SQL> shutdown abort

$ rman target /

RMAN> startup nomount

查看控制文件备份：以下显示控制文件自动备份到$ORACLE_HOME/dbs下

RMAN> show all;

$ cd $ORACLE_HOME/dbs

$ ls -ltr

DBID=2288695261

$ rman target /

RMAN> set dbid=2288695261;

RMAN> restore controlfile from autobackup;

RMAN> alter database mount;

RMAN> recover database;

 RMAN> alter database open resetlogs;

SQL> select open_mode from v$database;

4、风险预估

前提是有最新的控制文件的备份，且归档无缺失，此种情况下无数据丢失，恢复的时间取决于redo大小

场景3：无冗余无备份

未开启归档、控制文件没有备份、没有冗余的情况下，手动创建控制文件

1、构造故障场景

（1）关归档

SQL> shutdown immediate

SQL> startup mount

SQL> alter database noarchivelog;

SQL> alter database open;

SQL> archive log list

（2）构造故障

SQL> show parameter control_files

 $ rm -f /oradata/CC/control01.ctl

SQL> shutdown immediate

2、解决思路

控制文件按照固定格式创建控制文件，手动创建前确定库名、字符集、logfile位置、datafile位置

3、解决步骤

SQL> shutdown abort

SQL> startup nomount

（1）创建控制文件

SQL> CREATE CONTROLFILE REUSE DATABASE "CC" NORESETLOGS FORCE LOGGING NOARCHIVELOG

MAXLOGFILES 16

MAXLOGMEMBERS 3

MAXDATAFILES 1000

MAXINSTANCES 8

MAXLOGHISTORY 2920

LOGFILE

group 1 '/oradata/CC/redo01.log' size 2G BLOCKSIZE 512,

group 2 '/oradata/CC/redo02.log' size 2G BLOCKSIZE 512,

group 3 '/oradata/CC/redo03.log' size 2G BLOCKSIZE 512,

group 4 '/oradata/CC/redo04.log' size 2G BLOCKSIZE 512,

group 5 '/oradata/CC/redo05.log' size 2G BLOCKSIZE 512,

group 6 '/oradata/CC/redo06.log' size 2G BLOCKSIZE 512,

group 7 '/oradata/CC/redo07.log' size 2G BLOCKSIZE 512,

group 8 '/oradata/CC/redo08.log' size 2G BLOCKSIZE 512,

group 9 '/oradata/CC/redo09.log' size 2G BLOCKSIZE 512

-- STANDBY LOGFILE

-- GROUP 11 '/oradata/dgtests/redo11.log' SIZE 500M BLOCKSIZE 512,

-- GROUP 13 '/oradata/dgtests/redo13.log' SIZE 500M BLOCKSIZE 512

DATAFILE

'/oradata/CC/idx_abc_001.dbf',

'/oradata/CC/idx_abc_002.dbf',

'/oradata/CC/idx_apig_001.dbf',

'/oradata/CC/idx_balc_001.dbf',

'/oradata/CC/idx_bcsc_001.dbf',

'/oradata/CC/idx_btc_001.dbf',

'/oradata/CC/idx_cc_001.dbf',

'/oradata/CC/idx_cc_02.dbf',

'/oradata/CC/idx_cic_001.dbf',

'/oradata/CC/idx_comc_001.dbf',

'/oradata/CC/idx_cpc_001.dbf',

'/oradata/CC/idx_crm_001.dbf',

'/oradata/CC/idx_ctr_001.dbf',

'/oradata/CC/idx_dap_001.dbf',

'/oradata/CC/idx_digchan_001.dbf',

'/oradata/CC/idx_drm_001.dbf',

'/oradata/CC/idx_ecclub_001.dbf',

'/oradata/CC/idx_ecinv_001.dbf',

'/oradata/CC/idx_ecord_001.dbf',

'/oradata/CC/idx_ecprod_001.dbf',

'/oradata/CC/idx_esales_001.dbf',

'/oradata/CC/idx_etl_001.dbf',

'/oradata/CC/idx_ftf_001.dbf',

'/oradata/CC/idx_inv_001.dbf',

'/oradata/CC/idx_inv_002.dbf',

'/oradata/CC/idx_jnc_001.dbf',

'/oradata/CC/idx_med_001.dbf',

'/oradata/CC/idx_mid_001.dbf',

'/oradata/CC/idx_pbc_001.dbf',

'/oradata/CC/idx_pcb_001.dbf',

'/oradata/CC/idx_pcc_001.dbf',

'/oradata/CC/idx_prc_001.dbf',

'/oradata/CC/idx_quot_001.dbf',

'/oradata/CC/idx_rbc_001.dbf',

'/oradata/CC/idx_rbc_002.dbf',

'/oradata/CC/idx_rlc_001.dbf',

'/oradata/CC/idx_rpt_001.dbf',

'/oradata/CC/idx_sett_001.dbf',

'/oradata/CC/idx_sfa_001.dbf',

'/oradata/CC/idx_sic_001.dbf',

'/oradata/CC/idx_spc_001.dbf',

'/oradata/CC/idx_spn_001.dbf',

'/oradata/CC/idx_str_001.dbf',

'/oradata/CC/idx_ucc_001.dbf',

'/oradata/CC/idx_uosflow_001.dbf',

'/oradata/CC/idx_vcoff_001.dbf',

'/oradata/CC/sysaux01.dbf',

'/oradata/CC/system01.dbf',

'/oradata/CC/tab_abc_001.dbf',

'/oradata/CC/tab_abc_002.dbf',

'/oradata/CC/tab_abc_003.dbf',

'/oradata/CC/tab_apig_001.dbf',

'/oradata/CC/tab_balc_002.dbf',

'/oradata/CC/tab_bcsc_001.dbf',

'/oradata/CC/tab_btc_001.dbf',

'/oradata/CC/tab_cc_001.dbf',

'/oradata/CC/tab_cc_002.dbf',

'/oradata/CC/tab_cc_003.dbf',

'/oradata/CC/tab_cic_001.dbf',

'/oradata/CC/tab_comc_001.dbf',

'/oradata/CC/tab_cpc_001.dbf',

'/oradata/CC/tab_crm_001.dbf',

'/oradata/CC/tab_ctr_001.dbf',

'/oradata/CC/tab_dap_001.dbf',

'/oradata/CC/tab_digchan_001.dbf',

'/oradata/CC/tab_drm_001.dbf',

'/oradata/CC/tab_ecclub_001.dbf',

'/oradata/CC/tab_ecinv_001.dbf',

'/oradata/CC/tab_ecord_001.dbf',

'/oradata/CC/tab_ecprod_001.dbf',

'/oradata/CC/tab_esales_001.dbf',

'/oradata/CC/tab_etl_001.dbf',

'/oradata/CC/tab_ftf_001.dbf',

'/oradata/CC/tab_int_001.dbf',

'/oradata/CC/tab_inv_001.dbf',

'/oradata/CC/tab_inv_002.dbf',

'/oradata/CC/tab_inv_003.dbf',

'/oradata/CC/tab_jnc_001.dbf',

'/oradata/CC/tab_med_001.dbf',

'/oradata/CC/tab_mid_001.dbf',

'/oradata/CC/tab_pbc_001.dbf',

'/oradata/CC/tab_pcb_001.dbf',

'/oradata/CC/tab_pcc_001.dbf',

'/oradata/CC/tab_prc_001.dbf',

'/oradata/CC/tab_quot_001.dbf',

'/oradata/CC/tab_rbc_001.dbf',

'/oradata/CC/tab_rbc_002.dbf',

'/oradata/CC/tab_rbc_003.dbf',

'/oradata/CC/tab_rbc_004.dbf',

'/oradata/CC/tab_rlc_001.dbf',

'/oradata/CC/tab_rpt_001.dbf',

'/oradata/CC/tab_sett_001.dbf',

'/oradata/CC/tab_sfa_001.dbf',

'/oradata/CC/tab_sic_001.dbf',

'/oradata/CC/tab_spc_001.dbf',

'/oradata/CC/tab_spn_001.dbf',

'/oradata/CC/tab_str_001.dbf',

'/oradata/CC/tab_ucc_001.dbf',

'/oradata/CC/tab_uosflow_001.dbf',

'/oradata/CC/tab_vcoff_001.dbf',

'/oradata/CC/tab_vcoff_002.dbf',

'/oradata/CC/test1.dbf',

'/oradata/CC/test2.dbf',

'/oradata/CC/test.dbf',

'/oradata/CC/undotbs01.dbf',

'/oradata/CC/undotbs02.dbf',

'/oradata/CC/undotbs03.dbf',

'/oradata/CC/users01.dbf'

CHARACTER SET AL32UTF8

;

按照此格式创建控制文件，格式都是一样的，要修改的一般是数据库名、是否归档、字符集等

然后datafile和logfile根据实际位置和大小填充（千万不要漏掉数据文件）

创建控制文件后，实例会自动mount

（2）recover database并open

SQL> recover database;

SQL> alter database open;

（3）添加tempfile

ALTER TABLESPACE TEMP ADD TEMPFILE '/oradata/CC/temp01.dbf' REUSE,'/oradata/CC/temp02.dbf' REUSE;

检查alert日志，若有报错，进行相应处理

4、风险评估

单控制文件丢失不会造成数据丢失，恢复的时间取决于redo大小