Rizwan's Oracle Blog

OEM 13c: The Next-Gen OPATCH version 13.9 and issues on Solaris 10 - "opatch: test: unknown operator =="

2017-01-23T13:19:00.000+03:00

To those running OEM 13c - you may have noticed the bug "ORA-06553: PLS-306: wrong number or types of arguments in call to 'REPORT_SQL_MONITOR_LIST'" causing the SQL Monitoring page to fail.

Rejoice - the fix is now available "Oracle® Management Service 13c Release 2 (13.2.0.0.0) System Patch Patch for Bug # 25197714 - Enterprise Manager for OMS Plug-ins 13.2.0.0.0"

Readme --> HERE

This post is to do mostly with "Pre-requisite #5":
-----
Ensure that you have the latest version of OPatch 13.9.0.0.0 and OMSPatcher 13.8.0.0.1 on all OMS instance platform homes.
If you are not sure about the OPatch version you have, or to download the latest version, follow the instructions outlined in the My Oracle Support note 224346.1 available at:
https://support.oracle.com/CSP/main/article?cmd=show&type=NOT&id=224346.1

If you are not sure about the OMSPatcher version you have, or to download the latest version, follow the instructions outlined in the My Oracle Support note 2203007.1 available at:
https://support.oracle.com/epmos/faces/DocumentDisplay?id=2203007.1
----

OMSPatcher upgrade is simple - download --> unzip --> replace folder [the usual (but now historical) procedure used to upgrade OPatch].

Hang on, what do you mean by "historical" procedure? What's new with OPatch upgrade?

OPatch now comes bundled in a 'jar' file that needs to be installed using at least JRE 1.7. Luckily, OMS 13c installation comes built in with this version of JAVA.

Command is as below:
[oracle->ogcoms]/u01/software/6880880]$OMS_HOME/oracle_common/jdk/bin/java -jar -d64 opatch_generic.jar -silent oracle_home=$OMS_HOME

(-d64 parameter is required if you are installing OPatch in a 64 bit setup)

Should work like a charm for any other OS, except Solaris (as of the version 13.9).

Issue:
Upgrade was successful without any errors. But, "opatch" commands were failing:

[oracle->ogcoms]/u01/app/oracle/middleware13c/OPatch]./opatch
./opatch: test: unknown operator ==
[oracle->ogcoms]/u01/app/oracle/middleware13c/OPatch]./opatch version
./opatch: test: unknown operator ==

This is a bug! (yes, yet another bug!)

Bug 16393828 [ - OPATCH.SH FAILS ON SOLARIS
Bug 16393904 [ - OPATCH-NEXTGEN: OPATCH NOT WORKING IN SOLARIS SPARC 10

opatch is an ascii file, so looking for the text "==" in the file gives you the faulty line:

[oracle->ogcoms]/home/oracle/OPatch]grep == opatch
# ==> yes: ready to 'callOPatch'
# ==> no: go to #2
# ==> yes: ready to 'callOpatch'
# ==> no: go to #3
# ==> yes: ready to 'callOpatch'
# ==> no: Error and Exit
if [ "$PLATFORM" == "Linux" -a "$ARCH" == "x86_64" ];then
# JDK 8: MaxPermSize <=== replaced by ===> MaxMetaspaceSize: default = 128M
# if JDK8 and has flag 'MaxPermSize', replace MaxPermSize <==> MaxMetaspaceSize

Faulty line is:

if [ "$PLATFORM" == "Linux" -a "$ARCH" == "x86_64" ];then

Solution:
Issue with Solaris server is that "==" will not work, therefore I modified it to "=" in the opatch and opatch.pl files:

if [ "$PLATFORM" = "Linux" -a "$ARCH" = "x86_64" ];then

I tried opatch command again and voola - we have a perfectly working OPatch!

[oracle->ogcoms]/u01/app/oracle/middleware13c/OPatch]./opatch version
OPatch Version: 13.9.1.0.0

OPatch succeeded.

I miss the "non-buggy", simple unzip and replace OPatch directory method!

ORA-600 [ktu_format_nr no disk align] - Oracle 12c Database Migration stuck at Convert Datafile of UNDO tablespace

2017-01-23T12:44:00.004+03:00

Bugs Bugs Bugs... 3rd bug this month!

So, I was performing a simple cross-platform Database Migration from "Solaris[tm] OE (64-bit)" to "HP-UX IA (64-bit)". As both the source and the target OS runs on the BIG endian format, the migration using "RMAN transportable database" is the simplest - requiring to convert only the datafiles/tablespaces containing some kind of UNDO segment.

Steps for this migration can be found at:
Cross-Platform Database Migration (across same endian) using RMAN Transportable Database (Doc ID 1401921.1)

Now, I had 1 file from SYSTEM tablespace and a few from UNDOTBS_01 to convert.
Most of them converted without a hickup, but one file "undotbs_01_01.dbf" would get stuck while conversion.

RMAN> CONVERT
DATAFILE '/ofdmdb/STAGE_DIR/undotbs_01_01.dbf'
FORMAT '/ofdmdb/undotbs_01_01.dbf'
FROM PLATFORM 'Solaris[tm] OE (64-bit)' ;

Starting conversion at target at 18-JAN-17
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile conversion
input file name=/ofdmdb/STAGE_DIR/undotbs_01_01.dbf

--- -- - Stuck here since 2 hours and counting...

I check the alert.log and found the below:

Thu Jan 19 15:06:46 2017
Errors in file /u01/app/oracle/diag/rdbms/ofdm/ofdm/trace/ofdm_ora_16742.trc (incident=65019):
----->>> ORA-00600: internal error code, arguments: [ktu_format_nr no disk align], [104], [33281], [45056], [], [], [], [], [], [], [], []
Incident details in: /u01/app/oracle/diag/rdbms/ofdm/ofdm/incident/incdir_65019/ofdm_ora_16742_i65019.trc
Thu Jan 19 15:06:49 2017
Dumping diagnostic data in directory=[cdmp_20170119150649], requested by (instance=1, osid=9513668), summary=[incident=65019].
Use ADRCI or Support Workbench to package the incident.
See Note 411.1 at My Oracle Support for error and packaging details.
Thu Jan 19 15:07:08 2017
Sweep [inc][65019]: completed
Sweep [inc2][65019]: completed

