How to Backup and Restore a RAC Database

This example illustrates how to backup and recover a 2-node, 10gR2 RAC cluster on

Linux. Backup and recovery operations for RAC databases are similar to that of single

instance databases, however there are some key differences – the goal of this post is to

highlight the steps required for recoverying RAC databases.

Backup the Database

Use the method you prefer. I used a NOCATALOG, disk-based backup for this example,

using the scripts found here.

Simulate the Failure

Shutdown the database and simulate a failure by dropping and/or corrupting a datafile.

# export ORACLE_SID=em1

# . oraenv

# srvctl stop database -d em

# srvctl status database -d em

Instance em1 is not running on node rac1

Instance em2 is not running on node rac2

# cd /u02/oracle/ora1/em

# rm sysaux01.dbf

Verify the Failure

# srvctl start instance -d em -i em1

PRKP-1001 : Error starting instance em1 on node rac1

CRS-0215: Could not start resource ‘ora.em.em1.inst’.

You will find something similar to the following in the instance’s alert log:

Sat May 24 16:53:47 2008

Errors in file /u02/oracle/logs/em/bdump/em1_dbw0_16947.trc:

ORA-01157: cannot identify/lock data file 3 – see DBWR trace file

ORA-01110: data file 3: ‘/u02/oracle/ora1/em/sysaux01.dbf’

ORA-27037: unable to obtain file status

Linux Error: 2: No such file or directory

Additional information: 3

ORA-1157 signalled during: ALTER DATABASE OPEN…

Sat May 24 16:53:49 2008

Shutting down instance (abort)

Restore the Database

First, take the database out of cluster mode via:

# sqlplus “/ as sysdba”

SQL*Plus: Release 10.2.0.3.0 – Production on Sat May 24 17:02:17 2008

Copyright (c) 1982, 2006, Oracle.  All Rights Reserved.

Connected to an idle instance.

SQL> startup nomount;

ORACLE instance started.

Total System Global Area  268435456 bytes

Fixed Size                  1261300 bytes

Variable Size             251658508 bytes

Database Buffers            8388608 bytes

Redo Buffers                7127040 bytes

SQL> alter system set cluster_database=false scope=spfile sid=’*’;

System altered.

SQL> shutdown immediate;

ORA-01507: database not mounted

ORACLE instance shut down.

Then restore the database via RMAN:

# rman target=/

Recovery Manager: Release 10.2.0.3.0 – Production on Sat May 24

17:04:10 2008

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

connected to target database (not started)

RMAN> startup mount;

Oracle instance started

database mounted

Total System Global Area     268435456 bytes

Fixed Size                     1261300 bytes

Variable Size                251658508 bytes

Database Buffers               8388608 bytes

Redo Buffers                   7127040 bytes

RMAN> restore database;

Starting restore at 24-MAY-08

using target database control file instead of recovery catalog

allocated channel: ORA_DISK_1

channel ORA_DISK_1: sid=154 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 /u02/oracle/ora1/em/system01.dbf

restoring datafile 00002 to /u02/oracle/ora1/em/undotbs01.dbf

restoring datafile 00003 to /u02/oracle/ora1/em/sysaux01.dbf

restoring datafile 00004 to /u02/oracle/ora1/em/users01.dbf

restoring datafile 00005 to /u02/oracle/ora1/em/example01.dbf

restoring datafile 00006 to /u02/oracle/ora1/em/undotbs02.dbf

restoring datafile 00007 to /u02/oracle/ora2/em/mgmt.dbf

restoring datafile 00008 to /u02/oracle/ora1/em/mgmt_ecm_depot1.dbf

channel ORA_DISK_1: reading from backup piece

/u02/oracle/ora3/em/backups/0ijh6j4t_1_1

channel ORA_DISK_1: restored backup piece 1

piece handle=/u02/oracle/ora3/em/backups/0ijh6j4t_1_1

tag=FHB_EM1200805241630

channel ORA_DISK_1: restore complete, elapsed time: 00:06:36

Finished restore at 24-MAY-08

Since my database is so small, I will simply restore the entire thing, however, since you

know which datafile is missing, you could simply restore it and then recover the database

as necessary.

Recover the Database

RMAN> recover database;

Starting recover at 24-MAY-08

using channel ORA_DISK_1

starting media recovery

media recovery complete, elapsed time: 00:00:03

Finished recover at 24-MAY-08

RMAN> alter database open;

database opened

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 rows

ORACLE error from target database:

ORA-06502: PL/SQL: numeric or value error: character to number

conversion error

Afterwards, place the database back into cluster mode and startup both instances:

# sqlplus “/ as sysdba”

SQL*Plus: Release 10.2.0.3.0 – Production on Sat May 24 17:16:36 2008

Copyright (c) 1982, 2006, Oracle.  All Rights Reserved.

Connected to:

Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 – Production

With the Partitioning, Real Application Clusters, OLAP and Data Mining

options

SQL> alter system set cluster_database=true scope=spfile sid=’*’;

System altered.

SQL> shutdown immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

# srvctl start database -d em

[oracle@rac1 bdump]$ srvctl status database -d em

Instance em1 is running on node rac1

Instance em2 is running on node rac2

Advertisements

RMAN Question & Answers

What is RMAN and How to configure it?

  •   RMAN is an Oracle Database client
  •   It performs backup and recovery tasks on your databases and automates administration of your backup strategies
  •   It greatly simplifies the dba jobs by managing the production database’s backing up, restoring, and recovering database files
  •   This tool integrates with sessions running on an Oracle database to perform a range of backup and recovery activities, including maintaining an RMAN repository of historical data about backups
  •  There is no additional installation required for this tool
  •   It is by default get installed with the oracle database installation
  •   The RMAN environment consists of the utilities and databases that play a role in acking up your data
  •   We can access RMAN through the command line or through Oracle Enterprise Manager

2) Why to use RMAN?

RMAN gives you access to several backup and recovery techniques and features not available with user-managed backup and recovery. The most noteworthy are the following:

Automatic specification of files to include in a backup

Establishes the name and locations of all files to be backed up

Maintain backup repository

  •    Backups are recorded in the control file, which is the main repository of RMAN metadata
  •   Additionally, you can store this metadata in a recovery catalog

Incremental backups 

  • Incremental backup stores only blocks changed since a previous backup
  • Thus, they provide more compact backups and faster recovery, thereby reducing the need to apply redo during datafile media recovery

Unused block compression: 

In unused block compression, RMAN can skip data blocks that have never been used

Block media recovery

We can repair a datafile with only a small number of corrupt data blocks without taking it offline or restoring it from backup

Binary compression

A binary compression mechanism integrated into Oracle Database reduces the size of backups

Encrypted backups

RMAN uses backup encryption capabilities integrated into Oracle Database to store backup sets in an encrypted format

Corrupt block detection

RMAN checks for the block corruption before taking its backup

3) How RMAN works?

  •  RMAN backup and recovery operation for a target database are managed by RMAN client
  •  RMAN uses the target database control file to gather metadata about the target database and to store information about its own operations
  •   The RMAN client itself does not perform backup, restore, or recovery operations
  •   When you connect the RMAN client to a target database, RMAN allocates server sessions on the target instance and directs them to perform the operations
  •   The work of backup and recovery is performed by server sessions running on the  target database
  •   A channel establishes a connection from the RMAN client to a target or auxiliary database instance by starting a server session on the instance
  •   The channel reads data into memory, processes it, and writes it to the output device
  •   When you take a database backup using RMAN, you need to connect to the target database using RMAN Client
  •   The RMAN client can use Oracle Net to connect to a target database, so it can be located on any host that is connected to the target host through Oracle Net
  •   For backup you need to allocate explicit or implicit channel to the target database
  • An RMAN channel represents one stream of data to a device, and corresponds to one database server session.
  •  This session dynamically collect information of the files from the target database control file before taking the backup or while restoring
  •   For example if you give ‘ Backup database ‘ from RMAN, it will first get all the datafiles information from the controlfile
  •   Then it will divide all the datafiles among the allocated channels. (Roughly equal size of work as per the datafile size)
  •   Then it takes the backup in 2 steps

