jeudi 10 novembre 2011

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;
}

mercredi 9 novembre 2011

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;

mardi 8 novembre 2011

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.

lundi 7 novembre 2011

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;

jeudi 3 novembre 2011

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

mercredi 2 novembre 2011

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.

Oracle 10g : How to remove XPT services

To remove XPT services from Oracle 10g then add the following parameter in the intialization parameter file :
__dg_broker_service_names=''

jeudi 8 septembre 2011

Oracle 11g: What is the id of the current session

To know the id of the current session :
SQL>select SYS_CONTEXT('USERENV', 'SESSIONID') from dual;
SYS_CONTEXT('USERENV','SESSIONID')
----------------------------------
4294967295

See this page to retrieve all parameters defined at the user session level :
http://download.oracle.com/docs/cd/B28359_01/server.111/b28286/functions172.htm

Oracle 11g : SQLLoader

See the following reference for more information :

Oracle 11g : How to retrieve the source code of a procedure

Look the content of the user_source table:
desc user_source;
NAME   VARCHAR2(30)
TYPE   VARCHAR2(12)
LINE   NUMBER
TEXT   VARCHAR2(4000)

You can issue the following SQL request to find the source code of a specific procedure :
SELECT text
FROM   user_source
WHERE  name = 
AND    type = 'PROCEDURE'
ORDER BY line;

jeudi 1 septembre 2011

Oracle 11g: How to display explain plan

Intallation
To retrieve the execution plan for a specific sql request or session, you need to install the PLAN_TABLE table in your database.
Oracle provide the utlxplan.sql script located under ${ORACLE_HOME}/rdbms/admin/ to perform its installation.
SQL>@?/rdbms/admin/utlxplan;
Calculate plan for a specific request
To populate the PLAN_TABLE table for a specific request :
SQL>truncate table PLAN_TABLE;
SQL>explain plan for select * from user_tables;
Display plan for the previous request
SQL>set linesize 400
SQL>@?/rdbms/admin/utlxpls

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
Plan hash value: 4164660786

---------------------------------------------------------------------------------------------
| Id  | Operation                   | Name          | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |               |  1831 |  4173K|   515   (1)| 00:00:07 |
|*  1 |  HASH JOIN RIGHT OUTER      |               |  1831 |  4173K|   515   (1)| 00:00:07 |
|   2 |   INDEX FAST FULL SCAN      | I_OBJ2        | 12815 |   300K|    33   (0)| 00:00:01 |
|*  3 |   HASH JOIN RIGHT OUTER     |               |  1831 |  4130K|   482   (2)| 00:00:06 |
|   4 |    TABLE ACCESS FULL        | USER$         |    93 |  1581 |     3   (0)| 00:00:01 |
|*  5 |    HASH JOIN RIGHT OUTER    |               |  1831 |  4100K|   478   (1)| 00:00:06 |

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
|   6 |     INDEX FAST FULL SCAN    | I_OBJ1        | 12815 |   100K|    11   (0)| 00:00:01 |
|*  7 |     HASH JOIN               |               |  1831 |  4085K|   466   (1)| 00:00:06 |
|   8 |      TABLE ACCESS FULL      | TS$           |     5 |    95 |     3   (0)| 00:00:01 |
|*  9 |      HASH JOIN RIGHT OUTER  |               |  1831 |  4051K|   463   (1)| 00:00:06 |
|  10 |       TABLE ACCESS FULL     | SEG$          |  5235 |   230K|    43   (0)| 00:00:01 |
|* 11 |       HASH JOIN RIGHT OUTER |               |  1831 |  3971K|   419   (1)| 00:00:06 |
|  12 |        TABLE ACCESS FULL    | DEFERRED_STG$ |  2783 | 64009 |     7   (0)| 00:00:01 |
|* 13 |        HASH JOIN            |               |  1831 |  3930K|   412   (1)| 00:00:05 |
|* 14 |         TABLE ACCESS FULL   | TAB$          |  2729 |   261K|   367   (0)| 00:00:05 |
|  15 |         MERGE JOIN CARTESIAN|               |  1831 |  3754K|    44   (3)| 00:00:01 |
|* 16 |          HASH JOIN          |               |     1 |  2070 |     1 (100)| 00:00:01 |

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
|* 17 |           FIXED TABLE FULL  | X$KSPPI       |     1 |    55 |     0   (0)| 00:00:01 |
|  18 |           FIXED TABLE FULL  | X$KSPPCV      |   100 |   196K|     0   (0)| 00:00:01 |
|  19 |          BUFFER SORT        |               |  1831 | 54930 |    44   (3)| 00:00:01 |
|* 20 |           TABLE ACCESS FULL | OBJ$          |  1831 | 54930 |    43   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("T"."BOBJ#"="CO"."OBJ#"(+))
   3 - access("CX"."OWNER#"="CU"."USER#"(+))

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
   5 - access("T"."DATAOBJ#"="CX"."OBJ#"(+))
   7 - access("T"."TS#"="TS"."TS#")
   9 - access("T"."FILE#"="S"."FILE#"(+) AND "T"."BLOCK#"="S"."BLOCK#"(+) AND
              "T"."TS#"="S"."TS#"(+))
  11 - access("T"."OBJ#"="DS"."OBJ#"(+))
  13 - access("O"."OBJ#"="T"."OBJ#")
  14 - filter(BITAND("T"."PROPERTY",1)=0)
  16 - access("KSPPI"."INDX"="KSPPCV"."INDX")
  17 - filter("KSPPI"."KSPPINM"='_dml_monitoring_enabled')
  20 - filter("O"."OWNER#"=USERENV('SCHEMAID') AND BITAND("O"."FLAGS",128)=0)

43 rows selected.