On searching for related documents I found that this was a Bug and there were no straight patches available yet:

Bug 24332831 - RMAN: ORA-600 [ktu_format_nr no disk align] while convert from HP-UX ( Doc ID 24332831.8 )
or a more accessible post:
http://remidian.com/2012/07/transporting-an-oracle-database-to-another-os-platform-the-fastest-way

I then had to raise a ticket with Oracle Support and they prepared a bugfix patch (Patch 24332831 for HP-UX) for us based on our version and patch level.

I then applied the patch using the usual "opatch apply", and then tried to convert the file again:

oracle->sysdev]/u01/software/bugfix/24332831>rman target /
Recovery Manager: Release 12.1.0.2.0 - Production on Mon Jan 23 09:06:45 2017
Copyright (c) 1982, 2014, Oracle and/or its affiliates. All rights reserved.
connected to target database: OFDM (not mounted)

RMAN> CONVERT DATAFILE '/ofdmdb/STAGE_DIR/undotbs_01_01.dbf'
FORMAT '/ofdmdb/undotbs_01_01.dbf'
FROM PLATFORM 'Solaris[tm] OE (64-bit)' ;2> 3>

Starting conversion at target at 23-JAN-17
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=122 device type=DISK
channel ORA_DISK_1: starting datafile conversion
input file name=/ofdmdb/STAGE_DIR/undotbs_01_01.dbf
converted datafile=/ofdmdb/undotbs_01_01.dbf
channel ORA_DISK_1: datafile conversion complete, elapsed time: 00:00:15
Finished conversion at target at 23-JAN-17

Finally, I proceeded with the next steps in migration without another hiccup.

Oracle 12c RAC: A step by step manual PSU patching (October 2016) without using opatchauto

2016-11-21T16:11:00.000+03:00

Why would you want to 'not' use OPATCHAUTO? Maybe because there's no bash shell in your server due to some reason, or you just want to avoid any complications by opatchauto, or simply you like a more granular control over the patching process.

I primarily work on Oracle RAC setups on HP-UX servers - where the bash shell is not installed (and cant be due to policy). You will agree, life is much easier on Linux. Therefore I must make use of the old and dependable 'opatch' utility!

About the October 2016 patch:
Patch File: p24412235_121020_HPUX-IA64.zip
Contents: DB PSU patch #24006101 and OCW PSU patch #23854735
The unzipped patch file path: /u01/software/psuoct2016/12cGI/24412235

About the environment:
- In the below example, I am applying the patch on a 2-node RAC in a rolling mode. I will be sharing only the tested steps and not the outputs.

- I usually have 4 putty sessions open on each node:

GRID_HOME environment variables set
RDBMS_HOME environment variables set
Monitoring the activity in the alert.log of the crs
Monitoring the activity in the alert.log of the database

- The GRID and RDBMS home owner is 'oracle' in this example.

Prerequisites:

Make sure your have the latest OPatch in both the RDBMS_HOME and GRID_HOME.

$ opatch version

OPatch Version: 12.2.0.1.8

OPatch succeeded.

Let's begin...

Node 1:

1. As RDBMS owner (oracle), shutdown the instance on node 1 and stop all services on the RDBMS_HOME

$ srvctl stop instance -d testdb -i testdb1

# NOTE: Make sure the shutdown database was clean by verifying the message "Instance shutdown complete" in the alert log.

$ $ORACLE_HOME/bin/srvctl stop home -o $RDBMS_HOME -s /tmp/status -n node1

2. Unlock the CRS as ‘root’ user:

export GRID_HOME=/u01/app/12.1.0/grid

$GRID_HOME/crs/install/rootcrs.pl -prepatch

3. As Grid Infrastructure Owner (oracle), apply the OCW patch on the Grid Home:

$GRID_HOME/OPatch/opatch napply -oh $GRID_HOME -local /u01/software/psuoct2016/12cGI/24412235/23854735

4. As Grid Infrastructure Owner (oracle), apply the DB PSU patch on the Grid Home:
$GRID_HOME/OPatch/opatch napply -oh $GRID_HOME -local /u01/software/psuoct2016/12cGI/24412235/24006101

5. As RDBMS Owner (oracle), apply the OCW PSU patch on the RDBMS Home:
$ cd /u01/software/psuoct2016/12cGI/24412235/23854735/custom/scripts
$ ./prepatch.sh
$ $ORACLE_HOME/OPatch/opatch napply -oh $RDBMS_HOME -local /u01/software/psuoct2016/12cGI/24412235/23854735

6. As RDBMS Owner (oracle), apply the DB PSU patch on the RDBMS_HOME and run ‘postpatch’:
$ORACLE_HOME/OPatch/opatch napply -oh $RDBMS_HOME -local /u01/software/psuoct2016/12cGI/24412235/24006101
cd /u01/software/psuoct2016/12cGI/24412235/23854735/custom/scripts
./postpatch.sh

7. As root:
/u01/app/12.1.0/grid/rdbms/install/rootadd_rdbms.sh
$GRID_HOME/crs/install/rootcrs.pl -postpatch

Executing the final command "rootcrs.pl -postpatch" with lock the crs files and start the cluster services

Wait for 2-3 minutes – till all CRS components are started and DB is in OPEN state
/u01/app/12.1.0/grid/bin/crsctl stat res -t

8. As ‘oracle’:
$ORACLE_HOME/bin/srvctl start home -o $RDBMS_HOME -s /tmp/status1 -n node1
(No Output)

9. As ‘oracle’:
ps -ef | grep pmon

10. Verify the Patches on both GI and RDBMS HOMES:
$ORACLE_HOME/OPatch/opatch lsinventory -oh $RDBMS_HOME
$ORACLE_HOME/OPatch/opatch lsinventory -oh $RDBMS_HOME | grep 24007012
$ORACLE_HOME/OPatch/opatch lsinventory -oh $RDBMS_HOME | grep 23854735
$ORACLE_HOME/OPatch/opatch lsinventory -oh $GRID_HOME
$ORACLE_HOME/OPatch/opatch lsinventory -oh $GRID_HOME | grep 24007012
$ORACLE_HOME/OPatch/opatch lsinventory -oh $GRID_HOME | grep 23854735

11. Check the status of CRS components:
$ crs_stat -t

Node 2:

