Mount ASM diskgroups with new ASM instance

Wednesday, October 29, 2014 0 comments
Imagine you have 11gR2 Oracle Restart configuration with database files located in ASM.

After server crash you realized that local disks are corrupted and with local disks you lost all Oracle installations. Even though this is important system you don’t have database backup (always take backups!).

But you managed to save all ASM disks as they were located on separate storage.


This will be small beginner guide on how to help yourself in such situation.


As old server crashed you must create new server configuration, identical as old configuration. Nice thing about ASM is that it keeps it’s metadata in disk header. If disks are intact and headers are not damaged you should be able to mount diskgroups with new ASM instance. But this new instance must be compatible with your diskgroups.


Grid Infrastrcuture and database software were 11.2.0.1 version and this version I will install on new server.

To keep this post short enough steps like creating users, installing ASMLib and other packages, configuring kernel parameters,... are excluded.


List Oracle ASM disks mounted to new server.
With "scandisks" command I will find devices which have been labeled as ASM disks.

# oracleasm scandisks
Reloading disk partitions: done
Cleaning any stale ASM disks...
Scanning system for ASM disks...

# oracleasm listdisks
DISK1
DISK2
DISK3
DISK4
DISK5
FRA1

Install "Oracle Grid Infrastructure software only" option to avoid automatic Oracle Restart and ASM configuration. This configuration will be performed later manually.

After installation finished run noted perl script as root to configure Grid Infrastructure for a Stand-Alone server.
For my configuration script looks like this:
To configure Grid Infrastructure for a Stand-Alone Server run the following command as the root user:
/u01/app/11.2.0.1/grid/perl/bin/perl -I/u01/app/11.2.0.1/grid/perl/lib -I/u01/app/11.2.0.1/grid/crs/install /u01/app/11.2.0.1/grid/crs/install/roothas.pl


Start cssd if it’s not running.

# ./crsctl stat res -t
--------------------------------------------------------------------------------
NAME           TARGET  STATE        SERVER                   STATE_DETAILS
--------------------------------------------------------------------------------
Cluster Resources
--------------------------------------------------------------------------------
ora.cssd
      1        OFFLINE OFFLINE
ora.diskmon
      1        OFFLINE OFFLINE

# ./crs_start ora.cssd
Attempting to start `ora.cssd` on member `asterix`
Attempting to stop `ora.diskmon` on member `asterix`
Stop of `ora.diskmon` on member `asterix` succeeded.
Attempting to start `ora.diskmon` on member `asterix`
Start of `ora.diskmon` on member `asterix` succeeded.
Start of `ora.cssd` on member `asterix` succeeded.


Create parameter file for ASM instance in $ORACLE_HOME/dbs directory of Grid Infrastructure.

init+ASM.ora
*.asm_diskstring='/dev/oracleasm/disks'
*.asm_power_limit=1
*.diagnostic_dest='/u01/app/grid'
*.instance_type='asm'
*.large_pool_size=12M
*.remote_login_passwordfile='EXCLUSIVE'


Register and start ASM instance.

$ export ORACLE_SID=+ASM
$ export ORACLE_HOME=/u01/app/11.2.0.1/grid
$ srvctl add asm -p $ORACLE_HOME/dbs/init+ASM.ora

$ srvctl start asm
$ srvctl status asm
ASM is running on asterix


Now notice what I see when I start ASM configuration assistant.

$ ./asmca



These are diskgroups with my database and recovery files.
Click "Mount all" to mount them all.






Install Oracle database software and create parameter file in "$ORACLE_HOME/dbs" to start database.

$ export ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1
$ export ORACLE_SID=ora11gr2

$ cd $ORACLE_HOME/dbs
$ cat initora11gr2.ora
*.spfile='+DATA1/ora11gr2/spfileora11gr2.ora'

$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Wed Oct 29 14:29:37 2014

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

Connected to an idle instance.

SQL> startup
ORACLE instance started.

Total System Global Area  668082176 bytes
Fixed Size                  2216344 bytes
Variable Size             222301800 bytes
Database Buffers          436207616 bytes
Redo Buffers                7356416 bytes
Database mounted.
Database opened.
SQL>
SQL>
SQL> select name from v$datafile;

NAME
--------------------------------------------------------------------------------
+DATA1/ora11gr2/datafile/system.297.844627929
+DATA1/ora11gr2/datafile/sysaux.265.844627967
+DATA1/ora11gr2/datafile/undotbs1.266.844627991
+DATA1/ora11gr2/datafile/users.267.844628031
+DATA2/ora11gr2/datafile/marko.261.859213577


Database is successfully opened and you can register instance using SRVCTL command.

$ srvctl add database -d $ORACLE_SID -o $ORACLE_HOME -p $ORACLE_HOME/dbs/initora11gr2.ora
$ srvctl start database -d $ORACLE_SID


Final status.

$ ./crsctl stat res -t
--------------------------------------------------------------------------------
NAME           TARGET  STATE        SERVER                   STATE_DETAILS
--------------------------------------------------------------------------------
Local Resources
--------------------------------------------------------------------------------
ora.DATA1.dg
               ONLINE  ONLINE       asterix
ora.DATA2.dg
               ONLINE  ONLINE       asterix
ora.FRA1.dg
               ONLINE  ONLINE       asterix
ora.asm
               ONLINE  ONLINE       asterix                  Started
--------------------------------------------------------------------------------
Cluster Resources
--------------------------------------------------------------------------------
ora.cssd
      1        ONLINE  ONLINE       asterix
ora.diskmon
      1        ONLINE  ONLINE       asterix
ora.ora11gr2.db
      1        ONLINE  ONLINE       asterix                  Open


Be aware that this demo is performed in virtual environment on my notebook.

Read More...

Increase disk space for VM running Linux

Friday, October 24, 2014 1 comments
When I create virtual machines on my notebook I always create too small disk for root partition or partition where I put Oracle binaries. After a while when I want to perform upgrade, or install another Oracle software, there is not enough space. This time I want to note steps about how to increase disk free space.

I can easily extend or shrink my logical volumes because I am using LVM in my virtual machines. Consider using LVM in production also because it gives you more flexibility then using normal hard drive partitions.

In this demo I'm using Oracle Linux 6.4.


Check disk free space after OS installation.

# df -h
Filesystem            Size  Used Avail Use% Mounted on
/dev/mapper/vg_linuxtest-lv_root
                      4.9G  2.8G  2.0G  59% /
tmpfs                 770M  100K  770M   1% /dev/shm
/dev/sda1             485M   55M  405M  12% /boot


Add "/u01" mount and assign some disk space for Oracle installation files.


Shutdown VM and add disk.


Partition new disk "/dev/sdb" using fdisk command.

# fdisk /dev/sdb
Device contains neither a valid DOS partition table, nor Sun, SGI or OSF disklabel
Building a new DOS disklabel with disk identifier 0xa07249dd.
Changes will remain in memory only, until you decide to write them.
After that, of course, the previous content won't be recoverable.

Warning: invalid flag 0x0000 of partition table 4 will be corrected by w(rite)

WARNING: DOS-compatible mode is deprecated. It's strongly recommended to
         switch off the mode (command 'c') and change display units to
         sectors (command 'u').

Command (m for help): n
Command action
   e   extended
   p   primary partition (1-4)
p
Partition number (1-4): 1
First cylinder (1-391, default 1):
Using default value 1
Last cylinder, +cylinders or +size{K,M,G} (1-391, default 391):
Using default value 391

Command (m for help): t
Selected partition 1
Hex code (type L to list codes): L

 0  Empty           24  NEC DOS         81  Minix / old Lin bf  Solaris
 1  FAT12           39  Plan 9          82  Linux swap / So c1  DRDOS/sec (FAT-
 2  XENIX root      3c  PartitionMagic  83  Linux           c4  DRDOS/sec (FAT-
 3  XENIX usr       40  Venix 80286     84  OS/2 hidden C:  c6  DRDOS/sec (FAT-
 4  FAT16 <32M      41  PPC PReP Boot   85  Linux extended  c7  Syrinx
 5  Extended        42  SFS             86  NTFS volume set da  Non-FS data
 6  FAT16           4d  QNX4.x          87  NTFS volume set db  CP/M / CTOS / .
 7  HPFS/NTFS       4e  QNX4.x 2nd part 88  Linux plaintext de  Dell Utility
 8  AIX             4f  QNX4.x 3rd part 8e  Linux LVM       df  BootIt
 9  AIX bootable    50  OnTrack DM      93  Amoeba          e1  DOS access
 a  OS/2 Boot Manag 51  OnTrack DM6 Aux 94  Amoeba BBT      e3  DOS R/O
 b  W95 FAT32       52  CP/M            9f  BSD/OS          e4  SpeedStor
 c  W95 FAT32 (LBA) 53  OnTrack DM6 Aux a0  IBM Thinkpad hi eb  BeOS fs
 e  W95 FAT16 (LBA) 54  OnTrackDM6      a5  FreeBSD         ee  GPT
 f  W95 Ext'd (LBA) 55  EZ-Drive        a6  OpenBSD         ef  EFI (FAT-12/16/
10  OPUS            56  Golden Bow      a7  NeXTSTEP        f0  Linux/PA-RISC b
11  Hidden FAT12    5c  Priam Edisk     a8  Darwin UFS      f1  SpeedStor
12  Compaq diagnost 61  SpeedStor       a9  NetBSD          f4  SpeedStor
14  Hidden FAT16 <3 63  GNU HURD or Sys ab  Darwin boot     f2  DOS secondary
16  Hidden FAT16    64  Novell Netware  af  HFS / HFS+      fb  VMware VMFS
17  Hidden HPFS/NTF 65  Novell Netware  b7  BSDI fs         fc  VMware VMKCORE
18  AST SmartSleep  70  DiskSecure Mult b8  BSDI swap       fd  Linux raid auto
1b  Hidden W95 FAT3 75  PC/IX           bb  Boot Wizard hid fe  LANstep
1c  Hidden W95 FAT3 80  Old Minix       be  Solaris boot    ff  BBT
1e  Hidden W95 FAT1
Hex code (type L to list codes): 8e
Changed system type of partition 1 to 8e (Linux LVM)

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

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


Notice that I have identified partition as "Linux LVM" choosing "8e" hex code.


Using pvcreate command create a physical volume for later use by the LVM.

# pvcreate /dev/sdb1
  Physical volume "/dev/sdb1" successfully created

Create new volume group "vg_orabin". Later I can add or remove disks from this volume group.

# vgcreate vg_orabin /dev/sdb1
  Volume group "vg_orabin" successfully created


Information about volume group.

# vgdisplay vg_orabin
  --- Volume group ---
  VG Name               vg_orabin
  System ID
  Format                lvm2
  Metadata Areas        1
  Metadata Sequence No  1
  VG Access             read/write
  VG Status             resizable
  MAX LV                0
  Cur LV                0
  Open LV               0
  Max PV                0
  Cur PV                1
  Act PV                1
  VG Size               2.99 GiB
  PE Size               4.00 MiB
  Total PE              766
  Alloc PE / Size       0 / 0
  Free  PE / Size       766 / 2.99 GiB
  VG UUID               h3N1o5-AlYF-9nkL-PXiB-P8HK-tGAa-GlXPa5


Create logical volume using disk space from volume group.

# lvcreate --extents 766 -n lv_orabin vg_orabin
  Logical volume "lv_orabin" created


Create and mount filesystem.

# mkfs.ext4 /dev/mapper/vg_orabin-lv_orabin
mke2fs 1.41.12 (17-May-2010)
Filesystem label=
OS type: Linux
Block size=4096 (log=2)
Fragment size=4096 (log=2)
Stride=0 blocks, Stripe width=0 blocks
196224< inodes, 784384 blocks
39219 blocks (5.00%) reserved for the super user
First data block=0
Maximum filesystem blocks=805306368
24 block groups
32768 blocks per group, 32768 fragments per group
8176 inodes per group
Superblock backups stored on blocks:
        32768, 98304, 163840, 229376, 294912

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

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




# mkdir /u01
# mount /dev/mapper/vg_orabin-lv_orabin /u01

Check disk space available.


# df -h
Filesystem            Size  Used Avail Use% Mounted on
/dev/mapper/vg_linuxtest-lv_root
                      4.9G  2.8G  2.0G  59% /
tmpfs                 770M   88K  770M   1% /dev/shm
/dev/sda1             485M   55M  405M  12% /boot
/dev/mapper/vg_orabin-lv_orabin
                      3.0G   69M  2.8G   3% /u01


Hm, 2.8G is not enough free space for me. Let’s extend this mount adding another disk.



Shutdown VM and add disk.


Partition new disk and create physical volume for LVM.

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

Warning: invalid flag 0x0000 of partition table 4 will be corrected by w(rite)

WARNING: DOS-compatible mode is deprecated. It's strongly recommended to
         switch off the mode (command 'c') and change display units to
         sectors (command 'u').

Command (m for help): n
Command action
   e   extended
   p   primary partition (1-4)
p
Partition number (1-4): 1
First cylinder (1-652, default 1):
Using default value 1
Last cylinder, +cylinders or +size{K,M,G} (1-652, default 652):
Using default value 652

Command (m for help): t
Selected partition 1
Hex code (type L to list codes): 8e
Changed system type of partition 1 to 8e (Linux LVM)

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

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




# pvcreate /dev/sdc1
  Physical volume "/dev/sdc1" successfully created

Check current status of volume group “vg_orabin”.

# vgdisplay vg_orabin
  --- Volume group ---
  VG Name               vg_orabin
  System ID
  Format                lvm2
  Metadata Areas        1
  Metadata Sequence No  2
  VG Access             read/write
  VG Status             resizable
  MAX LV                0
  Cur LV                1
  Open LV               0
  Max PV                0
  Cur PV                1
  Act PV                1
  VG Size               2.99 GiB
  PE Size               4.00 MiB
  Total PE              766
  Alloc PE / Size       766 / 2.99 GiB
  Free  PE / Size       0 / 0
  VG UUID               h3N1o5-AlYF-9nkL-PXiB-P8HK-tGAa-GlXPa5

Extend volume group by adding physical volume "/dev/sdc1" using vgextend command.

# vgextend vg_orabin /dev/sdc1
  Volume group "vg_orabin" successfully extended


Check volume group size - it is extended from 2.99G to 7.98G.

# vgdisplay vg_orabin
  --- Volume group ---
  VG Name               vg_orabin
  System ID
  Format                lvm2
  Metadata Areas        2
  Metadata Sequence No  3
  VG Access             read/write
  VG Status             resizable
  MAX LV                0
  Cur LV                1
  Open LV               0
  Max PV                0
  Cur PV                2
  Act PV                2
  VG Size               7.98 GiB
  PE Size               4.00 MiB
  Total PE              2044
  Alloc PE / Size       766 / 2.99 GiB
  Free  PE / Size       1278 / 4.99 GiB
  VG UUID               h3N1o5-AlYF-9nkL-PXiB-P8HK-tGAa-GlXPa5


Using pvscan command scan all disks and notice physical volumes with free space.

# pvscan
  PV /dev/sdb1   VG vg_orabin      lvm2 [2.99 GiB / 0    free]
  PV /dev/sdc1   VG vg_orabin      lvm2 [4.99 GiB / 4.99 GiB free]
  PV /dev/sda2   VG vg_linuxtest   lvm2 [6.51 GiB / 0    free]
  Total: 3 [14.49 GiB] / in use: 3 [14.49 GiB] / in no VG: 0 [0   ]


With lvdisplay command display logical volume properties
Notice LV size = 2.99G.

# lvdisplay /dev/vg_orabin/lv_orabin
  --- Logical volume ---
  LV Path                /dev/vg_orabin/lv_orabin
  LV Name                lv_orabin
  VG Name                vg_orabin
  LV UUID                ypw9X1-vIsM-4rVF-NtVB-ACrf-f5nh-25p2sn
  LV Write Access        read/write
  LV Creation host, time linuxtest.localdomain, 2014-10-23 13:19:56 +0200
  LV Status              available
  # open                 0
  LV Size                2.99 GiB
  Current LE             766
  Segments               1
  Allocation             inherit
  Read ahead sectors     auto
  - currently set to     256
  Block device           252:2


I will add only 2G (of 5G) using lvextend command.

# lvextend -L +2G /dev/mapper/vg_orabin-lv_orabin /dev/sdc1
  Extending logical volume lv_orabin to 4.99 GiB
  Logical volume lv_orabin successfully resized


Mount volume and check for free space.

# mount /dev/mapper/vg_orabin-lv_orabin /u01

# df -h
Filesystem            Size  Used Avail Use% Mounted on
/dev/mapper/vg_linuxtest-lv_root
                      4.9G  2.8G  2.0G  59% /
tmpfs                 770M   88K  770M   1% /dev/shm
/dev/sda1             485M   55M  405M  12% /boot
/dev/mapper/vg_orabin-lv_orabin
                      3.0G   69M  2.8G   3% /u01


Resize filesystem using resize2fs command:
# resize2fs /dev/mapper/vg_orabin-lv_orabin
resize2fs 1.41.12 (17-May-2010)
Filesystem at /dev/mapper/vg_orabin-lv_orabin is mounted on /u01; on-line resizing required
old desc_blocks = 1, new_desc_blocks = 1
Performing an on-line resize of /dev/mapper/vg_orabin-lv_orabin to 1308672 (4k) blocks.
The filesystem on /dev/mapper/vg_orabin-lv_orabin is now 1308672 blocks long.


Now I have 4.6G free space for "/u01" mount.

# df -h
Filesystem            Size  Used Avail Use% Mounted on
/dev/mapper/vg_linuxtest-lv_root
                      4.9G  2.8G  2.0G  59% /
tmpfs                 770M   88K  770M   1% /dev/shm
/dev/sda1             485M   55M  405M  12% /boot
/dev/mapper/vg_orabin-lv_orabin
                      5.0G   70M  4.6G   2% /u01



===========================================

Now I will try to extend root partition.

Newer Oracle Linux releases are using LVM by default during install.
Let’s see can I increase my root partition using commands above.


Display information about logical volumes using lvs command.

# lvs
  LV        VG           Attr      LSize Pool Origin Data%  Move Log Cpy%Sync Convert
  lv_root   vg_linuxtest -wi-ao--- 4.97g
  lv_swap   vg_linuxtest -wi-ao--- 1.54g
  lv_orabin vg_orabin    -wi-a---- 4.99g

Check free space.
# df -h
Filesystem            Size  Used Avail Use% Mounted on
/dev/mapper/vg_linuxtest-lv_root
                      4.9G  2.8G  2.0G  59% /
tmpfs                 770M   88K  770M   1% /dev/shm
/dev/sda1             485M   55M  405M  12% /boot


Shutdown VM and add disk for extending root partition.


Partition new disk and create physical volume for LVM.

# fdisk /dev/sdd
Device contains neither a valid DOS partition table, nor Sun, SGI or OSF disklabel
Building a new DOS disklabel with disk identifier 0xf0608435.
Changes will remain in memory only, until you decide to write them.
After that, of course, the previous content won't be recoverable.

Warning: invalid flag 0x0000 of partition table 4 will be corrected by w(rite)

WARNING: DOS-compatible mode is deprecated. It's strongly recommended to
         switch off the mode (command 'c') and change display units to
         sectors (command 'u').

Command (m for help): n
Command action
   e   extended
   p   primary partition (1-4)
p
Partition number (1-4): 1
First cylinder (1-652, default 1):
Using default value 1
Last cylinder, +cylinders or +size{K,M,G} (1-652, default 652):
Using default value 652

Command (m for help): t
Selected partition 1
Hex code (type L to list codes): 8e
Changed system type of partition 1 to 8e (Linux LVM)

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

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



# pvcreate /dev/sdd1
  Physical volume "/dev/sdd1" successfully created


Check information about volume group.

# vgdisplay vg_linuxtest

  --- Volume group ---
  VG Name               vg_linuxtest
  System ID
  Format                lvm2
  Metadata Areas        1
  Metadata Sequence No  3
  VG Access             read/write
  VG Status             resizable
  MAX LV                0
  Cur LV                2
  Open LV               2
  Max PV                0
  Cur PV                1
  Act PV                1
  VG Size               6.51 GiB
  PE Size               4.00 MiB
  Total PE              1666
  Alloc PE / Size       1666 / 6.51 GiB
  Free  PE / Size       0 / 0
  VG UUID               TXkKYl-PIxu-s2xk-LsEB-sgTZ-TdcO-8wapCV

Extend volume group using new physical volume.

# vgextend vg_linuxtest /dev/sdd1
  Volume group "vg_linuxtest" successfully extended

Logical volume status.
# lvdisplay  /dev/vg_linuxtest/lv_root
  --- Logical volume ---
  LV Path                /dev/vg_linuxtest/lv_root
  LV Name                lv_root
  VG Name                vg_linuxtest
  LV UUID                VNgeT7-4yhd-XqRi-2da1-XTqT-qTvm-oVK2pz
  LV Write Access        read/write
  LV Creation host, time linuxtest.localdomain, 2014-10-23 10:30:21 +0200
  LV Status              available
  # open                 1
  LV Size                4.97 GiB
  Current LE             1272
  Segments               1
  Allocation             inherit
  Read ahead sectors     auto
  - currently set to     256
  Block device           252:0


Extend logical volume.
# lvextend /dev/mapper/vg_linuxtest-lv_root /dev/sdd1
  Extending logical volume lv_root to 9.96 GiB
  Logical volume lv_root successfully resized

Resize filesystem.

# resize2fs /dev/mapper/vg_linuxtest-lv_root
resize2fs 1.41.12 (17-May-2010)
Filesystem at /dev/mapper/vg_linuxtest-lv_root is mounted on /; on-line resizing required
old desc_blocks = 1, new_desc_blocks = 1
Performing an on-line resize of /dev/mapper/vg_linuxtest-lv_root to 2611200 (4k) blocks.
The filesystem on /dev/mapper/vg_linuxtest-lv_root is now 2611200 blocks long.


Check disk free space. Notice that I have 6.6G of free space for my root partition.

# df -h
Filesystem            Size  Used Avail Use% Mounted on
/dev/mapper/vg_linuxtest-lv_root
                      9.9G  2.8G  6.6G  30% /
tmpfs                 770M   88K  770M   1% /dev/shm
/dev/sda1             485M   55M  405M  12% /boot



WARNING! Be very careful when using commands from blog post on your production system. These are dangerous commands which can cause loss of data or many other problems. I’ve used this commands in my test environment for educational purpose and it is possible that I have made mistakes in this demo. After all I am only simple Oracle DBA not Linux SA :-)


REFERENCES
http://www.linuxuser.co.uk/features/resize-your-disks-on-the-fly-with-lvm
http://www.rootusers.com/how-to-increase-the-size-of-a-linux-lvm-by-adding-a-new-disk/
https://wiki.archlinux.org/index.php/LVM

Read More...

Using Oracle Flex ASM with single instance database

Friday, July 25, 2014 0 comments
Oracle Flex ASM was introduced in 12c version. This is one of the best features introduced with new version in my opinion.

I won’t speak in detail about Flex ASM because you can find more information in documentation. In this post I will concentrate on how Flex ASM handles crash of ASM instance.

For this test I’ve created 2 node cluster - 12c Grid Infrastructure with Flex ASM enabled.

$ asmcmd showclustermode
ASM cluster : Flex mode enabled

$ srvctl config asm ASM home: /u01/app/12.1.0/grid_1 Password file: +OCRVOTE/ASM/PASSWORD/pwdasm.256.853771307 ASM listener: LISTENER ASM instance count: ALL Cluster ASM listener: ASMNET1LSNR_ASM
$ srvctl status asm ASM is running on cluster1,cluster2


Install single instance database on one of the nodes.

$ ./dbca -silent \
> -createDatabase \
> -templateName General_Purpose.dbc \
> -gdbName singl12 \
> -sid singl12 \
> -sysPassword oracle \
> -SystemPassword oracle \
> -emConfiguration none \
> -recoveryAreaDestination FRA \
> -storageType ASM \
> -asmSysPassword oracle \
> -diskGroupName DATA \
> -characterSet AL32UTF8 \
> -nationalCharacterSet AL16UTF16 \
> -totalMemory 768 \

Copying database files 1% complete 3% complete 10% complete 17% complete 24% complete 31% complete 35% complete Creating and starting Oracle instance 37% complete 42% complete 47% complete 52% complete 53% complete 56% complete 58% complete Registering database with Oracle Restart 64% complete Completing Database Creation 68% complete 71% complete 75% complete 85% complete 96% complete 100% complete Look at the log file "/u01/app/orcl12/cfgtoollogs/dbca/singl12/singl12.log" for further details.


Single instance database is registered to the OCR.

$ srvctl config database -d singl12
Database unique name: singl12
Database name: singl12
Oracle home: /u01/app/orcl12/product/12.1.0/dbhome_1
Oracle user: orcl12
Spfile: +DATA/singl12/spfilesingl12.ora
Password file:
Domain:
Start options: open
Stop options: immediate
Database role: PRIMARY
Management policy: AUTOMATIC
Server pools: singl12
Database instance: singl12
Disk Groups: DATA
Mount point paths:
Services:
Type: SINGLE  <<<<<-------
Database is administrator managed

V$ASM_CLIENT shows that my database is managed by the Oracle ASM instance.

SQL> select instance_name, db_name, status
  2  from v$asm_client
  3  where db_name='singl12';

INSTANCE_NAME        DB_NAME  STATUS
-------------------- -------- ------------
singl12              singl12  CONNECTED


Check that ASM instances are running on both nodes.

$ ./crsctl status resource ora.asm
NAME=ora.asm
TYPE=ora.asm.type
TARGET=ONLINE            , ONLINE
STATE=ONLINE on cluster2, ONLINE on cluster1


My database is running on cluster1 node.

$ srvctl status database -d singl12
Instance singl12 is running on node cluster1

SQL> select instance_name, host_name from v$instance; INSTANCE_NAME HOST_NAME --------------- -------------------- singl12 cluster1.localdomain

Now I will simulate crash of ASM instance on cluster1 node where I have my database running.

# ps -ef|grep asm_pmon|grep -v grep
oracle    3072     1  0 10:12 ?        00:00:01 asm_pmon_+ASM1

# kill -9 3072

Without Flex ASM I would expect that crash of ASM instance would crash database instance also but with Flex ASM my database stays up and running.

Check alert log of database instance:
...
NOTE: ASMB registering with ASM instance as client 0x10005 (reg:2156157897)
NOTE: ASMB connected to ASM instance +ASM2 (Flex mode; client id 0x10005)
NOTE: ASMB rebuilding ASM server state
NOTE: ASMB rebuilt 1 (of 1) groups
NOTE: ASMB rebuilt 13 (of 13) allocated files
NOTE: fetching new locked extents from server
NOTE: 0 locks established; 0 pending writes sent to server
SUCCESS: ASMB reconnected & completed ASM server state

Check line - "NOTE: ASMB connected to ASM instance +ASM2 (Flex mode; client id 0x10005)"

As +ASM1 instance crashed ASMB connected to ASM instance +ASM2.


Check status:
# ./crsctl status resource ora.asm
NAME=ora.asm
TYPE=ora.asm.type
TARGET=ONLINE            , ONLINE
STATE=ONLINE on cluster2, INTERMEDIATE on cluster1


SQL> select instance_name, host_name from v$instance;

INSTANCE_NAME   HOST_NAME
--------------- --------------------
singl12         cluster1.localdomain

Oracle Clusterware restarted crashed ASM instance and both instances were up in a minute.

# ./crsctl status resource ora.asm
NAME=ora.asm
TYPE=ora.asm.type
TARGET=ONLINE            , ONLINE
STATE=ONLINE on cluster2, ONLINE on cluster1

Now to test crash ASM instance on second node.

SQL> select instance_name from v$instance;

INSTANCE_NAME
----------------
+ASM2

SQL> shutdown abort;
ASM instance shutdown

Excerpt from alertlog:

...
Fri Jul 25 12:44:33 2014
NOTE: ASMB registering with ASM instance as client 0x10005 (reg:4169355750)
NOTE: ASMB connected to ASM instance +ASM1 (Flex mode; client id 0x10005)
NOTE: ASMB rebuilding ASM server state
NOTE: ASMB rebuilt 1 (of 1) groups
NOTE: ASMB rebuilt 13 (of 13) allocated files
NOTE: fetching new locked extents from server
NOTE: 0 locks established; 0 pending writes sent to server
SUCCESS: ASMB reconnected & completed ASM server state


Again, user connected to database instance didn’t even noticed that something is happening with ASM.

Flex ASM enables for ASM instance to run on separate nodes than database servers. If ASM instance fails database will failover to another available ASM instance.

In case you are running <12c databases on your cluster you can still configure Flex ASM but you are required to configure local ASM instances on nodes. ASM instance failover won’t work for 10g or 11g databases.

Good reason to move towards 12c? ;-)



Read More...

ORA-19909: datafile 1 belongs to an orphan incarnation

Thursday, July 17, 2014 1 comments
I love to read Oracle related blogs, forum posts and mailing lists much more often than books. Why? Because there many Oracle DBA’s and developers share their experiences, problems, "best practices",... which are very valuable to me.

It's great that we have so big and active Oracle community.

Today I noticed mail from Oracle-L list where someone asked for help with recovery after overwriting production controlfiles. Check Oracle-L for more info.

It reminded me that I haven’t played with controlfile recoveries for a while.
Mistakes or various disasters could happen when you least expect it. When problem occurs it is essential that DBA is confident with recovery procedure.
Confidence comes only with practice. Create test environment and enjoy in destroying/recovering databases.
Of course - be creative with disasters :)


