Rename a datafile in the primary and propogate the changes to the standby database

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

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> col value format a50

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


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\nvcob\oradata\orcl11g\USERS01.DBF' TO 'D:\app\nav6cob\oradata\orcl11g_1\USERS01.DBF';

Tablespace altered.

5. Bring the Tablespace back online:

SQL> ALTER TABLESPACE USERS ONLINE;

Tablespace altered.

SQL> ALTER SYSTEM SWITCH LOGFILE;

System altered.

SQL>

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

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
SQL>

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\nav6cob\oradata\11gstdby\USERS01.DBF' to 'D:\app\nav6cob\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\NAV6COB\ORADATA\11GSTDBY_1\USERS01.DBF

SQL>