本文通过创建软链接和重建控制文件的方式,使裸设备在数据库中显示为普通数据文件常用的名称。
重建控制文件过程中,解决了在 RAC环境下, 重建控制文件报错问题: ORA-01503: CREATE CONTROLFILE failed ORA-12720: operation requires database is in EXCLUSIVE mode。
【实验环境】
操作系统:AIX 5300-09
集群软件:CRS 10.2.0.1
数据库: Oracle 10.2.0.1
【AIX-RAC结构信息】
![](http://img.blog.itpub.net/blog/attachment/201408/11/29475508_1407689846QPO4.jpg?x-oss-process=style/bb)
【实验背景】
在AIX系统上搭建的双节点RAC,使用hacmp实现卷组并发来建立共享存储。DBCA创建数据库时,存储选项选择裸设备,裸设备的映射文件如下:直接对应的是共享逻辑卷
- control1=/dev/rrac_control1
- control2=/dev/rrac_control2
- example=/dev/rrac_example
- redo1_1=/dev/rrac_redo1_1
- redo1_2=/dev/rrac_redo1_2
- redo2_1=/dev/rrac_redo2_1
- redo2_2=/dev/rrac_redo2_2
- spfile=/dev/rrac_spfile
- sysaux=/dev/rrac_sysaux
- system=/dev/rrac_system
- temp=/dev/rrac_temp
- undotbs1=/dev/rrac_undotbs1
- undotbs2=/dev/rrac_undotbs2
- users=/dev/rrac_users
- pwdfile=/dev/rrac_pwdfile
select file_id,file_name,tablespace_name,bytes/1024/1024 from dba_data_files;
![](http://img.blog.itpub.net/blog/attachment/201408/10/29475508_1407681822JZfd.jpg?x-oss-process=style/bb)
file_name 显示的就是共享逻辑卷,并不是我们所熟悉的方式
想要在查询数据文件、日志文件时,按如下所示我们所熟悉的方式显示,该如何来实现呢?
![](http://img.blog.itpub.net/blog/attachment/201408/10/29475508_14076818423LIZ.jpg?x-oss-process=style/bb)
首先,创建目录并创建软链接,链接到对应逻辑卷
ln -s /dev/rrac_control1 /u01/app/oracle/oradata/prod/control01.ctl
ln -s /dev/rrac_control2 /u01/app/oracle/oradata/prod/control02.ctl
ln -s /dev/rrac_example /u01/app/oracle/oradata/prod/example01.dbf
ln -s /dev/rrac_redo1_1 /u01/app/oracle/oradata/prod/log11.log
ln -s /dev/rrac_redo1_2 /u01/app/oracle/oradata/prod/log12.log
ln -s /dev/rrac_redo2_1 /u01/app/oracle/oradata/prod/log21.log
ln -s /dev/rrac_redo2_2 /u01/app/oracle/oradata/prod/log22.log
ln -s /dev/rrac_spfile /u01/app/oracle/oradata/prod/spfile01
ln -s /dev/rrac_sysaux /u01/app/oracle/oradata/prod/sysaux01.dbf
ln -s /dev/rrac_system /u01/app/oracle/oradata/prod/system01.dbf
ln -s /dev/rrac_temp /u01/app/oracle/oradata/prod/temp01.dbf
ln -s /dev/rrac_undotbs1 /u01/app/oracle/oradata/prod/undotbs01.dbf
ln -s /dev/rrac_undotbs2 /u01/app/oracle/oradata/prod/undotbs02.dbf
ln -s /dev/rrac_users /u01/app/oracle/oradata/prod/users01.dbf
ln -s /dev/rrac_pwdfile /u01/app/oracle/oradata/prod/pwdfile01
![](http://img.blog.itpub.net/blog/attachment/201408/10/29475508_14076819512wt4.jpg?x-oss-process=style/bb)
然后,就需要修改控制文件里的数据文件、日志文件信息了。
可以通过重建控制文件的方式,在重建时将数据文件、日志文件信息更改为上面建立的软链接。
下面就是在RAC环境下重建控制文件的具体实验过程了
【实验过程】
1、dump当前控制文件到指定位置
![](http://img.blog.itpub.net/blog/attachment/201408/10/29475508_1407682510YXwh.jpg?x-oss-process=style/bb)
查看dump出来的控制文件信息
- [root@aix227:/u01/app/oracle]#more controlbak.ctl
- -- The following are current System-scope REDO Log Archival related
- -- parameters and can be included in the database initialization file.
- --
- -- LOG_ARCHIVE_DEST=''
- -- LOG_ARCHIVE_DUPLEX_DEST=''
- --
- -- LOG_ARCHIVE_FORMAT=%t_%s_%r.dbf
- --
- -- DB_UNIQUE_NAME="prod"
- --
- -- LOG_ARCHIVE_CONFIG='SEND, RECEIVE, NODG_CONFIG'
- -- LOG_ARCHIVE_MAX_PROCESSES=2
- -- STANDBY_FILE_MANAGEMENT=MANUAL
- -- STANDBY_ARCHIVE_DEST=?/dbs/arch
- -- FAL_CLIENT=''
- -- FAL_SERVER=''
- --
- -- LOG_ARCHIVE_DEST_1='LOCATION=/u01/app/oracle/product/10.2.0/db_1/dbs/arch'
- -- LOG_ARCHIVE_DEST_1='MANDATORY NOREOPEN NODELAY'
- -- LOG_ARCHIVE_DEST_1='ARCH NOAFFIRM EXPEDITE NOVERIFY SYNC'
- -- LOG_ARCHIVE_DEST_1='NOREGISTER NOALTERNATE NODEPENDENCY'
- -- LOG_ARCHIVE_DEST_1='NOMAX_FAILURE NOQUOTA_SIZE NOQUOTA_USED NODB_UNIQUE_NAME'
- -- LOG_ARCHIVE_DEST_1='VALID_FOR=(PRIMARY_ROLE,ONLINE_LOGFILES)'
- -- LOG_ARCHIVE_DEST_STATE_1=ENABLE
-
- --
- -- Below are two sets of SQL statements, each of which creates a new
- -- control file and uses it to open the database. The first set opens
- -- the database with the NORESETLOGS option and should be used only if
- -- the current versions of all online logs are available. The second
- -- set opens the database with the RESETLOGS option and should be used
- -- if online logs are unavailable.
- -- The appropriate set of statements can be copied from the trace into
- -- a script file, edited as necessary, and executed when there is a
- -- need to re-create the control file.
- --
- -- Set #1. NORESETLOGS case
- --
- -- The following commands will create a new control file and use it
- -- to open the database.
- -- Data used by Recovery Manager will be lost.
- -- Additional logs may be required for media recovery of offline
- -- Use this only if the current versions of all online logs are
- -- available.
-
- -- After mounting the created controlfile, the following SQL
- -- statement will place the database in the appropriate
- -- protection mode:
- -- ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE PERFORMANCE
-
- STARTUP NOMOUNT
- CREATE CONTROLFILE REUSE DATABASE "PROD" NORESETLOGS NOARCHIVELOG
- MAXLOGFILES 192
- MAXLOGMEMBERS 3
- MAXDATAFILES 1024
- MAXINSTANCES 32
- MAXLOGHISTORY 292
- LOGFILE
- GROUP 1 '/dev/rrac_redo1_1' SIZE 50M,
- GROUP 2 '/dev/rrac_redo1_2' SIZE 50M,
- GROUP 3 '/dev/rrac_redo2_1' SIZE 50M,
- GROUP 4 '/dev/rrac_redo2_2' SIZE 50M
- -- STANDBY LOGFILE
-
- DATAFILE
- '/dev/rrac_system',
- '/dev/rrac_undotbs1',
- '/dev/rrac_sysaux',
- '/dev/rrac_users',
- '/dev/rrac_example',
- '/dev/rrac_undotbs2'
- CHARACTER SET ZHS16GBK
- ;
-
- -- Commands to re-create incarnation table
- -- Below log names MUST be changed to existing filenames on
- -- disk. Any one log file from each branch can be used to
- -- re-create incarnation records.
- -- ALTER DATABASE REGISTER LOGFILE '/u01/app/oracle/product/10.2.0/db_1/dbs/arch1_1_564280945.dbf';
- -- ALTER DATABASE REGISTER LOGFILE '/u01/app/oracle/product/10.2.0/db_1/dbs/arch1_1_854793143.dbf';
- -- Recovery is required if any of the datafiles are restored backups,
- -- or if the last shutdown was not normal or immediate.
- RECOVER DATABASE
-
- -- Database can now be opened normally.
- ALTER DATABASE OPEN;
-
- -- Commands to add tempfiles to temporary tablespaces.
- -- Online tempfiles have complete space information.
- -- Other tempfiles may require adjustment.
- ALTER TABLESPACE TEMP ADD TEMPFILE '/dev/rrac_temp'
- SIZE 20971520 REUSE AUTOEXTEND ON NEXT 655360 MAXSIZE 536870912 ;
- -- End of tempfile additions.
- --
- -- Set #2. RESETLOGS case
- --
- -- The following commands will create a new control file and use it
- -- to open the database.
- -- Data used by Recovery Manager will be lost.
- -- The contents of online logs will be lost and all backups will
- -- be invalidated. Use this only if online logs are damaged.
-
- -- After mounting the created controlfile, the following SQL
- -- statement will place the database in the appropriate
- -- protection mode:
- -- ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE PERFORMANCE
-
- STARTUP NOMOUNT
- CREATE CONTROLFILE REUSE DATABASE "PROD" RESETLOGS NOARCHIVELOG
- MAXLOGFILES 192
- MAXLOGMEMBERS 3
- MAXDATAFILES 1024
- MAXINSTANCES 32
- MAXLOGHISTORY 292
- LOGFILE
- GROUP 1 '/dev/rrac_redo1_1' SIZE 50M,
- GROUP 2 '/dev/rrac_redo1_2' SIZE 50M
- -- STANDBY LOGFILE
-
- DATAFILE
- '/dev/rrac_system',
- '/dev/rrac_undotbs1',
- '/dev/rrac_sysaux',
- '/dev/rrac_users',
- '/dev/rrac_example',
- '/dev/rrac_undotbs2'
- CHARACTER SET ZHS16GBK
- ;
2、修改控制文件重建语句
因为要在正常关库的情况下,主动重建控制文件,我们可以采用NORESETLOGS方式根据dump出来的信息,修改其中logfile、datafile为前面创建的软链接,得到重建控制文件语句如下:
SQL> CREATE CONTROLFILE REUSE DATABASE "PROD" NORESETLOGS NOARCHIVELOG
2 MAXLOGFILES 192
3 MAXLOGMEMBERS 3
4 MAXDATAFILES 1024
5 MAXINSTANCES 32
6 MAXLOGHISTORY 292
7 LOGFILE
8 GROUP 1 '/u01/app/oracle/oradata/prod/log11.log' SIZE 50M,
9 GROUP 2 '/u01/app/oracle/oradata/prod/log12.log' SIZE 50M,
10 GROUP 3 '/u01/app/oracle/oradata/prod/log21.log' SIZE 50M,
11 GROUP 4 '/u01/app/oracle/oradata/prod/log22.log' SIZE 50M
12 DATAFILE
13 '/u01/app/oracle/oradata/prod/system01.dbf',
14 '/u01/app/oracle/oradata/prod/undotbs01.dbf',
15 '/u01/app/oracle/oradata/prod/sysaux01.dbf',
16 '/u01/app/oracle/oradata/prod/users01.dbf',
17 '/u01/app/oracle/oradata/prod/example01.dbf',
18 '/u01/app/oracle/oradata/prod/undotbs02.dbf'
19 CHARACTER SET ZHS16GBK
20 ;3、正常关闭数据库,把一个实例启动到nomount状态
正常关闭整个数据库(所有实例)
srvctl stop database -d prod
![](http://img.blog.itpub.net/blog/attachment/201408/10/29475508_1407683565IeKc.jpg?x-oss-process=style/bb)
sqlplus登录其中一个实例:prod1,启动到nomount状态
![](http://img.blog.itpub.net/blog/attachment/201408/10/29475508_1407683902vIvv.jpg?x-oss-process=style/bb)
4、直接重建控制文件报错:
![](http://img.blog.itpub.net/blog/attachment/201408/10/29475508_1407684086VSG9.jpg?x-oss-process=style/bb)
- SQL> CREATE CONTROLFILE REUSE DATABASE "PROD" NORESETLOGS NOARCHIVELOG
- 2 MAXLOGFILES 192
- 3 MAXLOGMEMBERS 3
- 4 MAXDATAFILES 1024
- 5 MAXINSTANCES 32
- 6 MAXLOGHISTORY 292
- 7 LOGFILE
- 8 GROUP 1 '/u01/app/oracle/oradata/prod/log11.log' SIZE 50M,
- 9 GROUP 2 '/u01/app/oracle/oradata/prod/log12.log' SIZE 50M,
- 10 GROUP 3 '/u01/app/oracle/oradata/prod/log21.log' SIZE 50M,
- 11 GROUP 4 '/u01/app/oracle/oradata/prod/log22.log' SIZE 50M
- 12 DATAFILE
- 13 '/u01/app/oracle/oradata/prod/system01.dbf',
- 14 '/u01/app/oracle/oradata/prod/undotbs01.dbf',
- 15 '/u01/app/oracle/oradata/prod/sysaux01.dbf',
- 16 '/u01/app/oracle/oradata/prod/users01.dbf',
- 17 '/u01/app/oracle/oradata/prod/example01.dbf',
- 18 '/u01/app/oracle/oradata/prod/undotbs02.dbf'
- 19 CHARACTER SET ZHS16GBK
- 20 ;
- CREATE CONTROLFILE REUSE DATABASE "PROD" NORESETLOGS NOARCHIVELOG
- *
- ERROR at line 1:
- ORA-01503: CREATE CONTROLFILE failed
- ORA-12720: operation requires database is in EXCLUSIVE mode
-
-
- SQL>
5、使用EXCLUSIVE模式启动到nomount状态
作为一个oracle小白,根据前面报错信息
尝试startup nomount exclusive;后重建控制文件
![](http://img.blog.itpub.net/blog/attachment/201408/10/29475508_1407684101NN7m.jpg?x-oss-process=style/bb)
再次重建控制文件依然报相同错误
![](http://img.blog.itpub.net/blog/attachment/201408/10/29475508_1407684118WRL9.jpg?x-oss-process=style/bb)
6、修改cluster_database参数值为false
查看cluster_databse参数
![](http://img.blog.itpub.net/blog/attachment/201408/10/29475508_1407684217x1b5.jpg?x-oss-process=style/bb)
设置cluster_databse值为false
![](http://img.blog.itpub.net/blog/attachment/201408/10/29475508_14076850957sn1.jpg?x-oss-process=style/bb)
7、正常关闭数据库后,重启到nomount状态
![](http://img.blog.itpub.net/blog/attachment/201408/10/29475508_1407685107S6sj.jpg?x-oss-process=style/bb)
8、再次重建控制文件:成功
![](http://img.blog.itpub.net/blog/attachment/201408/10/29475508_1407685182lGw9.jpg?x-oss-process=style/bb)
9、调整临时表空间数据文件
重建控制文件成功后,还需要从dump出来的控制文件信息中,找出与临时表空间相关的语句,调整为我们的软链接位置
SQL> ALTER TABLESPACE TEMP ADD TEMPFILE '/u01/app/oracle/oradata/prod/temp01.dbf'
2 SIZE 20971520 REUSE AUTOEXTEND ON NEXT 655360 MAXSIZE 536870912 ;
![](http://img.blog.itpub.net/blog/attachment/201408/10/29475508_1407685222xZJD.jpg?x-oss-process=style/bb)
10、恢复cluster_database参数为true
![](http://img.blog.itpub.net/blog/attachment/201408/10/29475508_14076853880bhk.jpg?x-oss-process=style/bb)
11、正常关闭数据库,启动数据库所有实例查验
![](http://img.blog.itpub.net/blog/attachment/201408/10/29475508_1407685403GPy3.jpg?x-oss-process=style/bb)
![](http://img.blog.itpub.net/blog/attachment/201408/10/29475508_14076854208rzv.jpg?x-oss-process=style/bb)
![](http://img.blog.itpub.net/blog/attachment/201408/10/29475508_1407685434NX5p.jpg?x-oss-process=style/bb)
查看数据库状态,两个实例都已经open
查看数据文件和日志文件,已经是我们想要的结果。^_^
![](http://img.blog.itpub.net/blog/attachment/201408/10/29475508_1407685450Cd1W.jpg?x-oss-process=style/bb)
【实验总结】
一、裸设备搭建的 rac,修改 数据文件、日志文件显示方式
首先创建软链接,实现常规文件名链接到具体设备;
然后通过重建控制文件的方式,在重建时将数据文件和日志文件的路径改为新建的链接。
二、RAC重建控制文件报错常见原因
RAC环境重建控制文件报错, 使用startup nomount exclusive;并不能真正实现非共享。 在RAC集群环境下,初始化参数文件中有cluster_database参数,此参数值为true时,是集群数据库,数据文件是所有实例共享的。 如果要重建控制文件,需要在 EXCLUSIVE 模式,必须先将cluster_database 参数的值设置为false。
三、RAC环境重建控制文件一般顺序
1、使用dump出来的控制文件信息,选择重建控制文件语句
2、设置cluster_databse=false
3、正常关闭数据库,将一个实例启动到nomount状态
4、重建控制文件
5、恢复cluster_databse=true
6、recover databse (如果正常关库、无数据损坏或丢失,使用noresetlog方式重建则不需要)
7、重启数据库查验 (所有实例)
吕星昊
2014.8.10
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/29475508/viewspace-1248871/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/29475508/viewspace-1248871/