Oracle Import Errors With ORA-03113, ORA-03114

When trying to import to a newly created database, Oracle import outputs with the following error:

IMP-00003: ORACLE error 3113 encountered
ORA-03113: end-of-file on communication channel
IMP-00003: ORACLE error 3114 encountered
ORA-03114: not connected to ORACLE

To correct this error, run the following scripts using the sys account:

$ORACLE_HOME/rdbms/admin/dbmsread.sql

$ORACLE_HOME/rdbms/admin/prvtread.plb

How To Make A Local Yum Repository Using The RHEL6 DVD

1. Insert RHEL 6 DVD and mount it.
2. Copy the Packages directory from the DVD on a local directory on your hard drive.

mkdir /RHEL6DVD
cp -R /media/RHEL_6.1\ x86_64\ Disc\ 1/Packages /RHEL6DVD/

3. Install createrepo

cd /RHEL6DVD/Packages
rpm -ivh deltarpm-3.5-0.5.20090913git.el6.x86_64.rpm python-deltarpm-3.5-0.5.20090913git.el6.x86_64.rpm createrepo-0.9.8-4.el6.noarch.rpm

4. Create new yum repository

vi /etc/yum.repos.d/RHEL6DVD.repo

[dvd]
name=RHEL6DVD
baseurl=file:///RHEL6DVD/Packages
enabled=1
gpgcheck=0


5. Update yum repository

yum update

6. To install new packages

cd /RHEL6DVD/Packages
yum install binutils-2.20.51.0.2-5.20.el6.x86_64

REP-0118 Unable to create temporary file

Earlier today, we were getting the following error when generating reports in Oracle Developer 6i:

REP-0118 Unable to create temporary file

It seems that the reports runtime wasn't able to create a temporary file in the reports temporary directory.  So, we simply changed the permission of this directory to correct the problem.  We gave write permission to the user who was running the report.  This directory is located here:

$ORACLE_HOME/reports60/TMP

Installations can be customized, so to get the exact location of the above folder, look for the regedit entry: REPORTS60_TMP

In summary, the cause of this error is the changing of the permission of the REPORTS60_TMP folder to read only.  The user running the report has no permission to write any file on this directory.

How to Drop an Oracle Account

Here's the command to drop an Oracle account or schema:

DROP USER username ;

or

DROP USER username CASCADE;

Use CASCADE option to drop all dependent objects on the schema (e.g. constraints).

find Not Working in cron

I'm getting the following error issuing a find in cron:

find: 0652-018 An expression term lacks a required parameter.

Here's my crontab entry:

0 23 * * * find /orafra/flash_recovery_area/exportArea -type f -mtime +6 -exec rm -f {} \;

After research and a lot of googling, I found out that cron is eating up some symbols, specifically the backslash "\" and the semicolon ";"

With that, here's my now working crontab entry:

0 23 * * * find /orafra/flash_recovery_area/exportArea -type f -mtime +6 -exec rm -f {} \\\;

Dropping Old Temporary Tablespace Hangs

I have recently switched my database to a new temporary tablespace and wanted to drop the old one to retrieve the space it was occupying. But the drop statement seem to hang or is taking too long.

Here's my drop statement:

DROP TABLESPACE temp INCLUDING CONTENTS AND DATAFILES;

To solve this, I issued the following query:

SELECT b.username, b.sid, b.serial#, b.terminal

FROM v$sort_usage a, v$session

WHERE a.session_num = b.serial#

AND a.tablespace = 'TEMP';

Then I killed the sessions that were using the old temporary segment (of course I asked permission from the user before killing them).

After this, the hanged drop tablespace command continued and dropped the temp tablespace successfully.

Cron Not Running Commands With $(date +%Y%m%d-%H%M)

I have a script that runs in the shell with no problems. But when I put it in cron, it fails to execute. Here's my crontab entry.

00 00 * * 0 /oracle/flash_recovery_area/scripts/rman-level0.sh PROD fb4srv > /oracle/flash_recovery_area/PROD/logs/PROD-LVL0-$(date +%Y%m%d-%H%M).log