So I’ve imagined one scenario and decided to share it in form of blog post.


Create backup of current controlfile.

RMAN> backup current controlfile;

Starting backup at 17.07.2014 13:45:29
using channel ORA_DISK_1
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
including current control file in backup set
channel ORA_DISK_1: starting piece 1 at 17.07.2014 13:45:30
channel ORA_DISK_1: finished piece 1 at 17.07.2014 13:45:33
piece handle=/u01/oradata/fra/ORAKL/backupset/2014_07_17/o1_mf_ncnnf_TAG20140717T142921_9whjf2sc_.bkp tag=TAG20140717T134529 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:03
Finished backup at 17.07.2014 13:45:33

After that I’ve performed incomplete recovery and opened database with resetlogs option.

...
...
...
SQL> alter database open resetlogs;
Database altered.

Create small status table and insert one row.

SQL> create table admin.test (datum date);
Table created.

SQL> insert into admin.test values(sysdate);
1 row created.

SQL> commit;
Commit complete.

SQL> select to_char(datum,'dd.mm.yyyy hh24:mi:ss') from admin.test;

TO_CHAR(DATUM,'DD.M
-------------------
17.07.2014 14:44:44


Where are my controlfiles:

SQL> select name from v$controlfile;

NAME
--------------------------------------------------------------------------------
/u01/oradata/orakl/orakl/control01.ctl
/u01/oradata/orakl/orakl/control02.ctl
/u01/oradata/orakl/orakl/control03.ctl

Overwrite all controlfiles with random file to simulate problem.

$ cp o1_mf_s_853163282_9whfzmk1_.bkp /u01/oradata/orakl/orakl/control01.ctl
$ cp o1_mf_s_853163282_9whfzmk1_.bkp /u01/oradata/orakl/orakl/control02.ctl
$ cp o1_mf_s_853163282_9whfzmk1_.bkp /u01/oradata/orakl/orakl/control03.ctl

After that instance crashed with error in alertlog:

Thu Jul 17 14:45:30 2014
Errors in file /u01/app/oracle/diag/rdbms/orakl/orakl/trace/orakl_ckpt_8103.trc:
ORA-00201: control file version  incompatible with ORACLE version
ORA-00202: control file: '/u01/oradata/orakl/orakl/control01.ctl'
CKPT (ospid: 8103): terminating the instance due to error 201
Instance terminated by CKPT, pid = 8103

To perform quick recovery I’ve restored previously backed up controlfile and mounted database.

RMAN> run
2> {
3> restore controlfile from '/u01/oradata/fra/ORAKL/backupset/2014_07_17/o1_mf_ncnnf_TAG20140717T142921_9whjf2sc_.bkp';
4> }

Starting restore at 17.07.2014 14:46:34
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=153 device type=DISK

channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:03
output file name=/u01/oradata/orakl/orakl/control01.ctl
output file name=/u01/oradata/orakl/orakl/control02.ctl
output file name=/u01/oradata/orakl/orakl/control03.ctl
Finished restore at 17.07.2014 14:46:37

RMAN> alter database mount;
database mounted

Recover database using backup controlfile.

SQL> recover database using backup controlfile;
ORA-00283: recovery session canceled due to errors
ORA-19909: datafile 1 belongs to an orphan incarnation
ORA-01110: data file 1: '/u01/oradata/orakl/orakl/system01.dbf'

Recovery failed with ORA-19909 error.
If you remember I’ve performed incomplete recovery after creating backup of controlfile.
Controlfile backup belongs to another incarnation, and I don’t have fresh controlfile backup.


I will use this “old” controlfile and create SQL script to reproduce new control file.

SQL> alter database backup controlfile to trace as '/tmp/ctlfile.sql';

Database altered.

Delete unnecessary lines and create SQL script "/tmp/ctlfile.sql":

STARTUP NOMOUNT
CREATE CONTROLFILE REUSE DATABASE "ORAKL" RESETLOGS  ARCHIVELOG
    MAXLOGFILES 16
    MAXLOGMEMBERS 3
    MAXDATAFILES 100
    MAXINSTANCES 8
    MAXLOGHISTORY 292
LOGFILE
  GROUP 1 '/u01/oradata/orakl/orakl/redo01.log'  SIZE 50M,
  GROUP 2 '/u01/oradata/orakl/orakl/redo02.log'  SIZE 50M,
  GROUP 3 '/u01/oradata/orakl/orakl/redo03.log'  SIZE 50M
DATAFILE
  '/u01/oradata/orakl/orakl/system01.dbf',
  '/u01/oradata/orakl/orakl/sysaux01.dbf',
  '/u01/oradata/orakl/orakl/undotbs01.dbf',
  '/u01/oradata/orakl/orakl/users01.dbf'
CHARACTER SET UTF8
;

Shutdown instance and create controlfile.

SQL> shutdown abort;
ORACLE instance shut down.
SQL>
SQL>
SQL> @/tmp/ctlfile.sql
ORACLE instance started.

Total System Global Area  626327552 bytes
Fixed Size                  2162280 bytes
Variable Size             171966872 bytes
Database Buffers          444596224 bytes
Redo Buffers                7602176 bytes

Control file created.

Use information from redologs for recovery.

SQL> select member, status from v$logfile;

MEMBER                                                                 STATUS
---------------------------------------------------------------------- ---------
/u01/oradata/orakl/orakl/redo03.log                                    STALE
/u01/oradata/orakl/orakl/redo02.log                                    STALE
/u01/oradata/orakl/orakl/redo01.log                                    STALE

SQL> recover database using backup controlfile;
ORA-00279: change 7015200 generated at 07/17/2014 14:43:54 needed for thread 1
ORA-00289: suggestion :
/u01/oradata/fra/ORAKL/archivelog/2014_07_17/o1_mf_1_1_%u_.arc
ORA-00280: change 7015200 for thread 1 is in sequence #1


Specify log: {=suggested | filename | AUTO | CANCEL}
/u01/oradata/orakl/orakl/redo01.log
Log applied.
Media recovery complete.

Open database with resetlogs.

SQL> alter database open resetlogs;

Database altered.

Check status table.

SQL> select to_char(datum,'dd.mm.yyyy hh24:mi:ss') from admin.test;

TO_CHAR(DATUM,'DD.M
-------------------
17.07.2014 14:44:44

Check incarnations:

RMAN> list incarnation;

using target database controlfile instead of recovery catalog

List of Database Incarnations
DB Key  Inc Key DB Name  DB ID            STATUS  Reset SCN  Reset Time
------- ------- -------- ---------------- --- ---------- ----------
2       2       ORAKL    3724279545       PARENT  641001     12.10.2013 15:30:46
1       1       ORAKL    3724279545       PARENT  7015197    17.07.2014 14:43:44
3       3       ORAKL    3724279545       CURRENT 7015386    17.07.2014 14:55:50



Cheers!

Read More...

DBMS_SPM.LOAD_PLAN_FROM_CURSOR_CACHE typo

Friday, June 27, 2014 0 comments
I've played a little with Oracle SQL Plan Management and something caught my eye.

Let's create simple test.
SQL> select /* test_spm */ count(*) from admin.objekti;

  COUNT(*)
----------
     76378

SQL> set lines 200
SQL> col sql_text for a50 wrapped
SQL> select sql_id, sql_text from v$sql
  2  where sql_text like '%test_spm%';

SQL_ID        SQL_TEXT
------------- --------------------------------------------------
cpzgdw9swdvzk select /* test_spm */ count(*) from admin.objekti
ap9td7vafq26n select sql_id, sql_text from v$sql where sql_text
              like '%test_spm%'

I will try to load plan from the cursor cache using SQL_ID.
SQL> var cnt number
SQL> execute :cnt := DBMS_SPM.LOAD_PLAN_FROM_CURSOR_CACHE(sql_id => 'cpzgdw9swdvzk');
BEGIN :cnt := DBMS_SPM.LOAD_PLAN_FROM_CURSOR_CACHE(sql_id => 'cpzgdw9swdvzk'); END;

                       *
ERROR at line 1:
ORA-06550: line 1, column 24:
PLS-00302: component 'LOAD_PLAN_FROM_CURSOR_CACHE' must be declared
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored

Hm... PLS-00302 error when I use LOAD_PLAN_FROM_CURSOR_CACHE.

Change function name and use PLANS instead of PLAN.
SQL> execute :cnt := DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE(sql_id => 'cpzgdw9swdvzk');

PL/SQL procedure successfully completed.

SQL> print cnt

       CNT
----------
         1

Now everything works as it should.

It is well written in documentation to use PLANS:

LOAD_PLANS_FROM_CURSOR_CACHE Functions

This function loads one or more plans present in the cursor cache for a SQL statement, or a set of SQL statements. It has four overloads: using SQL statement text, using SQL handle, using SQL ID, or using attribute_name and attribute_value pair.

But, try to search LOAD_PLAN_FROM_CURSOR_CACHE on Google.

There are many sites mentioning that procedure.

Even in Oracle documentation - "Oracle® Database Upgrade Guide 12c Release 1 (12.1)" (and earlier releases) this procedure is mentioned:

1. In the source release of Oracle Database, use the DBMS_SPM.LOAD_PLAN_FROM_CURSOR_CACHE procedure or Oracle Enterprise Manager to load all of the execution plans in the cursor cache into the SQL Management Base.


Now I'm a bit confused.

Let's check procedure names on three database releases.


12.1.0.1.0
SQL> select procedure_name from dba_procedures
  2  where procedure_name like 'LOAD_PLAN%';

PROCEDURE_NAME
--------------------------------------------------------------------------------------------------------------------------------
LOAD_PLANS_SET
LOAD_PLANS_FROM_SQLSET
LOAD_PLANS_FROM_CURSOR_CACHE
LOAD_PLANS_FROM_CURSOR_CACHE
LOAD_PLANS_FROM_CURSOR_CACHE
LOAD_PLANS_FROM_CURSOR_CACHE

6 rows selected.

11.2.0.4.0
SQL> select procedure_name from dba_procedures
  2  where procedure_name like 'LOAD_PLAN%';

PROCEDURE_NAME
------------------------------
LOAD_PLANS_SET
LOAD_PLANS_FROM_SQLSET
LOAD_PLANS_FROM_CURSOR_CACHE
LOAD_PLANS_FROM_CURSOR_CACHE
LOAD_PLANS_FROM_CURSOR_CACHE
LOAD_PLANS_FROM_CURSOR_CACHE

6 rows selected.

11.1.0.7.0
SQL> select procedure_name from dba_procedures
  2  where procedure_name like 'LOAD_PLAN%';

PROCEDURE_NAME
------------------------------
LOAD_PLANS_SET
LOAD_PLANS_FROM_SQLSET
LOAD_PLANS_FROM_CURSOR_CACHE
LOAD_PLANS_FROM_CURSOR_CACHE
LOAD_PLANS_FROM_CURSOR_CACHE
LOAD_PLANS_FROM_CURSOR_CACHE

6 rows selected.


Still not a sign about LOAD_PLAN_FROM_CURSOR_CACHE or maybe I'm looking in wrong direction.

At the end I think that this is just often used typo.



Read More...

RAC One Node 12cR1 Instance placement after failover

Wednesday, June 25, 2014 0 comments
RAC One Node is Oracle active-passive solution very similar to RAC which is active-active solution.
Running active-passive solution using Oracle Clusterware was possible even before introduction of RAC One Node, but RAC One node brought configuration and managing of active-passive cluster to another level.

In my test environment I've created active-passive cluster using 12cR1 Grid Infrastructure with 12cR1 RAC One Node database.

I've tested failover for Oracle database and noticed interesting behavior with instance placement.


Configuration of my database:
$ srvctl config database -d one12
Database unique name: one12
Database name: one12
Oracle home: /u01/app/orcl12/product/12.1.0/dbhome_1
Oracle user: orcl12
Spfile: +DATA/one12/spfileone12.ora
Password file: +DATA/one12/orapwone12
Domain:
Start options: open
Stop options: immediate
Database role: PRIMARY
Management policy: AUTOMATIC
Server pools: one12
Database instances:
Disk Groups: DATA,FRA
Mount point paths:
Services: one12app
Type: RACOneNode
Online relocation timeout: 30
Instance name prefix: one12
Candidate servers: cluster1,cluster2
Database is administrator managed

Notice type is RACOneNode which is specified for RAC One Node database "one12".


Check status of database.
$ srvctl status database -d one12
Instance one12_1 is running on node cluster1
Online relocation: INACTIVE
col instance_name for a20
col host_name for a20
SQL> select host_name, instance_name from v$instance;

HOST_NAME            INSTANCE_NAME
-------------------- --------------------
cluster1.localdomain one12_1

Instance one12_1 is running on node cluster1.


Let's relocate database to another node.
$ srvctl relocate database -d one12 -n cluster2 -w 5 -v
Configuration updated to two instances
Instance one12_2 started
Services relocated
Waiting for up to 5 minutes for instance one12_1 to stop ...
Instance one12_1 stopped
Configuration updated to one instance

Check status.
$ srvctl status database -d one12
Instance one12_2 is running on node cluster2
Online relocation: INACTIVE
col instance_name for a20
col host_name for a20
SQL> select host_name, instance_name from v$instance;

HOST_NAME            INSTANCE_NAME
-------------------- --------------------
cluster2.localdomain one12_2

After relocation active node became cluster2. Instance one12_2 was brought up on cluster2 node while one12_1 was shut down on cluster1 node.



Now I will simulate instance crash killing PMON process on cluster2 node. Clusterware will restart failed instance few times on the same node and then relocate database to cluster1 node.

You can check in resource parameters how many restart attempts will Clusterware perform before failover to other node.

$ ./crsctl stat res ora.one12.db -p|grep "RESTART_ATTEMPTS"

RESTART_ATTEMPTS=2

In my case Clusterware will try twice to restart failed instance before relocating to other node.

Check status of resource.
$ ./crsctl stat res ora.one12.db -v |\
> grep -w 'STATE\|RESTART_COUNT\|FAILURE_COUNT'

STATE=ONLINE on cluster2
RESTART_COUNT=0
FAILURE_COUNT=0

Kill PMON on cluster2 node.
$ ps -ef|grep pmon_one12|grep -v grep
orcl12    4503     1  0 12:19 ?        00:00:00 ora_pmon_one12_2

$ kill -9 4503

Wait for a short time for Clusterware to restart instance and check status.
$ ./crsctl stat res ora.one12.db -v |\
> grep -w 'STATE\|RESTART_COUNT\|FAILURE_COUNT'

STATE=ONLINE on cluster2
RESTART_COUNT=1
FAILURE_COUNT=0

Clusterware restarted instance on the same node. Notice RESTART_COUNT is now 1.

Kill PMON again.
$ ps -ef|grep pmon_one12|grep -v grep
orcl12    5262     1  0 12:37 ?        00:00:00 ora_pmon_one12_2

$ kill -9 5262

Check status.
$ ./crsctl stat res ora.one12.db -v |\
> grep -w 'STATE\|RESTART_COUNT\|FAILURE_COUNT'

STATE=ONLINE on cluster2
RESTART_COUNT=2
FAILURE_COUNT=0

RESTART_COUNT is now 2. Next time I kill PMON process Clusterware should relocate instance to cluster1 node.

$ ps -ef|grep pmon_one12|grep -v grep
orcl12    5588     1  0 12:40 ?        00:00:00 ora_pmon_one12_2

$ kill -9 5588

Check status.
$ ./crsctl stat res ora.one12.db -v |\
> grep -w 'STATE\|RESTART_COUNT\|FAILURE_COUNT'

STATE=ONLINE on cluster1
RESTART_COUNT=0
FAILURE_COUNT=1

Clusterware started instance on cluster1 node as expected. STATE is now ONLINE on cluster1.


Now the interesting part.
$ srvctl status database -d one12
Instance one12_2 is running on node cluster1
Online relocation: INACTIVE
col instance_name for a20
col host_name for a20
SQL> select host_name, instance_name from v$instance;

HOST_NAME            INSTANCE_NAME
-------------------- --------------------
cluster1.localdomain one12_2

Notice instance placement.
Instance one12_2 is started on cluster1 node. Clusterware kept instance name one12_2 after failover to cluster1 node.


Can we start instance one12_1 on cluster1 node manually.
$ srvctl stop database -d one12

$ srvctl start instance -db one12 -n cluster1 -instance one12_1
PRKO-2136 : 'srvctl start/stop/enable/disable/modify/status/setenv/getenv/unsetenv instance' commands are not supported with RAC One Node databases

This operation is not supported with RAC One Node.


Start database on cluster2 node.
$ srvctl start database -d one12 -n cluster2

$ srvctl status database -d one12
Instance one12_2 is running on node cluster2
Online relocation: INACTIVE
col instance_name for a20
col host_name for a20
SQL> select host_name, instance_name from v$instance;

HOST_NAME            INSTANCE_NAME
-------------------- --------------------
cluster2.localdomain one12_2

Instance one12_2 is started on cluster2 host.

Relocate database to cluster1 node.
$ srvctl relocate database -d one12 -n cluster1 -w 5 -v
Configuration updated to two instances
Instance one12_1 started
Services relocated
Waiting for up to 5 minutes for instance one12_2 to stop ...
Instance one12_2 stopped
Configuration updated to one instance

Check status.
$ srvctl status database -d one12
Instance one12_1 is running on node cluster1
Online relocation: INACTIVE
col instance_name for a20
col host_name for a20
SQL> select host_name, instance_name from v$instance;

HOST_NAME            INSTANCE_NAME
-------------------- --------------------
cluster1.localdomain one12_1

I have "one12_1" instance running on "cluster1" node again.


From documentation:
Using the Single Client Access Name (SCAN) to connect to the database, clients can locate the service independently of the node on which it is running.

As clients should connect to database using service name (Oracle recommendation) instance placement shouldn't be important.



Read More...

Convert 12cR1 non-RAC database to RAC database using rconfig

Monday, May 19, 2014 0 comments
I have recently configured Active/Passive Failover cluster using Grid Infrastructure 12cR1 and wrote document on that subject. Now I plan to use this environment and convert "test12" database from Single-Instance to RAC database.

There are several ways to convert non-RAC database to RAC database:
- using Database Configuration Assistant (DBCA)
- Oracle Enterprise Manager
- rconfig command line utility
- manually

In this post I will describe how to perform conversion using rconfig utility.


Before you convert single-instance database to RAC ensure that your environment meets prerequisites like:
- Oracle Clusterware is up and running
- shared storage (ASM,CFS)
- user equivalence
- Oracle 12cR1 software installed

I will skip preparation part in this post. You can find steps on how to prepare nodes for Oracle RAC in the first part of the document http://tinyurl.com/p5f52zs.


Rconfig uses customized xml template to convert single-instance database to RAC.

1. As orcl12 user (db owner) navigate to directory "$ORACLE_HOME/assistants/rconfig/sampleXMLs" where you can find two templates ConvertToRAC_AdminManaged.xml and ConvertToRAC_PolicyManaged.xml.

For this example I will edit ConvertToRAC_AdminManaged.xml and add custom entries.

$ cd $ORACLE_HOME/assistants/rconfig/sampleXMLs
$ cp ConvertToRAC_AdminManaged.xml ConvToRac.xml
$ vi ConvToRac.xml

My xml template: ConvToRac.xml

1. Convert verify - when YES rconfig performs checks to ensure that all prerequisites are met for conversion to Oracle RAC.
2. SourceDBHome - Oracle Home of single-instance database.
3. TargetDBHome - Oracle Home where rac database should be configured.
4. SourceDBInfo SID - SID of single-instance database.
5. User, Password, Role - credentials for user with sysdba role are required.
6. Node name - specify nodes for RAC instances.
7. InstancePrefix - enter instance prefix tag or leave this parameter empty.
8. SharedStorage type - type of shared storage.
9. TargetDatabaseArea - database area location for RAC database.
10. TargetFlashRecoveryArea - fast recovery area location for RAC database.


Backup database in case you decide to perform conversion on production database.

Run rconfig to perform conversion based on template:

$ rconfig ConvToRac.xml

<?xml version="1.0" ?>
<RConfig version="1.1" >
<ConvertToRAC>
    <Convert>
      <Response>
        <Result code="1" >
          Got Exception
        </Result>
       <ErrorDetails>
             Cannot determine if cdb or not
Operation Failed. Refer logs at
 /u01/app/orcl12/cfgtoollogs/rconfig/rconfig_05_19_14_13_09_25.log 
for more details.

       </ErrorDetails>
      </Response>
    </Convert>
  </ConvertToRAC></RConfig>


Operation failed with error - "Cannot determine if cdb or not" because my single-instance database was down.


Startup database and run rconfig again:
(you can track progress in $ORACLE_BASE/cfgtoollogs/rconfig/rconfig*.log)

$ rconfig ConvToRac.xml

Converting Database "test12" to Cluster Database. Target Oracle Home: /u01/app/orcl12/product/12.1.0/dbhome_1. Database Role: PRIMARY.
Setting Data Files and Control Files
Adding Trace files
Adding Database Instances
Adding Redo Logs
Enabling threads for all Database Instances
Setting TEMP tablespace
Adding UNDO tablespaces
Setting Fast Recovery Area
Updating Oratab
Creating Password file(s)
Configuring related CRS resources
Starting Cluster Database
<?xml version="1.0" ?>
<RConfig version="1.1" >
<ConvertToRAC>
    <Convert>
      <Response>
        <Result code="0" >
          Operation Succeeded
        </Result>
      </Response>
      <ReturnValue type="object">
<Oracle_Home>
         /u01/app/orcl12/product/12.1.0/dbhome_1
       </Oracle_Home>
       <Database type="ADMIN_MANAGED"  >
         <InstanceList>
           <Instance SID="test121" Node="cluster1"  >
           </Instance>
           <Instance SID="test122" Node="cluster2"  >
           </Instance>
         </InstanceList>
       </Database>     </ReturnValue>
    </Convert>
  </ConvertToRAC></RConfig>


Operation succeeded!

Status:
$ srvctl status database -d test12
Instance test121 is running on node cluster1
Instance test122 is running on node cluster2

$ srvctl config database -d test12
Database unique name: test12
Database name: test12
Oracle home: /u01/app/orcl12/product/12.1.0/dbhome_1
Oracle user: orcl12
Spfile: +DATA/spfiletest12.ora
Password file: +DATA/orapwtest12
Domain:
Start options: open
Stop options: immediate
Database role: PRIMARY
Management policy: AUTOMATIC
Server pools: test12
Database instances: test121,test122
Disk Groups: DATA,FRA
Mount point paths:
Services:
Type: RAC
Start concurrency:
Stop concurrency:
Database is administrator managed

$ sqlplus system@test12

SQL*Plus: Release 12.1.0.1.0 Production on Mon May 19 13:31:34 2014

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

Enter password:
Last Successful login time: Mon May 19 2014 13:30:41 +02:00

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP
and Advanced Analytics options

SQL> select instance_number, instance_name from v$instance;

INSTANCE_NUMBER INSTANCE_NAME
--------------- ----------------
              1 test121


NOTE
If conversion fails then use following steps to recover and reattempt conversion:
- review conversion log and fix problems that caused conversion to fail
- delete database using DBCA
- restore source database
- reattempt the conversion




REFERENCE:
http://docs.oracle.com/cd/E18673_01/doc/install.112/e17214/cvrt2rac.htm#BABGGEGJ

Read More...

Combining schedules with DBMS_SCHEDULER

Wednesday, April 16, 2014 2 comments
Suppose I want to create job which will execute every 30 minutes in “working” hours, and at the rest of the day every hour.

For example:
8:00
8:30
9:00
9:30
..
..
18:00
19:00
20:00



With DBMS_JOB I would write something like this in repeat interval:

(CASE WHEN TO_NUMBER(TO_CHAR(SYSDATE,'HH24')) IN (8,9,10,11,12,13,14,15,16,17) 
THEN TRUNC(SYSDATE,'HH')+30/1440 
ELSE TRUNC(sysdate,'HH')+1/24 END)

In DBMS_SCHEDULER I will create two schedules (“WORK”,”NIGHT”) and main schedule ("MAIN_DAY") which will combine those two for job schedule.
BEGIN

DBMS_SCHEDULER.create_schedule ('WORK',repeat_interval => 'FREQ=DAILY;BYHOUR=8,9,10,11,12,13,14,15,16,17;BYMINUTE=0,30');

DBMS_SCHEDULER.create_schedule ('NIGHT',repeat_interval => 'FREQ=DAILY;BYHOUR=0,1,2,3,4,5,6,7,18,19,20,21,22,23;' );

DBMS_SCHEDULER.create_schedule ('MAIN_DAY', repeat_interval =>'WORK, NIGHT');

END;

/


To test schedule I will create simple table and job which will insert sysdate into table.

create table admin.test_jobs_exec (run_date date);

BEGIN
    DBMS_SCHEDULER.CREATE_JOB (
            job_name => '"ADMIN"."J_TEST_JOBS_EXEC"',
            schedule_name => '"ADMIN"."MAIN_DAY"',
            job_type => 'PLSQL_BLOCK',
            job_action => 'begin
                           insert into test_jobs_exec values (sysdate);
                           commit;
                           end;',
            number_of_arguments => 0,
            enabled => FALSE,
            auto_drop => FALSE
);
    
    DBMS_SCHEDULER.enable(
             name => '"ADMIN"."J_TEST_JOBS_EXEC"');
END;
/


Let’s query test_jobs_exec table to see if scheduler used wanted job schedule.

select run_date from test_jobs_exec
order by run_date;


RUN_DATE          
-------------------
...
- 15.04.2014 16:00:32 
- 15.04.2014 16:30:32 
- 15.04.2014 17:00:32 
- 15.04.2014 17:30:32 
15.04.2014 18:00:32 
15.04.2014 19:00:33 
15.04.2014 20:00:32 
15.04.2014 21:00:32 
15.04.2014 22:00:32 
15.04.2014 23:00:32 
16.04.2014 00:00:32 
16.04.2014 01:00:32 
16.04.2014 02:00:32 
16.04.2014 03:00:32 
16.04.2014 04:00:33 
16.04.2014 05:00:32 
16.04.2014 06:00:32 
16.04.2014 07:00:32 
- 16.04.2014 08:00:32 
- 16.04.2014 08:30:32 
- 16.04.2014 09:00:32 
- 16.04.2014 09:30:32 
...



If you are still using old DBMS_JOB package replace it with DBMS_SCHEDULER.
You will get better logging, simple scheduling syntax, stored/reusable schedules and many more nice features.

Read More...

How to configure Active/Passive Failover cluster using Grid Infrastructure 12cR1

Wednesday, April 2, 2014 0 comments
I'm big fan of tools like VirtualBox or VmWare because they offer you opportunity to try various "enterprise" configurations, installations and scenarios on your notebook. Maybe the biggest benefit for my learning lies in these tools.

In this post I will share with you document about creation of Active/Passive failover cluster using Oracle Grid Infrastructure 12cR1 on OEL 6.4. There you can find screenshots taken during my playing in virtual environment packed in PDF document.

You will probably find many configuration/installation mistakes. This is why I set this document to be v1.0 version – I plan to change many things based on your suggestions.
I’m not native English speaker so there are probably lots grammatical or spelling errors also.

But still, as I already wrote document for myself, I have decided to share it with Oracle community for educational purposes.


If you are interested in this subject, and if you take some time to read document, please send me your suggestions and objections.

PDF document: Active Passive Failover Cluster 12c Configuration for Oracle Databases


Thank you,
Marko

Read More...

RMAN Catalog backuppiece located on Tape

Thursday, March 6, 2014 0 comments
I've recorded backups on tape to RMAN repository several times already, but every next time I needed to do that I was searching through notes to find proper procedure.

This time I will note procedure in form of the blog post.

Note!
Test is performed on Oracle version 11.1.0.7.


These were my unsuccessful attempts:

RMAN> run
2> {
3> allocate channel c1 device type 'sbt_tape';
4> send 'NSR_ENV=(NSR_SERVER=backup_server,NSR_CLIENT=oracle_client,NSR_DATA_VOLUME=OrclPool)';
5> catalog  backuppiece 'ARCH_ORCL_rep2dod5_s128878_p1';
6> }

allocated channel: c1
channel c1: SID=321 device type=SBT_TAPE
channel c1: NMDA Oracle v1.1.0

sent command to channel: c1

ORA-19870: error while restoring backup piece /u01/app/orcl11/product/11.1.0/db_1/dbs/ARCH_ORCL_rep2dod5_s128878_p1
ORA-19505: failed to identify file "/u01/app/orcl11/product/11.1.0/db_1/dbs/ARCH_ORCL_rep2dod5_s128878_p1"
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3

released channel: c1
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of catalog command at 03/05/2014 15:42:23
RMAN-06209: List of failed objects
RMAN-06211: ==========================
RMAN-06212:   Object Type   Filename/Handle
RMAN-06213: --------------- ---------------------------------------------------
RMAN-06214: Backup Piece    /u01/app/orcl11/product/11.1.0/db_1/dbs/ARCH_ORCL_rep2dod5_s128878_p1

and

RMAN> run
2> {
3> allocate channel c1 device type 'sbt_tape';
4> send 'NSR_ENV=(NSR_SERVER=backup_server,NSR_CLIENT=oracle_client,NSR_DATA_VOLUME=OrclPool)';
5> catalog device type 'sbt_tape' backuppiece 'ARCH_ORCL_rep2dod5_s128878_p1';
6> }

allocated channel: c1
channel c1: SID=321 device type=SBT_TAPE
channel c1: NMDA Oracle v1.1.0

sent command to channel: c1

released channel: c1
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of catalog command at 03/05/2014 15:36:57
RMAN-06470: DEVICE TYPE is supported only when automatic channels are used



How to catalog backuppiece on tape...


Add SBT_TAPE configuration:
RMAN> configure default device type to 'SBT_TAPE';
RMAN> configure channel device type 'SBT_TAPE' send '[MML PARAMETERS]';

Catalog backuppiece:
RMAN> catalog device type 'SBT_TAPE' backuppiece '[BACKUP NAME]';

To clear configuration:
RMAN> configure channel device type ‘SBT_TAPE’ clear;


My example:

Set configuration:
RMAN> configure default device type to 'SBT_TAPE';
RMAN> configure channel device type 'SBT_TAPE'
2> send 'NSR_ENV=(NSR_SERVER=backup_server,NSR_CLIENT=oracle_client,NSR_DATA_VOLUME=OrclPool)';
new RMAN configuration parameters:
CONFIGURE CHANNEL DEVICE TYPE 'SBT_TAPE' SEND  'NSR_ENV=(NSR_SERVER=backup_server,NSR_CLIENT=oracle_client,NSR_DATA_VOLUME=OrclPool)';
new RMAN configuration parameters are successfully stored

Catalog backuppiece:
RMAN> catalog device type 'SBT_TAPE' backuppiece 'ARCH_ORCL_rep2dod5_s128878_p1';

allocated channel: ORA_SBT_TAPE_1
channel ORA_SBT_TAPE_1: SID=321 device type=SBT_TAPE
channel ORA_SBT_TAPE_1: NMDA Oracle v1.1.0
cataloged backup piece
backup piece handle=ARCH_ORCL_rep2dod5_s128878_p1 RECID=127894 STAMP=841419878

Clear configuration:
RMAN> CONFIGURE CHANNEL DEVICE TYPE 'SBT_TAPE' CLEAR;

old RMAN configuration parameters:
CONFIGURE CHANNEL DEVICE TYPE 'SBT_TAPE' SEND  'NSR_ENV=(NSR_SERVER=backup_server,NSR_CLIENT=oracle_client,NSR_DATA_VOLUME=OrclPool)';
old RMAN configuration parameters are successfully deleted



Read More...

How to use index to get NULL values

Tuesday, February 18, 2014 1 comments
I have learned new trick today which I want to share and keep as reminder to myself. I am sure that most Oracle DBA’s or Developers are familiar with this - so please skip this post if you are one of them :)


My test table will have 1000000 rows with 1000 NULL values for “OBJECT_NAME” column.

Tests are performed on 11gR1 version.

Table/column info:
select c.table_name, c.column_name, c.data_type, c.num_nulls, 
       t.num_rows table_num_rows
from dba_tab_columns c, dba_tables t
where 1=1
and c.table_name = t.table_name
and c.table_name = 'OBJECTS'
and c.column_name = 'OBJECT_NAME';



TABLE_NAME    COLUMN_NAME     DATA_TYPE     NUM_NULLS TABLE_NUM_ROWS
------------- --------------- ------------ ---------- --------------
OBJECTS       OBJECT_NAME     VARCHAR2           1000        1000000 


Goal:
Count how many OBJECT_NAMEs are NULL.


Index object_name column using classic B-tree index.

create index idx_objname on objects(object_name);


select count(*)
from objects
where object_name is null;

  COUNT(*)
----------
      1000

------------------------------------------------------------------------------
| Id  | Operation          | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |         |       |       |  4252 (100)|          |
|   1 |  SORT AGGREGATE    |         |     1 |    25 |            |          |
|*  2 |   TABLE ACCESS FULL| OBJECTS |  1000 | 25000 |  4252   (1)| 00:00:52 |
------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("OBJECT_NAME" IS NULL)
   
   
drop index idx_objname;

FULL SCAN is used because NULL values are not indexed for single column indexes.


For a workaround you could create function based index to help yourself. This was (till now) my preferred option.

create index idx_objname_f on objects(nvl(object_name,'XX'));

select count(*)
from objects
where nvl(object_name,'XX')='XX';

  COUNT(*)
----------
      1000

-----------------------------------------------------------------------------------
| Id  | Operation         | Name          | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |               |       |       |     3 (100)|          |
|   1 |  SORT AGGREGATE   |               |     1 |    17 |            |          |
|*  2 |   INDEX RANGE SCAN| IDX_OBJNAME_F | 10000 |   166K|     3   (0)| 00:00:01 |
-----------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("OBJECTS"."SYS_NC00017$"='XX')

Now INDEX is used but as you can see I had to modify initial query using function to get number of NULLs.

Besides that, this index is unusable if I want to get other values then NULL.

select count(*)
from objects
where object_name='TEST';

  COUNT(*)
----------
        16


------------------------------------------------------------------------------
| Id  | Operation          | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |         |       |       |  4253 (100)|          |
|   1 |  SORT AGGREGATE    |         |     1 |    25 |            |          |
|*  2 |   TABLE ACCESS FULL| OBJECTS |    28 |   700 |  4253   (1)| 00:00:52 |
------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("OBJECT_NAME"='TEST')
   
      
drop index idx_objname_f;


The trick that I wanted to show.
In composite index NULL values are stored for leading column when second column is not NULL. I will use dummy value for second column.

create index idx_objname_d on objects(object_name,'X');

select count(*)
from objects
where object_name is null;

  COUNT(*)
----------
      1000

-----------------------------------------------------------------------------------
| Id  | Operation         | Name          | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |               |       |       |     8 (100)|          |
|   1 |  SORT AGGREGATE   |               |     1 |    25 |            |          |
|*  2 |   INDEX RANGE SCAN| IDX_OBJNAME_D |  1000 | 25000 |     8   (0)| 00:00:01 |
-----------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("OBJECT_NAME" IS NULL)


Index is used to count NULL values.


You can also use this index to get other values.


select count(*)
from objects
where object_name='TEST';

  COUNT(*)
----------
        16

-----------------------------------------------------------------------------------
| Id  | Operation         | Name          | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |               |       |       |     3 (100)|          |
|   1 |  SORT AGGREGATE   |               |     1 |    25 |            |          |
|*  2 |   INDEX RANGE SCAN| IDX_OBJNAME_D |    28 |   700 |     3   (0)| 00:00:01 |
-----------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("OBJECT_NAME"='TEST')


drop index idx_objname_d;

This trick was so nice discovery for me that deserved blog post.


It is worth to mention that bitmap index also stores NULL values.

create bitmap index idx_objname_d on objects(object_name);

select count(*)
from objects
where object_name is null;

  COUNT(*)
----------
      1000
   
---------------------------------------------------------------------------------------------
| Id  | Operation                   | Name          | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |               |       |       |     3 (100)|          |
|   1 |  SORT AGGREGATE             |               |     1 |    25 |            |          |
|   2 |   BITMAP CONVERSION COUNT   |               |  1000 | 25000 |     3   (0)| 00:00:01 |
|*  3 |    BITMAP INDEX SINGLE VALUE| IDX_OBJNAME_D |       |       |            |          |
---------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - access("OBJECT_NAME" IS NULL)



Clean up!

drop table objects;

Read More...

Wrong Result Bug using group-by placement optimization

Wednesday, February 5, 2014 0 comments
Last week I’ve mentioned on Twitter that we ran into wrong result bug. We found workaround quickly but I’ve decided to spend some time to reproduce error and write blog post to warn you about this optimizer behavior.
Special thanks to my colleague who spotted odd results which led us to this finding.

My test (virtual) environment is:
OS: Oracle Enterprise Linux 5.8
DB: Oracle EE 11.1.0.7.12

In test I will use three tables:
CONT
Name    Null Type          
------- ---- ------------- 
CUST_ID      NUMBER(38)    
CODE         VARCHAR2(100) 

CUST
Name    Null     Type       
------- -------- ---------- 
CUST_ID NOT NULL NUMBER(38) 

DRAG
Name    Null Type      
------- ---- --------- 
DRAG_ID      NUMBER(6) 


To gather fresh statistics for the tables:
begin
  dbms_stats.gather_table_stats(ownname=>user,tabname=>'CONT',estimate_percent=>100, cascade=>TRUE);
  dbms_stats.gather_table_stats(ownname=>user,tabname=>'CUST',estimate_percent=>100, cascade=>TRUE);
  dbms_stats.gather_table_stats(ownname=>user,tabname=>'DRAG',estimate_percent=>100, cascade=>TRUE);
end;
/

More details about tables:
select table_name, num_rows, blocks, partitioned, last_analyzed
from dba_tables
where table_name in ('CONT','CUST','DRAG');


TABLE_NAME     NUM_ROWS     BLOCKS PARTITIONED LAST_ANALYZED     
------------ ---------- ---------- ----------- -------------------
CONT            1181949       2892 NO          04.02.2014 14:49:24 
DRAG                314          5 NO          04.02.2014 14:49:25 
CUST             576233        902 NO          04.02.2014 14:49:25 

Information about indexes:
select index_name, table_name, uniqueness, distinct_keys, clustering_factor
from dba_indexes
where table_name in ('CONT','CUST','DRAG');


INDEX_NAME     TABLE_NAME   UNIQUENESS DISTINCT_KEYS CLUSTERING_FACTOR
-------------- ------------ ---------- ------------- -----------------
I_CUST_ID      CONT         NONUNIQUE         468738            753983 
PK_CUST_ID     CUST         UNIQUE            576233               878 

We have three small and simple tables with just two indexes. CUST table has primary key on “cust_id” column.


After this little introduction it is time for some tests.

I will flush buffer cache and shared pool before every query execution.
SQL> alter system flush shared_pool;
System altered.

SQL> alter system flush buffer_cache;
System altered.

First query execution and execution plan:
select /*+ gather_plan_statistics */ 
       count(co.code) as cnt
from drag t, 
     cust cus, 
     cont co
where 1=1
and t.drag_id = cus.cust_id
and cus.cust_id = co.cust_id(+) 
group by t.drag_id;

            CNT
---------------
              2
              2
              2
              2
              1
              2
              2
              2
              2
...
303 rows
SQL> select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------
SQL_ID  gpnrgy2vawafq, child number 0
-------------------------------------
select /*+ gather_plan_statistics */        count(co.code) as cnt from
drag t,      cust cus,      cont co where 1=1 and t.drag_id =
cus.cust_id and cus.cust_id = co.cust_id(+) group by t.drag_id

Plan hash value: 3989628059

---------------------------------------------------------------------------------------------------------------------------
| Id  | Operation            | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  |  OMem |  1Mem | Used-Mem |
---------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |      |      1 |        |    303 |00:00:00.62 |    3734 |   3724 |       |       |          |
|   1 |  HASH GROUP BY       |      |      1 |    303 |    303 |00:00:00.62 |    3734 |   3724 |  1096K|  1096K| 1264K (0)|
|*  2 |   HASH JOIN OUTER    |      |      1 |    792 |   1084 |00:00:00.16 |    3734 |   3724 |  1206K|  1206K| 1244K (0)|
|*  3 |    HASH JOIN         |      |      1 |    314 |    314 |00:00:00.04 |     890 |    885 |  1452K|  1452K| 1470K (0)|
|   4 |     TABLE ACCESS FULL| DRAG |      1 |    314 |    314 |00:00:00.01 |       7 |      6 |       |       |          |
|   5 |     TABLE ACCESS FULL| CUST |      1 |    576K|    576K|00:00:00.02 |     883 |    879 |       |       |          |
|   6 |    TABLE ACCESS FULL | CONT |      1 |   1181K|   1181K|00:00:00.01 |    2844 |   2839 |       |       |          |
---------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("CUS"."CUST_ID"="CO"."CUST_ID")
   3 - access("T"."DRAG_ID"="CUS"."CUST_ID")

Check result of the query - this is correct query result.

Now to simulate what we experienced in production.
SQL> alter system flush shared_pool;
System altered.
SQL> alter system flush buffer_cache;
System altered.   

With hint I want to force PK_CUST_ID index usage because this was preferred plan in production.
select /*+ gather_plan_statistics index(cus PK_CUST_ID) */ 
       count(co.code) as cnt
from drag t, 
     cust cus, 
     cont co
where 1=1
and t.drag_id = cus.cust_id
and cus.cust_id = co.cust_id(+) 
group by t.drag_id;

            CNT
---------------
              0
              0
              0
              0
              0
              0
              0
              0
              0
              0
... 303 rows
SQL> select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  9vf9uf7mhdmdz, child number 0
-------------------------------------
select /*+ gather_plan_statistics index(cus PK_CUST_ID) */
count(co.code) as cnt from drag t,      cust cus,      cont co where
1=1 and t.drag_id = cus.cust_id and cus.cust_id = co.cust_id(+) group
by t.drag_id

Plan hash value: 3263881209

-----------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation              | Name       | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  |  OMem |  1Mem | Used-Mem |
-----------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |            |      1 |        |    303 |00:00:00.70 |    3459 |   3094 |       |       |          |
|   1 |  HASH GROUP BY         |            |      1 |    303 |    303 |00:00:00.70 |    3459 |   3094 |   934K|   934K| 1267K (0)|
|*  2 |   HASH JOIN OUTER      |            |      1 |    764 |   1046 |00:00:00.22 |    3459 |   3094 |  1134K|  1134K| 1198K (0)|
|   3 |    NESTED LOOPS        |            |      1 |    303 |    303 |00:00:02.02 |     615 |    255 |       |       |          |
|   4 |     VIEW               | VW_GBC_9   |      1 |    303 |    303 |00:00:00.01 |       7 |      6 |       |       |          |
|   5 |      HASH GROUP BY     |            |      1 |    303 |    303 |00:00:00.01 |       7 |      6 |  1012K|  1012K| 1249K (0)|
|   6 |       TABLE ACCESS FULL| DRAG       |      1 |    314 |    314 |00:00:00.01 |       7 |      6 |       |       |          |
|*  7 |     INDEX UNIQUE SCAN  | PK_CUST_ID |    303 |      1 |    303 |00:00:00.22 |     608 |    249 |       |       |          |
|   8 |    TABLE ACCESS FULL   | CONT       |      1 |   1181K|   1181K|00:00:00.01 |    2844 |   2839 |       |       |          |
-----------------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("CUS"."CUST_ID"="CO"."CUST_ID")
   7 - access("ITEM_1"="CUS"."CUST_ID")

Check result of the query!
Count is displaying all 0 values because it received only NULLs to count.
Other functions like max and min are also affected by this error.

Check steps 4,5 and 6 in execution plan.

Instead of quick full scan on DRAG table Oracle transformed query and created inline view using smart group-by optimization.

In 10053 trace I could easily find what Oracle was doing.
SELECT
  /*+ INDEX ("CUS" "PK_CUST_ID") */
  SUM("VW_GBC_9"."ITEM_2") "CNT"
FROM
  (SELECT "T"."DRAG_ID" "ITEM_1",
    COUNT("CO"."CODE") "ITEM_2",
    "T"."DRAG_ID" "ITEM_3"
  FROM "ADMIN"."DRAG" "T"
  WHERE 1=1
  GROUP BY "T"."DRAG_ID",
    "T"."DRAG_ID"
  ) "VW_GBC_9",
  "ADMIN"."CUST" "CUS",
  "ADMIN"."CONT" "CO"
WHERE "VW_GBC_9"."ITEM_1"="CUS"."CUST_ID"
AND "CUS"."CUST_ID"      ="CO"."CUST_ID"(+)
GROUP BY "VW_GBC_9"."ITEM_3";


Quick workaround to fix this bug:
- Set "_optimizer_group_by_placement"=FALSE.

You could check in 10053 trace value of this parameter.
In my case: _optimizer_group_by_placement = true

SQL> alter session set "_optimizer_group_by_placement"=FALSE;

Session altered.

select /*+ gather_plan_statistics index(cus PK_CUST_ID) */ 
       count(co.code) as cnt
from drag t, 
     cust cus, 
     cont co
where 1=1
and t.drag_id = cus.cust_id
and cus.cust_id = co.cust_id(+) 
group by t.drag_id;

       CNT
----------
         2
         2
         2
         2
         1
         2
         2
         2
         2
...
303 rows
SQL> select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------
SQL_ID  a91bzhvupzquh, child number 0
-------------------------------------
select /*+ gather_plan_statistics index(cus PK_CUST_ID)*/
count(co.code) as cnt from drag t,      cust cus,      cont co where
1=1 and t.drag_id = cus.cust_id and cus.cust_id = co.cust_id(+) group
by t.drag_id

Plan hash value: 2460166079

------------------------------------------------------------------------------------------------------------------------
| Id  | Operation            | Name       | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |            |      1 |        |    303 |00:00:00.16 |    3481 |       |       |          |
|   1 |  HASH GROUP BY       |            |      1 |    303 |    303 |00:00:00.16 |    3481 |  1096K|  1096K| 1232K (0)|
|*  2 |   HASH JOIN OUTER    |            |      1 |    792 |   1084 |00:00:00.01 |    3481 |  1206K|  1206K| 1529K (0)|
|   3 |    NESTED LOOPS      |            |      1 |    314 |    314 |00:00:00.01 |     637 |       |       |          |
|   4 |     TABLE ACCESS FULL| DRAG       |      1 |    314 |    314 |00:00:00.01 |       7 |       |       |          |
|*  5 |     INDEX UNIQUE SCAN| PK_CUST_ID |    314 |      1 |    314 |00:00:00.01 |     630 |       |       |          |
|   6 |    TABLE ACCESS FULL | CONT       |      1 |   1181K|   1181K|00:00:00.01 |    2844 |       |       |          |
------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("CUS"."CUST_ID"="CO"."CUST_ID")
   5 - access("T"."DRAG_ID"="CUS"."CUST_ID")


27 rows selected.  



Oracle Support note associated with "_optimizer_group_by_placement" parameter.
Note.8945586.8 Ext/Pub Bug 8945586 - Wrong results using GROUP BY placement:
Description
Wrong results can occur when using GROUP BY placement where the aggregate column gets pruned from select list.


I’ve even found that “_optimizer_group_by_placement” parameter was mentioned in "Oracle® Fusion Middleware Oracle WebCenter Analytics Installation and Upgrade Guide".

Oracle 11g (11.1.0.6 and above) in default or Oracle Real Application Clusters (RAC) configuration

When running Oracle 11g versions prior to 11.1.0.7.0 the Oracle system parameter _optimizer_group_by_placement must be set to false. This can either be set in the init.ora file of the respective database instances or by by issuing an ALTER SYSTEM command as follows:

SQLPLUS /nolog
CONNECT / AS SYSDBA
ALTER SYSTEM SET "_optimizer_group_by_placement"=false


If you are running affected Oracle version - consider changing this parameter ;-)

Read More...

Oracle Scheduler External Jobs and Credentials

Friday, October 25, 2013 7 comments
A few months ago I wrote a blog post - Kill stuck Oracle process from OS using DBMS_SCHEDULER? We had some problems with stuck Oracle processes and I have created external job to kill stuck processes for a quick workaround.

At the end of that post I wrote:
When dbms_scheduler is executing external (operating system) jobs it is using lowly privileged user and group for your platform. In my environment (OEL 5.8) this is “nobody” and “nobody”. As you must kill OS process you must specify more privileged user. There is “externaljob.ora” configuration file in “$ORACLE_HOME/rdbms/admin”. Edit this file as root and specify privileged user and group.

During my preparation for “Oracle Database 11g: New Features for Administrators” I’ve learned that this is not the best way to grant privileges to external jobs.

In 11g, to improve security, Oracle strongly recommends to assign a credential to a external job for this task. Default credentials are included only for backward compatibility and may be deprecated in future releases.


This will be very simple example to demonstrate how to use credentials.


Let’s create simple script:
$ cat test_script.sh
#!/bin/bash

/bin/echo "TEST SCRIPT!" > /home/oracle/test_script.out

Grant execute privileges to OS user:
$ chmod u+x test_script.sh

Create test user and grant necessary privileges. To execute external jobs you will need "CREATE JOB" and "CREATE EXTERNAL JOB" privileges.
SQL> create user testusr identified by testusr;

