Data File Rename/Move in Oracle Active Data Guard Environment

Data File rename or move activity is not applied automatically to standby Database even if STANDBY_FILE_ MANAGEMENT is set to AUTO. When STANDBY_FILE_MANAGEMENT initialization parameter to AUTO, use of the following SQL statements is not allowed:


ALTER DATABASE RENAME .. 
ALTER DATABASE ADD/DROP LOGFILE ..
ALTER DATABASE ADD/DROP STANDBY LOGFILE MEMBER ..
ALTER DATABASE CREATE DATAFILE AS ..


Those following steps show how to move Data File in Primary Database and apply this change to Standby Database.

On Primary Database:


1. Set standby_file_managemen parameter to MANUAL.

SQL>ALTER SYSTEM SET standby_file_management=MANUAL;

2.In the primary database, take the tablespace offline.

SQL> ALTER TABLESPACE tbs_4 OFFLINE;

3.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 /disk1/oracle/oradata/payroll/tbs_4.dbf /disk1/oracle/oradata/payroll/tbs_x.dbf

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

SQL> ALTER TABLESPACE tbs_4 RENAME DATAFILE  '/disk1/oracle/oradata/payroll/tbs_4.dbf' TO '/disk1/oracle/oradata/payroll/tbs_x.dbf';

SQL> ALTER TABLESPACE tbs_4 ONLINE;


On Standby Database:



1.Connect to the standby database and stop Redo Apply:

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

2.Shut down the standby database:

SQL> SHUTDOWN IMMEDIATE;
SQL> STARTUP MOUNT;

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

% mv /disk1/oracle/oradata/payroll/tbs_4.dbf /disk1/oracle/oradata/payroll/tbs_x.dbf

4.Rename the datafile in the Data Dictionary. Note that the STANDBY_FILE_
MANAGEMENT database initialization parameter must be set to MANUAL in order to rename a datafile.

SQL> ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT=MANUAL SCOPE=BOTH;
SQL> ALTER DATABASE RENAME FILE '/disk1/oracle/oradata/payroll/tbs_4.dbf' TO	'/disk1/oracle/oradata/payroll/tbs_x.dbf';

5.On the standby database, Set STANDBY_FILE_MANAGEMENT=AUTO and restart Redo Apply:

SQL> ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT=AUTO SCOPE=BOTH;
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE

Thanks
A Rahim Khan

Advertisements
    • Fernando
    • December 3rd, 2014

    Hi Rahim,

    Great Article, great one.

    I got one doubt. What if I need to “move” a set of tablespaces on primary “only” to a new added disk?

    I will normally take the ts offline, copy it and then rename it.

    But what if is still and mus still on the same disk on the standby? What sould I do?

    See:
    PRIMARY TS INDX
    c:\oracle\oradata\indx.dbf has to be moved to E:\oracle\oradata\indx.dbf

    STANDY
    the file c:\oracle\oradata\indx.dbf must still on the same directory, just the PRIMARY has changed.

    How to do this without affecting the environment?

    tks really a lot

    • you may follow those steps

      1. standby database in mount state
      2. Stop redo apply
      3. standby file management manual on both primary and standby.
      4. make your change in primary
      5. change file management to auto again in both primary and standby

      Worst of all, I have not done this drill yet but it should be this way. hope for the best.

  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: