Much faster performance with ASM – a real world example

We have a  Peoplesoft database that  manages HR for ~80,000 staff.  Oracle 10.2.03.  Peoplesoft Tools 8.49 PS HR 9 HPUX 11.31 Itanium

There has been long running problems caused by I/O bottlenecks writing down to Hitachi XP SAN disks. We were using filesystem storage and disk_asynch_io was set to false

The root cause of our problem was that the Time and Labour batches contend when they need to acquire exclusive access to write dirty blocks to data files during truncate operations. 

David Kurtz has written about a specific Peoplesoft feature around truncate http://www.go-faster.co.uk/docs.htm#local_write_wait

 We migrated to using ASM disk and achieved significant benefits.

 The testing stages were

1)       Prepare a 2 hour Time and labour batch run which would generate significant throughput

2)       Capture a baseline at 10.2.0.3

3)       Upgrade to 10.2.0.4 and retest

4)       Upgrade to 10.2.0.4 and migrate to ASM (11.1.0.7) and retest

5)       Upgrade to 11.1.0.7 and migrate to ASM (11.1.0.7) and retest

 

Test 3 did not show any difference at all. We were still blocked on disk, which was expected. We probably would have remained at 10.2.0.3 but gone to ASM but there are problems with seeing ASM disk when using 10.2.0.3 (Metalink Note 434500, ORA-15059) so we had the option of applying a fix to get ASM working with 10.2.0.3 or upgrading to 10.2.0.4. We decided that the benefits of getting onto a terminal release and suggestions from Oracle that small changes in the truncate process had been made between 10.2.0.3 and 10.2.0.4 out-weighed the overhead of any application testing that we needed to do.

Test 4 gave us big performance benefits. Test 5 was not as clear cut but we knew some code had to be optimised and we did not have the luxury of time to address the issues. Remaining on 10g minimised the application testing, certainly compared to the testing require to upgrade to 11g.

The benefits can be seen quite clearly in the graph below

This shows a 2 hour period where a constant throughput of T&L jobs were being processed. In parallel a constant online load was being simulated

The black line shows throughput and elapsed time. The run consists of a number of jobs which take 2 hours to complete and batch time is around the 450 - 600 second mark

The same processing is then run with 10.2.0.4 database and ASM. disk_aysnch_io is set to FALSE. The jobs complete 40 minutes faster and elapsed time is around the 300-400 second mark but showing spikes and inconsistent performance

Finally the orange line shows 10.2.0.4 database and ASM. disk_aysnch_io =TRUE. The processing time has now been reduced to just over an hour and the elapsed time is significantly better at an average of 250 seconds and with a very consistent profile.

As with all things in life we have now moved onto the next bottleneck which is the wait flashback buf free caused because we have enabled flashback logging and as the redo log buffer fills up we wait for flashback to be written out. However this is a miniscule issue compared to the original problems.  We have taken flashback off some tablespaces primarily used for PS temporary tables (aka working storage tables) although that does give us an issue when when rebuilding the primary database after a DataGuard failover using flashback logs

I hope this real world example is of use

Managing control files in ASM

Just a couple of notes on how to create multiple ASM control files and moving control files toa different diskgroup.

Assuming we already have a ASM control file in the DATA diskgroup and we wish to add one to the FRA diskgroup

 SQL>show parameter control_files
+DATA/SID/controlfile/current.730.698514673
alter system set control_files='+DATA','+FRA' scope=spfile;
shutdown immediate;
startup nomount;
exit
rman target
/restore controlfile from '+DATAHR/MHRPRD1A/CONTROLFILE/current.730.698514673';
alter database mount;
alter database open;
SQL>show parameter control_files
+DATA/SID/controlfile/current.730.698514673, +FRAHR/SID/controlfile/current.766.698514673

 

                                                                           

Here is an example of how to migrate from one ASM diskgroup to another

Add the new disk discovery path in (if needed), create a diskgroup and add disks

alter system set asm_diskstring='/dev/oracle','/dev/oracle_hr/r*';

create diskgroup DATANEW  external redundancy disk '/dev/oracle_hr/rdisk1','/dev/oracle_hr/rdisk2';

SQL>show parameter control_files

control_files                        string      +DATA10/SID/controlfile/c urrent.796.695384211
alter system set CONTROL_FILES = '+DATANEW' scope=spfile

shutdown
rman target /
startup nomount
restore controlfile  from  '/+DATA/SID/controlfile/current.796.695384211';
alter database mount;
alter database open;

Moving from ASM storage back to filesystem

Whilst it is not very common to move from ASM to filesystem I needed to prepare a regression plan for the migration of a 450 datafile database in the event of problems during the migration or shortly afterwards.

The example below is from a small database called TEST and I used filestore in /app/oracle whereas normally I would have a /oradata/SID/ filesystem

The details below should be enough to get someone through the migration and the sql scripts can be modified to be more generic if necessary but it is not too hard to use them as they are now. 

List the datafiles and we will add a second datafile to the users tablespace to provide an example of an issue that can occur.

SYS@TEST SQL>select file_name from dba_data_files;  +DATA/test/datafile/undotbs1.301.697649965
+DATA/test/datafile/sysaux.300.697649963
+DATA/test/datafile/system.299.697649963
+DATA/test/datafile/users.302.697649965
+DATA/test/datafile/users.309.697650601

 mkdir /app/oracle/TEST

Some tablespaces may have multiple datafiles which would end up with the same name using the code above. However as a quick circumvention I manually identified those tablespaces using this sql and then edited the new data file names

select tablespace_name,count(file_name) from dba_data_files group by tablespace_name ORDER BY 2 

In my example it only applied to the USERS tablespace and I edited the second ASM datafile to be mapped to users02.dbf not users01.dbf as the script will output. This action would have to be repeated for all tablespaces with multiple datafiles

sqlplus / as sysdba

set lines 120 pages 0

select 'copy datafile '||file_id||' to '||'''/app/oracle/TEST/'||substr(file_name,21,instr(file_name,'.')-21)||'_01.dbf'''||';' from dba_data_files order by 1;

copy datafile 1 to '/app/oracle/TEST/system_01.dbf';

copy datafile 2 to '/app/oracle/TEST/sysaux_01.dbf';

copy datafile 3 to '/app/oracle/TEST/undotbs1_01.dbf';

copy datafile 4 to '/app/oracle/TEST/users_01.dbf';

copy datafile 5 to '/app/oracle/TEST/users_02.dbf';

shutdown immediate;

startup mount;

exit

rman target /

copy datafile 1 to '/app/oracle/TEST/system_01.dbf';

