CDC异步HotLog的简单测试。

最新推荐文章于 2021-05-29 19:42:35 发布

iteye_8899

最新推荐文章于 2021-05-29 19:42:35 发布

阅读量161

点赞数

文章标签：数据库

SQL> show parameter stream

streams_pool_size big integer 0
SQL> alter system set streams_pool_size =100m;

System altered.

SQL> alter database force logging;

Database altered.

SQL> alter database add supplemental log data;

Database altered.

SQL> alter table scott.dept
2 add supplemental log group log_group_dept
3 (deptno,dname,loc);

Table altered.

SQL> begin
2 dbms_capture_adm.prepare_table_instantiation(
3 table_name=>'scott.dept');
4 end;
5 /

PL/SQL procedure successfully completed.

SQL> begin
2 dbms_cdc_publish.create_change_set(
3 change_set_name=>'dept_cs',
4 description=>'hotlog change set for scott.dept',
5 change_source_name=>'HOTLOG_SOURCE',
6 stop_on_ddl=>'y',
7 begin_date=>sysdate,
8 end_date=>sysdate+1);
9 end;
10 /
begin
*
ERROR at line 1:
ORA-00258: manual archiving in NOARCHIVELOG mode must identify log
ORA-06512: at "SYS.DBMS_CAPTURE_ADM_INTERNAL", line 121
ORA-06512: at line 1
ORA-06512: at "SYS.DBMS_CDC_PUBLISH", line 560
ORA-06512: at line 2
SQL> shutdown immediate

Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> SQL> SQL> startup mount
ORACLE instance started.

Total System Global Area 1224736768 bytes
Fixed Size 1279916 bytes
Variable Size 469764180 bytes
Database Buffers 738197504 bytes
Redo Buffers 15495168 bytes
Database mounted.

SQL> alter database archivelog;

Database altered.

SQL> alter database open;

Database altered.

PL/SQL procedure successfully completed.
SQL> begin
2 dbms_cdc_publish.create_change_table(
3 owner =>'cdcpub',
4 change_table_name=>'dept_ct',
5 change_set_name=>'dept_cs',
6 source_schema=>'scott',
7 source_table=>'dept',
8 column_type_list=>'deptno number(2),DNAME VARCHAR2(14),LOC VARCHAR2(13)',
9 capture_values=>'both',
10 rs_id=>'y',
11 row_id=>'n',
12 user_id=>'n',
13 timestamp=>'n',
14 object_id=>'n',
15 source_colmap=>'n',
16 target_colmap=>'y',
17 options_string=>'tablespace users');
18 end;
19 /

PL/SQL procedure successfully completed.

SQL> begin
2 dbms_cdc_publish.alter_change_set(
3 change_set_name=>'dept_cs',
4 enable_capture=>'y');
5 end;
6 /

PL/SQL procedure successfully completed.

SQL> select count(*) from cdcpub.dept_ct;

SQL> grant select on cdcpub.dept_ct to cdcsub;

Grant succeeded.

SQL>
SQL> conn cdcsub/cdcsub;
Connected.
SQL> select * from all_source_tables;

SCOTT EMP
SCOTT DEPT

SQL> begin
2 dbms_cdc_subscribe.create_subscription(
3 change_set_name=>'dept_cs',
4 description=>'change data for dept',
5 subscription_name=>'dept_sub');
6 end;
7 /

PL/SQL procedure successfully completed.

SQL> begin
2 dbms_cdc_subscribe.subscribe(
3 subscription_name=>'dept_sub',
4 source_schema=>'SCOTT',
5 source_table=>'DEPT',
6 column_list=>'deptno,dname,loc',
7 subscriber_view=>'dept_view');
8 end;
9 /

PL/SQL procedure successfully completed.

SQL> begin
2 dbms_cdc_subscribe.activate_subscription(
3 subscription_name=>'DEPT_SUB');
4 end;
5 /

PL/SQL procedure successfully completed.

SQL> conn scott/oracle
Connected.
SQL> insert into dept values(50,'SMO','BJ');

