8 Performing Database Backup and Recovery with VSS

This chapter explains how to use Volume Shadow Copy Service (VSS) applications to back up and recover an Oracle database. This chapter contains the following topics:

Overview of Database Backup and Recovery with VSS

This section explains the basic concepts and tasks involved in backup and recovery with component-based shadow copies. The following topics are described:

Purpose of Database Backup and Recovery with VSS

VSS provides a Windows-specific interface that enables coordination between requesters that back up data, writers that update data on disk, and providers that manage storage. Oracle Database functions as a writer that is integrated with VSS-enabled applications.

You can use VSS-enabled software and storage systems on Windows to back up and restore an Oracle database. A key benefit is the ability to use a VSS-enabled application to make an online backup of the whole database.

Scope of This Chapter

This chapter assumes that you are familiar both with VSS applications and the Oracle Database backup and recovery principles and techniques described in Oracle Database Backup and Recovery User's Guide. This chapter does not attempt to provide an introduction to backup and recovery: it only explains how to perform database backup and recovery in the VSS infrastructure.

Basic Concepts of Database Backup and Recovery with VSS

VSS is an infrastructure on Windows server platforms that enables applications to create shadow copies. A shadow copy is a consistent snapshot of the data held on a volume or component at a well-defined point in time. A shadow copy set is a collection of shadow copies that are all taken at the same time. VSS identifies each shadow copy and shadow copy set by a persistent Global Unique Identifier (GUID).

VSS provides the following infrastructure for VSS applications:

  • Coordinates activities of requesters, providers, and writers in the creation and use of shadow copies

  • Furnishes the default system provider

  • Implements low-level driver functionality necessary for any provider to work

A VSS requester is an application that requests VSS services to create shadow copies. Typically, VSS requesters are backup applications. Requesters communicate with writers to gather system data and signal writers to prepare data for backup.

A VSS provider manages storage volumes and creates shadow copies on demand. In response to a requester, a provider generates COM events to signal applications of an impending shadow copy and creates and maintains this copy until it is no longer needed. During the life cycle of the shadow copy, the provider effectively supports two independent copies: the disk that is actively updated and a fixed copy that is stable for backup.

A VSS writer is an application or service that writes data to disk and cooperates with VSS providers and requesters. During backups, writers ensure that data is in the proper state for a shadow copy.

The Oracle VSS writer is a Windows service that coordinates an Oracle database instance and other VSS components. The writer service, which is started under the user account with SYSDBA privileges, runs separately from the database instance. You must use third-party requesters to perform backup and recovery within the VSS infrastructure.

As explained in the following sections, the Oracle VSS writer supports both volume-based and component-based shadow copies. You can use these shadow copies in a backup and recovery strategy or to create a copy of your original database. You can use the duplicate database for testing or as a standby database.

Component-Based Shadow Copies

The Oracle VSS writer supports component-based shadow copies, which are sets of database files. The recommended technique for backing up an Oracle database with VSS writer is to create shadow copies of components. During a backup, the Oracle VSS writer saves the redo generated during snapshot creation in a metadata document. During a restore operation, the writer automatically extracts the redo from the metadata document and applies it to files restored from a snapshot.

Volume-Based Shadow Copies

The Oracle VSS writer supports volume-based shadow copies, which are snapshots of complete drive or volumes. Oracle Database places the files that it manages in a state suitable to create shadow copies. For example, the datafiles are placed in hot backup mode and a new snapshot control file is created for a database in ARCHIVELOG mode. Oracle VSS writer excludes files such as the current control file and online redo logs from the shadow copies. The writer also returns an error if the snapshot cannot be taken. For example, if a NOARCHIVELOG database is open in read/write mode, then the writer returns an error indicating that the snapshot is not possible.

Note:

Automatic Storage Management files and raw files are not supported for Oracle VSS snapshots.

Oracle VSS Backup Types

