==========================================
Adding a Datafile or Creating a Tablespace
==========================================
The initialization parameter, STANDBY_FILE_MANAGEMENT, enables you to control whether or not adding a datafile to the primary database is automatically propagated to the standby database, as follows:
* If you set the STANDBY_FILE_MANAGEMENT initialization parameter in the standby database server parameter file (SPFILE) to AUTO, any new datafiles created on the primary database are automatically created on the standby database as well.
* If you do not specify the STANDBY_FILE_MANAGEMENT initialization parameter or if you set it to MANUAL, then you must manually copy the new datafile to the standby database when you add a datafile to the primary database.
Note that if you copy an existing datafile from another database to the primary database, then you must also copy the new datafile to the standby database and re-create the standby control file, regardless of the setting of STANDBY_FILE_MANAGEMENT initialization parameter.
The following sections provide examples of adding a datafile to the primary and standby databases when the STANDBY_FILE_MANAGEMENT initialization parameter is set to AUTO and MANUAL, respectively.
====================================================================================
Adding a Tablespace and a Datafile When STANDBY_FILE_MANAGEMENT Is Set to AUTO
====================================================================================
The following example shows the steps required to add a new datafile to the primary and standby databases when the STANDBY_FILE_MANAGEMENT initialization parameter is set to AUTO.
1. Add a new tablespace to the primary database:
SQL> CREATE TABLESPACE new_ts DATAFILE ‘/disk1/oracle/oradata/payroll/t_db2.dbf’
2> SIZE 1m AUTOEXTEND ON MAXSIZE UNLIMITED;
2. Archive the current online redo log file so the redo data will be transmitted to and applied on the standby database:
SQL> ALTER SYSTEM ARCHIVE LOG CURRENT;
3. Verify the new datafile was added to the primary database:
SQL> SELECT NAME FROM V$DATAFILE;
NAME
———————————————————————-
/disk1/oracle/oradata/payroll/t_db1.dbf
/disk1/oracle/oradata/payroll/t_db2.dbf
4. Verify the new datafile was added to the standby database:
SQL> SELECT NAME FROM V$DATAFILE;
NAME
———————————————————————-
/disk1/oracle/oradata/payroll/s2t_db1.dbf
/disk1/oracle/oradata/payroll/s2t_db2.dbf
====================================================================================
Adding a Tablespace and a Datafile When STANDBY_FILE_MANAGEMENT Is Set to MANUAL
====================================================================================
The following example shows the steps required to add a new datafile to the primary and standby database when the STANDBY_FILE_MANAGEMENT initialization parameter is set to MANUAL. You must set the STANDBY_FILE_MANAGEMENT initialization parameter to MANUAL when the standby datafiles reside on raw devices.
1. Add a new tablespace to the primary database:
SQL> CREATE TABLESPACE new_ts DATAFILE ‘/disk1/oracle/oradata/payroll/t_db2.dbf’
2> SIZE 1m AUTOEXTEND ON MAXSIZE UNLIMITED;
2. Verify the new datafile was added to the primary database:
SQL> SELECT NAME FROM V$DATAFILE;
NAME
———————————————————————-
/disk1/oracle/oradata/payroll/t_db1.dbf
/disk1/oracle/oradata/payroll/t_db2.dbf
3. Perform the following steps to copy the tablespace to a remote standby location:
1. Place the new tablespace offline:
SQL> ALTER TABLESPACE new_ts OFFLINE;
2. Copy the new tablespace to a local temporary location using an operating system utility copy command. Copying the files to a temporary location will reduce the amount of time the tablespace must remain offline. The following example copies the tablespace using the UNIX cp command:
% cp /disk1/oracle/oradata/payroll/t_db2.dbf /disk1/oracle/oradata/payroll/s2t_db2.dbf
3. Place the new tablespace back online:
SQL> ALTER TABLESPACE new_ts ONLINE;
4. Copy the local copy of the tablespace to a remote standby location using an operating system utility command. The following example uses the UNIX rcp command:
%rcp /disk1/oracle/oradata/payroll/s2t_db2.dbf standby_location
4. Archive the current online redo log file on the primary database so it will get transmitted to the standby database:
SQL> ALTER SYSTEM ARCHIVE LOG CURRENT;
5. Use the following query to make sure that Redo Apply is running. If the MRP or MRP0 process is returned, Redo Apply is being performed.
SQL> SELECT PROCESS, STATUS FROM V$MANAGED_STANDBY;
6. Verify the datafile was added to the standby database after the archived redo log file was applied to the standby database:
SQL> SELECT NAME FROM V$DATAFILE;
NAME
———————————————————————-
/disk1/oracle/oradata/payroll/s2t_db1.dbf
/disk1/oracle/oradata/payroll/s2t_db2.dbf
====================================================================================
Dropping a Tablespace in the Primary Database
====================================================================================
When you delete one or more datafiles or drop one or more tablespaces in the primary database, you also need to delete the corresponding datafiles in the standby database. The following sections provide examples of dropping a datafile to the primary and standby databases when the STANDBY_FILE_MANAGEMENT initialization parameter is set to AUTO and MANUAL. To verify any deleted datafiles are no longer part of the database, query the V$DATAFILE view.
==============================================================================================================================
Dropping a Tablespace and a Datafile When STANDBY_FILE_MANAGEMENT Is Set to AUTO or MANUAL
==============================================================================================================================
The following procedure works whether the STANDBY_FILE_MANAGEMENT initialization parameter is set to either MANUAL or AUTO, as follows:
1. Drop the tablespace at the primary site:
SQL> DROP TABLESPACE tbs_4;
SQL> ALTER SYSTEM SWITCH LOGFILE;
2. Make sure that Redo Apply is running (so that the change is applied to the standby database). If the following query returns the MRP or MRP0 process, Redo Apply is running.
SQL> SELECT PROCESS, STATUS FROM V$MANAGED_STANDBY;
Optionally, you can query the V$DATAFILE view to verify any deleted datafiles are no longer part of the database.
3. Delete the corresponding datafile on the standby site after the archived redo log file was applied to the standby database. For example:
% rm /disk1/oracle/oradata/payroll/s2tbs_4.dbf
4. On the primary database, after ensuring the standby database applied the redo information for the dropped tablespace, you can remove the datafile for the tablespace. For example:
% rm /disk1/oracle/oradata/payroll/tbs_4.dbf
====================================================================================
Dropping a Tablespace and a Datafile When STANDBY_FILE_MANAGEMENT Is Set to AUTO
====================================================================================
You can issue the SQL DROP TABLESPACE INCLUDING CONTENTS AND DATAFILES statement on the primary database to delete the datafiles on both the primary and standby databases. To use this statement, the STANDBY_FILE_MANAGEMENT initialization parameter must be set to AUTO. For example, to drop the tablespace at the primary site:
SQL> DROP TABLESPACE NCLUDING CONTENTS AND DATAFILES tbs_4;
SQL> ALTER SYSTEM SWITCH LOGFILE;