1. As RDBMS owner (oracle), shutdown the instance on node 1 and stop all services on the RDBMS_HOME

$ srvctl stop instance -d testdb -i testdb2

# NOTE: Make sure the shutdown database was clean by verifying the message "Instance shutdown complete" in the alert log.

$ $ORACLE_HOME/bin/srvctl stop home -o $RDBMS_HOME -s /tmp/status -n node2

Follow steps 2-7 from the Node 1 section

8. As ‘oracle’:
$ORACLE_HOME/bin/srvctl start home -o $RDBMS_HOME -s /tmp/status1 -n node2
(No Output)

9. As ‘oracle’:
ps -ef | grep pmon

10. Verify the Patches on both GI and RDBMS HOMES:
$ORACLE_HOME/OPatch/opatch lsinventory -oh $RDBMS_HOME
$ORACLE_HOME/OPatch/opatch lsinventory -oh $RDBMS_HOME | grep 24007012
$ORACLE_HOME/OPatch/opatch lsinventory -oh $RDBMS_HOME | grep 23854735
$ORACLE_HOME/OPatch/opatch lsinventory -oh $GRID_HOME
$ORACLE_HOME/OPatch/opatch lsinventory -oh $GRID_HOME | grep 24007012
$ORACLE_HOME/OPatch/opatch lsinventory -oh $GRID_HOME | grep 23854735

11. Check the status of CRS components:
$ crs_stat -t

Post -Patch Instructions:

On Node 1:
cd $RDBMS_HOME/OPatch
./datapatch -verbose

Hope this has helped you in some way...
If yes, please don't forget to add a comment!

Oracle RAC 12c - Simple steps to manually rollback a PSU Patch from a node

2016-11-20T09:56:00.000+03:00

I will use the latest October 2016 PSU patch as a test case for this post...

Environment:
Server/OS - HP UX Itanium (11.31)
2 Node RAC - no ACFS, no shared homes, ASM is used

Method of applying patches -
Fully Manual
Non-Rolling
Why?
- better control,
- non-availability of bash in our HP UX servers which is required to run opatchauto

What do should know about the October PSU patch for RAC?
OCW PSU patch number -- 23854735
DB PSU patch number -- 24006101

Below are very very simple steps to rollback the patches in your environment:

DB HOME:
opatch nrollback -local -id 24006101 -oh $ORACLE_HOME
cd /u01/software/psuoct2016/12cGI/24412235/23854735/custom/scripts
./prepatch.sh
opatch nrollback -local -id 23854735 -oh $ORACLE_HOME
./postpatch.sh

GRID HOME:
$GRID_HOME/crs/install/rootcrs.pl -prepatch [-nonrolling]
opatch nrollback -local -id 24006101 -oh $GRID_HOME
opatch nrollback -local -id 23854735 -oh $GRID_HOME
$GRID_HOME/crs/install/rootcrs.pl -postpatch [-nonrolling]

Use -nonrolling only if you have patched using the nonrolling mode.

Hope this helps!
Cheers

EM 13c: ORA-06553: PLS-306: wrong number or types of arguments in call to 'REPORT_SQL_MONITOR_LIST'

2016-11-16T11:18:00.003+03:00

[UPDATE: Bugfix Bundle Patch is out: OMS Patch 25197714 ]

Enterprise Manager 13c Installation... Check!
Agents pushed to target servers... Check!
Cluster and Database targets discovered and configured... Check!

Aaah! EM 13c looks and feels brilliant...

Starting to carry out the usual daily health checks on the core-banking database...
Performance page looks great...
No Locking/Blocking either...
Heading on to the SQL Monitoring page.... WHAAAAT ???
ORA-06553: PLS-306: wrong number or types of arguments in call to 'REPORT_SQL_MONITOR_LIST'

After a successful deployment of Oracle Enterprise Manager 13.2, I've encountered a serious BUG in the 1st hour of my EM 13c experience!

DBAs usually depend on the "SQL Monitoring" page of the OEM to communicate the long running active queries to the App Support Teams and take necessary actions. And, the graphs always make a better impact...

So naturally, on searching for a solution on Oracle Support, I came across the below document:

EM13c: Accessing SQL Monitoring Raises ORA-06553: PLS-306: wrong number or types of arguments in call to 'REPORT_SQL_MONITOR_LIST' (Doc ID 2199723.1)

Apparently, if you are accessing a database with a lower version that 12c, you are most likely to hit this bug!
I then raised a ticket with the support, and the assigned engineer informed me that the fix is expected in the next bundle patch releasing anytime between last week of Nov and 1st week of Dec 2016.

Stay tuned for more...

EM 13c: What happens when you don't run root.sh after the Agent installation?

2016-11-15T12:19:00.002+03:00

Admit it, there are times when you skip execution of the little "root.sh" script, especially when you don't have direct or indirect root access. Also, you may skip it when it was already executed for a previous installation on the same server.

Obviously, root.sh is "the most" important part of a RAC setup as it configures the CRS services and brings them up, but why in OEM 13c? Hmmm...

As a DBA, it's always a best practice to check what's different, or what's new in the root.sh files when installing a newer version of any Oracle based software. That's what I did when I implemented the OEM 13c's Oracle Management Server (OMS) and deployed agents across numerous Unix, Solaris and Windows servers that were hosting Oracle Databases.

We all know that the Enterprise Manager is no longer restricted to just monitoring databases. Especially from 12c, the Enterprise Manager has been Cloudified (oooo). It can monitor and manage almost any infrastructure hardware or software (if configured). It's a complete ENTERPRISE Cloud Management Solution! Why do I say this now? Wait for it...

So, after pushing Agents on the numerous servers, I skipped executing the root.sh, as I wanted to find out if there were any impact on any of the proceeding steps.

Everything was smooth, I discovered cluster and database targets, and added them to the EM. I was able to login into these targets and perform tasks as normal. I left for home, watched the Walking Dead, and when I returned in the morning, I noticed something interesting:

The Database Targets were stuck in "PENDING STATE"!

Initially, I thought maybe a network issue causing the Upload to pause. I then did the usual things to try and fix that problem:

