Network Communication Issues in GoldenGate

Please check ggerror.log file:-- Network Communication Issues in GoldenGate

ERROR   OGG-01033  Oracle GoldenGate Capture for Oracle, extpump.prm:  There is a problem in network communication, a remote file problem, encryption keys for target and source do not match (if using ENCRYPT) or an unknown error. (Remote file used is /opt/app/t1mm1d56/ggs/trails/rt000377, reply received is Could not create /opt/app/t1mm1d56/ggs/trails/rt000377).

WARNING OGG-01525  Oracle GoldenGate Capture for Oracle, extpump.prm:  Failed to open trace output file, 'gglog-EXTPUMP.dmp', error 13 (Permission denied).

To FIX:

Restart the manager processm, answer "y" when prompted.

Step 1:

GGSCI (hltd212) 64> stop mgr
Manager process is required by other GGS processes.
Are you sure you want to stop it (y/n)?

Step 2: please try to restart mgr and others process after stopped like below

GGSCI (hltd212) 65>start mgr

UNIQUE Constraint Violation issue in Oracle Golden Gate

Please check ggerror.log file :-- UNIQUE Constraint Violation issue in Oracle Golden Gate

WARNING OGG-00869  Oracle GoldenGate Delivery for Oracle, st3_rep1.prm:  OCI Error ORA-00001: unique constraint (NIM.SYS_C0074582) violated (status = 1), SQL <INSERT INTO "NIM"."MCWARNING" ("ACTIVITYDEFINITIONID","WARNINGMESSAGE") VALUES (:a0,:a1)>.

WARNING OGG-01004  Oracle GoldenGate Delivery for Oracle, st3_rep1.prm: 
Aborted grouped transaction on 'NIM.MCWARNING', Database error 1 (ORA-00001: unique constraint (NIM.SYS_C0074582) violated).

To FIX:

Step 1:

edit param st3_rep1

Step2 :

Uncomment the HANDLECOLLISIONS parameter then restart.  To uncomment take out the two dashes in front of the parameter.

Once the lag is dimished, again comment this out and restart the thread i.e. restart the process.

LONG RUNNING TRANSACTION issue in Oracle GoldenGate

Please check ggerror.log file :--- LONG RUNNING TRANSACTION issue in Oracle GoldenGate

GGS WARNING     190  Oracle GoldenGate Capture for Oracle, extgrp1.prm:  Long Running Transaction: XID 313.5.780269, Items 3, Extract EXTGRP1, Redo Thread 1, SCN 2912.2575963070 (12509520729022), Redo Seq #147217, Redo RBA 150735888.

To FIX:

*Monitor and see if the LRT will clear on its own.  If a lag starts to happen and it reaches 30 minutes then perform the following step to skip the transaction.*

send extgrp1 skiptrans 313.5.780269

extgrp1:- GG extract process name
skiptrans:- keyword
313.5.780269:-- LRT

BAD COLUMN INDEX issue in Oracle GoldenGate

Please check gg log file to fix: -- BAD COLUMN INDEX issue in Oracle GoldenGate

ERROR   OGG-01161  Oracle GoldenGate Delivery for Oracle, st5_rep1.prm:  Bad column index (10) specified for table NIM.LSRVINFO, max columns = 10.

To FIX:

This usually means the the columns on the target and source do not match.  Compare the tables on target and source to see if all columns match.  Update target to match source by adding a column or removing a column or altering the size of a column, etc...  In this case we needed to add a column.

gglst1d2:/appl/gg/ggs/ $ sqlplus nim_st5/nim_st5

SQL*Plus: Release 11.1.0.7.0 - Production on Fri Oct 7 08:11:05 2011
Copyright (c) 1982, 2008, Oracle.  All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production

SQL> Alter table nim_st5.lsrvinfo add ORIGINALFOCDUEDATE timestamp(6);
Table altered.

ARCHIVE LOG MISSING in Oracle GoldenGate

