Replicação consolidada e em cascata combinadas (DDL e DML) usando Oracle GoldenGate (11g e 12c) entre Oracle databases (11g e 12c)

Por Yenugula Venkata Ravikumar Oracle ACE, Rodrigo Mufalani Oracle ACE e Archana Durai
Postado em Dezembro 2016

Revisado por Marcelo Pivovar - Solution Architect

INTRODUÇÃO:

Replicação consolidada é um tipo de replicação onde dados de múltiplas origens são enviadas para um database alvo.

Replicação em Cascata é um tipo de replicação onde os dados de uma origem são enviados para múltiplos databases alvos.

Este artigo é para ilustrar a configuração de ambos tipos de replicação de dados entre diferentes versões do Oracle database e Oracle GoldenGate (Consolidado e em cascata – Uni-direcional) combinados juntos.

Fig: REPLICAÇÃO CONSOLIDADA E EM CASCATA USANDO ORACLE GOLDENGATE


Source Database (srcdb11g)

Target Database (tgtdb1)

1.Oracle Enterprise Linux 5.8 (x86-64)
2.Oracle 11g R2 Database (11.2.0.3)         ORACLE_HOME=/u01/app/oracle/product/11.2.0/db_1
3.Oracle GoldenGate 11G (11.2.1.0.0)
GOLDENGATE_HOME= /u01/app/ogg/11gg
4.Database Name: srcdb11g
5.Schema Name: scott
6.Hostname: ggnode1
7.IP Address: 192.168.56.120

1.Oracle Enterprise Linux 5.8 (x86-64)
2.Oracle 12c R1 Database (12.1.0.1.0)    ORACLE_HOME=/u01/app/oracle/ product/12.1.0/db_1
3.Oracle GoldenGate 12c (12.1.2.0.0)
GOLDENGATE_HOME=/u01/app/ogg/12gg
4.Database Name: tgtdb1
5.Schema Name: scott, hr
6.Hostname: ggnode2
7.IP Address: 192.168.56.125

 

Source Database (srcdb12c)

Target Database (tgtdb2)

1. Oracle Enterprise Linux 5.8 (x86-64)
2. Oracle 12c R1 Database (12.1.0.1.0)        ORACLE_HOME=/u01/app/oracle/product/12.1.0/db_1
3. Oracle GoldenGate 12c (12.1.2.0.0)
GOLDENGATE_HOME=/u01/app/ogg/12gg
4. Database Name: srcdb12c
5. Schema Name: hr
6. Hostname: ggnode1
7. IP Address: 192.168.56.120

1.Oracle Enterprise Linux 5.8 (x86-64)
2. Oracle 11g R2 Database (11.2.0.3)         ORACLE_HOME=/u01/app/oracle/product/11.2.0/db_1
3. Oracle GoldenGate 11G (11.2.1.0.0)
GOLDENGATE_HOME= /u01/app/ogg/11gg
4.Database Name: tgtdb2
5.Schema Name: hr, scott
6.Hostname: ggnode2
7.IP Address: 192.168.56.125

 

AMBIENTE INSTALADO
 
DATABASE DE ORIGEM

  • Instalação dos binários oracle 11g R2 (11.2.0.3.0) do database ‘srcdb11g’ no diretório:
$ORACLE_HOME=/u01/app/oracle/product/11.2.0/db_1

  • Instalação dos binários oracle 12c R1 (12.1.0.1.0) do database ‘srcdb12c’ no diretório:
$ORACLE_HOME=/u01/app/oracle/product/12.1.0/db_1

  • Instalação dos binários do Oracle Oracle GoldenGate para o Oracle 11g R2 e Oracle 12c R1 nos seguintes diretórios respectivamente no ggnode1 :
$GG_HOME=/u01/app/ogg/11gg
$GG_HOME=/u01/app/ogg/12gg

DATABASE DE DESTINO

  • Instalação dos binários oracle 11g R2 (11.2.0.3.0) do database ‘tgtdb11g’ no diretório:
$ORACLE_HOME=/u01/app/oracle/product/11.2.0/db_1

  • Instalação dos binários oracle 12c R1 (12.1.0.1.0) do database ‘tgtdb12c’ no diretório:
$ORACLE_HOME=/u01/app/oracle/product/12.1.0/db_1

  • Instalação dos binários do Oracle Oracle GoldenGate para o Oracle 11g R2 e Oracle 12c R1 nos seguintes diretórios respectivamente no ggnode2 :
$GG_HOME=/u01/app/ogg/11gg
$GG_HOME=/u01/app/ogg/12gg

 

PRE-REQUISITOS PARA A CONFIGURAÇÃO DO ORACLE GOLDENGATE:

DATABASE DE ORIGEM

  • Logar com o usuário ‘Oracle’ no database server de origem (ggnode1) e criar os arquivos de configuração das variáveis de ambiente (.env) para os databases de origem srcdb11g e srcdb12c.
  • Inicie os databases de origem srcdb11g e srcdb12c, se estiverem parados.
[oracle@ggnode1 ~]$ . ./srcdb11g.env 
[oracle@ggnode1 ~]$ env|grep ORA
ORACLE_UNQNAME=srcdb11g
ORACLE_SID=srcdb11g
ORACLE_BASE=/u01/app/oracle
ORACLE_HOSTNAME=ggnode1
ORACLE_TERM=xterm
ORACLE_HOME=/u01/app/oracle/product/11.2.0/db_1
[oracle@ggnode1 ~]$ sqlplus / as sysdba
SQL>startup
SQL>exit

[oracle@ggnode1 ~]$ . ./srcdb12c.env 
[oracle@ggnode1 ~]$ env|grep ORA
ORACLE_UNQNAME=srcdb12c
ORACLE_SID=srcdb12c
ORACLE_BASE=/u01/app/oracle
ORACLE_HOSTNAME=ggnode1
ORACLE_TERM=xterm
ORACLE_HOME=/u01/app/oracle/product/12.1.0/db_1
[oracle@ggnode1 ~]$ sqlplus / as sysdba
SQL>startup
SQL>exit

  • Crie uma tablespace (GGTBS) para o usuário do GoldenGate, o (OGGUSER)  e conceda os privilégios necessários para este usuário neste tablespace em ambos os databases de origem srcdb11g e srcdb12c.
    • Os databases de origem srcdb11g e srcdb12c devem estar configurados em modo archivelog com as opções  ‘force logging’ e ‘Supplemental Log Data’ ligadas.
[oracle@ggnode1 ~]$ sqlplus / as sysdba
SQL> select force_logging, supplemental_log_data_min from v$database;
SQL> ALTER DATABASE FORCE LOGGING;
SQL> ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;
SQL> ALTER SYSTEM SWITCH LOGFILE;
SQL> select force_logging, supplemental_log_data_min from v$database;
SQL> select tablespace_name from dba_tablespaces;
SQL> select file_name from dba_data_files;
SQL> CREATE TABLESPACE GGTBS DATAFILE SIZE 100M AUTOEXTEND ON; à USANDO OMF
SQL> CREATE USER ogguser IDENTIFIED BY oracle DEFAULT TABLESPACE GGTBS TEMPORARY TABLESPACE TEMP ACCOUNT UNLOCK;
SQL> GRANT CONNECT, DBA, UNLIMITED TABLESPACE, SELECT ANY DICTIONARY TO ogguser;
SQL> alter user scott identified by oracle account unlock; à SRCDB11G 
SQL> alter user scott identified by oracle account unlock; à SRCDB12C 
SQL> exit

  • Instale o Oracle GoldenGate software para o database de origem (Oracle 11G - SRCDB11G).
[oracle@ggnode1 11gg]$ tar -xvf fbo_ggs_Linux_x64_ora11g_64bit.tar 
[oracle@ggnode1 11gg]$ ./ggsci

Oracle GoldenGate Command Interpreter for Oracle
Version 11.2.1.0.0 OGGCORE_11.2.1.0.0_PLATFORMS_120131.1910_FBO
Linux, x64, 64bit (optimized), Oracle 11g on Jul 31 2016 00:55:59
Copyright (C) 1995, 2016, Oracle and/or its affiliates. All rights reserved.

GGSCI (ggnode1.oracle.com) 1> create subdirs

Creating subdirectories under current directory /u01/app/ogg/11gg

Parameter files                /u01/app/ogg/11gg/dirprm: already exists
Report files                   /u01/app/ogg/11gg/dirrpt: created
Checkpoint files               /u01/app/ogg/11gg/dirchk: created
Process status files           /u01/app/ogg/11gg/dirpcs: created
SQL script files               /u01/app/ogg/11gg/dirsql: created 
Database definitions files     /u01/app/ogg/11gg/dirdef: created
Extract data files             /u01/app/ogg/11gg/dirdat: created
Temporary files                /u01/app/ogg/11gg/dirtmp: created
Stdout files                   /u01/app/ogg/11gg/dirout: created 
GGSCI (ggnode1.oracle.com) 2> 

  • Cheque para ter certeza que os binários do Oracle Oracle GoldenGate estão “linkados” com as bibliotecas do Oracle (11g) executando os comandos abaixo no S.O dentro do diretório $GG_HOME:
[oracle@ggnode1 11gg]$ ldd mgr
[oracle@ggnode1 11gg]$ ldd extract
[oracle@ggnode1 11gg]$ ldd replicat
[oracle@ggnode1 11gg]$ ldd ggsci 

  • Rode os scripts abaixo para habilitar a replicação de DDL no banco de dados de origem (Oracle 11g - SRCDB11G). Entrar com o usuário do GoldenGate (OGGUSER) sempre que perguntado por:
[oracle@ggnode1 ~]$ sqlplus / as sysdba
SQL>select name from v$database;
SQL>@marker_setup.sql
SQL>@ddl_setup.sql
SQL>@role_setup.sql
SQL>GRANT GGS_GGSUSER_ROLE TO OGGUSER;
SQL>@ddl_enable.sql
SQL>@ddl_pin.sql ogguser
SQL>@marker_status.sql
SQL>@ddl_status.sql
SQL>exit 

  • Instale o Oracle GoldenGate software para o banco de dados de origem (Oracle 12C - SRCDB12C).
[oracle@ggnode1 ~Disk1]$ ./runInstaller 

Select the option “Oracle GoldenGate for Oracle Database 12c”

Select Software Location:

/u01/app/ogg/12gg e “Desmarque a opçãoStart Manager

[oracle@ggnode1 12gg]$ ./ggsci

Oracle GoldenGate Command Interpreter for Oracle
Version 12.1.2.0.0 17185003 OGGCORE_12.1.2.0.0_PLATFORMS_130924.1316_FBO
Linux, x64, 64bit (optimized), Oracle 12c on Jul 31 2016 02:33:54
Operating system character set identified as UTF-8.
Copyright (C) 1995, 2013, Oracle and/or its affiliates. All rights reserved.

