跨平台级联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的级联备库支持实时应用。