Warning: Creating default object from empty value in /home/customer/www/gavinsoorma.com/public_html/wp-content/themes/specular/admin/inc/class.redux_filesystem.php on line 29
Backup and Recovery | Oracle DBA – Tips and Techniques | Page 2
News
Perth, Australia
+ (61) 417713124
prosolutions@gavinsoorma.com

11g Release 2 RMAN Backup Compression

  • Posted by Gavin Soorma
  • On December 1, 2009
  • 1 Comments
Oracle 11g Release 2 introduced compression algorithm levels which can be used for compressing table data, Data Pump exports and RMAN backups as well. The compression levels are BASIC, LOW, MEDIUM and HIGH and each affords a trade off related to backup througput and the degree of compression afforded. To use this option, we can […]
Read More
 

11g RMAN Virtual Private Catalog

  • Posted by Gavin Soorma
  • On December 1, 2009
  • 0 Comments
In Oracle 11g, we can grant restricted access to the RMAN catalog to some users so that they can only access a limited set of databases that are registered in the RMAN catalog. This is done by creating a Virtual Private Catalog which in turn will grant a particular user read/write access to only that […]
Read More
 

RMAN Restore Validate Examples

  • Posted by Gavin Soorma
  • On November 20, 2009
  • 0 Comments
We can use the RMAN RESTORE VALIDATE command to check and verify the integrity of the backups which are stored on tapes or disk as well. A point to keep in mind is that the RESTORE DATABASE VALIDATE command will only check for the datafile backups and will not check for either the archivelog of […]
Read More
 

11g RMAN Substitution Variables

  • Posted by Gavin Soorma
  • On September 22, 2009
  • 0 Comments
We can now use SQL*PLUS type substitution variables in RMAN like &1, &2 and so on and the same can be linked to shell scripts as shown below with the new clause USING where we are passing the Incremental Level and the backup tag to the actual RMAN command. Also note that in 11g we […]
Read More
 

Duplicating a database from an ASM to a Non-ASM file system

  • Posted by Gavin Soorma
  • On August 26, 2009
  • 1 Comments
By default, when we are using ASM with a Flash Recovery Area configured, backups to disk will go to the “backupset” subdirectory – unless we use the FORMAT clause while taking the backup to point the backups to another location on disk. When we try to duplicate a database which is hosted on an ASM […]
Read More
 

Using FLASHBACK to rollback a TRUNCATE

  • Posted by Gavin Soorma
  • On August 18, 2009
  • 2 Comments
This scenario will show how we can use a combination of FLASHBACK database and also recovery to take a database back in time to undo a TRUNCATE operation and then roll forward the database after the flashback operation to bring it to the current point in time. INSERT ROWS INTO TABLE MYOBJ – THIS WILL […]
Read More
 

READ ONLY Tablespace Restore and Recovery

  • Posted by Gavin Soorma
  • On August 12, 2009
  • 0 Comments
Keeping static or historical data in read only tablespaces is a good practice especially for data warehouse type environments. Using the RMAN SKIP READONLY command, we can reduce the backup window and overhead as well by excluding these read only tablespaces from the database backupsets. But we need to keep in mind that we need […]
Read More
 

RMAN TSPITR Limitations

  • Posted by Gavin Soorma
  • On August 4, 2009
  • 0 Comments
While the RMAN Tablespace Point In Time Recovery (TSPITR) automates the process of creating an auxiliary instance and exporting and importing objects from the auxiliary instance to the target database, it still has many limitations and we encountered bugs which apparently have been only fixed in 11g. Not only do we need to allocate temporary […]
Read More
 

RMAN Recovering a Dropped Tablespace

  • Posted by Gavin Soorma
  • On August 4, 2009
  • 0 Comments
To recover from a case of a dropped tablespace, the Tablespace Point In Time Recovery (TSPITR) method cannot be used. When you drop a tablespace, the controlfile will then no longer have any records of the tablespace which has been dropped. Attempts to use the RMAN RECOVER TABLESPACE command will return the RMAN error RMAN-06019 […]
Read More
 

RMAN Recovery using the SWITCH DATABASE TO COPY command

  • Posted by Gavin Soorma
  • On July 31, 2009
  • 3 Comments