/u01/app/oracle/agent13c/agent_inst/bin/emctl stop agent
/u01/app/oracle/agent13c/agent_inst/bin/emctl clearstate agent
/u01/app/oracle/agent13c/agent_inst/bin/emctl start agent
/u01/app/oracle/agent13c/agent_inst/bin/emctl upload agent
/u01/app/oracle/agent13c/agent_inst/bin/emctl status agent

But it didn't work. I put on my Oracle Support socks and Google hat, and started searching for an answer. Looked at similar problems and solutions provided by my favorite Oracle Blogs - Pythian and DBAKevlar

Nada! didn't solve my problem.

Last resort - I applied the root.sh on one of the servers, and voola! In a few minutes the database targets of that server started showing Up status!

Ohhh ! I always wanted to know what happens if I don't run the root.sh!

Later, I checked if I can get the same information from somewhere else. So I headed to on of the Agent's page and guess what I found?

ERROR: NMO not setuid-root (Unix-only). What does that mean? And why does it require root.sh to be executed?

NMO is an executable file in the sbin folder of the Agent's Home. Some of the executables in this folder need to be owned by root in order for the "Enterprise" manager to be able to work for the whole "Enterprise". Clearly, the "oracle" user ownership and permissions are nearly not enough.

Before root.sh:

oracle@xxxxx /u01/app/oracle/agent13c/agent_13.2.0.0.0/sbin> ls -l
total 68594
-rwx--x--x 1 oracle dba 22840 Sep 30 23:29 nmb.0
-rwx--x--x 1 oracle dba 114768 Sep 30 23:29 nmgsshe.0
-rwx--x--x 1 oracle dba 100528 Sep 30 23:31 nmhs.0
-rwx--x--x 1 oracle dba 8695880 Sep 30 23:29 nmo.0
-rwx--x--x 1 oracle dba 8608224 Sep 30 23:29 nmoconf
-rwx--x--x 1 oracle dba 8617024 Sep 30 23:31 nmopdpx.0
-rwx--x--x 1 oracle dba 8617024 Sep 30 23:31 nmosudo.0
-rwx------ 1 oracle dba 87776 Sep 30 23:31 nmr.0
-rw-r----- 1 oracle dba 9615 Aug 1 15:37 nmr_macro_list
-rwx------ 1 oracle dba 14976 Sep 30 23:31 nmrconf

After root.sh:

oracle@xxxxx /u01/app/oracle/agent13c/agent_13.2.0.0.0/sbin> ls -l
total 203890
-rwsr-x--- 1 root dba 87224 Nov 14 10:30 nmb
-rwx--x--x 1 oracle dba 87224 Oct 1 06:53 nmb.0
-rwxr-xr-x 1 root dba 78656 Nov 14 10:30 nmgsshe
-rwx--x--x 1 oracle dba 78656 Oct 1 06:53 nmgsshe.0
-rwsr-x--- 1 root dba 99680 Nov 14 10:30 nmhs
-rwx--x--x 1 oracle dba 99680 Oct 1 06:59 nmhs.0
-rwsr-x--- 1 root dba 13002232 Nov 14 10:30 nmo
-rwx--x--x 1 oracle dba 13002232 Oct 1 06:53 nmo.0
-rwx------ 1 root sys 13002232 Nov 14 10:30 nmo.new.bak
-rw-r----- 1 root dba 188 Nov 14 10:30 nmo_public_key.txt
-rwx--x--x 1 oracle dba 12857336 Oct 1 06:53 nmoconf
-rwxr-xr-x 1 root dba 12862728 Nov 14 10:30 nmopdpx
-rwx--x--x 1 oracle dba 12862728 Oct 1 06:59 nmopdpx.0
-rwxr-xr-x 1 root dba 12862728 Nov 14 10:30 nmosudo
-rwx--x--x 1 oracle dba 12862728 Oct 1 06:59 nmosudo.0
-rwsr-x--- 1 root dba 148312 Nov 14 10:30 nmr
-rwx------ 1 oracle dba 148312 Oct 1 06:59 nmr.0
-rwx------ 1 root sys 148312 Nov 14 10:30 nmr.new.bak
-rw-r----- 1 root dba 9615 Aug 1 22:37 nmr_macro_list
-rwx------ 1 oracle dba 80648 Oct 1 06:59 nmrconf

I hope you see the difference in permissions and ownership of the files.

Once I applied the root.sh on the remaining servers, all the cluster and database targets were up and running.

Let me know if you have any questions or a conflict of thoughts :) !

Cheers,

Getting started in Oracle Cloud - Database as a Service

2016-05-12T16:36:00.004+03:00

Cloud - the recent talk of the tech-town. We all know Oracle has fired up the sales teams all over the world across all spheres of Businesses with a single goal - convince the customer to move to Cloud now !

Well, as technical beasts - we are more interested in how we can transition into this wave of Cloud computing, especially if you are an Oracle DBA.