GGSCI (ggnode1.oracle.com) 1> create subdirs

Creating subdirectories under current directory /u01/app/ogg/12gg

Parameter files                /u01/app/ogg/12gg/dirprm: already exists
Report files                   /u01/app/ogg/12gg/dirrpt: created
Checkpoint files               /u01/app/ogg/12gg/dirchk: created
Process status files           /u01/app/ogg/12gg/dirpcs: created
SQL script files               /u01/app/ogg/12gg/dirsql: created
Database definitions files     /u01/app/ogg/12gg/dirdef: created
Extract data files             /u01/app/ogg/12gg/dirdat: created
Temporary files                /u01/app/ogg/12gg/dirtmp: created
Credential store files         /u01/app/ogg/12gg/dircrd: created
Masterkey wallet files         /u01/app/ogg/12gg/dirwlt: created
Dump files                     /u01/app/ogg/12gg/dirdmp: created
GGSCI (ggnode1.oracle.com) 2> 

  • Cheque para ter certeza que os binários do Oracle GoldenGate 12c estão “linkados” com as bibliotecas do (Oracle 12c) executando os comandos abaixo no S.O de dentro do diretório $GG_HOME
[oracle@ggnode1 12gg]$ ldd mgr
[oracle@ggnode1 12gg]$ ldd extract
[oracle@ggnode1 12gg]$ ldd replicat
[oracle@ggnode1 12gg]$ ldd ggsci 

  • Rode os scripts abaixo para habilitar a replicação dos DDLs do database de origem (Oracle 12c - SRCDB12C). Entre com o usuário do GoldenGate (OGGUSER) sempre que perguntado:

Este passo é mandatório se estivermos usando extração CLASSIC (quando usamos extração INTEGRATED no 12c database, este passo pode ser ignorado)!

[oracle@ggnode1 ~]$ sqlplus / as sysdba
SQL>select name from v$database;
SQL>@marker_setup.sql
SQL>@ddl_setup.sql
SQL>@role_setup.sql
SQL>GRANT GGS_GGSUSER_ROLE TO OGGUSER;
SQL>@ddl_enable.sql
SQL>@ddl_pin.sql ogguser
SQL>@marker_status.sql
SQL>@ddl_status.sql
SQL>exit 

 DATABASE DESTINO

  • Logar com o usuário ‘Oracle’ no database server de destino (ggnode2) e crie os arquivos com as variáveis de ambiente (.env) para os databases (Oracle 12c - tgtdb1 & Oracle 11g - tgtdb2).
  • Inicie os databases de destino (Oracle 12c - tgtdb1 & Oracle 11g - tgtdb2), se estiverem parados.
[oracle@ggnode2 ~]$ . ./tgtdb1.env 
[oracle@ggnode2 ~]$ env|grep ORA
ORACLE_UNQNAME=tgtdb1
ORACLE_SID=tgtdb1
ORACLE_BASE=/u01/app/oracle
ORACLE_HOSTNAME=ggnode2
ORACLE_TERM=xterm
ORACLE_HOME=/u01/app/oracle/product/12.1.0/db_1
[oracle@ggnode2 ~]$  sqlplus / as sysdba
SQL>startup
SQL>exit

[oracle@ggnode2 ~]$ . ./tgtdb2.env
[oracle@ggnode2 ~]$ env|grep ORA
ORACLE_UNQNAME=tgtdb2
ORACLE_SID=tgtdb2
ORACLE_BASE=/u01/app/oracle
ORACLE_HOSTNAME=ggnode2
ORACLE_TERM=xterm
ORACLE_HOME=/u01/app/oracle/product/11.2.0/db_1
[oracle@ggnode2 ~]$ sqlplus / as sysdba
SQL>startup
SQL>exit

  • Crie uma tabespace (GGTBS) para o usuário do GoldenGate (OGGUSER) e conceda os privilégios necessários para este usuário, em ambos os databases de destino (Oracle 12c - tgtdb1 & Oracle 11g - tgtdb2) .
    • Os databases destino (Oracle 12c - tgtdb1 & Oracle 11g - tgtdb2) também devem estar configurados em modo archivelog com as opções ‘force logging’ e ‘Supplemental Log Data’ ligadas, em caso de habilitar a replicação bi-direcional em um passo futuro.
[oracle@ggnode2 ~]$ sqlplus / as sysdba
SQL>select force_logging, supplemental_log_data_min from v$database;
SQL>ALTER DATABASE FORCE LOGGING;
SQL>ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;
SQL>ALTER SYSTEM SWITCH LOGFILE;
SQL>ALTER SYSTEM SWITCH LOGFILE;
SQL>select force_logging, supplemental_log_data_min from v$database;
SQL>select tablespace_name from dba_tablespaces;
SQL>select file_name from dba_data_files;
SQL>CREATE TABLESPACE GGTBS DATAFILE SIZE 100M AUTOEXTEND ON; à USANDO OMF 
SQL>CREATE USER ogguser IDENTIFIED BY oracle DEFAULT TABLESPACE GGTBS TEMPORARY TABLESPACE TEMP ACCOUNT UNLOCK;
SQL>GRANT CONNECT, DBA, UNLIMITED TABLESPACE, SELECT ANY DICTIONARY TO ogguser;
SQL>alter user scott identified by oracle account unlock; à tgtdb1
SQL>alter user hr identified by oracle account unlock; à tgtdb1 
SQL>alter user scott identified by oracle account unlock; à tgtdb2 
SQL>alter user hr identified by oracle account unlock; à tgtdb2 
SQL>exit

  • Instale o Oracle GoldenGate software para o 11G no database destino tgtdb2.
[oracle@ggnode2 11gg]$ tar -xvf fbo_ggs_Linux_x64_ora11g_64bit.tar 
[oracle@ggnode2 11gg]$ ./ggsci

Oracle GoldenGate Command Interpreter for Oracle
Version 11.2.1.0.0 OGGCORE_11.2.1.0.0_PLATFORMS_120131.1910_FBO
Linux, x64, 64bit (optimized), Oracle 11g on Jul 31 2016 00:55:59
Copyright (C) 1995, 2016, Oracle and/or its affiliates. All rights reserved.

GGSCI (ggnode2.oracle.com) 1> create subdirs
Creating subdirectories under current directory /u01/app/ogg/11gg

Parameter files                /u01/app/ogg/11gg/dirprm: already exists
Report files                   /u01/app/ogg/11gg/dirrpt: created
Checkpoint files               /u01/app/ogg/11gg/dirchk: created
Process status files           /u01/app/ogg/11gg/dirpcs: created
SQL script files               /u01/app/ogg/11gg/dirsql: created 
Database definitions files     /u01/app/ogg/11gg/dirdef: created
Extract data files             /u01/app/ogg/11gg/dirdat: created
Temporary files                /u01/app/ogg/11gg/dirtmp: created
Stdout files                   /u01/app/ogg/11gg/dirout: created 

  • Cheque para ter certeza que os binários do Oracle Oracle GoldenGate estão “linkados” com as bibliotecas do Oracle (11g) executando os comandos abaixo no S.O dentro do diretório $GG_HOME
[oracle@ggnode2 11gg]$ ldd mgr
[oracle@ggnode2 11gg]$ ldd extract
[oracle@ggnode2 11gg]$ ldd replicat
[oracle@ggnode2 11gg]$ ldd ggsci 

  • Execute os scripts para habilitar a replicação DDL no database destino (Oracle 11g - tgtdb2). Entre com o usuário do Oracle Golden Gate (OGGUSER) sempre que perguntado.
[oracle@ggnode2 ~]$ sqlplus / as sysdba
SQL>select name from v$database;
SQL>@marker_setup.sql
SQL>@ddl_setup.sql
SQL>@role_setup.sql
SQL>GRANT GGS_GGSUSER_ROLE TO OGGUSER;
SQL>@ddl_enable.sql
SQL>@ddl_pin.sql ogguser
SQL>@marker_status.sql
SQL>@ddl_status.sql
SQL>exit 

  • Instale o Oracle GoldenGate software para o database destino (Oracle 12c - tgtdb1).
[oracle@ggnode2 ~Disk1]$ ./runInstaller 

Select the option “Oracle GoldenGate for Oracle Database 12c”

Selecione a localização de destino do software:
/u01/app/ogg/12gg e Desmarque a opção Start Manager

[oracle@ggnode2 12gg]$ ./ggsci

Oracle GoldenGate Command Interpreter for Oracle
Version 12.1.2.0.0 17185003 OGGCORE_12.1.2.0.0_PLATFORMS_130924.1316_FBO
Linux, x64, 64bit (optimized), Oracle 12c on Jul 31 2016 02:33:54
Operating system character set identified as UTF-8.
Copyright (C) 1995, 2016, Oracle and/or its affiliates. All rights reserved.

GGSCI (ggnode2.oracle.com) 1> create subdirs
Creating subdirectories under current directory /u01/app/ogg/12gg

Parameter files                /u01/app/ogg/12gg/dirprm: already exists
Report files                   /u01/app/ogg/12gg/dirrpt: created
Checkpoint files               /u01/app/ogg/12gg/dirchk: created
Process status files           /u01/app/ogg/12gg/dirpcs: created
SQL script files               /u01/app/ogg/12gg/dirsql: created
Database definitions files     /u01/app/ogg/12gg/dirdef: created
Extract data files             /u01/app/ogg/12gg/dirdat: created
Temporary files                /u01/app/ogg/12gg/dirtmp: created
Credential store files         /u01/app/ogg/12gg/dircrd: created
Masterkey wallet files         /u01/app/ogg/12gg/dirwlt: created
Dump files                     /u01/app/ogg/12gg/dirdmp: created

  • Cheque e tenha certeza que os binários do Oracle Oracle GoldenGate então “lincados” com as bibliotecas do Oracle (12c) excutando os comandos abaixo no S.O dentro do diretório $GG_HOME:
[oracle@ggnode2 12gg]$ ldd mgr
[oracle@ggnode2 12gg]$ ldd extract
[oracle@ggnode2 12gg]$ ldd replicat
[oracle@ggnode2 12gg]$ ldd ggsci 

  • Execute os scripts abaixo para habilitar a replicação DDL no database destino (Oracle 12c - tgtdb1). Entre com o usuário do GoldenGate (OGGUSER) sempre que perguntado.

Este passo é mandatório quando estamos usando extração CLASSIC e se for necessário habilitar replicação bi-direcional em um passo future (quando usamos extração INTEGRATED no 12c database, esse passo pode ser pulado).

[oracle@ggnode2 ~]$ sqlplus / as sysdba
SQL> select name from v$database;
SQL>@marker_setup.sql
SQL>@ddl_setup.sql
SQL>@role_setup.sql
SQL>GRANT GGS_GGSUSER_ROLE TO OGGUSER;
SQL>@ddl_enable.sql
SQL>@ddl_pin.sql ogguser
SQL>@marker_status.sql
SQL>@ddl_status.sql
SQL>exit 