Oracle VSS writer supports log, copy, full, differential, and incremental backups. The VSS writer uses time stamp mechanism for incremental and differential backups and stores a time stamp in the backup document using SetBackupStamp() API. This backup stamp is used by Oracle VSS writer during incremental or differential backups to specify changed files since the last full or incremental backup using AddDifferencedFilesByLastModifyTime() API.

Oracle VSS writer also stores backup metadata and restore metadata, which must be available during restore operations so that the VSS writer can perform intelligent postrestore operations. In case of full or copy backup, the restore metadata contains important redo information to make the restored files consistent. Hence, it is imperative that Oracle VSS writer is called during restore operations to perform the recovery operations.

Basic Steps of Backup and Recovery with VSS

The Oracle VSS writer is installed automatically as part of the database. If you must install or uninstall it manually, see "Installing and Uninstalling the Oracle VSS Writer Service".

In the most typical backup scenario, you select the Oracle Database component in your VSS-enabled application and create a shadow copy. The shadow copy contains the database files, control files, and server parameter file. If the database is in ARCHIVELOG mode, then you can create the shadow copy when the database is open or closed; otherwise, only when closed.

In a typical recovery scenario, you select the Oracle Database component in your VSS-enabled application and restore it. Afterward, you can open the database either in read-only mode or with the RESETLOGS option. The Oracle VSS writer also supports applications that perform point-in-time recovery.

To restore a subset of database files, you can select individual components and restore them. The Oracle VSS writer performs the appropriate actions automatically in the postrestore phase so that the file can be used (or brought online) at the end of restore operation. For example, if you select a datafile component for restore, then the writer automatically recovers the datafile by using RMAN.

See Also:

Installing and Uninstalling the Oracle VSS Writer Service

The Oracle VSS writer runs separately from the Oracle database instance. From the perspective of the database, the VSS writer is simply an OCI client.

The Oracle VSS writer provides command-line options to install and uninstall the writer service. During installation, you can specify the Windows account under which the service must be started. The writer uses operating system authentication when connecting to a database instance. Thus, the Windows user must be able to log in as SYSDBA to the Oracle database instances managed by the writer service.

The command-line syntaxes for the Oracle VSS writer are as follows:

oravssw {/q [/start | /stop | /status]}
oravssw SID [/tl trace_level] [/tf trace_file]
oravssw SID [/i] [/d]

Note:

You can change the userid and password using the Services snapin.

Table 8-1 describes the options for the Oracle VSS writer.

Table 8-1 Oracle VSS Writer Options

Option Description

SID

SID of the Oracle instance to which the service connects.

/i

Installs the service for a specified SID.

/q

Queries the Oracle VSS writer services. But when not used with options like /start or /status or /stop, it just displays the list of Oracle VSS writer services.

/status

Displays the current status of all Oracle writer services and can be used only with the /q option.

/start

Starts all Oracle VSS writer services and can be used only with the /q option.

/stop

Stops all Oracle VSS writer services and can be used only with the /q option.

/tl

Specifies the trace level for a Oracle VSS writer for a specified SID.

/tf

Specifies the trace file name for Oracle VSS writer for a specified SID.

/d

Uninstalls the service for a specified SID.


In Example 8-1, you install the service so that it connects to the prod1 instance.

Example 8-1 Installing Oracle VSS Writer

oravssw prod1 /i 

Note:

  • Any errors during operation of the Oracle VSS writer are reported by means of Windows System Event logging APIs. You can view these errors with the Windows Event Viewer.

  • Oracle Database 10g Release 2 (10.2.0.3) supports Oracle VSS snapshots only when Oracle VSS writer 11g is configured to manage the 10.2.0.3 database. See My Oracle Support (formerly OracleMetaLink) https://support.oracle.com Note 580558.1 for more information about installing Oracle VSS writer for use with 9i and 10g databases.

Backing Up a Database

The technique for backing up a database depends on the archiving mode of the database and whether you are making a component-based or volume-based backup. Oracle recommends shadow copies taken in component mode for backing up the Oracle database using VSS writer. The Oracle VSS writer defines the components that include the set of database files. The Oracle VSS writer then saves the redo generated during hot backup mode when the snapshot was created in the backup writer metadata document.

