APPSINFODB – Information Technology Group

Finding Solutions for DBA's to smile…….!!!!!!! DBA its just not Administration, its core of customer’s data integrity

  • Blog Stats

    • 36,440 hits
  • Visitors

    free counters
  • Vistors Access

  • Calendar

    July 2017
    M T W T F S S
    « Dec    
     12
    3456789
    10111213141516
    17181920212223
    24252627282930
    31  
  • Archives

  • Authors

Archive for the ‘Backup and Recovery’ Category

Recover Database using RMAN

Posted by appsinfo on November 22, 2012

 Oracle Database full Restore using RMAN

Scenario #: Let’s say your database is crashed due to some reason and you are not able to access the database or all data files were deleted or OS file system was restored due to crash or you attempted cloning and the clone failed and would like to restore from previous/last valid available full backup.

 Ideal Pre-Requisite: 

  • Valid full database backup available – controlfiles, datafiles, archivelogs
  • All OS file system and mounts points have been restored (LINUX or UNIX) 

Steps to Restore 

  • Create a directory on linux/unix machine  “mkdir restore” /U01/DEV/rman_backup/restore 
  • Locate the control file; we usually backup the control file in the same location along with data backup. 
  • Restore the control file

                 Connect to the database in nomount 

                $ sqlplus / as sysdba

                SQL> shutdown immediate OR shutdown abort

                SQL> startup nomount

                SQL> show parameter control_files

                SQL> exit

                $ rman target /

                …

                Connected to target database <SID> (not mounted)

                RMAN> restore controlfile from ‘/U01/DEV/rman_backup/controlfile_name.ctlbkup’; 

  • Restore the database

                                 RMAN> alter database mount;

                                RMAN> restore database;

                                RMAN> recover database;

                                RMAN> alter database open resetlogs;

Posted in Backup and Recovery | Tagged: | Leave a Comment »

Overview to Oracle 10g RMAN

Posted by appsinfo on February 24, 2009

Overview of the RMAN

Recovery Manager (RMAN) is an Oracle database client which performs backup and recovery tasks on an Oracle database.

The environment for RMAN must include:

  • a target database to backed up
  • the RMAN client

Optional RMAN components:

  • Flash Recovery Area: a disk location in which the database can store and manage files related to backup and recovery
  • Media Management Software: required for RMAN to interface with backup devices such as tape drives
  • Recovery Catalog Database: a separate database schema used to record RMAN activity against one or more target databases

Target Database

Is the database which needs to be backed up.

Password File

To connect to a target database the password file must exist in ORACLE_HOME/dbs. For more information refer oracle documentation.

RMAN Backup Formats: Image Copies and Backup Sets

RMAN backups can be stored in one of two formats:

  • Image Copy
  • Backup Set

Image Copies

An image copy is an exact copy of a single datafile, archived redo log file, or control file. Image copies are not stored in an RMAN-specific format. They are identical to the results of copying a file with the UNIX cp command. This is the same as a Cold Backup.

Backup Sets

A backup set is a collection of files (called backup pieces) each of which contain the backup to one or more database files. A backup set contains the data from one or more datafiles or archived redo logs, or control files or spfile. Datafiles and archivelogs cannot be mixed together in the same backup set.

Binary Compression of Backup Sets

You can use binary compression to reduce the size of your backup sets. The compression algorithm built into the Oracle server is tuned specifically for efficient compression of Oracle archived logs and datafiles, and will generally yield better compression than general-purpose compression utilities not tuned for Oracle database files.

RMAN Client

RMAN is a command-line-oriented database client. From the RMAN client you can issue RMAN commands and SQL statements to perform and report on backup and recovery operations.

The RMAN executable is typically installed in the same directory as the other database executables. On Unix systems, for example, the RMAN executable is located in $ORACLE_HOME/bin.

The RMAN executable is located in $ORACLE_HOME/bin.

RMAN Repository

RMAN maintains metadata about the target database and its backup and recovery operations in the RMAN repository. The metadata includes:

  • the target database schema
  • archived redo logs
  • all backup files on disk or tape

You can display RMAN repository information with the LIST REPORT and SHOW commands.

RMAN repository data is always stored in the control file of the target database. The CONTROL_FILE_RECORD_KEEP_TIME initialization parameter controls how long backup records are kept in the control file before those records are re-used to hold information about more recent backups. The repository can also be kept in a recovery catalog, a separate database that keeps historical data on backup activities much longer than the control file and preserves backup information if the control file is lost.

Flash Recovery Area

The Automatic Disk-Based Backup and Recovery feature simplifies managing disk space and files related to backup and recovery, by managing all backup and recovery related files in a flash recovery area. You set the flash recovery area size and location, using the DB_RECOVERY_FILE_DEST and DB_RECOVERY_FILE_DEST_SIZE initialization parameters. You also specify a retention policy that dictates when backups may be discarded. RMAN then manages your backup storage, deleting obsolete backups and backups already copied to tape when space is needed, but keeping as many backups on disk as space permits.

Recovery Catalog

In addition to RMAN repository records, the recovery catalog can also hold RMAN stored scripts, sequences of RMAN commands for common backup tasks

Posted in Backup and Recovery | Leave a Comment »

New Backup Features in Oracle Database 11g

Posted by appsinfo on November 15, 2008

Data Recovery Advisor
The Data Recovery Advisor is a new tool aimed at reducing a user’s time spent analyzing and formulating a suitable recovery plan for a given failure. A ‘failure’ in the context of the DRA can be a missing, inaccessible, or wrong version of a file (e.g. control file, data file), physical corruptions resulting from I/O errors, or logical block inconsistency. After identifying all current failures, the DRA then recommends the optimal, feasible recovery plan, and if the user desires, automatically executes a selected recovery plan. All DRA functions can be accessed via EM or RMAN’s command-line interface.

Multisection Backups
RMAN can back up or restore a single file in parallel by dividing the work among multiple channels. Each channel backs up one file section, which is a contiguous range of blocks. This speeds up overall backup and restore performance, and particularly for bigfile tablespaces, in which a data file can be sized upwards of several hundred GB to TB’s.

Fast Backup Compression
In addition to the Oracle Database 10g backup compression algorithm (BZIP2), RMAN now supports the ZLIB algorithm, which offers 40% better performance, with a trade-off of no more than 20% lower compression ratio, versus BZIP2.

Network-enabled Database Duplication
A clone database on a remote site can now be easily created directly over the network with the enhanced DUPLICATE command, without the need for existing backups.

Virtual Private Catalog
A recovery catalog administrator can grant visibility of a subset of registered databases in the catalog to specific RMAN users.

Integration with Windows Volume Shadow Copy Services (VSS)
The Oracle database can participate in the VSS infrastructure on Windows platforms, with compatible backup management applications and storage systems. This feature allows VSS-enabled backup management applications to snapshot the Oracle database and restore at the datafile, tablespace, or database level.

Posted in Backup and Recovery | Leave a Comment »

New Backup Features in Oracle Database 10g Release 2

Posted by appsinfo on November 15, 2008

Backup Set Encryption
Backup security is vital to the well-being of any company. Backups should only be able to be opened and read by their creators. With Oracle Database 10gR2, backup sets made to disk can now be encrypted, for the whole database or particular tablespaces, using the new CONFIGURE ENCRYPTION FOR [DATABASE | TABLESPACE …] option.

Unused Block Compression
With unused block compression (enabled by default), only the currently used blocks are read and written during a full backup. This speeds up backups and reduces backup size. In previous releases, blocks that are currently unused, but had been used at some point in the past, were required to continue to be backed up. Also, blocks that have never been used are never backed up.

Dynamic Channel Allocation for RAC Environments
By configuring the PARALLELISM parameter, RMAN will dynamically allocate the specified number of channels across all active RAC nodes, to perform the backup or restore operation. RMAN utilizes Oracle Clusterware (formerly known as Cluster Ready Services) to allocate channels to the least loaded nodes, to perform the operations. In this way, the overall backup or restore workload can be distributed across the RAC nodes more efficiently.

