Blog Feed - alfredokriegdba.com

Why my new Disk Group is not getting mounted automatically when ASM instance starts?

Today I faced the issue that a new ASM Disk Group created in instance +ASM1 is not getting automatically mounted in instance +ASM2.

The first step was to check DB dependencies:

[oracle@ol5-112-rac2 trace]$ srvctl config database -d RAC

Database unique name: RAC

Database name: RAC

Oracle home: /u01/app/oracle/product/11.2.0/db_1

Oracle user: oracle

Spfile: +DATA/RAC/spfileRAC.ora

Domain: localdomain

Start options: open

Stop options: immediate

Database role: PRIMARY

Management policy: AUTOMATIC

Server pools: RAC

Database instances: RAC1,RAC2

Disk Groups: DATA

Services: reports

Database is administrator managed

You can see that only dependent Disk Groups are ‘DATA’.

Let us add ‘FRA’ also as dependent Disk Group:

[oracle@ol5-112-rac2 trace]$ srvctl modify database -d RAC -a ‘DATA,FRA’

[oracle@ol5-112-rac2 trace]$ srvctl config database -d RAC

Database unique name: RAC

Database name: RAC

Oracle home: /u01/app/oracle/product/11.2.0/db_1

Oracle user: oracle

Spfile: +DATA/RAC/spfileRAC.ora

Domain: localdomain

Start options: open

Stop options: immediate

Database role: PRIMARY

Management policy: AUTOMATIC

Server pools: RAC

Database instances: RAC1,RAC2

Disk Groups: DATA,FRA

Services: reports

Database is administrator managed

The next step is to add the Disk Groups to ASM_DISKGROUPS parameter:

SQL> ALTER SYSTEM SET ASM_DISKGROUPS=’DATA’,’FRA’ scope=both sid=’+ASM1′;

System altered.

SQL> ALTER SYSTEM SET ASM_DISKGROUPS=’DATA’,’FRA’ scope=both sid=’+ASM2′;

System altered.

After restarting +ASM2 instance we can see that FRA Disk Group is mounted automatically:

[oracle@ol5-112-rac2 trace]$ sqlplus / as sysasm

SQL*Plus: Release 11.2.0.1.0 Production on Wed Jun 12 15:44:44 2013

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 – Production

With the Real Application Clusters and Automatic Storage Management options

SQL> set lines 1000

SQL> select * from V$ASM_DISKGROUP;

GROUP_NUMBER NAME SECTOR_SIZE BLOCK_SIZE ALLOCATION_UNIT_SIZE STATE TYPE TOTAL_MB FREE_MB HOT_USED_MB COLD_USED_MB REQUIRED_MIRROR_FREE_MB USABLE_FILE_MB OFFLINE_DISKS COMPATIBILITY DATABASE_COMPATIBILITY V

———— —————————— ———– ———- ——————– ———– —— ———- ———- ———– ———— ———————– ————– ————- ———————————————————— ———————————————————— –

1 DATA 512 4096 1048576 MOUNTED EXTERN 15342 12616 0 2726 0 12616 0 11.2.0.0.0 10.1.0.0.0 N

2 FRA 512 4096 1048576 MOUNTED EXTERN 6133 5962 0 171 0 5962 0 11.2.0.0.0 10.1.0.0.0 N

SQL> exit

Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 – Production

With the Real Application Clusters and Automatic Storage Management options

Thanks,

Alfredo

ORA-15097: cannot SHUTDOWN ASM instance with connected client

In 11g R2 Clusterware configurations the OCR is located in ASM disk, that’s why ASM can’t be shutdown while the cluster is running. You have to shutdown your cluster in order to shutdown ASM instance.

Oracle support note 984663.1

Here’s a good blog about this:

http://divakarmehta.wordpress.com/2012/04/19/how-crs-accesses-ocr-in-oracle-asm-during-11-2-grid-infra-rac-startup/

Example, how to shutdown ASM instance on node ol5-112-rac1:

[oracle@ol5-112-rac1 ~]$ crsctl status resource -t

——————————————————————————–

NAME TARGET STATE SERVER STATE_DETAILS

——————————————————————————–

Local Resources

——————————————————————————–

ora.DATA.dg

ONLINE ONLINE ol5-112-rac1

ONLINE ONLINE ol5-112-rac2

ora.FRA.dg

OFFLINE OFFLINE ol5-112-rac1

ONLINE ONLINE ol5-112-rac2

ora.LISTENER.lsnr

ONLINE ONLINE ol5-112-rac1

ONLINE ONLINE ol5-112-rac2

ora.asm

ONLINE ONLINE ol5-112-rac1

ONLINE ONLINE ol5-112-rac2 Started

ora.eons

ONLINE ONLINE ol5-112-rac1

ONLINE ONLINE ol5-112-rac2

ora.gsd

OFFLINE OFFLINE ol5-112-rac1

OFFLINE OFFLINE ol5-112-rac2

ora.net1.network

ONLINE ONLINE ol5-112-rac1

ONLINE ONLINE ol5-112-rac2

ora.ons

ONLINE ONLINE ol5-112-rac1

ONLINE ONLINE ol5-112-rac2

——————————————————————————–

Cluster Resources

——————————————————————————–

ora.LISTENER_SCAN1.lsnr

1 ONLINE ONLINE ol5-112-rac1

ora.oc4j

1 OFFLINE OFFLINE

ora.ol5-112-rac1.vip

1 ONLINE ONLINE ol5-112-rac1

ora.ol5-112-rac2.vip

1 ONLINE ONLINE ol5-112-rac2

ora.rac.db

1 ONLINE ONLINE ol5-112-rac2 Open

2 ONLINE ONLINE ol5-112-rac1

ora.rac.reports.svc

1 ONLINE ONLINE ol5-112-rac1

ora.scan1.vip

1 ONLINE ONLINE ol5-112-rac1

[oracle@ol5-112-rac1 ~]$

Shutdown RAC1 instance first:

[oracle@ol5-112-rac1 ~]$ srvctl stop instance –d RAC –I RAC1 –o immediate

Then proceed to shutdown cluster (root required):

[oracle@ol5-112-rac1 ~]$ su –

[root@ol5-112-rac1 bin]#

[root@ol5-112-rac1 bin]# ./crsctl stop cluster

CRS-2673: Attempting to stop ‘ora.crsd’ on ‘ol5-112-rac1’

CRS-2790: Starting shutdown of Cluster Ready Services-managed resources on ‘ol5-112-rac1’

CRS-2673: Attempting to stop ‘ora.LISTENER.lsnr’ on ‘ol5-112-rac1’

CRS-2673: Attempting to stop ‘ora.DATA.dg’ on ‘ol5-112-rac1’

CRS-2677: Stop of ‘ora.LISTENER.lsnr’ on ‘ol5-112-rac1’ succeeded

CRS-2673: Attempting to stop ‘ora.ol5-112-rac1.vip’ on ‘ol5-112-rac1’

CRS-2677: Stop of ‘ora.ol5-112-rac1.vip’ on ‘ol5-112-rac1’ succeeded

CRS-2672: Attempting to start ‘ora.ol5-112-rac1.vip’ on ‘ol5-112-rac2’

CRS-2676: Start of ‘ora.ol5-112-rac1.vip’ on ‘ol5-112-rac2’ succeeded

CRS-2677: Stop of ‘ora.DATA.dg’ on ‘ol5-112-rac1’ succeeded

CRS-2673: Attempting to stop ‘ora.asm’ on ‘ol5-112-rac1’

CRS-2677: Stop of ‘ora.asm’ on ‘ol5-112-rac1’ succeeded

CRS-2673: Attempting to stop ‘ora.ons’ on ‘ol5-112-rac1’

CRS-2673: Attempting to stop ‘ora.eons’ on ‘ol5-112-rac1’

CRS-2677: Stop of ‘ora.ons’ on ‘ol5-112-rac1’ succeeded

CRS-2673: Attempting to stop ‘ora.net1.network’ on ‘ol5-112-rac1’

CRS-2677: Stop of ‘ora.net1.network’ on ‘ol5-112-rac1’ succeeded

CRS-2677: Stop of ‘ora.eons’ on ‘ol5-112-rac1’ succeeded

CRS-2792: Shutdown of Cluster Ready Services-managed resources on ‘ol5-112-rac1’ has completed

CRS-2677: Stop of ‘ora.crsd’ on ‘ol5-112-rac1’ succeeded

CRS-2673: Attempting to stop ‘ora.cssdmonitor’ on ‘ol5-112-rac1’

CRS-2673: Attempting to stop ‘ora.ctssd’ on ‘ol5-112-rac1’

CRS-2673: Attempting to stop ‘ora.evmd’ on ‘ol5-112-rac1’

CRS-2673: Attempting to stop ‘ora.asm’ on ‘ol5-112-rac1’

CRS-2677: Stop of ‘ora.cssdmonitor’ on ‘ol5-112-rac1’ succeeded

CRS-2677: Stop of ‘ora.evmd’ on ‘ol5-112-rac1’ succeeded

CRS-2677: Stop of ‘ora.ctssd’ on ‘ol5-112-rac1’ succeeded

CRS-2677: Stop of ‘ora.asm’ on ‘ol5-112-rac1’ succeeded

CRS-2673: Attempting to stop ‘ora.cssd’ on ‘ol5-112-rac1’

CRS-2677: Stop of ‘ora.cssd’ on ‘ol5-112-rac1’ succeeded

CRS-2673: Attempting to stop ‘ora.diskmon’ on ‘ol5-112-rac1’

CRS-2677: Stop of ‘ora.diskmon’ on ‘ol5-112-rac1’ succeeded

[root@ol5-112-rac1 bin]# ps -ef|grep pmon

root 7072 5722 0 14:45 pts/2 00:00:00 grep pmon

[root@ol5-112-rac1 bin]# ps -ef|grep +ASM1

root 7074 5722 0 14:46 pts/2 00:00:00 grep +ASM1

[root@ol5-112-rac1 bin]#

At this point ASM & Cluster Ready Services managed resources are down.

In order to start them again just need to issue:

[root@ol5-112-rac1 bin]# ./crsctl start cluster

[root@ol5-112-rac1 bin]# exit

[oracle@ol5-112-rac1 ~]$ srvctl start instance –d RAC –I RAC1

Thanks,

Alfredo

Hands on practice ASM

Today I want to share with you an ASM exercise which includes how to add an additional disk to our VM RAC nodes, prepare the disk, add the disk to ASM configuration and create a new diskgroup using this disk.

The very first step is to have our VM machines turned off. Then go into your VM host server’s terminal and get into ASM shared disks location.

alfredo@alfredo-N56VM:~$ cd /u04

alfredo@alfredo-N56VM:/u04$ ls

VirtualBox

alfredo@alfredo-N56VM:/u04$ cd VirtualBox/

