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 Apr 22, 2010

Using Oracle Database 11gR2's New ASM Features During ASM Migration

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 illustrates how to upgrade an Oracle 11gR1 database quickly and efficiently to an Oracle 11gR2 database home and then migrate all of its database files so they’re resident within ASM-managed storage.

The Oracle Database 11gR2: Building an ASM Clustered File System (ACFS) explained how to:

  • Install and configure the new Oracle Database 11gR2 ASM Clustered File System (ACFS)
  • Create a new Oracle 11gR2 database home within an ACFS file system
  • Use several new features of the ASM command-line utility, ASMCMD, to manage all facets of an ASM instance, ASM disk groups, and ASM disks

This article will illustrate how to:

  • Upgrade an existing Oracle database 11gR1 to Oracle 11gR2 using an ACFS-resident Oracle Home
  • Use new ASMCMD command options to selectively monitor ASM disk performance and ASM file system content

Upgrading an Existing Oracle Database 11gR1 to Oracle 11gR2

Now that I have a new Oracle 11gR2 database home installed and configured, I’ll finally turn my attention to running an Oracle 11gR2 single-instance database within that new Oracle home. To accomplish this, I’ve selected one of my original “test bed” Oracle 11gR1 databases as the target of a manual upgrade to the latest release of Oracle (11.2.0.1.0).

Preparing to Upgrade Manually. To make this scenario a bit more interesting, I’ll also be upgrading my “test bed” database from a 32-bit Oracle Enterprise Linux (OEL) environment to a corresponding 64-bit OEL environment:

  • First, I copied over all of the crucial database files – control files, datafiles, tempfiles, and online redo logs – from my 32-bit environment to my 64-bit environment.
  • Next, I built a brand-new SPFILE from the original 32-bit database’s initialization parameters by first creating a PFILE for that database, copying it over to the target 64-bit environment, and then issuing the CREATE SPFILE FROM PFILE=’/home/oracle/initorcl.ora’; command within SQL*Plus. Remember, it’s necessary to have the database instance completely shut down before an SPFILE can be recreated.
  • Next, I started the instance in MOUNT mode – this is most important! – while pointing to the new Oracle 11gR2 database home that’s resident on ACFS storage. I made sure that the control files were recognized during the startup; once I verified this, I shut the database down once again.
  • I then opened the database in STARTUP UPGRADE mode to prepare it for the manual upgrade process, and then I executed procedure script utlirp.sql from directory $ORACLE_HOME/rdbms/admin directory to invalidate all PL/SQL packages in the database. This step insures that all PL/SQL packages will be recompiled during the 32-bit to 64-bit conversion process.
  • Finally, I shut down the database once again, started it up in STARTUP UPGRADE mode, spooled my output to a log file, and then ran script utlu112i.sql from the $ORACLE_HOME/rdbms/admin directory to find out what adjustments (if any) were required before I could begin the upgrade. The results from executing this script are shown in Listing 3.1; note that no abnormal results were reported, so I knew it was now safe to proceed with an attempt at a manual upgrade.

Performing the Manual Upgrade. Now that I’m ready to proceed with the manual upgrade, I spooled my output to a new log file and then ran script catupgrd.sql from the $ORACLE_HOME/rdbms/admin directory; after about 30 minutes, it had successfully completed all of its conversion tasks. I’ve captured just the very beginning and very end of the spooled output – there were over 110,000 lines! – and it’s displayed in Listing 3.2. Once the upgrade process was complete, I ran procedure utlrp.sql from the $ORACLE_HOME/rdbms/admin directory to recompile any PL/SQL objects that were still invalid, and my database was ready for action.

Converting to ASM File System Storage

I also have to tackle conversion of my newly-upgraded Oracle 11gR2 database from its “native” Linux-based EXT3 file system to using the Oracle 11gR2 ASM file system instead. Thankfully, this is easier than ever because 11gR2 has added some excellent new functionality into ASM, as I’ll demonstrate shortly; but first, I’ll create another new ASM disk group, +FRA, that will become my new Oracle 11gR2 ASM-based Fast Recovery Area. I’ll use the asmca utility to complete this prerequisite task:

Oracle Database: Listing the Current ASM Disk Groups
Figure 3.1: Listing the Current ASM Disk Groups

As Figure 3.1 confirms, there’s no current FRA ASM disk group, so I’ll create one from the available candidate mount points shown in Figure 3.2 below.

Oracle Database Adding the New +FRA ASM Disk Group
Figure 3.2: Adding the New +FRA ASM Disk Group

Note that I’ve decided to use external redundancy for this new disk group; in a real-world scenario, of course, I’d like to be sure that these mount points are protected from data loss using some sort of RAID configuration. Figure 3.3 shows the end result of my configuration efforts:

Oracle Database Confirmation of ASM Disk Groups +FRA Creation
Figure 3.3: Confirmation of ASM Disk Groups +FRA Creation

