搭建oracle11g
拉取oracle11g镜像
docker pull registry.cn-hangzhou.aliyuncs.com/helowin/oracle_11g
创建容器
docker run -d -p 1521:1521 --name oracle11g registry.cn-hangzhou.aliyuncs.com/helowin/oracle_11g
进入容器进行配置
docker exec -it oracle11g bash
切换root
su root
密码为:helowin
编辑profile文件配置ORACLE环境变量
vi /etc/profile
export ORACLE_HOME=/home/oracle/app/oracle/product/11.2.0/dbhome_2
export ORACLE_SID=helowin
export PATH=$ORACLE_HOME/bin:$PATH
source /etc/profile
创建软连接
ln -s $ORACLE_HOME/bin/sqlplus /usr/bin
切换到oracle用户
su - oracle
登录sqlplus 并修改sys、system用户密码
sqlplus /nolog
conn /as sysdba
alter user system identified by oracle;
alter user sys identified by oracle;
ALTER PROFILE DEFAULT LIMIT PASSWORD_LIFE_TIME UNLIMITED;
退出:exit;
查看oracle实例状态
lsnrctl status
测试连接
服务名:helowin(一定要填写helowin)
密码:oracle(第12步设置的密码)
开启归档日志
打开归档日志开关(启动LogMiner)
使用有DBA权限的用户登录Oracle
sqlplus /nolog
conn /as sysdba
检查数据库日志模式
select log_mode from v$database;
如果结果是NOARCHIVELOG,执行下面SQL命令
shutdown immediate;
startup mount;
alter database archivelog;
alter database open;
查看存储路径
select dest_id,name from v$archived_log;
启用 Supplemental Logging
为了从日志里获取数据,LogMiner需要启用数据库或表的supplemental logging。Supplemental logging又分为identification key logging和full supplemental logging。Identification key logging只包含主键和发生改变的字段数据,而full supplemental logging包含了所有字段的数据,这两种根据需求选择一种即可。
启用Identification key logging
- 指定一张表启用
ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;
ALTER TABLE <schema name>.<table name> ADD SUPPLEMENTAL LOG DATA (PRIMARY KEY) COLUMNS;
- 同时为数据库所有表中启用
ALTER DATABASE ADD SUPPLEMENTAL LOG DATA (PRIMARY KEY) COLUMNS;
启用full supplemental logging
- 指定一张表
ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;
ALTER TABLE <schema name>.<table name> ADD SUPPLEMENTAL LOG DATA (ALL) COLUMNS;
- 同时为数据库所有表中启用
ALTER DATABASE ADD SUPPLEMENTAL LOG DATA (ALL) COLUMNS;
提交修改
ALTER SYSTEM SWITCH LOGFILE;
创建用户并授权
create user mbsdb identified by "admin";
grant create session, alter session, execute_catalog_role, select any transaction, select any table to mbsdb;
grant select on GV_$DATABASE to mbsdb;
grant select on GV_$ARCHIVED_LOG to mbsdb;
grant select on V_$ARCHIVED_LOG to mbsdb;
GRANT SELECT ON V_$DATABASE to mbsdb ;
GRANT SELECT ON V_$TRANSACTION to mbsdb ;
GRANT SELECT ON V_$LOG TO mbsdb ;
GRANT SELECT ON V_$ARCHIVE_DEST_STATUS TO mbsdb ;
GRANT SELECT ON V_$LOGFILE TO mbsdb ;
grant select on V_$LOGMNR_CONTENTS to mbsdb;
grant select on SYSTEM.T_DEF_SEX to mbsdb;
提取LogMiner Dictionary到redo log
当你希望LogMiner使用来自redo log中的字典时,需要执行此步骤,将字典提取到redo log中,且必须在启动pipeline之前执行。如果使用online catlog中的字典,则不需要。
EXECUTE DBMS_LOGMNR_D.BUILD(OPTIONS=> DBMS_LOGMNR_D.STORE_IN_REDO_LOGS);