Rename/Move a datafile in the primary and propagate the changes in the standby database.

Rename a datafile in the primary database and manually propagate the changes to the standby database.

 in PRIMARY

1. Set STANDBY_FILE_MANAGEMENT=MANUAL on both Primary and Standby Database.

SQL> show parameter standby_file_manag

NAME                                 TYPE        VALUE
------------------------------------ -----------
standby_file_management              string      AUTO

SQL>  select name,value from v$parameter where name like 'db_unique_name';

NAME                VALUE
-----------------  ---------------
db_unique_name      orcl11g

SQL>
SQL> alter system set standby_file_management=MANUAL;

System altered.

SQL>  show parameter standby_file

NAME                                 TYPE        VALUE
------------------------------------ -----------
standby_file_management              string      MANUAL

  

in standby

SQL> alter system set standby_file_management=MANUAL;

System altered.

SQL> select name,value from v$parameter where name like 'db_unique_name';

NAME                VALUE
------------------ ------------
db_unique_name      11gstdby

SQL> show parameter standby_file

NAME                                 TYPE        VALUE
------------------------------------ -----------
standby_file_management              string      MANUAL

 

in PRIMARY

2. Take the Tablespace offline on the Primary Database:

SQL> select * from v$tablespace;

       TS# NAME                           INC BIG FLA ENC
---------- ------------------------------ --- --- --- ---
         0 SYSTEM                         YES NO  YES
         1 SYSAUX                         YES NO  YES
         2 UNDOTBS1                       YES NO  YES
         4 USERS                          YES NO  YES
         3 TEMP                           NO  NO  YES
         6 FDB                            YES NO  YES

6 rows selected.


SQL> alter tablespace users offline;

Tablespace altered.

3. Rename Datafile on Primary Site:

   copy the file using OS command to the new location


4. Rename the Datafile in the Primary Database.

SQL> alter tablespace users rename datafile 'D:\app\nvcb\oradata\orcl11g\USERS01.DBF' TO 'D:\app\nvcb\oradata\orcl11g_1\USERS01.DBF';

Tablespace altered.

5. Bring the Tablespace back online:

SQL> ALTER TABLESPACE USERS ONLINE;

Tablespace altered.

in standby

1. Stop Redo Apply on Standby Database.

SQL> recover managed standby database cancel;
Media recovery complete.

2. Shutdown the Standby Database.

SQL> shutdown immediate
ORA-01109: database not open

Database dismounted.
ORACLE instance shut down.

3. Move the Datafile to the new location at the Standby site.

Copy the file using OS command to the new location.

4. Start and mount the Standby Database.

SQL> startup mount
ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance
ORACLE instance started.

Total System Global Area  535662592 bytes
Fixed Size                  1375792 bytes
Variable Size             209715664 bytes
Database Buffers          318767104 bytes
Redo Buffers                5804032 bytes
Database mounted.

5.Rename the Datafile in the Standby Database control file.

SQL> alter database rename file 'D:\app\nvcb\oradata\11gstdby\USERS01.DBF' to 'D:\app\nvcb\oradata\11gstdby_1\USERS01.DBF';

Database altered.

6. On the Standby Database, restart Redo Apply:

SQL> recover managed standby database disconnect from session;
Media recovery complete.


7. Set STANDBY_FILE_MANAGEMENT=AUTO on both Primary and Standby Database.


SQL> alter system set standby_file_management=auto;

System altered.

SQL> show parameter standby_file

NAME                                 TYPE        VALUE
------------------------------------ -----------
standby_file_management              string      AUTO

SQL>  select file#,name from v$datafile where name like '%USERS%';

     FILE#   NAME

----------  -----------------------------------------------
          4 D:\APP\nvcb\ORADATA\11GSTDBY_1\USERS01.DBF

in Primary

SQL> alter system set standby_file_management=auto;

System altered.

SQL> show parameter standby_file

NAME                                 TYPE        VALUE
------------------------------------ -----------
standby_file_management              string      AUTO


SQL>  select file#,name from v$datafile where name like '%USERS%';

     FILE# NAME
---------- --------------------------------------

         4 D:\APP\NAV6COB\ORADATA\ORCL11G_1\USERS01.DBF

Regards,

Navaneeth