Friday, July 31, 2015

Oracle Backup & Recovery Series - 4 (RMAN BKP Configuration)

In my previous posts, all the settings for recoverability was discussed, If you have not read them yet, maybe you can start reading them now. In this blogpost we will start discussing on the backup configurations we should be concentrating on.

 1) Multiplexing Controlfile
2) Multiplex Redo Logs & FRA Setup
3) Enable Archive Log Mode
 In the 3rd post in the series, I did end up with a RMAN full database backup, If you notice the RMAN backup session the dates mentioned where in the format "YYYY-MM-DD" only. But backup and recovery activities are mostly time precision ed activity, for e.g. we may land up in a situation where I need to recover my database until time which would be as precised as 2015-07-31:23:54:53.

 1) Let's list my existing backups (I have only 1 backup at a time, again space constraint  :)  )





ajithpathiyil1:/home/oracle[ajith1]$ rman target /

Recovery Manager: Release 11.2.0.4.0 - Production on Fri Jul 31 23:56:32 2015

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

connected to target database: AJITHPAT (DBID=2331484960)

RMAN> list backup;


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


BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
5       Full    1.50G      DISK        00:00:14     31-JUL-15
        BP Key: 5   Status: AVAILABLE  Compressed: NO  Tag: TAG20150731T235438
        Piece Name: +BACKUP/ajithpathiyil/backupset/2015_07_31/nnndf0_tag20150731t235438_0.264.886550079
  List of Datafiles in backup set 5
  File LV Type Ckp SCN    Ckp Time  Name
  ---- -- ---- ---------- --------- ----
  1       Full 4438822    31-JUL-15 +DATA/ajithpathiyil/datafile/system.256.862005857
  2       Full 4438822    31-JUL-15 +DATA/ajithpathiyil/datafile/sysaux.257.862005859
  3       Full 4438822    31-JUL-15 +DATA/ajithpathiyil/datafile/undotbs1.258.862005861
  4       Full 4438822    31-JUL-15 +DATA/ajithpathiyil/datafile/users.259.862005861
  5       Full 4438822    31-JUL-15 +DATA/ajithpathiyil/datafile/example.264.862005925
  6       Full 4438822    31-JUL-15 +DATA/ajithpathiyil/datafile/undotbs2.265.862005979
  7       Full 4438822    31-JUL-15 +DATA/ajithpathiyil/datafile/undotbs3.266.862005981

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
6       Full    17.70M     DISK        00:00:01     31-JUL-15
        BP Key: 6   Status: AVAILABLE  Compressed: NO  Tag: TAG20150731T235438
        Piece Name: +BACKUP/ajithpathiyil/backupset/2015_07_31/ncsnf0_tag20150731t235438_0.267.886550095
  SPFILE Included: Modification time: 31-JUL-15
  SPFILE db_unique_name: AJITHPATHIYIL
  Control File Included: Ckp SCN: 4438862      Ckp time: 31-JUL-15

RMAN>



2) We can use the NLS_DATE_FORMAT environment variable to change the timestamp format as shown below.



ajithpathiyil1:/home/oracle[ajith1]$  ls -ltra .bashrc
-rw-r--r-- 1 oracle oinstall 124 Aug 10  2014 .bashrc
ajithpathiyil1:/home/oracle[ajith1]$ cat .bashrc
# .bashrc

# Source global definitions
if [ -f /etc/bashrc ]; then
        . /etc/bashrc
fi

# User specific aliases and functions

export NLS_DATE_FORMAT="yyyy-mm-dd:hh24:mi:ss"
ajithpathiyil1:/home/oracle[ajith1]$



3) The environment variable will be set only in a new terminal, So, I opened a new terminal session and listed my RMAN backups, and we have the actual timestamps of the backup as shown below.



ajithpathiyil1:/home/oracle[ajith1]$ rman target /

Recovery Manager: Release 11.2.0.4.0 - Production on Sat Aug 1 03:09:33 2015

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

connected to target database: AJITHPAT (DBID=2331484960)

RMAN> list backup;

using target database control file instead of recovery catalog

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


BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ -------------------
5       Full    1.50G      DISK        00:00:14     2015-07-31:23:54:53
        BP Key: 5   Status: AVAILABLE  Compressed: NO  Tag: TAG20150731T235438
        Piece Name: +BACKUP/ajithpathiyil/backupset/2015_07_31/nnndf0_tag20150731t235438_0.264.886550079
  List of Datafiles in backup set 5
  File LV Type Ckp SCN    Ckp Time            Name
  ---- -- ---- ---------- ------------------- ----
  1       Full 4438822    2015-07-31:23:54:39 +DATA/ajithpathiyil/datafile/system.256.862005857
  2       Full 4438822    2015-07-31:23:54:39 +DATA/ajithpathiyil/datafile/sysaux.257.862005859
  3       Full 4438822    2015-07-31:23:54:39 +DATA/ajithpathiyil/datafile/undotbs1.258.862005861
  4       Full 4438822    2015-07-31:23:54:39 +DATA/ajithpathiyil/datafile/users.259.862005861
  5       Full 4438822    2015-07-31:23:54:39 +DATA/ajithpathiyil/datafile/example.264.862005925
  6       Full 4438822    2015-07-31:23:54:39 +DATA/ajithpathiyil/datafile/undotbs2.265.862005979
  7       Full 4438822    2015-07-31:23:54:39 +DATA/ajithpathiyil/datafile/undotbs3.266.862005981

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ -------------------
6       Full    17.70M     DISK        00:00:01     2015-07-31:23:54:55
        BP Key: 6   Status: AVAILABLE  Compressed: NO  Tag: TAG20150731T235438
        Piece Name: +BACKUP/ajithpathiyil/backupset/2015_07_31/ncsnf0_tag20150731t235438_0.267.886550095
  SPFILE Included: Modification time: 2015-07-31:23:45:44
  SPFILE db_unique_name: AJITHPATHIYIL
  Control File Included: Ckp SCN: 4438862      Ckp time: 2015-07-31:23:54:54

RMAN>



4) Also, if you looked at the backup list, the control file was not backed up, we should always have the controlfile autobackup on.



RMAN> show controlfile autobackup;

RMAN configuration parameters for database with db_unique_name AJITHPATHIYIL are:
CONFIGURE CONTROLFILE AUTOBACKUP OFF; # default

RMAN> configure controlfile autobackup on;

new RMAN configuration parameters:
CONFIGURE CONTROLFILE AUTOBACKUP ON;
new RMAN configuration parameters are successfully stored

RMAN>



5) Now, let's verify if my RMAN full backup automatically back's up my control file.



RMAN> backup database;

Starting backup at 2015-08-01:03:13:43
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=34 instance=ajith1 device type=DISK
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00002 name=+DATA/ajithpathiyil/datafile/sysaux.257.862005859
input datafile file number=00001 name=+DATA/ajithpathiyil/datafile/system.256.862005857
input datafile file number=00003 name=+DATA/ajithpathiyil/datafile/undotbs1.258.862005861
input datafile file number=00006 name=+DATA/ajithpathiyil/datafile/undotbs2.265.862005979
input datafile file number=00005 name=+DATA/ajithpathiyil/datafile/example.264.862005925
input datafile file number=00007 name=+DATA/ajithpathiyil/datafile/undotbs3.266.862005981
input datafile file number=00004 name=+DATA/ajithpathiyil/datafile/users.259.862005861
channel ORA_DISK_1: starting piece 1 at 2015-08-01:03:13:43
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03009: failure of backup command on ORA_DISK_1 channel at 08/01/2015 03:13:58
ORA-19809: limit exceeded for recovery files
ORA-19804: cannot reclaim 68157440 bytes disk space from 3221225472 limit
RMAN>


Struck with space constraint, Let me remove my existing full backup.




RMAN> list backup;


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


BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ -------------------
5       Full    1.50G      DISK        00:00:14     2015-07-31:23:54:53
        BP Key: 5   Status: AVAILABLE  Compressed: NO  Tag: TAG20150731T235438
        Piece Name: +BACKUP/ajithpathiyil/backupset/2015_07_31/nnndf0_tag20150731t235438_0.264.886550079
  List of Datafiles in backup set 5
  File LV Type Ckp SCN    Ckp Time            Name
  ---- -- ---- ---------- ------------------- ----
  1       Full 4438822    2015-07-31:23:54:39 +DATA/ajithpathiyil/datafile/system.256.862005857
  2       Full 4438822    2015-07-31:23:54:39 +DATA/ajithpathiyil/datafile/sysaux.257.862005859
  3       Full 4438822    2015-07-31:23:54:39 +DATA/ajithpathiyil/datafile/undotbs1.258.862005861
  4       Full 4438822    2015-07-31:23:54:39 +DATA/ajithpathiyil/datafile/users.259.862005861
  5       Full 4438822    2015-07-31:23:54:39 +DATA/ajithpathiyil/datafile/example.264.862005925
  6       Full 4438822    2015-07-31:23:54:39 +DATA/ajithpathiyil/datafile/undotbs2.265.862005979
  7       Full 4438822    2015-07-31:23:54:39 +DATA/ajithpathiyil/datafile/undotbs3.266.862005981

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ -------------------
6       Full    17.70M     DISK        00:00:01     2015-07-31:23:54:55
        BP Key: 6   Status: AVAILABLE  Compressed: NO  Tag: TAG20150731T235438
        Piece Name: +BACKUP/ajithpathiyil/backupset/2015_07_31/ncsnf0_tag20150731t235438_0.267.886550095
  SPFILE Included: Modification time: 2015-07-31:23:45:44
  SPFILE db_unique_name: AJITHPATHIYIL
  Control File Included: Ckp SCN: 4438862      Ckp time: 2015-07-31:23:54:54

