1.
Source:
host 1
CBD: cdb141
PDB: MCRMB
Target:
host 2
CBD: cdb161
PDB: MCHUB
2.
Pre Check
2.1 Steps on both Source&Target
2.1.1 Install GG binaries on both Source and Target
2.1.2 Create GGS Useer on both Source and target
CREATE USER "GGS_OWNER" IDENTIFIED BY password DEFAULT TABLESPACE "USERS" TEMPORARY TABLESPACE "TEMP" PROFILE "XXX"
2.1.3 Grant required priviliges to GGS user on both source and target databases.
on CDB:
grant connect,resource to GGS_OWNER container=all;
grant dba to GGS_OWNER container=all;
exec dbms_goldengate_auth.grant_admin_privilige('GGS_OWNER','capture',container=>'all');
grant create session to GGS_OWNER container=all;
grant alter session to GGS_OWNER container=all;
grant set container to GG_OWNER container=all;
select privilege from dba_sys_privs where grantee = 'GGS_OWNER';
alter user GGS_OWNER quota unlimited on users container=all;
grant unlimited tablespace to GGS_OWNER container=all;
on PDB:
grant connect, resource, unlimited tablespace to GGS_OWNER;
grant select any dictory, select any table to GGS_OWNER;
grant create table to GGS_OWNER;
grant flashback any table to GGS_OWNER;
grant execute on dbms_flashback to GGS_OWNER;
grant execute on utl_file to GGS_OWNER;
grant dba to GGS_OWNER;
grant insert on system.logmnr_restart_ckpt￥ to GGS_OWNER;
grant update on sys.streams$_caputue_process to GGS_OWNER;
grant become user to GGS_OWNER;
exec dbms_streams_auth.grant_admin_prvilege ('GGS_OWNER');
exec dbms_goldengate_auth.grant_admin_privilege('GGS_OWNER');
2.1.4 Enable force logging and enable goldengate replication on Source
select force_logging, supplemental_log_data_min FROM v$database;
alter database force logging;
show parameter enable_goldengate_replication
alter system set enable_goldengate_replication=true scope=both;
2.2.1 Verify Golden Gate installed-SOURCE
Logon to server host1:
. ~/DB
ORACLE_HOME=/u01/app/oracle/product/12.2.0.1/db_1
export ORACLE_SID=cdb141
cd /acfsdata1/mcrmbop_ogg
./ggsci
GGSCI > info all
2.2.2 Verify Gloden Gate installed-Target
loggon to server host2
. ~/DB
ORACLE_HOME=/u01/app/oracle/product/12.2.0.1/db_1
export ORACLE_SID=cdb141
cd /acfsdata1/mcrhubp_ogg
./ggsci
GGSCI > info all
3.SOURCE
Setup Data replication source from ORACLE MCRMB database.
3.1 Manager Process Configuration – Setup GG Wallet
mgr.prm
PORT 7811
Create Wallet, credentialstore and User Alias.
GGSCI > create wallet
GGSCI > add credentialstore
GGSCI > alter credentialstore add user GGS_OWNER@CDB141 password XXX alias CBDB141
GGSCI > Info CredentialStore
3.2 Setup Marker, Roles and sequences etc.
Login to Source DB and execute the below scripts to setup DDL Replication
Go to GG Home directory and execute below scripts
cd /acfs/ogghome
alter session set container=MCRMB;
@/acfs/ogghome/marker_setup.sql
@/acfs/ogghome/role_setup.sql
GRANT GGS_GGSUSER_ROLE TO GGS_OWNER;
@?/rdbms/admin/dbmspool.sql;
@/acfs/ogghome/sequence.sql;
3.3 Extract Process Configuration
Add extract process and register it in Source database
Go to GG Home directory and execute below scripts
cd /acfs/ogghome
./ggsci
GGSCI> dblogin USERIDALIAS MCRMB
GGSCI> register extract EPORDH01 database container(MCRMB)
GGSCI> ADD EXTRACT EPORDH01, INTEGRATED TRANLOG, BEGIN NOW
GGSCI> ADD EXTTRAIL /acfs/ogghome/dirdat/EPORDH01/EX, EXTRACT EPORDH01
3.4 Prepare initial Load export tables at the Source
Initial Load, export the tables from the source.
Login to the database and get the current using
col TRANSACTION_DBUSER for a30
select nvl((select max(username) from gv￥session s, gv$transaction t where t.ses_addr = s.saddr and t.inst_id =s.inst_id), 'Get this SCN for Export') transaction_dbuser, (select to_char(current_scn) from v￥database) scn from dual;
Export the source tables from the above SCN[Eg.Export file added to Appendix without PASSWORD reference]
expdp parfile=/***.par
4.SOURCE:Maintenance Steps
4.1 Start/Stop steps
Starting GG:
Enter the Golden Gate Command interface. To enter it, execute the following from the /acf/ogghome/ location;
./ggsci
Execute the following commands:
GGSCI> start manager
GGSCI> start extract EPORDH01
To exit GGSCI, tyoe quit and press ENTER.
To exit UNIX Systems Services, type exit and press ENTER twice.
During the starup of GG Processes, using the ‘info all’ command to determine process status is crucial. Successful starup of each process in the commands listed ablove must occur before going on to the next command.
Stopping OGG:
GGSCI> stop extract EPORDH01
At this point you need to wait for the Extract processes to complete shutdown.
4.2 Monitoring Steps
GGSCI> info manager
Manager is running(OP port: xxxxxx. process ID: 172***)
To get the status of the Extract processes on the Source, use
GGSCI> status extract EPORDH01
EXTRACT EPORDH01: RUNNING
GGSCI> info EPORDH01
4.3 Troubleshooting Steps
To troubleshoot the extract process, please follow below steps
Get the status of the manager process
GGSCI> info mgr
Get the status of the extract processes
GGSCI> info EPORDH01
If we are having issue with particular extract process, check the process report to get more details using:
GGSCI> view report EPORDH01
Also, check teh goldengate log file
more ggserr.log
5. TARGET: Initial Setup
Setup Data replication target for Oracle Database environment
5.1 Manager Process Configuration
mgr.prm
PORT 7820
5.2 Replicat Process Configuration
Step1: Copy the dumpfiles and import the tables.
Import the tables to the target database.
impdp parfile=**.par
Step 2: Add the control Columns on the target.
Add Control columns using below SQL
select 'alter table BILLING_OWNER.' || table_name || ' add (RECORD_STATUS_CD CHAR(1), INSERT_USER VARCHAR2(10), INSERT_PROCESS VARCHAR2(5), ACTUAL_INSERT_TIMESTAMP TIMESTAMP, GG_INSERT_TIMESTAMP TIMESTAMP, UPDATE_USER VARCHAR2(10), UPDATE_PROCESS CHAR(5), ACTUAL_UPDATE_TIMESTAMP TIMESTAMP, GG_UPDATE_TIMESTAMP TIMESTAMP);' from dba_Tables where owner = 'BILLING_OWNER';
5.3 Replicat Process Configuration
Note: In this replication we are using a Replicat process with names as PRORDH
GO to GG HOME of the target
GGSCI> ADD replicat RPORDH01 EXTTRAIL /ogghome/dirdat/EPORDH/EX, CHECKPOINTTABLE GGS_OWNER.GGS_CHECKPOINT
GGSCI> add replicat RPORDH01 exttrail /ogghome/dirdat/EXPORDH/EX, checkpointtable GGS_OWNER.GGS_CHECKPOINT
start replicat RPORDH01 aftercsn
start replicat RPORDH02 aftercsn
6.4 Adding additional tables to the thread – steps
On Source:
Below are the steps on the source side to add a new table to the existing configuration.
1.Enable supplemental logging on the table.
alter table source.table add supplemental log data(all) columns;
2.Add trandata for the new table.
GGSCI> ADD TRANDATA source.tablename
3.Add the table to EXTRACT and Pump Process.
GGSCI> EDIT params Extract_Name
table source.tablename
4.Restart Extract and Pump processes.
GGSCI> Stop extract Extract_Name
GGSCI> Start extract Extract_Name
GGSCI> info all
5.Capture current SCN
select current_scn from v$database;
6.Export backup of the new table using the above SCN.
On Target:
Below are the steps to add new table to the replicat on target
1.Import the table to the target schema BILLING_OWNER.
2.Add control columns to the table
select 'alter table BILLING_OWNER.' || table_name || ' add (RECORD_STATUS_CD CHAR(1), INSERT_USER VARCHAR2(10), INSERT_PROCESS VARCHAR2(5), ACTUAL_INSERT_TIMESTAMP TIMESTAMP, GG_INSERT_TIMESTAMP TIMESTAMP, UPDATE_USER VARCHAR2(10), UPDATE_PROCESS CHAR(5), ACTUAL_UPDATE_TIMESTAMP TIMESTAMP, GG_UPDATE_TIMESTAMP TIMESTAMP);' from dba_Tables where owner = 'BILLING_OWNER';
3.Add the table to the Replicat
Edit params Replicat_name
Map source.tablename, target target.tablename(with filter for tansaction greater than the above CSN)
4.Restart the Replicat
GGSCI> Stop the replicat Replicat_name
GGSCI> start the replicat Replicat_name
GGSCI> info all
5.Verify the data sync between the Source and Target tables
6.Once the row count matches, remove the filter from the replicat param file.
7.Restart the Replicat
GGSCI> Stop the replicat Replicat_name
GGSCI> start the replicat Replicat_name
GGSCI> info all