Oracle Golden Gate - Integrated or Classic mode - 12.2 - Tuning

Find lag for integrated extract:

SELECT capture_name, (86400*(available_message_create_timecapture_message_create_time)) lag_in_seconds FROM GV$GOLDENGATE_CAPTURE;

Find lag for integrated replicate:

SELECT r.apply_name, (86400*(r.dequeue_time -c.lwm_message_create_time)) latency_in_seconds

FROM GV$GG_APPLY_READER r, GV$GG_APPLY_COORDINATOR c 

WHERE r.apply# = c.apply# AND r.apply_name= c.apply_name;

GG Error log:

You can add lag parameters to the manager parameter file to capture lag alerts in the ggserror.log file

LAGREPORTMINUTES 5

LAGINFOMINUTES 5

LAGCRITICALMINUTES 15

Best Practices: Configuring Oracle GoldenGate for the Best Performance :

CPUs to enable parallelism :

Integrated Extract/Replicat- MAX_SGA_SIZE and PARALLELISM

Extract: 

TRANLOGOPTIONS INTEGRATEDPARAMS (MAX_SGA_SIZE 1024, parallelism 2)

Replicat: 

DBOPTIONS INTEGRATEDPARAMS (MAX_SGA_SIZE 1024, parallelism 2)

STREAM_POOL_SIZE :

alter system set STREAMS_POOL_SIZE=3G scope=both;

By default, one Integrated Extract requests the logmining server to run with MAX_SGA_SIZE of 1GB.

MAX_SGA_SIZE * (Number of Extract)* (Parallelism in Extract) *1.25 

A high-throughput network with the optimized TCP configurations:Example:

RMTHOST gg_hostname, MGRPORT 7840, TCPBUFSIZE 3000000, TCPFLUSHBYTES 3000000

Refer:

Excessive LAG on OGG Extract Data Pump Sending Data over WAN, Calculate TCP TCPBUFSIZE TCPFLUSHBYTES (Doc ID 1071892.1)

Compressed trail files if the network is constrained:

RMTHOST gg_hostname, MGRPORT 7840,TCPBUFSIZE 3000000, TCPFLUSHBYTES 3000000,COMPRESS

Best Practice:

Enable supplemental logging –TRANDATA or SCHEMATRANDATA

If using oracle DBs, configure integrated extract and replicate

Use PASSTHRU in data pump when no filtering is used

Default packet size of data sent to RMTHOST :30,000 byes

Use table level filters to extract required tables

Perform data filter at pump level so less data is sent over the network

Perform data conversion on the replicate side

Do not use DDL INCLUDE ALL until required

Fast Disk I/Os to speed up the trail files read/write

Batch Transactions:

Replicat applies transactions one by one

Check AWR for high waits on LogMinerprocess

By default, GG extract will eat up 80% of SGA size. This can cause performance issues in the source database.

Limit the amount of SGA to be used by GG extract using below in extract parameter file

TRANLOGOPTIONS INTEGRATEDPARAMS (MAX_SGA_SIZE 2560, PARALLELISM 2)

Parallelism 2 will configure 2 log miner service. Default is 2

TRANLOGOPTIONS INTEGRATEDPARAMS (MAX_SGA_SIZE 2560, PARALLELISM 3)

Parallelism 3 will configure 3 log miner service. Default is 2

RANGE and THREAD/THREADRANGE Coordinated Delivery - 12c:

Splitting Large Table with Coordinated Delivery:

replicat.prm

MAP jay.trade, TARGET rpt.trade, THREADRANGE(1-3, TRADEID));

The key benefits of coordinated replication are:

A single parameter file

Automatic control of the replication order and to ensure the correctness of the transaction splitting process. 

Enhancements to facilitate error recovery.

OR

splitting large tables into multiple Delivery processes using RANGE:

REP1.PRM:MAP jay.trade, TARGET rpt.trade,FILTER (@RANGE (1, 3, TRADEID));

REP2.PRM:MAP jay.trade, TARGET rpt.trade,FILTER (@RANGE (2, 3, TRADEID));

REP3.PRM:MAP jay.trade, TARGET rpt.trade,FILTER (@RANGE (3, 3, TRADEID));

Note: THREAD and THREADRANGE may be used with wildcard MAP statements.

Reference:

Note 1448324.1 OGG Integrated Healthcheck Script

Note 1488668.1 GoldenGate Performance Data Gathering

Note 1596493.1 GoldenGate Integration with AWR

Note 1485620.1 Best Practices Downstream Capture

MAA white paper Oracle GoldenGate Performance Best Practices

https://www.oracle.com/technetwork/database/availability/maa-gg-performance-1969630.pdf

 How to Stop and Start a Pluggable Database - automatically ?

$srvctl start database -d dbname

SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED

---------- ------------------------------ ---------- ----------

         2 PDB$SEED                       READ ONLY  NO

         3 TEST1                               MOUNTED

SQL> alter pluggable database TEST1 open instances=all;

Pluggable database altered.

SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED

---------- ------------------------------ ---------- ----------

         2 PDB$SEED                       READ ONLY  NO

         3 TEST1                               READ WRITE NO

SQL> alter pluggable database TEST1 save state instances=all;

Pluggable database altered.

SQL> SELECT con_name, instance_name, state FROM dba_pdb_saved_states;

