Webgeest DBA blog

Enterprise Manager 12c overview

2011-12-11T14:09:00.001+01:00

I've been to a demo-day in Hilversum. Really cool stuff.
This movie will give a short overview of EM12c:

Flashback package source in Oracle 9.2

2011-09-22T08:45:00.001+02:00

A developer came to me that he messed up his package source he was working on.

He asked me if we could retrieve it from backup, preferable from an hour ago. This is typical a flashback scenario, but he was working on 9.2. Is this a problem? No, we can use Flashback Query in 9.2. We just have to query the dba_source view.

The database (development) is not running in archivemode, so we can flash back to any point within the redo logs. This time we flash back to about half an hour ago.

The output is saved in a spool file.

ALTER SESSION
SET NLS_TIMESTAMP_FORMAT =
'DD-MON-YYYY HH24:MI:SS.FF3';

set lines 9999

set trimspool on

set pages 9999

set heading off

spool old_version.sql

select text from dba_source

as of timestamp to_timestamp('22-SEP-2011 07:30:00.000')

where owner='SCOTT'

and name='MYPACKAGE'

order by type, line;

spool off

Secure Files

2011-08-23T10:08:00.000+02:00

Today I have been playing with Secure files. Arup Nanda has an excellent article on this:

Oracle Database 11g: The Top New Features for DBAs and Developers | SecureFiles

Some notes on installing 10.2.0.4 on SLES 11 64bits

2011-07-02T06:06:00.001+02:00

When installing this unsupported combination, some solutions might be:

- Change the /etc/SuSe-release: backup the original file and change it to SuSe 9.
- error invoking target of makefile ins_ctx.mk --> install gcc43-32bit
- error on ins_sysman.mk --> unset LD_LIBRARY_PATH
- error on ins_emdb.mk -->ignore this error
Much more can be found on http://www.puschitz.com/InstallingOracle10g.shtml and MOS (My Oracle Support).

Move tables, indexes and lobs to another tablespace

2011-06-27T10:45:00.007+02:00

On some occasions, I have been using a script from Tom Kyte to move all objects from one tablespace to another. This script also gets all the indexes of the tables that are being moved. After moving the table, you should rebuild the index.
The script has been slightly adapted by me, but the main code is by Tom.

We do need to purge the recyclebin, otherwise you will end up with moving BIN$ objects in your script. What remains sometimes, are the lob segments. These can be moved with:

alter table owner.table move lob(columnname) store as (tablespace newtablespace);

This has been added in a second part of the query. So the total scripting is like below. You may call it with @moveall USERS USERS2 if you want everything to move to the USERS2 tablespace. Note that it only creates a script, the actual moving has to be done and can take quite a while.

set echo off
prompt Enter a value for the OLD tablespace:
define old_tablespace=&1
prompt Enter a value for the NEW tablespace:
define new_tablespace=&2

column order_col1 noprint
column order_col2 noprint
set heading off verify off feedback off echo off
set pages 0 lines 9999 trimspool on
purge dba_recyclebin;

spool tmp.sql

select decode( segment_type, 'TABLE', segment_name, table_name ) order_col1,
decode( segment_type, 'TABLE', 1, 2 ) order_col2,
'alter ' || segment_type || ' ' || owner || '.' || segment_name || decode( segment_type, 'TABLE', ' move ', ' rebuild ' ) ||
' tablespace &&new_tablespace ' || ';'
from dba_segments,
(select table_name, index_name from user_indexes )
where segment_type in ( 'TABLE', 'INDEX' )
and segment_name = index_name (+)
and tablespace_name = '&&old_tablespace'
order by owner, order_col1, order_col2
/

select 'alter table ' || owner || '.' || table_name ||
' move lob(' || column_name || ') store as (tablespace &&new_tablespace);'
from dba_lobs where TABLESPACE_NAME='&&old_tablespace'
/

spool off

set heading on verify on feedback on echo on

REM Now you can:
REM 1. Backup your database
REM 2. Start it restricted if possible
REM 3. Run the generated script

Use it with care, make sure you have a database backup and start up the database in restricted mode if possible.

After the move, check for unusable indexes and invalid objects.

