What is a Checkpoint?

A Checkpoint is a database event which synchronizes the modified data blocks in memory with the datafiles on disk and CKPT process updates the control file and datafile headers with last SCN.

A checkpoint has two purposes: (1) to establish data consistency, and (2) enable faster database recovery.   How is recovery faster?  Because all database changes up to the checkpoint have been recorded in the datafiles, making it unnecessary to apply redo log entries prior to the checkpoint. The checkpoint must ensure that all the modified buffers in the cache are really written to the corresponding datafiles to avoid the loss of data which may occur with a crash (instance or disk failure).

Oracle writes the dirty buffers to disk only on certain conditions:
  - A shadow process must scan more than one-quarter of the db_block_buffer parameter.
  - Every three seconds.
  - When a checkpoint is produced.

A checkpoint is realized on five types of events:
  - At each switch of the redo log files.
  - When the delay for LOG_CHECKPOINT_TIMEOUT is reached.
  - When the size in bytes corresponding to :
     (LOG_CHECKPOINT_INTERVAL* size of IO OS blocks) is written on the current redo log file.
  -  Directly by the ALTER SYSTEM SWITCH LOGFILE command.
  - Directly with the ALTER SYSTEM CHECKPOINT command.

During a checkpoint the following occurs:
 -  The database writer (DBWR) writes all modified database  blocks in the buffer cache back to datafiles,
 -  Log writer (LGWR) updates both the controlfile and  the datafiles to indicate when the last checkpoint  occurred (SCN)

Various types of checkpoints  in Oracle :

• Full checkpoint
• Thread checkpoint
• File checkpoint
• Parallel Query checkpoint
• Object checkpoint
• Log switch checkpoint
• Incremental checkpoints

ORA-20000: Unable to analyze TABLE ' ' , insufficient privileges or does not exist

When you want to gather stat of a table and oracle throw ORA-20000:

begin 

dbms_stats.gather_table_stats(

ownname=> 'your_username',

tabname=> 'jbm' ,

estimate_percent=> DBMS_STATS.AUTO_SAMPLE_SIZE,

cascade=> DBMS_STATS.AUTO_CASCADE,

degree=> null,

no_invalidate=> DBMS_STATS.AUTO_INVALIDATE,

granularity=> 'AUTO',

method_opt=> 'FOR ALL COLUMNS SIZE AUTO');

end;

ERROR at line 1:

ORA-20000: Unable to analyze TABLE "your_username"."jbm", insufficient

privileges or does not exist

ORA-06512: at "SYS.DBMS_STATS", line 24270

ORA-06512: at "SYS.DBMS_STATS", line 24332

ORA-06512: at line 2

SQL> grant analyze any to your_username;

SQL> exec dbms_stats.unlock_table_stats(ownname => 'your_username',tabname => 'jbm');

SQL> begin 

dbms_stats.gather_table_stats(

ownname=> 'your_username',

tabname=> 'jbm' ,

estimate_percent=> DBMS_STATS.AUTO_SAMPLE_SIZE,

cascade=> DBMS_STATS.AUTO_CASCADE,

degree=> null,

no_invalidate=> DBMS_STATS.AUTO_INVALIDATE,

granularity=> 'AUTO',

method_opt=> 'FOR ALL COLUMNS SIZE AUTO');

end;

/

PL/SQL procdure successfully completed.

DB_LOST_WRITE_PROTECT,DB_BLOCK_CHECKING,DB_BLOCK_CHECKSUM

Scope
This document is intended for Database Administrators wanting to learn how to prevent and detect from various data block corruptions. 
A corrupt block is a block that has been changed so that it differs from what Oracle Database expects to find. This note covers three data block corruption types:

• In a physical block corruption, which is also called a media corruption, the database does not recognize the block at all: the checksum is invalid, the block contains all zeros, the header and footer of the block do not match or one of the key data block data structure is incorrect such as the data block address (DBA).
• In a logical block corruption, the contents of the block are physically sound and pass the physical block checks; however the block can be logically inconsistent. Examples of logical corruption include corruption of a row piece or index entry.
• Block corruptions caused by stray writes, lost writes or misdirected writes can also cause havoc to your database availability. The data block may be physically or logically correct but in this case the block’s content is older or stale or in the wrong location.

