Tip & Hint: DB_CREATE_FILE_DEST Behavior when using ASM

Recently I saw database admin with issue during the Restore  Database using RMAN and ASM, when database is locate in more than one Diskgroup.
The Datafiles and REDO were created in two different diskgroups.
It was necessary to perform a restore of the database, then the dba removed all datafiles in both diskgroup and executed restore the database.
During the restore RMAN was reported restoring the datafiles in the correct diskgroup and  showing original path/file on prompt of RMAN, but at the end of restore  all datafiles were restored in only one diskgroup, all datafiles were moved/renamed to diskgroup   specified in parameter DB_CREATE_FILE_DEST.
Question: It’s a normal behavior?
Yes…When using ASM and Oracle parameters such as DB_CREATE_FILE_DEST and DB_CREATE_ONLINE_LOG_DEST_n the dba must be careful to either use all OMF files or keep track of the files not created OMF. There is an order in which files are created with these parameters.
By default, when create a Database using DBCA storing datafiles in  ASM the parameter DB_CREATE_FILE_DEST  is set during creation of database, the parameter  DB_CREATE_ONLINE_LOG_DEST_n is not set.

Datafiles Behavior

The restore behavior is the same as with file creation behavior for OMF files. When OMF files are restored, they follow an order of precedence. That order is:

  1. If “SET NEWNAME” is specified, RMAN will use that name for restore.
  2.  If the original file exists, RMAN will use the original filename for restore.
  3.  If the DB_CREATE_FILE_DEST is set and original filename does not exist, RMAN will use the diskgroup name specified
  4.  If no DB_CREATE_FILE_DEST is set and the original file does not exist, then RMAN will create another name for that file in the original disk group.

So, If you do not want surprise after or during the restore, you must unset the parameter DB_CREATE_FILE_DEST before start restore.

alter system set DB_CREATE_FILE_DEST='' scope=memory;

After Restore/Recover (i.e during open database) you can have suprise if DB_CREATE_FILE_DEST is not set.

So, I recommend you set DB_CREATE_FILE_DEST  between   after RECOVER DATABASE and before “OPEN RESETLOGS”  or set  DB_CREATE_ONLINE_LOG_DEST_n, because during “open resetlogs” Oracle will create Online Logs files if it does not exists.

Online Logs (Redo) Behavior

 When OMF files are restored, they follow an order of precedence. That order is:

  1. If the original file exists, RMAN will use the original filename for restore.
  2. If the DB_CREATE_ONLINE_LOG_DEST_n  is set and original filename does not exist, RMAN will use the diskgroup name specified in DB_CREATE_ONLINE_LOG_DEST_n
  3. If no DB_CREATE_ONLINE_LOG_DEST_n  is set and the original file does not exist  but  DB_CREATE_FILE_DEST and DB_RECOVERY_FILE_DEST  (FRA) is set, RMAN will use the diskgroup name specified in both (DB_CREATE_FILE_DEST & DB_RECOVERY_FILE_DEST)  to multiplex ONLINELOG.
  4. If no DB_CREATE_ONLINE_LOG_DEST_n and DB_RECOVERY_FILE_DEST   is set and the original file does not exist  but  DB_CREATE_FILE_DEST is set,  RMAN will use the diskgroup name specified in DB_CREATE_FILE_DEST.
  5. If no DB_CREATE_ONLINE_LOG_DEST_n and DB_CREATE_FILE_DEST   is set and the original file does not exist  but  DB_RECOVERY_FILE_DEST is set,  RMAN will use the diskgroup name specified in DB_RECOVERY_FILE_DEST.
  6. If no DB_CREATE_ONLINE_LOG_DEST_n, DB_CREATE_FILE_DEST and  DB_RECOVERY_FILE_DEST is set and the original file does not exist, Oracle will raise “ORA-19801: initialization parameter DB_RECOVERY_FILE_DEST is not set” during “alter database open resetlogs“.
Hope this help


Why Use Parallel Processing?

This demonstration compares the use of parallel and serial processing for the same SQL query.
This demonstration is associated with lessons 2 and 3 of the Oracle University seminar: Parallel Processing in Oracle 11g.


How to Migrate a Microsoft SQL Server Database to Oracle Database 11g


What Is SQL Developer?

Oracle SQL Developer is a free graphical tool that enhances productivity and simplifies database development tasks. Using Oracle SQL Developer, you can browse database objects, run SQL statements, edit and debug PL/SQL statements and run reports, whether provided or created.

Microsoft SQL Server Migration Overview

Using Oracle SQL Developer Migration Workbench, you can quickly migrate your third-party database to Oracle.

There are four main steps in the database migration process:


Capture the Source Database The first step is to capture a “snapshot” of the Microsoft SQL Server database. This can be done in two ways.

  1. Online Capture: This requires creating a connection in SQL Developer to a live Microsoft SQL Server database. Using JDBC, the Microsoft SQL Server database metadata can be accessed and the Capture Model created.
  2. Offline Capture: This involves BCP scripts generated by SQL Developer to extract the Microsoft SQL Server database metadata to files. These files can then be “captured” by SQL Developer to create the Captured Model.

The second method is what you will perform in this tutorial.

Using SQL Developers Offline Capture feature, the Microsoft SQL Server Northwind sample database has been extracted into offline data files. The SQLServer2005.ocp file generated by the Capture tool contains the database schema information for the Microsoft SQL Server Northwind Traders database. Oracle SQL Developer Migration Workbench uses this file as the basis for building a representation of the structure of the source Microsoft SQL Server database. This structure is called the Captured Model.

