分类目录归档：Database

Oracle 10g RAC修改数据库名与实例名

发表回复

生产数据库系统在某些情况下[数据库名与业务系统名匹配]，可能需要修改数据库和实例名，该篇将详细演示，如何修改RAC数据库及实例名。
###原数据库及实例名
db_name: test
instance_name1: tets1
instance_name2: test2
###修改后数据库及实例名
db_name: k13test
instance_name1: k13tets1
instance_name2: k13test2

---查看原集群数据库和实例名运行状态
[oracle@k1rac3 ~]$ crs_stat.sh
Name                                Target     State      Host      
----------------------------------- ---------- ---------  -------   
ora.k1rac1.LISTENER_K1RAC1.lsnr     ONLINE     ONLINE     k1rac1    
ora.k1rac1.gsd                      ONLINE     ONLINE     k1rac1    
ora.k1rac1.ons                      ONLINE     ONLINE     k1rac1    
ora.k1rac1.vip                      ONLINE     ONLINE     k1rac1    
ora.k1rac3.LISTENER_K1RAC3.lsnr     ONLINE     ONLINE     k1rac3    
ora.k1rac3.gsd                      ONLINE     ONLINE     k1rac3    
ora.k1rac3.ons                      ONLINE     ONLINE     k1rac3    
ora.k1rac3.vip                      ONLINE     ONLINE     k1rac3    
ora.test.p1p2.cs                    ONLINE     ONLINE     k1rac3    
ora.test.p1p2.test1.srv             ONLINE     ONLINE     k1rac1    
ora.test.p1p2.test2.srv             ONLINE     ONLINE     k1rac3    
ora.test.db                         ONLINE     ONLINE     k1rac3    
ora.test.test1.inst                 ONLINE     ONLINE     k1rac1    
ora.test.test2.inst                 ONLINE     ONLINE     k1rac3    

---查看集群数据库实例spfile文件位置
SQL> show parameter spfile
 
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
spfile                               string      /home/db/oracle/oradata/spfile

---创建集群数据库实例pfile文件
SQL> create pfile='/tmp/pfiletest.ora' from spfile;
 
File created.

---停止集群数据库和实例
[oracle@k1rac3 ~]$ srvctl stop database -d test
[oracle@k1rac3 ~]$ crs_stat.sh
Name                                Target     State      Host      
----------------------------------- ---------- ---------  -------   
ora.k1rac1.LISTENER_K1RAC1.lsnr     ONLINE     ONLINE     k1rac1    
ora.k1rac1.gsd                      ONLINE     ONLINE     k1rac1    
ora.k1rac1.ons                      ONLINE     ONLINE     k1rac1    
ora.k1rac1.vip                      ONLINE     ONLINE     k1rac1    
ora.k1rac3.LISTENER_K1RAC3.lsnr     ONLINE     ONLINE     k1rac3    
ora.k1rac3.gsd                      ONLINE     ONLINE     k1rac3    
ora.k1rac3.ons                      ONLINE     ONLINE     k1rac3    
ora.k1rac3.vip                      ONLINE     ONLINE     k1rac3    
ora.test.p1p2.cs                    OFFLINE    OFFLINE              
ora.test.p1p2.test1.srv             OFFLINE    OFFLINE              
ora.test.p1p2.test2.srv             OFFLINE    OFFLINE              
ora.test.db                         OFFLINE    OFFLINE              
ora.test.test1.inst                 OFFLINE    OFFLINE              
ora.test.test2.inst                 OFFLINE    OFFLINE              

