Backup Recovery Oracle

Simple intro Backup and Recovery of Oracle Databases 9i, 10g, 11g

Important: This is a quick and simple introduction to Oracle Backup and Recovery, and is by no means a complete description.

This basic intro is for people who know at least some basics of Oracle Databases, but who need an overview on how to backup and restore (and recover) an Oracle database.

This manual is suitable for Oracle 9i, 10g, and 11g. Ofcourse, there are differences between those versions, but with

respect to backup and recovery, the differences are actually not too big. But please be aware of the fact that 11g has some interresting features that are not mentioned in this document.

What is in this file?

This sheet explains some basic features on the subject of "how to create backups".

The second sheet, "Examples_and_Notes", shows some examples of rman backup and recovery scripts.

The third sheet, "Dictionary_views_target_db", will show you which dictionary views (of the Target DB) you can query on backups and recovery. The fourth sheet, "SomeDisasterRecoveryScenarios", will show you some important problems from which you may recover.

1. What to backup? What should you include in a database backup?

1. Physical backup: That is a copy of all relevant database files to tape(s) or backup disk(s). 2. Logical backup: This is a full or partial export of the contents of the database (e.g. tables,

indexes, procedures etc..) to one or more file(s).

So what type of backup should you choose? Actually, most dba's do both types of backups, whereas the "physical backup" is regarded as the most important backup, that is, it‟s the backup you probably will use in case of disaster recovery. The logical backup is then probably scheduled less frequently and can be used as some sort of "last resort", or as way for an easy restore of an object (like a table).

Let's first take a look at what exactly should be backupped frequently. We will use an example of an simple Oracle database, let's call it "PLANNING", which, in this example, contains of a small number of database files. The whole organization of the Oracle Software, and the actual database, could be as in the following example. We can distinguish 3 "areas" so to speak:

I Software (Static stuff)

This is just the Oracle Software. Location of Oracle Software ( ORACLE_HOME ) Those files are "static" and remains This is where the software the same unless you to plan

(binaries, libraries etc..) lives. to do an upgrade, or install a patch. for example in the location:

/opt/ora10g/product/10g/ But, in a default installation, also - big tree of some logs can be found here, for - subdirectories example the "listener.log", but those

logs are not directly critical for the operation of the database.

In our example, the Oracle software was installed in /opt/ora10g/product/10g but evidently, this could also have been

another path, like e.g. /home/oracle or /u01/ora10g II Databasefiles (Will change constantly while the database is open)

/u01/oradata/planning/planning_data01.dbf /u02/oradata/planning/planning_indx01.dbf /u03/oradata/planning/users.dbf /u03/oradata/planning/system.dbf /u03/oradata/planning/sysaux.dbf /u03/oradata/planning/temp.dbf /u03/oradata/planning/undo.dbf

/u04/oradata/planning/redo01.dbf, redo02.dbf, redo03.dbf

/u05/oradata/planning/control01.dbf, control02.dbf, control03.dbf These are the "real" database files. Here all modifications to all tables and indexes (and other objects) are collected.

III Admin/Log directories ("Fairly" static stuff)

Administrative Logfiles and the socalled "parameter file" are usually stored in a directory like for example:

/dbms/oracle/admin

This directory usually contains several subdirs like "dump", "udump", "cdump", "audit" which contains several logfiles, like the main log file "alert.log" where

all important events (and oracle errors) are logged.

It can contain also the directory "pfile" where the parameter file "init.ora" or "spfile.ora" can be located. Such a parameterfile contains all kinds of settings for the Oracle instance (like how much memory it may use, or how many processes can be concurrent at a time).

Evidently, also this path could be somewhere else like /home/oracle/admin or some other filesystem or directory of your choice.

So when we talk about a "backup the database", we talk about creating a copy of the files in section II, that is, the true databasefiles, that is, "where the data lives".

However trivial that former statement may be, its important to know what exactly you need to backup if you want to backup the true database data.

Note: What is a tablespace?

In this document, we sometimes come across the concept of "tablespace".

A database consists of a number of files, like our planning database shown in II.

But oracle also uses the logical concept of "tablespace". A tablespace has no meaning to the OS, because the OS only knows of files.

But in Oracle, you could create a tablespace consisting of 1 or more files.

The purpose of a tablespace is at least twofold: performance gain, and neat administration. 1. Possible performance gain:

Suppose you have a large database with large tables.

It would have been nice if you could store a large table over multiple files, over multiple filesystems. This could benefit performance.

So, suppose I create the tablespace "PLANNING_DATA", and I want to let it have two files:

CREATE TABLESPACE PLANNING_DATA

DATAFILE '/u01/oradata/planning/planning_data01.dbf' SIZE 1000M, DATAFILE '/u02/oradata/planning/planning_data02.dbf' SIZE 1000M, EXTENT MANAGEMENT LOCAL;

That would work. Note that the tablespace consists of two files, over two separate filessystems /u01 and /u02. Now I could create a table, for example EMPLOYEES, and let it "live" in tablespace PLANNING_DATA:

CREATE TABLE EMPLOYEES (

employee_id number, employee__name varchar(50) )

Now I know for sure on which files the table will be on. Additionally, if the table gets very large

I could benefit from "parallel IO" because of the two independent filesystems (assuming those are on separate disks). Now, since in my example database, I only have one physical file "planning_data01.dbf",

I have used the following CREATE TABLESPACE command:

CREATE TABLESPACE PLANNING_DATA

DATAFILE '/u01/oradata/planning/planning_data01.dbf' SIZE 1000M EXTENT MANAGEMENT LOCAL;

2. Neat Administration:

The dba can create multiple tablespaces, maybe not only for performance reasons, but also for Administration purposes. Would it not be great if you, for example, separate your tables and indexes on separate tablespaces?

Also, maybe there are several "schema's" (useraccount) in your database, and you would create a neat administration if you separate those objects in their own tablespaces.

2. Tools and commands you use to backup a database.

We already have seen that Oracle itself, makes a distinction between a physical backup and a logical backup of a database. In a moment, you will see that there is also a difference in a socalled "open" backup, that is, creating a backup

while the database is open, and a socalled "closed" backup, where the database is shutdown.

You will see that the Oracle supplied utility "RMAN" is THE tool to backup a database. However, the following tools and commands can be used to backup an Oracle database:

Tool type of backup

exp (Oracle supplied utility) logical expdb (Oracle supplied utility) logical rman (Oracle supplied utility) physical

OS commands like physical

cp, tar, cpio etc..

third party tools that use physical rman

Now you may have seen on, for example a unix system, that the sysadmin is using Tivoli TSM (or other backup suite) for filesystem backups.

Those types of tools may ALSO have been configured with the add-on product TDPO, so that (open) Oracle databases can be backupped as well.

So it might look to you as if the above table is not complete ! That's not true. Almost all

of those professional backup software suites, that are configured for making Oracle backups as well, uses the Oracle's RMAN utility "under water", so it's not immediately obvious that really rman is creating the Oracle backups.

Now we give some examples in creating a backup of a database. Do NOT take these as "live" or "usable" examples, because we still NEED to excplain the difference between a database in archive mode and in non-archive mode. So, creating backups could be similar as in the following examples: (do not takes these for as "being usable"): If you need to see examples that can be used for real, please see section 4.

