客户有个需求，需要将在一个包含多个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' <<<<
ora11g1.remote_listener='(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.56.114)(PORT=1521))))' <<<<
ora11g2.remote_listener='(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.56.113)(PORT=1521))))' <<<<
*.remote_login_passwordfile='exclusive'
*.sec_case_sensitive_logon=FALSE
*.standby_file_management='auto'
ora11g2.thread=2 <<<<
ora11g1.thread=1 <<<<
ora11g1.undo_tablespace='UNDOTBS1'
ora11g2.undo_tablespace='UNDOTBS2' <<<<
[oracle@rac1 tmp]$
修改后：
[oracle@rac1 tmp]$ cat initora11g.ora.bak
*.audit_file_dest='/u01/app/oracle/admin/ora11g/adump'
*.audit_trail='db'
*.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'
*.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
*.processes=150
*.remote_login_passwordfile='exclusive'
*.sec_case_sensitive_logon=FALSE
*.standby_file_management='auto'
ora11g1.undo_tablespace='UNDO
（5）启动单实例，并修改redo和undo
SQL> startup pfile='/tmp/initora11g.ora.bak'
ORACLE instance started.
Total System Global Area 1068937216 bytes
Fixed Size 2260088 bytes
Variable Size 671089544 bytes
Database Buffers 390070272 bytes
Redo Buffers 5517312 bytes
Database mounted.
Database opened.
SQL>
SQL>
SQL>
SQL>
SQL>
SQL> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME
---------- ---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- --------- ------------ ---------
1 1 593 52428800 512 2 YES ACTIVE 4025536 17-AUG-16 4025592 17-AUG-16
2 1 594 52428800 512 2 NO CURRENT 4025592 17-AUG-16 2.8147E+14
3 2 523 52428800 512 2 YES INACTIVE 4025588 17-AUG-16 4025779 17-AUG-16
4 2 522 52428800 512 2 YES INACTIVE 4022067 17-AUG-16 4025588 17-AUG-16
SQL>
SQL>
SQL>
SQL> alter system checkpoint;
System altered.
SQL> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME
---------- ---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- --------- ------------ ---------
1 1 593 52428800 512 2 YES INACTIVE 4025536 17-AUG-16 4025592 17-AUG-16
2 1 594 52428800 512 2 NO CURRENT 4025592 17-AUG-16 2.8147E+14
3 2 523 52428800 512 2 YES INACTIVE 4025588 17-AUG-16 4025779 17-AUG-16
4 2 522 52428800 512 2 YES INACTIVE 4022067 17-AUG-16 4025588 17-AUG-16
SQL> alter system switch logfile;
System altered.
SQL> --检查确实是thread 1发生了切换
SQL> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME
---------- ---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- --------- ------------ ---------
1 1 595 52428800 512 2 NO CURRENT 4025868 17-AUG-16 2.8147E+14
2 1 594 52428800 512 2 YES INACTIVE 4025592 17-AUG-16 4025868 17-AUG-16
3 2 523 52428800 512 2 YES INACTIVE 4025588 17-AUG-16 4025779 17-AUG-16
4 2 522 52428800 512 2 YES INACTIVE 4022067 17-AUG-16 4025588 17-AUG-16
SQL>
SQL> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME
---------- ---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- --------- ------------ ---------
1 1 595 52428800 512 2 NO CURRENT 4025868 17-AUG-16 2.8147E+14
2 1 594 52428800 512 2 YES INACTIVE 4025592 17-AUG-16 4025868 17-AUG-16
3 2 523 52428800 512 2 YES INACTIVE 4025588 17-AUG-16 4025779 17-AUG-16
4 2 522 52428800 512 2 YES INACTIVE 4022067 17-AUG-16 4025588 17-AUG-16
SQL>
SQL>
SQL> --先disable thread 2
SQL> alter database disable thread 2;
Database altered.
SQL> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME
---------- ---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- --------- ------------ ---------
1 1 595 52428800 512 2 NO CURRENT 4025868 17-AUG-16 2.8147E+14
2 1 594 52428800 512 2 YES INACTIVE 4025592 17-AUG-16 4025868 17-AUG-16
3 2 523 52428800 512 2 YES INACTIVE 4025588 17-AUG-16 4025779 17-AUG-16
4 2 522 52428800 512 2 YES INACTIVE 4022067 17-AUG-16 4025588 17-AUG-16
SQL> --再删除thread 2的redo group：
SQL> alter database drop logfile group 3;
Database altered.
SQL> alter database drop logfile group 4;
Database altered.
SQL> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME
---------- ---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- --------- ------------ ---------
1 1 595 52428800 512 2 NO CURRENT 4025868 17-AUG-16 2.8147E+14
2 1 594 52428800 512 2 YES INACTIVE 4025592 17-AUG-16 4025868 17-AUG-16
SQL>
SQL> select tablespace_name from dba_tablespaces;
TABLESPACE_NAME
------------------------------
SYSTEM
SYSAUX
UNDOTBS1
TEMP
UNDOTBS2
USERS
TEST_TBS
7 rows selected.
SQL>
SQL>
SQL>
SQL> show parameter undo
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
undo_management string AUTO
undo_retention integer 900
undo_tablespace string UNDOTBS1
SQL>
SQL>
SQL>
SQL>
SQL> drop tablespace UNDOTBS2 including contents and datafiles;
Tablespace dropped.
SQL>
（6）将spfile还原回到asm上。
SQL> create spfile='+DATA/ora11g/spfileora11g.ora' from pfile='/tmp/initora11g.ora.bak';
File created.
SQL>
[oracle@rac1 dbs]$ pwd
/u01/app/oracle/product/11.2.0.3/db_1/dbs
[oracle@rac1 dbs]$ ls -l init*
-rw-r--r--. 1 oracle oinstall 2851 May 15 2009 init.ora
-rw-r-----. 1 oracle oinstall 39 Nov 11 2014 initora11g1.ora <<<
[oracle@rac1 dbs]$
[oracle@rac1 dbs]$ mv initora11g1.ora initora11g.ora
[oracle@rac1 dbs]$
[oracle@rac1 ~]$ env |grep SID
ORACLE_SID=ora11g1
[oracle@rac1 ~]$
修改profile，将ORACLE_SID从ora11g1改成ora11g。
[oracle@rac1 ~]$ env |grep SID
ORACLE_SID=ora11g
[oracle@rac1 ~]$
[oracle@rac1 ~]$ sqlplus "/ as sysdba"
SQL*Plus: Release 11.2.0.4.0 Production on Wed Aug 17 11:57:07 2016
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup
ORACLE instance started.
Total System Global Area 246386688 bytes
Fixed Size 2252096 bytes
Variable Size 188744384 bytes
Database Buffers 50331648 bytes
Redo Buffers 5058560 bytes
Database mounted.
Database opened.
SQL>