Step1:

The channel will read all the Blocks of the entire datafile to find out all the formatted blocks to backup

Note:

RMAN do not take backup of the unformatted blocks

Step2:

In the second step it takes back up of the formatted blocks

Example:

  • This is the best advantage of using RMAN as it only takes back up of the required blocks
  •   Lets say in a datafile of 100 MB size, there may be only 10 MB of use full data and rest 90 MB is free then RMAN will only take backup of those 10 MB

4) What O/S and oracle user privilege required using RMAN?

  •   RMAN always connects to the target or auxiliary database using the SYSDBA privilege
  •   RMAN always connects to the target or auxiliary database using the SYSDBA privilege
  •   Its connections to a database are specified and authenticated in the same way as SQL*Plus connections to a database
  •   The O/S user should be part of the DBA group
  •   For remote connection it needs the password file Authentication
  •   Target database should have the initialization parameter REMOTE_LOGIN_PASSWORDFILE set to EXCLUSIVE or SHARED

5) RMAN terminology:

A target database:

  •   An Oracle database to which RMAN is connected with the TARGET keyword
  •   A target database is a database on which RMAN is performing backup and recovery operations
  •   RMAN always maintains metadata about its operations on a database in the control file of the database

A recovery Catalog:

  •   A separate database schema used to record RMAN activity against one or more target databases
  •   A recovery catalog preserves RMAN repository metadata if the control file is lost, making it much easier to restore and recover following the loss of the control file
  •   The database may overwrite older records in the control file, but RMAN maintains records forever in the catalog unless deleted by the user

Backup sets:

  • RMAN can store backup data in a logical structure called a backup set, which is the smallest unit of an RMAN backup
  •   One backup set contains one or more datafiles a section of datafile or archivelogs

Backup Piece:

  •  A backup set contains one or more binary files in an RMAN-specific format
  •   This file is known as a backup piece
  •   Each backup piece is a single output file
  •   The size of a backup piece can be restricted; if the size is not restricted, the backup set will comprise one backup piece
  •   Backup piece size should be restricted to no larger than the maximum file size that your filesystem will support

Image copies:

  • An image copy is a copy of a single file (datafile, archivelog, or controlfile)
  •   It is very similar to an O/S copy of the file
  •   It is not a backupset or a backup piece
  •   No compression is performed

Snapshot Controlfile:

  • When RMAN needs to resynchronize from a read-consistent version of the control file, it creates a temporary snapshot control file
  •   The default name for the snapshot control file is port-specific

Database Incarnation:

  • Whenever you perform incomplete recovery or perform recovery using a backup control file, you must reset the online redo logs when you open the database
  •   The new version of the reset database is called a new incarnation
  •   The reset database command directs RMAN to create a new database incarnation record in the recovery catalog
  •   This new incarnation record indicates the current incarnation

6) What is RMAN Configuration and how to configure it?

  • The RMAN backup and recovery environment is preconfigured for each target database
  •   The configuration is persistent and applies to all subsequent operations on this target database, even if you exit and restart RMAN
  •   RMAN configured settings can specify backup devices, configure a connection to a backup device , policies affecting backup strategy, encryption algorithm, snap shot controlfile loaion  and others
  •   By default there are few default configuration are set when you login to RMAN
  •   You can customize them as per your requirement
  •   Any time you can check the current setting by using the “Show all” command
  •   CONFIGURE command is used to create persistent settings in the RMAN environment, which apply to all subsequent operations, even if you exit and restart RMAN

7) How to check RMAN configuration?

RMAN>Show all;

8) How to reset to default configuration?

To reset the default configuration setting use Connect to the target database from sqlplus and run
SQL> connect <sys/passwd as sysdba>@target_database;
SQL> execute dbms_backup_restore.resetConfig;

RMAN Catalog Database

9) What is Catalog database and How to configure it?
This is a separate database which contains catalog schema
You can use the same target database as the catalog database but it’s not at all recommended

10) How Many catalog database I can have?

  • You can have multiple catalog databases for the same target database
  •   But at a time you can connect to only 1 catalog database via RMAN. Its not recommended to have multiple catalog database

11) Is this mandatory to use catalog database?

       No! It’s an optional one

12) What is the advantage of catalog database?

  •  Catalog database is a secondary storage of backup metadata
  •   It’s very useful in case you lost the current controlfile, as all the backup information are there in the catalog schema
  •   Secondly from contolfile the older backup information are aged out depending upon the control_file_record_keep_time
  •   RMAN catalog database mainten the history of data

13) What is the difference between catalog database & catalog schema?
Catalog database is like any other database which contains the RMAN catalog user’s schema

14)  What happen if catalog database lost?

  • Since catalog database is an optional there is no direct effect of loss of catalog database
  •   Create a new catalog database and register the target database with the newly created catalog one All the backup information from the target database current controlfile will be updated to the catalog schema
  •   If any backup information which is aged out from the target database then you need to manually catalog those backup pieces

RMAN backup:

15)  What are the database file’s that RMAN can backup?
RMAN can backup Controlfile, Datafiles, Archive logs, standby database controfile, Spfile

16) What are the database file’s that RMAN cannot backup?

RMAN can not take backup of the pfile, Redo logs, network configuration files, password files, external tables and the contents of the Oracle home files

17) Can I have archivelogs and datafile backup in a single backupset?
No.  We can not put datafiles and archive logs in the same backupset

18)  Can I have datafiles and contolfile backup in a single backup set?

  •  Yes
  •   If the controlfile autobackup is not ON then RMAN takes backup of controlfile along with the datafile 1, whenever you take backup of the database or System tablespace

19) Can I regulate the size of backup piece and backup set?

  •  Yes!
  •   You can set max size of the backupset as well as the backup piece
  •   By default one RMAN channel creates a single backupset with one backup piece in it
  •   You can use the MAXPIECESIZE channel parameter to set limits on the size of backup pieces
  •   You can also use the MAXSETSIZE parameter on the BACKUP and CONFIGURE commands to set a limit for the size of backup sets

20) What is the difference between backup set backup and Image copy backup?

  •  A backup set is an RMAN-specific proprietary format, whereas an image copy is a bit-for-bit copy of a file
  •   By default, RMAN creates backup sets

21) What is RMAN consistent backup and inconsistent backup?

  •  A consistent backup occurs when the database is in a consistent state
  •   That means backup of the database taken after a shutdown immediate, shutdown normal or shutdown transactional
  •   If the database is shutdown with abort option then its not a consistent backup
  • A backup when the database is up and running is called an inconsistent backup
  •   When a database is restored from an inconsistent backup, Oracle must perform media recovery before the database can be opened, applying any pending changes from the redo logs
  •   You can not take inconsistent backup when the database is in Noarchivelog mode

22)  Can I take RMAN backup when the database is down?
No!

You can take RMAN backup only when the target database is Open or in Mount stage

It’s because RMAN keep the backup metadata in controfile

Only in open or mount mode controlfile is accessible

23)  Do I need to place the database in begin backup mode while taking RMAN inconsistent backup?
RMAN does not require extra logging or backup mode because it knows the format of data blocks

RMAN is guaranteed not to back up fractured blocks

No extra redo is generated during RMAN backup

24) Can I compress RMAN backups?
ü  RMAN supports binary compression of backup sets

ü  The supported algorithms are BZIP2 (default) and ZLIB

ü  It’s not recommended to compress the RMAN backup using any other OS or third party utility

Note:

ü  RMAN compressed backup with BZIP2 provides great compression but is CPU intensive

ü  Using ZLIB compression requires the Oracle Database 11g Advanced Compression Option and is only supported with an 11g database

ü  The feature is not backward compatible with 10g databases

25) Can I encrypt RMAN backup?
ü  RMAN supports backup encryption for backup sets

ü  You can use wallet-based transparent encryption, password-based encryption, or both

ü  You can use the CONFIGURE ENCRYPTION command to configure persistent transparent encryption