Remark : An other way to display a plan under sqlplus is :
SQL> set autotrace on;
To trace only the plan and associated statistics:
SQL> set autotrace traceonly explain statistics;
Note that you may need to enable the PLUSTRACE role for the user that activates the autotrace feature. ($ORACLE_HOME/sqlplus/admin/plustrce.sql)
If you have an error then give the 'select any dictionary' role to the user.

Remark : Using the dbms_xplan package:
  •  To display the plan from the plan table
    SQL> select * from table( dbms_xplan.display);
  •  To display the plan from the shared pool
    SQL> select * from table( dbms_xplan.display_cursor);
 

Displaying current plan with dbms_xplan
  • Retrieve the sql_id associated to the current request in the v$sql view
  • Display the plan like this:
    SQL> select * from table( dbms_xplan.display_cursor('<sql_id>', NULL));
Note that under 11g, the adaptive cursor sharing can generate several suboptimal plan for a sql request depending on statistics and histograms. The second parameter of the display_cursor procedure correspond to the child number so a specific plan in relation to the selectivity of parameters used in the request.
To known if a sql request is sensitive to bind values and adaptive cursor sharing:
SELECT sql_id, child_number, is_bind_sensitive, is_bind_aware
FROM   v$sql WHERE  sql_text = '...';

SQL_ID        CHILD_NUMBER I I
------------- ------------ - -
9bmm6cmwa8saf            0 Y N
9bmm6cmwa8saf            1 Y Y

2 rows selected.
 

Oracle 11g: Export import DATA PUMP

Data pump export/import is a utility for unloading and loading data and metadata from a database into a set of system files. Because the dump files are written by the server, rather than by the client, the database administrator must create directory objects where the utility can managed the files set.
SQL>create or replace directory export_dir as '/opt/oracle/admin/export';
SQL>grant read, write on directory export_dir to fmosse;
DATA_PUMP_DIR directory
By default Oracle create the DATA_PUMP_DIR that references the file system directory '/opt/oracle/admin/<sid>/dpdump/'. User must have the EXP_FULL_DATABASE or IMP_FULL_DATABASE privileges to use the DATA_PUMP_DIR database directory object.
If for some reason the system directory defined in DATA_PUMP_DIr is not found then Oracle use the $ORACLE_HOME/rdbms/log directory instead.

Data Pump Export Modes

Export provides different modes for unloading different portions of the database. The mode is specified on the command line, using the appropriate parameter. The available modes are as follows:
  • Full Export Mode
  • Schema Mode
  • Table Mode
  • Tablespace Mode
  • Transportable Tablespace Mode
Remark : You need the dba privilege to export or import the entire database or some tablespaces.

Exporting the full database
expdb system content=ALL full=y directory=DATA_PUMP_DIR dumpfile=export_full.dmp logfile=export_full.log
The 'content' parameter specifies which data to export. this parameter can have the following values :
  • ALL : To export all data and metadata
  • DATA_ONLY : Only data will be exported
  • METADATA_ONLY: only the structure of the database will be exported
The 'full' parameter specifies that we will export the entire database.

Exporting some schemas
expdb system content=ALL schemas=fmosse directory=DATA_PUMP_DIR dumpfile=export_full.dmp logfile=export_full.log
/br>Exporting some tables of a specific schema
expdb system content=ALL schemas=fmosse tables="TABLE1,TABLE2,TABLE3" directory=DATA_PUMP_DIR dumpfile=export_full.dmp logfile=export_full.log

Exporting a database with a set of dumpfile that does not exceed a specific size
expdp system content=all full=y directory=DATA_PUMP_DIR dumpfile=export_full_%u.dmp logfile=export_full.log filesize=250000
If the 'parallel' parameter is defined with the %U, then oracle create initially a set of dump files defined using the value of the 'parallel' attribute.Oracle can create more files than defined in the parallel attribute if the filesize is specified with a value that requires more files than defined in the parallel attribute.

Exporting a database using several processes in parallel
expdp system content=all full=y directory=DATA_PUMP_DIR dumpfile=export_full.dmp logfile=export_full.log parallel=4

Overwrite export files if already exists
expdp system content=all full=y directory=DATA_PUMP_DIR dumpfile=export_full_%u.dmp  logfile=export_full.log parallel=4 filesize=250000 reuse_dumpfiles=Y

Estimating the size of the export
expdp system content=all full=y directory=DATA_PUMP_DIR estimate_only=y estimate=statistics
If estimate_only=y is specified then the dumpfile parameter must not be defined.
The 'estimate' parameter defines the algorithm to use for estimation. The available values are:
  • BLOCKS : The default value
  • STATISTICS


Importing data
impdp hr directory=DATA_PUMP_DIR dumpfile=hr_export.dmp content=all tables="EMPLOYEES" parallel=1

DataPump import transformation
It is possible with the impdp tool to remap parts of the object when importing a dump file:
  • REMAP_DATAFILE : to remap datafiles
  • REMAP_TABLESPACES : to remap tablespaces
  • REMAP_SCHEMA : to remap a schema to an other one
  • REMAP_TABLE : to remap the name of a table
  • REMAP_DATA : to alter data before the import
To change the name of a table yuo can write REMAP_TABLE="EMPOYEE:EMP".

Consistent backup
Use the parameter flashback_time=to_timestamp( '24/10/2011 18:19:00', 'DD/MM/YYYY HH24:MI:SS')

mercredi 31 août 2011

Oracle 11g : Backup and recovery

Some useful information @ http://wap.orafaq.com/wiki/Oracle_database_Backup_and_Recovery_FAQ

Oracle 11g : Using Flashback table and Managing the recycle bin

what is the recycle bin
Oracle says : "The recycle bin is actually a data dictionary table containing information about dropped objects.Dropped tables and any associated objects such as indexes, constraints, nested tables, and the likes are not removed and still occupy space. They continue to count against user space quotas, until specifically purged from the recycle bin or the unlikely situation where they must be purged by the database because of tablespace space constraints."

