move datafile的日常操作有
Renaming an Online Data File(重命名操作)
alter database move datafile '/oradata/cdb1/system01.dbf' to '/oradata/cdb1/system.dbf';
Relocating an Online Data File(移动数据文件)
alter database move datafile '/oradata/cdb1/system01.dbf' to '/oradata/system.dbf';
Copying an Online Data File(复制数据文件)
alter database move datafile '/oradata/cdb1/system01.dbf' to '/oradata/system.dbf' keep;
Relocating an Online Data File and Overwriting an Existing File(移动并覆盖数据文件)
alter database move datafile '/oradata/cdb1/system01.dbf' to '/oradata/system.dbf' reuse;
Relocating an Online Data File to Oracle ASM(移动数据文件到ASM)
alter database move datafile '/oradata/cdb1/system01.dbf' to '+DATA_PGOLD';
在Session A中设置10046事件。同时在Session B中dump控制文件信息。
Session A
SQL> select name from v$datafile;
NAME
--------------------------------------------------------------------------------
/oradata/cdb1/system01.dbf
/oradata/cdb1/sysaux01.dbf
/oradata/cdb1/undotbs01.dbf
/oradata/cdb1/pdbseed/system01.dbf
/oradata/cdb1/users01.dbf
/oradata/cdb1/pdbseed/sysaux01.dbf
/oradata/cdb1/pdb11/system01.dbf
/oradata/cdb1/pdb11/sysaux01.dbf
/oradata/cdb1/pdb11/pdb11_users01.dbf
/oradata/cdb1/pdb12/system01.dbf
/oradata/cdb1/pdb12/sysaux01.dbf
/oradata/cdb1/pdb12/pdb12_users01.dbf
12 rows selected.
SQL> oradebug setmypid;
Statement processed.
SQL> oradebug event 10046 trace name context forever,level 12;
Statement processed.
SQL> alter database move datafile '/oradata/cdb1/system01.dbf' to '/oradata/cdb1/system.dbf';
Database altered.
SQL> oradebug tracefile_name;
/u01/oracle/diag/rdbms/cdb1/cdb1/trace/cdb1_ora_4221.trc
SQL>
Session B
SQL> oradebug setmypid;
Statement processed.
SQL> oradebug dump controlf 3;
Statement processed.
SQL> oradebug dump controlf 3;
Statement processed.
SQL> oradebug tracefile_name;
/u01/oracle/diag/rdbms/cdb1/cdb1/trace/cdb1_ora_4470.trc
SQL>
通过观察,我们可以看到实际上,Oracle又重新创建一个数据文件
***************************************************************************
DATA FILE RECORDS
***************************************************************************
(size = 520, compat size = 520, section max = 1024, section in-use = 13,
last-recid= 106, old-recno = 0, last-recno = 0)
(extent = 1, blkno = 11, numrecs = 1024)
DATA FILE #1:
name #6: /oradata/cdb1/system01.dbf
name #20: /oradata/cdb1/system.dbf ( second)
creation size=0 block size=8192 status=0xe flg=0x1 head=6 tail=20 dup=1
pdb_id 1, tablespace 0, index=2 krfil=1 prev_file_in_ts=0 prev_file_in_pdb=4
unrecoverable scn: 0x0000.00000000 01/01/1988 00:00:00
Checkpoint cnt:212 scn: 0x0000.0023aa83 05/16/2016 13:47:22
Stop scn: 0xffff.ffffffff 05/14/2016 21:47:14
Creation Checkpointed at scn: 0x0000.00000007 07/07/2014 05:38:57
通过目录我们也可以发现文件的变化
[oracle@db1 cdb1]$ ls system*
system01.dbf
[oracle@db1 cdb1]$ ls system*
system01.dbf system.dbf
[oracle@db1 cdb1]$
[oracle@db1 cdb1]$ ls system*
system.dbf
等待事件信息
Elapsed times include waiting on following events: Event waited on Times Max. Wait Total Waited ---------------------------------------- Waited ---------- ------------ SQL*Net message to client 5 0.00 0.00 SQL*Net message from client 5 56.19 94.78 control file sequential read 3264 0.00 0.01 db file sequential read 803 0.01 2.32 control file parallel write 26 0.00 0.00 Disk file operations I/O 7 0.00 0.00 db file single write 803 0.01 4.17 rdbms ipc reply 1 0.01 0.01 concurrent I/O completion 2 0.00 0.00
Reference
http://docs.oracle.com/database/121/ADMIN/dfiles.htm#ADMIN012
http://docs.oracle.com/database/121/NEWFT/chapter12101.htm#FEATURENO07040