ü  Use the SET ENCRYPTION, command at the RMAN session level to specify password-based encryption

26)  Can RMAN take backup to Tape?

ü  Yes!

ü  We can use RMAN for the tape backup

ü  But RMAN can not able to write directly to tape

ü  You need to have third party Media Management Software installed

ü  Oracle has published an API specification which Media Management Vendor’s who are members of Oracle’s Backup Solutions Partner program have access to

ü  Media Management Vendors (MMVs) then write an interface library which the Oracle server uses to write and read to and from tape
Starting from oracle 10g R2 oracle has its Own Media management software for the database backup to tape called OSB

27) How RMAN Interact with Media manager?

ü  Before performing backup or restore to a media manager, you must allocate one or more channels or configure default channels for use with the media manager to handle the communication with the media manager

ü  RMAN does not issue specific commands to load, label, or unload tapes

ü  When backing up, RMAN gives the media manager a stream of bytes and associates a unique name with this stream

ü  When RMAN needs to restore the backup, it asks the media manager to retrieve the byte stream

ü  All details of how and where that stream is stored are handled entirely by the media manager

28) What is Proxy copy backup to tape?

ü  Proxy copy is functionality, supported by few media manager in which they handle the entire data movement between datafiles and the backup devices

ü  Such products may use technologies such as high-speed connections between storage and media subsystems to reduce load on the primary database server

ü  RMAN provides a list of files requiring backup or restore to the media manager, which in turn makes all decisions regarding how and when to move the data

29) What is Oracle Secure backup?

ü  Oracle Secure Backup is a media manager provided by oracle that provides reliable and secure data protection through file system backup to tape

ü  All major tape drives and tape libraries in SAN, Gigabit Ethernet, and SCSI environments are supported

30) Can I restore or duplicate my previous version database using a later version of Oracle?

For example, is it possible to restore a 9i backup while using the 10g executables?

It is possible to use the 10.2 RMAN executable to restore a 9.2 database (same for 11.2 to 11.1 or 11.1 to 10.2, etc) even if the restored datafiles will be stored in ASM

RMAN is configured so that a higher release is able to restore a lower release, but it is strongly suggested you use only the same version

31) Can I restore or duplicate between two different patchset levels?

ü  As you can restore between different Oracle versions, you can also do so between two different patchset levels

Alter database open resetlogs upgrade;
OR
alter database open resetlogs downgrade;

32) Can I restore or duplicate between two different versions of the same operating system?

For example, can I restore my 9.2.0.1.0 RMAN backup taken against a host running Solaris 9 to a different machine where 9.2.0.1.0 is installed but where that host is running Solaris 10?
If the same Oracle Server installation CDs (media pack) can be used to install 9.2.0.1.0 on Solaris 9 and Solaris 10, this type of restore is supportable

33) Is it possible to restore or duplicate when the bit level (32 bit or 64 bit) of Oracle does not match?

For example, is it possible to restore or duplicate my 9.2. 64-bit database to a 9.2.32-bit installation?

  •  It is preferable to keep the same bit version when performing a restore/recovery
  •   However, excluding the use of duplicate command, the use of the same operating system platform should allow for a restore/recovery between bit levels (32 bit or 64 bit) of Oracle
  •   Note, this may be specific to the particular operating system and any problems with this should be reported to Oracle Support
  •   If you will be running the 64-bit database against the 32-bit binary files or vice versa, after the recovery has ended the database bit version must be converted using utlirp.sql
  • If you do not run utlirp.sql you will see errors including but not limited to:

ORA-06553: PLS-801: INTERNAL ERROR [56319]

34) Can I restore or duplicate my RMAN backup between two different platforms such as Solaris to Linux?

In general, you cannot restore or duplicate between two different platforms

35) What are the corruption types?

ü  Datafile Block Corruption – Physical/Logical

ü  Table/Index Inconsistency

ü  Extents Inconsistencies

ü  Data Dictionary Inconsistencies

Scenarios:

Goal: How to identify all the corrupted segments in the database reported by RMAN?

Solution:

Step 1: Identify the corrupt blocks (Datafile Block Corruption – Intra block corruption)

RMAN> backup validate check logical database;

To make it faster, it can be configured to use PARALLELISM with multiple channels:

RMAN> run {
allocate channel d1 type disk;
allocate channel d2 type disk;
allocate channel d3 type disk;
allocate channel d4 type disk;
backup validate check logical database;
}

Step2:  Using the view v$database_block_corruption:

SQL> select * from v$database_block_corruption;

FILE#          BLOCK#          BLOCKS CORRUPTION_CHANGE# CORRUPTIO
————————————————————————————————————
6              10                          1          8183236781662                      LOGICAL
6              42                          1                  0                                      FRACTURED
6              34                          2                  0                                      CHECKSUM
6              50                          1      8183236781952                          LOGICAL
6              26                          4                  0                                      FRACTURED

5 rows selected.

Datafile Block Corruption – Intra block corruption

  • It refers to intra block corruptions that may cause different errors like ORA-1578, ORA-8103, ORA-1410, ORA-600 etc.
  •   Oracle classifies the corruptions as Physical and Logical

ü  To identify both Physical and Logical Block Corruptions use the “CHECK LOGICAL” option

ü  It checks the complete database for both corruptions without actually doing a backup

Solution1:

$ rman target /
RMAN> backup check logical validate database;

$ rman target /
RMAN> backup check logical database;

Solution2:

ü  Chek the view V$DATABASE_BLOCK_CORRUPTION to identify the block corruptions detected by RMAN

Solution3: DBVerify – Identify Datafile Block Corruptions

ü  DBVERIFY identify Physical and Logical Intra Block Corruptions by default

ü  Dbverify cannot be run for the whole database in a single command

ü  It does not need a database connection either

dbv file=<datafile name> blocksize=<datafile Block size>

RMAN Vs DBVerify – Datafile Intra Block Corruption

When the logical option is used by RMAN, it does exactly the same checks as DBV does for intra block corruption.

RMAN can be run with PARALLELISM using multiple channels making it faster than DBV which can not be run in parallel in a single command

DBV checks for empty blocks. In 10g RMAN may not check blocks in free extents when Locally Managed Tablespaces are used. In 11g RMAN checks for both free and used extents.

  • Both DBV and RMAN (11g) can check for a range of blocks. RMAN: VALIDATE DATAFILE 1 BLOCK 10 to 100;.  DBV: start=10 end=100
  • RMAN keeps corruption information in the control file (v$database_block_corruption, v$backup_corruption). DBV does not.
  • RMAN may not report the corruption details like what is exactly corrupted in a block reported as a LOGICAL corrupted block. DBV reports the corruption details in the screen or in a log file.
  • DBV can scan blocks with a higher SCN than a given SCN.
  • DBV does not need a connection to the database.

dentify TABLE / INDEX Inconsistency

Table / Index inconsistencies is when an entry in the Table does not exist in the Index or vice versa. The common errors are ORA-8102, ORA-600 [kdsgrp1], ORA-1499 by “analyze validate structure cascade”.

  • The tool to identify TABLE / INDEX inconsistencies is the ANALYZE command:

analyze table <table name> validate structure cascade;

When an inconsistency is identified, the above analyze command will produce error ORA-1499 and a trace file.

35) What Happens When A Tablespace/Database Is Kept In Begin Backup Mode?

ü  One danger in making online backups is the possibility of inconsistent data within a block

ü  For example, assume that you are backing up block 100 in datafile users.dbf

ü  Also, assume that the copy utility reads the entire block while DBWR is in the middle of updating the block

ü  In this case, the copy utility may read the old data in the top half of the block and the new data in the bottom top half of the block

ü  The result is called a fractured block, meaning that the data contained in this block is not consistent at a given SCN

Therefore oracle internally manages the consistency as below :

The first time a block is changed in a datafile that is in hot backup mode, the entire block is written to the redo log files, not just the changed bytes

Normally only the changed bytes (a redo vector) is written

In hot backup mode, the entire block is logged the first time

