1. 环境说明, 续上章RAC单向DDL同步。
2. 确认源库处于归档模式。
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /oracle/nimng
Oldest online log sequence 42
Next log sequence to archive 43
Current log sequence 43
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /oracle/nimng
Oldest online log sequence 42
Next log sequence to archive 43
Current log sequence 43
3. 确认源库为FORCE_LOGGIN。
SQL> alter database force logging;
SQL> alter database force logging;
Database altered.
SQL> select force_logging from v$database;
FOR
---
YES
---
YES
4. 确认源库打开辅助日志。
SQL> select supplemental_log_data_min from v$database;
SQL> select supplemental_log_data_min from v$database;
SUPPLEME
--------
YES
--------
YES
5. 源库关闭回收站。
SQL> show parameter recyclebin
SQL> show parameter recyclebin
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
recyclebin string on
SQL> alter system set recyclebin=off;
------------------------------------ ----------- ------------------------------
recyclebin string on
SQL> alter system set recyclebin=off;
System altered.
SQL> show parameter recyclebin
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
recyclebin string OFF
------------------------------------ ----------- ------------------------------
recyclebin string OFF
6. 保证字符集一致。
7. UNDO设置
SQL> alter system set undo_retention=86400;
7. UNDO设置
SQL> alter system set undo_retention=86400;
System altered.
8. 安装DDL_OBJETS
SQL> @marker_setup
SQL> @marker_setup
Marker setup script
You will be prompted for the name of a schema for the Oracle GoldenGate database objects.
NOTE: The schema must be created prior to running this script.
NOTE: Stop all DDL replication before starting this installation.
NOTE: The schema must be created prior to running this script.
NOTE: Stop all DDL replication before starting this installation.
Enter Oracle GoldenGate schema name:goldengate
Marker setup table script. complete, running verification script...
Please enter the name of a schema for the GoldenGate database objects:
Setting schema name to GOLDENGATE
MARKER TABLE
-------------------------------
OK
-------------------------------
OK
MARKER SEQUENCE
-------------------------------
OK
-------------------------------
OK
Script. complete.
执行DDL_STEP
SQL> @ddl_setup
SQL> @ddl_setup
Oracle GoldenGate DDL Replication setup script
Verifying that current user has privileges to install DDL Replication...
You will be prompted for the name of a schema for the Oracle GoldenGate database objects.
NOTE: For an Oracle 10g source, the system recycle bin must be disabled. For Oracle 11g and later, it can be enabled.
NOTE: The schema must be created prior to running this script.
NOTE: Stop all DDL replication before starting this installation.
NOTE: For an Oracle 10g source, the system recycle bin must be disabled. For Oracle 11g and later, it can be enabled.
NOTE: The schema must be created prior to running this script.
NOTE: Stop all DDL replication before starting this installation.
Enter Oracle GoldenGate schema name:goldengate
Working, please wait ...
Spooling to file ddl_setup_spool.txt
Spooling to file ddl_setup_spool.txt
Checking for sessions that are holding locks on Oracle Golden Gate metadata tables ...
Check complete.
Using GOLDENGATE as a Oracle GoldenGate schema name.
Working, please wait ...
RECYCLEBIN must be empty.
This installation will purge RECYCLEBIN for all users.
To proceed, enter yes. To stop installation, enter no.
This installation will purge RECYCLEBIN for all users.
To proceed, enter yes. To stop installation, enter no.
Enter yes or no:yes
DDL replication setup script. complete, running verification script...
Please enter the name of a schema for the GoldenGate database objects:
Setting schema name to GOLDENGATE
CLEAR_TRACE STATUS:
Line/pos Error
---------------------------------------- -----------------------------------------------------------------
No errors No errors
---------------------------------------- -----------------------------------------------------------------
No errors No errors
CREATE_TRACE STATUS:
Line/pos Error
---------------------------------------- -----------------------------------------------------------------
No errors No errors
---------------------------------------- -----------------------------------------------------------------
No errors No errors
TRACE_PUT_LINE STATUS:
Line/pos Error
---------------------------------------- -----------------------------------------------------------------
No errors No errors
---------------------------------------- -----------------------------------------------------------------
No errors No errors
INITIAL_SETUP STATUS:
Line/pos Error
---------------------------------------- -----------------------------------------------------------------
No errors No errors
---------------------------------------- -----------------------------------------------------------------
No errors No errors
DDLVERSIONSPECIFIC PACKAGE STATUS:
Line/pos Error
---------------------------------------- -----------------------------------------------------------------
No errors No errors
---------------------------------------- -----------------------------------------------------------------
No errors No errors
DDLREPLICATION PACKAGE STATUS:
Line/pos Error
---------------------------------------- -----------------------------------------------------------------
No errors No errors
---------------------------------------- -----------------------------------------------------------------
No errors No errors
DDLREPLICATION PACKAGE BODY STATUS:
Line/pos Error
---------------------------------------- -----------------------------------------------------------------
No errors No errors
---------------------------------------- -----------------------------------------------------------------
No errors No errors
DDL IGNORE TABLE
-----------------------------------
OK
-----------------------------------
OK
DDL IGNORE LOG TABLE
-----------------------------------
OK
-----------------------------------
OK
DDLAUX PACKAGE STATUS:
Line/pos Error
---------------------------------------- -----------------------------------------------------------------
No errors No errors
---------------------------------------- -----------------------------------------------------------------
No errors No errors
DDLAUX PACKAGE BODY STATUS:
Line/pos Error
---------------------------------------- -----------------------------------------------------------------
No errors No errors
---------------------------------------- -----------------------------------------------------------------
No errors No errors
SYS.DDLCTXINFO PACKAGE STATUS:
Line/pos Error
---------------------------------------- -----------------------------------------------------------------
No errors No errors
---------------------------------------- -----------------------------------------------------------------
No errors No errors
SYS.DDLCTXINFO PACKAGE BODY STATUS:
Line/pos Error
---------------------------------------- -----------------------------------------------------------------
No errors No errors
---------------------------------------- -----------------------------------------------------------------
No errors No errors
DDL HISTORY TABLE
-----------------------------------
OK
-----------------------------------
OK
DDL HISTORY TABLE(1)
-----------------------------------
OK
-----------------------------------
OK
DDL DUMP TABLES
-----------------------------------
OK
-----------------------------------
OK
DDL DUMP COLUMNS
-----------------------------------
OK
-----------------------------------
OK
DDL DUMP LOG GROUPS
-----------------------------------
OK
-----------------------------------
OK
DDL DUMP PARTITIONS
-----------------------------------
OK
-----------------------------------
OK
DDL DUMP PRIMARY KEYS
-----------------------------------
OK
-----------------------------------
OK
DDL SEQUENCE
-----------------------------------
OK
-----------------------------------
OK
GGS_TEMP_COLS
-----------------------------------
OK
-----------------------------------
OK
GGS_TEMP_UK
-----------------------------------
OK
-----------------------------------
OK
DDL TRIGGER CODE STATUS:
Line/pos Error
---------------------------------------- -----------------------------------------------------------------
No errors No errors
---------------------------------------- -----------------------------------------------------------------
No errors No errors
DDL TRIGGER INSTALL STATUS
-----------------------------------
OK
-----------------------------------
OK
DDL TRIGGER RUNNING STATUS
------------------------------------------------------------------------------------------------------------------------
ENABLED
------------------------------------------------------------------------------------------------------------------------
ENABLED
STAYMETADATA IN TRIGGER
------------------------------------------------------------------------------------------------------------------------
OFF
------------------------------------------------------------------------------------------------------------------------
OFF
DDL TRIGGER SQL TRACING
------------------------------------------------------------------------------------------------------------------------
0
------------------------------------------------------------------------------------------------------------------------
0
DDL TRIGGER TRACE LEVEL
------------------------------------------------------------------------------------------------------------------------
0
------------------------------------------------------------------------------------------------------------------------
0
LOCATION OF DDL TRACE FILE
------------------------------------------------------------------------------------------------------------------------
/home/oracle/admin/gg1/udump/ggs_ddl_trace.log
------------------------------------------------------------------------------------------------------------------------
/home/oracle/admin/gg1/udump/ggs_ddl_trace.log
Analyzing installation status...
STATUS OF DDL REPLICATION
------------------------------------------------------------------------------------------------------------------------
SUCCESSFUL installation of DDL Replication software components
Script. complete.
执行ROLE_SETUP
SQL> @role_setup
SQL> @role_setup
GGS Role setup script
This script. will drop and recreate the role GGS_GGSUSER_ROLE
To use a different role name, quit this script. and then edit the params.sql script. to change the gg_role parameter to the preferred name. (Do not run the script.)
To use a different role name, quit this script. and then edit the params.sql script. to change the gg_role parameter to the preferred name. (Do not run the script.)
You will be prompted for the name of a schema for the GoldenGate database objects.
NOTE: The schema must be created prior to running this script.
NOTE: Stop all DDL replication before starting this installation.
NOTE: The schema must be created prior to running this script.
NOTE: Stop all DDL replication before starting this installation.
Enter GoldenGate schema name:goldengate
Wrote file role_setup_set.txt
Wrote file role_setup_set.txt
PL/SQL procedure successfully completed.
Role setup script. complete
Grant this role to each user assigned to the Extract, GGSCI, and Manager processes, by using the following SQL command:
GRANT GGS_GGSUSER_ROLE TO
where is the user assigned to the GoldenGate processes.
将此角色给GOLDENGATE用户。
SQL> grant ggs_ggsuser_role to goldengate;
SQL> grant ggs_ggsuser_role to goldengate;
Grant succeeded.
开启DDL
SQL> @ddl_enable
SQL> @ddl_enable
Trigger altered.
安装性能优化包。
SQL> @?/rdbms/admin/dbmspool
SQL> @?/rdbms/admin/dbmspool
Package created.
Grant succeeded.
View created.
Package body created.
ddl_pin将触发器用到的plsql包放进内存中
SQL> @ddl_pin goldengate
SQL> @ddl_pin goldengate
PL/SQL procedure successfully completed.
PL/SQL procedure successfully completed.
PL/SQL procedure successfully completed.
9. ./GLOBALS配置
源端:
GGSCI (rac1) 5> edit params ./GLOBALS
GGSCHEMA goldengate
CHECKPOINTTABLE goldengate.chkpoint
源端:
GGSCI (rac1) 5> edit params ./GLOBALS
GGSCHEMA goldengate
CHECKPOINTTABLE goldengate.chkpoint
目标端:
GGSCI (rac1) 5> edit params ./GLOBALS
GGSCHEMA goldengate
CHECKPOINTTABLE goldengate.chkpoint
GGSCI (rac1) 5> edit params ./GLOBALS
GGSCHEMA goldengate
CHECKPOINTTABLE goldengate.chkpoint
GGSCI (rac1) 2> dblogin userid goldengate, password goldengate
Successfully logged into database.
Successfully logged into database.
GGSCI (rac1) 3> add checkpointtable goldengate.chkpoint
Successfully created checkpoint table GOLDENGATE.CHKPOINT.
10. MGR配置
源端:增加如下:PURGEOLDEXTRACTS /gg/ggtarget/trails/w2*, USECHECKPOINTS, MINKEEPFILES 10
源端:增加如下:PURGEOLDEXTRACTS /gg/ggtarget/trails/w2*, USECHECKPOINTS, MINKEEPFILES 10
目标端: 增加如下:PURGEOLDEXTRACTS /gg/ggsource/trails/w2*, USECHECKPOINTS, MINKEEPFILES 10
11. 源端添加EXTRACT
GGSCI (rac1) 2> add extract w2ext, tranlog, threads 2, begin now
EXTRACT added.
GGSCI (rac1) 2> add extract w2ext, tranlog, threads 2, begin now
EXTRACT added.
修改配置参数:
GGSCI (rac1) 3> edit params w2ext
EXTRACT w2ext
SETENV (NLS_LANG=AMERICAN_AMERICA.AL32UTF8)
USERID goldengate@nimng1, PASSWORD goldengate
EXTTRAIL /gg/ggtarget/trails/w2
TRANLOGOPTIONS EXCLUDEUSER goldengate
IGNOREREPLICATES
tranlogoptions rawdeviceoffset 0
DISCARDFILE w2extdsc,APPEND,MEGABYTES 5
TRANLOGOPTIONS ALTARCHIVELOGDEST /oracle/nimng
TRANLOGOPTIONS ALTARCHIVEDLOGFORMAT %t_%s_%r.dbf
DDL INCLUDE MAPPED
DDLOPTIONS ADDTRANDATA
FETCHOPTIONS, USESNAPSHOT, NOUSELATESTVERSION, MISSINGROW REPORT
STATOPTIONS REPORTFETCH
WARNLONGTRANS 1H, CHECKINTERVAL 5M
TABLE hr.*;
GGSCI (rac1) 3> edit params w2ext
EXTRACT w2ext
SETENV (NLS_LANG=AMERICAN_AMERICA.AL32UTF8)
USERID goldengate@nimng1, PASSWORD goldengate
EXTTRAIL /gg/ggtarget/trails/w2
TRANLOGOPTIONS EXCLUDEUSER goldengate
IGNOREREPLICATES
tranlogoptions rawdeviceoffset 0
DISCARDFILE w2extdsc,APPEND,MEGABYTES 5
TRANLOGOPTIONS ALTARCHIVELOGDEST /oracle/nimng
TRANLOGOPTIONS ALTARCHIVEDLOGFORMAT %t_%s_%r.dbf
DDL INCLUDE MAPPED
DDLOPTIONS ADDTRANDATA
FETCHOPTIONS, USESNAPSHOT, NOUSELATESTVERSION, MISSINGROW REPORT
STATOPTIONS REPORTFETCH
WARNLONGTRANS 1H, CHECKINTERVAL 5M
TABLE hr.*;
添加该extract对应的trail文件,用来存储抽取的数据。单个文件大小设置为100MB
GGSCI (rac1) 7> add exttrail /gg/ggtarget/trails/w2,extract w2ext, MEGABYTES 100
EXTTRAIL added.
GGSCI (rac1) 7> add exttrail /gg/ggtarget/trails/w2,extract w2ext, MEGABYTES 100
EXTTRAIL added.
12. 修改目标端W1EXT
添加如下内容:
TRANLOGOPTIONS EXCLUDEUSER goldengate
IGNOREREPLICATES
添加如下内容:
TRANLOGOPTIONS EXCLUDEUSER goldengate
IGNOREREPLICATES
13. 源端增加PUMP
GGSCI (rac1) 5> add extract w2extdp, exttrailsource /gg/ggtarget/trails/w2, begin now
EXTRACT added.
GGSCI (rac1) 5> add extract w2extdp, exttrailsource /gg/ggtarget/trails/w2, begin now
EXTRACT added.
修改配置:
GGSCI (rac1) 6> edit params w2extdp
EXTRACT w2extdp
SETENV (NLS_LANG=AMERICAN_AMERICA.AL32UTF8)
USERID goldengate@nimng1, PASSWORD goldengate
RMTHOST rac1, MGRPORT 5898
RMTTRAIL /gg/ggsource/trails/w2
DISCARDFILE w2extdpdsc,APPEND,MEGABYTES 5
TABLE hr.*;
GGSCI (rac1) 6> edit params w2extdp
EXTRACT w2extdp
SETENV (NLS_LANG=AMERICAN_AMERICA.AL32UTF8)
USERID goldengate@nimng1, PASSWORD goldengate
RMTHOST rac1, MGRPORT 5898
RMTTRAIL /gg/ggsource/trails/w2
DISCARDFILE w2extdpdsc,APPEND,MEGABYTES 5
TABLE hr.*;
增加RMTRAIL:
GGSCI (rac1) 7> add rmttrail /gg/ggsource/trails/w2, extract w2extdp, megabytes 100
RMTTRAIL added.
GGSCI (rac1) 7> add rmttrail /gg/ggsource/trails/w2, extract w2extdp, megabytes 100
RMTTRAIL added.
14. 增加目标端REP
GGSCI (rac1) 3> add replicat w2rep, exttrail /gg/ggsource/trails/w2, checkpointtable goldengate.chkpoint
REPLICAT added.
REPLICAT added.
修改配置:
GGSCI (rac1) 4> edit params w2rep
REPLICAT w2rep
ASSUMETARGETDEFS
SETENV (NLS_LANG=AMERICAN_AMERICA.AL32UTF8)
USERID goldengate@ninvoice1, PASSWORD goldengate
DISCARDFILE w2repdsc,APPEND,MEGABYTES 5
DDL INCLUDE MAPPED
DDLOPTIONS REPORT
BATCHSQL
DBOPTIONS DEFERREFCONST
DBOPTIONS LOBWRITESIZE 102400
HANDLECOLLISIONS
DDLERROR DEFAULT DISCARD RETRYOP MAXRETRIES 5 RETRYDELAY 20
tableexclude hr.mv*
MAP hr.* , TARGET hr.* ;
GGSCI (rac1) 4> edit params w2rep
REPLICAT w2rep
ASSUMETARGETDEFS
SETENV (NLS_LANG=AMERICAN_AMERICA.AL32UTF8)
USERID goldengate@ninvoice1, PASSWORD goldengate
DISCARDFILE w2repdsc,APPEND,MEGABYTES 5
DDL INCLUDE MAPPED
DDLOPTIONS REPORT
BATCHSQL
DBOPTIONS DEFERREFCONST
DBOPTIONS LOBWRITESIZE 102400
HANDLECOLLISIONS
DDLERROR DEFAULT DISCARD RETRYOP MAXRETRIES 5 RETRYDELAY 20
tableexclude hr.mv*
MAP hr.* , TARGET hr.* ;
17. 源端增加传输表。
GGSCI (rac1) 12> dblogin userid goldengate, password goldengate
Successfully logged into database.
GGSCI (rac1) 12> dblogin userid goldengate, password goldengate
Successfully logged into database.
GGSCI (rac1) 13> add trandata hr.*
Logging of supplemental redo data enabled for table HR.COUNTRIES.
Logging of supplemental redo data enabled for table HR.DEPARTMENTS.
Logging of supplemental redo data enabled for table HR.EMPLOYEES.
Logging of supplemental redo data enabled for table HR.JOBS.
Logging of supplemental redo data enabled for table HR.JOB_HISTORY.
Logging of supplemental redo data enabled for table HR.LOCATIONS.
2012-09-24 09:39:13 WARNING OGG-00869 No unique key is defined for table MLOG$_MV_T21. All viable columns will be used to represent the key, but may not guarantee uniqueness. KEYCOLS may be used to define the key.
Logging of supplemental redo data enabled for table HR.MLOG$_MV_T21.
Logging of supplemental redo data enabled for table HR.MV_T21.
Logging of supplemental redo data enabled for table HR.REGIONS.
2012-09-24 09:39:14 WARNING OGG-00869 No unique key is defined for table T. All viable columns will be used to represent the key, but may not guarantee uniqueness. KEYCOLS may be used to define the key.
Logging of supplemental redo data enabled for table HR.T.
2012-09-24 09:39:14 WARNING OGG-00869 No unique key is defined for table T1. All viable columns will be used to represent the key, but may not guarantee uniqueness. KEYCOLS may be used to define the key.
Logging of supplemental redo data enabled for table HR.T1.
2012-09-24 09:39:15 WARNING OGG-00869 No unique key is defined for table T2. All viable columns will be used to represent the key, but may not guarantee uniqueness. KEYCOLS may be used to define the key.
Logging of supplemental redo data enabled for table HR.T2.
Logging of supplemental redo data enabled for table HR.T21.
2012-09-24 09:39:15 WARNING OGG-00869 No unique key is defined for table T22. All viable columns will be used to represent the key, but may not guarantee uniqueness. KEYCOLS may be used to define the key.
Logging of supplemental redo data enabled for table HR.T22.
2012-09-24 09:39:15 WARNING OGG-00869 No unique key is defined for table T228. All viable columns will be used to represent the key, but may not guarantee uniqueness. KEYCOLS may be used to define the key.
Logging of supplemental redo data enabled for table HR.T228.
2012-09-24 09:39:16 WARNING OGG-00869 No unique key is defined for table T23. All viable columns will be used to represent the key, but may not guarantee uniqueness. KEYCOLS may be used to define the key.
Logging of supplemental redo data enabled for table HR.T23.
2012-09-24 09:39:16 WARNING OGG-00869 No unique key is defined for table T25. All viable columns will be used to represent the key, but may not guarantee uniqueness. KEYCOLS may be used to define the key.
Logging of supplemental redo data enabled for table HR.T25.
2012-09-24 09:39:16 WARNING OGG-00869 No unique key is defined for table T28. All viable columns will be used to represent the key, but may not guarantee uniqueness. KEYCOLS may be used to define the key.
Logging of supplemental redo data enabled for table HR.T28.
2012-09-24 09:39:16 WARNING OGG-00869 No unique key is defined for table T288. All viable columns will be used to represent the key, but may not guarantee uniqueness. KEYCOLS may be used to define the key.
Logging of supplemental redo data enabled for table HR.T288.
2012-09-24 09:39:16 WARNING OGG-00869 No unique key is defined for table T33. All viable columns will be used to represent the key, but may not guarantee uniqueness. KEYCOLS may be used to define the key.
Logging of supplemental redo data enabled for table HR.T33.
2012-09-24 09:39:17 WARNING OGG-00869 No unique key is defined for table T88. All viable columns will be used to represent the key, but may not guarantee uniqueness. KEYCOLS may be used to define the key.
Logging of supplemental redo data enabled for table HR.T88.
2012-09-24 09:39:17 WARNING OGG-00869 No unique key is defined for table TEST28. All viable columns will be used to represent the key, but may not guarantee uniqueness. KEYCOLS may be used to define the key.
Logging of supplemental redo data enabled for table HR.TEST28.
2012-09-24 09:39:17 WARNING OGG-00869 No unique key is defined for table TTT. All viable columns will be used to represent the key, but may not guarantee uniqueness. KEYCOLS may be used to define the key.
Logging of supplemental redo data enabled for table HR.TTT.
2012-09-24 09:39:17 WARNING OGG-00869 No unique key is defined for table TTTT. All viable columns will be used to represent the key, but may not guarantee uniqueness. KEYCOLS may be used to define the key..
Logging of supplemental redo data enabled for table HR.TTTT.
18. 查询源库SCN
SQL> select current_scn from v$database;
SQL> select current_scn from v$database;
CURRENT_SCN
-----------
4759254
-----------
4759254
19. 停止原GG进程, 重启所有的GOLDENGATE进程。
源端:
源端:
GGSCI (rac1) 17> info all
Program Status Group Lag Time Since Chkpt
MANAGER RUNNING
EXTRACT RUNNING W2EXT 00:00:00 00:00:02
EXTRACT RUNNING W2EXTDP 00:00:00 00:00:05
REPLICAT RUNNING W1REP 00:00:00 00:00:06
EXTRACT RUNNING W2EXT 00:00:00 00:00:02
EXTRACT RUNNING W2EXTDP 00:00:00 00:00:05
REPLICAT RUNNING W1REP 00:00:00 00:00:06
目标端:
GGSCI (rac1) 23> info all
GGSCI (rac1) 23> info all
Program Status Group Lag Time Since Chkpt
MANAGER RUNNING
EXTRACT RUNNING W1EXT 00:00:00 00:00:03
EXTRACT RUNNING W1EXTDP 00:00:00 00:00:05
REPLICAT RUNNING W2REP 00:00:00 00:00:06
EXTRACT RUNNING W1EXT 00:00:00 00:00:03
EXTRACT RUNNING W1EXTDP 00:00:00 00:00:05
REPLICAT RUNNING W2REP 00:00:00 00:00:06
20 数据测试:
原环境:
源端:
原环境:
源端:
SQL> create table huang(id number(9));
Table created.
SQL> insert into huang(id) values(88);
1 row created.
SQL> commit;
Commit complete.
目标端:
SQL> select * from huang;
SQL> select * from huang;
ID
----------
88
----------
88
后增加:
SQL> create table liu(id number(9));
SQL> create table liu(id number(9));
Table created.
SQL> insert into liu(id) values(88);
1 row created.
SQL> commit;
Commit complete.
目标端:
SQL> select * from liu;
ID
----------
88
----------
88
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/22740983/viewspace-745036/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/22740983/viewspace-745036/