ORA-600

11gR2:Oracle Online Patching

Online patching is new feature introduced in 11.1.0.6 and oracle started delivering patches with 11.2.0.2.

This patch allows you to apply patch to live database i.e we do not need to shutdown database. This feature is only available for RDBMS home and can be installed/enabled using opatch.

RDBMS Online Patching Aka Hot Patching [ID 761111.1] explains internals of how this patching works.

I tested it while working on Database corruption issue caused by Bug 10205230 ORA-600 / corruption possible during shutdown in RAC. This bug is present in 11.2.0.2 database which causes corruption when you try to use shutdown immediate/normal/transactional option to shutdown RAC database. As per MOS note 1318986.1, Data corruption occurs around shutdown one or more of the RAC instances

* One of the following ORA-600 asserts:
– ORA-600 [kclchkblk_3]
– ORA-600 [kclwcrs_6]
– ORA-600 [ktubko_1]
– ORA-600 [kcratr_scan_lostwrt]
– ORA-600[3020] on the standby database

Workaround is to perform local check point and do shutdown abort. Patch 10205230 was available for our platform and I downloaded using below command directly to server

read mos_user_id
read -s h_passwd
wget --http-user="$mos_user_id" --http-password="$h_passwd" "https://updates.oracle.com/Orion/Services/download/p10205230_112020_Linux-x86-64.zip?aru=13671866&patch_file=p10205230_112020_Linux-x86-64.zip" -O p10205230_112020_Linux-x86-64.zip

mos_user_id – Metalink aka Mos user id (email id)
h_passwd – Password

To query if patch is online, go to patch directory and run following

 

$opatch query -all online
Invoking OPatch 11.2.0.1.6
Oracle Interim Patch Installer version 11.2.0.1.6
Copyright (c) 2011, Oracle Corporation. All rights reserved.

Oracle Home : /home/oracle/product/11.2
Central Inventory : /home/oracle/oraInventory
 from : /etc/oraInst.loc
OPatch version : 11.2.0.1.6
OUI version : 11.2.0.2.0
Log file location : /home/oracle/product/11.2/cfgtoollogs/opatch/opatch2012-06-20_16-43-21PM.log
--------------------------------------------------------------------------------
 Patch created on 2 Apr 2011, 12:57:14 hrs PST8PDT
 Need to shutdown Oracle instances: false
 Patch is roll-backable: true
 Patch is a "Patchset Update": false
 Patch is a rolling patch: true
 Patch has sql related actions: false
 Patch is an online patch: true
 Patch is a portal patch: false
 Patch is an "auto-enabled" patch: false
List of platforms supported:
 226: Linux x86-64
List of bugs to be fixed:
 10205230: TB_X64: HIT ORA-00600: [KCLWCRS_6]
This patch is a "singleton" patch.
This patch belongs to the "db" product family
List of executables affected:
 ORACLE_HOME/bin/oracle
List of optional components:
 oracle.rdbms: 11.2.0.2.0
List of optional actions:
 Patch the Database instances with Online Patch hpatch/bug10205230.pch
Possible XML representation of the patch:
<ONEOFF REF_ID="10205230" ROLLBACK="T" XML_INV_LOC="oneoffs/10205230/" ACT_INST_VER="11.2.0.2.0" INSTALL_TIME="2012.Jun.20 16:43:21 PDT">
 <DESC></DESC>
 <REF_LIST>
 <REF NAME="oracle.rdbms" VER="11.2.0.2.0" HOME_IDX="0"/>
 </REF_LIST>
 <BUG_LIST>
 <BUG>10205230</BUG>
 </BUG_LIST>
 <FILE_LIST/>
</ONEOFF>
--------------------------------------------------------------------------------
OPatch succeeded.

“Patch is an online patch: true” indicates patch can be applied to live db.

To apply the patch, we can use following syntax

Non – RAC => opatch apply online -connectString <SID>:<USERNAME>:<PASSWORD>:<NODE>
RAC => opatch apply online -connectString <SID>:<USERNAME>:<PASSWORD>:<NODE1>,<SID2>:<USERNAME>:<PASSWORD>:<NODE2>,…

Since we are patching RAC database, we will use second syntax

$ opatch apply online -connectString orcl01d1:sys:oracle123:prod03,orcl01d2:sys:oracle123:prod04
Invoking OPatch 11.2.0.1.6
Oracle Interim Patch Installer version 11.2.0.1.6
Copyright (c) 2011, Oracle Corporation. All rights reserved.

Oracle Home : /home/oracle/product/11.2
Central Inventory : /home/oracle/oraInventory
 from : /etc/oraInst.loc
OPatch version : 11.2.0.1.6
OUI version : 11.2.0.2.0
Log file location : /home/oracle/product/11.2/cfgtoollogs/opatch/opatch2012-06-20_16-53-36PM.log

