Database administrator workshop

Installation of Kerberos 5 on linux and Oracle authentication on 11gR2

2011-01-05T16:44:00.006+01:00

For more information about Kerberos just read the MIT documentation about the terminology used. For Oracle authentcation just read the Oracle Advanced Security Administrator's Guide.

First of all as oracle user you have to check if you installed your Oracle Database Server with Oracle Advanced Security option. Type


[oracle@plutone ~]$ adapters

and you should see something like


...
Installed Oracle Advanced Security options are:
...
Kerberos v5 authentication
...

Go to the MIT website and download the latest available stable realease of Kerberos.
http://mit.edu/kerberos/

For me it was krb5-1.9.
I've copied the krb5-1.9-signed.tar file on my directory /u04/kerberos5 and then typed the following commands:


[root@plutone /root]# cd /u04/kerberos5
[root@plutone kerberos5]# tar xvf krb5-1.9-signed.tar
[root@plutone kerberos5]# tar xvfz krb5-1.9.tar.gz
[root@plutone kerberos5]# cd krb5-1.9/src
[root@plutone src]# pwd
/u04/kerberos5/krb5-1.9/src

Then you have to compile the kerberos files using the usual commands:


[root@plutone src]# ./configure 
[root@plutone src]# make 
[root@plutone src]# make install

Now it's time to configure one of the two Kerberos configuration files. To the default conf file I've modified only my hostname (plutone)


[root@plutone src]# vi /etc/krb5.conf
[logging]
 default = FILE:/var/log/krb5libs.log
 kdc = FILE:/var/log/krb5kdc.log
 admin_server = FILE:/var/log/kadmind.log

[libdefaults]
 default_realm = EXAMPLE.COM
 dns_lookup_realm = false
 dns_lookup_kdc = false
 ticket_lifetime = 24h
 forwardable = yes

[realms]
 EXAMPLE.COM = {
  kdc = plutone:88
  admin_server = plutone:749
  default_domain = example.com
 }

[domain_realm]
 .example.com = EXAMPLE.COM
 example.com = EXAMPLE.COM

[appdefaults]
 pam = {
   debug = false
   ticket_lifetime = 36000
   renew_lifetime = 36000
   forwardable = true
   krb4_convert = false
 }

Then you should modify (or create) the second kerberos configuration file, I mean kdc.conf:


[root@plutone src]# cd /usr/local/var/krb5kdc/
[root@plutone krb5kdc]# ll
total 0
[root@plutone krb5kdc]# vi kdc.conf 
[kdcdefaults]
   kdc_ports = 750,88

[realms]
   EXAMPLE.COM = {
      kdc_ports = 750,88
      database_name = /usr/local/var/krb5kdc/principal
      admin_keytab = FILE:/usr/local/var/krb5kdc/kadm5.keytab
      acl_file = /usr/local/var/krb5kdc/kadm5.acl
      key_stash_file = /usr/local/var/krb5kdc/.k5.KERB.IT
      max_life = 10h 0m 0s
      max_renewable_life = 7d 0h 0m 0s
   }

Let's create the kerberos database for the Key Distribution Centers (KDCs) . The following command created five files.


[root@plutone krb5kdc]# /usr/local/sbin/kdb5_util create -r EXAMPLE.COM -s
Loading random data
Initializing database '/usr/local/var/krb5kdc/principal' for realm 'EXAMPLE.COM',
master key name 'K/M@EXAMPLE.COM'
You will be prompted for the database Master Password.
It is important that you NOT FORGET this password.
Enter KDC database master key:    
Re-enter KDC database master key to verify: 
[root@plutone krb5kdc]# ls -la
total 32
drwxr-xr-x 2 root root 4096 Jan  5 14:30 .
drwxr-xr-x 3 root root 4096 Jan  5 13:06 ..
-rw------- 1 root root   72 Jan  5 14:30 .k5.KERB.IT
-rw-r--r-- 1 root root  386 Jan  5 14:28 kdc.conf
-rw------- 1 root root 8192 Jan  5 14:30 principal
-rw------- 1 root root 8192 Jan  5 14:30 principal.kadm5
-rw------- 1 root root    0 Jan  5 14:30 principal.kadm5.lock
-rw------- 1 root root    0 Jan  5 14:30 principal.ok

Now let's create the acl file


[root@plutone krb5kdc]# echo "*/plutone@EXAMPLE.COM * *" > kadm5.acl        
[root@plutone krb5kdc]# more kadm5.acl                            
*/plutone@EXAMPLE.COM * *

and create the principal administrator of the kerberos database


[root@plutone krb5kdc]# /usr/local/sbin/kadmin.local 
Authenticating as principal root/plutone@EXAMPLE.COM with password.
kadmin.local:  addprinc admin/plutone@EXAMPLE.COM
WARNING: no policy specified for admin/plutone@EXAMPLE.COM; defaulting to no policy
Enter password for principal "admin/plutone@EXAMPLE.COM": 
Re-enter password for principal "admin/plutone@EXAMPLE.COM": 
Principal "admin/plutone@EXAMPLE.COM" created.
kadmin.local:  quit

Now let's start the Kerberos daemons:


[root@plutone krb5kdc]# /usr/local/sbin/krb5kdc 
[root@plutone krb5kdc]# /usr/local/sbin/kadmind

and see if there are any errors on log files


[root@plutone ~]# tail -f /var/log/kadmind.log
Jan 05 14:42:36 plutone kadmind[22621](info): listening on fd 7: udp ::.464 (pktinfo)
kadmind: setsockopt(8,IPV6_V6ONLY,1) worked
Jan 05 14:42:37 plutone kadmind[22621](info): listening on fd 9: tcp 0.0.0.0.464
Jan 05 14:42:37 plutone kadmind[22621](info): listening on fd 8: tcp ::.464
Jan 05 14:42:37 plutone kadmind[22621](info): listening on fd 10: rpc 0.0.0.0.749
kadmind: setsockopt(11,IPV6_V6ONLY,1) worked
Jan 05 14:42:37 plutone kadmind[22621](info): listening on fd 11: rpc ::.749
Jan 05 14:42:37 plutone kadmind[22621](info): set up 6 sockets
Jan 05 14:42:37 plutone kadmind[22622](info): Seeding random number generator
Jan 05 14:42:37 plutone kadmind[22622](info): starting

[root@plutone ~]# tail -f /var/log/krb5kdc.log  
Jan 05 14:42:30 plutone krb5kdc[22619](Error): preauth pkinit failed to initialize: No realms configured correctly for pkinit support
Jan 05 14:42:30 plutone krb5kdc[22619](info): setting up network...
Jan 05 14:42:30 plutone krb5kdc[22619](info): listening on fd 6: udp 0.0.0.0.88 (pktinfo)
Jan 05 14:42:30 plutone krb5kdc[22619](info): listening on fd 7: udp 0.0.0.0.750 (pktinfo)
krb5kdc: setsockopt(8,IPV6_V6ONLY,1) worked
Jan 05 14:42:30 plutone krb5kdc[22619](info): listening on fd 8: udp ::.88 (pktinfo)
krb5kdc: setsockopt(9,IPV6_V6ONLY,1) worked
Jan 05 14:42:30 plutone krb5kdc[22619](info): listening on fd 9: udp ::.750 (pktinfo)
Jan 05 14:42:30 plutone krb5kdc[22619](info): set up 4 sockets
Jan 05 14:42:31 plutone krb5kdc[22620](info): commencing operation

Now add a service principal for our Oracle Database Server


[root@plutone krb5kdc]# /usr/local/sbin/kadmin.local 
Authenticating as principal root/plutone@EXAMPLE.COM with password.
kadmin.local:  DB11G/plutone@EXAMPLE.COM
kadmin.local: Unknown request "DB11G/plutone@EXAMPLE.COM".  Type "?" for a request list.
kadmin.local:  addprinc -randkey DB11G/plutone@EXAMPLE.COM
WARNING: no policy specified for DB11G/plutone@EXAMPLE.COM; defaulting to no policy
Principal "DB11G/plutone@EXAMPLE.COM" created.

Next the principal for the Oracle user, named marco, is created


[root@plutone krb5kdc]# /usr/local/sbin/kadmin.local 
Authenticating as principal root/plutone@EXAMPLE.COM with password.
kadmin.local:  addprinc marco
WARNING: no policy specified for marco@EXAMPLE.COM; defaulting to no policy
Enter password for principal "marco@EXAMPLE.COM": 
Re-enter password for principal "marco@EXAMPLE.COM": 
Principal "marco@EXAMPLE.COM" created.

Extract the service table from kerberos. This file will be used on sqlnet.ora configuration.


kadmin.local:  ktadd -k /tmp/keytab DB11G/plutone
Entry for principal DB11G/plutone with kvno 4, encryption type aes256-cts-hmac-sha1-96 added to keytab WRFILE:/tmp/keytab.
Entry for principal DB11G/plutone with kvno 4, encryption type aes128-cts-hmac-sha1-96 added to keytab WRFILE:/tmp/keytab.
Entry for principal DB11G/plutone with kvno 4, encryption type des3-cbc-sha1 added to keytab WRFILE:/tmp/keytab.
Entry for principal DB11G/plutone with kvno 4, encryption type arcfour-hmac added to keytab WRFILE:/tmp/keytab.