CONFIGURACAO DO ORACLE GOLDENGATE:

DATABASE ORIGEM

  • Carregue as variáveis de ambiente do Oracle GoldenGate 11g no database server de origem (ggnode1).
[oracle@ggnode1 11gg]$ ./ggsci

Oracle GoldenGate Command Interpreter for Oracle
Version 11.2.1.0.0 OGGCORE_11.2.1.0.0_PLATFORMS_120131.1910_FBO
Linux, x64, 64bit (optimized), Oracle 11g on Jul 31 2016 00:55:59
Copyright (C) 1995, 2016, Oracle and/or its affiliates. All rights reserved.

  • Logue-se no 11g database de origem (SRCDB11G) do GGSCI prompt e configure o manager, os processos de extract (es1a) e pump (ps1a – Oracle 11g & ps1b – Oracle 12c).
GGSCI (ggnode1.oracle.com) 1> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt
MANAGER     STOPPED

GGSCI (ggnode1.oracle.com) 2> dblogin userid ogguser, password oracle
Successfully logged into database.

GGSCI (ggnode1.oracle.com) 3> edit param mgr
GGSCI (ggnode1.oracle.com) 4> view param mgr
port 7809

Os seguintes comandos adicionam o processo de extração (Extract process).
A opção TRANLOG indica que iremos capturar transações do redolog do Oracle e a opção BEGIN NOW quer dizer “bookmark” ou checkpoint deste timestamp particular para iniciar a capturar as transações.

 

 

 

GGSCI (ggnode1.oracle.com) 5> add extract es1a, tranlog, begin now
EXTRACT added.

GGSCI (ggnode1.oracle.com) 6> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt
MANAGER     STOPPED
EXTRACT     STOPPED     ES1A        00:00:00      00:00:03

O comando ADD EXTTRAIL adiciona um arquivo de trilha com o prefixo “ya”, que será escrito na local machine e associar isto a um Extract process es1a. O arquivo de trilha será criado com este prefixo e 6 dígitos iniciando com “000000”. Quando o arquivo atingir 5 megabytes um novo arquivo de trilha será criado e o dígito incrementado ex: (ya000001).

GGSCI (ggnode1.oracle.com) 7> add exttrail /u01/app/ogg/11gg/dirdat/ya, extract es1a, megabytes 5
EXTTRAIL added.

GGSCI (ggnode1.oracle.com) 8> info es1a, detail

EXTRACT    ES1A      Initialized   2016-07-18 06:56   Status STOPPED
Checkpoint Lag       00:00:00 (updated 00:00:56 ago)
Log Read Checkpoint  Oracle Redo Logs
2016-07-18 06:56:03  Seqno 0, RBA 0
SCN 0.0 (0)

Target Extract Trails:
Remote Trail Name                                Seqno        RBA     Max MB

/u01/app/ogg/11gg/dirdat/ya                          0          0          5

Extract Source                          Begin             End
Not Available                           * Initialized *   2016-07-18 06:56

Current directory    /u01/app/ogg/11gg

Report file          /u01/app/ogg/11gg/dirrpt/ES1A.rpt (does not yet exist)
Parameter file       /u01/app/ogg/11gg/dirprm/es1a.prm (does not yet exist)
Checkpoint file      /u01/app/ogg/11gg/dirchk/ES1A.cpe
Process file         /u01/app/ogg/11gg/dirpcs/ES1A.pce
Stdout file          /u01/app/ogg/11gg/dirout/ES1A.out
Error log            /u01/app/ogg/11gg/ggserr.log

GGSCI (ggnode1.oracle.com) 9>

Este commando adiciona um processo de extração adicional, chamado de Pump. O Pump lê de um arquivo de trilha e não de um log de transação de um database (redolog). O commando EXTTRAILSOURCE aponta para o arquivo de trilha de onde ele lerá. Note que isso é a trilha de saída do processo es1a adicionado acima.

GGSCI (ggnode1.oracle.com) 9> add extract ps1a, exttrailsource /u01/app/ogg/11gg/dirdat/ya
EXTRACT added.

O comando ADD RMTTRAIL adiciona um arquivo de trilha com o prefixo “ra” que irá escrever na remote machine e atachar isso ao processo Extract, ps1a.

GGSCI (ggnode1.oracle.com) 10> add rmttrail /u01/app/ogg/12gg/dirdat/ra, extract ps1a, megabytes 5
RMTTRAIL added.
GGSCI (ggnode1.oracle.com) 11> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     STOPPED
EXTRACT     STOPPED     ES1A        00:00:00      00:09:15
EXTRACT     STOPPED     PS1A        00:00:00      00:00:58

GGSCI (ggnode1.oracle.com) 12> info ps1a, detail

EXTRACT    PS1A      Initialized   2016-07-18 07:04   Status STOPPED
Checkpoint Lag       00:00:00 (updated 00:01:02 ago)
Log Read Checkpoint  File /u01/app/ogg/11gg/dirdat/ya000000
First Record  RBA 0

Target Extract Trails:
Remote Trail Name                                Seqno        RBA     Max MB
/u01/app/ogg/12gg/dirdat/ra                          0          0          5

Extract Source                          Begin             End
/u01/app/ogg/11gg/dirdat/ya000000       * Initialized *   First Record

Current directory    /u01/app/ogg/11gg
Report file          /u01/app/ogg/11gg/dirrpt/PS1A.rpt (does not yet exist)
Parameter file       /u01/app/ogg/11gg/dirprm/ps1a.prm (does not yet exist)
Checkpoint file      /u01/app/ogg/11gg/dirchk/PS1A.cpe
Process file         /u01/app/ogg/11gg/dirpcs/PS1A.pce
Stdout file          /u01/app/ogg/11gg/dirout/PS1A.out
Error log            /u01/app/ogg/11gg/ggserr.log

GGSCI (ggnode1.oracle.com) 13> edit param es1a
GGSCI (ggnode1.oracle.com) 14> view param es1a

extract es1a
exttrail /u01/app/ogg/11gg/dirdat/ya
userid ogguser,password oracle
ddl include all
ddloptions  report
statoptions resetreportstats
tranlogoptions excludeuser ogguser
table scott.*;

GGSCI (ggnode1.oracle.com) 15> edit param ps1a
GGSCI (ggnode1.oracle.com) 16> view param ps1a

extract ps1a
rmthost ggnode2, mgrport 15000
rmttrail /u01/app/ogg/12gg/dirdat/ra
passthru
table scott.*;

O comando ADD SCHEMATRANDATA adiciona supplemental logging para todas as tabelas de um schema. Supplemental logging garante que todas as informações relevantes sobre operações de update e delete sejam gravadas nos redologs do Oracle, então o Oracle GoldenGate pode replicar  as transações para aquelas tabelas corretamente.

GGSCI (ggnode1.oracle.com) 37> add schematrandata scott

2016-07-18 07:35:13  INFO    OGG-01788  SCHEMATRANDATA has been added on schema scott.

  • Carregue as variáveis de ambiente do Oracle GoldenGate 12c no database server de origem (ggnode1).
[oracle@ggnode1 ~]$ . ./srcdb12c.env
[oracle@ggnode1 12gg]$ ./ggsci

Oracle GoldenGate Command Interpreter for Oracle
Version 12.1.2.0.0 17185003 OGGCORE_12.1.2.0.0_PLATFORMS_130924.1316_FBO
Linux, x64, 64bit (optimized), Oracle 12c on Jul 31 2016 02:33:54
Operating system character set identified as UTF-8.
Copyright (C) 1995, 2016, Oracle and/or its affiliates. All rights reserved.

GGSCI (ggnode1.oracle.com) 1>

  • Logue-se no database de origem 12c (SRCDB12C) pelo prompt do GGSCI e configure o manager e o processo de extração (es1b).
GGSCI (ggnode1.oracle.com) 1> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt
MANAGER     STOPPED

GGSCI (ggnode1.oracle.com) 2> dblogin userid ogguser, password oracle
Successfully logged into database.

GGSCI (ggnode1.oracle.com) 3> edit param mgr
GGSCI (ggnode1.oracle.com) 4> view param mgr
port 7810

GGSCI (ggnode1.oracle.com) 5> add extract es1b, tranlog, begin now
EXTRACT added.

GGSCI (ggnode1.oracle.com) 6> add exttrail /u01/app/ogg/12gg/dirdat/yb, extract es1b, megabytes 5
EXTTRAIL added.

GGSCI (ggnode1.oracle.com) 7> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     STOPPED
EXTRACT     STOPPED     ES1B        00:00:00      00:00:23

GGSCI (ggnode1.oracle.com) 8> info es1b, detail

EXTRACT    ES1B      Initialized   2016-07-18 07:21   Status STOPPED
Checkpoint Lag       00:00:00 (updated 00:00:29 ago)
Log Read Checkpoint  Oracle Redo Logs
2016-07-18 07:21:13  Seqno 0, RBA 0
SCN 0.0 (0)

Target Extract Trails:

Trail Name                                       Seqno        RBA     Max MB Trail Type
/u01/app/ogg/12gg/dirdat/yb                          0          0          5 EXTTRAIL

Extract Source                          Begin             End
Not Available                           * Initialized *   2016-07-18 07:21

Current directory    /u01/app/ogg/12gg
Report file          /u01/app/ogg/12gg/dirrpt/ES1B.rpt (does not yet exist)
Parameter file       /u01/app/ogg/12gg/dirprm/es1b.prm (does not yet exist)
Checkpoint file      /u01/app/ogg/12gg/dirchk/ES1B.cpe
Process file         /u01/app/ogg/12gg/dirpcs/ES1B.pce
Error log            /u01/app/ogg/12gg/ggserr.log

GGSCI (ggnode1.oracle.com) 9> edit param es1b
GGSCI (ggnode1.oracle.com) 10> view param es1b
extract es1b
exttrail /u01/app/ogg/12gg/dirdat/yb, format release 11.2
userid ogguser,password oracle
ddl include all
ddloptions  report
statoptions resetreportstats
tranlogoptions excludeuser ogguser
table hr.*;

GGSCI (ggnode1.oracle.com) 15> add schematrandata hr

2016-07-18 07:35:06  INFO    OGG-01788  SCHEMATRANDATA has been added on schema hr.
2016-07-18 07:35:06  INFO    OGG-01976  SCHEMATRANDATA for scheduling columns has been added on schema hr.

  • Agora configure outro processo Pump no database de origem (Oracle 11g - SRCDB11G) para ler os arquivos de trilha locais do database de origem (Oracle 12c – SRCDB12C).
GGSCI (ggnode1.oracle.com) 18> add extract ps1b, exttrailsource /u01/app/ogg/12gg/dirdat/yb
EXTRACT added.

