Clearing the RECYCLEBIN in an Oracle database

How does a DBA clear objects from a recyclebin of another user in an Oracle database ?...It can be done in one of the three ways listed below.

SQL> select owner,OBJECT_NAME,ORIGINAL_NAME,TS_NAME from dba_recyclebin;

OWNER      OBJECT_NAME                    ORIGINAL_NAME     TS_NAME
---------  ------------------------------ ----------------- ----------
USER1      BIN$b5VRkexOdOjgRAADurN2OQ==$0 TABLE1            TBL_SPACE
USER1      BIN$b5VRkexNdOjgRAADurN2OQ==$0 TABLE2            TBL_SPACE
USER1      BIN$b5VRkexMdOjgRAADurN2OQ==$0 TABLE3            TBL_SPACE
USER1      BIN$b5VRkexLdOjgRAADurN2OQ==$0 TABLE4            TBL_SPACE
USER1      BIN$b5VRkexJdOjgRAADurN2OQ==$0 TABLE5            TBL_SPACE
                                                 

Method-1
--------

SQL> purge table user1."BIN$b5VRkexOdOjgRAADurN2OQ==$0";

Table purged.
                                                 

Method-2
--------

SQL> purge table user1."TABLE2";

Table purged.
                                                 

Method-3
--------

SQL> drop table user1."BIN$b5VRkexMdOjgRAADurN2OQ==$0";
drop table xdb."BIN$lMZuKzEkDFfgRAADurN2OQ==$0"
               *
ERROR at line 1:
ORA-38301: can not perform DDL/DML over objects in Recycle Bin
                                                 

This is like killing the " mother of recyclebin evil " - we cannot perform DDL/DML on an object in recyclebin as the definition of the error states. The only reason for getting an ORA-38301 in this situation is because the RECYCLEBIN database parameter is turned on and turning it off lets a DBA issue a DROP TABLE command against a recyclebin table but at the cost of not being able to flashback the database to recover a lost table.

SQL> sho parameter recyclebin

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
recyclebin                           string      on

SQL> SQL> sho parameter spfile

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
spfile                               string      /opt/app/oracle/product/10.2.0
                                                 /dbs/spfileprod_db.ora

SQL> alter system set recyclebin=off scope=both;

System altered.

SQL> sho parameter recyclebin

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
recyclebin                           string      OFF


SQL> drop table user1."BIN$b5VRkexMdOjgRAADurN2OQ==$0";

Table dropped.
                                                 

sga_max_size and sga_target

In this post, I am going to be demonstrating the basics of working of the sga_max_size and sga_target and also to cover some myths of playing around with these parameters.

To begin with, sga_max_size is set in the spfile to a value of 200M and then I try to set the sga_target and the story unfolds as below.

SQL> sho parameter spfile

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
spfile                               string      /opt/app/oracle/product/10.2.0
                                                 /dbs/spfileprod_db.ora
SQL> sho parameter sga_

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
sga_max_size                         big integer 200M
sga_target                           big integer 0


SQL> sho sga

Total System Global Area  209715200 bytes
Fixed Size                  1977560 bytes
Variable Size             155194152 bytes
Database Buffers           50331648 bytes
Redo Buffers                2211840 bytes



SQL> sho parameter shared_pool_size

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
shared_pool_size                     big integer 84M

SQL> sho parameter db_cache_size

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_cache_size                        big integer 48M

SQL> sho parameter large

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
large_pool_size                      big integer 0

SQL> sho parameter java_pool_size

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
java_pool_size                       big integer 24M

SQL> sho parameter streams_pool

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
streams_pool_size                    big integer 0

Now, trying to set the sga_target to a value of 210M which is greater than that of sga_max_size gives out an error of ORA-02097 and ORA-00823, this shows that the sga_target can never be greater than the sga_max_size.

SQL> alter system set sga_target=210M scope=both;
alter system set sga_target=210M scope=both
*
ERROR at line 1:
ORA-02097: parameter cannot be modified because specified value is invalid
ORA-00823: Specified value of sga_target greater than sga_max_size

However, sga_target can be set less than or equal to sga_max_size.

SQL> alter system set sga_target=200M scope=both;

System altered.

SQL> sho parameter sga_

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
sga_max_size                         big integer 200M
sga_target                           big integer 200M