The patch should be applied/rolled back in '-all_nodes' mode only.
Converting the RAC mode to '-all_nodes' mode.
Applying interim patch '10205230' to OH '/home/oracle/product/11.2'
Verifying environment and performing prerequisite checks...
Do you want to proceed? [y|n]
y
User Responded with: Y
All checks passed.
Backing up files...
Patching component oracle.rdbms, 11.2.0.2.0...
The patch will be installed on active database instances.
Installing and enabling the online patch 'bug10205230.pch', on database 'orcl01d1'.

Patching in all-node mode.
Updating nodes 'prod04' 
 Apply-related files are:
 FP = "/home/oracle/product/11.2/.patch_storage/10205230_Apr_2_2011_12_57_14/rac/copy_files.txt"
 DP = "/home/oracle/product/11.2/.patch_storage/10205230_Apr_2_2011_12_57_14/rac/copy_dirs.txt"
 MP = "/home/oracle/product/11.2/.patch_storage/10205230_Apr_2_2011_12_57_14/rac/make_cmds.txt"
 RC = "/home/oracle/product/11.2/.patch_storage/10205230_Apr_2_2011_12_57_14/rac/remote_cmds.txt"
Instantiating the file "/home/oracle/product/11.2/.patch_storage/10205230_Apr_2_2011_12_57_14/rac/copy_files.txt.instantiated" by replacing $ORACLE_HOME in "/home/oracle/product/11.2/.patch_storage/10205230_Apr_2_2011_12_57_14/rac/copy_files.txt" with actual path.
Propagating files to remote nodes...
Instantiating the file "/home/oracle/product/11.2/.patch_storage/10205230_Apr_2_2011_12_57_14/rac/copy_dirs.txt.instantiated" by replacing $ORACLE_HOME in "/home/oracle/product/11.2/.patch_storage/10205230_Apr_2_2011_12_57_14/rac/copy_dirs.txt" with actual path.
Propagating directories to remote nodes...
Installing and enabling the online patch 'bug10205230.pch', on database 'orcl01d2' on node 'prod04'.
Patch 10205230 successfully applied
Log file location: /home/oracle/product/11.2/cfgtoollogs/opatch/opatch2012-06-20_16-53-36PM.log
OPatch succeeded.

You can verify that patch is applied and enabled

SQL> oradebug patch list
Patch File Name State
================ =========
bug10205230.pch ENABLED

If you have multiple ORACLE databases running out of same RDBMS Home, you can enable patch for other database using enableonlinepatch option

$ opatch util enableonlinepatch -connectString orcl02d1:sys:oracle123:prod03,orcl02d2:sys:oracle123:prod04 -id 10205230

In case of single instance home, below syntax is used

$ opatch util enableonlinepatch -connectString orcl:sys:oracle123 -id 10205230

Alert log is updated with following entries during patch application

Patch bug10205230.pch Installed - Update #1
Patch bug10205230.pch Enabled - Update #2
Thu Jun 21 02:13:17 2012
Online patch bug10205230.pch has been installed
Online patch bug10205230.pch has been enabled

Online patching is very important feature introduced in 11.2.0.2 as this allows you to apply patches without any downtime. Note that if you apply patch using normal opatch apply, it will not be enabled. You can confirm this by oradebug patch list command.

22 June, 2012 by 11g 1

Recreating Database Link

I was trying to recreate database links as part of database migration activity. This involved mixture of 8i,9i and 10g database.

In case of 8i/9i it is very easy to find the old password which was used to create database link as passwords for database links are stored in Clear Text (decrypted format) in SYS.LINK$ .As a result we can easily re-create database links. Find below steps to perform the same

SQL> set pages 1000 lines 80
SQL> col db_link for a20
SQL> col host for a20
SQL> col user_id for a20
SQL> SELECT NAME,HOST,USERID,PASSWORD FROM SYS.LINK$;

NAME            HOST                 USERID                 PASSWORD
-------------- -------------------- ----------------- -----------------
TO_AMIT         AMITDB             	 AMIT                 AMIT123

create database link TO_AMIT connect to AMIT identified by AMIT123 using 'AMITDB';
Database link created.

In case of 10g passwords are stored in encrypted format in SYS.LINK$. I tried to workaround this by recreating using identified by values.

SQL> create database link TO_AMIT connect to AMIT identified by values '14573425Z6D15503' using 'AMITDB';

Database link created.

SQL> select sysdate from dual@TO_AMIT;
select sysdate from dual@TO_APREAD
                         *
ERROR at line 1:
ORA-00600: internal error code, arguments: [kzdlk_zt2 err], [18446744073709551603], [], [], [], [], [], []

I researched metalink and found Note 456320.1 according to which this is expected behavior and ‘identified by values’ can be used only by Oracle Internal utilities. Therefore only way to re-create database link is to use exp/imp. As this is 10g, EXPDP and IMPDP is better suited as it will help in  exporting/importing database links only.


