Flash/Fast Recovery Area (FRA)

The flash recovery area is the most powerful tool available from Oracle 10^g, that plays a vital role in performing database backup & recovery operations. From 11g release2, flash recovery area is called as fast recovery area.

Flash Recovery Area can be defined as a single, centralized, unified storage area that keep all the database backup & recovery related files and performs those activities in Oracle databases.

Unified Backup Files Storage, all backup components can be stored in one consolidated spot. The flash recovery area is managed via Oracle Managed Files (OMF), and it can utilize disk resources managed by Automatic Storage Management (ASM). Flash recovery area can be configured for use by multiple database instances.

Automated Disk-Based Backup and Recovery, once the flash recovery area is configured, all backup components are managed automatically by Oracle.

Automatic Deletion of Backup Components, once backup components have been successfully created, RMAN can be configured to automatically clean up files that are no longer needed (thus reducing risk of insufficient disk space for backups).

Disk Cache for Tape Copies, if your disaster recovery (DR) plan involves backing up to alternate media, the flash recovery area can act as a disk cache area for those backup components that are eventually copied to tape.

Flashback Logs, the FRA is also used to store and manage flashback logs, which are used during flashback backup operations to quickly restore a database to a prior desired state.

You can designate the FRA as the location for one of the control files and redo log members to limit the exposure in case of disk failure.

In case of a media failure or a logical error, the flash recovery area is referred to retrieve all the files needed to recover a database.

Following are the various entities that can be considered as FRA:

File System:
1. A single directory
2. An entire file system

Raw Devices:
1. Automatic storage management (ASM)

FRA Components

The flash/fast recovery area can contain the following:

• Control files: During database creation, a copy of the control file is created in the flash recovery area.
• Online redologs: Online redologs can be kept in FRA.
• Archived log files: During the configuration of the FRA, the LOG_ARCHIVE_DEST_10 parameter in init.ora file is automatically set to the flash recovery area location. Archived log files are created by ARCn processes in the flash recovery area location and the location defined by LOG_ARCHIVE_DEST_n.
• Flashback logs: Flashback logs are kept in the flash recovery area when flashback database is enabled.
• Control file and SPFILE backups: The flash recovery area also keeps the control file and SPFILE backups, which is automatically generated by Recovery Manager (RMAN) only if RMAN has been configured for control file autobackup.
• Datafile copies: The flash recovery area also keeps the datafile copies.
• RMAN backup sets: The default destination of backup sets and image copies generated by RMAN is the flash recovery area.

Notes:

• The FRA is shared among databases in order to optimize the usage of disk space for database recovery operations.
• Before any backup and recovery activity can take place, the Flash Recovery Area must be set up. The flash recovery area is a specific area of disk storage that is set aside exclusively for retention of backup components such as datafile image copies, archived redo logs, and control file auto backup copies.
• RMAN also transfers the restored archive files from tape to the flash recovery area in order to perform recovery operations.

Configuring FRA

Following are the three initialization parameters that should be defined in order to set up the flash recovery area:

• DB_RECOVERY_FILE_DEST_SIZE
• DB_RECOVERY_FILE_DEST
• DB_FLASHBACK_RETENTION_TARGET

DB_RECOVERY_FILE_DEST_SIZE specifies the total size of all files that can be stored in the Flash Recovery Area. The size of the flash recovery area should be large enough to hold a copy of all data files, all incremental backups, online redo logs, archived redo log not yet backed up on tape, control files, and control file auto backups.

SQL> ALTER SYSTEM SET db_recovery_file_dest_size = 10g SCOPE = BOTH;

DB_RECOVERY_FILE_DEST parameter is to specify the physical location where all the flash recovery files are to be stored. Oracle recommends that this be a separate location from the datafiles, control files, and redo logs.

SQL> ALTER SYSTEM SET db_recovery_file_dest = '/OFR1' SCOPE = BOTH;

If the database is using Automatic Storage Management (ASM) feature, then the shared disk area that ASM manages can be targeted for the Flashback Recovery Area.

SQL> ALTER SYSTEM SET db_recovery_file_dest = '+dgroup1' SCOPE = BOTH;

The DB_RECOVERY_FILE_DEST_SIZE and DB_RECOVERY_FILE_DEST are defined to make the flash recovery area usable without shutting down and restarting the database instance i.e. these two parameters are dynamic.

SQL> ALTER SYSTEM SET db_flashback_retention_target = 1440 SCOPE = BOTH;

Notes:

• DB_RECOVERY_FILE_DEST_SIZE is defined before DB_RECOVERY_FILE_DEST in order to define the size of the flash recovery area.
• If the value specified in the DB_RECOVERY_FILE_DEST parameter is cleared then as a result the flash recovery area is disabled.
• DB_RECOVERY_FILE_DEST_SIZE parameter cannot be cleared up prior to the DB_RECOVERY_FILE_DEST parameter.

The flash recovery area can be created and maintained using Oracle Enterprise Manager Database Control.

Enabling Flashback

SQL> alter database flashback on;

The database must be in archive log mode to enable flashback.
Configuring Online Redolog Creation in Flash Recovery Area
To store online redologs in FRA, you have to set DB_CREATE_ONLINE_LOG_DEST_1 (OMF init parameter) to FRA location and create the online log groups/members.

The initialization parameters that determine where online redolog files are created are DB_CREATE_ONLINE_LOG_DEST_n, DB_RECOVERY_FILE_DEST and DB_CREATE_FILE_DEST.

Configuring Control File Creation in Flash Recovery Area
To store control file in FRA, you have to set CONTROL_FILES parameter to FRA location.

The initialization parameters CONTROL_FILES, DB_CREATE_ONLINE_LOG_DEST_n, DB_RECOVERY_FILE_DEST and DB_CREATE_FILE_DEST all interact to determine the location where the database control files are created.

Configuring Archived Redolog Creation in Flash Recovery Area
If Archive log mode is enabled and LOG_ARCHIVE_DEST & DB_RECOVERY_FILE_DEST are not set, then the archive logs will be generated in $ORACLE_HOME/dbs directory.

