Relational Model to Dimensional Model

In this post I will demonstrate how a normalized relational data model which is in 3rd Normal Form (3NF) evolves to a dimensional data model (star schema) in a data warehouse.

You can use this post to practice your dimensional modeling and find tips and clues on how to transform the solid Relational Data model of your operational OLTP environment into a dimensional data model suitable for your data warehouse.

There is lots of literature on benefits and uses of dimensional modeling and design in data warehouses. Primary author in this area is Ralph Kimball and his book called The Data Warehouse Toolkit. After I read this book I have been tempted to investigate his approaches hands-on and describe them in a post.

Why do we have to use Dimensional Modeling in the Data Warehouse?

According to Ralph Kimball the necessity for Dimensional Modeling in the warehouse is the complexity and the performance problems of Relational Models. He believes that relational models are complex and hard to understand when presented to business people and the granularity of information in these models is too much. Business people are after slicing and dicing the data and expect quick response times to their ad hoc queries. But business people, in order to be able to pose the query, first they must understand the data. Kimball says that dimensional modeling is able to do that and make business managers better understand data in their organisations.

His second argument is on performance. The relational models are too detailed he says, they contain to many tables related to each other, which make them difficult to join. He argues that the relational models describe in one big picture, business processes which usually do not happen at the same time (ie, sales and delivery). Data normalization and the goal to avoid redundancy and inconsistency in OLTP systems is what drives relational models. But, in the data warehouse the drive is different. Data warehouses are all about providing historical information for general use in reporting and decision making. It is not about capturing and ensuring that the information is consistent. He does not by any means suggest that Data Warehouse data is not consistent. He only makes the point that their driving idea is different. Operational OLTP systems have little data, maybe the last week of operations, whereas Data Warehouses have data for the last 5-10 years. The large amount of data is another factor effecting performance in data warehouses.


All this is fine. Now lets look on how Kimball suggests that we change our Relational Model to a Dimensional model.

When attacking a corporate data model to transform it to a dimensional data model to be used in the data warehouse, the designer must know very well the definitions of the fact table this table is also called cube or multidimensional hypercube and the dimension table. Because these are the only 2 different kinds of tables you have in a dimensional model. Loosely speaking you have to distinguish and blend these 2 kinds of tables from your relational model and at the end you will have a dimensional model!

Definitions of the Fact Table and Dimension Table from Ralph Kimball's book are below.

Fact Table: A fact table is the primary table in a dimensional model where the numeric performance measurements of the business are stored.

Dimensions Table: Dimension tables are integral companions to a fact table. The dimension tables contain the textual descriptors of the business.

Equipped with the above knowledge let's look at our sample relational model of a business:




In redesigning this relational model to a dimensional model, first thing you will have to do is to find the fact table. When looking for the fact table, as Kimball says, you will have to look for the numeric measurements tables, or the tables which count things, the tables which record transactions and the tables which are constantly changing and are very big with lots of rows. Another way to look for them is to find out the many-to-many relationship tables or intersection tables of the relational model. Those tables are the best candidates for the fact table. In our sample model above, best candidates are the ORDERS and ORDER_ITEMS tables as these are the tables which record transactions, constantly changing information and are the largest tables in the model. CUSTOMERS, SALES_CHANNEL and PRODUCTS tables are not measurement tables or tables with constant activity on them. They are more likely to be dimension tables rather than fact tables.

Nothing stops you from introducing new dimensions in a dimensional model, even dimensions which can not translate or do not exist in your relational models. See the TIME_DIMENSION table below which is a table of dates. If you find it appropriate to have a table just to record dates and date attributes, such as holidays, weeks, months, quarters, you can go ahead and create one. It is a standard practice for such dimensions to exist in data warehouses.

So as you can see later in the dimensional model, what happens when re-designing a relation model as a dimensional model is that the primary keys of the dimension tables become the foreign keys in the fact tables. That is, the fact table which is the measurement table is full of foreign keys coming from the dimension tables. Initially if you visualize such a table you might realize that the fact table is not in 3NF, as redundant data will exist in such a table and a non-prime attribute from such a table can depend functionally to another attribute and not directly to the primary key. In many cases the fact tables might not even have a primary key but might have composite primary keys. So redundancy is permitted and normalization laws of data are relaxed in fact tables.

