Flash Recovery Area - Disable during recovery

Introduction

Flash Recovery Area (FRA) is one my favorite features in the Oracle Database. The FRA helps in automatic management of archive log files, RMAN backup pieces, control file autobackups and the flash back log files (if the FLASHBACK DATABASE is turned on). The FRA nicely creates all these files with well defined sub directory structure (based on dates) and also automatically deletes obsolete backup pieces (if the RMAN recovery window is set).

The other day, I was tasked with creating a clone of an Oracle 11g R1 (11.1.0.7) production database using the RMAN restore and recovery commands on the destination host. As usual, i made a copy of the production database's parameter file and edited it as per the new database (clone) settings. I did NOT remove any existing parameters though and retained all parameters including the FRA parameters viz. DB_RECOVERY_FILE_DEST and DB_RECOVERY_FILE_DEST_SIZE

Problem

Our RMAN restore was not successful as just before the restore started, Oracle strangely deleted two datafiles (as per the alertlog) and then RMAN first created two empty datafiles with the same name which were deleted and then started the actual restore. These two datafiles were created years ago on the production database and they were online datafiles. Nothing wrong with them.


**FROM THE ALERT LOG***

Thu Mar 24 10:49:24 2011
Deleted Oracle managed file +ORADATA/prod_myhost_db/datafile/fints_index2.1887.709799705
Deleted Oracle managed file +ORADATA/prod_myhost_db/datafile/fints_index2.1906.723204215

**FROM THE RMAN LOG**

creating datafile file number=1630 name=+ORADATA/prod_myhost_db/datafile/fints_index2.1887.709799705
creating datafile file number=1649 name=+ORADATA/prod_myhost_db/datafile/fints_index2.1906.723204215

channel d1: starting datafile backup set restore
channel d1: specifying datafile(s) to restore from backup set
channel d1: restoring datafile 00030 to +ORADATA/prod_myhost_db/datafile/sales.1690.700575591
channel d1: restoring datafile 00063 to +ORADATA/prod_myhost_db/datafile/argus2_index2.263.700567185
channel d1: restoring datafile 00083 to +ORADATA/prod_myhost_db/datafile/bits_index2.1800.700576857
channel d1: restoring datafile 00112 to +ORADATA/prod_myhost_db/datafile/cbo_data2.1826.700576943
....
....
....

Solution

I got this working by disabling the FRA. I commented the two FRA parameters DB_RECOVERY_FILE_DEST and DB_RECOVERY_FILE_DEST_SIZE in the destination database's init parameter file. Then I recreated the +ORADATA ASM diskgroup (formatted the ASM disks to remove data from the other files that got restored so far) and restarted the restore. This time the restore was successful.

Conclusion

Turn off the FRA (remove/comment the DB_RECOVERY_FILE_DEST and DB_RECOVERY_FILE_DEST_SIZE) on the destination database until the cloning process is over (restore and recovery). Once the new database is opened successfully, the FRA can be turned on again.

Database Administrator is TOP 7 out of 100 BEST Jobs in America

CNN.com partnered with PayScale.com and come out with a list of the TOP 100 best jobs in America. The criteria for selection included great pay and growth prospects.

Guess what, Database Administrators are again in the TOP TEN. It is ranked 7th best out of the TOP 100 in USA.

Apart from Database Administrators, the only other IT job in the TOP TEN is Software Architect.

For a full list, click here.

Oracle DBA Training

Interested in Oracle DBA Training and Oracle Apps DBA (Oracle Applications DBA) Training ?. If anyone is interested in getting trained in these skills, please register for courses offered by DBA University.

All the courses are covered with the latest topics, practical assignments as well as access to a world class LAB environment along with job oriented training.

Both on-site and online training are available.

Database Administrator stands 5th in the 'MOST DIFFICULT TO FILL' IT Job

I wrote in March 2008 (just before the onset of the worst recession in USA) that statistics showed Oracle Database is the skill with the biggest demand in the IT Job Market. Click here to read that post.

With the job market in USA still not recovered fully, the latest July 2010 dice.com monthly job report says that Database Administrators stand 5th in the 'MOST DIFFICULT to FILL' IT Job. This shows that IT organizations continue to have a tough time to find quality Database Administrators even in the ongoing recession in USA.

The same report states that DBAs make $10,000 more than the average IT job paycheck in the United States. Same applies for pay raises. DBAs get double the average yearly pay raises in the USA. Click here to download the latest IT job market report.

CNN.com partnered with the leading job portal www.CareerBuilder.com and ranked DBAs among the TOP TEN most IN-DEMAND professions for the 2009-2016 period with a projected growth rate of 29% per year. Click here for more details.

Question on restoring SPFILE after a complete database server crash

Introduction

I am posting a database recovery related question asked by one my blog's visitors' last week for restoring an spfile. A few additional steps are necessary if the database is using ASM technology.

[06:19] sunil: i had a catalog db and i m taking the bkp of target db in to tape drive, all of a sudden my target server crashed(means i lost everything includibg spfile,now i want to built a new server and want to restore the bkp from tape drive,how it'll happen.

[06:19] sunil: how i can restore my spfile?

Sunil,

It is not very tough to restore the spfile. Please try the below commands to restore your spfile. For simplicity, I am making the following assumptions.

1) You have used RMAN and a seperate CATALOG database for your backup strategy.
2) You still know the DBID of your "crashed" database. If you don't have your backup logfile, you can still find the DBID by querying the RMAN Catalog. Let it be 123456789
3) You have made the tapes containing your backup accessible to the new server.

