Report with backups - created from Grid Control

Today I wanted to create a report from Enterprise Manager Grid Control 11g. We have about 40 databases here (mixed, 9i/10g/11g). I would like to have a daily report of the last database backup date.

The view SYSMAN.MGMT$HA_BACKUP provides a very usefull report for backups. But there is a drawback it seems.

For 9i databases most columns are null (start_time, end_time, status, output_bytes_display etc.). So there is no usefull information on these databasebackups for 9i database (9.2.0.8 to be precise).

Enterprise Manager 12c overview

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

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

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

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

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

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!

EXPLAIN PLAN FOR SELECT * FROM MYTABLE; SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

clone virtual machine in VirtualBox

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

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

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

Copyright (c) 1982, 2009, Oracle.  All rights reserved.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Click here to read this paper

format columns in awk with printf

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%