Golden gate daily hits
[oracle@ggsrv1 ogg]$ echo $GG_HOME
/u01/app/oracle/product/ogg
[oracle@ggsrv1 ogg]$ ggsci
Oracle GoldenGate Command Interpreter for Oracle
Version 12.2.0.1.1 OGGCORE_12.2.0.1.0_PLATFORMS_151211.1401_FBO
Linux, x64, 64bit (optimized), Oracle 12c on Dec 12 2015 02:56:48
Operating system character set identified as UTF-8.
Copyright (C) 1995, 2015, Oracle and/or its affiliates. All rights reserved.
GGSCI (ggsrv1.localdomain) 1>
GGSCI (ggsrv1.localdomain) 1> h
GGSCI Command History
1: h
GGSCI (ggsrv1.localdomain) 2> history
GGSCI Command History
1: h
2: history
GGSCI (ggsrv1.localdomain) 3>
GGSCI (ggsrv1.localdomain) 3> !2 --> this command will execute the history
1. In source database (db1), verify that all the tables included in your replication have primary key constraints
SQL> select t.OWNER, t.TABLE_NAME from DBA_TABLES t where not exists
(select 1 from DBA_CONSTRAINTS c where c.OWNER = t.OWNER and c.TABLE_NAME = t.TABLE_NAME and c.CONSTRAINT_TYPE = 'P') and t.OWNER='HR'; 2
no rows selected
B. Verify the archiving mode is enabled
Make sure the archiving mode is enabled in the databases involved in your GoldenGate configuration. If not, enable it.
2. In both databases, login as sys user and enable the archiving mode:
conn / as sysdba
SELECT LOG_MODE FROM V$DATABASE ;
ALTER SYSTEM SET LOG_ARCHIVE_DEST_1 = 'LOCATION=USE_DB_RECOVERY_FILE_DEST' scope=BOTH;
SHUTDOWN IMMEDIATE
STARTUP MOUNT
ALTER DATABASE ARCHIVELOG;
ALTER DATABASE OPEN;
ALTER SYSTEM SWITCH LOGFILE;
SELECT NAME FROM V$ARCHIVED_LOG;
C.Set up a GoldenGate database user
In both the source and target databases, you need to create a database user that will be used by GoldenGate. In source database, the GoldenGate user reads the data and transactions made on the source tables. In the target database, GoldenGate user will apply the transactions data. Grant the required privileges to the GoldenGate user.
In each database, create a database user that will be used by Oracle GoldenGate
-- in DB1 -- create tablespace dedicated to OGG
CREATE TABLESPACE OGG_TBS DATAFILE '/u01/app/oracle/oradata/DB1/oggtbs01.dbf' SIZE 100M AUTOEXTEND ON NEXT 10M MAXSIZE 1G EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO;
--create OGG user
CREATE USER OGG IDENTIFIED BY oracle DEFAULT TABLESPACE OGG_TBS;
-- in DB2
-- create tablespace dedicated to OGG
CREATE TABLESPACE OGG_TBS DATAFILE '/u01/app/oracle/oradata/DB2/oggtbs01.dbf' SIZE 100M AUTOEXTEND ON NEXT 10M MAXSIZE 1G EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO;
-- create OGG user
CREATE USER OGG IDENTIFIED BY oracle DEFAULT TABLESPACE OGG_TBS;
4. Grant the required privileges to OGG user
-- (on both databases) grant privileges to OGG user
EXEC dbms_goldengate_auth.grant_admin_privilege('OGG') ;
-- check out the privileges granted to OGG:
SELECT PRIVILEGE FROM dba_sys_privs WHERE grantee='OGG'
union all
SELECT GRANTED_ROLE FROM dba_role_privs WHERE grantee='OGG'
union all
SELECT PRIVILEGE||' on '|| table_name FROM dba_tab_privs WHERE grantee='OGG';
256 rows selected.
-- (on both databases) grant further privileges to OGG: (on both source and target databases)
GRANT CREATE SESSION TO OGG;
GRANT ALTER SYSTEM TO OGG;
GRANT RESOURCE TO OGG;
GRANT CONNECT TO OGG;
GRANT DBA to OGG;
GRANT SELECT ANY DICTIONARY TO OGG;
GRANT EXECUTE on DBMS_FLASHBACK TO OGG;
GRANT LOCK ANY TABLE TO OGG;
# (in db1):
GRANT SELECT ANY TRANSACTION TO OGG;
# (in db2) only required by Replicat:
GRANT INSERT ANY TABLE TO OGG;
GRANT UPDATE ANY TABLE TO OGG;
GRANT DELETE ANY TABLE TO OGG;
GRANT CREATE TABLE TO OGG;
D. Enable supplemental logging
5. You must enable the minimal supplemental logging in the database. You also must enable the table-level or schema-level supplemental logging for the data to replicate.
# in both databases
SELECT SUPPLEMENTAL_LOG_DATA_MIN, FORCE_LOGGING FROM V$DATABASE; ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;
ALTER DATABASE FORCE LOGGING;
ALTER SYSTEM SWITCH LOGFILE;
6. (Optional) Open a monitoring session to monitor the ggserr.log file. Use this monitoring session to view how to GoldenGate responds to your commands in the background and for troubleshooting.
tail -f $GG_HOME/ggserr.log
7. In both databases, enable GoldenGate replication
ALTER SYSTEM SET ENABLE_GOLDENGATE_REPLICATION=TRUE scope=both;
8. In the source database (db1),
enable schema-level supplemental logging for HR schema
cd $GG_HOME
ggsci
ggsci> DBLogin UserID ogg, Password oracle
ggsci> ADD SCHEMATRANDATA hr
E. Configure and start the Manager process
We have not created the Manager process during in the installer wizard and it is time to create it now. As with all GoldenGate processes, you first configure the parameter file then you start the process.
9. At the GGSCI prompt, enter the command as shown below.
This command creates a new mgr.prm file and starts the vi editor to allow you to edit the parameter file. If the file already exists, it will start the editor and opens the file for you.
ggsci> edit Param mgr
10. Add the following runtime parameter to the file:
Port 7809
DynamicPortList 9500-9520
PurgeOldExtracts ./dirdat/*, UseCheckpoints, MinKeepDays 5
11. Save and close the file.
12. Start the Manager process. The following command is equivalent to the command “start manager” ggsci> start mgr
13. View the status of the Manager process.
ggsci> Info mgr
14. Repeat the same steps to create and start the Manager process in the target database system (ggsrv2). Put the following parameters in its parameter file: In the target side, the port number will be different that is 7810
Port 7810
PurgeOldExtracts ./dirdat/*, UseCheckpoints, MinKeepDays 5
F. Configure the GLOBALS parameter file
The parameters that you add to the GLOBALS file affect all the processes in an Oracle GoldenGate instance. In the following steps, you will create the file and add a parameter to it.
15. In the source system at the ggsci prompt, issue the following command to create the GLOBALS file (uppercase, without a file extension).
ggsci> EDIT PARAMS ./GLOBALS
16. Add the parameter GGSCHEMA to it.
This parameter tells the GoldenGate which database schema it should use to store its objects. GGSCHEMA OGG
Repeat the same steps in the target system (ggsrv2).
======******=========*****========*****=========*******=====*****=====
GGSCI (ggsrv1.localdomain) 20> send extract ESRV1,status
ERROR: EXTRACT ESRV1 not currently running.
GGSCI (ggsrv1.localdomain) 21> send extract PSRV1, status
Sending STATUS request to EXTRACT PSRV1 ...
EXTRACT PSRV1 (PID 3112)
Current status: Recovery complete: At EOF
Current read position:
Sequence #: 4
RBA: 404850
Timestamp: 2022-05-27 22:21:59.218609
Extract Trail: ./dirdat/es
Current write position:
Sequence #: 7
RBA: 882
Timestamp: 2022-06-24 11:47:04.239881
Extract Trail: ./dirdat/rt ----> rt=remote trail
**Please check the environment set for the concerned database for which you want to enter into the GGSCI command prompt mode**
****executing command from history****
GGSCI (ggsrv2.localdomain) 21> h
GGSCI Command History
12: edit params rsrv1
13: edit params rsrv2
14: start r*
15: info all
16: stop r*
17: edit param rsrv2
18: start r*
19: info all
20: stop r*
21: h
GGSCI (ggsrv2.localdomain) 22> !17 --> here use !(cmd number from above list)
*******get param info *******
-- in ggsrv1:
send extract esrv1 getparaminfo
-- in ggsrv2:
send replicat rsrv2 getparaminfo
GGSCI (ggsrv1.localdomain as ogg@db1) 15> lag extract esrv1
Sending GETLAG request to EXTRACT ESRV1 ...
Last record lag 2 seconds.
At EOF, no more records to process.
To verify that all the tables included in your replication have primary key constraints.
select t.OWNER, t.TABLE_NAME
from DBA_TABLES t
where not exists (select 1
from DBA_CONSTRAINTS c
where c.OWNER = t.OWNER and c.TABLE_NAME = t.TABLE_NAME
and c.CONSTRAINT_TYPE = 'P')
and t.OWNER='HR';
from DBA_TABLES t
where not exists (select 1
from DBA_CONSTRAINTS c
where c.OWNER = t.OWNER and c.TABLE_NAME = t.TABLE_NAME
and c.CONSTRAINT_TYPE = 'P')
and t.OWNER='HR';
