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