---将新创建的集群数据库实例pfile文件中cluster_database参数值修改为flase
[oracle@k1rac1 ~]$ cat /tmp/pfiletest.ora
test1.__db_cache_size=80815849472
test2.__db_cache_size=82376130560
test1.__java_pool_size=117440512
test2.__java_pool_size=117440512
test1.__large_pool_size=33554432
test2.__large_pool_size=33554432
test1.__shared_pool_size=4764729344
test2.__shared_pool_size=3321888768
test1.__streams_pool_size=150994944
test2.__streams_pool_size=33554432
*.audit_file_dest='/home/db/oracle/admin/test/adump'
*.background_dump_dest='/home/db/oracle/admin/test/bdump'
*.cluster_database_instances=2
*.compatible='10.2.0.5.0'
*.control_files='/home/db/oracle/oradata/control1.ctl','/home/db/oracle/oradata/control2.ctl','/home/db/oracle/oradata/control3.ctl'
*.cluster_database=flase
*.core_dump_dest='/home/db/oracle/admin/test/cdump'
*.db_block_size=32768
*.db_domain=''
*.db_file_multiblock_read_count=16
*.db_name='test'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=testXDB)'
test1.instance_number=1
test2.instance_number=2
*.job_queue_processes=10
test1.local_listener='LISTENER_K1RAC1'
test2.local_listener='LISTENER_K1RAC3'
*.open_cursors=300
*.parallel_max_servers=256
*.parallel_min_servers=0
*.pga_aggregate_target=21474836480
*.processes=1000
*.remote_listener='LISTENERS_p1p2'
*.remote_login_passwordfile='exclusive'
*.service_names='p1p2'
*.sessions=1100
*.sga_max_size=85899345920
*.sga_target=85899345920
test1.thread=1
test2.thread=2
*.undo_management='AUTO'
test1.undo_tablespace='UNDOTBS1'
test2.undo_tablespace='UNDOTBS2'
*.user_dump_dest='/home/db/oracle/admin/test/udump'
*.utl_file_dir='/home/db/oracle/logmnr'

---使用修改后的pfile参数文件以单一方式启动集群数据库到mount状态
[oracle@k1rac1 ~]$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.5.0 - Production on Fri Dec 19 11:52:22 2014

Copyright (c) 1982, 2010, Oracle.  All Rights Reserved.

Connected to an idle instance.

SQL> startup mount pfile='/tmp/pfiletest.ora'
ORACLE instance started.

Total System Global Area 8.5899E+10 bytes
Fixed Size                  2230024 bytes
Variable Size            5066733816 bytes
Database Buffers         8.0816E+10 bytes
Redo Buffers               14532608 bytes
Database mounted.

---使用nid工具将原集群数据库名test修改为k13test
[oracle@k1rac1 ~]$ nid

DBNEWID: Release 10.2.0.5.0 - Production on Fri Dec 19 11:53:40 2014

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

Keyword     Description                    (Default)
----------------------------------------------------
TARGET      Username/Password              (NONE)
DBNAME      New database name              (NONE)
LOGFILE     Output Log                     (NONE)
REVERT      Revert failed change           NO
SETNAME     Set a new database name only   NO
APPEND      Append to output log           NO
HELP        Displays these messages        NO

[oracle@k1rac1 ~]$ nid TARGET=sys/manager DBNAME=k13test setname=y

DBNEWID: Release 10.2.0.5.0 - Production on Fri Dec 19 11:57:29 2014

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

Connected to database TEST (DBID=2141888172)

Connected to server version 10.2.0

Control Files in database:
    /home/db/oracle/oradata/control1.ctl
    /home/db/oracle/oradata/control2.ctl
    /home/db/oracle/oradata/control3.ctl

Change database name of database TEST to K13TEST? (Y/[N]) => y

