Oracle GoldenGate 12.3.0.1.4 in Oracle 12.2 之04 ( Integrated Capture and Apply )

前提条件: 完成 Oracle GoldenGate 12.3.0.1.4 in Oracle 12.2 之03(预安装) 中的步骤1到9 配置

1.管理服务器资源(xag130,xag131)

#默认情况下,一个集成的捕获摘录请求日志挖掘服务器以MAX_SGA_SIZE1GB 的速度运行。
#因此,如果在同一数据库实例中以集成捕获模式运行三个提取,则需要至少3 GB的内存分配给Streams池。
#最佳做法是保持25%的流池可用。例如,如果在集成捕获模式下有3个摘录,请将STREAMS_POOL_SIZE数据库设置为以下值:
# 3 GB * 1.25 = 3.75 GB

--SQL> alter system set streams_pool_size=3.75 GB

#本次测试用2个提取,每个 MAX_SGA_SIZE= 200M 来测试故  即 TRANLOGOPTIONS INTEGRATEDPARAMS (max_sga_size 200, parallelism 2)
[oracle@xag130 ~]$ sqlplus / as sysdba

SQL> alter system set streams_pool_size=500M scope=both;

SQL> show parameter streams_pool_size;

NAME				     TYPE	 VALUE
------------------------------------ ----------- ------------------------------
streams_pool_size		     big integer 500

2.源端配置Extract进程参数(xag130)

[oracle@xag130 ~]$ cd $OGG_HOME
[oracle@xag130 ogg]$ ggsci
GGSCI (xag130) > DBLOGIN USERID c##ogg_admin, PASSWORD 123456

GGSCI > ADD EXTRACT EX002, INTEGRATED TRANLOG,BEGIN now 

GGSCI > add exttrail /u01/app/oracle/product/ogg/dirdat/t2, extract EX002


GGSCI (xag130) 1> EDIT PARAMS EX002

GGSCI (xag130) 2> VIEW PARAMS EX002
----------------------------------------------------------------------------------
EXTRACT EX002
USERID c##ogg_admin@CDB1, PASSWORD 123456

--加入前镜像(如果ogg12c前的版本,用GETUPDATEBEFORES ) 
LOGALLSUPCOLS 

--extract把前后镜像混合到trail文件中去(ogg12c的参数)
UPDATERECORDFORMAT COMPACT 

TRANLOGOPTIONS INTEGRATEDPARAMS (max_sga_size 200, parallelism 2)
EXTTRAIL /u01/app/oracle/product/ogg/dirdat/t2 
SOURCECATALOG PDB1
TABLE XAG.MY_HEALT_CHECK;
SOURCECATALOG PDB1
TABLE XAG.MY_HEALT_CHECK;
----------------------------------------------------------------------------------

#创建  data pump parameter
GGSCI > ADD EXTRACT DP002 EXTTRAILSOURCE /u01/app/oracle/product/ogg/dirdat/t2
GGSCI >	ADD RMTTRAIL /u01/app/oracle/product/ogg/dirdat/r2, EXTRACT DP002
   

GGSCI (xag130) 4> EDIT PARAMS DP002

GGSCI (xag130) 5> VIEW PARAMS DP002
----------------------------------------------------------------------------------
EXTRACT DP002
USERID c##ogg_admin@CDB1, PASSWORD 123456
RMTHOST xag131, MGRPORT 7809
RMTTRAIL /u01/app/oracle/product/ogg/dirdat/r2 

--表示传输进程直接跟抽取进程交互,而不再和数据库进行交互,减少数据库资源的利用
PASSTHRU 
SOURCECATALOG PDB1
TABLE XAG.MY_HEALT_CHECK;
SOURCECATALOG PDB1
TABLE XAG.MY_HEALT_CHECK;
----------------------------------------------------------------------------------

3.注册 extract

GGSCI (xag130) 21> DBLOGIN USERID c##ogg_admin, PASSWORD 123456