Please check ggerror.log file : ARCHIVE LOG MISSING in Oracle GoldenGate

ERROR   OGG-00446  Oracle GoldenGate Capture for Oracle, extgrp1.prm:  Opening ASM file +IPAG_RECOVERY/1_14170_778456717.dbf in DBLOGREADER mode: (308) ORA-00308: cannot open archived log '+IPAG_RECOVERY/1_14170_778456717.dbf'

ORA-17503: ksfdopn:2 Failed to open file +IPAG_RECOVERY/1_14170_778456717.dbf ORA-15173: entry '1_14170_778456717.dbf' does not exist in directory '/'Not able to establish initial position for sequence 14170, rba 94987280.

To FIX:

Restore archivelogs using RMAN on the source database, then restart GG.  The first archive log sequence that is missing can be found in the error message, "'Not able to establish initial position for sequence 14170". 

To find the last archive log missing run the SQL the first archive log listed minus one will be the last archive log missing, in this case it would be 14271.  So in order to fix the above error a restore from 14170 to 14270 would need to be performed.

SQL> select THREAD#, SEQUENCE#, COMPLETION_TIME , DELETED from gv$archived_log where DEST_ID = 1 and DELETED = 'NO' order by THREAD#, SEQUENCE#;

   THREAD#  SEQUENCE# COMPLETIO DEL
---------- --------- -------- --
         1      14271 31-MAR-13 NO
         1      14272 31-MAR-13 NO
         1      14273 31-MAR-13 NO
         1      14274 31-MAR-13 NO

${ORACLE_HOME}/bin/rman target / catalogrman102/cat@rmandb.snt.bst.bls.com

RMAN>  run { restore archivelog from logseq=14170 until logseq=14270; }

Foreign Key Issues in Oracle GoldenGate

Please check ggerror.log file :

Database error 2292 (OCI Error ORA-02292: integrity constraint (CRA.PT_PT_FK) violated - child record found (status = 2292), SQL <DELETE FROM "CRA"."PROJTASK"  WHERE "PROJTASKID" = :b0>).

Solution:

Disable the foreign key on the mirror database and restart process.

DATA LOSS ISSUE on source side in GoldenGate

Please check gg log file:--

ERROR   OGG-01973  Oracle GoldenGate Capture for Oracle, elci3t1d.prm:  The redo record indicates data loss on object 80,238.

To FIX:

Query the database with the folloiwng query then exclude the table from the extract.  In this case it was the workorder table.

select owner, object_name from dba_objects where object_id=80238 or data_object_id=80238;

GGSCI (nmsis) 18> edit param ELCI3T1D

TABLEEXCLUDE  MER.WORKORDER;

SAVE AND EXIT

Downgrade from oracle 11gR2 to oracle 11gR1 using Datadump i.e expdp

Downgrade from oracle 11gR2 to oracle 11gR1. Please follow up below table to downgrade with version parameter.

Exporting from Oracle Database 11g Release 11.2 and Importing Into Oracle Database 11g Release 11.1, 10g Release 2, or 10g Release 1

Export From	Import To	Export Version to Use	Import Version to Use
Release 11.2	Release 11.1	Data Pump Export Release 11.2 with VERSION=11.1	Data Pump Import Release 11.1
Release 11.2	Release 10.2	Data Pump Export Release 11.2 with VERSION=10.2	Data Pump Import Release 10.2
Release 11.2	Release 10.1	Data Pump Export Release 11.2 with VERSION=10.1	Data Pump Import Release 10.1

ORA-19625: error identifying file string tips

When you are taken archive backup suing rman then got an above ORA error. i.e. some archive logs are missing on archive destination. So you need to run below commands to fixed this.

change archivelog all validate;

crosscheck archivelog all;   

PLS-00201: identifier 'AWRRPT_INSTANCE_LIST_TYPE' must be declared

When I execute awrgrpt.sql to get globel RAC report then got an below errors:

ERROR at line 15:
ORA-06550: line 15, column 11:
PLS-00201: identifier 'AWRRPT_INSTANCE_LIST_TYPE' must be declared
ORA-06550: line 15, column 11:
PL/SQL: Item ignored
ORA-06550: line 47, column 24:
PLS-00320: the declaration of the type of this expression is incomplete or
malformed
ORA-06550: line 48, column 4:
PL/SQL: ORA-00904: : invalid identifier
ORA-06550: line 46, column 4:
PL/SQL: SQL Statement ignored

Solution:

CONNECT / AS SYSDBA
GRANT ALL ON awrrpt_instance_list_type TO <user>;

ORA-38701: Flashback database log 518 seq 6752 thread 1:

ORA-38701: Flashback database log 518 seq 6752 thread 1: "/pac/lci1d1/arch/LCI1D1/flashback/o1_mf_9zc63rhb_.flb"

ORA-27037: unable to obtain file status

Cause of The Problem
The above errors occurred due to a well identified bug. If there is any I/O error that is preventing write to the flashback logs, the instance will crash. Losing the current flashback log will cause the instance to crash. Also during database startup if flashback logs are inaccessible then instance will crush.

Solution of The Problem

Solution A)-Upgarde Database Version:
This bug is affected from database version 10.1.0.1 to 10.1.0.4
It is fixed in database version 10gR2. So upgrade your database version.

Solution B)-Disable Flashback and restart the instance.
1)If you don't have the situation to upgrade the database then start the database in mount stage.
SQL> startup mount;

2)Turn off the Flashback
SQL> alter database flashback off;

3)Turn on the Flashback
SQL> alter database flashback on;

4)Startup the database
SQL>alter database open;

Or, SQL>shutdown
SQL>startup

Note:
After disabling flashback if you have guaranteed restore point then you still might get the error ORA-38760: This database instance failed to turn on flashback database while starting up the database. In this case you have to drop the guaranteed restore point.

You can see the available guaranteed restore point by,
select NAME,SCN,GUARANTEE_FLASHBACK_DATABASE,DATABASE_INCARNATION# from v$restore_point;
Then drop the guaranteed restore point by,
DROP RESTORE POINT NAME_OF_THE_GUARANTEED_RESTORE_POINT;
Now start your database.

RMAN-08137: WARNING: archived log not deleted, needed for standby or upstream capture process

RMAN-08137: WARNING: archived log not deleted, needed for standby or upstream capture process

When we are taking archive log backup with delete then got an error.

RMAN-08137: WARNING: archived log not deleted, needed for standby or upstream capture process

archived log file name=/pac/lci9t1/arch/redo_1_16470_842608348.arc thread=1 sequence=16470

RMAN-08137: WARNING: archived log not deleted, needed for standby or upstream capture process

archived log file name=/pac/lci9t1/arch/redo_1_16471_842608348.arc thread=1 sequence=16471

RMAN-08137: WARNING: archived log not deleted, needed for standby or upstream capture process

SOLUATION:

Cause: The archive logs seems that it is not yet applied in the standby database.

                        Select sequence# , applied from v$archived_log;

Action: Check the standby database and apply the logs manually and the start the deletion process.

              I can delete the archive logs by force using the below command.

      RMAN > delete noprompt force archivelog all;

But I don't want to delete the archives which are not applied in standby  so i'm changing the configuration

              

RMAN> show all;

Old configuration:

CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default

Change as Below:

RMAN> CONFIGURE ARCHIVELOG DELETION POLICY TO SHIPPED TO STANDBY;

new RMAN configuration parameters:

CONFIGURE ARCHIVELOG DELETION POLICY TO SHIPPED TO STANDBY;

new RMAN configuration parameters are successfully stored

RMAN-08591: WARNING: invalid archived log deletion policy


Now check the configuration

RMAN> show all;

CONFIGURE ARCHIVELOG DELETION POLICY TO SHIPPED TO STANDBY;