GGSCI (ggnode1.oracle.com) 19> add rmttrail /u01/app/ogg/11gg/dirdat/rb, extract ps1b, megabytes 5
RMTTRAIL added.

GGSCI (ggnode1.oracle.com) 20> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     STOPPED
EXTRACT     STOPPED     ES1A        00:00:00      00:33:33
EXTRACT     STOPPED     PS1A        00:00:00      00:25:16
EXTRACT     STOPPED     PS1B        00:00:00      00:00:37

GGSCI (ggnode1.oracle.com) 21> info ps1b, detail

EXTRACT    PS1B      Initialized   2016-07-18 07:28   Status STOPPED
Checkpoint Lag       00:00:00 (updated 00:00:43 ago)
Log Read Checkpoint  File /u01/app/ogg/12gg/dirdat/yb000000
First Record  RBA 0

Target Extract Trails:
Remote Trail Name                                Seqno        RBA     Max MB
/u01/app/ogg/11gg/dirdat/rb                          0          0          5


Extract Source                          Begin             End
/u01/app/ogg/12gg/dirdat/yb000000       * Initialized *   First Record

 

Current directory    /u01/app/ogg/11gg
Report file          /u01/app/ogg/11gg/dirrpt/PS1B.rpt (does not yet exist)
Parameter file       /u01/app/ogg/11gg/dirprm/ps1b.prm (does not yet exist)
Checkpoint file      /u01/app/ogg/11gg/dirchk/PS1B.cpe
Process file         /u01/app/ogg/11gg/dirpcs/PS1B.pce
Stdout file          /u01/app/ogg/11gg/dirout/PS1B.out
Error log            /u01/app/ogg/11gg/ggserr.log

GGSCI (ggnode1.oracle.com) 22> edit param ps1b
GGSCI (ggnode1.oracle.com) 24> view param ps1b
extract ps1b
rmthost ggnode2, mgrport 15200
rmttrail /u01/app/ogg/11gg/dirdat/rb, format release 11.2
passthru
table hr.*;

DATABASE DESTINO

  • Carregue as variáveis de ambiente do Oracle GoldenGate 12c no database server destino (ggnode2).
[oracle@ggnode2 12gg]$ ./ggsci

Oracle GoldenGate Command Interpreter for Oracle
Version 12.1.2.0.0 17185003 OGGCORE_12.1.2.0.0_PLATFORMS_130924.1316_FBO
Linux, x64, 64bit (optimized), Oracle 12c on Jul 31 2016 02:33:54
Operating system character set identified as UTF-8.

Copyright (C) 1995, 2013, Oracle and/or its affiliates. All rights reserved.

  • Logue-se no database destino (Oracle 12c- tgtdb1) do prompt do GGSCI e configure o manager e o processo de replicação (rs1a – Oracle 11g).
GGSCI (ggnode2.oracle.com) 1> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt
MANAGER     STOPPED

GGSCI (ggnode2.oracle.com) 2> dblogin userid ogguser, password oracle
Successfully logged into database.

GGSCI (ggnode2.oracle.com) 3> edit param ./GLOBALS
GGSCI (ggnode2.oracle.com) 4> view param ./GLOBALS
ggschema ogguser
enablemonitoring
checkpointtable ggs_checkpoint

GGSCI (ggnode2.oracle.com) 5> exit

[oracle@ggnode2 12gg]$ ./ggsci

Oracle GoldenGate Command Interpreter for Oracle
Version 12.1.2.0.0 17185003 OGGCORE_12.1.2.0.0_PLATFORMS_130924.1316_FBO
Linux, x64, 64bit (optimized), Oracle 12c on Jul 31 2016 02:33:54
Operating system character set identified as UTF-8.
Copyright (C) 1995, 2013, Oracle and/or its affiliates. All rights reserved.

GGSCI (ggnode2.oracle.com) 1> edit param mgr
GGSCI (ggnode2.oracle.com) 2> view param mgr
port 15000

GGSCI (ggnode2.oracle.com) 3> dblogin userid ogguser, password oracle
Successfully logged into database.

GGSCI (ggnode2.oracle.com) 4> add checkpointtable

No checkpoint table specified. Using GLOBALS specification (ggs_checkpoint)...
Successfully created checkpoint table ggs_checkpoint.

GGSCI (ggnode2.oracle.com) 5> add replicat rs1a, exttrail /u01/app/ogg/12gg/dirdat/ra
REPLICAT added.

GGSCI (ggnode2.oracle.com) 6> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt
MANAGER     STOPPED
REPLICAT    STOPPED     RS1A        00:00:00      00:00:02

GGSCI (ggnode2.oracle.com) 7> info rs1a, detail

REPLICAT   RS1A      Initialized   2016-07-18 07:45   Status STOPPED
Checkpoint Lag       00:00:00 (updated 00:00:07 ago)
Log Read Checkpoint  File /u01/app/ogg/12gg/dirdat/ra000000
First Record  RBA 0

Current Log BSN value: <NULL>

Extract Source                          Begin             End
/u01/app/ogg/12gg/dirdat/ra000000       * Initialized *   First Record

Current directory    /u01/app/ogg/12gg
Report file          /u01/app/ogg/12gg/dirrpt/RS1A.rpt (does not yet exist)
Parameter file       /u01/app/ogg/12gg/dirprm/rs1a.prm (does not yet exist)
Checkpoint file      /u01/app/ogg/12gg/dirchk/RS1A.cpr
Checkpoint table     ggs_checkpoint
Process file         /u01/app/ogg/12gg/dirpcs/RS1A.pcr
Error log            /u01/app/ogg/12gg/ggserr.log

GGSCI (ggnode2.oracle.com) 8> edit param rs1a
GGSCI (ggnode2.oracle.com) 9> view param rs1a
replicat rs1a
assumetargetdefs
discardfile /u01/app/ogg/12gg/dirout/rs1a.dsc, purge
userid ogguser, password oracle
ddloptions report
map scott.*, target scott.*;

GGSCI (ggnode2.oracle.com) 13> add replicat rs1c, exttrail /u01/app/ogg/11gg/dirdat/rb
REPLICAT added.

GGSCI (ggnode2.oracle.com) 14> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt
MANAGER     RUNNING
REPLICAT    STOPPED     RS1A        00:00:00      00:39:04
REPLICAT    STOPPED     RS1C        00:00:00      00:00:03

GGSCI (ggnode2.oracle.com) 15> info rs1c, detail

REPLICAT   RS1C      Initialized   2016-07-18 08:24   Status STOPPED
Checkpoint Lag       00:00:00 (updated 00:00:11 ago)
Log Read Checkpoint  File /u01/app/ogg/11gg/dirdat/rb000000
First Record  RBA 0

Current Log BSN value: <NULL>

Extract Source                          Begin             End
/u01/app/ogg/11gg/dirdat/rb000000       * Initialized *   First Record

Current directory    /u01/app/ogg/12gg
Report file          /u01/app/ogg/12gg/dirrpt/RS1C.rpt (does not yet exist)
Parameter file       /u01/app/ogg/12gg/dirprm/rs1c.prm (does not yet exist)
Checkpoint file      /u01/app/ogg/12gg/dirchk/RS1C.cpr
Checkpoint table     ggs_checkpoint
Process file         /u01/app/ogg/12gg/dirpcs/RS1C.pcr
Error log            /u01/app/ogg/12gg/ggserr.log

GGSCI (ggnode2.oracle.com) 16> edit param rs1c
GGSCI (ggnode2.oracle.com) 17> view param rs1c

replicat rs1c
assumetargetdefs
discardfile /u01/app/ogg/12gg/dirout/rs1c.dsc, purge
userid ogguser, password oracle
ddloptions report
map hr.*, target hr.*;

GGSCI (ggnode2.oracle.com) 18>

  • Carregue as variáveis de ambiente do Oracle GoldenGate 11g no database server destino (ggnode2).
[oracle@ggnode2 11gg]$ ./ggsci

Oracle GoldenGate Command Interpreter for Oracle
Version 11.2.1.0.0 OGGCORE_11.2.1.0.0_PLATFORMS_120131.1910_FBO
Linux, x64, 64bit (optimized), Oracle 11g on Jul 31 201600:55:59
Copyright (C) 1995, 2012, Oracle and/or its affiliates. All rights reserved.

  • Logue-se no database destino 11g  (tgtdb2) do prompt do GGSCI e configure o manager e o processo de replicação (rs1b – 12c source & rs1d – 11g source) .
GGSCI (ggnode2.oracle.com) 1> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt
MANAGER     STOPPED

GGSCI (ggnode2.oracle.com) 2> edit param mgr
GGSCI (ggnode2.oracle.com) 3> view param mgr
port 15200

GGSCI (ggnode2.oracle.com) 4> edit param ./GLOBALS
GGSCI (ggnode2.oracle.com) 5> view param ./GLOBALS
ggschema ogguser
enablemonitoring
checkpointtable ggs_chkpnt

GGSCI (ggnode2.oracle.com) 6> exit

[oracle@ggnode2 11gg]$ ./ggsci

Oracle GoldenGate Command Interpreter for Oracle
Version 11.2.1.0.0 OGGCORE_11.2.1.0.0_PLATFORMS_120131.1910_FBO
Linux, x64, 64bit (optimized), Oracle 11g on Jul 31 201600:55:59
Copyright (C) 1995, 2012, Oracle and/or its affiliates. All rights reserved.

GGSCI (ggnode2.oracle.com) 1> dblogin userid ogguser,password oracle
Successfully logged into database.

GGSCI (ggnode2.oracle.com) 3> add checkpointtable

No checkpoint table specified, using GLOBALS specification (ggs_chkpnt)...
Successfully created checkpoint table ggs_chkpnt.

GGSCI (ggnode2.oracle.com) 4> add replicat rs1b, exttrail /u01/app/ogg/11gg/dirdat/rb
REPLICAT added.

GGSCI (ggnode2.oracle.com) 5> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     STOPPED
REPLICAT    STOPPED     RS1B        00:00:00      00:00:05

GGSCI (ggnode2.oracle.com) 6> info rs1b, detail

REPLICAT   RS1B      Initialized   2016-07-18 07:52   Status STOPPED
Checkpoint Lag       00:00:00 (updated 00:00:09 ago)
Log Read Checkpoint  File /u01/app/ogg/11gg/dirdat/rb000000
First Record  RBA 0

Extract Source                          Begin             End
/u01/app/ogg/11gg/dirdat/rb000000       * Initialized *   First Record

Current directory    /u01/app/ogg/11gg

Report file          /u01/app/ogg/11gg/dirrpt/RS1B.rpt (does not yet exist)
Parameter file       /u01/app/ogg/11gg/dirprm/rs1b.prm (does not yet exist)
Checkpoint file      /u01/app/ogg/11gg/dirchk/RS1B.cpr
Checkpoint table     ggs_chkpnt
Process file         /u01/app/ogg/11gg/dirpcs/RS1B.pcr
Stdout file          /u01/app/ogg/11gg/dirout/RS1B.out
Error log            /u01/app/ogg/11gg/ggserr.log

