20 September, 2011

RMAN Tips -- 2

Incremental Backup from a particular SCN

RMAN allows you to take an Incremental Backup "FROM SCN". Why ? For the purpose of updating a Standby Database.


[oracle@localhost ~]$ sqlplus '/ as sysdba'

SQL*Plus: Release 11.2.0.1.0 Production on Tue Sep 20 23:35:38 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, OLAP, Data Mining and Real Application Testing options

SQL> select current_scn from v$database;

CURRENT_SCN
-----------
9250081

SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@localhost ~]$
[oracle@localhost ~]$ rman target /

Recovery Manager: Release 11.2.0.1.0 - Production on Tue Sep 20 23:36:07 2011

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

connected to target database: ORCL (DBID=1229390655)

RMAN> backup as compressed backupset incremental from SCN=9250021 database format '/addtl/oracle/tmp/%U';

Starting backup at 20-SEP-11

using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=27 device type=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: SID=34 device type=DISK
backup will be obsolete on date 27-SEP-11
archived logs will not be kept or backed up
RMAN-06755: WARNING: datafile 1: incremental-start SCN is too recent; using checkpoint SCN 9249229 instead
RMAN-06755: WARNING: datafile 2: incremental-start SCN is too recent; using checkpoint SCN 9249229 instead
RMAN-06755: WARNING: datafile 3: incremental-start SCN is too recent; using checkpoint SCN 9249229 instead
RMAN-06755: WARNING: datafile 4: incremental-start SCN is too recent; using checkpoint SCN 9249229 instead
RMAN-06755: WARNING: datafile 5: incremental-start SCN is too recent; using checkpoint SCN 9249229 instead
RMAN-06755: WARNING: datafile 6: incremental-start SCN is too recent; using checkpoint SCN 9249229 instead
RMAN-06755: WARNING: datafile 7: incremental-start SCN is too recent; using checkpoint SCN 9249229 instead
RMAN-06755: WARNING: datafile 8: incremental-start SCN is too recent; using checkpoint SCN 9249229 instead
RMAN-06755: WARNING: datafile 9: incremental-start SCN is too recent; using checkpoint SCN 9249229 instead
RMAN-06755: WARNING: datafile 10: incremental-start SCN is too recent; using checkpoint SCN 9249229 instead
RMAN-06755: WARNING: datafile 11: incremental-start SCN is too recent; using checkpoint SCN 9249229 instead
RMAN-06755: WARNING: datafile 12: incremental-start SCN is too recent; using checkpoint SCN 9249229 instead
RMAN-06755: WARNING: datafile 13: incremental-start SCN is too recent; using checkpoint SCN 9249229 instead
RMAN-06755: WARNING: datafile 15: incremental-start SCN is too recent; using checkpoint SCN 9249229 instead
RMAN-06755: WARNING: datafile 16: incremental-start SCN is too recent; using checkpoint SCN 9249229 instead
RMAN-06755: WARNING: datafile 17: incremental-start SCN is too recent; using checkpoint SCN 9249229 instead
RMAN-06755: WARNING: datafile 18: incremental-start SCN is too recent; using checkpoint SCN 9249229 instead
channel ORA_DISK_1: starting compressed full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00003 name=/home/oracle/app/oracle/oradata/orcl/undotbs01.dbf
input datafile file number=00016 name=/addtl/oracle/oradata/orcl/Uniform_64KB.dbf
input datafile file number=00018 name=/addtl/oracle/oradata/orcl/MSSM.dbf
input datafile file number=00015 name=/addtl/oracle/oradata/orcl/UNDO.dbf
input datafile file number=00017 name=/usr/tmp/X.dbf
input datafile file number=00002 name=/home/oracle/app/oracle/oradata/orcl/sysaux01.dbf
input datafile file number=00001 name=/home/oracle/app/oracle/oradata/orcl/system01.dbf
input datafile file number=00004 name=/home/oracle/app/oracle/oradata/orcl/users01.dbf
input datafile file number=00014 name=/home/oracle/app/oracle/oradata/orcl/hemant_01.dbf
channel ORA_DISK_1: starting piece 1 at 20-SEP-11
channel ORA_DISK_2: starting compressed full datafile backup set
channel ORA_DISK_2: specifying datafile(s) in backup set
input datafile file number=00005 name=/home/oracle/app/oracle/oradata/orcl/example01.dbf
input datafile file number=00006 name=/home/oracle/app/oracle/oradata/orcl/FLOW_1046101119510758.dbf
input datafile file number=00007 name=/home/oracle/app/oracle/oradata/orcl/FLOW_1146416395631714.dbf
input datafile file number=00008 name=/home/oracle/app/oracle/oradata/orcl/FLOW_1170420963682633.dbf
input datafile file number=00009 name=/home/oracle/app/oracle/oradata/orcl/FLOW_1194425963955800.dbf
input datafile file number=00010 name=/home/oracle/app/oracle/oradata/orcl/FLOW_1218408858999342.dbf
input datafile file number=00011 name=/home/oracle/app/oracle/oradata/orcl/FLOW_1242310449730067.dbf
input datafile file number=00012 name=/home/oracle/app/oracle/oradata/orcl/FLOW_1266412439758696.dbf
input datafile file number=00013 name=/home/oracle/app/oracle/oradata/orcl/APEX_1295922881855015.dbf
channel ORA_DISK_2: starting piece 1 at 20-SEP-11
channel ORA_DISK_2: finished piece 1 at 20-SEP-11
piece handle=/addtl/oracle/tmp/52mn2bl3_1_1 tag=TAG20110920T233703 comment=NONE
channel ORA_DISK_2: backup set complete, elapsed time: 00:00:35
channel ORA_DISK_1: finished piece 1 at 20-SEP-11
piece handle=/addtl/oracle/tmp/51mn2bl2_1_1 tag=TAG20110920T233703 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:02:16

