一聚教程网:一个值得你收藏的教程网站

热门教程

oralce中RAC转成单实例

时间:2022-06-29 09:32:43 编辑:袖梨 来源:一聚教程网

客户有个需求,需要将在一个包含多个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'                                             

热门栏目