oracle cdc 命令

1、查看当前的数据库是否开启日志归档模式, 这是开启cdc的必要条件

v$database在oracle中是一个视图，用于提供有关当前数据库实例的信息。它包含了一些列，其中之一是 log_mode

log_mode 列用于显示数据库实例的归档模式（Archive Mode）的状态。ARCHIVELOG说明开启了归档模式(即可以执行cdc相关操作)

SELECT log_mode FROM v$database;

2、查看哪些表开启了日志归档模式

ALTER TABLE inventory.customers ADD SUPPLEMENTAL LOG DATA (ALL) COLUMNS;---表级别的开启

ALTER TABLE FLINKUSER.MY_TABLE3 NOLOGGING; ---表级别关闭

ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;---数据库级别的开启
--查询设置结果
SELECT * FROM all_tables where table_name = 'MY_TABLE' and  logging = 'YES'

 3、设置数据库恢复文件的路径和大小--用户存储归档日志和其他数据库恢复相关的文件

alter system set db_recovery_file_dest_size = 10G;

alter system set db_recovery_file_dest = '/opt/oracle/oradata/recovery_area' scope=spfile;

--查询设置结果

SELECT name, value
FROM v$parameter
WHERE name = 'db_recovery_file_dest_size';

SELECT name, value
FROM v$parameter
WHERE name = 'db_recovery_file_dest';

4、查看日志文件大小， 这个查询将返回已生成的归档日志文件的序列号、文件名和大小（以字节为单位）

SELECT sequence#, name, BLOCKS * BLOCK_SIZE AS size_in_bytes FROM v$archived_log;

 5、官网推荐的权限列表

GRANT CREATE SESSION TO cdc_logminer;
GRANT SET CONTAINER TO cdc_logminer;---删除
GRANT SELECT ON V_$DATABASE to cdc_logminer;
GRANT FLASHBACK ANY TABLE TO cdc_logminer;
GRANT SELECT ANY TABLE TO cdc_logminer;
GRANT SELECT_CATALOG_ROLE TO cdc_logminer;
GRANT EXECUTE_CATALOG_ROLE TO cdc_logminer;
GRANT SELECT ANY TRANSACTION TO cdc_logminer;
GRANT LOGMINING TO cdc_logminer; --删除
GRANT CREATE TABLE TO cdc_logminer;
GRANT LOCK ANY TABLE TO cdc_logminer;
GRANT ALTER ANY TABLE TO cdc_logminer;
GRANT CREATE SEQUENCE TO cdc_logminer;
GRANT EXECUTE ON DBMS_LOGMNR TO cdc_logminer;
GRANT EXECUTE ON DBMS_LOGMNR_D TO cdc_logminer;
GRANT SELECT ON V_$LOG TO cdc_logminer;
GRANT SELECT ON V_$LOG_HISTORY TO cdc_logminer;
GRANT SELECT ON V_$LOGMNR_LOGS TO cdc_logminer;
GRANT SELECT ON V_$LOGMNR_CONTENTS TO cdc_logminer;
GRANT SELECT ON V_$LOGMNR_PARAMETERS TO cdc_logminer;
GRANT SELECT ON V_$LOGFILE TO cdc_logminer;
GRANT SELECT ON V_$ARCHIVED_LOG TO cdc_logminer;
GRANT SELECT ON V_$ARCHIVE_DEST_STATUS TO cdc_logminer;

6。开启补充日志(如果不开启，只会跟踪变更字段)

库开启:

ALTER DATABASE ADD SUPPLEMENTAL LOG DATA (ALL) COLUMNS;

SELECT SUPPLEMENTAL_LOG_DATA_MIN, SUPPLEMENTAL_LOG_DATA_ALL FROM V$DATABASE;

表开启: 

ALTER TABLE TEST_15 ADD SUPPLEMENTAL LOG DATA (ALL) COLUMNS;

 

PS: