A! Help

Upgrading Grid Control 11g to 12c - 2

2012-02-11T11:32:00.000-08:00

Earlier post showed the installing of pre-upgrade patch and switching agents to 12c. This post shows the rest of the upgrade steps which is upgrading of OMS and deinstalling the old OMS and agents.

Download the new Grid control software, copy the downloaded files to the host where grid control is currently installed and unzip the two files.

Shutdown the OMS with

emctl stop oms

It is important to stop it without the "-all" option. If the OMS is stopped with "-all" option then following warning will appear during the upgrade.

Another thing that can come up is the following error.

To fix this start the OMS and run

emctl config emkey -copy_to_repos -sysman_pwd gridb321
Oracle Enterprise Manager 11g Release 1 Grid Control
Copyright (c) 1996, 2010 Oracle Corporation.  All rights reserved.
The EMKey has been copied to the Management Repository. This operation will cause the EMKey to become unsecure.
After the required operation has been completed, secure the EMKey by running "emctl config emkey -remove_from_repos".

After this stop the oms again (without the -all option) and rerun the installer.

There other warnings and errors where some would allow to continue whereas others would require some steps to correct these warnings.

To fix these warnings

SQL> grant execute on dbms_random to dbsnmp;
Grant succeeded.

SQL> grant execute on dbms_random to sysman;
Grant succeeded.

SQL> select account_status,username from dba_users where username='ORACLE_OCM';

ACCOUNT_STATUS     USERNAME
------------------ -----------
EXPIRED & LOCKED   ORACLE_OCM

SQL> alter user oracle_ocm account unlock;
User altered.

SQL> alter user oracle_ocm identified by ******;
User altered.

SQL> select * from v$log;

    GROUP#    THREAD#  SEQUENCE#      BYTES    MEMBERS ARC STATUS           FIRST_CHANGE# FIRST_TIM
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ---------
         1          1        376   52428800          2 YES INACTIVE               6438727 10-FEB-12
         2          1        377   52428800          2 YES INACTIVE               6452498 10-FEB-12
         3          1        378   52428800          2 NO  CURRENT                6483486 10-FEB-12

SQL> alter database drop logfile group 1; -- do for other groups as well
Database altered.

SQL> alter database add logfile group 1 size 300M;
Database altered.

SQL>  select * from v$log;

    GROUP#    THREAD#  SEQUENCE#      BYTES    MEMBERS ARC STATUS           FIRST_CHANGE# FIRST_TIM
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ---------
         1          1        379  314572800          2 NO  CURRENT                6521242 10-FEB-12
         2          1          0  314572800          2 YES UNUSED                       0
         3          1          0  314572800          2 YES UNUSED                       0

Some steps (eg step 5) in the install process take longer to complete and at times there will be movement on the progress bar. It is not stuck and it will help to keep and eye on the logs as well. Logs for installation is available in Middleware12/oms/sysman/logs (Middleware12 is directory for new installation). As part of the install WebLogic server 10.3.5 will be installed.

# /opt/app/oracle/Middleware12/oms/allroot.sh

Starting to execute allroot.sh .........

Starting to execute /opt/app/oracle/Middleware12/oms/root.sh ......
Running Oracle 11g root.sh script...

The following environment variables are set as:
    ORACLE_OWNER= oracle
    ORACLE_HOME=  /opt/app/oracle/Middleware12/oms

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

Entries will be added to the /etc/oratab file as needed by
Database Configuration Assistant when a database is created
Finished running generic part of root.sh script.
Now product-specific root actions will be performed.
/etc exist
/opt/app/oracle/Middleware12/oms
Finished execution of  /opt/app/oracle/Middleware12/oms/root.sh ......

At the end of the upgrade the 12c agents switched earlier can now communicate with the OMS. Checking the status of the agents shows

emctl  status agent
Oracle Enterprise Manager 12c Cloud Control 12.1.0.1.0
Copyright (c) 1996, 2011 Oracle Corporation.  All rights reserved.
---------------------------------------------------------------
Agent Version     : 12.1.0.1.0
OMS Version       : 12.1.0.1.0
Protocol Version  : 12.1.0.1.0
Agent Home        : /opt/app/oracle/grid_agent/agent12g/agent_inst
Agent Binaries    : /opt/app/oracle/grid_agent/agent12g/core/12.1.0.1.0
Agent Process ID  : 4072
Parent Process ID : 3935
Agent URL         : https://rac1. .net:3872/emd/main
Repository URL    : https://hpc4. .net:4900/empbs/upload
Started at        : 2012-02-09 16:26:48
Started by user   : oracle
Last Reload       : (none)
Last successful upload                       : 2012-02-10 12:59:01
Last attempted upload                        : 2012-02-10 12:59:01
Total Megabytes of XML files uploaded so far : 2.44
Number of XML files pending upload           : 0
Size of XML files pending upload(MB)         : 0
Available disk space on upload filesystem    : 13.31%
Collection Status                            : Collections enabled
Last attempted heartbeat to OMS              : 2012-02-10 13:02:12
Last successful heartbeat to OMS             : 2012-02-10 13:02:12

---------------------------------------------------------------
Agent is Running and Ready

Status from old agent shows it's running but not communicating with OMS anymore

emctl status agent
Oracle Enterprise Manager 11g Release 1 Grid Control 11.1.0.1.0
Copyright (c) 1996, 2010 Oracle Corporation.  All rights reserved.
---------------------------------------------------------------
Content-Length: 0
Server: Jetty(7.0.1.v20091125)

---------------------------------------------------------------
Agent is Running but Not Ready

This completes the upgrade part of grid control from 11g

to 12c

12c grid control Home page.

It could be seen that agents from exisiting hosts have already started communicating with new OMS.

12c's new ASH Analytics page

Last step is to deinstall the old agents and OMS.
To deinstall the agents from local and remote hosts including RAC follow the agent deinstall post or How to Remove the 11.1.0.1.0 Grid Control Management Agent with the Silent Method [ID 1068036.1].

For OMS use How to Deinstall 11g Grid Control Components in Graphical Mode

More inforation is available from Enterprise Manager Grid Control Advanced Installation and Configuration Guide

To enable/disable the auto start of any agent

chkconfig gcstartup on/off
chkconfig --list | grep gcstartup
gcstartup       0:off   1:off   2:on    3:on    4:on    5:on    6:off

Useful metalink notes
How to Remove the 11.1.0.1.0 Grid Control Management Agent with the Silent Method [ID 1068036.1]
12c: Cluster Database Target Remains in Pending Delete Status Indefinetely after Re-adding [ID 1403833.1]
12C Cloud Control: How to De-install 12C Agent [ID 1368088.1]
How To De-Install EM 12c Cloud Control [ID 1363418.1]
How to Delete One Node from a RAC Cluster in EM 12c? [ID 1391107.1]
How to Deploy the Latest Database Plugin to the OMS and the Agent in 12C Cloud Control [ID 1388143.1]
EM 12c: How to Install EM 12c Agent using Silent Install Method with Response File [ID 1360083.1]
EM 12c: How to Install Enterprise Manager 12.1.0.1 Using Silent Method [ID 1361643.1]
How to install Enterprise Manager Cloud Control 12c Agent on Linux [ID 1360183.1]

Upgrading Grid Control 11g to 12c - 1

2012-02-11T10:20:00.000-08:00

This blog upgrades the earlier installed Grid Control 11g.

There are three approaches to upgrade as per Enterprise Manager Cloud Control Upgrade Guide (direct quote)
"1-system upgrade, understandably, must always be done on the same platform as the earlier release of the Enterprise Manager system because you are upgrading on the same host.

2-system upgrade is possible on the same platform as the earlier release of the Enterprise Manager system, and from Linux (32-bit) to Linux (64-bit) (or vice versa) because you are upgrading on a remote host.

1-system upgrade on a different host is possible on the same platform as the earlier release of the Enterprise Manager system, and from Linux (32-bit) to Linux (64-bit) (or vice versa) because you are upgrading on a remote host, much like 2-system upgrade approach.".

Comparison of the three approaches.

This blog uses 1-system upgrade on the same host path. The upgrade consists of four main steps. Installing the pre-upgrade patch which allows the agent upgrade. Upgrade of agents and then finally grid control upgrade and post-upgrade steps. Oracle documentation provides a detail breakdown of these steps.

There are few things to know about the upgrade.

Upgrades do not upgrade the database, if the existing database is certified for grid control see 412431.1 then it shouldn't be an issue. For this DB is 11.1.0.7.0.

12c Agents communicate only with 12c OMS so agents are upgrade before OMS and time between agent upgrade and OMS upgrade there won't be any communication between agent and OMS and there won't be any monitoring.

Any agent could be upgrades as long as 12c management software is available.

Do not upgrade EM cloud control on a NFS mounted drive.

EM cloud control will install jdk 1.6_24 and Oracle WebLogic Server 11g Release 1 (10.3.5) if these are not present. For this existing web logic version is 10.3.2
and jdk 1.6_30

Earlier agents ports are carried forward with the upgrade.

Get the pre-upgrade patch and apply it to OMS.

Oracle documents mention the pre-upgrade patch as 10044087 but this is now replaced with patch 13503844. So if the the download link prompt this patch it is fine. Follow the readme.txt in this patch bundle to install it. One of the pre-req for this patch is "It is Mandatory to have PSU1 (10065631) or later applied on your environment."

Metalink note How to Determine the List of Patch Set Update(PSU) Applied to the Enterprise Manager OMS and Agent Oracle Homes? [ID 1358092.1] shows the latest patches available for OMS and agents for both unix and windows. At the time of this blog post the latest patch was 13248190 for OMS and 9346243 for Agent. So those were applied. Database had PSU 11.1.0.7.5 applied.

Following metalink notes may help with PSU patch applying issues.
Troubleshooting the Improved Patching Solution for Grid Control 11.1GC Management Server [ID 1326515.1]
Grid Control 11g: post_install script for PSU 4 Patch 12423703 fails with error for "MGMT_SET_JOB_Q_PROC" [ID 1362945.1]
Grid Control 11g: Post Install Script For PSU Patch 12423703 gives Error: "ORA-20110:Unexpected error executing MGMT_SET_JOB_Q_PROC" [ID 1359157.1]
Applying Post Install Scripts For Patch Getting Error: "ORA-00068: invalid value 0 for parameter job_queue_processes" [ID 1375352.1]

Once the PSU patches applied it's time to apply the pre-upgrade console patch 13503844.

13503844]$ /opt/app/oracle/Middleware/oms11g/OPatch/opatch apply
Oracle Interim Patch Installer version 11.1.0.9.4
Copyright (c) 2011, Oracle Corporation. All rights reserved.

Oracle Home : /opt/app/oracle/Middleware/oms11g
Central Inventory : /opt/app/oraInventory
from : /opt/app/oracle/Middleware/oms11g/oraInst.loc
OPatch version : 11.1.0.9.4
OUI version : 11.1.0.8.0
Log file location : /opt/app/oracle/Middleware/oms11g/cfgtoollogs/opatch/13503844_Feb_09_2012_10_56_45/apply2012-02-09_10-56-44AM_1.log

OPatch detects the Middleware Home as "/opt/app/oracle/Middleware"

OPatch will do the following:
[Oracle Home discovery] : Configure and Validate Oracle Home info.
[Prerequisite for apply] : Invoke prerequisites to see if patch can be applied.
[Patch conflict detection for apply patch] : Check if any conflict with already installed patches in Oracle Home.

Applying interim patch '13503844' to OH '/opt/app/oracle/Middleware/oms11g'
Verifying environment and performing prerequisite checks...
To continue, OPatch will do the following:
[Back up for restore] : Back up 68 files for Oracle Home restore in case of patching error.
[Back up for rollback] : Back up 39 files for future patch rollback.
[Patch Oracle Home for apply] : Copy 39 file(s) to Oracle Home;
Archive 4 jar file(s) with 254 class file(s).
[Update inventory for apply] : Modify the inventory to record patch [13503844].

Do you want to proceed? [y|n]
y
User Responded with: Y
All checks passed.
Provide your email address to be informed of security issues, install and
initiate Oracle Configuration Manager. Easier for you if you use your My
Oracle Support Email address/User Name.
Visit http://www.oracle.com/support/policies.html for details.
Email address/User Name:

You have not provided an email address for notification of security issues.
Do you wish to remain uninformed of security issues ([Y]es, [N]o) [N]: y
Backing up files...

Patching component oracle.sysman.oms.core, 11.1.0.1.0...

Verifying the update...
Patch 13503844 successfully applied
Log file location: /opt/app/oracle/Middleware/oms11g/cfgtoollogs/opatch/13503844_Feb_09_2012_10_56_45/apply2012-02-09_10-56-44AM_1.log

OPatch succeeded.

13503844]$ $ORACLE_HOME/bin/rcuJDBCEngine sys/gridb@192.168.0.76:1521:gridb JDBC_SCRIPT $ORACLE_HOME/sysman/preupgc/puc_dblink_pkgdef.sql
Completed SQL script execution normally.
1 scripts were processed

13503844]$ $ORACLE_HOME/bin/rcuJDBCEngine sys/gridb@192.168.0.76:1521:gridb JDBC_SCRIPT $ORACLE_HOME/sysman/preupgc/puc_dblink_pkgbody.sql
Completed SQL script execution normally.
1 scripts were processed

13503844]$ $ORACLE_HOME/bin/rcuJDBCEngine sysman/gridb321@192.168.0.76:1521:gridb JDBC_SCRIPT $ORACLE_HOME/sysman/preupgc/pre_upg_console.sql
l_target_guids.COUNT : 4

oracle.sysman.beacon SMPADDON 11.1.0.1.0 Oracle Beacon Plugin
oracle.sysman.emrep SMPADDON 11.1.0.1.0 Oracle Management And Repository Plugin
oracle.sysman.oh SMPADDON 11.1.0.1.0 Oracle OracleHome
oracle.sysman.csa SMPADDON 11.1.0.1.0 Oracle Client System Analyzer
oracle.sysman.db SMPADDON 11.1.0.1.0 Oracle Database Plugin
oracle.sysman.emas SMPADDON 11.1.0.1.0 Oracle Fusion Middleware Plugin
oracle.sysman.empa ADDON 11.1.0.1.0 Oracle Siebel Plugin
oracle.sysman.vt ADDON 11.1.0.1.0 Oracle Virtualization Plugin
oracle.fmw.bhv ADDON 11.1.0.1.0 Oracle Beehive Plugin

Completed SQL script execution normally.
24 scripts were processed

When the patch is applied it add a link in the deployment tab page under upgrade topic.
Below is a screenshot of this page before pre-upgrade patch is applied.

Below is a screenshot of the same page after the pre-upgrade console patch applied.

This link will allow navigation into upgrade console page which has option to chose the upgrade type, agent upgrade status and over view page describing the steps in the upgrade. Below is the screenshot of the upgrade console page.

The overview link on this page gives information about each upgrade approach. Select the upgrade approach and click on the overview link. Below is screenshot of 1-system on same host and 1-system on different host overview page.

Download agent core plugin and other needed plugins and copy these files into a directory in the host where grid control is running.

Below is the list of downloaded plugins.

-rw-r--r-- 1 oracle oinstall    495016 Feb  9 11:58 12.1.0.1.0_oracle.sysman.oh_2000_0.opar
-rw-r--r-- 1 oracle oinstall    499849 Feb  9 11:58 12.1.0.1.0_oracle.sysman.csa_2000_0.opar
-rw-r--r-- 1 oracle oinstall    297231 Feb  9 11:58 12.1.0.1.0_oracle.sysman.emrep_2000_0.opar
-rw-r--r-- 1 oracle oinstall   3925371 Feb  9 11:58 12.1.0.1.0_oracle.em.soee_2000_0.opar
-rw-r--r-- 1 oracle oinstall 230699506 Feb  9 11:59 12.1.0.1.0_AgentCore_226.zip
-rw-r--r-- 1 oracle oinstall 223979520 Feb  9 11:59 12.1.0.1.0_AgentCore_46.zip
-rw-r--r-- 1 oracle oinstall 621251038 Feb  9 11:59 12.1.0.1.0_oracle.sysman.db_2000_0.opar
-rw-r--r-- 1 oracle oinstall 106452968 Feb  9 12:08 12.1.0.1.0_oracle.sysman.beacon_2000_0.opar
-rw-r--r-- 1 oracle oinstall 440206388 Feb  9 12:17 12.1.0.1.0_oracle.sysman.emas_2000_0.opar

Select 1-system upgrade type and then click mange software link in the upgrade console page. This will show the agent upgradeability. without the above plugin being discovered agents will be shown as unable to upgrade. Below screenshots show the current state of agent upgradeability as no plugin or agent software is validated.

Give the directory location of the software and validate.

When the plugins and agent software is validate all the agents become upgradable. Resolve any issues, missing plugins and re-validate before continuing.

As part of the validate process plugins will be extracted into plugin_bin folder.

plugin_bin]$ ls -lrt
drwxr----- 3 oracle oinstall  4096 Feb  9 12:18 oracle.sysman.oh
drwxr----- 3 oracle oinstall  4096 Feb  9 12:18 oracle.sysman.emrep
drwxr----- 3 oracle oinstall  4096 Feb  9 12:18 oracle.sysman.emas
drwxr----- 3 oracle oinstall  4096 Feb  9 12:18 oracle.em.soee
drwxr----- 3 oracle oinstall  4096 Feb  9 12:18 oracle.sysman.beacon
drwxr----- 3 oracle oinstall  4096 Feb  9 12:18 oracle.sysman.db
drwxr----- 3 oracle oinstall  4096 Feb  9 12:19 oracle.sysman.csa

Clicking on the upgradable link next to each green pie chart will show the upgradeability of each agent and each target.

Once all agents are upgradeable click on "Agent Upgrade Status" link which is under "Other Links" in upgrade console page.At this stage all the status should be not started.

Click deploy and configure agents button. Multiple agents could be selected to be deployed and configured but this require host and agent credentials if these are not set then oracle and root credentials must be same for all hosts. Here agents are deployed and configured one at a time. Use credentials, selecting sudo gave error "sudo is not present".

Select the host deploy and configure the new agent.

Clicking the deploy and configure button open up the page below which allows to specify the new directory for agent host credentials.

Click continue and give root user credentials.

When the submit button is clicked it will start the deploy and configure job.

As said earlier it is also possible to deploy and configure on multiple hosts. This is useful in RAC environments.

When the job has finished the status will be shown. If job fails inspect the logs which could be accessed through the links given as steps in the same page.

Once the agent deployment is successful run agent readiness check.

At the end of agent readiness check configuration and deployment status will be success.

At the end agent readiness check Health Check Status column has "report pending verification". This requires manually verifying it is alright to proceed. Click on view and verify health check report button and inspect the report and confirm if it is error free.

After this agent is ready for the switch.

This where the down time starts. When switch agent is started the current 11g agent will be stopped and disabled. The 12c new agent will be started but cannot report to 11g OMS until it's upgrade. Therefore until the OMS is upgraded there will be no monitoring. To keep this unmonitored time to a minimum carry out the steps before "switch agent" all the agents. Considering the agents listed in this blog agent on host hpc1 will not be switched until hpc4 and rac1 and rac2 all have completed their agent deployments and health checks.
Agent switch could be done one agent a time or multiple agents at the same time. Below are the screenshots from switching agents on all the RAC nodes at the same time.

Agent upgrade status after all the agents are switched.

When all the agents are switched to 12c agents and monitoring has effectively halted until OMS is upgraded. Status of the new agent states its up and ready but still unable to communicate with OMS.

./emctl status agent
Oracle Enterprise Manager 12c Cloud Control 12.1.0.1.0
Copyright (c) 1996, 2011 Oracle Corporation.  All rights reserved.
---------------------------------------------------------------
Agent Version     : 12.1.0.1.0
OMS Version       : (unknown)
Protocol Version  : 12.1.0.1.0
Agent Home        : /opt/app/oracle/grid_agent/agent12g/agent_inst
Agent Binaries    : /opt/app/oracle/grid_agent/agent12g/core/12.1.0.1.0
Agent Process ID  : 4072
Parent Process ID : 3935
Agent URL         : https://rac1.   .net:3872/emd/main
Repository URL    : https://hpc4.   .net:4900/empbs/upload
Started at        : 2012-02-09 16:26:48
Started by user   : oracle
Last Reload       : (none)
Last successful upload                       : (none)
Last attempted upload                        : (none)
Total Megabytes of XML files uploaded so far : 0
Number of XML files pending upload           : 82
Size of XML files pending upload(MB)         : 0.54
Available disk space on upload filesystem    : 13.59%
Collection Status                            : Collections enabled
Last attempted heartbeat to OMS              : 2012-02-09 16:32:58
Last successful heartbeat to OMS             : (none)

---------------------------------------------------------------
Agent is Running and Ready

To keep the un-monitored time to a minimum begin the OMS upgrade soon after the agent switch.

Part 2 shows the OMS upgrade.

Useful metalink notes

Upgrade Advisor: Enterprise Manager 1-System Upgrade from 11.1.0.1 to 12.1.0.1 [ID 328.1]
12c: Upgrading the 11.1.0.1 Enterprise Manager on Linux x86_64 to 12.1.0.1 with the 1-System Method [ID 1364931.1]
How to Determine the List of Patch Set Update(PSU) Applied to the Enterprise Manager OMS and Agent Oracle Homes? [ID 1358092.1]

ORA-00600 [kgmgchd1]

2012-02-09T16:55:00.000-08:00

ORA-00600: internal error code, arguments: [kgmgchd1] appeared on 11.1.0.7 (11.1.0.7.9).

There are number of bugs related to this 11792492 which also duplicated as bug 6769075.

Download and apply Patch 6769075.

ORA-00600 [kgmgchd1] in 11.1.0.7 [ID 1333584.1]

Deinstalling Cluster base Grid Control Agents

2012-02-09T04:44:00.000-08:00

To remove grid control agents from a clustered configuration or standalone database use

/opt/app/oracle/grid_agent/agent11g/oui/bin/runInstaller -deinstall -silent "REMOVE_HOMES={/opt/app/oracle/grid_agent/agent11g}" -removeallfiles
Starting Oracle Universal Installer...

Checking swap space: must be greater than 500 MB.   Actual 4094 MB    Passed
Preparing to launch Oracle Universal Installer from /tmp/OraInstall2012-02-09_12-46-19PM. Please wait ...[oracle@rac1 bin]$ Oracle Universal Installer, Version 11.1.0.8.0 Production
Copyright (C) 1999, 2010, Oracle. All rights reserved.

Starting deinstall

Deinstall in progress (Thursday, February 9, 2012 12:47:08 PM GMT)
Configuration assistant "Agent Deinstall Assistant" succeeded
Configuration assistant "Oracle Configuration Manager Deinstall" succeeded
............................................................... 100% Done.

Deinstall successful
Removing Cluster Oracle homes

This would remove agent installations from all the nodes in the cluster.

Linux Root Exploit CVE-2012-0056

2012-01-27T11:42:00.000-08:00

A Linux vulnerability has been identified which allows a user to gain root user privilege. This is caused by "failure of the Linux kernel to properly restrict access to the "/proc//mem" file".

Computer world has a detail article about this (Same article available on CIO).

Redhat has a support note regarding this vulnerability and a test code to check if the system is vulnerable.

Resizing VirtualBox and Linux Partitions with GParted

2012-01-27T11:20:00.000-08:00

VirtualBox 4 has an option to modify the logical size of the virtual disk after it's been created without affecting the physical size. The size specified in this option is the new overall size of the disk not the increment. If the current size is 100MB and resizing to 200MB would make the disk 200MB not 300MB (100 + 200). Once the size is increased it cannot be decreased (yet).

Current size of the disk

-rw------- 1 root root  18G Jan 27 16:43 rac4.vdi

The size of disk seen by the OS using this virutal disk

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

Size of the partitions

Filesystem            Size  Used Avail Use% Mounted on
/dev/sda3              16G   13G  1.8G  88% /
/dev/sda1              99M   12M   83M  13% /boot
tmpfs                1006M     0 1006M   0% /dev/shm

Increase the size with modifyhd option

VBoxManage modifyhd rac4.vdi --resize 40960
0%...10%...20%...30%...40%...50%...60%...70%...80%...90%...100%

Physical size is unaffected. This will grow as the space is used.

-rw------- 1 root root  18G Jan 27 16:45 rac4.vdi

But the size of the disk seen by the OS has changed

Disk /dev/sda: 42.9 GB, 42949672960 bytes
255 heads, 63 sectors/track, 5221 cylinders
Units = cylinders of 16065 * 512 = 8225280 bytes

The additional space is unallocated and linux partitions are not affected after this increase.

Filesystem            Size  Used Avail Use% Mounted on
/dev/sda3              16G   13G  1.8G  88% /
/dev/sda1              99M   12M   83M  13% /boot
tmpfs                1006M     0 1006M   0% /dev/shm

Using the Gnome Partition Editor (GParted) it is possible to resize the linux partition and allocate the unused space to it effectively increasing the size of it.

Download the Live CD iso of gparted and boot the virtual box server from it.

Select the default option and complete the startup.

GParted shows the size of current partitions (15.9 GB) and the unallocated space (20 GB).

Right click on the partition to resize and select resize/move item from the popup menu.

Resize the partition by either dragging the highlighted box or adjusting the numerical spinners.

Click apply to complete the resizing.

Restart the virtual server and see the size of linux partition changed.

Filesystem            Size  Used Avail Use% Mounted on
/dev/sda3              35G   13G   21G  39% /
/dev/sda1              99M   12M   83M  13% /boot
tmpfs                1006M     0 1006M   0% /dev/shm

Size of the new partition is 35 GB (15 GB previously allocated and 20 GB added with GParted).

Connection Leak Detection on Applications Servers

2012-01-27T04:39:00.000-08:00

Connection leak detection on a 3-tier application servers present a challenge as the connecting database user name would be same for all the connections. Therefore when a connection leak happens unless the usecase is known, or if it's a something recently started then leak is probably due to some new deployment it's diffcult to detect from the database site. One way is finding the number of session with the same sqlid for previous sql

select inst_id,machine,username, prev_sql_id,count(*) from gv$session where username='ASANGA' group by inst_id,machine,username, prev_sql_id order by 1,2,3,4

SQL id with the highest count is a suspect but may not always be the case.

Use of logon triggers would be of no use if a connection pool is used as when connections are handed out from the pool logon trigger doesn't fire.

To identify the exact point in the code where the leak happening is to populate the CLIENT_INFO column with line and class name before handing over the connection. One way this could be accomplished is by overrding the getConnection method.

Throwable throwable = new Throwable();
StackTraceElement[] stackTraceElements = throwable.getStackTrace();
StackTraceElement element = stackTraceElements[stackTraceElements.length > 0 ? 1 : 0];
CallableStatement clm = connection.prepareCall("begin DBMS_APPLICATION_INFO.set_client_info(?); end;");
clm.setString(1, element.getClassName() + " : " + element.getMethodName() + " : " + element.getLineNumber());
clm.execute();
clm.close();

The above code snippet will populate the CLIENT_INFO column with the class name, method name and line number.Quering the v$session will show these values

select username,client_info from v$session where username is not null;

USERNAME CLIENT_INFO
-------- ---------------------------
ASANGA   com.Test : main : 86
ASANGA   com.Test : main : 79
ASANGA   com.Test : main : 79
ASANGA   com.Test : main : 79
ASANGA   com.Test : main : 85

2012 January PSU and the growth in the System Change Number (SCN) bug.

2012-01-19T03:04:00.000-08:00

With 2012 January PSU/CPU Oracle has patched the SCN growth bug, the case when a database is having a high rate change for SCN. Computer world has an comprehensive article about this issue.

Following metalink notes provides means to test if the database is affected by this bug.
Information on the System Change Number (SCN) and how it is used in the Oracle Database [ID 1376995.1]
Installing, Executing and Interpreting output from the "scnhealthcheck.sql" script [ID 1393363.1]
Evidence to collect when reporting "high SCN rate" issues to Oracle Support [ID 1388639.1]

scnhealthcheck.sql mentioned in 1393363.1 could be used to verify if the database is affected.

SQL> @scnhealthcheck.sql
--------------------------------------------------------------
ScnHealthCheck
--------------------------------------------------------------
Current Date: 2012/01/19 10:39:57
Current SCN:  4165911
Version:      11.2.0.3.0
--------------------------------------------------------------
Result: A - SCN Headroom is good
Apply the latest recommended patches
based on your maintenance schedule
For further information review MOS document id 1393363.1
--------------------------------------------------------------

Recommendations are if the output is Result: A - SCN Headroom is good then system SCN health is good apply the recommended patches (756671.1) during the normal maintenance schedule.

If the output is Result: B - SCN Headroom is low then apply the recommended patches immediately outside the normal maintenance schedule and continue to monitor the SCN health. Once patched headroom will increase overtime (could take days or weeks) to script to report Result: A - SCN Headroom is good.

If the output is Result: C - SCN Headroom is low then apply the recommended patches immediately and collect required information listed on 1388639.1 and raise a SR so additional advice could be given.

With this PSU a new hidden parameter called "_external_scn_rejection_threshold_hours" has been introduced in connection with this bug. Once the patch is applied Oracle recommend setting this to 24 on 10g and 11gR1 systems. Value of _external_scn_rejection_threshold_hours after patched applied on 11gR1 (11.1.0.7)

To set the value to 24

alter system set "_external_scn_rejection_threshold_hours" = 24 
   comment='Set threshold on dd/mon/yyyy - See MOS Document 1393363.1' 
   scope=spfile ;

On 11gR2 systems this set to 24 by default once the patch is applied.

Apart from the the test cases mentioned in 1376995.1 IOUG newsletter mentioned another "way to test is to look at the backups with a restore process to validate SCN values and recoverability which is part of a best practice for database administrators. With these tests and test plans for applying the Patch Set Updates (PSU) or traditional Critical Patch Update patches, the vulnerabilities can be validated with appropriate fixes of the environment."

More clarifications on the issue from computer world

dbms_cube_exp.schema_info_imp_beg error while importing with impdp

2012-01-18T05:39:00.000-08:00

Following error came up when doing a schema import from a 11gR1 standard edition database to another 11gR1 standard edition database.Beginning of import

ORA-39083: Object type PROCACT_SCHEMA failed to create with error:
ORA-12663: Services required by client not available on the server
ORA-36961: Oracle OLAP is not available.
Failing sql is:
BEGIN 
dbms_cube_exp.schema_info_imp_beg(0, '11.01.00.00.00');
dbms_cube_exp.schema_info_imp_end(0, '11.01.00.00.00');COMMIT; END;

and at the end