I: Physical backup using OS commands like cp or tar:

Suppose the Planning database is shutdown. This means that no process is acting on the databasefiles. They are just a bunch of "cold" files at this time. This is very important, otherwise we COULD NOT use the commands as shown in the examples. As an example we could give the following command sequence:

# cp /u01/oradata/planning/planning_data01.dbf /backup/planning # cp /u02/oradata/planning/planning_indx01.dbf /backup/planning # cp /u03/oradata/planning/users.dbf /backup/planning # cp /u03/oradata/planning/system.dbf /backup/planning # cp /u03/oradata/planning/sysaux.dbf /backup/planning # cp /u03/oradata/planning/temp.dbf /backup/planning

# cp /u03/oradata/planning/undo.dbf /backup/planning # cp /u04/oradata/planning/redo0* /backup/planning # cp /05/oradata/planning/control* /backup/planning

or backup the files to tape with for example tar:

# tar -cvf /A589 /u01/oradata Or something like # tar -cvf /dev/rmt/0hc /u01/oradata # tar -cvf /dev/rmt1.1 /u02/oradata this on your # tar -rvf /dev/rmt/0hc /u02/oradata # tar -cvf /dev/rmt1.1 /u03/oradata unix and tape # tar -rvf /dev/rmt/0hc /u03/oradata # tar -cvf /dev/rmt1.1 /u04/oradata device: # tar -rvf /dev/rmt/0hc /u04/oradata # tar -cvf /dev/rmt1.1 /u05/oradata # tar -rvf /dev/rmt/0hc /u05/oradata

The exact form of the tar commands, depends ofcourse how your tapedrive or class is called..

Now, if the database was indeed down, this was a valid backup. If the database was open, and thus in use, this backup was not valid, because the shared databases files are accessed by potentially many

processes, possibly modifying blocks while the database backup was running. Notes:

1. On some unixes, rmt1.1 is the non-rewinding class of the tapedrive on rmt1, in contrast with "rmt1", which is rewinding Be very sure to know what is your non-rewinding device, or you will only have the last file on tape!

2. The commands above could have been in much smarter format like "cp with using ranges with brackets []

2. Logical backup using "exp" and "expdp" utilities:

Suppose the Planning database is open. Now, that‟s actually a requirement if we want to use the "exp" or "expdp" uitilites. We will create a logical backup, that is, all objects (tables, indexes, procedures and all other fancy stuff)

will be put nicely in one (or more) files. This file can be used if we want to restore the whole database, or only some objects (like some tables or so).

Here are some examples:

This is a full database backup:

# exp system/manager@planning file=planning_06062008.dmp full=y log=planning_06062008.log

This is a backup of just one table, namely the table "JOHN.SALES":

# exp system/manager file=sales.dmp tables=JOHN.SALES

This is a backup of all objects in one schema (useraccount in the database. In this example, we backup all of Harry):

# exp system/manager file=harry.dmp owner=HARRY

Note:

The "system/manager@planning" part in upper commands should be read as "account/password@connectstring" whereas "system" is one of the standard administrative superusers in the database.

Actually, every Oracle database has two very important superusers, namely "sys" and "system", whereas the "sys" account has all possible powers in the database. But you can make perfect full dabase exports using system or sys.

Although the "exp" utility is available in 9i,10g, and 11g, as from version 10g, Oracle strongly encourages all dba's to use the "expdp" utility instead (but many dba's still use the exp utility).

As an example of the expdb utility:

# expdp scott/tiger@db10g tables=EMP,DEPT directory=TEST_DIR dumpfile=EMP_DEPT.dmp logfile=expdpEMP_DEPT.log

3. Making a backup using the RMAN utility:

The rman utility is quite good. In any case much better than the other options that are available to you. Once correctly "configured", a full database backup could be produced as in the following example,

while the database is open:

Just call the rman executable from the prompt:

# rman

RMAN>

Now, in this example, you could type the following commands:

RMAN> run {

2> backup database plus archivelog; 3> delete noprompt obsolete;

4> }

If you don't understand the upper command, that's allright, because we still need to explain rman. And that will be done in section 4.

The advantages (although its probably not obvious) is that rman will figure out where to store backups, and it will keep a repository of all recent backups, and it will compress backups (saving space on disk or tape).

As promised before, in section 4 we will explain exactly how to make correct backups, and how to restore them.

3. Database in Archive log or non Archive log.

Before we go into the real specifics of backup and restore, we need to know what the differences are between a database that‟s in "archive mode", and a database that‟s not in archive mode.

Before we explain that, here is a general dba rule:

Production databases should really run in archive mode. If not, you might risk loss of data.. Test / development databases might be in non archive mode.

If we take a look at our sample database, planning, we only have a few files. But Oracle databases might contain hundreds or thousends of files.

Our "planning" database consists of the following files:

planning_data01.dbf file, created by the dba, and ment to contain the application tables.

planning_index01.dbf file, created by the dba, and ment to contain indexes (of the application tables). system.dbf "standard file" and contains the dictionary (metadata)

undo.dbf "standard file" and contains undo data for transactional consistency

temp.dbf "standard file", contains only temporary data from querys that produces sorts

sysaux.dbf "standard file", contains stuff we do not consider in this note. Contains no data of the application, but its more tool oriented.

redo01.dbf redo logfile

redo02.dbf redo logfile

redo03.dbf redo logfile

control01.ctl control file

control02.ctl control file

control03.ctl control file

So, the .dbf files, are the files with data.

(actually, Oracle does not mind what file extension is in place)

Some files are just "there", just after you have created a database: these are "standard files", like the dictionary "system.dbf", or the undo tablespace, which contains the "former" values of tabledata if users do DML (insert, update, delete) on tables.

In our sample database, the by the dba created files "planning_data01.dbf" and "planning_index01.dbf", are specifically created to contain all the application tables and indexes.

Now what are the redo*.dbf files for? In our sample, we have three of those files, but there could be more of them. That's the choice of the dba.

By the name of these files, you might even have guessed there purpose: they are "logs" to make the "redo" of transactions possible in case of a malfunction (crash) of some sort.

Here is how a transaction works:

A user does an insert, or update, or delete statement on table(s). These changes are captured in a small buffer in RAM (logbuffer), and once the user commits his or her transaction, those changes are immediately written to the current redolog, for example redo03.log.

That is because professional databases uses "write ahead" logs

that captures changes. Ofcourse, the changed data will also be written to the true database file(s), (very) shortly after the capture in the current redolog.

Why is that organized in that way? Actually, we have two "writes" of changes: First a process (logwriter) puts the changes immediately in the current redolog, then shortly after that, another process (db writer) writes the changes

to the actual databasefiles.

This is more failsafe than that the db writer was the only process writing changes. Ofcourse, there is a little more overhead this way.

Suppose a crash occurs. The current redolog has all commited changes captured.When the database starts up again, a process called "recovery and rollback" takes place, which means that all uncommited changes will rollback, but all committed changes (which were not yet written to the databasefiles) will be redone (redone).

Now, in our example we have three redologs. They are used one after the other, that is, if redo01.dbf gets full, the system will use redo02.dbf. If that one gets full also, the system will use redo03.dbf.

