list - tells you what has already been done<br /> report - tells you what needs to be done<br />

• RMAN duplicate database disected - Pythian blog
• Oracle RMAN Best Practices and Myths
• RMAN restore scenarios - thegeekstuff
• RMAN restore scenarios - Gavin Soorma
• Different ways of restoring a controlfile
• Oracle Data Guard 11g Data Protection and Availability for Oracle Database - oracle.com
• Oracle RMAN DUPLICATE database
• Duplicate a Database Using RMAN in Oracle Database 11g Release 2 - oracle-base.com - code snippets
• Duplicate Oracle Database with RMAN - www.akadia.com - code examples
• Preventing, Detecting, and Repairing Block Corruption: Oracle Database 11g - oracle.com
• uhesse.files.wordpress.com/2009/01/newsletter_data_guard.pdf
• Make a self-contained autonomous backup using just the necessary archivelogs - damir-vadas
• To crosscheck or not to crosscheck - caleb.com

Set this at unix prompt before starting RMAN

0@@

1@@

2@@

3@@

4@@

5@@

6@@

7@@

8@@

9@@

10@@

11@@

The library used here is for AIX

12@@

…and this works on RHEL

13@@

14@@

15@@

In a space emergency, backup all the archivelogs to the configured tape and immediately delete them from disk

16@@

or ensure the correct device…

17@@

18@@

If the Flash (or Fast) Recovery Area is used for archive redo logs as well as flashback logs, we need to monitor it's size

19@@

and in detail…

20@@

size taken up by the flashback logs

21@@

• dba-oracle.com

Validate that the database components are free of corruption

22@@

or validate that the database components on tape are free of corruption

23@@

eg (using Commvault):

24@@

or

25@@

or

26@@

Use “preview” instead of “validate” to see the list of backup pieces that would be used in the restore process. “preview” actually mimics the performance of the task. “validate” does a brief check.

• oracle.com

• oracle.com

27@@

If that approach does not work, try this

28@@

Environment must be set before running…

29@@

Had a problem where the name of the data file was created incorrectly. It had non-printable characters in it due to backspace character being setup wrongly and someone making a typing mistake.<br />

30@@

In version 12c file renaming is more like it should be!

31@@

32@@

Channel test

33@@

34@@

35@@

• Connect to destination host
• Set environment for destination (auxiliary) SID - easiest way is make sure SID is in oratab
• Check sufficient disk space for datafiles
• Make a minimal init.ora file - depending on version, may need some memory parameters and compatible parameter in addition to db_name
• Add file_convert parameters to init.ora in case datafile location is different
• Make directories for audit, datafiles and archivelogs (if new db)
• Check source db is accessible via TNS (not necessary but useful depending on restore method)
• Check catalog is accessible via TNS (not necessary but useful depending on restore method)
• Create a password file (if new db)
• Shutdown destination db (if already existing)
• Delete destination db files (if already existing)
• Startup auxiliary instance in nomount mode referencing the minimal pfile
• Run an RMAN file like this one to clone the database. This one is specific to EMC (Legato) Networker

36@@

• Background reading: gavinsoorma.com

There are several interesting reasons for using backup to copy instead of the default (backupset). Our scenario here is that:

• a 36Tb database has been shrunk to 3Tb and needs reorganising
• somehow the ckfs command shows that a filesystem needs repairing but the database is still working fine
• it is a production database and needs minimal downtime

A filesystem needs to be available with enough space to hold the (new) 3Tb database.<br /> The %b format means just the filenames are used.

37@@

This backup failed partially because someone in the past had added a few datafiles to a tablespace but appended a space to the end of the filename so we needed this additional step: The %U format means that a unique filename is generated for each file (but includes the file_id so it can be reassociated with its name).

38@@

At intervention time, stop the applications accessing the database, stop the listeners and wait for any ongoing transactions to end.

Run an archivelog backup sending any new logs to tape but don't delete them from disk as they will be needed for recovery.

39@@

40@@

Take a text copy as well just in case…

41@@

42@@

Copy one member of each group just in case…

43@@

Change any parameters that have a directory path to reflect the location of the new files. At the very least, change the control_files parameter.

Startup the instance using nomount and specify the modified pfile

44@@

If all has been modified correctly, monting the database should prove successful

