Archive for the ‘Backup and Recovery’ Category

Recovery Manager Features

RMAN is an Oracle utility that you use to manage the backup, restore, and recovery operations on Oracle databases. RMAN has a powerful command language that is independent of the operating system.

RMAN provides several features not available when you make user-managed backups with
operating system commands.

  • Frequently executed operations can be stored as scripts in the database.
  • With block change tracking enabled in the database RMAN can limit incremental backups to recording only those blocks that have changed since the previous backup.
  • RMAN can be used to manage the size of backup pieces and save time by parallelizing the backup operation.
  • RMAN can recover an individual corrupt data block or set of data blocks within a data file rather than restoring and recovering the entire data file.
  • RMAN operations can be integrated with the Oracle Database Scheduler to automate
    backup operations.
  • You can use RMAN to detect block corruption. The information relating to the block
    corruption that is detected during backup can be obtained by using the
    V$BACKUP_CORRUPTION and V$COPY_CORRUPTION dynamic views.
  • RMAN provides performance enhancements such as:
            – Automatic parallelization of backup, restore, and recovery operations
            – No generation of extra redo during online database backups
            – Backups can be restricted to limit reads per file, per second to avoid interfering   with OLTP work
            -  Prevention of flooding of any one file with reads and writes while still keeping a tape drive streaming, using multiplexing
  • RMAN has a media management API to work seamlessly with third-party media
    management tools interfacing with storage devices providing increased speed and
    reliability.
  • Under the user-managed method you need to keep track of all database files and backups. In a recovery situation you must locate backups for each data file, copy them to the correct place using operating system commands, and choose which redo log files to apply. RMAN manages these tasks automatically.
Advertisements

Backup Concepts

Database Backup

 

A whole database backup includes all data files and at least one control file.

Full backups make a copy of every data block within the files being backed up that contains data.

Partial database backups may include zero or more tablespaces, zero or more data files, and may or may not include a control file.

 

Incremental backups make a copy of all data blocks that have changed since some previous backup. Oracle Database 10g supports two levels of incremental backup (0-1). A level 0 or baseline backup is equivalent to a full backup and contains all data blocks. A level 1 incremental backs up all database blocks changed since the level 0 backup. To restore using incremental backups, the baseline backup must first be restored, and then the incremental.

Offline backups or Cold backups(also known as consistent backups) are taken while the database is not open. They are consistent because at the time of the backup, the SCN data file headers matches the SCN in the control files.

Online backups (also known as hot or inconsistent backups) are taken while the database is open. The backups are inconsistent because with the database open there is no guarantee that the data files are synchronized with the control files. Inconsistent backups require recovery in order to be used. For online backup, ARCHIVELOG mode should be enabled.

 

Modes of Backups

i. ARCHIVELOG

If the database is in noarchive log mode, online redo logs can be overwritten without making sure they are saved. This implies that a database cannot be recovered fully (it can be rolled forward to the last transaction) even if backups were made.

ii. NOARCHIVELOG

If the database is in log archive mode, the database makes sure that online redo logs are not overwritten before they have been archived. Database can be recovered to the last

Types of Backups

i. Physical Backup

Physical backups are backups of the physical files used in storing and recovering your database, such as datafiles, control files, and archived redo logs.

ii. Logical Backup

Logical backups contain logical data (tables or stored procedures) exported from a database with an Oracle export utility and stored in a binary file, for later re-importing into a database using the corresponding Oracle import utility.

 

Backup Types based on Physical Backup

For performing backup and recovery based on physical backups, you have two solutions available:

  • Recovery Manager (RMAN), a tool (with command-line client and Enterprise Manager GUI interfaces) that integrates with sessions running on the Oracle server to perform a range of backup and recovery activities, as well as maintaining a repository of historical data about your backups
  • The traditional user-managed backup and recovery, where you directly manage the files that make up your database with a mixture of host operating system commands and SQL*Plus backup and recovery-related capabilities

Recovery Manager isthe preferred solution for database backup and recovery. It can perform the same types of backup and recovery available through user-managed methods 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.

 

Image Copies and Backup Sets

 

Image copies are duplicates of data or archived log files (similar to simply copying the files using OS commands).

Backup sets are copies of one or more data or archived log files. With backup sets, empty data locks are not stored, thereby causing backup sets to use less space on disk or tape. Backup sets an be compressed to further reduce the space requirements of the backup.

