- I will have to perform a zero downtime migration, i.,e migrate when the application is live,
- My table's doesn't have primary keys or unique index,
- Though not a perfect data model, from the application the table uniqueness are maintained,
How am i suppose to migrate under these circumstances using Oracle Goldengate ?
Ok lets do some blogging, Let me first create the environment as per the criteria given above. Lets migrate data from one schema to another schema
/*
DB Name - OGG
Source Schema - hari@OGG
Target Schema - puthranv@OGG
----------------------------------------------------------------------
Setting the Source Env
----------------------------------------------------------------------
*/
SQL> set timing on
SQL> set linesize 150
SQL>
SQL> REM CREATING SOURCE TABLE
SQL> create table hari.T5 as select * from dba_objects where 1=2;
Table created.
Elapsed: 00:00:00.21
SQL> exec dbms_stats.gather_table_stats('HARI','T5');
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.05
SQL> ALTER TABLE hari.T5 ADD SUPPLEMENTAL LOG DATA (ALL) COLUMNS;
Table altered.
Elapsed: 00:00:00.15
SQL>
Note : No primary key / unique index in table T5
Procedures to manipulate data on T5
SQL> SQL> create or replace PROCEDURE sp_ins_gg_source AS 2 BEGIN 3 INSERT 4 INTO hari.T5(object_id, object_type) WITH temp_max AS 5 (SELECT nvl(MAX(object_id),1) AS 6 max_id 7 FROM hari.T5) 8 SELECT max_id + rownum AS 9 object_id, 10 'ORACLE' ||(max_id + rownum) AS 11 object_type 12 FROM temp_max CONNECT BY LEVEL < 11; 13 14 COMMIT; 15 16 END sp_ins_gg_source; 17 / Procedure created. Elapsed: 00:00:00.42 SQL> create or replace PROCEDURE sp_upd_gg_source AS 2 BEGIN 3 4 UPDATE t5 5 SET subobject_name = 'OGG' 6 WHERE subobject_name IS NULL 7 AND rownum < 2; 8 COMMIT; 9 10 END sp_upd_gg_source; 11 / Procedure created. Elapsed: 00:00:00.06 SQL> SQL> create or replace PROCEDURE sp_del_gg_source AS 2 BEGIN 3 4 DELETE FROM t5 5 WHERE object_id = 6 (SELECT min(object_id) 7 FROM t5 8 WHERE object_id is not null) ; 9 10 COMMIT; 11 12 END sp_del_gg_source; 13 / Procedure created. Elapsed: 00:00:00.01 SQL> SQL>
SQL> select current_scn from v$database;
CURRENT_SCN
-----------
1644171
Elapsed: 00:00:00.07
SQL>
/*
DB Name - OGG
Target Schema - puthranv@OGG
----------------------------------------------------------------------
Check for Table T5 in the Target Env
----------------------------------------------------------------------
*/
SQL> desc t5;
ERROR:
ORA-04043: object t5 does not exist
Starting Manager -
***********************************************************************
Oracle GoldenGate Manager for Oracle
Version 11.1.1.1.2 OGGCORE_11.1.1.1.2_PLATFORMS_111004.2100
Windows (optimized), Oracle 11g on Oct 5 2011 00:28:27
Copyright (C) 1995, 2011, Oracle and/or its affiliates. All rights reserved.
Starting at 2013-01-04 00:12:45
***********************************************************************
Operating System Version:
Microsoft Windows 7 , on x86
Version 6.1 (Build 7600: )
Process id: 3240
Parameters...
PORT 1350
DYNAMICPORTLIST 12010-12030,1350,1230
***********************************************************************
** Run Time Messages **
***********************************************************************
2013-01-04 00:12:45 INFO OGG-00983 Manager started (port 1350).
2013-01-04 00:14:19 INFO OGG-00963 Command received from GGSCI on host 192.168.1.2 (START EXTRACT HDPUMP1 ).
2013-01-04 00:14:20 INFO OGG-00975 EXTRACT HDPUMP1 starting.
2013-01-04 00:14:43 INFO OGG-00963 Command received from GGSCI on host 192.168.1.2 (START EXTRACT HEXT1 ).
2013-01-04 00:14:43 INFO OGG-00975 EXTRACT HEXT1 starting.
*********************************************************************** ** Running with the following parameters ** *********************************************************************** SETENV (ORACLE_SID=ogg) Set environment variable (ORACLE_SID=ogg) SETENV (NLS_LANG="AMERICAN_AMERICA.WE8MSWIN1252") Set environment variable (NLS_LANG=AMERICAN_AMERICA.WE8MSWIN1252) EXTRACT hdump1 USERID hari@ogg,PASSWORD ******** RMTHOST localhost, MGRPORT 7809 RMTTRAIL I:\OGG\TARGET\dirdat\RT NOPASSTHRU TABLE hari.t5; CACHEMGR virtual memory values (may have been adjusted) CACHEBUFFERSIZE: 64K CACHESIZE: 1G CACHEBUFFERSIZE (soft max): 4M CACHEPAGEOUTSIZE (normal): 4M PROCESS VM AVAIL FROM OS (min): 1.74G CACHESIZEMAX (strict force to disk): 1.54G Database Version: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production PL/SQL Release 11.2.0.1.0 - Production CORE 11.2.0.1.0 Production TNS for 32-bit Windows: Version 11.2.0.1.0 - Production NLSRTL Version 11.2.0.1.0 - Production Database Language and Character Set: NLS_LANG = "AMERICAN_AMERICA.WE8MSWIN1252" NLS_LANGUAGE = "AMERICAN" NLS_TERRITORY = "AMERICA" NLS_CHARACTERSET = "WE8MSWIN1252" 2013-01-04 00:25:43 INFO OGG-01226 Socket buffer size set to 27985 (flush size 27985). 2013-01-04 00:25:43 INFO OGG-01052 No recovery is required for target file I:\OGG\TARGET\dirdat\RT000000, at RBA 0 (file not opened). 2013-01-04 00:25:43 INFO OGG-01478 Output file I:\OGG\TARGET\dirdat\RT is using format RELEASE 10.4/11.1.
Starting Extract
***********************************************************************
** Running with the following parameters **
***********************************************************************
SETENV (ORACLE_SID=ogg)
Set environment variable (ORACLE_SID=ogg)
SETENV (NLS_LANG="AMERICAN_AMERICA.WE8MSWIN1252")
Set environment variable (NLS_LANG=AMERICAN_AMERICA.WE8MSWIN1252)
EXTRACT hext1
USERID hari@ogg,PASSWORD ********
EXTTRAIL I:\OGG\SOURCE\dirdat\EX
TABLE hari.t5, TOKENS (TKN1 = @GETENV("TRANSACTION", "CSN")),KEYCOLS (OBJECT_ID);
2013-01-04 00:25:53 INFO OGG-01635 BOUNDED RECOVERY: reset to initial or altered checkpoint.
Bounded Recovery Parameter:
Options = BRRESET
BRINTERVAL = 4HOURS
BRDIR = I:\OGG\SOURCE
CACHEMGR virtual memory values (may have been adjusted)
CACHEBUFFERSIZE: 64K
CACHESIZE: 1G
CACHEBUFFERSIZE (soft max): 4M
CACHEPAGEOUTSIZE (normal): 4M
PROCESS VM AVAIL FROM OS (min): 1.74G
CACHESIZEMAX (strict force to disk): 1.54G
Database Version:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
PL/SQL Release 11.2.0.1.0 - Production
CORE 11.2.0.1.0 Production
TNS for 32-bit Windows: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production
Database Language and Character Set:
NLS_LANG = "AMERICAN_AMERICA.WE8MSWIN1252"
NLS_LANGUAGE = "AMERICAN"
NLS_TERRITORY = "AMERICA"
NLS_CHARACTERSET = "WE8MSWIN1252"
2013-01-04 00:25:53 WARNING OGG-01423 No valid default archive log destination directory found for thread 1.
2013-01-04 00:25:53 INFO OGG-01515 Positioning to begin time Jan 4, 2013 12:20:11 AM.
2013-01-04 00:25:53 INFO OGG-01052 No recovery is required for target file I:\OGG\SOURCE\dirdat\EX000000, at RBA 0 (file not opened).
2013-01-04 00:25:53 INFO OGG-01478 Output file I:\OGG\SOURCE\dirdat\EX is using format RELEASE 10.4/11.1.
There are no primary keys / unique index in the table, therefore in order to retrieve the records from the redo logs KEYCOLS are being used, but this doesn't confirm the uniqueness of the record,
RUN time messages from Extract
***********************************************************************
** Run Time Messages **
***********************************************************************
2013-01-04 00:25:54 INFO OGG-01515 Positioning to begin time Jan 4, 2013 12:20:11 AM.
2013-01-04 00:25:55 INFO OGG-01516 Positioned to Sequence 27, RBA 39400464, Jan 4, 2013 12:20:11 AM.
2013-01-04 00:25:55 INFO OGG-01517 Position of first record processed Sequence 27, RBA 39400464, SCN 0.1643323, Jan 4, 2013 12:20:15 AM.
TABLE resolved (entry HARI.T5):
TABLE HARI.T5, TOKENS (TKN1 = @GETENV("TRANSACTION", "CSN")),KEYCOLS (OBJECT_ID);
Using the following key columns for source table HARI.T5: OBJECT_ID.
2013-01-04 00:26:11 INFO OGG-01021 Command received from GGSCI: STATS.
Export data for the specified SCN
expdp hari/******@ogg tables=t5 content=all directory=exp_dir dumpfile=t5.dmp logfile=expdpt5.log flashback_scn=1644171
Export: Release 11.2.0.1.0 - Production on Fri Jan 4 00:27:27 2013 Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved. ;;; Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production With the Partitioning, OLAP, Data Mining and Real Application Testing options Starting "HARI"."SYS_EXPORT_TABLE_01": hari/********@ogg tables=t5 content=all directory=exp_dir dumpfile=t5.dmp logfile=expdpt5.log flashback_scn=1644171 Estimate in progress using BLOCKS method... Processing object type TABLE_EXPORT/TABLE/TABLE_DATA Total estimation using BLOCKS method: 64 KB Processing object type TABLE_EXPORT/TABLE/TABLE Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS . . exported "HARI"."T5" 16.23 KB 241 rows Master table "HARI"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded ******************************************************************************
Importing the Dump to Target Schema
impdp puthranv/*******@ogg REMAP_SCHEMA=hari:puthranv directory=exp_dir dumpfile=t5.dmp logfile=impdpt5.log
Import: Release 11.2.0.1.0 - Production on Fri Jan 4 00:28:13 2013 Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved. ;;; Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production With the Partitioning, OLAP, Data Mining and Real Application Testing options Master table "PUTHRANV"."SYS_IMPORT_FULL_01" successfully loaded/unloaded Starting "PUTHRANV"."SYS_IMPORT_FULL_01": puthranv/********@ogg REMAP_SCHEMA=hari:puthranv directory=exp_dir dumpfile=t5.dmp logfile=impdpt5.log Processing object type TABLE_EXPORT/TABLE/TABLE Processing object type TABLE_EXPORT/TABLE/TABLE_DATA . . imported "PUTHRANV"."T5" 16.23 KB 241 rows Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS Job "PUTHRANV"."SYS_IMPORT_FULL_01" successfully completed at 00:28:21
SQL> select count(*) from puthranv.t5;
COUNT(*)
----------
241
Elapsed: 00:00:00.01
SQL>
*********************************************************************** ** Running with the following parameters ** *********************************************************************** SETENV (ORACLE_SID=ogg) Set environment variable (ORACLE_SID=ogg) SETENV (NLS_LANG="AMERICAN_AMERICA.WE8MSWIN1252") Set environment variable (NLS_LANG=AMERICAN_AMERICA.WE8MSWIN1252) REPLICAT hhr1 USERID puthranv@ogg, PASSWORD ******** ASSUMETARGETDEFS MAP hari.t5, TARGET puthranv.t5, KEYCOLS (OBJECT_ID); CACHEMGR virtual memory values (may have been adjusted) CACHEBUFFERSIZE: 64K CACHESIZE: 512M CACHEBUFFERSIZE (soft max): 4M CACHEPAGEOUTSIZE (normal): 4M PROCESS VM AVAIL FROM OS (min): 1G CACHESIZEMAX (strict force to disk): 881M Database Version: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production PL/SQL Release 11.2.0.1.0 - Production CORE 11.2.0.1.0 Production TNS for 32-bit Windows: Version 11.2.0.1.0 - Production NLSRTL Version 11.2.0.1.0 - Production Database Language and Character Set: NLS_LANG = "AMERICAN_AMERICA.WE8MSWIN1252" NLS_LANGUAGE = "AMERICAN" NLS_TERRITORY = "AMERICA" NLS_CHARACTERSET = "WE8MSWIN1252" For further information on character set settings, please refer to user manual.
*********************************************************************** ** Run Time Messages ** *********************************************************************** Opened trail file I:\OGG\TARGET\dirdat\RT000000 at 2013-01-04 00:50:36 2013-01-04 00:50:36 INFO OGG-01373 User requested start after CSN 1644171. 2013-01-04 00:50:36 INFO OGG-01374 Transaction delivery commencing at position Seqno 0, RBA 28928, Transaction ID 7.23.849, CSN 1644183, 70 transaction(s) skipped. MAP resolved (entry HARI.T5): MAP HARI.T5, TARGET puthranv.t5, KEYCOLS (OBJECT_ID); 2013-01-04 00:50:48 WARNING OGG-00869 No unique key is defined for table T5. All viable columns will be used to represent the key, but may not guarantee uniqueness. KEYCOLS may be used to define the key. Using following columns in default map by name: OWNER, OBJECT_NAME, SUBOBJECT_NAME, OBJECT_ID, DATA_OBJECT_ID, OBJECT_TYPE, CREATED, LAST_DDL_TIME, TIMESTAMP, STATUS, TEMPORARY, GENERATED, SECONDARY, NAMESPACE, EDITION_NAME Using the following key columns for target table PUTHRANV.T5: OBJECT_ID. 2013-01-04 00:50:48 INFO OGG-01021 Command received from GGSCI: STATS.
KEYCOL map though specified at the replicate, they are only used during the extraction process. Lets check the data at the Source and Target
At Source
SQL> show user
USER is "HARI"
SQL>
SQL>
SQL> select systimestamp from dual;
SYSTIMESTAMP
---------------------------------------------------------------------------
04-JAN-13 01.13.17.727000 AM +05:30
Elapsed: 00:00:00.01
SQL> REM CHECK FOR UPDATES
SQL> select count(*) from t5 where subobject_name = 'OGG';
COUNT(*)
----------
294
Elapsed: 00:00:00.00
SQL>
SQL> REM CHECK FOR INSERT AND DELETE
SQL> select count(*) from t5;
COUNT(*)
----------
523
Elapsed: 00:00:00.00
SQL> select count(*) from t5;
COUNT(*)
----------
523
Elapsed: 00:00:00.00
SQL>
SQL> show user
USER is "PUTHRANV"
SQL>
SQL> select systimestamp from dual;
SYSTIMESTAMP
---------------------------------------------------------------------------
04-JAN-13 01.13.18.160000 AM +05:30
Elapsed: 00:00:00.00
SQL> REM CHECK FOR UPDATES
SQL> select count(*) from t5 where subobject_name = 'OGG';
COUNT(*)
----------
293
Elapsed: 00:00:00.00
SQL> select count(*) from t5 where subobject_name = 'OGG';
COUNT(*)
----------
294
Elapsed: 00:00:00.00
SQL> REM CHECK FOR INSERT AND DELETE
SQL> select count(*) from t5;
COUNT(*)
----------
524
Elapsed: 00:00:00.00
SQL> select count(*) from t5;
COUNT(*)
----------
523
Elapsed: 00:00:00.00
SQL>
Thus we are able to migrate data while the application is running using Oracle GoldenGate, even when there is no primary key / unique index on the table.
