TIP 101#: GoldenGate and Archivelog cleanup

In earlier version of GoldenGate, there was always a concern on not to cleanup archivelogs which are required by GoldenGate (when it runs or in case it is bounced).
The way that I do archivelog cleanup in GoldenGate environment was to have a script to extract the sequence which recovery checkpoint of all extracts is and make sure not to cleanup any archivelog after the minimum of sequences.
However, with the new version of GoldenGate, Oracle introduced integration of RMAN with GoldenGate which makes RMAN aware of GoldenGate and the required archivelogs.
This feautre works with some configurations in place for Classic capture and it should automatically works for Integrated Capture (Oracle newly introduced GoldenGate capture).
I ran a test on integrated capture in 11.2.0.1 on Linux 32bit and the following shows that although RMAN is aware of GoldenGate but because of a bug???, no archivelogs which are generated after start up of extract is cleaned up by RMAN. (Concern of archivelog dest full !!!)

Please see the following example :

Exatract checkpoints

GGSCI (localhost.localdomain) 15> info extract ext_int, showch

EXTRACT    EXT_INT   Last Started 2012-09-15 12:40   Status RUNNING
Checkpoint Lag       00:00:00 (updated 00:00:10 ago)
Log Read Checkpoint  Oracle Integrated Redo Logs
2012-09-15 13:07:26
SCN 0.6199997 (6199997)


Current Checkpoint Detail:

Read Checkpoint #1

Oracle Integrated Redo Log

Startup Checkpoint (starting position in the data source):
Timestamp: 2012-09-15 12:40:41.000000
SCN: Not available

  Recovery Checkpoint (position of oldest unprocessed transaction in the data source):
Timestamp: 2012-09-15 13:07:26.000000
SCN: 0.6199997 (6199997)

Current Checkpoint (position of last record read in the data source):
Timestamp: 2012-09-15 13:07:26.000000
SCN: 0.6199997 (6199997)

Write Checkpoint #1

GGS Log Trail

Current Checkpoint (current write position):
Sequence #: 15
RBA: 1065
Timestamp: 2012-09-15 13:07:30.085648
Extract Trail: ./dirdat/tg

CSN state information:
CRC: D5-D0-B1-2C
CSN: Not available

Header:
Version = 2
Record Source = A
Type = 12
# Input Checkpoints = 1
# Output Checkpoints = 1

File Information:
Block Size = 2048
Max Blocks = 100
Record Length = 2048
Current Offset = 0

Configuration:
Data Source = 3
Transaction Integrity = 1
Task Type = 0

Status:
Start Time = 2012-09-15 12:40:57
Last Update Time = 2012-09-15 13:07:30
Stop Status = A
Last Result = 400

Recovery checkpoint is on 6199997 scn which according the following is sequence
664

select 'arch',sequence#,first_time,next_time,RESETLOGS_TIME from v$archived_log where &your_scn between first_change# and next_change#
union all
select 'log-hist',sequence#,first_time,null,RESETLOGS_TIME from v$log_history where &your_scn between first_change# and next_change#
union all
select 'log-current',sequence#,first_time,next_time,null from v$log where &your_scn between first_change# and next_change#

arch 664 09/15/2012 13:05:41 09/15/2012 13:07:34 07/26/2012 08:03:23
log-hist 664 09/15/2012 13:05:41  07/26/2012 08:03:23
log-current 664 09/15/2012 13:05:41 09/15/2012 13:07:34 


Checking archive destinaton 

SQL> archive log list
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            /u02/oracle/app/oracle/product/arch
Oldest online log sequence     663
Next log sequence to archive   665
Current log sequence           665
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production
With the Partitioning, Data Mining and Real Application Testing options
[oracle@localhost ~]$ cd  /u02/oracle/app/oracle/product/arch

