Backup Recovery Oracle

Simple intro Backup and Recovery of Oracle Databases 9i, 10g Albert van der Sel

-

Antapex Technologies B.V. -

version 0.12

-

30/09/2008

Important: This is a quick and simple introduction to Oracle Backup and Recovery, and is by no means a complete descrip This basic intro is for people who know at least some basics of Oracle Databases, but who need 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 tho respect to backup and recovery, the differences are actually not too big . But please be aware 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 Tar The fourth sheet, "SomeDisasterRecoveryScenarios", will show you some important problems from wh

1. What to backup? What should you include in a database backup? The Oracle literature distinguishes two types of backups you can make of an Oracle database: 1. Physical backup: 2. Logical backup:

That is a copy of all relevant database files to tape(s) or backup This is a full or partial export of the contents of the database (e 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 is regarded as the most important backup, that is, it‟s the backup you probably will use in case The logical backup is then probably scheduled less frequently and can be used as some sort of "l 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 o Oracle database, let's call it "PLANNING", which, in this example, contains of a small number of The whole organization of the Oracle Software, and the actual database, could be as in the follo We can distinguish 3 "areas" so to speak: I

Software

(Static stuff)

Location of Oracle Software ( ORACLE_HOME ) This is where the software (binaries, libraries etc..) lives. for example in the location: /opt/ora10g/product/10g/ - big tree of - subdirectories

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 operat 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/ II

Databasefiles (Will change constantly while the database is open)

Example locations of the database files

/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 se 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 bac 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 file 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' DATAFILE '/u02/oradata/planning/planning_data02.dbf' EXTENT MANAGEMENT LOCAL;

SIZE 1000M, SIZE 1000M,

That would work. Note that the tablespace consists of two files, over two separate filessystems 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) ) TABLESPACE PLANNING_DATA;

Now I know for sure on which files the table will be on. Additionally, if the table gets very la I could benefit from "parallel IO" because of the two independent filesystems (assuming those ar 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' EXTENT MANAGEMENT LOCAL;

SIZE 1000M

2. Neat Administration:

The dba can create multiple tablespaces, maybe not only for performance reasons, but also for Ad Would it not be great if you, for example, separate your tables and indexes on separate tablespa Also, maybe there are several "schema's" (useraccount) in your database, and you would create a 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 log In a moment, you will see that there is also a difference in a socalled "open" backup, that is, 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 expdb (Oracle supplied rman (Oracle supplied OS commands like cp, tar, cpio etc.. third party tools that rman

utility) utility) utility)

logical logical physical physical

use

physical

Now you may have seen on, for example a unix system, that the sysadmin is using Tivoli TSM (or o for filesystem backups. Those types of tools may ALSO have been configured with the add-on product TDPO, so that (open) 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 w 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 "us because we still NEED to excplain the difference between a database in archive mode and in non-a

So, creating backups could be similar as in the following examples: (do not takes these for as " 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 databasef of "cold" files at this time. This is very important, otherwise we COULD NOT use the commands as As an example we could give the following command sequence: # # # # # #

cp cp cp cp cp cp

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

# cp # cp # cp

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

or backup the files to tape with for example tar: # # # # #

tar tar tar tar tar

-cvf -cvf -cvf -cvf -cvf

/A589 /u01/oradata /dev/rmt1.1 /u02/oradata /dev/rmt1.1 /u03/oradata /dev/rmt1.1 /u04/oradata /dev/rmt1.1 /u05/oradata

Or something like this on your unix and tape device:

# # # # #

tar tar tar tar tar

-cvf -rvf -rvf -rvf -rvf

/dev/rmt/0hc /dev/rmt/0hc /dev/rmt/0hc /dev/rmt/0hc /dev/rmt/0hc

/u01/orada /u02/orada /u03/orada /u04/orada /u05/orada

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 thu 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 Be very sure to know what is your non-rewinding device, or you will only have the last file o 2. The commands above could have been in much smarter format like "cp with using ranges with bra 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 We will create a logical backup, that is, all objects (tables, indexes, procedures and all other will be put nicely in one (or more) files. This file can be used if we want to restore the whole 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 # exp system/manager file=harry.dmp owner=HARRY

Note: The "system/manager@planning" part in upper commands should be read as "account/password@connect 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", wh has all possible powers in the database. But you can make perfect full dabase exports using syst

Although the "exp" utility is available in 9i,10g, and 11g, as from version 10g, Oracle strongly 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=expdp 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 availabl Once correctly "configured", a full database backup could be produced as in the following exampl while the database is open: Just call the rman executable from the prompt: # rman The rman prompt appears:

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 rma And that will be done in section 4.

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

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

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 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 databas or thousends of files. Our "planning" database consists of the following files: planning_data01.dbf planning_index01.dbf system.dbf undo.dbf temp.dbf sysaux.dbf redo01.dbf redo02.dbf redo03.dbf control01.ctl control02.ctl control03.ctl

file, created by the dba, and ment to contain the application table file, created by the dba, and ment to contain indexes (of the appli "standard file" and contains the dictionary (metadata) "standard file" and contains undo data for transactional consistenc "standard file", contains only temporary data from querys that prod "standard file", contains stuff we do not consider in this note. Co redo logfile redo logfile redo logfile control file control file 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" or the undo tablespace, which contains the "former" values of tabledata if users do DML (insert, In our sample database, the by the dba created files "planning_data01.dbf" and "planning_index0 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 cou 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 t 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 in RAM (logbuffer), and once the user commits his or her transaction, those changes are immediat the current redolog, for example redo03.log. Why not write directly to the real database files, like the planning files?

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 (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 (l immediately in the current redolog, then shortly after that, another process (db writer) writes to the actual databasefiles. This is more failsafe than that the db writer was the only process writing changes. Ofcourse, th more overhead this way.

Suppose a crash occurs. The current redolog has all commited changes captured.When the database a process called "recovery and rollback" takes place, which means that all uncommited changes wi 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 redo0 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 redo and starts using that one again! So, the use of the logs is somewhat "circular", that is: use th one after the other, and if all are used, return to the first redolog and start using that one a redo01.dbf

redo02.dbf

redo03.dbf

use this one

then use this one

then use this one

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 you would have multiplexed the redologs, so that every logfile has a mirror on another filesyste

Suppose our planning database is still in non archive mode. If we place it in archive mode, Orac different behaviour with regards to the redologs. This is what happens in archive mode: if the s to re-use a certain redolog, it will FIRST MAKE A COPY OF THAT FILE AND STORE THAT COPY IN SOME This location, where the "archived" redologs are stored, is usually called "archive log destinat 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 are created at some "archive log destination", we build a history of all changes that occurred o Sure this means some extra Administrative maintenance. Somebody needs to take care of those arch 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 archived redologs, and after backup, it deletes them, thereby implementing some automatic mainte run { allocate channel t1 type 'sbt_tape' parms allocate channel t2 type 'sbt_tape' parms backup full database ; backup (spfile) (current controlfile) ; sql 'alter system archive log current'; backup archivelog all delete input ; release channel t1; release channel t2; }

'ENV=(tdpo_optfile=/usr/tivoli/tsm/clie 'ENV=(tdpo_optfile=/usr/tivoli/tsm/clie

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 nu that's continuously increasing. Oracle uses this number to uniquely identify any change in the d 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 wil update all database fileheaders with the last SCN. This is very important, because if you start that database again, the Oracle processes will chec 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 tha If you have more than one Oracle instance running on your system, its very likely you need to ex that "points" to the correct "SID" (Oracle database identifier), like for example in the followi $ 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 i in the following examples: $ sqlplus /nolog SQL> connect / as sysdba

OR

$ sqlplus username/password SQL>

You see that you have the SQL> prompt in front of you. If you have been logged on as an Orcale d 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 sys 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 Stop a database: SQL> shutdown normal | immediate | abort

If you use "normal", Oracle waits until all transactions are finished which could mean that you indefenitely. So do not use that ! The clause "immediate" is always the best to use, because ru 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 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 wi 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 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 dat 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 comm to a dump disk or tape(s), like for example for our example PLANNING database: # # # # #

tar tar tar tar tar

-cvf -cvf -cvf -cvf -cvf

/dev/rmt1.1 /dev/rmt1.1 /dev/rmt1.1 /dev/rmt1.1 /dev/rmt1.1

/u01/oradata /u02/oradata /u03/oradata /u04/oradata /u05/oradata

Or something like this on your unix and tape device:

# # # # #

tar tar tar tar tar



/u01/orada /u02/orada /u03/orada /u04/orada /u05/orada

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 maintenanc 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 t all relevant files at a particular moment. If you have such a "dynamic" script, you don't need t 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 creati 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 wor different blocks at the same time. When your copy command is busy copying the file, those proces changed many blocks all over the file. Actually, we really do not know what has changed at diffe 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 BEGIN BACKUP" comman 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 modifi 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 END BAC 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> SQL> SQL> SQL> SQL> SQL>

alter alter alter alter alter alter

tablespace tablespace tablespace tablespace tablespace tablespace

SYSTEM begin backup; PLANNING_DATA begin backup; PLANNING_INDEX begin backup; USERS begin backup; SYSAUX begin backup; 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> SQL> SQL> SQL> SQL> SQL>

alter alter alter alter alter alter

tablespace tablespace tablespace tablespace tablespace tablespace

SYSTEM begin backup; PLANNING_DATA begin backup; PLANNING_INDEX begin backup; USERS begin backup; SYSAUX begin backup; 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> SQL> SQL> SQL> SQL> SQL>

ALTER ! tar ! tar ! tar ALTER ! tar

DATABASE BEGIN BACKUP; -cvf /dev/rmt/0hc /u01/oradata -rvf /dev/rmt/0hc /u02/oradata -rvf /dev/rmt/0hc /u03/oradata DATABASE END BACKUP; -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: Now we are pretty close to a correct and complete backup script, but what we see above

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 backi 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 con 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 installa 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 di

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> SQL> SQL> SQL> SQL> SQL> SQL> SQL> SQL>

alter ALTER ! tar ! tar ! tar ALTER ! tar ALTER ! Tar

system archive log current; DATABASE BEGIN BACKUP; -cvf /dev/rmt/0hc /u01/oradata -rvf /dev/rmt/0hc /u02/oradata -rvf /dev/rmt/0hc /u03/oradata DATABASE END BACKUP; -rvf /dev/rmt/0hc /u06/archives DATABASE BACKUP CONTROLFILE TO '/u06/backup/cf.bak' REUSE; -rvf /dev/rmt/0hc/ /u06/backup

Or on your Unix, it might look like this:

SQL> SQL> SQL> SQL> SQL> SQL> SQL> SQL> SQL>

alter ALTER ! tar ! tar ! tar ALTER ! tar ALTER ! Tar

system archive log current; DATABASE BEGIN BACKUP; -cvf /dev/rmt0.1 /u01/oradata -rvf /dev/rmt0.1 /u02/oradata -rvf /dev/rmt0.1 /u03/oradata DATABASE END BACKUP; -rvf /dev/rmt0.1 /u06/archives DATABASE BACKUP CONTROLFILE TO '/u06/backup/cf.bak' REUSE; -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> SQL> SQL> SQL> SQL> SQL> SQL> SQL>

alter system archive log current; ALTER DATABASE BACKUP CONTROLFILE TO '/backups/planning/cf.bak' REUSE; ALTER DATABASE BEGIN BACKUP; ! cp /u01/oradata/planning/* /backups/planning ! cp /u02/oradata/planning/* /backups/planning ! cp /u03/oradata/planning/* /backups/planning ALTER DATABASE END BACKUP; ! 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 Although you can manage rman backups and restores via the graphical interface of the Enterprise

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 relea Also, on your Oracle version, rman could have some nasty bugs. It has happened before. Be sure t 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 to avoid using the controlfile as metadata for rman. Even stronger, the controlfile remains Authorative in the sense that this "latest info", and per 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 controlfile. In the controlfile, only the last backups are listed.

server session(s)

target database

target's controlfile

RMAN backupjobs / scripts

rman catalog which is a small Oracle database

(which you want to backup) Remark: should one backup that

backup tapes or

database as well? Yes, an export or cold or hot backup with sufficient frequency should be fi

backup tapes or backup disks

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 command Persistent RMAN configuration settings If we want a RMAN recovery catalog in a database, we follow the following steps: 1. 2. 3. 4. 5.

Create a small database Create a suitable tablespace, e.g. with the name "rman" and, let's say, with a initial size o Create the user "rman" which has as its default tablespace tablespace "rman" Create the catalog on tablespace rman as user rman (use the "CREATE CATALOG" command). 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 50 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 $ rman

(it does not always has to be in capital, maybe your dat (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 p 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

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 RMAN DATA BCB RMAN DATA BCF RMAN DATA BDF RMAN DATA BP RMAN DATA BRL RMAN DATA BS RMAN DATA CCB RMAN DATA CCF RMAN DATA CDF RMAN DATA CKP RMAN DATA CONFIG RMAN DATA DB RMAN DATA DBINC RMAN DATA DF RMAN DATA DFATT RMAN DATA OFFR RMAN DATA ORL RMAN DATA RCVER RMAN DATA RLH RMAN DATA RR RMAN DATA RT RMAN DATA SCR RMAN DATA SCRL RMAN DATA TS RMAN DATA TSATT RMAN DATA XCF RMAN DATA XDF RMAN DATA

RMAN RMAN RMAN RMAN RMAN RMAN RMAN RMAN RMAN RMAN RMAN RMAN RMAN RMAN RMAN RMAN RMAN RMAN RMAN RMAN RMAN RMAN RMAN RMAN RMAN RMAN RMAN RMAN

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 we register To do this, That can be

step we need to register the target database. In the following example the PLANNING database to the catalog. we have to connect to the target database, AND to the catalog. 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 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

5.2

catalog rman/rman@PRODRMAN

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 da and that per default the backups will be stored to disk, and that at every backupjob, the contro 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 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 CONFIGURE SNAPSHOT CONTROLFILE NAME TO 'C:\ORA10G\PRODUCT\10.2.0\DB_1\DATABASE\SNCFTEST10G.ORA';

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 exam RMAN> configure controlfile autobackup on; And you can let rman run a script by starting with "run {" RMAN> run { statements … }

followed by as series of 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 3> allocate channel t2 type 'sbt_tape' parms 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> }

'ENV=(tdpo_optfile=/usr/tivoli/tsm/client/oracle/b 'ENV=(tdpo_optfile=/usr/tivoli/tsm/client/oracle/b

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_t 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 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 a certain configuration file, which will contain pointers on how to access the specilized librar

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/bin6

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 conside 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 ma 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\2 0628T012918_46C1GZMK_.BKP Backup Set 25 28-JUN-08 Backup Piece 27 28-JUN-08 C:\ORACLE\FLASH_RECOVERY_AREA\TEST10G\BACKUPSET\2 0628T012918_46C1KQ3F_.BKP Backup Set 39 28-JUN-08 Backup Piece 43 28-JUN-08 C:\ORACLE\FLASH_RECOVERY_AREA\TEST10G\BACKUPSET\2 0628T013052_46C1KY00_.BKP

Backup Set 40 Backup Piece 44 0628T013052_46C1L26T_.BKP Backup Set 65 Backup Piece 67 0628T013104_46C1L9TC_.BKP Backup Set 89 Backup Piece 94 0628T014827_46C2OXQG_.BKP

28-JUN-08 28-JUN-08

C:\ORACLE\FLASH_RECOVERY_AREA\TEST10G\BACKUPSET\2

28-JUN-08 28-JUN-08


28-JUN-08 28-JUN-08


RMAN>

Note: An obsolete backup differs from an expired backup. An obsolete backup is no longer needed accord to the user's retention policy. An expired backup is a backup that the CROSSCHECK command fails 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 wan 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 availa 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 LI 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 DB Key ------1

Database Incarnations Inc Key DB Name DB ID CUR Reset SCN Reset Time ------- -------- ---------------- --- ---------- ---------2 AIRM 2092303715 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; -- Example 3: Query on database backups

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 . . . .

Backups and proxy copies of a database, tablespace, datafile, archived redo log, or control fi 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 prox 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 verbo generates too much output. Listing To list execute you can

Backups by Backup backups by backup, connect to the target database and recovery catalog (if you use one), the LIST BACKUP command. Specify the desired objects with the listObjList clause. For ex enter:

LIST BACKUP; LIST BACKUPSET; LIST COPY;

# lists backup sets, image copies, and proxy copies # lists only backup sets and proxy copies # 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 Specify the desired objects with the listObjectList or recordSpec clause. If you do not specify then LIST BACKUP displays all backups.

After connecting to the target database and recovery catalog (if you use one), execute LIST BACK 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 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-03022: compiling command: report

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 req for database recoverability. The two major forms of REPORT used to determine whether your databa is recoverable are: RMAN> REPORT NEED BACKUP;

Reports which database files need to be backed up to meet a configured or specified retention po Use the REPORT NEED BACKUP command to determine which database files need backup under a specifi With no arguments, REPORT NEED BACKUP reports which objects need backup under the currently conf 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 op such as a direct-path insert

You can report backup sets, backup pieces and datafile copies that are obsolete, that is, not ne to meet a specified retention policy, by specifying the OBSOLETE keyword. If you do not specify other options, then REPORT OBSOLETE displays the backups that are obsolete according to the curr 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 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; delete 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; report unrecoverable tablespace 'USERS';

cle Databases 9i, 10g, 11g

12

-

30/09/2008

ery, and is by no means a complete description.

of Oracle Databases, but who need an overview

there are differences between those versions, but with not too big . But please be aware of the fact that 11g has some

to create backups". rman backup and recovery scripts. hich dictionary views (of the Target DB) you can query on backups and recovery. ou some important problems from which you may recover.

n a database backup?

an make of an Oracle database:

atabase files to tape(s) or backup disk(s). of the contents of the database (e.g. tables, e or more file(s).

do both types of backups, whereas the "physical backup" ckup you probably will use in case of disaster recovery. and can be used as some sort of "last resort", or as

equently. We will use an example of an simple ple, contains of a small number of database files. database, could be as in the following example.

st the Oracle Software. s are "static" and remains nless you to plan pgrade, or install a patch.

default installation, also can be found here, for e "listener.log", but those ot directly critical for the operation

mple, the Oracle software led in /opt/ora10g/product/10g tly, this could also have been th, like e.g. /home/oracle or /u01/ora10g

usually stored in

udump", "cdump", "audit"

file "init.ora" or kinds of settings ow many processes

creating a copy of the files in section II, that is, know what exactly you need to backup

tabase shown in II. blespace has no meaning to the OS,

ain, and neat administration.

multiple files, over multiple filesystems.

t to let it have two files:

s, over two separate filessystems /u01 and /u02. "live" in tablespace PLANNING_DATA:

ionally, if the table gets very large ent filesystems (assuming those are on separate disks).

le "planning_data01.dbf",

rformance reasons, but also for Administration purposes. s and indexes on separate tablespaces? database, and you would create a neat administration

etween a physical backup and a logical backup of a database. socalled "open" backup, that is, creating a backup ere the database is shutdown.

ool to backup a database. p an Oracle database:

sysadmin is using Tivoli TSM (or other backup suite)

d-on product TDPO, so that (open) Oracle databases

! That's not true. Almost all red for making Oracle backups as well, diately obvious that really rman

Do NOT take these as "live" or "usable" examples, abase in archive mode and in non-archive mode.

ples: (do not takes these for as "being usable"):

process is acting on the databasefiles. They are just a bunch e we COULD NOT use the commands as shown in the examples.

# # # # #

tar tar tar tar tar




tapedrive or class is called..

If the database was open, and thus in use, are accessed by potentially many

pedrive on rmt1, in contrast with "rmt1", which is rewinding you will only have the last file on tape! ike "cp with using ranges with brackets []

requirement if we want to use the "exp" or "expdp" uitilites. indexes, procedures and all other fancy stuff) ed if we want to restore the whole database, or

y log=planning_06062008.log

the database. In this example, we backup all of Harry):

read as "account/password@connectstring" ers in the database. ers, namely "sys" and "system", whereas the "sys" account ect full dabase exports using system or sys.

from version 10g, Oracle strongly encourages all dba's he exp utility).

umpfile=EMP_DEPT.dmp logfile=expdpEMP_DEPT.log

he other options that are available to you. roduced as in the following example,

cause we still need to explain rman.

n will figure out where to store backups, and it will backups (saving space on disk or tape).

o make correct backups, and how to restore them.

need to know what the differences are between in archive mode.

, you might risk loss of data..

ve a few files. But Oracle databases might contain hundreds

nt to contain the application tables. nt to contain indexes (of the application tables). dictionary (metadata) o data for transactional consistency emporary data from querys that produces sorts we do not consider in this note. Contains no data of the application, but its more tool oriented.

tabase: these are "standard files", like the dictionary "system.dbf", tabledata if users do DML (insert, update, delete) on tables. g_data01.dbf" and "planning_index01.dbf",

hree of those files, but there could be more of them.

purpose: they are "logs" to make the "redo"

(s). These changes are captured in a small buffer action, those changes are immediately written to

written to the true database file(s),

es" of changes: First a process (logwriter) puts the changes nother process (db writer) writes the changes

cess writing changes. Ofcourse, there is a little

hanges captured.When the database starts up again, ans that all uncommited changes will rollback, but asefiles) will be redone (redone).

after the other, that is, if redo01.dbf gets full, e system will use redo03.dbf. ther choice than to return to redo01.dbf mewhat "circular", that is: use the logs redolog and start using that one again. if database is in archive mode

archived archived redolog archived redolog redolog archived redolog

the time. (If you follow Oracle's advice, has a mirror on another filesystem).

we place it in archive mode, Oracle will use happens in archive mode: if the system needs FILE AND STORE THAT COPY IN SOME DESIGNATED PLACE. ually called "archive log destination",

edo02.dbf, redo03.dbf) but because copies ory of all changes that occurred on the database. y needs to take care of those archive logs,

s an RMAN script, and notice that it also backups the implementing some automatic maintenance on the archived logs.

(tdpo_optfile=/usr/tivoli/tsm/client/oracle/bin64/tdpo.opt)'; (tdpo_optfile=/usr/tivoli/tsm/client/oracle/bin64/tdpo.opt)';

ot about time, but it‟s about a number quely identify any change in the database. s a row in or from a table, this

ntified by" a certain SCN.

", then the checkpoint process will

in, the Oracle processes will check the SCN needed on one or more files. ery is needed at startup.

(that is probably the account that also owns the software). em, its very likely you need to export a variable ), like for example in the following way:

PLANNING database. to do that. What you could do is illustrated

$ sqlplus username/password (you already are connected or logged on)

have been logged on as an Orcale database r stop a database. database like in "connect / as sysdba" n to the database as "sys". here is how to show you to stop or start a database.

inished which could mean that you might wait always the best to use, because running transactions s, in a consistent way. se, the fileheaders have not been updated with

u can easily startup a database with

_path_to_parameterfile_init.ora"

if you need to explicitly startup a database with a certain or default init_.ora

sing the rman utility.

cussing how to make a backup using OS commands. ll in use by some dba's.

iles. If you have shutdown the database in a clean way, all files are

not. Just use your favourite command to copy the files ANNING database:

# # # # #

tar tar tar tar tar




apedrive or class.

the archived logfiles. e. Suppose some sort of maintenance e of days are in (for example)

ript, possibly in a dynamic form that might discover "dynamic" script, you don't need to adjust it all the time

of the relevant databasefiles via:

E. It is absolutely of no use

f diskpages. At the time of creation 4K, 8K, 16K, 32K etc.. mand, many process could be at work on many usy copying the file, those processes could have not know what has changed at different parts totally inconsistent.

nform" Oracle about your plans

e_space_name> BEGIN BACKUP" command,

insure that your OS copy command

gs if such a block is being modified while

e to place the tablespaces in LESPACE END BACKUP". is running in archive mode)?

abase files resides: associated tablespace PLANNING_DATA PLANNING_INDEX USERS SYSTEM SYSAUX TEMP UNDO

its running, goes as shown below.

o your backup commands:

ding them with the "!" character.

u are going to use OS commands isting, which has the same

t, but what we see above

esystems. Why? Is it not

ACE.." or "ALTER DATABASE.." tablespaces, but there's aces). Actually, when we are backing up the tablespaces, OS command is not the way

if we need to recover

mands in our backup script. e archive destination, we produce

e have all the latest transactions

transactions in the redologs

redologs. What is left are the controlfiles. dled with an OS command.

s. The controlfile only contains database as well as some e contains the real metadata adata, such as which files jects are present, which user

e tablespaces, it needs a (small)

s are just safety copies which the

r she wants. In a default installation, ckup the controlfile, you only

rored control files on separate disks

rite one that currently exists.)

ands to create a backup

address the tapedevice. the database to a dumpdisk:

not change frequently. ehaviour of your Instance, processes may be concurrent. d you make sure that you ts missing), then all is fine. backupscript that copies make a open backup of a running

how to backup an Oracle database

ct of backup an restore.

of them on disk

/archives/today' ript. We assume here other will be good as well.

hical interface of the Enterprise Manager,

ive lists and reports. kup of files, compared granular metric.

have in using rman. fixes and patchsets will be released regularly. It has happened before. Be sure to check the most important bugs

ating the catalog.

th multiple databases, possibly

many databases, rman needs are associated to which database, full information.

e which rman will backup)

equired. So you could use hat database is an important just for holding rman's metadata, (which does not need to be catalog, much more historical

used, or a separate database ase is present, the controlfiles ackup information, so its actually impossible

e that this "latest info", and periodically that data

e types of data, then the target's database

metadata: what kind of backup, status, date/time etc.. The "real" data rman catalog which is a small Oracle database Remark: should one backup that

periodic synchronization

database as well? Yes, an export or cold or hot backup with sufficient frequency should be fine.

-created sequences of RMAN commands

e following steps:

, let's say, with a initial size of 100MB. tablespace "rman" "CREATE CATALOG" command).

autoextend on next 10M maxsize 500M

that is, the user rman:

to the right Database:

s to be in capital, maybe your database id uses small capital).

e "connect catalog" command, and pass along

DRMAN, and those objects live

store the metadata.

lso retrieve all kinds tables and views). ews. Just use the

he following example

s (like the name and dbid) in the catalog.

its much better to set, or store, ly we want to backup.

log database in one command, in the following example:

me options in your scripts a configurable setting.

ckups that are not older than 5 days, hat at every backupjob, the controlfile

if you inderstand

command tells rman

abase to any point in time in the last 7 days:

O '%F'; # default

.0\DB_1\DATABASE\SNCFTEST10G.ORA'; # default

complete (open) backup

", the script will execute. t is, one statement, like for example:

llowed by as series of statements.

nder why there is no specification between tape or disk.

"channel" is a Server session he data to the backuplocation. e for backups to disk.

s the database to tape(s).

le=/usr/tivoli/tsm/client/oracle/bin64/tdpo.opt)'; le=/usr/tivoli/tsm/client/oracle/bin64/tdpo.opt)';

the actual backupcommands statement you might see the "sbt_tape"

osts Operating System lt" parameters in the

ecific third-party software. ameters that referr to ow to access the specilized libraries.

similar to this example:

/usr/tivoli/tsm/client/oracle/bin64/tdpo.opt)';

kup, so that it no longer

ckup should be subject to the even indefinitely.

licy either indefinitely or until lete even if they would be considered ong-term backups. EP, so that the configured

ing backupsets with the tag

obsolete based

restore and recovery operations, he records for these backups ore and recovery until they are marked

which are "after" the

ted as obsolete.

_RECOVERY_AREA\TEST10G\BACKUPSET\2008_06_28\O1_MF_NNNDF_TAG2008

_RECOVERY_AREA\TEST10G\BACKUPSET\2008_06_28\O1_MF_NCSNF_TAG2008

_RECOVERY_AREA\TEST10G\BACKUPSET\2008_06_28\O1_MF_NCNNF_TAG2008

_RECOVERY_AREA\TEST10G\BACKUPSET\2008_06_28\O1_MF_NNSNF_TAG2008

_RECOVERY_AREA\TEST10G\BACKUPSET\2008_06_28\O1_MF_ANNNN_TAG2008

_RECOVERY_AREA\TEST10G\BACKUPSET\2008_06_28\O1_MF_NCSNF_TAG2008

backup is no longer needed according that the CROSSCHECK command fails to find

ps. They only fall outside your backups. Make sure you really want this,

which backups or copies are available.

CKUP and BY FILE options of the LIST command

backups are available. For example, you can list:

, archived redo log, or control file

serially lists each backup or proxy copy also list backups by file.

ups in a summary mode if the verbose mode

recovery catalog (if you use one), and then ith the listObjList clause. For example,

fy that RMAN display the output in summarized form. pec clause. If you do not specify an object,

if you use one), execute LIST BACKUP,

kups that were not found during a crosscheck:

rategy is in fact meeting your requirements d to determine whether your database

nfigured or specified retention policy files need backup under a specific retention policy. ed backup under the currently configured retention policy. s similar to this example:

been affected by some NOLOGGING operation

that are obsolete, that is, not needed TE keyword. If you do not specify any are obsolete according to the current

, tape or both, using any one

the database files.

log (if you use one), issue REPORT SCHEMA

ention policies,

ts more tool oriented.

Examples and notes on rman See Sheet 4 "SomeDisasterScenarios" for complete examples.

Section 1. Good Examples using RMAN on 9i or 10g or 11g: >>>> Full Backup 1. A good rman script that will backup your database and archived logs, and that will work in most is the following. But you must also read note 2 for an enhancement of this script. RMAN> run { 2> allocate channel t1 type disk; 3> backup full database ; 4> backup (spfile) (current controlfile) ; 5> sql 'alter system archive log current'; 6> backup archivelog all delete input ; 7> release channel t1; 8> }

Note 1: If you backup to tape, your allocate channel command would probably look similar to the following example: allocate channel t1 type 'sbt_tape' parms 'ENV=(tdpo_optfile=/usr/tivoli/tsm/client/oracle/bin6

Note 2: IMPORTANT: Instead that you explicitly list the "backup current controlfile" command in upper sc to have set as a persistent configuration parameter: RMAN> configure controlfile autobackup on; REASON: This will ensure that you always have an up to date controlfile available that has been taken at the end of the current backup not during.

>>>> Full Backup 2. Creating a full backup can even be as simple as in the following example. First we configure several persistant parameters for this instance: RMAN> RMAN> RMAN> RMAN>

configure configure configure configure

retention policy to recovery window of 5 days; default device type to disk; controlfile autobackup on; channel device type disk format 'C:\Oracle\Admin\W2K2\Backup%d_DB_%u_%s_%p';

Next we perform a complete database backup using a single command: RMAN> run { 2> backup database plus archivelog; 3> delete noprompt obsolete; 4> }

The recovery catalog should be resyncronized on a regular basis so that changes to the database and presence of new archive logs is recorded. Some commands perform partial and full resyncs imp but if you are in doubt you can perform a full resync using the follwoing command: RMAN> resync catalog; >>>> Restore & Recover The Whole Database

If the controlfiles and online redo logs are still present a whole database recovery can be achi by running the following script:

run { shutdown immediate; # use abort if this fails startup mount; restore database; recover database; alter database open; }

This will result in all datafiles being restored then recovered. RMAN will apply archive logs as necessary until the recovery is complete. At that point the database is opened. If the tempfiles are still present you can issue a command like like the following for each of t sql "ALTER TABLESPACE temp ADD TEMPFILE ''C:\Oracle\oradata\W2K2\temp01.dbf'' REUSE"; If the tempfiles are missing they must be recreated as follows: sql "ALTER TABLESPACE temp ADD TEMPFILE ''C:\Oracle\oradata\W2K2\temp01.dbf'' SIZE 100M AUTOEXTEND ON NEXT 64K";

>>>> Restore & Recover A Subset Of The Database

A subset of the database can be restored in a similar fashion: Suppose you have placed tablespace users offline, and want to restore and recover this tablespac run { allocate channel t1 type 'sbt_tape' parms allocate channel t2 type 'sbt_tape' parms restore tablespace users; recover tablespace users; sql 'ALTER TABLESPACE users ONLINE'; }

'ENV=(tdpo_optfile=/usr/tivoli/tsm/client/oracle/bin6 'ENV=(tdpo_optfile=/usr/tivoli/tsm/client/oracle/bin6

run { sql 'ALTER TABLESPACE users OFFLINE IMMEDIATE'; restore tablespace users; recover tablespace users; sql 'ALTER TABLESPACE users ONLINE'; }

Recovering a Tablespace in an Open Database The following example takes tablespace TBS_1 offline, restores and recovers it, then brings it b run { allocate channel dev1 type 'sbt_tape'; sql "ALTER TABLESPACE tbs_1 OFFLINE IMMEDIATE"; restore tablespace tbs_1; recover tablespace tbs_1; sql "ALTER TABLESPACE tbs_1 ONLINE"; }

Recovering Datafiles Restored to New Locations The following example allocates one disk channel and one media management channel to use datafil on disk and backups on tape, and restores one of the datafiles in tablespace TBS_1 to a differen run { allocate channel dev1 type disk; allocate channel dev2 type 'sbt_tape'; sql "ALTER TABLESPACE tbs_1 OFFLINE IMMEDIATE";

set newname for datafile 'disk7/oracle/tbs11.f' to 'disk9/oracle/tbs11.f'; restore tablespace tbs_1; switch datafile all; recover tablespace tbs_1; sql "ALTER TABLESPACE tbs_1 ONLINE"; }

Use the RESTORE DATAFILE and RECOVER DATAFILE commands on individual current datafiles when the Take the datafile that needs recovery offline, restored recover the datafile, and bring the data For example, to restore and recover datafile 7: RMAN> SQL 'ALTER DATABASE DATAFILE 7 OFFLINE'; RESTORE DATAFILE 7; RECOVER DATAFILE 7; SQL 'ALTER DATABASE DATAFILE 7 ONLINE'; >>>> Example backup with channels / server sessions using sbt_tape: echo " run { allocate channel t1 type 'sbt_tape' parms allocate channel t2 type 'sbt_tape' parms

'ENV=(tdpo_optfile=/usr/tivoli/tsm/clie 'ENV=(tdpo_optfile=/usr/tivoli/tsm/clie

backup full database ; backup (spfile) (current controlfile) ; sql 'alter system archive log current'; backup archivelog all delete input ; release channel t1; release channel t2; }

>>>> Incomplete Recovery

As you would expect, RMAN allows incomplete recovery to a specified time, SCN or sequence number run { shutdown immediate; startup mount; set until time 'Nov 15 2000 09:00:00'; # set until scn 1000; # alternatively, you can specify SCN # set until sequence 9923; # alternatively, you can specify log sequence number restore database; recover database; alter database open resetlogs; } The incomplete recovery requires the database to be opened using the RESETLOGS option.

>>>> Disaster Recovery

In a disaster situation where all files are lost you can only recover to the last SCN in the arc Beyond this point the recovery would have to make reference to the online redo logs which are no Disaster recovery is therefore a type of incomplete recovery. To perform disaster recovery conne C:>rman catalog=rman/rman@w2k1 target=sys/password@w2k2 Once in RMAN do the following:

startup nomount; restore controlfile; alter database mount; From SQL*Plus as SYS get the last archived SCN using: SQL> SELECT archivelog_change#-1 FROM v$database; ARCHIVELOG_CHANGE#-1 -------------------1048438 1 row selected. SQL>Back in RMAN do the following: run { set until scn 1048438; restore database; recover database; alter database open resetlogs; }

If the "until scn" were not set the following type of error would be produced once a redo log wa RMAN-00571: =========================================================== RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS =============== RMAN-00571: =========================================================== RMAN-03002: failure of recover command at 03/18/2003 09:33:19 RMAN-06045: media recovery requesting unknown log: thread 1 scn 1048439 With the database open all missing tempfiles must be replaced: sql "ALTER TABLESPACE temp ADD TEMPFILE ''C:\Oracle\oradata\W2K2\temp01.dbf'' SIZE 100M AUTOEXTEND ON NEXT 64K"; Once the database is fully recovered a new backup should be perfomed.

The recovered database will be registered in the catalog as a new incarnation. The current incar can be listed and altered using the following commands:

list incarnation; reset database to incarnation x;Lists And Reports RMAN has extensive listing and reporting functionality allowing you to monitor you backups and m the recovery catalog. >>>> Restoring a datafile to another location:

For example, if you restore datafile ?/oradata/trgt/tools01.dbf to its default location, then RM the file ?/oradata/trgt/tools01.dbf and overwrites any file that it finds with the same filename If you run a SET NEWNAME command before you restore a file, then RMAN creates a datafile copy with the name that you specify. For example, assume that you run the following commands: SET NEWNAME FOR DATAFILE '?/oradata/trgt/tools01.dbf' TO '/tmp/tools01.dbf'; RESTORE DATAFILE '?/oradata/trgt/tools01.dbf';

In this case, RMAN creates a datafile copy of ?/oradata/trgt/tools01.dbf named /tmp/tools01.dbf and records it in the repository. To change the name for datafile ?/oradata/trgt/tools01.dbf to /tmp/tools01.dbf in the control fi run a SWITCH command so that RMAN considers the restored file as the current database file. For SWITCH DATAFILE '/tmp/tools01.dbf' TO DATAFILECOPY '?/oradata/trgt/tools01.dbf';

The SWITCH command is the RMAN equivalent of the SQL statement ALTER DATABASE RENAME FILE. >>>> Archive logs What is the purpose and are the differences of “ALTER SYSTEM ARCHIVE LOG CURRENT” and “ALTER SYSTEM ARCHIVE LOG ALL” # When the database is open, run the following SQL statement to force Oracle to switch out of the current log and archive it as well as all other unarchived logs: ALTER SYSTEM ARCHIVE LOG CURRENT;

# When the database is mounted, open, or closed, you can run the following SQL statement to forc to archive all noncurrent redo logs: ALTER SYSTEM ARCHIVE LOG ALL;

A log switch does not mean that the redo is archived. When you execute "'alter system archive log current" you force that the current log to be archiv so it is safe: you are sure to have all the needed archived logs. alter system archive log all: This command will archive all filled redo logs but will not complete current log because it will not be full.

Section 2: Other notes on RMAN About RMAN Backups: ----------------------------

When you execute the BACKUP command in RMAN, you create one or more backup sets or image copies. RMAN creates backup sets regardless of whether the destination is disk or a media manager. >>>About Image Copies

An image copy is an exact copy of a single datafile, archived redo log file, or control file. Image copies are not stored in an RMAN-specific format. They are identical to the results of cop with operating system commands. RMAN can use image copies during RMAN restore and recover operat and you can also use image copies with non-RMAN restore and recovery techniques.

To create image copies and have them recorded in the RMAN repository, run the RMAN BACKUP AS COP (or, alternatively, configure the default backup type for disk as image copies using CONFIGURE DEVICE TYPE DISK BACKUP TYPE TO COPY before performing a backup). A database server session is used to create the copy, and the server session also performs actio validating the blocks in the file and recording the image copy in the RMAN repository.

You can also use an operating system command such as the UNIX dd command to create image copies, though these will not be validated, nor are they recorded in the RMAN repository. You can use th to add image copies created with native operating system tools in the RMAN repository. >>>Using RMAN-Created Image Copies

If you run a RESTORE command, then by default RMAN restores a datafile or control file to its or by copying an image copy backup to that location. Image copies are chosen over backup sets becau extra overhead of reading through an entire backup set in search of files to be restored.

However, if you need to restore and recover a current datafile, and if you have an image copy of available on disk, then you do not actually need to have RMAN copy the image copy back to its ol

You can instead have the database use the image copy in place, as a replacement for the datafile The SWITCH command updates the RMAN repository indicate that the image copy should now be treate the current datafile. Issuing the SWITCH command in this case is equivalent to issuing the SQL s ALTER DATABASE RENAME FILE. You can then perform recovery on the copy. >>>User-Managed Image Copies

RMAN can use image copies created by mechanisms outside of RMAN, such as native operating system or third-party utilities that leave image copies of files on disk. These copies are known as use or operating system copies.

The RMAN CATALOG command causes RMAN to inspect an existing image copy and enter its metadata in Once cataloged, these files can be used like any other backup with the RESTORE or SWITCH command

Some sites store their datafiles on mirrored disk volumes, which permit the creation of image co a mirror. After you have broken the mirror, you can notify RMAN of the existence of a new user-m thus making it a candidate for a backup operation. You must notify RMAN when the copy is no long by using the CHANGE ... UNCATALOG command. In this example, before resilvering the mirror (not i copies of the broken mirror), you must use a CHANGE ... UNCATALOG command to update the recovery and indicate that this copy is no longer available. >>>Storage of Backups on Disk and Tape

RMAN can create backups on disk or a third-party media device such as a tape drive. If you speci DEVICE TYPE DISK, then your backups are created on disk, in the file name space of the target in that is creating the backup. You can make a backup on any device that can store a datafile.

To create backups on non-disk media, such as tape, you must use third-party media management sof and allocate channels with device types, such as SBT, that are supported by that software. >>>Backups of Archived Logs There are several features of RMAN backups specific to backups of archived redo logs.

Deletion of Archived Logs After Backups RMAN can delete one or all copies of archived logs from disk after backing them up to backup set If you specify the DELETE INPUT option, then RMAN backs up exactly one copy of each specified lo and thread from an archive destination to tape, and then deletes the specific file it backed up the other copies on disk. If you specify the DELETE ALL INPUT option, then RMAN backs up exactly specified log sequence number and thread, and then deletes that log from all archive destination Note that there are special considerations related to deletion of archived redo logs in standby See Oracle Data Guard Concepts and Administration for details. >>>Backups of Backup Sets

The RMAN BACKUP BACKUPSET command backs up previously created backup sets. Only backup sets that on device type DISK can be backed up, and they can be backed up to any available device type. Note: RMAN issues an error if you attempt to run BACKUP AS COPY BACKUPSET.

The BACKUP BACKUPSET command uses the default disk channel to copy backup sets from disk to disk To back up from disk to tape, you must either configure or manually allocate a non-disk channel.

Uses for Backups of Backup Sets The BACKUP BACKUPSET command is a useful way to spread backups among multiple media. For example you can execute the following BACKUP command weekly as part of the production backup schedule: # makes backup sets on disk BACKUP DEVICE TYPE DISK AS BACKUPSET DATABASE PLUS ARCHIVELOG; BACKUP DEVICE TYPE sbt BACKUPSET ALL; # copies backup sets on disk to tape

In this way, you ensure that all your backups exist on both disk and tape. You can also duplex b of backup sets, as in this example:

BACKUP COPIES 2 DEVICE TYPE sbt BACKUPSET ALL; (Again, control file autobackups are never duplexed.)

You can also use BACKUP BACKUPSET to manage backup space allocation. For example, to keep more r on disk and older backups only on tape, you can regularly run the following command: BACKUP DEVICE TYPE sbt BACKUPSET COMPLETED BEFORE 'SYSDATE-7' DELETE INPUT;

This command backs up backup sets that were created more than a week ago from disk to tape, and them from disk. Note that DELETE INPUT here is equivalent to DELETE ALL INPUT; RMAN deletes all existing copies of the backup set. If you duplexed a backup to four locations, then RMAN deletes all four copies of the pieces in the backup set. >>> Restoring Files with RMAN Use the RMAN RESTORE command to restore the following types of files from disk or other media: =====-

Database (all datafiles) Tablespaces Control files Archived redo logs Server parameter files

Because a backup set is in a proprietary format, you cannot simply copy it as you would a backup created with an operating system utility; you must use the RMAN RESTORE command to extract its c In contrast, the database can use image copies created by the RMAN BACKUP AS COPY command without additional processing.

RMAN automates the procedure for restoring files. You do not need to go into the operating syste locate the backup that you want to use, and manually copy files into the appropriate directories When you issue a RESTORE command, RMAN directs a server session to restore the correct backups t =- the default location, overwriting the files with the same name currently there =- A new location, which you can specify with the SET NEWNAME COMMAND

To restore a datafile, either mount the database or keep it open and take the datafile to be res When RMAN performs a restore, it creates the restored files as datafile image copies and records in the repository. The following table describes the behavior of the RESTORE, SET NEWNAME, and S >>>Datafile Media Recovery with RMAN The concept of datafile media recovery is the application of online or archived redo logs or incremental backups to a restored datafile in order to update it to the current time or some other specified time. Use the RMAN RECOVER command to perform media recovery and apply logs or incremental backups automatically.

RMAN Media Recovery: Basic Steps If possible, make the recovery catalog available to perform the media recovery. If it is not ava or if you do not maintain a recovery catalog, then RMAN uses metadata from the target database c If both the control file and recovery catalog are lost, then you can still recover the database assuming that you have backups of the datafiles and at least one autobackup of the Control file. The generic steps for media recovery using RMAN are as follows:

-Place the Database in the appropriate state: mounted or open. For Example, mount the Database when performing whole database recovery, or open the database when performing online tablespace -To perform incomplete recovery, use the SET UNTIL command to specify the time, SCN, or log sequence number at which recovery terminates. Alternatively, specify the UNTIL clause on the RESTORE and RECOVER commands. -Restore the necessary files with the Restore command. -Recover the datafiles with the Recover command. -Place the Database in its normal state. For Example, open it or bring recovered Tablespaces onl RESTORE DATABASE;

RECOVER DATABASE; >>> Corrupt Block recovery

Although datafile media recovery is the principal form of recovery, you can also use the RMAN BL command to perform block media recovery. Block media recovery recovers an individual corrupt dat or set of datablocks within a datafile. In cases when a small number of blocks require media rec you can selectively restore and recover damaged blocks rather than whole datafiles. For example, you may discover the following messages in a user trace file: ORA-01578: ORA-01110: ORA-01578: ORA-01110:

ORACLE data block corrupted (file # 7, block # 3) data file 7: '/oracle/oradata/trgt/tools01.dbf' ORACLE data block corrupted (file # 2, block # 235) data file 2: '/oracle/oradata/trgt/undotbs01.dbf'

You can then specify the corrupt blocks in the BLOCKRECOVER command as follows: BLOCKRECOVER DATAFILE 7 BLOCK 3 DATAFILE 2 BLOCK 235; >>> After a Database Restore and Recover, RMAN gives the error:

RMAN-00571: =========================================================== RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS =============== RMAN-00571: =========================================================== RMAN-03002: failure of backup command at 03/03/2008 11:13:06 RMAN-06059: expected archived log not found, lost of archived log compromises recoverability ORA-19625: error identifying file /dbms/tdbaeduc/educroca/recovery/archive/arch_1_870_6171166 ORA-27037: unable to obtain file status IBM AIX RISC System/6000 Error: 2: No such file or directory Note 1: If you no longer have a particular archivelog file you can let RMAN catalog know this by issuing the following command at the rman prompt after connecting to the rman catalog and the target database RMAN> change archivelog all crosscheck ; This will check the archivelog folder and then make the catalog agree with what is actually available. rman> DELETE EXPIRED ARCHVIELOG ; Oracle Error :: RMAN-20011 target database incarnation is not current in recovery catalog

Cause the database incarnation that matches the resetlogs change# and time of the mounted target datab control file is not the current incarnation of the database

Action If "reset database to incarnation " was used to make an old incarnation current then restor target database from a backup that matches the incarnation and mount it. You will need to do "st before you can restore the control file using RMAN. Otherwise use "reset database to incarnation " make the intended incarnation current in the >>> Note about rman and tape sbt and recovery window:

Suppose you have a retention period defined in rman, like for example CONFIGURE RETENTION POLICY TO REDUNDANCY 3 This means that 3 backups needs to be maintained by rman, and other backups are considered "obso But those other backups beyond retention, are not expired or otherwise not usable. If they are still present, you can use them in a recovery. Besides this, it cannot be known beforehand how the tape subsystem will deal with rman commands like "delete obsolete". The tape subsystem has probably its own retention period, and you need much more details about all systems involved, before you know whats going on.

Section 3: RMAN errors and troubleshooting: Err 1: Missing archived redolog: ================================ Problem: If an archived redo is missing, you might get a message similar like this:

RMAN-00571: =========================================================== RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS =============== RMAN-00571: =========================================================== RMAN-03002: failure of backup command at 03/05/2008 07:44:35 RMAN-06059: expected archived log not found, lost of archived log compromises recoverability ORA-19625: error identifying file /dbms/tdbaeduc/educroca/recovery/archive/arch_1_817_6171166 ORA-27037: unable to obtain file status IBM AIX RISC System/6000 Error: 2: No such file or directory Solution: If archived redo logs are (wrongly) deleted/moved/compressed from disk without being backed up, this has happened, and will keep attempting to backup the missing archived redo logs. That will to fail altogether with an error like: RMAN-06059: expected archived log not found, lost of archived log compromises recoverability

If you can, you should bring back the missing archved redo logs to their original location and n But if that is impossible, the workaround is to “crosscheck archivelog all”, like: rman < crosscheck archivelog all; You‟ll get output like this:

validation succeeded for archived log archive log filename=D:REDOARCHARCH_1038.DBF recid=1017 st for every archived log as they are all checked on disk. That should be the catalog fixed, run an archivelog backup to make sure.

Err 2: online redo logs listed as archives: =========================================== Testcase: a 10g 10.2.0.3 shows after recovery with resetlogs the following in v$archived_log. It looks as if it will stay there forever: SEQ#

1

FIRST 814 1.7E+07 815 1.7E+07 816 1.7E+07 817 1.7E+07 2 1.7E+07 0 0 0 0 0 0 -->17402532 1.7E+07 1 1.7E+07 2 1.7E+07

NEXT NAME DIFF 1.7E+07 12 1.7E+07 42877 1.7E+07 12 1.7E+07 47857 ####### redo01.log 2.8147E+14 A 0 redo02.log ####### 0 redo03.log ####### 0 redo04.log ####### redo05.log 12755 A 1.7E+07 1622 1.7E+07 11

STATUS D D D D

D D

FIRST_CHANGE# NEXT_CHANGE# SEQUENCE# RESETLOGS_CHANGE# ------------- ------------ ---------- ----------------17311785 17354662 815 1 17354662 17354674 816 1 17354674 17402531 817 1 -->17402532 17404154 1 -->17402532 17404154 17404165 2 17402532 17404165 17415733 3 17402532 We dont know what is going on here.

Err 3: Highlevel overview RMAN Error Codes ========================================== RMAN error codes are summarized in the table below. 0550-0999 Command-line interpreter 1000-1999 Keyword analyzer 2000-2999 Syntax analyzer 3000-3999 Main layer 4000-4999 Services layer 5000-5499 Compilation of RESTORE or RECOVER command 5500-5999 Compilation of DUPLICATE command 6000-6999 General compilation 7000-7999 General execution 8000-8999 PL/SQL programs 9000-9999 Low-level keyword analyzer 10000-10999 Server-side execution 11000-11999 Interphase errors between PL/SQL and RMAN 12000-12999 Recovery catalog packages 20000-20999 Miscellaneous RMAN error messages

Err 4: RMAN-03009 accompinied with ORA- error: ==============================================

Q: Here is my problem; When trying to delete obsolete RMAN backupsets, I get an error: RMAN> change backupset 698, 702, 704, 708 delete; List of BP Key ------698 702 704 708

Backup Pieces BS Key Pc# Cp# ------- --- --698 1 1 702 1 1 704 1 1 708 1 1

Status ----------AVAILABLE AVAILABLE AVAILABLE AVAILABLE

Device Type ----------SBT_TAPE SBT_TAPE SBT_TAPE SBT_TAPE

Piece Name ---------df_546210555_706_1 df_546296605_709_1 df_546383776_712_1 df_546469964_715_1

Do you really want to delete the above objects (enter YES or NO)? YES RMAN-00571: =========================================================== RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS =============== RMAN-00571: =========================================================== RMAN-03009: failure of delete command on ORA_MAINT_SBT_TAPE_1 channel at ####### ORA-27191: sbtinfo2 returned error Additional information: 2 What in the world does "Additional information: 2" mean? I can't find any more useful detail than this. A: Oracle Error :: ORA-27191 sbtinfo2 returned error

Cause sbtinfo2 returned an error. This happens while retrieving backup file information from the media

Action This error is returned from the media management software which is linked with Oracle. There sho which explain the cause of the error. This error usually requires contacting the media managemen A: ---> ORA-27191 John Clarke: My guess is that "2" is an O/S return code, and in /usr/sys/include/errno.h, you'll see that error# 2 is "no such file or directory. Accompanied with ORA-27191, I'd guess that your problem is that your tape library doesn't currently have the tape(s) loaded and/or can't find them. Mladen Gogala: Additional information 2 means that OS returned status 2. That is a file not found error. In plain Spanglish, you cannot delete files from tape, only from the disk drives. Niall Litchfield: The source error is the ora-27191 error (http://download-west.oracle.com/docs/cd/B14117_01/server.101/b10744/e24280.htm#ORA-27191) which suggests a tape library issue to me. You can search for RMAN errors using the error search page as well http://otn.oracle.com/pls/db10g/db10g.error_search?search=rman-03009, for example A:

---> RMAN-03009 RMAN-03009: failure of delete command on ORA_MAINT_SBT_TAPE_1 channel at date/time RMAN-03009: failure of allocate command on t1 channel at date/time RMAN-03009: failure of backup command on t1 channel at date/time etc..

-> Means most of the time that you have Media Management Library problems -> Can also mean that there is a problem with backup destination (disk not found, no space, tape

ERR 5: Test your Media Management API: ======================================

Testing the Media Management API On specified platforms, Oracle provides a diagnostic tool called "sbttest". This utility perform tape library by acting as the Oracle database server and attempting to communicate with the medi Obtaining the Utility On UNIX, the sbttest utility is located in $ORACLE_HOME/bin. Obtaining Online Documentation For online documentation of sbttest, issue the following on the command line: % sbttest The program displays the list of possible arguments for the program: Error: backup file name must be specified Usage: sbttest backup_file_name # this is the only required parameter <-dbname database_name> <-trace trace_file_name> <-remove_before> <-no_remove_after> <-read_only> <-no_regular_backup_restore> <-no_proxy_backup> <-no_proxy_restore> <-file_type n> <-copy_number n> <-media_pool n> <-os_res_size n> <-pl_res_size n> <-block_size block_size> <-block_count block_count> <-proxy_file os_file_name bk_file_name [os_res_size pl_res_size block_size block_count]>

The display also indicates the meaning of each argument. For example, following is the descripti for two optional parameters: Optional parameters: -dbname specifies the database name which will be used by SBT to identify the backup file. The default is "sbtdb" -trace specifies the name of a file where the Media Management software will write diagnostic messages.

Using the Utility Use sbttest to perform a quick test of the media manager. The following table explains how to in If sbttest returns...

Then...

0 The program ran without error. In other words, the media manager is installed and can accept a return the same data when requested.

non-0 The program encountered an error. Either the media manager is not installed or it is not config To use sbttest:

Make sure the program is installed, included in your system path, and linked with Oracle by typi % sbttest If the program is operational, you should see a display of the online documentation. Execute the program, specifying any of the arguments described in the online documentation. For to create test file some_file.f and write the output to sbtio.log: % sbttest some_file.f -trace sbtio.log

You can also test a backup of an existing datafile. For example, this command tests datafile tbs % sbttest tbs_33.f -dbname prod

Examine the output. If the program encounters an error, it provides messages describing the fail For example, if Oracle cannot find the library, you see: libobk.so could not be loaded. Check that it is installed properly, and that LD_ LIBRARY_PATH environment variable (or its equivalent on your platform) includes the directory where this file can be found. Here is some additional information on the cause of this error: ld.so.1: sbttest: fatal: libobk.so: open failed: No such file or directory

ERR 6: RMAN-12004 ================= Hi, I m facing this problem any pointers will of great help.... 1 RMAN-00571: =========================================================== RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS =============== RMAN-00571: =========================================================== RMAN-00579: the following error occurred at 12/16/2003 02:46:31 RMAN-10035: exception raised in RPC: RMAN-10031: ORA-19624 occurred during call to DBMS_BACKUP_RESTORE.BACKUPPIECECREATE RMAN-03015: error occurred in stored script backup_db_full RMAN-03015: error occurred in stored script backup_del_all_al RMAN-03007: retryable error occurred during execution of command: backup RMAN-12004: unhandled exception during command execution on channel t1 RMAN-10035: exception raised in RPC: ORA-19506: failed to create sequential file, name="l0f93ro5_1_1", parms="" ORA-27028: skgfqcre: sbtbackup returned error ORA-19511: Error received from media manager layer, error text: sbtbackup: Failed to process backup file. RMAN-10031: ORA-19624 occurred during call to DBMS_BACKUP_RESTORE.BACKUPPIECECREATE OR another errorstack

RMAN-12004: unhandled exception during command execution on channel disk13 RMAN-10035: exception raised in RPC: ORA-19502: write error on file "/db200_backup/archive_log03

blockno 612353 (blocksize=1024) ORA-27072: skgfdisp: I/O error HP-UX Error: 2: No such file or directory Additional information: 612353 RMAN-10031: ORA-19624 occurred during call to DBMS_BACKUP_RESTORE.BACKUPPIECECREATE OR another errorstack

RMAN-12004: unhandled exception during command execution on channel ch00 RMAN-10035: exception raised in RPC: ORA-19599: block number 691 is corrupt in controlfile C:\OR RMAN-10031: ORA-19583 occurred during call to DBMS_BACKUP_RESTORE.BACKUPPIECECREATE OR another errorstack Have managed to create a job to backup my db, but I can't restore. I get the following: RMAN-03002: failure during compilation of command RMAN-03013: command type: restore RMAN-03006: non-retryable error occurred during execution of command: IRESTORE RMAN-07004: unhandled exception during command execution on channel BackupTest RMAN-10035: exception raised in RPC: ORA-19573: cannot obtain exclusive enqueue for datafile 1 RMAN-10031: ORA-19583 occurred during call to DBMS_BACKUP_RESTORE.RESTOREBACKUPPIECE Seems to relate to corrupt or missing Oracle files.

Note 1: Subject: OERR: RMAN-12004 unhandled exception during command execution on channel %s Doc ID: Note:291388.1 Type: REFERENCE Last Revision Date: 22-NOV-2004 Status: PUBLISHED { This note contains error information about an "RMAN" error number. It may contain additional support notes as described in Note 22080.1 } Error: RMAN-12004 (RMAN-12004) Text: unhandled exception during command execution on channel %s --------------------------------------------------------------------------Cause: Action: This message should be accompanied by other error message(s) indicating the cause of the error. So, RMAN-12004 always means that you should investigate on the accompanying error messages.

Note 2: Subject: RMAN-12004, RMAN-10035, ORA-27072 Errors With Incremental RMAN Backup Doc ID: Note:437444.1 Type: PROBLEM Last Revision Date: 24-JUL-2007 Status: MODERATED In this Document Symptoms Cause Solution

--------------------------------------------------------------------------------

This document is being delivered to you via Oracle Support's Rapid Visibility (RaV) process, and subject to an independent technical review.

Applies to: Oracle Server - Enterprise Edition - Version: 10.1.0.5.0 This problem can occur on any platform. Symptoms Getting this error during the incremental backups with rman: RMAN-00579: the following error occurred at 06/20/2007 04:12:30 RMAN-03015: error occurred in stored script inc_wed RMAN-03007: retryable error occurred during execution of command: backup RMAN-12004: unhandled exception during command execution on channel ORA_DISK_1 RMAN-10035: exception raised in RPC: ORA-19502: write error on file jsikni42_1_1, blockno 6924289 (blocksize=512) ORA-27072: skgfdisp: I/O error Linux Error: 2: No such file or directory Additional information: 6924289 RMAN-10031: ORA-19624 occurred during call to DBMS_BACKUP_RESTORE. BACKUPPIECECREATE Cause The filesystem involved was out of space. Solution Add space to the filesystem involved in your backup. --------------------------------------------------------------------------------

ERR 7: ORA-27211 ================ Q: Continue to get ORA-27211 Failed to load media management library A:

had a remarkably similar experience a few months ago with Legato NetWorker and performed all of you listed with the same results. The problem turned out to be very simple. The SA installed t of the Legato Networker client because it is a 64-bit server. However, we were running a 32-bit Installing the 32-bit client solved the problem. A:

Cause: User-supplied SBT_LIBRARY or libobk.so could not be loaded. Call to dlopen for media libr See Additional information for error code. Action: Retry the command with proper media library. Or re-install Media management module for O A: Exact Error Message ORA-27211: Failed to load Media Management Library on HP-UX system Details: Overview:

The Oracle return code ORA-27211 implies a failure to load a shared object library into process Oracle Recovery Manager (RMAN) backups will fail with a message "ORA-27211: Failed to load Media if the SBT_LIBRARY keyword is defined and points to an incorrect library name. The SBT_LIBRARY k in the PARMS clause of the ALLOCATE CHANNEL statement in the RMAN script. This keyword is not va

and is new to Oracle 9i. If this value is set, it overrides the default search path for the libo By default, SBT_LIBRARY is not set. Possible quick solution: Check permissions on libraries like listed in the PARMS of the allocate channel statement. Other Troubleshooting:

If an ORA-27211 error is seen for an Oracle RMAN backup, it is necessary to review the Oracle RM is either not set or is set correctly. If set, the filename should be libobk.sl for HP-UX 10, 11 but libobk.so for HP-UX 11.23 (ia64) clients. Example of an invalid entry for HP-UX 11.23 (ia64) clients: PARMS='SBT_LIBRARY=/usr/openv/netbackup/bin/libobk.sl' Example of a correct entry for HP-UX 11.23 (ia64) clients: PARMS='SBT_LIBRARY=/usr/openv/netbackup/bin/libobk.so' Master Server Log Files: Media Server Log Files:

n/a n/a

Client Log Files: The RMAN log file on the client will show the following error message: RMAN-00571: =========================================== RMAN-00569: ======= ERROR MESSAGE STACK FOLLOWS ======= RMAN-00571: =========================================== RMAN-03009: failure of allocate command on ch00 channel at 05/21/2005 16:39:17 ORA-19554: error allocating device, device type: SBT_TAPE, device name: ORA-27211: Failed to load Media Management Library Additional information: 25 Resolution:

The Oracle return code ORA-27211 implies a failure to load a shared object library into process Oracle RMAN backups will fail with a message "ORA-27211: Failed to load Media Management Library if the SBT_LIBRARY keyword is defined and points to an incorrect library name. To manually set the SBT_LIBRARY path, follow the steps described below: 1. Modify the RMAN ALLOCATE CHANNEL statement in the backup script to reference the HP-UX 11.23 PARMS='SBT_LIBRARY=/usr/openv/netbackup/bin/libobk.so'

Note: This setting would be added to each ALLOCATE CHANNEL statement. A restart of the Oracle in is not needed for this change to take affect. 2. Run a test backup or wait for the next scheduled backup of the Oracle database

ERR8: More on DBMS_BACKUP_RESTORE: ================================== Note 1: The dbms_backup_restore package is used as a PL/SQL command-line interface for replacing native and it has very little documentation. The Oracle docs note how to install and configure the dbms_backup_restore package:

“The DBMS_BACKUP_RESTORE package is an internal package created by the dbmsbkrs.sql and prvtbkrs This package, along with the target database version of DBMS_RCVMAN, is automatically installed when the catproc.sql script is run. This package interfaces with the Oracle database server and to provide the I/O services for backup and restore operations as directed by RMAN.”

The docs also note that “The DBMS_BACKUP_RESTORE package has a PL/SQL procedure to normalize fil Oracle DBA John Parker gives this example of dbms_backup_restore to recover a controlfile: declare devtype varchar2(256); done boolean; begin devtype:=dbms_backup_restore.deviceallocate( type=>'sbt_tape', params=>'ENV=(OB2BARTYPE=Oracle8,OB2APPNAME=rdcs,OB2BARLIST=ORA_RDCS_WEEKLY)', ident=>'t1'); dbms_backup_restore.restoresetdatafile; dbms_backup_restore.restorecontrolfileto('D:\oracle\ora81\dbs\CTL1rdcs.ORA'); dbms_backup_restore.restorebackuppiece( 'ORA_RDCS_WEEKLY.dbf', DONE=>done ); dbms_backup_restore.restoresetdatafile; dbms_backup_restore.restorecontrolfileto('D:\DBS\RDCS\CTL2RDCS.ORA'); dbms_backup_restore.restorebackuppiece( 'ORA_RDCS_WEEKLY.dbf', DONE=>done ); dbms_backup_restore.devicedeallocate('t1'); end; Here are some other examples of using dbms_backup_restore:

DECLARE devtype varchar2(256); done boolean; BEGIN devtype := dbms_backup_restore.DeviceAllocate (type => '',ident => 'FUN'); dbms_backup_restore.RestoreSetDatafile; dbms_backup_restore.RestoreDatafileTo(dfnumber => 1,toname => 'D:\ORACLE_BASE\datafiles\SYSTEM01 dbms_backup_restore.RestoreDatafileTo(dfnumber => 2,toname => 'D:\ORACLE_BASE\datafiles\UNDOTBS. --dbms_backup_restore.RestoreDatafileTo(dfnumber => 3,toname => 'D:\ORACLE_BASE\datafiles\MYSPAC dbms_backup_restore.RestoreBackupPiece(done => done,handle => 'D:\ORACLE_BASE\RMAN_BACKUP\MYDB_D dbms_backup_restore.DeviceDeallocate; END; /

--Restore Archived redolog DECLARE devtype varchar2(256); done boolean; BEGIN devtype := dbms_backup_restore.DeviceAllocate (type => '',ident => 'FUN'); dbms_backup_restore.RestoreSetArchivedLog(destination=>'D:\ORACLE_BASE\achive\'); dbms_backup_restore.RestoreArchivedLog(thread=>1,sequence=>1); dbms_backup_restore.RestoreArchivedLog(thread=>1,sequence=>2); dbms_backup_restore.RestoreArchivedLog(thread=>1,sequence=>3); dbms_backup_restore.RestoreBackupPiece(done => done,handle => 'D:\ORACLE_BASE\RMAN_BACKUP\MYDB_L dbms_backup_restore.DeviceDeallocate; END; / Note 2: --------Restore controlfile DECLARE

devtype varchar2(256); done boolean; BEGIN devtype := dbms_backup_restore.DeviceAllocate(type => '',ident => 'FUN'); dbms_backup_restore.RestoresetdataFile; dbms_backup_restore.RestoreControlFileto('D:\ORACLE_BASE\controlfiles\CONTROL01.CTL'); dbms_backup_restore.RestoreBackupPiece('D:\ORACLE_BASE\Rman_Backup\MYDB_DF_BCK0BH1JBVA_1_1',done dbms_backup_restore.RestoresetdataFile; dbms_backup_restore.RestoreControlFileto('D:\ORACLE_BASE\controlfiles\CONTROL02.CTL'); dbms_backup_restore.RestoreBackupPiece('D:\ORACLE_BASE\Rman_Backup\MYDB_DF_BCK0BH1JBVA_1_1',done dbms_backup_restore.RestoresetdataFile; dbms_backup_restore.RestoreControlFileto('D:\ORACLE_BASE\controlfiles\CONTROL03.CTL'); dbms_backup_restore.RestoreBackupPiece('D:\ORACLE_BASE\Rman_Backup\MYDB_DF_BCK0BH1JBVA_1_1',done dbms_backup_restore.DeviceDeallocate; END; /

--Restore datafile DECLARE devtype varchar2(256); done boolean; BEGIN devtype := dbms_backup_restore.DeviceAllocate (type => '',ident => 'FUN'); dbms_backup_restore.RestoreSetDatafile; dbms_backup_restore.RestoreDatafileTo(dfnumber => 1,toname => 'D:\ORACLE_BASE\datafiles\SYSTEM01 dbms_backup_restore.RestoreDatafileTo(dfnumber => 2,toname => 'D:\ORACLE_BASE\datafiles\UNDOTBS. --dbms_backup_restore.RestoreDatafileTo(dfnumber => 3,toname => 'D:\ORACLE_BASE\datafiles\MYSPAC dbms_backup_restore.RestoreBackupPiece(done => done,handle => 'D:\ORACLE_BASE\RMAN_BACKUP\MYDB_D dbms_backup_restore.DeviceDeallocate; END; /

--Restore Archived redolog DECLARE devtype varchar2(256); done boolean; BEGIN devtype := dbms_backup_restore.DeviceAllocate (type => '',ident => 'FUN'); dbms_backup_restore.RestoreSetArchivedLog(destination=>'D:\ORACLE_BASE\achive\'); dbms_backup_restore.RestoreArchivedLog(thread=>1,sequence=>1); dbms_backup_restore.RestoreArchivedLog(thread=>1,sequence=>2); dbms_backup_restore.RestoreArchivedLog(thread=>1,sequence=>3); dbms_backup_restore.RestoreBackupPiece(done => done,handle => 'D:\ORACLE_BASE\RMAN_BACKUP\MYDB_L dbms_backup_restore.DeviceDeallocate; END; /

ERR 9: RMAN-00554 initialization of internal recovery manager package failed: =============================================================================

connected to target database: PLAYROCA (DBID=575215626) RMAN-00571: =========================================================== RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS =============== RMAN-00571: =========================================================== RMAN-00554: initialization of internal recovery manager package failed RMAN-04004: error from recovery catalog database: ORA-03135: connection lost contact keys: RMAN-00554

RMAN-04004 ORA-03135 ORA-3136 >>>> In alertlog of the rman, we can find: WARNING: inbound connection timed out (ORA-3136) Thu Mar 13 23:09:54 2008 >>>> In Net logs sqlnet.log we can find: Warning: Errors detected in file /dbms/tdbaplay/ora10g/home/network/log/sqlnet.log

> *********************************************************************** > Fatal NI connect error 12170. > > VERSION INFORMATION: > TNS for IBM/AIX RISC System/6000: Version 10.2.0.3.0 - Production > TCP/IP NT Protocol Adapter for IBM/AIX RISC System/6000: Version 10.2.0.3.0 > Oracle Bequeath NT Protocol Adapter for IBM/AIX RISC System/6000: Version 10.2 > Time: 18-MAR-2008 23:01:43 > Tracing not turned on. > Tns error struct: > ns main err code: 12535 > TNS-12535: TNS:operation timed out > ns secondary err code: 12606 > nt main err code: 0 > nt secondary err code: 0 > nt OS err code: 0 > Client address: (ADDRESS=(PROTOCOL=tcp)(HOST=57.232.4.123)(PORT=35844))

Note 1: RMAN-00554: initialization of internal recovery manager package failed Is a general error code. You must turn your attention the the codes underneath this one. For example: RMAN-00571: =========================================================== RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS =============== RMAN-00571: =========================================================== RMAN-00554: initialization of internal recovery manager package failed RMAN-06003: ORACLE error from target database: ORA-00210: cannot open the specified control file ORA-00202: control file: '/devel/dev02/dev10g/standbyctl.ctl' RMAN-00571: =========================================================== RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS =============== RMAN-00571: =========================================================== RMAN-00554: initialization of internal recovery manager package failed RMAN-04005: error from target database: ORA-01017: invalid username/password;

ERR: RMAN-20242: specification does not match any archive log in the recovery catalog =================================================================================== Starting backup at 17-MAY-08 released channel: t1 released channel: t2 RMAN-00571: ===========================================================

RMAN-00569: RMAN-00571: RMAN-03002: RMAN-06004: RMAN-20242:

=============== ERROR MESSAGE STACK FOLLOWS =============== =========================================================== failure of backup command at 05/17/2008 23:30:13 ORACLE error from recovery catalog database: specification does not match any archive log in the recovery catalog

Note 1: Oracle Error :: RMAN-20242 specification does not match any archivelog in the recovery catalog Cause No archive logs in the specified archive log range could be found. Action Check the archive log specifier.

Note 2: Some of the common RMAN errors are: RMAN-20242: Specification does not match any archivelog in the recovery catalog. Add to RMAN script: sql 'alter system archive log current';

Note 3: Q: RMAN-20242: specification does not match any archive log in the recovery ca Posted: Feb 12, 2008 7:52 AM Reply

A couple of archive log files were deleted from the OS. They still show up in the list of archiv in Enterprise Manager. I want to fix this because now whenever I try to run a crosscheck command, I get the message: RMAN-20242: specification does not match any archive log in the recovery catalog I also tried to uncatalog those files, but got the same message. Any suggestions on what to do? Thanks! A: hi, from rman run the command list expired archivelog; if ther archives are in this list they will show, then i think you should do a crosscheck archivelog all; then you should be able to delete them.

regards

Note 4:

The RMAN error number would be helpful, but this is a common problem - RMAN-20242 - and is addre Either the name specification (the one you entered) is wrong, or you could be using mismatched v between RMAN and the database (don't know since you didn't provide any version details).

Note 5: Q: Hi there! We are having problems with an Oracle backup. The compiling of the backup command fails with the error message: RMAN-20242: specification does not match any archivelog in the recovery catalog But RMAN is only supposed to backup any archived logs that are there and then insert them in the catalog... Did anybody experience anything similar? This is 8.1.7 on HP-UX with Legato Networker Thanks, A:

If i ask rman to backup archivelogs that are more than 2days old and there are none, thats not a That is when i see it the most, now most companies will force a log switch after a set amount of time during the day so in DR, you dont lose days worth of redo that might still be hanging in a redo log if it gets lost.

Starting backup at 30-JUN-08 released channel: t1 released channel: t2 RMAN-00571: =========================================================== RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS =============== RMAN-00571: =========================================================== RMAN-03002: failure of backup command at 06/30/2008 23:30:45 RMAN-06004: ORACLE error from recovery catalog database: RMAN-20242: specification does not m RMAN> Recovery Manager complete. End archiving

: 23:30:51

Note 6: Subject: RMAN-20242 specification does not match any archive log in the recovery catalog Doc ID: Note:198718.1 Type: BULLETIN Last Revision Date: 03-DEC-2007 Status: PUBLISHED

PURPOSE ------How to resolve the RMAN-20242 messages at the end of an archivelog backup.

Checks While Getting RMAN-20242: ----------------------------------------RMAN-20242 messages while backing up the archive log The RMAN-20242 appears at the end of the log while backing up the archive log. The script used to take the backup of the database and the archive log is below: run { backup database plus archivelog; } This is an Oracle9i command which executes the following procedure - ARCHIVE LOG CURRENT - Backup ARCHIVES - Backup DATABASE - ARCHIVE LOG CURRENT - Backup ARCHIVES Cause: -----This could happens when the RMAN executable, recovery catalog and the target database differ in their version. Solution: ---------Use the highest version of the RMAN executable to create the catalog or upgrade the catalog with : RMAN> upgrade catalog; RMAN> upgrade catalog; RELATED DOCUMENTS ----------------Note 133870.1: RMAN-20242: Specification does not Match any Archivelog in the Recovery Catalog Note 180169.1: RMAN OPS Archivelog Restore Fails with RMAN-20242

Note 7: Subject: RMAN-20242: Specification does not Match any Archivelog in the Recovery Catalog Doc ID: Note:133870.1 Type: BULLETIN Last Revision Date: 21-APR-2006 Status: PUBLISHED PURPOSE ------The purpose of this article is to resolve RMAN-20242 when trying to backup archived log files. SCOPE & APPLICATION ------------------RMAN 'archivelog like' method is very useful when trying to backup archived log

on oracle parallel server and oracle parallel failsafe where archived logs exist on all nodes local disks. Resolving RMAN-20242: specification does not match any archivelog in the recovery catalog ----------------------------------------------------------------------------------------Trying to run the following RMAN script result with RMAN-20242. RMAN> run { 2> allocate channel c1 type disk; 3> backup 4> archivelog like 'E:\oracle\oradata\prod\archive%'; 5> } RMAN-03022: RMAN-03023: RMAN-08030: RMAN-08500:

compiling command: executing command: allocated channel: channel c1: sid=17

allocate allocate c1 devtype=DISK

RMAN-03022: RMAN-03026: RMAN-08031: RMAN-00571: RMAN-00569: RMAN-00571: RMAN-03002: RMAN-03013: RMAN-06004: on does not

compiling command: backup error recovery releasing channel resources released channel: c1 =========================================================== =============== ERROR MESSAGE STACK FOLLOWS =============== =========================================================== failure during compilation of command command type: backup ORACLE error from recovery catalog database: RMAN-20242: specificati match any archivelog in the recovery catalog

To resolve the above error, query v$archived_log to find out the archived log details and note the format of the name column. SQL> select * from v$archived_log; RECID STAMP ---------- ---------NAME -------------------------------------------------------------------------------THREAD# SEQUENCE# RESETLOGS_CHANGE# RESETLOGS FIRST_CHANGE# FIRST_TIM ---------- ---------- ----------------- --------- ------------- --------NEXT_CHANGE# NEXT_TIME BLOCKS BLOCK_SIZE CREA REGI STA ARC DEL COMPLETIO ------------ --------- ---------- ---------- ---- ---- --- --- --- --------1 419970034 E:\ORACLE\ORADATA\PROD\ARCHIVE\PRODT001S01524.ARC 1 1524 1 21-JAN-01 599954 25-JAN-01 600067 25-JAN-01 103 512 FGRD FGRD NO YES YES 25-JAN-01 Notice the name is in upper case - 'E:\ORACLE\ORADATA\PROD\ARCHIVE\PRODT001S01524.ARC'. Modify the script and run again. RMAN> run { 2> allocate channel c1 type disk; 3> backup 4> archivelog like 'E:\ORACLE\ORADATA\PROD\ARCHIVE\PROD%' 5> delete input; 6> release channel c1; 7> } RMAN-03022: RMAN-03023: RMAN-08030: RMAN-08500:

compiling command: executing command: allocated channel: channel c1: sid=17

allocate allocate c1 devtype=DISK

RMAN-03022: compiling command: backup

RMAN-03023: executing command: backup RMAN-08009: channel c1: starting archivelog backupset RMAN-08502: set_count=36 set_stamp=420311754 creation_time=29-JAN-01 RMAN-08014: channel c1: specifying archivelog(s) in backup set RMAN-08504: input archivelog thread=1 sequence=1538 recid=15 stamp=420196579 RMAN-08504: input archivelog thread=1 sequence=1539 recid=16 stamp=420239024 RMAN-08504: input archivelog thread=1 sequence=1540 recid=17 stamp=420310708 RMAN-08504: input archivelog thread=1 sequence=1541 recid=18 stamp=420310800 RMAN-08504: input archivelog thread=1 sequence=1542 recid=19 stamp=420310879 RMAN-08013: channel c1: piece 1 created RMAN-08503: piece handle=E:\ORACLE\ORA817\DATABASE\14CGQSMA_1_1 comment=NONE RMAN-08525: backup set complete, elapsed time: 00:00:02 RMAN-08071: channel c1: deleting archivelog(s) RMAN-08514: archivelog filename=E:\ORACLE\ORADATA\PROD\ARCHIVE\PRODT001S01538.AR C recid=15 stamp=420196579 RMAN-08514: archivelog filename=E:\ORACLE\ORADATA\PROD\ARCHIVE\PRODT001S01539.AR C recid=16 stamp=420239024 RMAN-08514: archivelog filename=E:\ORACLE\ORADATA\PROD\ARCHIVE\PRODT001S01540.AR C recid=17 stamp=420310708 RMAN-08514: archivelog filename=E:\ORACLE\ORADATA\PROD\ARCHIVE\PRODT001S01541.AR C recid=18 stamp=420310800 RMAN-08514: archivelog filename=E:\ORACLE\ORADATA\PROD\ARCHIVE\PRODT001S01542.AR C recid=19 stamp=420310879 RMAN-03023: executing command: partial resync RMAN-08003: starting partial resync of recovery catalog RMAN-08005: partial resync complete RMAN-03022: compiling command: release RMAN-03023: executing command: release RMAN-08031: released channel: c1 RMAN> So, the crux is: check case of identifiers in scripts

Note 8: Subject: Bug 6768289 - RMAN-6004 / RMAN-20242 if archivelog does not exist Doc ID: Note:6768289.8 Type: PATCH Last Revision Date: 02-JUL-2008 Status: PUBLISHED Click here for details of sections in this note. Bug 6768289 RMAN-6004 / RMAN-20242 if archivelog does not exist This note gives a brief overview of bug 6768289. Affects: Product (Component) Oracle Server (Rdbms) Range of versions believed to be affected Versions < 11 Versions confirmed as being affected 10.2.0.3 10.2.0.4 Platforms affected Generic (all / most platforms affected) Fixed: This issue is fixed in 11.1.0.6 (Base Release) Symptoms: Related To: Error May Occur RMAN-20242 / RMAN-6004 RMAN (Recovery Manager) Description

Some "BACKUP ARCHIVELOG" commands can result in no archive log being found leading to RMAN-6004 / RMAN-20242 errors when not expected. (Only an RMAN-20242 informational message is expected) eg: RMAN> backup archivelog all delete input; RMAN> backup archivelog UNTIL TIME 'sysdate'; ^ RMAN-6004 / RMAN-20242 is signalled when it should just be an informational RMAN-20242 error. Workaround: Only specify options for which archive log(s) exist. (May not be practical)

Note 9: Subject: RMAN-20242, RMAN-6027: on „CHANGE ARCHIVELOG...‟ Commands after Open Resetlogs Doc ID: Note:94206.1 Type: PROBLEM Last Revision Date: 21-OCT-2005 Status: PUBLISHED Problem Description: -------------------RMAN-20242 or RMAN-6027 on „CHANGE ARCHIVELOG after opening target database with resetlogs.

...‟ commands immediately

Obviously, you already reset the catalog database into a new incarnation number, however, the Change command give the following errors: RMAN> allocate channel for delete type disk; RMAN-03022: RMAN-03023: RMAN-08030: RMAN-08500:

compiling command: allocate executing command: allocate allocated channel: delete channel delete: sid=15 devtype=DISK

RMAN> change archivelog all validate; RMAN-03022: RMAN-03026: RMAN-00569: RMAN-03002: RMAN-03013: RMAN-06038: RMAN-20242:

compiling command: change error recovery releasing channel resources ================error message stack follows================ failure during compilation of command command type: change recovery catalog package detected an error specification does not match any archivelog in the recovery catalog

Solution Description: --------------------Perform log switch on target database. That action will produce first entry in the „AL‟ table of RMAN catalog for current database incarnation. Explanation: -----------RMAN catalog „AL‟ table does not contain any archivelog entry for a current database incarnation. You need to perform a log switch on the target database, and then resync the catalog database. That action will update the catalog

database „AL‟ table with a first entry for the current incarnation. RMAN-20242: specification does not match any archivelog in the recovery catalog error can be misleading. In that particular case should be understood as null listing returned fr

Other articles. Article 1

http://www.backupcentral.com/phpBB2/two-way-mirrors-of-external-mailing-lists-3/ibm-tsm-13/how-d My DBAs and I are completely baffled. How do I get TDPO to send controlfiles to SBT_TAPE instead of DISK? My setup: TSM server (Enterprise) 5.5 on AIX 5.2. TDPO 5.4.1 on Solaris 10/SPARC and Oracle 10gR2. RMAN settings: RMAN> show all; RMAN configuration parameters are: CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 7 DAYS; CONFIGURE BACKUP OPTIMIZATION OFF; CONFIGURE DEFAULT DEVICE TYPE TO 'SBT_TAPE'; CONFIGURE CONTROLFILE AUTOBACKUP ON; CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE SBT_TAPE TO '%F'; # default CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F'; # default CONFIGURE DEVICE TYPE 'SBT_TAPE' PARALLELISM 3 BACKUP TYPE TO BACKUPSET; CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO BACKUPSET; # default CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE SBT_TAPE TO 1; # default CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE SBT_TAPE TO 1; # default CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default CONFIGURE CHANNEL DEVICE TYPE 'SBT_TAPE' PARMS ''; CONFIGURE MAXSETSIZE TO UNLIMITED; # default CONFIGURE ENCRYPTION FOR DATABASE OFF; # default CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/opt/oracle10/product/10.2.0/db_1/dbs/snapcf_mydb.f'; # How we backup -- RMAN commands:

allocate channel t1 type 'SBT_TAPE' parms 'ENV=(TDPO_OPTFILE=/opt/tivoli/tsm/client/oracle/bin64

allocate channel t2 type 'SBT_TAPE' parms 'ENV=(TDPO_OPTFILE=/opt/tivoli/tsm/client/oracle/bin64

allocate channel t3 type 'SBT_TAPE' parms 'ENV=(TDPO_OPTFILE=/opt/tivoli/tsm/client/oracle/bin64 sql 'alter system archive log current'; backup incremental level = ${LEVEL} filesperset 5 format "df_%d_%s_%p_%t.lv${LEVEL}" (database include current controlfile); backup filesperset 20

format "ar_%d_%s_%p_%t.lv${LEVEL}" (archivelog all delete all input); release channel t1; release channel t2; release channel t3; tdpo.conf for the restore on a test box: DSMI_ORC_CONFIG /opt/tivoli/tsm/client/oracle/bin64/dsm.opt DSMI_LOG /var/log/tsm TDPO_FS prod-oracle TDPO_MGMT_CLASS_2 tsmoracle-mgmt2 TDPO_MGMT_CLASS_3 tsmoracle-mgmt3 TDPO_MGMT_CLASS_4 tsmoracle-mgmt4 Not a fancy setup; pretty much 'stock' (standard). Thoughts/ideas? -Dan Back to top Dan Foster Guest

Posted: Tue Apr 22, 2008 11:03 pm

Post subject: How do I get TDPO to send controlfiles to SB

-------------------------------------------------------------------------------Hot Diggety! Dan Foster was rumored to have written: Quote: My DBAs and I are completely baffled. How do I get TDPO to send controlfiles to SBT_TAPE instead of DISK? Eventually resolved: needed to get *every* single config file on BOTH hosts to match for a restore to the DR host, AND to disable PASSWordaccess GENERATE. After that, went swimmingly, and the DBAs not familiar with RMAN/TDPO were impressed. We then set up a RMAN catalog db, which worked great, too. -Dan Back to top Steve Stackwick Guest

Posted: Wed Apr 23, 2008 9:50 am

Post subject: How do I get TDPO to send controlfiles to SBT

--------------------------------------------------------------------------------

On Thu, Apr 10, 2008 at 3:26 PM, Dan Foster evilphb.org> wrote: Quote: My setup: TSM server (Enterprise) 5.5 on AIX 5.2. TDPO 5.4.1 on Solaris 10/SPARC and Oracle 10gR2.

Do you really have TSM 5.5 running on AIX 5.2? I wondered if that would work, and kind of thought it would, but Tivoli sez u need AIX 5.3. Just wondering. Steve -Stephen Stackwick Jacob & Sundstrom, Inc. 401 East Pratt St., Suite 2214 Baltimore, MD 21202-3003 (410) 539-1135 * (866) 539-1135 sstackwick < at > jasi.com Back to top Dan Foster Guest

Posted: Wed Apr 23, 2008 10:51 am

Post subject: How do I get TDPO to send controlfiles to SB

-------------------------------------------------------------------------------Hot Diggety! Steve Stackwick was rumored to have written: Quote: On Thu, Apr 10, 2008 at 3:26 PM, Dan Foster evilphb.org> wrote: Quote: My setup: TSM server (Enterprise) 5.5 on AIX 5.2. TDPO 5.4.1 on Solaris 10/SPARC and Oracle 10gR2.

Do you really have TSM 5.5 running on AIX 5.2? I wondered if that would work, and kind of thought it would, but Tivoli sez u need AIX 5.3. Just wondering. There's two kinds of 'work': - 'probably technically works but you're on your own' vs. - '20 foot wall of flames will erupt if you attempt it' IBM's statement in this particular case is more of the former, presumably partly to reduce costs in regression testing (amongst many other valid reasons). And, of course, if you hit a bug with such a setup, they're unlikely to fix it or even give the time of the day.

The $65,536 question: Why did I put 5.5 on AIX 5.2? Simple: I was a little too hasty in reading the min requirements, a rare departure for my character. I always, with that one glaring exception, stick to supported revs for everything. Why not backout? A lengthy maint moratorium amongst other things in my environment made it more practical to just stay put. With that said: # lslpp -l tivoli.tsm.server.aix5.rte64 Fileset Level State Description ---------------------------------------------------------------------------Path: /usr/lib/objrepos tivoli.tsm.server.aix5.rte64 5.5.0.0 COMMITTED IBM Tivoli Storage Manager 64 bit Server Runtime Path: /etc/objrepos tivoli.tsm.server.aix5.rte64 5.5.0.0 COMMITTED IBM Tivoli Storage Manager 64 bit Server Runtime # oslevel 5.2.0.0 (It's actually at 5.2 ML9 + some patches) I haven't seen any issues so far, though I would not recommend doing this to anyone except the truly desperate. It's by far better to stay supported for the server side. You can usually get away with downrev clients past the officially supported revs, but server [and support] is too important to risk for most folks. Then there's that TSM server revs may have some relation to device support of the underlying AIX OS, particularly for newer devices. In my case, I hadn't changed anything else, so got by with that. I'm looking to bring the server up to a newer AIX version soon to resolve this uncomfortable position. Last, but not the least, I've also been duly thwacked by colleagues with multiple copies of the old ADSM 3.1 printed manuals to serve as a reminder to be more careful and diligent in my reading. Sure deserved that. -Dan Back to top

Display posts from previous: All Posts1 Day7 Days2 Weeks1 Month3 Months6 Months1 Year Oldest Fir Backup Central Forums Forum Index -> IBM TSM All times are GMT - 8 Hours Page 1 of 1 Article 2 http://dba-brent.blogspot.com/2007/06/issues-with-rman-on-aix-using-tdp-for.html Issues with RMAN on AIX using TDP for Oracle

So our group has thankfully decided to go with RMAN for database backups, and you know i'm first

Here's my setup at a high level: 10gR2 database (rmandb - snappy name huh? :) IBM's TDP 5.3 for Oracle AIX 5.2 Steps: 1. 2. 3. 4. Once

My internal IT department setup my TSM domain, etc. Sysadmin installed the TDP fileset and then generated the password file for me I used the TSM Quick Start Guide to get all of my config files and directories setup I used OEM GC R3 to set the RMAN preferences, and to setup the Tape parameters: ENV=(TDPO_O all of this was done and "$ tdpoconf SHOWENVironment" looked good, i went for my first test

I logged into RMAN, connected to target, and then ran a connection test: run { allocate channel oem_sbt_backup type 'sbt_tape' format '%U' parms 'ENV=(TDPO_OPTFILE=/usr/tivoli release channel oem_sbt_backup; } And it worked fine. Woot!

Next, I used the "Test Backup" button on the Backup Setting OEM page. It's a simple test consist run { allocate channel oem_sbt_backup type 'sbt_tape' format '%U' parms 'ENV=(TDPO_OPTFILE=/usr/tivoli backup as BACKUPSET current controlfile tag '05252007020711'; restore controlfile validate from tag '05252007020711'; release channel oem_sbt_backup; } And... it fails with this lovely error output: RMAN-00571: =========================================================== RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS =============== RMAN-00571: =========================================================== RMAN-03009: failure of backup command on ORA_SBT_TAPE_1 channel at 05/22/2007 16:28:09 ORA-19506: failed to create sequential file, name="0aiiceoi_1_2", parms="" ORA-27028: skgfqcre: sbtbackup returned error ORA-19511: Error received from media manager layer, error text: ANS0231E (RC2025) The management class name was not found. Nice huh?? What the heck is "ANS0231E (RC2025) The management class name was not found."?

If you think that's depressing, try entering "ANS0231E" into Google. There is less than 30 resul After working with IS and IBM for 3 weeks (3 weeks!!!), we finally worked our way up to a SEV-1 Wait for it...

My tdpo.opt file had no stanza for "TDPO_MGMT_CLASS_2" (or _3, or _4) because I was only allocat

Here's the gotcha: I setup RMAN to do 'duplex' copies, e.g.: create 2 copies of all datafiles an The problem is RMAN opens 2 channels when duplex is set!!!

Bottom Line: (I had IBM add this to their KB) If you receive the dreaded "ANS0231E (RC2025) The management class name was not found." error, t This was a hard lesson learned... :-) Article 3 http://adsm.org/forum/archive/index.php?t-4549.html

I am facing the following error with RMAN backup with TDP for oracle in one of the My cluster No My Env:

TDP for oracle with 5.2.1 TSM 5.3 with LANFREE Client AIX with Oracle 9iR2 I am able to take the lanfree backup from cluster nodes. But I am getting the following error for with rman in one of the server. Error Details: RMAN> 2> 3> 4> 5> 6> 7> 8> 9> 10> RMAN-00571: ================================================== ========= RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS =============== RMAN-00571: ================================================== ========= RMAN-03009: failure of allocate command on t2 channel at 07/05/2005 12:56:36 ORA-19554: error allocating device, device type: SBT_TAPE, device name: ORA-27000: skgfqsbi: failed to initialize storage subsystem (SBT) layer IBM AIX RISC System/6000 Error: 106: Reserved errno was encountered Additional information: 7011 ORA-19511: Error received from media manager layer, error text: SBT error = 7011, errno = 106, sbtopen: system error

Please suggest me any help for this prob.

Rgds Sanku -------------------------------------------------------------------------------cheffern07-05-2005, 07:03 AM Anything with the SBT layer is usually indicative of an issue with the Oracle relinking. Have you relinked Oracle after the TDP library install? --------------------------------------------------------------------------------

sgabriel6207-06-2005, 12:52 AM Yep, that is the answer. Relink your Oracle libraries. Also ensure your tdpo.conf file states yo

Steve -------------------------------------------------------------------------------sanku07-14-2005, 01:01 AM Dear Friends,

I have relinked the Oracle libraries through "relink all " command, After the relinkg still i am My all configuration file are correct(dsm.opt,dsm.sys &tdpo.opt).

Kindly provide some suggetions on this. Advanced apprications to them.

Rgds Sanku -------------------------------------------------------------------------------sgabriel6207-16-2005, 12:05 AM Send me your RMAN configuration script -------------------------------------------------------------------------------sanku07-18-2005, 10:25 AM Thanks for your support.

My rman script: rman log=/backup/logs/level0_backup.log target / <
CROSSCHECK BACKUP; LIST BACKUP OF DATABASE;

REPORT UNRECOVERABLE; REPORT SCHEMA; REPORT NEED BACKUP; REPORT OBSOLETE; quit EOF

Rgds Sanku -------------------------------------------------------------------------------sgabriel6207-21-2005, 01:05 AM Here is a discrepancy allocate channel t1 type 'sbt_tape' parms Versus the following error RMAN-03009: failure of allocate command on t2 channel at 07/05/2005 12:56:36

You have "channel differences"

Also where is your TDP dsm.opt file, it should be in Oracle's working directory.

Steven -------------------------------------------------------------------------------rhysm08-02-2005, 03:53 AM I have problem that looks very similar. AIX 5.3L 64 bit , Oracle 9i 9.20, TSM 5.3 with tdpo

/u01/app/oracle/920/lib/libobk.a is linked to /usr/lib/libobk64.a, which is in turn linked to th tsm/client/oracle/bin64/dsm.opt contains servername as per dsm.sys. tsm/client/oracle/bin64/tdpo.opt contains dsm.opt location, log location and nodename.

tsm/client/api/bin64/dsm.sys is linked to tsm/client/baclient/bin/dsm.sys, which contains a serv

Password is set and the node is registered. I have done a relink all.

Receiving this when attempting a very simple RMAN backup script:

RMAN> run { 2> allocate channel t1 type 'sbt_tape' parms 'ENV=(TDPO_OPTFILE=/usr/tivoli/tsm/client/oracle/bin64/tdpo.opt)'; 3> backup filesperset 5 format 'dt_%t_%s_%p' (database); 4> }

RMAN-00571: RMAN-03009: failure of allocate command on t1 channel at 08/02/2005 19:12:58 ORA-19554: error allocating device, device type: SBT_TAPE, device name: ORA-27000: skgfqsbi: failed to initialize storage subsystem (SBT) layer IBM AIX RISC System/6000 Error: 106: Reserved errno was encountered Additional information: 7011 ORA-19511: Error received from media manager layer, error text: SBT error = 7011, errno = 106, sbtopen: system error

Any ideas?

Rhys. -------------------------------------------------------------------------------nezih08-03-2005, 09:05 AM hey rhysm, give chmod 777 to libobk64.a on /usr/tivoli/tsm/client/oracle/bin64 directory and try again... Article 4 Error is

RMAN-20207: UNTIL TIME or RECOVERY WINDOW is before RESETLOGS time Note 1: Subject: Recover With Until Time fails With RMAN-20207 When Time Is Before Last RESETLOGS Doc ID: Note:159405.1 Type: PROBLEM Last Revision Date: 15-MAY-2003 Status: PUBLISHED

fact: Oracle Server - Enterprise Edition 8

fact: Recovery Manager (RMAN) symptom: Recover with UNTIL TIME option fails symptom: RMAN-20207: UNTIL TIME or RECOVERY WINDOW is before RESETLOGS time cause: The UNTIL TIME is prior to the current Database incarnation in the RMAN-catalog.

fix: Set the Database Incarnation back to an incarnation before the last RESETLOG time. Example commands : rman catalog rman/rman@rcat RMAN> list incarnation; RMAN> Set dbid RMAN> connect target un/pw@ RMAN> reset database to incarnation 2; Preform the recovery. See also Note 1070453.6 Point-in-Time Recovery of a Backup From Before Last Resetlogs

Note 2: Subject: RMAN: Point-in-Time Recovery of a Backup From Before Last Resetlogs Doc ID: Note:1070453.6 Type: PROBLEM Last Revision Date: 21-MAR-2002 Status: PUBLISHED Problem Description: ==================== You would like to restore a database to a point-in-time, but the backup being used is from before the last resetlogs. The problem is that when a database is opened with resetlogs, the incarnation of the database is also reset and RMAN keeps a record of the incarnation of the database in the Recovery Catalog. Problem Explanation: ==================== When we do a point-in-time recovery and open the database with resetlogs, we also need to tell RMAN that we are reseting the database incarnation. Therefore we use the RESET DATABASE command to direct RMAN to create a new database incarnation record in the recovery catalog. This new incarnation record indicates the current incarnation. RMAN will associate all subsequent backups and log archiving done by the target database with the new database incarnation. If we want to restore the database to a point-in-time before the last resetlogs, we need to tell RMAN that our current incarnation needs to be reset to the old one. Problem References: =================== ORACLE8i BACKUP AND RECOVERY GUIDE Search Words:

============= RESET INCARNATION RECOVER RESETLOGS Solution Description: ===================== Before we can restore a backup from before the last resetlogs, we need to reset the database to the old incarnation number. Here are the steps to reseting the correct incarnation and restoring the database to a point in time before the last resetlogs. Note: Target database should be nomounted. 1. Start RMAN and connect only to the catalog database. % rman catalog rman/rman@rcat 2. Execute the command that lists all the incarnation values for the databases in the recovery catalog. RMAN> list incarnation; List of DB Key ------1 224

Database Incarnations Inc Key DB Name DB ID ------- -------- ---------------2 R815 579966833 225 R815 579966833

CUR --NO YES

Reset SCN ---------1 92402

Reset Time ---------03-MAR-99 05-MAY-99

We can see that one of the databases had been open with resetlogs and a new incarnation was started (DB_ID 579966833). We should look at the column DATABASE INC KEY. We can see the original incarnation for this database was 2 and was reset to what is still the current incarnation of 225. 2 is the database incarnation that has to be set in order to restore a backup from before the last resetlogs. 3. We first need to let RMAN know what database ID we will be dealing with, so we execute the following command: RMAN> Set dbid 579966833; 4. Now we need to connect to the target instance so we can verify the database ID. RMAN> connect target RMAN-06005: connected to target database: R815 (DBID=579966833) 5. Since we are connected to both the recovery catalog and target database, we need to tell RMAN to reset the database incarnation to 2. We do this with the following command: RMAN> reset database to incarnation 2; We can now see that the current incarnation has been set back to 2. RMAN> list incarnation; List of DB Key ------1 224

Database Incarnations Inc Key DB Name DB ID ------- -------- ---------------2 R815 579966833 225 R815 579966833

CUR --YES NO

Reset SCN ---------1 92402

Reset Time ---------03-MAY-99 05-MAY-99

6.

After resetting the database, issue restore and recover commands to restore and recover the database files from the prior incarnation, then open the database with the RESETLOGS option. You need start from restoring a previous incarnation control file when the database is in nomount state. When the database is reset to the previous incarnation, the catalog will automatically picks up a right control file. After restoring the control file, the database must be mounted for datafiles restore. run { set until time 'Jul 8 1999 07:55:00'; # set time to just before data was lost allocate channel dev1 type disk; shutdown abort; startup nomount; restore controlfile; alter database mount; # mount database after restoring control file restore database; recover database; alter database open resetlogs; # this command automatically resets the database # so that this incarnation is the new incarnation }

Solution Explanation: ===================== Once we set the database to the prior incarnation, RMAN will allow a restore and recover of the database using a backup from before the last resetlogs. Solution References: ==================== Oracle8i Recovery Manager User‟s Guide, Release 1 (8.1.6) - Part No. A76990-01 Oracle9i Recovery Manager User‟s Guide, Release 1 (9.0.1) - Part No. A9013 Note 3: { This note contains error information about an "RMAN" error number. It may contain additional support notes as described in Note 22080.1 } Oracle9i Release 1 Message ~~~~~~~~~~~~~~~~~~~~~~~~~~~ Error: B2136 (RMAN-20207) Text: UNTIL TIME or RECOVERY WINDOW is before RESETLOGS time --------------------------------------------------------------------------Cause: UNTIL TIME and RECOVERY WINDOW cannot be less than the database creation time or RESETLOGS time. Action: Check the UNTIL TIME or RECOVERY WINDOW. If the database needs to be restored to an old incarnation, use the RESET DATABASE TO INCARNATION command. Oracle 8.1 or Earlier Error Message ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Error: RMAN 20207 Text: UNTIL TIME is before RESETLOGS time --------------------------------------------------------------------------Cause: UNTIL TIME cannot be less than the database creation time or RESETLOGS time. Action: Check the UNTIL TIME. If the database needs to be restored to an old incarnation, use the RESET DATABASE TO INCARNATION command.

Note 4: I have a hot backup from yesterday. Try to do point-in-time recovery and get the following: RMAN-08031: released channel: d2 RMAN-08031: released channel: d3 RMAN-00571: =========================================================== RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS =============== RMAN-00571: =========================================================== RMAN-03002: failure during compilation of command RMAN-03013: command type: restore RMAN-03002: failure during compilation of command RMAN-03013: command type: IRESTORE RMAN-06004: ORACLE error from recovery catalog database: RMAN-20207: UNTIL TIME is before RESETLOGS time I do confirm that backupsets are in the catalog database. Here is my recovery script. run { allocate channel d1 type 'SBT_TAPE'; allocate channel d2 type 'SBT_TAPE'; allocate channel d3 type 'SBT_TAPE'; restore database until time "to_date('Aug 27 2001 02:00:00','Mon DD YYYY HH24:MI:SS')"; recover database until time "to_date('Aug 27 2001 1:25:00','Mon DD YYYY HH24:MI:SS')"; sql "alter database open resetlogs"; release channel d1; release channel d2; release channel d3; } ## If successful, need to run reset database below ##reset database __________________ goodhealth goodhealth View Public Profile Send a private message to goodhealth Find all posts by goodhealth #2 08-29-2001, 06:14 PM sambavan Senior Advisor Join Date: Oct 2000 Location: Saskatoon, SK, Canada Posts: 3,916 What is your oracle version, backup client that you use and etc? Need more information... Sam __________________ Thanx Sam

Life is a journey, not a destination!

sambavan View Public Profile Send a private message to sambavan Find all posts by sambavan #3 08-29-2001, 07:09 PM goodhealth Junior Member Join Date: Dec 2000 Posts: 75 Oracle 8.1.7 running on HP-UX 11. __________________ goodhealth goodhealth View Public Profile Send a private message to goodhealth Find all posts by goodhealth #4 08-30-2001, 03:17 AM vinit Senior Member Join Date: Oct 2000 Posts: 466 remove until time after recover -------------------------------------------------------------------------------Hi, This is because your restore is till 2. Try to give a restore till the point-in-time. Your script should b something like restore until time 1.25 recover database

RMAn will take the backups taken before 1.25 and apply the logs so as to make it recover till 1. Cheers __________________ Vinit Note 5: Tuesday, February 12, 2008 How to recover user errors There is traditional way to recover the user errors i.e drop table by doing incomplete recovery the table,in oracle 9i you can recover the user errors by flashback technologies flashback drop by using recycle bin,here i am not considering the space issues of recyclebin. SQL*Plus: Release 10.1.0.2.0 - Production on Wed Dec 12 15:02:09 2007 Copyright (c) 1982, 2004, Oracle.

All rights reserved.

Connected to: Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 - Production With the Partitioning, OLAP and Data Mining options SQL> shutdown immediate Database closed. Database dismounted. ORACLE instance shut down.

SQL> startup mount ORACLE instance started. Total System Global Area 171966464 Fixed Size 787988 Variable Size 145488364 Database Buffers 25165824 Redo Buffers 524288 Database mounted. SQL> alter database archivelog 2 /

bytes bytes bytes bytes bytes

Database altered. SQL> alter database open 2 / Database altered. SQL> create table t (a number) 2 / Table created. Recovery Manager: Release 10.1.0.2.0 - Production Copyright (c) 1995, 2004, Oracle.


RMAN> connect target sys/sys connected to target database: ORCL1 (DBID=1025591162) RMAN> backup database 2> ; Starting backup at 12-DEC-07 using target database controlfile instead of recovery catalog allocated channel: ORA_DISK_1 channel ORA_DISK_1: sid=139 devtype=DISK channel ORA_DISK_1: starting full datafile backupset channel ORA_DISK_1: specifying datafile(s) in backupset input datafile fno=00001 name=C:\ORACLE10G\ORACLE\ORADATA\ORCL1\ORCL1\SYSTEM01.D BF input datafile fno=00003 name=C:\ORACLE10G\ORACLE\ORADATA\ORCL1\ORCL1\SYSAUX01.D BF input datafile fno=00002 name=C:\ORACLE10G\ORACLE\ORADATA\ORCL1\ORCL1\UNDOTBS01. DBF input datafile fno=00004 name=C:\ORACLE10G\ORACLE\ORADATA\ORCL1\ORCL1\USERS01.DB F channel ORA_DISK_1: starting piece 1 at 12-DEC-07 channel ORA_DISK_1: finished piece 1 at 12-DEC-07 piece handle=C:\ORACLE\FLASH_RECOVERY_AREA\ORCL1\ORCL1\BACKUPSET\2007_12_12\O1_M F_NNNDF_TAG20071212T150529_3OZDMCGN_.BKP comment=NONE channel ORA_DISK_1: backup set complete, elapsed time: 00:01:36 channel ORA_DISK_1: starting full datafile backupset channel ORA_DISK_1: specifying datafile(s) in backupset including current controlfile in backupset including current SPFILE in backupset channel ORA_DISK_1: starting piece 1 at 12-DEC-07 channel ORA_DISK_1: finished piece 1 at 12-DEC-07 piece handle=C:\ORACLE\FLASH_RECOVERY_AREA\ORCL1\ORCL1\BACKUPSET\2007_12_12\O1_M F_NCSNF_TAG20071212T150529_3OZDPGX9_.BKP comment=NONE channel ORA_DISK_1: backup set complete, elapsed time: 00:00:20 Finished backup at 12-DEC-07

RMAN>

SQL> select to_char(sysdate,'DD-MM-YYYY:HH24:MI:SS') 2 from dual 3 / TO_CHAR(SYSDATE,'DD ------------------####### SQL> drop table t 2 / Table dropped. SQL> shutdown immediate Database closed. Database dismounted. ORACLE instance shut down. SQL> startup mount ORACLE instance started. Total System Global Area Fixed Size Variable Size Database Buffers Redo Buffers Database mounted. SQL>

171966464 787988 145488364 25165824 524288


Recovery Manager: Release 10.1.0.2.0 - Production Copyright (c) 1995, 2004, Oracle.


RMAN> connect target sys/sys connected to target database: ORCL1 (DBID=1025591162) RMAN> run 2> { 3> set until time "to_date('12-12-2007 15:09:42','DD-MM-YYYY HH24:MI:SS')"; 4> restore database; 5> recover database; 6> } executing command: SET until clause using target database controlfile instead of recovery catalog Starting restore at 12-DEC-07 allocated channel: ORA_DISK_1 channel ORA_DISK_1: sid=159 devtype=DISK channel ORA_DISK_1: starting datafile backupset restore channel ORA_DISK_1: specifying datafile(s) to restore from backup set restoring datafile 00001 to C:\ORACLE10G\ORACLE\ORADATA\ORCL1\ORCL1\SYSTEM01.DBF restoring datafile 00002 to C:\ORACLE10G\ORACLE\ORADATA\ORCL1\ORCL1\UNDOTBS01.DB F restoring datafile 00003 to C:\ORACLE10G\ORACLE\ORADATA\ORCL1\ORCL1\SYSAUX01.DBF restoring datafile 00004 to C:\ORACLE10G\ORACLE\ORADATA\ORCL1\ORCL1\USERS01.DBF channel ORA_DISK_1: restored backup piece 1

piece handle=C:\ORACLE\FLASH_RECOVERY_AREA\ORCL1\ORCL1\BACKUPSET\2007_12_12\O1_M F_NNNDF_TAG20071212T150529_3OZDMCGN_.BKP tag=TAG20071212T150529 channel ORA_DISK_1: restore complete Finished restore at 12-DEC-07 Starting recover at 12-DEC-07 using channel ORA_DISK_1 starting media recovery media recovery complete Finished recover at 12-DEC-07

RMAN> alter database open resetlogs 2> ; database opened RMAN> list incarnation 2> ; List of DB Key ------1 2 3

Database Incarnations Inc Key DB Name DB ID ------- -------- ---------------1 ORCL1 1025591162 2 ORCL1 1025591162 3 ORCL1 1025591162

STATUS Reset SCN Reset Time --- ---------- ---------PARENT 1 09-MAR-04 PARENT 318842 12-DEC-07<---------set this CURRENT 331575 12-DEC-07<-----its current

SQL> conn sys/sys as sysdba Connected. SQL> desc t Name Null? Type ----------------------------------------- -------- ---------------------------A NUMBER SQL> drop table t 2 / Table dropped. SQL> shutdown immediate Database closed. Database dismounted. ORACLE instance shut down. SQL> startup mount ORACLE instance started. Total System Global Area Fixed Size Variable Size Database Buffers Redo Buffers Database mounted. SQL>

171966464 787988 145488364 25165824 524288


Recovery Manager: Release 10.1.0.2.0 - Production Copyright (c) 1995, 2004, Oracle.


RMAN> connect target sys/sys connected to target database: ORCL1 (DBID=1025591162) RMAN> run 2> { 3> set until time "to_date('12-12-2007 15:09:42','DD-MM-YYYY HH24:MI:SS')"; 4> restore database; 5> recover database; 6> } executing command: SET until clause using target database controlfile instead of recovery catalog RMAN-00571: =========================================================== RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS =============== RMAN-00571: =========================================================== RMAN-03002: failure of set command at 12/12/2007 15:31:22 RMAN-20207: UNTIL TIME or RECOVERY WINDOW is before RESETLOGS time RMAN> shutdown immediate using target database controlfile instead of recovery catalog database dismounted Oracle instance shut down RMAN> startup nomount connected to target database (not started) Oracle instance started Total System Global Area

171966464 bytes

Fixed Size Variable Size Database Buffers Redo Buffers

787988 145488364 25165824 524288

bytes bytes bytes bytes

RMAN> restore controlfile from 'C:\oracle\flash_recovery_area\orcl1\ORCL1\BACKUPSET\2007_12_12\O 2> ; Starting restore at 12-DEC-07 allocated channel: ORA_DISK_1 channel ORA_DISK_1: sid=160 devtype=DISK channel ORA_DISK_1: restoring controlfile channel ORA_DISK_1: restore complete output filename=C:\ORACLE10G\ORACLE\ORADATA\ORCL1\ORCL1\CONTROL01.CTL output filename=C:\ORACLE10G\ORACLE\ORADATA\ORCL1\ORCL1\CONTROL02.CTL output filename=C:\ORACLE10G\ORACLE\ORADATA\ORCL1\ORCL1\CONTROL03.CTL Finished restore at 12-DEC-07 RMAN> startup mount database is already started database mounted RMAN> reset database to incarnation 2 2> ;

Starting implicit crosscheck backup at 12-DEC-07 released channel: ORA_DISK_1 allocated channel: ORA_DISK_1 channel ORA_DISK_1: sid=160 devtype=DISK Crosschecked 1 objects Finished implicit crosscheck backup at 12-DEC-07 Starting implicit crosscheck copy at 12-DEC-07 using channel ORA_DISK_1 Finished implicit crosscheck copy at 12-DEC-07 searching for all files in the recovery area cataloging files... cataloging done List of Cataloged Files ======================= File Name: C:\ORACLE\FLASH_RECOVERY_AREA\ORCL1\ORCL1\ARCHIVELOG\2007_12_12\O1_MF _1_5_3OZFHKNJ_.ARC File Name: C:\ORACLE\FLASH_RECOVERY_AREA\ORCL1\ORCL1\BACKUPSET\2007_12_12\O1_MF_ NCSNF_TAG20071212T150529_3OZDPGX9_.BKP database reset to incarnation 2

RMAN> run 2> { 3> set until time "to_date('12-12-2007 15:09:42','DD-MM-YYYY HH24:MI:SS')"; 4> restore database; 5> recover database; 6> } executing command: SET until clause ORACLE error from target database: ORA-19922: there is no parent row with id 0 and level 0 ORA-06512: at "SYS.X$DBMS_BACKUP_RESTORE", line 5038 ORA-06512: at line 1 RMAN-06900: WARNING: unable to generate V$RMAN_STATUS or V$RMAN_OUTPUT row RMAN-06901: WARNING: disabling update of the V$RMAN_STATUS and V$RMAN_OUTPUT row s Starting restore at 12-DEC-07 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 00001 to C:\ORACLE10G\ORACLE\ORADATA\ORCL1\ORCL1\SYSTEM01.DBF restoring datafile 00002 to C:\ORACLE10G\ORACLE\ORADATA\ORCL1\ORCL1\UNDOTBS01.DB F restoring datafile 00003 to C:\ORACLE10G\ORACLE\ORADATA\ORCL1\ORCL1\SYSAUX01.DBF restoring datafile 00004 to C:\ORACLE10G\ORACLE\ORADATA\ORCL1\ORCL1\USERS01.DBF channel ORA_DISK_1: restored backup piece 1 piece handle=C:\ORACLE\FLASH_RECOVERY_AREA\ORCL1\ORCL1\BACKUPSET\2007_12_12\O1_M F_NNNDF_TAG20071212T150529_3OZDMCGN_.BKP tag=TAG20071212T150529 channel ORA_DISK_1: restore complete Finished restore at 12-DEC-07 Starting recover at 12-DEC-07 using channel ORA_DISK_1 starting media recovery

archive log thread 1 sequence 5 is already on disk as file C:\ORACLE\FLASH_RECOV ERY_AREA\ORCL1\ORCL1\ARCHIVELOG\2007_12_12\O1_MF_1_5_3OZFHKNJ_.ARC archive log filename=C:\ORACLE\FLASH_RECOVERY_AREA\ORCL1\ORCL1\ARCHIVELOG\2007_1 2_12\O1_MF_1_5_3OZFHKNJ_.ARC thread=1 sequence=5 media recovery complete Finished recover at 12-DEC-07 RMAN> alter database open resetlogs 2> ; database opened Connected to: Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 - Production With the Partitioning, OLAP and Data Mining options SQL> desc t Name Null? Type ----------------------------------------- -------- ---------------------------A NUMBER SQL> select name from v$database 2 / NAME --------ORCL1

You can recover the same table by using flashback technologies its by default enabled at 10g ,you can recover table from recycle bin unless you dont purge it, Recycle Bin cat Index etc when you drop table. SQL> desc dept Name Null? ----------------------------------------- -------DEPTNO NOT NULL DNAME LOC

Type ---------------------------NUMBER(2) VARCHAR2(14) VARCHAR2(13)

SQL> show recyclebin ORIGINAL NAME RECYCLEBIN NAME OBJECT TYPE DROP TIME ---------------- ------------------------------ ------------ ------------------EMP BIN$z6oBZ3FARF+b2GD99UVNkQ==$0 TABLE 2008-02-11:23:19:28 SQL> drop table dept 2 / Table dropped. SQL> show recyclebin ORIGINAL NAME RECYCLEBIN NAME ---------------- -----------------------------DEPT BIN$aAEWEykwReeRQG4USzgP8w==$0 EMP BIN$z6oBZ3FARF+b2GD99UVNkQ==$0

OBJECT TYPE -----------TABLE TABLE

DROP TIME ------------------2008-02-12:15:59:12 2008-02-11:23:19:28

SQL> select object_name,original_name 2 from recyclebin 3 / OBJECT_NAME ORIGINAL_NAME ------------------------------ --------------------------------

BIN$5c4ILi0WRlq9WhqKxCr/1A==$0 BIN$z6oBZ3FARF+b2GD99UVNkQ==$0 BIN$m4hxC9lWQe67YFtifqXGNg==$0 BIN$aAEWEykwReeRQG4USzgP8w==$0

PK_EMP EMP PK_DEPT DEPT

SQL> flashback table "BIN$aAEWEykwReeRQG4USzgP8w==$0" to before drop 2 / Flashback complete. SQL> desc dept Name Null? ----------------------------------------- -------DEPTNO NOT NULL DNAME LOC

Type ---------------------------NUMBER(2) VARCHAR2(14) VARCHAR2(13)

Note 6. Subject: RMAN-20011 Error When Restoring And Recovering To An Earlier Incarnation Doc ID: Note:455677.1 Type: PROBLEM Last Revision Date: 23-AUG-2007 Status: MODERATED In this Document Symptoms Cause Solution References This document is being delivered to you via Oracle Support's Rapid Visibility (RaV) process, and therefore has not been subject to an independent technical review. Applies to: Oracle Server - Enterprise Edition - Version: 10.2.0.1.0 This problem can occur on any platform.

Symptoms Recovering to a previous incarnation after setting the database incarnation to a previous database incarnation and restoring the controlfile, mounting the controlfile, restoring and rec the database with an appropriate time, getting RMAN-20011 errors.

Cause This is a known issue that is documented in an unpublished BUG. The currently is no patch for t however, there is a workaround. The problem is RMAN does an implicit catalog of all files in flash Recovery area. This causes the current incarnation to change to latest incarnation. Solution The workaround is to execute "reset database to incarnation" command after mounting the restored control file or when ever RMAN-20011 is signalled. Note that it is "NOT" required to restore the database files again. Note 7. RESET DATABASESyntax reset::=

Description of the illustration reset.gif Purpose

To reset the incarnation of the target database in the RMAN repository, which means to do either

Inform RMAN that the SQL statement ALTER DATABASE OPEN RESETLOGS has been executed and that a ne

To reset the database to a previous incarnation. Typically, you would reset the incarnation when Restrictions and Usage Notes Execute RESET DATABASE only at the RMAN prompt. You must be connected to the target database.

A recovery catalog connection is optional. Unlike in catalog mode, RESET DATABASE in nocatalog m You must issue a RESET DATABASE command before you can use RMAN with a target database that has

If RMAN is connected NOCATALOG, then you can only specify TO INCARNATION if the database is moun

If RMAN is connected in CATALOG mode, then you can specify TO INCARNATION when the database is m Keywords and Parameters Syntax Element Description TO INCARNATION primary_key Changes the current incarnation to an older incarnation. Specify the Examples Resetting RMAN to a Previous Incarnation in NOCATALOG Mode: Example In NOCATALOG mode, you must mount a control file that knows about the incarnation that you want CONNECT TARGET / NOCATALOG # step 1: start and mount a control file that knows about the incarnation to which # you want to return. if the current control file does not know about it, then # you must restore an older control file STARTUP NOMOUNT; RESTORE CONTROLFILE UNTIL TIME 'SYSDATE-250'; ALTER DATABASE MOUNT; # step 2: obtain the primary key of old incarnation LIST INCARNATION OF DATABASE trgt; List of DB Key ------1 1

Database Incarnations Inc Key DB Name DB ID ------- -------- ------------2 TRGT 1334358386 116 TRGT 1334358386

STATUS ------PARENT CURRENT

Reset SCN ---------154381 154877

Reset Time ---------OCT 30 2001 16:02:12 OCT 30 2001 16:37:39

# step 3: in this example, reset database to incarnation key 2 RESET DATABASE TO INCARNATION 2; # step 4: restore and recover the database to a point before the RESETLOGS RESTORE DATABASE UNTIL SCN 154876; RECOVER DATABASE UNTIL SCN 154876; # step 5: make this incarnation the current incarnation and then list incarnations: ALTER DATABASE OPEN RESETLOGS; LIST INCARNATION OF DATABASE trgt;

List of Database Incarnations DB Key Inc Key DB Name DB ID STATUS ------- ------- -------- ---------------- ------1 2 TRGT 1334358386 PARENT 1 116 TRGT 1334358386 PARENT 1 311 TRGT 1334358386 CURRENT Resetting the Database After Incomplete Recovery:

Reset SCN ---------154381 154877 154877 Example

Reset Time ---------OCT 30 2001 16:02:12 OCT 30 2001 16:37:39 AUG 13 2002 17:17:03

This example assumes that an incomplete recovery or recovery with a backup control file was perf % rman target / catalog rman/rman@catdb Recovery Manager: Release 10.1.0.2.0 - Production Copyright (c) 1995, 2003, Oracle.


connected to target database: TRGT (DBID=1334531173) connected to recovery catalog database RMAN> RESYNC CATALOG; RMAN-00571: RMAN-00569: RMAN-00571: RMAN-03009: RMAN-20003:

=========================================================== =============== ERROR MESSAGE STACK FOLLOWS =============== =========================================================== failure of resync command on default channel at 11/01/2001 12:00:43 target database incarnation not found in recovery catalog

RMAN> RESET DATABASE; new incarnation of database registered in recovery catalog starting full resync of recovery catalog full resync complete

Note: Subject: ORA-600 [Krbodo_badend] On Rman Backup Doc ID: Note:412170.1 Type: PROBLEM Last Revision Date: 20-JUN-2008 Status: REVIEWED In this Document Symptoms Changes Cause Solution References Applies to: Oracle Server - Enterprise Edition - Version: 10.2.0.2 This problem can occur on any platform. RMAN backup Symptoms An RMAN backup fails with the internal error:

ORA-00600: internal error code, arguments: [krboDo_badend], [641], [], [], [], [], [], [] In some cases ORA-00600[krboReadBitmap_badbitmap] may appear. Call stack: ksedmp kgerinv kgeasnmierr krbounotread krbbaddb krbb1cf krbbtgb krbbpc krbibpc pevm_icd_cal pfrinstr_ICAL pfrrun_no_tool pfrrun plsql_run pricar pricbr prient2 Changes

RMAN full backup is the issue here. In 10.2.0.2 has been introduced a new feature that avoids h all the blocks of the datafile during full datafile backup. Starting with 10.2.0.2, if database compatibility is 10.0.2 full backups of locally managed data the space bitmaps so that only blocks that are currently allocated need to be read and included The validity of the backups reading the whole datafile or using the space bitmaps is the same (a

Cause The cause of this problem has been identified in Bug 5556503. The cause of the issue is that the As per Bug 5556503

this issue is fixed in 10.2.0.4.

Solution It's available only a workaround, actually: - use: backup BLOCKS ALL

BLOCKS ALL does not mean that all blocks will be backed up. It means that unused block optimizat

The tablespaces should be checked to avoid any other reason for this error using the dbms_space_

DBMS_SPACE_ADMIN.ASSM_TABLESPACE_VERIFY ( tablespace_name IN VARCHAR2, ts_option IN POSITIVE, segment_option IN POSITIVE DEFAULT NULL); With:

* TS_VERIFY_BITMAPS: The bitmaps are verified against the extents. This will detect bits that are marked used or free wrongly and will also detect multiple a Example: oradebug setmypid execute dbms_space_admin.assm_tablespace_verify('USERS',dbms_space_admin.TS_VERIFY_BITMAPS) oradebug tracefile_name

Or, if the tablespace is not ASSM, then: DBMS_SPACE_ADMIN.TABLESPACE_VERIFY ( tablespace_name IN VARCHAR2, verify_option IN POSITIVE DEFAULT TABLESPACE_VERIFY_BITMAP); Example: oradebug setmypid execute dbms_space_admin.tablespace_verify('USERS') oradebug tracefile_name

Although the documentation does not say that this will verify the metadata against file$ and con References Bug 5556503 - ORA-600 [KRBODO_BADEND] REPORTED IN ALERT.LOG DURING RMAN BACKUP

Errors ORA-600[KRBODO_BADEND] Keywords 'ASSM' 'LOCALLY~MANAGED'

'ORADEBUG'

'DBMS_SPACE_ADMIN.TABLESPACE_VERIFY'

Note: Applies to: Oracle Server - Enterprise Edition - Version: 8.1.7 to 10.2 This problem can occur on any platform. Symptoms - Doing an incomplete recovery and would end up with this error: recover automatic database until cancel using backup controlfile; ORA-00283: recovery session canceled due to errors ORA-19909: datafile 1 belongs to an orphan incarnation ORA-01110: data file 1: '/u67/oradata/SUREXP/system01.dbf'

- The following entry appears in the alert.log: Media Recovery Start Warning: Recovery target destination is in a sibling branch <<<<<< of the controlfile checkpoint. Recovery will only recover changes to datafiles. Fri May 16 13:14:36 2008 Media Recovery failed with error 19909 ORA-283 signalled during: ALTER DATABASE RECOVER automatic database until cancel using backup co Changes Database have been upgraded and the control file was restored from a pre-upgrade backup.

Cause The incarnation in the control file is listed wrongly. In other words, on the restore of the old This can be verified by issuing the following query: #NAME? -#NAME? set pagesize 20000 set linesize 1000 set pause off set serveroutput on set feedback on set echo on set numformat 999999999999999 Spool recover.lst show parameter pfile; archive log list; select * from v$backup; select file#, status, substr(name, 1, 70) from v$datafile; select distinct checkpoint_change# from v$datafile_header; -select status, resetlogs_change#, resetlogs_time, checkpoint_change#, to_char(checkpoint_time, 'DD-MON-YYYY HH24:MI:SS') as checkpoint_time, count(*)

from v$datafile_header group by status, resetlogs_change#, resetlogs_time, checkpoint_change#, checkpoint_time order by status, checkpoint_change#, checkpoint_time ; -select substr(name,1,60), recover, fuzzy, checkpoint_change#, resetlogs_change#, resetlogs_time from v$datafile_header; select name, open_mode, checkpoint_change#, ARCHIVE_CHANGE# from v$database; select GROUP#,THREAD#,SEQUENCE#,MEMBERS,ARCHIVED,STATUS,FIRST_CHANGE# from v$log; select GROUP#,substr(member,1,60) from v$logfile; select * from v$log_history; select * from v$recover_file; select * from v$recovery_log; select HXFIL File_num,substr(HXFNM,1,70) File_name,FHTYP Type,HXERR Validity, FHSCN SCN, FHTNM TABLESPACE_NAME,FHSTA status ,FHRBA_SEQ Sequence from X$KCVFH; select hxfil FileNo,FHSTA status from x$kcvfhall; spool off The output would show a mismatch of the incarnation value.

Solution This leaves us with no option except re-creating controlfile. Any other solution will not be a v 1. Please mount the instance to get a script sceleton for recreating the controlfile sql> startup mount .. sql> alter database backup controlfile to trace ;

this will create a tracefile in your 'udump' directory Please modify it to build a create contro see also Reference: ---------Note 1012929.6 How to Recreate the Controlfile 2. With new create controlfile please try again: o check all datafiles ONLINE sql> reocver database until cancel using backup controlfile apply all logs needed sql> alter databse open resetlogs ; References Note 1012929.6 - How to Recreate the Controlfile Keywords ORPHAN; ARCHIVE~LOG~LIST; CONTROLFILE; SCN; ARCHIVED~LOGS; RESETLOGS; TRACE;

Subject: How to recover and open the database if the archivelog required for recovery is either Doc ID: Note:465478.1 Type: HOWTO Last Revision Date: 28-MAY-2008 Status: PUBLISHED In this Document Goal Solution References

Applies to: Oracle Server - Enterprise Edition - Version: 8.1.7.4 to 11.1

Information in this document applies to any platform. Goal How to recover and open the database if the archivelog required for recovery is either missing,

Solution The assumption here is that we have exhausted all possible locations to find another good and va

directories defined in the LOG_ARCHIVE_DEST_n another directory in the same server or another server standby database RMAN backup OS backup If the archivelog is not found in any of the above mentioned locations, then the approach and st

For the SCN of the datafiles, it is important to know the mode of the database when the datafile

If the datafiles are restored from an online or hot backup, which means that the database is ope

However, if the datafiles are restored from an offline or cold backup, and the database is clean

The critical key thing here is to ensure that all of the online datafiles are synchronized in te select status, checkpoint_change#, to_char(checkpoint_time, 'DD-MON-YYYY HH24:MI:SS') as checkpoint_time, count(*) from v$datafile_header group by status, checkpoint_change#, checkpoint_time order by status, checkpoint_change#, checkpoint_time; The results of the above query must return one and only one row for the online datafiles, which

The results of the query above may return some offline datafiles. So, ensure that all of the req You may run the following query to determine the offline datafiles: select file#, name from v$datafile where file# in (select file# from v$datafile_header where status='OFFLINE'); You may issue the following SQL statement to change the status of the required datafile(s) from

alter database datafile online; If we are lucky that the required log sequence# is still available in the online redologs and th

set echo on feedback on pagesize 100 numwidth 16 alter session set nls_date_format = 'DD-MON-YYYY HH24:MI:SS'; select LF.member, L.group#, L.thread#, L.sequence#, L.status, L.first_change#, L.first_time, DF.min_checkpoint_change# from v$log L, v$logfile LF, (select min(checkpoint_change#) min_checkpoint_change# from v$datafile_header where status='ONLINE') DF where LF.group# = L.group# and L.first_change# >= DF.min_checkpoint_change#; If the above query returns no rows, because the V$DATABASE.CONTROLFILE_TYPE has a value of "BACK

select * from v$logfile; If you have tried to apply all of the online redolog members instead of an archivelog during the ORA-00279: change 189189555 generated at 11/03/2007 09:27:46 needed for thread 1 ORA-00289: suggestion : +BACKUP ORA-00280: change 189189555 for thread 1 is in sequence #428 Specify log: {=suggested | filename | AUTO | CANCEL} #NAME? ORA-00310: archived log contains sequence 503; sequence 428 required

ORA-00334: archived log: '+BACKUP/prmy/onlinelog/group_2.258.603422107' After trying all of the possible solutions mentioned above, but you still cannot open the databa

Option#1: Force open the database by setting the _ALLOW_RESETLOGS_CORRUPTION=TRUE in the init.or

Option#2: If you have a good and valid backup of the database, then restore the database from th

Option#3: Manually extract the data using the Oracle's Data Unloader (DUL), which is performed b References

Error RMAN-20052 Subject: Resync Catalog returns RMAN-20052: invalid datafile create SCN error Doc ID: Note:343213.1 Type: PROBLEM Last Revision Date: 02-DEC-2005 Status: MODERATED In this Document Symptoms Cause Solution References

--------------------------------------------------------------------------------

This document is being delivered to you via Oracle Support's Rapid Visibility (RaV) process, and

Applies to: Oracle Server - Enterprise Edition - Version: 9.2.0.6.0 This problem can occur on any platform. SymptomsResync catalog returns error: RMAN-00579: the following error occurred at 12/01/2005 13:19:26 RMAN-03006: non-retryable error occurred during execution of command: resync RMAN-12004: unhandled exception during command execution on channel default RMAN-20052: invalid datafile create SCN Screen shot RMAN-00579: RMAN-03006: RMAN-12004: RMAN-20052:

posted to the tar shows errors: the following error occurred at 12/01/2005 13:19:26 non-retryable error occurred during execution of command: resync unhandled exception during command execution on channel default invalid datafile create SCN

CauseInformation on datafile creation_scn is different between the catalog (RC_DATAFILE) and tar controlfile (V$DATAFILE). Cause was justified comparing queries between target and catalog. The following queries will return different creation_change# values for the file# in question: In Target database: select file#, creation_change# from v$datafile; select file#, creation_change# from v$datafile_header; /* the creation_change# between these views will be the same, but different from the catalog */ In Catalog database: select db_key, db_name, file#, creation_change# from RC_DATAFILE

SolutionTo implement the solution, please execute the following steps:

If this is the ONLY database registered in the catalog, can drop the catalog schema and recreate 1. Connect to sqlplus of the catalog database and execute: - Drop user RMAN cascade; #NAME? TEMRORARY TABLESPACE temp DEFAULT TABLESPACE tools QUOTA UNLIMITED ON tools; - GRANT RECOVERY_CATALOG_OWNER TO rman; - GRANT CONNECT, RESOURCE TO rman; 2. Connect to RMAN and create the catalog: rman TARGET / CATALOG rman/cat@catdb RMAN> Create catalog; 3. register the target: RMAN> Register database; If the catalog has multiple registered databases, unregister this target and reregister it: 1. execute dbms_rcvcat.unregisterdatabase (db_key, db_id) 2. register database;

Error on: How to restore rman backups made with sbt and TDPO to another host. Subject: How to achieve alternate client restore with RMAN and Tivoli Doc ID: Note:265595.1 Type: BULLETIN Last Revision Date: 23-MAY-2008 Status: REVIEWED *** This article is being delivered in Draft form and may contain errors. Please use the MetaLink "Feedback" button to advise Oracle of any issues related to this article. *** PURPOSE ------To describe how to achieve restores with TIVOLI DATAPROTECTION FOR ORACLE (TDPO) and Oracle Recovery Manager (RMAN) on a different host, than the backup host. The note is for TDPO releases only, which use TDPO_OPTFILE environment. i.e. Release 2.2.0 and above. (NOT for ADSM Connect Agent) SCOPE & APPLICATION ------------------This article is for DBA's and consultants who need the alternate client restore functionality. How to achieve alternate client restore with RMAN and Tivoli -----------------------------------------------------------The alternate client restore functionality means, that you want to restore backups on a different host like the backup host.

This is needed in several circumstances: #NAME? - Creation of a standby database using DUPLICATE DATABASE ... FOR STANDBY #NAME? #NAME? #NAME? Let's start with a simple example. We have a database called LISA, which is located on host "DBLIMS" and we want to restore a controlfile of LISA on a host called "DBQIS". We have copied the init.ora file of LISA and already started the instance in NOMOUNT state. First we check, if there is a backup of the controlfile available: $ rman target / catalog tsm/tsm@tsm Recovery Manager: Release 9.0.1.4.0 - 64bit Production (c) Copyright 2001 Oracle Corporation. All rights reserved. connected to target database: LISA (not mounted) connected to recovery catalog database RMAN> list backup of controlfile; List of Backup Sets =================== BS Key Type LV Size Device Type Elapsed Time Completion Time ------- ---- -- ---------- ----------- ------------ --------------1844 Full 4M SBT_TAPE 00:00:09 11-MAR-04 BP Key: 1845 Status: AVAILABLE Tag: Piece Name: c-1133799276-20040311-00 Controlfile Included: Ckp SCN: 4373374817 Ckp time: 11-MAR-04 Now we start the restore session: --------------------------------RMAN> run { allocate channel tivoli type 'SBT_TAPE' parms 'ENV=(tdpo_optfile=/usr/tivoli/tsm/client/oracle/bin64/tdpo.opt)'; restore controlfile; } allocated channel: tivoli channel tivoli: sid=11 devtype=SBT_TAPE channel tivoli: Tivoli Data Protection for Oracle: version 2.2.1.0 Starting restore at 12-MAR-04 channel tivoli: starting datafile backupset restore channel tivoli: restoring controlfile output filename=/prod/redo1/ctrl1LISA.dbf released channel: tivoli RMAN-00571: =========================================================== RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS =============== RMAN-00571: =========================================================== RMAN-00579: the following error occurred at 03/12/2004 12:16:26 RMAN-03002: failure during compilation of command RMAN-03013: command type: restore RMAN-03007: retryable error occurred during execution of command: IRESTORE RMAN-12004: unhandled exception during command execution on channel tivoli RMAN-10035: exception raised in RPC: ORA-19507: failed to retrieve sequential file, handle="c-1133799276-20040311-00", parms=""

ORA-27029: skgfrtrv: sbtrestore returned error RMAN-10031: ORA-19624 occurred during call to DBMS_BACKUP_RESTORE.RESTOREBACKUPPIECE This error message basically means, that the TSM server didn't find the backuppiece called "c-1133799276-20040311-00" ! So we verify the existence of this piece at TSM side with the following query: (See Note 120468.1 for background information) tsm: IS4KAPBK01_SERVER1> select * from backups where ll_name = 'c-1133799276-20040311-00' ANR2963W This SQL query may produce a very large result table, or may require a significant amount of time to compute. Do you wish to proceed? (Yes (Y)/No (N)) Y NODE_NAME: FILESPACE_NAME: FILESPACE_ID: STATE: TYPE: HL_NAME: LL_NAME: OBJECT_ID: BACKUP_DATE: DEACTIVATE_DATE: OWNER: CLASS_NAME:

DBLIMS_ORA /dblims_lisa 1 ACTIVE_VERSION FILE // c-1133799276-20040311-00 1662525 2004-03-11 17:46:19.000000 oracle DEFAULT

Here we see, that the backup piece is "owned" by node "DBLIMS_ORA", belongs to Filespace "dblims_lisa" (the "/" is automatically put in by TSM) and is basically still restoreable. (Status: ACTIVE_VERSION) Explanation: -----------All backups taken with RMAN are stored on the TSM Server together with the node name (TDPO_NODE) of the backup host. This information is stored in the TDPO client configuration file called "TDPO_OPTFILE", which is referenced by RMAN at allocating a tape (SBT_TAPE) channel: allocate channel tivoli type 'SBT_TAPE' parms 'ENV=(TDPO_OPTFILE=/usr/tivoli/tsm/client/oracle/bin64/tdpo.opt)'; OR in the persistent channel configuration template: CONFIGURE CHANNEL DEVICE TYPE 'SBT_TAPE' PARMS 'ENV=(TDPO_OPTFILE=/usr/tivoli/tsm/client/oracle/bin64/tdpo.opt)'; Please note, that TDPO_OPTFILE is case sensitive and must be provided in UPPERCASE only. Our client configuration file on host "dbqis": ********************************************************************* * Tivoli Storage Manager - Tivoli Data Protection for Oracle * * Sample tdpo.opt for the AIX TDP for Oracle ********************************************************************* DSMI_ORC_CONFIG DSMI_LOG

/usr/tivoli/tsm/client/oracle/bin/dsm.opt /var/adm/tsm

TDPO_FS TDPO_NODE

DBQIS_PROD DBQIS_ORA

*TDPO_OWNER TDPO_PSWDPATH

/usr/tivoli/tsm/client/oracle/bin

TDPO_DATE_FMT TDPO_NUM_FMT TDPO_TIME_FMT

3 1 1

*TDPO_MGMT_CLASS2 *TDPO_MGMT_CLASS3 *TDPO_MGMT_CLASS4

ORCBACKUP ORCBACKUP ORCBACKUP

In this example TDPO_NODE is called "DBQIS_ORA". The filespace "DBQIS_PROD" is used to logically separate the different database backups on this host. Since the backup piece 'c-1133799276-20040311-00' belongs to "DBLIMS_ORA", we cannot restore it with this client configuration. Solution: --------Create a separate client configuration, where the TDPO_NODE is named "DBLIMS_ORA" and the TDPO_FS is "dblims_lisa". First we need to create a separate TDPO_OPTFILE: -----------------------------------------------$ cd /usr/tivoli/tsm/client/oracle/bin64 The file is named "tdpo_DBLIMS.opt" in this case: ********************************************************************* * Tivoli Storage Manager - Tivoli Data Protection for Oracle * * Sample tdpo.opt for the AIX TDP for Oracle ********************************************************************* DSMI_ORC_CONFIG DSMI_LOG

/usr/tivoli/tsm/client/oracle/bin/dsm.opt /var/adm/tsm

TDPO_FS TDPO_NODE *TDPO_OWNER TDPO_PSWDPATH

dblims_lisa DBLIMS_ORA /usr/tivoli/tsm/client/oracle/bin

TDPO_DATE_FMT TDPO_NUM_FMT TDPO_TIME_FMT

3 1 1

Second we create a separate passwordfile for this configuration: ---------------------------------------------------------------# tdpoconf passw -tdpo_optfile=tdpo_DBLIMS.opt *************************************************************** * Tivoli Data Protection for Oracle Utility * * Password file initialization/update program * * ROOT privilege needed to update value * *************************************************************** Please enter current password: Please enter new password: Please reenter new password for verification:

ANS1025E (RC137)

Session rejected: Authentication failure

The password is node specific on TSM server and thus we have to use password for DBLIMS_ORA node !!! (We used it for DBQIS) After using correct TSM password it works: ANU0260I Password successfully changed. Now we got a new passwordfile "TDPO.dblims_ora" created for this node locally: # ls -ltr ../bin/TDPO.* -rw-r--r-1 root -rw-r--r-1 root

system system

48 Feb 25 08:38 ../bin/TDPO.dbqis_ora 48 Mar 12 12:29 ../bin/TDPO.dblims_ora

Now we retry the restore by using new tdpo_DBLIMS.opt: -----------------------------------------------------# rman target / catalog tsm/tsm@tsm Recovery Manager: Release 9.0.1.4.0 - 64bit Production (c) Copyright 2001 Oracle Corporation. All rights reserved. connected to target database: LISA (not mounted) connected to recovery catalog database RMAN> run { allocate channel tivoli type 'SBT_TAPE' parms 'ENV=(tdpo_optfile=/usr/tivoli/tsm/client/oracle/bin64/tdpo_DBLIMS.opt)'; restore controlfile; } allocated channel: tivoli channel tivoli: sid=11 devtype=SBT_TAPE channel tivoli: Tivoli Data Protection for Oracle: version 2.2.1.0 Starting restore at 12-MAR-04 channel tivoli: starting datafile backupset restore channel tivoli: restoring controlfile output filename=/prod/redo1/ctrl1LISA.dbf channel tivoli: restored backup piece 1 piece handle=c-1133799276-20040311-00 tag=null params=NULL channel tivoli: restore complete replicating controlfile input filename=/prod/redo1/ctrl1LISA.dbf output filename=/prod/redo2/ctrl2LISA.dbf output filename=/prod/app/oracle/product/9.0.1/dbs/ctrl3LISA.dbf Finished restore at 12-MAR-04 released channel: tivoli Hint: ----You can verify TDPO configuration with this command: ---------------------------------------------------# tdpoconf showenv -tdpo_optfile=tdpo_DBLIMS.opt TDP FOR ORACLE INFORMATION Version: 2 Release: 2 Level: 1 Sublevel: 0 Platform: 64bit TDP Oracle AIX

TSM SERVER INFORMATION Server Name: Server Address: Server Type: Server Port: Communication Method:

IS4KAPBK01_ORACLE 172.29.254.12 Windows 1500 TCP/IP

SESSION INFORMATION Owner Name: Node Name: Node Type: DSMI_DIR: DSMI_ORC_CONFIG: TDPO_OPTFILE: Password Directory: Compression:

DBLIMS_ORA >>> Correct ! TDP Oracle AIX /usr/tivoli/tsm/client/api/bin64 /usr/tivoli/tsm/client/oracle/bin/dsm.opt tdpo_DBLIMS.opt /usr/tivoli/tsm/client/oracle/bin FALSE

You can verify client authentication by querying TSM logfile: ------------------------------------------------------------# dsmadmc -id=oper -pass=oper -cons Tivoli Storage Manager Command Line Administrative Interface - Version 5, Release 1, Level 6.11 (C) Copyright IBM Corporation 1990, 2003 All Rights Reserved. Session established with server IS4KAPBK01_SERVER1: Windows Server Version 5, Release 2, Level 2.2 Server date/time: 2004-03-12 12:34:20 Last access: 2004-03-12 12:24:52 ANR0406I Session 2303 started for node DBLIMS_ORA (TDP Oracle AIX) (Tcp/Ip dbqis.domain.at(-32369)). If you still get node "DBQIS_ORA", then the new client configuration is not used. This can be the case, if you don't provide TDPO_OPTFILE in uppercase during channel allocation. RELATED DOCUMENTS ----------------Note 120468.1 How to Query Tivoli (ADSM) to Get Name and Status of RMAN Backups Note 183025.1 ORA-19507 When Restoring Using RMAN and Tivoli (TSM)

Error on: Performance problems rman:

Known RMAN Performance Problems - Metalink Note : 247611.1 This morning, I have come across of a recently revised Metalink Note : 247611.1 (revised on 27-0 The note has workaround for the following bugs: Bug 5219484 - CATALOG RESYNCS ARE VERY SLOW - ROUT TABLE HAS 6 MILLION ROWS + Bug 5047758 - RMAN SLOW PERFORMANCE ON EXIT (UPDATING STATUS STATISTICS) Bug 5247609 RMAN SLOW PERFORMANCE DURING REGISTER DATABASE/OPEN RESETLOGS Bug 1551773 RMAN RESTORE RUNS VERY SLOWLY WHEN THERE ARE MANY ROWS IN THE CKP TABLE Bug 2803823 RESYNC CATALOG AND MAINTENANCE COMMANDS ARE SLOW Bug 4013855 - RMAN CROSSCHECK IS TAKING A LONG TIME TO COMPLETE Note 339964.1 RMAN 8.1.7 Crosscheck Command Is Very Slow Bug 4548861 RMAN RESTORE ARCHIVELOG UNTIL SEQUENCE TAKES A LONG TIME TO COMPLETE Note 342999.1 First Resync on Production Host After Running RMAN on Bug 2876717 RMAN backup starts SLOW if READ-ONLY device is used Bug 1887868 RMAN RESYNC takes a long time with many tablespaces

Internal Bug 1712720 RMAN MAINTENANCE COMMANDS ARE VERY SLOW Bug 2174697 RMAN incremental backup performance degrades over time in NOCATALOG mode Bug 2385857 RMAN backup performance degrades over time for large DB_FILES Bug 2484250 RMAN CUMULATIVE BACKUP / MAINTENENCE may appear to hang (spin) Bug 2610596 Huge CPU usage from RMAN server when TAPE not mounted with BACKUP_TAPE_IO_SLAVE=TRUE Bug 2968394 RESYNC TAKES A LONG TIME TO COMPLETE : LARGE BP TABLE Bug 1407413 NO INDEX TO ON RI CONSTRAINT BETWEEN BS AND BDF Bug 4110489 RMAN SKIP READ ONLY TABLESPACE CLAUSE TAKING TOO MUCH TIME TO SKIP Bug 3966722 CONCURRENT RMAN BACKUPS WAIT ON TM ENQUEUE ON CKP TABLE Bug 2710321 X$KCCFN / V$DATAFILE much slower in 9i than 8i with many datafiles

Bug 5047758 - RMAN Performance Degradation Posted at Monday, June 18, 2007 Upon moving to 10g, you may experience poor performance when using RMAN with a 10g recovery cata Resync operations, in particular will run extremely slow, increasing the overall amount of time take to run to fruition. This is Bug #5047758 as documented in Metalink note 247611.1.

To remedy this, issue the following command and/or include it in any stored scripts you might be sql "alter session set optimizer_mode=RULE"; Or nest in your stored script or run block: CREATE SCRIPT SAMPLE_SCRIPT {sql "alter session set optimizer_mode=RULE";} Oracle also recommends that in response you gather the fixed objects' statistics via: exec dbms_stats.gather_fixed_objects_stats()on the recovery catalog database(s) in question. Labels: Bugs, RMAN

ved logs, and that will work in most cases ncement of this script.

probably look similar to the

le=/usr/tivoli/tsm/client/oracle/bin64/tdpo.opt)';

rent controlfile" command in upper script, it is much better

lfile available that has been

Admin\W2K2\Backup%d_DB_%u_%s_%p';

asis so that changes to the database structure perform partial and full resyncs implicitly, the follwoing command:

a whole database recovery can be achieved

ered. RMAN will apply archive logs the database is opened. like like the following for each of them:

to restore and recover this tablespace:

le=/usr/tivoli/tsm/client/oracle/bin64/tdpo.opt)'; le=/usr/tivoli/tsm/client/oracle/bin64/tdpo.opt)';

res and recovers it, then brings it back online:

dia management channel to use datafile copies les in tablespace TBS_1 to a different location:

ndividual current datafiles when the database open. over the datafile, and bring the datafile online.

NV=(tdpo_optfile=/usr/tivoli/tsm/client/oracle/bin64/tdpo.opt)'; NV=(tdpo_optfile=/usr/tivoli/tsm/client/oracle/bin64/tdpo.opt)';

pecified time, SCN or sequence number:

ify log sequence number

using the RESETLOGS option.

ly recover to the last SCN in the archived redo logs. to the online redo logs which are not present. y. To perform disaster recovery connect to RMAN:

would be produced once a redo log was referenced:

a new incarnation. The current incarnation

wing you to monitor you backups and maintain

.dbf to its default location, then RMAN restores that it finds with the same filename. then RMAN creates a datafile copy u run the following commands:

tmp/tools01.dbf';

t/tools01.dbf named

to /tmp/tools01.dbf in the control file, le as the current database file. For example:

ta/trgt/tools01.dbf';

ent ALTER DATABASE RENAME FILE.

t to force Oracle to switch out of

n the following SQL statement to force Oracle

rce that the current log to be archived,

or more backup sets or image copies. By default, ion is disk or a media manager.

ed redo log file, or control file. y are identical to the results of copying a file uring RMAN restore and recover operations, recovery techniques.

epository, run the RMAN BACKUP AS COPY command isk as image copies using rming a backup). he server session also performs actions such as opy in the RMAN repository.

IX dd command to create image copies, n the RMAN repository. You can use the CATALOG command ols in the RMAN repository.

a datafile or control file to its original location ies are chosen over backup sets because of the earch of files to be restored.

ile, and if you have an image copy of the datafile AN copy the image copy back to its old location.

ce, as a replacement for the datafile to be restored. t the image copy should now be treated as se is equivalent to issuing the SQL statement

RMAN, such as native operating system file copy commands n disk. These copies are known as user-managed copies

image copy and enter its metadata into the RMAN repository. up with the RESTORE or SWITCH commands.

which permit the creation of image copies by breaking RMAN of the existence of a new user-managed copy, notify RMAN when the copy is no longer available, before resilvering the mirror (not including other ATALOG command to update the recovery catalog

ce such as a tape drive. If you specify the file name space of the target instance evice that can store a datafile.

use third-party media management software, are supported by that software.

ups of archived redo logs.

k after backing them up to backup sets. exactly one copy of each specified log sequence number letes the specific file it backed up while leaving UT option, then RMAN backs up exactly one copy of each that log from all archive destinations. ion of archived redo logs in standby database configurations.

ed backup sets. Only backup sets that were created d up to any available device type.

to copy backup sets from disk to disk. manually allocate a non-disk channel.

ups among multiple media. For example, of the production backup schedule:

disk and tape. You can also duplex backups

location. For example, to keep more recent backups un the following command:

7' DELETE INPUT;

an a week ago from disk to tape, and then deletes o DELETE ALL INPUT; RMAN deletes all to four locations, then RMAN deletes

of files from disk or other media:

simply copy it as you would a backup database file RMAN RESTORE command to extract its contents. he RMAN BACKUP AS COPY command

t need to go into the operating system, iles into the appropriate directories. sion to restore the correct backups to either:

e name currently there

open and take the datafile to be restored offline. as datafile image copies and records them or of the RESTORE, SET NEWNAME, and SWITCH commands.

f online or archived redo logs pdate it to the current time to perform media recovery and

the media recovery. If it is not available, s metadata from the target database control file. n you can still recover the database t one autobackup of the Control file.

en. For Example, mount the Database ase when performing online tablespace recovery. to specify the time, SCN, natively, specify the UNTIL clause

it or bring recovered Tablespaces online.

ecovery, you can also use the RMAN BLOCKRECOVER ry recovers an individual corrupt datablock ll number of blocks require media recovery, er than whole datafiles.

command as follows:

hived log compromises recoverability a/recovery/archive/arch_1_870_617116679.arch

let RMAN catalog

and time of the mounted target database

n old incarnation current then restore the and mount it. You will need to do "startup nomount"

e intended incarnation current in the recovery catalog.

nd other backups are considered "obsolete". r otherwise not usable.

bsystem will deal with rman commands own retention period, and you need ow whats going on.

ssage similar like this:

hived log compromises recoverability a/recovery/archive/arch_1_817_617116679.arch

d from disk without being backed up, the rman catalog will not know issing archived redo logs. That will cause rman archived redo log backups

ed log compromises recoverability

logs to their original location and name, and let rman back them up. archivelog all”, like:

D:REDOARCHARCH_1038.DBF recid=1017 stamp=611103638

ackup file information from the media manager"s catalog.

hich is linked with Oracle. There should be additional messages quires contacting the media management vendor.

01/b10744/e24280.htm#ORA-27191)

man-03009, for example

_1 channel at date/time

ation (disk not found, no space, tape not loaded etc..)

alled "sbttest". This utility performs a simple test of the tempting to communicate with the media manager.

the command line:

equired parameter

ize block_count]>

r example, following is the description

he following table explains how to interpret the output:

anager is installed and can accept a data stream and

is not installed or it is not configured correctly.

path, and linked with Oracle by typing sbttest at the command line:

the online documentation.

bed in the online documentation. For example, enter the following

mple, this command tests datafile tbs_33.f of database PROD:

provides messages describing the failure.

roperly, and that LD_ r platform) includes the onal information on the

reate sequential

on file "/db200_backup/archive_log03/EDPP_ARCH0_21329_1_492222998",

ESTORE.BACKUPPIECECREATE

r 691 is corrupt in controlfile C:\ORACLE\ORA90\DATABASE\SNCFSUMMITDB.ORA ESTORE.BACKUPPIECECREATE

estore. I get the following:

f command: IRESTORE channel BackupTest in exclusive enqueue for datafile 1 ESTORE.RESTOREBACKUPPIECE

nd execution on channel %s

----------------

the accompanying error messages.

remental RMAN Backup

---------------------

s Rapid Visibility (RaV) process, and therefore has not been

channel ORA_DISK_1

---------------------

egato NetWorker and performed all of the steps o be very simple. The SA installed the 64-bit version r. However, we were running a 32-bit version of Oracle on it.

loaded. Call to dlopen for media library returned error.

install Media management module for Oracle.

a shared object library into process space. sage "ORA-27211: Failed to load Media Management Library" rrect library name. The SBT_LIBRARY keyword must be set e RMAN script. This keyword is not valid with the SEND command

the default search path for the libobk library.

the allocate channel statement.

is necessary to review the Oracle RMAN script and verify if SBT_LIBRARY should be libobk.sl for HP-UX 10, 11.00 and 11.11,

05/21/2005 16:39:17

a shared object library into process space. iled to load Media Management Library"

script to reference the HP-UX 11.23 library file directly:

statement. A restart of the Oracle instance

of the Oracle database

-line interface for replacing native RMAN commands,

backup_restore package:

ated by the dbmsbkrs.sql and prvtbkrs.plb scripts. S_RCVMAN, is automatically installed in every Oracle database with the Oracle database server and the operating system ns as directed by RMAN.”

s a PL/SQL procedure to normalize filenames on Windows NT platforms.”

store to recover a controlfile:

=ORA_RDCS_WEEKLY)',

bs\CTL1rdcs.ORA');

=> 'D:\ORACLE_BASE\datafiles\SYSTEM01.DBF'); => 'D:\ORACLE_BASE\datafiles\UNDOTBS.DBF'); e => 'D:\ORACLE_BASE\datafiles\MYSPACE.DBF'); => 'D:\ORACLE_BASE\RMAN_BACKUP\MYDB_DF_BCK05H2LLQP_1_1', params => null);