Image copies must be backed up to disk whereas Backup sets can be sent to disk or directly to tape.

Cloning/Duplicating Database with RMAN

Cloning database ASM to ASM

Clone an Oracle database using RMAN duplicate (same server)

Duplicating Database using dynamic SQL

This tutorial is for cloning database on the same server.

1. Create Password File for Auxiliary Database

orapwd file=c:\oracle\ora92\database\PWDAUX.ora password=oracle

2.Create an Initialization Parameter for the Auxiliary Database

Copy the initialization parameter for the target database and make the necessary changes for the duplicated database.

SQL> create pfile=’c:\oracle\product\10.2.0\db\database\initAUX.ora’ from spfile;

After creating the initialization parameter for the duplicate database, and change at least the following parameters:

db_file_name_convert = ('C:\ORACLE\ORADATA\ORCL', 'C:\ORACLE\ORADATA\AUX')
log_file_name_convert = ('C:\ORACLE\ORADATA\ORCL', 'C:\ORACLE\ORADATA\AUX')
control_files = 'C:\oracle\oradata\AUX\control01.ctl'
              , 'C:\oracle\oradata\AUX\control02.ctl'
              , 'C:\oracle\oradata\AUX\control03.ctl'
db_name = 'AUX'
instance_name = 'AUX'
background_dump_dest = 'C:\oracle\admin\AUX\bdump'
core_dump_dest = 'C:\oracle\admin\AUX\cdump'
user_dump_dest = 'C:\oracle\admin\AUX\udump'
service_names = 'AUX.IDEVELOPMENT.INFO'
log_archive_dest_1 = 'location=C:\oracle\oradata\AUX\archive MANDATORY'
  

3. Create / Start the Auxiliary Instance

    Create new windows service for duplicate database AUX using oradim

oradim -new -sid AUX -intpwd oracle -startmode auto -pfile ‘C:\oracle\product\10.2.0\db\dbs\initaux.ora’

3. Ensure Oracle Net Connectivity to Auxiliary Database

Modify both the listener.ora and tnsnames.ora file to be able to connect to the auxiliary database. After making changes to the networking files, test the connection keeping in mind that you must be able to connect to the auxiliary instance with SYSDBA privileges, so a valid password file must exist.

C:\> lsnrctl stop LISTENER

C:\> lsnrctl start LISTENER

C:\> sqlplus sys/oracle@AUX as sysdba
It would connect as idle instance

SQL> startup nomount pfile=’C:\oracle\product\10.2.0\db\database\initAUX.ora’;
ORA-02778: Name given for the log directory is invalid

Check the location of your bdump file(other dump directories as well). If situation persists then change the bdump destination and other log file destinations.

SQL> startup nomount pfile=’C:\oracle\product\10.2.0\db\database\initAUX.ora’;

3. Mount or Open the Source Database

As mentioned in the pre-requisites section of this article, the target database should be either opened or mounted.

C:\> set ORACLE_SID=orcl
C:\> sqlplus "/ as sysdba"
SQL> startup open

4. Ensure You Have the Necessary Backups and Archived Redo Log Files

As mentioned in the pre-requisites section of this article, ensure that you have a current backup that you wish to use to create the duplicate database. Login to query the RMAN catalog:

C:\> rman target sys/passwd@ORCL

RMAN> list backup summary;
 

5.Login to Target and Auxiliary Database using RMAN

C:\> rman target sys/passwd@ORCL  auxiliary sys/password@AUX
 

6.Run the RMAN DUPLICATE DATABASE Command

The following RUN block can be used to fully duplicate the target database from the latest full backup.
Note that you can duplicate the database to a specific date/time using the UNTIL TIME '<DATE>' clause. For example, to duplicate the new database to yesterdays date/time, use the following:

duplicate target database to AUX until time 'SYSDATE-1';.

run {
# Allocate the channel for the duplicate work
allocate auxiliary channel ch1 type disk;

# Duplicate the database to AUX
set until sequence 156;

duplicate target database to AUX;

}

Reblog this post [with Zemanta]

Optimizing Import/Export