This is because you can get into a situation where the process copying the datafile and DBWR are working on the same block simultaneously

Lets say they are and the OS blocking read factor is 512bytes (the OS reads 512 bytes from disk at a time). The backup program goes to read an 8k Oracle block. The OS gives it 4k. Meanwhile — DBWR has asked to rewrite this block. the OS schedules the DBWR write to occur right now. The entire 8k block is rewritten. The backup program starts running again (multi-tasking OS here) and reads the last 4k of the block. The backup program has now gotten an fractured block — the head and tail are from two points in time.
We cannot deal with that during recovery. Hence, we log the entire block image so that during recovery, this block is totally rewritten from redo and is consistent with itself atleast. We can recover it from there.

2.  The datafile headers which contain the SCN of the last completed checkpoint are not updated while a file is in hot backup mode. This lets the recovery process understand what archive redo log files might be needed to fully recover this file.

To limit the effect of this additional logging, you should ensure you only place one tablepspace at a time in backup mode and bring the tablespace out of backup mode as soon as you have backed it up. This will reduce the number of blocks that may have to be logged to the minimum possible.
Try to take the hot/online backups when there is less / no load on the database, so that less redo will be generated.

How to Drop a Datafile from a Tablespace

Contents

Introduction

I have been asked on several occasions about how to drop a datafile from a tablespace. Much of the confusion comes from the ALTER DATABASE DATAFILE <datafile name> OFFLINE DROP statement. This statement is not meant to allow you to remove a datafile. What the statement really means is that you are offlining the datafile with the intention of dropping the tablespace. In this article I attempt to erase the confusion surrounding this ALTER DATABASE statement and how to successfully drop a datafile from a tablespace.

There are two reasons why a DBA would want to remove a datafile from a tablespace:

  • The DBA may have mistakenly added a file to a tablespace, or perhaps made the file much larger than intended and now want to remove it.
  • The DBA may be involved in a recovery scenario and the database won’t start because a datafile is missing.

Until Oracle Database 10g Release 2, Oracle did not provide an interface for dropping datafiles in the same way you would drop a schema object such as a table or a user. Prior to 10gr2, once the DBA created a datafile for a tablespace, the datafile could not be removed. This article discusses several workarounds to this limitation as well as the new DROP DATAFILE / TEMPFILE statement introduced with Oracle 10g R2.

As with any critical operation like dropping datafiles, ensure that you have a full backup of the database before running the statements and commands highlighted in this article.

Tablespace / Datafile Example

The examples provided in this article were performed on the USERS tablespace using Oracle Database 11g release 2.

To determine how many and which datafiles make up a tablespace, you can use the following query:


SELECT TABLESPACE_NAME, FILE_NAME, FILE_ID

FROM DBA_DATA_FILES

WHERE TABLESPACE_NAME = ‘name_of_tablespace‘;

For example:

select tablespace_name, file_name, file_id

from dba_data_files

where tablespace_name = ‘USERS’;

TABLESPACE_NAME  FILE_NAME                                                   FILE_ID

—————- ———————————————————- ——–

USERS            C:\ORACLE\ORADATA\ORCL\DATAFILE\O1_MF_USERS_5YM2N5D0_.DBF         5

To add a 5MB datafile to the USERS tablespace configured with Oracle Managed Files (OMF), use the following:

SQL> alter tablespace users add datafile size 5m;

Tablespace altered.

Re-query the data dictionary to determine the name and location of the new datafile:

select tablespace_name, file_name, file_id

from dba_data_files

where tablespace_name = ‘USERS’;

TABLESPACE_NAME  FILE_NAME                                            FILE_ID

—————- ————————————————— ——–

USERS      C:\ORACLE\ORADATA\ORCL\DATAFILE\O1_MF_USERS_5YM2N5D0_.DBF    5

USERS      C:\ORACLE\ORADATA\ORCL\DATAFILE\O1_MF_USERS_6LD56LRR_.DBF    6   <- New File

Drop Datafile by Oracle Release

An Oracle datafile is a physical part of the database that occupies storage within a file system to store user data. Whenever a datafile gets created or grows, more space on the storage device is required. There are times when a datafile grows beyond an acceptable threshold, an unwanted datafile is created, or a datafile gets created in the wrong tablespace and the datafile needs to be dropped. This section describes the different methods to drop a datafile from a tablespace depending on the Oracle release.

Oracle8i

There is no direct SQL statement to drop datafiles from a tablespace in Oracle8i. In that case we need to drop the tablespace after all data has been moved to a new tablespace.

  1. Create a new tablespace to hold moved objects.
  2. Move all tables to the new tablespace.
  3. Move all indexes to the new tablespace.
  4. Move all other segments to the new tablespace.
  5. Drop the old tablespace using the INCLUDING CONTENTS option.
  6. Remove all datafiles belonging to dropped tablespace using OS command.

Oracle9i Release 1

As with Oracle8i, there is no direct SQL statement to drop datafiles from a tablespace in Oracle9i Release 1. In that case we need to drop the tablespace after all data has been moved to a new tablespace.

  1. Create a new tablespace to hold moved objects.
  2. Move all tables to the new tablespace.
  3. Move all indexes to the new tablespace.
  4. Move all other segments to the new tablespace.
  5. Drop the old tablespace using the INCLUDING CONTENTS AND DATAFILES option.
Oracle9i Release 1 introduced the “AND DATAFILES” clause to the DROP TABLESPACEstatement which will automatically remove physical datafiles from the file system.

Oracle9i Release 2

As with Oracle8i and Oracle9i Release 1, there is no direct SQL statement to drop datafiles from a tablespace in Oracle9i Release 2. In that case we need to drop the tablespace after all data has been moved to a new tablespace.

  1. Create a new tablespace to hold moved objects.
  2. Move all tables to the new tablespace.
  3. Move all indexes to the new tablespace.
  4. Move all other segments to the new tablespace.
  5. Drop the old tablespace using the INCLUDING CONTENTS AND DATAFILES option.

Although datafiles cannot be dropped using a direct SQL statement, tempfiles can be dropped starting in Oracle9i Release 2 using SQL as follows:

SQL> alter database tempfile ‘/u02/oradata/TESTDB/temp01.dbf’ drop including datafiles;

Database altered.

Oracle Database 10g Release 1

Use the same procedures documented for Oracle9i Release 2

Oracle Database 10g Release 2

Starting with Oracle Database 10g Release 2, you can use the DROP DATAFILE and DROP TEMPFILE clauses of the ALTER TABLESPACE command to drop a single datafile or tempfile. The datafile must be empty. A datafile is considered to be empty when no extents remain allocated from it.

When you drop a datafile or tempfile, references to the datafile or tempfile are removed from the data dictionary and control files, and the physical file is deleted from the file system or Automatic Storage Management (ASM) disk group.

The following example drops an empty datafile O1_MF_USERS_6LD56LRR_.DBF that belongs to the USERS tablespace.

SQL> alter tablespace users drop datafile ‘C:\ORACLE\ORADATA\ORCL\DATAFILE\O1_MF_USERS_6LD56LRR_.DBF’;

Tablespace altered.

The next example drops the tempfile O1_MF_TEMP_6LDH8JQB_.TMP which belongs to the TEMP tablespace.

SQL> alter tablespace temp drop tempfile ‘C:\ORACLE\ORADATA\ORCL\DATAFILE\O1_MF_TEMP_6LDH8JQB_.TMP’;

Tablespace altered.

Note that this is equivalent to the following statement that was introduced in Oracle9i Release 2 to drop tempfiles:

SQL> alter database tempfile ‘C:\ORACLE\ORADATA\ORCL\DATAFILE\O1_MF_TEMP_6LDH8JQB_.TMP’ drop including datafiles;

Database altered.

