Zero Downtime Upgrade of Oracle 10g to Oracle 11g Using GoldenGate — 3

DDL Setup Steps

SQL> grant execute on utl_file to ggs;

Grant succeeded.

 

Create GLOBALS file

[oracle@zwc ~]$ cd /ggs/
[oracle@zwc ggs]$ cat GLOBALS 
GGSCHEMA GGS

 

run marker_setup.sql

[oracle@zwc ggs]$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.4.0 – Production on Fri Jun 6 18:56:32 2014

Copyright (c) 1982, 2007, Oracle.  All Rights Reserved.

Connected to:
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> @marker_setup.sql

Marker setup script

You will be prompted for the name of a schema for the Oracle GoldenGate database objects.
NOTE: The schema must be created prior to running this script.
NOTE: Stop all DDL replication before starting this installation.

Enter Oracle GoldenGate schema name:ggs

Marker setup table script complete, running verification script…
Please enter the name of a schema for the GoldenGate database objects:
Setting schema name to GGS

MARKER TABLE
——————————-
OK

MARKER SEQUENCE
——————————-
OK

Script complete.

 

run ddl_setup.sql

SQL> alter system set recyclebin=off;

System altered.

SQL> create tablespace ggs datafile '/u01/app/oracle/oradata/zwc/ggs01.dbf' size 100M autoextend on;

Tablespace created.

SQL> 
SQL> alter user ggs default tablespace ggs;

User altered.

SQL> @ddl_setup.sql

Oracle GoldenGate DDL Replication setup script

Verifying that current user has privileges to install DDL Replication…

You will be prompted for the name of a schema for the Oracle GoldenGate database objects.
NOTE: For an Oracle 10g source, the system recycle bin must be disabled. For Oracle 11g and later, it can be enabled.
NOTE: The schema must be created prior to running this script.
NOTE: Stop all DDL replication before starting this installation.

Enter Oracle GoldenGate schema name:ggs

Working, please wait …
Spooling to file ddl_setup_spool.txt

Checking for sessions that are holding locks on Oracle Golden Gate metadata tables …

Check complete.

Using GGS as a Oracle GoldenGate schema name.

Working, please wait …

DDL replication setup script complete, running verification script…
Please enter the name of a schema for the GoldenGate database objects:
Setting schema name to GGS

CLEAR_TRACE STATUS:

Line/pos             Error
——————– —————————————————————–
No errors            No errors

CREATE_TRACE STATUS:

Line/pos             Error
——————– —————————————————————–
No errors            No errors

TRACE_PUT_LINE STATUS:

Line/pos             Error
——————– —————————————————————–
No errors            No errors

INITIAL_SETUP STATUS:

Line/pos             Error
——————– —————————————————————–
No errors            No errors

DDLVERSIONSPECIFIC PACKAGE STATUS:

Line/pos             Error
——————– —————————————————————–
No errors            No errors

DDLREPLICATION PACKAGE STATUS:

Line/pos             Error
——————– —————————————————————–
No errors            No errors

DDLREPLICATION PACKAGE BODY STATUS:

Line/pos             Error
——————– —————————————————————–
No errors            No errors

DDL IGNORE TABLE
———————————–
OK

DDL IGNORE LOG TABLE
———————————–
OK

DDLAUX  PACKAGE STATUS:

Line/pos             Error
——————– —————————————————————–
No errors            No errors

DDLAUX PACKAGE BODY STATUS:

Line/pos             Error
——————– —————————————————————–
No errors            No errors

SYS.DDLCTXINFO  PACKAGE STATUS:

Line/pos             Error
——————– —————————————————————–
No errors            No errors

SYS.DDLCTXINFO  PACKAGE BODY STATUS:

Line/pos             Error
——————– —————————————————————–
No errors            No errors

DDL HISTORY TABLE
———————————–
OK

DDL HISTORY TABLE(1)
———————————–
OK

DDL DUMP TABLES
———————————–
OK

DDL DUMP COLUMNS
———————————–
OK

DDL DUMP LOG GROUPS
———————————–
OK

DDL DUMP PARTITIONS
———————————–
OK

DDL DUMP PRIMARY KEYS
———————————–
OK