Viewing the content of the recycle bin
SQL>select * from recyclebin;

SQL>desc RECYCLEBIN;
OBJECT_NAME      NOT NULL VARCHAR2(30)
ORIGINAL_NAME    VARCHAR2(32)
OPERATION        VARCHAR2(9)
TYPE             VARCHAR2(25)
TS_NAME          VARCHAR2(30)
CREATETIME       VARCHAR2(19)
DROPTIME         VARCHAR2(19)
DROPSCN          NUMBER
PARTITION_NAME   VARCHAR2(32)
CAN_UNDROP       VARCHAR2(3)
CAN_PURGE        VARCHAR2(3)
RELATED          NOT NULL NUMBER
BASE_OBJECT      NOT NULL NUMBER
PURGE_OBJECT     NOT NULL NUMBER
SPACE            NUMBER

Object naming in the recycle bin
When a dropped table is moved to the recycle bin, the table and its associated objects are given system-generated names.
Format of objects is : BIN$unique_id$version

where:
  • unique_id is a 26-character globally unique identifier for this object, which makes the recycle bin name unique across all databases
  • version is a version number assigned by the database

Enabling and Disabling the Recycle Bin
To disable the recycle bin, issue one of the following statements:
SQL>ALTER SESSION SET recyclebin = OFF;
or
SQL>ALTER SYSTEM SET recyclebin = OFF scope=SPFILE;

To enable the recycle bin, issue one of the following statements:
SQL>ALTER SESSION SET recyclebin = ON;
or
SQL>ALTER SYSTEM SET recyclebin = ON scope=SPFILE;

Remark :For modification at the system level, you need to restart your database.

Viewing the content of the recycle bin
2 views available :
  • USER_RECYCLEBIN
  • DBA_RECYCLEBIN

SQL>SELECT object_name, original_name FROM dba_recyclebin
   WHERE owner = 'XXX';
SQL>show recyclebin

Restoring table from recycle bin
Issue the following command :
FLASHBACK TABLE <my_table> TO BEFORE DROP;

If you want to rename in the same time the table :
SQL>FLASHBACK TABLE <my_table> TO BEFORE DROP RENAME TO <new_table_name>;

More reference on flashback database: see @ http://abcdba.com/abcdbabackupdataguardhowtoconfigureaflashbackdb

mardi 30 août 2011

Oracle 11g: Recover loss of datafile in NOARCHIVELOG database

Using SQLplus
First of all, you have to identify the missing or corrupted file using the alert log or the v$recover_file view.

Once the datafile identified, you need to stop the database :
SQL>shutdown immediate;

Copy your backup of the file at the same place of your missing datafile.

Startup your database :
SQL>startup;
ORACLE instance started.

Total System Global Area  728199168 bytes
Fixed Size                  1338924 bytes
Variable Size             213909972 bytes
Database Buffers          503316480 bytes
Redo Buffers                9633792 bytes
Database mounted.
ORA-01113: file 44 needs media recovery
ORA-01110: data file 44: '/database/VOLATI/data/DATA_TEST.dbf'
SQL>

As you can see the file you copy need media recovery.

if you check the status of the database, you can see that the database is not opened but stay MOUNT state:
SQL> select status from v$instance;
STATUS
------------
MOUNTED
SQL>

Now copy the corrupted file or missing one from your last backup to the previous location of the file.

Here you can ask for media recovery on datafile with file id 44 :
SQL>recover datafile 44;

May be you may need to set the datafile online :
SQL>alter database datafile '/database/VOLATI/data/DATA_TEST.dbf' online;
or
SQL>alter database datafile 44 online;

If the recovery on a unique file fails, then try to recover the entire database by restoring all the data files and also the control files from the last backup of your database (remember as your database is in noarchive log, may be you lost some data).


Using RMAN
$>rman target /
RMAN>sql 'alter database datafile 44 offline';
RMAN>restore datafile 44;
RMAN>recover datafile 44;
RMAN>sql 'alter database datafile 44 online';

Oracle 11g : How to know which file to recover

Using SQLplus
Connect to the database and use the following sql instruction :
SQL>select * from v$recover_file;
     FILE# ONLINE  ONLINE_ ERROR                                                                CHANGE# TIME
---------- ------- ------- ----------------------------------------------------------------- ---------- ---------
        44 ONLINE  ONLINE  FILE NOT FOUND                                                             0
As you can see the file with id 44 is missing.

To retrieve the name of the datafile you can write :
SQL>select file#, name from v$datafile join v$recovery_file using( file#);

Using RMAN
To validate the structure of the database.
$>rman target /
RMAN>validate database;
If you want to validate also logical block failure :
$>rman target /
RMAN>validate database CHECK LOGICAL;

To list all database failure that need to be restored or recovered:
$>rman target /
RMAN>list failure all;

To ask an advise from the data recovery advisor :
$>rman target /
RMAN>advise failure;

To ask rman to recover all failure that could be repaired automatically :
$>rman target /
RMAN>repair failure;
The 'repair failure' command need to be executed after the 'advise failure' command because 'advise failure' registers all problems in the Automatic Diagnostic Repository.

Oracle 11g: Recover the lost of a redo log


Redo log file is part of a redo log group that have more than one file
  • Identify the missing or corrupted redo log file from alert log
  • Drop the log file member from the group:
    SQL>alter database drop logfile member '<redo_log_file_name>';
  • Add a new file redo log file member in the previous group :
    SQL>alter database add logfile member '<redo_log_file_name>' to group #;