If LOG_ARCHIVE_DEST is set & DB_RECOVERY_FILE_DEST is not set, then the archive logs will be generated at LOG_ARCHIVE_DEST path.

If you enable FRA (DB_RECOVERY_FILE_DEST is set), then the archive log files will be generated in FRA, and it will ignore the LOG_ARCHIVE_DEST and LOG_ARCHIVE_FORMAT i.e. FRA will follow its own naming convention. The generated filenames for the archived redologs in the flash recovery area are Oracle Managed Filenames and are not determined by LOG_ARCHIVE_FORMAT.

It is recommended to use flash recovery area as an archived log location because the archived logs are automatically managed by the database. Whatever archiving scheme you choose, it is always advisable to create multiple copies of archived logs.

You can always define a different location for archive redo logs, if you use a different location, then you can’t just erase the values of the parameters for LOG_ARCHIVE_DEST and LOG_ARCHIVE_DUPLEX_DEST in order to specify the location of the FRA.

To place your log files somewhere else other than the FRA you should use a different parameter to specify the archived redo log locations: use LOG_ARCHIVE_DEST_1 instead of LOG_ARCHIVE_DEST.

Suppose log_archive_dest was set to ‘+arc_disk3′, you can use LOG_ARCHIVE_DEST_1 to specify the same location for the archived redologs.
Query the parameter to verify its current value:
SQL> show parameter log_archive_dest
SQL> show parameter log_archive_dest_1
SQL> alter system set log_archive_dest_1=’location=+arc_disk3′ scope=both;
SQL> alter system set log_archive_dest=” scope=both;

Managing Flash/Fast Recovery Area

As the DB_RECOVERY_FILE_DEST_SIZE parameter specifies the space for the flash recovery area. In a situation when the space does not prove enough for all flash recovery files, then in such a case Oracle itself keeps track of those files that are not required on the disk. These unnecessary files are then deleted to resolve the space issue in the flash recovery area.

Whenever a file is deleted from the flash recovery area, a message is written in the alert log.

There are various other circumstances in which messages are written in the alert log:
1. When none of the files are deleted.
2. When the used space in the FRA is 85 percentage (a warning).
3. When the used space in the FRA is 97 percentage (a critical warning).
4. The warning messages issued can be viewed in the DBA_OUTSTANDING_ALERTS data dictionary view and are also available in the OEM Database Control main window.

To recover from these alerts, a number of steps can be taken as remedial options:
1. Adjust the retention policy to keep fewer copies of data files.
In case the retention policy is sounds good, then the steps taken to recover from the alerts are:

• More disk space should be added.
• Backup some of the flash recovery files to another destination such as another disk or tape drive.

2. Reduce the number of days in the recovery window

RMAN files creation in the Flash Recovery Area

This section describes RMAN commands or implicit actions (such as control file auto backup) that can create files in the flash recovery area, and how to control whether a specific command creates files there or in some other destination. The assumption in all cases is that a flash recovery area has already been configured for your database. The commands are:

· BACKUP
Do not specify a FORMAT option to the BACKUP command, and do not configure a FORMAT option for disk backups. In such a case, RMAN creates backup pieces and image copies in the flash recovery area, with names in Oracle Managed Files name format.

· CONTROLFILE AUTOBACKUP
RMAN can create control file autobackups in the flash recovery area. Use the RMAN command CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK CLEAR to clear any configured format option for the control file autobackup location on disk. Control file autobackups will be placed in the flash recovery area when no other destination is configured.

· RESTORE ARCHIVELOG
Explicitly or implicitly (as in the case of, set one of the LOG_ARCHIVE_DEST_n) parameters to 'LOCATION=USE_DB_RECOVERY_FILE_DEST'. If you do not specify SET ARCHIVELOG DESTINATION to override this behavior, then restored archived redo log files will be stored in the flash recovery area.

· RECOVER DATABASE or TABLESPACE, BLOCKRECOVER, and FLASHBACK DATABASE
These commands restore archived redo logs from backup for use during media recovery, as required by the command. RMAN restores any redo log files needed during these operations to the flash recovery area, and delete them once they are applied during media recovery.

To direct the restored archived redo logs to the flash recovery area, set one of the LOG_ARCHIVE_DEST_n parameters to 'LOCATION=USE_DB_RECOVERY_FILE_DEST", and make sure you are not using SET ARCHIVELOG DESTINATION to direct restored archived logs to some other destination.

You can use RMAN to remove old archivelog:
$ rman target=/
RMAN> delete noprompt archivelog all;
RMAN> delete noprompt backup of database;
RMNA> delete noprompt copy of database;

Resolving full Flash Recovery Area

You have a number of choices on how to resolve full flash/fast recovery area when there are no files eligible for deletion:

• Make more disk space available, and increase DB_RECOVERY_FILE_DEST_SIZE to reflect the new space.
• Move backups from the flash recovery area to a tertiary device such as tape. One convenient way to back up all of your flash recovery area files to tape at once is the BACKUP RECOVERY AREA command.

After you transfer backups from the flash recovery area to tape, you can resolve the full recovery area condition by deleting files from the flash recovery area, using forms of the RMAN DELETE command.

Note:

• Flashback logs cannot be backed up outside the flash recovery area. Therefore, in a BACKUP RECOVERY AREA operation the flashback logs are not backed up to tape.
• Flashback logs are deleted automatically to satisfy the need for space for other files in the flash recovery area. However, a guaranteed restore point can force the retention of flashback logs required to perform Flashback Database to the restore point SCN. See
• Delete unnecessary files from the flash recovery area using the RMAN DELETE command. (Note that if you use host operating system commands to delete files, then the database will not be aware of the resulting free space. You can run the RMAN CROSSCHECK command to have RMAN re-check the contents of the flash recovery area and identify expired files, and then use the DELETE EXPIRED command to remove missing files from the RMAN repository.)

You may also need to consider changing your backup retention policy and, if using Data Guard, consider changing your archivelog deletion policy.

Changing the Flash Recovery Area to a new location

