Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Links Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS SQL
Oracle
DB2
MS Access
MySQL
» RESOURCES
Database Tools
SQL Scripts & Samples
Links
» Database Forum
» Sitemap
Free Newsletters:
DatabaseDaily  
News Via RSS Feed


follow us on Twitter
Database Journal |DBA Support |SQLCourse |SQLCourse2
 

Featured Database Articles

Oracle

Posted May 27, 2010

Leveraging ACFS Snapshots and Cluster Infrastructure in Oracle Database 11gR2

By Jim Czuprynski

Synopsis. Oracle Database 11g Release 2 (11gR2) offers several new Automatic Storage Management (ASM) features for managing both Oracle database files as well as files stored within its new ASM Clustered File System (ACFS). This article – the last in this series – illustrates how to upgrade an Oracle 11gR1 database quickly and efficiently to Oracle 11gR2 and place its database files within ASM-managed storage, and closes with a brief demonstrate of how ACFS snapshots can be used to recover quickly from human error when crucial components of an Oracle home are damaged or destroyed.

The prior article in this series explained how to:

  • Upgrade an existing Oracle Database 11gR1 to Oracle 11gR2 using an ACFS-resident Oracle Home
  • Use new ASMCMD command options to configure, control, and manage an ASM instance and monitor ASM disk performance

As I conclude this article series on Oracle 11gR2’s new ASM and ACFS features, I’ll show how to:

  • Start and stop an Oracle Database 11gR2 using SRVCTL commands, including setting up Database Restart features
  • Use ACFS File System Snapshots (FSS) to recover from human error

Controlling Oracle Database Instances: Oracle Restart

If you’re already familiar with using the SRVCTL utility to control any aspect of a Real Applications Cluster environment when managing a RAC database in earlier Oracle releases, you’ll be happy to note that the latest release of Oracle expands this capability, making it easier than ever to control an Oracle 11gR2 single-instance database with a few simple commands. The good news here is that the old mechanisms that DBAs used to control and guarantee that a single-instance database would restart whenever a server restarted – the venerable DBSTART scripts in non-Windows NTFS environments, or setting appropriate parameters for the Windows database services in Windows NTFS environments – are finally relegated to the scrap heap.

Oracle 11gR2 replaces DBSTART scripting with the much more flexible srvctl add database command syntax. For example, it’s now quite simple to configure my newly-migrated Oracle 11gR2 database (orcl) for automatic startup and shutdown whenever Oracle 11gR2 High-Availability Services (HAS) starts up or terminates, respectively. The full srvctl command string to implement this is shown in Listing 4.1.

To confirm that the orcl database is now part of the Oracle Restart environment, I’ll issue a variant of the srvctl config command to verify the parameters that have been set:

$> srvctl config database -d orcl
Database unique name: orcl
Database name: 
Oracle home: /u01/app/oracle/acfsmounts/acfs_db1
Oracle user: oracle
Spfile: /u01/app/oracle/acfsmounts/acfs_db1/dbs/spfileorcl.ora
Domain: 
Start options: open
Stop options: immediate
Database role: PRIMARY
Management policy: AUTOMATIC
Disk Groups:
Services:

Notice that the Oracle Restart configuration specifies that the database should always be started normally (i.e. finish in OPEN mode) and that when a database shutdown is requested, a SHUTDOWN IMMEDIATE command will be issued. Also, notice that there are no dependencies between this database and its corresponding ASM disk groups … yet. I will remedy that in short order by issuing the appropriate format of the srvctl modify database command to include dependencies for the +DATA and +FRA disk groups:

$> srvctl modify database -d orcl -a "DATA,FRA"
 
$> srvctl config database -d orcl
Database unique name: orcl
Database name: 
Oracle home: /u01/app/oracle/acfsmounts/acfs_db1
Oracle user: oracle
Spfile: /u01/app/oracle/acfsmounts/acfs_db1/dbs/spfileorcl.ora
Domain: 
Start options: open
Stop options: immediate
Database role: PRIMARY
Management policy: AUTOMATIC
Disk Groups: DATA,FRA
Services: 

Using SRVCTL to Start and Stop the Database. Now that Oracle Restart parameters are configured, it’s much simpler to start the database using the same srvctl start database syntax that I’d use if this were a Real Application Clusters (RAC) database:

$> srvctl start database -d orcl

Similarly, I’ll now use the same srvctl stop database syntax that I’d use if this were a Real Application Clusters (RAC) database to shut it down. Note that I can still override the default database shutdown mode of IMMEDIATE by adding the desired shutdown mode to the command string’s invocation:

$> srvctl stop database -d orcl -o abort

And here’s a snippet of the database’s alert log to show the result of that command’s execution:

Sat Apr 24 16:38:10 2010
Shutting down instance (abort)
License high water mark = 11
USER (ospid: 15699): terminating the instance
Instance terminated by USER, pid = 15699
Sat Apr 24 16:38:11 2010
Instance shutdown complete

ACFS File System Snapshots

