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 systemTarget DB : GOBO1 on RAC +ASMTarget Instance: GOBO1A, GOBO1BTarget node: bo2dbp, bo2dbsSource Env: Oracle 10g(10.2.0.3) + Suse 10Target 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/dbStep 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 fileStep 18# start instance 2# check alert log fileStep 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 -tStep 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體繫結構)