Now from 12c database you can move/relocate datafiles ONLINE menaing users can still able to access the database and
objects while the move/relocate is in process, prior to 12c it was not the case.
Prerequisites:
1. Oracle cluster need to be installed on that server( for ASM)
2. Create required asm disk groups.
EXAMPLE:
Currently the datafiles are in /u01 mount point. We will move them to ASM DISK GROUP +DATA.
Database Name: TESTDB
Asm disk group: +DATA
oracle $ rman target /
Recovery Manager: Release 12.1.0.2.0 – Production on Wed Jul 28 20:45:03 2018
Copyright (c) 1982, 2014, Oracle and/or its affiliates. All rights reserved.
connected to target database: TESTDB (DBID=2123458733)
RMAN> report schema;
using target database control file instead of recovery catalog
Report of database schema for database with db_unique_name TESTDB
List of Permanent Datafiles
===========================
File Size(MB) Tablespace RB segs Datafile Name
—- ——– ——————– ——- ————————
1 1024 SYSTEM YES /u01/TESTDB/u01/oradata/TESTDB/system01.dbf
2 1114 SYSAUX NO /u01/TESTDB/u02/oradata/TESTDB/sysaux01.dbf
3 31974 UNDOTBS1 YES /u01/TESTDB/u04/oradata/TESTDB/undotbs01.dbf
4 50 USERS NO /u01/TESTDB/u03/oradata/TESTDB/users01.dbf
5 100 STAGING_TS NO /u01/TESTDB/u01/oradata/TESTDB/staging_01.dbf
……………..Trimmed …………….
186 100 COMP_INDEX NO /u01/TESTDB/u02/oradata/TESTDB/COMP_INDEX_05.dbf
187 100 COMP_INDEX NO /u01/TESTDB/u01/oradata/TESTDB/COMP_INDEX_04.dbf
188 100 COMP_INDEX NO /u01/TESTDB/u03/oradata/TESTDB/COMP_INDEX_03.dbf
189 100 COMP_INDEX NO /u01/TESTDB/u01/oradata/TESTDB/COMP_INDEX_02.dbf
List of Temporary Files
=======================
File Size(MB) Tablespace Maxsize(MB) Tempfile Name
—- ——– ——————– ———– ——————–
1 1177 TEMP 32767 /u01/TESTDB/u03/oradata/TESTDB/temp01.dbf
2 2554 TEMP 32767 /u01/TESTDB/u03/oradata/TESTDB/temp02.dbf
3 2554 TEMP 32767 /u01/TESTDB/u03/oradata/TESTDB/temp03.dbf
***********Trimmed ********
29 2554 TEMP 32767 /u01/TESTDB/u04/oradata/TESTDB/temp29.dbf
30 2554 TEMP 32767 /u01/TESTDB/u04/oradata/TESTDB/temp30.dbf
31 2554 TEMP 32767 /u01/TESTDB/u04/oradata/TESTDB/temp31.dbf
32 2554 TEMP 32767 /u01/TESTDB/u04/oradata/TESTDB/temp32.dbf
RMAN> exit
Datafile Move:
==============
Create SCRIPTS rename datafile scripts and execute
SQL> set heading off pages 999 line 280
SQL> spool move_dbfiles.sql
SQL> select ‘ALTER DATABASE MOVE DATAFILE ”’||name||”’ TO ”+DATA’;’ from v$datafile order by con_id;
SQL> spool off;
SQL> @move_dbfiles.sql
Tempfile:
=========
SELECT FILE_NAME,BYTES/1024/1024 FROM DBA_TEMP_FILES;
create temporary tablespace TEMP1 tempfile ‘+DATA’ SIZE 100m reuse AUTOEXTEND ON NEXT 10M MAXSIZE 500M;
ALTER DATABASE DEFAULT TEMPORARY TABLESPACE TEMP1;
Check the Session that using Temp Tablespace:
==============================================
col hash_value for a40
col tablespace for a10
col username for a15
set linesize 132 pagesize 1000
SQL>SELECT s.sid,s.serial#, s.username, u.tablespace, s.sql_hash_value||’/’||u.sqlhash hash_value, u.segtype, u.contents, u.blocks
FROM v$session s, v$tempseg_usage u
WHERE s.saddr=u.session_addr
order by u.blocks;
SQL>alter system kill session ‘sid,serial#’ immediate;
SQL>drop tablespace temp including contents and datafiles;
SQL>create temporary tablespace TEMP tempfile ‘+DATA’ SIZE 100m AUTOEXTEND ON NEXT 100M MAXSIZE 2G;
SQL>ALTER TABLESPACE temp ADD TEMPFILE ‘+DATA’ SIZE 100m AUTOEXTEND ON NEXT 10M MAXSIZE 1G;
SQL>ALTER DATABASE DEFAULT TEMPORARY TABLESPACE TEMP
SQL>drop tablespace temp1 including contents and datafiles;
Logfile:
==========
SQL> select member from v$logfile;
MEMBER
———————————————————
/u01/TESTDB/u70/redo/redo01a.log
/u01/TESTDB/u72/redo/redo02b.log
/u01/TESTDB/u70/redo/redo02a.log
/u01/TESTDB/u72/redo/redo01b.log
/u01/TESTDB/u70/redo/redo03a.log
/u01/TESTDB/u72/redo/redo03b.log
SQL> select group#, status from v$log;
GROUP# STATUS
———- —————-
1 INACTIVE
2 INACTIVE
3 INACTIVE
4 CURRENT
5 INACTIVE
6 INACTIVE
SQL>> alter database drop logfile group 1;
Statement processed
SQL>> alter database add logfile group 1 (‘+DATA’) size 200M;
Statement processed
SQL>> alter database drop logfile group 2;
Statement processed
SQL>> alter database drop logfile group 3 ;
Statement processed
SQL>> alter database add logfile group 2 (‘+DATA’) size 200M;
Statement processed
SQL>> alter database add logfile group 3 (‘+DATA’) size 200M;
Statement processed
SQL>> select group#, status from v$log;
GROUP# STATUS
———- —————-
1 UNUSED
2 UNUSED
3 UNUSED
4 CURRENT
5 INACTIVE
6 INACTIVE
SQL> select member from v$logfile;
MEMBER
——————————————————————————–
+DATA/TESTDB/ONLINELOG/group_1.260.947891785
+DATA/TESTDB/ONLINELOG/group_2.261.947891801
+DATA/TESTDB/ONLINELOG/group_3.256.947890863
/u01/TESTDB/u72/redo/redo01b.log
/u01/TESTDB/u70/redo/redo03a.log
/u01/TESTDB/u72/redo/redo03b.log
SQL> alter system switch logfile;
Statement processed
SQL> select group#, status from v$log;
GROUP# STATUS
———- —————-
1 CURRENT
2 UNUSED
3 UNUSED
4 ACTIVE
5 INACTIVE
6 INACTIVE
SQL> alter system checkpoint;
Statement processed
SQL> select group#, status from v$log;
GROUP# STATUS
———- —————-
1 CURRENT
2 UNUSED
3 UNUSED
4 INACTIVE
5 INACTIVE
6 INACTIVE
SQL> alter database drop logfile group 4;
Statement processed
SQL> alter database drop logfile group 5;
Statement processed
SQL> alter database drop logfile group 6;
Statement processed
SQL> alter database add logfile group 4 (‘+DATA’) size 200M;
Statement processed
SQL> alter database add logfile group 5 (‘+DATA’) size 200M;
Statement processed
SQL> alter database add logfile group 6 (‘+DATA’) size 200M;
Statement processed
SQL> select member from v$logfile;
MEMBER
——————————————————————————–
+DATA/TESTDB/ONLINELOG/group_1.260.947891785
+DATA/TESTDB/ONLINELOG/group_2.261.947891801
+DATA/TESTDB/ONLINELOG/group_3.256.947890863
+DATA/TESTDB/ONLINELOG/group_4.257.947891047
+DATA/TESTDB/ONLINELOG/group_5.258.947891057
+DATA/TESTDB/ONLINELOG/group_6.259.947891065
Controlfile;
==============
SQL> sho parameter control
NAME TYPE VALUE
———————————— ———– ——————————
control_file_record_keep_time integer 10
control_files string /u01/TESTDB/u02/oradata/TESTDB/control01.ctl, /u01/TESTDB/u03/oradata/TESTDB/control02.ctl
control_management_pack_access string DIAGNOSTIC+TUNING
SQL> ALTER SYSTEM SET control_files=’+DATA’ scope=spfile;
SQL> select name from v$controlfile;
SQL>shut immediate;
SQL>startup nomount;
$ rman target /
Recovery Manager: Release 12.1.0.2.0 – Production on Wed Jul 28 23:20:32 2018
Copyright (c) 1982, 2014, Oracle and/or its affiliates. All rights reserved.
connected to target database: TESTDB (not mounted)
RMAN> restore controlfile to ‘+DATA’ from ‘/u01/TESTDB/u02/oradata/TESTDB/control01.ctl’;
Starting restore at 28-Jul-17
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=1861 device type=DISK
channel ORA_DISK_1: copied control file copy
Finished restore at 28-Jul-17
RMAN> restore controlfile to ‘+DATA’ from ‘/u01/TESTDB/u03/oradata/TESTDB/control02.ctl’;
Starting restore at 28-Jul-17
using channel ORA_DISK_1
channel ORA_DISK_1: copied control file copy
Finished restore at 28-Jul-17
MAN> exit
Recovery Manager complete.
Check the newly created control files in ASM
:+ASM:/home/oracle $ asmcmd -p
ASMCMD [+] > lsdg
State Type Rebal Sector Block AU Total_MB Free_MB Req_mir_free_MB Usable_file_MB Offline_disks Voting_files Name
MOUNTED EXTERN N 4096 4096 1048576 5722044 5680486 0 5680486 0 N DATA/
ASMCMD [+DATA/TESTDB] > ls
CONTROLFILE/
DATAFILE/
TEMPFILE/
ASMCMD [+DATA/TESTDB] > cd CONTROLFILE/
ASMCMD [+DATA/TESTDB/CONTROLFILE] > ls
current.448.947892061
current.262.947892111
/home/oracle$ sqlplus / as sysdba
SQL*Plus: Release 12.1.0.2.0 Production on Wed Jul 28 23:27:16 2018
Copyright (c) 1982, 2014, Oracle. All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 – 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Advanced Analytics
and Real Application Testing options
SQL> sho parameter spfile
NAME TYPE VALUE
———————————— ———– ——————————
spfile string /opt/oracle/product/12.1.0.2.64/dbs/spfileTESTDB.ora
SQL> alter system set control_files=’+DATA/TESTDB/CONTROLFILE/current.448.947892061′,’+RECO/TESTDB/CONTROLFILE/current.262.947892111′ scope=spfile;
System altered.
SQL> alter database mount ;
Database altered.
SQL>select name from v$controlfile;
SQL> shut immediate;
SQL> startup;