SQL> select * from DATABASE_EXPORT_OBJECTS where object_path like '%LINK%';

OBJECT_PATH                              COMMENTS                                           N
---------------------------------------- -------------------------------------------------- -
DATABASE_EXPORT/SCHEMA/DB_LINK           Private and public database links
DB_LINK                                  Private and public database links                  Y
SCHEMA/DB_LINK                           Private and public database links

From above we can find that we need to use DB_LINK in INCLUDE clause for exporting Database Links. Find below steps which I used to re-create database link in another schema

SQL> conn test/test123
Connected.
SQL> create database link TO_AMIT connect to AMIT identified by amit123 using 'test10';

Database link created.

SQL> select sysdate from dual@to_amit;

SYSDATE
---------
18-JAN-09

SQL> select owner,db_link,host from dba_db_links;

OWNER                          DB_LINK                                                      HOST
------------------------------ ------------------------------------------------------------ --------------------
TEST                           TO_AMIT.REGRESS.RDBMS.DEV.US.ORACLE.COM                      test10

D:\>expdp system/oracle schemas=TEST include=DB_LINK dumpfile=expdp.dmp

Export: Release 10.2.0.1.0 - Production on Sunday, 18 January, 2009 16:18:06

Copyright (c) 2003, 2005, Oracle.  All rights reserved.

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
Starting "SYSTEM"."SYS_EXPORT_SCHEMA_01":  system/******** schemas=TEST include=DB_LINK dumpfile=expdp.dmp
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 0 KB
Processing object type SCHEMA_EXPORT/DB_LINK
Master table "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYSTEM.SYS_EXPORT_SCHEMA_01 is:
  G:\ORACLE\PRODUCT\10.2.0\ADMIN\TEST10\DPDUMP\EXPDP.DMP
Job "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully completed at 16:18:10

D:\>impdp system/oracle remap_schema=TEST:AMIT include=DB_LINK dumpfile=expdp.dmp

Import: Release 10.2.0.1.0 - Production on Sunday, 18 January, 2009 16:20:38

Copyright (c) 2003, 2005, Oracle.  All rights reserved.

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
Master table "SYSTEM"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "SYSTEM"."SYS_IMPORT_FULL_01":  system/******** remap_schema=TEST:AMIT include=DB_LINK dumpfile=expdp.dmp
Processing object type SCHEMA_EXPORT/DB_LINK
Job "SYSTEM"."SYS_IMPORT_FULL_01" successfully completed at 16:20:40

D:\>exit

SQL> l
  1* select owner,db_link,host from dba_db_links
SQL> /

OWNER                          DB_LINK                                                      HOST
------------------------------ ------------------------------------------------------------ --------------------
TEST                           TO_AMIT.REGRESS.RDBMS.DEV.US.ORACLE.COM                      test10
AMIT                           TO_AMIT.REGRESS.RDBMS.DEV.US.ORACLE.COM                      test10

SQL> conn amit/amit123
Connected.
SQL> select sysdate from dual@to_Amit;

SYSDATE
---------
18-JAN-09
18 January, 2009 by 10g 4

ORA-00600 [12333]

ORA-00600 with [12333] is an error that I have encountered on multiple occasions. This post is to shed some light on what this error is all about.

To start with let us try to understand when does oracle report ora-00600 error. An ora-00600 error is raised in the exception handler section of Oracle’s c-program code. i.e. Coders have forseen certain exceptional situation that are potential threats to data integrity or memory integrity and written appropriate exceptional handlers to report a ora-00600 error with appropriate information about the exception condition.

Details like [12333] indicate or provide hints as to what caused the exception. ora-00600 [12333] is reported when the server recieves data from a client and the server cannot recognize the data format. This error mostly is because of issues with network and does not indicate any data corruption.

Few Common Causes & Suggestions:
1. An incompatible client software can cause such an internal error. Metalink document 207303.1 explains the supported combinations of clients and Server release.

2. An incompatible NLS settings on Client. Check NLS settings especially ORA_NLS33 for 9i, ORA_NLS10 for 10g.

3. Apart from this, it could be because of network issues, client software, TIMEOUT setting, etc. Try to narrow down the error to a particular client machine/ client software.

4. This can be because of an Oracle bug also. Refer to metalink document 428629.1 for list of known bugs with ora-00600 [12333].

As a last resort you can check this with Oracle Support for any new issues.

20 May, 2008 by 10g 0

Disclaimer!!

This blog reflect our own views and do not necessarily represent the views of our current or previous employers.

The contents of this blog are from our experience, you may use at your own risk, however you are strongly advised to cross reference with Product documentation and test before deploying to production environments.

Subscribe to Blog via Email

Enter your email address to subscribe to this blog and receive notifications of new posts by email.

Join 188 other subscribers