【Oracle Database】Oracle GoldenGate （single-single）

源端数据库配置[root@king01 ~]# su - oracle[oracle@king01 ~]$ sqlplus / as sysdbaSQL> create tablespace goldengate datafile '/home/oracle/oradata/king/ogg01.dbf' 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;SQL> alter system archive log current;源端安装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.zip[oracle@king01 ~]$ cd fbo_ggs_Linux_x64_shiphome/Disk1[oracle@king01 Disk1]$ ./runInstaller[oracle@king01 ~]$ cd /home/ogg[oracle@king01 ogg]$ ./ggsciOracle GoldenGate Command Interpreter for OracleVersion 12.2.0.1.1 OGGCORE_12.2.0.1.0_PLATFORMS_151211.1401_FBOLinux, x64, 64bit (optimized), Oracle 11g on Dec 12 2015 00:54:38Operating system character set identified as UTF-8.Copyright (C) 1995, 2015, Oracle and/or its affiliates. All rights reserved.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 nowEXTRACT 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_soeDYNAMICRESOLUTIONUSERID ggs,PASSWORD ggsEXTTRAIL ./dirdat/stTABLEEXCLUDE 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 2017-08-30 13:47   Status RUNNINGCheckpoint Lag       00:00:00 (updated 00:00:07 ago)Process ID           4234Log Read Checkpoint  Oracle Redo Logs                     2017-08-30 16:58:33  Seqno 59, RBA 211786752                     SCN 0.2361582 (2361582)                     源端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.202, 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 2017-08-30 15:22   Status RUNNINGCheckpoint Lag       00:00:00 (updated 00:00:08 ago)Process ID           4844Log Read Checkpoint  File ./dirdat/st000000000                     2017-08-30 16:20:08.000000  RBA 47753236                     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:06源端备份数据库[oracle@king01 ~]$ mkdir /home/oracle/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)----------------------------------------1032191SQL> 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 backup[oracle@king01 backup]$  scp * 192.168.1.202:/home/oracle/backup/[oracle@king01 ~]$ cd $ORACLE_HOME/dbs[oracle@king01 dbs]$ scp orapwking 192.168.1.202:/home/database/11.2.0.4/product/dbs

