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 複制