ORACLE_BASE\achive\');

=> 'D:\ORACLE_BASE\RMAN_BACKUP\MYDB_LOG_BCK0DH1JGND_1_1', params => null);

ntrolfiles\CONTROL01.CTL'); _Backup\MYDB_DF_BCK0BH1JBVA_1_1',done => done);



=> 'D:\ORACLE_BASE\datafiles\SYSTEM01.DBF'); => 'D:\ORACLE_BASE\datafiles\UNDOTBS.DBF'); e => 'D:\ORACLE_BASE\datafiles\MYSPACE.DBF'); => 'D:\ORACLE_BASE\RMAN_BACKUP\MYDB_DF_BCK05H2LLQP_1_1', params => null);

ORACLE_BASE\achive\');

=> 'D:\ORACLE_BASE\RMAN_BACKUP\MYDB_LOG_BCK0DH1JGND_1_1', params => null);

er package failed: ==================

135: connection lost contact

/network/log/sqlnet.log

*****************

10.2.0.3.0 - Production SC System/6000: Version 10.2.0.3.0 - Production BM/AIX RISC System/6000: Version 10.2.0.3.0 - Production

4.123)(PORT=35844))

he codes underneath this one.

og in the recovery catalog ========================

in the recovery catalog

the recovery catalog.

