ERROR OGG-00446 e_###.prm: error 13 (Permission denied) opening redo log

Scenario:We installed goldengate on the database server z11.kc.com as oracle-goldengate-user ggs01, we configured Extract, pump against source database and replicats  on target database. successfully replicat's and pump came up during initial START and extract's are abended with following error  2014-12-14 10:48:21  

ERROR   OGG-00446: Oracle GoldenGate Capture for Oracle, e_###.prm:  error 13 (Permission denied) opening redo log /fs01/talent9/redo01.log for sequence 76Not able to establish initial position for begin time 2014-12-14 10:48:21   10:11:38. 2014-12-14 10:48:21   ERROR   OGG-01668  Oracle GoldenGate Capture for Oracle, e_###.prm:  PROCESS ABENDING.

During initial investigation, I found following above error in report file. As we configured classic extract, you that classic extract works on online redo/archive log on database. when we started extract's, extracts started accessing transaction log (redo log)

Resolution:By looking at above error, permission denied, i closely looked at permission's on online redo log file directory, which is owned by Different user oracle and dba group. later on we worked with SA and changed perimissions 665 on redo logfile directory. After extract came up automatically from where it is abended :) 

----Nikhil Tatineni -----

Goldengate Extract is not moving?

Hi guys,
when your extract is not collecting transactions against source table (or) it is not moving against database ? 

To find out resolution..Kindly investigate following steps
step1: check trandata is enabled for your source table?
ggsci> dblogin userid ggsuser password XXXXXggsci> info trandata schema.tablename

step2: check there is any long running transactions on your database or blocking sessions, try to skip transactions using XID 
After making sure these 2 steps
using logdump utility, kindly validate extract is collecting data and writing data on  local trail

Thank you --- Nikhil Tatineni ---

SPLITING OracleGoldenGate REPLICAT

Hey guys !! Welcome back..

I have seen many of my friends facing issues with replicating huge volume tables using goldengate, Here we go, I want to discuss, how goldengate replicat split helps and improve the performance of replication.

In order to split replicat 1 into many,  Table involved in replication should have primary key and unique key should be on same column, If a table have primary key and unique key On different column, REPLICAT WILL ABEND BY GENERATING A LOT OF DISCARDS, REPLICAT'S  involved in splitting WILL COMPETE EACH OTHER IN APPLYING SAME  RECORD ON THE TRAIL to the target table.

Resolution:
we need to add handlecollisions to replicat paramfile and restart the replicat from abend 

Steps to split replicat 1 into many, we split replicat using  FILTER and RANGE parameters.  In this scenario, We are splitting replicat 1 into 4, each replicat logically work ( perform DML) on 1/4 th of base table; 

steps to split replicat as follows

STEP1: STOP REPLICAT

GGSCI (eniya.varman.com) 4> STOP RTECVE
Sending STOP request to REPLICAT RTECVE ...
Request processed.

STEP2: collect RBA  and sequence number of remote trail from the old replicat !! 

GGSCI (eniya.varman.com) 19> INFO RTECVE

REPLICAT   RTECVE    Last Started 2015-02-01 13:51   Status STOPPED
Checkpoint Lag       00:00:00 (updated 00:00:23 ago)
Log Read Checkpoint  File /u01/app/goldengate/remotetrail/zz000004
                     2015-02-01 19:02:16.537343  RBA 2292

STEP3: PREPARE THE NEW OGG REPLICAT PROCESSES 

-- Handles all tables WITHOUT PK /UI
-- GGSCI> add replicat rtecve_1, exttrail /u01/app/goldengate/remotetrail/zz, checkpointtable ggsuser.ckpt
—- ALTER rtecve_1, extseqno 4 extrba 2292 
replicat rtecve_1
assumetargetdefs
sourcedefs ./dirdef/teacher.def
------structure of source table and target table is same 
userid ggsuser, password oracle
reportcount every 24 hours, rate
discardfile /u01/app/goldengate/discardtrail/rtecve_1.dsc, megabytes 10, append
discardrollover at 06:00
map nspdba.teacher, target nspdba.teacher, FILTER(@RANGE(1,4));