45@@

46@@

47@@

48@@

49@@

50@@

51@@

52@@

On source host

53@@

54@@

55@@

On destination server

56@@

Make sure there is only one set of backup files in the directory. RMAN could get confused about which controlfile to use even though the documentation says it will take the most recent.

57@@

• autonomous-rman-online-backup.html

Maybe find out how many threads there are first and script it.<br /> Find the archivelog sequence number before the backup starts.

58@@

59@@

Run the backup switching logfile before and after.

60@@

Find the archivelog sequence number now that the backup has finished.

61@@

Backup just the archivelogs that will be necessary to recover.

62@@

• another version by dba-oracle

63@@

Copy over the backup files from source to destination server<br />

• using scp

64@@

• using rsync

65@@

• using zfs disk (Solaris)

On source server unmount the disk and comment the appropriate line in /etc/vfstab

66@@

On destination server uncomment the appropriate line in /etc/vfstab and mount the disk

67@@

Standby database needs to be able to connect to the primary via tns

68@@

69@@

Used for the initial start of the standby database in the above script

70@@

RMAN command file used in the above script

71@@

Harnesses the power of an RMAN image copy backup to generate clones that can be used for dev/qa etc.<br /> Only the blocks different from those in the RMAN datafiles are kept in the cloned datafiles making huge space savings.<br />

• A somewhat confusing article from Oracle explaining why snapshots are not the same as RMAN backups (like that was ever in doubt!)
• Clone your dNFS Production Database for Testing (Metalink note 1210656.1 on clonedb - with clonedb.pl script)
• Oracle white paper - Database Cloning using Oracle Sun ZFS Storage Appliance and Oracle Data Guard

• High Availability Demonstrations

Includes the following methods…<br />

• Data Guard
• Active Data Guard
• Recovery Manager (RMAN)
• Flashback Technology
• Data Guard and Applications
• Oracle Secure Backup (OSB)
• Global Data Services (GDS)
• Oracle GoldenGate
• Oracle Sharding
• Cloud MAA

72@@

73@@

74@@

or from SQL prompt…

75@@

Monitor progress of RMAN backups

76@@

Run this one from sys on the database server

77@@

Run from the RMAN catalog owner, this checks to see how long ago a full backup was taken from all databases in the catalog

78@@

Run from the RMAN catalog owner, this shows the backup history for a particular database in the catalog

79@@

Run from the RMAN catalog owner, this shows the archivelog backup history for a particular database in the catalog

80@@

Sometimes the catalog needs cleaning up. Maybe the backup scripts are not doing a crosscheck and delete properly.

81@@

If this produces results, records exist that go back before the retention period and may need deleting (depending on the policy).

82@@

If you get this error…

83@@

Allocate a fake tape channel (but not in a run block!)<br /> Apparently you can - didn't work for me

84@@

Not really necessary as RMAN can work it out but if you need to know… select media , to_char(completion_time,'DD-MON-YYYY HH24:MI:SS') backed_up from rc_backup_piece where 1=1 and db_id = 4053457814 and completion_Time > sysdate - 2 order by 2

Check / Set it

85@@

or check from the Operating System for the CTWR background process<br /> Here we see 3 of the 8 databases on this machine have block change tracking enabled

86@@

Enable it

87@@

Disable it

88@@

Rename / Move it

89@@

If the status of an RMAN backup is stuck in RUNNING, the next backup will not start up. Reset the view with this…

90@@

91@@

92@@

93@@

94@@

95@@

96@@

97@@

Problem with this method is that anyone doing a 'ps -ef' will see the catalog password! Do it this way and the password will not be visible…

98@@

99@@

100@@

101@@

102@@

• Delete Expired and Obsolete backups - thegeekstuff.com

Obsolete - No longer needed as they are older than the retention period. They will go back longer if there was no full backup within the retention period.<br /> Expired - The backup file exists in the control file / catalog but no longer exists in reality.

103@@

104@@

Scenario <br /> Customer wants a complete database restored to a different server so that they can retrieve data that is no longer available on the source database.<br /> It is not possible to restore from an export as the required dump files are no longer available.<br /> <br /> Source: PROD1 on prod004<br /> Destination: PRODX on test002<br /> <br /> Method

