Oracle Role Insufficient For User's Procedure To Compile

Roles are useful, though there are a few limitations. This is one limitation of Oracle roles.

Table owned by userA. UserB has "select any table" and creates SQL that sucessfully runs in sql*plus. When userB tries to put the SQL in a package, there is ORA-00942 compile error.
The solution is to directly grant the userA.table_name SELECT privilege to userB. It's not enough to have a role that allows the SELECT permission.

Example:
Create package header with no problems, then try to create package body:

Warning: Package Body created with compilation errors.

SQL> show err
Errors for PACKAGE BODY xPKG:

LINE/COL ERROR
--------
-----------------------------------------------------------------
27/7 PL/SQL: SQL Statement ignored
72/29 PL/SQL: ORA-00942: table or view does not exist

-- In another session, directly grant SELECT on table_name to this user.

-- In this session, re-submit package body.

SQL> /
Package body created.

Move Oracle Indexes To Another Tablespace

Some (mostly old-school) Oracle DBAs like to move indexes into a tablespace other than the tablespace(s) for tables. Moving an index does rebuild the index, for the limited cases when an index rebuild is a good thing.
This SQL will generate the SQL to move indexes.

select 'alter index ' || owner || '.' || index_name || ' rebuild
tablespace &newTablespace'
from sys.dba_indexes
where owner = '&ownerName'
/

Useful HP-UX Commands

Useful HP-UX commands:
bdf Show disk space.

tusc Trace system calls.

lsof List open files.

/usr/sbin/kmtune Show and sets kernel parameters.

Record system performance:
/usr/bin/sar System activity reporter.
vmstat Virtual memory statistics. Non-zero 'b' column is an I/O blockage.
netstat -i Network status. Large and increasing values for errors and collisions is a symptom of a network problem.

HP-UX operating system administrators may already be running the perfstat tool. If you see rep_server processes running from root, then perfstat is in use.
Perfstat records useful information. Record this info to a readable file with /opt/perf/bin/perfstat -z.

GlancePlus

glance

perfstat -c shows kernel parms.

/usr/sbin/swapinfo

Oracle RMAN Backup Scripts

#!/bin/ksh
#*************************************************************************************
# Open level 0 backup, without recovery catalog, including controlfile.
# (Level 0 incremental backs up all blocks.)
# DB must be in archivelog mode.
# "delete all input" removes archived log files after they have been backed up.
#*************************************************************************************
/bin/date
# The next setting is hard-coded in the RMAN command.
ORABACKUPSET=/u01/oracle/admin/backupset

$ORACLE_HOME/bin/rman nocatalog << EOF
connect target /

run {
allocate channel c1 type disk;
backup
incremental level 0
format '/u01/oracle/admin/backupset/%d.rman_level0_open_disk_nocatalog_database_%s_%p'
database plus archivelog delete all input;
backup
format '/u01/oracle/admin/backupset/%d.rman_level0_open_disk_nocatalog_controlfile_%s_%p'
current controlfile;
release channel c1;
}
EOF

RC=$?
if [ $RC != 0 ]
then
echo "RMAN return code is $RC. See error log in $ORABACKUPSET." | /usr/bin/mailx -s "`hostname`:$ORACLE_SID RMAN level0 to disk nocatalog failed" user@example.com
fi

/bin/date

**************************************************************
**************************************************************
**************************************************************

#!/bin/ksh
#**************************************************************************
# Open level 0 rman backup to disk.
#**************************************************************************
/bin/date

rman << EOF
connect rcvcat rman/x@bakcat
connect target sys/x

run {
allocate channel c1 type disk;
setlimit channel c1 kbytes 2097150 maxopenfiles 32 readrate 200;
backup
incremental level 0
format '/db_archive/rman/%d_rman_level0_open_database_%s_%p'
database;
sql 'alter system archive log current';
backup skip inaccessible
filesperset 20
format '/db_archive/rman/%d_rman_level0_open_archivelog_%s_%p'
archivelog all
delete input;
backup
format '/db_archive/rman/%d_rman_level0_open_controlfile_%s_%p'
current controlfile;
release channel c1;
}
EOF

if [ $? != 0 ]
then
mailx -s "`hostname`:$ORACLE_SID RMAN to disk failed" usererrormessage@example.com < $LOGFILE
fi

/bin/date
/usr/bin/save -v /db_archive/rman
if [ $? != 0 ]
then
mailx -s "`hostname`:$ORACLE_SID RMAN disk files to tape lib failed" user@example.com < $LOGFILE
fi

**************************************************************
**************************************************************
**************************************************************

#!/bin/ksh
#**************************************************************************
# Open level 0 rman backup to tape.
#**************************************************************************