CON_NAME                       INSTANCE_NAME                  STATE

------------------------------ ------------------------------ --------------

TEST1                          TESTY1                         OPEN

TEST1                          TESTY2                         OPEN

How to find default tablespace type - Oracle 19c ?

select property_value from database_properties where property_name = 'DEFAULT_TBS_TYPE';

PROPERTY_VALUE

--------------------------------------------------------------------------------

BIGFILE

If the default tablespace type was set to BIGFILE at database creation, you need not specify the keyword BIGFILE in the CREATE TABLESPACE statement. A bigfile tablespace is created by default.

If the default tablespace type was set to BIGFILE at database creation, but you want to create a traditional (smallfile) tablespace, then specify a CREATE SMALLFILE TABLESPACE statement to override the default tablespace type for the tablespace that you are creating.

 

How to enable ddl logging in Oracle Container database 19c ?

Solution:

At CDB:

SQL> ALTER SYSTEM SET enable_ddl_logging=TRUE SCOPE=BOTH;

-- ALTER SYSTEM SET enable_ddl_logging=FALSE SCOPE=BOTH; -- Turn off

Any PDB:

create table tbl_a (num number);

alter table tbl_a add num2 number;

Check ddl logging with new log file:

$DUMP_LOCATION/log/ddl_dbname.log  and

$DUMP_LOCATION/log/ddl/log.xml

ddl_dbname.log or log.xml:

2021-01-28T13:46:41.640037-05:00

diag_adl:create table tbl_a (num number)

2021-01-28T13:50:20.725803-05:00

diag_adl:alter table tbl_a add num2 number

Oracle 12.2 and Oracle 19.6 - Container Database START/SHUTDOWN :
SQL*Plus: Release 19.0.0.0.0 - Production on Sat Dec 26 20:32:30 2020
Version 19.6.0.0.0

SQL> startup nomount;
ORACLE instance started.
Total System Global Area 5368705960 bytes
Fixed Size                  9159592 bytes
Variable Size            2315255808 bytes
Database Buffers         2936012800 bytes
Redo Buffers              108277760 bytes

CON_NAME
------------------------------
CDB$ROOT

SQL> select name,open_mode,con_id,dbid from v$containers;
no rows selected

No CDB or PDBs info at NOMOUNT state. NOMOUNT use for re-create or restore a missing control file for the CDB instance.

SQL> alter database mount;
Database altered.

SQL> select name,open_mode,cdb from v$database;
NAME                           OPEN_MODE            CDB
------------------------------ -------------------- ---
CJTED                          MOUNTED              YES

SQL> SELECT con_id, name, open_mode FROM v$pdbs;
    CON_ID NAME                           OPEN_MODE
---------- ------------------------------ ----------
         2 PDB$SEED                   MOUNTED
         3 PJTED1                         MOUNTED
         4 PJTED2                         MOUNTED

CDB’s control files opened for the instance, but both the CDB$ROOT and all PDBs are changed to the MOUNT state.

SQL> alter database open; or alter database CJTED open;
Database altered.

SQL> select name,open_mode,con_id,dbid from v$containers;
NAME                           OPEN_MODE      CON_ID       DBID
------------------------------ ---------- ---------- ----------
CDB$ROOT                  READ WRITE          1  105692156
PDB$SEED                   READ ONLY           2  657829016
PJTED1                         READ WRITE          3 1002662416         >>> Oracle 12.2   
PJTED2                         READ WRITE          4  531562146

SQL> SELECT con_name, instance_name, state FROM dba_pdb_saved_states;   >>> Oracle 12.2
no rows selected

CDB$ROOT is OPEN, it’s available for READ and WRITE operations. 
All PDBs are still READ WRITE with the seed database PDB$SEED mounted as READ ONLY:
dba_pdb_saved_states is valid for Oracle 12.1

========================= shutdown ================================

SQL> ALTER PLUGGABLE DATABASE ALL CLOSE IMMEDIATE;
Pluggable database altered.

SQL> select name,open_mode,con_id,dbid from v$containers;
NAME                           OPEN_MODE      CON_ID       DBID
------------------------------ ---------- ---------- ----------
CDB$ROOT                      READ WRITE          1  105692156      >>>> ROOT
PDB$SEED                       READ ONLY           2  657829016       >>>> SEED
PJTED1                            MOUNTED             3 1002662416
PJTED2                            MOUNTED             4  531562146
CSTG1                             MOUNTED             5 2604502831

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

It will shutdown ALL PDBs, PDB$SEED & CDB$ROOT inside CDB.

Oracle 19c:

ORA-65040: operation not allowed from within a pluggable database, indicates an operation was attempted that can only be performed in the root container.

Issue:

SQL> CREATE OR REPLACE DIRECTORY DATA_PUMP_DIR AS '/zfs/dev/expdp/abc';

CREATE OR REPLACE DIRECTORY DATA_PUMP_DIR AS '/zfs/dev/expdp/abc'

*

ERROR at line 1:

ORA-65040: operation not allowed from within a pluggable database

Solution:

SQL> alter session set container=cdb$root;

Session altered.

SQL> SELECT DIRECTORY_NAME,DIRECTORY_PATH FROM DBA_DIRECTORIES where DIRECTORY_NAME = 'DATA_PUMP_DIR';