The component hierarchy defined by the Oracle VSS writer is illustrated in Figure 8-1.

Figure 8-1 Oracle VSS Writer Component Hierarchy

Description of Figure 8-1 follows
Description of "Figure 8-1 Oracle VSS Writer Component Hierarchy"

"About Component-Based Backups" explains which components you can select when making component-based backups.

About Component-Based Backups

The components supported by the Oracle VSS writer are listed in Table 8-2. The name of the component is the value returned by an OnIdentify VSS message. The Selectable for Backup column indicates whether a component is eligible to be selected in VSS shadow copies.

Table 8-2 Components Backed Up by the Oracle VSS Writer

Component Description Selectable for Backup

Oracle Database

Contains the database files, control files, and server parameter file.

Yes

Control File

Contains the snapshot location of the control file for a database in ARCHIVELOG mode, or the current control file locations when all database files are recovered to a consistent SCN.

Note: The files included in the Control File component determine whether current control files or snapshot control files are excluded. For example, if the database is opened in read-only mode, then the snapshot control file location is excluded from the snapshot.

No

Server Parameter File

Contains the location of the server parameter file, if the instance is using one.

No

All TableSpaces

Includes all tablespaces in a snapshot.

No

tablespace_names

Selects individual TableSpaces.

No

Datafile number=n

Selects individual datafiles. The metadata will contain RESETLOGS information, tablespace number, tablespace name, and DBID.

No

ArchiveLogDest_n

Selects individual local archiving destinations other than the fast recovery area.

Yes

Fast Recovery Area

Includes all backup files and archived logs in the Fast Recovery Area in the VSS snapshot.

Files backed up by VSS from the Fast Recovery Area can be subjected to deletion under space constraints.

Yes

Archived Logs

Logs in Fast Recovery Area

No

Backup Files

Backs up from Fast Recovery Area

No


You can select only Oracle Database, ArchiveLogDest_n, and Fast Recovery Area, exposed by the Oracle VSS writer during a backup. The availability of the components in Table 8-2 may depend on the database state. For example, if the database is in NOARCHIVELOG mode, the Archived Logs component will not be returned. Likewise, if the instance is not started with a server parameter file, the Server Parameter File component will not be returned.

Note:

The components that are not marked as Selectable for Backup are implicitly included by components marked as Selectable for Backup.

When you select Oracle Database component for backup or restore, all other components are implicitly selected for backup or restore. This implies that all files that are part of the selected component are candidates for backup or restore.

ArchiveLogDest_n and Fast Recovery Area components are defined to contain only log or incremental files. This means that the requester should backup files from these components only when creating a log for incremental or differential backup. Likewise, the requester should restore files from these components only when restoring from log or incremental or differential backups.

The files in all other components other than ArchiveLogDest_n and Fast Recovery Area define database files. This means that the requester should backup files from these components only when creating a full or copy backup. Likewise, the requester should restore files from these components only when restoring from full or copy backup.

Backing Up a Database in ARCHIVELOG Mode

The procedures in this section assume that the database is open read/write. You can also make closed, consistent backups by following the procedures in "Backing Up a Database in NOARCHIVELOG Mode".

Making Component-Based Backups of an ARCHIVELOG Database

This section explains how to back up the whole database. You can back up only Oracle Database, ArchiveLogDest_n, and Fast Recovery Area, listed in Table 8-2, "Components Backed Up by the Oracle VSS Writer".

To back up the whole database:

  1. Start a SQL*Plus session on the target database and make sure the database is open READ WRITE.

  2. Use a third-party VSS requester to select the Oracle Database component.

  3. Create a snapshot of the database.

    Oracle VSS writer includes the server parameter file, control file, and datafiles in the snapshot. The online redo logs will not be included in the snapshot.

Making Volume-Based Backups of an ARCHIVELOG Database

