Moving datafile from one location to another in Standby
3 min read
[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.