If you need to move the flash recovery area of your database to a new location, you can follow this procedure:

1. Change the DB_RECOVERY_FILE_DEST initialization parameter.
SQL> ALTER SYSTEM SET DB_RECOVERY_FILE_DEST='+disk1' SCOPE=BOTH SID='*';
2. After you change this parameter, all new flash recovery area files will be created in the new location.
3. The permanent files (control files and online redo log files), flashback logs and transient files can be left in the old flash recovery area location. The database will delete the transient files from the old flash recovery area location as they become eligible for deletion.

Oracle will clean up transient files remaining in the old flash recovery area location as they become eligible for deletion.

In Oracle Database 11g, a new feature introduced i.e. Flashback Data Archive - flashback will make use of flashback logs, explicitly created for that table, in FRA, will not use undo. Flashback data archives can be defined on any table/tablespace. Flashback data archives are written by a dedicated background process called FBDA so there is less impact on performance. Can be purged at regular intervals automatically.
Related views
V$RECOVERY_FILE_DEST
V$FLASH_RECOVERY_AREA_USAGE
V$DBA_OUTSTANDING_ALERTS
V$FLASHBACK_DATABASE_LOGFILE

Automatic Workload Repository (AWR)

The AWR collect and stores database statistics relating to problem detection and tuning. AWR is a replacement for the statspack utility which helps gather database performance statistics. AWR generates snapshots of the key performance data, such as system and session statistics, segment-usage statistics, time model statistics, high load statistics and stores in the sysaux.

AWR provides statistics in 2 formats

·         Temporary – in memory collection of statistics in the SGA, accessible via the V$ views.

·         Persistent – type of performance data in the form of regular AWR snapshots which you access via DBA_views.

The MMON process is responsible for collecting the statistics in the SGA and saving them to the sysaux tablespaces.

The AWR is used to collect 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 statements.

Some of the Important Views and tables regarding to AWR

The following workload repository views are available:

• V$ACTIVE_SESSION_HISTORY - Displays the active session history (ASH) sampled every second.
• V$METRIC - Displays metric information.
• V$METRICNAME - Displays the metrics associated with each metric group.
• V$METRIC_HISTORY - Displays historical metrics.
• V$METRICGROUP - Displays all metrics groups.
• V$SYS_TIME_MODEL – time model stats (db time, java execution time, pl/sql execution time,etc.,)
• V$OSSTAT – operating system stats(avg busy tick, avg idle ticks, etc)
• V$SERVICE_STATS - wait statistics(db cpu, app wait time, user commits, etc)
• V$SESSTAT – session stats
• V$SYSSTAT – system stats
• DBA_HIST_ACTIVE_SESS_HISTORY - Displays the history contents of the active session history.
• DBA_HIST_BASELINE - Displays baseline information.
• DBA_HIST_DATABASE_INSTANCE - Displays database environment information.
• DBA_HIST_SNAPSHOT - Displays snapshot information.
• DBA_HIST_SQL_PLAN - Displays SQL execution plans.
• DBA_HIST_WR_CONTROL - Displays AWR settings.

Generating, Modifying And Droping The Snapshots

Creating of Snapshots

Extra snapshots can be taken and existing snapshots can be removed using:

EXEC DBMS_WORKLOAD_REPOSITORY.create_snapshot;

Modifying an existing snapshot

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:

