Oracle database deployment without system and schema passwords

Jephe Wu -  http://linuxtechres.blogspot.com

Objective:  deploying Oracle database scripts without OS user 'oracle' account login and Oracle system users and individual schema passwords for accountability and audit purpose
Environment: RHEL 5, Oracle 11g 64bit


Steps:
1. configure sudosh for Oracle database server so that you can login as your own id then run 'sudo -u oracle sudosh' to switch to oracle user.

Refer to article http://linuxtechres.blogspot.com/2008/12/use-sudosh-to-enable-server-auditing.html


2. login as sysdba then alter session to change current schema to individual client schema
$ sudo -u oracle sudosh


then use the following shell script to deploy script.sql to individual client schemas without password:

$ cat deploy.sh
#!/bin/sh
FILENAME=deploy
for i in jephe1 jephe2 jephe3
do
echo "deploying $i"
cat >${FILENAME}_$i.sql <<END
spool ${FILENAME}_$i.log
set define off sqlblanklines on
@script.sql
spool off;
exit;
END


sqlplus / as sysdba <<END
alter session set current_schema=$i;
@${FILENAME}_$i.sql
END

sleep 1
done

3. After deployment, recompile all client schemas
$ cat compileallschemas.sh
#!/bin/sh
cd /home/oracle/
cat > comileallschemas.sql << END
exec dbms_utility.compile_schema('JEPHE1');
exec dbms_utility.compile_schema('JEPHE2');
exec dbms_utility.compile_schema('JEPHE3');
END


sqlplus / as sysdba @/home/oracle/compileallschemas.sql

4. check all invalid objects 
$ cat numberofinvalidobjects.sql
select count(*)  jephe1, object_type from dba_objects where owner='JEPHE1' and status <> 'VALID' and object_name not like 'EXCLUDED_OBJ%' group by object_type;
select count(*)  jephe2, object_type from dba_objects where owner='JEPHE2' and status <> 'VALID' and object_name not like 'EXCLUDED_OBJ%' group by object_type;
select count(*)  jephe3, object_type from dba_objects where owner='JEPHE3' and status <> 'VALID' and object_name not like 'EXCLUDED_OBJ%' group by object_type;
exit;


$ sqlplus / as sysdba @numberofinvalidobjects.sql
==========================================
$ cat invalidobjects.sql
column object_type format A10;
column object_name format A30;
select object_type "Invalid jephe1",object_name from dba_objects where owner='JEPHE1' and status <> 'VALID' and object_name not like 'EXCLUDED_OBJ%' order by object_type,object_name
select object_type "Invalid jephe1",object_name from dba_objects where owner='JEPHE1' and status <> 'VALID' and object_name not like 'EXCLUDED_OBJ%' order by object_type,object_name
select object_type "Invalid jephe1",object_name from dba_objects where owner='JEPHE1' and status <> 'VALID' and object_name not like 'EXCLUDED_OBJ%' order by object_type,object_name
exit;


$ sqlplus / a sysdba @invalidobjects.sql