User created.

SQL> grant create session to testusr;

Grant succeeded.

SQL> grant create job to testusr;

Grant succeeded.

SQL> grant create external job to testusr;

Grant succeeded.

Create simple job.
SQL> conn testusr/testusr
Connected.
SQL>
begin
   dbms_scheduler.create_job(
   job_name => 'test_script_job',
   job_type => 'EXECUTABLE',
   job_action => '/home/oracle/test_script.sh',
   start_date => SYSDATE,
   enabled => FALSE,
   repeat_interval => NULL);
   end;
 10  /

PL/SQL procedure successfully completed.

Try to run job without assigning credentials.
SQL> begin
  2  dbms_scheduler.run_job(job_name => 'test_script_job', use_current_session=> TRUE);
  3  end;
  4  /
begin
*
ERROR at line 1:
ORA-27369: job of type EXECUTABLE failed with exit code: 274662
ORA-06512: at "SYS.DBMS_ISCHED", line 185
ORA-06512: at "SYS.DBMS_SCHEDULER", line 486
ORA-06512: at line 2

Now to create credential using CREATE_CREDENTIAL procedure in the DBMS_SCHEDULER package.
OS user specified in credential must have necessary privileges to run script.
SQL> begin
  2  dbms_scheduler.create_credential(
  3  credential_name => 'ORACLE_CRED',
  4  username => 'oracle',
  5  password => ‘os_password’);
  6  end;
  7  /

