ORA-00214: controlfile inconsistent error after hiberning virtual machine

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

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)

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

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

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

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.