To make volume-based shadow copies of Oracle database when the database is open in read/write mode, the archived redo logs must be physically located on a separate volume from the volume containing the oracle datafiles, control files, server parameter file, and online redo logs.

To back up the database and archived redo logs by volume:

  1. Start a SQL*Plus session on the target database and make sure the database is open READ WRITE.

  2. Use a third-party VSS requester to select the volumes where the datafiles, control files, and server parameter file are physically located.

  3. Create a snapshot of the database files.

    Oracle VSS writer includes the server parameter file, control file, and datafiles in the snapshot. The online redo logs will not be included in the snapshot. Note that you can restore the server parameter file individually, but the control files and datafiles must be always restored together.

  4. Use a third-party VSS requester to select the volumes where all of the archived redo logs (or the fast recovery area) are physically located.

  5. Create a snapshot of the archived redo logs.

Backing Up a Database in NOARCHIVELOG Mode

For an Oracle database in NOARCHIVELOG mode, the database must be in a consistent state when you create a VSS snapshot. Backups made while the database is open read/write are not supported.

Making Component-Based Backups of a NOARCHIVELOG Database

For an Oracle database in NOARCHIVELOG mode, the only supported component-based VSS snapshot is of Oracle Database when the type is full, default, or copy.

To back up the database by component:

  1. Start a SQL*Plus session on the target database and place the database in a consistent state. For example, enter the following commands:

    SHUTDOWN
    STARTUP MOUNT
    
  2. Use a third-party VSS requester to select the Oracle Database component.

  3. Create a volume-based VSS snapshot.

    Oracle VSS writer includes the server parameter file, control file, and datafiles in the snapshot. The online redo logs will not be included in the snapshot.

Making Volume-Based Backups of a NOARCHIVELOG Database

To back up the database by volume:

  1. Start a SQL*Plus session on the target database and place the database in a consistent state. For example, enter the following commands:

    SHUTDOWN
    STARTUP MOUNT
    
  2. Use a third-party VSS requester to select the volumes where the datafiles, control files, and server parameter file are physically located.

  3. Create a volume-based VSS snapshot.

    Oracle VSS writer includes the server parameter file, control file, and datafiles in the snapshot. The online redo logs will not be included in the snapshot. Note that you can restore the server parameter file individually, but the control files and datafiles must be always restored together.

Restoring and Recovering a Database

This section explains how to restore and recover VSS snapshots. As in the case of backups, the procedure depends on the archiving mode of the database and the type of snapshot that you are restoring. The following topics are described:

Restoring and Recovering a Database in ARCHIVELOG Mode

You can select the components listed in Table 8-3, "Components Usable in a Restore Operation" in a restore and recovery operation. The table describes the validations that Oracle VSS writer performs for the components during the pre-restore phase, and the actions that it performs after the restore completes.

Table 8-3 Components Usable in a Restore Operation

Component Pre-Restore Phase PostRestore Phase Section

Server Parameter File

Verifies that the database instance is not started. Otherwise, the writer returns a pre-restore failure.

Ensures that the database is started NOMOUNT. If the server parameter file is restored to the default location for the Oracle home, then the instance starts NOMOUNT automatically. Otherwise, you must set ORA_SID_PFILE to the location of the text-based initialization parameter file that points to the location of the server parameter file.

"Restoring the Server Parameter File"

Control File

Verifies that the instance is either started NOMOUNT or not started. If the instance is not started, the writer either starts the instance with the ORA_SID_PFILE instance parameter file, or uses the initialization parameter file or server parameter file in the default location.

Mounts control file after replicating control file to all the current control file locations pointed to by the instance.

"Recovering from the Loss of All Control Files"

Tablespace or datafile component

Verifies that the database must be mounted or the specified datafiles or tablespaces must be offline.

Performs complete recovery of these tablespaces or datafiles. The requester application can override the default recovery behavior.

"Recovering Tablespaces or Datafiles"

All Tablespaces

Verifies that the database is mounted.

