Monday, January 29, 2007

Oracle Database Go-Live Check List

Items to review before production go-live of an Oracle database system.

+) Security
accounts (check users with roles: DBA, import any, ...)
listener password
(if non-DBAs can log on to DB machine) check file Oracle directory and permissions
check for default DB accounts that can be locked or have the passwords changed
change OS passwords
change at least DB system password

+) Automate instance/listener start and stop with the operating system startup "rc" scripts.

+) Backup/recovery
verify archive log mode is on
verify rman jobs are scheduled and running correctly
in case backup system is down, ensure a couple days of disk space for archive_log_dest
export schema rows=n weekly or monthly
depending on requirements and performance considerations, enable db_block checksum

+) Performance
configure resource governor to guide CPU usage and parallel query usage
configure resource governor to limit undo usage by non-application owner accounts
schedule statspack snap every twenty or thirty minutes
enable monitoring on application tables
schedule dbms_stats.gather_schema_stats to gather stale (usually weekly or monthly)
use dbms_stats to create and export a statistics table (useful to quickly restore older version of statistics)

+) Checks
tablespace that will not be able to grow
undo tablespace growth is capped at less than the size of the file system
alert log error messages
users with default tablespace of system
operating system error log "syslog"
network error (netstat -i)

+) Multiple temp tablespaces
one tablespace that is only available to application owner
other tablespaces to segregate other users from interfering with application temp usage

Saturday, January 27, 2007

Disk Usage By User

In shared unix filesystems, like /tmp, it is often useful to find who is using the most disk space.

While
du -sk *
will find large files,
find . -user mc -type f -exec du -k {} \; | awk '{ s = s+$1 } END { print "Total used: ",s }'
will show which user is consuming the most space.

IBM has an article describing this combination of find, du, and awk.

Monday, January 15, 2007

Locating Perl Modules

If perl modules are installed and perl is complaining of not being able to find the modules, try one of these:


1) create environment PERL5LIB, which works similar to the PATH environment variable


2) in the perl code, add:
BEGIN {unshift @INC, "/home/user/perl/modules/lib"};


3) in the perl code, add:
use lib '/home/user/perl/modules/lib';

Saturday, January 13, 2007

Oracle User Logon Trigger

It can be useful to set session settings for a user in a logon trigger. A user may want to override the setting for a specific parameter such as star_transformation_enabled, or it may be necessary to turn on sql_trace for a user.

create or replace trigger logontrigger after logon on schema
declare
p_session_user varchar2(30);
p_host varchar2(200);
p_sql varchar2(230);
p_ip_address varchar2(30);
p_sessionid number(30);
begin
select translate(sys_context('userenv', 'host'), '-\ /', '____') into p_host from dual;
select sys_context('userenv', 'session_user') into p_session_user from dual;
select sys_context('userenv', 'sessionid') into p_sessionid from dual;
select sys_context('userenv', 'ip_address') into p_ip_address from dual;

-- Create trace file with a recognizable name.
p_sql:='alter session set tracefile_identifier=' || p_session_user || '_' || p_host;
execute immediate p_sql;

-- Choose one of the following trace levels. The second form provides bind variables and the result set.
p_sql:='alter session set sql_trace=true';
--select 'alter session set events ' || '''10046 trace name context forever, level 12''' into p_sql from dual;
execute immediate p_sql;

end;
/

show err
prompt
prompt Remember the user needs 'alter session' privilege.
prompt To remove: drop trigger logontrigger
After creating the logon trigger, log on as the user. If it does not generate a file in the user jump destination, then check the alert.log for errors.

Tuesday, January 09, 2007

Oracle Data Migration Via SQL Script

Data changes are often promoted to production environments via scripts that are stored in a change control system.

If using a SQL script to apply data changes, these are useful to include:

-- The output file name. DO NOT include a path nor a drive letter.
spool outputFileName

prompt Include info here about defect tracking number, developer, etc.

-- On error rollback and exit. This is generally only needed for scripts that will be scheduled. For an ad-hoc migration, this is not needed.
whenever sqlerror exit failure rollback
whenever oserror exit failure rollback

-- Set the date format to avoid relying on defaults that are platform-specific.
alter session set nls_date_format='MM/DD/YYYY';

-- set echo on will print the SQL to the screen. This is optional and may not be useful if there is too much output.
set echo on

-- set define off will disable interpreting of the ampersand character.
set define off

-- Select the current time to show when script was implemented.
select systimestamp from dual;

-- set timing on will print elapsed time of each command.
set timing on

-- place script contents here.
UPDATE tablename set columna=value1;

-- spool off will stop logging to the output file.
spool off


For change control purposes, the migrations to production may run via sqlplus. Be aware that sqlplus has a limit to the number of lines per SQL, while tools such as Benthic appear to not have a reasonable limit to the number of lines per SQL. What may be encountered is a developer testing a script via Benthic with 5000 or more lines per statement (typically a large IN list) that will not run via sqlplus. The error message will be:
ERROR at line 565:
ORA-00933: SQL command not properly ended

Wednesday, January 03, 2007

Become Another Oracle User

This technique is useful to log in as another user to troubleshoot a problem they may be having. Oracle DBA privileges are needed.

Open two windows.

In the first window:
select password from dba_users where username='THEUSER'
alter user THEUSER identified by pass

In the second window:
sqlplus THEUSER/pass

In the first window:
alter user THEUSER identified by values 'password values from first query'


In the second window you will be logged on as the user. If the password can be set and reset quickly, this can be used for troubleshooting on a live production system.

Tuesday, January 02, 2007