But an interesting point to be noted in here is that as soon as the sga_target is set to a non-zero value, it starts affecting the memory areas of the SGA which is evident from the change in the value of Database Buffers (db_cache_size) from 50331648 (48M) from the previous execution of SHO SGA to 92274688 (88M), while every other memory area remains unchanged.

SQL> sho sga

Total System Global Area  209715200 bytes
Fixed Size                  1977560 bytes
Variable Size             113251112 bytes
Database Buffers           92274688 bytes
Redo Buffers                2211840 bytes


SQL>  sho parameter db_Cache_size

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_cache_size                        big integer 88M

SQL> sho parameter shared_pool_size

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
shared_pool_size                     big integer 84M

SQL> sho parameter java_pool

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
java_pool_size                       big integer 24M

SQL> sho parameter streams_pool

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
streams_pool_size                    big integer 0

SQL> sho parameter large_pool

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
large_pool_size                      big integer 0

Let's try to unset the sga_target and see if Database Buffers (db_cache_size) reverts back to what it was, but it does not.

SQL> alter system set sga_target=0 scope=both;

System altered.

SQL> sho sga

Total System Global Area  209715200 bytes
Fixed Size                  1977560 bytes
Variable Size             113251112 bytes
Database Buffers           92274688 bytes
Redo Buffers                2211840 bytes

SQL> sho parameter sga_


NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
sga_max_size                         big integer 200M
sga_target                           big integer 0


SQL>  sho parameter db_Cache_size

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_cache_size                        big integer 88M

It was hoped that bouncing the database would make a difference, on the contrary it did not. It is another discussion as to why the Database Buffers (db_Cache_size) does not change back to what it was even after unsetting the sga_target and bouncing the database, let's not get into the nitty-gritty of that.

SQL> sho sga

Total System Global Area  209715200 bytes
Fixed Size                  1977560 bytes
Variable Size             113251112 bytes
Database Buffers           92274688 bytes
Redo Buffers                2211840 bytes

SQL> sho parameter db_Cache_size

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_cache_size                        big integer 88M

SQL> shut immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> startup
ORACLE instance started.

Total System Global Area  209715200 bytes
Fixed Size                  1977560 bytes
Variable Size             113251112 bytes
Database Buffers           92274688 bytes
Redo Buffers                2211840 bytes
Database mounted.
Database opened.


SQL> sho parameter sga_

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
sga_max_size                         big integer 200M
sga_target                           big integer 0


SQL> sho parameter db_Cache_size

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_cache_size                        big integer 88M

The Oracle server does not let you to resize the sga_max_size on the fly since it is not a dynamically changeable parameter. Let's now change sga_target to the value of sga_max_size. After this I will comment the sga_max_size and add sga_target=200M in the pfile, convert it into spfile and bounce the database.

SQL> sho parameter sga_

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
sga_max_size                         big integer 200M
sga_target                           big integer 0


SQL> alter system set sga_max_size=0 scope=both;
alter system set sga_max_size=0 scope=both
                 *
ERROR at line 1:
ORA-02095: specified initialization parameter cannot be modified


SQL> alter system set sga_target=200M scope=both;

System altered.

SQL> sho parameter sgA_

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
sga_max_size                         big integer 200M
sga_target                           big integer 200M



### MAIN SGA PARAMS ###
###sga_max_size=200M
sga_target=200M

SQL> create spfile from pfile;

File created.

SQL> startup
ORACLE instance started.

Total System Global Area  209715200 bytes
Fixed Size                  1977528 bytes
Variable Size              75502408 bytes
Database Buffers          130023424 bytes
Redo Buffers                2211840 bytes
Database mounted.
Database opened.

SQL> sho parameter sga_

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
sga_max_size                         big integer 200M
sga_target                           big integer 200M

SQL> sho parameter db_cache_size

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_cache_size                        big integer 0

SQL> sho parameter shared_pool_size

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
shared_pool_size                     big integer 0

SQL> sho parameter large_pool

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
large_pool_size                      big integer 0

SQL> sho parameter java_pool_s

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
java_pool_size                       big integer 0

SQL> sho parameter streams_pool

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
streams_pool_size                    big integer 0
SQL>

It now shows that as soon as the sga_target is set and set_max_size is unset, all the other memory area related parameters get reset to a value of zero(0) - making it clear that the Automatic Shared Memory Management is fully functional and that Memory Manager (MMAN) background process is actively doing its job. Also has the Database Buffers (db_cache_size) taken a higher value of 130023424 (124M) against the previous 88M.


