rlwrap : Command History and Auto-Completion for SQL*Plus on Unix/Linux

see http://www.idevelopment.info/data/Oracle/DBA_tips/SQL_PLUS/SQLPLUS_8.shtml
or see http://matthieucornillon.fr/2011/05/rlwrap-pour-sqlplus-auto-completion-historique-etc/

Oracle 11g: automatic startup/shutdown

To configure an instance for automatic startup and shutdown, edit the "/etc/oratab" file and for each instance define the following line :

SID:<value_of_the_oracle_home>:[Y|N]

For exemple, to register for automatic startup the DB11G instance :

DB11G:/opt/oracle/11.2.0:Y

Next, create a file called "/etc/init.d/dbora" as the root user, containing the following :

#!/bin/sh
# chkconfig: 345 99 10
# description: Oracle auto start-stop script.
#
ORACLE_HOME=/opt/oracle/11.2.0
ORACLE_OWNER=oracle

if [ ! -f $ORACLE_HOME/bin/dbstart ]
then
    echo "Oracle startup: cannot start"
    exit
fi

case "$1" in
    'start')
        # Start the Oracle databases:
        # The following command assumes that the oracle login 
        # will not prompt the user for any values
        su - $ORACLE_OWNER -c "$ORACLE_HOME/bin/lsnrctl start"
        su - $ORACLE_OWNER -c $ORACLE_HOME/bin/dbstart
        touch /var/lock/subsys/dbora
        ;;
    'stop')
        # Stop the Oracle databases:
        # The following command assumes that the oracle login 
        # will not prompt the user for any values
        su - $ORACLE_OWNER -c $ORACLE_HOME/bin/dbshut
        su - $ORACLE_OWNER -c "$ORACLE_HOME/bin/lsnrctl stop"
        rm -f /var/lock/subsys/dbora
        ;;
esac

Set the dbora file in the dba group :

$> chgrp dba dbora

Use the chmod command to set the privileges to 750:

$> chmod 750 /etc/init.d/dbora

Associate the dbora service with the appropriate run levels and set it to auto-start using the following command:

$> chkconfig --add dbora

links :
For Oracle 10.2 see http://docs.oracle.com/cd/B19306_01/server.102/b15658/strt_stp.htm#CFAHAHGA
For Oracle 11.2 see http://docs.oracle.com/cd/E11882_01/server.112/e10839/strt_stp.htm#BABGDGHF

Oracle 11g: Exp/imp how to get metadata

The idea is to use exp and imp utilities to extract only DDL in order to duplicate a schema.
To do this, export first the database using exp :

$> export ORACLE_HOME=/opt/oracle/10.2.0
$> export PATH=$PATH:$ORACLE_HOME/bin
$> export ORACLE_SID=MYDB
$> export NLS_LANG=AMERICAN_AMERICA.UTF8
$> exp userid=system file=export.dmp log=export.log owner=<schemaname_to_export> consistent=y

Import the database but specify that metadata will be redirected to a file :

$> imp userid=system file=export.dmp indexfile=metadata.txt fomuser=<schemaname_to_import>

Oracle11g: Flashback database

Flashback database allows a user to rewind an entire database in the past.
Flashback database uses specific flashback logs that contains blocks of database that have been modified. These logs are stored in the Fast Recovery area.

Configuring a database for flashback database

1.  The database must be in archivelog
   

   SQL> ALTER DATABASE ARCHIVELOG;

2.  The target retention policy must be configured
   

   SQL> ALTER SYSTEM SET DB_FLASHBACK_RETENTION_TARGET=3000 SCOPE=BOTH;

   In this example, the retention is configure for 3000 minutes. There is no guarantee on the retention target; it is depending on the amount of data stores in the flashback logs and the size of the fast recovery area. If space is needed for the fast recovery area, flashback logs could be lost.
   


3.  Flashback Database must be explicitly activated
   

   SQL> ALTER DATABASE FLASHBACK ON;

   note: This activation must be done when the database is mounted in an exclusive mode (NOT OPENED!).
   

How to flashback a database

To perform a flashback, the database must be in MOUNT EXCLUSIVE state :

SQL> FLASHBACK DATABASE TO TIMESTAMP (sysdate - interval '1' DAY);
SQL> FLASHBACK DATABASE TO SCN #;
SQL> FLASHBACK DATABASE TO RESTORE POINT <restore_point>;
SQL> FLASHBACK DATABASE TO SEQUENCE #;

Once flashback done, the database must be reopen with RESETLOGS option.

Flashback opérations could be followed through the V$SESSION_LONGOPS view.

note : Flashback database could not be used if

•  The control file has been restored or re-created
•  A tablespace has been dropped
•  The size of a file has be reduced
•  The restoration is before a previous use of RESETLOGS (in this case use TO BEFORE RESETLOGS)

How to monitor a flashback database
To monitor disk space usage :

SQL> SELECT ESTIMATED_FLASHBACK_SIZE, FLASHBACK_SIZE FROM V$FLASHBACK_DATABASE_LOG;

To monitor the SCN window :

SQL> SELECT OLDEST_FLASHBACK_SCN, OLDEST_FLASHBACK_TIME FROM V$FLASHBACK_DATABASE_LOG;

To monitor flashback statistics :

SQL>SELECT BEGIN_TIME, END_TIME, FLASHBACK_DATA, DB_DATA, REDO_DATA, ESTIMATED_FLASHBACK_SIZE FROM V$FLASHBACK_DATABASE_STAT;

The v$flashback_database_stat contains 24 hours of statistics collection. Each line correspond to one hour of flashback activity. This view is usefull to calculate the size requiered by flashback logs in relation to the retention target defined by the administrator.
FLASHBACK_DATA is the number of bytes of flashback data and REDO_DATA the number of bytes of redo log generated for the period.
DB_DATA is the number of bytes of data block read and write.

The V$RECOVERY_FILE_DEST gives information on fast recovery disk usage (size quota, used space, reclamable space and number of files for each location of the fast recovery area).

Guaranted restore point
With flashback database, it is possible to defined a guaranted restore point that ensure that the flash recovery area maintains all information used to restore the database at this specific restore point (In this case no lost of flashback logs).
To define a guaranted restore point, you can issue:

SQL> CREATE RESTORE POINT <restore_point> GUARANTEE FLASHBACK DATABASE;

To create this kind of restore point, the following pre-requisite are mandatory:

•  The COMPATIBLE parameter must be greater than or equals to 10.2
•  The database must be in archivelog
•  Archive logs must be available before the creation of the guaranted restore point
•  The Fast recovery area must be configured and available

Oracle11g : Flashback Data Archive [Oracle Total Recall]

Oracle total recal register all tables modification in dedicated tablespaces associated to FLASHBACK ARCHIVE area.

To create a flashback archive you need the FLASHBACK ARCHIVE ADMINISTER priviledge. With this priviledge you can craete a flashback archive :

SQL> CREATE FLASHBACK ARCHIVE fda TABLESPACE fda_tbs QUOTA 100M RETENTION 5 YEARS;

To register a table with a flashback archive you need the FLASHBACK ARCHIVE privilege.

SQL> GRANT FLASHBACK ARCHIVE on fda to <user>;

With this priviledge you can reister a table with a specific flashback archive :

SQL> ALTER TABLE my_table FALSHBACK ARCHIVE fda;

With flashback archive registered for a specific table you can query the table in the past

SQL> SELECT * FROM my_table AS OF TIMESTAMP to_timestamp( '05/12/2001 10:41:00', 'DD/MM/YYYY HH24:MI:SS');

note : To make Flashback Data Archive fda the default Flashback Data Archive:

SQL> ALTER FLASHBACK ARCHIVE fda SET DEFAULT;

note : to modify the retention policy

SQL> ALTER FLASHBACK ARCHIVE fda MODIFY RETENTION 2 YEARS;

note : to purge data older then a specific time

SQL> ALTER FLASHBACK ARCHIVE fda PURGE BEFORE TIMESTAMP (SYSTIMESTAMP - INTERVAL '5' DAY);

note : to drop an archive

SQL> DROP FLASHBACK ARCHIVE fda;

note : common DDL requests are supported by flashback archive but for complete ones it is necessary to disassociate the table before to procceed any modification. Total recall provides the DISASSOCIATED_FBA & REASSOCIATE_FBA procedure of the DBMS_FLASHBACK_ARCHIVE package for this purpose.

note :

•  Automatic undo management must be enabled.
•  The tablespace in which flashback data archive is created must have Automatic Segment Space Management activated.
•  You can not drop a table that is managed by total recall but you can truncate it.
  

Oracle11g: Flashback Transaction Query, Flashback Transaction

Flashback transaction allows the user to rollback an entire transaction.
This technology is based on undo data and redo logs availables.

Pre-requisite
You need to :

1.  Activate supplemental log data
2.  Activate supplemental log data primary key
3.  Grant execute on DBMS_FLASHBACK package
4.  Grant SELECT ANY TRANSACTION priviledge to user

These can be done like this :

SQL> alter database add supplemental log data;
SQL> alter database add supplemental log data (primary key) columns;
SQL> grant execute on DBMS_FLASHBACK to <user>;
SQL> grant SELECT ANY TRANSACTION to <user>;

Transaction back-out
See the excellent example from this oracle blog to perform a transaction backout.
For Oracle documentation.

Oracle 11g: Flashback Query, Flashback Query Version, Flashback Table

The flashback Technology uses undo data to retrieve information (except for flashback database). Ensure that you have the correct UNDO_RETENTION parameter definition to guarantee your flashback opération through a specific period of time.


FlashBack Query
Flashback query allows user to query the database and see what were the data as a specific point in the past.

To perform a query on the past you can write :

SQL> SELECT employee_id, salary from HR.employees AS OF TIMESTAMP <time_stamp in past>;

or using a specific SCN number :

SQL> SELECT employee_id, salary from HR.employees AS OF SCN #;

note: It is not possible to flashback query a table if a DDL request has been performed after your targeted SCN or TIMESTAMP.


FlashBack Query Version
Flashback query version allows the user to display all modifications performed on a set of data between two timestamps or to SCN.

SQL> select VERSIONS_XID, employee_id, salary from HR.employees VERSIONS BETWEEN TIMESTAMP <T1> AND <T2>;

or using SCN

SQL> select VERSIONS_XID, employee_id, salary from HR.employees VERSIONS BETWEEN SCN # AND #;

VERSIONS_XID is a pseudo-column that defines the transaction identifier responsible of the line modification (Deleted lines are also considered).

note: Only commited lines are taken into account.
note: Do not use the query version to query :

•  External tables
•  Temporary tables
•  Views
•  V$ views

note: It is not possible to perform a flashback query version if a DDL request has been performed between the two targeted timstamps or SCN.


FlashBack Table
Flashback table restore a table at a specific time or SCN.
You must :

1.  be granted FLASHBACK TABLE or FLASHBACK ANY TABLE
2.  have SELECT , UPDATE, DELETE and ALTER priviledges on the targeted table
3.  enable ROW MOVEMENT on the targeted tables

   SQL> ALTER TABLE <table_name> ENABLE ROW MOVEMENT;

To flashback a table at a specific time in the past :

SQL> FLASHBACK TABLE <table_name> TO TIMESTAMP to_timestamp( '01/12/2011 17:35:00', 'DD/MM/YYYY HH24:MI:SS');

note: The flashback table could not be performed on a tables in the SYSTEM tablespace, views, V$ views and external tables. It could not be also performed if a DDL request has be performed after the targeted point in time.
note: Flashback table generates redo and undo data.