Redo log file is unique inside a redo log group
  • May be you can drop the group and recreate a new one (if redo log not ACTIVE or CURRENT) :
    SQL>alter database drop logfile group #;
    SQL>alter database add logfile group # ('<redo_log_file_name>') SIZE 150M;
  • May be you can clear the log file of the group that is corrupted or missing.
    • If the database is in NOARCHIVELOG or if the redo log has been archived you can write :
      SQL>alter database clear logfile group #;
    • If the database is in ARCHIVELOG and the redo log file has not been archived :
      SQL>alter database clear unarchived logfile group #;
  • If the redo log is ACTIVE then you can try :
    SQL>alter system checkpoint;
    If the checkpoint fails then you need to perform a complete recovery from a previous backup.


Remark : A log cannot be dropped or cleared until the thread's checkpoint has advanced out of the log.
If the database is not open, then open it. Crash recovery will advance the checkpoint.
If the database is open force a global checkpoint. If the log is corrupted so that the database cannot be opened, it may be necessary to do incomplete recovery until cancel at this log.

For an incomplete recovery :
SQL>RECOVER DATABASE UNTIL CANCEL;
SQL>ALTER DATABASE OPEN RESETLOGS;
SQL>SHUTDOWN IMMEDIATE;
SQL>STARTUP;

Remark : If you are unable to recover a datafile with redolog (cause redolog is corrupted) and you accept to loose data from datafile then you can use :
SQL>alter database clear logfile unrecoverable datafile;
Oracle requires that you use the unrecoverable datafile option when the particular redo log group is both unarchived and you cannot get the data from it. It looks like Oracle expects that you had already tried to recover the datafile and could not because of a bad redo log.

