OGG-00868 Oracle GoldenGate Capture for Oracle

 Issue :

GoldenGate Extract was getting abended with below error

OGG-00868 Oracle GoldenGate Capture for Oracle, extract01.prm: No data found retrieving if to ignore object

Fix :

Permissions on the Table were changed, Granting the permissions helped fixing this issue

Hope this helps.. 

Oracle : ORA-01186,ORA-01111, ORA-01110, ORA-01157 - MRP Terminated with error 1111 - SOLVED

Datafiles Named "UNNAMED%" 

The datafiles are incorrectly stored under $ORACLE_HOME/dbs location with name 'UNNAMED%' for different reasons. No space available to create the datafiles under the allotted datafile location or if the parameter "STANDBY_FILE_MANAGEMENT" is not set to AUTO

As per Oracle 

STANDBY_FILE_MANAGEMENT : enables or disables automatic standby file management. When automatic standby file management is enabled, operating system file additions and deletions on the primary database are replicated on the standby database. 

Precaution should be taken while using this parameter, Make sure the FILE NAME CONVERT parameter is set correctly. 

1st Case : If the allotted datafiles location is running short on space and a new datafile is added on Primary Database, then the datafile is stored under $ORACLE_HOME/dbs location. 

Fix : Get more space added to the mountpoint and perform the below steps to rename the datafile 

2nd Case : If a new datafile is added on Primary database and the parameter STANDBY_FILE_MANAGEMENT is set to MANUAL, then Oracle Recovery Process(MRP) will get terminated due to wrong parameter setting and create the datafile under $ORACLE_HOME/dbs location.

Fix : Rename the Datafile to correct name and change the parameter STANDBY_FILE_MANAGEMENT

I had the 2nd Case on my Database

Errors From Alert Log file:-

Mon Oct 26 14:02:03 2020

Managed Standby Recovery starting Real Time Apply

Mon Oct 26 14:02:03 2020

Errors in file /usr/app/oracle/diag/rdbms/SDB1/SDB11/trace/SDB11_dbw0_2960.trc:

ORA-01186: file 997 failed verification tests

ORA-01157: cannot identify/lock data file 997 - see DBWR trace file

ORA-01111: name for data file 997 is unknown - rename to correct file

ORA-01110: data file 997: '/usr/app/oracle/product/11.2.0/home/dbs/UNNAMED00997'

File 997 not verified due to error ORA-01157

Mon Oct 26 14:02:04 2020

MRP0: Background Media Recovery terminated with error 1111

Errors in file /usr/app/oracle/diag/rdbms/SDB1/SDB11/trace/SDB11_pr00_342871.trc:

ORA-01111: name for data file 997 is unknown - rename to correct file

ORA-01110: data file 997: '/usr/app/oracle/product/11.2.0/home/dbs/UNNAMED00997'

ORA-01157: cannot identify/lock data file 997 - see DBWR trace file

ORA-01111: name for data file 997 is unknown - rename to correct file

ORA-01110: data file 997: '/usr/app/oracle/product/11.2.0/home/dbs/UNNAMED00997'

Managed Standby Recovery not using Real Time Apply

Fix:-

Check for the files needs to be recovered.

SQL> select file#,name,status from v$datafile where status not in ('ONLINE','SYSTEM');

     FILE# NAME                                                    STATUS

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

       997 /usr/app/oracle/product/11.2.0/home/dbs/UNNAMED00997    RECOVER

Get the datafile from Primary Database

     FILE# NAME                                       STATUS

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

       997 +DATA/PDB/datafile/tbs1.4268.1054563669    ONLINE

- Stop Recovery on Standby Database ( Most Likely it wont be running as Oracle will be terminating it)

SQL > RECOVER MANAGED STANDBY DATABASE CANCEL;

- Make sure STANDBY_FILE_MANAGEMENT is set to MANUAL, if not then run below

SQL> alter system set standby_file_management=MANUAL scope=both sid='*';

System altered.