As I’ve demonstrated in an earlier article in this series, Oracle 11gR2’s new ASM Clustered File System (ACFS) extends the intrinsic benefits of Automatic Storage Management for controlling non-database files like an Oracle Home. One feature that’s becoming more prevalent in higher-end storage systems is the ability to create a snapshot of a file system, a read-only copy of an existing set of files in a file system that’s frozen in time.

A robust file system typically leverages a well-known concept called copy on write (sometimes called more correctly “copy on first write”) to track changes to the files within the file system after the snapshot has been taken. Whenever a file has been modified, the dirtied object is first copied to a secondary volume for future synchronization with the existing snapshot. Retaining just the “delta” version of the file(s) that have been added, changed, or removed from the file system means only one initial snapshot needs to be taken, and the “deltas” remain relatively small in size.

Creating an ACFS Snapshot. To confirm whether any snapshots already exist for this Oracle database home, I’ll issue the the acfsutil info fs command:

 [root@11gR2Base ~]# /sbin/acfsutil info fs
/u01/app/oracle/acfsmounts/acfs_db1
    ACFS Version: 11.2.0.1.0.0
    flags:        MountPoint,Available
    mount time:   Sun May  9 16:51:36 2010
    volumes:      1
    total size:   6442450944
    total free:   1058045952
    primary volume: /dev/asm/acfs_db1-152
        label:                 
        flags:                 Primary,Available,ADVM
        on-disk version:       39.0
        allocation unit:       4096
        major, minor:          252, 77825
        size:                  6442450944
        free:                  1058045952
        ADVM diskgroup         ACFS
        ADVM resize increment: 268435456
        ADVM redundancy:       unprotected
        ADVM stripe columns:   4
        ADVM stripe width:     131072
    number of snapshots:  0
    snapshot space usage: 0

This confirms there are no existing snapshots for this ACFS file system, so I’ll create one via the acfsutil snap create command:

[root@11gR2Base ~]# /sbin/acfsutil snap create orahome0 /u01/app/oracle/acfsmounts/acfs_db1
acfsutil snap create: Snapshot operation is complete.

After only a few seconds the snapshot creation is completed, I’ll confirm that by once again issuing the acfsutil info fs command against that same file system:

[root@11gR2Base ~]# /sbin/acfsutil info fs
/u01/app/oracle/acfsmounts/acfs_db1
    ACFS Version: 11.2.0.1.0.0
    flags:        MountPoint,Available
    mount time:   Sun May  9 16:51:36 2010
    volumes:      1
    total size:   6442450944
    total free:   991715328
    primary volume: /dev/asm/acfs_db1-152
        label:                 
        flags:                 Primary,Available,ADVM
        on-disk version:       39.0
        allocation unit:       4096
        major, minor:          252, 77825
        size:                  6442450944
        free:                  991715328
        ADVM diskgroup         ACFS
        ADVM resize increment: 268435456
        ADVM redundancy:       unprotected
        ADVM stripe columns:   4
        ADVM stripe width:     131072
    number of snapshots:  1
    snapshot space usage: 22056960

Since an ACFS file system is managed as part of an ASM instance, Oracle 11gR2 also provides the V$ASM_ACFSSNAPSHOTS view to review the status of all existing ACFS file system snapshots. A sample query against this view and its corresponding output are shown in Listing 4.2.

Using an ACFS Snapshot For Recovery Purposes. I’ll demonstrate one of the most powerful features of ACFS Snapshots to recover from what would normally be a serious infrastructure failure: the loss of several crucial files or directories within an Oracle database home. (While in theory this should never happen – what Oracle DBA in her right mind would ever do such a thing, after all! – it tends to be one of the more maddening problems to resolve just because the loss of any directory or file within an Oracle Home is so inconceivable.) So let’s imagine a scenario in which my senior DBA suddenly realizes she cannot run SQL*Plus on the database server to complete some simple recompilations; in fact, she cannot connect to the database at all:

[oracle@11gR2Base acfs_db1]$ sqlplus / as sysdba
Error 6 initializing SQL*Plus
SP2-0667: Message file sp1.msb not found
SP2-0750: You may need to set ORACLE_HOME to your Oracle software directory
[oracle@11gR2Base snaps]$ cd /u01/app/oracle/acfsmounts/acfs_db1/.ACFS/snaps/orahome0/

After several moments of utter frustration, she finally realizes what’s happened: An Oracle DBA trainee had accidentally copied TNSNAMES.ORA (the network configuration file) to a wrong directory in the Oracle database home and in his zeal to clean up after his mistake, removed the entire directory containing the SQL*Plus software from the database server. As she counts to ten to get her blood pressure back under control, she suddenly remembers that all is not lost, for she had created a snapshot for this Oracle 11gR2 database home just a few days ago. The directory that contains the ACFS snapshot for this database home is actually found in a “hidden” directory beneath the original Oracle database home that’s prefaced with .ACFS:

[oracle@11gR2Base orahome0]$ ls -la /u01/app/oracle/acfsmounts/acfs_db1/.ACFS/snaps/orahome0/sqlplus/
 
