Oracle GoldenGate – Setup & Near Zero-Downtime Migration

 

Oracle GoldenGate – Setup & Near Zero-Downtime Migration

 

 Extract Process:

·       Data capture engine

·       Runs on the source system

·       Reads committed changes directly from the Oracle redo logs and archive logs

·       Write logs on local trail files

 

Replicat Process:

·       Runs on the target system

·       Reads the trail files

·       Applies the changes in target DB

 

GoldenGate Installation and Basic Setup

 

# Unzip GoldenGate software

unzip V982985-01.zip -d /u01/goldengate

 

# Run the installer (silent mode)

cd /u01/goldengate

./runInstaller -silent -responseFile /tmp/gg_install.rsp

 

# Create the GoldenGate deployment directory

mkdir -p /u01/gg/deployment

mkdir -p /u01/gg/trails

 

# Create the GGS manager port configuration

GGSCI> CREATE SUBDIRS

GGSCI> EDIT PARAMS MGR

PORT 7809

DYNAMICPORTLIST 7810-7820

PURGEOLDEXTRACTS /u01/gg/trails/*, USECHECKPOINTS, MINKEEPDAYS 3

AUTORESTART EXTRACT *, RETRIES 3, WAITSECONDS 30

 

GGSCI> START MGR

 

 

# Enabling Supplemental Logging on Source

 

·       Enable minimal supplemental logging at database level

Sql> ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;

·       Enable supplemental logging for all columns (for tables without PK)

Sql> ALTER DATABASE ADD SUPPLEMENTAL LOG DATA (ALL) COLUMNS;

·       Or enable it per schema (more targeted)

Sql> ALTER TABLE app_owner.orders ADD SUPPLEMENTAL LOG DATA (ALL) COLUMNS;

·       Verify supplemental logging status

Sql> SELECT supplemental_log_data_min, supplemental_log_data_pk, supplemental_log_data_all FROM   v$database;

·       Grant GoldenGate permissions to capture user

Sql> CREATE USER ggadmin IDENTIFIED BY gg_secure_pass;

Sql> GRANT CREATE SESSION TO ggadmin;

Sql> EXEC DBMS_GOLDENGATE_AUTH.GRANT_ADMIN_PRIVILEGE('GGADMIN');

 

# Configuring Extract on Source

 

·       In GGSCI on SOURCE server

GGSCI> DBLOGIN USERID ggadmin, PASSWORD gg_secure_pass

GGSCI> ADD EXTRACT EXT_SRC, INTEGRATED TRANLOG, BEGIN NOW

GGSCI> ADD EXTTRAIL /u01/gg/trails/lt, EXTRACT EXT_SRC, MEGABYTES 500

GGSCI> EDIT PARAMS EXT_SRC

EXTRACT EXT_SRC

USERID ggadmin, PASSWORD gg_secure_pass

EXTTRAIL /u01/gg/trails/lt

LOGALLSUPCOLS

UPDATERECORDFORMAT COMPACT

TABLE APP_OWNER.*;

TABLE APP_CONFIG.*;

 

GGSCI> REGISTER EXTRACT EXT_SRC DATABASE

GGSCI> START EXTRACT EXT_SRC

GGSCI> INFO EXTRACT EXT_SRC

 

·       Configuring Data Pump Extract

GGSCI> ADD EXTRACT PMP_SRC, EXTTRAILSOURCE /u01/gg/trails/lt

GGSCI> ADD RMTTRAIL /u01/gg/trails/rt, EXTRACT PMP_SRC, MEGABYTES 500

GGSCI> EDIT PARAMS PMP_SRC

EXTRACT PMP_SRC

USERID ggadmin, PASSWORD gg_secure_pass

RMTHOST target-server, MGRPORT 7809

RMTTRAIL /u01/gg/trails/rt

TABLE APP_OWNER.*;

TABLE APP_CONFIG.*;

GGSCI> START EXTRACT PMP_SRC

 

# Initial Load Strategy

·       Data Pump export-import

·       GoldenGate's Initial-load Extract

·       RMAN restore

 

## INITIAL LOAD USING DATA PUMP

·       Start GoldenGate Extract (must be running FIRST)

GGSCI> START EXTRACT EXT_SRC

·       Get the current SCN

SQL> SELECT CURRENT_SCN FROM V$DATABASE;

·       Export with flashback_scn to get a consistent snapshot

expdp userid='/ as sysdba' schemas=APP_OWNER,APP_CONFIG dumpfile=gg_initial_%U.dmp flashback_scn=<scn_no> parallel=8 directory=MIGRATION_DIR compression=all exclude=statistics

·       Import on TARGET

impdp userid='/ as sysdba' schemas=APP_OWNER,APP_CONFIG dumpfile=gg_initial_%U.dmp parallel=8 directory=MIGRATION_DIR transform=disable_archive_logging:y

·       Start Replicat from the SCN used in the export

GGSCI> ADD REPLICAT REP_TGT, INTEGRATED, EXTTRAIL /u01/gg/trails/rt, BEGIN SCN <scn_no>

       GGSCI> EDIT PARAMS REP_TGT

REPLICAT REP_TGT

USERID ggadmin, PASSWORD gg_secure_pass

MAP APP_OWNER.*, TARGET APP_OWNER.*;

MAP APP_CONFIG.*, TARGET APP_CONFIG.*;

 

GGSCI> START REPLICAT REP_TGT

 

# Monitoring and Lag Management

       INFO ALL

       LAG EXTRACT EXT_SRC

       VIEW REPORT REP_TGT

       STATS REPLICAT REP_TGT, TABLE APP_OWNER.ORDERS, LATEST

 

# Cutover Planning

·       Stop source applications

·       Verify Extract is not receiving new changes

GGSCI> INFO EXTRACT EXT_SRC

·       Wait for Replicat lag to reach zero -- Lag at Last Checkpoint: 00:00:00

GGSCI> LAG REPLICAT REP_TGT

·       Stop all GoldenGate processes gracefully

GGSCI> STOP REPLICAT REP_TGT

GGSCI> STOP EXTRACT PMP_SRC

GGSCI> STOP EXTRACT EXT_SRC

·       Verify row counts on source with target

SQL> SELECT 'APP_OWNER.ORDERS', COUNT(*) FROM APP_OWNER.ORDERS UNION ALL SELECT 'APP_OWNER.CUSTOMERS', COUNT(*) FROM APP_OWNER.CUSTOMERS;

·       Migrate application connection to target

 

# GoldenGate Issues 

·       Issue: Extract abending with ORA-01291 (missing logfile) or ORA-01555 (snapshot too old)

Check what SCN Extract needs

GGSCI> INFO EXTRACT EXT_SRC, DETAIL

Check if required archive logs exist

SELECT sequence#, name, first_change#, next_change# FROM v$archived_log WHERE next_change# >= &required_scn ORDER BY sequence#;

Retain archives longer (change RMAN retention policy)

RMAN> CONFIGURE ARCHIVELOG DELETION POLICY TO APPLIED ON STANDBY;

Or register GoldenGate with Oracle to prevent archive deletion

GGSCI> DBLOGIN USERID ggadmin, PASSWORD gg_pass

GGSCI> REGISTER EXTRACT EXT_SRC DATABASE

 

·       Issue: Replicat abending with ORA-00001 (Unique Constraint Violated)

Fix: delete and re-add Replicat with correct starting SCN

GGSCI> STOP REPLICAT REP_TGT

GGSCI> DELETE REPLICAT REP_TGT

GGSCI> ADD REPLICAT REP_TGT, INTEGRATED, EXTTRAIL /u01/gg/trails/rt, BEGIN SCN &correct_export_scn , or

Configure Replicat to handle duplicates gracefully

In Replicat parameter file, add:

HANDLECOLLISIONS

 

·       Issue: GoldenGate Lag increasing over time

Check Replicat statistics

GGSCI> STATS REPLICAT REP_TGT -- Switch to Parallel Replicat

GGSCI> DELETE REPLICAT REP_TGT

GGSCI> ADD REPLICAT REP_TGT,

PARALLEL,

EXTTRAIL /u01/gg/trails/rt -- In Replicat parameter file:

REPLICAT REP_TGT

USERID ggadmin, PASSWORD gg_pass

MAP_PARALLELISM 4

APPLY_PARALLELISM 8

MAP APP_OWNER.*, TARGET APP_OWNER.*;

Comments

Post a Comment

Popular posts from this blog

 Exadata  Oracle Exadata Smart Flash Cache * Exadata cell -> Flash LUN -> Cell Disk -> Flash Cache/Flash Log  * CELL_FLASH_CACHE - – DEFAULT (LRU), KEEP, NONE * ALTER TABLE <TABLE_NAME> STORAGE(FLASH_CACHE [NONE|DEFAULT|KEEP]) * ALTER INDEX <INDEX NAME> STORAGE(FLASH_CACHE [NONE|DEFAULT|KEEP]) Smart Flash Cache Mode: 1. Write Through Flash Cache Mode (Default):  Write on Disk -> Copy to Flash Cache  * excellent for absorbing repeated random reads  * commonly found in OLTP applications. * dcli -g cell_servers -l celladmin cellcli -e "list cell attributes flashcachemode" * Steps to enable Write-Through Mode CellCLI> ALTER FLASHCACHE ALL FLUSH CellCLI> DROP FLASHCACHE CellCLI> ALTER CELL SHUTDOWN SERVICES CELLSRV CellCLI> ALTER CELL flashCacheMode=WriteThrough CellCLI> ALTER CELL STARTUP SERVICES CELLSRV CellCLI> CREATE FLASHCACHE ALL 2. Write Back Flash Cache Mode:  Write on Flash -> Copy to Disk  * best ...
Read more

Database Network : Configuration Parameters

LSNRCTL> command [listener_name] lsnrctl @file_name  #to execute in batch mode. REM or # to identify comments in the batch script Listener.ora file parameters: ·          PASSWORDS_listener_name  #an unencrypted password ·          LSNRCTL> change_password #to establish an encrypted password ·          LSNRCTL> SAVE_CONFIG  #Any changes are stored in listener.ora ·          LSNRCTL> SET PASSWORD  #If a password is set, then issue SET PASSWORD command before changing the password. Use the command SET PASSWORD prior to privileged Listener Control utility commands ·          Reload : the database services, instances, service handlers, and listening endpoints that were dynamically registered with the listener will be unregistered and subseque...
Read more

Undo Segments : Drop Problematic Undo Segments

ERROR about to recover undo segment 98 status:6 inst:0 mark undo segment 98 as available status:6 ret:1  about to recover undo segment 46 status:6 inst:0 mark undo segment 46 as available status:6 ret:1   SQL> select * from V$FAST_START_TRANSACTIONS; USN SLT SEQ STATE UNDOBLOCKSDONE UNDOBLOCKSTOTAL PID CPUTIME PARENTUSN PARENTSLT PARENTSEQ XID PXID RCVSERVERS CON_ID ---------- ---------- ---------- ---------------- -------------- --------------- ---------- ---------- ---------- ---------- 46 46 2313064 RECOVERING 505 24992423 77 5586 0 0 0 10001000684B2300 0000000000000000 1 0 98 25 1352150 RECOVERING 0 226231 78 5586 0 0 0 30001900D6A11400 0000000000000000 1 0   SQL> SELECT segment_name, tablespace_name  FROM dba_rollback_segs  WHERE segment_id IN (98, 46); SEGMENT_NAME               TABLESPACE_NAME ------------------------------ ----------------------------...
Read more