GGSCI (xag130 as c##ogg_admin@cdb1/CDB$ROOT) 22> register extract EX002 database container(PDB1)

4.创建integrated replicat RT002

GGSCI> DBLOGIN USERID USERID ogg_admin@xag131:1521/pdb1, PASSWORD 123456   --?

GGSCI> ADD REPLICAT RT002 INTEGRATED EXTTRAIL /u01/app/oracle/product/ogg/dirdat/r2, NODBCHECKPOINT

GGSCI (xag131) 3> EDIT PARAMS RT002

GGSCI (xag131) 4> VIEW PARAMS RT002
----------------------------------------------------------------------------------
REPLICAT RT002 
SETENV (ORACLE_SID=‘CDB1‘) 
DBOPTIONS INTEGRATEDPARAMS(parallelism 6) 
USERID ogg_admin@xag131:1521/pdb1, PASSWORD 123456
--源和目标端 结构一致
ASSUMETARGETDEFS 

MAP PDB1.XAG.MY_HEALT_CHECK, TARGET PDB1.XAG.MY_HEALT_CHECK;
----------------------------------------------------------------------------------

5.初始数据导入

[oracle@xag130 ~]$ sql xag/123456@xag130:1521/pdb1

SQL>SET SQLFORMAT ansiconsole

SQL> select current_scn from v$database;
CURRENT_SCN  
1758144

#仅仅导出 my_healt_check 表的数据
[oracle@xag130 ~]$ expdp xag/123456@xag130:1521/pdb1 directory=mydir dumpfile=xag2.dmp logfile=xag2.log tables=xag.my_healt_check content=data_only flashback_scn=1758144;

#导入dmp文件中的所有数据,例:
[oracle@xag131 ~]$ impdp xag/123456@xag131:1521/pdb1 directory=mydir dumpfile=xag2.dmp logfile=xag2.log full=yes

6.Start the Manager, Extract, and Replicat, and Test the Result

[oracle@xag131 ~]$ cd $OGG_HOME

[oracle@xag131 ogg]$ ggsci

GGSCI (xag131) 9> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     STOPPED                                           
REPLICAT    STOPPED     RT001       00:00:00      10:04:21    
REPLICAT    STOPPED     RT002       00:00:00      00:08:29 

GGSCI (xag131) 5> START MGR

#首次因要约初始数据衔接需手工加 aftercsn 启动
GGSCI (xag131) 6> start replicat RT002,aftercsn 1758144

GGSCI (xag131) 12> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING                                           
REPLICAT    STOPPED     RT001       00:00:00      10:05:38    
REPLICAT    RUNNING     RT002       00:00:00      00:00:07  


#主机
[oracle@xag130 ~]$ cd $OGG_HOME

[oracle@xag130 ogg]$ ggsci

GGSCI (xag130) 5> START MGR

GGSCI (xag130) 39> start extract EX002

GGSCI (xag130) 16> start extract DP002

GGSCI (xag130) 17> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING                                           
EXTRACT     RUNNING     DP002       00:00:00      00:00:03    
EXTRACT     STOPPED     EX001       00:00:00      00:05:47    
EXTRACT     RUNNING     EX002       00:00:00      00:00:03  

7.监控 捕获

# Monitoring an Integrated Capture
[oracle@xag130 ogg]$ sql / as sysdba

SQL> SET SQLFORMAT ansiconsole

#监控 捕获过程
SQL> SELECT CAPTURE_NAME, QUEUE_NAME, START_SCN, STATUS, 
     CAPTURED_SCN, APPLIED_SCN, SOURCE_DATABASE, 
     LOGMINER_ID, REQUIRED_CHECKPOINT_SCN, 
     STATUS_CHANGE_TIME, ERROR_NUMBER, ERROR_MESSAGE, 
     CAPTURE_TYPE, START_TIME 
     FROM DBA_CAPTURE;
CAPTURE_NAME   QUEUE_NAME   START_SCN  STATUS    CAPTURED_SCN  APPLIED_SCN  SOURCE_DATABASE  LOGMINER_ID  REQUIRED_CHECKPOINT_SCN  STATUS_CHANGE_TIME  ERROR_NUMBER  ERROR_MESSAGE  CAPTURE_TYPE  START_TIME                       
OGG$CAP_EX001  OGG$Q_EX001  1608327    DISABLED  1764260       1764252      PDB1             1            1764252                  28-JUN-20                                        LOCAL         28-JUN-20 09.55.30.000000000 AM  
OGG$CAP_EX002  OGG$Q_EX002  1725414    ENABLED   1773544       1773537      PDB1             2            1773537                  28-JUN-20                                        LOCAL         28-JUN-20 09.24.10.000000000 PM  

#通过 dynamic statistics 查询捕获过程
SQL> SELECT sid, serial#, capture#, CAPTURE_NAME, STARTUP_TIME, CAPTURE_TIME, 
     state, SGA_USED, BYTES_OF_REDO_MINED, 
     to_char(STATE_CHANGED_TIME, ‘yyyy-mm-dd hh24:mi‘) STATE_CHANGED_TIME 
     FROM V$GOLDENGATE_CAPTURE;
SID  SERIAL#  CAPTURE#  CAPTURE_NAME   STARTUP_TIME  CAPTURE_TIME  STATE                    SGA_USED   BYTES_OF_REDO_MINED  STATE_CHANGED_TIME  
762  62098    2         OGG$CAP_EX002  28-JUN-20     28-JUN-20     WAITING FOR TRANSACTION  126542744  208398828            2020-06-28 22:27    

#查询捕获消息的创建时间及其编号的其他详细信息
SQL> SELECT capture_name, 
     to_char(capture_time, ‘yyyy-mm-dd hh24:mi‘) capture_time, 
     capture_message_number, 
     to_char(capture_message_create_time ,‘yyyy-mm-dd hh24:mi‘) capture_message_create_time, 
     to_char(enqueue_time,‘yyyy-mm-dd hh24:mi‘) enqueue_time, 
     enqueue_message_number, to_char(enqueue_message_create_time, ‘yyyy-mm-dd hh24:mi‘) enqueue_message_create_time, 
     available_message_number, 
     to_char(available_message_create_time,‘yyyy-mm-dd hh24:mi‘) available_message_create_time 
     FROM GV$GOLDENGATE_CAPTURE;
CAPTURE_NAME   CAPTURE_TIME      CAPTURE_MESSAGE_NUMBER  CAPTURE_MESSAGE_CREATE_TIME  ENQUEUE_TIME      ENQUEUE_MESSAGE_NUMBER  ENQUEUE_MESSAGE_CREATE_TIME  AVAILABLE_MESSAGE_NUMBER  AVAILABLE_MESSAGE_CREATE_TIME  
OGG$CAP_EX002  2020-06-28 22:28  1773871                 2020-06-28 22:28             2020-06-28 22:28  1773871                 2020-06-28 22:28             1773878                   2020-06-28 22:28               

#查询监视每个捕获进程的开放事务和LCR的数量
SQL>  SELECT component_name capture_name, count(*) open_transactions, 
     sum(cumulative_message_count) LCRs 
     FROM GV$GOLDENGATE_TRANSACTION 
     WHERE component_type=‘CAPTURE‘ 
     group by component_name;

no rows selected

#查询Log Miner会话和统计的Log Miner视图
SQL> select INST_ID, SESSION_ID,SESSION_NAME,SESSION_STATE, DB_NAME, 
     NUM_PROCESS,START_SCN,END_SCN,SPILL_SCN, PROCESSED_SCN, PREPARED_SCN, 
     READ_SCN MAX_MEMORY_SIZE,USED_MEMORY_SIZE PINNED_TXN, PINNED_COMMITTED_TXN 
     from GV$LOGMNR_SESSION; 
INST_ID  SESSION_ID  SESSION_NAME   SESSION_STATE  DB_NAME  NUM_PROCESS  START_SCN  END_SCN  SPILL_SCN  PROCESSED_SCN  PREPARED_SCN  MAX_MEMORY_SIZE  PINNED_TXN  PINNED_COMMITTED_TXN  
1        2           OGG$CAP_EX002  ACTIVE         CDB1     4            0          0        1725145    1773993        1773977       1773993          126545896   0                     


SQL>  SELECT SESSION_ID, NAME, VALUE FROM V$LOGMNR_STATS;
SESSION_ID  NAME                                     VALUE                
2           session start time                       06/28/2020 22:06:26  
2           session restart SCN                      1725145              
2           txns delivered to client                 20089                
2           DML txns delivered                       4029                 
2           DDL txns delivered                       530                  
2           CTAS txns delivered                      77                   
2           recursive txns delivered                 15501                
2           rolled back txns mined                   136                  
2           DDL txns mined                           478                  
2           CTAS txns mined                          77                   
2           LCRs delivered to client                 50389                
2           bytes of redo mined                      208429160            
2           bytes paged out                          0                    
2           pageout time (seconds)                   0                    
2           bytes checkpointed                       0                    
2           checkpoint time (seconds)                0                    
2           system idle time (seconds)               0                    
2           standby redo logs mined                  1                    
2           archived logs mined                      1                    
2           gap fetched logs mined                   0                    
2           standby redo log reuse detected          0                    
2           logfile open failures                    0                    
2           current logfile wait (seconds)           0                    
2           total logfile wait (seconds)             0                    
2           thread enable mined                      0                    
2           thread disable mined                     0                    
2           parallel DML mined                       0                    
2           direct path insert LCR                   120                  
2           distinct txns in queue                   0                    
2           number of logged PLSQL procedures mined  0       

8.停OGG

#停源端
GGSCI (xag130) 6> stop extract DP002

GGSCI (xag130) 7> stop extract EX002

GGSCI (xag130) 8> stop mgr


#停目标端
GGSCI (xag131) 5> stop replicat RT002

GGSCI (xag131) 6> stop mgr

Oracle GoldenGate 12.3.0.1.4 in Oracle 12.2 之04 ( Integrated Capture and Apply )

上一篇:Android 开发学习进程0.17 Android资源文件selector textview显示两种不同字体


下一篇:AndroidGetAPKInfo --- 检查包名(packageName)、版本(versionName\versionCode)、应用签名(Signature)等信息