PDA

View Full Version : Moving datafiles on the primary database


Arjen Visser
03-25-2012, 08:36 AM
Question

We want to move datafiles (or relocate) on the primary database. We will use the following steps:
SQL>SHUTDOWN IMMEDIATE
... Move the datafile
cp /mnt/ssd/oradata/ops/audit_aux01.dbf /oradata/ops/audit_aux01.dbf
cp /oradata/perfstat.dbf /oradata/ops/perfstat.dbf

... Start the database in mount mode
SQL> Startup mount
... Rename file
SQL>ALTER DATABASE RENAME FILE '/mnt/ssd/oradata/ops/audit_aux01.dbf' TO '/oradata/ops/audit_aux01.dbf';
SQL>ALTER DATABASE RENAME FILE '/oradata/perfstat.dbf' to '/oradata/ops/perfstat.dbf'

What are the appropriate steps to take for the standby?
Will it automatically do the right thing?
Or, do I need to take some special steps?

Answer
The ALTER DATABASE RENAME file command is not propagated to the standby database.
If you do NOT want the files to be moved on the standby database, then there is no action to take. It is possible that the primary and standby database files are in different locations.

If you DO want the standby files to be moved, then the ALTER DATABASE RENAME file command needs to be given on the standby as well.

The steps are:

Shutdown standby database
Move the datafiles using the OS commands as with the primary database
Startup the standby database in mount mode with: dbv_oraStartStop start DDC
Rename the datafiles using the same command as on the primary database:
SQL>ALTER DATABASE RENAME ...


Notes:
If STANDBY_FILE_MANAGEMENT is set to AUTO, then the following error can occur:

SQL> ALTER DATABASE RENAME FILE '/mnt/ssd/oradata/ops/audit_aux01.dbf' TO '/oradata/ops/audit_aux01.dbf';
ALTER DATABASE RENAME FILE '/mnt/ssd/oradata/ops/audit_aux01.dbf' TO '/oradata/ops/audit_aux01.dbf'
*
ERROR at line 1:
ORA-01511: error in renaming log/data files
ORA-01275: Operation RENAME is not allowed if standby file management is
automatic.


Wrap the rename datafile with the following command to resolve this issue:
SQL>ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT=MANUAL;
SQL>ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT=AUTO;

RichardChambers
03-26-2012, 09:19 PM
Hi Arjen,

Would it not be as easy simply to create a new standby database once you have moved the files on the primary?

Regards
Richard Chambers.

Arjen Visser
03-27-2012, 07:43 AM
Hi Richard,
Yes re-creating a new standby database once the files have been moved on the primary is also a valid solution. It depends on how large your standby database is, and whether this is feasible option or not.

Regards,