Convert the Captured Database Oracle SQL Developer Migration Workbench uses the Captured Model to convert the captured objects to Oracle-format objects, building up a representation of the structure of the destination database. This structure is called the Converted Model.
Generate the Oracle Database Oracle SQL Developer Migration Workbench generates DDL statements to create the new Oracle database, based upon the Converted Model objects. Running the DDL statements will result in the creation of the objects in the Oracle database.
Migrate the Data The last step in the process is to migrate the data. You can do this in one of two ways.

  1. Online Data Move: You can create a connection from within Oracle SQL Developer to your Microsoft SQL Server source database and migrate the data.
  2. Offline Data Move: You can export the data from Microsoft SQL Server. SQL Developer</font> will create a series of BCP and sqlldr files that you can run from a batch file.

The second method is what you will perform in this tutorial.

In this tutorial, the required scripts for the offline migration have already been generated and modified. If you do not have time to perform this tutorial, you can also view the offline method, click here.

To view the steps for the online method, click here.

See link below




EMC CLARiiON: Oracle Database 10g/11g/11gR2 with Storage Replication Consistency


This white paper is intended of database and system administrators interested in implementing backup and remote disaster protection plan on Linux and Windows Plataform for Oracle databases using the consistenty features of EMC CLARiiON SnapView nad MirrorView/S. The reader should be familiar with Oracle Database software and ASM and EMC CLARiion SnapView and MirrorView replication  technologies.

Click link below…



Hints and Tips for installing Oracle Database 10g ( and up) and 11g on IBM System x3950 M2 with NUMA enabled

IBM System x3950 M2 utilizes eX4 technology which is primarily designed for but not limited to three major workloads: database servers, server consolidation using virtualization services, and Enterprise Resource Planning (ERP) servers.

The eX4 design consists of two 4U 4-socket rack-optimized chassis (available on the System x3850 M2 and x3950 M2 systems) with the ability to scale up to 16 sockets and up to 96 cores.

We will use “x3950 M2” for the remainder of this document, when referring to the IBM System x3950 M2 hardware.

By adding a ScaleXpander Option Kit, two (or more) x3950 M2 chassis can be configured into a multi-node NUMA capable x3950 M2.

In order to set up x3950 M2 with NUMA, two or more x3950 M2 chassis are required.

Each chassis serves as a single NUMA node.

The steps are:

1. Set up multimode configuration

2. Install NUMA-enabled Operating System

3. Install Oracle or above

This white paper is meant to increase the readers’ understanding of how Oracle Database and above and 11g can be deployed utilizing NUMA on an x3950 M2.

The example shown in this paper has not been extensively tested in a production environment.

Click link below..

Hints and TIps on Installing on IBM x3950 M2 with NUMA_v7 January 17 2011



RMAN Performance Tuning / Notes – My Oracle Support (Metalink)

I’ll post here useful notes from MOS about the RMAN Performance and Tuning.

To view these documents you must have access to the MOS.


RMAN Myths Dispelled: Common RMAN Performance Misconceptions [ID 134214.1]

This document will help dispel some of the common misconceptions related to proper usage of Recovery Manager (RMAN).




RMAN Restore Database Slow / How To Improve RMAN Restore Performance [ID 467694.1]

Goal: RMAN Restore database is slow.  Backup to tape takes 13 hours and the restore takes over 26hrs.



Advise On How To Improve Rman Performance [ID 579158.1]

Goal: How  to boost RMAN performance ?

Also is a recommended for improving RMAN performance on AIX5L based system. IBM suggestions the AIX related advices



RMAN Performance Tuning Diagnostics [ID 311068.1]

Provide diagnostic reference for collecting RMAN backup performance diagnostic information.



RMAN Performance Tuning Using Buffer Memory Parameters [ID 1072545.1]

The purpose of RMAN performance tuning is to identify the bottlenecks for a given backup or restore job and use RMAN commands, initialization parameters, or adjustments to physical media to improve overall performance.




RMAN: Monitoring Recovery Manager Jobs [ID 144640.1]

To inform all the Oracle Recovery Manager Documentation has the information to resolve most problems.

All RMAN users should review the RMAN Troubleshooting section of the online documentation provided.



Performance benefits of IBM Storwize V7000 with Easy Tier for Oracle 11g workload

This paper demonstrates the performance benefits that IBM® Easy Tier™ provides by seamlessly migrating hot extents from hard disk drives (HDDs) to a higher performing solid-state drives within theIBM Storwize V7000 solution.

This might be either to internal solid-state drives in the IBM Storwize V7000or to external storage systems that are virtualized by IBM Storwize V7000.

The other load generator tool that has been used here is Oracle Vdbench. The objective of Vdbench is togenerate a wide variety of controlled storage I/O workloads, allowing control over workload parameterssuch as I/O rate, logical unit number (LUN) or file sizes, transfer sizes, thread count, volume count,volume skew, read/write ratios, read and write cache hit percentages, and random or sequentialworkloads.

The other load generator used to arrive at the configuration guidelines is the Oracle I/O Calibration ToolORION) calibration tool.

This tool generates I/O using the same I/O software stack used by the Oracle server software without having to install the server software and create a database. It can simulatevarious workload types at different load levels to arrive at performance metrics for input/output operationsper second (IOPS), and latency (response time). It can also simulate the effect of striping performed byAutomatic Storage management (ASM).

The intention of this paper is not to demonstrate the maximum possible I/O benchmark or performancenumber for the IBM Storwize V7000. Those benchmark and performance numbers are likely to be shownin the Storage Performance Council SPC-1 and SPC-2 results posted by IBM on the SPC website. Thispaper demonstrates how to configure Easy Tier, and explains how Easy Tier might benefit theperformance for an Oracle database workload by optimizing the utilization of solid-state drives.

See link below…

EasyTier for Oracle



Get every new post delivered to your Inbox.

Join 276 other followers