It could be possible the flashback a table to a specific SCN or restore point.

SQL> FLASHBACK TABLE <table_name> TO SCN #;

SQL> FLASHBACK TABLE <table_name> TO RESTORE POINT <restore_point_name>;

Oracle 11g: Database block recovery

There are some pre-requisite to the database block recovery :

1.  The database must be in ARCHIVELOG
2.  The database must be mounted or opened

The rman utility can use the following components for block recovery purposes:

1.  Full database backup or incremential level 0 database could be used for block recovery
2.  Archived redo log if available
3.  Flashback database logs if available

To check which blocks need recovery, you can look at:

1.  The V$DATABASE_BLOCK_CORRUPTION
2.  Results returned by the following rman commands :
•  LIST FAILURE
•  VALIADTE {DATABASE|TABLESPACE|DATAFILE}
•  BACKUP DATABASE VALIDATE
•  ANALYZE {TABLE|INDEX}
•  Trace files (for location look at the V$DIAG_INFO)
•  DBVERIFY utility

To revocver a block : After identifying the block number and associated file number of the corrupted block to repair, you can issue:

RMAN> RECOVER DATAFILE 4 BLOCK 2;
RMAN> RECOVER
  DATAFILE 1 BLOCK 9
  DATAFILE 4 BLOCK 254
  DATAFILE 5 BLOCK 454;

or to repair all block registered in the v$database_block_corruption :

RMAN> RECOVER CORRUPTION LIST;

Oracle 11g: Managing SQL Plan

Oracle says about sql plan management :
"SQL plan management prevents performance regressions resulting from sudden changes to the execution plan of a SQL statement by providing components for capturing, selecting, and evolving SQL plan information."

During the life cycle of our product some times an event causes the SQL execution plan of a sql request changed and introduce some regressions in SQL performance. From the tuner/profiler point of view, it will be relevant to be aware of this modification and check that the new SQL execution plan is correct.
This section will give some inputs to achieve this goal.

How to register SQL Plan

SQL plan management provides a mechanism that records and evaluates the execution plans of SQL statements over time, and builds SQL plan baselines.
Each SQL business request will be linked to an SQL plan baseline that contains an history of all execution plans build by oracle over the time for this request.

As a starting point, we need to capture execution plan for SQL request and add it in the SQL baseline.
This could be done automatically during a product run or could be done one by one by selecting a specific sql request.

Automatic SQL plan capture

To enable automatic plan capture, set the OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES initialization parameter to TRUE. By default, this parameter is set to FALSE.
1) Set the OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES parameter to TRUE

alter system set OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES=TRUE;

2) Run all your product use cases to capture all SQL execution plan

3) Restore the OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES parameter to its default value.

alter system set OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES=FALSE;

4) Look at the DBA_SQL_PLAN_BASELINES table to see all sql plans in relation to the schema you need to monitor:

select sql_handle, plan_name, enabled, accepted, fixed,OPTIMIZER_COST,sql_text from dba_sql_plan_baselines where PARSING_SCHEMA_NAME='FMOSSE';
SQL_HANDLE                     PLAN_NAME                      ENA ACC FIX OPTIMIZER_COST SQL_TEXT
------------------------------ ------------------------------ --- --- --- -------------- --------------------------------------------------------------------------------
SYS_SQL_495711156939d306       SQL_PLAN_4kpsj2pnmmns68e3968ca YES YES NO               0 delete from FMOSSE.S_PROPERTY where PROPERTYENTRY = :1
SYS_SQL_4a52f8b0626b170b       SQL_PLAN_4nnrsq1j6q5sb659ff4e8 YES YES NO               1 UPDATE S_ACCOUNT SET ACCOUNTSTATUS = :1  WHERE (ACCOUNTID = :2 )
SYS_SQL_4c596a52d15cd98f       SQL_PLAN_4sqbaab8ptqcg657f43ee YES YES NO               2 SELECT COUNT(FILEID) FROM S_FILE
SYS_SQL_4db332738e676fb7       SQL_PLAN_4vctkff76fvxr5c9cc6bc YES YES NO               2 select FOLDERID, FOLDERNAME, FOLDERDATECREATION, FOLDERIDPARENT, FOLDERSHARED, F
SYS_SQL_4e84758c387d2c28       SQL_PLAN_4x13pjhw7ub181e49339d YES YES NO               2 delete from FMOSSE.S_FILE
SYS_SQL_4fc3800bd9da7784       SQL_PLAN_4zhw01gcxnxw4291c9d40 YES YES NO               1 SELECT FOLDERID, FOLDERSHARED, FOLDERNAME, FOLDERSIZE, FOLDERDATECREATION, FOLDE
SYS_SQL_513d44ab9e0b94d8       SQL_PLAN_52ga4pfg0r56sacc4c75e YES YES NO               9 select count(*), sum(f.filesize), round(avg(f.filesize), 2), max(f.filesize), mi
SYS_SQL_51a8ae22d631730b       SQL_PLAN_53a5f4bb32wsb7b701d8a YES YES NO               3 select PROPERTYENTRY, PROPERTYVALUE from FMOSSE.S_PROPERTY order by PROPERTYENTR
SYS_SQL_51e053d0f1bfb831       SQL_PLAN_53s2mu3svzf1jc7624a29 YES YES NO               4 SELECT t0.ACCOUNTID, t0.ACCOUNTNAME, COUNT(t3.FILEID), TO_CHAR(NVL(SUM(t3.FILESI
SYS_SQL_521f93c697ccfbb2       SQL_PLAN_547wmsubwtyxk29b1bccc YES YES NO               2 select arf.accountid from s_accountresourcefolder arf inner join s_folder f on f
SYS_SQL_56ceb93fab2da70d       SQL_PLAN_5dmpt7ypkv9sd42ba1be5 YES YES NO               0 DELETE FROM S_ACCOUNT WHERE (ACCOUNTID = :1 )

Manual SQL plan capture

1) To capture a sql execution plan manually, you have to take a look at the V$SQL view and select the request you need to monitor :

SELECT sql_id, sql_fulltext FROM V$SQL WHERE sql_text LIKE '%SELECT COUNT(FILEID)%' and PARSING_SCHEMA_NAME='FMOSSE';
SQL_ID        SQL_FULLTEXT
------------- --------------------------------------------------------------------------------
1xjd9vbxt5f7w SELECT COUNT(FILEID) FROM S_FILE

2) Using the SQL_ID create a SQL plan baseline for the statement.
variable cnt number;

EXECUTE :cnt :=DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE(sql_id=>'1xjd9vbxt5f7w');

3) Check the DBA_SQL_PLAN_BASELINES tables

select B.SQL_HANDLE, B.PLAN_NAME, B.SQL_TEXT, B.ORIGIN, B.PARSING_SCHEMA_NAME, B.ENABLED, B.ACCEPTED, B.FIXED from DBA_SQL_PLAN_BASELINES B, V$SQL S WHERE S.SQL_PLAN_BASELINE=B.PLAN_NAME AND S.SQL_ID='1xjd9vbxt5f7w';
SQL_HANDLE                     PLAN_NAME                      SQL_TEXT                          ORIGIN         PARSIN ENA ACC FIX
------------------------------ ------------------------------ --------------------------------- -------------- ------ --- --- ---
SYS_SQL_4c596a52d15cd98f       SQL_PLAN_4sqbaab8ptqcg657f43ee SELECT COUNT(FILEID) FROM S_FILE  AUTO-CAPTURE   FMOSSE YES YES NO

SQL Plan Baseline activation

Now we need to indicate to the optimizer to refere to the SQL plan baseline defined for each request :

alter system set optimizer_use_sql_plan_baselines=TRUE;

The optimizer will initially look for a given query if there is a baseline and compare both the cost of the execution plan from the baseline and cost of the execution plan of the current query. If the cost has changed and the execution plan of the query is better then the cost of the execution plan of the baseline then the new execution plan will be added to the baseline.

To illustrate this:
1) I create first a table and populate some data in it.

create table test (id NUMBER, VALUE VARCHAR2) NOLOGGING;
declare
  i number;
begin
  for i in 1..300000 loop
    insert /*+ APPEND */ into test values( i, 'LABEL# ' || i);
  end loop;
  commit;
end;

2) Then i perform a select :

alter system set cursor_sharing=force;

select * from test where id=215445;

3) Using the v$sql view, i found the sql_id of the new request and load its execution plan in the sql plan baseline:

variable cnt number;
EXECUTE :cnt :=DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE(sql_id=>'05abfh82j5z85');

4) Now i display the baseline

select SQL_HANDLE, PLAN_NAME, OPTIMIZER_COST, SQL_TEXT, ORIGIN, PARSING_SCHEMA_NAME, ENABLED, ACCEPTED, FIXED from DBA_SQL_PLAN_BASELINES;
SQL_HANDLE                     PLAN_NAME                      OPTIMIZER_COST SQL_TEXT                                                                      ORIGIN          PARSING_SCHEMA_NAME            ENA ACC FIX
------------------------------ ------------------------------ -------------- -------------------------------------------------------------------------------- -------------- ------------------------------ --- --- ---
SYS_SQL_d223cef6c0ff72ee       SQL_PLAN_d48yfyv0gywrf97bbe3d0            104 select * from test where id=:"SYS_B_0"                                        MANUAL-LOAD    TEST                            YES YES NO

5) Now, i create an contraint on the id column and so on an associated index:
alter table test add constraint PK_TEST_ID primary key( id);
Do an other select with a different id value.

6) Look again for the baseline:

select SQL_HANDLE, PLAN_NAME, OPTIMIZER_COST, SQL_TEXT, ORIGIN, PARSING_SCHEMA_NAME, ENABLED, ACCEPTED, FIXED from DBA_SQL_PLAN_BASELINES;
SQL_HANDLE                     PLAN_NAME                      OPTIMIZER_COST SQL_TEXT                                                                      ORIGIN          PARSING_SCHEMA_NAME            ENA ACC FIX
------------------------------ ------------------------------ -------------- -------------------------------------------------------------------------------- -------------- ------------------------------ --- --- ---
SYS_SQL_d223cef6c0ff72ee       SQL_PLAN_d48yfyv0gywrf97bbe3d0            104 select * from test where id=:"SYS_B_0"                                        MANUAL-LOAD    TEST                            YES YES NO
SYS_SQL_d223cef6c0ff72ee       SQL_PLAN_d48yfyv0gywrf7ad59d6c              2 select * from test where id=:"SYS_B_0"                                        AUTO-CAPTURE   TEST                            YES NO  NO

What we can see it that a new sql plan has been added in the baseline with a better optimizer cost (2 instead of 104).
We can also see that the first sql plan has been added manually in the baseline (ORIGIN=MANUAL-LOAD) and that the second one has been added by oracle itself (ORIGIN=AUTO-CAPTURE).
As we add a new index, the sql plan of the request as changed due to a database structure modification. In this case the new plan has been added in the base line with ENABLED=YES (could be choose by the optimizer) but with ACCEPTED=NO due to the fact that the DBA has not yet validate the new SQL plan.
If we want to force the use of the first plan we need to set the plan as FIXED :

variable cnt number;
exec :cnt :=DBMS_SPM.ALTER_SQL_PLAN_BASELINE(
SQL_HANDLE => 'SYS_SQL_d223cef6c0ff72ee',
PLAN_NAME => 'SQL_PLAN_d48yfyv0gywrf97bbe3d0',
ATTRIBUTE_NAME => 'fixed',
ATTRIBUTE_VALUE => 'YES');

