User-Managed Backups&Recovery of Tablespaces and Data Files

（一）Making User-Managed Backups of Tablespaces and Data Files

1. Making User-Managed Backups of Offline Tablespaces and Data Files

Note the following guidelines when backing up offline tablespaces:

1. You cannot take offline the SYSTEM tablespace or a tablespace with active undo segments. The following technique cannot be used for such tablespaces.
2. Assume that a table is in tablespace Primary and its index is in tablespace Index. Taking tablespace Index offline while leaving tablespace Primary online can cause errors when data manipulation language (DML) is issued against the indexed tables located in Primary. The problem appears only when the access method chosen by the optimizer must access the indexes in the Index tablespace.

To back up offline tablespaces:

1. Before beginning a backup of a tablespace, identify the tablespace's data files by querying the DBA_DATA_FILES view:

SELECT TABLESPACE_NAME, FILE_NAME

  FROM SYS.DBA_DATA_FILES

  WHERE TABLESPACE_NAME = 'USERS';

1. Take the tablespace offline using normal priority if possible, because it guarantees that you can subsequently bring the tablespace online without having to recover it. For example:

SQL> ALTER TABLESPACE users OFFLINE NORMAL;

1. Back up the offline data files. For example:

% cp /oracle/oradata/trgt/users01.dbf /d2/users01_'date "+%m_%d_%y"'.dbf

1. Bring the tablespace online. For example:

ALTER TABLESPACE users ONLINE;

Note: If you took the tablespace offline using temporary or immediate priority, then you cannot bring the tablespace online unless you perform tablespace recovery.

1. Archive the unarchived redo logs so that the redo required to recover the tablespace backup is archived. For example, enter:

ALTER SYSTEM ARCHIVE LOG CURRENT;

1. Making User-Managed Backups of Online Tablespaces and Data Files

2.1 Making User-Managed Backups of Online Read/Write Tablespaces

To back up online read/write tablespaces in an open database:

1. Before beginning a backup of a tablespace, use the DBA_DATA_FILES data dictionary view to identify all of the data files in the tablespace:

SELECT TABLESPACE_NAME, FILE_NAME

FROM   SYS.DBA_DATA_FILES

WHERE  TABLESPACE_NAME = 'USERS';

1. Mark the beginning of the online tablespace backup:

SQL> ALTER TABLESPACE users BEGIN BACKUP;

1. Back up the online data files of the online tablespace with operating system commands:

% cp /oracle/oradata/trgt/users01.dbf /d2/users01_'date "+%m_%d_%y"'.dbf

% cp /oracle/oradata/trgt/users02.dbf /d2/users02_'date "+%m_%d_%y"'.dbf

1. After backing up the data files of the online tablespace, run the SQL statement ALTER TABLESPACE with the END BACKUP option:

SQL> ALTER TABLESPACE users END BACKUP;

1. Archive the unarchived redo logs so that the redo required to recover the tablespace backup is archived:

SQL> ALTER SYSTEM ARCHIVE LOG CURRENT;

Caution: If you fail to take the tablespace out of backup mode, then Oracle Database continues to write copies of data blocks in this tablespace to the online redo logs, causing performance problems. Also, you receive an ORA-01149 error if you try to shut down the database with the tablespaces still in backup mode.

2.2 Making User-Managed Backups of Read-Only Tablespaces

When backing up an online read-only tablespace, you can simply back up the online data files. You do not have to place the tablespace in backup mode because the database is not permitting changes to the data files.

To back up online read-only tablespaces in an open database:

1. Query the DBA_TABLESPACES view to determine which tablespaces are read-only:

SELECT TABLESPACE_NAME, STATUS

FROM DBA_TABLESPACES

WHERE STATUS = 'READ ONLY';

1. Before beginning a backup of a read-only tablespace, identify all of the tablespace's data files by querying the DBA_DATA_FILES data dictionary view:

SELECT TABLESPACE_NAME, FILE_NAME

FROM SYS.DBA_DATA_FILES

WHERE TABLESPACE_NAME = 'HISTORY';

 

TABLESPACE_NAME                   FILE_NAME

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

HISTORY                           /oracle/oradata/trgt/history01.dbf

HISTORY                           /oracle/oradata/trgt/history02.dbf

1. Back up the online data files of the read-only tablespace with operating system commands.

% cp $ORACLE_HOME/oradata/trgt/history*.dbf  /disk2/backup/

表空间READ ONLY备份后又设置为READ WRITE，在恢复时需要RECOVER

When restoring a backup of a read-only tablespace, take the tablespace offline, restore the data files, then bring the tablespace online. A backup of a read-only tablespace is still usable if the read-only tablespace is made read/write after the backup, but the restored backup requires recovery.