the recovery ca

y still show up in the list of archive logs

scheck command, I get the message: the recovery catalog

ink you should do a

n problem - RMAN-20242 - and is addressed in detail in MetaLink notes. g, or you could be using mismatched versions provide any version details).

s old and there are none, thats not an error. ce a log switch after a

g in a redo log if it gets lost.

RMAN-20242: specification does not match any archive log in the recovery catalog

ve log in the recovery catalog

rchivelog backup.

up the archive log.

ivelog in the Recovery Catalog

rying to backup archived log files.

backup archived log

e archived logs exist

ivelog in the recovery catalog ------------------------------

AN-20242: specificati

t the archived log

---------------------

ARC DEL COMPLETIO --- --- ---------

YES YES 25-JAN-01

ARCHIVE\PRODT001S01524.ARC'. Modify

5 6 7 8 9

stamp=420196579 stamp=420239024 stamp=420310708 stamp=420310800 stamp=420310879

_1_1 comment=NONE

IVE\PRODT001S01538.AR





g does not exist

Commands after Open Resetlogs

the recovery catalog

duce first entry in

ry for a current he target database,

the recovery catalog

rnal-mailing-lists-3/ibm-tsm-13/how-do-i-get-tdpo-to-send-controlfiles-to-sbt-tape-instead-o-88699/

