oracle 10g rac数据库,Oracle 10g RAC修改数据库实例与主机对应关系

RAC中使用dbca在创建集群数据库时，由于未在主节点[主机号小的节点]上启动图形界面创建数据库，导致最终创建好的集群数据库实例号与主机名号之间关系不对应，例如这里的实验环境如下：

instance_name etltest1对应主机名k1rac4

instance_name etltest2对应主机名k1rac2

下面用实验演示，如何将数据库实例号与主机号的对应关系改为如下：

instance_name etltest1对应主机名k1rac2

instance_name etltest2对应主机名k1rac4

---查看当前集群数据库实例与主机对应关系

[oracle@k1rac2 ~]$ crs_stat.sh

Name Target State Host

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

ora.etltest.db ONLINE ONLINE k1rac4

ora.etltest.etltest1.inst ONLINE ONLINE k1rac4

ora.etltest.etltest2.inst ONLINE ONLINE k1rac2

ora.etltest.etltests.cs ONLINE ONLINE k1rac2

ora.etltest.etltests.etltest2.srv ONLINE ONLINE k1rac2

ora.k1rac2.LISTENER_K1RAC2.lsnr ONLINE ONLINE k1rac2

ora.k1rac2.gsd ONLINE ONLINE k1rac2

ora.k1rac2.ons ONLINE ONLINE k1rac2

ora.k1rac2.vip ONLINE ONLINE k1rac2

ora.k1rac4.LISTENER_K1RAC4.lsnr ONLINE ONLINE k1rac4

ora.k1rac4.gsd ONLINE ONLINE k1rac4

ora.k1rac4.ons ONLINE ONLINE k1rac4

ora.k1rac4.vip ONLINE ONLINE k1rac4

---查看集群数据库实例spfile文件位置

SQL> show parameter spfile

NAME TYPE VALUE

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

spfile string /home/db/oracle/oradata/etl_spfile

---创建集群数据库实例pfile文件

SQL> create pfile='/tmp/pfileetl.ora' from spfile;

File created.

---将以下两行添加到新创建的集群数据库实例pfile文件中

etltest1.instance_name=etltest1

etltest2.instance_name=etltest2

---将主机k1rac2上数据库库实例etltest2密码文件改为实例etltest1所有

[oracle@k1rac2 dbs]$ mv orapwetltest2 orapwetltest1

---将主机k1rac2上数据库库实例etltest2 pfile文件改为实例etltest1所有

[oracle@k1rac2 dbs]$ more initetltest2.ora

SPFILE='/home/db/oracle/oradata/etl_spfile'

[oracle@k1rac2 dbs]$ mv initetltest2.ora initetltest2.ora

---将主机k1rac4上数据库库实例etltest1密码文件改为实例etltest2所有

[oracle@k1rac4 dbs]$ mv orapwetltest1 orapwetltest2

---将主机k1rac4上数据库库实例etltest1 pfile文件改为实例etltest2所有

[oracle@k1rac4 dbs]$ more initetltest1.ora

SPFILE='/home/db/oracle/oradata/etl_spfile'

[oracle@k1rac4 dbs]$ mv initetltest1.ora initetltest2.ora

---停止集群数据库实例

[oracle@k1rac2 dbs]$ srvctl stop database -d etltest

[oracle@k1rac2 dbs]$ crs_stat.sh

Name Target State Host

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

ora.etltest.db OFFLINE OFFLINE

ora.etltest.etltest1.inst OFFLINE OFFLINE

ora.etltest.etltest2.inst OFFLINE OFFLINE

ora.etltest.etltests.cs OFFLINE OFFLINE

ora.etltest.etltests.etltest2.srv OFFLINE OFFLINE

ora.k1rac2.LISTENER_K1RAC2.lsnr ONLINE ONLINE k1rac2

ora.k1rac2.gsd ONLINE ONLINE k1rac2

ora.k1rac2.ons ONLINE ONLINE k1rac2

ora.k1rac2.vip ONLINE ONLINE k1rac2

ora.k1rac4.LISTENER_K1RAC4.lsnr ONLINE ONLINE k1rac4

ora.k1rac4.gsd ONLINE ONLINE k1rac4

ora.k1rac4.ons ONLINE ONLINE k1rac4

ora.k1rac4.vip ONLINE ONLINE k1rac4

---更新集群数据库spfile文件

SQL> create SPFILE='/home/db/oracle/oradata/etl_spfile' from pfile='/tmp/pfileetl.ora';

File created.

---将数据库及实例从集群中移除

[oracle@k1rac2 dbs]$ srvctl remove database -d etltest

Remove the database etltest? (y/[n]) y

[oracle@k1rac2 dbs]$ crs_stat.sh

Name Target State Host

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

ora.k1rac2.LISTENER_K1RAC2.lsnr ONLINE ONLINE k1rac2

ora.k1rac2.gsd ONLINE ONLINE k1rac2

ora.k1rac2.ons ONLINE ONLINE k1rac2

ora.k1rac2.vip ONLINE ONLINE k1rac2

ora.k1rac4.LISTENER_K1RAC4.lsnr ONLINE ONLINE k1rac4

ora.k1rac4.gsd ONLINE ONLINE k1rac4

ora.k1rac4.ons ONLINE ONLINE k1rac4

ora.k1rac4.vip ONLINE ONLINE k1rac4

---重新将数据库及实例添加到集群

[oracle@k1rac2 dbs]$ srvctl add database -d etltest -o $ORACLE_HOME

