RMAN-05001 :Auxiliary Filename Conflicts with a File Used by the Target Database

If we lookup the cause of error   RMAN :05001 we find that this error has occur because  RMAN is attempting to use the specified file name as a restore destination in the auxiliary database, but this name is already in use by the target database. 

If we are experiencing RMAN-05001: auxiliary filename conflicts with a file used by the target database, then we may be confused as I was about how to proceed. If we are converting ALL files from various locations to new locations, then use db_file_name_convert. If we want to retain the exact same locations with no conversion, we use NOFILENAMECHECK. But what if we want to convert some, and not convert others? I was not sure whether NOFILENAMECHECK would then override db_file_name_convert, but it turns out that we can use both in concert. For those files that are in the same location, it will not complain, but the others that we want to convert will be converted properly from the db_file_name_convert entries in our pfile.

Enjoy   :-) 

Difference Between Dataguard and Standby

There is common confusion about oracle standby database and dataguard. I found many people who consider dataguard and standby are same . Here are the difference between Dataguard and standby : 

Dataguard  :  Dataguard is mechanism/tool to maintain standby database. Data Guard provides a comprehensive set of services that create, maintain, manage, and monitor one or more standby databases to enable production Oracle databases to survive disasters and data corruptions. Data Guard maintains these standby databases as copies of the production database. Then, if the production database becomes unavailable because of a planned or an unplanned outage, Data Guard can switch any standby database to the production role, minimizing the downtime associated with the outage. 

The dataguard is set up between primary and standby instance .DataGuard can manage both physical and logical standby.  DataGuard requires the enterprise edition, while we could write our own standby scripts using the standard edition. we have got support and testing issues-- if something goes wrong, we are on our own to debug our scripts. If we are using DataGuard, there is a variety of documentation available and a variety of options for support. Oracle has also invested quite a bit of time testing DataGuard with various failure scenarios-- it's likely that we wouldn't have that much time to test our own scripts. On a day-to-day basis, DataGuard provides integration with various monitoring utilities (i.e. Enterprise Manager) and provides a rather nice set of tables to view information about what's going on. DataGuard also provides functionality like the ability to automatically detect and resolve gaps in the archived log files that your scripts would not likely be written to handle. And, of course, our scripts could only manage a physical standby database. 

Data Guard was originally a set of scripts, but now is the entire environment including a set of processes that control the extraction of redo (directly from log bugger, from redo logs or archive redo logs) from the primary, shipping to the standby, ensuring that the logs are applied. Data Guard processes also include the mechanics needed to make the standby database active automatically (failover) or manually (switchover) and also to re-sync and make the original database active again (switchback).

All that said, Data Guard is only available on Enterprise Edition. Standby capability is available on Standard Edition. And there are commercial products around that provide capability similar to Data Guard for Standard Edition. For more detail click here

Standby Database :  Physical standby database provides a physically identical copy of the primary database, with on disk database structures that are identical to the primary database on a block-for-block basis. The database schema, including indexes, are the same. A physical standby database is kept synchronized with the primary database, though Redo Apply, which recovers the redo data received from the primary database and applies the redo to the physical standby database.   A physical standby database can be used for business purposes other than disaster recovery on a limited basis.

Enjoy     :-)

Automatic Workload Repository (AWR) in Oracle

Oracle have provided many performance gathering and reporting tools over the years. Originally the UTLBSTAT/UTLESTAT scripts were used to monitor performance metrics. Oracle8i introduced the Statspack functionality which Oracle9i extended. In Oracle 10g statspack has evolved into the Automatic Workload Repository (AWR).The AWR is a repository of performance information collected by the database to aid in the tuning process for DBAs.

Oracle 10g uses a scheduled job, GATHER_STATS_JOB, to collect AWR statistics. This job is created, and enabled automatically when we create a new Oracle database. We can disable and enable the schedule job by following command:

we can disable this job by using the dbms_scheduler.disable procedure as below :

Exec dbms_scheduler.disable(’GATHER_STATS_JOB’);

And we can enable the job using the dbms_scheduler.enable procedure as below :

Exec dbms_scheduler.enable(’GATHER_STATS_JOB’);

AWR consists of a collection of performance statistics including :

• Wait events used to identify performance problems.
• Time model statistics indicating the amount of DB time associated with a process from the v$sess_time_model and v$sys_time_model views.
• Active Session History (ASH) statistics from the v$active_session_history view.
• Some system and session statistics from the v$sysstat and v$sesstat views.
• Object usage statistics.
• Resource intensive SQL and PL/SQL.

The resource intensive SQL and PL/SQL section of the report can be used to focus tuning efforts on those areas that will yield the greatest returns.  The statements are ordered by several criteria including :

• SQL ordered by Elapsed Time
• SQL ordered by CPU Time
• SQL ordered by Gets
• SQL ordered by Reads
• SQL ordered by Executions
• SQL ordered by Parse Calls
• SQL ordered by Sharable Memory

Several of the automatic database tuning features require information from the AWR to function correctly, including:

• Automatic Database Diagnostic Monitor
• SQL Tuning Advisor
• Undo Advisor
• Segment Advisor

 How to generate AWR report ?  

There are two scripts that are provided by oracle to generate the AWR report. The scripts are available in the directory  $ORACLE_HOME\rdbms\admin. The two scripts are 

1.) awrrpt.sql   : If we have only One Oracle Database then run awrrpt.sql sql script.

2.) awrrpti.sql  : If we have more than One Oracle Instance (Like RAC) then run awrrpti.sql script so that we can particular instance for awr report creation.

By default snapshots of the relevant data are taken every hour and retained for 7 days. The default values for these settings can be altered using the below procedure :

BEGIN

  DBMS_WORKLOAD_REPOSITORY.modify_snapshot_settings(

    retention => 43200,        -- Minutes (= 30 Days). Current value retained if NULL.

    interval  => 15);          -- Minutes. Current value retained if NULL.

END;

/

                  Here we have  alter the snapshot interval to 15min. It is recommended that 15 Minutes is enough in two snapshot for better performance bottleneck.

AWR using Enterprise Manager    :   The automated workload repository administration tasks have been included in Enterprise Manager. The "Automatic Workload Repository" page is accessed from the main page by clicking on the "Administration" link, then the "Workload Repository" link under the "Workload" section. The page allows us to modify AWR settings or manage snapshots without using the PL/SQL APIs.

Here is the Demo of the AWR report .

C:\>sqlplus sys/xxxx@orcl as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Thu Jun 16 11:42:19 2011

Copyright (c) 1982, 2010, Oracle.  All rights reserved.

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> @D:\app\Neerajs\product\11.2.0\dbhome_1\RDBMS\ADMIN\awrrpt.sql

Current Instance

~~~~~~~~~~~~~~~~

   DB Id                       DB Name        Inst Num          Instance

-----------                      ------------           --------          ------------

 1281052636                ORCL                1                    orcl

Specify the Report Type

~~~~~~~~~~~~~~~~~~~~~~~

Would you like an HTML report, or a plain text report?

Enter 'html' for an HTML report, or 'text' for plain text

Defaults to 'html'

Enter value for report_type: HTML

Type Specified:  html

Instances in this Workload Repository schema

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

   DB Id                        Inst Num       DB Name        Instance           Host

------------                      --------           ------------        ------------        ------------

* 1281052636                1                   ORCL              orcl                xxxx

Using 1281052636 for database Id

Using          1 for instance number

Specify the number of days of snapshots to choose from

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Entering the number of days (n) will result in the most recent

(n) days of snapshots being listed.  Pressing <return> without

specifying a number lists all completed snapshots.

Enter value for num_days:       (Press Enter to see all the snapshots)

Listing all Completed Snapshots

                                                       

Instance     DB Name        Snap Id       Snap Started               Level

---------       ------------         ---------         ------------------              -----

orcl             ORCL                 1             08 Jun 2011 11:30          1

                                               3             08 Jun 2011 14:41         1

                                               4             08 Jun 2011 15:30         1

                                 .

 Data is truncated

         .            

                                                120           16 Jun 2011 05:30       1

                                                121           16 Jun 2011 06:30       1

                                                122            16 Jun 2011 07:30      1

                                                123            16 Jun 2011 08:30      1

                                                124            16 Jun 2011 09:30      1

                                                125            16 Jun 2011 10:30      1

                                                126            16 Jun 2011 11:30      1

Specify the Begin and End Snapshot Ids

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Enter value for begin_snap: 125

Begin Snapshot Id specified: 125

Enter value for end_snap: 126

End   Snapshot Id specified: 126

Specify the Report Name

~~~~~~~~~~~~~~~~~~~~~~~

The default report file name is awrrpt_1_125_126.html.  To use this name,

press <return> to continue, otherwise enter an alternative.

Enter value for report_name:          (Press enter if you want to use the above name)

Using the report name awrrpt_1_125_126.html

.

.

Report is truncated 

.

.

End of Report

</body></html>

Report written to awrrpt_1_125_126.html

SQL>

In the above report the line which are shaded with red colour are the entered values when it prompts.

Enjoy     :-)