SQL> select username from dba_users where account_status='OPEN';
USERNAME
--------------------------------------------------------------------------------
SYS
SYSTEM
TORCLUATBYADMIN
COREADMIN
DWETL
DAOSHU
DBSNMP
V7_BY_UAT
OPS_BY_UAT
CMS_BY_UAT
BEDC_BY_UAT
EBANK_BY_UAT
SMARTBI
OPS
RDP
SMARTBI1
CMS
EBANK
18 rows selected.
SQL> select * from nls_database_parameters where parameter='NLS_CHARACTERSET';
PARAMETER
--------------------------------------------------------------------------------
VALUE
--------------------------------------------------------------------------------
NLS_CHARACTERSET
ZHS16GBK
1.1修改参数：
SQL> show parameter GOLDENGATE
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
enable_goldengate_replication boolean FALSE
resource_manage_goldengate boolean FALSE
SQL> ALTER SYSTEM SET ENABLE_GOLDENGATE_REPLICATION = TRUE;
System altered.
SQL> show parameter GOLDENGATE
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
enable_goldengate_replication boolean TRUE
resource_manage_goldengate boolean FALSE
1.2CDB创建ogg用户：
SQL> create user C##GGADMIN identified by ggadmin;
User created.
SQL> exec dbms_goldengate_auth.grant_admin_privilege('C##GGADMIN',container=>'ALL');
PL/SQL procedure successfully completed.
SQL> grant connect,resource,unlimited tablespace to c##ggadmin container=all;
Grant succeeded.
SQL> grant dba to c##ggadmin container=all;
Grant succeeded.
SQL> select username from dba_users where account_status='OPEN' and
username='C##GGADMIN'; 2
USERNAME
--------------------------------------------------------------------------------
C##GGADMIN
SQL>
2.参数文件添加参数 （可选）
vim /home/oracle/.bash_profile
export LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib
export OGG_HOME=/data/app/ogg/oggsoft
export PATH=$PATH:$OGG_HOME
#将捕获进程注册到database
GGSCI (qas-oracle as C##GGADMIN@tcimcgbk/CDB$ROOT) 40> REGISTER EXTRACT ETBY DATABASE CONTAINER(TORCLUATBY)
2023-05-10 11:36:08 INFO OGG-02003 Extract ETBY successfully registered with database at SCN 10844830584509.
根据scn expdp数据
GGSCI (qas-oracle as C##GGADMIN@tcimcgbk/CDB$ROOT) 41> start ETBY
Sending START request to MANAGER ...
EXTRACT ETBY starting
GGSCI (qas-oracle as C##GGADMIN@tcimcgbk/CDB$ROOT) 43> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
EXTRACT RUNNING ETBY 00:00:00 00:02:32
3.4 启动捕获进程
start ETBY
3.5 根据SCN号expdp导出数据（初始化工作）
export ORACLE_SID=cimcgbk1
sqlplus / as sysdba
alter session set container=TORCLUATBY;
SQL> select DIRECTORY_NAME,DIRECTORY_PATH from dba_directories;
SQL> create or replace directory memaexpdp as '/data/app/ogg/oggexpdump';
SQL> grant read,write on directory memaexpdp to public;
#查看SCN号
SQL> alter session set container=TORCLUATBY;
Session altered.
SQL> col current_scn format 99999999999999999999999
select current_scn from v$database;SQL>
CURRENT_SCN
------------------------
10844833026242
3.5expdp导出表
vi ogg_TORCLUATBY.txt
directory=memaexpdp
dumpfile=TORCLUATBY_ogg_%U.dmp
logfile=TORCLUATBY_ogg.log
tables=CMS_BY_UAT.b_wf_voumng
,CMS_BY_UAT.portal_customer
,CMS_BY_UAT.PORTAL_BK
,CMS_BY_UAT.portal_inter_customer
,CMS_BY_UAT.portal_customer_person
,CMS_BY_UAT.FI_TRANSCODESETTING
,CMS_BY_UAT.sign_receiptinfo
,CMS_BY_UAT.file_binary
,CMS_BY_UAT.r_ts_chkbill
,BEDC_BY_UAT.BEDC_BANKACC
,BEDC_BY_UAT.bedc_hisbal
,BEDC_BY_UAT.BEDC_ELECTRONIC_RECEIPT_DETAIL_LIST
,BEDC_BY_UAT.bedc_detail
,BEDC_BY_UAT.bedc_hisdetail
,BEDC_BY_UAT.BEDC_BANKBRANCH
,BEDC_BY_UAT.BEDC_BANKSWIFTCODE
,BEDC_BY_UAT.BEDC_JNWBBRANCH
,V7_BY_UAT.kdpa_kehuzh
,V7_BY_UAT.kdpa_zhxinx
,V7_BY_UAT.kbrp_jgcshu
,V7_BY_UAT.kdpa_kehuzh
,V7_BY_UAT.kdpa_zhduiz
,V7_BY_UAT.V_KDPA_ZHXINX
,V7_BY_UAT.kdpp_pklist
,V7_BY_UAT.kdpa_zhxcdy
,V7_BY_UAT.kdpa_zhdqdy
,V7_BY_UAT.kcfb_cfdgjc
,V7_BY_UAT.kdpa_zhjxdy
,V7_BY_UAT.kdpa_zhbcxx
,V7_BY_UAT.kdpa_zhlldy
,V7_BY_UAT.vi_zhminx
,V7_BY_UAT.kdpb_zhmrye
,V7_BY_UAT.kdpb_fpmrxx
,V7_BY_UAT.kdpa_fpvyeb
,V7_BY_UAT.kapp_huobcs
,V7_BY_UAT.kdpl_zhminx
,V7_BY_UAT.v_pool_zhminx
,V7_BY_UAT.kdpl_fpjymx
flashback_scn='10844833026242'
parallel=4
cluster=no
[poracle@sfa-blwr600010 ogg_expdp]$ expdp system/oracle@TORCLUATBY parfile=ogg_TORCLUATBY.txt
报错：
ORA-39166: Object BEDC_BY_UAT.BEDC_JNWBBRANCH was not found or could not be exported or imported.
ORA-39166: Object V7_BY_UAT.V_KDPA_ZHXINX was not found or could not be exported or imported.
ORA-39166: Object V7_BY_UAT.VI_ZHMINX was not found or could not be exported or imported.
ORA-39166: Object V7_BY_UAT.V_POOL_ZHMINX was not found or could not be exported or imported.
Master table "SYSTEM"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
SQL> select object_name,object_type from dba_objects where object_name='BEDC_JNWBBRANCH';
OBJECT_NAME
--------------------------------------------------------------------------------
OBJECT_TYPE
-----------------------
BEDC_JNWBBRANCH
TABLE
BEDC_JNWBBRANCH
TABLE
总结：查看owner得知所属用户不对 更改用户 根据scn重新导出这个表
另外三个是视图 后续获取源端DDL语句创建视图
SQL> select object_name,object_type from dba_objects where object_name='V_POOL_ZHMINX';
OBJECT_NAME
--------------------------------------------------------------------------------
OBJECT_TYPE
-----------------------
V_POOL_ZHMINX
VIEW
V_POOL_ZHMINX
VIEW
SQL> select object_name,object_type from dba_objects where object_name='V_POOL_ZHMINX';
OBJECT_NAME
--------------------------------------------------------------------------------
OBJECT_TYPE
-----------------------
V_POOL_ZHMINX
VIEW
V_POOL_ZHMINX
VIEW
SQL> select object_name,object_type from dba_objects where object_name='VI_ZHMINX';
OBJECT_NAME
--------------------------------------------------------------------------------
OBJECT_TYPE
-----------------------
VI_ZHMINX
VIEW
sqlplus / as sysdba
alter session set container=TORCLOGG;
SQL> select DIRECTORY_NAME,DIRECTORY_PATH from dba_directories;
SQL> create or replace directory memaexpdp as '/data/app/ogg/oggexpdump';
SQL> grant read,write on directory memaexpdp to public;
create user V7_BY_UAT identified by V7 DEFAULT TABLESPACE V7_BY_UAT_DATA;
create user CMS_BY_UAT identified by CMS DEFAULT TABLESPACE CMS_BY_UAT_DATA;
create user BEDC_BY_UAT identified by BEDC DEFAULT TABLESPACE BEDC_BY_UAT_DATA;
grant dba,resource,connect,unlimited tablespace to V7_BY_UAT,CMS_BY_UAT,BEDC_BY_UAT;
nohup impdp system/oracle@TORCLOGG directory=MEMAEXPDP dumpfile=TORCLUATBY_ogg_%U.dmp logfile=TORCLUATBY_ogg.log parallel=4 cluster=no > logfile.txt 2>&1 &
tail -f logfile.txt
Resumable stmt start: 05/10/23 12:57:11 stmt suspend: 05/10/23 12:57:11
Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER
Job "SYSTEM"."SYS_IMPORT_FULL_01" completed with 3 error(s) at Wed May 10 15:05:33 2023 elapsed 0 02:30:57
You have mail in /var/spool/mail/oracle
[oracle@qas-oracle oggexpdump]$
2.数据库操作
2.1 先确认是否已创建OGG用户
sqlplus / as sysdba
alter session set container=TORCLOGG;
SQL> create user ogg identified by ggadmin;
User created.
SQL> grant connect,resource,unlimited tablespace to ogg;
SQL> grant dba to ogg;
Grant succeeded.
SQL> exec dbms_goldengate_auth.grant_admin_privilege('ogg',container=>'TORCLOGG');
PL/SQL procedure successfully completed.
2.2 禁用触发器
SQL> select 'alter trigger '||OBJECT_NAME||' disable;' From user_objects Where
Object_type='TRIGGER';
alter trigger LOGMNRGGC_TRIGGER disable;
alter trigger AW_TRUNC_TRG disable;
alter trigger AW_REN_TRG disable;
alter trigger AW_DROP_TRG disable;
3.GoldenGate配置
3.1 mgr进程配置
[oracle@hyoda2 ~]$ cd $OGG_HOME
[oracle@hyoda2 ogg19c]$ ggsci
GGSCI> info all
GGSCI> create subdirs
GGSCI> edit params mgr
port 7809
DYNAMICPORTLIST 7829-7849
PURGEOLDEXTRACTS ./dirdat/*, USECHECKPOINTS, MINKEEPDAYS 2
AUTORESTART REPLICAT *, RETRIES 3, WAITMINUTES 5, RESETMINUTES 60
GGSCI> start mgr
2378
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