BEGIN

  DBMS_WORKLOAD_REPOSITORY.modify_snapshot_settings(

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

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

END;

/

The changes to the settings are reflected in the DBA_HIST_WR_CONTROL view. Typically the retention period should capture at least one complete workload cycle. If you system has monthly archive and loads a 1 month retention time would be more beneficial that the default 7 days. An interval of "0" switches off snapshot collection, which in turn stops much of the self-tuning functionality, hence this is not recommended. Automatic collection is only possible if the STATISTICS_LEVEL parameter is set to TYPICAL or ALL. If the value is set to BASIC manual snapshots can be taken, but they will be missing some statistics.

Droping The Snapshots

BEGIN

  DBMS_WORKLOAD_REPOSITORY.drop_snapshot_range (

    low_snap_id  => 22,

    high_snap_id => 32);

END;

/

Workload Repository Reports

Oracle provide two scripts to produce workload repository reports (awrrpt.sql and awrrpti.sql). They are similar in format to the statspack reports and give the option of HTML or plain text formats. The two reports give essential the same output but the awrrpti.sql allows you to select a single instance. The reports can be generated as follows:

@$ORACLE_HOME/rdbms/admin/awrrpt.sql

@$ORACLE_HOME/rdbms/admin/awrrpti.sql

The scripts prompt you to enter the report format (html or text), the start snapshot id, the end snapshot id and the report filename. The resulting report can be opend in a browser or text editor accordingly.

Sample output for AWR reports

OUTPUT

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

WORKLOAD REPOSITORY report for

DB Name         DB Id    Instance     Inst Num Release     Cluster Host

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

DBDABR          37933856 dbdabr              1             10.1.0.2.0       NO      Host1

              Snap Id      Snap Time      Sessions Curs/Sess

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

Begin Snap:      2900 19-Aug-04 11:00:29        18       5.2

  End Snap:      2911 19-Aug-04 22:00:16        18       4.6

   Elapsed:              659.78 (mins)

   DB Time:               10.08 (mins)

Cache Sizes (end)

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

Buffer Cache:      48M      Std Block Size:         8K

Shared Pool Size:  56M          Log Buffer:       256K

Load Profile

~~~~~~~~~~~~          Per Second       Per Transaction

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

        Redo size: 1,766.20             18,526.31

    Logical reads:    39.21                411.30

    Block changes:    11.11                116.54

   Physical reads:     0.38                  3.95

  Physical writes:     0.38                  3.96

       User calls:     0.06                  0.64

           Parses:     2.04                 21.37

      Hard parses:     0.14                  1.45

            Sorts:     1.02                 10.72

           Logons:     0.02                  0.21

         Executes:     4.19                 43.91

About Load Profile in AWR Reports

This section gives a glimpse of the database workload activity that occurred within the snapshot interval. For example, the load profile below shows that an average transaction generates about 18K of redo data, and the database produces about 1.8K redo per second.

Load Profile

~~~~~~~~~~~~           Per Second       Per Transaction

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

        Redo size:       1,766.20             18,526.31

    Logical reads:          39.21                411.30

    Block changes:          11.11                116.54

   Physical reads:           0.38                  3.95

  Physical writes:           0.38                  3.96

       User calls:           0.06                  0.64

           Parses:           2.04                 21.37

      Hard parses:           0.14                  1.45

            Sorts:           1.02                 10.72

           Logons:           0.02                  0.21

         Executes:           4.19                 43.91

The above statistics give an idea about the workload the database experienced during the time observed. However, they do not indicate what in the database is not working properly. For example, if there are a high number of physical reads per second, this does not mean that the SQLs are poorly tuned.

Perhaps this AWR report was built for a time period when large DSS batch jobs ran on the database. This workload information is intended to be used along with information from other sections of the AWR report in order to learn the details about the nature of the applications running on the system.  The goal is to get a correct picture of database performance.

The following list includes detailed descriptions for particular statistics:

Redo size: The amount of redo generated during this report.

Logical Reads: Calculated as (Consistent Gets + DB Block Gets = Logical Reads).

Block changes: The number of blocks modified during the sample interval.

Physical Reads: The number of requests for a block that caused a physical I/O operation.

Physical Writes: Number of physical writes performed.

User Calls: Number of user queries generated.

Parses: The total of all parses; both hard and soft.

Hard Parses: The parses requiring a completely new parse of the SQL statement. These consume both latches and shared pool area.

Soft Parses: Soft parses are not listed but derived by subtracting the hard parses from parses. A soft parse reuses a previous hard parse; hence it consumes far fewer resources.

Sorts, Logons, Executes and Transactions: All self-explanatory.

Parse activity statistics should be checked carefully because they can immediately indicate a problem within the application. For example, a database has been running several days with a fixed set of applications, it should, within a course of time, parse most SQLs issued by the applications, and these statistics should be near zero.

If there are high values of Soft Parses  or especially Hard Parses statistics, such values should be taken as an indication that the applications make little use of bind variables and produce large numbers of unique SQLs. However, if the database serves developmental purposes, high vales of these statistics are not bad.

The following information is also available in the workload section:

  % Blocks changed per Read:    4.85    Recursive Call %:    89.89

 Rollback per transaction %:    8.56       Rows per Sort:    13.39

The % Blocks changed per Read statistic indicates that only 4.85 percent of all blocks are retrieved for update, and in this example, the Recursive Call % statistic is extremely high with about 90 percent. However, this fact does not mean that nearly all SQL statements executed by the database are caused by parsing activity, data dictionary management, space management, and so on.

Remember, Oracle considers all SQL statements executed within PL/SQL programs to be recursive.  If there are applications making use of a large number of stored PL/SQL programs, this is good for performance. However, applications that do not widely use PL/SQL may indicate the need to further investigate the cause of this high recursive activity.

It is also useful to check the value of the Rollback per transaction % statistic. This statistic reports the percent of transactions rolled back. In a production system, this value should be low. If the output indicates a high percentage of transactions rolled back, the database expends a considerable amount of work to roll back changes made.  This should be further investigated in order to see why the applications roll back so often.

About Cache Sizes in AWR Reports

This report section contains the cache sizes at the end of the snapshot period. Remember, if you are using Automatic Memory Management, AMM may adjust your size between snapshots. This information is valid for the time specific to the end snapshot of the snapshot interval observed. This is essential because the Automatic Memory Management activity could resize caches during the snapshot interval time or the DBA can do it manually.

Cache Sizes (end)
~~~~~~~~~~~~~~~~~
Buffer Cache: 48M Std Block Size: 8K
Shared Pool Size: 56M Log Buffer: 256K

One interesting feature in AWR is the use of begin values and end values for those parameters that are changed dynamically or via the

Automatic Memory Manager (AMM) facility in Oracle10g:

PARAMETER NAME	BEGIN VALUE	END VALUE (IF DIFFERENT)
background_dump_dest	u01/app/DBDABR/ DUMP
compatible	10.1.0.2.0
db_block_size	8192
db_cache_size	364904448	455210668

Differences between AWR and STATSPACK

http://vinu3012dba.blogspot.com/2010/08/difference-or-advantage-between-awr-and.html

Duplicate a Database Using RMAN

A nice feature of RMAN is the ability to duplicate, or clone, a database from a previous backup. It is possible to create a duplicate database on a remote server with the same file structure, a remote server will a different file structure or the local server with a different file structure. In this article I'll demonstrate the last method, how to duplicate a database on the local server with a different file structure. This can prove useful when you want to recover selected objects from a backup, rather than roll back a whole database or tablespace.

First we must create a password file for the duplicate instance:

orapwd file=/u01/app/oracle/product/9.2.0.1.0/dbs/orapwDUP password=password entries=10

Next add the appropriate entries into the tnsnames.ora and listener.ora files in the $ORACLE_HOME/network/admin directory. Remember to load the new configuration into the listener:

# Added to the listener.ora SID_LIST
(SID_DESC =
  (ORACLE_HOME = /u01/app/oracle/product/9.2.0.1.0)
  (SID_NAME = DUP)
)

# Added to the tnsnames.ora
DUP =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = server1.tshcomputing.com)(PORT = 1521))
    )
    (CONNECT_DATA =
      (ORACLE_SID = DUP)
    )
  )

# Reload listener
lsnrctl reload

Next create an init.ora file for the duplicate database. Since we are duplicating the database onto the same server as the original we must convert the file names so there is no conflict:

# Minimum Requirement.
DB_NAME=DUP
CONTROL_FILES=(/u02/oradata/DUP/control01.ctl,
               /u02/oradata/DUP/control02.ctl,
               /u02/oradata/DUP/control03.ctl)

