Automatic Startup and Shutdown (dbstart & dbstop)

Starting Oracle Instance up on System Startup

RHEL5: -
Creating the startup bash script: -
Open a text editor (as root) and put the content below in it, saving the le as /etc/init.d/dbora :

#!/bin/sh
# chkconfig: 345 99 10
# description: Oracle auto start-stop script.
#
# Set ORA_HOME to be equivalent to the $ORACLE_HOME
# from which you wish to execute dbstart and dbshut;
#
# Set ORA_OWNER to the user id of the owner of the
# Oracle database in ORA_HOME.
ORA_HOME=/u01/app/oracle/product/10.2.0/db_1
ORA_OWNER=oracle
if [ ! -f $ORA_HOME/bin/dbstart ]
then
echo "Oracle startup: cannot start"
exit
fi
case "$1" in
'start')
# Start the Oracle databases:
# The following command assumes that the oracle login
# will not prompt the user for any values
su - $ORA_OWNER -c "$ORA_HOME/bin/lsnrctl start"
su - $ORA_OWNER -c $ORA_HOME/bin/dbstart
su - $ORA_OWNER -c "$ORA_HOME/bin/emctl start dbconsole"
su - $ORA_OWNER -c "$ORA_HOME/bin/isqlplusctl start"
;;
'stop')
# Stop the Oracle databases:
# The following command assumes that the oracle login
# will not prompt the user for any values
su - $ORA_OWNER -c "$ORA_HOME/bin/isqlplusctl stop"
su - $ORA_OWNER -c "$ORA_HOME/bin/emctl stop dbconsole"
su - $ORA_OWNER -c $ORA_HOME/bin/dbshut
su - $ORA_OWNER -c "$ORA_HOME/bin/lsnrctl stop"
;;
esac

Then, open a root terminal and give the le executable rights:

chmod 755 /etc/init.d/dbora

Then link this script to the les inside /etc/rc3.d:

ln -s /etc/init.d/dbora /etc/rc3.d/S99dbora
ln -s /etc/init.d/dbora /etc/rc4.d/S99dbora
ln -s /etc/init.d/dbora /etc/rc5.d/S99dbora
ln -s /etc/init.d/dbora /etc/rc0.d/K10dbora
ln -s /etc/init.d/dbora /etc/rc6.d/K10dbora

Fixing the dbstart script

The dbstart script, which is called inside the dbora script, has some errors. Open the le /u01/app/oracle/product/10.2.0/db_1/bin/dbstart as oracle user and change: -

ORACLE_HOME_LISTNER=/ade/vikrkuma_new/oracle
to
ORACLE_HOME_LISTNER=$ORACLE_HOME

Otherwise, your listener might not get started.

Flagging the orcl instance to be started: -

We open the /etc/oratab le and change the last letter from N to Y:

orcl:/u01/app/oracle/product/10.2.0/db_1:N
to
orcl:/u01/app/oracle/product/10.2.0/db_1:Y
___________________________________________________________________________________

Normal: -

1. Create oradb file under /etc/init.d

#vi /etc/init.d/oradb (add below contents)

#!/bin/sh
# chkconfig: 345 99 10
# description: Oracle auto start-stop script.
#
# Set ORA_HOME to be equivalent to the $ORACLE_HOME
# from which you wish to execute dbstart and dbshut;
#
# Set ORA_OWNER to the user id of the owner of the
# Oracle database in ORA_HOME.

ORA_HOME=/oracle/u01/app/oracle/product/10.2.0/db_1
ORA_OWNER=oracle

if [ ! -f $ORA_HOME/bin/dbstart ]
then
echo "Oracle startup: cannot start"
exit
fi

case "$1" in
'start')
# Start the Oracle databases:
# The following command assumes that the oracle login
# will not prompt the user for any values
su - $ORA_OWNER -c "$ORA_HOME/bin/lsnrctl start"
su - $ORA_OWNER -c $ORA_HOME/bin/dbstart
su - $ORA_OWNER -c "$ORA_HOME/bin/emctl start dbconsole"
su - $ORA_OWNER -c "$ORA_HOME/bin/isqlplusctl start"
;;
'stop')
# Stop the Oracle databases:
# The following command assumes that the oracle login
# will not prompt the user for any values
su - $ORA_OWNER -c "$ORA_HOME/bin/isqlplusctl stop"
su - $ORA_OWNER -c "$ORA_HOME/bin/emctl stop dbconsole"
su - $ORA_OWNER -c $ORA_HOME/bin/dbshut
su - $ORA_OWNER -c "$ORA_HOME/bin/lsnrctl stop"
;;
esac

