Search My Oracle Blog

Custom Search

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.

Aggregated by orafaq.com

Aggregated by orafaq.com
This blog is being aggregated by orafaq.com

Top 50 Oracle SQL Blogs 2016

Top 50 Oracle SQL Blogs 2016
Top 50 Oracle SQL Blogs 2016