Wednesday, November 17, 2010

GCC Customer Story on Oracle Technology Network



A GCC Oracle ADF Customer Story has been published on Oracle Technology Network.


It shows the GCC technology move to new Fusion Middleware technologies and the integration with existing Oracle Forms and Reports and the Infrastructure & Development roadmap of the IS Department.


You can also refer to the ~50 slides presentation covering the technical aspects of this transition.



Monitor LOBs space

If you use LOBs and delete records from the tables with a LOB attribute, you should monitor LOBs segments.
For example, ADF is using a table (PS_TXN) to serialize user session state to the database, a good monitoring of any growing space used by PS_TXN must be done.


Check Space used by LOB segments

select a.tablespace_name LOB_TSpace, b.tablespace_name TAB_TSPACE,
       b.owner TAB_OWNER,a.owner LOB_OWNER,
       table_name,column_name,a.segment_name LOG_SEGMENT,index_name,
       sum(bytes) LOB_SEGMENT_SIZE from all_lobs a , dba_segments b
where a.segment_name= b.segment_name
and a.owner in ('MY_USER')
group by a.tablespace_name,b.tablespace_name, b.owner,a.owner,
table_name,column_name,a.segment_name,index_name;


Free Up LOB space

ALTER TABLE mytable modify lob (image) (shrink space);

To Automatically delete and shrink LOBs space on Log file or ADF PS_TXN file, you can create a DBMS scheduled job 

begin
 dbms_scheduler.drop_job('MYJOB',false);
 dbms_scheduler.drop_schedule('MYJOBSCHED',false);
 dbms_scheduler.create_schedule('MYJOBSCHED',null,'freq=WEEKLY;byday=MON;byhour=18',null,'Weekly Schedule to purge ADF persistency segments');
 dbms_scheduler.create_job('MYJOBS','MYJOBSCHED','PLSQL_BLOCK',
                           'Begin delete from MY_USER.ps_txn where creation_date<sysdate-1; alter table MY_USER.ps_txn modify lob (content) (shrink space); end;',
                            0,'DEFAULT_JOB_CLASS',FALSE,TRUE,'Comment');
 dbms_scheduler.disable('MYJOBSCHED');
 dbms_scheduler.enable('MYJOBSCHED');
end;


You can check if Job ran successfully with

select * from dba_scheduler_job_run_details where job_name like '%MYJOB%';

2 minutes full off-line Database backup using Volume Snapshot

If you use a good Network Storage system, certified with Oracle and for simplicity mount Oracle home and datafiles to NFS volumes, you can make offline backups in less than 5 minutes...

You will :
1/. Shutdown the database
2/. Snapshot the NFS volume on the storage system (StorageName) where Oracle home is located.
    This can also be done if datafiles, archlogs, etc.. are a distinct volume on the Storage.
3/. Restart the database

The snapshot can be mirrored to a DR Storage System and/or backed up to Tape.

Database down,snap & up script (backuporacle.sh)

ORACLE_HOME=/u01/app/oracle/orahome/mydbase
LD_LIBRARY_PATH=$ORACLE_HOME/lib
PATH=$PATH:$ORACLE_HOME/bin:$ORACLE_HOME/OPatch
ORACLE_SID=mydbase
export ORACLE_HOME
export ORACLE_SID
export PATH
export LD_LIBRARY_PATH
echo Shutting down the database
echo ------------------------------------
/u01/app/oracle/orahome/mydbase/bin/lsnrctl stop<<EOF
EOF
/u01/app/oracle/orahome/mydbase/bin/sqlplus /nolog<<EOF
connect / as sysdba
shutdown immediate
EOF
echo Filer Snapshots - delete day-2 Oracle home volume snapshot (oraHprev),
echo rename day-1 to oraHprev and snap (oraHsnap).
echo ------------------------------------
/usr/bin/rsh StorageName -l rootaccount:rootpassword snap delete orahome oraHprev
/usr/bin/rsh StorageName -l rootaccount:rootpassword snap rename orahome oraHsnap oraHprev
/usr/bin/rsh StorageName -l rootaccount:rootpassword snap create orahome oraHsnap
echo Removing archived logs in both paths
echo ------------------------------------
/bin/rm /u01/app/oracle/Archlogs/arch*.arc
/bin/rm /u01/app/oracle/exports/Archlogs/arch*.arc
echo Restarting the database
echo ------------------------------------
/u01/app/oracle/orahome/mydbase/bin/lsnrctl start<<EOF
EOF
/u01/app/oracle/orahome/mydbase/bin/sqlplus /nolog<<EOF
connect / as sysdba
startup
EOF


Linux cron file to execute the backup script for example at 23h30, Monday to Friday (1-5) :
(2 single lines, no line break)

29 23 * * 1-5 /bin/echo Shutdown the database, rename snapshots, create snapshots, startup the database >> /u01/app/oracle/BUPScripts/backup.log

30 23 * * 1-5 /u01/app/oracle/BUPScripts/backuporacle.sh 1>> /u01/app/oracle/BUPScripts/backup.log 2>&1

Migrate and Integrate Forms, Reports and ADF

Slides from General Construction Co.Ltd Case Study presented at SAOUG 2010 :-
Move to Oracle Application Development Framework (ADF) and integration with Oracle Forms and Reports covering Infrastructure, Security in ADF and Weblogic and a few ADF application screenshots.

This approach can be used for anyone developping with Forms and moving or with the intention to move to ADF.