Mir Sayeed Hassan – Oracle Blog

Move the datafile in standby database from one disk to another in Oracle 11gR2(11.2.0.4)

Check the database status

SQL> select version,instance_name,open_mode from V$database,v$instance;

VERSION           INSTANCE_NAME    OPEN_MODE
------- ---------------- --------------------
11.2.0.4.0         teststd         MOUNTED

Fallow the below process to move the datafile from one location to another.

Set the standby file management to manual

SQL> show parameter  standby

NAME                                 TYPE        VALUE
------------------ ----------- ------------------------------
standby_archive_dest                 string      ?/dbs/arch
standby_file_management              string      AUTO

Set to Manual

SQL> alter system set standby_file_management=manual;
System altered.

Stop the recover process

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

Check the name of datafile exist in current location

SQL> select name from v$datafile;

NAME
-------------------------------------------------------------
/u02/oradata/TESTSTD/datafile/o1_mf_system_h0hzrvd1_.dbf
/u02/oradata/TESTSTD/datafile/o1_mf_sysaux_h0hzrvcs_.dbf
/u02/oradata/TESTSTD/datafile/o1_mf_undotbs1_h0hzrvd2_.dbf
/u02/oradata/TESTSTD/datafile/o1_mf_users_h0hzrvd4_.dbf
/u02/oradata/TESTSTD/datafile/o1_mf_example_h0hzrvd5_.dbf
/u02/oradata/TESTSTD/datafile/o1_mf_tesstd_ca_h0hzrvd1_.dbf
/u02/oradata/TESTSTD/datafile/o1_mf_teststd_ca_hmgyrm2_.dbf

7 rows selected.

Shutdown the database

SQL> shu immediate
ORA-01109: database not open
Database dismounted
ORACLE instance shut down.

Startup the database in Mount & cancel the recover process if running

SQL> alter database recover managed standby database cancel;
Database altered.

Create a directory to place existing datafile to New location

[oracle@teststd-stbydb ~]$ cd /u03/

[oracle@teststd-stbydb u03]$ mkdir -p oradata/TESTSTD/datafile

[oracle@teststd-stbydb u03]$ cd /u02/oradata/TESTSTD/datafile

[oracle@teststd-stbydb datafile]$ ls

o1_mf_example_h0hzrvd5_.dbf  o1_mf_system_h0hzrvd1_.dbf o1_mf_users_h0hzrvd4_.dbf o1_mf_sysaux_h0hzrvcs_.dbf o1_mf_undotbs1_h0hzrvd2_.dbf o1_mf_tesstd_ca_h0hzrvd1_.dbf o1_mf_teststd_ca_hmgyrm2_.dbf

Copy or Move all the datafile Physically from exiting location to the new location

[oracle@teststd-stbydb datafile]$ cp * /u03/oradata/TESTSTD/datafile

Once copied or Move successfully, Verify the size

Rename the above datafile into new location

SQL> alter database rename file '/u02/oradata/TESTSTD/datafile/o1_mf_system_h0hzrvd1_.dbf' to '/u03/oradata/TESTSTD/datafile/o1_mf_system_h0hzrvd1_.dbf';
Database altered.

SQL> alter database rename file '/u02/oradata/TESTSTD/datafile/o1_mf_sysaux_h0hzrvcs_.dbf' to '/u03/oradata/TESTSTD/datafile/o1_mf_sysaux_h0hzrvcs_.dbf';
Database altered.

SQL> alter database rename file '/u02/oradata/TESTSTD/datafile/o1_mf_undotbs1_h0hzrvd2_.dbf' to '/u03/oradata/TESTSTD/datafile/o1_mf_undotbs1_h0hzrvd2_.dbf';
Database altered.

SQL> alter database rename file '/u02/oradata/TESTSTD/datafile/o1_mf_users_h0hzrvd4_.dbf' to '/u03/oradata/TESTSTD/datafile/o1_mf_users_h0hzrvd4_.dbf';
Database altered.

SQL> alter database rename file '/u02/oradata/TESTSTD/datafile/o1_mf_example_h0hzrvd5_.dbf' to '/u03/oradata/TESTSTD/datafile/o1_mf_example_h0hzrvd5_.dbf';
Database altered.

SQL> alter database rename file '/u02/oradata/TESTSTD/datafile/o1_mf_tesstd_ca_h0hzrvd1_.dbf' to '/u03/oradata/TESTSTD/datafile/o1_mf_tesstd_ca_h0hzrvd1_.dbf';
Database altered.

SQL> alter database rename file '/u02/oradata/TESTSTD/datafile/o1_mf_teststd_ca_hmgyrm2_.dbf' to '/u03/oradata/TESTSTD/datafile/o1_mf_teststd_ca_hmgyrm2_.dbf';
Database altered.

Verify

SQL> select name  from V$datafile;

NAME
------------------------------------------------------------
/u03/oradata/TESTSTD/datafile/o1_mf_system_h0hzrvd1_.dbf
/u03/oradata/TESTSTD/datafile/o1_mf_sysaux_h0hzrvcs_.dbf
/u03/oradata/TESTSTD/datafile/o1_mf_undotbs1_h0hzrvd2_.dbf
/u03/oradata/TESTSTD/datafile/o1_mf_users_h0hzrvd4_.dbf
/u03/oradata/TESTSTD/datafile/o1_mf_example_h0hzrvd5_.dbf
/u03/oradata/TESTSTD/datafile/o1_mf_tesstd_ca_h0hzrvd1_.dbf
/u03/oradata/TESTSTD/datafile/o1_mf_teststd_ca_hmgyrm2_.dbf

7 rows selected.

Check the tempfile & rename it

SQL> select name from V$tempfile;

NAME
--------------------------------------------------------
/u02/oradata/TESTSTD/datafile/o1_mf_temp_gwnckbp8_.tmp

SQL> alter database rename file '/u02/oradata/TESTSTD/datafile/o1_mf_temp_gwnckbp8_.tmp' to '/u03/oradata/TESTSTD/datafile/o1_mf_temp_gwnckbp8_.tmp';

Database altered.

SQL> select name from V$tempfile;

NAME
--------------------------------------------------------
/u03/oradata/TESTSTD/datafile/o1_mf_temp_gwnckbp8_.tmp

SQL> show parameter db_create_

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_create_file_dest                  string      /u02/oradata/
db_create_online_log_dest_1          string      /u02/oradata/

SQL> alter system set db_create_file_dest='/u03/oradata/';
System altered.

SQL> show parameter db_create_

NAME                                 TYPE        VALUE
-------------------- ----------- ------------------------------
db_create_file_dest                  string      /u03/oradata/

SQL> alter system set standby_file_management='auto';
System altered.

Sync the database

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

====Hence the database is Sync with Primary DB, Therefore tested & verified=====