Oracle 重建controlfile

1、备份数据库控制文件
sqlplus / as sysdba
start mount
alter database backup controlfile to trace as '/home/oradev/dev_ctl.sql'
exit;


2、创建重建控制文件的脚本：
复制备份的控制文件的Create controlfile这一段，修改如下：
CREATE CONTROLFILE REUSE DATABASE "DEV" NORESETLOGS NOARCHIVELOG
MAXLOGFILES 32
MAXLOGMEMBERS 5
MAXDATAFILES 512
MAXINSTANCES 8
MAXLOGHISTORY 14607
LOGFILE
GROUP 1 (
'/u01/dev/db/data/log01a.dbf',
'/u01/dev/db/data/log01b.dbf'
) SIZE 1000M BLOCKSIZE 512,
GROUP 2 (
'/u01/dev/db/data/log02a.dbf',
'/u01/dev/db/data/log02b.dbf'
) SIZE 1000M BLOCKSIZE 512
-- STANDBY LOGFILE
DATAFILE
'/u01/dev/db/data/system01.dbf',
'/u01/dev/db/data/system02.dbf',
'/u01/dev/db/data/system03.dbf',
'/u01/dev/db/data/system04.dbf',
'/u01/dev/db/data/system05.dbf',
'/u01/dev/db/data/ctxd01.dbf',
'/u01/dev/db/data/owad01.dbf',
'/u01/dev/db/data/a_queue02.dbf',
'/u01/dev/db/data/odm.dbf',
'/u01/dev/db/data/olap.dbf',
'/u01/dev/db/data/sysaux01.dbf',
'/u01/dev/db/data/apps_ts_tools01.dbf',
'/u01/dev/db/data/system12.dbf',
'/u01/dev/db/data/a_txn_data04.dbf',
'/u01/dev/db/data/a_txn_ind06.dbf',
'/u01/dev/db/data/a_ref03.dbf',
'/u01/dev/db/data/a_int02.dbf',
'/u01/dev/db/data/sysaux02.dbf',
'/u01/dev/db/data/system13.dbf',
'/u01/dev/db/data/system14.dbf',
'/u01/dev/db/data/system15.dbf',
'/u01/dev/db/data/system16.dbf',
'/u01/dev/db/data/system17.dbf',
'/u01/dev/db/data/system18.dbf',
'/u01/dev/db/data/system19.dbf',
'/u01/dev/db/data/system20.dbf',
'/u01/dev/db/data/system21.dbf',
'/u01/dev/db/data/system22.dbf',
'/u01/dev/db/data/system23.dbf',
'/u01/dev/db/data/system24.dbf',
'/u01/dev/db/data/system25.dbf',
'/u01/dev/db/data/a_ref04.dbf',
'/u01/dev/db/data/a_ref05.dbf',
'/u01/dev/db/data/a_ref06.dbf',
'/u01/dev/db/data/undo02.dbf',
'/u01/dev/db/data/a_txn_data05.dbf',
'/u01/dev/db/data/a_txn_data06.dbf',
'/u01/dev/db/data/a_txn_ind07.dbf',
'/u01/dev/db/data/a_txn_ind08.dbf',
'/u01/dev/db/data/undo03.dbf',
'/home/oradev/oradump/hec.dbf',
'/u01/dev/db/data/system10.dbf',
'/u01/dev/db/data/system06.dbf',
'/u01/dev/db/data/portal01.dbf',
'/u01/dev/db/data/system07.dbf',
'/u01/dev/db/data/system09.dbf',
'/u01/dev/db/data/system08.dbf',
'/u01/dev/db/data/system11.dbf',
'/u01/dev/db/data/undo01.dbf',
'/u01/dev/db/data/a_txn_data01.dbf',
'/u01/dev/db/data/a_txn_ind01.dbf',
'/u01/dev/db/data/a_ref01.dbf',
'/u01/dev/db/data/a_int01.dbf',
'/u01/dev/db/data/a_summ01.dbf',
'/u01/dev/db/data/a_nolog01.dbf',
'/u01/dev/db/data/a_archive01.dbf',
'/u01/dev/db/data/a_queue01.dbf',
'/u01/dev/db/data/a_media01.dbf',
'/u01/dev/db/data/a_txn_data02.dbf',
'/u01/dev/db/data/a_txn_data03.dbf',
'/u01/dev/db/data/a_txn_ind02.dbf',
'/u01/dev/db/data/a_txn_ind03.dbf',
'/u01/dev/db/data/a_txn_ind04.dbf',
'/u01/dev/db/data/a_txn_ind05.dbf',
'/u01/dev/db/data/a_ref02.dbf'
CHARACTER SET AL32UTF8;




3、创建添加临时文件的脚本：复制备份的控制文件的 ALTER TABLESPACE TEMP… 这一段，修改如下：
ALTER TABLESPACE TEMP1 ADD TEMPFILE '/u01/dev/db/data/temp01.dbf'
SIZE 1100M REUSE AUTOEXTEND OFF;
ALTER TABLESPACE TEMP1 ADD TEMPFILE '/u01/dev/db/data/temp0001.dbf'
SIZE 1048576000 REUSE AUTOEXTEND OFF;
ALTER TABLESPACE TEMP2 ADD TEMPFILE '/u01/dev/db/data/temp02.dbf'
SIZE 1024M REUSE AUTOEXTEND OFF;


4、准备重建控制文件
export ORACLE_HOME=/u01/dev/db/12.1.0
export ORACLE_SID=DEV
export PATH=$ORACLE_HOME/bin:$PATH


cd $ORACLE_HOME/dbs
mv spfileDEV.ora spfileDEV.ora.old


vi initDEV.ora
注释掉以下行：
#local_listener = DEV_LOCAL
同时确保文件中的db_name是DEV


将原有的控制文件改个名称
cd /u01/dev/db/data
mv cntrl01.dbf cntrl01.dbf.old
mv cntrl02.dbf cntrl02.dbf.old


运行脚本重建控制文件
SQL> conn “/as sysdba”
Connected.
SQL> startup nomount
SQL> @create_ctrl.sql
Control file created.
SQL> alter database open resetlogs;
Database altered.

至此，手工创建控制文件已经完成。

(笔者原创文章，转载请注明出处：https://blog.youkuaiyun.com/LFCuiYs）谢谢！