How to overwrite an existing execution plan

Some time during a profiling session we need to overwrite an execution plan for a specific request because we improve its performance.
To do that we need :

1) to disable the current sql plan in the baseline:

variable cnt number;
exec :cnt :=DBMS_SPM.ALTER_SQL_PLAN_BASELINE(
    SQL_HANDLE => 'SYS_SQL_d223cef6c0ff72ee',
    PLAN_NAME => 'SQL_PLAN_d48yfyv0gywrf97bbe3d0',
    ATTRIBUTE_NAME => 'enabled',
    ATTRIBUTE_VALUE => 'NO'
);

2) register the new plan under the same sql_handle but with the sql_id and plan_hash_value of the new request (values found in the v$sql view)

variable cnt number;
exec :cnt :=DBMS_SPM.ALTER_SQL_PLAN_BASELINE(
    SQL_ID => '72fb94sq0karh',
    PLAN_HASH_VALUE => '1357081020',
    SQL_HANDLE => 'SYS_SQL_d223cef6c0ff72ee'
);

3) Ensure that the new plan has been accepted in the base line :

select SQL_HANDLE, PLAN_NAME, OPTIMIZER_COST, SQL_TEXT, ORIGIN, PARSING_SCHEMA_NAME, ENABLED, ACCEPTED, FIXED from DBA_SQL_PLAN_BASELINES;
SQL_HANDLE                     PLAN_NAME                      OPTIMIZER_COST SQL_TEXT                                                                      ORIGIN          PARSING_SCHEMA_NAME            ENA ACC FIX
------------------------------ ------------------------------ -------------- -------------------------------------------------------------------------------- -------------- ------------------------------ --- --- ---
SYS_SQL_d223cef6c0ff72ee       SQL_PLAN_d48yfyv0gywrf97bbe3d0            2 select * from test where id=:"SYS_B_0"                                        MANUAL-LOAD    TEST                            YES YES NO

How to display an execution plan registered under the baseline

To view the plans stored in the SQL plan baseline for a given statement, use the DISPLAY_SQL_PLAN_BASELINE function of the DBMS_XPLAN package:

select * from table(
    dbms_xplan.display_sql_plan_baseline(
        sql_handle=>'SYS_SQL_d223cef6c0ff72ee',
        format=>'basic'));
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

--------------------------------------------------------------------------------
SQL handle: SYS_SQL_d223cef6c0ff72ee
SQL text: select * from test where id=:"SYS_B_0"
--------------------------------------------------------------------------------

--------------------------------------------------------------------------------
Plan name: SQL_PLAN_d48yfyv0gywrf7ad59d6c         Plan id: 2060819820
Enabled: YES     Fixed: NO      Accepted: NO      Origin: AUTO-CAPTURE
--------------------------------------------------------------------------------


PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 3803811902

--------------------------------------------------
| Id  | Operation                   | Name       |
--------------------------------------------------
|   0 | SELECT STATEMENT            |            |
|   1 |  TABLE ACCESS BY INDEX ROWID| TEST       |
|   2 |   INDEX UNIQUE SCAN         | PK_TEST_ID |
--------------------------------------------------

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

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan name: SQL_PLAN_d48yfyv0gywrf97bbe3d0         Plan id: 2545673168
Enabled: YES     Fixed: NO      Accepted: YES     Origin: MANUAL-LOAD
--------------------------------------------------------------------------------

Plan hash value: 1357081020

----------------------------------
| Id  | Operation         | Name |
----------------------------------
|   0 | SELECT STATEMENT  |      |
|   1 |  TABLE ACCESS FULL| TEST |

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
----------------------------------

34 rows selected.

Oracle documentation

http://download.oracle.com/docs/cd/B28359_01/server.111/b28274/optplanmgmt.htm

Oracle 11g: How to restore a backup

Commands used by RMAN are :

    - RESTORE Used to retrieve a set of files from a backup.

RESTORE {DATABASE | TABLESPACE name [,name] | DATAFILE name [,name]}

You can restore the database to a specific SCN, time, restore point or redo log sequence number.

    - RECOVER Applies modifications registered inside incremental backups, archived log and redo logs to all the files previously restored.

RECOVER {DATABASE | TABLESPACE name [,name] | DATAFILE name [,name]}

How to restore/recover an entire database

RMAN> SHUTDOWN IMMEDIATE;
RMAN> STARTUP MOUNT;
RESTORE DATABASE;
RECOVER DATABASE;
ALTER DATABASE OPEN RESETLOGS;

note: In case of you lost your online redo logs, you can indicate to RMAN that during the recovery procress you do not want to apply redo logs :

RMAN> RECOVER DATABASE NOREDO;

How to restore/recover a tablespace
If the tablespace is not a system tablespace then the database could be opened during the restoration otherwise the database must be in MOUNT.

RMAN> sql 'alter tablespace users offline immediate';
RMAN> RESTORE TABLESPACE USERS;
RMAN> RECOVER TABLESPACE USERS;
RMAN> sql 'alter tablespace users online';

How to restore/recover a datafile
If the datafile is not a datafile of a system tablespace then the database could be opened during the restoration otherwise the database must be in MOUNT.
Anyway the datafile must be set offline before the restoration.

RMAN> sql 'alter database datafile 4 offline immediate';
RMAN> RESTORE DATAFILE 4;
RMAN> RECOVER DATAFILE 4;
RMAN> sql 'alter database datafile 4 online';

How to restore/recover a database until the last successful transaction

RMAN> RESTORE DATABASE UNTIL CANCEL;
RMAN> RECOVER DATABASE UNTIL CANCEL;

How to restore/recover a database at a specific point in time
The following example restore a database as it should be 10 minutes before :

RMAN> RECOVER DATABASE UNTIL TIME 'sysdate-10/60/24';

If you use a specific time, it is then necessary to define the NLS_LANG & NLS_DATE_FORMAT system parameters. These parameters will be used by rman to decode the date ans time passed for the restoration.

$> export NLS_LANG = american_america.us7ascii
$> export NLS_DATE_FORMAT = 'DD/MM/YYYY HH24:MI:SS'
$> RMAN target / catalog rcatowner/rcatowner@rcat_db
RMAN> RESTORE DATABASE UNTIL TIME '18/11/2011 10:40:00';
RMAN> RECOVER DATABASE UNTIL TIME '18/11/2011 10:40:00';
RMAN> ALTER DATABASE OPEN RESETLOGS;

or using the SET command :

$> export NLS_LANG = american_america.us7ascii
$> export NLS_DATE_FORMAT = 'DD/MM/YYYY HH24:MI:SS'
$> RMAN target / catalog rcatowner/rcatowner@rcat_db
RMAN> run {
SET UNTIL TIME '18/11/2011 10:40:00';
RESTORE DATABASE;
RECOVER DATABASE;
}
RMAN> ALTER DATABASE OPEN RESETLOGS;

remark : If you want to check that the point in time is correct by checking all the data restore, you need to open the database in READ ONLY. By this way, the redo logs will not be reseted and you can move your point in time if not relevant.

RMAN> ALTER DATABASE OPEN READ ONLY;

Once the point in time is correct you can open the database and reset the redo logs.

RMAN> ALTER DATABASE OPEN RESETLOGS;

This remark could also be applied for restoration at a specific SCN or sequence number.

How to restore/recover a database at a specific SCN

RMAN> SHUTDOWN IMMEDIATE;
RMAN> STARTUP MOUNT;
RMAN> run {
SET UNTIL SCN #;
RESTORE DATABASE;
RECOVER DATABASE;
}
RMAN> ALTER DATABASE OPEN RESETLOGS;

remark :"SET UNTIL" DOESN'T WORK WITH A TABLESPACE!. It is not possible to restore only one tablespace at a specific time in the past. The database will not open.

remark :If the SCN you need to reach is not part of the current incarnation of the database, you need to reset the database to the incarnation juste before your scn and after perform the recovery. Here is the example of the restoration of the database at the SCN 5726167:

RMAN> run {
2> set until scn 5726167;
3> restore database;
4> recover database;
5> }

executing command: SET until clause

Starting restore at 18-11-11 13:28:10
using channel ORA_DISK_1
using channel ORA_SBT_TAPE_1
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of restore command at 11/18/2011 13:28:10
RMAN-20208: UNTIL CHANGE is before RESETLOGS change

RMAN> list incarnation;
List of Database Incarnations
DB Key  Inc Key DB Name  DB ID            STATUS  Reset SCN  Reset Time
------- ------- -------- ---------------- --- ---------- ----------
1       1       TEST     2058702964       PARENT  1          13-08-09 23:00:48
2       2       TEST     2058702964       PARENT  754488     30-08-11 17:42:15
3       3       TEST     2058702964       PARENT  5571932    16-11-11 14:39:08
4       4       TEST     2058702964       ORPHAN  5576815    16-11-11 16:11:53
5       5       TEST     2058702964       ORPHAN  5577397    16-11-11 16:18:46
6       6       TEST     2058702964       ORPHAN  5598121    16-11-11 16:37:33
7       7       TEST     2058702964       PARENT  5576413    16-11-11 18:02:08
8       8       TEST     2058702964       CURRENT 5726169    18-11-11 11:41:56

RMAN> shutdown immediate;
RMAN> startup mount;
RMAN> reset database to incarnation 7;
database reset to incarnation 7

RMAN> run {
2> set until scn 5726167;
3> restore database;
4> recover database;
5> }

RMAN> ALTER DATABASE OPEN RESETLOGS;

note : The RESET DATABASE need to alter the control file, so the database must be in mount state but not open. In case of an opend database you will receive the following exception : ORA-19910: can not change recovery target incarnation in control file

How to restore/recover a database at a specific sequence number

RMAN> SHUTDOWN IMMEDIATE;
RMAN> STARTUP MOUNT;
RMAN> run {
SET UNTIL SEQUENCE #;
RESTORE DATABASE;
RECOVER DATABASE;
}
RMAN> ALTER DATABASE OPEN RESETLOGS;

How to restore a control file
To restore a control file the database must be in NOMOUNT state:

RMAN> STARTUP NOMOUNT;
RMAN> RESTORE CONTROLFILE;
RMAN> ALTER DATABASE MOUNT;
RMAN> RECOVER DATABASE;
RMAN> ALTER DATABASE OPEN RESETLOGS;

If you want to restore from an autobackup and you have an rman catalog :

RMAN> RESTORE CONTROLFILE FROM AUTOBACKUP;

If you don't have a catalog, you need to define the DBID:

RMAN> SET DBID 1090778584;
RMAN> RESTORE CONTROLFILE FROM AUTOBACKUP;

How to restore a parameter file
To restore a parameter file, the database must be in NOMOUNT state:

RMAN> STARTUP FORCE NOMOUNT;
RMAN> RESTORE SPFILE FROM AUTOBACKUP;
RMAN> STARTUP FORCE;

You can change the name of the parameter file :

RMAN> RESTORE SPFILE TO <parameter_file_name> FROM AUTOBACKUP;

Using sql, it is possible to generate the parameter file using

SQL> CREATE PFILE [='pfile_name']
FROM { { SPFILE [='spfile_name']} | MEMORY};

or

SQL> CREATE SFILE [='sfile_name']
FROM { { PFILE [='pfile_name']} | MEMORY};

How to restore/recover a database at a specific incarnation

RMAN> LIST INCARNATION;
RMAN> RESET DATABASE TO INCARNATION #;

Oracle 11g: Restore points

