Renaming A Datafile In A Physical Standby Environment

In this article we will see how to rename a datafile in a physical standby setup. Most structural changes made to a primary database are automatically propagated through redo data to a physical standby database. Some structural and configuration changes at primary database requires manual intervention at a physical standby 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.

1. Verify primary and standby databases and also change the initialization parameter STANDBY_FILE_MANAGEMENT from AUTO to MANUAL.

On Primary :

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

NAME      DB_UNIQUE_NAME                 DATABASE_ROLE    SWITCHOVER_STATUS
--------- ------------------------------ ---------------- --------------------
ORCL      orcl                           PRIMARY          TO STANDBY

SQL> archive log list
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            /home/oracle/app/oracle/flash_recovery_area/orcl/
Oldest online log sequence     524
Next log sequence to archive   526
Current log sequence           526
SQL> select database_role,switchover_status from v$database;

DATABASE_ROLE    SWITCHOVER_STATUS
---------------- --------------------
PRIMARY          TO STANDBY

SQL> sho parameter STANDBY_FILE_MANAGEMENT

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
standby_file_management              string      AUTO
SQL> ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT=MANUAL;

System altered.

SQL> sho parameter STANDBY_FILE_MANAGEMENT

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

On Standby :

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

NAME      DB_UNIQUE_NAME                 DATABASE_ROLE    SWITCHOVER_STATUS
--------- ------------------------------ ---------------- --------------------
ORCL      sbyorcl                        PHYSICAL STANDBY NOT ALLOWED

SQL> archive log list
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            /home/oracle/app/oracle/flash_recovery_area/sbyorcl/
Oldest online log sequence     524
Next log sequence to archive   0
Current log sequence           526
SQL> select database_role,switchover_status from v$database;

DATABASE_ROLE    SWITCHOVER_STATUS
---------------- --------------------
PHYSICAL STANDBY NOT ALLOWED

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

SEQUENCE# APPLIED
---------- ---------
517 YES
518 YES
519 YES
520 YES
521 YES
522 YES
523 YES
524 YES
525 IN-MEMORY

9 rows selected.

SQL> sho parameter STANDBY_FILE_MANAGEMENT

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
standby_file_management              string      AUTO
SQL> ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT=MANUAL;

System altered.

SQL> sho parameter STANDBY_FILE_MANAGEMENT

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
standby_file_management              string      MANUAL
SQL>

2. Verify the tablespace to which the datafile belongs and make it offline. This step is performed on primary database.

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

FILE_NAME
--------------------------------------------------------------------------------
/home/oracle/app/oracle/oradata/orcl/example01.dbf

SQL> alter tablespace example offline;

Tablespace altered.

3. Rename the datafile to new location and bring tablespace back online. This step is performed on primary database.

[oracle@dgaskmpri01 OPatch]$ mv /home/oracle/app/oracle/oradata/orcl/example01.dbf /tmp/askm/example01_temp.dbf
[oracle@dgaskmpri01 OPatch]$ sqlplus '/as sysdba'

SQL*Plus: Release 11.2.0.2.0 Production on Tue Nov 22 05:47:17 2011

Copyright (c) 1982, 2010, Oracle. All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> alter tablespace example rename datafile '/home/oracle/app/oracle/oradata/orcl/example01.dbf' to '/tmp/askm/example01_temp.dbf';

Tablespace altered.

SQL> alter tablespace example online;

Tablespace altered.

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

FILE_NAME
--------------------------------------------------------------------------------
/tmp/askm/example01_temp.dbf

SQL>

4. Verify the same tablespace on standby database.

SQL> select ts#,name from v$tablespace where name='EXAMPLE';

TS# NAME
---------- ------------------------------
6 EXAMPLE

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

NAME
--------------------------------------------------------------------------------
/home/oracle/app/oracle/oradata/sbyorcl/example01.dbf

SQL>

5. Stop recovery on standby database and shut it down.

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

Database altered.

SQL> shut immediate
ORA-01109: database not open

Database dismounted.
ORACLE instance shut down.
SQL>

6. Rename the datafile on standby database.

[oracle@dgaskmsby01 askm]$ mv /home/oracle/app/oracle/oradata/sbyorcl/example01.dbf /tmp/askm/example01_temp.dbf
[oracle@dgaskmsby01 askm]$ sqlplus '/as sysdba'

SQL*Plus: Release 11.2.0.2.0 Production on Tue Nov 22 05:53:49 2011

Copyright (c) 1982, 2010, Oracle. All rights reserved.

Connected to an idle instance.

SQL> startup mount
ORACLE instance started.

Total System Global Area 456146944 bytes
Fixed Size                  1344840 bytes
Variable Size             343935672 bytes
Database Buffers          104857600 bytes
Redo Buffers                6008832 bytes
Database mounted.
SQL> alter database rename file '/home/oracle/app/oracle/oradata/sbyorcl/example01.dbf' to '/tmp/askm/example01_temp.dbf';

Database altered.

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

NAME
--------------------------------------------------------------------------------
/tmp/askm/example01_temp.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.

Reference :

Renaming a Datafile in the Primary Database

MOS ID : 733796.1

Hope It Helps

SRI

askMLabs