Infrastructure at your Service

Lazhar Felahi

Migrate Oracle Database 9.2.0.6 to Oracle 19c using GoldenGate

When a customer wanted to take the challenge to migrate an oracle database 9.2.0.6 (the prehistory in the Oracle world) to Oracle 19c using Oracle GodenGate, I saw more problems than add value for different reasons:

  •  Oracle 9.2.0.6 database is out of support (final 9.2 patch was Oracle 9.2.0.8).
  • The customer Operating Systems was AIX 7.4 and only Oracle GoldenGate 11.1.1.1.2 for Oracle 9.2 for AIX 5.3 is available on https://edelivery.oracle.com.
  • The Patch 13606038: ORACLE GOLDENGATE V11.1.1.0.31 FOR ORACLE 9I is not available for download since we need special support to got it.

Oracle GoldenGate database Schema Profile check script

The first step is to download from Oracle Support, the Oracle GoldenGate database Schema Profile check script to query the database by schema to identify current configuration and any unsupported data types or types that may need special considerations for Oracle GoldenGate in an oracle environment:

  • Oracle GoldenGate database Schema Profile check script for Oracle DB (Doc ID 1296168.1) : full-schemaCheckOracle_07072020.sql

Even Oracle Support mentions that this script is written for Oracle database version 9i thru 11g, some adaptation must be done for an Oracle 9.2.0.6 database:

First of all, add a parameter to specify schema name as entry :

[email protected]: /opt/oracle/software/goldengate> ls -ltr

vi full-schemaCheckOracle_07072020.sql

--Lazhar Felahi – 10.12.2020 - comment this line
--spool schemaCheckOracle.&&schema_name.out
--Lazhar Felahi – 10.12.2020 - comment this line
--ACCEPT schema_name char prompt 'Enter the Schema Name > '
variable b0 varchar2(50)
--Lazhar Felahi – 10.12.2020 - comment this line
--exec :b0 := upper('&schema_name');
--Lazhar Felahi – 10.12.2020 - add this line
exec :b0 := '&1';
--Lazhar Felahi – 10.12.2020 - comment this line
--spool schemaCheckOracle.&&schema_name..out
--Lazhar Felahi – 10.12.2020 - add this line
spool schemaCheckOracle.&&1..out

Execute the script for schemas needed:

[email protected]: /opt/oracle/software/goldengate>
sqlplus /nolog
SQL*Plus: Release 9.2.0.6.0 - Production on Thu Dec 10 21:19:37 2020
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
SQL> start full-schemaCheckOracle_07072020.sql HR
error :
ERROR at line 4:
ORA-00904: "SUPPLEMENTAL_LOG_DATA_ALL": invalid identifier
platform_name
*
ERROR at line 2:
ORA-00904: "PLATFORM_NAME": invalid identifier
------ Integrated Extract unsupported objects in HR

select object_name, support_mode from DBA_GOLDENGATE_SUPPORT_MODE WHERE OWNER = :b0 and support_mode = 'NONE'
ERROR at line 1:
ORA-00942: table or view does not exist

 

The above errors can be ignored :

  • The errors ORA-00904: “SUPPLEMENTAL_LOG_DATA_ALL”: invalid identifier and ORA-00904: “PLATFORM_NAME”: invalid identifier can be ignored since this column does not exist into the data dictionary view v$database for the version Oracle 9.2.0.6 database.
  • The error ORA-00942: table or view does not exist can be ignored since the view DBA_GOLDENGATE_SUPPORT_MODE  is available starting with Oracle Database 11g Release 2 (11.2.0.4).

Adapt the script and re-execute it, an output file is generated listing different checks and any types unsupported.

For instance, the script lists all tables with no primary key or Unique Index or Tables with NOLOGGING setting.

GoldenGate needs tables with primary key. If no PK exist for one table, GG will take all column to define the unicity.

GOLDENGATE INSTALLATION – ON SOURCE SERVER

Download the zip file corresponding to Oracle GoldenGate 11.1.1.1.2 software from https://edelivery.oracle.com :

  • V28955-01.zip Oracle GoldenGate 11.1.1.1.2 for Oracle 9.2 for AIX 5.3 on IBM AIX on POWER Systems (64-bit), 45.5 MB

