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 :
oracle@aixSourceServer-Ora9i: /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:
oracle@aixSourceServer-Ora9i: /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:
oracle@aixSourceServer-Ora9i: /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 oracle@aixSourceServer-Ora9i:/opt/oracle/software/goldengate> oracle@aixSourceServer-Ora9i:/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 oracle@aixSourceServer-Ora9i: /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:
oracle@aixSourceServer-Ora9i: /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 oracle@aixSourceServer-Ora9i: /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:
oracle@aixSourceServer-Ora9i:/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 oracle@ 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:
oracle@aixSourceServer-Ora9i: /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:
oracle@aixSourceServer-Ora9i: /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 goldengate@LGGATE alias goldengate Password: Credential store altered. GGSCI (aixTargetServer-Ora19c) 1> dblogin useridalias goldengate Successfully logged into database. GGSCI (aixTargetServer-Ora19c as goldengate@LGGATE) 2>
Add the replicat:
--add replicat GGSCI (aixTargetServer-Ora19c) 5> dblogin useridalias goldengate Successfully logged into database. GGSCI (aixTargetServer-Ora19c as goldengate@LGGATE) 8> add replicat REPLSO, exttrail /data/oradata/goldengate/data/LGGATE/so,checkpointtable GOLDENGATE.CHECKPOINT; REPLICAT added. GGSCI (aixTargetServer-Ora19c as goldengate@LGGATE) 9> add replicat REPLNA, exttrail /data/oradata/goldengate/data/LGGATE/na,checkpointtable GOLDENGATE.CHECKPOINT; REPLICAT added. GGSCI (aixTargetServer-Ora19c as goldengate@LGGATE) 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 goldengate@LGGATE) 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 goldengate@LGGATE) 18> start replicat REPLNA, atcsn 10228186709471 Sending START request to MANAGER ... REPLICAT REPLNA starting --Start replicat REPLS0 GGSCI (aixTargetServer-Ora19c as goldengate@LGGATE) 18> start replicat REPLSO, atcsn 10228186709471 Sending START request to MANAGER ... REPLICAT REPLSO starting GGSCI (aixTargetServer-Ora19c as goldengate@LGGATE) 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.
![Thumbnail [60x60]](https://www.dbi-services.com/blog/wp-content/uploads/2022/10/LFE_web-min-scaled.jpg)
![Thumbnail [90x90]](https://www.dbi-services.com/blog/wp-content/uploads/2022/08/DWE_web-min-scaled.jpg)
![Thumbnail [90x90]](https://www.dbi-services.com/blog/wp-content/uploads/2022/08/OLS_web-min-scaled.jpg)
Nicolo
04.08.2023I need to change my small Ora db (300kb) to 19c. Can you help to convert? What is your charge?