What does “HANDLECOLLISIONS” do in GoldenGate?

HANDLECOLLISIONS is a parameter in Oracle GoldenGate designed to help manage data conflicts that arise during data replication. It's especially useful during initial data loads or when a specific subset of tables needs to be reloaded on the target database.

How HANDLECOLLISIONS Works: This parameter directs GoldenGate on how to handle specific types of data conflicts that can occur:

• Insert Collision: When an insert operation is attempted, but the row already exists on the target.
• Update Collision: When an update is attempted, but the target row is missing.
• Delete Collision: When a delete operation is attempted, but the row is not found on the target.

HANDLECOLLISIONS takes the following actions:

• For insert collisions, GoldenGate will skip the error if the row already exists.
• For update collisions, it will convert the update to an insert if no row is found.
• For delete collisions, it will skip the error if the row doesn't exist.

This parameter should only be used temporarily during initial loads or first-time synchronization, as keeping it on long-term can mask real data consistency issues. Many users mistakenly leave it on and later face data integrity concerns.

While enabling this parameter can prevent the replicat process from stopping unexpectedly, it’s important to understand that it does not provide alerts about underlying issues in the system. This lack of visibility can mean that critical issues go unnoticed until they escalate. Therefore, you should carefully consider the trade-off between stability and the potential risk of unresolved issues.

Prerequisites for using HANDLECOLLISIONS:

1. HANDLECOLLISIONS requires supplemental logging of all columns for each table, which can add significant overhead to the database. Once HANDLECOLLISIONS is disabled, revert supplemental logging to default settings (SCHEDULINGCOLS).
2. It requires the full "before" image of every column in the trail file, which is enabled through LOGALLSUPCOLS in the Extract.
3. HANDLECOLLISIONS only works with tables that have a primary or unique key, which GoldenGate can use. If used on tables without such a key, it may result in duplicate data during update or delete collisions.

Be sure to turn off HANDLECOLLISIONS once the initial load or synchronization is complete. Keeping it enabled beyond this can hide data consistency issues in production. Disabling HANDLECOLLISIONS after the initial synchronization helps detect and address data problems promptly, preserving system and data integrity and preventing performance and reliability issues in the long term.

GoldenGate : GGSCI CHEATSHEET

## INFO

INFO MANAGER         Provides details of the Manager process

INFO MGR         Also provides details of the Manager process

STATUS MANAGER         This command also display the info of manager

INFO ER *         To info all of the process

INFO EXTTRAIL /u01/app/OGGH/DIRDAT/SE         Display info for specific exttrails

INFO EXTTRAIL *         Display info for all exttrails

INFO RMTTRAIL /u01/app/OGGH/DIRDAT/TE         Display info for specific rmttrails

INFO RMTTRAIL *         Display info for all rmttrails

## REFRESH

REFRESH MANAGER         Reloads from the Manager Parameter file

REFRESH MGR         Reloads from the Manager Parameter file

# SEND

SEND MANAGER CHILDSTATUS         Displays status of processes, started by Manager.

SEND MANAGER CHILDSTATUS DEBUG         Return the ports numbers allocated by the Manager

SEND MANAGER GETPORTINFO         Displays the list of currently allocated ports by MGR process

SEND MANAGER GETPORTINFO DETAIL         Provides info on ports and process assigned to them.

SEND MANAGER GETPURGEOLDEXTRACTS         Retrieves trail purge retention info.

# START

START MANAGER         Starts the Manager Process

START MGR         Starts the Manager Process

# STOP

STOP MGR         Stops the Manager Process

STOP MANAGER !         Stops Manager without asking for user confirmation.

STOP MGR !         Stops Manager without asking for user confirmation.

# Add Command

ADD EXTRACT EXT_SR, Tranlog, Begin Now         Used to specify transaction logs as data source for extract.

ADD EXTRACT EXT_SR, Begin Now, Passive         Specifies the extract to be run in passive mode.

ADD EXTRACT EXT_SR, Extseqno 3 Extrba 1056, Begin Now   Specifies the extract process starting position

ADD EXTRACT EXT_SR, SOURCEISTABLE         Extracts data from data tables for initial loading.

ADD REPLICAT INITLOAD, SPECIALRUN         It will create a special run replicat as task.

ADD REPLICAT REPL_TR1, EXTTRAIL         Create the replicat with the trail

/u01/app/OGGH/DIRDAT/AA

ADD REPLICAT REPL_TR1, EXTTRAIL                     Create the replicat with the trail and the

/u01/app/OGGH/DIRDAT/AA, CHECKPOINTTABLE GGSUSER.OGG_CHECKPOINT  Checkpoint info like the DB table used to save checkpoint info.

ADD REPLICAT REPL_TR1, EXTTRAIL           Create the replicat with the trail and specifying that this

/u01/app/OGGH/DIRDAT/AA, NODBCHECKPOINT                  replicat didn’t write info to DB table info to DB table

ADD EXTTRAIL It is used to create the local trail file for extract process on local system

ADD EXTTRAIL /u01/app/OGGH/DIRDAT/SE, EXTRACT EXT_SR, Create EXTTRAIL with the Prefix”SE”, and the size of 100 mb

MEGABYTES 100

ADD EXTTRAIL /u01/app/OGGH/DIRDAT/SE000008 To create the EXTTRAIL with specific sequence number

ADD RMTTRAIL It is used to create the remote trail files for the extract or pump processes on remote systems

ADD RMTTRAIL /u01/app/OGGH/DIRDAT/TE, EXTRACT PXT_SR, Create RMTTRAIL with the Prefix”TE”, and the size of 100 mb

MEGABYTES 100

ADD RMTTRAIL /u01/app/OGGH/DIRDAT/SE000010 To create the RMTTRAIL with specific sequence number

# ALTER Command

ALTER EXTRACT EXT_SR, BEGIN NOW Instructs extract to start processing

ALTER EXTRACT EXT_SR, BEGIN 20241009 Instructs extract to start processing from specific date

ALTER EXTRACT EXT_SR, ETROLLOVER Extract rolls over to next trail file

ALTER EXTRACT EXT_SR, EXTSEQNO 3, EXTRBA 1056 Alters extract to start from the specific locaton in the trail

ALTER EXTRACT EXT_SR, THREAD 4, BEGIN 20241009 Alters extract thread & start date for RAC

ALTER REPLICAT REPL_TR1, BEGIN NOW Alter replicat to start processing from now

ALTER REPLICAT REPL_TR1, BEGIN 20241009 Alter replicat to start processing from specific date

ALTER REPLICAT REPL_TR1, BEGIN 20241009 10:00:10 Alter replicat to start processing from specific date and time

ALTER REPLICAT REPL_TR1, EXTSEQNO 8 Alter replicat to start from the specific trail file

ALTER REPLICAT REPL_TR1, EXTRBA 254998 Alter replicat to start from the specific location in the trail

ALTER EXTTRAIL ./DIRDAT/SE, EXTRACT EXT_SR, MEGABYTES 50 It is used to change the options of the existing EXTTRAIL file for extract process on local system

ALTER RMTTRAIL ./DIRDAT/TE, EXTRACT PXT_SR, MEGABYTES 50 It is used to change the options of the existing RMTTRAIL file of extract or pump processes on remote systems

# CLEANUP Command

CLEANUP EXTRACT EXT_SR It purges all history of records except last

CLEANUP EXTRACT EXT_SR, SAVE 10 It saves last 10 records and deletes all other

CLEANUP REPLICAT REPL_TR1 It purges all history of records except last

CLEANUP REPLICAT REPL_TR1, SAVE 10 It saves last 10 records and deletes all other

# DELETE Command

DELETE EXTRACT EXT_SR Deletes the extract process

DELETE EXTRACT e* Deletes all extract process whose name starts with e

