Migrate database from single instance to Oracle RAC

Migrate database from single instance to Oracle RAC

# Preparation before restore
# Backup database
# check listener
# check ASM disk group free space is sufficient.
# the following steps was completed sucessful on Oracle 10g RAC + Suse Linux 10.

Synopsis:
source DB : GOBO1 on file system
Target DB : GOBO1 on RAC +ASM
Target Instance: GOBO1A, GOBO1B
Target node: bo2dbp, bo2dbs
Source Env: Oracle 10g(10.2.0.3) + Suse 10
Target Env: Oracle 10g(10.2.0.3) RAC + ASM + RAW + Suse 10
      ORA_CRS_HOME=/u01/oracle/crs
      ORA_ASM_HOME=/u01/oracle/asm
      ORACLE_BASE=/u01/oracle
      ORACLE_HOME=/u01/oracle/db

Step 1
# restore spfile
  export ORACLE_SID=GOBO1A
  rman target /
  startup nomount;
  restore spfile to pfile '/u01/oracle/db/dbs/initGOBO1A.ora'
  from '<dir>';
  shutdown immediate;

Step 2
# create directory for instance on local file system.(two nodes)
  export ORACLE_SID=GOBO1
  mkdir -p /u01/oracle/admin/${ORACLE_SID}/{bdump,cdump,udump,adump}

Step 3
#Modify pfile
#Remove original path and or change them to new path on target server.
#Add new item for cluster 
    cp initGOBO1A.ora initGOBO1A.ora.bak
    vi initGOBO1A.ora
  GOBO1.__db_cache_size=230686720
  GOBO1.__java_pool_size=4194304
  GOBO1.__large_pool_size=4194304
  GOBO1.__shared_pool_size=289406976
  GOBO1.__streams_pool_size=0
  *.compatible='10.2.0.3.0'
  
  #*.control_files='/u02/database/GOBO1/controlf/cntl1GOBO1.ctl','/u02/database/GOBO1/controlf/cntl2GOBO1.ctl',
  #'/u02/database/GOBO1/controlf/cntl3GOBO1.ctl'
  #*.core_dump_dest='/u02/database/GOBO1/cdump'
  
  *.db_block_size=8192
  *.db_domain=''
  *.db_file_multiblock_read_count=16
  *.DB_FLASHBACK_RETENTION_TARGET=2880
  *.db_name='GOBO1'
  
  #*.db_recovery_file_dest='/u02/database/GOBO1/flash_recovery_area'
  
  *.db_recovery_file_dest_size=4G
  
  *.dispatchers='(PROTOCOL=TCP) (SERVICE=GOBO1XDB)'
  *.job_queue_processes=10
  
  #*.LOG_ARCHIVE_DEST_1='LOCATION=/u02/database/GOBO1/archive/'
  
  *.log_archive_format='arch_%r_%t_%s.arc'
  *.open_cursors=300
  *.pga_aggregate_target=199229440
  *.processes=1000
  *.remote_login_passwordfile='EXCLUSIVE'
  *.sga_target=512M
  *.shared_pool_size=256M
  
  #*.undo_management='AUTO'
  #*.undo_tablespace='UNDOTBS1'
  #*.user_dump_dest='/u02/database/GOBO1/udump'
  #*.UTL_FILE_DIR='/u02/database/GOBO1/udump'
  #*.background_dump_dest='/u02/database/GOBO1/bdump'
  
  #Added new dump directory
  *.core_dump_dest='/u01/oracle/admin/GOBO1/cdump'
  *.user_dump_dest='/u01/oracle/admin/GOBO1/udump'
  *.UTL_FILE_DIR='/u01/oracle/admin/GOBO1/udump'
  *.background_dump_dest='/u01/oracle/admin/GOBO1/bdump'
  *.audit_file_dest='/u01/oracle/admin/GOBO1/adump'
  
  *.cluster_database = TRUE 
  *.cluster_database_instances = 2 
  *.undo_management='AUTO' 
  *.control_files='+DG1/GOBO1/controlf/cntl1GOBO1.ctl','+DG1/GOBO1/controlf/cntl2GOBO1.ctl'
  
  GOBO1A.undo_tablespace='UNDOTBS1'
  GOBO1A.instance_name=GOBO1A 
  GOBO1A.instance_number=1 
  GOBO1A.thread=1 
  #GOBO1A.local_listener=<LISTENERNAME>_<HOSTNAME1>
  
  GOBO1B.undo_tablespace='UNDOTBS2' 
  GOBO1B.instance_name=GOBO1B
  GOBO1B.instance_number=2 
  #GOBO1B.thread=2 
  #GOBO1B.local_listener=<LISTENERNAME>_<HOSTNAME2>
  
  *.db_create_file_dest='+DG2'
  *.db_recovery_file_dest='+REV'
  *.log_archive_dest_1='LOCATION=+REV/GOBO1/archivelog'

Step 4
#Create directory on ASM for new database
#Currently, seperate different file type to different disk group.

    export ORACLE_SID=+ASM1
  asmcmd
  cd +DG1
  mkdir GOBO1
  cd GOBO1
  mkdir controlf parameterf onlinelog
  
  cd +DG2
  mkdir GOBO1
  cd GOBO1
  mkdir datafile
  
Step 5
#Create password on 2 nodes.
  $ORACLE_HOME/dbs/orapwd file=?/dbs/orapwGOBO1A password=oracle entries=10
  
  $ORACLE_HOME/dbs/orapwd file=?/dbs/orapwGOBO1B password=oracle entries=10
    
Step 6
#Generate spfile from pfile
  export ORACLE_SID=GOBO1A
  sqlplus / as sysdba
  startup nomount;
  create spfile='+DG1/GOBO1/parameterf/spfileGOBO1.ora' from pfile='/u01/oracle/db/dbs/initGOBO1A.ora' 
  shutdown immediate;
  
  echo "SPFILE='+DG1/GOBO1/parameterf/spfileGOBO1.ora'" >/u01/oracle/db/dbs/initGOBO1A.ora  # on node A ( two nodes)
  echo "SPFILE='+DG1/GOBO1/parameterf/spfileGOBO1.ora'" >/u01/oracle/db/dbs/initGOBO1B.ora  # on node B ( two nodes)
  
  startup nomount;
  show parameter spfile;

Step 7
#Restore controlfile from autobackup
  export ORACLE_SID=GOBO1A
  $ORACLE_HOME/bin/rman target /
  restore controlfile from '<dir>';
  sql 'alter database mount';  
  
Step 8
#Check datafile path and convert to ASM
#Restore Database
  sys@GOBO1> select file_id,file_name from dba_data_files order by 1;
  
     FILE_ID FILE_NAME
  ---------- ------------------------------------------------------------
           1 /u02/database/GOBO1/oradata/sysGOBO1.dbf
           2 /u02/database/GOBO1/undo/undotbsGOBO1.dbf
           3 /u02/database/GOBO1/oradata/sysauxGOBO1.dbf
           4 /u02/database/GOBO1/undo/undotbsGOBO12.dbf
           5 /u02/database/GOBO1/oradata/GOBO1_account_tbl.dbf
           6 /u02/database/GOBO1/oradata/GOBO1_stock_tbl.dbf
           7 /u02/database/GOBO1/oradata/GOBO1_stock_l_tbl.dbf
           8 /u02/database/GOBO1/oradata/GOBO1_tx_tbl.dbf
           9 /u02/database/GOBO1/oradata/GOBO1_users_tbl.dbf
          10 /u02/database/GOBO1/oradata/GOBO1_account_idx.dbf
          11 /u02/database/GOBO1/oradata/GOBO1_stock_idx.dbf
          12 /u02/database/GOBO1/oradata/GOBO1_stock_l_idx.dbf
          13 /u02/database/GOBO1/oradata/GOBO1_tx_idx.dbf
          14 /u02/database/GOBO1/oradata/GOBO1_users_idx.dbf
          15 /u02/database/GOBO1/oradata/GOBO1_IES_IDX.DBF
          16 /u02/database/GOBO1/oradata/GOBO1_IES_TBL.DBF
          17 /u02/database/GOBO1/oradata/GOBO1_import_idx.dbf
          18 /u02/database/GOBO1/oradata/GOBO1_import_tbl.dbf
          19 /u02/database/GOBO1/oradata/GOBO1_fix_tx_idx.dbf
          20 /u02/database/GOBO1/oradata/GOBO1_fix_tx_tbl.dbf
          21 /u02/database/GOBO1/oradata/GOBO1_fix_users_idx.dbf
          22 /u02/database/GOBO1/oradata/GOBO1_fix_users_tbl.dbf
          23 /u02/database/GOBO1/oradata/xxxx_ipo_tbl.dbf
          24 /u02/database/GOBO1/oradata/xxxx_ipo_idx.dbf
  catalog start with '/install_source/rman_bak';    
  run { 
  set newname for datafile 1 to   '+DG2/GOBO1/datafile/sysGOBO1.dbf';             
  set newname for datafile 2 to   '+DG2/GOBO1/datafile/undotbsGOBO1.dbf';         
  set newname for datafile 3 to   '+DG2/GOBO1/datafile/sysauxGOBO1.dbf';          
  set newname for datafile 4 to   '+DG2/GOBO1/datafile/undotbsGOBO12.dbf';        
  set newname for datafile 5 to   '+DG2/GOBO1/datafile/GOBO1_account_tbl.dbf';    
  set newname for datafile 6 to   '+DG2/GOBO1/datafile/GOBO1_stock_tbl.dbf';      
  set newname for datafile 7 to   '+DG2/GOBO1/datafile/GOBO1_stock_l_tbl.dbf';    
  set newname for datafile 8 to   '+DG2/GOBO1/datafile/GOBO1_tx_tbl.dbf';         
  set newname for datafile 9 to   '+DG2/GOBO1/datafile/GOBO1_users_tbl.dbf';      
  set newname for datafile 10 to  '+DG2/GOBO1/datafile/GOBO1_account_idx.dbf';    
  set newname for datafile 11 to  '+DG2/GOBO1/datafile/GOBO1_stock_idx.dbf';      
  set newname for datafile 12 to  '+DG2/GOBO1/datafile/GOBO1_stock_l_idx.dbf';    
  set newname for datafile 13 to  '+DG2/GOBO1/datafile/GOBO1_tx_idx.dbf';         
  set newname for datafile 14 to  '+DG2/GOBO1/datafile/GOBO1_users_idx.dbf';      
  set newname for datafile 15 to  '+DG2/GOBO1/datafile/GOBO1_IES_IDX.DBF';        
  set newname for datafile 16 to  '+DG2/GOBO1/datafile/GOBO1_IES_TBL.DBF';        
  set newname for datafile 17 to  '+DG2/GOBO1/datafile/GOBO1_import_idx.dbf';     
  set newname for datafile 18 to  '+DG2/GOBO1/datafile/GOBO1_import_tbl.dbf';     
  set newname for datafile 19 to  '+DG2/GOBO1/datafile/GOBO1_fix_tx_idx.dbf';     
  set newname for datafile 20 to  '+DG2/GOBO1/datafile/GOBO1_fix_tx_tbl.dbf';     
  set newname for datafile 21 to  '+DG2/GOBO1/datafile/GOBO1_fix_users_idx.dbf';  
  set newname for datafile 22 to '+DG2/GOBO1/datafile/GOBO1_fix_users_tbl.dbf';   
  set newname for datafile 23 to  '+DG2/GOBO1/datafile/xxxx_ipo_tbl.dbf';         
  set newname for datafile 24 to  '+DG2/GOBO1/datafile/xxxx_ipo_idx.dbf';         
  restore database;   
  switch datafile all;
  switch tempfile all;
  } 

