How To Backup All Tablespaces In Parallel With a Single Command

It is no longer necessary to issue a separate command to place each tablespace in hot backup mode.

You can now use the "ALTER DATABASE" statement to place all tablespaces in backup mode.

The database must be in mounted or open mode when you issue this command.

Also, the BEGIN BACKUP command now runs faster than before.

Earlier DBAs have used the prepare all online tablespaces for backup by issuing
all necessary ALTER TABLESPACE statements at once.

For example, put tablespaces users, tools, and indx in backup mode as follows:
SQL> ALTER TABLESPACE users BEGIN BACKUP;
SQL> ALTER TABLESPACE tools BEGIN BACKUP;
SQL> ALTER TABLESPACE indx BEGIN BACKUP;


Now you can Use ALTER DATABASE BEGIN BACKUP to put all tablespaces in backup mode simultaneously,


Hence If you are backing up all tablespaces, you might want to use this command:

SQL> ALTER DATABASE BEGIN BACKUP;


Example 1:
--------------------
Issue BEGIN BACKUP command when data files belonging to a tablespace are in READ-ONLY mode:

SQL> alter tablespace users read only;

Tablespace altered.

SQL> alter tablespace users begin backup;
alter tablespace users begin backup
*
ERROR at line 1:
ORA-01642: begin backup not needed for read only tablespace 'USERS'


SQL> alter database begin backup;

Database altered.

Example 2:
------------------------
Issue BEGIN BACKUP command when data files belonging to a tablespace are in READ-ONLY mode:

SQL> alter tablespace tools offline;

Tablespace altered.

SQL> alter tablespace tools begin backup;
alter tablespace tools begin backup
*
ERROR at line 1:
ORA-01128: cannot start online backup - file 5 is offline
ORA-01110: data file 5: 'C:\ORACLE\ORADATA\SAMPLE\tools .DBF'

SQL> alter database begin backup;

Database altered.

The two examples above demonstrate that when you issue the ALTER DATABASE BEGIN BACKUP command, any read-only and offline data files are simply skipped, and processing continues.



SQL> ALTER DATABASE END BACKUP;


ALTER DATABASE END BACKUP command when you have multiple tablespaces still in backup mode. You can issue the statement to take all data files currently in backup mode out of backup mode. However, you can use this statement only when the database is mounted, and not open in Oracle9i.

If the database is open, you can only use ALTER TABLESPACE ... END BACKUP or ALTER DATABASE DATAFILE ... END BACKUP for each affected tablespace or datafile.

In Oracle 10g, you can issue the ALTER DATABASE END BACKUP command when the database is open.

If you issue the command while one of the data files is offline or in read-only mode, a warning message will return:

SQL> alter database end backup;
alter database end backup
*
ERROR at line 1:
ORA-01260: warning: END BACKUP succeeded but some files found not to be in backup mode

Note: However, that by putting all tablespaces in online mode at once, you can generate
large redo logs if there is heavy update activitiy on the affected tablespaces,
because the redo must contain a copy of each changed data block in each changed
datafile.

Be sure to consider the size of the likely redo before using the procedure outlined here..

References

Oracle 10G Documentation / Metalink -Note:270531.1

Errors

ORA-1260 { Message text depends on version }
ORA-1642 begin backup not needed for read-only tablespace
ORA-1110 datafile :
ORA-1128 cannot start online backup - file is offline

---