Proceeding with operation
Changing database name from TEST to K13TEST
    Control File /home/db/oracle/oradata/control1.ctl - modified
    Control File /home/db/oracle/oradata/control2.ctl - modified
    Control File /home/db/oracle/oradata/control3.ctl - modified
    Datafile /home/db/oracle/oradata/system01.dbf - wrote new name
    Datafile /home/db/oracle/oradata/undotbs01.dbf - wrote new name
    Datafile /home/db/oracle/oradata/sysaux01.dbf - wrote new name
    Datafile /home/db/oracle/oradata/undotbs02.dbf - wrote new name
    Datafile /home/db/oracle/oradata/users01.dbf - wrote new name
    Datafile /home/db/oracle/oradata/userdata028.dbf - wrote new name
    Datafile /home/db/oracle/oradata/userdata029.dbf - wrote new name
    Datafile /home/db/oracle/oradata/userdata030.dbf - wrote new name
    Datafile /home/db/oracle/oradata/userdata031.dbf - wrote new name
    Datafile /home/db/oracle/oradata/userdata032.dbf - wrote new name
    Datafile /home/db/oracle/oradata/userdata033.dbf - wrote new name
    Datafile /home/db/oracle/oradata/userdata034.dbf - wrote new name
    Datafile /home/db/oracle/oradata/userdata035.dbf - wrote new name
    Datafile /home/db/oracle/oradata/userdata036.dbf - wrote new name
    Datafile /home/db/oracle/oradata/userdata037.dbf - wrote new name
    Datafile /home/db/oracle/oradata/userdata038.dbf - wrote new name
    Datafile /home/db/oracle/oradata/userdata039.dbf - wrote new name
    Datafile /home/db/oracle/oradata/userdata040.dbf - wrote new name
    Datafile /home/db/oracle/oradata/userdata041.dbf - wrote new name
    Datafile /home/db/oracle/oradata/userdata042.dbf - wrote new name
    Datafile /home/db/oracle/oradata/userdata043.dbf - wrote new name
    Datafile /home/db/oracle/oradata/userdata044.dbf - wrote new name
    Datafile /home/db/oracle/oradata/userdata045.dbf - wrote new name
    Datafile /home/db/oracle/oradata/userdata046.dbf - wrote new name
    Datafile /home/db/oracle/oradata/userdata047.dbf - wrote new name
    Datafile /home/db/oracle/oradata/userdata048.dbf - wrote new name
    Datafile /home/db/oracle/oradata/userdata049.dbf - wrote new name
    Datafile /home/db/oracle/oradata/userdata050.dbf - wrote new name
    Datafile /home/db/oracle/oradata/userdata051.dbf - wrote new name
    Datafile /home/db/oracle/oradata/userdata052.dbf - wrote new name
    Datafile /home/db/oracle/oradata/userdata053.dbf - wrote new name
    Datafile /home/db/oracle/oradata/userdata054.dbf - wrote new name
    Datafile /home/db/oracle/oradata/userdata055.dbf - wrote new name
    Datafile /home/db/oracle/oradata/userdata056.dbf - wrote new name
    Datafile /home/db/oracle/oradata/userdata057.dbf - wrote new name
    Datafile /home/db/oracle/oradata/userdata058.dbf - wrote new name
    Datafile /home/db/oracle/oradata/userdata059.dbf - wrote new name
    Datafile /home/db/oracle/oradata/userdata060.dbf - wrote new name
    Datafile /home/db/oracle/oradata/userdata061.dbf - wrote new name
    Datafile /home/db/oracle/oradata/userdata062.dbf - wrote new name
    Datafile /home/db/oracle/oradata/userdata068.dbf - wrote new name
    Datafile /home/db/oracle/oradata/userdata063.dbf - wrote new name
    Datafile /home/db/oracle/oradata/userdata064.dbf - wrote new name
    Datafile /home/db/oracle/oradata/userdata065.dbf - wrote new name
    Datafile /home/db/oracle/oradata/userdata066.dbf - wrote new name
    Datafile /home/db/oracle/oradata/userdata067.dbf - wrote new name
    Datafile /home/db/oracle/oradata/userdata069.dbf - wrote new name
    Datafile /home/db/oracle/oradata/userdata070.dbf - wrote new name
    Datafile /home/db/oracle/oradata/userdata071.dbf - wrote new name
    Datafile /home/db/oracle/oradata/userdata072.dbf - wrote new name
    Datafile /home/db/oracle/oradata/userdata073.dbf - wrote new name
    Datafile /home/db/oracle/oradata/userdata074.dbf - wrote new name
    Datafile /home/db/oracle/oradata/userdata075.dbf - wrote new name
    Datafile /home/db/oracle/oradata/undotbs03.dbf - wrote new name
    Datafile /home/db/oracle/oradata/undotbs04.dbf - wrote new name
    Datafile /home/db/oracle/oradata/undotbs06.dbf - wrote new name
    Datafile /home/db/oracle/oradata/undotbs05.dbf - wrote new name
    Datafile /home/db/oracle/oradata/temp01.dbf - wrote new name
    Datafile /home/db/oracle/oradata/temp02.dbf - wrote new name
    Datafile /home/db/oracle/oradata/temp03.dbf - wrote new name
    Control File /home/db/oracle/oradata/control1.ctl - wrote new name
    Control File /home/db/oracle/oradata/control2.ctl - wrote new name
    Control File /home/db/oracle/oradata/control3.ctl - wrote new name
    Instance shut down

Database name changed to K13TEST.
Modify parameter file and generate a new password file before restarting.
Succesfully changed database name.
DBNEWID - Completed succesfully.

---使用nid工具修改完数据库名后会自动将数据库shutdown
[oracle@k1rac1 ~]$ ps -ef|grep smon
oracle    9005 12698  0 11:58 pts/0    00:00:00 grep smon

---将创建的集群数据库实例pfile文件中test1全局替换成k13test1，test2全局替换成k13test2，test全局替换成k13test
[oracle@k1rac1 ~]$ cat /tmp/pfiletest.ora 
k13test1.__db_cache_size=80815849472
k13test2.__db_cache_size=82376130560
k13test1.__java_pool_size=117440512
k13test2.__java_pool_size=117440512
k13test1.__large_pool_size=33554432
k13test2.__large_pool_size=33554432
k13test1.__shared_pool_size=4764729344
k13test2.__shared_pool_size=3321888768
k13test1.__streams_pool_size=150994944
k13test2.__streams_pool_size=33554432
*.audit_file_dest='/home/db/oracle/admin/k13test/adump'
*.background_dump_dest='/home/db/oracle/admin/k13test/bdump'
*.cluster_database_instances=2
*.compatible='10.2.0.5.0'
*.control_files='/home/db/oracle/oradata/control1.ctl','/home/db/oracle/oradata/control2.ctl','/home/db/oracle/oradata/control3.ctl'
*.cluster_database=true
*.core_dump_dest='/home/db/oracle/admin/k13test/cdump'
*.db_block_size=32768
*.db_domain=''
*.db_file_multiblock_read_count=16
*.db_name='k13test'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=testXDB)'
k13test1.instance_number=1
k13test2.instance_number=2
*.job_queue_processes=10
k13test1.local_listener='LISTENER_K1RAC1'
k13test2.local_listener='LISTENER_K1RAC3'
*.open_cursors=300
*.parallel_max_servers=256
*.parallel_min_servers=0
*.pga_aggregate_target=21474836480
*.processes=1000
*.remote_listener='LISTENERS_p1p2'
*.remote_login_passwordfile='exclusive'
*.service_names='p1p2'
*.sessions=1100
*.sga_max_size=85899345920
*.sga_target=85899345920
k13test1.thread=1
k13test2.thread=2
*.undo_management='AUTO'
k13test1.undo_tablespace='UNDOTBS1'
k13test2.undo_tablespace='UNDOTBS2'
*.user_dump_dest='/home/db/oracle/admin/k13test/udump'
*.utl_file_dir='/home/db/oracle/logmnr'

---两节点分别修改原集群数据库实例dump上层目录名
[oracle@k1rac1 ~]$ cd /home/db/oracle/admin
[oracle@k1rac1 admin]$ mv test k13test
[oracle@k1rac3 ~]$ cd /home/db/oracle/admin
[oracle@k1rac3 admin]$ mv test k13test

---两节点分别修改原集群数据库实例密码文件和参数文件名
[oracle@k1rac1 ~]$ cd $ORACLE_HOME/dbs
[oracle@k1rac1 dbs]$ more inittest1.ora 
SPFILE='/home/db/oracle/oradata/spfile'
[oracle@k1rac1 dbs]$ mv inittest1.ora initk13test1.ora
[oracle@k1rac1 dbs]$ mv orapwtest1 orapwk13test1
[oracle@k1rac3 ~]$ cd $ORACLE_HOME/dbs
[oracle@k1rac3 dbs]$ more inittest2.ora 
SPFILE='/home/db/oracle/oradata/spfile'
[oracle@k1rac3 dbs]$ mv inittest2.ora initk13test2.ora
[oracle@k1rac3 dbs]$ mv orapwtest2 orapwk13test2

---更新集群数据库spfile文件
[oracle@k1rac1 ~]$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.5.0 - Production on Fri Dec 19 12:00:29 2014

Copyright (c) 1982, 2010, Oracle.  All Rights Reserved.

Connected to an idle instance.

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

File created.

