Oracle 12c Golden Gate setup

Oracle 12c Golden Gate setup

Install Oracle Golden Gate Software

Using SQL Plus, add minimal supplemental logging at the database level on Source and Target

SQL> alter database add supplemental log data;

SQL> select supplemental_log_data_min from v$database;

–output should be YES

SQL> alter system switch logfile;

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

Create the Golden Gate user and tablespace at source and target

sqlplus / as sysdba

Create tablespace ggs_data datafile ‘<name.dbf>’ size 512m;

create user ggs identified by <password> default tablespace ggs_data ;

grant create session to ggs;

grant connect, resource, dba to ggs;

grant alter system to ggs;

grant lock any table to ggs;

grant alter any table to ggs;

grant select any table to ggs;

grant create any table to ggs;

grant insert any table to ggs;

grant update any table to ggs;

grant delete any table to ggs;

grant drop any table to ggs;

exec dbms_goldengate_auth.grant_admin_privilege(‘GGS’);

Execute Golden Gate scripts at source and Target

Execute the golden gate configuration scripts with sys as sysdba

The following scripts are available in Oracle Golden Gate home directory

SQL> @marker_setup.sql

Enter Oracle GoldenGate schema name: GGS

SQL> @ddl_setup.sql

Enter Oracle GoldenGate schema name: GGS

SQL> @role_setup.sql

Enter Oracle GoldenGate schema name: GGS

SQL> GRANT GGS_GGSUSER_ROLE TO GGS;

SQL> @ddl_enable.sql

Create database login userid alias

GGSCI> dblogin userid GGS password <password>

GGSCI> add credentialstore

GGSCI> alter credentialstore add user GGS password <passwd> alias <alias name>

Add checkpoint table at source and target

GGSCI> add checkpointtable GGS.CHECKPOINTTABLE

Enable trandata for the application user

GGSCI>dblogin useridalias <useridalias name>

GGSCI> add trandata <schema_name>.*

GGSCI> info trandata <schema_name>.*

Setup parameter files at first database

Set up parameter files and starting the Manager, Extract, Replicat and Pump process on source and target machines

GGSCI> edit params mgr

Enter following parameters for manager process

PORT 7809

useridalias <useridalias name>

LAGINFOMINUTES 30

LAGCRITICALMINUTES 60

LAGREPORTMINUTES 30

PURGEOLDEXTRACTS  /goldengate/<database name>/dirdat/<ab>*, USECHECKPOINTS, MINKEEPDAYS 30

PURGEOLDEXTRACTS  /goldengate/<database name>/dirdat/<bc>*, USECHECKPOINTS, MINKEEPDAYS 30

PURGEMARKERHISTORY MINKEEPDAYS 7, MAXKEEPDAYS 10, FREQUENCYMINUTES 30

PURGEDDLHISTORY MINKEEPDAYS 3, MAXKEEPDAYS 5, FREQUENCYMINUTES 30

DOWNCRITICAL

DOWNREPORTMINUTES 30

AUTORESTART EXTRACT *, RETRIES 3, WAITMINUTES 5, RESETMINUTES 60

AUTORESTART REPLICAT *, RETRIES 3, WAITMINUTES 5, RESETMINUTES 60

* The parameter PURGEOLDEXTRACTS need to modify according to the trail file name format

Extract process:

GGSCI> ADD EXTRACT <extract name>, TRANLOG, BEGIN NOW

GGSCI> ADD EXTTRAIL </goldengate/<database name>/dirdat/<ab>, EXTRACT <extract name>, MEGABYTES 100

GGSCI> edit params <extract name>

extract <extract name>
SETENV (ORACLE_HOME=<oracle home>)
SETENV (ORACLE_SID=<Database Name>)
USERIDALIAS <userid alias name>

DiscardFile /goldengate/<database name>/dirrpt/<extract name>.dis, Append

EXTTRAIL /goldengate/<database name>/dirdat/<ab>

TRANLOGOPTIONS EXCLUDEUSER GGS

DDL INCLUDE ALL

DDLOPTIONS GETREPLICATES

DDLOPTIONS GETAPPLOPS

SEQUENCE < application username >.*;

TABLE < application username >.*;

Datapump extract process:

