非同期AutoLog CDC構成
CDCには同期モードと非同期モード1がある.CDC同期モード:triggerで実現する.2.CDC非同期モード:2.1非同期HotLogモード2.2非同期分散HotLogモード2.3非同期AutoLogモード.2.3.1非同期Autologモード-online redo log 2.3.2非同期Autologモード-archive log非同期Autologモード(Asynchronous AutoLog Mode)は、まずsource databaseからstaging databaseにログファイルを渡し、staging databaseでログ解析を実行します.これにより、source databaseに対する圧力を最小限に抑えることができる.ログの転送はRedo transportサービスで実現されますが、このサービス名に詳しいのではないでしょうか.Data Guardでもこのサービスによってメインライブラリのログがバックアップに渡されますが、実際にはログの処理とData Guardでは基本的に同じメカニズムです.ここでもsource databaseに対応するLOG_を設定する必要がありますARCHIVE_DEST_nパラメータはログの伝達を実現する.非同期AutoLogモードでは、オンライン・ログを使用するか、アーカイブ・ログを使用して増分データ情報を取得できます.AUTOLOG CDCはOracle Streams downstream captureを使用して非同期autologパブリケーションを実施し、非同期Autolog online redo logはStreams real-time downstream capture、非同期Autolog archive logはStreams archived-log downstreams captureを使用します.非同期Autolog archive logモードの実装
また、購読者はDBMS_を使用することができるCDC_SUBSCRIBE.PURGE_WINDOWプロセスpurgeに不要なデータは、購読者がDBMS_を使用できます.CDC_SUBSCRIBE.DROP_SUBSCRIPTION procedureは購読を停止します.
source側とtarget側の初期化パラメータの具体的な構成はoracle公式ドキュメントを参照することができ、target側はjavaの構成に注意しなければならない.pool_sizeとstreams_pool_size.
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
取得プロセスと適用プロセスのステータスとエラー情報の表示
AutoLog online構成の説明
AutoLog archive構成とは異なるパラメータ
注意:AutoLog onlineモードでは、1つの中間データベースに1つの変更ソースしかありません.変更セットは1つしか含まれません.
--1. source
[oracle@source ~]$ slqlplus / as sysdba
SQL> show parameter undo_retention;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
undo_retention integer 900
SQL> alter system set undo_retention=3600 scope=BOTH;
System altered.
SQL> alter system set log_archive_dest_1 ="location=/home/oracle/archive mandatory reopen=2"; scope=both;
System altered.
SQL> alter system set log_archive_dest_2 = "service=targdb arch optional noregister reopen=2 template=/home/oracle/archive2/arch_%s_%t_%r.arc" scope=both;
System altered.
SQL> alter system set log_archive_dest_state_1=enable scope=both;
System altered.
SQL> alter system set log_archive_dest_state_2=enable scope=both;
System altered.
SQL> alter system set log_archive_format='arch_%s_%t_%r.arc' scope=spfile;
System altered.
SQL> show parameter global_names
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
global_names boolean FALSE
SQL> alter system set global_names=TRUE scope=BOTH;
System altered.
SQL> alter system set remote_login_passwordfile=shared
2 scope =spfile;
System altered.
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.
Total System Global Area 276824064 bytes
Fixed Size 2020160 bytes
Variable Size 92277952 bytes
Database Buffers 180355072 bytes
Redo Buffers 2170880 bytes
Database mounted.
SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /home/oracle/archive
Oldest online log sequence 1
Next log sequence to archive 2
Current log sequence 2
--2. target
[oracle@target ~]$ sqlplus / as sysdba
SQL> alter system set global_names=TRUE scope=BOTH;
System altered.
SQL> alter system set undo_retention=3600 scope=BOTH;
System altered.
SQL> show parameter remote_archive_enable
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
remote_archive_enable string true
SQL> alter system set log_archive_dest_1="location=/home/oracle/archive" scope=both;
System altered.
SQL> alter system set remote_login_passwordfile=shared scope =spfile;
System altered.
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.
Total System Global Area 276824064 bytes
Fixed Size 2020160 bytes
Variable Size 92277952 bytes
Database Buffers 180355072 bytes
Redo Buffers 2170880 bytes
Database mounted.
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /home/oracle/archive
Oldest online log sequence 1
Next log sequence to archive 2
Current log sequence 2
SQL> alter database open;
Database altered.
SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /home/oracle/archive
Oldest online log sequence 1
Next log sequence to archive 2
Current log sequence 2
SQL> alter system switch logfile;
System altered.
SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /home/oracle/archive
Oldest online log sequence 1
Next log sequence to archive 2
Current log sequence 3
--3.source FORCE LOGGING SUPPLEMENTAL LOG
SQL> alter database force logging;
Database altered.
SQL> alter database add supplemental log data;
Database altered.
SQL> alter database open;
Database altered.
SQL> alter system switch logfile;
System altered.
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /home/oracle/archive
Oldest online log sequence 1
Next log sequence to archive 2
Current log sequence 3
SQL> col log_min format a7
SQL> col log_pk format a6
SQL> col log_pk format a6
SQL> col log_ui format a6
SQL> col log_fk format a6
SQL> col log_all format a7
SQL> col force_log format a9
SQL> SELECT supplemental_log_data_min LOG_MIN, supplemental_log_data_pk LOG_PK, supplemental_log_data_ui LOG_UI, supplemental_log_data_fk LOG_FK,
supplemental_log_data_all LOG_ALL, force_logging FORCE_LOG
FROM v$database;
LOG_MIN LOG_PK LOG_UI LOG_FK LOG_ALL FORCE_LOG
------- ------ ------ ------ ------- ---------
YESNO NO NO NO YES
--4.target ，
QL> create tablespace ts_cdcpub
2 datafile '/home/oracle/oradata/targ/ts_cdcpub01.dbf' size 100M;
Tablespace created.
SQL> CREATE USER cdcpub IDENTIFIED BY cdcpub DEFAULT TABLESPACE ts_cdcpub
QUOTA UNLIMITED ON SYSTEM
QUOTA UNLIMITED ON SYSAUX; 2 3
User created.
SQL> GRANT CREATE SESSION TO cdcpub;
Grant succeeded.
SQL> GRANT CREATE TABLE TO cdcpub;
Grant succeeded.
SQL> GRANT CREATE TABLESPACE TO cdcpub;
Grant succeeded.
SQL> GRANT UNLIMITED TABLESPACE TO cdcpub;
Grant succeeded.
SQL> GRANT SELECT_CATALOG_ROLE TO cdcpub;
Grant succeeded.
SQL> GRANT EXECUTE_CATALOG_ROLE TO cdcpub;
Grant succeeded.
SQL> GRANT DBA TO cdcpub;
Grant succeeded.
SQL> GRANT CREATE SEQUENCE TO cdcpub;
Grant succeeded.
SQL> GRANT EXECUTE on DBMS_CDC_PUBLISH TO cdcpub;
Grant succeeded.
SQL> EXECUTE DBMS_STREAMS_AUTH.GRANT_ADMIN_PRIVILEGE(grantee => 'cdcpub');
PL/SQL procedure successfully completed.
--5.source LogMiner ， 6
SQL> SET SERVEROUTPUT ON
VARIABLE f_scn NUMBER;
BEGIN
:f_scn := 0;
DBMS_CAPTURE_ADM.BUILD(:f_scn);
DBMS_OUTPUT.PUT_LINE('The first_scn value is ' || :f_scn);
END;
/SQL> SQL> 2 3 4 5 6
The first_scn value is 544891
PL/SQL procedure successfully completed.
--6.source
SQL> SQL> desc scott.e dept
Name Null? Type
----------------------------------------- -------- ----------------------------
DEPTNO NOT NULL NUMBER(2)
DNAME VARCHAR2(14)
LOC VARCHAR2(13)
SQL> BEGIN
DBMS_CAPTURE_ADM.PREPARE_TABLE_INSTANTIATION(
TABLE_NAME => 'scott.dept');
END;
/ 2 3 4 5
PL/SQL procedure successfully completed.
--7.source ， source global_name, target AutoLog 。
SQL> SELECT GLOBAL_NAME FROM GLOBAL_NAME;
GLOBAL_NAME
--------------------------------------------------------------------------------
SOUR.REGRESS.RDBMS.DEV.US.ORACLE.COM
--8.target ，
SQL> BEGIN
DBMS_CDC_PUBLISH.CREATE_AUTOLOG_CHANGE_SOURCE(
change_source_name => 'CHICAGO',
description => 'test source',
source_database => 'SOUR.REGRESS.RDBMS.DEV.US.ORACLE.COM',
first_scn => 544891);
END;
/ 2 3 4 5 6 7 8
PL/SQL procedure successfully completed.
--9.target ，
SQL> BEGIN
DBMS_CDC_PUBLISH.CREATE_CHANGE_SET(
change_set_name => 'CHICAGO_DAILY',
description => 'change set for dept info',
change_source_name => 'CHICAGO',
stop_on_ddl => 'y');
END;
/ 2 3 4 5 6 7 8
PL/SQL procedure successfully completed.
--10.target ，
SQL> BEGIN
2 DBMS_CDC_PUBLISH.CREATE_CHANGE_TABLE(
owner => 'cdcpub',
change_table_name => 'dept_ct',
3 change_set_name => 'CHICAGO_DAILY',
source_schema => 'SCOTT',
4 source_table => 'DEPT',
column_type_list => 'DEPTNO NUMBER(2), DNAME VARCHAR2(14), LOC VARCHAR2(13)',
capture_values => 'both',
rs_id => 'y',
row_id => 'n',
5 user_id => 'n',
6 timestamp => 'n',
object_id => 'n',
7 source_colmap => 'n',
target_colmap => 'y',
options_string => 'TABLESPACE ts_cdcpub');
8 END;
/
9 10 11 12 13 14 15 16 17 18 19
PL/SQL procedure successfully completed.
--11.target ， ， oracle
SQL> SQL> BEGIN
DBMS_CDC_PUBLISH.ALTER_CHANGE_SET(
change_set_name => 'CHICAGO_DAILY',
enable_capture => 'y');
END;
/ 2 3 4 5 6
PL/SQL procedure successfully completed.
--12.source ， ，oracle 。
SQL> alter system switch logfile;
System altered.
--13.target ，
SQL> create user subs identified by subs default tablespace users;
User created.
SQL> grant connect,resource to subs;
Grant succeeded.
SQL> grant select on cdcpub.dept_ct to subs;
Grant succeeded.
------------------------------------------------------------------------------------------------------------------------
（target ）,
--1.
SQL> SELECT * FROM ALL_SOURCE_TABLES;
SOURCE_SCHEMA_NAME SOURCE_TABLE_NAME
------------------------------ ------------------------------
SCOTT DEPT
--2.
SQL> SELECT UNIQUE CHANGE_SET_NAME, COLUMN_NAME, PUB_ID
FROM ALL_PUBLISHED_COLUMNS
WHERE SOURCE_SCHEMA_NAME ='SCOTT' AND SOURCE_TABLE_NAME = 'DEPT';
2 3
CHANGE_SET_NAME COLUMN_NAME PUB_ID
------------------------------ ------------------------------ ----------
CHICAGO_DAILY DEPTNO 51401
CHICAGO_DAILY DNAME 51401
CHICAGO_DAILY LOC 51401
--3.
SQL> BEGIN
DBMS_CDC_SUBSCRIBE.CREATE_SUBSCRIPTION(
change_set_name => 'CHICAGO_DAILY',
description => 'Change data for DEPT',
subscription_name => 'DEPT_SUB');
END;
/
2 3 4 5 6 7
PL/SQL procedure successfully completed.
--4.
SQL> BEGIN
DBMS_CDC_SUBSCRIBE.SUBSCRIBE(
subscription_name => 'DEPT_SUB',
source_schema => 'SCOTT',
source_table => 'DEPT',
column_list => 'DEPTNO , DNAME, LOC',
subscriber_view => 'DEPT_VIEW');
END;
/ 2 3 4 5 6 7 8 9
PL/SQL procedure successfully completed.
--5.
SQL> BEGIN
DBMS_CDC_SUBSCRIBE.ACTIVATE_SUBSCRIPTION(
subscription_name => 'DEPT_SUB');
END;
/ 2 3 4 5
PL/SQL procedure successfully completed.
------------------------------------------------------------------------------------------------------------------------
--source
SQL> insert into scott.dept values(51,'hello',wo 'world');
1 row created.
SQL> commit;
Commit complete.
SQL> alter system switch logfile;
System altered.
--target ，
SQL> BEGIN
DBMS_CDC_SUBSCRIBE.EXTEND_WINDOW(
subscription_name => 'DEPT_SUB');
END;
/ 2 3 4 5
PL/SQL procedure successfully completed.
SQL> SELECT DEPTNO,DNAME,LOC FROM DEPT_VIEW;
DEPTNO DNAME LOC
---------- -------------- -------------
51 hello worldまた、購読者はDBMS_を使用することができるCDC_SUBSCRIBE.PURGE_WINDOWプロセスpurgeに不要なデータは、購読者がDBMS_を使用できます.CDC_SUBSCRIBE.DROP_SUBSCRIPTION procedureは購読を停止します.
source側とtarget側の初期化パラメータの具体的な構成はoracle公式ドキュメントを参照することができ、target側はjavaの構成に注意しなければならない.pool_sizeとstreams_pool_size.
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
取得プロセスと適用プロセスのステータスとエラー情報の表示
--
SQL> desc dba_capture
Name Null? Type
----------------------------------------- -------- ----------------------------
CAPTURE_NAME NOT NULL VARCHAR2(30)
QUEUE_NAME NOT NULL VARCHAR2(30)
QUEUE_OWNER NOT NULL VARCHAR2(30)
RULE_SET_NAME VARCHAR2(30)
RULE_SET_OWNER VARCHAR2(30)
CAPTURE_USER VARCHAR2(30)
START_SCN NUMBER
STATUS VARCHAR2(8)
CAPTURED_SCN NUMBER
APPLIED_SCN NUMBER
USE_DATABASE_LINK VARCHAR2(3)
FIRST_SCN NUMBER
SOURCE_DATABASE VARCHAR2(128)
SOURCE_DBID NUMBER
SOURCE_RESETLOGS_SCN NUMBER
SOURCE_RESETLOGS_TIME NUMBER
LOGMINER_ID NUMBER
NEGATIVE_RULE_SET_NAME VARCHAR2(30)
NEGATIVE_RULE_SET_OWNER VARCHAR2(30)
MAX_CHECKPOINT_SCN NUMBER
REQUIRED_CHECKPOINT_SCN NUMBER
LOGFILE_ASSIGNMENT VARCHAR2(8)
STATUS_CHANGE_TIME DATE
ERROR_NUMBER NUMBER
ERROR_MESSAGE VARCHAR2(4000)
VERSION VARCHAR2(64)
CAPTURE_TYPE VARCHAR2(10)
LAST_ENQUEUED_SCN NUMBER
CHECKPOINT_RETENTION_TIME NUMBER
SQL> SELECT CAPTURE_NAME,status, CAPTURE_TYPE from dba_capture;
CAPTURE_NAME STATUS CAPTURE_TY
------------------------------ -------- ----------
CDC$C_CHICAGO_DAILY ENABLED DOWNSTREAM
SQL> desc dba_apply;
Name Null? Type
----------------------------------------- -------- ----------------------------
APPLY_NAME NOT NULL VARCHAR2(30)
QUEUE_NAME NOT NULL VARCHAR2(30)
QUEUE_OWNER NOT NULL VARCHAR2(30)
APPLY_CAPTURED VARCHAR2(3)
RULE_SET_NAME VARCHAR2(30)
RULE_SET_OWNER VARCHAR2(30)
APPLY_USER VARCHAR2(30)
APPLY_DATABASE_LINK VARCHAR2(128)
APPLY_TAG RAW(2000)
DDL_HANDLER VARCHAR2(98)
PRECOMMIT_HANDLER VARCHAR2(98)
MESSAGE_HANDLER VARCHAR2(98)
STATUS VARCHAR2(8)
MAX_APPLIED_MESSAGE_NUMBER NUMBER
NEGATIVE_RULE_SET_NAME VARCHAR2(30)
NEGATIVE_RULE_SET_OWNER VARCHAR2(30)
STATUS_CHANGE_TIME DATE
ERROR_NUMBER NUMBER
ERROR_MESSAGE VARCHAR2(4000)
SQL> select apply_name,status from dba_apply;
APPLY_NAME STATUS
------------------------------ --------
CDC$A_CHICAGO_DAILY ENABLEDAutoLog online構成の説明
AutoLog archive構成とは異なるパラメータ
--source
log_archive_dest_1 ="location=/home/oracle/archive mandatory reopen=2"
log_archive_dest_2 ="service=targdb lgwr async optional noregister reopen=2
valid_for=(online_logfile,primary_role)"
--target
log_archive_dest_1="location=/home/oracle/archive mandatory reopen=2
valid_for=(online_logfile,primary_role)"
log_archive_dest_2="location=/home/oracle/stdby_arch mandatory
valid_for=(standby_logfile,primary_role)"
--target Standby Redo Log
ALTER DATABASE ADD STANDBY LOGFILE GROUP 4
('/home/oracle/oradata/targ/stdbylogs/slog4a.rdo') SIZE 50M;
ALTER DATABASE ADD STANDBY LOGFILE GROUP 5
('/home/oracle/oradata/targ/stdbylogs/slog5a.rdo') SIZE 50M;
ALTER DATABASE ADD STANDBY LOGFILE GROUP 6
('/home/oracle/oradata/targ/stdbylogs/slog6a.rdo') SIZE 50M;
ALTER DATABASE ADD STANDBY LOGFILE GROUP 7
('/home/oracle/oradata/targ/stdbylogs/slog7a.rdo') SIZE 50M;
--target
BEGIN
DBMS_CDC_PUBLISH.CREATE_AUTOLOG_CHANGE_SOURCE(
change_source_name => 'CHICAGO',
description => 'test source',
source_database => 'SOUR.REGRESS.RDBMS.DEV.US.ORACLE.COM',
first_scn => 582857,
online_log => 'y'); -- online_log=>'y' AutoLog online
END;
/注意:AutoLog onlineモードでは、1つの中間データベースに1つの変更ソースしかありません.変更セットは1つしか含まれません.