Extracts redo from the backup writer metadata document and performs incomplete recovery on all the restored datafiles up to the time of snapshot creation. The requester application can override the default recovery behavior.

"Recovering All Tablespaces"

Oracle Database

Verifies that the instance is not started.

Starts the database instance, mounts the control file, and performs recovery. See the descriptions of postrestore behavior for Server Parameter File, Control File, and All Tablespaces.

"Performing Disaster Recovery" and "Restoring Component-Based Backups of a NOARCHIVELOG Database"

Archived redo log or fast recovery area

None.

Does not perform default recovery of this component. Nevertheless, the requester application can run required RMAN commands.

 

Restoring the Server Parameter File

This section explains how to restore the server parameter file.

To restore the server parameter file:

  1. Select the component named Server Parameter File from a VSS snapshot.

  2. Restore the server parameter file.

    Oracle VSS writer restores the server parameter file to the original location from where it was copied. You can also restore it to a new location.

Recovering from the Loss of All Control Files

This section explains how to recover from the loss of all multiplexed control files.

To recover from the loss of all control files:

  1. Ensure that the database is in NOMOUNT state or can be started in NOMOUNT state by the Oracle VSS writer.

  2. Select the component named Control File from a VSS snapshot.

  3. Restore the component containing the lost control file.

    The Oracle VSS writer automatically mounts the database with the restored control files. If only the control file needs to be recovered, then the VSS requester application can ask the Oracle writer to perform complete recovery.

  4. Restore and recover other database components if necessary.

  5. Open the database with the RESETLOGS option.

Recovering Tablespaces or Datafiles

This section explains how to recover from the loss of one or more tablespaces or datafiles. This procedure assumes that not all datafiles are lost.

To recover from the loss of all tablespaces or datafiles:

  1. Ensure that the database is either mounted or open. If the database is open, then take the datafiles or tablespaces needing recovery offline with the ALTER DATABASE ... OFFLINE statement.

  2. If the archived redo logs are required for recovery of the datafiles or tablespaces, then restore the archived redo logs.

  3. Select the components from the VSS snapshot that contains the lost datafiles, or all datafiles in the lost tablespaces.

  4. Restore the component containing the lost datafiles.

    The Oracle VSS writer automatically recovers the restored datafiles. If some archived logs are missing, then you can restore the logs and recover the datafiles with SQL*Plus or RMAN.

  5. Bring the offline datafiles or tablespaces back online.

Recovering All Tablespaces

This section explains how to recover from the loss of all tablespaces.

To recover all datafiles:

  1. Ensure that the database is mounted.

  2. If the archived redo logs are required for recovery of the datafiles or tablespaces, then restore the archived redo logs.

  3. Select the component named All Tablespaces from a VSS snapshot.

  4. Restore the tablespaces.

    The Oracle VSS writer automatically recovers the restored datafiles. If some archived logs are missing, then you can restore the logs and recover the datafiles with SQL*Plus or RMAN.

  5. Open the database.

Performing Disaster Recovery

This section explains how to recover from the loss of the server parameter file, control file, and all datafiles.

To perform disaster recovery:

  1. Ensure that the instance is not started.

  2. If the archived redo logs are required for recovery of the datafiles or tablespaces, then restore the archived redo logs.

  3. Select the component named Oracle Database from a VSS snapshot.

  4. Restore the database.

    The Oracle VSS writer automatically starts the instance, mount the database, and recovers the restored datafiles. If some archived logs are missing, then you can restore the logs and recover the datafiles with SQL*Plus or RMAN.

  5. Open the database with the RESETLOGS option.

Restoring a Database in NOARCHIVELOG Mode

For an Oracle database in NOARCHIVELOG mode, no archived redo logs have been generated so no media recovery is possible. The database files must be in consistent state and require no additional recovery.

Restoring Component-Based Backups of a NOARCHIVELOG Database

To restore a component-based backup:

  1. Use a third-party VSS requester to select the Oracle Database component.

    The Oracle VSS writer automatically restores the datafiles and mounts the database.

  2. Open the database with the RESETLOGS option.