• Request Unix team to attach a temporary filesystem big enough to hold the complete database (and an extra bit for contingency) to the destination server
• Request Unix team to restore (or copy from source) the Oracle software to the mounted filesystem
• Recreate the /oracle/SID/admin directory (contains necessary tdpo files)
• Update dsm.sys with SID stanza from source server
• Test the tdpo access from destination server
• Add destination SID to oratab
• Create a minimal init.ora file
• Check that the desired data can be restored (validate)
• Create an RMAN command file to perform the work
• Create a shell to run the process

<br /> <br /> Request Unix team to attach a temporary filesystem big enough to hold the complete database (and an extra bit for contingency) to the destination server<br /> Either attach the storage directly to the destination server or nfs mount it (with hard mount options) from another server<br /> <br /> Request Unix team to restore (or copy from source) the Oracle software to the mounted filesystem<br /> The whole of the Oracle source code can be copied from the source server to the destination server. The contents of the dbs directory can be removed.<br /> <br /> Recreate the /oracle/SID/admin directory (contains necessary tdpo files)<br /> For this customer, we keep all the TSM related files in this directory. These include the tdpo.opt, dsm.opt and TSM.PWD files.<br />

105@@

<br /> Update dsm.sys with SID stanza from source server<br /> The stanza from the source server relating to this database should be copied into the dsm.sys on the destination server so that tdpo can access the TSM server.

106@@

<br /> Test the tdpo access from destination server<br /> Use tdpoconf to test access to the TSM server and to check the related files are all usable.

107@@

<br /> Add destination SID to oratab<br /> Add a line with the new SID

108@@

<br /> Create a minimal init.ora file<br /> This is just a dummy file so that we can start an idle instance.

109@@

<br /> Check that the desired data can be restored (validate)<br /> Use an RMAN preview command to ensure that files can be restored as far back as the required date.<br /> If you have no access to the catalog, you will need to use “set dbid=<DBID>;”

110@@

<br /> Create an RMAN command file to perform the work<br /> This syntax does not require a connection to the target database

111@@

<br /> Create a shell to run the process

112@@

<br />

When media failure strikes, look in the alertlog for more info if you can still get to it.

If some/all datafiles in one tablespace are damaged, you can recover the tablespace.

113@@

or while database is open…

114@@

or until a particular moment in time (TSPITR)

115@@

NOTE 1: If you do not specify auxiliary destination, it will try to use the same location as the original datafiles - and will conflict!<br /> NOTE 2: There is a bug which ignores the fact that a tape channel has been allocated.<br /> The workaround is to use CONFIGURE to allocate a channel. Do not forget to reset it later.

116@@

Output of the above script:

117@@

…or even better if flashbask is enabled (this takes seconds instead of hours) - but this is the whole database not just a tablespace!!

118@@

Used the purge option? Pisser - no flashback. However, from 12c, it is possible (causing RMAN to work very hard) to restore a table.<br /> It's possible in any version doing the steps manually (so long as RMAN is being used and database is in archivelog mode), but 12c automates it down to 1 line!

• Recover a table using RMAN in 12c

119@@

To get a list of datafiles and their numbers, use 'report schema' or 'select file#,name from v$datafile;' (this queries the control file)

120@@

or while database is open…

121@@

122@@

If all else fails, restore the database to a time before the bad thing happened, then recover it by rolling forward with the archivelogs.<br /> Brief list of actions involved. Not all steps will be needed. Depends on same/different server restore:

123@@

Can do it by time, scn, logseq.<br /> Can do it in one step or two.

124@@

Add this if connecting to a catalog connect catalog catusr/catpwd@catdb;

125@@

or by scn…

126@@

or

127@@

Assuming database is started in NOMOUNT mode…

Restore controlfile(s) to original location(s)

128@@

Restore the controlfile but only to a location other than those mentioned in the CONTROL_FILES parameter

129@@

Restore controlfile(s) from specified file to original locations unless to 'filename' is specified (then only to this location).

130@@

Note: If controlfile was backed up with autobackup format set to non-default location, format must be specified for restore with: SET CONTROLFILE AUTOBACKUP FORMAT '<format>';<br /> If catalog is not available, following is not possible. RMAN cannot know from where to restore it

131@@

