您的位置: 首页  >  文章  >  Oracle 重建控制文件

Oracle 重建控制文件

分类: 文章 • 2024-01-02 17:04:10

大家看原文地址：http://www.cnblogs.com/j-notes/p/6527165.html



一、使用sysdba用户登入数据库

   

Oracle 重建控制文件

此时普通用户已无法链接数据库

   

二、启动数据库到mount状态

   

Oracle 重建控制文件

此时可以看出异常来源：由于控制文件中的实例名与实例本身实例名不一致导致数据库实例无法正常启用

   

三、获取创建控制文件的脚本格式

   

在Oracle中我们可以通过 alter database backup controlfile to trace; 命令获取控制文件的创建方法，但由于此时实例ORCL上的控制文件已经出现异常，我们无法在这个实例上获取.trc文件；不过我们可以使用这条命令在其他正常的实例上获取此文件，并根据实际情况创建重建控制文件的脚本。一下是具体方法：

   

获取文件，重命名并保存到/tmp目录下，方便查看

 

Oracle 重建控制文件
 1 [[email protected] ~]$ sqlplus / as sysdba
 2 
 3    
 4 
 5 SQL*Plus: Release 11.2.0.4.0 Production on 星期四 3月 9 16:45:50 2017
 6 
 7    
 8 
 9 Copyright (c) 1982, 2013, Oracle. All rights reserved.
10 
11    
12 
13    
14 
15 Connected to:
16 
17 Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
18 
19 With the Partitioning, OLAP, Data Mining and Real Application Testing options
20 
21    
22 
23 SQL> alter database backup controlfile to trace as '/tmp/control_rebuild.trc'
24 
25 2 ;
26 
27    
28 
29 Database altered.
Oracle 重建控制文件

 

   

查看重建控制文件的格式

[[email protected] ~]$ more /tmp/control_rebuild.trc

 

Oracle 重建控制文件 View Code

 

 

 

由于我们这里不需要重做重做日志，所以选择第一种方法。然后根据ORCL实例的重做日志和表空间路径，写出重建控制文件的脚本。

Oracle 重建控制文件

   

   

Oracle 重建控制文件

Oracle 重建控制文件
STARTUP NOMOUNT

CREATE CONTROLFILE REUSE DATABASE "ORCL" NORESETLOGS ARCHIVELOG

MAXLOGFILES 16

MAXLOGMEMBERS 3

MAXDATAFILES 100

MAXINSTANCES 8

MAXLOGHISTORY 292

LOGFILE

GROUP 1 '/u01/app/oracle/oradata/orcl/redo01.log' SIZE 50M ,

GROUP 2 '/u01/app/oracle/oradata/orcl/redo02.log' SIZE 50M ,

GROUP 3 '/u01/app/oracle/oradata/orcl/redo03.log' SIZE 50M

DATAFILE

'/u01/app/oracle/oradata/orcl/system01.dbf',

'/u01/app/oracle/oradata/orcl/sysaux01.dbf',

'/u01/app/oracle/oradata/orcl/undotbs01.dbf',

'/u01/app/oracle/oradata/orcl/users01.dbf',

'/u01/app/oracle/oradata/orcl/test01.dbf',

'/u01/app/oracle/oradata/test001.dbf',

'/u01/app/oracle/oradata/test1.dbf',

'/u01/app/oracle/oradata/test2.dbf',

CHARACTER SET AL32UTF8

;

VARIABLE RECNO NUMBER;

RECOVER DATABASE

ALTER SYSTEM ARCHIVE LOG ALL;

ALTER DATABASE OPEN;

ALTER TABLESPACE TEMP ADD TEMPFILE '/u01/app/oracle/oradata/orcl/temp01.dbf'

SIZE 61865984 REUSE AUTOEXTEND ON NEXT 655360 MAXSIZE 32767M;
Oracle 重建控制文件

 

   

注意：控制文件中不包含临时表空间

   

四、运行脚本

 

Oracle 重建控制文件
[[email protected] orcl]$ sqlplus / as sysdba

   

SQL*Plus: Release 11.2.0.4.0 Production on Thu Mar 9 17:41:24 2017

   

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

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options 

SQL> shutdown immediate;

ORA-01507: database not mounted

    

ORACLE instance shut down.

SQL> set echo on

SQL> @rebuild

SQL> STARTUP NOMOUNT

ORACLE instance started.

   

Total System Global Area 839282688 bytes

Fixed Size                 2257880 bytes

Variable Size                 507513896 bytes

Database Buffers         327155712 bytes

Redo Buffers                 2355200 bytes

SQL> CREATE CONTROLFILE REUSE DATABASE "ORCL" NORESETLOGS ARCHIVELOG

2          MAXLOGFILES 16

3          MAXLOGMEMBERS 3

4          MAXDATAFILES 100

5          MAXINSTANCES 8

6          MAXLOGHISTORY 292

7 LOGFILE

8 GROUP 1 '/u01/app/oracle/oradata/orcl/redo01.log' SIZE 50M ,

9 GROUP 2 '/u01/app/oracle/oradata/orcl/redo02.log' SIZE 50M ,

10 GROUP 3 '/u01/app/oracle/oradata/orcl/redo03.log' SIZE 50M

11 DATAFILE

12 '/u01/app/oracle/oradata/orcl/system01.dbf',