total 52
drwxr-xr-x  7 oracle dba       4096 Mar 20 06:37 .
drwxrwxr-x 74 oracle oinstall  8192 May  9 17:04 ..
drwxr-xr-x  3 oracle dba       4096 Mar 20 06:28 admin
drwxr-xr-x  2 oracle dba       4096 Mar 20 06:37 bin
drwxr-xr-x  2 oracle dba       4096 Mar 20 06:28 doc
drwxr-xr-x  2 oracle dba       4096 Mar 20 06:37 lib
drwxr-xr-x  2 oracle dba      12288 Mar 20 06:34 mesg

It’s now a simple matter to resolve the problem by copying the appropriate files from the ACFS snapshot directory:

[oracle@11gR2Base acfs_db1]$ cp -R /u01/app/oracle/acfsmounts/acfs_db1/.ACFS/snaps/orahome0/sqlplus $ORAHOME/

Now our intrepid DBA can breathe a sigh of relief, as the full functionality of the database server’s SQL*Plus environment has been successfully restored:

[oracle@11gR2Base acfs_db1]$ sqlplus / as sysdba
 
SQL*Plus: Release 11.2.0.1.0 Production on Tue May 11 08:40:25 2010
 
Copyright (c) 1982, 2009, Oracle.  All rights reserved.
 
Connected to an idle instance.

Removing an Existing ACFS Snapshot. Should she decide to remove the ACFS snapshot from the ACFS file system, all that our intrepid senior DBA needs to do is to issue the acfsutil snap delete command:

[oracle@11gR2Base ~]$ /sbin/acfsutil snap delete orahome0 /u01/app/oracle/acfsmounts/acfs_db1
acfsutil snap delete: Snapshot operation is complete.
 
[oracle@11gR2Base ~]$ /sbin/acfsutil info fs
/u01/app/oracle/acfsmounts/acfs_db1
    ACFS Version: 11.2.0.1.0.0
    flags:        MountPoint,Available
    mount time:   Mon May 10 19:30:34 2010
    volumes:      1[oracle@11gR2Base ~]$ /sbin/acfsutil snap delete orahome0 /u01/app/oracle/acfsmounts/acfs_db1
acfsutil snap delete: Snapshot operation is complete.
[oracle@11gR2Base ~]$ /sbin/acfsutil info fs
/u01/app/oracle/acfsmounts/acfs_db1
    ACFS Version: 11.2.0.1.0.0
    flags:        MountPoint,Available
    mount time:   Mon May 10 19:30:34 2010
    volumes:      1
    total size:   6442450944
    total free:   1017229312
    primary volume: /dev/asm/acfs_db1-152
        label:                 
        flags:                 Primary,Available,ADVM
        on-disk version:       39.0
        allocation unit:       4096
        major, minor:          252, 77825
        size:                  6442450944
        free:                  1017229312
        ADVM diskgroup         ACFS
        ADVM resize increment: 268435456
        ADVM redundancy:       unprotected
        ADVM stripe columns:   4
        ADVM stripe width:     131072
    number of snapshots:  0
    snapshot space usage: 0

Conclusions

Throughout this article series, I’ve endeavored to explore the newest features of Oracle 11g Release 2, especially those that make the most sense to understand and implement during initial database home software installation and configuration. Once you’ve read through the articles in this series, you should have an excellent idea of how Oracle 11gR2 continues the trend of earlier releases to focus on the grid computing model first introduced in Oracle 10gR1, and that includes the centralization of common command sets and a deeper implementation of the continuously-improving ASM file system.

References and Additional Reading

Before you proceed to experiment with any of these new features, I strongly suggest that you first look over the corresponding detailed Oracle documentation before trying them out for the first time. I’ve drawn upon the following Oracle Database 11g Release 2 documents for this article’s technical details:

E10881-02 Oracle Database 11gR2 New Features

E10592-03 Oracle Database 11gR2 SQL Language Reference

E10700-01 Oracle Database 11gR2 DataGuard Concepts and Administration

E10595-05 Oracle Database 11gR2 Administrator’s Guide

E10713-03 Oracle Database 11gR2 Concepts

E10820-02 Oracle Database 11gR2 Reference

E10500-02 Oracle Database 11gR2 Storage Administrator’s Guide

E10837-02 Oracle Database 11gR2 VLDB and Partitioning Guide

Previous

» See All Articles by Columnist Jim Czuprynski



Oracle Archives

Comment and Contribute

 


(Maximum characters: 1200). You have characters left.

 

 




Latest Forum Threads
Oracle Forum
Topic By Replies Updated
Oracle Data Mining: Classification jan.hasller 0 July 5th, 07:19 AM
Find duplicates - Unique IDs Lava 5 July 2nd, 08:30 AM
no matching unique or primary key rcanter 1 April 25th, 12:32 PM
Update values of one table based on condition of values in other table using Trigger Gladiator 3 February 29th, 06:01 PM