DDL SEQUENCE
———————————–
OK

GGS_TEMP_COLS
———————————–
OK

GGS_TEMP_UK
———————————–
OK

DDL TRIGGER CODE STATUS:

Line/pos             Error
——————– —————————————————————–
No errors            No errors

DDL TRIGGER INSTALL STATUS
———————————–
OK

DDL TRIGGER RUNNING STATUS
———————————————————————-
ENABLED

STAYMETADATA IN TRIGGER
———————————————————————-
OFF

DDL TRIGGER SQL TRACING
———————————————————————-
0

DDL TRIGGER TRACE LEVEL
———————————————————————-
0

LOCATION OF DDL TRACE FILE
————————————————————————————————————————
/u01/app/oracle/admin/zwc/udump/ggs_ddl_trace.log

Analyzing installation status…

STATUS OF DDL REPLICATION
————————————————————————————————————————
SUCCESSFUL installation of DDL Replication software components

Script complete.

 

run role_setup.sql

SQL> @role_setup.sql

GGS Role setup script

This script will drop and recreate the role GGS_GGSUSER_ROLE
To use a different role name, quit this script and then edit the params.sql script to change the gg_role parameter to the preferred name. (Do not run the script.)

You will be prompted for the name of a schema for the GoldenGate database objects.
NOTE: The schema must be created prior to running this script.
NOTE: Stop all DDL replication before starting this installation.

Enter GoldenGate schema name:ggs
Wrote file role_setup_set.txt

PL/SQL procedure successfully completed.

Role setup script complete

Grant this role to each user assigned to the Extract, GGSCI, and Manager processes, by using the following SQL command:

GRANT GGS_GGSUSER_ROLE TO <loggedUser>

where <loggedUser> is the user assigned to the GoldenGate processes.

SQL> grant ggs_ggsuser_role to ggs;

Grant succeeded.

 

run ddl_enable.sql

SQL> @ddl_enable.sql

Trigger altered.

 

Run ddl_pin.sql to improve the performance of DDL trigger.In order to run this,make sure dbms_shared_pool package is installed. If not then install that package first,which can be installed by running following script.

SQL> @?/rdbms/admin/dbmspool.sql

Package created.

Grant succeeded.

View created.

Package body created.

 

run ddl_pin.sql with "ggs" as argument

SQL> @ddl_pin.sql ggs

PL/SQL procedure successfully completed.

PL/SQL procedure successfully completed.

PL/SQL procedure successfully completed.

 

Create Parameter Files & Start Manager