Now I am able to delete those archivelogs now.

RMAN> delete noprompt archivelog all;

RMAN> crosscheck archivelog all;

Once I deleted those logs, I changed the rman configuration by the default one.

RMAN> CONFIGURE ARCHIVELOG DELETION POLICY CLEAR;

RMAN> Show all;

ORA-00059: maximum number of DB_FILES exceeded

ORA-00059: maximum number of DB_FILES exceeded

SQL> alter tablespace  sysaux add datafile '+JBBOS_DATA' size 15G;

alter tablespace  sysaux add datafile '+JBBOS_DATA' size 15G

*

ERROR at line 1:

ORA-00059: maximum number of DB_FILES exceeded

SQL> show parameter db_files

NAME                                 Type   VALUE

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

db_files                             integer 220

SQL> select count(*) from dba_data_files;

  COUNT(*)

----------

       220

 I have increased the DB_FILES Parameter from 220 t0 250.

SQL> alter system set db_files=250 scope=spfile;

System altered.

SQL>

Changes will be reflect after DB restart, so please plan a restart.

ORA-00206: error in writing (block 512, # blocks 64) of control file

ORA-00206: error in writing (block 512, # blocks 64) of control file

ORA-00202: control file: '/usr/local/opt/oracle/product/11.2.0.3/dbs/snapcf_lsmat7d1.f'

ORA-27072: File I/O error

Additional information: 4

RMAN-08132: WARNING: cannot update recovery area reclaimable file list

Solution:

I followed the below steps to fix the issue.

1] Shutdown the database

2] Restore the control01.dbf from the backup location.

3] Mount the database

4] Recover database until cancel

5] Alter database open resetlogs.

Also, took a complete RMAN cold backup of the database and multiplexed control files and redo logs.

ORA-32000: write to SPFILE requested but SPFILE is not modifiable

ORA-32000: write to SPFILE requested but SPFILE is not modifiable

Solution:  

If you see the spfile parameter in the database it shows as below.
Suppose have you shutdown DB and not able to startup, your spfile is on ASM disk but when you going to start DB then oracle lock the spfile in $ORACLE_HOME/dbs by default.
ex:
SQL> show parameter spfile
NAME                                       TYPE        VALUE
------------------------------------ ----------- ------------------------------
spfile                                       string       /u01/app/oracle/product/11.2.0
                                                             /dbhome_1/dbs/spfiledb11gr2.or
                                                             a
Content of the spfile looks like below.
cat /u01/app/oracle/product/11.2.0/dbhome_1/dbs/spfiledb11gr2.ora
SPFILE='+DATA/db11gr2/spfiledb11gr2.ora' 
1. Move the spfile which is there in $ORACLE_HOME/dbs
ex:
mv spfiledb11gr2.ora spfiledb11gr2.ora.bkp

2. Create the file in $ORACLE_HOME/dbs location with init<SID>.ora name with below content.
SPFILE='<spfile location which is there in ASM>'
ex:
FileName: initdb11gr2.ora
SPFILE='+DATA/db11gr2/spfiledb11gr2.ora'

3. Then restart the database, it will use the spfile which is there in ASM.

4. Now you will be able to modify the spfile with below command.

ALTER SYSTEM set db_recovery_file_dest_size=10G scope=both sid='*' ;

PRCD-1120 : The resource for database racdb could not be found.

oracle@node1 dbs]$ srvctl status database -d racdb

PRCD-1120 : The resource for database racdb could not be found.

PRCR-1001 : Resource ora.racdb.db does not exist

STEP 1:

oracle@node1 dbs]$ srvctl add database -d racdb -o /u01/app/oracle/product/11.2.0.4/dbhome_2

oracle@node1 dbs]$ srvctl config database -d racdb

Database unique name: racdb

Database name:

Oracle home: /u01/app/oracle/product/11.2.0.4/dbhome_2

Oracle user: oracle

STEP 2:

[oracle@node1 dbs]$ srvctl start database -d racdb

PRKO-3119 : Database racdb cannot be started since it has no configured instances.

[oracle@node1 dbs]$ srvctl add instance -d racdb -i racdb1 -n node1

[oracle@node1 dbs]$ srvctl add instance -d racdb -i racdb2 -n node2

[oracle@node1 dbs]$ srvctl config database -d racdb

STEP 3:

[oracle@node1 dbs]$ srvctl start database -d racdb

[oracle@node1 dbs]$ srvctl status database -d racdb

Instance racdb1 is running on node node1

Instance racdb2 is running on node node2

STEP 4:

we need to create spfile

SYS@racdb1> create spfile='+DATA' from pfile='/u01/app/oracle/product/11.2.0.4/dbhome_2/dbs/initracdb1.ora';

File created.

ASMCMD> pwd

+data/racdb/parameterfile

ASMCMD> ls

spfile.280.848177711

ASMCMD> mkalias +data/racdb/parameterfile/spfile.280.848177711 spfileracdb.ora

STEP 5:

[oracle@node1 dbs]$ srvctl modify database -d racdb -p +data/racdb/spfileracdb.ora

[oracle@node1 dbs]$ srvctl config database -d racdb

SYS@racdb1> show parameter spfile

NAME TYPE VALUE

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

spfile string +DATA/racdb/spfileracdb.ora

ORA-01578: ORACLE data block corrupted

ORA-00604: error occurred at recursive SQL level 1

ORA-01110: data file 2: '/pac/ls1fbddb/data/ls1fbddb/sysaux01.dbf'

ORA-01578: ORACLE data block corrupted (file # 2, block # 405)

Solution:  before going to recover block had full DB backup with all archive logs.

we need to check which block is corrupted using below command:

select * from v$database_block_corruption;

you can identified which block is corrupted using DBVerify  utility for e.g.

Now, let me check the same using DBV utility.

dbv file=’ /pac/ls1fbddb/data/ls1fbddb/sysaux01.dbf'

this utility gives some output and DB blocks details.

So, let me check for the corruption using RMAN “validate database”.

Rman target /

Run {validate database;}

Validate all DB datafiles and backup. And check in which datafile block corrupted.

Then u need to execute below command to recover block.

run {blockrecover datafile 2 block 405;}

recovery completed.

ORA-04031: unable to allocate 56 bytes of shared memory

ORA-04031: unable to allocate 56 bytes of shared memory ("streams pool","unknown object","streams pool","fixed allocation callback")

Action: first we need to check shared pool size using below command.

Show parameter shared_;

sho parameters sga_target

select * from ( select POOL, NAME, BYTES, BYTES/1048576 as MBytes from v$sgastat where pool='shared pool' order by BYTES desc ) where rownum <= 25;

select * from V$LIBRARY_CACHE_MEMORY;

try to do flushing of shared_pool and try to restart the system.. it seems your SGA defragmented

====================================

SQL> alter system flush shared_pool;

Action: If the shared pool is out of memory, either use the dbms_shared_pool package to pin large packages, reduce your use of shared memory, or increase the amount of available shared memory by increasing the value of the INIT.ORA parameters "shared_pool_reserved_size" and "shared_pool_size". If the large pool is out of memory, increase the INIT.ORA parameter "large_pool_size".

1 ps -ef|grep oracle

2 find the smon and kill the pid for it

3 SQL> startup mount

ORACLE instance started.

Total System Global Area 4831838208 bytes Fixed Size 2027320 bytes Variable Size 4764729544 bytes Database Buffers 50331648 bytes Redo Buffers 14749696 bytes Database mounted. SQL>

4 SQL> alter system set shared_pool_size=100M scope=spfile;

System altered.

5 SQL> shutdown immediate

ORA-01109: database not open

Database dismounted. ORACLE instance shut down.

6 SQL> startup

ORACLE instance started.

7 SQL> create pfile from spfile;

File created.