Pages

Showing posts with label Oracle Database. Show all posts
Showing posts with label Oracle Database. Show all posts

Monday, January 23, 2017

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

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.

Monday, November 21, 2016

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

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:
  1. GRID_HOME environment variables set
  2. RDBMS_HOME environment variables set
  3. Monitoring the activity in the alert.log of the crs
  4. 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!

Tuesday, May 6, 2014

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


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.


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

Wednesday, September 5, 2012

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

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:






Wednesday, February 22, 2012

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

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.

Tuesday, September 27, 2011

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

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.