High level steps upgrade from 11g to 12c database:

1)    Check network between source and target.

2)    Prepare goldengate Software.

3)    Setup extract and datapump for source site.

4)    Setup replict for target side.

5)    Start extract.

6)    Export and import initial load.

7)    Start the replicat.

This hands-on lab will demonstrate how to migrate the ‘TEST’ schema from Oracle database 11.2.0.4 to Oracle database 12.2.0.1.0 pdb with zero down time.

1)    Check network between source and target. For example, ping 10.182.242.176

2)    Prepare GoldenGate Software, we are using DIPC remote agent in this tutorial

Unzip it, the structure like following

./dicloud

|---- gghome11g

|---- gghome

|---- oci11g

|---- oci

…

The gghome11g is for the 11g source database, and the gghome is for the 12c target database.

Setup the tnsnames for source database and target database.

$mkdir –p ./dicloud/oci11g/network/admin/

$mkdir –p ./dicloud/oci/network/admin/

Prepare the tnsname.ora file.

src =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = 10.182.242.176)(PORT = 1521))

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = orcl)

)

)

tar =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = 10.182.211.15)(PORT = 1521))

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = tar)

)

)

The environment parameters for ogg source like following:

export ORACLE_HOME=/home/oracle/ogg/dicloud/oci11g

export LD_LIBRARY_PATH=$ORACLE_HOME:/lib:/usr/lib

export TNS_ADMIN='/home/oracle/ogg/dicloud/oci11g/network/admin/'

The environment parameters for ogg target like following:

export ORACLE_HOME=/home/oracle/ogg/dicloud/oci

export LD_LIBRARY_PATH=$ORACLE_HOME:/lib:/usr/lib

export TNS_ADMIN='/home/oracle/ogg/dicloud/oci/network/admin/'

After setting above environment parameters, you can run the ./ggsci command under the gghome or gghome11g.

For Source

For Target

Setting for Source Database:

SQL>shutdown immediate;

SQL>startup mount;

SQL>alter database archivelog;

SQL>alter database open;

SQL>alter system switch logfile;

SQL>archive log list;

SQL>SELECT supplemental_log_data_min, force_logging FROM v$database;

SQL>alter database add supplemental log data;

SQL>alter database force logging;

SQL>SELECT supplemental_log_data_min, force_logging FROM v$database;

SQL> alter system set enable_goldengate_replication = true scope=both;

SQL>create user ogg identified by ogg;

SQL>GRANT DBA to ogg;

SQL>BEGIN

dbms_goldengate_auth.grant_admin_privilege (

grantee => 'ogg',

privilege_type => 'CAPTURE',

grant_select_privileges => TRUE

);

END;

/

--create test user

SQL>create user test identified by test default tablespace users quota unlimited on users;

SQL>grant create table, create session, execute on dbms_lock to test;

OGG setting for Source:

GGSCI> view param extsrc

extract extsrc

userid ogg@src,password ogg

exttrail ./dirdat/es

table TEST.*;

GGSCI> add extract extsrc, integrated tranlog, begin now

GGSCI > add exttrail ./dirdat/es, extract extsrc

GGSCI> dblogin userid ogg@src, password ogg

GGSCI> register extract extsrc database

GGSCI> add schematrandata TEST

GGSCI> info trandata TEST.*

GGSCI> view param pumsrc

extract pumsrc

passthru

rmthost 10.182.209.212,mgrport 7910

rmttrail ./dirdat/rs

table TEST.*;

GGSCI> add extract pumsrc, exttrailsource ./dirdat/es

GGSCI> add rmttrail ./dirdat/rs, extract pumsrc

Prepare the target PDB:

sqlplus / as sysdba

SQL>create pluggable database tar admin user adm identified by adm

file_name_convert=('/u01/app/oracle/oradata/dbaas/pdbseed', '/u01/app/oracle/oradata/dbaas/tar');

SQL>alter session set container=tar;

SQL>create user ogg identified by ogg;

SQL>GRANT DBA to ogg;

SQL>create bigfile tablespace users datafile '/u01/app/oracle/oradata/dbaas/tar/users.dbf' size 100m autoextend on maxsize 5G;

SQL>BEGIN

dbms_goldengate_auth.grant_admin_privilege

(

grantee => 'ogg',

privilege_type => 'APPLY',

grant_select_privileges => TRUE

);

END;

/

OGG setting for the target:

GGSCI > view param repsrc

replicat repsrc

userid ogg@tar, password ogg

discardfile repsrc.dsc, append

DBOPTIONS ENABLE_INSTANTIATION_FILTERING

map TEST.*, target TEST.*;

GGSCI > dblogin userid ogg@tar, password ogg

GGSCI > add replicat repsrc, integrated ,exttrail ./dirdat/rs

For the source database without Patch 17030189. You can apply follow workaround now. Or later back to this step if the capture abended with the errors mentioned the patch 17030189.

There is a script "prvtlmpg.plb" under gghome11g directory. Need to run this script in the source database.

sqlplus / as sysdba

SQL>@prvtlmpg.plb

Open a new terminal to run the sample application in source database. This PL/SQL block simulate a running application.

SQL>conn test/test

SQL>create table tmp_check (s integer);

SQL>declare

i pls_integer:=0;

begin

loop

insert into tmp_check values(i);

commit;

i:=i+1;

dbms_lock.sleep(2);

end loop;

end;

/

--Please note that the above PL/SQL is running forever. You can press ‘Ctrl’ + ‘c’ from your keyboard to stop it.

Start the OGG capture.

GGSCI>start EXTRACT EXTSRC

GGSCI>start EXTRACT PUMSRC

Check the status.

GGSCI>info all

Create the directory for exporting.

sqlplus / as sysdba

SQL>create directory ogg_dir as ‘/home/oracle/migration’;

Export Schema ‘TEST’ from the source database.

expdp \' / as sysdba\' directory=ogg_dir dumpfile=test_%U.dmp schemas=test statistics=none parallel=2

Move the dump files from source to target.

scp *.dmp :

Create directory for importing.

sqlplus / as sysdba

SQL> alter session set container=tar;

SQL> create directory ogg_dir as ‘/home/oracle/migration’;

Import the schema ‘TEST’ to the target PDB.

impdp system/oracle@tar directory=ogg_dir dumpfile=test_%U.dmp parallel=2

After the above import successfully. Start the replicate to sync the source and target data.

GGSCI>start REPLICAT REPSRC

Stop the running PL/SQL application.

Check the records in Source database.

SQL>conn test/test

SQL>select * from tmp_check order by 1;

Check the records in Target database. The results should be the same as the results from the Source database.

SQL>conn test/test@tar

SQL>select * from tmp_check order by 1;

Summary

With OGG, it is easy to achieve zero down time migration + upgrade from Oracle 11g to Oracle 12c.We use DBOPTIONS ENABLE_INSTANTIATION_FILTERING - Tells Replicat to filter records per table based on Data Pump system table data, no SCN record or used in the data initialization. However, in real case, you need to consider more according to your specific environment.