Unzip and untar the file:

[email protected]: /opt/oracle/software/goldengate> ls -ltr
total 365456
-rw-rw-r--    1 oracle   dba       139079680 Oct  5 2011  ggs_AIX_ppc_ora9.2_64bit.tar
-rw-r--r--    1 oracle   dba          245329 Oct 28 2011  OGG_WinUnix_Rel_Notes_11.1.1.1.2.pdf
-rw-r--r--    1 oracle   dba           25065 Oct 28 2011  Oracle GoldenGate 11.1.1.1 README.txt
-rwx------    1 oracle   dba        47749729 Dec 10 13:55 V28955-01.zip
drwxr-xr-x    2 oracle   dba            4096 Dec 14 09:35 check_script
[email protected]:/opt/oracle/software/goldengate>

[email protected]:/opt/oracle/product/gg_11.1.1.1.2> tar -xvf /opt/oracle/software/goldengate/ggs_AIX_ppc_ora9.2_64bit.tar
…
x marker_setup.sql, 3702 bytes, 8 tape blocks
x marker_status.sql, 1715 bytes, 4 tape blocks
[email protected]: /opt/oracle/product/gg_11.1.1.1.2> ls -ltr
total 235344
-r--r--r-- 1 oracle dba 1476 Oct 15 2010 zlib.txt
. . .
-rwxr-xr-x 1 oracle dba 13911955 Oct 5 2011 replicat

Let’s set the LIBPATH environment variable and call “ggsci” utility:

[email protected]: /opt/oracle/product/gg_11.1.1.1.2> export LIBPATH=/opt/oracle/product/gg_11.1.1.1.2/:$ORACLE_HOME/lib:/opt/oracle/product/9.2.0.6/lib32/:/opt/oracle/product/9.2.0.6/lib/:$LIBPATH
[email protected]: /opt/oracle/product/gg_11.1.1.1.2> ./ggsci

Oracle GoldenGate Command Interpreter for Oracle
Version 11.1.1.1.2 OGGCORE_11.1.1.1.2_PLATFORMS_111004.2100
AIX 5L, ppc, 64bit (optimized), Oracle 9.2 on Oct 5 2011 00:37:06

Copyright (C) 1995, 2011, Oracle and/or its affiliates. All rights reserved.

GGSCI (aixSourceServer-Ora9i) 1> info all

Program Status Group Lag Time Since Chkpt

MANAGER STOPPED

GOLDENGATE SETUP – ON SOURCE SERVER

Create the goldengate admin user on source and target database:

[email protected]:/home/oracle/ [DB2] sqlplus / as sysdba

SQL> create tablespace GOLDENGATE datafile '/u02/oradata/DB2/goldengate.dbf' size 2G ;

SQL> create profile GGADMIN limit password_life_time unlimited ;

SQL> create user GGADMIN identified by "******" default tablespace
     goldengate temporary tablespace temp profile GGADMIN ;

SQL> grant create session, dba to GGADMIN ;

SQL> exec dbms_goldengate_auth.grant_admin_privilege ('GGADMIN') ;

SQL> grant flashback any table to GGADMIN ;
--create subdirs
GGSCI (aixSourceServer-Ora9i) 1> create subdirs

Creating subdirectories under current directory /opt/oracle/product/gg_11.1.1.1.2

Parameter files                /opt/oracle/product/gg_11.1.1.1.2/dirprm: created
Report files                   /opt/oracle/product/gg_11.1.1.1.2/dirrpt: created
Checkpoint files               /opt/oracle/product/gg_11.1.1.1.2/dirchk: created
Process status files           /opt/oracle/product/gg_11.1.1.1.2/dirpcs: created
SQL script files               /opt/oracle/product/gg_11.1.1.1.2/dirsql: created
Database definitions files     /opt/oracle/product/gg_11.1.1.1.2/dirdef: created
Extract data files             /opt/oracle/product/gg_11.1.1.1.2/dirdat: created
Temporary files                /opt/oracle/product/gg_11.1.1.1.2/dirtmp: created
Veridata files                 /opt/oracle/product/gg_11.1.1.1.2/dirver: created
Veridata Lock files            /opt/oracle/product/gg_11.1.1.1.2/dirver/lock: created
Veridata Out-Of-Sync files     /opt/oracle/product/gg_11.1.1.1.2/dirver/oos: created
Veridata Out-Of-Sync XML files /opt/oracle/product/gg_11.1.1.1.2/dirver/oosxml: created
Veridata Parameter files       /opt/oracle/product/gg_11.1.1.1.2/dirver/params: created
Veridata Report files          /opt/oracle/product/gg_11.1.1.1.2/dirver/report: created
Veridata Status files          /opt/oracle/product/gg_11.1.1.1.2/dirver/status: created
Veridata Trace files           /opt/oracle/product/gg_11.1.1.1.2/dirver/trace: created
Stdout files                   /opt/oracle/product/gg_11.1.1.1.2/dirout: created