alfredo@alfredo-N56VM:/u04/VirtualBox$ ls

ol5-112-rac

alfredo@alfredo-N56VM:/u04/VirtualBox$ cd ol5-112-rac/

alfredo@alfredo-N56VM:/u04/VirtualBox/ol5-112-rac$ ls

asm1.vdi  asm2.vdi  asm3.vdi  asm4.vdi

Then create the new VM shared disk and assign it to your VM machines.

alfredo@alfredo-N56VM:/u04/VirtualBox/ol5-112-rac$ VBoxManage createhd --filename asm5.vdi --size 1024 --format VDI --variant Fixed

0%...10%...20%...30%...40%...50%...60%...70%...80%...90%...100%

Disk image created. UUID: f317a4fb-d132-49fa-8572-76ca7a509e04

alfredo@alfredo-N56VM:/u04/VirtualBox/ol5-112-rac$ VBoxManage storageattach ol5-112-rac1 --storagectl "SATA" --port 5 --device 0 --type hdd --medium asm5.vdi --mtype shareable

alfredo@alfredo-N56VM:/u04/VirtualBox/ol5-112-rac$ VBoxManage modifyhd asm5.vdi --type shareable

alfredo@alfredo-N56VM:/u04/VirtualBox/ol5-112-rac$ VBoxManage storageattach ol5-112-rac2 --storagectl "SATA" --port 5 --device 0 --type hdd --medium asm5.vdi --mtype shareable

alfredo@alfredo-N56VM:/u04/VirtualBox/ol5-112-rac$ VBoxManage modifyhd asm5.vdi --type shareable

Now turn your on your node 1, open a terminal as root and format the disk.

[oracle@ol5-112-rac1 ~]$ su -
Password: 
[root@ol5-112-rac1 ~]# cd /dev
[root@ol5-112-rac1 dev]# ls sd*
sda  sda1  sda2  sdb  sdb1  sdc  sdc1  sdd  sdd1  sde  sde1  sdf  sdf1  sdg  sdg1  sdh
[root@ol5-112-rac1 dev]# fdisk /dev/sdh
Device contains neither a valid DOS partition table, nor Sun, SGI or OSF disklabel
Building a new DOS disklabel. Changes will remain in memory only,
until you decide to write them. After that, of course, the previous
content won't be recoverable.

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

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

Command (m for help): p

Disk /dev/sdh: 1073 MB, 1073741824 bytes
255 heads, 63 sectors/track, 130 cylinders
Units = cylinders of 16065 * 512 = 8225280 bytes

   Device Boot      Start         End      Blocks   Id  System
/dev/sdh1               1         130     1044193+  83  Linux

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

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

Turn on the second VM node.
The next step is to add the newly partitioned disk to ASM.

[root@ol5-112-rac1 dev]# /usr/sbin/oracleasm listdisks
DISK1
DISK2
DISK3
DISK4
[root@ol5-112-rac1 dev]# /usr/sbin/oracleasm createdisk DISK5 /dev/sdh1
Writing disk header: done
Instantiating disk: done
[root@ol5-112-rac1 dev]# /usr/sbin/oracleasm listdisks
DISK1
DISK2
DISK3
DISK4
DISK5
[root@ol5-112-rac1 dev]# exit

At this point the new disk is already added to ASM configuration; let’s login to ASM instance and confirm that new disk is present.

[oracle@ol5-112-rac2 ~]$ . oraenv
ORACLE_SID = [RAC2] ? +ASM2
The Oracle base for ORACLE_HOME=/u01/app/11.2.0/grid is /u01/app/oracle
[oracle@ol5-112-rac2 ~]$ crsctl status resource -t
--------------------------------------------------------------------------------
NAME           TARGET  STATE        SERVER                   STATE_DETAILS       
--------------------------------------------------------------------------------
Local Resources
--------------------------------------------------------------------------------
ora.DATA.dg
               ONLINE  ONLINE       ol5-112-rac1                                 
               ONLINE  ONLINE       ol5-112-rac2                                 
ora.LISTENER.lsnr
               ONLINE  ONLINE       ol5-112-rac1                                 
               ONLINE  ONLINE       ol5-112-rac2                                 
ora.asm
               ONLINE  ONLINE       ol5-112-rac1                                 
               ONLINE  ONLINE       ol5-112-rac2             Started             
ora.eons
               ONLINE  ONLINE       ol5-112-rac1                                 
               ONLINE  ONLINE       ol5-112-rac2                                 
ora.gsd
               OFFLINE OFFLINE      ol5-112-rac1                                 
               OFFLINE OFFLINE      ol5-112-rac2                                 
ora.net1.network
               ONLINE  ONLINE       ol5-112-rac1                                 
               ONLINE  ONLINE       ol5-112-rac2                                 
ora.ons
               ONLINE  ONLINE       ol5-112-rac1                                 
               ONLINE  ONLINE       ol5-112-rac2                                 
--------------------------------------------------------------------------------
Cluster Resources
--------------------------------------------------------------------------------
ora.LISTENER_SCAN1.lsnr
      1        ONLINE  ONLINE       ol5-112-rac1                                 
ora.oc4j
      1        OFFLINE OFFLINE                                                   
ora.ol5-112-rac1.vip
      1        ONLINE  ONLINE       ol5-112-rac1                                 
ora.ol5-112-rac2.vip
      1        ONLINE  ONLINE       ol5-112-rac2                                 
ora.rac.db
      1        ONLINE  ONLINE       ol5-112-rac2             Open                
      2        ONLINE  ONLINE       ol5-112-rac1                                 
ora.rac.reports.svc
      1        ONLINE  ONLINE       ol5-112-rac1                                 
ora.scan1.vip
      1        ONLINE  ONLINE       ol5-112-rac1                                 
[oracle@ol5-112-rac2 ~]$ sqlplus / as sysasm


SQL*Plus: Release 11.2.0.1.0 Production on Sun Jun 9 16:06:27 2013

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


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Real Application Clusters and Automatic Storage Management options

SQL> set lines 1000
SQL> column path format a50
SQL> SELECT PATH,NAME,GROUP_NUMBER,DISK_NUMBER,MOUNT_STATUS,HEADER_STATUS,MODE_STATUS,STATE,REDUNDANCY,OS_MB,TOTAL_MB,FREE_MB FROM V$ASM_DISK;

PATH                                               NAME                           GROUP_NUMBER DISK_NUMBER MOUNT_S HEADER_STATU MODE_ST STATE    REDUNDA      OS_MB   TOTAL_MB    FREE_MB
-------------------------------------------------- ------------------------------ ------------ ----------- ------- ------------ ------- -------- ------- ---------- ---------- ----------
/dev/oracleasm/disks/DISK5                                                                   0           0 CLOSED  PROVISIONED  ONLINE  NORMAL   UNKNOWN       1019          0          0
/dev/oracleasm/disks/DISK4                         DATA_0003                                 1           3 CACHED  MEMBER       ONLINE  NORMAL   UNKNOWN       5114       5114       4439
/dev/oracleasm/disks/DISK3                         DATA_0002                                 1           2 CACHED  MEMBER       ONLINE  NORMAL   UNKNOWN       5114       5114       4439
/dev/oracleasm/disks/DISK2                         DATA_0001                                 1           1 CACHED  MEMBER       ONLINE  NORMAL   UNKNOWN       5114       5114       4444
/dev/oracleasm/disks/DISK1                         DATA_0000                                 1           0 CACHED  MEMBER       ONLINE  NORMAL   UNKNOWN       5114       5114       4406

You can see the DISK5 present in the ASM system view, the HEADER_STATUS is ‘PROVISIONED’. The PROVISIONED status implies that an additional platform-specific action has been taken by an administrator to make the disk available for ASM.

Now, let’s drop DISK4 from DATA diskgroup, this will free DISK4 for our new configuration.

SQL> select * from V$ASM_DISKGROUP;

GROUP_NUMBER NAME                           SECTOR_SIZE BLOCK_SIZE ALLOCATION_UNIT_SIZE STATE       TYPE     TOTAL_MB    FREE_MB HOT_USED_MB COLD_USED_MB REQUIRED_MIRROR_FREE_MB USABLE_FILE_MB OFFLINE_DISKS COMPATIBILITY            DATABASE_COMPATIBILITY                                        V
------------ ------------------------------ ----------- ---------- -------------------- ----------- ------ ---------- ---------- ----------- ------------ ----------------------- -------------- ------------- ------------------------------------------------------------ ------------------------------------------------------------ -
           1 DATA                                   512       4096 1048576 MOUNTED     EXTERN      20456      17728           0         2728 0          17728             0 11.2.0.0.0               10.1.0.0.0 N



SQL> ALTER DISKGROUP DATA DROP DISK DATA_0003;

Diskgroup altered.

SQL> SELECT PATH,NAME,GROUP_NUMBER,DISK_NUMBER,MOUNT_STATUS,HEADER_STATUS,MODE_STATUS,STATE,REDUNDANCY,OS_MB,TOTAL_MB,FREE_MB FROM V$ASM_DISK;

PATH                                               NAME                           GROUP_NUMBER DISK_NUMBER MOUNT_S HEADER_STATU MODE_ST STATE    REDUNDA      OS_MB   TOTAL_MB    FREE_MB
-------------------------------------------------- ------------------------------ ------------ ----------- ------- ------------ ------- -------- ------- ---------- ---------- ----------
/dev/oracleasm/disks/DISK5                                                                   0           0 CLOSED  PROVISIONED  ONLINE  NORMAL   UNKNOWN       1019          0          0
/dev/oracleasm/disks/DISK4                         DATA_0003                                 1           3 CACHED  MEMBER       ONLINE  DROPPING UNKNOWN       5114       5114       4546
/dev/oracleasm/disks/DISK3                         DATA_0002                                 1           2 CACHED  MEMBER       ONLINE  NORMAL   UNKNOWN       5114       5114       4404
/dev/oracleasm/disks/DISK2                         DATA_0001                                 1           1 CACHED  MEMBER       ONLINE  NORMAL   UNKNOWN       5114       5114       4409
/dev/oracleasm/disks/DISK1                         DATA_0000 1           0 CACHED  MEMBER       ONLINE  NORMAL   UNKNOWN       5114 5114       4369

You can see the STATE of DISK4 is DROPPING, in this stage ASM will rebalance all the data from DISK4 into the disk of diskgroup DATA. In order to speed this operation we are going to modify the rebalance power of the diskgroup.

SQL> ALTER DISKGROUP DATA REBALANCE POWER 5;

Diskgroup altered.

SQL> SELECT PATH,NAME,GROUP_NUMBER,DISK_NUMBER,MOUNT_STATUS,HEADER_STATUS,MODE_STATUS,STATE,REDUNDANCY,OS_MB,TOTAL_MB,FREE_MB FROM V$ASM_DISK;


PATH                                               NAME                           GROUP_NUMBER DISK_NUMBER MOUNT_S HEADER_STATU MODE_ST STATE    REDUNDA      OS_MB   TOTAL_MB    FREE_MB
-------------------------------------------------- ------------------------------ ------------ ----------- ------- ------------ ------- -------- ------- ---------- ---------- ----------
/dev/oracleasm/disks/DISK5                                                                   0           0 CLOSED  PROVISIONED  ONLINE  NORMAL   UNKNOWN       1019          0          0
/dev/oracleasm/disks/DISK4                                                                   0           1 CLOSED  FORMER       ONLINE  NORMAL   UNKNOWN       5114          0          0
/dev/oracleasm/disks/DISK3                         DATA_0002                                 1           2 CACHED  MEMBER       ONLINE  NORMAL   UNKNOWN       5114       5114       4213
/dev/oracleasm/disks/DISK2                         DATA_0001                                 1           1 CACHED  MEMBER       ONLINE  NORMAL   UNKNOWN       5114       5114       4218
/dev/oracleasm/disks/DISK1                         DATA_0000                                 1           0 CACHED  MEMBER       ONLINE  NORMAL   UNKNOWN       5114       5114       4185

SQL> show parameter power

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
asm_power_limit                      integer     1
SQL> ALTER DISKGROUP DATA REBALANCE POWER 1;

Diskgroup altered.

After modifying the rebalance power to 5 ASM will provide more resources to the rebalance process, this results in faster rebalance times. You can see that DISK4’s header status is now ‘FORMER’ this means a disk was once part of a disk group but has been dropped cleanly from the group. It may be added to a new disk group with the ALTER DISKGROUP statement.

Be sure to modify back the power of the disk group DATA to 1.

Now let’s create a new disk group named FRA using DISK4 & DISK5.

SQL> CREATE DISKGROUP FRA EXTERNAL REDUNDANCY DISK '/dev/oracleasm/disks/DISK4' NAME FRA_0000, '/dev/oracleasm/disks/DISK5' NAME FRA_0001;

Diskgroup created.

SQL> SELECT PATH,NAME,GROUP_NUMBER,DISK_NUMBER,MOUNT_STATUS,HEADER_STATUS,MODE_STATUS,STATE,REDUNDANCY,OS_MB,TOTAL_MB,FREE_MB FROM V$ASM_DISK;

PATH                                               NAME                           GROUP_NUMBER DISK_NUMBER MOUNT_S HEADER_STATU MODE_ST STATE    REDUNDA      OS_MB   TOTAL_MB    FREE_MB
-------------------------------------------------- ------------------------------ ------------ ----------- ------- ------------ ------- -------- ------- ---------- ---------- ----------
/dev/oracleasm/disks/DISK4                         FRA_0000                                  2           0 CACHED  MEMBER       ONLINE  NORMAL   UNKNOWN       5114       5114       5072
/dev/oracleasm/disks/DISK3                         DATA_0002                                 1           2 CACHED  MEMBER       ONLINE  NORMAL   UNKNOWN       5114       5114       4215
/dev/oracleasm/disks/DISK2                         DATA_0001                                 1           1 CACHED  MEMBER       ONLINE  NORMAL   UNKNOWN       5114       5114       4218
/dev/oracleasm/disks/DISK1                         DATA_0000                                 1           0 CACHED  MEMBER       ONLINE  NORMAL   UNKNOWN       5114       5114       4183
/dev/oracleasm/disks/DISK5                         FRA_0001                                  2           1 CACHED  MEMBER       ONLINE  NORMAL   UNKNOWN       1019       1019       1009

SQL> select * from V$ASM_DISKGROUP;

GROUP_NUMBER NAME                           SECTOR_SIZE BLOCK_SIZE ALLOCATION_UNIT_SIZE STATE       TYPE     TOTAL_MB    FREE_MB HOT_USED_MB COLD_USED_MB REQUIRED_MIRROR_FREE_MB USABLE_FILE_MB OFFLINE_DISKS COMPATIBILITY            DATABASE_COMPATIBILITY                                        V
------------ ------------------------------ ----------- ---------- -------------------- ----------- ------ ---------- ---------- ----------- ------------ ----------------------- -------------- ------------- ------------------------------------------------------------ ------------------------------------------------------------ -
           1 DATA                                   512       4096              1048576 MOUNTED     EXTERN      15342      12616           0         2726                       0          12616             0 11.2.0.0.0               10.1.0.0.0                                                    N
           2 FRA                                    512       4096              1048576 MOUNTED     EXTERN       6133       6081           0           52                       0           6081             0 10.1.0.0.0               10.1.0.0.0                                                    N

SQL> 

ALTER DISKGROUP FRA SET ATTRIBUTE 'compatible.asm'='11.2.0.0.0';

SQL> ALTER DISKGROUP FRA SET ATTRIBUTE 'compatible.asm'='11.2.0.0.0';

Diskgroup altered.

SQL> select * from V$ASM_DISKGROUP;

GROUP_NUMBER NAME                           SECTOR_SIZE BLOCK_SIZE ALLOCATION_UNIT_SIZE STATE       TYPE     TOTAL_MB    FREE_MB HOT_USED_MB COLD_USED_MB REQUIRED_MIRROR_FREE_MB USABLE_FILE_MB OFFLINE_DISKS COMPATIBILITY            DATABASE_COMPATIBILITY                                        V
------------ ------------------------------ ----------- ---------- -------------------- ----------- ------ ---------- ---------- ----------- ------------ ----------------------- -------------- ------------- ------------------------------------------------------------ ------------------------------------------------------------ -
           1 DATA                                   512       4096              1048576 MOUNTED     EXTERN      15342      12616           0         2726                       0          12616             0 11.2.0.0.0               10.1.0.0.0                                                    N
           2 FRA                                    512       4096              1048576 MOUNTED     EXTERN       6133       6079           0           54                       0           6079             0 11.2.0.0.0               10.1.0.0.0                                                    N

SQL>

FRA disk group is ready to host our flash recovery area for archivelogs and backups.

This is a small but useful example of the ASM functionality.

Thanks,
Alfredo

GoldenGate 11G Install on Linux x86

Today I will show you how to install GoldenGate under Linux 5.5 following the recipe of my teammate Gleb.