By using Optimized Incremental Backup to disk backup strategy, an up to date copy of the database is always available on disk. In the event of a failure, the SWITCH command will point the controlfile to the backup of the datafiles that are present on disk. In this way, we can significantly reduce the downtime […]
Read More
 1

RMAN-06563: control file or SPFILE must be restored using FROM AUTOBACKUP

  • Posted by Gavin Soorma
  • On July 28, 2009
  • 0 Comments
Backup and Recovery best practices dictate that we must use a RMAN recovery catalog and also have the controlfile AUTOBACKUP enabled. If we do not do either and we lose all the controlfiles, we cannot restore the controlfiles even if we have taken a backup to tape as shown in the case below. We will […]
Read More
 

Script – Check RMAN Backup Status

  • Posted by Arjun Raja
  • On July 28, 2009
  • 2 Comments
Scripts to check backup status and timings of database backups – This script will be run in the database, not the catalog. Login as sysdba – This script will report on all backups – full, incremental and archivelog backups – col STATUS format a9 col hrs format 999.99 select SESSION_KEY, INPUT_TYPE, STATUS, to_char(START_TIME,'mm/dd/yy hh24:mi') start_time, […]
Read More
 1

Change DBID of database

  • Posted by Arjun Raja
  • On July 14, 2009
  • 0 Comments
After a clone of a database to another machine with the same database name or if a new database name is created by recreating the controlfile, there is a need to recreate the dbid especially if the new database is to be registered in the same RMAN catalog. This ensures especially if an RMAN catalog […]
Read More
 

RMAN Backup and Recovery scenarios

  • Posted by Gavin Soorma
  • On July 14, 2009
  • 0 Comments
The following notes will illustrate the steps required to recover from a number of common failure scenarios that a DBA could possibly face in his career. RMAN Restore on another machine with different file system- same database name RMAN duplicate database on the same host Loss Of All Files – Disaster Recovery Incomplete Recovery (until […]
Read More
 

ORA-19755 error using block change tracking file.

  • Posted by Arjun Raja
  • On July 13, 2009
  • 1 Comments
While restoring and recovering a database UNTIL TIME which uses the block change tracking file, the recovery may be interrupted. This was the error – released channel: ch1 RMAN-00571: =========================================================== RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS =============== RMAN-00571: =========================================================== RMAN-03002: failure of recover command at 02/18/2009 09:33:29 ORA-00283: recovery session canceled due to errors RMAN-11003: […]
Read More
 

RMAN – Create Catalog

  • Posted by Arjun Raja
  • On July 13, 2009
  • 0 Comments
CREATING NEW RMAN CATALOG Database name to store catalog – RMAN10CAT 1. Create Tablespace for catalog owner in catalog database Login to RMAN10CAT database as sysdba SQL> create tablespace rman10 datafile ‘/u03/oradata/rman10cat/rman10.dbf’ size 200m autoextend on; Create catalog owner in Catalog database create user rman10 identified by rman default tablespace rman10 temporary tablespace temp; Issue […]
Read More
 

RMAN Duplicate database on same host

  • Posted by Gavin Soorma
  • On July 11, 2009
  • 2 Comments
This note describes in detail the procedure used to duplicate an Oracle 10g database on the same host but with a different database name using RMAN (on a Linux platform). Download …
Read More
 

RMAN unregister database from catalog

  • Posted by Arjun Raja
  • On July 7, 2009
  • 0 Comments
1. Go to the database you wish to unregister and get its DBID: system@HERML> select dbid,name from v$database; DBID NAME —————– —————————— 2082566940 HERML 2. Connect to the recovery catalog-owning schema and determine the DB_KEY of the database in the recovery catalog: rman10@RMAN10P> select db_key,dbid,name from rc_database 2 where dbid = 2082566940; DB_KEY DBID NAME […]
Read More
 

10g RMAN restore on another machine

  • Posted by Arjun Raja
  • On July 7, 2009
  • 0 Comments
TASK: Restoring a tape backup onto another machine and bringing up the database. ORIGINAL DATABASE: RPSP ON ITLINUX25 AND TARGET IS RPSP ON ITLINUX26. PRE-REQUISITES: Make sure that the tdpo.opt file uses the same TDP library pointer on the new machine as the original machine – only then will RMAN restore the backup from the […]
Read More