Place your own ad here!  This space for rent.
Contact
srea1957@maristream.com today.  Just $200 per month.

 


Banner Oracle DBA Consulting
(Just $150/hour)

 

Steve Rea's Oracle and Ellucian
Banner Tips, Tricks, and Scripts

Now at www.OracleTips.Guru
Blog at http://oracletipssite.wordpress.com


CAKid.org for Foster Parents


Fireworks Picture

Visits since 9/1/98:
50,000: 3/16/00
100,000: 11/14/00
150,000: 3/26/01
200,000: 7/1/01
250,000: 10/26/01
300,000: 4/12/02
350,000: 10/7/02
400,000: 5/12/03
450,000: 1/9/04

 
Featuring:

Health Check for Banner, Oracle, and Linux

Tuning Oracle and Linux

Two-Minute Ultra-Small Instant Database Clones

WebLogic and Fusion Middleware Installation and Configuration

Bulletproofing, Backups, and Disaster Recovery Scenarios
 
Implementing Data Guard
 
RMAN Backups and Cloning
 
(This Page was Last Updated on 07/26/16)


Fireworks Picture

Over One Million Visits!

Visits since 9/1/98:
500,000: 7/30/04
600,000: 1/4/06
700,000: 10/10/07
and on and on!

to support this web site and
for Maristream, LLC, payments.

Welcome to my web collection of Oracle and Banner sticky notes (now, I can throw the paper ones away!).  Here (see contents) are some tips and tricks that I've learned while working with Oracle as a DBA for over 19 years, downloadable scripts that I and others have developed for maintaining Oracle databases and gleaning information about the Ellucian Banner data in those databases, and other Oracle, Banner, and UNIX information you might find useful.  Some of this is Banner specific, but even those can give you ideas for similar scripts to be used with other third-party Oracle applications.  Check back often for additions and updates!

Also see Bruce Knox's web site, www.betwinx.com, for Banner Reporting Solutions
and SkillBuilders web site, www.skillbuilders.com for other free DBA and APEX tutorials along with Oracle training.

Help keep this web site up with a donation to www.gofundme.com/oracletips

Night Heron Picture

Banner Oracle DBA Consulting Services

You Want What???!

(Night Heron courtesy of
The Nature Conservancy)
 

Contents:

SQL and PL/SQL Tips and Tricks:

Oracle and Banner Presentations (PowerPoint files) (added 4/1/16):

UNIX Tips and Tricks for Oracle:

Oracle Procedures and Triggers:

These tips, tricks, and scripts were developed under various versions of Oracle and Banner, and mainly under Linux and AIX Unix, and you may need to modify them for your particular version or operating system.

Two-Minute Ultra-Small Instant Database Clones      [Back]

Oracle's new DNFS (Direct Network File System) cloning was introduced, but undocumented, in Oracle 11gR2, and is documented in Oracle 12c.  Using their NFS OFM (Oracle File Manager)  library in place of their current OFM, NAS (Network Attached Servers) drives can be attached to your database servers through NFS servers to provide near unlimited disk space to y our database servers.  Oracle's DNFS implementation also provides an interesting side effect, in that you can have it loop back to itself, resulting in just having to have one server instead of three or more servers.  And, it also provides a way to service multiple clones derived from a base image database created through RMAN.  The base image database contains all of the database data up to the time that the RMAN image backup was created.  Using a script in Oracle 12c (clonedb.pl, which can also be found in a Metalink note that can be used with Oracle 11gR2), a second set of mapped datafiles can be created to hold just the changes (DDL, DML) to that base copy.  So, using this process, you can create numerous clones occupying a very small space apiece (relative to current cloning techniques) in a very short "cloning" time (around 2 minutes apiece).  My Powerpoint presentation from the CoHEsion (SETA) Nashville 2013 conference shows how to do this.  Is this cool, or what?!!!

WebLogic and Fusion Middleware Installation      [Back]

There has been a lot of nervousness about upgrading to WebLogic and Fusion Middleware.  With dozens of FAQ's and manuals, no wonder.  It took me about a month and a half to pour through all of those documents and to install WebLogic and Fusion Middleware on a clean Linux server.  I've written all of my notes and steps down, and have posted them here, so, hopefully, what took me a month and a half to do, you can do in under a week.  And, with the time pressure we are under from Ellucian and Oracle to get this done, this is being posted in the nick of time.  Happy installing!

Restarting WebLogic Processes      [Back]

As a companion to the WebLogic and Fusion Middleware Installation above, I'm also including my instructions with the steps to stop and restart the PROD database, WebLogic, NodeManager, and opmn on a Linux server.  It's basically just a step-by-step copy-and-paste operation, which helps me to not forget any of the steps.

Bulletproofing, Backups, and Disaster Recovery Scenarios      [Back]

This page shows you step-by-step how to bulletproof your database as much as possible against data loss, gives you the what's and how's of database backups and restores, and takes you through disaster recovery scenarios if you encounter those heart-stopping Oracle errors on database startup, such as datafile corruption or loss, disk drive loss, and other nasties.  You can also download the slides for the presentation in Microsoft PowerPoint format, including the viewer (totalling around 3,483 K in size).  Note that this is a work in progress that I will be adding to and testing as time and opportunities permit beyond what was given at the Ellucian conference.

Implementing Data Guard      [Back]

Do you want additional protection for your database, besides just knowing what to do if it crashes?  Oracle's Data Guard is your answer, and it's free!  This page shows you step-by-step how to implement and configure a standby database locally or on a remote site so that it's ready to take over your processing at a moment's notice with no data loss.  It includes my SETA Central 2005 PowerPoint presentation entitled "Crashes Happen - Downtime Won't - With Data Guard".  It also includes a step-by-step description of the live payroll test (yikes!) that we did on the standby in "Switching Primary and Standby for Disaster Planning Tests".  But, wait!  If you want to see how to do this using RMAN, check out the topic below.

RMAN Backups and Cloning      [Back]

What is this thing called RMAN, and how do I use it?  How about backing up your database quickly, either a full backup or an incremental backup, without taking your database down?  How about cloning your database, either on the same server or across servers?  How about creating a Data Guard standby database for your primary database on another server?  How about restoring your database quickly if it becomes corrupted or if data is accidentally deleted?  And. if you don't want to bother with the intricacies of RMAN, I even have scripts to back up one or all of your databases on a server, either fully or incrementally, and to clone your database from an RMAN backup.  Select this link to see my RMAN Backups and Cloning page, where I take you step-by-step through these tasks, making you an RMAN expert in a day!

Sample RMAN Backup Script      [Back]      (added 5/6/16)

I modified PROD8's RMAN backup script to force it to put the backups in the directory specified by the db_recovery_file_dest parameter (RMAN was ignoring that and putting them into the default $ORACLE_HOME/dbs directory for some reason, which was filling up /u01) (script is /u03/backups/backup_scripts/RMAN_hot_backup_PROD8.shl):

    # Put a divider and the date and time into the RMAN log file
    NLS_DATE_FORMAT="DD-MON-YY HH24:MI:SS"; export NLS_DATE_FORMAT
    echo "================================================================================" >>/u03/backups/backup_logs/RMAN_hot_backup_PROD8.log
    date >>/u03/backups/backup_logs/RMAN_hot_backup_PROD8.log
    # Hot database backup
    export ORACLE_HOME=/u01/app/oracle/product/11.2.0/db_1
    export PATH=$ORACLE_HOME/bin:$PATH
    export ORACLE_SID=PROD8
    #
    # RMAN isn't putting the backup files into the db_recovery_file_dest directory for some reason, so,
    # we will have to force the backup files to go there
    #
    sqlplus -s / as sysdba <<EOF | grep '/' | sed 's~\(.*\)~echo \1/${ORACLE_SID}/backupset/`date +"%Y_%m_%d"`~' | sh >backupset
        set heading off
        select value from v\$parameter where name = 'db_recovery_file_dest';
        exit
    EOF
    mkdir `cat backupset` 2>/dev/null
    export backupset=`cat backupset`
    #
    rman target / << EOF | tee -a /u03/backups/backup_logs/RMAN_hot_backup_PROD8.log
        run { CONFIGURE BACKUP OPTIMIZATION ON;
            CONFIGURE CONTROLFILE AUTOBACKUP ON;
            CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '${backupset}/ctl_%d_%F';
            CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT '${backupset}/bkup_%d_%t_%p' MAXPIECESIZE 2 G;
            CONFIGURE SNAPSHOT CONTROLFILE NAME TO '${backupset}/sncf_PROD8.ora';
            backup as compressed backupset database include current controlfile plus archivelog;
            restore database validate;
            DELETE noprompt ARCHIVELOG ALL BACKED UP 1 TIMES TO DISK COMPLETED BEFORE 'SYSDATE-3';
            CONFIGURE RETENTION POLICY TO REDUNDANCY 3;
            #LIST BACKUP SUMMARY;
            #LIST BACKUP BY FILE;
            #LIST COPY OF DATABASE ARCHIVELOG ALL;
            crosscheck backup;
            crosscheck archivelog all;
            delete noprompt obsolete;
            delete noprompt expired backup; }
        exit;
    EOF
    #END