All this for the sake of performance and the ability to analyze data and for a better representation of data to business people, as Kimball points out, Data Warehouses are to serve business people, departmental chiefs and CEOs and Data Warehouse Administration is somewhere between a DBA and an MBA.

In this case our relational model above could become a dimensional model, star schema, like the one below.



Here as you see the ORDERS and ORDER_ITEMS tables, the tables which record day to day measurements for the business are integrated into the SALES_FACTS table, which is the fact table of this dimensional model.

Now the next step after this design would be to find a way to do ETL (Extract, Transform, Load), that is, to extract the information from the OLTP system (relational model) periodically and to insert it in the data warehouse (dimensional model). Then your dimensional model would be ready to be used in your OLAP environment analytic workspace for reporting applications or with your Business Intelligence software for any kind of analytics and performance magic these systems can offer in combination with your dimensional model.

Resources: The Data Warehouse Toolkit - Ralph Kimball, Margy Ross

Data Warehouse, Data Mart, Data Mining and OLAP Definitions

Have you ever heard industry jargon like Data Warehouse, Data Mart, Data Mining, OLAP and more. Did you ever wanted to know in one sentence what are these words all about? Then keep on reading I have put together some quick definitions from Oracle docs.

Data Warehouse: (An Oracle Docs Definition)

A data warehouse is a relational database that is designed for query and analysis rather than for transaction processing. It usually contains historical data derived from transaction data, but can include data from other sources. Data warehouses separate analysis workload from transaction workload and enable an organization to consolidate data from several sources.


Data Mart: (An Oracle Docs Definition)

A data warehouse that is designed for a particular line of business, such as sales, marketing, or finance. In a dependent data mart, the data can be derived from an enterprise-wide data warehouse. In an independent data mart, data can be collected directly from sources. Is the categorization of your datawarehouse data to a particular line of your business. For example BIG DATAWAREHOUSE DATA becomes PURCHASING DATA and SALES DATA and INVENTORY DATA. So a Data Mart is, loosely speaking, a piece of the big thing.


Data Mining: (An Oracle Docs Definition)

Too much data and not enough information — this is a problem facing many businesses and industries. Most businesses have an enormous amount of data, with a great deal of information hiding within it, but "hiding" is usually exactly what it is doing: So much data exists that it overwhelms traditional methods of data analysis.

Data mining provides a way to get at the information buried in the data. Data mining creates models to find hidden patterns in large, complex collections of data, patterns that sometimes elude traditional statistical approaches to analysis because of the large number of attributes, the complexity of patterns, or the difficulty in performing the analysis.


OLAP: (An Oracle Docs Definition)

OLAP functionality is characterized by dynamic, multidimensional analysis of historical data, which supports activities such as the following:

• Calculating across dimensions and through hierarchies

• Analyzing trends

• Drilling up and down through hierarchies

• Rotating to change the dimensional orientation

OLAP tools can run against a multidimensional database or interact directly with a relational database.

Sources:

Oracle® Database Data Warehousing Guide 10g Release 2 (10.2)
http://download.oracle.com/docs/cd/B19306_01/server.102/b14223/concept.htm#sthref47

Oracle® Data Mining Concepts 10g Release 2 (10.2)
http://download.oracle.com/docs/cd/B19306_01/datamine.102/b14339/1intro.htm#i1023970

RMAN Hot Backup Script

The following unix shell script will do a full RMAN hot backup to your database and will copy the backup files compressed to the directory you will specify. This is a hot-backup, and the database must to be in ARCHIVELOG mode for this to work.

A backup retention policy with a recovery window of 2 days is defined in this script. With this retention policy RMAN will keep archive logs and backup files necessary to recover to any point of time within those 2 days in the recovery window. Older backups and archivelogs not needed to satisfy this retention policy will be automatically deleted by this script. No RECOVERY CATALOG is being used with this script, instead database controlfiles are used to record the RMAN repository information.

Make sure you set the rman CONFIGURE CONTROLFILE AUTOBACKUP parameter to ON in in RMAN in order to take extra backups of the controlfile and spfile (RMAN will not backup init.ora files) as extra protection.

This script will delete obsolete backups and not needed archive logs from the disks only after a successful backup. This means you don’t need to set up cronjobs or manually delete not needed backups. This is the beauty of using RMAN. It does this automatically with the commands: “…DELETE NOPROMPT OBSOLETE;…” and “…DELETE NOPROMPT EXPIRED BACKUP…” See last lines in the script.