EXPORT:

  • Set the BUFFER parameter to a high value (e.g. 2Mb — entered as an integer "2000000")
  • Set the RECORDLENGTH parameter to a high value (e.g. 64Kb — entered as an integer "64000")
  • Use DIRECT=yes (direct mode export)
  • Stop unnecessary applications to free-up resources for your job.
  • If you run multiple export sessions, ensure they write to different physical disks.
  • DO NOT export to an NFS mounted filesystem. It will take forever.

IMPORT:

  • Create an indexfile so that you can create indexes AFTER you have imported data. Do this by setting INDEXFILE to a filename and then import. No data will be imported but a file containing index definitions will be created. You must edit this file afterwards and supply the passwords for the schemas on all CONNECT statements.
  • Place the file to be imported on a separate physical disk from the oracle data files
  • Increase DB_CACHE_SIZE (DB_BLOCK_BUFFERS prior to 9i) considerably in the init$SID.ora file
  • Set the LOG_BUFFER to a big value and restart oracle.
  • Stop redo log archiving if it is running (ALTER DATABASE NOARCHIVELOG;)
  • Create a BIG tablespace with a BIG rollback segment inside. Set all other rollback segments offline (except the SYSTEM rollback segment of course). The rollback segment must be as big as your biggest table (I think?)
  • Use COMMIT=N in the import parameter file if you can afford it
  • Use STATISTICS=NONE in the import parameter file to avoid time consuming to import the statistics.
  • Use ANALYZE=N in the import parameter file to avoid time consuming ANALYZE statements
  • Remember to run the indexfile previously created.

Improving Data Pump Export and Import:

Use Parallel option and export to as many files as the degree of parallelism.

expdp username/password directory=dump_dir filesize=1G dumpfile=full%U.dmp logfile=fulllog parallel=16

Increasing pga_aggregate_target to boost import.

Impdp Options

Analyze once after the load – Set analyze=n and analyze with dbms_stats after the load has completed.

Increase recordlength – Many set recordlength to 64k, but it needs to be a multiple of your I/O chunk size and db_block_size (or your multiple block size, e.g. db_32k_block_size).

Set commit=n – For tables that can afford not to commit until the end of the load, this option provides a significant performance increase.  Larger tables may not be suitable for this option due to the required rollback/undo space.

Dedicate a single, large rollback segment – Many professionals create a single large rollback segment and take all others offline during the import.

Set indexes=n – Index creation can be postponed until after import completes, by specifyingindexes=n.  If indexes for the target table already exist at the time of execution, import performs index maintenance when data is inserted into the table.  Setting indexes=neliminates this maintenance overhead.   You can also Use the indexfile parm to rebuild all the indexes once, after the data is loaded.

Defer CBO stats – Using impdp with the parameter exclude=statistics will greatly improve the import speed, but statistics will need to be re-analyzed or imported later.

Use the buffer parameter – By using a larger buffer setting, import can do more work before disk access is performed.

Disable logging – You can also use the hidden parameter _disable_logging = true to reduce redo, but beware that the resulting import will be unrecoverable.

Further read Oracle data load

RMAN Lists Crosschecks and Reports

RMAN Lists

For complete list of syntax and options see link

The primary purpose of the LIST command is to determine which backups or copies are available. Note that only backups and copies that completed successfully are stored in the repository. For example, you can list:

  • Backups (backup sets and proxy copies) or image copies recorded in the RMAN repository
  • Backups or image copies of a specified database, tablespace, datafile, archived redo log, or control file
  • Backups and image copies that have expired
  • Backups and image copies restricted by options such as time, path name, device type, tag, or recoverability
  • Incarnations of a specified database

list backup;

list backup of database; # lists backups of all files in database

list backup of datafile ‘/oracle/dbs/tbs_1.f’; #lists backup of specified datafile

list backup of tablespace SYSTEM; # lists all backups of SYSTEM tablespace

list backup of archivelog all; # lists all archived redo logs and copies of logs

list backup of controlfile; # lists all control file backups list expired backup;

list incarnation;

RMAN Crosschecks

Crosschecks update outdated RMAN repository information about backups whose repository records do not match their physical status. For example, if a user removes archived logs from disk with an operating system command, the repository still indicates that the logs are on disk, when in fact they are not.

If the backup is on disk, then the CROSSCHECK command determines whether the header of the file is valid. If the backup is on tape, then the command simply checks that the backup exists. The possible status values for backups are AVAILABLE, UNAVAILABLE, and EXPIRED.

CROSSCHECK BACKUP;