Flashback Database      [Back]      (added 5/9/16)

In case something goes really wrong, you can issue a Flashback Database (if implemented; see below) to take your entire database back to a particular point in time or to a particular SCN (system change number).  With our current setup, you can look at the control file backup name to find the DBID (database ID), such as, for rman_PROD_c-748470787-20110413-00.ctl, the DBID is 748470787. It is also in the rman log file, which shows for example: connected to target database: PROD (DBID=748470787).

    Example 1: Flashback a Database using RMAN (to midnight here):

        RMAN> FLASHBACK DATABASE TO TIME = TO_DATE('05/09/16 12:00:00','MM/DD/YY HH:MI:SS');

    Example 2: Flashback a database using SQL command.  The database must be in mount state to issue either of these commands (5/24 means 5 hours):

        SQL> FLASHBACK DATABASE TO TIMESTAMP (SYSDATE - 5/24);
        SQL> FLASHBACK DATABASE TO SCN 76239;

    You must issue the following command afterwards (RMAN or SQL):

        SQL> ALTER DATABASE RESETLOGS;

Implementing Flashback Database      [Back]      (added 5/12/16)

Implement flashback database for PROD (all archivelogs and flashback logs go to flash recovery area):

    sudo su (as root)
    mkdir /u01/flash_recovery_area
    chown oracle.oinstall /u01/flash_recovery_area
    su - oracle
    sqlplus "/ as sysdba"
        alter system set db_recovery_file_dest_size=100G; -- about 15 days of archivelogs and flashback log files
        alter system set db_recovery_file_dest='/u01/flash_recovery_area';
        alter system set db_flashback_retention_target=1440; -- 1440 minutes is 1 day for flashback database
        alter system set undo_retention=14400; -- 14400 seconds is 4 hours for the other flashback functions
        shutdown immediate
        exit
    sqlplus "/ as sysdba"
        startup mount exclusive
        alter database flashback on;
        alter database open;
        shutdown immediate
        startup
        exit

Changing from Flash Recovery Area to two Archivelog Directories      [Back]      (added 5/12/16)

Revert back from flash recovery on PROD (no flashback database) and send the archives to two directories - one on prodora and one on banner-dev (mapped /storage to banner-dev):

    as root:
        mkdir -p /storage/prodora1/oracle
        chmod 775 /storage/prodora1/oracle
        chown oracle:oinstall /storage/prodora1/oracle
    su - oracle
        mkdir /u01/app/oracle/oradata/PROD/archivelogs
        mkdir -p /storage/prodora1/oracle/oradata/PROD/archivelogs
        cd /opt/app/oracle/product/11.1.0/db_1/dbs
        ls -ltr *spfile*
            shows:
                -rw-r----- 1 oracle oinstall 3584 Mar 15 2015 spfilePROD.bak
                -rw-r----- 1 oracle oinstall 5632 Apr 30 08:29 spfilePROD.ora
        ls -ltr initPROD*.ora*
            shows:
                -rw-r--r-- 1 oracle oinstall 1629 Apr 30 2015 initPROD.ora.save
                -rw-r--r-- 1 oracle oinstall 1965 May 10 11:16 initPROD.ora
        mv initPROD.ora initPROD.ora.20160510
        sqlplus / as sysdba
            create pfile from spfile;
            exit
        cp -p initPROD.ora initPROD.ora.save
        vi initPROD.ora
            change:
                *.db_flashback_retention_target=1440
                *.db_recovery_file_dest='/u01/flash_recovery_area'
                *.db_recovery_file_dest_size=134217728000
            to:
                *.db_flashback_retention_target=0
                *.db_recovery_file_dest=''
                *.db_recovery_file_dest_size=0
                *.log_archive_dest_1='LOCATION=/u01/app/oracle/oradata/PROD/archivelogs MANDATORY'
                *.log_archive_dest_2='LOCATION=/storage/prodora1/oracle/oradata/PROD/archivelogs OPTIONAL'
                *.log_archive_format='PROD_%d_%t_%s_%r.arc'
            :wq
        sqlplus / as sysdba
            create spfile from pfile;
            shutdown immediate
            startup
            exit

Restoring a Database from RMAN Backup      [Back]      (added 5/13/16)

If you don't have Flashback Database enabled, you can restore your database up to a current point in time from the latest RMAN backup.  Here, I will restore the database up till 9:30 AM today (3/11/16):

    login oracle
        PROD
    export NLS_DATE_FORMAT='MM/DD/YY:HH24:MI:SS'
    cd $ORACLE_HOME/dbs
    ls -ltr PROD*ctl | tail -1
        -rw-r----- 1 oracle dba 2035712 Mar 10 23:36 PROD_rbkou8ro_1_1_ctl
    ls -ltr PROD*spf | tail -1
        -rw-r----- 1 oracle dba 24576 Mar 10 23:36 PROD_rakou8rm_1_1_spf
    rman target /
        startup force mount
        sql 'alter system enable restricted session';
        drop database;
            YES
        exit
    rman target /
        startup force nomount
        restore controlfile from 'PROD_rbkou8ro_1_1_ctl';
        restore spfile from 'PROD_rakou8rm_1_1_spf';
        alter database mount;
        host 'ls -ltr /orcl/oradata/PROD/archivelogs';
            shows the following archivelog was created before 9:30:
                -rw-r----- 1 oracle dba 2096128 Mar 11 09:21 arch_PROD_0001_0614109426_0000060193.arc
                    this is sequence number 60193.
        restore database until sequence 60193;
        exit
    sqlplus "/ as sysdba"
        startup force mount
        recover automatic database until time '2016-03-11:09:30:00' using backup controlfile;
        alter database open resetlogs;
        exit

Another Restore Database from RMAN      [Back]      (added 5/15/16)