GGSCI> ADD EXTRACT <datapump extract name>, EXTTRAILSOURCE /goldengate/<database name>/dirdat/<ab>

GGSCI> ADD RMTTRAIL /goldengate/<target database name>/dirdat/<ab>, EXTRACT <datapump extract name>

GGSCI> edit params <datapump extract name>

extract <datapump extract name>
SETENV (ORACLE_HOME=<oracle home>)
SETENV (ORACLE_SID=<database name>)
USERIDALIAS <userid alias name>
PASSTHRU
TRANLOGOPTIONS EXCLUDEUSER GGS
RMTHOST <TARGET HOSTNAME>, MGRPORT <Manager port number at target host>
rmttrail /goldengate/<database name>/dirdat/<ab>
SEQUENCE <application username>.*;
TABLE <application username>.*;

Note: If you are setting up uni-directional replication then replicat need to configure at target database and ignore the setup the extract processes at second database step

Replicat process:

GGSCI>        ADD REPLICAT <replicat name>, EXTTRAIL /goldengate/<database name>/dirdat/bc

GGSCI> edit params <replicat name>

REPLICAT <replicat name>

SETENV (ORACLE_HOME=<oracle home>)

SETENV (ORACLE_SID=<database name>)

USERIDALIAS <userid alias name>

DISCARDFILE /goldengate/<database name>/dirrpt/<replicat name>.dsc, APPEND

DISCARDROLLOVER AT 00:00

DDLOPTIONS MAPSESSIONSCHEMA <source schema name> TARGET <target schema name>

ASSUMETARGETDEFS

HANDLECOLLISIONS

FILTERDUPS

TRANSACTIONTIMEOUT 10 MINUTES

MAP <source schema name>.*, TARGET <target schema name>.*;

Setup parameter files at second database

Note: This steps is required for bi-directional replication only

Set up parameter files and starting the Manager, Extract, Replicat and Pump process on source and target machines

Manager process:

GGSCI> edit params mgr

Enter following parameters for manager process

PORT 7809

useridalias <userid alias name>

LAGINFOMINUTES 30

LAGCRITICALMINUTES 60

LAGREPORTMINUTES 30

PURGEOLDEXTRACTS  /goldengate/<database name>/dirdat/<ab>*, USECHECKPOINTS, MINKEEPDAYS 30

PURGEOLDEXTRACTS  /goldengate/<database name>/dirdat/<bc>*, USECHECKPOINTS, MINKEEPDAYS 30

PURGEMARKERHISTORY MINKEEPDAYS 7, MAXKEEPDAYS 10, FREQUENCYMINUTES 30

PURGEDDLHISTORY MINKEEPDAYS 3, MAXKEEPDAYS 5, FREQUENCYMINUTES 30

DOWNCRITICAL

DOWNREPORTMINUTES 30

AUTORESTART EXTRACT *, RETRIES 3, WAITMINUTES 5, RESETMINUTES 60

AUTORESTART REPLICAT *, RETRIES 3, WAITMINUTES 5, RESETMINUTES 60

* The parameter PURGEOLDEXTRACTS need to modify according to the trail file name format

Extract process:

GGSCI> ADD EXTRACT <extract name>, TRANLOG, BEGIN NOW

GGSCI> ADD EXTTRAIL /goldengate/<database name>/dirdat/<ab>, EXTRACT <extract name>, MEGABYTES 100

GGSCI> edit params <extract name>

extract <extract name>
SETENV (ORACLE_HOME=<oracle home>)
SETENV (ORACLE_SID=<Database Name>)
USERIDALIAS <userid alias name>

DiscardFile /goldengate/<database name>/dirrpt/<extract name>.dis, Append

EXTTRAIL /goldengate/<database name>/dirdat/<bc>

TRANLOGOPTIONS EXCLUDEUSER GGS

DDL INCLUDE ALL

DDLOPTIONS GETREPLICATES

DDLOPTIONS GETAPPLOPS

SEQUENCE <application username>.*;

TABLE <application username>.*;

Datapump extract process:

GGSCI> ADD EXTRACT <datapump extract name>, EXTTRAILSOURCE /goldengate/<database name>/dirdat/<ab>