--add GGSCHEMA into ./GLOBALS file in source and target
[email protected] aixSourceServer-Ora9i: /opt/oracle/product/gg_11.1.1.1.2> ./ggsci
GGSCI (aixSourceServer-Ora9i) 3> view param ./GLOBALS
GGSCHEMA goldengate

--add PORT into mgr parameter file and start the manager
GGSCI (aixSourceServer-Ora9i) 1> edit params mgr

PORT 7809
GGSCI (aixSourceServer-Ora9i) 6> start mgr
Manager started.
GGSCI (aixSourceServer-Ora9i) 7> info all
Program     Status      Group       Lag           Time Since Chkpt
MANAGER     RUNNING

--Installing the DDL support on the source database : You will be prompted for the name of a schema for the GoldenGate database objects.
SQL> @marker_setup.sql
. . .
Script complete
SQL> @ddl_setup.sql
. . .
SUCCESSFUL installation of DDL Replication software components
SQL> @role_setup.sql
Role setup script complete
SQL> grant ggs_ggsuser_role to goldengate;
SQL> @ddl_enable.sql
Trigger altered
--On both database (source and target), Installing Support for Sequences
SQL> @sequence.
. . .
SUCCESSFUL installation of Oracle Sequence Replication support

Add the trandata on schemas concerned by the GoldenGate replication:

[email protected]: /opt/oracle/product/gg_11.1.1.1.2> ./ggsci

Oracle GoldenGate Command Interpreter for Oracle
Version 11.1.1.1.2 OGGCORE_11.1.1.1.2_PLATFORMS_111004.2100
AIX 5L, ppc, 64bit (optimized), Oracle 9.2 on Oct  5 2011 00:37:06

Copyright (C) 1995, 2011, Oracle and/or its affiliates. All rights reserved.
	


GGSCI (aixSourceServer-Ora9i) 1> dblogin userid goldengate
Password:
Successfully logged into database.


GGSCI (aixSourceServer-Ora9i) 2> add trandata bgh.*
GGSCI (aixSourceServer-Ora9i) 2> add trandata all_opi.*