DELETE EXTRACT e* ! Deletes all extract process whose name starts with e without prompting

DELETE REPLICAT REPL_TR1 Deletes the replicat process

DELETE EXTRACT r* Deletes all replicat process whose name starts with r

DELETE EXTRACT r* ! Deletes all replicat process whose name starts with r without prompting

DELETE EXTTRAIL /u01/app/OGGH/DIRDAT/SE It is used to delete the exttrail assigned to the extract on local system

DELETE RMTTRAIL /u01/app/OGGH/DIRDAT/TE It is used to delete the exttrail for the extract or pump on remote system

# INFO Command

INFO EXTRACT EXT_SR, SHOWCH Display checkpoint info of extract

INFO EXTRACT EXT_SR, DETAIL Display trail info, run history

INFO EXTRACT EXT_SR, TASKS Display extract tasks

INFO REPLICAT *, TASKS It is used to display replicat tasks only.

INFO REPLICAT REPL_TR1 It is used to display the information of replicat.

INFO REPLICAT REPL_TR1, DETAIL It display the detailed information of replicat.

INFO REPLICAT REPL_TR1, SHOWCH It displays the checkpoint table information, from checkpoint file and checkpoint table.

# KILL Command

KILL EXTRACT EXT_SR

KILL REPLICAT REPL_TR1

KILL ER * To kill all of the process

# LAG Command

LAG EXTRACT EXT_SR To find lag for extract EXT_SR”

LAG EXTRACT * To find lag for all of the extract processes

LAG REPLICAT REPL_TR1 To find lag for replicat “REPL_TR1”

LAG REPLICAT * To find lag for all of the replicat processes

LAG ER * To get lag info of all the process

# REGISTER Command

REGISTER EXTRACT EXT_SR LOGRETENTION To register extract EXT_SR”

# UNREGISTER Command

UNREGISTER EXTRACT EXT_SR LOGRETENTION To unregister the extract EXT_SR””

# SEND Command

SEND EXTRACT SRC_EXT1, ROLLOVER         To increment the extract to next file in trail

SEND EXTRACT SRC_EXT1, STOP To stop the extract process

SEND EXTRACT SRC_EXT1, TRANLOGOPTIONS TRANSCLEANUPFREQUENCY 20 For the Oracle RAC, specify the time after which the OGG scan and delete the orphan transactions

SEND EXTRACT SRC_EXT1, SKIPTRANS 1.11.1111 THREAD 2 For skipping the transaction in the Oracle RAC environment

SEND EXTRACT EXT_SR, SHOWTRANS Display the info about the open transactions like checkpoint, extract group name, SCN, Redo log and RB, status etc

SEND EXTRACT EXT_SR, SHOWTRANS COUNT 2 Display the info for two transactions only

SEND REPLICAT REPL_TR1, HANDLECOLLISIONS Enable the handlecollisions option of OGG used for error handling

SEND REPLICAT REPL_TR1, REPORT HANDLECOLLISIONS r_* Generate statical report to replicat report file

SEND REPLICAT REPL_TR1, GETLAG Get the lag info in seconds

SEND ER * To use send command on all of the process

SEND EXTRACT SRC_EXT1, BR BRCHECKPOINT IMMEDIATE This command forces a BR checkpoint after skipping any old running transactions

# START Command

START EXTRACT EXT_SR

START REPLICAT REPL_TR1

START REPLICAT REPL_TR1, ATCSN 2549980 To start the replicat process from the oraclespecific CSN number including the CSN no transaction

START REPLICAT REPL_TR1, AFTERCSN 25472359 To start the replicat process from the oraclespecific CSN number, but ignoring that CSN no transaction

START ER * To start all of the process

# STATS Command

STATS EXT_SR

STATS EXTRACT EXT_SR Stats will be displayed for the extract EXT_SR””

STATS EXTRACT EXT_SR REPORTRATE SEC Display the stats for the fetch operations per sec

STATS EXTRACT EXT_SR, TOTAL, DAILY The total stats is shown since the start of the day