Note seen from another site (Don't remember where cause just have a copy past):
Oracle Recovery with _allow_resetlogs_corruption

Recovery of a database using the undocumented parameter _allow_resetlogs_corruption should be regarded as a last-ditch, emergency recovery scenario only, and should not be attempted until all other avenues of recovery have been exhausted.
Note that Oracle will not support a database that has been recovered using this method unless it is subsequently exported and rebuilt.
Essentially, using _allow_resetlogs_corruption forces the opening of the datafiles even if their SCNs do not match up; then, on the next checkpoint, the old SCN values are overwritten. This could leave the database in an unknown state as far as concurrency.
To use this option:
SQL>alter system set "_allow_resetlogs_corruption"=true scope=spfile;
SQL>shutdown immediate;
SQL>startup mount;
SQL>recover database until cancel;
CANCEL
SQL>alter database open resetlogs;
SQL>shutdown immediate;
SQL>startup;

lundi 29 août 2011

Oracle 11g: Recover the loss of a control file

Using RMAN
  • Shutdown the database
  • Statup the database in NOMOUNT
  • Use rman to recover the control file :
    $>rman target /
    rman>startup nomount
    rman>restore controlfile from '+DATA/<oracle_sid>/controlfile/current.xxx.yyyy';
    rman>alter database mount;
    rman>alter database open;
    
You can also restore the control file from an autobackup :
...
RMAN> RESTORE CONTROLFILE FROM AUTOBACKUP;
...
Using files from file system
  • Check the alert log file to retrieve the lost controlfile
  • Shutdown the database
  • Copy a multiplexe control file (remember control file must be multiplexed and Oracle recommends having at least three copies of the control file) into the missing one.
  • Startup the database in the NOMOUNT state
  • Execute the following command under SQLplus:
    SQL> RECOVER CONTROLFILE USING BACKUP CONTROLFILE UNTIL CANCEL;
  • Open the database with RESETLOGS option:
    SQL> ALTER DATABASE MOUNT;
    SQL> ALTER DATABASE OPEN RESETLOGS;

Oracle 11g : Data recovery advisor activation


To activate the Data recovery advisor in single instance :
$> rman target /
rman> list failure all;

vendredi 26 août 2011

Oracle 11g: Online user backup

Today I have learned a new thing about backup and recovery on how to keep a database in backup mode.
Even if i know that Oracle strongly recommend the use of RMAN for backup and recovery . I thought, this would be good thing to learn how to make an online backup of the database using the legacy method of backup, i.e. ALTER TABLESPACE | DATABASE BEGIN/END BACKUP.

Database in Archive log
First of all, the database must be in archive log mode.
To do this, shutdown the database and be sure that the database will not be in recovery state (avoid shuting down with abort option).
After shuting down, start the database in mount state (archive log could only be activated in mount state).
SQL>shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> startup mount;
ORACLE instance started.

Total System Global Area 1807921152 bytes
Fixed Size                  1337128 bytes
Variable Size            1073744088 bytes
Database Buffers          721420288 bytes
Redo Buffers               11419648 bytes
Database mounted.

Now, the database can be set in archivelog mode.
SQL>alter database archivelog;
Database altered.

SQL>archive log list;
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            /opt/oracle/11.2.0/dbs/arch
Oldest online log sequence     299
Current log sequence           306

SQL>alter database open;
Database altered.

Backup the entire database
Since Oracle 11g all the files could be set in backup at once :
SQL>alter database begin backup;

Then you can check if all file are in backup mode :
select * from v$backup;

     FILE# STATUS                CHANGE# TIME
---------- ------------------ ---------- ---------
         1 ACTIVE                      0
         2 ACTIVE                      0
         3 ACTIVE                      0
         4 ACTIVE                      0
         5 ACTIVE                      0
         6 ACTIVE               33564182 26-AUG-11
         7 ACTIVE                      0

7 rows selected.

Now you can copy all the database files using the system command line.

To remove the database from the backup mode, use:
SQL>alter database end backup;

Backup some parts of the database
Instead of using the alter database begin/end backup, it is possible to set each tablespace in backup mode using the following sql command :
SQL>alter tablespace <tablespace_name> begin backup;

To retrieve all tablespaces and associated datafiles use the DBA_DATA_FILES table.

Here is an example showing the procedure to set the system table space in backup mode :
SQL>alter tablespace system begin backup;
Tablespace altered.
SQL>select * from v$backup;

     FILE# STATUS                CHANGE# TIME
---------- ------------------ ---------- ---------
         1 ACTIVE               33587729 26-AUG-11
         2 NOT ACTIVE                  0
         3 NOT ACTIVE                  0
         4 NOT ACTIVE                  0
         5 NOT ACTIVE                  0
         6 NOT ACTIVE           33564182 26-AUG-11
         7 NOT ACTIVE                  0

7 rows selected.

SQL>alter tablespace system end backup;
Tablespace altered.

Remark : READ ONLY, INVALID & OFFLINE tablespaces could not be set in backup mode. They could be copied directly because they could not be accessed or modified by any users.

Remark : Don't forget to backup the server parameter file, the control files and current redo log files.
To backup the controle file in a specific location and file name :
SQL>alter database backup controlfile to '&repertoire/control.ctl' REUSE ;
Before to copy the redo log, look at which redo log is currently in used :
SQL> select * from v$log;

    GROUP#    THREAD#  SEQUENCE#      BYTES  BLOCKSIZE    MEMBERS ARC STATUS           FIRST_CHANGE# FIRST_TIME          NEXT_CHANGE# NEXT_TIME
---------- ---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ------------------- ------------ -------------------
         1          1        306  209715200        512          1 YES INACTIVE              33559556 26/08/2011 12:03:45     33584866 26/08/2011 14:44:25
         2          1        307  209715200        512          1 YES INACTIVE              33584866 26/08/2011 14:44:25     33585365 26/08/2011 14:46:41
         3          1        308  209715200        512          1 YES INACTIVE              33585365 26/08/2011 14:46:41     33585368 26/08/2011 14:46:43
         8          1        305  209715200        512          1 YES INACTIVE              33559526 26/08/2011 12:02:37     33559556 26/08/2011 12:03:45
         5          1        302  209715200        512          1 YES INACTIVE              33302574 22/08/2011 01:18:56     33428871 24/08/2011 14:36:26
         6          1        303  209715200        512          1 YES INACTIVE              33428871 24/08/2011 14:36:26     33512626 25/08/2011 17:04:01
         7          1        304  209715200        512          1 YES INACTIVE              33512626 25/08/2011 17:04:01     33559526 26/08/2011 12:02:37
         4          1        309  209715200        512          1 NO  CURRENT               33585368 26/08/2011 14:46:43   2.8147E+14

8 rows selected.
The current redo log is the redo log member #1 of the group #4.
To retrieve the name of the redo log take a look at the v$logfile table or use the following command :
select l.status, l.group#,l.members, f.member from v$log l inner join v$logfile f on( l.group#=f.group#);

Once the current redo log file identified, perform a swith log file using:
SQL>alter system switch logfile;

Then you can copy the redo log file in your backup or recovery area.

jeudi 25 août 2011

Oracle 11g : Dynamic Performance Statistics

Oracle 11g provides 3 levels of performance statistics :
  • System statistics
  • Session statistics
  • Service statistics

For each level, Oracle provide cumulative statistics and Wait event statistics.

Dynamic Statistics Views
System level Session level Service level
Cumulative view V$SYSSTAT V$SESSSTAT V$SERVICE_STAT
Wait event view V$SYSTEM_EVENT V$SESSION_EVENT V$SERVICE_EVENT


For memory, you can also take a look at :
Memory views
V$SGAINFO
V$SGASTAT
V$PGASTAT
V$BUFFER_POOL_CACHE
V$LIBRARYCACHE

For contention, you can look at :
Contention views
V$LOCK
V$UNDOSTAT
V$WAITSTAT
V$LATCH

Oracle 11g : AMM & ASMM

AMM - Automatic Memory Management
AMM allows the dba to define precisely the global use of memory for the entire database.
This memory allocation is divided between the SGA component and all PGA components. This memory distribution is done automatically by the database memory size advisor.
There is 2 parameters to set in order to activate the Automatic Memory Management :
  • MEMORY_TARGET : Minumum memory we wish to allow for SGA and all PGAs
  • MEMORY_MAX_TARGET : Maximum memory that MEMORY_TARGET should not exceed
you enable the automatic memory management by setting only a target memory size initialization parameter (MEMORY_TARGET) and optionally a maximum memory size initialization parameter (MEMORY_MAX_TARGET).

When using automatic memory management, the SGA_TARGET and PGA_AGGREGATE_TARGET act as minimum size settings for their respective memory areas. To allow Oracle to take full control of the memory management, these parameters should be set to zero.

The activation of the AMM implies the activation of ASMM and the activation of the PGA memory advisor.

Remark :
  • If MEMORY_TARGET is set and MEMORY_MAX_TARGET is omitted then MEMORY_MAX_TARGET is set automatically with the value of MEMORY_TARGET.
  • If MEMORY_MAX_TARGET is set and MEMORY_TARGET omitted then MEMORY_TARGET is set to 0. MEMORY_TARGET could be updated dynamicaly using ALTER SYSTEM. After startup, you can then dynamically change MEMORY_TARGET to a nonzero value, provided that it does not exceed the value of MEMORY_MAX_TARGET.
  • We assume commonly that MEMORY_TARGET = SGA_TARGET + PGA_AGGREGATE_TARGET.

Remark : Any modification on these parameters implies the reboot of the entire database. MEMORY_MAX_TARGET is not a dynamic initialization parameter.

ASMM - Automatic Shared Memory Management
ASMM allows the dba to define the memory used by the SGA only.

There is 2 parameters to set in order to activate the Automatic Shared Memory Management :
  • SGA_TARGET : Minumum memory we wish to allow for SGA (not the PGAs)
  • SGA_MAX_SIZE : Maximum memory that SGA_TARGET should not exceed
You enable the automatic shared memory management feature by setting the SGA_TARGET parameter to a nonzero value.
Note:
The STATISTICS_LEVEL initialization parameter must be set to TYPICAL (the default) or ALL for automatic shared memory management to function.

Remark :
  • If SGA_TARGET is set then SGA_MAX_SIZE is set automatically with the value of SGA_TARGET.
  • If SGA_MAX_SIZE is defined then SGA_TARGET is setted by default to 0.
  • SGA_MAX_SIZE is a static parameter. You need to reboot the instance if updated.

Remark :
  • If ASMM is activated then the SGA memory size advisor is activated.
  • If ASMM is activated for the first time then it is not allowed to set a value for the following database parameters in the initialization parameters file:
    • SHARED_POOL_SIZE
    • DB_CACHE_SIZE
    • LARGE_POOL_SIZE
    • JAVA_POOL_SIZE
    • STREAM_POOL_SIZE
  • Setting a value for one component of the SGA when ASMM is activated implies that we want to overload the behaviour the advisor. If the value is less than the value calculated by ASMM then the value is defined as a minimal requirement. If the value is more then the value defined by ASMM then the size of the component is increased to fit the dba requirement. The previous parameter could be altered using ALTER SYSTEM (except for the LOG_BUFFER parameter that is static after instance startup).

mardi 23 août 2011

Oracle 11g : Alert's type and associated tables


There is 2 kind of alerts in database :
  • Stateful alerts : Alerts based on threshold (Physical reads Per sec, User commits Per sec)
  • Stateless alerts : Alerts that does not depends on any kind of threshold (Snapshot too old, Recovery are low free space)

Stateless alerts
Stateless alerts are logged in the DBA_ALERT_HISTORY table.

Stateful alerts
Stateful alerts are logged in the DBA_OUTSTANDING_ALERTS table.
When the situation is resolved, the alert is cleared from this table and moved automatically to the DBA_ALERT_HISTORY.

Action on alerts
An alert could be cleared from the history. Its status changes to cleared and is keeped in the table.
An alert could be purged from the alert log history. In this way, the alert is removed from the repository and discared.


Oracle 11g: Automatic Workload Repository

General points
  • The MMON (Managability Monitor) database process generates a snapshot of the database every 60 minutes.
  • Snapshots are logged over a period of 8 days. (Retention policy by default, could be changed using the enterprise Manager console or through DBMS_WORKLOAD_REPOSITORY package)
  • The AWR repository is the property of the SYSMAN user and stored in the SYSAUX tablespace.

Baseline AWR
A baseline AWR is a set of snapshots between two snapshot id or two dates.
A baseline is identified using a unique name.
A baseline is keep in database until the baseline is deleted.

To create a baseline use :
Function
DBMS_WORKLOAD_REPOSITORY.CREATE_BASELINE(
    START_SNAP_ID   IN NUMBER,
    END_SNAP_ID     IN NUMBER,
    BASELINE_NAME   IN VARCHAR2, 
    DBID            IN NUMBER DEFAULT,
    EXPIRATION      IN NUMBER DEFAULT
) RETURNS NUMBER;
or
Procedure
DBMS_WORKLOAD_REPOSITORY.CREATE_BASELINE(
    START_TIME      IN DATE,
    END_TIME        IN DATE,
    BASELINE_NAME   IN VARCHAR2,
    DBID            IN NUMBER DEFAULT,
    EXPIRATION      IN NUMBER DEFAULT
);

To drop a baseline use :
Procedure
DBMS_WORKLOAD_REPOSITORY.DROP_BASELINE(
    BASELINE_NAME   IN VARCHAR2,
    CASCADE         IN BOOLEAN  DEFAULT,
    DBID            IN NUMBER   DEFAULT
);

Baseline information can be queried from the DBA_HIST_BASELINE view.

How to generate an extra snapshot
Write the following sql command :
SQL>exec dbms_workload_repository.create_snapshot('ALL');

How to list all snapshot registered in the AWR repository?
Select the DBA_HIST_SNAPSHOT to retrive the snapshot id from all snapshot registered in the AWR repository.
This table contains the following information :
SQL>desc DBA_HIST_SNAPSHOT;

SNAP_ID             NOT NULL NUMBER
DBID                NOT NULL NUMBER
INSTANCE_NUMBER     NOT NULL NUMBER
STARTUP_TIME        NOT NULL TIMESTAMP(3)
BEGIN_INTERVAL_TIME NOT NULL TIMESTAMP(3)
END_INTERVAL_TIME   NOT NULL TIMESTAMP(3)
FLUSH_ELAPSED       INTERVAL DAY(5) TO SECOND(1)
SNAP_LEVEL          NUMBER
ERROR_COUNT         NUMBER
SNAP_FLAG           NUMBER

Statistics level
Adjust the STATISTICS_LEVEL database parameter :
  • ALL : Generaly not used
  • TYPICAL : Use default preferences, gives more advise of the database
  • BASIC : Statistics collections not active
This parameter is dynamic.

Changing the snapshot frequency and retention policy
By default snapshots of the relevant data are taken every hour and retained for 8 days.
The default values for these settings can be altered using the following procedure.
BEGIN
      DBMS_WORKLOAD_REPOSITORY.modify_snapshot_settings(
        retention => 43200,        -- Minutes (= 30 Days). Current value retained if NULL.
        interval  => 30);          -- Minutes. Current value retained if NULL.
    END;
    /
An interval of "0" switches off snapshot collection, which in turn stops much of the self-tuning functionality, hence this is not recommended. Automatic collection is only possible if the STATISTICS_LEVEL parameter is set to TYPICAL or ALL. If the value is set to BASIC manual snapshots can be taken, but they will be missing some statistics.

To retirve the retention delay and snapshot interval :
SQL> select * from dba_hist_wr_control;

      DBID SNAP_INTERVAL        RETENTION            TOPNSQL
---------- -------------------- -------------------- ----------
2058702964 +00000 01:00:00.0    +00008 00:00:00.0    DEFAULT

Workload Repository Views
The following workload repository views are available:
V$ACTIVE_SESSION_HISTORY     - Displays the active session history (ASH) sampled every second.
    V$METRIC                     - Displays metric information.
    V$METRICNAME                 - Displays the metrics associated with each metric group.
    V$METRIC_HISTORY             - Displays historical metrics.
    V$METRICGROUP                - Displays all metrics groups.
    DBA_HIST_ACTIVE_SESS_HISTORY - Displays the history contents of the active session history.
    DBA_HIST_BASELINE            - Displays baseline information.
    DBA_HIST_DATABASE_INSTANCE   - Displays database environment information.
    DBA_HIST_SNAPSHOT            - Displays snapshot information.
    DBA_HIST_SQL_PLAN            - Displays SQL execution plans.
    DBA_HIST_WR_CONTROL          - Displays AWR settings.

Workload Repository Reports

Oracle provides two scripts to produce workload repository reports (awrrpt.sql and awrrpti.sql). They are similar in format to the statspack reports and give the option of HTML or plain text formats.
The two reports give essential the same output but the awrrpti.sql allows you to select a single instance. The reports can be generated as follows.
@$ORACLE_HOME/rdbms/admin/awrrpt.sql
or for a specific database instance
@$ORACLE_HOME/rdbms/admin/awrrpti.sql


to compare two periods of snapshot :
@$ORACLE_HOME/rdbms/admin/awrddrpt.sql
or for a specific database instance
@$ORACLE_HOME/rdbms/admin/awrddrpi.sql

More references
See more @ http://download.oracle.com/docs/cd/B28359_01/server.111/b28274/autostat.htm

Oracle11g: Oracle optimizer and Statistics collections

Statistics collections activation
The STATISTICS_LEVEL is the initial database parameter used to enable/disable advisories statistics collections.
There here 3 levels for the STATISTICS_LEVEL parameter :
  • BASIC : Desactivates all the advisory statistics collections
  • TYPICAL : Activates some advisory statistics collections
  • ALL : All advisory are activated

SQL>alter system set statistics_level=basic;
System altered.
SQL>SELECT statistics_name,
         session_status,
         system_status,
         activation_level,
         session_settable
  FROM   v$statistics_level
order by statistics_name;


STATISTICS_NAME                SESSION_STATUS  SYSTEM_STATUS   ACTIVATION_LEVEL SESSION_SETTABLE
------------------------------ --------------- --------------- ---------------- ----------------
Active Session History         DISABLED        DISABLED        TYPICAL          NO
Adaptive Thresholds Enabled    DISABLED        DISABLED        TYPICAL          NO
Automated Maintenance Tasks    DISABLED        DISABLED        TYPICAL          NO
Bind Data Capture              DISABLED        DISABLED        TYPICAL          NO
Buffer Cache Advice            DISABLED        DISABLED        TYPICAL          NO
Global Cache Statistics        DISABLED        DISABLED        TYPICAL          NO
Longops Statistics             DISABLED        DISABLED        TYPICAL          NO
MTTR Advice                    DISABLED        DISABLED        TYPICAL          NO
Modification Monitoring        DISABLED        DISABLED        TYPICAL          NO
PGA Advice                     DISABLED        DISABLED        TYPICAL          NO
Plan Execution Sampling        DISABLED        DISABLED        TYPICAL          YES

STATISTICS_NAME                SESSION_STATUS  SYSTEM_STATUS   ACTIVATION_LEVEL SESSION_SETTABLE
------------------------------ --------------- --------------- ---------------- ----------------
Plan Execution Statistics      DISABLED        DISABLED        ALL              YES
SQL Monitoring                 DISABLED        DISABLED        TYPICAL          YES
Segment Level Statistics       DISABLED        DISABLED        TYPICAL          NO
Shared Pool Advice             DISABLED        DISABLED        TYPICAL          NO
Streams Pool Advice            DISABLED        DISABLED        TYPICAL          NO
Threshold-based Alerts         DISABLED        DISABLED        TYPICAL          NO
Time Model Events              DISABLED        DISABLED        TYPICAL          YES
Timed OS Statistics            DISABLED        DISABLED        ALL              YES
Timed Statistics               ENABLED         ENABLED         TYPICAL          YES
Ultrafast Latch Statistics     DISABLED        DISABLED        TYPICAL          NO
Undo Advisor, Alerts and Fast  DISABLED        DISABLED        TYPICAL          NO

STATISTICS_NAME                SESSION_STATUS  SYSTEM_STATUS   ACTIVATION_LEVEL SESSION_SETTABLE
------------------------------ --------------- --------------- ---------------- ----------------
V$IOSTAT_* statistics          DISABLED        DISABLED        TYPICAL          NO

23 rows selected.



SQL>alter system set statistics_level=typical;
System altered.
SQL>SELECT statistics_name,
         session_status,
         system_status,
         activation_level,
         session_settable
  FROM   v$statistics_level
order by statistics_name;

STATISTICS_NAME                SESSION_STATUS  SYSTEM_STATUS   ACTIVATION_LEVEL SESSION_SETTABLE
------------------------------ --------------- --------------- ---------------- ----------------
Active Session History         DISABLED        DISABLED        TYPICAL          NO
Adaptive Thresholds Enabled    DISABLED        DISABLED        TYPICAL          NO
Automated Maintenance Tasks    DISABLED        DISABLED        TYPICAL          NO
Bind Data Capture              DISABLED        DISABLED        TYPICAL          NO
Buffer Cache Advice            DISABLED        DISABLED        TYPICAL          NO
Global Cache Statistics        DISABLED        DISABLED        TYPICAL          NO
Longops Statistics             DISABLED        DISABLED        TYPICAL          NO
MTTR Advice                    DISABLED        DISABLED        TYPICAL          NO
Modification Monitoring        DISABLED        DISABLED        TYPICAL          NO
PGA Advice                     DISABLED        DISABLED        TYPICAL          NO
Plan Execution Sampling        ENABLED         DISABLED        TYPICAL          YES

STATISTICS_NAME                SESSION_STATUS  SYSTEM_STATUS   ACTIVATION_LEVEL SESSION_SETTABLE
------------------------------ --------------- --------------- ---------------- ----------------
Plan Execution Statistics      DISABLED        DISABLED        ALL              YES
SQL Monitoring                 ENABLED         DISABLED        TYPICAL          YES
Segment Level Statistics       DISABLED        DISABLED        TYPICAL          NO
Shared Pool Advice             DISABLED        DISABLED        TYPICAL          NO
Streams Pool Advice            DISABLED        DISABLED        TYPICAL          NO
Threshold-based Alerts         DISABLED        DISABLED        TYPICAL          NO
Time Model Events              ENABLED         DISABLED        TYPICAL          YES
Timed OS Statistics            DISABLED        DISABLED        ALL              YES
Timed Statistics               ENABLED         ENABLED         TYPICAL          YES
Ultrafast Latch Statistics     DISABLED        DISABLED        TYPICAL          NO
Undo Advisor, Alerts and Fast  DISABLED        DISABLED        TYPICAL          NO

STATISTICS_NAME                SESSION_STATUS  SYSTEM_STATUS   ACTIVATION_LEVEL SESSION_SETTABLE
------------------------------ --------------- --------------- ---------------- ----------------
V$IOSTAT_* statistics          DISABLED        DISABLED        TYPICAL          NO

23 rows selected.



SQL>alter system set statistics_level=all;
System altered.
SQL>SELECT statistics_name,
         session_status,
         system_status,
         activation_level,
         session_settable
  FROM   v$statistics_level
order by statistics_name;

STATISTICS_NAME                SESSION_STATUS  SYSTEM_STATUS   ACTIVATION_LEVEL SESSION_SETTABLE
------------------------------ --------------- --------------- ---------------- ----------------
Active Session History         DISABLED        DISABLED        TYPICAL          NO
Adaptive Thresholds Enabled    DISABLED        DISABLED        TYPICAL          NO
Automated Maintenance Tasks    DISABLED        DISABLED        TYPICAL          NO
Bind Data Capture              DISABLED        DISABLED        TYPICAL          NO
Buffer Cache Advice            DISABLED        DISABLED        TYPICAL          NO
Global Cache Statistics        DISABLED        DISABLED        TYPICAL          NO
Longops Statistics             DISABLED        DISABLED        TYPICAL          NO
MTTR Advice                    DISABLED        DISABLED        TYPICAL          NO
Modification Monitoring        DISABLED        DISABLED        TYPICAL          NO
PGA Advice                     DISABLED        DISABLED        TYPICAL          NO
Plan Execution Sampling        ENABLED         DISABLED        TYPICAL          YES

STATISTICS_NAME                SESSION_STATUS  SYSTEM_STATUS   ACTIVATION_LEVEL SESSION_SETTABLE
------------------------------ --------------- --------------- ---------------- ----------------
Plan Execution Statistics      ENABLED         DISABLED        ALL              YES
SQL Monitoring                 ENABLED         DISABLED        TYPICAL          YES
Segment Level Statistics       DISABLED        DISABLED        TYPICAL          NO
Shared Pool Advice             DISABLED        DISABLED        TYPICAL          NO
Streams Pool Advice            DISABLED        DISABLED        TYPICAL          NO
Threshold-based Alerts         DISABLED        DISABLED        TYPICAL          NO
Time Model Events              ENABLED         DISABLED        TYPICAL          YES
Timed OS Statistics            ENABLED         DISABLED        ALL              YES
Timed Statistics               ENABLED         ENABLED         TYPICAL          YES
Ultrafast Latch Statistics     DISABLED        DISABLED        TYPICAL          NO
Undo Advisor, Alerts and Fast  DISABLED        DISABLED        TYPICAL          NO

STATISTICS_NAME                SESSION_STATUS  SYSTEM_STATUS   ACTIVATION_LEVEL SESSION_SETTABLE
------------------------------ --------------- --------------- ---------------- ----------------
V$IOSTAT_* statistics          DISABLED        DISABLED        TYPICAL          NO

23 rows selected.

The gathering of statistics is performed each day and stored in a repository for 30 days.

Gathering statistics
Use the DBMS_STATS to retrieve statistics for the entire database, a schema or a specific table (DBMS_STATS.GATHER_*_STATS()).
For the database : DBMS_STATS.GATHER_DATABASE_STATS()
For schema : DBMS_STATS.GATHER_SCHEMA_STATS()
For a table : DBMS_STATS.GATHER_TABLE_STATS()

Use DBMS_STATS.GATHER_*_STATS() when 10% of the database, schema or table changes during the last 24 hours.

Removing statistics from the repository
Use the DBMS_STATS.DELETE_*_STATS() procedures.

Statistics preferences
The DBA can assign default or prefered parameters values for users that invoke the DBMS_STATS package.
The DBMS_STATS.SET_*_PREFS() procedures of the package allows the DBA to set preferences for the following parameters :
CASCADE
DEGREE
ESTIMATE_PERCENT
NO_INVALIDATE
METHOD_OPT
GRANULARITY
INCREMENTAL
PUBLISH
STALE_PERCENT

Preferences can be deleted using DBMS_STATS.DELETE_*_PREFS()
It is all possible to reset values to the initial setup using DBMS_STATS.RESET_PARAM_PREFS()

Remark : If preferences have been setted at a table level, setting preferences at the schema or database level overwritting preferences setted previously at the table level.

View statistics parameters preferences
Object prefrences can be retrieve from the DBA_TAB_STAT_PREFS table.
For preferences setted are a global level, use the DBMS_STATS.GET_PREFS() function of the DBMS_STATS package.