Summary of RMAN Commands
Following are RMAN commands that you can execute at the RMAN prompt, within a RUN command, or both. All commands from previous RMAN releases work with the current release.
Friday, 26 July 2013
Flash/Fast Recovery Area (FRA) in Oracle
The flash recovery area is the most powerful tool available from Oracle 10g, that plays a vital role in performing database backup & recovery operations. From Oracle 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 (Recovery Manager) 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)
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 (Recovery Manager) 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 recoveryoperations.
§ 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:
o DB_RECOVERY_FILE_DEST_SIZE
o DB_RECOVERY_FILE_DEST
o 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_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.
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.
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.
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:
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:
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.
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 flashbacklogs, 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
also see http://learnappsdbaskills.blogspot.com/2014/02/flashback-concepts-in-oracle.html
Related views
V$RECOVERY_FILE_DEST
V$FLASH_RECOVERY_AREA_USAGE
V$DBA_OUTSTANDING_ALERTS
V$FLASHBACK_DATABASE_LOGFILE
also see http://learnappsdbaskills.blogspot.com/2014/02/flashback-concepts-in-oracle.html
How to troubleshoot Login issues of Apps Applications
How to troubleshoot Login issues of Apps Applications
Action Plan:
1. Please do the following:
- stop all middle tier services
- make sure to delete the states and persistence files:
rm -fr $INST_TOP/ora/10.1.3/j2ee/oacore/persistence/*
rm -fr $INST_TOP/ora/10.1.3/j2ee/oafm/persistence/*
rm -fr $INST_TOP/ora/10.1.3/j2ee/forms/persistence/*
rm $ORA_CONFIG_HOME/10.1.3/opmn/logs/states/*
- Clear the TLD cache. Delete/backup the file:
$COMMON_TOP/_TldCache
- start all middle tier services
- Retest
2. If the issue still exist, please perform the following in order to obtain more information:
- Shutdown the http server, oc4j and opmn services
adapcctl.sh stop adoacorectl.sh stop adformsctl.sh stop adoafmctl.sh stop adopmnctl.sh stop
- Backup and then delete existing log files in (THIS IS IMPORTANT STEP TO GET FRESH LOG FILES):
$LOG_HOME/ora/10.1.3/Apache
$LOG_HOME/ora/10.1.3/j2ee
$LOG_HOME/ora/10.1.3/opmn
$APPLRGF/javacache.log
- Enable HTTP/OC4J/OPMN debug logging
Enable http ODL logging
-----------------------------
Edit httpd.conf file, add the following to the end of file
$ORA_CONFIG_HOME/10.1.3/Apache/Apache/conf/httpd.conf
OraLogMode oracle
OraLogSeverity TRACE:32
- Make the following directories which will be where the log files are created
mkdir $LOG_HOME/ora/10.1.3/Apache/oracle
mkdir $INST_TOP/pids/10.1.3/Apache/logs/oracle
- Increase OACore OC4J logging
----------------------------
edit j2ee-logging.xml adjust the following in file:
$ORA_CONFIG_HOME/10.1.3/j2ee/oacore/config/j2ee-logging.xml
<logger name='oracle' level='TRACE:32' useParentHandlers='false'>
edit orion-web.xml adjust the following in file:
$ORA_CONFIG_HOME/10.1.3/j2ee/oacore/application-deployments/oacore/html/orion-web.xml
param-name>debug_mode</param-name>
<param-value>true</param-value>
- Increase OPMN Logging
-------------------------------
edit opmn.xml adjust the following in file:
$ORA_CONFIG_HOME/10.1.3/opmn/conf/opmn.xml
<debug path="/u01/inst/apps/JB_atg/logs/ora/10.1.3/opmn/opmn.dbg" comp="
internal;ons;pm" rotation-size="1500000" rotation-hour="0" />
- Enable AFLogging
Open the file $ORA_CONFIG_HOME/10.1.3/j2ee/oacore/config/oc4j.properties in a text editor
Add:the following values:
AFLOG_ENABLED=true
AFLOG_LEVEL=statement
AFLOG_MODULE=%
AFLOG_FILENAME=/tmp/debug.log
- Startup the http server, oacore oc4j, forms oc4j, oafm oc4j and opmn
- Check the services status
$ADMIN_SCRIPTS_HOME/adopmnctl.sh status
- Try to login
- Review all the files from:
$LOG_HOME/ora/10.1.3/Apache
$LOG_HOME/ora/10.1.3/j2ee
$LOG_HOME/ora/10.1.3/opmn
$APPLRGF/javacache.log
$INST_TOP/appl/admin/<instance>.xml
$ORA_CONFIG_HOME/10.1.3/opmn/conf/opmn.xml
/tmp/debug.log
Tuesday, 23 July 2013
How to recover and open the database if the archive log required for recovery is missing.
How to recover and open the database if the archive log required for recovery is missing.
As part of recovery process, our restore went fine and also were able to re-create controlfile. During recovery, it asked for Archive logs. We checked with our Unix team for required archivelogs and found out they don’t have required archive logs.
Subscribe to:
Posts (Atom)