Enterprise Manager Enhancements
Oracle Enterprise Manager, a single, integrated solution for administering and monitoring systems and applications based on the Oracle technology stack, is further enhanced for managing and monitoring backup jobs.

Database Control allows DBAs to view all backup jobs by date range and backup type (e.g. full, datafile, archive log), along with their status (e.g. “completed”, “completed with warnings”), input and output sizes, and output rate. Each backup job can be further drilled down to review input files and output backup sets/image copies, their sizes, and compression ratio (if enabled).

Grid Control offers several enhancements to manage backups across the enterprise. Backup jobs can be viewed across all target databases, and a failed job can be easily restarted without having to resubmit the job again. In case a backup job fails, the DBA can be notified immediately via email. In addition, user-defined RMAN scripts can be created as jobs and applied to any number of target databases. The recovery wizard has also been enhanced to allow restore and recovery to a different Oracle home, in the event that the original Oracle home or database is lost.

Posted in Backup and Recovery | Leave a Comment »

Backup Image Copy

Posted by appsinfo on November 15, 2008

IMAGE COPY

Its again a kind of backup. The advantage of uing Image copy is its not in RMAN proprietary format.

Backup Format

RMAN backup is not in oracle format but in RMAN format. Oracle backup comprises of backup sets and it consists of backup pieces. Backup sets are logical entity. In oracle 9i it gets stored in a default location.

There are two type of backup sets

Datafile backup sets,

Archivelog backup sets

One more important point of data file backup sets is it do not include empty blocks. A backup set would contain many backup pieces. A single backup piece consists of physical files which are in RMAN proprietary format.

You can go to RMAN prompt by just typing rman. RMAN executable is present in ORACLE_HOME/bin location.

bash-2.05$ rman

Recovery Manager: Release 10.2.0.1.0 – Production on Mon Nov 12 02:16:55 2008

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

RMAN>

You can use target connect to connect to database. The database it will connect to depends on the environment variable ORACLE_HOME.

RMAN> connect target

connected to target database: ORCL (DBID=1156435946)

Alternatively you can use “rman TARGET SYS/oracle@test NOCATALOG” to connect to the RMAN of “test” instance.

Here we will be using target database control file to store all the information required for RMAN, like backupsets and backup image information etc.

Backup Database:

RMAN> shutdown immediate

using target database control file instead of recovery catalog
database closed
database dismounted
Oracle instance shut down

RMAN> startup mount

connected to target database (not started)
Oracle instance started
database mounted

Total System Global Area 1258291200 bytes

Fixed Size 1978336 bytes
Variable Size 318771232 bytes
Database Buffers 922746880 bytes
Redo Buffers 14794752 bytes

RMAN> backup database
2> ;

Starting backup at 12-NOV-08
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=155 devtype=DISK
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
input datafile fno=00001 name=/dy/oracle/product/oradata/orcl/system01.dbf
input datafile fno=00003 name=/dy/oracle/product/oradata/orcl/sysaux01.dbf
input datafile fno=00005 name=/dy/oracle/product/oradata/orcl/example01.dbf
input datafile fno=00002 name=/dy/oracle/product/oradata/orcl/undotbs01.dbf
input datafile fno=00004 name=/dy/oracle/product/oradata/orcl/users01.dbf
channel ORA_DISK_1: starting piece 1 at 12-NOV-08
channel ORA_DISK_1: finished piece 1 at 12-NOV-08
piece handle=/dy/oracle/product/flash_recovery_area/ORCL/backupset/2008_11_12/

o1_mf_nnndf_TAG20081112T031355_3b8zv57d_.bkp tag=TAG20081112T031355 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:01:36
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
including current control file in backupset
including current SPFILE in backupset
channel ORA_DISK_1: starting piece 1 at 12-NOV-08
channel ORA_DISK_1: finished piece 1 at 12-NOV-08
piece handle=/dy/oracle/product/flash_recovery_area/ORCL/backupset/2008_11_12/

o1_mf_ncsnf_TAG20081112T031355_3b8zy7xr_.bkp tag=TAG20081112T031355 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:06
Finished backup at 12-NOV-08

Posted in Backup and Recovery | Leave a Comment »