How to Rename A Datafile in A Physical Standby Environment

How to Rename A Datafile in A Physical Standby Environment:-


Primary db_unique_name:- prim
Standby db_unique_name:- stand

1.Verify primary and standby databases and also change the initialization parameter STANDBY_FILE_MANAGEMENT from AUTO to MANUAL.
At Primary Database :-
SQL> select name,db_unique_name,database_role,switchover_status from v$database;

NAME      DB_UNIQUE_NAME                 DATABASE_ROLE    SWITCHOVER_STATUS
--------- ------------------------------ ---------------- --------------------
PRIM      prim                           PRIMARY          TO STANDBY



SQL> SHOW PARAMETER STANDBY_FILE_MANAGEMENT;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
standby_file_management              string      auto


SQL> ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT=MANUAL;

System altered.

SQL> show parameter STANDBY_FILE_MANAGEMENT

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

SQL> archive log list;
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            /u01/app/oracle/flash_recovery_area
Oldest online log sequence     27
Next log sequence to archive   29
Current log sequence           29


On Standby database :
SQL> select name,db_unique_name,database_role,switchover_status from v$database;

NAME      DB_UNIQUE_NAME                 DATABASE_ROLE    SWITCHOVER_STATUS
--------- ------------------------------ ---------------- --------------------
PRIM      stand                          PHYSICAL STANDBY NOT ALLOWED

SQL> archive log list;
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            /u01/app/oracle/flash_recovery_area
Oldest online log sequence     21
Next log sequence to archive   0
Current log sequence           29

SQL> SELECT SEQUENCE#,APPLIED FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#;

 SEQUENCE# APPLIED
---------- ---------
         7 YES
         8 YES
         9 YES
        10 YES
        11 YES
        12 YES
        13 YES
        14 YES
        15 YES
        16 YES
        17 YES

 SEQUENCE# APPLIED
---------- ---------
        18 YES
        19 YES
        20 YES
        21 YES
        22 YES
        23 YES
        24 YES
        25 YES
        26 YES
        27 YES
        28 YES

22 rows selected.

SQL> sho parameter STANDBY_FILE_MANAGEMENT

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
standby_file_management              string      auto

SQL> ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT=MANUAL;



2.Now we will verify the tablespace to which the datafile belongs and make it offline. This step is performed on primary database.

SQL> select file_name , tablespace_name from dba_data_files;
/u01/app/oracle/oradata/prim/users01.dbf USERS

/u01/app/oracle/oradata/prim/undotbs01.dbf UNDOTBS1

/u01/app/oracle/oradata/prim/sysaux01.dbf SYSAUX

/u01/app/oracle/oradata/prim/system01.dbf SYSTEM

/u01/app/oracle/oradata/prim/example01.dbf EXAMPLE

3. Rename the datafile to new location and bring tablespace back online. This step is performed on primary database.
[oracle@server1 prim]$ cd /u01/app/oracle/oradata/prim
[oracle@server1 prim]$ ls -ltrh
-rw-r-----. 1 oracle oinstall 671M Dec 28 01:14 system01.dbf
-rw-r-----. 1 oracle oinstall 101M Dec 28 01:16 example01.dbf
......
[oracle@server1 prim]$ mv example01.dbf /home/oracle/example_01.dbf
[oracle@server1 prim]$ sqlplus /  as sysdba
SQL> alter tablespace example rename datafile '/u01/app/oracle/oradata/prim/example01.dbf' to '/home/oracle/example_01.dbf';

Tablespace altered.

SQL> alter tablespace example online;

Tablespace altered.

SQL> select file_name from dba_data_files where tablespace_name= 'EXAMPLE';

FILE_NAME
--------------------------------------------------------------------------------
/home/oracle/example_01.dbf

4. Verify the same  tablespace on standby database.
SQL> select ts# , name from v$tablespace;

       TS# NAME
---------- ------------------------------
         0 SYSTEM
         1 SYSAUX
         2 UNDOTBS1
         4 USERS
         3 TEMP
         6 EXAMPLE

5. Stop recovery on standby database and shut it down.
QL>  ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

Database altered.

SQL> shut immediate;

6. Rename the datafile on standby database.
[oracle@server2 ~]$ cd /u01/app/oracle/oradata/stand/
[oracle@server2 stand]$ mv example01.dbf /home/oracle/example_01.dbf
[oracle@server2 stand]$ sqlplus /  as sysdba
SQL> startup mount
SQL> alter database rename file '/u01/app/oracle/oradata/stand/example01.dbf' to '/home/oracle/example_01.dbf';

Database altered.

SQL> select name from v$datafile where ts#=6;

NAME
--------------------------------------------------------------------------------
/home/oracle/example_01.dbf

7. Keep standby database in recovery mode.

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT FROM SESSION;

Database altered.

8. Set initialization parameter STANDBY_FILE_MANAGEMENT value back to AUTO.

On Primary :

SQL> ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT=AUTO;

System altered.

On Standby :

SQL> ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT=AUTO;

System altered.

on Standby:-
SQL> alter database recover managed standby database cancel;

Database altered.

SQL> alter database recover managed standby database using current logfile disconnect from session;

Database altered.

SQL> select open_mode from v$database;

OPEN_MODE
--------------------
READ ONLY WITH APPLY