DIRECTORY_NAME          DIRECTORY_PATH

DATA_PUMP_DIR /u01/app/oracle/product/19.0.0.0/dbhome_2/rdbms/log/

SQL> CREATE OR REPLACE DIRECTORY DATA_PUMP_DIR AS '/zfs/dev/expdp/abc';

Directory created.

SQL> SELECT DIRECTORY_NAME,DIRECTORY_PATH FROM DBA_DIRECTORIES where DIRECTORY_NAME = 'DATA_PUMP_DIR';

DIRECTORY_NAME  DIRECTORY_PATH

DATA_PUMP_DIR   /zfs/backup01/dev/expdp/abc

Refer:

ORA-65040: operation not allowed from within a pluggable database" (Doc ID 2122655.1)

Sequence - Oracle 11g - 12c - 18c:

1. Oracle 11g - Sequence and Trigger for auto increment columns :

CREATE TABLE T1 ( ID NUMBER(10) NOT NULL,

DESCRIPTION VARCHAR2(50) NOT NULL);

ALTER TABLE T1 ADD ( CONSTRAINT T1_pk PRIMARY KEY (ID));

CREATE SEQUENCE  SEQ_11g START WITH 1;

CREATE OR REPLACE TRIGGER T1_trg

BEFORE INSERT ON T1

FOR EACH ROW

BEGIN

SELECT SEQ_11g.NEXTVAL

INTO :new.id

FROM dual;

END;

/

2. IDENTITY column on Oracle 12c:

CREATE TABLE  T1 ( c1 NUMBER GENERATED by default on null as IDENTITY,

c2 VARCHAR2(10));

or specify starting and increment values, also preventing any insert into the identity column (GENERATED ALWAYS) (again, Oracle 12c+ only)

CREATE TABLE  T1 ( 

c1 NUMBER GENERATED ALWAYS as IDENTITY(START with 1 INCREMENT by 1),

c2 VARCHAR2(10));

Alternatively, Oracle 12 also allows to use a sequence as a default value:

CREATE SEQUENCE SEQ_12c START WITH 1;

CREATE TABLE T1 (

ID NUMBER(10) DEFAULT SEQ_12c.nextval NOT NULL,

DESCRIPTION VARCHAR2(50) NOT NULL);

ALTER TABLE T1 ADD ( CONSTRAINT T1_pk PRIMARY KEY (ID));

3. Oracle 18c - scalable sequence

Now in Oracle 18c, in those cases of data ingestion workloads with high level of concurrency, the new scalable sequence by generating unordered primary or unique key values helps in significantly reducing the sequence and index block contention caused by the right-handed indexes and thus provides better throughput, data load scalability and performance as compared to the pre -Oracle 19c solution of having to configuring a very large sequence cache using the CACHE clause of CREATE SEQUENCE  or ALTER SEQUENCE statement.

The scalable sequence is made up of a sequence offset number which by default contains 6 digits. The first 3 digits is derived from the instance number with 100 added and the next 3 digits is derived from the SID of that session.

create sequence seq_test_11g

start with 1 increment by 1

maxvalue 100; 

select seq_test_11g.nextval from dual;  

NEXTVAL  :    1 

create sequence seq_test_18c

start with 1 increment by 1

maxvalue 100

scale extend; >>>> (SID nnn, Instance_Number 001)

SQL> select seq_test_18c.nextval from dual;    

NEXTVAL :  101608001

SQL> select instance_number from v$instance; 

INSTANCE_NUMBER :      1 

SQL> select sid from v$mystat where rownum = 1;  

SID :    608

select sequence_name, scale_flag, extend_flag from user_sequences  

where sequence_name='SEQ_TEST_18c';

SEQ_TEST_18c                                                  Y Y

SEQ_TEST_11g                                                  N N

Golden Gate Initial Load :

Prerequisites for the Golden Gate Initial Load

Before starting the Golden Gate initial load, ensure the following

1) You must disable any foreign-key constraints on the target tables to prevent errors/speed up performance.

2) Disable any triggers on the target tables.

3) You may want to drop indexes on your target tables to speed up performance. 

Procedure 1. Using SOURCEISTABLE and RMTTASK/SPECIALRUN

Source Database:

1. Note the SCN from source database:

SQL> select dbms_flashback.get_system_change_number() from dual;

SQL> select current_scn from v$database ;

 

2.Create EXTRACT/PUMP/REPLICATE :./ggsci

GGSCI> dblogin userid ogg, password ogg

 GGSCI> ADD EXTRACT EJAY1, INTEGRATED TRANLOG, BEGIN NOW

GGSCI> register extract EJAY1 database

GGSCI> add exttrail ./dirdat/sa, extract EJAY1

GGSCI> add trandata JAY.TBL1

 

GGSCI> edit param EJAY1

EXTRACT EJAY1

USERID ogg, PASSWORD ogg

EXTTRAIL ./dirdat/sa

TABLE JAY.TBL1;

 

3. Create PUMP:

GGSCI> Add extract PJAY1, EXTTRAILSOURCE ./dirdat/sa

GGSCI> Add rmttrail ./dirdat/rf, extract PJAY1

 

GGSCI> edit param Pschema1D1

EXTRACT PJAY1

USERID ogg, PASSWORD ogg