# Convert file names to allow for different directory structure.
DB_FILE_NAME_CONVERT=(/u02/oradata/TSH1/,/u02/oradata/DUP/)
LOG_FILE_NAME_CONVERT=(/u01/oradata/TSH1/,/u01/oradata/DUP/)

# make sure block_size and compatible parameters
# match if you are not using the default.
DB_BLOCK_SIZE=8192
COMPATIBLE=9.2.0.0.0

Next we connect to the duplicate instance:

ORACLE_SID=DUP; export ORACLE_SID
sqlplus /nolog
conn / as sysdba

Next we create an SPFILE based on the init.ora:

CREATE SPFILE FROM PFILE='/u01/app/oracle/admin/DUP/pfile/init.ora';

Next we start the database in NOMOUNT mode:

STARTUP FORCE NOMOUNT;

With the duplicate database started we can now connect to it from RMAN. For the duplication to work we must connect to the original database (TARGET), the recovery catalog (CATALOG) and our duplicate database (AUXILIARY):

ORACLE_SID=DUP; export ORACLE_SID
rman TARGET sys/password@tsh1 CATALOG rman/rman@tshadm AUXILIARY /

We can then dupicate the database using one of the following commands:

# Duplicate database to TARGET's current state.
DUPLICATE TARGET DATABASE TO DUP;

# Duplicate database to TARGET's state 4 days ago.
DUPLICATE TARGET DATABASE TO DUP UNTIL TIME 'SYSDATE-4';

The time it takes to complete varies depending on the size of the database and the specification of the server. Once the process is finished RMAN produces a completion message and you have your duplicate instance.

Note: At this point the new instance has little in the way of initialization parameters. You may want to assign figures more realistic than the defaults in the initial init.ora file.

For more information see:

• Duplicating a Database with Recovery Manager

Opatch

Introduction:
This post has been written on request of one of regular visitor of my blog (Altaaf). This post is all about Oracle Patching Utility (OPATCH) and various options that can be used along with Opatch.
We will begin the discussion by knowing what exactly is Opatch?
- OPatch is a java based utility that allow the application and rolling back of interim patches to an Oracle product. The program has sub-commands that may take arguments. The program requires Java(JVM) to be present on the current system.OPatch expects users to have commands such as fuser, jar, ar and make on Unix platforms and jar on Windows platforms to be available in their PATH.
Opatch Details
How to check the Opatch version?
-bash-3.00$ ./opatch version
Invoking OPatch 10.2.0.3.0
OPatch Version: 10.2.0.3.0
OPatch succeeded.
How to know which version is the correct version of Opatch for your RDBMS home?
You can verify whether the Opatch version for your RDBMS release is correct or not using metalink note ID 357221.1.
This note ID gives the copatibilities between OPatch version and RDBMS version.
How to get the latest version of OPatch?
You can download the latest version of OPatch from metalink using following URL.
http://updates.oracle.com/download/6880880.html
In the release dropdown you can select the OPatch release that you want to download based on your RDBMS version. Please read the README.txt before applying the patch.
What is Oracle Database Inventory and where it is located?
Oracle Inventory is the location or the place where all the information about an Oracle Home is stored and maintained. When ever we install an RDBMS Oracle Home, a new inventory gets created. Applying any new patch to Oracle Home will update the invnrtory for that Oracle Home and record the details for the patch applied. This inventory is in the form of XML files.
The location of inventory is defined in a file called oraInst.loc. The path for this file is provided while installing Oracle Home. If we dont supply any path, the is file will be present at central location /etc/oraInst.loc. Also we can have a central inventory if its not used by any other oracle installation. If the central inventory is used by previous Oracle installation we can create local inventory. The content of oraInst.loc file is as shown below.
-bash-3.00$ cd $ORACLE_HOME
-bash-3.00$ cat oraInst.loc
inventory_loc=/slot/ems2029/appmgr/apps/tech_st/10.1.2/oraInventory
inst_group=ems2029
-bash-3.00$
Here “inventory_loc” gives the location of Inventory directory where as inst_group gives the name of the group which is the owner of this inventory.
How to create Local Inventory?
You can create local inventory at any location while installing Oracle Home. You need to use -invPtrLoc variable along with runInstaller command and give the location of oraInst.loc. Otherwise the default path assumed for oraInst.loc is /etc/oraInst.loc.
./runInstaller -invPtrLoc /slot/ems2029/appmgr/apps/tech_st/10.1.2/oraInst.loc
After the installation starts it will ask for location of oraInventory directory and group which should own it. It will make entry of these into oraInst.loc file.
Having know the above information about opatch, now we will move to details about using opatch and various options available.
Opatch help
You can get all the options using opatch -help
-bash-3.00$ ./opatch -help
Invoking OPatch 11.1.0.6.0
Oracle Interim Patch Installer version 11.1.0.6.0
Copyright (c) 2007, Oracle Corporation.  All rights reserved.
Usage: opatch [ -help ] [ -r[eport] ] [ command ]
command := apply
lsinventory
prereq
query
rollback
util
version
:= -help       Displays the help message for the command.
-report     Print the actions without executing.
example:
‘opatch -help’
‘opatch apply -help’
‘opatch lsinventory -help’
‘opatch prereq -help’
‘opatch rollback -help’
‘opatch util -help’
OPatch succeeded.
You can get specific help for any command of opatch using opatch -help. Example opatch apply -help.
Applying single patch using opatch
1. You MUST read the Readme.txt file included in opatch file, look for any prereq. steps/ post installation steps or and DB related changes. Also, make sure that you have the correct opatch version required by this patch.
2.Make sure you have a good backup of database.
3. Make a note of all Invalid objects in the database prior to the patch.
4. Shutdown All the Oracle Processes running from that Oracle Home , including the Listener and Database instance, Management agent etc.
5. You MUST Backup your oracle Home and Inventory
tar cvf – $ORACLE_HOME $ORACLE_HOME/oraInventory | gzip > Backup_Software_Version.tar.gz
6. Unzip the patch in $ORACLE_HOME/patches
If you are applying a single intrim patch to oracle home, then change the location to the directory of that patch and then invoke following command.
Example if I need to apply patch 6972343 to RDBMS Oracle Home
-bash-3.00$ cd 6972343
-bash-3.00$ pwd
/slot/ems2029/oracle/db/tech_st/11.1.0/patches/6972343
-bash-3.00$ ls
etc  files  README.txt
$ORACLE_HOME/OPatch/opatch apply
If you have created local inventory then you need to give the correct path for local inventory stored in oraInst.loc. If you have updated /etc/oraInst.loc with correct location of your inventory, then above command will work, If not you can give the correct location by giving local oraInst.loc file.
$ORACLE_HOME/OPatch/opatch apply -invPtrLoc /slot/ems2029/oracle/db/tech_st/11.1.0/oraInst.loc
Remember that when we are applying patch, patch number is NOT provided as an input to opatch command. So we have to to be in the directory of patch since opatch will pick the patch files from current directory.
Rolling back a patch
In case if a patch did not fix the issue or if you has applied wrong patch to oracle home, you can always rollback the patch using following comamnd.
opatch rollback -id
Applying bundle patches
Some times if you have to apply bundle patch having many patches, example lets say you want to a bundle patch 6778860 (    11.1.0.6 BUNDLED PATCH FOR EBS 11I)) containing many other patches to RDBMS home, OPatch provides a facility to apply many patches to RDBMS home using a single comamnd. Following command should be used for applying bundle patches.
Download the patch and extract the same. You will see many directories (one for each patch) inside the main patch directory. Execute following command to apply all patches.
$ORACLE_HOME/OPatch/opatch util NApply -invPtrLoc /slot/ems2029/oracle/db/tech_st/11.1.0/oraInst.loc
NApply -> Will apply all patches to RDBMS home.
You can also use -phBaseDir to point to the directory where bundle patch is installed. So you can run opatch command from any location
$ORACLE_HOME/OPatch/opatch util NApply -phBaseDir /slot/ems2029/oracle/db/tech_st/11.1.0/patches/6778860 -invPtrLoc /slot/ems2029/oracle/db/tech_st/11.1.0/oraInst.loc
You can also apply specific patches using NApply
opatch util napply -id 1,2,3 -skip_subset -skip_duplicate
This will apply patches 1, 2, and 3 which are under < the patch_location> directory. OPatch will skip duplicate patches and subset patches (patches under that are subsets of patches installed in the ORACLE_HOME)
You can see all the options for NApply using following help command.
$ORACLE_HOME/OPatch/opatch util NApply -help