copy datafile 2 to '/app/oracle/TEST/sysaux_01.dbf';

copy datafile 3 to '/app/oracle/TEST/undotbs1_01.dbf';

copy datafile 4 to '/app/oracle/TEST/users_01.dbf';

copy datafile 5 to '/app/oracle/TEST/users_02.dbf';

exit

sysdba

select 'alter database rename file '''||file_name||''' to '||'''/app/oracle/TEST/'||substr(file_name,21,instr(file_name,'.')-21)||'_01.dbf'''||';' from dba_data_files order by 1;

alter database rename file '+DATA/test/datafile/sysaux.300.697649963' to '/app/oracle/TEST/sysaux_01.dbf';
alter database rename file '+DATA/test/datafile/system.299.697649963' to '/app/oracle/TEST/system_01.dbf';
alter database rename file '+DATA/test/datafile/undotbs1.301.697649965' to '/app/oracle/TEST/undotbs1_01.dbf';
alter database rename file '+DATA/test/datafile/users.302.697649965' to '/app/oracle/TEST/users_01.dbf';
alter database rename file '+DATA/test/datafile/users.309.697650601' to '/app/oracle/TEST/users_02.dbf';

open the database and check for files still using ASM

open database

select name from v$controlfile
union
select name from v$tempfile
union
select member from v$logfile
union
select filename from v$block_change_tracking;

 

NAME
--------------------------------------------------------------------------------
+DATA/test/controlfile/current.303.697650047
+DATA/test/onlinelog/group_1.304.697650049
+DATA/test/onlinelog/group_2.305.697650049
+DATA/test/onlinelog/group_3.306.697650049
+DATA/test/tempfile/temp.307.697650095
+FRA/test/controlfile/current.260.697650047
+FRA/test/onlinelog/group_1.261.697650049
+FRA/test/onlinelog/group_2.262.697650049
+FRA/test/onlinelog/group_3.263.697650051

alter database rename file '+DATA/test/datafile/sysaux.300.697649963' to '/app/oracle/TEST/sysaux_01.dbf';

alter database rename file '+DATA/test/datafile/system.299.697649963' to '/app/oracle/TEST/system_01.dbf';

alter database rename file '+DATA/test/datafile/undotbs1.301.697649965' to '/app/oracle/TEST/undotbs1_01.dbf';

alter database rename file '+DATA/test/datafile/users.302.697649965' to '/app/oracle/TEST/users_01.dbf';

alter database rename file '+DATA/test/datafile/users.309.697650601' to '/app/oracle/TEST/users_02.dbf';

ALTER DATABASE ADD LOGFILE GROUP 4 ('/app/oracle/TEST/redo_04a.dbf','/app/oracle/TEST/redo_04b.dbf') size 50M;

ALTER DATABASE ADD LOGFILE GROUP 5 ('/app/oracle/TEST/redo_05a.dbf','/app/oracle/TEST/redo_05b.dbf') size 50M;

ALTER DATABASE ADD LOGFILE GROUP 6 ('/app/oracle/TEST/redo_06a.dbf','/app/oracle/TEST/redo_06b.dbf') size 50M; 

You will need to perform logfile switches or checkpoint before the log groups using ASM are available for removal. 

SQL>

show parameter control

 control_files +DATA/test/controlfile/current .303.697650047, +FRA/test/cont rolfile/current.260.697650047

create a pfile from spfile and edit the pfile to show new placement for the controlfiles

*.control_files='/app/oracle/TEST/control01.ctl','/app/oracle/TEST/control02.ctl'

startup nomount

$rman target / 

Recovery Manager: Release 11.1.0.7.0 - Production on Wed Sep 16 16:36:15 2009 connected to target database:

TEST (not mounted) using target database control file instead of recovery catalog

 restore controlfile from '+DATA/test/controlfile/current.303.697650047';

Starting restore at 16-SEP-09 allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=70 device type=DISK channel ORA_DISK_1: copied control file copy output file name=/app/oracle/TEST/control01.ctl output file name=/app/oracle/TEST/control02.ctl

Finished restore at 16-SEP-09

 exit

Recovery Manager complete.

 

create spfile='/app/oracle/product/11.1.0/db_1/dbs/spfileTEST.ora' from pfile='/home/oracle/john.ora';

File created.
shutdown immediate; 
startup

show parameter control

control_files  /app/oracle/TEST/control01.ctl , /app/oracle/TEST/control02.ctl 

All that is left is to recreate the temp tablespace to use filesystem storage.

startup mount;

RMAN> run { set newname for tempfile 1 to '/app/oracle/TEST/temp01.dbf'; switch tempfile all;}

executing command:

SET NEWNAME renamed tempfile 1 to /app/oracle/TEST/temp01.dbf in control file  rman target /

 TEST (not mounted) using target database control file instead of recovery catalog RMAN> restore controlfile from '+DATA/test/controlfile/current.303.697650047';

Starting restore at 16-SEP-09 allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=69 device type=DISK channel ORA_DISK_1:

copied control file copy output file

name=/app/oracle/TEST/control01.ctl output file name=/app/oracle/TEST/control02.ctl

Finished restore at 16-SEP-09

All should be complete now. As I said, this is just a hint on what actions to take and the sql scripts should be useful if you have lots of datafiles.

All that is left is to create a new  block_change_tracking file and the ASM storage can be released.

Oracle 11G Release 2 available (Linux only though)

Linux only at the moment

 http://www.oracle.com/technology/products/database/oracle11g/index.html

  11GR2 documentation

 http://www.oracle.com/pls/db112/homepage

 11GR2 new features

 http://download.oracle.com/docs/cd/E11882_01/server.112/e10881/toc.htm

  new features index page shows the following (of which I will be looking at ASM first)

 1.1 Application Development

• • 1.1.1 Oracle Application Express
    • 1.1.1.1 Application Date Format
    • 1.1.1.2 Custom Themes
    • 1.1.1.3 Declarative BLOB Support
    • 1.1.1.4 Documented JavaScript Libraries
    • 1.1.1.5 Enhanced Report Printing
    • 1.1.1.6 Forms Conversion
    • 1.1.1.7 Improved Security
    • 1.1.1.8 Interactive Reporting Region
    • 1.1.1.9 Runtime-Only Installation
  • 1.1.2 Other General Development Features
    • 1.1.2.1 Support WITH HOLD Option for CURSOR DECLARATION in Pro*C
    • 1.1.2.2 Pro*C Support for 8-Byte Native Numeric Host Variable for INSERT and FETCH
    • 1.1.2.3 Pro*COBOL Support for 8-Byte Native Numeric Host Variable for INSERT and FETCH
    • 1.1.2.4 JDBC Support for Time Zone Patching
    • 1.1.2.5 JDBC Support for SecureFile Zero-Copy LOB I/O and LOB Prefetching
    • 1.1.2.6 OCI Support for 8-Byte Integer Bind/Define
• 1.2 Availability
  • 1.2.1 Backup and Recovery
    • 1.2.1.1 Automatic Block Repair
    • 1.2.1.2 Backup to Amazon Simple Storage Service (S3) Using OSB Cloud Computing
    • 1.2.1.3 DUPLICATE Without Connection to Target Database
    • 1.2.1.4 Enhanced Tablespace Point-In-Time Recovery (TSPITR)
    • 1.2.1.5 New DUPLICATE Options
    • 1.2.1.6 New SET NEWNAME Clauses and Format Options
    • 1.2.1.7 Tablespace Checks in DUPLICATE
  • 1.2.2 Online Application Maintenance and Upgrade
    • 1.2.2.1 Edition-based Redefinition
    • 1.2.2.2 Enhance CREATE or REPLACE TYPE to Allow FORCE
    • 1.2.2.3 Fine-Grained Dependencies for Triggers
    • 1.2.2.4 IGNORE_ROW_ON_DUPKEY_INDEX Hint for INSERT Statement
  • 1.2.3 Oracle Data Guard
    • 1.2.3.1 Compressed Table Support in Logical Standby Databases and Oracle LogMiner
    • 1.2.3.2 Configurable Real-Time Query Apply Lag Limit
    • 1.2.3.3 Integrated Support for Application Failover in a Data Guard Configuration
    • 1.2.3.4 Support Up to 30 Standby Databases
• 1.3 Business Intelligence and Data Warehousing
  • 1.3.1 Improved Analytics
    • 1.3.1.1 Analytic Functions 2.0
    • 1.3.1.2 Recursive WITH Clause
  • 1.3.2 Improved Data Loading
    • 1.3.2.1 EXECUTE Privilege for DIRECTORY Objects
    • 1.3.2.2 Preprocessing Data for ORACLE_LOADER Access Driver in External Tables
  • 1.3.3 Improved Partitioning
    • 1.3.3.1 Allow Virtual Columns in the Primary Key or Foreign Key for Reference Partitioning
    • 1.3.3.2 System-Managed Indexes for List Partitioning
  • 1.3.4 Improved Performance and Scalability
    • 1.3.4.1 In-Memory Parallel Execution
    • 1.3.4.2 Minimal Effort Parallel Execution – Auto Degree of Parallelism (DOP) and Queuing
    • 1.3.4.3 The DBMS_PARALLEL_EXECUTE Package
    • 1.3.4.4 Significant Performance Improvement of On-Commit Fast Refresh
  • 1.3.5 Oracle Warehouse Builder
    • 1.3.5.1 Advanced Find Support in Mapping Editor
    • 1.3.5.2 Business Intelligence Tool Integration
    • 1.3.5.3 Copy and Paste of Operators and Attributes in Mapping Editor
    • 1.3.5.4 Current Configuration Dropdown List in Design Center Toolbar
    • 1.3.5.5 Enhanced Support for Flat File Imports
    • 1.3.5.6 Enhanced Table Function Support
    • 1.3.5.7 Experts Available in Editor Menu
    • 1.3.5.8 Expression Editing in Operator Edit Dialog
    • 1.3.5.9 Grouping and Spotlighting of Objects in Mapping Editor
    • 1.3.5.10 Improved Management of Locations Registered in Multiple Control Centers
    • 1.3.5.11 Improved User Interface for Managing Locations
    • 1.3.5.12 Key Lookup Operator Enhancements
    • 1.3.5.13 Mapping Debugger Enhancements
    • 1.3.5.14 New JDeveloper-Style User Interface
    • 1.3.5.15 Operator References Included in Generated PL/SQL Code
    • 1.3.5.16 Quick Mapper
    • 1.3.5.17 Repository Browser Changes
    • 1.3.5.18 Simplified Oracle Warehouse Builder Repository Upgrades
    • 1.3.5.19 Support for Extracting Data From Tables Containing LONG Data Type
    • 1.3.5.20 Support for Subqueries in Join Operator
• 1.4 Clustering
  • 1.4.1 Oracle Real Application Clusters Ease-of-Use
    • 1.4.1.1 Configuration Assistants Support New Oracle RAC Features
    • 1.4.1.2 Enhanced Cluster Verification Utility
    • 1.4.1.3 Integration of Cluster Verification Utility and Oracle Universal Installer
    • 1.4.1.4 Cluster Time Service
    • 1.4.1.5 Oracle Cluster Registry (OCR) Enhancements
    • 1.4.1.6 Grid Plug and Play (GPnP)
    • 1.4.1.7 Oracle Restart
    • 1.4.1.8 Policy-Based Cluster and Capacity Management
    • 1.4.1.9 Improved Clusterware Resource Modeling
    • 1.4.1.10 Role-Separated Management
    • 1.4.1.11 Agent Development Framework
    • 1.4.1.12 Zero Downtime Patching for Oracle Clusterware and Oracle RAC
    • 1.4.1.13 Enterprise Manager-Based Clusterware Resource Management
    • 1.4.1.14 Enterprise Manager Provisioning for Oracle Clusterware and Oracle Real Application Clusters
    • 1.4.1.15 Enterprise Manager Support for Grid Plug and Play
    • 1.4.1.16 Enterprise Manager Support for Oracle Restart
    • 1.4.1.17 Configuration Assistant Support for Removing Oracle RAC Installations
    • 1.4.1.18 Oracle Universal Installer Support for Removing Oracle RAC Installations
    • 1.4.1.19 Improved Deinstallation Support With Oracle Universal Installer
    • 1.4.1.20 Downgrading Database Configured With DBControl
    • 1.4.1.21 Oracle Restart Integration with Oracle Universal Installer
    • 1.4.1.22 Out-of-Place Oracle Clusterware Upgrade
    • 1.4.1.23 OUI Support for Out-of-Place Oracle Clusterware Upgrade
    • 1.4.1.24 Server Control (SRVCTL) Enhancements
    • 1.4.1.25 Server Control (SRVCTL) Enhancements to Support Grid Plug and Play
    • 1.4.1.26 SRVCTL Support for Single-Instance Database in a Cluster
    • 1.4.1.27 Universal Connection Pool (UCP) Integration with Oracle Data Guard
    • 1.4.1.28 UCP Integration With Oracle Real Application Clusters
    • 1.4.1.29 Universal Connection Pool (UCP) for JDBC
    • 1.4.1.30 Java API for Oracle RAC FAN High Availability Events
• 1.5 Database Overall
  • 1.5.1 General
    • 1.5.1.1 Flashback Data Archive Support for DDLs
    • 1.5.1.2 Instance Caging
  • 1.5.2 Improvements to Oracle Scheduler
    • 1.5.2.1 E-mail Notification
    • 1.5.2.2 File Watcher
    • 1.5.2.3 Multiple Destination Jobs
    • 1.5.2.4 Remote Database Jobs
  • 1.5.3 Improvements to Utilities
    • 1.5.3.1 Data Pump Legacy Mode
  • 1.5.4 IPv6 Support
    • 1.5.4.1 Complete IPv6 Support for JDBC Thin Clients
    • 1.5.4.2 Complete IPv6 Support for JVM and Java Debuggers
• 1.6 Diagnosability
  • 1.6.1 Support Workbench
    • 1.6.1.1 Enterprise Manager Support Workbench for ASM
• 1.7 Performance
  • 1.7.1 General Server Performance
    • 1.7.1.1 Stored Outlines Migration to SQL Plan Management
    • 1.7.1.2 Client Result Cache Using Table Annotations Support
    • 1.7.1.3 Support 4 KB Sector Disk Drives
• 1.8 Security
  • 1.8.1 Audit Data Management
    • 1.8.1.1 Audit Trail Cleanup
  • 1.8.2 Encryption Key Management
    • 1.8.2.1 Tablespace Master Key Rekey
• 1.9 Server Manageability
  • 1.9.1 Automatic Storage Management for All Data
    • 1.9.1.1 ASM Cluster File System (ACFS)
    • 1.9.1.2 ASM Dynamic Volume Manager (DVM)
    • 1.9.1.3 ASM FS Snapshot
    • 1.9.1.4 Oracle Cluster Registry (OCR) and Voting Disk on ASM
    • 1.9.1.5 ASM Intelligent Data Placement
    • 1.9.1.6 ASM Storage Management Configuration Assistant
    • 1.9.1.7 Automatic Storage Management (ASM) File Access Control
    • 1.9.1.8 ASMCMD Command Extensions
    • 1.9.1.9 Enterprise Manager Support for ASM Cluster File System (ACFS)
    • 1.9.1.10 Enterprise Manager Integration for ASM File Access Control
  • 1.9.2 Database Management
    • 1.9.2.1 EMCA Supports New Oracle RAC Configuration for Enterprise Manager
    • 1.9.2.2 Patch Application with DBControl
    • 1.9.2.3 Automatic Patching of Time Stamp With Time Zone Data
    • 1.9.2.4 Prevent Data Loss for Time Zone with Local Time Zone Data Type
    • 1.9.2.5 Segment Creation on Demand
    • 1.9.2.6 Zero-Size Unusable Indexes and Index Partitions
    • 1.9.2.7 Metadata SXML Comparison Tool
    • 1.9.2.8 Compare Period Report
    • 1.9.2.9 Compare SQL Tuning Sets
    • 1.9.2.10 Enable Sampling for Active Data Guard
    • 1.9.2.11 Exadata Simulation
    • 1.9.2.12 Global Oracle RAC ASH Report + ADDM Backwards Compatibility
    • 1.9.2.13 Oracle MTS and Streams Support
    • 1.9.2.14 Parallel Query + Alt Plan Recording, Export STS Enhancements
    • 1.9.2.15 Synchronization Controls
• 1.10 Unstructured Data Management
  • 1.10.1 Enhanced Oracle Multimedia and DICOM Support
    • 1.10.1.1 Attribute Extraction of Requested Attributes Only
    • 1.10.1.2 Client-Side DICOM Attribute Extraction
    • 1.10.1.3 DICOM Enhancements
    • 1.10.1.4 Watermarking and Image Processing Enhancements
  • 1.10.2 Enhanced Oracle Spatial Support
    • 1.10.2.1 3D Visualization Framework
    • 1.10.2.2 Network Data Model Enhancements
    • 1.10.2.3 New GeoRaster JAVA API
    • 1.10.2.4 Raster Reprojections and Ground Control Point-Based (GCP) Georeferencing
    • 1.10.2.5 Routing and Geocoding Enhancements
  • 1.10.3 Oracle SecureFiles
    • 1.10.3.1 Database File System (DBFS)
    • 1.10.3.2 LZO Support for SecureFiles
  • 1.10.4 Oracle XML DB Scale and Performance Improvements
    • 1.10.4.1 Binary XML Enhancements
    • 1.10.4.2 Oracle XML DB Repository Performance Improvements and Guidelines

1.10.4.3 XMLIndex Enhancements

ASM space marked as internal – use check all repair

The problem was that the FRA diskgroup seemed to be using a lot of space and and yet there was hardly  files on  disk as far as I could tell. HP Itanium – 11.1.0.7

ASM Disk Groups
===============

     Group Group Name                State           Type      Total GB Free GB
---------- ------------------------- --------------- ------- ---------- -------
         1 DATA                      MOUNTED         EXTERN        2581     109
         2 FRA                       MOUNTED         EXTERN        1602     127

 
Group Disk Header    Mode     State           Redundancy   Total MB    Free MB Disk Name                      Failure Gr
----- ---- --------- -------- --------------- ---------- ---------- ---------- ------------------------------ ----------
    2    1 MEMBER    ONLINE   NORMAL          UNKNOWN         91138          0 FRA_0001                       FRA_0001
    2    2 MEMBER    ONLINE   NORMAL          UNKNOWN         91138          0 FRA_0002                       FRA_0002
    2    4 MEMBER    ONLINE   NORMAL          UNKNOWN         91138          0 FRA_0004                       FRA_0004
    2    5 MEMBER    ONLINE   NORMAL          UNKNOWN         91138          0 FRA_0005                       FRA_0005
    2    6 MEMBER    ONLINE   NORMAL          UNKNOWN         91138          0 FRA_0006                       FRA_0006
    2    7 MEMBER    ONLINE   NORMAL          UNKNOWN         91138          0 FRA_0007                       FRA_0007
    2    8 MEMBER    ONLINE   NORMAL          UNKNOWN         91138          0 FRA_0008                       FRA_0008
    2    9 MEMBER    ONLINE   NORMAL          UNKNOWN         91138          0 FRA_0009                       FRA_0009
    2   10 MEMBER    ONLINE   NORMAL          UNKNOWN         91138          0 FRA_0010                       FRA_0010
    2   11 MEMBER    ONLINE   NORMAL          UNKNOWN         91138          0 FRA_0011                       FRA_0011
    2   12 MEMBER    ONLINE   NORMAL          UNKNOWN         91138          0 FRA_0012                       FRA_0012
    2   13 MEMBER    ONLINE   NORMAL          UNKNOWN         91138          0 FRA_0013                       FRA_0013
    2   14 MEMBER    ONLINE   NORMAL          UNKNOWN         91138          0 FRA_0014                       FRA_0014
    2   15 MEMBER    ONLINE   NORMAL          UNKNOWN         91138          0 FRA_0015                       FRA_0015
    2   16 MEMBER    ONLINE   NORMAL          UNKNOWN         91138          0 FRA_0016                       FRA_0016
    2   17 MEMBER    ONLINE   NORMAL          UNKNOWN         91138          0 FRA_0017                       FRA_0017
    2   18 MEMBER    ONLINE   NORMAL          UNKNOWN         91138      65242 FRA_0018                       FRA_0018
    2   19 MEMBER    ONLINE   NORMAL          UNKNOWN         91138      65112 FRA_0019                       FRA_0019

 I had removed one disk from FRA and forced a rebalance but that still did not release space. I had checked the FRA disks using asmcmd but that only showed 2 online logs which were 2Gb each (we were not in archive log mode).

I had checked the diskgroup using the EM Gui which issued the command  

ALTER DISKGROUP FRA CHECK ALL:

but still no success. Looking at the documentation the check command taking NOREPAIR as the default should have reported any issues into the ASM alert log.  Which indeed it did (and I would have seen them if I had bothered to look)

Wed Jul 29 16:37:55 2009
SQL> ALTER DISKGROUP FRA CHECK DISK FRA_0001,FRA_0002,FRA_0004,FRA_0005,FRA_0006,FRA_0007,FRA_0008,FRA_0009,FRA_0010,FRA_0011,FRA_0012,FRA_0013,FRA_0014,FRA_0015,FRA_0016,FRA_0017,FRA_0018,FRA_0019
WARNING: Deprecated privilege SYSDBA for command 'ALTER DISKGROUP CHECK'
kfdp_checkDsk(): 33
NOTE: disk FRA_0001, used AU total mismatch: DD=4294965892, AT=2283
kfdp_checkDsk(): 34
NOTE: disk FRA_0002, used AU total mismatch: DD=4294966012, AT=2283
kfdp_checkDsk(): 35
NOTE: disk FRA_0004, used AU total mismatch: DD=4294966210, AT=2283
kfdp_checkDsk(): 36
NOTE: disk FRA_0005, used AU total mismatch: DD=4294966625, AT=2283
kfdp_checkDsk(): 37
NOTE: disk FRA_0006, used AU total mismatch: DD=4294965615, AT=2280
kfdp_checkDsk(): 38
NOTE: disk FRA_0007, used AU total mismatch: DD=4294964292, AT=2283
kfdp_checkDsk(): 39
NOTE: disk FRA_0008, used AU total mismatch: DD=4294967237, AT=2284
kfdp_checkDsk(): 40
NOTE: disk FRA_0009, used AU total mismatch: DD=4294965595, AT=2280
kfdp_checkDsk(): 41
NOTE: disk FRA_0010, used AU total mismatch: DD=4294966758, AT=2279
kfdp_checkDsk(): 42
NOTE: disk FRA_0011, used AU total mismatch: DD=4294966324, AT=2279
kfdp_checkDsk(): 43
NOTE: disk FRA_0012, used AU total mismatch: DD=4294966743, AT=2279
kfdp_checkDsk(): 44
NOTE: disk FRA_0013, used AU total mismatch: DD=4294964514, AT=2278
kfdp_checkDsk(): 45
NOTE: disk FRA_0014, used AU total mismatch: DD=4294965734, AT=2278
kfdp_checkDsk(): 46
NOTE: disk FRA_0015, used AU total mismatch: DD=4294965204, AT=2279
kfdp_checkDsk(): 47
NOTE: disk FRA_0016, used AU total mismatch: DD=4294965599, AT=2279
kfdp_checkDsk(): 48
NOTE: disk FRA_0017, used AU total mismatch: DD=4294964950, AT=2280
kfdp_checkDsk(): 49
NOTE: disk FRA_0018, used AU total mismatch: DD=25896, AT=2278
kfdp_checkDsk(): 50
NOTE: disk FRA_0019, used AU total mismatch: DD=26026, AT=2280
WARNING: deprecated use of ALTER DISKGROUP CHECK arguments
SUCCESS: ALTER DISKGROUP FRA CHECK DISK FRA_0001,FRA_0002,FRA_0004,FRA_0005,FRA_0006,FRA_0007,FRA_0008,FRA_0009,FRA_0010,FRA_0011,FRA_0012,FRA_0013,FRA_0014,FRA_0015,FRA_0016,FRA_0017,FRA_0018,FRA_0019

From the command line I then ran the comand

ALTER DISKGROUP FRA CHECK ALL REPAIR;

and I had immediate success. It took about 1 minute and corrected the issues and then released the space that had been grabbed internally.

     Group Group Name                State           Type      Total GB Free GB
---------- ------------------------- --------------- ------- ---------- -------
         1 DATA                      MOUNTED         EXTERN        2581     109
         2 FRA                       MOUNTED         EXTERN  1602   1,562

Group Disk Header    Mode     State           Redundancy   Total MB    Free MB Disk Name                      Failure Gr
----- ---- --------- -------- --------------- ---------- ---------- ---------- ------------------------------ ----------
    2    1 MEMBER    ONLINE   NORMAL          UNKNOWN         91138      88855 FRA_0001                       FRA_0001
    2    2 MEMBER    ONLINE   NORMAL          UNKNOWN         91138      88855 FRA_0002                       FRA_0002
    2    4 MEMBER    ONLINE   NORMAL          UNKNOWN         91138      88855 FRA_0004                       FRA_0004
    2    5 MEMBER    ONLINE   NORMAL          UNKNOWN         91138      88855 FRA_0005                       FRA_0005
    2    6 MEMBER    ONLINE   NORMAL          UNKNOWN         91138      88858 FRA_0006                       FRA_0006
    2    7 MEMBER    ONLINE   NORMAL          UNKNOWN         91138      88855 FRA_0007                       FRA_0007
    2    8 MEMBER    ONLINE   NORMAL          UNKNOWN         91138      88854 FRA_0008                       FRA_0008
    2    9 MEMBER    ONLINE   NORMAL          UNKNOWN         91138      88858 FRA_0009                       FRA_0009
    2   10 MEMBER    ONLINE   NORMAL          UNKNOWN         91138      88859 FRA_0010                       FRA_0010
    2   11 MEMBER    ONLINE   NORMAL          UNKNOWN         91138      88859 FRA_0011                       FRA_0011
    2   12 MEMBER    ONLINE   NORMAL          UNKNOWN         91138      88859 FRA_0012                       FRA_0012
    2   13 MEMBER    ONLINE   NORMAL          UNKNOWN         91138      88860 FRA_0013                       FRA_0013
    2   14 MEMBER    ONLINE   NORMAL          UNKNOWN         91138      88860 FRA_0014                       FRA_0014
    2   15 MEMBER    ONLINE   NORMAL          UNKNOWN         91138      88859 FRA_0015                       FRA_0015
    2   16 MEMBER    ONLINE   NORMAL          UNKNOWN         91138      88859 FRA_0016                       FRA_0016
    2   17 MEMBER    ONLINE   NORMAL          UNKNOWN         91138      88858 FRA_0017                       FRA_0017
    2   18 MEMBER    ONLINE   NORMAL          UNKNOWN         91138      88860 FRA_0018                       FRA_0018
    2   19 MEMBER    ONLINE   NORMAL          UNKNOWN         91138      88858 FRA_0019                       FRA_0019

 

SQL> alter diskgroup fra check all repair
NOTE: starting check of diskgroup FRA
kfdp_checkDsk(): 51
WARNING: disk FRA_0001, changing DD used AUs from 4294965892 to 2283
kfdp_checkDsk(): 52
WARNING: disk FRA_0002, changing DD used AUs from 4294966012 to 2283
kfdp_checkDsk(): 53
WARNING: disk FRA_0004, changing DD used AUs from 4294966210 to 2283
kfdp_checkDsk(): 54
WARNING: disk FRA_0005, changing DD used AUs from 4294966625 to 2283
kfdp_checkDsk(): 55
WARNING: disk FRA_0006, changing DD used AUs from 4294965615 to 2280
kfdp_checkDsk(): 56
WARNING: disk FRA_0007, changing DD used AUs from 4294964292 to 2283
kfdp_checkDsk(): 57
WARNING: disk FRA_0008, changing DD used AUs from 4294967237 to 2284
kfdp_checkDsk(): 58
WARNING: disk FRA_0009, changing DD used AUs from 4294965595 to 2280
kfdp_checkDsk(): 59
WARNING: disk FRA_0010, changing DD used AUs from 4294966758 to 2279
kfdp_checkDsk(): 60
WARNING: disk FRA_0011, changing DD used AUs from 4294966324 to 2279
kfdp_checkDsk(): 61
WARNING: disk FRA_0012, changing DD used AUs from 4294966743 to 2279
kfdp_checkDsk(): 62
WARNING: disk FRA_0013, changing DD used AUs from 4294964514 to 2278
kfdp_checkDsk(): 63
WARNING: disk FRA_0014, changing DD used AUs from 4294965734 to 2278
kfdp_checkDsk(): 64
WARNING: disk FRA_0015, changing DD used AUs from 4294965204 to 2279
kfdp_checkDsk(): 65
WARNING: disk FRA_0016, changing DD used AUs from 4294965599 to 2279
kfdp_checkDsk(): 66
WARNING: disk FRA_0017, changing DD used AUs from 4294964950 to 2280
kfdp_checkDsk(): 67
WARNING: disk FRA_0018, changing DD used AUs from 25896 to 2278
kfdp_checkDsk(): 68
WARNING: disk FRA_0019, changing DD used AUs from 26026 to 2280
SUCCESS: check of diskgroup FRA found no errors
WARNING: deprecated use of ALTER DISKGROUP CHECK arguments
SUCCESS: alter diskgroup fra check all repair
Thu Jul 30 10:31:33 2009

 

I would have expected the gui to have reported errors but it didn’t. Equally I can see no good reason  for not fixing errors it identified when a check is run. That was the default in 10G  so  there must be a good reason why it was changed. Perhaps it had a performance impact which I would not have noticed on a pretty empty diskgroup. The other reason is that it might make the assumption that the DBA would want a good backup before repairing.

As we had nothing in there and we backup ASM metadata every day then I was happy to go ahead. http://jhdba.wordpress.com/2009/06/11/script-to-backup-asm-metadata/

 So the lessons learned are

1. Use the check all feature
2. Check the alert log afterwards
3. Then use the check all repair statement from command line

Script to backup ASM metadata

This script was written by a colleague James Hardaker.
Creates a backup directory if required, works out the ASM SID, renames the previous entry, then loops round backing up the metadata data for each diskgroup. It also lists each file backed up which is quite helpful.
Finally it creates error checking and a log and error file outputs

#!/bin/sh
#
# Date  Version Author          Comments
# 09/06/09      1.1             Initial Version
# 10/06/09      1.2             Added Error Handling
#
#
# Description
# Queries ASM for a list of disk groups and backs up the metadata of each to /app/oracle/backups
# Should be run as the oracle user

# Tunables
###

BACKUPDIR=/app/oracle/backups

###

##########
# Start of functions
#
# asm_metadata - backup the asm metadata
asm_metadata()
{

export ORACLE_SID=`cat /etc/oratab | grep ^+ASM | awk '{FS=":"} {print $1} ' `
export ORACLE_HOME=`cat /etc/oratab | grep ^+ASM | awk '{FS=":"} {print $2} ' `

for DG in `asmcmd ls + | sed 's/\///'`
do
        echo "INFO: Backup of ${DG} diskgroup started at `date`"
        if [ -f ${BACKUPDIR}/asm_metadata_${DG}.bkp ]
        then
                echo "INFO: Moving the file ${BACKUPDIR}/asm_metadata_${DG}.bkp to ${BACKUPDIR}/asm_metadata_${DG}.old"
                cp ${BACKUPDIR}/asm_metadata_${DG}.bkp ${BACKUPDIR}/asm_metadata_${DG}.old 2>/dev/null
                        if [ $? -ne 0 ]
                        then
                                echo "WARN: Unable to backup ${BACKUPDIR}/asm_metadata_${DG}.bkp, will overwrite it anyway"
                        fi
                rm -f ${BACKUPDIR}/asm_metadata_${DG}.bkp 2>/dev/null
        fi
        echo "INFO: Backing up the ${DG} diskgroup to ${BACKUPDIR}/asm_metadata_${DG}.bkp"
        rm ${BACKUPDIR}/asm_metadata_${DG}.err 2>/dev/null
        ${ORACLE_HOME}/bin/asmcmd md_backup -b ${BACKUPDIR}/asm_metadata_${DG}.bkp -g ${DG} 2>${BACKUPDIR}/asm_metadata_${DG}.err
        if [ -s ${BACKUPDIR}/asm_metadata_${DG}.err ]
        then
                echo ""
                echo "WARN: Unable to backup the ${DG} diskgroup to ${BACKUPDIR}/asm_metadata_${DG}.bkp"
                cat ${BACKUPDIR}/asm_metadata_${DG}.err
                echo ""
                ERR=98
        else
                echo "INFO: Backup of ${DG} diskgroup completed at `date`"
        fi
        echo ""
done
}

##########
# End of functions

### Main Program

echo "INFO: ASM Metadata backup started at `date`"
echo ""
echo "INFO: This script is being run as user `/usr/bin/id -un`"
echo ""

# create the backup directory if it does not exist
#

if [ ! -d ${BACKUPDIR} ]
then
        mkdir ${BACKUPDIR} 2>/dev/null
        echo "INFO: Created the directory ${BACKUPDIR} as it did not exist"
        echo ""
                if [ $? -ne 0 ]
                then
                        echo "ERROR: Unable to create the directory ${BACKUPDIR}"
                        exit 99
                fi
fi

# backup the asm metadata
#
ERR=0
asm_metadata

#Finish
#

if [ ${ERR} != 0 ]
then
        echo "ERROR: An error occurred backing up one or more disk groups, see above"
        exit 98
else
        echo "INFO: ASM Metadata backup completed at `date`"
fi

exit

A sample output for part of the DATA datagroup looks like this :-

@diskgroup_set = (
                   {
                     'ATTRINFO' => {
                                     'AU_SIZE' => '8388608',
                                     'DISK_REPAIR_TIME' => '3.6h',
                                     'COMPATIBLE.ASM' => '11.1.0.0.0',
                                     'COMPATIBLE.RDBMS' => '11.1'
                                   },
                     'DISKSINFO' => {
                                      'DATA_0036' => {
                                                       'DATA_0036' => {
                                                                        'TOTAL_MB' => '91136',
                                                                        'FAILGROUP' => 'DATA_0036',
                                                                        'NAME' => 'DATA_0036',
                                                                        'DGNAME' => 'DATA',
                                                                        'PATH' => '/dev/oracle/disk22'
                                                                      }
                                                     },
                                      'DATA_0006' => {
                                                       'DATA_0006' => {
                                                                        'TOTAL_MB' => '91136',
                                                                        'FAILGROUP' => 'DATA_0006',
                                                                        'NAME' => 'DATA_0006',
                                                                        'DGNAME' => 'DATA',
                                                                        'PATH' => '/dev/oracle/disk6'
                                                                      }
                                                     },
                                      'DATA_0069' => {
                                                       'DATA_0069' => {
                                                                        'TOTAL_MB' => '91136',
                                                                        'FAILGROUP' => 'DATA_0069',
                                                                        'NAME' => 'DATA_0069',
                                                                        'DGNAME' => 'DATA',
                                                                        'PATH' => '/dev/oracle/disk87'

ASM metadata and migrating a database to ASM

Another ASM entry today covering two discussion points
I had to migrate a database from cooked filesystem to ASM and rather than using a script I used the ‘migrate to ASM’ option in Enterprise Manager. This was the first time and it was an easy point and click task but it did seem to be very slow.
This is documented on OTN at Migrate ASM
The second point covers ASM metdata. If you create a new diskgroup with 5 disks and add the 5 disks as part of the creation using a command similar to

create diskgroup newdg external redundancy disk '/dev/oracle/disk100';

then metadata is written to the disk
If you create the diskgroup adding all 5 disks at the same time

create diskgroup newdg external redundancydisk '/dev/oracle/disk100','/dev/oracle/disk101','/dev/oracle/disk102','/dev/oracle/disk103','/dev/oracle/disk104'; 

then the metadata is striped across all 5 disks
However if you create the diskgroup with 1 disk and add 4 disks later the metadata is only written to the first disk. I dont see this as a major issue but it does seem a flaw in the functionality

create diskgroup newdg external redundancy disk '/dev/oracle/disk100';
alter diskgroup newdg add disk '/dev/oracle/disk101','/dev/oracle/disk102','/dev/oracle/disk103','/dev/oracle/disk104';

My next entry will also be on ASM and will provide a nice script for backing up metadata

Autostart using the ‘W’ flag in oratab

In 10.2.0.2 an option arrived to manage the automatic startup and shutdown of the databases. This is documented in the manual and also in Metalink note 788502.1.
It  is probably easier to reproduce the key paragraph here

Database entries in the oratab file are displayed in the following format:

SID:ORACLE_HOME:{Y|N|W}

In this example, the values Y and N specify whether you want the scripts to start up or shut down the database, respectively. For each database for which you want to automate shutdown and startup, first determine the instance identifier (SID) for that database, which is identified by the SID in the first field.

Then, change the last field for each to Y.

You can set dbstart to autostart a single-instance database that uses an Automatic Storage Management installation that is auto-started by Oracle Clusterware. This is the default behavior for an Automatic Storage Management cluster. If you want to do this, then you must change the oratab entry of the database and the Automatic Storage Management installation to use a third field with the value W and N, respectively. These values specify that dbstart auto-starts the database only after the Automatic Storage Management instance is started.

However on playing around with these settings on a server with ASM and 11.1.0.7 installed complete with a single instance database I could not see any way that the functionality was working.

Looking around on the net there does not seem to be much information out there and nothing on Metalink

In our environment we have a standard that we set up the oratab in the following manner

DATABASE SID that will be the default ORACLE_SID and ORACLE_HOME when you login
DATABASE_SID1 – if available
DATABASE_SID2 – if available
+ASM – the asm instance entry
agent10g – the ORACLE_HOME for the grid agent.

I suspect that if the ‘W’ flag in oratab did work then we would need to change that standard to get the ASM entry in as the first entry.

If anyone has any more feedback please feel free to comment

Location of diagnostic directories in 11g

Since the advent of 11g and the use of diagnostic_dest there is no requirement to create the bdump/cdump/udump  and other directories required for oracle trace and admin files. Well, that is true if you are happy to accept the default location under $ORACLE_BASE. However the path to the trace area where alert logs and dump files are kept is a long one and I prefer  to use an alias to get there.

In the .profile I set up two aliases to point to the trace areas for ASM and whichever database my current SID is set to

alias diagdest=’cd $ORACLE_BASE/`adrci exec=”show homes” | grep $ORACLE_SID`/trace’

alias diagdestasm=’cd $ORACLE_BASE/`adrci exec=”show homes” | grep ASM`/trace’

I am sure there are other ways of setting a similar alias and I am happy to hear of any that others use

Cloning a database (ASM to ASM)

Cloning production ASM (RAC)  to ASM (non RAC)

In this document I will be cloning database SOURCEDB (11.1.0.6 RAC) to TARGETDB (11.1.0.6 single instance). There will be no downtime on the source database and minimal impact.

Source server source_server  150Gb data ASM storage used

Target server target_server

Pre-requirements -ensure that you have backed up the source database and are comfortable that you are covered in case anything goes wrong

200Gb of disk space

NFS mount between the 2 servers. In this case I created a directory /app/oradata/rman_backup_dir and arranged for a NFS mount to the same filesystem on target_server

source_server:/app/oradata/rman_backup_dir

403701760 42657229 338479316   11% /app/oradata/rman_backup_dir

The steps we will follow will be :-

• Prepare database on target server
• Backup source database
• Run duplicate database scripts

Note 1

RMAN uses a terminology of target as being the source database and auxiliary as the duplicated database. In this document I am using the terms source and target for the two databases. However the RMAN script will use target and auxiliary instead out of necessity.

Note 2

Initially I wanted to use active duplicate database however whilst it did work once it failed many more times and there is an Oracle bug Bug 6603587 – CREATION OF SECOND STANDBY USING RMAN DUPLICATE FAILS WITH RMAN-20242

I then tried using non-active database and with a database backup. This time I hit Bug.5653255: PHSB: RMAN CAN\’T RESTORE DATAFILES CORRECTLY ON ASM USING IMAGE COPIES. The workaround to this was to use a backupset..

Prepare target database

I am assuming that we have an existing database built on the target server and we are just performing a refresh from production. However for completeness this section shows how to prepare the spfile and startup nomount the target database.

If this process is a refresh rather than new build then go to here

Create a tnsnames.ora entry on the source server

Note that we are using port 1524 whereas the source database uses port 1522

TARGETDB_CLONE  =

(DESCRIPTION =

(ADDRESS_LIST=

(ADDRESS = (PROTOCOL = TCP)(HOST = target_server)(PORT = 1524))

)

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = TARGETDB)

)

)

Create a listener on the target server

Note that we are using port 1524 whereas the source database uses port 1522

LISTENERCLONE =

(DESCRIPTION_LIST =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = target_server)(PORT = 1524))

)

)

SID_LIST_LISTENERCLONE =

(SID_LIST =

(SID_DESC =

(GLOBAL_DBNAME = TARGETDB)

(ORACLE_HOME = /app/oracle/product/11.1.0/db_1/)

(SID_NAME = TARGETDB)

)

)

Start the listener

lsnrctl start listenerclone

On source_server

create pfile=\’/home/oracle/initTARGETDB.ora\’ from spfile;

Create pfile – target_server. Edit to remove references to RAC (threads etc). As I am moving from DATA/FRA diskgroup to DATA/FRA diskgroup I have no need to use any covert file_name parameters. Edit the spfile to replace the source SID name with the new SID

Edit /etc/oratab to add database entry

Copy the pfile from source_server

cd $ORACLE_HOME/dbs

scp oracle@source_server:/home/oracle/initTARGETDB.ora .

Create audit dest on targetserver

mkdir -p /app/oracle/admin/TARGETDB/adump

Copy passwordfile from source_server

scp oracle@source_server:/app/oracle/product/11.1.0/db_1/orapwSOURCEDB /app/oracle/product/11.1.0/db_1/orapwTARGETDB

Startup standby database from targetserver

sqlplus  / as sysdba

create spfile from pfile;

startup nomount

exit

Start the clone listener on target_server.

This is required as you cannot connect to a database that is unavailable with the asm listener

lsnrctl start listenerclone

From the primary database server source_server test the connection to the target database using the command:

sqlplus sys/password@TARGETDB_CLONE as sysdba

Prepare refreshed database

sqlplus  / as sysdba

shutdown immediate;

startup nomount;

exit

Backup source database

rm /app/oradata/rman_backup_dir/*

export NLS_DATE_FORMAT=\’YYYY-MM-DD:HH24:MI:SS\’

rman target /

run

{

allocate channel d1 device type disk format \’/app/oradata/rman_backup_dir/%U\’;

allocate channel d2 device type disk format \’/app/oradata/rman_backup_dir/%U\’;

allocate channel d3 device type disk format \’/app/oradata/rman_backup_dir/%U\’;

allocate channel d4 device type disk format \’/app/oradata/rman_backup_dir/%U\’;

allocate channel d5 device type disk format \’/app/oradata/rman_backup_dir/%U\’;

backup as backupset database plus archivelog;

}

This takes around 10 minutes. Make a note of the finish time (which is why the NLS_DATE_FORMAT variable was set)

Duplicate database

2 scripts have been created in /home/oracle on pebor02 which are pasted here

rman_duplicate_from_backup.sh

#

# Set the Oracle Environment

#

export ORACLE_HOME= /app/oracle/product/11.1.0/db_1/

export ORACLE_SID=SOURCEDB

export PATH=$ORACLE_HOME/bin:$PATH

export NLS_LANG=american

export NLS_DATE_FORMAT=\’YYYY-MM-DD:HH24:MI:SS\’

echo $ORACLE_HOME

echo $ORACLE_SID

#

# Run the RMAN Command

#

nohup  $ORACLE_HOME/bin/rman target sys/password nocatalog auxiliary sys/password@TARGETDB_clone @rman_duplicate_from_backup.cmd log=/home/oracle/rman_duplicate_from_backup.log  &

rman_duplicate_from_backup.cmd

run

{

set until scn 9660022443946;

allocate auxiliary channel d1 device type disk;

allocate auxiliary channel d2 device type disk;

allocate auxiliary channel d3 device type disk;

allocate auxiliary channel d4 device type disk;

allocate auxiliary channel d5 device type disk;

duplicate target database to TARGETDB;

}

Run the shell script  ./ rman_duplicate_from_backup.sh then it will fail with an invalid SCN, however  the output will provide the latest SCN that can be recovered until. Edit the rman_duplicate_from_backup.cmd file and replace the SCN and rerun.

You can get the SCN from the timestamp by this way you are letting Oracle do the work for you..

Restart the shell script and monitor the log file specified in the shell script.

Tidy up

Stop the listener_clone and start a listener for the newly refreshed database.

Remove the files from /app/oradata/rman_backup_dir

Notify the Apps team that the database has been refreshed and the time that the backup finished.