[oracle@localhost arch]$ ls -ltr
total 458680
-rw-rw---- 1 oracle oracle 41370624 Sep 12 18:18 1_647_789638603.dbf
-rw-rw---- 1 oracle oracle 44616192 Sep 12 18:27 1_648_789638603.dbf
-rw-rw---- 1 oracle oracle 45473792 Sep 12 18:27 1_649_789638603.dbf
-rw-rw---- 1 oracle oracle 43464192 Sep 12 18:28 1_650_789638603.dbf
-rw-rw---- 1 oracle oracle 45917696 Sep 12 18:28 1_651_789638603.dbf
-rw-rw---- 1 oracle oracle 45963776 Sep 12 18:28 1_652_789638603.dbf
-rw-rw---- 1 oracle oracle 44632576 Sep 12 18:29 1_653_789638603.dbf
-rw-rw---- 1 oracle oracle 41152512 Sep 12 18:29 1_654_789638603.dbf
-rw-rw---- 1 oracle oracle 46688256 Sep 12 18:30 1_655_789638603.dbf
-rw-rw---- 1 oracle oracle 28416512 Sep 15 12:26 1_656_789638603.dbf
-rw-rw---- 1 oracle oracle   103936 Sep 15 12:27 1_657_789638603.dbf
-rw-rw---- 1 oracle oracle 33522176 Sep 15 12:37 1_658_789638603.dbf
-rw-rw---- 1 oracle oracle  1222144 Sep 15 12:49 1_659_789638603.dbf
-rw-rw---- 1 oracle oracle  6498816 Sep 15 13:05 1_660_789638603.dbf
-rw-rw---- 1 oracle oracle     1024 Sep 15 13:05 1_661_789638603.dbf
-rw-rw---- 1 oracle oracle     7680 Sep 15 13:05 1_662_789638603.dbf
-rw-rw---- 1 oracle oracle     2560 Sep 15 13:05 1_663_789638603.dbf
-rw-rw---- 1 oracle oracle    67072 Sep 15 13:07 1_664_789638603.dbf

I expect RMAN cleans up all archivelog but 664
Running RMAN Cleanup

[oracle@localhost arch]$ rman target /
Recovery Manager: Release 11.2.0.3.0 - Production on Sat Sep 15 13:11:29 2012
Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.
connected to target database: ORCL1123 (DBID=3438744075)
RMAN> delete archivelog all;
.
.
.
Recovery Manager complete.
[oracle@localhost arch]$ ls -ltr
total 68316
-rw-rw---- 1 oracle oracle 28416512 Sep 15 12:26 1_656_789638603.dbf
-rw-rw---- 1 oracle oracle   103936 Sep 15 12:27 1_657_789638603.dbf
-rw-rw---- 1 oracle oracle 33522176 Sep 15 12:37 1_658_789638603.dbf
-rw-rw---- 1 oracle oracle  1222144 Sep 15 12:49 1_659_789638603.dbf
-rw-rw---- 1 oracle oracle  6498816 Sep 15 13:05 1_660_789638603.dbf
-rw-rw---- 1 oracle oracle     1024 Sep 15 13:05 1_661_789638603.dbf
-rw-rw---- 1 oracle oracle     7680 Sep 15 13:05 1_662_789638603.dbf
-rw-rw---- 1 oracle oracle     2560 Sep 15 13:05 1_663_789638603.dbf
-rw-rw---- 1 oracle oracle    67072 Sep 15 13:07 1_664_789638603.dbf

For some reasons RMAN keep archives between sequence 656 to 664.
No retention policy for archivelog is defined.


Checking the capture which is started by integrated capture.

select session_restart_scn from v$streams_capture;

6156108

this scn is scn which log miner started from.
sequence 656 based on the following query is mapped to above scn.

select 'arch',sequence#,first_time,next_time,RESETLOGS_TIME from v$archived_log where &your_scn between first_change# and next_change#
union all
select 'log-hist',sequence#,first_time,null,RESETLOGS_TIME from v$log_history where &your_scn between first_change# and next_change#
union all
select 'log-current',sequence#,first_time,next_time,null from v$log where &your_scn between first_change# and next_change#


