RMAN Report of Database Schema
To find out which backups are not needed in the recovery
process, i.e. are beyond the specified retention policy, use this
command:
See the following example to understand this command more
clearly. For this, list backup of the database, show the current
retention policy and do two backups of datafile 4:
RMAN> list backup;
using target database control file instead of recovery catalog
RMAN> show retention policy;
RMAN configuration parameters are:
configure retention policy to redundancy 1; # default
RMAN> backup datafile 4;
piece handle=../o1_mf_nnndf_tag20090901t164959_59t2m7n0_.bkp
RMAN> backup datafile 4;
piece handle=../o1_mf_nnndf_tag20090901t165007_59t2mhj3_.bkp
As we have configured retention policy to redundancy 1, one
backup will be useless and should be deleted. To confirm that,
use the report obsolete command and then by using the delete
obsolete command, delete it as follows:
RMAN> report obsolete;
RMAN retention policy will be applied to the command
RMAN retention policy is set to redundancy 1
Report of obsolete backups and copies
Type Key Completion Time Filename/Handle
-------------------- ------ ------------------
--------------------
Backup Set 9 01-SEP-09
Backup Piece 9 01-SEP-09
../O1_mf_nnndf_tag20090901t164959_59t2m7n0_.bkp
RMAN> delete obsolete;
deleted backup piece
backup piece
handle=../O1_mf_nnndf_tag20090901t164959_59t2m7n0_.bkp
Deleted 1 objects
Any backup can be verified against any retention policyas follows:
RMAN> report obsolete redundancy 2;
no obsolete backups found
RMAN> report obsolete recovery window of 1 days;
no obsolete backups found
RMAN>
To get a list of all datafiles of our database, use the
following command:
RMAN> report schema;
using target database control file instead of recovery catalog
Report of database schema
List of Permanent Datafiles
===========================
File Size(MB) Tablespace RB segs Datafile Name
---- -------- -------------------- ------- -----------
1 480 system ***
c:\oracle\product\10.2.0\oradata\test\system01.dbf
2 25 undotbs1 ***
c:\oracle\product\10.2.0\oradata\test\undotbs01.dbf
3 240 sysaux ***
c:\oracle\product\10.2.0\oradata\test\sysaux01.dbf
4 5 users ***
c:\oracle\product\10.2.0\oradata\test\users01.dbf
List of Temporary Files
=======================
File Size(MB) Tablespace Maxsize(MB) Tempfile
Name
--- -------- -------------------- ----------- --------
1 20 TEMP 32767
C:\oracle\product\10.2.0\oradata\test\temp01.dbf
To get information about datafiles of a database according to a
specific point in time, use the following:
report schema at time sysdate-1';
report schema at scn=554962;
report schema at sequence 3 thread 1;
In order to use this feature of the report schema command, we should connect
to RMAN using Recovery Catalog, or an error like this will appear:
C:\>rman target /
RMAN> report schema at time 'sysdate-1';
using target database control file instead of recovery catalog
RMAN-00571:
================================================
RMAN-00569:=============== ERROR MESSAGE STACK FOLLOWS
RMAN-00571:
================================================
RMAN-03002: failure of report command at 09/01/2009 17:04:07
RMAN-06137: must have recovery catalog for REPORT SCHEMA AT TIME
RMAN>
Connect to RMAN using Recovery Catalog, and issue the same
command. This shows that yesterday we did not have the tbs_rcat
datafile, but today we have created it as shown here:
C:\>rman target / catalog rcat/rcat
connected to target database: test (DBID=1994589404)
connected to recovery catalog database
RMAN> report schema;
Report of database schema
List of Permanent Datafiles
===========================
File Size(MB) Tablespace RB segs Datafile Name
---- -------- -------------------- ------- -----------
1 490 system yes
c:\oracle\product\10.2.0\oradata\test\system01.dbf
2 25 undotbs1 yes
c:\oracle\product\10.2.0\oradata\test\undotbs01.dbf
3 240 sysaux no
c:\oracle\product\10.2.0\oradata\test\sysaux01.dbf
4 5 users no
c:\oracle\product\10.2.0\oradata\test\users01.dbf
5 200 tbs_rcat no
c:\tbs_rcat.dbf
List of Temporary Files
=======================
File Size(MB) Tablespace Maxsize(MB) Tempfile Name
---- -------- -------------------- ----------- -------
1 20 temp 32767
c:\oracle\product\10.2.0\oradata\test\temp01.dbf
RMAN> report
schema at time 'sysdate-1';
Report of database schema
List of Permanent Datafiles
===========================
File Size(MB) Tablespace RB segs Datafile Name
---- -------- -------------------- ------- -----------1 490
system yes
c:\oracle\product\10.2.0\oradata\test\system01.dbf
2 25 undotbs1 yes
c:\oracle\product\10.2.0\oradata\test\undotbs01.dbf
3 240 sysaux yes
c:\oracle\product\10.2.0\oradata\test\sysaux01.dbf
4 5 users yes
c:\oracle\product\10.2.0\oradata\test\users01.dbf
We can use the report command
in both disk and tape. To switch between them, add the device
type keyword to the end of each report command and specify to
which device being connected to as follows:
report schema
device type disk;
Summary
This chapter has shown almost all the ways to back up the
physical structure of the database using RMAN. Each physical file
of the database can be backed up while it is open, compressed and
encrypted using different algorithms to save more space and the
backup can be protected using Oracle Wallet or any password.
We have seen incremental backup types and techniques where RMAN
backs up only the changed data blocks and not the whole
database. By using incremental backups, we can save space and
time by not backing up those unchanged data blocks. Additionally,
we have learned how to obtain information about backups we have
performed. The two commands which are used for this purpose are
the list and report commands. Both these commands were explained
in detail with different examples in this chapter.