The RMAN hot backup script rman_backup.sh

# Declare your ORACLE environment variables
export ORACLE_SID= (put your SID here)
export ORACLE_BASE= (put your ORACLE BASE here)
export ORACLE_HOME= (put your ORACLE_HOME here)
export PATH=$PATH:${ORACLE_HOME}/bin

# Start the rman commands
rman target=/ << EOF
CONFIGURE CONTROLFILE AUTOBACKUP ON;
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO ‘/(put backup directory here)/autobackup_control_file%F’;
CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 2 DAYS;
run {
ALLOCATE CHANNEL RMAN_BACK_CH01 TYPE DISK;
CROSSCHECK BACKUP;
BACKUP AS COMPRESSED BACKUPSET DATABASE FORMAT ‘/(put backup directory here)/databasefiles_%d_%u_%s_%T’;
sql ‘ALTER SYSTEM ARCHIVE LOG CURRENT’;
BACKUP AS COMPRESSED BACKUPSET ARCHIVELOG ALL FORMAT ‘/(put backup directory here)/archivelogs_%d_%u_%s_%T’ DELETE INPUT;
BACKUP AS COMPRESSED BACKUPSET CURRENT CONTROLFILE FORMAT ‘/(put backup directory here)/controlfile_%d_%u_%s_%T’;
CROSSCHECK BACKUP;
DELETE NOPROMPT OBSOLETE;
DELETE NOPROMPT EXPIRED BACKUP;
RELEASE CHANNEL RMAN_BACK_CH01;
}
EXIT;
EOF
#

Note: If you run this script on a database which was being backed up using OS methods and user managed backups, initially it will fail as it will look to satisfy the ‘… ARCHIVELOG ALL…” clause. That is it will try to backup archivelogs since day 1. Well, unless you have lots of money to allocate for storage, we know that is not practical to keep all archivelogs and with OS backups usually we delete them manually according to the chosen backup retention pollicy. The script once run initially will fail like this:

Starting backup at 24-MAR-08
current log archived
using channel ORA_DISK_1
archived log /u00/arch/1_2_650282994.arc not found or out of sync with catalog
trying alternate file for archivelog thread 1, sequence 2
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of backup command at 03/24/2008 10:43:29
RMAN-06059: expected archived log not found, lost of archived log compromises recoverability
ORA-19625: error identifying file /u01/arch/1_2_650282994.arc
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3

As you can see the script assumes that you have all archivelogs, if that is not the case (most likely), and you have been periodically deleting them ie. crontab, or manually, you can always sync the existing RMAN catalog (or controlfile) by crosschecking what is there on the disk before you run it with the following command from RMAN:

For before Oracle 9i

RMAN> change archivelog all crosscheck;

or in Oracle 9i,10g

RMAN> crosscheck archivelog all;

This will crosscheck existing archivelogs and will show you the ones which are EXPIRED and need to be deleted from the RMAN catalog, that is, they are not on the disks anymore, cause you have deleted them not using RMAN. RMAN doesn’t know this, it expects them! To delete these misleading entries from your control file you will have to run the command:

RMAN> delete expired archivelog all;

After you delete the expired archivelog records from the control file you can run the script and take your backups.

To see how a recovery is done with a backup taken with this script on a New Host with the same directory structure search this blog for RMAN Recovery.

Backing up, Restoring and Recovering Read Only tablespaces with RMAN

This post is all about making historical data read only in the data warehouse. By making historical data read-only the aim is to reduce storage needs and backup times. RMAN can skip read-only tablespaces once backed up and will not repeatedly back them up.

1. Part One: Moving a large range partitioned table to a read only tablespace
2. Part Two: Backing up, Restoring and Recovering Read Only tablespaces with RMAN.



1. Part One: Moving a large range partitioned table to a read only tablespace

This post is about making historical data read only in a data warehouse. My goal is to convert some of the historical data in this data warehouse to read only and stop backing it up. This way I am planning to save space and improve daily backup times. I have a tablespace called EXAMPLE which has got a large partitioned table called PT and I would like to move the last 12 years data out of this table into a read only tablespace and stop backing up this old historical portion of the table.

Here is how the table data is:

select tablespace_name, table_name, partition_name, blocks, num_rows from user_tab_partitions
where table_name='PT'
order by partition_position