---将原数据库及实例从集群中移除
[oracle@k1rac1 ~]$ crs_stat.sh
Name                                Target     State      Host      
----------------------------------- ---------- ---------  -------   
ora.k1rac1.LISTENER_K1RAC1.lsnr     ONLINE     ONLINE     k1rac1    
ora.k1rac1.gsd                      ONLINE     ONLINE     k1rac1    
ora.k1rac1.ons                      ONLINE     ONLINE     k1rac1    
ora.k1rac1.vip                      ONLINE     ONLINE     k1rac1    
ora.k1rac3.LISTENER_K1RAC3.lsnr     ONLINE     ONLINE     k1rac3    
ora.k1rac3.gsd                      ONLINE     ONLINE     k1rac3    
ora.k1rac3.ons                      ONLINE     ONLINE     k1rac3    
ora.k1rac3.vip                      ONLINE     ONLINE     k1rac3    
ora.test.p1p2.cs                    OFFLINE    OFFLINE              
ora.test.p1p2.test1.srv             OFFLINE    OFFLINE              
ora.test.p1p2.test2.srv             OFFLINE    OFFLINE              
ora.test.db                         OFFLINE    OFFLINE              
ora.test.test1.inst                 OFFLINE    OFFLINE              
ora.test.test2.inst                 OFFLINE    OFFLINE              
[oracle@k1rac1 ~]$ srvctl remove database -d test
Remove the database test? (y/[n]) y
[oracle@k1rac1 ~]$ crs_stat.sh
Name                                Target     State      Host      
----------------------------------- ---------- ---------  -------   
ora.k1rac1.LISTENER_K1RAC1.lsnr     ONLINE     ONLINE     k1rac1    
ora.k1rac1.gsd                      ONLINE     ONLINE     k1rac1    
ora.k1rac1.ons                      ONLINE     ONLINE     k1rac1    
ora.k1rac1.vip                      ONLINE     ONLINE     k1rac1    
ora.k1rac3.LISTENER_K1RAC3.lsnr     ONLINE     ONLINE     k1rac3    
ora.k1rac3.gsd                      ONLINE     ONLINE     k1rac3    
ora.k1rac3.ons                      ONLINE     ONLINE     k1rac3    
ora.k1rac3.vip                      ONLINE     ONLINE     k1rac3    

---重新将修改完名数据库及实例添加到集群
[oracle@k1rac1 ~]$ srvctl add database -d k13test -o $ORACLE_HOME
[oracle@k1rac1 ~]$ crs_stat.sh
Name                                Target     State      Host      
----------------------------------- ---------- ---------  -------   
ora.k13test.db                      OFFLINE    OFFLINE              
ora.k1rac1.LISTENER_K1RAC1.lsnr     ONLINE     ONLINE     k1rac1    
ora.k1rac1.gsd                      ONLINE     ONLINE     k1rac1    
ora.k1rac1.ons                      ONLINE     ONLINE     k1rac1    
ora.k1rac1.vip                      ONLINE     ONLINE     k1rac1    
ora.k1rac3.LISTENER_K1RAC3.lsnr     ONLINE     ONLINE     k1rac3    
ora.k1rac3.gsd                      ONLINE     ONLINE     k1rac3    
ora.k1rac3.ons                      ONLINE     ONLINE     k1rac3    
ora.k1rac3.vip                      ONLINE     ONLINE     k1rac3    
[oracle@k1rac1 ~]$ srvctl add instance -d k13test -i k13test1 -n k1rac1
[oracle@k1rac1 ~]$ srvctl add instance -d k13test -i k13test2 -n k1rac3

------查看集群中数据库及实例状态
[oracle@k1rac1 ~]$ crs_stat.sh
Name                                   Target     State      Host      
-----------------------------------    ---------- ---------  -------   
ora.k13test.db                         OFFLINE    OFFLINE              
ora.k13test.k13test1.inst              OFFLINE    OFFLINE              
ora.k13test.k13test2.inst              OFFLINE    OFFLINE              
ora.k1rac1.LISTENER_K1RAC1.lsnr        ONLINE     ONLINE     k1rac1    
ora.k1rac1.gsd                         ONLINE     ONLINE     k1rac1    
ora.k1rac1.ons                         ONLINE     ONLINE     k1rac1    
ora.k1rac1.vip                         ONLINE     ONLINE     k1rac1    
ora.k1rac3.LISTENER_K1RAC3.lsnr        ONLINE     ONLINE     k1rac3    
ora.k1rac3.gsd                         ONLINE     ONLINE     k1rac3    
ora.k1rac3.ons                         ONLINE     ONLINE     k1rac3    
ora.k1rac3.vip                         ONLINE     ONLINE     k1rac3