_TAPE TO '%F'; # default K TO '%F'; # default

KUPSET; # default TO 1; # default

ct/10.2.0/db_1/dbs/snapcf_mydb.f'; # default

E=/opt/tivoli/tsm/client/oracle/bin64/tdpo.opt)';



I get TDPO to send controlfiles to SBT_TAPE instead o

---------------------

get TDPO to send controlfiles to SBT_TAPE instead o

---------------------

> evilphb.org> wrote:

I get TDPO to send controlfiles to SBT_TAPE instead o

---------------------

> evilphb.org> wrote:

-----------------

onth3 Months6 Months1 Year Oldest FirstNewest First

es are GMT - 8 Hours

ix-using-tdp-for.html

abase backups, and you know i'm first in line to take this task on.

the password file for me ig files and directories setup etup the Tape parameters: ENV=(TDPO_OPTFILE=/usr/tivoli/tsm/client/oracle/bin64/tdpo.opt) looked good, i went for my first test drive.

parms 'ENV=(TDPO_OPTFILE=/usr/tivoli/tsm/client/oracle/bin64/tdpo.opt)';

OEM page. It's a simple test consisting of: parms 'ENV=(TDPO_OPTFILE=/usr/tivoli/tsm/client/oracle/bin64/tdpo.opt)';

nnel at 05/22/2007 16:28:09

nt class name was not found."?

o Google. There is less than 30 results worldwide! I hate when that happens...

finally worked our way up to a SEV-1 analyst on a conference call and he figures it out.

_3, or _4) because I was only allocating one channel (these values are only used when more than one channel is o

: create 2 copies of all datafiles and 2 copies of all control files.

t class name was not found." error, the first question you need to ask yourself is, "Am i using duplex copies",

or oracle in one of the My cluster Node.

/05/2005 12:56:36

---------------------

ue with the Oracle relinking.

---------------------

ensure your tdpo.conf file states you want to use Lanfree option. When in doubt, view your environment with the

---------------------

command, After the relinkg still i am facing same prblem.

---------------------

---------------------

---------------------

/05/2005 12:56:36

's working directory.

---------------------

bk64.a, which is in turn linked to the libobk64.a in the TDPO installation.

log location and nodename.

nt/bin/dsm.sys, which contains a server stanza for the TDPO.

hannel at 08/02/2005 19:12:58

---------------------

le/bin64 directory and try again...

re RESETLOGS time

n Time Is Before Last RESETLOGS

ore RESETLOGS time carnation in the

the last RESETLOG

ore Last Resetlogs

s, the incarnation e incarnation of the

ith resetlogs, we carnation. Therefore

subsequent backups database incarnation.

n needs to be reset

gs, we need to reset

s for the

etlogs and a new

his database was 2

commands to restore on, then open the database g a previous incarnation n the database is reset to y picks up a right control st be mounted for datafiles

o just before data was lost

ring control file

matically resets the database rnation is the new incarnation

l allow a restore

Part No. A76990-01

----------------

---------------on time or RESETLOGS

restored to an old

recovery and get the following:

AN-20207: UNTIL TIME is

ere is my recovery script.

---------------------

re till the point-in-time.

logs so as to make it recover till 1.25. Hope you do have all the logs since then.

p table by doing incomplete recovery till the time before dropped lashback technologies ng the space issues of recyclebin.

RCL1\ORCL1\SYSTEM01.D

RCL1\ORCL1\SYSAUX01.D

RCL1\ORCL1\UNDOTBS01.

RCL1\ORCL1\USERS01.DB

UPSET\2007_12_12\O1_M

UPSET\2007_12_12\O1_M

YY HH24:MI:SS')";

L1\ORCL1\SYSTEM01.DBF

L1\ORCL1\UNDOTBS01.DB

L1\ORCL1\SYSAUX01.DBF

L1\ORCL1\USERS01.DBF

UPSET\2007_12_12\O1_M

12-DEC-07<---------set this 12-DEC-07<-----its current

---------------------

YY HH24:MI:SS')";

ea\orcl1\ORCL1\BACKUPSET\2007_12_12\O1_MF_NCSNF_TAG20071212T150529_3OZDPGX9_.BKP'

ELOG\2007_12_12\O1_MF

SET\2007_12_12\O1_MF_

YY HH24:MI:SS')";

and V$RMAN_OUTPUT row

L1\ORCL1\SYSTEM01.DBF

L1\ORCL1\UNDOTBS01.DB

L1\ORCL1\SYSAUX01.DBF

L1\ORCL1\USERS01.DBF

UPSET\2007_12_12\O1_M

C:\ORACLE\FLASH_RECOV

CL1\ARCHIVELOG\2007_1

---------------------

ies its by default ss you dont purge it, Recycle Bin catches receives database objects and its dependent objects i.e PK,

---------------------

- ------------------2008-02-11:23:19:28

- ------------------2008-02-12:15:59:12 2008-02-11:23:19:28

---------------------

An Earlier Incarnation

s Rapid Visibility (RaV) process,

base incarnation to a previous g the controlfile, restoring and recovering

BUG. The currently is no patch for the BUG, n implicit catalog of all files to change to latest incarnation.

" command after mounting

repository, which means to do either of the following actions:

TLOGS has been executed and that a new incarnation of the target database has been created. Note that if you run

you would reset the incarnation when performing incomplete recovery to a point before a RESETLOGS operation, or

g mode, RESET DATABASE in nocatalog mode changes the incarnation only for the current RMAN session.

RMAN with a target database that has been opened with the SQL statement ALTER DATABASE OPEN RESETLOGS option. If

O INCARNATION if the database is mounted and the control file contains a record of the prior incarnation. If you

TO INCARNATION when the database is mounted. If database is mounted, however, then the control file must have a

to an older incarnation. Specify the primary key of the DBINC record for the database incarnation. Run LIST INCA

about the incarnation that you want to recover. The following scenario makes an old incarnation of database trg

he incarnation to which know about it, then

OCT 30 2001 16:02:12 OCT 30 2001 16:37:39

then list incarnations:

OCT 30 2001 16:02:12 OCT 30 2001 16:37:39 AUG 13 2002 17:17:03

y with a backup control file was performed in NOCATALOG mode. Later, RMAN is started in CATALOG mode, but the RE

11/01/2001 12:00:43

nd], [641], [], [], [], [], [], []