GGSCI> ADD RMTTRAIL /goldengate/<target database name>/dirdat/<ab>, EXTRACT <datapump extract name>

GGSCI> edit params <datapump extract name>

extract <datapump extract name>
SETENV (ORACLE_HOME=<oracle home>)
SETENV (ORACLE_SID=<database name>)
USERIDALIAS <userid alias name>
PASSTHRU
TRANLOGOPTIONS EXCLUDEUSER GGS
RMTHOST <TARGET HOSTNAME>, MGRPORT <Manager port number at target host>
rmttrail /goldengate/<database name>/dirdat/<ab>
SEQUENCE <application username>.*;
TABLE <application username>.*;

Replicat process:

GGSCI>  ADD REPLICAT <replicat name>, EXTTRAIL /goldengate/<database name>/dirdat/<bc>

GGSCI> edit params <replicat name>

REPLICAT <replicat name>

SETENV (ORACLE_HOME=<oracle home>)

SETENV (ORACLE_SID=<database name>)

USERIDALIAS <userid alias name>

DISCARDFILE /goldengate/<database name>/dirrpt/<replicat name>.dsc, APPEND

DISCARDROLLOVER AT 00:00

DDLOPTIONS MAPSESSIONSCHEMA <source schema name> TARGET <target schema name>

ASSUMETARGETDEFS

HANDLECOLLISIONS

FILTERDUPS

TRANSACTIONTIMEOUT 10 MINUTES

MAP <source schema name>.*, TARGET <target schema name>.*;

Start the Manager, extract and data pump processes at first database

Note: Do not start replicat process

At first database:

Get the database SCN to start the extract process

SQL> select current_scn from v$database;

GGSCI> start mgr

GGSCI> alter extract <extract name>, SCN <database scn from above command>

GGSCI> start extract <extract name>

GGSCI> start extract <datapump extract name>

GGSCI (slabora1) 1> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING

EXTRACT     RUNNING     <extract name>    00:00:00      00:00:06

EXTRACT     RUNNING     <datapump extract name>    00:00:00      00:00:04

Start the Manager, extract and data pump processes at second database

Note: Do not start replicat process

At second database:

Get the database SCN to start the extract process

SQL> select current_scn from v$database;

GGSCI> start mgr

GGSCI> alter extract <extract name>, SCN <database scn from above command>

GGSCI> start extract <extract name>

GGSCI> start extract <datapump extract name>

GGSCI (slabora1) 1> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING

EXTRACT     RUNNING     <extract name>    00:00:00      00:00:06

EXTRACT     RUNNING     <datapump extract name>    00:00:00      00:00:04

Data migration from first database to second database:

Export the data from source database/schema using flashback scn option

expdp directory=<directory name> dumpfile=<dumpfile name>.dmp logfile=<logfile name>.log schemas=<application schema name> flashback_scn=<database scn used to start the extract process at first database>

Import the data at target database

impdp directory=<directory name> dumpfile=<dumpfile name>.dmp logfile=<logfile name>.log schemas=<application schema name>

Data migration from second database to first database:

Note: This steps is required in case any data need to copy from second database to first database

Export the data from source database/schema using flashback scn option

expdp directory=<directory name> dumpfile=<dumpfile name>.dmp logfile=<logfile name>.log schemas=<application schema name> flashback_scn=<database scn used to start the extract process at first database>

Import the data at target database

impdp directory=<directory name> dumpfile=<dumpfile name>.dmp logfile=<logfile name>.log schemas=<application schema name>

Start the replicat processat both sides

At first database:

GGSCI> start replicat <Replicat name> AFTERCSN < database scn used to start the extract process at first database>

At second database:

GGSCI> start replicat <Replicat name> AFTERCSN < database scn used to start the extract process at second database>

Make sure running all process.

GGSCI 1> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER   RUNNING

EXTRACT     RUNNING     <extract process>     00:00:00      00:00:03

EXTRACT     RUNNING     <datapump extract process>     00:00:00      00:00:08

REPLICAT    RUNNING     <replicat process>     00:00:00      00:00:06

The status should be RUNNING

Ref: https://govindgalla.wordpress.com

Configure DDL replication for GoldenGate

[oracle@node1 ggs]$ ggsci