---启动集群数据库
[oracle@k1rac1 ~]$ srvctl start database -d k13test
[oracle@k1rac1 ~]$ crs_stat.sh
Name                                   Target     State      Host      
-----------------------------------    ---------- ---------  -------   
ora.k13test.db                         ONLINE     ONLINE     k1rac3    
ora.k13test.k13test1.inst              ONLINE     ONLINE     k1rac1    
ora.k13test.k13test2.inst              ONLINE     ONLINE     k1rac3    
ora.k1rac1.LISTENER_K1RAC1.lsnr        ONLINE     ONLINE     k1rac1    
ora.k1rac1.gsd                         ONLINE     ONLINE     k1rac1    
ora.k1rac1.ons                         ONLINE     ONLINE     k1rac1    
ora.k1rac1.vip                         ONLINE     ONLINE     k1rac1    
ora.k1rac3.LISTENER_K1RAC3.lsnr        ONLINE     ONLINE     k1rac3    
ora.k1rac3.gsd                         ONLINE     ONLINE     k1rac3    
ora.k1rac3.ons                         ONLINE     ONLINE     k1rac3    
ora.k1rac3.vip                         ONLINE     ONLINE     k1rac3    

---添加相应的Service服务
[oracle@k1rac1 ~]$ srvctl add service -d k13test -s p1p2 -r k13test1,k13test2 -P PRECONNECT
[oracle@k1rac1 ~]$ crs_stat.sh
Name                                   Target     State      Host      
-----------------------------------    ---------- ---------  -------   
ora.k1rac1.LISTENER_K1RAC1.lsnr        ONLINE     ONLINE     k1rac1    
ora.k1rac1.gsd                         ONLINE     ONLINE     k1rac1    
ora.k1rac1.ons                         ONLINE     ONLINE     k1rac1    
ora.k1rac1.vip                         ONLINE     ONLINE     k1rac1    
ora.k1rac3.LISTENER_K1RAC3.lsnr        ONLINE     ONLINE     k1rac3    
ora.k1rac3.gsd                         ONLINE     ONLINE     k1rac3    
ora.k1rac3.ons                         ONLINE     ONLINE     k1rac3    
ora.k1rac3.vip                         ONLINE     ONLINE     k1rac3    
ora.k13test.p1p2.cs                    OFFLINE    OFFLINE              
ora.k13test.p1p2.k13test1.srv          OFFLINE    OFFLINE              
ora.k13test.p1p2.k13test2.srv          OFFLINE    OFFLINE              
ora.k13test.db                         ONLINE     ONLINE     k1rac3    
ora.k13test.test1.inst                 ONLINE     ONLINE     k1rac1    
ora.k13test.test2.inst                 ONLINE     ONLINE     k1rac3    

---启动新添加的服务并查看集群状态
[oracle@k1rac1 ~]$ srvctl start service -d k13test
[oracle@k1rac1 ~]$ crs_stat.sh
Name                                   Target     State      Host      
-----------------------------------    ---------- ---------  -------   
ora.k1rac1.LISTENER_K1RAC1.lsnr        ONLINE     ONLINE     k1rac1    
ora.k1rac1.gsd                         ONLINE     ONLINE     k1rac1    
ora.k1rac1.ons                         ONLINE     ONLINE     k1rac1    
ora.k1rac1.vip                         ONLINE     ONLINE     k1rac1    
ora.k1rac3.LISTENER_K1RAC3.lsnr        ONLINE     ONLINE     k1rac3    
ora.k1rac3.gsd                         ONLINE     ONLINE     k1rac3    
ora.k1rac3.ons                         ONLINE     ONLINE     k1rac3    
ora.k1rac3.vip                         ONLINE     ONLINE     k1rac3    
ora.k13test.p1p2.cs                    ONLINE     ONLINE     k1rac3    
ora.k13test.p1p2.k13test1.srv          ONLINE     ONLINE     k1rac1    
ora.k13test.p1p2.k13test2.srv          ONLINE     ONLINE     k1rac3    
ora.k13test.db                         ONLINE     ONLINE     k1rac3    
ora.k13test.test1.inst                 ONLINE     ONLINE     k1rac1    
ora.k13test.test2.inst                 ONLINE     ONLINE     k1rac3    

---最后再更新listener.ora及tnsnames.ora文件信息

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文件