Tuesday, August 29, 2017

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

at
Labels:

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)

Oracle row type vs PostgreSQL record

The Oracle row type concept can be accomplished with the record type in PostgreSQL. Example: Oracle: CURSOR travel_cur IS  SELECT  c.travel_...