Showing posts with label 11g upgrade. Show all posts
Showing posts with label 11g upgrade. Show all posts

7.03.2011

RMAN Snapshot Controlfile for RAC database in 11gR2

Just thought of quickly updating the change made in RMAN Snapshot Controlfile behavior for RAC database with Oracle 11gR2 and later. Well, if you are wondering why post 11gR2 upgrade the RMAN Snapshot Controlfile backup for your RAC database is failing with 'ORA-00245: control file backup operation failed', here is the reason plus  a workaround to tackle the issue.


According to MOS [ID 1263621.1], it is an expected behavior with 11gR2 or later in contrast to earlier Oracle versions.  Since all instances of the RAC database shares the same snapshot controlfile, you must ensure that the SCF for a RAC database is configured on a shared device plus all instances of the RAC database must have access to the file. 


The error in the context is likely to appear if all instances of  RAC database can't access the snapshot controlfile. Therefore, the following RMAN configuration across all instances (on each node) should avoid the situation:


Syntax:


RMAN>  CONFIGURE SNAPSHOT CONTROLFILE NAME TO ;




References:
RMAN Snapshot Controlfile Must Reside on Shared Device for RAC database in 11g [ID 1263621.1]


Happy reading,


Jaffar


6.28.2011

ASM instance in INTERMEDIATE state + CHECK TIMED OUT state_details

Yet another clash with ASM 11gR2 BUG during a new database creation under a 6 node development RAC environment. Got a requirement to create a new database, thereby, created a new ASM diskgroup manually and began creating the database with DBCA tool. However,  ran over the following error when the newly created ASM diskgroup was selected to host the data files of the database:


After getting the error, I thought I would better ensure once again that the diskgroup is properly mounted on the local ASM instance, despite the fact that the diskgroup in very well mount state, the issue persists. I therefore check over the resource (asm and dg) status in the cluster issuing the 'crsctl stat res -t' command. The output displays that the diskgroup in the question has OFFLINE STATE and scrolling further down it was a bit strange and scary moment. The ora.asm resource was in INTERMEDIATE state with CHECK TIMED OUT state_details.

My curiosity let me do some research over the MOS and quickly come across the following document:

oraagent.bin Exits After Check Timed Out [ID 1323679.1]

The issue was nearly identical to what has been explained in the document and the behavior is due to bug 11807012 that apparently fixed in 11.2.0.3, 12.1 and above. However, the document also did mention that the Interim patch 11807012 exists for certain platform/version and advised to engage Oracle Support to request if it does not exist for your platform/version. Unfortunately, we couldn't find the interim patch for our OS (HPUX). We then opened a SR and the engineer indeed confirmed it is due to the bug in the context. As a workaround, recommend the following action plan followed ASM instance restart:

crsctl modify resource "ora.asm" -attr "CHECK_TIMEOUT=132"

The above change was applied and bounced the ASM instance to reflect the change.(We seek the downtime to deploy the change, since it was a dev. environment, we easily get it). The ora.asm resource then cam back to normal condition and the database was successfully created subsequently..

Out of six ASM instances, we found three of them were in the same state. However, on a positive side, it will not cause any troubles with the ongoing operations unless you wanna do something what we needed. The following image outlines the status of the ASM resources:


Happy reading,

Jaffar


6.25.2011

11gR2 upgrade aftermath - impdp failed with ORA-29913+ ORA-31011+ ORA-19202 + LPX-00217

It has been almost close to 3 months since we had a successful Oracle 10gR2 (10.2.0.4) clusterware upgrade with nearly 100 database to Oracle 11gR2 (11.2.0.2) at our site. However, our association with an unforeseen bugs and adjusting our application code (a very minimal) to confront the significant changes in the behavior with11gR2 functionality is yet to come to a happy ending .  I wanna share yet another 11gR2 upgrade aftermath fairy tale (BUG) that we have come across about a few days ago. Before I start of summarizing what had happen, I must confess it indeed put me in a catch-22 situation and luckily it was with the non-production RAC database, I managed to overcome from the pain. To cut the long story short, here is what happened...

In order to fix some of the application bugs that encountered with the close of business(COB) process, the development team wanted to simulate the test, thereby, requested me to take a logical backup (export) of one of the schema in a RAC database that was recently upgraded to 11gR2. The actual plan was like, performing a logical backup (export) for the schema, simulating the COB test (application) failure, dropping the schema followed by the schema restore (import). The schema contains over 35 thousand objects where every table has only two columns and one of the columns defined XMLTYPE data type. I knew it is bit unusual.

Once the test was over, I dropped the schema and initiated the import just before I left home for the day. The very next morning I promptly jumped to the bottom of the import logfile to ensure everything went smoothly. By bad, what I had seen was a bit disappointing because the import completed with around 15 errors. A quick scan through the logfile from top to down shows that data import has been failed in 15 tables with the following errors:

ORA-31693: Table data object "SCHEMA"."TABLE_NAME" failed to load/unload and is being skipped due to error:
ORA-29913: error in executing ODCIEXTTABLEFETCH callout
ORA-31011: XML parsing failed
ORA-19202: Error occurred in XML processing
LPX-00217: invalid character 27 (U+001B)
Error at line 1



Subsequent search over the internet and in the MOS for the above listed errors really doesn't help me resolving the issue. As per some initial suggestion and workaround, I did run the utlrp.sql script (to recompile a few invalid objects in the SYS schema) and ensure there is no database option left INVALID post database upgrade. After those couple of checks, I gave another try to upload the data into those tables, but, failed with the same errors.

I then opened a Service Request (SR) with Oracle support and seek their assistance to get me out the situation.  Right after analyzing the import logfile and other details, the engineer was quick to mention the following lines:

