STEP 0 ————— Hardware and O/S configuration
STEP 1 ————— Database Pre requisites
STEP 2 ————— GG Software Installation
STEP 3 ————— Preparing source database for replication
STEP 3a ————— GG DDL Support Replication
STEP 4 ————— Create Manager process @ Source
STEP 5 ————— Create Extract process @ Source
STEP 6 ————— Create Checkpoint table @ Target
STEP 7 ————— Create Replicate process @ Target
STEP 8 ————— Initial Data load (EXP/IMP)
STEP 9 ————— Start Extract
STEP 10 ————— Start Replicat
STEP 11————— TEST CASES
| |||||||||||||||||||||||||
STEP
|
TASK
| ||||||||||||||||||||||||
STEP 0:-
Hardware and O/S configuration
|
Now, let’s take a look at the Oracle database details which we will be using for our GOLDEN GATE configuration.( I am using VMWARE nodes)
Download the oracle Golden Gate software from below URL and copy to both source and target servers
we need to download the required software from the Oracle E-Delivery web site
| ||||||||||||||||||||||||
Step 1:–
Database Pre requisites
|
Need to be carried out at both source and target
SQL> create tablespace ggs_data
datafile ‘/oradata/<DBNAME>ggs_data01.dbf’ size 200m;
SQL> create user ggs_owner identified by ggs_owner
default tablespace ggs_data temporary tablespace temp;
grant connect, resource to ggs_owner;
grant select any dictionary, 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 create any table to ggs_owner;
grant insert any table to ggs_owner;
grant update any table to ggs_owner;
grant delete any table to ggs_owner;
grant drop any table to ggs_owner;
INIT_ORA change
| ||||||||||||||||||||||||
Step 2:–
GG Software Installation
|
Need to be carried out at both source and targetNote:
1 binary for SOURCE
1 binary for TARGET REPLICAT
SOURCE
n mkdir /oradata/gg/
TARGET
n mkdir /oradata/gg/
set environment variables on both SOURCE and TARGET before starting the installation on all the three locations
$mkdir /oradata/gg
$unzip V18157-01.zip
Archive: V18157-01.zip
inflating: ggs_redhatAS40_x64_ora10g_64bit_v10.4.0.19_002.tar
$ tar -xf ggs_redhatAS40_x64_ora10g_64bit_v10.4.0.19_002.tar
export LD_LIBRARY_PATH=$ORACLE_HOME/lib:/oradata/gg
$ . /ggsci
— YOU WILL GET GG COMMAND PROMPT
GGSCI (hostname) 1> CREATE SUBDIRS
— Issue the following command to exit GGSCI.
GGSCI (hostname) 1> EXIT
| ||||||||||||||||||||||||
STEP 3:-
Preparing source database for replication
|
Need to be carried out at source Switch the database to archivelog mode:shutdown immediate startup mount
alter database archivelog;
alter database open;
Enable minimal supplemental logging:
SQL> alter database add supplemental log data;
Prepare the database to support ddl replication.
SQL> alter system set recyclebin=off scope=spfile;
n bounce the database
| ||||||||||||||||||||||||
Step3a :-
Run scripts for creating all necessary objects for support DDL replication
|
Need to be carried out at source
Note – run the scripts as SYSDBA
SQL> @/oradata/gg/marker_setup.sql
Marker setup 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: GGS_OWNER
Marker setup table script complete, running verification script…
Please enter the name of a schema for the GoldenGate database objects:
Setting schema name to GGS_OWNER
MARKER TABLE
——————————-
OK
MARKER SEQUENCE
——————————-
OK
Script complete.
SQL> @/oradata/gg/ddl_setup.sql
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 GoldenGate database objects.
NOTE: The schema must be created prior to running this script.
NOTE: On Oracle 10g and up, system recycle bin must be disabled.
NOTE: Stop all DDL replication before starting this installation.
Enter GoldenGate schema name: GGS_OWNER
You will be prompted for the mode of installation.
To install or reinstall DDL replication, enter INITIALSETUP
To upgrade DDL replication, enter NORMAL
Enter mode of installation:INITIALSETUP
Working, please wait …
Spooling to file ddl_setup_spool.txt
Using GGS_OWNER as a GoldenGate schema name, INITIALSETUP as a mode of installation.
Working, please wait …
RECYCLEBIN must be empty.
This installation will purge RECYCLEBIN for all users.
To proceed, enter yes. To stop installation, enter no.
Enter yes or no:yes
DDL replication setup script complete, running verification script…
Please enter the name of a schema for the GoldenGate database objects:
Setting schema name to GGS_OWNER
DDLORA_GETTABLESPACESIZE STATUS:
Line/pos Error
———- —————————————————————–
No errors No errors
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 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
——————————————————————————–
/u01/app/oracle/diag/rdbms/gavin/gavin/trace/ggs_ddl_trace.log
Analyzing installation status…
STATUS OF DDL REPLICATION
——————————————————————————–
SUCCESSFUL installation of DDL Replication software components
Script complete.
SQL>
SQL> @/oradata/gg/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:GGS_OWNER
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
where is the user assigned to the GoldenGate processes.
SQL> grant ggs_ggsuser_role to ggs_owner;
Grant succeeded.
SQL> @/oradata/gg/ddl_enable
Trigger altered.
SQL> @/oradata/gg/ddl_pin GGS_OWNER
PL/SQL procedure successfully completed.
PL/SQL procedure successfully completed.
PL/SQL procedure successfully completed.
Enable additional logging at the table level
Note- We had earlier enabled additional supplemental logging at the database level. Using the ADD TRANDATA command we now enable it at even the table level as this is required by Golden Gate for DDL support.
GGSCI (soruce hostname) 5> DBLOGIN USERID ggs_owner, PASSWORD ggs_owner
SQL> select ‘add trandata ‘||owner||’.'||object_name||’;’ from dba_objects where owner=’RPD_PRC’ and object_type=’TABLE’;
GGSCI(source hostname) 6> add trandata <owner>.<tablename>
| ||||||||||||||||||||||||
STEP 4:-
Create and start manager on the source and the destination.
|
NEED TO BE CARRIED OUT AT SOURCE
[oracle@DBSOURCE gg]$ ./ggsci Oracle GoldenGate Command Interpreter for Oracle
Version 11.1.1.0.0 Build 078
Linux, x64, 64bit (optimized), Oracle 10 on Jul 28 2010 13:21:11
Copyright (C) 1995, 2010, Oracle and/or its affiliates. All rights reserved.
GGSCI (DBSOURCE) 1> DBLOGIN USERID ggs_owner, PASSWORD ggs_owner
Successfully logged into database.
GGSCI (DBSOURCE) 44> info all
Program Status Group Lag Time Since Chkpt
MANAGER STOPPED
GGSCI (DBSOURCE) 45> edit params mgr
PORT 7809
USERID ggs_owner, PASSWORD ggs_owner
PURGEOLDEXTRACTS /oradata/gg/dirdat/ex, USECHECKPOINTS
GGSCI (DBSOURCE) 46> info all
Program Status Group Lag Time Since Chkpt
MANAGER STOPPED
GGSCI (DBSOURCE) 47> dblogin USERID ggs_owner, PASSWORD ggs_owner
Successfully logged into database.
GGSCI (DBSOURCE) 48> start manager
Manager started.
| ||||||||||||||||||||||||
STEP 5 :- Create the extract group
|
NEED TO BE CARRIED OUT AT SOURCE
GGSCI (DBSOURCE) 49> add extract ext1, tranlog, begin nowEXTRACT added.
GGSCI (DBSOURCE) 50> add exttrail /oradata/gg/dirdat/lt, extract ext1
EXTTRAIL added.
GGSCI (DBSOURCE) 51> edit params ext1
Add the following lines to the new parameter file for our extract:
–extract group–
extract ext1
–connection to database–
userid ggs_owner, password ggs_owner
–hostname and port for trail–
rmthost 172.168.10.108, mgrport 7810
–path and name for trail–
rmttrail /oradata/gg/dirdat/lt
–DDL support
DDL INCLUDE ALL
ddl include mapped objname SCOTT.*;
–DML
table SCOTT.*;
table SCOTT.EMP;
table SCOTT.DEPT;
table SCOTT.SALARY;
:wq
| ||||||||||||||||||||||||
STEP 6:-
CREATE CHECKPOINT TABLE
|
NEED TO BE CARRIED OUT AT TARGET
[oracle@DBTARGET gg]$ ./ggsci add checkpoint table to the destination database
GGSCI (DBTARGET) 1> edit params ./GLOBAL
and put following lines to the global parameter file:
GGSCHEMA ggs_owner
CHECKPOINTTABLE ggs_owner. Checkpoint
GGSCI (DBTARGET) 2> dblogin userid ggs_owner password password
Successfully logged into database.
GGSCI (DBTARGET) 3> add checkpointtable ggs_owner.checkpoint
Successfully created checkpoint table ggs_owner.CHECKPOINT.
| ||||||||||||||||||||||||
STEP 7:-
CREATE REPLICAT
|
NEED TO BE CARRIED OUT AT TARGET
GGSCI (DBTARGET) 4>add replicat rep1, exttrail /oradata/gg/dirdat/lt,checkpointtable ggs_owner.checkpointGGSCI (DBTARGET) 4> EDIT PARAMS rep1
edit params rep1
–target database login –
userid ggs_owner, password ggs_owner
–file for dicarded transaction –
–discardfile /oradata/gg/discard/rep1_discard.txt, append, megabytes 10
–ddl support
DDL INCLUDE ALL
DDLERROR DEFAULT IGNORE RETRYOP
–Specify table mapping —
MAP SCOTT.*, TARGET SCOTT.*;
MAP SCOTT.EMP, TARGET SCOTT.EMP;
MAP SCOTT.DEPT, TARGET SCOTT.DEPT;
MAP SCOTT.SALARY, TARGET SCOTT.SALARY;
| ||||||||||||||||||||||||
STEP 8:-
INITIAL DATALOAD
|
EXPORT: @ SOURCE
$ expdp directory=db_dir dumpfile=schema_gg.dmp logfile=schema_gg.log schemas=scott Scp from SOURCE and TARGET
$scp –p schema_gg.dmp 172.168.10.108:/oradata
IMPORT: @ TARGET
$impdp directory=db_dir dumpfile=schema_gg.dmp logfile=schema_imp_gg.log schemas=SCOTT
| ||||||||||||||||||||||||
STEP 9:-
START EXTRACT
|
NEED TO BE CARRIED OUT AT SOURCE
GGSCI (DBSOURCE) 45> start EXT1
Sending START request to MANAGER …
EXTRACT EXT2 starting
| ||||||||||||||||||||||||
STEP 10:-
START REPLICAT
|
NEED TO BE CARRIED OUT AT TARGET
GGSCI (DBTARGET) 10> start replicat rep1
Sending START request to MANAGER …
REPLICAT REP1 starting
| ||||||||||||||||||||||||
STEP 11:-
TEST CASES
|
CASE 1: CREATE TABLE IN THE SOURCE DATABASE
SOURCE:create table SCOTT.Employee(
ID VARCHAR2(4 BYTE) ,
First_Name VARCHAR2(10 BYTE),
Last_Name VARCHAR2(10 BYTE),
Start_Date DATE,
End_Date DATE,
Salary Number(8,2),
City VARCHAR2(10 BYTE),
Description VARCHAR2(15 BYTE)
);
SELECT * FROM SCOTT.EMPLOYEE;
NO ROWS SELECTED
TARGET:
SELECT * FROM SCOTT.EMPLOYEE;
NO ROWS SELECTED
Case 2:CREATE TABLE AND INSERT DATA INTO IT
SOURCE:
Create table test_gg_1(Id number2(10),Username varchar2(20));
Insert into test_gg_1 values(1, ‘USER1’);
Insert into test_gg_1 values(2,’USER2’);
Insert into test_gg_1 values(3,’USER3’);
Insert into test_gg_1 values(4,’USER4’);
Insert into test_gg_1 values(5,’USER5’);
COMMIT;
SELECT * FROM SCOTT.TEST_GG_1;
TARGET :
SELECT * FROM SCOTT.TEST_GG_1;
CASE3: UPDATE THE RECORD
SOURCE:
Update scott.test_gg_1 set username=’USERDBA’ where id=1;
Commit;
TARGET:
Select * from scott.test_gg_1 where id=1
n you will find the updated record in the target database
CASE4: DELETE THE RECORD
SOURCE :
Delete from scott.test_gg_1 where id=4;
Commit;
TARGET:
Select * from scott.test_gg_1 where id=4;
n you will not find the deleted record in the target database
CASE 5: TRUNCAT THE RECORDS
SOURCE:
Truncate table scott.test_gg_1;
Commit;
Select * from scott.test_gg_1;
TARGET :
Select * from scott.test_gg_1;
n since the records are truncated in the source you will not find any records in the target.
CASE 6: CREATE A TRIGGER
SOURCE:
create or replace trigger SCOTT.emp_biu
BEFORE INSERT OR UPDATE
of salary on employee for each row
declare
v_error VARCHAR2(2000);
begin
if :new.salary > 10000
then
v_error:=:old.first_name||’ cannot have that much!’;
raise_application_error(-20999,v_error);
end if;
end;
/
SELECT OBJECT_NAME,OBJECT_TYPE,OWNER FROM DBA_OBJECTS WHERE OBJECT_NAME=’EMP_BIU’;
TARGET:
SELECT OBJECT_NAME,OBJECT_TYPE,OWNER FROM DBA_OBJECTS WHERE OBJECT_NAME=’EMP_BIU’;
CASE 7: create table with primary Key
If you create any table with constrains, Golden gate will not replicated it automatically. We need to perform below steps:
| ||||||||||||||||||||||||
Reference:
https://gssdba.wordpress.com/2011/07/
0 comments:
Post a Comment