Oracle GoldenGate Command Interpreter for Oracle
Version 11.2.1.0.1 OGGCORE_11.2.1.0.1_PLATFORMS_120423.0230_FBO
Linux, x64, 64bit (optimized), Oracle 11g on Apr 23 2012 08:32:14

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

GGSCI (node1.localdomain) 1> edit params ./GLOBALS
GGSCHEMA ggadmin

SQL> alter system set recyclebin=off deferred ;

System altered.


SQL> grant restricted session to ggadmin ;

Grant succeeded.

[oracle@node1 ggs]$ pwd
/data01/oracle/ggs
[oracle@node1 ggs]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Sat Oct 10 03:34:49 2015

Copyright (c) 1982, 2009, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> @marker_setup.sql

Marker setup script

You will be prompted for the name of a schema for the Oracle GoldenGate database objects.
NOTE: The schema must be created prior to running this script.
NOTE: Stop all DDL replication before starting this installation.

Enter Oracle GoldenGate schema name:GGADMIN


Marker setup table script complete, running verification script...
Please enter the name of a schema for the GoldenGate database objects:
Setting schema name to GGADMIN

MARKER TABLE
-------------------------------
OK

MARKER SEQUENCE
-------------------------------
OK

Script complete.


SQL> @ddl_setup.sql

Oracle 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 Oracle 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 Oracle GoldenGate schema name:GGADMIN

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 GGADMIN as a Oracle GoldenGate schema name.

Working, please wait ...

DDL replication setup script complete, running verification script...
Please enter the name of a schema for the GoldenGate database objects:
Setting schema name to GGADMIN

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 IGNORE TABLE
-----------------------------------
OK

DDL IGNORE LOG TABLE
-----------------------------------
OK

DDLAUX PACKAGE STATUS:

Line/pos   Error
---------- -----------------------------------------------------------------
No errors  No errors

DDLAUX PACKAGE BODY STATUS:

Line/pos   Error
---------- -----------------------------------------------------------------
No errors  No errors

SYS.DDLCTXINFO PACKAGE STATUS:

Line/pos   Error
---------- -----------------------------------------------------------------
No errors  No errors

SYS.DDLCTXINFO 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
------------------------------------------------------------------------------------------------------------------------
/data01/app/oracle/diag/rdbms/ggsrc/ggsrc/trace/ggs_ddl_trace.log

Analyzing installation status...


STATUS OF DDL REPLICATION
------------------------------------------------------------------------------------------------------------------------
SUCCESSFUL installation of DDL Replication software components

Script complete.
SQL>

SQL> @role_setup.sql

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:GGADMIN
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 to HR ;

Grant succeeded.

SQL> revoke GGS_GGSUSER_ROLE from ggadmin ;

Revoke succeeded.

SQL> @ddl_enable.sql

Trigger altered.

Oracle is now ready to replicate DDL operation along with DML operations . You can now configure the extract process with DDLOPTIONS paramter to  control DDL replication for that extract process .

RMAN & Golden Gate

RMAN & Golden Gate

RMAN can be used to instantiate a database which will be kept in sync later using Golden Gate.

Prerequisites:
– Source DB : source - (everything in ASM, this adds one more challenge to configure Golden Gate)
– Target DB : target - on the same host we are going to create the target database, and setup the golden gate replication from a particular SCN

The steps:

1. Create you source database using dbca  (make sure you have ASM instance and listener on 1521 port ready).

I don’t want to put any comment on this one, as it should be very straighforward. Your spfile should look something similar to this one

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29

source.__db_cache_size=1191182336 source.__java_pool_size=16777216 source.__large_pool_size=16777216 source.__shared_pool_size=352321536 source.__streams_pool_size=16777216 *.audit_file_dest='/u01/app/oracle/admin/source/adump' *.background_dump_dest='/u01/app/oracle/admin/source/bdump' *.compatible='10.2.0.5.0' *.control_files='+DATA_DG/source/controlfile/current...','+DATA_DG/source/controlfile/current...' *.core_dump_dest='/u01/app/oracle/admin/source/cdump' *.db_block_sq20ize=8192 *.db_create_file_dest='+DATA_DG' *.db_domain='' *.db_file_multiblock_read_count=16 *.db_name='source' *.db_recovery_file_dest='+DATA_DG' *.db_recovery_file_dest_size=104857600000 *.dispatchers='(PROTOCOL=TCP) (SERVICE=sourceXDB)' *.job_queue_processes=10 *.log_archive_dest_1='LOCATION=+DATA_DG/' *.log_archive_format='%t_%s_%r.dbf' *.open_cursors=300 *.pga_aggregate_target=3424649216 *.processes=150 *.remote_login_passwordfile='EXCLUSIVE' *.sga_target=1610612736 *.undo_management='AUTO' *.undo_tablespace='UNDOTBS1' *.user_dump_dest='/u01/app/oracle/admin/source/udump'