Another way to confirm the successful creation of this disk group is through the enhanced command set that the new 11gR2 ASMCMD utility provides. Listing 3.3 shows a sample of the output produced when I invoked the lsdg command within an ASMCMD session.

Now that I’ve got a new ASM disk group as a target for my ASM-based Fast Recovery Area, I’m ready to migrate my database to ASM storage using a quite simple five-step approach:

1.)   Migrating the SPFILE. I’ll first convert the database’s SPFILE so that my database will use ASM storage for any new database files that need to be created during the migration (which, of course, will be all of my database’s files). To do this, I’ll create a PFILE from the existing SPFILE, and then add a few parameters to it so that Oracle Managed Files (OMF) will point at the appropriate ASM disk groups:

*.db_create_file_dest = "+DATA"
*.db_create_online_log_dest_1= "+DATA"
*.db_recovery_file_dest = "+FRA"

Once these changes are made, I’ll build a new SPFILE from that PFILE within ASM storage in the new Oracle 11gR2 database home:

SQL> CREATE SPFILE FROM PFILE=’/home/oracle/initorcl.toasm’;

To confirm everything is working properly, I’ll start up the database instance in NOMOUNT mode. Here’s the result of that STARTUP NOMOUNT command from the perspective of the database’s alert log:

Thu Apr 15 16:48:42 2010
Starting ORACLE instance (normal)
LICENSE_MAX_SESSION = 0
LICENSE_SESSIONS_WARNING = 0
Picked latch-free SCN scheme 3
Using LOG_ARCHIVE_DEST_1 parameter default value as USE_DB_RECOVERY_FILE_DEST
Autotune of undo retention is turned on. 
IMODE=BR
ILAT =27
LICENSE_MAX_USERS = 0
SYS auditing is disabled
NOTE: Volume support  enabled
Starting up:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options.
Using parameter settings in client-side pfile /home/oracle/initorcl.toasm on machine 11gR2Base
System parameters with non-default values:
  processes                = 150
  sga_max_size             = 776M
  shared_pool_size         = 128M
  large_pool_size          = 4M
  java_pool_size           = 4M
  streams_pool_size        = 4M
  sga_target               = 752M
  control_files            = "/u01/app/oracle/oradata/orcl/control01.ctl"
  control_files            = "/u01/app/oracle/oradata/orcl/control02.ctl"
  control_files            = "/u01/app/oracle/oradata/orcl/control03.ctl"
  db_block_size            = 8192
  db_cache_size            = 376M
  compatible               = "11.2.0.0.0"
  db_create_file_dest      = "+DATA"
  db_create_online_log_dest_1= "+DATA"
  db_recovery_file_dest    = "+FRA"
. . .

Note that the database instance is still pointed at the original control files on non-ASM storage; that’s the next thing I’ll tackle during this migration process.

2.)   Migrating the Control Files. Since the instance is already started, I’ll issue the following SQL*Plus commands to prepare for migrating multiplexed versions of the control files, one each to the +DATA and +FRA disk groups:

ALTER SYSTEM SET CONTROL_FILES=’+DATA’,’+FRA’ SCOPE=SPFILE SID=’*’;

This forces the SPFILE to register the new locations for the control files. Next I’ll perform a SHUTDOWN ABORT, restart the instance, and then confirm the new parameters are now in place by checking the alert log after the instance restart:

. . .
Using parameter settings in server-side spfile 
/u01/app/oracle/acfsmounts/acfs_db1/dbs/spfileorcl.ora
System parameters with non-default values:
  processes                = 150
  sga_max_size             = 776M
  shared_pool_size         = 128M
  large_pool_size          = 4M
  java_pool_size           = 4M
  streams_pool_size        = 4M
  sga_target               = 752M
  control_files            = "+DATA"
  control_files            = "+FRA"
  db_block_size            = 8192
  db_cache_size            = 376M
  compatible               = "11.2.0.0.0"
  db_create_file_dest      = "+DATA"
  db_create_online_log_dest_1= "+DATA"
  db_recovery_file_dest    = "/u01/app/flash_recovery_area"
  db_recovery_file_dest_size= 8G
. . .

With the database still in NOMOUNT mode, I’ll now restore a copy of the current control file to the two multiplexed locations via a few simple RMAN commands:

[oracle@11gR2Base ~]$ rman target /

Recovery Manager: Release 11.2.0.1.0 - Production on Thu Apr 15 17:20:33 2010
Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.
connected to target database: ORCL (not mounted)

RMAN> RESTORE CONTROLFILE FROM '/u01/app/oracle/oradata/orcl/control01.ctl';

Starting restore at 2010-04-15 17:20:59
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=24 device type=DISK

channel ORA_DISK_1: copied control file copy
output file name=+DATA/orcl/controlfile/current.257.716404861
output file name=+FRA/orcl/controlfile/current.256.716404861
Finished restore at 2010-04-15 17:21:07

Excellent! Once I’ve issued the ALTER DATABASE MOUNT; command from SQL*Plus to mount these two newly-copied control files, it’s time to move on to the next migration step.



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