6156108  ==> sequence 656 

'ARCH' SEQUENCE# FIRST_TIME NEXT_TIME RESETLOGS_TIME
arch 656 09/12/2012 18:30:07 09/15/2012 12:26:54 07/26/2012 08:03:23
log-hist 656 09/12/2012 18:30:07  07/26/2012 08:03:23

RMAN keeps all archivelogs after scn which integrated capture is started from
To confirm, I bounced extract and confirmed that now RMAN moved to the next scn which extract is started from.

stop extract ext_int
start extract ext_int
select session_restart_scn from v$streams_capture;

6200279

Above scn is mapped to sequence 665.
Now if I run RMAN archivelog deletion, then all archivelogs till sequence 665 is removed.

[oracle@localhost arch]$ ls -ltr
total 69468
-rw-rw---- 1 oracle oracle 28416512 Sep 15 12:26 1_656_789638603.dbf
-rw-rw---- 1 oracle oracle   103936 Sep 15 12:27 1_657_789638603.dbf
-rw-rw---- 1 oracle oracle 33522176 Sep 15 12:37 1_658_789638603.dbf
-rw-rw---- 1 oracle oracle  1222144 Sep 15 12:49 1_659_789638603.dbf
-rw-rw---- 1 oracle oracle  6498816 Sep 15 13:05 1_660_789638603.dbf
-rw-rw---- 1 oracle oracle     1024 Sep 15 13:05 1_661_789638603.dbf
-rw-rw---- 1 oracle oracle     7680 Sep 15 13:05 1_662_789638603.dbf
-rw-rw---- 1 oracle oracle     2560 Sep 15 13:05 1_663_789638603.dbf
-rw-rw---- 1 oracle oracle    67072 Sep 15 13:07 1_664_789638603.dbf
-rw-rw---- 1 oracle oracle  1161728 Sep 15 13:23 1_665_789638603.dbf
-rw-rw---- 1 oracle oracle     3584 Sep 15 13:23 1_666_789638603.dbf
-rw-rw---- 1 oracle oracle     6656 Sep 15 13:23 1_667_789638603.dbf

rman target /
delete archivelog all;


[oracle@localhost arch]$ ls -ltr
total 1152  
-rw-rw---- 1 oracle oracle 1161728 Sep 15 13:23 1_665_789638603.dbf
-rw-rw---- 1 oracle oracle    3584 Sep 15 13:23 1_666_789638603.dbf
-rw-rw---- 1 oracle oracle    6656 Sep 15 13:23 1_667_789638603.dbf

If you use integrated capture or classic Goldengate, it is safer to prepare your own script for cleanup archivelog after extracting recovery checkpoint of all extract processes. Currently, RMAN does cleanup archivelog only to the scn which extracts are started, If you want to rely on RMAN cleanup, make sure to plan to bounce extract regularly AND watch archivelog destination closely.

TIP 86# : What is missed when RMAN backuping up to tape ?

When backing up datafiles into backup sets, RMAN does not back up the contents of
data blocks that have never been allocated.
However, RMAN only skips unused blocks (Blocks which do not currently contain data but they had data) if the following conditions are all met :


■ The COMPATIBLE initialization parameter is set to 10.2
■ There are currently no guaranteed restore points defined for the database
■ The datafile is locally managed
■ The datafile is being backed up to a backup set as part of a full backup or a level 0 incremental backup
■ The backup set is being created on disk.


Above means that if RMAN backing up to tape, it will backup all unused blocks (blocks which have been touched before but now are empty). In other words, backup to tape could waste space if there are many unused blocks.

Here is a demonstration to show the difference of backup between when it is sent to tape and when it is sent to disk :

Step 1: Create a new empty tablespace



Step 2 : Backup new tablespace to disk




Step 3 : Backup new tablespace to tape



Step 4 : Create a table in the new tablespace



