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