"...Coolest Recovery Demo Ever..."

Yesterday, I got the chance to perform a presentation at the Dutch Oracle User Group (OGh) DBA day about a topic that I have blogged about a couple of days ago: Database Crash Recovery : To Restore Or Not Restore.

I proved my "theory" with a live demo. In VMware I crashed my database after having it backed up, after which I was able to recover within 5 minutes, without restoring the backup. All changes were kept, even the changes after the last incremental backup was taken... (I admit, I was impressed myself, too!:-)

The presentation went well, and today I received some comments from some of the attendees, telling me that the presentation was received extremely well. One of the attendees even twittered about "the coolest recovery demo ever".

For anyone interested in the solution/approach: The concepts of this approach are outlined in this and this article, founde elsewhere on my weblog.

Happy reading and testing!

Oracle E-Business Suite Release 12.1.2 - New Features

Wondering what the next release of E-Business Suite R12 will contain? Steven Chan posted a weblog about the availability of the 12.1.2 RCD yesterday, way before R12.1.2 is made available (the release date is not yet known).
However, it is always interesting to see what the next upcoming release will contain. I will stick to the Applications Technology, because that is my part of the pond:

Technology Highlights

• The most eye-catching new feature is support for 11gR2 database. Unfortunately (in a way) 11gR2 will not be shipped with 12.1.2. It'll still come with 11.1.0.7.
• On the middle-tier, support is introduced for AS 10gR3 (10.1.3.5) which will be shipped with the release, JDeveloper 10.1.3 and it uses the Sun Java Plug-in version 6.
• The Application Server for Forms and Reports will be 10.1.2.3. The maintenance pack will either include or require customers to upgrade to this version.
• Of all externally installed Oracle technology (WebCenter, Discoverer, Portal, Internet Directory, SSO, BPEL PM, OBIEE, OEM, etc) the most recent version will be certified with 12.1.2.

Internationalization Highlights

• Added support for Lithuanian, Indonesian and Ukrainian languages.
• Space and single quote number group separators
• First day of the week support (choose whichever day is the first day of the week)
• The Gantt chart and inline date picker features support Thai and Hijrah calendars

Oracle Applications Framework Highlights

• Redesigned Home Page
  new folder-like structure, better spacing, favorites are now in a pull-down menu. In general the home page is more compact and allows for easy extension and custom content.
• New Navigator Menu and favorites Pull-Down menue in the Global Header area
  The Navigator Menu now appears in every OAF page (Pull-down menu). Favorites menu is also a pull down menu from the Global Header area and allows for marking the current page as favorite.
• Look-ahead capability in lists of values
  When entering a limited number of characters in a field, an inline window will let you select from a list of values based on the characters you typed. This way, you can limit the number of values in the inline window and ease your search for the correct value.
• ability to add, view, edit and delete attachments inline
  Inline attachments now have a hover-over interaction allowing users to view, add, edit and delete attachments to an entity. This capability eliminates the need to go to the Add and View attachments pages to maintain attachments.
• pop-up component
  This allows applications to display a pop-up window on top of an OAF page.
• Portlet Generator
  For static conversion of a standalone OAF region into a portlet. These can be embedded in any portal application or WebCenter.
• A new component: rich container that can hold OBIEE content.
  Through personalization, users can configure this component to hold a certain OBIEE report that accepts specific parameters.

Oracle Application Object Library Highlights

• Oracle Access Manager integration support
  OAM can be used in stead of Oracle Single Sign On for thos applications or products that support it, but it can also be used together with OSSO to provide centralized authentication.
• AOL Java APIs
  Support for Oracle E-Business Suite Release 12.1.2 is provided
• Scheduling Charts UI Enhancements
  Dragging task numbers into the scheduling chart to create new tasks and shifts on the fly. Simplified ability for creating, assigning, searching and displaying new task assignments from a single source.

Diagnostics Framework

• Added support for XML file type diagnostics.

