Operating
System : RHEL5 - 64bit.
Database : Oracle Database
11g Enterprise Edition Release 11.2.0.1.0 - Production
PHREMD1 -> P (prev) HR (DB) E (EXTRACT) MD1 ( sid ) PHREMD2 edit params PHREMD1 RHREMD1 ( Replicat ) RHREMD2 edit params RHREMD1 -- --------------------------------------------------- Start a Manager process: Add an Extract group: Add a local trail: Start an Extract group: GGSCI> START MGR GGSCI> ADD EXTRACT myext, TRANLOG, BEGIN NOW GGSCI> ADD EXTTRAIL /ggs/dirdat/lt, EXTRACT myext GGSCI> START EXTRACT myext -- ------------------------------------------------------ ADD EXTRACT finance, TRANLOG, BEGIN 2009-01-31 08:00 -- ------------------------------------------------------ GGSCI> EDIT PARAMS pump EXTRACT pump PASSTHRU RMTHOST1 2 3 4, MGRPORT RMTTRAIL ./dirdat/rt TABLE SALES.ORDERS; TABLE SALES.INVENTORY; GGSCI> ADD EXTRACT pump, EXTTRAILSOURCE ./dirdat/lt GGSCI> ADD RMTTRAIL ./dirdat/rt, EXTRACT pump GGSCI> START EXTRACT pump -- ------------------------------------------------------ GGSCI> DBLOGIN USERID login PASSWORD pw GGSCI> ADD CHECKPOINTTABLE checkpt GGSCO> EDIT PARAMS REPORD REPLICAT REPORD USERID ggsuser PASSWORD ggspass ASSUMETARGETDEFS DISCARDFILE /ggs/dirrpt/REPORD.dsc, APPEND MAP SALES.ORDERS, TARGET USSALES.USORDERS; MAP SALES.INVENTORY, TARGET USSALES.USINVENTORY; GGSCI> ADD REPLICAT REPORD, EXTTRAIL /ggs/dirdat/rt GGSCI> START REPLICAT REPORD -- ------------------------------------------------------- START REPLICAT ATCSN | AFTERCSN a specific transaction commit sequence number (CSN): -- -------------------------------------------------------
Zero Downtime Upgrade of Oracle 10g to Oracle 11g Using GoldenGate - 2 -- ------------------------------------------------------------------------ http://blog.csdn.net/staricqxyz/article/details/31380001 http://blog.csdn.net/staricqxyz/article/details/31380063 http://blog.csdn.net/staricqxyz/article/details/31380169 http://blog.csdn.net/staricqxyz/article/details/31380321 -- ------------------------------------------------------------------------- Zero Downtime Upgrade of Oracle 10g to Oracle 11g Using GoldenGate --- 1 Source Database DB Name: zwc Schemas: HR,OE,PM Version: 10.2.0.4 RAC: No OS Version: Red Hat Enterprise Linux Server release 5.9 x86_64 OGG Version: Oracle GoldenGate V11.2.1.0.6 for Oracle 10g on Linux x86-64 Target Database DB Name: zwc Schemas: HR,OE,PM Version: 11.2.0.2 RAC: Yes Nodes: 2 OS Version: Red Hat Enterprise Linux Server release 5.9 x86_64 OGG Version: Oracle GoldenGate V11.2.1.0.22 for Oracle 11g on Linux x86-64 On 10g Source Database Check Size of Data
SQL> select owner,round(sum(bytes/1024/1024)) MB from dba_segments where owner not in ('SYS','SYSTEM') group by rollup (owner) order by owner;
OWNER MB .......... ...- CTXSYS 5 DBSNMP 2 DMSYS 0 EXFSYS 4 HR 2 IX 2 MDSYS 41 OE 9 OLAPSYS 16 ORDSYS 1 OUTLN 1 PM 3 SCOTT 0 SH 56 SYSMAN 49 TSMSYS 0 WMSYS 7 XDB 49 244 Check Special Data-types SQL> select distinct owner,data_type from dba_tab_cols where owner in ('HR','OE','PM') order by 1,2; OWNER DATA_TYPE .......... ............ HR CHAR HR DATE HR NUMBER HR VARCHAR2 OE ACTIONS_T OE ACTION_V OE CATEGORY_TYP OE CHAR OE CLOB OE CORPORATE_CUSTOMER_TYP OE CUSTOMER_TYP OE CUST_ADDRESS_TYP OE DATE OE INTERVAL YEAR(2) TO MONTH OE INVENTORY_LIST_TYP OE INVENTORY_TYP OE LINEITEMS_T OE LINEITEM_V OE NUMBER OE NVARCHAR2 OE ORDER_ITEM_LIST_TYP OE ORDER_LIST_TYP OE ORDER_TYP OE PHONE_LIST_TYP OE PRODUCT_INFORMATION_TYP OE PRODUCT_REF_LIST_TYP OE PURCHASEORDER_T OE RAW OE REJECTION_T OE SDO_ELEM_INFO_ARRAY OE SDO_GEOMETRY OE SDO_ORDINATE_ARRAY OE SHIPPING_INSTRUCTIONS_T OE SUBCATEGORY_REF_LIST_TYP OE TIMESTAMP(6) WITH LOCAL TIME ZONE OE VARCHAR2 OE WAREHOUSE_TYP OE XDB$RAW_LIST_T OE XMLTYPE OE XMLTYPEEXTRA OE XMLTYPEPI PM ADHEADER_TYP PM BFILE PM BLOB PM CLOB PM DATE PM NCLOB PM NUMBER PM ORDAUDIO PM ORDDOC PM ORDIMAGE PM ORDIMAGESIGNATURE PM ORDVIDEO PM RAW PM TEXTDOC_TAB PM VARCHAR2 Check Cascade Delete Constraints SQL> select owner,table_name,constraint_name,delete_rule,deferred,deferrable,status from dba_constraints where delete_rule<>'NO ACTION' and owner in ('HR','OE','PM'); OWNER TABLE_NAME CONSTRAINT_NAME DELETE_RU DEFERRED DEFERRABLE STATUS .......... .......... .......... ... ... ..... ... OE ORDER_ITEMS ORDER_ITEMS_ORDER_ID_FK CASCADE IMMEDIATE NOT DEFERRABLE ENABLED OE ORDERS ORDERS_CUSTOMER_ID_FK SET NULL IMMEDIATE NOT DEFERRABLE ENABLED OE CUSTOMERS CUSTOMERS_ACCOUNT_MANAGER_FK SET NULL IMMEDIATE NOT DEFERRABLE ENABLED OE WAREHOUSES WAREHOUSES_LOCATION_FK SET NULL IMMEDIATE NOT DEFERRABLE ENABLED OE ORDERS ORDERS_SALES_REP_FK SET NULL IMMEDIATE NOT DEFERRABLE ENABLED Check Triggers SQL> select table_owner,table_name,owner,trigger_name,status,trigger_type from dba_triggers where owner in ('HR','OE','PM'); TABLE_OWNER TABLE_NAME OWNER TRIGGER_NAME STATUS TRIGGER_TYPE .......... .......... .......... .......... ... .....- HR EMPLOYEES HR SECURE_EMPLOYEES DISABLED BEFORE STATEMENT HR EMPLOYEES HR UPDATE_JOB_HISTORY ENABLED AFTER EACH ROW OE ORDER_ITEMS OE INSERT_ORD_LINE ENABLED BEFORE EACH ROW OE PURCHASEORDER OE PURCHASEORDER$xd ENABLED BEFORE EACH ROW OE OC_ORDERS OE ORDERS_TRG ENABLED INSTEAD OF OE OC_ORDERS OE ORDERS_ITEMS_TRG ENABLED INSTEAD OF Check Sequences SQL> select sequence_owner,sequence_name,cycle_flag,increment_by,last_number from dba_sequences where sequence_owner in ('HR','OE','PM'); SEQUENCE_OWNER SEQUENCE_NAME C INCREMENT_BY LAST_NUMBER .......... .......... . .... .... HR LOCATIONS_SEQ N 100 3300 HR DEPARTMENTS_SEQ N 10 280 HR EMPLOYEES_SEQ N 1 207 OE ORDERS_SEQ N 1 1000 Check Services SQL> select name,failover_method,enabled from dba_services; NAME FAILOVER_METHOD ENA .....................- .....................- . SYS$BACKGROUND NO SYS$USERS NO seeddataXDB NO seeddata NO zwcXDB NO zwc NO Check Jobs SQL> select job,schema_user,broken,what,interval from dba_jobs order by schema_user,job; no rows selected SQL> select owner,job_name,enabled,state from dba_scheduler_jobs order by owner,enabled,state; OWNER JOB_NAME ENABL STATE .......... .......... .. ..... EXFSYS RLM$SCHDNEGACTION TRUE SCHEDULED EXFSYS RLM$EVTCLEANUP TRUE SCHEDULED ORACLE_OCM MGMT_CONFIG_JOB TRUE SCHEDULED ORACLE_OCM MGMT_STATS_CONFIG_JOB TRUE SCHEDULED SYS FGR$AUTOPURGE_JOB FALSE DISABLED SYS GATHER_STATS_JOB TRUE SCHEDULED SYS AUTO_SPACE_ADVISOR_JOB TRUE SCHEDULED SYS PURGE_LOG TRUE SCHEDULED Check Character Set SQL> select parameter,value from nls_database_parameters where parameter in ('NLS_CHARACTERSET','NLS_NCHAR_CHARACTERSET'); PARAMETER VALUE .......... ........................... NLS_CHARACTERSET ZHS16GBK NLS_NCHAR_CHARACTERSET AL16UTF16 Install Oracle GoldenGate Configuration Environment for GoldenGate export GG_HOME=/ggs export ORACLE_BASE=/u01/app/oracle export ORACLE_HOME=$ORACLE_BASE/product/10.2.0/db_1 export ORACLE_SID=zwc export PATH=$ORACLE_HOME/bin:$GG_HOME:$PATH export LD_LIBRARY_PATH=$ORACLE_HOME/lib:$$ORACLE_HOME/lib32:$GG_HOME:$LD_LIBRARY_PATH export NLS_LANG=AMERICAN_AMERICA.ZHS16GBK Create GoldenGate Directories [oracle@zwc ggs]$ ggsci Oracle GoldenGate Command Interpreter for Oracle Version 11.2.1.0.6 16211226 OGGCORE_11.2.1.0.6_PLATFORMS_130418.1829_FBO Linux, x64, 64bit (optimized), Oracle 10g on Apr 18 2013 22:43:23 Copyright (C) 1995, 2013, Oracle and/or its affiliates. All rights reserved. GGSCI (zwc) 1> create subdirs Creating subdirectories under current directory /ggs Parameter files /ggs/dirprm: already exists Report files /ggs/dirrpt: created Checkpoint files /ggs/dirchk: created Process status files /ggs/dirpcs: created SQL script files /ggs/dirsql: created Database definitions files /ggs/dirdef: created Extract data files /ggs/dirdat: created Temporary files /ggs/dirtmp: created Stdout files /ggs/dirout: created Create "diroby","dirdsc" directories for OGG obey scripts and discard filesrespectively [oracle@zwc ggs]$ pwd /ggs [oracle@zwc ggs]$ mkdir diroby dirdsc -- ------------------------------------------------------------------------- Zero Downtime Upgrade of Oracle 10g to Oracle 11g Using GoldenGate --- 2 Prepare 10g Database for OGG Create GGS and GGS_MON Database Users SQL> create tablespace ggs_tbs datafile '/u01/app/oracle/oradata/zwc/gg_tbs01.dbf' size 100M; Tablespace created. SQL> create user ggs identified by ggs default tablespace ggs_tbs temporary tablespace temp; User created. SQL> grant dba to ggs; Grant succeeded. SQL> create user ggs_mon identified by ggs_mon default tablespace ggs_tbs temporary tablespace temp; User created. SQL> grant connect,resource to ggs_mon; Grant succeeded. Enable Database Level Supplemental Logging SQL> select name,supplemental_log_data_min from v$database; NAME SUPPLEME ... ... ZWC NO SQL> alter database add supplemental log data; Database altered. SQL> alter system switch logfile; System altered. SQL> select name,supplemental_log_data_min from v$database; NAME SUPPLEME ... ... ZWC YES Enable Force Logging SQL> select force_logging from v$database; FOR . NO SQL> shutdown immediate Database closed. Database dismounted. ORACLE instance shut down. SQL> startup mount ORACLE instance started. Total System Global Area 281018368 bytes Fixed Size 2083336 bytes Variable Size 155190776 bytes Database Buffers 117440512 bytes Redo Buffers 6303744 bytes Database mounted. SQL> alter database force logging; Database altered. SQL> alter database open; Database altered. SQL> select force_logging from v$database; FOR . YES Check Table-Level Supplemental Logging SQL> select t.owner, t.tbl_cnt, s.sup_log_grp_cnt, t.tbl_cnt . s.sup_log_grp_cnt "Diff" from (select owner, count(*) tbl_cnt from dba_tables group by owner) t, (select owner, count(*) sup_log_grp_cnt from dba_log_groups group by owner) s where t.owner = s.owner(+) and t.owner in ('HR', 'OE', 'PM'); OWNER TBL_CNT SUP_LOG_GRP_CNT Diff .. ...- ..... ...- HR 7 PM 2 OE 12 If you are planning to use sqlplus then you can use commands like: alter databaseadd supplemental log data (all) columns; alter database add supplemental log data (primary key) columns; For this demo,we will use Oracle GoldenGate command interface to add table level supplemental logging. The command from ggsci interface is "add trandata ". [oracle@zwc ggs]$ sqlplus ggs SQL*Plus: Release 10.2.0.4.0 . Production on Thu Jun 5 22:01:53 2014 Copyright (c) 1982, 2007, Oracle. All Rights Reserved. Enter password: Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 . 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options SQL> spool add_trandata.oby SQL> set linesize 150 pagesize 0 feedback off SQL> spool add_missing_trandata.oby SQL> select 'add trandata ' || t.owner || '.' || t.table_name stmt from (select owner, table_name from dba_tables) t, (select owner, table_name from dba_log_groups) s where t.owner = s.owner(+) and t.table_name = s.table_name(+) and s.table_name is null and t.owner in ('HR', 'OE', 'PM'); add trandata HR.REGIONS add trandata HR.LOCATIONS add trandata HR.DEPARTMENTS add trandata HR.JOBS add trandata OE.WAREHOUSES add trandata OE.ORDER_ITEMS add trandata OE.ORDERS add trandata OE.PRODUCT_INFORMATION add trandata OE.PROMOTIONS add trandata OE.SYS_IOT_OVER_52810 add trandata OE.SYS_IOT_OVER_52815 add trandata OE.PRODUCT_REF_LIST_NESTEDTAB add trandata OE.SUBCATEGORY_REF_LIST_NESTEDTAB add trandata HR.COUNTRIES add trandata PM.ONLINE_MEDIA add trandata PM.PRINT_MEDIA add trandata OE.CUSTOMERS add trandata HR.JOB_HISTORY add trandata OE.PRODUCT_DESCRIPTIONS add trandata OE.INVENTORIES add trandata HR.EMPLOYEES SQL> spool off [oracle@zwc ggs]$ ggsci Oracle GoldenGate Command Interpreter for Oracle Version 11.2.1.0.6 16211226 OGGCORE_11.2.1.0.6_PLATFORMS_130418.1829_FBO Linux, x64, 64bit (optimized), Oracle 10g on Apr 18 2013 22:43:23 Copyright (C) 1995, 2013, Oracle and/or its affiliates. All rights reserved. GGSCI (zwc) 1> dblogin userid ggs password ggs Successfully logged into database. GGSCI (zwc) 2> obey ./diroby/add_missing_trandata.oby GGSCI (zwc) 3> add trandata HR.REGIONS Logging of supplemental redo data enabled for table HR.REGIONS. GGSCI (zwc) 4> add trandata HR.LOCATIONS Logging of supplemental redo data enabled for table HR.LOCATIONS. GGSCI (zwc) 5> add trandata HR.DEPARTMENTS Logging of supplemental redo data enabled for table HR.DEPARTMENTS. GGSCI (zwc) 6> add trandata HR.JOBS Logging of supplemental redo data enabled for table HR.JOBS. GGSCI (zwc) 7> add trandata OE.WAREHOUSES SQL> select t.owner, t.tbl_cnt, s.sup_log_grp_cnt, t.tbl_cnt . s.sup_log_grp_cnt "Diff" from (select owner, count(*) tbl_cnt from dba_tables group by owner) t, (select owner, count(*) sup_log_grp_cnt from dba_log_groups group by owner) s where t.owner = s.owner(+) and t.owner in ('HR', 'OE', 'PM'); OWNER TBL_CNT SUP_LOG_GRP_CNT Diff .......... ...- ..... ...- HR 7 7 0 OE 12 8 4 PM 2 2 0 Create Tables for Heartbeat SQL> create table ggs_mon.ggs_heartbeat(id number,ts date); Table created. SQL> insert into ggs_mon.ggs_heartbeat values(1,sysdate); 1 row created. SQL> commit; Commit complete. SQL> create table ggs_mon.ggs_lagtime (id number, ts date, committime date, groupname varchar2(8), host varchar2(60), local_insert_time date); Table created. == ============================================================================================= Zero Downtime Upgrade of Oracle 10g to Oracle 11g Using GoldenGate --- 3 DDL Setup Steps SQL> grant execute on utl_file to ggs; Grant succeeded. Create GLOBALS file [oracle@zwc ~]$ cd /ggs/ [oracle@zwc ggs]$ cat GLOBALS GGSCHEMA GGS run marker_setup.sql [oracle@zwc ggs]$ sqlplus / as sysdba SQL*Plus: Release 10.2.0.4.0 . Production on Fri Jun 6 18:56:32 2014 Copyright (c) 1982, 2007, Oracle. All Rights Reserved. Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 . 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options SQL> @marker_setup.sql Marker setup script You 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:ggs Marker setup table script complete, running verification script. Please enter the name of a schema for the GoldenGate database objects: Setting schema name to GGS MARKER TABLE ..........- OK MARKER SEQUENCE ..........- OK Script complete. run ddl_setup.sql SQL> alter system set recyclebin=off; create tablespace ggs datafile '/u01/app/oracle/oradata/zwc/ggs01.dbf' size 100M autoextend on; alter user ggs default tablespace ggs; @ddl_setup.sql Oracle GoldenGate DDL Replication setup script Verifying 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:ggs Working, please wait . Spooling to file ddl_setup_spool.txt Checking 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 GGS CLEAR_TRACE STATUS: Line/pos Error ....... ...................... No errors No errors CREATE_TRACE STATUS: Line/pos Error ....... ...................... No errors No errors TRACE_PUT_LINE STATUS: Line/pos Error ....... ...................... No errors No errors INITIAL_SETUP STATUS: Line/pos Error ....... ...................... No errors No errors DDLVERSIONSPECIFIC PACKAGE STATUS: Line/pos Error ....... ...................... No errors No errors DDLREPLICATION PACKAGE STATUS: Line/pos Error ....... ...................... No errors No errors DDLREPLICATION PACKAGE BODY STATUS: Line/pos Error ....... ...................... No errors No errors DDL IGNORE TABLE ............ OK DDL IGNORE LOG TABLE ............ OK DDLAUX PACKAGE STATUS: Line/pos Error ....... ...................... No errors No errors DDLAUX PACKAGE BODY STATUS: Line/pos Error ....... ...................... No errors No errors SYS.DDLCTXINFO PACKAGE STATUS: Line/pos Error ....... ...................... No errors No errors SYS.DDLCTXINFO PACKAGE BODY STATUS: Line/pos Error ....... ...................... No errors No errors DDL HISTORY TABLE ............ OK DDL HISTORY TABLE(1) ............ OK DDL DUMP TABLES ............ OK DDL DUMP COLUMNS ............ OK DDL DUMP LOG GROUPS ............ OK DDL DUMP PARTITIONS ............ OK DDL DUMP PRIMARY KEYS ............ OK DDL SEQUENCE ............ OK GGS_TEMP_COLS ............ OK GGS_TEMP_UK ............ OK DDL TRIGGER CODE STATUS: Line/pos Error ....... ...................... No errors No errors DDL TRIGGER INSTALL STATUS ............ OK DDL TRIGGER RUNNING STATUS .......................- ENABLED STAYMETADATA IN TRIGGER .......................- OFF DDL TRIGGER SQL TRACING .......................- 0 DDL TRIGGER TRACE LEVEL .......................- 0 LOCATION OF DDL TRACE FILE ........................................ /u01/app/oracle/admin/zwc/udump/ggs_ddl_trace.log Analyzing installation status. STATUS OF DDL REPLICATION ........................................ SUCCESSFUL installation of DDL Replication software components Script complete. run role_setup.sql SQL> @role_setup.sql GGS Role setup script This script will drop and recreate the role GGS_GGSUSER_ROLE To 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:ggs Wrote file role_setup_set.txt PL/SQL procedure successfully completed. Role setup script complete Grant 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. run ddl_enable.sql SQL> @ddl_enable.sql Trigger altered. Run ddl_pin.sql to improve the performance of DDL trigger.In order to run this,make sure dbms_shared_pool package is installed. If not then install that package first,which can be installed by running following script. SQL> @?/rdbms/admin/dbmspool.sql Package created. Grant succeeded. View created. Package body created. run ddl_pin.sql with "ggs" as argument SQL> @ddl_pin.sql ggs PL/SQL procedure successfully completed. PL/SQL procedure successfully completed. PL/SQL procedure successfully completed. Create Parameter Files & Start Manager [oracle@zwc ggs]$ ggsci Oracle GoldenGate Command Interpreter for Oracle Version 11.2.1.0.6 16211226 OGGCORE_11.2.1.0.6_PLATFORMS_130418.1829_FBO Linux, x64, 64bit (optimized), Oracle 10g on Apr 18 2013 22:43:23 Copyright (C) 1995, 2013, Oracle and/or its affiliates. All rights reserved. GGSCI (zwc) 1> view param mgr PORT 7809 DYNAMICPORTLIST 7850-7860 LAGINFOMINUTES 0 LAGREPORTMINUTES 10 LAGCRITICALMINUTES 60 AUTORESTART EXTRACT E*, RETRIES 10, WAITMINUTES 5, RESETMINUTES 1440 AUTORESTART EXTRACT P*, RETRIES 10, WAITMINUTES 5, RESETMINUTES 1440 PURGEOLDEXTRACTS /ggs/trails/source/*, USECHECKPOINTS, MINKEEPDAYS 10, FREQUENCYMINUTES 15 GGSCI (zwc) 2> start mgr Manager started. GGSCI (zwc) 3> info mgr Manager is running (IP port zwc.7809). GGSCI (zwc) 4> info all Program Status Group Lag at Chkpt Time Since Chkpt MANAGER RUNNING GGSCI (zwc) 5> Create directory for trail files mkdir -p /ggs/trails/source Create parameter file for Extract GGSCI (zwc) 8> view param e10gdb EXTRACT E10GDB SETENV (ORACLE_SID=zwc) SETENV (ORACLE_HOME=/u01/app/oracle/product/10.2.0/db_1) SETENV (NLS_LANG="AMERICAN_AMERICA.ZHS16GBK") USERID ggs, PASSWORD ggs EXTTRAIL /ggs/trails/source/aa TRANLOGOPTIONS EXCLUDEUSER GGS TRANLOGOPTIONS LOGRETENTION DISABLED WARNLONGTRANS 3H CHECKINTERVAL 30m REPORTCOUNT EVERY 10 MINUTES, RATE DISCARDFILE /ggs/dirdef/E10GDB.dec, APPEND DISCARDROLLOVER ON SUNDAY GETUPDATEBEFORES DDL INCLUDE MAPPED DDLOPTIONS ADDTRANDATA RETRYOP RETRYDELAY 10 MAXRETRIES 10 TABLE HR.*; TABLE OE.*; TABLE PM.*; SEQUENCE HR.*; SEQUENCE OE.*; SEQUENCE PM.*; Create parameter filefile for Pump GGSCI (zwc) 10> view param p10gdb EXTRACT P10GDB SETENV (ORACLE_SID=zwc) SETENV (ORACLE_HOME=/u01/app/oracle/product/10.2.0/db_1) SETENV (NLS_LANG="AMERICAN_AMERICA.ZHS16GBK") PASSTHRU RMTHOST 192.168.1.61, MGRPORT 7809, & COMPRESS, TCPBUFSIZE 262144, TCPFLUSHBYTES 262144 RMTTRAIL /ggs/trails/target/aa STATOPTIONS REPORTFETCH REPORTCOUNT EVERY 10 MINUTES, RATE DISCARDFILE /ggs/dirdef/P10GDB.dec, APPEND DISCARDROLLOVER ON SUNDAY TABLE HR.*; TABLE OE.*; TABLE PM.*; SEQUENCE HR.*; SEQUENCE OE.*; SEQUENCE PM.*; In 10.2 database,follow there setps to make log_retention enable. SQL> exec dbms_streams_auth.grant_admin_privilege('GGS'); PL/SQL procedure successfully completed. SQL> grant insert on system.logmnr_restart_ckpt$ to ggs; Grant succeeded. SQL> grant update on sys.streams$_capture_process to ggs; Grant succeeded. SQL> grant become user to ggs; Grant succeeded. Add following parameter in the extract.In this demo,we are not enabling log retention. TRANLOGOPTIONS LOGRETENTION SR Add Extract and Pump [oracle@zwc diroby]$ cat add_extract.oby dblogin userid ggs password ggs add extract e10gdb, tranlog, begin now add exttrail /ggs/trails/source/aa, extract e10gdb, megabytes 100 [oracle@zwc diroby]$ cat add_pump.oby add extract p10gdb, exttrailsource /ggs/trails/source/aa, extseqno 0, extrba 0 add rmttrail /ggs/trails/target/aa, extract p10gdb, megabytes 100 [oracle@zwc ggs]$ ggsci Oracle GoldenGate Command Interpreter for Oracle Version 11.2.1.0.6 16211226 OGGCORE_11.2.1.0.6_PLATFORMS_130418.1829_FBO Linux, x64, 64bit (optimized), Oracle 10g on Apr 18 2013 22:43:23 Copyright (C) 1995, 2013, Oracle and/or its affiliates. All rights reserved. GGSCI (zwc) 1> info all Program Status Group Lag at Chkpt Time Since Chkpt MANAGER RUNNING GGSCI (zwc) 2> obey ./diroby/add_extract.oby GGSCI (zwc) 3> dblogin userid ggs password ggs Successfully logged into database. GGSCI (zwc) 4> add extract e10gdb, tranlog, begin now EXTRACT added. GGSCI (zwc) 5> add exttrail /ggs/trails/source/aa, extract e10gdb, megabytes 100 EXTTRAIL added. GGSCI (zwc) 6> obey ./diroby/add_pump.oby GGSCI (zwc) 7> add extract p10gdb, exttrailsource /ggs/trails/source/aa, extseqno 0, extrba 0 EXTRACT added. GGSCI (zwc) 8> add rmttrail /ggs/trails/target/aa, extract p10gdb, megabytes 100 RMTTRAIL added. GGSCI (zwc) 9> info all Program Status Group Lag at Chkpt Time Since Chkpt MANAGER RUNNING EXTRACT STOPPED E10GDB 00:00:00 00:00:14 EXTRACT STOPPED P10GDB 00:00:00 00:00:03 Start only the extract at this point and not the pump GGSCI (zwc) 2> start e10gdb Sending START request to MANAGER . EXTRACT E10GDB starting GGSCI (zwc) 3> info all Program Status Group Lag at Chkpt Time Since Chkpt MANAGER RUNNING EXTRACT STOPPED E10GDB 00:00:00 00:04:12 EXTRACT STOPPED P10GDB 00:00:00 00:04:02 GGSCI (zwc) 4> info all Program Status Group Lag at Chkpt Time Since Chkpt MANAGER RUNNING EXTRACT RUNNING E10GDB 00:04:13 00:00:02 EXTRACT STOPPED P10GDB 00:00:00 00:04:05 Setup Sequence Replication Oracle GoldenGate supports Oracle Sequences replication.There are some limitations with sequence replication. 1.sequence replication in bi-directional is not supported 2.cache size,increment interval,and cycle/no-cycle settings must be identical on source and target databases SQL> @sequence.sql Please enter the name of a schema for the GoldenGate database objects: ggs Setting schema name to GGS UPDATE_SEQUENCE STATUS: Line/pos ....... Error ...................... No errors No errors GETSEQFLUSH Line/pos ....... Error ...................... No errors No errors SEQTRACE Line/pos ....... Error ...................... No errors No errors REPLICATE_SEQUENCE STATUS: Line/pos ....... Error ...................... No errors No errors STATUS OF SEQUENCE SUPPORT ..................... SUCCESSFUL installation of Oracle Sequence Replication support SQL> grant execute on ggs.updateSequence to ggs; Grant succeeded. SQL> grant execute on ggs.replicateSequence to ggs; Grant succeeded. SQL> alter table sys.seq$ add supplemental log data (primary key) columns; Table altered. Restart extract,flush sequence [oracle@zwc diroby]$ cat flush_seq.oby flush sequence hr.* flush sequence oe.* flush sequence pm.* [oracle@zwc ggs]$ ggsci Oracle GoldenGate Command Interpreter for Oracle Version 11.2.1.0.6 16211226 OGGCORE_11.2.1.0.6_PLATFORMS_130418.1829_FBO Linux, x64, 64bit (optimized), Oracle 10g on Apr 18 2013 22:43:23 Copyright (C) 1995, 2013, Oracle and/or its affiliates. All rights reserved. GGSCI (zwc) 1> stop extract e10gdb Sending STOP request to EXTRACT E10GDB . Request processed. GGSCI (zwc) 2> info all Program Status Group Lag at Chkpt Time Since Chkpt MANAGER RUNNING EXTRACT STOPPED E10GDB 00:00:00 00:00:01 EXTRACT STOPPED P10GDB 00:00:00 00:22:40 GGSCI (zwc) 3> start extract e10gdb Sending START request to MANAGER . EXTRACT E10GDB starting GGSCI (zwc) 4> info all Program Status Group Lag at Chkpt Time Since Chkpt MANAGER RUNNING EXTRACT RUNNING E10GDB 00:00:00 00:00:09 EXTRACT STOPPED P10GDB 00:00:00 00:22:47 GGSCI (zwc) 5> dblogin userid ggs password ggs Successfully logged into database. GGSCI (zwc) 6> obey ./diroby/flush_seq.oby GGSCI (zwc) 7> flush sequence hr.* Successfully flushed 3 sequence(s) hr.* GGSCI (zwc) 8> flush sequence oe.* Successfully flushed 1 sequence(s) oe.* GGSCI (zwc) 9> flush sequence pm.* ERROR: Sequence pm.* does not exist. Setup Heartbeat Job In order to start the pump,we need to make target 11g database server ready,by installing Oracle GoldenGate and starting manager.Before we do that lets setup a heartbeat job in cron. The purpose of heartbeat job is to keep updating ggs_mon.ggs_heartbeat table,those transactions will be extracted by Oracle GoldenGate,causing extract RBA to move,and also keep record of lag time in ggs_lagtime table on the target side. [oracle@zwc ~]$ cd /ggs [oracle@zwc ggs]$ mkdir scripts [oracle@zwc ggs]$ touch ggs_heartbeat.sh [oracle@zwc ggs]$ chmod 755 ggs_heartbeat.sh [oracle@zwc ggs]$ cat ggs_heartbeat.sh #!/bin/sh #Purpose: To update heartbeat table for OGG monitoring cd ${HOME} source ${HOME}/.bash_profile > /dev/null 2>&1 echo $GG_HOME cd $GG_HOME DATETIME=`date` echo "Heartbeat for DB ${ORACLE_SID} start at ${DATETIME}.\n" sqlplus -L -S ggs_mon/ggs_mon << EOF 2>&1 update ggs_heartbeat set ts = sysdate; commit; exit EOF Setup cron for this to run every minute * * * * * /ggs/ggs_heartbeat.sh > /tmp/ggshb.log 2>&1 In extract e10gdb parameter file,add following two lines,and restart extract GGSCI (zwc) 15> view param e10gdb EXTRACT E10GDB SETENV (ORACLE_SID=zwc) SETENV (ORACLE_HOME=/u01/app/oracle/product/10.2.0/db_1) SETENV (NLS_LANG="AMERICAN_AMERICA.ZHS16GBK") USERID ggs, PASSWORD ggs EXTTRAIL /ggs/trails/source/aa TRANLOGOPTIONS EXCLUDEUSER GGS TRANLOGOPTIONS LOGRETENTION DISABLED WARNLONGTRANS 3H CHECKINTERVAL 30m REPORTCOUNT EVERY 10 MINUTES, RATE DISCARDFILE /ggs/dirdef/E10GDB.dec, APPEND DISCARDROLLOVER ON SUNDAY GETUPDATEBEFORES DDL INCLUDE MAPPED DDLOPTIONS ADDTRANDATA RETRYOP RETRYDELAY 10 MAXRETRIES 10 TABLE HR.*; TABLE OE.*; TABLE PM.*; SEQUENCE HR.*; SEQUENCE OE.*; SEQUENCE PM.*; TABLE GGS_MON.ggs_heartbeat , Tokens (host = @GETENV ("GGENVIRONMENT", "HOSTNAME") ); In pump p10gdb parameter file,add following line.After editing the pump parameter file,if pump is running then stop and restart. GGSCI (zwc) 18> view param p10gdb EXTRACT P10GDB SETENV (ORACLE_SID=zwc) SETENV (ORACLE_HOME=/u01/app/oracle/product/10.2.0/db_1) SETENV (NLS_LANG="AMERICAN_AMERICA.ZHS16GBK") PASSTHRU RMTHOST 192.168.1.61, MGRPORT 7809, & COMPRESS, TCPBUFSIZE 262144, TCPFLUSHBYTES 262144 RMTTRAIL /ggs/trails/target/aa STATOPTIONS REPORTFETCH REPORTCOUNT EVERY 10 MINUTES, RATE DISCARDFILE /ggs/dirdef/P10GDB.dec, APPEND DISCARDROLLOVER ON SUNDAY TABLE HR.*; TABLE OE.*; TABLE PM.*; SEQUENCE HR.*; SEQUENCE OE.*; SEQUENCE PM.*; TABLE GGS_MON.GGS_HEARTBEAT; Also in replicat we have not created yet,but when we will create replicat parameter file,then we will add following MAP statement in it to make heartbeat work properly. MAP ggs_mon.ggs_heartbeat, TARGET ggs_mon.ggs_lagtime, KEYCOLS (ID), INSERTALLRECORDS, COLMAP (USEDEFAULTS, id = 0, committime = @GETENV ("GGHEADER", "COMMITTIMESTAMP"), groupname = @GETENV ("GGENVIRONMENT", "GROUPNAME"), host = @TOKEN("host"), local_insert_time = @DATENOW() ); Now if we invoke ggsci and run "info" command on extract couple of times after few intervals,we will be able see that its RBA is moving even when there is no application activity going on in the database. GGSCI (zwc) 48> info e10gdb EXTRACT E10GDB Last Started 2014-06-06 21:24 Status RUNNING Checkpoint Lag 00:00:00 (updated 00:00:07 ago) Log Read Checkpoint Oracle Redo Logs 2014-06-06 21:24:36 Seqno 11, RBA 12435456 SCN 0.778809 (778809) GGSCI (zwc) 49> info e10gdb EXTRACT E10GDB Last Started 2014-06-06 21:24 Status RUNNING Checkpoint Lag 00:00:00 (updated 00:00:06 ago) Log Read Checkpoint Oracle Redo Logs 2014-06-06 21:24:54 Seqno 11, RBA 12436992 SCN 0.778815 (778815) == ==================================================================================== Target Side Setup Install OGG on Target Side Creates required directories for OGG [oracle@vzwc1 ggs]$ ./ggsci Oracle GoldenGate Command Interpreter for Oracle Version 11.2.1.0.23 18709404 OGGCORE_11.2.1.0.0OGGBP_PLATFORMS_140523.1046_FBO Linux, x64, 64bit (optimized), Oracle 11g on May 23 2014 15:41:58 Copyright (C) 1995, 2014, Oracle and/or its affiliates. All rights reserved. GGSCI (vzwc1) 1> create subdirs Creating subdirectories under current directory /ggs Parameter files /ggs/dirprm: already exists Report files /ggs/dirrpt: created Checkpoint files /ggs/dirchk: created Process status files /ggs/dirpcs: created SQL script files /ggs/dirsql: created Database definitions files /ggs/dirdef: created Extract data files /ggs/dirdat: created Temporary files /ggs/dirtmp: created Stdout files /ggs/dirout: created Create "diroby","dirdsc","scripts" and "trails" directories for OGG obey scripts,discard files,scripts and trails respectively. [oracle@vzwc1 ~]$ mkdir -p /ggs/diroby [oracle@vzwc1 ~]$ mkdir -p /ggs/dirdsc [oracle@vzwc1 ~]$ mkdir -p /ggs/trails/target [oracle@vzwc1 ~]$ mkdir -p /ggs/trails/source [oracle@vzwc1 ~]$ mkdir -p /ggs/scripts Create Parameter Files and Start Manager [oracle@vzwc1 ggs]$ ./ggsci Oracle GoldenGate Command Interpreter for Oracle Version 11.2.1.0.23 18709404 OGGCORE_11.2.1.0.0OGGBP_PLATFORMS_140523.1046_FBO Linux, x64, 64bit (optimized), Oracle 11g on May 23 2014 15:41:58 Copyright (C) 1995, 2014, Oracle and/or its affiliates. All rights reserved. GGSCI (vzwc1) 1> view param mgr PORT 7809 DYNAMICPORTLIST 7850-7860 LAGINFOMINUTES 0 LAGREPORTMINUTES 10 LAGCRITICALMINUTES 60 AUTORESTART EXTRACT E*, RETRIES 10, WAITMINUTES 5, RESETMINUTES 1440 AUTORESTART EXTRACT P*, RETRIES 10, WAITMINUTES 5, RESETMINUTES 1440 PURGEOLDEXTRACTS /ggs/trails/target/*, USECHECKPOINTS, MINKEEPDAYS 10, FREQUENCYMINUTES 15 GGSCI (vzwc1) 2> info all Program Status Group Lag at Chkpt Time Since Chkpt MANAGER STOPPED GGSCI (vzwc1) 3> start mgr Manager started. GGSCI (vzwc1) 4> info all Program Status Group Lag at Chkpt Time Since Chkpt MANAGER RUNNING GGSCI (vzwc1) 5> info mgr Manager is running (IP port vzwc1.7809). Create GGS and GGS_MON Users [oracle@vzwc1 ~]$ sqlplus / as sysdba SQL*Plus: Release 11.2.0.2.0 Production on Mon Jun 16 12:28:37 2014 Copyright (c) 1982, 2010, Oracle. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 . 64bit Production With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP, Data Mining and Real Application Testing options SQL> create tablespace ggs_tbs datafile '+DATADG' size 100M; Tablespace created. SQL> create user ggs identified by ggs default tablespace ggs_tbs temporary tablespace temp; User created. SQL> create user ggs_mon identified by ggs_mon default tablespace ggs_tbs temporary tablespace temp; User created. SQL> grant dba to ggs; Grant succeeded. SQL> grant connect,resource to ggs_mon; Grant succeeded. SQL> SQL> select name from v$datafile; NAME ........................... +DATADG/zwc/datafile/system.256.849043917 +DATADG/zwc/datafile/sysaux.257.849043921 +DATADG/zwc/datafile/undotbs1.258.849043923 +DATADG/zwc/datafile/users.259.849043925 +DATADG/zwc/datafile/example.264.849044181 +DATADG/zwc/datafile/undotbs2.265.849044603 +DATADG/zwc/datafile/ggs_tbs.269.850393741 7 rows selected. Create GGS_MON Tables SQL> create table ggs_mon.ggs_heartbeat(id number,ts date); Table created. SQL> insert into ggs_mon.ggs_heartbeat values(1,sysdate); 1 row created. SQL> commit; Commit complete. SQL> create table ggs_mon.ggs_lagtime 2 (id number, 3 ts date, 4 committime date, 5 groupname varchar2(8), 6 host varchar2(60), 7 local_insert_time date); Table created. Enable Database Level Supplemental Logging SQL> select supplemental_log_data_min from v$database; SUPPLEME ... NO SQL> alter database add supplemental log data; Database altered. SQL> alter system switch all logfile; System altered. SQL> select supplemental_log_data_min from v$database; SUPPLEME ... YES Start Pump On 10g Server [oracle@zwc ggs]$ ./ggsci Oracle GoldenGate Command Interpreter for Oracle Version 11.2.1.0.6 16211226 OGGCORE_11.2.1.0.6_PLATFORMS_130418.1829_FBO Linux, x64, 64bit (optimized), Oracle 10g on Apr 18 2013 22:43:23 Copyright (C) 1995, 2013, Oracle and/or its affiliates. All rights reserved. GGSCI (zwc) 1> info all Program Status Group Lag at Chkpt Time Since Chkpt MANAGER RUNNING EXTRACT RUNNING E10GDB 00:00:00 00:00:06 EXTRACT STOPPED P10GDB 00:00:00 232:22:33 GGSCI (zwc) 2> start p10gdb Sending START request to MANAGER . EXTRACT P10GDB starting GGSCI (zwc) 3> info all Program Status Group Lag at Chkpt Time Since Chkpt MANAGER RUNNING EXTRACT RUNNING E10GDB 00:00:00 00:00:06 EXTRACT RUNNING P10GDB 00:00:00 00:00:00 GGSCI (zwc) 4> info p10gdb EXTRACT P10GDB Last Started 2014-06-16 12:48 Status RUNNING Checkpoint Lag 00:00:00 (updated 00:00:04 ago) Log Read Checkpoint File /ggs/trails/source/aa000000 First Record RBA 0 We should be able to see trails starting with "aa" in the "/ggs/trails/target" on 11g database server. [oracle@vzwc1 target]$ ls -l /ggs/trails/target/ total 104 -rw-r.. 1 oracle oinstall 100381 Jun 16 12:52 aa000000 Perform Initial Data Load using Expdb/impdb For expdb we need to create directoty on 10g database server [oracle@zwc ~]$ mkdir -p /ggs/zwc/exp [oracle@zwc ~]$ sqlplus / as sysdba SQL*Plus: Release 10.2.0.4.0 . Production on Mon Jun 16 12:55:21 2014 Copyright (c) 1982, 2007, Oracle. All Rights Reserved. Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 . 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options SQL> create directory exp_ggs_dir as '/ggs/zwc/exp'; Directory created. SQL> grant read,write on directory exp_ggs_dir to ggs; Grant succeeded. Create parameter file for export.But before we create parameter file,one thing we need to carefully understand here is that we must get a consistent export by using FLASHBACK_SCN parameter of expdb utility. SQL> select nvl((select max(username) from gv$session s, gv$transaction t where t.ses_addr = s.saddr and t.inst_id = s.inst_id), 'Get this SCN for Export') transaction_dbuser, (select to_char(current_scn) from v$database) scn from dual / TRANSACTION_DBUSER SCN .......... .............- Get this SCN for Export 834116 [oracle@zwc exp]$ cat expdp_10g.par userid=ggs/ggs flashback_scn=834116 job_name=GG_10G_EXPORT directory=EXP_GGS_DIR dumpfile=expdp_gg_10g_%U.dmp logfile=expdp_gg_10g.log parallel=2 SCHEMAS=HR,OE,PM Start export by issuing the following command [oracle@zwc exp]$ nohup expdp parfile=expdp_10g.par & [1] 7949 [oracle@zwc exp]$ nohup: appending output to `nohup.out' [oracle@zwc exp]$ tail -f nohup.out Export: Release 10.2.0.4.0 . 64bit Production on Monday, 16 June, 2014 13:09:15 Copyright (c) 2003, 2007, Oracle. All rights reserved. Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 . 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options FLASHBACK automatically enabled to preserve database integrity. Starting "GGS"."GG_10G_EXPORT": parfile=expdp_10g.par Estimate in progress using BLOCKS method. Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA ORA-39139: Data Pump does not support XMLSchema objects. TABLE_DATA:"OE"."PURCHASEORDER" will be skipped. Total estimation using BLOCKS method: 6.625 MB Processing object type SCHEMA_EXPORT/USER Processing object type SCHEMA_EXPORT/SYSTEM_GRANT Processing object type SCHEMA_EXPORT/ROLE_GRANT Processing object type SCHEMA_EXPORT/DEFAULT_ROLE . . exported "OE"."PRODUCT_DESCRIPTIONS" 2.379 MB 8640 rows . . exported "PM"."ONLINE_MEDIA" 39.24 KB 9 rows Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA Processing object type SCHEMA_EXPORT/SYNONYM/SYNONYM Processing object type SCHEMA_EXPORT/TYPE/INC_TYPE Processing object type SCHEMA_EXPORT/TYPE/TYPE_SPEC Processing object type SCHEMA_EXPORT/SEQUENCE/SEQUENCE Processing object type SCHEMA_EXPORT/TABLE/TABLE Processing object type SCHEMA_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS Processing object type SCHEMA_EXPORT/TABLE/COMMENT Processing object type SCHEMA_EXPORT/TABLE/RLS_POLICY . . exported "PM"."PRINT_MEDIA" 188.8 KB 4 rows Processing object type SCHEMA_EXPORT/FUNCTION/FUNCTION Processing object type SCHEMA_EXPORT/PROCEDURE/PROCEDURE Processing object type SCHEMA_EXPORT/FUNCTION/ALTER_FUNCTION Processing object type SCHEMA_EXPORT/PROCEDURE/ALTER_PROCEDURE . . exported "OE"."LINEITEM_TABLE" 283.5 KB 2232 rows . . exported "OE"."CUSTOMERS" 75.22 KB 319 rows Processing object type SCHEMA_EXPORT/VIEW/VIEW Processing object type SCHEMA_EXPORT/VIEW/GRANT/OWNER_GRANT/OBJECT_GRANT Processing object type SCHEMA_EXPORT/TYPE/TYPE_BODY Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT Processing object type SCHEMA_EXPORT/TABLE/TRIGGER Processing object type SCHEMA_EXPORT/VIEW/TRIGGER Processing object type SCHEMA_EXPORT/TABLE/INDEX/FUNCTIONAL_AND_BITMAP/INDEX Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/FUNCTIONAL_AND_BITMAP/INDEX_STATISTICS . . exported "OE"."WAREHOUSES" 12.61 KB 9 rows Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS . . exported "PM"."TEXTDOCS_NESTEDTAB" 87.35 KB 12 rows . . exported "OE"."PRODUCT_INFORMATION" 71.73 KB 288 rows . . exported "OE"."ACTION_TABLE" 14.87 KB 132 rows . . exported "HR"."COUNTRIES" 6.085 KB 25 rows . . exported "HR"."DEPARTMENTS" 6.632 KB 27 rows . . exported "HR"."EMPLOYEES" 15.76 KB 107 rows . . exported "HR"."JOBS" 6.609 KB 19 rows . . exported "HR"."JOB_HISTORY" 6.585 KB 10 rows . . exported "HR"."LOCATIONS" 7.710 KB 23 rows . . exported "HR"."REGIONS" 5.289 KB 4 rows . . exported "OE"."PRODUCT_REF_LIST_NESTEDTAB" 12.32 KB 288 rows . . exported "OE"."CATEGORIES_TAB" 13.12 KB 22 rows . . exported "OE"."SUBCATEGORY_REF_LIST_NESTEDTAB" 6.398 KB 21 rows . . exported "OE"."INVENTORIES" 21.39 KB 1112 rows . . exported "OE"."ORDERS" 11.64 KB 105 rows . . exported "OE"."ORDER_ITEMS" 20.41 KB 665 rows . . exported "OE"."PROMOTIONS" 5.312 KB 2 rows Master table "GGS"."GG_10G_EXPORT" successfully loaded/unloaded ****************************************************************************** Dump file set for GGS.GG_10G_EXPORT is: /ggs/zwc/exp/expdp_gg_10g_01.dmp /ggs/zwc/exp/expdp_gg_10g_02.dmp Job "GGS"."GG_10G_EXPORT" completed with 1 error(s) at 13:10:04 Once export is finished,then transfer dump files to the 11g database server.Then create exp_ggs_dir directoty,and grant read/write privlege to "GGS" user.For initiating import we will be using "GGS" user. [oracle@vzwc1 target]$ mkdir -p /ggs/zwc/imp [oracle@vzwc1 target]$ sqlplus / as sysdba SQL*Plus: Release 11.2.0.2.0 Production on Mon Jun 16 13:15:59 2014 Copyright (c) 1982, 2010, Oracle. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 . 64bit Production With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP, Data Mining and Real Application Testing options SQL> create directory exp_ggs_dir as '/ggs/zwc/imp'; Directory created. SQL> grant read,write on directory exp_ggs_dir to ggs; Grant succeeded. Create parameter file for import but do not start inport right away.We need to check jobs,which we will do after creating import parameter file. [oracle@vzwc1 imp]$ cat impdp_11g.par userid=ggs/ggs job_name=GG_11G_IMPORT directory=EXP_GGS_DIR dumpfile=expdp_gg_10g_%U.dmp logfile=impdp_gg_11g.log SCHEMAS=HR,OE,PM Set job_queue_processes to Zero [oracle@vzwc1 imp]$ sqlplus / as sysdba SQL*Plus: Release 11.2.0.2.0 Production on Mon Jun 16 13:21:55 2014 Copyright (c) 1982, 2010, Oracle. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 . 64bit Production With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP, Data Mining and Real Application Testing options SQL> show parameter job_queue_process NAME TYPE VALUE ............ .... .......... job_queue_processes integer 1000 SQL> alter system set job_queue_processes=0 scope=both sid='*'; System altered. SQL> show parameter job_queue_process NAME TYPE VALUE ............ .... .......... job_queue_processes integer 0 Start Import in 11g Database [oracle@vzwc1 imp]$ impdp parfile=impdp_11g.par Import: Release 11.2.0.2.0 . Production on Mon Jun 16 13:28:40 2014 Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 . 64bit Production With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP, Data Mining and Real Application Testing options Master table "GGS"."GG_11G_IMPORT" successfully loaded/unloaded Starting "GGS"."GG_11G_IMPORT": ggs/******** parfile=impdp_11g.par Processing object type SCHEMA_EXPORT/USER Processing object type SCHEMA_EXPORT/SYSTEM_GRANT Processing object type SCHEMA_EXPORT/ROLE_GRANT Processing object type SCHEMA_EXPORT/DEFAULT_ROLE Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA Processing object type SCHEMA_EXPORT/SYNONYM/SYNONYM Processing object type SCHEMA_EXPORT/TYPE/INC_TYPE Processing object type SCHEMA_EXPORT/TYPE/TYPE_SPEC Processing object type SCHEMA_EXPORT/SEQUENCE/SEQUENCE Processing object type SCHEMA_EXPORT/TABLE/TABLE Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA ORA-39034: Table TABLE_DATA:"OE"."ACTION_TABLE" does not exist. ORA-39034: Table TABLE_DATA:"OE"."LINEITEM_TABLE" does not exist. . . imported "OE"."PRODUCT_DESCRIPTIONS" 2.379 MB 8640 rows . . imported "PM"."ONLINE_MEDIA" 39.24 KB 9 rows . . imported "PM"."PRINT_MEDIA" 188.8 KB 4 rows . . imported "OE"."CUSTOMERS" 75.22 KB 319 rows . . imported "OE"."WAREHOUSES" 12.61 KB 9 rows . . imported "PM"."TEXTDOCS_NESTEDTAB" 87.35 KB 12 rows . . imported "OE"."PRODUCT_INFORMATION" 71.73 KB 288 rows . . imported "HR"."COUNTRIES" 6.085 KB 25 rows . . imported "HR"."DEPARTMENTS" 6.632 KB 27 rows . . imported "HR"."EMPLOYEES" 15.76 KB 107 rows . . imported "HR"."JOBS" 6.609 KB 19 rows . . imported "HR"."JOB_HISTORY" 6.585 KB 10 rows . . imported "HR"."LOCATIONS" 7.710 KB 23 rows . . imported "HR"."REGIONS" 5.289 KB 4 rows . . imported "OE"."PRODUCT_REF_LIST_NESTEDTAB" 12.32 KB 288 rows . . imported "OE"."CATEGORIES_TAB" 13.12 KB 22 rows . . imported "OE"."SUBCATEGORY_REF_LIST_NESTEDTAB" 6.398 KB 21 rows . . imported "OE"."INVENTORIES" 21.39 KB 1112 rows . . imported "OE"."ORDERS" 11.64 KB 105 rows . . imported "OE"."ORDER_ITEMS" 20.41 KB 665 rows . . imported "OE"."PROMOTIONS" 5.312 KB 2 rows Processing object type SCHEMA_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS Disable Jobs select job,schema_user,broken,what from dba_jobs; Then you can use following PL/SQL block to disable jobs one by one which you find from above query. begin dbms_job.broken(jobid,TRUE,''); end; / For enabling a DBMS_JOB,following PL/SQL block can be used begin dbms_job.broken(jobid,FALSE,''); end; / Run the following query to find out about DBMS_SCHEDULER jobs in a database. select owner, job_name, state, enabled from dba_scheduler_jobs where owner in ('HR', 'OE', 'PM') and enabled = 'TRUE' order by state, 1 / Script for generating script for Disabling Scheduler Jobs. set head off set feedback off set linesize 100 spool disable_jobs_11g.sql select 'exec dbms_scheduler.disable (name => ' || '''' || owner || '.' || JOB_NAME || '''' || ');' stmt from dba_scheduler_jobs where owner in ('HR', 'OE', 'PM') and state <> 'DISABLED'; spool off Script for generating script for Enabling Scheduler Jobs. set head off set feedback off set linesize 100 spool enable_jobs_11g.sql select 'exec dbms_scheduler.enable (name => ' || '''' || owner || '.' || JOB_NAME || '''' || ');' stmt from dba_scheduler_jobs where owner in ('HR', 'OE', 'PM') and state <> 'DISABLED'; spool off Chcek Invalid Objects select owner, object_type, object_name, status from dba_objects where owner in ('HR', 'OE', 'PM') and status <> 'VALID' / Disable Constraints select owner, table_name, constraint_name, constraint_type, delete_rule, status from dba_constraints where constraint_type = 'R' and delete_rule <> 'NO ACTION' and owner in ('HR', 'OE', 'PM') and status = 'ENABLED'; Script for generating script for Disabling Constraints. set head off set pagesize 600 set feedback off set linesize 100 spool disable_constraints_11g.sql select 'alter table ' || owner || '.' || table_name || ' disable constraint ' || constraint_name || ' ;' from dba_constraints where constraint_type = 'R' and delete_rule <> 'NO ACTION' and owner in ('HR', 'OE', 'PM') and status = 'ENABLED' order by status, owner, table_name, constraint_name; spool off Script for generating script for Enabling Constraints. set head off set pagesize 600 set feedback off set linesize 100 spool enable_constraints_11g.sql select 'alter table ' || owner || '.' || table_name || ' enable constraint ' || constraint_name || ' ;' from dba_constraints where constraint_type = 'R' and delete_rule <> 'NO ACTION' and owner in ('HR', 'OE', 'PM') and status = 'ENABLED' order by status, owner, table_name, constraint_name; spool off Disable Triggers select owner, trigger_name, status from dba_triggers where owner in ('HR', 'OE', 'PM') and status = 'ENABLED'; Script for generating script for Disabling Triggers. set head off set feedback off set linesize 100 spool disable_triggers_11g.sql select 'alter trigger ' || owner || '.' || trigger_name || ' disable;' from dba_triggers where owner in ('HR', 'OE', 'PM') and status = 'ENABLED' order by owner, trigger_name; spool off Script for generating script for Enabling Triggers. set head off set feedback off set linesize 100 spool enable_triggers_11g.sql select 'alter trigger ' || owner || '.' || trigger_name || ' enable;' from dba_triggers where owner in ('HR', 'OE', 'PM') and status = 'ENABLED' order by owner, trigger_name; spool off Start Replicat Add checkpoint table,and add checkpoint table to ./GLOBALS file. [oracle@vzwc1 ggs]$ ./ggsci Oracle GoldenGate Command Interpreter for Oracle Version 11.2.1.0.23 18709404 OGGCORE_11.2.1.0.0OGGBP_PLATFORMS_140523.1046_FBO Linux, x64, 64bit (optimized), Oracle 11g on May 23 2014 15:41:58 Copyright (C) 1995, 2014, Oracle and/or its affiliates. All rights reserved. GGSCI (vzwc1) 1> dblogin userid ggs, password ggs Successfully logged into database. GGSCI (vzwc1) 2> add checkpointtable ggs.chkpoint_table Successfully created checkpoint table ggs.chkpoint_table. GGSCI (vzwc1) 5> view param ./GLOBALS CHECKPOINTTABLE ggs.chkpoint_table For adding a replicat invoke ggsci interface,and create parameter file fot replicat. GGSCI (vzwc1) 10> view param r10gdb REPLICAT R10GDB SETENV (ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1) SETENV (NLS_LANG="AMERICAN_AMERICA.ZHS16GBK") USERID ggs@ZWC, PASSWORD ggs ASSUMETARGETDEFS REPORTCOUNT EVERY 10 MINUTES, RATE DISCARDFILE /ggs/dirdsc/R10GDB.dsc, APPEND, MEGABYTES 100 DISCARDROLLOVER ON SUNDAY REPERROR (default, abend) DDLERROR default abend DYNAMICRESOLUTION GROUPTRANSOPS 2000 DBOPTIONS SUPPRESSTRIGGERS DDL INCLUDE MAPPED DDLOPTIONS REPORT MAP HR.*, TARGET HR.*; MAP OE.*, TARGET OE.*; MAP PM.*, TARGET PM.*; .add for heartbeat MAP ggs_mon.ggs_heartbeat, TARGET ggs_mon.ggs_lagtime, KEYCOLS (ID), INSERTALLRECORDS, COLMAP (USEDEFAULTS, id = 0, committime = @GETENV ("GGHEADER", "COMMITTIMESTAMP"), groupname = @GETENV ("GGENVIRONMENT", "GROUPNAME"), host = @TOKEN("host"), local_insert_time = @DATENOW() ); GGSCI (vzwc1) 11> dblogin userid ggs, password ggs Successfully logged into database. GGSCI (vzwc1) 13> add replicat r10gdb, exttrail /ggs/trails/target/aa, checkpointtable ggs.chkpoint_table, extseqno 0, extrba 0 REPLICAT added. GGSCI (vzwc1) 14> start replicat r10gdb atcsn 834116 Sending START request to MANAGER . REPLICAT R10GDB starting GGSCI (vzwc1) 15> info all Program Status Group Lag at Chkpt Time Since Chkpt MANAGER RUNNING REPLICAT RUNNING R10GDB 01:24:49 00:00:01 Simple DML test On 10g database server [oracle@zwc ~]$ sqlplus / as sysdba SQL*Plus: Release 10.2.0.4.0 . Production on Mon Jun 16 14:52:53 2014 Copyright (c) 1982, 2007, Oracle. All Rights Reserved. Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 . 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options SQL> delete from hr.employees where rownum<=2; 2 row deleted. SQL> commit; Commit complete. GSCI (zwc) 15> stats e10gdb Sending STATS request to EXTRACT E10GDB . Start of Statistics at 2014-06-16 14:53:26. DDL replication statistics (for all trails): *** Total statistics since extract started *** Operations 3.00 Mapped operations 0.00 Unmapped operations 1.00 Other operations 2.00 Excluded operations 3.00 Output to /ggs/trails/source/aa: *** Total statistics since 2014-06-16 11:28:07 *** No database operations have been performed. *** Daily statistics since 2014-06-16 11:28:07 *** No database operations have been performed. *** Hourly statistics since 2014-06-16 14:00:00 *** No database operations have been performed. *** Latest statistics since 2014-06-16 11:28:07 *** No database operations have been performed. *** Total statistics since 2014-06-16 11:28:07 *** No database operations have been performed. *** Daily statistics since 2014-06-16 11:28:07 *** No database operations have been performed. *** Hourly statistics since 2014-06-16 14:00:00 *** No database operations have been performed. *** Latest statistics since 2014-06-16 11:28:07 *** No database operations have been performed. *** Total statistics since 2014-06-16 11:28:07 *** No database operations have been performed. *** Daily statistics since 2014-06-16 11:28:07 *** No database operations have been performed. *** Hourly statistics since 2014-06-16 14:00:00 *** No database operations have been performed. *** Latest statistics since 2014-06-16 11:28:07 *** No database operations have been performed. Extracting from GGS_MON.GGS_HEARTBEAT to GGS_MON.GGS_HEARTBEAT: *** Total statistics since 2014-06-16 11:28:07 *** Total inserts 0.00 Total updates 129.00 Total befores 129.00 Total deletes 0.00 Total discards 0.00 Total operations 129.00 *** Daily statistics since 2014-06-16 11:28:07 *** Total inserts 0.00 Total updates 129.00 Total befores 129.00 Total deletes 0.00 Total discards 0.00 Total operations 129.00 *** Hourly statistics since 2014-06-16 14:00:00 *** Total inserts 0.00 Total updates 54.00 Total befores 54.00 Total deletes 0.00 Total discards 0.00 Total operations 54.00 *** Latest statistics since 2014-06-16 11:28:07 *** Total inserts 0.00 Total updates 129.00 Total befores 129.00 Total deletes 0.00 Total discards 0.00 Total operations 129.00 Extracting from GGS.GGS_MARKER to GGS.GGS_MARKER: *** Total statistics since 2014-06-16 11:28:07 *** No database operations have been performed. *** Daily statistics since 2014-06-16 11:28:07 *** No database operations have been performed. *** Hourly statistics since 2014-06-16 14:00:00 *** No database operations have been performed. *** Latest statistics since 2014-06-16 11:28:07 *** No database operations have been performed. Extracting from HR.EMPLOYEES to HR.EMPLOYEES: *** Total statistics since 2014-06-16 11:28:07 *** Total inserts 0.00 Total updates 0.00 Total deletes 2.00 Total discards 0.00 Total operations 2.00 *** Daily statistics since 2014-06-16 11:28:07 *** Total inserts 0.00 Total updates 0.00 Total deletes 2.00 Total discards 0.00 Total operations 2.00 *** Hourly statistics since 2014-06-16 14:00:00 *** Total inserts 0.00 Total updates 0.00 Total deletes 2.00 Total discards 0.00 Total operations 2.00 *** Latest statistics since 2014-06-16 11:28:07 *** Total inserts 0.00 Total updates 0.00 Total deletes 2.00 Total discards 0.00 Total operations 2.00 End of Statistics. GGSCI (zwc) 16> GGSCI (zwc) 16> stats p10gdb Sending STATS request to EXTRACT P10GDB . Start of Statistics at 2014-06-16 14:53:38. Output to /ggs/trails/target/aa: *** Total statistics since 2014-06-16 12:48:55 *** No database operations have been performed. *** Daily statistics since 2014-06-16 12:48:55 *** No database operations have been performed. *** Hourly statistics since 2014-06-16 14:00:00 *** No database operations have been performed. *** Latest statistics since 2014-06-16 12:48:55 *** No database operations have been performed. *** Total statistics since 2014-06-16 12:48:55 *** No database operations have been performed. *** Daily statistics since 2014-06-16 12:48:55 *** No database operations have been performed. *** Hourly statistics since 2014-06-16 14:00:00 *** No database operations have been performed. *** Latest statistics since 2014-06-16 12:48:55 *** No database operations have been performed. *** Total statistics since 2014-06-16 12:48:55 *** No database operations have been performed. *** Daily statistics since 2014-06-16 12:48:55 *** No database operations have been performed. *** Hourly statistics since 2014-06-16 14:00:00 *** No database operations have been performed. *** Latest statistics since 2014-06-16 12:48:55 *** No database operations have been performed. Extracting from HR.LOCATIONS_SEQ to HR.LOCATIONS_SEQ: *** Total statistics since 2014-06-16 12:48:55 *** Total updates 2.00 Total discards 0.00 Total operations 2.00 *** Daily statistics since 2014-06-16 12:48:55 *** Total updates 2.00 Total discards 0.00 Total operations 2.00 *** Hourly statistics since 2014-06-16 14:00:00 *** No database operations have been performed. *** Latest statistics since 2014-06-16 12:48:55 *** Total updates 2.00 Total discards 0.00 Total operations 2.00 Extracting from HR.DEPARTMENTS_SEQ to HR.DEPARTMENTS_SEQ: *** Total statistics since 2014-06-16 12:48:55 *** Total updates 2.00 Total discards 0.00 Total operations 2.00 *** Daily statistics since 2014-06-16 12:48:55 *** Total updates 2.00 Total discards 0.00 Total operations 2.00 *** Hourly statistics since 2014-06-16 14:00:00 *** No database operations have been performed. *** Latest statistics since 2014-06-16 12:48:55 *** Total updates 2.00 Total discards 0.00 Total operations 2.00 Extracting from HR.EMPLOYEES_SEQ to HR.EMPLOYEES_SEQ: *** Total statistics since 2014-06-16 12:48:55 *** Total updates 2.00 Total discards 0.00 Total operations 2.00 *** Daily statistics since 2014-06-16 12:48:55 *** Total updates 2.00 Total discards 0.00 Total operations 2.00 *** Hourly statistics since 2014-06-16 14:00:00 *** No database operations have been performed. *** Latest statistics since 2014-06-16 12:48:55 *** Total updates 2.00 Total discards 0.00 Total operations 2.00 Extracting from OE.ORDERS_SEQ to OE.ORDERS_SEQ: *** Total statistics since 2014-06-16 12:48:55 *** Total updates 2.00 Total discards 0.00 Total operations 2.00 *** Daily statistics since 2014-06-16 12:48:55 *** Total updates 2.00 Total discards 0.00 Total operations 2.00 *** Hourly statistics since 2014-06-16 14:00:00 *** No database operations have been performed. *** Latest statistics since 2014-06-16 12:48:55 *** Total updates 2.00 Total discards 0.00 Total operations 2.00 Extracting from GGS_MON.GGS_HEARTBEAT to GGS_MON.GGS_HEARTBEAT: *** Total statistics since 2014-06-16 12:48:55 *** Total inserts 0.00 Total updates 401.00 Total befores 401.00 Total deletes 0.00 Total discards 0.00 Total operations 401.00 *** Daily statistics since 2014-06-16 12:48:55 *** Total inserts 0.00 Total updates 401.00 Total befores 401.00 Total deletes 0.00 Total discards 0.00 Total operations 401.00 *** Hourly statistics since 2014-06-16 14:00:00 *** Total inserts 0.00 Total updates 54.00 Total befores 54.00 Total deletes 0.00 Total discards 0.00 Total operations 54.00 *** Latest statistics since 2014-06-16 12:48:55 *** Total inserts 0.00 Total updates 401.00 Total befores 401.00 Total deletes 0.00 Total discards 0.00 Total operations 401.00 Extracting from HR.EMPLOYEES to HR.EMPLOYEES: *** Total statistics since 2014-06-16 12:48:55 *** Total inserts 0.00 Total updates 0.00 Total deletes 2.00 Total discards 0.00 Total operations 2.00 *** Daily statistics since 2014-06-16 12:48:55 *** Total inserts 0.00 Total updates 0.00 Total deletes 2.00 Total discards 0.00 Total operations 2.00 *** Hourly statistics since 2014-06-16 14:00:00 *** Total inserts 0.00 Total updates 0.00 Total deletes 2.00 Total discards 0.00 Total operations 2.00 *** Latest statistics since 2014-06-16 12:48:55 *** Total inserts 0.00 Total updates 0.00 Total deletes 2.00 Total discards 0.00 Total operations 2.00 End of Statistics. On 11g database server GGSCI (vzwc1) 38> stats r10gdb Sending STATS request to REPLICAT R10GDB . Start of Statistics at 2014-06-16 14:59:05. DDL replication statistics: *** Total statistics since replicat started *** Operations 0.00 Mapped operations 0.00 Unmapped operations 0.00 Other operations 0.00 Excluded operations 0.00 Errors 0.00 Retried errors 0.00 Discarded errors 0.00 Ignored errors 0.00 Replicating from GGS_MON.GGS_HEARTBEAT to GGS_MON.GGS_LAGTIME: *** Total statistics since 2014-06-16 14:29:53 *** Total inserts 0.00 Total updates 114.00 Total deletes 0.00 Total discards 0.00 Total operations 114.00 *** Daily statistics since 2014-06-16 14:29:53 *** Total inserts 0.00 Total updates 114.00 Total deletes 0.00 Total discards 0.00 Total operations 114.00 *** Hourly statistics since 2014-06-16 14:29:53 *** Total inserts 0.00 Total updates 114.00 Total deletes 0.00 Total discards 0.00 Total operations 114.00 *** Latest statistics since 2014-06-16 14:29:53 *** Total inserts 0.00 Total updates 114.00 Total deletes 0.00 Total discards 0.00 Total operations 114.00 Replicating from HR.EMPLOYEES to HR.EMPLOYEES: *** Total statistics since 2014-06-16 14:29:53 *** Total inserts 0.00 Total updates 0.00 Total deletes 2.00 Total discards 0.00 Total operations 2.00 *** Daily statistics since 2014-06-16 14:29:53 *** Total inserts 0.00 Total updates 0.00 Total deletes 2.00 Total discards 0.00 Total operations 2.00 *** Hourly statistics since 2014-06-16 14:29:53 *** Total inserts 0.00 Total updates 0.00 Total deletes 2.00 Total discards 0.00 Total operations 2.00 *** Latest statistics since 2014-06-16 14:29:53 *** Total inserts 0.00 Total updates 0.00 Total deletes 2.00 Total discards 0.00 Total operations 2.00 End of Statistics. == ============================================================================
STEP
1 :- Installation of
GoldenGate Software by unzipping/untar the tar file.
**************************** SOURCE ***********************
[oracle@rac3 goldengate]$ cd /home/oracle/gg
[oracle@rac3 gg]$ ls
fbo_ggs_Linux_x86_ora11g_32bit.tar
[oracle@rac3 gg]$ tar -xvf fbo_ggs_Linux_x86_ora11g_32bit.tar
**************************** TARGET ***********************
[oracle@rac3 goldengate]$ cd /home/oracle/gg
[oracle@rac3 gg]$ ls
fbo_ggs_Linux_x86_ora11g_32bit.tar
[oracle@rac3 gg]$ tar -xvf fbo_ggs_Linux_x86_ora11g_32bit.tar
STEP
2:- It is mandatory to
setup the Path, LD_Library_path to execute ggsci command else you will receive
the highlighted error below.
**************************** SOURCE
***********************
[oracle@rac3 bin-D_H]$export PATH=$PATH:/home/oracle/gg
[oracle@rac3 bin-D_H]$export LD_LIBRARY_PATH=$ORACLE_HOME/lib/:/home/oracle/gg
[oracle@rac3 bin-D_H]$cd /home/oracle/gg
[oracle@rac3 gg-D_H]$./ggsci
Oracle GoldenGate Command
Interpreter for Oracle
Version 11.2.1.0.1
OGGCORE_11.2.1.0.1_PLATFORMS_120423.0230_FBO
Linux, x86, 32bit
(optimized), Oracle 11g on Apr 23 2012 08:09:25
Copyright (C) 1995, 2012,
Oracle and/or its affiliates. All rights reserved.
GGSCI (rac3.sukku.com)
1>
***************************** TARGET
***********************
[oracle@rac4 gg-D_H]$./ggsci
./ggsci: error while loading
shared libraries: libnnz11.so: cannot open shared object file: No such file or
directory
[oracle@rac4
gg-D_H]$export PATH=$PATH:/home/oracle/gg
[oracle@rac4
gg-D_H]$export LD_LIBRARY_PATH=$ORACLE_HOME/lib/:/home/oracle/gg
[oracle@rac4 gg-D_H]$pwd
/home/oracle/gg
[oracle@rac4
gg-D_H]$./ggsci
Oracle GoldenGate Command
Interpreter for Oracle
Version 11.2.1.0.1
OGGCORE_11.2.1.0.1_PLATFORMS_120423.0230_FBO
Linux, x86, 32bit
(optimized), Oracle 11g on Apr 23 2012 08:09:25
Copyright (C) 1995, 2012,
Oracle and/or its affiliates. All rights reserved.
GGSCI (rac4.sukku.com)
1>
STEP
3:- it is mandatory to
create sub directories by using “create
subdirs” command at ggsci
prompt.
**************************** SOURCE ***********************
GGSCI (rac3.sukku.com)
1> create subdirs
Creating subdirectories
under current directory /home/oracle/gg
Parameter files /home/oracle/gg/dirprm: already
exists
Report files /home/oracle/gg/dirrpt:
created
Checkpoint files /home/oracle/gg/dirchk: created
Process status files /home/oracle/gg/dirpcs: created
SQL script files /home/oracle/gg/dirsql: created
Database definitions
files /home/oracle/gg/dirdef: created
Extract data files /home/oracle/gg/dirdat: created
Temporary files /home/oracle/gg/dirtmp: created
Stdout files /home/oracle/gg/dirout:
created
***************************** TARGET ***********************
GGSCI (rac4.sukku.com)
1> create subdirs
Creating subdirectories
under current directory /home/oracle/gg
Parameter files /home/oracle/gg/dirprm: already
exists
Report files /home/oracle/gg/dirrpt:
created
Checkpoint files /home/oracle/gg/dirchk: created
Process status files /home/oracle/gg/dirpcs: created
SQL script files /home/oracle/gg/dirsql: created
Database definitions
files /home/oracle/gg/dirdef: created
Extract data files /home/oracle/gg/dirdat: created
Temporary files /home/oracle/gg/dirtmp: created
Stdout files /home/oracle/gg/dirout:
created
STEP
4:- create a user at
database level to access DB from ggsci.
**************************** SOURCE
***********************
SQL> grant connect,resource, dba, select any dictionary, select any
table, create table, flashback any table, execute on dbms_flashback, execute on
utl_file to ggs identified by ggs;
Grant succeeded.
SQL> conn ggs/ggs
Connected.
***************************** TARGET
***********************
SQL> grant
connect,resource, dba, select any dictionary, select any table, create table,
flashback any table, execute on dbms_flashback, execute on utl_file to ggs
identified by ggs;
Grant succeeded.
SQL> conn ggt/ggt
Connected.
STEP
5:- It is mandatory to
login to database to access date from ggsci
**************************** SOURCE
***********************
GGSCI (rac3.sukku.com)
2> dblogin userid ggs, password ggs
Successfully logged into
database.
***************************** TARGET
***********************
Note: There may be a chance of multiple DB
instances running on same node. It is mandatory to export the instance name
where we have created a user for GoldenGate.
GGSCI (rac4.sukku.com)
2> dblogin userid ggt, password ggt
ERROR: Unable to connect to
database using user ggt. Please check privileges.
ORA-12162: TNS:net service name
is incorrectly specified.
[oracle@rac4 gg-D_H]$. oraenv
ORACLE_SID = [oracle] ?
ggtestdb2
The Oracle base for
ORACLE_HOME=/oraeng/app/oracle/product/11.2.0 is /oraeng/app/oracle/product
GGSCI (rac4.sukku.com)
1> dblogin userid ggt, password ggt
Successfully logged into
database.
##############################################################
Note: ggsci command won’t accept ‘;’
at the end of the commands.
GGSCI (rac3.sukku.com)
3> info all; ----- ; not allowed for ggsci commands.
ERROR: Invalid command.
##############################################################
STEP
6:- To check what are the
resources running on goldengate.
**************************** SOURCE
***********************
GGSCI (rac3.sukku.com)
4> info all
Program Status
Group Lag at Chkpt Time Since Chkpt --- by default Manager process will be in
stopped status.
MANAGER STOPPED
Note: Edit the parameter file.
User authentication is not mandatory in the
parameter file, but it is recommended to authenticate.
GGSCI (rac3.sukku.com)
5> edit params mgr
---------- vi editor----
port 7809
userid ggs, password ggs
--- (NOT Mandatory)
------------------------
Note: To start the manager process.
GGSCI (rac3.sukku.com)
6> start manager
Manager started.
GGSCI (rac3.sukku.com)
7> info all
Program Status
Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
***************************** TARGET
***********************
[oracle@rac4
dirprm-D_H]$pwd
/home/oracle/gg/dirprm
[oracle@rac4 dirprm-D_H]$vi mgr.prm
port 7809
GGSCI (rac4.sukku.com)
4> info all
Program Status
Group Lag at Chkpt Time Since Chkpt
MANAGER STOPPED
Note: To start the manager process.
GGSCI (rac4.sukku.com)
5> start manager
Manager started.
Note: To stop the manager process.
GGSCI (rac4.sukku.com)
6> stop manager
Manager process is
required by other GGS processes.
Are you sure you want to
stop it (y/n)? yes
Sending STOP request to
MANAGER ...
Request processed.
Manager stopped.
GGSCI (rac4.sukku.com)
7> start manager
Manager started.
Note: To check the status of manager
process.
GGSCI (rac4.sukku.com)
8> status manager
Manager is running (IP
port rac4.sukku.com.7809).
GGSCI (rac4.sukku.com)
9>
**************************** SOURCE
***********************
---
Initial Load through extract process -------
Note: before starting the initial load
replication, make sure that the structure of table exists at Target side.
“SOURCEISTABLE” is the keyword/parameter to run the
Initial load through GoldenGate.
GGSCI (rac3.sukku.com)
9> ADD EXTRACT initload, SOURCEISTABLE
EXTRACT added.
GGSCI (rac3.sukku.com)
10> info all
Program Status
Group Lag at Chkpt Time Since Chkpt ---
Initial load extract process will not show in info all
MANAGER RUNNING
GGSCI (rac3.sukku.com)
11> edit params initload
EXTRACT initload
USERID ggs, PASSWORD ggs
RMTHOST rac4, MGRPORT 7809
RMTTASK replicat, GROUP repload
TABLE ggs.t;
***************************** TARGET
***********************
“SPECIALRUN” is the keyword to receive the initial load at
replicat side.
Extract/Replicat
group name must be less than 8 characters.
GGSCI (rac4.sukku.com)
8> ADD REPLICAT initload2, SPECIALRUN
ERROR: Invalid group name (must
be at most 8 characters).
GGSCI (rac4.sukku.com)
9> ADD REPLICAT repload, SPECIALRUN
REPLICAT added.
GGSCI (rac4.sukku.com)
10> EDIT PARAMS repload
REPLICAT repload
USERID ggt, PASSWORD ggt
ASSUMETARGETDEFS
MAP ggs.t, TARGET ggt.t;
**************************** SOURCE
***********************
Example: Create a table and insert few records..
SQL> select count(*) from t;
COUNT(*)
----------
160
GGSCI (rac3.sukku.com)
23> start extract initload
Sending START request to MANAGER...
EXTRACT INITLOAD starting
GGSCI (rac3.sukku.com)
24> info extract initload
EXTRACT INITLOAD Last Started 2012-09-20 15:17 Status RUNNING
Checkpoint Lag Not Available
Log Read Checkpoint Table
GGS.T
2012-09-20 15:17:51 Record 1
Task SOURCEISTABLE
GGSCI (rac3.sukku.com)
25> info extract initload
EXTRACT INITLOAD
Last Started 2012-09-20 15:20 Status STOPPED
Checkpoint Lag Not Available
Log Read Checkpoint Table GGS.T
2012-09-20 15:20:22 Record 160
Task SOURCEISTABLE
***************************** TARGET
***********************
create a table as similar to source..
GGSCI (rac4.sukku.com)
17> info replicat repload
REPLICAT REPLOAD
Initialized 2012-09-20
15:02 Status
RUNNING
Checkpoint Lag 00:00:00 (updated 00:18:04 ago)
Log Read Checkpoint Not Available
Task SPECIALRUN
GGSCI (rac4.sukku.com)
18> info replicat repload
REPLICAT REPLOAD
Initialized 2012-09-20
15:02 Status
STOPPED
Checkpoint Lag 00:00:00 (updated 00:18:55 ago)
Log Read Checkpoint Not Available
Task SPECIALRUN
SQL> select count(*) from t;
COUNT(*)
----------
160
Note:
Checkpoint Table is mandatory for normal Extract & Replicat.
GGSCI (rac3.sukku.com)
26> edit params ./GLOBALS
GGSCHEMA ggs
CHECKPOINTTABLE ggs.chkpt
GGSCI (rac3.sukku.com)
27> dblogin userid ggs, password ggs
Successfully logged into
database.
GGSCI (rac3.sukku.com)
28> add checkpointtable ggs.chkpt
Successfully created
checkpoint table ggs.chkpt.
SQL> select * from tab;
TNAME TABTYPE CLUSTERID
------------------------------
------- ----------
CHKPT TABLE
CHKPT_LOX TABLE
T TABLE
SQL> desc chkpt_lox
Name
Null? Type
-----------------------------------------
-------- ----------------------------
GROUP_NAME NOT NULL
VARCHAR2(8)
GROUP_KEY NOT NULL
NUMBER(19)
LOG_CMPLT_CSN NOT NULL
VARCHAR2(129)
LOG_CMPLT_XIDS_SEQ NOT NULL NUMBER(5)
LOG_CMPLT_XIDS NOT NULL
VARCHAR2(2000)
SQL> desc chkpt
Name
Null? Type
-----------------------------------------
-------- ----------------------------
GROUP_NAME NOT NULL VARCHAR2(8)
GROUP_KEY NOT NULL
NUMBER(19)
SEQNO
NUMBER(10)
RBA NOT NULL
NUMBER(19)
AUDIT_TS VARCHAR2(29)
CREATE_TS NOT NULL DATE
LAST_UPDATE_TS NOT NULL DATE
CURRENT_DIR NOT NULL
VARCHAR2(255)
LOG_CSN VARCHAR2(129)
LOG_XID
VARCHAR2(129)
LOG_CMPLT_CSN
VARCHAR2(129)
LOG_CMPLT_XIDS
VARCHAR2(2000)
VERSION NUMBER(3)
***************************** SOURCE
***********************
“threads 2” is for RAC databases, so that Goldengate will
be aware of that it should get the data from 2 sources (Node1 & Node2
online redologs).
GGSCI (rac3.sukku.com)
30> add extract ext,tranlog, threads 2, begin now
EXTRACT added.
GGSCI (rac3.sukku.com)
31> add rmttrail /home/oracle/gg/dirdat/rt, extract ext
RMTTRAIL added.
GGSCI (rac3.sukku.com)
32> edit params ext
EXTRAXT ext
USERID ggs, PASSWORD ggs
RMTHOST rac4, MGRPORT 7809
RMTTRAIL /home/oracle/gg/dirdat/rt
TABLE ggs.t;
***************************** TARGET
***********************
GGSCI (rac4.sukku.com)
19> add replicat rep, exttrail /home/oracle/gg/dirdat/rt
ERROR: No checkpoint table
specified for ADD REPLICAT.
GGSCI (rac4.sukku.com)
20> edit params ./GLOBALS
GGSCHEMA ggt
CHECKPOINTTABLE ggt.chkpt
GGSCI (rac4.sukku.com)
21> dblogin userid ggt, password ggt
Successfully logged into
database.
GGSCI (rac4.sukku.com)
22> add checkpointtable ggt.chkpt
Successfully created
checkpoint table ggt.chkpt.
GGSCI (rac4.sukku.com)
23> add replicat rep, exttrail /home/oracle/gg/dirdat/rt
REPLICAT added.
GGSCI (rac4.sukku.com)
24> edit params rep
REPLICAT rep
ASSUMETARGETDEFS
USERID ggt, PASSWORD ggt
MAP ggs.t, TARGET ggt.t;
##############################################################
***************************** SOURCE
***********************
GGSCI (rac3.sukku.com)
36> start extract ext
Sending START request to
MANAGER ...
EXTRACT EXT starting
GGSCI (rac3.sukku.com)
51> info extract ext
EXTRACT EXT
Initialized 2012-09-20
16:10 Status RUNNING
Checkpoint Lag 00:00:00 (updated 00:00:49 ago)
Log Read Checkpoint Oracle Redo Logs
2012-09-20 16:10:08 Thread 1, Seqno 0, RBA 0
SCN 0.0 (0)
Log Read Checkpoint Oracle Redo Logs
2012-09-20 16:10:08 Thread 2, Seqno 0, RBA 0
SCN 0.0 (0)
GGSCI (rac3.sukku.com)
52> info all
Program Status
Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
EXTRACT RUNNING
EXT 00:00:00 00:00:58
--------------------------------------------- without
threads 2 option in RAC DB
------------------------------------------
2012-09-20 15:57:18
ERROR OGG-00446 Oracle GoldenGate Capture for Oracle,
ext.prm: The number of Oracle redo
threads (2) is not the same as the number of checkpoint threads (1). EXTRACT
groups on RAC systems should be created with the THREADS parameter (e.g., ADD
EXT <group name>, TRANLOG, THREADS 2, BEGIN...).
----------------------------------------------------------------------------------------------------------------------------------
***************************** TARGET
***********************
GGSCI (rac4.sukku.com)
29> start replicat rep
Sending START request
REPLICAT REP starting
GGSCI (rac4.sukku.com)
30> info all
Program Status
Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
REPLICAT RUNNING
REP 00:00:00 00:00:00
GGSCI (rac4.sukku.com)
31> info replicat rep
REPLICAT REP
Last Started 2012-09-20 16:13
Status RUNNING
Checkpoint Lag 00:00:00 (updated 00:00:06 ago)
Log Read Checkpoint File /home/oracle/gg/dirdat/rt000000
First Record RBA 0
########################################################################################
Note: Unable to
replicate.. because redo's are in ASM..
2012-09-20 16:57:33
ERROR OGG-00446 Oracle GoldenGate Capture for Oracle,
ext.prm: No valid log files for current
redo sequence 256, thread 1, error retrieving redo file name for sequence 256,
archived = 0, use_alternate = 0Not able to establish initial position for begin
time 2012-09-20 16:56:52.
2012-09-20 16:57:33
ERROR OGG-01668 Oracle GoldenGate Capture for Oracle,
ext.prm: PROCESS ABENDING.
Note: create password
file and grant sysasm privilege and added this parameter to read ASM logfiles
to fix the above error.
EXTRACT ext
USERID ggs, PASSWORD ggs
RMTHOST rac4, MGRPORT 7809
RMTTRAIL /home/oracle/gg/dirdat/rt
tranlogoptions asmuser sys@asm1, asmpassword sys
TABLE ggs.t;
GGSCI (rac3.sukku.com) 63> stats extract ext
Sending STATS request to
EXTRACT EXT ...
Start of Statistics at
2012-09-20 21:21:27.
DDL replication statistics
(for all trails):
*** Total statistics since
extract started ***
Operations 3.00
Mapped operations 3.00
Unmapped operations 0.00
Other operations 0.00
Excluded operations 0.00
Output to
/home/oracle/gg/dirdat/rt:
Extracting from GGS.A to
GGS.A:
*** Total statistics since
2012-09-20 18:45:06 ***
Total inserts 1.00
Total updates 0.00
Total deletes 0.00
Total discards 0.00
Total operations 1.00
*** Daily statistics since
2012-09-20 18:45:06 ***
Total inserts 1.00
Total updates 0.00
Total deletes 0.00
Total discards 0.00
Total operations 1.00
*** Hourly statistics since
2012-09-20 21:00:00 ***
No database operations have been
performed.
*** Latest statistics since
2012-09-20 18:45:06 ***
Total inserts 1.00
Total updates 0.00
Total deletes 0.00
Total discards 0.00
Total operations 1.00
Extracting from
GGS.GGS_MARKER to GGS.GGS_MARKER:
*** Total statistics since
2012-09-20 18:45:06 ***
No database operations have been
performed.
*** Daily statistics since
2012-09-20 18:45:06 ***
No database operations have been
performed.
*** Hourly statistics since
2012-09-20 21:00:00 ***
No database operations have been
performed.
*** Latest statistics since
2012-09-20 18:45:06 ***
No database operations have been
performed.
Extracting from
GGS.GGS_MARKER to GGS.GGS_MARKER:
*** Total statistics since
2012-09-20 18:45:06 ***
No database operations have been
performed.
*** Daily statistics since
2012-09-20 18:45:06 ***
No database operations have been
performed.
*** Hourly statistics since
2012-09-20 21:00:00 ***
No database operations have been
performed.
*** Latest statistics since
2012-09-20 18:45:06 ***
No database operations have been performed.
Extracting from GGS.H to
GGS.H:
*** Total statistics since
2012-09-20 18:45:06 ***
Total inserts 2.00
Total updates 0.00
Total deletes 0.00
Total discards 0.00
Total operations 2.00
*** Daily statistics since
2012-09-20 18:45:06 ***
Total inserts 2.00
Total updates 0.00
Total deletes 0.00
Total discards 0.00
Total operations 2.00
*** Hourly statistics since
2012-09-20 21:00:00 ***
No database operations have been
performed.
*** Latest statistics since
2012-09-20 18:45:06 ***
Total inserts 2.00
Total updates 0.00
Total deletes 0.00
Total discards 0.00
Total operations 2.00
End of Statistics.