Step 9
#Recover database
  recover database;

Step 10
#Handle online redo log
  sys@GOBO1> select * from v$logfile;
  
      GROUP# STATUS  TYPE    MEMBER                                                       IS_
  ---------- ------- ------- ------------------------------------------------------------ ---
           1         ONLINE  /u02/database/GOBO1/redolog/log1aGOBO1.log                   NO
           1         ONLINE  /u02/database/GOBO1/redolog/log1bGOBO1.log                   NO
           3         ONLINE  /u02/database/GOBO1/redolog/log3aGOBO1.log                   NO
           3         ONLINE  /u02/database/GOBO1/redolog/log3bGOBO1.log                   NO
           2         ONLINE  /u02/database/GOBO1/redolog/log2aGOBO1.log                   NO
           2         ONLINE  /u02/database/GOBO1/redolog/log2bGOBO1.log                   NO    
  
  alter database rename file '/u02/database/GOBO1/redolog/log1aGOBO1.log' to '+DG1/GOBO1/onlinelog/log1aGOBO1.log'; 
  alter database rename file '/u02/database/GOBO1/redolog/log1bGOBO1.log' to '+DG1/GOBO1/onlinelog/log1bGOBO1.log'; 
  alter database rename file '/u02/database/GOBO1/redolog/log3aGOBO1.log' to '+DG1/GOBO1/onlinelog/log3aGOBO1.log'; 
  alter database rename file '/u02/database/GOBO1/redolog/log3bGOBO1.log' to '+DG1/GOBO1/onlinelog/log3bGOBO1.log'; 
  alter database rename file '/u02/database/GOBO1/redolog/log2aGOBO1.log' to '+DG1/GOBO1/onlinelog/log2aGOBO1.log'; 
  alter database rename file '/u02/database/GOBO1/redolog/log2bGOBO1.log' to '+DG1/GOBO1/onlinelog/log2bGOBO1.log'; 
  
  #Add online log for instance 2
  alter database add logfile thread 2 group 4 
    ('+DG1/GOBO1/onlinelog/log4aGOBO1.log','+DG1/GOBO1/onlinelog/log4bGOBO1.log') size 20M; 
  alter database add logfile thread 2 group 5 
    ('+DG1/GOBO1/onlinelog/log5aGOBO1.log','+DG1/GOBO1/onlinelog/log5bGOBO1.log') size 20M;
  alter database add logfile thread 2 group 6
    ('+DG1/GOBO1/onlinelog/log6aGOBO1.log','+DG1/GOBO1/onlinelog/log6bGOBO1.log') size 20M;
  