Restore controlfile(s) to original locations

132@@

Restore the controlfile to specified location

133@@

Restore controlfile(s) from specified file to original locations unless to 'filename' is specified (then only to this location). Note: SET DBID must be used here.

134@@

If catalog is not available, following is not possible. Catalog needs to be available to be able to restore from a tag

135@@

136@@

Upgrade has failed part way<br /> Catalog is still on old ORACLE_HOME<br /> Database is already on new ORACLE_HOME

• Shut the database down

137@@

• Set old home in oratab

138@@

• Restore a controlfile from before the upgrade

139@@

• Read the controlfile

140@@

• Restore the database

141@@

• Recover the database

142@@

• Start the database

143@@

• from www.dbspecialists.com

One of the new Backup and Recovery features of Oracle 11g Release 2 (11gR2) is the ability to duplicate a database without connecting to the target database.<br /> In RMAN terminology, the “target” database is the one you wish to duplicate.<br /> In this case, the only requirement is that you provide a disk backup location where RMAN can find all the backups, data file copies, archived logs, and control file copies for database duplication.<br /> This database duplication enhancement is helpful when it is not possible to connect to the target database and the recovery catalog.<br /> <br /> This came in handy for me recently on a customer project. They wanted me to test a disaster recovery scenario in which the production database server was completely gone and we needed to restore it from tape onto another server.<br /> This is a fairly typical DR situation, but in this case it was made more difficult because the directory structure on the test server didn’t match that of the production server.<br /> Specifically, on the production server, everything was located on the E: drive while on the Test server, the only drive that exists is C:.<br /> This means that all file locations need to be changed as part of the duplicate procedure.<br /> This can all be done in one single RMAN command.<br /> <br /> Note: One thing that makes this process easier is to enable controlfile autobackups on the production database.<br /> This is a good practice in general and should be configured for all databases.<br /> <br />

144@@

Here are the steps that I followed:<br /> <br /> Step1) Copy the most recent backup files and archivelog files to the test server.<br /> In this case, we restored from tape to the location c:\prod backup files. The backup files and archivelogs come from the following locations:<br /> <br /> E:\app\flash_recovery_area\prod\ARCHIVELOG\2013_03_21<br /> E:\app\flash_recovery_area\prod\BACKUPSET\2013_03_21<br /> E:\app\flash_recovery_area\prod\AUTOBACKUP\2013_03_21<br /> <br /> Step 2) Create a file called INITPROD2.ORA in %ORACLE_HOME%\database containing one line: db_name=PROD2<br /> <br /> Step 3) At a command prompt:<br />

145@@

Step 4) At the RMAN prompt:<br /> <br />

146@@

That's all it takes! As you can see, I changed all file location parameters and converted datafile and logfile names with the _CONVERT parameters.<br /> You can set any parameters for the new database in this way. For example you may want to disable all database jobs in the duplicate database by setting job_queue_processes=0.<br /> Or, change the size of the SGA for a smaller server. RMAN will create the PROD2 database, perform as much recovery as possible given the available archive logs and open the database with the resetlogs option.<br /> This is a really nice feature of RMAN.

If you backup the controlfile with:<br /> alter database backup controlfile to trace as '/oracle/${OSID}/admin/${NSID}_controlfile.sql';<br /> This example found in backup controlfile

147@@

This script can generate 'set newname' statements for use when duplicating a database for standby.<br /> If the files are scattered all over the place, this will gather them together and spread the datafiles evenly over the available space.

148@@

Reference: pythian.com<br /> Reproduced here in case it disappears from the internets.

A lot of metadata about the RMAN backup jobs can be found in the V$RMAN_% views.<br /> These views show past RMAN jobs as well as jobs currently running.<br /> Once the jobs complete backup sets, metadata about the sets and pieces are also added to the control file and can be accessed through the V$BACKUP_% views.<br /> <br /> For the queries in this post I need only four of those views:<br /> <br />

• V$BACKUP_SET
• V$BACKUP_SET_DETAILS
• V$RMAN_BACKUP_JOB_DETAILS
• GV$RMAN_OUTPUT

Query 1 (Backup jobs' status and metadata)

149@@

Query 2 (Backup set details)

150@@

Query 3 (Backup job output)

151@@

152@@