【Oracle Database】GoldenGate （rac-single）

源端数据库配置[oracle@king01 ~]$ sqlplus / as sysdbaSQL> create tablespace goldengate datafile '+DATAFILE' size 1024M;Tablespace created.SQL> create user ggs identified by ggs default tablespace goldengate;User created.SQL> grant dba to ggs;Grant succeeded.SQL> select log_mode from v$database;LOG_MODE------------------------------------ARCHIVELOGSQL> alter database force logging;Database altered.SQL> select force_logging from v$database;FORCE_LOGGING------------------------------YESSQL> alter database add supplemental log data;Database altered.SQL> col supplemental_log_data_min for a30SQL> select supplemental_log_data_min from v$database;SUPPLEMENTAL_LOG_DATA_MIN------------------------------YESSQL> alter system set enable_goldengate_replication=true;System altered.SQL> alter system archive log current;System altered.源端安装OGG[oracle@king01 ~]$ vi .bash_profileexport OGG_HOME=/home/oggexport LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/libexport PATH=$OGG_HOME:$PATH[oracle@king01 ~]$ source .bash_profile[oracle@king01 ~]$ mkdir -p /home/ogg[oracle@king01 ~]$ unzip fbo_ggs_Linux_x64_shiphome[oracle@king01 ~]$ cd fbo_ggs_Linux_x64_shiphome/Disk1[oracle@king01 Disk1]$ ./runInstaller[oracle@king01 ~]$ cd /home/ogg[oracle@king01 ogg]$ ./ggsci创建目录GGSCI (king01) 1> create subdirsCreating subdirectories under current directory /home/oggParameter files                /home/ogg/dirprm: createdReport files                   /home/ogg/dirrpt: createdCheckpoint files               /home/ogg/dirchk: createdProcess status files           /home/ogg/dirpcs: createdSQL script files               /home/ogg/dirsql: createdDatabase definitions files     /home/ogg/dirdef: createdExtract data files             /home/ogg/dirdat: createdTemporary files                /home/ogg/dirtmp: createdCredential store files         /home/ogg/dircrd: createdMasterkey wallet files         /home/ogg/dirwlt: createdDump files                     /home/ogg/dirdmp: created源端MANAGER进程组GGSCI (king01) 2> edit params mgrPORT 7839   DYNAMICPORTLIST 7840-7939AUTORESTART EXTRACT *,RETRIES 5,WAITMINUTES 3PURGEOLDEXTRACTS ./dirdat/*,USECHECKPOINTS, MINKEEPDAYS 7LAGREPORTHOURS 1LAGINFOMINUTES 3LAGCRITICALMINUTES 10GGSCI (king01) 3> start mgrManager started.GGSCI (king01) 4> info mgrManager is running (IP port king01.7839, Process ID 3243).源端EXTRACT进程组GGSCI (king01) 5> dblogin userid ggs,password ggsSuccessfully logged into database.GGSCI (king01 as ggs@king) 6>  add trandata soe.*GGSCI (king01 as ggs@king) 7> add extract ext_soe, tranlog, begin now, threads 2EXTRACT added.GGSCI (king01 as ggs@king) 8> add exttrail ./dirdat/st,extract ext_soe,megabytes 100EXTTRAIL added.GGSCI (king01 as ggs@king) 9> edit params ext_soeEXTRACT ext_soeSETENV (NLS_LANG = "AMERICAN_AMERICA.UTF8")USERID ggs, PASSWORD ggsREPORTCOUNT EVERY 30 MINUTES, RATEREPORTROLLOVER AT 02:00TRANLOGOPTIONS DBLOGREADERTRANLOGOPTIONS EXCLUDEUSER ggtDBOPTIONS ALLOWUNUSEDCOLUMN EXTTRAIL ./dirdat/stDISCARDFILE ./dirrpt/ext_soe.dsc,APPEND,MEGABYTES 5TABLEEXCLUDE SOE.ORDERENTRY_METADATATABLE SOE.*;GGSCI (king01 as ggs@king) 10> start ext_soeSending START request to MANAGER ...EXTRACT EXT_SOE startingGGSCI (king01 as ggs@king) 11> info ext_soeEXTRACT    EXT_SOE   Last Started 2018-11-08 17:05   Status RUNNINGCheckpoint Lag       00:00:00 (updated 00:00:03 ago)Process ID           7172Log Read Checkpoint  Oracle Redo Logs                     2018-11-09 08:53:58  Thread 1, Seqno 74, RBA 27958272                     SCN 0.1517400 (1517400)Log Read Checkpoint  Oracle Redo Logs                     2018-11-09 08:54:01  Thread 2, Seqno 58, RBA 19046912                     SCN 0.1517403 (1517403)                     源端PUMP进程组                     GGSCI (king01 as ggs@king) 12> add extract pmp_soe, exttrailsource ./dirdat/stEXTRACT added.GGSCI (king01 as ggs@king) 13> add rmttrail ./dirdat/rt,EXTRACT pmp_soe,megabytes 100RMTTRAIL added.GGSCI (king01 as ggs@king) 14> edit params pmp_soeEXTRACT pmp_soePASSTHRURMTHOST 192.168.1.203, MGRPORT 7839, COMPRESSRMTTRAIL ./dirdat/rtTABLE SOE.*;GGSCI (king01 as ggs@king) 15> start pmp_soeSending START request to MANAGER ...EXTRACT PMP_SOE startingGGSCI (king01 as ggs@king) 16>  info pmp_soeEXTRACT    PMP_SOE   Last Started 2018-11-08 17:01   Status RUNNINGCheckpoint Lag       00:00:00 (updated 00:00:07 ago)Process ID           6690Log Read Checkpoint  File /home/ogg/dirdat/st000000000                     First Record  RBA 1382                     GGSCI (king01 as ggs@king) 17> info allProgram     Status      Group       Lag at Chkpt  Time Since ChkptMANAGER     RUNNING                                           EXTRACT     RUNNING     EXT_SOE     00:00:00      00:00:01    EXTRACT     RUNNING     PMP_SOE     00:00:00      00:00:04源端备份数据库[oracle@king01 ~]$ mkdir backup[oracle@king01 ~]$ rman target /RMAN> CONFIGURE CONTROLFILE AUTOBACKUP ON;RMAN> CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '/home/oracle/backup/%F';RMAN> RUN {BACKUP INCREMENTAL LEVEL=0 TAG 'FULL_BACKUP' DATABASEFORMAT '/home/oracle/backup//soe_full_incr_%s_%p_%T'PLUS ARCHIVELOGFORMAT '/home/oracle/backup/soe_arch_%s_%p_%T' delete all input;DELETE NOPROMPT OBSOLETE;CROSSCHECK BACKUP;DELETE NOPROMPT EXPIRED BACKUP;}[oracle@king01 ~]$ sqlplus / as sysdbaSQL> select to_char(current_scn) from v$database;TO_CHAR(CURRENT_SCN)----------------------------------------1419545SQL> alter system archive log current ;System altered.SQL> alter system archive log current ;System altered.SQL> alter system archive log current ;System altered.[oracle@king01 ~]$ rman target /RMAN> backup archivelog all FORMAT '/home/oracle/backup/soe_arch_%s_%p_%T';[oracle@king01 ~]$ cd /home/oracle/backup[oracle@king01 backup]$  scp * 192.168.1.203:/home/oracle/backup

目标端恢复数据库[oracle@king03 ~]$ mkdir -p /home/oracle/admin/kingdb/adump[oracle@king03 ~]$ mkdir -p /home/oracle/oradata/kingdb/[oracle@king03 ~]$ mkdir -p /home/oracle/fast_recovery_area/kingdb[oracle@king03 ~]$ cd $ORACLE_HOME/dbs[oracle@king03 dbs]$ vi initkingdb.ora*.audit_trail='db'*.compatible='11.2.0.4.0'*.control_files='/home/oracle/oradata/kingdb/control01.ctl','/home/oracle/fast_recovery_area/kingdb/control02.ctl'*.db_block_size=8192*.db_domain=''*.db_name='kingdb'*.db_recovery_file_dest='/home/oracle/fast_recovery_area'*.db_recovery_file_dest_size=21474836480*.diagnostic_dest='/home/oracle'*.dispatchers='(PROTOCOL=TCP) (SERVICE=kingdbXDB)'*.enable_goldengate_replication=TRUE*.job_queue_processes=0*.log_archive_format='%t_%s_%r.dbf'*.open_cursors=300*.pga_aggregate_target=536870912*.processes=150*.remote_login_passwordfile='EXCLUSIVE'*.sga_target=2147483648*.undo_tablespace='UNDOTBS1'[oracle@king03 dbs]$ sqlplus / as sysdbaSQL> create spfile from pfile;File created.[oracle@king03 dbs]$ orapwd file=/home/oracle/product/11.2.0/db_1/dbs/orapwkingdb password=oracle entries=5 force=y[oracle@king03 ~]$ rman target /RMAN> startup nomountTotal System Global Area    1068937216 bytesFixed Size                     2260088 bytesVariable Size                322962312 bytesDatabase Buffers             687865856 bytesRedo Buffers                  55848960 bytesRMAN> set DBID=4126740520executing command: SET DBIDRMAN> run {set CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '/home/oracle/backup/%F';restore controlfile from autobackup;}RMAN> alter database mount;RMAN> run {                                 set until scn 1419545;SET NEWNAME FOR DATAFILE 1 to '/home/oracle/oradata/kingdb/system.dbf';SET NEWNAME FOR DATAFILE 2 to '/home/oracle/oradata/kingdb/sysaux.dbf';SET NEWNAME FOR DATAFILE 3 to '/home/oracle/oradata/kingdb/undotbs1.dbf';SET NEWNAME FOR DATAFILE 4 to '/home/oracle/oradata/kingdb/users.dbf';SET NEWNAME FOR DATAFILE 5 to '/home/oracle/oradata/kingdb/undotbs2.dbf';SET NEWNAME FOR DATAFILE 6 to '/home/oracle/oradata/kingdb/soe.dbf';SET NEWNAME FOR DATAFILE 7 to '/home/oracle/oradata/kingdb/goldengate.dbf';SET NEWNAME FOR DATAFILE 8 to '/home/oracle/oradata/kingdb/tpcc.dbf';SET NEWNAME FOR DATAFILE 9 to '/home/oracle/oradata/kingdb/tpch.dbf';SET NEWNAME FOR TEMPFILE 1 to '/home/oracle/oradata/kingdb/temp01.dbf';RESTORE DATABASE; SWITCH DATAFILE ALL;SWITCH TEMPFILE ALL; recover database;}[oracle@king03 ~]$  sqlplus / as sysdbaSQL> alter database rename file '+DATAFILE/kingdb/onlinelog/group_1.261.991491245' to '/home/oracle/oradata/kingdb/redo1_a.log';SQL> alter database rename file '+FRA/kingdb/onlinelog/group_1.257.991491249' to '/home/oracle/oradata/kingdb/redo1_b.log';SQL> alter database rename file '+DATAFILE/kingdb/onlinelog/group_2.262.991491251' to '/home/oracle/oradata/kingdb/redo2_a.log';SQL> alter database rename file '+FRA/kingdb/onlinelog/group_2.258.991491255' to '/home/oracle/oradata/kingdb/redo2_b.log';SQL> alter database rename file '+DATAFILE/kingdb/onlinelog/group_3.265.991491457' to '/home/oracle/oradata/kingdb/redo3_a.log';SQL> alter database rename file '+FRA/kingdb/onlinelog/group_3.259.991491461' to '/home/oracle/oradata/kingdb/redo3_b.log';SQL> alter database rename file '+DATAFILE/kingdb/onlinelog/group_4.266.991491465' to '/home/oracle/oradata/kingdb/redo4_a.log';SQL> alter database rename file '+FRA/kingdb/onlinelog/group_4.260.991491469' to '/home/oracle/oradata/kingdb/redo4_b.log';SQL> alter database open resetlogs;SQL> SQL> select thread#,status,enabled from v$thread;   THREAD# STATUS             ENABLED---------- ------------------ ------------------------         1 OPEN               PUBLIC         2 CLOSED             PUBLIC         SQL> alter database disable thread 2;Database altered.SQL> select thread#,status,enabled from v$thread;   THREAD# STATUS             ENABLED---------- ------------------ ------------------------         1 OPEN               PUBLIC         2 CLOSED             DISABLED目标端数据库设置[oracle@king03 ~]$  sqlplus / as sysdbaSQL> select 'alter table '||owner||'.'||table_name||' disable constraint '||constraint_name||';' from dba_constraints where constraint_type in ('R') and owner in('SOE') order by status,owner; 'ALTERTABLE'||OWNER||'.'||TABLE_NAME||'DISABLECONSTRAINT'||CONSTRAINT_NAME||';'--------------------------------------------------------------------------------alter table SOE.ADDRESSES disable constraint ADD_CUST_FK;alter table SOE.ORDERS disable constraint ORDERS_CUSTOMER_ID_FK;alter table SOE.ORDER_ITEMS disable constraint ORDER_ITEMS_PRODUCT_ID_FK;alter table SOE.INVENTORIES disable constraint INVENTORIES_PRODUCT_ID_FK;alter table SOE.ORDER_ITEMS disable constraint ORDER_ITEMS_ORDER_ID_FK;alter table SOE.INVENTORIES disable constraint INVENTORIES_WAREHOUSES_FK;6 rows selected.SQL> select 'alter trigger '||owner||'.'||trigger_name||' disable;' from dba_triggers where owner in('SOE') order by status,owner;no rows selectedSQL> drop user ggs cascade;User dropped.SQL> create user ggt identified by ggt default tablespace goldengate;User created.SQL> grant dba to ggt;Grant succeeded.目标端安装OGG[oracle@king03 ~]$ vi .bash_profileexport OGG_HOME=/home/oggexport LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/libexport PATH=$OGG_HOME:$PATH[oracle@king03 ~]$ source .bash_profile[oracle@king03 ~]$ mkdir -p /home/ogg[oracle@king03 ~]$ unzip fbo_ggs_Linux_x64_shiphome[oracle@king03 ~]$ cd fbo_ggs_Linux_x64_shiphome/Disk1[oracle@king03 Disk1]$ ./runInstaller [oracle@king03 Disk1]$ cd /home/ogg[oracle@king03 ogg]$ ./ggsci创建目录GGSCI (king03) 1> create subdirsCreating subdirectories under current directory /home/oggParameter files                /home/ogg/dirprm: createdReport files                   /home/ogg/dirrpt: createdCheckpoint files               /home/ogg/dirchk: createdProcess status files           /home/ogg/dirpcs: createdSQL script files               /home/ogg/dirsql: createdDatabase definitions files     /home/ogg/dirdef: createdExtract data files             /home/ogg/dirdat: createdTemporary files                /home/ogg/dirtmp: createdCredential store files         /home/ogg/dircrd: createdMasterkey wallet files         /home/ogg/dirwlt: createdDump files                     /home/ogg/dirdmp: created目标端MANAGER进程组GGSCI (king03) 2> edit params mgrPORT 7839   DYNAMICPORTLIST 7840-7939AUTORESTART EXTRACT *,RETRIES 5,WAITMINUTES 3PURGEOLDEXTRACTS ./dirdat/*,USECHECKPOINTS, MINKEEPDAYS 7LAGREPORTHOURS 1LAGINFOMINUTES 3LAGCRITICALMINUTES 10GGSCI (king03) 3> start mgrManager started.GGSCI (king03) 4> info mgrManager is running (IP port king03.7839, Process ID 13650).目标端REPLICAT进程组GGSCI (king03) 5> edit params ./GLOBALScheckpointtable ggt.chkptGGSCI (king03) 6> dblogin userid ggt,password ggtSuccessfully logged into database.GGSCI (king03 as ggt@king) 7> add checkpointtableNo checkpoint table specified. Using GLOBALS specification (ggt.chkpt)...Successfully created checkpoint table ggt.chkpt.GGSCI (king03 as ggt@king) 8> add replicat rep_soe,exttrail ./dirdat/rtREPLICAT added.GGSCI (king03 as ggt@king) 9> edit param rep_soe REPLICAT rep_soeSETENV (NLS_LANG = "AMERICAN_AMERICA.UTF8")USERID ggt, PASSWORD ggtREPORTCOUNT EVERY 30 MINUTES, RATEREPORTROLLOVER AT 02:00DBOPTIONS SUPPRESSTRIGGERSREPERROR DEFAULT, ABENDDISCARDFILE ./dirrpt/rep_soe.dsc,APPEND,MEGABYTES 5ASSUMETARGETDEFS   ALLOWNOOPUPDATESMAP SOE.*, TARGET SOE.*;  GGSCI (king03 as ggt@king) 10> start replicat rep_soe, aftercsn 1419545Sending START request to MANAGER ...REPLICAT REP_SOE startingGGSCI (king03 as ggt@king) 11> info rep_soeREPLICAT   REP_SOE   Last Started 2018-11-08 17:10   Status RUNNINGCheckpoint Lag       00:00:00 (updated 00:00:03 ago)Process ID           28121Log Read Checkpoint  File /home/ogg/dirdat/rt000000000                     First Record  RBA 0                     GGSCI (king03 as ggt@king) 12> info allProgram     Status      Group       Lag at Chkpt  Time Since ChkptMANAGER     RUNNING                                           REPLICAT    RUNNING     REP_SOE     00:00:00      00:00:01