What happens if redo03.dbf gets full also? The system has no other choice than to return to redo01.dbf and starts using that one again! So, the use of the logs is somewhat "circular", that is: use the logs one after the other, and if all are used, return to the first redolog and start using that one again.

if redo03.dbf is full then use redo01 again

Note: So please note that the database is using one redolog at the time. (If you follow Oracle's advice, you would have multiplexed the redologs, so that every logfile has a mirror on another filesystem).

Suppose our planning database is still in non archive mode. If we place it in archive mode, Oracle will use different behaviour with regards to the redologs. This is what happens in archive mode: if the system needs to re-use a certain redolog, it will FIRST MAKE A COPY OF THAT FILE AND STORE THAT COPY IN SOME DESIGNATED PLACE. This location, where the "archived" redologs are stored, is usually called "archive log destination",

which is ofcourse just some filesystem or directory on your host machine.

So, we still have our three Online redologfiles (redo01.dbf, redo02.dbf, redo03.dbf) but because copies

are created at some "archive log destination", we build a history of all changes that occurred on the database. Sure this means some extra Administrative maintenance. Somebody needs to take care of those archive logs, because after weeks or months, those archives can fill up your disk(s).

The archived redologs plays an important role in recovery of the database.

Now, we do not ask from you to understand the script below. Its an RMAN script, and notice that it also backups the

archived redologs, and after backup, it deletes them, thereby implementing some automatic maintenance on the archived logs.

run {

allocate channel t1 type 'sbt_tape' parms 'ENV=(tdpo_optfile=/usr/tivoli/tsm/client/oracle/bin64/tdpo.opt)'; allocate channel t2 type 'sbt_tape' parms 'ENV=(tdpo_optfile=/usr/tivoli/tsm/client/oracle/bin64/tdpo.opt)'; backup full database ;

backup (spfile) (current controlfile) ; sql 'alter system archive log current';

backup archivelog all delete input ;

release channel t1; release channel t2; }

redo01.dbf use this one

redo02.dbf then use this one

redo03.dbf then use this one

About the SCN: System Change Number

In an Oracle database, a sort of "clock" is running. This is not about time, but it‟s about a number that's continuously increasing. Oracle uses this number to uniquely identify any change in the database. So, for example, suppose a user Updates, or Deletes, or Inserts a row in or from a table, this

change is characterized by a unique number, derived from this "clock". That number is called the "System Change Number", or SCN.

So, all modifications to the database, are "liked to", or "identified by" a certain SCN.

Suppose you shutdown the database. If you shutdown it "cleanly", then the checkpoint process will update all database fileheaders with the last SCN.

This is very important, because if you start that database again, the Oracle processes will check the SCN of the database files, and it will determine if "recovery" is needed on one or more files.

If you shut down an Oracle database "in a clean way", no recovery is needed at startup.

Note: How to shutdown an Oracle database?

We will explain how to stop and start a database from the prompt.

First of all, logon to the OS (or "su -") as the "oracle user" (that is probably the account that also owns the software). If you have more than one Oracle instance running on your system, its very likely you need to export a variable

that "points" to the correct "SID" (Oracle database identifier), like for example in the following way:

$ export ORACLE_SID=PLANNING

The upper variable is the ORACLE_SID that now "points" to the PLANNING database.

Now you want to logon to the database. There are several ways to do that. What you could do is illustrated in the following examples:

$ sqlplus /nolog OR $ sqlplus username/password

SQL> connect / as sysdba SQL>

You see that you have the SQL> prompt in front of you. If you have been logged on as an Orcale database superuser, that is SYS, then you have the authority to start or stop a database.

It might look a bit weird, but if you connect yourself to the database like in "connect / as sysdba" you are "automatically" a sysdba, which means you are logged on to the database as "sys".

We don‟t go in deeper on those specifics, because the purpose here is how to show you to stop or start a database. Stop a database:

SQL> shutdown normal | immediate | abort

If you use "normal", Oracle waits until all transactions are finished which could mean that you might wait

indefenitely. So do not use that ! The clause "immediate" is always the best to use, because running transactions are rolled back and the database will shutdown cleanly, that is, in a consistent way.

The "abort" option, immediately kills the database. In this case, the fileheaders have not been updated with the last SCN, so at a restart recovery is needed (using the online redologs).

You will only seldom use this option.

So under normal circumstance, you shutdown a database cleanly with:

SQL> shutdown immediate

Startup a database:

If the database is down, but you are connected as a sysdba, you can easily startup a database with

SQL> startup OR SQL> mount pfile="full_path_to_parameterfile_init.ora" SQL> alter database open;

4. Backup an Oracle Database with OS commands

As said before, the best way to backup your database is with using the rman utility. This will be discussed in Section 5.

But, we can demonstrate some features and theory, by first discussing how to make a backup using OS commands. And, not everybody is using rman, and "legacy" scripts are still in use by some dba's.

4.1 Backup a closed database with OS commands.

If a database is closed, no process is acting on the databasefiles. If you have shutdown the database in a clean way, all files are consistent and have the same SCN in their fileheaders.

It does not matter whether the database is in archive mode, or not. Just use your favourite command to copy the files to a dump disk or tape(s), like for example for our example PLANNING database:

# tar -cvf /dev/rmt1.1 /u01/oradata Or something like # tar -cvf /dev/rmt/0hc /u01/oradata # tar -cvf /dev/rmt1.1 /u02/oradata this on your # tar -rvf /dev/rmt/0hc /u02/oradata # tar -cvf /dev/rmt1.1 /u03/oradata unix and tape # tar -rvf /dev/rmt/0hc /u03/oradata # tar -cvf /dev/rmt1.1 /u04/oradata device: # tar -rvf /dev/rmt/0hc /u04/oradata # tar -cvf /dev/rmt1.1 /u05/oradata # tar -rvf /dev/rmt/0hc /u05/oradata

The exact form of the tar commands, depends ofcourse on your tapedrive or class. If the database runs in archive mode, you also want to backup the archived logfiles.

How "much" of archive you want to backup, is really your choice. Suppose some sort of maintenance is in operation on those archived logs and only the last couple of days are in (for example) "/u06/archives", you should probably add to your backup commands:

# tar -cf /dev/rmt1.1 /u06/archives

Now its left to the DBA or sysdamin, to create a good shell script, possibly in a dynamic form that might discover all relevant files at a particular moment. If you have such a "dynamic" script, you don't need to adjust it all the time when files are added or deleted from the database.

Note:

Once logged on the the database via sqlplus, you can get lists of the relevant databasefiles via:

SQL> select name from v$datafile; SQL> select * from v$logfile; SQL> select * from v$controlfile;

4.2 Backup an open database with OS commands.

You can ONLY BACKUP AN OPEN DATABASE IF IT RUNS IN ARCHIVE MODE. It is absolutely of no use to use command like shown in section 4.1 on an open database.

>>>> How to explain that statement?

A database file consists of blocks which ultimately consists of diskpages. At the time of creation of the database, you may specify the blocksize you favor, like 4K, 8K, 16K, 32K etc..

Now if you use "cp" or "cpio" or "dd" or "tar" or whatever command, many process could be at work on many different blocks at the same time. When your copy command is busy copying the file, those processes could have changed many blocks all over the file. Actually, we really do not know what has changed at different parts of the file, but there is a very high probability your copy is totally inconsistent.

If you want to use OS commands on a open database, you MUST "inform" Oracle about your plans to backup the database using a certain OS command.

You do that by issuing the SQL command "ALTER TABLESPACE <table_space_name> BEGIN BACKUP" command, for every tablespace in the database.

If you have done that, Oracle will take certain "measures" to insure that your OS copy command will produce a correct result.

(Actually, it will copy complete block(s) to the online redologs if such a block is being modified while your OS command is running against that particular file).

And when you are ready using your OS command, you signal Oracle to place the tablespaces in

their "normal" mode again, by using the SQL command "ALTER TABLESPACE <table_space_name> END BACKUP". So how do you use OS commands at an open Oracle database (that is running in archive mode)?

As an example, we show how to do this for our PLANNING database.

For easy reference, we show again on which filesystems the database files resides: File: /u01/oradata/planning/planning_data01.dbf /u02/oradata/planning/planning_indx01.dbf /u03/oradata/planning/users.dbf /u03/oradata/planning/system.dbf /u03/oradata/planning/sysaux.dbf /u03/oradata/planning/temp.dbf /u03/oradata/planning/undo.dbf

/u04/oradata/planning/redo01.dbf, redo02.dbf, redo03.dbf

/u05/oradata/planning/control01.dbf, control02.dbf, control03.dbf

So, to create the backup of the Oracle PLANNING database while its running, goes as shown below. But beware that the script is not yet complete!

SQL> alter tablespace SYSTEM begin backup;

SQL> alter tablespace PLANNING_DATA begin backup; SQL> alter tablespace PLANNING_INDEX begin backup; SQL> alter tablespace USERS begin backup;

SQL> alter tablespace SYSAUX begin backup; SQL> alter tablespace UNDO begin backup; SQL> ! tar -cvf /dev/rmt/0hc /u01/oradata SQL> ! tar -rvf /dev/rmt/0hc /u02/oradata SQL> ! tar -rvf /dev/rmt/0hc /u03/oradata SQL> alter tablespace SYSTEM begin backup;

SQL> alter tablespace PLANNING_DATA begin backup; SQL> alter tablespace PLANNING_INDEX begin backup; SQL> alter tablespace USERS begin backup;

SQL> alter tablespace SYSAUX begin backup; SQL> alter tablespace UNDO begin backup;

And if the archived redologs are in "/u06/archives", you add to your backup commands:

SQL> ! tar -rvf /dev/rmt/0hc /u06/archives

Notes:

1. From the sqlplus utility you can issue OS commands by preceding them with the "!" character. 2. You can also exit sqlplus, do the OS commands, and enter sqlplus again.

Since Oracle 10g, you can tell Oracle in one statement that you are going to use OS commands to backup the tablespaces. Just take a look at the following listing, which has the same effect as the script above:

SQL> ALTER DATABASE BEGIN BACKUP;

SQL> ! tar -cvf /dev/rmt/0hc /u01/oradata SQL> ! tar -rvf /dev/rmt/0hc /u02/oradata SQL> ! tar -rvf /dev/rmt/0hc /u03/oradata SQL> ALTER DATABASE END BACKUP;

SQL> ! tar -rvf /dev/rmt/0hc /u06/archives

So you do not need the separate ALTER TABLESPACE commands in Oracle 10g/11g. Issues with online redologs and control files:

is still not complete!

You will notice that I have left out the "/u04" and "/u05" filesystems. Why? Is it not neccesarry to backup the redologs and controlfiles?

Yes, but again we have the open file issue. The "ALTER TABLESPACE.." or "ALTER DATABASE.." commands make it possible to backup the files belonging to the tablespaces, but there's

still an issue with the online redologs (which are not tablespaces). Actually, when we are backing up the tablespaces, there could be a lot of activity on the online redologs. So an OS command is not the way

to backup those structures. So how do we handle this then?

As you know, the contents of the online logs is most valuable if we need to recover the database with the latest transactions.

The solutions is TO ARCHIVE THE ONLINE LOGS with the right commands in our backup script. If we know of a way to archive, or copy, the current log to the archive destination, we produce "a cold" file that we can easily backup with an OS command.

This way, even if the current online log wasn't even "full", we have all the latest transactions in our new archived redolog.

Fortunately, we can archive the current online redolog with:

SQL> alter system archive log current;

So if we add that statement to our backup script, we have the transactions in the redologs covered as well.

Sofar we have the tablespaces covered and the contents of the redologs. What is left are the controlfiles. Also here we have an open file issue that cannot be easily handled with an OS command.

Before we go furthur, we need to know what exactly a control file is.

Well, the real data (tables and indexes) are in the tablespaces. The controlfile only contains "metadata" of the database, such as which files belong to the database as well as some

historical data. Now you might argue that the SYSTEM tablespace contains the real metadata of the database. Sure the SYSTEM tablespace contains every metadata, such as which files belongs to the database, which tables and indexes and other objects are present, which user has rights on which objects etc..

But before the Instance (Oracle software) can find and open the tablespaces, it needs a (small) file which contains "just enough" metadata to get the database mounted.

Indeed, the Instance only needs one controlfile. All the others are just safety copies which the database will keep in sync.

Normally, the dba makes a choice on how many controlfiles he or she wants. In a default installation, the number of controlfiles is usually three. If you want to backup the controlfile, you only

need one copy.

Oracle recommendation: Use a minimum of two multiplexed or mirrored control files on separate disks

We can use the following SQL command to create a backup of the controlfile:

SQL> ALTER DATABASE BACKUP CONTROLFILE TO '/u06/backup/cf.bak' REUSE;

(Specify the REUSE option to make the new control file overwrite one that currently exists.) So, now we have arrived to a usable backupscript using OS commands to create a backup

of our PLANNING database which is open and running:

SQL> alter system archive log current; SQL> ALTER DATABASE BEGIN BACKUP;

SQL> ! tar -cvf /dev/rmt/0hc /u01/oradata SQL> ! tar -rvf /dev/rmt/0hc /u02/oradata SQL> ! tar -rvf /dev/rmt/0hc /u03/oradata SQL> ALTER DATABASE END BACKUP;

SQL> ! tar -rvf /dev/rmt/0hc /u06/archives

SQL> ALTER DATABASE BACKUP CONTROLFILE TO '/u06/backup/cf.bak' REUSE; SQL> ! Tar -rvf /dev/rmt/0hc/ /u06/backup

SQL> alter system archive log current; SQL> ALTER DATABASE BEGIN BACKUP;

SQL> ! tar -cvf /dev/rmt0.1 /u01/oradata SQL> ! tar -rvf /dev/rmt0.1 /u02/oradata SQL> ! tar -rvf /dev/rmt0.1 /u03/oradata SQL> ALTER DATABASE END BACKUP;

SQL> ! tar -rvf /dev/rmt0.1 /u06/archives

SQL> ALTER DATABASE BACKUP CONTROLFILE TO '/u06/backup/cf.bak' REUSE; SQL> ! Tar -rvf /dev/rmt0.1 /u06/backup

Ofcourse, the only difference between both listings is, how to address the tapedevice. Notice how we completely manage the backup from the sqlplus utility.

Just for demonstration purposes, here is a script that backups the database to a dumpdisk:

SQL> alter system archive log current;

SQL> ALTER DATABASE BACKUP CONTROLFILE TO '/backups/planning/cf.bak' REUSE; SQL> ALTER DATABASE BEGIN BACKUP;

SQL> ! cp /u01/oradata/planning/* /backups/planning SQL> ! cp /u02/oradata/planning/* /backups/planning SQL> ! cp /u03/oradata/planning/* /backups/planning SQL> ALTER DATABASE END BACKUP;

SQL> ! cp /u06/archives/* /backups/planning

Now your parameter file (init.ora or spfile.ora) will probably not change frequently. Remember that the parameterfile determines many settings and behaviour of your Instance, like how much memory it may claim from the system, or how many processes may be concurrent. If you have recent backups of the parameter file available, and you make sure that you always can fallback on a good copy (in case the current one gets missing), then all is fine. Otherwise, you might consider an additional statement to your backupscript that copies the parameterfile to a safe location.

This coveres on what we wanted to say on the subject of how to make a open backup of a running Oracle database using OS commands.

Note that we still have omitted many interresting things like how to backup an Oracle database that lives on raw devices instead of filesystems or ASM.

But this document is supposed to be a quick intro on the subject of backup an restore. Note: example on how to maintain the number of archived redologs.

If you do not use rman, you need to have a method to deal with the growing number of archived redologs. Ofcourse, you want to keep enough of them on disk (in case you need them), but diskspace might be an issue as well.

Suppose your database archives redologs to the directory '/u06/archives/today' Now, you could place the following at the end of your backupscript. We assume here

that the archived redologs have the '.arc' extension, but any other will be good as well. We also assume that the script runs late in the evening, or at night.

rm -rf /u06/archives/2dayago/*.arc

mv /u06/archives/1dayago/*.arc /u06/archives/2dayago mv /u06/archives/today/*.arc /u06/archives/1dayago

So, effectively, we keep two days of archived redologs on disk.

5. Backup an Oracle Database using RMAN

we will limit ourselves here to prompt oriented sessions. RMAN has some important advantages over OS commands: 1. Compression: your backups can be compressed.

2. "Automatic" history of your backups, of which you can retreive lists and reports. 3. Faster and slimmer backups because rman "knows" what to backup of files, compared to the rather "crude" OS commands, which has a file as its granular metric. 4. If you want, with rman you can create full and incremental backups.

5. In case of a corrupt database block, rman can perform blockrecovery.

So, the larger your database is, the more advantages you will have in using rman.

But be carefull. Every Oracle RDBMS version has bugs, of which fixes and patchsets will be released regularly.

Also, on your Oracle version, rman could have some nasty bugs. It has happened before. Be sure to check the most important bugs and check whether you need a fix or patchset.

5.1 Some notes about the RMAN architecture, and creating the catalog.

RMAN creates backups of your database. But you can use rman with multiple databases, possibly on different host machines.

Any way, whether you use rman to backup a single database, or many databases, rman needs

a "repository" to store some vital data like which backupsets are associated to which database, the pieces that belong to the sets, and all kinds of other usefull information.

This repository is called the "rman catalog". You can use the following repositories:

1. The target's database controlfile (belonging to the database which rman will backup) or

2. A separate small database, especially created to store rman's metadata.

Actually, only a separate rman tablespace and a user rman is required. So you could use an existing database for holding the rman repository. But if that database is an important target database (to backup), then, ofcourse, its silly to use that database.

Although you might not be happy to create a separate database, just for holding rman's metadata, only a very small database is required with an rman tablespace (which does not need to be

larger than 100M). Ofcourse, if a database is used as the rman catalog, much more historical data can be stored, in comparison to a controlfile.

Now above we said that either the target's controlfile can be used, or a separate database can be used as the rman catalog. Well, even if a catalog database is present, the controlfiles

of the targetdatabase(s) will always contain the latest rman backup information, so its actually impossible to avoid using the controlfile as metadata for rman.

Even stronger, the controlfile remains Authorative in the sense that this "latest info", and periodically that data will be transferred to the catalog. That‟s called synchronizing the catalog.

But the rman catalog database can hold much more data, and more types of data, then the target's database controlfile. In the controlfile, only the last backups are listed.

rman catalog which is a small

target database Oracle database

(which you want to backup)

Remark: should one backup that target's controlfile RMAN backupjobs / scripts server session(s) backup tapes or

database as well? Yes, an export or cold or hot

backup with sufficient frequency should be fine.

The recovery catalog contains information about RMAN operations, including: Datafile and archived redo log backup sets and backup pieces

Datafile copies

Archived redo logs and their copies

Tablespaces and datafiles on the target database

If you want you can also store scripts, which are named user-created sequences of RMAN commands Persistent RMAN configuration settings

If we want a RMAN recovery catalog in a database, we follow the following steps: 1. Create a small database

2. Create a suitable tablespace, e.g. with the name "rman" and, let's say, with a initial size of 100MB. 3. Create the user "rman" which has as its default tablespace tablespace "rman"

4. Create the catalog on tablespace rman as user rman (use the "CREATE CATALOG" command). 5. Register all the databases you want to backup with this catalog.

So, suppose you have created the database "PRODRMAN" (step 1). Now connect as sys or system to that database.

Create the tablespace, for example like:

create smallfile tablespace rman logging

datafile '/dbms/oradata/prodrman/rman01.dbf' size 100M reuse autoextend on next 10M maxsize 500M extent management local

segment space management auto;

The above command was step 2. Now we create the catalog owner, that is, the user rman:

create user rman identified by rman default tablespace rman

temporary tablespace temp;

- give the right permissions:

grant connect, resource, recovery_catalog_owner to rman;

Next we create the catalog in database PRODRMAN:

On unix, make sure your environment variable ORACLE_SID points to the right Database:

$ export ORACLE_SID=PRODRMAN (it does not always has to be in capital, maybe your database id uses small capital).

$ rman (call the rman executable) The RMAN> prompt appears:

RMAN>

Now, to connect, or logon, to the catalog database, you use the "connect catalog" command, and pass along the right credentials as in:

RMAN>connect catalog rman/rman

RMAN-06008 connected to recovery catalog database RMAN-06428 recovery catalog is not installed RMAN> create catalog;

RMAN-06431 recovery catalog created

backup tapes or backup disks

A number of tables and views are installed in the database PRODRMAN, and those objects live in the tablespace "rman".

You can expect something like the following to exist in the PRODRMAN database:

SQL> select table_name, tablespace_name, owner 2 from dba_tables where owner='RMAN';

TABLE_NAME TABLESPACE_NAME

----

---AL DATA RMANRMAN BCB DATA RMANRMAN BCF DATA RMANRMAN BDF DATA RMANRMAN BP DATA RMANRMAN BRL DATA RMANRMAN BS DATA RMANRMAN CCB DATA RMANRMAN CCF DATA RMANRMAN CDF DATA RMANRMAN CKP DATA RMANRMAN CONFIG DATA RMANRMAN DB DATA RMANRMAN DBINC DATA RMANRMAN DF DATA RMANRMAN DFATT DATA RMANRMAN OFFR DATA RMANRMAN ORL DATA RMANRMAN RCVER DATA RMANRMAN RLH DATA RMANRMAN RR DATA RMANRMAN RT DATA RMANRMAN SCR DATA RMANRMAN SCRL DATA RMANRMAN TS DATA RMANRMAN TSATT DATA RMANRMAN XCF DATA RMANRMAN XDF DATA RMANRMAN

Those tables (and the views) have all specialized purposes to store the metadata. Like for example, table DB, which listst all "registered" databases.

You may ofcourse query the tables and the views, but you can also retrieve all kinds of reports from the RMAN> prompt (which itself does query the tables and views). You absolutely do not have to interface to those tables and views. Just use the rman executable to retrieve info and reports.

As the last step we need to register the target database. In the following example we register the PLANNING database to the catalog.

To do this, we have to connect to the target database, AND to the catalog. That can be done as follows:

$rman

RMAN> connect system/password@PLANNING or RMAN> connect target / connected to target database: PLANNING (DBID=899275577)

(you see that rman now has retrieved the database id)

RMAN> connect catalog rman/rman@PRODRMAN connected to recovery catalog database RMAN> register database;

And after a little while, rman is ready storing some parameters (like the name and dbid) in the catalog. In principle, we are ready to backup the target database. But its much better to set, or store,

some default values in the catalog that has to do on how exactly we want to backup.

Note: You can also connect to the target database and the catalog database in one command, with passing to the rman executable the right credentials, as in the following example:

$ rman target SYS/oracle@PLANNING catalog rman/rman@PRODRMAN

5.2 Setting defaults.

You are certainly not required to set defaults in rman, but some options in your scripts that you normally always would set, can just as well be put as a configurable setting. Suppose you would give the following series of commands:

RMAN> configure retention policy to recovery window of 5 days; RMAN> configure default device type to disk;

RMAN> configure controlfile autobackup on;

With this series of command, you have told rman to keep all backups that are not older than 5 days, and that per default the backups will be stored to disk, and that at every backupjob, the controlfile must be automatically included.

Note:

You could go now to the second sheet "Some_RMAN_Notes" and see if you inderstand all backup and restore examples over there.

Ofcourse, you are invited to finish this sheet first.

The above list of rman commands, are very important. The first command tells rman how long to keep backups available, before considering them as obsolete.

Some other examples of setting the retention policy:

Ensure that RMAN retains all backups needed to recover the database to any point in time in the last 7 days:

RMAN> CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 7 DAYS;

Retain three backups of each datafile:

RMAN> CONFIGURE RETENTION POLICY TO REDUNDANCY 3;

You can get a list of all current setting by using the command: RMAN> show all;

RMAN configuration parameters are:

CONFIGURE RETENTION POLICY TO REDUNDANCY 1; # default CONFIGURE BACKUP OPTIMIZATION OFF; # default

CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default CONFIGURE CONTROLFILE AUTOBACKUP OFF; # default

CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F'; # default CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO BACKUPSET; # default CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default

CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default CONFIGURE MAXSETSIZE TO UNLIMITED; # default

CONFIGURE ENCRYPTION FOR DATABASE OFF; # default CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default

5.3 Creating backups with rman.

Lets start with a basic backup script that really will make a complete (open) backup of an oracle database, plus all archived redologs.

RMAN> run {

2> backup database plus archivelog; 3> delete noprompt obsolete;

4> }

From the prompt, type in those commands, and after the last "}", the script will execute.

So, from the RMAN> prompt, you can give singular commands, that is, one statement, like for example:

RMAN> configure controlfile autobackup on;

And you can let rman run a script by starting with "run {" followed by as series of statements.

RMAN> run {

statements … }

If you take a look again at the above backupscript, you may wonder why there is no specification to where you want to backup, because you might want to choose between tape or disk.

Well, you can configure that as a default as shown in section 5.2.

Secondly, we do not see a "channel" alocation in the script. A "channel" is a Server session which will access the database, selects the data, and writes the data to the backuplocation. RMAN comes preconfigured with one DISK channel that you can use for backups to disk.

So, if you backup to disk, you do not need to specify a "channel".

Now let us take a look at a second backup script, which backups the database to tape(s).

RMAN> run {

2> allocate channel t1 type 'sbt_tape' parms 'ENV=(tdpo_optfile=/usr/tivoli/tsm/client/oracle/bin64/tdpo.opt)'; 3> allocate channel t2 type 'sbt_tape' parms 'ENV=(tdpo_optfile=/usr/tivoli/tsm/client/oracle/bin64/tdpo.opt)'; 4> backup full database ;

5> backup (spfile) (current controlfile) ; 6>

7> sql 'alter system archive log current'; 8>

9> backup archivelog all delete input ; 10>

11> release channel t1; 12> release channel t2; 13> }

In this example, you see that two channels are created before the actual backupcommands

are called. These channels will write to tape, because in the statement you might see the "sbt_tape" identifier. This is "rman language" for "we will backup to tape".

Writing to disk, is easy for the Server session, because the Hosts Operating System "knows" how to write to disks. So there will never be "difficult" parameters in the "allocate channel" command.

In this case, the allocate channel command have the simple form of:

allocate channel <channel_name> type disk;

So for example:

allocate channel d1 type disk;

Writing to tapes cannot be done by the Server session without the help of specialized libaries.

The tapedrive or taperobot can probably only be accessed by specific third-party software. That‟s why in the channel allocation command, you will see parameters that referr to

That‟s why you find in the allocate channel command parameters similar to this example:

allocate channel t1 type 'sbt_tape' parms 'ENV=(tdpo_optfile=/usr/tivoli/tsm/client/oracle/bin64/tdpo.opt)';

5.4 Changing the status of backups on disk or tape.

The following options in changing the status of backups will be discussed: CHANGE … KEEP / NO KEEP

CHANGE … AVAILABLE / UNAVAILABLE OBSOLETE BACKUPS

CHANGE .. KEEP / NO KEEP: Changing Retention Policy Status of RMAN Backups

Maybe sometimes you want to change the status of a certain backup, so that it no longer falls under the rules of the RETENTION POLICY.

Use CHANGE... KEEP or CHANGE... NOKEEP to specify whether a backup should be subject to the configured retention policy or kept until a different date or even indefinitely.

The KEEP option exempts a backup from the current retention policy either indefinitely or until the specified UNTIL time. RMAN does not mark the files as obsolete even if they would be considered obsolete under the retention policy. Such backups are called long-term backups.

CHANGE ... NOKEEP is used to undo the effects of CHANGE ... KEEP, so that the configured retention policy applies to the backup.

For example, the following command prevents RMAN from considering backupsets with the tag 'year_end_2002' as obsolete under the retention policy:

RMAN> CHANGE BACKUPSET TAG year_end_2002 KEEP FOREVER NOLOGS;

To allow backupsets with the tag year_end_2002 to be marked as obsolete based on the retention policy, use this command:

RMAN> CHANGE BACKUPSET TAG year_end_2002 NOKEEP;

If you want to prevent the use of a backup marked with KEEP in restore and recovery operations, then mark these backups as UNAVAILABLE. RMAN will not delete the records for these backups

from the RMAN repository, but will not try to use them in restore and recovery until they are marked AVAILABLE again.

OBSOLETE BACKUPS.

With the REPORT OBSOLETE command, you can find the backupsets which are "after" the retention policy. Lets say that your retention policy window is 7 days.

In that case, all backups that are older than 7 days are reported as obsolete.

RMAN> report obsolete;

RMAN retention policy will be applied to the command RMAN retention policy is set to recovery window of 1 days Report of obsolete backups and copies

Type Key Completion Time Filename/Handle --- --- ---Backup Set 24 28-JUN-08

Backup Piece 26 28-JUN-08 C:\ORACLE\FLASH_RECOVERY_AREA\TEST10G\BACKUPSET\2008_06_28\O1_MF_NNNDF_TAG2008 0628T012918_46C1GZMK_.BKP

Backup Set 25 28-JUN-08

Backup Piece 27 28-JUN-08 C:\ORACLE\FLASH_RECOVERY_AREA\TEST10G\BACKUPSET\2008_06_28\O1_MF_NCSNF_TAG2008 0628T012918_46C1KQ3F_.BKP

Backup Set 39 28-JUN-08

Backup Piece 43 28-JUN-08 C:\ORACLE\FLASH_RECOVERY_AREA\TEST10G\BACKUPSET\2008_06_28\O1_MF_NCNNF_TAG2008 0628T013052_46C1KY00_.BKP

Backup Set 40 28-JUN-08

Backup Piece 44 28-JUN-08 C:\ORACLE\FLASH_RECOVERY_AREA\TEST10G\BACKUPSET\2008_06_28\O1_MF_NNSNF_TAG2008 0628T013052_46C1L26T_.BKP

Backup Set 65 28-JUN-08

Backup Piece 67 28-JUN-08 C:\ORACLE\FLASH_RECOVERY_AREA\TEST10G\BACKUPSET\2008_06_28\O1_MF_ANNNN_TAG2008 0628T013104_46C1L9TC_.BKP

Backup Set 89 28-JUN-08

Backup Piece 94 28-JUN-08 C:\ORACLE\FLASH_RECOVERY_AREA\TEST10G\BACKUPSET\2008_06_28\O1_MF_NCSNF_TAG2008 0628T014827_46C2OXQG_.BKP

RMAN>

Note:

An obsolete backup differs from an expired backup. An obsolete backup is no longer needed according to the user's retention policy. An expired backup is a backup that the CROSSCHECK command fails to find on the specified media device.

Its important to realize that you can still use obsolete backups. They only fall outside your retention period.

It's necessary to maintain the catalog and delete the obsolete backups. Make sure you really want this, and have enough recent backups available.

You can achieve this with:

RMAN> delete obsolete;

5.5 List and Report commands of RMAN Backups

>>>> LIST COMMAND:

List commands query the catalog or control file, to determine which backups or copies are available. List commands provide for basic information.

Report commands can provide for much more detail. About RMAN Reports Generated by the LIST Command

You can control how the output is displayed by using the BY BACKUP and BY FILE options of the LIST command and choosing between the SUMMARY and VERBOSE options.

-- Example 1: Query on the incarnations of the target database RMAN> list incarnation of database;

RMAN-03022: compiling command: list List of Database Incarnations

DB Key Inc Key DB Name DB ID CUR Reset SCN Reset Time --- --- --- --- --- ---1 2 AIRM 20923037---15 YES ---1 24-DEC-02

-- Example 2: Query on tablespace backups

You can ask for lists of tablespace backups, as shown in the following example:

RMAN> list backup of tablespace users;

RMAN> list backup of database;

-- Example 4: Query on backup of archivelogs: RMAN> list backup of archivelog all;

The primary purpose of the LIST command is to determine which backups are available. For example, you can list: . Backups and proxy copies of a database, tablespace, datafile, archived redo log, or control file

. Backups that have expired

. Backups restricted by time, path name, device type, tag, or recoverability . Incarnations of a database

By default, RMAN lists backups by backup, which means that it serially lists each backup or proxy copy and then identifies the files included in the backup. You can also list backups by file.

By default, RMAN lists in verbose mode. You can also list backups in a summary mode if the verbose mode generates too much output.

Listing Backups by Backup

To list backups by backup, connect to the target database and recovery catalog (if you use one), and then execute the LIST BACKUP command. Specify the desired objects with the listObjList clause. For example, you can enter:

LIST BACKUP; # lists backup sets, image copies, and proxy copies LIST BACKUPSET; # lists only backup sets and proxy copies

LIST COPY; # lists only disk copies Example:

RMAN> LIST BACKUP OF DATABASE;

By default the LIST output is detailed, but you can also specify that RMAN display the output in summarized form. Specify the desired objects with the listObjectList or recordSpec clause. If you do not specify an object,

then LIST BACKUP displays all backups.

After connecting to the target database and recovery catalog (if you use one), execute LIST BACKUP, specifying the desired objects and options. For example:

LIST BACKUP SUMMARY; # lists backup sets, proxy copies, and disk copies

You can also specify the EXPIRED keyword to identify those backups that were not found during a crosscheck: LIST EXPIRED BACKUP SUMMARY;

# Show all backup details list backup;

>>>> REPORT COMMANDS: RMAN>report schema;

Shows the physical structure of the target database.

RMAN> report obsolete;

RMAN-06147: no obsolete backups found

About Reports of RMAN Backups

Reports enable you to confirm that your backup and recovery strategy is in fact meeting your requirements for database recoverability. The two major forms of REPORT used to determine whether your database

is recoverable are: RMAN> REPORT NEED BACKUP;

Reports which database files need to be backed up to meet a configured or specified retention policy

Use the REPORT NEED BACKUP command to determine which database files need backup under a specific retention policy. With no arguments, REPORT NEED BACKUP reports which objects need backup under the currently configured retention policy. The output for a configured retention policy of REDUNDANCY 1 is similar to this example:

REPORT NEED BACKUP;

RMAN retention policy will be applied to the command RMAN retention policy is set to redundancy 1

Report of files with less than 1 redundant backups File #bkps Name

---- --- ---2 0 /oracle/oradata/trgt/undotbs01.dbf

RMAN> REPORT UNRECOVERABLE;

Reports which database files require backup because they have been affected by some NOLOGGING operation such as a direct-path insert

You can report backup sets, backup pieces and datafile copies that are obsolete, that is, not needed to meet a specified retention policy, by specifying the OBSOLETE keyword. If you do not specify any other options, then REPORT OBSOLETE displays the backups that are obsolete according to the current retention policy, as shown in the following example:

RMAN> REPORT OBSOLETE;

In the simplest case, you could crosscheck all backups on disk, tape or both, using any one of the following commands:

RMAN> CROSSCHECK BACKUP DEVICE TYPE DISK; RMAN> CROSSCHECK BACKUP DEVICE TYPE SBT;

RMAN> CROSSCHECK BACKUP; # crosshecks all backups on all devices

The REPORT SCHEMA command lists and displays information about the database files.

After connecting RMAN to the target database and recovery catalog (if you use one), issue REPORT SCHEMA as shown in this example:

RMAN> REPORT SCHEMA;

# Show items that beed 7 days worth of # archivelogs to recover completely report need backup days = 7 database; report need backup;

# Show/Delete items not needed for recovery report obsolete;

# Show/Delete items not needed for point-in-time # recovery within the last week

report obsolete recovery window of 7 days; delete obsolete recovery window of 7 days; RMAN> REPORT OBSOLETE REDUNDANCY 2;

RMAN> REPORT OBSOLETE RECOVERY WINDOW OF 5 DAYS;

RMAN displays backups that are obsolete according to those retention policies, regardless of the actual configured retention policy.

# Show/Delete items with more than 2 newer copies available report obsolete redundancy = 2 device type disk;

delete obsolete redundancy = 2 device type disk; # Show datafiles that connot currently be recovered report unrecoverable database;

Simple intro Backup and Recovery of Oracle Databases 9i, 10g, 11g

Important: This is a quick and simple introduction to Oracle Backup and Recovery, and is by no means a complete description.

This basic intro is for people who know at least some basics of Oracle Databases, but who need an overview

This manual is suitable for Oracle 9i, 10g, and 11g. Ofcourse, there are differences between those versions, but with

respect to backup and recovery, the differences are actually not too big. But please be aware of the fact that 11g has some

This sheet explains some basic features on the subject of "how to create backups".

The second sheet, "Examples_and_Notes", shows some examples of rman backup and recovery scripts.

The third sheet, "Dictionary_views_target_db", will show you which dictionary views (of the Target DB) you can query on backups and recovery. The fourth sheet, "SomeDisasterRecoveryScenarios", will show you some important problems from which you may recover.

1. What to backup? What should you include in a database backup?

That is a copy of all relevant database files to tape(s) or backup disk(s). This is a full or partial export of the contents of the database (e.g. tables, indexes, procedures etc..) to one or more file(s).

So what type of backup should you choose? Actually, most dba's do both types of backups, whereas the "physical backup" is regarded as the most important backup, that is, it‟s the backup you probably will use in case of disaster recovery. The logical backup is then probably scheduled less frequently and can be used as some sort of "last resort", or as

Let's first take a look at what exactly should be backupped frequently. We will use an example of an simple Oracle database, let's call it "PLANNING", which, in this example, contains of a small number of database files. The whole organization of the Oracle Software, and the actual database, could be as in the following example.

This is just the Oracle Software. Those files are "static" and remains the same unless you to plan

to do an upgrade, or install a patch. But, in a default installation, also some logs can be found here, for example the "listener.log", but those

logs are not directly critical for the operation

In our example, the Oracle software was installed in /opt/ora10g/product/10g but evidently, this could also have been

Administrative Logfiles and the socalled "parameter file" are usually stored in

This directory usually contains several subdirs like "dump", "udump", "cdump", "audit"

It can contain also the directory "pfile" where the parameter file "init.ora" or "spfile.ora" can be located. Such a parameterfile contains all kinds of settings for the Oracle instance (like how much memory it may use, or how many processes

So when we talk about a "backup the database", we talk about creating a copy of the files in section II, that is, However trivial that former statement may be, its important to know what exactly you need to backup

A database consists of a number of files, like our planning database shown in II.

But oracle also uses the logical concept of "tablespace". A tablespace has no meaning to the OS,

The purpose of a tablespace is at least twofold: performance gain, and neat administration.

It would have been nice if you could store a large table over multiple files, over multiple filesystems.

So, suppose I create the tablespace "PLANNING_DATA", and I want to let it have two files:

That would work. Note that the tablespace consists of two files, over two separate filessystems /u01 and /u02. Now I could create a table, for example EMPLOYEES, and let it "live" in tablespace PLANNING_DATA:

Now I know for sure on which files the table will be on. Additionally, if the table gets very large

I could benefit from "parallel IO" because of the two independent filesystems (assuming those are on separate disks). Now, since in my example database, I only have one physical file "planning_data01.dbf",

The dba can create multiple tablespaces, maybe not only for performance reasons, but also for Administration purposes. Would it not be great if you, for example, separate your tables and indexes on separate tablespaces?

Also, maybe there are several "schema's" (useraccount) in your database, and you would create a neat administration

We already have seen that Oracle itself, makes a distinction between a physical backup and a logical backup of a database. In a moment, you will see that there is also a difference in a socalled "open" backup, that is, creating a backup

while the database is open, and a socalled "closed" backup, where the database is shutdown.

You will see that the Oracle supplied utility "RMAN" is THE tool to backup a database. However, the following tools and commands can be used to backup an Oracle database:

Now you may have seen on, for example a unix system, that the sysadmin is using Tivoli TSM (or other backup suite) Those types of tools may ALSO have been configured with the add-on product TDPO, so that (open) Oracle databases So it might look to you as if the above table is not complete ! That's not true. Almost all

of those professional backup software suites, that are configured for making Oracle backups as well, uses the Oracle's RMAN utility "under water", so it's not immediately obvious that really rman

Now we give some examples in creating a backup of a database. Do NOT take these as "live" or "usable" examples, because we still NEED to excplain the difference between a database in archive mode and in non-archive mode. So, creating backups could be similar as in the following examples: (do not takes these for as "being usable"):

Suppose the Planning database is shutdown. This means that no process is acting on the databasefiles. They are just a bunch of "cold" files at this time. This is very important, otherwise we COULD NOT use the commands as shown in the examples.

# tar -cvf /dev/rmt/0hc /u01/oradata # tar -rvf /dev/rmt/0hc /u02/oradata # tar -rvf /dev/rmt/0hc /u03/oradata # tar -rvf /dev/rmt/0hc /u04/oradata # tar -rvf /dev/rmt/0hc /u05/oradata

The exact form of the tar commands, depends ofcourse how your tapedrive or class is called..

Now, if the database was indeed down, this was a valid backup. If the database was open, and thus in use, this backup was not valid, because the shared databases files are accessed by potentially many

1. On some unixes, rmt1.1 is the non-rewinding class of the tapedrive on rmt1, in contrast with "rmt1", which is rewinding Be very sure to know what is your non-rewinding device, or you will only have the last file on tape!

2. The commands above could have been in much smarter format like "cp with using ranges with brackets []

Suppose the Planning database is open. Now, that‟s actually a requirement if we want to use the "exp" or "expdp" uitilites. We will create a logical backup, that is, all objects (tables, indexes, procedures and all other fancy stuff)

will be put nicely in one (or more) files. This file can be used if we want to restore the whole database, or

# exp system/manager@planning file=planning_06062008.dmp full=y log=planning_06062008.log

This is a backup of all objects in one schema (useraccount in the database. In this example, we backup all of Harry):

The "system/manager@planning" part in upper commands should be read as "account/password@connectstring" whereas "system" is one of the standard administrative superusers in the database.

Actually, every Oracle database has two very important superusers, namely "sys" and "system", whereas the "sys" account has all possible powers in the database. But you can make perfect full dabase exports using system or sys.

Although the "exp" utility is available in 9i,10g, and 11g, as from version 10g, Oracle strongly encourages all dba's to use the "expdp" utility instead (but many dba's still use the exp utility).

# expdp scott/tiger@db10g tables=EMP,DEPT directory=TEST_DIR dumpfile=EMP_DEPT.dmp logfile=expdpEMP_DEPT.log

The rman utility is quite good. In any case much better than the other options that are available to you. Once correctly "configured", a full database backup could be produced as in the following example,