A)

rman
connect target /
connect catalog catuser/catuser@catdb
set DBID 123456789
startup force nomount;
exit


B)

rman
connect target /
connect catalog catuser/catuser@catdb
set DBID 123456789
run
{
allocate channel t1 type 'SBT_TAPE';
restore spfile;
}
exit

c)

sqlplus '/ as sysdba'
create pfile from spfile;
shutdown immediate;

D)

Restore CONTROLFILE and update PFILE with new controlfile location

E)
The following additional steps must be performed if you have ASM for your database.
Let +ASMDATA be the diskgroup name for storing the spfile.

sqlplus '/ as sysdba'
create spfile='+ASMDATA' from pfile;
exit

cd $ORACLE_HOME/dbs
mv pfile pfile.old

Create a new pfile with the below contents after verifying the exact name of the restored spfile from v$asm_alias view
SPFILE='+ASMDATA/DBNAME/parameterfile/spfile_name_in_asm_alias_view'

rm the spfile on disk in $ORACLE_HOME/dbs directory.

The below command will startup the database using the above pfile which points to the actual SPFILE

sqlplus '/ as sysdba'
startup mount;

ASM for Oracle 11g with R12 E-Business Suite

Introduction

One of my visitors' posed a question in my weekly online chat in this site.

>> Tom asked

Hi Srinivas, we are planning to utilizing ASM 11gR2 DB for R12 ebusiness, is there any document that you can point me to for best practice for setting the ASM disks? Currently have only 2 disks; 1 is DATA and the other is RECOVERY. Data disk is for transaction and RECOVERY is for redo, archive log, etc. Can you point me to the right direction. thanks, Tom.

I am making a blogpost for this question because it is ASM is such an important consideration for today's enterprise databases, both for manageability and high availability of data.

Tom,

You have taken a good decision to implement ASM for your database. We had very good success with ASM and its also fun to work with ASM. Makes your life easy.

You should put even redo also as part of the DATA diskgroup.

1) DATA diskgroup = Datafiles + online redologs + controlfiles
2) RECOVERY diskgroup = just the archived redologs.

Are you using ASM on Linux platform ? ASM is optimized for Linux platform. Oracle supplies an utility called ASMLib for the Linux platform, an utility that further automates ASM management and administration.


Also when you create the diskgroup, I prefer you to use "EXTERNAL REDUNDANCY". Your organization must already be using SAN Fabric for your database. For example, if you use EMC Symmetrix as the SAN solution, you must utilize its inbuilt redundancy feature. So you let EMC handle redundancy instead of Oracle.

RMAN Active Duplicate Advantages

Introduction

We are planning to start upgrading some of our enterprise Oracle databases from 10g to 11g and I am evaluating the new features of Oracle 11g that our organization can be benefited from. A 11g new feature that has caught my attention is the 'ACTIVE Database Duplicate' option in Oracle RMAN. This feature allows a database to be duplicated directly from its live source database instead of using its backup.

Advantages

Instant Database Clones possible (On Demand Cloning)

If one requires a database clone, all one has to do is to run the active database duplicate command after specifying the source and destination databases. I call this Cloning onDemand ! This helps organizations create test database environments in a rapid and simpler fashion.

EMC TimeFinder BCV sync/split is not necessary

Consider an organization relying on BCV sync/split technology to perform database clones. In this case one must take the help of a system administrator to perform the BCV sync/split operation. A database is put in hotbackup mode and the BCV split process is performed. Once the operation is completed, the database is taken out of backup mode. This is all additional work and the Active Database Duplication eliminates all these steps as it performs a direct copy.

Staging ASM Diskgroup is not required

Consider the case when ASM (Automatic Storage Management) is used as the underlying disk management technology for an Oracle database. An ASM-2-ASM database clone always requires a consistent BCV sync/split operation and this during process the ASM disk headers are copied to the destination host. This requires staging (intermediate) ASM Diskgroups on the destination host with the same name as that of the source database's ASM diskgroups. After the consistent BCV sync/split is completed, data is again copied from the staging ASM diskgroups to the clone database's diskgroups(DEV/TEST diskgroup names). Active Database Duplication eliminates the need for staging ASM diskgroups, thus saving a lot of diskspace (equal to the total size of the database being cloned)

No additional space required

In the traditional backup-based database duplication, one has to make the backups accessible to the destination host before the duplication is started. For tape backups, this equates to moving the tape to a drive attached to the destination host or using a network-accessible tape server. For disk backups, this equates to copying the backup to the destination host (additional space). Active Database Duplication does not require backups and hence the additional disk space requirement is no longer needed on the destination host.

Restrictions

Incomplete recovery NOT possible (no SET UNTIL)

Active Database Duplication cannot be used to duplicate a database until a point in time. One has to rely on the backup-based database duplication method if one wants to create a duplicate database until a specific point in time ( a specific time, log sequence or SCN). Furthermore, active duplicate copies data only until the last archived log file in the source database. The contents of the online redologs will not be copied to the duplicate database.