Migração para a versão 12c do Banco de Dados sem Downtime, utilizando Oracle Golden Gate
Revisado por Marcelo Pivovar - Solution Architect
A interrupção dos serviços durante a migração de versão do banco de dados é um dos principais motivos que influenciam na decisão de migrar ou não. Porém ao optar por não migrar, você vai ter que lidar com problemas conhecidos, como a falta de suporte do fornecedor, bugs e a falta de novos recursos presentes nas versões mais recentes.
Utilizar o "Oracle Golden Gate" para realizar a migração, é a maneira mais simples e eficaz de reduzir o tempo de interrupção dos serviços durante uma migração de versão de um banco de dados Oracle.
Durante a migração, o desempenho do banco de dados de origem não será afetado, devido a forma como as informações são extraídas, fazendo o uso dos arquivos de "redo logs".
Ambiente utilizado:
Banco de dados:
- Banco de dados de origem: /u01/app/oracle/product/10.2.0/db_1
(Oracle 10g – 10.2.0.5.0)
- Banco de dados temporário: /u01/app/oracle/product/10.2.0/db_1
(Oracle 10g – 10.2.0.5.0)
- Banco de dados de destino: /u01/app/oracle/product/12.1.0/db_1
(Oracle 12c – 12.1.0.1.0)
Oracle Golden Gate:
- Versão no servidor de origem: 11.1.1.1.0 for Oracle 10g
/u01/app/ogg/10g
- Versão no servidor de destino: 12.1.0.0 for Oracle 12c
/u01/app/ogg/12c
Migração com o Oracle Golden Gate
Resumo:
- Faça um backup completo do banco de dados de origem e inicie a extração das alterações utilizando o “Oracle Golden Gate”.
- Aplique o backup extraído da base de dados de origem na base de dados de destino como uma carga inicial e migre o banco de dados.
- Aplique as alterações de dados extraídos do banco de dados de origem para o banco de dados de destino (Oracle Database 12c).
- Verifique o processo de sincronização no banco de dados de destino (Oracle Database 12c).
Inicialize o banco de dados de origem:
[oracle@grid11g SRCDB:~]# sqlplus / as sysdba
SQL*Plus: Release 10.2.0.5.0 - Production on Tue Dec 24 15:39:48 2013
Copyright (c) 1982, 2010, Oracle. All Rights Reserved.
Connected to an idle instance.
SQL> startup;
ORACLE instance started.
Total System Global Area 281018368 bytes
Fixed Size 2095672 bytes
Variable Size 96470472 bytes
Database Buffers 176160768 bytes
Redo Buffers 6291456 bytes
Database mounted.
Database opened.
Verifique se existem objetos inválidos no banco de dados de origem:
SQL> SELECT UNIQUE object_name, object_type, owner FROM dba_objects
WHERE status='INVALID';
no rows selected
Execute o script “utlrp.sql” no banco de dados de origem:
Este passo só é necessário se forem encontrados objetos inválidos.
SQL> @?/rdbms/admin/utlrp.sql
TIMESTAMP
--------------------------------------------------------------------------------
COMP_TIMESTAMP UTLRP_BGN 2013-12-24 15:42:06
DOC> The following PL/SQL block invokes UTL_RECOMP to recompile invalid
DOC> objects in the database. Recompilation time is proportional to the
DOC> number of invalid objects in the database, so this command may take
DOC> a long time to execute on a database with a large number of invalid objects.
DOC>
DOC> Use the following queries to track recompilation progress:
DOC>
DOC> 1. Query returning the number of invalid objects remaining. This
DOC> number should decrease with time.
DOC> SELECT COUNT(*) FROM obj$ WHERE status IN (4, 5, 6);
DOC>
DOC> 2. Query returning the number of objects compiled so far. This number
DOC> should increase with time.
DOC> SELECT COUNT(*) FROM UTL_RECOMP_COMPILED;
DOC>
DOC> This script automatically chooses serial or parallel recompilation
DOC> based on the number of CPUs available (parameter cpu_count) multiplied
DOC> by the number of threads per CPU (parameter parallel_threads_per_cpu).
DOC> On RAC, this number is added across all RAC nodes.
DOC>
DOC> UTL_RECOMP uses DBMS_SCHEDULER to create jobs for parallel
DOC> recompilation. Jobs are created without instance affinity so that they
DOC> can migrate across RAC nodes. Use the following queries to verify
DOC> whether UTL_RECOMP jobs are being created and run correctly:
DOC>
DOC> 1. Query showing jobs created by UTL_RECOMP
DOC> SELECT job_name FROM dba_scheduler_jobs
DOC> WHERE job_name like 'UTL_RECOMP_SLAVE_%';
DOC>
DOC> 2. Query showing UTL_RECOMP jobs that are running
DOC> SELECT job_name FROM dba_scheduler_running_jobs
DOC> WHERE job_name like 'UTL_RECOMP_SLAVE_%';
DOC>#
PL/SQL procedure successfully completed.
TIMESTAMP
--------------------------------------------------------------------------------
COMP_TIMESTAMP UTLRP_END 2013-12-24 15:42:08
PL/SQL procedure successfully completed.
DOC> The following query reports the number of objects that have compiled
DOC> with errors (objects that compile with errors have status set to 3 in
DOC> obj$). If the number is higher than expected, please examine the error
DOC> messages reported with each object (using SHOW ERRORS) to see if they
DOC> point to system misconfiguration or resource constraints that must be
DOC> fixed before attempting to recompile these objects.
DOC>#
OBJECTS WITH ERRORS
-------------------
0
DOC> The following query reports the number of errors caught during
DOC> recompilation. If this number is non-zero, please query the error
DOC> messages in the table UTL_RECOMP_ERRORS to see if any of these errors
DOC> are due to misconfiguration or resource constraints that must be
DOC> fixed before objects can compile successfully.
DOC>#
ERRORS DURING RECOMPILATION
---------------------------
0
PL/SQL procedure successfully completed.
Limpe a “lixeira” do banco de dados de origem:
SQL> purge DBA_RECYCLEBIN;
DBA Recyclebin purged.
Instale o Oracle Golden Gate no banco de dados de origem:
[oracle@grid11g SRCDB:~]# cd /u01/app/ogg/10g
[oracle@grid11g SRCDB:/u01/app/ogg/10g]# unzip fbo_ggs_Linux_x64_ora10g_64bit.zip
Archive: fbo_ggs_Linux_x64_ora10g_64bit.zip
replace fbo_ggs_Linux_x64_ora10g_64bit.tar? [y]es, [n]o, [A]ll, [N]one, [r]ename: A
inflating: fbo_ggs_Linux_x64_ora10g_64bit.tar
inflating: OGG_WinUnix_Rel_Notes_11.1.1.1.0.pdf
inflating: README.txt
[oracle@grid11g SRCDB:/u01/app/ogg/10g]# tar -xvf fbo_ggs_Linux_x64_ora10g_64bit.tar
UserExitExamples/
UserExitExamples/ExitDemo_more_recs/
UserExitExamples/ExitDemo_more_recs/Makefile_more_recs.AIX
UserExitExamples/ExitDemo_more_recs/exitdemo_more_recs.vcproj
UserExitExamples/ExitDemo_more_recs/Makefile_more_recs.SOLARIS
UserExitExamples/ExitDemo_more_recs/exitdemo_more_recs.c
UserExitExamples/ExitDemo_more_recs/Makefile_more_recs.LINUX
UserExitExamples/ExitDemo_more_recs/Makefile_more_recs.HPUX
UserExitExamples/ExitDemo_more_recs/readme.txt
UserExitExamples/ExitDemo_pk_befores/
UserExitExamples/ExitDemo_pk_befores/Makefile_pk_befores.AIX
UserExitExamples/ExitDemo_pk_befores/exitdemo_pk_befores.c
UserExitExamples/ExitDemo_pk_befores/Makefile_pk_befores.HPUX
UserExitExamples/ExitDemo_pk_befores/exitdemo_pk_befores.vcproj
UserExitExamples/ExitDemo_pk_befores/Makefile_pk_befores.SOLARIS
UserExitExamples/ExitDemo_pk_befores/Makefile_pk_befores.LINUX
UserExitExamples/ExitDemo_pk_befores/readme.txt
UserExitExamples/ExitDemo_passthru/
UserExitExamples/ExitDemo_passthru/Makefile_passthru.HPUX
UserExitExamples/ExitDemo_passthru/Makefile_passthru.SOLARIS
UserExitExamples/ExitDemo_passthru/Makefile_passthru.AIX
UserExitExamples/ExitDemo_passthru/exitdemo_passthru.c
UserExitExamples/ExitDemo_passthru/Makefile_passthru.LINUX
UserExitExamples/ExitDemo_passthru/readme.txt
UserExitExamples/ExitDemo_passthru/exitdemopassthru.vcproj
UserExitExamples/ExitDemo_passthru/Makefile_passthru.HP_OSS
UserExitExamples/ExitDemo_lobs/
UserExitExamples/ExitDemo_lobs/exitdemo_lob.vcproj
UserExitExamples/ExitDemo_lobs/Makefile_lob.SOLARIS
UserExitExamples/ExitDemo_lobs/Makefile_lob.AIX
UserExitExamples/ExitDemo_lobs/exitdemo_lob.c
UserExitExamples/ExitDemo_lobs/Makefile_lob.LINUX
UserExitExamples/ExitDemo_lobs/Makefile_lob.HPUX
UserExitExamples/ExitDemo_lobs/readme.txt
UserExitExamples/ExitDemo/
UserExitExamples/ExitDemo/Makefile_exit_demo.SOLARIS
UserExitExamples/ExitDemo/exitdemo.c
UserExitExamples/ExitDemo/Makefile_exit_demo.HP_OSS
UserExitExamples/ExitDemo/Makefile_exit_demo.HPUX
UserExitExamples/ExitDemo/Makefile_exit_demo.AIX
UserExitExamples/ExitDemo/exitdemo.vcproj
UserExitExamples/ExitDemo/Makefile_exit_demo.LINUX
UserExitExamples/ExitDemo/readme.txt
bcpfmt.tpl
bcrypt.txt
cfg/
cfg/password.properties
cfg/jps-config-jse.xml
cfg/Config.properties
cfg/MPMetadataSchema.xsd
cfg/ProfileConfig.xml
cfg/mpmetadata.xml
chkpt_ora_create.sql
cobgen
convchk
db2cntl.tpl
ddl_access.tpl
ddl_cleartrace.sql
ddl_db2.tpl
ddl_db2_os390.tpl
ddl_ddl2file.sql
ddl_disable.sql
ddl_enable.sql
ddl_filter.sql
ddl_informix.tpl
ddl_mss.tpl
ddl_mysql.tpl
ddl_nopurgeRecyclebin.sql
ddl_nssql.tpl
ddl_ora10.sql
ddl_ora10upCommon.sql
ddl_ora11.sql
ddl_ora9.sql
ddl_oracle.tpl
ddl_pin.sql
ddl_purgeRecyclebin.sql
ddl_remove.sql
ddl_session.sql
ddl_session1.sql
ddl_setup.sql
ddl_sqlmx.tpl
ddl_status.sql
ddl_staymetadata_off.sql
ddl_staymetadata_on.sql
ddl_sybase.tpl
ddl_tandem.tpl
ddl_trace_off.sql
ddl_trace_on.sql
ddl_tracelevel.sql
ddlcob
ddlgen
defgen
demo_more_ora_create.sql
demo_more_ora_insert.sql
demo_ora_create.sql
demo_ora_insert.sql
demo_ora_lob_create.sql
demo_ora_misc.sql
demo_ora_pk_befores_create.sql
demo_ora_pk_befores_insert.sql
demo_ora_pk_befores_updates.sql
dirjar/
dirjar/spring-security-core-3.0.1.RELEASE.jar
dirjar/org.springframework.test-3.0.0.RELEASE.jar
dirjar/jps-mbeans.jar
dirjar/org.springframework.instrument-3.0.0.RELEASE.jar
dirjar/spring-security-config-3.0.1.RELEASE.jar
dirjar/oraclepki.jar
dirjar/jagent.jar
dirjar/jps-wls.jar
dirjar/identitystore.jar
dirjar/spring-security-web-3.0.1.RELEASE.jar
dirjar/spring-security-cas-client-3.0.1.RELEASE.jar
dirjar/fmw_audit.jar
dirjar/osdt_core.jar
dirjar/org.springframework.context.support-3.0.0.RELEASE.jar
dirjar/osdt_xmlsec.jar
dirjar/jacc-spi.jar
dirjar/slf4j-api-1.4.3.jar
dirjar/org.springframework.aspects-3.0.0.RELEASE.jar
dirjar/identityutils.jar
dirjar/org.springframework.core-3.0.0.RELEASE.jar
dirjar/spring-security-acl-3.0.1.RELEASE.jar
dirjar/jps-manifest.jar
dirjar/jsr250-api-1.0.jar
dirjar/jps-upgrade.jar
dirjar/log4j-1.2.15.jar
dirjar/jps-ee.jar
dirjar/org.springframework.transaction-3.0.0.RELEASE.jar
dirjar/xpp3_min-1.1.4c.jar
dirjar/xmlparserv2.jar
dirjar/spring-security-taglibs-3.0.1.RELEASE.jar
dirjar/jps-unsupported-api.jar
dirjar/slf4j-log4j12-1.4.3.jar
dirjar/org.springframework.context-3.0.0.RELEASE.jar
dirjar/xstream-1.3.jar
dirjar/jdmkrt-1.0-b02.jar
dirjar/jps-patching.jar
dirjar/org.springframework.beans-3.0.0.RELEASE.jar
dirjar/monitor-common.jar
dirjar/jmxremote_optional-1.0-b02.jar
dirjar/jps-common.jar
dirjar/org.springframework.expression-3.0.0.RELEASE.jar
dirjar/org.springframework.asm-3.0.0.RELEASE.jar
dirjar/osdt_cert.jar
dirjar/org.springframework.aop-3.0.0.RELEASE.jar
dirjar/org.springframework.web-3.0.0.RELEASE.jar
dirjar/commons-codec-1.3.jar
dirjar/org.springframework.orm-3.0.0.RELEASE.jar
dirjar/jps-internal.jar
dirjar/jps-api.jar
dirjar/ldapjclnt11.jar
dirjar/commons-logging-1.0.4.jar
dirjar/org.springframework.jdbc-3.0.0.RELEASE.jar
emsclnt
extract
freeBSD.txt
ggMessage.dat
ggsci
help.txt
jagent.sh
keygen
libicudata.so.38
libicui18n.so.38
libicuuc.so.38
libxerces-c.so.28
libxml2.txt
logdump
marker_remove.sql
marker_setup.sql
marker_status.sql
mgr
notices.txt
params.sql
prvtclkm.plb
pw_agent_util.sh
remove_seq.sql
replicat
reverse
role_setup.sql
sequence.sql
server
sqlldr.tpl
tcperrs
usrdecs.h
zlib.txt
Abra a console de linha de comandos do Oracle Golden Gate (GGSCI) e crie os diretórios necessários:
[oracle@grid11g SRCDB:/u01/app/ogg/10g]# ./ggsci
Oracle GoldenGate Command Interpreter for Oracle
Version 11.1.1.1 OGGCORE_11.1.1_PLATFORMS_110421.2040
Linux, x64, 64bit (optimized), Oracle 10g on Apr 21 2011 22:26:49
Copyright (C) 1995, 2011, Oracle and/or its affiliates. All rights reserved.
GGSCI (grid11g.kr.oracle.com) 1> create subdirs
Creating subdirectories under current directory /u01/app/ogg/10g
Parameter files /u01/app/ogg/10g/dirprm: already exists
Report files /u01/app/ogg/10g/dirrpt: already exists
Checkpoint files /u01/app/ogg/10g/dirchk: already exists
Process status files /u01/app/ogg/10g/dirpcs: already exists
SQL script files /u01/app/ogg/10g/dirsql: already exists
Database definitions files /u01/app/ogg/10g/dirdef: already exists
Extract data files /u01/app/ogg/10g/dirdat: already exists
Temporary files /u01/app/ogg/10g/dirtmp: already exists
Veridata files /u01/app/ogg/10g/dirver: already exists
Veridata Lock files /u01/app/ogg/10g/dirver/lock: already exists
Veridata Out-Of-Sync files /u01/app/ogg/10g/dirver/oos: already exists
Veridata Out-Of-Sync XML files /u01/app/ogg/10g/dirver/oosxml: already exists
Veridata Parameter files /u01/app/ogg/10g/dirver/params: already exists
Veridata Report files /u01/app/ogg/10g/dirver/report: already exists
Veridata Status files /u01/app/ogg/10g/dirver/status: already exists
Veridata Trace files /u01/app/ogg/10g/dirver/trace: already exists
Stdout files /u01/app/ogg/10g/dirout: already exists
GGSCI (grid11g.kr.oracle.com) 2> exit
Nota: Se a mensagem "already exists" for exibida enquanto os diretórios são criados, você pode ignorá-la.
Crie um usuário no banco de dados para o "Oracle Golden Gate" e atribua os privilégios necessários:
[oracle@grid11g SRCDB:/u01/app/ogg/10g]# sqlplus / as sysdba
SQL*Plus: Release 10.2.0.5.0 - Production on Tue Dec 24 15:44:03 2013
Copyright (c) 1982, 2010, Oracle. All Rights Reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> create user oggmgr identified by oracle default tablespace users;
User created.
SQL> @role_setup
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:oggmgr
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,connect, resource, dba to oggmgr; Grant succeeded.
Altere o modo "logging" e as propriedades do banco de dados de origem:
SQL> alter database force logging;
Database altered.
SQL> alter database add supplemental log data;
Database altered.
SQL> alter system switch logfile;
System altered.
SQL> alter system archive log current;
System altered.
SQL> alter system set recyclebin=off scope=both;
System altered.
SQL> @marker_setup
Marker setup 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: oggmgr
Marker setup table script complete, running verification script... Please enter the name of a schema for the GoldenGate database objects: Setting schema name to OGGMGR
MARKER TABLE
-------------------------------
OK
MARKER SEQUENCE
-------------------------------
OK
Script complete.
SQL> @ddl_setup
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 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 GoldenGate schema name: oggmgr
You will be prompted for the mode of installation.
To install or reinstall DDL replication, enter INITIALSETUP
To upgrade DDL replication, enter NORMAL
Enter mode of installation:INITIALSETUP
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 OGGMGR as a GoldenGate schema name, INITIALSETUP as a mode of installation.
Working, please wait ...
RECYCLEBIN must be empty.
This installation will purge RECYCLEBIN for all users.
To proceed, enter yes. To stop installation, enter no.
Enter yes or no:yes
DDL replication setup script complete, running verification script...
Please enter the name of a schema for the GoldenGate database objects:
Setting schema name to OGGMGR
DDLORA_GETTABLESPACESIZE STATUS:
Line/pos Error
--------------------------------- ------------------------------------
No errors No errors
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 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/SRCDB/udump/ggs_ddl_trace.log
Analyzing installation status...
STATUS OF DDL REPLICATION
----------------------------------------------------------------------
SUCCESSFUL installation of DDL Replication software components
Script complete.
SQL> @ddl_enable
Trigger altered.
SQL> exec dbms_streams_auth.grant_admin_privilege('OGGMGR');
PL/SQL procedure successfully completed.
SQL> grant insert on system.logmnr_restart_ckpt$ to oggmgr;
Grant succeeded.
SQL> grant update on sys.streams$_capture_process to oggmgr;
Grant succeeded.
SQL> grant become user to oggmgr;
Grant succeeded.
SQL> exit
Instale e configure o “Oracle Golden Gate“ no banco de dados de destino:
[oracle@grid11g SRCDB:~/stage]# ls -lrth
-rwxrwx--- 1 oracle oinstall 326M Oct 28 10:38 121200_fbo_ggs_Linux_x64_shiphome.zip
[oracle@grid11g SRCDB:~/stage]#unzip 121200_fbo_ggs_Linux_x64_shiphome.zip
[oracle@grid11g SRCDB:~/stage]# cd fbo_ggs_Linux_x64_shiphome/
[oracle@grid11g SRCDB:~/stage/fbo_ggs_Linux_x64_shiphome]# pwd
/home/oracle/stage/fbo_ggs_Linux_x64_shiphome
[oracle@grid11g SRCDB:~/stage/fbo_ggs_Linux_x64_shiphome]# ls -lrth
total 4.0K
drwxr-xr-x 5 oracle oinstall 4.0K Sep 25 18:59 Disk1
[oracle@grid11g SRCDB:~/stage/fbo_ggs_Linux_x64_shiphome]# cd Disk1/
[oracle@grid11g SRCDB:~/stage/fbo_ggs_Linux_x64_shiphome/Disk1]# ls -lrth
total 16K
drwxr-xr-x 4 oracle oinstall 4.0K Sep 25 18:59 install
drwxr-xr-x 11 oracle oinstall 4.0K Sep 25 18:59 stage
-rwxr-xr-x 1 oracle oinstall 918 Sep 25 18:59 runInstaller
drwxrwxr-x 2 oracle oinstall 4.0K Sep 25 18:59 response
[oracle@grid11g SRCDB:~/stage/fbo_ggs_Linux_x64_shiphome/Disk1]#./runInstaller &
Selecione a opção “Oracle Goldendate for Oracle Database 12c (506.0MB)” Clique no botão “Next“.
Altere o caminho de instalação para “/u01/app/ogg/12c” e desmarque a opção “Start Manager” Clique no botão “Next“.
Clique no botão “Install“.
Clique no botão “Finish“.
Abra a console de linha de comandos do “Oracle Golden Gate (GGSCI) e crie os diretórios necessários no banco de dados de destino:”
[oracle@grid11g TGTDB:/u01/app/ogg/12c]# ./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 Sep 25 2013 02:33:54
Operating system character set identified as UTF-8.
Copyright (C) 1995, 2013, Oracle and/or its affiliates. All rights reserved.
GGSCI (grid11g.kr.oracle.com) 1> create subdirs
Creating subdirectories under current directory /u01/app/ogg/12c
Parameter files /u01/app/ogg/12c/dirprm: already exists
Report files /u01/app/ogg/12c/dirrpt: already exists
Checkpoint files /u01/app/ogg/12c/dirchk: already exists
Process status files /u01/app/ogg/12c/dirpcs: already exists
SQL script files /u01/app/ogg/12c/dirsql: already exists
Database definitions files /u01/app/ogg/12c/dirdef: already exists
Extract data files /u01/app/ogg/12c/dirdat: already exists
Temporary files /u01/app/ogg/12c/dirtmp: already exists
Credential store files /u01/app/ogg/12c/dircrd: already exists
Masterkey wallet files /u01/app/ogg/12c/dirwlt: already exists
Dump files /u01/app/ogg/12c/dirdmp: already exists
GGSCI (grid11g.kr.oracle.com) 2> exit
Crie um “schema“ temporário no banco de dados de origem:
[oracle@grid11g SRCDB:/u01/app/ogg/10g]# sqlplus / as sysdba
SQL*Plus: Release 10.2.0.5.0 - Production on Tue Dec 24 16:12:20 2013
Copyright (c) 1982, 2010, Oracle. All Rights Reserved.
Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> create user scott identified by tiger default tablespace users;
User created.
SQL> grant connect, resource to scott;
Grant succeeded.
SQL> conn scott/tiger
Connected
Nota: Existem duas tabelas já criadas "tcustmer" e "tcustord" com alguns registros inseridos. Crie uma sequência:
SQL> CREATE SEQUENCE SCOTT.TQ increment by 1 start with 1 nomaxvalue nocycle nocache;
Sequence created.
SQL> CREATE TABLE SCOTT.T ( ID NUMBER, TXDATE DATE) TABLESPACE USERS;
Table created.
SQL> insert into T values ( TQ.NEXTVAL,sysdate);
1 row created.
SQL> commit;
Commit complete.
Nota: Realize transações simples e execute o "commit" no banco dados de origem (Oracle 10g), isto será feito com o objetivo de verificar se a configuração está funcionando adequadamente.
Configure os parâmetros do “oggmgr” no banco de dados de origem:
[oracle@grid11g SRCDB:/u01/app/ogg/10g]# ./ggsci
Oracle GoldenGate Command Interpreter for Oracle
Version 11.1.1.1 OGGCORE_11.1.1_PLATFORMS_110421.2040
Linux, x64, 64bit (optimized), Oracle 10g on Apr 21 2011 22:26:49
Copyright (C) 1995, 2011, Oracle and/or its affiliates. All rights reserved.
GGSCI (grid11g.kr.oracle.com) 1> edit params mgr
--GoldenGate Manager parameter file
PORT 5000
--FOR Source, ASIS
PURGEOLDEXTRACTS ./dirdat/TA*, USECHECKPOINTS, MINKEEPDAYS 3
GGSCI (grid11g.kr.oracle.com) 2> start mgr
Manager started.
GGSCI (grid11g.kr.oracle.com) 3> info mgr
Manager is running (IP port grid11g.kr.oracle.com.5000).
Configure os parâmetros do “oggmgr” no banco de dados de destino:
oracle@grid11g TGTDB:~]# cd /u01/app/ogg/12c
[oracle@grid11g TGTDB:/u01/app/ogg/12c]# ./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 Sep 25 2013 02:33:54
Operating system character set identified as UTF-8.
Copyright (C) 1995, 2013, Oracle and/or its affiliates. All rights reserved.
GGSCI (grid11g.kr.oracle.com) 1> edit params mgr
GGSCI (grid11g.kr.oracle.com) 1> view params mgr
--GoldenGate Manager parameter file
PORT 5001
--FOR Target, TOBE
PURGEOLDEXTRACTS ./dirdat/TB*, USECHECKPOINTS, MINKEEPDAYS 3
GGSCI (grid11g.kr.oracle.com) 2> start mgr
Manager started.
GGSCI (grid11g.kr.oracle.com) 3> info mgr
Manager is running (IP port grid11g.kr.oracle.com.5001, Process ID 4775).
GGSCI (grid11g.kr.oracle.com) 4> exit
Habilite o “Supplemental Logging” a nível de tabela no banco de dados de origem:
[oracle@grid11g SRCDB:/u01/app/ogg/10g]# cd /u01/app/ogg/10g
[oracle@grid11g SRCDB:/u01/app/ogg/10g]# ./ggsci
Oracle GoldenGate Command Interpreter for Oracle
Version 11.1.1.1 OGGCORE_11.1.1_PLATFORMS_110421.2040
Linux, x64, 64bit (optimized), Oracle 10g on Apr 21 2011 22:26:49
Copyright (C) 1995, 2011, Oracle and/or its affiliates. All rights reserved.
GGSCI (grid11g.kr.oracle.com) 1> dblogin userid oggmgr, password oracle
Successfully logged into database.
GGSCI (grid11g.kr.oracle.com) 2> add trandata scott.*
2013-12-24 16:19:04 WARNING OGG-00869 No unique key is defined for table T.
All viable columns will be used to represent the key, but may not guarantee uniqueness.
KEYCOLS may be used to define the key.
Logging of supplemental redo data enabled for table SCOTT.T.
Logging of supplemental redo data enabled for table SCOTT.TCUSTMER.
Logging of supplemental redo data enabled for table SCOTT.TCUSTORD.
GGSCI (grid11g.kr.oracle.com) 3> info trandata scott.*
Logging of supplemental redo log data is enabled for table SCOTT.T
Logging of supplemental redo log data is enabled for table SCOTT.TCUSTMER
Logging of supplemental redo log data is enabled for table SCOTT.TCUSTORD
GGSCI (grid11g.kr.oracle.com) 4> exit
Configure a extração e o processo “Data Pump” no banco de dados de origem:
GGSCI (grid11g.kr.oracle.com) 2> add extract extra, tranlog, begin now
2013-12-24 16:19:52 INFO OGG-01749 Successfully registered
EXTRACT EXTRA to start managing log retention at SCN 227038.
EXTRACT added.
GGSCI (grid11g.kr.oracle.com) 3> info extract extra
EXTRACT EXTRA Initialized 2013-12-24 16:19 Status STOPPED
Checkpoint Lag 00:00:00 (updated 00:00:10 ago)
Log Read Checkpoint Oracle Redo Logs
2013-12-24 16:19:50 Seqno 0, RBA 0
GGSCI (grid11g.kr.oracle.com) 4> add extract pumpa, exttrailsource ./dirdat/TA
EXTRACT added.
GGSCI (grid11g.kr.oracle.com) 5> info extract pumpa
EXTRACT PUMPA Initialized 2013-12-24 16:20 Status STOPPED
Checkpoint Lag 00:00:00 (updated 00:00:06 ago)
Log Read Checkpoint File ./dirdat/TA000000
First Record RBA 0
GGSCI (grid11g.kr.oracle.com) 6> edit params extra
GGSCI (grid11g.kr.oracle.com) 7> view params extra
-- Extract Parameter File
EXTRACT EXTRA
USERID oggmgr, PASSWORD oracle
TRANLOGOPTIONS OPENARCHIVEIMMEDIATE
THREADOPTIONS MAXCOMMITPROPAGATIONDELAY 60000 IOLATENCY 60000
TRANLOGOPTIONS EXCLUDEUSER OGGMGR, EXCLUDEUSERID 54
EXTTRAIL ./dirdat/TA
REPORTCOUNT EVERY 5 MINUTES, RATE
-- Use DDL parameter to configure support for all mapped DDL operations
DDL INCLUDE MAPPED
DDLOPTIONS ADDTRANDATA, REPORT
-- Table/Sequence List
TABLE SCOTT.*;
SEQUENCE SCOTT.*;
GGSCI (grid11g.kr.oracle.com) 8> edit params pumpa
GGSCI (grid11g.kr.oracle.com) 9> view params pumpa
-- Data Pump Parameter File
EXTRACT PUMPA
PASSTHRU
RMTHOST grid11g.kr.oracle.com, MGRPORT 5001
RMTTRAIL ./dirdat/TB
REPORTCOUNT EVERY 5 MINUTES, RATE
-- Table/Sequence List
TABLE SCOTT.*;
SEQUENCE SCOTT.*;
GGSCI (grid11g.kr.oracle.com) 10> add exttrail ./dirdat/TA, extract extra, megabytes 100
EXTTRAIL added.
GGSCI (grid11g.kr.oracle.com) 11> info exttrail *
Extract Trail: ./dirdat/TA
Extract: EXTRA
Seqno: 0
RBA: 0
File Size: 100M
GGSCI (grid11g.kr.oracle.com) 12> add rmttrail ./dirdat/TB, extract pumpa, megabytes 100
RMTTRAIL added.
GGSCI (grid11g.kr.oracle.com) 13> info rmttrail *
Extract Trail: ./dirdat/TA
Extract: EXTRA
Seqno: 0
RBA: 0
File Size: 100M
Extract Trail: ./dirdat/TB
Extract: PUMPA
Seqno: 0
RBA: 0
File Size: 100M
Inicie o processo de extração e o processo “Data Pump” no banco de dados de origem:
GGSCI (grid11g.kr.oracle.com) 12> start extract extra
Sending START request to MANAGER ...
EXTRACT EXTRA starting
GGSCI (grid11g.kr.oracle.com) 13> start extract pumpa
Sending START request to MANAGER ...
EXTRACT PUMPA starting
GGSCI (grid11g.kr.oracle.com) 14> info *
EXTRACT EXTRA Last Started 2013-12-24 16:22 Status RUNNING
Checkpoint Lag 00:02:19 (updated 00:00:09 ago)
Log Read Checkpoint Oracle Redo Logs
2013-12-24 16:19:50 Seqno 21, RBA 16422416
EXTRACT PUMPA Last Started 2013-12-24 16:22 Status RUNNING
Checkpoint Lag 00:00:00 (updated 00:00:00 ago)
Log Read Checkpoint File ./dirdat/TA000000
First Record RBA 0
GGSCI (grid11g.kr.oracle.com) 15> info extract extra, detail
EXTRACT EXTRA Last Started 2013-12-24 16:22 Status RUNNING
Checkpoint Lag 00:00:00 (updated 00:00:08 ago)
Log Read Checkpoint Oracle Redo Logs
2013-12-24 16:22:18 Seqno 21, RBA 17361408
Target Extract Trails:
Remote Trail Name Seqno RBA Max MB
./dirdat/TA 0 22271 100
Extract Source Begin End
/u01/app/oracle/oradata/SRCDB/redo03.log 2013-12-24 16:19 2013-12-24 16:22
Not Available * Initialized * 2013-12-24 16:19
Current directory /u01/app/ogg/10g
Report file /u01/app/ogg/10g/dirrpt/EXTRA.rpt
Parameter file /u01/app/ogg/10g/dirprm/extra.prm
Checkpoint file /u01/app/ogg/10g/dirchk/EXTRA.cpe
Process file /u01/app/ogg/10g/dirpcs/EXTRA.pce
Stdout file /u01/app/ogg/10g/dirout/EXTRA.out
Error log /u01/app/ogg/10g/ggserr.log
GGSCI (grid11g.kr.oracle.com) 16> info extract pumpa, detail
EXTRACT PUMPA Last Started 2013-12-24 16:22 Status RUNNING
Checkpoint Lag 00:00:00 (updated 00:00:09 ago)
Log Read Checkpoint File ./dirdat/TA000000
2013-12-24 16:22:24.000000 RBA 23160
Target Extract Trails:
Remote Trail Name Seqno RBA Max MB
./dirdat/TB 0 23212 100
Extract Source Begin End
./dirdat/TA000000 * Initialized * 2013-12-24 16:22
./dirdat/TA000000 * Initialized * First Record
Current directory /u01/app/ogg/10g
Report file /u01/app/ogg/10g/dirrpt/PUMPA.rpt
Parameter file /u01/app/ogg/10g/dirprm/pumpa.prm
Checkpoint file /u01/app/ogg/10g/dirchk/PUMPA.cpe
Process file /u01/app/ogg/10g/dirpcs/PUMPA.pce
Stdout file /u01/app/ogg/10g/dirout/PUMPA.out
Error log /u01/app/ogg/10g/ggserr.log
GGSCI (grid11g.kr.oracle.com) 17> view report extra
***********************************************************************
Oracle GoldenGate Capture for Oracle
Version 11.1.1.1 OGGCORE_11.1.1_PLATFORMS_110421.2040
Linux, x64, 64bit (optimized), Oracle 10g on Apr 30 2011 18:21:22
Copyright (C) 1995, 2011, Oracle and/or its affiliates. All rights reserved.
Starting at 2013-12-24 16:22:06
***********************************************************************
Operating System Version:
Linux
Version #1 SMP Tue May 15 19:55:50 EDT 2012, Release 2.6.32-300.25.1.el5uek
Node: grid11g.kr.oracle.com
Machine: x86_64
soft limit hard limit
Address Space Size : unlimited unlimited
Heap Size : unlimited unlimited
File Size : unlimited unlimited
CPU Time : unlimited unlimited
Process id: 5077
Description:
***********************************************************************
** Running with the following parameters **
***********************************************************************
-- Extract Parameter File
EXTRACT EXTRA
USERID oggmgr, PASSWORD ******
TRANLOGOPTIONS OPENARCHIVEIMMEDIATE
THREADOPTIONS MAXCOMMITPROPAGATIONDELAY 60000 IOLATENCY 60000
TRANLOGOPTIONS EXCLUDEUSER OGGMGR, EXCLUDEUSERID 54
EXTTRAIL ./dirdat/TA
REPORTCOUNT EVERY 5 MINUTES, RATE
-- Use DDL parameter to configure support for all mapped DDL operations
DDL INCLUDE MAPPED
DDLOPTIONS ADDTRANDATA, REPORT
-- Table/Sequence List
TABLE SCOTT.*;
SEQUENCE SCOTT.*;
2013-12-24 16:22:08 INFO OGG-01635 BOUNDED RECOVERY: reset to initial or altered checkpoint.
Bounded Recovery Parameter:
Options = BRRESET
BRINTERVAL = 4HOURS
BRDIR = /u01/app/ogg/10g
CACHEMGR virtual memory values (may have been adjusted)
CACHEBUFFERSIZE: 64K
CACHESIZE: 8G
CACHEBUFFERSIZE (soft max): 4M
CACHEPAGEOUTSIZE (normal): 4M
PROCESS VM AVAIL FROM OS (min): 16G
CACHESIZEMAX (strict force to disk): 13.99G
Database Version:
Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bi
PL/SQL Release 10.2.0.5.0 - Production
CORE 10.2.0.5.0 Production
TNS for Linux: Version 10.2.0.5.0 - Production
NLSRTL Version 10.2.0.5.0 - Production
Database Language and Character Set:
NLS_LANG = "AMERICAN_AMERICA.AL32UTF8"
NLS_LANGUAGE = "AMERICAN"
NLS_TERRITORY = "AMERICA"
NLS_CHARACTERSET = "AL32UTF8"
2013-12-24 16:22:08 INFO OGG-01515 Positioning to begin time Dec 24, 2013 4:19:50 PM.
2013-12-24 16:22:09 INFO OGG-01516 Positioned to Sequence 21, RBA 16422416, Dec 24, 2013 4:19:50 PM.
2013-12-24 16:22:09 INFO OGG-01052 No recovery is required for target file
./dirdat/TA000000, at RBA 0 (file not opened).
2013-12-24 16:22:09 INFO OGG-01478 Output file ./dirdat/TA is using format RELEASE 10.4/11.1.
***********************************************************************
** Run Time Messages **
***********************************************************************
2013-12-24 16:22:09 INFO OGG-01517 Position of first record processed Sequence
21, RBA 16422416, SCN 0.227038, Dec 24, 2013 4:19:50 PM.
SEQUENCEWildcard resolved (entry SCOTT.*):
SEQUENCE SCOTT.TQ;
Resolving source sequence SCOTT.TQ.
TABLEWildcard resolved (entry SCOTT.*):
TABLE SCOTT.T;
2013-12-24 16:22:10 WARNING OGG-00869 No unique key is defined for table T.
All viable columns will be used to represent the key, but may not guarantee uniqueness.
KEYCOLS may be used to define the key.
Using the following key columns for source table SCOTT.T: ID, TXDATE.
TABLE resolved (entry OGGMGR.GGS_MARKER):
TABLE OGGMGR.GGS_MARKER;
Using the following key columns for source table OGGMGR.GGS_MARKER: SEQNO, FRAGMENTNO, OPTIME.
GGSCI (grid11g.kr.oracle.com) 18> view report pumpa
***********************************************************************
Oracle GoldenGate Capture for Oracle
Version 11.1.1.1 OGGCORE_11.1.1_PLATFORMS_110421.2040
Linux, x64, 64bit (optimized), Oracle 10g on Apr 30 2011 18:21:22
Copyright (C) 1995, 2011, Oracle and/or its affiliates. All rights reserved.
Starting at 2013-12-24 16:22:13
***********************************************************************
Operating System Version:
Linux
Version #1 SMP Tue May 15 19:55:50 EDT 2012, Release 2.6.32-300.25.1.el5uek
Node: grid11g.kr.oracle.com
Machine: x86_64
soft limit hard limit
Address Space Size : unlimited unlimited
Heap Size : unlimited unlimited
File Size : unlimited unlimited
CPU Time : unlimited unlimited
Process id: 5090
Description:
***********************************************************************
** Running with the following parameters **
***********************************************************************
-- Data Pump Parameter File
EXTRACT PUMPA
PASSTHRU
RMTHOST grid11g.kr.oracle.com, MGRPORT 5001
RMTTRAIL ./dirdat/TB
REPORTCOUNT EVERY 5 MINUTES, RATE
-- Table/Sequence List
TABLE SCOTT.*;
SEQUENCE SCOTT.*;
CACHEMGR virtual memory values (may have been adjusted)
CACHEBUFFERSIZE: 64K
CACHESIZE: 8G
CACHEBUFFERSIZE (soft max): 4M
CACHEPAGEOUTSIZE (normal): 4M
PROCESS VM AVAIL FROM OS (min): 16G
CACHESIZEMAX (strict force to disk): 13.99G
2013-12-24 16:22:18 INFO OGG-01226 Socket buffer size set to 27985 (flush size 27985).
2013-12-24 16:22:19 INFO OGG-01052 No recovery is required for target file
./dirdat/TB000000, at RBA 0 (file not opened).
2013-12-24 16:22:19 INFO OGG-01478 Output file ./dirdat/TB is using format RELEASE 10.4/11.1.
***********************************************************************
** Run Time Messages **
***********************************************************************
Opened trail file ./dirdat/TA000000 at 2013-12-24 16:22:19
SEQUENCEWildcard resolved (entry SCOTT.*):
SEQUENCE SCOTT.TQ;
PASSTHRU mapping resolved for source table SCOTT.TQ
TABLEWildcard resolved (entry SCOTT.*):
TABLE SCOTT.T;
PASSTHRU mapping resolved for source table SCOTT.T
GGSCI (grid11g.kr.oracle.com) 19> exit
Configure o ambiente para criação do banco de dados 10g no servidor de destino:
[oracle@grid11g SRCDB:~]# . .db10s
[oracle@grid11g SRCDB:~]# mkdir -p /u01/app/oracle/oradata/TGTDB
[oracle@grid11g SRCDB:~]# mkdir -p /u01/app/oracle/oradata/TGTDB/arch
[oracle@grid11g SRCDB:~]# mkdir -p /u01/app/oracle/admin/TGTDB/adump
[oracle@grid11g SRCDB:~]# mkdir -p /u01/app/oracle/admin/TGTDB/bdump
[oracle@grid11g SRCDB:~]# mkdir -p /u01/app/oracle/admin/TGTDB/udump
[oracle@grid11g SRCDB:~]# mkdir -p /u01/app/oracle/admin/TGTDB/cdump
[oracle@grid11g SRCDB:~]# cd /u01/app/oracle/product/10.2.0/db_1/dbs
[oracle@grid11g SRCDB:/u01/app/oracle/product/10.2.0/db_1/dbs]# sqlplus / as sysdba
SQL*Plus: Release 10.2.0.5.0 - Production on Tue Dec 24 16:24:51 2013
Copyright (c) 1982, 2010, Oracle. All Rights Reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> create pfile from spfile;
File created.
SQL> exit
Crie um arquivo de parâmetros de inicialização no banco de dados 10g de destino:
[oracle@grid11g SRCDB:/u01/app/oracle/product/10.2.0/db_1/dbs]# cp
initSRCDB.ora initTGTDB.ora
[oracle@grid11g SRCDB:/u01/app/oracle/product/10.2.0/db_1/dbs]# vi
initTGTDB.ora
[oracle@grid11g SRCDB:/u01/app/oracle/product/10.2.0/db_1/dbs]# cat
initTGTDB.ora
TGTDB.__db_cache_size=171966464
TGTDB.__java_pool_size=4194304
TGTDB.__large_pool_size=4194304
TGTDB.__shared_pool_size=88080384
TGTDB.__streams_pool_size=4194304
*.audit_file_dest='/u01/app/oracle/admin/TGTDB/adump'
*.background_dump_dest='/u01/app/oracle/admin/TGTDB/bdump'
*.compatible='10.2.0.5.0'
*.control_files='/u01/app/oracle/oradata/TGTDB/control01.ctl','/u01/app/oracle/oradata/TGTDB/control02.ctl',
'/u01/app/oracle/oradata/TGTDB/control03.ctl'
*.core_dump_dest='/u01/app/oracle/admin/TGTDB/cdump'
*.db_block_size=8192
*.db_domain=''
*.db_file_multiblock_read_count=16
*.db_name='TGTDB'
*.job_queue_processes=10
*.log_archive_dest='/u01/app/oracle/oradata/TGTDB/arch/'
*.open_cursors=300
*.pga_aggregate_target=92274688
*.processes=150
*.recyclebin='OFF'
*.remote_login_passwordfile='EXCLUSIVE'
*.sga_target=278921216
*.undo_management='AUTO'
*.undo_tablespace='UNDOTBS1'
*.user_dump_dest='/u01/app/oracle/admin/TGTDB/udump'
[oracle@grid11g SRCDB:/u01/app/oracle/product/10.2.0/db_1/dbs]#
Realize um Backup do banco de dados de origem (Oracle 10g):
[oracle@grid11g SRCDB:~]# sqlplus / as sysdba
SQL*Plus: Release 10.2.0.5.0 - Production on Tue Dec 24 16:27:30 2013
Copyright (c) 1982, 2010, Oracle. All Rights Reserved.
Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> alter system switch logfile;
System altered.
SQL> alter database begin backup;
Database altered.
SQL> host
[oracle@grid11g ~]$ cp /u01/app/oracle/oradata/SRCDB/system01.dbf /u01/app/oracle/oradata/TGTDB/system01.dbf
[oracle@grid11g ~]$ cp /u01/app/oracle/oradata/SRCDB/undotbs01.dbf /u01/app/oracle/oradata/TGTDB/undotbs01.dbf
[oracle@grid11g ~]$ cp /u01/app/oracle/oradata/SRCDB/sysaux01.dbf /u01/app/oracle/oradata/TGTDB/sysaux01.dbf
[oracle@grid11g ~]$ cp /u01/app/oracle/oradata/SRCDB/users01.dbf /u01/app/oracle/oradata/TGTDB/users01.dbf
[oracle@grid11g ~]$ exit
SQL> alter database end backup;
Database altered.
SQL> select current_scn from v$database;
CURRENT_SCN
--------------------
230362
Nota: É muito importante anotar este SCN.
SQL> alter system switch logfile;
System altered.
SQL> alter system switch logfile;
System altered.
SQL> !cp /u01/app/oracle/oradata/SRCDB/arch/* /u01/app/oracle/oradata/TGTDB/arch/.
SQL> exit
Inicialize o banco de dados em estado “NOMOUNT” e crie o “control file” no banco de dados 10g de destino:
[oracle@grid11g TGTDB:~]# sqlplus / as sysdba
SQL*Plus: Release 10.2.0.5.0 - Production on Tue Dec 24 16:30:35 2013
Copyright (c) 1982, 2010, Oracle. All Rights Reserved.
Connected to an idle instance.
SQL> STARTUP NOMOUNT
ORACLE instance started.
Total System Global Area 281018368 bytes
Fixed Size 2095672 bytes
Variable Size 100664776 bytes
Database Buffers 171966464 bytes
Redo Buffers 6291456 bytes
SQL> CREATE CONTROLFILE SET DATABASE "TGTDB" RESETLOGS FORCE LOGGING ARCHIVELOG
2 MAXLOGFILES 16
3 MAXLOGMEMBERS 3
4 MAXDATAFILES 100
5 MAXINSTANCES 8
6 MAXLOGHISTORY 292
7 LOGFILE
8 GROUP 1 '/u01/app/oracle/oradata/TGTDB/redo01.log' SIZE 100M,
9 GROUP 2 '/u01/app/oracle/oradata/TGTDB/redo02.log' SIZE 100M,
10 GROUP 3 '/u01/app/oracle/oradata/TGTDB/redo03.log' SIZE 100M
11 DATAFILE
12 '/u01/app/oracle/oradata/TGTDB/system01.dbf',
13 '/u01/app/oracle/oradata/TGTDB/undotbs01.dbf',
'/u01/app/oracle/oradata/TGTDB/sysaux01.dbf',
14 15 '/u01/app/oracle/oradata/TGTDB/users01.dbf'
16 CHARACTER SET AL32UTF8;
Control file created.
Inicialize o processo de recuperação no banco de dados 10g de destino:
SQL> recover database using backup controlfile until change 230362;
ORA-00279: change 230221 generated at 12/24/2013 16:27:43 needed for thread 1
ORA-00289: suggestion : /u01/app/oracle/oradata/TGTDB/arch/1_22_759613235.dbf
ORA-00280: change 230221 for thread 1 is in sequence #22
Specify log: {=suggested | filename | AUTO | CANCEL}
AUTO
Log applied.
Media recovery complete.
SQL> alter database open resetlogs;
Database altered.
SQL> ALTER TABLESPACE TEMP
ADD TEMPFILE '/u01/app/oracle/oradata/TGTDB/temp01.dbf' SIZE 300m;
Tablespace altered.
SQL> exit
Elimine o usuário “oggmgr” no banco de dados 10g de destino:
[oracle@grid11g TGTDB:/u01/app/ogg/10g]# sqlplus / as sysdba
SQL*Plus: Release 10.2.0.5.0 - Production on Tue Dec 24 16:33:05 2013
Copyright (c) 1982, 2010, Oracle. All Rights Reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> @ddl_disable
Trigger altered.
SQL> @ddl_remove
DDL replication removal script.
WARNING: this script removes all DDL replication objects and data.
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.
Enter GoldenGate schema name:oggmgr
Working, please wait ...
Spooling to file ddl_remove_spool.txt
Script complete.
SQL> drop user oggmgr cascade;
User dropped.
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> exit
Migre o banco de dados destino da versão 10.2.0.5.0 para a versão 12.1.0.1.0:
[oracle@grid11g TGTDB:~]# vi /etc/oratab
[oracle@grid11g TGTDB:~]# cat /etc/oratab
# This file is used by ORACLE utilities. It is created by root.sh
# and updated by the Database Configuration Assistant when creating a database.
# A colon, ':', is used as the field terminator. A new line terminates
# the entry. Lines beginning with a pound sign, '#', are comments.
# Entries are of the form:
# $ORACLE_SID:$ORACLE_HOME::
# The first and second fields are the system identifier and home
# directory of the database respectively. The third filed indicates
# to the dbstart utility that the database should , "Y", or should not,
# "N", be brought up at system boot time.
#
# Multiple entries with the same $ORACLE_SID are not allowed.
#
#SRCDB:/u01/app/oracle/product/10.2.0/db_1:N
TGTDB:/u01/app/oracle/product/10.2.0/db_1:N
Configure a replicação no banco de dados de destino (Oracle database 12c):
[oracle@grid11g TGTDB:/u01/app/ogg/12c]# sqlplus / as sysdba
SQL*Plus: Release 12.1.0.1.0 Production on Tue Dec 24 17:26:11 2013
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> create user oggmgr identified by oracle default tablespace users;
User created.
SQL> @role_setup
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:oggmgr 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 ,
connect, resource, dba to oggmgr; Grant succeeded.
SQL> exit
Crie uma tabela “checkpoint” no banco de dados de destino (Oracle database 12c)
[oracle@grid11g TGTDB:/u01/app/ogg/12c]# ./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 Sep 25 2013 02:33:54
Operating system character set identified as UTF-8.
Copyright (C) 1995, 2013, Oracle and/or its affiliates. All rights reserved.
GGSCI (grid11g.kr.oracle.com) 1> edit params ./GLOBALS
GGSCI (grid11g.kr.oracle.com) 2> view params ./GLOBALS
-- GoldenGate GLOBALS parameter file
CHECKPOINTTABLE oggmgr.ggschkpt
GGSCHEMA oggmgr
GGSCI (grid11g.kr.oracle.com) 3> exit
[oracle@grid11g TGTDB:/u01/app/ogg/12c]# ./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 Sep 25 2013 02:33:54
Operating system character set identified as UTF-8.
Copyright (C) 1995, 2013, Oracle and/or its affiliates. All rights reserved.
GGSCI (grid11g.kr.oracle.com) 1> dblogin userid oggmgr, password oracle
Successfully logged into database.
GGSCI (grid11g.kr.oracle.com) 2> add checkpointtable
No checkpoint table specified. Using GLOBALS specification (oggmgr.ggschkpt)...
Successfully created checkpoint table oggmgr.ggschkpt.
GGSCI (grid11g.kr.oracle.com) 3> add replicat repla, exttrail ./dirdat/TB
REPLICAT added.
GGSCI (grid11g.kr.oracle.com) 4> edit params repla
GGSCI (grid11g.kr.oracle.com) 5> view params repla
-- Change Delivery parameter file to apply
REPLICAT REPLA
USERID oggmgr, PASSWORD oracle
ASSUMETARGETDEFS
DISCARDFILE ./dirrpt/REPLA.DSC, APPEND
REPORTCOUNT EVERY 5 MINUTES, RATE
-- Configure DDL replication
DDL INCLUDE MAPPED
DDLOPTIONS REPORT
-- Table/Sequence List
MAP SCOTT.* , TARGET SCOTT.*;
GGSCI (grid11g.kr.oracle.com) 6> exit
[oracle@grid11g TGTDB:/u01/app/ogg/12c]# sqlplus / as sysdba
SQL*Plus: Release 12.1.0.1.0 Production on Tue Dec 24 17:29:14 2013
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> @sequence
Please enter the name of a schema for the GoldenGate database objects:
oggmgr
Setting schema name to OGGMGR
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> exit
[oracle@grid11g TGTDB:/u01/app/ogg/12c]# ./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 Sep 25 2013 02:33:54
Operating system character set identified as UTF-8.
Copyright (C) 1995, 2013, Oracle and/or its affiliates. All rights reserved.
GGSCI (grid11g.kr.oracle.com) 1> start replicat repla aftercsn 230362
Sending START request to MANAGER ...
REPLICAT REPLA starting
GGSCI (grid11g.kr.oracle.com) 2> info replicat repla
REPLICAT REPLA Last Started 2013-12-24 17:29 Status RUNNING
Checkpoint Lag 01:00:49 (updated 00:00:10 ago)
Process ID 11195
Log Read Checkpoint File ./dirdat/TB000000
2013-12-24 16:29:07.000335 RBA 78224
GGSCI (grid11g.kr.oracle.com) 3> lag replicat repla
Sending GETLAG request to REPLICAT REPLA ...
Last record lag 2,107 seconds.
GGSCI (grid11g.kr.oracle.com) 4> exit
[oracle@grid11g TGTDB:/u01/app/ogg/12c]# ./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 Sep 25 2013 02:33:54
Operating system character set identified as UTF-8.
Copyright (C) 1995, 2013, Oracle and/or its affiliates. All rights reserved.
GGSCI (grid11g.kr.oracle.com) 1>
GGSCI (grid11g.kr.oracle.com) 1> lag replicat repla
Sending GETLAG request to REPLICAT REPLA ...
Last record lag 7 seconds.
At EOF, no more records to process.
GGSCI (grid11g.kr.oracle.com) 2> exit
Verifique a sincronização e as mudanças entre o banco de dados de origem e o de destino:
Nota: Realize algumas transações e execute o “commit”
Verifique o banco de dados de destino (Oracle Database 12c):
Alex Zaballa, formado em Análise de Sistemas, é especialista em Banco de Dados Oracle com sólidos conhecimentos em Servidores de Aplicação e Sistemas Operacionais; trabalha com Oracle há 15 anos, é ORACLE ACE, certificado OCM Database 11G e conta com mais de 100 outras certificações em produtos da Oracle. Desde 2007 é funcionário da empresa Júpiter em Angola, alocado em um projeto no Ministério das Finanças. Alex também é fundador do Grupo de Usuários Oracle de Angola (GUOA) e membro do time OraWorld.
Yenugula Venkata Ravikumar é um DBA com mais de 15 anos de experiencia com Oracle e em ambientes de alta disponibilidade (RAC, Data Guard, dentre outros), tuning e desempenho, migrações, backup e recover, Oracle Exadata X2 e X3, é Expert em sistemasoperacionais tais como como AIX, HP-UX e Linux. Já participou como conferencista de Oracle pela India, ode mora atualmente. Obteve o titulo de "Oracle Certified Master (OCM 10g)" em 2009.
Nassyam Basha é um DBA, OCM 11g, com experiência em tecnologias como Oracle Data Guard, RMAN, RAC. Ele já fez mais de 90 configurações do Data Guard em diferentes plataformas, de não-RAC RAC e vice-versa. Ele fez migrações bem-sucedidas com "switchovers" e "failovers" a vários bancos de dados de produção crítica. Ele participa ativamente de fóruns Oracle utilizando o usuário "CKPT" e ganhou mais de 10.000 pontos (nível guru). Publica regularmente artigos em seu blog www.oracle-ckpt.com e é co-autor do livro "Guia de Administração do Oracle Data Guard 11gR2".
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.