Step 5 : Backup tablespace to disk when it has table with records




Step 6 : Backup tablespace to tape when it has table with the records




Step 7: Delete all records in table (unused blocks - Blocks with no data but had data before)



Step 8 : Backup tablespace to disk after deletion of all records in table



Step 9 : Backup tablespace to disk after deletion of all records in table



Step 10 : Checking backup size



As it is shown, Disk backup after truncate was reduced to 600K while tape backup did not change (~ 5M same before delete). This proves that unused blocks are only not backed up for disk backup. Maybe another reason to use Flash Recovery area !!!

TIP 79 : Simple and effective backup script.

How many times you have seen different backup script with different commands ? Have you ever asked if all consider Oracle best practices and what is pros and cons of each ?
Here, I am trying to focus on some Oracle best recommendations in terms of backup/recovery and then at the end represent a sample RMAN backup script which takes into account all those recommendations

Recommendations :

- Check logical corruption to make sure backup is good.
- Put full database backup as incremental level 0 so it is considered in incremental backup/recovery scenario.
- Make sure that backup pieces have time-stamp to overcome look-up performance issue when recovery catalog is backup.
- Make sure to have each datafile in a single backup piece so for partial recovery RMAN goes through only one piece.
- Make sure to have unique name for each backup piece so RMAN does not overwrite backup pieces if for any reason backup is taken more often.
- Tag backups properly to make searching them and restoring them easier.
- Keep DBID in controlfile backup piece in order to save time to find it when it is required.
- Take current controlfile backup although autobackup is ON,that way it guarantees that always controlfile backup is there.

Based on the above, the following is the standard template that covers all features.


run
{
allocate channel t1 type disk;
BACKUP AS COMPRESSED BACKUPSET check logical INCREMENTAL LEVEL 0 DATABASE filesperset 1 plus archivelog format '/mnt/u05/backuptest/backup_%d_set%s_piece%p_copy%c_%T_%U' TAG = DB_BACK_FULL_DAILY ;
backup current controlfile format '/mnt/u05/backuptest/backup_controlfile_%d_DBID%I_%T_%U.ctl' TAG=CTL_BACK_FULL_DAILY;
backup spfile format '/mnt/u05/backuptest/backup_parameter_%d_DBID%I_%T_%U.ctl' TAG=PARAM_BACK_FULL_DAILY ;
delete obsolete;
release channel t1;
}

TIP 70# : Performance issue on restoring database with Veritas netbackup

A client asked me to refresh a test database from a production including data and structure. Production database was 800GB and RMAN level 0 backup was taken every day.
In the first step, I tried to restore a 4GB datafile from tape to have better picture on how long the whole exerxise would take. Result was surprising!.
Restore of 4GB datafile took 15 minutes. Simple math showed me that whole restore should take more than 46 hours !!!
This time obviously was not acceptable to the client.
While I was searching more, I found that whole database backup just takes only 7 hours.
As the first guess I was suspicious to Veritas catalog and how it looks up backups.
I finally found metalink note 309891.1 which strongly recommends to put specific backup format to improve RMAN restore using Veritas netbackup.
Oracle recommends to put %t (lower case) at the end of backup format (Only at the end) to ensure uniqueness and optimal performance.
Interestingly, when backup format was changed to 'datafile208__%d_%u_%s_%T_%t'. the whole restore was done in 8 hours.
This workaround is applicable to 8.1.7.4 to 10.2.0.0.

TIP #46 : Using 'Plus archivelog' in backup

I had a client which using Oracle 10gR3 on Windows.Its backup strategy was simple. Daily incremental level 1 backup and weekly level 0 backup.Database was configured to use flash recovery area for archivelogs and backups.
I regularly get flash recovery full problem with retention policy 2 weeks.Suprisingly database is only 1GB.
Bacjup script was as simple as 'backup database ...... plus archivelog ';

