Deploying OGG 11.1 Replication from Oracle 10g FS to Oracle 11g RAC

Oracle GoldenGate V11.1.1.1.0 for Oracle 10g 64bit on Windows 2003, 2008
Oracle GoldenGate V11.1.1.1.0 for Oracle 11g on Linux x86-64

1 Environment Preparation

Source:

IP: 192.168.1.50
Hostname: oracle10g
Database: 10.2.0.4 - 64bit
SID: orcl
OS: Windows Server 2008 R2

Target:

IP: 192.168.1.51
Hostname: rac-cluster
Database: 11.2.0.4.0 - 64bit
SID: orcl
OS: Oracle Linux Server 6.10

Requirements:

Replicat schema: zemr
Eable DDL replication
Unidirectional replication

2 Installing OGG 11.1 Software

2.1 On source.

K:\> mkdir k:\ggs
K:\> cd k:\ggs
K:\> .\ggsci

GGSCI> create subdirs

2.2 On target.

~]$ mkdir /soft/ggs
~]$ cd /soft/ggs
~]$ ./ggsci

GGSCI> create subdirs

3 Configuring Source Database

3.1 Turning recycle off.

SQL> show parameter recyclebin
SQL> alter system set recyclebin=off scope=both;
SQL> shutdown immediate;
SQL> startup

For 10gr2, set RECYCLEBIN in parameter file to OFF. For 10gr1, set _RECYCLEBIN in parameter file to FALSE. Then restart database and installation.

3.2 Query archive mode, supplemental log data min, force logging.

SQL> select log_mode,supplemental_log_data_min,force_logging from v$database;

LOG_MODE SUPPLEME FOR
------------ -------- ---
NOARCHIVELOG NO NO

3.3 Enabling archive mode.

SQL> alter system set log_archive_dest_1='location=k:\archive' scope=both;
SQL> shutdown immediate;
SQL> startup mount;
SQL> alter database archivelog;
SQL> alter database open;

3.4 Enabling supplemetal log data and force logging.

SQL> alter database add supplemental log data;
SQL> alter database add supplemental log data (primary key) columns;
SQL> alter database add supplemental log data (foreign key) columns;
SQL> alter database add supplemental log data (unique) columns;
SQL> alter database force logging;

SQL> alter system archive log current;

SQL> select log_mode,supplemental_log_data_min,force_logging from v$database;

LOG_MODE SUPPLEME FOR
------------ -------- ---
ARCHIVELOG YES YES

3.5 Creating the OGG user.

create tablespace ogg_tbs datafile 'D:\oradata\fydb\ogg_tbs.dbf' size 200m autoextend on next 10m maxsize 10G extent management local segment space management auto;

create user ggsadmin identified by ggsdba11 default tablespace ogg_tbs temporary tablespace temp quota unlimited on ogg_tbs;

GRANT CONNECT TO ggsadmin;
GRANT ALTER ANY TABLE TO ggsadmin;
GRANT ALTER SESSION TO ggsadmin;
GRANT CREATE SESSION TO ggsadmin;
GRANT FLASHBACK ANY TABLE TO ggsadmin;
GRANT SELECT ANY DICTIONARY TO ggsadmin;
GRANT SELECT ANY TABLE TO ggsadmin;
GRANT RESOURCE TO ggsadmin;
GRANT DROP ANY TABLE TO ggsadmin;
GRANT DBA TO ggsadmin;
GRANT INSERT ANY TABLE TO ggsadmin;
GRANT UPDATE ANY TABLE TO ggsadmin;
GRANT DELETE ANY TABLE TO ggsadmin;
GRANT sysdba TO ggsadmin;  -- DDL replicate

4 Configuring Source OGG Processes

4.1 Configure the mgr process.

GGSCI> edit params mgr
port 7809
dynamicportlist 7810-7830, 7860
autostart extract *
autorestart extract *, retries 3, waitminutes 3, resetminutes 5
purgeoldextracts k:\ggs\dirdat\cc*, usecheckpoints, minkeepdays 7

GGSCI> start mgr
GGSCI> info mgr
Manager is running (IP port emrrac1.7809).

4.2 Specify the ggschema for DDL replicate.

GGSCI> edit params ./GLOBALS
ggschema ggsadmin

4.3 Run the scripts to enable DDL replicate.

K:\> cd k:\ggs
K:\> sqlplus / as sysdba

SQL> @marker_setup.sql
Enter GoldenGate schema name:ggsadmin

SQL> @ddl_setup.sql
Enter GoldenGate schema name:ggsadmin
Enter mode of installation:INITIALSETUP
Enter yes or no:yes

SQL> @role_setup.sql
Enter GoldenGate schema name:ggsadmin

SQL> grant GGS_GGSUSER_ROLE to ggsadmin;

SQL> @ddl_enable.sql

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

SQL> @ddl_pin ggsadmin

4.4 Configure the capture process.

GGSCI> dblogin userid ggsadmin, password ggsdba11

GGSCI> edit params tlcapt
extract tlcapt
userid ggsadmin, password ggsdba11
exttrail k:\ggs\dirdat\cc,
ddl include all
table zemr.*;

GGSCI> add extract tlcapt, tranlog, begin now
GGSCI> add exttrail k:\ggs\dirdat\cc, extract tlcapt, megabytes 50

4.5 Configure the datapump process.

GGSCI> edit params tlpump
extract tlpump
rmthost 168.1.1.51, mgrport 7809
rmttrail /soft/ggs/dirdat/pt
passthru
table zemr.*;

GGSCI> add extract tlpump, exttrailsource ./dirdat/cc
GGSCI> add rmttrail ./dirdat/pt, extract tlpump, megabytes 50

GGSCI> info all
Program Status Group Lag Time Since Chkpt
MANAGER RUNNING
EXTRACT STOPPED TLCAPT 00:00:00 00:17:38
EXTRACT STOPPED TLPUMP 00:00:00 00:00:46

5 Configuring Target Database

5.1 Check the long transactions on source, wait or kill it.

SQL> select * from gv$transaction;

5.2 Start the capture and datapump processes.

GGSCI> start extract extl
GGSCI> start extract tlpump

5.3 Record the database SCN on source.

SQL> select dbms_flashback.get_system_change_number current_scn from dual;
SQL> select current_scn from v$database;

CURRENT_SCN
-----------
12709546804929

5.4 Expdp data from source.

SQL> create directory expdp as 'k:\expdp';
SQL> grant read,write on directory expdp to sys;
SQL> grant read,write on directory expdp to zemr;

~]$ expdp userid='/ as sysdba' directory=expdp dumpfile=fyzemr_%U.dmp filesize=8g parallel=8 EXCLUDE=TABLE:\"IN('ZEMR_EMR_CONTENT')\" logfile=fyzemr_exclude.log flashback_scn=12709546804929

~]$ expdp zemr/zemr DIRECTORY=expdp tables=(ZEMR_EMR_CONTENT) DUMPFILE=content.dmp LOGFILE=exp_tab.log flashback_scn=12709546804929

5.5 Impdp data to target.

SQL> create directory impdp as '/backup';
SQL> grant read,write on directory impdp to sys;
SQL> grant read,write on directory impdp to zemr;

~]$ impdp \'sys/oracle as sysdba\' directory=impdp dumpfile=fyzemr_%U.DMP parallel=4 cluster=N logfile=impdb.log

~]$ impdp zemr/zemr directory=impdp dumpfile=content%U.DMP cluster=N logfile=content.log

5.6 Disabling constraints/triggers/jobs.