Export large database without using much diskspace

2011-03-06T07:58:00.008+01:00

I have one pretty large database running in one of our development environments. I was wondering if this Oracle 8i database could be easily exported. I didn't have much space for export files, so I used compression during export with a script a found at http://www.tc.umn.edu/~hause011/code/exp-imp-db.ksh.

How much compression would it give me and how long would it take?

Here are some results:

Database 8.1.7.4 on AIX 5.
Export running 40 hours
database size: 236 Gb
export dumpfile (gzip) 16Gb

Import would take considerably longer, because indexes would need te be created. I would guess around a week. The export time will probably not have been much shorter without compression.

explain plan easy!

2011-02-09T06:18:00.001+01:00

EXPLAIN PLAN FOR SELECT * FROM MYTABLE;

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

clone virtual machine in VirtualBox

2010-12-23T08:35:00.000+01:00

Goal of this procedure:
Source: machine called EntLnx10 with disk EntLnx_10.vdi
Target: machine called EntLnx09 with disk EntLnx_09.vdi

Here is what we do:

start up Virtualbox program.
Go to File > Virtual Media Manager (or press Ctrl-D)
Select the EntLnx_10.vdi and click Release button.
Select the EntLnx_10.vdi and click Remove button.
Select the Keep button (!!)

Now start a commandline and give the command (also see example below):

[path]\VBoxManage.exe clonehd [path]\sourcedisk.vdi [path]\targetdisk.vdi

This starts copying the disk, which might take a while.

D:\Virtual Machines>"c:\Program Files\Oracle\VirtualBox\VBoxManage.exe" clonehd "d:\Virtual Machines\EntLnx_10.vdi" "d:\Virtual Machines\EntLnx_09.vdi"
Oracle VM VirtualBox Command Line Management Interface Version 3.2.12
(C) 2005-2010 Oracle Corporation
All rights reserved.

0%...10%...20%...30%...40%...50%...60%...70%...80%...90%...100%
Clone hard disk created in format 'VDI'. UUID: 65a30476-0a44-4dea-aee8-9e0e95a12e7c

Now add the new disk to Virtualbox:

Go to File > Virtual Media Manager (or press Ctrl-D)
Click Add and select the new vdi file. Click Ok.

Now create a new virtual machine, and select in this process the 'existing' new vdi file as harddisk.

Start up the EntLnx09 machine.

Good luck!

Getting the NLS_LANG right for exp and imp

2010-12-06T06:41:00.001+01:00

If you use the old-fashioned imp en exp tools, you really need to set the NLS_LANG environment variable right. To what value? It can be read from the v$nls_parameters. To make life easier, I always keep this set_nls.sql script available. It creates just the right (Linux) statement to make the setting.

set heading off
set feedback off
select 'export NLS_LANG=' || lan.value || '_' || ter.value || '.' || chr.value
  from v$nls_parameters lan,
   v$nls_parameters ter,
   v$nls_parameters chr
  where lan.parameter='NLS_LANGUAGE'
   and ter.parameter='NLS_TERRITORY'
   and chr.parameter='NLS_CHARACTERSET';
set heading on
set feedback on

This gives me for example:

export NLS_LANG=AMERICAN_AMERICA.WE8ISO8859P1

Just copy it in your shell environment and fire up the imp or exp!

My favorite alias

2010-11-21T12:06:00.001+01:00

Aliasses are most useful tiny little means of making life easy on Linux.
One of my favorites is 'dba'

alias dba='sqlplus "/ as sysdba" @/home/oracle/scripts/login.sql'

with the login.sql:

set sqlprompt "_USER'@'_CONNECT_IDENTIFIER _PRIVILEGE> "

This makes login most simple by typing dba and it looks like:

oracle@server01:~> dba

SQL*Plus: Release 11.2.0.1.0 Production on Sun Nov 21 12:04:22 2010

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production

With the Partitioning, Oracle Label Security, OLAP, Data Mining,

Oracle Database Vault and Real Application Testing options

SYS@SERVER01 AS SYSDBA>

What is your favorite alias?

Hands-on GoldenGate

2010-11-19T06:26:00.000+01:00

