更改数据库实例名（生产勿操作）
穷苦书生_万事愁 2024-10-07 13:37:02 阅读 57
单实例修改实例名
单实例的数据库修改SID比较简单，只需要修改pfile相关参数，就可以挂载数据库
RAC 更改实例名
环境说明
<code>#集群状态
[root@testosa ~]# crsctl status resource -t
--------------------------------------------------------------------------------code>
NAME TARGET STATE SERVER STATE_DETAILS
--------------------------------------------------------------------------------
Local Resources
--------------------------------------------------------------------------------
ora.DATA.dg
ONLINE ONLINE testosa
ONLINE ONLINE testosb
ONLINE ONLINE testosc
ora.DATA02.dg
ONLINE ONLINE testosa
ONLINE ONLINE testosb
ONLINE ONLINE testosc
ora.GRID.dg
ONLINE ONLINE testosa
ONLINE ONLINE testosb
ONLINE ONLINE testosc
ora.LISTENER.lsnr
ONLINE ONLINE testosa
ONLINE ONLINE testosb
ONLINE ONLINE testosc
ora.RECOVERY.dg
ONLINE ONLINE testosa
ONLINE ONLINE testosb
ONLINE ONLINE testosc
ora.asm
ONLINE ONLINE testosa Started
ONLINE ONLINE testosb Started
ONLINE ONLINE testosc Started
ora.gsd
OFFLINE OFFLINE testosa
OFFLINE OFFLINE testosb
OFFLINE OFFLINE testosc
ora.net1.network
ONLINE ONLINE testosa
ONLINE ONLINE testosb
ONLINE ONLINE testosc
ora.ons
ONLINE ONLINE testosa
ONLINE ONLINE testosb
ONLINE ONLINE testosc
--------------------------------------------------------------------------------
Cluster Resources
--------------------------------------------------------------------------------
ora.LISTENER_SCAN1.lsnr
1 ONLINE ONLINE testosa
ora.cvu
1 ONLINE ONLINE testosa
ora.oc4j
1 ONLINE ONLINE testosa
ora.rac_db.db
1 ONLINE ONLINE testosc Open
2 ONLINE ONLINE testosa Open
3 ONLINE ONLINE testosb Open
ora.scan1.vip
1 ONLINE ONLINE testosa
ora.testosa.vip
1 ONLINE ONLINE testosa
ora.testosb.vip
1 ONLINE ONLINE testosb
ora.testosc.vip
1 ONLINE ONLINE testosc
[root@testosa ~]#
[root@testosa ~]# srvctl status database -d rac_db
Instance racdb_2 is running on node testosa
Instance racdb_3 is running on node testosb
Instance racdb_1 is running on node testosc
[root@testosa ~]#
#sql查询实例相关信息
SQL> select HOST_NAME,INSTANCE_NUMBER,INSTANCE_NAME,STATUS from gv$instance order by 1;
HOST_NAME INSTANCE_NUMBER INSTANCE_NAME STATUS
--------- --------------- ------------- ------
testosa 2 racdb_2 OPEN
testosb 3 racdb_3 OPEN
testosc 1 racdb_1 OPEN
SQL>
现在需要修改为以下效果：
HOST_NAME INSTANCE_NUMBER INSTANCE_NAME STATUS
--------- --------------- ------------- ------
testosa 1 racdb_1 OPEN
testosb 2 racdb_2 OPEN
testosc 3 racdb_3 OPEN
更改实例名步骤
修改各个节点数据库参数
-- 查看参数类型
SQL> select NAME,VALUE,ISDEFAULT,ISSES_MODIFIABLE,ISSYS_MODIFIABLE,ISINSTANCE_MODIFIABLE,ISMODIFIED,ISBASIC
2 from v$parameter where name = 'instance_name';
NAME VALUE ISDEFAULT ISSES_MODIFIABLE ISSYS_MODIFIABLE ISINSTANCE_MODIFIABLE ISMODIFIED ISBASIC
------------- ------- --------- ---------------- ---------------- --------------------- ---------- -------
instance_name racdb_1 TRUE FALSE FALSE FALSE FALSE FALSE
-- 修改 INSTANCE_NAME 参数
SQL> alter system set INSTANCE_NAME='racdb_1' scope=spfile sid='racdb_2';code>
System altered.
SQL> alter system set INSTANCE_NAME='racdb_2' scope=spfile sid='racdb_3';code>
System altered.
SQL> alter system set INSTANCE_NAME='racdb_3' scope=spfile sid='racdb_1';code>
System altered.
-- 重启所有实例code>
[root@testosa ~]# srvctl stop database -d rac_db
[root@testosa ~]#
[root@testosa ~]# srvctl start database -d rac_db
[root@testosa ~]#
-- 查询实例运行状态
[root@testosa ~]# srvctl status database -d rac_db
Instance racdb_1 is running on node testosa
Instance racdb_2 is running on node testosb
Instance racdb_3 is running on node testosc
[root@testosa ~]#
-- sql查询实例相关信息
SQL> select HOST_NAME,INSTANCE_NUMBER,INSTANCE_NAME,STATUS from gv$instance order by 1;
HOST_NAME INSTANCE_NUMBER INSTANCE_NAME STATUS
---------- --------------- -------------------------------- ------------------------
testosa 1 racdb_1 OPEN
testosb 2 racdb_2 OPEN
testosc 3 racdb_3 OPEN
如上可以看出参数里面修改OK了
#查询集群资源信息
[root@testosa ~]# crsctl status resource -t
--------------------------------------------------------------------------------
NAME TARGET STATE SERVER STATE_DETAILS
--------------------------------------------------------------------------------
Local Resources
--------------------------------------------------------------------------------
ora.DATA.dg
ONLINE ONLINE testosa
ONLINE ONLINE testosb
ONLINE ONLINE testosc
ora.DATA02.dg
ONLINE ONLINE testosa
ONLINE ONLINE testosb
ONLINE ONLINE testosc
ora.GRID.dg
ONLINE ONLINE testosa
ONLINE ONLINE testosb
ONLINE ONLINE testosc
ora.LISTENER.lsnr
ONLINE ONLINE testosa
ONLINE ONLINE testosb
ONLINE ONLINE testosc
ora.RECOVERY.dg
ONLINE ONLINE testosa
ONLINE ONLINE testosb
ONLINE ONLINE testosc
ora.asm
ONLINE ONLINE testosa Started
ONLINE ONLINE testosb Started
ONLINE ONLINE testosc Started
ora.gsd
OFFLINE OFFLINE testosa
OFFLINE OFFLINE testosb
OFFLINE OFFLINE testosc
ora.net1.network
ONLINE ONLINE testosa
ONLINE ONLINE testosb
ONLINE ONLINE testosc
ora.ons
ONLINE ONLINE testosa
ONLINE ONLINE testosb
ONLINE ONLINE testosc
--------------------------------------------------------------------------------
Cluster Resources
--------------------------------------------------------------------------------
ora.LISTENER_SCAN1.lsnr
1 ONLINE ONLINE testosa
ora.cvu
1 ONLINE ONLINE testosb
ora.oc4j
1 ONLINE ONLINE testosb
ora.rac_db.db
1 ONLINE ONLINE testosc Open # 还需要修改此处信息
2 ONLINE ONLINE testosa Open
3 ONLINE ONLINE testosb Open
ora.scan1.vip
1 ONLINE ONLINE testosa
ora.testosa.vip
1 ONLINE ONLINE testosa
ora.testosb.vip
1 ONLINE ONLINE testosb
ora.testosc.vip
1 ONLINE ONLINE testosc
[root@testosa ~]#
如上集群的信息里面，还是乱的
更新ocr信息
移除数据库注册信息
#先停止数据库
[root@testosa ~]# srvctl stop database -d rac_db
[root@testosa ~]#
#移除数据库注册信息
[root@testosa ~]# srvctl remove database -d rac_db
Remove the database rac_db? (y/[n]) y
[root@testosa ~]#
#查询集群资源信息
[root@testosa ~]# crsctl status resource -t
--------------------------------------------------------------------------------
NAME TARGET STATE SERVER STATE_DETAILS
--------------------------------------------------------------------------------
Local Resources
--------------------------------------------------------------------------------
ora.DATA.dg
ONLINE ONLINE testosa
ONLINE ONLINE testosb
ONLINE ONLINE testosc
ora.DATA02.dg
ONLINE ONLINE testosa
ONLINE ONLINE testosb
ONLINE ONLINE testosc
ora.GRID.dg
ONLINE ONLINE testosa
ONLINE ONLINE testosb
ONLINE ONLINE testosc
ora.LISTENER.lsnr
ONLINE ONLINE testosa
ONLINE ONLINE testosb
ONLINE ONLINE testosc
ora.RECOVERY.dg
ONLINE ONLINE testosa
ONLINE ONLINE testosb
ONLINE ONLINE testosc
ora.asm
ONLINE ONLINE testosa Started
ONLINE ONLINE testosb Started
ONLINE ONLINE testosc Started
ora.gsd
OFFLINE OFFLINE testosa
OFFLINE OFFLINE testosb
OFFLINE OFFLINE testosc
ora.net1.network
ONLINE ONLINE testosa
ONLINE ONLINE testosb
ONLINE ONLINE testosc
ora.ons
ONLINE ONLINE testosa
ONLINE ONLINE testosb
ONLINE ONLINE testosc
--------------------------------------------------------------------------------
Cluster Resources
--------------------------------------------------------------------------------
ora.LISTENER_SCAN1.lsnr
1 ONLINE ONLINE testosa
ora.cvu
1 ONLINE ONLINE testosb
ora.oc4j
1 ONLINE ONLINE testosb
ora.scan1.vip
1 ONLINE ONLINE testosa
ora.testosa.vip
1 ONLINE ONLINE testosa
ora.testosb.vip
1 ONLINE ONLINE testosb
ora.testosc.vip
1 ONLINE ONLINE testosc
[root@testosa ~]#
重新注册数据库（oracle用户操作）
[oracle@testosa:/home/oracle]$srvctl add database -d rac_db -o /oracle/app/oracle/product/11.2.0/db_1
[oracle@testosa:/home/oracle]$
#查询集群资源状态
[root@testosa ~]# crsctl status resource -t
--------------------------------------------------------------------------------
NAME TARGET STATE SERVER STATE_DETAILS
--------------------------------------------------------------------------------
Local Resources
--------------------------------------------------------------------------------
ora.DATA.dg
ONLINE ONLINE testosa
ONLINE ONLINE testosb
ONLINE ONLINE testosc
ora.DATA02.dg
ONLINE ONLINE testosa
ONLINE ONLINE testosb
ONLINE ONLINE testosc
ora.GRID.dg
ONLINE ONLINE testosa
ONLINE ONLINE testosb
ONLINE ONLINE testosc
ora.LISTENER.lsnr
ONLINE ONLINE testosa
ONLINE ONLINE testosb
ONLINE ONLINE testosc
ora.RECOVERY.dg
ONLINE ONLINE testosa
ONLINE ONLINE testosb
ONLINE ONLINE testosc
ora.asm
ONLINE ONLINE testosa Started
ONLINE ONLINE testosb Started
ONLINE ONLINE testosc Started
ora.gsd
OFFLINE OFFLINE testosa
OFFLINE OFFLINE testosb
OFFLINE OFFLINE testosc
ora.net1.network
ONLINE ONLINE testosa
ONLINE ONLINE testosb
ONLINE ONLINE testosc
ora.ons
ONLINE ONLINE testosa
ONLINE ONLINE testosb
ONLINE ONLINE testosc
--------------------------------------------------------------------------------
Cluster Resources
--------------------------------------------------------------------------------
ora.LISTENER_SCAN1.lsnr
1 ONLINE ONLINE testosa
ora.cvu
1 ONLINE ONLINE testosb
ora.oc4j
1 ONLINE ONLINE testosb
ora.rac_db.db
1 OFFLINE OFFLINE
ora.scan1.vip
1 ONLINE ONLINE testosa
ora.testosa.vip
1 ONLINE ONLINE testosa
ora.testosb.vip
1 ONLINE ONLINE testosb
ora.testosc.vip
1 ONLINE ONLINE testosc
[root@testosa ~]#
重新注册实例信息
[root@testosa ~]# srvctl add instance -d rac_db -i racdb_1 -n testosa
[root@testosa ~]#
[root@testosa ~]# srvctl add instance -d rac_db -i racdb_2 -n testosb
[root@testosa ~]#
[root@testosa ~]# srvctl add instance -d rac_db -i racdb_3 -n testosc
[root@testosa ~]#
#查询集群资源状态
[root@testosa ~]# crsctl status resource -t
--------------------------------------------------------------------------------
NAME TARGET STATE SERVER STATE_DETAILS
--------------------------------------------------------------------------------
Local Resources
--------------------------------------------------------------------------------
ora.DATA.dg
ONLINE ONLINE testosa
ONLINE ONLINE testosb
ONLINE ONLINE testosc
ora.DATA02.dg
ONLINE ONLINE testosa
ONLINE ONLINE testosb
ONLINE ONLINE testosc
ora.GRID.dg
ONLINE ONLINE testosa
ONLINE ONLINE testosb
ONLINE ONLINE testosc
ora.LISTENER.lsnr
ONLINE ONLINE testosa
ONLINE ONLINE testosb
ONLINE ONLINE testosc
ora.RECOVERY.dg
ONLINE ONLINE testosa
ONLINE ONLINE testosb
ONLINE ONLINE testosc
ora.asm
ONLINE ONLINE testosa Started
ONLINE ONLINE testosb Started
ONLINE ONLINE testosc Started
ora.gsd
OFFLINE OFFLINE testosa
OFFLINE OFFLINE testosb
OFFLINE OFFLINE testosc
ora.net1.network
ONLINE ONLINE testosa
ONLINE ONLINE testosb
ONLINE ONLINE testosc
ora.ons
ONLINE ONLINE testosa
ONLINE ONLINE testosb
ONLINE ONLINE testosc
--------------------------------------------------------------------------------
Cluster Resources
--------------------------------------------------------------------------------
ora.LISTENER_SCAN1.lsnr
1 ONLINE ONLINE testosa
ora.cvu
1 ONLINE ONLINE testosb
ora.oc4j
1 ONLINE ONLINE testosb
ora.rac_db.db
1 OFFLINE OFFLINE
2 OFFLINE OFFLINE
3 OFFLINE OFFLINE
ora.scan1.vip
1 ONLINE ONLINE testosa
ora.testosa.vip
1 ONLINE ONLINE testosa
ora.testosb.vip
1 ONLINE ONLINE testosb
ora.testosc.vip
1 ONLINE ONLINE testosc
[root@testosa ~]#
#启动数据库
[root@testosa ~]# srvctl start database -d rac_db
[root@testosa ~]#
#查询集群资源状态
[root@testosa ~]# crsctl status resource -t
--------------------------------------------------------------------------------
NAME TARGET STATE SERVER STATE_DETAILS
--------------------------------------------------------------------------------
Local Resources
--------------------------------------------------------------------------------
ora.DATA.dg
ONLINE ONLINE testosa
ONLINE ONLINE testosb
ONLINE ONLINE testosc
ora.DATA02.dg
ONLINE ONLINE testosa
ONLINE ONLINE testosb
ONLINE ONLINE testosc
ora.GRID.dg
ONLINE ONLINE testosa
ONLINE ONLINE testosb
ONLINE ONLINE testosc
ora.LISTENER.lsnr
ONLINE ONLINE testosa
ONLINE ONLINE testosb
ONLINE ONLINE testosc
ora.RECOVERY.dg
ONLINE ONLINE testosa
ONLINE ONLINE testosb
ONLINE ONLINE testosc
ora.asm
ONLINE ONLINE testosa Started
ONLINE ONLINE testosb Started
ONLINE ONLINE testosc Started
ora.gsd
OFFLINE OFFLINE testosa
OFFLINE OFFLINE testosb
OFFLINE OFFLINE testosc
ora.net1.network
ONLINE ONLINE testosa
ONLINE ONLINE testosb
ONLINE ONLINE testosc
ora.ons
ONLINE ONLINE testosa
ONLINE ONLINE testosb
ONLINE ONLINE testosc
--------------------------------------------------------------------------------
Cluster Resources
--------------------------------------------------------------------------------
ora.LISTENER_SCAN1.lsnr
1 ONLINE ONLINE testosa
ora.cvu
1 ONLINE ONLINE testosb
ora.oc4j
1 ONLINE ONLINE testosb
ora.rac_db.db
1 ONLINE ONLINE testosa Open
2 ONLINE ONLINE testosb Open
3 ONLINE ONLINE testosc Open
ora.scan1.vip
1 ONLINE ONLINE testosa
ora.testosa.vip
1 ONLINE ONLINE testosa
ora.testosb.vip
1 ONLINE ONLINE testosb
ora.testosc.vip
1 ONLINE ONLINE testosc
[root@testosa ~]#
检查实例名
#数据库查询
SQL> select HOST_NAME,INSTANCE_NUMBER,INSTANCE_NAME,STATUS from gv$instance order by 1;
HOST_NAME INSTANCE_NUMBER INSTANCE_NAME STATUS
---------- --------------- -------------------------------- ------------------------
testosa 1 racdb_1 OPEN
testosb 2 racdb_2 OPEN
testosc 3 racdb_3 OPEN
SQL>
#集群资源查询
[root@testosa ~]# crsctl status resource -t
--------------------------------------------------------------------------------
NAME TARGET STATE SERVER STATE_DETAILS
--------------------------------------------------------------------------------
Local Resources
--------------------------------------------------------------------------------
ora.DATA.dg
ONLINE ONLINE testosa
ONLINE ONLINE testosb
ONLINE ONLINE testosc
ora.DATA02.dg
ONLINE ONLINE testosa
ONLINE ONLINE testosb
ONLINE ONLINE testosc
ora.GRID.dg
ONLINE ONLINE testosa
ONLINE ONLINE testosb
ONLINE ONLINE testosc
ora.LISTENER.lsnr
ONLINE ONLINE testosa
ONLINE ONLINE testosb
ONLINE ONLINE testosc
ora.RECOVERY.dg
ONLINE ONLINE testosa
ONLINE ONLINE testosb
ONLINE ONLINE testosc
ora.asm
ONLINE ONLINE testosa Started
ONLINE ONLINE testosb Started
ONLINE ONLINE testosc Started
ora.gsd
OFFLINE OFFLINE testosa
OFFLINE OFFLINE testosb
OFFLINE OFFLINE testosc
ora.net1.network
ONLINE ONLINE testosa
ONLINE ONLINE testosb
ONLINE ONLINE testosc
ora.ons
ONLINE ONLINE testosa
ONLINE ONLINE testosb
ONLINE ONLINE testosc
--------------------------------------------------------------------------------
Cluster Resources
--------------------------------------------------------------------------------
ora.LISTENER_SCAN1.lsnr
1 ONLINE ONLINE testosa
ora.cvu
1 ONLINE ONLINE testosb
ora.oc4j
1 ONLINE ONLINE testosb
ora.rac_db.db
1 ONLINE ONLINE testosa Open
2 ONLINE ONLINE testosb Open
3 ONLINE ONLINE testosc Open
ora.scan1.vip
1 ONLINE ONLINE testosa
ora.testosa.vip
1 ONLINE ONLINE testosa
ora.testosb.vip
1 ONLINE ONLINE testosb
ora.testosc.vip
1 ONLINE ONLINE testosc
[root@testosa ~]#
参考资料
https://blog.csdn.net/weixin_42405705/article/details/116328072
https://blog.csdn.net/weixin_39992199/article/details/116328076
声明
本文内容仅代表作者观点，或转载于其他网站，本站不以此文作为商业用途
如有涉及侵权，请联系本站进行删除
转载本站原创文章，请注明来源及作者。