PL/SQL procedure successfully completed.


BE AWARE!

It is noted in documentation, passwords are stored obfuscated, and are not displayed in the *_SCHEDULER_CREDENTIALS views. But as SYS user you could extract password with DBMS_ISCHED.GET_CREDENTIAL_PASSWORD function.
SQL>
SELECT u.name CREDENTIAL_OWNER, O.NAME CREDENTIAL_NAME, C.USERNAME,
  DBMS_ISCHED.GET_CREDENTIAL_PASSWORD(O.NAME, u.name) pwd
FROM SYS.SCHEDULER$_CREDENTIAL C, SYS.OBJ$ O, SYS.USER$ U
WHERE U.USER# = O.OWNER#
  5    AND C.OBJ#  = O.OBJ# ;

CREDENTIAL_OWNER     CREDENTIAL_NAME                USERNAME             PWD
-------------------- ------------------------------ -------------------- ----------
TESTUSR              ORACLE_CRED                    oracle               os_password

Thanks Martin Berger for this information: http://berxblog.blogspot.com/2012/02/restore-dbmsschedulercreatecredential.html

You already know that SYS user is very powerful and should be well protected - this is just another reason.


As I have created credential lets assign it to my external job.
SQL> begin
  2  dbms_scheduler.set_attribute('test_script_job','credential_name','ORACLE_CRED');
  3  end;
  4  /