GGSCI (ggnode2.oracle.com) 7> edit param rs1b
GGSCI (ggnode2.oracle.com) 8> view param rs1b
replicat rs1b
assumetargetdefs
discardfile /u01/app/ogg/11gg/dirout/rs1b.dsc, purge
userid ogguser, password oracle
ddloptions report
map hr.*, target hr.*;

GGSCI (ggnode2.oracle.com) 12> add replicat rs1d, exttrail /u01/app/ogg/12gg/dirdat/ra
REPLICAT added.

GGSCI (ggnode2.oracle.com) 13> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt
MANAGER     RUNNING
REPLICAT    STOPPED     RS1B        00:00:00      00:24:52
REPLICAT    STOPPED     RS1D        00:00:00      00:00:19

GGSCI (ggnode2.oracle.com) 14> info rs1d, detail

REPLICAT   RS1D      Initialized   2016-07-18 08:17   Status STOPPED
Checkpoint Lag       00:00:00 (updated 00:00:24 ago)
Log Read Checkpoint  File /u01/app/ogg/12gg/dirdat/ra000000
First Record  RBA 0

Extract Source                          Begin             End
/u01/app/ogg/12gg/dirdat/ra000000       * Initialized *   First Record

Current directory    /u01/app/ogg/11gg

Report file          /u01/app/ogg/11gg/dirrpt/RS1D.rpt (does not yet exist)
Parameter file       /u01/app/ogg/11gg/dirprm/rs1d.prm (does not yet exist)
Checkpoint file      /u01/app/ogg/11gg/dirchk/RS1D.cpr
Checkpoint table     ggs_chkpnt
Process file         /u01/app/ogg/11gg/dirpcs/RS1D.pcr
Stdout file          /u01/app/ogg/11gg/dirout/RS1D.out
Error log            /u01/app/ogg/11gg/ggserr.log

GGSCI (ggnode2.oracle.com) 15> edit param rs1d
GGSCI (ggnode2.oracle.com) 16> view param rs1d
replicat rs1d
assumetargetdefs
discardfile /u01/app/ogg/11gg/dirout/rs1d.dsc, purge
userid ogguser, password oracle
ddloptions report
map scott.*, target scott.*;

Neste cenário, nós temos a replicação do Oracle GoldenGate configurada de uma versão mais atual do (Oracle 12c – Database origem) para uma versão mais antiga  (Oracle 11g – Database destino), cujo (Oracle Database 12c) exttrail (arquivos de trilha local) são lidos pela versão mais antiga do processo Pump (Oracle Database 11g  - origem).

Portanto, existe uma uma diferença no formato versão/nível entre a extração na origem (Oracle 12c) e o a origem do Pump (Oracle 11g).

Antes mesmo de iniciarmos estes processos, temos que ter certeza que os arquivos de trilha da origem Oracle 12c são legíveis pelo processo Pump Oracle 11g na origem.

Realize os passos abaixo para realizar esta etapa:

Os processos precisam ser iniciados na seguinte ordem:

  • Manager (mgr) on all sources (Oracle 11g & Oracle 12c) & target (Oracle 11g & Oracle 12c)
  • Source Database (Oracle 11g) - Extract es1a
  • Source Database (Oracle 12c) - Extract es1b - perform ETROLLOVER and then start the extract
  • Source Database (Oracle 11g) - Pump Process ps1a & Pump Process ps1b (perform ETROLLOVER, alter EXTSEQNO & EXTRBA to read the local trail files of Source Oracle Database 12C)
  • Target Database (Oracle 12c) – Replicat rs1a & rs1c
  • Target Database (Oracle 11g) – Replicat rs1b & rs1d

DATABASE DE ORIGEM (Oracle 11g):

GGSCI (ggnode1.oracle.com) 29> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt
MANAGER     STOPPED
EXTRACT     STOPPED     ES1A        00:00:00      00:21:36
EXTRACT     STOPPED     PS1A        00:00:00      00:19:18
EXTRACT     STOPPED     PS1B        00:00:00      00:12:30

GGSCI (ggnode1.oracle.com) 30> start mgr
Manager started.

GGSCI (ggnode1.oracle.com) 31> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt
MANAGER     RUNNING
EXTRACT     STOPPED     ES1A        00:00:00      00:21:42
EXTRACT     STOPPED     PS1A        00:00:00      00:19:24
EXTRACT     STOPPED     PS1B        00:00:00      00:12:36

GGSCI (ggnode1.oracle.com) 32> start es1a

Sending START request to MANAGER ...
EXTRACT ES1A starting

GGSCI (ggnode1.oracle.com) 33> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt
MANAGER     RUNNING
EXTRACT     RUNNING     ES1A        00:25:27      00:00:01
EXTRACT     STOPPED     PS1A        00:00:00      00:23:10
EXTRACT     STOPPED     PS1B        00:00:00      00:16:23

GGSCI (ggnode1.oracle.com) 43> start ps1a

Sending START request to MANAGER ...
EXTRACT PS1A starting

GGSCI (ggnode1.oracle.com) 44> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt
MANAGER     RUNNING
EXTRACT     RUNNING     ES1A        00:00:00      00:00:04
EXTRACT     RUNNING     PS1A        00:00:00      00:36:01
EXTRACT     STOPPED     PS1B        00:00:00      00:29:13

GGSCI (ggnode1.oracle.com) 61> alter extract ps1b, etrollover

2016-07-31 05:16:55  INFO   OGG-01520  Rollover performed.  
For each affected output trail of Version 10 or higher format, after starting the source extract, 
issue ALTER EXTSEQNO for that trail's reader (either pump EXTRACT or REPLICAT) 
to move the reader's scan to the new trail file; it will not happen automatically.
EXTRACT altered.

GGSCI (ggnode1.oracle.com) 62> alter extract ps1b, exttrailsource /u01/app/ogg/12gg/dirdat/yb, extseqno 1, extrba 1078
EXTRACT altered.

  • O extseqno e extrba são obtidos do processo de extração 12c ES1B (origem).
GGSCI (ggnode1.oracle.com) 63> start ps1b

Sending START request to MANAGER ...
EXTRACT PS1B starting

GGSCI (ggnode1.oracle.com) 64> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt
MANAGER     RUNNING
EXTRACT     RUNNING     ES1A        00:00:00      00:00:07
EXTRACT     RUNNING     PS1A        00:00:00      00:00:00
EXTRACT     RUNNING     PS1B        00:00:00      00:00:10

GGSCI (ggnode1.oracle.com) 65> info ps1b, detail

EXTRACT    PS1B      Last Started 2016-07-31 05:17   Status RUNNING
Checkpoint Lag       00:00:00 (updated 00:00:01 ago)
Log Read Checkpoint  File /u01/app/ogg/12gg/dirdat/yb000001
First Record  RBA 1078

Target Extract Trails:
Remote Trail Name                                Seqno        RBA     Max MB
/u01/app/ogg/11gg/dirdat/rb                          1          0          5

Extract Source                          Begin             End
/u01/app/ogg/12gg/dirdat/yb000001       * Initialized *   First Record
/u01/app/ogg/12gg/dirdat/yb000001       * Initialized *   First Record
/u01/app/ogg/12gg/dirdat/yb000000       * Initialized *   First Record
/u01/app/ogg/12gg/dirdat/yb000000       * Initialized *   First Record

Current directory    /u01/app/ogg/11gg
Report file          /u01/app/ogg/11gg/dirrpt/PS1B.rpt
Parameter file       /u01/app/ogg/11gg/dirprm/ps1b.prm
Checkpoint file      /u01/app/ogg/11gg/dirchk/PS1B.cpe
Process file         /u01/app/ogg/11gg/dirpcs/PS1B.pce
Stdout file          /u01/app/ogg/11gg/dirout/PS1B.out
Error log            /u01/app/ogg/11gg/ggserr.log

DATABASE ORIGEM (Oracle 12c):

GGSCI (ggnode1.oracle.com) 12> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt
MANAGER     STOPPED
EXTRACT     STOPPED     ES1B        00:00:00      00:03:37

GGSCI (ggnode1.oracle.com) 13> start mgr
Manager started.

GGSCI (ggnode1.oracle.com) 14> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt
MANAGER     RUNNING
EXTRACT     STOPPED     ES1B        00:00:00      00:15:08

GGSCI (ggnode1.oracle.com) 15> alter extract es1b, etrollover

2016-07-31 05:08:18  INFO OGG-01520  Rollover performed.  
For each affected output trail of Version 10 or higher format, after starting the source extract, 
issue ALTER EXTSEQNO for that trail's reader (either pump EXTRACT or REPLICAT) 
to move the reader's scan to the new trail file; it will not happen automatically.
EXTRACT altered.

GGSCI (ggnode1.oracle.com) 16> start es1b

Sending START request to MANAGER ...
EXTRACT ES1B starting

GGSCI (ggnode1.oracle.com) 17> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt
MANAGER     RUNNING
EXTRACT     RUNNING     ES1B        00:00:00      00:00:09

GGSCI (ggnode1.oracle.com) 21> info es1b, detail

EXTRACT    ES1B      Last Started 2016-07-31 05:08   Status RUNNING
Checkpoint Lag       00:00:00 (updated 00:00:01 ago)
Process ID           29682
Log Read Checkpoint  Oracle Redo Logs
2016-07-31 05:10:00  Seqno 12, RBA 9804800
SCN 0.1898371 (1898371)

Target Extract Trails:
Trail Name                           Seqno        RBA     Max MB Trail Type
/u01/app/ogg/12gg/dirdat/yb          1            1078    5      EXTTRAIL

Extract Source                          Begin             End
/u01/app/oracle/oradata/SRCDB12C/onlinelog/o1_mf_3_cr2z2nxh_.log  2016-07-31 04:43  2016-07-31 05:10
Not Available                           * Initialized *   2016-07-31 04:43
Not Available                           * Initialized *   2016-07-31 04:43

Current directory    /u01/app/ogg/12gg
Report file          /u01/app/ogg/12gg/dirrpt/ES1B.rpt
Parameter file       /u01/app/ogg/12gg/dirprm/es1b.prm
Checkpoint file      /u01/app/ogg/12gg/dirchk/ES1B.cpe
Process file         /u01/app/ogg/12gg/dirpcs/ES1B.pce
Error log            /u01/app/ogg/12gg/ggserr.log

DATABASE DESTINO (Oracle 12c):

GGSCI (ggnode2.oracle.com) 19> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt
MANAGER     STOPPED
REPLICAT    STOPPED     RS1A        00:00:00      00:04:07
REPLICAT    STOPPED     RS1C        00:00:00      00:00:55