There is yet another point to be noted here, besides sga_max_size being commented in spfile - it takes a value equal to the sga_target.

SQL> sho parameter sga_

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
sga_max_size                         big integer 200M
sga_target                           big integer 200M


UNIX$> grep -i "sga" initprod_db.ora
###sga_max_size=200M
sga_target=200M


UNIX$> grep -i "sga" $ORACLE_HOME/dbs/spfileprod_db.ora
*.sga_target=200M

The conclusions that we can draw from these little experiments are listed below.

1) sga_max_size cannot be changed without bouncing the database.

2) sga_target can be changed on the fly without bouncing the database but it can never be set to a value greater than the sga_max_size.

3) If the sga_max_size is not set in either of the pfile or spfile and the sga_target is set then the sga_max_size takes the value of sga_target when you do a sho parameter sga_

Oracle Tuning Methodology

Here is what Burleson Consulting (BC) has to say about the sequence of
tuning a performance situation in an Oracle database. Click on the slides
to read more on this from BC's website.

Data Guard Operational Modes

Here is a quick map of the three different modes of operation in a Data Guard (DG) configuration. There are two snaps, one has a traditional white background and the other for reduced power consumption - when viewed

FTP automation on Windows and Unix

Automation is one thing that I like the most, it makes my job easier on a daily basis - makes you sit back and relax and sprawl whilst a critical task is being completed by the script but in the long run the same script would be more than sufficient to get me fired out of my Organization since every thing is taken care by the machine against man - let me not sound more or less like a Cyborg from a James Cameron's famous movie ;-).


Here are two scripts that would enlable you to automate an FTP process from a source host to a destination.

###############
## U N I X  ###
###############

##########################
### FTP_Automation.ksh ###
##########################
#!/usr/bin/ksh

if [ "$#" -ne 3 ]
then
echo " USAGE: "
echo " ------ "
echo "\n"
echo " ksh FTP_Automation.ksh TARGET_HOST_OR_IP PASSWORD_OF_USERNAME USERNAME"
echo "\n"
echo " Examples : "
echo " ---------- "
echo "\n"
echo " ksh FTP_Automation.ksh unix_host_2 manager oracle"
echo "\n"
echo " ksh FTP_Automation.ksh 10.10.11.12 manager oracle"
echo "\n"
exit 1
fi

export target_host=$1
export username=$3
export password=2
ftp -niv << E_O_F
open $target_host
user $username $password
hash
bin
cd $HOME/unix2
put $HOME/unix1/file1
put $HOME/unix1/file2
put $HOME/unix1/file3
bye
E_O_F


#### The files are being copied from $HOME/unix1 directory
#### on unix_host_1 host (server) to $HOME/unix2 directory
#### on unix_host_2 host (server).


#### The files will be copied to $HOME/unix2 directory
#### Please change the locations and the file names in the 
#### script if you want the files to be copied to some other 
#### location.


###########
## E N D ##
###########

unix_host_1:$> ksh FTP_Automation.ksh 10.10.11.12 pass root

#####################
## W I N D O W S  ###
#####################

##########################
### FTP_Automation.txt ###
##########################

user user_name
pass_word
bin
hash
put "C:\Documents and Settings\oracle_and_unix\Desktop\file1.txt"
put "C:\Documents and Settings\oracle_and_unix\Desktop\file2.txt"
bye

#########
# E N D #
#########

##########################
### FTP_Automation.bat ###
##########################

ftp -n -s:C:\Documents and Settings\oracle_and_unix\Desktop\FTP_Automation.txt 10.10.11.12

#########
# E N D #
#########

The batch file FTP_Automation.bat implicitly calls the FTP_Automation.txt, the batch file (FTP_Automation.bat) can be scheduled in Scheduled Tasks of 'Control Panel' or simply run from the command prompt or even a double click on the batch file would inititate the ftp copy of files.

ORA-01801: date format is too long for internal buffer

Target type=Agent 
Host=UNIX 
Occurred At=Feb 29, 2008 6:19:17 AM EST 
Message=25 successive severity upload requests have failed. Last 
severity upload error is ORA-01801: date format is too long for internal buffer 
Metric=Consecutive severity upload failure count 
Metric value=25 
Severity=Critical 
Acknowledged=No 
Notification Rule Name=Misconfigured agents 
Notification Rule Owner=SYSMAN 