Upon investigating the cron log, (I usually just check the mail) I saw this:

& 1

Message 1:

From oracle@somedomain.com Tue Feb 7 00:00:02 2012

Date: Tue, 7 Feb 2012 00:00:02 +0800

From: root@somedomain.com (Cron Daemon)

To: oracle@somedomain.com

Subject: Cron /oracle/flash_recovery_area/scripts/rman-level1.sh PROD fb4srv > /oracle/flash_recovery_area/PROD/logs/PROD-LVL1-$(date +

Content-Type: text/plain; charset=UTF-8

Auto-Submitted: auto-generated

X-Cron-Env:

X-Cron-Env:

X-Cron-Env:

X-Cron-Env:

X-Cron-Env:

/bin/bash: -c: line 0: unexpected EOF while looking for matching `)'

/bin/bash: -c: line 1: syntax error: unexpected end of file

&

So it seems, cron doesn't recognize the values after the + sign. And so, clearly, the error was because of the following characters: %Y%m%d-%H%M).log

Looking at the characters above, my guess is that the % sign was causing the problem. So, I escaped them or replaced them with \%.

My crontab entry now looked like this:

00 00 * * 0 /oracle/flash_recovery_area/scripts/rman-level0.sh PROD fb4srv > /oracle/flash_recovery_area/PROD/logs/PROD-LVL0-$(date +\%Y\%m\%d-\%H\%M).log

And this solved my problem. The script now runs in cron.

How to Change the Domain Name of An Already Running Oracle Database Instance

We have an application and database deployment that was done in virtual machines. Now we want to port this deployment to another office. This would mean changing the network settings for the virtual machines. We are running Oracle 11g 11.1.0.7 on RHEL 5.3. I was then required to change the domain name of the database in it. Here's what I did.

First I changed the network files on the server.

/etc/oratab

/etc/hosts

$ORACLE_HOME/network/admin/listener.ora

$ORACLE_HOME/network/admin/tnsnames.ora

Then I issued the following statements in the database.

ALTER DATABASE RENAME global_name TO oracle_sid.newdomain.com;

ALTER SYSTEM SET DB_DOMAIN = 'newdomain.com' SCOPE=spfile;

Then I restarted the database and did an ALTER SYSTEM REGISTER; to register the instance with the default listener.

To test, just try to connect to the database, or show parameter db_domain, or select * from global_name, or maybe issue a lsnrctl services command.

How to Update the Default JDK Installed in Oracle Application Server 10g 10.1.2.0.2 in Windows

We are currently running Oracle Application Server 10g 10.1.2.0.2 on a Windows 2003 x64 Server. The default Java that came with this release is Java 1.4.2. Now, one of our developers approached me and requested me to upgrade the Java so that she could use some of the new features in Java 6. Usually I would decline this request and force developers to use the existing technology. But in this case, I did differently because the Java running on our server is quite old and a bit slow.

Anyway, here's what I did to upgrade the Java.

First, download the JDK you need from oracle.com. In my case I downloaded JDK 1.6 update 13.

http://www.oracle.com/technetwork/java/javasebusiness/downloads/java-archive-downloads-javase6-419409.html#jdk-6u13-oth-JPR

Then, stop all running Oracle processes.

Install the downloaded package with its default settings (just do a next-next-next install).

Now backup the old JDK directory. This should be located at $ORACLE_HOME/jdk. Rename this directory to jdk.orig.

Now copy the newly installed JDK to the same location. You can find this in C:\Program Files\Java\.

Copy the following directories over the newly created $ORACLE_HOME/jdk WITHOUToverwriting the old files.

$ORACLE_HOME/jdk.orig/jre/lib/ext

and

$ORACLE_HOME/jdk.orig/jre/lib/fonts

Finally, start all Oracle Services.

REFERENCES

==========

How to Update the Default JDK Installed in Application Server 9.0.4 and 10.1.2 [ID 272808.1]