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 database add 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.