The following are restrictions for dropping datafiles and tempfiles using the new ALTER TABLESPACE DROP DATAFILE | TEMPFILE command:

  • The database must be open.
  • If a datafile is not empty, it cannot be dropped. If you must remove a datafile that is not empty and that cannot be made empty by dropping schema objects, you must drop the tablespace that contains the datafile.
  • You cannot drop the first or only datafile in a tablespace. This means that DROP DATAFILE cannot be used with a bigfile tablespace.
  • You cannot drop datafiles in a read-only tablespace.
  • You cannot drop datafiles in the SYSTEM tablespace.
  • If a datafile in a locally managed tablespace is offline, it cannot be dropped.

Oracle Database 11g Release 1

Use the same procedures documented for Oracle Database 10g Release 2

Oracle Database 11g Release 2

Use the same procedures documented for Oracle Database 10g Release 2

Alternative Scenarios

Scenario 1

If the datafile you wish to remove is the only datafile in that tablespace, you can simply drop the entire tablespace using the following:


DROP TABLESPACE <tablespace name> INCLUDING CONTENTS;

The above statement will remove the tablespace, the datafile, and the tablespace’s contents from the data dictionary. All of the objects that where contained in that tablespace are permanently removed. If the datafile was configured using Oracle Managed Files (a feature introduced in Oracle9i Release 1), the physical datafile will be automatically removed from the file system using the DROP TABLESPACE statement.

Oracle9i Release 1 introduced the “AND DATAFILES” clause to the DROP TABLESPACE statement which will automatically remove physical datafiles from the file system:


DROP TABLESPACE <tablespace name> INCLUDING CONTENTS AND DATAFILES;

When not using OMF or the “AND DATAFILES” clause, Oracle will not drop the physical datafile after the DROP TABLESPACE statement. This action would need to be performed manually at the operating system. Depending on the OS (Windows for example), you may have to completely shut down the Oracle instance and associated service before the operating system will allow you to delete the file because of file locks still still being held by Oracle.

Scenario 2

If you have more than one datafile in the tablespace, and you do not need the information contained in that tablespace, or if you can easily recreate the information in this tablespace, then use the same statement as above:

DROP TABLESPACE <tablespace name> INCLUDING CONTENTS;

Again, this will remove the tablespace, the datafiles, and the tablespace’s contents from the data dictionary. Oracle will no longer have access to any object that was contained in this tablespace. You can then use CREATE TABLESPACE and re-import the appropriate objects back into the tablespace.

Scenario 3

If you have more than one datafile in the tablespace and you wish to keep the objects that reside in the other datafile(s) which are part of this tablespace, then you must export all the objects inside the tablespace. Although Oracle introduced the DROP DATAFILEcommand in 10gR2, if the datafile is non-empty, it cannot be removed. Start by gathering information on the current datafiles within the tablespace by running the following query in SQL*Plus:

SELECT TABLESPACE_NAME, FILE_NAME, FILE_ID

FROM DBA_DATA_FILES

WHERE TABLESPACE_NAME = ‘name_of_tablespace‘;

You now need to identify which objects are inside the tablespace for the purpose of running an export. To do this, run the following query:


SELECT OWNER, SEGMENT_NAME, SEGMENT_TYPE

FROM DBA_SEGMENTS

WHERE TABLESPACE_NAME = ‘name_of_tablespace‘;

Now, export all the objects that you wish to keep.

Once the export is done, drop the tablespace:


DROP TABLESPACE name_of_tablespace INCLUDING CONTENTS AND DATAFILES;

Note that this PERMANENTLY removes all objects in this tablespace. Recreate the tablespace with the desired datafile(s), then import the objects into that tablespace.

Always remember that the ALTER DATABASE DATAFILE <datafile name> OFFLINE DROP statement is not meant to allow you to remove a datafile. What the statement really means is that you are offlining the datafile with the intention of dropping the tablespace.

If you are running in archivelog mode, you can also use ALTER DATABASE DATAFILE <datafile name> OFFLINE instead of OFFLINE DROP. Once the datafile is offline, Oracle no longer attempts to access it, but it is still considered part of that tablespace. This datafile is marked only as offline in the controlfile and there is no SCN comparison done between the controlfile and the datafile during startup (This also allows you to startup a database with a non-critical datafile missing). The entry for that datafile is not deleted from the controlfile to give us the opportunity to recover that datafile.

Scenario 4

If you do not wish to follow any of the procedures documented in this article, there are other functions that can be performed besides dropping the tablespace.

  • If the reason you wanted to drop the file is because you mistakenly created the file of the wrong size, then consider using the RESIZE option.
  • If you really added the datafile by mistake, and Oracle has not yet allocated any space within this datafile, then you can use ALTER DATABASE DATAFILE <filename> RESIZE; statement to make the file smaller than 5 Oracle blocks. If the datafile is re-sized to smaller than 5 oracle blocks, then it will never be considered for extent allocation. At some later date, the tablespace can be rebuilt to exclude the incorrect datafile.

Data guard GAP Detection and Resolution

Overview:

An archive gap is a range of missing archived redo logs created whenever the standby system is unable to receive the next archived redo log generated by the primary  db.

For example, an archive gap occurs when the network becomes unavailable and automatic archiving from the primary database to the standby database stops. When the network is available again, automatic transmission of the redo data from the primary database to the failed standby database resumes.

Methods of Gap Resolution:

Data Guard provides 2 methods for gap resolution, Automatic and FAL (Fetch Archive Log). The automatic method requires no configuration while FAL requires configuration via init.ora parameters.

Automatic Gap Resolution:

In both 9.0.1 and 9.2.0 Automatic Gap Resolution is implemented during log transport processing. As the LGWR or ARCH process begins to send redo over to the standby, the sequence number of the log being archived is compared to the last sequence received by the RFS process on the standby. If the RFS process detects that the archive log being received is greater than the last sequence received plus one, then the RFS will piggyback a request to the primary to send the missing archive logs. Since the standby destination requesting the gap resolution is already defined by the LOG_ARCHIVE_DEST_n parameter on the primary, the ARCH process on the primary sends the logs to the standby and notifies the LGWR that the gaps have been resolved.

Starting in 9.2.0, automatic gap resolution has been enhanced. In addition to the above, the ARCH process on the primary database polls all standby databases every minute to see if there is a gap in the sequence of archived redo logs. If a gap is detected then the ARCH process sends the missing archived redo log files to the standby databases that reported the gap. Once the gap is resolved, the LGWR process is notified that the site is up to date.

FAL Gap Resolution:

As the RFS process on the standby receives an archived log, it updates the standby controlfile with the name and location of the file. Once the MRP (Media Recovery Process) sees the update to the controlfile, it attempts to recover that file. If the MRP process finds that the archived log is missing or is corrupt, FAL is called to resolve the gap or obtain a new copy. Since MRP has no direct communications link with the primary, it must use the FAL_SERVER and FAL_CLIENT initialization parameters to resolve the gap.Both of these parameters must be set in the standby init.ora. The 2 parameters are defined as:

FAL_SERVER:  An OracleNet service name that exist in the standby tnsnames.ora file that points to the primary db listener. The FAL_SERVER parameter can contain a comma delimited list of locations that should be attempted during gap resolution.

FAL_CLIENT:   An OracleNet service name that exist in the primary tnsnames.ora file that points to the standby database listener. The value of FAL_CLIENT should also be listed as the service in a remote archive destination pointing to the standby.

Once MRP needs to resolve a gap it uses the value from FAL_SERVER to call the primary database. Once communication with the primary has been established, MRP passes the FAL_CLIENT value to the primary ARCH process. The primary ARCH process locates the remote archive destination with the corresponding service name and ships the missing archived redo logs. If the first destination listed in FAL_SERVER is unable to resolve the gap then the next destination is attempted until either the gap is resolved or all FAL_SERVER destination have been tried.

As of 9.2.0 FAL Gap Resolution only works with Physical Standby databases as the process is tied to MRP. Gap recovery on a logical standby database is  handled through the heartbeat mechanism.

Simulating Gap Recovery

The follow steps can be used to illustrate and verify both automatic and FAL gap recovery. As the steps involve shutting down the standby database, which can impact disaster recovery, it is recommended to perform these procedures in a test environment.