TABLE_NAME                     PARTITION_NAME                     BLOCKS   NUM_ROWS
------------------------------ ------------------------------ ---------- ----------
PT                             PART_95                               748     100539
PT                             PART_96                               748     100540
PT                             PART_97                               748     100539
PT                             PART_98                               748     100539
PT                             PART_99                               748     100539
PT                             PART_00                               748     100539
PT                             PART_01                               748     100539
PT                             PART_02                               748     100539
PT                             PART_03                               748     100539
PT                             PART_04                               748     100539
PT                             PART_05                               748     100539
PT                             PART_06                               748     100539
PT                             PART_07                               748     100539
PT                             PART_MAX                                0          0

14 rows selected.

This are the segments and the total bytes in the EXAMPLE Tablespace which belong to table PT:

SEGMENT_NAME                        MB
----------------------------------- --------------------
IDX_PT_X_UNIQUE                     26 MB
PT                                  78.0625 MB

I want to move these segments, except for the current partition 2007, out of the EXAMPLE tablespace in a READ ONLY EXAMPLE_RO tablespace that I will create.I then used the following SQL to get the commands to move the partitions out of EXAMPLE to EXAMPLE_RO tablespace.

SELECT 'ALTER TABLE ' || TABLE_NAME || ' MOVE PARTITION ' || partition_name || ' TABLESPACE EXAMPLE_RO COMPRESS; '
FROM user_tab_partitions
WHERE TABLE_NAME = 'PT'
AND partition_name NOT IN('PART_MAX',   'PART_07')
ORDER BY partition_position DESC;



'ALTERTABLE'||TABLE_NAME||'MOVEPARTITION'||PARTITION_NAME||'TABLESPACEEXAMPLE_ROCOMPRESS;'                          
-------------------------------------------------------------------------------------------------------------------------
ALTER TABLE PT MOVE PARTITION PART_06 TABLESPACE EXAMPLE_RO COMPRESS;                                               
ALTER TABLE PT MOVE PARTITION PART_05 TABLESPACE EXAMPLE_RO COMPRESS;                                               
ALTER TABLE PT MOVE PARTITION PART_04 TABLESPACE EXAMPLE_RO COMPRESS;                                               
ALTER TABLE PT MOVE PARTITION PART_03 TABLESPACE EXAMPLE_RO COMPRESS;                                               
ALTER TABLE PT MOVE PARTITION PART_02 TABLESPACE EXAMPLE_RO COMPRESS;                                               
ALTER TABLE PT MOVE PARTITION PART_01 TABLESPACE EXAMPLE_RO COMPRESS;                                               
ALTER TABLE PT MOVE PARTITION PART_00 TABLESPACE EXAMPLE_RO COMPRESS;                                               
ALTER TABLE PT MOVE PARTITION PART_99 TABLESPACE EXAMPLE_RO COMPRESS;                                               
ALTER TABLE PT MOVE PARTITION PART_98 TABLESPACE EXAMPLE_RO COMPRESS;                                               
ALTER TABLE PT MOVE PARTITION PART_97 TABLESPACE EXAMPLE_RO COMPRESS;                                               
ALTER TABLE PT MOVE PARTITION PART_96 TABLESPACE EXAMPLE_RO COMPRESS;                                               
ALTER TABLE PT MOVE PARTITION PART_95 TABLESPACE EXAMPLE_RO COMPRESS;                                               

First I have to create the read-only tablespace as normal

SQL> create tablespace example_ro datafile '/usr/local/oracle/test01/example_ro.dbf' size 100M reuse autoextend on;

Tablespace created.

Then move the data in:
(Note that I also compress this data for maximum saving on space)

ALTER TABLE PT MOVE PARTITION PART_06 TABLESPACE EXAMPLE_RO COMPRESS;                                               
ALTER TABLE PT MOVE PARTITION PART_05 TABLESPACE EXAMPLE_RO COMPRESS;                                               
ALTER TABLE PT MOVE PARTITION PART_04 TABLESPACE EXAMPLE_RO COMPRESS;                                               
ALTER TABLE PT MOVE PARTITION PART_03 TABLESPACE EXAMPLE_RO COMPRESS;                                               
ALTER TABLE PT MOVE PARTITION PART_02 TABLESPACE EXAMPLE_RO COMPRESS;                                               
ALTER TABLE PT MOVE PARTITION PART_01 TABLESPACE EXAMPLE_RO COMPRESS;                                               
ALTER TABLE PT MOVE PARTITION PART_00 TABLESPACE EXAMPLE_RO COMPRESS;                                               
ALTER TABLE PT MOVE PARTITION PART_99 TABLESPACE EXAMPLE_RO COMPRESS;                                               
ALTER TABLE PT MOVE PARTITION PART_98 TABLESPACE EXAMPLE_RO COMPRESS;                                               
ALTER TABLE PT MOVE PARTITION PART_97 TABLESPACE EXAMPLE_RO COMPRESS;                                               
ALTER TABLE PT MOVE PARTITION PART_96 TABLESPACE EXAMPLE_RO COMPRESS;                                               
ALTER TABLE PT MOVE PARTITION PART_95 TABLESPACE EXAMPLE_RO COMPRESS;