using channel ORA_DISK_1
using channel ORA_DISK_2
backup will be obsolete on date 27-SEP-11
archived logs will not be kept or backed up
channel ORA_DISK_1: starting compressed full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
including current control file in backup set
channel ORA_DISK_1: starting piece 1 at 20-SEP-11
channel ORA_DISK_1: finished piece 1 at 20-SEP-11
piece handle=/addtl/oracle/tmp/53mn2bpb_1_1 tag=TAG20110920T233703 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 20-SEP-11

RMAN>


RMAN "automatically" determines an appropriate SCN.

Is this database backup usable for any other purpose ?
Is it "visible" ?

[oracle@localhost ~]$ rman target /

Recovery Manager: Release 11.2.0.1.0 - Production on Tue Sep 20 23:41:06 2011

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

connected to target database: ORCL (DBID=1229390655)

RMAN> list backup summary;

using target database control file instead of recovery catalog

List of Backups
===============
Key TY LV S Device Type Completion Time #Pieces #Copies Compressed Tag
------- -- -- - ----------- --------------- ------- ------- ---------- ---
130 B F A DISK 31-MAY-11 1 1 YES TAG20110531T224447
132 B A A DISK 31-MAY-11 1 1 YES TAG20110531T224945
133 B F A DISK 31-MAY-11 1 1 NO TAG20110531T224947
134 B F A DISK 05-JUL-11 1 1 YES TAG20110705T230230
135 B F A DISK 05-JUL-11 1 1 YES TAG20110705T230230
136 B F A DISK 05-JUL-11 1 1 NO TAG20110705T230640
137 B F A DISK 13-JUL-11 1 1 NO TAG20110713T224918
138 B F A DISK 03-SEP-11 1 1 NO TAG20110903T232648
139 B F A DISK 03-SEP-11 1 1 YES TAG20110903T234602
140 B F A DISK 03-SEP-11 1 1 YES TAG20110903T234602
141 B F A DISK 03-SEP-11 1 1 NO TAG20110903T235631
142 B A A DISK 04-SEP-11 1 1 YES TAG20110904T001454
143 B A A DISK 04-SEP-11 1 1 YES TAG20110904T001454
144 B F A DISK 04-SEP-11 1 1 NO TAG20110904T001459
145 B A A DISK 16-SEP-11 1 2 NO TAG20110916T225318
146 B A A DISK 16-SEP-11 1 2 NO TAG20110916T225318
147 B F A DISK 16-SEP-11 1 1 NO TAG20110916T225321
148 B A DISK 20-SEP-11 1 1 YES TAG20110920T233703
149 B A DISK 20-SEP-11 1 1 YES TAG20110920T233703
150 B A DISK 20-SEP-11 1 1 YES TAG20110920T233703

