Skip to main content

Posts

Showing posts from February, 2010

ORA-20000 -- Buffer Overflow

Often when we use dbms_output.putline in our sql's to print on the console after setting serveroutput on, it throws an error saying buffer overflow.

ERROR at line 1:
ORA-20000: ORU-10027: buffer overflow, limit of 2000 bytes
ORA-06512: at "SYS.DBMS_OUTPUT", line 35
ORA-06512: at "SYS.DBMS_OUTPUT", line 198
ORA-06512: at "SYS.DBMS_OUTPUT", line 139
ORA-06512: at line 9

Option to get rid of this issue are,

1. SET SERVEROUTPUT ON SIZE 100000000
You might get an error on 9i as below,
SP2-0547: size option 276447232 out of range (2000 through 1000000)
In 10g you can specify UNLIMITED for SIZE
SET SERVEROUTPUT ON SIZE UNLIMITED

2. exec DBMS_OUTPUT.ENABLE(10000000);
This works for any value on 9i and it accepts UNLIMITED on 10gR2.

Else the other option is to use UTL_FILE to flush the output to a file.

Pre-requisite to use UTL_FILE is to create a database directory.

CREATE DIRECTORY 'DIRNAME' AS 'PATH' '';

Path should already be adde…

11.5.10.2 Cloning/Migration from Linux 4 to Linux 5

Lately i wanted to try out an upgrade from 11.5.10.2 to R12.1.1. For this i needed to clone APPS from Linux 4 to Linux 5. I tried to search for some document on metalink but nothing concrete. All i got was 287453.1 Oracle Applications 11.5.10 - Installation Update Notes for Linux x86, Solaris SPARC, MS Windows, HP-UX PA-RISC, HP-Tru64, IBM AIX - B13590-01

Apart from this they do not even have a document on metalink/support portal certification matrix. So here it is.

OS Version:

Source:
Red Hat Enterprise Linux AS release 4 (Nahant)
2.6.9-5.ELsmp #1 SMP Wed Jan 5 19:30:39 EST 2005 i686 i686 i386 GNU/Linux

Destination:

Red Hat Enterprise Linux Server release 5.3 (Tikanga)
2.6.18-128.el5 #1 SMP Wed Dec 17 11:42:39 EST 2008 i686 i686 i386 GNU/Linux



DB Cloning:
--------------------------

Copy all the files from source to destination. Set the following env variables.
FILES: ORACLE_HOME, ALL DB FILES, CONTROL FILES, REDO FILES


Now set the env to start the database, As an example following was m…

Create Control File

I keep forgetting the syntax for create control file, thought it would help me and others for the syntax and the steps to create.

STEPS:


1. Shutdown the database
2. sqlplus '/as sysdba'
3. startup nomount
4. Create Control file (syntax below)
5. alter database open

CREATE CONTROLFILE REUSE DATABASE "anydatabasename" NORESETLOGS NOARCHIVELOG
MAXLOGFILES 50
MAXLOGMEMBERS 3
MAXDATAFILES 1000
MAXINSTANCES 8
MAXLOGHISTORY 500
LOGFILE
GROUP 1 'REDLOG PATH' SIZE 10M,
GROUP 2 'REDLOG PATH' SIZE 10M
DATAFILE
;


EXAMPLE:

CREATE CONTROLFILE REUSE DATABASE "PROD" RESETLOGS NOARCHIVELOG
MAXLOGFILES 50
MAXLOGMEMBERS 3
MAXDATAFILES 1000
MAXINSTANCES 8
MAXLOGHISTORY 500
LOGFILE
GROUP 1 '/APPS/proddata/log01a.dbf' SIZE 10M,
GROUP 2 '/APPS/proddata/log02a.dbf' SIZE 10M
DATAFILE
'/APPS/proddata/a_txn_data01.dbf' ,
'/APPS/proddata/a_txn_data02.dbf' …