This is a very fine article hands-on for trying GoldenGate:
http://www.pythian.com/news/7959/oracle-goldengate-installation-part-1/

Application Deployment targets showing metric collection errors

2010-11-11T09:01:00.003+01:00

On Grid Control 11.1.0.1.0, running on Weblogic 10.3.2 I had some errors on

/secFarm_GCDomain/GCDomain/EMGC_OMS1/emgc
/secFarm_GCDomain/GCDomain/EMGC_OMS1/OCMRepeater

'Application Deployment' targets, showing metric collection errors on the Grid Control.

For database targets, this is easy to solve by entering the right dbsnmp password :
Targets - Configure

Now the errors on 'Application Deployment' targets are also easy. Just go to Targets , select the target and click configure. Now there are no configurable properties, no passwords or such, but just click OK button.

After a few seconds (almost instantly) the target is shown 'Up' in Grid Control.

How easy can it be?

Oracle 9R2

2010-11-10T09:23:00.000+01:00

I thought I would NEVER install Oracle 9R2 again. But our sysadmin wanted to get rid of this old SLES 9 machine, so we reinstalled on SLES10.
Never say never again!

Moving datafiles – almost online

2010-11-08T11:33:00.057+01:00

In this post I will show a method to move datafiles with minimal downtime. A testcase moves an 8Gb datafile with 5 seconds downtime.

If you need to move a datafile in Oracle, but the database or even tablespace cannot be taken offline, you have a hard job.

I came accros the following post, and decided to make a nice script around it. The script will put the tablespace in backup mode, copy it to the new location, end the backup mode. After that,

take the datafile offline, rename it in the database, recover it and put it online.

Here is my script (move_online.sql):

-- Script to move a datafile (almost) online.
-- Do not use easily, there is a small downtime
--
-- You can minimize downtime by following these steps:
-- 1) alter tablespace <TS name> begin backup;
-- 2) HOST cp <old name> <new name>;
-- 3) alter tablespace <TS name> end backup;
-- 4) alter database datafile <old name> offline;
-- 5) alter database rename file <old name> to <new name>;
-- 6) recover datafile <new name>;
-- 7) alter database datafile <new name> online;

WHENEVER SQLERROR EXIT

-- Entry Section
--

set heading off

set feedback off
set verify off
define TSNAME = &TablespaceName

prompt

prompt Existing files, pick a low-active file if possible
--
-- Display filestatistics, pick a low-active file if possible
--

col file_name format a40

col tablespace_name format a12 heading Tablespace

set heading on set lines 120 set pages 999 select

   f.file_name,
   f.tablespace_name,
   s.phyrds,
   s.phywrts
from v$filestat s,
   dba_data_files f
where f.tablespace_name='&&TSNAME'
and f.file_id=s.file#
;

set heading off

prompt
prompt Enter the old file without quotes:
define OLDFILE = &Old_Filename
prompt
prompt Enter the new file without quotes:
define NEWFILE = &New_Filename

--
-- Processing
--
prompt Set backup status...
alter tablespace &&TSNAME begin backup;

prompt Copying file...
host cp &&OLDFILE &&NEWFILE

prompt End backup status...
alter tablespace &&TSNAME end backup;

prompt Set file offline...
alter database datafile '&&OLDFILE' offline;

prompt Rename file in database...
alter database rename file '&&OLDFILE' to '&&NEWFILE';

prompt Recover datafile...

recover datafile '&&NEWFILE';

prompt Set file online...

alter database datafile '&&NEWFILE' online;

-- Cleanup
--
prompt Rename old file...
host mv &&OLDFILE &&OLDFILE._`date +"%Y%m%d_%T"`.old

-- Display Status
--
prompt Datafiles with wrong status:
set feedback on
select name, b.status from v$backup b, v$datafile f

where f.file#=b.file# and b.status !='NOT ACTIVE'

;

prompt All actions are logged in alert logging.

prompt

prompt Ready!

Warning:

There will be short period of unavailability of the datafile.

The output looks as follows on my test database (Suse Linux), I marked my input

strings:

SQL> @move_online.sql

Enter value for tablespacename: USERS

Existing files, pick a low-active file if possible