f krbbtgb krbbpc krbibpc pevm_icd_call_common

ntroduced a new feature that avoids having to read

full backups of locally managed datafiles might use located need to be read and included in the backup. sing the space bitmaps is the same (as long as error us not signaled).

3. The cause of the issue is that the changes in the way the backup is performed is to use the space bitmaps to

It means that unused block optimization will not be used to speed up backup. Unused block optimization is a new for this error using the dbms_space_admin package.

ongly and will also detect multiple allocation of extents. The file metadata will be validated against file$ and

s_space_admin.TS_VERIFY_BITMAPS)

BLESPACE_VERIFY_BITMAP);

fy the metadata against file$ and control file.

DURING RMAN BACKUP

.TABLESPACE_VERIFY'

database until cancel using backup controlfile

ed from a pre-upgrade backup.

ther words, on the restore of the old copy of the controlfile (pre-upgrade), the incarnation was listed as 1. No

int_change#, checkpoint_time

#, resetlogs_change#, resetlogs_time

from v$database; ST_CHANGE# from v$log;

pe,HXERR Validity, quence from X$KCVFH;

e. Any other solution will not be a valid fix for the issue.

ecreating the controlfile

se modify it to build a create controlfile script and recreate the controlfile

RESETLOGS; TRACE;

velog required for recovery is either missing, lost or corrupted?

ired for recovery is either missing, lost or corrupted?

locations to find another good and valid copy or backup of the archivelog that we are looking for, which could b

d locations, then the approach and strategy on how to recover and open the database depends on the SCN (System C

ode of the database when the datafiles are backed up. That is whether the database is open, mounted or shutdown

which means that the database is open when the backup is taken, then we must apply at least the archivelog(s) o

old backup, and the database is cleanly shutdown before the backup is taken, that means that the database is eit

line datafiles are synchronized in terms of their SCN before we can normally open the database. So, run the foll

s checkpoint_time,

row for the online datafiles, which means that they are already synchronized in terms of their SCN. Otherwise,

files. So, ensure that all of the required datafiles are online, because we may not be able to recover later the

tus of the required datafile(s) from "OFFLINE" to "ONLINE":

ailable in the online redologs and the corresponding redolog member is still physically existing on disk, then w

CONTROLFILE_TYPE has a value of "BACKUP", then try to apply each of the redolog membes one at a time during the

s instead of an archivelog during the recovery, but you always received the ORA-00310 error, as shown in the exa

6 needed for thread 1

but you still cannot open the database, because the archivelog required for recovery is either missing, lost or

ETLOGS_CORRUPTION=TRUE in the init.ora. But there is no 100% guarantee that this will open the database. However

se, then restore the database from the said backup, and recover the database by applying up to the last availabl

Unloader (DUL), which is performed by Oracle Field Support at the customer site on the next business day and fo

le create SCN error

---------------------

s Rapid Visibility (RaV) process, and therefore has not been subject to an independent technical review.

f command: resync channel default

f command: resync channel default

een the catalog (RC_DATAFILE) and target

talog. The following queries will

e, but different from the catalog */

drop the catalog schema and recreate it:

r this target and reregister it:

ION FOR ORACLE (TDPO) n the backup host.

want to restore

d LISA, which is le of LISA on a

d the instance in controlfile available:

.

etion Time ---------R-04

1-MAR-04

in64/tdpo.opt)';

2.2.1.0

============ ============ ============ 6:26

mmand: IRESTORE channel tivoli

c-1133799276-20040311-00", parms=""

ESTORE.RESTOREBACKUPPIECE

n't find the backuppiece

he following query:

ame = 'c-1133799276-20040311-00'

able, or may require a

MS_ORA", belongs by TSM) and is

gether with the file called "TDPO_OPTFILE",

in64/tdpo.opt)';

bin64/tdpo.opt)';

e provided in UPPERCASE only.

************ cle

************

opt

ace "DBQIS_PROD" s on this host.

o "DBLIMS_ORA",

is named "DBLIMS_ORA"

cle

************

opt

****** * * * ******

e

ave to use password

this node locally:

bin/TDPO.dbqis_ora bin/TDPO.dblims_ora

.

in64/tdpo_DBLIMS.opt)';

n 2.2.1.0

L

ISA.dbf

dsm.opt

e 1, Level 6.11 ved.

ws

4-03-12 12:24:52

acle AIX) (Tcp/Ip

iguration is not LE in uppercase

atus of RMAN Backups

link Note : 247611.1 (revised on 27-02.2007) which addressed several known performance problems with RMAN backup

S 6 MILLION ROWS +

NY ROWS IN THE CKP TABLE

LONG TIME TO COMPLETE

er time in NOCATALOG mode

ounted with BACKUP_TAPE_IO_SLAVE=TRUE

O MUCH TIME TO SKIP

with many datafiles

n using RMAN with a 10g recovery catalog. ncreasing the overall amount of time your backup jobs

it in any stored scripts you might be using:

d objects' statistics via: catalog database(s) in question.

bt-tape-instead-o-88699/

used when more than one channel is opened to TSM, otherwise, it used the channel appropriately named "DEFAULT")

self is, "Am i using duplex copies", if so, i'll bet you don't have a value for "TDPO_MGMT_CLASS_2" in your tdpo

doubt, view your environment with the "showenv" variable. Oh For the likes of me, since is midnight on a Tues, I

dependent objects i.e PK,

as been created. Note that if you run the RMAN command ALTER DATABASE OPEN RESETLOGS (not the SQL statement with

oint before a RESETLOGS operation, or when attempting to undo the affects of a RESETLOGS by restoring backups ta

he current RMAN session.

ER DATABASE OPEN RESETLOGS option. If you do not, then RMAN refuses to access the recovery catalog because it ca

cord of the prior incarnation. If you do not run RESET DATABASE, RMAN recovers to the last incarnation recorded

r, then the control file must have a record of the prior incarnation.

e database incarnation. Run LIST INCARNATION OF DATABASE to obtain possible key values. After you issue RESET DA

es an old incarnation of database trgt current again:

s started in CATALOG mode, but the RESYNC command fails because the incarnation has not been reset in the catalo

ormed is to use the space bitmaps to find the blocks that are currently allocated and need to be included in the

p. Unused block optimization is a new feature in 10.2.0.2, with BLOCKS ALL the backup will be done in the same w

a will be validated against file$ and control file. This is the default option (1).

, the incarnation was listed as 1. Now the real database incarnation is 2, so a mismatch appeared.

hat we are looking for, which could be in one of the following:

database depends on the SCN (System Change Number) of the datafiles, as well as, whether the log sequence# requi

atabase is open, mounted or shutdown (normally) when the backup is taken.

st apply at least the archivelog(s) or redolog(s) whose log sequence# are generated from the beginning and until

, that means that the database is either not open, is in nomount mode or mounted when the backup is taken, then

y open the database. So, run the following SQL statement, as shown below, to determine whether the datafiles are

ed in terms of their SCN. Otherwise, if the results return more than one row for the online datafiles, then the

may not be able to recover later the offline datafile once we open the database in resetlogs. Even though we ca

l physically existing on disk, then we may apply them instead of the archivelog. To confirm, issue the following

olog membes one at a time during the recovery. You may run the following query to determine the redolog members:

ORA-00310 error, as shown in the example below, then the log sequence# required for recovery is no longer avail

r recovery is either missing, lost or corrupted, or the corresponding log sequence# is no longer available in th

this will open the database. However, once the database is opened, then we must immediately rebuild the databas

e by applying up to the last available archivelog. In this option, we will only recover the database up to the l

site on the next business day and for an extra charge. If the customer wants to pursue this approach, we need t

independent technical review.

performance problems with RMAN backup and recovery.

hannel appropriately named "DEFAULT").

for "TDPO_MGMT_CLASS_2" in your tdpo.opt file.

of me, since is midnight on a Tues, I cannot think of the command.

RESETLOGS (not the SQL statement with the same keywords), then RMAN resets the target database automatically so

f a RESETLOGS by restoring backups taken before a RESETLOGS.

ss the recovery catalog because it cannot distinguish between a RESETLOGS operation and an accidental restore of

ers to the last incarnation recorded in the control file.

key values. After you issue RESET DATABASE TO INCARNATION, then you can run RMAN commands such as RESTORE and R

tion has not been reset in the catalog.

ocated and need to be included in the backup The backup seems to misread the space and then raises the ORA-600 e

the backup will be done in the same way that it was done in 10.2.0.1 and previous versions.

so a mismatch appeared.

l as, whether the log sequence# required for the recovery is still available in the online redologs.

enerated from the beginning and until the completion of the said backup that was used to restore the datafiles.

unted when the backup is taken, then the datafiles are already synchronized in terms of their SCN. In this situa

o determine whether the datafiles are synchronized or not. Take note that we query the V$DATAFILE_HEADER, becaus

w for the online datafiles, then the datafiles are still not synchronized yet. In this case, we need to apply ar

abase in resetlogs. Even though we can recover the database beyond resetlogs for the Oracle database starting fr

elog. To confirm, issue the following query, as shown below, that is to determine the redolog member(s) that you

ery to determine the redolog members:

uired for recovery is no longer available in the online redolog.

equence# is no longer available in the online redolog, since they are already overwritten during the redolog swi

must immediately rebuild the database. Database rebuild means doing the following, namely: (1) perform a full-d

only recover the database up to the last archivelog that is applied, and any data after that are lost. If no arc

ts to pursue this approach, we need the complete name, phone# and email address of the person who has the author

the target database automatically so that you do not have to run RESET DATABASE. By resetting the database, RMA

peration and an accidental restore of an old control file. The RESET DATABASE command informs RMAN that you iss

n RMAN commands such as RESTORE and RECOVER.

e space and then raises the ORA-600 error.

evious versions.

e in the online redologs.

t was used to restore the datafiles.

in terms of their SCN. In this situation, we can immediately open the database without even applying archivelo

e query the V$DATAFILE_HEADER, because we want to know the SCN recorded in the header of the physical datafile,

et. In this case, we need to apply archivelog(s) or redolog(s) to synchronize all of the online datafiles. By t

s for the Oracle database starting from 10g and later versions due to the introduction of the format "%R" in th

ermine the redolog member(s) that you can apply to recover the database:

dy overwritten during the redolog switches, then we cannot normally open the database, since the datafiles are

llowing, namely: (1) perform a full-database export, (2) create a brand new and separate database, and finally

y data after that are lost. If no archivelogs are applied at all, then we can only recover the database from th

ress of the person who has the authority to sign the work order in behalf of the customer.

BASE. By resetting the database, RMAN considers the new incarnation as the current incarnation of the database.

SE command informs RMAN that you issued a RESETLOGS command.

base without even applying archivelogs, because the datafiles are already in a consistent state, except if ther

the header of the physical datafile, and not the V$DATAFILE, which derives the information from the controlfile

ze all of the online datafiles. By the way, take note of the CHECKPOINT_TIME in the V$DATAFILE_HEADER, which in

ntroduction of the format "%R" in the LOG_ARCHIVE_FORMAT, it is recommended that you online the required datafi

e database, since the datafiles are in an inconsistent state. So, the following are the 3 options available to

and separate database, and finally (3) import the recent export dump. This option can be tedious and time cons

an only recover the database from the backup that is restored. However, if we restored from an online or hot ba

current incarnation of the database.

n a consistent state, except if there is a requirement to roll the database forward to a point-in-time after th the information from the controlfile.

E in the V$DATAFILE_HEADER, which indicates the date and time how far the datafiles have been recovered. that you online the required datafiles now than after the database is open in resetlogs to avoid any possible

wing are the 3 options available to allow you to open the database:

option can be tedious and time consuming, but once we successfully open the new database, then we expect minim

we restored from an online or hot backup, then we may not be able to open the database, because we still need t

e forward to a point-in-time after the said backup is taken.

datafiles have been recovered.

n in resetlogs to avoid any possible problems. However, in some cases, we intentionally offline the datafile(s),

he new database, then we expect minimal or perhaps no data loss at all. Before you try this option, ensure that

the database, because we still need to apply the archivelogs generated during the said backup in order to synchr

ntentionally offline the datafile(s), because we are doing a partial database restore, or perhaps we don't need

ore you try this option, ensure that you have a good and valid backup of the current database.

ng the said backup in order to synchronize the SCN of the datafiles before we can normally open the database.

se restore, or perhaps we don't need the contents of the said datafile.

e current database.

we can normally open the database.

Some important dictionary views and queries in target database relate (should be empty)

V$BACKUP_CORRUPTION RECID STAMP SET_STAMP SET_COUNT PIECE# FILE# BLOCK# BLOCKS CORRUPTION_CHANGE# MARKED_CORRUPT CORRUPTION_TYPE

V$LOGFILE GROUP# STATUS TYPE MEMBER IS_RECOVERY_DEST_FILE

V$LOG GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARCHIVED STATUS FIRST_CHANGE# FIRST_TIME

V$BACKUP_SET RECID STAMP SET_STAMP SET_COUNT BACKUP_TYPE CONTROLFILE_INCLUDED INCREMENTAL_LEVEL PIECES START_TIME COMPLETION_TIME ELAPSED_SECONDS BLOCK_SIZE INPUT_FILE_SCAN_ONLY KEEP KEEP_UNTIL KEEP_OPTIONS

V$DATAFILE

FILE# CREATION_CHANGE# CREATION_TIME TS# RFILE# STATUS ENABLED CHECKPOINT_CHANGE# CHECKPOINT_TIME UNRECOVERABLE_CHANGE# UNRECOVERABLE_TIME LAST_CHANGE# LAST_TIME OFFLINE_CHANGE# ONLINE_CHANGE# ONLINE_TIME BYTES BLOCKS BLOCK_SIZE NAME PLUGGED_IN BLOCK1_OFFSET AUX_NAME FIRST_NONLOGGED_SCN FIRST_NONLOGGED_TIME

V$BACKUP FILE# STATUS CHANGE# TIME

V$LOG_HISTORY RECID STAMP THREAD# SEQUENCE# FIRST_CHANGE# FIRST_TIME NEXT_CHANGE# RESETLOGS_CHANGE# RESETLOGS_TIME

V$DATAFILE_HEADER FILE# STATUS ERROR FORMAT RECOVER FUZZY CREATION_CHANGE# CREATION_TIME TABLESPACE_NAME TS# RFILE# RESETLOGS_CHANGE# RESETLOGS_TIME CHECKPOINT_CHANGE# CHECKPOINT_TIME CHECKPOINT_COUNT BYTES BLOCKS NAME SPACE_HEADER LAST_DEALLOC_SCN

You might use the following queries to inspect backups and possible errors: SELECT V1.GROUP#, MEMBER, SEQUENCE#, FIRST_CHANGE# FROM V$LOG V1, V$LOGFILE V2 WHERE V1.GROUP# = V2.GROUP# ; This will list all your online redolog files and their respective sequence and first change numbers. If you want to know if you can recover from the Online logs (archive mode or non archive mode),

SELECT FILE#, CHANGE# FROM V$RECOVER_FILE; If the CHANGE# is GREATER than the minimum FIRST_CHANGE# of your logs, a datafile can be recovered from the online logs even if the database. runs in NONArchive mode.

SELECT * FROM v$backup; SELECT file#, status, substr(name, 1, 70), checkpoint_change# FROM v$datafile; SELECT file#, status, checkpoint_change# FROM v$datafile_header; SELECT status,resetlogs_change#,resetlogs_time,checkpoint_change#, to_char(checkpoint_time, 'DD-MON-YYYY HH24:MI:SS') as checkpoint_time,count(*) FROM v$datafile_header group by status, resetlogs_change#, resetlogs_time, checkpoint_change#, checkpoint_time order by status, checkpoint_change#, checkpoint_time ;

The results of the above query must return one and only one row for the online datafiles, which in terms of their SCN. Otherwise, if the results return more than one row for the online datafil not synchronized yet. In this case, we need to apply archivelog(s) or redolog(s) to synchronize By the way, take note of the CHECKPOINT_TIME in the V$DATAFILE_HEADER, which indicates the date

SELECT substr(name,1,60), recover, fuzzy, checkpoint_change#, resetlogs_change#, resetlo FROM v$datafile_header; SELECT name, open_mode, checkpoint_change#, ARCHIVE_CHANGE# FROM v$database; SELECT GROUP#,THREAD#,SEQUENCE#,MEMBERS,ARCHIVED,STATUS,FIRST_CHANGE# FROM v$log; SELECT GROUP#,substr(member,1,70) FROM v$logfile; SELECT * FROM v$log_history; SELECT * FROM v$recover_file; SELECT * FROM v$recovery_log; SELECT first_change#, next_change#, sequence#, archived, substr(name, 1, 40) FROM V$ARCHIVED_LOG; SELECT HXFIL File_num,substr(HXFNM,1,70) File_name,FHTYP Type,HXERR Validity, FHSCN SCN, FHTNM TABLESPACE_NAME,FHSTA status ,FHRBA_SEQ Sequence FROM X$KCVFH; SELECT hxfil FileNo,FHSTA status FROM x$kcvfhall; SELECT LF.member, L.group#, L.thread#, L.sequence#, L.status, L.first_change#, L.first_time, DF.min_checkpoint_change# FROM v$log L, v$logfile LF, (select min(checkpoint_change#) min_checkpoint_change# from v$datafile_header where status='ONLINE') DF WHERE LF.group# = L.group#

AND

L.first_change# >= DF.min_checkpoint_change#;

If the above query returns no rows, because the V$DATABASE.CONTROLFILE_TYPE has a value of "BACK of the redolog membes one at a time during the recovery. You may run the following query to dete select from where and and (

al.sequence# v$archived_log al, v$log rl al.sequence# = rl.sequence# (+) al.thread# = rl.thread# (+) rl.status = 'INACTIVE' or rl.status is null

) and al.deleted = 'NO' order by al.sequence# The above command should show all archived redologs which are ready to be backupped with rman if rman also deletes the input. SELECT RECOVERY_ESTIMATED_IOS FROM V$INSTANCE_RECOVERY; The above sql gives you an idea about how many IO needs to be performed at recovery.

Examples: SQL> SELECT * FROM v$backup; FILE# ---------1 2 3 4 5 6 7 8

STATUS CHANGE# TIME ------------------ ---------- --------NOT ACTIVE 0 NOT ACTIVE 0 NOT ACTIVE 0 NOT ACTIVE 0 NOT ACTIVE 0 NOT ACTIVE 0 NOT ACTIVE 0 NOT ACTIVE 0

8 rows selected. SQL> SELECT file#, FROM v$datafile; 2 FILE# STATUS ---------- ------1 SYSTEM 2 ONLINE 3 ONLINE 4 ONLINE 5 ONLINE 6 ONLINE 7 ONLINE 8 ONLINE

status, substr(name, 1, 70)

SUBSTR(NAME,1,70) --------------------------------------------------------------------/dbms/tdbaprod/prodroca/database/default/system01.dbf /dbms/tdbaprod/prodroca/database/default/undotbs01.dbf /dbms/tdbaprod/prodroca/database/default/sysaux01.dbf /dbms/tdbaprod/prodroca/database/default/users01.dbf /dbms/tdbaprod/prodroca/database/rocade/roc_rmlive_data01.dbf /dbms/tdbaprod/prodroca/database/rocade/roc_rmlive_indx01.dbf /dbms/tdbaprod/prodroca/database/rocade/roc_rmwork_data01.dbf /dbms/tdbaprod/prodroca/database/rocade/roc_rmwork_indx01.dbf

8 rows selected. SQL> SELECT file#, status, checkpoint_change# FROM v$datafile_header;

2 FILE# ---------1 2 3 4 5 6 7 8

STATUS CHECKPOINT_CHANGE# ------- -----------------ONLINE 20344051 ONLINE 20344051 ONLINE 20344051 ONLINE 20344051 ONLINE 20344051 ONLINE 20344051 ONLINE 20344051 ONLINE 20344051

8 rows selected. SQL> 2 3 4 5

SELECT status,resetlogs_change#,resetlogs_time,checkpoint_change#, to_char(checkpoint_time, 'DD-MON-YYYY HH24:MI:SS') as checkpoint_time,count( FROM v$datafile_header group by status, resetlogs_change#, resetlogs_time, checkpoint_change#, checkpoint_ order by status, checkpoint_change#, checkpoint_time ;

STATUS RESETLOGS_CHANGE# RESETLOGS CHECKPOINT_CHANGE# CHECKPOINT_TIME COUNT(*) ------- ----------------- --------- ------------------ -------------------- ---------ONLINE 1 18-JUN-07 20344051 30-JUN-2008 08:20:56 8

SQL> SELECT substr(name,1,60), recover, fuzzy, checkpoint_change#, resetlogs_change#, re FROM v$datafile_header; 2 SUBSTR(NAME,1,60) REC FUZ CHECKPOINT_CHANGE# ------------------------------------------------------------ --- --- -----------------/dbms/tdbaprod/prodroca/database/default/system01.dbf NO YES 20344051 /dbms/tdbaprod/prodroca/database/default/undotbs01.dbf NO YES 20344051 /dbms/tdbaprod/prodroca/database/default/sysaux01.dbf NO YES 20344051 /dbms/tdbaprod/prodroca/database/default/users01.dbf NO YES 20344051 /dbms/tdbaprod/prodroca/database/rocade/roc_rmlive_data01.db NO YES 20344051 /dbms/tdbaprod/prodroca/database/rocade/roc_rmlive_indx01.db NO YES 20344051 /dbms/tdbaprod/prodroca/database/rocade/roc_rmwork_data01.db NO YES 20344051 /dbms/tdbaprod/prodroca/database/rocade/roc_rmwork_indx01.db NO YES 20344051 8 rows selected. SQL> SELECT name, open_mode, checkpoint_change#, ARCHIVE_CHANGE# FROM v$database; 2 NAME OPEN_MODE CHECKPOINT_CHANGE# ARCHIVE_CHANGE# --------- ---------- ------------------ --------------PRODROCA READ WRITE 20344051 20324343 SQL> SELECT GROUP#,THREAD#,SEQUENCE#,MEMBERS,ARCHIVED,STATUS,FIRST_CHANGE# FROM v$log; GROUP# THREAD# SEQUENCE# MEMBERS ARC STATUS FIRST_CHANGE# ---------- ---------- ---------- ---------- --- ---------------- ------------1 1 901 1 YES INACTIVE 20324346 2 1 902 1 NO CURRENT 20344051 3 1 898 1 YES INACTIVE 20280936 4 1 899 1 YES INACTIVE 20280948 5 1 900 1 YES INACTIVE 20324332

SQL> SELECT GROUP#,substr(member,1,70) FROM v$logfile; GROUP# ---------1 2 3 4 5

SUBSTR(MEMBER,1,70) ---------------------------------------------------------------------/dbms/tdbaprod/prodroca/recovery/redo_logs/redo01.log /dbms/tdbaprod/prodroca/recovery/redo_logs/redo02.log /dbms/tdbaprod/prodroca/recovery/redo_logs/redo03.log /dbms/tdbaprod/prodroca/recovery/redo_logs/redo04.log /dbms/tdbaprod/prodroca/recovery/redo_logs/redo05.log

SQL> SELECT * FROM v$recover_file; no rows selected SQL> SELECT * FROM v$recovery_log; no rows selected SQL> SELECT HXFIL File_num,substr(HXFNM,1,70) File_name,FHTYP Type,HXERR Validity, FHSCN SCN, FHTNM TABLESPACE_NAME,FHSTA status ,FHRBA_SEQ Sequence FROM X$KCVFH; 2 3 FILE_NUM ---------1 2 3 4 5 6 7 8

FILE_NAME TYPE V ---------------------------------------------------------------------- ---------- --/dbms/tdbaprod/prodroca/database/default/system01.dbf 3 /dbms/tdbaprod/prodroca/database/default/undotbs01.dbf 3 /dbms/tdbaprod/prodroca/database/default/sysaux01.dbf 3 /dbms/tdbaprod/prodroca/database/default/users01.dbf 3 /dbms/tdbaprod/prodroca/database/rocade/roc_rmlive_data01.dbf 3 /dbms/tdbaprod/prodroca/database/rocade/roc_rmlive_indx01.dbf 3 /dbms/tdbaprod/prodroca/database/rocade/roc_rmwork_data01.dbf 3 /dbms/tdbaprod/prodroca/database/rocade/roc_rmwork_indx01.dbf 3

8 rows selected. SQL> 2 3 4 5 6 7 8

SELECT LF.member, L.group#, L.thread#, L.sequence#, L.status, L.first_change#, L.first_time, DF.min_checkpoint_change# FROM v$log L, v$logfile LF, (select min(checkpoint_change#) min_checkpoint_change# from v$datafile_header where status='ONLINE') DF WHERE LF.group# = L.group# AND L.first_change# >= DF.min_checkpoint_change#;

MEMBER -------------------------------------------------------------------------------GROUP# THREAD# SEQUENCE# STATUS FIRST_CHANGE# FIRST_TIM ---------- ---------- ---------- ---------------- ------------- --------MIN_CHECKPOINT_CHANGE# ---------------------/dbms/tdbaprod/prodroca/recovery/redo_logs/redo02.log 2 1 902 CURRENT 20344051 30-JUN-08 2E+07

s in target database related to backup information V$CONTROLFILE STATUS NAME IS_RECOVERY_DEST_FILE BLOCK_SIZE FILE_SIZE_BLKS (should be empty)

V$RECOVER_FILE FILE# ONLINE ONLINE_STATUS ERROR CHANGE# TIME

(should be empty)

V$DATABASE_BLOCK_CORRUPTION FILE# BLOCK# BLOCKS CORRUPTION_CHANGE# CORRUPTION_TYPE

V$ARCHIVED_LOG

V$BACKUP_REDOLOGS

RECID STAMP NAME DEST_ID THREAD# SEQUENCE# RESETLOGS_CHANGE# RESETLOGS_TIME RESETLOGS_ID FIRST_CHANGE# FIRST_TIME NEXT_CHANGE# NEXT_TIME BLOCKS BLOCK_SIZE CREATOR REGISTRAR STANDBY_DEST ARCHIVED APPLIED DELETED STATUS COMPLETION_TIME DICTIONARY_BEGIN DICTIONARY_END END_OF_REDO BACKUP_COUNT ARCHIVAL_THREAD# ACTIVATION# IS_RECOVERY_DEST_FILE

RECID STAMP SET_STAMP SET_COUNT THREAD# SEQUENCE# RESETLOGS_CHANGE# RESETLOGS_TIME FIRST_CHANGE# FIRST_TIME NEXT_CHANGE# NEXT_TIME BLOCKS BLOCK_SIZE

and possible errors:

ective sequence and (archive mode or non archive mode), run:

(should be empty)

V$RECOVERY_LOG THREAD# SEQUENCE# TIME ARCHIVE_NAME

V$DATABASE DBID NAME CREATED RESETLOGS_CHANGE# RESETLOGS_TIME PRIOR_RESETLOGS_CHANGE# PRIOR_RESETLOGS_TIME LOG_MODE CHECKPOINT_CHANGE# ARCHIVE_CHANGE# CONTROLFILE_TYPE CONTROLFILE_CREATED CONTROLFILE_SEQUENCE# CONTROLFILE_CHANGE# CONTROLFILE_TIME OPEN_RESETLOGS VERSION_TIME OPEN_MODE PROTECTION_MODE PROTECTION_LEVEL REMOTE_ARCHIVE ACTIVATION# ARCHIVELOG_CHANGE# ARCHIVELOG_COMPRESSION RECOVERY_TARGET_INCARNATION# LAST_OPEN_INCARNATION# CURRENT_SCN FLASHBACK_ON

ogs even if the database.

S') as checkpoint_time,count(*)

eckpoint_change#, checkpoint_time

row for the online datafiles, which means that they are already synchronized e than one row for the online datafiles, then the datafiles are still elog(s) or redolog(s) to synchronize all of the online datafiles. ILE_HEADER, which indicates the date and time how far the datafiles have been recovered.

hange#, resetlogs_change#, resetlogs_time

S,FIRST_CHANGE# FROM v$log;

d, substr(name, 1, 40)

YP Type,HXERR Validity, FHRBA_SEQ Sequence

CONTROLFILE_TYPE has a value of "BACKUP", then try to apply each u may run the following query to determine the redolog members:

re ready to be backupped with

be performed at recovery.

----------------------------------fault/system01.dbf fault/undotbs01.dbf fault/sysaux01.dbf fault/users01.dbf cade/roc_rmlive_data01.dbf cade/roc_rmlive_indx01.dbf cade/roc_rmwork_data01.dbf cade/roc_rmwork_indx01.dbf

eckpoint_change#, :MI:SS') as checkpoint_time,count(*)

e, checkpoint_change#, checkpoint_time CHECKPOINT_TIME COUNT(*) -------------------- ---------30-JUN-2008 08:20:56 8

int_change#, resetlogs_change#, resetlogs_time

REC ------ --NO NO NO NO a01.db NO x01.db NO a01.db NO x01.db NO

FUZ CHECKPOINT_CHANGE# RESETLOGS_CHANGE# RESETLOGS --- ------------------ ----------------- --------YES 20344051 1 18-JUN-07 YES 20344051 1 18-JUN-07 YES 20344051 1 18-JUN-07 YES 20344051 1 18-JUN-07 YES 20344051 1 18-JUN-07 YES 20344051 1 18-JUN-07 YES 20344051 1 18-JUN-07 YES 20344051 1 18-JUN-07

STATUS,FIRST_CHANGE# FROM v$log;

FIRST_CHANGE# ---------- ------------VE 20324346 T 20344051 VE 20280936 VE 20280948 VE 20324332

---------------------------

e,FHTYP Type,HXERR Validity, FHRBA_SEQ Sequence

TYPE VALIDITY SCN ---------------------- ---------- ---------- ---------------1.dbf 3 0 20344051 01.dbf 3 0 20344051 1.dbf 3 0 20344051 .dbf 3 0 20344051 ve_data01.dbf 3 0 20344051 ve_indx01.dbf 3 0 20344051 rk_data01.dbf 3 0 20344051 rk_indx01.dbf 3 0 20344051

---------------------

TABLESPACE_NAME STATUS SEQUEN ------------------------------ ---------- -------SYSTEM 8196 9 UNDOTBS1 4 9 SYSAUX 4 9 USERS 4 9 ROC_RMLIVE_DATA 4 9 ROC_RMLIVE_INDX 4 9 ROC_RMWORK_DATA 4 9 ROC_RMWORK_INDX 4 9

TUS

RECOVERY_DEST_FILE CK_SIZE E_SIZE_BLKS

ID ME EATED SETLOGS_CHANGE# SETLOGS_TIME IOR_RESETLOGS_CHANGE# IOR_RESETLOGS_TIME G_MODE

CHIVE_CHANGE# NTROLFILE_TYPE NTROLFILE_CREATED NTROLFILE_SEQUENCE# NTROLFILE_CHANGE# NTROLFILE_TIME EN_RESETLOGS RSION_TIME EN_MODE OTECTION_MODE OTECTION_LEVEL MOTE_ARCHIVE TIVATION# CHIVELOG_CHANGE# CHIVELOG_COMPRESSION COVERY_TARGET_INCARNATION# ST_OPEN_INCARNATION# RRENT_SCN ASHBACK_ON