(http://www.pythian.com/blog/oracle-goldengate-installation-part-1/)

Prerequisites:

· Oracle Linux 5.5 installed in 2 VM using VirtualBox, I have used one pre-built VM from Oracle (http://www.oracle.com/technetwork/database/enterprise-edition/databaseappdev-vm-161299.html)

· Download Oracle GoldenGate software from (http://www.oracle.com/technetwork/middleware/goldengate/downloads/index.html)

· Start both VM machines and change the host name to source_host & target_host respectively

· Test ping connectivity from source_host to target_host

Installation

· Create /01 directory and change the owner to oracle:dba on source_host:

[oracle@source_host Desktop]$ su –

Password:

[root@source_host ~]# cd /

[root@source_host /]# df -h

Filesystem Size Used Avail Use% Mounted on

/dev/hda1 11G 2.7G 6.9G 28% /

/dev/hdb1 12G 11G 683M 94% /home

tmpfs 1014M 264M 750M 27% /dev/shm

[root@source_host /]# mkdir /u01

[root@source_host /]# chown oracle:dba /u01

[root@source_host /]# exit

Logout

· Copy GoldenGate binaries to /u01 and unzip the file, then create goldengate directory under /u01 and untar the file:

[oracle@source_host Desktop]$ mv ogg112101_fbo_ggs_Linux_x86_ora11g_32bit.zip /u01/

[oracle@source_host Desktop]$ cd /u01

[oracle@source_host u01]$ ls

ogg112101_fbo_ggs_Linux_x86_ora11g_32bit.zip

[oracle@source_host u01]$ unzip ogg112101_fbo_ggs_Linux_x86_ora11g_32bit.zip

Archive: ogg112101_fbo_ggs_Linux_x86_ora11g_32bit.zip

inflating: fbo_ggs_Linux_x86_ora11g_32bit.tar

inflating: OGG_WinUnix_Rel_Notes_11.2.1.0.1.pdf

inflating: Oracle GoldenGate 11.2.1.0.1 README.txt

inflating: Oracle GoldenGate 11.2.1.0.1 README.doc

[oracle@source_host u01]$ ls

fbo_ggs_Linux_x86_ora11g_32bit.tar

ogg112101_fbo_ggs_Linux_x86_ora11g_32bit.zip

OGG_WinUnix_Rel_Notes_11.2.1.0.1.pdf

Oracle GoldenGate 11.2.1.0.1 README.doc

Oracle GoldenGate 11.2.1.0.1 README.txt

[oracle@source_host u01]$ mkdir goldengate

[oracle@source_host u01]$ mv fbo_ggs_Linux_x86_ora11g_32bit.tar goldengate/

[oracle@source_host u01]$ cd goldengate/

[oracle@source_host goldengate]$ ls

fbo_ggs_Linux_x86_ora11g_32bit.tar

[oracle@source_host goldengate]$ tar -xvf fbo_ggs_Linux_x86_ora11g_32bit.tar

UserExitExamples/

UserExitExamples/ExitDemo_passthru/

UserExitExamples/ExitDemo_passthru/Makefile_passthru.AIX

UserExitExamples/ExitDemo_passthru/exitdemo_passthru.c

UserExitExamples/ExitDemo_passthru/Makefile_passthru.LINUX

UserExitExamples/ExitDemo_passthru/Makefile_passthru.HP_OSS

UserExitExamples/ExitDemo_passthru/exitdemopassthru.vcproj

UserExitExamples/ExitDemo_passthru/readme.txt

UserExitExamples/ExitDemo_passthru/Makefile_passthru.SOLARIS

UserExitExamples/ExitDemo_passthru/Makefile_passthru.HPUX

UserExitExamples/ExitDemo_lobs/

UserExitExamples/ExitDemo_lobs/exitdemo_lob.vcproj

UserExitExamples/ExitDemo_lobs/Makefile_lob.HPUX

UserExitExamples/ExitDemo_lobs/exitdemo_lob.c

UserExitExamples/ExitDemo_lobs/Makefile_lob.AIX

UserExitExamples/ExitDemo_lobs/Makefile_lob.SOLARIS

UserExitExamples/ExitDemo_lobs/Makefile_lob.LINUX

UserExitExamples/ExitDemo_lobs/readme.txt

UserExitExamples/ExitDemo/

UserExitExamples/ExitDemo/exitdemo.vcproj

UserExitExamples/ExitDemo/Makefile_exit_demo.SOLARIS

UserExitExamples/ExitDemo/exitdemo_utf16.c

UserExitExamples/ExitDemo/readme.txt

UserExitExamples/ExitDemo/exitdemo.c

UserExitExamples/ExitDemo/Makefile_exit_demo.HPUX

UserExitExamples/ExitDemo/Makefile_exit_demo.AIX

UserExitExamples/ExitDemo/Makefile_exit_demo.LINUX

UserExitExamples/ExitDemo/Makefile_exit_demo.HP_OSS

UserExitExamples/ExitDemo_pk_befores/

UserExitExamples/ExitDemo_pk_befores/exitdemo_pk_befores.c

UserExitExamples/ExitDemo_pk_befores/Makefile_pk_befores.SOLARIS

UserExitExamples/ExitDemo_pk_befores/exitdemo_pk_befores.vcproj

UserExitExamples/ExitDemo_pk_befores/readme.txt

UserExitExamples/ExitDemo_pk_befores/Makefile_pk_befores.AIX

UserExitExamples/ExitDemo_pk_befores/Makefile_pk_befores.LINUX

UserExitExamples/ExitDemo_pk_befores/Makefile_pk_befores.HPUX

UserExitExamples/ExitDemo_more_recs/

UserExitExamples/ExitDemo_more_recs/Makefile_more_recs.HPUX

UserExitExamples/ExitDemo_more_recs/exitdemo_more_recs.vcproj

UserExitExamples/ExitDemo_more_recs/Makefile_more_recs.AIX

UserExitExamples/ExitDemo_more_recs/readme.txt

UserExitExamples/ExitDemo_more_recs/exitdemo_more_recs.c

UserExitExamples/ExitDemo_more_recs/Makefile_more_recs.LINUX

UserExitExamples/ExitDemo_more_recs/Makefile_more_recs.SOLARIS

bcpfmt.tpl

bcrypt.txt

cfg/

cfg/ProfileConfig.xml

cfg/jps-config-jse.xml

cfg/password.properties

cfg/MPMetadataSchema.xsd

cfg/mpmetadata.xml

cfg/Config.properties

chkpt_ora_create.sql

cobgen

convchk

db2cntl.tpl

ddl_cleartrace.sql

ddl_ddl2file.sql

ddl_disable.sql

ddl_enable.sql

ddl_filter.sql

ddl_nopurgeRecyclebin.sql

ddl_ora10.sql

ddl_ora10upCommon.sql

ddl_ora11.sql

ddl_ora9.sql

ddl_pin.sql

ddl_purgeRecyclebin.sql

ddl_remove.sql

ddl_session.sql

ddl_session1.sql

ddl_setup.sql

ddl_status.sql

ddl_staymetadata_off.sql

ddl_staymetadata_on.sql

ddl_trace_off.sql

ddl_trace_on.sql

ddl_tracelevel.sql

ddlcob

defgen

demo_more_ora_create.sql

demo_more_ora_insert.sql

demo_ora_create.sql

demo_ora_insert.sql

demo_ora_lob_create.sql

demo_ora_misc.sql

demo_ora_pk_befores_create.sql

demo_ora_pk_befores_insert.sql

demo_ora_pk_befores_updates.sql

dirjar/

dirjar/xmlparserv2.jar

dirjar/spring-security-cas-client-3.0.1.RELEASE.jar

dirjar/spring-security-taglibs-3.0.1.RELEASE.jar

dirjar/org.springframework.transaction-3.0.0.RELEASE.jar

dirjar/identityutils.jar

dirjar/xpp3_min-1.1.4c.jar

dirjar/jps-api.jar

dirjar/jacc-spi.jar

dirjar/org.springframework.jdbc-3.0.0.RELEASE.jar

dirjar/org.springframework.aspects-3.0.0.RELEASE.jar

dirjar/identitystore.jar

dirjar/jps-mbeans.jar

dirjar/fmw_audit.jar

dirjar/commons-codec-1.3.jar

dirjar/jmxremote_optional-1.0-b02.jar

dirjar/spring-security-core-3.0.1.RELEASE.jar

dirjar/log4j-1.2.15.jar

dirjar/commons-logging-1.0.4.jar

dirjar/org.springframework.context-3.0.0.RELEASE.jar

dirjar/org.springframework.asm-3.0.0.RELEASE.jar

dirjar/org.springframework.expression-3.0.0.RELEASE.jar

dirjar/org.springframework.instrument-3.0.0.RELEASE.jar

dirjar/jps-wls.jar

dirjar/jps-upgrade.jar

dirjar/jdmkrt-1.0-b02.jar

dirjar/jps-ee.jar

dirjar/jps-common.jar

dirjar/org.springframework.beans-3.0.0.RELEASE.jar

dirjar/jps-manifest.jar

dirjar/oraclepki.jar

dirjar/spring-security-acl-3.0.1.RELEASE.jar

dirjar/jagent.jar

dirjar/osdt_xmlsec.jar

dirjar/jps-patching.jar

dirjar/org.springframework.context.support-3.0.0.RELEASE.jar

dirjar/jps-unsupported-api.jar

dirjar/monitor-common.jar

dirjar/osdt_cert.jar

dirjar/spring-security-web-3.0.1.RELEASE.jar

dirjar/spring-security-config-3.0.1.RELEASE.jar

dirjar/org.springframework.aop-3.0.0.RELEASE.jar

dirjar/xstream-1.3.jar

dirjar/jps-internal.jar

dirjar/osdt_core.jar

dirjar/org.springframework.core-3.0.0.RELEASE.jar

dirjar/slf4j-api-1.4.3.jar

dirjar/ldapjclnt11.jar

dirjar/org.springframework.test-3.0.0.RELEASE.jar

dirjar/org.springframework.orm-3.0.0.RELEASE.jar

dirjar/org.springframework.web-3.0.0.RELEASE.jar

dirjar/slf4j-log4j12-1.4.3.jar

dirjar/jsr250-api-1.0.jar

dirprm/

dirprm/jagent.prm

emsclnt

extract

freeBSD.txt

ggMessage.dat

ggcmd

ggsci

help.txt

jagent.sh

keygen

libantlr3c.so

libdb-5.2.so

libgglog.so

libggrepo.so

libicudata.so.38

libicui18n.so.38

libicuuc.so.38

libxerces-c.so.28

libxml2.txt

logdump

marker_remove.sql

marker_setup.sql

marker_status.sql

mgr

notices.txt

oggerr

params.sql

prvtclkm.plb

pw_agent_util.sh

remove_seq.sql

replicat

retrace

reverse

role_setup.sql

sequence.sql

server

sqlldr.tpl

tcperrs

ucharset.h

ulg.sql

usrdecs.h

zlib.txt

[oracle@source_host goldengate]$

· Start GG command line utility and create necessary working directories for GoldenGate:

[oracle@source_host goldengate]$ ./ggsci

Oracle GoldenGate Command Interpreter for Oracle

Version 11.2.1.0.1 OGGCORE_11.2.1.0.1_PLATFORMS_120423.0230_FBO

Linux, x86, 32bit (optimized), Oracle 11g on Apr 23 2012 08:09:25

GGSCI (source_host.localdomain) 1> create subdirs

Creating subdirectories under current directory /u01/goldengate

Parameter files /u01/goldengate/dirprm: already exists

Report files /u01/goldengate/dirrpt: created

Checkpoint files /u01/goldengate/dirchk: created

Process status files /u01/goldengate/dirpcs: created

SQL script files /u01/goldengate/dirsql: created

Database definitions files /u01/goldengate/dirdef: created

Extract data files /u01/goldengate/dirdat: created

Temporary files /u01/goldengate/dirtmp: created

Stdout files /u01/goldengate/dirout: created

GGSCI (source_host.localdomain) 2>

GGSCI (source_host.localdomain) 2> exit

[oracle@source_host goldengate]$ mkdir discard

[oracle@source_host goldengate]$

· Connect to Source DB and run required configuration and scripts:

[oracle@source_host goldengate]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.2.0 Production on Sat Jun 8 11:55:56 2013

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 – Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> alter database add supplemental log data;

Database altered.

SQL> alter system set recyclebin=off scope=spfile;

System altered.

SQL> create user ggate identified by ggate default tablespace users temporary tablespace temp;

User created.

SQL> grant connect,resource to ggate;

Grant succeeded.

SQL> grant execute on utl_file to ggate;

Grant succeeded.

SQL>

SQL> @marker_setup.sql

Marker setup script

You will be prompted for the name of a schema for the Oracle GoldenGate database objects.

NOTE: The schema must be created prior to running this script.

NOTE: Stop all DDL replication before starting this installation.

Enter Oracle GoldenGate schema name:ggate

Marker setup table script complete, running verification script…

Please enter the name of a schema for the GoldenGate database objects:

Setting schema name to GGATE

MARKER TABLE

——————————-

MARKER SEQUENCE

——————————-

Script complete.

SQL>

SQL> @ddl_setup.sql

Oracle GoldenGate DDL Replication setup script

Verifying that current user has privileges to install DDL Replication…

You will be prompted for the name of a schema for the Oracle GoldenGate database objects.

NOTE: For an Oracle 10g source, the system recycle bin must be disabled. For Oracle 11g and later, it can be enabled.

NOTE: The schema must be created prior to running this script.

NOTE: Stop all DDL replication before starting this installation.

Enter Oracle GoldenGate schema name:ggate

Working, please wait …

Spooling to file ddl_setup_spool.txt

Checking for sessions that are holding locks on Oracle Golden Gate metadata tables …

Check complete.

Using GGATE as a Oracle GoldenGate schema name.

Working, please wait …

DDL replication setup script complete, running verification script…

Please enter the name of a schema for the GoldenGate database objects:

Setting schema name to GGATE

CLEAR_TRACE STATUS:

Line/pos Error

—————————————- —————————————————————–

No errors No errors

CREATE_TRACE STATUS:

Line/pos Error

—————————————- —————————————————————–

No errors No errors

TRACE_PUT_LINE STATUS:

Line/pos Error

—————————————- —————————————————————–

No errors No errors

INITIAL_SETUP STATUS:

Line/pos Error

—————————————- —————————————————————–

No errors No errors

DDLVERSIONSPECIFIC PACKAGE STATUS:

Line/pos Error

—————————————- —————————————————————–

No errors No errors

DDLREPLICATION PACKAGE STATUS:

Line/pos Error

—————————————- —————————————————————–

No errors No errors

DDLREPLICATION PACKAGE BODY STATUS:

Line/pos Error

—————————————- —————————————————————–

No errors No errors

DDL IGNORE TABLE

———————————–

DDL IGNORE LOG TABLE

———————————–

DDLAUX PACKAGE STATUS:

Line/pos Error

—————————————- —————————————————————–

No errors No errors

DDLAUX PACKAGE BODY STATUS:

Line/pos Error

—————————————- —————————————————————–

No errors No errors

SYS.DDLCTXINFO PACKAGE STATUS:

Line/pos Error

—————————————- —————————————————————–

No errors No errors

SYS.DDLCTXINFO PACKAGE BODY STATUS:

Line/pos Error

—————————————- —————————————————————–

No errors No errors

DDL HISTORY TABLE

———————————–

DDL HISTORY TABLE(1)

———————————–

DDL DUMP TABLES

———————————–

DDL DUMP COLUMNS

———————————–

DDL DUMP LOG GROUPS

———————————–

DDL DUMP PARTITIONS

———————————–

DDL DUMP PRIMARY KEYS

———————————–

DDL SEQUENCE

———————————–

GGS_TEMP_COLS

———————————–

GGS_TEMP_UK

———————————–

DDL TRIGGER CODE STATUS:

Line/pos Error

—————————————- —————————————————————–

No errors No errors

DDL TRIGGER INSTALL STATUS

———————————–

DDL TRIGGER RUNNING STATUS

————————————————————————————————————————

ENABLED

STAYMETADATA IN TRIGGER

————————————————————————————————————————

OFF

DDL TRIGGER SQL TRACING

————————————————————————————————————————

DDL TRIGGER TRACE LEVEL

————————————————————————————————————————

LOCATION OF DDL TRACE FILE

————————————————————————————————————————

/home/oracle/app/oracle/diag/rdbms/orcl/orcl/trace/ggs_ddl_trace.log

Analyzing installation status…

STATUS OF DDL REPLICATION

————————————————————————————————————————

SUCCESSFUL installation of DDL Replication software components

Script complete.

SQL>

SQL> @role_setup.sql

GGS Role setup script

This script will drop and recreate the role GGS_GGSUSER_ROLE

To use a different role name, quit this script and then edit the params.sql script to change the gg_role parameter to the preferred name. (Do not run the script.)

You will be prompted for the name of a schema for the GoldenGate database objects.

NOTE: The schema must be created prior to running this script.

NOTE: Stop all DDL replication before starting this installation.

Enter GoldenGate schema name:ggate

Wrote file role_setup_set.txt

PL/SQL procedure successfully completed.

Role setup script complete

Grant this role to each user assigned to the Extract, GGSCI, and Manager processes, by using the following SQL command:

GRANT GGS_GGSUSER_ROLE TO

where is the user assigned to the GoldenGate processes.

SQL>

SQL> grant GGS_GGSUSER_ROLE to ggate;

Grant succeeded.

SQL> @ddl_enable.sql

Trigger altered.

SQL>

SQL> create user sender identified by qwerty default tablespace users temporary tablespace temp;

User created.

SQL> grant connect,resource,unlimited tablespace to sender;

Grant succeeded.

SQL>

· The installation is done in the Source host, now do the same on the Target host

· Connect to the Target DB and create a receiver user which will be synced from Source DB

SQL> create user receiver identified by qwerty default tablespace users temporary tablespace temp;

User created.

SQL> grant connect,resource,unlimited tablespace to receiver;

Grant succeeded.

SQL>

· In the Source host configure the MANAGER and the Extract processes

[oracle@source_host goldengate]$ ./ggsci

Oracle GoldenGate Command Interpreter for Oracle

Version 11.2.1.0.1 OGGCORE_11.2.1.0.1_PLATFORMS_120423.0230_FBO

Linux, x86, 32bit (optimized), Oracle 11g on Apr 23 2012 08:09:25

GGSCI (source_host.localdomain) 1> info all

Program Status Group Lag at Chkpt Time Since Chkpt

MANAGER STOPPED

GGSCI (source_host.localdomain) 2> edit params mgr

–Add this line–

PORT 7809

GGSCI (source_host.localdomain) 3> start manager

Manager started.

GGSCI (source_host.localdomain) 7> info all

Program Status Group Lag at Chkpt Time Since Chkpt

MANAGER RUNNING

GGSCI (source_host.localdomain) 8> add extract ext1, tranlog, begin now

EXTRACT added.

GGSCI (source_host.localdomain) 9> add exttrail /u01/goldengate/dirdat/lt, extract ext1

EXTTRAIL added.

GGSCI (source_host.localdomain) 10> edit params ext1

–Add these lines–

–extract group–

extract ext1

–connection to database–

userid ggate, password ggate

–hostname and port for trail–

rmthost target_host.localdomain, mgrport 7809

–path and name for trail–

rmttrail /u01/goldengate/dirdat/lt

–DDL support

ddl include mapped objname sender.*;

–DML

table sender.*;

GGSCI (source_host.localdomain) 12>

· Now configure the MANAGER and Replicat processes in Target host

[oracle@target_host goldengate]$ ./ggsci

Oracle GoldenGate Command Interpreter for Oracle

Version 11.2.1.0.1 OGGCORE_11.2.1.0.1_PLATFORMS_120423.0230_FBO

Linux, x86, 32bit (optimized), Oracle 11g on Apr 23 2012 08:09:25

GGSCI (target_host.localdomain) 1> edit params ./GLOBAL

GGSCI (target_host.localdomain) 4> dblogin userid ggate

Password:

Successfully logged into database.

GGSCI (target_host.localdomain) 5> add checkpointtable ggate.checkpoint

Successfully created checkpoint table ggate.checkpoint.

GGSCI (target_host.localdomain) 6> add replicat rep1, exttrail /u01/goldengate/dirdat/lt,checkpointtable ggate.checkpoint

REPLICAT added.

GGSCI (target_host.localdomain) 7> edit params rep1

–Add these lines–

–Replicat group —

replicat rep1

–source and target definitions

ASSUMiETARGETDEFS

–target database login —

userid ggate, password ggate

–file for dicarded transaction —

discardfile /u01/goldengate/discard/rep1_discard.txt, append, megabytes 10

–ddl support

DDL

–Specify table mapping —

map sender.*, target receiver.*;

GGSCI (target_host.localdomain) 11> edit params mgr

–Add this line–

PORT 7809

GGSCI (target_host.localdomain) 12> start manager

Manager started.

GGSCI (target_host.localdomain) 13> info all

Program Status Group Lag at Chkpt Time Since Chkpt

MANAGER RUNNING

REPLICAT STOPPED REP1 00:00:00 00:03:31

· Now on the Source host start the Extract process

GGSCI (source_host.localdomain) 3> start extract ext1

Sending START request to MANAGER …

EXTRACT EXT1 starting

GGSCI (source_host.localdomain) 4> info all

Program Status Group Lag at Chkpt Time Since Chkpt

MANAGER RUNNING

EXTRACT RUNNING EXT1 00:00:00 00:22:34

GGSCI (source_host.localdomain) 5>

· Now on the Target host start the Replicat process

GGSCI (target_host.localdomain) 14> start replicat rep1

Sending START request to MANAGER …

REPLICAT REP1 starting

GGSCI (target_host.localdomain) 16> info all

Program Status Group Lag at Chkpt Time Since Chkpt

MANAGER RUNNING

REPLICAT RUNNING REP1 00:00:00 00:00:05

· At this point GoldenGate is installed and configured in both Source & Target hosts, Extract/Replicat processes are running, now the funny part begins. Let’s test our replication environment!

· In the Source DB run the below command:

SQL> create table sender.test_tab_1 (id number,rnd_str varchar2(12));

SQL> insert into sender.test_tab_1 values (1,’test_1′);

SQL> commit;

· · Now check the Target DB:

SQL> select * from receiver.test_tab_1;

ID RND_STR

———- ————

1 test_1

Our GoldenGate DDL and DML replication is now working. The table was created on the Target DB and data were replicated.

Thanks,

Alfredo

Why the execution plan changed? Case study

Overview

At some point in time the execution plan of the SQL_ID etp65bryqtd2d changed. The new execution plan drops on performance from an Avg. of 0.01 seconds per execution to an Avg. of 1.5 seconds per execution.

This increase on elapsed time per execution provoke that the application can’t process work orders fast enough, the work queue started to drastically grow leading the application team to escalate a severity 1 issue.

Analysis

Execution plan changes

The execution plan changes only when a parse is performed, this because the _optim_bind_peeking parameter is on default value “TRUE”. A hard parse is required due to cursor invalidation in the shared pool; a cursor can be invalidated for some reasons but the most happening is, object change (Index rebuild, DBMS_STATS, etc.).

Rolling Cursor Invalidation

Starting with Oracle10g cursors are marked for rolling invalidation instead of marked INVALID immediately. On the next execution of the query the Oracle server will generate a random number between 0 and the value of the _optimizer_invalidation_period parameter, which has a default value of 18000, and the cursor will remain valid for this number of seconds. Upon every following execution Oracle will check if this random selected timeout has expired. If that is the case then the cursor will be hard parsed again.

Things will change without notice! Cursor invalidation has changed in the past and the reason for this change is that previously all dependent cursors were invalidated immediately after gathering new object statistics. This massive invalidation might cause a serious degradation in performance right after statistics gathering due to a high number of hard parses. Rolling cursor invalidation spreads the invalidation of cursors out over a longer period of time thereby avoiding the performance degradation caused by hard parsing the invalidated cursors.

Reference: Metalink note:557661.1

Bind variable peeking

The query optimizer peeks at the values of user-defined bind variables on the first invocation of a cursor. This feature lets the optimizer determine the selectivity of any WHERE clause condition, as well as if literals have been used instead of bind variables. On subsequent invocations of the cursor, no peeking takes place, and the cursor is shared, based on the standard cursor-sharing criteria, even if subsequent invocations use different bind values.

In this case we can clearly see that the elapsed time per execution is not changing because of the bind peeking; when Oracle peeks a bad execution plan, all elapsed times are bad.

CBO trace event 10053

Behind the scenes, what is the CBO doing when it comes to how it comes up with an execution plan? This is where the 10053 trace event comes to play. Other tools or settings show us WHAT the CBO comes up with; the 10053 setting tells us HOW the CBO came to its decision (the final execution plan).

conn / as sysdba

oradebug setmypid

oradebug unlimit

oradebug event 10053 trace name context forever, level 1

…your statement here…

oradebug event 10053 trace name context off

oradebug tracefile_name

We have performed this CBO trace when the query was performing bad and when it was performing well. With this method we have 2 trace files to compare and find out what is making the CBO to take such bad plan.

Notice that this method will produce a trace file only in the hard parse stage. So, in order to force the CBO to hard parse the SQL we have run it with a comment hint.

variable N1 VARCHAR2(128)

exec :N1 :=’125′;

select /* comment*/…

After comparing both trace files we can clearly see when the CBO chose the good plan:

***********************

Best so far: Table#: 1 cost: 7.3522 card: 3973.0000 bytes: 218515

Table#: 7 cost: 16.2850 card: 9.0178 bytes: 612

Table#: 2 cost: 21.6884 card: 33.7888 bytes: 3094

Table#: 5 cost: 48.9215 card: 3565.0298 bytes: 406410

Table#: 4 cost: 1475.7155 card: 3565.4846 bytes: 556140

Table#: 0 cost: 1483.2886 card: 3559.8976 bytes: 644360

Table#: 6 cost: 2195.7103 card: 3599.4022 bytes: 770186

Table#: 3 cost: 2197.9000 card: 3599.4022 bytes: 820572

***********************

In the other hand, the trace of the bad plan is having a huge cost:

***********************

Best so far: Table#: 1 cost: 7.3510 card: 3928.0000 bytes: 216040

Table#: 7 cost: 16.2836 card: 8.9798 bytes: 612

Table#: 2 cost: 21.6870 card: 32.6678 bytes: 3003

Table#: 5 cost: 153.8561 card: 20168.2755 bytes: 2299152

Table#: 4 cost: 8225.5481 card: 20170.8485 bytes: 3146676

Table#: 0 cost: 8233.2391 card: 20139.2414 bytes: 3645159

Table#: 6 cost: 12263.4248 card: 20362.7290 bytes: 4357682

Table#: 3 cost: 12266.6610 card: 20362.7290 bytes: 4642764

***********************

In the trace files we can clearly see that cost of table#5 messed up our total cost. Why is that?

***************

Now joining: TABLE5[TL2]#5

***************

NL Join

Outer table: Card: 32.67 Cost: 21.69 Resp: 21.69 Degree: 1 Bytes: 91

Inner table: TABLE5 Alias: TL2

Access Path: TableScan

Index: TABLE5_IDX3

resc_io: 20.00 resc_cpu: 360839

ix_sel: 3.9216e-04 ix_sel_with_filters: 3.9216e-04

NL Join: Cost: 153.86 Resp: 153.86 Degree: 1

Cost_io: 152.55 Cost_cpu: 18392646

Resp_io: 152.55 Resp_cpu: 18392646

****** finished trying bitmap/domain indexes ******

Best NL cost: 153.86

resc: 153.86 resc_io: 152.55 resc_cpu: 18392646

resp: 153.86 resp_io: 152.55 resp_cpu: 18392646

Join Card: 20168.28 = outer (32.67) * inner (1574304.00) * sel (3.9216e-04)

Join Card – Rounded: 20168 Computed: 20168.28

Index selectivity on TABLE5_IDX3 is very low (3.9216e-04) compared with the good plan (6.7020e-05).

Index selectivity

After looking into the Index selectivity, the question arises; why the Index selectivity is low?

B*TREE Indexes improve the performance of queries that select a small percentage of rows from a table. As a general guideline, we should create indexes on tables that are often queried for less than 15% of the table’s rows. This value may be higher in situations where all data can be retrieved from an index, or where the indexed columns can be used for joining to other tables.

The ratio of the number of distinct values in the indexed column / columns to the number of records in the table represents the selectivity of an index. The ideal selectivity is 1. Such selectivity can be reached only by unique indexes on NOT NULL columns.

Selectivity = Distinct Values / Total Number Rows

With this measure we can correlate the importance of the statistics in the objects. If we gather statistics only on the table, the total number of rows will change; leading to drastically change the Index selectivity. In the same manner, if we only gather statistics on the Index, the number of distinct values will change and the selectivity will dramatically change.

Apart of that, the accuracy of the statistics will also play an important role. The accurate the statistics, the accurate the Index selectivity.

Suggestions – How to fix this issue?

The suggestions came from the percept that Index selectivity is playing a bad game on this particular SQL_ID.

• As table and its indexes have a close relationship on how the CBO calculates the cost, we must gather statistics on the table and its indexes in the same time. This will avoid the Index selectivity to drastically change.

• When an index is rebuilt, we must gather statistics of the table too.

• Review the accuracy of the statistics. In some cases AUTO_SAMPLE_SIZE is not giving a good sample.

Some test on this SQL_ID have proven that gathering statistics to the TABLE5 table and its indexes with an estimate percent of 50%, plus invalidating the cursor immediately solved the issue.

The CBO was able to pick the good execution plan because the index selectivity was good enough.

Thanks,

Alfred

CRSCTL command examples

CRSCTL is an interface between you and Oracle Clusterware, parsing and calling

Oracle Clusterware APIs for Oracle Clusterware objects.

Oracle Clusterware 11g release 2 (11.2) introduces cluster-aware commands with

which you can perform check, start, and stop operations on the cluster. You can run

these commands from any node in the cluster on another node in the cluster, or on all

nodes in the cluster, depending on the operation.

You can use CRSCTL commands to perform several operations on Oracle Clusterware,

such as:

■ Starting and stopping Oracle Clusterware resources

■ Enabling and disabling Oracle Clusterware daemons

■ Checking the health of the cluster

■ Managing resources that represent third-party applications

■ Debugging Oracle Clusterware components

■ Integrating Intelligent Platform Management Interface (IPMI) with Oracle Clusterware to provide failure isolation support and to ensure cluster integrity

Some examples:

$ crs_stat -t
Name Type Target State Host
————————————————————
ora.DATA.dg ora….up.type ONLINE ONLINE ol5-…rac1
ora….ER.lsnr ora….er.type ONLINE ONLINE ol5-…rac1
ora….N1.lsnr ora….er.type ONLINE ONLINE ol5-…rac1
ora.asm ora.asm.type ONLINE ONLINE ol5-…rac1
ora.eons ora.eons.type ONLINE ONLINE ol5-…rac1
ora.gsd ora.gsd.type OFFLINE OFFLINE
ora….network ora….rk.type ONLINE ONLINE ol5-…rac1
ora.oc4j ora.oc4j.type OFFLINE OFFLINE
ora….SM1.asm application ONLINE ONLINE ol5-…rac1
ora….C1.lsnr application ONLINE ONLINE ol5-…rac1
ora….ac1.gsd application OFFLINE OFFLINE
ora….ac1.ons application ONLINE ONLINE ol5-…rac1
ora….ac1.vip ora….t1.type ONLINE ONLINE ol5-…rac1
ora….SM2.asm application ONLINE ONLINE ol5-…rac2
ora….C2.lsnr application ONLINE ONLINE ol5-…rac2
ora….ac2.gsd application OFFLINE OFFLINE
ora….ac2.ons application ONLINE ONLINE ol5-…rac2
ora….ac2.vip ora….t1.type ONLINE ONLINE ol5-…rac2
ora.ons ora.ons.type ONLINE ONLINE ol5-…rac1
ora.rac.db ora….se.type ONLINE ONLINE ol5-…rac2
ora….rts.svc ora….ce.type ONLINE ONLINE ol5-…rac1
ora.scan1.vip ora….ip.type ONLINE ONLINE ol5-…rac1

CRS_STAT is deprecated in 11.2, below is the new command line to check the status of the resources:

$ crsctl status resource -t

——————————————————————————–

NAME TARGET STATE SERVER STATE_DETAILS

——————————————————————————–

Local Resources

——————————————————————————–

ora.DATA.dg

ONLINE ONLINE ol5-112-rac1

ONLINE ONLINE ol5-112-rac2

ora.LISTENER.lsnr

ONLINE ONLINE ol5-112-rac1

ONLINE ONLINE ol5-112-rac2

ora.asm

ONLINE ONLINE ol5-112-rac1 Started

ONLINE ONLINE ol5-112-rac2 Started

ora.eons

ONLINE ONLINE ol5-112-rac1

ONLINE ONLINE ol5-112-rac2

ora.gsd

OFFLINE OFFLINE ol5-112-rac1

OFFLINE OFFLINE ol5-112-rac2

ora.net1.network

ONLINE ONLINE ol5-112-rac1

ONLINE ONLINE ol5-112-rac2

ora.ons

ONLINE ONLINE ol5-112-rac1

ONLINE ONLINE ol5-112-rac2

——————————————————————————–

Cluster Resources

——————————————————————————–

ora.LISTENER_SCAN1.lsnr

1 ONLINE ONLINE ol5-112-rac1

ora.oc4j

1 OFFLINE OFFLINE

ora.ol5-112-rac1.vip

1 ONLINE ONLINE ol5-112-rac1

ora.ol5-112-rac2.vip

1 ONLINE ONLINE ol5-112-rac2

ora.rac.db

1 ONLINE ONLINE ol5-112-rac2 Open

2 ONLINE ONLINE ol5-112-rac1 Open

ora.rac.reports.svc

1 ONLINE ONLINE ol5-112-rac2

ora.scan1.vip

1 ONLINE ONLINE ol5-112-rac1

In order to check the health of the cluster:

$ crsctl check cluster

CRS-4537: Cluster Ready Services is online

CRS-4529: Cluster Synchronization Services is online

CRS-4533: Event Manager is online

To stop all resources in the cluster:

$ crsctl stop resource -all
CRS-2500: Cannot stop resource ‘ora.gsd’ as it is not running
CRS-2500: Cannot stop resource ‘ora.oc4j’ as it is not running
CRS-2673: Attempting to stop ‘ora.LISTENER.lsnr’ on ‘ol5-112-rac1’
CRS-2673: Attempting to stop ‘ora.LISTENER.lsnr’ on ‘ol5-112-rac2’
CRS-2673: Attempting to stop ‘ora.eons’ on ‘ol5-112-rac2’
CRS-2673: Attempting to stop ‘ora.eons’ on ‘ol5-112-rac1’
CRS-2789: Cannot stop resource ‘ora.gsd’ as it is not running on server ‘ol5-112-rac1’
CRS-2673: Attempting to stop ‘ora.DATA.dg’ on ‘ol5-112-rac2’
CRS-2673: Attempting to stop ‘ora.rac.db’ on ‘ol5-112-rac2’
CRS-2789: Cannot stop resource ‘ora.gsd’ as it is not running on server ‘ol5-112-rac2’
CRS-2677: Stop of ‘ora.LISTENER.lsnr’ on ‘ol5-112-rac1’ succeeded
CRS-2673: Attempting to stop ‘ora.ons’ on ‘ol5-112-rac1’
CRS-2673: Attempting to stop ‘ora.LISTENER_SCAN1.lsnr’ on ‘ol5-112-rac1’
CRS-2673: Attempting to stop ‘ora.rac.reports.svc’ on ‘ol5-112-rac1’
CRS-2677: Stop of ‘ora.rac.reports.svc’ on ‘ol5-112-rac1’ succeeded
CRS-2673: Attempting to stop ‘ora.DATA.dg’ on ‘ol5-112-rac1’
CRS-2673: Attempting to stop ‘ora.rac.db’ on ‘ol5-112-rac1’
CRS-2673: Attempting to stop ‘ora.ol5-112-rac1.vip’ on ‘ol5-112-rac1’
CRS-2677: Stop of ‘ora.LISTENER.lsnr’ on ‘ol5-112-rac2’ succeeded
CRS-2673: Attempting to stop ‘ora.ons’ on ‘ol5-112-rac2’
CRS-2673: Attempting to stop ‘ora.ol5-112-rac2.vip’ on ‘ol5-112-rac2’
CRS-2677: Stop of ‘ora.ons’ on ‘ol5-112-rac1’ succeeded
CRS-2789: Cannot stop resource ‘ora.ons’ as it is not running on server ‘ol5-112-rac1’
CRS-2677: Stop of ‘ora.ol5-112-rac1.vip’ on ‘ol5-112-rac1’ succeeded
CRS-2677: Stop of ‘ora.ol5-112-rac2.vip’ on ‘ol5-112-rac2’ succeeded
CRS-2677: Stop of ‘ora.LISTENER_SCAN1.lsnr’ on ‘ol5-112-rac1’ succeeded
CRS-2673: Attempting to stop ‘ora.scan1.vip’ on ‘ol5-112-rac1’
CRS-2677: Stop of ‘ora.scan1.vip’ on ‘ol5-112-rac1’ succeeded
CRS-2673: Attempting to stop ‘ora.net1.network’ on ‘ol5-112-rac1’
CRS-2677: Stop of ‘ora.net1.network’ on ‘ol5-112-rac1’ succeeded
CRS-2677: Stop of ‘ora.ons’ on ‘ol5-112-rac2’ succeeded
CRS-2789: Cannot stop resource ‘ora.ons’ as it is not running on server ‘ol5-112-rac2’
CRS-2673: Attempting to stop ‘ora.net1.network’ on ‘ol5-112-rac2’
CRS-2677: Stop of ‘ora.net1.network’ on ‘ol5-112-rac2’ succeeded
CRS-2677: Stop of ‘ora.eons’ on ‘ol5-112-rac2’ succeeded
CRS-2677: Stop of ‘ora.eons’ on ‘ol5-112-rac1’ succeeded
CRS-2675: Stop of ‘ora.DATA.dg’ on ‘ol5-112-rac2’ failed
CRS-2675: Stop of ‘ora.DATA.dg’ on ‘ol5-112-rac1’ failed
CRS-2677: Stop of ‘ora.rac.db’ on ‘ol5-112-rac2’ succeeded
CRS-2677: Stop of ‘ora.rac.db’ on ‘ol5-112-rac1’ succeeded
CRS-4000: Command Stop failed, or completed with errors.

To start all resources registered in the cluster:

$ crsctl start resource -all

CRS-2501: Resource ‘ora.gsd’ is disabled

CRS-2501: Resource ‘ora.oc4j’ is disabled

CRS-2501: Resource ‘ora.gsd’ is disabled

CRS-2664: Resource ‘ora.asm’ is already running on ‘ol5-112-rac1’

CRS-2672: Attempting to start ‘ora.net1.network’ on ‘ol5-112-rac1’

CRS-2664: Resource ‘ora.asm’ is already running on ‘ol5-112-rac2’

CRS-2672: Attempting to start ‘ora.net1.network’ on ‘ol5-112-rac2’

CRS-2676: Start of ‘ora.net1.network’ on ‘ol5-112-rac2’ succeeded

CRS-2672: Attempting to start ‘ora.ons’ on ‘ol5-112-rac2’

CRS-2676: Start of ‘ora.net1.network’ on ‘ol5-112-rac1’ succeeded

CRS-2672: Attempting to start ‘ora.ons’ on ‘ol5-112-rac1’

CRS-2676: Start of ‘ora.ons’ on ‘ol5-112-rac2’ succeeded

CRS-2676: Start of ‘ora.ons’ on ‘ol5-112-rac1’ succeeded

CRS-2672: Attempting to start ‘ora.eons’ on ‘ol5-112-rac1’

CRS-2672: Attempting to start ‘ora.eons’ on ‘ol5-112-rac2’

CRS-2672: Attempting to start ‘ora.ol5-112-rac2.vip’ on ‘ol5-112-rac2’

CRS-2672: Attempting to start ‘ora.scan1.vip’ on ‘ol5-112-rac1’

CRS-2676: Start of ‘ora.scan1.vip’ on ‘ol5-112-rac1’ succeeded

CRS-2672: Attempting to start ‘ora.LISTENER_SCAN1.lsnr’ on ‘ol5-112-rac1’

CRS-2676: Start of ‘ora.LISTENER_SCAN1.lsnr’ on ‘ol5-112-rac1’ succeeded

CRS-2676: Start of ‘ora.ol5-112-rac2.vip’ on ‘ol5-112-rac2’ succeeded

CRS-2672: Attempting to start ‘ora.LISTENER.lsnr’ on ‘ol5-112-rac2’

CRS-2676: Start of ‘ora.eons’ on ‘ol5-112-rac2’ succeeded

CRS-2676: Start of ‘ora.eons’ on ‘ol5-112-rac1’ succeeded

CRS-2676: Start of ‘ora.LISTENER.lsnr’ on ‘ol5-112-rac2’ succeeded

CRS-2672: Attempting to start ‘ora.rac.db’ on ‘ol5-112-rac2’

CRS-2676: Start of ‘ora.rac.db’ on ‘ol5-112-rac2’ succeeded

CRS-2672: Attempting to start ‘ora.rac.reports.svc’ on ‘ol5-112-rac2’

CRS-2676: Start of ‘ora.rac.reports.svc’ on ‘ol5-112-rac2’ succeeded

CRS-5702: Resource ‘ora.rac.reports.svc’ is already running on ‘ol5-112-rac2’

CRS-4000: Command Start failed, or completed with errors.

Thanks

Basic Clusterware Administration Commands

In the previous blog we talk about Oracle Clusterware basics and definitions. Now let’s talk about basic administration commands.

olsnodes

The OLSNODES command provides the list of nodes and other information for all nodes participating in the cluster. The syntax for the OLSNODES command is:

olsnodes [-n] [-i] [-l] [-v] [-g] [-p]

If you issue the OLSNODES command without any command parameters, the command returns a listing of the nodes in the cluster:

# olsnodes
node1
node2
node3
node4

Table B-1 describes the options you can include on the OLSNODES command to obtain additional cluster-related information.

Table B-1 OLSNODES Command Options

Option	Description
`-g`	Logs cluster verification information with more details.
`-i`	Lists all nodes participating in the cluster and includes the Virtual Internet Protocol (VIP) address assigned to each node.
`-l`	Displays the local node name.
`-n`	Lists al nodes participating in the cluster and includes the assigned node numbers.
`-p`	Lists all nodes participating in the cluster and includes the private interconnect assigned to each node.
`-v`	Logs cluster verification information in verbose mode.

ocrcheck

OCRCHECK utility is used to verify the OCR integrity. The OCRCHECK utility displays the version of the OCR’s block format, total space available and used space, OCRID, and the OCR locations that you have configured. OCRCHECK performs a block-by-block checksum operation for all of the blocks in all of the OCRs that you have configured. It also returns an individual status for each file as well as result for the overall OCR integrity check. The following is a sample of the OCRCHECK output:

# ./ocrcheck

Status of Oracle Cluster Registry is as follows :

Version : 3

Total space (kbytes) : 262120

Used space (kbytes) : 2528

Available space (kbytes) : 259592

ID : 1197072699

Device/File Name : +DATA

Device/File integrity check succeeded

Device/File not configured

Device/File not configured

Device/File not configured

Device/File not configured

Cluster registry integrity check succeeded

Logical corruption check succeeded

Note: Run this command as root in order to enable the Cluster registry integrity check, if you run this command with a non-privileged user this will be bypassed.

ocrcheck -local

In Oracle Clusterware 11g release 2 (11.2), each node in a cluster has a local registry for node-specific resources, called an Oracle Local Registry (OLR), that is installed and configured when Oracle Clusterware installs OCR. Multiple processes on each node have simultaneous read and write access to the OLR particular to the node on which they reside, regardless of whether Oracle Clusterware is running or fully functional.

By default, OLR is located at Grid_home/cdata/host_name.olr on each node. Manage OLR using the OCRCHECK, OCRDUMP, and OCRCONFIG utilities as root with the -local option.

# ./ocrcheck -local
Status of Oracle Local Registry is as follows :
Version : 3
Total space (kbytes) : 262120
Used space (kbytes) : 2184
Available space (kbytes) : 259936
ID : 1563540278
Device/File Name : /u01/app/11.2.0/grid/cdata/ol5-112-rac1.olr
Device/File integrity check succeeded

Local registry integrity check succeeded

Logical corruption check succeeded

crsctl

CRSCTL is an interface between you and Oracle Clusterware, parsing and calling Oracle Clusterware APIs for Oracle Clusterware objects.

Oracle Clusterware 11g release 2 (11.2) introduces cluster-aware commands with which you can perform check, start, and stop operations on the cluster. You can run these commands from any node in the cluster on another node in the cluster, or on all nodes in the cluster, depending on the operation.

CRSCTL documentation is quite extensive and can be found in the below link:
(http://docs.oracle.com/cd/E11882_01/rac.112/e16794/crsref.htm)

CRSCTL can be also used to check the state of the Voting disks:

# ./crsctl query css votedisk
## STATE File Universal Id File Name Disk group
— —– —————– ——— ———
1. ONLINE 911b058da40b4f96bf456abb771dddc8 (/dev/oracleasm/disks/DISK1) [DATA]
Located 1 voting disk(s).

Thanks,
Alfred

Oracle Clusterware 11gR2 Basics

In this blog will talk about Oracle Clusterware basics. It is really needed to know the basics before we can start talking about Clusterware installation, configuration and administration. The below information is an extract from the Oracle Clusterware, Administration and Deployment Guide 11g Release 2 (11.2) (http://docs.oracle.com/cd/E14072_01/rac.112/e10717.pdf).

What is Oracle Clusterware?

Oracle Clusterware enables servers to communicate with each other, so that they
appear to function as a collective unit. This combination of servers is commonly
known as a cluster. Although the servers are standalone servers, each server has
additional processes that communicate with other servers. In this way the separate
servers appear as if they are one system to applications and end users.
Oracle Clusterware provides the infrastructure necessary to run Oracle Real
Application Clusters (Oracle RAC). Oracle Clusterware also manages resources, such
as virtual IP (VIP) addresses, databases, listeners, services, and so on. These resources
are generally named ora.resource_name.host_name. Oracle does not support
editing these resources except under the explicit direction of Oracle support.
Additionally, Oracle Clusterware can help you manage your applications.

Oracle Clusterware has two stored components, besides the binaries: The voting disk
files, which record node membership information, and the Oracle Cluster Registry
(OCR), which records cluster configuration information. Voting disks and OCRs must
reside on shared storage available to all cluster member nodes.

Oracle Clusterware uses voting disk files to provide fencing and cluster node
membership determination. The OCR provides cluster configuration information. You can place the Oracle Clusterware files on either Oracle ASM or on shared common
disk storage. If you configure Oracle Clusterware on storage that does not provide file
redundancy, then Oracle recommends that you configure multiple locations for OCR
and voting disks. The voting disks and OCR are described as follows:

■ Voting Disks
Oracle Clusterware uses voting disk files to determine which nodes are members
of a cluster. You can configure voting disks on Oracle ASM, or you can configure
voting disks on shared storage.
If you configure voting disks on Oracle ASM, then you do not need to manually
configure the voting disks. Depending on the redundancy of your disk group, an
appropriate number of voting disks are created.
If you do not configure voting disks on Oracle ASM, then for high availability,
Oracle recommends that you have a minimum of three voting disks on physically
separate storage. This avoids having a single point of failure. If you configure a
single voting disk, then you must use external mirroring to provide redundancy.
You should have at least three voting disks, unless you have a storage device, such
as a disk array that provides external redundancy. Oracle recommends that you do
not use more than five voting disks. The maximum number of voting disks that is
supported is 15.

■ Oracle Cluster Registry
Oracle Clusterware uses the Oracle Cluster Registry (OCR) to store and manage
information about the components that Oracle Clusterware controls, such as
Oracle RAC databases, listeners, virtual IP addresses (VIPs), and services and any
applications. The OCR stores configuration information in a series of key-value
pairs in a tree structure. To ensure cluster high availability, Oracle recommends
that you define multiple OCR locations (multiplex). In addition:
– You can have up to five OCR locations
– Each OCR location must reside on shared storage that is accessible by all of the
nodes in the cluster
– You can replace a failed OCR location online if it is not the only OCR location
– You must update the OCR through supported utilities such as Oracle
Enterprise Manager, the Server Control Utility (SRVCTL), the OCR
configuration utility (OCRCONFIG), or the Database Configuration Assistant
(DBCA)

Oracle Clusterware Processes on Linux and UNIX Systems

Oracle Clusterware processes on Linux and UNIX systems include the following:

■ crsd: Performs high availability recovery and management operations such as
maintaining the OCR and managing application resources. This grid infrastructure
process runs as root and restarts automatically upon failure.

When you install Oracle Clusterware in a single-instance database environment
for Oracle ASM and Oracle Restart, ohasd manages application resources and
crsd is not used.

■ cssdagent: Starts, stops, and checks the status of the CSS daemon, ocssd. In
addition, the cssdagent and cssdmonitor provide the following services to
guarantee data integrity:
– Monitors the CSS daemon; if the CSS daemon stops, then it shuts down the
node
– Monitors the node scheduling to verify that the node is not hung, and shuts
down the node on recovery from a hang.
oclskd: (Oracle Clusterware Kill Daemon) CSS uses this daemon to stop
processes associated with CSS group members for which stop requests have come
in from other members on remote nodes.

■ ctssd: Cluster time synchronization service daemon: Synchronizes the time on all
of the nodes in a cluster to match the time setting on the master node but not to an
external clock.

■ diskmon (Disk Monitor daemon): Monitors and performs I/O fencing for HP
Oracle Exadata Storage Server storage. Because Exadata storage can be added to
any Oracle RAC node at any time, the diskmon daemon is always started when
ocssd starts.

■ evmd (Event manager daemon): Distributes and communicates some cluster
events to all of the cluster members so that they are aware of changes in the
cluster.

evmlogger (Event manager logger): This is started by EVMD at startup. This
reads a configuration file to determine what events to subscribe to from EVMD
and it runs user defined actions for those events. This facility maintains backward
compatibility only.

■ gpnpd (Grid Plug and Play daemon): Manages distribution and maintenance of
the Grid Plug and Play profile containing cluster definition data.

■ mdnsd (Multicast Domain Name Service daemon): Manages name resolution and
service discovery within attached subnets.

■ ocssd (Cluster Synchronization Service daemon): Manages cluster node
membership and runs as the oracle user; failure of this process results in a node
restart.

■ ohasd (Oracle High Availability Services daemon): Starts Oracle Clusterware
processes and also manages the OLR and acts as the OLR server.

In a cluster, ohasd runs as root. However, in an Oracle Restart environment,
where ohasd manages application resources, it runs as the oracle user

Next blog -> Basic Clusterware Administration Commands

Thanks,
Alfred

Cleanup After Failed Installation Oracle Clusterware 11gR2

In this blog I will talk about how to cleanup a failed installation of the Oracle Clusterware 11gR2 in one particular node. I was installing the new Oracle Clusterware 11.2.0.1 in my home lab by follwing Tom Kyte’s instructions (http://www.oracle-base.com/articles/11g/oracle-db-11gr2-rac-installation-on-oel5-using-virtualbox.php#install_grid_infrastructure), then went through the part that the installer GUI needs us to run the root.sh scripts.

The easy thought was to run the root.sh script in both nodes of my RAC (RAC1 & RAC2) at the same time, without reading the explicit instructions “Run the script on the local node first. After successful completion, you can run the script in parallel on all other nodes.”:

The script run successful in the RAC1 node but in the RAC2 node. So when the script finished in RAC1 tried to run it again in RAC2, but the same results “the output was that the script was already ran”.

What to do next?, do I have to start over from scratch?

Surfing the web found a good & useful article from Guenadi Jilevski (http://gjilevski.com/2010/08/12/how-to-clean-up-after-a-failed-11g-crs-install-what-is-new-in-11g-r2-2/), here shows how to perform a manual cleanup in 11gR1, but also shows the new features and scripts in 11gR2.

Summarized steps:

Deconfigure Oracle Clusterware 11.2.x.x without removing the binaries:

# cd $GRID_HOME/crs/install

Run rootcrs.pl with the -deconfig -force flags on the node you have the issue.

# perl rootcrs.pl -deconfig -force

If you are deconfiguring Oracle Clusterware on all the nodes in the cluster, then you have to add the -lastnode flag on the last one in order to deconfigure OCR and Voting disks.

# perl rootcrs.pl -deconfig -force -lastnode

After these steps, run the root.sh script again. This time it will smoothly run.

You should also want to read the complete OSS note:

How to Deconfigure/Reconfigure(Rebuild OCR) or Deinstall Grid Infrastructure [ID 1377349.1]

Thanks,

Alfredo

Historical SQL Statistics And Execution Plan Change

How many times have you received user’s phone call stating that the DB is slow?, well sometimes this has nothing to do with the DB’s overall performance itself, but with a user’s query.

Now the question is, is this query really slow?, the best way to find this out is comparing the actual execution time with the ones in the past. Here’s a SQL statement which search inside AWR repository (Caution!!! a special license is required!!!), it compares the execution plan of all SQL_ID’s against the previous snapshot.

awr_planchanges.sql

prompt enter the number of days in the past to scan
SET LINES 500
SELECT A.SNAP_ID,
BEGIN_INTERVAL_TIME,
SQL_ID,
HASH_VALUE1,
HASH_VALUE2,
VALUE1 as “ELAPSED TIME PER EXEC 1” ,
VALUE2 as “ELAPSED TIME PER EXEC 2”,
ROUND (CHANGE_PERCENT) as “CHANGE PERCENT”
FROM ( SELECT SNAP_ID,
SQL_ID,
SUM (pvalue1) HASH_VALUE1,
SUM (pvalue2) HASH_VALUE2,
SUM (value1) VALUE1,
SUM (value2) VALUE2,
(SUM (VALUE1) + 1) * 100 / (SUM (VALUE2) + 1)
AS CHANGE_PERCENT
FROM (SELECT snap_id,
0 AS snap2,
sql_id,
plan_hash_value AS pvalue1,
0 AS pvalue2,
ROUND (
elapsed_time_delta / executions_delta / 1000000,
3)
AS value1,
0 AS value2
FROM dba_hist_sqlstat sql
WHERE executions_delta > 0
UNION
SELECT snap_id + 1,
snap_id AS snap2,
sql_id,
0 AS pvalue1,
plan_hash_value AS pvalue2,
0 AS vaule,
ROUND (
elapsed_time_delta / executions_delta / 1000000,
3)
AS value2
FROM dba_hist_sqlstat sql
WHERE executions_delta > 0)
GROUP BY SNAP_ID, SQL_ID) A,
dba_hist_snapshot B
WHERE A.SNAP_ID = B.SNAP_ID
AND HASH_VALUE1 > 0
AND HASH_VALUE2 > 0
AND HASH_VALUE1 != HASH_VALUE2
AND BEGIN_INTERVAL_TIME > SYSDATE – (&days)
ORDER BY A.SNAP_ID
/

This is the output of the script:

SNAP_ID BEGIN_INTERVAL_TIME SQL_ID HASH_VALUE1 HASH_VALUE2 SEC PER EXE 1 SEC PER EXE 2 CHANGE %
——– ———————– ————- ———– ———– ————- ————- ——–
9547 01-OCT-12 01.00.07.841 AM 4urszd9dt9fjv 4862331523 891004645 .015 .007 101
9585 02-OCT-12 03.00.41.798 PM 4urszd9dt9fjv 4862331523 891004645 .009 .003 101
9586 02-OCT-12 04.00.36.393 PM 4urszd9dt9fjv 891004645 4862331523 .006 .009 100
9587 02-OCT-12 05.00.25.306 PM 1k30v0pyg32vu 414828074 878600859 .179 .157 102
9587 02-OCT-12 05.00.25.306 PM dsm86bzuqtd2r 2452407222 3005749068 .048 1.37 44
9616 03-OCT-12 10.00.34.499 PM 4urszd9dt9fjv 4862331523 891004645 .018 .023 100
9621 04-OCT-12 03.00.06.979 AM 4urszd9dt9fjv 891004645 4862331523 .03 .027 100
9640 04-OCT-12 09.00.40.250 PM 4urszd9dt9fjv 4862331523 891004645 .042 .012 103
9641 04-OCT-12 10.00.22.954 PM dfmu8nm1cscx7 3810296266 4308029399 .938 .703 114
9710 07-OCT-12 07.00.09.269 PM 4urszd9dt9fjv 4862331523 891004645 .046 .015 103
9750 09-OCT-12 11.00.59.162 AM 1k30v0pyg32vu 1634868183 414828074 .16 .155 100
9758 09-OCT-12 07.00.10.659 PM b70xavb9wv27v 1111647858 4256287279 5.937 6.475 93

12 rows selected.

You can clearly see how the HASH_VALUE changed and the execution time as well. If the CHANGE % is above 100% means that execution time decreased, on the other hand CHANGE % below 100% means the execution time increased.

ORAganism blog is having a really good script which searches by SQL_ID.

http://oraganism.wordpress.com/2011/12/14/a-dba_hist_sqlstat-query-that-i-am-very-fond-of/

Happy troubleshooting,
Alfred

Same post in Spanish here!