ORA-00600: internal error code, arguments: [kcratr_nab_less_than_odr]

I have an Oracle 11g R2 database on a Vista box. I got this error twice, and in both occasions, it was due to Vista crashing (ran out of battery, blue screen)

Here are the steps I performed to resolve this error

Launch SQL Plus from Oracle\bin folder ( not from Client folder)

SQL> conn sys/sys as sysdba
Connected to an idle instance.

SQL> startup mount
ORACLE instance started.

SQL> alter database recover;
Database altered.

SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-00600: internal error code, arguments: [kcratr_nab_less_than_odr], [1],
[136], [65730], [65738], [], [], [], [], [], [], []

SQL> shutdown immediate;
ORACLE instance shut down.

SQL> startup mount
ORACLE instance started.

SQL> alter database recover until cancel using backup controlfile;
alter database recover until cancel using backup controlfile
*
ERROR at line 1:
ORA-00279: change 3997604 generated at 02/11/2011 21:08:59 needed for thread 1
ORA-00289: suggestion :
C:\APP\RMANNI\RECOVERY_AREA\ORCLDB11G\ARCHIVELOG\2011_02_14\O1_MF_1_136_%U_.ARC
ORA-00280: change 3997604 for thread 1 is in sequence #136

Since my database is not running in archive log mode, I couldn't find the file specified in the above error message. I proceeded with the following

SQL> alter database recover continue default;
alter database recover continue default
*
ERROR at line 1:
ORA-00308: cannot open archived log
'C:\APP\RMANNI\RECOVERY_AREA\ORCLDB11G\ARCHIVELOG\2011_02_14\O1_MF_1_136_%U_.ARC'
ORA-27041: unable to open file
OSD-04002: unable to open file
O/S-Error: (OS 2) The system cannot find the file specified.


SQL> alter database recover cancel;
Database altered.

If the above step errored out, then proceed with the lines shown below inbetween the dotted lines, if not

SQL> alter database open resetlogs;
Database altered.

SQL> Your database is good to go, You should now be able to connect


-----------If Alter database recover cancel Failed ------------------

SQL> alter database recover cancel;
alter database recover cancel
*
ERROR at line 1:
ORA-10879: error signaled in parallel recovery slave
ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below
ORA-01194: file 1 needs more recovery to be consistent
ORA-01110: data file 1: 'C:\APP\RMANNI\ORADATA\ORCLDB\SYSTEM01.DBF'

SQL> recover database using backup controlfile until cancel;
ORA-00275: media recovery has already been started

SQL> shutdown immediate;
ORACLE instance shut down.

SQL> startup mount
ORACLE instance started.

Now, when you try to recover, Oracle will provide a suggestion,provide the path for your REDO log files. One of the log files will fix your database. I started with Redo03, but Redo01 fixed my database. You probably don't need to shutdown and restart the database for each Redo log flies.


SQL> recover database using backup controlfile until cancel;

ORA-00279: change 2448238 generated at 11/16/2010 22:05:53 needed for thread 1
ORA-00289: suggestion :
C:\APP\RMANNI\FLASH_RECOVERY_AREA\ORCLDB\ARCHIVELOG\2011_02_14\O1_MF_1_67_%U_.ARC
ORA-00280: change 2448238 for thread 1 is in sequence #67

Specify log: {=suggested | filename | AUTO | CANCEL}
C:\app\rmanni\oradata\ORCLDB\REDO03.LOG
ORA-00310: archived log contains sequence 66; sequence 67 required
ORA-00334: archived log: 'C:\APP\RMANNI\ORADATA\ORCLDB\REDO03.LOG'
ORA-10879: error signaled in parallel recovery slave
ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below
ORA-01194: file 1 needs more recovery to be consistent
ORA-01110: data file 1: 'C:\APP\RMANNI\ORADATA\ORCLDB\SYSTEM01.DBF'

SQL> shutdown immediate;
ORACLE instance shut down.

SQL> startup mount
ORACLE instance started.

SQL> recover database using backup controlfile until cancel;
ORA-00279: change 2448238 generated at 11/16/2010 22:05:53 needed for thread 1
ORA-00289: suggestion :
C:\APP\RMANNI\FLASH_RECOVERY_AREA\ORCLDB\ARCHIVELOG\2011_02_14\O1_MF_1_67_%U_.ARC
ORA-00280: change 2448238 for thread 1 is in sequence #67

