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 100 M;

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,
2 t. tbl_cnt,
3 s. sup_log_grp_cnt,
4 t. tbl_cnt-s. sup_log_grp_cnt "Diff"
5 from (select owner, count (*) tbl_cnt from dba_tables group by owner) t,
6 (select owner, count (*) sup_log_grp_cnt
7 from dba_log_groups
8 group by owner) s
9 where t. owner = s. owner (+)
10 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 <table_name> add supplemental log data (all) columns;

Alter database <table_name> 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 <table_name> ".

[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,200 7, Oracle. All Rights Reserved.

Enter password:

Connected:
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
2 from (select owner, table_name from dba_tables) t,
3 (select owner, table_name from dba_log_groups) s
4 where t. owner = s. owner (+)
5 and t. table_name = s. table_name (+)
6 and s. table_name is null
7 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. MERs
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,201 3, 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. Administrative ments

Logging of supplemental redo data enabled for table HR. lifecycle.

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,
2 t. tbl_cnt,
3 s. sup_log_grp_cnt,
4 t. tbl_cnt-s. sup_log_grp_cnt "Diff"
5 from (select owner, count (*) tbl_cnt from dba_tables group by owner) t,
6 (select owner, count (*) sup_log_grp_cnt
7 from dba_log_groups
8 group by owner) s
9 where t. owner = s. owner (+)
10 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
2 (id number,
3 ts date,
4 committime date,
5 groupname varchar2 (8 ),
6 host varchar2 (60 ),
7 local_insert_time date );

Table created.