Query the inventory for patches applied
We can query the inventory to check various components installed for database as well as to check various patches applied to database. Use following command to get a minimum information from inventory about patches applied and components installed.
$ORACLE_HOME/OPatch/opatch lsinventory -invPtrLoc /slot/ems2029/oracle/db/tech_st/11.1.0/oraInst.loc
Patch  5763576      : applied on Wed May 28 03:20:53 PDT 2008
Created on 6 Feb 2008, 02:26:04 hrs PST8PDT
Bugs fixed:
5763576
If you are using central inventory then -invPtrLoc variable is not required.
You can also get the detailed view of inventory using following command. In case of detail information it gives which are the files that this patch have touched.
$ORACLE_HOME/OPatch/opatch lsinventory -detail -invPtrLoc /slot/ems2029/oracle/db/tech_st/11.1.0/oraInst.loc
Patch  6318357      : applied on Wed May 28 03:33:27 PDT 2008
Created on 4 Dec 2007, 22:02:16 hrs PST8PDT
Bugs fixed:
6318357
Files Touched:
udjvmrm.sql –> ORACLE_HOME/javavm/install/udjvmrm.sql
Patch Location in Inventory:
/slot/ems2029/oracle/db/tech_st/11.1.0/inventory/oneoffs/6318357
Patch Location in Storage area:
/slot/ems2029/oracle/db/tech_st/11.1.0/.patch_storage/6318357_Dec_4_2007_22_02_16
Which options are installed in Oracle Home?
You can check the options installed in RDBMS home using above command. The options installed will be listed at the start of output.
Example: $ORACLE_HOME/OPatch/opatch lsinventory -detail -invPtrLoc /slot/ems2029/oracle/db/tech_st/11.1.0/oraInst.loc
The complete output of this command can be seen at this location.
Opatch Log files
Log file for Opatch utility can be found at $ORACLE_HOME/cfgtoollogs/opatch
Advanced Option for OPatch
Here are some of the advanced options of OPatch utility.
UpdateRemoteNodes
It is used to propagate/remove files/directories to/from remote nodes using files under ORACLE_HOME/.patch_storage//rac/*.
The directories listed in copy_dirs.txt will be copied to remote nodes.
The files listed in copy_files.txt wil be copied to remote nodes.
The directories listed in remove_dirs.txt will be deleted from remote nodes.
The files listed in remove_files.txt will be deleted from remote nodes.
This is used where RAC setup is present.
Cleanup
It is used to clean up ‘restore.sh, make.txt’ files and ‘rac, scratch, backup’ directories in the ORACLE_HOME/.patch_storage directory. If -ps option is used, then it cleans the above specified areas only for that patch, else for all patches under ORACLE_HOME/.patch_storage. You will be still able to rollback patches after this cleanup.
Example: opatch util cleanup -ps 6121183_Ju _21_2007_04_19_42
CopyListedFiles
It is used to copy all files listed in ORACLE_HOME/.patch_storage//rac/copy_files.txt to remote nodes. If -fp option is used, then one can specify the path of the file containing the list of files to be copied. The files mentioned in this file will be copied to the remote nodes.
Example: opatch util copylistedfiles -fp a -remote_nodes ceintcb-a5
This is used where RAC setup is present.
CopyListedFilesTest
It is used to copy a single file to remote nodes. The usage remains the same as CopyListedFiles.
Example: opatch util copylistedfilestest -fp /home/oracle/a -remote_nodes ceintcb-a5
This is used where RAC setup is present.
CopyListedDirs
It is used to recursively copy all directories listed in ORACLE_HOME/.patch_storage//rac/copy_dirs.txt to remote nodes. If -dp option is used, then one can specify the path of the file containing the list of directories to be copied. The directories mentioned in this file will be copied to the remote nodes.
This is used where RAC setup is present.
CopyListedDirsTest
It is used to copy a single file to remote nodes. The usage remains the same as CopyListedDirs.
This is used where RAC setup is present.
RemoveListedFiles
It is used to remove files listed in ORACLE_HOME/.patch_storage//rac/remove_files.txt on remote nodes. If -fr option is used, then one can specify the path of the file containing the list of files to be removed. The files mentioned in this file will be removed from the remote nodes.
This is used where RAC setup is present.
RemoveListedFilesTest
It is used to remove a single file from remote nodes. The usage remains the same as RemoveListedFiles.
This is used where RAC setup is present.
RemoveListedDirs
It is used to recursively remove directories listed in ORACLE_HOME/.patch_storage//rac/remove_dirs.txt from remote nodes. If -dr option is used, then one can specify the path of the file containing the list of directories to be removed. The directories mentioned in this file will be removed from the remote nodes.
This is used where RAC setup is present.
RemoveListedDirsTest
It is used to remove a single directory from remote nodes. The usage remains the same as RemoveListedDirs.
This is used where RAC setup is present.
RunLocalMake
It is used to invoke re-link on the local node. The make commands are stored in ORACLE_HOME/.patch_storage//make.txt. You need to use the -ps option to specify the Patch ID with timestamp. A directory by this name will be present under ORACLE_HOME/.patch_storage. The make.txt file present under ORACLE_HOME/.patch_storage// will be used to perform the local make operation. This command cannot be run if you have already run Cleanup as it would have removed these make.txt files.
Example: opatch util runlocalmake -ps 6121250_ un_21_2007_04_16_11
RunRemoteMake
It is used to invoke re-link on remote nodes. The make commands are stored in
ORACLE_HOME/.patch_storage//rac/makes_cmd.txt. The usage remains the same as RunLocalMake.
This is used where RAC setup is present.
RunAnyCommand
It is used to run any command on remote nodes. The command should be specified using the -cmd option.
Example: opatch util runanycommand -remote_nodes ceintcb-a5 -cmd ls
This is used where RAC setup is present.
LoadXML
It is used to check the validity of an XML file. The -xmlInput option can be used to specify the path of the xml file.
@ Support can use this utility to verify the integrity of XML files contained in the local and central inventory.
Example: opatch util loadxml -xmlInput $ORACLE_HOME/inventory/ContentsXML/comps.xml
Verify
It is used to run the patch verification process to ensure that the patch was applied to the ORACLE_HOME. It uses the defined ORACLE_HOME and the given patch location via -ph, to run the check.
@ Support can use this utility to re-run the OPatch verification stage, if required for patch diagnosis. The patch staging area has to be present or created.
Example: opatch util verify -ph ~/6646853/6121183
Troubleshooting
Some times the inventory get corrupted because of some issues. In that case you need to repair the inventory. Following are the two methods which I know can be used to repair the inventory.
1) Development has released a utility (checkinv) which can be use to repair the  inventory if it becomes corrupted for some reason.
Please note that this version of checkinv works only for 9.2 and 10.1 releases of oracle database. This doesn’t work for 10.2 releases of oracle database.
You can download the checkinv utility from Patch 4121946.
To cleanup the components, you should determine the unwanted or dangling components, then specify those using the option “-remcomp”, to cleanup the component list.
The utility detects and asks user’s permission to repair a bad inventory.  In case you want to repair the inventory, the utility will first back up the files before any modification.
However, it is better that you backup the inventory before running the tool.
For more details, check metalink note ID 298906.1
2) Creating a new inventory using Oracle Universal Installer (OUI).
Some times you clone an oracle home manually using OS level commands and this might cause problem in your existing inventory. Your new ORACLE_HOME will not be registered in your existing registry.
As far as running of database is concerned, we wont face any problem, trouble comes when in future you want to apply a patch to an oracle home which is not registered in inventory. The patch can be applied to an ORACLE_HOME with -no_inventory option, but then you wont have the record of patches applied to that ORACLE_HOME.
For such situation, you can register your new ORACLE_HOME manually to the existing inventory (or create a new inventory). Below is the command for the same.
cd $ORACLE_HOME/oui/bin 
./runInstaller -attachHome ORACLE_HOME=”/slot/ems1696/oracle/db/tech_st/11.1.0″ ORACLE_HOME_NAME=”Oracle11g_home1″ 
Here ORACLE_HOME will contain the complete path for ORACLE_HOME and  ORACLE_HOME_NAME will be the name you want to give for your ORACLE_HOME
Hope this helps !!