Specify log: {=suggested | filename | AUTO | CANCEL}
C:\app\rmanni\oradata\ORCLDB\REDO02.LOG
ORA-00310: archived log contains sequence 65; sequence 67 required
ORA-00334: archived log: 'C:\APP\RMANNI\ORADATA\ORCLDB\REDO02.LOG'
ORA-10879: error signaled in parallel recovery slave
ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below
ORA-01194: file 1 needs more recovery to be consistent
ORA-01110: data file 1: 'C:\APP\RMANNI\ORADATA\ORCLDB\SYSTEM01.DBF'

SQL> Shutdown immediate;
ORACLE instance shut down.

SQL> startup mount;
ORACLE instance started.

SQL> recover database using backup controlfile until cancel;
ORA-00279: change 2448238 generated at 11/16/2010 22:05:53 needed for thread 1
ORA-00289: suggestion :
C:\APP\RMANNI\FLASH_RECOVERY_AREA\ORCLDB\ARCHIVELOG\2011_02_14\O1_MF_1_67_%U_.ARC
ORA-00280: change 2448238 for thread 1 is in sequence #67

Specify log: {=suggested | filename | AUTO | CANCEL}
C:\app\rmanni\oradata\ORCLDB\REDO01.log
Log applied.
Media recovery complete.

SQL> alter database open resetlogs;
Database altered.

-----------If Alter database recover cancel Failed ------------------

Oracle: Disable Archive log mode

Launch SQLPLUS and login as SYS

Determine if the database is setup in archive log mode

SQL> Select log_mode from v$database

SQL> archive log list;

Archive log list gives a little bit detailed information about your archive log setup. By default, when you enable archive log, all the archive log files are written into flash_recovery_area.Below command will show you the OS path

sql> show parameter recovery_file_dest

To Disable Archive log
SQL> shutdown immediate
SQL> startup mount
SQL> alter database noarchivelog
SQL> alter database open
SQL> archive log list

Your database is now running in NO archive log mode

Oracle: Enable Archive log mode

Launch SQLPLUS and login as SYS

Determine if the database is setup in archive log mode

SQL> Select log_mode from v$database

SQL> archive log list;

Archive log list gives a little bit detailed information about your archive log setup. By default, when you enable archive log, all the archive log files are written into flash_recovery_area.Below command will show you the OS path

SQL> show parameter recovery_file_dest

To Enable Archive log
SQL> shutdown immediate
SQL> startup mount
SQL> alter database archivelog
SQL> alter database open
SQL> archive log list

Your database is now running in archive log mode

Oracle: Import 11g data dump into 10g

You can use Oracle data dump to export data dump from 11g server, and import into 10g using the

data Pump parameter called Version. When you use Version, Datapump exports all objects that are compatible for your target version

So, If your database is 11g and you want to export 11g and import into 10g

from 11g db cmd promt> expdp Test_schema_name/passs directory=datapump schemas=Test_Schema_Name Version=10.2.0.4.0

Once the export is done, you do the regular import from 10g server.

Oracle: Convert XMLType to Varchar2

Use xmltype.getStringVal() or xmltype.getClobVal() based on the size.

declare
myxml_type xmltype;
mystr Varchar2(4000);
begin
mystr :=myxml_type.getStringVal();
end;
/

Explain Plan table in Toad

There are 2 options

1. Use PLAN_TABLE that comes with the schema
2. Use TOAD_PLAN_TABLE (Created by Toad)

Option 1

Query user table for PLAN_TABLE

Select * from user_tables where table_name ='PLAN_TABLE'

Set this table in Toad Menu

View->Toad Options-> Select Oracle

on the right had side, you will see options for Explain Plan

Option 2

You can create TOAD_PLAN_TABLE, by running TOADPREP.SQL (Found in older version of Toads). This is no longer available in Toad 10

ORA-01172:recovery of thread 1 stuck at block xxxx of file x

Lately, I've been plagued with all sorts of Oracle start up errors on my Vista box. I have Oracle 11gR2 installed, and it seems to crash everytime I force reboot my vista box

Yesterday I got,

ORA-01172:recovery of thread 1 stuck at block xxxx of file x

To solve

1) Open SqlPlus from Oracle Bin folder, login as sys/zzz as sysdba
2) Since the database is not open, you can only query on fixed tables/Views
3) Query on v$datafile for the file name in the error message

If the error is "ORA-01172:recovery of thread 1 stuck at block 1241 of file 3",

then Select name from v$datafile where file#=3

4) Recover datafile name_from_the_above_select
5) If file is recovered, then try
Alter database open

