跨平臺級聯dataguard配置

    正式環境中的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的級聯備庫支持實時應用。