CONVERTING DATABASE FROM NON-ASM to ASM

CONVERTING DATABASE FROM NON-ASM to ASM
=======================================

1) Backup the database.

2) Shut down and startup the database in nomount mode.

$ sqlplus
SQL*Plus: Release 10.2.0.4.0 - Production on Fri Jan 1 12:52:44 2010

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

Enter user-name: /as sysdba
Connected to an idle instance.



SQL> startup nomount
ORACLE instance started.

Total System Global Area 1.6034E+10 bytes
Fixed Size 2127408 bytes
Variable Size 1610615248 bytes
Database Buffers 1.4361E+10 bytes
Redo Buffers 60174336 bytes
SQL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP, Data Mining
and Real Application Testing options


$

3) Copy the non-asm files to asm diskgroup.

[oracle]$ rman nocatalog

Recovery Manager: Release 10.2.0.4.0 - Production on Fri Jan 1 12:57:14 2010

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

RMAN> connect target

RMAN> sql 'alter database mount';

sql statement: alter database mount
released channel: ORA_DISK_1

RMAN> BACKUP AS COPY DATABASE FORMAT '+DATA';

Starting backup at 01-JAN-10
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=383 devtype=DISK
channel ORA_DISK_1: starting datafile copy
input datafile fno=00379 name=/u10/oracle/data/TEST/undo01.dbf
output filename=+DATA/TEST/datafile/apps_undots1.257.707144559 tag=TAG20100101T130234 recid=1 stamp=707144573
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:26
channel ORA_DISK_1: starting datafile copy
input datafile fno=00353 name=/u10/oracle/data/TEST/system01.dbf
output filename=+DATA/TEST/datafile/system.258.707144581 tag=TAG20100101T130234 recid=2 stamp=707144595
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:25
channel ORA_DISK_1: starting datafile copy
input datafile fno=00406 name=/u10/oracle/data/TEST/a_txn_ind01.dbf
output filename=+DATA/TEST/datafile/apps_ts_tx_idx.259.707144605 tag=TAG20100101T130234 recid=3 stamp=707144616
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:15
channel ORA_DISK_1: starting datafile copy
......
.......
input datafile fno=00009 name=/u10/oracle/data/TEST/odm.dbf
output filename=+DATA/TEST/datafile/odm.298.707145081 tag=TAG20100101T130234 recid=42 stamp=707145080
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01
channel ORA_DISK_1: starting datafile copy
input datafile fno=00007 name=/u10/oracle/data/TEST/owad01.dbf
output filename=+DATA/TEST/datafile/owapub.299.707145081 tag=TAG20100101T130234 recid=43 stamp=707145081
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01
Finished backup at 01-JAN-10