1. Making User-Managed Backups of Tablespaces in CDBs

方法同non-cdb的tablespaces and data files备份方式，过程略，只是有两种连接方式：

Perform one of the following operations:

To back up tablespaces in the root, connect to the root as a common user with the SYSDBA or SYSBACKUP system privilege.

To back up tablespaces in a PDB, connect to the PDB as a common user or local user with SYSDBA or SYSBACKUP system privilege.

3.1 User-Managed Backups of Offline Tablespaces and Data Files in CDBs

3.2 User-Managed Backups of Online Tablespaces in CDBs and PDBs

（二）Making User-Managed Recovery of Tablespaces and Data Files

You can perform complete recovery of non-SYSTEM data files in a database while the database is open. 也可以在mount下对表空间或数据文件恢复，过程同数据库手动恢复

This procedure assumes the following:

1. The current control file is available.
2. You have backups of all needed data files.
3. All necessary archived redo logs are available.

在写入损坏的数据文件时自动将此数据文件OFFLINE，但不会将对应表空间OFFLINE；读取损坏的数据文件操作并不会将数据文件OFFLINE，只是报错

Damaged data files—but not the tablespaces that contain them—are automatically taken offline if the database writer cannot write to them. If the database writer cannot open a data file, an error is still returned. Queries that cannot read damaged files return errors, but the data files are not taken offline because of the failed queries. For example, you may run a SQL query and see output such as:

ERROR at line 1:

ORA-01116: error in opening database file 3

ORA-01110: data file 11: '/oracle/oradata/trgt/cwmlite02.dbf'

ORA-27041: unable to open file

SVR4 Error: 2: No such file or directory

Additional information: 3

Note: You cannot use the procedure in this section to perform complete media recovery on the SYSTEM tablespace while the database is open. If the media failure damages data files of the SYSTEM tablespace, then the database automatically shuts down.

To restore data files in an open database:

1. Determine which data file need recovery using V$RECOVER_FILE

SELECT FILE#, ERROR, ONLINE_STATUS, CHANGE#, TIME

FROM V$RECOVER_FILE;

1. Determine which archivelogs are needed using V$RECOVERY_LOG and V$ARCHIVED_LOG

V$ARCHIVED_LOG lists file names for all archived redo logs, whereas V$RECOVERY_LOG lists only the archived redo logs that the database needs to perform media recovery. V$RECOVERY_LOG view also includes the probable names of the files based on the naming convention specified by using the LOG_ARCHIVE_FORMAT parameter.

如果需要使用归档日志备份，则在恢复前需要先还原归档日志

1. If the database is open, then take all tablespaces containing damaged data files offline:

ALTER TABLESPACE users OFFLINE TEMPORARY;

ALTER TABLESPACE tools OFFLINE TEMPORARY;

1. Inspect the media to determine the source of the problem.

You can use the DBVERIFY utility to run an integrity check on offline data files.

If the hardware problem that caused the media failure was temporary, and if the data was undamaged, then no media recovery is required. You can bring the offline tablespaces online and resume normal operations. If you cannot repair the problem, or if DBVERIFY reports corrupt blocks, then proceed to the Step 5.

1. If files are permanently damaged, then use operating system commands to restore the most recent backup files of only the data files damaged by the media failure:

% cp /disk2/backup/users01.dbf $ORACLE_HOME/oradata/trgt/users01.dbf

If the hardware problem is fixed and the data files can be restored to their original locations, then do so. Otherwise, restore the data files to an alternative storage device.

1. If you restored one or more damaged data files to alternative locations, then update the control file of the database to reflect the new data file names:

ALTER DATABASE RENAME FILE '?/oradata/trgt/users01.dbf' TO

                           '/disk2/users01.dbf';

1. If you restored archived redo logs to an alternative location, then you can specify the location before media recovery with the LOGSOURCE parameter of the SET command in SQL*Plus:

SET LOGSOURCE /tmp

Alternatively, you can skip Step 6 and use the FROM parameter on the RECOVER command as in Step 8:

RECOVER AUTOMATIC FROM '/tmp' TABLESPACE users, tools;

1. Connect to the database with administrator privileges, and start offline tablespace recovery of all damaged data files in one or more offline tablespaces using one step:

RECOVER AUTOMATIC TABLESPACE users, tools;

Unless the application of files is automated with the RECOVER AUTOMATIC or SET AUTORECOVERY ON commands, the database prompts for each required redo log file.

The online redo logs are then automatically applied to the restored data files to complete media recovery.

1. When the damaged tablespaces are recovered up to the moment that media failure occurred, bring the offline tablespaces online:

ALTER TABLESPACE users ONLINE;

ALTER TABLESPACE tools ONLINE;