A restore point gives a name to a point in time.

You can create a restore point at the current time :

SQL> CREATE RESTORE POINT <name_of__restore_point>;

or create a point in the past :

SQL> CREATE RESTORE POINT <name_of__restore_point> AS OF SCN #;

The restore point is stored in the control file within the period of retention defined by the CONTROL_FILE_RECORD_KEEP_TIME parameter.
If you want the restore point never expires add the PRESERVE key word at the previous commands.

SQL> CREATE RESTORE POINT <name_of__restore_point> PRESERVE;

You can see all restore points created through the database view V$RESTORE_POINT or use RMAN:

RMAN> LIST RESTORE POINT ALL;

You can delete a restore point using:

SQL> DROP RESTORE POINT <name_of__restore_point>;

Oracle 11g: How to trace in alert log file

To trace sonn messahe in the alert log file :

exec dbms_system.ksdwrt(2, 'This is a message sent at ' || TO_CHAR(SYSDATE, 'dd/mm/yyyy HH24"H"MI'));

note : For more information about dbms_system package see http://psoug.org/reference/dbms_system.html

Oracle 11g: Monitoring RMAN backups

If you are connected as SYSDBA

The following dynamic views give information about backups performance and statistics:

View Name	Description
V$BACKUP_SET	Backupset created
V$BACKUP_PIECE	All backup piece created
V$DATAFILE_COPY	All image copies created
V$BACKUP_FILES	All files created during backups
V$BACKUP_DATAFILE	All datafiles backuped and gives information about the moniroting of incremential backups

If you are connected as the catalog owner

View Name	Description
RC_BACKUP_SET	Backupset created
RC_BACKUP_PIECE	All backup piece created
RC_DATAFILE_COPY	All image copies created
RC_BACKUP_FILES	All files created during backups
RC_BACKUP_DATAFILE	All datafiles backuped and gives information about the moniroting of incremential backups

note: The following sql request gives information about the number of block read for an incremental level 1 backup.

sqlplus / as sysdba
SQL> select file#,
         avg(datafile_blocks),
         avg(blocks_read),
         avg(blocks_read/datafile_blocks) * 100 as PCT_READ_FOR_BACKUP,
         avg(blocks)
  from v$backup_datafile
  where used_change_tracking = 'YES'
        and incremental_level > 0
  group by file#;

Incremental level 1 backup are interesting as the value of the PCT_READ_FOR_BACKUP column is less than 50%. Above this value, it is recommanded to perform a level 0 incremential backup.

Oracle 11g: RMAN catalog resynchronization

To synchronize the target database control file with the catalog :

RAM> RESYNC CATALOG;

To check if backups on file system are deleted but still referenced in the rman catalog:
    a) for backupset

RMAN> crosscheck backupset;
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=143 device type=DISK
using channel ORA_SBT_TAPE_1
crosschecked backup piece: found to be 'EXPIRED'
backup piece handle=43mraiho_1_1 RECID=59 STAMP=766855736
crosschecked backup piece: found to be 'EXPIRED'
backup piece handle=44mrail9_1_1 RECID=60 STAMP=766855849
crosschecked backup piece: found to be 'EXPIRED'
backup piece handle=4cmraj7q_1_1 RECID=62 STAMP=766856442
crosschecked backup piece: found to be 'EXPIRED'
backup piece handle=4dmral4o_1_1 RECID=63 STAMP=766858392
crosschecked backup piece: found to be 'EXPIRED'
backup piece handle=4lmranr6_1_1 RECID=65 STAMP=766861158
crosschecked backup piece: found to be 'EXPIRED'
backup piece handle=4mmranun_1_1 RECID=66 STAMP=766861271
crosschecked backup piece: found to be 'EXPIRED'
backup piece handle=4nmrao37_1_1 RECID=67 STAMP=766861415
Crosschecked 7 objects

For a specific device type

RMAN> crosscheck backupset device type sbt;

    b) for image copy

RMAN> crosscheck copy;

The crosscheck check if the backup still exists on file system; if not the backuppiece is marked as EXPIRED.

To list expired backupset :

RMAN> list expired backupset;
RMAN> list expired backupset summary;

To list expired copy :

RMAN> list expired copy;

To delete expired backupset or copy:

RMAN> delete expired backupset;
RMAN> delete expired copy;

It is also possible to check for obsolete backup. An obsolete backup is a backup that is not used to garantee the retention policy.
To check for obsolete backup :

RMAN> report obsolete;

To delete obsolete backup :

RMAN> delete obsolete;

Oracle11g: Backuping backups with RMAN

The goal of this section is to show how to transfert a set of backupset to a tape (image copy could not be transfered on tape).

Transfering a copy of the database, datafile,tablespace to tape

First of all, we need for the example to configure the sbt device type to a location on the file system in order to simulate a tape.
Here we would like to ensure that when using the sbt channel all data are redirected on disk at the '/database/TEST/flash_recovery_area/TEST/backupset/tape' location :

RMAN> configure channel device type sbt
parms='SBT_LIBRARY=oracle.disksbt, ENV=(BACKUP_DIR=/database/TEST/flash_recovery_area/TEST/backupset/tape)';

new RMAN configuration parameters:
CONFIGURE CHANNEL DEVICE TYPE 'SBT_TAPE' PARMS  'SBT_LIBRARY=oracle.disksbt, ENV=(BACKUP_DIR=/database/TEST/flash_recovery_area/TEST/backupset/tape)';
new RMAN configuration parameters are successfully stored
starting full resync of recovery catalog
full resync complete

Now we would like to make a backupset that is a copy of all image copies to the tape using the sbt channel (backup copy of {database|datafile|tablespace}).

RMAN> backup as backupset device type sbt copy of database delete all input;
Starting backup at 10-NOV-11
using channel ORA_SBT_TAPE_1
channel ORA_SBT_TAPE_1: starting full datafile backup set
channel ORA_SBT_TAPE_1: including datafile copy of datafile 00001 in backup set
input file name=/database/TEST/flash_recovery_area/TEST/datafile/o1_mf_system_7cqqswb2_.dbf
channel ORA_SBT_TAPE_1: including datafile copy of datafile 00002 in backup set
input file name=/database/TEST/flash_recovery_area/TEST/datafile/o1_mf_sysaux_7cqqtpj6_.dbf
channel ORA_SBT_TAPE_1: including datafile copy of datafile 00003 in backup set
input file name=/database/TEST/flash_recovery_area/TEST/datafile/o1_mf_undotbs1_7cqqvgpl_.dbf
channel ORA_SBT_TAPE_1: including datafile copy of datafile 00004 in backup set
input file name=/database/TEST/flash_recovery_area/TEST/datafile/o1_mf_users_7cqqvoro_.dbf
channel ORA_SBT_TAPE_1: including datafile copy of datafile 00005 in backup set
input file name=/database/TEST/flash_recovery_area/TEST/datafile/o1_mf_rcat_ts_7cqqvs26_.dbf
channel ORA_SBT_TAPE_1: starting piece 1 at 10-NOV-11
channel ORA_SBT_TAPE_1: finished piece 1 at 10-NOV-11
piece handle=44mrail9_1_1 comment=API Version 2.0,MMS Version 8.1.3.0
channel ORA_SBT_TAPE_1: backup set complete, elapsed time: 00:00:45
deleted datafile copy
datafile copy file name=/database/TEST/flash_recovery_area/TEST/datafile/o1_mf_system_7cqqswb2_.dbf RECID=26 STAMP=766855645
deleted datafile copy
datafile copy file name=/database/TEST/flash_recovery_area/TEST/datafile/o1_mf_sysaux_7cqqtpj6_.dbf RECID=27 STAMP=766855673
deleted datafile copy
datafile copy file name=/database/TEST/flash_recovery_area/TEST/datafile/o1_mf_undotbs1_7cqqvgpl_.dbf RECID=28 STAMP=766855681
deleted datafile copy
datafile copy file name=/database/TEST/flash_recovery_area/TEST/datafile/o1_mf_users_7cqqvoro_.dbf RECID=29 STAMP=766855687
deleted datafile copy
datafile copy file name=/database/TEST/flash_recovery_area/TEST/datafile/o1_mf_rcat_ts_7cqqvs26_.dbf RECID=30 STAMP=766855689
Finished backup at 10-NOV-11

remark: Using DELETE ALL INPUT ensure that all copy moved to tape are deleted from the disk.

remark: To backup a specific backup, you can use the Tag syntax

RMAN> backup device type sbt copy of database from tag 'MY_BACKUP' Tag 'TAPE_BACKUP' delete all input;

remark: To backup all archivelog to tape:

RMAN> backup device type sbt archivelog all delete all input;

Starting backup at 10-NOV-11
current log archived
using channel ORA_SBT_TAPE_1
channel ORA_SBT_TAPE_1: starting archived log backup set
channel ORA_SBT_TAPE_1: specifying archived log(s) in backup set
input archived log thread=1 sequence=267 RECID=96 STAMP=766855623
input archived log thread=1 sequence=268 RECID=97 STAMP=766855624
input archived log thread=1 sequence=269 RECID=98 STAMP=766855625
input archived log thread=1 sequence=270 RECID=99 STAMP=766855627
input archived log thread=1 sequence=271 RECID=101 STAMP=766855693
input archived log thread=1 sequence=272 RECID=102 STAMP=766861270
channel ORA_SBT_TAPE_1: starting piece 1 at 10-NOV-11
channel ORA_SBT_TAPE_1: finished piece 1 at 10-NOV-11
piece handle=4mmranun_1_1 tag=TAG20111110T170111 comment=API Version 2.0,MMS Version 8.1.3.0
channel ORA_SBT_TAPE_1: backup set complete, elapsed time: 00:00:01
channel ORA_SBT_TAPE_1: deleting archived log(s)
archived log file name=/database/TEST/flash_recovery_area/TEST/archivelog/2011_11_10/o1_mf_1_267_7cqqsqln_.arc RECID=96 STAMP=766855623
archived log file name=/database/TEST/flash_recovery_area/TEST/archivelog/2011_11_10/o1_mf_1_267_7cqocxbx_.arc RECID=91 STAMP=766853133
archived log file name=/database/TEST/flash_recovery_area/TEST/archivelog/2011_11_10/o1_mf_1_267_7cqo77yt_.arc RECID=89 STAMP=766852984
archived log file name=/database/TEST/flash_recovery_area/TEST/archivelog/2011_11_10/o1_mf_1_268_7cqqsrps_.arc RECID=97 STAMP=766855624
archived log file name=/database/TEST/flash_recovery_area/TEST/archivelog/2011_11_10/o1_mf_1_268_7cqocyh3_.arc RECID=92 STAMP=766853134
archived log file name=/database/TEST/flash_recovery_area/TEST/archivelog/2011_11_10/o1_mf_1_268_7cqocw57_.arc RECID=90 STAMP=766853132
archived log file name=/database/TEST/flash_recovery_area/TEST/archivelog/2011_11_10/o1_mf_1_269_7cqqssvk_.arc RECID=98 STAMP=766855625
archived log file name=/database/TEST/flash_recovery_area/TEST/archivelog/2011_11_10/o1_mf_1_269_7cqolrj4_.arc RECID=94 STAMP=766853352
archived log file name=/database/TEST/flash_recovery_area/TEST/archivelog/2011_11_10/o1_mf_1_269_7cqok20g_.arc RECID=93 STAMP=766853298
archived log file name=/database/TEST/flash_recovery_area/TEST/archivelog/2011_11_10/o1_mf_1_270_7cqqsv0o_.arc RECID=99 STAMP=766855627
archived log file name=/database/TEST/flash_recovery_area/TEST/archivelog/2011_11_10/o1_mf_1_270_7cqqspg8_.arc RECID=95 STAMP=766855622
archived log file name=/database/TEST/flash_recovery_area/TEST/archivelog/2011_11_10/o1_mf_1_271_7cqqvxrq_.arc RECID=101 STAMP=766855693
archived log file name=/database/TEST/flash_recovery_area/TEST/archivelog/2011_11_10/o1_mf_1_271_7cqqvwjz_.arc RECID=100 STAMP=766855692
archived log file name=/database/TEST/flash_recovery_area/TEST/archivelog/2011_11_10/o1_mf_1_272_7cqxb5w9_.arc RECID=102 STAMP=766861270
Finished backup at 10-NOV-11

