客户有个需求,需要将在一个包含多个rac、多个single instance的大cluster中的某个rac 节点,改成single instance。数据文件还在asm上,原来的数据文件还要继续时候用。
我们可以如下操作:
High Level Step:
1.备份spfile
2.停需要转换的rac database
3.删除在cluster中注册的这个rac database对应的service信息,对应的instance信息,和对应的database信息。
4.修改spfile中,删除关于cluster有关的信息
5.启动单实例,删除多余的redo和undo
6.将spfile还原回asm上,并且将ORACLE_SID从ora11g1改成ora11g
下面我们来具体操作:
(1)备份spfile
切到grid用户
[oracle@rac1 ~]$ crsctl stat res -t
--------------------------------------------------------------------------------
NAME TARGET STATE SERVER STATE_DETAILS
--------------------------------------------------------------------------------
Local Resources
--------------------------------------------------------------------------------
ora.ACFS.dg
ONLINE ONLINE rac1
ONLINE ONLINE rac2
ora.DATA.dg
ONLINE ONLINE rac1
ONLINE ONLINE rac2
ora.FRA.dg
ONLINE ONLINE rac1
ONLINE ONLINE rac2
ora.LISTENER.lsnr
ONLINE ONLINE rac1
ONLINE ONLINE rac2
ora.NEW_FRA.dg
OFFLINE OFFLINE rac1
OFFLINE OFFLINE rac2
ora.OCRVOT.dg
ONLINE ONLINE rac1
ONLINE ONLINE rac2
ora.acfs.acfsvol.acfs
ONLINE ONLINE rac1 mounted on /acfs
ONLINE ONLINE rac2 mounted on /acfs
ora.asm
ONLINE ONLINE rac1 Started
ONLINE ONLINE rac2 Started
ora.gsd
OFFLINE OFFLINE rac1
OFFLINE OFFLINE rac2
ora.net1.network
ONLINE ONLINE rac1
ONLINE ONLINE rac2
ora.ons
ONLINE ONLINE rac1
ONLINE ONLINE rac2
ora.registry.acfs
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.ora11g.db
1 ONLINE ONLINE rac1 Open <<<<<
2 ONLINE ONLINE rac2 Open <<<<<
ora.ora11g.myserv.svc
1 ONLINE ONLINE rac1 <<<<<
ora.ora11g.srv_di_1.svc
1 ONLINE ONLINE rac2 <<<<<
ora.prydb.db
1 OFFLINE OFFLINE Instance Shutdown
ora.rac1.vip
1 ONLINE ONLINE rac1
ora.rac2.vip
1 ONLINE ONLINE rac2
ora.scan1.vip
1 ONLINE ONLINE rac1
[oracle@rac1 ~]$
切到oracle用户
[oracle@rac1 ~]$ sqlplus "/ as sysdba"
SQL*Plus: Release 11.2.0.4.0 Production on Wed Aug 17 10:22:25 2016
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Real Application Clusters and Automatic Storage Management options
SQL> show parameter spfile
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
spfile string +DATA/ora11g/spfileora11g.ora
SQL>
SQL>
SQL> create pfile='/tmp/initora11g.ora.bak' from spfile;
File created.
SQL>
(2)停需要转换的数据库实例
[oracle@rac1 ~]$ srvctl stop database -d ora11g
[oracle@rac1 ~]$
切到grid用户
[oracle@rac1 ~]$ crsctl stat res -t
--------------------------------------------------------------------------------
NAME TARGET STATE SERVER STATE_DETAILS
--------------------------------------------------------------------------------
Local Resources
--------------------------------------------------------------------------------
ora.ACFS.dg
ONLINE ONLINE rac1
ONLINE ONLINE rac2
ora.DATA.dg
ONLINE ONLINE rac1
ONLINE ONLINE rac2
ora.FRA.dg
ONLINE ONLINE rac1
ONLINE ONLINE rac2
ora.LISTENER.lsnr
ONLINE ONLINE rac1
ONLINE ONLINE rac2
ora.NEW_FRA.dg
OFFLINE OFFLINE rac1
OFFLINE OFFLINE rac2
ora.OCRVOT.dg
ONLINE ONLINE rac1
ONLINE ONLINE rac2
ora.acfs.acfsvol.acfs
ONLINE ONLINE rac1 mounted on /acfs
ONLINE ONLINE rac2 mounted on /acfs
ora.asm
ONLINE ONLINE rac1 Started
ONLINE ONLINE rac2 Started
ora.gsd
OFFLINE OFFLINE rac1
OFFLINE OFFLINE rac2
ora.net1.network
ONLINE ONLINE rac1
ONLINE ONLINE rac2
ora.ons
ONLINE ONLINE rac1
ONLINE ONLINE rac2
ora.registry.acfs
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.ora11g.db
1 OFFLINE OFFLINE Instance Shutdown <<<<
2 OFFLINE OFFLINE Instance Shutdown <<<<<
ora.ora11g.myserv.svc
1 OFFLINE OFFLINE <<<<<
ora.ora11g.srv_di_1.svc
1 OFFLINE OFFLINE <<<<
ora.prydb.db
1 OFFLINE OFFLINE Instance Shutdown
ora.rac1.vip
1 ONLINE ONLINE rac1
ora.rac2.vip
1 ONLINE ONLINE rac2
ora.scan1.vip
1 ONLINE ONLINE rac1
[oracle@rac1 ~]$
(3)删除数据库实例在cluster中的注册信息
切到oracle用户
[oracle@rac1 ~]$ srvctl remove service -d ora11g -s myserv
[oracle@rac1 ~]$
[oracle@rac1 ~]$ srvctl remove service -d ora11g -s srv_di_1
[oracle@rac1 ~]$
切到grid用户
[oracle@rac1 ~]$ crsctl stat res -t
--------------------------------------------------------------------------------
NAME TARGET STATE SERVER STATE_DETAILS
--------------------------------------------------------------------------------
Local Resources
--------------------------------------------------------------------------------
ora.ACFS.dg
ONLINE ONLINE rac1
ONLINE ONLINE rac2
ora.DATA.dg
ONLINE ONLINE rac1
ONLINE ONLINE rac2
ora.FRA.dg
ONLINE ONLINE rac1
ONLINE ONLINE rac2
ora.LISTENER.lsnr
ONLINE ONLINE rac1
ONLINE ONLINE rac2
ora.NEW_FRA.dg
OFFLINE OFFLINE rac1
OFFLINE OFFLINE rac2
ora.OCRVOT.dg
ONLINE ONLINE rac1
ONLINE ONLINE rac2
ora.acfs.acfsvol.acfs
ONLINE ONLINE rac1 mounted on /acfs
ONLINE ONLINE rac2 mounted on /acfs
ora.asm
ONLINE ONLINE rac1 Started
ONLINE ONLINE rac2 Started
ora.gsd
OFFLINE OFFLINE rac1
OFFLINE OFFLINE rac2
ora.net1.network
ONLINE ONLINE rac1
ONLINE ONLINE rac2
ora.ons
ONLINE ONLINE rac1
ONLINE ONLINE rac2
ora.registry.acfs
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.ora11g.db
1 OFFLINE OFFLINE Instance Shutdown <<<<
2 OFFLINE OFFLINE Instance Shutdown <<<<
ora.prydb.db
1 OFFLINE OFFLINE Instance Shutdown
ora.rac1.vip
1 ONLINE ONLINE rac1
ora.rac2.vip
1 ONLINE ONLINE rac2
ora.scan1.vip
1 ONLINE ONLINE rac1
[oracle@rac1 ~]$
切到oracle用户
[oracle@rac1 ~]$ srvctl remove instance -d ora11g -i ora11g1
Remove instance from the database ora11g? (y/[n]) y
[oracle@rac1 ~]$
[oracle@rac1 ~]$ srvctl remove instance -d ora11g -i ora11g2
Remove instance from the database ora11g? (y/[n]) y
[oracle@rac1 ~]$
[oracle@rac1 ~]$ srvctl remove database -d ora11g
Remove the database ora11g? (y/[n]) y
[oracle@rac1 ~]$
切到grid用户
[oracle@rac1 ~]$ crsctl stat res -t
--------------------------------------------------------------------------------
NAME TARGET STATE SERVER STATE_DETAILS
--------------------------------------------------------------------------------
Local Resources
--------------------------------------------------------------------------------
ora.ACFS.dg
ONLINE ONLINE rac1
ONLINE ONLINE rac2
ora.DATA.dg
ONLINE ONLINE rac1
ONLINE ONLINE rac2
ora.FRA.dg
ONLINE ONLINE rac1
ONLINE ONLINE rac2
ora.LISTENER.lsnr
ONLINE ONLINE rac1
ONLINE ONLINE rac2
ora.NEW_FRA.dg
OFFLINE OFFLINE rac1
OFFLINE OFFLINE rac2
ora.OCRVOT.dg
ONLINE ONLINE rac1
ONLINE ONLINE rac2
ora.acfs.acfsvol.acfs
ONLINE ONLINE rac1 mounted on /acfs
ONLINE ONLINE rac2 mounted on /acfs
ora.asm
ONLINE ONLINE rac1 Started
ONLINE ONLINE rac2 Started
ora.gsd
OFFLINE OFFLINE rac1
OFFLINE OFFLINE rac2
ora.net1.network
ONLINE ONLINE rac1
ONLINE ONLINE rac2
ora.ons
ONLINE ONLINE rac1
ONLINE ONLINE rac2
ora.registry.acfs
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.prydb.db
1 OFFLINE OFFLINE Instance Shutdown
ora.rac1.vip
1 ONLINE ONLINE rac1
ora.rac2.vip
1 ONLINE ONLINE rac2
ora.scan1.vip
1 ONLINE ONLINE rac1
[oracle@rac1 ~]$
(4)修改pfile中关于cluster相关的内容
[oracle@rac1 tmp]$ cat initora11g.ora.bak
ora11g2.__db_cache_size=394264576
ora11g1.__db_cache_size=385875968
ora11g2.__java_pool_size=4194304
ora11g1.__java_pool_size=4194304
ora11g2.__large_pool_size=8388608
ora11g1.__large_pool_size=8388608
ora11g1.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment
ora11g2.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment
ora11g2.__pga_aggregate_target=432013312
ora11g1.__pga_aggregate_target=444596224
ora11g2.__sga_target=641728512
ora11g1.__sga_target=629145600
ora11g2.__shared_io_pool_size=0
ora11g1.__shared_io_pool_size=0
ora11g2.__shared_pool_size=222298112
ora11g1.__shared_pool_size=218103808
ora11g2.__streams_pool_size=0
ora11g1.__streams_pool_size=0
*.audit_file_dest='/u01/app/oracle/admin/ora11g/adump'
*.audit_trail='db'
*.cluster_database=true <<<<
*.compatible='11.2.0.4.0'
*.control_files='+DATA/ora11g/controlfile/current.257.863382281','+FRA/ora11g/controlfile/current.261.863382289'
*.db_block_size=8192
*.db_create_file_dest='+DATA'
*.db_domain=''
*.db_name='ora11g'
*.db_recovery_file_dest='+FRA'
*.db_recovery_file_dest_size=3145728000
*.db_unique_name='ora11g'
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=ora11gXDB)'
*.event='19823 trace name context forever,level 50'
*.fal_client='ora11g'
*.fal_server='dgora11g'
ora11g1.instance_number=1 <<<<<
ora11g2.instance_number=2 <<<<<
ora11g1.local_listener='(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.56.113)(PORT=1521))))' <<<<
ora11g2.local_listener='(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.56.114)(PORT=1521))))' <<<<
*.log_archive_config='dg_config=(ora11g,dgora11g)'
*.log_archive_dest_2='service=dgora11g reopen=120 lgwr async valid_for=(online_logfiles,primary_role) db_unique_name=dgora11g'
*.log_archive_dest_state_2='ENABLE'
*.memory_target=1073741824
*.open_cursors=300
ora11g2.parallel_max_servers=80 <<<<
*.processes=150
ora11g1.processes=200 <<<<
ora11g2.processes=200 <<<<
*.remote_listener='rac-scan:1521'