rman nocatalog << EOF
connect target /
run {
allocate channel c1 type 'SBT_TAPE';
backup
incremental level 0
format '%d_rman_level0_open_database_%s_%p'
database;
sql 'alter system archive log current';
backup skip inaccessible
filesperset 20
format '%d_rman_level0_open_archivelog_%s_%p'
archivelog all
delete input;
backup
format '%d_rman_level0_open_controlfile_%s_%p'
current controlfile;
release channel c1;
}
exit;
EOF

RC=$?
if [ $RC != 0 ]
then
mailx -s "$ORACLE_SID@`hostname` RMAN Backup Failed" user@example.com << EOT
Return code $RC.
Successful would have been 0.
EOT

fi

**************************************************************
**************************************************************
**************************************************************

#!/bin/ksh
/bin/date
ORABACKUPSET=/u01/oracle/admin/backupset

$ORACLE_HOME/bin/rman nocatalog << EOF
connect target /

list backup;
EOF

RC=$?
/bin/date

exit

**************************************************************
**************************************************************
**************************************************************

#!/bin/ksh
/bin/date
ORABACKUPSET=/u01/oracle/admin/backupset

$ORACLE_HOME/bin/rman nocatalog << EOF
connect target /

list incarnation;
EOF

RC=$?
/bin/date

exit

**************************************************************
**************************************************************
**************************************************************

#!/bin/ksh
#*************************************************************************************
# Delete expired backups then crosscheck.
#*************************************************************************************
/bin/date
ORABACKUPSET=/u01/oracle/admin/backupset

$ORACLE_HOME/bin/rman nocatalog << EOF
connect target /

delete noprompt expired backup;
crosscheck backup;
EOF

RC=$?
/bin/date

exit

**************************************************************
**************************************************************
**************************************************************

#!/bin/ksh
#*************************************************************************************
# Archived redo and control file and spfile, without recovery catalog.
# DB must be in archivelog mode.
# The log file is not being switched because archive_lag_target handles this task.
# "delete all input" removes archived log files after they have been backed up.
#*************************************************************************************
/bin/date
ORABACKUPSET=/u01/oracle/admin/backupset

$ORACLE_HOME/bin/rman nocatalog << EOF
connect target /

run {
allocate channel c1 type disk;
backup
format '/u01/oracle/admin/backupset/%d.rman_archivelog_%s_%p'
archivelog all
delete all input;
backup
format '/u01/oracle/admin/backupset/%d.rman_controlfile_%s_%p'
current controlfile;
backup
format '/u01/oracle/admin/backupset/%d.rman_spfile_%s_%p'
spfile;
release channel c1;
}
EOF

RC=$?
/bin/date
if [ $RC != 0 ]
then
echo "Return code is $RC. See error log in $ORABACKUPSET." | /usr/bin/mailx -s "`hostname`:$ORACLE_SID RMAN archivelogcontrolfile to disk nocatalog failed" usererrormessage@example.com
fi

exit

**************************************************************
**************************************************************
**************************************************************

#!/bin/ksh
#**********************************************************************
# Database Point In Time Recovery
# /dbteam/code/rmanDBPITR.oracle.ksh
#
# You must know the time to restore until.
# Refer to Oracle 10g Recovery Manager Reference, page 210.
#
# Example:
# - Ensure a good control file is in place. If not, do control file recovery.
# - Restore redo from tape to /u01/oracle/admin/sid/flash_recovery_area/sid/archivelog/.
# - Restore rman backup files from tape to /u01/oracle/admin/backupset.
# - Ensure files are not gzip'd or compressed. (gzip -d /u01/oracle/admin/backupset/*.gz)
# - Ensure database is shut down.
# - Put the proper time in this script in two places. Both times should be the same.
# - Run this script.
# - May need to: ALTER TABLESPACE temp ADD TEMPFILE '/u01/oradata/sid/temp01.dbf' REUSE;
#**********************************************************************
/bin/date

$ORACLE_HOME/bin/rman nocatalog << EOF
connect target /

run {
startup nomount;
alter database mount;
restore database until time "timestamp '2005-04-07 13:16:00.00'";
recover database until time "timestamp '2005-04-07 13:16:00.00'";
alter database open resetlogs;
}
EOF

exit

Oracle Export Through Compressing Pipe

This is an example of using a unix pipe to compress an Oracle export on-the-fly. This is useful when disk space is tight and when there are multiple CPUs.

When the export is running, do a top/prstat and watch how exp and compress both get CPU.

This is from a unix shell. Shell environment and PATHs are configured.

$ mknod mypipe p

$ /usr/bin/compress < mypipe > fullexp.SID.`date +%m%d`.dmp.Z &

$ $ORACLE_HOME/bin/exp userid=/ full=y file=mypipe log=fullexp.SID.`date +%m%d`.log direct=y consistent=n

$ rm mypipe

Sites WIth Great Ad Revenue