FILE_NAME Tablespace PHYRDS PHYWRTS

---------------------------------- ------------ ------- -------
/u04/oradata/DBATST01/users01.dbf USERS 446 413
/u04/oradata/DBATST01/users02.dbf USERS 6 2

Enter the old file without quotes:

Enter value for old_filename: /u04/oradata/DBATST01/users02.dbf

Enter the new file without quotes:

Enter value for new_filename: /u01/oradata/DBATST01/users02.dbf

Set backup status...

Copying file...
End backup status...
Set file offline...
Rename file in database...
Recover datafile...
Media recovery complete.
Set file online...
Rename old file...

Datafiles with wrong status:

no rows selected

All actions are logged in alert logging.

Ready!

Logging

If we take a look at the alert logging, we can see the timing of this:

alter tablespace USERS begin backup

Mon Nov 8 10:43:28 2010
Completed: alter tablespace USERS begin backup
Mon Nov 8 10:43:28 2010
alter tablespace USERS end backup
Completed: alter tablespace USERS end backup
Mon Nov 8 10:43:28 2010
alter database datafile

'/u04/oradata/DBATST01/users02.dbf' offline

Completed: alter database datafile

'/u04/oradata/DBATST01/users02.dbf' offline

Mon Nov 8 10:43:28 2010
alter database rename file '/u04/oradata/DBATST01/users02.dbf' to '/u01/oradata/DBATST01/users02.dbf'
Starting control autobackup
Control autobackup written to DISK device handle '/backup/mdoradb01/DBATST01c-4185353572-20101108-13'
Completed: alter database rename file '/u04/oradata/DBATST01/users02.dbf' to '/u01/oradata/DBATST01/users02.dbf'
Mon Nov 8 10:43:29 2010
ALTER DATABASE RECOVER datafile '/u01/oradata/DBATST01/users02.dbf'
Media Recovery Start
Mon Nov 8 10:43:29 2010
Recovery of Online Redo Log: Thread 1 Group 2 Seq 41

Reading mem 0 Mem# 0: /u04/oradata/DBATST01/redo02.log

Mon Nov 8 10:43:29 2010
Media Recovery Complete (DBATST01)
Completed: ALTER DATABASE RECOVER datafile '/u01/oradata/DBATST01/users02.dbf'
Mon Nov 8 10:43:32 2010
alter database datafile '/u01/oradata/DBATST01/users02.dbf' online
Starting control autobackup
Control autobackup written to DISK device handle '/backup/mdoradb01/DBATST01c-4185353572-20101108-14'
Completed: alter database datafile '/u01/oradata/DBATST01/users02.dbf' online

We can see, there is about four seconds offline status of the datafile. Now this is a 11Mb datafile. Another testcase moving a 8.2Gb file, took about 6 minutes to copy but we had online 5 seconds(!) offline status.

Note that the old file is renamed. You should remove it manually afterwards if all went well.

Conclusion

Conclusion of this method is, that we can move datafiles with a minimal offline duration provided that the database is running in archivelog. You have to be aware of the risks. If you have jobs running or queries against the datafile when it is offline, they probably fail.

Furthermore, if you have many updates and inserts on the tablespace while the copy is running, you might end up with a lot of redo logging.

Using a file that is not much used (as can be seen from the filestats), and moving the file outside office hours might reduce these risks.

truncate table speed

2010-09-09T11:42:00.000+02:00

When you delete many rows with DELETE FROM, you generate a lot of redo and undo. It may even fail, if your undo runs out of space or your archive location is insufficient.

If you need to empty a full table, it is a poor decision to use DELETE. In that case, use TRUNCATE. It is far more efficient and quicker.

You know it should be quick, but who has the opportunity to test it on a big table?
I had just over 100.000.000 rows in a audit table that had to be cleaned.

Let's see the results (schema and table name have been changed):

SQL> select count(1) from SCOTT.AUDIT ;

COUNT(1)
----------
101622759

Elapsed: 00:02:02.38

SQL> truncate table SCOTT.AUDIT drop storage;

Table truncated.

Elapsed: 00:00:48.69

48 seconds for 101 million rows. Not bad! Drop storage is default BTW.
Times depend on Operating system. This is a pretty quick Linux server.

