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