PL/SQL procedure successfully completed.

Everything should work fine now.
SQL> begin
  2  dbms_scheduler.run_job(job_name => 'test_script_job', use_current_session=> TRUE);
  3  end;
  4  /

PL/SQL procedure successfully completed.

Quick check.
SQL> !cat /home/oracle/test_script.out
TEST SCRIPT!

Cleanup:
SQL> drop user testusr cascade;

User dropped.


$ rm /home/oracle/test_script.sh /home/oracle/test_script.out


REFERENCES:
http://docs.oracle.com/cd/B28359_01/server.111/b28310/schedover004.htm#BGBHJCHH

Read More...

RMAN Duplicate from Active Database - ASM to non-ASM

Monday, October 14, 2013 10 comments
In this post I want to explain how to create duplicate database from active 11gR2 database using RMAN. There are many blog posts covering that subject but most of them are covering non-ASM to non-ASM, ASM to ASM or non-ASM to ASM duplications.
I want to cover ASM to non-ASM duplication from active database as this subject is not widely covered.


Active duplication from ASM to non-ASM (or other duplications) can be little tricky. You could easily lose quite of time on troubleshooting if you don't comply all prerequisites during preparation. I will show you examples of some errors you could experience.

I haven't used active database duplication in production and practiced much with this feature. But still was confident that I will perform task successfully from the first time. After few hours of troubleshooting and few failed attempts I ended up on detailed reading Oracle Documentation - what I should do at the first place.
Learn on my mistakes :)


So let’s start with little demo.


- Create password file for auxiliary instance

The easiest method would be to copy password file from the source database.
$ scp orapwora11gr2 ora11gr2@192.168.56.107:/u01/app/ora11gr2/product/11.2.0/dbhome_1/dbs
ora11gr2@192.168.56.107's password:
orapwora11gr2                                                                                             100% 1536     1.5KB/s   00:00


- Network setup

For auxiliary database create static instance registration to listener.
Add to listener.ora:
SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = ora11gr2)
      (ORACLE_HOME = /u01/app/ora11gr2/product/11.2.0/dbhome_1)
      (SID_NAME = ora11gr2)
    )
  )

For both databases tnsnames.ora would be:
duplicate =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.56.107)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = ora11gr2)
      (UR=A)
    )
  )

ora11gr2 =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.56.109)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = ora11gr2)
      (UR=A)
    )
  )

Start both listeners.


- Create needed directories on target host
$ mkdir -p /u01/app/ora11gr2/admin/ora11gr2/adump
$ mkdir -p /u01/oradata/fra
$ mkdir -p /u01/oradata/ora11gr2/datafiles
$ mkdir -p /u01/oradata/ora11gr2/redologs


- Create simple parameter file and test connectivity
$ cd $ORACLE_HOME/dbs
$ cat initora11gr2.ora
db_name=ora11gr2

Start NOMOUNT auxiliary database using specified parameter file
$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Sat Oct 12 00:26:18 2013

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

Connected to an idle instance.

SQL> startup nomount pfile=$ORACLE_HOME/dbs/initora11gr2.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
SQL>

Test connectivity.
Target:
$ sqlplus sys/oracle@ora11gr2 as sysdba

Source:
$ sqlplus sys/oracle@duplicate as sysdba


I am executing this commands on target host but you could also execute them on source.

