Oracle控制文件重建-优快云博客

本文介绍Oracle数据库控制文件丢失后的重建步骤，包括生成重建脚本、调整参数、创建新的控制文件等关键操作，并确保数据库正常运行。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

1、生成重建controlfile的脚本

SQL> alter database backup controlfile to trace;

Database altered.
2、查看脚本位置
SQL> show parameter user_dump_dest

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
user_dump_dest string /u01/app/oracle/diag/rdbms/jzh
/jzh1/trace
3、关闭数据库
[oracle@jzh1 ~]$ srvctl stop database -d jzh
4、启动数据库至nomount状态(启动一个实例即可)
[oracle@jzh1 ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Wed Jun 17 15:40:07 2015

Copyright (c) 1982, 2011, Oracle. All rights reserved.

Connected to an idle instance.

SQL> startup nomount
ORACLE instance started.

Total System Global Area 1436389376 bytes
Fixed Size 2228384 bytes
Variable Size 905973600 bytes
Database Buffers 520093696 bytes
Redo Buffers 8093696 bytes
5、执行前面生成的脚本
alter database backup controlfile to trace
Backup controlfile written to trace file /u01/app/oracle/diag/rdbms/jzh/jzh1/trace/jzh1_ora_4989.trc
脚本在上面的trace文件中
SQL> CREATE CONTROLFILE REUSE DATABASE "JZH" NORESETLOGS ARCHIVELOG
2 MAXLOGFILES 192
3 MAXLOGMEMBERS 3
4 MAXDATAFILES 1024
5 MAXINSTANCES 32
6 MAXLOGHISTORY 292
7 LOGFILE
8 GROUP 1 (
9 '+DATA/jzh/onlinelog/group_1.271.880001913',
10 '+ARCH/jzh/onlinelog/group_1.261.880001915'
11 ) SIZE 50M BLOCKSIZE 512,
12 GROUP 2 (
13 '+DATA/jzh/onlinelog/group_2.268.880001917',
14 '+ARCH/jzh/onlinelog/group_2.271.880001919'
15 ) SIZE 50M BLOCKSIZE 512,
16 GROUP 3 (
17 '+DATA/jzh/onlinelog/group_3.260.880005747',
18 '+ARCH/jzh/onlinelog/group_3.260.880005753'
19 ) SIZE 50M BLOCKSIZE 512,
20 GROUP 4 (
21 '+DATA/jzh/onlinelog/group_4.264.880005761',
22 '+ARCH/jzh/onlinelog/group_4.259.880005763'
23 ) SIZE 50M BLOCKSIZE 512
24 -- STANDBY LOGFILE
25 DATAFILE
26 '+DATA/jzh/datafile/system.261.880001919',
27 '+DATA/jzh/datafile/sysaux.265.880001965',
28 '+DATA/jzh/datafile/undotbs1.272.880001991',
29 '+DATA/jzh/datafile/undotbs2.269.880002027',
30 '+DATA/jzh/datafile/users.262.880002029'
31 CHARACTER SET AL32UTF8;
CREATE CONTROLFILE REUSE DATABASE "JZH" NORESETLOGS ARCHIVELOG
*
ERROR at line 1:
ORA-01503: CREATE CONTROLFILE failed
ORA-12720: operation requires database is in EXCLUSIVE mode----------&gt该错误是由于没有设置cluster_database=false
SQL> show parameter cluster_database

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
cluster_database boolean TRUE
cluster_database_instances integer 2
SQL> alter system set cluster_database=false scope=spfile;

System altered.

SQL> shu immediate
ORA-01507: database not mounted

ORACLE instance shut down.
SQL> startup nomount
ORACLE instance started.
Total System Global Area 1436389376 bytes
Fixed Size 2228384 bytes
Variable Size 905973600 bytes
Database Buffers 520093696 bytes
Redo Buffers 8093696 bytes
SQL> CREATE CONTROLFILE REUSE DATABASE "JZH" NORESETLOGS ARCHIVELOG
2 MAXLOGFILES 192
3 MAXLOGMEMBERS 3
4 MAXDATAFILES 1024
5 MAXINSTANCES 32
6 MAXLOGHISTORY 292
7 LOGFILE
8 GROUP 1 (
9 '+DATA/jzh/onlinelog/group_1.271.880001913',
10 '+ARCH/jzh/onlinelog/group_1.261.880001915'
11 ) SIZE 50M BLOCKSIZE 512,
12 GROUP 2 (
'+DATA/jzh/onlinelog/group_2.268.880001917',
'+ARCH/jzh/onlinelog/group_2.271.880001919'
) SIZE 50M BLOCKSIZE 512,
GROUP 3 (
'+DATA/jzh/onlinelog/group_3.260.880005747',
13 14 15 16 17 18 '+ARCH/jzh/onlinelog/group_3.260.880005753'
19 ) SIZE 50M BLOCKSIZE 512,
20 GROUP 4 (
21 '+DATA/jzh/onlinelog/group_4.264.880005761',
22 '+ARCH/jzh/onlinelog/group_4.259.880005763'
23 ) SIZE 50M BLOCKSIZE 512
24 -- STANDBY LOGFILE
25 DATAFILE
26 '+DATA/jzh/datafile/system.261.880001919',
27 '+DATA/jzh/datafile/sysaux.265.880001965',
28 '+DATA/jzh/datafile/undotbs1.272.880001991',
29 '+DATA/jzh/datafile/undotbs2.269.880002027',
30 '+DATA/jzh/datafile/users.262.880002029'
31 CHARACTER SET AL32UTF8;
Control file created.
6、确认redo log
SQL> select * from v$log;

GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME
---------- ---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- --------- ------------ ---------
1 1 69 52428800 512 2 NO CURRENT 972789 17-JUN-15 2.8147E+14 17-JUN-15
2 1 68 52428800 512 2 NO INACTIVE 952423 18-MAY-15 952425 18-MAY-15
3 2 3 52428800 512 2 NO INACTIVE 952462 18-MAY-15 972466 17-JUN-15
4 2 4 52428800 512 2 NO INACTIVE 972466 17-JUN-15 981652 17-JUN-15
7、recover database
SQL> recover database;
ORA-00283: recovery session canceled due to errors
ORA-00264: no recovery required
8、打开数据库
SQL> alter system archive log all;

System altered.

SQL> alter database open
2 ;
Database altered.
9、添加原tempfile至temp表空间
SQL> select name from v$tempfile;
no rows selected
SQL> select tablespace_name from dba_tablespaces;
TABLESPACE_NAME
------------------------------
SYSTEM
SYSAUX
UNDOTBS1
TEMP
UNDOTBS2
USERS
6 rows selected.
SQL> alter tablespace temp add tempfile '+DATA/jzh/tempfile/TEMP.267.880001997';
Tablespace altered.
SQL> select name from v$tempfile;
NAME
--------------------------------------------------------------------------------
+DATA/jzh/tempfile/temp.267.880001997
10、设置cluster_database为true
SQL> alter system set cluster_database=true scope=spfile;
System altered.
SQL>
SQL> shu immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
11、启动所有实例
[oracle@jzh1 ~]$ srvctl start database -d jzh
[oracle@jzh1 ~]$ srvctl status database -d jzh
Instance jzh1 is running on node jzh1
Instance jzh2 is running on node jzh2
controlfile 重建完成！

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/10271187/viewspace-1703392/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/10271187/viewspace-1703392/