A technical troubleshooting blog about Oracle with other Databases & Cloud Technologies.

Moving datafile from one location to another in Standby

3 min read
railings around hatch on asphalt carriageway

Photo by Skylar Kang on Pexels.com

[oracle@localhost ~]$ ps -ef|grep pmon
oracle    2113     1  0 Sep20 ?        00:54:32 ora_pmon_prod1
oracle   30032 29996  0 08:50 pts/0    00:00:00 grep --color=auto pmon
[oracle@localhost ~]$
[oracle@localhost ~]$
[oracle@localhost ~]$ . oraenv
ORACLE_SID = [oracle] ? prod1
The Oracle base remains unchanged with value /opt/app/oracle

[oracle@localhost ~]$ sqlplus / as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on Mon Oct 28 08:50:25 2019

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


Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

SQL>
SQL> select name,open_mode from v$database;

NAME      OPEN_MODE
--------- --------------------
PROD      READ ONLY WITH APPLY

Check the datafile location along with name:

SQL> select file_name,TABLESPACE_NAME, bytes/1024/1024/1024 from dba_data_files where TABLESPACE_NAME='prod101';

FILE_NAME
--------------------------------------------------------------------------------
TABLESPACE_NAME                BYTES/1024/1024/1024
------------------------------ --------------------
/datafile/PROD/datafile/PROD.dbf_01
prod101                                      10

/datafile/PROD/datafile/PROD02.dbf
prod101                              22.9083252

/datafile/PROD/datafile/PROD03.dbf
prod101                                      10


FILE_NAME
--------------------------------------------------------------------------------
TABLESPACE_NAME                BYTES/1024/1024/1024
------------------------------ --------------------
/datafile/PROD/datafile/PROD04.dbf
prod101                                      20

/datafile/PROD/datafile/prod101009.dbf
prod101                                      30

/datafile/PROD/datafile/prod101010.dbf
prod101                                      30


FILE_NAME
--------------------------------------------------------------------------------
TABLESPACE_NAME                BYTES/1024/1024/1024
------------------------------ --------------------
/opt/app/oracle/oradata/prod1/datafile/o1_mf_PRODarad_gvf0obtw_.dbf
prod101                                      30


7 rows selected.
SQL> ho
[oracle@localhost ~]$ cd /opt/app/oracle/oradata/prod1/datafile

[oracle@localhost datafile]$ ls -ltrh
total 31G
-rw-r-----. 1 oracle oinstall 438M Oct 28 09:01 o1_mf_temp_gr0xqfxb_.tmp
-rw-r-----. 1 oracle oinstall  31G Oct 28 09:11 o1_mf_PRODarad_gvf0obtw_.dbf
[oracle@localhost datafile]$

Set standby file management parameter to manual before moving datafile:

SQL> show parameter standby_file_ma

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
standby_file_management              string      AUTO


SQL> ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT=MANUAL;
System altered.


SQL>  show parameter standby_file_ma

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

Stop the MRP :

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

Database altered.

Move the datafile now :

SQL> ALTER DATABASE CREATE  DATAFILE '/opt/app/oracle/oradata/prod1/datafile/o1_mf_PRODarad_gvf0obtw_.dbf' as /datafile/PROD/datafile/prod101011.dbf'  ;

ALTER DATABASE CREATE  DATAFILE '/opt/app/oracle/oradata/prod1/datafile/o1_mf_PRODarad_gvf0obtw_.dbf' as '/datafile/PROD/datafile/prod101011.dbf'
*
ERROR at line 1:
ORA-01182: cannot create database file 17 - file is in use or recovery
ORA-01110: data file 17: '/datafile/PROD/datafile/prod101011.dbf'


Bounce the DB to overcome this issue:

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.


SQL> startup mount;
ORACLE instance started.

Total System Global Area 4.0534E+10 bytes
Fixed Size                 29864808 bytes
Variable Size            7113542808 bytes
Database Buffers         3.3286E+10 bytes
Redo Buffers              104349696 bytes
Database mounted.
SQL> ALTER DATABASE CREATE  DATAFILE '/opt/app/oracle/oradata/prod1/datafile/o1_mf_PRODarad_gvf0obtw_.dbf' as '/datafile/PROD/datafile/prod101011.dbf'  ;

Database altered.

Open the DB in read only:

SQL> alter database open;

Database altered.

SQL> select name,open_mode from v$database;

NAME      OPEN_MODE
--------- --------------------
PROD      READ ONLY

SQL>

SQL> ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT=AUTO;

System altered.

SQL>  show parameter STANDBY_FILE_MANAGE

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
standby_file_management              string      AUTO

Start MRP:

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT;

Database altered.

SQL>  select name,open_mode from v$database;

NAME      OPEN_MODE
--------- --------------------
PROD      READ ONLY WITH APPLY

Now validate ,all datafiles are in same location:

SQL> select file_name,TABLESPACE_NAME, bytes/1024/1024/1024 from dba_data_files where TABLESPACE_NAME='prod101';

FILE_NAME
--------------------------------------------------------------------------------
TABLESPACE_NAME                BYTES/1024/1024/1024
------------------------------ --------------------
/datafile/PROD/datafile/PROD.dbf_01
prod101                                      10

/datafile/PROD/datafile/PROD02.dbf
prod101                              22.9083252

/datafile/PROD/datafile/PROD03.dbf
prod101                                      10


FILE_NAME
--------------------------------------------------------------------------------
TABLESPACE_NAME                BYTES/1024/1024/1024
------------------------------ --------------------
/datafile/PROD/datafile/PROD04.dbf
prod101                                      20

/datafile/PROD/datafile/prod101009.dbf
prod101                                      30

/datafile/PROD/datafile/prod101010.dbf
prod101                                      30


FILE_NAME
--------------------------------------------------------------------------------
TABLESPACE_NAME                BYTES/1024/1024/1024
------------------------------ --------------------
/datafile/PROD/datafile/prod101011.dbf
prod101                                      30


7 rows selected.