CROSSCHECK COPY; # crosschecks only disk copies of db files

CROSSCHECK BACKUPSET; # crosschecks backupsets on disk and SBT

CROSSCHECK BACKUP OF DATAFILE “?/oradata/trgt/system01.dbf” COMPLETED AFTER ‘SYSDATE-14’;

CROSSCHECK BACKUP OF ARCHIVELOG ALL SPFILE;


Reporting on Backups, Copies, and Database Schema

To gain more detailed information from the RMAN repository, generate a report. Use the REPORT command to answer questions such as the following:

  • Which files need a backup?
  • Which files have had unrecoverable operations performed on them?
  • Which backups or copies are obsolete and can be deleted?
  • What was the physical schema of the database at some previous time?
  • Which files have not been backed up recently?

report schema at time ‘SYSDATE-7’;

report need backup days 2 tablespace system;

report unrecoverable;

report obsolete;

REPORT NEED BACKUP INCREMENTAL = 1 DATABASE;

REPORT OBSOLETE RECOVERY WINDOW OF 7 DAYS; # lists backups or copies that are superfluous because more than 2 copies of the # files exist on tape

REPORT OBSOLETE REDUNDANCY = 2 DEVICE TYPE sbt;

REPORT SCHEMA AT SCN 1000; # schema as it existed at scn 1000

REPORT SCHEMA AT SEQUENCE 100 THREAD 1; # schema as it existed at log sequence 100

Monitor RMAN job progress

SELECT SID, SERIAL#, CONTEXT, SOFAR, TOTALWORK,ROUND(SOFAR/TOTALWORK*100,2) “%_COMPLETE”FROM V$SESSION_LONGOPS
WHERE OPNAME LIKE ‘RMAN%’
AND OPNAME NOT LIKE ‘%aggregate%’
AND TOTALWORK != 0   AND SOFAR <> TOTALWORK

User Managed Backup

User-managed backups can be either logical or physical. You can use the Export utility to make backups of logical objects such as tables, views, and stored procedures, and use the Import utility to restore these objects.

  • Files are backed up using operating system commands.
  • Backups are restored using operating system commands.
  • Recovery is accomplished using SQL and SQL*PLUS commands.

Basic Backup Methodology

The basic method for taking user-managed backups of the whole database is as follows:

  1. Identify the datafiles, control files, and archived redo logs to be backed up by querying dynamic performance views or data dictionary tables (refer to “Querying V$ Views to Obtain Backup Information” for procedures).
  2. Use an operating system command such as the UNIX cp command to back up datafiles and archived redo logs (refer to “Making User-Managed Backups of the Whole Database” for procedures).
  3. Use a SQL statement to back up the control file (refer to “Making User-Managed Backups of the Control File” for procedures).
  4. Use an operating system command such as the UNIX cp command to back up configuration files (refer to“Making User-Managed Backups of Miscellaneous Oracle Files” for procedures).

WHOLE DATABASE BACKUP

CONSISTENT DATABASE BACKUP