Block corruptions can also be divided into interblock corruption and intrablock corruption:

• In intrablock corruption, the corruption occurs in the block itself and can be either a physical or a logical corruption.
• In an interblock corruption, the corruption occurs between blocks and can only be a logical corruption.

Details
Causes of corrupted blocks
Block corruptions can be caused by various failures including, but not limited to the following:

• Faulty disks and disk controllers
• Faulty memory
• Faulty network components
• Firmware, operating system, volume manager, NFS or third party software defects
• Oracle Database software defects

Block corruptions can be also be caused by operator errors such as copying backups over existing data files or restoring inconsistent database backups.

Configuration Details
Configure at Primary Database:

• DB_BLOCK_CHECKSUM=FULL
• DB_BLOCK_CHECKING=FULL or MEDIUM
• DB_LOST_WRITE_PROTECT=TYPICAL
• Enable Flashback Technologies for fast point-in-time recovery from human errors (e.g. drop table, inadvertent or malicious data changes) and for fast reinstatement of a primary database following failover.

Configure at Data Guard Standby Database:

• DB_BLOCK_CHECKSUM=FULL
• DB_BLOCK_CHECKING=FULL or MEDIUM
• DB_LOST_WRITE_PROTECT=TYPICAL
• Enable Flashback Technologies for fast point-in-time recovery from human errors (e.g. drop table, inadvertent or malicious data changes) and for fast reinstatement of a primary database following failover.
• Use Active Data Guard to enable Automatic Block Repair (Data Guard 11.2 onward).

Review the additional background on each of these settings provided in the sections below, especially if tests show that any of the above recommendations have a greater than acceptable impact on the performance of your application.

DB_ULTRA_SAFE Parameter

DB_ULTRA_SAFE is a new parameter introduced in 11g. It provides an integrated mechanism to offer protection from various possible data corruptions. and provides critical high availability benefits for Oracle Database. Setting DB_ULTRA_SAFE initialization parameter will configure the appropriate data protection block checking level in the database. It will control DB_BLOCK_CHECKING, DB_BLOCK_CHECKSUM, and DB_LOST_WRITE_PROTECT.

DB_BLOCK_CHECKING controls whether or not Oracle performs block checking for database blocks.

DB_BLOCK_CHECKSUM determines whether DBWn and the direct loader will calculate a checksum (a number calculated from all the bytes stored in the block) and store it in the cache header of every data block when writing it to disk.

DB_LOST_WRITE_PROTECT enables or disables lost write detection. A data block lost write occurs when an I/O subsystem acknowledges the completion of the block write, while in fact the write did not occur in the persistent storage.

DB_ULTRA_SAFE Parameter can be set to 3 different values: OFF, DATA_ONLY and DATA_AND_INDEX. Default value is OFF.

Here are the descriptions of these values:

OFF: It will not change values of DB_BLOCK_CHECKING, DB_BLOCK_CHECKSUM and DB_LOST_WRITE_PROTECT if they are explicitly set, otherwise all of them will set to default values.

DATA_ONLY: It will set DB_BLOCK_CHECKING to medium, DB_LOST_WRITE_PROTECT to typical and DB_BLOCK_CHECKSUM to full.

DATA_AND_INDEX: It will set DB_BLOCK_CHECKING to full, DB_LOST_WRITE_PROTECT to typical, and DB_BLOCK_CHECKSUM to full.

The only difference between DATA_AND_INDEX and DATA_ONLY is DB_BLOCK_CHECKING. When DB_BLOCK_CHECKING is set to full, Oracle will do semantic checks for index blocks.

New features in Oracle Database 12c Release 1

Oracle Database 12c, c for cloud, a multi-tenant database management system, with nearly 500 new features.

SQL:

• Increased size limit for VARCHAR2, NVARCHAR2, and RAW datatypes to 32K  (from 4K).
• We can make a column invisible. 
  SQL> create table test (column-name column-type invisible);
  SQL> alter table table-name modify column-name invisible; 
  SQL> alter table table-name modify column-name visible;