Restored PREP from last Thursday night's backup (6/19). The server crash on Wednesday morning corrupted the /ndxs files.

    login root
    cd /pgms/oracle/product/v10203/dbs
    Mount the backup tape contining the RMAN backup files to restore (from full backup done on June 19).
    cpio -itvC64 </dev/rmt0 | grep 'Jun 19 .*PREP.*_1_1_' |
    sed 's/.*\/pgms/\/pgms/' >xyz
    cpio -imvC64 `cat xyz | tr '\n' ' '` </dev/rmt0
    login oracle
        PREP
    sqlplus "/ as sysdba"
        shutdown abort
        exit
    cp -p /orcl/oradata/${ORACLE_SID}/archivelogs/remove_old_logs.shl /home/oracle/remove_old_logs.${ORACLE_SID}.shl
    cp -p /orcl/oradata/${ORACLE_SID}/archivelogs/fillup.shl /home/oracle/fillup.${ORACLE_SID}.shl
    rm -r /data/oradata/PREP
    rm -r /ndxs/oradata/PREP
    rm -r /orcl/oradata/PREP
    rm -r /pgms/oradata/PREP
    mkdir -p /data/oradata/PREP
    mkdir -p /ndxs/oradata/PREP
    mkdir -p /orcl/oradata/PREP/archivelogs
    mkdir -p /pgms/oradata/PREP/audit
    mkdir -p /pgms/oradata/PREP/bdump
    mkdir -p /pgms/oradata/PREP/cdump
    mkdir -p /pgms/oradata/PREP/udump
    cp -p /home/oracle/remove_old_logs.${ORACLE_SID}.shl /orcl/oradata/${ORACLE_SID}/archivelogs/remove_old_logs.shl
    cp -p /home/oracle/fillup.${ORACLE_SID}.shl /orcl/oradata/${ORACLE_SID}/archivelogs/fillup.shl
    cd $ORACLE_HOME/dbs
    ls -ltr | grep PREP | egrep '(Jun 18|Jun 19)'
        shows:
            -rw-r----- 1 oracle dba 20955136 Jun 18 00:33 PREP_2kkhrj61_1_1_dbf
            -rw-r----- 1 oracle dba 1605632 Jun 18 00:33 PREP_2lkhrjvh_1_1_dbf
            -rw-r----- 1 oracle dba 585728 Jun 18 00:33 PREP_2jkhrj60_1_1_dbf
            -rw-r----- 1 oracle dba 98304 Jun 18 00:33 PREP_2mkhrjvk_1_1_dbf
            -rw-r----- 1 oracle dba 67484672 Jun 18 00:35 PREP_2okhrjvt_1_1_arc
            -rw-r----- 1 oracle dba 68599296 Jun 18 00:35 PREP_2nkhrjvt_1_1_arc
            -rw-r----- 1 oracle dba 98304 Jun 18 00:35 PREP_2pkhrk2b_1_1_spf
            -rw-r----- 1 oracle dba 1605632 Jun 18 00:35 PREP_2qkhrk2c_1_1_ctl
            -rw-r----- 1 oracle dba 900452352 Jun 19 00:38 PREP_2rkhu7hp_1_1_dbf
            -rw-r----- 1 oracle dba 1605632 Jun 19 00:38 PREP_2tkhu8k1_1_1_dbf
            -rw-r----- 1 oracle dba 98304 Jun 19 00:38 PREP_2ukhu8k5_1_1_dbf
            -rw-r----- 1 oracle dba 994357248 Jun 19 00:38 PREP_2skhu7hp_1_1_dbf
            -rw-r----- 1 oracle dba 80832512 Jun 19 00:40 PREP_30khu8ku_1_1_arc
            -rw-r----- 1 oracle dba 80167936 Jun 19 00:40 PREP_2vkhu8ku_1_1_arc
            -rw-r----- 1 oracle dba 77558784 Jun 19 00:41 PREP_31khu8nv_1_1_arc
            -rw-r----- 1 oracle dba 98304 Jun 19 00:41 PREP_32khu8pe_1_1_spf
            -rw-r----- 1 oracle dba 1605632 Jun 19 00:41 PREP_33khu8pg_1_1_ctl
    rman target /
        startup force nomount
        restore controlfile from 'PREP_33khu8pg_1_1_ctl';
        startup force mount
        spool log to listbackupset.lst
        list backupset;
        spool log off
        host "grep ' *[0-9][0-9]* *19-JUN-09$' listbackupset.lst";
            shows:
                1 3898 400381508 18-JUN-09 400382628 19-JUN-09
                1 3899 400382628 19-JUN-09 400384018 19-JUN-09
                1 3900 400384018 19-JUN-09 400384253 19-JUN-09
                1 3901 400384253 19-JUN-09 400384260 19-JUN-09
        restore archivelog from scn=400381508 until scn=400384260;
        restore database until time "to_date('06/19/09:00:41:21','MM/DD/YY:HH24:MI:SS')";
        The date and time is when the control file backup finished on June 19 (shown in "Server Nightly Backup e-mail 6/19 1:06 AM).
        Restored:
            2rkhu7hp tag=PREP_090619002000FRIFULL_DBF
            2skhu7hp tag=PREP_090619002000FRIFULL_DBF
        exit
    sqlplus "/ as sysdba"
        startup force mount
        recover automatic database until cancel using backup controlfile;
            CANCEL
        startup force mount
        alter database open resetlogs;
        exit

Clearing out Audit Files and Records      [Back]      (added 5/13/16)

    Remove audit files (just shows shutdowns and startups):
        cd /pgms/oradata/PROD/audit
        find . -name '*.aud' -exec rm {} \;
    Clear out records in dba_audit_trail (audit connects) older than 30 days:
        sqlplus / as sysdba
            delete from sys.aud$ where ntimestamp# < to_timestamp(sysdate - 30);
            exit;

Free Up Space in GUBOUTP/GUROUTP Tablespace      [Back]      (added 5/14/16)

    sqlplus / as sysdba
        !exp general/rs396240 file=outp_PREP.dmp tables=guroutp,guboutp
        Ran fk_enable_disable.sql to disable guboutp's foreign key constraint references:
        @/home/dba_scripts/fk_enable_disable.sql
            general
            guroutp,guboutp
            disable
        truncate table general.guroutp;
        truncate table general.guboutp;
        Ran fk_enable_disable.sql to enable guboutp's foreign key constraint references:
        @/home/dba_scripts/fk_enable_disable.sql
            general
            guroutp,guboutp
            enable
        exit

Show When a User Logged Onto Banner      [Back]      (added 5/14/16)

See when a particular user logged onto Banner (Oracle) during a certain date (enter user ID and what date), along with whether the user logged off or timed out, the IP address the user was logged on from, and any errors on login (such  as wrong password or account locked):

    sqlplus / as sysdba
        column username format a11
        column "O/S User" format a11
        column "Action" format a7
        column "IP Address" format a14
        column "Error" format a5
        set recsep off pagesize 1000
        select dat.username,os_username "O/S User",
            decode(action_name,'LOGOFF BY CLEANUP','CLEANUP',action_name) "Action",
            substr(comment_text,instr(comment_text,'(HOST=')+6,
            instr(substr(comment_text,instr(comment_text,'(HOST=')),')')-7) "IP Address",
            to_char(timestamp,'DD-MON-YY HH24:MI:SS') "Logon",
            to_char(logoff_time,'HH24:MI:SS') "Logoff",
            decode(returncode,0,'OK',28000,'Lockd',28001,'Expir',28003,'Verfy',28007,'Reuse',
                1045,'NoINB',1017,decode(nvl(du.username,'~'),'~','User?','Pswd?'),to_char(returncode)) "Error"
            from dba_audit_trail dat left outer join dba_users du on dat.username = du.username
            where dat.username = upper('&userid') and trunc(timestamp) = '&whatdate'
                and os_username = 'SYSTEM'
            order by timestamp;
        exit;

Jobs Run by User on Date      [Back]      (added 5/14/16)

See what jobs were run by a particular user on a certain date (replace Mon and DD with month and day, and lower-case userid):

    cd /home/jobsub
    ls -ltr | grep ' <Mon DD> ' | grep <lower userid>

Restricting User Login Times      [Back]      (added 5/14/16)

Only allow a user <userid> to log into telnet from 7:45 in the morning to 5:30 at night, and not on Saturday or Sunday:

    chuser logintimes=:0745-1730,!sat-sun <userid>

Adding a Database Access Descriptor (DAD)      [Back]      (added 5/14/16)

Adding a DAD (Database Access Descriptor) for theserver_surv1:

    Bring up Application Server on Datastor:
        http://datastor.abcd.edu:1811
    Click on HTTP_Server
    Click on Administration
    Click on PL/SQL Properties
    Click on Create under DADs
    Select General.
    Click Next button.
    On Create DAD: Database Connection screen, enter the following:
        DAD Name or Location: /pls/theserver_surv1
        Username: surv
        Password: surv
        Connect String: theserver.ces.abcd.edu:1521:SURV1
        Connect String Format: SIDFormat (host:port:SID)
        NLS Language: AMERICAN_AMERICA.US7ASCII
        Click Next button.
    On Create DAD: Document, Alias and Session, select the following:
        Session State Management: Stateless-ResetPackageState
        Click OK button.
        Click OK to confirm /pls/theserver_surv1 has been created.
    Click Application Server: ias904AS.datastor.ces.abcd.edu
    Click Ports at top of page.
        It shows:
            Component: Oracle HTTP Server, Type: Listen, Port 7778
    Click Home link
    Click HTTP_Server | Administration | Advanced Server Properties | httpd.conf
    After Port 80, it does show:
        Listen 7778
    Click Application Server: ias904AS.datastor.ces.abcd.edu
    Mark box beside HTTP_Server.
    Click Restart button.
    Click Yes button to confirm restart.

Health Check for Banner, Oracle, and Linux      [Back]      (added 3/29/16)

My Banner, Oracle, and Linux health check script points out potential and upcoming problems to address, such as disk space for the tablespaces filling up and jobsub output tables growing too large, and the steps to fix them.  A sample output text file shows a typical run, with lots of information about the unix server and databases.

Optimizing SQL      [Back]      (added 4/26/16)

The following information on how to optimize SQL is on the Oracle FAQ's web site at http://www.orafaq.com/wiki/Explain_Plan, http://www.orafaq.com/wiki/DBMS_SHARED_POOL, and http://www.orafaq.com/wiki/TKProf.  Also see http://www.orafaq.com/wiki/Category:Performance_tuning for additional tuning procedures and commands.  Some of these Oracle commands and scripts that you can use to optimize SQL are listed below:

Tuning Oracle and Linux      [Back]      (added 4/27/16)

Here are my notes from the Oracle webcast "Maximize Oracle Linux Performance on the Cloud".  It is mainly the commands and settings used to maximize performance.  See the presentation for a full explanation of these commands and settings.

Double Your Oracle 12c Database Speed      [Back]      (added 6/30/16)

An Oracle 12c multi-threaded database allows one unix process to handle dozens of oracle processes, instead of having dozens of individual unix processes with one oracle process per unix process. The result is an almost doubling of database speed. To convert to a multi-threaded database, the threaded_execution parameter in Oracle must be set to TRUE and the database restarted. You also need to add or set dedicated_through_broker_<listener-name> in listener.ora to ON and change the connect strings in dbstart and dbshut (and in your own scripts) to contain sys/<syspassword>, since "connect / as sysdba" no longer works.  You will also need to do this with the sqlplus and rman commands and other Oracle commands that use operating system authentication.  Warning: There may be a memory leak when using threaded execution, so, be on the lookout for that.  To set up a multi-threaded database:

    ps -ef | grep orcl
        Shows dozens of oracle processes.
    Add "DEDICATED_THROUGH_BROKER_<listener-name>=ON" parameter to the end of the listener.ora file where listener-name is the name of the database listener.
    lsnrctl reload
    Change "connect / as sysdba" strings in dbstart and dbshut (and in your own scripts) to "connect sys/<syspassword> as sysdba".
    sqlplus sys/<syspassword> as sysdba
        alter system set threaded_execution=true scope=spfile;
        startup force
        connect sys/<syspassword> as sysdba
        select pname,sosid,spid,stid,execution_type from v$process where background = 1 order by pname;
            Shows dozens of oracle processes, most of which are THREAD processes.
        select count(spid),spid,execution_type from v$process where background = 1 group by spid,execution_type;
            Shows the number of oracle processes per unix process (SPID).
        exit
    ps -ef | grep orcl
        Shows about a half dozen oracle unix processes.  One unix process (SPID - u004 or u005) is running most of those dozens of oracle processes as multi-threaded processes.
        Warning:  You cannot kill individual threads within a unix process with the unix kill command without taking every other user with you (you can kill the pmon process, since it is in its own separate unix process).

Showing 5-Minute Running Load Average      [Back]      (added 5/8/16)

Log the rolling 5-minute system load average (to loadaverage.log) and e-mail that information, along with the top 5 %CPU processes, if it goes over a specified maximum, by running the loadaverage.sh script.  Note: Set this up in cron to run every few minutes, such as every 5th minute as in:
    */5 * * * * /home/oracle/scripts/loadaverage.sh &>/dev/null
Before doing that, set the maximum 5-minute Load Average to be notified on (3 is considered critical), and the e-mail address(es) to notify about high load averages, in the loadaverage.sh script.  Also make sure that the permissions on loadaverage.sh are set to 755.

To find average, min, and max system load average values from loadaverage.log:
    awk '{if($2=="Average:") {if(min==""){min=max=$3}; if($3>max) {max=$3}; if($3<min) {min=$3}; total+=$3; count+=1}} END {print total/count, min, max}' loadaverage.log

To find system load average values > 3 in loadaverage.log:
    awk '{if($2=="Average:" && ($3>3)) {print $0}}' loadaverage.log
        if dw00 line there, check trace file, such as:
            locate trace | grep dw00 | sed 's/^/ls -ltr /' | sh | grep 'Sep 06'
        vi /u01/app/oracle/diag/rdbms/prod/PROD/trace/PROD_dw00_8206.trc
            look for lines between 21:25:000 and 21:35:000 for what was running in the spike shown below, such as:
                *** MODULE NAME:(Data Pump Worker) 2013-09-06 21:30:22.819
                *** ACTION NAME:(SYS_EXPORT_FULL_04) 2013-09-06 21:30:22.819

To find system load average values between 2 and 3 in loadaverage.log:
    awk '{if($2=="Average:" && ($3>=2) && ($3<=3)) {print $0}}' loadaverage.log

To create a comma-delimited CSV file with date/times and load averages:
    cat loadaverage.log | grep 'Average:' | grep 'Sep 6' | awk '{print "echo `date -dq",$5,$6,$7,$8,"q +q%x %Xq`","t",$3}' | tr 'q' "'" | sh | sed 's/ t /,/' >loadaverage.csv
With that loadaverage.csv file, you can transfer it to a Windows server and open it up with Microsoft Excel.  Then, you can have Excel graph that CSV file to show the load average over the specific time periods that it was taken.  Below is a sample of that loadaverage graph which I created in Excel.  Is that cool, or what?



Getting the Banner Product Version Numbers      [Back]      (added 5/3/16)

The versions.sql script here shows the latest Banner products, versions, and upgrade dates found in the %VERS tables, along with the host name, database, and Oracle version number.

Cross Referencing Tables and Forms Used by a Form      [Back]

Here is a unix script, formtables.shl, which shows which tables are used by the given Oracle form, and how they are used (Select or List of values (LOV), Update, Insert, Delete, or undetermined), which can be used on .fmb (form) and .pll (library) files.   It parses the strings in the form file, and makes a best guess effort in determining the tables used by the form, matching the string patterns against a file listing the non-system tables in the database (generated by formtables.sql).   Since it doesn't analyze the code or trace through the function calls in the attached libraries, or look at database triggers for side effects of table changes, it may not find some table references, but, it does a pretty good job on most form files.   For Banner users, it also shows which forms are called by this form, including form access (query only, etc.).

You will first need to run formtables.sql from sqlplus (using @formtables.sql), logging in as SYS or SYSTEM or as a DBA userid, to generate the list of non-system tables in the database that will be matched by your formtables.shl runs.   (Note: You should also run formtables.sql after at least installing each major upgrade in Banner, such as 3.x to 4.x, and possibly minor upgrades as well, to keep the table list current.)  You may want to edit the resulting formtables.tables file to get rid of any non-Banner tables.   After generating the table listing, you can then run formtables.shl on a form-by-form basis as needed, or on an entire directory of forms using the unix find command.  Both of those ways are shown below (the lines with and following the find command must be all on one line when you type it in; make sure you do a "chmod 755 formtables.shl" in unix before using the script):

formtables.shl $BANNER_HOME/<product>/forms/<formname>.fmb
find $BANNER_HOME/<product>/forms -name '*.fmb'
     -exec formtables.shl {} \;

Another Banner-specific unix script, formcalls.shl, recursively finds all forms which are called by the given form, showing the list of all forms, a pruned tree of called forms, and the complete list of forms with their subforms, including access switches (query only, etc.).   This information can be used in tasks such as developing the list of forms for a class when you're only given a few top-level form names for that class.  Note that it requires that all forms and subforms must be in or linked in the $BANNER_LINKS directory.   Also, be aware that it will not find calls to generated form names, such as those generated by Banner LIST_VALUES_CALL triggers.  Because of the large amount of output produced for some forms, such as the employee information screen with all of its subscreens, you should probably only run this on a form-by-form basis as needed, as shown below (also do a "chmod 755 formcalls.shl" in unix before using the script):

formcalls.shl $BANNER_LINKS/<formname>.fmb

This could be used to find the list of forms called by a set of forms by creating a unix shell script similar to the following (which could take a long time to run, depending on how many forms had to be recursed through):

rm fcalls.out
formcalls.shl $BANNER_LINKS/form1.fmb
cat fcall.out >>fcalls.out
formcalls.shl $BANNER_LINKS/form2.fmb
cat fcall.out >>fcalls.out
... (pairs of lines like above for each form in the set of forms)
sort fcalls.out | uniq >fcall.out
cat fcall.out

Modified versions of the formtables scripts, collector.shl and collector.sql, are available to identify just the list of forms which reference Banner collector tables (or any given set of tables).  The collector.shl script would be run using the "find" command (after first running collector.sql), similar to the formtables scripts:

find $BANNER_HOME/<product>/forms -name '*.fmb' -exec collector.shl {} \;

If you want to find which forms contain a given string, use the formgrep.shl unix script (certainly not to be confused with FormGrep from August Tenth Systems, Inc.!), calling it with the form name and string to search for, such as searching for shrcomc in all forms in the $BANNER_LINKS directory:

find $BANNER_LINKS -name '*.fmb' -exec formgrep.shl {} shrcomc -1 \;

The optional third parameter to formgrep.shl indicates how many lines to show: -1 for no lines (just the file name if it matches), 0 for just the matching line (the default), or greater than zero for the number of lines before and after the matching line to also show.  If you show matching lines, you may need to wade through the junk lines to find what you want.

To show which tables are updated by the given Pro-C (*.pc) or Pro-COBOL (*.pco) program, and how they are used (Update, Insert, Delete), you can use the progtables.shl script.  To find the tables in all of the Focus programs (*.fex) in the current directory, you can use the foctables.shl script.  Run formtables.sql first before doing any progtables.shl or foctables.shl runs.  (Note: We no longer use Focus, so this foctables.shl is my final version of it.)

Increasing the Maximum Memory Map Entries Per Process      [Back]      (added 5/3/16)

Increase the maximum memory map entries per process from the default 65536 to 262144 to fix an "out of memory" error on the PROD server (which will require a restart of the PROD database to take effect).

    From the root account:
        more /proc/sys/vm/max_map_count
            shows: 65536 (the default value)
        sysctl -w vm.max_map_count=262144
        more /proc/sys/vm/max_map_count
            shows: 262144 (the new value)
        vi /etc/sysctl.conf
            added the following line to make the new value persistent:
            vm.max_map_count = 262144
            :wq

    From the oracle account in PROD database (sqlplus):
        select i.ksppinm name , v.ksppstvl cur_val,
            v.ksppstdf default_val, v.ksppstvf
            from x$ksppi i, x$ksppcv v
            where i.indx = v.indx
            and i.ksppinm in ('_realfree_heap_pagesize_hint', '_use_realfree_heap');
                still shows _realfree_heap_pagesize_hint is set to 65536
        alter system set "_realfree_heap_pagesize_hint" = 262144 scope=spfile;

We will need to stop and restart the PROD database for this change to take effect, since the active "_realfree_heap_pagesize_hint" parameter can't be changed using the "alter system" command.

TWO_TASK and Insufficient Privileges      [Back]      (added 5/6/16)

If you are getting "ORA-01031: insufficient privileges" on "sqlplus / as sysdba", then do:
    unset TWO_TASK
Reason:  When TWO_TASK is set, then SQL*Net will be used to connect to the DB. The defaults upon installation is that the DB can not be started/stopped remotely (using SQL*Net) due to security concerns.

Using Restricted Built-In's in Any Form Trigger      [Back]

Oracle Form triggers such as when-validate-item don't allow restricted built-ins such as execute_query and navigation routines (like go_item and go_field).  To get around this, you can create a text variable in the block (such as ship_changed, with it's initial value set to 'N'), and set it to 'Y' in the when-validate-item for the field being changed.  Then, create a trigger at the form level (such as ship_change) which checks that flag variable, does the processing, including whatever restricted built-ins, then resets the flag; and put execute_trigger calls to the trigger in the form-level when-new-item-instance and when-new-block-instance triggers.  Also, in order to show the changes immediately if a user types a value into the field and then clicks on a field in a different block on the form, put a couple of go_field calls to jump to the block that needs the focus for the processing, do the processing, then return to the clicked-on block's field.  Sample source code for the form-level ship_change trigger is shown below:

declare
     came_from varchar2(128);
begin
     if :ship_changed = 'Y'
     then
          :ship_changed := 'N';
          came_from := :SYSTEM.CURSOR_ITEM;
          GO_FIELD('FTVSHIP_ADDR_LINE1');
          execute_query;
          GO_FIELD(came_from);
     end if;
end;

If the change routine modifies a field on the form, and you don't want the field's "when-validate-item" trigger to re-validate (since that trigger is fired both when someone types into that field and when a routine updates that field), you can call the set_item_property routine to set the field's state back to valid after your routine changes it so that the when-validate-item trigger doesn't fire because of the change (although, if the user then types into the field, the field's state is then changed back and the trigger will fire).  For example:

set_item_property('vendor_spriden_id',item_is_valid,property_true);

Kevin Davidson sent me another solution to using restricted built-ins in any form trigger, which uses a timer expiring to fire a trigger.  See http://www.arrowsent.com/oratip/tip29.htm.   A second article with this solution is at http://the-big-o.port5.com/article13.shtml.

Creating a csv File in Oracle      [Back]

Below is a method for creating a csv (comma separated value) file using SQL.  This example shows how to create a csv file for "select * from sfrfaud where sfrfaud_pidm = 271397 and sfrfaud_term_code = '201130';"

sqlplus / as sysdba
set pagesize 1000
column spriden_id format a9
column spriden_first_name format a15
column spriden_mi format a15
column spriden_last_name format a15
select spriden_pidm,spriden_id,spriden_first_name,spriden_mi,spriden_last_name
    from spriden where spriden_id in ('S00531521','S00271397') and spriden_change_ind is null;
        SPRIDEN_PIDM SPRIDEN_I SPRIDEN_FIRST_N SPRIDEN_MI SPRIDEN_LAST_NA
        ------------ --------- --------------- --------------- ---------------
        271397 S00271397 Geralyn R. Ayala
        532912 S00531521 Ramiro De Los Santos
set pagesize 0
set feedback off
set linesize 2000
set trimspool on
set recsep off
column column_id noprint
spool csv1.sql
select 0 column_id,'select' from dual
union all
select column_id,decode(column_id,1,'','||'',''||')||''''||column_name||''''
    from dba_tab_columns
    where table_name = upper('sfrfaud')
    union all
    select 9999,'from dual;' from dual
    order by 1;
select 0 column_id,'select' from dual
    union all
    select column_id,decode(column_id,1,'','||'',''||')||
    decode(data_type,'VARCHAR2','''"''||'||column_name||'||''"''',
    'DATE','to_char('||column_name||',''MM/DD/YYYY'')',
    column_name)
    from dba_tab_columns
    where table_name = upper('sfrfaud')
    union all
    select 9998,'from sfrfaud' from dual
    union all
    select 9999,'where sfrfaud_pidm = 271397 and sfrfaud_term_code = ''201130'';' from dual
    order by 1;
spool off
!grep -v '^SQL' csv1.sql | grep -v '^ ' >csv.sql
!rm csv1.sql
spool sfrfaud_271397.csv1
@csv.sql
spool off
exit
!grep -v '^SQL' sfrfaud_271397.csv1 >sfrfaud_271397_$ORACLE_SID.csv
!rm sfrfaud_271397.csv1

Locating strings or substrings in Oracle tables      [Back]

This example (similar to fieldin.sql) shows how we can find where "PROD8" occurs in the wtailor tables by running the SQL produced by the following:

sqlplus / as sysdba
set pagesize 1000
set linesize 160
select 'select distinct ''' || column_name || ''' "COLUMN" from ' || owner || '.' ||
table_name || ' where ' || column_name || ' like ''%PROD8%'' union' from dba_tab_columns
where owner = 'WTAILOR' and data_type = 'VARCHAR2'
union
select 'select null from dual;' from dual;
run the above sql, which shows:
COLUMN
-----------------------------
TWGBGLUI_SYSTEM_NAME

Finding the Self-Service pin for a particular SPRIDEN ID (FAQ 1-3R1ZCV)      [Back]

select gorpaud_pin
    from gorpaud a
    where a.gorpaud_pin is not NULL
    and a.gorpaud_pidm in (
        select spriden_pidm from spriden
        where spriden_id = '&ID')
    and a.gorpaud_activity_date in (
        select max(gorpaud_activity_date)
        from gorpaud b
        where a.gorpaud_pidm = b.gorpaud_pidm
        and b.gorpaud_pin is not NULL);

Finding the Oracle password for a Banner ID      [Back]

Until Oracle fixes this HUGE SECURITY HOLE, you can usually find someone's Oracle password (if they have logged into INB lately) by looking at the INB (Internet Native Banner) Apache logs, as shown below (in this case, the Apache logs are in /opt/app/oracle/product/10gFR_1/Apache/Apache/logs).  It takes a while to run, depending on the total size of the Apache logs in the logs directory, but it does show the clear-text password used to log into INB.

cd /opt/app/oracle/product/10gFR_1/Apache/Apache/logs
grep <lower case user ID> access_log* | grep ltc | tail
    see the ltc value, which contains the clear-text password!

UPDATE:  It looks like Oracle did fix this with 10.2 IAS (OAS).  I ran this at a site that has 10.2, and it didn't display the clear-text password.  So, this may be a problem for 10.1 and earlier.  So, be sure to update your IAS to beyond 10.1, if you haven't already done so, to get around this security hole.

Adding SQL Scripts to Job Submission      [Back]

Adding SQL Scripts to run from Banner job submission is fairly easy.  It uses the runsqlplus.pc Pro-C program in Oracle Scripts.  (Specific to Banner products.  For security purposes, you will be prompted to enter the name of the Banner job submission screen to view this web page.)

Truncating the JobSub tables      [Back]      (added 5/3/16)

Truncate the jobsub output tables in the current database, keeping the latest 6 months of data (03-NOV-15 here - change as needed; fk_enable_disable in Oracle Scripts table below):

        Lock the guboutp and guroutp tables in exclusive mode so that no one will be updating them; then, export those two tables so that we have a backup of them in case something goes wrong:
        sqlplus / as sysdba
            lock table general.guboutp,general.guroutp in exclusive mode;
            !exp sys/change_on_install file=/u04/Backup/PROD/guboutp_copy.dmp tables="(general.guboutp,general.guroutp)" consistent=Y
        Create a copy of the latest guboutp and guroutp records that we want to keep (such as for the current quarter):
            create table general.guboutp_copy as
                (select * from general.guboutp where guboutp_date_saved >=
                    to_date('03-NOV-15'));
            create table general.guroutp_copy as
                (select * from general.guroutp where 1 = 0);
            insert into general.guroutp_copy
                (select * from general.guroutp g1 where exists
                    (select 'x' from general.guboutp_copy g2
                        where g1.guroutp_user_num = g2.guboutp_user_num
                        and g1.guroutp_one_up_no = g2.guboutp_one_up_no
                        and g1.guroutp_file_number = g2.guboutp_file_number));
          Disable the constraints for guboutp and guroutp to prevent checking while we are deleting and recreating those tables:
            @/home/oracle/SCRIPTS/fk_enable_disable.sql
                general
                guboutp,guroutp
                disable
          Truncate the guboutp and guroutp tables to release the disk space allocated to them (this also unlocks those tables):
            truncate table general.guroutp;
            truncate table general.guboutp;
        Copy the latest guboutp and guroutp records back into their general tables:
            insert into general.guboutp (select * from general.guboutp_copy);
            insert into general.guroutp (select * from general.guroutp_copy);
        Re-enable the constraints for guboutp and guroutp, and commit these updates:
            @/home/oracle/SCRIPTS/fk_enable_disable.sql
                general
                guboutp,guroutp
                enable
            commit;
        Drop the copies of guboutp and guroutp:
            drop table general.guboutp_copy;
            drop table general.guroutp_copy;

Adding Web Pages to the Self Service Products      [Back]

Our Drivers License update screen was added to be run from the Personal Information menu, and can be used as-is for drivers license entry, or as a starting point (the code is heavily documented) to see how you can add your own web pages to the Self Service products.  It also contains instructions on how to add name and SSN fields to the View Pay Stub Detail web page, along with a Self Service PIN Reset web page, a Banner Password Reset web page, and a Timesheet Approvals web page.  (Specific to Banner products.  For security purposes, you will be prompted to enter the name of the Banner job submission screen to view this web page.)  It also shows how to add web pages to any Oracle database, not just Banner.

Converting Employee ID's from SSN to Generated ID's      [Back]

We have converted our employee ID's in Banner from SSN to generated ID's.  Here are my notes on how we did this, including an Employee ID Lookup web page.  (Specific to Banner products (versions 5, 6, and 7).  For security purposes, you will be prompted to enter the name of the Banner job submission screen to view this web page.)

Finding Non-Null Banner table SSN Fields      [Back]      (added 5/3/16)

Of the Banner table columns having "SSN" in their name, see which of those columns actually have SSN data in them, counting those non-null entries (such as for columns to encrypt using Oracle Advanced Security).

    set pagesize 10000 linesize 240 trimspool on recsep off
    spool ssndata1.sql
    select 'select ''' || dc.owner || ''' owner,''' || dc.table_name ||
            ''' table_name,''' || dc.column_name || ''' column_name,count(*) from ' ||
            dc.owner || '.' || dc.table_name || ' where ' ||
            dc.column_name || ' is not null having count(*) > 0 union all'
        from dba_col_comments dc,dba_objects do
        where dc.owner = do.owner
            and dc.table_name = do.object_name
            and do.object_type = 'TABLE'
            and dc.column_name not like '% %'
            and dc.column_name like '%SSN%'
        order by dc.owner,dc.table_name,dc.column_name;
    spool off
    !grep '^select ' <ssndata1.sql >ssndata2.sql
    !cat ssndata2.sql | sed "`cat ssndata2.sql | wc -l`s/ union all/;/" >ssndata1.sql
    column owner format a13
    column table_name format a24
    column column_name format a30
    spool ssndata.lst
        @ssndata1.sql
    spool off
    column owner clear
    column table_name clear
    column column_name clear
    !echo The above listing is in ssndata.lst

Showing What Databases Are Running on this Server      [Back]      (added 5/6/16)

Searching for the pmon processes indicates what databases are running on this server, such as the PROD server:

    ps -ef | grep pmon | grep -v grep
        oracle 38469826 1 0 Apr 15 - 0:29 ora_pmon_CAT
        oracle 42336408 1 3 Apr 15 - 1:08 ora_pmon_PROD8
        oracle 44302482 1 0 Apr 15 - 0:38 ora_pmon_RMANCAT
        oracle 49741948 1 0 Apr 15 - 0:29 ora_pmon_TNETPROD6

How To Fix Banner and Oracle Problems      [Back]

Here are my notes on how to fix problems with Oracle and Banner (mainly Ellucian Banner Finance) that we have encountered, such as unqueueing a posting document that is crashing posting.  (Specific to Banner products.  For security purposes, you will be prompted to enter the name of the Banner job submission screen to view this web page.)

Fixing Unable to Allocate Shared Memory      [Back]      (added 5/15/16)

Increase the size of the SGA and the large pool when we get the error message: Failed to connect to database instance: ORA-04031: unable to allocate 80 bytes of shared memory.

    sqlplus "/ as sysdba"
        alter system set shared_pool_size=200M scope=spfile;
        alter system set large_pool_size=8M scope=spfile;
        exit

E-Mailing From Oracle      [Back]

Here is how we e-mail from Oracle using PL/SQL scripts, which includes my email_files procedure that not only sends text e-mails, but also HTML e-mails plus multiple attachments of various types.  This is for Oracle 9.2 and above.  For earlier versions of Oracle, see Running System Commands from PL/SQL Using Pipes below.

Automated Installer for Banner Patches and Bundles      [Back]

Our automated installer for Banner patches and bundles (autopatch) is a companion to Ellucian's automated installer for Banner upgrades.  These scripts are for UNIX platforms.  (Specific to Banner products.  For security purposes, you will be prompted to enter the name of the Banner job submission screen to view this web page.)

Application Express Installation      [Back]

We have also installed Oracle's Application Express and it's prerequisites into an Oracle database.  APEX, formerly HTML DB, is Oracle's rapid web application development tool for the Oracle database.  This installation is for the APEX 3.0 version.

Archive/Purge of Banner Data      [Back]

To free up space in Banner's database, we archived fiscal years 1998, 1999, and 2000.  This includes running Ellucian's archive process (foparcp) for fgbtrnd/fgbtrnh, as well as moving old nhrdist records off to a separate database containing just an nhrdist table and it's indexes.  (Specific to Banner products.  For security purposes, you will be prompted to enter the name of the Banner job submission screen to view this web page.)

Oracle Database Tuning      [Back]

I have a few tuning topics for Oracle, which I will be adding to as time permits.  These should be generic to any standard Oracle installation, and are not Banner specific.  Current topics include Redo Log Buffer Latches, Database Buffer Cache Size, and Shared Pool Size.  And, be sure to specify optimizer_mode=rule in your init.ora files (i.e., $ORACLE_HOME/dbs/initPROD.ora) for Banner.

Oracle Bug in Resize Datafile      [Back]

I think I've found a bug in resizing a datafile ("alter database datafile 'filename' resize ...;") that will prevent you from doing a "create controlfile". Select this link to read about the resize datafile bug. After further testing, it seems to only be a problem if you try to shrink a datafile such that it is SMALLER than when it was originally created. Also, it does not seem to cause any problems with the Oracle recovery procedures.

If you have any contacts at Oracle, please let them know about this bug and have them get in touch with me to see if we can work something out to fix the problem or correct my understanding or procedures that caused it!

Old Stuff      [Back]

This section contains some old and probably out-of-date information from the earlier days of my web site.  It is mainly here for historical purposes, and mainly for older versions of Oracle and Banner, but may contain a few gems that you can put to use.  As always, be sure of what you are doing beforehand, and use these at your own risk!

Fast Forms Development - Step-By-Step

Do you need to develop a custom form for Banner, but don't know where to start?  Well, this is the place.  This page shows you how to get a simple form up and running in under 2 hours, taking you through step-by-step how to create an Invoice Hold Indicator Change form, which is derived from forms that Ellucian supplies with Banner.  After creating this form for yourself, you can then use a similar procedure to create your own simple custom forms, and build on your Forms Designer knowledge from there to create more complex forms.  This forms development page gives both a Banner 3 version and a Banner 4 version (we stopped using that form when we went to Banner 5/Developer 6i).   (Specific to Banner products.  For security purposes, you will be prompted to enter the name of the Banner job submission screen to view this web page.)

Uniform Extent Size Reorganization

Reorganizing your tables and indexes into small, medium, and large tablespaces with uniform extent sizes eliminates fragmentation problems, reduces occurrences of "unable to allocate next extent" errors, and gives you exact space availability and predictable growth patterns for your tablespaces.  Select this link to go to the uniform extent size reorganization page showing how to do this using Ken Payton's gzrrddl script, including a complete log of the reorganization steps I went through and the scripts that I used, along with references to other reorganization web sites and papers.

Banner Password Aging

Before Oracle got into the act, we had a procedure for password aging in Banner, where the users must change their passwords at least once every 6 months in order to continue accessing their screens in Banner.  Note that although Oracle 8 has password aging built in, it is not currently fully integrated with Banner until release 5.4 (Banner General 5.5), at which time all password parameters in an Oracle profile are supported.  (Specific to Banner products.  For security purposes, you will be prompted to enter the name of the Banner job submission screen to view this web page.)

Oracle Technical Support Bulletins

Select this link to see a list of Oracle technical support bulletins and scripts from Rhubarb's Oracle Site, which is no longer.  This isn't a complete list, but there's plenty here for everyone searching for Oracle technical information as well as database tuning and information scripts.  There is also a link to Oracle's web site with the latest list.

Faxing with GNU efax

If you're running Banner on AIX or some other unix system, and if you want to fax documents, such as PO's, you can install and use the GNU "efax" public domain software to send the PO as a fax.  Select this link to see the instructions on how to customize and set up efax on AIX so that you'll have a unix command line fax command available for you to use.  (Producing a PO file as text or other format supported by efax is not covered here.)

Oracle Scripts      [Back]

These scripts and procedures, which are mainly for Oracle DBA's, are what I use almost daily to maintain our Banner Oracle database.   I've indicated in the table below which ones are specific to Banner products.   The .sql files listed in the table are sql scripts that you can download and run (which may need to be edited first - see Notes below).  The .txt files are ascii text descriptions of the scripts, suitable for being downloaded as help files, and, in some cases, contain comprehensive explanations of the procedures (such as resize.txt and the move procedures).   The .shl files are unix scripts (IBM AIX).  There are also scripts in some of the Tips and Tricks entries which aren't included here.

all_rights.shl Used by other scripts to grant all rights to files produced (unix).
autoora.sql Create the Focus .acx and .mas table descriptor files for the given table/view name or wildcard and table/view owner or wildcard.  (Generic to any Oracle database product.  Note: We no longer use Focus, so this is my final version of this script.)
bigsegsown.sql Find the largest segment (table or index) for each owner, showing its size and its percentage of that owner's current data.  (Generic to any Oracle database product.)
bigsegsts.sql Find the largest segment (table or index) in each data tablespace, showing its size and its percentage of that tablespace's current data.  (Generic to any Oracle database product.)
collector.shl

collector.sql

Shows which collector tables (or any given set of tables) are used by the given form (.fmb), and how they are used (Select, Update, Insert, Delete).  (Does not search through attached library files or triggers for references, so, it may miss some tables.)  Run collector.sql first before doing any collector.shl runs.  See Cross Referencing Tables and Forms Used by a Form.
comptables.sql Compare a table's records between two databases (local and remote), showing the records in one database which don't match the corresponding records in the other database.  See Comparing Tables Between Databases.  (Generic to any Oracle database product.)
datein.txt

datein.sql

Shows which tables in Banner contain the given activity date, including a count of the records with that date in each of the tables.  (Specific to Banner products.  Also see datesin.sql.)
dateinminmax.sql Shows which tables in Banner contain the given range of activity dates, including a count of the records with those dates in each of the tables.  (Specific to Banner products.)
datemax.sql Show the maximum activity date value for each table, sorted from earliest to latest maximum activity date.  (Specific to Banner products.)
dates.sql Shows the activity dates in a given Banner table, along with counts of records containing those dates.  (Specific to Banner products.)
datesin.sql Shows which tables in Banner contain the given range of activity dates (plus an optional pidm), including a count of the records with that date in each of the tables.  (Specific to Banner products.   Also see datein.sql.)
dbbackup.shl
dbb_gen_all.shl
dbb_gen_list.shl
dbb_gen_cmds.shl
dbb_overview.sql
dbb_ctlfile.sql
dbb_index_stats.sql
A generic version of our set of database backup scripts, which we run nightly as a cron job from userid oracle for cold backups.  (Should be generic enough for any Oracle installation that follows standard naming conventions such as Optimal Flexible Architecture (OFA), with the upper-case instance name in the directory structure.  Change the scripts to match your directory structure and database instance names - select this link to see our backup scripts instructions.)  Runs datafile zips in parallel (as background processes) to reduce database downtime during backup by 75%.
delpidm.sql Creates sql to delete all records containing a given pidm in all Banner tables, which you can edit and run.  (Specific to Banner products.)  Note:  The delfield.sql script should probably be used instead of this.
delpidmslike.sql Creates sql to delete all records containing a given pidm pattern in all Banner tables, which you can edit and run.  (Specific to Banner products.)  Note:  The delfield.sql script should probably be used instead of this.
delfield.sql Finds all tables containing a field with a name like a given substring (such as ACTIVITY_DATE), and creates sql to delete the records in those tables where that field contains a given value, list, or wildcard.  (Can be used with any Oracle database product if "Banner specific" lines removed or changed.)
fieldin.sql Finds all tables containing a field with a name like a given substring (such as ACTIVITY_DATE), and counts the records in those tables where that field contains a given value, list, or wildcard.  (Can be used with any Oracle database product if "Banner specific" lines removed or changed.)
fk_enable_disable.sql Enable or disable foreign keys on a table so that we can insert into or delete from that table without affecting other tables.
foctables.shl Find the tables in all of the Focus programs (*.fex) in the current directory.  Run formtables.sql first before doing any foctables.shl runs.  See Cross Referencing Tables and Forms Used by a Form Note: We no longer use Focus, so this is my final version of this script.
formcalls.shl Recursively finds all forms which are called by the given form (.fmb), showing the list of all forms, a pruned tree of called forms, and the complete list of forms with their subforms, including access switches (query only, etc.). Banner specific.  See Cross Referencing Tables and Forms Used by a Form.
formgrep.shl Searches for occurrences of a given string in a form (.fmb), showing the matching line(s) and, optionally, the surrounding lines, or just showing the matching form name.  See Cross Referencing Tables and Forms Used by a Form.
formtables.shl

formtables.sql

Shows which tables are used by the given form (.fmb or .pll), and how they are used (Select, Update, Insert, Delete), along with which forms are called by this form (for Banner forms).  (Does not search through attached library files or triggers for references, so, it may miss some tables and form calls.)  Run formtables.sql first before doing any formtables.shl runs.  See Cross Referencing Tables and Forms Used by a Form.
grants_made.sql List all tables with privileges granted to users (no roles or Banner or system userids).  (Generic to any Oracle database product.)
grants_recvd.sql List all users with tables granted privileges to (no roles or Banner or system userids).  (Generic to any Oracle database product.)
kill_user.txt

kill_user.sql

Kills the Banner and sqlplus sessions of a given user, listing the users currently logged on to select from.  (Generic to any Oracle database product.)
monitor.shl

monitor1.shl

monitor.sql

Monitors .log files in /home/jobsub directory for posting errors and archivelogs filling up too fast (runaway process?), notifying operator console of them until monitor.lst is deleted.  Ours is set up in cron from root to check every 10 minutes (0,10,20,30,40,50 8-16 * * 1-5 /usr/bin/su - jobsub "-c sh monitor.shl").  (Specific to Banner products and AIX Unix.)
moveindx.txt

move_indexes.sql

Moves all specified indexes, wildcards accepted, to a separate tablespace using drop/add, including rebuilding references.   Specify same tablespace name to recreate (thus, compress) all indexes.  (Generic to any Oracle database product.  Assumes primary key names begin with "PK_".  Edit to use original next_extent (default) or current pctincrease next_extent.)  Note: This is a very old script and may not work or work well with current versions of Oracle.
moveuser.txt

move_user.sql

Moves a given user's tables and indexes to separate tablespaces using multiple import passes with different default tablespaces.   The moveuser.txt file contains sql commands to determine how big the tables and indexes tablespaces should be initially.  The script contains some neat tricks, which you may want to learn.  Note: This is a very old script and may not work or work well with current versions of Oracle.
pidmin.txt

pidmin.sql

Shows which tables in Banner contain the given pidm, including a count of the records with that pidm in each of the tables.   (Specific to Banner products.)  Note:  The fieldin.sql script should probably be used instead of this.
progtables.shl Show which tables are updated by the given Pro-C (*.pc) or Pro-COBOL (*.pco) program, and how they are used (Update, Insert, Delete.   Run formtables.sql first before doing any progtables.shl runs.  See Cross Referencing Tables and Forms Used by a Form.
recreate_index.sql Recreates (drop/rebuild) a regular or primary key index to free up deleted space for a given index and owner, including rebuilding references.  (Generic to any Oracle database product.  Assumes primary key names begin with "PK_".  Edit to use original next_extent (default) or current pctincrease next_extent.)  This can also be done in Oracle 7.3.4+ using the alter index command, such as in: "alter index posnctl.nhrfinc_key_index rebuild unrecoverable tablespace large_indexes;".
resize.txt

resize.sql

Calculates the new table/index sizes for gurrddl for a given number of records per extent for those tables that are over a given percentage full.  The resize.txt file gives a full explanation of resizing, including gurrddl, maxextents, and export/import.
resize_table.sql Calculates the new table/index sizes for gurrddl for a given number of records per extent for a given table (single-table version of resize.sql).
runsqlplus.pc This program takes the .sql script and parameters from jobsub and passes them to sqlplus using an input file; afterwards, deleting the input file and corresponding records from the collector table.  Put it in $BANNER_LINKS and compile it for use with "Adding SQL Scripts to Job Submission".  (Specific to Banner products.)
stub.sql

stub.txt

Generates SQL to create a database using the current database as a model, including all of it's logfiles and logfile groups, system datafiles, all tablespaces and their datafiles and default storage settings, and all rollback segments, along with running the Oracle catalog procedures.  See Cloning a Database using Export/Import.
tabinfo.sql

tabinfobig.sql

Reports information about one or more tables, including sizes, columns, indexes, primary key, foreign keys, constraints, triggers, and references to a given .lst file name; originally by Biju Thomas, with updates. tabinfobig is for output >1,000,000 bytes. (Generic to any Oracle database product.)
table_changes.sql Lists the table definition changes and added/deleted tables between the current (post-upgrade) database and another (pre-upgrade) database.
terminated_ids.sql Shows the Banner user ID's for terminated employees who's accounts are not yet locked or deleted, the objects (tables) they own, and the commands to lock or remove them.  (Specific to Banner products.)
tuning.sql

tuning2.sql

Performance tuning scripts - the first by David Midgett at Eastern Kentucky University; the second was from http://www.oramag.com/code/cod46dba.html (no longer available).
userlocks.sql Shows which user sessions have locks against which tables and other objects, and the commands to kill those sessions (in case an aborted session still has locks on objects).  (Generic to any Oracle database product.)
usertables.sql List all tables and their owners and record counts in the USERS tablespace.  (Generic to any Oracle database product.)
vercheck.sql

vercheck.shl

Check the version numbers in the forms against the version numbers in Banner for any mismatches, as well as for any versions not at a given point release.  (Specific to Banner products.)
view_or_print.shl Used by other scripts to view and/or print their results (unix).

whoson.sql

Shows who is currently logged into Banner, either through Banner GUI (shows form name) or sqlplus (shows SQL*Plus) or as a process (such as for jobsub), for the current database.  (Generic to any Oracle database product.)
whossql.sql A modification of an older version of the whoson.sql script to also show the SQL currently being executed or the most recent SQL executed by the user.
whowebbed.sql Shows who has accessed Self Service in the past 3 days (from Penny Ginn), as well as those currently on Self Service (from John Wade).  (Specific to Banner products.)

If you discover any problems or omissions in these scripts, please contact me at srea1957@gmail.com.

Notes:  Some of these scripts may be specific to or have references to Ellucian Corporation's Banner modules, such as HR/Payroll, Finance, and Student, so, you may need to edit those scripts to work with your own 3rd-party applications.  Also, there may be references to /home/common and /home/dba_scripts in the scripts, which is where we keep our scripts, which you will probably need to change to reflect your directory structure.

Some Useful Oracle Links      [Back]

With Banner Info:

http://betwinx.com - Banner Reporting Solutions by Bruce Knox.  Includes information and scripts for SQL, Argos, MS Access, and Application Express.
https:/aprilcsims.wordpress.com/ - Oracle High Availability, by April Sims.  Includes presentation and information on creating Data Guard logical standby databases (instead of physical standby databases that I show).
http://www.utm.edu/staff/lholder/dba - Larry Holder's DBA Page.

Just Oracle:

http://www.oracle.com - Oracle Corporation's Web Site.
http://education.oracle.com - Oracle Corporation's Education Site.
http://www.skillbuilders.com/instructor-led-training/index.cfm - Webinars on Oracle and Apex (many free tutorials) - very comprehensive and succinct.
http://SearchOracle.com - Tips, scripts, news, white papers, ask the experts, discussion forums - lots of neat stuff!
http://www.orafaq.org - The Underground Oracle Frequently-Asked Questions List by Frank Naudé (from South Africa!).  Also reached via http://www.orafaq.net. FAQ categories are at http://www.orafaq.org/faq2.htm.
http://www.orafans.com - ORACLE User Forum and Fans Club (especially the ORACLE Technical Papers. pages).  Site no longer available.
http://www.oraclefans.com - Info from various sites on their pages, such as machine-based FAQ's (eg., AIX and Solaris) from faqs.org in their Links page (not FAQ's page).
http://www.OracleTuning.com - Scripts and articles for DBA's.  Site no longer available.
http://www.dbresources.com - Scanning the web for Oracle articles or news?  Check out this compilation.  Site no longer available.
http://www.bijoos.com/oracle/index.htm - Biju's Oracle Page - Scripts, Utilities, Source code generators, etc.
http://www.oracle-books.com/oracle - Rhubarb's Oracle Site (from which came the Oracle Technical Bulletins).
http://www.fortunecity.com/skyscraper/oracle/699/orahtml/index.html - A bunch of articles (tuning, etc.) for Oracle DBA's (another Rhubarb collection).  Site no longer available.
http://www.fors.com/orasupp - More articles by Oracle Worldwide Customer Support (on a Russian site, no less!).  Includes some of the older Oracle Technical Bulletins.  Site no longer available.
http://www.szofi.hu/index_link.html#Oracle - Szofi Link Exchange - tons of links, not only Oracle, but other programming languages and operating systems as well.  Site no longer available.
http://the-big-o.port5.com - Oracle Tips, Tricks, Hints, and How-To's, including Oracle Forms articles and general database articles.  Site no longer available.
http://www.think-forward.com - Adelante, Ltd, Computer Consultants, with scripts and tips (no longer available), including a UNIX to VMS Translation Table (included here; from Britain).
http://www.vb-bookmark.com/vbOracle.html - Oracle Bookmark with Oracle and Perl sites containing articles, tutorials, tips, tricks, guides, and samples.
http://www.tusc.com/oracle/download/categories.html - Years of PowerPoint presentations, including lots of Oracle 9i stuff.  Site no longer available.
http://home.clara.net/dwotton/dba/oracle_extn_rtn.htm - Calling External routines from PL/SQL.  Site no longer available.
Other sites with Oracle scripts (added 5/13/16):
    https://oracle-base.com/dba/scripts
    www.orafaq.com/wiki/Scripts
    www.akadia.com/html/ora_scripts.html
    www.idevelopment.info/cgi/ORACLE_dba_scripts.cgi
    www.oracle-scripts.net 

Read blogs from Oracle and the extended community of Oracle Database evangelists.  From Oracle Database Insider Newsletter (May 2016).      (added 5/11/16)

Oracle Database Insider
Oracle Database In-Memory
Oracle High Availability
Oracle Big Data
Oracle New-Generation Database Access
Jeff Smith—Optimizing Your Database Experience
Steven Feuerstein on Oracle PL/SQL
Oracle SQL Advocate Connor McDonald’s Oracle Blog
Joel Kallman—Yet Another Blog About Oracle Application Express
Oracle Developer Advocates for SQL—All Things SQL
OracleVoice on Forbes.com
Oracle Data Mining (ODM)

Oracle Magazine

Humor Me and Other Sayings      [Back]

And, finally, these don't have anything to do with this site's subject (?), but they have given me and others of you quite a few chuckles, and, sometimes you've just got to lighten up a bit.  You might want to post some (or all) of them above your desk (I've run out of room!), and refer to them frequently, especially during those high-stress "What happened to the database?" times!

Things to Keep in Mind
Dr. (Techno) Seuss
English Can Be a Silly Language
Gentler Ways to Say Someone is Stupid
More One-Liners
Yesterday: A DBA's Backup Song
"God Speaks" Billboards
Evolution of a Programmer
Mergers and Acquisitions
Time is Short
A Different Point Of View

Technology for Country Folks
Badtimes - A Really Nasty Virus
Gems from Josephus

When a person does a good deed, when he or she didn't have to,
God looks down and smiles and says, "For this moment alone, it was worth creating the world."

Our Wedding - April 23, 2004

Hawaii - March 11, 2005

Disney - April 6, 2006


You Are Visitor Number

This Page Was Last Updated on 07/26/16

Copyright © 2016 by Maristream, LLC.   All information, scripts, forms, and other material
on this web site are freely available to all Banner and Oracle Database Administrators,
Systems Administrators, Programmers, and others that may need it.

The webmaster who maintains this web site may be reached at srea1957@maristream.com.

Disclaimer:  As with all software, especially where it affects your vital data, make sure that you examine theses scripts and that you understand what they do before you use them to see if they would have any adverse effect on your particular setup or database layout.  Make a full backup of your database in case you have to revert to your original copy of the database before the scripts were run.  Use these scripts at your own risk.  As a condition of using these scripts, you agree to hold harmless both Maristream and Stephen Rea for any problems that they may cause or other situations that may arise from their use, and that neither Maristream nor I will be held liable for those consequences.