STATS EXTRACT EXT_SR, TOTAL, HOURLY, REPORTRATE MIN, By using comma between the keywords multiple options can be used for the stats command

RESET, REPORTFETCH

STATS REPL_TR1

STATS REPLICAT REPL_TR1 Stats will be displayed for the REPLICAT REPL_TR1””

STATS REPLICAT REPL_TR1 REPORTDETAIL SEC Display the stats for the opertaion that were not replicated due to errors

STATS REPLICAT REPL_TR1, TOTALSONLY *.* Total of all transactions since start

STATS REPLICAT REPL_TR1, TOTAL, DAILY The total stats is shown since the start of the day

STATS REPLICAT REPL_TR1, TOTAL, HOURLY, REPORTRATE MIN, By using comma between the keywords multiple options can be used for the stats command

RESET, NOREPORTDETAIL

STATS ER * To check the stats all of the process

# STATUS Command

STATUS EXTRACT EXT_SR To check the status for extract EXT_SR”

STATUS EXTRACT e* To check the status for all extracts starting with “e”

STATUS REPLICAT REPL_TR1 To check the status for replicat “REPL_TR1”

STATUS REPLICAT r* To check the status for all replicat starting with “r”

STATUS ER * To find status of all the process

# STOP Command

STOP EXTRACT EXT_SR To stop the extract EXT_SR”

STOP EXTRACT e* To stop all of the running extract process whose name start with “e”

STOP EXTRACT * To stop all of the running extract processes

STOP REPLICAT REPL_TR1 To stop the replicat “REPL_TR1”

STOP REPLICAT r* To stop all of the running replicat process whose name start with “r”

STOP REPLICAT * To stop all of the running replicat processes

STOP ER * To stop all of the process

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

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

Crontab – Quick Reference

Setting up cron jobs in Unix, Solaris & Linux
cron is a Unix, solaris, Linux utility that allows tasks to be automatically run in the background at regular intervals by the cron daemon.

What is cron ? – Cron is a daemon which runs at the times of system boot from /etc/init.d scripts. If needed it can be stopped/started/restart using init script or with command service crond start in Linux systems.

Crontab Commands

export EDITOR=vi ;to specify a editor to open crontab file.

crontab -e    Edit crontab file, or create one if it doesn’t already exist.
crontab -l    crontab list of cronjobs , display crontab file contents.
crontab -r    Remove your crontab file.
crontab -v    Display the last time you edited your crontab file. (This option is only available on a few systems.)

Crontab file
Crontab syntax :

A crontab file has five fields for specifying day , date and time followed by the command to be run at that interval.

# ┌───────────── minute (0 - 59)
# │ ┌───────────── hour (0 - 23)
# │ │ ┌───────────── day of the month (1 - 31)
# │ │ │ ┌───────────── month (1 - 12)
# │ │ │ │ ┌───────────── day of the week (0 - 6) (Sunday to Saturday;
# │ │ │ │ │                                   7 is also Sunday on some systems)
# │ │ │ │ │
# │ │ │ │ │
# * * * * * command to execute

* in the value field above means all legal values as in braces for that column.

The value column can have a * or a list of elements separated by commas. An element is either a number in the ranges shown above or two numbers in the range separated by a hyphen (meaning an inclusive range)

Crontab Examples

A line in crontab file like below removes the tmp files from /home/someuser/tmp each day at 5:15 PM.