1 row created.

SQL> commit;

Commit complete.

SQL> conn cdcsub/cdcsub
Connected.
SQL> select * from dept_view;

no rows selected

SQL> begin
2 dbms_cdc_subscribe.extend_window(
3 subscription_name=>'DEPT_SUB');
4 end;
5 /

PL/SQL procedure successfully completed.

SQL> select * from dept_view;

I 8.3731E+12 25-FEB-09 7 9 1115 1
FE0F0000000000000000000000000000000000000000000000000000000000000000000000000000
00000000000000000000000000000000000000000000000000000000000000000000000000000000
00000000000000000000000000000000000000000000000000000000000000000000000000000000
000000000000
50 SMO BJ

Alert LOG中可以看到如下内容：

RECO started with pid=9, OS id=3011
CJQ0 started with pid=10, OS id=3013
"alert_orcl.log" [Read only] line 1 of 1864 --0%--
Wed Feb 25 13:17:45 2009
ARC0: STARTING ARCH PROCESSES
Wed Feb 25 13:17:45 2009
ARC1: Becoming the 'no FAL' ARCH
ARC1: Becoming the 'no SRL' ARCH
Wed Feb 25 13:17:45 2009
ARC2: Archival started
ARC0: STARTING ARCH PROCESSES COMPLETE
ARC0: Becoming the heartbeat ARCH
ARC2 started with pid=18, OS id=2088
Wed Feb 25 13:17:45 2009
MTTR advisory is disabled because FAST_START_MTTR_TARGET is not set
Wed Feb 25 13:17:45 2009
SMON: enabling cache recovery
Wed Feb 25 13:17:46 2009
Successfully onlined Undo Tablespace 1.
Wed Feb 25 13:17:46 2009
SMON: enabling tx recovery
Wed Feb 25 13:17:46 2009
Database Characterset is WE8ISO8859P1
replication_dependency_tracking turned off (no async multimaster replication found)
Starting background process QMNC
QMNC started with pid=19, OS id=2090
Wed Feb 25 13:17:50 2009
Completed: alter database open
Wed Feb 25 13:17:50 2009
db_recovery_file_dest_size of 2048 MB is 0.33% used. This is a
user-specified limit on the amount of space that will be used by this
database for recovery-related files, and does not reflect the amount of
space available in the underlying filesystem or ASM diskgroup.
Wed Feb 25 13:18:17 2009
Thread 1 advanced to log sequence 87
Wed Feb 25 13:18:17 2009
Shutting down archive processes
Wed Feb 25 13:18:17 2009
Current log# 2 seq# 87 mem# 0: /export/home/workzone/database/oracle/oradata/orcl/redo02.log
Wed Feb 25 13:18:18 2009
Wed Feb 25 13:18:18 2009
Logminer Bld: Build started
Wed Feb 25 13:18:22 2009
ARCH shutting down
ARC2: Archival stopped
Wed Feb 25 13:18:23 2009
Thread 1 advanced to log sequence 88
Current log# 3 seq# 88 mem# 0: /export/home/workzone/database/oracle/oradata/orcl/redo03.log
Wed Feb 25 13:18:23 2009
Wed Feb 25 13:18:23 2009
Logminer Bld: Lockdown Complete. DB_TXN_SCN is 1949 2170423892
Wed Feb 25 13:18:27 2009
Thread 1 cannot allocate new log, sequence 89
Checkpoint not complete
Current log# 3 seq# 88 mem# 0: /export/home/workzone/database/oracle/oradata/orcl/redo03.log
Thread 1 advanced to log sequence 89
Current log# 1 seq# 89 mem# 0: /export/home/workzone/database/oracle/oradata/orcl/redo01.log
Wed Feb 25 13:18:32 2009
Wed Feb 25 13:18:32 2009
Logminer Bld: Done
Wed Feb 25 13:18:36 2009
knlciAlterCapture: start scn changed.
scn: 0x079d.815e0e3a
Wed Feb 25 13:31:35 2009
Streams APPLY A001 started with pid=18, OS id=2519
Streams Apply Reader started P000 with pid=24 OS id=2521
Streams Apply Server started P001 with pid=25 OS id=2523
Wed Feb 25 13:31:37 2009
Streams CAPTURE C001 started with pid=26, OS id=2525
Wed Feb 25 13:31:39 2009
LOGMINER: Parameters summary for session# = 1
LOGMINER: Number of processes = 3, Transaction Chunk Size = 1
LOGMINER: Memory Size = 10M, Checkpoint interval = 10M
LOGMINER: session# = 1, reader process P002 started with pid=27 OS id=2527
LOGMINER: session# = 1, builder process P003 started with pid=28 OS id=2529
LOGMINER: session# = 1, preparer process P004 started with pid=29 OS id=2531
Wed Feb 25 13:31:40 2009
LOGMINER: Begin mining logfile: /export/home/workzone/database/oracle/10g/flash_recovery_area/ORCL/archivelog/2009_02_25/o1_mf_1_88_
4t9o58c5_.arc
Wed Feb 25 13:31:45 2009
Thread 1 advanced to log sequence 90
Current log# 2 seq# 90 mem# 0: /export/home/workzone/database/oracle/oradata/orcl/redo02.log
Wed Feb 25 13:31:46 2009
LOGMINER: End mining logfile: /export/home/workzone/database/oracle/10g/flash_recovery_area/ORCL/archivelog/2009_02_25/o1_mf_1_88_4t
9o58c5_.arc
Wed Feb 25 13:31:46 2009
LOGMINER: Begin mining logfile: /export/home/workzone/database/oracle/oradata/orcl/redo01.log
Wed Feb 25 13:31:49 2009
Some indexes or index [sub]partitions of table SYSTEM.LOGMNR_ATTRCOL$ have been marked unusable
Wed Feb 25 13:31:49 2009
Some indexes or index [sub]partitions of table SYSTEM.LOGMNR_CCOL$ have been marked unusable
Wed Feb 25 13:31:49 2009
Some indexes or index [sub]partitions of table SYSTEM.LOGMNR_CDEF$ have been marked unusable
Wed Feb 25 13:31:49 2009
Some indexes or index [sub]partitions of table SYSTEM.LOGMNR_COL$ have been marked unusable
Wed Feb 25 13:31:49 2009
Some indexes or index [sub]partitions of table SYSTEM.LOGMNR_COLTYPE$ have been marked unusable
Wed Feb 25 13:31:49 2009
Some indexes or index [sub]partitions of table SYSTEM.LOGMNR_ICOL$ have been marked unusable
Wed Feb 25 13:31:49 2009
Some indexes or index [sub]partitions of table SYSTEM.LOGMNR_IND$ have been marked unusable
Wed Feb 25 13:31:49 2009
Some indexes or index [sub]partitions of table SYSTEM.LOGMNR_INDCOMPART$ have been marked unusable
Wed Feb 25 13:31:49 2009
Some indexes or index [sub]partitions of table SYSTEM.LOGMNR_INDPART$ have been marked unusable
Wed Feb 25 13:31:49 2009
Some indexes or index [sub]partitions of table SYSTEM.LOGMNR_INDSUBPART$ have been marked unusable
Wed Feb 25 13:31:49 2009
Some indexes or index [sub]partitions of table SYSTEM.LOGMNR_LOB$ have been marked unusable
Wed Feb 25 13:31:49 2009
Some indexes or index [sub]partitions of table SYSTEM.LOGMNR_LOBFRAG$ have been marked unusable
Wed Feb 25 13:31:49 2009
Some indexes or index [sub]partitions of table SYSTEM.LOGMNR_OBJ$ have been marked unusable
Wed Feb 25 13:31:49 2009
Some indexes or index [sub]partitions of table SYSTEM.LOGMNR_TAB$ have been marked unusable
Wed Feb 25 13:31:49 2009
Some indexes or index [sub]partitions of table SYSTEM.LOGMNR_TABCOMPART$ have been marked unusable
Wed Feb 25 13:31:49 2009
Some indexes or index [sub]partitions of table SYSTEM.LOGMNR_TABPART$ have been marked unusable
Wed Feb 25 13:31:49 2009
Some indexes or index [sub]partitions of table SYSTEM.LOGMNR_TABSUBPART$ have been marked unusable
Wed Feb 25 13:31:49 2009
Some indexes or index [sub]partitions of table SYSTEM.LOGMNR_TS$ have been marked unusable
Wed Feb 25 13:31:49 2009
Some indexes or index [sub]partitions of table SYSTEM.LOGMNR_TYPE$ have been marked unusable
Wed Feb 25 13:31:49 2009
Some indexes or index [sub]partitions of table SYSTEM.LOGMNR_USER$ have been marked unusable
Indexes of table SYSTEM.LOGMNR_ATTRCOL$ have been rebuilt and are now usable
Indexes of table SYSTEM.LOGMNR_ATTRIBUTE$ have been rebuilt and are now usable
Indexes of table SYSTEM.LOGMNR_CCOL$ have been rebuilt and are now usable
Indexes of table SYSTEM.LOGMNR_CDEF$ have been rebuilt and are now usable
Indexes of table SYSTEM.LOGMNR_COL$ have been rebuilt and are now usable
Indexes of table SYSTEM.LOGMNR_COLTYPE$ have been rebuilt and are now usable
Indexes of table SYSTEM.LOGMNR_DICTIONARY$ have been rebuilt and are now usable
Indexes of table SYSTEM.LOGMNR_ICOL$ have been rebuilt and are now usable
Indexes of table SYSTEM.LOGMNR_IND$ have been rebuilt and are now usable
Indexes of table SYSTEM.LOGMNR_INDCOMPART$ have been rebuilt and are now usable
Indexes of table SYSTEM.LOGMNR_INDPART$ have been rebuilt and are now usable
Indexes of table SYSTEM.LOGMNR_INDSUBPART$ have been rebuilt and are now usable
Indexes of table SYSTEM.LOGMNR_LOB$ have been rebuilt and are now usable
Indexes of table SYSTEM.LOGMNR_LOBFRAG$ have been rebuilt and are now usable
Indexes of table SYSTEM.LOGMNR_OBJ$ have been rebuilt and are now usable
Indexes of table SYSTEM.LOGMNR_TAB$ have been rebuilt and are now usable
Indexes of table SYSTEM.LOGMNR_TABCOMPART$ have been rebuilt and are now usable
Indexes of table SYSTEM.LOGMNR_TABPART$ have been rebuilt and are now usable
Indexes of table SYSTEM.LOGMNR_TABSUBPART$ have been rebuilt and are now usable
Indexes of table SYSTEM.LOGMNR_TS$ have been rebuilt and are now usable
Indexes of table SYSTEM.LOGMNR_TYPE$ have been rebuilt and are now usable
Indexes of table SYSTEM.LOGMNR_USER$ have been rebuilt and are now usable
Wed Feb 25 13:32:00 2009
LOGMINER: Begin mining logfile: /export/home/workzone/database/oracle/10g/flash_recovery_area/ORCL/archivelog/2009_02_25/o1_mf_1_88_
4t9o58c5_.arc
Wed Feb 25 13:32:01 2009
LOGMINER: End mining logfile: /export/home/workzone/database/oracle/10g/flash_recovery_area/ORCL/archivelog/2009_02_25/o1_mf_1_88_4t
9o58c5_.arc
Wed Feb 25 13:32:01 2009
LOGMINER: Begin mining logfile: /export/home/workzone/database/oracle/oradata/orcl/redo01.log
Wed Feb 25 13:32:25 2009
LOGMINER: End mining logfile: /export/home/workzone/database/oracle/oradata/orcl/redo01.log
Wed Feb 25 13:32:25 2009
LOGMINER: Begin mining logfile: /export/home/workzone/database/oracle/oradata/orcl/redo02.log