RMTHOST ggdev, MGRPORT 7809

RMTTRAIL ./dirdat/ra

TABLE JAY.TBL1;

 

4. Replicate Database:

GGSCI> dblogin userid ogg, password ogg

GGSCI> add replicat RJAY1, integrated exttrail ./dirdat/ra

 

GGSCI> edit param RJAY1

REPLICAT RJAY1

USERID ogg, PASSWORD ogg

ASSUMETARGETDEFS

MAP JAY.TBL1 TARGET JAY.TBL1;

 

5. Configure initial load extract:

GGSCI> ADD EXTRACT ELOAD, SOURCEISTABLE    >>>> for initial load extract

 

GGSCI> EDIT PARAM ELOAD

EXTRACT ELOAD

userid ogg, password ogg

RMTHOST dev.ip.com, mgrport 7840

RMTTASK REPLICAT, GROUP RLOAD             >>>> To auto start initial load replicate

TABLE JAY.TBL1;

 

6. initial Replicate:

GGSCI> ADD REPLICAT RLOAD, SPECIALRUN     >>>> for initial load Replicate

 

GGSCI> EDIT PARAM RLOAD

REPLICAT RLOAD

userid ogg, password ogg

ASSUMETARGETDEFS

MAP JAY.TBL1, TARGET JAY.TBL1;

 

7. Start Change Sync EXTRACT and PUMP but NOT Replicate:

START EJAY1

START PJAY1

[ HOLD Replicate for change sync process]

 

8. Now start the initial load extract. Remember, this will automatically start the initial load replicate on target :

mgr.prm at Target add (ACCESSRULE, PROG *, IPADDR *, ALLOW) (otherwise - ERROR OGG-01201 Error reported by MGR : Access denied.)

GGSCI> refresh mgr

START ELOAD

INFO ELOAD

 

At Target:

sqlplus / as sysdba

select * from JAY.TBL1;

 9) Start sync Replicate using SCN

GGSCI> start RJAY1, aftercsn <Initial_scn_from_step#1>

x

Procedure 2. Using expdp with SCN number :

1) Start the Extract

While your source database is running, you first need to start the extract so that the transactions are captured by Oracle Golden Gate.

GGSCI>start extract exext, begin now

 2) Check the Source DB SCN

Check the database SCN after the extract started, so that you make sure all the new transactions after this SCN are captured by Oracle Golden Gate.

SQL> select dbms_flashback.get_system_change_number() from dual; 

SQL> select current_scn from v$database ; 

 3) Export Data from the Source Database

Export the database with with the flashback_scn option.

expdp directory=pump_dir flashback_scn=xxx dumpfile=src.dmp logfile=sourcedb.log (schema=xxx)

 4) Import Data to the Target Database:

impdp directory=pump_dir dumpfile=src.dmp logfile=sourcedb_imp.log  (schemas=xxx)

 5) Start the Replicate:

Starting the replicat with aftercsn.

GGSCI> start replicat exrep, AFTERCSN XXX

 

 How to lock and unlock Linux  user ?

1. Example:

To lock the password:

# passwd -l oracle

Locking password for user oracle.

passwd: Success

Check :more /etc/shadow

Before Lock:

oracle:$1$3qMtw2cK$LJwR4.e9dY.axeCfEi9zY0:18583:1::7:::

After lock:

oracle:!!$1$3qMtw2cK$LJwR4.e9dY.axeCfEi9zY0:18583:1::7:::

Check two exclamation mark (!!) before the encrypted password which means that the password has been locked

To unlock the password:

# passwd -u oracle

2. Example:

# passwd -l oracle   >>> Lock the Oracle user

Locking password for user oracle.

passwd: Success

# passwd -S oracle   >>>> To check lock/unlock Linux user

oracle LK 2020-11-17 1 -1 7 -1 (Password locked.)

# passwd -u oracle    >>>> Unlock Linux user

Unlocking password for user oracle.

passwd: Success

# passwd -S oracle

oracle PS 2020-11-17 1 -1 7 -1 (Password set, MD5 crypt.)

 RMAN: How to modify RMAN CHANNEL ?

rman target /

RMAN> show all;

using target database control file instead of recovery catalog

RMAN configuration parameters for database with db_unique_name QEMPR_NJ2 are:

CONFIGURE RETENTION POLICY TO REDUNDANCY 2;

CONFIGURE BACKUP OPTIMIZATION ON;

CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default

CONFIGURE CONTROLFILE AUTOBACKUP ON;

CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '/location/rman/dbname/%F';

CONFIGURE DEVICE TYPE DISK PARALLELISM 4 BACKUP TYPE TO BACKUPSET;

CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default

CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default

CONFIGURE CHANNEL 1 DEVICE TYPE DISK CONNECT '*' FORMAT   '/location/rman/dbname/full_dbname_%T_%U';

CONFIGURE CHANNEL 2 DEVICE TYPE DISK CONNECT '*' FORMAT   '/location/rman/dbname/full_dbname_%T_%U';

CONFIGURE MAXSETSIZE TO UNLIMITED; # default

CONFIGURE ENCRYPTION FOR DATABASE OFF; # default

CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default

CONFIGURE COMPRESSION ALGORITHM 'BASIC' AS OF RELEASE 'DEFAULT' OPTIMIZE FOR LOAD TRUE ; # default