Tiny

2010-07-30T10:30:00.000+02:00

Are you always looking for Oracle documentation?
It can be found on http://tinyurl.com/oradoc.

For some reason it was never linked on tinyurl.

Have fun!

Password expired in 11gR2

2010-07-29T08:44:00.002+02:00

On most of our 11gR2 databases that we have been using over the last months, the passwords start expiring recently.
I noticed that the default password expiry time is 180 days on the DEFAULT profile, so:

ALTER PROFILE "DEFAULT" LIMIT PASSWORD_LIFE_TIME UNLIMITED;

Or, you can create a profile for your application-owners with unlimited password life time - it's up to you.

Note: this is also a problem on your DBSNMP user in your EMREP database when you use Grid Control with a 11gR2 database for EMREP.

11g Grid Control running on Enterprise Linux - Virtualbox

2010-07-29T07:56:00.002+02:00

I read this fine paper by Martin Decker on installing the 11g Grid Control.
It can be pretty easily done with Virtualbox and Oracle Enterprise Linux.

You should really try for yourself, especially those who have never worked on 11g Grid. Main difference is that you first install Weblogic application server, then a database (repository) and then the Grid Control. Have fun...!

Last changed user and date in apex

2010-06-17T09:45:00.000+02:00

I didn't know I could pass the APP_USER from APEX into a databasetrigger!
The nvl construction saves me from empty fields when updating on backend.

create or replace trigger TRG_BIU
before insert or update on TBL_DEMO
for each row
begin
:new.DB_LAST_CHANGED_DATE := sysdate();
:new.DB_LAST_CHANGED_BY := nvl(v('APP_USER'),USER);
end;

Finally got LDAP authentication working in PL/SQL

2010-05-21T21:55:00.000+02:00

For a project in APEX, I wanted to use LDAP (Active Directory) authentication. It took me a few hours to got it working. The only thing I needed is to change the short 'windows' name into the CN name.

With the help of this great example:
http://idevelopment.info/data/Oracle/DBA_tips/LDAP_OID_9.2.0/LDAP_21.shtml
I fixed it.

The example shows how to login to LDAP and get values from it.

Grid Control 11

2010-04-22T21:46:00.001+02:00

Hi,
The new Grid Control 11.1.0.1.0 is now on OTN available. For Linux only.
So start downloading and test it!

Oracle Database 11.2 for windows release

2010-04-03T10:32:00.000+02:00

The 11.2 release for windows x64 has now been released on OTN. We'll have to wait for 32 bits.

Columns showing active databases

2010-03-03T09:56:00.007+01:00

When I logon to a server with a large number of databases on it, I like to see what instances are active. When the list is growing, a simple grep on e.g. pmon processes would not give a nice list. Columns can be created with the pr command:

ls /usr/bin | pr -T4 -W$

The screenwidth in a script can be found with the $(tput cols) variable or - outside a script - with the $COLUMNS variable. Putting it all together gives me the following, which can be sourced in .bash_profile if you like. Nice thing is, that the number of columns is variable and will change depending on your screenwidth.

# Determine number of columns to use
MINCOLWD=10 # minimum column width
SCRNCHAR=$(( $(tput cols) )) # screen width
NROFCOLS=$(( $SCRNCHAR / $MINCOLWD )) # number of columns

ps -ef | grep pmon | grep -v 'grep pmon' | awk '{ print substr($8,10,10) }' | sort | pr -T${NROFCOLS} -W${SCRNCHAR} > list_db.tmp
cat list_db.tmp
#
# Determine number of active databases
NRACTDB=`ps -ef | grep pmon | grep -v 'grep pmon' | wc -l`
echo \>\> Total: $NRACTDB databases active

The output looks like this:

ABCONT61 COVUG02 DPSONT ESYUNTD1 LNXBTR01
ABCONT62 CRILS30T DPSTST GFKONT LNXTTO
BRFP01 CRILS61 ECWCTST HBJSONT OAADB
BKDWHO CRILS61T ECWPTST INOKDS01 SQWWUR
BKDWHT CRILS62
>> Total: 26 databases active

Managing Statistics