This errors is due to a problem in the 11.2 XML parser and this is still under investigation with the Development team under bug 11877267, So please try as a workaround to import the same dump file on a 10.2 database with the parameter version=10.2 and use a DBlink to copy those tables from the 10g database to the 11g.


I was really caught in a catch-22 situation as I had no Oracle 10g database to perform the workaround mentioned by the support engineer. Moreover, the engineer himself wasn't sure about the workaround could work out or not. When I informed the application about the situation, they initially told me they can go ahead without the data into those tables. After a set of meetings with the application team and discussing the ways to recover the database, they agreed to copy the data from another environment (in fact another schema of the same database). This decision came like a life savior for me.

Though we have done 3 RAC environments cluster+database upgrade to 11gR2, we are yet to upgrade the major production 8 node RAC environment. I really hope by the time we perform the critical production upgrade, we should be able to address most of the issues we come across post 11gR2 upgrade.

Since no solution available yet for the bug in the context, I strongly recommend you to be careful when you have a similar requirements of mine.


Reference
Bug 11877267: EXPDP/IMPDP ARE NOT ABLE TO HANDLE HEXADECIMAL CHARACTER REFERENCES IN XMLTYPE

5.30.2011

Yet another Oracle 11g upgrade aftermath - ORA-07445: exception encountered: core dump [evaopn3()]

Apparently, the Oracle 11gR2 aftermath stories is like never ending story from my part. Yet another aftermath fairy tale for you.

It was brought to our notice a couple of days ago that one of the application query failed to execute of late (perhaps after 11gR2 upgrade) and the following error has been logged in the application log file:

Dynamic Query PreparedStatement SQLException: java.sql.SQLException: No more data to read from socket SQLState: null ErrorCode: 17410

During my initial investigation the following errors have been found in the database alert.log:

Exception [type: SIGSEGV, Address not mapped to object] [ADDR:0x1200000000] [PC:0x400000000631B880, evaopn3()+384] [flags: 0x0, coun
t: 1]
Errors in file /u00/app/oracle/diag/rdbms/pmsit/XXXXX/trace/XXXXX1_ora_23652.trc  (incident=9016):
ORA-07445: exception encountered: core dump [evaopn3()+384] [SIGSEGV] [ADDR:0x1200000000] [PC:0x400000000631B880] [Address not mappe
d to object] []
Incident details in: /u00/app/oracle/diag/rdbms/pmsit/XXXXX1/incident/incdir_9016/PMSIT1_ora_23652_i9016.trc
Use ADRCI or Support Workbench to package the incident.
See Note 411.1 at My Oracle Support for error and packaging details.
Mon May 30 11:13:54 2011
Dumping diagnostic data in directory=[cdmp_20110530111354], requested by (instance=1, osid=23652), summary=[incident=9016].
Mon May 30 11:14:10 2011
Sweep [inc][9016]: completed
Sweep [inc][8952]: completed
Sweep [inc][8680]: completed
Sweep [inc2][9016]: completed
Sweep [inc2][8952]: completed
Sweep [inc2][8680]: completed


Additionally, the following has been found in the trace file in the context:

DDE:Problem Key 'ORA 7445[evaopn3()+384]'was flood controlled(0x2)(incident: 8657)
ORA-07445: exception encountered: core dump [evaopn3()+384] [SIGSEGV] 

ORA-07445 error caught my attention and as a immediate fix or thumb of rule, perhaps, I have flushed the SHARED POOL and asked the person to execute the query again. Unfortunately, the issue persists. Since this is a development database, I took the permission from the concerning application team to re-start the database which also never worked in our favor.

After a little bit of research over the MOS (metalink), I found  a potential workaround  to set the initialization parameter _simple_view_merging value to false to get rid of the issue. This indeed did the trick and the statement (query) was able to run without any issues this time. Additionally, found a recommendation to turn of _complex_view_merging initialization parameter too.

Moving on further with my investigation, I used the ORA-07445 look up tool by providing the first argument of the error '[evaopn3()+384]'  that suggests me to have a look at MOS ORA-7445 (evaopn3) [ID 860969.1] and the following is the excerpt from the document:

ERROR:
  ORA-7445 failing function "evaopn3"

VERSIONS:
  versions 10.1 to 11.2

DESCRIPTION:

  ORA-7445 [evaopn3] generally occurs during the execution of a query.

  The error typically only occurs with a specific stream of data being
  fetched from the objects in the execution plan.

  The problem is caused often by an optimization but it is not until a
  row is fetched and evaluated that the error happens.
 


The error is largely because of  a few Oracle bugs (depends on the version of database). Nevertheless, the document indeed suggests a few strategies to cope up the error. In my case, the above solution worked perfectly, though not really aware of the consequence of disabling the feature. I need to monitor the database behavior for sometime to make the change permanent.

References:
http://www.freelists.org/post/oracle-l/ora3113-related-to-histograms-11gr2,1
MOS ORA-7445 (evaopn3) [ID 860969.1]
MOS Customer Introduction to ORA-7445 Errors [ID 211909.1]
 
Happy reading,

Jaffar

6.05.2010

Oracle 11g OCP upgrade

Friends,

A short update about my Oracle certification. On 2nd June 2010, I have successfully completed my Oracle 11g OCP upgrade exam.



Jaffar

3.06.2008

Complete Checklist for Manual Upgrades to 11gR1 - ML Note:429825.1

I am really not sure how many of you aware of the newly published Metalink Note about step-by-step manual upgrade to 11gR1.

Metalink Note : Note:429825.1. (latest revision on 05-MAR-2008)

Jaffar