GGSCI (ggnode2.oracle.com) 20> start mgr
Manager started.

GGSCI (ggnode2.oracle.com) 21> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt
MANAGER     RUNNING
REPLICAT    STOPPED     RS1A        00:00:00      00:09:08
REPLICAT    STOPPED     RS1C        00:00:00      00:05:56

GGSCI (ggnode2.oracle.com) 22> start rs1a

Sending START request to MANAGER ...
REPLICAT RS1A starting

GGSCI (ggnode2.oracle.com) 23> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt
MANAGER     RUNNING
REPLICAT    RUNNING     RS1A        00:00:00      00:00:01
REPLICAT    STOPPED     RS1C        00:00:00      00:29:22

GGSCI (ggnode2.oracle.com) 26> alter replicat rs1c, exttrail /u01/app/ogg/11gg/dirdat/rb, extseqno 1, extrba 0

REPLICAT altered.

  • O extseqno e extrba são obtidos do processo Pump do 11G origem (PS1B) que lê os arquivos de trilha locais do processo de extract do 12c ES1B (origem).
GGSCI (ggnode2.oracle.com) 27> start rs1c

Sending START request to MANAGER ...
REPLICAT RS1C starting

GGSCI (ggnode2.oracle.com) 28> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt
MANAGER     RUNNING
REPLICAT    RUNNING     RS1A        00:00:00      00:00:07
REPLICAT    RUNNING     RS1C        00:00:00      00:00:05

DATABASE DESTINO (Oracle 11g):

GGSCI (ggnode2.oracle.com) 17> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt
MANAGER     STOPPED
REPLICAT    STOPPED     RS1B        00:00:00      00:01:46
REPLICAT    STOPPED     RS1D        00:00:00      00:00:54

GGSCI (ggnode2.oracle.com) 18> start mgr
Manager started.

GGSCI (ggnode2.oracle.com) 19> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt
MANAGER     RUNNING
REPLICAT    STOPPED     RS1B        00:00:00      00:03:22
REPLICAT    STOPPED     RS1D        00:00:00      00:02:30

GGSCI (ggnode2.oracle.com) 20> start rs1d

Sending START request to MANAGER ...
REPLICAT RS1D starting

GGSCI (ggnode2.oracle.com) 21> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt
MANAGER     RUNNING
REPLICAT    STOPPED     RS1B        00:00:00      00:27:51
REPLICAT    RUNNING     RS1D        00:00:00      00:00:00

GGSCI (ggnode2.oracle.com) 26> alter replicat rs1b, exttrail /u01/app/ogg/11gg/dirdat/rb, extseqno 1, extrba 0

REPLICAT altered.

  • O extseqno e extrba são obtidas do processo de origem Pump 11G (PS1B) que lê os arquivos de trilha local do processo extração 12c ES1B (origem).
GGSCI (ggnode2.oracle.com) 27> start rs1b

Sending START request to MANAGER ...
REPLICAT RS1B starting

GGSCI (ggnode2.oracle.com) 28> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt
MANAGER     RUNNING
REPLICAT    RUNNING     RS1B        00:00:00      00:00:01
REPLICAT    RUNNING     RS1D        00:00:00      00:00:02

GGSCI (ggnode2.oracle.com) 29> info rs1b, detail

REPLICAT   RS1B      Last Started 2016-07-31 05:26   Status RUNNING
Checkpoint Lag       00:00:00 (updated 00:00:05 ago)
Log Read Checkpoint  File /u01/app/ogg/11gg/dirdat/rb000001
First Record  RBA 0

Extract Source                          Begin             End
/u01/app/ogg/11gg/dirdat/rb000001       * Initialized *   First Record
/u01/app/ogg/11gg/dirdat/rb000001       * Initialized *   First Record
/u01/app/ogg/11gg/dirdat/rb000000       * Initialized *   First Record

Current directory    /u01/app/ogg/11gg
Report file          /u01/app/ogg/11gg/dirrpt/RS1B.rpt
Parameter file       /u01/app/ogg/11gg/dirprm/rs1b.prm
Checkpoint file      /u01/app/ogg/11gg/dirchk/RS1B.cpr
Checkpoint table     ggs_chkpnt
Process file         /u01/app/ogg/11gg/dirpcs/RS1B.pcr
Stdout file          /u01/app/ogg/11gg/dirout/RS1B.out
Error log            /u01/app/ogg/11gg/ggserr.log

 

TESTANDO A REPLICAÇÃO CONSOLIDADA E EM CASCATA DO ORACLE GOLDENGATE USANDO DDL & DML ENTRE O ORACLE 11G E ORACLE 12C ORIGEM/DESTINO:

Logue-se no Database de Origem (Oracle 11g - SRCDB11G):

[oracle@ggnode1 ~]$ sqlplus /nolog

SQL*Plus: Release 11.2.0.3.0 Production on Sun Jul 31 04:29:22 2016
Copyright (c) 1982, 2011, Oracle.  All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> conn scott/oracle
Connected.

SQL> select * from dept;

DEPTNO DNAME          LOC
------ -------------- -------------
10     ACCOUNTING     NEW YORK
20     RESEARCH       DALLAS
30     SALES          CHICAGO
40     OPERATIONS     BOSTON

SQL> insert into dept values ('50','ORACLE DBA','NJ');
1 row created.

SQL> commit;
Commit complete.

SQL> select * from dept;

DEPTNO DNAME          LOC
------ -------------- -------------
10     ACCOUNTING     NEW YORK
20     RESEARCH       DALLAS
30     SALES          CHICAGO
40     OPERATIONS     BOSTON
50     ORACLE DBA     NJ

SQL> desc dept
Name          Null?    Type                                                                                                 
------------- -------- -------------------------------
DEPTNO        NOT NULL NUMBER(2)
DNAME                  VARCHAR2(14)
LOC                    VARCHAR2(13)                                                                                                       

SQL> alter table dept add test_col varchar(25);
Table altered.

SQL> desc dept
Name         Null?     Type                                                                                                 
------------ --------  ---------------------------------------------------------------
DEPTNO       NOT NULL  NUMBER(2)
DNAME                  VARCHAR2(14)
LOC                    VARCHAR2(13)
TEST_COL               VARCHAR2(25)                                                                                                         

SQL> alter table dept drop column test_col;
Table altered.

SQL> desc dept
Name         Null?     Type                                                                                                 
------------ --------  ---------------------------------------------------------------
DEPTNO       NOT NULL  NUMBER(2)
DNAME                  VARCHAR2(14)
LOC                    VARCHAR2(13)                                                                                                       

Logon no Oracle GoldenGate 11g (origem) e cheque as estatísticas dos processos (Extract e Pump)

[oracle@ggnode1 11gg]$ ./ggsci

Oracle GoldenGate Command Interpreter for Oracle
Version 11.2.1.0.0 OGGCORE_11.2.1.0.0_PLATFORMS_120131.1910_FBO
Linux, x64, 64bit (optimized), Oracle 11g on Jul 31 201600:55:59
Copyright (C) 1995, 2012, Oracle and/or its affiliates. All rights reserved.

GGSCI (ggnode1.oracle.com) 1> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt
MANAGER     RUNNING
EXTRACT     RUNNING     ES1A        00:00:00      00:00:07
EXTRACT     RUNNING     PS1A        00:00:00      00:00:01
EXTRACT     RUNNING     PS1B        00:00:00      00:00:03

GGSCI (ggnode1.oracle.com) 2> stats es1a,total

Sending STATS request to EXTRACT ES1A ...
Start of Statistics at 2016-07-31 05:49:27.
DDL replication statistics (for all trails):

*** Total statistics since extract started     ***
Operations                                         2.00
Mapped operations                                  2.00
Unmapped operations                                0.00
Other operations                                   0.00
Excluded operations                                0.00

Output to /u01/app/ogg/11gg/dirdat/ya:
Extracting from OGGUSER.GGS_MARKER to OGGUSER.GGS_MARKER:

*** Total statistics since 2016-07-31 05:02:16 ***
No database operations have been performed.
Extracting from SCOTT.DEPT to SCOTT.DEPT:

*** Total statistics since 2016-07-31 05:02:16 ***
Total inserts                                      1.00
Total updates                                      0.00
Total deletes                                      0.00
Total discards                                     0.00
Total operations                                   1.00
End of Statistics.

GGSCI (ggnode1.oracle.com) 3> stats ps1a,total

Sending STATS request to EXTRACT PS1A ...
Start of Statistics at 2016-07-31 05:55:57.
DDL replication statistics (for all trails):

*** Total statistics since extract started     ***
Operations                                         2.00

Output to /u01/app/ogg/12gg/dirdat/ra:

Extracting from SCOTT.DEPT to SCOTT.DEPT:

*** Total statistics since 2016-07-31 05:41:07 ***
Total inserts                                      1.00
Total updates                                      0.00
Total deletes                                      0.00
Total discards                                     0.00
Total operations                                   1.00
End of Statistics.

Logue-se no Database destino (Oracle 11g - TGTDB2):

[oracle@ggnode2 ~]$ sqlplus /nolog

SQL*Plus: Release 11.2.0.3.0 Production on Sun Jul 31 04:32:30 2016
Copyright (c) 1982, 2011, Oracle.  All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> conn scott/oracle
Connected.

SQL> select * from dept;

DEPTNO  DNAME          LOC
------- -------------- -------------
10      ACCOUNTING     NEW YORK
20      RESEARCH       DALLAS
30      SALES          CHICAGO
40      OPERATIONS     BOSTON
50      ORACLE DBA     NJ

SQL> desc dept
Name         Null?     Type                                                                                                 
------------ --------  --------------------------------------------
DEPTNO       NOT NULL  NUMBER(2)
DNAME                  VARCHAR2(14)
LOC                    VARCHAR2(13)                                                                                                       

SQL> desc dept
Name          Null?    Type                                                                                                 
--------------------------------------------------------------------
DEPTNO       NOT NULL  NUMBER(2)
DNAME                  VARCHAR2(14)
LOC                    VARCHAR2(13)
TEST_COL               VARCHAR2(25)                                                                                                         

SQL> desc dept
Name         Null?     Type                                                                                                 
------------ --------  ----------------------------------------------
DEPTNO       NOT NULL  NUMBER(2)
DNAME                  VARCHAR2(14)
LOC                    VARCHAR2(13)                                                                                                       

Logue-se no Oracle GoldenGate 11g (Destino) e cheque as estatísticas do processo (Replicat)

[oracle@ggnode2 11gg]$ ./ggsci

Oracle GoldenGate Command Interpreter for Oracle
Version 11.2.1.0.0 OGGCORE_11.2.1.0.0_PLATFORMS_120131.1910_FBO
Linux, x64, 64bit (optimized), Oracle 11g on Jul 31 201600:55:59
Copyright (C) 1995, 2012, Oracle and/or its affiliates. All rights reserved.