remark: To backup some datafile to tape:

RMAN> backup device type sbt copy of datafile 4,5 delete input;
Starting backup at 10-NOV-11
released channel: ORA_DISK_1
allocated channel: ORA_SBT_TAPE_1
channel ORA_SBT_TAPE_1: SID=146 device type=SBT_TAPE
channel ORA_SBT_TAPE_1: WARNING: Oracle Test Disk API
channel ORA_SBT_TAPE_1: starting full datafile backup set
channel ORA_SBT_TAPE_1: including datafile copy of datafile 00004 in backup set
input file name=/database/TEST/flash_recovery_area/TEST/datafile/o1_mf_users_7cqcf30k_.dbf
channel ORA_SBT_TAPE_1: including datafile copy of datafile 00005 in backup set
input file name=/database/TEST/flash_recovery_area/TEST/datafile/o1_mf_rcat_ts_7cqprkm1_.dbf
channel ORA_SBT_TAPE_1: starting piece 1 at 10-NOV-11
channel ORA_SBT_TAPE_1: finished piece 1 at 10-NOV-11
piece handle=4omrao7v_1_1 comment=API Version 2.0,MMS Version 8.1.3.0
channel ORA_SBT_TAPE_1: backup set complete, elapsed time: 00:00:01
deleted datafile copy
datafile copy file name=/database/TEST/flash_recovery_area/TEST/datafile/o1_mf_users_7cqcf30k_.dbf RECID=21 STAMP=766842932
deleted datafile copy
datafile copy file name=/database/TEST/flash_recovery_area/TEST/datafile/o1_mf_rcat_ts_7cqprkm1_.dbf RECID=25 STAMP=766854561
Finished backup at 10-NOV-11

How to delete a copy of backup from a tape

To delete a copy of backupset from the tape, you can write :

RMAN> detele backupset <bs_key> device type sbt;

Transfering a copy of a backupset to tape

To copy a backupset to tape you can use the syntax

RMAN> backup device type sbt BACKUPSET <bs_key>;

To copy all backupset:

RMAN> backup device type sbt BACKUPSET ALL;

Transfering the recovery area to tape

It is possible to backup the recovery area to tape (not possible for device type disk)

RMAN> backup RECOVERY AREA;

note: The backup of recovery area doesn't backed up files that have been previously backed up. Use FORCE option to ensure that all files will be backed up each time the backup recovery area is called:

RMAN> BACKUP device type sbt RECOVERY AREA FORCE;

It is possible to copy all files of the recovery area even those how are not database files.

RMAN> backup RECOVERY FILES;

Oracle 11g: RMAN's backup

This section will explain how to perform backup of a database or datafile using RMAN.
Files generated by RMAN can be either :
  - an image copy (strict binary copy of database files)
  - a set of backupsets (a set of binary files that contains severals pieces of the database). The backupset does not contains empty blocks or blocks allocated over the High Water Mark.

How to backup the entire database as backupsets

To backup the database as a backupset you can issue the following command:

$> export ORACLE_SID=TEST

$> rman target / catalog rcatowner/rcatowner@rcat_db

RMAN> BACKUP AS BACKUPSET database;
Starting backup at 10-NOV-11
using channel ORA_DISK_1
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00001 name=/database/TEST/system/system01.dbf
input datafile file number=00005 name=/database/TEST/rcat/rcat01.dbf
input datafile file number=00002 name=/database/TEST/system/sysaux01.dbf
input datafile file number=00003 name=/database/TEST/undo/undotbs01.dbf
channel ORA_DISK_1: starting piece 1 at 10-NOV-11
channel ORA_DISK_1: finished piece 1 at 10-NOV-11
piece handle=/database/TEST/flash_recovery_area/TEST/backupset/2011_11_10/o1_mf_nnndf_TAG20111110T104845_7cq7hxmg_.bkp tag=TAG20111110T104845 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:45
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00004 name=/database/TEST/users/users01.dbf
channel ORA_DISK_1: starting piece 1 at 10-NOV-11
channel ORA_DISK_1: finished piece 1 at 10-NOV-11
piece handle=/database/TEST/flash_recovery_area/TEST/backupset/2011_11_10/o1_mf_nnndf_TAG20111110T104845_7cq7kbgs_.bkp tag=TAG20111110T104845 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:03
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
including current control file in backup set
including current SPFILE in backup set
channel ORA_DISK_1: starting piece 1 at 10-NOV-11
channel ORA_DISK_1: finished piece 1 at 10-NOV-11
piece handle=/database/TEST/flash_recovery_area/TEST/backupset/2011_11_10/o1_mf_ncsnf_TAG20111110T104845_7cq7kgol_.bkp tag=TAG20111110T104845 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 10-NOV-11

All datafiles, controlfiles and associated spfile are backuped as a backupset.

You can list the backupset to see its content :

RMAN> list backupset;

List of Backup Sets
===================

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
2661    Full    1.19G      DISK        00:00:41     10-NOV-11
        BP Key: 2664   Status: AVAILABLE  Compressed: NO  Tag: TAG20111110T104845
        Piece Name: /database/TEST/flash_recovery_area/TEST/backupset/2011_11_10/o1_mf_nnndf_TAG20111110T104845_7cq7hxmg_.bkp
  List of Datafiles in backup set 2661
  File LV Type Ckp SCN    Ckp Time  Name
  ---- -- ---- ---------- --------- ----
  1       Full 5141811    10-NOV-11 /database/TEST/system/system01.dbf
  2       Full 5141811    10-NOV-11 /database/TEST/system/sysaux01.dbf
  3       Full 5141811    10-NOV-11 /database/TEST/undo/undotbs01.dbf
  5       Full 5141811    10-NOV-11 /database/TEST/rcat/rcat01.dbf

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
2662    Full    33.46M     DISK        00:00:01     10-NOV-11
        BP Key: 2665   Status: AVAILABLE  Compressed: NO  Tag: TAG20111110T104845
        Piece Name: /database/TEST/flash_recovery_area/TEST/backupset/2011_11_10/o1_mf_nnndf_TAG20111110T104845_7cq7kbgs_.bkp
  List of Datafiles in backup set 2662
  File LV Type Ckp SCN    Ckp Time  Name
  ---- -- ---- ---------- --------- ----
  4       Full 5141833    10-NOV-11 /database/TEST/users/users01.dbf

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
2663    Full    9.61M      DISK        00:00:01     10-NOV-11
        BP Key: 2666   Status: AVAILABLE  Compressed: NO  Tag: TAG20111110T104845
        Piece Name: /database/TEST/flash_recovery_area/TEST/backupset/2011_11_10/o1_mf_ncsnf_TAG20111110T104845_7cq7kgol_.bkp
  SPFILE Included: Modification time: 10-NOV-11
  SPFILE db_unique_name: TEST
  Control File Included: Ckp SCN: 5141834      Ckp time: 10-NOV-11

Note that the type of the backup is FULL and performed on the disk.

You can check that no images copies are generated:

RMAN> list copy;

specification does not match any datafile copy in the repository
specification does not match any control file copy in the repository
specification does not match any archived log in the repository

remark: You can delete the backupset using the following commands

RMAN> delete backupset 2661,2662,2663;

using channel ORA_DISK_1

List of Backup Pieces
BP Key  BS Key  Pc# Cp# Status      Device Type Piece Name
------- ------- --- --- ----------- ----------- ----------
2664    2661    1   1   AVAILABLE   DISK        /database/TEST/flash_recovery_area/TEST/backupset/2011_11_10/o1_mf_nnndf_TAG20111110T104845_7cq7hxmg_.bkp
2665    2662    1   1   AVAILABLE   DISK        /database/TEST/flash_recovery_area/TEST/backupset/2011_11_10/o1_mf_nnndf_TAG20111110T104845_7cq7kbgs_.bkp
2666    2663    1   1   AVAILABLE   DISK        /database/TEST/flash_recovery_area/TEST/backupset/2011_11_10/o1_mf_ncsnf_TAG20111110T104845_7cq7kgol_.bkp

Do you really want to delete the above objects (enter YES or NO)? y
deleted backup piece
backup piece handle=/database/TEST/flash_recovery_area/TEST/backupset/2011_11_10/o1_mf_nnndf_TAG20111110T104845_7cq7hxmg_.bkp RECID=33 STAMP=766838925
deleted backup piece
backup piece handle=/database/TEST/flash_recovery_area/TEST/backupset/2011_11_10/o1_mf_nnndf_TAG20111110T104845_7cq7kbgs_.bkp RECID=34 STAMP=766838970
deleted backup piece
backup piece handle=/database/TEST/flash_recovery_area/TEST/backupset/2011_11_10/o1_mf_ncsnf_TAG20111110T104845_7cq7kgol_.bkp RECID=35 STAMP=766838974
Deleted 3 objects

or using the Tag value

RMAN> delete backupset Tag 'TAG20111110T104845';

remark: You can overwrite the tag like this

RMAN> backup as backupset database tag 'MY_FIRST_BACKUP';

and check the tag value

RMAN> list backupset summary;

List of Backups
===============
Key     TY LV S Device Type Completion Time #Pieces #Copies Compressed Tag
------- -- -- - ----------- --------------- ------- ------- ---------- ---
2833    B  F  A DISK        10-NOV-11       1       1       NO         MY_FIRST_BACKUP
2834    B  F  A DISK        10-NOV-11       1       1       NO         MY_FIRST_BACKUP
2835    B  F  A DISK        10-NOV-11       1       1       NO         MY_FIRST_BACKUP

remark: You can also backup all the archive log in the same time of the database:

RMAN> backup as backupset database plus archivelog;
...
RMAN> list backupset;


List of Backup Sets
===================