-- Handles all tables WITHOUT PK /UI
-- GGSCI> add replicat rtecve_2, exttrail /u01/app/goldengate/remotetrail/zz, checkpointtable ggsuser.ckpt
—- ALTER rtecve_2, extseqno 4 extrba 2292 
replicat rtecve_2
assumetargetdefs
sourcedefs ./dirdef/teacher.def
------structure of source table and target table is same 
userid ggsuser, password oracle
reportcount every 24 hours, rate
discardfile /u01/app/goldengate/discardtrail/rtecve_2.dsc, megabytes 10, append
discardrollover at 06:00
map nspdba.teacher, target nspdba.teacher, FILTER(@RANGE(2,4));

-- Handles all tables WITHOUT PK /UI
-- GGSCI> add replicat rtecve_3, exttrail /u01/app/goldengate/remotetrail/zz, checkpointtable ggsuser.ckpt
—- ALTER rtecve_3, extseqno 4 extrba 2292 
replicat rtecve_3
assumetargetdefs
sourcedefs ./dirdef/teacher.def
------structure of source table and target table is same 
userid ggsuser, password oracle
reportcount every 24 hours, rate
discardfile /u01/app/goldengate/discardtrail/rtecve_3.dsc, megabytes 10, append
discardrollover at 06:00
map nspdba.teacher, target nspdba.teacher, FILTER(@RANGE(4,4));

-- Handles all tables WITHOUT PK /UI
-- GGSCI> add replicat rtecve_4, exttrail /u01/app/goldengate/remotetrail/zz, checkpointtable ggsuser.ckpt
—- ALTER rtecve_4, extseqno 4 extrba 2292 
replicat rtecve_4
assumetargetdefs
sourcedefs ./dirdef/teacher.def
------structure of source table and target table is same 
userid ggsuser, password oracle
reportcount every 24 hours, rate
discardfile /u01/app/goldengate/discardtrail/rtecve_4.dsc, megabytes 10, append
discardrollover at 06:00
map nspdba.teacher, target nspdba.teacher, FILTER(@RANGE(3,4));

REPLICAT    STOPPED     RTECVE      00:00:00      00:12:35    
REPLICAT    STOPPED     RTECVE_1    00:00:00      00:04:57    
REPLICAT    STOPPED     RTECVE_2    00:00:00      00:03:39    
REPLICAT    STOPPED     RTECVE_3    00:00:00      00:02:53    
REPLICAT    STOPPED     RTECVE_4    00:00:00      00:00:25  

STEP 4: START NEW REPLICAT’S

GGSCI (eniya.varman.com) 48> start RTECVE_1
Sending START request to MANAGER ...
REPLICAT RTECVE_1 starting

GGSCI (eniya.varman.com) 49> start RTECVE_2
Sending START request to MANAGER ...
REPLICAT RTECVE_2 starting

GGSCI (eniya.varman.com) 50> start RTECVE_3
Sending START request to MANAGER ...
REPLICAT RTECVE_3 starting


GGSCI (eniya.varman.com) 51> start RTECVE_4
Sending START request to MANAGER ...
REPLICAT RTECVE_4 starting

STEP 5: VALIDATE ALL PROCESSES AND PARAMETER’S USED IN REPLICATS !!

REPLICAT    RUNNING     RTECVE_1    00:00:00      00:00:04    
REPLICAT    RUNNING     RTECVE_2    00:00:00      00:00:09    
REPLICAT    RUNNING     RTECVE_3    00:00:00      00:00:07    
REPLICAT    RUNNING     RTECVE_4    00:00:00      00:00:02 

STEP 6: REMOVE THE OLD REPLICAT —

GGSCI (eniya.varman.com) 8> dblogin userid ggsuser@eniya, password oracle
Successfully logged into database.

GGSCI (eniya.varman.com) 9> delete RTECVE
Deleted REPLICAT RTECVE.


-------Thank you


I went through this pdf, this is really intresting, kindly go through

http://www.oracle11ggotchas.com/articles/Defining%20Multiple%20Replicats%20to%20Increase%20GoldenGate%20Performance.pdf

ERROR OGG-01028 Formatting error on: table

Scenario:

Configured brand new goldengate processes for  NEW table EWT_RWT_EMP,  Started  configured replicat, pump and extract. Extract is abended on source oracle Goldengate home, 

EXTRACT     ABENDED     EWTEMP        00:00:00      00:00:02

During initial investigation, we found following error in report file

2014-08-24 15:02:04  INFO-OGG-01517  Position of first record processed Sequence 10140, RBA 500086800, SCN 3230. 3920648400, Aug 22, 2014 1:21:38 PM.