RMAN>

Note the last 3 entries. The Backup Level (column "LV") is not updated. There is no controlfile autobackup !
Note the warning towards the end of the backup run : "archived logs will not be kept or backed up" ! I must explicitly backup the archivelogs that I need for the Standby.
(The BACKUP FROM INCREMENTAL SCN cannot include a "PLUS ARCHIVELOG")


[oracle@localhost ~]$ cd /addtl/oracle/tmp
[oracle@localhost tmp]$ ls -ltr
total 2596
-rw-rw---- 1 oracle oracle 106496 Sep 20 23:37 52mn2bl3_1_1
-rw-rw---- 1 oracle oracle 1343488 Sep 20 23:39 51mn2bl2_1_1
-rw-rw---- 1 oracle oracle 1196032 Sep 20 23:39 53mn2bpb_1_1
[oracle@localhost tmp]$


However, I can list a datafile individually, thus "seeing" the backup done today :

RMAN> list backup of datafile 1;

using target database control file instead of recovery catalog

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


BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
135 Full 659.84M DISK 00:04:08 05-JUL-11
BP Key: 135 Status: AVAILABLE Compressed: YES Tag: TAG20110705T230230
Piece Name: /addtl/oracle/flash_recovery_area/ORCL/backupset/2011_07_05/o1_mf_nnndf_TAG20110705T230230_7169w8s7_.bkp
List of Datafiles in backup set 135
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
1 Full 8286449 05-JUL-11 /home/oracle/app/oracle/oradata/orcl/system01.dbf

BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
140 Full 2.81G DISK 00:10:28 03-SEP-11
BP Key: 140 Status: AVAILABLE Compressed: YES Tag: TAG20110903T234602
Piece Name: /addtl/oracle/flash_recovery_area/ORCL/backupset/2011_09_03/o1_mf_nnndf_TAG20110903T234602_764lxvd6_.bkp
List of Datafiles in backup set 140
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
1 Full 9008606 03-SEP-11 /home/oracle/app/oracle/oradata/orcl/system01.dbf

BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
149 Incr 1.27M DISK 00:02:13 20-SEP-11
BP Key: 151 Status: AVAILABLE Compressed: YES Tag: TAG20110920T233703
Piece Name: /addtl/oracle/tmp/51mn2bl2_1_1
Keep: NOLOGS Until: 27-SEP-11
List of Datafiles in backup set 149
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
1 Incr 9250390 20-SEP-11 /home/oracle/app/oracle/oradata/orcl/system01.dbf

RMAN>


There are 3 different SCNs now. The one I specified in the INCREMENTAL FROM SCN. The one that RMAN "automatically" used. And the SCN tht appears as the Checkpoint SCN in the LIST BACKUP.

.
.
.

5 comments:

Ariq said...

Great tips. Thanks Hemant's for your impressive blog

Kapil said...

Hi Hemant,

I read your blog, it is quite informative.

I have a question, at the end of the blog you have written there are now 3 SCN numbers. I could not understand why rman is 'listing' a different SCN at the end? Is it due to the incremental checkpointing that is happening while the backup is running?

Hemant K Chitale said...

Kapil,
RMAN causes a Checkpoint before it actually starts a backup of datafiles.

Hemant

Anonymous said...

really ur blog is informative.. I found all the information that I needed. Appreciate ur work.

Anonymous said...

really ur blog is informative.. I found all the information that I needed. Appreciate ur work.