Oracle11g RAC 增加控制文件（二）
之前【Oracle11g RAC 增加控制文件（一）】是利用asmcmd的cp创建的控制文件，本例是利用RMAN创建的控制文件
1、查看当前控制文件数量及位置、名称
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 |
[oracle@rac1 ~]$ sqlplus / as sysdba SQL*Plus: Release 11.2.0.2.0 Production on Tue Feb 11 17:11:53 2014 Copyright (c) 1982, 2010, Oracle. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP, Data Mining and Real Application Testing options SQL> show parameter control_files NAME TYPE VALUE ------------------------------------ ----------- --------------------------------------------- control_files string +DATA/racdb/controlfile/current.260.838213361 SQL> //当然，也可以通过下面方法查看 [grid@rac1 ~]$ asmcmd ASMCMD> cd data/racdb/con* ASMCMD> ls Current.260.838213361 ASMCMD> |
2、备份参数文件
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 |
//节点1：生成最新pfile文件，一致性关闭数据库 SQL> create pfile='/tmp/initRAC1.ora' from spfile; create pfile='/home/grid/rac1_spfileracdb.ora' from spfile; File created. SQL> SQL> shutdown immediate; Database closed. Database dismounted. ORACLE instance shut down. SQL> //节点2：一致性关闭数据库 SQL> shutdown immediate; Database closed. Database dismounted. ORACLE instance shut down. SQL> //查看资源状态 [grid@rac1 ~]$ crs_stat -t Name Type Target State Host ------------------------------------------------------------ ora.DATA.dg ora....up.type ONLINE ONLINE rac1 ora....ER.lsnr ora....er.type ONLINE ONLINE rac1 ora....N1.lsnr ora....er.type ONLINE ONLINE rac1 ora....VOTE.dg ora....up.type ONLINE ONLINE rac1 ora.OV.dg ora....up.type ONLINE ONLINE rac1 ora.asm ora.asm.type ONLINE ONLINE rac1 ora.cvu ora.cvu.type ONLINE ONLINE rac1 ora.gsd ora.gsd.type ONLINE ONLINE rac1 ora....network ora....rk.type ONLINE ONLINE rac1 ora.oc4j ora.oc4j.type ONLINE ONLINE rac1 ora.ons ora.ons.type ONLINE ONLINE rac1 ora....SM1.asm application ONLINE ONLINE rac1 ora....C1.lsnr application ONLINE ONLINE rac1 ora.rac1.gsd application ONLINE ONLINE rac1 ora.rac1.ons application ONLINE ONLINE rac1 ora.rac1.vip ora....t1.type ONLINE ONLINE rac1 ora....SM2.asm application ONLINE ONLINE rac2 ora....C2.lsnr application ONLINE ONLINE rac2 ora.rac2.gsd application ONLINE ONLINE rac2 ora.rac2.ons application ONLINE ONLINE rac2 ora.rac2.vip ora....t1.type ONLINE ONLINE rac2 ora.racdb.db ora....se.type OFFLINE OFFLINE ora.scan1.vip ora....ip.type ONLINE ONLINE rac1 [grid@rac1 ~]$ |
3、利用RMAN创建控制文件
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 |
[oracle@rac1 ~]$ rman target / Recovery Manager: Release 11.2.0.2.0 - Production on Tue Feb 11 17:17:12 2014 Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved. connected to target database (not started) RMAN> startup nomount; Oracle instance started Total System Global Area 835104768 bytes Fixed Size 2231088 bytes Variable Size 570426576 bytes Database Buffers 260046848 bytes Redo Buffers 2400256 bytes RMAN> restore controlfile to '+DATA/RACDB/CONTROLFILE/current.261.838213361' from '+DATA/RACDB/CONTROLFILE/current.260.838213361'; Starting restore at 11-FEB-14 using target database control file instead of recovery catalog allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=32 instance=RACDB1 device type=DISK channel ORA_DISK_1: copied control file copy Finished restore at 11-FEB-14 RMAN> restore controlfile to '+DATA/RACDB/CONTROLFILE/current.262.838213361' from '+DATA/RACDB/CONTROLFILE/current.260.838213361'; Starting restore at 11-FEB-14 using channel ORA_DISK_1 channel ORA_DISK_1: copied control file copy Finished restore at 11-FEB-14 RMAN> RMAN> shutdown abort; Oracle instance shut down RMAN> exit Recovery Manager complete. [oracle@rac1 ~]$ //注意：由于是OMF(Oracle managed file)方式管理，所以生成的控制文件的名称会有所变化。 //因为是OMF管理，所以，RMAN还原时，直接指定磁盘组即可 //RMAN> restore controlfile to '+DATA' from '+DATA/RACDB/CONTROLFILE/current.260.838213361'; |
4、验证新创建的控制文件，我们可以看到，用RMAN的方式创建的控制文件，就是在本目录下创建的。
|
1 2 3 4 5 6 7 8 9 10 11 12 |
[grid@rac1 ~]$ asmcmd ASMCMD> ASMCMD> ls Current.260.838213361 current.268.839288595 current.272.839288591 ASMCMD> ls -l Type Redund Striped Time Sys Name CONTROLFILE UNPROT FINE FEB 11 23:00:00 Y Current.260.838213361 CONTROLFILE UNPROT FINE FEB 11 23:00:00 Y current.268.839288595 CONTROLFILE UNPROT FINE FEB 11 23:00:00 Y current.272.839288591 ASMCMD> |
5、修改参数文件，使新建的控制文件生效（方法有两种，一种是命令行修改，参照【Oracle11g RAC 增加控制文件（一）】，另一种是利用pfile来修改，本测试采用此方法）
5.1、删除spfile参数文件
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
ASMCMD> ls CONTROLFILE/ DATAFILE/ ONLINELOG/ TEMPFILE/ spfileracdb.ora ASMCMD> rm spfileracdb.ora ASMCMD> ls CONTROLFILE/ DATAFILE/ ONLINELOG/ TEMPFILE/ ASMCMD> |
5.2、 节点1修改备份的pfile
|
1 2 3 4 5 |
[root@rac1 ~]# vi /tmp/initRAC1.ora //修改内容如下： *.control_files='+DATA/racdb/controlfile/current.260.838213361' 改为 *.control_files='+DATA/racdb/controlfile/current.260.838213361','+DATA/racdb/controlfile/current.271.839265577','+DATA/racdb/controlfile/current.272.839265561' |
5.3、节点1利用pfile创建spfile
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
[oracle@rac1 ~]$ sqlplus / as sysdba SQL*Plus: Release 11.2.0.2.0 Production on Wed Feb 12 00:24:02 2014 Copyright (c) 1982, 2010, Oracle. All rights reserved. Connected to an idle instance. SQL> SQL> create spfile='+DATA/RACDB/spfileRACDB.ora' from pfile='/tmp/initRAC1.ora'; File created. SQL> |
5.4、查看生成的spfile文件，我们看到，spfile已经生成，不过，不完美，生成的spfile文件是Alias
|
1 2 3 4 5 6 7 8 9 10 |
ASMCMD> pwd +data/racdb ASMCMD> ls -l Type Redund Striped Time Sys Name Y CONTROLFILE/ Y DATAFILE/ Y ONLINELOG/ Y TEMPFILE/ N spfileracdb.ora => +DATA/DB_UNKNOWN/PARAMETERFILE/SPFILE.270.839289779 ASMCMD> |
5.5、节点1数据库启动到nomount状态
注：这一步必须启动，否则在5.7小节创建spfile的时候，将找不到spifle的Alias，因为在OMF方式创建时，它无法知道SID是什么，所以需要启动到nomount模式
|
1 2 3 4 5 6 7 8 9 |
SQL> startup nomount; ORACLE instance started. Total System Global Area 835104768 bytes Fixed Size 2231088 bytes Variable Size 570426576 bytes Database Buffers 260046848 bytes Redo Buffers 2400256 bytes SQL> |
5.6、数据库启动后，删除刚刚创建的spfile文件
|
1 2 |
ASMCMD> rm spfileracdb.ora ASMCMD> |
5.7、节点1再次创建spfile
|
1 2 3 4 5 |
SQL> create spfile='+DATA' from pfile='/tmp/initRAC1.ora'; File created. SQL> |
5.8、查看创建后的spfile，我们会发现，此时的spfile已经存在PARAMETERFILE目录下，并且，参数文件已经不是链接了
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
ASMCMD> pwd +data/racdb ASMCMD> ls CONTROLFILE/ DATAFILE/ ONLINELOG/ PARAMETERFILE/ TEMPFILE/ ASMCMD> cd p* ASMCMD> pwd +data/racdb/PARAMETERFILE ASMCMD> ls spfile.270.839290327 ASMCMD> ls -l Type Redund Striped Time Sys Name PARAMETERFILE UNPROT COARSE FEB 12 00:00:00 Y spfile.270.839290327 ASMCMD> |
5.9、关闭数据库，重新用最新的spfile启动数据库（因为数据库目前启动在nomount模式，所以，直接abort即可）
|
1 2 3 |
SQL> shutdown abort; ORACLE instance shut down. SQL> |
5.10、修改节点1的pfile文件
|
1 2 3 4 5 6 7 8 |
[oracle@rac1 ~]$ cd $ORACLE_HOME/dbs [oracle@rac1 dbs]$ pwd /u01/app/oracle/product/11.2.0/dbhome_1/dbs [oracle@rac1 dbs]$ [oracle@rac1 dbs]$ vi initRACDB1.ora SPFILE='+DATA/RACDB/spfileRACDB.ora' 改为 SPFILE='+DATA/RACDB/PARAMETERFILE/spfile.269.839292173' |
5.11、节点1再次启动数据库
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 |
[oracle@rac1 dbs]$ sqlplus / as sysdba SQL*Plus: Release 11.2.0.2.0 Production on Wed Feb 12 00:42:19 2014 Copyright (c) 1982, 2010, Oracle. All rights reserved. Connected to an idle instance. SQL> startup nomount; ORACLE instance started. Total System Global Area 835104768 bytes Fixed Size 2231088 bytes Variable Size 570426576 bytes Database Buffers 260046848 bytes Redo Buffers 2400256 bytes SQL> SQL> SQL> alter database mount; Database altered. SQL> alter database open; Database altered. SQL> |
5.12、节点2修改pfile文件，启动数据库
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 |
--节点2修改pfile [oracle@rac2 ~]$ cd $ORACLE_HOME/dbs [oracle@rac2 dbs]$ pwd /u01/app/oracle/product/11.2.0/dbhome_1/dbs [oracle@rac2 dbs]$ [oracle@rac2 dbs]$ vi initRACDB2.ora SPFILE='+DATA/RACDB/spfileRACDB.ora' 改为 SPFILE='+DATA/RACDB/PARAMETERFILE/spfile.269.839292173' --节点2启动数据库 [oracle@rac2 dbs]$ sqlplus / as sysdba SQL*Plus: Release 11.2.0.2.0 Production on Wed Feb 12 00:46:39 2014 Copyright (c) 1982, 2010, Oracle. All rights reserved. Connected to an idle instance. SQL> startup nomount; ORACLE instance started. Total System Global Area 835104768 bytes Fixed Size 2231088 bytes Variable Size 562037968 bytes Database Buffers 268435456 bytes Redo Buffers 2400256 bytes SQL> SQL> alter database mount; Database altered. SQL> alter database open; Database altered. SQL> |
5.13、验证
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 |
--节点1： [oracle@rac1 ~]$ sqlplus / as sysdba SQL*Plus: Release 11.2.0.2.0 Production on Wed Feb 12 00:49:52 2014 Copyright (c) 1982, 2010, Oracle. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP, Data Mining and Real Application Testing options SQL> show parameter spfile NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ spfile string +DATA/racdb/parameterfile/spfi le.269.839292173 SQL> show parameter control_files NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ control_files string +DATA/racdb/controlfile/curren t.260.838213361, +DATA/racdb/c ontrolfile/current.268.8392885 95, +DATA/racdb/controlfile/cu rrent.272.839288591 SQL> 节点2： [oracle@rac2 ~]$ sqlplus / as sysdba SQL*Plus: Release 11.2.0.2.0 Production on Wed Feb 12 00:50:37 2014 Copyright (c) 1982, 2010, Oracle. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP, Data Mining and Real Application Testing options SQL> show parameter spfile NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ spfile string +DATA/racdb/parameterfile/spfi le.269.839292173 SQL> show parameter control_files NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ control_files string +DATA/racdb/controlfile/curren t.260.838213361, +DATA/racdb/c ontrolfile/current.268.8392885 95, +DATA/racdb/controlfile/cu rrent.272.839288591 SQL> --查看资源状态 [root@rac1 ~]# crsctl stat res -t -------------------------------------------------------------------------------- NAME TARGET STATE SERVER STATE_DETAILS -------------------------------------------------------------------------------- Local Resources -------------------------------------------------------------------------------- ora.DATA.dg ONLINE ONLINE rac1 ONLINE ONLINE rac2 ora.LISTENER.lsnr ONLINE ONLINE rac1 ONLINE ONLINE rac2 ora.OCR_VOTE.dg ONLINE ONLINE rac1 ONLINE ONLINE rac2 ora.OV.dg ONLINE ONLINE rac1 ONLINE ONLINE rac2 ora.asm ONLINE ONLINE rac1 ONLINE ONLINE rac2 ora.gsd ONLINE ONLINE rac1 ONLINE ONLINE rac2 ora.net1.network ONLINE ONLINE rac1 ONLINE ONLINE rac2 ora.ons ONLINE ONLINE rac1 ONLINE ONLINE rac2 -------------------------------------------------------------------------------- Cluster Resources -------------------------------------------------------------------------------- ora.LISTENER_SCAN1.lsnr 1 ONLINE ONLINE rac1 ora.cvu 1 ONLINE ONLINE rac1 ora.oc4j 1 ONLINE ONLINE rac1 ora.rac1.vip 1 ONLINE ONLINE rac1 ora.rac2.vip 1 ONLINE ONLINE rac2 ora.racdb.db 1 ONLINE ONLINE rac1 Open 2 ONLINE ONLINE rac2 Open ora.scan1.vip 1 ONLINE ONLINE rac1 [root@rac1 ~]# |
6、小结
终于修改了，这种方法太麻烦了。最佳的办法是利用RMAN创建控制文件，然后利用命令行修改参数的方式添加创建的控制文件。