Restoring Volume-Based Backups of a NOARCHIVELOG Database

To restore a volume-based backup:

  1. Use a third-party VSS requester to select the volumes where the datafiles, control files, and server parameter file are physically located.

  2. Restore all volumes where datafiles and logs are located.

  3. Open the database with the RESETLOGS option.

Integrating VSS with Third-Party Requester Applications

Oracle VSS writer allows third-party requester applications to control the behavior of recovery and backup sessions. Third-party requester applications use VSS API setBackupOptions or setRestoreOptions to pass an appropriate string to the writer. The writer uses getBackupOptions or getRestoreOptions to get the string set from the requester to perform the pre or post backup and restore actions.

The following are the protocols to follow to exploit the flexibility of recovery operations provided by the writer:

Writer Control Commands

The writer control commands are applicable to all the restored components during the postrestore phase. The format is as follows:

OP1=CMD1, OP2=CMD2, . . . 

Run the commands in the following sequence:

  1. POST_WTRCMD=NORECOVER

    This command instructs the writer to not perform any postrestore recovery activities defined in the default postrestore recovery operations for the restored component. Otherwise, the postrestore phase default actions are performed.

  2. POST_RMANCMD=cmdstr

    This command instructs the writer to run specific RMAN commands, instead of the default operations, after the current operation.

  3. PRE_SQLCMD=cmdstr

    This command instructs the writer to run specific SQL commands in OnPrepareBackup or OnPreRestore callback, before performing any other validations. The command is used to stop MRP on a standby database before VSS snapshot is created or to shut down database instance creating a cold backup of the database.

  4. POST_SQLCMD=cmdstr

    This command instructs the writer to run specific SQL commands in PostSnapshot or PostRestore callback. This command is used to restart MRP on standby database after VSS snapshot is created or to restart the database instance after the cold backup of the database is performed.

Control Commands for Database or All Tablespaces Component

Run the following command:

POST_WTRCMD=UNTIL_SNAPSHOT

This command instructs the writer to perform recovery to the snapshot creation time. The writer extracts the system change number of the redo logs stored in the database component and performs recovery until the system change number.

Duplicating a Database

If your VSS shadow copies are transportable, then you can use these shadow copies to duplicate the primary database. In this context of this chapter, duplication refers to the creation of a new database out of the shadow copies for a different database. A duplicate database created from shadow copies can either be a nonstandby database or a standby database for use in a Data Guard environment. Note that RMAN duplication, which makes use of the DUPLICATE command, is a different procedure.

This section covers the following topics:

Creating a Nonstandby Database from Shadow Copies

This section assumes that you are duplicating the database on a host with the same file system structure as the primary database.

To create a nonstandby database from shadow copies:

  1. Restore the database on the new host by following the procedure in "Performing Disaster Recovery".

  2. Start a SQL*Plus session on the duplicate database and obtain the DBID. You can query the DBID as follows:

    SELECT DBID FROM V$DATABASE;
    
  3. Shut down the database consistently. You can shut down the database as follows:

    SHUTDOWN;
    
  4. Use the DBNEWID utility to change the DBID.

    Oracle Database Utilities explains how to use DBNEWID.

  5. Open the database.

  6. Start a SQL*Plus session on the duplicate database and query the DBID. You can query the DBID as follows:

    SELECT DBID FROM V$DATABASE;
    

Creating a Standby Database From Shadow Copies

This section assumes that you are create a standby database on a host with the same file system structure as the primary database. This section also assumes that you have read Oracle Data Guard Concepts and Administration and are familiar with standby database creation and maintenance.

To create a standby database from shadow copies:

  1. Restore the database on the standby host by following the procedure in "Performing Disaster Recovery".

  2. Start a SQL*Plus session on the new database and a new standby control file must be obtained from primary database. You can create the control file with the SQL statement ALTER DATABASE CREATE STANDBY CONTROLFILE.

  3. Start the instance and mount the standby control file.