Automatic Gap Resolution:

  1. Shutdown the physical standby database.
  2. Determine the current sequence on the primary database.
  3. Perform at least three log switches on the primary database.
  4. Verify that the logs did not get transferred to the standby archive dest.
  5. Start the standby database.
  6. Perform a log switch on the primary and verify that the gap gets resolvedon the standby.

FAL Gap Resolution:

  1. In the standby init.ora define the fal_server and fal_client parameters.
  2. Bounce the standby database so that the parameters are put into effect.
  3. Perform three log switches on the primary database.
  4. In the standby_archive_dest directory delete the middle archive log onthe standby.
  5. Start managed recovery and verify that the gap is resolved by FAL_SERVERand FAL_CLIENT.

Manually Resolving a Gap:

In some rare cases it might be necessary to manually resolve gaps. The following section describes how to query the appropriate views to determine if a gap exists.

On your physical standby database:

Query the V$ARCHIVE_GAP view:

SQL> SELECT * FROM V$ARCHIVE_GAP;

THREAD#   LOW_SEQUENCE#  HIGH_SEQUENCE#

———–             ————-       ————–

1                        443                    446

The query results show that your physical standby database is currently missing logs from sequence 443 to sequence 446 for thread 1. After you identify the gap, issue the following SQL statement on the primary database to locate the archived redo logs on your primary database:

SQL> SELECT NAME FROM V$ARCHIVED_LOG WHERE THREAD#=1 AND DEST_ID=1  AND  SEQUENCE# BETWEEN 443 AND 446;

NAME

——————————————————-

/u01/oradata/arch/arch_1_443.arc

/u01/oradata/arch/arch_1_444.arc

/u01/oradata/arch/arch_1_445.arc

Copy the logs returned by the query to your physical standby database and

register using the ALTER DATABASE REGISTER LOGFILE command.

SQL> ALTER DATABASE REGISTERLOGFILE    ‘/u01/oradata/stby/arch/arch_1_443.arc’;

SQL> ALTER DATABASE REGISTER LOGFILE    ‘/u01/oradata/stby/arch/arch_1_444.arc’;

SQL> ALTER DATABASE REGISTER LOGFILE    ‘/u01/oradata/stby/arch/arch_1_445.arc’;

Once the log files have been registered in the standby controlfile, you can restart the MRP process.

On a logical standby database:

Query the DBA_LOGSTDBY_LOG view.

SQL> SELECT THREAD#, SEQUENCE#, FILE_NAME FROM DBA_LOGSTDBY_LOG L    WHERE NEXT_CHANGE# NOT IN  (SELECT FIRST_CHANGE# FROM DBA_LOGSTDBY_LOG WHERE L.THREAD# =THREAD#)  ORDER BY THREAD#,SEQUENCE#;

THREAD#          SEQUENCE#             FILE_NAME

———-                    ———-             ———————————————–

1                         451                   /u01/oradata/logical_stby/arch/arch_1_451.arc

1                         453                   /u01/oradata/logical_stby/arch/arch_1_453.arc

Copy the missing logs to the logical standby system and register them using the ALTER DATABASE REGISTER LOGICAL LOGFILE statement on your logical standby db.

SQL> ALTER DATABASE REGISTER LOGICAL LOGFILE /u01/oradata/logical_stby/arch/arch_1_452.arc;

After you register these logs on the logical standby database, you can restart log apply services.

ORA-01940: Cannot drop user that is currently connected

SQL> drop user username cascade;
drop user username cascade
*
ERROR at line 1:
ORA-01940: cannot drop a user that is currently connected

Solution 

Make sure the user is logged out, then re-execute the command.

If you are permitted to kill the session of that user then find out the connected user sid and serial# by,
SQL> select sid, serial# from v$session where username = ‘USERNAME’;

 SID    SERIAL#
———- ———-
 268       1268
 315       1223
If RAC use GV$SESSION view to get instance#
NOTE  Before killing session you may wish to lock the account for further connection attempts. This is extremely necessary to drop users who automatically establish session like to drop an application user or to drop a user who performs batch jobs.

SQL> Alter user username account lock;

Now kill the connected session.