Looking at the partitions and their tablespaces, it seems like I have got what I wanted.

TABLESPACE_NAME                TABLE_NAME PARTITION_     BLOCKS   NUM_ROWS
------------------------------ ---------- ---------- ---------- ----------
EXAMPLE_RO                     PT         PART_95           748     100539
EXAMPLE_RO                     PT         PART_96           748     100540
EXAMPLE_RO                     PT         PART_97           748     100539
EXAMPLE_RO                     PT         PART_98           748     100539
EXAMPLE_RO                     PT         PART_99           748     100539
EXAMPLE_RO                     PT         PART_00           748     100539
EXAMPLE_RO                     PT         PART_01           748     100539
EXAMPLE_RO                     PT         PART_02           748     100539
EXAMPLE_RO                     PT         PART_03           748     100539
EXAMPLE_RO                     PT         PART_04           748     100539
EXAMPLE_RO                     PT         PART_05           748     100539

TABLESPACE_NAME                TABLE_NAME PARTITION_     BLOCKS   NUM_ROWS
------------------------------ ---------- ---------- ---------- ----------
EXAMPLE_RO                     PT         PART_06           748     100539
EXAMPLE                        PT         PART_07           748     100539
EXAMPLE                        PT         PART_MAX            0          0

Next lets turn the EXAMPLE_RO tablespace read only.

SQL> alter tablespace example_ro read only;

Tablespace altered.

Good, now let's test our table PT see if it is accepting row insertions in partitions PART_2007 and PART_MAX and not in any other partition before that as is read only.

SQL> insert into pt values (111111111111, 2007, 'kubilay', 'kubilay', 'kubilay')

*
ERROR at line 1:
ORA-01502: index 'OE.IDX_PT_X_UNIQUE' or partition of such index is in unusable
state

Oooops, we broke the index with the move partition command as the index is left behind, still in the EXAMPLE tablespace. See below

SQL> select index_name, tablespace_name from user_indexes where index_name='IDX_PT_X_UNIQUE';

INDEX_NAME                     TABLESPACE_NAME
------------------------------ ------------------------------
IDX_PT_X_UNIQUE                EXAMPLE

Let's rebuild this index to repoint it to the moved partitions.

SQL> alter index IDX_PT_X_UNIQUE rebuild;

Index altered.

And now we can do our test again

SQL> insert into pt values (111111111111, 2007, 'kubilay', 'kubilay', 'kubilay');

1 row created.

SQL> commit;

Commit complete.


SQL> insert into pt values (1111111111110, 2005,  'kubilay', 'kubilay', 'kubilay')
      *
ERROR at line 1:
ORA-00372: file 6 cannot be modified at this time
ORA-01110: data file 6: '/usr/local/oracle/test01/example_ro.dbf'

Success! What we have is the current portions of this partitioned table PT in a READ WRITE tablespace, and this will be backed up as default by RMAN and the rest of the historical data, which will never change again, in a READ ONLY tablespace EXAMPLE_RO which will only be backed up once at the beginning and never again.


2. Part Two: Backing up, Restoring and Recovering Read Only tablespaces with RMAN

If you ever use an RMAN script (no catalog) with the option SKIP READONLY in the BACKUP.. command you have to make sure you have to have backed up at least once your read only datafiles (tablespace). Otherwise you are doing nothing and in case you loose everything and want to restore the database to a New Host you will not have the read only datafile! You will get an error like this:

Starting recover at 24-FEB-08
using channel ORA_DISK_1
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 02/24/2008 14:16:52
RMAN-06094: datafile 6 must be restored