BS Key  Size       Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ ---------------
586     73.50K     DISK        00:00:00     10-NOV-11
        BP Key: 590   Status: AVAILABLE  Compressed: NO  Tag: TAG20111110T114303
        Piece Name: /database/TEST/flash_recovery_area/TEST/backupset/2011_11_10/o1_mf_annnn_TAG20111110T114303_7cqboq9j_.bkp

  List of Archived Logs in backup set 586
  Thrd Seq     Low SCN    Low Time  Next SCN   Next Time
  ---- ------- ---------- --------- ---------- ---------
  1    260     5144748    10-NOV-11 5145114    10-NOV-11
  1    261     5145114    10-NOV-11 5145118    10-NOV-11
  1    262     5145118    10-NOV-11 5145125    10-NOV-11
  1    263     5145125    10-NOV-11 5145128    10-NOV-11
  1    264     5145128    10-NOV-11 5145131    10-NOV-11
  1    265     5145131    10-NOV-11 5145199    10-NOV-11

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
587     Full    1.19G      DISK        00:00:45     10-NOV-11
        BP Key: 591   Status: AVAILABLE  Compressed: NO  Tag: TAG20111110T114304
        Piece Name: /database/TEST/flash_recovery_area/TEST/backupset/2011_11_10/o1_mf_nnndf_TAG20111110T114304_7cqborsw_.bkp
  List of Datafiles in backup set 587
  File LV Type Ckp SCN    Ckp Time  Name
  ---- -- ---- ---------- --------- ----
  1       Full 5145217    10-NOV-11 /database/TEST/system/system01.dbf
  2       Full 5145217    10-NOV-11 /database/TEST/system/sysaux01.dbf
  3       Full 5145217    10-NOV-11 /database/TEST/undo/undotbs01.dbf
  5       Full 5145217    10-NOV-11 /database/TEST/rcat/rcat01.dbf

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
588     Full    33.46M     DISK        00:00:02     10-NOV-11
        BP Key: 592   Status: AVAILABLE  Compressed: NO  Tag: TAG20111110T114304
        Piece Name: /database/TEST/flash_recovery_area/TEST/backupset/2011_11_10/o1_mf_nnndf_TAG20111110T114304_7cqbq5sc_.bkp
  List of Datafiles in backup set 588
  File LV Type Ckp SCN    Ckp Time  Name
  ---- -- ---- ---------- --------- ----
  4       Full 5145240    10-NOV-11 /database/TEST/users/users01.dbf

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
589     Full    9.67M      DISK        00:00:02     10-NOV-11
        BP Key: 593   Status: AVAILABLE  Compressed: NO  Tag: TAG20111110T114304
        Piece Name: /database/TEST/flash_recovery_area/TEST/backupset/2011_11_10/o1_mf_ncsnf_TAG20111110T114304_7cqbqb0p_.bkp
  SPFILE Included: Modification time: 10-NOV-11
  SPFILE db_unique_name: TEST
  Control File Included: Ckp SCN: 5145241      Ckp time: 10-NOV-11

BS Key  Size       Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ ---------------
613     7.00K      DISK        00:00:00     10-NOV-11
        BP Key: 616   Status: AVAILABLE  Compressed: NO  Tag: TAG20111110T114356
        Piece Name: /database/TEST/flash_recovery_area/TEST/backupset/2011_11_10/o1_mf_annnn_TAG20111110T114356_7cqbqdhm_.bkp

  List of Archived Logs in backup set 613
  Thrd Seq     Low SCN    Low Time  Next SCN   Next Time
  ---- ------- ---------- --------- ---------- ---------
  1    266     5145199    10-NOV-11 5145247    10-NOV-11

remark: You can also delete all archived log after they have been backuped:

RMAN> backup as bacupset database plus archivelog delete input;

How to backup the entire database as image copies

To backup the database as image copy use the following command:

RMAN> backup as copy database;
Starting backup at 10-NOV-11
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile copy
input datafile file number=00001 name=/database/TEST/system/system01.dbf
output file name=/database/TEST/flash_recovery_area/TEST/datafile/o1_mf_system_7cqcc9jo_.dbf tag=TAG20111110T115433 RECID=18 STAMP=766842890
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:25
channel ORA_DISK_1: starting datafile copy
input datafile file number=00002 name=/database/TEST/system/sysaux01.dbf
output file name=/database/TEST/flash_recovery_area/TEST/datafile/o1_mf_sysaux_7cqcd2th_.dbf tag=TAG20111110T115433 RECID=19 STAMP=766842916
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:25
channel ORA_DISK_1: starting datafile copy
input datafile file number=00003 name=/database/TEST/undo/undotbs01.dbf
output file name=/database/TEST/flash_recovery_area/TEST/datafile/o1_mf_undotbs1_7cqcdvz1_.dbf tag=TAG20111110T115433 RECID=20 STAMP=766842927
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:07
channel ORA_DISK_1: starting datafile copy
input datafile file number=00004 name=/database/TEST/users/users01.dbf
output file name=/database/TEST/flash_recovery_area/TEST/datafile/o1_mf_users_7cqcf30k_.dbf tag=TAG20111110T115433 RECID=21 STAMP=766842932
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:03
channel ORA_DISK_1: starting datafile copy
input datafile file number=00005 name=/database/TEST/rcat/rcat01.dbf
output file name=/database/TEST/flash_recovery_area/TEST/datafile/o1_mf_rcat_ts_7cqcf64t_.dbf tag=TAG20111110T115433 RECID=22 STAMP=766842934
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01
channel ORA_DISK_1: starting datafile copy
copying current control file
output file name=/database/TEST/flash_recovery_area/TEST/controlfile/o1_mf_TAG20111110T115433_7cqcf79c_.ctl tag=TAG20111110T115433 RECID=23 STAMP=766842935
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
including current SPFILE in backup set
channel ORA_DISK_1: starting piece 1 at 10-NOV-11
channel ORA_DISK_1: finished piece 1 at 10-NOV-11
piece handle=/database/TEST/flash_recovery_area/TEST/backupset/2011_11_10/o1_mf_nnsnf_TAG20111110T115433_7cqcf8gw_.bkp tag=TAG20111110T115433 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 10-NOV-11

You can check the image copy using:

RMAN> list copy;

specification does not match any archived log in the repository
List of Datafile Copies
=======================

Key     File S Completion Time Ckp SCN    Ckp Time
------- ---- - --------------- ---------- ---------------
671     1    A 10-NOV-11       5145845    10-NOV-11
        Name: /database/TEST/flash_recovery_area/TEST/datafile/o1_mf_system_7cqcc9jo_.dbf
        Tag: TAG20111110T115433

672     2    A 10-NOV-11       5145891    10-NOV-11
        Name: /database/TEST/flash_recovery_area/TEST/datafile/o1_mf_sysaux_7cqcd2th_.dbf
        Tag: TAG20111110T115433

673     3    A 10-NOV-11       5145901    10-NOV-11
        Name: /database/TEST/flash_recovery_area/TEST/datafile/o1_mf_undotbs1_7cqcdvz1_.dbf
        Tag: TAG20111110T115433

674     4    A 10-NOV-11       5145904    10-NOV-11
        Name: /database/TEST/flash_recovery_area/TEST/datafile/o1_mf_users_7cqcf30k_.dbf
        Tag: TAG20111110T115433

675     5    A 10-NOV-11       5145906    10-NOV-11
        Name: /database/TEST/flash_recovery_area/TEST/datafile/o1_mf_rcat_ts_7cqcf64t_.dbf
        Tag: TAG20111110T115433

List of Control File Copies
===========================

Key     S Completion Time Ckp SCN    Ckp Time
------- - --------------- ---------- ---------------
677     A 10-NOV-11       5145906    10-NOV-11
        Name: /database/TEST/flash_recovery_area/TEST/controlfile/o1_mf_TAG20111110T115433_7cqcf79c_.ctl
        Tag: TAG20111110T115433

remark: If you check for backupset, you will see that the SPFILE as been backuped as a backupset and not as an image copy.

RMAN> list backupset;

List of Backup Sets
===================

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
667     Full    80.00K     DISK        00:00:00     10-NOV-11
        BP Key: 668   Status: AVAILABLE  Compressed: NO  Tag: TAG20111110T115433
        Piece Name: /database/TEST/flash_recovery_area/TEST/backupset/2011_11_10/o1_mf_nnsnf_TAG20111110T115433_7cqcf8gw_.bkp
  SPFILE Included: Modification time: 10-NOV-11
  SPFILE db_unique_name: TEST

How to define a new location for backups

To define a specific location for backup destination, use the following syntax :

RMAN> BACKUP AS BACKUPSET FORMAT '/u01/oradata/backups/%F' database;

How to backup only a datafile

To backup only a datafile you can use the BACKUP DATAFILE syntax. First of all, you need to identify the datafile to backup either by its location in the file system or it identifier.
a) by the datafile id

RMAN> report schema;
Report of database schema for database with db_unique_name TEST

List of Permanent Datafiles
===========================
File Size(MB) Tablespace           RB segs Datafile Name
---- -------- -------------------- ------- ------------------------
1    760      SYSTEM               YES     /database/TEST/system/system01.dbf
2    730      SYSAUX               NO      /database/TEST/system/sysaux01.dbf
3    100      UNDOTBS1             YES     /database/TEST/undo/undotbs01.dbf
4    37       USERS                NO      /database/TEST/users/users01.dbf
5    15       RCAT_TS              NO      /database/TEST/rcat/rcat01.dbf

List of Temporary Files
=======================
File Size(MB) Tablespace           Maxsize(MB) Tempfile Name
---- -------- -------------------- ----------- --------------------
1    43       TEMP                 32767       /database/TEST/temp/temp01.dbf

RMAN> backup as backupset datafile 4
Starting backup at 10-NOV-11
using channel ORA_DISK_1
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00004 name=/database/TEST/users/users01.dbf
channel ORA_DISK_1: starting piece 1 at 10-NOV-11
channel ORA_DISK_1: finished piece 1 at 10-NOV-11
piece handle=/database/TEST/flash_recovery_area/TEST/backupset/2011_11_10/o1_mf_nnndf_TAG20111110T143410_7cqnpl6d_.bkp tag=TAG20111110T143410 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 10-NOV-11
;

or using a file name

RMAN> backup as backupset datafile '/database/TEST/users/users01.dbf';
Starting backup at 10-NOV-11
using channel ORA_DISK_1
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00004 name=/database/TEST/users/users01.dbf
channel ORA_DISK_1: starting piece 1 at 10-NOV-11
channel ORA_DISK_1: finished piece 1 at 10-NOV-11
piece handle=/database/TEST/flash_recovery_area/TEST/backupset/2011_11_10/o1_mf_nnndf_TAG20111110T143720_7cqnwk2r_.bkp tag=TAG20111110T143720 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 10-NOV-11

How to backup a tablespace

To backup only a tablespace you can use the BACKUP TABLESPACE syntax.

RMAN> BACKUP AS BACKUPSET TABLESPACE USERS;
Starting backup at 10-NOV-11
using channel ORA_DISK_1
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00004 name=/database/TEST/users/users01.dbf
channel ORA_DISK_1: starting piece 1 at 10-NOV-11
channel ORA_DISK_1: finished piece 1 at 10-NOV-11
piece handle=/database/TEST/flash_recovery_area/TEST/backupset/2011_11_10/o1_mf_nnndf_TAG20111110T144000_7cqo1jtn_.bkp tag=TAG20111110T144000 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 10-NOV-11

How to backup archivelog

RMAN> backup as backupset archivelog all;
Starting backup at 10-NOV-11
current log archived
using channel ORA_DISK_1
channel ORA_DISK_1: starting archived log backup set
channel ORA_DISK_1: specifying archived log(s) in backup set
input archived log thread=1 sequence=267 RECID=89 STAMP=766852984
channel ORA_DISK_1: starting piece 1 at 10-NOV-11
channel ORA_DISK_1: finished piece 1 at 10-NOV-11
piece handle=/database/TEST/flash_recovery_area/TEST/backupset/2011_11_10/o1_mf_annnn_TAG20111110T144305_7cqo79b4_.bkp tag=TAG20111110T144305 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 10-NOV-11

or