TABLE resolved (entry hr.ewt_rwt_id,emp):

table "HR"."EWT_RWT_EMP", keycols (ewt_rwt_id,emp);
Using the following key columns for source table hr.ewt_rwt_id,emp: ewt_rwt_id,emp

Source Context :
SourceModule            : [er.redo.ora]
SourceID: [/scratch/aime1/adestore/views/aime1_staxj04/oggcore/OpenSys/src/app/er/redo/oracle/redoora.c]
SourceFunction        : [REDOORA_handle_format_error(file_def *, hdr_info_t *, char *)]
SourceLine              : [9058]

2014-08-24 15:02:04  ERROR   OGG-01028  Formatting error on: table name ewt_rwt_id,emp, rowid AADlmAAAHAAIQ6EAAA, XID 12.8.385427, position (Seqno 10140, RBA 500103096). Error converting timestamp with timezone from Oracle to ASCII format for column DISC_DT.

***********************************************************************
*                   ** Run Time Statistics **                         *
***********************************************************************

Report at 2014-08-24 15:02:04 (activity since 2014-08-24 15:02:04)

Output to u02/app/ggs/trails/bb:

No records extracted.

Resolution:

Added following parameter into extract and restart the extract 

TRANLOGOPTIONS INCLUDEREGIONID

EXTRACT     RUNNING     EWTEMP       00:00:00      00:00:02

started successfully !!

Oracle in-memory !!

ERROR OGG-01232 Receive TCP params error: TCP/IP error 73

Scenario:
we have compliant from application team, data is not getting replicated for one of the critical table to target instances,  During initial investigation,  we found  Ogg pump is abended  on source side and also we found following error in report file, ogg monitoring is disabled on servers

we found pump is abended on source side,

EXTRACT     ABENDED     PEMPRI     00:00:00      09:26:59

GGSCI> view report PEMPRI

2014-10-05 10:38:06  INFO    OGG-01226  Socket buffer size set to 27985 (flush size 27985).
Source Context :  SourceModule            : [ggnet.tpcrtn]
  SourceID                : [/scratch/aime1/adestore/views/aime1_staxj04/oggcore/OpenSys/src/gglib/ggnet/tcprtn.c]
  SourceFunction          : [opt_settle(int, char *, int32_t, char *)]
  SourceLine              : [3129]

2014-10-05 10:43:06  ERROR   OGG-01232  Receive TCP params error: TCP/IP error 73 (Connection reset by peer), endpoint:sfo.com.
2014-10-05 10:43:06  ERROR   OGG-01668  PROCESS ABENDING.

Resolution: 

check goldengate manager process  is  running on target  goldengate home / server

GGSCI> Info mgr

• If the manager is not running on target goldengate home, start the manager and start abended pump on source side
• If the manager is running, still the pump is abended state, i recommend you to bounce the associated replicat on target goldengate home. Replicat process is stale and can hold lock on the  remote trail 
• If still pump is not coming up check RBA of the pump with size of the local trail on source server

GGSCI (sfo) 19> info PEMPRI

EXTRACT    PEMPRI   Last Started 2014-10-05 10:38   Status ABENDED

Checkpoint Lag       00:00:00 (updated 09:18:45 ago)

Log Read Checkpoint  File /u02/app/ggs/trails/ba000083

                     2014-10-05 01:18:48.000000  RBA 447538549

GGSCI (sfo) 20> exit

ggs01@sfo> cd /u02/app/ggs/trails/
ggs01@sfo> ls -ltr ba*

-rw-r-----    1 ggs01    ggsoper   447538549 Oct 05 01:45 ba000083
-rw-r-----    1 ggs01    ggsoper    10543247 Oct 05 10:38 ba000084

when you compared the size of the trail and RBA is same, we can conclude that pump is unable to perform ETROLLOVER, Now you can forcefully ETROLLOVER the pump to next trail 

GGSCI> alter PEMPRI, ETROLLOVER

GGSCI (sfo) 19> info PEMPRI

EXTRACT    PEMPRI   Last Started 2014-10-05 10:38   Status RUNNING

Checkpoint Lag       00:00:00 (updated 09:18:45 ago)

Log Read Checkpoint  File /u02/app/ggs/trails/ba000084

                     2014-10-05 01:18:48.000000  RBA 1168

Now we can see, pump is up and running, and we can see  RBA "RBA 1168" is increasing !

! -- Oracle In-memory !!!

DBPITR :RMAN

I want to discuss Database Point InTime Recovery day !

As a DBA, 1st thumb rule, we need to have backup for database to avoid logical corruption, datafile recovery, block recovery, tablespace point in-time recovery and for disaster recovery so on..

we know,  database will log errors in alert.log file in " background_dump_dest " location,  we can find out SCN when database is crashed from alert.log file.  

Scenario:

Let's assume that some batch jobs running on database inserted wrong data into the tables, corrupted whole database, now we have to perform Database Point Intime Recovery as follows,

Step: 1 we can find out information about jobs, at what time jobs started on database ?,  get this information from application team or query "DBA_SCHEDULER_JOBS" view on database

let's assume that, jobs started on database at "31-01-2015 11:15:00" we have to perform, database point time recovery using this timestamp, I want to match current timestamp with SCN of database, we can use both SCN and TIMESTAMP to restore and recover the database.

STEP1:
SQL> select timestamp_to_scn(to_timestamp(’31-01-2015 11:15:00’,’DD/MM/YYYY HH24:MI:SS')) as scn from dual; SCN ---------- 912264

In STEP:2 I am bringing down database and starting the database in mount stage
SQL> shut immediate; Database closed. Database dismounted. ORACLE instance shut down. SQL> startup mount; ORACLE instance started. Total System Global Area 841162752 bytes Fixed Size 1348188 bytes Variable Size 499125668 bytes Database Buffers 335544320 bytes Redo Buffers 5144576 bytes Database mounted.

In STEP3: I am connecting to RMAN, and restoring database to point using SCN or Timestamp in step:1

 oracle@sfo ~]$ . oraenv

ORACLE_SID = [r456] ?     

The Oracle base remains unchanged with value /u01/app/oracle

[oracle@sfo ~]$ rman target /

Recovery Manager: Release 11.2.0.3.0 - Production on Sat Jan 31 13:03:41 2015

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

connected to target database: R456 (DBID=707274850, not open)

RMAN>  RUN

{ SET UNTIL '31-01-2015 11:15:00'; restore database; RECOVER DATABASE; }

--Output

RMAN> Starting restore at 31-JAN-15 allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=20 device type=DISK allocated channel: ORA_DISK_2 channel ORA_DISK_2: SID=21 device type=DISK channel ORA_DISK_1: starting datafile backup set restore channel ORA_DISK_1: specifying datafile(s) to restore from backup set channel ORA_DISK_1: restoring datafile 00002 to /u01/app/oracle/oradata/r456/sysaux01.dbf channel ORA_DISK_1: restoring datafile 00005 to /u01/app/oracle/oradata/r456/GGS1.dbf channel ORA_DISK_1: restoring datafile 00007 to /u01/app/oracle/oradata/r456/NSPDB01.dbf channel ORA_DISK_1: restoring datafile 00008 to /u01/app/oracle/oradata/r456/GG_USER01.dbf channel ORA_DISK_1: restoring datafile 00009 to /u01/app/oracle/oradata/r456/GG01.dbf channel ORA_DISK_1: reading from backup piece /u01/RMANBACKUP/08pu3lje_1_1 channel ORA_DISK_2: starting datafile backup set restore channel ORA_DISK_2: specifying datafile(s) to restore from backup set channel ORA_DISK_2: restoring datafile 00001 to /u01/app/oracle/oradata/r456/system01.dbf channel ORA_DISK_2: restoring datafile 00003 to /u01/app/oracle/oradata/r456/undotbs01.dbf channel ORA_DISK_2: restoring datafile 00004 to /u01/app/oracle/oradata/r456/users01.dbf channel ORA_DISK_2: restoring datafile 00006 to /u01/app/oracle/oradata/r456/OSPDB01.dbf channel ORA_DISK_2: restoring datafile 00010 to /u01/app/oracle/oradata/r456/masood01.dbf channel ORA_DISK_2: reading from backup piece /u01/RMANBACKUP/09pu3lje_1_1 channel ORA_DISK_1: piece handle=/u01/RMANBACKUP/08pu3lje_1_1 tag=TAG20150131T122829 channel ORA_DISK_1: restored backup piece 1 channel ORA_DISK_1: restore complete, elapsed time: 00:01:46 channel ORA_DISK_2: piece handle=/u01/RMANBACKUP/09pu3lje_1_1 tag=TAG20150131T122829 channel ORA_DISK_2: restored backup piece 1 channel ORA_DISK_2: restore complete, elapsed time: 00:01:46 Finished restore at 31-JAN-15

Starting recover at 31-JAN-15 using channel ORA_DISK_1 using channel ORA_DISK_2 starting media recovery media recovery complete, elapsed time: 00:00:01 Finished recover at 31-JAN-15

RMAN successfully restored and recovered the database 
job is completed


STEP:4: Open database and validate data in database

[oracle@sfo ~]$ . oraenv ORACLE_SID = [r456] ? The Oracle base remains unchanged with value /u01/app/oracle [oracle@sfo ~]$ sqlplus / as sysdba SQL*Plus: Release 11.2.0.3.0 Production on Sat Jan 31 13:15:44 2015 Copyright (c) 1982, 2011, Oracle. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production With the Partitioning, OLAP, Data Mining and Real Application Testing options SQL> select open_mode from v$database; OPEN_MODE -------------------- MOUNTED SQL> SQL> alter database open; Database altered. SQL> SQL> archive log list; Database log mode Archive Mode Automatic archival Enabled Archive destination USE_DB_RECOVERY_FILE_DEST Oldest online log sequence 5 Next log sequence to archive 7 Current log sequence 7

SQL> select open_mode, database_name from v$database;

OPEN_MODE, DATABASE_NAME

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

READ WRITE, R456

---Theory

DBPITR within the current incarnation is performed using the current control file. When performing DBPITR, you can avoid errors by using the SET UNTIL command to set the target time at the beginning of the process, rather than specifying the UNTIL clause on the RESTORE and RECOVER commands individually. This ensures that the datafiles restored from backup will have timestamps early enough to be used in the subsequent RECOVER operation.

The following example performs DBPITR on the target database until SCN 912264:
RUN { SET UNTIL 912264;
restore database; RECOVER DATABASE; } Note: You can also use time expressions, restore points, or log sequence numbers to specify the SET UNTIL time: SET UNTIL TIME 'Nov 15 2004 09:00:00'; SET UNTIL SEQUENCE 9923; SET UNTIL RESTORE POINT before_update; # Alternatives: # SET UNTIL TIME 'Nov 15 2004 09:00:00'; # SET UNTIL SEQUENCE 9923; RESTORE DATABASE;


----------
Conclusion:

DBPITR is a technique to restore database to point in time, if the size of the database is large 8TB, we cannot perform "DBPITR", if your database is logically corrupted, DBTIR takes more time to restore and recover database, need application downtime. As alternative we can perform FLASHBACK USING SCN number !!

Thank you !! Oracle in memory !!:)