To make a consistent whole database backup:

  1. SQL> SHUTDOWN IMMEDIATE (can use shutdown normal , transactional as well)
  2. Use an operating system utility to make backups of all datafiles as well as all control files specified by the CONTROL_FILES parameter of the initialization parameter file. Also, back up the initialization parameter file and other Oracle product initialization files.
    cp /disk1/oracle/dbs/*.dbf /disk2/backup
  3. Restart Database
      Startup;
INCONSISTENT DATABASE BACKUP

Database should be in archivelog mode.

1. ALTER DATABASE BEGIN BACKUP;
2. Copy database files
3. ALTER DATABASE END BACKUP;   –to exit from the backup mode.

TABLESPACE BACKUPS

OFFLINE TABLESPACE BACKUPS

1. Identify the tablespace’s datafiles by querying the DBA_DATA_FILES view.

SELECT TABLESPACE_NAME, FILE_NAME
  FROM SYS.DBA_DATA_FILES
  order by tablespace_name;

2. ALTER TABLESPACE users OFFLINE NORMAL; (NORMAL bring TS online without recovery)
3. Back up the offline datafiles.
4. ALTER TABLESPACE users ONLINE;
5. ALTER SYSTEM ARCHIVE LOG CURRENT; (archive the unarchived redo logs )

ONLINE TABLESPACE BACKUPS

1. Identify the tablespace’s datafiles by querying the DBA_DATA_FILES view.

SELECT TABLESPACE_NAME, FILE_NAME
  FROM SYS.DBA_DATA_FILES
  order by tablespace_name;

2. ALTER TABLESPACE users begin backup;
3. Back up the offline datafiles.
4. ALTER TABLESPACE users end backup;
5. ALTER SYSTEM ARCHIVE LOG CURRENT;

 

CONTROLFILE BACKUPS

Back up the database’s control file, specifying a filename for the output binary file. The following SQL statement backs up a database’s control file to /oracle/backup/cf.bak:

ALTER DATABASE BACKUP CONTROLFILE TO ‘/oracle/backup/cf.bak’ REUSE;

 

To back up the control file to a trace file, mount or open the database and issue the following SQL statement:

ALTER DATABASE BACKUP CONTROLFILE TO TRACE;

If you specify neither the RESETLOGS nor NORESETLOGS option in the SQL statement, then the output is a trace file containing a CREATE CONTROLFILE ... NORESETLOGS statement. As in the case of binary control file backups, tempfile entries are not included in the trace output.

Startup and Shutdown

Starting Up Database

You can start an instance and database in a variety of ways:

  • start the instance without mounting a database
  • start the instance and mount the database, but leave it closed
  • start the instance, and mount and open the database in:
    • unrestricted mode (accessible to all users)
    • restricted mode (accessible to database administrators only)
Starting an Instance: Scenarios

NOMOUNT DATABASE:

You can start an instance without mounting a database.
STARTUP NOMOUNT;

MOUNT DATABASE:
You can start an instance and mount a database without opening it, which you can do when you want to perform specific maintenance operations. For example, the database must be mounted but not open during the following tasks:

  • renaming datafiles
  • adding, dropping, or renaming redo log files
  • enabling and disabling redo log archiving options
  • performing full database recovery

STARTUP NOMOUNT;

START and OPEN DATABASE:

Start an instance and then mount and open the database by using the STARTUP command by itself:

STARTUP;

Restricting Access to a Database at Startup

STARTUP RESTRICT;

ALTER SYSTEM
   ENABLE RESTRICTED SESSION;
ALTER SYSTEM
   DISABLE RESTRICTED SESSION;
Forcing an Instance to Start
  • You cannot shut down the current instance with the SHUTDOWN NORMAL, SHUTDOWN IMMEDIATE, or SHUTDOWN TRANSACTIONAL commands.
  • You experience problems when starting an instance.

If one of these situations arises, you can usually solve the problem by starting a new instance (and optionally mounting and opening the database) using the STARTUP command with the FORCE option (If an instance is running, STARTUP FORCE shuts it down with mode ABORT before restarting it).

 STARTUP FORCE;
STARTUP WITH RECOVERY

If you know that media recovery is required, you can start an instance, mount a database to the instance, and have the recovery process automatically start by using the STARTUP command with the RECOVER option:

STARTUP OPEN RECOVER;

Altering Database Availability

ALTER DATABASE MOUNT; (changing from nomount to mount)

ALTER DATABASE OPEN; (changing from nomount|mount to open)

ALTER DATABASE OPEN READ ONLY;ALTER DATABASE OPEN READ WRITE;

Shutting Down a Database

Shutting Down with the NORMAL Option
  • No new connections are allowed after the statement is issued.
  • Before the db is shut down, Oracle waits for all connected users to disconnect from the db.
  • The next startup of the database will not require any instance recovery procedures.SHUTDOWN NORMAL;
    Shutting Down with the IMMEDIATE Option
  • Any uncommitted transactions are rolled back. (If long uncommitted transactions exist, this method of shutdown might not complete quickly, despite its name.)
  • Oracle does not wait for users currently connected to the database to disconnect; Oracle implicitly rolls back active transactions and disconnects all connected users.SHUTDOWN IMMEDIATE;
    Shutting Down with the TRANSACTIONAL Option

    When you wish to perform a planned shutdown of an instance while allowing active transactions to complete first, use the SHUTDOWN command with the TRANSACTIONAL option:

    SHUTDOWN TRANSACTIONAL;

    Shutting Down with the ABORT Option
  • Current client SQL statements being processed by Oracle are immediately terminated.
  • Uncommitted transactions are not rolled back.
  • SHUTDOWN ABORT;