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%