SQL> alter database create datafile '/usr/app/oracle/product/11.2.0/home/dbs/UNNAMED00997' as '+DATA/SDB1/datafile/tbs1.4268.1054563669';

 *

 ERROR at line 1:

 ORA-01276: Cannot add file

 +DATA/SDB1/datafile/tbs1.4268.1054563669. File has an Oracle Managed Files file name.

 

This Failed for me as we were managing the ASM with OMF. With Oracle Managed Files, you specify file system directories in which the database automatically creates, names, and manages files at the database object level. For example, you need only specify that you want to create a tablespace; you do not need to specify the name and path of the tablespace's data file with the DATAFILE clause. This feature works well with a logical volume manager (LVM).

 

With OMF in place, its easier to rename the dummy datafile

SQL> alter database create datafile '/usr/app/oracle/product/11.2.0/home/dbs/UNNAMED00997' as new;

Database altered.

-- Set the Parameter back to AUTO

SQL> alter system set standby_file_management=AUTO scope=both;

System altered.

-- Start Recovery Process 

SQL> alter database recover managed standby database disconnect from session;

Database altered.

-- This fixed my issue and got the MRP started back and all Datafiles ONLINE.  Hope it resolves your Issue... 

ORA-12537: TNS:connection closed - Oracle RAC 11g and above

 Client was getting Below error while connecting to the RAC database

sqlplus tester@RACDB_1

SQL*Plus: Release 10.2.0.4.0 - Production on Tue Sep 22 11:34:07 2020

Copyright (c) 1982, 2007, Oracle.  All Rights Reserved.

ERROR:

ORA-12537: TNS:connection closed

Issue : 

The Oracle Binary permissions got changed and was not allowing the connections

Current Permissions under Oracle Home

[oracle@host1 ~]$ ls -lrt $ORACLE_HOME/bin/oracle

-rwxr-sr-x 1 oracle asmadmin 243043788 Jun  2 01:12 oracle

It should be set to 6751 and should look like "-rwsr-s--x"

But doing chmod 6751 on oracle binary was not setting the correct permissions

[oracle@host1 bin]$ chmod 6751 oracle

[oracle@host1 bin]$ ls -lrt $ORACLE_HOME/bin/oracle

-rwsr-x--x 1 oracle asmadmin 243043788 Jun  2 01:12 oracle

Running below as RDBMS database Owner user helped , in this case "oracle" user

1) Stop the database instance where the permissions got changed

 srvctl stop instance -d RACDB -i RACDB1

 2) Run as Oracle Database owner, in this case its Oracle OS user.  

[oracle@host1 ~]$ $GRID_HOME/bin/setasmgidwrap o=$ORACLE_HOME/bin/oracle

3) The permissions got changed and resolved the connection issues

[oracle@host1 ~]$ ls -lrt $ORACLE_HOME/bin/oracle

-rwsr-s--x 1 oracle asmadmin 243043788 Jun  2 01:12 oracle

Hope this resolves your issue.. 

GoldenGate 12c/18c and 19c New Features

Oracle GoldenGate Drop Capture : ORA-01338: Other process is attached to LogMiner session

While Dropping the GG capture Process, it was giving below errors

SQL> exec dbms_capture_adm.drop_capture('OGG$CAPTURE');
BEGIN dbms_capture_adm.drop_capture('OGG$CAPTURE'); END;

*
ERROR at line 1:
ORA-01338: Other process is attached to LogMiner session
ORA-06512: at "SYS.DBMS_CAPTURE_ADM_INTERNAL", line 829
ORA-06512: at "SYS.DBMS_CAPTURE_ADM_INTERNAL", line 800
ORA-06512: at "SYS.DBMS_CAPTURE_ADM", line 566
ORA-06512: at line 1

Cause :

An execution of the DROP_CAPTURE procedure drops a logminer session.  The problem was caused by an attempt to drop a logminer session while processes are still attached to it.  The processes are still attached, because the capture has not been stopped.

Solution : 

Stop the GG Capture process and then drop 

SQL> exec dbms_capture_adm.stop_capture('OGG$CAPTURE');

PL/SQL procedure successfully completed.

SQL> exec dbms_capture_adm.drop_capture('OGG$CAPTURE');


PL/SQL procedure successfully completed.


Hope this helps you .. 

Oracle GoldenGate : OGG-00663 OCI Error ORA-00904: "INTCOL#": invalid identifier

Issue :

The GoldenGate Extract was getting abended with below error

020-06-03 12:21:09  ERROR   OGG-00663  OCI Error ORA-00904: "INTCOL#": invalid identifier (status = 904), SQL <SELECT .....l#   FROM TABLE (system.logmnr$col_gg_tabf_public( :sid, :mdh, :object_id, :objv, to_number(:csn) )) 

Fix :

We applied the patch: - 

Patch 17030189: LOGMINER GG DICTIONARY SUPPORT : MISSING ATTRIBUTES

 

cd <Patch_Location>/17030189.11204161018

-- Check for Conflicts 

$OH/OPatch/opatch prereq CheckConflictAgainstOHWithDetail -ph .

-- Apply the Bug Patch

$OH/OPatch/opatch apply

-- Run Post Install

SQL> @?/sqlpatch/17030189/postinstall.sql

-- Then I ran these scripts as well and this time after the db bounce there were no errors:

SQL> drop PACKAGE LOGMNR_DICT_CACHE;

Package dropped.

SQL> @prvtlmcs.plb

Package created.

No errors.

SQL> @$ORACLE_HOME/rdbms/admin/prvtlmcb.plb

SQL> grant EXECUTE,debug on SYSTEM.LOGMNR$KEY_GG_TABF_PUBLIC to GG_USER;

Hope this resolves your issue.. 

Oracle : Query to find ASM Freespace with Redundancy

Below Query will show how much space is available to use incase of High or Normal Redundancy

TOTAL_MB:- Refers to Total Capacity of the Diskgroup
FREE_MB :- Refers to raw Free Space Available in Diskgroup in MB.

FREE_MB = (TOTAL_MB – (HOT_USED_MB + COLD_USED_MB))

REQUIRED_MIRROR_FREE_MB :- Indicates how much free space is required in an ASM disk group to restore redundancy after the failure of an ASM disk or ASM failure group.In exadata it is the disk capacity of one failure group.

USABLE_FILE_MB :- Indicates how much space is available in an ASM disk group considering the redundancy level of the disk group.

Its calculated as :-

USABLE_FILE_MB=(FREE_MB – REQUIRED_MIRROR_FREE_MB ) / 2 –> For Normal Redundancy
USABLE_FILE_MB=(FREE_MB – REQUIRED_MIRROR_FREE_MB ) / 3 –> For High Redundancy


Query to Run:

column total format 999,999 Heading "Total(G)"
column free format 999,999 Heading "Free (G)"
column Mirror_GB format 999,999 Heading "Space Used |for Mirroring(G)"
column Usable_GB format 999,999 Heading "Space Available |to Use(G)"
column pct format 999.0 Heading "% Free |in DG" 
column pct2 format 999.0 Heading "Real % Free |in DG" 
column type format a10
column name format a20
set linesize 200
set colsep '|'
prompt
Prompt "NOTE **** Incase of High or Normal Redundancy the Usable Space is lower than actual shown because of Mirroring *****"
prompt
select name,type, TOTAL_MB/1024 total, FREE_MB/1024 free, REQUIRED_MIRROR_FREE_MB/1024 Mirror_GB, USABLE_FILE_MB/1024 Usable_GB ,100-((total_MB-FREE_MB)/total_mb)*100 pct, 100-((total_MB-USABLE_FILE_MB)/total_mb)*100 pct2  from v$asm_diskgroup;


Sample Output :

"NOTE **** Incase of High or Normal Redundancy the Usable Space is lower than actual shown because of Mirroring *****"


                    |          |        |        |     Space Used |Space Available |% Free |Real % Free
NAME                |TYPE      |Total(G)|Free (G)|for Mirroring(G)|       to Use(G)|  in DG|       in DG
--------------------|----------|--------|--------|----------------|----------------|-------|------------
DATA1              |HIGH      | 260,496|  57,951|          14,472|          14,493|   22.2|         5.6
REDO1              |HIGH      |  65,124|  33,322|           3,618|           9,901|   51.2|        15.2