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

Oracle : Convert TIMESTAMP to SCN and SCN to TIMESTAMP

Oracle : Convert TIMESTAMP to SCN and SCN to TIMESTAMP

In many recovery scenario we need to know our SCN and timestamps.

We can convert this by using the following function

SCN_TO_TIMESTAMP

TIMESTAMP_TO_SCN

We can use this function with help of dual functions. Example of using this is below

1. Convert SCN to Timestamp

SQL> select scn_to_timestamp(2011955) from dual;

SCN_TO_TIMESTAMP(2011955)-----------------------------------------------------05-SEP-18 12.46.20.000000000 PM

2. Convert Timestamp to SCN

SQL> select timestamp_to_scn(to_timestamp('05-09-2018 12:46:21','dd-mm-yyyy hh24:mi:ss')) scn from dual;

SCN----------2011955

Oracle RAC : CRS-4995: The command 'Delete resource' is invalid in crsctl. Use srvctl for this command

Issue :

$crsctl delete resource ora.RACTEST.db
CRS-4995: The command 'Delete resource' is invalid in crsctl. Use srvctl for this command.

Solution

Add “-unsupported” option to the command.  It should apply to “crsctl modify resource” as well.

Eg:
crsctl delete resource ora.RACTEST.db -unsupported

Oracle RMAN : Run Full Backup with Archivelog and Control file

RUN
{
ALLOCATE CHANNEL c1 DEVICE TYPE disk;
ALLOCATE CHANNEL c2 DEVICE TYPE disk;
ALLOCATE CHANNEL c3 DEVICE TYPE disk;
ALLOCATE CHANNEL c4 DEVICE TYPE disk;
  BACKUP
  FORMAT '/opt/sev1/full_backup/%d_%T_%s_%p_FULL'
  DATABASE
  CURRENT CONTROLFILE
  FORMAT '/opt/sev1/full_backup/%d_C_%T_%u'
  PLUS ARCHIVELOG
  FORMAT '/opt/sev1/full_backup/%d_A_%T_%u_s%s_p%p';
release channel c1;
release channel c2;
release channel c3;
release channel c4;
}

oracle : Scheduling ASH reports through Crontab

I was asked to capture ASH reports every 5 minutes for an ongoing Database issue. Below is the process to schedule it through Crontab

Script to capture the ASH reports every 5 Minutes

$ cat ash.ksh
#!/bin/bash
export TZ=US/Central

dateString=`date +%d-%b-%Y_%H:%M:%S`
sqlplus -s / as sysdba << EOD1
define report_type = 'html'
define begin_time = '-5'
define duration = ''
define report_name = '/u01/user/local/reports/ashrpt.${dateString}.html'
@?/rdbms/admin/ashrpt
exit
EOF

To run it through Cron

0,5,10,15,20,22,25,30,32,35,40,45,50,55 * * * * /u01/user/local/ash/ash.ksh > /u01/user/local/reports/log/ash_collect.log 1>/dev/null 2>&1