6) In my case, I got some weird ora-6000 [4194], So I did a shutdown and startup force, which resovled my issue

7) Shutdown immediate
8) Startup force

ORA-12528: TNS:Listener: All Appropriate instances are blocking new connections

• Go to the database server box
• Navigate to the bin folder and launch SQLPLUS (Usually C:\app\rmanni\product\11.1.0\db_1\BIN )

Luanch sqlplus and connect to the database as SYS without using @TNSAlias

ex: conn sys/sys as sysdba

Once you are in, do a

• Shutdown
• Startup

You are good to go.

Oracle 11g Installation: File not found

Oracle 11g Release 2 Installation files contains 2 zip files.

1. win32_11gR2_database_1of2.zip
2. win32_11gR2_database_2of2.zip

You need to unzip both the files into the same location. Unzipping the first one, will not unzip the second file.

Pretty lame, but you have to unzip both.

ORA-12514: TNS:listener does not currently know of service requested in connect descriptor

ORA-12514: TNS:listener does not currently know of service requested in connect descriptor is the most common and most frustrating error in Oracle

What it means is, In tnsnams.ora, the service_name parameter is in correct or not available

TNS_Service =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = machine_name)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = Database_Name)
)
)

In my instance, I have Oracle hosted on a Windows box, and my OracleServiceDatabase_Name
was not started.

Once I started the Windows service , I was able to connect to the database

Start->Run->Services.msc

Locate the OracleServiceDatabase_Name, and right click and start the service.

Regular expression to split comma separated values

with test as
(select '5/10/2007,36352737,,"first_name, last_name Jr.",211001,7204,-75.00' str from dual)
Select regexp_substr(str,'".*"|[^,]+', 1, level)
from test
connect by regexp_substr(str,'".*"|[^,]+', 1, level) is not null

Oracle: Spool to Excel from SQLPlus

Spooling the output from SQLPlus to log file is often in unreadable format.

To Spool SQLPLus output to Excel (.xls) file, the trick is to turn On "MARKUP HTML"

SQL>set markup html on
SQL>set feedback off

SQL>spool c:\test.xls

SQL>select * from user_objects where rownum <= 20;
SQL>spool off

SQL>set markup html off

Oracle: Forgot SYS and SYSTEM password

If you forget or lost both SYS and SYSTEM password, you can reset them without logging in to the database.

Here is a cool utility to reset SYS and SYSTEM password, if you ever loose or forget the passwords for these account.

This is tested in Windows box only ( orapwd should work in Unix too)
---------------------------------------------------------------------

1) Go to services ( start -> Run->Services.msc)
2) Stop all Oracle services.
3) Find PWDxxxx.ora file. It is usually located in the Oracle\product\10.x\db_x\database. xxxx stands for your Oracle SID. For example, if you database is called ORCL, then the file will be PWDorcl.ora
4) Back up the existing file (just rename the file)
5) Generate new PWDxxxx.ora file using orapwd utility.
6) Go to command prompt, type
ORAPWD file=path_to_the_PWDxxx.ora_file password=zzzz
zzzz will the new password for both SYS and SYSTEM account
7) Now you will see a new PWDxxx.ora file generated.
8) Restart your Oracle service
9) You should be able to connect to the database with the new password.

ORA-04028: cannot generate diana for object

ORA-04028: cannot generate diana for object object_name

Goes away if you recreate the object in question

I got this error when compiling a package. The error said "Cannot generate Diana for a particular view".

There was nothing wrong with view, and select * from view_name ran fine with no errors.

I dropped and recreated the view, the Diana message went away.

Oracle: Regular expression to remove html tags

There are two ways to strip HTML tags from a string

1) Try to extract everything between end html tag(>)and next starting html tag (<)

2) First approach becomes complicated when you have nested tags. So the solution is to replace everything that is between (<) and (>)

SQL below replaces all the tags with space.

Select Regexp_Replace('<>source string<>bold<> with html tags<>', '<[^>]+>','') from dual

*Note: just put some tags between <> to test, google is not displaying the tags properly. The inner tags (before and after bold ) simulate nested tags.

Oracle: How to replace non printable characters in a string

You can replace non printable characters in a string using regexp_replace

select regexp_replace('Test'||chr(10)||chr(13)||chr(1)||' String ', '([^[:print:]])',' ') from dual

If you want to retain the the new line characters like chr(10) and chr(13) then