Integrated SOA Gateway

• SAML Token Support
  E-Business Suite environments can trust external systems. These external systems can use a public key to authenticate a SOAP request using SAML.
• Enhanced SOA Diagnostics
  To check the health of the EBS Integrated SOA Gateway.
• New interface type: Java APIs for Forms
  these are XML document based interfaces, wrapped in Java classes to execute logic in Oracle Forms. Integrated in the Oracle Integration Repository (iRep)
• Integrated SOA Gateway Customization
  Support for custom integration interfaces, that can be deployed as custom Web Services. Customization feature is provided through the Integration Repository Parser.

Web ADI Highlights

• Introducing the Web Applications Desktop Integrator Extension Framework
  wizard-based UI to create and manage desktop integrator solutions, providing a user-friendly approach to create custom integrators, without having to know the complexity of the EBS Schema.

Conclusion

My personal curiosity is focused on 11gR2 (I would be really interested in seeing the E-Business Suite running on 11gR2 Grid Infrastructure!). For sure there is enough to lookout for. Again, I am eagerly awaiting this new release.

using the RMAN backup as copy as base for Disaster Recovery

In an earlier post I have elaborated on the advantages the backup as copy feature offers to quickly recover from a damaged database.

In this article I will try to show how this advantage goes even further by adding another server to the infrastructure, introducing some kind of Disaster Recovery solution. OK, it is not fully featured like Oracle Dataguard, but it works and can be the first step towards Disaster Recovery. No need for a standby database, redo log shipping etc. It is all plain simple and straight-forward.

Shopping list:

1. 2 servers, not necessarily equal in specifications, but having identical Operating Systems
2. Clustered File System (OCFS2 will suffice, even NFS will do the job)

Preparations

First of all, you need to set up OCFS. Tons of information out there on the Internet to get your OCFS working. It is not difficult, See this PDF for information.
What I have done to make this work is set up 2 OCFS2 File Systems, one under /oradata and one under /orafra.
Next, install the Oracle RDBMS software on both machines, each using the same location for the Oracle Home and installing identical copies.

Once you have OCFS2 set up on your 2 servers, create or move your database onto /oradata, and make sure the DB_FILE_RECOVERY_DEST parameter points to /orafra and the DB_FILE_CREATION_DEST points to /oradata. Don't forget the DB_FILE_RECOVERY_DEST_SIZE parameter! Set it to a size equal to the file system where it is located, to make sure you will not run out of space too soon.

Your infrastructure will look like this (more or less):

/oradata is the location for you database, /orafra is for the Flash Recovery Area.

Now we can prepare the secondary machine to serve as Failover server.

To do this, take the following steps:

1. Copy the server parameter file and bring it to the secondary machine under $ORACLE_HOME/dbs. Edit this file and make adjustments to the following parameters:
CONTROL_FILES = /orafra/SID/controlfile/cp_cntrl.ctl
DB_FILE_RECOVERY_DEST = /oradata
DB_FILE_CREATE_DEST = /orafra
Optionally, you can create an spfile from this parameter file on the secondary machine.

2. Copy the /etc/oratab file to the secondary node

3. Create the necessary dump directories ($ORACLE_HOME/admin)

Now you are ready to go! Bring up your database on the primary machine and create a full backup as copy from your database:

allocate channel for maintenance type disk;
configure controlfile autobackup on;
configure default device type to disk;
run
{
RECOVER COPY OF DATABASE WITH TAG ‘IMG_COPY’ UNTIL TIME ‘SYSDATE – 1’;
BACKUP INCREMENTAL LEVEL 1 FOR RECOVER OF COPY WITH TAG ‘IMG_COPY’ DATABASE PLUS ARCHIVELOG DELETE INPUT;
copy current controlfile to ‘/orafra//controlfile/cp_cntrl.ctl’;
}

This script will do everything for you. See this post for an explanation of the script.