2010-02-18T10:37:00.003+01:00

Read a good paper on statistics today, by Karen Morton:

Click here to read this paper

format columns in awk with printf

2010-02-17T12:03:00.001+01:00

I was struggling with awk to display the diskspace on my Linux host in the most convenient way. One problem was that a df command splits the output on two lines, but this is fixed by df -P.

Formatting the output with awk required using printf instead of print. With printf you can give formatting to the columns. I came on the following which is seems very usefull in my case.

oracle@myserver:~> df -Ph | awk '{ printf "%-12s%8s\n", $6 , $5 }'

Mounted Use%

/ 9%

/dev 1%

/boot 10%

/home 4%

/opt 31%

/tmp 20%

/usr 52%

/var 11%

/data 15%

/backup 83%

Tablespace point in time recovery

2009-12-02T11:46:00.004+01:00

I'm glad it was just a test for this TSPITR....

RMAN> run

2> { recover tablespace USERS

3> until logseq 23 thread 1

4> auxiliary destination '/u04/oradata/';

5> }

Starting recover at 02-DEC-09

using target database control file instead of recovery catalog

allocated channel: ORA_DISK_1

channel ORA_DISK_1: sid=146 devtype=DISK

RMAN-00571: ===========================================================

RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============

RMAN-00571: ===========================================================

RMAN-03002: failure of recover command at 12/02/2009 11:41:25

RMAN-06455: point-in-time tablespace recovery requires Enterprise Edition

Ooops.... running SE.

Kill all processes - the rough way

2009-11-24T11:55:00.001+01:00

On certain occasions, it might be needed to kill several hunderds of processes on a linux machine. Of course you should NOT do this on a production site, but the use of xargs might be handy:

ps -ef | grep oracle | grep -v bash | awk '{ print $2}' | xargs kill -9

With the grep -v you exclude your own putty session.

Sqlplus connect on HP-UX

2009-11-24T09:41:00.000+01:00

I had a problem connecting with a script on HP-UX. Somehow it felt like I had seen it earlier....

sqlplus myuser/mypassword@mydb

The HP-UX does not like the @ sign. You'll need to escape it:

sqlplus myuser/mypassword\@mydb

This solved my problem.

Correct NLS_LANG for exports

2009-04-07T14:44:00.000+02:00

It's important to set the right NLS_LANG environment variable when doing exports/imports.
The following query makes it easy getting the syntax and values right.

set heading off
set feedback off
select 'export NLS_LANG=' || lan.value || '_' || ter.value || '.' || chr.value
from v$nls_parameters lan,
v$nls_parameters ter,
v$nls_parameters chr
where lan.parameter='NLS_LANGUAGE'
and ter.parameter='NLS_TERRITORY'
and chr.parameter='NLS_CHARACTERSET';
set heading on
set feedback on

This gives the line (e.g.)
export NLS_LANG=AMERICAN_AMERICA.WE8ISO8859P15

which can easily be copied before the imp/exp is run.
For windows environments, you would need to replace 'export' by 'set' in the query.

Enjoy!

determine the Oracle version in a nutshell

2009-03-13T07:27:00.001+01:00

On a host with multiple Oracle versions in multiple Oracle homes, it might be prove hard to quickly determine the Oracle version in a shell script. I came across the following method and like to share it.

First, grep the line from /etc/oratab which contains the ORACLE_SID at the beginning of that line:

grep -i ^$ORACLE_SID: /etc/oratab

Then cut everything behind the semicolon away:

cut -f2 -d:

this gives you the Oracle home path.

Now cut out everything behind the first dot:

cut -f1 -d.

Taking the basename, will remove the path and leave the Oracle version.
In one statement it will become:

VERSION=$( basename $(grep -i ^$ORACLE_SID: /etc/oratab | cut -f2 -d: | cut -f1 -d. ) )

I know that there are different ways. Suggestions welcome!

Index montoring

2009-03-10T07:46:00.000+01:00

You easily forget about all the options you have. I came across a situation, where 39 indexes where created on a single table. This extreme number was grown over the years. There was no administration on which indexes were needed for what purpose. Good luck!

Luckily, you have index monitoring that can be turned on.