CONFIGURE ARCHIVELOG DELETION POLICY TO BACKED UP 1 TIMES TO DISK;

CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/location/rman/dbname/snapcf_dbname.f';

RMAN> SHOW CHANNEL;

RMAN configuration parameters for database with db_unique_name dbname are:

CONFIGURE CHANNEL 1 DEVICE TYPE DISK CONNECT '*' FORMAT   '/location/rman/dbname/full_dbname_%T_%U';

CONFIGURE CHANNEL 2 DEVICE TYPE DISK CONNECT '*' FORMAT   '/location/rman/dbname/full_dbname_%T_%U';

RMAN> CONFIGURE CHANNEL 1 DEVICE TYPE DISK CLEAR;

old RMAN configuration parameters:

CONFIGURE CHANNEL 1 DEVICE TYPE DISK CONNECT '*' FORMAT   '/location/rman/dbname/full_dbname_%T_%U';

old RMAN configuration parameters are successfully deleted

RMAN> CONFIGURE CHANNEL 2 DEVICE TYPE DISK CLEAR;

old RMAN configuration parameters:

CONFIGURE CHANNEL 2 DEVICE TYPE DISK CONNECT '*' FORMAT   '/location/rman/dbname/full_dbname_%T_%U';

old RMAN configuration parameters are successfully deleted

RMAN> SHOW CHANNEL;

RMAN configuration parameters for database with db_unique_name dbname are:

RMAN configuration has no stored or default parameters

 Reinstate standby database after activation using flashback.

Physical standby database PRI_DB has been activated using steps listed in the "Disaster recovery procedure." 

If a decision is made to convert the database back to physical standby the following steps apply.

Stop the database and mount one instance.

srvctl stop database -d PRI_DB

SQL> startup mount;

If a guaranteed restore point has been created prior to standby activation.

SQL>FLASHBACK DATABASE TO restore point grp_before_activate;

If no guaranteed restore point has been created but SCN prior to activation had been recorded

SQL>FLASHBACK DATABASE TO scn  <<scn prior to activate>>;

If none of the above applies, time of activation has to be determined from the alert log.

SQL> FLASHBACK DATABASE TO time  <<timestamp before standby activation>>;

After a successful flashback, convert to physical standby.

SQL>ALTER DATABASE CONVERT TO PHYSICAL STANDBY;

SQL>shutdown immediate

Change cluster configuration and start the database.

srvctl modify database -d PRI_DB -r physical_standby -s "read only"

srvctl start database -d PRI_DB

srvctl start service -d dbname

Start MRP.

SQL>alter database recover managed standby database using current logfile disconnect;

On the primary enable the corresponding destination.

SQL>alter system set log_archive_dest_state_2='enable' scope=both sid='*';

Check the alert logs on both primary and standby to verify media recovery.

PRI_DB: Check log_archive_dest_state_2 and 3 for DEFER or ENABLE. Keep this parameter ENABLE for RMAN script.

show parameter log_archive_dest_state_2  (if DEFER then make it enable)

show parameter log_archive_dest_state_3  (if DEFER then make it enable)

alter system set log_archive_dest_state_2='enable' scope=both sid='*';

alter system set log_archive_dest_state_3='enable' scope=both sid='*';

PRI_DB - How to drop RESTORE POINT ?

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

srvctl stop database -d dbname_PRI_DB

Startup mount;

Drop restore point GRP_BEFORE_ACTIVATE;

shutdown immediate

srvctl start database -d dbname_PRI_DB

srvctl start service -d dbname_PRI_DB

alter database recover managed standby database using current logfile disconnect;

 Oracle 19c - expdp - UDE-31623: operation generated ORACLE error 31623

expdp system/oracle@pdb1  schemas=JAY directory=DATA_PUMP_DIR dumpfile=JAY.dmp

Export: Release 19.0.0.0.0 - Production on Tue Sep 29 15:07:33 2020

Version 19.6.0.0.0

Copyright (c) 1982, 2019, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

UDE-31623: operation generated ORACLE error 31623

ORA-31623: a job is not attached to this session via the specified handle

ORA-06512: at "SYS.DBMS_DATAPUMP", line 4747

ORA-06512: at "SYS.KUPV$FT_INT", line 2144

ORA-06512: at "SYS.DBMS_SYS_ERROR", line 79

ORA-06512: at "SYS.KUPV$FT_INT", line 2081

ORA-06512: at "SYS.DBMS_DATAPUMP", line 2263

ORA-06512: at "SYS.DBMS_DATAPUMP", line 4496

ORA-06512: at "SYS.DBMS_DATAPUMP", line 6127

ORA-06512: at line 1

Solution:

alter system set streams_pool_size=2G scope=both sid='*';

If the STREAMS_POOL_SIZE is too small, then a Data Pump job will fail. This can also happen when using Automatic Shared Memory Management (ASMM), or Automatic Memory Management (AMM) and there is not sufficient memory to increase the STREAMS_POOL_SIZE. Manual settings for the STREAMS_POOL_SIZE of 64M, 128M or even to 256M have proven to be successful. Also increase sga_target (for ASMM) or memory_target (for AMM) to have more free memory available during automatic tuning of the SGA components. (Doc ID 1907256.1)

 Integrated Golden Gate 19c: Container database 