Now let's make some changes to the database and run the backup script again (NOTE: in order to have all changes you made to the database immediately reflected in the copy of your database, remove the UNTIL clause in the first line of the backup script).

What you can do now, is to simulate a crash of the database (there are a lot of ways to do this, I would recommend you just issue a shutdown abort at your first attempt).

Go to the second machine, log in as the oracle user, source your environment and perform the following steps to recover your database:

1. start an rman session to the database (which is not started yet)
2. mount the controlfile (located in /orafra/SID/controlfile, pointed to by the parameter file)
3. issue "switch database to copy"
4. issue "recover database"
5. issue "alter database open resetlogs"

That is all it takes. Now your database is opened and available from the secondary machine. Now make sure that you reverse file locations in the backup scripts on the secondary machine to reflect the database file locations and the Flash Recovery Area. The database files should be located in /orafra, the flash recovery area should be located in /oradata. If you want to switch back, just use the backup script to create another backup as copy and switch back the way you did earlier. Make sure you have defined the correct file locations on the primary server, alike the secondary machine, to smoothen the switch.

Caveats

Like what is the case with any other regular backup, when you have made changes to the physical structure of your database (like adding datafiles, tablespaces, etc), an incremental backup needs to be taken immediately after, to prevent issues while recovering the database from the copy.

Another caveat is that it is necessary to have both database servers available in your tns alias being used to access the database. In such a case it is important to have load balancing set to off and failover set to on, having the primary as the first address.

Alternatives

If your server is still available, but the database has become corrupted, you can easily switch to the copy without the need for the secondary machine with a similar procedure as mentioned above. This way you will always have the choice of what to do.

Happy testing!

Oracle 11g Release 2 Clusterware New Feature: Policy-based Cluster and capacity management

I was reading through the Oracle 11g Release 2 Real Applications Cluster New Features List and stumbled upon this really cool new feature. I have to tell you about it, because this, imho, is one of the things that makes Oracle Clusterware a “Grid”.

So, what is Policy-based management? In order to answer that, you must first understand some basic changes in 11gR2. This is what the manual says:

With Oracle Clusterware 11g release 2 (11.2) and later, resources are contained in logical groups of servers called server pools. Resources are hosted on a shared infrastructure and are isolated with respect to their resource consumption by policies, behaving as if they were deployed in a single-system environment.

What does this mean? I have a bunch of resources and a bunch of servers (a grid) and subsets of these servers are combined in server pools. The resources are assigned to these server pools, but the server pool acts as if it were just one system. The isolation is done on a policy base. The whole idea is being able to limit the capacity that is assigned to a resource, and have a set of server pools to service those resources. BTW. It is possible to assign a server to multiple server pools.

This kind of management enables you to dynamically assign capacity to meet your policies and priorities, as well as to allocate (physical) resources by importance. Your critical applications will always have a minimum of resources at its disposal. It also gives you the opportunity to limit applications to use a set of servers.

How does it work? You just assign the minimum and maximum number of nodes to a resource, as well as the priority and Oracle Clusterware will do the rest.

The servers will get a number of attributes, like a name (basically the node name), the pools to which it belongs and the state it is in with some details.

How do Server Pools work? They are sets of servers, that service resources. What they will try to do is to spread the workload over the available servers in the pool. It is possible to limit a database to run in a specific server pool.

The top-level server pool is an exclusive server pool, which means that any given server can only be a part of one top-level server pool. It is impossible to be a member of multiple top-level server pools. This way, the grid or cluster is logically divided.

All servers that join the cluster/grid are automatically assigned to the Free Pool initially. This Free Pool is a server pool that is automatically created at installation. From here, servers can be assigned to other server pools. Another pool, the Generic Pool is also automatically generated, and all servers will be part of this pool too, in order to ensure compatibility with prior releases. Any pre-11gR2 database will need to run in this generic server pool.