GGSCI (ggnode2.oracle.com) 1> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt
MANAGER     RUNNING
REPLICAT    RUNNING     RS1B        00:00:00      00:00:00
REPLICAT    RUNNING     RS1D        00:00:00      00:00:05

GGSCI (ggnode2.oracle.com) 2> stats rs1d, total

Sending STATS request to REPLICAT RS1D ...
Start of Statistics at 2016-07-31 05:51:27.
DDL replication statistics:

*** Total statistics since replicat started     ***
Operations                                         2.00
Mapped operations                                  2.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

Replicating from SCOTT.DEPT to SCOTT.DEPT:

*** Total statistics since 2016-07-31 05:41:12 ***
Total inserts                                      1.00
Total updates                                      0.00
Total deletes                                      0.00
Total discards                                     0.00
Total operations                                   1.00
End of Statistics.

Logue-se no Database destino (Oracle 12c - TGTDB1):

[oracle@ggnode2 ~]$ sqlplus /nolog

SQL*Plus: Release 12.1.0.1.0 Production on Sun Jul 31 04:31:46 2016
Copyright (c) 1982, 2013, Oracle.  All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL> conn scott/oracle
Connected.

SQL> select * from dept;

DEPTNO  DNAME          LOC
------- -------------- -------------
10      ACCOUNTING     NEW YORK
20      RESEARCH       DALLAS
30      SALES          CHICAGO
40      OPERATIONS     BOSTON
50      ORACLE DBA     NJ

SQL> desc dept
Name         Null?     Type                                                                                                 
------------ --------  -------------------------------------
DEPTNO       NOT NULL  NUMBER(2)
DNAME                  VARCHAR2(14)
LOC                    VARCHAR2(13)                                                                                                       

SQL> desc dept
Name         Null?     Type                                                                                                 
------------ --------  --------------------------------------
DEPTNO       NOT NULL  NUMBER(2)
DNAME                  VARCHAR2(14)
LOC                    VARCHAR2(13)
TEST_COL               VARCHAR2(25)                                                                                                         

SQL> desc dept
Name         Null?     Type                                                                                                 
------------ --------  ---------------------------------------
DEPTNO       NOT NULL  NUMBER(2)
DNAME                  VARCHAR2(14)
LOC                    VARCHAR2(13)                                                                                                       

 

Logue-se no Oracle GoldenGate 12c (Destino) e cheque as estatísticas do processo (Replicat)

[oracle@ggnode2 12gg]$ ./ggsci

Oracle GoldenGate Command Interpreter for Oracle
Version 12.1.2.0.0 17185003 OGGCORE_12.1.2.0.0_PLATFORMS_130924.1316_FBO
Linux, x64, 64bit (optimized), Oracle 12c on Jul 31 2016 02:33:54
Operating system character set identified as UTF-8.
Copyright (C) 1995, 2013, Oracle and/or its affiliates. All rights reserved.

GGSCI (ggnode2.oracle.com) 1> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt
MANAGER     RUNNING
REPLICAT    RUNNING     RS1A        00:00:00      00:00:09
REPLICAT    RUNNING     RS1C        00:00:00      00:00:00

GGSCI (ggnode2.oracle.com) 2> stats rs1a, total

Sending STATS request to REPLICAT RS1A ...
Start of Statistics at 2016-07-31 05:51:04.
DDL replication statistics:

*** Total statistics since replicat started     ***
Operations                                         2.00
Mapped operations                                  2.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

Replicating from SCOTT.DEPT to SCOTT.DEPT:

*** Total statistics since 2016-07-31 05:41:15 ***
Total inserts                                      1.00
Total updates                                      0.00
Total deletes                                      0.00
Total discards                                     0.00
Total operations                                   1.00
End of Statistics.

Logue-se no Database de origem (Oracle 12c - Srcdb12c)

[oracle@ggnode1 ~]$ sqlplus /nolog

SQL*Plus: Release 12.1.0.1.0 Production on Sun Jul 31 04:31:20 2016
Copyright (c) 1982, 2013, Oracle.  All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL> conn hr/oracle
Connected.

SQL> select * from jobs;

JOB_ID     JOB_TITLE                           MIN_SALARY MAX_SALARY
---------- ----------------------------------- ---------- ----------
AD_PRES    President                                20080      40000
AD_VP      Administration Vice President            15000      30000
AD_ASST    Administration Assistant                  3000       6000
FI_MGR     Finance Manager                           8200      16000
FI_ACCOUNT Accountant                                4200       9000
AC_MGR     Accounting Manager                        8200      16000
AC_ACCOUNT Public Accountant                         4200       9000
SA_MAN     Sales Manager                            10000      20080
SA_REP     Sales Representative                      6000      12008
PU_MAN     Purchasing Manager                        8000      15000
PU_CLERK   Purchasing Clerk                          2500       5500
ST_MAN     Stock Manager                             5500       8500
ST_CLERK   Stock Clerk                               2008       5000
SH_CLERK   Shipping Clerk                            2500       5500
IT_PROG    Programmer                                4000      10000
MK_MAN     Marketing Manager                         9000      15000
MK_REP     Marketing Representative                  4000       9000
HR_REP     Human Resources Representative            4000       9000
PR_REP     Public Relations Representative           4500      10500

19 rows selected.

SQL> insert into jobs values ('ORA_DBA','Database Administrator','39000','93000');
1 row created.

SQL> commit;
Commit complete.

SQL> select * from jobs;

JOB_ID     JOB_TITLE                           MIN_SALARY MAX_SALARY
---------- ----------------------------------- ---------- ----------
ORA_DBA    Database Administrator                   39000      93000
AD_PRES    President                                20080      40000
AD_VP      Administration Vice President            15000      30000
AD_ASST    Administration Assistant                  3000       6000
FI_MGR     Finance Manager                           8200      16000
FI_ACCOUNT Accountant                                4200       9000
AC_MGR     Accounting Manager                        8200      16000
AC_ACCOUNT Public Accountant                         4200       9000
SA_MAN     Sales Manager                            10000      20080
SA_REP     Sales Representative                      6000      12008
PU_MAN     Purchasing Manager                        8000      15000
PU_CLERK   Purchasing Clerk                          2500       5500
ST_MAN     Stock Manager                             5500       8500
ST_CLERK   Stock Clerk                               2008       5000
SH_CLERK   Shipping Clerk                            2500       5500
IT_PROG    Programmer                                4000      10000
MK_MAN     Marketing Manager                         9000      15000
MK_REP     Marketing Representative                  4000       9000
HR_REP     Human Resources Representative            4000       9000
PR_REP     Public Relations Representative           4500      10500

20 rows selected.

SQL> desc jobs
Name                                      Null?    Type
----------------------------------------- -------- ----------------------------
JOB_ID                                    NOT NULL VARCHAR2(10)
JOB_TITLE                                 NOT NULL VARCHAR2(35)
MIN_SALARY                                         NUMBER(6)
MAX_SALARY                                         NUMBER(6)

SQL> alter table jobs add test_col varchar(25);
Table altered.

SQL> desc jobs
Name                                      Null?    Type
----------------------------------------- -------- ----------------------------
JOB_ID                                    NOT NULL VARCHAR2(10)
JOB_TITLE                                 NOT NULL VARCHAR2(35)
MIN_SALARY                                         NUMBER(6)
MAX_SALARY                                         NUMBER(6)
TEST_COL                                           VARCHAR2(25)

SQL> alter table jobs drop column test_col;
Table altered.

SQL> desc jobs
Name                                      Null?    Type
----------------------------------------- -------- ----------------------------
JOB_ID                                    NOT NULL VARCHAR2(10)
JOB_TITLE                                 NOT NULL VARCHAR2(35)
MIN_SALARY                                         NUMBER(6)
MAX_SALARY                                         NUMBER(6)

Logue-se no Oracle GoldenGate 12c (Origem) e cheque as estatísticas do processo (Extract)

[oracle@ggnode1 12gg]$ ./ggsci

Oracle GoldenGate Command Interpreter for Oracle
Version 12.1.2.0.0 17185003 OGGCORE_12.1.2.0.0_PLATFORMS_130924.1316_FBO
Linux, x64, 64bit (optimized), Oracle 12c on Jul 31 2016 02:33:54
Operating system character set identified as UTF-8.
Copyright (C) 1995, 2013, Oracle and/or its affiliates. All rights reserved.

GGSCI (ggnode1.oracle.com) 1> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING
EXTRACT     RUNNING     ES1B        00:00:00      00:00:08

GGSCI (ggnode1.oracle.com) 33> stats es1b, total

Sending STATS request to EXTRACT ES1B ...
Start of Statistics at 2016-07-31 05:50:09.
DDL replication statistics (for all trails):

*** Total statistics since extract started     ***
Operations                                         2.00
Mapped operations                                  2.00
Unmapped operations                                0.00
Other operations                                   0.00
Excluded operations                                0.00

Output to /u01/app/ogg/12gg/dirdat/yb:

Extracting from OGGUSER.GGS_MARKER to OGGUSER.GGS_MARKER:
*** Total statistics since 2016-07-31 05:30:34 ***
No database operations have been performed.
Extracting from HR.JOBS to HR.JOBS:

*** Total statistics since 2016-07-31 05:30:34 ***
Total inserts                                      1.00
Total updates                                      0.00
Total deletes                                      0.00
Total discards                                     0.00
Total operations                                   1.00

End of Statistics.

Logue-se no Oracle GoldenGate 11g (Origem) e cheque as estatísticas dos processos (Pump)

Note: Oracle 11G SOURCE PUMP PS1B - TO READ LOCAL TRAIL FILES FROM Oracle 12C SOURCE

[oracle@ggnode1 11gg]$ ./ggsci

Oracle GoldenGate Command Interpreter for Oracle
Version 11.2.1.0.0 OGGCORE_11.2.1.0.0_PLATFORMS_120131.1910_FBO
Linux, x64, 64bit (optimized), Oracle 11g on Jul 31 201600:55:59

Copyright (C) 1995, 2012, Oracle and/or its affiliates. All rights reserved.

GGSCI (ggnode1.oracle.com) 1> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt
MANAGER     RUNNING
EXTRACT     RUNNING     ES1A        00:00:00      00:00:07
EXTRACT     RUNNING     PS1A        00:00:00      00:00:01
EXTRACT     RUNNING     PS1B        00:00:00      00:00:03

GGSCI (ggnode1.oracle.com) 2> stats ps1b, total

Sending STATS request to EXTRACT PS1B ...
Start of Statistics at 2016-07-31 05:50:20.
DDL replication statistics (for all trails):

*** Total statistics since extract started     ***
Operations                                         2.00

Output to /u01/app/ogg/11gg/dirdat/rb:

Extracting from HR.JOBS to HR.JOBS:

*** Total statistics since 2016-07-31 05:46:41 ***
Total inserts                                      1.00
Total updates                                      0.00
Total deletes                                      0.00
Total discards                                     0.00
Total operations                                   1.00

End of Statistics.

Logue-se no Database destino (Oracle 11g - TGTDB2)

[oracle@ggnode2 ~]$ sqlplus /nolog

SQL*Plus: Release 11.2.0.3.0 Production on Sun Jul 31 04:32:30 2016
Copyright (c) 1982, 2011, Oracle.  All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> conn hr/oracle
Connected.

SQL>  select * from jobs;

JOB_ID     JOB_TITLE                           MIN_SALARY MAX_SALARY
---------- ----------------------------------- ---------- ----------
ORA_DBA    Database Administrator                   39000      93000
AD_PRES    President                                20080      40000
AD_VP      Administration Vice President            15000      30000
AD_ASST    Administration Assistant                  3000       6000
FI_MGR     Finance Manager                           8200      16000
FI_ACCOUNT Accountant                                4200       9000
AC_MGR     Accounting Manager                        8200      16000
AC_ACCOUNT Public Accountant                         4200       9000
SA_MAN     Sales Manager                            10000      20080
SA_REP     Sales Representative                      6000      12008
PU_MAN     Purchasing Manager                        8000      15000
PU_CLERK   Purchasing Clerk                          2500       5500
ST_MAN     Stock Manager                             5500       8500
ST_CLERK   Stock Clerk                               2008       5000
SH_CLERK   Shipping Clerk                            2500       5500
IT_PROG    Programmer                                4000      10000
MK_MAN     Marketing Manager                         9000      15000
MK_REP     Marketing Representative                  4000       9000
HR_REP     Human Resources Representative            4000       9000
PR_REP     Public Relations Representative           4500      10500

20 rows selected.

SQL> desc jobs
Name                                      Null?    Type
----------------------------------------- -------- ----------------------------
JOB_ID                                    NOT NULL VARCHAR2(10)
JOB_TITLE                                 NOT NULL VARCHAR2(35)
MIN_SALARY                                         NUMBER(6)
MAX_SALARY                                         NUMBER(6)

SQL> desc jobs
Name                                      Null?    Type
----------------------------------------- -------- ----------------------------
JOB_ID                                    NOT NULL VARCHAR2(10)
JOB_TITLE                                 NOT NULL VARCHAR2(35)
MIN_SALARY                                         NUMBER(6)
MAX_SALARY                                         NUMBER(6)
TEST_COL                                           VARCHAR2(25)

SQL> desc jobs
Name                                      Null?    Type
----------------------------------------- -------- ----------------------------
JOB_ID                                    NOT NULL VARCHAR2(10)
JOB_TITLE                                 NOT NULL VARCHAR2(35)
MIN_SALARY                                         NUMBER(6)
MAX_SALARY                                         NUMBER(6)

Logue-se no Oracle GoldenGate 11g (Destino) e cheque se as estatístics dos processos (Replicat)

[oracle@ggnode2 11gg]$ ./ggsci

Oracle GoldenGate Command Interpreter for Oracle
Version 11.2.1.0.0 OGGCORE_11.2.1.0.0_PLATFORMS_120131.1910_FBO
Linux, x64, 64bit (optimized), Oracle 11g on Jul 31 201600:55:59
Copyright (C) 1995, 2012, Oracle and/or its affiliates. All rights reserved.

GGSCI (ggnode2.oracle.com) 1> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt
MANAGER     RUNNING
REPLICAT    RUNNING     RS1B        00:00:00      00:00:00
REPLICAT    RUNNING     RS1D        00:00:00      00:00:05

GGSCI (ggnode2.oracle.com) 2> stats rs1b, total

Sending STATS request to REPLICAT RS1B ...
Start of Statistics at 2016-07-31 05:51:18.
DDL replication statistics:

*** Total statistics since replicat started     ***
Operations                                         2.00
Mapped operations                                  2.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

Replicating from HR.JOBS to HR.JOBS:

*** Total statistics since 2016-07-31 05:46:43 ***
Total inserts                                      1.00
Total updates                                      0.00
Total deletes                                      0.00
Total discards                                     0.00
Total operations                                   1.00

End of Statistics.

Logue-se no Target Database (Oracle 12c - TGTDB1)

[oracle@ggnode2 ~]$ sqlplus /nolog

SQL*Plus: Release 12.1.0.1.0 Production on Sun Jul 31 04:31:46 2016
Copyright (c) 1982, 2013, Oracle.  All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL> conn hr/oracle
Connected.

SQL>  select * from jobs;

JOB_ID     JOB_TITLE                           MIN_SALARY MAX_SALARY
---------- ----------------------------------- ---------- ----------
ORA_DBA    Database Administrator                   39000      93000
AD_PRES    President                                20080      40000
AD_VP      Administration Vice President            15000      30000
AD_ASST    Administration Assistant                  3000       6000
FI_MGR     Finance Manager                           8200      16000
FI_ACCOUNT Accountant                                4200       9000
AC_MGR     Accounting Manager                        8200      16000
AC_ACCOUNT Public Accountant                         4200       9000
SA_MAN     Sales Manager                            10000      20080
SA_REP     Sales Representative                      6000      12008
PU_MAN     Purchasing Manager                        8000      15000
PU_CLERK   Purchasing Clerk                          2500       5500
ST_MAN     Stock Manager                             5500       8500
ST_CLERK   Stock Clerk                               2008       5000
SH_CLERK   Shipping Clerk                            2500       5500
IT_PROG    Programmer                                4000      10000
MK_MAN     Marketing Manager                         9000      15000
MK_REP     Marketing Representative                  4000       9000
HR_REP     Human Resources Representative            4000       9000
PR_REP     Public Relations Representative           4500      10500

20 rows selected.

SQL> desc jobs
Name                                      Null?    Type
----------------------------------------- -------- ----------------------------
JOB_ID                                    NOT NULL VARCHAR2(10)
JOB_TITLE                                 NOT NULL VARCHAR2(35)
MIN_SALARY                                         NUMBER(6)
MAX_SALARY                                         NUMBER(6)

SQL> desc jobs
Name                                      Null?    Type
----------------------------------------- -------- ----------------------------
JOB_ID                                    NOT NULL VARCHAR2(10)
JOB_TITLE                                 NOT NULL VARCHAR2(35)
MIN_SALARY                                         NUMBER(6)
MAX_SALARY                                         NUMBER(6)
TEST_COL                                           VARCHAR2(25)

SQL> desc jobs
Name                                      Null?    Type
----------------------------------------- -------- ----------------------------
JOB_ID                                    NOT NULL VARCHAR2(10)
JOB_TITLE                                 NOT NULL VARCHAR2(35)
MIN_SALARY                                         NUMBER(6)
MAX_SALARY                                         NUMBER(6)

Logue-se no Oracle GoldenGate 12c (Destino) e cheque estatísticas dos processos (Replicat)

[oracle@ggnode2 12gg]$ ./ggsci

Oracle GoldenGate Command Interpreter for Oracle
Version 12.1.2.0.0 17185003 OGGCORE_12.1.2.0.0_PLATFORMS_130924.1316_FBO
Linux, x64, 64bit (optimized), Oracle 12c on Jul 31 2016 02:33:54
Operating system character set identified as UTF-8.
Copyright (C) 1995, 2013, Oracle and/or its affiliates. All rights reserved.

GGSCI (ggnode2.oracle.com) 1> info all

MANAGER     RUNNING
REPLICAT    RUNNING     RS1A        00:00:00      00:00:09
REPLICAT    RUNNING     RS1C        00:00:00      00:00:00

GGSCI (ggnode2.oracle.com) 2> stats rs1c, total

Sending STATS request to REPLICAT RS1C ...
Start of Statistics at 2016-07-31 05:51:12.
DDL replication statistics:

*** Total statistics since replicat started     ***
Operations                                         2.00
Mapped operations                                  2.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
Replicating from HR.JOBS to HR.JOBS:

*** Total statistics since 2016-07-31 05:46:46 ***
Total inserts                                      1.00
Total updates                                      0.00
Total deletes                                      0.00
Total discards                                     0.00
Total operations                                   1.00
End of Statistics.

Conclusão:  Os cenários acima ilustram a configuração e o funcionamento da replicação/consolidação do Oracle GoldenGate (várias origens para um único destino) e em cascata (uma origem para muitos destinos) combinados juntos usando diferentes versões do Oracle GoldenGate 11G & 12C e diferentes versões do Oracle Databases 11G & 12C.

 


Yenugula Venkata Ravikumar é um Oracle ACE e Oracle Certified Master (OCM) com 18 anos de experiência em  instituições financeiras, serviços financeiros e seguros (BFSI) e atuou em diversos papeis como Senior Database Architect e Production DBA.  Ele também é OCP em Oracle 8i, 9i, 10g, 11g & 12c e Certificado em Golden Gate, RAC, Performance Tuning & Oracle Exadata. Ele continua motivando muitos DBAs e ajudando a Oracle Community publicando suas dicas /ideias/sugestões/soluções em seu blog. Ele é membro do All India Oracle User Group (AIOUG) e frequente Oracle speaker in @NYOUG, @OTN, AIOUG, Sangam e IOUG. Ele desenha, projeta e implementa Core Banking System (CBS) Databases para o Central Banks em dois países – India e Mahe, Seychelles. Ele é Co-Founder do OraWorld (www.oraworld.com).

Rodrigo Mufalani é um DBA Sr. com mais de 10 anos de experiência, começou com o Oracle 8i, mas teve a oportunidade de dar suporte a Oracle 7.3.4 em diante. É especialista em banco de dados Oracle com foco principal em Performance & Tuning e RAC. É palestrante em eventos de Oracle como: OTN LAD TOUR e outros. Atualmente trabalha como consultor diversas empresas no segmento de variados ramos como: Educação, Saúde, Tecnologia, Seguros e etc. Foi o terceiro Oracle ACE a ser nomeado no Brasil e é OCP DBA nas versões 10g e 11g. Atualmente trabalha na Mufalani. Twitter @mufalani / blog www.mufalani.com.br/blog

Archana Durai tem mais 7+ anos de experiência como Oracle Database Administrator e tem expertise em vários Sistemas RDBMS. Ela tem experiência em muitos domínios como Life Sciences e Media & Communications. Ela é Oracle Certified Professional 9i, 10g, 11g & 12c e certificada em Oracle GoldenGate e Real Application Clusters (RAC), IBM DB2 Certified Administrator (v10.1). Além disso, ela possui a certifação Red Hat Certified Engineer (RHCE) e ITIL V3 Foundation.

Este artigo foi revisto pela equipe de produtos Oracle e está em conformidade com as normas e práticas para o uso de produtos Oracle.