本文详细介绍了在Oracle数据库中遇到控制文件丢失或冗余的情况时，如何进行故障恢复。包括使用操作系统层拷贝恢复、修改SPFILE配置、从备份恢复以及在无冗余无备份时手动创建控制文件的步骤。每个方法的风险评估和关键步骤都进行了说明，对于数据库管理员来说是宝贵的故障处理指南。
测试环境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大小
扫一扫
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