The server pool is given an minimum and maximum number of nodes. As soon as the number of nodes decreases below the minimum value, Clusterware will automatically take necessary action and take servers from other pools to increase the number of servers to meet the required minimum number.

If this poses a problem for other server pools, decisions are made upon the importance of the pools. When importance is equal, it is only possible to take servers, if the number of servers is higher than the minimum value.

All of the above can be done completely dynamically, however, manual manipulation is still possible. You might think: "Where is my control?", but if you set it up carefully, it could save you a bundle. For example, when you configure Grid Plug and Play (another one of those new features), it is possible to have a database bring up a new instance in the server pool and automatically create new log threads, undo tablespaces, etc. Isn't that way cool?

Oracle 11g Release 2 - ADVM (ASM Dynamic Volume Manager)

One of the new features in Oracle 11g Release 2 is Automatic Storage Management Cluster File System. This file system provides concurrent access from multiple (clustered) nodes.

ACFS is provided through the Automatic Storage Management Dynamic Volume Manager. In order to have a file system, one needs a volume (be it physical or logical) to put it on. Oracle has developed a Dynamic Volume Manager (ADVM) that can provide these volumes from ASM Diskgroups. You can use ADVM to create volumes that are replicated within the ASM diskgroup mechanism – by means of normal or high redundancy. Multiple volumes can reside on one diskgroup. The ADVM provides a device driver interface to the ACFS client.

Due to the mirroring features of ASM, it is possible to have a mirrored file system (even across a stretched cluster, when your storage infrastructure allows for such an architecture) that will leverage the features of ASM to provide consistency over hardware failure and ASM instance crashes.

You can use the file systems provided through ADVM not only for database files, but also for regular files such as executables and log files. During creation of the file system you have the choice to designate the file system for Oracle Home functionality. By choosing this option, the file system is registered in the Cluster Registry as a managed resource, giving Oracle the opportunity to layer dependencies on this file system for the use of Oracle Homes.

Because of the fact that Oracle ADVM Volumes are technically spoken ASM files located on ASM Disk groups, and the fact that the Dynamic Volumes do not use the traditional device partitioning, it enables Oracle to extend some of the ASM features to the ASM Clustered File Systems, which are created inside these ADVM Volumes, such as dynamic resizing or dynamically adding volumes. This makes ADVM and ACFS a far more flexible solution than traditional physical devices.

Important Note: Oracle ADVM supports all storage solutions supported for Oracle ASM with the exception of NFS and Exadata storage. That will have to wait for some time still…


The above picture shows the User Interface, provided through the ASM Configuration Assistant (asmca), to create file systems inside ASM using the ADVM.

Oracle ACFS advanced topics

While everybody is exploring new features of the latest release of Oracle 11g, we're all very excited about all the new possibilities.
One of those new features is Oracle ACFS (ASM Cluster File System), wich provides an Oracle Home shared file system or a general purpose clustered file system on ASM storage. These are very nice features that will be used by many customers, in time to come. My first experiences are quite good. However, it is of great importance to know the limitations of those new features as well. Oracle has - on the topic of ACFS - thought about this very carefully, by adding an Appendix to the Storage Administrator's Guide, called ACFS Advanced Topics. Amongst these, a number of limitations are discussed. Well, one can argue whether these are actual limitations, that really limit you in operating ACFS, but it is good to know about these when planning for you Grid Infrastructure.

Oracle ACFS Disk Space Usage
Some Facts and Figures: Oracle ACFS supports:

• 64 million files in a file system,
• 63 snapshots,
• 64 mounts on 32 bit systems,
• 256 mounts on 64 bit systems.

Oracle allocates metadata for each node that mounts the file system, comprising approximately 64 – 128 MB per node. Also, some space is reserved for local bitmaps to reduce contention on the global bitmap in order to find free space. This can add up to 128MB per node. All of this space is reported as “in use” when querying the file system for disk space.