Step 11
#open the database with resetlogs
  
  alter database open resetlogs; --> if failed, recover database using backup controlfile until cancel by sqlplus;

Step 12
#modify parameter 
  alter system set thread=1 scope=spfile sid='GOBO1A';
  alter system set thread=2 scope=spfile sid='GOBO1B';
  alter database enable thread 2;    

Step 13
#add undo tablspace for instance 2
  alter system set undo_tablespace='UNDOTBS2' scope=spfile sid='GOBO1B';  
  -->if current db has no undotbs2,create it firstly as follows
  create undo tablespace UNDOTBS2 datafile '+DG2/GOBO1/datafile/undotbs02.dbf' size 500m autoextnd on; 

Step 14
# add temporary tablespace and datafile 

  col file_name format a55
  select file_name,tablespace_name,bytes/1022/1024 from dba_temp_files;
  alter tablespace temp add tempfile '+DG2/GOBO1/datafile/temp.dbf' size 50m;
  alter tablespace goex_temp add tempfile '+DG2/GOBO1/datafile/goex_temp.dbf' size 50m;

Step 15
# check default temporary tablespace for all users. If that are different from orginal database, amend them.

  select username,default_tablespace,temporary_tablespace from dba_users; 

Step 16
#check parameter
  select * from v$option where parameter = 'Real Application Clusters';
  show parameter cluster;
  show parameter thread;
  show parameter instance_number;

Step 17
# restart instance 1
# check alert log file

Step 18
# start instance 2
# check alert log file

Step 19
# check all instance is fine.
  select instance_number,instance_name,host_name from gv$instance;

Step 20
#create cluster database specific views within the existing instance

  $ORACLE_HOME/rdbms/admin/catclust.sql
  
Step 21
# configure listener 
  by netca
  ps -ef | grep lsnr
  crs_stat -t #check listener
  
Step 22
#Add configuration to crs
  srvctl add database -d GOBO1 -o $ORACLE_HOME -p +DG1/GOBO1/parameterf/spfileGOBO1.ora 
  srvctl add instance -d GOBO1 -i GOBO1A -n bo2dbp
  srvctl add instance -d GOBO1 -i GOBO1B -n bo2dbs

  srvctl modify instance -d GOBO1 -i GOBO1A -s +ASM1
  srvctl modify instance -d GOBO1 -i GOBO1B -s +ASM2
  crs_stat -t

Step 23
#Restart database
#Author : Robinson
#Blog : http://blog.csdn.net/robinson_0612

  srvctl start database -d GOBO1  

更多参考：
 

有关Oracle 网络配置相关基础以及概念性的问题请参考：
        配置ORACLE 客户端连接到数据库
        配置非默认端口的动态服务注册
        配置sqlnet.ora限制IP访问Oracle
       Oracle 监听器日志配置与管理
       设置 Oracle 监听器密码(LISTENER)
       Oracle RAC 监听配置
       ORACLE RAC 下非缺省端口监听配置(listener.ora tnsnames.ora)

       Oracle RAC 客户端连接负载均衡(Load Balance)

       配置RAC负载均衡与故障转移

有关基于用户管理的备份和备份恢复的概念请参考

    Oracle 冷备份

    Oracle 热备份

    Oracle 备份恢复概念

    Oracle 实例恢复

    Oracle 基于用户管理恢复的处理

   SYSTEM 表空间管理及备份恢复

    SYSAUX表空间管理及恢复

   Oracle 基于备份控制文件的恢复(unsing backup controlfile)

有关RMAN的备份恢复与管理请参考

    RMAN 概述及其体系结构

    RMAN 配置、监控与管理

    RMAN 备份详解

    RMAN 还原与恢复

    RMAN catalog 的创建和使用

    基于catalog 创建RMAN存储脚本

    基于catalog 的RMAN 备份与恢复

    RMAN 备份路径困惑

有关ORACLE体系结构请参考

    Oracle 表空间与数据文件

    Oracle 密码文件

    Oracle 参数文件

    Oracle 联机重做日志文件(ONLINE LOG FILE)

    Oracle 控制文件(CONTROLFILE)

    Oracle 归档日志

    Oracle 回滚(ROLLBACK)和撤销(UNDO)

    Oracle 数据库实例启动关闭过程

    Oracle 10g SGA 的自动化管理

    Oracle 实例和Oracle数据库(Oracle体系结构)