RMAN> backup as copy archivelog all;
Starting backup at 10-NOV-11
current log archived
using channel ORA_DISK_1
channel ORA_DISK_1: starting archived log copy
input archived log thread=1 sequence=267 RECID=89 STAMP=766852984
output file name=/database/TEST/flash_recovery_area/TEST/archivelog/2011_11_10/o1_mf_1_267_7cqocxbx_.arc RECID=91 STAMP=766853133
channel ORA_DISK_1: archived log copy complete, elapsed time: 00:00:01
channel ORA_DISK_1: starting archived log copy
input archived log thread=1 sequence=268 RECID=90 STAMP=766853132
output file name=/database/TEST/flash_recovery_area/TEST/archivelog/2011_11_10/o1_mf_1_268_7cqocyh3_.arc RECID=92 STAMP=766853134
channel ORA_DISK_1: archived log copy complete, elapsed time: 00:00:01
Finished backup at 10-NOV-11

If you need to backup a specific archive log, you can write :

RAMN> backup as copy archivelog like '/database/TEST/flash_recovery_area/TEST/archivelog/2011_11_10/o1_mf_1_269_7cqok20g_.arc';
Starting backup at 10-NOV-11
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=145 device type=DISK
channel ORA_DISK_1: starting archived log copy
input archived log thread=1 sequence=269 RECID=93 STAMP=766853298
output file name=/database/TEST/flash_recovery_area/TEST/archivelog/2011_11_10/o1_mf_1_269_7cqolrj4_.arc RECID=94 STAMP=766853352
channel ORA_DISK_1: archived log copy complete, elapsed time: 00:00:01
Finished backup at 10-NOV-11

How to perform incremental backups

The start point of an incremental backup must be a level 0 inrecemtal batabase backup. See here under to perform an incremental level 0 backup:

RMAN> BACKUP INCREMENTAL LEVEL 0 DATABASE:

Once an level 0 backup is perform, it is then possible to perform incremental backups. There are 2 types of incremental backups :
    a) differential incremental backup

RMAN> backup incremental level 1 DATABASE;

A differential incremental backup contains blocks that have change since the last incremental backup level 1 or 0, if it is the list incremental backup.
    b) cummulative incremental backup

RMAN> backup incremental level 1 CUMULATIVE DATABASE;

A cumulative backup contains blocks that have changed since the level 0 backup;

remark: to speed up the incremental backups, it is possible to activate a block change tracking feature that will trace into a file all blocks that have changed since the last backup. To do this, you can use the following syntax :

SQL> ALTER DATABASE {ENABLE|DISABLE} BLOCK CHANGE TRACKING [USING FILE '...'] [REUSE];

How to perform backups with multisections

To optimize backup for big files, it will be possible to backup file using the multi section feature. In this way, we can cut up the big files in severals smallest ones that could be backed up in parallel through multiple channels allocations or parallelism.

RMAN> CONFIGURE DEVICE TYPE DISK PARALLELISM 4;
RMAN> BACKUP as backupset INCREMENTAL LEVEL 0 DATABASE SECTION SIZE=25M;

or

RMAN> RUN {
ALLOCATE CHANNEL disk1 device type disk;
ALLOCATE CHANNEL disk2 device type disk;
ALLOCATE CHANNEL disk3 device type disk;
ALLOCATE CHANNEL disk4 device type disk;
BACKUP as backupset DATABASE SECTION SIZE=25M;
}

Oracle11g : RMAN persistant parameters

Persistant parameters for RMAN are defined through the CONFIGURE command.

To display all default parameters you can issue the following command:

$>rman target /
RMAN> SHOW ALL;
RMAN configuration parameters for database with db_unique_name TEST are:
CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 1 DAYS;
CONFIGURE BACKUP OPTIMIZATION OFF; # default
CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default
CONFIGURE CONTROLFILE AUTOBACKUP OFF; # default
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F'; # default
CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO BACKUPSET; # default
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE MAXSETSIZE TO UNLIMITED; # default
CONFIGURE ENCRYPTION FOR DATABASE OFF; # default
CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default
CONFIGURE COMPRESSION ALGORITHM 'BASIC' AS OF RELEASE 'DEFAULT' OPTIMIZE FOR LOAD TRUE ; # default
CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default
CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/opt/oracle/11.2.0/dbs/snapcf_TEST.f'; # default

or you can connect to the database catalog instance as SYSDBA and take a look at the V$RMAN_CONFIGURATION.

$> export ORACLE_SID=RCAT_DB
$> sqlplus / as sydba
SQL> select * from V$RMAN_CONFIGURATION;

     CONF# NAME                 VALUE
---------- -------------------- ----------------------------------------------------------------------------------------------------
         1 RETENTION POLICY     TO RECOVERY WINDOW OF 1 DAYS

or you can connect to the database catalog instance as catalog owner and take a look at the RC_RMAN_CONFIGURATION.

$> export ORACLE_SID=RCAT_DB
$> sqlplus rcatowner/rcatowner
SQL> select * from RC_RMAN_CONFIGURATION;

     CONF# NAME                 VALUE
---------- -------------------- ----------------------------------------------------------------------------------------------------
         1 RETENTION POLICY     TO RECOVERY WINDOW OF 1 DAYS

remark:SHOW ALL is only available when you are connected to the target instance through RMAN. It does not work if you are connected only to the catalog (rman catalog <user>/<pwd>).

How to use the CONFIGURE command
With the CONFIGURE command, it is possible to :

1. Configure the automatic bakup of the control file:
   

   RMAN> CONFIGURE CONTROLFILE AUTOBACKUP ON;

   RMAN> CONFIGURE CONTROLFILE AUTOBACKUP OFF;

   The controlfile autobackup is performed each time :
       - a successful backup is done
       - a script has been executed in RMAN
       - RMAN detects a database structure modification
   
   
2. Alter the location and the file name format used by RMAN for the controlfile backup file:
   

   RMAN> CONFIGURE CONTROLFILE AUTOBACKUP FORMAT
   FOR DEVICE TYPE DISK TO '/u01/oradata/cf_ORCL_auto_%F';

3. Configure an access to a device type in parallele:
   

   RMAN> CONFIGURE DEVICE TYPE sbt PARALLELISM 3;

4. Configure the default backup destination:
   

   RMAN> CONFIGURE DEFAULT DEVICE TYPE TO DISK;

   RMAN> CONFIGURE DEFAULT DEVICE TYPE TO TAPE;

5. Configure a channel used by a backup operation:
   

   RMAN> CONFIGURE CHANNEL DEVICE TYPE sbt...;

6. Create severals copies of a same backup.
   In the following example, the datafile is backuped to a tape with 2 copies.
   

   RMAN> CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE sbt TO 2;

   Same for archive logs
   

   RMAN> CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE sbt TO 2;

   remark :This option is only available for backupset but not for image copies.
   
   
7. Activate the RMAN optimization feature.
   This option allow RMAN to do not backup a file, if an identical file already exists in a previous backup.
   

   RMAN> CONFIGURE BACKUP OPTIMIZATION ON;

   This option is set to OFF by default.
   
   
8. Compress blocks using a compression algorithm (HIGH | MEDIUM | LOW | BASIC)
   

   RMAN> CONFIGURE COMPRESSION ALGORITHM 'LOW';

   or
   

   RMAN> RUN {
     SET COMPRESSION ALGORITHM 'LOW';
   }

   The section here under describes the compression algorithm used by each level :
       LOW       ==> LZO
       MEDIUM  ==> ZLIB
       HIGH      ==> GZIP
       BASIC     ==> BZIP2
   

note:Inside a RUN{} prefers the use of SET instead of CONFIGURE

RMAN> RUN {
  SET CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE disk TO ‘/opt/oracle/diag/rdbms/backup/rman/V1120/%F’;
}

How to show a parameter value
To display the default value, you can use the SHOW command.
For example :

RMAN> SHOW DEFAULT DEVICE TYPE;
RMAN configuration parameters for database with db_unique_name TEST are:
CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default

How to reset a parameter to its default value
To reset a persistant parameter you can use the keyword CLEAR in association to the CONFIGURE command.
For example to reset the default channel:

RMAN> CONFIGURE DEFAULT DEVICE TYPE CLEAR;

Oracle11g: Creating Virtual Private Catalog with RMAN

In this section, i will describe how to create virtual private catalog in order to allow several rman users to manage distinct database catalog in a private way.

I assume here that the database instance that contains the catalog is created (rcat_db) with the main catalog owner (rcatowner).

1) Create the catalog
Here we connect to the catalog with rcatowner and create a new catalog.

RMAN> connect catalog rcatowner/rcatowner
RMAN> CREATE CATALOG;

2) create a virtual catalog owner
Now we connect to the database instance that manages catalogs in order to create a new virtual catalog owner and give it the RECOVERY_CATALOG_OWNER role.

$> export ORACLE_SID=rcat_db
$>sqlplus / as sysdba
SQL> CREATE USER vpcuser1 IDENTIFIED BY vpcuser1
TEMPORARY TABLESPACE TEMP
DEFAULT TABLESPACE rcat_ts
QUOTA UNLIMITED ON rcat_ts;
SQL> GRANT RECOVERY_CATALOG_OWNER TO vpcuser1;

3) Give the user the right to register a new database in its virtual private catalog
May be we can give the virtual user the right to register a database itself in its catalog or the possibility to reference a database that has been previously registered by the catalog owner.

    a) How to give the REGISTER right to a private catalog user

$> rman catalog rcatowner/rcatowner
RMAN> GRANT REGISTER DATABASE to vpcuser1;

    b) How to allow a virtual user to reference a database previously registered by the catalog owner

$> rman catalog rcatowner/rcatowner
RMAN> GRANT CATALOG FOR DATABASE <db_name> TO vpcuser1;

4) Let's the virtual private catalog owner creating it's private catalog

RMAN> CONNECT CATALOG vpcuser1/vpcuser1
RMAN> CREATE VIRTUAL CATALOG;

5) Now the user can register its database into its private catalog

$gt: export ORACLE_SID=MY_DB
$> rman target / CATALOG pvcuser1/vpcuser1@rcat_db
RMAN> REGISTER DATABASE;
RMAN> list incarnation;

List of Database Incarnations
DB Key  Inc Key DB Name  DB ID            STATUS  Reset SCN  Reset Time
------- ------- -------- ---------------- --- ---------- ----------
726     727     MY_DB    179332583        CURRENT 1          07-MAR-11

note: If we connect with the rcatowner user, we can see that this user can see all the database (it own registrered database and database registered for user vpcuser1).

$> rman catalog rcatowner/rcatowner@rcat_db
RMAN> list incarnation;

List of Database Incarnations
DB Key  Inc Key DB Name  DB ID            STATUS  Reset SCN  Reset Time
------- ------- -------- ---------------- --- ---------- ----------
2       20      TEST     2058702964       PARENT  1          13-AUG-09
2       4       TEST     2058702964       CURRENT 754488     30-AUG-11
726     727     MY_DB    179332583        CURRENT 1          07-MAR-11

Oracle11g: how to know which database is managed by RMAN

Some times it will be interesting to know which database is managed by the RMAN catalog.
There is three ways for archiving this :

  a) By connecting to the rman catalog only

$> rman catalog rcatowner/rcatowner
Recovery Manager: Release 11.2.0.1.0 - Production on Tue Nov 8 17:35:43 2011

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

connected to target database: TEST (DBID=2058702964)
connected to recovery catalog database

RMAN> list incarnation;

List of Database Incarnations
DB Key  Inc Key DB Name  DB ID            STATUS  Reset SCN  Reset Time
------- ------- -------- ---------------- --- ---------- ----------
2       20      TEST     2058702964       PARENT  1          13-AUG-09
2       4       TEST     2058702964       CURRENT 754488     30-AUG-11

  b) By connecting to the rman instance without the catalog