RMAN> delete backup TAG='TAG20150731T235438';

using channel ORA_DISK_1

List of Backup Pieces
BP Key  BS Key  Pc# Cp# Status      Device Type Piece Name
------- ------- --- --- ----------- ----------- ----------
5       5       1   1   AVAILABLE   DISK        +BACKUP/ajithpathiyil/backupset/2015_07_31/nnndf0_tag20150731t235438_0.264.886550079
6       6       1   1   AVAILABLE   DISK        +BACKUP/ajithpathiyil/backupset/2015_07_31/ncsnf0_tag20150731t235438_0.267.886550095

Do you really want to delete the above objects (enter YES or NO)? YES
deleted backup piece
backup piece handle=+BACKUP/ajithpathiyil/backupset/2015_07_31/nnndf0_tag20150731t235438_0.264.886550079 RECID=5 STAMP=886550079
deleted backup piece
backup piece handle=+BACKUP/ajithpathiyil/backupset/2015_07_31/ncsnf0_tag20150731t235438_0.267.886550095 RECID=6 STAMP=886550095
Deleted 2 objects


RMAN>


4) Now, we are good for taking a full database backup.

RMAN> backup database;

Starting backup at 2015-08-01:03:14:58
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=00002 name=+DATA/ajithpathiyil/datafile/sysaux.257.862005859
input datafile file number=00001 name=+DATA/ajithpathiyil/datafile/system.256.862005857
input datafile file number=00003 name=+DATA/ajithpathiyil/datafile/undotbs1.258.862005861
input datafile file number=00006 name=+DATA/ajithpathiyil/datafile/undotbs2.265.862005979
input datafile file number=00005 name=+DATA/ajithpathiyil/datafile/example.264.862005925
input datafile file number=00007 name=+DATA/ajithpathiyil/datafile/undotbs3.266.862005981
input datafile file number=00004 name=+DATA/ajithpathiyil/datafile/users.259.862005861
channel ORA_DISK_1: starting piece 1 at 2015-08-01:03:14:58
channel ORA_DISK_1: finished piece 1 at 2015-08-01:03:15:13
piece handle=+BACKUP/ajithpathiyil/backupset/2015_08_01/nnndf0_tag20150801t031458_0.267.886562099 tag=TAG20150801T031458 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:15
Finished backup at 2015-08-01:03:15:13

Starting Control File and SPFILE Autobackup at 2015-08-01:03:15:13
piece handle=+BACKUP/ajithpathiyil/autobackup/2015_08_01/s_886562113.264.886562115 comment=NONE
Finished Control File and SPFILE Autobackup at 2015-08-01:03:15:14

RMAN>




HAPPY LEARNING!



Posted by at
Labels: ,

5 comments:

  1. UnknownAugust 7, 2015 at 6:41 AM

    Hi Ajith, very good article, thanks for providing in-depth information on Oracle DBA Technology. Please continue sharing.

    ReplyDelete
  2. Ajith PathiyilAugust 7, 2015 at 7:10 AM

    Many thanks Vasu! It's good to hear that someone is getting benefit with the blogpost. Its a motivation for more blogposts.

    ReplyDelete
  3. UnknownAugust 21, 2015 at 3:55 AM

    Nice article.Excellent information sharing in this blog and useful to Oracle learners.Oracle 11g DBA Online Training

    ReplyDelete
  4. UnknownSeptember 15, 2015 at 10:40 AM

    It is good to see the best website for all updates on oracle dba with good examples.


    Oracle DBA Tutorial

    ReplyDelete
    Replies
    1. Ajith PathiyilApril 4, 2016 at 3:07 AM

      Many thanks tom, Happy to know, It is helping somebody who is searching for a information, It is easy to learn from blogs, I go through tons of blogs when struck at work.

      Delete

Thanks for you valuable comments !

Subscribe to: Post Comments (Atom)