目标端恢复数据库[oracle@king02 ~]$ mkdir -p /home/oracle/admin/king/adump[oracle@king02 ~]$ mkdir -p /home/oracle/oradata/king[oracle@king02 ~]$ mkdir -p /home/oracle/fast_recovery_area/king[oracle@king02 ~]$ mkdir -p /home/oracle/archive[oracle@king02 ~]$ rman target /RMAN> startup nomountstartup failed: ORA-01078: failure in processing system parametersLRM-00109: could not open parameter file '/home/oracle/product/11.2.0/db_1/dbs/initking.ora'starting Oracle instance without parameter file for retrieval of spfileOracle instance startedTotal System Global Area    1068937216 bytesFixed Size                     2260088 bytesVariable Size                281019272 bytesDatabase Buffers             780140544 bytesRedo Buffers                   5517312 bytesRMAN> set DBID=1085678857RMAN> run{ set CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '/home/oracle/backup/%F'; restore spfile from autobackup; } RMAN> shutdown immediateRMAN> startup nomountTotal System Global Area    1068937216 bytesFixed Size                     2260088 bytesVariable Size                322962312 bytesDatabase Buffers             687865856 bytesRedo Buffers                  55848960 bytesRMAN> set DBID=1085678857executing command: SET DBIDRMAN> run{2> set CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '/home/oracle/backup/%F';3> restore controlfile from autobackup;4> }RMAN> alter database mount;RMAN> run{ set until scn 1032191; restore database; recover database; } RMAN> alter database open resetlogs;目标端数据库设置SQL> 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>  alter system set job_queue_processes=0 scope=both;System altered.SQL> 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@king02 ~]$ vi .bash_profileexport OGG_HOME=/home/oggexport LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/libexport PATH=$OGG_HOME:$PATH[oracle@king02 ~]$ source .bash_profile[oracle@king02 ~]$ mkdir -p /home/ogg[oracle@king02 ~]$ unzip fbo_ggs_Linux_x64_shiphome.zip[oracle@king02 ~]$ cd fbo_ggs_Linux_x64_shiphome/Disk1[oracle@king02 Disk1]$ ./runInstaller [oracle@king02 Disk1]$ cd /home/ogg[oracle@king02 ogg]$ ./ggsciGGSCI (king02) 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 (king02) 2> edit params mgrPORT 7839   DYNAMICPORTLIST 7840-7939AUTORESTART EXTRACT *,RETRIES 5,WAITMINUTES 3PURGEOLDEXTRACTS ./dirdat/*,USECHECKPOINTS, MINKEEPDAYS 7LAGREPORTHOURS 1LAGINFOMINUTES 3LAGCRITICALMINUTES 10GGSCI (king02) 3> start mgrManager started.GGSCI (king02) 4> info mgrManager is running (IP port king02.7839, Process ID 13650).目标端REPLICAT进程组GGSCI (king02) 5> edit params ./GLOBALScheckpointtable ggt.chkptGGSCI (king02) 6> dblogin userid ggt,password ggtSuccessfully logged into database.GGSCI (king02 as ggt@king) 7> add checkpointtableNo checkpoint table specified. Using GLOBALS specification (ggt.chkpt)...Successfully created checkpoint table ggt.chkpt.GGSCI (king02 as ggt@king) 8> add replicat rep_soe,exttrail ./dirdat/rtREPLICAT added.GGSCI (king02 as ggt@king) 9> edit param rep_soe REPLICAT rep_soeUSERID ggt, PASSWORD ggtREPERROR DEFAULT, ABENDDISCARDFILE ./dirrpt/rep_soe.dsc,APPEND,MEGABYTES 1024ASSUMETARGETDEFSALLOWNOOPUPDATESMAP SOE.*, TARGET SOE.*;GGSCI (king02 as ggt@king) 10> start replicat rep_soe, aftercsn 1032191Sending START request to MANAGER ...REPLICAT REP_SOE startingGGSCI (king02 as ggt@king) 11> info rep_soeREPLICAT   REP_SOE   Last Started 2018-08-04 14:40   Status RUNNINGCheckpoint Lag       00:00:00 (updated 00:00:00 ago)Process ID           13902Log Read Checkpoint  File ./dirdat/rt000000000                     2018-08-04 14:43:08.676383  RBA 42207GGSCI (king02 as ggt@king) 12> info allProgram     Status      Group       Lag at Chkpt  Time Since ChkptMANAGER     RUNNING                                           REPLICAT    RUNNING     REP_SOE     00:00:00      00:00:04

OGG DDL复制[oracle@king01 ogg]$ ./ggsciGGSCI (king01) 1> edit params ./GLOBALSGGSCHEMA ggs[oracle@king01 ogg]$  sqlplus / as sysdbaSQL> show parameter recyclebinNAME                                 TYPE                              VALUE------------------------------------ --------------------------------- ------------------------------recyclebin                           string                            onSQL> alter system set recyclebin=off deferred;System altered.SQL> purge recyclebin;Recyclebin purged.SQL> @marker_setup.sqlMarker setup scriptYou will be prompted for the name of a schema for the Oracle GoldenGate database objects.NOTE: The schema must be created prior to running this script.NOTE: Stop all DDL replication before starting this installation.Enter Oracle GoldenGate schema name:ggsMarker setup table script complete, running verification script...Please enter the name of a schema for the GoldenGate database objects:Setting schema name to GGSMARKER TABLE---------------------------------------------------------------------------------------------OKMARKER SEQUENCE---------------------------------------------------------------------------------------------OKScript complete.SQL> @ddl_setup.sqlOracle GoldenGate DDL Replication setup scriptVerifying that current user has privileges to install DDL Replication...You will be prompted for the name of a schema for the Oracle GoldenGate database objects.NOTE: For an Oracle 10g source, the system recycle bin must be disabled. For Oracle 11g and later, it can be enabled.NOTE: The schema must be created prior to running this script.NOTE: Stop all DDL replication before starting this installation.Enter Oracle GoldenGate schema name:ggsWorking, please wait ...Spooling to file ddl_setup_spool.txtChecking for sessions that are holding locks on Oracle Golden Gate metadata tables ...Check complete.Using GGS as a Oracle GoldenGate schema name.Working, please wait ...DDL replication setup script complete, running verification script...Please enter the name of a schema for the GoldenGate database objects:Setting schema name to GGSCLEAR_TRACE STATUS:Line/pos------------------------------------------------------------------------------------------------------------------------Error-----------------------------------------------------------------No errorsNo errorsCREATE_TRACE STATUS:Line/pos------------------------------------------------------------------------------------------------------------------------Error-----------------------------------------------------------------No errorsNo errorsTRACE_PUT_LINE STATUS:Line/pos------------------------------------------------------------------------------------------------------------------------Error-----------------------------------------------------------------No errorsNo errorsINITIAL_SETUP STATUS:Line/pos------------------------------------------------------------------------------------------------------------------------Error-----------------------------------------------------------------No errorsNo errorsDDLVERSIONSPECIFIC PACKAGE STATUS:Line/pos------------------------------------------------------------------------------------------------------------------------Error-----------------------------------------------------------------No errorsNo errorsDDLREPLICATION PACKAGE STATUS:Line/pos------------------------------------------------------------------------------------------------------------------------Error-----------------------------------------------------------------No errorsNo errorsDDLREPLICATION PACKAGE BODY STATUS:Line/pos------------------------------------------------------------------------------------------------------------------------Error-----------------------------------------------------------------No errorsNo errorsDDL IGNORE TABLE---------------------------------------------------------------------------------------------------------OKDDL IGNORE LOG TABLE---------------------------------------------------------------------------------------------------------OKDDLAUX  PACKAGE STATUS:Line/pos------------------------------------------------------------------------------------------------------------------------Error-----------------------------------------------------------------No errorsNo errorsDDLAUX PACKAGE BODY STATUS:Line/pos------------------------------------------------------------------------------------------------------------------------Error-----------------------------------------------------------------No errorsNo errorsSYS.DDLCTXINFO  PACKAGE STATUS:Line/pos------------------------------------------------------------------------------------------------------------------------Error-----------------------------------------------------------------No errorsNo errorsSYS.DDLCTXINFO  PACKAGE BODY STATUS:Line/pos------------------------------------------------------------------------------------------------------------------------Error-----------------------------------------------------------------No errorsNo errorsDDL HISTORY TABLE---------------------------------------------------------------------------------------------------------OKDDL HISTORY TABLE(1)---------------------------------------------------------------------------------------------------------OKDDL DUMP TABLES---------------------------------------------------------------------------------------------------------OKDDL DUMP COLUMNS---------------------------------------------------------------------------------------------------------OKDDL DUMP LOG GROUPS---------------------------------------------------------------------------------------------------------OKDDL DUMP PARTITIONS---------------------------------------------------------------------------------------------------------OKDDL DUMP PRIMARY KEYS---------------------------------------------------------------------------------------------------------OKDDL SEQUENCE---------------------------------------------------------------------------------------------------------OKGGS_TEMP_COLS---------------------------------------------------------------------------------------------------------OKGGS_TEMP_UK---------------------------------------------------------------------------------------------------------OKDDL TRIGGER CODE STATUS:Line/pos------------------------------------------------------------------------------------------------------------------------Error-----------------------------------------------------------------No errorsNo errorsDDL TRIGGER INSTALL STATUS---------------------------------------------------------------------------------------------------------OKDDL TRIGGER RUNNING STATUS------------------------------------------------------------------------------------------------------------------------ENABLEDSTAYMETADATA IN TRIGGER------------------------------------------------------------------------------------------------------------------------OFFDDL TRIGGER SQL TRACING------------------------------------------------------------------------------------------------------------------------0DDL TRIGGER TRACE LEVEL------------------------------------------------------------------------------------------------------------------------NONELOCATION OF DDL TRACE FILE------------------------------------------------------------------------------------------------------------------------/u01/app/oracle/diag/rdbms/king/king/trace/ggs_ddl_trace.logAnalyzing installation status...VERSION OF DDL REPLICATION------------------------------------------------------------------------------------------------------------------------OGGCORE_12.2.0.1.0_PLATFORMS_151211.1401STATUS OF DDL REPLICATION------------------------------------------------------------------------------------------------------------------------SUCCESSFUL installation of DDL Replication software componentsScript complete.SQL> @role_setup.sqlGGS Role setup scriptThis script will drop and recreate the role GGS_GGSUSER_ROLETo use a different role name, quit this script and then edit the params.sql script to change the gg_role parameter to the preferred name. (Do not run the script.)You will be prompted for the name of a schema for the GoldenGate database objects.NOTE: The schema must be created prior to running this script.NOTE: Stop all DDL replication before starting this installation.Enter GoldenGate schema name:ggsWrote file role_setup_set.txtPL/SQL procedure successfully completed.Role setup script completeGrant this role to each user assigned to the Extract, GGSCI, and Manager processes, by using the following SQL command:GRANT GGS_GGSUSER_ROLE TO where  is the user assigned to the GoldenGate processes.SQL> grant ggs_ggsuser_role to ggs;Grant succeeded.SQL> @ddl_enable.sqlTrigger altered.SQL> @marker_status.sqlPlease enter the name of a schema for the GoldenGate database objects:ggsSetting schema name to GGSMARKER TABLE---------------------------------------------------------------------------------------------OKMARKER SEQUENCE---------------------------------------------------------------------------------------------OK[oracle@king01 ogg]$ ./ggsciGGSCI (king01) 1> edit params ext_soeEXTRACT ext_soeUSERID ggs,PASSWORD ggsEXTTRAIL ./dirdat/stDDL INCLUDE ALLDDLOPTIONS ADDTRANDATADDLOPTIONS REPORTTABLEEXCLUDE SOE.ORDERENTRY_METADATATABLE SOE.*;[oracle@king02 ogg]$ ./ggsciGGSCI (king02) 1>  edit params rep_soeREPLICAT rep_soeUSERID ggt, PASSWORD ggtDISCARDFILE ./dirrpt/rep_soe.dsc,APPEND,MEGABYTES 1024ASSUMETARGETDEFSALLOWNOOPUPDATESDDL INCLUDE MAPPEDDDLERROR DEFAULT ABENDDDLOPTIONS REPORTMAP SOE.*, TARGET SOE.*;

源端OGG监控GGSCI (king01) 1> info ext_soeEXTRACT    EXT_SOE   Last Started 2018-08-11 14:16   Status RUNNINGCheckpoint Lag       00:00:00 (updated 00:00:09 ago)Process ID           8491Log Read Checkpoint  Oracle Redo Logs                     2018-08-11 14:50:10  Seqno 38, RBA 25028608                     SCN 0.1085945 (1085945)GGSCI (king01) 2> info pmp_soeEXTRACT    PMP_SOE   Last Started 2018-08-11 14:16   Status RUNNINGCheckpoint Lag       00:00:00 (updated 00:00:01 ago)Process ID           8504Log Read Checkpoint  File ./dirdat/st000000002                     2018-08-11 14:23:46.000000  RBA 2188582GGSCI (king01) 3> dblogin userid ggs,password ggsSuccessfully logged into database.GGSCI (king01 as ggs@king) 4> lag ext_soeSending GETLAG request to EXTRACT EXT_SOE ...Last record lag 2 seconds.At EOF, no more records to process.GGSCI (king01 as ggs@king) 5> stats ext_soe,daily,table soe.ordersSending STATS request to EXTRACT EXT_SOE ...Start of Statistics at 2018-08-11 14:49:34.DDL replication statistics (for all trails):*** Total statistics since extract started     ***        Operations                                        11.00        Mapped operations                                  2.00        Unmapped operations                                7.00        Other operations                                   2.00        Excluded operations                                0.00Output to ./dirdat/st:Extracting from SOE.ORDERS to SOE.ORDERS:*** Daily statistics since 2018-08-11 14:16:36 ***        Total inserts                                    851.00        Total updates                                    971.00        Total deletes                                      0.00        Total discards                                     0.00        Total operations                                1822.00End of Statistics.目标端OGG监控GGSCI (king02) 1> info rep_soeREPLICAT   REP_SOE   Last Started 2018-08-11 14:21   Status RUNNINGCheckpoint Lag       00:00:00 (updated 00:00:06 ago)Process ID           1890Log Read Checkpoint  File ./dirdat/rt000000004                     2018-08-11 14:23:46.590809  RBA 2188623GGSCI (king02) 2> dblogin userid ggt,password ggtSuccessfully logged into database.GGSCI (king02 as ggt@king) 3> lag rep_soeSending GETLAG request to REPLICAT REP_SOE ...Last record lag 6 seconds.GGSCI (king02 as ggt@king) 4> stats rep_soe,daily,table soe.orders                                                            Sending STATS request to REPLICAT REP_SOE ...Start of Statistics at 2018-08-11 14:50:49.DDL replication statistics:*** Total statistics since replicat started     ***        Operations                                        11.00        Mapped operations                                  1.00        Unmapped operations                                8.00        Other operations                                   2.00        Excluded operations                               10.00        Errors                                             0.00        Retried errors                                     0.00        Discarded errors                                   0.00        Ignored errors                                     0.00Replicating from SOE.ORDERS to SOE.ORDERS:*** Daily statistics since 2018-08-11 14:21:50 ***        Total inserts                                    851.00        Total updates                                    971.00        Total deletes                                      0.00        Total discards                                     0.00        Total operations                                1822.00End of Statistics.