2020-12-18 10:46:45  WARNING OGG-00706  Failed to add supplemental log group on table bgh.KLI_J_TEST_HIST due to ORA-02257: maximum number of columns exceeded, SQL ALTER TABLE "bgh"."KLI_J_TEST_HIST" ADD SUPPLEMENTAL LOG GROUP "GGS_KLI_J_TEST_HIST_901157" ("ENH_N_ID","ENH_N_NOINSCRIPTION","ENH_N_NOCOURS","ENH_C_P1NOTE","ENH_C_P2NOTE","ENH_C_P3NOTE","ENH_C_.


2020-12-18 10:46:52  WARNING OGG-00706  Failed to add supplemental log group on table bgh.TABLE_ELEVES_SVG due to ORA-02257: maximum number of columns exceeded, SQL ALTER TABLE "bgh"."TABLE_ELEVES" ADD SUPPLEMENTAL LOG GROUP "GGS_TABLE_ELEVES_901320" ("NOINSCRIPTION","NOCOURS","P1NOTE","P2NOTE","P3NOTE","P4NOTE","P5NOTE","P6NOTE","P7NOTE","P8NOTE","P1COMPTE".

2020-12-18 10:46:52  WARNING OGG-00869  No unique key is defined for table TABLENOTE_TMP. All viable columns will be used to represent the key, but may not guarantee uniqueness.  KEYCOLS may be used to define the key.

Logging of supplemental redo data enabled for table all_opi.ZUI_VM_RETUIO_SCOLARITE.
ERROR: OCI Error retrieving bind info for query (status = 100), SQL <SELECT * FROM "all_opi"."EXT_POI_V_RTEWR">.

The warning OGG-00706 and OGG–00869 are solved by adding a primary key to the tables concerned.

The OCI error must be investigated by opening an Oracle  Service Request.

Add the extract, exttrail and start it :

GGSCI (aixSourceServer-Ora9i) 2> add extract EXTRSO, tranlog, begin now
EXTRACT added.

add extract EXTRNA, tranlog, begin now
GGSCI (aixSourceServer-Ora9i) 7> add EXTTRAIL /opt/oracle/goldengate/data/DDIP9/so, EXTRACT EXTRSO
EXTTRAIL added.

add EXTTRAIL /opt/oracle/goldengate/data/DDIP9/na, EXTRACT EXTRNA

edit param EXTRSO
Extract EXTRSO
userid goldengate password ******
Exttrail /opt/oracle/goldengate/data/DDIP9/so
ENCRYPTTRAIL AES192
DDL INCLUDE MAPPED OBJNAME bgh.*
Table bgh.*;

edit param EXTRNA
Extract EXTRNA
userid goldengate password ******
Exttrail /opt/oracle/goldengate/data/DDIP9/na
ENCRYPTTRAIL AES192
DDL INCLUDE MAPPED OBJNAME all_opi.*
Table all_api.*;

start EXTRSO

Sending START request to MANAGER ...
EXTRACT EXTRSO starting

start EXTRNA

GGSCI (aixSourceServer-Ora9i) 11> info all

Program     Status      Group       Lag           Time Since Chkpt

MANAGER     RUNNING
EXTRACT     RUNNING     EXTRHR      00:00:00      00:00:04
EXTRACT     RUNNING     EXTRSO      00:08:18      00:00:00
EXTRACT     RUNNING     PUMPHR      00:00:00      00:00:07

Check if trail files are created:

[email protected]: /opt/oracle/product/gg_11.1.1.1.2> ls -ltr /opt/oracle/goldengate/data/DDIP9/
total 72
-rw-rw-rw-    1 oracle   dba             960 Dec 16 09:34 hr000000
-rw-rw-rw-    1 oracle   dba            1021 Dec 16 10:25 hr000001
-rw-rw-rw-    1 oracle   dba            1021 Dec 16 10:34 hr000002
-rw-rw-rw-    1 oracle   dba            2679 Dec 16 14:26 hr000003
-rw-rw-rw-    1 oracle   dba             960 Dec 16 19:54 so000000
-rw-rw-rw-    1 oracle   dba            1021 Dec 16 19:59 na000000

Add the PUMP:

GGSCI (aixSourceServer-Ora9i) 1> add extract PUMPSO,EXTTRAILSOURCE /opt/oracle/goldengate/data/DDIP9/so
EXTRACT added.

add extract PUMPNA,EXTTRAILSOURCE /opt/oracle/goldengate/data/DDIP9/na

GGSCI (aixSourceServer-Ora9i) 2> add rmttrail /data/oradata/goldengate/data/LGGATE/so, extract PUMPSO
RMTTRAIL added.

add rmttrail /data/oradata/goldengate/data/LGGATE/na, extract PUMPNA

extract PUMPSO
userid goldengate password ******
RMTHOST aixTargetServer-Ora19c, MGRPORT 7810
RMTTRAIL /data/oradata/goldengate/data/LGGATE/so
TABLE bgh.*;

extract PUMPNA
userid goldengate password ******
RMTHOST aixTargetServer-Ora19c, MGRPORT 7810
RMTTRAIL /data/oradata/goldengate/data/LGGATE/na
TABLE all_api.*;


GGSCI (aixSourceServer-Ora9i) 6> start pumpso

Sending START request to MANAGER ...
EXTRACT PUMPSO starting

start pumpna

GGSCI (aixSourceServer-Ora9i) 26> info all

Program     Status      Group       Lag           Time Since Chkpt

MANAGER     RUNNING
EXTRACT     RUNNING     EXTRHR      00:00:00      00:00:07
EXTRACT     RUNNING     EXTRNA      00:00:00      00:00:08
EXTRACT     RUNNING     EXTRSO      00:00:00      00:00:05
EXTRACT     RUNNING     PUMPHR      00:00:00      00:00:03
EXTRACT     RUNNING     PUMPNA      00:00:00      00:03:42
EXTRACT     RUNNING     PUMPSO      00:00:00      00:00:00

 

GOLDENGATE INITIAL LOAD

On the source schemas, got the last active transaction and do the export:

SELECT dbms_flashback.get_system_change_number as current_scn FROM DUAL;
10228186709471 --Backup this SCN, it will be used later to start the goldengate replicat process on the target server
nohup  exp / file=rg081DDIP9.s0.202012172303.dmp log=rg081DDIP9.s0.202012172303.dmp.log tables=bgh.% flashback_scn=10228186709471 &
nohup  exp / file=rg081DDIP9.nbds_adm.202012172303.dmp log=rg081DDIP9.all_opi.202012172303.dmp.log tables=nbds_adm.% flashback_scn=10228186709471 &

Copy the dump file on the target and do the import :

drop user bgh cascade;
create user bgh identified by "******" default tablespace SO temporary tablespace TEMP;
alter user bgh quota unlimited on S0_D;
alter user bgh quota unlimited on S0_I;
alter user bgh quota unlimited on S0_LOB;
nohup imp / file=/data/export/LGGATE/rg081DDIP9.s0.202012172303.dmp log=so.imp171220202303.log buffer=1000000 fromuser=bgh touser=bgh grants=n statistics=none constraints=n ignore=y &

drop user all_opi cascade;
create user all_opi identified by "******" default tablespace NA temporary tablespace TEMP;
alter user all_opi quota unlimited on NBDS_D;
alter user all_opi quota unlimited on NBDS_I;
alter user all_opi quota unlimited on NBDS_LOB;
alter user all_opi quota unlimited on system;
alter user all_opi quota unlimited on na;
nohup imp / file=/data/export/LGGATE/rg081DDIP9.nbds_adm.202012172303.dmp log=na.imp171220202303.log buffer=1000000 fromuser=all_opi touser=all_opi grants=n statistics=none constraints=n ignore=y &

Since the import is done without the constraints, get all primary key from the source database and create it into target.

Disable all triggers on the target:

select 'alter trigger '||owner||'.'||trigger_name||' disable;' from dba_triggers where owner = 'NBDS_ADM';

Check no ref. constraints exist, job_queue_processes parameter equal to 0 and recompile all:

--checK ref constraints
SQL> select * from dba_constraints where owner = 'NBDS_ADM' and constraint_type = 'R';

no rows selected

SQL>
--check job_queue_processes

SQL> sho parameter job

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
job_queue_processes                  integer     384
max_datapump_jobs_per_pdb            string      100
max_datapump_parallel_per_job        string      50
SQL> alter system set job_queue_Processes = 0;

System altered.

SQL>

--recompile all
SQL> start ?/rdbms/admin/utlrp.sql

Session altered.
. . .

GOLDENGATE SETUP – ON TARGET SERVER

Install the last version of GoldenGate software for AIX from : https://www.oracle.com/middleware/technologies/goldengate-downloads.html.

The goldengate installation has nothing special, just read the documentation : https://docs.oracle.com/en/middleware/goldengate/core/19.1/index.html

Do the standard database setup for goldengate written into above documentation.

Under ggsci, create a wallet :

GGSCI (aixTargetServer-Ora19c) 11> create wallet

Created wallet.

Opened wallet.

GGSCI (aixTargetServer-Ora19c) 12> add credentialstore

Credential store created.

GGSCI (aixTargetServer-Ora19c) 13> alter credentialstore add user [email protected] alias goldengate
Password:

Credential store altered.

GGSCI (aixTargetServer-Ora19c) 1> dblogin useridalias goldengate
Successfully logged into database.

GGSCI (aixTargetServer-Ora19c as [email protected]) 2>

Add the replicat:

--add replicat
GGSCI (aixTargetServer-Ora19c) 5> dblogin useridalias goldengate
Successfully logged into database.

GGSCI (aixTargetServer-Ora19c as [email protected]) 8> add replicat REPLSO, exttrail /data/oradata/goldengate/data/LGGATE/so,checkpointtable GOLDENGATE.CHECKPOINT;
REPLICAT added.


GGSCI (aixTargetServer-Ora19c as [email protected]) 9> add replicat REPLNA, exttrail /data/oradata/goldengate/data/LGGATE/na,checkpointtable GOLDENGATE.CHECKPOINT;
REPLICAT added.


GGSCI (aixTargetServer-Ora19c as [email protected]) 10> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING
REPLICAT    RUNNING     REPLHR      00:00:00      00:00:09
REPLICAT    STOPPED     REPLNA      00:00:00      00:00:02
REPLICAT    STOPPED     REPLSO      00:00:00      00:00:17


GGSCI (aixTargetServer-Ora19c as [email protected]) 11>

--configure replicat
Replicat REPLSO
--DBOPTIONS INTEGRATEDPARAMS (parallelism 6)
SOURCECHARSET PASSTHRU
DISCARDFILE /opt/oracle/product/gg_191004/dirrpt/REPLSO_discard.txt, append, megabytes 10
USERIDALIAS goldengate
ASSUMETARGETDEFS
MAP bhg.*,TARGET bgh.*;


Replicat REPLNA
--DBOPTIONS INTEGRATEDPARAMS (parallelism 6)
SOURCECHARSET PASSTHRU
DISCARDFILE /opt/oracle/product/gg_191004/dirrpt/REPLNA_discard.txt, append, megabytes 10
USERIDALIAS goldengate
ASSUMETARGETDEFS
MAP all_opi.*,TARGET all_opi.*;

--Start replicat REPLNA
GGSCI (aixTargetServer-Ora19c as [email protected]) 18> start replicat REPLNA, atcsn 10228186709471

Sending START request to MANAGER ...
REPLICAT REPLNA starting

--Start replicat REPLS0
GGSCI (aixTargetServer-Ora19c as [email protected]) 18> start replicat REPLSO, atcsn 10228186709471

Sending START request to MANAGER ...
REPLICAT REPLSO starting

GGSCI (aixTargetServer-Ora19c as [email protected]) 19> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING
REPLICAT    RUNNING     REPLHR      00:00:00      00:00:02
REPLICAT    RUNNING     REPLNA      00:00:00      00:00:01
REPLICAT    RUNNING     REPLSO      00:00:00      00:21:10

Wait unti the lag decrease…

GOLDENGATE TEST SYNCHRONIZATION

Add some activity DML + DDL on the source database and check the synchronization with goldengate “stats” commands on both servers:

GGSCI (aixSourceServer-Ora9i) 5> stats extract EXTRNA, totalsonly *.*

Sending STATS request to EXTRACT EXTRNA ...

Start of Statistics at 2020-12-18 16:35:00.

DDL replication statistics (for all trails):

*** Total statistics since extract started     ***
        Operations                                   0.00
        Mapped operations                            0.00
        Unmapped operations                          0.00
        Other operations                             0.00
        Excluded operations                          0.00

Output to /opt/oracle/goldengate/data/DDIP9/na:

Cumulative totals for specified table(s):

*** Total statistics since 2020-12-18 10:42:15 ***
        Total inserts                                8.00
        Total updates                                1.00
        Total deletes                               25.00
        Total discards                               0.00
        Total operations                            34.00

*** Daily statistics since 2020-12-18 10:42:15 ***
        Total inserts                                8.00
        Total updates                                1.00
        Total deletes                               25.00
        Total discards                               0.00
        Total operations                            34.00

*** Hourly statistics since 2020-12-18 16:00:00 ***

        No database operations have been performed.

*** Latest statistics since 2020-12-18 10:42:15 ***
        Total inserts                                8.00
        Total updates                                1.00
        Total deletes                               25.00
        Total discards                               0.00
        Total operations                            34.00

End of Statistics.

GGSCI (aixSourceServer-Ora9i) 10> stats extract EXTRSO, totalsonly *.*

Sending STATS request to EXTRACT EXTRSO ...

Start of Statistics at 2020-12-18 16:36:06.

DDL replication statistics (for all trails):

*** Total statistics since extract started     ***
        Operations                                   0.00
        Mapped operations                            0.00
        Unmapped operations                          0.00
        Other operations                             0.00
        Excluded operations                          0.00

Output to /opt/oracle/goldengate/data/DDIP9/so:

Cumulative totals for specified table(s):

*** Total statistics since 2020-12-18 10:42:15 ***
        Total inserts                              156.00
        Total updates                                0.00
        Total deletes                                0.00
        Total discards                               0.00
        Total operations                           156.00

*** Daily statistics since 2020-12-18 10:42:15 ***
        Total inserts                              156.00
        Total updates                                0.00
        Total deletes                                0.00
        Total discards                               0.00
        Total operations                           156.00

*** Hourly statistics since 2020-12-18 16:00:00 ***

        No database operations have been performed.

*** Latest statistics since 2020-12-18 10:42:15 ***
        Total inserts                              156.00
        Total updates                                0.00
        Total deletes                                0.00
        Total discards                               0.00
        Total operations                           156.00

End of Statistics.

--On the target server
GGSCI (aixTargetServer-Ora19c) 5> stats replicat REPLNA, totalsonly *.*

Sending STATS request to REPLICAT REPLNA ...

Start of Statistics at 2020-12-18 16:36:45.

DDL replication statistics:

*** Total statistics since replicat started     ***
        Operations                                         1.00
        Mapped operations                                  1.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

Cumulative totals for specified table(s):

*** Total statistics since 2020-12-18 11:42:12 ***
        Total inserts                                    526.00
        Total updates                                      1.00
        Total deletes                                    543.00
        Total upserts                                      0.00
        Total discards                                     0.00
        Total operations                                1070.00

*** Daily statistics since 2020-12-18 11:42:12 ***
        Total inserts                                    526.00
        Total updates                                      1.00
        Total deletes                                    543.00
        Total upserts                                      0.00
        Total discards                                     0.00
        Total operations                                1070.00

*** Hourly statistics since 2020-12-18 16:00:00 ***

        No database operations have been performed.

*** Latest statistics since 2020-12-18 11:42:12 ***
        Total inserts                                    526.00
        Total updates                                      1.00
        Total deletes                                    543.00
        Total upserts                                      0.00
        Total discards                                     0.00
        Total operations                                1070.00

End of Statistics.

 

If you want to remove your GoldenGate configuration

on source :
delete trandata hr.*
delete trandata bgh.*
delete trandata all_opi.*
drop user goldengate cascade;
SQL> @ddl_disable

on target :
SQL> drop user goldengate cascade;

User dropped.


NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
enable_goldengate_replication        boolean     TRUE
resource_manage_goldengate           boolean     FALSE
SQL> alter system set enable_goldengate_replication=FALSE scope = both;

System altered.

SQL> sho parameter goldengate

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
enable_goldengate_replication        boolean     FALSE
resource_manage_goldengate           boolean     FALSE
SQL>

Conclusion

  • Synchronize an oracle database 9.2.0.6 to Oracle 19c (Oracle 19.7 in our case) with GoldenGate works !!! Of course some test with more activity as we have in the real life (production database) must be done to evaluate all possible problems.
  • Oracle does some enhancements to the Oracle GoldenGate software, we don’t need any parameter to convert the trail file format between different Oracle GoldenGate versions (as we had in the past between GG prior 10g and GG post 10g), the converison is done automatically.
  • Using GoldenGate to migrate your Oracle 9i database to Oracle 19c must be compared with alternative migration solution :
    • Transportable tablespace
    • Export/Import or Datapump
  • The focus must be done on the downtime available for the migration:
    • Less Downtime you have, Oracle Export Import, DataPump or Transportable Tablespaces will be better solution.
    • Near Zero Downtime you have, GoldenGate could be a solution only if the applicative team (architect, project manager, developer) participates since, for instance, tables without primary key will prevent GoldenGate to work, thus, developer must choose column/s to be candidate to be the PK into source.

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Lazhar Felahi
Lazhar Felahi

Consultant