Skip to main content

Oracle 12c Golden Gate Integrated Configuration

Enable minimal supplemental logging:
ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;
ALTER DATABASE FORCE LOGGING;
Configure source MGR
GGSCI > edit param mgr
PORT 7809
DynamicPortList 20000-20099
PurgeOldExtracts ./dirdat/*, UseCheckPoints, MinKeepHours 2
Autostart Extract E*
AUTORESTART Extract *, WaitMinutes 1, Retries 3    
Configure Target MGR
GGSCI >  edit param mgr
Port 7909
DynamicPortList 20100-20199
PurgeOldExtracts ./dirdat/pe*, UseCheckPoints, MinKeepHours 2
Autostart Replicat R*
AUTORESTART Replicat *, WaitMinutes 1, Retries 3
Configure Wallet at Source, so you don't need to provide password in
GGSCI>  Create Wallet
Created wallet at location 'dirwlt'.
Opened wallet at location 'dirwlt'.
GGSCI> Add CredentialStore                                     
Credential store created in ./dircrd/.
GGSCI> Alter CredentialStore Add User <User>@<ServiceName> Password <pwd> Alias <alias>
Credential store in ./dircrd/ altered.
GGSCI> Info CredentialStore
GGSCI >  DBLogin UserIDAlias <alias>                           
Copy the wallet and credential files from Source to Target
Make sure you can connect to target database using alias
Enable supplemental logging
Oracle database must be set to log the table key values whenever it logs a rows change, so that they are available to Oracle GoldenGate in Redo. By default, the database logs only those column values that change. This is required so Oracle GoldenGate can locate the correct row on the target for update and delete operations.
GGSCI > Add TranData <SourceSchema>.*  ALLCOLS
GGSCI > Info TranData <SourceSchema>.*

Configure Extract


GGSCI > edit param <ExtractName>
Extract <ExtractName>
SETENV (ORACLE_SID='<OracleSID>')
UserIdAlias <Aliase>
TranlogOptions IntegratedParams (max_sga_size 256)
Exttrail ./dirdat/in
LOGALLSUPCOLS
UPDATERECORDFORMAT COMPACT

Table <SourceSchema>.*;  

Parameters LOGALLSUPCOLS and UPDATERECORDFORMAT. The Integrated Replicat, requires the source extract parameter file to contain these new parameters introduced in 12c.

LOGALLSUPCOLS causes Extract to automatically includes in the trail record the before image of all supplementally logged columns for UPDATE and DELETE operations.
When two records are generated for an update to a single row, it incurs additional disk I/O and processing for both Extract and Replicat. If supplemental logging is enabled on all columns, the unmodified columns may be repeated in both the the before and after records. The overall size of the trail is larger, as well. This overhead is reduced by using UPDATERECORDFORMAT COMPACT.

Register Integrated Extract and create the Extract group and local Extract trail file

GGSCI > dblogin useridalias ogg_user
GGSCI > register extract <ExtractName> database
Extract <ExtractName> successfully registered with database at SCN <No>.
GGSCI > add extract <ExtractName>, integrated tranlog, begin now
GGSCI > add exttrail ./dirdat/in, extract <ExtractName>, megabytes 10 
Megabytes 10 is optional. The default is 100 Megabytes.

Create the secondary Extract (data pump) parameter file.
GGSCI > Edit Param <DP_Name>                                                  
Extract  <DP_Name>
SETENV (ORACLE_SID='<OracleSID>')
UserIdAlias <Alias>
rmthost localhost, mgrport 7909
rmttrail ./dirdat/pn
table <SchemaName>.*;

Create the data pump group and the remote Extract trail file.
GGSCI > add extract <DP_Name>, exttrailsource ./dirdat/in
GGSCI > add rmttrail ./dirdat/pn, extract <DP_Name>
 Start Extract
GGSCI > start Extract <ExtractName>
GGSCI > start extract <DP_Name>

Configuring Integrated Replicat
GGSCI >Edit Param <RepName>                                                           
Replicat <RepName>
SETENV(ORACLE_SID='<OracleSID>')
DBOPTIONS INTEGRATEDPARAMS(parallelism 7)
AssumeTargetDefs
DiscardFile ./dirrpt/rpdw.dsc, Purge
UserIdAlias <Alias>
Map <SourceSchema>.*, target <TargetSchema>.*;

DBOPTIONS INTEGRATEDPARAMS(parallelism 6) for Integrated Replicat, minimum number of parallel apply processes will be 7.

Add the Replicat <RepName>, connecting it to the Exttrail ./dirdat/pn

GGSCI > DBlogin UserIdAlias <Alias>
GGSCI > Add Replicat <RepName> Integrated  exttrail ./dirdat/pn                       
REPLICAT (Integrated) added.
GGSCI > Start Replicat <RepName>

Note:- Query DBA_GOLDENGATE_INBOUND view for detail

Ref: https://docs.oracle.com/goldengate/1212/gg-winux/GIORA/config_capt_integrated.htm#GIORA916
Ref: http://www.morganslibrary.org/pres/lad15/GOUG/guatemala15_goldengate.pdf

Popular posts from this blog

Linux vs Windows, Find out Linux or Windows is best fit for requirement

When users are having question about whether Linux or Windows is best for them, and they need helps to make decision whether to use Linux or Windows. Here we are going to explore Linux v/s Windows for several factor such as when to learn, career opportunity, hardware cost, software cost, license cost, clone cost, protection against virus, ease of use, flexibility etc. so the user can make a decision about to use Windows or Linux Training.

When to Learn: -
Kids start using windows when they go to the pre-school at the age of 3 to 5 year to play the education game. Some kids start using windows in middle school for homework. Windows operating system becomes part of our life.
While most of the time person starts learning Linux when it is required by the circumstances such as office, where they work running business application on open source software. Software engineers are learning Linux during their college education. Some people are learning Linux if they want to work in the cloud compu…

Oralce Pluggable Database

Pluggable database administration:-

Open all pluggable databases:
Connect to container database using oraenv
$. oraenv=DB_Name
$sqlplus "/as sysdba"
SQL> alter pluggable database all open;
Get pluggable database status:
SQL> select con_id, dbid, name, open_mode from v$pdbs;
Switch to Puggable database from container :
SQL> alter session set container = <Puggable_DB>;
Get status of Pluggable database
SELECT v.name, v.open_mode, NVL(v.restricted, 'n/a') "RESTRICTED", d.status
FROM v$pdbs v, dba_pdbs d WHERE v.guid = d.guid ORDER BY v.create_scn;
Get file location for pluggable database:
SQL>select file_name from dba_data_files;
Create table space for pluggable datatbase:
SQL> alter session set container=<Pluggable_DB>;
Session altered.
SQL> CREATE TABLESPACE TB_Name LOGGING DATAFILE '/<Path>/<File_Name>.dbf' SIZE 1024M REUSE AUTOEXTEND ON NEXT 8192K MAXSIZE UNLIMITED EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M;
Set new t…

Oracle Golden Gate Basic Classic

Create environment
01. Source & target - Create GG tablespace.
02. Source & target - Create the GoldenGate Schema Owner
03. Grant privilege including DBA to GG schema owner
04. Add schema owner to global parameter file ./GLOBALS
05. Execute role set up script to create role GGS_GGSUSER_ROLE
06. Grant role GGS_GGSUSER_ROLE to GG user at both Source and target
Configure GG  Extract
01. Source and Target - Configure Manager Parameters
$ ggsci
GGSCI 1> EDIT PARAMS MGR
PORT 7809
DYNAMICPORTLIST 7810-7820
02. Source, create parameter file for Extract - ex1,
EXTRACT ex1
USERID <id>, PASSWORD <pwd>
EXTTRAIL /home/oracle/goldengate/dirdat/ex
TABLE <Schema>.*;
03. Source, configure Data Pump Parameters
EXTRACT dp1
USERID <id>, PASSWORD <pwd>
RMTHOST <hostname>, MGRPORT 7809
RMTTRAIL /home/oracle/goldengate/dirdat/rt
TABLE <schema>.*;
Target - Create check point table
$ ggsci
GGSCI 1> DBLOGIN USERID <id>, PASSWORD <pwd>
GGSCI 2>…