Oracle ACFS Error Handling
CAUTION: It is very important to unmount any file system that is using the Oracle ASM Dynamic Volume Manager (ADVM), before shutting down an Oracle ASM Instance. Failing to do so can result in I/O failures. One can never guarantee that the ASM instance(s) will never fail, but in case it does, volumes must be closed and opened again to be able to access them. Dismount the file systems that were mounted during failure and after the instance is restarted, mount the corresponding disk group with the volume enabled, after which you can mount the file system again.

Therefore, if any file systems are currently mounted on Oracle ADVM volume files, the SHUTDOWN ABORT command should not be used to terminate the Oracle ASM instance without first dismounting those file systems.

When a metadata write fails, regardless whether it be an ASM or storage failure, ACFS isolates the errors to a single file system and puts it in an off-line state. The only thing you can do with it is dismount. Other nodes (when available) recover the transaction, if it can write to that particular storage. After this, the file system can be mounted again.

When the file system being put offline is in use by processes or users, it might not be possible to unmount it. The administrator has to identify the processes that are claiming the file system and stop them before unmounting.

Oracle ACFS and NFS
Because block device major numbers of ADVM devices can be different after a reboot, the -fsid=# should be used when exporting NFS file systems. The option forces the clients to use this number (#) when communicating with this file system. Obviously, the number should be unique across the cluster. It replaces the number derived from the major/minor number of the device itself, since it can change with reboots.

Limits of Oracle ADVM
Some Facts: The default configuration for an Oracle ADVM volume is four columns of 64 MB extents in length and a 128 KB stripe width. Oracle ADVM writes data as 128 KB stripe chunks in round robin fashion to each column and fills a stripe set of four 64 MB extents with 2000 stripe chunks before moving to a second stripe set of four 64 MB extents for volumes greater than 256 megabytes. Note that setting the number of columns on an Oracle ADVM dynamic volume to 1 effectively turns off striping for the Oracle ADVM volume (are you still with me?;-)

On Linux platforms Oracle ASM Dynamic Volume Manager (Oracle ADVM) volume devices are created as block devices regardless of the configuration of the underlying storage in the Oracle ASM disk group. Do not use raw (8) to map Oracle ADVM volume block devices into raw volume devices.

Oracle ACFS and Oracle Restart
Oracle Restart does not support root-based Oracle ACFS resources for this release. As a result, the following operations are not automatically performed:

• Loading Oracle ACFS drivers
• Mounting Oracle ACFS file systems listed in the Oracle ACFS mount registry
• Mounting resource-based Oracle ACFS database home file systems

The Oracle ACFS resources associated with these actions are not created for Oracle Restart configurations.
In short, the following resources are only supported for the Oracle Grid Infrastructure Cluster configurations, and are not supported for Oracle Restart Configurations:

• The Oracle ACFS drivers resource (ora.drivers.acfs),
• The Oracle ACFS registry resource (ora.registry.acfs)
• The Oracle ACFS individual file system resource ora.diskgroup.volume.acfs)

Source: Oracle Database Storage Administrator's Guide, 11g Release 2, Appendix B

ASM Cluster File System: Available Storage From the ASM Perspective

I was checking my system this morning, having it booted for the first time after the installation of Oracle 11g Release 2 Grid Infrastructure.
I opened up SQL*plus for a connection to ASM, to see what the disk space usage was.
I found the following information:

The first section of the screen shows the total amount of diskspace and available diskspace from an ASM perspective, the lower part of the screen shows the file system usage.
Here you can see that the file system, even though it has practically all space available, has claimed all of its space in ASM. Logically, you might think, but since the two are so tightly integrated, might surprise a bit.
The principle is the same compared to 10g ASM with Oracle Datafiles. The datafile itself might not been occupied fully, still all of the space that the datafile is configured with needs to be allocated in ASM. When you want to see what your actual usage is on an ASM disk and how much you can grow, you would need to calculate the free space in your file system and add the FREE_MB column from within ASM to it to determine the exact free space on your ASM Disk(s).