15     17     *     *     *         chmod 777 /home/user/files/*

Crontab every hour

This is most commonly used for running cron every hour and executing a command after an interval of one hour.

crontab format every hour is simple to have hour field as *  which runs every hour as the clock switches to new hour.  if you want to run it at the beginning of hour the minute filed needs to be 0 or any other minutes when you want to run it at a specific minute of the hour.
cron every hour to run at the beginning of the hour.
00     *     *     *     *         chmod 777 /home/user/files/*

cron every hour to run at 15 minute of an hour..
15     *     *     *     *         chmod 777 /home/user/files/*

Cron every minute

To run cron every minute keep the minutes field as * , as minute changes to new minute cron will be executed every minute.  if you want to run it continuously every hour then the hour field also needs to have value of  * .

*     *     *     *     *         chmod 777 /home/user/files/*

if you want to run a script every minute at specific hour, change the value of hour field to specific value such as 11th hour.

*     11     *     *     *          chmod 777 /home/user/files/*

More crontab examples

Changing the parameter values as below will cause this command to run at different time schedule below :
min hour day/month month day/week Execution time
30 0 1 1,6,12 * — 00:30 Hrs  on 1st of Jan, June & Dec.

0 20 * 10 1-5 –8.00 PM every weekday (Mon-Fri) only in Oct.

0 0 1,10,15 * * — midnight on 1st ,10th & 15th of month

5,10 0 10 * 1 — At 12.05,12.10 every Monday & on 10th of every month

Note : If you inadvertently enter the crontab command with no argument(s), do not attempt to get out with Control-d. This removes all entries in your crontab file. Instead, exit with Control-c.

Crontab Environment

cron invokes the command from the user’s HOME directory with the shell, (/usr/bin/sh).
cron supplies a default environment for every shell, defining:

HOME=user’s-home-directory
LOGNAME=user’s-login-id
PATH=/usr/bin:/usr/sbin:.
SHELL=/usr/bin/sh

Users who desire to have their .profile executed must explicitly do so in the crontab entry or in a script called by the entry.

Disable Email

By default cron jobs sends a email to the user account executing the cronjob. If this is not needed put the following command At the end of the cron job line .

>/dev/null 2>&1

Generate log file

To collect the cron execution execution log in a file :

15 17 * * * chmod 777 /home/user/file/* > /home/user/file/perm.log

Crontab file location

User crontab files are stored by the login names in different locations in different Unix and Linux flavors. These files are useful for backing up, viewing and restoring but should be edited only with crontab command by the users.

• Mac OS X
/usr/lib/cron/tabs/
• BSD Unix 
/var/cron/tabs/
• Solaris, HP-UX, Debian, Ubuntu
/var/spool/cron/crontabs/
• AIX, Red Hat Linux, CentOS, Ferdora
/var/spool/cron/

Message file RMAN.msb not found

Was getting below error when I ran rman


$ rman target /
Message file RMAN<lang>.msb not found
Verify that ORACLE_HOME is set properly


ORACLE_HOME was set correctly and other environments were set as well After looking further into this, we found the below files missing


$ORACLE_HOME/rdbms/mesg/rmanus.msg
$ORACLE_HOME/rdbms/mesg/rmanus.msb


Copied those two files from another working server and the issue got resolved.

Oracle : ORA-30554: function-based index XDB.XDB$ACL_XIDX is disabled

ACL creation failing with

ORA-30554: function-based index XDB.XDB$ACL_XIDX is disabled


BEGIN 
    DBMS_NETWORK_ACL_ADMIN.create_acl(acl => 'Resolve_Access.xml',
         description  => 'Resolve Network Access using UTL_INADDR', 
                                      principal    => 'SYS', 
                                      is_grant     => TRUE, 
                                      privilege    => 'resolve', 
                                      start_date   => NULL, 
                                      end_date     => NULL 
                                                          ); 

    COMMIT; 
END; 
/
BEGIN
*
ERROR at line 1:
ORA-30554: function-based index XDB.XDB$ACL_XIDX is disabled
ORA-06512: at "SYS.DBMS_NETWORK_ACL_ADMIN", line 258
ORA-06512: at line 2

Fix :

SQL> select owner, index_name, status, domidx_status, domidx_opstatus,funcidx_status from dba_indexes where domidx_opstatus is not null;

OWNER                          INDEX_NAME                     STATUS   DOMIDX_STATU DOMIDX FUNCIDX_
------------------------------ ------------------------------ -------- ------------ ------ --------
XDB                            XDBHI_IDX                      VALID    VALID        VALID  ENABLED
XDB                            XDB$ACL_XIDX                   VALID    VALID        VALID  DISABLED

SQL> alter index XDB.XDB$ACL_XIDX rebuild;

Index altered.

SQL> select owner, index_name, status, domidx_status, domidx_opstatus,funcidx_status from dba_indexes where domidx_opstatus is not null;

OWNER                          INDEX_NAME                     STATUS   DOMIDX_STATU DOMIDX FUNCIDX_
------------------------------ ------------------------------ -------- ------------ ------ --------
XDB                            XDBHI_IDX                      VALID    VALID        VALID  ENABLED
XDB                            XDB$ACL_XIDX                   VALID    VALID        VALID  ENABLE

No Rerun the Create ACL command

BEGIN 
    DBMS_NETWORK_ACL_ADMIN.create_acl(acl => 'Resolve_Access.xml',
         description  => 'Resolve Network Access using UTL_INADDR', 
                                      principal    => 'SYS', 
                                      is_grant     => TRUE, 
                                      privilege    => 'resolve', 
                                      start_date   => NULL, 
                                      end_date     => NULL 
                                                          ); 

    COMMIT; 
END; 

PL/SQL procedure successfully completed.

Oracle : Check who is locking User

There are different ways to find who is locking the USER depending on what level of auditing is set on the database

For OS level Auditing

SQL> show parameter audit_trail

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
audit_trail                          string      OS

$cd <audit-log-location>
$cat *.aud | grep -i <user> | grep 1017

For DB level Auditing

SQL> show parameter audit_trail

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
audit_trail                          string      DB


set lines 200
set pages 200
column USERNAME format a12
column OS_USERNAME format a12
column USERHOST format a25
column EXTENDED_TIMESTAMP format a40

SELECT USERNAME, OS_USERNAME, USERHOST, EXTENDED_TIMESTAMP
FROM SYS.DBA_AUDIT_SESSION WHERE returncode != 0 and username = '&Account_Locked'
and EXTENDED_TIMESTAMP > (systimestamp-1) order by 4 desc
/

ORACLE RAC : TERMINATING THE INSTANCE DUE TO ERROR 304

After refreshing my QA database using RMAN DUPLICATE, my instance startup was failing with below error


USER (ospid: 60897): terminating the instance due to error 304
Instance terminated by USER, pid = 60897
Wed Mar 06 02:14:34 2019
Starting ORACLE instance (normal)

Looking into the spfile, I noticed the the database was pulling wrong instance_number and thread numbers even though the DB configuration was correct

$ srvctl config database -d oradb
Database unique name: oradb
Database name: oradb
Oracle home: /opt/app/oradb/oracle/product/11.2.0.4
Oracle user: oradb
Spfile: +oradb_DATA/oradb/spfileoradb.ora
Domain: db.abc.com
Start options: open
Stop options: immediate
Database role: PRIMARY
Management policy: AUTOMATIC
Server pools: oradb
Database instances: oradb1,oradb2
Disk Groups: oradb_DATA,oradb_FRA,oradb_REDO1,oradb_REDO2
Mount point paths: 
Services: oradb_1_2.db.abc.com,oradb_2_1.db.abc.com
Type: RAC
Database is administrator managed

From the pfile I created from the current spfile I could see 

*.instance_number=2
*.thread=2

To resolve this, bring down the complete database and just start the failing instance, in our case instance 1

srvctl start instance -d oradb -i oradb1

Once the instance is started, login to SQL and run below

SQL> alter system set instance_number=1 scope=spfile sid='oradb1';

System altered.

SQL> alter system set thread=1 scope=spfile sid='oradb1';

System altered.

SQL> alter system set undo_tablespace='UNDO01' sid='oradb1';

System altered.

shutdown the instance and start the complete database

SQL> shutdown immediate

srvctl start database -d oradb


Hope this resolves your issue.