Oracle单实例数据库迁移到Oracle RAC 环境之(2)--实施篇
系统环境：
操作系统：RedHat EL55
Oracle ： Oracle 11.2.0.1.0
集群软件：Oracle GI 11.2.0.1.0
本案例采用的是基于DataGuard的迁移方式
主库（bjdb）：
1、修改初始化参数文件
[oracle@bjsrv dbs]$ cat initcuug.ora
*.audit_file_dest='/u01/app/oracle/admin/cuug/adump'
*.audit_trail='db'
*.compatible='11.2.0.0.0'
*.control_files='/u01/app/oracle/oradata/cuug/control01.ctl','/u01/app/oracle/oradata/cuug/control02.ctl'
*.db_block_size=8192
*.db_domain=''
*.db_name='cuug'
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=cuugXDB)'
*.log_archive_format='arch_%t_%s_%r.log'
*.memory_target=419430400
*.open_cursors=300
*.processes=150
*.undo_tablespace='UNDOTBS1'
DB_UNIQUE_NAME=bjdb
LOG_ARCHIVE_CONFIG='DG_CONFIG=(bjdb,stddb)'
LOG_ARCHIVE_DEST_1='LOCATION=/dsk3/arch_cuug VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=bjdb'
LOG_ARCHIVE_DEST_2='SERVICE=stddb LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=stddb'
LOG_ARCHIVE_DEST_STATE_1=ENABLE
LOG_ARCHIVE_DEST_STATE_2=ENABLE
REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE
LOG_ARCHIVE_MAX_PROCESSES=3
DB_FILE_NAME_CONVERT='+dg1/stddb/datafile/','/u01/app/oracle/oradata/cuug/','+dg1/stddb/tempfile/','/u01/app/oracle/oradata/cuug/'
LOG_FILE_NAME_CONVERT='+dg1/stddb/onlinelog/','/dsk1/oradata/cuug','+rcy1/stddb/onlinelog/','/dsk2/oradata/cuug'
STANDBY_FILE_MANAGEMENT=AUTO
2、通过新的初始化参数文件启动Instance
16:54:57 SYS@ cuug>create spfile from pfile；
16:55:57 SYS@ cuug>show parameter name
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_file_name_convert string +dg1/stddb/datafile, /u01/app/oracle/or adata/cuug/,+dg1/stddb/tempfile,/u01/app/oracle/oradata/cuug
db_name string cuug
db_unique_name string bjdb
global_names boolean FALSE
instance_name string cuug
log_file_name_convert string +dg1/stddb/onlinelog, /dsk1/oradata/cuug
, +rcy1/stddb/onlinelog, /dsk2/oradata/c
uug
service_names string bjdb
3、对数据库进行冷备份并建立standby controlfile
数据库在mount状态下，进行冷备：
[oracle@bjsrv admin]$ rman target /
Recovery Manager: Release 11.2.0.1.0 - Production on Wed May 21 16:58:25 2014
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
connected to target database: CUUG (DBID=1329392875, not open)
RMAN> backup database format '/dsk3/%d_%s.bak';
生成standby controlfile：
RMAN> backup current controlfile for standby format '/dsk3/std_control01.ctl';
4、启动NFS共享将存储库备份的文件系统共享
[root@bjsrv ~]# cat /etc/exports
/dsk3 *(rw,async,nohide,no_subtree_check)
[root@bjsrv ~]# exportfs -av
exporting *:/dsk3
5、拷贝实例初始化参数文件和口令文件到RAC主机
[oracle@bjsrv dbs]$ scp orapwcuug node1:$ORACLE_HOME/dbs/orapwstddb1
[oracle@bjsrv dbs]$ scp orapwcuug node2:$ORACLE_HOME/dbs/orapwstddb2
[oracle@bjsrv dbs]$ scp initcuug.ora node1:~/initstddb1.ora
6、配置listener和tnsnames
listener 采用系统默认listener即可
[oracle@bjsrv admin]$ cat tnsnames.ora
# tnsnames.ora Network Configuration File: /u01/app/oracle/product/11.2.0/db_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.
STDDB =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = node1)(PORT = 1521))
(ADDRESS = (PROTOCOL = TCP)(HOST = node2)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = stddb)
)
)
BJDB =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = bjsrv)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = bjdb)
)
)
备库（stddb）：
1、修改初始化参数文件
[oracle@node1 ~]$ cat initstddb1.ora
*.audit_file_dest='/u01/app/oracle/admin/stddb/adump'
*.audit_trail='db'
*.compatible='11.2.0.0.0'
*.control_files='+dg1/stddb/controlfile/std_control01.ctl'
*.db_block_size=8192
*.db_domain=''
*.db_name='cuug'
*.diagnostic_dest='/u01/app/oracle'
*.log_archive_format='arch_%t_%s_%r.log'
*.memory_target=419430400
*.open_cursors=300
*.processes=150
stddb1.instance_number=1
stddb2.instance_number=2
stddb2.thread=2
stddb1.thread=1
stddb1.undo_tablespace='UNDOTBS1'
stddb2.undo_tablespace='UNDOTBS2'
*.cluster_database=true
DB_UNIQUE_NAME=stddb
LOG_ARCHIVE_CONFIG='DG_CONFIG=(bjdb,stddb)'
LOG_ARCHIVE_DEST_1='LOCATION=+rcy1 VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=stddb'
LOG_ARCHIVE_DEST_2='SERVICE=bjdb LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=bjdb'
LOG_ARCHIVE_DEST_STATE_1=ENABLE
LOG_ARCHIVE_DEST_STATE_2=ENABLE
REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE
LOG_ARCHIVE_MAX_PROCESSES=3
FAL_SERVER=bjdb
DB_FILE_NAME_CONVERT='/u01/app/oracle/oradata/cuug/','+dg1/stddb/datafile/','/u01/app/oracle/oradata/cuug/','+dg1/stddb/tempfile'
LOG_FILE_NAME_CONVERT='/dsk1/oradata/cuug','+dg1/stddb/onlinelog','/dsk2/oradata/cuug','+rcy1/stddb/onlinelog'
STANDBY_FILE_MANAGEMENT=AUTO
2、建立相关目录
[oracle@node1 ~]$ mkdir -p /u01/app/oracle/admin/stddb/adump
[oracle@node2 dbs]$ mkdir -p /u01/app/oracle/admin/stddb/adump
3、通过pfile启动Instance到nomount
SQL> startup force nomount pfile='/home/oracle/initstddb1.ora'
ORACLE instance started.
Total System Global Area 418484224 bytes
Fixed Size 1336932 bytes
Variable Size 260049308 bytes
Database Buffers 150994944 bytes
Redo Buffers 6103040 bytes
SQL> show parameter name
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_file_name_convert string /u01/app/oracle/oradata/cuug/,
+dg1/stddb/datafile,/u01/app/oracle/oradata/cuug/,
+dg1/stddb/tempfile
db_name string cuug
db_unique_name string stddb
global_names boolean FALSE
instance_name string stddb1
log_file_name_convert string /dsk1/oradata/cuug, +dg1/stddb/onlinelog
, /dsk2/oradata/cuug, +rcy1/st
ddb/onlinelog
service_names string stddb
4、创建spfile和pfile 文件
SQL> create spfile='+dg1/stddb/spfilestddb.ora' from pfile='/home/oracle/initstddb1.ora';
File created.
[oracle@node1 dbs]$ cat initstddb1.ora
spfile='+dg1/stddb/spfilestddb.ora'
[oracle@node2 dbs]$ cat initstddb2.ora
spfile='+dg1/stddb/spfilestddb.ora'
5、配置tnsnames
[oracle@node1 admin]$ cat tnsnames.ora
# tnsnames.ora Network Configuration File: /u01/app/oracle/product/11.2.0/db_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.
STDDB =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = node1)(PORT = 1521))
(ADDRESS = (PROTOCOL = TCP)(HOST = node2)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = stddb)
)
)
BJDB =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = bjsrv)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = bjdb)
)
)
6、Mount主库主机共享到本地(注意：本地目录需和共享目录相同的路径和名称）
[root@node1 ~]# mount -o rw,bg,hard,nointr,rsize=32768,wsize=32768,tcp,actimeo=0,vers=3,timeo=600 bjsrv:/dsk3 /dsk3
7、在备库做数据库恢复（node1）
首先restore controlfile：
[oracle@node1 admin]$ rman target /
Recovery Manager: Release 11.2.0.1.0 - Production on Wed May 21 17:31:06 2014
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
connected to target database: CUUG (not mounted)
RMAN> restore standby controlfile from '/dsk3/std_control01.ctl';
Starting restore at 21-MAY-14
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=1 device type=DISK
channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
output file name=+DG1/stddb/controlfile/std_control01.ctl
Finished restore at 21-MAY-14
RMAN> alter database mount;
database mounted
released channel: ORA_DISK_1
再restore database：
RMAN> restore database;
Starting restore at 21-MAY-14
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=36 device type=DISK
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00001 to +DG1/stddb/datafile/system01.dbf
channel ORA_DISK_1: restoring datafile 00002 to +DG1/stddb/datafile/sysaux01.dbf
channel ORA_DISK_1: restoring datafile 00003 to +DG1/stddb/datafile/undotbs01.dbf
channel ORA_DISK_1: restoring datafile 00004 to +DG1/stddb/datafile/users01.dbf
channel ORA_DISK_1: restoring datafile 00005 to +DG1/stddb/datafile/example01.dbf
channel ORA_DISK_1: reading from backup piece /dsk3/CUUG_1.bak
channel ORA_DISK_1: piece handle=/dsk3/CUUG_1.bak tag=TAG20140521T165858
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:01:35
Finished restore at 21-MAY-14
8、在mount状态下做Database Recovery
SQL> recover managed standby database disconnect from session;
Media recovery complete.
9、查看主库传送的归档日志
SQL> select name from v$archived_log where name is not null;
NAME
--------------------------------------------------------------------------------
+RCY1/stddb/archivelog/2014_05_21/thread_1_seq_9.265.848168007
+RCY1/stddb/archivelog/2014_05_21/thread_1_seq_8.264.848168007
+RCY1/stddb/archivelog/2014_05_21/thread_1_seq_11.267.848168007
......
10、打开数据库
SQL> recover managed standby database cancel;
Media recovery complete.
SQL> alter database open;
Database altered.
查看据库角色：
SQL> select name,dbid,database_role,protection_mode,switchover_status from v$database;
NAME DBID DATABASE_ROLE PROTECTION_MODE SWITCHOVER_STATUS
--------- ---------- ---------------- -------------------- --------------------
CUUG 1329392875 PHYSICAL STANDBY MAXIMUM PERFORMANCE NOT ALLOWED
查看数据是否和主库同步：
SQL> select count(*) from scott.dept;
COUNT(*)
----------
4
SQL> select count(*) from scott.emp1;
COUNT(*)
----------
14
在主库查看数据库角色：
8:18:00 SYS@ cuug>select name,dbid,database_role,protection_mode,switchover_status from v$database;
NAME DBID DATABASE_ROLE PROTECTION_MODE SWITCHOVER_STATUS
--------- ---------- ---------------- -------------------- --------------------
CUUG 1329392875 PRIMARY MAXIMUM PERFORMANCE TO STANDBY
Elapsed: 00:00:00.04
@至此，物理的DataGuard构建成功，下一步将进行主备库switchover，将RAC database切换成主库，完成数据迁移。