$> rman target /

Recovery Manager: Release 11.2.0.1.0 - Production on Tue Nov 8 17:47:09 2011

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

connected to target database: TEST (DBID=2058702964)

RMAN> list incarnation;

using target database control file instead of recovery catalog

List of Database Incarnations
DB Key  Inc Key DB Name  DB ID            STATUS  Reset SCN  Reset Time
------- ------- -------- ---------------- --- ---------- ----------
1       1       TEST     2058702964       PARENT  1          13-AUG-09
2       2       TEST     2058702964       CURRENT 754488     30-AUG-11

  b) By connecting directly to the database instance that owns the catalog schema

$> sqlplus rcatowner/rcatowner
SQL*Plus: Release 11.2.0.1.0 Production on Tue Nov 8 17:35:31 2011

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

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning option

SQL> select * from dbinc;

 DBINC_KEY     DB_KEY DB_NAME   RESET_SCN RESET_TIM PARENT_DBINC_KEY DBINC_ST
---------- ---------- -------- ---------- --------- ---------------- --------
         4          2 TEST         754488 30-AUG-11               20 CURRENT
        20          2 TEST              1 13-AUG-09                  PARENT

note: Each time a database is open using resetlogs, a new database incarnation is created and registered in the catalog database.

Oracle 11g: How to define the fast recovery area

The initialization parameters used to define the Fast Recovery Area are:
-DB_RECOVERY_FILE_DEST : Location of the FRA
-DB_RECOVERY_FILE_DEST_SIZE : Size limit of the FRA

The size of the FRA is mandatory when the DB_RECOVERY_FILE_DEST parameter is defined.

remark: An alert is issued in the alert_log file when 85% of the space is filled. A critical alter is issued when 97% of the space is filled. These thresholds could not be configured.

It is possible to display these alerts using the dba_outstanding_alerts.

SQL> SELECT object_type, message_type, message_level, reason, suggested_action
FROM dba_outstanding_alerts;

Oracle11g: How to create a Recovery Catalog for RMAN

Prerequisites

1) Database must be in archivelog
To perform backup through rman, the database must be in archivelog mode:

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> startup mount;
ORACLE instance started.

Total System Global Area 1238732800 bytes
Fixed Size                  1336204 bytes
Variable Size             788532340 bytes
Database Buffers          436207616 bytes
Redo Buffers               12656640 bytes
Database mounted.

SQL> alter database archivelog;

Database altered.
SQL> alter database open;

Database altered.
SQL> archive log list;
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     193
Next log sequence to archive   195
Current log sequence           195

As we can see, the archive destination references the USE_DB_RECOVERY_FILE_DEST value. To identify the real location where the archivelog will be written we can display the value of the DB_RECOVERY_FILE_DEST init parameter:

SQL> show parameter DB_RECOVERY_FILE_DEST;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest                string      /database/TEST/flash_recovery_
                                                 area
db_recovery_file_dest_size           big integer 3852M

2) Configure severals destinations of archive log files

It is recommanded to configure more than one archive log file destination because if only one is configured, the database may frozen if the unique destination is full.

Local and remote destination are configured through the LOG_ARCHIVE_DEST_n paraemeters where n is in range [1..31].
For each LOG_ARCHIVE_DEST_n parameter there is a LOG_ARCHIVE_DEST_STATE_n parameter which indicates if the destination is enabled or not. By default the destination is enabled.

The LOG_ARCHIVE_DEST_n parameter may contain :
a) a local destination : LOG_ARCHIVE_DEST_n='LOCATION=/disk1/arch'
b) a remote destination through a net service name : LOG_ARCHIVE_DEST_n='SERVICE=remote_service'
The service name must be defined in the tnsnames.ora configuration file.

A destination may be declared MANDATORY or OPTIONAL. If a destination marked as MANDATORY fails, then the archiving process fails and the database frozen whatever the value defined in the LOG_ARCHIVE_MIN_SUCCEED_DEST. We can conclude that a the LOG_ARCHIVE_MIN_SUCCEED_DEST is ignored when a MANDATORY destination fails.

remark: Under the Oracle Database Standard Edition, only two archive log destinations are available : LOG_ARCHIVE_DEST & LOG_ARCHIVE_DUPLEX_DEST. Under Enterprise Edition, Oracle recommands LOG_ARCHIVE_DEST_n.

remark: The view V$ARCHIVE_DEST contains all information about archivelog destinations.

3) Ensuring success for archiving log files

When having severals archivelog destinations, it is usefull to define the minimum number of destination that must succeed in order to consider that the archiving process is successful. This is done through the LOG_ARCHIVE_MIN_SUCCEED_DEST parameter.
By default this parameter is setted to 1.

4) Defining a conservation strategies for backups

It is necessary to define a period of time during which it is possible to repair the database from backups. This strategy could be based on
a) a recovery window of n days

RMAN> CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF <days> DAYS;

b) or a number of backups to keep available.

RMAN> CONFIGURE RETENTION POLICY TO REDUNDANCY <copies>;

remark: If you don't use a rman catalog, then the retention policy (recovery window or redundancy) must be less than the value of the CONTROL_FILE_RECORD_KEEP_TIME. The default value for CONTROL_FILE_RECORD_KEEP_TIME is 7 days.

remark: If you use a rman catalog, then ensure that the value of the CONTROL_FILE_RECORD_KEEP_TIME parameter is more then the time between 2 catalog resynchronizations. Resynchronizations are performed when you create a new backup or when you use the RESYNC CATALOG command.

remark: If you desactivate the retention policy then backups are keeped by rman as long as defined by the CONTROL_FILE_RECORD_KEEP_TIME parameter. To desactivate the retention policy :

RMAN> CONFIGURE RETENTION POLICY TO NONE;

5) Configure deletion policy for archivelog files

It is possible to configure a deletion policy for archivelog file as well as backupset or image copies.

RMAN> CONFIGURE ARCHIVELOG DELETION POLICY TO {CLEAR | TO {APPLIED ON [ALL] STANDBY |
  BACKED UP integer TIMES TO DEVICE TYPE deviceSpecifier |
  NONE | SHIPPED TO [ALL] STANDBY}
  [ {APPLIED ON [ALL] STANDBY | BACKED UP integer TIMES TO DEVICE TYPE deviceSpecifier |
  NONE | SHIPPED TO [ALL] STANDBY}]...}

The extended syntax allows for configurations where logs are eligible for deletion only after being applied to, or transferred to, one or more standby database destinations.

Creating a recovery catalog for RMAN

The RMAN data are always stored in the control file but they can be duplicated to a distinct database called Recovery Catalog in order to enhance historization of backups (remember that the control file record backups for a fixed period defined through CONTROL_FILE_RECORD_KEEP_TIME parameter).

1) Create a database using dbca (for example)

2) Create a tablespace used by the catalog owner
    a) Create the directory on filesystem where will be located the tablespace (Not necessary if the database is manged through OMF)

$> mkdir /database/TEST/rcat

    b) Create the tablespace used by the catalog

SQL> CREATE TABLESPACE rcat_ts datafile '/database/TEST/rcat/rcat01.dbf' size 15M;
Tablespace created.

Each database registered inside an RMAN catalog require generally 15MB of free space.

3) Create the database owner of the RMAN catalog

SQL> CREATE USER rcatowner IDENTIFIED BY rcatowner
    TEMPORARY TABLESPACE TEMP
    DEFAULT TABLESPACE rcat_ts
    QUOTA UNLIMITED ON rcat_ts;

User created.

Grant the RECOVERY_CATALOG_OWNER role to the rcatowner user;

SQL> GRANT RECOVERY_CATALOG_OWNER to rcatowner;

Grant succeeded.

The RECOVERY_CALATOG_OWNER grants the following system priviledges:

SQL> select * from dba_sys_privs where grantee='RECOVERY_CATALOG_OWNER';

GRANTEE                        PRIVILEGE                                ADM
------------------------------ ---------------------------------------- ---
RECOVERY_CATALOG_OWNER         CREATE SYNONYM                           NO
RECOVERY_CATALOG_OWNER         CREATE CLUSTER                           NO
RECOVERY_CATALOG_OWNER         ALTER SESSION                            NO
RECOVERY_CATALOG_OWNER         CREATE DATABASE LINK                     NO
RECOVERY_CATALOG_OWNER         CREATE PROCEDURE                         NO
RECOVERY_CATALOG_OWNER         CREATE SEQUENCE                          NO
RECOVERY_CATALOG_OWNER         CREATE TABLE                             NO
RECOVERY_CATALOG_OWNER         CREATE SESSION                           NO
RECOVERY_CATALOG_OWNER         CREATE TYPE                              NO
RECOVERY_CATALOG_OWNER         CREATE VIEW                              NO
RECOVERY_CATALOG_OWNER         CREATE TRIGGER                           NO

11 rows selected.

4) Connect to the instance and create the RMAN catalog

$> rman
Recovery Manager: Release 11.2.0.1.0 - Production on Tue Nov 8 11:14:57 2011

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

RMAN> connect CATALOG rcatowner/rcatowner

connected to recovery catalog database

RMAN> CREATE CATALOG;

recovery catalog created

5) Register a database inside the RMAN catalog
Connect both to the database instance and the rman catalog.

$> export ORACLE_SID=TEST
$> rman TARGET / CATALOG rcatowner/rcatowner
Recovery Manager: Release 11.2.0.1.0 - Production on Tue Nov 8 11:31:42 2011

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

connected to target database: TEST (DBID=2058702964)
connected to recovery catalog database

Register all information in relation to the targetted database inside the rman catalog

RMAN> REGISTER DATABASE;
database registered in recovery catalog
starting full resync of recovery catalog
full resync complete

remark: To unregister a database from the recovery catalog, use UNREGISTER DATABASE;

6) Configure the retention policy

RMAN> CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 7 DAYS;
new RMAN configuration parameters:
CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 7 DAYS;
new RMAN configuration parameters are successfully stored
starting full resync of recovery catalog
full resync complete

Oracle 11g: what is the oracle version and patches applied

To know what is the oracle version regardless the patch installed, you can use the following request under sqlplus :

sql> select * from v$version

If you need to know which patch has been installed on your database, it will be recommended to use the opatch utility located under the $ORACLE_BASE/11.2.0/OPatch directory :

$>./optach lsinventory
Invoking OPatch 11.1.0.6.6

Oracle Interim Patch Installer version 11.1.0.6.6
Copyright (c) 2009, Oracle Corporation.  All rights reserved.


Oracle Home       : /opt/oracle/11.2.0
Central Inventory : /opt/oracle/oraInventory
   from           : /etc/oraInst.loc
OPatch version    : 11.1.0.6.6
OUI version       : 11.2.0.1.0
OUI location      : /opt/oracle/11.2.0/oui
Log file location : /opt/oracle/11.2.0/cfgtoollogs/opatch/opatch2011-11-02_16-20-24PM.log

Patch history file: /opt/oracle/11.2.0/cfgtoollogs/opatch/opatch_history.txt

Lsinventory Output file location : /opt/oracle/11.2.0/cfgtoollogs/opatch/lsinv/lsinventory2011-11-02_16-20-24PM.txt

--------------------------------------------------------------------------------
Installed Top-level Products (1):

Oracle Database 11g                                                  11.2.0.1.0
There are 1 products installed in this Oracle Home.


There are no Interim patches installed in this Oracle Home.


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

OPatch succeeded.