• Oracle Database 12c has new feature called "Identity Columns" which are auto-incremented at the time of insertion (like in MySQL). 
  SQL> create table dept (dept_id number generated as identity, dept_name varchar);
  SQL> create table dept (dept_id number generated as identity (start with 1 increment by 1 cache 20 noorder), dept_name varchar);
• Temporary undo (for global temporary tables) will not generate undo. We can manage this by using init parameter temp_undo_enabled (=false|true).
• Duplicate Indexes - Create duplicate indexes on the same set of columns. Till Oracle 11.2, if we try to create an index using the same columns, in the same order, as an existing index, we'll get an error. In some cases, we might want two different types of index on the same data (such as in a datawarehouse where we might want a bitmap index on the leading edge of a set of columns that exists in a Btree index).
• PL/SQL inside SQL: this new feature allows to use DDL inside SQL statements (i.e.: to create a one shot function)
• The object DEFAULT clause has been enhanced. Adding a column to an existing table with a default value (much faster with Oracle 12c and it consumes less space than before, pointer to the Oracle Data Dictionary), applies also to sequences, identity types etc...
• Pagination query, SQL keywords to limit the number of records to be displayed, and to replace ROWNUM records. 
  SQL> select ... fetch first n rows only; 
  SQL> select ... offset m rows fetch next n rows only; 
  SQL> select ... fetch first n percent rows only; 
  SQL> select ... fetch first n percent rows with ties;
• Moving and Renaming datafile is now ONLINE, no need to put datafile in offline.
  SQL> alter database move datafile 'path' to 'new_path';
• The TRUNCATE command has been enhanced with a CASCADE option which follows child records.
• Reduces contents of regular UNDO, allowing better flashback operations.

PL/SQL:

• PL/SQL Unit Security - A role can now be granted to a code unit. That means you can determine at a very fine grain, who can access a specific unit of code.
• SQL WITH clause enhancement - In Oracle 12c, we can declare PL/SQL functions in the WITH clause of a select statement.
• Implicit Result Sets - create a procedure, open a ref cursor, return the results. No types, not muss, no mess. Streamlined data access (kind of a catch up to other databases).
• MapReduce in the Database - MapReduce can be run from PL/SQL directly in the database.
• We can use Booleans values in dynamic PL/SQL. Still no Booleans as database type.

Database:

• New background processes - LREG (Listener Registration), SA (SGA Allocator), RM.
• RESOURCE role does not include UNLIMITED TABLESPACE anymore.
• No need to shutdown database for changing archive log mode.
• Like sysdba, sysoper & sysasm, we have new privileges, in Oracle 12.1.0.
  sysbackup for Backup operations
  sysdg for Data Guard operations
  syskm for Key management
• Like sys, system, we have new default SYS users, in Oracle 12c Release1.
  sysbackup for Backup operations
  sysdg for Data Guard operations
  syskm for Key management
• Oracle Database 12c Data Pump will allow turning off redo for the import operation (only).
  $ impdp ... transform=disable_archive_logging:y
• expdp has transport view, view_as_tables options.
  $ expdp ... VIEWS_AS_TABLES=test_view
• Enhanced statistics (Hybrid histograms for more than 254 distinct values, dynamic sampling up to eleven, and stats automatically gathered during load).
• Row pattern matching - "MATCH_RECOGNIZATION" (identification of patterns within a table ordered/sorted by the SQL statement).
• Adaptive execution plans (change of the plan at runtime).
• Oracle 12c includes database level redaction, allowing granular control of access to sensitive data.
• Multi threaded database with parameter threaded_executions. Multiple processes and multiple threads within each process, provides improved performance and manageability through more efficient sharing of system and processor resources.
• Oracle introduced parameter PGA_AGGREGATE_LIMIT which is a real/PGA memory limit.
• UNDO for temporary tables can now be managed in TEMP, rather than the regular UNDO tablespace. Queried through v$tempundostat.
• Oracle Enterprise Manage Express (lightweight EM Cloud Control 12c version), replaces the Oracle Database console and, is installed automatically.
• enable_ddl_logging
• New type of sequences - session sequences, for global temporary tables
• Materialized View out-of-place refresh.
  DBMS_MVIEW.REFRESH('TEST_MV', method => '?', atomic_refresh => FALSE, out_of_place => TRUE);