ORA-39083: Object type PROCACT_SCHEMA failed to create with error:
ORA-12663: Services required by client not available on the server
ORA-36961: Oracle OLAP is not available.
Failing sql is:
BEGIN 
dbms_cube_exp.schema_info_imp_beg(1, '11.01.00.00.00');
dbms_cube_exp.schema_info_imp_loop(1, '11.01.00.00.00', '&lt;?xml version="1.0" encoding="UTF-16"?>
&lt;Metadata
Version="1.1"
CreateOnly="True">
&lt;/Metadata>');
dbms_cube_exp.schema_info_imp_end(1, '11.01.00.00.00');COMMIT; END;

The import completed without any further errors and schema objects were available on the imported database. Metalink note DataPump Import (IMPDP) Receives The Errors ORA-39083 PLS-201 identifier DBMS_CUBE_EXP.SCHEMA_INFO_IMP_BEG [ID 789549.1] mentions that this is a ignorable error.

OLAP is not an option available with standard edition database but it gets added in an invalid state when a database is created using one of the provided database templates (transaction processing database) with oracle. Although option get installed it remains in a invalid state.
Metalink note "How To Find Out If OLAP Is Being Used And How To Remove OLAP [Doc ID 739032.1]" could be used to verify if olap is installed and used. (being used in a standard edition system is unlikely). The remove steps listed in this metalink note cannot be run on standard edition as those files are not installed with standard edition.

There's another metalink that list "How to Remove OLAP From the Standard Edition database. [ID 1362752.1]" which is by dropping the OLAPSYS user and running utlrp afterwards. This will remove the OLAP Catalog option from the database but OLAP Analytic Workspace and Oracle OLAP API will remain in an invalid state.

SQL> select comp_id, comp_name, version, status
  2         from dba_registry
  3         where comp_name like '%OLAP%';

COMP_ COMP_NAME                 VERSION    STATUS
----- ------------------------- ---------- --------
APS   OLAP Analytic Workspace   11.1.0.7.0 INVALID
XOQ   Oracle OLAP API           11.1.0.7.0 INVALID

Metalink note Remove Invalid OLAP Objects From SYS And OLAPSYS Schemas [ID 565773.1] could be used to resolve any invalid objects issues after the removal of olap catalog.

SR has been raised to find out ways to remove these options from a standard edition database.

Useful metalink notes
How To Remove or De-activate OLAP After Migrating From 9i To 10g or 11g [ID 467643.1]
ORA-39127 SYS.DBMS_CUBE_EXP.INSTANCE_EXTENDED_INFO_EXP ORA-44002 On Expdp After Upgrade To 11.2.0.2 [ID 1353491.1]
How To Find Out If OLAP Is Being Used And How To Remove OLAP [Doc ID 739032.1]
How to Remove OLAP From the Standard Edition database. [ID 1362752.1]
Remove Invalid OLAP Objects From SYS And OLAPSYS Schemas [ID 565773.1]
How To Remove Or To Reinstall The OLAP Option To 10g And 11g [ID 332351.1]
DataPump Import(IMPDP) Receives The Errors ORA-39083 PLS-201 identifier DBMS_CUBE_EXP.SCHEMA_INFO_IMP_BEG [ID 789549.1]

Workaround
First of all it must be strenuously stressed that this workaround is not an official oracle supported one (yet! maybe!).

Copied the olap folder ($ORACLE_HOME/olap) from a enterprise installation into standard edition home and ran the remove scripts mentioned in Doc ID 739032.1.

@?/olap/admin/catnoamd.sql
@?/olap/admin/olapidrp.plb
@?/olap/admin/catnoaps.sql
@?/olap/admin/catnoxoq.sql

Afterwards ran @?/rdbms/admin/utlrp and execution didn't show any errors. But three sys object related OLAP came as invalid

SQL> select object_name,object_type from dba_objects where owner='SYS' and status <> 'VALID';

OBJECT_NAME              OBJECT_TYPE
------------             -------------
INTERACTIONEXECUTE        FUNCTION
XOQ_VALIDATE              PROCEDURE
CWM2_OLAP_INSTALLER       PACKAGE BODY

Dropped them and ran utlrp again still no errors. The OLAP option that remained invalid in the dba_registry is now shown as removed.

SQL> select comp_id, comp_name, version, status
  2         from dba_registry
  3         where comp_name like '%OLAP%';

COMP_ COMP_NAME                 VERSION    STATUS
----- ------------------------- ---------- --------
APS   OLAP Analytic Workspace   11.1.0.7.0 REMOVED
XOQ   Oracle OLAP API           11.1.0.7.0 REMOVED

Update 02 February 2012
Outcome from the SR was these options were never intended to be in SE (Standard Edition) but could get in through imp and using the template that has some EE (Enterprise Edition) components (was the case here). So there's no "official" way to remove these components from SE and require a custom approach since the script provided to remove these components from EE are not even installed on SE. Therefore it wouldn't be a issue if those scripts are copied from EE to SE to remove the unsupported components.

Running Opatch throws up org.xml.sax.SAXParseException: : XML-20108: (Fatal Error) Start of root element expected

2012-01-18T05:12:00.000-08:00

The server was hosting multiple Oracle homes from 10gR2, 11gR1 and 11gR2. Primarily used for testing PSU patch before applying to production system. There hasn't been any new installation of Oracle homes and inventory has been without any errors.

Trying to apply the PSU Jan threw up the following error.

Oracle Interim Patch Installer version 11.2.0.1.9
Copyright (c) 2011, Oracle Corporation.  All rights reserved.

Oracle Home       : /opt/app/oracle/product/11.2.0/ent11.2.0.3
Central Inventory : /opt/app/oraInventory
   from           : /opt/app/oracle/product/11.2.0/ent11.2.0.3/oraInst.loc
OPatch version    : 11.2.0.1.9
OUI version       : 11.2.0.3.0
Log file location : /opt/app/oracle/product/11.2.0/ent11.2.0.3/cfgtoollogs/opatch/opatch2012-01-18_12-16-37PM.log

org.xml.sax.SAXParseException: : XML-20108: (Fatal Error) Start of root element expected.
        at oracle.xml.parser.v2.XMLError.flushErrorHandler(XMLError.java:415)
        at oracle.xml.parser.v2.XMLError.flushErrors1(XMLError.java:284)
        at oracle.xml.parser.v2.NonValidatingParser.parseRootElement(NonValidatingParser.java:331)
        at oracle.xml.parser.v2.NonValidatingParser.parseDocument(NonValidatingParser.java:293)
        at oracle.xml.parser.v2.XMLParser.parse(XMLParser.java:209)
        at oracle.sysman.oii.oiii.OiiiOracleHomeInfoXMLReader.updateHomeProperties(OiiiOracleHomeInfoXMLReader.java:86)
        at oracle.sysman.oii.oiii.OiiiInstallInventory.updateOracleHomesProperties(OiiiInstallInventory.java:803)
        at oracle.sysman.oii.oiii.OiiiInstallInventory.updateOracleHomesProperties(OiiiInstallInventory.java:781)
        at oracle.sysman.oii.oiii.OiiiInstallInventory.readHomes(OiiiInstallInventory.java:737)
        at oracle.sysman.oii.oiii.OiiiInstallAreaControl.loadPartialInstallInv(OiiiInstallAreaControl.java:776)
        at oracle.sysman.oii.oiii.OiiiInstallAreaControl.initInstallInv(OiiiInstallAreaControl.java:821)
        at oracle.sysman.oii.oiii.OiiiInstallAreaControl.loadInstallInventory(OiiiInstallAreaControl.java:592)
        at oracle.sysman.oii.oiii.OiiiInstallAreaControl.initAreaControl(OiiiInstallAreaControl.java:1977)
        at oracle.sysman.oii.oiii.OiiiInstallAreaControl.initAreaControl(OiiiInstallAreaControl.java:1930)
        at oracle.sysman.oii.oiic.OiicStandardInventorySession.initSession(OiicStandardInventorySession.java:301)
        at oracle.sysman.oii.oiic.OiicStandardInventorySession.initSession(OiicStandardInventorySession.java:240)
        at oracle.sysman.oii.oiic.OiicStandardInventorySession.initSession(OiicStandardInventorySession.java:189)
        at oracle.opatch.OUIInventorySession.initSession(OUIInventorySession.java:63)
        at oracle.opatch.OUISessionManager.setupSession(OUISessionManager.java:150)
        at oracle.opatch.OUISessionManager.lockCentralInventory(OUISessionManager.java:267)
        at oracle.opatch.OUISessionManager.instantiate(OUISessionManager.java:87)
        at oracle.opatch.OUISessionManager.updateOPatchEnvironment(OUISessionManager.java:661)
        at oracle.opatch.InventorySessionManager.updateOPatchEnvironment(InventorySessionManager.java:91)
        at oracle.opatch.OPatchSession.main(OPatchSession.java:1661)
        at oracle.opatch.OPatch.main(OPatch.java:653)

No matter which Oracle home was choosen the same error came up.

Two metalink notes related to the issue are
OPatch Is Failing With "org.xml.sax.SAXParseException" XML-20108: (Fatal Error) [ID 1365569.1]
Opatch command throws warning:org.xml.sax.SAXParseException: : XML-20108: (Fatal Error) Start of root element expected. [ID 1344580.1]

Issue mentioned in the first metalink note isn't the case here as the opatch version matches the oracle home version.
The second one mentions if $ORACLE_HOME/inventory/ContentsXML/oraclehomeproperties.xml file is 0 bytes then this error is shown. But the size of this file in the oracle home wasn't 0 bytes

ls -l $ORACLE_HOME/inventory/ContentsXML/oraclehomeproperties.xml
-rw-rw---- 1 oracle oinstall 526 Nov 16 14:20 /opt/app/oracle/product/11.2.0/ent11.2.0.3/inventory/ContentsXML/oraclehomeproperties.xml

It seemed the cause mentioned in the second metalink note is also not the case. But checking all the homes in the server it was found that there was one oracle home (home running 11gR1) which had this file with 0 bytes. There was a cloned copy of this home on which the file wasn't 0 bytes. Copying it into the this oracle home's inventory/ContentsXML resolved this issue.

Therefore when there are multiple oracle homes in a server and if inventory/ContentsXML/oraclehomeproperties.xml file gets corrupted in at least one of them the opatch will throw the above mentioned XML parse error.

Bitmap Index Locking

2012-01-17T09:17:00.000-08:00

Bitmap indexes are useful when the cardinality of a column is low and it is required to join multiples of such columns. Primarily designed for used in data warehouse environments where DML activities are infrequent. Bitmap index can introduce performance overheads if used in a OLTP systems where DML activities are frequent. Reason is the way rows are locked to make changes to the bitmap index when a row is changed or inserted into the table.

This blog is to show these locks in action. Bitmap indexes are only available with Oracle Enterprise Edition.

Setup the initial environment

create table y (a number, b varchar2(20));

begin
    for i in  1 .. 10
    loop
      insert into y values(mod(i,5),dbms_random.string('X',10));
    end loop;
end;
    /

select DBMS_ROWID.ROWID_OBJECT(rowid) "OBJECT",
       DBMS_ROWID.ROWID_RELATIVE_FNO(rowid) "FILE",
       DBMS_ROWID.ROWID_BLOCK_NUMBER(rowid) "BLOCK",
       DBMS_ROWID.ROWID_ROW_NUMBER(rowid) "ROW", rowid,y.* from y order by 5,6;
    
    OBJECT       FILE      BLOCK        ROW ROWID                       A B
---------- ---------- ---------- ---------- ------------------ ---------- ----------
     93735          4        583          0 AAAW4nAAEAAAAJHAAA          1 1ML8L3NZBE
     93735          4        583          1 AAAW4nAAEAAAAJHAAB          2 E79SCS1B26
     93735          4        583          2 AAAW4nAAEAAAAJHAAC          3 OAMU9H2Y7B
     93735          4        583          3 AAAW4nAAEAAAAJHAAD          4 OAIF1LZZLE
     93735          4        583          4 AAAW4nAAEAAAAJHAAE          0 FU68J1S48B
     93735          4        583          5 AAAW4nAAEAAAAJHAAF          1 7WD1OB3O6T
     93735          4        583          6 AAAW4nAAEAAAAJHAAG          2 FC9UTGS8RY
     93735          4        583          7 AAAW4nAAEAAAAJHAAH          3 7H58F7BEZH
     93735          4        583          8 AAAW4nAAEAAAAJHAAI          4 S9UKCCLM38
     93735          4        583          9 AAAW4nAAEAAAAJHAAJ          0 EEBV89AYHA

10 rows selected.

create bitmap index aidx on y(a) compute statistics;

Values shown for column B will be different replace them accordingly.

The bitmap index entry would contain the column A as the index key and low rowid and high row id range encompassing all the rows in that bitmap key entry and the bitmap for the specific rowid range. See the bitmap index storage section

The above bitmap index could be conceptually visualised as

A       low rowid           high rowid          bitmap
1,  AAAW4nAAEAAAAJHAAA,  AAAW4nAAEAAAAJHAAJ,  1000010000
2,  AAAW4nAAEAAAAJHAAA,  AAAW4nAAEAAAAJHAAJ,  0100001000
3,  AAAW4nAAEAAAAJHAAA,  AAAW4nAAEAAAAJHAAJ,  0010000100
4,  AAAW4nAAEAAAAJHAAA,  AAAW4nAAEAAAAJHAAJ,  0001000010
0,  AAAW4nAAEAAAAJHAAA,  AAAW4nAAEAAAAJHAAJ,  0000100001

Now to see how locking takes place open two sql plus sessions and set auto commit off on one of them run a DML to change column A value from 1 to 2 for a single row.

SQL> update y set a = 2 where b  = '1ML8L3NZBE';

1 row updated.

As a result of this update two bitmap index entries will lock. Those are the index entry for 1 (to remove bitmap value 1 and set it to 0 for the row) and index entry for 2 (to set bitmap value from 0 to 1 for the row).

Because of this trying to update column A value from 1 to any other for another row (1 ---> 0,2,3,4) or any other value trying to change to 1 (0,2,3,4 --> 1) from two different sessions will result in session executing in second being blocked and will wait for first session to commit.

Similarly any other row that try to change its column A value to 2 will also block and wait. That is 0,1,3,4 ---> 2 and 2 ---> 0,1,3,4 will block and wait.

To test run the following updates from second session.

-- the other row with 1 on column A will be blocked and wait
--Wait will appear as a enq: TX row lock contention
update y set a = 4 where b = '7WD1OB3O6T'; 

-- row with 0 on column A trying to update to 1 blocked
update y set a = 1 where b = 'EEBV89AYHA';  

--another row with 2 on column A trying to update blocked and wait
update y set a = 3 where b = 'FC9UTGS8RY'; -- (could have used E79SCS1B26 as well) 

-- row with 0 on column A trying to update to 2 blocked and wait
update y set a = 2 where b = 'EEBV89AYHA';

Trying to delete any of the other rows with values 1 (B = 7WD1OB3O6T) or 2 (B = E79SCS1B26 or B = FC9UTGS8RY) will also block and wait. But it is it is possible to insert rows with values 1 or 2 for column A.

This is why it is considered bad practice to use bitmap indexes on tables with frequent DML activities.

Rollback the update statement on the first session and execute a insert statement

insert into y values (1,'ABCDEF');

now trying to update or delete any row with value 1 on column A will block and wait.

The above locking will take place as long as low rowid and high rowid of the rows in questions are the same index entry. If updating row's rowids are in two different index entries then the updates and deletes will go through without blocking. Taking the above example if new index entries are created in the bitmap index (represented by rowid range starting with BBBW) as below

A       low rowid           high rowid          bitmap
1,  AAAW4nAAEAAAAJHAAA,  AAAW4nAAEAAAAJHAAJ,  1000010000
2,  AAAW4nAAEAAAAJHAAA,  AAAW4nAAEAAAAJHAAJ,  0100001000
3,  AAAW4nAAEAAAAJHAAA,  AAAW4nAAEAAAAJHAAJ,  0010000100
4,  AAAW4nAAEAAAAJHAAA,  AAAW4nAAEAAAAJHAAJ,  0001000010
0,  AAAW4nAAEAAAAJHAAA,  AAAW4nAAEAAAAJHAAJ,  0000100001
1,  BBBW4nAAEAAAAJHAAA,  BBBW4nAAEAAAAJHAAJ,  1000010000
2,  BBBW4nAAEAAAAJHAAA,  BBBW4nAAEAAAAJHAAJ,  0100001000
3,  BBBW4nAAEAAAAJHAAA,  BBBW4nAAEAAAAJHAAJ,  0010000100
4,  BBBW4nAAEAAAAJHAAA,  BBBW4nAAEAAAAJHAAJ,  0001000010
0,  BBBW4nAAEAAAAJHAAA,  BBBW4nAAEAAAAJHAAJ,  0000100001

then when a row is updated in 1,AAAW4nAAEAAAAJHAAA-AAAW4nAAEAAAAJHAAJ rowid range it is possible to update another row in 1,BBBW4nAAEAAAAJHAAA-BBBW4nAAEAAAAJHAAJ
row id range without being blocked. This could be tested by inserting many more rows to the table which will result in additional index entries being created. Run the following from a different session to the two that is opened

begin
    for i in  1 .. 90000
    loop
    insert into y values(mod(i,5),dbms_random.string('X',10));
   end loop;
    end;
    /
commit;

select DBMS_ROWID.ROWID_OBJECT(rowid) "OBJECT",
           DBMS_ROWID.ROWID_RELATIVE_FNO(rowid) "FILE",
           DBMS_ROWID.ROWID_BLOCK_NUMBER(rowid) "BLOCK",
           DBMS_ROWID.ROWID_ROW_NUMBER(rowid) "ROW", rowid,y.* from y where a = 1 order by 5,6;
     
...
...
   OBJECT       FILE      BLOCK        ROW ROWID                       A B
---------- ---------- ---------- ---------- ------------------ ---------- ----------
     93735          4       5375        356 AAAW4nAAEAAABT/AFk          1 PQCAAA3OI7
     93735          4       5375        361 AAAW4nAAEAAABT/AFp          1 6G20GPXCSL
     93735          4       5375        366 AAAW4nAAEAAABT/AFu          1 XH9BDPW8XL
     93735          4       5375        371 AAAW4nAAEAAABT/AFz          1 WFAI39JSNU
     93735          4       5375        376 AAAW4nAAEAAABT/AF4          1 GXE3899NJE
     93735          4       5375        381 AAAW4nAAEAAABT/AF9          1 X8X0I0PMRB

18002 rows selected.

Running

update y set a = 2 where b  ='GXE3899NJE';

on session 1 and

update y set a = 2 where b  ='X8X0I0PMRB';

on session two will result in session 2 being blocked. It is safe to presume (may not always be the case) those two rowids are in the same range. But

update y set a = 2 where b  ='GXE3899NJE';

and

update y set a = 2 where b  = '1ML8L3NZBE';

will not block each other presuming these rowids are in different index entries.

KFOD - ASM Tool

2012-01-10T02:06:00.000-08:00

KFOD tool available in ASM Home (Grid Home) in 11gR2 and Oracle Home in other versions (11gR1 and 10gR2) gives a view of disks from the operating systems point of view. This is useful in diagnosting disk access errors in ASM.

Options available wth KFOD

kfod help=y
_asm_a/llow_only_raw_disks              KFOD allow only raw devices [_asm_allow_only_raw_disks=TRUE/(FALSE)]
_asm_l/ibraries         ASM Libraries[_asm_libraries=lib1,lib2,...]
_asms/id                ASM Instance[_asmsid=sid]
a/sm_diskstring         ASM Diskstring [asm_diskstring=discoverystring, discoverystring ...]
c/luster                KFOD cluster [cluster=TRUE/(FALSE)]
db/_unique_name         db_unique_name for ASM instance[db_unique_name=dbname]
di/sks          Disks to discover [disks=raw,asm,all]
ds/cvgroup              Include group name [dscvgroup=TRUE/(FALSE)]
g/roup          Disks in diskgroup [group=diskgroup]
h/ostlist               hostlist[hostlist=host1,host2,...]
metadata_a/usize                AU Size for Metadata Size Calculation
metadata_c/lients               Client Count for Metadata Size Calculation
metadata_d/isks         Disk Count for Metadata Size Calculation
metadata_n/odes         Node Count for Metadata Size Calculation
metadata_r/edundancy            Redundancy for Metadata Size Calculation
n/ohdr          KFOD header suppression [nohdr=TRUE/(FALSE)]
o/p             KFOD options type [OP=DISKS/CANDIDATES/MISSING/GROUPS/INSTS/VERSION/CLIENTS/RM/RMVERS/DFLTDSTR/GPNPDSTR/METADATA/ALL]
p/file          ASM parameter file [pfile=parameterfile]
s/tatus         Include disk header status [status=TRUE/(FALSE)]
v/erbose                KFOD verbose errors [verbose=TRUE/(FALSE)]

More on metalink note ASM tool to check access to ASM disks [ID 1359873.1]

enq JI - contention

2011-12-05T03:49:00.000-08:00

This enqueue used to prevent two sessions refreshing a materialized view at the same time. It is unavoidable if the situation allows multiple sessions to refresh at the same time.

Even when multiple sessions try to refresh the same materialized view the refresh process come to a conclusion, unless one of the session that is holding the enqueue dies or killed without the immediate option.

Following test case shows two sessions trying to refresh the same materialized view resulting in high JI contention but both session ultimately finishing the refresh task.

1. Create two tables and the materialized view.

create table x (a number primary key, b number);
create table y (c number,d number, foreign key (c) references x(a));

begin
for i in 1 .. 10
loop
insert into x values (i, i + 10);
end loop;
end;
/

begin
for i in 1 .. 5
loop
insert into y values (i, i + 5);
end loop;
end;
/

create materialized view log on x with rowid;
create materialized view log on y with rowid;

CREATE MATERIALIZED VIEW mvtest (p,q,yid,xid) BUILD IMMEDIATE USING INDEX REFRESH FAST ON demand AS select y.d p,x.b q,y.rowid yid,x.rowid xid from y, x where x.a=y.c;

2. Open three sqlplus sessions for the test. Two (called session 2, 3 from now on) will be running the refresh and other session will be used to synchronize the starting of the to refresh sessions (called session 1 from now on).

3. On session 1 run the following pl/sql code block to create and explicitly lock an id

declare
    i pls_integer;
begin
    i := dbms_lock.request(10);
end;
/

4. On session 2 and 3 run the following code. Both session will hang unable to get the lock id 10 in shared mode (which is held in exclusive mode by session 1). However once they obtain the lock both will try to refresh (fast) the materialized view 1000 times

declare
    i pls_integer;
begin
    i := dbms_lock.request(10,DBMS_LOCK.S_MODE);

    for i in 1 .. 1000
    loop
     dbms_snapshot.refresh('mvtest','F'); 
    end loop;
end;
/

5. Release the lock on id 10 by running the following code which will start the refresh process

declare
    j pls_integer;
begin
    j := dbms_lock.release(10);
end;
/

6. Observer the JI contention on em console or APConsole

If the dba_waiters view was queries repeatedly while the refresh process is going on, it could be observed that holding session and waiting session columns will have sid or session 2 and session 3 changing intermittently.

Both session eventually will complete and return to sql prompt.

However as said earlier if the refresh sessions are serialized this will not result in contention and refresh could complete much quicker. (The last bit of CPU spike at the end of the graph above).

Some useful metalink notes.
What is JI enqueue used for? [ID 580001.1]
Materialized View Refresh is Hanging With JI Contention [ID 1358453.1]
Monitoring Locks During Materialized View Refreshes [ID 258258.1]
MATERIALIZED VIEW REFRESH: Locking, Performance, Monitoring [ID 258252.1]
Performance Degradation and JI Lock Contention Observed During Batch Load of Tables With On-Commit Materialized Views [ID 371869.1]

Upgrading from 10.2.0.4 to 11.2.0.3

2011-12-01T04:36:00.000-08:00

There's a earlier blog about upgrading from 10g to 11gR2. This blog is to add some missing points in the previous blog and additional information with regard to upgrading from 10.2.0.4 to 11.2.0.3.

Following metalink notes are useful for this upgrade processes.
Upgrade Advisor: Database from 10.2 to 11.2 [ID 251.1]
Complete Checklist to Upgrade the Database to 11gR2 using DBUA [ID 870814.1]
Complete Checklist for Manual Upgrades to 11gR2 [ID 837570.1]
Oracle Database Upgrade Path Reference List [ID 730365.1]

The database to upgrade is a 10.2.0.4 with CPU 2009 July being the latest CPU that has been applied on it. In other words no CPU or PSU released after July 2009 has been applied on this database.

Secondly it's a standard edition database with the following registry components.

COMP_NAME                           STATUS
----------------------------------- -------
OLAP Analytic Workspace             INVALID
OLAP Catalog                        INVALID
Oracle OLAP API                     INVALID
JServer JAVA Virtual Machine        VALID
Oracle Data Mining                  VALID
Oracle Database Catalog Views       VALID
Oracle Database Java Packages       VALID
Oracle Database Packages and Types  VALID
Oracle Enterprise Manager           VALID
Oracle Expression Filter            VALID
Oracle Rules Manager                VALID
Oracle Text                         VALID
Oracle Workspace Manager            VALID
Oracle XDK                          VALID
Oracle XML Database                 VALID
Oracle interMedia                   VALID
Spatial                             VALID

Running utlrp.sql gives the following output as the number of invalid objects

OBJECTS WITH ERRORS
-------------------
                  8
DOC> The following query reports the number of errors caught during
..
ERRORS DURING RECOMPILATION
---------------------------
                          0

4 OLAPSYS packages and 4 public role synonyms are the objects with errors in this case.
OLAP* and several other components are not valid options on a standard edition database. In this case only OLAP* components are invalid but spatial, intermedia, data mining are all not valid for a standard edition database. But the registry will be valid on a fresh installation but after installing CPU or revoking execute on utl* packages for public and etc some of these will become invalid. The earlier blog was of such a fresh installation of 10gR2 that didn't have any components invalid.

Following metalink notes could be used to install/deinstall various components that are not valid in standard edition (if they were install initially).
How To Find Out If OLAP Is Being Used And How To Remove OLAP [ID 739032.1]
Remove Invalid OLAP Objects From SYS And OLAPSYS Schemas [ID 565773.1]
Steps for Manual De-installation of Oracle Spatial [ID 179472.1]
Removed Spatial Option But Spatial Still Appears In V$Option [ID 273573.1]
Where to Find the Information to Install, Upgrade, Downgrade and Deinstall interMedia/Oracle Multimedia? [ID 337415.1](on 10gR2 deinstall $OH/ord/im/admin/imdinst.sql, on 11gR2 $OH/rdbms/admin/catcmprm.sql ORDIM )

Trying to uninstall all non valid components could result in more objects with errors which caused problems during a test upgrade and made the upgrade to fail. If it is decided to remove all the components that are not valid in standard edition it is advisable to make sure that there are no invalid objects prior to upgrade. In this case the upgrade will continue with these three OLAP* components invalid.

The pre-upgrade information tool's output is as follows

SQL> @utlu112i.sql
Oracle Database 11.2 Pre-Upgrade Information Tool 11-29-2011 16:14:13
Script Version: 11.2.0.3.0 Build: 001
.
**********************************************************************
Database:
**********************************************************************
--> name:          stdb1
--> version:       10.2.0.4.0
--> compatible:    10.2.0.3.0
--> blocksize:     8192
--> platform:      Linux x86 64-bit
--> timezone file: V4
.
**********************************************************************
Tablespaces: [make adjustments in the current environment]
**********************************************************************
--> SYSTEM tablespace is adequate for the upgrade.
.... minimum required size: 725 MB
--> UNDOTBS1 tablespace is adequate for the upgrade.
.... minimum required size: 400 MB
--> SYSAUX tablespace is adequate for the upgrade.
.... minimum required size: 461 MB
--> TEMP tablespace is adequate for the upgrade.
.... minimum required size: 60 MB
.
**********************************************************************
Flashback: OFF
**********************************************************************
**********************************************************************
Update Parameters: [Update Oracle Database 11.2 init.ora or spfile]
Note: Pre-upgrade tool was run on a lower version 64-bit database.
**********************************************************************
--> If Target Oracle is 32-Bit, refer here for Update Parameters:
-- No update parameter changes are required.
.

--> If Target Oracle is 64-Bit, refer here for Update Parameters:
-- No update parameter changes are required.
.
**********************************************************************
Renamed Parameters: [Update Oracle Database 11.2 init.ora or spfile]
**********************************************************************
-- No renamed parameters found. No changes are required.
.
**********************************************************************
Obsolete/Deprecated Parameters: [Update Oracle Database 11.2 init.ora or spfile]
**********************************************************************
--> background_dump_dest         11.1       DEPRECATED   replaced by  "diagnostic_dest"
--> user_dump_dest               11.1       DEPRECATED   replaced by  "diagnostic_dest"
.

**********************************************************************
Components: [The following database components will be upgraded or installed]
**********************************************************************
--> Oracle Catalog Views         [upgrade]  VALID
--> Oracle Packages and Types    [upgrade]  VALID
--> JServer JAVA Virtual Machine [upgrade]  VALID
--> Oracle XDK for Java          [upgrade]  VALID
--> Oracle Workspace Manager     [upgrade]  VALID
--> OLAP Analytic Workspace      [upgrade]  INVALID
--> OLAP Catalog                 [upgrade]  INVALID
--> EM Repository                [upgrade]  VALID
--> Oracle Text                  [upgrade]  VALID
--> Oracle XML Database          [upgrade]  VALID
--> Oracle Java Packages         [upgrade]  VALID
--> Oracle interMedia            [upgrade]  VALID
--> Spatial                      [upgrade]  VALID
--> Data Mining                  [upgrade]  VALID
--> Expression Filter            [upgrade]  VALID
--> Rule Manager                 [upgrade]  VALID
--> Oracle OLAP API              [upgrade]  INVALID
.
**********************************************************************
Miscellaneous Warnings
**********************************************************************
WARNING: --> Database is using a timezone file older than version 14.
.... After the release migration, it is recommended that DBMS_DST package
.... be used to upgrade the 10.2.0.4.0 database timezone version
.... to the latest version which comes with the new release.
WARNING: --> Database contains INVALID objects prior to upgrade.
.... The list of invalid SYS/SYSTEM objects was written to
.... registry$sys_inv_objs.
.... The list of non-SYS/SYSTEM objects was written to
.... registry$nonsys_inv_objs.
.... Use utluiobj.sql after the upgrade to identify any new invalid
.... objects due to the upgrade.
.... USER OLAPSYS has 4 INVALID objects.
.... USER PUBLIC has 4 INVALID objects.
WARNING: --> EM Database Control Repository exists in the database.
.... Direct downgrade of EM Database Control is not supported. Refer to the
.... Upgrade Guide for instructions to save the EM data prior to upgrade.
WARNING: --> Your recycle bin is turned on and currently contains no objects.
.... Because it is REQUIRED that the recycle bin be empty prior to upgrading
.... and your recycle bin is turned on, you may need to execute the command:
        PURGE DBA_RECYCLEBIN
.... prior to executing your upgrade to confirm the recycle bin is empty.
.
**********************************************************************
Recommendations
**********************************************************************
Oracle recommends gathering dictionary statistics prior to
upgrading the database.
To gather dictionary statistics execute the following command
while connected as SYSDBA:

    EXECUTE dbms_stats.gather_dictionary_stats;

**********************************************************************
Oracle recommends reviewing any defined events prior to upgrading.

To view existing non-default events execute the following commands
while connected AS SYSDBA:
  Events:
    SELECT (translate(value,chr(13)||chr(10),' ')) FROM sys.v$parameter2
      WHERE  UPPER(name) ='EVENT' AND  isdefault='FALSE'

  Trace Events:
    SELECT (translate(value,chr(13)||chr(10),' ')) from sys.v$parameter2
      WHERE UPPER(name) = '_TRACE_EVENTS' AND isdefault='FALSE'

Changes will need to be made in the init.ora or spfile.

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

Carry out the steps recommended by this tool such as gathering dictionary and schema statistics and etc. More steps available on 837570.1 which are not relevant on this upgrade so they are not listed.

Another key difference is that 11.2.0.3 provides a way to upgrade the timezone at the same time the database is upgrade. Which was not there on the base 11gR2 release. Compare the 5th picture on the previous blog with the picture below which gives the option to upgrade the timezone.

There are several metalink notes related to timezone upgrade.
Updating the RDBMS DST version in 11gR2 (11.2.0.1 and up) using DBMS_DST [ID 977512.1]
Actions For DST Updates When Upgrading To Or Applying The 11.2.0.3 Patchset [ID 1358166.1]
Before the upgrade the timezone file version was

upgrade 
SQL> select version from v$timezone_file;

   VERSION
----------
         4

Key points relevant to this are (quoting from the metalink note 1358166.1)If this reports a timezone version lower then 14 please see point B.3) in this document.

B.3) If your current timezone version is lower than 14 (typically 4):

B.3b) When upgrading from 10.1.0.x , 10.2.0.x or 11.1.0.x to 11.2.0.3:
For 10.2.0.2 , 10.2.0.3 , 10.2.0.4 or 10.2.0.5 there is no need to apply any patchset before upgrading to 11.2.0.3

Upgrade to 11.2.0.3.No need to apply DST patches on the 10.2.0.2 , 10.2.0.3 , 10.2.0.4 or 10.2.0.5 side first. You can skip any DST related upgrade instructions.
The 11.2.0.3 RDBMS DST version after the upgrade to 11.2.0.3 will be the same DST version as used in 10.2.0.2 , 10.2.0.3 , 10.2.0.4 or 10.2.0.5.

After the upgrade to 11.2.0.3 you can:
(recommended) update the 11.2.0.3 database(s) to DSTv14 (standard DST version of 11.2.0.3) by following Note 977512.1 Updating the RDBMS DST version in 11gR2 (11.2.0.1 and up) using DBMS_DST from step 3a) onwards, when going to DSTv14 there is no need to apply any DST patch to the 11.2.0.3 home

(optional) update to a higher DST than 14 version if this is needed.
Apply the latest 11.2.0.3 DST update patch after the upgrade to 11.2.0.3 and use DDBMS_DST.

The lastest DST patch and related note on how to apply this is found in Note 412160.1 Updated Time Zones in Oracle Time Zone File patches under "C) Notes covering the current DST available updates". Simply follow the note for the latest DST update. If decided it is possible to upgrade the database's timezone after the database upgrade by following the above metalink notes. In this case timezone was upgraded at the same time database was upgraded.

Save the em control incase later on it is decided to downgrade back to 10g.
Create a TNS name entry in the 10g Oracle Home (network/admin) with the same name as the SID before running the above

/opt/app/oracle/product/11.2.0/dbhome_1/bin/emdwgrd -save -sid stdb1 -path /home/oracle/emdctl
Enter sys password for database stdb1?

Database Unique Name : stdb1
Tue Nov 29 17:01:29 2011 - Validating DB Connection to stdb1 ... pass
Tue Nov 29 17:01:29 2011 - Verify EM DB Control files ... pass
ENV var EM_REMCP not defined, check if rcp or scp is configured.
RCP = /usr/bin/rcp -rp, REMSH = /usr/bin/rsh
shared = 0
Tue Nov 29 17:01:29 2011 - Creating directory ... created
Tue Nov 29 17:01:30 2011 - Stopping DB Control ... stopped
Tue Nov 29 17:01:35 2011 - Saving DB Control files
 ... saved
Tue Nov 29 17:01:35 2011 - Recompiling invalid objects ... recompiled
Tue Nov 29 17:01:36 2011 - Exporting sysman schema for stdb1 ... exported
Tue Nov 29 17:01:59 2011 - DB Control was saved successfully.
Tue Nov 29 17:01:59 2011 - Starting DB Control ... started
Tue Nov 29 17:03:33 2011 - Dump directory was dropped successfully.

Disable all batch and cron jobs.

