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

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

I kept getting following error while I was trying to run the Oracle Support suggested script:

ERROR at line 1: 
ORA-20000: ORU-10027: buffer overflow, limit of 2000 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 line 28 

I was suffering from short of time so instead of consulting the Oracle Support I googled it and found the following solution:

I opened the script and changed the following line

set serveroutput on 
with 
set serveroutput on size 1000000

 and then executed the script it executed nicely.

Oracle basically says this:

20000, 00000, "%s"

// *Cause:  The stored procedure 'raise_application_error'

//          was called which causes this error to be generated.

// *Action: Correct the problem as described in the error message or contact

//          the application administrator or DBA for more information.

It doesn't really help.

I finally found the answer to increase the output characters using:

DBMS_OUTPUT.ENABLE()

By default, you can output 2000 characters, the maximum is 1,000,000

DBMS_OUTPUT.ENABLE(1000000);

However, If the code excedes the limit you will get the same error.

You can also also use the following to fix the error message:

set serveroutput on size 1000000

Hope this helps.

Thanks,

following is the link from where I found the solution:

http://www.franklinfaces.com/Topic196-56-1.aspx

Your feedback/comment is quite important for improving this blog so feel free to leave your comments & suggestions

Read More

Oracle: Auto Backup

Oracle Auto Back up

1. Create ‘filname.sh’ file with following code anywhere in server after amendments. (Possible amendments in RED.) e.g. filename is make_db.sh. 

#!/bin/bash 

# 

# 

dy=`date +%a` 

hn=`hostname` 

bk='/opt/back' 

sb=$bk'/'$hn 

# 

#!/bin/bash 

# 

# 

dy=`date +%a` 

hn=`hostname` 

bk='/opt/back' 

sb=$bk'/'$hn 

# 

# 

cm_d01='tar -Pczf '$sb'/'$hn'_'$dy'_d01.tar.gz /opt/d01'       

cm_d02='tar -Pczf '$sb'/'$hn'_'$dy'_d02.tar.gz /opt/d02'       

cm_d02_da='tar -Pczf '$sb'/'$hn'_'$dy'_d02_data.tar.gz /opt/d02/oracle/proddata'   

# 

# 

dy1="Tue" 

dy2="Wed" 

dy3="Thu" 

dy4="Fri" 

dy5="Sat" 

dy6="Sun" 

dy7="Mon" 

both_bk_on=$dy7 

# 

# 

printf "\n" 

if test -d /opt/d01 ; then 

  printf "Source Folder d01 Exist ............... OK\n" 

  if test -d /opt/d02 ; then 

    printf "Source Folder d02 Exist ............... Ok\n" 

    if test -d $bk ; then 

      printf "$bk Folder Exist ............... Ok\n" 

    else 

      printf "Going to create $bk Folder\n" 

      mkdir $bk 

    fi 

    if test -d $sb ; then 

      printf "$sb Sub Folder Exist ............... Ok\n" 

    else 

      printf "Going to create $sb $ub Folder\n" 

      mkdir $sb 

    fi 

#   ------------------------------------------------------------------ 

    printf "Going to create tar file for d02 \n" 

    /home/oracle/down.sh 

    ls -l /opt/d02/oracle/proddata > $sb/list"_"$hn"_"$dy"_d02_b4.txt" 

    if test "$dy" = "$both_bk_on" ; then 

    $cm_d02 

   else 

   $cm_d02_da 

  fi 

    ls -l /opt/d02/oracle/proddata > $sb/list"_"$hn"_"$dy"_d02_aft.txt" 

    tar -tzvf $sb"/"$hn"_"$dy"_"d02.tar.gz > $sb"/list_"$hn"_"$dy"_d02_tar.txt" 

    if test "$dy" = "$both_bk_on" ; then 

      printf "Also going to create tar file for d01\n" 

      $cm_d01 

    fi 

    /home/oracle/up.sh 

#   ------------------------------------------------------------------ 

  else 

    printf "Source Folder D02 Does Not Exist\n" 

  fi 

else 

  printf "Source Folder d01 Does Not Exist\n" 

fi 

2. Now we will edit CRONTAB utility according to our requirement. Crontab is just like a scheduler. (See basics of CRONTAB at the end.) 
3. Add this line in CRONTAB ‘00 21 * * * /opt/back/make_db.sh’ 

The above line will automatically run the make_db.sh file everyday at 21:00. 

Manual Bakcup 

This document will guide you to take backup of single Tier R12. DB and Apps combine. Incase you are working on multi node repeat the steps for DB and Apps server separately. 

Take Backup 

1. Go to the directory where you want to place the backup...e.g. /opt/back. 
2. Down services. (So that no further live entries should made and it will speedup the process) 
3. Now take the backup as followed. 

tar -Pczf haseeb_oracle_04apr11.ar.gz /opt/haseeb_oracle/ 

(where tar -Pczf are the keywords then target folder with backup file name and then source folder with path(of which you want to take backup.)) 

4. Backup done. 

Place Backup 

5. Go to the path where you want to place the extracted file.(target folder) and run the following command 

tar -xzf back/haseeb_oracle_04apr11.ar.gz 

6. This will place the your extracted files in that directory. 
7. Now move the files accordingly so that your directory structure remains the same. 

In my case when I extracted the files it creates the ‘opt’ folder in existing ‘/opt’ folder (make sure there is hell off difference in '/opt' and 'opt/'. In '/opt','/'itself is directory.) 

While I need to retain the file structure like opt/oracle/. 

So I will remove the existing 'oracle' directory from '/opt' and move the oracle directory from 'opt/' to '/opt'. 

Now I’ll have the exact file structure what I need to have. 

All done. 

All the best. 

Your feedback/comment is quite important for improving this blog so feel free to leave your comments & suggestions

Read More

DBLINK : Connect to remote database

Connect to remote database

Create database link by executing following command in TOAD.

1.     create database link dblink1 connect to apps identified by apps  using 'TESTUPGRADE';

  Where ‘dblink1’ is link alias, first ‘APPS’ is schema name ,second ‘APPS’ is password and    ‘TESTUPGRADE’ is service name.

2.  select * from tablename@dblink1

   Before executing query you need to add entry of remote database in the ‘TNSNAMES.ORA’     as illustrated below using following command.

  vi /opt/d02/oracle/proddb/9.2.0/network/admin/PROD_test/tnsnames.ora

how to connect to remote database using dblink

Now you can access the tables of remote database by just adding DBLINK  alias at the end of the table e.g. tablename@dblink1. Best way to compare data of same table from two different databases.

If you want to remove the link then:

3.       drop database link dblink1 (if you want to drop link).

Your feedback/comment is quite important for improving this blog so feel free to leave your comments & suggestions

Read More