Oracle is currently giving you 1 month free trial of the Cloud Services - so please make hay while the sun shines (that's lame :P)...

To begin with, register HERE

Under Platform --> Data --> Database --> Try it --> Database as a Service --> Star Trial

P.S. - I had some difficulty to register myself as the Request Code that is sent through SMS didn't arrive at all. For any difficulty - please click the "Chat" icon to get immediate support from Oracle. They are pretty helpful.

You will receive an email with the subject "Oracle Cloud Access Details" which will give you direct access links to the dashboard.

A great place to start with following the instructions here - DBaaS Quick Start Guide

This is a simple guide that will guide you through the simple task of creating your 1st Database Instance as a Cloud Service.

This exercise will also give you an insight as to how easy it is to provision either a standalone database or a 2-node RAC database.

Happy Clouding!

Unable to empty RECYCLEBIN even after PURGE DBA_RECYCLEBIN

2016-02-07T13:12:00.000+03:00

Recently, while upgrading a database from 11g to 12c, I faced an issue where the RECYCLEBIN just wouldn't empty even after multiple attempts at PURGE DBA_RECYCLEBIN command. Obviously, as you may have already guessed - this is a Bug!

As usual the 1st line of solution is the Oracle Support documentation, and this is what saved the day.

Unable To Empty or delete rows from Sys.recyclebin$ which is causing dbua (upgrade) stopped and purge dba_recyclebin not helping (Doc ID 1910945.1)

Solution is to manually truncate the recyclebin$.
This can be done by following the simple below steps.

spool truncate_recyclebin.txt

alter system set recyclebin=off scope=spfile;
shutdown immediate
startup
purge recyclebin;
purge dba_recyclebin;
show recyclebin
show dba_recyclebin
select count(*) from sys.RECYCLEBIN$;
select OBJ#,OWNER#,SPACE,ORIGINAL_NAME,PURGEOBJ from sys.RECYCLEBIN$;
truncate table sys.RECYCLEBIN$;
execute dbms_stats.gather_table_stats('SYS','RECYCLEBIN$');
show recyclebin
show dba_recyclebin
select count(*) from sys.RECYCLEBIN$;
select OBJ#,OWNER#,SPACE,ORIGINAL_NAME,PURGEOBJ from sys.RECYCLEBIN$;
purge recyclebin;
purge dba_recyclebin;
show recyclebin
show dba_recyclebin
select count(*) from sys.RECYCLEBIN$;
select OBJ#,OWNER#,SPACE,ORIGINAL_NAME,PURGEOBJ from sys.RECYCLEBIN$;
alter system set recyclebin=on scope=spfile;
shutdown immediate
startup

spool off

I was then able to proceed with my database upgrade, and it's running fine in Oracle 12c as of now.

After 12c Upgrade: ORA-28040: No matching authentication protocol

2015-09-18T15:04:00.004+03:00

An application could not connect to the Oracle Database that was upgraded to 12c (12.1.0.2). Whenever it tried connecting, the database would return the error ORA-28040: No matching authentication protocol back to the application.

Bug 14575666 states:
In 12.1, the default value for the SQLNET.ALLOWED_LOGON_VERSION parameter (deprecated now) has been updated to 11. This means that database clients using pre-11g JDBC thin drivers cannot authenticate to 12.1 database servers unless the SQLNET.ALLOWED_LOGON_VERSION parameter is set to the old default of 8.

Solution:
As the SQLNET.ALLOWED_LOGON_VERSION is a deprecated parameter, although it will work but will keep flooding your ALERT LOG file, it's best to use the updated parameter(s):

Add the below line(s) in the SQLNET.ORA file on the database server:

SQLNET.ALLOWED_LOGON_VERSION_SERVER=8
SQLNET.ALLOWED_LOGON_VERSION_CLIENT=8

Hope this helps...

Oracle Linux 7.1 Installation on VMWARE for Oracle Databases

2015-09-17T22:15:00.002+03:00

Required Software:

VMWARE Workstation
Linux 7.1 Installation ISO (V74844-01.iso) from edelivery.com.oracle

Let's get started:

Step 1: Create a new Virtual Machine on VMWARE.

This is something very usual so I wont be getting into details.

Use Bridged Networking if you want to assign the VM it's own IP Address

Choose your location accordingly.

Add the ISO image of Linux Installation to the CD ROM. Then start the Virtual Machine.

Step 2: Start the Virtual Machine

Let's start with the OS Installation:

Next screen is where most of the Installation Configuration will take place. Let's take it one-by-one.

Date and Time - change the settings as per your wish/requirement

Installation Destination:
You can let the installation to Automatically partition the disk (recommended), else Configure it as per your requirement.

Software Selection:

Choose Server with GUI on the left, and the following Add-Ons on the right:

- Java Platform

- Compatibility Libraries

- Development Tools

Network and Host name:
If you miss this step, you may have a tough time to configure your VM later on. This is where the installation is a bit different from Linux 7 - for which there are many guides already available on the Internet.

Swipe the ON/OFF button to show ON.
Also, change the host name as per your preference.

This will take the IP address from the DHCP server of the router your laptop/PC is connected to. So, we will set it manually.

Click Configure, and add the IP Address, Netmask (255.255.255.0) and Gateway as per your network preferences.

KDump - can stay enabled or disabled as per your wish.

So finally the Installation Summary should look like the below. Click 'Begin Installation'.

While the OS starts installing, you can set the root password, and any User Creations if required.

After the reboot, accept the license to proceed.

Voola... Your Linux 7.1 Virtual Machine is ready for use!

Feel free to drop in any queries you may have.

RMAN BACKUP AS COPY failing with ORA-00600 [kfioTranslateIO03] [17090]

2015-09-17T00:27:00.000+03:00

Recently, I migrating a standalone database 'rnwphoto' from file system to ASM. I decided to go with the BACKUP AS COPY strategy, to save on downtime. But I was stuck for a long time trying to make the copy, but was being haunted by its failure.

Let's get to the background information.

Database Standalone Home: /u01/app/oracle/product/11.2.0/dbhome_1 --> This is the Oracle Home used by rnwphoto database.

To convert the database to ASM based RAC, we installed GI in the GRID_HOME (/u01/app/11.2.0/grid) and RAC DB Home (/u01/app/oracle/product/11.2.0/dbhome_2)

Issue:

RMAN> BACKUP AS COPY DATABASE FORMAT '+DATA_DG';

Starting backup at 09-SEP-15
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=8 device type=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: SID=69 device type=DISK
allocated channel: ORA_DISK_3
channel ORA_DISK_3: SID=132 device type=DISK
allocated channel: ORA_DISK_4
channel ORA_DISK_4: SID=72 device type=DISK
channel ORA_DISK_1: starting datafile copy
input datafile file number=00001 name=/u01/app/oracle/rnwphoto/system01.dbf
channel ORA_DISK_2: starting datafile copy
input datafile file number=00002 name=/u01/app/oracle/rnwphoto/sysaux01.dbf
channel ORA_DISK_3: starting datafile copy
input datafile file number=00005 name=/u01/app/oracle/rnwphoto/example01.dbf
channel ORA_DISK_4: starting datafile copy
input datafile file number=00003 name=/u01/app/oracle/rnwphoto/undotbs01.dbf
RMAN-03009: failure of backup command on ORA_DISK_1 channel at 09/09/2015 11:12:22
RMAN-10038: database session for channel ORA_DISK_1 terminated unexpectedly
channel ORA_DISK_1 disabled, job failed on it will be run on another channel
RMAN-03009: failure of backup command on ORA_DISK_2 channel at 09/09/2015 11:12:22
RMAN-10038: database session for channel ORA_DISK_2 terminated unexpectedly
channel ORA_DISK_2 disabled, job failed on it will be run on another channel
RMAN-03009: failure of backup command on ORA_DISK_3 channel at 09/09/2015 11:12:22
RMAN-10038: database session for channel ORA_DISK_3 terminated unexpectedly
channel ORA_DISK_3 disabled, job failed on it will be run on another channel
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03009: failure of backup command on ORA_DISK_4 channel at 09/09/2015 11:12:22
RMAN-10038: database session for channel ORA_DISK_4 terminated unexpectedly

ASM alertlog didnt show any errors or warnings.
In the database alertlog you will see that each time the RMAN command fails with --> ORA-00600 [kfioTranslateIO03] [17090]

A similar issue and it's resolution is shown in the Doc ID 1336846.1
As per this document, this issue occurs due to multiple reasons and one of them is a permission issue...

1. Check the group owner required on 'oracle' binary file to access ASM successfully:

[grid@node1-riz lib]$ cat $ORACLE_HOME/lib/config.c | grep ASM
#define SS_ASM_GRP "asmadmin" >>>>
char *ss_dba_grp[] = {SS_DBA_GRP, SS_OPER_GRP, SS_ASM_GRP};

2. Let's check the group owner of all ORACLE_HOME/bin/oracle binary:

Grid Home:
[grid@node1-riz ~]$ ls -latr $GRID_HOME/bin/oracle
-rwsr-s--x 1 grid oinstall 169802036 Sep 3 12:22 /u01/app/11.2.0/grid/bin/oracle

RAC Database Home:
[oracle@node1-riz ~]$ ls -lart /u01/app/oracle/product/11.2.0/dbhome_2/bin/oracle -rwsr-s--x 1 oracle asmadmin 192296439 Sep 3 13:30 /u01/app/oracle/product/11.2.0/dbhome_2/bin/oracle

rnwphoto Standalone Database Home:
[oracle@node1-riz ~]$ ls -lart $ORACLE_HOME/bin/oracle
-rwsr-s--x 1 oracle oinstall 192296337 Sep 2 19:41 /u01/app/oracle/product/11.2.0/dbhome_1/bin/oracle
>>>>> ***************************************************** <<<<<

Do you see it? If not, read ahead...

Point #1 states that 'asmadmin' should be the group owner of the $ORACLE_HOME/bin/oracle

Let's ignore the Grid Home findings - as that's not a part of the problem. It's just for your information.

RAC Database Home works fine with the ASM -- as the group owner of the 'oracle' binary is 'asmadmin'

Standalone Database Home was created much before there was a Grid Home, so it still has 'oinstall' as the group owner of the 'oracle' binary file.

Solution:
In the Standalone Oracle Home, change the ownership of the 'oracle' binary from oracle:oinstall to oracle:asmadmin --- and WOOOLA !!!

WARNING: You need a downtime to make this change. If you change the ownership without shutting the database(s), then expect all the databases running on that ORACLE_HOME to CRASH!!!

My Photography Slideshow

2015-09-16T12:47:00.001+03:00

Many of you may or may not know, that I am not only passionate about Oracle and new technologies, but also passionate about Photography.

I am working on creating a small slideshow to showcase some of my special work - just to keep you even more relaxed.

If you wish to check it out, you can visit my:

Facebook Page: https://www.facebook.com/rizwanwangdephotography
500px: https://500px.com/rizwanwangde

I'm back...

2015-09-16T12:38:00.002+03:00

Hi Folks,

I am back ! This blog has been dormant for way too long now. There are so many things I could have shared in the past year but lost on all those opportunities. I promise to make it up, and post as regularly as possible.

Other than the daily stuff, my short term major goals are as below:

1. EM12cR3 Setup on HP-UX

2. DataGuard 12c Setup - ASM <--> Filesystem

Regards,

Rizwan Wangde

Oracle 10g Undo Auto Retention Bug causing continuous increase in UNDO tablespace utilization

2014-05-06T17:03:00.002+03:00

Something interesting happened the other day on one of our Production Databases. The UNDO tablespace just kept filling up, without showing any downward trend. As this was a mission critical database, core-banking, we couldn't take the risk.

I check the usage of UNDO and for any other abnormal sessions or jobs that might be out of place. Nothing!!! But the space usage just kept increasing. I realized this had to be some sort of a bug. Well, after so much experience on the Oracle Databases, a DBA must have the intuition that "this must be a bug!". It was weird because the Undo Segments just wouldn't Expire... All were in the Active state.

I checked the Metalink for some time, and found a document which was very closely related to my situation.

Bug 5387030 Automatic tuning of undo_retention causes unusual extra space allocation

Problem Description:

When undo tablespace is using NON-AUTOEXTEND datafiles,V$UNDOSTAT.TUNED_UNDORETENTION may be calculated too high preventing undo block from being expired and reused. In extreme cases the undo tablespace could be filled to capacity by these unexpired blocks.

To find out if you are hitting the same, check the output of the below statement:

select end_time, MAXQUERYLEN, TUNED_UNDORETENTION

from v$undostat where

trunc(end_time)>trunc(sysdate-1) order by 1,2,3;

This will show a comparison between the maxquerylen and the tuned_undoretention. Ideally, the maximum amount of time set by the automatic Undo Retention should be either the maxquerylen or the value of undo_retention parameter. But in this case, the values under TUNED_UNDORETENTION column may turn out to be much higher than the both the maxquerylen or the value of undo_retention parameter

Solution:

Set the below hidden parameter:

alter system set "_smu_debug_mode" = 33554432 scope=both;

You can see that all the UNDO segments that were ACTIVE and well passed the UNDO_RETENTION value have finally expired, and the space released from the UNDO Tablespace.

Oracle Database

2014-05-06T16:21:00.003+03:00

ORA-01017: invalid username/password when connecting from a 9i ODBC driver to an 11g Database

2012-09-05T10:27:00.000+03:00

Hi folks,

HP Openview, used for monitoring and alerting, is in it's migration phase. The database now runs on Oracle 11g. However, the Reporting Server that connects to the database underwent no such migration or upgrade. This Reporting Server is still using the Oracle 9.1 ODBC drivers.

While trying to connect to the new 11g database, the team encountered the following error:

Unable to connect
SQLState=28000
[Oracle][ODBC][Ora]ORA-01017: invalid username/password; logon denied

Interestingly, if I tried to connect using SQLPLUS, I could establish a connection without any problems.

C:\Documents and Settings\xxxxxx>sqlplus

SQL*Plus: Release 9.2.0.1.0 - Production on Wed Sep 5 10:13:57 2012

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

Enter user-name: opc_report@xxxxxxxxxxxxx
Enter password:

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> select * from v$version;

BANNER
-------------------------------------------------------------------------------

Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
PL/SQL Release 11.2.0.3.0 - Production
CORE 11.2.0.3.0 Production
TNS for HPUX: Version 11.2.0.3.0 - Production
NLSRTL Version 11.2.0.3.0 - Production

SQL>

Cause and Solution:

This was because of the usage of CASE SENSITIVE passwords in Oracle 11g. This was resolved by turning off the case sensitive feature in the database:

$ sqlplus

SQL*Plus: Release 11.2.0.3.0 Production on Wed Sep 5 10:05:06 2012

Copyright (c) 1982, 2011, Oracle. All rights reserved.

Enter user-name: / as sysdba

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> alter system set SEC_CASE_SENSITIVE_LOGON = FALSE
2 scope=both;

System altered.

And, woolaaa:

Windows 8 and the Blank Laptop LCD Screen issue

2012-08-08T13:06:00.000+03:00

I recently installed the leaked Windows 8 RTM, to try it out before I purchase (and I will most definitely buy it as it cost only 15$ for the upgrade).
I have worked on the Windows 8 for 2 days now, and I think "THIS IS IT!". Windows 8 is one of the most amazing and an innovative platform of OS' that Microsoft has ever released. It's blazing FAST, beautiful and easy to use. I know that the first time users will find it a little difficult to get used to the new style of Navigation, but 2 days in and you'll love everything about Windows.

Blank LCD/Screen issue on loading Windows 8:
Many of you might be facing this issue - after Windows 8 completes loading, you will see a blank screen instead of the Login Screen. When I connect the laptop to an external LCD monitor, it works fine. But the Laptop's LCD just wont!

I tried a lot of tricks to fix it, but this is what clicked at the end:

1. Put your laptop into Sleep Mode by pressing the Power Button once or you can just close the lid of your laptop.

2. Wake your Laptop from the Sleep Mode by pressing the Power Button again or opening the LCD lid.

Woola... I can see my screen now!

I had to do this 2-3 times, and after that I have rebooted, or hard reset my laptop many times without facing the Blank LCD issue.

Let me know if you faced this issue and if the above trick helped you.

After applying Oracle PSU of JAN 2012 (10.2.0.5.6), "Cursor: pin S" waits and 100% CPU are BACK !!!

2012-02-22T10:12:00.000+03:00

In my earlier post, dated 27-Sept-2011, I shared how we were able to resolve the issue of Cursor Pin S Waits and 100 % CPU.

1. Apply patch 6904068
2. Set _first_spare_parameter = 1 (keep increasing this value slowly if the problem still exists, do not set it very high as this can worsen things)

We generally apply the Oracle Quarterly PSU Patches within a month of their release. Patch 13343471 (PSU 10.2.0.5.6 for January 2012) was applied, which conflicted with the earlier applied patch to fix the High CPU usage when there are "cursor: pin S" waits, and rolled it back.

The following day, we saw the bug hit again, on two of our databases. We immediately got in touch with Oracle Support, and found out that there's another patch -- Bug 13619396: MERGE REQUEST ON TOP OF 10.2.0.5.6 PSU FOR BUGS 6904068 7045446 8684595.

Beware, the _first_spare_parameter that we had set when we initially set is not applicable after applying the updated Bug Fix. Instead, you have to set _third_spare_parameter.

Hope this helps, as there is no mention of this on the internet as yet.

Cursor S Pin Waits & High CPU utilization (Oracle 10g)

2011-09-27T11:11:00.000+03:00

We are running a Mission Critical 24x7 (10.2.0.5) on HP-UX platform a stand alone HP Itanium server. One morning when everything else was running smoothly, we were alerted of connection requests timing out on this database.
We were already facing a lot of Network/Firewall issues, so we thought it might be the network. As a formality, I tried to login into the server with the intention to check the incoming sessions traffic in the listener logs. But to my surprise, my connection request was taking toooo long by the server and then timed out. I got in touch with the System Admin and he was able to get a session into the server. We saw that the whole server was in a hanged or in an extremely slow state!
On checking for the top resources, we found that the Oracle processes were utilizing 100% CPU, hence causing the hang. I was then lucky to have a session of OEM opened on that database, and saw the top wait event was "Cursor S Pin" related waits. I later checked that there were more than 700 sessions waiting for mutex to be released. As it became almost impossible to execute any more commands, it was decided to bounce the server to get the services back online.
Till then we were not sure whether the problem was in the hardware, the OS or the database. The only clue was:
1. 100% CPU utilization in the server
2. An AWR report, which just "luckily" completed before the Server Reboot !

I checked the metalink, and found the exact same symptoms for a bug: Bug 6904068 - High CPU usage when there are "cursor: pin S" waits [ID 6904068.8] Applied the patch at midnight, and haven't had the issue since then.

Basically what is happening is that a session tries to get the mutex (kind of a latch) in S mode, but is unable to, and immediately yields so another session can come in and request it, and this can start causing excessive CPU usage and cause extreme performance degradation or hangs. This is a very common bug when the "cursor: pin S" wait is seen.

Applying this patch allows you to set _first_spare_parameter to wait for a fixed time instead of yielding when trying to obtain the mutex in S mode and there is no X holder.

Oracle 11g: OER 7451 in Load Indicator

2011-09-08T23:01:00.001+03:00

I installed Oracle 11g R1 on my laptop running Windows 7 Enterprise Edition (x64 bit).
As I was working on it, i felt like there was something wrong - it was too slow.
I shutdown the database, and shot the startup command, but got stuck on "Mounted".
Although I was able to get it started a minute later by stopping and starting the Services (services.msc).
I checked the alert log for any errors and found the following was filling up my alert log:

Thu Sep 08 21:00:41 2011
Errors in file e:\oracle11g\app\diag\rdbms\rizwan\rizwan\trace\rizwan_j008_8896.trc:
ORA-12012: error on auto execute of job 11689
ORA-01403: no data found
OER 7451 in Load Indicator : Error Code = OSD-04500: illegal option specified
O/S-Error: (OS 1) Incorrect function. !
Thu Sep 08 21:00:47 2011
OER 7451 in Load Indicator : Error Code = OSD-04500: illegal option specified
O/S-Error: (OS 1) Incorrect function. !
Thu Sep 08 21:01:07 2011
OER 7451 in Load Indicator : Error Code = OSD-04500: illegal option specified
O/S-Error: (OS 1) Incorrect function. !
Thu Sep 08 21:01:09 2011
ALTER TABLESPACE example READ WRITE
Completed: ALTER TABLESPACE example READ WRITE
OER 7451 in Load Indicator : Error Code = OSD-04500: illegal option specified
O/S-Error: (OS 1) Incorrect function. !
Thu Sep 08 21:01:17 2011
OER 7451 in Load Indicator : Error Code = OSD-04500: illegal option specified
O/S-Error: (OS 1) Incorrect function. !
OER 7451 in Load Indicator : Error Code = OSD-04500: illegal option specified
O/S-Error: (OS 1) Incorrect function. !

After checking a document on MOS, I found out what the mistake was. I was running a 32-bit Oracle Software on my 64-bit OS.

I fixed it by uninstalling the 32-bit software and installing the 64-bit software.

"ORA-01652: unable to extend temp segment by 128 in tablespace TEMP" encountered while creating new table using Create Table As Select (CTAS)

2011-09-06T13:32:00.002+03:00

Recently, in a SQA database I noticed something... CTAS was using TEMPORARY Tablespace!

In SQA, we have 4 copies of the same database, we call them 'Regions' - namely I,P,Q,R. Each region is used for a different testing stage. In region R, the SQA was testing on a Housekeeping Solution on a large table (85 GB) to purge 6 months data. Something went wrong, and they requested us to get a copy from another Region (Q) where this release was not tested.

It was decided to use the option of CTAS, rather than export/import due to storage space constraints. This was to be done using DB Link to the Q Region's Database. When the command was executed, we ran into
ORA-01652: unable to extend temp segment by 128 in tablespace TEMP.

12:25:35 SQL> create table GECT
12:25:35 2 TABLESPACE TS_GECT
12:25:35 3 PCTUSED 0
12:25:35 4 PCTFREE 10
12:25:35 5 INITRANS 10
12:25:35 6 MAXTRANS 255
12:25:35 7 STORAGE (
12:25:35 8 INITIAL 100M
12:25:35 9 NEXT 100M
12:25:35 10 MINEXTENTS 1
12:25:35 11 MAXEXTENTS UNLIMITED
12:25:35 12 PCTINCREASE 0
12:25:35 13 BUFFER_POOL DEFAULT
12:25:35 14 )
12:25:35 15 NOLOGGING
12:25:35 16 PARALLEL
12:25:35 17 AS
12:25:35 18 (SELECT * FROM FNSONLP.GECT@GECT_LINK);
create table GECT
*
ERROR at line 1:
ORA-12801: error signaled in parallel query server
ORA-01652: unable to extend temp segment by 128 in tablespace TEMP

It didn't make any sense! CTAS should not use TEMPORARY Tablespace unless some sort was to be performed.

I did a little digging on Blogs and MOS, but the best I could find was:

DURING CTAS (CREATE TABLE AS SELECT) ORA-1652 REPORTED ON THE DATA TABLESPACE [ID 577643.1]

But the document or blogs only speaks about ORA-1652 on the "data tablespace", not the "temporary tablespace."

Solution:

I removed the clause "PARALLEL" from my CTAS statement. As soon as I did that, the target tablespace started filling up, and my CTAS was successful.

How to stop access using "/ as sysdba"

2011-09-03T12:07:00.000+03:00

A friend of mine wanted to secure his client's database access by restricting the DBAs' connecting to the database as 'SYS' user by using "/ as sysdba".

In the file sqlnet.ora, located in $ORACLE_HOME/network/admin folder, add the following line:

SQLNET.AUTHENTICATION_SERVICES=(NONE)

Try logging in again by using "/ as sysdba":

No doubt this will prevent the access of "/ as sysdba" when connected as the 'oracle' user (oracle owner account) but the DBA can easily comment the configuration parameter in SQLNET.ORA - if he has the required permisssion on the file.
To avoid this, change the ownership of the sqlnet.ora file to 'root' or any other functional OS user, and provide a read permission to dba/oinstall group.

chown root:oinstall sqlnet.ora
chmod 640 sqlnet.ora

Solving ORA-08102: index key not found, obj# 7941, file 11, block 398468 (2)

2011-08-22T12:37:00.000+03:00

This error was noticed in a Development database when the Developers tried to delete a row from a table called LIMITS:
SQL> DELETE FROM LIMITS WHERE ACCT_ID = 1496605012;
ORA-08102: index key not found, obj# 7941, file 11, block 398468 (2)

At the first glance, it was clear that this could be due to Index Corruption. It basically means that the stored key in the index are different from the value in the table.

Solution: Drop the index and re-create it.
Find out what Index is facing this Issue:
SQL> select owner,OBJECT_NAME,OBJECT_TYPE,OBJECT_ID from dba_objects where OBJECT_ID = 7941;
OWNER
-------------
OBJECT_NAME
-------------------
OBJECT_TYPE OBJECT_ID
------------------- ----------
CCRPROD
IND12LIMITS
INDEX 7941

Generate the Index Creation DDL Statement:
SQL> set pages 999;
SQL> set long 90000;
SQL> select dbms_metadata.get_ddl('INDEX','IND12LIMITS','CCRPROD') from dual;
DBMS_METADATA.GET_DDL('INDEX','IND12LIMITS','CCRPROD')
--------------------------------------------------------------------------
CREATE INDEX "CCRPROD"."IND12LIMITS" ON "CCRPROD"."LIMITS" ("PARTY_ID")
PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
STORAGE(INITIAL 1048576 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "CCR_INDEX_TS"

Drop the index:
SQL> DROP INDEX CCRPROD.IND12LIMITS;
Create the Index using the DDL statement generated.

Note: Many blogs would tell you to rebuild the index. But how can rebuilding the index fix this issue? Rebuilding indexes generally reads itself to generate the new rebuilt index. To fix this issue, the index needs to read the values from the table directly which can be done only when creating a new index.