2. Change permissions of the file: -
#chmod 750 /etc/init.d/oradb

3. Associate the oradb service with the appropriate run levels and set it to auto-start using the following command.
#chkconfig --level 345 oradb on

.bash_profile

# .bash_profile
# Get the aliases and functions
if [ -f ~/.bashrc ]; then
. ~/.bashrc
fi
# User specific environment and startup programs
PATH=$PATH:$HOME/bin
export PATH
# Oracle Settings
TMP=/tmp; export TMP
TMPDIR=$TMP; export TMPDIR
ORACLE_BASE=/u01/app/oracle; export ORACLE_BASE
ORACLE_HOME=$ORACLE_BASE/product/10.2.0/db_1; export ORACLE_HOME
ORACLE_SID=orcl; export ORACLE_SID
ORACLE_TERM=xterm; export ORACLE_TERM
PATH=$ORACLE_HOME/bin:$PATH; export PATH
LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib; export LD_LIBRARY_PATH

if [ $USER = "oracle" ]; then
if [ $SHELL = "/bin/ksh" ]; then
ulimit -p 16384
ulimit -n 65536
else
ulimit -u 16384 -n 65536
fi
fi
alias sqlplus='rlwrap sqlplus'
alias rman='rlwrap rman'
export EPC_DISABLED=TRUE
export PATH=/bin:/usr/gnu/bin:/opt/SUNWspro/bin:/usr/bin:${ORACLE_BASE}/local:/usr/sbin:/usr/ccs/bin:/usr/ucb:/usr/openwin/bin:/usr/local/bin:${ORACLE_HOME}/
bin:/opt/bin:/etc:.
export ENV=${HOME}/.kshrc
export MANPATH=/opt/SUNWspro/man:usr/man:/opt/sfw/man
clear
echo "The SIDs on this machine are:"
cat /etc/oratab | awk -F: '{print $1}' | grep -v "#"
. oraenv

export in Windows (.bat file) and Linux (.sh file)

How to create a batch file in Windows to take full DB backup:-

exp utility: -

for /f "tokens=1,2" %%u in ('date /t') do set d=%%v
for /f "tokens=1" %%u in ('time /t') do set t=%%u
if "%t:~1,1%"==":" set t=0%t%
set timestr=%d:~6,4%%d:~0,2%%d:~3,2%_%t:~0,2%%t:~3,2%
set copydmp=filename_%timestr%_%computername%.dmp
set copylog=filename_%timestr%_%computername%.log
set ORACLE_SID=(sid_name)
exp username/password file=E:\%copydmp% log=E:\%copylog% full=y

expdp utility: -

for /f "tokens=1,2" %%u in ('date /t') do set d=%%v
for /f "tokens=1" %%u in ('time /t') do set t=%%u
if "%t:~1,1%"==":" set t=0%t%
set timestr=%d:~6,4%%d:~0,2%%d:~3,2%_%t:~0,2%%t:~3,2%
set copydmp=filename_%timestr%_%computername%.dmp
set copylog=filename_%timestr%_%computername%.log
set ORACLE_SID=(sid_name)
expdp username/password directory=data_pump_dir dumpfile=%copydmp% logfile=%copylog% full=y

How to write a shell to take full DB backup:-

exp utility: -

export ORACLE_SID=sid_name
export ORACLE_HOME=/u01/app/oracle/product/10.2.0/db_1
echo $ORACLE_HOME
echo $ORACLE_SID
echo 'EXPORTING FULL DB'
exp system/password@sid_name file=full_expdp_dbname_`date +%d%m%H%M`.dmp log=full_expdp_dbname_`date +%d%m%H%M`.log full=y

expdp utility: -

export ORACLE_SID=sid_name
export ORACLE_HOME=/u01/app/oracle/product/10.2.0/db_1
echo $ORACLE_HOME
echo $ORACLE_SID
echo 'EXPORTING FULL DB'
expdp system/password@sid_name directory=data_pump_dir dumpfile=full_expdp_dbname_`date +%d%m%H%M`.dmp logfile=full_expdp_dbname_`date +%d%m%H%M`.log full=y

Shrinking the Tablespace

/*Procedure : Shrink_tbsp
Owner : SYS
Author : Naresh Awasthi
Note : Set serveroutput on before running this procedure.*/

create or replace procedure SHRINK_TBSP (p_tablespace_name in varchar2,
p_keep_size_pct number) AUTHID CURRENT_USER is
FILE_SIZE number;
FREE_BYTES number;
FILEID number;

