Overview of User-Managed Backup&Recovery

• Overview of User-Managed Backup

1. About Backup Mode

Some user-managed backup procedures require you to place the tablespace in backup mode to protect against the possibility of a fractured block.

However, updates to the database create more than the usual amount of redo in backup mode.In backup mode, the database copies whole changed data blocks into the redo stream.

After you take the tablespace out of backup mode with the ALTER TABLESPACE ... END BACKUP or ALTER DATABASE END BACKUP statement, the database advances the data file checkpoint SCN to the current database checkpoint SCN.

1. Ending a Backup After Instance Failure or SHUTDOWN ABORT

如果有数据文件未执行END BACKUP是无法正常关闭数据的

如果有数据文件未执行END BACKUP且实例非正常关闭，会导致数据文件进行实例恢复，即无法启动到OPEN状态. 这时要么在MOUNT下执行END BACKUP要么执行RECOVER

The following situations can cause a tablespace backup to fail and be incomplete:

1. The backup completed, but you did not run the ALTER TABLESPACE ... END BACKUP statement.
2. An instance failure or SHUTDOWN ABORT interrupted the backup.

使用END BACKUP还是RECOVER的区别在于数据文件是否CURRENT

The ALTER DATABASE END BACKUP statement is not the only way to respond to a failed online backup; you can also run the SQL*Plus RECOVER command. This method is useful when you are not sure whether someone has restored a backup, because if someone has indeed restored a backup, then the RECOVER command brings the backup up-to-date. Only run the ALTER DATABASE END BACKUP or ALTER TABLESPACE ... END BACKUP statement if you are sure that the files are current.

2.1 Ending Backup Mode with the ALTER DATABASE END BACKUP Statement

To take tablespaces out of backup mode simultaneously:

1. Query the V$BACKUP view to list the data files of the tablespaces that were being backed up before the database was restarted:

SQL>  SELECT * FROM V$BACKUP WHERE STATUS = 'ACTIVE';

FILE#      STATUS             CHANGE#    TIME       CON_ID

---------- ------------------ ---------- ---------  -------

        12 ACTIVE                  20863 25-NOV-02        0

        13 ACTIVE                  20863 25-NOV-02        0

        20 ACTIVE                  20863 25-NOV-02        0

3 rows selected.

1. Issue the ALTER DATABASE END BACKUP statement to take all data files currently in backup mode out of backup mode. For example, enter:

SQL> ALTER DATABASE END BACKUP;

You can use this statement only when the database is mounted but not open. If the database is open, then use ALTER TABLESPACE ... END BACKUP or ALTER DATABASE DATAFILE ... END BACKUP for each affected tablespace or data file.

Caution: Do not use ALTER DATABASE END BACKUP if you have restored any of the affected files from a backup.

2.2 Ending Backup Mode with the SQL*Plus RECOVER Command

To take tablespaces out of backup mode with the RECOVER command:

1. Mount the database. For example, enter:

SQL> STARTUP MOUNT

1. Recover the database as usual. For example, enter:

SQL> RECOVER DATABASE

1. Use the V$BACKUP view to confirm that there are no active data files:

SQL>  SELECT * FROM V$BACKUP WHERE STATUS = 'ACTIVE';

no rows selected.

• Overview of User-Managed Recovery

手动备份可能已经过时，但手动恢复在一些场景还是需要的，如恢复REDO LOG、重建控制文件等

To start any type of media recovery, you must adhere to the following restrictions:

1. You must have administrator privileges.
2. All recovery sessions must be compatible.
3. One session cannot start complete media recovery while another performs incomplete media recovery.
4. You cannot start media recovery if you are connected to the database through a shared server process.

1. RECOVERY WITH ARCHIVELOGS

1.1  Automatic Recovery with the RECOVER Command

Automatic recovery initiates recovery without manually prompting SQL*Plus to apply each individual archived redo log.

使用以下选项会使进行自动应用需要的默认归档日志

When using SQL*Plus, you have the following options for automating the application of the default file names of archived redo logs needed during recovery:

1. Issuing SET AUTORECOVERY ON before issuing the RECOVER command. If you perform recovery with SET AUTORECOVERY OFF, which is the default, then you must enter file names manually or accept the suggested file name by pressing Enter.
2. Specifying the AUTOMATIC keyword as an option of the RECOVER command.

In either case, no interaction is required when you issue the RECOVER command if the necessary files are in the correct locations with the correct names.

在出现归档日志断档或需要应用REDO LOG时会进行提示，这时应先根据建议的归档文件名字查看是否为归档断点，如果不是应用ONLINE REDO LOG

You are then prompted for the next redo log in the sequence. If the most recently applied log is the last required log, then recovery is terminated. When the database successfully applies a redo log file, the following message is returned:

Log applied.

默认应用的归档日志位置及名称:

The file names used for automatic recovery are derived from the concatenated values of LOG_ARCHIVE_FORMAT with LOG_ARCHIVE_DEST_n, where n is the highest value among all enabled, local destinations. For example, assume that the following initialization parameter settings are in effect in the database instance:

LOG_ARCHIVE_DEST_1 = "LOCATION=/arc_dest/loc1/"

LOG_ARCHIVE_DEST_2 = "LOCATION=/arc_dest/loc2/"

LOG_ARCHIVE_DEST_STATE_1 = DEFER

LOG_ARCHIVE_DEST_STATE_2 = ENABLE

LOG_ARCHIVE_FORMAT = arch_%t_%s_%r.arc

In this example, SQL*Plus automatically suggests the file name /arc_dest/loc2/arch_%t_%s_%r.arc (where %t is the thread, %s is the sequence and %r is the resetlogs ID).

Note: If you use an Oracle Real Application Clusters configuration, and if you are performing incomplete recovery or using a backup control file, then the database can only compute the name of the first archived redo log file from the first redo thread. You may have to manually apply the first log file from the other redo threads. After the first log file in a given thread has been supplied, the database can suggest the names of the subsequent logs in this thread.

1.1.1 Automatic Recovery with SET AUTORECOVERY

After restoring data file backups, you can run the SET AUTORECOVERY ON command to enable automatic recovery:

STARTUP MOUNT

SET AUTORECOVERY ON

RECOVER DATABASE

ALTER DATABASE OPEN;

1.1.2 Automatic Recovery with the AUTOMATIC Option of the RECOVER Command

Besides using SET AUTORECOVERY to turn on automatic recovery, you can also simply specify the AUTOMATIC keyword in the RECOVER command:

STARTUP MOUNT

RECOVER AUTOMATIC DATABASE

ALTER DATABASE OPEN;

1.2 Recovery When Archived Logs Are in the Default Location

不使用自动应用则需要手动指定归档日志，数据库会提示建议应用日志

During recovery, as a log is needed, the database suggests the file name. If you run nonautomatic media recovery with SQL*Plus, then the output is displayed in the format shown by this example:

ORA-00279: change 53577 generated at 11/26/02 19:20:58 needed for thread 1

ORA-00289: suggestion : /oracle/oradata/trgt/arch/arcr_1_802.arc

ORA-00280: change 53577 for thread 1 is in sequence #802

Specify log: [<RET> for suggested | AUTO | FROM logsource | CANCEL ]

If you start media recovery and must then interrupt it, then either enter CANCEL when prompted for a redo log file, or use your operating system's interrupt signal if you must terminate when recovering an individual data file, or when automated recovery is in progress. After recovery is canceled, you can resume it later with the RECOVER command. Recovery resumes where it left off when it was canceled.

Note: Similar messages are returned when you use an ALTER DATABASE ... RECOVER statement. However, no prompt is displayed.

The database constructs suggested archived log file names by concatenating the current values of the initialization parameters LOG_ARCHIVE_DEST_n (where n is the highest value among all enabled, local destinations) and LOG_ARCHIVE_FORMAT and using log history data from the control file. The following are possible settings:

LOG_ARCHIVE_DEST_1 = 'LOCATION = /oracle/oradata/trgt/arch/'