Knowledge on goldengate

Knowledge on Oracle Goldengate



components of goldengate:
Manager,
Collector,
Extract,
Local trails,
Pump,
Replicat,
Remote trails,
checkpointtable,

Manager & Collector: Manager runs on both Source and Target oracle goldengate home with default port number 7809, we introduce different parameters in manager process, example manager purging rules, auto restart parameters, reporting rules etc. In goldengate, DBA interact with  oracle goldengate manager  process, we send stop and start ogg process request to manager, manager completes the request. when pump tries to copy trail from source to  target server, manager calls collector to receive it, collector allows the pump to flush the trail in to remote trail directory.
we can Implement purging rules in manager process to purge old trails in Trail Directory 
purgeoldextracts  /local_trail/*, usecheckpoints, minkeepdays 2,  frequencyminutes 60
we can Implement Autorestart parameters in manager process, if any process abends it will automatically restart the abended process 
autorestart extract e*, retries 10, waitminutes 2, resetminutes 1440
create warning in ggserror.log for critical errors 
lagcriticalseconds 30

Extract processes:
Extract works on source database, it collects transactions against source database, write data to local trails, as we know that SCN number of database is equal to commit sequence number CSN of goldengate. we can configure extract at table level & schema level. there are different type of extracts, classic and Integrated extracts. Classic extract works on online redo logs / archive logs to collect transactions against source tables where Integrated extract work's with log mining server to extract data against source tables. we can introduce different type of parameters in EXTRACT parameter file depend on type of  replication configuration ( DML/DDL).

to be continued .....