LAST_BLOCKID_USED number;
FREE_BLOCKID number;
FILE_NAME varchar2(2000);
STMT varchar2(2000);
SHRINK_TO number;

cursor FREE_SPACE_CUROR is
select file_id FILEID,max(block_id) FREE_BLOCKID from dba_free_space
where tablespace_name=p_tablespace_name group by file_id,bytes;


BEGIN
FOR fsc_row in FREE_SPACE_CUROR LOOP
select bytes into FREE_BYTES from dba_Free_space where file_id=fsc_row.FILEID and block_id=fsc_row.FREE_BLOCKID;
select bytes,file_name into FILE_SIZE,FILE_NAME from dba_data_files where file_id=fsc_row.FILEID;
select nvl(max(block_id),0) into LAST_BLOCKID_USED from dba_extents where file_id=fsc_row.FILEID;
if LAST_BLOCKID_USED < fsc_row.FREE_BLOCKID THEN
select round((FILE_SIZE - round(FREE_BYTES * (100 - p_keep_size_pct) / 100))/1024/1024) into SHRINK_TO from dual;
STMT := 'alter database datafile '||''''||FILE_NAME||''''||' resize '||SHRINK_TO||'M';
EXECUTE IMMEDIATE STMT;

dbms_output.put_line ('Shrunk '||FILE_NAME||' from
'||FILE_SIZE||' to '||SHRINK_TO||' MB.');
end if;
END LOOP;

EXCEPTION
WHEN NO_DATA_FOUND THEN
dbms_output.put_line('Done.');
WHEN OTHERS THEN
dbms_output.put_line('Unhandled Error : '||sqlerrm);
END;
/
Ex:- exec SHRINK_TBSP('tablespace_name',shrink_size);

Dropping all objects in a Schema

CREATE OR REPLACE PROCEDURE DROP_OBJECTS(P_Owners varchar2) IS
cursor obj_crs is
select owner, object_name, object_type
from all_objects
where owner = upper(P_Owners);
obj_REC obj_CRS%ROWTYPE;
l_grants_objects varchar2(500);
BEGIN
dbms_output.put_line('Begining to drop all objects');

Case upper(P_Owners)
when 'SYS' then dbms_output.put_line('Can drop objects for:'||upper(P_Owners));
when 'SYSTEM' then dbms_output.put_line('Can drop objects for:'||upper(P_Owners));
when 'WMSYS' then dbms_output.put_line('Can drop objects for:'||upper(P_Owners));
when 'TSMSYS' then dbms_output.put_line('Can drop objects for:'||upper(P_Owners));
when 'OUTLN' then dbms_output.put_line('Can drop objects for:'||upper(P_Owners));
when 'DBSNMP' then dbms_output.put_line('Can drop objects for:'||upper(P_Owners));
ELSE
Begin
FOR obj_REC IN obj_CRS LOOP
begin
dbms_output.put_line('OUT-1 -> ' || obj_REC.object_type);
CASE obj_REC.object_type
WHEN 'TABLE' THEN
l_grants_objects := 'drop table '||obj_REC.owner||'.'||obj_REC.object_name||' cascade constraints PURGE';
EXECUTE IMMEDIATE l_grants_objects;
-- WHEN 'MATERIALIZED VIEW' THEN
-- dbms_output.put_line('OUT-2 -> INSIDE MATERIALIZED VIEW');
-- l_grants_objects := 'drop MATERIALIZED VIEW ' || obj_REC.owner||'.'||obj_REC.object_name ;
-- dbms_output.put_line(' OUT-2 -> ' || l_grants_objects);
-- EXECUTE IMMEDIATE l_grants_objects;
WHEN 'INDEX' THEN l_grants_objects := '';
WHEN 'TYPE' THEN
l_grants_objects := 'drop type '||obj_REC.owner||'.'||obj_REC.object_name||' force';
EXECUTE IMMEDIATE l_grants_objects;
ELSE
dbms_output.put_line('OUT-2222** -> INSIDE MATERIALIZED VIEW');
l_grants_objects := 'drop '||obj_REC.object_type||' '||obj_REC.owner||'.'||obj_REC.object_name;
EXECUTE IMMEDIATE l_grants_objects;
END CASE;
dbms_output.put_line(l_grants_objects);
Exception
when others then
l_grants_objects := '';
end;
end loop;
end;
end case;
dbms_output.put_line('All objects are dropped');
End;
/

Ex:- exec DROP_OBJECTS('user_name');