正式環境中的OA庫要從Windows遷移到Linux，但由於資料量比較大，頻寬又比較窄，資料泵方式耗時太長，因此打算用DataGuard方式遷移，因此進行了測試。

環境

主庫：OS:Windows VERSION:11.2.0.4 ORACLE_SID:OA DB_NAME:OA DB_UNIQUE_NAME:OA

備庫：OS:Windows VERSION:11.2.0.4 ORACLE_SID:OA DB_NAME:OA DB_UNIQUE_NAME:OA_DG

級聯備庫：OS:Linux VERSION:11.2.0.4 ORACLE_SID:OA DB_NAME:OA DB_UNIQUE_NAME:OA_DG2

1.檢視各庫平臺資訊

SQL>select platform_id,platform_name from v$database;

主庫與備庫相同：

PLATFORM_ID PLATFORM_NAME

----------- ----------------------------

12 Microsoft Windows x86 64-bit

級聯備庫：

PLATFORM_ID PLATFORM_NAME

----------- ---------------------------

13 Linux x86 64-bit

檢視相容表格，可看到當前的平臺與Oracle版本是可跨平臺搭建DataGuard的，但是要打Patch 13104881(當前版本已打)

2.備庫生成pfile，並將pfile、密碼檔案傳輸到OA_DG2相關目錄下

3.級聯備庫上修改pfile

[oracle@node3 dbs]$ more initOA.ora

*. audit_file_dest ='/opt/app/oracle/admin/OA/adump'

*.audit_trail='DB'

*.compatible='11.2.0.0.0'

*. control_files ='/opt/app/oracle/oradata/OA/CONTROL01.CTL','/opt/app/oracle/fast_recovery_area/OA/CONTROL02.CTL'

*.db_block_size=8192

*.db_name='OA'

*.db_recovery_file_dest_size=4102029312

*. db_recovery_file_dest ='/opt/app/oracle/fast_recovery_area'

*. db_unique_name ='OA_DG2'

*. diagnostic_dest ='/opt/app/oracle'

*.dispatchers='(PROTOCOL=TCP) (SERVICE=OAXDB)'

*. fal_client ='OA_DG2'

*. fal_server ='OA_DG'

*. db_file_name_convert ='C:\APP\ADMINISTRATOR\ORADATA\OA\','/opt/app/oracle/oradata/OA/','C:\APP\ADMINISTRATOR\FLASH_RECOVERY_AREA\OA\','/opt/app/oracle/fast_recovery_area/OA/'

*. log_file_name_convert ='C:\APP\ADMINISTRATOR\ORADATA\OA\','/opt/app/oracle/oradata/OA/'

*. log_archive_config ='dg_config=(OA,OA_DG,OA_DG2)'

*.log_archive_dest_1='location=/opt/app/oracle/archivelog valid_for=(all_logfiles,all_roles)

*.log_archive_format='ARC%S_%R.%T'

*.nls_language='SIMPLIFIED CHINESE'

*.nls_territory='CHINA'

*.open_cursors=300

*.pga_aggregate_target=428867584

*.processes=150

*.recyclebin='OFF'

*.remote_login_passwordfile='EXCLUSIVE'

*.sga_target=1291845632

*.standby_file_management='AUTO'

*.undo_tablespace='UNDOTBS1'

修改紅色字型部分，使其適應當前環境

4.級聯備庫上建立所需目錄

cd $ORACLE_BASE

mkdir -p oradata/OA/adump

mkdir -p oradata/OA

mkdir -p fast_recovery_area/OA

5.新增tns

備庫新增級聯備庫的tns

OA_DG2 =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = 172.21.74.231)(PORT = 1521))

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = oa)

)

級聯備庫新增備庫的tns

OA_DG =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = 172.21.74.233)(PORT = 1521))

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = oa)

)

6.修改備庫引數，將standby logfile傳輸到級聯備庫

SQL> alter system set log_archive_dest_state_3=defer; --暫時關閉

SQL> alter system set log_archive_config='dg_config=(OA,OA_DG,OA_DG2)';

SQL> alter system set log_archive_dest_3='service=OA_DG2 async valid_for=(STANDBY_LOGFILES,STANDBY_ROLE) db_unique_name=OA_DG2'

7.級聯備庫配置靜態監聽

[oracle@node3 admin]$ more listener.ora

LISTENER =

(DESCRIPTION_LIST =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))

(ADDRESS = (PROTOCOL = TCP)(HOST = 172.21.74.231)(PORT = 1521))

)

SID_LIST_LISTENER=

(SID_LIST=

(SID_DESC=

(SID_NAME = OA)

(ORACLE_HOME = /opt/app/oracle/product/11g)

(GLOBAL_DBNAME = OA)

)

ADR_BASE_LISTENER = /opt/app/oracle

8.使用RMAN的複製功能建立備庫：

1)lsnrctl start

2)rman target sys@primary auxiliary sys@standby

3)duplicate target database for standby from active database;

如果報錯RMAN-05001: auxiliary file name /opt/app/oracle/oradata/orcl/users01.dbf conflicts with a file used by the target database，則執行duplicate target database for standby from active database nofilenamecheck;