Run this command to establish connection between databases.
$ rman target sys/oracle@ora11gr2 auxiliary sys/oracle@duplicate

Recovery Manager: Release 11.2.0.1.0 - Production on Sat Oct 12 00:39:00 2013

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

connected to target database: ORA11GR2 (DBID=138112863)
connected to auxiliary database: ORA11GR2 (not mounted)


- We are ready to test duplicate scenarios


Let's start with this simple script.
run
{
         DUPLICATE TARGET DATABASE TO “ora11gr2”
         FROM ACTIVE DATABASE
         DB_FILE_NAME_CONVERT '+DATA1/ora11gr2/datafile/','/u01/oradata/ora11gr2/datafiles/'
         SPFILE
         SET LOG_FILE_NAME_CONVERT '+DATA1/ora11gr2/onlinelog/','/u01/oradata/ora11gr2/redologs/'
         SET AUDIT_FILE_DEST '/u01/app/ora11gr2/admin/ora11gr2/adump';
}

I get error:
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of Duplicate Db command at 10/12/2013 00:39:29
RMAN-06136: ORACLE error from auxiliary database: ORA-00200: control file could not be created
ORA-00202: control file: '+data1'
ORA-17502: ksfdcre:4 Failed to create file +data1
ORA-15001: diskgroup "DATA1" does not exist or is not mounted
ORA-15077: could not locate ASM instance serving a required diskgroup
ORA-29701: unable to connect to Cluster Synchronization Service
Oracle wants to create control file is on '+DATA1' but I don't have ASM instance running on target host. I will change location for control file adding "SET CONTROL_FILES '/u01/oradata/ora11gr2/datafiles/control01.ctl'" to the script.

As directory structure on target host is completely different I will change location for DIAGNOSTIC_DEST also.
run
{
         DUPLICATE TARGET DATABASE TO "ora11gr2"
         FROM ACTIVE DATABASE
         DB_FILE_NAME_CONVERT '+DATA1/ora11gr2/datafile/','/u01/oradata/ora11gr2/datafiles/'
         SPFILE
         SET LOG_FILE_NAME_CONVERT '+DATA1/ora11gr2/onlinelog/','/u01/oradata/ora11gr2/redologs/'
         SET DIAGNOSTIC_DEST '/u01/app/ora11gr2'
         SET AUDIT_FILE_DEST '/u01/app/ora11gr2/admin/ora11gr2/adump'
         SET CONTROL_FILES '/u01/oradata/ora11gr2/datafiles/control01.ctl';
}

This time I get:
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of Duplicate Db command at 10/12/2013 00:50:24
RMAN-05501: aborting duplication of target database
RMAN-05517: tempfile +DATA1/ora11gr2/tempfile/temp.280.821541379 conflicts with file used by target database
With specified DB_FILE_NAME_CONVERT parameter location for temporary file is not changed. My temporary file is not in "+DATA1/ora11gr2/datafile/", but in "+DATA1/ora11gr2/tempfile/".


I will use "SET NEWNAME FOR TEMPFILE" to change location.
run
{
         SET NEWNAME FOR TEMPFILE 1 TO '/u01/oradata/ora11gr2/datafiles/temp01.dbf';
         DUPLICATE TARGET DATABASE TO "ora11gr2"
         FROM ACTIVE DATABASE
         DB_FILE_NAME_CONVERT '+DATA1/ora11gr2/datafile/','/u01/oradata/ora11gr2/datafiles/'
         SPFILE
         SET LOG_FILE_NAME_CONVERT '+DATA1/ora11gr2/onlinelog/','/u01/oradata/ora11gr2/redologs/'
         SET DIAGNOSTIC_DEST '/u01/app/ora11gr2'
         SET AUDIT_FILE_DEST '/u01/app/ora11gr2/admin/ora11gr2/adump'
         SET CONTROL_FILES '/u01/oradata/ora11gr2/datafiles/control01.ctl';
}

Again error:
...
contents of Memory Script:
{
   backup as copy reuse
   archivelog like  "+FRA1/ora11gr2/archivelog/2013_10_12/thread_1_seq_222.1249.828580067" auxiliary format
 "+FRA1"   ;
   catalog clone recovery area;
   switch clone datafile all;
}
executing Memory Script

Starting backup at 12.10.2013 01:07:51
using channel ORA_DISK_1
channel ORA_DISK_1: starting archived log copy
input archived log thread=1 sequence=222 RECID=247 STAMP=828580069
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of Duplicate Db command at 10/12/2013 01:07:52
RMAN-03015: error occurred in stored script Memory Script
RMAN-03009: failure of backup command on ORA_DISK_1 channel at 10/12/2013 01:07:52
ORA-17628: Oracle error 19505 returned by remote Oracle server
Oracle cannot perform recovery because '+FRA' diskgroup is missing on target host.

Now it is obvious that I could save some time if I had checked parameter file on the source host before duplication.

In my case RMAN copies parameter file from source to the target host. As I have different directory structure on the target host I have to update directory locations using SET commands.
run
{
         SET NEWNAME FOR TEMPFILE 1 TO '/u01/oradata/ora11gr2/datafiles/temp01.dbf';
         DUPLICATE TARGET DATABASE TO "ora11gr2"
         FROM ACTIVE DATABASE
         DB_FILE_NAME_CONVERT '+DATA1/ora11gr2/datafile/','/u01/oradata/ora11gr2/datafiles/'
         SPFILE
         SET LOG_FILE_NAME_CONVERT '+DATA1/ora11gr2/onlinelog/','/u01/oradata/ora11gr2/redologs/'
         SET AUDIT_FILE_DEST '/u01/app/ora11gr2/admin/ora11gr2/adump'
         SET CONTROL_FILES '/u01/oradata/ora11gr2/datafiles/control01.ctl'
         SET DB_RECOVERY_FILE_DEST '/u01/oradata/fra'
         SET DB_CREATE_FILE_DEST '/u01/oradata/ora11gr2/datafiles/'
         SET DIAGNOSTIC_DEST '/u01/app/ora11gr2';
}
Excerpt from RMAN log:
…
contents of Memory Script:
{
   Alter clone database open resetlogs;
}
executing Memory Script

database opened
Finished Duplicate Db at 12.10.2013 09:34:14

Ah, finally!
Finished duplication without errors.

Let's check locations of database files on the target host.
SQL> select name from v$datafile
  2  union all
  3  select name from v$tempfile
  4  union all
  5  select name from v$controlfile
  6  union all
  7  select member from v$logfile;

NAME
--------------------------------------------------------------------------------
/u01/oradata/ora11gr2/datafiles/system.268.824220237
/u01/oradata/ora11gr2/datafiles/sysaux.283.824220237
/u01/oradata/ora11gr2/datafiles/undotbs1.279.824220239
/u01/oradata/ora11gr2/datafiles/users.270.824220237
/u01/oradata/ora11gr2/datafiles/example.297.824220239
/u01/oradata/ora11gr2/datafiles/secure1.277.825195489
/u01/oradata/ora11gr2/datafiles/insecure1.267.825195681
/u01/oradata/ora11gr2/datafiles/lobdata.266.827849207
/u01/oradata/ora11gr2/datafiles/dbfstbs.265.827850825

/u01/oradata/ora11gr2/datafiles/temp01.dbf

/u01/oradata/ora11gr2/datafiles/control01.ctl

/u01/oradata/ora11gr2/redologs/group_3.282.821541363
/u01/oradata/ora11gr2/redologs/group_3.281.821541367
/u01/oradata/ora11gr2/redologs/group_2.292.821541357
/u01/oradata/ora11gr2/redologs/group_2.291.821541361
/u01/oradata/ora11gr2/redologs/group_1.294.821541349
/u01/oradata/ora11gr2/redologs/group_1.293.821541355

17 rows selected.

Hm... I don't like this ASM file naming for my duplicate database.

Again, modify script.
run
{
  SET NEWNAME FOR DATAFILE 1 TO '/u01/oradata/ora11gr2/datafiles/system01.dbf'; 
  SET NEWNAME FOR DATAFILE 2 TO '/u01/oradata/ora11gr2/datafiles/sysaux01.dbf';
  SET NEWNAME FOR DATAFILE 3 TO '/u01/oradata/ora11gr2/datafiles/undotbs01.dbf';
  SET NEWNAME FOR DATAFILE 4 TO '/u01/oradata/ora11gr2/datafiles/users01.dbf'; 
  SET NEWNAME FOR DATAFILE 5 TO '/u01/oradata/ora11gr2/datafiles/example01.dbf';
  SET NEWNAME FOR DATAFILE 6 TO '/u01/oradata/ora11gr2/datafiles/secure1.dbf';
  SET NEWNAME FOR DATAFILE 7 TO '/u01/oradata/ora11gr2/datafiles/insecure1.dbf';
  SET NEWNAME FOR DATAFILE 9 TO '/u01/oradata/ora11gr2/datafiles/lobdata.dbf';
  SET NEWNAME FOR DATAFILE 10 TO '/u01/oradata/ora11gr2/datafiles/dbfstbs.dbf';
  SET NEWNAME FOR TEMPFILE 1 TO '/u01/oradata/ora11gr2/datafiles/temp01.dbf'; 
         DUPLICATE TARGET DATABASE TO "ora11gr2"
         FROM ACTIVE DATABASE
                     LOGFILE
      GROUP 1 ('/u01/oradata/ora11gr2/redologs/redo01a.log', 
               '/u01/oradata/ora11gr2/redologs/redo01b.log') SIZE 50M REUSE, 
      GROUP 2 ('/u01/oradata/ora11gr2/redologs/redo02a.log', 
               '/u01/oradata/ora11gr2/redologs/redo02b.log') SIZE 50M REUSE,
      GROUP 3 ('/u01/oradata/ora11gr2/redologs/redo03a.log', 
               '/u01/oradata/ora11gr2/redologs/redo03b.log') SIZE 50M REUSE
         SPFILE
         SET AUDIT_FILE_DEST '/u01/app/ora11gr2/admin/ora11gr2/adump'
         SET CONTROL_FILES '/u01/oradata/ora11gr2/datafiles/control01.ctl'
         SET DB_RECOVERY_FILE_DEST '/u01/oradata/fra'
         SET DB_CREATE_FILE_DEST '/u01/oradata/ora11gr2/datafiles/'
         SET DIAGNOSTIC_DEST '/u01/app/ora11gr2';
}

Check database files.
SQL> select name from v$datafile
  2  union all
  3  select name from v$tempfile
  4  union all
  5  select name from v$controlfile
  6  union all
  7  select member from v$logfile;


NAME
--------------------------------------------------------------------------------
/u01/oradata/ora11gr2/datafiles/system01.dbf
/u01/oradata/ora11gr2/datafiles/sysaux01.dbf
/u01/oradata/ora11gr2/datafiles/undotbs01.dbf
/u01/oradata/ora11gr2/datafiles/users01.dbf
/u01/oradata/ora11gr2/datafiles/example01.dbf
/u01/oradata/ora11gr2/datafiles/secure1.dbf
/u01/oradata/ora11gr2/datafiles/insecure1.dbf
/u01/oradata/ora11gr2/datafiles/lobdata.dbf
/u01/oradata/ora11gr2/datafiles/dbfstbs.dbf

/u01/oradata/ora11gr2/datafiles/temp01.dbf

/u01/oradata/ora11gr2/datafiles/control01.ctl

/u01/oradata/ora11gr2/redologs/redo03a.log
/u01/oradata/ora11gr2/redologs/redo03b.log
/u01/oradata/ora11gr2/redologs/redo02a.log
/u01/oradata/ora11gr2/redologs/redo02b.log
/u01/oradata/ora11gr2/redologs/redo01a.log
/u01/oradata/ora11gr2/redologs/redo01b.log


SQL> select instance_name from v$instance;

INSTANCE_NAME
----------------
ora11gr2
Much better!


I am currently preparing for “Oracle Database 11g: New Features for Administrators” exam and this is how I practice/learn more about new features.

Pursuing Oracle Certification path forces me to learn more about new features that I don't use much in my daily work. I would recommend to any Oracle expert to choose one of the exams and prepare themselves for that exam. You don't have to take exam (if it's too expensive for you) - after all, all the fun is in preparation ;-)


REFERENCES: http://docs.oracle.com/cd/B28359_01/backup.111/b28270/rcmdupdb.htm

Read More...