Wednesday, June 11, 2014

1z0-060 Upgrade to Oracle Database 12c - Move a data file online

oracle@solaris:~$ sqlplus donghua@orcl

SQL*Plus: Release 12.1.0.1.0 Production on Thu Jun 12 04:26:54 2014

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

Enter password:
Last Successful login time: Thu Jun 12 2014 04:26:43 +08:00

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL> alter database move datafile '/u01/app/oracle/oradata/orcl/users2.dbf'
  2  to '/u01/app/oracle/oradata/orcl/users2_new.dbf';

Database altered.

SQL> ! ls -l /u01/app/oracle/oradata/orcl/users2*
-rw-r-----   1 oracle   oinstall 10493952 Jun 12 04:27 /u01/app/oracle/oradata/orcl/users2_new.dbf

SQL> alter database move datafile '/u01/app/oracle/oradata/orcl/users2_new.dbf'
  2   to '/u01/app/oracle/oradata/orcl/users2.dbf' keep;

Database altered.

SQL> ! date
Thursday, June 12, 2014 04:29:02 AM SGT

SQL> alter system checkpoint;

System altered.

SQL> ! ls -l /u01/app/oracle/oradata/orcl/users2*
-rw-r-----   1 oracle   oinstall 10493952 Jun 12 04:28 /u01/app/oracle/oradata/orcl/users2_new.dbf
-rw-r-----   1 oracle   oinstall 10493952 Jun 12 04:29 /u01/app/oracle/oradata/orcl/users2.dbf

SQL> alter database move datafile '/u01/app/oracle/oradata/orcl/users2.dbf'
  2  to '/u01/app/oracle/oradata/orcl/users2_new.dbf';
alter database move datafile '/u01/app/oracle/oradata/orcl/users2.dbf'
*
ERROR at line 1:
ORA-01119: error in creating database file
'/u01/app/oracle/oradata/orcl/users2_new.dbf'
ORA-27038: created file already exists
Additional information: 1

SQL> alter database move datafile '/u01/app/oracle/oradata/orcl/users2.dbf'
  2  to '/u01/app/oracle/oradata/orcl/users2_new.dbf' reuse;

Database altered.

SQL> ! ls -l /u01/app/oracle/oradata/orcl/users2*
-rw-r-----   1 oracle   oinstall 10493952 Jun 12 04:31 /u01/app/oracle/oradata/orcl/users2_new.dbf