2. Download the Golden Gate sofwtare (Version 11.1.1.1 for Oracle 10g on Linux x86-64) from OTN

http://www.oracle.com/technetwork/middleware/goldengate/downloads/index.html

This is the fastest way to get the software however – as you can see at the bottom of the page -, I’d recommend using the edelivery.oracle.com site, as you can download the differnt Golden Gate products for different Databases only from here. Yes, you will have to use different software if you want to setup replication between different database products (eg. MySQL vs. Oracle)

3. Let us keep it simple and put the GG sofware in the oracle users home directory (~/source – for the source database; ~/target – for the target database)

1 2 3 4 5 6 7

oracle@vm-ora2:~$ mkdir source oracle@vm-ora2:~$ cp fbo_ggs_Linux_x64_ora10g_64bit.zip source/ oracle@vm-ora2:~$ cd source/ oracle@vm-ora2:~/source$ unzip fbo_ggs_Linux_x64_ora10g_64bit.zip oracle@vm-ora2:~/source$ tar xf fbo_ggs_Linux_x64_ora10g_64bit.tar oracle@vm-ora2:~/source$ cd .. oracle@vm-ora2:~$ cp -r source target

4.  Configure the source database
In this step we are going to setup the schema which will be used by Golden Gate to connect to our source database (and also later on after duplicating this database to target, it will be used on the target side as well)

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37

oracle@vm-ora2:~$ . oraenv ORACLE_SID = [oracle] ? source oracle@vm-ora2:~$ sqlplus -x / as sysdba SQL> create tablespace ggs_data datafile size 200m autoextend on; Tablespace created. SQL> create user ggs_owner identified by ggs_owner default tablespace ggs_data temporary tablespace temp; User created. SQL> grant connect,resource to ggs_owner; Grant succeeded. SQL> grant select any dictionary, select any table to ggs_owner; Grant succeeded. SQL> grant create table to ggs_owner; Grant succeeded. SQL> grant flashback any table to ggs_owner; Grant succeeded. SQL> grant execute on dbms_flashback to ggs_owner; Grant succeeded. SQL> grant execute on utl_file to ggs_owner; Grant succeeded. SQL> ALTER DATABASE ADD SUPPLEMENTAL LOG DATA (ALL) COLUMNS;

The following ones are documented in MOS note: 1330577.1

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15

SQL> exec dbms_streams_auth.grant_admin_privilege('ggs_owner'); PL/SQL procedure successfully completed. SQL> grant insert on system.logmnr_restart_ckpt$ to ggs_owner; Grant succeeded. SQL> grant update on sys.streams$_capture_process to ggs_owner; Grant succeeded. SQL> grant become user to ggs_owner; Grant succeeded.

5. Create a simple demo schema in the source database (the column “text” is clob just to try if replication of log columns is working as well)

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15

SQL> grant create session, create table to demo identified by demo; SQL> alter user demo quota unlimited on users; SQL> conn demo/demo Connected. SQL> create table mytable (id number primary key, name varchar2(50), text clob); Table created. SQL> insert into mytable values (1, 'Istvan Stahl', 'istvanstahl.worldpress.com'); 1 row created. SQL> commit; Commit complete.

6. Backup your source database

1 2 3 4 5

oracle@vm-ora2:~$ . oraenv ORACLE_SID = [oracle] ? source oracle@vm-ora2:~$ rman target / RMAN> CONFIGURE CONTROLFILE AUTOBACKUP ON; RMAN> backup database plus archivelog;

7. Configure source GG

First of all create a directory for the target’s trail files (we will need this during the source GG setup):