alter index MY_IDX_1 monitoring usage;

If you wait for a considerable amount of time, you will see that v$object_usage will contain information on whether the index was used:

select index_name,monitoring,used,start_monitoring,end_monitoring
from v$object_usage;

Note that it is important to log on as the index owner and not as sysdba to see any information in this view. The USED column will probably (maybe) switch from NO to YES if the index is being used.

If you turn off the monitoring:

alter index MY_IDX_1 nomonitoring usage;

the END_MONITORING date field will be filled.

You should not turn off monitoring too soon, there might be monthly reports or hardly used screens that incidentally need the index.

There is a drawback on this. During statististics the index will be used. This will blurr your results. A solution might be to lock statistics during monitoring, using

exec dbms_stats.lock_table_stats(ownname=>'SCOTT',tabname=>'DEPT');

but on heavily changing tables locking statistics might be no such a good idea.

hidden parameter checking

2009-03-02T11:22:00.000+01:00

You might want to check the value of hidden parameters in Oracle.... Here's how to do this. You can enter a (part of) the parametername (in lowercase) to find what you're looking for.

set pages 8888
set lines 120
select a.ksppinm name,
b.ksppstvl value,
b.ksppstdf deflt,
decode
(a.ksppity, 1,
'boolean', 2,
'string', 3,
'number', 4,
'file', a.ksppity) type,
a.ksppdesc description
from
sys.x$ksppi a,
sys.x$ksppcv b
where
a.indx = b.indx
and
a.ksppinm like '\_%&Part_Of_Parametername%' escape '\'
order by
name
;

Always working on backups....

2009-02-26T19:12:00.000+01:00

Today I tried to improve a backup. On a host with a large number of databases running, which do change now and then, you would like that your backup more or less manages itself. So it would have to pick up all running databases, and backup these.

The backup script is stored in a RMAN catalog. The script picks up all databases on a (linux) system and selects them on the running pmon process:

ps -ef | grep pmon | grep -v 'grep pmon' | awk '{print "backupdb " substr($8,10,8) ";"}'

With the awk command, you extract the instance name from the grep output. The result of the grep is put into a tmpwrk.lst file, and looks likes this:

backupdb TEST01;
backupdb PROD01;
backupdb PROD02;
etc.

The total script I use is the following:

#!/bin/sh
#
# Script for backup all databases on the server
#
export ORACLE_BASE=/opt/oracle
export ORACLE_HOME=$ORACLE_BASE/product/10.2.0
export PATH=$ORACLE_HOME/bin:/usr/local/bin:/usr/local/sbin:$PATH

export WRKDIR=/home/oracle/scripts
export LOGDIR=/backup/$(hostname)
export JOBLOG=$LOGDIR/backup_$(hostname).log
echo "Starting backup $(hostname) databases at $(date)" > $JOBLOG

backupdb()
{
if [ -z $1 ]
then
echo "No parameters passed to function."
return 0
else
export ORACLE_SID=$1
rman target / catalog rman/rmanpswd@RMANCAT CMDFILE=$WRKDIR/rman_backup.rcv LOG=$LOGDIR/$ORACLE_SID.backup.log;
FOUTEN=$(grep 'ERROR MESSAGE' $LOGDIR/$ORACLE_SID.backup.log | wc -l);
if [ $FOUTEN -ne 0 ]
then
cat LOG=$LOGDIR/$ORACLE_SID.backup.log | nail -s "BACKUP FAILED $ORACLE_SID" mailme@myisp.com;
echo "$(date) : backup $ORACLE_SID FAILED!!!" >> $JOBLOG
else
echo "$(date) : backup $ORACLE_SID completed" >> $JOBLOG
fi
fi
}

#-----------------------------------------
# MAIN
#-----------------------------------------

#--
# Make list of all active databases
# and create tmpbck.lst file
#---------
ps -ef | grep pmon | grep -v 'grep pmon' | awk '{print "backupdb " substr($8,10,8) ";"}' > $WRKDIR/tmpbck.lst

#--
# run database cold backup for this list
#---------
source $WRKDIR/tmpbck.lst

echo "Backup $(hostname) finished at $(date)" >> $JOBLOG

exit