[oracle@k1rac2 dbs]$ crs_stat.sh

Name Target State Host

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

ora.etltest.db OFFLINE OFFLINE

ora.k1rac2.LISTENER_K1RAC2.lsnr ONLINE ONLINE k1rac2

ora.k1rac2.gsd ONLINE ONLINE k1rac2

ora.k1rac2.ons ONLINE ONLINE k1rac2

ora.k1rac2.vip ONLINE ONLINE k1rac2

ora.k1rac4.LISTENER_K1RAC4.lsnr ONLINE ONLINE k1rac4

ora.k1rac4.gsd ONLINE ONLINE k1rac4

ora.k1rac4.ons ONLINE ONLINE k1rac4

ora.k1rac4.vip ONLINE ONLINE k1rac4

[oracle@k1rac2 dbs]$ srvctl add instance -d etltest -i etltest1 -n k1rac2

[oracle@k1rac2 dbs]$ srvctl add instance -d etltest -i etltest2 -n k1rac4

---查看集群中数据库及实例状态

[oracle@k1rac2 dbs]$ crs_stat.sh

Name Target State Host

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

ora.etltest.db OFFLINE OFFLINE

ora.etltest.etltest1.inst OFFLINE OFFLINE

ora.etltest.etltest2.inst OFFLINE OFFLINE

ora.k1rac2.LISTENER_K1RAC2.lsnr ONLINE ONLINE k1rac2

ora.k1rac2.gsd ONLINE ONLINE k1rac2

ora.k1rac2.ons ONLINE ONLINE k1rac2

ora.k1rac2.vip ONLINE ONLINE k1rac2

ora.k1rac4.LISTENER_K1RAC4.lsnr ONLINE ONLINE k1rac4

ora.k1rac4.gsd ONLINE ONLINE k1rac4

ora.k1rac4.ons ONLINE ONLINE k1rac4

ora.k1rac4.vip ONLINE ONLINE k1rac4

---启动集群数据库

[oracle@k1rac2 dbs]$ srvctl start database -d etltest

[oracle@k1rac2 dbs]$ crs_stat.sh

Name Target State Host

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

ora.etltest.db ONLINE ONLINE k1rac2

ora.etltest.etltest1.inst ONLINE ONLINE k1rac2

ora.etltest.etltest2.inst ONLINE ONLINE k1rac4

ora.k1rac2.LISTENER_K1RAC2.lsnr ONLINE ONLINE k1rac2

ora.k1rac2.gsd ONLINE ONLINE k1rac2

ora.k1rac2.ons ONLINE ONLINE k1rac2

ora.k1rac2.vip ONLINE ONLINE k1rac2

ora.k1rac4.LISTENER_K1RAC4.lsnr ONLINE ONLINE k1rac4

ora.k1rac4.gsd ONLINE ONLINE k1rac4

ora.k1rac4.ons ONLINE ONLINE k1rac4

ora.k1rac4.vip ONLINE ONLINE k1rac4

---添加相应的Service服务

[oracle@k1rac2 dbs]$ srvctl add service -d etltest -s etltests -r etltest1 -a etltest2 -P BASIC

[oracle@k1rac2 dbs]$ crs_stat.sh

Name Target State Host

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

ora.etltest.db ONLINE ONLINE k1rac2

ora.etltest.etltest1.inst ONLINE ONLINE k1rac2

ora.etltest.etltest2.inst ONLINE ONLINE k1rac4

ora.etltest.etltests.cs OFFLINE OFFLINE

ora.etltest.etltests.etltest1.srv OFFLINE OFFLINE

ora.k1rac2.LISTENER_K1RAC2.lsnr ONLINE ONLINE k1rac2

ora.k1rac2.gsd ONLINE ONLINE k1rac2

ora.k1rac2.ons ONLINE ONLINE k1rac2

ora.k1rac2.vip ONLINE ONLINE k1rac2

ora.k1rac4.LISTENER_K1RAC4.lsnr ONLINE ONLINE k1rac4

ora.k1rac4.gsd ONLINE ONLINE k1rac4

ora.k1rac4.ons ONLINE ONLINE k1rac4

ora.k1rac4.vip ONLINE ONLINE k1rac4

---启动新添加的服务并查看集群状态

[oracle@k1rac2 dbs]$ srvctl start service -d etltest

[oracle@k1rac2 dbs]$ crs_stat.sh

Name Target State Host

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

ora.etltest.db ONLINE ONLINE k1rac2

ora.etltest.etltest1.inst ONLINE ONLINE k1rac2

ora.etltest.etltest2.inst ONLINE ONLINE k1rac4

ora.etltest.etltests.cs ONLINE ONLINE k1rac2

ora.etltest.etltests.etltest1.srv ONLINE ONLINE k1rac2

ora.k1rac2.LISTENER_K1RAC2.lsnr ONLINE ONLINE k1rac2

ora.k1rac2.gsd ONLINE ONLINE k1rac2

ora.k1rac2.ons ONLINE ONLINE k1rac2

ora.k1rac2.vip ONLINE ONLINE k1rac2

ora.k1rac4.LISTENER_K1RAC4.lsnr ONLINE ONLINE k1rac4

ora.k1rac4.gsd ONLINE ONLINE k1rac4

ora.k1rac4.ons ONLINE ONLINE k1rac4

ora.k1rac4.vip ONLINE ONLINE k1rac4

--- 更新集群节点listener和tns文件