LOG_ARCHIVE_FORMAT = arcr_%t_%s.arc

SELECT NAME FROM V$ARCHIVED_LOG;

NAME

----------------------------------------

/oracle/oradata/trgt/arch/arcr_1_467.arc

/oracle/oradata/trgt/arch/arcr_1_468.arc

/oracle/oradata/trgt/arch/arcr_1_469.arc

1.3 Recovery When Archived Logs Are in a Nondefault Location

To perform media recovery when archived redo log files are stored in a nondefault location, you must specify the location of archived redo log files.

有两种解决办法重新设置LOG_ARCHIVE_DEST_n以及使用SET或RECOVER LOGFILE命令

You have the following mutually exclusive options when performing media recovery when archived logs are not in their default location:

1. Edit the LOG_ARCHIVE_DEST_n parameter that specifies the location of the archived redo logs, then recover as usual.
2. Use the SET statement in SQL*Plus to specify the nondefault log location before recovery, or the LOGFILE parameter of the RECOVER command.

1.3.1 Resetting the Archived Log Destination

You can edit the initialization parameter file or issue ALTER SYSTEM statements to change the default location of the archived redo logs.

To change the default archived log location before recovery:

1. Use an operating system utility to restore the archived logs to a nondefault location：

% cp /backup/arch/* /tmp/

1. Change the value for the archive log parameter to the nondefault location:

LOG_ARCHIVE_DEST_1 = 'LOCATION=/tmp/'

LOG_ARCHIVE_FORMAT = arcr_%t_%s.arc

1. Using SQL*Plus, start a new instance by specifying the edited initialization parameter file, and then mount the database. For example, enter:

STARTUP MOUNT

1. Begin media recovery as usual. For example, enter:

RECOVER DATABASE

1.3.2 Overriding the Archived Log Destination

In some cases, you may want to override the current setting for the archiving destination parameter as a source for archived log files.

To recover archived logs in a nondefault location with SET LOGSOURCE:

1. Using an operating system utility, copy the archived redo logs to an alternative location:

% cp $ORACLE_HOME/oradata/trgt/arch/* /tmp

1. Specify the alternative location within SQL*Plus for the recovery operation. Use the LOGSOURCE parameter of the SET statement. For example, start SQL*Plus and run:

SET LOGSOURCE "/tmp"

1. Recover the offline tablespace. For example, to recover the offline tablespace users do the following:

RECOVER AUTOMATIC TABLESPACE users

1. Alternatively, you can avoid running SET LOGSOURCE and simply run:

RECOVER AUTOMATIC TABLESPACE users FROM "/tmp"

Note: Overriding the redo log source does not affect the archive redo log destination for online redo log groups being archived.

1. Parallel Media Recovery

Recover时默认是并行应用前滚的，默认为CPU_COUNT的并行数

By default, Oracle Database uses parallel media recovery to improve performance of the roll forward phase of media recovery. In parallel recovery of media, the database uses a "division of labor" approach to allocate different processes to different data blocks while rolling forward, thereby making the procedure more efficient.

The number of processes used is derived from the CPU_COUNT initialization parameter, which by default equals the number of CPUs on the system. For example, if parallel recovery is performed on a system where CPU_COUNT is 4, and only one data file is recovered, then four spawned processes read blocks from the archive logs and apply redo.

Typically, media recovery is limited by data block reads and writes. Parallel recovery attempts to use all of the available I/O bandwidth of the system to improve performance. Unless there is a system I/O bottleneck or poor asynchronous I/O support, parallel recovery is likely to improve performance of recovery.

可以在RECOVER命令中使用NOPARALLEL或PARALLEL n来替代默认行为。另外注意RECOVERY_PARALLELISM参数只对instance or crash recovery有效

To override the default behavior of performing parallel recovery, use the SQL*Plus RECOVER command with the NOPARALLEL option, or RECOVER PARALLEL 0. The RECOVERY_PARALLELISM initialization parameter controls instance or crash recovery only. Media recovery is not affected by the value used for RECOVERY_PARALLELISM.