This is an out of the blue error that any environment would throw out as
part of the daily monitoring using OEM GC. This usually crops up from the
target host where the OEM agent takes care of the job of monitoring the
host and its instances and other processes. Any DBA out of his/her
instinctive efforts would try to bounce the agent thinking the agent has
gone crazy after having run for so many days or may be so many months.
But with an ./emctl upload from the AGENT_HOME/bin shows up
the same error again.

Some folks even try to bounce the OEM console giving it a vain attempt to
resolve the issue but to no avail. However the actual trick is to bounce
the OEM repository database along with the OEM console, to work around the
situation. The order of bounce to be followed is shown below.

Shutdown Order
--------------

OEM GC --> OEM Repository DB

OMS_HOME/opmn/bin/opmnctl stopall

ORACLE_HOME/bin/lsnrctl stop LISTENER

ORACLE_HOME/bin/dbshut


Startup Order
-------------

OEM Repository DB --> OEM GC

ORACLE_HOME/bin/dbstart

ORACLE_HOME/bin/lsnrctl start LISTENER

OMS_HOME/opmn/bin/opmnctl startall

Check for empty file in Unix systems

Often, there arises a need to check if a particular file exists and/or is it empty ?, this usually is the scenario when developing shell scripts to automate stuff and a file is created by the script to hold intermediate results. So when the script is scheduled to run next time, it has to execute a sequence of commands if the file exists or it would execute a different set of commands when the file is no longer available.

Here is a simple code snippet which checks for the existence of a file and that it is NOT empty to return TRUE and it returns FALSE otherwise.

if [ -s test_file ]  ### if the test_file exists and that it is not empty
                     ### i.e., file has contents
then
echo "TRUE"          ### returns 'true' ONLY if the file exists and it is NOT empty,
                     ### i.e., file has contents - file is not of 0 bytes in size
else
echo "FALSE"         ### returns 'false' if the file exists and it is empty
                     ### (0 bytes in size).
                     ### returns 'false' also if the file does not exist
fi

For different results to appear, try creating a file 'test_file' and by changing its contents to make it a sized file and/or to make it an empty file.

Funny Oracle Error....

I always have a habit of looking up for the errors returned by Oracle in the server itself using the 'oerr' utility before googling it out since 'oerr' gives you a suggestion of a quick fix saving you quite some time that is spent on rooting through the innumerable hits from Google for a workaround or a fix.

I stumbled upon an error when trying to pull the DDL of a materialized view using the dbms_metadata.get_ddl function, upon trying to find the cause/action of each of the errors in the returned error stack I was told to "Please contact someone who can fix the problem.", but who would that SOMEONE be....Hey Larry !!! Mr. Ellison, are you listening ;-)

SQL> select dbms_metadata.get_ddl('MATERIALIZED_VIEW','JUNK_MVIEW','BOGUS_SCHEMA') from dual;

ERROR:
ORA-06502: PL/SQL: numeric or value error
ORA-31605: the following was returned from LpxXSLResetAllVars in routine
kuxslResetParams:
LPX-1: NULL pointer
ORA-06512: at "SYS.UTL_XML", line 246
ORA-06512: at "SYS.DBMS_METADATA_INT", line 7511
ORA-06512: at "SYS.DBMS_METADATA_INT", line 9453
ORA-06512: at "SYS.DBMS_METADATA", line 1919
ORA-06512: at "SYS.DBMS_METADATA", line 2792
ORA-06512: at "SYS.DBMS_METADATA", line 4333
ORA-06512: at line 1

SQL> ! oerr ora 6502
06502, 00000, "PL/SQL: numeric or value error%s"
// *Cause:
// *Action:

SQL> ! oerr ora 31605
31605, 00000, "the following was returned from %s in routine %s:\nLPX-%d: %s"
// *Cause:  An LPX routine (XML/XSL processing) returned an internal
//          error number to its PL/SQL wrapper routine in facility KUX which
//          provides the implementation for package UTL_XML.
// *Action: Look up the LPX error number and follow its corrective action.

SQL> ! oerr lpx 1
00001, 00000, "NULL pointer"
// *Cause:  A NULL pointer was detected as an internal error condition.
// *Action: This is a programming error by the caller of the XML parser.
//          Please contact someone who can fix the problem.

