Prior to Oracle 12c, if you wanted to move a database’s file, you either had to shutdown the database, or take the datafile/tablespace offline. Here is an example of the steps you might take
SQL> ALTER TABLESPACE test OFFLINE;
SQL> !mv /ora/test1/test01.dbf /ora/test2/test01.dbf
SQL> ALTER DATABASE RENAME FILE ‘/ora/test1/test01' TO ‘/ora/test2/test01.dbf’;
SQL>ALTER TABLESPACE test ONLINE;
[oracle@oracle12c bin]$ export ORACLE_SID=cdb1
[oracle@oracle12c bin]$ pwd
/ora/oracle/app/oracle/product/12.1.0/dbhome_1/bin
[oracle@oracle12c bin]$ ./sqlplus /nolog
SQL*Plus: Release 12.1.0.1.0 Production on Fri 6 Sept 22 01:38:02 2013
Copyright (c) 1982, 2013, Oracle. All rights reserved.
SQL> conn / as sysdba
Connected.
SQL> show con_name
CON_NAME
------------------------------
CDB$ROOT
SQL> alter session set container=pdb1;
Session altered.
SQL> show pdbs;
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
3 PDB1 READ WRITE NO
SQL> select file_name from dba_data_Files;
FILE_NAME
--------------------------------------------------------------------------------
/ora/oracle/app/oracle/oradata/cdb1/pdb1/system01.dbf
/ora/oracle/app/oracle/oradata/cdb1/pdb1/sysaux01.dbf
/ora/oracle/app/oracle/oradata/cdb1/pdb1/pdb1_users01.dbf
SQL> show con_name
CON_NAME
------------------------------
PDB1
SQL> select tablespace_name from dba_data_Files;
TABLESPACE_NAME
------------------------------
SYSTEM
SYSAUX
USERS
Creating Tablespace user_data
Creating Tablespace user_data
SQL> create tablespace user_dat
2 datafile '/ora/oracle/app/oracle/oradata/cdb1/pdb1/userdat.dbf'
3 size 1g;
Tablespace created.
SQL> select tablespace_name from dba_data_files;
TABLESPACE_NAME
------------------------------
SYSTEM
SYSAUX
USERS
USER_DAT
Inserting Rows in test table
SQL> create table system.test(id number) tablespace user_dat;
Table created.
SQL> insert into system.test values(05);
1 row created.
SQL> commit;
Commit complete.
Move Tablespace datafile to another location
Move Tablespace datafile to another location
SQL> alter database move datafile
2 '/ora/oracle/app/oracle/oradata/cdb1/pdb1/userdat.dbf' to
3 '/ora/oracle/app/oracle/oradata/cdb1/movefile/userdat.dbf';
Database altered.
SQL> insert into system.test values(20);
1 row created.
SQL> commit;
Commit complete.
SQL> select * from system.test;
ID
----------
5
20
SQL> exit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
[oracle@oracle12c bin]$ pwd
/ora/oracle/app/oracle/product/12.1.0/dbhome_1/bin
oracle@oracle12c bin]$ cd /
oracle@oracle12c cdb1]$ ls
control01.ctl pdb1.xml pdbseed redo03.log temp01.dbf
movefile pdb2 redo01.log sysaux01.dbf undotbs01.dbf
pdb1 pdb3 redo02.log system01.dbf users01.dbf
oracle@oracle12c cdb1]$ cd movefile
oracle@oracle12c movefile]$ pwd
/ora/oracle/app/oracle/oradata/cdb1/movefile
oracle@oracle12c movefile]$ ls
userdat.dbf
[oracle@oracle12c movefile]$ cd ..
[oracle@oracle12c cdb1]$ cd pdb1
[oracle@oracle12c pdb1]$ ls
pdb1_users01.dbf
sysaux01.dbf
system01.dbf
temp01.dbf
Thank you, good explanation with practical examples :)
ReplyDelete