How to create Integrated extract/replicate  in Oracle 19c  container database ?

1) EXTRACT:

Note: Using Oracle 19c container database. Create TNS entry for cdb and pdb databases for Golden Gate.

EXTRACT EDEV1

USERID C##GGOLD_EXT@GG_CDB1, PASSWORD *****

DISCARDFILE /acfs01/goldengate/gg19c/discard/EDEV1.dsc, APPEND, megabytes 300

EXTTRAIL ./dirdat/la

TRANLOGOPTIONS INTEGRATEDPARAMS (max_sga_size 1024)

LOGALLSUPCOLS

UPDATERECORDFORMAT COMPACT

SOURCECATALOG PDB1

OBEY ./dirprm/dev_tablelist_PDB1.oby  >>> list of table name

or 

TABLE jay.table_name;

2) REPLICATE:

REPLICAT RDEV1

USERID C##GGOLD_REP@GG_PDB1, PASSWORD *****

DISCARDFILE  /acfs01/goldengate/gg19c/discard/RDEV1.dsc, APPEND, megabytes 300

DBOPTIONS INTEGRATEDPARAMS(parallelism 2)

ASSUMETARGETDEFS

SOURCECATALOG PDEV1

OBEY ./dirprm/dev_tablelist_PDB1.oby

or

MAP rpt.tablename, target rptng.tablename;

or

MAP rpt.tablename, target rptng.tablename, colmap (usedefaults,timestamp = @datenow(),flag="N"); >>> Extra column in Replicate database

3. How to Register and start Extract and Replicate ?

Extract:

dblogin USERID C##GGOLD_EXT@EDEV1, PASSWORD ******

REGISTER EDEV1 DATABASE CONTAINER (PDB1)

ADD EXTRACT DEV1, INTEGRATED TRANLOG, BEGIN NOW

ADD RMTTRAIL ./dirdat/la, EXTRACT DEV1,megabytes 300 

Replicate:

USERID C##GGOLD_REP@GG_PDB1, PASSWORD *****

ADD REPLICAT RDC3_DTC INTEGRATED EXTTRAIL ./dirdat/la

--START GG PROCESS

STRAT EDEV1

START RDEV1

or

ALTER EDEV1  BEGIN 2020-09-03 12:00

ALTER RDEV1  BEGIN 2020-09-03 12:00

4.How to create manager for Oracle 19c Golden Gate ?

PORT 7900

DYNAMICPORTLIST 7901-7999

AUTOSTART EXTRACT *

AUTORESTART EXTRACT *

AUTOSTART REPLICAT *

AUTORESTART REPLICAT *