Realising this mistake, you decide to copy the read only file from somewhere (tape, an OS backup) into the location on the New Host. But you will find out bitterly that this still doesn't work and you will get an error like:

RMAN> recover database until sequence=48;

Starting recover at 24-FEB-08
using channel ORA_DISK_1

starting media recovery
media recovery failed
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 02/24/2008 14:23:55
ORA-00283: recovery session canceled due to errors
RMAN-11003: failure during parse/execution of SQL statement: alter database recover if needed
start until cancel using backup controlfile
ORA-00283: recovery session canceled due to errors
ORA-19909: datafile 6 belongs to an orphan incarnation
ORA-01110: data file 6: '/usr/local/oracle/test01/example_ro.dbf'

This happens because RMAN wants the Read Only file that backed up itself! Where is it? It asks. Well this is bad, and you are in a bad situation now.

What you should have don is to take a backup of this read-only tablespace just after you have made it read-only, by modifying your backup script slightly only for once, then use the modified RMAN command below in your backup script. Or just don't SKIP READONLY when you take the backup after you make the tablespace read-only. Make sure it backs up!

RMAN> backup database force;

Once you have taken the backup with the option above RMAN WILL KEEP the backup of this read-only tablespace and will NEVER delete it, no matter what retention policy you have, unless obviously you delete it with OS commands yourself. RMAN Will always keep the backupset containing the read only tablespace.

At the full database restore point you will have to get RMAN to check for read-only files, as it doesn't restore them!? I was puzzled with this myself, yes but that is the case. A simple full restore database will not work! So you have to restore like below, by indicating RMAN to check to see if any read-only tablespaces exist and need to be restored.

RMAN> restore database check readonly;

Starting restore at 24-FEB-08
using channel ORA_DISK_1

channel ORA_DISK_1: starting datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
restoring datafile 00006 to /usr/local/oracle/test01/example_ro.dbf
channel ORA_DISK_1: reading from backup piece /u00/test01/databasefiles_TEST01_1qj9rmti_58_20080222
channel ORA_DISK_1: restored backup piece 1
...

At this point RMAN will find the read-only tablespace/datafiles in the backupset, and will restore them before proceeding with restoring the other files. If you do not use the check readonly option during a full database restore RMAN will not restore the file and you will keep on getting the error.

RMAN monitoring commands

Some Quick RMAN monitoring commands I find useful and their explanations



1. LIST commands


To show the incarnation of the database after the OPEN RESETLOGS operation for a database. As the Oracle Docs say "When performing incremental backups, RMAN can use a backup from a previous incarnation or the current incarnation as a basis for subsequent incremental backups. When performing restore and recovery, RMAN can use backups from a previous incarnation in restore and recovery operations just as it would use backups from the current incarnation, as long as all archived logs are available."

RMAN> list incarnation;


Backup commands to see summaries of available backups.

RMAN> list backup of database summary;
RMAN> list expired backup of archivelog all summary;
RMAN> list backup of tablespace users summary;

To see backups eligible for recovery which have status AVAILABLE use:

RMAN> list recoverable backup of database;

To see expired backups which will probably be deleted in the next backup run - if you have set up, delete expired, in your scripts.

RMAN> list expired backup;


To see each backup by database file

RMAN> list backup by file;


To see a particular tablespace backup

RMAN> list backup of tablespace users;


To see a particular datafile backup

RMAN> list backup of datafile 4;


To see all archivelogs currently on disk even the ones that have not been backed up by RMAN use:

RMAN> list archivelog all; -- does not mean that they have been backed up;


Lists the archivelogs available for recovery by RMAN

RMAN> list backup of archivelog all;


To see the controlfiles backed up and available for recovery

RMAN> list backup of controlfile;

To see the SPFILE backup, which is done automatically with the controlfile if you have set CONTROLFILE AUTOBACKUP ON.

RMAN> list backup of spfile;



2. REPORT commands

To see what datafiles/tablespaces haven't been backed up in the last 3 days use:

RMAN> report need backup days=3;


To see for which datafiles/tablespaces you haven't got at least 2 copies of backups (Retention policy to REDUNDANCY 2).

RMAN> report need backup redundancy 2;


To see objects which have been created with options like UNRECOVERABLE (for reasons of speed) and have not been backed up yet use:

RMAN> report unrecoverable;


This is the best ever RMAN command, shows you your database. If you have read these so far and you haven't used this command you will like it.

RMAN> report schema;