在前面的3個小節中，分别介紹了使用expdp，傳輸表空間以及rman的異機恢複等方式實作單執行個體資料庫向rac環境的遷移，本節中将介紹下使用physical dataguard實作這一過程，核心思想是建構基于單執行個體資料庫的實體備庫(rac環境)，而後進行主備庫的切換，盡可能的減少遷移資料的時間消耗和對生産環境的影響！但同前3種方式相比，遷移的難度也增加了不少！由于文章篇幅限制，本節示範将分為上下兩篇文章。本文主要參考下列的文章，感謝作者分享！
<a href="http://space.itpub.net/4227/viewspace-622322">http://space.itpub.net/4227/viewspace-622322</a>
一：環境介紹
primary資料庫(單執行個體環境)
IP:192.168.1.49/24
作業系統版本：rhel5.4 64位
資料庫版本：10.2.0.5
physical standby資料庫(rac環境，2個節點)
節點1 IP:192.168.1.41/24
節點2 IP:192.168.1.42/24
作業系統版本：centos4.8 64位
二：配置primary資料庫的tnsnames.ora檔案以及dataguard相關的設定，由于即将建構的備庫為rac環境，因而需要增加undotbs2表空間和thread 2日志組
[oracle@server49 ~]$ cd $ORACLE_HOME/network/admin
[oracle@server49 ~]$ cat tnsnames.ora
PRIMARY =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = server49.yang.com)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = primary.yang.com)
)
)
STANDBY =
(ADDRESS = (PROTOCOL = TCP)(HOST = rac1.yang.com)(PORT = 1521))
(SID = orcl1)
(SERVICE_NAME = standby.yang.com)
SQL> show parameter name;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_file_name_convert string
db_name string orcl
db_unique_name string primary
global_names boolean FALSE
instance_name string orcl
lock_name_space string
log_file_name_convert string
service_names string primary.yang.com
SQL> select name,open_mode,log_mode,force_logging from v$database;
NAME OPEN_MODE LOG_MODE FOR
--------- ---------- ------------ ---
ORCL READ WRITE ARCHIVELOG NO
SQL> alter database force logging;
Database altered.
SQL> select bytes/(1024*1024) MB,autoextensible from dba_data_files where tablespace_name='UNDOTBS1';
MB AUT
---------- ---
30 YES
SQL> create undo tablespace undotbs2 datafile '/u01/app/oracle/oradata/orcl/undotbs02.dbf' size 30M autoextend on maxsize 10G;
Tablespace created.
SQL> select bytes/(1024*1024) MB,autoextensible from dba_data_files where tablespace_name in ('UNDOTBS1','UNDOTBS2');
QL> select a.group#,a.member,b.bytes/(1024*1024) MB,b.thread# from v$logfile a,v$log b where a.group#=b.group#;
GROUP# MEMBER MB THREAD#
---------- ---------------------------------------- ---------- ----------
3 /u01/app/oracle/oradata/orcl/redo03.log 50 1
2 /u01/app/oracle/oradata/orcl/redo02.log 50 1
1 /u01/app/oracle/oradata/orcl/redo01.log 50 1
SQL> alter database add logfile thread 2 group 4 '/u01/app/oracle/oradata/orcl/redo04.log' size 50M;
SQL> alter database add logfile thread 2 group 5 '/u01/app/oracle/oradata/orcl/redo05.log' size 50M;
SQL> alter database add logfile thread 2 group 6 '/u01/app/oracle/oradata/orcl/redo06.log' size 50M;
SQL> select a.group#,a.member,b.bytes/(1024*1024) MB,b.thread# from v$logfile a,v$log b where a.group#=b.group#;
4 /u01/app/oracle/oradata/orcl/redo04.log 50 2
5 /u01/app/oracle/oradata/orcl/redo05.log 50 2
6 /u01/app/oracle/oradata/orcl/redo06.log 50 2
SQL> alter database enable thread 2;
SQL> alter system set log_archive_config='DG_CONFIG=(PRIMARY,STANDBY)';
System altered.
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 19
Next log sequence to archive 21
Current log sequence 21
SQL> !mkdir -p /u01/app/oracle/archive_log
SQL> alter system set log_archive_dest_1='LOCATION=/u01/app/oracle/archive_log VALID_FOR=ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=ORCL';
SQL> alter system set log_archive_dest_2='SERVICE=standby LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=standby';
SQL> alter system set fal_server=standby;
SQL> alter system set fal_client=primary;
SQL> alter system set standby_file_management=auto;
System altered.SQL> !mkdir -p /home/oracle/rman_bak
三：在primar庫上生成pfile，并使用rman備份資料庫,将備份的資料複制到rac節點1上
SQL> create pfile='/home/oracle/rman_bak/initorcl.ora' from spfile;
File created.
[oracle@server49 ~]$ rman target /
Recovery Manager: Release 10.2.0.5.0 - Production on Sun Jan 8 14:49:21 2012
Copyright (c) 1982, 2007, Oracle. All rights reserved.
connected to target database: ORCL (DBID=1299224612)
RMAN> run {
2> allocate channel c1 device type disk;
3> allocate channel c2 device type disk;
4> backup incremental level 0
5> format '/home/oracle/rman_bak/inr0_%U'
6> tag 'full_bak_for_rac_standby'
7> database plus archivelog delete all input;
8> release channel c1;
9> release channel c2;
10> }
RMAN> backup format '/home/oracle/rman_bak/control01.ctl' current controlfile for standby;
[oracle@server49 ~]$ scp -rp rman_bak/ rac1:$(pwd)
四：standby(節點1)資料庫上，同樣配置tnsnames.ora檔案，建立密碼檔案，修改參數檔案
[oracle@rac1 ~]$ cat $ORACLE_HOME/network/admin/tnsnames.ora
ORCL1 =
(ADDRESS = (PROTOCOL = TCP)(HOST = rac1-vip.yang.com)(PORT = 1521))
(INSTANCE_NAME = orcl1)
ORCL2 =
(ADDRESS = (PROTOCOL = TCP)(HOST = rac2-vip.yang.com)(PORT = 1521))
(INSTANCE_NAME = orcl2)
ORCL =
(LOAD_BALANCE = yes)
(SERVICE_NAME = orcl.yang.com)
LISTENERS_ORCL =
(ADDRESS_LIST =
(INSTANCE_NAME = orcl)
)
[oracle@rac1 ~]$ crs_stat -t -v
Name Type R/RA F/FT Target State Host
----------------------------------------------------------------------
ora....SM1.asm application 0/5 0/0 ONLINE ONLINE rac1
ora....C1.lsnr application 0/5 0/0 ONLINE ONLINE rac1
ora.rac1.gsd application 0/5 0/0 ONLINE ONLINE rac1
ora.rac1.ons application 0/3 0/0 ONLINE ONLINE rac1
ora.rac1.vip application 0/0 0/0 ONLINE ONLINE rac1
ora....SM2.asm application 0/5 0/0 ONLINE ONLINE rac2
ora....C2.lsnr application 0/5 0/0 ONLINE ONLINE rac2
ora.rac2.gsd application 0/5 0/0 ONLINE ONLINE rac2
ora.rac2.ons application 0/3 0/0 ONLINE ONLINE rac2
ora.rac2.vip application 0/0 0/0 ONLINE ONLINE rac2
[oracle@rac1 ~]$ orapwd file=$ORACLE_HOME/dbs/orapworcl1 password=123456
[oracle@rac1 ~]$ cat /home/oracle/rman_bak/initorcl.ora
*.__db_cache_size=79691776
*.__java_pool_size=4194304
*.__large_pool_size=4194304
*.__shared_pool_size=109051904
*.__streams_pool_size=8388608
*.audit_file_dest='/u01/app/oracle/admin/orcl/adump'
*.background_dump_dest='/u01/app/oracle/admin/orcl/bdump'
*.core_dump_dest='/u01/app/oracle/admin/orcl/cdump'
*.user_dump_dest='/u01/app/oracle/admin/orcl/udump'
*.compatible='10.2.0.5.0'
*.control_files='+DATA/ORCL/CONTROLFILE/CONTROL01.CTL','+FRA/ORCL/CONTROLFILE/CONTROL02.CTL'
*.db_block_size=8192
*.db_domain='yang.com'
*.db_file_multiblock_read_count=16
*.db_name='orcl'
*.db_recovery_file_dest='+FRA'
*.db_recovery_file_dest_size=21474836480
*.dispatchers='(PROTOCOL=TCP) (SERVICE=orclXDB)'
*.fal_client='STANDBY'
*.fal_server='PRIMARY'
*.job_queue_processes=10
*.log_archive_config='DG_CONFIG=(PRIMARY,STANDBY)'
*.log_archive_dest_1='LOCATION=+FRA/ORCL/ARCHIVELOG VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=STANDBY'
*.log_archive_dest_2='SERVICE=PRIMARY LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=PRIMARY'
*.log_archive_format='%t_%s_%r.dbf'
*.open_cursors=300
*.pga_aggregate_target=71303168
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.sga_target=213909504
*.standby_file_management='AUTO'
*.undo_management='AUTO'
*.cluster_database=true
*.cluster_database_instances=2
*.db_unique_name='standby'
*.log_file_name_convert=('/u01/app/oracle/flash_recover_area', '+FRA/ORCL/ARCHIVELOG')
*.db_file_name_convert=('/u01/app/oracle/oradata/orcl', '+DATA/ORCL/DATAFILE')
orcl1.undo_tablespace='UNDOTBS1'
orcl2.undo_tablespace='UNDOTBS2'
orcl1.instance_number=1
orcl2.instance_number=2
orcl1.thread=1
orcl2.thread=2
orcl1.local_listener='(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.1.41)(PORT=1521))'
orcl2.local_listener='(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.1.42)(PORT=1521))'
六：在節點1資料庫上恢複控制檔案
[oracle@rac1 ~]$ rman target /
connected to target database: orcl (not mounted)
RMAN> restore controlfile from '/home/oracle/rman_bak/control01.ctl';
備注：ASM磁盤内的相應目錄要先建立好
ASMCMD> pwd
+FRA/ORCL/controlfile
ASMCMD> ls
control02.ctl
ASMCMD> cd +DATA/ORCL/controlfile
control01.ctl
七：節點1資料庫連接配接primary進行duplicate操作
connected to auxiliary database: ORCL (not mounted)
RMAN> duplicate target database for standby;
輸出省略
connected to target database: ORCL (DBID=1299224612, not open)
RMAN> report schema;
Starting implicit crosscheck backup at 2012-01-09 15:35:29
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
Crosschecked 9 objects
Finished implicit crosscheck backup at 2012-01-09 15:35:31
Starting implicit crosscheck copy at 2012-01-09 15:35:31
using channel ORA_DISK_1
Finished implicit crosscheck copy at 2012-01-09 15:35:31
searching for all files in the recovery area
cataloging files...
no files cataloged
RMAN-06139: WARNING: control file is not current for REPORT SCHEMA
Report of database schema
List of Permanent Datafiles
===========================
File Size(MB) Tablespace RB segs Datafile Name
---- -------- -------------------- ------- ------------------------
1 450 SYSTEM *** +DATA/orcl/datafile/system01.dbf
2 30 UNDOTBS1 *** +DATA/orcl/datafile/undotbs01.dbf
3 260 SYSAUX *** +DATA/orcl/datafile/sysaux01.dbf
4 5 USERS *** +DATA/orcl/datafile/users01.dbf
5 100 EXAMPLE *** +DATA/orcl/datafile/example01.dbf
6 410 EXP_RAC *** +DATA/orcl/datafile/exp_rac01.dbf
7 300 EXP_RAC_INDEX *** +DATA/orcl/datafile/exp_rac_index01.dbf
8 300 EXP_RAC1 *** +DATA/orcl/datafile/exp_rac1_01.dbf
9 300 EXP_RAC1_INDEX *** +DATA/orcl/datafile/exp_rac1_index_01.dbf
10 30 UNDOTBS2 *** +DATA/orcl/datafile/undotbs02.dbf
List of Temporary Files
=======================
File Size(MB) Tablespace Maxsize(MB) Tempfile Name
---- -------- -------------------- ----------- --------------------
1 0 TEMP 32767 +DATA/orcl/datafile/temp01.dbf
+data/ORCL/datafile
example01.dbf
exp_rac01.dbf
exp_rac1_01.dbf
exp_rac1_index_01.dbf
exp_rac_index01.dbf
sysaux01.dbf
system01.dbf
undotbs01.dbf
undotbs02.dbf
users01.dbf
八：啟動節點1資料庫到應用日志模式下，中間若出現歸檔日志的gap，則需要在主庫上restore相應的歸檔日志檔案
[oracle@rac1 ~]$ sqlplus /nolog
SQL*Plus: Release 10.2.0.5.0 - Production on Mon Jan 9 15:43:34 2012
Copyright (c) 1982, 2010, Oracle. All Rights Reserved.
SQL> conn /as sysdba
Connected.
Archive destination +FRA/orcl/archivelog
Oldest online log sequence 26
Next log sequence to archive 0
Current log sequence 28
SQL> alter database recover managed standby database disconnect from session;
SQL> select name,database_role from gv$database;
NAME DATABASE_ROLE
--------------------------- ------------------------------------------------
ORCL PHYSICAL STANDBY
SQL> select first_time,next_time,sequence#,applied from v$archived_log;
FIRST_TIME NEXT_TIME SEQUENCE# APPLIED
------------------- ------------------- ---------- ---------
2012-01-09 14:17:57 2012-01-09 14:52:34 27 NO
[oracle@rac1 ~]$ tail -f /u01/app/oracle/admin/orcl/bdump/alert_orcl1.log
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
Mon Jan 09 15:44:26 CST 2012
Errors in file /u01/app/oracle/admin/orcl/bdump/orcl1_mrp0_15952.trc:
ORA-00344: unable to re-create online log '/u01/app/oracle/oradata/orcl/redo01.log'
ORA-27040: file create error, unable to create file
Clearing online redo logfile 1 complete
Media Recovery Waiting for thread 1 sequence 26
Fetching gap sequence in thread 1, gap sequence 26-26
Completed: alter database recover managed standby database disconnect from session
Mon Jan 09 15:45:00 CST 2012
FAL[client]: Failed to request gap sequence
GAP - thread 1 sequence 26-26
DBID 1299224612 branch 771443882
FAL[client]: All defined FAL servers have been attempted.
-------------------------------------------------------------
Check that the CONTROL_FILE_RECORD_KEEP_TIME initialization
parameter is defined to a value that is sufficiently large
enough to maintain adequate log switch information to resolve
archivelog gaps.
primary資料庫操作：
RMAN> restore archivelog from sequence 24;
[oracle@server49 ~]$ sqlplus /nolog
SQL*Plus: Release 10.2.0.5.0 - Production on Mon Jan 9 16:15:16 2012
Archive destination /u01/app/oracle/archive_log
Next log sequence to archive 28
SQL> alter system switch logfile;
RFS[2]: Assigned to RFS process 28510
RFS[2]: Identified database type as 'physical standby'
Primary database is in MAXIMUM PERFORMANCE mode
Mon Jan 09 16:15:46 CST 2012
RFS[1]: Archived Log: '+FRA/orcl/archivelog/1_28_771443882.dbf'
RFS[1]: Archived Log: '+FRA/orcl/archivelog/2_6_771443882.dbf'
Mon Jan 09 16:15:50 CST 2012
Fetching gap sequence in thread 2, gap sequence 5-5
Mon Jan 09 16:16:25 CST 2012
GAP - thread 2 sequence 5-5
SQL>select first_time,next_time,sequence#,applied from v$archived_log;
2012-01-09 14:13:14 2012-01-09 14:17:57 26 NO
2012-01-09 14:52:34 2012-01-09 16:15:37 28 NO
2012-01-09 14:17:56 2012-01-09 16:15:44 6 NO
RMAN> restore archivelog from sequence 5 thread 2;
節點1資料庫繼續查詢：
2012-01-09 14:17:57 2012-01-09 14:52:34 27 YES
2012-01-09 14:13:14 2012-01-09 14:17:57 26 YES
2012-01-09 14:52:34 2012-01-09 16:15:37 28 YES
2012-01-09 14:17:56 2012-01-09 16:15:44 6 YES
2012-01-09 14:13:12 2012-01-09 14:17:56 5 YES
2012-01-09 16:15:37 2012-01-09 16:30:00 29 NO
6 rows selected.
上述過程沒有問題後，就可以啟動節點2的資料庫執行個體到mount狀态！
SQL> select name,db_unique_name,open_mode,database_role from gv$database;
NAME DB_UNIQUE_ OPEN_MODE DATABASE_ROLE
-------------------- ---------- -------------------- --------------------
ORCL standby MOUNTED PHYSICAL STANDBY
最後确定下主庫和備庫的日志傳輸和應用狀況是否正常！
Oldest online log sequence 34
Next log sequence to archive 36
Current log sequence 36
SQL> select first_time,next_time,sequence#,applied,thread# from v$archived_log order by 5,3 ;
FIRST_TIME NEXT_TIME SEQUENCE# APPLIED THREAD#
------------------- ------------------- ---------- --------- ----------
2012-01-09 14:13:14 2012-01-09 14:17:57 26 YES 1
2012-01-09 14:17:57 2012-01-09 14:52:34 27 YES 1
2012-01-09 14:52:34 2012-01-09 16:15:37 28 YES 1
2012-01-09 16:15:37 2012-01-09 16:30:00 29 YES 1
2012-01-09 16:30:00 2012-01-09 18:04:06 30 YES 1
2012-01-09 18:04:06 2012-01-09 18:04:18 31 YES 1
2012-01-09 18:04:18 2012-01-09 18:06:40 32 YES 1
2012-01-09 18:06:40 2012-01-09 19:55:25 33 YES 1
2012-01-09 19:55:25 2012-01-09 19:55:38 34 YES 1
2012-01-09 19:55:38 2012-01-09 19:56:30 35 NO 1
2012-01-09 14:13:12 2012-01-09 14:17:56 5 YES 2
2012-01-09 14:17:56 2012-01-09 16:15:44 6 YES 2
2012-01-09 16:15:44 2012-01-09 18:04:05 7 YES 2
2012-01-09 18:04:05 2012-01-09 18:04:17 8 YES 2
2012-01-09 18:04:17 2012-01-09 18:06:36 9 YES 2
2012-01-09 18:06:36 2012-01-09 19:55:23 10 YES 2
2012-01-09 19:55:23 2012-01-09 19:55:37 11 YES 2
2012-01-09 19:55:37 2012-01-09 19:56:26 12 YES 2
18 rows selected.
SQL> select name,db_unique_name,database_role,switchover_status from v$database;
NAME DB_UNIQUE_NAME DATABASE_ROLE SWITCHOVER_STATUS
--------- ------------------------------ ---------------- --------------------
ORCL primary PRIMARY SESSIONS ACTIVE
至此，單執行個體主庫和rac實體備庫的環境以及基本完成，下一節中将介紹下switchover過程！
本文轉自斬月部落格51CTO部落格，原文連結http://blog.51cto.com/ylw6006/760860如需轉載請自行聯系原作者
ylw6006
![【MySQL資料庫】資料庫索引事務1.索引2.事務[圖]](data:image/gif;base64,R0lGODlhAQABAIAAAP///wAAACwAAAAAAQABAAACAkQBADs=)