Supposedly this dating site makes $10,000 a day in Adsense revenue.

Article on ugly web sites.

Oracle Statspack Install And Configure

Oracle statspack is the replacement to the bstat/estat scripts. Statspack is an Oracle provided tool that takes snapshots of database instance performance and can provide report on the performance.
While there is good documentation on statspack, and at least one thick book on statspack, thick documentation may lead people to believe that it is too complicated to learn.

This quick guide covers a simple install and configuration of Oracle statspack (database version 9.2) on unix. Most DBAs should be able to these instructions and have a useful statspack implementation in less that thirty minutes.


High level instructions:

Create perfstat user in new tablespace.
Install statspack schema from $ORACLE_HOME/rdbms/admin.
Schedule statspack snaps.
Create statspack reports.


Detailed instructions:

Navigate to the statspack install files.
$ cd $ORACLE_HOME/rdbms/admin

$ ls -l sp*
-rw-r--r-- 1 oracle dba 1771 Mar 9 2002 spauto.sql
-rw-r--r-- 1 oracle dba 100932 Apr 17 2002 spcpkg.sql
-rw-r--r-- 1 oracle dba 861 Apr 17 2002 spcreate.sql
-rw-r--r-- 1 oracle dba 47661 Apr 17 2002 spctab.sql
-rw-r--r-- 1 oracle dba 9062 Apr 17 2002 spcusr.sql
-rw-r--r-- 1 oracle dba 84109 Apr 17 2002 spdoc.txt
-rw-r--r-- 1 oracle dba 758 Mar 9 2002 spdrop.sql
-rw-r--r-- 1 oracle dba 4883 Mar 9 2002 spdtab.sql
-rw-r--r-- 1 oracle dba 1363 Mar 9 2002 spdusr.sql
-rw-r--r-- 1 oracle dba 8423 Apr 1 2002 sppurge.sql
-rw-r--r-- 1 oracle dba 137070 Apr 17 2002 sprepins.sql
-rw-r--r-- 1 oracle dba 1284 Mar 9 2002 spreport.sql
-rw-r--r-- 1 oracle dba 27197 Apr 17 2002 sprepsql.sql
-rw-r--r-- 1 oracle dba 2850 Mar 9 2002 sptrunc.sql
-rw-r--r-- 1 oracle dba 588 Mar 9 2002 spuexp.par
-rw-r--r-- 1 oracle dba 30684 Apr 18 2002 spup816.sql
-rw-r--r-- 1 oracle dba 23329 Apr 18 2002 spup817.sql
-rw-r--r-- 1 oracle dba 19129 Apr 18 2002 spup90.sql

Review the installation help file.
$ more spdoc.txt
< snip >
... the minimum space requirement is approximately 100MB.

After locating a suitable disk location, create the tablespace.
$ sqlplus '/ as sysdba'

SQL> CREATE TABLESPACE PERFSTAT LOGGING
DATAFILE '/u109/oradata/DMPRD/perfstat01.dbf' SIZE 100M REUSE AUTOEXTEND
ON NEXT 10M MAXSIZE 5000M
EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO
SQL> /
Tablespace created.

Create the perfstat user. "Perfstat" is hard coded in Oracle's create scripts.
SQL> connect / as sysdba
SQL> @?/rdbms/admin/spcreate
There will be prompts for the perfstat user's password, default tablepace, and temporary tablespace.

Check spool files for errors. Note the password is shown in a spool file.
$ more spc*.lis

Verify instance timed_statistics is set to true. Statspack reports are less useful if this is set to false.
$ sqlplus '/ as sysdba'

SQL> show parameter timed_statistics

NAME TYPE VALUE
------------------------------------ -----------------------------------------
timed_statistics boolean TRUE

Verify job_queue_processes is at least one.
$ sqlplus perfstat


SQL> show parameter job
NAME TYPE VALUE
------------------------------------ -----------------------------------------
job_queue_processes integer 10

Schedule the "snap" job with the PERFSTAT user. By default, this will schedule a level 5 snapshot. Snapping every 15 - 30 minutes is generally appropriate. Longer snap intervals (like an hour) often will not provide enough granular details. Install the job as the perfstat user.

$ sqlplus perfstat
SQL> variable jobNbr number;
begin
dbms_job.submit( :jobNbr, 'statspack.snap;', trunc(sysdate), 'sysdate+1/48');
end;
SQL> 2 3
4 /

PL/SQL procedure successfully completed.

SQL> col priv_user form a11
SQL> col schema_user form a11
SQL> col log_user form a11

SQL> select JOB, NEXT_DATE, NEXT_sec, INTERVAL, WHAT
from user_jobs
where job = :jobNbr
2 3
4 /