PURGEOLDEXTRACTS /acfs01/goldengate/gg19c/dirdat/*, USECHECKPOINTS, MINKEEPDAYS 15

--START mgr

--STOP mgr

5. Advance parameter :

Extract:

DDL INCLUDE MAPPED SOURCECATALOG PORNDV1, OBJTYPE 'TABLE', EXCLUDE OPTYPE DROP, EXCLUDE INSTR 'FOREIGN KEY'

DDLOPTIONS ADDTRANDATA RETRYOP RETRYDELAY 10 MAXRETRIES 5

DDLOPTIONS REPORT

Replicate:

--DDL INCLUDE MAPPED

--DDLOPTIONS REPORT

--DBOPTIONS SUPPRESSTRIGGERS

--DDLERROR DEFAULT IGNORE

--HANDLECOLLISIONS

--MAPEXCLUDE PDB.Schema_Name.TableName;

Refer Oracle docs: https://www.oracle.com/webfolder/technetwork/tutorials/obe/fmw/goldengate/12c/OGG12c_Integrated_Replicat/index.html

 

Golden Gate : logdump >>  Find BAD or Corrupt trail files :

/path/ggs]$ logdump

Logdump 1 >open /opt/oracle/ggs/dirdat/ff000042
Current LogTrail is /opt/oracle/ggs/dirdat/ff000042
Logdump 2 >ghdr on
Logdump 4 >pos last
Reading forward from RBA 7702542
Logdump 5 >sfh prev
Scanned   16384 bytes
Scanned   32768 bytes
Scanned   49152 bytes
Scanned   65536 bytes
Scanned   65564 bytes and did not find a header
Logdump 6 >pos 0
Reading forward from RBA 0
Logdump 7 >count
Bad record found at RBA 7631536, format 5.50 Header token)     <<<<<<< Bad Files hence Trail file corrupted
0000 0000                                         | ....
LogTrail /opt/oracle/ggs/dirdat/ff000042 has 8861 records
Total Data Bytes           6592327
  Avg Bytes/Record             743
Insert                        5900
FieldComp                       42
LargeObject                   2918
Others                           1
After Images                  8860

Average of 5891 Transactions
    Bytes/Trans .....       1191
    Records/Trans ...          1
    Files/Trans .....          0

Logdump 8 >exit

Example of JDBC THIN and THICK client: Oracle 11g/12c with SCAN IP

THIN Client:

jdbc:oracle:thin:@(DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = vip_db1.com)(PORT = 1521))

(ADDRESS = (PROTOCOL = TCP)(HOST = vip_db2.com)(PORT = 1521)) (LOAD_BALANCE = ON)(FAILOVER = ON))

(CONNECT_DATA = (SERVER = DEDICATED)(SERVICE_NAME = SERVICE_NAME.COM)(FAILOVER_MODE = (TYPE = SELECT)(METHOD = BASIC))))

Thick Client:

SERVICE_NAME.COM =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = SCAN_NAME.com)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = SERVICE_NAME.COM) ) )

What are the various Golden Gate sub directories for?
 
Question What are the functions of the various sub directories within the Golden Gate installation directory?

• dirchk

Contains the checkpoint files created by Extract and Replicat processes, which store current read and write positions to support data accuracy and fault tolerance. Written in internal GoldenGate format. File name format is <group name><sequence number>.<ext> where <sequence number> is a sequential number appended to aged files and <ext> is either cpe for Extract checkpoint files or cpr for Replicate checkpoint files. Do not edit these files.
Examples: ext1.cpe rep1.cpr

• dirdat

The default location for Golden Gate trail files and extract files created by Extract processes to store records of extracted data for further processing, either by the Replicate process or another application or utility. Written in internal Golden Gate format. File name format is a user-defined two-character prefix followed by either a six-digit sequence number (trail files) or the user-defined name of the associated Extract process group (extract files). Do not edit these files.
Examples: rt000001 finance

• dirdef

The default location for data definitions files created by the DEFGEN utility to contain source or target data definitions used in a heterogeneous synchronization environment. Written in external ASCII. File name format is a user-defined name specified in the DEFGEN parameter file. These files may be edited to add definitions for newly created tables. If you are unsure of how to edit a definitions file, contact GoldenGate technical support.
Example: defs.dat

• dirpcs

Default location for status files. File name is <group>.<extension> where <group> is the name of the group and <extension> is either pce (Extract), pcr (Replicat), or pcm (Manager). These files are only created while a process is running. The file shows the program name, the process name, the port and process id that is running. Do not edit these files.
Examples: mgr.pcm ext.pce

• dirprm

The default location for GoldenGate parameter files created by GoldenGate users to store run-time parameters for GoldenGate process groups or utilities. Written in external ASCII format. File name format is <group name/user-defined name>.prm or mgr.prm. These files may be edited to change GoldenGate parameter values. They can be edited directly from a text editor or by using the EDIT PARAMS command in GGSCI.
Examples: defgen.prm finance.prm

• dirrpt

The default location for process report files created by Extract, Replicat, and Manager processes to report statistical information relating to a processing run. Written in external ASCII format. File name format is <group name><sequence number>.rpt where <sequence number> is a sequential number appended to aged files. Do not edit these files.
Examples: ABC.rpt MGR4.rpt

• dirsql

The default location for scripts created by the TRIGGEN utility to contain SQL syntax for creating GoldenGate logging triggers and GoldenGate log tables. Written in external format.
File name format is a user-defined name or the defaults of GGSLOG (table-creation script) or the table name (trigger-creation script), with the extension of .sql. These scripts can be edited if needed.
Examples: ggslog.sql account.sql

• dirtmp

The default location for storing large transactions when the size exceeds the allocated memory size. Do not edit these files.

Golden Gate Sync Using EXPDP and IMPDP :

1. Find CURRENT_SCN or FLASHBACK SCN:

select CURRENT_SCN, dbms_flashback.get_system_change_number from v$database;

2. No need to stop the EXRACT and REPLICATE
   Source: EXPDP with FLASHBACK_SCN = 999999999
   scp expdp dump file to target server 

3. Target: IMPDP – import Metadata and data
STOP EXTRACT 
STOP REPLICATE
Added following command into REPLICATE.prm file for NEW TABLE only:
MAP Schema_Name.New_Table_Name ,TARGET Schema_Name.New_Table_Name ,

FILTER ( @GETENV ("TRANSACTION", "CSN") > 999999999);
START EXTRACT 
START REPLICATE

Golden Gate Sync Using EXPDP and IMPDP :

1. Find CURRENT_SCN or FLASHBACK SCN:

select CURRENT_SCN, dbms_flashback.get_system_change_number from v$database;

2. No need to stop the EXRACT and REPLICATE
   Source: EXPDP with FLASHBACK_SCN = 999999999
   scp expdp dump file to target server 

3. Target: IMPDP – import Metadata and data
STOP EXTRACT 
STOP REPLICATE
Added following command into REPLICATE.prm file for NEW TABLE only:
MAP Schema_Name.New_Table_Name ,TARGET Schema_Name.New_Table_Name ,

FILTER ( @GETENV ("TRANSACTION", "CSN") > 999999999);
START EXTRACT 
START REPLICATE

GoldenGate Installation : 11.2

Golden Gate Software:

GG Software:        Oracle Golden Gate v11.2.1.0.20 or Higher
Oracle Database:    Oracle 11gR2
Operating System:   Linux x86_64

 Source database Pre-request : 

ARCHIVE LOG LIST -- Database must be archive log mode
ADD SUPPLEMENTAL LOG DATA -- Enable supplement logging
RECYCLEBIN=OFF – Keep recycle bin off
ENABLE_GOLDENGATE_REPLICATION = TRUE -- Enable GG parameter

Installation of GG binaries: Source and Target  :

Setup environment:
export GG_HOME=/opt/oracle/ggs
export PATH=$PATH:/opt/oracle/ggs
export LD_LIBRARY_PATH=$ORACLE_HOME/lib:/opt/oracle/ggs

Download Golden Gate software from Oracle.com
cd $GG_HOME
unzip p18322848_1121020_Linux-x86-64.zip
tar -xvf fbo_ggs_Linux_x64_ora11g_64bit.tar
cd $GG_HOME

./ggsci
  GGSCI (nydevdb01) 1> create subdirs
  Creating subdirectories under current directory /opt/oracle/ggs
  Parameter files                /opt/oracle/ggs/dirprm: already exists
  Report files                   /opt/oracle/ggs/dirrpt: created
  Checkpoint files               /opt/oracle/ggs/dirchk: created
  Process status files           /opt/oracle/ggs/dirpcs: created
  SQL script files               /opt/oracle/ggs/dirsql: created
  Database definitions files     /opt/oracle/ggs/dirdef: created
  Extract data files             /opt/oracle/ggs/dirdat: created
  Temporary files                /opt/oracle/ggs/dirtmp: created
  Stdout files                   /opt/oracle/ggs/dirout: created
  GGSCI (nydevdb01) 2>exit
  mkdir discard
  Golden Gate software has been successfully installed.

Create Golden Gate Users: Source and Target:

 Source User Name: GGS_EXT
Target User Name: GGS_REP

Create a Table space GG_DATA – 2G Size –AUTOEXTEND ON
CREATE USER GGS_EXT IDENTIFIED BY GG_USER
DEFAULT TABLESPACE GG_DATA TEMPORARY TABLESPACE TEMP PROFILE DEFAULT ACCOUNT UNLOCK;

GRANT CONNECT, RESOURCE TO GG_USER;
GRANT SELECT ANY DICTIONARY, SELECT ANY TABLE TO GG_USER;
GRANT SELECT ANY TRANSACTION TO GG_USER;
GRANT FLASHBACK ANY TABLE TO GG_USER;
GRANT EXCUTE ON DBMS_FLASHBACK TO GG_USER;
GRANT EXECUTE ON UTL_FILE TO GG_USER;
GRANT CREATE ANY TABLE TO GG_USER;
GRANT INSERT ANY TABLE TO GG_USER;
GRANT UPDATE ANY TABLE TO GG_USER;
GRANT DELETE ANY TABLE TO GG_USER;
GRANT DROP ANY TABLE TO GG_USER;
GRANT UNLIMITED TABLESPACE TO GG_USER;
EXEC DBMS_GOLDENGATE_AUTH.GRANT_ADMIN_PRIVILEGE('GG_USER');

 GG Installation Verification: 

./ggsci
GGSCI> START MGR
GGSCI> INFO ALL
GGSCI>DBLOGIN USERID GGS_EXT, PASSWORD *****  
Successfully logged into database.

If need DDL Replication, run following Golden Gate scripts at Source database : 

 sqlplus / as sysdba
@marker_setup.sql  Enter Oracle GoldenGate schema name:GG_USER
@ddl_setup.sql     Enter Oracle GoldenGate schema name:GG_USER
@role_setup.sql    Enter Oracle GoldenGate schema name:GG_USER
grant ggs_ggsuser_role to GG_USER;
@ddl_enable;
--@ddl_pin GGATE; (optional)
--@sequence (optional)

 

 

GoldenGate integration in clusterware

 1. To start/stop manager - It will start golden on a node where GG VIP is running:

$ crsctl start res ggate
$ crsctl stop res ggate

2. To start manager on a specific node:

$ crsctl stop res ggate
$ crsctl start res ggate -n nodename -f

3. To relocate goldengate resource from existing node to new node:

$ crsctl relocate resource ggate –s existing_node –n new_node –f 

4.If below parameters are added in mgr.prm then replicat/extract will be started along with manager as well.:

AUTOSTART EXTRACT *
AUTORESTART EXTRACT *
AUTOSTART REPLICAT *
AUTORESTART REPLICAT *

 Steps to integrate GoldenGate in to clusterware:

 1. As a root user create an application VIP for Oracle GoldenGate :

appvipcfg create -network=1 -ip= XX.XX.XX.XX -vipname=app-oragg-vip -user=root

2. As a root user set read and execute privilege for ORACLE and GRID user on newly added application VIP:

crsctl setperm resource app-oragg-vip -u user:oracle:r-x
crsctl setperm resource app-oragg-vip -u user:grid:r-x

3. As an Oracle user start application VIP resource :

 crsctl start resource app-oragg-vip

4. Prepare action script for handling GoldenGate resource in a cluster. 

5. As an Oracle users add clusterware resource for it:

crsctl add resource ggate -type cluster_resource -attr "ACTION_SCRIPT=

/path/ggs/gg_action.sh,CHECK_INTERVAL=30, START_DEPENDENCIES=

'hard(app-oragg-vip,ora.asm) pullup(app-oragg-vip)', STOP_DEPENDENCIES=

'hard(app-oragg-vip)'" 

6.As a root make Oracle as an owner of GoldenGate resource in the cluster :

 crsctl setperm resource ggate -o oracle

7. As an Oracle user start GoldenGate resource :

  crsctl start res ggate

Oracle Support Documents referred –

1. Doc ID - 1112506.1

2. Doc ID - 1313703.1