SQL> alter system kill session ‘268,1268’;           (use @instance# if RAC db)
       System altered

SQL> alter system kill session ‘315,1223’;
      System altered

And then drop the user.

SQL> drop user username cascade;
 User dropped
 

Data Recovery Advisor(DRA)

Oracle 11g come up with one new cool feature for Database Backup & recovery called DRA (Data Recovery Advisor) which help us to recover the database without any trouble with few RMAN commands.
What is Data Recovery Advisor?
DRA is an oracle database tool that automatically diagnoses data failures, determines and presents appropriate repair options and executes repairs at user requests.
The following RMAN commands are use to perform Data Recovery Advisor.
1. List Failure
2. Advise Failure
3. Repair Failure
4. Change Failure
1. List Failure:     List failure command gives us information regarding failures and the effect of these on database operations. Each failures uniquely identified by failure number.
2. Advise Failure:   Advise failure give us advise for how to deal with failure against database means advise failure give us solution for particular failure.
3. Repair failure:  Repair failure command gives us rman generated scrits which restore and recover database from backup.
4. Change failure:  Change failure is RMAN command which change the failure status or priority.
Like there is two status : OPEN or CLOSED and Priority is HIGH or LOW.
If some failure is rman showing HIGH and we want to change it to LOW then using change failure command we can change it.
1. Suppose I lost my system data file.
2. Now I am not worry because I have database backup with 11g database.
3. I need to just connect with RMAN, after connect with RMAN I do the following task.
RMAN>      List failure;

Using target database control file instead of recovery catalog
List of Database Failures
=========================
Failure ID Priority Status Time Detected Summary
———- ——– ——— ————- ——-
602 CRITICAL OPEN 26-JUL-08 System datafile 1: ‘C:\APP\M.TAJ\ORADATA\TEST\SYSTEM01.DBF’ is missing
After got the failure description we can get “advise” from oracle about failure through advice failure command.
RMAN>     Advise failure;

List of Database Failures
=========================
Failure ID Priority Status Time Detected Summary
———- ——– ——— ————- ——-
602 CRITICAL OPEN 26-JUL-08 System datafile 1: ‘C:\APP\M.TAJ\ORADATA\TEST\SYSTEM01.DBF’ is missing
analyzing automatic repair options; this may take some time
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=152 device type=DISK
analyzing automatic repair options complete
Mandatory Manual Actions
========================
no manual actions available
Optional Manual Actions
=======================
1. If file C:\APP\M.TAJ\ORADATA\TEST\SYSTEM01.DBF was unintentionally renamed or moved, restore it
Automated Repair Options
========================
Option Repair Description
—— ——————
1 Restore and recover datafile 1
Strategy: The repair includes complete media recovery with no Data loss Repair script: c:\app\m.taj\diag\rdbms\test\test\hm\reco_2508517227.hm
Above is rman advise regarding particular failure if above suggested repair option is helpful and fix the current problem then ok otherwise need to call oracle support services.
now check oracle suggested repair options or scripts.
RMAN>   repair failure preview;

Strategy:
The repair includes complete media recovery with no data loss Repair script: c:\app\m.taj\diag\rdbms\test\test\hm\reco_2508517227.hm
contents of repair script:
# restore and recover datafile
restore datafile 1;
recover datafile 1;
Above is suggested script from RMAN to restore and recover database for particular failure, if suppose we want to use above script then again run “repair failure” command without ‘preview’ keyword.
RMAN>   repair failure ;
Strategy: The repair includes complete media recovery with no data loss
Repair script: c:\app\m.taj\diag\rdbms\test\test\hm\reco_2508517227.hm
contents of repair script:
# restore and recover datafile
restore datafile 1;
recover datafile 1;
Do you really want to execute the above repair (enter YES or NO)? YES
executing repair script
Starting restore at 26-JUL-08
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00001 to C:\APP\M.TAJ\ORADATA\TEST\SYSTEM01.DBF
channel ORA_DISK_1: reading from backup piece C:\APP\M.TAJ\PRODUCT\11.1.0\DB_1\DATABASE5JMEU48_1_1
channel ORA_DISK_1: piece handle=C:\APP\M.TAJ\PRODUCT\11.1.0\DB_1\DATABASE5JMEU48_1_1 tag=TAG20080726T124808
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:05:25
Finished restore at 26-JUL-08
Starting recover at 26-JUL-08
using channel ORA_DISK_1
starting media recovery
media recovery complete, elapsed time: 00:00:03
Finished recover at 26-JUL-08
If we lost “tempfiles”  in 10gr1 we need to manually RE-CREATE temporary tablespace but in 11g it is automatically done by ORACLE .

RMAN Backup Views

The following Oracle dynamic views can be used to obtain RMAN information stored in the control file:

  •  V$ARCHIVED_LOG:    show which archives have been created, backed up, and      cleared in the database
  •  V$BACKUP_CORRUPTION:    show which blocks have been found to be corrupt during a backup of a backup set
  • V$BACKUP_DATAFILE:   useful for creating equal-sized backup sets by determining the number of blocks in each datafile. It can also help you find the number of corrupt blocks in the datafile
  •    V$BACKUP_DEVICE:   display information about supported backup devices. DISK is  not returned because it is always available
  •  V$BACKUP_FILES:   display information about all RMAN backups (image copies and backup sets) and archived logs. The view simulates the LIST BACKUP and LIST COPYcommands
  •  V$BACKUP_PIECE:    show backup pieces created for backup sets 
  • V$BACKUP_REDOLOG:   show archived logs stored in backup sets
  •  V$BACKUP_SET :    show backup sets that have been created
  •  V$BACKUP_SPFILE:   display information about server parameter files in backup sets 
  • V$COPY_CORRUPTION:     show which blocks have been found to be corrupt during an image copy
  • V$DATABASE_BLOCK_CORRUPTION:    list database blocks marked as corrupt during the most recent RMAN backup 
  • V$RMAN_CONFIGURATION:    list information about RMAN persistent configuration settings
  • V$PROXY_ARCHIVEDLOG, V$PROXY_DATAFILE:     proxy settings for RMAN
  • V$BACKUP_ASYNC_IO, V$BACKUP_SYNC_IO:    backup performance statistics

RMAN Recovery Catalog

This article gives the steps to create the recovery catalog.

  1. Identify the database where catalog needs to be created.

Here I am using rcvcat as the catalog database

2. Create the tablespace to store the catalog information.

SQL> CREATE TABLESPACE CATALOG_DATA
DATAFILE ‘/u01/oradata/rvcvat/catalog_data_01.dbf’
SIZE 500M;

  1. Create the user that is going to be the catalog owner

SQL> CREATE USER RMAN
IDENTIFIED BY RMAN
DEFAULT TABLESPACE CATALOG_DATA
TEMPORARY TABLESPACE TEMP;

  1. Grant the roles and the privileges

SQL> GRANT RESOURCE TO RMAN;
SQL> GRANT CONNECT TO RMAN;

SQL> GRANT RECOVERY_CATALOG_OWNER TO RMAN;

SQL> ALTER USER RMAN DEFAULT ROLE ALL;
SQL> ALTER USER RMAN QUOTA UNLIMITED ON RMAN_DATA;

  1. Create the catalog

[oracle@naikh]$ rman catalog rman/rman@rvccat
RMAN> create catalog;

  1. Register the target database

[oracle@naikh]$ export ORACLE_SID=orcl

[oracle@naikh]$ rman target / catalog rman/rman@rvccat

RMAN> register database;

Oracle Backup and Recovery Solutions

When implementing a backup and recovery strategy, you have the following solutions available:

– Recovery Manager (RMAN)

This tool integrates with sessions running on an Oracle database to perform a
range of backup and recovery activities, including maintaining an RMAN
repository of historical data about backups. You can access RMAN through the command line or through Oracle Enterprise Manager.

– User-managed backup and recovery

In this solution, you perform backup and recovery with a mixture of host
operating system commands and SQL*Plus recovery commands.
Both of the preceding solutions are supported by Oracle and are fully documented, but RMAN is the preferred solution for database backup and recovery.

RMAN performs the same types of backup and recovery available through user-managed techniques more easily, provides a common interface for backup tasks across different host operating systems, and offers a number of backup techniques not available through user-managed methods.
Most of this manual focuses on RMAN-based backup and recovery.

User-managed backup and recovery techniques are covered in Section VIII, “Performing User-Managed Backup and Recovery.” RMAN gives you access to several backup and recovery techniques and features not available with user-managed backup and recovery. The most noteworthy are the following:

– Incremental backups

An incremental backup stores only blocks changed since a previous backup.
Thus, they provide more compact backups and faster recovery, thereby reducing the need to apply redo during datafile media recovery. If you enable block change tracking, then you can improve performance by avoiding full scans of every input datafile. You use the BACKUP INCREMENTAL command to perform incremental backups.

– Block media recovery

You an repair a datafile with only a small number of corrupt data blocks without taking it offline or restoring it from backup. You use the RECOVER command to perform block media recovery.

– Unused block compression

In unused block compression, RMAN can skip data blocks that have never been used and, in some cases, used blocks that are currently unused.

– Binary compression

A binary compression mechanism integrated into Oracle Database reduces the size of backups.

– Encrypted backups

RMAN uses backup encryption capabilities integrated into Oracle Database to store backup sets in an encrypted format. To create encrypted backups on disk, the database must use the Advanced Security Option. To create encrypted backups directly on tape, RMAN must use the Oracle Secure Backup SBT interface, but does not require the Advanced Security Option.
Whether you use RMAN or user-managed methods, you can supplement physical backups with logical backups of schema objects made with Data Pump Export utility.

You can later use Data Pump Import to re-create data after restore and recovery. Logical backups are for the most part beyond the scope of the backup and recovery documentation.

– Oracle Flashback Technology

As explained in Oracle Database Concepts, Oracle Flashback Technology complements your physical backup and recovery strategy. This set of features provides an additional layer of data protection. Specifically, you can use flashback features to view past states of data and rewind your database without restoring backups or performing point-in-time recovery. In general, flashback features are more efficient and less disruptive than media recovery in most situations in which they apply.

– Logical Flashback Features

Most of the flashback features of Oracle operate at the logical level, enabling you to view and manipulate database objects. The logical-level flashback features of Oracle do not depend on RMAN and are available whether or not RMAN is part of your backup strategy. With the exception of Flashback Drop, the logical flashback features rely on undo data, which are records of the effects of each database update and the values overwritten in the update.

Oracle Database includes the following logical flashback features:

– Oracle Flashback Query

You can specify a target time and run queries against a database, viewing results as they would have appeared at the target time. To recover from an unwanted change like an update to a table, you could choose a target time before the error and run a query to retrieve the contents of the lost rows.

Oracle Database Advanced Application Developer’s Guide explains how to use this feature.

– Oracle Flashback Version Query

You can view all versions of all rows that ever existed in one or more tables in a specified time interval. You can also retrieve metadata about the differing versions of the rows, including start and end time, operation, and transaction ID of the transaction that created the version. You can use this feature to recover lost data values and to audit changes to the tables queried.

Oracle Database Advanced Aplication Developer’s Guide explains how to use this feature.

– Oracle Flashback Transaction Query

You can view changes made by a single transaction, or by all the transactions uring a period of time. Oracle Database Advanced Application Developer’s Guide xplains how to use this feature.

– Oracle Flashback Transaction

You can reverse a transaction. Oracle Database determines the dependencies between transactions and in effect creates a compensating transaction that reverses the unwanted changes. The database rewinds to a state as if the transaction, and any transactions that could be dependent on it, had never happened. Oracle
Database Advanced Application Developer’s Guide explains how to use this feature.

– Oracle Flashback Table

You can recover a table or set of tables to a specified point in time in the past
without taking any part of the database offline. In many cases, Flashback Table eliminates the need to perform more complicated point-in-time recovery operations. Flashback Table restores tables while automatically maintaining
associated attributes such as current indexes, triggers, and constraints, and in this way enabling you to avoid finding and restoring database-specific properties.

– Oracle Flashback Drop

You can reverse the effects of a DROP TABLE statement. “Rewinding a DROP
TABLE Operation with Flashback Drop” on page 16-7 explains how to use this
feature.

A flashback data archive enables you to use some of the logical flashback features to access data from far back in the past. A flashback data archive consists of one or more tablespaces or parts of tablespaces. When you create a flashback data archive, you specify the name, retention period, and tablespace. You can also specify a default flashback data archive. The database automatically purges old historical data the day after the retention period expires.

You can turn flashback archiving on and off for individual tables. By default, flashback archiving is turned off for every table.

– Flashback Database

At the physical level, Oracle Flashback Database provides a more efficient data protection alternative to database point-in-time recovery (DBPITR). If the current datafiles have unwanted changes, then you can use the RMAN command FLASHBACK DATABASE to revert the datafiles to their contents at a past time. The end product is much like the result of a DBPITR, but is generally much faster because it does not require restoring datafiles from backup and requires less redo than media recovery.

Flashback Database uses flashback logs to access past versions of data blocks and some information from archived redo logs. Flashback Database requires that you configure a flash recovery area for a database because the flashback logs can only be stored there. Flashback logging is not enabled by default. Space used for flashback logs is managed automatically by the database and balanced against space required for other files in the flash recovery area.

Oracle Database also supports restore points in conjunction with Flashback Database and backup and recovery. A restore point is an alias corresponding to a system change number (SCN). You can create a restore point at any time if you anticipate needing to return part or all of a database to its contents at that time. A guaranteed restore point ensures that you can use Flashback Database to return a database to the time of the restore point.

– Data Recovery Advisor

Oracle Database includes a Data Recovery Advisor tool that automatically diagnoses persistent data failures, presents appropriate repair options, and executes repairs at your request. Data Recovery Advisor provides a single point of entry for Oracle backup and recovery solutions. You can use Data Recovery Advisor through the Enterprise Manager Database Control or Grid Control console or through the RMAN command-line client.

A database failure usually manifests itself as a set of symptoms: error messages, alerts, trace files and dumps, and failed data integrity checks. Data Recovery Advisor automatically diagnoses and informs you of these failures. Within the context of Data Recovery Advisor, a failure is a persistent data corruption that can be directly mapped to a set of repair actions. Each failure has a status of open or closed. Each failure also has a priority of critical, high, or low.

Failures are detected by data integrity checks, which are diagnostic procedures executed to assess the health of the database or its components. If a data integrity check reveals a failure, then Data Recovery Advisor automatically assesses the effect of a set of failures and maps it to a set of repair options. In most cases, Data Recovery Advisor presents both automated and manual repair options.

Data Recovery Advisor determines the best automated repair option and its effect on the database. The repair option may include repairs such as datafile restore and recovery, media recovery, Flashback Database, and so on. Before presenting an automated repair option, Data Recovery Advisor validates it with respect to the specific environment and the availability of media components required to complete the proposed repair.

If you choose an automated repair option, then RMAN coordinates sessions on the Oracle database to perform the repair for you. The Data Recovery Advisor tool verifies the repair success and closes the appropriate failures.

RMAN BASICS

RMAN is not “too” different from WinZip. Since everybody knows WinZip, it will probably be easier to understand RMAN. Just like compressing a folder using WinZip, RMAN creates a compressed backup of the physical database files, including controlfiles, datafiles, archived logs and stores them somewhere. This somewhere can be a disk or a tape.

Therefore, the first important point to remember is:

“RMAN creates compressed backups of the physical database; if you prefer, RMAN zips the database, the way you like it”.

The usual definition for RMAN is given as,

Recovery Manager is a client/server application that uses database server sessions to perform backup and recovery. It stores metadata about its operations in the control file of the target database and, optionally, in a recovery catalog schema in an Oracle database.

Why Should we use RMAN

Ability to perform incremental backups.Ability to recover one block of a datafile.
Ability to perform the backup and restore with parallelization.
Ability to automatically delete archived redo logs after they are backed up.
Ability to automatically backup the control file and the SPFILE.
Ability to restart a failed backup without having to start from the beginning.
Ability to verify the integrity of the backup.
Ability to test the restore process without having to actually perform the restore.
Comparison of RMAN Automated and User-Managed Procedures
By using operating system commands for User-Managed Backup and Recovery , a DBA manually keeps track of all database files and backups. But RMAN performs these same tasks automatically.

Understanding the RMAN Architecture
An oracle RMAN comprises of RMAN EXECUTABLE This could be present and fired even through client side, TARGET DATABASE (This is the database which needs to be backed up) and RECOVERY CATALOG (Recovery catalog is optional otherwise backup details are stored in target database controlfile .)

About the RMAN Repository

The RMAN repository is a set of metadata that RMAN uses to store information about the target database and its backup and recovery operations. RMAN stores information about:

Backup sets and pieces
Image copies (including archived redo logs)
Proxy copies
The target database schema
Persistent configuration settings
If you start RMAN without specifying either CATALOG or NOCATALOG on the command line, then RMAN makes no connection to a repository. If you run a command that requires the repository, and if no CONNECT CATALOG command has been issued yet, then RMAN automatically connects in the default NOCATALOG mode. After that point, the CONNECT CATALOG command is not valid in the session.

Types of Database Connections

You can connect to the following types of databases.
Target database
RMAN connects you to the target database with the SYSDBA privilege. If you do not have this privilege, then the connection fails.

Recovery catalog database

This database is optional: you can also use RMAN with the default NOCATALOG option.

Auxiliary database

You can connect to a standby database, duplicate database, or auxiliary instance (standby instance or tablespace point-in-time recovery instance
Note:
That a SYSDBA privilege is not required when connecting to the recovery catalog. The only requirement is that the RECOVERY_CATALOG_OWNER role be granted to the schema owner.

Using Basic RMAN Commands

After you have learned how to connect to a target database, you can immediately begin performing backup and recovery operations. Use the examples in this section to go through a basic backup and restore scenario using a test database. These examples assume the following:

The test database is in ARCHIVELOG mode.
You are running in the default NOCATALOG mode.
The RMAN executable is running on the same host as the test database.

Connecting to the Target Database

rman TARGET /

If the database is already mounted or open, then RMAN displays output similar to the following:

Recovery Manager: Release 9.2.0.0.0

connected to target database: RMAN (DBID=1237603294)

Reporting the Current Schema of the Target Database
In this example, you generate a report describing the target datafiles. Run the report schema command as follows:

RMAN> REPORT SCHEMA; (RMAN displays the datafiles currently in the target database.

Backing Up the Database

In this task, you back up the database to the default disk location. Because you do not specify the format parameter in this example, RMAN assigns the backup a unique filename.

You can make two basic types of backups: full and incremental.

Making a Full Backup

Run the backup command at the RMAN prompt as follows to make a full backup of the datafiles, control file, and current server parameter file (if the instance is started with a server parameter file) to the default device type:
RMAN> BACKUP DATABASE;

Making an Incremental Backup

Incremental backups are a convenient way to conserve storage space because they back up only database blocks that have changed. RMAN compares the current datafiles to a base backup, also called a level 0 backup, to determine which blocks to back up.

RMAN> BACKUP INCREMENTAL LEVEL 1 DATABASE;

Backing Up Archived Logs
Typically, database administrators back up archived logs on disk to a third-party storage medium such as tape. You can also back up archived logs to disk. In either case, you can delete the input logs automatically after the backup completes.To back up all archived logs and delete the input logs (from the primary archiving destination only), run the backup command at the RMAN prompt as follows:

RMAN> BACKUP ARCHIVELOG ALL DELETE INPUT;

Listing Backups and Copies

To list the backup sets and image copies that you have created, run the list command as follows:

RMAN> LIST BACKUP;

To list image copies, run the following command:

RMAN> LIST COPY;