V$INSTANCE_RECOVERY RECOVERY_ESTIMATED_IOS ACTUAL_REDO_BLKS TARGET_REDO_BLKS LOG_FILE_SIZE_REDO_BLKS LOG_CHKPT_TIMEOUT_REDO_BLKS LOG_CHKPT_INTERVAL_REDO_BLKS FAST_START_IO_TARGET_REDO_BLKS TARGET_MTTR ESTIMATED_MTTR CKPT_BLOCK_WRITES OPTIMAL_LOGFILE_SIZE ESTD_CLUSTER_AVAILABLE_TIME WRITES_MTTR WRITES_LOGFILE_SIZE WRITES_LOG_CHECKPOINT_SETTINGS WRITES_OTHER_SETTINGS WRITES_AUTOTUNE WRITES_FULL_THREAD_CKPT

ME STATUS SEQUENCE ----------------- ---------- ---------8196 902 4 902 4 902 4 902 TA 4 902 DX 4 902 TA 4 902 DX 4 902

Some Disaster Recovery Scenarios Article 1.

Oracle Article. Recover a datafile with missing archivelogs

Subject: RECOVER A DATAFILE WITH MISSING ARCHIVELOGS Doc ID: Note:418476.1 Type: PROBLEM Last Revision Date: 23-NOV-2007 Status: REVIEWED In this Document Symptoms Cause Solution

Applies to: Oracle Server - Enterprise Edition - Version: 8.1.7.4.0 to 10.2.0.4 This problem can occur on any platform.

Symptoms Database cannot be opened, because a datafile checkpoint is lagging behind from the rest of the datafiles. Cause A datafile was restored from a previous backup, but archivelogs required to recover the said datafile are m Solution There are 3 options available, as shown below:

Option#1: Restore the database from the same backupset, and then recover it by applying up to the last avai but any updates to the database after the point-in-time of recovery will be lost.

Option#2: Force open the database by setting the _ALLOW_RESETLOGS_CORRUPTION=TRUE in the init.ora. But ther that we can open the database. However, once the database is opened, then you must immediately rebuild the Database rebuild means doing the following, namely: (1) perform a full-database export, (2) create a new an and finally (3) import the recent export dump.

Option#3: Manually extract the data using the Oracle's Data Unloader (DUL), which is performed by Oracle Fi

Note by Antapex: never rely completely on your physical backup. Make sure you have a good and recent export If you make use of exp (or imp) in combination with mknod to create a pipe, and using gzip or compress, you can typically create small exports from even quite large databases. For example, a 500GB database can be ex As an example, a script could contain code like: Create a compressed export on the fly. # create a named pipe mknod exp.pipe p # read the pipe - output to zip file in the background gzip < exp.pipe > scott.exp.gz & # feed the pipe exp userid=scott/tiger file=exp.pipe

Article 2.

TEST:

Incomplete Recovery to a point in time: up to a certain SCN

Suppose we have target database test10g Suppose we have the rman catalog in database rman Suppose we have user "albert" in test10g which has created the table PERSON. At this moment there were NO BACKUPS taken: SQL> select * from person;

PERS_ID ---------1 2

PERS_NAME --------------------db was never gebackupped before 1st backup

The SCN at that time is 549794. But ofcourse the SCN is always increasing. >>> Now we run our first backup: run { allocate channel t1 type disk; backup full database ; backup (spfile) (current controlfile) ; sql 'alter system archive log current'; backup archivelog all delete input ; release channel t1; } Some time after the backup, albert does an insert. SQL> select * from person; PERS_ID ---------1 2 3

PERS_NAME ---------------------------db was never gebackupped before 1st backup after 1ste backup

>>> Now we run our second backup: run { allocate channel t1 type disk; backup full database ; backup (spfile) (current controlfile) ; sql 'alter system archive log current'; backup archivelog all delete input ; release channel t1; } Some time after the second backup, albert does an insert. SQL> select * from person; PERS_ID ---------1 2 3 4

PERS_NAME ---------------------------db was never gebackupped before 1st backup after 1ste backup after 2nd backup

>>> Now we run our third backup: run { allocate channel t1 type disk; backup full database ; backup (spfile) (current controlfile) ; sql 'alter system archive log current'; backup archivelog all delete input ; release channel t1; } SQL> SELECT DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER() from dual; DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER()

----------------------------------------572936 Now we want to restore to SCN where there were only two records in the PERSON table: The target instance of database "test10g" is started, but not mounted or opened. RMAN> run { 2> allocate channel t1 type disk; 3> set until scn 549794; 4> restore controlfile; 5> alter database mount; 6> restore database ; 7> recover database ; 8> release channel t1; 9> alter database open resetlogs; 10> } allocated channel: t1 channel t1: sid=156 devtype=DISK executing command: SET until clause Starting restore at 27-JUN-08 released channel: t1 RMAN-00571: =========================================================== RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS =============== RMAN-00571: =========================================================== RMAN-03002: failure of restore command at 06/27/2008 23:10:43 RMAN-06026: some targets not found - aborting restore RMAN-06024: no backup or copy of the control file found to restore Because we also tried to restore the controlfile, at SCN=549794, rman tells us that there is no backup at that specific SCN. Lets see if we can find an SCN "close" to what we want. RMAN> list backup of controlfile; List of Backup Sets ===================

BS Key Type LV Size Device Type Elapsed Time Completion Time ------- ---- -- ---------- ----------- ------------ --------------47 Full 6.80M DISK 00:00:03 27-JUN-08 BP Key: 49 Status: AVAILABLE Compressed: NO Tag: TAG20080627T103023 Piece Name: C:\ORACLE\FLASH_RECOVERY_AREA\TEST10G\BACKUPSET\2008_06_27\O1_MF_NCSNF_TAG20 Control File Included: Ckp SCN: 549830 Ckp time: 27-JUN-08

BS Key Type LV Size Device Type Elapsed Time Completion Time ------- ---- -- ---------- ----------- ------------ --------------61 Full 6.77M DISK 00:00:02 27-JUN-08 BP Key: 65 Status: AVAILABLE Compressed: NO Tag: TAG20080627T103209 Piece Name: C:\ORACLE\FLASH_RECOVERY_AREA\TEST10G\BACKUPSET\2008_06_27\O1_MF_NCNNF_TAG20 Control File Included: Ckp SCN: 549850 Ckp time: 27-JUN-08


BS Key Type LV Size Device Type Elapsed Time Completion Time ------- ---- -- ---------- ----------- ------------ --------------137 Full 6.77M DISK 00:00:02 27-JUN-08 BP Key: 141 Status: AVAILABLE Compressed: NO Tag: TAG20080627T104755 Piece Name: C:\ORACLE\FLASH_RECOVERY_AREA\TEST10G\BACKUPSET\2008_06_27\O1_MF_NCNNF_TAG20 Control File Included: Ckp SCN: 550302 Ckp time: 27-JUN-08


BS Key Type LV Size Device Type Elapsed Time Completion Time ------- ---- -- ---------- ----------- ------------ --------------228 Full 6.77M DISK 00:00:02 27-JUN-08 BP Key: 232 Status: AVAILABLE Compressed: NO Tag: TAG20080627T111442 Piece Name: C:\ORACLE\FLASH_RECOVERY_AREA\TEST10G\BACKUPSET\2008_06_27\O1_MF_NCNNF_TAG20 Control File Included: Ckp SCN: 551032 Ckp time: 27-JUN-08 RMAN> So lets try the restore with SCN=549830 RMAN> run { 2> allocate channel t1 type disk; 3> set until scn 549830; 4> restore controlfile; 5> alter database mount; 6> restore database ; 7> recover database ; 8> release channel t1; 9> alter database open resetlogs; 10> } allocated channel: t1 channel t1: sid=156 devtype=DISK executing command: SET until clause Starting restore at 27-JUN-08

channel t1: starting datafile backupset restore channel t1: restoring control file channel t1: reading from backup piece C:\ORACLE\FLASH_RECOVERY_AREA\TEST10G\BACKUPSET\2008_06_27 23_469DWKVT_.BKP channel t1: restored backup piece 1 piece handle=C:\ORACLE\FLASH_RECOVERY_AREA\TEST10G\BACKUPSET\2008_06_27\O1_MF_NCSNF_TAG20080627T 0080627T103023 channel t1: restore complete, elapsed time: 00:00:05 output filename=C:\ORACLE\ORADATA\TEST10G\CONTROL01.CTL output filename=C:\ORACLE\ORADATA\TEST10G\CONTROL02.CTL output filename=C:\ORACLE\ORADATA\TEST10G\CONTROL03.CTL Finished restore at 27-JUN-08 database mounted Starting restore at 27-JUN-08 Starting implicit crosscheck backup at 27-JUN-08 Crosschecked 1 objects Finished implicit crosscheck backup at 27-JUN-08 Starting implicit crosscheck copy at 27-JUN-08 Finished implicit crosscheck copy at 27-JUN-08

searching for all files in the recovery area cataloging files... cataloging done

List of Cataloged Files ======================= File Name: C:\ORACLE\FLASH_RECOVERY_AREA\TEST10G\BACKUPSET\2008_06_27\O1_MF_ANNNN_TAG20080627T10 File Name: C:\ORACLE\FLASH_RECOVERY_AREA\TEST10G\BACKUPSET\2008_06_27\O1_MF_ANNNN_TAG20080627T10 File Name: C:\ORACLE\FLASH_RECOVERY_AREA\TEST10G\BACKUPSET\2008_06_27\O1_MF_ANNNN_TAG20080627T11 File Name: C:\ORACLE\FLASH_RECOVERY_AREA\TEST10G\BACKUPSET\2008_06_27\O1_MF_NCNNF_TAG20080627T10 File Name: C:\ORACLE\FLASH_RECOVERY_AREA\TEST10G\BACKUPSET\2008_06_27\O1_MF_NCNNF_TAG20080627T10 File Name: C:\ORACLE\FLASH_RECOVERY_AREA\TEST10G\BACKUPSET\2008_06_27\O1_MF_NCNNF_TAG20080627T11 File Name: C:\ORACLE\FLASH_RECOVERY_AREA\TEST10G\BACKUPSET\2008_06_27\O1_MF_NCSNF_TAG20080627T10 File Name: C:\ORACLE\FLASH_RECOVERY_AREA\TEST10G\BACKUPSET\2008_06_27\O1_MF_NCSNF_TAG20080627T10 File Name: C:\ORACLE\FLASH_RECOVERY_AREA\TEST10G\BACKUPSET\2008_06_27\O1_MF_NCSNF_TAG20080627T11 File Name: C:\ORACLE\FLASH_RECOVERY_AREA\TEST10G\BACKUPSET\2008_06_27\O1_MF_NNNDF_TAG20080627T10 File Name: C:\ORACLE\FLASH_RECOVERY_AREA\TEST10G\BACKUPSET\2008_06_27\O1_MF_NNNDF_TAG20080627T11 File Name: C:\ORACLE\FLASH_RECOVERY_AREA\TEST10G\BACKUPSET\2008_06_27\O1_MF_NNSNF_TAG20080627T10 File Name: C:\ORACLE\FLASH_RECOVERY_AREA\TEST10G\BACKUPSET\2008_06_27\O1_MF_NNSNF_TAG20080627T10 File Name: C:\ORACLE\FLASH_RECOVERY_AREA\TEST10G\BACKUPSET\2008_06_27\O1_MF_NNSNF_TAG20080627T11

channel t1: starting datafile backupset restore channel t1: specifying datafile(s) to restore from backup set restoring datafile 00001 to C:\ORACLE\ORADATA\TEST10G\SYSTEM01.DBF restoring datafile 00002 to C:\ORACLE\ORADATA\TEST10G\UNDOTBS01.DBF restoring datafile 00003 to C:\ORACLE\ORADATA\TEST10G\SYSAUX01.DBF restoring datafile 00004 to C:\ORACLE\ORADATA\TEST10G\USERS01.DBF restoring datafile 00005 to C:\ORACLE\ORADATA\TEST10G\TEST01.DBF channel t1: reading from backup piece C:\ORACLE\FLASH_RECOVERY_AREA\TEST10G\BACKUPSET\2008_06_27 23_469DSJF5_.BKP channel t1: restored backup piece 1 piece handle=C:\ORACLE\FLASH_RECOVERY_AREA\TEST10G\BACKUPSET\2008_06_27\O1_MF_NNNDF_TAG20080627T 0080627T103023 channel t1: restore complete, elapsed time: 00:01:25 Finished restore at 27-JUN-08 Starting recover at 27-JUN-08 starting media recovery

channel t1: starting archive log restore to default destination channel t1: restoring archive log archive log thread=1 sequence=11 channel t1: reading from backup piece C:\ORACLE\FLASH_RECOVERY_AREA\TEST10G\BACKUPSET\2008_06_27 27_469DXG4D_.BKP channel t1: restored backup piece 1 piece handle=C:\ORACLE\FLASH_RECOVERY_AREA\TEST10G\BACKUPSET\2008_06_27\O1_MF_ANNNN_TAG20080627T 0080627T103227 channel t1: restore complete, elapsed time: 00:00:01 archive log filename=C:\ORACLE\ORADATA\LOG\ARC00011_0658480420.001 thread=1 sequence=11 media recovery complete, elapsed time: 00:00:02 Finished recover at 27-JUN-08 released channel: t1 database opened new incarnation of database registered in recovery catalog starting full resync of recovery catalog full resync complete RMAN> Now we return to database test10g.

SQL> select * from person; PERS_ID ---------1 2

PERS_NAME --------------------db was never gebackupped before 1st backup

SQL>

SQL> SELECT DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER() from dual; DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER() ----------------------------------------553255

Article 3.

TEST:

Incomplete Recovery to a point in time: up to a certain SCN

This example is quite similar to the example above. But there is a difference. This time, we have set the following default in rman: RMAN> CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 1 DAYS;

This will mark all backups older than one day as obsolete. Now in this example we are going to t if we still can use a backup that‟s several days old. Lets first list our backups: RMAN> list backup of database; List of Backup Sets ===================

BS Key Type LV Size Device Type Elapsed Time Completion Time ------- ---- -- ---------- ----------- ------------ --------------24 Full 498.62M DISK 00:01:24 28-JUN-08 BP Key: 26 Status: AVAILABLE Compressed: NO Tag: TAG20080628T012918 Piece Name: C:\ORACLE\FLASH_RECOVERY_AREA\TEST10G\BACKUPSET\2008_06_28\O1_MF_NNNDF_TAG20 List of Datafiles in backup set 24 File LV Type Ckp SCN Ckp Time Name ---- -- ---- ---------- --------- ---1 Full 545773 28-JUN-08 C:\ORACLE\ORADATA\TEST10G\SYSTEM01.DBF 2 Full 545773 28-JUN-08 C:\ORACLE\ORADATA\TEST10G\UNDOTBS01.DBF 3 Full 545773 28-JUN-08 C:\ORACLE\ORADATA\TEST10G\SYSAUX01.DBF 4 Full 545773 28-JUN-08 C:\ORACLE\ORADATA\TEST10G\USERS01.DBF 5 Full 545773 28-JUN-08 C:\ORACLE\ORADATA\TEST10G\TEST01.DBF

BS Key Type LV Size Device Type Elapsed Time Completion Time ------- ---- -- ---------- ----------- ------------ --------------88 Full 498.66M DISK 00:01:28 28-JUN-08 BP Key: 93 Status: AVAILABLE Compressed: NO Tag: TAG20080628T014827 Piece Name: C:\ORACLE\FLASH_RECOVERY_AREA\TEST10G\BACKUPSET\2008_06_28\O1_MF_NNNDF_TAG20 List of Datafiles in backup set 88 File LV Type Ckp SCN Ckp Time Name ---- -- ---- ---------- --------- ---1 Full 546326 28-JUN-08 C:\ORACLE\ORADATA\TEST10G\SYSTEM01.DBF 2 Full 546326 28-JUN-08 C:\ORACLE\ORADATA\TEST10G\UNDOTBS01.DBF 3 Full 546326 28-JUN-08 C:\ORACLE\ORADATA\TEST10G\SYSAUX01.DBF 4 Full 546326 28-JUN-08 C:\ORACLE\ORADATA\TEST10G\USERS01.DBF 5 Full 546326 28-JUN-08 C:\ORACLE\ORADATA\TEST10G\TEST01.DBF BS Key

Type LV Size

Device Type Elapsed Time Completion Time

------- ---- -- ---------- ----------- ------------ --------------186 Full 499.70M DISK 00:01:22 30-JUN-08 BP Key: 193 Status: AVAILABLE Compressed: NO Tag: TAG20080630T044903 Piece Name: C:\ORACLE\FLASH_RECOVERY_AREA\TEST10G\BACKUPSET\2008_06_30\O1_MF_NNNDF_TAG20 List of Datafiles in backup set 186 File LV Type Ckp SCN Ckp Time Name ---- -- ---- ---------- --------- ---1 Full 551460 30-JUN-08 C:\ORACLE\ORADATA\TEST10G\SYSTEM01.DBF 2 Full 551460 30-JUN-08 C:\ORACLE\ORADATA\TEST10G\UNDOTBS01.DBF 3 Full 551460 30-JUN-08 C:\ORACLE\ORADATA\TEST10G\SYSAUX01.DBF 4 Full 551460 30-JUN-08 C:\ORACLE\ORADATA\TEST10G\USERS01.DBF 5 Full 551460 30-JUN-08 C:\ORACLE\ORADATA\TEST10G\TEST01.DBF RMAN>

What we want to do now, is restore the backup of 28 june, while our latest backup is from 30 jun The first backup is, according to rman, obsolete, but we want to investigate whether we can use this backup in a simple restore/recovery action. Let's try: RMAN> run { 2> allocate channel t1 type disk; 3> set until scn 545773; 4> restore controlfile; 5> alter database mount; 6> restore database ; 7> recover database ; 8> release channel t1; 9> alter database open resetlogs; 10> } allocated channel: t1 channel t1: sid=157 devtype=DISK executing command: SET until clause Starting restore at 30-JUN-08 released channel: t1 RMAN-00571: =========================================================== RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS =============== RMAN-00571: =========================================================== RMAN-03002: failure of restore command at 06/30/2008 06:46:31 RMAN-06026: some targets not found - aborting restore RMAN-06024: no backup or copy of the control file found to restore Again, there is no controlfile of that specific SCN, so lets take a look at the backups of the controlfile with an SCN close to what we want. We are not going to list those backups, but take for granted that the command below shows us what we want to find: RMAN> list backup of controlfile; List of Backup Sets ===================

BS Key Type LV Size Device Type Elapsed Time Completion Time ------- ---- -- ---------- ----------- ------------ --------------25 Full 6.80M DISK 00:00:03 28-JUN-08 BP Key: 27 Status: AVAILABLE Compressed: NO Tag: TAG20080628T012918 Piece Name: C:\ORACLE\FLASH_RECOVERY_AREA\TEST10G\BACKUPSET\2008_06_28\O1_MF_NCSNF_TAG20 Control File Included: Ckp SCN: 545801 Ckp time: 28-JUN-08 BS Key Type LV Size Device Type Elapsed Time Completion Time ------- ---- -- ---------- ----------- ------------ ---------------

39

Full 6.77M DISK 00:00:02 28-JUN-08 BP Key: 43 Status: AVAILABLE Compressed: NO Tag: TAG20080628T013052 Piece Name: C:\ORACLE\FLASH_RECOVERY_AREA\TEST10G\BACKUPSET\2008_06_28\O1_MF_NCNNF_TAG20 Control File Included: Ckp SCN: 545820 Ckp time: 28-JUN-08

etc.. Etc.. So now we try: RMAN> run { 2> allocate channel t1 type disk; 3> set until scn 545801; 4> restore controlfile; 5> alter database mount; 6> restore database ; 7> recover database ; 8> release channel t1; 9> alter database open resetlogs; 10> } allocated channel: t1 channel t1: sid=157 devtype=DISK executing command: SET until clause Starting restore at 30-JUN-08 channel t1: starting datafile backupset restore channel t1: restoring control file channel t1: reading from backup piece C:\ORACLE\FLASH_RECOVERY_AREA\TEST1 18_46C1KQ3F_.BKP channel t1: restored backup piece 1 piece handle=C:\ORACLE\FLASH_RECOVERY_AREA\TEST10G\BACKUPSET\2008_06_28\O 0080628T012918 channel t1: restore complete, elapsed time: 00:00:03 output filename=C:\ORACLE\ORADATA\TEST10G\CONTROL01.CTL output filename=C:\ORACLE\ORADATA\TEST10G\CONTROL02.CTL output filename=C:\ORACLE\ORADATA\TEST10G\CONTROL03.CTL Finished restore at 30-JUN-08 database mounted Starting restore at 30-JUN-08 Starting implicit crosscheck backup at 30-JUN-08 Crosschecked 1 objects Finished implicit crosscheck backup at 30-JUN-08 Starting implicit crosscheck copy at 30-JUN-08 Finished implicit crosscheck copy at 30-JUN-08 searching for all files in the recovery area etc.. Etc.. Finished restore at 30-JUN-08 Starting recover at 30-JUN-08 starting media recovery channel t1: starting archive log restore to default destination channel t1: restoring archive log archive log thread=1 sequence=8 channel t1: reading from backup piece C:\ORACLE\FLASH_RECOVERY_AREA\TEST10G\BACKUPSET\2 04_46C1L9TC_.BKP channel t1: restored backup piece 1

piece handle=C:\ORACLE\FLASH_RECOVERY_AREA\TEST10G\BACKUPSET\2008_06_28\O1_MF_ANNNN_TAG 0080628T013104 channel t1: restore complete, elapsed time: 00:00:02 archive log filename=C:\ORACLE\ORADATA\LOG\ARC00008_0658542361.001 thread=1 sequence=8 media recovery complete, elapsed time: 00:00:01 Finished recover at 30-JUN-08 released channel: t1 database opened new incarnation of database registered in recovery catalog starting full resync of recovery catalog full resync complete So even with the small "retention period" of one day, we can restore a backup from several days

Article 4. TEST: Restore a database from a former incarnation.

If you did a recovery of a database to a certain point in time, or a certain SCN, then after the recovery, you must have opened the database with the "reset logs" clause. As far as rman "sees it", a new Database Incarnation has been created, and all backup start from scratch. The former backups just belong to the former Incarnation, and are detached from the new incarnation. You "just start again from 0" when you create new backups from the new incarnation. But, can we still use the former backups, if we really need to do so? Yes they can. Suppose we have target database prodross Suppose we have the rman catalog in database rman Suppose we have restored and recovered the target database to a prior SCN. In that case, we have opened the target with the "reset logs" clause, and a new incarnation was created. Lets see how this looks in rman RMAN> connect target / connected to target database: PRODROSS (DBID=1443222271) RMAN> connect catalog rman/rman@rman connected to recovery catalog database RMAN> list incarnation; List of DB Key ------1 1 1

Database Incarnations Inc Key DB Name DB ID ------- -------- ---------------8 PRODROSS 1443222271 2 PRODROSS 1443222271 345 PRODROSS 1443222271

STATUS Reset SCN Reset Time --- ---------- ---------PARENT 1 30-AUG-05 PARENT 534907 01-JUL-08 CURRENT 547426 01-JUL-08

If you were to open a sqlplus session to the RMAN database, you could have issued the following SQL query: SQL>select DBINC_KEY,DB_KEY,DB_NAME,RESET_SCN,RESET_TIME,DBINC_STATUS from DBINC; DBINC_KEY DB_KEY DB_NAME RESET_SCN RESET_TIM DBINC_ST ---------- ---------- -------- ---------- --------- -------2 1 PRODROSS 534907 01-JUL-08 PARENT

8 345

1 PRODROSS 1 PRODROSS

1 30-AUG-05 PARENT 547426 01-JUL-08 CURRENT

So, the table DBINC, as well as several other tables in the catalog, store the incarnation infor Suppose now that we want to "restore" the database to a prior incarnation, older backup. In this case, we need to "make clear" to rman, we are going to use such an to the prior incarnation. We can do that by using the "RESET DATABASE TO INCARNATION " command. the database to that incarnation, characterized by the correct incarnation to use those older backups.

using an sufficiently

old backup, belonging If we bring back key, we are able

How was the database a while ago? Lets check alberts person table. SQL> select * from albert.person; PERS_ID ---------1 2 3

PERS_NAME ----------------------------Before any backup After 1st backup After 2nd backup

Then we restored the 2nd backup. Albert's table now is PERS_ID ---------1 2

PERS_NAME ----------------------------Before any backup After 1st backup

What we want to do now, is restore the first backup, so Albert's table should become PERS_ID PERS_NAME ---------- ----------------------------1 Before any backup Lets take a look at the first controlfile backup: RMAN> list backup of controlfile; List of Backup Sets ===================

BS Key Type LV Size Device Type Elapsed Time Completion Time ------- ---- -- ---------- ----------- ------------ --------------32 Full 6.80M DISK 00:00:03 01-JUL-08 BP Key: 34 Status: AVAILABLE Compressed: NO Tag: TAG20080701T074551 Piece Name: C:\ORACLE\FLASH_RECOVERY_AREA\PRODROSS\BACKUPSET\2008_07_01\O1_MF_NCSNF_TAG2 Control File Included: Ckp SCN: 546782 Ckp time: 01-JUL-08 So now we are going to reset the incarnation to the former one, and we will use the backup that was associated to that incarnation. Watch this:

RMAN> RESET DATABASE TO INCARNATION 2; database reset to incarnation 2 RMAN> RESTORE DATABASE UNTIL SCN 546782; Starting restore at 02-JUL-08 allocated channel: ORA_DISK_1

channel ORA_DISK_1: sid=155 devtype=DISK

channel ORA_DISK_1: starting datafile backupset restore channel ORA_DISK_1: specifying datafile(s) to restore from backup set restoring datafile 00001 to C:\ORACLE\ORADATA\PRODROSS\SYSTEM01.DBF restoring datafile 00002 to C:\ORACLE\ORADATA\PRODROSS\UNDOTBS01.DBF restoring datafile 00003 to C:\ORACLE\ORADATA\PRODROSS\SYSAUX01.DBF restoring datafile 00004 to C:\ORACLE\ORADATA\PRODROSS\USERS01.DBF restoring datafile 00005 to C:\ORACLE\ORADATA\PRODROSS\TEST.DBF channel ORA_DISK_1: reading from backup piece C:\ORACLE\FLASH_RECOVERY_AREA\PRODROSS\BAC 0701T074551_46MNO0VC_.BKP channel ORA_DISK_1: restored backup piece 1 piece handle=C:\ORACLE\FLASH_RECOVERY_AREA\PRODROSS\BACKUPSET\2008_07_01\O1_MF_NNNDF_TAG 20080701T074551 channel ORA_DISK_1: restore complete, elapsed time: 00:01:16 Finished restore at 02-JUL-08 RMAN> RECOVER DATABASE UNTIL SCN 546782; Starting recover at 02-JUL-08 using channel ORA_DISK_1 starting media recovery

channel ORA_DISK_1: starting archive log restore to default destination channel ORA_DISK_1: restoring archive log archive log thread=1 sequence=12 channel ORA_DISK_1: reading from backup piece C:\ORACLE\FLASH_RECOVERY_AREA\PRODROSS\BAC 0701T074725_46MNQZXS_.BKP channel ORA_DISK_1: restored backup piece 1 piece handle=C:\ORACLE\FLASH_RECOVERY_AREA\PRODROSS\BACKUPSET\2008_07_01\O1_MF_ANNNN_TAG 20080701T074725 channel ORA_DISK_1: restore complete, elapsed time: 00:00:02 archive log filename=C:\ORACLE\ORADATA\LOG\ARC00012_0658911298.001 thread=1 sequence=12 media recovery complete, elapsed time: 00:00:02 Finished recover at 02-JUL-08 RMAN> ALTER DATABASE OPEN RESETLOGS; database opened new incarnation of database registered in recovery catalog starting full resync of recovery catalog full resync complete RMAN> Now we enter a session in the prodross database, and have a look at Albert's table: SQL> select * from albert.person; PERS_ID PERS_NAME ---------- ------------------------------1 Before any backup We have done it!

Article 5.

Oracle paper: Disaster Recovery

http://download-east.oracle.com/docs/cd/B19306_01/backup.102/b14191/rcmrecov004.htm#sthref734 Oracle® Database Backup and Recovery Advanced User's Guide

10g Release 2 (10.2) Part Number B14191-02

Performing Disaster RecoveryDisaster recovery includes the restore of and recovery of the target

To perform a disaster recovery, the minimum required set of backups is backups of some datafiles some archived redo logs generated after the time of the backup, and at least one autobackup of t The basic procedure for disaster recovery begins with restoring an autobackup of the server para as described in Oracle Database Backup and Recovery Basics. Once you have an SPFILE, you can start the target database instance, restore the control file fr With the control file mounted, then follow the instructions found in "Performing Recovery with a to restore and recover your datafiles. Note:

If you are restoring to a new host, you should review the considerations described in "Restore a The following scenario restores and recovers the database to the most recently available archive It assumes that: You are restoring the database to a new host with the same directory structure.

You have one tape drive containing backups of all the datafiles and archived redo logs through l of the control file and server parameter file. You do not use a recovery catalog. In this scenario, perform the following steps:

If possible, restore all relevant network files such as tnsnames.ora and listener.ora by means o

Start RMAN and connect to the target database. If you do not have the Oracle Net files, then con Specify the DBID for the target database with the SET DBID command, as described in "Performing

Run the STARTUP NOMOUNT command. RMAN attempts to start the instance with a dummy server paramet

Allocate a channel to the media manager and then run the RESTORE SPFILE FROM AUTOBACKUP command.

Run STARTUP FORCE NOMOUNT mode so that the instance is restarted with the restored server parame

