Monday, June 30, 2008

ORA-20000: ORU-10027: buffer overflow, limit of 1000000 bytes

ORA-20000: ORU-10027: buffer overflow, limit of 1000000 bytes


Example

Inserting record with ID: 41247
Inserting record with ID: 41248
DECLARE

*

ERROR at line 1:
ORA-20000: ORU-10027: buffer overflow, limit of 1000000 bytes
ORA-06512: at "SYS.DBMS_OUTPUT", line 32
ORA-06512: at "SYS.DBMS_OUTPUT", line 97
ORA-06512: at "SYS.DBMS_OUTPUT", line 112
ORA-06512: at "BIMA.LOBAUDIT", line 129
ORA-06512: at line 5


Details

The above error was caused by the fact that dbms_output.putline has reached its buffer
limit of 1000000 bytes , this is an absolute limit. This limit does not exist for 10GR2 and above


Solution

The best solution is to use an alternative method to create a log file such as "create directory method" , or the
less secure UTL_FILE approach , a simple "spool" will also work.

For 10GR2 and above this limit does not exist you can get around this issue as follows set serveroutput on size unlimited
format word_wrapped.

Monday, June 9, 2008

RMAN-06091 no channel allocated for maintenance (of an appropriate type)

RMAN-06091 no channel allocated for maintenance (of an appropriate type)

Example


RMAN-00571: ===========================================================

RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============

RMAN-00571: ===========================================================

RMAN-03002: failure of delete command at 06/03/2008 23:22:20

RMAN-06091: no channel allocated for maintenance (of an appropriate type)


Details


Backups failing due to the following when running any of the following options with "delete obsolete"


RMAN> delete obsolete;

RMAN> delete noprompt obsolete;

RMAN> delete force obsolete;



Solution


Run the following to specify the device type , in this example use device type disk


RMAN> delete noprompt obsolete device type disk;


If the above fails, allocate the appropriate channel for maintenance , perform the appropriate crosschecks and delete option.
In this example the device type disk was allocated for maintenance

ALLOCATE CHANNEL FOR MAINTENANCE DEVICE TYPE DISK;

run

{

report obsolete;

CROSSCHECK BACKUP;

CROSSCHECK COPY;

DELETE EXPIRED BACKUP;

DELETE EXPIRED COPY;

delete obsolete;

}

release channel;

ORA-00344 : unable to recreate


ORA-00344 unable to re-create


Example


RMAN> alter database open resetlogs;

RMAN-00571: ===========================================================

RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============

RMAN-00571: ===========================================================

RMAN-03002: failure of alter db command at 06/09/2008 10:03:56

ORA-00344: unable to re-create online log 'D:ADMINDATAFILESREDOREDO03.LOG'

ORA-27040: skgfrcre: create error, unable to create file

OSD-04002: unable to open file

O/S-Error: (OS 3) The system cannot find the path specified.


In the example above we try to perform an "alter database open resetlogs" however we get an IO

error due the fact that the directory path "D:ADMINDATAFILESREDO" does not exist.


An additional side note is that in this example only the directory structure needs to be recreated ,

you do not need to have an actual redo log on file system , an "open resetlogs" will create a new

redo log as specified in the control file.


Details


ORA-00344: unable to re-create

ORA-27040: skgfrcre: create error, unable to create file

OSD-04002: unable to open file

O/S-Error: (OS 3) The system cannot find the path specified.


ORA-00344 is an IO error , you may have tried to open a database with incorrect redo log locations

specified in the control file.


Solution


You can either recreate the directory structure as specified in the error.


Create a new control file with the desired directory structure for the redo logs.


Tuesday, June 3, 2008

VERIFY CATCPU.SQL WAS EXECUTED

VERIFY IF CATCPU.SQL WAS EXECUTED
Strangley enough people often apply Oracle CPU's (Critical Patch Updates) without running catcpu.sql 
onces the binaries have been upgraded.

Check the readme for the CPU (Critical Patch Update ) for instruction on how catcpu.sql is executed.
The readme is accompanied with the patch from metalink.
When you apply CPU (Critical Patch Update ) on a Oracle home you are patching the binaries only.
Catcpu.sql is run to patch the database.


You
have to run catcpu.sql in an upgraded or newly created database from an oracle home where the
CPU patch is applied.


In an upgrade operation run catcpu.sql after running catpatch.sql (for 9i) or catupgrd.sql (for 10G).

For newly created database run catcpu.sql after starting up the database. This is to ensure that the
database vulnerabilities are fixed by making the necessary changes in the dictionary.

To Verify If catcpu.sql has been run against a database run the following:

col action_time for a28

col action for a6

col namespace for a9

col version for a10

col id for 99999999

col comments for a10

select * from registry$history;

e.g.

SQL> col action_time for a28

SQL> col action for a6

SQL> col namespace for a9

SQL> col version for a10

SQL> col id for 99999999

SQL> col comments for a10

SQL> select * from registry$history;

25-APR-08 10.17.22.950000 PM CPU SERVER 10.2.0.3.0 6637239 CPUJAN2008

25-APR-08 10.24.44.233000 PM CPU 6452863 view recom

pilation