1	oracle@vm-ora2:~$ mkdir -p /home/oracle/target/dirdat/rt

Now, configure the GG source site (manager and the extract process)

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39

oracle@vm-ora2:~$ cd source oracle@vm-ora2:~/source$ ggsci GGSCI (vm-ora2) 1> create subdirs  GGSCI (vm-ora2) 2> edit params mgr  PORT 7777  USERID ggs_owner, PASSWORD ggs_owner  PURGEOLDEXTRACTS /home/oracle/source/dirdat/ex, USECHECKPOINTS  GGSCI (vm-ora2) 3> start mgr Manager started. GGSCI (vm-ora2) 4> info all  Program Status Group Lag Time Since Chkpt  MANAGER RUNNING GGSCI (vm-ora2) 5> EDIT PARAMS ./GLOBALS GGSCHEMA GGS_OWNER CHECKPOINTTABLE GGS_OWNER.CHKPTAB GGSCI (vm-ora2) 6> DBLOGIN USERID ggs_owner, PASSWORD ggs_owner Successfully logged into database. GGSCI (vm-ora2) 7> ADD CHECKPOINTTABLE GGS_OWNER.CHKPTAB Successfully created checkpoint table GGS_OWNER.CHKPTAB. GGSCI (vm-ora2) 8> ADD EXTRACT ext1, TRANLOG, BEGIN NOW 2011-07-08 16:42:07  INFO    OGG-01749  Successfully registered EXTRACT EXT1 to start managing log retention at SCN 401678. EXTRACT added. GGSCI (vm-ora2) 9> ADD RMTTRAIL /home/oracle/target/dirdat/rt, EXTRACT ext1 RMTTRAIL added. GGSCI (vm-ora2) 10> EDIT PARAMS ext1 EXTRACT ext1 USERID ggs_owner, PASSWORD ggs_owner RMTHOST vm-ora2, MGRPORT 8888 RMTTRAIL /home/oracle/target/dirdat/rt TABLE demo.mytable; TRANLOGOPTIONS ASMUSER sys@asm, ASMPASSWORD *****

In the last line we had to specify the connection details to the ASM instance, without this GG is not able to mine the logs.

8. Now let’s create the target database using RMAN

First backup the newly created archive logs of the source database, and create a pfile.

1 2 3 4 5 6 7

oracle@vm-ora2:~$ . oraenv ORACLE_SID = [oracle] ? source oracle@vm-ora2:~$ rman target / RMAN> sql 'alter system switch logfile'; RMAN> backup archivelog all; RMAN> sql 'create pfile from spfile';

add an entry for the target database to the /etc/oratab file:

1	oracle@vm-ora2:~$ echo "target:/u01/app/oracle/product/10.2.0/db_1:N" >> /etc/oratab

Create the password file:

1 2 3 4 5

oracle@vm-ora2:~$ . oraenv ORACLE_SID =  ? target oracle@vm-ora2:~$ cd $ORACLE_HOME/dbs oracle@vm-ora2:/u01/app/oracle/product/10.2.0/db_1/dbs$ orapwd file=orapwtarget password=oracle entries=5

Edit the pfile, so that it can be used by the target database and create the required directories:

1 2 3 4 5 6 7

oracle@vm-ora2:/u01/app/oracle/product/10.2.0/db_1/dbs$ cp initsource.ora inittarget.ora oracle@vm-ora2:/u01/app/oracle/product/10.2.0/db_1/dbs$ sed -i "s/source/target/g" inittarget.ora oracle@vm-ora2:/u01/app/oracle/product/10.2.0/db_1/dbs$ sed -i "/control_files/d" inittarget.ora oracle@vm-ora2:/u01/app/oracle/product/10.2.0/db_1/dbs$ mkdir -p /u01/app/oracle/admin/target/adump oracle@vm-ora2:/u01/app/oracle/product/10.2.0/db_1/dbs$ mkdir -p /u01/app/oracle/admin/target/bdump oracle@vm-ora2:/u01/app/oracle/product/10.2.0/db_1/dbs$ mkdir -p /u01/app/oracle/admin/target/cdump oracle@vm-ora2:/u01/app/oracle/product/10.2.0/db_1/dbs$ mkdir -p /u01/app/oracle/admin/target/udump