Allocate a channel to the media manager and then restore a control file autobackup (refer to"Per Mount the restored control file.

Catalog any backups not recorded in the repository with the CATALOG command (refer to"Removing D

Restore the datafiles to their original locations. If volume names have changed, then run SET NE before the restore and perform a switch after the restore to update the control file with the ne Recover the datafiles. RMAN stops recovery when it reaches the log sequence number specified.

Open the database in RESETLOGS mode. Only complete this last step if you are certain that no oth # Start RMAN and connect to the target database % rman TARGET SYS/oracle@trgt # Set the DBID for the target database RMAN> SET DBID 676549873; RMAN> STARTUP FORCE NOMOUNT; # rman starts instance with dummy parameter file RUN { ALLOCATE CHANNEL t1 DEVICE TYPE sbt; RESTORE SPFILE FROM AUTOBACKUP; } # Restart instance with restored server parameter file RMAN> STARTUP FORCE NOMOUNT;

RMAN> RUN { # Manually allocate a channel to the media manager ALLOCATE CHANNEL t1 DEVICE TYPE sbt; # Restore autobackup of the control file. This example assumes that you have # accepted the default format for the autobackup name. RESTORE CONTROLFILE FROM AUTOBACKUP; # The set until command is used in case the database # structure has changed in the most recent backups, and you wish to # recover to that point-in-time. In this way RMAN restores the database # to the same structure that the database had at the specified time. ALTER DATABASE MOUNT; SET UNTIL SEQUENCE 1124 THREAD 1; RESTORE DATABASE; RECOVER DATABASE; } RMAN> ALTER DATABASE OPEN RESETLOGS; # Reset the online logs after recovery completes

The following example of the RUN command shows the same scenario except with new filenames for t RMAN> RUN { # If you need to restore the files to new locations, tell Recovery Manager # to do this using SET NEWNAME commands: SET NEWNAME FOR DATAFILE 1 TO '/dev/vgd_1_0/rlvt5_500M_1'; SET NEWNAME FOR DATAFILE 2 TO '/dev/vgd_1_0/rlvt5_500M_2'; SET NEWNAME FOR DATAFILE 3 TO '/dev/vgd_1_0/rlvt5_500M_3'; ALLOCATE CHANNEL t1 DEVICE TYPE sbt; RESTORE CONTROLFILE FROM AUTOBACKUP; ALTER DATABASE MOUNT; SET UNTIL SEQUENCE 124 THREAD 1; RESTORE DATABASE; SWITCH DATAFILE ALL; # Update control file with new location of datafiles. RECOVER DATABASE; } RMAN> ALTER DATABASE OPEN RESETLOGS;

Article 6: Oracle Paper http://download-east.oracle.com/docs/cd/B19306_01/backup.102/b14191/rcmrecov003.htm#i1006245 Oracle® Database Backup and Recovery Advanced User's Guide 10g Release 2 (10.2) Part Number B14191-02

Performing Recovery with a Backup Control FileIf all copies of the current control file are lost then you must restore and mount a backup control file before you can perform recovery. When using a backup control file, and using a recovery catalog, the process is identical to recovery with a current control file, as the RMAN repository information missing from the backup control file is available from the re There are special considerations when using a backup controlfile and not using a recovery catalo The following notes and restrictions apply regardless of whether you use a recovery catalog:

You must run the RECOVER command after restoring a backup control file, even if no datafiles hav

You must open the database with the RESETLOGS option after performing either complete or point-i with a backup control file.

If the online redo logs are inaccessible, then you must perform incomplete recovery to an SCN be in the online redo logs. This limitation is necessary because RMAN does not back up online logs.

During recovery, RMAN automatically searches for online and archived redo logs that are not reco

and catalogs any that it finds so that it can use them in recovery.

RMAN attempts to find a valid archived log in any of the current archiving destinations with the The current format is specified in the initialization parameter file used to start the instance (or all instances in a Real Application Clusters installation). Similarly, RMAN attempts to find by using the filenames as specified in the control file.

If you changed the archiving destination or format during recovery, or if you added new online l the control file, then RMAN may not be able to automatically catalog a needed online or archived RMAN reports errors similar to the following: RMAN-00571: RMAN-00569: RMAN-00571: RMAN-03002: RMAN-06054:

=========================================================== =============== ERROR MESSAGE STACK FOLLOWS =============== =========================================================== failure of recover command at 08/29/2001 14:23:09 media recovery requesting unknown log: thread 1 scn 86945

In this case, you must use the CATALOG command to manually add the required logs to the reposito The cataloging procedure is described in Oracle Database Backup and Recovery Basics.

Performing Recovery with a Backup Control File and No Recovery Catalog: Scenario This section assumes that you have RMAN backups of the control file, but do not use a recovery c

Assuming that you enabled the control file autobackup feature for the target database, you can r autobackup of the control file. Because the autobackup uses a default format, RMAN can restore i a repository available that lists the available backups. You can restore the autobackup to the d RMAN replicates the control file to all CONTROL_FILES locations automatically. Note:

If you know the backup piece name (for example, from the media manager or because the piece is o the piece name using the RESTORE CONTROLFILE FROM 'filename' command. The server records the loc in the alert log. Because you are not connected to a recovery catalog, the RMAN repository contains only informati at the time of the control file backup. If you know the location of other usable backup sets or control file RMAN repository with the CATALOG command.

Because the repository is not available when you restore the control file, you must use the SET the target database. The DBID is used to determine the location of control file autobackups. Use in the following special circumstances:

You are not connected to a recovery catalog and want to restore the control file or server param

You are connected to a recovery catalog and want to restore the control file, but the database n in the recovery catalog. The server parameter file is lost and you want to restore it. To recover the database with an autobackup of the control file without a recovery catalog: Start RMAN and connect to the target database. For example, run: CONNECT TARGET / Start the target instance without mounting the database. For example: STARTUP NOMOUNT;

Set the database identifier for the target database with SET DBID. RMAN displays the DBID whenev You can also obtain it by inspecting saved RMAN log files, querying the catalog, or looking at t control file autobackup. (refer to "Restoring Control File When Databases in the Catalog Have th SET DBID 676549873; Restore the autobackup control file, then perform recovery. Do the following:

Optionally, specify the most recent backup time stamp that RMAN can use when searching for a con If you know that a different control file autobackup format was in effect when the control file then specify a nondefault format for the restore of the control file.

If the channel that created the control file autobackup was device type sbt, then you must alloc Because no repository is available, you cannot use preconfigured channels.

Restore the autobackup of the control file, optionally setting the maximum number of days backwa and the initial sequence number that it should use in its search for the first day.

If you know that your control file contained information about configured channels that will be restore process, you can exit the RMAN client at this point, to clear manually allocated channel the RMAN client and mount the database those configured channels become available for your use i

If you do not care about using configured channels from your control file, then you can simply m

If the online logs are usable, then perform a complete restore and recovery as described in Orac

Otherwise, restore and perform incomplete recovery of the database, as described in Oracle Datab Use an UNTIL clause to specify a target time , SCN or log sequence number for the recovery prior

In this example, the online redo logs have been lost, and the most recent archived log sequence how to restore the control file autobackup, then performs recovery of the database to log sequen RUN { # Optionally, set upper limit for eligible time stamps of control file # backups # SET UNTIL TIME '09/10/2000 13:45:00'; # Specify a nondefault autobackup format only if required # SET CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK # TO '?/oradata/%F.bck'; ALLOCATE CHANNEL c1 DEVICE TYPE sbt PARMS='...'; # allocate manually RESTORE CONTROLFILE FROM AUTOBACKUP MAXSEQ 100 # start at sequence 100 and count down MAXDAYS 180; # start at UNTIL TIME and search back 6 months ALTER DATABASE MOUNT DATABASE; } # uses automatic channels configured in restored control file RESTORE DATABASE UNTIL SEQUENCE 13243; RECOVER DATABASE UNTIL SEQUENCE 13243; # recovers to latest archived log If recovery was successful, then open the database and reset the online logs: ALTER DATABASE OPEN RESETLOGS;

Article 7.

How to use the Online redologs for recovery.

Subject: How To Recover Using The Online Redo Log Doc ID: Note:186137.1 Type: BULLETIN Last Revision Date: 11-OCT-2007 Status: PUBLISHED How to recover using the online redo log ======================================== PURPOSE

------To easily and quickly find out if the online redo log files can be used to recover a database. AUDIENCE -------This document is addressed to DBAs that want to quickly find the best recovery solution in case of a database crash. HOW TO -----Many databases today are run without archive logging enabled, this reduces the available options to quickly recover a database. Basically 2 options are available: a) restore from a backup b) recover the database using the online redo logs. Option a) is straight forward and will not be covered here. Only important thing to mention is that option a) WILL cause loss of data if there has been updates/inserts to the database since the backup was taken. Let us instead take a look at option b): In case of a database crash or a database that will not startup due to ORA-1110, ORA-1113 or both, we first need to identify which files need to be recovered. 1) First we mount the database then issue the following query: select * from v$recover_file;

This will give us a list of the files that need media recovery. It will also give us CHANGE#, i.e. the SCN where the media recovery must start. To get the name of the file use the FILE# column and query against v$datafile like this: select name from v$datafile where file# =

or like this in 9i: select substr(d.name,1,30) fname , r.online_status , r.error , r.change# , r.time from v$datafile d, v$recover_file r where d.file# = r.file#

2) Next we do: archive log list

This will give us the current log sequence. We need the current log sequence -1. This will give us the last redo log file that was in use. 3) As the last step we do: select recid , stamp , sequence#

, first_change# , next_change# from v$log_history where sequence# =
This will show us the NEXT_CHANGE#, i.e.the highest SCN, in the redo log file. It will also give us the FIRST_CHANGE# SCN in this redo log file. We need these 2 SCN numbers to find out if we can use the redo log file for recovery against the file(s) found in 1). If the CHANGE# from 1) is between the FIRST_CHANGE# and the NEXT_CHANGE# then we can use the redo log file for recovery. If the CHANGE# from 1) is lower than FIRST_CHANGE# we need to investigate an earlier online redo log file. When we have identified which redo log files to use for the recovery we perform the recovery using the redo log file(s) in the following way: =- mount the Database =- recover Database

The recover process will now display something similar to the following: ORA-00279: change 12599 generated at 08/18/98 13:25:48 needed for thread 1 ORA-00289: suggestion : /oracle/OFA_base/app/oracle/admin/NE804DB1/arch/129.arc ORA-00280: change 12599 for thread 1 is in sequence #129 Specify log: {=suggested | filename | AUTO | CANCEL} As the database is not in ARCHIVELOG mode we will not have the 129.arc file. Instead the name of the redo log file must be entered on the command line. The filename must include the full path. After the redo log file has been applied the recover process will return: Log applied. At this stage 2 things can happend: 1) Media Recovery Completed 2) or additional redo log files must be applied

If 2) then just specify the next redo log file on the command line and continue to do so until Media Recovery Completed is displayed. This must be done with all the redo log files also the CURRENT redo log. To find the CURRENT redo log file one can issue the following query: select , from , where and

f.member,to_char(v.first_change#) v.sequence# v$log v v$logfile f v.group# = f.group# v.status='CURRENT';

When the CURRENT redo log has been applied the database can be opened with: alter database open; It is necessary to apply all relevant redo logs otherwise we will not be able to perform the complete recovery which is the only option we have when the database is in NOARCHIVELOG mode. If we do not find any online redo log files which covers the CHANGE# from 1) we cannot do a recover of the database or datafile(s). This means we are left with only 2 options of bringing the database back online: 1) restore from a valid backup taken before the crash. Doing so and running the database in NOARCHIVELOG MODE will cause a loss of data. This is unavoidable. 2) force the database open. This will override Oracle's internal datafile synchronisation and consistency check. The result is an

inconsistent database. The database MUST now be exported and rebuild as an inconsistent database is unreliable, and hence not supported. This last option should only be used in cooperation with Oracle Support

Article 8. Oracle paper:

RMAN best practices

Subject: Top 10 Backup and Recovery best practices. Doc ID: Note:388422.1 Type: FAQ Last Revision Date: 05-DEC-2007 Status: PUBLISHED In this Document Purpose Top 10 Backup and Recovery best practices. Questions and Answers

Applies to: Oracle Server - Enterprise Edition - Version: 9.2 Information in this document applies to any platform. Purpose Top 10 Backup and Recovery best practices. This document assumes that you are doing the Backup and Recovery basics =- Running in Archivelog mode =- multiplexing the controlfile =- Taking regular backups =- Periodically doing a complete restore to test your procedures. Questions and Answers 1. Turn on block checking. REASON: The aim is to detect, very early the presence of corrupt blocks in the database. This has a slight performance overhead, but will allow Oracle to detect early corruption caused by underlying disk, storage system, or I/O system problems. SQL> alter system set db_block_checking = true scope=both;

2. Turn on block tracking when using RMAN backups (if running 10g) REASON: This will allow RMAN to backup only those blocks that have changed since the last full backup, which will reduce the time taken to back up, as less blocks will be backed up. SQL> alter database enable block change tracking using file „/u01/oradata/ora1/change_tracking.f‟;

3. Duplex log groups and members and have more than one archive log dest. REASON: If an archivelog is corrupted or lost, by having multiple copies in multiple locations, the other logs will still be available and could be used. If an online log is deleted or becomes corrupt, you will have another member that can be used to recover if required. SQL> alter system set log_archive_dest_2='location=/new/location/archive2' scope=both; SQL> alter database add logfile member '/new/location/redo21.log' to group 1;

4. When backing up the database use the 'check logical' parameter REASON: This will cause RMAN to check for logical corruption within a block as well as the normal head/tail checksumming. This is the best way to ensure that you will get a good backup.

RMAN> backup check logical database plus archivelog delete input;

5. Test your REASON: This determine if critical and

backup. will do everything except actually restore the database. This is the best method to your backup is good and usable before being in a situation where it is issues exist.

RMAN> restore validate database;

6. Have each datafile in a single backup piece REASON: When doing a partial restore RMAN must read through the entire piece to get the datafile/archivelog requested. The smaller the backup piece the quicker the restore can complete. This is especially relevent with tape backups of large databases or where the restore is only on individual / few files. RMAN> backup database filesperset 1 plus archivelog delete input;

7. Maintain your RMAN catalog/controlfile REASON: Choose your retention policy carefully. Make sure that it compliments your tape subsystem retention policy, requirements for backup recovery strategy. If not using a catalog, ensure that your controlfile record keep time instance parameter matches your retention policy. SQL> alter system set control_file_record_keep_time=21 scope=both; This will keep 21 days of backup records. Run regular catalog maintenance. REASON: Delete obsolete will remove backups that are outside your retention policy. If obsolete backups are not deleted, the catalog will continue to grow until performance becomes an issue. RMAN> delete obsolete; REASON: crosschecking will check that the catalog/controlfile matches the physical backups. If a backup is missing, it will set the piece to 'EXPIRED' so when a restore is started, that it will not be eligible, and an earlier backup will be used. To remove the expired backups from the catalog/controlfile use the delete expired command. RMAN> crosscheck backup; RMAN> delete expired backup;

8. Prepare for loss of controlfiles. set autobackup on REASON: This will ensure that you always have an up to date controlfile available that has been taken at the end of the current backup not during. RMAN> configure controlfile autobackup on; keep your backup logs REASON: The backup log contains parameters for your tape access, locations on controlfile backups that can be utilised if complete loss occurs. 9. Test your recovery REASON: During a recovery situation this will let you know how the recovery will go without actually doing it, and can avoid having to restore source datafiles again. SQL> recover database test;

10. Do not specify 'delete all input' when backing up archivelogs REASON: Delete all input' will backup from one destination then delete both copies of the archivelog where as 'delete input' will backup from one location and then delete what has

been backed up. The next backup will back up those from location 2 as well as new logs from location 1, then delete all that are backed up. This means that you will have the archivelogs since the last backup available on disk in location 2 (as well as backed up once) and two copies backup up prior to the previous backup. See Note 443814.1 Managing multiple archive log destinations with RMAN for details.

Article 9. Oracle article: Recovery after disk loss Subject: Recover database after disk loss Doc ID: Note:230829.1 Type: TROUBLESHOOTING Last Revision Date: 20-MAY-2007 Status: PUBLISHED

*** This article is being delivered in Draft form and may contain errors. Please use the MetaLink "Feedback" button to advise Oracle of any issues related to this article. *** PURPOSE ------This article aims at walking you through some of the common recovery techniques after a disk failure SCOPE & APPLICATION ------------------All Oracle support Analysts, DBAs and Consultants who have a role to play in recovering an Oracle database Loss due to Disk Failure -----------------------What can we lose due to disk failure: A) Control files B) Redo log files C) Archivelog files D) Datafiles E) Parameter file or SPFILE F) Oracle software installation Detecting disk failure ----------------------1) Run copy utilities like "dd" on unix 2) If using RAID mechanisms like RAID 5, parity information may mask the disk failure and more vigorous check would be needed 3) As always, check the Operating system log files 4) Another obvious case would be when the disk could not be seen or mounted by the OS. 5) On the Oracle side, run dbverify if the file affected is a datafile 6) The best way to detect disk failure is by running Hardware diagnostic tools and OS specific disk utilities. Next Action -----------Once the type of failure is identified, the next step is to rectify them. Options could be: (1) Replace the corrupted disk with a new one and mount them with the same name (say /oracle or D:\)

(2) Replace the corrupted disk with a new one and mount them with a different name (say /oracle1 as the new mount point) (3) Decide to use another existing disk mounted with a different name (say /oracle2) The most common methods are (1) AND (3). Oracle Recovery --------------Once the disk problem is sorted, the next step is to perform recovery at the Oracle level. This would depend on the type of files that is lost (see Loss due to Disk Failure section) and also on the type of disk recovery done as mentioned in the "Next Action" section above. (A) Control Files -----------------Normally, we have multiplexing of controlfiles and they are expected to be placed in different disks. If one or more controlfile is/are lost,mount will fail as shown below: SQL> startup Oracle Instance started .... ORA-00205: error in identifying controlfile, check alert log for more info You can verify the controlfile copies using: SQL> select * from v$controlfile; **If atleast one copy of the controlfile is not affected by the disk failure, When the database is shutdown cleanly: (a) Copy a good copy of the controlfile to the missing location (b) Start the database Alternatively, remove the lost control file location specified in the init parameter control_files and start the database. **If all copies of the controlfile are lost due to the disk failure, then: Check for a backup controlfile. Backup controlfile is normally taken using either of the following commands: (a) SQL> alter database backup controlfile to '/backup/control.ctl'; -- This would have created a binary backup of the current controlfile --->If the backup was done in binary format as mentioned above, restore the file to the lost controlfile locations using OS copying utilities. --> SQL> startup mount; --> SQL> recover database using backup controlfile; --> SQL> alter database open; (b) SQL> alter database backup controlfile to trace; -- This would have created a readable trace file containing create controlfile script ---> Edit the trace file created (check user_dump_dest for the location) and retain the SQL commands alone. Save this to a file say cr_ctrl.sql --> Run the script SQL> @cr_ctrl This would create the controlfile, recover database and open the database. ** If no copy of the controlfile or backup is available, then create a controlfile creation script using the datafile and redo log file information. Ensure that the

file names are listed in the correct order as in FILE$. Then the steps would be similar to the one followed with cr_ctrl.sql script.

Note that all controlfile related SQL maintenance operations are done in the database nomount state

(B) Redo logs --------In normal cases, we would not have backups of online redo log files. But the inactive logfile changes could already have been checkpointed on the datafiles and even archive log files may be available. SQL> startup mount Oracle Instance Started Database mounted ORA-00313: open failed for members of log group 1 of thread 1 ORA-00312: online log 1 thread 1: '/ORACLE/ORADATA/H817/REDO01.LOG' ORA-27041: unable to open file OSD-04002: unable to open file O/S-Error: (OS 2) The system cannot find the file specified. ** Verify if the lost redolog file is Current or not. SQL> select * from v$log; SQL> select * from v$logfile; --> If the lost redo log is an Inactive logfile, you can clear the logfile: SQL> alter database clear logfile '/ORACLE/ORADATA/H817/REDO01.LOG'; Alternatively, you can drop the logfile if you have atleast two other logfiles: SQL> alter database drop logfile group 1;

--> If the logfile is the Current logfile, then do the following: SQL> recover database until cancel; Type Cancel when prompted SQL>alter database open resetlogs;

The 'recover database until cancel' command can fail with the following errors: ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below ORA-01194: file 1 needs more recovery to be consistent ORA-01110: data file 1: '/ORACLE/ORADATA/H817/SYSTEM01.DBF' In this case , restore an old backup of the database files and apply the archive logs to perform incomplete recovery. --> restore old backup SQL> startup mount SQL> recover database until cancel using backup controlfile; SQL> alter database open resetlogs;

If the database is in noarchivelog mode and if ORA-1547, ORA-1194 and ORA-1110 errors occur, then you would

Note that all redo log maintenance operations are done in the database mount state

(C) Archive logs ----------------If the previous archive log files alone have been lost, then there is not much to panic. ** Backup the current database files using hot or cold backup which would ensure that you would not need the missing archive logs (D) Datafiles -------------This obviously is the biggest loss. (1) If only a few sectors are damaged, then you would get ora-1578 when accessing those blocks. --> Identify the object name and type whose block is corrupted by querying dba_extents --> Based on the object type, perform appropriate recovery --> Check metalink Note 28814.1 for resolving this error (2) If the entire disk is lost, then one or more datafiles may need to be recovered . SQL> startup ORACLE instance started. ... Database mounted. ORA-01157: cannot identify/lock data file 3 - see DBWR trace file ORA-01110: data file 3: '/ORACLE/ORADATA/H817/USERS01.DBF' Other possible errors are ORA-00376 and ORA-1113 The views and queries to identify the datafiles would be: SQL> select file#,name,status from v$datafile; SQL> select file#,online,error from v$recover_file;

** If restoring to a replaced disk mounted with the same name, then : (1) Restore the affected datafile(s) using OS copy/restore commands from the previous backup (2) Perform recovery based on the type of datafile affected namely SYSTEM, ROLLBACK or UNDO, TEMP , DATA or INDEX. (3) The recover commands could be 'recover database', 'recover tablespace' or 'recover datafile' based on the loss and the database state ** If (1) (2) (3)

restoring to a different mount point, then : Restore the files to the new location from a previous backup SQL> STARTUP MOUNT SQL> alter database rename file '/old path_name' to 'new path_name'; -- Do this renaming for all datafiles affected. -(4) Perform recovery based on the type of datafile affected namely SYSTEM, ROLLBACK or UNDO, TEMP , DATA or INDEX. (5) The recover commands could be 'recover database', 'recover tablespace' or 'recover datafile' based on the loss and the database state

The detailed steps of recovery based on the datafile lost and the Oracle error are outlined in the following articles :

Note Note Note Note

184327.1 198640.1 183327.1 183367.1

NOARCHIVELOG DATABASE ===================== The loss mentioned in (A),(B) and (D) would be different in this case wherever archive logs are involved. We will discuss the datafile loss scenarios here: (a) If the datafile lost is a SYSTEM datafile, restore the complete database from the previous backup and start the database. (b) If the datafile lost is Rollback related datafile with active transactions, restore from the previous backup and start the database. (c) If the datafile contains rollback with no active rollback segments, you can offline the datafile (after commenting the rollback_segments parameter assuming that they are private rollback segments) and open the database. (d) If the datafile is temporary, offline the datafile and open the database. Drop the tablespace and recreate the tablespace. (e) If the datafile is DATA or INDEX, **Offline the tablespace and start the database. **If you have a previous backup, restore it to a separate location. **Then export the objects in the affected tablespace ( using User or table level export). **Create the tablespace in the original database. **Import the objects exported above. If the database is 8i or above, you can also use Transportable tablespace feature.

(E) Parameter file --------------This is not a major loss and can be easily restored. Options are: (1) If there is a backup, restore the file (2) If there is no backup, copy sample file or create a new file and add the required parameters. Ensure that the parameters db_name, control_files, db_block_size, compatible are set correctly (3) If the spfile is lost, you can create it from the init parameter file

(F) Oracle Software Installation ---------------------------There are two ways to recover from this scenario: (1) If there is a backup of the Oracle home and Oracle Inventory, restore them to the respective directories. Note if you change the Oracle Home, the inventory would not be aware of thid new path and you would not be able to apply patchsets. Also restore to the same OS user and group. (2) Perform a fresh Install

PRACTICAL SCENARIO ================== In most cases, when a disk is lost, more than one type of file could be lost. The recovery in this scenario would be:

(1) A combination of each of these data loss recovery scenarios (2) Perform entire database restore from the last backup and apply archive logs to perform recovery. This is a highly preferred method but could be time consuming.

Note: For any issues or clarifications, call into Oracle Support

RELATED DOCUMENTS -----------------Note 103176.1 - How to Recover Having Lost Controlfiles and Online Redo Logs Note 184327.1 - Common Causes and Solutions on ORA-1157 Error Found in B&R Note 198640.1 - How to Recover from a Lost Datafile with Different Scenarios Note 183327.1 - Common Causes and Solutions on ORA-376 Error Found in B&R Note 183367.1 - Common Causes and Solutions on ORA-1113 Error Found in B&R Note 117481.1 - Loss Of Online Redo Log And ORA-312 And ORA-313

th missing archivelogs

g behind from the rest of the datafiles.

quired to recover the said datafile are missing.

ecover it by applying up to the last available archivelog to roll it forward,

CORRUPTION=TRUE in the init.ora. But there is no 100% guarantee d, then you must immediately rebuild the database. full-database export, (2) create a new and separate database,

er (DUL), which is performed by Oracle Field Support on-site for an extra charge.

ke sure you have a good and recent export of the database as well. e a pipe, and using gzip or compress, you . For example, a 500GB database can be exported to a 25GB exportfile.

in time: up to a certain SCN

the table PERSON.

ords in the PERSON table: ot mounted or opened.

9794, rman tells us that there is no

: TAG20080627T103023 ACKUPSET\2008_06_27\O1_MF_NCSNF_TAG20080627T103023_469DWKVT_.BKP

: TAG20080627T103209 ACKUPSET\2008_06_27\O1_MF_NCNNF_TAG20080627T103209_469DWV0Z_.BKP

g: TAG20080627T104611 ACKUPSET\2008_06_27\O1_MF_NCSNF_TAG20080627T104611_469FT57B_.BKP

g: TAG20080627T104755 ACKUPSET\2008_06_27\O1_MF_NCNNF_TAG20080627T104755_469FTDJM_.BKP

g: TAG20080627T111308 ACKUPSET\2008_06_27\O1_MF_NCSNF_TAG20080627T111308_469HDDYR_.BKP

g: TAG20080627T111442 ACKUPSET\2008_06_27\O1_MF_NCNNF_TAG20080627T111442_469HDN5C_.BKP

ERY_AREA\TEST10G\BACKUPSET\2008_06_27\O1_MF_NCSNF_TAG20080627T103

T\2008_06_27\O1_MF_NCSNF_TAG20080627T103023_469DWKVT_.BKP tag=TAG

2008_06_27\O1_MF_ANNNN_TAG20080627T103227_469DXG4D_.BKP 2008_06_27\O1_MF_ANNNN_TAG20080627T104805_469FTQ6T_.BKP 2008_06_27\O1_MF_ANNNN_TAG20080627T111453_469HDYYR_.BKP 2008_06_27\O1_MF_NCNNF_TAG20080627T103209_469DWV0Z_.BKP 2008_06_27\O1_MF_NCNNF_TAG20080627T104755_469FTDJM_.BKP 2008_06_27\O1_MF_NCNNF_TAG20080627T111442_469HDN5C_.BKP 2008_06_27\O1_MF_NCSNF_TAG20080627T103023_469DWKVT_.BKP 2008_06_27\O1_MF_NCSNF_TAG20080627T104611_469FT57B_.BKP 2008_06_27\O1_MF_NCSNF_TAG20080627T111308_469HDDYR_.BKP 2008_06_27\O1_MF_NNNDF_TAG20080627T104611_469FQ48S_.BKP 2008_06_27\O1_MF_NNNDF_TAG20080627T111308_469H9OK2_.BKP 2008_06_27\O1_MF_NNSNF_TAG20080627T103209_469DWZ5C_.BKP 2008_06_27\O1_MF_NNSNF_TAG20080627T104755_469FTGQX_.BKP 2008_06_27\O1_MF_NNSNF_TAG20080627T111442_469HDPF5_.BKP

ERY_AREA\TEST10G\BACKUPSET\2008_06_27\O1_MF_NNNDF_TAG20080627T103

T\2008_06_27\O1_MF_NNNDF_TAG20080627T103023_469DSJF5_.BKP tag=TAG

ERY_AREA\TEST10G\BACKUPSET\2008_06_27\O1_MF_ANNNN_TAG20080627T103

T\2008_06_27\O1_MF_ANNNN_TAG20080627T103227_469DXG4D_.BKP tag=TAG

420.001 thread=1 sequence=11

in time: up to a certain SCN

re is a difference.

Now in this example we are going to test

: TAG20080628T012918 ACKUPSET\2008_06_28\O1_MF_NNNDF_TAG20080628T012918_46C1GZMK_.BKP

0G\UNDOTBS01.DBF

: TAG20080628T014827 ACKUPSET\2008_06_28\O1_MF_NNNDF_TAG20080628T014827_46C2LW5V_.BKP

0G\UNDOTBS01.DBF

g: TAG20080630T044903 ACKUPSET\2008_06_30\O1_MF_NNNDF_TAG20080630T044903_46JOXJQX_.BKP

0G\UNDOTBS01.DBF

hile our latest backup is from 30 june. nt to investigate whether

s take a look at the backups

: TAG20080628T012918 ACKUPSET\2008_06_28\O1_MF_NCSNF_TAG20080628T012918_46C1KQ3F_.BKP

: TAG20080628T013052 ACKUPSET\2008_06_28\O1_MF_NCNNF_TAG20080628T013052_46C1KY00_.BKP

ERY_AREA\TEST10G\BACKUPSET\2

T\2008_06_28\O1_MF_ANNNN_TAG


n restore a backup from several days old.

ime, or a certain SCN, with the "reset logs" clause.

en created, and all backup ormer Incarnation, and are

rom the new incarnation. to do so? Yes they can.

s" clause, and a new incarnation

this is the new incarnation you could have issued the following

INC_STATUS from DBINC;

catalog, store the incarnation information.

or incarnation, using an sufficiently to use such an old backup, belonging

" command. If we bring back ect incarnation key, we are able

ert's table should become

: TAG20080701T074551 BACKUPSET\2008_07_01\O1_MF_NCSNF_TAG20080701T074551_46MNQG7T_.BKP

one, and we will use the backup that

from backup set

E\FLASH_RECOVERY_AREA\PRODROSS\BACKUPSET\2008_07_01\O1_MF_NNNDF_TAG200

CKUPSET\2008_07_01\O1_MF_NNNDF_TAG20080701T074551_46MNO0VC_.BKP tag=TA

fault destination

E\FLASH_RECOVERY_AREA\PRODROSS\BACKUPSET\2008_07_01\O1_MF_ANNNN_TAG200

CKUPSET\2008_07_01\O1_MF_ANNNN_TAG20080701T074725_46MNQZXS_.BKP tag=TA


ave a look at Albert's table:

2/b14191/rcmrecov004.htm#sthref734

restore of and recovery of the target database

backups is backups of some datafiles, kup, and at least one autobackup of the control file. ring an autobackup of the server parameter file,

instance, restore the control file from autobackup and mount it. found in "Performing Recovery with a Backup Control File" (see Article 2)

onsiderations described in "Restore and Recovery of the Database on a New Host". o the most recently available archived log, which in this example is log 1124 in thread 1.

directory structure.

iles and archived redo logs through log 1124, as well as autobackups

names.ora and listener.ora by means of operating system utilities.

t have the Oracle Net files, then connect using operating system authentication.

command, as described in "Performing Recovery with a Backup Control File and No Recovery Catalog: Scenario". instance with a dummy server parameter file.

STORE SPFILE FROM AUTOBACKUP command.

arted with the restored server parameter file.

control file autobackup (refer to"Performing Recovery with a Backup Control File and No Recovery Catalog: Scenar

CATALOG command (refer to"Removing DELETED Records From the Recovery Catalog After Upgrade").

e names have changed, then run SET NEWNAME commands o update the control file with the new locations for the datafiles (refer to"Performing Disaster Recovery").

the log sequence number specified.

t step if you are certain that no other archived logs can be applied.

ummy parameter file

sumes that you have

s after recovery completes

nario except with new filenames for the restored datafiles:

l Recovery Manager

ion of datafiles.

2/b14191/rcmrecov003.htm#i1006245

of the current control file are lost or damaged,

l file, and using a recovery catalog,

control file is available from the recovery catalog. lfile and not using a recovery catalog. ether you use a recovery catalog:

ontrol file, even if no datafiles have been restored.

performing either complete or point-in-time recovery

form incomplete recovery to an SCN before the earliest SCN se RMAN does not back up online logs. archived redo logs that are not recorded in the RMAN repository,

rrent archiving destinations with the current log format. eter file used to start the instance on). Similarly, RMAN attempts to find the online redo logs

ecovery, or if you added new online log members after the backup of y catalog a needed online or archived log. In this situation,

add the required logs to the repository so that recovery can proceed. ckup and Recovery Basics.

ery Catalog: Scenario rol file, but do not use a recovery catalog.

re for the target database, you can restore an a default format, RMAN can restore it even though it does not have u can restore the autobackup to the default or a new location. ions automatically.

dia manager or because the piece is on disk), then you can specify ' command. The server records the location of every autobackup

AN repository contains only information about available backups ation of other usable backup sets or image copies, add them to the

e control file, you must use the SET DBID command to identify tion of control file autobackups. Use SET DBID command only

tore the control file or server parameter file. the control file, but the database name is not unique

ile without a recovery catalog:

T DBID. RMAN displays the DBID whenever you connect to the target. querying the catalog, or looking at the filenames of When Databases in the Catalog Have the Same Name: Example"). For example, run:

Do the following:

RMAN can use when searching for a control file autobackup to restore. was in effect when the control file autobackup was created, device type sbt, then you must allocate one or more sbt channels.

ing the maximum number of days backward that RMAN can search (up to 366) earch for the first day.

out configured channels that will be useful to you in the rest of the , to clear manually allocated channels from step "c". If you then restart nnels become available for your use in the rest of the restore and recovery process.

r control file, then you can simply mount the database at this point.

ore and recovery as described in Oracle Database Backup and Recovery Basics.

atabase, as described in Oracle Database Backup and Recovery Basics equence number for the recovery prior to the first SCN of the online redo logs.

he most recent archived log sequence number is 13243. This example shows ecovery of the database to log sequence 13243.

t the online logs:

E804DB1/arch/129.arc

be opened with:

ase back online:

locks in the database.

changed since the last full s will be backed up.

oradata/ora1/change_tracking.f‟;

copies in multiple locations,

her member that can be

rchive2' scope=both;

n a block as well as the normal ll get a good backup.

se. This is the best method to

tire piece to get the icker the restore can atabases or where the

compliments your tape subsystem t using a catalog, atches your retention policy.

grow until performance

hes the physical backups. a restore is started, To remove the expired

lfile available that has been

ocations on controlfile backups

recovery will go without

lete both copies of the nd then delete what has

as well as new logs at you will have the (as well as backed up

create a controlfile on. Ensure that the

and ORA-1110 errors occur, then you would have restore from an old backup and start the database.

querying dba_extents

24 in thread 1.

d No Recovery Catalog: Scenario".

File and No Recovery Catalog: Scenario").

og After Upgrade").

o"Performing Disaster Recovery").

Backup Recovery Oracle

Recommend Documents