Focus On Oracle

Installing, Backup & Recovery, Performance Tuning,
Troubleshooting, Upgrading, Patching

Oracle Engineered System


当前位置: 首页 » 技术文章 » Oracle 12c+

12c在线移动数据文件

在线移动数据文件意思为在不需要离线数据文件的情况下可以在线移动数据文件,用户可以继续访问,不会造成业务的中断,进而满足客户高可用性的要求。日常的维护有:迁移数据文件到另一台存储,迁移数据文件到Oracle ASM,重命名数据文件等。这些操作在12c之后可以在线完成。

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


关键词:12c 

相关文章

关于max_string_size
在Oracle数据库19c中使用JSON
保障业务连续性的神器
Oracle事务卫士(Transaction Guard)和应用连续性(Application Continuity)
容器数据库(CDB)和可插拔数据库(PDB)概述
How to generate AWR on PDB and ADG(12.2 afterwards)
在12c上使用wm_concat
Exadata with database 12.2
如何在oracle 12c中正确的应用补丁?
在OEL6.8上安装12.2 RAC
Oracle Database 12.2 Hands-On Lab
How to create single physical standby for RAC
Top