Take a full backup of the database. (Better if it's a cold backup, not necessary)

Stop the 10g listener and create a listener on 11gR2 home and start it.

Set ORACLE_BASE ,ORACLE_HOME and PATH environment variable pointing to the 11gr2 home and run dbua from the 11gR2 home's bin. Upgrade summary is given below.

Upgrade process will omit some of the components from the upgrade process.

This is also mentioned when selecting the database to upgrade.

During the upgrade it could be seen that some of the components are not upgraded. These components are not valid on standard edition. Also note timezone upgrade.

Upgrade results

Verifying the timezone upgrade

SQL> select version from v$timezone_file;

   VERSION
----------
        14

Components status in the database registry

COMP_NAME                                     VERSION                        STATUS
--------------------------------------------- ------------------------------ -----------
Oracle Multimedia                             11.2.0.3.0                     INVALID
OLAP Analytic Workspace                       10.2.0.4.0                     OPTION OFF
OLAP Catalog                                  10.2.0.4.0                     OPTION OFF
Oracle Data Mining                            10.2.0.4.0                     OPTION OFF
Oracle OLAP API                               10.2.0.4.0                     OPTION OFF
Spatial                                       10.2.0.4.0                     OPTION OFF
JServer JAVA Virtual Machine                  11.2.0.3.0                     VALID
Oracle Database Catalog Views                 11.2.0.3.0                     VALID
Oracle Database Java Packages                 11.2.0.3.0                     VALID
Oracle Database Packages and Types            11.2.0.3.0                     VALID
Oracle Enterprise Manager                     11.2.0.3.0                     VALID
Oracle Expression Filter                      11.2.0.3.0                     VALID
Oracle Rules Manager                          11.2.0.3.0                     VALID
Oracle Text                                   11.2.0.3.0                     VALID
Oracle Workspace Manager                      11.2.0.3.0                     VALID
Oracle XDK                                    11.2.0.3.0                     VALID
Oracle XML Database                           11.2.0.3.0                     VALID

17 rows selected.

Oracle Multimedia(Oracle interMedia in 10g) is invalid after the upgrade. Following two metalink notes help identifying the reason for this
Things To Check When Oracle Multimedia/interMedia Is INVALID In DBA_REGISTRY Or The Version Is Not The Same As The Database Version [ID 1065954.1]
Verifying an Installed Version of Oracle Multimedia [ID 458228.1]

Running the validate_ordim gives the following errors

SQL> set serveroutput on
SQL> execute validate_ordim;
Locator INVALID OBJECTS: PRVT_IDX - 5 - 11
Locator INVALID OBJECTS: SDO_GEOM - 5 - 11
Locator INVALID OBJECTS: SAMCLUST_IMP_T - 5 - 14

PL/SQL procedure successfully completed.

From the output it seems that some of the spatial component related objects are invalid (since spatial option is off after the upgrade) thus oracle media that depends on it also becomes invalid. Remove the Oracle media component with (which also removes spatial component which is depends on media)

SQL> @?/rdbms/admin/catcmprm.sql ORDIM
About to remove Oracle Multimedia.
Checking to see if anyone is using Oracle Multimedia.

Oracle Multimedia is not being used
PL/SQL procedure successfully completed.

Are you sure you want to remove Oracle Multimedia (Y/N): y
PL/SQL procedure successfully completed.

Removing Oracle Multimedia
PL/SQL procedure successfully completed.

After remove dba registry status

COMP_NAME                           VERSION                        STATUS
----------------------------------- ------------------------------ --------------------------------------------
OLAP Analytic Workspace             10.2.0.4.0                     OPTION OFF
OLAP Catalog                        10.2.0.4.0                     OPTION OFF
Oracle Data Mining                  10.2.0.4.0                     OPTION OFF
Oracle OLAP API                     10.2.0.4.0                     OPTION OFF
JServer JAVA Virtual Machine        11.2.0.3.0                     VALID
Oracle Database Catalog Views       11.2.0.3.0                     VALID
Oracle Database Java Packages       11.2.0.3.0                     VALID
Oracle Database Packages and Types  11.2.0.3.0                     VALID
Oracle Enterprise Manager           11.2.0.3.0                     VALID
Oracle Expression Filter            11.2.0.3.0                     VALID
Oracle Rules Manager                11.2.0.3.0                     VALID
Oracle Text                         11.2.0.3.0                     VALID
Oracle Workspace Manager            11.2.0.3.0                     VALID
Oracle XDK                          11.2.0.3.0                     VALID
Oracle XML Database                 11.2.0.3.0                     VALID

15 rows selected.

This will increase the number of invalid objects but these will be OLAP related objects and has no impact on the rest of the database operation.

SQL>select owner,object_type,count(*) from dba_objects where status='INVALID' group by owner,object_type order by owner,object_type ;

OWNER                          OBJECT_TYPE           COUNT(*)
------------------------------ ------------------- ----------
OLAPSYS                        PACKAGE BODY                 3
OLAPSYS                        VIEW                        25
PUBLIC                         SYNONYM                     25

It is possible to drop the olapsys user and also drop remaining public synonyms.

DROP USER OLAPSYS CASCADE;
select 'drop '||object_type||' '||substr(object_name,1,40) ||';' from  dba_objects where status='INVALID';

After this expdp/impdp could run into following error

ORA-39127: unexpected error from call to "SYS"."DBMS_CUBE_EXP"."INSTANCE_EXTENDED_INFO_EXP"
ORA-44002: invalid object name
ORA-06512: at "SYS.DBMS_ASSERT", line 316
ORA-06512: at "SYS.DBMS_METADATA", line 9114

Metalink notes How To Remove or De-activate OLAP After Migrating From 9i To 10g or 11g [ID 467643.1] and ORA-39127 SYS.DBMS_CUBE_EXP.INSTANCE_EXTENDED_INFO_EXP ORA-44002 On Expdp After Upgrade To 11.2.0.2 [ID 1353491.1] explains the solution for this, which is If the OLAP option is not used delete DBMS_CUBE_EXP OLAP package from the export view as follows

SQL> select PACKAGE,SCHEMA,class from exppkgact$ where (schema,package) not in (select owner,object_name from dba_objects where object_type='PACKAGE');

PACKAGE                        SCHEMA                              CLASS
------------------------------ ------------------------------ ----------
DBMS_CUBE_EXP                  SYS                                     4

SQL> delete from sys.exppkgact$ where package = 'DBMS_CUBE_EXP' and schema= 'SYS';
1 row deleted.
SQL> commit;
Commit complete.

After this export will work as expected and there will no invalid objects nor any invalid database components

@?/rdbms/admin/utlrp
OBJECTS WITH ERRORS
-------------------
                  0

DOC> The following query reports the number of errors caught during
..
ERRORS DURING RECOMPILATION
---------------------------
                          0

SQL> select comp_name,version,status from dba_registry order by 3,1,2;

COMP_NAME                           VERSION                        STATUS
----------------------------------- ------------------------------ -----------
OLAP Analytic Workspace             10.2.0.4.0                     OPTION OFF
Oracle Data Mining                  10.2.0.4.0                     OPTION OFF
Oracle OLAP API                     10.2.0.4.0                     OPTION OFF
JServer JAVA Virtual Machine        11.2.0.3.0                     VALID
Oracle Database Catalog Views       11.2.0.3.0                     VALID
Oracle Database Java Packages       11.2.0.3.0                     VALID
Oracle Database Packages and Types  11.2.0.3.0                     VALID
Oracle Enterprise Manager           11.2.0.3.0                     VALID
Oracle Expression Filter            11.2.0.3.0                     VALID
Oracle Rules Manager                11.2.0.3.0                     VALID
Oracle Text                         11.2.0.3.0                     VALID
Oracle Workspace Manager            11.2.0.3.0                     VALID
Oracle XDK                          11.2.0.3.0                     VALID
Oracle XML Database                 11.2.0.3.0                     VALID

14 rows selected.

This concluded the ugprade to 11.2.0.3. After the upgrade the database will exhibit three shared segment behavior that is only found on 11.2.0.3.

APConsole

2011-11-24T15:08:00.000-08:00

I was tired of not having something similar to emconsole for standard edition databases.

So I developed APConsole.

Feature list and contact information (site 1)
Feature list and contact information (site 2)

Request a Trial Version

Installation is same as in ADMon (see ADMON user guide)

Full Page Demo

Comparison of EM Console vs APConsole
APCosnole has a longer x-axis than EM Cosnole therefore the graph's shape may seemed wider but time values are same and also the y-axis values.

Waits (APConsole vs EM Console)

IO Throughput (APConsole vs EM Console)

IO Rate (APConsole vs EM Console)

IO Type : Throughput (APConsole vs EM Console)

IO Type : Rate (APConsole vs EM Console)

Database load was generated with DBMS_RESOURCE_MANAGER.CALIBRATE_IO which gave the output of max_iops = 119, latency = 7, max_mbps = 19

ORA-00600 [kqlindp-bad-user]

2011-11-22T07:01:00.000-08:00

ORA-00600 [kqlindp-bad-user] error started to happen in a two node RAC running 11.1.0.7 on RHEL 64 bit system.

Effect on the system was both expdp and impdp was giving the following errors on the client side and ORA-00600 [kqlindp-bad-user] being logged on the alert.log. For import

UDI-31623: operation generated ORACLE error 31623
ORA-31623: a job is not attached to this session via the specified handle
ORA-06512: at "SYS.DBMS_DATAPUMP", line 2862
ORA-06512: at "SYS.DBMS_DATAPUMP", line 4052
ORA-06512: at line 1

For export

UDE-31623: operation generated ORACLE error 31623
ORA-31623: a job is not attached to this session via the specified handle
ORA-06512: at "SYS.DBMS_DATAPUMP", line 2862
ORA-06512: at "SYS.DBMS_DATAPUMP", line 4052
ORA-06512: at line 1 and also any local PL/SQL package execution would a

The only metalink note (at the time of writing) related to this ora-600 was ORA-600 [kqlindp-bad-user] [ID 753007.1] which wasn't much help.

Other issue was execution of PL/SQL packages (even users own ones, not the pl/sql packages on a different schema) throw up the ORA-00600 [kqlindp-bad-user] error. The error was coming up on the client side (JDBC connections) as well as beign logged on the server side.

There were plenty of metalink notes for errors UDI-31623 and ORA-31623 but none of those solutions helped.

After raising a SR following steps were recommended which resolved the issue.

1. Execute the following query and examine the output

select owner#, name
from sys.obj$ where owner# not in (select user# from sys.user$) and type# != 10;

This returned set of PL/SQL objects with owner id in this case all the objects seem to be from one owner.

2. After submitting the above query output Oracle came back with The output of the previous query shows non-existent users referenced by table sys.obj$. If you run hcheck.full it may show some data dictionary inconsistency. The ORA-600 error is not directly a consequence of the orphan obj$ rows; it is caused by one or more of the bad obj# being present in the objerror$ table.

Offered solution was take a full backup and in order to fix the issue remove the row from objerror$:

startup restrict
delete from sys.objerror$ where obj# in (select obj# from sys.obj$ where owner# = owner_id from previous sql and owner# not in (select user# from sys.user$));
commit;
shutdown abort;

The shutdown abort is required to save changes to the data dictionary. Once the database is shutdown, you must then perform a startup and a clean shutdown before it can be used again.

startup restrict;
shutdown immediate;
startup;

3. This resolved the issue with ORA-00600 [kqlindp-bad-user] coming up when executing pl/sql packages but expdp and impdp still gave errors, but this time it was different error.

ORA-31626: job does not exist
ORA-31637: cannot create job SYS_EXPORT_FULL_01 for user SYSTEM
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 95
ORA-06512: at "SYS.KUPV$FT_INT", line 601
ORA-39080: failed to create queues "" and "" for Data Pump job
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 95
ORA-06512: at "SYS.KUPC$QUE_INT", line 1550
ORA-01403: no data found

4. After refereing metalink notes
Impdp or Expdp Fails With ORA-31626 and ORA-31637 [ID 345198.1]
How To Reload Datapump Utility EXPDP/IMPDP [ID 430221.1] and
Invalid Objects After Installing a 10.2 Patchset [ID 361025.1]

@?/rdbms/admin/catproc.sql + steps in 361025.1 and @?/rdbms/admin/utlrp was executed which resolved the issue.

CLSC_RECEIVE: (*) ERROR 2

2011-11-18T05:37:00.000-08:00

crsd.log would have lines similar to

2011-11-17 19:12:19.860: [ COMMCRS][1455892800]clsc_receive: (0x1582a560) error 2

According to metalink note 733659.1 "This is basically a warning/informational message. The "error 2" is a generic failure code for a single message transmit."

More on Crsd.Log Showing The Following Message: CLSC_RECEIVE: (*) ERROR 2 [ID 733659.1]

Multiple Shared Memory Segments Created by Default on 11.2.0.3

2011-11-16T07:35:00.000-08:00

There appear to be a change in the way shared memory segments are allocated in 11.2.0.3 when using automatic shared memory management (ASMM not AMM). Metalink note 15566.1 describes the three possible ways shared memory segments are allocated to depending on the SHMMAX value and the size of the SGA. They are "one-segment,contiguous multi-segment, and non-contiguous multi-segment."

When the SHMMAX value is larger than the SGA only one shared memory segment would be created. This was the behavior seen from 10gR2 upto 11.2.0.2. But as of 11.2.0.3 no matter how big the SHMMAX three segments get created.

Below is a comparison of segment creation for two databases one with 11.2.0.2 and other with 11.2.0.3. Both database reside in the same physical server.

The SHM* values on the server.

ipcs -lm

------ Shared Memory Limits --------
max number of segments = 4096
max seg size (kbytes) = 67108864
max total shared memory (kbytes) = 17179869184
min seg size (bytes) = 1

The maximum shared segment size possible is 64GB and the system has 12GB of RAM. Server is running RHEL 5 (2.6.18-194.el5). There are no other databases or applications running beside these two databases. Only one of these database will be up and running at one time. Server was rebooted after shutting down one database and before starting the other.

First the 11.2.0.2 database information

memory_max_target                    big integer 0
memory_target                        big integer 0
pga_aggregate_target                 big integer 1200M
sga_max_size                         big integer 3600M
sga_target                           big integer 3600M

Find the shared memory segments used by this database.

$ORACLE_HOME/bin/sysresv

IPC Resources for ORACLE_SID "ent11g2" :
Shared Memory:
ID              KEY
60751874        0x3b6207ac
Semaphores:
ID              KEY
10551318        0x74afa7cc
Oracle Instance alive for sid "ent11g2"

The shared memory ID and key will be used to match the key shown with ipcs command. Since this is the only database running at this time and also shmmax is larger than the sga size only one shared segment is expected

------ Shared Memory Segments --------
key        shmid      owner      perms      bytes      nattch     status
0x3b6207ac 60751874   oracle    660        3776970752  29

The key and shmid match confirming that this is the shared segment created for this database.

Now the 11.2.0.3 database information (similar size sga as 11.2.0.2)

memory_max_target                    big integer 0
memory_target                        big integer 0
pga_aggregate_target                 big integer 1200M
sga_max_size                         big integer 3600M
sga_target                           big integer 3600M

Finding the shared memory segment for the database as above

$ORACLE_HOME/bin/sysresv

IPC Resources for ORACLE_SID "ent11g3" :
Shared Memory:
ID              KEY
61079554        0x00000000
61112324        0x00000000
61145093        0xf9058128
Semaphores:
ID              KEY
10977302        0xf1843220
Oracle Instance alive for sid "ent11g3"

Three segments have been created. Looking at the ipcs command output

ipcs -m

------ Shared Memory Segments --------
key        shmid      owner      perms      bytes      nattch     status
0x00000000 61079554   oracle    640        33554432     28
0x00000000 61112324   oracle    640        3741319168   28
0xf9058128 61145093   oracle    640        2097152      28

Instead of one as before three shared segments are created. If the size of all these segments are added up it would be equal (33554432 + 3741319168 + 2097152 = 3776970752) to the size of the single segment created with 11.2.0.2 database.

This is the behavior shown on all 11.2.0.3 databases whether they were upgraded from 11.2.0.2 to 11.2.0.3 or created new as in this case. Another observation is in all cases the last segment is of 2M size and second segment is the largest and close to the actual SGA size while size of the first segment changes depending on the SGA size.

Unless this a bug introduced in 11.2.0.3, this signals the change in behavior for allocating shared memory segments when using ASMM.

Interestingly even with AMM the number of created shared memory segments between 11.2.0.2 and 11.2.0.3 are 1 to 3 (this may or may not be related to the above observations and ipcs is not a correct way of finding out about the shared memory segments when using AMM. More on metalink note 731658.1)

With AMM on 11.2.0.2

$ORACLE_HOME/bin/sysresv
IPC Resources for ORACLE_SID "ent11g2" :
Shared Memory:
ID              KEY
3244034         0x3b6207ac
Semaphores:
ID              KEY
3801110         0x74afa7cc
Oracle Instance alive for sid "ent11g2"

With ipcs -m

ipcs -m

------ Shared Memory Segments --------
key        shmid      owner      perms      bytes      nattch     status
0x3b6207ac 3244034    oracle    660        4096        0

With AMM on 11.2.0.3

$ORACLE_HOME/bin/sysresv
IPC Resources for ORACLE_SID "ent11g3" :
Shared Memory:
ID              KEY
3047426         0x00000000
3080196         0x00000000
3112965         0xf9058128
Semaphores:
ID              KEY
3506198         0xf1843220
Oracle Instance alive for sid "ent11g3"

ipcs -m output shows three segments

ipcs -m

------ Shared Memory Segments --------
key        shmid      owner      perms      bytes      nattch     status
0x00000000 3047426    oracle    640        4096       0
0x00000000 3080196    oracle    640        4096       0
0xf9058128 3112965    oracle    640        4096       0

Update 29 November 2011
Had raised a SR about this and oracle seem to be missing the point that 11.2.0.3 is creating more segments than 11.2.0.2. Instead focusing on shmmax values which was set to 64GB saying it's too much. This has no effect on the number of segments,system only creates segments of requested sizes. Only good thing to come out of this so far is a request for a strace output. Start the database with

strace -f -o start.out sqlplus "/ as sysdba"

once started exit the SQL prompt. This may get stuck and might require killing the process manually.
ipcs -m output

ipcs -m
------ Shared Memory Segments --------
key        shmid      owner      perms      bytes      nattch     status
0x00000000 8880130    oracle    640        33554432    25
0x00000000 8912900    oracle    640        3741319168  25
0xf9058128 8945669    oracle    640        2097152     25

sysresv output

$ORACLE_HOME/bin/sysresv

IPC Resources for ORACLE_SID "ent11g3" :
Shared Memory:
ID              KEY
8880130         0x00000000
8912900         0x00000000
8945669         0xf9058128
Semaphores:
ID              KEY
9502742         0xf1843220
Oracle Instance alive for sid "ent11g3"

From the strace output these segment creation system calls could be picked up. Size of the segment and segment id has been highlighted.

21293 shmget(IPC_PRIVATE, 33554432, IPC_CREAT|IPC_EXCL|0640) = 8880130
21293 mmap(NULL, 1048576, PROT_READ|PROT_WRITE, MAP_PRIVATE|MAP_ANONYMOUS, -1, 0) = 0x2b8cd1281000
21293 getrlimit(RLIMIT_STACK, {rlim_cur=32768*1024, rlim_max=RLIM_INFINITY}) = 0
21293 open("/proc/self/maps", O_RDONLY) = 11


21293 shmget(IPC_PRIVATE, 3741319168, IPC_CREAT|IPC_EXCL|0640) = 8912900
21293 getrlimit(RLIMIT_STACK, {rlim_cur=32768*1024, rlim_max=RLIM_INFINITY}) = 0
21293 open("/proc/self/maps", O_RDONLY) = 11

21293 shmget(0xf9058128, 2097152, IPC_CREAT|IPC_EXCL|0640) = 8945669
21293 shmget(0xf9058129, 4096, 0)       = -1 ENOENT (No such file or directory)
21293 shmget(0xf9058129, 4096, 0)       = -1 ENOENT (No such file or directory)
21293 shmget(0xf9058129, 0, 0)          = -1 ENOENT (No such file or directory)
21293 shmget(0xf905812a, 4096, 0)       = -1 ENOENT (No such file or directory)
21293 shmget(0xf905812a, 4096, 0)       = -1 ENOENT (No such file or directory)
21293 shmget(0xf905812a, 0, 0)          = -1 ENOENT (No such file or directory)
21293 shmget(0xf905812b, 4096, 0)       = -1 ENOENT (No such file or directory)
21293 shmget(0xf905812b, 4096, 0)       = -1 ENOENT (No such file or directory)
21293 shmget(0xf905812b, 0, 0)          = -1 ENOENT (No such file or directory)
21293 getrlimit(RLIMIT_STACK, {rlim_cur=32768*1024, rlim_max=RLIM_INFINITY}) = 0
21293 open("/proc/self/maps", O_RDONLY) = 11

However on 11.2.0.2 database you only see one segment creation system call on the strace output matching the entire sga size.

ipcs -m

------ Shared Memory Segments --------
key        shmid      owner      perms      bytes      nattch     status
0x3b6207ac 9142278    oracle    660        3776970752   30


21778 munmap(0x2af10d7c3000, 4096)      = 0
21778 shmget(0x3b6207ac, 3776970752, IPC_CREAT|IPC_EXCL|0660) = 9142278
21778 shmget(0x3b6207ad, 4096, 0)       = -1 ENOENT (No such file or directory)
21778 shmget(0x3b6207ad, 4096, 0)       = -1 ENOENT (No such file or directory)
21778 shmget(0x3b6207ad, 0, 0)          = -1 ENOENT (No such file or directory)
21778 shmget(0x3b6207ae, 4096, 0)       = -1 ENOENT (No such file or directory)
21778 shmget(0x3b6207ae, 4096, 0)       = -1 ENOENT (No such file or directory)
21778 shmget(0x3b6207ae, 0, 0)          = -1 ENOENT (No such file or directory)
21778 shmget(0x3b6207af, 4096, 0)       = -1 ENOENT (No such file or directory)
21778 shmget(0x3b6207af, 4096, 0)       = -1 ENOENT (No such file or directory)
21778 shmget(0x3b6207af, 0, 0)          = -1 ENOENT (No such file or directory)
21778 mmap(NULL, 1048576, PROT_READ|PROT_WRITE, MAP_PRIVATE|MAP_ANONYMOUS, -1, 0) = 0x2af10d7c3000
21778 getrlimit(RLIMIT_STACK, {rlim_cur=32768*1024, rlim_max=RLIM_INFINITY}) = 0
21778 open("/proc/self/maps", O_RDONLY) = 5

Update 21 December 2011
Latest update is this issue could be related to following bug Bug 13360043 - ORA-27123: UNABLE TO ATTACH TO SHARED MEMORY SEGMENT STARTING 11.2.0.3 DATABASE
Oracle still investigating.

Update 28 December 2011
Oracle asked to comapre the db_block_numa value on both 11.2.0.2 and 11.2.0.3. On both databases it was the same value

select x.ksppinm, y.ksppstvl from x$ksppi x , x$ksppcv y where x.indx = y.indx and x.ksppinm like '\_db_block_numa%' escape '\' order by x.ksppinm;

KSPPINM KSPPSTVL
-----------------
_db_block_numa 1

Then asked to change the enable_NUMA_optimization to TRUE only on 11.2.0.3 and see if the same behavior exists. Logic was with _enable_NUMA_optimization=false and _db_block_numa=1 oracle would allocate only 1 shared segment for the SGA. (Couldn't understand why then change it to true). By default _enable_NUMA_optimization is false, could be found out with

select x.ksppinm, y.ksppstvl from x$ksppi x , x$ksppcv y where x.indx = y.indx and x.ksppinm like '\_enable_NUMA_optimization%' escape '\' order by x.ksppinm;

Changed it to true ask instructed but no change.

Following two metalink notes also listed
11G on HP Creates 6 Shared Memory Segments [Doc ID 601552.1]
Shared Memory Segment Is Split Up In Different Pieces With NUMA Setting [Doc ID 429872.1]

Update 06 January 2012
Oracle has finally reproduced the behavior in house. This will be logged as a bug. Blog will be updated with the findings.

Update 03 February 2012
Oracle has created a metalink note explaining this behavior. Why Multiple Shared Memory Segments are Created From 11.2.0.3 [ID 1399908.1]
This segment creation behavior change is the side effect of a fix for the unpublished bug 12654172. ~~Not yet known if this side effect is localised to 11.2.0.3 and will be "fixed" with a PSU or in 11.2.0.4 or 12.1~~
It seem this is the expected behavior going forward from 11.2.0.3. This means that one segment model described in 15566.1 won't be there anymore.

Configuring NTP Service on Linux

2011-11-16T05:26:00.000-08:00

Servers' time fall out of sync with one another after some time. This is problematic if the time gap between each server is wide such that it affects the execution of business logic. In RAC configuration this could even lead to node eviction.

The solution is to sync all servers with a NTP server that keep accurate time. For this to work the servers must be able to connect to the internet to access these NTP servers. But for security reason this may not always be possible. However it is possible to have a setup as shown below where only one server connect to the NTP servers and all others get their time sync from this "internal" server without having to connect to the internet.

It is assumed ntp is installed on the Linux servers if not install.

1. Backup the current /etc/ntp.conf file on all servers.

mv /etc/ntp.conf /etc/ntp.conf.orig

2. On the server that is going to act as the internal NTP server create a new ntp.conf file (make sure it's owned by root and permissions are 644) in /etc and add the following entries. Important parts are shown in bold

server  ntpserver1.org           
server  ntpserver2.org           

restrict ntpserver1.org  mask 255.255.255.255 nomodify notrap noquery
restrict ntpserver1.org  mask 255.255.255.255 nomodify notrap noquery

restrict 192.168.0.0 mask 255.255.255.0 nomodify notrap

restrict 127.0.0.1
restrict -6 ::1

# Undisciplined Local Clock. This is a fake driver intended for backup
# and when no outside source of synchronized time is available.
server  127.127.1.0     # local clock
fudge   127.127.1.0 stratum 10

driftfile /var/lib/ntp/drift

# Key file containing the keys and key identifiers used when operating
# with symmetric key cryptography.
keys /etc/ntp/keys

# Specify the key identifiers which are trusted.
#trustedkey 4 8 42

# Specify the key identifier to use with the ntpdc utility.
#requestkey 8

# Specify the key identifier to use with the ntpq utility.
#controlkey 8

ntpserver1/2.org are the NTP servers that are being used for synchronization. (netserver1/2.org used here is just an example).

The restirct line tells what is allowed for the NTP servers, in this case NTP servers are not allowed to modify nor query the local server. The mask 255.255.255.255 limits access to the single IP address of the remote NTP servers.

Third line is the key for allowing other servers to get the time from this server. This is done by removing the noquery line from the restrict and specifiying computers coming from which network segment are allowed to query. In this case all the servers in the local network would be able to get their time sync from this server.

3. Leave the other options as it is.

4. If the server time is lagging far behind the standard time, then use the ntpdate command to get the time updated allowing ntp service to start with a small time lag. To do this first stop the ntp service

/etc/init.d/ntpd stop

and then run

ntpdate -u ntpserver1.org
16 Nov 12:58:49 ntpdate[13104]: adjust time server 129.67.1.160 offset -0.070418 sec

Run the above command 2-3 times.

5. Start the ntp service and monitor the ntp synchronization in progress

/etc/init.d/ntpd start

watch -n 5 ntpq -p

Every 5.0s: ntpq -p             Wed Nov 16 13:08:44 2011

     remote           refid      st t when poll reach   delay   offset  jitter
==============================================================================
*ntpserver1.org 192.6.2.82     2 u    7   64  377    3.732  -67.808   0.520
+ntpserver2.org 192.6.2.74     2 u   61   64  377    3.755  -67.672  17.760
 LOCAL(0)        .LOCL.          10 l   53   64  377    0.000    0.000   0.001

6. On other servers (servers get their time sync from the internal NTP server) eg. App and DB servers on the above diagram, create a new /etc/ntp.conf file and add the following entries

server  internal_ntp.server.net

restrict internal_ntp.server.net   mask 255.255.255.255 nomodify notrap noquery

restrict 127.0.0.1
restrict -6 ::1

# Undisciplined Local Clock. This is a fake driver intended for backup
# and when no outside source of synchronized time is available.
server  127.127.1.0     # local clock
fudge   127.127.1.0 stratum 10

driftfile /var/lib/ntp/drift

# Key file containing the keys and key identifiers used when operating
# with symmetric key cryptography.
keys /etc/ntp/keys

# Specify the key identifiers which are trusted.
#trustedkey 4 8 42

# Specify the key identifier to use with the ntpdc utility.
#requestkey 8

# Specify the key identifier to use with the ntpq utility.
#controlkey 8

In this configuraiton the ntp server would be the hostname or the IP of the internal ntp server.

7. If this is a RAC server then before start the ntp service add -x to /etc/sysconfig/ntpd file(metalink note 551704.1)

# Drop root to id 'ntp:ntp' by default.
OPTIONS="-x -u ntp:ntp -p /var/run/ntpd.pid"

8. Start the ntp service and monitor the synchronization progress

Every 5.0s: ntpq -p Wed Nov 16 13:19:40 2011

     remote           refid      st t when poll reach   delay   offset  jitter
==============================================================================
 internal_ntp_IP    163.1.2.160      3 u    1   64    1    0.279    0.074   0.001
 LOCAL(0)        .LOCL.          10 l    -   64    0    0.000    0.000   0.001

Useful metalink notes

NTP Information and Setup [ID 1010136.1]
Ntpd Does not Use Defined NTP Server [ID 1178614.1]
An Example NTP Client Configuration to use with Oracle Clusterware 11gR2 [ID 1104473.1]
Linux OS Service ntpd [ID 551704.1]
How to Set Up a Network Time Protocol (NTP) Client in Solaris [ID 1005887.1]
CTSSD Runs in Observer Mode Even Though No Time Sync Software is Running [ID 1054006.1]
NTP leap second event causing Oracle Clusterware node reboot [ID 759143.1]

ASM for Standalone Server in 11gR2 with Role Separation

2011-11-11T07:15:00.000-08:00

One of the major differences in 11gR2 compared to previous version is the moving of ASM configuration to Grid Infrastructure. Therefore to setup a standalone database with ASM, grid infrastructure is needed.
In this blog the grid user will be used for installing and managing grid infrastructure and oracle user will be used for installing and managing the database software. This is not a comprehensive step by step guide, some general database installation steps are not shown here focusing mainly on the steps that are specific to the topic at hand. The software used here are 11gR2 (11.2.0.3)

1. Create the following groups in the OS

groupadd oinstall
groupadd dba
groupadd oper
groupadd asmadmin
groupadd asmdba
groupadd asmoper

and create the grid user and oracle user as follows

useradd -g oinstall -G asmadmin,asmdba,asmoper grid
useradd -g oinstall -G dba,oper,asmdba oracle

If Oralce user does not have the asmdba then ASM disk groups created by grid user won't be visible to Oracle user.

2. Create the grid infrastructure location and make the ownership as grid:oinstall

cd /opt/app/11.2.0/
ls -l
drwxr-xr-x 2 grid oinstall 4096 Nov  3 17:41 grid

3. Install and configure oracleasm libs. User grid user and asmdba as the oracleasm interface user and group

/etc/init.d/oracleasm configure -i
Configuring the Oracle ASM library driver.

This will configure the on-boot properties of the Oracle ASM library
driver.  The following questions will determine whether the driver is
loaded on boot and what permissions it will have.  The current values
will be shown in brackets ('[]').  Hitting  without typing an
answer will keep that current value.  Ctrl-C will abort.

Default user to own the driver interface []: grid
Default group to own the driver interface []: asmdba
Start Oracle ASM library driver on boot (y/n) [n]: y
Scan for Oracle ASM disks on boot (y/n) [y]: y
Writing Oracle ASM library driver configuration: done
Initializing the Oracle ASMLib driver:                     [  OK  ]
Scanning the system for Oracle ASMLib disks:               [  OK  ]
/etc/init.d/oracleasm createdisk DATA /dev/sdb1
Marking disk "DATA" as an ASM disk:                        [  OK  ]
 /etc/init.d/oracleasm createdisk FLASH /dev/sdc1
Marking disk "FLASH" as an ASM disk:                       [  OK  ]

4. Login as grid user and start the grid infrastructure installation and select grid infrastrcuture for standalone server.

5. Create a ASM diskgroup to store data files. On this step only one diskgroup could be created. Once the grid infrastrcuture is installed use ASMCA to create any additional diskgroups needed.

6. Select the OS group related ASM activities. If the OS groups mentioned in step 1 are created this step would come pre-selected as shown below.

7. When grid infrastructure is installed for RAC it is advised not to install it as a sub directory of ORACLE BASE. But in this case trying to install out side oracle base would give a warning as shown below.

The grid infrastructure path is selected as below.

8. Installing summary

9. Execute the root scripts when prompted

/opt/app/oracle/product/11.2.0/grid/root.sh
Performing root user operation for Oracle 11g

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

Enter the full pathname of the local bin directory: [/usr/local/bin]:
   Copying dbhome to /usr/local/bin ...
   Copying oraenv to /usr/local/bin ...
   Copying coraenv to /usr/local/bin ...


Creating /etc/oratab file...
Entries will be added to the /etc/oratab file as needed by
Database Configuration Assistant when a database is created
Finished running generic part of root script.
Now product-specific root actions will be performed.
Using configuration parameter file: /opt/app/oracle/product/11.2.0/grid/crs/install/crsconfig_params
Creating trace directory
LOCAL ADD MODE
Creating OCR keys for user 'grid', privgrp 'oinstall'..
Operation successful.
LOCAL ONLY MODE
Successfully accumulated necessary OCR keys.
Creating OCR keys for user 'root', privgrp 'root'..
Operation successful.
CRS-4664: Node rhel5 successfully pinned.
Adding Clusterware entries to inittab

rhel5     2011/11/03 18:15:01     /opt/app/oracle/product/11.2.0/grid/cdata/rhel5/backup_20111103_181501.olr
Successfully configured Oracle Grid Infrastructure for a Standalone Server

10. Once the root scripts are finished listener and ASM instance will be created.

11. Following commands could be used to stop and start the grid infrastructure related services

crsctl stop has
crsctl start has

12. ASM's SPFile will be residing inside ASM itself. This could be found out in number of ways, looking at the alert log of ASM when ASM starts

Machine:        x86_64
Using parameter settings in server-side spfile +DATA/asm/asmparameterfile/registry.253.766260991
System parameters with non-default values:
  large_pool_size          = 12M
  instance_type            = "asm"
  remote_login_passwordfile= "EXCLUSIVE"
  asm_diskgroups           = "FLASH"
  asm_diskgroups           = "DATA"
  asm_power_limit          = 1
  diagnostic_dest          = "/opt/app/oracle"

Or using the asmcmd's spget command which shows the spfile location registered with GnP profile

ASMCMD> spget
+DATA/asm/asmparameterfile/registry.253.766260991

To copy the ASM spfile to a file systme location use spcopy (use spmove to move it). Before executing the spcopy stop the diskgroups

srvctl stop diskgroup -g data[flash] ASMCMD> spcopy +DATA/asm/asmparameterfile/registry.253.766260991 /opt/app/oracle/product/11.2.0/grid/dbs/spfile+ASM.ora

and update the GnP profile with the new location using spset

ASMCMD> spset /opt/app/oracle/product/11.2.0/grid/dbs/spfile+ASM.ora
ASMCMD> spget
/opt/app/oracle/product/11.2.0/grid/dbs/spfile+ASM.ora

When ASM starts next the new location is listed as below

Machine:        x86_64
Using parameter settings in server-side spfile /opt/app/oracle/product/11.2.0/grid/dbs/spfile+ASM.ora
System parameters with non-default values:
  large_pool_size          = 12M
  instance_type            = "asm"
  remote_login_passwordfile= "EXCLUSIVE"
  asm_diskgroups           = "FLASH"
  asm_diskgroups           = "DATA"
  asm_power_limit          = 1
  diagnostic_dest          = "/opt/app/oracle"

With this concludes the installation of grid infrastructure and creation of ASM.

13. Login as Oracle user and start the database software installation.

14. Some locations may not have write permissions for Oracle user, permissions might be set as 755 grid : oinstall. Change permissions as 775 to allow oracle user the access to these location.

15. There is a pre-req check to see if grid user is also a member of the dba group. As per metalink note 1084186.1 "In case of stand alone set up, if the 11.2 Database be managed by Oracle Restart ( srvctl stop/start ), then 'grid' user should also be a part of 'dba' group."

16. Complete the installation.

17. Run dbca as Oracle user from the database software home. Creating database is straightforward. If anything can go wrong then it will be that ASM disk are not being shown when ASM is created for data file locations.

This will be because Oracle user is not in the asmdba group. Once added the disk will appear for selection. This concludes the creation of standalone database with ASM in 11gR2.

The database comes with Oracle restart configured as well as ASM as a target in the EM Console

Some useful metalink notes

DBCA Does Not Display ASM Disk Groups In 11.2 [ID 1177483.1] (issue seems to be fixed in 11.2.0.3)
Patch 11gR2 Grid Infrastructure Standalone (Oracle Restart) [ID 1089476.1]
11gR2 (11.2.0.1) Unable To Create ASM Spfile Standalone [ID 1082332.1]
Database Creation on 11.2 Grid Infracture with Role Separation ( ORA-15025, KFSG-00312, ORA-15081 ) [ID 1084186.1]
How to change ownership and group memberships of Grid home in 11.2 Standalone [ID 1171674.1]
Ora-01115, Ora-01110, Ora-15081 When Connecting DB with non-dba Group Users [ID 1372571.1]
ASM 11.2 Configuration KIT (ASM 11gR2 Installation & Configuration, Deinstallation, Upgrade, ASM Job Role Separation (Standalone Only). [ID 1092213.1]

Roll Foward a Physical Standby on 11gR2

2011-10-20T08:13:00.000-07:00

Previous posts showed how to do roll forward 10gR2 and 11gR1 physical standby databases.
This shows how to roll forward a 11gR2 physical standby that's in RAC configuration setup earlier.

In this case archive log files were deleted on primary and there's no other way to bring the standby up to date with the primary.
Current dataguard configuration is

DGMGRL> show configuration

Configuration - rac11g2_dgb

  Protection Mode: MaxPerformance
  Databases:
    rac11g2  - Primary database
    rac11g2s - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS

1. Identify the archive gap

SQL> select * from v$archive_gap;

   THREAD# LOW_SEQUENCE# HIGH_SEQUENCE#
---------- ------------- --------------
         2           901            901

2. Stop all but one standby RAC instances

srvctl stop instance -d rac11g2s -i rac11g2s2

3. Stop log apply on the standby

DGMGRL> edit database rac11g2s set state='APPLY-OFF';
Succeeded.

4. Find the current SCN on the standby

SQL> SELECT CURRENT_SCN FROM V$DATABASE;

CURRENT_SCN
-----------
   45716408

5. Using the SCN identified in the above step create an incrimental backup in the primary database

RMAN> BACKUP INCREMENTAL FROM SCN 45716408 DATABASE FORMAT '/home/oracle/forstandby%U' tag 'forstandby';

Starting backup at 20-OCT-11

using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=46 instance=rac11g21 device type=DISK
backup will be obsolete on date 27-OCT-11
archived logs will not be kept or backed up
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00002 name=+DATA/rac11g2/datafile/sysaux.257.740770047
input datafile file number=00001 name=+DATA/rac11g2/datafile/system.256.740770045
input datafile file number=00006 name=+DATA/rac11g2/datafile/undotbs3.276.745423577
input datafile file number=00005 name=+DATA/rac11g2/datafile/undotbs2.264.740770355
input datafile file number=00003 name=+DATA/rac11g2/datafile/abc.280.755611691
input datafile file number=00004 name=+DATA/rac11g2/datafile/users.259.740770049
channel ORA_DISK_1: starting piece 1 at 20-OCT-11
channel ORA_DISK_1: finished piece 1 at 20-OCT-11
piece handle=/home/oracle/forstandby2fmpj6s7_1_1 tag=FORSTANDBY comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:35

using channel ORA_DISK_1
backup will be obsolete on date 27-OCT-11
archived logs will not be kept or backed up
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 20-OCT-11
channel ORA_DISK_1: finished piece 1 at 20-OCT-11
piece handle=/home/oracle/forstandby2gmpj6tb_1_1 tag=FORSTANDBY comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 20-OCT-11

6. Copy the created backup files to standby using scp, ftp or any other file transfer utility.

7. Catalog backup files on standby

RMAN> catalog start with '/home/oracle/forstand';

using target database control file instead of recovery catalog
searching for all files that match the pattern /home/oracle/forstand

List of Files Unknown to the Database
=====================================
File Name: /home/oracle/forstandby2fmpj6s7_1_1
File Name: /home/oracle/forstandby2gmpj6tb_1_1

Do you really want to catalog the above files (enter YES or NO)? yes
cataloging files...
cataloging done

List of Cataloged Files
=======================
File Name: /home/oracle/forstandby2fmpj6s7_1_1
File Name: /home/oracle/forstandby2gmpj6tb_1_1

8. Data guard concept and administration guide states "Connect to the standby database as the RMAN target and execute the REPORT SCHEMA statement to ensure that the standby database site is automatically registered and that the files names at the standby site are displayed". Doesn't say registered in what, but safe to assume talking about the recovery catalog. In this case recovery catalog is not used but run the command and make a note of the values as these will be used later on.

RMAN> report schema;

RMAN-06139: WARNING: control file is not current for REPORT SCHEMA
Report of database schema for database with db_unique_name RAC11G2S

List of Permanent Datafiles
===========================
File Size(MB) Tablespace           RB segs Datafile Name
---- -------- -------------------- ------- ------------------------
1    720      SYSTEM               ***     +DATA/rac11g2s/datafile/system.258.754586581
2    1290     SYSAUX               ***     +DATA/rac11g2s/datafile/sysaux.259.754586467
3    10       ABC                  ***     +DATA/rac11g2s/datafile/abc.287.755615113
4    5        USERS                ***     +DATA/rac11g2s/datafile/users.276.754586727
5    150      UNDOTBS2             ***     +DATA/rac11g2s/datafile/undotbs2.261.754586713
6    600      UNDOTBS3             ***     +DATA/rac11g2s/datafile/undotbs3.262.754586657

List of Temporary Files
=======================
File Size(MB) Tablespace           Maxsize(MB) Tempfile Name
---- -------- -------------------- ----------- --------------------
1    20       TEMP                 32767       +DATA/rac11g2s/tempfile/temp.288.755615251

9. Restore the standby controlfile using the backup file (shown in bold on the backup output earlier) and mount the database

RMAN> restore standby controlfile from '/home/oracle/forstandby2gmpj6tb_1_1';

Starting restore at 20-OCT-11
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=17 instance=rac11g2s1 device type=DISK

channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:05
output file name=+DATA/rac11g2s/controlfile/current.257.754586439
output file name=+FLASH/rac11g2s/controlfile/current.482.754586439
Finished restore at 20-OCT-11

RMAN> alter database mount;

database mounted
released channel: ORA_DISK_1

10. Run a report schema again and see that datafile names reported are the ones of the primary databae

RMAN> report schema;

RMAN-06139: WARNING: control file is not current for REPORT SCHEMA
Report of database schema for database with db_unique_name RAC11G2S

List of Permanent Datafiles
===========================
File Size(MB) Tablespace           RB segs Datafile Name
---- -------- -------------------- ------- ------------------------
1    0        SYSTEM               ***     +DATA/rac11g2s/datafile/system.256.740770045
2    0        SYSAUX               ***     +DATA/rac11g2s/datafile/sysaux.257.740770047
3    0        ABC                  ***     +DATA/rac11g2s/datafile/abc.280.755611691
4    0        USERS                ***     +DATA/rac11g2s/datafile/users.259.740770049
5    0        UNDOTBS2             ***     +DATA/rac11g2s/datafile/undotbs2.264.740770355
6    0        UNDOTBS3             ***     +DATA/rac11g2s/datafile/undotbs3.276.745423577

List of Temporary Files
=======================
File Size(MB) Tablespace           Maxsize(MB) Tempfile Name
---- -------- -------------------- ----------- --------------------
1    20       TEMP                 32767       +DATA/rac11g2s/tempfile/temp.263.740770177

11. Catalog the datafiles to the original standby datafile names

RMAN> catalog start with '+DATA/rac11g2s/datafile';

searching for all files that match the pattern +DATA/rac11g2s/datafile

List of Files Unknown to the Database
=====================================
File Name: +data/RAC11G2S/DATAFILE/SYSAUX.259.754586467
File Name: +data/RAC11G2S/DATAFILE/SYSTEM.258.754586581
File Name: +data/RAC11G2S/DATAFILE/UNDOTBS3.262.754586657
File Name: +data/RAC11G2S/DATAFILE/UNDOTBS2.261.754586713
File Name: +data/RAC11G2S/DATAFILE/USERS.276.754586727
File Name: +data/RAC11G2S/DATAFILE/ABC.287.755615113

Do you really want to catalog the above files (enter YES or NO)? yes
cataloging files...
cataloging done

List of Cataloged Files
=======================
File Name: +data/RAC11G2S/DATAFILE/SYSAUX.259.754586467
File Name: +data/RAC11G2S/DATAFILE/SYSTEM.258.754586581
File Name: +data/RAC11G2S/DATAFILE/UNDOTBS3.262.754586657
File Name: +data/RAC11G2S/DATAFILE/UNDOTBS2.261.754586713
File Name: +data/RAC11G2S/DATAFILE/USERS.276.754586727
File Name: +data/RAC11G2S/DATAFILE/ABC.287.755615113

12. Run switch database to copy to update the control file with the changes

RMAN> switch database to copy;

datafile 1 switched to datafile copy "+DATA/rac11g2s/datafile/system.258.754586581"
datafile 2 switched to datafile copy "+DATA/rac11g2s/datafile/sysaux.259.754586467"
datafile 3 switched to datafile copy "+DATA/rac11g2s/datafile/abc.287.755615113"
datafile 4 switched to datafile copy "+DATA/rac11g2s/datafile/users.276.754586727"
datafile 5 switched to datafile copy "+DATA/rac11g2s/datafile/undotbs2.261.754586713"
datafile 6 switched to datafile copy "+DATA/rac11g2s/datafile/undotbs3.262.754586657"

13. Recover the database with no redo

RMAN> recover database noredo;

Starting recover at 20-OCT-11
using channel ORA_DISK_1
channel ORA_DISK_1: starting incremental datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
destination for restore of datafile 00001: +DATA/rac11g2s/datafile/system.258.754586581
destination for restore of datafile 00002: +DATA/rac11g2s/datafile/sysaux.259.754586467
destination for restore of datafile 00003: +DATA/rac11g2s/datafile/abc.287.755615113
destination for restore of datafile 00004: +DATA/rac11g2s/datafile/users.276.754586727
destination for restore of datafile 00005: +DATA/rac11g2s/datafile/undotbs2.261.754586713
destination for restore of datafile 00006: +DATA/rac11g2s/datafile/undotbs3.262.754586657
channel ORA_DISK_1: reading from backup piece /home/oracle/forstandby2fmpj6s7_1_1
channel ORA_DISK_1: piece handle=/home/oracle/forstandby2fmpj6s7_1_1 tag=FORSTANDBY
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:07

Finished recover at 20-OCT-11

14. At this stage should have cleared the log files as in step 10 herebut still without clearing the old logs when log apply is enabled new log files will be created automatically. Old log files will remain in the ASM taking up space and could be manually removed using asmcmd rm.

15. Enable log apply on the standby and verify log files are getting applied

DGMGRL> edit database rac11g2s set state='APPLY-ON';

   THREAD#  SEQUENCE# APPLIED
---------- ---------- ---------
         2        907 YES
         2        908 YES
         2        909 YES
         2        910 YES
         2        910 YES

Upgrading from 11.2.0.2 to 11.2.0.3

2011-10-14T09:15:00.000-07:00

Upgrading from 11.2.0.2 to 11.2.0.3 is not much different from upgrading from 11.2.0.1 to 11.2.0.2 (part 2). Same as before (with upgrading to 11.2.0.2) there's some pre-reqs that need to be done. Continuing the tradition started with 11gR2 Oracle doesn't give a single read me file with all the necessary information for upgrade. One has to read several user guides and metalink notes to get the full picture. Below are some extracts taken from upgrade guide, asm guide and grid infrastructure installation guide.

Oracle Clusterware Upgrade Configuration Force Feature
If nodes become unreachable in the middle of an upgrade, starting with release 11.2.0.3, you can run the rootupgrade.sh script with the -force flag to force an upgrade to complete.

Starting with Oracle Grid Infrastructure 11g release 2 (11.2.0.3) and later, you can use the CVU healthcheck command option to check your Oracle Clusterware and Oracle Database installations for their compliance with mandatory requirements and best practices guidelines, and to check to ensure that they are functioning properly.

Known Issue with the Deinstallation Tool for This Release
Cause: After upgrading from 11.2.0.1 or 11.2.0.2 to 11.2.0.3, deinstallation of the Oracle home in the earlier release of Oracle Database may result in the deletion of the old Oracle base that was associated with it. This may also result in the deletion of data files, audit files, etc., which are stored under the old Oracle base.
Action: Before deinstalling the Oracle home in the earlier release, edit the orabase_cleanup.lst file found in the $Oracle_Home/utl directory and remove the "oradata" and "admin" entries. Then, deinstall the Oracle home using the 11.2.0.3 deinstallation tool.

To upgrade existing Oracle Grid Infrastructure installations from 11.2.0.2 to a later release, you must apply patch 11.2.0.2.1 (11.2.0.2 PSU 1) or later.
To upgrade existing 11.2.0.1 Oracle Grid Infrastructure installations to Oracle Grid Infrastructure to any later version (11.2.0.2 or 11.2.0.3), you must patch the release 11.2.0.1 Oracle Grid Infrastructure home (11.2.0.1.0) with the 9706490 patch.

To upgrade existing 11.1 Oracle Clusterware installations to Oracle Grid Infrastructure 11.2.0.3 or later, you must patch the release 11.1 Oracle Clusterware home with the patch for bug 7308467.

Oracle recommends that you leave Oracle RAC instances running. When you start the root script on each node, that node's instances are shut down and then started up again by the rootupgrade.sh script. If you upgrade from release 11.2.0.1 to any later version (11.2.0.2 or 11.2.0.3), then all nodes are selected by
default. You cannot select or de-select the nodes. For single instance Oracle Databases on the cluster, only those that use Oracle ASM need to be shut down. Listeners do not need to be shut down.

From metalink notes

Actions For DST Updates When Upgrading To Or Applying The 11.2.0.3 Patchset [ID 1358166.1]

If your current RDBMS time timezone version is 14 , install 11.2.0.3 in a new home and update the 11.2.0.2 or 11.2.0.1 database to 11.2.0.2.
You can skip any DST related sections in the patchset documentation , there is no need to apply DST patches or check for DST issues for the update to 11.2.0.3

If your current RDBMS time timezone version is lower than 14 , install 11.2.0.3 in a new home and update the 11.2.0.2 or 11.2.0.1 database to 11.2.0.3
You can skip any DST related sections in the patchset documentation , there is no need to apply DST patches or check for DST issues for the update to 11.2.0.3

If your current RDBMS time timezone version is higher than 14, then there's some work to be done read the above metalink note.

In this case database was 11.2.0.2

SQL> SELECT version FROM v$timezone_file;

   VERSION
----------
        14

Before starting the upgrade using cluster verification tool it's now possible to validate the readiness to upgrade.

./runcluvfy.sh stage -pre crsinst -upgrade -n hpc3 -rolling -src_crshome /flash/11.2.0/grid11.2.0.2 -dest_crshome /flash/11.2.0/grid11.2.0.3 -dest_version 11.2.0.3.0 -fixup -fixupdir /home/oracle/fixupscript -verbose

Prior to upgrading to 11.2.0.3 a patch should be applied to 11.2.0.2 (similar to 11.2.0.2) only difference is this patch number wasn't mentioned in any of the documents referenced (which means some more reading is required to get the complete picture) but luckily the pre-req check flags this.

Applying patch 12539000

The patch itself has some issues when it comes to applying.

1. Applying using opatch auto, which is suppse to apply to both GI Home and Oracle Home only applied to Oracle Home.

2. Read me says to run the /12539000/custom/server/12539000/custom/scripts/prepatch.sh but this directory structure and the file is missing.

3. So finally had to apply the patch with opatch auto but one home at a time. (opatch auto PATH_TO_PATCH_DIRECTORY -oh GI_HOME or OH

Another issue was shmmni was flaged as not configured (this already had an upgrade to 11.2.0.2 and value was set from the begining). Running the fixup script gives the output below which doesn't modify but makes the warning disappear

# /tmp/CVU_11.2.0.3.0_oracle/runfixup.sh
Response file being used is :/tmp/CVU_11.2.0.3.0_oracle/fixup.response
Enable file being used is :/tmp/CVU_11.2.0.3.0_oracle/fixup.enable
Log file location: /tmp/CVU_11.2.0.3.0_oracle/orarun.log
Setting Kernel Parameters...
The value for shmmni in response file is not greater than value of shmmni for current session. Hence not changing it.

Apart from these upgrade is straightforward.

By default all the nodes will be selected for upgrade (this cluster only has one node). Oracle recommends to upgrade both GI and ASM at the same time. This option has been selected for this upgrade.

SCAN IP is set on the /etc/hosts but the pre-req checks if this IP could be resolved using nslookup and flags a warning when cannot. This could be ignored and proceeded. Metalink note 1212703.1 mentions the multicast requirements. In this upgrade all these were ignored (on production environments should resolve these issues before proceeding)

When prompted run the rootupgrade.

# /flash/11.2.0/grid11.2.0.3/rootupgrade.sh
Performing root user operation for Oracle 11g

The following environment variables are set as:
    ORACLE_OWNER= oracle
    ORACLE_HOME=  /flash/11.2.0/grid11.2.0.3

Enter the full pathname of the local bin directory: [/usr/local/bin]:
The contents of "dbhome" have not changed. No need to overwrite.
The contents of "oraenv" have not changed. No need to overwrite.
The contents of "coraenv" have not changed. No need to overwrite.

Entries will be added to the /etc/oratab file as needed by
Database Configuration Assistant when a database is created
Finished running generic part of root script.
Now product-specific root actions will be performed.
Using configuration parameter file: /flash/11.2.0/grid11.2.0.3/crs/install/crsconfig_params
Creating trace directory
User ignored Prerequisites during installation

ASM upgrade has started on first node.

CRS-2791: Starting shutdown of Oracle High Availability Services-managed resources on 'hpc3'
CRS-2673: Attempting to stop 'ora.crsd' on 'hpc3'
CRS-2790: Starting shutdown of Cluster Ready Services-managed resources on 'hpc3'
CRS-2673: Attempting to stop 'ora.CLUSTERDG.dg' on 'hpc3'
CRS-2673: Attempting to stop 'ora.registry.acfs' on 'hpc3'
CRS-2673: Attempting to stop 'ora.clusdb.db' on 'hpc3'
CRS-2673: Attempting to stop 'ora.LISTENER_SCAN1.lsnr' on 'hpc3'
CRS-2673: Attempting to stop 'ora.oc4j' on 'hpc3'
CRS-2673: Attempting to stop 'ora.LISTENER.lsnr' on 'hpc3'
CRS-2673: Attempting to stop 'ora.cvu' on 'hpc3'
CRS-2677: Stop of 'ora.LISTENER.lsnr' on 'hpc3' succeeded
CRS-2673: Attempting to stop 'ora.hpc3.vip' on 'hpc3'
CRS-2677: Stop of 'ora.LISTENER_SCAN1.lsnr' on 'hpc3' succeeded
CRS-2673: Attempting to stop 'ora.scan1.vip' on 'hpc3'
CRS-2677: Stop of 'ora.scan1.vip' on 'hpc3' succeeded
CRS-2677: Stop of 'ora.hpc3.vip' on 'hpc3' succeeded
CRS-2677: Stop of 'ora.registry.acfs' on 'hpc3' succeeded
CRS-2677: Stop of 'ora.clusdb.db' on 'hpc3' succeeded
CRS-2673: Attempting to stop 'ora.DATA.dg' on 'hpc3'
CRS-2673: Attempting to stop 'ora.FLASH.dg' on 'hpc3'
CRS-2677: Stop of 'ora.DATA.dg' on 'hpc3' succeeded
CRS-2677: Stop of 'ora.FLASH.dg' on 'hpc3' succeeded
CRS-2677: Stop of 'ora.oc4j' on 'hpc3' succeeded
CRS-2677: Stop of 'ora.cvu' on 'hpc3' succeeded
CRS-2677: Stop of 'ora.CLUSTERDG.dg' on 'hpc3' succeeded
CRS-2673: Attempting to stop 'ora.asm' on 'hpc3'
CRS-2677: Stop of 'ora.asm' on 'hpc3' succeeded
CRS-2673: Attempting to stop 'ora.ons' on 'hpc3'
CRS-2677: Stop of 'ora.ons' on 'hpc3' succeeded
CRS-2673: Attempting to stop 'ora.net1.network' on 'hpc3'
CRS-2677: Stop of 'ora.net1.network' on 'hpc3' succeeded
CRS-2792: Shutdown of Cluster Ready Services-managed resources on 'hpc3' has completed
CRS-2677: Stop of 'ora.crsd' on 'hpc3' succeeded
CRS-2673: Attempting to stop 'ora.ctssd' on 'hpc3'
CRS-2673: Attempting to stop 'ora.evmd' on 'hpc3'
CRS-2673: Attempting to stop 'ora.asm' on 'hpc3'
CRS-2673: Attempting to stop 'ora.drivers.acfs' on 'hpc3'
CRS-2673: Attempting to stop 'ora.mdnsd' on 'hpc3'
CRS-2677: Stop of 'ora.asm' on 'hpc3' succeeded
CRS-2673: Attempting to stop 'ora.cluster_interconnect.haip' on 'hpc3'
CRS-2677: Stop of 'ora.drivers.acfs' on 'hpc3' succeeded
CRS-2677: Stop of 'ora.evmd' on 'hpc3' succeeded
CRS-2677: Stop of 'ora.cluster_interconnect.haip' on 'hpc3' succeeded
CRS-2677: Stop of 'ora.mdnsd' on 'hpc3' succeeded
CRS-2677: Stop of 'ora.ctssd' on 'hpc3' succeeded
CRS-2673: Attempting to stop 'ora.cssd' on 'hpc3'
CRS-2677: Stop of 'ora.cssd' on 'hpc3' succeeded
CRS-2673: Attempting to stop 'ora.diskmon' on 'hpc3'
CRS-2673: Attempting to stop 'ora.crf' on 'hpc3'
CRS-2677: Stop of 'ora.diskmon' on 'hpc3' succeeded
CRS-2677: Stop of 'ora.crf' on 'hpc3' succeeded
CRS-2673: Attempting to stop 'ora.gipcd' on 'hpc3'
CRS-2677: Stop of 'ora.gipcd' on 'hpc3' succeeded
CRS-2673: Attempting to stop 'ora.gpnpd' on 'hpc3'
CRS-2677: Stop of 'ora.gpnpd' on 'hpc3' succeeded
CRS-2793: Shutdown of Oracle High Availability Services-managed resources on 'hpc3' has completed
CRS-4133: Oracle High Availability Services has been stopped.
OLR initialization - successful
Replacing Clusterware entries in inittab
clscfg: EXISTING configuration version 5 detected.
clscfg: version 5 is 11g Release 2.
Successfully accumulated necessary OCR keys.
Creating OCR keys for user 'root', privgrp 'root'..
Operation successful.
Started to upgrade the Oracle Clusterware. This operation may take a few minutes.
Started to upgrade the CSS.
Started to upgrade the CRS.
The CRS was successfully upgraded.
Oracle Clusterware operating version was successfully set to 11.2.0.3.0

ASM upgrade has finished on last node.

PRKO-2116 : OC4J is already enabled
Configure Oracle Grid Infrastructure for a Cluster ... succeeded

Check the versions

crsctl query crs activeversion
Oracle Clusterware active version on the cluster is [11.2.0.3.0]

crsctl query crs softwareversion
Oracle Clusterware version on node [hpc3] is [11.2.0.3.0]

crsctl query crs releaseversion
Oracle High Availability Services release version on the local node is [11.2.0.3.0]

As mentioned in the begining of this blog cluvfy could be used to verify the compliance of clusterware and database.

./cluvfy comp healthcheck -collect cluster -bestpractice -deviations -html -save -savedir /home/oracle

This command will create a html file in /home/oracle listing the findings of the commands. There are serveral other options related to this and could use to verify the database as well. To verify the database must first run the $GI_HOME/cv/admin/cvusys.sql script to create the necessary user.

After rootupgrade continue rest of the configuration

This concludes the GI upgrade. Next is to upgrade the Oracle Home and the database. As with 11.2.0.2 upgrade this is done as an out of place upgrade. Both software and database is upgraded at the same time.

When prompted run the root.sh

/opt/app/oracle/product/11.2.0/clusdb_3/root.sh
Performing root user operation for Oracle 11g

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

Enter the full pathname of the local bin directory: [/usr/local/bin]:
The contents of "dbhome" have not changed. No need to overwrite.
The contents of "oraenv" have not changed. No need to overwrite.
The contents of "coraenv" have not changed. No need to overwrite.

Entries will be added to the /etc/oratab file as needed by
Database Configuration Assistant when a database is created
Finished running generic part of root script.
Now product-specific root actions will be performed.
Finished product-specific root actions.

Summary will be shown once the upgrade is completed.

COMP_NAME                           STATUS   VERSION
----------------------------------- -------- ------------
OWB                                 VALID    11.2.0.1.0
Oracle Application Express          VALID    3.2.1.00.10
Oracle Enterprise Manager           VALID    11.2.0.3.0
OLAP Catalog                        VALID    11.2.0.3.0
Spatial                             VALID    11.2.0.3.0
Oracle Multimedia                   VALID    11.2.0.3.0
Oracle XML Database                 VALID    11.2.0.3.0
Oracle Text                         VALID    11.2.0.3.0
Oracle Expression Filter            VALID    11.2.0.3.0
Oracle Rules Manager                VALID    11.2.0.3.0
Oracle Workspace Manager            VALID    11.2.0.3.0
Oracle Database Catalog Views       VALID    11.2.0.3.0
Oracle Database Packages and Types  VALID    11.2.0.3.0
JServer JAVA Virtual Machine        VALID    11.2.0.3.0
Oracle XDK                          VALID    11.2.0.3.0
Oracle Database Java Packages       VALID    11.2.0.3.0
OLAP Analytic Workspace             VALID    11.2.0.3.0
Oracle OLAP API                     VALID    11.2.0.3.0
Oracle Real Application Clusters    VALID    11.2.0.3.0

From patch history (using ADMon)

This concludes the upgrade to 11.2.0.3.

If automatic shared memory managment (ASMM) is used then read the post Multiple Shared Memory Segments Created by Default on 11.2.0.3

Upgrading from 11.2.0.1 to 11.2.0.2 - Part 2

2011-10-14T03:44:00.000-07:00

Metalink notes related to 11.2.0.1 to 11.2.0.2 upgrade

Complete checklist for out-of-place manual upgrade from 11.2.0.1 to 11.2.0.2 [ID 1276368.1]
Important Changes to Oracle Database Patch Sets Starting With 11.2.0.2 [ID 1189783.1]
Things to Consider Before Upgrading to 11.2.0.2 Grid Infrastructure [ID 1312225.1]
ASM 11gR2: How To Upgrade An ASM Instance To Release 11gR2 (11.2)? [ID 950200.1]
Oracle Grid Infrastructure 11.2.0.2 Installation or Upgrade may fail due to Multicasting Requirement [ID 1212703.1]
Oracle Database Upgrade Path Reference List [ID 730365.1]
Upgrade Advisor: Database from 10.2 to 11.2 [ID 251.1]
Complete Checklist for Manual Upgrades to 11gR2 [ID 837570.1]

Latch Free Waits

2011-10-05T07:56:00.000-07:00

A test development running 11gR1 (11.1.0.7) started to show high number of latch free waits during a load test.

Looking at the AWR report it could be seen that latch free waits is part of the top 5 events.

Going through the latch activity section of the AWR report it could seen Result Cache: Latch and SQL memory manager latch having the large wait times compared to other latch types.

Result Cache latch is used for controlling access to the result cache.

Used GV$RESULT_CACHE_OBJECTS to identify the objects that are being cached. In this case there was only one object which was a function. Remove the caching option from the function and ran the load test again.

In the second test the latch free waits were less and concurrency (cache buffer chains) were predominant.

Top 5 wait events shows that latch free events have decreased compared to previous time.

The high number of cache buffer chains are as a result of multiple sessions running the same sql and accessing same set of blocks (hot blocks). It is this sql that was inside the function that had used result cached feature. Since result caching was removed from the function sql was getting executed rather than result being served from the cache.

Predominant latch in the second period was cache buffer chain.

Comparison AWR report shows DB Time being reduced while DB CPU time increasing.

Wait Event comparison captures the reduction of the latch free and increase of cache buffer chain.

Latch wait time comparison

Mutex Sleep Summary in test 1.

Mutex Sleep Summary in test 2.

Comparison of the above two summaries shows during the second period mutex were obtained much quicker and far less sleeps to get the mutex.

Take home point is even though result cache is expected to reduce the load on the database by preventing the execution of the SQL and serving sessions using the cache, it could have other effects/bottlenecks/issues etc that could negate the gains.

Another event that was predominant in these test was SQL Memory Manager latch. Metalink note 9732503.8 mentions Bug 9732503 with regard to this latch in certain platforms and Oracle versions. More importantly it also states

This issue is often caused when the value of pga_aggregate_target is set too low, ie. when the statistic "over allocation count" is non zero in v$pgastat. When this is the case, increase pga_aggregate_target such that it is set to at least "maximum PGA allocated" in v$pgastat. You can find more information on tuning pga_aggregate_target in the Oracle tuning guide.

Metalink notes useful in diagnosing latch free wait events

WAITEVENT: "latch free" Reference Note [ID 34576.1]
How to Identify Which Latch is Associated with a "latch free" wait [ID 413942.1]

SQL Sinppet : Size of BLOB for each row.

2011-09-13T06:26:00.000-07:00

Assuming there's a table with a blob column to get the size of each row's blob size

select object_name,DBMS_LOB.GETLENGTH(blob_column) from table;

select object_name,lengthb(blob_column) from table;

Scalar subqueries to reduce PL/SQL to SQL switching

2011-08-30T03:48:00.000-07:00

Tom Kyte has written an excellent article on the current issue (September/October) of Oracle Magazine which shows how to reduce switching between pl/sql and sql using scalar subquery.

Tom describes a scalar subquery as "What exactly is a scalar subquery? It is a subquery in a SQL statement that returns exactly one column and zero rows or one row. That single column can be a complex object type, so it can consist of many attributes, but the subquery returns a single scalar value (or NULL if the subquery returns zero records). A scalar subquery can be used anywhere a literal could have been used."

On the article there are many comparisons of using scalar subquery vs deterministic pl/sql, result cache and etc.
Below is the simplest case of performance comparison with and without scalar subquery.

1.Create a big table to query

SQL> create table bigtable as select * from all_objects nologging;

2. Create the function that will be used in this test case

create or replace function mytest(p_owner varchar2) return number is

obj_count number :=0;

begin 

 select count(*) into obj_count from bigtable bt where bt.owner = p_owner; 

 return obj_count; 

end; /

3. Run the first test case without scalar subquery

set timing on

variable cpu number;

begin

:cpu := dbms_utility.get_cpu_time;

end;

/



SQL> select owner,mytest(owner) from bigtable;

....

SYS                                    31328

SYS                                    31328



72232 rows selected. -- this is all the rows in the table

Elapsed: 00:05:11.01



SQL> select dbms_utility.get_cpu_time-:cpu cpu_hsecs from dual;



CPU_HSECS

---------

30931

4. Second test case with scalar subquery caching

set timing on

variable cpu number;

begin

:cpu := dbms_utility.get_cpu_time;

end;

/



SQL> select owner,(select mytest(owner) from dual) mo from bigtable;



SYS                                                    31328

SYS                                                    31328

SYS                                                    31328



72232 rows selected.  -- all the rows in the table



Elapsed: 00:00:17.05



SQL> select dbms_utility.get_cpu_time-:cpu cpu_hsecs from dual;

CPU_HSECS

---------

56

As seen from the outputs both cpu usage and elapsed time has reduced. How this happens has been explained in the above mentioned article.

ksfd: async disk io

2011-08-26T06:30:00.000-07:00

ksfd: async disk io was appearing from time to time somewhat predominately (compared to other waits events) on standard edition 11gR1 two node cluster.

Monitoring with APConsole showed the following.

Following metalink notes are related to this wait event.

High waits for "ksfd: async disk io" [ID 1326634.1]
Bug 8660422 - "unspecified wait event" and/or "ksfd: async disk IO" after applying patches that include fix for Bug 7510766 [ID 8660422.8]

This database has the PSU 11.1.0.7.6 applied. Blog post will be updated when PSU 11.1.0.7.7 or the patch 8660422 is applied.

Update on 19th November 2011

After October 2011 PSU (11.1.0.7.9) was applied ksfd: async disk io waits disappeared. Graph (generated with ADMon)below shows the waits for two week period. Wait was happening daily on both or at least one instance (this was a two node RAC) but stopped since the applying of the patch (Patch was applied on 17th).

Removing Datafiles from a Tablespace

2011-08-04T04:29:00.000-07:00

There are serveral ways to remove a datafile, one is with an offline drop and other is using alter tablespace. The offline drop is to be used inconjunction with drop tablespace, that is is expected the tablespace will also be dropped. If the intention is just to drop one or more datafiles that is part of a tablespace then alter tablespace drop datafile must be used which was introduced with 10gR2. There are several constraints to dropping a datafile they are (from Oracle documentation)
The following are restrictions for dropping datafiles and tempfiles:
The database must be open.

If a datafile is not empty, it cannot be dropped. If you must remove a datafile that is not empty and that cannot be made empty by dropping schema objects, you must drop the tablespace that contains the datafile.

You cannot drop the first or only datafile in a tablespace. This means that DROP DATAFILE cannot be used with a bigfile tablespace.

You cannot drop datafiles in a read-only tablespace.

You cannot drop datafiles in the SYSTEM tablespace.

If a datafile in a locally managed tablespace is offline, it cannot be dropped..

Situation is that there are segment(s) where extents are spread across multiple datafiles and now it is decided to move all the extents into single datafile.

SELECT x.*,  COUNT(*) FROM
  (SELECT owner, (SELECT name FROM v$datafile WHERE file#=file_id ) AS filename ,
    segment_name ,    segment_type ,    tablespace_name
  FROM dba_extents  WHERE tablespace_name='TEST'  ) x
group by owner,filename,segment_name,segment_type,tablespace_name ORDER BY 1,4 DESC,  3;

OWNER  FILENAME                                    SEGMENT_ SEGMENT_ TABLE   COUNT(*)
------ ------------------------------------------- -------- -------- ----- ----------
ASANGA +DATA/rac11g1/datafile/test.372.758295243   FULT     TABLE    TEST          19
ASANGA +FLASH/rac11g1/datafile/test.265.758295919  FULT     TABLE    TEST           1
ASANGA +FLASH/rac11g1/datafile/test.265.758295919  FULINDX  INDEX    TEST          12

For testing this could be created with step 1-4 in an earlier post.

To drop the datafile in the flash diskgroup move the segments (table and indexes) to another tablespace either through alter table .. move (there are other ways too) and alter index rebuild .. tablespace (just one way to move an index). Depending on the number of segments in the datafile that is in flash diskgroup this could take some time and if nologging is used could generate lot of redo.

Once all the extents are moved out drop the datafile from the tablespace. If the datafile is not empty following error will be shown

SQL> alter tablespace test drop datafile '+FLASH/rac11g1/datafile/test.265.758295919';
alter tablespace test drop datafile '+FLASH/rac11g1/datafile/test.265.758295919'
*
ERROR at line 1:
ORA-03262: the file is non-empty

To move segments

SQL> alter table fult move tablespace users;

Table altered.

SQL> alter index fulindx rebuild online tablespace users;

Index altered.

After the move verify no extents are on the datafiles in question

SQL> SELECT x.*,  COUNT(*) FROM
  2    (SELECT owner, (SELECT name FROM v$datafile WHERE file#=file_id ) AS filename ,
  3      segment_name ,    segment_type ,    tablespace_name
  4    FROM dba_extents  WHERE tablespace_name='TEST'  ) x
  5  group by owner,filename,segment_name,segment_type,tablespace_name ORDER BY 1,4 DESC,  3;

no rows selected

Drop the datafile

SQL> alter tablespace test drop datafile '+FLASH/rac11g1/datafile/test.265.758295919';

Tablespace altered.

Move the segments back into the tablespace which now has only the desired datafile(s).

SQL> alter table fult move tablespace test;

Table altered.

SQL> alter index fulindx rebuild online tablespace test;

Index altered.

SQL> SELECT x.*,  COUNT(*) FROM
  2    (SELECT owner, (SELECT name FROM v$datafile WHERE file#=file_id ) AS filename ,
  3      segment_name ,    segment_type ,    tablespace_name
  4    FROM dba_extents  WHERE tablespace_name='TEST'  ) x
  5  group by owner,filename,segment_name,segment_type,tablespace_name ORDER BY 1,4 DESC,  3;

OWNER  FILENAME                                    SEGMENT_ SEGMENT_ TABLE   COUNT(*)
------ ------------------------------------------- -------- -------- ----- ----------
ASANGA +DATA/rac11g1/datafile/test.372.758295243   FULT     TABLE    TEST          20
ASANGA +DATA/rac11g1/datafile/test.372.758295243   FULINDX  INDEX    TEST          12

Changing Backup Policy on Standby DB - 3

2011-07-04T06:34:00.000-07:00

Previous two posts were using non-RAC data guard configurations. This blog used a RAC-RAC data guard configuration.

1. Requirment is to change the backup retention policy.

RMAN>
using target database control file instead of recovery catalog
RMAN configuration parameters for database with db_unique_name RAC11G2S are:
CONFIGURE RETENTION POLICY TO REDUNDANCY 1; # default

RMAN> CONFIGURE RETENTION POLICY TO REDUNDANCY 2;

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of configure command at 04/07/2011 12:47:36
RMAN-05021: this configuration cannot be changed for a BACKUP or STANDBY control file

2. Change the backup retention policy on primary first

RMAN> CONFIGURE RETENTION POLICY TO REDUNDANCY 2;

new RMAN configuration parameters:
CONFIGURE RETENTION POLICY TO REDUNDANCY 2;
new RMAN configuration parameters are successfully stored

3. Make a standby controlfile on the primary after this can revert back to the earlier backup policy on primary.

RMAN> backup current controlfile for standby format '/tmp/standby.ctl';

Starting backup at 04-JUL-11
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=43 instance=rac11g21 device type=DISK
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
including standby control file in backup set
channel ORA_DISK_1: starting piece 1 at 04-JUL-11
channel ORA_DISK_1: finished piece 1 at 04-JUL-11
piece handle=/tmp/standby.ctl tag=TAG20110623T123939 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:03
Finished backup at 04-JUL-11

Starting Control File and SPFILE Autobackup at 04-JUL-11
piece handle=+FLASH/rac11g2/autobackup/2011_07_04/s_754576786.494.754576789 comment=NONE
Finished Control File and SPFILE Autobackup at 04-JUL-11

4. Move the newly created standby controlfile on primary server to standby server.

5. Stop log apply and redo transport

DGMGRL> show configuration verbose

Configuration - rac11g2_dgb

  Protection Mode: MaxPerformance
  Databases:
    rac11g2  - Primary database
    rac11g2s - Physical standby database
 
DGMGRL> edit database rac11g2s set state='APPLY-OFF';
DGMGRL> edit database rac11g2 set state='TRANSPORT-OFF';

6. Shutdown all instances in standby database RAC and start one instance in nomount mode

srvctl stop database -d rac11g2s

SQL> startup nomount
ORACLE instance started.

7. Restore the controlfiles using the newly created standby controlfiles

RMAN> restore standby controlfile from '/home/oracle/standby.ctl';

Starting restore at 04-JUL-11
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=143 instance=rac11g2s2 device type=DISK

channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:02
output file name=+DATA/rac11g2s/controlfile/current.257.754586439
output file name=+FLASH/rac11g2s/controlfile/current.482.754586439

8. Mount the database and catalog the datafiles

RMAN> alter database mount;

database mounted
released channel: ORA_DISK_1

RMAN> catalog start with '+DATA/rac11g2s/datafile';

Starting implicit crosscheck backup at 04-JUL-11
allocated channel: ORA_DISK_1
Crosschecked 8 objects
Finished implicit crosscheck backup at 04-JUL-11

Starting implicit crosscheck copy at 04-JUL-11
using channel ORA_DISK_1
Crosschecked 2 objects
Finished implicit crosscheck copy at 04-JUL-11

searching for all files in the recovery area
cataloging files...
cataloging done

List of Cataloged Files
=======================
File Name: +flash/RAC11G2S/AUTOBACKUP/2011_07_04/s_755582799.520.755612793
File Name: +flash/RAC11G2S/AUTOBACKUP/2011_07_01/s_755372829.380.755372831
File Name: +flash/RAC11G2S/AUTOBACKUP/2011_07_01/s_755369531.371.755373091
File Name: +flash/RAC11G2S/BACKUPSET/2011_07_04/annnf0_TAG20110704T122511_0.435.755612715
File Name: +flash/RAC11G2S/BACKUPSET/2011_07_04/nnndf0_TAG20110704T122546_0.424.755612747
File Name: +flash/RAC11G2S/BACKUPSET/2011_07_01/annnf0_TAG20110701T174611_0.414.755372773
File Name: +flash/RAC11G2S/BACKUPSET/2011_07_01/nnndf0_TAG20110701T174616_0.387.755372777
File Name: +flash/RAC11G2S/BACKUPSET/2011_07_01/annnf0_TAG20110701T174706_0.478.755372827
File Name: +flash/RAC11G2S/BACKUPSET/2011_07_01/ncnnf0_TAG20110701T175126_0.315.755373087
File Name: +flash/RAC11G2S/ARCHIVELOG/2011_07_04/thread_1_seq_769.427.755612261

searching for all files that match the pattern +DATA/rac11g2s/datafile

List of Files Unknown to the Database
=====================================
File Name: +data/RAC11G2S/DATAFILE/SYSAUX.259.754586467
File Name: +data/RAC11G2S/DATAFILE/SYSTEM.258.754586581
File Name: +data/RAC11G2S/DATAFILE/UNDOTBS3.262.754586657
File Name: +data/RAC11G2S/DATAFILE/UNDOTBS2.261.754586713
File Name: +data/RAC11G2S/DATAFILE/USERS.276.754586727

Do you really want to catalog the above files (enter YES or NO)? yes
cataloging files...
cataloging done

List of Cataloged Files
=======================
File Name: +data/RAC11G2S/DATAFILE/SYSAUX.259.754586467
File Name: +data/RAC11G2S/DATAFILE/SYSTEM.258.754586581
File Name: +data/RAC11G2S/DATAFILE/UNDOTBS3.262.754586657
File Name: +data/RAC11G2S/DATAFILE/UNDOTBS2.261.754586713
File Name: +data/RAC11G2S/DATAFILE/USERS.276.754586727

9. Switch database to copy

RMAN> switch database to copy;

datafile 1 switched to datafile copy "+DATA/rac11g2s/datafile/system.258.754586581"
datafile 2 switched to datafile copy "+DATA/rac11g2s/datafile/sysaux.259.754586467"
datafile 4 switched to datafile copy "+DATA/rac11g2s/datafile/users.276.754586727"
datafile 5 switched to datafile copy "+DATA/rac11g2s/datafile/undotbs2.261.754586713"
datafile 6 switched to datafile copy "+DATA/rac11g2s/datafile/undotbs3.262.754586657"

10. Clear the log files

SQL> select group# from v$log;

    GROUP#
----------
         1
         2
         3
         4

alter database clear logfile group 1;
alter database clear logfile group 2;
alter database clear logfile group 3;
alter database clear logfile group 4;

Generate the clearing SQL using and run the output

SQL> select 'alter database clear logfile group '||group#||';' from v$standby_log;
alter database clear logfile group 5;
alter database clear logfile group 6;
alter database clear logfile group 7;
alter database clear logfile group 8;
alter database clear logfile group 9;
alter database clear logfile group 10;

11. Shutdown the instance and start the RAC database

Database dismounted.
ORACLE instance shut down.

srvctl start database -d rac11g2s

12. Enable log apply and transport

dgmgrl sys/rac11g2db
Connected.

DGMGRL> edit database rac11g2s set STATE='APPLY-ON';
Succeeded.

DGMGRL> edit database rac11g2 SET STATE='TRANSPORT-ON';
Succeeded.

13. Verify log apply is working

SQL> select thread#,applied from v$archived_log;

   THREAD# APPLIED
---------- ---------
         1 YES
         1 YES
         1 IN-MEMORY
         2 NO
         2 YES

On primary do a log file archive

SQL> alter system archive log current;

and then on standby

THREAD# APPLIED
---------- ---------
         1 YES
         1 YES
         1 YES
         2 NO
         2 YES
         1 YES
         2 YES
         1 YES
         2 IN-MEMORY

14. Verify that retention policy is changed

RMAN> show all

using target database control file instead of recovery catalog
RMAN configuration parameters for database with db_unique_name RAC11G2S are:
CONFIGURE RETENTION POLICY TO REDUNDANCY 2;

Once everything is confirmed to be working fine, remove the duplicate log files using asmcmd.

Changing Backup Policy on Standby DB - 2

2011-07-04T06:02:00.000-07:00

The previous post is a mixture of steps on metalink notes [ID 734862.1] and [ID 459411.1]. This blog is based on [ID 734862.1] and the same data guard configuration used in the previous post is used here as well. Again the requirment is to change the backup retention policy.

On Primary

1. Change the backup retention policy on primary to the desired retention policy on standby

RMAN> CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 2 days;

old RMAN configuration parameters:
CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 1 DAYS;
new RMAN configuration parameters:
CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 2 DAYS;
new RMAN configuration parameters are successfully stored

2. Create a new standby controlfile

RMAN> backup current controlfile for standby format '/tmp/standby.ctl';

copy the above file to standby server.

On standby

3. Stop the log apply and start the standby DB in nomount mode

SQL> alter database recover managed standby database cancel;

Database altered.

SQL> shutdown immediate;
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.

SQL> startup nomount
ORACLE instance started.

4. Restore the controlfile using the newly created standby controlfile

rman target /
RMAN> restore standby controlfile from '/home/oracle/standby.ctl';

Starting restore at 01-JUL-11
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=1088 device type=DISK

channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
output file name=/opt/app/oracle/oradata/STDAUX/controlfile/o1_mf_5q7xfq1o_.ctl
output file name=/opt/app/oracle/oradata/STDAUX/controlfile/o1_mf_5q7xfq2t_.ctl
Finished restore at 01-JUL-11

5. Mount the database

RMAN> alter database mount;

database mounted
released channel: ORA_DISK_1

6. Catalog the datafiles

RMAN> catalog start with '/opt/app/oracle/oradata/STDAUX/datafile';

Starting implicit crosscheck backup at 01-JUL-11
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=1070 device type=DISK
Finished implicit crosscheck backup at 01-JUL-11

Starting implicit crosscheck copy at 01-JUL-11
using channel ORA_DISK_1
Crosschecked 4 objects
Finished implicit crosscheck copy at 01-JUL-11

searching for all files in the recovery area
cataloging files...
cataloging done

List of Cataloged Files
=======================
File Name: /opt/app/oracle/flash_recovery_area/STDAUX/archivelog/2011_07_01/o1_mf_1_1822_70vb948l_.arc
File Name: /opt/app/oracle/flash_recovery_area/STDAUX/archivelog/2011_07_01/o1_mf_1_1817_70v8hn1w_.arc
File Name: /opt/app/oracle/flash_recovery_area/STDAUX/archivelog/2011_07_01/o1_mf_1_1820_70v994l1_.arc
File Name: /opt/app/oracle/flash_recovery_area/STDAUX/archivelog/2011_07_01/o1_mf_1_1816_70v7bz26_.arc
File Name: /opt/app/oracle/flash_recovery_area/STDAUX/archivelog/2011_07_01/o1_mf_1_1818_70v8vn7k_.arc
File Name: /opt/app/oracle/flash_recovery_area/STDAUX/archivelog/2011_07_01/o1_mf_1_1823_70vcb25c_.arc
File Name: /opt/app/oracle/flash_recovery_area/STDAUX/archivelog/2011_07_01/o1_mf_1_1815_70tj299j_.arc
File Name: /opt/app/oracle/flash_recovery_area/STDAUX/archivelog/2011_07_01/o1_mf_1_1819_70v93sg2_.arc
File Name: /opt/app/oracle/flash_recovery_area/STDAUX/archivelog/2011_07_01/o1_mf_1_1821_70v9rxmb_.arc
File Name: /opt/app/oracle/flash_recovery_area/STDAUX/archivelog/2011_07_01/o1_mf_1_1817_70v8wc4x_.arc
File Name: /opt/app/oracle/flash_recovery_area/STDAUX/archivelog/2011_07_01/o1_mf_1_1814_70t0vmyk_.arc
File Name: /opt/app/oracle/flash_recovery_area/STDAUX/archivelog/2011_06_30/o1_mf_1_1813_70scm1p0_.arc

searching for all files that match the pattern /opt/app/oracle/oradata/STDAUX/datafile

List of Files Unknown to the Database
=====================================
File Name: /opt/app/oracle/oradata/STDAUX/datafile/o1_mf_undotbs1_70sbx6n4_.dbf
File Name: /opt/app/oracle/oradata/STDAUX/datafile/o1_mf_tbxlobs_70sbx6nb_.dbf
File Name: /opt/app/oracle/oradata/STDAUX/datafile/o1_mf_test_70sbxko9_.dbf
File Name: /opt/app/oracle/oradata/STDAUX/datafile/o1_mf_system_70sbx6nk_.dbf
File Name: /opt/app/oracle/oradata/STDAUX/datafile/o1_mf_tbxindex_70sbx6n7_.dbf
File Name: /opt/app/oracle/oradata/STDAUX/datafile/o1_mf_travelbo_70sbx6mx_.dbf
File Name: /opt/app/oracle/oradata/STDAUX/datafile/o1_mf_tbx2ktbs_70sc6qw8_.dbf
File Name: /opt/app/oracle/oradata/STDAUX/datafile/o1_mf_temp_70v7btqh_.tmp
File Name: /opt/app/oracle/oradata/STDAUX/datafile/o1_mf_users_70sbx6nq_.dbf
File Name: /opt/app/oracle/oradata/STDAUX/datafile/o1_mf_sysaux_70sbx6nd_.dbf
File Name: /opt/app/oracle/oradata/STDAUX/datafile/o1_mf_example_70sbx6ns_.dbf

Do you really want to catalog the above files (enter YES or NO)? yes
cataloging files...
cataloging done

List of Cataloged Files
=======================
File Name: /opt/app/oracle/oradata/STDAUX/datafile/o1_mf_undotbs1_70sbx6n4_.dbf
File Name: /opt/app/oracle/oradata/STDAUX/datafile/o1_mf_tbxlobs_70sbx6nb_.dbf
File Name: /opt/app/oracle/oradata/STDAUX/datafile/o1_mf_test_70sbxko9_.dbf
File Name: /opt/app/oracle/oradata/STDAUX/datafile/o1_mf_system_70sbx6nk_.dbf
File Name: /opt/app/oracle/oradata/STDAUX/datafile/o1_mf_tbxindex_70sbx6n7_.dbf
File Name: /opt/app/oracle/oradata/STDAUX/datafile/o1_mf_travelbo_70sbx6mx_.dbf
File Name: /opt/app/oracle/oradata/STDAUX/datafile/o1_mf_tbx2ktbs_70sc6qw8_.dbf
File Name: /opt/app/oracle/oradata/STDAUX/datafile/o1_mf_temp_70v7btqh_.tmp
File Name: /opt/app/oracle/oradata/STDAUX/datafile/o1_mf_users_70sbx6nq_.dbf
File Name: /opt/app/oracle/oradata/STDAUX/datafile/o1_mf_sysaux_70sbx6nd_.dbf
File Name: /opt/app/oracle/oradata/STDAUX/datafile/o1_mf_example_70sbx6ns_.dbf

7. Switch datafile to copy

RMAN> switch database to copy;

datafile 1 switched to datafile copy "/opt/app/oracle/oradata/STDAUX/datafile/o1_mf_system_70sbx6nk_.dbf"
datafile 2 switched to datafile copy "/opt/app/oracle/oradata/STDAUX/datafile/o1_mf_sysaux_70sbx6nd_.dbf"
datafile 3 switched to datafile copy "/opt/app/oracle/oradata/STDAUX/datafile/o1_mf_undotbs1_70sbx6n4_.dbf"
datafile 4 switched to datafile copy "/opt/app/oracle/oradata/STDAUX/datafile/o1_mf_users_70sbx6nq_.dbf"
datafile 5 switched to datafile copy "/opt/app/oracle/oradata/STDAUX/datafile/o1_mf_example_70sbx6ns_.dbf"
datafile 6 switched to datafile copy "/opt/app/oracle/oradata/STDAUX/datafile/o1_mf_travelbo_70sbx6mx_.dbf"
datafile 7 switched to datafile copy "/opt/app/oracle/oradata/STDAUX/datafile/o1_mf_tbxindex_70sbx6n7_.dbf"
datafile 8 switched to datafile copy "/opt/app/oracle/oradata/STDAUX/datafile/o1_mf_tbxlobs_70sbx6nb_.dbf"
datafile 9 switched to datafile copy "/opt/app/oracle/oradata/STDAUX/datafile/o1_mf_tbx2ktbs_70sc6qw8_.dbf"
datafile 10 switched to datafile copy "/opt/app/oracle/oradata/STDAUX/datafile/o1_mf_test_70sbxko9_.dbf"

8. Stop and start flashback if it was enabled

SQL>ALTER DATABASE FLASHBACK OFF;
SQL>ALTER DATABASE FLASHBACK ON;

9. Clear logfiles

SQL> select group# from v$log;

    GROUP#
----------
         1
         2
         6
         4
         5
         3

6 rows selected.

alter database clear logfile group 1;
alter database clear logfile group 2;
alter database clear logfile group 6;
alter database clear logfile group 4;
alter database clear logfile group 5;
alter database clear logfile group 3;

SQL> select group# from v$standby_log;

    GROUP#
----------
         7
         8
         9
        10
        11
  
alter database clear logfile group 7;
alter database clear logfile group 8;
alter database clear logfile group 9;
alter database clear logfile group 10;
alter database clear logfile group 11;

10. Start log apply service

SQL> alter database recover managed standby database using current logfile disconnect;

and verify log apply with

SQL> select sequence#,applied from v$archived_log;
 SEQUENCE# APPLIED
---------- ---------
      1814 NO
      1813 NO
      1825 YES
      1826 YES
      1827 YES
      1828 IN-MEMORY

11. Confirm that backup retention policy has changed

RMAN> show all;

using target database control file instead of recovery catalog
RMAN configuration parameters for database with db_unique_name ENT11G1 are:
CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 2 DAYS;

Once everything is confirm removed the duplicate standby logs and online logs from the disk using OS utility. Although metalink note states to do this step early on, having done this early one resulted in getting the following error

RMAN> switch database to copy;

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of switch to copy command at 06/23/2011 14:01:05
RMAN-06571: datafile 1 does not have recoverable copy

It is possible the old logs may contain some information needed for recovery. So it's best to remove the duplicate log files at the end rather than early on.

Changing Backup Policy on Standby DB - 1

2011-07-04T04:29:00.000-07:00

Once a standby database is created it is not possible to change the backup retention policy. If the space available on the standby site is less or due to some other constraint it may be required to change the backup retention policy from that of the primary DB. Trying to do so using the configure command would give the following error

MAN> CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 2 days;

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of configure command at 07/01/2011 11:25:47
RMAN-05021: this configuration cannot be changed for a BACKUP or STANDBY control file

If possible the easiest way to change the retention policy is to do a switchover (making the standby the primary) and change the retention policy and switchover back to original primary. But this may not be possible at all times, especially if the data guard configuration is asymmetric and standby is used more for data protection than for disaster recovery.

What is required here is to change the retention policy in primary to that is desired on the standby, created a standby controlfile , revert the retention policy on primary, restore the newly created standby controlfile on standby DB which will have the modified retention policy.

There are two metalink notes that can help with this.

Steps to recreate a Physical Standby Controlfile [ID 459411.1]
Step By Step Guide On How To Recreate Standby Control File When Datafiles Are On ASM And Using Oracle Managed Files [ID 734862.1]

This blog will use steps listed in both these notes first on a single instance data guard configuration using file system for datafile storage (OMF) and another blog will follow with RAC data guard configuration using ASM.

On standby database

1. Requirement is to change the retention policy to recovery window of 2 days which cannot be done with the configure command and switchover to standby is not possible at this time

RMAN> CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 2 days;

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of configure command at 07/01/2011 11:25:47
RMAN-05021: this configuration cannot be changed for a BACKUP or STANDBY control file

2. Cancel log apply on standby and get the datafile name list and shutdown the standby database

SQL>alter database recover managed standby database cancel;

SQL> select name from v$datafile;

NAME
--------------------------------------------------------------------
/opt/app/oracle/oradata/STDAUX/datafile/o1_mf_system_70sbx6nk_.dbf
/opt/app/oracle/oradata/STDAUX/datafile/o1_mf_sysaux_70sbx6nd_.dbf
/opt/app/oracle/oradata/STDAUX/datafile/o1_mf_undotbs1_70sbx6n4_.dbf
/opt/app/oracle/oradata/STDAUX/datafile/o1_mf_users_70sbx6nq_.dbf
/opt/app/oracle/oradata/STDAUX/datafile/o1_mf_example_70sbx6ns_.dbf
/opt/app/oracle/oradata/STDAUX/datafile/o1_mf_travelbo_70sbx6mx_.dbf
/opt/app/oracle/oradata/STDAUX/datafile/o1_mf_tbxindex_70sbx6n7_.dbf
/opt/app/oracle/oradata/STDAUX/datafile/o1_mf_tbxlobs_70sbx6nb_.dbf
/opt/app/oracle/oradata/STDAUX/datafile/o1_mf_tbx2ktbs_70sc6qw8_.dbf
/opt/app/oracle/oradata/STDAUX/datafile/o1_mf_test_70sbxko9_.dbf

SQL>shutdown immediate;

On primary DB

3. Change the retention policy to what is required in standby

RMAN> CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 2 days;

old RMAN configuration parameters:
CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 1 DAYS;
new RMAN configuration parameters:
CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 2 DAYS;
new RMAN configuration parameters are successfully stored

4. Create the new standby controlfile in primary db server and copy it to the standby db server

SQL> alter database create standby controlfile as '/home/oracle/standby.ctl';

Database altered.

5. Backup the current controlfile on standby using OS utility. (only valid if the database is shutdown clearnly).

mv o1_mf_5q7xfq1o_.ctl o1_mf_5q7xfq1o_.ctl.bak
mv o1_mf_5q7xfq2t_.ctl o1_mf_5q7xfq2t_.ctl.bak

Replace the current controlfiles on standby with the new standby controlfiles

cp /home/oracle/standby.ctl o1_mf_5q7xfq1o_.ctl
cp /home/oracle/standby.ctl o1_mf_5q7xfq2t_.ctl

6.Mount the standby database

7. Metalink note [ID 459411.1] says If the File-Structure is different between Primary and Standby Database and db_file_name_convert/log_file_name_convert is not set, rename the File-Location(s) in the new Standby Controlfile:

In this case above mention initialization parameters were set but since the standby was using OMF the datafile names are different from the primary hence the reason to query them in step 1. This could be observed by runnign following query on standby

SQL> select name from v$datafile;

NAME
----------------------------------------------------------------------
/opt/app/oracle/oradata/STDAUX/datafile/o1_mf_system_5q7x745y_.dbf
/opt/app/oracle/oradata/STDAUX/datafile/o1_mf_sysaux_5q7x746p_.dbf
/opt/app/oracle/oradata/STDAUX/datafile/o1_mf_undotbs1_5q7x746t_.dbf
/opt/app/oracle/oradata/STDAUX/datafile/o1_mf_users_5q7x747g_.dbf
/opt/app/oracle/oradata/STDAUX/datafile/o1_mf_example_5q7xg0kk_.dbf
/opt/app/oracle/oradata/STDAUX/datafile/o1_mf_travelbo_5zb2lltr_.dbf
/opt/app/oracle/oradata/STDAUX/datafile/o1_mf_tbxindex_5zb2lzvc_.dbf
/opt/app/oracle/oradata/STDAUX/datafile/o1_mf_tbxlobs_5zb2mf60_.dbf
/opt/app/oracle/oradata/STDAUX/datafile/o1_mf_tbx2ktbs_5zb2t0jn_.dbf
/opt/app/oracle/oradata/STDAUX/datafile/o1_mf_test_70p7blqh_.dbf

These are the datafile names on primary and would not be same as standby datafile names.

8. Rename the datafile names to their original names

SQL> alter database rename file '/opt/app/oracle/oradata/STDAUX/datafile/o1_mf_system_5q7x745y_.dbf' to '/opt/app/oracle/oradata/STDAUX/datafile/o1_mf_system_70sbx6nk_.dbf';
alter database rename file '/opt/app/oracle/oradata/STDAUX/datafile/o1_mf_system_5q7x745y_.dbf' to '/opt/app/oracle/oradata/STDAUX/datafile/o1_mf_system_70sbx6nk_.dbf'
*
ERROR at line 1:
ORA-01511: error in renaming log/data files
ORA-01275: Operation RENAME is not allowed if standby file management is
automatic.

To overcome this problem change the standby file management to manual for the duration of the rename

SQL> alter system set standby_file_management='MANUAL' scope=memory;

System altered.

SQL> alter database rename file '/opt/app/oracle/oradata/STDAUX/datafile/o1_mf_system_5q7x745y_.dbf' to '/opt/app/oracle/oradata/STDAUX/datafile/o1_mf_system_70sbx6nk_.dbf';

Database altered.

Rename all the files like this and change the standby file management to auto

SQL> alter system set standby_file_management='AUTO' SCOPE=MEMORY;

System altered.

9. Stop and start flashback if it was enabled

SQL>ALTER DATABASE FLASHBACK OFF;
SQL>ALTER DATABASE FLASHBACK ON;

10. If standby log files were not created on primary create them on standby now, if standby log exists on standby clear standby log as well online logs

SQL> select group# from v$log;

    GROUP#
----------
         1
         2
         6
         4
         5
         3

6 rows selected.

SQL> alter database clear logfile group 1;

Database altered.

Do the same for other logs as well.

SQL> select group# from v$standby_log;

    GROUP#
----------
         7
         8
         9
        10
        11

SQL>  alter database clear logfile group 7;

Database altered.

Do the same for other logs as well.

11. Start log apply on standby and verify log apply is working

SQL> alter database recover managed standby database using current logfile disconnect;

Database altered.


SQL> select sequence#,applied from v$archived_log;

 SEQUENCE# APPLIED
---------- ---------
      1820 IN-MEMORY

Do a log archive on primary

SQL> alter system archive log current;

and check on standby

SQL> select sequence#,applied from v$archived_log;

SEQUENCE# APPLIED
---------- ---------
      1820 YES
      1821 IN-MEMORY

12. Finally verify the backup retention on standby has changed to desired retention policy

RMAN> show all;

using target database control file instead of recovery catalog
RMAN configuration parameters for database with db_unique_name ENT11G1 are:
CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 2 DAYS;

If database uses OMF then when log apply is started new online logfiles and standby logfiles would have been created. To save space identify the currently used logfiles with

select group#,member from v$logfile;

and remove the logfiles that doesn't appear on the output from the disk. (This is the step 3 B on [ID 734862.1]
Depending on the location of the logfiles on the standby server remove all online and standby redo logs from the standby directories Using an Operating System utility or ASMCMD and make sure that you have the LOG_FILE_NAME_CONVERT parameter defined to translate any directory paths. )

Restore Point Prerequisites change in 11.2

2011-06-09T04:07:00.000-07:00

For 10.2 and 11.1 to enable guarantee restore points following were prerequisites, from Oracle Documentation (Database Backup and Recovery User Guide)

To support the use of guaranteed restore points, the database must satisfy the
following prerequisites:
The COMPATIBLE initialization parameter must be set to 10.2 or greater.

The database must be running in ARCHIVELOG mode. To rewind your database to a guaranteed restore point, the FLASHBACK DATABASE command requires the use of archived redo logs starting from around the time of the restore point.

A flash recovery area must be configured, Guaranteed restore points use a mechanism similar to flashback logging. As with flashback logging, Oracle Database must store the required logs in the flash recovery area.

If Flashback Database is not enabled, then the database must be mounted, not open, when creating the first guaranteed restore point (or if all previously created guaranteed restore points have been dropped).

If a restore point command is issued on 11.1 in the open mode or without first creating a restore point in the mount mode following error would be thrown

SQL> select flashback_on from v$database;

FLASHBACK_ON
------------------
NO

SQL> SELECT NAME, SCN, TIME, DATABASE_INCARNATION#, GUARANTEE_FLASHBACK_DATABASE, STORAGE_SIZE FROM V$RESTORE_POINT WHERE GUARANTEE_FLASHBACK_DATABASE='YES';

no rows selected

SQL> select open_mode from v$database;

OPEN_MODE
----------
READ WRITE

SQL> create restore point first_restore guarantee flashback database;
create restore point first_restore guarantee flashback database
*
ERROR at line 1:
ORA-38784: Cannot create restore point 'FIRST_RESTORE'.
ORA-38787: Creating the first guaranteed restore point requires mount mode when
flashback database is off.

But the prerequisites has changed in 11.2 now the only pre-req is (as per Database Backup and Recovery User Guide) To use guaranteed restore points, the database must satisfy the following additional prerequisite: the COMPATIBLE initialization parameter must be set to 10.2.0 or greater and that's all.

Same steps above done on a 11.2 db

SQL> select flashback_on from v$database;

FLASHBACK_ON
------------------
NO

SQL> SELECT NAME, SCN, TIME, DATABASE_INCARNATION#, GUARANTEE_FLASHBACK_DATABASE, STORAGE_SIZE FROM V$RESTORE_POINT WHERE GUARANTEE_FLASHBACK_DATABASE='YES';

no rows selected

SQL> select open_mode from v$database;

OPEN_MODE
--------------------
READ WRITE

SQL> create restore point first_restore guarantee flashback database;

Restore point created.

SQL> SELECT NAME, SCN, TIME, DATABASE_INCARNATION#, GUARANTEE_FLASHBACK_DATABASE, STORAGE_SIZE FROM V$RESTORE_POINT WHERE GUARANTEE_FLASHBACK_DATABASE='YES';

NAME                        SCN TIME                           DATABASE_INCARNATION# GUA STORAGE_SIZE
-------------------- ---------- ------------------------------ --------------------- --- ------------
FIRST_RESTORE          17213755 09-JUN-11 12.05.26.000000000                       2 YES     15941632

On 11.2 it is possible to create restore points without first having the database in a mount mode (when flashback is off)

ORA 03137 TTC protocol internal error

2011-06-03T06:14:00.000-07:00

Some useful metalink notes for ora-03137.

Intermittant Ora-03137, Ora-03106, Ora-03124 When Forking Across Connections (Doc ID 859472.1)

ora-600 [12333] / ora-3137 [12333] Troubleshooting (Doc ID 828123.1)

ORA-03137: TTC Protocol Internal Error : [12333] Using JDBC Driver (Doc ID 752297.1)

Bug 8625762 - ORA-3137 [12333] due to bind data not read from wire (Doc ID 8625762.8)

Effects Of Sequence Aging Out Of Shared Pool

2011-06-01T03:13:00.000-07:00

Like any other database object in shared pool sequence could also be aged out or flushed out of the shared pool to make room for other objects. When this happens values return by the sequence may not be in the expected order.

SQL> create sequence mseq ;

SQL> select * from user_sequences;
SEQUENCE_NAME    MIN_VALUE  MAX_VALUE INCREMENT_BY C O CACHE_SIZE LAST_NUMBER
--------------- ---------- ---------- ------------ - - ---------- -----------
MSEQ                     1 1.0000E+28            1 N N         20           1

Take the first value from the sequence and examine the last number

SQL> select mseq.nextval from dual;

   NEXTVAL
----------
         1

SQL> select * from user_sequences;

SEQUENCE_NAME    MIN_VALUE  MAX_VALUE INCREMENT_BY C O CACHE_SIZE LAST_NUMBER
--------------- ---------- ---------- ------------ - - ---------- -----------
MSEQ                     1 1.0000E+28            1 N N         20          21

At this stage if the sequence is aged out of the shared pool (in this test case by flushing the shared pool) the next value returned would be 21 instead of 2. As sysdba run

SQL> alter system flush shared_pool;

and then as owner of the sequence

SQL> select mseq.nextval from dual;

   NEXTVAL
----------
        21

SQL> select * from user_sequences;

SEQUENCE_NAME    MIN_VALUE  MAX_VALUE INCREMENT_BY C O CACHE_SIZE LAST_NUMBER
--------------- ---------- ---------- ------------ - - ---------- -----------
MSEQ                     1 1.0000E+28            1 N N         20          41

This creates gaps in the values returned by the sequences and it only affects the cached sequences and nocache, order sequences are not affected

SQL> create sequence mseq nocache order;

SQL> select mseq.nextval from dual;

   NEXTVAL
----------
         1

SQL> select * from user_sequences;

SEQUENCE_NAME    MIN_VALUE  MAX_VALUE INCREMENT_BY C O CACHE_SIZE LAST_NUMBER
--------------- ---------- ---------- ------------ - - ---------- -----------
MSEQ                     1 1.0000E+28            1 N Y          0           2

Flush the shared pool

SQL> alter system flush shared_pool;

Sequence returns the next value 2 and flushing shared pool has no effect

SQL> select * from user_sequences;

SEQUENCE_NAME    MIN_VALUE  MAX_VALUE INCREMENT_BY C O CACHE_SIZE LAST_NUMBER
--------------- ---------- ---------- ------------ - - ---------- -----------
MSEQ                     1 1.0000E+28            1 N Y          0           2

SQL> select mseq.nextval from dual;

   NEXTVAL
----------
         2

SQL>  select * from user_sequences;

SEQUENCE_NAME    MIN_VALUE  MAX_VALUE INCREMENT_BY C O CACHE_SIZE LAST_NUMBER
--------------- ---------- ---------- ------------ - - ---------- -----------
MSEQ                     1 1.0000E+28            1 N Y          0           3

One way to prevent is to pin the sequence in the shared pool

SQL> create sequence mseq ;

SQL> select mseq.nextval from dual;

   NEXTVAL
----------
         1

SQL> select * from user_sequences;

SEQUENCE_NAME    MIN_VALUE  MAX_VALUE INCREMENT_BY C O CACHE_SIZE LAST_NUMBER
--------------- ---------- ---------- ------------ - - ---------- -----------
MSEQ                     1 1.0000E+28            1 N N         20          21

Pin the sequence with

SQL> exec dbms_shared_pool.keep('ASANGA.MSEQ','Q');

and flush the shared pool

SQL> alter system flush shared_pool;

Ordered values are returned from the sequence without a gap

SQL> select mseq.nextval from dual;

   NEXTVAL
----------
         2

SQL> /

   NEXTVAL
----------
         3

SQL> select * from user_sequences;

SEQUENCE_NAME    MIN_VALUE  MAX_VALUE INCREMENT_BY C O CACHE_SIZE LAST_NUMBER
--------------- ---------- ---------- ------------ - - ---------- -----------
MSEQ                     1 1.0000E+28            1 N N         20          21

Gathering stats for gc lost blocks

2011-05-31T04:19:00.000-07:00

In a two node RAC gc cr block lost appeared to be second in the top 5 wait events list. But this was only happening in one of the nodes whereas other node has almost zero waiting on this event. Stats plotted with ADMon taken from statspack base tables

Waits
Wait Timesgc lost blocks diagnostics [ID 563566.1] describe what to look for to diagnose this wait event. Among them is stats related to dropped and fragmented packets. Looking at the two following could be observed, on the problem node

 netstat -s
Ip:
   650337997 total packets received
   145 with invalid addresses
   0 forwarded
   0 incoming packets discarded
   422623145 incoming packets delivered
   990924957 requests sent out
   1718163 fragments dropped after timeout
   271652117 reassemblies required
   43937411 packets reassembled ok
   4233888 packet reassembles failed
   42533293 fragments received ok
   259374063 fragments created

On the non-problem node

Ip:
   2896602613 total packets received
   4445 with invalid addresses
   0 forwarded
   0 incoming packets discarded
   1690637959 incoming packets delivered
   2164503370 requests sent out
   4433 outgoing packets dropped
   4 fragments dropped after timeout
   1430346149 reassemblies required
   224385940 packets reassembled ok
   4 packet reassembles failed
   239685318 fragments received ok
   52 fragments failed
   1386980139 fragments created

Solution for this is to increase the reassembly buffer space (ipfrag_low_thresh,ipfrag_high_thresh and ipfrag_time). But this didn't help either. As per above metalink note "In most cases, gc buffer lost has been attributed to (a) A missing OS patch (b) Bad network card (c) Bad cable (d) Bad switch (e) One of the network settings.".

Following shell script could be used to store the IP packet stats relating to the above wait event in a database table.

SQL>create table ipfrag(stat_time timestamp default systimestamp, fragments_dropped number, reassemblies_required number, reassembled_ok number, reassemble_failed number);

$ netstat -s | grep 'Ip' -A 12 | grep -E 'reassembl|dropped' | awk 'BEGIN {FS=" "} $2~/^fragments/ {dropped = $1} $2~/^reassemblies/ {res_required = $1}
$2~/^packets/ {res_ok = $1}  $3~/^reassembles/ {res_fail = $1; state=3} state == 3 {printf "%s%d,%d,%d,%d%s\n","insert into ipfrag (fragments_dropped
,reassemblies_required,reassembled_ok,reassemble_failed) values(",dropped,res_required,res_ok,res_fail,");"}' | sqlplus -s username/pw

Update on 06-06-2011
These servers are hosted in a hosting company's data centre and problem turned out to be a mismatch in a switch setting. Once fixed (speed and duplex was set to auto/auto in this case) and gc cr block lost waits went away on the problem node as well. Graphs taken with ADMon after the fix.

Performance Overhead with FBDA 11.1 vs 11.2

2011-05-26T04:55:00.000-07:00

Blog came about as a result of evaluating alternatives to a trigger base audit mechanism. Few simple test cases were run against databases on 11.1 and 11.2 with FBDA enabled and disabled. Test consists of inserting 100k rows into a table (which doesn't have any indexes and only two columns), then updating and deleting one row, updating and deleting 9999 rows then creating an unique index and running a update and delete loop. A commit is issued soon after the DML which stresses the fbda process and also makes the undo segments eligible for re-write once fbda has done with archiving. Flashback archive was created on a separate tablespace and the storage system is a file system (RHEL 5, ext3) not ASM and both 11.2 and 11.1 databases resided in the same machine.

On 11.2 (PSU 11.2.0.2.2)
Even though it was said in FBDA documentations that insert statements does not generate any records in the flashback archive, inserts were slow on tables with FBDA enabled than on table without FBDA. strace on the fbda process showed burst of activity when inserts were going on, though no records are written clearly some work is being carried out which adds some overhead.
However the individual DML statements (outside the loops) that were run (with FBDA enabled) had elapsed time that were close to DML statements run without FBDA.Time shown is inclusive of execute time and commit timeIt seem if the fbda is able to keep up with the modification the overhead is less but whenever there's burst of activity it is possible to exhaust the fbda process to add considerable overhead.

On 11.1 (PSU 11.1.0.7.7)
It was difficult to get the test case working on 11.1 at times with PL/SQL terminating with the following

SQL>  begin
  2      for i in 1 .. 100000
  3      loop
  4      insert into x values(i,i||'abcdefg');
  5      commit;
  6      end loop;
  7      end;
  8      /

 begin
*
ERROR at line 1:
ORA-55616: Transaction table needs Flashback Archiver processing
ORA-06512: at line 4

Explanation for this error says

*Cause: Too many transaction table slots were being taken by transactions on tracked tables.
*Action: Wait for some amount of time before doing tracked transactions.

Which indicates it is possible to exhaust the slots in the tracker table and worryingly this will put an end to further DML on that table until more slots are available. Overhead was far greater than in 11.2 However the key difference between 11.2 and 11.1 came when the elapsed time on individual DMLs were compared. Although execute time was low and similar to elapsed times of 11.2 or even without FBDA, commit time was far greater than in 11.2It could be said that 11.2 has some improvements over 11.1 when it comes to FBDA but still careful consideration must be given to the performance overhead introduced by FBDA.

From metalink
Bug : COMMIT DELAY WHEN UPDATING TABLE IN FLASHBACK DATA ARCHIVE MODE 8226666
Bug 9786460 - ORA-600 [qertbfetchbyrowid_fda:no selected row] after bugfix 8226666 [ID 9786460.8]

Test Case

set timing on
--create table x (a number, b varchar2(1000)); -- for without FBDA
create table x (a number, b varchar2(1000)) flashback archive fbdarchive; -- for with FBDA

begin
  for i in 1 .. 100000
  loop
    insert into x values(i,i||'abcdefg');
    commit;
   end loop;
end;
/
update x set b = 'aa' where a = 10;
commit;
delete from x where a = 10;
commit;
update x set b = 'bbbb' where a < 10001;
commit;
delete from x where a < 10001;
commit;

create unique index aidx on x(a) compute statistics nologging;

begin
  for i in 1 .. 100000
  loop
    update x set b = 'abcdef' where a = i;
    commit;
   end loop;
end;
/

begin
  for i in 1 .. 100000
  loop
    delete from x where a = i;
    commit;
   end loop;
end;
/

Flashback Data Archive 11.1 vs 11.2

2011-05-24T04:43:00.000-07:00

Some of the DDL statements that weren't allowed on tables that had FDBA enabled in 11.1 are now allowed on 11.2. Section below gives a summary of the changes.

From 11.1 documentation
DDL Statements Not Allowed on Tables Enabled for Flashback Data Archive

Using any of the following DDL statements on a table enabled for Flashback Data Archive causes error ORA-55610:

ALTER TABLE statement that does any of the following:
Drops, renames, or modifies a column
Performs partition or sub partition operations
Converts a LONG column to a LOB column
Includes an UPGRADE TABLE clause, with or without an INCLUDING DATA clause
DROP TABLE statement
RENAME TABLE statement
TRUNCATE TABLE statement

On 11.1.0.7 (11.1.0.7.3 PSU)

SQL> create table x (a number, b varchar2(1000)) flashback archive fbdarchive;

Table created.

SQL> alter table x rename to y;
alter table x rename to y
*
ERROR at line 1:
ORA-55610: Invalid DDL statement on history-tracked table


SQL> truncate table x;
truncate table x
               *
ERROR at line 1:
ORA-55610: Invalid DDL statement on history-tracked table

From 11.2 documentation
DDL Statements on Tables Enabled for Flashback Data Archive

Flashback Data Archive supports many DDL statements, including some that alter the table definition or move data. For example:
ALTER TABLE statement that does any of the following:
Adds, drops, renames, or modifies a column
Adds, drops, or renames a constraint
Drops or truncates a partition or sub partition operation
TRUNCATE TABLE statement
RENAME statement that renames a table

Some DDL statements cause error ORA-55610 when used on a table enabled for Flashback Data Archive. For example:
ALTER TABLE statement that includes an UPGRADE TABLE clause, with or without an INCLUDING DATA clause

ALTER TABLE statement that moves or exchanges a partition or sub partition operation

DROP TABLE statement

On 11.2 (11.2.0.2)

SQL> create table x (a number, b varchar2(1000)) flashback archive auditarchive;

Table created.

SQL> alter table x rename to y;

Table altered.

SQL> truncate table y;

Table truncated.

SQL> drop table y;
drop table y
           *
ERROR at line 1:
ORA-55610: Invalid DDL statement on history-tracked table

More bugs on 11.1 vs 11.2 Bug : COMMIT DELAY WHEN UPDATING TABLE IN FLASHBACK DATA ARCHIVE MODE 8226666

Bug 9786460 - ORA-600 [qertbfetchbyrowid_fda:no selected row] after bugfix 8226666 [ID 9786460.8]

New On Java 7

2011-05-15T08:16:00.000-07:00

Following are some of the things new on Java 7. Taken from various java documentations.

Catching More Than One Type of Exception with One Exception Handler

In Java SE 7 and later, a single catch block can handle more than one type of exception. This feature can reduce code duplication and lessen the temptation to catch an overly broad exception.

In the catch clause, specify the types of exceptions that block can handle, and separate each exception type with a vertical bar (|):http://www.blogger.com/img/blank.gif

catch (IOException|SQLException ex) {
logger.log(ex);
throw ex;
}

Note: If a catch block handles more than one exception type, then the catch parameter is implicitly final. In this example, the catch parameter ex is final and therefore you cannot assign any values to it within the catch block.

Using Non-Reifiable Parameters with Varargs Methods

Generics in varargs
The compiler issues a warning "warning: [unchecked] unchecked generic array creation" when generics are passed as varargs. This warning could be suppressed with @SafeVarArgs annotation on the method, which would ensure that the method wouldn't perform any unsafe operation.

public static void main(String[] args) {
        compute(new HashMap(), 
new TreeMap());
       }

@SafeVarargs
    static void compute(Map... args) {
        }
}

Using Strings in switch Statements
A switch works with the byte, short, char, and int primitive data types. It also works with enumerated types (discussed in Enum Types), the String class, and a few special classes that wrap certain primitive types: Character, Byte, Short, and Integer (discussed in Numbers and Strings).

In Java SE 7 and later, you can use a String object in the switch statement's expression.

public static int getMonthNumber(String month) {

int monthNumber = 0;

if (month == null) { return monthNumber; }

switch (month.toLowerCase()) {
case "january":    monthNumber =  1; break;
case "february":   monthNumber =  2; break;
case "march":      monthNumber =  3; break;
case "april":      monthNumber =  4; break;
case "may":        monthNumber =  5; break;
case "june":       monthNumber =  6; break;
case "july":       monthNumber =  7; break;
case "august":     monthNumber =  8; break;
case "september":  monthNumber =  9; break;
case "october":    monthNumber = 10; break;
case "november":   monthNumber = 11; break;
case "december":   monthNumber = 12; break;
default:           monthNumber =  0; break;
}

return monthNumber;
}

The String in the switch expression is compared with the expressions associated with each case label as if the String.equals method were being used.

Diamond syntax
Until Java 7, generic instances were created with

Map<Integer, String> map = new HashMap<Integer, String>();

In Java 7 only refernce requires type information and actual type could be without type information.

Map<Integer, String> map = new HashMap<>();

Binary literal and underscore in numeric literals
Binary values could be defined as

int number = 0b1100110; // decimal 102

Lengthy numeric values can be separated using underscores

long longnum = 28_06_86L;
long verylongnumber = 2334_7656_4503_3844L;

Automatic Resource Management
With the new Java 7 try block syntax, resources declared while defining the try block will automatically close when the control comes out of the try block.

try (BufferedReader reader = new BufferedReader(new FileReader(fileName))) {
while ((line = reader.readLine()) != null) {
         //do some work
       }
    } catch (IOException ex) {
 }

Get ASM Files Using LFTP

2011-04-04T06:32:00.000-07:00

If a database has XML DB set and ftp port configured then lftp could be used to get files out of the ASM.
FTP port could be configured with

SQL> @$ORACLE_HOME/rdbms/admin/catxdbdbca.sql 7001 8001
7001 is the ftp port 
8001 is the http port

Listener status would display these ports

lsnrctl status
...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=hostname)(PORT=7001))(Presentation=FTP)(Session=RAW))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=hostname)(PORT=8001))(Presentation=HTTP)(Session=RAW))
...

To get a file for example an archive log file use

lftp rac4 -usystem,passwd -p7001 -e 'get /sys/asm/FLASH/RAC11G2/ARCHIVELOG/2011_04_02/thread_2_seq_472.390.747360023;exit;'
41125376 bytes transferred in 4 seconds (11.02M/s)

Here rac4 is the host on which the ASM instance is running, command could be executed from a remote node as well. Following -u is the username used to login into the system, in this case database's system user and comma separated by the password for system user. FTP port is specified with -p.

Without the exit at the end, after the file is transferred shell prompt will end up in the ftp prompt.

To remove the http and ftp ports get rid of the dispatcher parameter (as per 274508.1 Listener Issue: Removing XDB Handlers for HTTP and FTP Ports) or

conn / as sysdba
exec dbms_xdb.sethttpport(0);
exec dbms_xdb.setftpport(0);
alter system register;

More on Master Note for Oracle XML DB Protocols: FTP HTTP HTTPS WebDAV, APEX and Native Database Web Services [ID 1083991.1]

Apart from lftp ftp client such filezilla could be used to connect to the ftp port specified and get the files.

Enable Flashback On Standby

2011-04-01T08:06:00.000-07:00

It is possible to enable flashback on a standby independent of the primary. (It's good to have flashback enabled both on primary and standby, if flashback IO is no concern).
Data guard environment created earlier is used here. Steps are similar to How to Enable Flashback for a RAC Database on ASM ( 819905.1)

To enable flashback on standby bring the stop log apply on standby (transport could also be stopped for the duration).

DGMGRL>  edit database rac11g2 set state='TRANSPORT-OFF';
Succeeded.
DGMGRL> edit database rac11g2s set state='APPLY-OFF';
Succeeded.

Trying to enable flashback while log apply is on will result in

SQL> alter database flashback on;
alter database flashback on
*
ERROR at line 1:
ORA-01153: an incompatible media recovery is active

If the standby is open in read only mode then shutdown and bring it up only one instance in mount mode as this is required to enable flashback.

srvctl stop database -d rac11g2s
srvctl start instance -d rac11g2s -i rac11g2s1 -o mount

Once in mount mode enable flashback on the standby

SQL> alter database flashback on;

Database altered.

Enable log apply and transport on

DGMGRL> edit database rac11g2 set state='TRANSPORT-ON';
Succeeded.
DGMGRL> edit database rac11g2s set state='APPLY-ON';
Succeeded.

Open the standby mode in read only if required. Check the flashback on status of the database

SQL> select flashback_on from v$database;

FLASHBACK_ON
------------
YES

Having flashback on standby is important especially to recover from open resetlogs situations in primary. If the standby has applied changes past the new resetlog scn and there's no flashback enabled on standby, only way to recover is to recreate the standby. If flashback was enabled on standby then it could be used to flashback to a scn prior to resetlogs and continue to use the standby from then onwards.

Stuck Archiver Processes and FAL Gap Resolution Not Working

2011-03-24T08:55:00.000-07:00

On a RAC to RAC data guard configuration (11.1.0.7) network wait class and waits on LGWR LNS seem to appear out of the blue. System has been running for a quite a number of years and there has not been any changes to network or any other hardware (NIC, cables and etc). Output from the emconsole
Drilling down to "other" wait class could see following LGWR LNS wait
The wait histogram showed a constant value of 16ms
Following was observed in the network wait class drill down
Metalink note Data Guard Wait Events (233491.1) describes these wait event as "ARCH wait on ATTACH - This wait event monitors the amount of time spent by all archive processes to spawn an RFS connection. The LGWR-LNS wait on channel wait event is for standby destinations configured with either the LGWR ASYNC or LGWR SYNC=PARALLEL attributes.
LGWR-LNS wait on channel - This wait event monitors the amount of time spent by the log writer (LGWR) process or the network server processes waiting to receive messages on KSR channels."

During this time there was a considerable archive gap between primary and standby and FAL gap resolution seems unable to resolve it. (FAL use to work fine).

The thought of making changes to transport related values in Oracle net (send and receive buffer) was suppressed since there has not been any hardware changes.
Metalink didn't give any more than definition for these waits.
Googling yield a forum post with a mention of metalink note Bug 5576816 - FAL gap resolution does not work with max_connection set in some scenario. This was applicable for 10.2.0.3 not 11.1.0.7 but the recommendation on the posting (found through googling) was to kill all the archive processes in primary(these will get restated as soon as they get killed). Reason given was that these archive processes were "stuck" and need restart. Looking at the emconsole it was also visible waits were happening on the archive processes.

Tried to kill the archive processors "proper way" by changing the log_archive_max_processes to 1 but this didn't kill any of the processes. Even after setting it to 1 all the archive processes were running. Then did a rolling shutdown and start up of the primary which resolved the issue.

Unfortunately the issue was back after few days on one of the nodes. This time killing the Oracle database session of the archive processes waiting for these wait events resolved it. It seem the archive processes being "stuck" is the symptom and cause could be something else.
Blog post will be updated ...

Remap Column Data on the Fly using EXPDP

2011-03-18T05:27:00.000-07:00

Remap data is a new feature introduced in 11g releases which allows data on a column to be changed on the fly when exporting (expdp).

Few things to note. The help option gives the usage of remap_data as

REMAP_DATA
Specify a data conversion function.
For example, REMAP_DATA=EMP.EMPNO:REMAPPKG.EMPNO.

It shows only the table name and column name is required but this result in following error

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
ORA-39001: invalid argument value
ORA-39146: schema "" does not exist

Schema name is also required.

Also the function used to remap the data must accept the column type as argument otherwise following error will be thrown

ORA-31693: Table data object "ASANGA"."X" failed to load/unload and is being skipped due to error:
ORA-06553: PLS-306: wrong number or types of arguments in call to function_name

Also the function used must be a function within a function. Schema level function will not be used and following error will be shown

ORA-39001: invalid argument value
ORA-39232: invalid remap function: DUMP_OBFUSCATE

Below is an example package function that could be used

create or replace package obfuscate as

 function dump_obfuscate(ssn in number) return number;
 
end;
/

create or replace package body  obfuscate as
  
 function dump_obfuscate(ssn in number) return number
 is
  i number;
 begin
  i := dbms_random.value(0,100);
  return  round(i);
 end;

end;
/

Use the expdp syntax as

expdp asanga/asa directory=dumpdir dumpfile=asa.dmp logfile=asa.log SCHEMAS=asanga
REMAP_DATA=asanga.x.a:obfuscate.dump_obfuscate

It is also possible to remap data in multiple tables using multiple remap functions.

expdp asanga/*** directory=dumpdir dumpfile=asa.dmp logfile=asa.log SCHEMAS=asanga
REMAP_DATA=asanga.x.a:obfuscate.dump_obfuscate,asanga.y.a:obfuscate1.dump_obfuscate1

Creating and Removing ACFS

2011-03-11T07:36:00.000-08:00

Oracle Automatic Storage Management Cluster File System (Oracle ACFS) is a multi-platform, scalable file system, and storage management technology that extends Oracle Automatic Storage Management (Oracle ASM) functionality to support customer files maintained outside of Oracle Database.

Creating ACFS

1. Set the diskgroup attribute parameters. Compatible.asm and compatible.advm should be >= 11.2

$ sqlplus  / as sysasm
SQL> alter diskgroup flash set attribute 'compatible.asm'='11.2';
SQL> alter diskgroup flash set attribute 'compatible.advm'='11.2';

2. Create a volume in the appropriate diskgroup

SQL> alter diskgroup flash add volume volume1 size 1g;

This could also be done using asmcmd. Volume device could be identified with

select volume_name,volume_device from v$asm_volume;
VOLUME_NAME    VOLUME_DEVICE
-------------- ---------------------
VOLUMNE1       /dev/asm/volumne1-398

This could also confirmed with ls /dev/asm/*

3. Create a file system on the created volume

# mkfs -t acfs /dev/asm/volumne1-398
mkfs.acfs: version                   = 11.2.0.2.0
mkfs.acfs: on-disk version           = 39.0
mkfs.acfs: volume                    = /dev/asm/volumne1-398
mkfs.acfs: volume size               = 1073741824
mkfs.acfs: Format complete.

4. Register the file system with

acfsutil registry -a /dev/asm/volumne1-398 /opt/acfsvol

/opt/acfsvol will be the mountpoint for this file system.

5. Mount the file system

# mount -t acfs /dev/asm/volumne1-398 /opt/acfsvol

ASMCMD> volinfo -G flash VOLUMNE1
Diskgroup Name: FLASH

         Volume Name: VOLUMNE1
         Volume Device: /dev/asm/volumne1-398
         State: ENABLED
         Size (MB): 1024
         Resize Unit (MB): 256
         Redundancy: UNPROT
         Stripe Columns: 4
         Stripe Width (K): 128
         Usage: ACFS
         Mountpath: /opt/acfsvol

6. Change ownership to Oracle user to allow oracle user processes to use the file system

# chown oracle:oinstall /opt/acfsvol

6. As oracle user create a file in the mount point

cd /opt/acfsvol
touch x

Removing ACFS

1. Unmount the file system

# umount /opt/acfsvol

2. De-register the file system

# acfsutil registry -d /opt/acfsvol
acfsutil registry: successfully removed ACFS mount point /opt/acfsvol from Oracle Registry

3. Drop the volumne from the diskgroup

SQL> alter diskgroup flash drop volume volume1;

Diskgroup altered.

SQL> select volume_name,volume_device from v$asm_volume;

no rows selected

11gR2 RAC to RAC Data Guard - 2

2011-03-10T05:12:00.000-08:00

The first 11gR2 RAC to RAC Data Guard described the process of setting up a Data Guard between two node RAC. This blog is about another way to achieve the duplication of primary database, and was inspired by the second method of duplication given in RAC to single instance active duplication.

It is assumed all the preparatory work has been carried as described in 11gR2 RAC to RAC Data Guard. The steps below would replace step 13 in the above blog.

1. Instead of creating a pfile with just db_name create a complete pfile that will suite the standby database when it is fully operational. This could be done by creating a pfile on the primary (once data gurad related initialization parameters are set) and modifying it to suite the standby. Below is the one used in this case. The key difference is for control_files only the diskgroup locations are set

cat initrac11g2s1.ora
rac11g2s1.__db_cache_size=373293056
rac11g2s1.__java_pool_size=4194304
rac11g2s1.__large_pool_size=4194304
rac11g2s1.__oracle_base='/opt/app/oracle'#ORACLE_BASE set from environment
rac11g2s1.__pga_aggregate_target=209715200
rac11g2s1.__sga_target=633339904
rac11g2s1.__shared_io_pool_size=0
rac11g2s1.__shared_pool_size=239075328
rac11g2s1.__streams_pool_size=0
rac11g2s2.__db_cache_size=373293056
rac11g2s2.__java_pool_size=4194304
rac11g2s2.__large_pool_size=4194304
rac11g2s2.__oracle_base='/opt/app/oracle'#ORACLE_BASE set from environment
rac11g2s2.__pga_aggregate_target=209715200
rac11g2s2.__sga_target=633339904
rac11g2s2.__shared_io_pool_size=0
rac11g2s2.__shared_pool_size=239075328
rac11g2s2.__streams_pool_size=0
*.audit_file_dest='/opt/app/oracle/admin/rac11g2s/adump'
*.audit_trail='db'
*.cluster_database=true
*.compatible='11.2.0.0.0'
*.control_files='+DATA','+FLASH'
*.db_block_size=8192
*.db_create_file_dest='+DATA'
*.db_domain='domain.net'
*.db_file_name_convert='+DATA/rac11g2','+DATA/rac11g2s'
*.db_name='rac11g2'
*.db_recovery_file_dest='+FLASH'
*.db_recovery_file_dest_size=9470738432
*.db_unique_name='rac11g2s'
*.diagnostic_dest='/opt/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=rac11g2sXDB)'
rac11g2s1.fal_client='RAC11G2S1'
rac1g2s22.fal_client='RAC11G2S2'
*.fal_server='RAC11G21','RAC11G22'
rac11g2s1.instance_number=1
rac11g2s2.instance_number=2
*.log_archive_config='dg_config=(rac11g2,rac11g2s)'
*.log_archive_dest_1='service=RAC11G2 LGWR ASYNC NOAFFIRM max_failure=10 max_connections=5 reopen=180 valid_for=(online_logfiles,primary_role) db_uniq
ue_name=rac11g2'
*.log_archive_dest_10='location=use_db_recovery_file_dest valid_for=(all_logfiles,all_roles) db_unique_name=rac11g2s'
*.log_archive_dest_state_1='enable'
*.log_archive_dest_state_10='enable'
*.log_archive_format='%t_%s_%r.dbf'
*.log_archive_max_processes=5
*.log_file_name_convert='+FLASH/rac11g2','+FLASH/rac11g2s','+DATA/rac11g2','+DATA/rac11g2s'
*.open_cursors=300
*.pga_aggregate_target=209715200
*.processes=150
*.remote_listener='racb-scan:1521'
*.remote_login_passwordfile='exclusive'
*.sga_target=631242752
*.standby_file_management='AUTO'
rac11g2s1.undo_tablespace='UNDOTBS1'
rac11g2s2.undo_tablespace='UNDOTBS2'

2. The duplication command is now very simple as all the parameters have been set. The duplication command on step 17 in earlier blog could be replaced with below command. Also multiple rman channels could be enabled to take advantage of parallel file duplication.

run {
allocate channel ch1 device type disk connect 'sys/rac11g2db@rac11g21';
allocate channel ch2 device type disk connect 'sys/rac11g2db@rac11g22';

allocate auxiliary channel ch3 device type disk connect 'sys/rac11g2db@rac11g2s1';
allocate auxiliary channel ch4 device type disk connect 'sys/rac11g2db@rac11g2s1';

duplicate target database for standby from active database;

release channel ch1;
release channel ch2;
release channel ch3;
release channel ch4;

}

Output of executing the above command is given below.

rman target / auxiliary sys/rac11g2db@rac11g2s1

Recovery Manager: Release 11.2.0.2.0 - Production on Thu Mar 10 11:39:52 2011
Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.
connected to target database: RAC11G2 (DBID=379668842)
connected to auxiliary database: RAC11G2 (not mounted)

RMAN> run {
2> allocate channel ch1 device type disk connect 'sys/rac11g2db@rac11g21';
3> allocate channel ch2 device type disk connect 'sys/rac11g2db@rac11g22';
4>
5> allocate auxiliary channel ch3 device type disk connect 'sys/rac11g2db@rac11g2s1';
6> allocate auxiliary channel ch4 device type disk connect 'sys/rac11g2db@rac11g2s1';
7>
8> duplicate target database for standby from active database;
9>
10> release channel ch1;
11> release channel ch2;
12> release channel ch3;
13> release channel ch4;
14>
15> }

using target database control file instead of recovery catalog
allocated channel: ch1
channel ch1: SID=156 instance=rac11g21 device type=DISK

allocated channel: ch2
channel ch2: SID=26 instance=rac11g22 device type=DISK

allocated channel: ch3
channel ch3: SID=141 instance=rac11g2s1 device type=DISK

allocated channel: ch4
channel ch4: SID=19 instance=rac11g2s1 device type=DISK

Starting Duplicate Db at 10-MAR-11

contents of Memory Script:
{
   backup as copy reuse
   targetfile  '/opt/app/oracle/product/11.2.0/db_1/dbs/orapwrac11g21' auxiliary format
 '/opt/app/oracle/product/11.2.0/db_1/dbs/orapwrac11g2s1'   ;
}
executing Memory Script

Starting backup at 10-MAR-11
Finished backup at 10-MAR-11

contents of Memory Script:
{
   backup as copy current controlfile for standby auxiliary format  '+DATA/rac11g2s/controlfile/current.257.745418483';
   restore clone controlfile to  '+FLASH/rac11g2s/controlfile/current.257.745418483' from
 '+DATA/rac11g2s/controlfile/current.257.745418483';
   sql clone "create spfile from memory";
   shutdown clone immediate;
   startup clone nomount;
   sql clone "alter system set  control_files =
  ''+DATA/rac11g2s/controlfile/current.257.745418483'', ''+FLASH/rac11g2s/controlfile/current.257.745418483'' comment=
 ''Set by RMAN'' scope=spfile";
   shutdown clone immediate;
   startup clone nomount;
}
executing Memory Script

Starting backup at 10-MAR-11
channel ch1: starting datafile copy
copying standby control file
output file name=/opt/app/oracle/product/11.2.0/db_1/dbs/snapcf_rac11g21.f tag=TAG20110310T114022 RECID=2 STAMP=745414824
channel ch1: datafile copy complete, elapsed time: 00:00:07
Finished backup at 10-MAR-11

Starting restore at 10-MAR-11

channel ch4: skipped, AUTOBACKUP already found
channel ch3: copied control file copy
Finished restore at 10-MAR-11

sql statement: create spfile from memory

Oracle instance shut down

connected to auxiliary database (not started)
Oracle instance started

Total System Global Area     630501376 bytes

Fixed Size                     2229120 bytes
Variable Size                251661440 bytes
Database Buffers             369098752 bytes
Redo Buffers                   7512064 bytes
allocated channel: ch3
channel ch3: SID=18 instance=rac11g2s1 device type=DISK
allocated channel: ch4
channel ch4: SID=141 instance=rac11g2s1 device type=DISK

sql statement: alter system set  control_files =   ''+DATA/rac11g2s/controlfile/current.257.745418483'', ''+FLASH/rac11g2s/controlfile/current.257.745418483'' comment= ''Set by RMAN'' scope=spfile

Oracle instance shut down

connected to auxiliary database (not started)
Oracle instance started

Total System Global Area     630501376 bytes

Fixed Size                     2229120 bytes
Variable Size                251661440 bytes
Database Buffers             369098752 bytes
Redo Buffers                   7512064 bytes
allocated channel: ch3
channel ch3: SID=16 instance=rac11g2s1 device type=DISK
allocated channel: ch4
channel ch4: SID=143 instance=rac11g2s1 device type=DISK

contents of Memory Script:
{
   sql clone 'alter database mount standby database';
}
executing Memory Script

sql statement: alter database mount standby database
RMAN-05529: WARNING: DB_FILE_NAME_CONVERT resulted in invalid ASM names; names changed to disk group only.

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

executing command: SET NEWNAME

renamed tempfile 1 to +data in control file

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

Starting backup at 10-MAR-11
channel ch1: starting datafile copy
input datafile file number=00002 name=+DATA/rac11g2/datafile/sysaux.257.740770047
channel ch2: starting datafile copy
input datafile file number=00001 name=+DATA/rac11g2/datafile/system.256.740770045
output file name=+DATA/rac11g2s/datafile/system.264.745418537 tag=TAG20110310T114116
channel ch2: datafile copy complete, elapsed time: 00:02:27
channel ch2: starting datafile copy
input datafile file number=00003 name=+DATA/rac11g2/datafile/undotbs1.258.740770049
output file name=+DATA/rac11g2s/datafile/sysaux.275.745418537 tag=TAG20110310T114116
channel ch1: datafile copy complete, elapsed time: 00:02:34
channel ch1: starting datafile copy
input datafile file number=00005 name=+DATA/rac11g2/datafile/undotbs2.264.740770355
output file name=+DATA/rac11g2s/datafile/undotbs1.274.745418685 tag=TAG20110310T114116
channel ch2: datafile copy complete, elapsed time: 00:00:35
channel ch2: starting datafile copy
input datafile file number=00004 name=+DATA/rac11g2/datafile/users.259.740770049
output file name=+DATA/rac11g2s/datafile/undotbs2.273.745418693 tag=TAG20110310T114116
channel ch1: datafile copy complete, elapsed time: 00:00:31
output file name=+DATA/rac11g2s/datafile/users.272.745418721 tag=TAG20110310T114116
channel ch2: datafile copy complete, elapsed time: 00:00:03
Finished backup at 10-MAR-11

sql statement: alter system archive log current

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

datafile 1 switched to datafile copy
input datafile copy RECID=2 STAMP=745418726 file name=+DATA/rac11g2s/datafile/system.264.745418537
datafile 2 switched to datafile copy
input datafile copy RECID=3 STAMP=745418726 file name=+DATA/rac11g2s/datafile/sysaux.275.745418537
datafile 3 switched to datafile copy
input datafile copy RECID=4 STAMP=745418726 file name=+DATA/rac11g2s/datafile/undotbs1.274.745418685
datafile 4 switched to datafile copy
input datafile copy RECID=5 STAMP=745418726 file name=+DATA/rac11g2s/datafile/users.272.745418721
datafile 5 switched to datafile copy
input datafile copy RECID=6 STAMP=745418726 file name=+DATA/rac11g2s/datafile/undotbs2.273.745418693
Finished Duplicate Db at 10-MAR-11

released channel: ch1

released channel: ch2

released channel: ch3

released channel: ch4

3. Since spfile is not copied from the primary the spfile generated during the duplication won't have any primary db related initializaton parametrs but it will have all the hidden parameters listed same as in rac to single instance duplication. Create a pfile from this genearted spfile and copy the control_files entry added by rman during duplication to initial pfile (initrac11g2s1.ora). During the duplication the control_files entry would have the full OMF path to the controlfiles.

*.cluster_database=TRUE
*.compatible='11.2.0.0.0'
*.control_files='+DATA/rac11g2s/controlfile/current.257.745423373','+FLASH/rac11g2s/controlfile/current.257.745423373'#Set by RMAN
*.core_dump_dest='/opt/app/oracle/diag/rdbms/rac11g2s/rac11g2s1/cdump'
*.db_block_size=8192

4. Once the above control_files line is copied to the initial pfile (initrac11g2s1.ora) shutdown the database and start the database in mount mode using this pfile.

shutdown immediate;
startup mount pfile='?/dbs/initrac11g2s1.ora';

5. Create the spfile for all instances with

SQL> create spfile='+DATA/rac11g2s/spfilerac11g2s.ora' from pfile='/opt/app/oracle/product/11.2.0/db_1/dbs/initrac11g2s1.ora';

For making the standby database cluster aware continue from step 22 onwards in the earlier blog.

Upgrading from 11.2.0.1 to 11.2.0.2

2011-03-09T05:37:00.000-08:00

Updated 14th October 2011 : More metalink notes

Unlike patchsets of previous releases 11.2 patchsets are full releases, which means they could be used for a fresh installation (no need to go from 11.2.0.1 to 11.2.0.2).
Following text is from various Oracle documentations.

"Starting with the 11.2.0.2 patch set, Oracle Database patch sets are full installations of the Oracle Database software. This means that you do not need to install Oracle Database 11g Release 2 (11.2.0.1) before installing Oracle Database 11g Release 2 (11.2.0.2).

Note the following changes with the new patch set packaging:

New installations consist of installing the most recent patch set, rather than installing a base release and then upgrading to a patch release.

Direct upgrades from previous releases to the most recent patch set are supported.

Out-of-place patch set upgrades recommended, in which you install the patch set into a new, separate Oracle home. In-place upgrades are supported, but not recommended.

Oracle Clusterware and Oracle ASM upgrades are always out-of-place upgrades. With 11g release 2 (11.2), you cannot perform an in-place upgrade of Oracle Clusterware and Oracle ASM to existing homes.

Please note that 11.2 Patch Sets 11.2.0.2 and higher are supplied as full releases. See Note:1189783.1 for details. Also note that 11.2.0.2 software was reissued on 17th Nov 2010 as described in Note:1266978.1 (1179474.1)"

Patchset comes in serveral bundles and don't have to download all of them unless required.

Oracle Database (includes Oracle Database and Oracle RAC) Note: you must download both zip files to install Oracle Database.
p10098816_112020_platform_1of7.zip
p10098816_112020_platform_2of7.zip

Oracle Grid Infrastructure (includes Oracle ASM, Oracle Clusterware, and Oracle Restart)
p10098816_112020_platform_3of7.zip

Oracle Database Client
p10098816_112020_platform_4of7.zip

Oracle Gateways
p10098816_112020_platform_5of7.zip

Oracle Examples
p10098816_112020_platform_6of7.zip

Deinstall
p10098816_112020_platform_7of7.zip

Another change is that patchsets don't have a readme.html or similar document with how to apply the patchset (which was avaibale with previous releases). For upgrade information Oracle (GI / Database) Administrator Guides must be refered. Admin Guide states "To upgrade existing 11.2.0.1 Oracle Grid Infrastructure installations to Oracle Grid Infrastructure 11.2.0.2 using a rolling upgrade, you must first do one of the following, depending on your platform:

Patch the release 11.2.0.1 Oracle Grid Infrastructure home with the 9413827 patch, and install Oracle Grid Infrastructure Patchset Update 1 (GI PSU1). When you apply patch 9413827, it shows up in the inventory as GIPSU2 bug 9655006.

Install Oracle Grid Infrastructure Patchset Update 2 (GI PSU2), which includes the 9413827 patch."

Failure to apply these patches could result in number of issues and these have been listed in following metalink notes.

Bug 9413827 - CRS rolling upgrade from 11.2.0.1 to 11.2.0.2 fails with OCR on ASM [ID 9413827.8]

Bug 10036834 - Linux Platforms: Patches not found upgrading Grid Infrastructure from 11.2.0.1 to 11.2.0.2 [ID 10036834.8]

Pre-requsite for 11.2.0.1 to 11.2.0.2 ASM Rolling Upgrade [ID 1274629.1]

If "Patch 9655006 - 11.2.0.1.2 for Grid Infrastructure (GI) Patch Set Update" is applied on the GI home then applying 9413827 or 9706490 (as per ID 1274629.1) will always try to rollback the 9655006.With 9413827

opatch napply -local -oh $CRS_HOME -id 9413827
..
..
Conflicts/Supersets for each patch are:

Patch : 9413827

Bug Superset of 9655006
Super set bugs are:
9655006,  9778840,  9343627,  9783609,  9262748,  9262722

Patches [   9655006 ] will be rolled back.

This is a conflict with the information given on 10036834.8. With 9706490

opatch napply -local -oh  $CRS_HOME -id 9706490
..
..
Conflicts/Supersets for each patch are:

Patch : 9706490

Bug Superset of 9655006
Super set bugs are:
9655006,  9778840,  9343627,  9783609,  9262748,  9262722

Patches [   9655006 ] will be rolled back.

For this upgrade the base installation of 11.2.0.1 was applied with the GI PSU 2 (Patch 9655006 - 11.2.0.1.2 for Grid Infrastructure (GI) Patch Set Update) and then 9706490 and finally 11.2.0.1.4 Patch Set Update was applied to the Oracle Home (Not GI Home). Steps below are from this point onwards.

Unzip the grid infrastructure patchset and execute runInstaller. Since this is an out of place upgrade the current cluster could be up and running.

On the second step select upgrade GI and ASM.
Select the nodes for upgrade, for this test it's a single node cluster

Select a new location to install the patchset.

Fix any pre-req warning and start the installation

After the installation is complete rootupgrade script needs to be run on the nodes involved. Up until this the cluster was running and during rootupgrade it will be brought down (via the rootupgrade script, no manual shutdown is required) and backup again once the upgrade is complete.

/flash/11.2.0/grid11.2.0.2/rootupgrade.sh
Running Oracle 11g root script...

The following environment variables are set as:
ORACLE_OWNER= oracle
ORACLE_HOME=  /flash/11.2.0/grid11.2.0.2

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

Entries will be added to the /etc/oratab file as needed by
Database Configuration Assistant when a database is created
Finished running generic part of root script.
Now product-specific root actions will be performed.
Using configuration parameter file: /flash/11.2.0/grid11.2.0.2/crs/install/crsconfig_params
Creating trace directory
Failed to add (property/value):('OLD_OCR_ID/'-1') for checkpoint:ROOTCRS_OLDHOMEINFO.Error code is 256

ASM upgrade has started on first node.

CRS-2791: Starting shutdown of Oracle High Availability Services-managed resources on 'hpc3'
CRS-2673: Attempting to stop 'ora.crsd' on 'hpc3'
CRS-2790: Starting shutdown of Cluster Ready Services-managed resources on 'hpc3'
CRS-2673: Attempting to stop 'ora.LISTENER.lsnr' on 'hpc3'
CRS-2673: Attempting to stop 'ora.LISTENER_SCAN1.lsnr' on 'hpc3'
CRS-2673: Attempting to stop 'ora.CLUSTERDG.dg' on 'hpc3'
CRS-2673: Attempting to stop 'ora.clusdb.db' on 'hpc3'
CRS-2673: Attempting to stop 'ora.registry.acfs' on 'hpc3'
CRS-2677: Stop of 'ora.LISTENER.lsnr' on 'hpc3' succeeded
CRS-2673: Attempting to stop 'ora.hpc3.vip' on 'hpc3'
CRS-2677: Stop of 'ora.LISTENER_SCAN1.lsnr' on 'hpc3' succeeded
CRS-2673: Attempting to stop 'ora.scan1.vip' on 'hpc3'
CRS-2677: Stop of 'ora.hpc3.vip' on 'hpc3' succeeded
CRS-2677: Stop of 'ora.scan1.vip' on 'hpc3' succeeded
CRS-2677: Stop of 'ora.registry.acfs' on 'hpc3' succeeded
CRS-2677: Stop of 'ora.CLUSTERDG.dg' on 'hpc3' succeeded
CRS-2677: Stop of 'ora.clusdb.db' on 'hpc3' succeeded
CRS-2673: Attempting to stop 'ora.DATA.dg' on 'hpc3'
CRS-2673: Attempting to stop 'ora.FLASH.dg' on 'hpc3'
CRS-2677: Stop of 'ora.DATA.dg' on 'hpc3' succeeded
CRS-2677: Stop of 'ora.FLASH.dg' on 'hpc3' succeeded
CRS-2673: Attempting to stop 'ora.asm' on 'hpc3'
CRS-2677: Stop of 'ora.asm' on 'hpc3' succeeded
CRS-2673: Attempting to stop 'ora.ons' on 'hpc3'
CRS-2673: Attempting to stop 'ora.eons' on 'hpc3'
CRS-2677: Stop of 'ora.ons' on 'hpc3' succeeded
CRS-2673: Attempting to stop 'ora.net1.network' on 'hpc3'
CRS-2677: Stop of 'ora.net1.network' on 'hpc3' succeeded
CRS-2677: Stop of 'ora.eons' on 'hpc3' succeeded
CRS-2792: Shutdown of Cluster Ready Services-managed resources on 'hpc3' has completed
CRS-2677: Stop of 'ora.crsd' on 'hpc3' succeeded
CRS-2673: Attempting to stop 'ora.cssdmonitor' on 'hpc3'
CRS-2673: Attempting to stop 'ora.ctssd' on 'hpc3'
CRS-2673: Attempting to stop 'ora.evmd' on 'hpc3'
CRS-2673: Attempting to stop 'ora.asm' on 'hpc3'
CRS-2673: Attempting to stop 'ora.drivers.acfs' on 'hpc3'
CRS-2673: Attempting to stop 'ora.mdnsd' on 'hpc3'
CRS-2677: Stop of 'ora.cssdmonitor' on 'hpc3' succeeded
CRS-2677: Stop of 'ora.evmd' on 'hpc3' succeeded
CRS-2677: Stop of 'ora.mdnsd' on 'hpc3' succeeded
CRS-2677: Stop of 'ora.ctssd' on 'hpc3' succeeded
CRS-2677: Stop of 'ora.drivers.acfs' on 'hpc3' succeeded
CRS-2677: Stop of 'ora.asm' on 'hpc3' succeeded
CRS-2673: Attempting to stop 'ora.cssd' on 'hpc3'
CRS-2677: Stop of 'ora.cssd' on 'hpc3' succeeded
CRS-2673: Attempting to stop 'ora.gpnpd' on 'hpc3'
CRS-2673: Attempting to stop 'ora.diskmon' on 'hpc3'
CRS-2677: Stop of 'ora.gpnpd' on 'hpc3' succeeded
CRS-2673: Attempting to stop 'ora.gipcd' on 'hpc3'
CRS-2677: Stop of 'ora.gipcd' on 'hpc3' succeeded
CRS-2677: Stop of 'ora.diskmon' on 'hpc3' succeeded
CRS-2793: Shutdown of Oracle High Availability Services-managed resources on 'hpc3' has completed
CRS-4133: Oracle High Availability Services has been stopped.
Successfully deleted 1 keys from OCR.
Creating OCR keys for user 'root', privgrp 'root'..
Operation successful.
OLR initialization - successful
Adding daemon to inittab
ACFS-9200: Supported
ACFS-9300: ADVM/ACFS distribution files found.
ACFS-9312: Existing ADVM/ACFS installation detected.
ACFS-9314: Removing previous ADVM/ACFS installation.
ACFS-9315: Previous ADVM/ACFS components successfully removed.
ACFS-9307: Installing requested ADVM/ACFS software.
ACFS-9308: Loading installed ADVM/ACFS drivers.
ACFS-9321: Creating udev for ADVM/ACFS.
ACFS-9323: Creating module dependencies - this may take some time.
ACFS-9327: Verifying ADVM/ACFS devices.
ACFS-9309: ADVM/ACFS installation correctness verified.
clscfg: EXISTING configuration version 5 detected.
clscfg: version 5 is 11g Release 2.
Successfully accumulated necessary OCR keys.
Creating OCR keys for user 'root', privgrp 'root'..
Operation successful.
Started to upgrade the Oracle Clusterware. This operation may take a few minutes.
Started to upgrade the CSS.
Started to upgrade the CRS.
The CRS was successfully upgraded.
Oracle Clusterware operating version was successfully set to 11.2.0.2.0
ASM upgrade has finished on last node.

Preparing packages for installation...
cvuqdisk-1.0.9-1
Configure Oracle Grid Infrastructure for a Cluster ... succeeded

The error line

Failed to add (property/value):('OLD_OCR_ID/'-1') for checkpoint:ROOTCRS_OLDHOMEINFO.Error code is 256

is similar to part of the error message give on 10036834.8 but after a SR was raised Oracle informed this is due to "Bug 10056593: FAIL TO ADD OLD_OCR_ID PROPERTY FOR ROOTCRS_OLDHOMEINFO. As per this bug the message can be ignored. You can continue with the installation."

Following commands shows that GI has been upgraded to 11.2.0.2

crsctl query crs softwareversion
Oracle Clusterware version on node [hpc3] is [11.2.0.2.0]

crsctl query crs releaseversion
Oracle High Availability Services release version on the local node is [11.2.0.2.0]

crsctl query crs activeversion
Oracle Clusterware active version on the cluster is [11.2.0.2.0]

It is important that if GI Home has been set on environment variables and PATH variable then to change it to point to new home, if not commands would be running out of the "old" GI Home.

Check the inventory.xml to see if the new GI Home has been set properly. Before upgrade

<HOME NAME="Ora11g_gridinfrahome1" LOC="/flash/11.2.0/grid11.2.0.1" TYPE="O" IDX="1" CRS="true">
<NODE_LIST>
<NODE NAME="hpc3"/>
</NODE_LIST>
</HOME>

After upgrade

<HOME NAME="Ora11g_gridinfrahome1" LOC="/flash/11.2.0/grid11.2.0.2" TYPE="O" IDX="1" CRS="true">
<NODE_LIST>
<NODE NAME="hpc3"/>
</NODE_LIST>
</HOME>

Next upgrade the Oracle Home and the database. With 11.2 patchset this could be done in one step where software is installed and at the end dbua will get run from the same session (no need to manually run dbua). Same as GI Home it is better to have a out of place upgrade for Oralce Home, trying to install on the same location would result in the following.

After running runInstaller from database patchset home, select upgrade existing database option

Select the nodes involved and start the install.

After the software has been installed (roughly around 86% mark on the progress bar) dbua would get run prompting to upgrade the database. This will have in addition to the usual option, an option to upgrade the time zone data, which will upgrade the time zone from 11 to 14.
Below is the summary before and after the upgrade.

Once the upgrade is complete the runInstaller will complete and concludes the upgrade process. As with GI Home set any environment variables to new Oracle Home.

Now it is possible to deinstall the "old" GI Home and Oracle Home. Either the deinstall within those Homes or an external deinstall installation (unzipping p10098816_112020_platform_7of7.zip) could be used for this.

cd /opt/app/oracle/product/11.2.0/clusdb_1/deinstall
unset ORACLE_HOME
./deinstall -local
..
Checking for existence of the Oracle home location /opt/app/oracle/product/11.2.0/clusdb_1
Oracle Home type selected for de-install is: RACDB
Oracle Base selected for de-install is: /opt/app/oracle
Checking for existence of central inventory location /opt/app/oraInventory
Checking for existence of the Oracle Grid Infrastructure home /opt/app/oracle/product/11.2.0/clusdb_1
The following nodes are part of this cluster: hpc3
..
Specify the list of database names that are configured in this Oracle home []:
Database Check Configuration END
..
Oracle Grid Infrastructure Home is: /opt/app/oracle/product/11.2.0/clusdb_1
The cluster node(s) on which the Oracle home exists are: (Please input nodes seperated by ",", eg: node1,node2,...)hpc3
Since -local option has been specified, the Oracle home will be de-installed only on the local node, 'hpc3', and the global configuration will be removed.
Oracle Home selected for de-install is: /opt/app/oracle/product/11.2.0/clusdb_1

It is imporant to note that database should not be running out of this Oracle Home. Similarly GI Home could also be deinstalled. In some cases during deinstall the directories inside GI Home may not get deleted and error message "directory in use" could be seen. But all the files inside the directories would be deleted (except in bin directory) and GI Home would be listed in inventory.xml as removed. In this case the "old" GI Home could be deleted with OS command.

Fast Connection Failover Behavior Change in 11.2

2011-03-02T07:30:00.000-08:00

Fast Connection Failover (FCF) allows jdbc connections to failover to surviving nodes in a RAC when one or more nodes are unavailable. This is very useful to have with middleware application server connection pools so when rolling upgrades or patch application is done on the database the connections in the middleware server would failover to surviving instances and client connections would not receive any errors.

It seem with 11.2.0.2 and 11.2.0.1 this doesn't work same as in 11gR1 and 10gR2.

There are two metalink notes available to test fcf on 10gR2 (How to Verify and Test Fast Connection Failover (FCF) Setup from a JDBC Thin Client Against a 10.2.x RAC Cluster [ID 433827.1]) and 11gR1 (Fast Connection Failover (FCF) Test Client Using 11g JDBC Driver and 11g RAC Cluster [ID 566573.1]).

In this case the java code provided with 566573.1 was used to test the failover of 10gR2 (10.2.0.4) , 11gR1 (11.1.0.7) and 11gR2 (11.2.0.1 and 11.2.0.2)

There are some differences between 433827.1 and 566573.1 note. In 10gR2 note the ons configuration uses vip hostname whereas in the 11gR1 note machine hostname is used. But for the testing purpose vip hostnames were used throughout (and 11gR1 has been deployed on production system using the vip hostname for ons configuration and failover worked without any problem), for 11gR2 it was tested with both vip hostname and hostname but there was no change in the result.

Furthermore the ons remote port on 11gR2 is changed to 6200 (same as 10gR2) but on 11gR1 it was 6251. The onsctl ping command on 10gR2 and 11gR1 provided the remote port this is no longer the case on 11gR2 but remote port could be observed with onsctl debug.

 $GI_HOME/bin/onsctl ping
ons is running ...

$GI_HOME/bin/onsctl debug
HTTP/1.1 200 OK
Content-Length: 2488
Content-Type: text/html
Response:

== rac4.domain.net:6200 3640 11/03/02 15:36:32 ==
Home: /opt/app/11.2.0/grid

======== ONS ========
     IP ADDRESS                   PORT    TIME   SEQUENCE  FLAGS
--------------------------------------- ----- -------- -------- --------
                     192.168.0.86  6200 4d6e6210 00000002 00000008

Listener:

TYPE                BIND ADDRESS               PORT  SOCKET
-------- --------------------------------------- ----- ------
Local                                  127.0.0.1  6100      5
Remote                                       any  6200      7
Remote                                       any  6200

ojdbc6.jar and ons.jar from a 11gR2 home was used for all the test.

The test code gets 5 jdbc connections from the connection pool and prints to which instance these connections point to and close them and waits 30 seconds and re-tries the same procedure. During the 30 second wait one of the instnaces is shutdown with

srvctl stop instance -d dbname -i instancename -o abort

If the FCF is working properly then connections to the aborted instance would have been cleaned up and all the connections in the pool will be valid connections, connected to surviving node(s).

Output from running against the 10gR2 RAC

java support.au.fcf11g.FCFDemo
>> PROGRAM using JDBC thin driver, no oracle client required
>> ojdbc5.jar (for JDK 1.5) or ojdbc6.jar (for JDK 1.6) and ons.jar from $ORACLE_HOME/opmn/lib directory must be in the CLASSPATH
>> Press CNTRL C to exit running program

** Retrieving 5 connections from pool **
5 connections have been retrieved..

=============
Database Product Name is ... Oracle
Database Product Version is  Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP, Data Mining
and Real Application Testing options
=============
JDBC Driver Name is ........ Oracle JDBC driver
JDBC Driver Version is ..... 11.2.0.2.0
JDBC Driver Major Version is 11
JDBC Driver Minor Version is 2
=============

-- Connection number #1
Instance -> rac10g21
Host -> rac1
Service -> rac10g2.domain.net
-- Connection number #2
Instance -> rac10g21
Host -> rac1
Service -> rac10g2.domain.net
-- Connection number #3
Instance -> rac10g22
Host -> rac2
Service -> rac10g2.domain.net
-- Connection number #4
Instance -> rac10g22
Host -> rac2
Service -> rac10g2.domain.net
-- Connection number #5
Instance -> rac10g22
Host -> rac2
Service -> rac10g2.domain.net

-- Displaying Cache Details --
NumberOfAvailableConnections: 0
NumberOfActiveConnections: 5
Number of query failures: 0
-----------

Sleeping for 30 seconds..
Woke up, will continue now..

** Retrieving 5 connections from pool **
5 connections have been retrieved..

-- Connection number #1
Instance -> rac10g22
Host -> rac2
Service -> rac10g2.domain.net
-- Connection number #2
Instance -> rac10g22
Host -> rac2
Service -> rac10g2.domain.net
-- Connection number #3
Instance -> rac10g22
Host -> rac2
Service -> rac10g2.domain.net
-- Connection number #4
Instance -> rac10g22
Host -> rac2
Service -> rac10g2.domain.net
-- Connection number #5
Instance -> rac10g22
Host -> rac2
Service -> rac10g2.domain.net

-- Displaying Cache Details --
NumberOfAvailableConnections: 0
NumberOfActiveConnections: 5
Number of query failures: 0
-----------

Sleeping for 30 seconds..

The two connections that were connected to instance rac10g21 has been cleaned up and all the connections now point to rac10g22.

Output from running against 11gR1 RAC

java support.au.fcf11g.FCFDemo
>> PROGRAM using JDBC thin driver, no oracle client required
>> ojdbc5.jar (for JDK 1.5) or ojdbc6.jar (for JDK 1.6) and ons.jar from $ORACLE_HOME/opmn/lib directory must be in the CLASSPATH
>> Press CNTRL C to exit running program

** Retrieving 5 connections from pool **
5 connections have been retrieved..

=============
Database Product Name is ... Oracle
Database Product Version is  Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP, Data Mining
and Real Application Testing options
=============
JDBC Driver Name is ........ Oracle JDBC driver
JDBC Driver Version is ..... 11.2.0.2.0
JDBC Driver Major Version is 11
JDBC Driver Minor Version is 2
=============

-- Connection number #1
Instance -> rac11g11
Host -> rac1
Service -> rac11g1.domain.net
-- Connection number #2
Instance -> rac11g11
Host -> rac1
Service -> rac11g1.domain.net
-- Connection number #3
Instance -> rac11g11
Host -> rac1
Service -> rac11g1.domain.net
-- Connection number #4
Instance -> rac11g12
Host -> rac2
Service -> rac11g1.domain.net
-- Connection number #5
Instance -> rac11g11
Host -> rac1
Service -> rac11g1.domain.net

-- Displaying Cache Details --
NumberOfAvailableConnections: 0
NumberOfActiveConnections: 5
Number of query failures: 0
-----------

Sleeping for 30 seconds..
Woke up, will continue now..

** Retrieving 5 connections from pool **
5 connections have been retrieved..

-- Connection number #1
Instance -> rac11g11
Host -> rac1
Service -> rac11g1.domain.net
-- Connection number #2
Instance -> rac11g11
Host -> rac1
Service -> rac11g1.domain.net
-- Connection number #3
Instance -> rac11g11
Host -> rac1
Service -> rac11g1.domain.net
-- Connection number #4
Instance -> rac11g11
Host -> rac1
Service -> rac11g1.domain.net
-- Connection number #5
Instance -> rac11g11
Host -> rac1
Service -> rac11g1.domain.net

-- Displaying Cache Details --
NumberOfAvailableConnections: 0
NumberOfActiveConnections: 5
Number of query failures: 0
-----------

Sleeping for 30 seconds..

There was one connection to rac11g12 instnace and this has been cleaned up and all the connections now point to rac11g11.

Now the 11.2 RACs first against 11.2.0.1

java support.au.fcf11g.FCFDemo
>> PROGRAM using JDBC thin driver, no oracle client required
>> ojdbc5.jar (for JDK 1.5) or ojdbc6.jar (for JDK 1.6) and ons.jar from $ORACLE_HOME/opmn/lib directory must be in the CLASSPATH
>> Press CNTRL C to exit running program

** Retrieving 5 connections from pool **
5 connections have been retrieved..

=============
Database Product Name is ... Oracle
Database Product Version is  Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options
=============
JDBC Driver Name is ........ Oracle JDBC driver
JDBC Driver Version is ..... 11.2.0.2.0
JDBC Driver Major Version is 11
JDBC Driver Minor Version is 2
=============

-- Connection number #1
Instance -> rac11g21
Host -> rac4
Service -> rac11g2.domain.net
-- Connection number #2
Instance -> rac11g22
Host -> rac5
Service -> rac11g2.domain.net
-- Connection number #3
Instance -> rac11g22
Host -> rac5
Service -> rac11g2.domain.net
-- Connection number #4
Instance -> rac11g21
Host -> rac4
Service -> rac11g2.domain.net
-- Connection number #5
Instance -> rac11g21
Host -> rac4
Service -> rac11g2.domain.net

-- Displaying Cache Details --
NumberOfAvailableConnections: 0
NumberOfActiveConnections: 5
Number of query failures: 0
-----------

Sleeping for 30 seconds..
Woke up, will continue now..

** Retrieving 5 connections from pool **
5 connections have been retrieved..

-- Connection number #1
Error running query: No more data to read from socket
-- Connection number #2
Error running query: No more data to read from socket
-- Connection number #3
Instance -> rac11g22
Host -> rac5
Service -> rac11g2.domain.net
-- Connection number #4
Error running query: No more data to read from socket
-- Connection number #5
Instance -> rac11g22
Host -> rac5
Service -> rac11g2.domain.net

-- Displaying Cache Details --
NumberOfAvailableConnections: 0
NumberOfActiveConnections: 5
Number of query failures: 3
-----------

Sleeping for 30 seconds..

As seen from the above output the FCF hasn't happened and an error is thrown on the java program.

Running against 11.2.0.2

java support.au.fcf11g.FCFDemo
>> PROGRAM using JDBC thin driver, no oracle client required
>> ojdbc5.jar (for JDK 1.5) or ojdbc6.jar (for JDK 1.6) and ons.jar from $ORACLE_HOME/opmn/lib directory must be in the CLASSPATH
>> Press CNTRL C to exit running program

** Retrieving 5 connections from pool **
5 connections have been retrieved..

=============
Database Product Name is ... Oracle
Database Product Version is  Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options
=============
JDBC Driver Name is ........ Oracle JDBC driver
JDBC Driver Version is ..... 11.2.0.2.0
JDBC Driver Major Version is 11
JDBC Driver Minor Version is 2
=============

-- Connection number #1
Instance -> rac11g22
Host -> rac5
Service -> rac11g2.domain.net
-- Connection number #2
Instance -> rac11g22
Host -> rac5
Service -> rac11g2.domain.net
-- Connection number #3
Instance -> rac11g21
Host -> rac4
Service -> rac11g2.domain.net
-- Connection number #4
Instance -> rac11g21
Host -> rac4
Service -> rac11g2.domain.net
-- Connection number #5
Instance -> rac11g21
Host -> rac4
Service -> rac11g2.domain.net

-- Displaying Cache Details --
NumberOfAvailableConnections: 0
NumberOfActiveConnections: 5
Number of query failures: 0
-----------

Sleeping for 30 seconds..
Woke up, will continue now..

** Retrieving 5 connections from pool **
5 connections have been retrieved..

-- Connection number #1
Error running query: No more data to read from socket
-- Connection number #2
Instance -> rac11g21
Host -> rac4
Service -> rac11g2.domain.net
-- Connection number #3
Instance -> rac11g21
Host -> rac4
Service -> rac11g2.domain.net
-- Connection number #4
Instance -> rac11g21
Host -> rac4
Service -> rac11g2.domain.net
-- Connection number #5
Error running query: No more data to read from socket

-- Displaying Cache Details --
NumberOfAvailableConnections: 0
NumberOfActiveConnections: 5
Number of query failures: 2
-----------

Sleeping for 30 seconds..

Since the FCF is not working when the application receives connections that are connected to the instnace that was shutdown error is thrown.

SR has been raised blog will be updated with the outcome.

Update 03 March 2011

Oracle has pointed out several metalink note regarding this. First it was stated this is expected behavior and mentioned in the metalink note
How To Use JDBC FCF Feature To Detect That a RAC Node Instance Has Been Shutdown ? [ID 364005.1]This error would have thrown if a node went down after a connection is taken from the pool and before doing some work with it. But the behavior change does not lie there but how connection pool cleans up invalid connections.

Two more metalink notes were pointed out Oracle Universal Connection Pool (UCP) and Deprecation of JDBC Implicit Connection Cache [ID 743726.1] and How to Verify Universal Connection Pool (UCP) / Fast Connection Failover (FCF) Setup [ID 1064652.1]

It seem with 11gR2 implicit connectoin caching is deprecated. Tests wer done using the code provided with 1064652.1 on 11gR2 and 11gR1 and still (with this whatever implicit caching mechanism present or not present on the jdbc side using UCP was common on both test cases) the difference on connection pool clean up is there. First test was on 11gR2 RAC.

----------- UCP Details ---------
NumberOfAvailableConnections: 4 <== 4 connection in the pool
BorrowedConnectionsCount: 1
---------------------------------

** UCPPool : connection returned to pool
** UCPPool : retrieveConnection
** FCFTest : Query #13  -> instance[rac11g22], host[rac5], service[rac11g2.domain.net]

----------- UCP Details ---------
NumberOfAvailableConnections: 4
BorrowedConnectionsCount: 1
---------------------------------

====================== instance shutdown =============================

** UCPPool : connection returned to pool
** UCPPool : retrieveConnection
** FCFTest : Connection retry necessary : No more data to read from socket
** FCFTest :
** UCPPool : connection returned to pool
** UCPPool : retrieveConnection
** FCFTest : Connection retry necessary : No more data to read from socket <== expected behavior on 11gR2
** FCFTest :
** UCPPool : connection returned to pool
** UCPPool : retrieveConnection
** FCFTest : Query #16  -> instance[rac11g22], host[rac5], service[rac11g2.domain.net]

----------- UCP Details ---------
NumberOfAvailableConnections: 2 <== connection pool cleaned up of invalid connections
BorrowedConnectionsCount: 1
---------------------------------

** UCPPool : connection returned to pool
** UCPPool : retrieveConnection
** FCFTest : Query #17  -> instance[rac11g22], host[rac5], service[rac11g2.domain.net]

----------- UCP Details ---------
NumberOfAvailableConnections: 2
BorrowedConnectionsCount: 1
---------------------------------

Now with 11gR1

java au.support.jdbc.scan.fcf.FCFTest
Started UCP FCF Test at Thu Mar 03 11:21:03 GMT 2011
** UCPPool : retrieveConnection
** FCFTest : Query #1  -> instance[rac11g12], host[rac2], service[rac11g1.domain.net]

----------- UCP Details ---------
NumberOfAvailableConnections: 4 <== 4 connections in the pool
BorrowedConnectionsCount: 1
---------------------------------

** UCPPool : connection returned to pool
** UCPPool : retrieveConnection
** FCFTest : Query #2  -> instance[rac11g11], host[rac1], service[rac11g1.domain.net]

----------- UCP Details ---------
NumberOfAvailableConnections: 4
BorrowedConnectionsCount: 1
---------------------------------
================== instance shutdown and connection pool gets cleaned up immediately.

** UCPPool : connection returned to pool
** UCPPool : retrieveConnection
** FCFTest : Query #3  -> instance[rac11g12], host[rac2], service[rac11g1.domain.net]

----------- UCP Details ---------
NumberOfAvailableConnections: 1 <== pool is cleaned up immediately no connection retry
BorrowedConnectionsCount: 1
---------------------------------

** UCPPool : connection returned to pool
** UCPPool : retrieveConnection
** FCFTest : Query #4  -> instance[rac11g12], host[rac2], service[rac11g1.domain.net]

----------- UCP Details ---------
NumberOfAvailableConnections: 1
BorrowedConnectionsCount: 1
---------------------------------

SR is ongoing will be updated.

Update 11 March 2011
Running the ucp test case with logging enabled showed the failover events in 11gR2 was missing the domain part of the service name whereas in 11gR1 the full service name including domain was sent. Logs below from the test for 11gR1

** UCPPool : connection returned to pool
2011-03-07T12:15:57.904+0000 UCP FINEST seq-43,thread-11 oracle.ucp.jdbc.oracle.OracleFailoverEventImpl.setEventType eventType: database/event/service
2011-03-07T12:15:57.904+0000 UCP FINEST seq-44,thread-11 oracle.ucp.jdbc.oracle.OracleFailoverEventImpl.validateEventType eventType: database/event/service
2011-03-07T12:15:57.904+0000 UCP FINEST seq-45,thread-11 oracle.ucp.jdbc.oracle.ONSDatabaseFailoverEvent. eventType: database/event/service, eventBody: VERSION=1.0 service=rac11g1.domain.net instance=rac11g11 database=rac11g1 host=rac1 status=down reason=user
2011-03-07T12:15:57.905+0000 UCP FINEST seq-46,thread-11 oracle.ucp.jdbc.oracle.OracleFailoverEventImpl.setServiceName serviceName: rac11g1.domain.net
2011-03-07T12:15:57.905+0000 UCP FINEST seq-47,thread-11 oracle.ucp.jdbc.oracle.OracleFailoverEventImpl.setInstanceName instanceName: rac11g11
2011-03-07T12:15:57.905+0000 UCP FINEST seq-48,thread-11 oracle.ucp.jdbc.oracle.OracleFailoverEventImpl.setDbUniqueName dbUniqueName: rac11g1
2011-03-07T12:15:57.906+0000 UCP FINEST seq-49,thread-11 oracle.ucp.jdbc.oracle.OracleFailoverEventImpl.setHostName hostName: rac1
2011-03-07T12:15:57.906+0000 UCP FINEST seq-50,thread-11 oracle.ucp.jdbc.oracle.OracleFailoverEventImpl.setStatus status: down
2011-03-07T12:15:57.906+0000 UCP FINEST seq-51,thread-11 oracle.ucp.jdbc.oracle.OracleFailoverEventImpl.setReason reason: user

On 11gR2 the domain name is missing on the service name

** UCPPool : connection returned to pool
2011-03-07T12:41:47.951+0000 UCP FINEST seq-49,thread-11 oracle.ucp.jdbc.oracle.OracleFailoverEventImpl.setEventType eventType: database/event/service
2011-03-07T12:41:47.951+0000 UCP FINEST seq-50,thread-11 oracle.ucp.jdbc.oracle.OracleFailoverEventImpl.validateEventType eventType: database/event/service
2011-03-07T12:41:47.952+0000 UCP FINEST seq-51,thread-11 oracle.ucp.jdbc.oracle.ONSDatabaseFailoverEvent. eventType: database/event/service, eventBody: VERSION=1.0 service=rac11g2 instance=rac11g21 database=rac11g2 host=rac4 status=down reason=USER
2011-03-07T12:41:47.952+0000 UCP FINEST seq-52,thread-11 oracle.ucp.jdbc.oracle.OracleFailoverEventImpl.setServiceName serviceName: rac11g2
2011-03-07T12:41:47.952+0000 UCP FINEST seq-53,thread-11 oracle.ucp.jdbc.oracle.OracleFailoverEventImpl.setInstanceName instanceName: rac11g21
2011-03-07T12:41:47.953+0000 UCP FINEST seq-54,thread-11 oracle.ucp.jdbc.oracle.OracleFailoverEventImpl.setDbUniqueName dbUniqueName: rac11g2
2011-03-07T12:41:47.953+0000 UCP FINEST seq-55,thread-11 oracle.ucp.jdbc.oracle.OracleFailoverEventImpl.setHostName hostName: rac4
2011-03-07T12:41:47.953+0000 UCP FINEST seq-56,thread-11 oracle.ucp.jdbc.oracle.OracleFailoverEventImpl.setStatus status: down
2011-03-07T12:41:47.954+0000 UCP FINEST seq-57,thread-11 oracle.ucp.jdbc.oracle.OracleFailoverEventImpl.setReason reason: user

According to Oracle Documentation provided throught the SRthis service name should match the service name in dba_services. Looking at this dba view

SQL> select service_id,name from dba_services;

SERVICE_ID NAME
---------- ------------
1 SYS$BACKGROUND
2 SYS$USERS
3 rac11g2sXDB
4 rac11g2s.domain.net
5 rac11g2XDB
6 rac11g2.domain.net

6 rows selected.

SQL> show parameter service

NAME TYPE VALUE
------------- ------ -------------------
service_names string rac11g2.domain.net

Pointed this out to oracle and was told this is related to "RDBMS Bug 9363352: SERVICE NAME HAS NO DOMAIN FOR UP/DOWN EVENTS, closed as not a bug" and at this point Oracle couldn't reproduce the error seen above (No more data to read from socket). Looking at the service name oracle is using noticed that it is a service name without a domain.

So created service name without a domain and tested.

srvctl add service -d rac11g2 -s nodomain -r "rac11g21,rac11g22"

srvctl start service -d rac11g2 -s nodomain

srvctl status service -d rac11g2 -s nodomain
Service nodomain is running on instance(s) rac11g21,rac11g22

SQL> show parameter service

NAME TYPE VALUE
------------- ------ ----------------------------
service_names string rac11g2.domain.net,nodomain

SQL> select name from dba_services;

NAME
---------------------
SYS$BACKGROUND
SYS$USERS
rac11g2sXDB
rac11g2s.domain.net
rac11g2XDB
rac11g2.domain.net
nodomain

$ lsnrctl status

LSNRCTL for Linux: Version 11.2.0.2.0 - Production on 11-MAR-2011 10:34:57

Copyright (c) 1991, 2010, Oracle.  All rights reserved.

Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 11.2.0.2.0 - Production
Start Date                07-MAR-2011 12:48:29
Uptime                    3 days 21 hr. 46 min. 27 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /opt/app/11.2.0/grid/network/admin/listener.ora
Listener Log File         /opt/app/oracle/diag/tnslsnr/rac4/listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=LISTENER)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.0.86)(PORT=1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.0.90)(PORT=1521)))
Services Summary...
Service "+ASM" has 1 instance(s).
Instance "+ASM1", status READY, has 1 handler(s) for this service...
Service "nodomain.domain.net" has 1 instance(s).
Instance "rac11g21", status READY, has 1 handler(s) for this service...
Service "rac11g2.domain.net" has 2 instance(s).
Instance "rac11g21", status UNKNOWN, has 1 handler(s) for this service...
Instance "rac11g21", status READY, has 1 handler(s) for this service...

Ran the test with logging enabled. This time the service name has the domain name even though it was created without a one and connection pool get refresh and validated quickly just as it was with 11gR1

** UCPPool : connection returned to pool
2011-03-11T10:25:00.022+0000 UCP FINEST seq-75,thread-11 oracle.ucp.jdbc.oracle.OracleFailoverEventImpl.setEventType eventType: database/event/service
2011-03-11T10:25:00.022+0000 UCP FINEST seq-76,thread-11 oracle.ucp.jdbc.oracle.OracleFailoverEventImpl.validateEventType eventType: database/event/service
2011-03-11T10:25:00.022+0000 UCP FINEST seq-77,thread-11 oracle.ucp.jdbc.oracle.ONSDatabaseFailoverEvent. eventType: database/event/service, eventBody: VERSION=1.0 service=nodomain.domain.net instance=rac11g22 database=rac11g2 host=rac5 status=down reason=USER
2011-03-11T10:25:00.023+0000 UCP FINEST seq-78,thread-11 oracle.ucp.jdbc.oracle.OracleFailoverEventImpl.setServiceName serviceName: nodomain.domain.net
2011-03-11T10:25:00.023+0000 UCP FINEST seq-79,thread-11 oracle.ucp.jdbc.oracle.OracleFailoverEventImpl.setInstanceName instanceName: rac11g22
2011-03-11T10:25:00.023+0000 UCP FINEST seq-80,thread-11 oracle.ucp.jdbc.oracle.OracleFailoverEventImpl.setDbUniqueName dbUniqueName: rac11g2
2011-03-11T10:25:00.024+0000 UCP FINEST seq-81,thread-11 oracle.ucp.jdbc.oracle.OracleFailoverEventImpl.setHostName hostName: rac5
2011-03-11T10:25:00.024+0000 UCP FINEST seq-82,thread-11 oracle.ucp.jdbc.oracle.OracleFailoverEventImpl.setStatus status: down
2011-03-11T10:25:00.024+0000 UCP FINEST seq-83,thread-11 oracle.ucp.jdbc.oracle.OracleFailoverEventImpl.setReason reason: user

Waiting for Oracle reply on this.

Update 06 April 2011
Oracle came back with this is similar to "unpublished bug 9740127 fixed in version 12" also mentioned couple of bugs 8779597 and 9740127 and pointed out that if a application service was created with domain name (instead of using the default database service) failover works as expected.

So created an application service (additional service as before)

 srvctl add service -d rac11g2 -s withdomain.domain.net -r "rac11g21,rac11g22"

Ran the test again and the connection failover was as before.

** UCPPool : connection returned to pool
** UCPPool : retrieveConnection
** FCFTest : Query #9  -> instance[rac11g21], host[rac4], service[withdomain.domain.net]

----------- UCP Details ---------
NumberOfAvailableConnections: 4
BorrowedConnectionsCount: 1
---------------------------------

** UCPPool : connection returned to pool
** UCPPool : retrieveConnection
** FCFTest : Query #10  -> instance[rac11g21], host[rac4], service[withdomain.domain.net]

----------- UCP Details ---------
NumberOfAvailableConnections: 0
BorrowedConnectionsCount: 1
---------------------------------

In other versions (10gR2 and 11gR1) default service with or without domain would work fine in a failover situation.
Created another database without a domain name and ran the test again and saw that failover wasn't working as expected.

** UCPPool : connection returned to pool
** UCPPool : retrieveConnection
** FCFTest : Query #181  -> instance[testdb1], host[rac4], service[testdb]

----------- UCP Details ---------
NumberOfAvailableConnections: 1
BorrowedConnectionsCount: 1
---------------------------------

** UCPPool : connection returned to pool
** UCPPool : retrieveConnection
** FCFTest : Connection retry necessary : No more data to read from socket
** FCFTest : Apr 6, 2011 2:14 PM SUCCESS     Connections:(Available=1 Affected=0 FailedToProcess=0 MarkedDown=0 Closed=0)(Borrowed=0 Affected=0 FailedToProcess=0 MarkedDown=0 MarkedDeferredClose=0 Closed=0) cardinality=2 targetedToTear=0 tornDown=0 markedToClose=0 targetUpEventNewConns=1 upEventNewConns=1
Apr 6, 2011 2:14 PM SUCCESS      Connections:(Available=2 Affected=1 FailedToProcess=0 MarkedDown=1 Closed=1)(Borrowed=0 Affected=0 FailedToProcess=0 MarkedDown=0 MarkedDeferredClose=0 Closed=0)
Apr 6, 2011 2:13 PM SUCCESS     Connections:(Available=1 Affected=0 FailedToProcess=0 MarkedDown=0 Closed=0)(Borrowed=0 Affected=0 FailedToProcess=0 MarkedDown=0 MarkedDeferredClose=0 Closed=0) cardinality=2 targetedToTear=0 tornDown=0 markedToClose=0 targetUpEventNewConns=1 upEventNewConns=1
Apr 6, 2011 2:12 PM SUCCESS      Connections:(Available=1 Affected=1 FailedToProcess=0 MarkedDown=1 Closed=1)(Borrowed=0 Affected=0 FailedToProcess=0 MarkedDown=0 MarkedDeferredClose=0 Closed=0)
Apr 6, 2011 2:12 PM SUCCESS     Connections:(Available=1 Affected=0 FailedToProcess=0 MarkedDown=0 Closed=0)(Borrowed=0 Affected=0 FailedToProcess=0 MarkedDown=0 MarkedDeferredClose=0 Closed=0) cardinality=2 targetedToTear=0 tornDown=0 markedToClose=0 targetUpEventNewConns=1 upEventNewConns=1
Apr 6, 2011 2:11 PM SUCCESS      Connections:(Available=2 Affected=1 FailedToProcess=0 MarkedDown=1 Closed=1)(Borrowed=0 Affected=0 FailedToProcess=0 MarkedDown=0 MarkedDeferredClose=0 Closed=0)
Apr 6, 2011 2:11 PM SUCCESS     Connections:(Available=1 Affected=0 FailedToProcess=0 MarkedDown=0 Closed=0)(Borrowed=0 Affected=0 FailedToProcess=0 MarkedDown=0 MarkedDeferredClose=0 Closed=0) cardinality=2 targetedToTear=0 tornDown=0 markedToClose=0 targetUpEventNewConns=1 upEventNewConns=1
Apr 6, 2011 2:10 PM SUCCESS      Connections:(Available=3 Affected=2 FailedToProcess=0 MarkedDown=2 Closed=2)(Borrowed=0 Affected=0 FailedToProcess=0 MarkedDown=0 MarkedDeferredClose=0 Closed=0)
Apr 6, 2011 2:10 PM SUCCESS     Connections:(Available=2 Affected=0 FailedToProcess=0 MarkedDown=0 Closed=0)(Borrowed=0 Affected=0 FailedToProcess=0 MarkedDown=0 MarkedDeferredClose=0 Closed=0) cardinality=2 targetedToTear=0 tornDown=0 markedToClose=0 targetUpEventNewConns=2 upEventNewConns=2
Apr 6, 2011 2:09 PM SUCCESS      Connections:(Available=6 Affected=4 FailedToProcess=0 MarkedDown=4 Closed=4)(Borrowed=0 Affected=0 FailedToProcess=0 MarkedDown=0 MarkedDeferredClose=0 Closed=0)
Apr 6, 2011 2:09 PM SUCCESS     Connections:(Available=3 Affected=0 FailedToProcess=0 MarkedDown=0 Closed=0)(Borrowed=0 Affected=0 FailedToProcess=0 MarkedDown=0 MarkedDeferredClose=0 Closed=0) cardinality=2 targetedToTear=0 tornDown=0 markedToClose=0 targetUpEventNewConns=3 upEventNewConns=3
Apr 6, 2011 2:08 PM SUCCESS      Connections:(Available=5 Affected=2 FailedToProcess=0 MarkedDown=2 Closed=2)(Borrowed=0 Affected=0 FailedToProcess=0 MarkedDown=0 MarkedDeferredClose=0 Closed=0)

** UCPPool : connection returned to pool
** UCPPool : retrieveConnection
** FCFTest : Query #183  -> instance[testdb2], host[rac5], service[testdb]

----------- UCP Details ---------
NumberOfAvailableConnections: 0
BorrowedConnectionsCount: 1
---------------------------------

Oracle confirmed this is related to using default database service and suggested to create and use application services instead of the default database service.

Data Guard Broker for 11gR2

2011-02-24T05:48:00.000-08:00

Data guard broker makes managing the data guard configuration easy by masking lot of the sql commands and multiple steps into few (sometimes one as in the case of switchover) data guard broker commands. Once the dataguard broker is setup it's advisable to use that to manipulate the dataguard configuration rather than the sql commands.
The data guard configuration created earlier will be configured to be managed through dataguard broker. There are some dataguard broker specific setup and configuration need to be done to the earlier setup.

1. Data guard broker requires a special naming convention to be followed in the value entered for global_dbname in listener.ora. Database sid should be suffixed with _DGMGRL if not data guard broker will not function properly. See metalink note Automatic Restart of Databases during Switchover fail with ORA-12514 in DGMGRL [ID 308943.1]
Change the earlier listener.ora files as below, on standby rac4b

more listener.ora

LISTENER=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER))))            # line added by Agent
LISTENER_SCAN1=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER_SCAN1))))                # line added by Agent
ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER_SCAN1=ON                # line added by Agent
ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER=ON              # line added by Agent


SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = rac11g2s.domain.net)
(SID_NAME = rac11g2s1)
(ORACLE_HOME = /opt/app/oracle/product/11.2.0/db_1)
)
(SID_DESC =
(GLOBAL_DBNAME = rac11g2s_DGMGRL.domain.net)
(SID_NAME = rac11g2s1)
(ORACLE_HOME = /opt/app/oracle/product/11.2.0/db_1)
)
)


SID_LIST_LISTENER_SCAN1 =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = rac11g2s.domain.net)
(SID_NAME = rac11g2s1)
(ORACLE_HOME = /opt/app/oracle/product/11.2.0/db_1)
)
(SID_DESC =
(GLOBAL_DBNAME = rac11g2s_DGMGRL.domain.net)
(SID_NAME = rac11g2s1)
(ORACLE_HOME = /opt/app/oracle/product/11.2.0/db_1)
)
)

on rac5b

more listener.ora

LISTENER_SCAN1=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER_SCAN1))))                # line added by Agent
LISTENER=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER))))            # line added by Agent
ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER=ON              # line added by Agent
ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER_SCAN1=ON                # line added by Agent


SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = rac11g2s.domain.net)
(SID_NAME = rac11g2s2)
(ORACLE_HOME = /opt/app/oracle/product/11.2.0/db_1)
)
(SID_DESC =
(GLOBAL_DBNAME = rac11g2s_DGMGRL.domain.net)
(SID_NAME = rac11g2s2)
(ORACLE_HOME = /opt/app/oracle/product/11.2.0/db_1)
)

)

SID_LIST_LISTENER_SCAN1 =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = rac11g2s.domain.net)
(SID_NAME = rac11g2s2)
(ORACLE_HOME = /opt/app/oracle/product/11.2.0/db_1)
)
(SID_DESC =
(GLOBAL_DBNAME = rac11g2s_DGMGRL.domain.net)
(SID_NAME = rac11g2s2)
(ORACLE_HOME = /opt/app/oracle/product/11.2.0/db_1)
)
)

Stop and start the listeners and check the static listener is available

srvctl stop listener -n `hostname -s`
srvctl start listener -n `hostname -s`

srvctl stop scan_listener
srvctl start scan_listener

lsnrctl status listener

LSNRCTL for Linux: Version 11.2.0.2.0 - Production on 23-FEB-2011 17:51:05

Copyright (c) 1991, 2010, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 11.2.0.2.0 - Production
Start Date                23-FEB-2011 17:42:04
Uptime                    0 days 0 hr. 9 min. 1 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /opt/app/11.2.0/grid/network/admin/listener.ora
Listener Log File         /opt/app/oracle/diag/tnslsnr/rac5b/listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=LISTENER)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.0.93)(PORT=1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.0.97)(PORT=1521)))
Services Summary...
Service "+ASM" has 1 instance(s).
Instance "+ASM2", status READY, has 1 handler(s) for this service...
Service "rac11g2s.domain.net" has 1 instance(s).
Instance "rac11g2s2", status UNKNOWN, has 1 handler(s) for this service...
Service "rac11g2s.domain.net" has 1 instance(s).
Instance "rac11g2s2", status READY, has 1 handler(s) for this service...
Service "rac11g2s_DGMGRL.domain.net" has 1 instance(s).
Instance "rac11g2s2", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully

To view the scan listener status it is important to set the ORACLE_HOME variable to GRID_HOME (or CRS_HOME whatever used) if not

lsnrctl status listener_scan1

TNS-01101: Could not find service name listener_scan1

will be shown.

export ORACLE_HOME=$CRS_HOME
$CRS_HOME/bin/lsnrctl status listener_scan1

LSNRCTL for Linux: Version 11.2.0.2.0 - Production on 23-FEB-2011 17:50:18

Copyright (c) 1991, 2010, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER_SCAN1)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER_SCAN1
Version                   TNSLSNR for Linux: Version 11.2.0.2.0 - Production
Start Date                23-FEB-2011 17:49:44
Uptime                    0 days 0 hr. 0 min. 34 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /opt/app/11.2.0/grid/network/admin/listener.ora
Listener Log File         /opt/app/11.2.0/grid/log/diag/tnslsnr/rac5b/listener_scan1/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=LISTENER_SCAN1)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.0.92)(PORT=1521)))
Services Summary...
Service "rac11g2s.domain.net" has 1 instance(s).
Instance "rac11g2s2", status UNKNOWN, has 1 handler(s) for this service...
Service "rac11g2s_DGMGRL.domain.net" has 1 instance(s).
Instance "rac11g2s2", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully

Similarly make the changes to primary listener.ora files as well. On rac4

LISTENER=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER))))            # line added by Agent
LISTENER_SCAN1=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER_SCAN1))))                # line added by Agent
ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER_SCAN1=ON                # line added by Agent
ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER=ON              # line added by Agent


SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = rac11g2.domain.net)
(SID_NAME = rac11g21)
(ORACLE_HOME = /opt/app/oracle/product/11.2.0/db_1)
)
(SID_DESC =
(GLOBAL_DBNAME = rac11g2_DGMGRL.domain.net)
(SID_NAME = rac11g21)
(ORACLE_HOME = /opt/app/oracle/product/11.2.0/db_1)
)
)


SID_LIST_LISTENER_SCAN1 =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = rac11g2.domain.net)
(SID_NAME = rac11g21)
(ORACLE_HOME = /opt/app/oracle/product/11.2.0/db_1)
)
(SID_DESC =
(GLOBAL_DBNAME = rac11g2_DGMGRL.domain.net)
(SID_NAME = rac11g21)
(ORACLE_HOME = /opt/app/oracle/product/11.2.0/db_1)
)
)

On rac5

LISTENER_SCAN1=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER_SCAN1))))                # line added by Agent
LISTENER=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER))))            # line added by Agent
ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER=ON              # line added by Agent
ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER_SCAN1=ON                # line added by Agent


SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = rac11g2.domain.net)
(SID_NAME = rac11g22)
(ORACLE_HOME = /opt/app/oracle/product/11.2.0/db_1)
)
(SID_DESC =
(GLOBAL_DBNAME = rac11g2_DGMGRL.domain.net)
(SID_NAME = rac11g22)
(ORACLE_HOME = /opt/app/oracle/product/11.2.0/db_1)
)
)


SID_LIST_LISTENER_SCAN1 =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = rac11g2.domain.net)
(SID_NAME = rac11g22)
(ORACLE_HOME = /opt/app/oracle/product/11.2.0/db_1)
)
(SID_DESC =
(GLOBAL_DBNAME = rac11g2_DGMGRL.domain.net)
(SID_NAME = rac11g22)
(ORACLE_HOME = /opt/app/oracle/product/11.2.0/db_1)
)
)

As with the standby restart the listeners and verify the static listener is available.

2. There are three initialization parameters that is related to data guard broker

SQL> show parameter dg

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
dg_broker_config_file1               string      /opt/app/oracle/product/11.2.0
/db_1/dbs/dr1rac11g2.dat
dg_broker_config_file2               string      /opt/app/oracle/product/11.2.0
/db_1/dbs/dr2rac11g2.dat
dg_broker_start                      boolean     FALSE

In a RAC environment dg_broker_config_file1 and dg_broker_config_file2 must be stored in a shared location that could be accessed by all instances. The location could be Cluster file system,Raw devices or ASM. These location must be same on all instances. The file need not exist but if a new location is created to store them create it beforehand. In this case these files were stored in ASM and a directory was created inside asm diskgroup. On primary

ASMCMD>cd data/rac11g2
pwd
+data/rac11g2
ASMCMD> mkdir dgbroker

On standby

ASMCMD>cd data/rac11g2s
pwd
+data/rac11g2s
ASMCMD> mkdir dgbroker

Then set the intialization parameters on primary with

SQL> alter system set dg_broker_config_file1='+DATA/rac11g2/dgbroker/dr1rac11g2.dat' scope=both sid='*';
System altered.

SQL> alter system set dg_broker_config_file2='+DATA/rac11g2/dgbroker/dr2rac11g2.dat' scope=both sid='*';
System altered.

On standby

SQL> alter system set dg_broker_config_file1='+DATA/rac11g2s/dgbroker/dr1rac11g2s.dat' scope=both sid='*';
System altered.

SQL> alter system set dg_broker_config_file2='+DATA/rac11g2s/dgbroker/dr2rac11g2s.dat' scope=both sid='*';
System altered.

3. Enterprise Manager will automatically set the next required initialization parameter DG_BROKER_START to TRUE for new standby databases that it creates. But as in this case when using DGMGRL, this must be explicitly set to true otherwise dataguard broker daemon will not start. Both on primary and standby set

SQL> alter system set dg_broker_start=true scope=both sid='*';
System altered.

When this gets executed following message will appear on the alert log indicating the start of data guard broker daemon

Thu Feb 24 11:04:54 2011
DMON started with pid=56, OS id=16310
Starting Data Guard Broker (DMON)
Thu Feb 24 11:05:08 2011
INSV started with pid=60, OS id=16327

4. Next step is to create the data guard broker configuration. This must be created on the primary running on standby would give the following error

DGMGRL> create configuration rac11g2_dgb as primary database is rac11g2 connect identifier is rac11g2;
Error: ORA-16584: operation cannot be performed on a standby database

Failed.

5. On primary run dgmgrl command line tool to create the configuration

$ dgmgrl
DGMGRL for Linux: Version 11.2.0.2.0 - 64bit Production

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

Welcome to DGMGRL, type "help" for information.
DGMGRL> connect sys
Password:
Connected.

DGMGRL> create configuration rac11g2_dgb as primary database is rac11g2 connect identifier is rac11g2;
Configuration "rac11g2_dgb" created with primary database "rac11g2"

DGMGRL> show configuration

Configuration - rac11g2_dgb

Protection Mode: MaxPerformance
Databases:
rac11g2 - Primary database

Fast-Start Failover: DISABLED

Configuration Status:
DISABLED

Syntax to add the physical standby could be of many forms. In 11gR1 documentation "maintained as physical" was used to explicity indicate the database added is a standby. But in 11gR2 this could be ommited and configuration recognize the database role and configure it accordingly. Both commands are shown below

DGMGRL> add database rac11g2s as connect identifier is rac11g2s;
Database "rac11g2s" added
DGMGRL> show configuration

Configuration - rac11g2_dgb

Protection Mode: MaxPerformance
Databases:
rac11g2  - Primary database
rac11g2s - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:
DISABLED

To remove a database from the configuration use

DGMGRL> remove database rac11g2s;
Removed database "rac11g2s" from the configuration

The 11gR1 syntax

DGMGRL> add database rac11g2s as connect identifier is rac11g2s maintained as physical;
Database "rac11g2s" added
DGMGRL> show configuration

Configuration - rac11g2_dgb

Protection Mode: MaxPerformance
Databases:
rac11g2  - Primary database
rac11g2s - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:
DISABLED

6. Even though configuration is created it is disabled. As the final step enable the configuration

DGMGRL> enable configuration;
Enabled.

Following could be observed on the primary alert log

Thu Feb 24 11:12:53 2011
RSM0 started with pid=61, OS id=16735
ALTER SYSTEM SET log_archive_trace=0 SCOPE=BOTH SID='rac11g21';
ALTER SYSTEM SET log_archive_format='%t_%s_%r.dbf' SCOPE=SPFILE SID='rac11g21';
ALTER SYSTEM SET standby_file_management='AUTO' SCOPE=BOTH SID='*';
ALTER SYSTEM SET archive_lag_target=0 SCOPE=BOTH SID='*';
ALTER SYSTEM SET log_archive_max_processes=10 SCOPE=BOTH SID='*';
ALTER SYSTEM SET log_archive_min_succeed_dest=1 SCOPE=BOTH SID='*';
ALTER SYSTEM SET db_file_name_convert='+DATA/rac11g2s','+DATA/rac11g2' SCOPE=SPFILE;
ALTER SYSTEM SET log_file_name_convert='+FLASH/rac11g2s','+FLASH/rac11g2','+DATA/rac11g2s','+DATA/rac11g2' SCOPE=SPFILE;
ALTER SYSTEM ARCHIVE LOG
Thu Feb 24 11:13:01 2011
Thread 1 advanced to log sequence 125 (LGWR switch)
Current log# 1 seq# 125 mem# 0: +DATA/rac11g2/onlinelog/group_1.261.740770161
Current log# 1 seq# 125 mem# 1: +FLASH/rac11g2/onlinelog/group_1.257.740770161
Thu Feb 24 11:13:03 2011
LNS: Standby redo logfile selected for thread 1 sequence 125 for destination LOG_ARCHIVE_DEST_1
Thu Feb 24 11:13:03 2011
Archived Log entry 353 added for thread 1 sequence 124 ID 0x16d045c1 dest 10:

and on standby alert log

Thu Feb 24 12:13:50 2011
RSM0 started with pid=54, OS id=19480
ALTER SYSTEM SET log_archive_trace=0 SCOPE=BOTH SID='rac11g2s1';
ALTER SYSTEM SET log_archive_format='%t_%s_%r.dbf' SCOPE=SPFILE SID='rac11g2s1';
ALTER SYSTEM SET standby_file_management='AUTO' SCOPE=BOTH SID='*';
ALTER SYSTEM SET archive_lag_target=0 SCOPE=BOTH SID='*';
ALTER SYSTEM SET log_archive_max_processes=5 SCOPE=BOTH SID='*';
ALTER SYSTEM SET log_archive_min_succeed_dest=1 SCOPE=BOTH SID='*';
ALTER SYSTEM SET db_file_name_convert='+DATA/rac11g2','+DATA/rac11g2s' SCOPE=SPFILE;
ALTER SYSTEM SET log_file_name_convert='+FLASH/rac11g2','+FLASH/rac11g2s','+DATA/rac11g2','+DATA/rac11g2s' SCOPE=SPFILE;
ALTER SYSTEM SET fal_server='rac11g2' SCOPE=BOTH;
Thu Feb 24 12:14:00 2011
RFS[2]: Selected log 5 for thread 1 sequence 125 dbid 379668842 branch 740770160
Thu Feb 24 12:14:00 2011
Archived Log entry 189 added for thread 1 sequence 124 ID 0x16d045c1 dest 10:
Thu Feb 24 12:14:01 2011
RFS[5]: Selected log 8 for thread 2 sequence 117 dbid 379668842 branch 740770160
Thu Feb 24 12:14:01 2011
Archived Log entry 190 added for thread 2 sequence 116 ID 0x16d045c1 dest 10:

7. Check the configuration status with

DGMGRL> show configuration

Configuration - rac11g2_dgb

Protection Mode: MaxPerformance
Databases:
rac11g2  - Primary database
rac11g2s - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS

If the status is not success then resolve any issues. Sometimes it is possible to have a warning status

DGMGRL> show configuration

Configuration - rac11g2_dgb

Protection Mode: MaxPerformance
Databases:
rac11g2  - Primary database
Warning: ORA-16792: configurable property value is inconsistent with database setting

rac11g2s - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:
WARNING

Looking at the error number

oerr ora 16792
16792, 0000, "configurable property value is inconsistent with database setting"
// *Cause:  The values of one or more configurable properties were
//          inconsistent with database in-memory settings or server parameter
//          file settings. This may happen by directly altering initialization
//          parameters instead of editing configurable property values using
//          Data Guard broker.
// *Action: Query the InconsistentProperties property on the database or check
//          the Data Guard broker log to find which properties are set
//          inconsistently. Reset these properties to make them consistent
//          with the database settings. Alternatively, enable the database
//          or the entire configuration to allow the configurable property
//          settings to be propagated to the initialization parameters.

it was possible to bring all the instance into a consistent state with a database restart

srvctl stop database -d rac11g2
srvctl start database -d rac11g2

gmgrl
DGMGRL for Linux: Version 11.2.0.2.0 - 64bit Production

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

Welcome to DGMGRL, type "help" for information.
DGMGRL> connect sys
Password:
Connected.
DGMGRL> show configuration

Configuration - rac11g2_dgb

Protection Mode: MaxPerformance
Databases:
rac11g2  - Primary database
rac11g2s - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS

8. Check the status of each database in the data guard broker configuration

DGMGRL> show database verbose rac11g2

Database - rac11g2

Role:            PRIMARY
Intended State:  TRANSPORT-ON
Instance(s):
rac11g21
rac11g22

Properties:
DGConnectIdentifier             = 'rac11g2'
ObserverConnectIdentifier       = ''
LogXptMode                      = 'ASYNC'
DelayMins                       = '0'
Binding                         = 'optional'
MaxFailure                      = '0'
MaxConnections                  = '1'
ReopenSecs                      = '300'
NetTimeout                      = '30'
RedoCompression                 = 'DISABLE'
LogShipping                     = 'ON'
PreferredApplyInstance          = ''
ApplyInstanceTimeout            = '0'
ApplyParallel                   = 'AUTO'
StandbyFileManagement           = 'AUTO'
ArchiveLagTarget                = '0'
LogArchiveMaxProcesses          = '10'
LogArchiveMinSucceedDest        = '1'
DbFileNameConvert               = '+DATA/rac11g2s, +DATA/rac11g2'
LogFileNameConvert              = '+FLASH/rac11g2s, +FLASH/rac11g2, +DATA/rac11g2s, +DATA/rac11g2'
FastStartFailoverTarget         = ''
InconsistentProperties          = '(monitor)'
InconsistentLogXptProps         = '(monitor)'
SendQEntries                    = '(monitor)'
LogXptStatus                    = '(monitor)'
RecvQEntries                    = '(monitor)'
SidName(*)
StaticConnectIdentifier(*)
StandbyArchiveLocation(*)
AlternateLocation(*)
LogArchiveTrace(*)
LogArchiveFormat(*)
TopWaitEvents(*)
(*) - Please check specific instance for the property value

Database Status:
SUCCESS


DGMGRL> show database verbose rac11g2s

Database - rac11g2s

Role:            PHYSICAL STANDBY
Intended State:  APPLY-ON
Transport Lag:   0 seconds
Apply Lag:       0 seconds
Real Time Query: OFF
Instance(s):
rac11g2s1 (apply instance)
rac11g2s2

Properties:
DGConnectIdentifier             = 'rac11g2s'
ObserverConnectIdentifier       = ''
LogXptMode                      = 'ASYNC'
DelayMins                       = '0'
Binding                         = 'MANDATORY'
MaxFailure                      = '10'
MaxConnections                  = '5'
ReopenSecs                      = '180'
NetTimeout                      = '30'
RedoCompression                 = 'DISABLE'
LogShipping                     = 'ON'
PreferredApplyInstance          = ''
ApplyInstanceTimeout            = '0'
ApplyParallel                   = 'AUTO'
StandbyFileManagement           = 'AUTO'
ArchiveLagTarget                = '0'
LogArchiveMaxProcesses          = '5'
LogArchiveMinSucceedDest        = '1'
DbFileNameConvert               = '+DATA/rac11g2, +DATA/rac11g2s'
LogFileNameConvert              = '+FLASH/rac11g2, +FLASH/rac11g2s, +DATA/rac11g2, +DATA/rac11g2s'
FastStartFailoverTarget         = ''
InconsistentProperties          = '(monitor)'
InconsistentLogXptProps         = '(monitor)'
SendQEntries                    = '(monitor)'
LogXptStatus                    = '(monitor)'
RecvQEntries                    = '(monitor)'
SidName(*)
StaticConnectIdentifier(*)
StandbyArchiveLocation(*)
AlternateLocation(*)
LogArchiveTrace(*)
LogArchiveFormat(*)
TopWaitEvents(*)
(*) - Please check specific instance for the property value

Database Status:
SUCCESS

From 11gR2 data guard broker documentation "If the standby database is an Oracle RAC database, the broker starts Redo Apply on exactly one standby instance, called the apply instance. If this instance fails, the broker automatically chooses another instance that is either mounted or open read-only. This new instance then becomes the apply instance."

Switchover Using Data Guard Broker

To test all the configuration parameters are setup properly a switchover will be carried out using the data guard broker. Data guard broker will take care of all the steps that were carried out manually during earlier switchover scenario using sql commands.

To switchover using data guard broker all that is required is issue the command "switchover to" followed by the database name to switchover to.

DGMGRL> switchover to rac11g2s
Performing switchover NOW, please wait...
New primary database "rac11g2s" is opening...
Operation requires shutdown of instance "rac11g21" on database "rac11g2"
Shutting down instance "rac11g21"...
ORACLE instance shut down.
Operation requires startup of instance "rac11g21" on database "rac11g2"
Starting instance "rac11g21"...
ORACLE instance started.
Database mounted.
Switchover succeeded, new primary is "rac11g2s"

. At the time issuing this there were two primary instances (rac11g21 and rac11g22) in open read,write mode and two standby instance (rac11g2s1 and rac11g2s2) in mount mode.

The command was issued from rac11g21 instance and as soon as it was issued rac11g22 was shutdown by the data guard broker(with shutdown abort) as seen from rac11g22 alert log

Thu Feb 24 15:35:50 2011
Shutting down instance (abort)
License high water mark = 5
USER (ospid: 22346): terminating the instance
Instance terminated by USER, pid = 22346
Thu Feb 24 15:35:50 2011
Instance shutdown complete

rac11g21 disconnect all the sessions and commit to switchover with session shutdown as seen from rac11g21 alert log (only partial information is shown here, not all the entries generated at the time of switchover)

Thu Feb 24 15:35:53 2011
ALTER DATABASE COMMIT TO SWITCHOVER TO PHYSICAL STANDBY WITH SESSION SHUTDOWN
ALTER DATABASE COMMIT TO SWITCHOVER TO PHYSICAL STANDBY [Process Id: 26886] (rac11g21)
Thu Feb 24 15:35:53 2011
Thread 1 advanced to log sequence 184 (LGWR switch)
Current log# 1 seq# 184 mem# 0: +DATA/rac11g2/onlinelog/group_1.261.740770161
Current log# 1 seq# 184 mem# 1: +FLASH/rac11g2/onlinelog/group_1.257.740770161
Thu Feb 24 15:35:53 2011
Stopping background process CJQ0
Stopping background process QMNC
Thu Feb 24 15:35:54 2011
Archived Log entry 541 added for thread 2 sequence 173 ID 0x16d1ac74 dest 10:
All dispatchers and shared servers shutdown
CLOSE: killing server sessions.
Active process 32445 user 'oracle' program 'oracle@rac4.domain.net (W000)'
Active process 32445 user 'oracle' program 'oracle@rac4.domain.net (W000)'
Active process 437 user 'oracle' program 'oracle@rac4.domain.net (TNS V1-V3)'
Active process 32445 user 'oracle' program 'oracle@rac4.domain.net (W000)'
..
..
Switchover: Complete - Database shutdown required
Completed: ALTER DATABASE COMMIT TO SWITCHOVER TO PHYSICAL STANDBY WITH SESSION SHUTDOWN

Once the shutdown is complete rac11g21 is started as the new standby and log apply process is started

ALTER SYSTEM SET fal_server='rac11g2s' SCOPE=BOTH;
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE  THROUGH ALL SWITCHOVER DISCONNECT  USING CURRENT LOGFILE
Attempt to start background Managed Standby Recovery process (rac11g21)
Thu Feb 24 15:36:29 2011
MRP0 started with pid=64, OS id=719
MRP0: Background Managed Standby Recovery process started (rac11g21)
started logmerger process
Thu Feb 24 15:36:34 2011
Managed Standby Recovery starting Real Time Apply
Parallel Media Recovery started with 2 slaves
Waiting for all non-current ORLs to be archived...
All non-current ORLs have been archived.
Clearing online redo logfile 1 +DATA/rac11g2/onlinelog/group_1.261.740770161
Clearing online log 1 of thread 1 sequence number 189
Completed: ALTER DATABASE RECOVER MANAGED STANDBY DATABASE  THROUGH ALL SWITCHOVER DISCONNECT  USING CURRENT LOGFILE
Clearing online redo logfile 1 complete

The other instance in this new standby (old primary) is also stated in the mount mode.

While the above is going on there's activity on the old standby (rac11g2s, new primary) as well. As soon as the switchover command is issued the apply instance (rac11g2s1) at standby disconnect from the primary and stop the log apply process

Thu Feb 24 16:36:47 2011
RFS[11]: Possible network disconnect with primary database
Thu Feb 24 16:36:47 2011
RFS[9]: Possible network disconnect with primary database
Thu Feb 24 16:36:47 2011
RFS[7]: Possible network disconnect with primary database
Thu Feb 24 16:36:47 2011
RFS[8]: Possible network disconnect with primary database
Thu Feb 24 16:36:47 2011
RFS[14]: Assigned to RFS process 29873
RFS[14]: Possible network disconnect with primary database
..
..
Resetting standby activation ID 382839924 (0x16d1ac74)
Thu Feb 24 16:36:56 2011
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL
Media Recovery Waiting for thread 1 sequence 185
MRP0: Background Media Recovery cancelled with status 16037

Once this completes it switchover to the primary database role and open the database

MRP0: Background Media Recovery process shutdown (rac11g2s1)
Managed Standby Recovery Canceled (rac11g2s1)
Completed: ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL
ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY WAIT WITH SESSION SHUTDOWN
ALTER DATABASE SWITCHOVER TO PRIMARY (rac11g2s1)
Maximum wait for role transition is 15 minutes.

As soon as the switchover is complete other instances in the (old)standby are also brought up in open mode and the new primary is available for use. Following from the rac11g2s2 alert log

Thu Feb 24 16:36:56 2011
Managed Standby Recovery not using Real Time Apply
Switchover: Complete - Database mounted as primary
Thu Feb 24 16:37:02 2011
ALTER DATABASE OPEN
Data Guard Broker initializing...
Picked broadcast on commit scheme to generate SCNs

Checking the new configuration

DGMGRL> show configuration

Configuration - rac11g2_dgb

Protection Mode: MaxPerformance
Databases:
rac11g2s - Primary database
rac11g2  - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS

DGMGRL> show database verbose rac11g2

Database - rac11g2

Role:            PHYSICAL STANDBY
Intended State:  APPLY-ON
Transport Lag:   0 seconds
Apply Lag:       0 seconds
Real Time Query: OFF
Instance(s):
 rac11g21 (apply instance)
rac11g22
..
..

Unlike 11gR1 configuration 11gR2 does use the VIPs for StaticConnectIdentifier.