Windows Oracle database administration scripts for hot backup and export backup

Automated conventional hot backup, export backup and their clean up script on a windows environment are pasted below - it is advised to go through the script / test on a Dev-Test environment before moving over to Prod since a couple of locations are to be hardcoded based on the environment. Most of the work is being carried out by pl/sql scripts, this gave me little chance to improvise my pl/sql skills ;-).


The .sql files in the .bat files create runtime sql files that will place the backup files in a folder named after the date string of the current day. The scripts are located in 'D:\oracle\backups\scripts' and the hot backup is taken in 'D:\oracle\backups\hot\DD-MON-YYYY' directory while the export backup is taken in 'D:\oracle\backups\exp\DD-MON-YYYY'. Based on your environment please change Drive letter and other locations, which has to be reflected in all the scripts and batch files.

Due to some limitations in the way this webpage is built some lines of the scripts may have been cropped, please click on the name of the script to open it in a new window which you can copy from.

###############################
# # #   H O T   B K U P   # # #
###############################

################################
### hot_backup_on_disk.bat  ####
################################

set ORACLE_SID=PROD

set ORACLE_HOME=D:\oracle\10.2.0

%ORACLE_HOME%\bin\sqlplus -S "/as sysdba" @D:\oracle\backups\scripts\01-bkup_dest.sql

%ORACLE_HOME%\bin\sqlplus -S "/as sysdba" @D:\oracle\backups\scripts\02-hot_bkup.sql

%ORACLE_HOME%\bin\sqlplus -S "/as sysdba" @D:\oracle\backups\scripts\03-arch_switch.sql

%ORACLE_HOME%\bin\sqlplus -S "/as sysdba" @D:\oracle\backups\scripts\04-ctrl_file.sql

###########################
####  01-bkup_dest.sql  ###
###########################

