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 :) )
2) We can use the NLS_DATE_FORMAT environment variable to change the timestamp format as shown below.
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.
4) Also, if you looked at the backup list, the control file was not backed up, we should always have the controlfile autobackup on.
5) Now, let's verify if my RMAN full backup automatically back's up my control file.
Struck with space constraint, Let me remove my existing full backup.
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!