也可以並行複製以提高效能。需要分派主庫和備庫多個通道後，再執行復制命令：

run

{

allocate channel chan1 type disk;

allocate channel chan2 type disk;

allocate channel chan3 type disk;

allocate channel chan4 type disk;

allocate auxiliary channel aux1 type disk;

allocate auxiliary channel aux2 type disk;

allocate auxiliary channel aux3 type disk;

allocate auxiliary channel aux4 type disk;

duplicate target database for standby from active database;

}

9.複製完成後，驗證級聯備庫中各檔案路徑是否正確

SQL> select file_name from dba_data_files;

FILE_NAME

--------------------------------------------------------------------------------

/opt/app/oracle/oradata/OA/USERS01.DBF

/opt/app/oracle/oradata/OA/UNDOTBS01.DBF

/opt/app/oracle/oradata/OA/SYSAUX01.DBF

/opt/app/oracle/oradata/OA/SYSTEM01.DBF

SQL> select group#,member from v$logfile;

GROUP# TYPE MEMBER

---------- ------ --------------------------------------------------

3 ONLINE /opt/app/oracle/oradata/OA/REDO03.LOG

2 ONLINE /opt/app/oracle/oradata/OA/REDO02.LOG

1 ONLINE /opt/app/oracle/oradata/OA/REDO01.LOG

4 STANDBY C:\APP\ADMINISTRATOR\ORADATA\OA\STANDBYREDO04.LOG

5 STANDBY C:\APP\ADMINISTRATOR\ORADATA\OA\STANDBYREDO05.LOG

6 STANDBY C:\APP\ADMINISTRATOR\ORADATA\OA\STANDBYREDO06.LOG

7 STANDBY C:\APP\ADMINISTRATOR\ORADATA\OA\STANDBYREDO07.LOG

7 rows selected.

可看到standby redo的目錄不正確(貌似log_file_name_convert對standby log沒起作用)，此時可手動刪掉standby日誌組，再重新新增：

SQL> alter database drop standby logfile group 4;

SQL> alter database drop standby logfile group 5;

SQL> alter database drop standby logfile group 6;

SQL> alter database drop standby logfile group 7;

SQL> alter database add standby logfile group 4 ('/opt/app/oracle/oradata/OA/STANDBYRD04.LOG') size 50M;

SQL> alter database add standby logfile group 5 ('/opt/app/oracle/oradata/OA/STANDBYRD05.LOG') size 50M;

SQL> alter database add standby logfile group 6 ('/opt/app/oracle/oradata/OA/STANDBYRD06.LOG') size 50M;

SQL> alter database add standby logfile group 7 ('/opt/app/oracle/oradata/OA/STANDBYRD07.LOG') size 50M;

驗證完畢後開啟備庫的傳輸引數

SQL> alter system set log_archive_dest_3_state=enable;

驗證傳輸是否正常，在級聯備庫執行

SQL> SELECT PROCESS,

2 PID,

3 STATUS,

4 SEQUENCE#,

5 DELAY_MINS

6 FROM V$MANAGED_STANDBY;

PROCESS PID STATUS SEQUENCE# DELAY_MINS

------------------------------ ---------- -------------------- ---------- ----------

ARCH 30382 CLOSING 158 0

ARCH 30384 CLOSING 154 0

ARCH 30386 CONNECTED 0 0

ARCH 30388 CLOSING 155 0

RFS 32195 IDLE 0 0

RFS 32193 IDLE 0 0

RFS 32191 IDLE 0 0

可看到RFS程式已經建立，說明傳輸沒有問題。(如果傳輸有問題，可結合備庫的alert日誌進行處理，應該就是密碼檔案的問題)

10.級聯備庫啟動實時應用

啟動日誌應用：

alter database recover managed standby database disconnect;

這個命令指示備庫開始使用歸檔日誌檔案進行恢復。

待歸檔日誌應用完畢後，啟動實時應用：

SQL> alter database recover managed standby database cancel;

SQL> alter database open;

SQL> alter database recover managed standby database using current logfile disconnect; - -雖然語句執行後MRP程式啟動成功，但驗證下來還是沒有真正實時應用日誌。

驗證實時應用情況：

SQL> SELECT * FROM V$DATAGUARD_STATS;

NAME VALUE UNIT TIME_COMPUTED DATUM_TIME

------------------------------ ------------------------------ ---------------------------------------- ------------------------------ ------------------------------

transport lag +00 00:05:22 day(2) to second(0) interval 11/01/2018 17:44:19 11/01/2018 17:43:28

apply lag +00 00:05:22 day(2) to second(0) interval 11/01/2018 17:44:19 11/01/2018 17:43:28

apply finish time day(2) to second(3) interval 11/01/2018 17:44:19

estimated startup time 11 second

注意：Oracle 11g的級聯備庫是不支援實時應用的，要等源庫日誌切換後才會應用。Oracle 12c的級聯備庫支援實時應用。

跨平臺級聯dataguard配置

相關文章