Thursday, November 21, 2013

My Full Export Backup Script for Linux

I also want to share my Linux full export backup script.  The script accepts the instance name as a parameter for servers that uses multiple instances.

Here's the script:

#!/bin/sh

# Set Environment Variable
. /home/oracle/.bash_profile

# Set Oracle SID
export ORACLE_SID=$1

exp full=y userid=\"/ as sysdba\" statistics=none file=/oracle/flash_recovery_area/exportArea/full_$1_$(date +\%Y-\%m-\%d).dmp log=/oracle/flash_recovery_area/exportArea/full_$1_$(date +\%Y-\%m-\%d).log


My CRONTAB entry are as follows:

SHELL=/bin/bash

### DELETE OLD DUMP FILES
0 23 * * * find /oracle/flash_recovery_area/exportArea -type f -mtime +6 -exec rm -f {} \\\;

### FULL EXPORT
0  2  *  *  *     /oracle/flash_recovery_area/scripts/full_export.sh edctss01

My Full Export Backup Script for Windows

I want to share my full export backup script for Windows.  7 day old files are deleted at the end of the script.  I usually run this script daily through the Windows Task Scheduler.

Also as a note, I create these scripts on a redundant Windows machine where my main database is on UNIX/Linux.

I use the DATE function to generate a date-driven filename.  And the FORFILES function to delete old dump files.  Lastly, I granted sysoper account with EXP_FULL_DATABASE privileges (you may need to grant additional system privileges to the sysoper account, this is depending on what's inside the database;  examples of these are SYS.DBMS_DEFER_IMPORT_INTERNAL and SYS.DBMS_EXPORT_EXTENSION).

Here's the batch script:

exp userid=sysoper/oper@orcl file=D:\DB_backup\edctss01_fullexp_%DATE:~10,4%%DATE:~4,2%%DATE:~7,2%.dmp log=D:\DB_backup\edctss01_fullexp_%DATE:~10,4%%DATE:~4,2%%DATE:~7,2%.log full=y statistics=none

forfiles -p "D:\EXPORT_AREA" -s -m *.dmp /D -7 /C "cmd /c del @path"
forfiles -p "D:\EXPORT_AREA" -s -m *.log /D -7 /C "cmd /c del @path"

Wednesday, May 9, 2012

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

Tuesday, April 24, 2012

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.

Thursday, March 22, 2012

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).



Tuesday, February 28, 2012

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 {} \\\;