SET SERVEROUTPUT ON SIZE 100000
BEGIN
  for c in (select 'ALTER TABLE ' || TABLE_NAME || ' DISABLE CONSTRAINT ' ||
                   constraint_name || ' ' as v_sql
              from user_constraints
             where CONSTRAINT_TYPE = 'R') loop
    DBMS_OUTPUT.PUT_LINE(C.V_SQL);
    begin
      EXECUTE IMMEDIATE c.v_sql;
    exception
      when others then
        dbms_output.put_line(sqlerrm);
    end;
  end loop;
  for c in (select 'ALTER TABLE ' || TNAME || ' DISABLE ALL TRIGGERS ' AS v_sql
              from tab
             where tabtype = 'TABLE') loop
    dbms_output.put_line(c.v_sql);
    begin
      execute immediate c.v_sql;
    exception
      when others then
        dbms_output.put_line(sqlerrm);
    end;
  end loop;
end;
/

6 Configuring Target OGG Processes

6.1 Configure the mgr process.

GGSCI> edit params mgr
port 7809
dynamicportlist 7810-7830, 7860
autostart replicat *
autorestart replicat *, retries 3, waitminutes 3, resetminutes 5
lagreporthours 1
laginfominutes 3
lagcriticalminutes 5
purgeoldextracts /soft/ggs/dirdat/pt*, usecheckpoints, minkeepdays 3

GGSCI> start mgr
GGSCI> info mgr
Manager is running (IP port emrrac1.7809).

6.2 Add the checkpointtable.

GGSCI> dblogin userid ggsadmin, password ggsdba11
GGSCI> add checkpointtable ggsadmin.checkpoint

GGSCI> edit params ./GLOBALS
checkpointtable ggsadmin.checkpoint

6.3 Configure the replicat process.

GGSCI> add replicat repl, exttrail /soft/ggs/dirdat/pt, checkpointtable ggsadmin.checkpoint

GGSCI> edit params repl
replicat repl
assumetargetdefs
applynoopupdates
userid ggsadmin, password ggsdba11
reperror (default, discard)
ddlerror default discard
ddloptions report
discardfile /soft/ggs/dirrpt/repl.dsc,purge,megabytes 4096
map zemr.*, target zemr.*;

GGSCI> info all
Program Status Group Lag Time Since Chkpt
MANAGER RUNNING
REPLICAT STOPPED REPL 00:00:00 00:01:03

Note: Keep the replicat process in a stopped state and do not start it.

7 Start Replication

GGSCI> start replicat repl, aftercsn 12709546804929

8 Swithover

8.1 Verify the consistency of the data.

8.2 Stop OGG replication

8.3 Enable constraints/triggers/jobs.

SET SERVEROUTPUT ON SIZE 100000
BEGIN
  for c in (select 'ALTER TABLE ' || TABLE_NAME || ' ENABLE CONSTRAINT ' ||
                   constraint_name || ' ' as v_sql
              from user_constraints
             where CONSTRAINT_TYPE = 'R') loop
    DBMS_OUTPUT.PUT_LINE(C.V_SQL);
    begin
      EXECUTE IMMEDIATE c.v_sql;
    exception
      when others then
        dbms_output.put_line(sqlerrm);
    end;
  end loop;
  for c in (select 'ALTER TABLE ' || TNAME || ' ENABLE ALL TRIGGERS ' AS v_sql
              from tab
             where tabtype = 'TABLE') loop
    dbms_output.put_line(c.v_sql);
    begin
      execute immediate c.v_sql;
    exception
      when others then
        dbms_output.put_line(sqlerrm);
    end;
  end loop;
end;
/

8.4 Modify the scan ip.

~]# su - grid
~]$ srvctl stop scan_listener
~]$ srvctl stop scan
~]$ exit

~]# vi /etc/hosts
~]# $GRID_HOME/bin/srvctl modify scan -n emrrac-scan

~]# su - grid
~]$ srvctl config scan
~]$ srvctl start scan
~]$ srvctl start scan_listener
~]$ srvctl modify scan_listener -u