Adding,Deleting or renaming tablespace or database on primary what affect on standby database

8.1 Adding a Datafile or Creating a Tablespace

The initialization parameter, STANDBY_FILE_MANAGEMENT, allows 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 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.

8.2 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 't_db2.dbf'

  2> SIZE 1m AUTOEXTEND ON MAXSIZE UNLIMITED;

2. Archive the current redo log so it will get copied to the standby database:

SQL> ALTER SYSTEM ARCHIVE LOG CURRENT;

3. Verify that the new datafile was added to the primary database:

SQL> SELECT NAME FROM V$DATAFILE;

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

/disk1/oracle/dbs/t_db1.dbf

/disk1/oracle/dbs/t_db2.dbf

4. Verify that the new datafile was added to the standby database:

SQL> SELECT NAME FROM V$DATAFILE;

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

/disk1/oracle/dbs/s2t_db1.dbf

/disk1/oracle/dbs/s2t_db2.dbf

8.3 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 't_db2.dbf'

       2> SIZE 1m AUTOEXTEND ON MAXSIZE UNLIMITED;

2.      Verify that the new datafile was added to the primary database:

SQL> SELECT NAME FROM V$DATAFILE;

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

/disk1/oracle/dbs/t_db1.dbf

/disk1/oracle/dbs/t_db2.dbf

3.      Perform the following steps to copy the tablespace to a remote standby location:

a.       Place the new tablespace offline:

SQL> ALTER TABLESPACE new_ts OFFLINE;

b.      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 that the tablespace must remain offline. The following example copies the tablespace using the UNIX cp command:

% cp t_db2.dbf s2t_db2.dbf

c.       Place the new tablespace back online:

SQL> ALTER TABLESPACE new_ts ONLINE;

d.      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 s2t_db2.dbf standby_location

4.      Archive the current redo log on the primary database so it will get copied to the standby database:

SQL> ALTER SYSTEM ARCHIVE LOG CURRENT;

5.      Use the following query to make sure that managed recovery is running. If the MRP or MRP0 process is returned, managed recovery is being performed.

SQL> SELECT PROCESS, STATUS FROM V$MANAGED_STANDBY;

6.      Verify that the datafile was added to the standby database after the redo log was applied to the standby database.

 SQL> SELECT NAME FROM V$DATAFILE;

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

/disk1/oracle/dbs/s2t_db1.dbf

/disk1/oracle/dbs/s2t_db2.dbf

8.4 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, as follows:

1. Drop the tablespace at the primary site:

SQL> DROP TABLESPACE tbs_4;

SQL> ALTER SYSTEM SWITCH LOGFILE;

% rm tbs_4.dbf

2. Make sure that managed recovery is on (so that the change is applied to the standby database). If the following query returns the MRP or MRP0 process, managed recovery is on.

SQL> SELECT PROCESS, STATUS FROM V$MANAGED_STANDBY;

3. Delete the corresponding datafile on the standby site after the archived redo log was applied to the standby database. For example:

% rm tbs_4.dbf

4. On the primary database, after ensuring that the standby database has applied the redo information for the dropped tablespace, you can remove the datafile for the tablespace. For example:

% rm tbs_4.dbf

8.5 Renaming a Datafile in the Primary Database

When you rename one or more datafiles in the primary database, the change is not propagated to the standby database. Therefore, if you want to rename the same datafiles on the standby database, you must manually make the equivalent modifications on the standby database because the modifications are not performed automatically, even if the STANDBY_FILE_MANAGEMENT initialization parameter is set to AUTO.

The following steps describe how to rename a datafile in the primary database and manually propagate the changes to the standby database. If you do not want the standby database to have the same physical structure as the primary database, then these steps are not required.

1. To rename the datafile in the primary database, take the tablespace offline:

SQL> ALTER TABLESPACE tbs_4 OFFLINE;

2. Exit from the SQL prompt and issue an operating system command, such as the following UNIX mv command, to rename the datafile on the primary system:

% mv tbs_4.dbf tbs_x.dbf

3. Rename the datafile in the primary database and bring the tablespace back online:

SQL> ALTER TABLESPACE tbs_4 RENAME DATAFILE 'tbs_4.dbf'

  2> TO 'tbs_x.dbf';

SQL> ALTER TABLESPACE tbs_4 ONLINE;

4. Connect to the standby database and make sure that all the logs are applied; then stop managed recovery operations:

SQL> SELECT NAME, SEQUENCE#, ARCHIVED, APPLIED

  2> FROM V$ARCHIVED_LOG;

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

5. Shut down the standby database:

SQL> SHUTDOWN;

6. Rename the datafile at the standby site using an operating system command, such as the UNIX mv command:

% mv tbs_4.dbf tbs_x.dbf

7. Start and mount the standby database with the new control file:

  SQL> STARTUP NOMOUNT;

     SQL> ALTER DATABASE MOUNT STANDBY DATABASE;

8. Rename the datafile in the standby controlfile. Note that the STANDBY_FILE_MANAGEMENT initialization parameter must be set to MANUAL.

SQL> ALTER DATABASE RENAME FILE 'tbs_4.dbf'

  2> TO 'tbs_x.dbf';

9. On the standby database, restart managed recovery operations:

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE

  2> DISCONNECT FROM SESSION;

If you do not rename the corresponding datafile at the standby site, and then try to refresh the standby database control file, the standby database will attempt to use the renamed datafile, but it will not find it. Consequently, you will see error messages similar to the following in the alert log:

ORA-00283: recovery session canceled due to errors

ORA-01157: cannot identify/lock data file 4 - see DBWR trace file

ORA-01110: data file 4: '/disk1/oracle/dbs/tbs_x.dbf'