13 '/u01/app/oracle/oradata/orcl/sysaux01.dbf',

14 '/u01/app/oracle/oradata/orcl/undotbs01.dbf',

15 '/u01/app/oracle/oradata/orcl/users01.dbf',

16 '/u01/app/oracle/oradata/orcl/test01.dbf',

17 '/u01/app/oracle/oradata/test001.dbf',

18 '/u01/app/oracle/oradata/test1.dbf',

19 '/u01/app/oracle/oradata/test2.dbf',

20 CHARACTER SET AL32UTF8

21 ;

CHARACTER SET AL32UTF8

*

ERROR at line 20:

ORA-02236: invalid file name
Oracle 重建控制文件

出错了，貌似是这里不能设置字符集（数据库版本是11.2.0.4），删除这条命令后就没有影响了。（知道具体原因的请赐教）

Oracle 重建控制文件
[[email protected] orcl]$ sqlplus / as sysdba

   

SQL*Plus: Release 11.2.0.4.0 Production on Thu Mar 9 18:29:43 2017

   

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

   

   

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

   

SQL> @rebuild

SQL> SHUTDOWN ABORT;

ORACLE instance shut down.

SQL> STARTUP NOMOUNT;

ORACLE instance started.

   

Total System Global Area 839282688 bytes

Fixed Size                 2257880 bytes

Variable Size                 507513896 bytes

Database Buffers         327155712 bytes

Redo Buffers                 2355200 bytes

SQL> CREATE CONTROLFILE REUSE DATABASE "ORCL" NORESETLOGS ARCHIVELOG

2          MAXLOGFILES 16

3          MAXLOGMEMBERS 3

4          MAXDATAFILES 100

5          MAXINSTANCES 8

6          MAXLOGHISTORY 292

7 LOGFILE

8 GROUP 1 '/u01/app/oracle/oradata/orcl/redo01.log' SIZE 50M ,

9 GROUP 2 '/u01/app/oracle/oradata/orcl/redo02.log' SIZE 50M ,

10 GROUP 3 '/u01/app/oracle/oradata/orcl/redo03.log' SIZE 50M

11 DATAFILE

12 '/u01/app/oracle/oradata/orcl/system01.dbf',

13 '/u01/app/oracle/oradata/orcl/sysaux01.dbf',

14 '/u01/app/oracle/oradata/orcl/undotbs01.dbf',

15 '/u01/app/oracle/oradata/orcl/users01.dbf',

16 '/u01/app/oracle/oradata/orcl/test01.dbf',

17 '/u01/app/oracle/oradata/test001.dbf',

18 '/u01/app/oracle/oradata/test1.dbf',

19 '/u01/app/oracle/oradata/test2.dbf';

Control file created.

   

SQL> VARIABLE RECNO NUMBER;

SQL> RECOVER DATABASE

ORA-00283: recovery session canceled due to errors

ORA-00264: no recovery required

   

   

SQL> ALTER SYSTEM ARCHIVE LOG ALL;

   

System altered.

   

SQL> ALTER DATABASE OPEN;

   

Database altered.

   

SQL> ALTER TABLESPACE TEMP ADD TEMPFILE '/u01/app/oracle/oradata/orcl/temp01.dbf'

2          SIZE 61865984 REUSE AUTOEXTEND ON NEXT 655360 MAXSIZE 32767M;

   

Tablespace altered.

   

SQL> conn test001/shuyi123

Connected.  
Oracle 重建控制文件

至此，数据库恢复正常。

-------------------------------------------------------------------------------

最后优化一下脚本

Oracle 重建控制文件

Oracle 重建控制文件
SET ECHO ON

SHUTDOWN ABORT;

STARTUP NOMOUNT;

CREATE CONTROLFILE REUSE DATABASE "ORCL" NORESETLOGS ARCHIVELOG

MAXLOGFILES 16

MAXLOGMEMBERS 3

MAXDATAFILES 100

MAXINSTANCES 8

MAXLOGHISTORY 292

LOGFILE

GROUP 1 '/u01/app/oracle/oradata/orcl/redo01.log' SIZE 50M ,

GROUP 2 '/u01/app/oracle/oradata/orcl/redo02.log' SIZE 50M ,

GROUP 3 '/u01/app/oracle/oradata/orcl/redo03.log' SIZE 50M

DATAFILE

'/u01/app/oracle/oradata/orcl/system01.dbf',

'/u01/app/oracle/oradata/orcl/sysaux01.dbf',

'/u01/app/oracle/oradata/orcl/undotbs01.dbf',

'/u01/app/oracle/oradata/orcl/users01.dbf',

'/u01/app/oracle/oradata/orcl/test01.dbf',

'/u01/app/oracle/oradata/test001.dbf',

'/u01/app/oracle/oradata/test1.dbf',

'/u01/app/oracle/oradata/test2.dbf';

VARIABLE RECNO NUMBER;

ALTER SYSTEM ARCHIVE LOG ALL;

ALTER DATABASE OPEN;

ALTER TABLESPACE TEMP ADD TEMPFILE '/u01/app/oracle/oradata/orcl/temp01.dbf'

SIZE 61865984 REUSE AUTOEXTEND ON NEXT 655360 MAXSIZE 32767M;
Oracle 重建控制文件

相关推荐