JOB NEXT_DATE NEXT_SEC
---------- ------------------- --------
INTERVAL
--------------------------------------------------------------------------------
WHAT
--------------------------------------------------------------------------------
2 2006-03-15 00:00:00 00:00:00
sysdate+1/48
statspack.snap;

Oracle provides an interactive script that allows choosing the instance and time frames for the report.
$ cd $ORACLE_HOME/rdbms/admin
$ sqlplus '/ as sysdba' @spreport.sql

If you decide statspack information is too valuable and might put you out of a job then you may want to remove it. Simply drop the perfstat user.
$ sqlplus '/ as sysdba'
SQL> drop user perfstat cascade;
SQL> drop tablespace perfstat;

Snapshots are stored in the perfstat tablespace, so it will be necessary to remove old snapshots when the tablespace is nearly full. $ORACLE_HOME/rdbms/admin/sppurge.sql is an interactive removal script that prompts the user for a range of snaphot IDs.

==========================================================================
The following instructions may not be appropriate for you environment. Use at your own risk.
==========================================================================



Install custom PL/SQL package that contains purge logic. 10g statspack.purge reduces the needed DELETE SQL.

$ sqlplus perfstat
SQL> create or replace package statspackCustom
as

procedure purge ( p_daysBack in number );
end statspackcustom;
/
show err

create or replace package body statspackCustom as
procedure purge ( p_daysBack in number )
is
minsnap number;
maxsnap number;
snapshots_purged number;
ldbid number;
linst number;
begin

select case when min(snap_id) is null then 0 else min(snap_id) end
into minsnap
from stats$snapshot
where snap_time < sysdate - p_daysBack;

select case when max(snap_id) is null then 0 else max(snap_id) end
into maxsnap
from stats$snapshot
where snap_time < sysdate - p_daysBack;

/* 10g
snapshots_purged := statspack.purge( i_begin_snap => minsnap
, i_end_snap => maxsnap
, i_snap_range => true
, i_extended_purge => false);
*/

/* 9.2, SQL from $ORACLE_HOME/rdbms/admin/sppurge.sql.*/
select dbid
into ldbid
from v$database;
select instance_number
into linst
from v$instance;

delete from stats$snapshot
where instance_number = linst
and dbid = ldbid
and snap_id between minsnap and maxsnap;

delete
from stats$undostat us
where dbid = ldbid
and instance_number = linst
and begin_time < sysdate - p_daysBack;

delete from stats$database_instance di
where instance_number = linst
and dbid = ldbid
and not exists (select 1
from stats$snapshot s
where s.dbid = di.dbid
and s.instance_number = di.instance_number
and s.startup_time = di.startup_time);

delete from stats$statspack_parameter sp
where instance_number = linst
and dbid = ldbid
and not exists (select 1
from stats$snapshot s
where s.dbid = sp.dbid
and s.instance_number = sp.instance_number);

end purge;
end statspackcustom;
/
show err

Schedule the purge to retain the most recent fifteen days.
$ sqlplus perfstat
SQL> col priv_user form a11
SQL> col schema_user form a11
SQL> col log_user form a11

SQL> variable jobNbr number;
begin
dbms_job.submit( :jobNbr, 'statspackCustom.purge(15);', trunc(sysdate), 'trunc(sysdate)+1');
end;
SQL> 2 3
4 /

PL/SQL procedure successfully completed.


Check jobs. Broken or failed jobs should be corrected.
$ sqlplus /
SQL> col priv_user form a11
SQL> col schema_user form a11
SQL> col log_user form a11
1* select what,broken, failures from dba_jobs where schema_user='PERFSTAT'
SQL> /

WHAT
--------------------------------------------------------------------------------
B FAILURES
- ----------
statspackCustom.purge(15);
N 0

statspack.snap;
N 0





...UNDER CONSTRUCTION...

It can be useful to automatically create reports for every snap period and for every day. This is custom code you can develop.

The script that creates the statspack report is in bighost:/export/home/oracle/local/statspackSpreport.ora.ksh.

Place these line in oracle's crontab to schedule the script.
# Report for most recent snap period - not necessarily an hour. Run after snap job completes. 10,40 assumes 30 minute snap starting on hour.
10,40 * * * * WEBDB=/tmp; export WEBDB; ORACLE_HOME=/apps/oracle/product/9.2.0; export ORACLE_HOME; ORACLE_SID=DMPRD; export ORACLE_SID; /bin/umask 002; /export/home/oracle/local/statspackSpreport.ora.ksh $ORACLE_SID MOSTRECENT >> /tmp/$ORACLE_SID.statspackSpreport.mostrecent.out 2>&1

Korn Shell File Name Completion

At the korn shell (ksh) command line, enter the first few characters of a file or directory name, and the shell will complete the name.

In HP-UX ksh, press <esc> <esc>. Or press \.

In Solaris ksh, press <esc> \.