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 <target_ip>:<target directory>

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;