Finally start the database using spfile:

1 2 3 4 5 6 7 8 9 10 11

oracle@vm-ora2:/u01/app/oracle/product/10.2.0/db_1/dbs$ sqlplus -x / as sysdba SQL> create spfile from pfile; File created. SQL> startup nomount; ORACLE instance started. Total System Global Area 1610612736 bytes Fixed Size                  2096632 bytes Variable Size             385876488 bytes Database Buffers         1207959552 bytes Redo Buffers               14680064 bytes

Duplicate the source database to target (you can use the SCN number what you have got when you created the extract process from Golden Gate, in our case 401678)

1 2 3 4

oracle@vm-ora2:~$ rman target sys/oracle@source auxiliary / connected to target database: SOURCE (DBID=2874333303) connected to auxiliary database: TARGET (not mounted) RMAN> duplicate target database to "target" until scn 401678;

Now, grant write access for the ggs_owner on our demo table:

1 2 3 4 5

oracle@vm-ora2:~$ sqlplus -x / as sysdba SQL> grant all on demo.mytable to ggs_owner; Grant succeeded.

9. Configure GG for the target database

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24

oracle@vm-ora2:~$ . oraenv ORACLE_SID = [oracle] ? target oracle@vm-ora2:~$ cd target/ oracle@vm-ora2:~/target$ ggsci GGSCI (vm-ora2) 1> create subdirs GGSCI (vm-ora2) 2> edit params mgr port 8888 USERID ggs_owner, PASSWORD ggs_owner GGSCI (vm-ora2) 3> start mgr Manager started. GGSCI (vm-ora2) 4> info all Program Status Group Lag Time Since Chkpt MANAGER RUNNING GGSCI (vm-ora2) 5> ADD REPLICAT rep1, EXTTRAIL /home/oracle/target/dirdat/rt, CHECKPOINTTABLE GGS_OWNER.CHKPTAB REPLICAT added. GGSCI (vm-ora2) 6> edit params rep1 REPLICAT rep1 ASSUMETARGETDEFS USERID ggs_owner, PASSWORD ggs_owner MAP demo.mytable, TARGET demo.mytable;

10. Now start the source extratct and the target replicat
Start the extract:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15

oracle@vm-ora2:~/target$ . oraenv ORACLE_SID = [target] ? source oracle@vm-ora2:~/target$ cd ../source oracle@vm-ora2:~/source$ ggsci GGSCI (vm-ora2) 1> start extract ext1 Sending START request to MANAGER ... EXTRACT EXT1 starting GGSCI (vm-ora2) 2> info all Program     Status      Group       Lag           Time Since Chkpt MANAGER     RUNNING EXTRACT     RUNNING     EXT1        00:01:01      00:00:06

Look at the log if any problem:

1	GGSCI (vm-ora2) 3> view ggsevt

and start the replicat process after the appropriate scn:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15

oracle@vm-ora2:~/source$ . oraenv ORACLE_SID =  ? target oracle@vm-ora2:~/source$ cd ../target oracle@vm-ora2:~/target$ ggsci GGSCI (vm-ora2) 3> start replicat rep1, aftercsn 401678 Sending START request to MANAGER ... REPLICAT REP1 starting GGSCI (vm-ora2) 4> info all Program     Status      Group       Lag           Time Since Chkpt MANAGER     RUNNING REPLICAT    RUNNING     REP1        00:00:00      00:00:07

11. Do some testing

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22

oracle@vm-ora2:~$ . oraenv ORACLE_SID =  ? source oracle@vm-ora2:~$ sqlplus -x demo/demo SQL> select * from mytable; SQL> insert into mytable values (2,'Tom Kyte','asktom.oracle.com'); 1 row created. SQL> commit; Commit complete. oracle@vm-ora2:~$ . oraenv ORACLE_SID =  ? target oracle@vm-ora2:~$ sqlplus -x demo/demo SQL> select * from mytable;         ID NAME                                               TEXT ---------- -------------------------------------------------- --------------------------------------------------------------------------------          1 Istvan Stahl                                       istvanstahl.worldpress.com          2 Tom Kyte                                           asktom.oracle.com

Ref : https://istvanstahl.wordpress.com