RMAN> SWITCH DATABASE TO COPY;

datafile 1 switched to datafile copy "+DATA/TEST/datafile/system.269.707144795"
datafile 2 switched to datafile copy "+DATA/TEST/datafile/system.270.707144811"
datafile 3 switched to datafile copy "+DATA/TEST/datafile/system.271.707144827"
.....
....
datafile 406 switched to datafile copy "+DATA/TEST/datafile/apps_ts_tx_idx.259.707144605"
datafile 407 switched to datafile copy "+DATA/TEST/datafile/apps_ts_seed.266.707144751"

RMAN>

4) Switch tempfiles

RMAN> run {
2> set newname for tempfile 1 to '+DATA';
3> set newname for tempfile 2 to '+DATA';
4> switch tempfile all;
5> }

executing command: SET NEWNAME

executing command: SET NEWNAME

renamed temporary file 1 to +DATA in control file
renamed temporary file 2 to +DATA in control file

RMAN> Alter database open;

database opened

5) Now, restore controlfile from non-asm to asm

RMAN> shutdown immediate

database closed
database dismounted
Oracle instance shut down

RMAN> startup nomount

connected to target database (not started)
Oracle instance started

Total System Global Area 1073741824 bytes

Fixed Size 2089336 bytes
Variable Size 427822728 bytes
Database Buffers 629145600 bytes
Redo Buffers 14684160 bytes

RMAN> restore controlfile to '+DATA' from '/u01/oracle/oradata/TEST/db/apps_st/data/cntrl01.dbf';

Starting restore at 01-JAN-10
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=384 devtype=DISK

channel ORA_DISK_1: copied control file copy
Finished restore at 01-JAN-10

RMAN> shutdown immediate

Oracle instance shut down

RMAN> exit


Recovery Manager complete.


6) Using asmcmd find out the complete path to the controlfile and edit the pfile/spfile to the path in the asm.


7) Add multiple logfile members to a group and drop non-asm files.

select memeber from v$logfile;

/u01/oracle/oradata/TESTlog02a.dbf
/u01/oracle/oradata/TESTlog02b.dbf
/u01/oracle/oradata/TESTlog01a.dbf
/u01/oracle/oradata/TESTlog01b.dbf

SQL> ALTER DATABASE ADD LOGFILE MEMBER '+DATA' TO GROUP 1;

Database altered.

SQL> /

Database altered.

SQL> ALTER DATABASE ADD LOGFILE MEMBER '+DATA' TO GROUP 2;

Database altered.

SQL> /

Database altered.

/u01/oracle/oradata/TESTlog02a.dbf
/u01/oracle/oradata/TESTlog02b.dbf
/u01/oracle/oradata/TESTlog01a.dbf
/u01/oracle/oradata/TESTlog01b.dbf



SQL> ALTER DATABASE DROP LOGFILE MEMBER '/u01/oracle/oradata/TESTlog02a.dbf';

Database altered.

SQL> ALTER SYSTEM SWITCH LOGFILE;

System altered.

SQL> ALTER DATABASE DROP LOGFILE MEMBER '/u01/oracle/oradata/TESTlog02b.dbf';
ALTER DATABASE DROP LOGFILE MEMBER '/u01/oracle/oradata/TESTlog02b.dbf'
*
ERROR at line 1:
ORA-01609: log 2 is the current log for thread 1 - cannot drop members
ORA-00312: online log 2 thread 1: '/u01/oracle/oradata/TESTlog02b.dbf'
ORA-00312: online log 2 thread 1: '+DATA/TEST/onlinelog/group_2.305.707145955'
ORA-00312: online log 2 thread 1: '+DATA/TEST/onlinelog/group_2.306.707145963'


SQL> ALTER DATABASE DROP LOGFILE MEMBER '/u01/oracle/oradata/TESTlog01a.dbf';

Database altered.

SQL> ALTER SYSTEM SWITCH LOGFILE;

System altered.

SQL> ALTER DATABASE DROP LOGFILE MEMBER '/u01/oracle/oradata/TESTlog02b.dbf';

Database altered.

SQL> ALTER SYSTEM SWITCH LOGFILE;

System altered.

SQL> ALTER DATABASE DROP LOGFILE MEMBER '/u01/oracle/oradata/TESTlog01b.dbf';

Database altered.

SQL> select * from v$logfile;

GROUP# STATUS TYPE MEMBER IS_
---------- ------- ------- -------------------------------------------------- ---
1 ONLINE +DATA/TEST/onlinelog/group_1.303.707145913 NO
1 ONLINE +DATA/TEST/onlinelog/group_1.304.707145943 NO
2 ONLINE +DATA/TEST/onlinelog/group_2.305.707145955 NO
2 ONLINE +DATA/TEST/onlinelog/group_2.306.707145963 NO

8) Restart the database.