• CSSCAN (Character Set Scanner) and CSALTER utilities replaced with DMU (Database Migration Assistant for Unicode).
• Monitor the privilege assignments easy in 12c Oracle with DBMS_PRIVILEGE_CAPTURE.
• SELECT ANY DICTIONARY no longer provides access to tables with password hashes (USER$ etc).
• Reduces the size of redo associated with recovering the regular UNDO tablespace.

ASM: (Oracle Grid Infrastructure new features)

• Introduction of Flex ASM, ASM would run only on 3 instances on a cluster even if more than 3, the instances that not have an ASM would request remote ASM, typically like SCAN. In normal conditions in a node if ASM fails the entire node will be useless, where in 12c the ability to get the extent map from remote ASM instance makes the node useful.
• Introduction of Flex Cluster, with light weight cluster stack, leaf node and traditional stack hub node, application layer is the typical example of leaf nodes where they don't require any network heartbeat.

RMAN:

• Table (or table partition) Point-In-Time Recovery (combination of Data Pump and RMAN, auxiliary instance required). The table is recovered into an auxiliary instance and there is the option to import the recovered table into a new table or partition using REMAP option or create the expdp dump of the recovered table only, for import at a later time of your choosing.
  RMAN> recover table table_name until scn scn_number auxiliary destination on 'path';
  RMAN> recover table sh:sales:P1 remap table sh.sales:P1:new_tab
• Running SQL commands and executing PLSQL procedures in RMAN without SQL keyword.
  RMAN> select * from v$session;
• Recover or copy files from Standby databases. 
  Refresh a single datafile on the primary from the standby (or standby from primary).
• Multi-sectional backup functionality, to improve backup and restore operation of large size data files, extended further in 12c to supports image copies and incremental backups along with the full database backupsets.
• Table level restoration i.e object level.
• Incremental recovery more faster, many of the tasks removed.
• In Oracle 12c, ACTIVE DUPLICATE DATABASE doesn’t not required any pre backup existence to clone the database, RMAN first takes backup of datafiles into backupsets, and transmit to the auxiliary location and will be restored/recovered subsequently.
• New NOOPEN clause in 12c ACTIVE DUPLICATE DATABASE, will prevent opening database immediately after the cloning process completion and the database will remain in MOUNT state and we have to manually open the database.
• Rolling forward/Synchronizing a standby database.

Partitioning:

• Partitioning enhancements (partition truncate, cascading, global index cleanup, online moving of a partition, ...)
• SQL> ALTER TABLE ... MOVE PARTITION ... ONLINE...
• Multiple partition operations in a single DDL.
• Interval-Ref Partitions - we can create a ref partition (to relate several tables with the same partitions) as a sub-partition to the interval type.
• Cascade for TRUNCATE and EXCHANGE partition.
• Asynchronous Global Index maintenance for DROP and TRUNCATE. Command returns instantly, but index cleanup happens later.
• Online move of a partition(without DBMS_REDEFINTIION).

Patching:

• Centralised patching.
• We can retrieve OPatch information using sqlplus query, using DBMS_QOPATCH package
  SQL> select dbms_qopatch.get_opatch_lsinventory() from dual;
• We can test patches on database copies, rolling patches out centrally once testing is complete.

Compression:

• Automated compression with heat map.
• Optimisation can be run on live databases with no disruption. Data optimization will monitor the data usage and with policy archive old data and hot data will be compressed for faster access. Inactive data can be more aggressively compressed or archived, greatly reducing storage costs.
• Advanced Row compression (for Hot Data).
• Columnar Query compression (for Warm Data).
• Columnar Archive compression (for Archive Data).
• New parameters, SQLNET_COMPRESSION and SQLNET.COMPRESSION_SCHEME_LIST, allow the compression of data transitioning over Oracle Net Services between client and server. Compression can be enabled at the: Connection level (connect string, URL), Service level (tnsnames.ora, ldap.ora) and Database level (sqlnet.ora).