Create the Oracle user on the Oracle Database Server. According to Oracle documentation (Oracle Advanced Security Administrator's Guide) "This username must be created in uppercase and must have the realm specified."


idle> startup
ORACLE instance started.

Total System Global Area  626327552 bytes
Fixed Size                  2215944 bytes
Variable Size             427823096 bytes
Database Buffers          188743680 bytes
Redo Buffers                7544832 bytes
Database mounted.
Database opened.
idle> create user "MARCO@EXAMPLE.COM" identified externally;

User created.

idle> grant create session to "MARCO@EXAMPLE.COM";

Grant succeeded.

The authent parameter should be as following:


idle> show parameter authent

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
os_authent_prefix                    string      ops$
remote_os_authent                    boolean     FALSE

idle> alter system set os_authent_prefix='' scope=spfile;

System altered.

Copy the service table file from /tmp directory to elsewhere and give Oracle permission


[root@plutone krb5kdc]# cp /tmp/keytab /etc/
[root@plutone krb5kdc]# chown oracle.oinstall /etc/keytab

Now it's time to configure sqlnet.ora file to point to kerberos. The following are the parameters added on sqlnet.ora file:


[oracle@plutone admin]$ pwd
/u01/app/oracle/product/11.2.0/db_1/network/admin
[oracle@plutone admin]$ vi sqlnet.ora 
SQLNET.KERBEROS5_KEYTAB=/etc/keytab
SQLNET.KERBEROS5_CONF=/etc/krb5.conf
SQLNET.KERBEROS5_CONF_MIT=TRUE
SQLNET.AUTHENTICATION_KERBEROS5_SERVICE=DB11G
SQLNET.AUTHENTICATION_SERVICES=(BEQ,KERBEROS5)

Let's get a ticket from Kerberos server


[oracle@plutone admin]$ okinit marco

Kerberos Utilities for Linux: Version 11.2.0.1.0 - Production on 05-JAN-2011 16:02:02

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

Password for marco@EXAMPLE.COM: 
[oracle@plutone admin]$ oklist

Kerberos Utilities for Linux: Version 11.2.0.1.0 - Production on 05-JAN-2011 16:02:15

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

Ticket cache: /tmp/krb5cc_500
Default principal: marco@EXAMPLE.COM

   Valid Starting           Expires            Principal
05-Jan-2011 16:02:06  06-Jan-2011 00:02:03  krbtgt/EXAMPLE.COM@EXAMPLE.COM

And now it's time to log in


[oracle@plutone admin]$ sqlplus /@DB11G

SQL*Plus: Release 11.2.0.1.0 Production on Wed Jan 5 16:10:11 2011

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


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

marco@example.com@DB11G>

What does it happen if I remove the ticket ?


[root@plutone krb5kdc]# rm /tmp/krb5cc_500 
rm: remove regular file `/tmp/krb5cc_500'? y

Pf course I'm not able to authenticate the user.


[oracle@plutone admin]$ sqlplus /@DB11G

SQL*Plus: Release 11.2.0.1.0 Production on Wed Jan 5 16:19:52 2011

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

ERROR:
ORA-12638: Credential retrieval failed


Enter user-name:

Support Position for Oracle Products Running on VMWare Virtualized Environments

2011-01-05T10:57:00.002+01:00

Today on Oracle website I discovered this interesting announcement on Oracle support status for VMware environments.
On my company we have a VMWare support and already several VMWare virtualized environments, but we never thought to use those envs to virtualize some Oracle databases. We already need to virtualize Oracle databases, especially those for development and pre-production platforms, but we were waiting for just a IBM blade order.
In my opinion I think to still suggest and implement the Oracle VM solution to my CTO because there are some issues not clear on the document and, at this moment, this VMware support position doesn't fit at all to our requirements because we would like to implement a RAC virtualized environment formed by 10gR2 nodes.

This is the position of Oracle at 8 november 2010 (see doc id 249212.1 on metalink for more details and news). In bold I have highlighted my doubts.
What's your opinion ?

Regards.

Support Status for VMware Virtualized Environments
--------------------------------------------------
Oracle has not certified any of its products on VMware virtualized
environments. Oracle Support will assist customers running Oracle products
on VMware in the following manner: Oracle will only provide
support for issues that either are known to occur on the native OS, or
can be demonstrated not to be as a result of running on VMware.

If a problem is a known Oracle issue, Oracle support will recommend the
appropriate solution on the native OS. If that solution does not work in
the VMware virtualized environment, the customer will be referred to VMware
for support. When the customer can demonstrate that the Oracle solution
does not work when running on the native OS, Oracle will resume support,
including logging a bug with Oracle Development for investigation if required.

If the problem is determined not to be a known Oracle issue, we will refer
the customer to VMware for support. When the customer can demonstrate
that the issue occurs when running on the native OS, Oracle will resume
support, including logging a bug with Oracle Development for investigation
if required.

NOTE: Oracle has not certified any of its products on VMware. For Oracle RAC, Oracle will only
accept Service Requests as described in this note on Oracle RAC 11.2.0.2 and later
releases.

ORA-00214: controlfile inconsistent error after hiberning virtual machine

2010-10-06T10:23:00.003+01:00

Today I discovered this error while trying to start a database on my virtual machine. I think it happened because yesterday I "hibernated" several times my machine while this virtual machine and database were still running and forgot to close them.
Anyway.. this is what I've done to bring my database back to a normal and available status.

[oracle@plutone ~]$ SQL
SQL*Plus: Release 11.2.0.1.0 Production on Mon Oct 4 11:56:36 2010

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

Connected to an idle instance.

idle> startup
ORACLE instance started.

Total System Global Area  263049216 bytes
Fixed Size                  2212448 bytes
Variable Size             213912992 bytes
Database Buffers           41943040 bytes
Redo Buffers                4980736 bytes
ORA-00214: control file '/DATA/DB11G/control01.ctl' version 1935 inconsistent with file '/u01/app/oracle/flash_recovery_area/DB11G/control02.ctl' version 1932

idle> shutdown immediate
ORA-01507: database not mounted

ORACLE instance shut down.
idle> exit

Ok.. the error is quite clear. I need to remove a controlfile. Let me see my configuration and where are located my controlfiles.


[oracle@plutone ~]$ SQL

SQL*Plus: Release 11.2.0.1.0 Production on Mon Oct 4 12:26:12 2010

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

Connected to an idle instance.

idle> startup nomount
ORACLE instance started.

Total System Global Area  263049216 bytes
Fixed Size                  2212448 bytes
Variable Size             213912992 bytes
Database Buffers           41943040 bytes
Redo Buffers                4980736 bytes
idle> show parameter control_file

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
control_file_record_keep_time        integer     7
control_files                        string      /DATA/DB11G/control01.ctl, /u0
                                                 1/app/oracle/flash_recovery_ar
                                                 ea/DB11G/control02.ctl

I tried to use only one controlfile and see if my database likes it.


idle> alter system set control_files='/DATA/DB11G/control01.ctl' scope=spfile;

System altered.

idle> shutdown immediate;
ORA-01507: database not mounted


ORACLE instance shut down.
idle> startup restrict
ORACLE instance started.

Total System Global Area  263049216 bytes
Fixed Size                  2212448 bytes
Variable Size             213912992 bytes
Database Buffers           41943040 bytes
Redo Buffers                4980736 bytes
Database mounted.
Database opened.
idle> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

Ok.. the first controlfile is the good one, so I have to copy it to my second controlfile location


idle> host
[oracle@plutone ~]$ cp /DATA/DB11G/control01.ctl /u01/app/oracle/flash_recovery_area/DB11G/control02.ctl
[oracle@plutone ~]$ exit
exit

Start the instance again in nomount mode and add the second controlfile.


idle> startup nomount
ORACLE instance started.

Total System Global Area  263049216 bytes
Fixed Size                  2212448 bytes
Variable Size             218107296 bytes
Database Buffers           37748736 bytes
Redo Buffers                4980736 bytes
idle> show parameter control_files

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
control_files                        string      /DATA/DB11G/control01.ctl
idle> alter system set control_files='/DATA/DB11G/control01.ctl','/u01/app/oracle/flash_recovery_area/DB11G/control02.ctl' scope=spfile;

System altered.
idle> shutdown immediate
ORA-01507: database not mounted

ORACLE instance shut down.

Now let's start the instance in normal mode


idle> startup
ORACLE instance started.

Total System Global Area  263049216 bytes
Fixed Size                  2212448 bytes
Variable Size             218107296 bytes
Database Buffers           37748736 bytes
Redo Buffers                4980736 bytes
Database mounted.
Database opened.
idle> show parameter control_files

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
control_files                        string      /DATA/DB11G/control01.ctl, /u0
                                                 1/app/oracle/flash_recovery_ar
                                                 ea/DB11G/control02.ctl

That's all.

RMAN: how to duplicate a non-ASM active database to an ASM database

2010-10-04T14:34:00.016+01:00

My goal is to duplicate a production database where all datafiles are on file system to an auxiliary database based on ASM.
According on the same steps performed on this previous post http://dbaworkshop.blogspot.com/2010/09/rman-how-to-duplicate-database-on-same.html I've used the following settings:

[oracle@plutone admin]$ more listener.ora
# listener.ora Network Configuration File: /u01/app/oracle/product/11.2.0/db_1/network/admin/listener.ora
# Generated by Oracle configuration tools.

SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = DB11G)
(ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_1)
(SID_NAME = DB11G)
)
(SID_DESC =
(GLOBAL_DBNAME = DUP11G)
(ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_1)
(SID_NAME = DUP11G)
)
)

LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.172.130)(PORT = 1521))
)
)

ADR_BASE_LISTENER = /u01/app/oracle

[oracle@plutone admin]$ more tnsnames.ora
# tnsnames.ora Network Configuration File: /u01/app/oracle/product/11.2.0/db_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.

DUP11G =
(DESCRIPTION =
(ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = plutone)(PORT = 1521)))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SID = DUP11G)
)
)

DB11G =
(DESCRIPTION =
(ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = plutone)(PORT = 1521)))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = DB11G)
)
)

I needed of course to add at least an ASM instance and a diskgroup. After I added 10GB of a virtual hard disk to my virtual machine I completed the following steps:

[root@plutone ~]# fdisk -l

Disk /dev/sda: 21.4 GB, 21474836480 bytes
255 heads, 63 sectors/track, 2610 cylinders
Units = cylinders of 16065 * 512 = 8225280 bytes

Device Boot      Start         End      Blocks   Id  System
/dev/sda1   *           1          25      200781   83  Linux
/dev/sda2              26         286     2096482+  82  Linux swap / Solaris
/dev/sda3             287        2610    18667530   83  Linux

Disk /dev/sdb: 10.7 GB, 10737418240 bytes
255 heads, 63 sectors/track, 1305 cylinders
Units = cylinders of 16065 * 512 = 8225280 bytes

Device Boot      Start         End      Blocks   Id  System
/dev/sdb1               1        1305    10482381   83  Linux

Disk /dev/sdc: 10.7 GB, 10737418240 bytes
255 heads, 63 sectors/track, 1305 cylinders
Units = cylinders of 16065 * 512 = 8225280 bytes

Device Boot      Start         End      Blocks   Id  System
/dev/sdc1               1        1305    10482381   83  Linux

Disk /dev/sdd: 10.7 GB, 10737418240 bytes
255 heads, 63 sectors/track, 1305 cylinders
Units = cylinders of 16065 * 512 = 8225280 bytes

Disk /dev/sdd doesn't contain a valid partition table
[root@plutone ~]# fdisk /dev/sdd
Device contains neither a valid DOS partition table, nor Sun, SGI or OSF disklabel
Building a new DOS disklabel. Changes will remain in memory only,
until you decide to write them. After that, of course, the previous
content won't be recoverable.

The number of cylinders for this disk is set to 1305.
There is nothing wrong with that, but this is larger than 1024,
and could in certain setups cause problems with:
1) software that runs at boot time (e.g., old versions of LILO)
2) booting and partitioning software from other OSs
(e.g., DOS FDISK, OS/2 FDISK)
Warning: invalid flag 0x0000 of partition table 4 will be corrected by w(rite)

Command (m for help): n
Command action
e   extended
p   primary partition (1-4)
p
Partition number (1-4): 1
First cylinder (1-1305, default 1):
Using default value 1
Last cylinder or +size or +sizeM or +sizeK (1-1305, default 1305):
Using default value 1305

Command (m for help): w
The partition table has been altered!

Calling ioctl() to re-read partition table.
Syncing disks.

[root@plutone ~]# fdisk -l

Disk /dev/sda: 21.4 GB, 21474836480 bytes
255 heads, 63 sectors/track, 2610 cylinders
Units = cylinders of 16065 * 512 = 8225280 bytes

Device Boot      Start         End      Blocks   Id  System
/dev/sda1   *           1          25      200781   83  Linux
/dev/sda2              26         286     2096482+  82  Linux swap / Solaris
/dev/sda3             287        2610    18667530   83  Linux

Disk /dev/sdb: 10.7 GB, 10737418240 bytes
255 heads, 63 sectors/track, 1305 cylinders
Units = cylinders of 16065 * 512 = 8225280 bytes

Device Boot      Start         End      Blocks   Id  System
/dev/sdb1               1        1305    10482381   83  Linux

Disk /dev/sdc: 10.7 GB, 10737418240 bytes
255 heads, 63 sectors/track, 1305 cylinders
Units = cylinders of 16065 * 512 = 8225280 bytes

Device Boot      Start         End      Blocks   Id  System
/dev/sdc1               1        1305    10482381   83  Linux

Disk /dev/sdd: 10.7 GB, 10737418240 bytes
255 heads, 63 sectors/track, 1305 cylinders
Units = cylinders of 16065 * 512 = 8225280 bytes

Device Boot      Start         End      Blocks   Id  System
/dev/sdd1               1        1305    10482381   83  Linux

[root@plutone ~]# mkfs.ext3 /dev/sdd1
mke2fs 1.39 (29-May-2006)
Filesystem label=
OS type: Linux
Block size=4096 (log=2)
Fragment size=4096 (log=2)
1310720 inodes, 2620595 blocks
131029 blocks (5.00%) reserved for the super user
First data block=0
Maximum filesystem blocks=2684354560
80 block groups
32768 blocks per group, 32768 fragments per group
16384 inodes per group
Superblock backups stored on blocks:
32768, 98304, 163840, 229376, 294912, 819200, 884736, 1605632

Writing inode tables: done           
Creating journal (32768 blocks): done
Writing superblocks and filesystem accounting information: done

This filesystem will be automatically checked every 34 mounts or
180 days, whichever comes first.  Use tune2fs -c or -i to override.

[root@plutone ~]# more /etc/fstab
LABEL=/                 /                       ext3    defaults        1 1
LABEL=/boot             /boot                   ext3    defaults        1 2
tmpfs                   /dev/shm                tmpfs   defaults        0 0
devpts                  /dev/pts                devpts  gid=5,mode=620  0 0
sysfs                   /sys                    sysfs   defaults        0 0
proc                    /proc                   proc    defaults        0 0
LABEL=SWAP-sda2         swap                    swap    defaults        0 0
/dev/sdb1               /u02                    ext3    defaults        1 1
#/dev/sdc1               /u03                    ext3    defaults        1 1
[root@plutone ~]# mkdir /u04

[root@plutone ~]# more /etc/fstab
LABEL=/                 /                       ext3    defaults        1 1
LABEL=/boot             /boot                   ext3    defaults        1 2
tmpfs                   /dev/shm                tmpfs   defaults        0 0
devpts                  /dev/pts                devpts  gid=5,mode=620  0 0
sysfs                   /sys                    sysfs   defaults        0 0
proc                    /proc                   proc    defaults        0 0
LABEL=SWAP-sda2         swap                    swap    defaults        0 0
/dev/sdb1               /u02                    ext3    defaults        1 1
#/dev/sdc1               /u03                    ext3    defaults        1 1
/dev/sdd1               /u04                    ext3    defaults        1 1

[root@plutone ~]# mount /u04
[root@plutone tmp]# mkdir -p /u04/app/oracle/product/11.2.0/grid
[root@plutone tmp]# chown -R oracle.oinstall /u04

Now it's time to install the ASM drivers. Download them from OTN. For my machine they were:

[root@plutone rpm]# ls
oracleasm-2.6.18-194.11.3.el5-2.0.5-1.el5.x86_64.rpm   oracleasmlib-2.0.4-1.el5.x86_64.rpm   oracleasm-support-2.1.3-1.el5.x86_64.rpm
[root@plutone rpm]# rpm -Uvh oracleasm*

Preparing...########################################## [100%]
1:oracleasm-support################################### [ 33%]
2:oracleasm-2.6.18-194.11############################# [ 67%]
3:oracleasmlib######################################## [100%]

ASMLib software packages are now installed successfully.

Configure the ASM library, with the following command:
/etc/init.d/oracleasm configure
Type oracle and oinstall as user and group name that own the driver interface; type y to configure starting ASM library on boot and again y to scan ASM disks on boot.

Create the diskgroup:
/etc/init.d/oracleasm createdisk DG_DATA_DUP11G /dev/sdd1

Download/extract the grid software from oracle website and create the ASM Instance as root user:
./runInstaller

Perform the post installation task

[root@plutone tmp]# /u04/app/oracle/product/11.2.0/grid/root.sh
Running Oracle 11g root.sh script...

The following environment variables are set as:
ORACLE_OWNER= oracle
ORACLE_HOME= /u04/app/oracle/product/11.2.0/grid

Enter the full pathname of the local bin directory: [/usr/local/bin]:
The file "dbhome" already exists in /usr/local/bin. Overwrite it? (y/n)
[n]: y
Copying dbhome to /usr/local/bin ...
The file "oraenv" already exists in /usr/local/bin. Overwrite it? (y/n)
[n]: y
Copying oraenv to /usr/local/bin ...
The file "coraenv" already exists in /usr/local/bin. Overwrite it? (y/n)
[n]: y
Copying coraenv to /usr/local/bin ...

Entries will be added to the /etc/oratab file as needed by
Database Configuration Assistant when a database is created
Finished running generic part of root.sh script.
Now product-specific root actions will be performed.
2010-10-01 16:11:18: Checking for super user privileges
2010-10-01 16:11:18: User has super user privileges
2010-10-01 16:11:18: Parsing the host name
Using configuration parameter file: /u04/app/oracle/product/11.2.0/grid/crs/install/crsconfig_params
Creating trace directory
LOCAL ADD MODE
Creating OCR keys for user 'oracle', privgrp 'oinstall'..
Operation successful.
CRS-4664: Node plutone successfully pinned.
Adding daemon to inittab
CRS-4123: Oracle High Availability Services has been started.
ohasd is starting
ADVM/ACFS is not supported on centos-release-5-5.el5.centos

plutone 2010/10/01 16:11:48 /u04/app/oracle/product/11.2.0/grid/cdata/plutone/backup_20101001_161148.olr
Successfully configured Oracle Grid Infrastructure for a Standalone Server
Updating inventory properties for clusterware
Starting Oracle Universal Installer...

Checking swap space: must be greater than 500 MB. Actual 2047 MB Passed
The inventory pointer is located at /etc/oraInst.loc
The inventory is located at /u01/app/oraInventory
'UpdateNodeList' was successful.

The ASM instance is now running:

[root@plutone tmp]# !ps
ps -ef|grep smon
oracle   15904     1  0 16:14 ?        00:00:00 asm_smon_+ASM
root     16204  5028  0 16:22 pts/0    00:00:00 grep smon

Create or copy the orapwd file for the duplicated database

[oracle@plutone ~]$ cd /u01/app/oracle/product/11.2.0/db_1/dbs
[oracle@plutone dbs]$ orapwd password=oracle file=orapwDUP11G entries=20
or
[oracle@plutone dbs]$ cp orapwDB11G orapwDUP11G

Startup the listener and the two databases (target DB11G in open mode, auxiliary DUP11G in nomount mode). Then start rman with the following command:

[oracle@plutone ~]$ rman target sys/oracle@DB11G auxiliary sys/oracle@DUP11G

Recovery Manager: Release 11.2.0.1.0 - Production on Mon Oct 4 15:11:36 2010

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

connected to target database: DB11G (DBID=197032563)
connected to auxiliary database: DUP11G (not mounted)

RMAN> duplicate target database to DUP11G from active database spfile parameter_value_convert '/DATA/DB11G/','+DATA/' set db_create_file_dest='+DATA';

Starting Duplicate Db at 2010-10-04:15:11:47
using target database control file instead of recovery catalog
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=97 device type=DISK

contents of Memory Script:
{
  backup as copy reuse
  targetfile  '/u01/app/oracle/product/11.2.0/db_1/dbs/spfileDB11G.ora' auxiliary format
'/u01/app/oracle/product/11.2.0/db_1/dbs/spfileDUP11G.ora'   ;
  sql clone "alter system set spfile= ''/u01/app/oracle/product/11.2.0/db_1/dbs/spfileDUP11G.ora''";
}
executing Memory Script

Starting backup at 2010-10-04:15:11:50
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=16 device type=DISK
Finished backup at 2010-10-04:15:11:55

sql statement: alter system set spfile= ''/u01/app/oracle/product/11.2.0/db_1/dbs/spfileDUP11G.ora''

contents of Memory Script:
{
  sql clone "alter system set  db_name =
''DUP11G'' comment=
''duplicate'' scope=spfile";
  sql clone "alter system set  control_files =
''+DATA/control01.ctl'' comment=
'''' scope=spfile";
  sql clone "alter system set  db_create_file_dest =
''+DATA'' comment=
'''' scope=spfile";
  shutdown clone immediate;
  startup clone nomount;
}
executing Memory Script

sql statement: alter system set  db_name =  ''DUP11G'' comment= ''duplicate'' scope=spfile

sql statement: alter system set  control_files =  ''+DATA/control01.ctl'' comment= '''' scope=spfile

sql statement: alter system set  db_create_file_dest =  ''+DATA'' comment= '''' scope=spfile

Oracle instance shut down

connected to auxiliary database (not started)
Oracle instance started

Total System Global Area     263049216 bytes

Fixed Size                     2212448 bytes
Variable Size                201330080 bytes
Database Buffers              54525952 bytes
Redo Buffers                   4980736 bytes

contents of Memory Script:
{
  sql clone "alter system set  db_name =
''DB11G'' comment=
''Modified by RMAN duplicate'' scope=spfile";
  sql clone "alter system set  db_unique_name =
''DUP11G'' comment=
''Modified by RMAN duplicate'' scope=spfile";
  shutdown clone immediate;
  startup clone force nomount
  backup as copy current controlfile auxiliary format  '+DATA/control01.ctl';
  alter clone database mount;
}
executing Memory Script

sql statement: alter system set  db_name =  ''DB11G'' comment= ''Modified by RMAN duplicate'' scope=spfile

sql statement: alter system set  db_unique_name =  ''DUP11G'' comment= ''Modified by RMAN duplicate'' scope=spfile

Oracle instance shut down

Oracle instance started

Total System Global Area     263049216 bytes

Fixed Size                     2212448 bytes
Variable Size                209718688 bytes
Database Buffers              46137344 bytes
Redo Buffers                   4980736 bytes

Starting backup at 2010-10-04:15:13:55
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile copy
copying current control file
output file name=/u01/app/oracle/product/11.2.0/db_1/dbs/snapcf_DB11G.f tag=TAG20101004T151355 RECID=4 STAMP=731517240
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:15
Finished backup at 2010-10-04:15:14:11

database mounted

contents of Memory Script:
{
  set newname for clone datafile  1 to new;
  set newname for clone datafile  2 to new;
  set newname for clone datafile  3 to new;
  set newname for clone datafile  4 to new;
  set newname for clone datafile  5 to new;
  set newname for clone datafile  6 to new;
  backup as copy reuse
  datafile  1 auxiliary format new
  datafile  2 auxiliary format new
  datafile  3 auxiliary format new
  datafile  4 auxiliary format new
  datafile  5 auxiliary format new
  datafile  6 auxiliary format new
  ;
  sql 'alter system archive log current';
}
executing Memory Script

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

Starting backup at 2010-10-04:15:14:18
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile copy
input datafile file number=00006 name=/DATA/DB11G/soe.dbf
output file name=+DATA/dup11g/datafile/soe.257.731517261 tag=TAG20101004T151418
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:03:58
channel ORA_DISK_1: starting datafile copy
input datafile file number=00001 name=/DATA/DB11G/system01.dbf
output file name=+DATA/dup11g/datafile/system.258.731517499 tag=TAG20101004T151418
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:01:06
channel ORA_DISK_1: starting datafile copy
input datafile file number=00003 name=/DATA/DB11G/undotbs01.dbf
output file name=+DATA/dup11g/datafile/undotbs1.259.731517565 tag=TAG20101004T151418
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:56
channel ORA_DISK_1: starting datafile copy
input datafile file number=00002 name=/DATA/DB11G/sysaux01.dbf
output file name=+DATA/dup11g/datafile/sysaux.260.731517621 tag=TAG20101004T151418
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:45
channel ORA_DISK_1: starting datafile copy
input datafile file number=00005 name=/DATA/DB11G/example01.dbf
output file name=+DATA/dup11g/datafile/example.261.731517665 tag=TAG20101004T151418
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:15
channel ORA_DISK_1: starting datafile copy
input datafile file number=00004 name=/DATA/DB11G/users01.dbf
output file name=+DATA/dup11g/datafile/users.262.731517681 tag=TAG20101004T151418
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01
Finished backup at 2010-10-04:15:21:21

sql statement: alter system archive log current

contents of Memory Script:
{
  backup as copy reuse
  archivelog like  "/u02/FRA11G/DB11G/archivelog/2010_10_04/o1_mf_1_58_6bmo6m11_.arc" auxiliary format
"/u02/FRA11G/DUP11G/archivelog/2010_10_04/o1_mf_1_58_%u_.arc"   ;
  catalog clone recovery area;
  switch clone datafile all;
}
executing Memory Script

Starting backup at 2010-10-04:15:21:25
using channel ORA_DISK_1
channel ORA_DISK_1: starting archived log copy
input archived log thread=1 sequence=58 RECID=7 STAMP=731517683
output file name=/u02/FRA11G/DUP11G/archivelog/2010_10_04/o1_mf_1_58_0elpk4nm_.arc RECID=0 STAMP=0
channel ORA_DISK_1: archived log copy complete, elapsed time: 00:00:01
Finished backup at 2010-10-04:15:21:27

searching for all files in the recovery area

List of Files Unknown to the Database
=====================================
File Name: /u02/FRA11G/DUP11G/archivelog/2010_10_04/o1_mf_1_58_0elpk4nm_.arc
cataloging files...
cataloging done

List of Cataloged Files
=======================
File Name: /u02/FRA11G/DUP11G/archivelog/2010_10_04/o1_mf_1_58_0elpk4nm_.arc

datafile 1 switched to datafile copy
input datafile copy RECID=4 STAMP=731517689 file name=+DATA/dup11g/datafile/system.258.731517499
datafile 2 switched to datafile copy
input datafile copy RECID=5 STAMP=731517689 file name=+DATA/dup11g/datafile/sysaux.260.731517621
datafile 3 switched to datafile copy
input datafile copy RECID=6 STAMP=731517689 file name=+DATA/dup11g/datafile/undotbs1.259.731517565
datafile 4 switched to datafile copy
input datafile copy RECID=7 STAMP=731517689 file name=+DATA/dup11g/datafile/users.262.731517681
datafile 5 switched to datafile copy
input datafile copy RECID=8 STAMP=731517689 file name=+DATA/dup11g/datafile/example.261.731517665
datafile 6 switched to datafile copy
input datafile copy RECID=9 STAMP=731517689 file name=+DATA/dup11g/datafile/soe.257.731517261

contents of Memory Script:
{
  set until scn  1325144;
  recover
  clone database
   delete archivelog
  ;
}
executing Memory Script

executing command: SET until clause

Starting recover at 2010-10-04:15:21:31
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=13 device type=DISK

starting media recovery

archived log for thread 1 with sequence 58 is already on disk as file /u02/FRA11G/DUP11G/archivelog/2010_10_04/o1_mf_1_58_0elpk4nm_.arc
archived log file name=/u02/FRA11G/DUP11G/archivelog/2010_10_04/o1_mf_1_58_0elpk4nm_.arc thread=1 sequence=58
media recovery complete, elapsed time: 00:00:02
Finished recover at 2010-10-04:15:21:40

contents of Memory Script:
{
  shutdown clone immediate;
  startup clone nomount;
  sql clone "alter system set  db_name =
''DUP11G'' comment=
''Reset to original value by RMAN'' scope=spfile";
  sql clone "alter system reset  db_unique_name scope=spfile";
  shutdown clone immediate;
  startup clone nomount;
}
executing Memory Script

database dismounted
Oracle instance shut down

connected to auxiliary database (not started)
Oracle instance started

Total System Global Area     263049216 bytes

Fixed Size                     2212448 bytes
Variable Size                226495904 bytes
Database Buffers              29360128 bytes
Redo Buffers                   4980736 bytes

sql statement: alter system set  db_name =  ''DUP11G'' comment= ''Reset to original value by RMAN'' scope=spfile

sql statement: alter system reset  db_unique_name scope=spfile

Oracle instance shut down

connected to auxiliary database (not started)
Oracle instance started

Total System Global Area     263049216 bytes

Fixed Size                     2212448 bytes
Variable Size                226495904 bytes
Database Buffers              29360128 bytes
Redo Buffers                   4980736 bytes
sql statement: CREATE CONTROLFILE REUSE SET DATABASE "DUP11G" RESETLOGS ARCHIVELOG
 MAXLOGFILES     16
 MAXLOGMEMBERS      3
 MAXDATAFILES      100
 MAXINSTANCES     8
 MAXLOGHISTORY      292
LOGFILE
 GROUP  1  SIZE 50 M ,
 GROUP  2  SIZE 50 M ,
 GROUP  3  SIZE 50 M
DATAFILE
 '+DATA/dup11g/datafile/system.258.731517499'
CHARACTER SET WE8MSWIN1252


contents of Memory Script:
{
  set newname for clone tempfile  1 to new;
  switch clone tempfile all;
  catalog clone datafilecopy  "+DATA/dup11g/datafile/sysaux.260.731517621",
"+DATA/dup11g/datafile/undotbs1.259.731517565",
"+DATA/dup11g/datafile/users.262.731517681",
"+DATA/dup11g/datafile/example.261.731517665",
"+DATA/dup11g/datafile/soe.257.731517261";
  switch clone datafile all;
}
executing Memory Script

executing command: SET NEWNAME

renamed tempfile 1 to +DATA in control file

cataloged datafile copy
datafile copy file name=+DATA/dup11g/datafile/sysaux.260.731517621 RECID=1 STAMP=731517731
cataloged datafile copy
datafile copy file name=+DATA/dup11g/datafile/undotbs1.259.731517565 RECID=2 STAMP=731517731
cataloged datafile copy
datafile copy file name=+DATA/dup11g/datafile/users.262.731517681 RECID=3 STAMP=731517731
cataloged datafile copy
datafile copy file name=+DATA/dup11g/datafile/example.261.731517665 RECID=4 STAMP=731517731
cataloged datafile copy
datafile copy file name=+DATA/dup11g/datafile/soe.257.731517261 RECID=5 STAMP=731517731

datafile 2 switched to datafile copy
input datafile copy RECID=1 STAMP=731517731 file name=+DATA/dup11g/datafile/sysaux.260.731517621
datafile 3 switched to datafile copy
input datafile copy RECID=2 STAMP=731517731 file name=+DATA/dup11g/datafile/undotbs1.259.731517565
datafile 4 switched to datafile copy
input datafile copy RECID=3 STAMP=731517731 file name=+DATA/dup11g/datafile/users.262.731517681
datafile 5 switched to datafile copy
input datafile copy RECID=4 STAMP=731517731 file name=+DATA/dup11g/datafile/example.261.731517665
datafile 6 switched to datafile copy
input datafile copy RECID=5 STAMP=731517731 file name=+DATA/dup11g/datafile/soe.257.731517261

contents of Memory Script:
{
  Alter clone database open resetlogs;
}
executing Memory Script

database opened
Finished Duplicate Db at 2010-10-04:15:22:39

RMAN> exit


Recovery Manager complete

Let's see where are the datafiles for DUP11G instance:

[oracle@plutone ~]$ export ORACLE_SID=DUP11G
[oracle@plutone ~]$ SQL

SQL*Plus: Release 11.2.0.1.0 Production on Mon Oct 4 15:28:11 2010

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

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options

sys@DUP11G> col NAME format a50
sys@DUP11G> select FILE#, NAME from V$DATAFILE;

    FILE# NAME
---------- --------------------------------------------------
        1 +DATA/dup11g/datafile/system.258.731517499
        2 +DATA/dup11g/datafile/sysaux.260.731517621
        3 +DATA/dup11g/datafile/undotbs1.259.731517565
        4 +DATA/dup11g/datafile/users.262.731517681
        5 +DATA/dup11g/datafile/example.261.731517665
        6 +DATA/dup11g/datafile/soe.257.731517261

6 rows selected.

That's all.

How to drop a database from SQL*PLUS (without DBCA)

2010-09-24T14:42:00.002+01:00

I need to remove a database created only for test purpose using RMAN duplicate command. DBCA is not able to identify this database because no info was available on the inventory neither on oratab file.

Let's drop this database (so deleting its datafiles) simply using sql*plus tool.
[oracle@plutone ~]$ export ORACLE_SID=DUP11G
[oracle@plutone ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Fri Sep 24 15:35:35 2010

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

Connected to an idle instance.

idle> startup
ORACLE instance started.

Total System Global Area 217157632 bytes
Fixed Size 2211928 bytes
Variable Size 159387560 bytes
Database Buffers 50331648 bytes
Redo Buffers 5226496 bytes
Database mounted.
Database opened.
idle> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
idle> startup mount exclusive restrict
ORACLE instance started.

Total System Global Area 217157632 bytes
Fixed Size 2211928 bytes
Variable Size 159387560 bytes
Database Buffers 50331648 bytes
Redo Buffers 5226496 bytes
Database mounted.
idle> drop database;

Database dropped.

Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
idle> exit

Let's see at file system level what's happening during this process...
Here are the datafiles and controlfiles of DUP11G database just one second before executing the DROP DATABASE command.
[oracle@plutone DUP11G]$ ll
total 3949168
-rw-r----- 1 oracle oinstall 7872512 Sep 24 09:50 users01.dbf
-rw-r----- 1 oracle oinstall 1918902272 Sep 24 09:50 soe.dbf
-rw-r----- 1 oracle oinstall 52429312 Sep 24 09:50 redo03.log
-rw-r----- 1 oracle oinstall 52429312 Sep 24 09:50 redo02.log
-rw-r----- 1 oracle oinstall 104865792 Sep 24 09:50 example01.dbf
-rw-r----- 1 oracle oinstall 195043328 Sep 24 09:52 temp01.dbf
-rw-r----- 1 oracle oinstall 592453632 Sep 24 10:02 undotbs01.dbf
-rw-r----- 1 oracle oinstall 713039872 Sep 24 10:02 system01.dbf
-rw-r----- 1 oracle oinstall 524296192 Sep 24 10:02 sysaux01.dbf
-rw-r----- 1 oracle oinstall 52429312 Sep 24 10:02 redo01.log
-rw-r----- 1 oracle oinstall 10076160 Sep 24 10:03 control02.ctl
-rw-r----- 1 oracle oinstall 10076160 Sep 24 10:03 control01.ctl

The same info few seconds after DROP DATABASE command was issued. All datafiles were already deleted.
[oracle@plutone DUP11G]$ ll
total 19712
-rw-r----- 1 oracle oinstall 10076160 Sep 24 15:38 control02.ctl
-rw-r----- 1 oracle oinstall 10076160 Sep 24 15:38 control01.ctl

After sql*plus stated "Database dropped" also the controlfile are deleted.
[oracle@plutone DUP11G]$ ll
total 0

That's all.

RMAN: how to duplicate a database on the same host

2010-09-24T09:15:00.011+01:00

I need to duplicate a database. There are many possibilities to complete this task and I choose to use RMAN and its commands: in particular I decided (to be honest it was just a poor notebook configuration to address my choice) to duplicate the target database to the same host. Perhaps I could spend my time on other possibilities offered by RMAN such as for example to duplicate the target database on a remote host.

I'm working with Oracle Database 11.2.0.1.0 on 64bit hosted by a CentOS virtualized machine: the target database is DB11G and the duplicated database will be DUP11G.

So this is what I did. First thing I have to do is to get a valid backup of DB11G.
The second thing is that I need to get some informations about my machine because I've added and removed many virtual disks lately and I don't remember where are exactly my datafiles... I just remember I have space problems.

Let's start the instance (SQL is an alias for sqlplus / as sysdba and my ORACLE_SID is already set to DB11G)
[oracle@plutone DB11G]$ SQL

SQL*Plus: Release 11.2.0.1.0 Production on Thu Sep 23 16:48:54 2010

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

Connected to an idle instance.

idle> startup
ORACLE instance started.

Total System Global Area 626327552 bytes
Fixed Size 2215944 bytes
Variable Size 432017400 bytes
Database Buffers 184549376 bytes
Redo Buffers 7544832 bytes
Database mounted.
Database opened.

I usually don't run test database on archivelog mode, just let me check. Otherwise I will set it on archivelog mode.
idle> archive log list
Database log mode No Archive Mode
Automatic archival Disabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 50
Current log sequence 52
idle> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
idle> startup mount;
ORACLE instance started.

Total System Global Area 626327552 bytes
Fixed Size 2215944 bytes
Variable Size 432017400 bytes
Database Buffers 184549376 bytes
Redo Buffers 7544832 bytes
Database mounted.
idle> alter database archivelog;

Database altered.

idle> alter database open;

Database altered.

idle> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 50
Next log sequence to archive 52
Current log sequence 52

Let me see how much space I have on FRA.
idle> show parameter recovery

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest string /u01/app/oracle/flash_recovery
_area
db_recovery_file_dest_size big integer 3882M
recovery_parallelism integer 0

idle> exit

I don't have any space to get a valid backup
[oracle@plutone DB11G]$ cd /u01/app/oracle/flash_recovery_area/DB11G
[oracle@plutone DB11G]$ df -h .
Filesystem Size Used Avail Use% Mounted on
/dev/sda3 18G 16G 802M 96% /

[oracle@plutone DB11G]$ df -h
Filesystem Size Used Avail Use% Mounted on
/dev/sda3 18G 16G 802M 96% /
/dev/sda1 190M 18M 163M 10% /boot
tmpfs 617M 0 617M 0% /dev/shm
/dev/sdb1 9.9G 4.3G 5.1G 46% /u02

Let me check what is the size of DB11G datafiles.
[oracle@plutone DB11G]$ cd /DATA/DB11G
[oracle@plutone DB11G]$ ll
total 4128412
-rw-r----- 1 oracle oinstall 195043328 Sep 23 16:51 temp01.dbf
-rw-r----- 1 oracle oinstall 52429312 Sep 23 16:54 redo03.log
-rw-r----- 1 oracle oinstall 52429312 Sep 23 16:54 redo02.log
-rw-r----- 1 oracle oinstall 7872512 Sep 23 16:55 users01.dbf
-rw-r----- 1 oracle oinstall 592453632 Sep 23 16:55 undotbs01.dbf
-rw-r----- 1 oracle oinstall 713039872 Sep 23 16:55 system01.dbf
-rw-r----- 1 oracle oinstall 524296192 Sep 23 16:55 sysaux01.dbf
-rw-r----- 1 oracle oinstall 1918902272 Sep 23 16:55 soe.dbf
-rw-r----- 1 oracle oinstall 52429312 Sep 23 16:55 redo01.log
-rw-r----- 1 oracle oinstall 104865792 Sep 23 16:55 example01.dbf
-rw-r----- 1 oracle oinstall 9748480 Sep 23 16:55 control01.ctl
[oracle@plutone DB11G]$ du -hs
4.0G .

Definitely I have no space to host a duplicated databese.
Let me point the FRA of DB11G to /u02.
[root@plutone u02]# cd /u02/
[root@plutone u02]# ll
total 40
drwxr-xr-x 3 ora10g oinstall 4096 Aug 16 14:18 app
drwxr-xr-x 3 ora10g oinstall 4096 Aug 16 16:43 DATA10
drwxr-xr-x 2 oracle oinstall 4096 Sep 23 17:32 DATA_DUP11G
drwxr-xr-x 3 ora10g oinstall 4096 Aug 16 16:44 FRA10
drwxr-xr-x 2 oracle oinstall 4096 Sep 23 17:30 FRA11G
drwx------ 2 ora10g oinstall 16384 Aug 16 14:04 lost+found
drwxr-xr-x 2 oraxe oinstall 4096 Sep 7 11:42 XE

[root@plutone u02]# mkdir FRA11G
[root@plutone u02]# chown -R oracle.oinstall FRA11G/
idle> alter system set db_recovery_file_dest='/u02/FRA11G' scope=spfile;

System altered.

idle> show parameter recovery

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest string /u01/app/oracle/flash_recovery
_area
db_recovery_file_dest_size big integer 3882M
recovery_parallelism integer 0
idle> alter system set db_recovery_file_dest='/u02/FRA11G' scope=memory;

System altered.

idle> show parameter recovery

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest string /u02/FRA11G
db_recovery_file_dest_size big integer 3882M
recovery_parallelism integer 0

Let me check if Oracle is able to write on that new FRA location:
[root@plutone FRA11G]# pwd
/u02/FRA11G
[root@plutone FRA11G]# ll
total 0

idle> alter database open;

Database altered.

idle> alter system switch logfile;

System altered.

[root@plutone FRA11G]# ll
total 4
drwxr-x--- 3 oracle oinstall 4096 Sep 23 17:38 DB11G
[root@plutone FRA11G]# cd DB11G/archivelog/
[root@plutone archivelog]# ll
total 4
drwxr-x--- 2 oracle oinstall 4096 Sep 23 17:38 2010_09_23
[root@plutone archivelog]# cd 2010_09_23/
[root@plutone 2010_09_23]# ll
total 1908
-rw-r----- 1 oracle oinstall 1947648 Sep 23 17:38 o1_mf_1_52_69px43oj_.arc

Let me proceed with RMAN configuration:
[oracle@plutone DB11G]$ rman target /

Recovery Manager: Release 11.2.0.1.0 - Production on Thu Sep 23 17:40:40 2010

Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.

connected to target database: DB11G (DBID=197032563)

RMAN> list backup;

using target database control file instead of recovery catalog
specification does not match any backup in the repository

RMAN> show all;

RMAN configuration parameters for database with db_unique_name DB11G are:
CONFIGURE RETENTION POLICY TO REDUNDANCY 1; # default
CONFIGURE BACKUP OPTIMIZATION OFF; # default
CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default
CONFIGURE CONTROLFILE AUTOBACKUP OFF; # default
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F'; # default
CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO BACKUPSET; # default
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE MAXSETSIZE TO UNLIMITED; # default
CONFIGURE ENCRYPTION FOR DATABASE OFF; # default
CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default
CONFIGURE COMPRESSION ALGORITHM 'BASIC' AS OF RELEASE 'DEFAULT' OPTIMIZE FOR LOAD TRUE ; # default
CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default
CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/u01/app/oracle/product/11.2.0/db_1/dbs/snapcf_DB11G.f'; # default

RMAN> CONFIGURE CONTROLFILE AUTOBACKUP ON;

new RMAN configuration parameters:
CONFIGURE CONTROLFILE AUTOBACKUP ON;
new RMAN configuration parameters are successfully stored

RMAN> CONFIGURE BACKUP OPTIMIZATION ON;

new RMAN configuration parameters:
CONFIGURE BACKUP OPTIMIZATION ON;
new RMAN configuration parameters are successfully stored

RMAN>

Just before I execute the backup I notice there is only one directory under /u02/FRA11G/DB11G:
[root@plutone DB11G]# ll
total 4
drwxr-x--- 3 oracle oinstall 4096 Sep 23 17:38 archivelog

Perform a complete full database backup plus archivelog:
RMAN> backup database plus archivelog;

Starting backup at 2010-09-23:17:42:26
current log archived
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=21 device type=DISK
channel ORA_DISK_1: starting archived log backup set
channel ORA_DISK_1: specifying archived log(s) in backup set
input archived log thread=1 sequence=52 RECID=1 STAMP=730489124
input archived log thread=1 sequence=53 RECID=2 STAMP=730489346
channel ORA_DISK_1: starting piece 1 at 2010-09-23:17:42:29
channel ORA_DISK_1: finished piece 1 at 2010-09-23:17:42:33
piece handle=/u02/FRA11G/DB11G/backupset/2010_09_23/o1_mf_annnn_TAG20100923T174228_69pxc5wb_.bkp tag=TAG20100923T174228 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:04
Finished backup at 2010-09-23:17:42:34

Starting backup at 2010-09-23:17:42:34
using channel ORA_DISK_1
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00006 name=/DATA/DB11G/soe.dbf
input datafile file number=00001 name=/DATA/DB11G/system01.dbf
input datafile file number=00003 name=/DATA/DB11G/undotbs01.dbf
input datafile file number=00002 name=/DATA/DB11G/sysaux01.dbf
input datafile file number=00005 name=/DATA/DB11G/example01.dbf
input datafile file number=00004 name=/DATA/DB11G/users01.dbf
channel ORA_DISK_1: starting piece 1 at 2010-09-23:17:42:35
channel ORA_DISK_1: finished piece 1 at 2010-09-23:17:49:02
piece handle=/u02/FRA11G/DB11G/backupset/2010_09_23/o1_mf_nnndf_TAG20100923T174235_69pxccs4_.bkp tag=TAG20100923T174235 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:06:27
Finished backup at 2010-09-23:17:49:02

Starting backup at 2010-09-23:17:49:03
current log archived
using channel ORA_DISK_1
channel ORA_DISK_1: starting archived log backup set
channel ORA_DISK_1: specifying archived log(s) in backup set
input archived log thread=1 sequence=54 RECID=3 STAMP=730489744
channel ORA_DISK_1: starting piece 1 at 2010-09-23:17:49:05
channel ORA_DISK_1: finished piece 1 at 2010-09-23:17:49:06
piece handle=/u02/FRA11G/DB11G/backupset/2010_09_23/o1_mf_annnn_TAG20100923T174904_69pxqkjl_.bkp tag=TAG20100923T174904 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 2010-09-23:17:49:06

Starting Control File and SPFILE Autobackup at 2010-09-23:17:49:07
piece handle=/u02/FRA11G/DB11G/autobackup/2010_09_23/o1_mf_s_730489747_69pxqqxd_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 2010-09-23:17:49:14

And now on file system I've:
[root@plutone DB11G]# ll
total 8
drwxr-x--- 3 oracle oinstall 4096 Sep 23 17:38 archivelog
drwxr-x--- 3 oracle oinstall 4096 Sep 23 17:42 backupset

[root@plutone DB11G]# ll -h backupset/2010_09_23/
total 3.3G
-rw-r----- 1 oracle oinstall 2.0M Sep 23 17:42 o1_mf_annnn_TAG20100923T174228_69pxc5wb_.bkp
-rw-r----- 1 oracle oinstall 165K Sep 23 17:49 o1_mf_annnn_TAG20100923T174904_69pxqkjl_.bkp
-rw-r----- 1 oracle oinstall 3.3G Sep 23 17:48 o1_mf_nnndf_TAG20100923T174235_69pxccs4_.bkp

Now I have to add a new virtualized disk because you can see I have no space to create a duplicate database of a 4G target one.
Have a look to my current disk configuration:
[root@plutone DB11G]# mount
/dev/sda3 on / type ext3 (rw)
proc on /proc type proc (rw)
sysfs on /sys type sysfs (rw)
devpts on /dev/pts type devpts (rw,gid=5,mode=620)
/dev/sda1 on /boot type ext3 (rw)
tmpfs on /dev/shm type tmpfs (rw)
/dev/sdb1 on /u02 type ext3 (rw)
none on /proc/sys/fs/binfmt_misc type binfmt_misc (rw)
none on /proc/fs/vmblock/mountPoint type vmblock (rw)
sunrpc on /var/lib/nfs/rpc_pipefs type rpc_pipefs (rw)
[root@plutone DB11G]# df -h
Filesystem Size Used Avail Use% Mounted on
/dev/sda3 18G 16G 794M 96% /
/dev/sda1 190M 18M 163M 10% /boot
tmpfs 617M 392M 226M 64% /dev/shm
/dev/sdb1 9.9G 7.6G 1.9G 81% /u02
[root@plutone DB11G]# more /etc/fstab
LABEL=/ / ext3 defaults 1 1
LABEL=/boot /boot ext3 defaults 1 2
tmpfs /dev/shm tmpfs defaults 0 0
devpts /dev/pts devpts gid=5,mode=620 0 0
sysfs /sys sysfs defaults 0 0
proc /proc proc defaults 0 0
LABEL=SWAP-sda2 swap swap defaults 0 0
/dev/sdb1 /u02 ext3 defaults 1 1

[root@plutone ~]# fdisk -l

Disk /dev/sda: 21.4 GB, 21474836480 bytes
255 heads, 63 sectors/track, 2610 cylinders
Units = cylinders of 16065 * 512 = 8225280 bytes

Device Boot Start End Blocks Id System
/dev/sda1 * 1 25 200781 83 Linux
/dev/sda2 26 286 2096482+ 82 Linux swap / Solaris
/dev/sda3 287 2610 18667530 83 Linux

Disk /dev/sdb: 10.7 GB, 10737418240 bytes
255 heads, 63 sectors/track, 1305 cylinders
Units = cylinders of 16065 * 512 = 8225280 bytes

Device Boot Start End Blocks Id System
/dev/sdb1 1 1305 10482381 83 Linux

After I've added a new virtualied disk of 10G I need to format and mount it.
[root@plutone ~]# fdisk -l

Disk /dev/sda: 21.4 GB, 21474836480 bytes
255 heads, 63 sectors/track, 2610 cylinders
Units = cylinders of 16065 * 512 = 8225280 bytes

Device Boot Start End Blocks Id System
/dev/sda1 * 1 25 200781 83 Linux
/dev/sda2 26 286 2096482+ 82 Linux swap / Solaris
/dev/sda3 287 2610 18667530 83 Linux

Disk /dev/sdb: 10.7 GB, 10737418240 bytes
255 heads, 63 sectors/track, 1305 cylinders
Units = cylinders of 16065 * 512 = 8225280 bytes

Device Boot Start End Blocks Id System
/dev/sdb1 1 1305 10482381 83 Linux

Disk /dev/sdc: 10.7 GB, 10737418240 bytes
255 heads, 63 sectors/track, 1305 cylinders
Units = cylinders of 16065 * 512 = 8225280 bytes

Disk /dev/sdc doesn't contain a valid partition table

[root@plutone ~]# fdisk /dev/sdc
Device contains neither a valid DOS partition table, nor Sun, SGI or OSF disklabel
Building a new DOS disklabel. Changes will remain in memory only,
until you decide to write them. After that, of course, the previous
content won't be recoverable.

The number of cylinders for this disk is set to 1305.
There is nothing wrong with that, but this is larger than 1024,
and could in certain setups cause problems with:
1) software that runs at boot time (e.g., old versions of LILO)
2) booting and partitioning software from other OSs
(e.g., DOS FDISK, OS/2 FDISK)
Warning: invalid flag 0x0000 of partition table 4 will be corrected by w(rite)

Command (m for help): n
Command action
e extended
p primary partition (1-4)
p
Partition number (1-4): 1
First cylinder (1-1305, default 1):
Using default value 1
Last cylinder or +size or +sizeM or +sizeK (1-1305, default 1305):
Using default value 1305

Command (m for help): w
The partition table has been altered!

Calling ioctl() to re-read partition table.
Syncing disks.

[root@plutone ~]# fdisk -l

Disk /dev/sda: 21.4 GB, 21474836480 bytes
255 heads, 63 sectors/track, 2610 cylinders
Units = cylinders of 16065 * 512 = 8225280 bytes

Device Boot Start End Blocks Id System
/dev/sda1 * 1 25 200781 83 Linux
/dev/sda2 26 286 2096482+ 82 Linux swap / Solaris
/dev/sda3 287 2610 18667530 83 Linux

Disk /dev/sdb: 10.7 GB, 10737418240 bytes
255 heads, 63 sectors/track, 1305 cylinders
Units = cylinders of 16065 * 512 = 8225280 bytes

Device Boot Start End Blocks Id System
/dev/sdb1 1 1305 10482381 83 Linux

Disk /dev/sdc: 10.7 GB, 10737418240 bytes
255 heads, 63 sectors/track, 1305 cylinders
Units = cylinders of 16065 * 512 = 8225280 bytes

Device Boot Start End Blocks Id System
/dev/sdc1 1 1305 10482381 83 Linux

[root@plutone ~]# mkfs.ext3 /dev/sdc1
mke2fs 1.39 (29-May-2006)
Filesystem label=
OS type: Linux
Block size=4096 (log=2)
Fragment size=4096 (log=2)
1310720 inodes, 2620595 blocks
131029 blocks (5.00%) reserved for the super user
First data block=0
Maximum filesystem blocks=2684354560
80 block groups
32768 blocks per group, 32768 fragments per group
16384 inodes per group
Superblock backups stored on blocks:
32768, 98304, 163840, 229376, 294912, 819200, 884736, 1605632

Writing inode tables: done
Creating journal (32768 blocks): done
Writing superblocks and filesystem accounting information: done

This filesystem will be automatically checked every 37 mounts or
180 days, whichever comes first. Use tune2fs -c or -i to override.

I then edited /etc/fstab and added the following line:
/dev/sdc1 /u03 ext3 defaults 1 1

[root@plutone ~]# mkdir /u03
[root@plutone ~]# mount /u03

[root@plutone ~]# df -h
Filesystem Size Used Avail Use% Mounted on
/dev/sda3 18G 16G 794M 96% /
/dev/sda1 190M 18M 163M 10% /boot
tmpfs 617M 0 617M 0% /dev/shm
/dev/sdb1 9.9G 7.6G 1.9G 81% /u02
/dev/sdc1 9.9G 151M 9.2G 2% /u03

Ok now I heve space to continue. Now it's time to add two dedicated local listeners (tns is an alias to go on the directory containing listener.ora and tnsnames.ora files):
[oracle@plutone DB11G]$ tns
[oracle@plutone admin]$ vi listener.ora

# listener.ora Network Configuration File: /u01/app/oracle/product/11.2.0/db_1/network/admin/listener.ora
# Generated by Oracle configuration tools.

SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = DB11G)
(ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_1/)
(SID_NAME = DB11G)
)
(SID_DESC =
(GLOBAL_DBNAME = DUP11G)
(ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_1/)
(SID_NAME = DUP11G)
)
)

LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
(ADDRESS = (PROTOCOL = TCP)(HOST = plutone)(PORT = 1521))
)
)

Add the following informations (adding of course DUP11G service) on tnsnames.ora:
[oracle@plutone admin]$ vi tnsnames.ora

# tnsnames.ora Network Configuration File: /u01/app/oracle/product/11.2.0/db_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.

DUP11G =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = plutone)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = DUP11G)
)
)

DB11G =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = plutone)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = DB11G)
)
)

Now it's time to create a init.ora file for DUP11G.
[oracle@plutone dbs]$ cd /u01/app/oracle/product/11.2.0/db_1/dbs

[oracle@plutone dbs]$ strings spfileDB11G.ora
DB11G.__db_cache_size=184549376
DB11G.__java_pool_size=4194304
DB11G.__large_pool_size=4194304
DB11G.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment
DB11G.__pga_aggregate_target=218103808
DB11G.__sga_target=411041792
DB11G.__shared_io_pool_size=0
DB11G.__shared_pool_size=201326592
DB11G.__streams_pool_size=4194304
*.audit_file_dest='/u01/app/oracle/admin/DB11G/adump'
*.audit_trail='db'
*.compatible='11.2.0.0.0'
*.control_files='/DATA/DB11G/control01.ctl','/u01/app/or
acle/flash_recovery_area/DB11G/control02.ctl'
*.db_block_size=8192
*.db_domain=''
*.db_name='DB11G'
*.db_recovery_file_dest_size=4070572032
*.db_recovery_file_dest='/u02/FRA11G'
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=DB11GXDB)'
*.memory_target=629145600
*.open_cursors=300
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.undo_tablespace='UNDOTBS1'

[oracle@plutone dbs]$ strings spfileDB11G.ora > initDUP11G.ora

I edited the spooled init file to accomplish my needings (and YES.. I know I don't have to put on the same mount point all my control files!! :) )
[oracle@plutone dbs]$ vi initDUP11G.ora
*.compatible='11.2.0.0.0'
*.control_files='/u03/DUP11G/control01.ctl','/u03/DUP11G/control02.ctl'
*.db_block_size=8192
*.db_name='DUP11G'
*.remote_login_passwordfile='EXCLUSIVE'
db_file_name_convert = '/DATA/DB11G','/u03/DUP11G'
log_file_name_convert = '/DATA/DB11G','/u03/DUP11G'

Let's start DUP11G in nomount mode:
[oracle@plutone dbs]$ export ORACLE_SID=DUP11G
[oracle@plutone dbs]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Fri Sep 24 07:25:00 2010

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

Connected to an idle instance.

idle> startup nomount pfile=/u01/app/oracle/product/11.2.0/db_1/dbs/initDUP11G.ora
ORACLE instance started.

Total System Global Area 217157632 bytes
Fixed Size 2211928 bytes
Variable Size 159387560 bytes
Database Buffers 50331648 bytes
Redo Buffers 5226496 bytes

Let's start the target database:
[oracle@plutone admin]$ export ORACLE_SID=DB11G
[oracle@plutone admin]$ ps -ef|grep smon
oracle 6702 1 0 07:35 ? 00:00:00 ora_smon_DUP11G
oracle 10411 9789 0 09:35 pts/4 00:00:00 grep smon
[oracle@plutone admin]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Fri Sep 24 09:35:54 2010

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

Connected to an idle instance.

idle> startup
ORACLE instance started.

Total System Global Area 626327552 bytes
Fixed Size 2215944 bytes
Variable Size 432017400 bytes
Database Buffers 184549376 bytes
Redo Buffers 7544832 bytes
Database mounted.
Database opened.
idle> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

Now let's try to execute the duplicate command:
[oracle@plutone admin]$ export ORACLE_SID=DUP11G
[oracle@plutone admin]$ rman

Recovery Manager: Release 11.2.0.1.0 - Production on Fri Sep 24 09:36:56 2010

Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.

RMAN> connect target sys/oracle@DB11G

connected to target database: DB11G (DBID=197032563)

RMAN> connect auxiliary /

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-04006: error from auxiliary database: ORA-12541: TNS:no listener

RMAN> exit

Recovery Manager complete.

Ok.. I forgot to start the listener with DUP11G informations.

[oracle@plutone dbs]$ lsnrctl start

And let's continue with the second attempt to duplicate DB11G to DUP11G (the scripts following the last command I issued "duplicate target database to DUP11G" are run by Oracle itsself.... I mean automatically!!!):
[oracle@plutone admin]$ rman

Recovery Manager: Release 11.2.0.1.0 - Production on Fri Sep 24 09:36:56 2010

Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.

RMAN> connect target sys/oracle@DB11G

connected to target database: DB11G (DBID=197032563)

RMAN> connect auxiliary /

connected to auxiliary database: DUP11G (not mounted)

RMAN> duplicate target database to DUP11G;

Starting Duplicate Db at 2010-09-24:09:44:19
using target database control file instead of recovery catalog
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=96 device type=DISK

contents of Memory Script:
{
sql clone "create spfile from memory";
}
executing Memory Script

sql statement: create spfile from memory

contents of Memory Script:
{
shutdown clone immediate;
startup clone nomount;
}
executing Memory Script

Oracle instance shut down

connected to auxiliary database (not started)
Oracle instance started

Total System Global Area 217157632 bytes

Fixed Size 2211928 bytes
Variable Size 159387560 bytes
Database Buffers 50331648 bytes
Redo Buffers 5226496 bytes

contents of Memory Script:
{
sql clone "alter system set db_name =
''DB11G'' comment=
''Modified by RMAN duplicate'' scope=spfile";
sql clone "alter system set db_unique_name =
''DUP11G'' comment=
''Modified by RMAN duplicate'' scope=spfile";
shutdown clone immediate;
startup clone force nomount
restore clone primary controlfile;
alter clone database mount;
}
executing Memory Script

sql statement: alter system set db_name = ''DB11G'' comment= ''Modified by RMAN duplicate'' scope=spfile

sql statement: alter system set db_unique_name = ''DUP11G'' comment= ''Modified by RMAN duplicate'' scope=spfile

Oracle instance shut down

Oracle instance started

Total System Global Area 217157632 bytes

Fixed Size 2211928 bytes
Variable Size 159387560 bytes
Database Buffers 50331648 bytes
Redo Buffers 5226496 bytes

Starting restore at 2010-09-24:09:44:37
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=95 device type=DISK

channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: restoring control file
channel ORA_AUX_DISK_1: reading from backup piece /u02/FRA11G/DB11G/autobackup/2010_09_23/o1_mf_s_730489747_69pxqqxd_.bkp
channel ORA_AUX_DISK_1: piece handle=/u02/FRA11G/DB11G/autobackup/2010_09_23/o1_mf_s_730489747_69pxqqxd_.bkp tag=TAG20100923T174907
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:07
output file name=/u03/DUP11G/control01.ctl
output file name=/u03/DUP11G/control02.ctl
Finished restore at 2010-09-24:09:44:46

database mounted

contents of Memory Script:
{
set until scn 1223173;
set newname for datafile 1 to
"/u03/DUP11G/system01.dbf";
set newname for datafile 2 to
"/u03/DUP11G/sysaux01.dbf";
set newname for datafile 3 to
"/u03/DUP11G/undotbs01.dbf";
set newname for datafile 4 to
"/u03/DUP11G/users01.dbf";
set newname for datafile 5 to
"/u03/DUP11G/example01.dbf";
set newname for datafile 6 to
"/u03/DUP11G/soe.dbf";
restore
clone database
;
}
executing Memory Script

executing command: SET until clause

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

Starting restore at 2010-09-24:09:44:52
using channel ORA_AUX_DISK_1

channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00001 to /u03/DUP11G/system01.dbf
channel ORA_AUX_DISK_1: restoring datafile 00002 to /u03/DUP11G/sysaux01.dbf
channel ORA_AUX_DISK_1: restoring datafile 00003 to /u03/DUP11G/undotbs01.dbf
channel ORA_AUX_DISK_1: restoring datafile 00004 to /u03/DUP11G/users01.dbf
channel ORA_AUX_DISK_1: restoring datafile 00005 to /u03/DUP11G/example01.dbf
channel ORA_AUX_DISK_1: restoring datafile 00006 to /u03/DUP11G/soe.dbf
channel ORA_AUX_DISK_1: reading from backup piece /u02/FRA11G/DB11G/backupset/2010_09_23/o1_mf_nnndf_TAG20100923T174235_69pxccs4_.bkp
channel ORA_AUX_DISK_1: piece handle=/u02/FRA11G/DB11G/backupset/2010_09_23/o1_mf_nnndf_TAG20100923T174235_69pxccs4_.bkp tag=TAG20100923T174235
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:05:06
Finished restore at 2010-09-24:09:49:58

contents of Memory Script:
{
switch clone datafile all;
}
executing Memory Script

datafile 1 switched to datafile copy
input datafile copy RECID=8 STAMP=730547400 file name=/u03/DUP11G/system01.dbf
datafile 2 switched to datafile copy
input datafile copy RECID=9 STAMP=730547400 file name=/u03/DUP11G/sysaux01.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=10 STAMP=730547400 file name=/u03/DUP11G/undotbs01.dbf
datafile 4 switched to datafile copy
input datafile copy RECID=11 STAMP=730547401 file name=/u03/DUP11G/users01.dbf
datafile 5 switched to datafile copy
input datafile copy RECID=12 STAMP=730547401 file name=/u03/DUP11G/example01.dbf
datafile 6 switched to datafile copy
input datafile copy RECID=13 STAMP=730547401 file name=/u03/DUP11G/soe.dbf

contents of Memory Script:
{
set until scn 1223173;
recover
clone database
delete archivelog
;
}
executing Memory Script

executing command: SET until clause

Starting recover at 2010-09-24:09:50:04
using channel ORA_AUX_DISK_1

starting media recovery

archived log for thread 1 with sequence 54 is already on disk as file /u02/FRA11G/DB11G/archivelog/2010_09_23/o1_mf_1_54_69pxqj3g_.arc
archived log file name=/u02/FRA11G/DB11G/archivelog/2010_09_23/o1_mf_1_54_69pxqj3g_.arc thread=1 sequence=54
media recovery complete, elapsed time: 00:00:02
Finished recover at 2010-09-24:09:50:11

contents of Memory Script:
{
shutdown clone immediate;
startup clone nomount;
sql clone "alter system set db_name =
''DUP11G'' comment=
''Reset to original value by RMAN'' scope=spfile";
sql clone "alter system reset db_unique_name scope=spfile";
shutdown clone immediate;
startup clone nomount;
}
executing Memory Script

database dismounted
Oracle instance shut down

connected to auxiliary database (not started)
Oracle instance started

Total System Global Area 217157632 bytes

Fixed Size 2211928 bytes
Variable Size 159387560 bytes
Database Buffers 50331648 bytes
Redo Buffers 5226496 bytes

sql statement: alter system set db_name = ''DUP11G'' comment= ''Reset to original value by RMAN'' scope=spfile

sql statement: alter system reset db_unique_name scope=spfile

Oracle instance shut down

connected to auxiliary database (not started)
Oracle instance started

Total System Global Area 217157632 bytes

Fixed Size 2211928 bytes
Variable Size 159387560 bytes
Database Buffers 50331648 bytes
Redo Buffers 5226496 bytes
sql statement: CREATE CONTROLFILE REUSE SET DATABASE "DUP11G" RESETLOGS ARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 292
LOGFILE
GROUP 1 ( '/u03/DUP11G/redo01.log' ) SIZE 50 M REUSE,
GROUP 2 ( '/u03/DUP11G/redo02.log' ) SIZE 50 M REUSE,
GROUP 3 ( '/u03/DUP11G/redo03.log' ) SIZE 50 M REUSE
DATAFILE
'/u03/DUP11G/system01.dbf'
CHARACTER SET WE8MSWIN1252

contents of Memory Script:
{
set newname for tempfile 1 to
"/u03/DUP11G/temp01.dbf";
switch clone tempfile all;
catalog clone datafilecopy "/u03/DUP11G/sysaux01.dbf",
"/u03/DUP11G/undotbs01.dbf",
"/u03/DUP11G/users01.dbf",
"/u03/DUP11G/example01.dbf",
"/u03/DUP11G/soe.dbf";
switch clone datafile all;
}
executing Memory Script

executing command: SET NEWNAME

renamed tempfile 1 to /u03/DUP11G/temp01.dbf in control file

cataloged datafile copy
datafile copy file name=/u03/DUP11G/sysaux01.dbf RECID=1 STAMP=730547441
cataloged datafile copy
datafile copy file name=/u03/DUP11G/undotbs01.dbf RECID=2 STAMP=730547441
cataloged datafile copy
datafile copy file name=/u03/DUP11G/users01.dbf RECID=3 STAMP=730547441
cataloged datafile copy
datafile copy file name=/u03/DUP11G/example01.dbf RECID=4 STAMP=730547441
cataloged datafile copy
datafile copy file name=/u03/DUP11G/soe.dbf RECID=5 STAMP=730547441

datafile 2 switched to datafile copy
input datafile copy RECID=1 STAMP=730547441 file name=/u03/DUP11G/sysaux01.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=2 STAMP=730547441 file name=/u03/DUP11G/undotbs01.dbf
datafile 4 switched to datafile copy
input datafile copy RECID=3 STAMP=730547441 file name=/u03/DUP11G/users01.dbf
datafile 5 switched to datafile copy
input datafile copy RECID=4 STAMP=730547441 file name=/u03/DUP11G/example01.dbf
datafile 6 switched to datafile copy
input datafile copy RECID=5 STAMP=730547441 file name=/u03/DUP11G/soe.dbf

contents of Memory Script:
{
Alter clone database open resetlogs;
}
executing Memory Script

database opened
Finished Duplicate Db at 2010-09-24:09:51:10

RMAN> exit

Recovery Manager complete.

How many instances are running and are they the same ?
[oracle@plutone admin]$ ps -ef|grep smon
oracle 10511 1 0 09:36 ? 00:00:01 ora_smon_DB11G
oracle 11303 1 0 09:50 ? 00:00:00 ora_smon_DUP11G
oracle 11421 9789 0 09:51 pts/4 00:00:00 grep smon

[oracle@plutone admin]$ SQL

SQL*Plus: Release 11.2.0.1.0 Production on Fri Sep 24 09:52:25 2010

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

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

sys@DUP11G> set pagesize 999
sys@DUP11G> select username from dba_users order by created;

USERNAME
------------------------------
SYS
SYSTEM
OUTLN
DIP
ORACLE_OCM
DBSNMP
APPQOSSYS
WMSYS
EXFSYS
CTXSYS
ANONYMOUS
XDB
XS$NULL
ORDDATA
MDSYS
ORDPLUGINS
ORDSYS
SI_INFORMTN_SCHEMA
OLAPSYS
MDDATA
SPATIAL_WFS_ADMIN_USR
SPATIAL_CSW_ADMIN_USR
SYSMAN
MGMT_VIEW
APEX_030200
FLOWS_FILES
APEX_PUBLIC_USER
OWBSYS
OWBSYS_AUDIT
SCOTT
SH
OE
HR
PM
BI
IX
STORE
SOE

38 rows selected.

sys@DUP11G> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@plutone admin]$ export ORACLE_SID=DB11G
[oracle@plutone admin]$ SQL

SQL*Plus: Release 11.2.0.1.0 Production on Fri Sep 24 10:02:33 2010

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

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

sys@DB11G> set pagesize 999
sys@DB11G> select username from dba_users order by created;

USERNAME
------------------------------
SYS
SYSTEM
OUTLN
DIP
ORACLE_OCM
DBSNMP
APPQOSSYS
WMSYS
EXFSYS
CTXSYS
ANONYMOUS
XDB
XS$NULL
ORDDATA
MDSYS
ORDPLUGINS
ORDSYS
SI_INFORMTN_SCHEMA
OLAPSYS
MDDATA
SPATIAL_WFS_ADMIN_USR
SPATIAL_CSW_ADMIN_USR
SYSMAN
MGMT_VIEW
APEX_030200
FLOWS_FILES
APEX_PUBLIC_USER
OWBSYS
OWBSYS_AUDIT
SCOTT
SH
OE
HR
PM
BI
IX
STORE
SOE

38 rows selected.

sys@DB11G> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@plutone admin]$ cd /u03/DUP11G/
[oracle@plutone DUP11G]$ ll
total 3949168
-rw-r----- 1 oracle oinstall 7872512 Sep 24 09:50 users01.dbf
-rw-r----- 1 oracle oinstall 1918902272 Sep 24 09:50 soe.dbf
-rw-r----- 1 oracle oinstall 52429312 Sep 24 09:50 redo03.log
-rw-r----- 1 oracle oinstall 52429312 Sep 24 09:50 redo02.log
-rw-r----- 1 oracle oinstall 104865792 Sep 24 09:50 example01.dbf
-rw-r----- 1 oracle oinstall 195043328 Sep 24 09:52 temp01.dbf
-rw-r----- 1 oracle oinstall 592453632 Sep 24 10:02 undotbs01.dbf
-rw-r----- 1 oracle oinstall 713039872 Sep 24 10:02 system01.dbf
-rw-r----- 1 oracle oinstall 524296192 Sep 24 10:02 sysaux01.dbf
-rw-r----- 1 oracle oinstall 52429312 Sep 24 10:02 redo01.log
-rw-r----- 1 oracle oinstall 10076160 Sep 24 10:03 control02.ctl
-rw-r----- 1 oracle oinstall 10076160 Sep 24 10:03 control01.ctl

At this point you can remove from DUP11G database any reference to db_file_name_convert and log_file_name_convert parameters

Ok... that's all

Installing Oracle 11gR2 (11.2.0.1.0) on CentOS 5.5

2010-08-09T14:19:00.015+01:00

This post is more about to test some features such as Video recording provided by vmware than to explain how to install Oracle Database 11gR2 on CentOS. Anyway you can see how it was easy to complete those steps. As soon as possible I'll write some lines about what files I have modified on CentOS to run a successful installation (I mean.. rpm required, sysctl parameters and so on)

Updated:
rpm list
rpm -Uvh binutils-2.*
rpm -Uvh compat-libstdc++-33*
rpm -Uvh compat-libstdc++-33*.i386.rpm
rpm -Uvh elfutils-libelf*
rpm -Uvh gcc-4.*
rpm -Uvh gcc-c++-4.*
rpm -Uvh glibc-2.*
rpm -Uvh glibc-common-2.*
rpm -Uvh glibc-devel-2.*
rpm -Uvh glibc-headers-2.*
rpm -Uvh ksh*
rpm -Uvh libaio-0.*
rpm -Uvh libaio-devel-0.*
rpm -Uvh libgomp-4.*
rpm -Uvh libgcc-4.*
rpm -Uvh libstdc++-4.*
rpm -Uvh libstdc++-devel-4.*
rpm -Uvh make-3.*
rpm -Uvh sysstat-7.*
rpm -Uvh unixODBC-2.*
rpm -Uvh unixODBC-devel-2.*
rpm -Uvh pdksh*

the /etc/sysctl.conf file
fs.aio-max-nr = 1048576
fs.file-max = 6815744
kernel.shmall = 2097152
kernel.shmmax = 536870912
kernel.shmmni = 4096
# semaphores: semmsl, semmns, semopm, semmni
kernel.sem = 250 32000 100 128
net.ipv4.ip_local_port_range = 9000 65500
net.core.rmem_default=4194304
net.core.rmem_max=4194304
net.core.wmem_default=262144
net.core.wmem_max=1048586

the /etc/security/limits.conf file
oracle soft nproc 2047
oracle hard nproc 16384
oracle soft nofile 1024
oracle hard nofile 65536

the /etc/pam.d/login file
#%PAM-1.0
auth [user_unknown=ignore success=ok ignore=ignore default=bad] pam_securetty.so
auth include system-auth
account required pam_nologin.so
account include system-auth
password include system-auth
# pam_selinux.so close should be the first session rule
session required pam_selinux.so close
session include system-auth
session required pam_loginuid.so
session optional pam_console.so
# pam_selinux.so open should only be followed by sessions to be executed in the user context
session required pam_selinux.so open
session optional pam_keyinit.so force revoke
session required pam_limits.so

the oracle user .bash_profile
# .bash_profile
# Get the aliases and functions
if [ -f ~/.bashrc ]; then
. ~/.bashrc
fi
# User specific environment and startup programs
PATH=$PATH:$HOME/bin
export PATH
# Oracle Settings
TMP=/tmp; export TMP
TMPDIR=$TMP; export TMPDIR
ORACLE_UNQNAME=DB11G; export ORACLE_UNQNAME
ORACLE_BASE=/u01/app/oracle; export ORACLE_BASE
ORACLE_HOME=$ORACLE_BASE/product/11.2.0/db_1; export ORACLE_HOME
ORACLE_SID=DB11G; export ORACLE_SID
ORACLE_TERM=xterm; export ORACLE_TERM
PATH=/usr/sbin:$PATH; export PATH
PATH=$ORACLE_HOME/bin:$PATH; export PATH
LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib; export LD_LIBRARY_PATH
CLASSPATH=$ORACLE_HOME/JRE:$ORACLE_HOME/jlib:$ORACLE_HOME/rdbms/jlib; export CLASSPATH
if [ $USER = "oracle" ]; then
if [ $SHELL = "/bin/ksh" ]; then
ulimit -p 16384
ulimit -n 65536
else
ulimit -u 16384 -n 65536
fi
fi

Executing the runInstaller

runInstaller finished to run

These steps describe how to install a listener and a database after the Oracle Database software is installed-

Final steps on Enterprise Manager Db console

Regards

11gR2: setup a larger /dev/shm after ORA-00845: MEMORY_TARGET not supported on this system

2010-04-28T15:18:00.006+01:00

If you get the following error

ORA-00845: MEMORY_TARGET not supported on this system

while using 11g and the AMM feature... it's time to increase your memory mapped files on /dev/shm.

As usually I've setup my /dev/shm devices at about half (4GB) of physical memory (8GB). I'm on a ProLiant DL180G5 and using OEL5.4. So the following was my file system configuration:
[root@rac31 ~]# df -h
Filesystem Size Used Avail Use% Mounted on
/dev/cciss/c0d0p2 267G 25G 228G 10% /
/dev/cciss/c0d0p1 99M 12M 82M 13% /boot
tmpfs 4.0G 192M 3.8G 5% /dev/shm
/dev/emcpowerb1 134G 1.1G 133G 1% /u02/ocfs2
/dev/emcpowerd1 5.0G 298M 4.8G 6% /u02/ocfs2_mirror

And my /etc/fstab:
[root@rac31 ~]# more /etc/fstab
LABEL=/1 / ext3 defaults 1 1
LABEL=/boot1 /boot ext3 defaults 1 2
tmpfs /dev/shm tmpfs defaults 0 0
devpts /dev/pts devpts gid=5,mode=620 0 0
sysfs /sys sysfs defaults 0 0
proc /proc proc defaults 0 0
LABEL=SW-cciss/c0d0p3 swap swap defaults 0 0
/dev/emcpowerb1 /u02/ocfs2/ ocfs2 _netdev,datavolume,nointr 0 0
/dev/emcpowerd1 /u02/ocfs2_mirror/ ocfs2 _netdev,datavolume,nointr 0 0

You have to edit /etc/fstab and change the previous tmpfs line with the following one:
[root@rac31 ~]# vi /etc/fstab
LABEL=/1 / ext3 defaults 1 1
LABEL=/boot1 /boot ext3 defaults 1 2
tmpfs /dev/shm tmpfs size=6g 0 0
devpts /dev/pts devpts gid=5,mode=620 0 0
sysfs /sys sysfs defaults 0 0
proc /proc proc defaults 0 0
LABEL=SW-cciss/c0d0p3 swap swap defaults 0 0
/dev/emcpowerb1 /u02/ocfs2/ ocfs2 _netdev,datavolume,nointr 0 0
/dev/emcpowerd1 /u02/ocfs2_mirror/ ocfs2 _netdev,datavolume,nointr 0 0

I then stopped CRS on only that node with the following command:
[root@rac31 ~]# /u01/app/11.2.0/grid/bin/crsctl stop crs

CRS-2791: Starting shutdown of Oracle High Availability Services-managed resources on 'rac31'
CRS-2673: Attempting to stop 'ora.crsd' on 'rac31'
CRS-2790: Starting shutdown of Cluster Ready Services-managed resources on 'rac31'
CRS-2673: Attempting to stop 'ora.LISTENER.lsnr' on 'rac31'
CRS-2673: Attempting to stop 'ora.LISTENER_SCAN1.lsnr' on 'rac31'
CRS-2673: Attempting to stop 'ora.DATA_RAC3.dg' on 'rac31'
CRS-2673: Attempting to stop 'ora.FRA_RAC3.dg' on 'rac31'
CRS-2673: Attempting to stop 'ora.registry.acfs' on 'rac31'
CRS-2677: Stop of 'ora.DATA_RAC3.dg' on 'rac31' succeeded
CRS-2677: Stop of 'ora.LISTENER.lsnr' on 'rac31' succeeded
CRS-2673: Attempting to stop 'ora.rac31.vip' on 'rac31'
CRS-2677: Stop of 'ora.LISTENER_SCAN1.lsnr' on 'rac31' succeeded
CRS-2673: Attempting to stop 'ora.scan1.vip' on 'rac31'
CRS-2677: Stop of 'ora.rac31.vip' on 'rac31' succeeded
CRS-2672: Attempting to start 'ora.rac31.vip' on 'rac32'
CRS-2677: Stop of 'ora.scan1.vip' on 'rac31' succeeded
CRS-2672: Attempting to start 'ora.scan1.vip' on 'rac32'
CRS-2677: Stop of 'ora.FRA_RAC3.dg' on 'rac31' succeeded
CRS-2677: Stop of 'ora.registry.acfs' on 'rac31' succeeded
CRS-2673: Attempting to stop 'ora.asm' on 'rac31'
CRS-2677: Stop of 'ora.asm' on 'rac31' succeeded
CRS-2676: Start of 'ora.rac31.vip' on 'rac32' succeeded
CRS-2676: Start of 'ora.scan1.vip' on 'rac32' succeeded
CRS-2672: Attempting to start 'ora.LISTENER_SCAN1.lsnr' on 'rac32'
CRS-2676: Start of 'ora.LISTENER_SCAN1.lsnr' on 'rac32' succeeded
CRS-2673: Attempting to stop 'ora.eons' on 'rac31'
CRS-2673: Attempting to stop 'ora.ons' on 'rac31'
CRS-2677: Stop of 'ora.ons' on 'rac31' succeeded
CRS-2673: Attempting to stop 'ora.net1.network' on 'rac31'
CRS-2677: Stop of 'ora.net1.network' on 'rac31' succeeded
CRS-2677: Stop of 'ora.eons' on 'rac31' succeeded
CRS-2792: Shutdown of Cluster Ready Services-managed resources on 'rac31' has completed
CRS-2677: Stop of 'ora.crsd' on 'rac31' succeeded
CRS-2673: Attempting to stop 'ora.drivers.acfs' on 'rac31'
CRS-2673: Attempting to stop 'ora.gpnpd' on 'rac31'
CRS-2673: Attempting to stop 'ora.asm' on 'rac31'
CRS-2673: Attempting to stop 'ora.cssdmonitor' on 'rac31'
CRS-2673: Attempting to stop 'ora.ctssd' on 'rac31'
CRS-2673: Attempting to stop 'ora.evmd' on 'rac31'
CRS-2673: Attempting to stop 'ora.mdnsd' on 'rac31'
CRS-2677: Stop of 'ora.cssdmonitor' on 'rac31' succeeded
CRS-2677: Stop of 'ora.mdnsd' on 'rac31' succeeded
CRS-2677: Stop of 'ora.gpnpd' on 'rac31' succeeded
CRS-2677: Stop of 'ora.evmd' on 'rac31' succeeded
CRS-2677: Stop of 'ora.drivers.acfs' on 'rac31' succeeded
CRS-2677: Stop of 'ora.ctssd' on 'rac31' succeeded
CRS-2677: Stop of 'ora.asm' on 'rac31' succeeded
CRS-2673: Attempting to stop 'ora.cssd' on 'rac31'
CRS-2677: Stop of 'ora.cssd' on 'rac31' succeeded
CRS-2673: Attempting to stop 'ora.diskmon' on 'rac31'
CRS-2673: Attempting to stop 'ora.gipcd' on 'rac31'
CRS-2677: Stop of 'ora.gipcd' on 'rac31' succeeded
CRS-2677: Stop of 'ora.diskmon' on 'rac31' succeeded
CRS-2793: Shutdown of Oracle High Availability Services-managed resources on 'rac31' has completed
CRS-4133: Oracle High Availability Services has been stopped.

No Oracle processes are now available.. I can simply reboot (yes I could mount it.. but I wanted to test it after a reboot)
[root@rac31 ~]# ps -ef|grep oracle
oracle 7079 1 0 Apr16 ? 00:00:01 /u01/app/oracle/product/11.2.0/dbhome_1/ccr/bin/nmz -cron -silent
root 28585 25068 0 17:42 pts/0 00:00:00 grep oracle

[root@rac31 ~]# reboot

Broadcast message from root (pts/0) (Tue Apr 27 17:42:42 2010):

The system is going down for reboot NOW!

And just few minutes after the instance (there was only the ASM one) started up I looked at my file system again.
[root@rac31 ~]# df -h
Filesystem Size Used Avail Use% Mounted on
/dev/cciss/c0d0p2 267G 25G 228G 10% /
/dev/cciss/c0d0p1 99M 12M 82M 13% /boot
tmpfs 6.0G 192M 5.9G 4% /dev/shm
/dev/emcpowerb1 134G 1.1G 133G 1% /u02/ocfs2
/dev/emcpowerd1 5.0G 295M 4.8G 6% /u02/ocfs2_mirror

Ok... it mounted my devices and it was now 6GB.

Now on the other node you have to repeat the same steps.
[root@rac32 ~]# vi /etc/fstab
LABEL=/ / ext3 defaults 1 1
LABEL=/boot /boot ext3 defaults 1 2
tmpfs /dev/shm tmpfs size=6g 0 0
devpts /dev/pts devpts gid=5,mode=620 0 0
sysfs /sys sysfs defaults 0 0
proc /proc proc defaults 0 0
LABEL=SW-cciss/c0d0p3 swap swap defaults 0 0
/dev/emcpowerb1 /u02/ocfs2/ ocfs2 _netdev,datavolume,nointr 0 0
/dev/emcpowerd1 /u02/ocfs2_mirror/ ocfs2 _netdev,datavolume,nointr 0 0

Stop the CRS.
[root@rac32 ~]# /u01/app/11.2.0/grid/bin/crsctl stop crs

CRS-2791: Starting shutdown of Oracle High Availability Services-managed resources on 'rac32'
CRS-2673: Attempting to stop 'ora.crsd' on 'rac32'
CRS-2790: Starting shutdown of Cluster Ready Services-managed resources on 'rac32'
CRS-2673: Attempting to stop 'ora.LISTENER.lsnr' on 'rac32'
CRS-2673: Attempting to stop 'ora.DATA_RAC3.dg' on 'rac32'
CRS-2673: Attempting to stop 'ora.FRA_RAC3.dg' on 'rac32'
CRS-2673: Attempting to stop 'ora.registry.acfs' on 'rac32'
CRS-2673: Attempting to stop 'ora.LISTENER_SCAN2.lsnr' on 'rac32'
CRS-2677: Stop of 'ora.DATA_RAC3.dg' on 'rac32' succeeded
CRS-2677: Stop of 'ora.LISTENER.lsnr' on 'rac32' succeeded
CRS-2673: Attempting to stop 'ora.rac32.vip' on 'rac32'
CRS-2677: Stop of 'ora.LISTENER_SCAN2.lsnr' on 'rac32' succeeded
CRS-2673: Attempting to stop 'ora.scan2.vip' on 'rac32'
CRS-2677: Stop of 'ora.rac32.vip' on 'rac32' succeeded
CRS-2672: Attempting to start 'ora.rac32.vip' on 'rac31'
CRS-2677: Stop of 'ora.scan2.vip' on 'rac32' succeeded
CRS-2672: Attempting to start 'ora.scan2.vip' on 'rac31'
CRS-2677: Stop of 'ora.registry.acfs' on 'rac32' succeeded
CRS-2677: Stop of 'ora.FRA_RAC3.dg' on 'rac32' succeeded
CRS-2673: Attempting to stop 'ora.asm' on 'rac32'
CRS-2677: Stop of 'ora.asm' on 'rac32' succeeded
CRS-2676: Start of 'ora.rac32.vip' on 'rac31' succeeded
CRS-2676: Start of 'ora.scan2.vip' on 'rac31' succeeded
CRS-2672: Attempting to start 'ora.LISTENER_SCAN2.lsnr' on 'rac31'
CRS-2676: Start of 'ora.LISTENER_SCAN2.lsnr' on 'rac31' succeeded
CRS-2673: Attempting to stop 'ora.ons' on 'rac32'
CRS-2673: Attempting to stop 'ora.eons' on 'rac32'
CRS-2677: Stop of 'ora.ons' on 'rac32' succeeded
CRS-2673: Attempting to stop 'ora.net1.network' on 'rac32'
CRS-2677: Stop of 'ora.net1.network' on 'rac32' succeeded
CRS-2677: Stop of 'ora.eons' on 'rac32' succeeded
CRS-2792: Shutdown of Cluster Ready Services-managed resources on 'rac32' has completed
CRS-2677: Stop of 'ora.crsd' on 'rac32' succeeded
CRS-2673: Attempting to stop 'ora.asm' on 'rac32'
CRS-2673: Attempting to stop 'ora.cssdmonitor' on 'rac32'
CRS-2673: Attempting to stop 'ora.ctssd' on 'rac32'
CRS-2673: Attempting to stop 'ora.evmd' on 'rac32'
CRS-2673: Attempting to stop 'ora.drivers.acfs' on 'rac32'
CRS-2673: Attempting to stop 'ora.mdnsd' on 'rac32'
CRS-2677: Stop of 'ora.cssdmonitor' on 'rac32' succeeded
CRS-2677: Stop of 'ora.evmd' on 'rac32' succeeded
CRS-2677: Stop of 'ora.mdnsd' on 'rac32' succeeded
CRS-2677: Stop of 'ora.ctssd' on 'rac32' succeeded
CRS-2677: Stop of 'ora.drivers.acfs' on 'rac32' succeeded
CRS-2677: Stop of 'ora.asm' on 'rac32' succeeded
CRS-2673: Attempting to stop 'ora.cssd' on 'rac32'
CRS-2677: Stop of 'ora.cssd' on 'rac32' succeeded
CRS-2673: Attempting to stop 'ora.gpnpd' on 'rac32'
CRS-2673: Attempting to stop 'ora.diskmon' on 'rac32'
CRS-2677: Stop of 'ora.gpnpd' on 'rac32' succeeded
CRS-2673: Attempting to stop 'ora.gipcd' on 'rac32'
CRS-2677: Stop of 'ora.gipcd' on 'rac32' succeeded
CRS-2677: Stop of 'ora.diskmon' on 'rac32' succeeded
CRS-2793: Shutdown of Oracle High Availability Services-managed resources on 'rac32' has completed
CRS-4133: Oracle High Availability Services has been stopped.

After a reboot the ASM instance was running again

and my /dev/shm devie was 6GB.
[root@rac32 ~]# df -h
Filesystem Size Used Avail Use% Mounted on
/dev/cciss/c0d0p2 267G 16G 238G 7% /
/dev/cciss/c0d0p1 99M 12M 82M 13% /boot
tmpfs 6.0G 192M 5.9G 4% /dev/shm
/dev/emcpowerb1 134G 1.1G 133G 1% /u02/ocfs2
/dev/emcpowerd1 5.0G 293M 4.8G 6% /u02/ocfs2_mirror

That's all.

11gR2: how to remove file from ASM diskgroup

2010-04-28T14:57:00.006+01:00

I needed to delete some asm files from my previous database installation.

I wanted to use ASMCMD tool so I loaded my ASM environment:
[oracle@rac32 ~]$ . oraenv
ORACLE_SID = [RAC32] ? +ASM2
The Oracle base for ORACLE_HOME=/u01/app/11.2.0/grid is /u01/app/oracle

Then I entered into asmcms tool:
[oracle@rac32 ~]$ asmcmd
ASMCMD> ls
DATA_RAC3/
FRA_RAC3/
ASMCMD> exit
[oracle@nov2211 ~]$ asmcmd -p
ASMCMD [+] > cd DATA_RAC3
ASMCMD [+DATA_RAC3] > ls
RAC3/
ASMCMD [+DATA_RAC3] > cd RAC3
ASMCMD [+DATA_RAC3/RAC3] > ls
ARCHIVELOG/
ASMPARAMETERFILE/
OCRFILE/

Ok .. there are some files and I would like to remove them.
ASMCMD [+DATA_RAC3/RAC3] > rm ARCHIVE*
You may delete multiple files and/or directories.
Are you sure? (y/n) y
ORA-15032: not all alterations performed
ORA-15177: cannot operate on system aliases (DBD ERROR: OCIStmtExecute)

That error could be tied at the metalink note 444812.1 and bug 3751057.8

In that doc id they suggest to run a sql script and recreate the alter diskgroup drop file syntax.
Likely I found the following link (and thanks to Freek D'Hooge) http://www.dba-village.com/village/dvp_scripts.ScriptDetails?ScriptIdA=2403#
that help me to reach my goal.
SQL> column a format a120
SQL> set linesize 120
SQL> select 'alter diskgroup ' || gname || ' drop file ''+' || gname || sys_connect_by_path(aname, '/') || ''';' a
2 from ( select b.name gname, a.parent_index pindex, a.name aname,
3 a.reference_index rindex , a.system_created, a.alias_directory
4 from v$asm_alias a, v$asm_diskgroup b
5 where a.group_number = b.group_number
6 )
7 where alias_directory = 'N'
8 and system_created = 'Y'
9 start with (mod(pindex, power(2, 24))) = 0
10 and rindex in
11 ( select a.reference_index
12 from v$asm_alias a, v$asm_diskgroup b
13 where a.group_number = b.group_number
14 and (mod(a.parent_index, power(2, 24))) = 0
15 and a.name = '&DATABASENAME'
16 )
17 connect by prior rindex = pindex;
Enter value for databasename: RAC3
old 15: and a.name = '&DATABASENAME'
new 15: and a.name = 'RAC3'

A
------------------------------------------------------------------------------------------------------------------------
alter diskgroup DATA_RAC3 drop file '+DATA_RAC3/RAC3/ASMPARAMETERFILE/REGISTRY.253.706985453';
alter diskgroup DATA_RAC3 drop file '+DATA_RAC3/RAC3/OCRFILE/REGISTRY.255.706985457';
alter diskgroup DATA_RAC3 drop file '+DATA_RAC3/RAC3/ARCHIVELOG/2010_01_08/thread_1_seq_81.272.707781651';
alter diskgroup DATA_RAC3 drop file '+DATA_RAC3/RAC3/ARCHIVELOG/2010_01_08/thread_2_seq_30.271.707775999';
alter diskgroup DATA_RAC3 drop file '+DATA_RAC3/RAC3/ARCHIVELOG/2010_01_08/thread_1_seq_80.270.707775999';
alter diskgroup DATA_RAC3 drop file '+DATA_RAC3/RAC3/ARCHIVELOG/2010_01_08/thread_1_seq_82.273.707787599';
...
alter diskgroup DATA_RAC3 drop file '+DATA_RAC3/RAC3/ARCHIVELOG/2010_03_25/thread_1_seq_699.1067.714573049';
alter diskgroup DATA_RAC3 drop file '+DATA_RAC3/RAC3/ARCHIVELOG/2010_03_25/thread_1_seq_700.1058.714573053';
alter diskgroup DATA_RAC3 drop file '+DATA_RAC3/RAC3/ARCHIVELOG/2010_03_25/thread_1_seq_701.1066.714573059';
alter diskgroup DATA_RAC3 drop file '+DATA_RAC3/RAC3/ARCHIVELOG/2010_03_25/thread_2_seq_579.1065.714573061';
alter diskgroup DATA_RAC3 drop file '+DATA_RAC3/RAC3/ARCHIVELOG/2010_03_25/thread_1_seq_702.1064.714573069';
alter diskgroup DATA_RAC3 drop file '+DATA_RAC3/RAC3/ARCHIVELOG/2010_03_25/thread_1_seq_691.1452.714532055';

1176 rows selected.

Then I simply copied and executed the output:
SQL> alter diskgroup DATA_RAC3 drop file '+DATA_RAC3/RAC3/ARCHIVELOG/2010_01_08/thread_1_seq_81.272.707781651';

Diskgroup altered.

[oracle@nov2211 ~]$ asmcmd -p
ASMCMD [+] > lsdg
State Type Rebal Sector Block AU Total_MB Free_MB Req_mir_free_MB Usable_file_MB Offline_disks Voting_files Name
MOUNTED EXTERN N 512 4096 1048576 511993 511882 0 511882 0 N DATA_RAC3/
MOUNTED EXTERN N 512 4096 1048576 511993 511896 0 511896 0 N FRA_RAC3/

That's all

11gR2: adding a mirrored voting disk on OCFS2 file system

2010-04-23T11:45:00.006+01:00

I want to add a mirrored voting disk after migration from ASM to OCFS2.
The following is my current configuration:
/u01/app/11.2.0/grid/bin/crsctl query css votedisk
## STATE File Universal Id File Name Disk group
-- ----- ----------------- --------- ---------
1. ONLINE 353efdf9e50d4f93bf71c6aa84b9e19b (/u02/ocfs2/css/vdisk) []
Located 1 voting disk(s).

Create a new directory on mirrored location

Set the right permissions, as I did:
touch css/vdisk
chown -R oracle.oinstall css/

and check if all is right

Now execute the command to add another voting disk:
/u01/app/11.2.0/grid/bin/crsctl add css votedisk /u02/ocfs2_mirror/css/vdisk
Now formatting voting disk: /u02/ocfs2_mirror/css/vdisk.
CRS-4603: Successful addition of voting disk /u02/ocfs2_mirror/css/vdisk.

As you can see, I have now two voting disk files.
/u01/app/11.2.0/grid/bin/crsctl query css votedisk
## STATE File Universal Id File Name Disk group
-- ----- ----------------- --------- ---------
1. ONLINE 353efdf9e50d4f93bf71c6aa84b9e19b (/u02/ocfs2/css/vdisk) []
2. ONLINE 0c254a5cdcd14f9bbfef2866447e908c (/u02/ocfs2_mirror/css/vdisk) []
Located 2 voting disk(s).

But what about if I want to have a mirrored copy also on Oracle ASM ?
It's simply not possible even if from the documentation I didn't find about it.
/u01/app/11.2.0/grid/bin/crsctl add css votedisk +DATA_RAC3
CRS-4671: This command is not supported for ASM diskgroups.
CRS-4000: Command Add failed, or completed with errors.

The command is not supported. I can mention this documentation note from "Clusterware Administration and Deployment Guide" (e10717.pdf):
Oracle does not support storing the OCR on different storage types simultaneously, such as storing OCR on both Oracle ASM and a shared file system, except during a migration.

That's all.

11gR2: removing a mirrored Oracle Cluster Registry and adding again

2010-04-23T10:38:00.006+01:00

I want to test a scenario where one of my OCR file is lost.

This is what I have done. Now let's have a look at my OCR configuration.

/u01/app/11.2.0/grid/bin/ocrcheck
Status of Oracle Cluster Registry is as follows :
Version : 3
Total space (kbytes) : 262120
Used space (kbytes) : 2708
Available space (kbytes) : 259412
ID : 2044210075
Device/File Name : /u02/ocfs2/ocr/OCRFile
Device/File integrity check succeeded
Device/File Name : /u02/ocfs2_mirror/ocr/OCRFile
Device/File integrity check succeeded

Device/File not configured

Device/File not configured

Device/File not configured

Cluster registry integrity check succeeded

Logical corruption check succeeded

As you can see I've two mirrored OCR files one on /u02/ocfs2/ path and another on /u02/ocfs2_mirror/. For your information, these paths are on two different LUNs formed by several disks and on a RAID-1 configuration

Here is where my mirrored OCR file exists.

Now I delete it

Issuing:
/u01/app/11.2.0/grid/bin/ocrcheck
PROT-602: Failed to retrieve data from the cluster registry
PROC-26: Error while accessing the physical storage

it says it's no more able to get data from OCR, even if my cluster is still up.

Now I try to recreate the "lost" OCR mirrored file

At first I simply tried to add the new OCR file, but I got:
/u01/app/11.2.0/grid/bin/ocrconfig -add /u02/ocfs2_mirror/ocr/OCRFile
PROT-29: The Oracle Cluster Registry location is already configured

Clear... I have to first remove from the configuration the "old" location of lost OCR file and then add it again.
/u01/app/11.2.0/grid/bin/ocrconfig -delete /u02/ocfs2_mirror/ocr/OCRFile
/u01/app/11.2.0/grid/bin/ocrconfig -add /u02/ocfs2_mirror/ocr/OCRFile

As you can see my OCR configuration is mirrored again:
/u01/app/11.2.0/grid/bin/ocrcheck
Status of Oracle Cluster Registry is as follows :
Version : 3
Total space (kbytes) : 262120
Used space (kbytes) : 2708
Available space (kbytes) : 259412
ID : 2044210075
Device/File Name : /u02/ocfs2/ocr/OCRFile
Device/File integrity check succeeded
Device/File Name : /u02/ocfs2_mirror/ocr/OCRFile
Device/File integrity check succeeded

Device/File not configured

Device/File not configured

Device/File not configured

Cluster registry integrity check succeeded

Logical corruption check succeeded

That's all.

11gR2: Migrating Oracle Cluster Registry from Oracle ASM to Other Types of Storage

2010-04-21T16:51:00.008+01:00

The following are the steps to migrate OCR from Oracle ASM to OCFS2. According to "Clusterware Administration and Deployment Guide" (e10717.pdf) run /u01/app/11.2.0/grid/bin/ocrcheck to see the status of cluster registry. Then create a file (just touch it) under the preferred directory and set the right permissions. This is what I did as root user:

touch /u02/ocfs2/ocr/OCRFile
chown root.oinstall /u02/ocfs2/ocr/OCRFile
chmod 640 /u02/ocfs2/ocr/OCRFile

Then add your new OCR file to the final directory:

/u01/app/11.2.0/grid/bin/ocrconfig -add /u02/ocfs2/ocr/OCRFile

and check again the OCR status:

/uo1/app/11.2.0./grid/bin/ocrcheck

Now it's time to remove OCR file from ASM.

Nothing to do on second node.
That's all.

11gR2: Migrating Voting Disks from Oracle ASM to Other Types of Storage

2010-04-21T16:08:00.010+01:00

The following are the steps to migrate voting disk from Oracle ASM to OCFS2. During these steps I found a problem already documented on metalink as a documentation bug, that is documentation is missing some informations.

Run

/u01/app/11.2.0/grid/bin/crsctl query css votedisk

to see the status and location of voting disk (in the following image it's the second command displayed just after ocrcheck command)

Create on the shared OCFS2 file system a directory where relocate the voting disk and set the right permissions. I've created ocr (useful for me for relocate also OCR file on OCFS2 file system) and css directories, owned by oracle:

chown -R oracle.oinstall css

Now it's time to run the following command (on the image below just look at the second command issued. On the first one I've missed to write the voting disk file name):

/u01/app/11.2.0/grid/bin/crsctl replace votedisk /u02/ocfs2/css/vdisk
but I received the following errors:
CRS-4256: Updating the profile
Failed to update the profile with the new discovery string
CRS-4000: Command Replace failed, or completed with errors.

And indeed the voting disk is still on Oracle ASM

Under css directory, a file was created. Having a look on permissions granted on that vdisk file... we can see it's owned by oracle.root.

Delete that file, create a new empty one (just touch it) and grant the following permissions:

chown oracle.oinstall vdisk

Using the previous command it's finally able to replace the voting disk.

Now you can see that the new voting disk is relocated on file system

That's all.

How to install Oracle STATSPACK

2010-03-09T15:38:00.005+01:00

To install STATSPACK utility, I created a new tablespace to associate later to a new user, PERFSTAT created using spcreate.sql script

This script is located at:
/u01/app/oracle/product/10.2.0/db_1/rdbms/admin/spcreate.sql

Log in as SYS user and then run the sql script. It will ask for PERFSTAT password and his default and temporary tablespaces. Try to choose a good password because that user will have many privileges.

If no error is reported STATSPACK tool is available to use.

To simply create a new snap, connect as PERFSTAT user and then run:
SQL> exec statspack.snap;

Get another snapshot after 15-20 minutes.
To view the available snapshot use the following query:
SQL> select name,snap_id,to_char(snap_time,'DD.MM.YYYY:HH24:MI:SS')
"Date/Time" from stats$snapshot,v$database;

NAME SNAP_ID Date/Time
--------- ---------- -------------------
BILLING 21 17.02.2010:17:44:09
BILLING 22 17.02.2010:17:59:46
BILLING 1 16.02.2010:12:52:04
BILLING 11 16.02.2010:13:04:14

To create a new report run:
SQL> @/u01/app/oracle/product/10.2.0/db_1/rdbms/admin/spreport.sql

It will ask for two SNAP_ID, a report file name... and then your report will be created.

Adding a new database on 11gR2 using dbca

2010-03-09T14:59:00.009+01:00

I needed to add a new database to test some BACKUP/RESTORE commands.

I selected a Custom Database

I used the same ASM disk groups available for an already active database.

I want to give 40% of available memory to SGA and PGA

I increased the number of processes.

Oracle 11g is using a new mechanism for managing shared memory: you need to configure large enough tmpfs on /dev/shm device to fit all requested memory up to MEMORY_MAX_TARGET.

To solve this problem you should correct as follow:

# umount tmpfs
# mount -t tmpfs shmfs -o size=3200m /dev/shm

The same alert on dbca log

To by-pass this error I reduced the amount of memory requested during this setup

The new database is now available to be used.

How to disable automatic startup of RAC database

2010-03-04T16:55:00.010+01:00

When I began to work in my current company there were 3 databases working on a RAC environment formed by two nodes. There were many reboots due to misconfiguration (one for all... they didn't use a gigabit switch for private interconnection, but.. a CROSS cable!!!!): I pretended to move some instances to new machines and now we have three RAC installations.

The following are the steps to disable two of the previous three instances from automatic startup.

I issued the following command to check the status of these databases:

srvctl status database -d SID -v

I shutted down both databases with immediate option:

srvctl stop database -d SID -o immediate

Again I looked at the status:

Then I definetely disabled the automatic startup of those databases with the following command:

srvctl disable database -d SID

That's all.

Oracle 11gR2 RAC installation: Database installation

2010-03-03T17:49:00.010+01:00

Login to first node as the oracle user and start the runInstaller

Configure your Oracle Support Account

Create and configure a database

Server class

It's a Rac installation

Test the SSH conectivity from previos screenshot.

SSH connectivity already estabilished

Advanced installation

Installing the Enterprise Edition

Options availables

Choose the right path

General purpose database installation

Global database name: RAC3

Character sets

Security

Sample schemas, no thanks.

In the course of introducing Automatic Memory Management (AMM) in version 11g, Oracle performed a major change in the internal memory management on OS level. This affects the prerequisites for setting up a database. It is important to know that, prior to starting the Oracle instance, the underlying virtual file system has to be sized accordingly. The determining factor here is the value of the memory_max_target parameter. If not set explicitly it defaults to the memory_target parameter value.

The following error I got adding another database (for test my restore script) using dbca on the same RAC environment.

ORA-00845: MEMORY_TARGET not supported on this system

and on the alert log you could read the following warning message:

WARNING: You are trying to use the MEMORY_TARGET feature. This feature requires the /dev/shm file system to be mounted for at least 1795162112 bytes. /dev/shm is either not mounted or is mounted with available space less than this size. Please fix this so that MEMORY_TARGET can work as expected. Current available is 1795063808 and used is 2394198016 bytes. Ensure that the mount point is /dev/shm for this directory.memory_target needs larger /dev/shm
I will cover this problem in another post.

Setting now a high value of database memory arise the following error

So from runInstaller choose a suitable memory. In my case I choose 44%

No Grid Control is available

As database storage I choosed Automatic Storage Management

I'm not enabling automated backups

Select the existing disk groups for ASM

That's all.

Oracle 11gR2 RAC installation: Grid Infrastructure installation

2010-02-22T12:00:00.016+01:00

To install the Grid Infrastructure, login as oracle user and start the runInstaller

I choosed to install as "Advanced Installation"

The following is a screenshot coming from the help of the runInstaller, describing a virtual scenario formed by two Rac nodes:

What is a SCAN name ?

Oracle introduced this new concept on 11gR2 version: it stands for Single Client Access Name (SCAN). Its purpose is to eliminate the need to change tns name entry when nodes are added to or removed from the Cluster. What does it happen when you have several client and you decide to add/remove a node? So you have to configure your SCAN name on your DNS otherwise...

you will get the following error. [INS-40718] Single Client Access Name (SCAN) name: RAC3-scan could not be resolved.

From the previous screenshot, click add to add configure the second node.

Specify public and private interconnection.

A new feature: it's now possible to select ASM to place OCR and voting disks.

Before proceeding with this option, just have a read to this interesting post:

http://www.dba-oracle.com/t_vulnerability_exposure_RAC_on_ASM.htm

Add a disk group, selecting from candidate disks. It was not possible to create one disk group and associate to only one candidate ... My intention was to create, as usual and as suggested in Oracle best practices, two disk groups: DATA_RAC3 and FRA_RAC3.

Select ASM password

I didn't use IPMI (the Intelligent Platform Management Interface)

At this time I didn't create the "Oracle Base" directory

So I created it manually

On both nodes

Execute the scripts as root user

The output of the scripts...

Grid Infrastructure installation finished.

Oracle 11gR2 RAC installation: install and configure ASM library

2010-02-03T15:26:00.006+01:00

To install ASM drivers download your rpm files from here http://www.oracle.com/technology/tech/linux/asmlib/index.html: for my installation (Oracle Enterprise Linux 5) I downloaded them (oracleasm-2.6.18-164.el5-2.0.5-1.el5.x86_64.rpm, oracleasmlib-2.0.4-1.el5.x86_64.rpm, oracleasm-support-2.1.3-1.el5.x86_64.rpm) from here http://www.oracle.com/technology/software/tech/linux/asmlib/rhel5.html

I executed the following command on both nodes as root user:
rpm -Uvh oracleasm*

Then I need to configure the ASM library, with the following command:
/etc/init.d/oracleasm configure

Type oracle and oinstall as user and group name that own the driver interface; type y to configure starting ASM library on boot and again y to scan ASM disks on boot.

Execute the same previous step on second node.

From one node only, I used the following command to create TWO disk group pointing to my "PowerPath" devices:

/etc/init.d/oracleasm createdisk DG_DATA_DWH /dev/emcpowere1

/etc/init.d/oracleasm createdisk DG_FRA_DWH /dev/emcpowerf1

From the other node, to scan for the new ASM disks I issued:

/etc/init.d/oracleasm scandisks

Verify the cluster is configured correctly for a "Grid" creation, running as oracle user, only from one node, the following command:

./runcluvfy.sh stage -pre crsinst -n nov2210,nov2211 -verbose

Oracle 11gR2 RAC installation: install and configure OCFS2

2010-02-02T12:32:00.007+01:00

This post will describe how to install and configure OCFS2, the Oracle Cluster File System, a shared disk file system developed by Oracle Corporation and released under the GNU General Public License.
First of all download and install on both nodes the requested rpm files from the Oracle website: http://oss.oracle.com/projects/ocfs2/files/

Then run the console using the following command as root just from one node:
ocfs2console &

Then select Cluster->Configure Nodes... from menu

and the following window will appear

Press tha ADD button

And insert all details of your node (specifing the private IP address)

Again ADD button to add details for the second node

Click the APPLY button

The select Cluster->Propagate Configuration... from menu

Type YES and the root password when asked. When FINISHED! is showned, click on CLOSE button

Now from first node type:
/etc/init.d/o2cb configure

Answer Y to load O2CB driver on boot, then use default value for cluster name, then 61 as heartbeat dead threshold and then accept all default values.

The same steps for the second node

Now it's time to format, selecting Tasks->Format from menu

A list of available devices will appear. Use a Volume label and then press OK. I will setup TWO devices to share disks

First devices is formatted

Proceed with the second one

Quit OCFS2 console

Now on both nodes, run:
mkdir -p /u02/ocfs2/
mkdir -p /u02/ocfs2_mirror/
mount -t ocfs2 -o datavolume,nointr /dev/emcpowerb1 /u02/ocfs2/
mount -t ocfs2 -o datavolume,nointr /dev/emcpowerd1 /u02/ocfs2_mirror/
echo "/dev/emcpowerb1 /u02/ocfs2/ ocfs2 _netdev,datavolume,nointr 0 0" >> /etc/fstab
echo "/dev/emcpowerd1 /u02/ocfs2_mirror/ ocfs2 _netdev,datavolume,nointr 0 0" >> /etc/fstab
reboot

Oracle 11gR2 RAC installation: kernel, ssh user equivalency, networking and time sync

2010-01-19T16:06:00.010+01:00

This post will describe how to configure SSH user equivalency, set some kernel parameters, the network, sync the time between the two nodes and load the hangcheck/timer module.
First of all let's set up the ssh user equivalency: use pgrep sshd to see if ssh daemon is running.
You need either an RSA or a DSA key for the SSH protocol. RSA is used with the SSH 1.5 protocol, while DSA is the default for the SSH 2.0 protocol.
To configure SSH you should create RSA keys on each node using the following steps as oracle user:
su - oracle
ssh-keygen -t rsa -b 1024
When it asks for a passphrase, leave it empty. This command writes the RSA public key to the ~/.ssh/id_rsa.pub file and the private key to the ~/.ssh/id_rsa file.
Repeat the above steps on the other node that you intend to make a member of the cluster, using the RSA key.
Then you should add all keys to a common authorized_keys file.
Combine the contents of the id_rsa.pub files from each server. This operation can be done starting simply from one node. Run the commands below from the first node (in my case 172.22.0.10), for example, as the oracle user (and from /home/oracle directory):
cat ~/.ssh/id_rsa.pub >> ~/.ssh/authorized_keys
ssh 172.22.0.11 cat ~/.ssh/id_rsa.pub >> ~/.ssh/authorized_keys
scp ~/.ssh/authorized_keys 172.22.0.11:/home/oracle/.ssh/
Each time you connect from any node to a new hostname for the first time, you will see a message similar to "The authenticity of host '172.22.0.11 (172.22.0.10)' can't be established. Are you sure you want to continue connecting (yes/no)?". Type "yes" and press ENTER.

Then edit /etc/hosts file for both nodes to set up public and private networking.

Use the following command syntax, where hostname1, hostname2, and so on, are the public hostnames(alias and fully qualified domain name) of nodes in the cluster to run SSH from the local node to each node, including from the local node to itself, and from each node to each other node:

ssh hostname1 hostname

At the end of this process, the public hostname for each member node should be registered in the known_hosts file for all other cluster member nodes.

Just because my nodes are connected to EMC SP Agent (Array Agent) I need to install Navisphere Host Agent, downloaded from EMC website.

Host Agent is required for the registration of the host bus adapters with the SPs to which they are connected and for integrating host-side information such as device and volume names. A Host Agent automatically registers hosts and HBAs and also provides drive mapping information for the UI and CLI

To let both nodes synced I used Network Time Server protocol, because you should ensure that the system clocks on all nodes are as close as possible, so edit /etc/ntp.conf file and add your ntp server.

Then configure ntpd to start automatically and update manually both nodes.

On all cluster nodes, verify that the kernel parameters are set to values greater than or equal to the recommended value. The procedure following the table describes how to verify and set the values on /etc/sysctl.conf. Then use /sbin/sysctl -p to load this configuration without restarting the nodes.

To improve the performance of the software on Linux systems, you must increase the following shell limits for the oracle user, editing /etc/security/limits.conf file.

Add or edit the following line in the /etc/pam.d/login file, if it does not already exist:

session required /lib/security/pam_limits.so

Depending on the oracle user's default shell, make the following changes to the default shell startup file. For the Bourne, Bash, or Korn shell, add the following lines to the /etc/profile file (or the file/etc/profile.local on SUSE systems):

For the C shell (csh or tcsh), add the following lines to the /etc/csh.login file (or the file/etc/csh.login.local on SUSE systems):

(*Modified on 27 September 2010: as stated on note ID 726833.1 on Oracle 11gR2 there's no need to configure the handcheck-timer module as I suggested below)
Before installing Oracle Real Application Clusters on Linux systems, verify that the hangcheck-timer module (hangcheck-timer) is loaded and configured correctly.

hangcheck-timer monitors the Linux kernel for extended operating system hangs that could affect the reliability of a RAC node and cause a database corruption.

If a kernel/device driver hang occurs, then the module restarts the node in seconds. There are 3 parameters used to control the behavior of the module:

1. The hangcheck_tick parameter: it defines how often, in seconds, the hangcheck-timer checks the node for hangs. The default value is 60 seconds. Oracle recommends to set it to 1(hangcheck_tick=1).

2.The hangcheck_margin parameter: it defines how long the timer waits, in seconds, for a response from the kernel. The default value is 180 seconds. Oracle recommends to set it to 10(hangcheck_margin=10)

3.The hangcheck_reboot parameter: If the value of hangcheck_reboot is equal to or greater than 1, then the hangcheck-timer module restarts the system. If the hangcheck_reboot parameter is set to zero, then the hangcheck-timer module will not restart the node. It should always be set to 1.

If the kernel fails to respond within the sum of the hangcheck_tick and hangcheck_margin parameter values, then the hangcheck-timer module restarts the system.

uname -a

On Kernel 2.6 enter a command similar to the following to start the module located in the directories of the current kernel version:

insmod /lib/modules/kernel_version/kernel/drivers/char/hangcheck-timer.ko hangcheck_tick=1 hangcheck_margin=10 hangcheck_reboot=1
echo "insmod /lib/modules/kernel_version/kernel/drivers/char/hangcheck-timer.ko hangcheck_tick=1 hangcheck_margin=10 hangcheck_reboot=1" >> /etc/rc.d/rc.local

How to multiplex redo log members and groups on ASM with sqlplus and EM

2010-01-19T09:29:00.013+01:00

This post will show you how to add new redo log members and groups when the storage engine is ASM; they will be added using both sqlplus console and Enterprise Manager Console.
The following picture is a screenshot taken from the Enterprise Manager after I finished to configure a default Oracle RAC installation formed by two nodes. As you can see there are four groups with just one redo log member.

From sqlplus console you can see how this redo log groups and redo log member are accessible by both nodes.
If you want to see which redo log member is accessible by one node, simply run the following query:
SQL> select * from v$logfile order by GROUP#;

Otherwise if you want to see which redo log member is accessible by both nodes, run this other query:
SQL> select * from gv$logfile order by INST_ID, GROUP#;

Now we will add a new redo log group from sqlplus console, using the following command:
SQL> ALTER DATABASE ADD LOGFILE THREAD 1 GROUP 5 SIZE 51200K;

As you can see from the EM console the new redo log group is created and magically two redo log members are added to this redo log group. Now let's create another redo log group, the sixth, but using the EM console. Click on the CREATE button on the right.

Complete the group name, the file size and choose to which thread assign this new redo log group. Then click on SHOW SQL button to see what command will be executed.

As you can see the command is the same we executed previously from sqlplus console. Click RETURN button. On the previuos screen you can simply click OK button to execute the command.

Again the EM console will show our new configuration and the new redo log group will be formed by two redo log members.

Now we want to add another redo log member to redo log groups 1, 2, 3 and 4. For 1, 2 and 3 I will add them by sqlplus console; the fourth will be added by EM console.

From sqlplus console I simply used the following commands as you can see from picture. My storage engine is based on ASM (Automatic Storage Management), so the syntax is very easy.

ALTER DATABASE ADD LOGFILE MEMBER '+DATA_RAC3' TO GROUP 1;
ALTER DATABASE ADD LOGFILE MEMBER '+DATA_RAC3' TO GROUP 2;
ALTER DATABASE ADD LOGFILE MEMBER '+DATA_RAC3' TO GROUP 3;

From EM console, select redo log group number 4 and then click EDIT button.

The following screenshot will be showed. To add a new redo log member, click ADD button.

Use default suggestion and the click on CONTINUE.

The following screenshot will be showed. Now select SHOW SQL button to see the command line.

As you can see it's the same we executed by sqlplus console. Click RETURN.

Click APPLY to add the new redo log member to redo log group number 4.

Finally... every redo log group is formed now by two redo log members.

Oracle 11gR2 RAC installation: EMC PowerPath configuration

2010-01-15T15:21:00.010+01:00

Let's continue to our environment setup.
As root I create DBA Group and Oracle User on both nodes, using the following commands:
groupadd dba
groupadd oinstall
useradd -g oinstall -G dba oracle
passwd oracle

I got the latest rpm available for Linux platform on 64bit from EMC website for PowerPath solution and installed on both nodes as root.

I've setup WWN for this new Rac installation and created a logical storage group from EMC Navisphere software solution.

I've licensed properly this new hosts

Started the PowerPath daemon on both nodes

Let's see what devices our machines can view

Ok. Our nodes can connect and use the logical storage group.

Now it's time to format them (just from one node) as the following screenshot

Next step is to configure SSH on Cluster Member Nodes

Oracle 11gR2 RAC installation using Enterprise Linux 5.4.0.0.0

2010-01-14T15:48:00.020+01:00

This is a first post about how I installed Oracle Real Application Clusters 11gR2 on two nodes using Oracle Enterprise Linux 5.4.0.0.0.

Just because I got every screenshot to document my activity and don't have time to write all the steps I did... when I decide one step is easy to understand... just look at the screenshot...

I'll prefer to attach just a series/set of pictures one after another and describe those steps that required more configuration and attention. Thanks...

First of all go to http://edelivery.oracle.com/ and get the 6 files for Enterprise Linux (I used Enterprise Linux Release 5 Update 4 Media Pack for x86_64 (64 bit) )...

Enterprise-R5-U4-Server-x86_64-disc1.iso
Enterprise-R5-U4-Server-x86_64-disc2.iso
Enterprise-R5-U4-Server-x86_64-disc3.iso
Enterprise-R5-U4-Server-x86_64-disc4.iso
Enterprise-R5-U4-Server-x86_64-disc5.iso
Enterprise-R5-U4-Server-x86_64-disc6.iso

From my ILO (Integrated Lights-Out), the embedded server management technology exclusive to HP, I've mounted as virtual media (CD-ROM) my local hard disk pointing to the first iso previously downloaded. Then I started my remote server and the following are the steps I've done:

Used a linux text installation

Skipped the media test

Language Selection

Keyboard selection

I had a previuos installation here, so I needed to format all DATA.

Reinstall System

Partition Configuration

Final partition configuration

Boot loader configuration

Network configuration

First network

Second network

Network mask for private intercommunication

Network Settings

Hostname

Software to install

The begin of packages installation

Reboot the machine

Disable SeLinux

The following are the network settings steps for the second nodes

That's all.. for now...

Enable ARCHIVELOG and FLASHBACK mode on ASM

2010-01-11T11:54:00.003+01:00

To enable ARCHIVELOG on ASM and configure FLASHBACK (without enabling it) you need to set up the appropriate initialization parameters (db_recovery_file_dest_size, db_recovery_file_dest, db_flashback_retention_target).

SQL>alter system set db_recovery_file_dest_size = '400G' scope=both sid='*';
SQL>alter system set db_recovery_file_dest = '+DATA_RAC3' scope=both sid='*';
SQL> alter system set db_flashback_retention_target = 60 scope=both sid='*';

Then you need to put the database in MOUNT mode; ARCHIVELOG and FLASHBACK mode can be changed only when the database is in the MOUNT state.
srvctl stop database -d RAC3 -o immediate
srvctl start database -d RAC3 -o mount

To enable ARCHIVELOG and FLASHBACK mode you should issue the following command
sql> ALTER DATABASE ARCHIVELOG;
sql> ALTER DATABASE FLASHBACK ON; (I didn't issue for my installation)

Then you can open your database and check its status.
sql> SELECT LOG_MODE,FLASHBACK_ON FROM V$DATABASE;
LOG_MODE FLASHBACK_ON
------------ ------------------
ARCHIVELOG NO (You should see YES)

Add a controlfile into ASM when original controlfile is stored on ASM

2010-01-11T11:01:00.004+01:00

Some days ago I installed Oracle 11gR2 on a RAC formed by two nodes. The default installation on ASM use just a controlfile. So I decided to add another one and the following are the steps I've done.

I'm using of course an spfile for my instance.
From a sqlplus sessions I needed to know where my current controlfile was and then altered the system to add another one. So I typed:
sql> show parameter control_files
sql> alter system set control_files='+DATA_RAC3/rac3/controlfile/current.260.706989187', '+DATA_RAC3' scope=spfile sid='*';

After I needed to put the instance in NOMOUNT mode, so as the oracle user, I typed:
srvctl stop database -d RAC3 -o immediate

From rman I was able to duplicate the controlfile, using the following commands:
$ rman nocatalog
RMAN>connect target
RMAN> restore controlfile from '+DATA_RAC3/rac3/controlfile/current.260.706989187';

After those commands, I put the database in OPEN mode, always from RMAN console:
RMAN> sql 'alter database mount';
RMAN> sql 'alter database open;

From a new sqlplus session I was able to see my new duplicated controlfile:
sql> select name from v$controlfile;

And now the last step. I needed to alter again the system, modifing the control_file parameter with the complete path of the new file:
sql> alter system set control_files='+DATA_RAC3/rac3/controlfile/current.260.706989187', '+DATA_RAC3/rac3/controlfile/current.268.707763013' scope=spfile sid='*';

Using DBMS_REDEFINITION package to reorganize tables online

2009-10-13T16:20:00.013+01:00

I needed to partition a table while it was accepting updates. So I decided to use DBMS_REDEFINITION package provide by Oracle.

The following privileges must be granted to the user:
ALTER ANY TABLE
CREATE ANY TABLE
DROP ANY TABLE
LOCK ANY TABLE
SELECT ANY TABLE

The following privileges may be required too:
CREATE ANY INDEX
CREATE ANY TRIGGER

First of all you have to create an interim table with the same columns and data type of the table to be redefined.

Anyway I used SYS user to perform the following steps:
alter session force parallel dml parallel 3;
alter session force parallel query parallel 3;

Next step is to check if table can be redefined
EXEC Dbms_Redefinition.Can_Redef_Table('SMS', 'SMS_TRANSACTION');

With no errors displayed, I can proceed starting the redefintion using the following command. This operation can take quite some time to complete, but any queries and DML are available on table being redefined during the entire process:
BEGIN
DBMS_REDEFINITION.start_redef_table(
uname => 'SMS',
orig_table => 'SMS_TRANSACTION',
int_table => 'INT_SMS_TRANSACTION');
END;

The following command copies dependent objects, so it automatically create any triggers, indexes, materialized view logs, grants, and constraints on our interim table:
DECLARE
num_errors PLS_INTEGER;
BEGIN
DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS('SMS', 'SMS_TRANSACTION','INT_SMS_TRANSACTION',
DBMS_REDEFINITION.CONS_ORIG_PARAMS, TRUE, TRUE, TRUE, TRUE, num_errors);
dbms_output.put_line(num_errors);
END;

At this point you can query DBA_REDEFINITION_ERRORS view to check for blocking errors. You have to correct these errors and then again reexecute
the COPY_TABLE_DEPENDENTS procedure above:
select object_name, base_table_name, ddl_txt from DBA_REDEFINITION_ERRORS;

The next optional step points to resynchronize the interim table before building any constraints and indexes, especially if you think there was delay between the completion of the previous operation and moving on to finish the redefinition.
BEGIN
dbms_redefinition.sync_interim_table(
uname => 'SMS',
orig_table => 'SMS_TRANSACTION',
int_table => 'INT_SMS_TRANSACTION');
END;

Next you can execute the following command to complete the redefinition:
BEGIN
DBMS_REDEFINITION.FINISH_REDEF_TABLE('SMS', 'SMS_TRANSACTION', 'INT_SMS_TRANSACTION');
END;

Finally you should gather new statistic for your "new" table:
begin
DBMS_STATS.GATHER_TABLE_STATS (
ownname => 'SMS',
tabname => 'SMS_TRANSACTION',
estimate_percent => 45
);
end;

Add a new TAF service with dbca

2008-07-04T14:16:00.013+01:00

I needed to set up a new service for my three nodes Oracle RAC production environment.
I decided to use dbca because it's easier compared with srvctl command line.
Indeed when you use DBCA to create a service you do not need to add the corresponding TNS entry in your tnsnames.ora on the cluster nodes: DBCA does it all for you in the background.
Of course you can use SRVCTL to create a service, but you also need to manually add the TNS entry: moreover when you use SRVCTL, after you've created the new service you need to start it with a separate command, while DBCA starts it when you click FINISH button.
So let's start logging as oracle user from one of your nodes, and type
dbca

It first shows the Welcome window where you need to select your type of database: Oracle Real Application Cluster database or Oracle single instance database.

Next you need to choose the operation to be performed. Choose Services Management and click NEXT button.

At the step 2 select your database. In my case the screen shows my only BILLING database

The step 3 shows the current services available for the database chosen. In this screen you can add, remove or modify services. Today we want to add a new service so click the ADD button.

After clicking the ADD button you can choose a name for next service: in my case I called it SIM_TAF. Press OK button after you enter your service name.

Now configure your option for the new service. I choose it should have only a preferred instance where to run (BILLING3) and two available instances to switch over in case of problems (BILLING1 and BILLING2). Choose also your TAF (Transparent Application Failover) policy:
transparent application failover is a client-side feature that allows for clients to reconnect to surviving databases in the event of a failure of a database instance. Notifications are used by the server to trigger TAF callbacks on the client-side. Your options could be: Basic, in which the application connects to a backup node only after the primary connection fails. This approach has low overhead, but the end user experiences a delay while the new connection is created, or Pre-Connect, where the application simultaneously connects to both a primary and a backup node. This offers faster failover, because a pre-spawned connection is ready to use. But the extra connection adds everyday overhead by duplicating connections. For my environment Basic TAF policy was good. Click FINISH.

A summary screen will appear. Click OK

The configuration of the new service will start

Click NO if you don't want to perform other operation.

That's all.

RMAN: UPGRADE CATALOG command

2008-04-18T09:37:00.007+01:00

While registering a new two nodes Oracle RAC installation to my recovery catalog I encountered the following error:

[oracle@blade05 ~]$ rman target sys/my_pwd catalog rman/cat@CATDB

Recovery Manager: Release 10.2.0.4.0 - Production on Fri Apr 18 10:32:46 2008

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

connected to target database: DWHDB (DBID=3585749232)
connected to recovery catalog database
PL/SQL package RMAN.DBMS_RCVCAT version 10.02.00.00 in RCVCAT database is not current
PL/SQL package RMAN.DBMS_RCVMAN version 10.02.00.00 in RCVCAT database is not current

It means I'm using a different version of rman between the RAC installation and the recovery catalog, indeed the RAC installation is at 10.2.0.4 while recovery catalog is at 10.2.01 version.
If you use a version of the recovery catalog that is older than that required by the RMAN client, then you must upgrade it using UPGRADE CATALOG command;
I'm upgrading from a client using 10.2.0.4 software version, while at the same time my recovery catalog is serving also other database using older or the same version. That's not a problem: it's important that recovery catalog is at least at the same version of your newest database client version.

So while I'm connected from RAC installation I need to issue the UPGRADE CATALOG command:
RMAN> upgrade catalog;

recovery catalog owner is RMAN
enter UPGRADE CATALOG command again to confirm catalog upgrade

RMAN> upgrade catalog;

recovery catalog upgraded to version 10.02.00.04
DBMS_RCVMAN package upgraded to version 10.02.00.00
DBMS_RCVCAT package upgraded to version 10.02.00.00

RMAN>

That's all.

Patching Oracle RAC 10.2.0.1 on ASM with 10.2.0.4 Patch Set 3 for Linux x86

2008-04-11T13:57:00.026+01:00

Using your oracle user, download and unzip the file p6810189_10204_Linux-x86.zip.

First of all, before applying the 10.2.0.4 patchset to the Oracle database you should verify if the DSTv4 (USA 2007) update included in the 10.2.0.4 patchset has implications on your database. Following the NoteId 553812.1 provided by Metalink the goal is to see if you have stored information that is affected and if you are already using a DST version higher then DSTv4 and what then to do.

Because I'm patching from a lower 10.2.0.X release to 10.2.0.4 I hav to execute the following query to check the current version of the Oracle time zone definitions:
SQL> SELECT version FROM v$timezone_file;

Becuse my current timezone version is lower then 4, I need to execute this other query to check if I'm storing *user* TZ (TSTZ and TSLTZ) data:
SQL> select c.owner || '.' || c.table_name || '(' || c.column_name || ') -' || c.data_type || ' ' col
from dba_tab_cols c, dba_objects o
where c.data_type like '%TIME ZONE'
and c.owner=o.owner
and c.table_name = o.object_name
and o.object_type = 'TABLE'
order by col
My output shows there is nothing outside the Data Dictionary (= other then SYS objects).

The following queries
SQL> SELECT object_name FROM dba_objects WHERE object_id IN (SELECT obj# FROM scheduler$_window);
SQL> SELECT object_name FROM dba_objects WHERE object_id IN (SELECT obj# FROM scheduler$_job);
show I have only 6 jobs defined and I have also no user TZ (TSTZ and TSLTZ) data, so there is no action to take for the Oracle time zone definitions, and I can simply upgrade from 10.2.0.1 to 10.2.0.4.

Now I have to stop one node because I need to patch first the clusterware software and it can be done using rolling updates.
Run the following commands from the first node of your RAC installation:
emctl stop dbconsole
isqlplusctl stop
srvctl stop service -d DWHDB -s DWHDB_TAF -i DWHDB1
srvctl stop instance -d DWHDB -i DWHDB1 -o immediate
srvctl stop asm -n bl3306
srvctl stop nodeapps -n bl3306

Go into the directory Disk1 and run ./runInstaller &

A Welcome screen appears.

Specify the home details for the clusterware home.

Click next on the cluster nodes screen.

It checks for product-specific prerequisites.

Click Install

And the Clusterware patching is starting.

Before click the EXIT button and complete the installation,login as root on the first node and execute the following command as suggested:

Run
/u01/app/oracle/product/10.2.0/crs_1/crsctl stop crs
/u01/app/oracle/product/10.2.0/crs_1/install/root102.sh

They stop and start the crs services on the first node.
Execute the same command on every nodes, I ru them only on my second node:
/u01/app/oracle/product/10.2.0/crs_1/crsctl stop crs
/u01/app/oracle/product/10.2.0/crs_1/install/root102.sh

Then execute as oracle user from one node:
crsctl query crs activeversion

Execute as oracle user from second node:
crsctl query crs activeversion

The clusterware software has been updated.

Click EXIT and then YES from the OUI interface.

Now execute the following command to completely stop the database (this patchset doesn't support rolling updates) from your first node:
emctl stop dbconsole
isqlplusctl stop
srvctl stop service -d database DWHDB -s DWHDB_TAF -i DWHDB1
srvctl stop database -d DWHDB -o immediate
srvctl stop asm -n bl3306
srvctl stop nodeapps -n bl3306

Execute the following command to completely stop the asm instance from your second node:
emctl stop dbconsole
isqlplusctl stop
srvctl stop asm -n bl3305
srvctl stop nodeapps -n bl3305

As oracle user run again the runInstaller

Click NEXT on Welcome screen.

Choose the Oracle home.

Click NEXT on cluster installation mode screen.

Click NEXT on prerequisite checks screen.

Enable and register your Oracle Configuration Manager.

Accept the license agreement

and test your registration

Click INSTALL on summary screen.

The installation is proceeding.

When asked execute as root user the following command on your first node
/u01/app/oracle/product/10.2.0/db_1/root.sh

and then again from your second node.

Clic EXIT on End of Installation screen

and the YES

Only from your first node, login as oracle user and execute:
srvctl start asm -n bl3306
srvctl start listener -n bl3306

Then run dbua

Click NEXT on Welcome screen.

Select Upgrade a Database and click NEXT.

Select your database and click NEXT, providing a user with sysdba privileges.

The upgrade of the database starts.

Choose the degree of parallelism to recompile any invalid objects, turn off archiving and click NEXT.

Specify your Flash Recovery Area and click NEXT.

Click FINISH on Summary screen.

Your database is now 10.2.0.4 patched.

Registering a target database to a recovery catalog

2008-03-31T14:19:00.006+01:00

It's quite simlpe to register your database to a catalog database.

First of all startup your database.

Then connect to your catalog database.

DOS> rman target sys/oracle catalog rman/cat@CATDB

where sys/oracle are username and password for the database you're registering,
rman/cat are username and password for the recovery catalog user and CATDB is the service name of recovery catalog database.

From the RMAN command line simply type

RMAN> register database;

Your database is now registered to the recovery catalog database.

RMAN and EMC Legato Networker 7.4

2008-02-21T16:43:00.032+01:00

In this post I would show you how to integrate RMAN with a media manager, in my case EMC Legato Networker 7.4

Launch EMC Legato Networker 7.4 from your browser or double click on the link to the java web interface.

Enter your administrative credentials into the Networker Management Console Login window.

The Networker Management Console will show the host used as Networker Server. Double clicking on the NAME Networker...

... it will show you the Networker Administration window

Expand the Groups and select the group created for the Data Warehouse, in my case it's called Gr_ORACLE_DWH

If you double click on the client associated with Gr_ORACLE_DWH on the right panel, the properties panel will be showed. On the General panel, Identity-->Name is the name of my vip server, IndexManagement shows the Browse and Retention policy for this backup, Backup-->Save Set shows you the path of the RMAN command that Networker will execute on the client, Backup-->Group identifies Gr_ORACLE_DWH as group, Backup-->Schedule show that this backup is scheduled as ORACLE_DWH_FULL.

On the Apps & Modules panel, Backup-->Backup command will show the command executed by Networker Server on the client, including some environment variables or pre and post backup command. This command nsrnmo_dwhdb2 is placed on the client under /usr/sbin/

If you instead right click on Gr_ORACLE_DWH group and select Properties the following screen is showed. In my case this backup starts at 19:00 but at present is disabled.

Selecting Schedules from the Networker Administration window on the left panel you can double click on ORACLE_DWH_FULL to view when and what type of backup is scheduled.

Select Media button on the top of Networker Administration window and then Media Pools.
The Media Pools for this backup is called ORAdatawarehouse. Backup data is sorted into backup media volumes by using media pools. A media pool is a specific collection of volumes to which the NetWorker server writes data. The server uses media pools to sort and store data. Media pools act as filters that tell the server which backup volumes should receive specific data.
When a scheduled backup occurs, the NetWorker server tries to match the save
stream to a media pool configuration. If the save stream matches the criteria of a
media pool configuration, it is directed to a labeled volume in the media pool. The
server then checks if a correctly labeled volume for that media pool is mounted on a
storage device:
◆ If a correctly labeled volume is mounted on a storage device, the NetWorker
server writes data to the volume.
◆ If there is no correctly labeled volume mounted on a storage device, the
NetWorker server requests that such a volume be mounted and waits until an
operator or an autochanger mounts an appropriate volume.

If you double click on ORAdatawarehouse a Basic Properties window is showed with some informations.

From the panel Selection Criteria, Target Device-->Devices shows that this kind of Media Pools use a TAPE Library (in my case they are /dev/nst0 and /dev/nst1)

General informations about the tapes available on the TAPE Library (ADIC Scalar i500) are showed selecting Devices button on the top of Networker Administration window and then Libraries-->ADIC.

Let's see the file what there is inside the file /usr/sbin/nsrnmo_dwhdb2 on the client.

You can find the required variables ORACLE_HOME and PATH, the optional variables ORACLE_SID...

...NLS_LANG, NSR_RMAN_ARGUMENTS, NSR_RMAN_OUTPUT, NSR_SB_DEBUG_FILE...

...PRECMD, POSTCMD and TNS_ADMIN.
Set PRECMD to the complete pathname of a file containing a preprocessing script to be executed before the RMAN backup script. If the pre-command script fails (returns a nonzero value), the scheduled Oracle backup does not proceed (that is, the RMAN script is not executed).
Set POSTCMD to the complete pathname of a file that contains a postprocessing script to be executed after the RMAN backup script. If the RMAN backup script fails, the failure is reported, and the postprocessing script is executed nevertheless. If the postprocessing script fails, an error message is reported.
These script files must have permissions that allow execution by the root user, as the scheduled Oracle backup is always launched by root. The scripts should return a zero value when they succeed and a nonzero value when they fail. The return of a nonzero value will cause the scheduled backup to fail.

My PRECMD file is located on /u01/app/oracle/srvctl_scripts/dwhdb/stop_database_DWHDB.sh and you can view what commands are executed. I need to shutdown my RAC database and then start it up in mount mode to execute a cold backup.

My POSTCMD file is located on /u01/app/oracle/srvctl_scripts/dwhdb/start_database_DWHDB.sh and you can view what commands are executed. I need to shutdown my RAC database (RMAN open it after is execution) and then start it up in open mode.

The RMAN commands for my Data warehouse cold backup is showed here.

Selecting Monitoring button on the top of Networker Administration window and then right click on Gr_ORACLE_DHW, you can start manually the group.

A popup window will ask you to proceed.

Double clicking on the group just started, you can view the Networker Server is trying to contact the client...

... and how the backup proceed.

You can select the tab Monitoring-->Devices to view how much Bytes are written on tape.

The backup is successfully completed.

STEP TO PARTITION A NON PARTITIONED TABLE

2008-02-13T16:08:00.008+01:00

I have a table that is not partitioned and I need to make into a
partitioned table.
To solve this issue it's possible to partition a non-partitioned table using three different ways:
A) export/import method

B) Insert with a subquery method

C) Partition exchange method

I've used the second method that is:
1) Create the partitioned table:

create table hr.fact_navigation_new
( date_events date not null
)
partition by range (date_events)
(
partition part_2008_01_01 values less than (to_date('2008-01-02', 'yyyy-mm-dd)) tablespace part_2008_01_usr_dwh ,
partition part_future values less than (MAXVALUE) tablespace part_future
) enable row movement;

With user SYS I created first two tablespaces:
create tablespace part_2008_01_usr_dwh
datafile '+dwh_db/dwhdb/datafile/dtf_part_2008_01_usr_dwh_01.ora' size 1024m reuse autoextend on next 51200k maxsize 32767m, '+dwh_db/dwhdb/datafile/dtf_part_2008_01_usr_dwh_02.ora' size 1024m reuse autoextend on next 51200k maxsize 32767m,
'+dwh_db/dwhdb/datafile/dtf_part_2008_01_usr_dwh_03.ora' size 512m reuse autoextend on next 51200k
maxsize 32767m extent management local segment space management auto;

create tablespace part_future
datafile '+dwh_db/dwhdb/datafile/dtf_part_future_usr_dwh_01.ora' size 512m reuse
autoextend on next 51200k maxsize 32767m, '+dwh_db/dwhdb/datafile/dtf_part_future_usr_dwh_02.ora' size 512m reuse autoextend on next 51200k
maxsize 32767m extent management local segment space management

2) Insert into the partitioned table with a subquery from the
non-partitioned table:

insert into hr.fact_navigation_new
select * from hr.fact_navigation;

3) To have the same name as the original table,
then rename the original table and the new table:

alter table hr.fact_navigation rename to hr.fact_navigation_ori;
alter table hr.fact_navigation_new rename to hr.fact_navigation

Now I have a new partitioned by range table with two partitions. What about if I need to add new daily partitions between part_2008_01_01 and part_future ?

I've solved with a PL/SQL procedure and a job executed by the DBMS_SCHEDULER utility.
As SYS user execute the following procedure:

create or replace PROCEDURE CHECK_PARTITIONS AS
l_curr_max_part varchar2(16); /* current max partition */
l_today_part varchar2(16); /* today+interval partition */
l_last_day_part varchar2(16); /* last partition from today + two months */
l_diff_part number;
l_exist_tab number;
l_tab_name varchar2(30); /* tablespace name */
l_date date;
l_stmt varchar2(2048);
l_part_name varchar2(16); /* partition name to be created */
l_range_month number; /* interval between created partitions and to_be_created partitions : 2 months is my default */
BEGIN

l_diff_part := 0;
l_exist_tab := 0;
l_range_month := 2;

/**
* Current max partition
*/
select substr(max(partition_name),6)
into l_curr_max_part
from dba_tab_partitions
where table_name = 'FACT_NAVIGATION'
and TABLE_OWNER = 'HR'
and partition_name <> 'PART_FUTURE';

/**
* Today+interval partition
*/
select to_char(add_months(sysdate, l_range_month), 'YYYY_MM_DD')
into l_today_part
from dual;

if (l_curr_max_part < l_today_part)
then
/**
* If max partition is the least compared with Today+interval partition,
* I need to detect the latest day of the partition to create
*/
select to_char(last_day(add_months(sysdate, l_range_month)), 'YYYY_MM_DD') LD
into l_last_day_part
from dual;

/**
* I need to detect the number of partitions to create starting
* from the current available max partition
* untill the latest
*/
select to_date(l_last_day_part, 'YYYY_MM_DD')-to_date(l_curr_max_part, 'YYYY_MM_DD') RET
into l_diff_part
from dual ;

for ii IN 1..l_diff_part
loop
/**
* Tablespace name to create
*/
select 'PART_' || to_char(to_date(l_curr_max_part, 'YYYY_MM_DD')+ii, 'YYYY_MM') || '_USR_DWH'
into l_tab_name
from dual;
/**
* I need to verify if tablespace name alreadyu exists. Otherwise I create it.
*/
select count(*)
into l_exist_tab
from dba_tablespaces
where tablespace_name = l_tab_name;

if (l_exist_tab = 0)
then
/**
* I need to create the tablespace
*/
execute immediate('CREATE TABLESPACE ' || l_tab_name || ' DATAFILE ''+DWH_DB/dwhdb/datafile/dtf_' || l_tab_name || '_01.ora''' || ' SIZE 1024M REUSE AUTOEXTEND ON NEXT 51200K MAXSIZE 32767M, ' || ' ''+DWH_DB/dwhdb/datafile/dtf_' || l_tab_name || '_02.ora''' || ' SIZE 1024M REUSE AUTOEXTEND ON NEXT 51200K MAXSIZE 32767M, ' || ' ''+DWH_DB/dwhdb/datafile/dtf_' || l_tab_name || '_03.ora''' || ' SIZE 512M REUSE AUTOEXTEND ON NEXT 51200K MAXSIZE 32767M ' || ' EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO');

execute immediate ('ALTER USER USR_SUPER_DWH QUOTA UNLIMITED ON ' || l_tab_name);
end if;

select to_date(l_curr_max_part, 'YYYY_MM_DD')+ii+1
into l_date
from dual;

execute immediate ('ALTER TABLE USR_SUPER_DWH.fact_navigation SPLIT PARTITION PART_FUTURE AT(to_date(''' || to_char(l_date, 'YYYY-MM-DD') || ''', ''YYYY-MM-DD'')) ' || ' INTO (PARTITION PART_' || l_part_name || ' tablespace '|| l_tab_name ||', PARTITION PART_FUTURE)');
end loop;
end if;
END CHECK_PARTITIONS;

The following is the script to create the job using the DBMS_SCHEDULER:
BEGIN
DBMS_SCHEDULER.CREATE_JOB (
job_name => 'SPLIT_PARTITION',
job_type => 'STORED_PROCEDURE',
job_action => 'CHECK_PARTITIONS',
start_date =>to_date('13-02-2008 15:05:00', 'DD-MM-YYYY hh24:mi:ss'),
repeat_interval =>'FREQ=WEEKLY;BYDAY=WED',
enabled => TRUE,
auto_drop => FALSE
);
END;

My job starts every wednesday at 15:05 every week.
If you want to see the status of this job, simply execute:
SELECT * FROM DBA_SCHEDULER_JOBS
WHERE JOB_NAME = 'SPLIT_PARTITION';

If you want to start it immediately, run the following statement:
BEGIN
DBMS_SCHEDULER.RUN_JOB (
job_name => 'SPLIT_PARTITION',
use_current_session => FALSE);
END;

In the following pictures you can see how I manually run the job and see the status RUNNING
in DBA_SCHEDULER_JOBS table, after scheduled the job at 15:05.
When the job finished, new partitions are created (from PART_2008_03_31 to PART_2008_04_30) and
the status of the job become again SCHEDULED.

Oracle Data Guard: 03 physical standby database configuration

2007-06-19T10:51:00.001+01:00

Click here to read the previous post

Now from the primary database start the listener, typing (see the picture below):
lsnrctl start

Then mount the database (see the picture below).

Now you can verify that the listener is listening for the PRIMARY service (see the picture below).

Also from the standby database start the listener, typing (see the picture below):
lsnrctl start

Then mount the database (see the picture below).

Now you can verify that the listener is listening for the STANDBY service (see the picture below).

We have just few work to complete the configuration... From the standby database, in the mount state, type (see the picture below):
selecr sequence#, first_time, next_time from v$archived_log order by sequence#;

Then
alter database recover managed standby database disconnect;

From the primary database type (see the picture below):
alter database open;

and then:
alter system set log_archive_dest_state_2=enable scope=both;

and
select status, error from v$archived_dest where dest_id=2;

Now force a log switch (see the picture below)
alter system switch logfile;

And view that something happened into the standby database :))) (see the picture below)
select sequence#, first_time, next_time from v$archived_log order by sequence#;

And again ...
(see the picture below)

the same results :)))
(see the picture below)

Oracle TimesTen: 01 Installation on Windows systems

2007-04-27T12:23:00.000+01:00

Oracle TimesTen provides a real-time infrastructure software designed for low latency, high-volume data, event and transaction management. It is used intensively in the telecom and financial markets because of their very high performance and robust reliability.

Oracle TimesTen uses in-memory database technology to speed access to data. Indexing, query optimization, and storage management, for example, are designed specially for in-memory data access. The result is high throughput and low, predictable response times, even on commodity hardware. The product also incorporates replication facilities, non-blocking operation, and an event-notification mechanism that works together with materialized views to convey changes to other systems. Oracle TimesTen enables the development of mission-critical, real-time applications in which response time is very important.

Oracle TimesTen also offers a Cache product that interoperates with an Oracle database backend, using one of several protocols, to cache frequently used data in TimesTen. SQL predicates can be used to specify what data is cached, or data can be loaded into the cache on demand (and later automatically aged out). Data can be changed in the cache, with writes being propagated synchronously or asynchronously back to Oracle.

On UNIX, you can install more than one instance of any TimesTen release. For the current release by default the instance name is tt70. The TimesTen installation script can detect if an instance of the particular release of TimesTen already exists on the machine and will prompt you for a new instance name and port number for the main TimesTen daemon.
Remember: on Windows you can only install one instance of TimesTen. The instance name is case-insensitive, can have up to 255 characters, can include underscores or period, but no other special characters.

Before installing TimesTen, make sure the appropriate requirements are met for your operating system. On platforms where JDBC is supported you must have the appropriate version of the JDK installed on your machine to use JDBC.
In my case I will install TimesTen for my environment, that is: Windows XP for Intel IA-32 CPU. It supports both JDK 1.4 and JDK 5.0.

Download from the Oracle website the file timesten70100.win32.zip, save it on a folder and extract it: double click on the setup.exe file and a Welcome wizard will start (see the figure below).

Click the NEXT button and choose the destination location where setup will install files.
The defaul location suggested is C:\TimesTen\tt70_32\ (see the figure below); click NEXT.

Select the setup type that best suits your needs.
- Compact installs the TimesTen client, ODBC drivers and examples;
- Typical installs the TimesTen Data Manager, TimesTen Client, TimesTen
Server, documentation and examples;
- With Custom you may customize installation by selecting any of the following
components: TimesTen Data Manager, TimesTen Client and/or
TimesTen Server including the DEBUG drivers.
I choose Typical (see the figure below) and then click NEXT.

When prompted to install Cache Connect to Oracle (see the figure below) you must have at least a client installation of Oracle Database 9i or 10g on the machine where you are installing TimesTen, to successful install the Cache Connect.
Oracle TimesTen can run as a standalone database, but it can also optionally run as a performance accelerator using the Cache Connect for data moving to or from an existing Oracle Database. Click YES.

Choose the datastore location: the default is
C:\Documents and Settings\USERNAME\Application Data\TimesTen\DemoDataStores.
Select your location (see the figure below) and click NEXT.

Select a program folder (see the figure below) and click NEXT.

Select the TimesTen Environment Options (see the figure below) and click NEXT.
The Datastore Access Control provides an environment of basic control for applications that use the internally defined privileges.
In TimesTen, user privileges are granted on a instance wide-basis. A user’s privileges apply to all data stores in a given TimesTen instance or installation.
Anyway in this installation I don't enable the Datastore Access Control.

Select which version of Java to add to the CLASSPATH environment (see the figure below) and click NEXT.
I have the JDK 5.0 installed on my machine.

Confirm your installation informations (see the figure below) clicking NEXT.

When the Install Wizard is completed, click the FINISH button (see the figure below).

In the next step I will setup a Data Sources (ODBC) to connect and use the Oracle TimesTen.

Recovery Manager: 07 Creating compressed backups

2007-04-26T15:30:00.000+01:00

Click here to read the previous step

Untill Oracle 9i version you could reduce the size of backups backing up only used blocks and skipping unused blocks: in this way you would reduce only the backup sizes of datafiles that were oversized or had significant free space.

Oracle 10g RMAN has introduced the capability to compress backups: indeed it is possible now to compress backups regardless of the contents of the datafiles, using the new BACKUP AS COMPRESSED command.
Compressed backups work only with backup sets (database, tablespace, and datafile backup sets), so you cannot compress image copies.

The following command will backup an entire database using a compressed backup
set:

RMAN> backup as compressed backupset database;

To configure the default backup type for a compressed backup set use the
following command:

RMAN> configure device type disk backup type to compressed backupset;

Compressed database backup sets are compressed at approximately a 5-to-1
ratio, or 20 percent of the size of a standard backup set.

Recovery Manager: 06 Creating image copies

2007-04-26T15:14:00.000+01:00

Click here to read the previous step.

An image copy in RMAN is equivalent to an operating system copy command such as cp or dd in Unix, or COPY in Windows. Image copies can be stored only on disk, so you cannot use tape channels for image copies, but if multiple files are to be copied, then you can consider parallelism.
This means that many of the great features of backup sets (such as incremental backup, compression, writing directly to tape, or controlling the size of the output pieces) cannot be used.
But it means that a restore can be very fast, because there is no need to extract the file from a backup set.

To configure the default backup type for an image copy use the
following command. This parameter or setting configures the type of backup to be an image
copy:

RMAN>configure device type disk backup type to copy;

The following command shows an example of using the RMAN COPY command to create an image copy of two database files (the SYSTEM tablespace and a control file) to the /u01/app/oracle/flash_recovery_area/ directory:

RMAN> run
2> {
3> allocate channel ch1 type disk;
4> copy datafile 1 to '/u01/app/oracle/flash_recovery_area/SYSTEM01.DBF' ,
5> current controlfile to '/u01/app/oracle/flash_recovery_area/CONTROL01.ctl';
6> }

In the above image copy example, the location of the SYSTEM01.DBF file must be known before you do the copy or you have to know that the datafile number 1 is your SYSTEM datafile. Anyway you can understand this requires a lot of extra work.
Likely in Oracle 10g, there is the new BACKUP AS COPY command: now you can perform image copies of an entire database, multiple tablespaces, datafiles, and archive logs without having to specify all of the individual files.
The following command shows an example of using the new RMAN BACKUP AS COPY command to create an image copy of an entire database:

RMAN> backup as copy database;

If you have not changed the default configuration the above command launchs one disk channel
and copies all the datafiles and the controlfile to the flash recovery area.
The following command instead will backup all the archive log files to the flash recovery area, deleting all the input file after the command is successful completed.

RMAN> backup as copy archivelog all delete all input;

Recovery Manager: 05 Creating backup sets

2007-04-19T07:39:00.000+01:00

Click here to read the previous step

As you know Recovery Manager can back up the Oracle objects in two ways:
as image copies or as backup sets.

Image copies are identical copies of the original file (database files, archive logs, or control files), are not stored in a special RMAN format and can be stored only on disk.
A backup set instead is a proprietary format: so only RMAN can manipulated the backup set.
A backup set can be written to disk or to tape, unlike image copies. Moreover it can be compressed and requires less space than a image copy; RMAN can also multiplex the files in a backup set, namely RMAN reads from multiple files and then each of the file blocks are written and interspersed to the same backup set.
Image copies cannot be multiplexed instead.

Backup sets have also the ability to make incremental backups. Furthermore, a backup set is always smaller than an image copy, because backup sets of datafiles never include blocks that have not been used, whereas an image copy of necessity includes the complete file.

To configure the default device to tape use the following command. This command sets the default media for RMAN to store the backup information:

RMAN>configure default device type to sbt;

To configure the default device to disk use the following command.

RMAN>configure default device type to disk;

To configure the default backup type for a backup set use the following command. This parameter or setting configures the type of backup to be a backup set:

RMAN>configure device type disk backup type to backupset;

To configure a default device for either tape or disk to a compressed backup set, use the
following commands:

RMAN>configure device type disk backup type to compressed backupset;
RMAN>configure device type sbt backup type to compressed backupset;

In the following example, you are backing up the database and storing in two backup sets:
FULL DATAFILE BACKUPSET and ARCHIVE LOG BACKUP SET.
FULL DATAFILE BACKUPSET is made up of four backup pieces;
ARCHIVE LOG BACKUPSET is comprised on two backup pieces (you can identify these pieces in the following log, highlighted in bold).

A format of db_%u_%d_%s means that a db_ will be concatenated to the backup set
unique identifier and then concatenated to database name with the backup set number.
The following list includes the format specifications for the FORMAT option used in the example:
%u Specifies an eight-character name constituted by compressed representations of the backup set or image copy number.
%d Specifies the name of the database.
%s Specifies the backup set number.
%t Specifies the backup set timestamp
%p Specifies the piece number within the backup set.

RMAN> run {
2> allocate channel c1 type disk;
3> allocate channel c2 type disk;
4> backup database format 'db_%u_%d_%s';
5> backup format 'log_t%t_s%s_p%p' (archivelog all);
6> }

released channel: ORA_DISK_1
allocated channel: c1
channel c1: sid=140 devtype=DISK

allocated channel: c2
channel c2: sid=145 devtype=DISK

Starting backup at 16-APR-07
channel c1: starting full datafile backupset
channel c1: specifying datafile(s) in backupset
input datafile fno=00001
name=/u01/app/oracle/oradata/PRIMARY/datafile/o1_mf_system_2yss9qlx_.dbf
input datafile fno=00004
name=/u01/app/oracle/oradata/PRIMARY/datafile/o1_mf_users_2yss9r32_.dbf
channel c1: starting piece 1 at 16-APR-07
channel c2: starting full datafile backupset
channel c2: specifying datafile(s) in backupset
input datafile fno=00003
name=/u01/app/oracle/oradata/PRIMARY/datafile/o1_mf_sysaux_2yss9qop_.dbf
input datafile fno=00002
name=/u01/app/oracle/oradata/PRIMARY/datafile/o1_mf_undotbs1_2yss9r16_.dbf
channel c2: starting piece 1 at 16-APR-07
channel c2: finished piece 1 at 16-APR-07
piece handle=/u01/app/oracle/product/10.2.0/db_1/dbs/db_0aifbgff_PRIMARY_10
tag=TAG20070416T220221 comment=NONE
channel c2: backup set complete, elapsed time: 00:02:09
channel c2: starting full datafile backupset
channel c2: specifying datafile(s) in backupset
including current control file in backupset
channel c2: starting piece 1 at 16-APR-07
channel c2: finished piece 1 at 16-APR-07
piece handle=/u01/app/oracle/product/10.2.0/db_1/dbs/db_0bifbgji_PRIMARY_11
tag=TAG20070416T220221 comment=NONE
channel c2: backup set complete, elapsed time: 00:00:12
channel c2: starting full datafile backupset
channel c2: specifying datafile(s) in backupset
including current SPFILE in backupset
channel c2: starting piece 1 at 16-APR-07
channel c2: finished piece 1 at 16-APR-07
piece handle=/u01/app/oracle/product/10.2.0/db_1/dbs/db_0cifbgjv_PRIMARY_12
tag=TAG20070416T220221 comment=NONE
channel c2: backup set complete, elapsed time: 00:00:04
channel c1: finished piece 1 at 16-APR-07
piece handle=/u01/app/oracle/product/10.2.0/db_1/dbs/db_09ifbgfe_PRIMARY_9
tag=TAG20070416T220221 comment=NONE
channel c1: backup set complete, elapsed time: 00:02:45
Finished backup at 16-APR-07

Starting backup at 16-APR-07
current log archived
channel c1: starting archive log backupset
channel c1: specifying archive log(s) in backup set
input archive log thread=1 sequence=4 recid=3 stamp=618986417
channel c1: starting piece 1 at 16-APR-07
channel c2: starting archive log backupset
channel c2: specifying archive log(s) in backup set
input archive log thread=1 sequence=5 recid=4 stamp=620085919
channel c2: starting piece 1 at 16-APR-07
channel c2: finished piece 1 at 16-APR-07
piece handle=/u01/app/oracle/product/10.2.0/db_1/dbs/log_t620085921_s14_p1
tag=TAG20070416T220519 comment=NONE
channel c2: backup set complete, elapsed time: 00:00:04
channel c1: finished piece 1 at 16-APR-07
piece handle=/u01/app/oracle/product/10.2.0/db_1/dbs/log_t620085921_s13_p1
tag=TAG20070416T220519 comment=NONE
channel c1: backup set complete, elapsed time: 00:00:19
Finished backup at 16-APR-07
released channel: c1
released channel: c2

Let's see the backup of our database.

RMAN> list backup of database;

List of Backup Sets
===================

BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
9 Full 176.40M DISK 00:02:05 16-APR-07
BP Key: 9 Status: AVAILABLE Compressed: NO Tag: TAG20070416T220221
Piece Name: /u01/app/oracle/product/10.2.0/db_1/dbs/db_0aifbgff_PRIMARY_10
List of Datafiles in backup set 9
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
2 Full 555181 16-APR-07 /u01/app/oracle/oradata/PRIMARY/datafile/o1_mf_undotbs1_2yss9r16_.dbf
3 Full 555181 16-APR-07 /u01/app/oracle/oradata/PRIMARY/datafile/o1_mf_sysaux_2yss9qop_.dbf

BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
12 Full 352.55M DISK 00:02:41 16-APR-07
BP Key: 12 Status: AVAILABLE Compressed: NO Tag: TAG20070416T220221
Piece Name: /u01/app/oracle/product/10.2.0/db_1/dbs/db_09ifbgfe_PRIMARY_9
List of Datafiles in backup set 12
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
1 Full 555175 16-APR-07 /u01/app/oracle/oradata/PRIMARY/datafile/o1_mf_system_2yss9qlx_.dbf
4 Full 555175 16-APR-07 /u01/app/oracle/oradata/PRIMARY/datafile/o1_mf_users_2yss9r32_.dbf

The job launches two channels, capable of writing to the file system. Each channel will generate its own backup set, and they will be generated in parallel.

The preceding examples are of online backups, but if the target is in noarchivelog mode, RMAN can do offline backups.
An RMAN offline backup is done in mount mode. A user-managed offline backup done with operating system utilities is always done when the instance is shut down, because otherwise you
cannot guarantee that the datafile and controlfile copies will be read-consistent;
RMAN needs the target database controlfile to be mounted in order to get to its repository.
For this reason, RMAN offline backups are done in mount mode.

Offline backups are done in mount mode, but the database must have been cleanly shut down first. The backup will fail if the shutdown were a crash or a SHUTDOWN ABORT.

The following is an example of an offline backup:

RMAN> run
2> {
3> shutdown immediate;
4> startup mount;
5> backup as backupset database;
6> alter database open;
7> }

Click here to read the next step

Recovery Manager: 04 executing commands from shell scripts

2007-04-06T08:26:00.000+01:00

Click here to read the previous step

First of all... what is a shell ?
A shell is a command interpreter: when you issue commands to the Unix operating system they are translated into a form the kernel (a software layer able to communicate with the hardware layer to complete tasks such as writing to disk or using a printer) can understand and returns the results to you.
Users direct the operation of the computer by entering command input as text for a shell to execute.

In the Unix operating system there are available many types of shell, but they work fundamentally in the same ways. Since in the Unix operating system users can select which shell they want to use (which program should execute when they login), many shells have been developed:

sh: The Bourne shell, written by Steven Bourne. It is the original UNIX shell.The Bourne shell was the shell used in early versions of Unix and became a de facto standard; every Unix-like system has at least one shell compatible with the Bourne shell. The Bourne shell program is located in the UNIX file hierarchy at /bin/sh
csh: The C shell, a Unix shell developed by Bill Joy for the BSD Unix system, uses syntax similar to the C programming language. The C shell added many feature improvements over the Bourne shell, such as aliases and command history.
ksh: The Korn shell, a Unix shell developed by David Korn, is considered a superset of the Bourne shell. The main advantage of ksh over the traditional Unix shell is in its use as a programming language. Since its conception, several features were gradually added, while maintaining strong backwards compatibility with the Bourne shell.
bash: The Bourne Again Shell is the Linux default shell which includes features of the Bourne shell as well as the Korn, C, and TCSH shells.

It is possible to invoke any available shell from within another shell. To start a new shell, you
simply type the name of the shell you want to run, ksh, csh, or sh. It is also possible to set the default startup shell for all your future sessions. The default shell for your account is stored in the system database /etc/passwd, along with the other information about your account. To change your default shell, use the chsh command.

And now... what is a shell script ?
A shell script is simply a file containing a set of commands. The shell script looks just like any regular UNIX file, but it contains commands or conditional (if-then, while-do, until-do) statements that can be executed by the shell.

Now... let's go to analyse the following Rman shell script:
The EOF text strings act as markers that tell Unix that any commands between the EOFs are commands associated with the command to the immediate left... in our case... the rman command.
If RMAN encounters an error, it returns a nonzero exit code that is evaluated within the shell script.

#!/bin/ksh
#----------------------------------------------
export TARGET_CONN=sys/oracle
#----------------------------------------------
rman <<EOF

# Connect to the target and the catalog
connect target ${TARGET_CONN}
# Run the backup command.
run { allocate channel d1 type disk;
backup as compressed backupset datafile 4; }
EOF

#Test for success of RMAN operation
if [ $? -ne 0 ];
then
echo "RMAN shell script completed with problems..."
else
echo "RMAN shell script successful completed..."
fi
#----------------------------------------------
exit

Click here to read the next step

Recovery Manager: 03 executing commands from prompt and from file

2007-04-05T17:27:00.000+01:00

Click here to read the previous step.

Would you like to create a backup of your datafiles in the flash recovery area directory.?
Just type the following commands, but remember that the target database must be in archivelog mode (alter database archivelog) before running this on-line backup:

$ rman target / nocatalog

Recovery Manager: Release 10.2.0.1.0 - Production on Wed Apr 4 06:19:33 2007

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

connected to target database: PRIMARY (DBID=1453964262)
using target database control file instead of recovery catalog

Let's gather more information about our database.

RMAN> report schema;

Report of database schema

List of Permanent Datafiles
===========================
File Size(MB) Tablespace RB segs Datafile Name
---- -------- -------------------- ------- ------------------------
1 480 SYSTEM ***
/u01/app/oracle/oradata/PRIMARY/datafile/o1_mf_system_2yss9qlx_.dbf
2 25 UNDOTBS1 ***
/u01/app/oracle/oradata/PRIMARY/datafile/o1_mf_undotbs1_2yss9r16_.dbf
3 240 SYSAUX ***
/u01/app/oracle/oradata/PRIMARY/datafile/o1_mf_sysaux_2yss9qop_.dbf
4 5 USERS ***
/u01/app/oracle/oradata/PRIMARY/datafile/o1_mf_users_2yss9r32_.dbf

List of Temporary Files
=======================
File Size(MB) Tablespace Maxsize(MB) Tempfile Name
---- -------- -------------------- ----------- --------------------
1 25 TEMP 32767
/u01/app/oracle/oradata/PRIMARY/datafile/o1_mf_temp_2yssg3oo_.tmp

Now I choose I want a compressed backup of datafile number 4.

RMAN> run { allocate channel d1 type disk;
2> backup as compressed backupset datafile 4; }

allocated channel: d1
channel d1: sid=159 devtype=DISK

Starting backup at 04-APR-07
channel d1: starting compressed full datafile backupset
channel d1: specifying datafile(s) in backupset
input datafile fno=00004
name=/u01/app/oracle/oradata/PRIMARY/datafile/o1_mf_users_2yss9r32_.dbf
channel d1: starting piece 1 at 04-APR-07
channel d1: finished piece 1 at 04-APR-07
piece
handle=/u01/app/oracle/flash_recovery_area/PRIMARY/backupset/2007_04_04/o1_mf_nnndf_TAG20070404T062359_316b30rc_.bkp
tag=TAG20070404T062359 comment=NONE
channel d1: backup set complete, elapsed time: 00:00:03
Finished backup at 04-APR-07
released channel: d1

Now I choose I want a compressed backup of the tablespace USERS.

RMAN> run { allocate channel d1 type disk;
2> backup as compressed backupset tablespace users;}

allocated channel: d1
channel d1: sid=159 devtype=DISK

Starting backup at 04-APR-07
channel d1: starting compressed full datafile backupset
channel d1: specifying datafile(s) in backupset
input datafile fno=00004
name=/u01/app/oracle/oradata/PRIMARY/datafile/o1_mf_users_2yss9r32_.dbf
channel d1: starting piece 1 at 04-APR-07
channel d1: finished piece 1 at 04-APR-07
piece
handle=/u01/app/oracle/flash_recovery_area/PRIMARY/backupset/2007_04_04/o1_mf_nnndf_TAG20070404T062502_316b4yns_.bkp
tag=TAG20070404T062502 comment=NONE
channel d1: backup set complete, elapsed time: 00:00:01
Finished backup at 04-APR-07
released channel: d1

Because datafile number 4 and the tablespace USERS are the same thing, if you want to see
how many backup you have for the datafile number 4, you would see two different compressed backup.

RMAN> list backup of datafile 4;

List of Backup Sets
===================
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
4 Full 432.00K DISK 00:00:03 04-APR-07
BP Key: 4 Status: AVAILABLE Compressed: YES Tag:TAG20070404T062359
Piece Name:
/u01/app/oracle/flash_recovery_area/PRIMARY/backupset/2007_04_04/o1_mf_nnndf_TAG20070404T062359_316b30rc_.bkp
List of Datafiles in backup set 4
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
4 Full 545019 04-APR-07
/u01/app/oracle/oradata/PRIMARY/datafile/o1_mf_users_2yss9r32_.dbf

BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
5 Full 432.00K DISK 00:00:01 04-APR-07
BP Key: 5 Status: AVAILABLE Compressed: YES Tag:TAG20070404T062502
Piece Name:
/u01/app/oracle/flash_recovery_area/PRIMARY/backupset/2007_04_04/o1_mf_nnndf_TAG20070404T062502_316b4yns_.bkp
List of Datafiles in backup set 5
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
4 Full 545058 04-APR-07
/u01/app/oracle/oradata/PRIMARY/datafile/o1_mf_users_2yss9r32_.dbf

Now I choose I want a backup of the current control file.

RMAN> run { allocate channel d1 type disk;
2> backup as backupset current controlfile;}

allocated channel: d1
channel d1: sid=159 devtype=DISK

Starting backup at 04-APR-07
channel d1: starting full datafile backupset
channel d1: specifying datafile(s) in backupset
including current control file in backupset
channel d1: starting piece 1 at 04-APR-07
channel d1: finished piece 1 at 04-APR-07
piece
handle=/u01/app/oracle/flash_recovery_area/PRIMARY/backupset/2007_04_04/o1_mf_ncnnf_TAG20070404T062800_316bbjx4_.bkp
tag=TAG20070404T062800 comment=NONE
channel d1: backup set complete, elapsed time: 00:00:01
Finished backup at 04-APR-07
released channel: d1

And here it's the command to list how many backup I have for my control file.

RMAN> list backup of controlfile;

List of Backup Sets
===================

BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
6 Full 6.77M DISK 00:00:01 04-APR-07
BP Key: 6 Status: AVAILABLE Compressed: NO Tag: TAG20070404T062800
Piece Name:
/u01/app/oracle/flash_recovery_area/PRIMARY/backupset/2007_04_04/o1_mf_ncnnf_TAG20070404T062800_316bbjx4_.bkp
Control File Included: Ckp SCN: 545172 Ckp time: 04-APR-07

Now I just delete the backupset files number 4 and 5 and then I run again the same commands above, but this time from a file.

RMAN> delete backupset 4,5;

allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=159 devtype=DISK

List of Backup Pieces
BP Key BS Key Pc# Cp# Status Device Type Piece Name
------- ------- --- --- ----------- ----------- ----------
4 4 1 1 AVAILABLE DISK
/u01/app/oracle/flash_recovery_area/PRIMARY/backupset/2007_04_04/o1_mf_nnndf_TAG20070404T062359_316b30rc_.bkp
5 5 1 1 AVAILABLE DISK
/u01/app/oracle/flash_recovery_area/PRIMARY/backupset/2007_04_04/o1_mf_nnndf_TAG20070404T062502_316b4yns_.bkp

Do you really want to delete the above objects (enter YES or NO)? yes
deleted backup piece
backup piece
handle=/u01/app/oracle/flash_recovery_area/PRIMARY/backupset/2007_04_04/o1_mf_nnndf_TAG20070404T062359_316b30rc_.bkp
recid=4 stamp=618992640
deleted backup piece
backup piece
handle=/u01/app/oracle/flash_recovery_area/PRIMARY/backupset/2007_04_04/o1_mf_nnndf_TAG20070404T062502_316b4yns_.bkp
recid=5 stamp=618992702
Deleted 2 objects

Edit and prepare a Recovery Manager command file.

vi backup_users.rmn

Then type into it the following rman commands:

run
{
#allocate the channel
allocate channel d1 type disk;

#issue the backup command
backup as compressed backupset datafile 4;
}

Then execute that file using the following command.

$ rman target / nocatalog @backup_users.rmn log=backup_users.log

The log option is useful to analyse your backup status, indeed:

$vi backup_users.log

Recovery Manager: Release 10.2.0.1.0 - Production on Wed Apr 4 06:36:04 2007

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

connected to target database: PRIMARY (DBID=1453964262)
using target database control file instead of recovery catalog

RMAN> run
2> {
3> #allocate the channel
4> allocate channel d1 type disk;
5>
6> #issue the backup command
7> backup as compressed backupset datafile 4;
8> }
9>
10>
allocated channel: d1
channel d1: sid=153 devtype=DISK

Starting backup at 04-APR-07
channel d1: starting compressed full datafile backupset
channel d1: specifying datafile(s) in backupset
input datafile fno=00004
name=/u01/app/oracle/oradata/PRIMARY/datafile/o1_mf_users_2yss9r32_.dbf
channel d1: starting piece 1 at 04-APR-07
channel d1: finished piece 1 at 04-APR-07
piece
handle=/u01/app/oracle/flash_recovery_area/PRIMARY/backupset/2007_04_04/o1_mf_nnndf_TAG20070404T063611_316bsvjc_.bkp
tag=TAG20070404T063611 comment=NONE
channel d1: backup set complete, elapsed time: 00:00:01
Finished backup at 04-APR-07
released channel: d1

Recovery Manager complete.

My backup is gone well!!!