Finally, I found that problem was because of plus archivelog.
Regarding to RMAN document, RMAN performs these steps when "plus archivelog" command is in backup script.

1. Runs an ALTER SYSTEM ARCHIVE LOG CURRENT statement.
2. Runs the BACKUP ARCHIVELOG ALL command. Note that if backupoptimization is enabled, then RMAN only backs up logs that have not yetbeen backed up.
3. Backs up the files specified in the BACKUP command.
4. Runs an ALTER SYSTEM ARCHIVE LOG CURRENT statement.
5. Backs up any remaining archived redo logs.

If backup otimization is off (default RMAN), each time all archivelogs are backed up which means that on daily basis backup size grows since archivelogs in flash recovery are not removed as space exists.

In a nutshell, when "plus archivelog" is used in backup script , make sure that optimization is on in RMAN configuration otherwise in each backup whole archive logs are backed up.

TIP #41: Estimate backup size (2)

I posted a blog on March 3rd, 2007 about estimation of RMAN backup size (TIP #25) .One of my collegue suggested me to run test for 10gR2.
I ran the following steps in 10.2.0.2 on AIX5 64bit. It seems that RMAN behavior in 10.2.0.2 is closer to reality however it is not 100% precise to database size .
To sum up I would say that Before 10.2.0.2 , backup size can not be the same size as dba_segments (RMAN only does not backup never touched blocks), while in 10.2.0.2 backup size of full database is roughly the same as database.

Sample test in 10gR2. (Same scenario as 10.1).

=== Create new tablespace
SQL> create tablespace rmantst datafile '/o022/test/rmantst.dbf' size 200M;
Tablespace created.

-- Taking RMAN backup from empty tablespace
run{
backup tablespace rmantst format '/o022/test/backup_after_ts_creation_%s.bak';
}

Starting backup at 15-APR-07
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=533 devtype=DISK
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
input datafile fno=00028 name=/o022/test/rmantst.dbf
channel ORA_DISK_1: starting piece 1 at 15-APR-07
channel ORA_DISK_1: finished piece 1 at 15-APR-07
piece handle=/o022/test/backup_after_ts_creation_4.bak tag=TAG20070415T112434 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 15-APR-07

Starting Control File and SPFILE Autobackup at 15-APR-07
piece handle=/o022/backup/control_back_c-1721358348-20070415-01 comment=NONE
Finished Control File and SPFILE Autobackup at 15-APR-07

-- Check backup size

oracle(/o022/test):ls -l

total 410064
-rw-r----- 1 oracle dba 209723392 Apr 15 11:24 rmantst.dbf
-rw-r----- 1 oracle dba 106496 Apr 15 11:24 backup_after_ts_creation_4.bak (Backup of empty tablespace is only 106KB).

---- Create some objects in rmantst tablespace

create table rmantest1 tablespace rmantst as select * from dba_objects;
create table rmantest2 tablespace rmantst as select * from dba_objects;
create table rmantest3 tablespace rmantst as select * from dba_objects;
create table rmantest4 tablespace rmantst as select * from dba_objects;
create table rmantest5 tablespace rmantst as select * from dba_objects union all select * from dba_objects union all select * from dba_objects union all select * from dba_objects;
create table rmantest6 tablespace rmantst as select * from dba_objects union all select * from dba_objects union all select * from dba_objects union all select * from dba_objects;
create table rmantest7 tablespace rmantst as select * from dba_objects union all select * from dba_objects union all select * from dba_objects union all select * from dba_objects;

SQL> select sum(bytes)/1024/1024 from dba_data_files where tablespace_name='RMANTST';
SUM(BYTES)/1024/1024
--------------------
200

SQL> select sum(bytes)/1024/1024 from dba_segments where tablespace_name='RMANTST';
SUM(BYTES)/1024/1024
--------------------
112 <== 112MB of 200MB was allocated.

--- Take tablespace backup

RMAN> run
2> {
backup tablespace rmantst format '/o022/test/backup_after_tbl_creation_%s.bak';
}
3> 4>
Starting backup at 15-APR-07
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=488 devtype=DISK
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
input datafile fno=00028 name=/o022/test/rmantst.dbf
channel ORA_DISK_1: starting piece 1 at 15-APR-07
channel ORA_DISK_1: finished piece 1 at 15-APR-07
piece handle=/o022/test/backup_after_tbl_creation_6.bak tag=TAG20070415T113143 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:03
Finished backup at 15-APR-07
Starting Control File and SPFILE Autobackup at 15-APR-07
piece handle=/o022/backup/control_back_c-1721358348-20070415-02 comment=NONE
Finished Control File and SPFILE Autobackup at 15-APR-07
RMAN> exit

Recovery Manager complete.
oracle(/o022/test):ls -l
total 634320
-rw-r----- 1 oracle dba 114786304 Apr 15 11:31 backup_after_tbl_creation_6.bak (Backup size is ~ 114M)
-rw-r----- 1 oracle dba 106496 Apr 15 11:24 backup_after_ts_creation_4.bak
-rw-r----- 1 oracle dba 209723392 Apr 15 11:31 rmantst.dbf

-- Remove data from all objects in tablespace.

SQL> truncate table rmantest2;
Table truncated.
SQL> truncate table rmantest1;
Table truncated.
SQL> truncate table rmantest3;
Table truncated.
SQL> truncate table rmantest4;
Table truncated.
SQL> truncate table rmantest5 ;
Table truncated.
SQL> truncate table rmantest6;
Table truncated.
SQL> truncate table rmantest7;
Table truncated.

SQL> select sum(bytes)/1024/1024 from dba_data_files where tablespace_name='RMANTST';
SUM(BYTES)/1024/1024
--------------------
200

SQL> select sum(bytes)/1024/1024 from dba_segments where tablespace_name='RMANTST';
SUM(BYTES)/1024/1024
--------------------
.4375 <== only 400K was allocated.

-- Take RMAN backup

run
{
backup tablespace rmantst format '/o022/test/backup_after_tbl_truncate_%s.bak';
}

oracle(/o022/test):ls -ltr
total 641792
-rw-r----- 1 oracle dba 106496 Apr 15 11:24 backup_after_ts_creation_4.bak
-rw-r----- 1 oracle dba 114786304 Apr 15 11:31 backup_after_tbl_creation_6.bak
-rw-r----- 1 oracle dba 209723392 Apr 15 11:35 rmantst.dbf
-rw-r----- 1 oracle dba 3825664 Apr 15 11:35 backup_after_tbl_truncate_8.bak ( Backup after truncate is only 3MB. It is not exactly the same as dba_segments but it is not as bad as 10.2.0.1).

TIP #29 : RMAN and Resetlogs in 10g.

After each incomplete recovery, database should be opened with resetlogs.
In 9i and below, if database is need to be restored to time prior to resetlogs, different incarnation should be restored.In addition, since new log thread starts with number 1 after resetlogs, all previous backups become obsolete.
In 10g, this is not an issue anymore. RMAN can use any backup before or after resetlogs to recover Oracle database.No need to use different incarnation.

TIP #28 : RMAN 10g and incremental backup.

Why incremental backup ?

The most important reason for doing incremental backups is associated with data warehouse environments, where many operations are done in NOLOGGING mode and data changes do not go to the archived log files.
Considering the massive size of data warehouses today, and the fact that most of the data in them does not change, full backups are neither desirable nor practical. Therefore , doing incremental backups in RMAN is an ideal alternative.

WhyDBAs dis not like incremental backup in 9i ?

Oracle does full scan for finding changes in 9i which generates some performance problem.This is why most DBAs did not like incremental backup in 9i.

What is new with 10g incremental backup ?

Block changes can be tracked in 10g which means that Oracle does not need to do full scan anymore to find out changes.
To enable track changing run the following command.

alter database enable block change tracking using file '/rman_bkups/change.log';

To disable block track change.

alter database disable block change tracking;

TIP #25: Estimate backup size (1)

Before taking a backup, it would be idea to estimate backup size.With Estimation of backup size, we can make sure that there is enough disk space for backup.
Regarding to my investigation, there is no official way to find backup size before taking backup.
However, Oracle comments is : "RMAN does not take backup from NEVER USED blocks" .
Never used blocks are blocks which have not ever been touched.It is different from empty blocks. From the following sample test which I ran, I found that empty blocks were definitely backed up.

Test
=====

== Scenario1 : Create new tablespace and backup (TS size:200M)

SQL> create tablespace rmantst datafile 'C:\ORACLE\ORADATA\ORA10GPR\rmantst.dbf' size 200M;
Tablespace created.
RMAN> connect target /
connected to target database: ORA10GPR (DBID=1394790175)
RMAN> run
2> {
3> backup tablespace rmantst format 'c:\backup_after_ts_creation_%s.bak';
4> }

Starting backup at 28-FEB-07
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=138 devtype=DISK
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
input datafile fno=00008 name=C:\ORACLE\ORADATA\ORA10GPR\RMANTST.DBF
channel ORA_DISK_1: starting piece 1 at 28-FEB-07
channel ORA_DISK_1: finished piece 1 at 28-FEB-07
piece handle=C:\BACKUP_AFTER_TS_CREATION_29.BAK tag=TAG20070228T225029 comment=N
ONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:15
Finished backup at 28-FEB-07
Starting Control File and SPFILE Autobackup at 28-FEB-07
piece handle=C:\ORACLE\FLASH_RECOVERY_AREA\ORA10GPR\AUTOBACKUP\2007_02_28\O1_MF_
S_615768645_2YDMDPG5_.BKP comment=NONE
Finished Control File and SPFILE Autobackup at 28-FEB-07
RMAN>

BACKUP_AFTER_TS_CREATION_29.BAK ==> 96KB.

== Scenario2 : Fill up tablespace with data (Fill 163MB of 200MB tablespace)

SQL> create table rmantest1 tablespace rmantst as select * from dba_objects;
Table created.
SQL> create table rmantest2 tablespace rmantst as select * from SOURCE$;
Table created.
SQL> create table rmantest3 tablespace rmantst as select * from SOURCE$;
Table created.
SQL> create table rmantest4 tablespace rmantst as select * from SOURCE$ nologging;
Table created.
SQL> select sum(bytes)/1024/1024 from dba_data_files where tablespace_name='RMANTST';

SUM(BYTES)/1024/1024
--------------------
200

SQL> select sum(bytes)/1024/1024 from dba_free_space where tablespace_name='RMANTST';
SUM(BYTES)/1024/1024
--------------------
37.9375

SQL> select sum(bytes)/1024/1024 from dba_segments where tablespace_name='RMANTST';
SUM(BYTES)/1024/1024
--------------------
162

BACKUP_AFTER_TABLES_CREATION_IN_TS_31.BAK ==> 163 MB.

== Scenario3 : Delete Objects in tablespace (Remove most objects ... Only 6MB was allocated for 200MB tablespace)

SQL> truncate table rmantest2;
Table truncated.
SQL> truncate table rmantest3;
Table truncated.
SQL> truncate table rmantest4;
Table truncated.

SQL> select sum(bytes)/1024/1024 from dba_data_files where tablespace_name='RMANTST';
SUM(BYTES)/1024/1024
--------------------
200
SQL> select sum(bytes)/1024/1024 from dba_free_space where tablespace_name='RMANTST';
SUM(BYTES)/1024/1024
--------------------
193.75
SQL> select sum(bytes)/1024/1024 from dba_segments where tablespace_name='RMANTST';
SUM(BYTES)/1024/1024
--------------------
6.1875 Only 6.1MB was allocated in RMANTST

RMAN> run
2> {
3> backup tablespace rmantst format 'c:\backup_after_tables_truncate_in_ts_%s.ba
k';
4> }
Starting backup at 28-FEB-07
using channel ORA_DISK_1
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
input datafile fno=00008 name=C:\ORACLE\ORADATA\ORA10GPR\RMANTST.DBF
channel ORA_DISK_1: starting piece 1 at 28-FEB-07
channel ORA_DISK_1: finished piece 1 at 28-FEB-07
piece handle=C:\BACKUP_AFTER_TABLES_TRUNCATE_IN_TS_33.BAK tag=TAG20070228T231456
comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:35
Finished backup at 28-FEB-07
Starting Control File and SPFILE Autobackup at 28-FEB-07
piece handle=C:\ORACLE\FLASH_RECOVERY_AREA\ORA10GPR\AUTOBACKUP\2007_02_28\O1_MF_
S_615770131_2YDNV53F_.BKP comment=NONE
Finished Control File and SPFILE Autobackup at 28-FEB-07

BACKUP_AFTER_TABLES_TRUNCATE_IN_TS_33.BAK ==> 163 M <== This proves that only NEVER used blocks are not being backed up. EMPTY blocks are backed up if they have already been touched. Therefore, it is not true to say dba_segments can always give backup size .

FYI : Scenarios were run in 10.2.0.1. I will test these in 10.2.0.2

TIP #14: Difference between full backup and level 0 incremental backup

I asked many times what the difference between full backup and level 0 incremental backup is.Whether or not they are the same and ...
Incremental level 0 backup which is the base for subsequent incremental backup copies all blocks containing data same as full backup. (Same functionality)
The only difference between these two backups is that a full backup never considered as base backup in incremental strategy.
Therefore, if you select incremental strategy as part of your backup strategy, you need at least one level 0 incremental backup.

TIP #13: Ideal RMAN format for backup

Naming RMAN backup is vital.
Proper backup name gives you better idea of which backup may be used during recovery.
My ideal RMAN backup format is : backup_%d_set%s_piece%p_%T_%U for backupsets when duplexing is not used. For duplexing backup it would be backup_%d_set%s_piece%p_copy%c_%T_%U
The following is brief description of these tags :

• %d : name of database
• %s :backup set number. (Unique for controlfile lifetime)
• %p :piece number in backup set.
• %T : Specified date in format YYYYMMDD
• %U : Unique number consist of %u_%p_%c
• %c : copy number of backup piece.when duplexing of backup piece is in use

Please pay attention that these tags are case sensitive.

If no format is specified, Oracle by default uses %U.For controlfile autobackup, I prefer to use default config which is %F.This tag has DBID in the filename which would be necessary in the case of recovery when RMAN catalog is not accessible.This tag can not be used for backup sets.

Enjoy reading ...

TIP #4 : RMAN Recovery window

I `d like to clarify misunderstanding of RMAN recovery window.
There is incorrect thought :

Setting RMAN recovery window to 14 days ==> any backup older than 14 days become obsolete. Incorrect

Regarding to Oracle document, A recovery window is a period of time that begins with the current time and extends backward in time to the point of recoverability. In other words, Recovery window determines the earliest point of time when recovery is possible.Consequently, RMAN may need backups older than recovery window=n days to recover database to n days ago.

Let me provide you an example :

Assumptions :
- Recovery window 7 days.
- Scheduled DB backups. (Nov 1st,Nov 14th,Nov 21th,Nov 28th)
- current date : Nov 16th.

Nov 1st -- Nov 14th ---Nov 21th-- Nov 28th
^
Nov9th ---- Nov 16th   

In this case for being recoverable between Nov 9th till Nov 16th (which is recovery window) , RMAN requires Nov 1st backup for recovery at any time between Nov 9th and Nov 14th. In addition, RMAN uses Nov 14th backup for recovery after that.
This shows better picture. Having recovery window of 7 days does not result to make Nov 1st backup as obsolete.