[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, 2013, Oracle and/or its affiliates. All rights reserved.

 

GGSCI (zwc) 1> view param mgr

PORT 7809
DYNAMICPORTLIST 7850-7860
LAGINFOMINUTES 0
LAGREPORTMINUTES 10
LAGCRITICALMINUTES 60
AUTORESTART EXTRACT E*, RETRIES 10, WAITMINUTES 5, RESETMINUTES 1440
AUTORESTART EXTRACT P*, RETRIES 10, WAITMINUTES 5, RESETMINUTES 1440
PURGEOLDEXTRACTS /ggs/trails/source/*, USECHECKPOINTS, MINKEEPDAYS 10, FREQUENCYMINUTES 15

GGSCI (zwc) 2> start mgr

Manager started.

GGSCI (zwc) 3> info mgr

Manager is running (IP port zwc.7809).

GGSCI (zwc) 4> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING                                           

GGSCI (zwc) 5> 

 

Create directory for trail files

mkdir -p /ggs/trails/source

 

Create parameter file for Extract

GGSCI (zwc) 8> view param e10gdb

EXTRACT E10GDB
SETENV (ORACLE_SID=zwc)
SETENV (ORACLE_HOME=/u01/app/oracle/product/10.2.0/db_1)
SETENV (NLS_LANG="AMERICAN_AMERICA.ZHS16GBK")
USERID ggs, PASSWORD ggs
EXTTRAIL /ggs/trails/source/aa
TRANLOGOPTIONS EXCLUDEUSER GGS
TRANLOGOPTIONS LOGRETENTION DISABLED
WARNLONGTRANS 3H CHECKINTERVAL 30m
REPORTCOUNT EVERY 10 MINUTES, RATE
DISCARDFILE /ggs/dirdef/E10GDB.dec, APPEND
DISCARDROLLOVER ON SUNDAY
GETUPDATEBEFORES
DDL INCLUDE MAPPED
DDLOPTIONS ADDTRANDATA RETRYOP RETRYDELAY 10 MAXRETRIES 10
TABLE HR.*;
TABLE OE.*;
TABLE PM.*;
SEQUENCE HR.*;
SEQUENCE OE.*;
SEQUENCE PM.*;

 

Create parameter filefile for Pump

GGSCI (zwc) 10> view param p10gdb

EXTRACT P10GDB
SETENV (ORACLE_SID=zwc)
SETENV (ORACLE_HOME=/u01/app/oracle/product/10.2.0/db_1)
SETENV (NLS_LANG="AMERICAN_AMERICA.ZHS16GBK")
PASSTHRU
RMTHOST 192.168.1.61, MGRPORT 7809, &
COMPRESS, TCPBUFSIZE 262144, TCPFLUSHBYTES 262144
RMTTRAIL /ggs/trails/target/aa
STATOPTIONS REPORTFETCH
REPORTCOUNT EVERY 10 MINUTES, RATE
DISCARDFILE /ggs/dirdef/P10GDB.dec, APPEND
DISCARDROLLOVER ON SUNDAY
TABLE HR.*;
TABLE OE.*;
TABLE PM.*;
SEQUENCE HR.*;
SEQUENCE OE.*;
SEQUENCE PM.*;

 

In 10.2 database,follow there setps to make log_retention enable.

SQL> exec dbms_streams_auth.grant_admin_privilege('GGS');

PL/SQL procedure successfully completed.

SQL> grant insert on system.logmnr_restart_ckpt$ to ggs;

Grant succeeded.

SQL> grant update on sys.streams$_capture_process to ggs;

Grant succeeded.

SQL> grant become user to ggs;

Grant succeeded.

 

Add following parameter in the extract.In this demo,we are not enabling log retention.

TRANLOGOPTIONS LOGRETENTION SR

 

Add Extract and Pump

[oracle@zwc diroby]$ cat add_extract.oby 
dblogin userid ggs password ggs
add extract e10gdb, tranlog, begin now
add exttrail /ggs/trails/source/aa, extract e10gdb, megabytes 100
[oracle@zwc diroby]$ cat add_pump.oby 
add extract p10gdb, exttrailsource /ggs/trails/source/aa, extseqno 0, extrba 0
add rmttrail /ggs/trails/target/aa, extract p10gdb, megabytes 100

 

[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, 2013, Oracle and/or its affiliates. All rights reserved.

 

GGSCI (zwc) 1> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING                                           

GGSCI (zwc) 2> obey ./diroby/add_extract.oby

GGSCI (zwc) 3> dblogin userid ggs password ggs

Successfully logged into database.

GGSCI (zwc) 4> add extract e10gdb, tranlog, begin now

EXTRACT added.

GGSCI (zwc) 5> add exttrail /ggs/trails/source/aa, extract e10gdb, megabytes 100

EXTTRAIL added.

GGSCI (zwc) 6> obey ./diroby/add_pump.oby

GGSCI (zwc) 7> add extract p10gdb, exttrailsource /ggs/trails/source/aa, extseqno 0, extrba 0

EXTRACT added.

GGSCI (zwc) 8> add rmttrail /ggs/trails/target/aa, extract p10gdb, megabytes 100

RMTTRAIL added.

GGSCI (zwc) 9> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING                                           
EXTRACT     STOPPED     E10GDB      00:00:00      00:00:14    
EXTRACT     STOPPED     P10GDB      00:00:00      00:00:03    

 

Start only the extract at this point and not the pump

GGSCI (zwc) 2> start e10gdb

Sending START request to MANAGER …
EXTRACT E10GDB starting

GGSCI (zwc) 3> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING                                           
EXTRACT     STOPPED     E10GDB      00:00:00      00:04:12    
EXTRACT     STOPPED     P10GDB      00:00:00      00:04:02    

GGSCI (zwc) 4> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING                                           
EXTRACT     RUNNING     E10GDB      00:04:13      00:00:02    
EXTRACT     STOPPED     P10GDB      00:00:00      00:04:05    

 

Setup Sequence Replication 

Oracle GoldenGate supports Oracle Sequences replication.There are some limitations with sequence replication.

1.sequence replication in bi-directional is not supported

2.cache size,increment interval,and cycle/no-cycle settings must be identical on source and target databases

SQL> @sequence.sql
Please enter the name of a schema for the GoldenGate database objects:
ggs
Setting schema name to GGS

UPDATE_SEQUENCE STATUS:

Line/pos
——————–
Error
—————————————————————–
No errors
No errors

GETSEQFLUSH

Line/pos
——————–
Error
—————————————————————–
No errors
No errors

SEQTRACE

Line/pos
——————–
Error
—————————————————————–
No errors
No errors

REPLICATE_SEQUENCE STATUS:

Line/pos
——————–
Error
—————————————————————–
No errors
No errors

STATUS OF SEQUENCE SUPPORT
————————————————————–
SUCCESSFUL installation of Oracle Sequence Replication support
SQL> grant execute on ggs.updateSequence to ggs;

Grant succeeded.

SQL> grant execute on ggs.replicateSequence to ggs;

Grant succeeded.

SQL> alter table sys.seq$ add supplemental log data (primary key) columns;

Table altered.

 

Restart extract,flush sequence

[oracle@zwc diroby]$ cat flush_seq.oby 
flush sequence hr.*
flush sequence oe.*
flush sequence pm.*

[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, 2013, Oracle and/or its affiliates. All rights reserved.

 

GGSCI (zwc) 1> stop extract e10gdb

Sending STOP request to EXTRACT E10GDB …
Request processed.

GGSCI (zwc) 2> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING                                           
EXTRACT     STOPPED     E10GDB      00:00:00      00:00:01    
EXTRACT     STOPPED     P10GDB      00:00:00      00:22:40    

GGSCI (zwc) 3> start extract e10gdb

Sending START request to MANAGER …
EXTRACT E10GDB starting

GGSCI (zwc) 4> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING                                           
EXTRACT     RUNNING     E10GDB      00:00:00      00:00:09    
EXTRACT     STOPPED     P10GDB      00:00:00      00:22:47    

GGSCI (zwc) 5> dblogin userid ggs password ggs
Successfully logged into database.

GGSCI (zwc) 6> obey ./diroby/flush_seq.oby

GGSCI (zwc) 7> flush sequence hr.*

Successfully flushed 3 sequence(s) hr.*
GGSCI (zwc) 8> flush sequence oe.*

Successfully flushed 1 sequence(s) oe.*
GGSCI (zwc) 9> flush sequence pm.*

ERROR: Sequence pm.* does not exist.

 

Setup Heartbeat Job

In order to start the pump,we need to make target 11g database server ready,by installing Oracle GoldenGate and starting manager.Before we do that lets setup a heartbeat job in cron.

The purpose of heartbeat job is to keep updating ggs_mon.ggs_heartbeat table,those transactions will be extracted by Oracle GoldenGate,causing extract RBA to move,and also keep record of lag time in ggs_lagtime table on the target side.

[oracle@zwc ~]$ cd /ggs
[oracle@zwc ggs]$ mkdir scripts
[oracle@zwc ggs]$ touch ggs_heartbeat.sh
[oracle@zwc ggs]$ chmod 755 ggs_heartbeat.sh 
[oracle@zwc ggs]$ cat ggs_heartbeat.sh 
#!/bin/sh
#Purpose:  To update heartbeat table for OGG monitoring
cd ${HOME}
source ${HOME}/.bash_profile > /dev/null 2>&1

echo $GG_HOME
cd $GG_HOME
DATETIME=`date`
    echo "Heartbeat for DB ${ORACLE_SID} start at ${DATETIME}…\n"
sqlplus -L -S ggs_mon/ggs_mon << EOF 2>&1
update ggs_heartbeat set ts = sysdate;
commit;
exit
EOF

 

Setup cron for this to run every minute

* * * * * /ggs/ggs_heartbeat.sh > /tmp/ggshb.log 2>&1

 

In extract e10gdb parameter file,add  following two lines,and restart extract

GGSCI (zwc) 15> view param e10gdb

EXTRACT E10GDB
SETENV (ORACLE_SID=zwc)
SETENV (ORACLE_HOME=/u01/app/oracle/product/10.2.0/db_1)
SETENV (NLS_LANG="AMERICAN_AMERICA.ZHS16GBK")
USERID ggs, PASSWORD ggs
EXTTRAIL /ggs/trails/source/aa
TRANLOGOPTIONS EXCLUDEUSER GGS
TRANLOGOPTIONS LOGRETENTION DISABLED
WARNLONGTRANS 3H CHECKINTERVAL 30m
REPORTCOUNT EVERY 10 MINUTES, RATE
DISCARDFILE /ggs/dirdef/E10GDB.dec, APPEND
DISCARDROLLOVER ON SUNDAY
GETUPDATEBEFORES
DDL INCLUDE MAPPED
DDLOPTIONS ADDTRANDATA RETRYOP RETRYDELAY 10 MAXRETRIES 10
TABLE HR.*;
TABLE OE.*;
TABLE PM.*;
SEQUENCE HR.*;
SEQUENCE OE.*;
SEQUENCE PM.*;

TABLE GGS_MON.ggs_heartbeat ,
   Tokens (host = @GETENV ("GGENVIRONMENT", "HOSTNAME") );

 

In pump p10gdb parameter file,add following line.After editing the pump parameter file,if pump is running then stop and restart.

GGSCI (zwc) 18> view param p10gdb

EXTRACT P10GDB
SETENV (ORACLE_SID=zwc)
SETENV (ORACLE_HOME=/u01/app/oracle/product/10.2.0/db_1)
SETENV (NLS_LANG="AMERICAN_AMERICA.ZHS16GBK")
PASSTHRU
RMTHOST 192.168.1.61, MGRPORT 7809, &
COMPRESS, TCPBUFSIZE 262144, TCPFLUSHBYTES 262144
RMTTRAIL /ggs/trails/target/aa
STATOPTIONS REPORTFETCH
REPORTCOUNT EVERY 10 MINUTES, RATE
DISCARDFILE /ggs/dirdef/P10GDB.dec, APPEND
DISCARDROLLOVER ON SUNDAY
TABLE HR.*;
TABLE OE.*;
TABLE PM.*;
SEQUENCE HR.*;
SEQUENCE OE.*;
SEQUENCE PM.*;

TABLE GGS_MON.GGS_HEARTBEAT;

 

Also in replicat we have not created yet,but when we will create replicat parameter file,then we will add following MAP statement in it to make heartbeat work properly.

MAP ggs_mon.ggs_heartbeat, TARGET ggs_mon.ggs_lagtime,

       KEYCOLS (ID),

 INSERTALLRECORDS,

   COLMAP (USEDEFAULTS,

            id = 0,

            committime = @GETENV ("GGHEADER", "COMMITTIMESTAMP"),

            groupname  = @GETENV ("GGENVIRONMENT", "GROUPNAME"),

            host       = @TOKEN("host"),

            local_insert_time = @DATENOW()

         );

 

Now if we invoke ggsci and run "info" command on extract couple of times after few intervals,we will be able see that its RBA is moving even when there is no application activity going on in the database.

GGSCI (zwc) 48> info e10gdb

EXTRACT    E10GDB    Last Started 2014-06-06 21:24   Status RUNNING
Checkpoint Lag       00:00:00 (updated 00:00:07 ago)
Log Read Checkpoint  Oracle Redo Logs
                     2014-06-06 21:24:36  Seqno 11, RBA 12435456
                     SCN 0.778809 (778809)

GGSCI (zwc) 49> info e10gdb

EXTRACT    E10GDB    Last Started 2014-06-06 21:24   Status RUNNING
Checkpoint Lag       00:00:00 (updated 00:00:06 ago)
Log Read Checkpoint  Oracle Redo Logs
                     2014-06-06 21:24:54  Seqno 11, RBA 12436992
                     SCN 0.778815 (778815)