Data Guard:

• Oracle Database 12c introduces a new redo transportation method which omits the acknowledgement (to primary) of the transaction on the standby. This feature is called "Fast Sync" redo transport.
• Creating a new type of redo destination called "Far Sync Standby". A "Far Sync Standby" is composed only of the standby control files, the standby redo logs and some disk space for archive logs which shall be sent to the Standby database. Failover & Switchover operations are totally transparent as the "Far Sync Standby" cannot be used as the target.
• Data Guard Broker commands have been extended. The "validate database" command to checks whether the database is ready for role transition or not.
• In 12c it is possible to cascade a Standby Database in Real Time, that is, the first Standby Database can send Redo from the Standby RedoLogs to the cascaded Standby Database.
• Data Guard Broker now supports cascaded standby.
• In 12c Data Guard, if you want to start MRP in RTA (real-time apply), use
  ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT;
• In 12c Data Guard, if you want to start MRP in non RTA (real-time apply), use ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING ARCHIVED LOGFILE DISCONNECT;
• Global data services, transparent to failover / switchover no client reconfiguration required.
• Data Guard supports Physical Standby Database for a Multitenant Database.
• Global Temporary Tables can now be used on an standby databases/Active Data Guard (ADG) standby databases.

OEM (Oracle Enterprise Manager):

• Oracle Enterprise Manager Database Express (DB Express), also referred to as EM Express, is a web-based tool for managing Oracle Database 12c, to support basic administrative tasks such as storage and user management, and provides comprehensive solutions for performance diagnostics and tuning.
• EM Express is similar to DB Control (Oracle Enterprise Manager DB Console) in 10g & 11g, but not (full) replacement because EM Express has very less features.
• To use EM Express, a database user needs to have EM_EXPRESS_BASIC or EM_EXPRESS_ALL role. DBA role includes both the EM_EXPRESS_BASIC and the EM_EXPRESS_ALL roles. 
  SQL> grant EM_EXPRESS_BASIC to user;
  SQL> grant EM_EXPRESS_ALL to user;

Pluggable Databases:
In Oracle 12c, in a pluggable database environment, we can create a single database container, and plug multiple databases into this container. All these databases then share the exact same oracle server/background processes and memory, unlike the previous versions where each database has its own background processes and shared memory. This helps in database consolidation and reduces the overhead of managing multiple desperate databases.

Container Database (CDB): Are the core data dictionary objects that come after an Oracle database installation.
Pluggable Database (PDB): Data dictionary objects and data related to the application. We can have many PDB plugged into a single CDB.

A new admin role "CDB Administrator" has been introduced in Oracle 12.1.0 release databases.
Multiple LGWR processes for each PDB’s and multiple databases can then share a master LGWR process, but have their own dedicated LGWR process within the container.

All Oracle database options/features are available on the PDB level.

RMAN backup at CDB level.

We can unplug a PDB from a CDB to another CDB.
PDB's can be cloned inside the CDB.
Management of PDB (clone/creation/plug/unplug/drop) are implemented as SQLs.
Extremely fast PDB-provisioning (clone inside the CDB), because each CDB comes with a “PDB Seed”.
Database patch/upgrade management very quick as CDB is a single point of installation.
Each PDB has its own data dictionary.
Data Guard configuration on CDB as whole.
RMAN point-in-time recovery at PDB level (while other PDB's remains open).
Resource Manager is extended for creating, unplugging, plugging in, and cloning, dropping or even setting up for the open mode of the PDB.
Flashback of a PDB should be available for Oracle 12c Release 2.

Entire containers can be backed up in single run, regardless of how many databases they contain.
Upgrade one container database and all pluggable databases are upgraded.

New Commands
create pluggable database ...
alter pluggable database ...
drop pluggable database ...

New Views/Packages in Oracle 12c Release1
dba_pdbs
v$pdbs
cdb_data_files

dbms_pdb
dbms_qopatch
UTL_CALLSTACK
dbms_redact

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)

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 offlashback 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

Source: 

Sachin's DBA Blog