Select regexp_replace('Test'||chr(10)||chr(13)||chr(1)||' String ','['||chr(1)||'-'||chr(9)||']|['||chr(11)||'-'||chr(12)||']|['||chr(14)||'-'||chr(31)||']',' ') from dual

Above sql is removing chr from 1-9, 11-12 and 14-31



Here is SQL to identify printable, non printable, control characters

select level,CHR(level),
decode(chr(level), regexp_substr(chr(level), '[[:graph:]]'), '*') is_graph, --printable character
decode(chr(level), regexp_substr(chr(level), '[[:blank:]]'), '*') is_blank,
decode(chr(level), regexp_substr(chr(level), '[[:alnum:]]'), '*') is_alnum, --Any alphanumeric character, [A-Za-z0-9]
decode(chr(level), regexp_substr(chr(level), '[[:alpha:]]'), '*') is_alpha, -- Any letter, [A-Za-z]
decode(chr(level), regexp_substr(chr(level), '[[:digit:]]'), '*') is_digit, -- Any digit, [0-9]
decode(chr(level), regexp_substr(chr(level), '[[:cntrl:]]'), '*') is_cntrl, --Any character not part of the character classes: [:upper:], [:lower:], [:alpha:], [:digit:], [:punct:], [:graph:], [:print:], [:xdigit:]
decode(chr(level), regexp_substr(chr(level), '[[:lower:]]'), '*') is_lower, -- Any lowercase letter, [a-z]
decode(chr(level), regexp_substr(chr(level), '[[:upper:]]'), '*') is_upper, --Any uppercase letter, [A-Z]
decode(chr(level), regexp_substr(chr(level), '[[:print:]]'), '*') is_print, --Any printable character
decode(chr(level), regexp_substr(chr(level), '[[:punct:]]'), '*') is_punct, --Any punctuation character: START ! ' # S % & ' ( ) * + , - . / : ; < = > ? @ [ / ] ^ _ { | } ~ END
decode(chr(level), regexp_substr(chr(level), '[[:space:]]'), '*') is_space, --A tab, new line, vertical tab, form feed, carriage return, or space
decode(chr(level), regexp_substr(chr(level), '[[:xdigit:]]'), '*') is_xdigit --Any hexadecimal digit, [0-9A-Fa-f]
from dual
where level between 0 and 255
connect by level <= 256

PLS-00306 WRONG number or types of arguments in call to XMLAGG

This a bug in Oracle. This error is thrown when you use ORDER BY clause with XMLAGG in PL/SQL.

Work Around: Remove the ORDER BY Clause

Funny part is, the SQL will work fine in SQLPLUS, but when you use in PL/SQL it will throws the above error.

Versions confirmed as being affected: 9.2.0.2 to 9.2.0.6

This bug is fixed from 9.2.0.7 onwards

ORA-38104: Columns referenced in the ON Clause cannot be updated

When you use Merge statement, you cannot update the columns reference in the ON Clause.

Not many know that, Merge supports "WHERE Clause".

Add a Where Clause to your Update statement under "When Matched"

Merge Into ...
Using ()
When Matched then
Update ...
Where ...
When Not Matched then
......

ORA-28056: Writing audit records to Windows Event Log failed

I started getting this error after installing Oracle llg database.

Here are couple of options to fix it.

Open Event Viewer: Click Start, click Control Panel,click Administrative Tools,
and then double-click Event Viewer.

1) Clear Application log. Right click on Application, Select clear all events

2) Or, Increase the Application log size. Right click on Application, Select properties, Increase Maximum log size.

3) Or, Change Max log size Over write options. Select "Overwrite events as needed"

ORA-06553: PLS-306: wrong number or types of arguments in call to 'OGC_X'

Can't figure out why your SELECT statement is throwing 'OGC_X' error ?

1) Most probably, your SELECT has a table with an alias "x"
2) Your oracle is version is lower than 11g
3) You probably mistyped column name

Solution
Prior to Oracle 11g, Oracle by default comes with a synonym 'X' defined for "OGC_X".

1) In your SELECT, if you mistype the column name from the table aliased as "x", Oracle tries to use the Synonym object "OGC_X", and hence you get the error.

Following SQL works
select * from dual x where x.dummy = 'X'

Now change, x.dummy to x.fummy (wrong column name), you will the error

select * from dual x where x.fummy = 'X'

In summary, if your table alias matches any synonym in your database and you typed the column name of the aliased table wrong, you get the error "ORA-06553: PLS-306: wrong number or types of arguments in call to ..."