set head off
set feed off
set line 150
set serveroutput on
spool D:\oracle\backups\scripts\bkup_dest_create.sql
declare
dir varchar2(20);
begin
select to_char(sysdate,'DD-MON-YYYY') into dir from dual;
dbms_output.put_line('host mkdir D:\oracle\backups\hot\'||dir);
end;
/
spool off
@D:\oracle\backups\scripts\bkup_dest_create.sql
exit

##########################
###  02-hot_bkup.sql  ####
##########################


set head off
set feed off
set line 150
set serveroutput on
spool D:\oracle\backups\scripts\hot_bkup_copy.sql
declare
cursor ts is select distinct(tablespace_name) 
from dba_data_files;
cursor fn (ts_name in VARCHAR2) is select file_name 
from dba_data_files
where tablespace_name=ts_name order by 1;
dir varchar2(20);
begin
select to_char(sysdate,'DD-MON-YYYY') into dir from dual;
dbms_output.put_line(chr(0));
dbms_output.put_line('-----------------------');
dbms_output.put_line('-- Copying Datafiles --');
dbms_output.put_line('-----------------------');
dbms_output.put_line(chr(0));
for c in ts
loop
dbms_output.put_line('alter tablespace '||c.tablespace_name - 
||' begin backup;');
for d in fn (c.tablespace_name)
loop
dbms_output.put_line('host copy '||d.file_name|| - 
' D:\oracle\backups\hot\'||dir||'\.');
end loop;
dbms_output.put_line('alter tablespace '|| - 
c.tablespace_name||' end backup;');
dbms_output.put_line(chr(0));
end loop;
end;
/
spool off
@D:\oracle\backups\scripts\hot_bkup_copy.sql
exit

#############################
####  03-arch_switch.sql ####
#############################

###
### The LOG_ARCHIVE_FORMAT parameter
### plays a significant role in here
### the format of archive files that 
### is used here would be
### ${ORACLE_SID}_ARCH_*_*001, which
### has to be changed appropriately
### based on your environment
###


set head off
set feed off
set line 150
set serveroutput on
spool D:\oracle\backups\scripts\arch_switch_copy.sql
declare
prev_seq number;
i number;
j number;
k number;
dir varchar2(20);
begin
select to_char(sysdate,'DD-MON-YYYY') into dir from dual;
select sequence# into i from v$log where status='CURRENT';
prev_seq:=i-1;
execute immediate ('alter system switch logfile');
select sequence# into j from v$log where status='CURRENT';
execute immediate ('alter system switch logfile');
select sequence# into k from v$log where status='CURRENT';
execute immediate ('alter system switch logfile');
dbms_output.put_line(chr(0));
dbms_output.put_line('-----------------------');
dbms_output.put_line('-- Copying Logfiles ---');
dbms_output.put_line('-----------------------');
dbms_output.put_line(chr(0));
dbms_output.put_line('host copy - 
D:\oracle\oradata\arch\PROD_ARCH_*'||prev_seq||-
'_*001 D:\oracle\backups\hot\'||dir||'\.');
dbms_output.put_line('host copy -
D:\oracle\oradata\arch\PROD_ARCH_*'||i||-
'_*001 D:\oracle\backups\hot\'||dir||'\.');
dbms_output.put_line('host copy -
D:\oracle\oradata\arch\PROD_ARCH_*'||j||-
'_*001 D:\oracle\backups\hot\'||dir||'\.');
dbms_output.put_line('host copy -
D:\oracle\oradata\arch\PROD_ARCH_*'||k||-
'_*001 D:\oracle\backups\hot\'||dir||'\.');
end;
/
spool off
@D:\oracle\backups\scripts\arch_switch_copy.sql
exit

##########################
###  04-ctrl_file.sql  ###
##########################

set head off
set feed off
set line 150
set serveroutput on
spool D:\oracle\backups\scripts\ctrl_file_copy.sql
declare
dir varchar2(20);
 
begin
select to_char(sysdate,'DD-MON-YYYY') into 
dir from dual;
execute immediate 'alter session set tracefile_identifier='||''''||dir||'''';
execute immediate ('alter database backup controlfile to trace');
execute immediate 'alter database backup controlfile to'||''''||'D:\oracle\backups\hot\'||dir||'\controlfile_'||dir||'_bin.bkup'||'''';
dbms_output.put_line('host move D:\oracle\admin\PROD\udump\*'-
||dir||'*.trc D:\oracle\backups\hot\'||dir||'\.');
end;
/
spool off
@D:\oracle\backups\scripts\ctrl_file_copy.sql
exit

###############################
# # #   E X P   B K U P   # # #
###############################

####################################
###   export_backup_on_disk.bat  ###
####################################

set ORACLE_SID=PROD

set ORACLE_HOME=D:\oracle\10.2.0

%ORACLE_HOME%\bin\sqlplus -S "/as sysdba" @D:\oracle\backups\scripts\01-exp_dest.sql

%ORACLE_HOME%\bin\sqlplus -S "/as sysdba" @D:\oracle\backups\scripts\02-exp_bkup.sql

#########################
###  01-exp_dest.sql  ###
#########################

set head off
set feed off
set line 150
set serveroutput on
spool D:\oracle\backups\scripts\exp_dest_create.sql
declare
dir varchar2(20);
begin
select to_char(sysdate,'DD-MON-YYYY') into dir from dual;
dbms_output.put_line('host mkdir D:\oracle\backups\exp\'||dir);
end;
/
spool off
@D:\oracle\backups\scripts\exp_dest_create.sql
exit

#########################
###  02-exp_bkup.sql  ###
#########################

set head off
set feed off
set line 300
set serveroutput on
spool D:\oracle\backups\scripts\exp_bkup_full.sql
declare
dir varchar2(20);
begin
select to_char(sysdate,'DD-MON-YYYY') into dir from dual;
dbms_output.put_line('host exp system/manager file=D:\oracle\backups\exp\'||dir||'\PROD_full.dmp log=D:\oracle\backups\exp\'||dir||'\PROD_full.log full=y buffer=10485760 consistent=y compress=n');
end;
/
spool off
@D:\oracle\backups\scripts\exp_bkup_full.sql
exit

#######################################
# # #   C L E A N U P   J O B S   # # #
#######################################

###########################
### delete_hot_bkup.bat ###
###########################

###
### Keeps the last 5 days of hot backups  
### on disk while deleting anything older
### than that
###

forfiles /p "D:\oracle\backups\hot" /s /m *.* /d -05 /c "cmd /c del @path"

###########################
### delete_exp_bkup.bat ###
###########################

###
### keeps the last 5 days of export backups
### on disk while deleting anything older than
### that
###

forfiles /p "D:\oracle\backups\exp" /s /m *.* /d -05 /c "cmd /c del @path"