impdp中的DISABLE_ARCHIVE_LOGGING参数测试

发表于 2017 年 4 月 8 日 由 惜分飞

在oracle 12c版本中引入了impdp中的TRANSFORM中的DISABLE_ARCHIVE_LOGGING值，可以实现在导入的时候使用nologging处理从而减少日志量也增加速度,但是在force logging情况下该参数无效
创建测试表

[oracle@localhost ~]$ sqlplus xff/oracle@localhost/pdb

SQL*Plus: Release 12.2.0.1.0 Production on Fri Apr 7 10:20:45 2017

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


Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

SQL> create table t_xifenfei as select * from dba_objects;

Table created.

SQL> insert into t_xifenfei select * from t_xifenfei;

217838 rows created.

SQL> /

435676 rows created.

SQL> /

871352 rows created.

SQL> /

1742704 rows created.

SQL> commit;

Commit complete.

SQL> select count(*) from t_xifenfei;

  COUNT(*)
----------
   3485408

导出测试表

[oracle@localhost ~]$ expdp xff/oracle@localhost/pdb dumpfile=t_xifenfei.dmp tables=t_xifenfei REUSE_DUMPFILES=yes

Export: Release 12.2.0.1.0 - Production on Fri Apr 7 11:55:01 2017

Copyright (c) 1982, 2017, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
Starting "XFF"."SYS_EXPORT_TABLE_01":  xff/********@localhost/pdb dumpfile=t_xifenfei.dmp tables=t_xifenfei REUSE_DUMPFILES=yes 
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER
Processing object type TABLE_EXPORT/TABLE/TABLE
. . exported "XFF"."T_XIFENFEI"                          460.6 MB 3485408 rows
Master table "XFF"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for XFF.SYS_EXPORT_TABLE_01 is:
  /u01/app/oracle/admin/xffdb/dpdump/4A93528C587D82CEE055000000000001/t_xifenfei.dmp
Job "XFF"."SYS_EXPORT_TABLE_01" successfully completed at Fri Apr 7 11:55:59 2017 elapsed 0 00:00:58

归档模式下不使用DISABLE_ARCHIVE_LOGGING导入

[oracle@localhost rdbms]$ sqlplus / as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on Sat Apr 8 02:43:23 2017

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


Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

SQL> select force_logging from v$database;

FORCE_LOGGING
---------------------------------------
NO

SQL> alter system switch logfile;

System altered.

SQL> archive log list;
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     14
Next log sequence to archive   16
Current log sequence           16

[oracle@localhost ~]$  impdp xff/oracle@localhost/pdb dumpfile=t_xifenfei.dmp

Import: Release 12.2.0.1.0 - Production on Sat Apr 8 02:46:05 2017

Copyright (c) 1982, 2017, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
Master table "XFF"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "XFF"."SYS_IMPORT_FULL_01":  xff/********@localhost/pdb dumpfile=t_xifenfei.dmp 
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported "XFF"."T_XIFENFEI"                          460.6 MB 3485408 rows
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER
Job "XFF"."SYS_IMPORT_FULL_01" successfully completed at Sat Apr 8 02:47:08 2017 elapsed 0 00:01:02

[oracle@localhost ~]$ sqlplus / as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on Sat Apr 8 02:47:30 2017

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


Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

SQL> archive log list;
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     17
Next log sequence to archive   19
Current log sequence           19

这里可以看出来,导入过程使用时间为1分钟多,导入过程日志切换 了3次

归档模式下使用DISABLE_ARCHIVE_LOGGING导入

[oracle@localhost ~]$ sqlplus xff/oracle@localhost/pdb

SQL*Plus: Release 12.2.0.1.0 Production on Sat Apr 8 02:49:23 2017

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

Last Successful login time: Sat Apr 08 2017 02:46:05 -04:00

Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

SQL> drop table t_xifenfei purge;

Table dropped.
SQL> exit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
[oracle@localhost ~]$ sqlplus / as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on Sat Apr 8 02:50:00 2017

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


Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

SQL> alter system switch logfile;

System altered.

SQL> archive log list;
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     18
Next log sequence to archive   20
Current log sequence           20


[oracle@localhost ~]$  impdp xff/oracle@localhost/pdb dumpfile=t_xifenfei.dmp TRANSFORM=DISABLE_ARCHIVE_LOGGING:y

Import: Release 12.2.0.1.0 - Production on Sat Apr 8 02:54:49 2017

Copyright (c) 1982, 2017, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
Master table "XFF"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "XFF"."SYS_IMPORT_FULL_01":  xff/********@localhost/pdb dumpfile=t_xifenfei.dmp TRANSFORM=DISABLE_ARCHIVE_LOGGING:y 
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported "XFF"."T_XIFENFEI"                          460.6 MB 3485408 rows
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER
Job "XFF"."SYS_IMPORT_FULL_01" successfully completed at Sat Apr 8 02:55:00 2017 elapsed 0 00:00:10
[oracle@localhost ~]$ sqlplus / as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on Sat Apr 8 02:55:45 2017

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


Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

SQL> archive log list;
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     18
Next log sequence to archive   20
Current log sequence           20

这里可以看出来当使用了DISABLE_ARCHIVE_LOGGING为Y之后导入日志没有发生切换,导入时间仅为10s.

非归档模式下不使用DISABLE_ARCHIVE_LOGGING导入

SQL> alter system switch logfile;

System altered.

SQL> archive log list;
Database log mode              No Archive Mode
Automatic archival             Disabled
Archive destination            USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     19
Current log sequence           21
SQL> drop table xff.t_xifenfei purge;

Table dropped.

[oracle@localhost ~]$  impdp xff/oracle@localhost/pdb dumpfile=t_xifenfei.dmp

Import: Release 12.2.0.1.0 - Production on Sat Apr 8 03:22:42 2017

Copyright (c) 1982, 2017, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
Master table "XFF"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "XFF"."SYS_IMPORT_FULL_01":  xff/********@localhost/pdb dumpfile=t_xifenfei.dmp 
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported "XFF"."T_XIFENFEI"                          460.6 MB 3485408 rows
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER
Job "XFF"."SYS_IMPORT_FULL_01" successfully completed at Sat Apr 8 03:23:17 2017 elapsed 0 00:00:27

[oracle@localhost ~]$ sqlplus / as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on Sat Apr 8 03:23:49 2017

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


Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

SQL> archive log list;
Database log mode              No Archive Mode
Automatic archival             Disabled
Archive destination            USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     19
Current log sequence           21

这里测试在非归档模式下不设置DISABLE_ARCHIVE_LOGGING，日志量增加不明显，导入时间变为为27秒.

非归档模式下使用DISABLE_ARCHIVE_LOGGING导入

[oracle@localhost ~]$ sqlplus xff/oracle@localhost/pdb

SQL*Plus: Release 12.2.0.1.0 Production on Sat Apr 8 03:24:10 2017

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

Last Successful login time: Sat Apr 08 2017 03:22:43 -04:00

Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

SQL> drop table t_xifenfei purge;

Table dropped.

SQL> conn / as sysdba
Connected.
SQL> alter system switch logfile;

System altered.

SQL> archive log list;
Database log mode              No Archive Mode
Automatic archival             Disabled
Archive destination            USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     20
Current log sequence           22

[oracle@localhost ~]$  impdp xff/oracle@localhost/pdb dumpfile=t_xifenfei.dmp TRANSFORM=DISABLE_ARCHIVE_LOGGING:y

Import: Release 12.2.0.1.0 - Production on Sat Apr 8 03:25:51 2017

Copyright (c) 1982, 2017, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
Master table "XFF"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "XFF"."SYS_IMPORT_FULL_01":  xff/********@localhost/pdb dumpfile=t_xifenfei.dmp TRANSFORM=DISABLE_ARCHIVE_LOGGING:y 
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported "XFF"."T_XIFENFEI"                          460.6 MB 3485408 rows
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER
Job "XFF"."SYS_IMPORT_FULL_01" successfully completed at Sat Apr 8 03:26:01 2017 elapsed 0 00:00:10
[oracle@localhost ~]$ sqlplus / as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on Sat Apr 8 03:26:37 2017

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


Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

SQL> archive log list;
Database log mode              No Archive Mode
Automatic archival             Disabled
Archive destination            USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     20
Current log sequence           22

这里可以看出来在非归档模式下使用DISABLE_ARCHIVE_LOGGING导入时间为10s,日志量也没有明显增加。

在force logging在非归档情况下使用不DISABLE_ARCHIVE_LOGGING参数

[oracle@localhost ~]$ sqlplus xff/oracle@localhost/pdb

SQL*Plus: Release 12.2.0.1.0 Production on Sat Apr 8 10:07:07 2017

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

Last Successful login time: Sat Apr 08 2017 03:29:36 -04:00

Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

SQL> drop table t_xifenfei purge;

Table dropped.
SQL> alter system switch logfile;

System altered.

SQL> archive log list;
Database log mode              No Archive Mode
Automatic archival             Disabled
Archive destination            USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     22
Current log sequence           24
SQL> select force_logging from v$database;

FORCE_LOGGING
---------------------------------------
YES

[oracle@localhost ~]$ impdp xff/oracle@localhost/pdb dumpfile=t_xifenfei.dmp TRANSFORM=DISABLE_ARCHIVE_LOGGING:y

Import: Release 12.2.0.1.0 - Production on Sat Apr 8 10:10:39 2017

Copyright (c) 1982, 2017, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
Master table "XFF"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "XFF"."SYS_IMPORT_FULL_01":  xff/********@localhost/pdb dumpfile=t_xifenfei.dmp TRANSFORM=DISABLE_ARCHIVE_LOGGING:y 
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported "XFF"."T_XIFENFEI"                          460.6 MB 3485408 rows
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER
Job "XFF"."SYS_IMPORT_FULL_01" successfully completed at Sat Apr 8 10:11:02 2017 elapsed 0 00:00:21

[oracle@localhost ~]$ sqlplus / as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on Sat Apr 8 10:11:17 2017

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


Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

SQL> archive log lsit;
SP2-0718: illegal ARCHIVE LOG option
SQL> archive log list;
Database log mode              No Archive Mode
Automatic archival             Disabled
Archive destination            USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     22
Current log sequence           24

这可以可以看出来在非归档情况下force logging无明显增加日志量和导入时间

在force logging在归档情况下使用DISABLE_ARCHIVE_LOGGING参数

SQL> drop table xff.t_xifenfei purge;

Table dropped.

SQL> alter system switch logfile;

System altered.

SQL> select force_logging from v$database;

FORCE_LOGGING
---------------------------------------
YES

SQL> archive log list;
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     23
Next log sequence to archive   25
Current log sequence           25

[oracle@localhost ~]$ impdp xff/oracle@localhost/pdb dumpfile=t_xifenfei.dmp TRANSFORM=DISABLE_ARCHIVE_LOGGING:y

Import: Release 12.2.0.1.0 - Production on Sat Apr 8 10:33:28 2017

Copyright (c) 1982, 2017, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
Master table "XFF"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "XFF"."SYS_IMPORT_FULL_01":  xff/********@localhost/pdb dumpfile=t_xifenfei.dmp TRANSFORM=DISABLE_ARCHIVE_LOGGING:y 
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported "XFF"."T_XIFENFEI"                          460.6 MB 3485408 rows
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER
Job "XFF"."SYS_IMPORT_FULL_01" successfully completed at Sat Apr 8 10:34:50 2017 elapsed 0 00:01:15
[oracle@localhost ~]$ sqlplus / as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on Sat Apr 8 10:35:09 2017

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


Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

SQL> archive log list;
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     26
Next log sequence to archive   28
Current log sequence           28

这里可以看出来在force logging情况下，设置DISABLE_ARCHIVE_LOGGING参数不生效
从上述测试在不管是非归档还是归档情况下使用DISABLE_ARCHIVE_LOGGING都会减小导入时间，减少归档量，但是需要注意如果数据库是force logging情况下，DISABLE_ARCHIVE_LOGGING参数会无效。

发表在 逻辑备份/恢复 | 标签为 | 评论关闭

12c官方不支持裸设备,功能上依旧支持

发表于 2017 年 4 月 4 日 由 惜分飞

根据官方描述:Announcement of DeSupport of using RAW devices in Oracle Database Version 12.1 (Doc ID 578455.1),从12.1开始oracle 彻底放弃了数据文件对raw的支持,但是我测试结果证明依旧可以使用（本测试只是证明功能上可以使用,不能说官方支持,稳定性和遭遇bug情况未知）,在条件允许情况下,不建议使用该方式.
block设备测试

[root@localhost ~]# chown oracle:oinstall /dev/sdf
[root@localhost ~]# ls -l /dev/sdf
brw-rw---- 1 oracle oinstall 8, 80 Apr  2 19:28 /dev/sdf

[oracle@localhost ~]$ sqlplus / as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on Sun Apr 2 19:34:22 2017

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


Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

SQL> select * from v$version;

BANNER
--------------------------------------------------------------------------------
    CON_ID
----------
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
         0

PL/SQL Release 12.2.0.1.0 - Production
         0

CORE    12.2.0.1.0      Production
         0

TNS for Linux: Version 12.2.0.1.0 - Production
         0

NLSRTL Version 12.2.0.1.0 - Production
         0

SQL> select name from v$datafile;

NAME
--------------------------------------------------------------------------------
+DATA/XFFDB/DATAFILE/system.257.938465955
+DATA/XFFDB/DATAFILE/sysaux.258.938465989
+DATA/XFFDB/DATAFILE/undotbs1.259.938466005
+DATA/XFFDB/4700A987085B3DFAE05387E5E50A8C7B/DATAFILE/system.271.938466083
+DATA/XFFDB/4700A987085B3DFAE05387E5E50A8C7B/DATAFILE/sysaux.270.938466083
+DATA/XFFDB/DATAFILE/users.260.938466007
+DATA/XFFDB/4700A987085B3DFAE05387E5E50A8C7B/DATAFILE/undotbs1.272.938466083
+DATA/XFFDB/4A93528C587D82CEE055000000000001/DATAFILE/system.276.938466401
+DATA/XFFDB/4A93528C587D82CEE055000000000001/DATAFILE/sysaux.277.938466401
+DATA/XFFDB/4A93528C587D82CEE055000000000001/DATAFILE/undotbs1.275.938466399
+DATA/XFFDB/4A93528C587D82CEE055000000000001/DATAFILE/users.279.938466427

11 rows selected.

SQL> create tablespace test datafile '/dev/sdf' size 10M autoextend on;

Tablespace created.

SQL> select name from v$datafile;

NAME
--------------------------------------------------------------------------------
+DATA/XFFDB/DATAFILE/system.257.938465955
+DATA/XFFDB/DATAFILE/sysaux.258.938465989
+DATA/XFFDB/DATAFILE/undotbs1.259.938466005
+DATA/XFFDB/4700A987085B3DFAE05387E5E50A8C7B/DATAFILE/system.271.938466083
+DATA/XFFDB/4700A987085B3DFAE05387E5E50A8C7B/DATAFILE/sysaux.270.938466083
+DATA/XFFDB/DATAFILE/users.260.938466007
+DATA/XFFDB/4700A987085B3DFAE05387E5E50A8C7B/DATAFILE/undotbs1.272.938466083
+DATA/XFFDB/4A93528C587D82CEE055000000000001/DATAFILE/system.276.938466401
+DATA/XFFDB/4A93528C587D82CEE055000000000001/DATAFILE/sysaux.277.938466401
+DATA/XFFDB/4A93528C587D82CEE055000000000001/DATAFILE/undotbs1.275.938466399
+DATA/XFFDB/4A93528C587D82CEE055000000000001/DATAFILE/users.279.938466427
/dev/sdf

12 rows selected.

SQL>  select file# from v$datafile where name like '%sdf%';

     FILE#
----------
        13

SQL> select rfile#,file#,name from v$datafile where file#=13;

    RFILE#      FILE# NAME
---------- ---------- ------------------------------
        13         13 /dev/sdf


SQL> create table t_xifenfei tablespace test as 
  2  select * from dba_objects;

Table created.

SQL> select count(*) from t_xifenfei;

  COUNT(*)
----------
     72660

SQL> select file_id,extent_id,blocks from dba_extents where segment_name='T_XIFENFEI';

   FILE_ID  EXTENT_ID     BLOCKS
---------- ---------- ----------
        13          0          8
        13          1          8
        13          2          8
        13          3          8
        13          4          8
        13          5          8
        13          6          8
        13          7          8
        13          8          8
        13          9          8
        13         10          8
        13         11          8
        13         12          8
        13         13          8
        13         14          8
        13         15          8
        13         16        128
        13         17        128
        13         18        128
        13         19        128
        13         20        128
        13         21        128
        13         22        128
        13         23        128
        13         24        128
        13         25        128
        13         26        128

27 rows selected.


SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area 2432696320 bytes
Fixed Size                  8795664 bytes
Variable Size             654313968 bytes
Database Buffers         1761607680 bytes
Redo Buffers                7979008 bytes
Database mounted.
Database opened.
SQL> select count(*) from t_xifenfei;

  COUNT(*)
----------
     72660

SQL> 
SQL> set pages 100
SQL> select name from v$datafile;

NAME
--------------------------------------------------------------------------------
+DATA/XFFDB/DATAFILE/system.257.938465955
+DATA/XFFDB/DATAFILE/sysaux.258.938465989
+DATA/XFFDB/DATAFILE/undotbs1.259.938466005
+DATA/XFFDB/4700A987085B3DFAE05387E5E50A8C7B/DATAFILE/system.271.938466083
+DATA/XFFDB/4700A987085B3DFAE05387E5E50A8C7B/DATAFILE/sysaux.270.938466083
+DATA/XFFDB/DATAFILE/users.260.938466007
+DATA/XFFDB/4700A987085B3DFAE05387E5E50A8C7B/DATAFILE/undotbs1.272.938466083
+DATA/XFFDB/4A93528C587D82CEE055000000000001/DATAFILE/system.276.938466401
+DATA/XFFDB/4A93528C587D82CEE055000000000001/DATAFILE/sysaux.277.938466401
+DATA/XFFDB/4A93528C587D82CEE055000000000001/DATAFILE/undotbs1.275.938466399
+DATA/XFFDB/4A93528C587D82CEE055000000000001/DATAFILE/users.279.938466427
/dev/sdf

12 rows selected.

lvm设备测试

[root@localhost ~]# pvcreate /dev/sdf
  Physical volume "/dev/sdf" successfully created.
[root@localhost ~]# 
[root@localhost ~]# 
[root@localhost ~]# vgcreate vgxff /dev/sdf
  Volume group "vgxff" successfully created
[root@localhost ~]# lvcreate -L 512M -n data01 vgxff
  Logical volume "data01" created.
[root@localhost ~]# lvcreate -L 512M -n data02 vgxff
  Logical volume "data02" created.
[root@localhost ~]# lvcreate -L 512M -n data03 vgxff
  Logical volume "data03" created.
[root@localhost ~]# lvdisplay vgxff
  --- Logical volume ---
  LV Path                /dev/vgxff/data01
  LV Name                data01
  VG Name                vgxff
  LV UUID                c9PowB-11OX-sjcF-fZLe-5vha-xV4x-ITRflH
  LV Write Access        read/write
  LV Creation host, time localhost.localdomain, 2017-04-02 19:46:17 -0400
  LV Status              available
  # open                 0
  LV Size                512.00 MiB
  Current LE             128
  Segments               1
  Allocation             inherit
  Read ahead sectors     auto
  - currently set to     8192
  Block device           251:2
   
  --- Logical volume ---
  LV Path                /dev/vgxff/data02
  LV Name                data02
  VG Name                vgxff
  LV UUID                vvMbWB-tale-twTH-hg4k-Kcwh-3I8e-neOnEk
  LV Write Access        read/write
  LV Creation host, time localhost.localdomain, 2017-04-02 19:46:21 -0400
  LV Status              available
  # open                 0
  LV Size                512.00 MiB
  Current LE             128
  Segments               1
  Allocation             inherit
  Read ahead sectors     auto
  - currently set to     8192
  Block device           251:3
   
  --- Logical volume ---
  LV Path                /dev/vgxff/data03
  LV Name                data03
  VG Name                vgxff
  LV UUID                DDz3M1-cVX0-NAJb-3rlK-DkYt-eSQG-xna8ew
  LV Write Access        read/write
  LV Creation host, time localhost.localdomain, 2017-04-02 19:46:25 -0400
  LV Status              available
  # open                 0
  LV Size                512.00 MiB
  Current LE             128
  Segments               1
  Allocation             inherit
  Read ahead sectors     auto
  - currently set to     8192
  Block device           251:4

[root@localhost ~]# cd /dev/vgxff/
[root@localhost vgxff]# ls -ltr
total 0
lrwxrwxrwx 1 root root 7 Apr  2 19:46 data01 -> ../dm-2
lrwxrwxrwx 1 root root 7 Apr  2 19:46 data02 -> ../dm-3
lrwxrwxrwx 1 root root 7 Apr  2 19:46 data03 -> ../dm-4
[root@localhost vgxff]# cd /dev/mapper/
[root@localhost mapper]# ls -ltr
total 0
crw------- 1 root root 10, 236 Apr  2 19:28 control
lrwxrwxrwx 1 root root       7 Apr  2 19:28 ol-swap -> ../dm-1
lrwxrwxrwx 1 root root       7 Apr  2 19:28 ol-root -> ../dm-0
lrwxrwxrwx 1 root root       7 Apr  2 19:46 vgxff-data01 -> ../dm-2
lrwxrwxrwx 1 root root       7 Apr  2 19:46 vgxff-data02 -> ../dm-3
lrwxrwxrwx 1 root root       7 Apr  2 19:46 vgxff-data03 -> ../dm-4
[root@localhost mapper]# ls -l /dev/dm*
brw-rw---- 1 root disk 251, 0 Apr  2 19:28 /dev/dm-0
brw-rw---- 1 root disk 251, 1 Apr  2 19:28 /dev/dm-1
brw-rw---- 1 root disk 251, 2 Apr  2 19:46 /dev/dm-2
brw-rw---- 1 root disk 251, 3 Apr  2 19:46 /dev/dm-3
brw-rw---- 1 root disk 251, 4 Apr  2 19:46 /dev/dm-4
[root@localhost mapper]# chown oracle:oinstall /dev/dm-[2-4]
[root@localhost mapper]# ls -l /dev/dm*
brw-rw---- 1 root   disk     251, 0 Apr  2 19:28 /dev/dm-0
brw-rw---- 1 root   disk     251, 1 Apr  2 19:28 /dev/dm-1
brw-rw---- 1 oracle oinstall 251, 2 Apr  2 19:46 /dev/dm-2
brw-rw---- 1 oracle oinstall 251, 3 Apr  2 19:46 /dev/dm-3
brw-rw---- 1 oracle oinstall 251, 4 Apr  2 19:46 /dev/dm-4
[root@localhost mapper]# 

[oracle@localhost ~]$ sqlplus / as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on Sun Apr 2 19:50:47 2017

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


Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

SQL> create tablespace lvtbs datafile '/dev/mapper/vgxff-data01' size 450M;

Tablespace created.

SQL> alter tablespace lvtbs add datafile '/dev/mapper/vgxff-data02' size 450M;

Tablespace altered.

SQL> alter tablespace lvtbs add datafile '/dev/mapper/vgxff-data03' size 450M;

Tablespace altered.

SQL> select rfile#,file#,name from v$datafile where name like '%mapper%';

    RFILE#      FILE# NAME
---------- ---------- --------------------------------------------------
        14         14 /dev/mapper/vgxff-data01
        15         15 /dev/mapper/vgxff-data02
        16         16 /dev/mapper/vgxff-data03

SQL> create table lv12c tablespace lvtbs as
  2  select * from dba_objects;

Table created.

SQL> select count(*) from lv12c;

  COUNT(*)
----------
     72660

SQL> select file_id,extent_id,blocks from dba_extents where segment_name='LV12C';

   FILE_ID  EXTENT_ID     BLOCKS
---------- ---------- ----------
        14          0          8
        14          1          8
        14          2          8
        14          3          8
        14          4          8
        14          5          8
        14          6          8
        14          7          8
        14          8          8
        14          9          8
        14         10          8
        14         11          8
        14         12          8
        14         13          8
        14         14          8
        14         15          8
        14         18        128
        14         21        128
        14         24        128
        15         17        128
        15         20        128
        15         23        128
        15         26        128
        16         16        128
        16         19        128
        16         22        128
        16         25        128

27 rows selected.

SQL> SHUTDOWN IMMEDIATE;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area 2432696320 bytes
Fixed Size                  8795664 bytes
Variable Size             654313968 bytes
Database Buffers         1761607680 bytes
Redo Buffers                7979008 bytes
Database mounted.
Database opened.
SQL>  select count(*) from lv12c;

  COUNT(*)
----------
     72660

raw设备测试

[root@localhost ~]# raw /dev/raw/raw1 /dev/sdf
/dev/raw/raw1:  bound to major 8, minor 80
[root@localhost ~]# ls -l /dev/raw/raw1
crw-rw---- 1 root disk 162, 1 Apr  4 04:00 /dev/raw/raw1
[root@localhost ~]# chown oracle:oinstall /dev/raw/raw1
[root@localhost ~]# ls -l /dev/raw/raw1
crw-rw---- 1 oracle oinstall 162, 1 Apr  4 04:00 /dev/raw/raw1
[oracle@localhost ~]$ sqlplus / as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on Tue Apr 4 04:01:42 2017

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


Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

SQL> create tablespace rawtbs datafile '/dev/raw/raw1' size 128M autoextend on;

Tablespace created.

SQL>  select rfile#,file#,name from v$datafile where name like '%raw%';

    RFILE#      FILE#
---------- ----------
NAME
--------------------------------------------------------------------------------
        17         17
/dev/raw/raw1

SQL>  create table t_xifenfei_raw tablespace rawtbs
  2  as select * from dba_objects;

Table created.

SQL> select count(*) from t_xifenfei_raw ;

  COUNT(*)
----------
     72660

SQL> select file_id,extent_id,blocks from dba_extents where segment_name='T_XIFENFEI_RAW';

   FILE_ID  EXTENT_ID     BLOCKS
---------- ---------- ----------
        17          0          8
        17          1          8
        17          2          8
        17          3          8
        17          4          8
        17          5          8
        17          6          8
        17          7          8
        17          8          8
        17          9          8
        17         10          8
        17         11          8
        17         12          8
        17         13          8
        17         14          8
        17         15          8
        17         16        128
        17         17        128
        17         18        128
        17         19        128
        17         20        128
        17         21        128
        17         22        128
        17         23        128
        17         24        128
        17         25        128
        17         26        128

27 rows selected.

从这里这里测试结果看,对于linux的block/raw/lvm设备依旧均可以在功能上做数据文件使用(稳定性和是否有其他bug未知).但没有出现mos中描述的直接报错,在条件允许的情况下,尽可能不要使用直接使用裸设备方式.
在12.2的administrator文档中出现明显描述：
12C-RAW2
Direct use of raw or block devices is not supported. You can only use raw or block devices under Oracle ASM.

发表在 Oracle | 标签为 , | 评论关闭

crfclust.bdb文件过大处理

发表于 2017 年 3 月 31 日 由 惜分飞

grid所在目录空间满

[root@wldb01 ~]# df -h
Filesystem            Size  Used Avail Use% Mounted on
/dev/sdw2              40G   22G   17G  58% /
tmpfs                  16G  219M   16G   2% /dev/shm
/dev/sdw1              50G   46G  1.5G  97% /u01

使用find命令找出来大文件

[root@wldb01 grid]# find ./ type f -size +1024M
./crf/db/wldb01/crfclust.bdb

由于文件crfclust.bdb是Cluster Health Monitor (CHM) file,他的默认大小是1G,但是有在一些平台和版本中由于bug原因导致过大.
Oracle Cluster Health Monitor (CHM) using large amount of space (more than default) (Doc ID 1343105.1)
Bug 20186278 – crfclust.bdb Becomes Huge Size Due to Sudden Retention Change (Doc ID 20186278.8)

[grid@wldb01 ~]$ /u01/app/11.2.0/grid/bin/oclumon manage -get reppath

CHM Repository Path = /u01/app/11.2.0/grid/crf/db/wldb01

 Done 

[root@wldb01 ~]# cd  /u01/app/11.2.0/grid/crf/db/wldb01
[root@wldb01 wldb01]# du -sh
24G     .
[root@wldb01 wldb01]# ls -lhtr
total 24G
-rw-r-----. 1 root root  16M Mar 25 21:38 log.0000047847
-rw-r-----. 1 root root 8.0K Mar 25 21:38 repdhosts.bdb
-rw-r-----. 1 root root  24K Mar 25 21:39 __db.001
-rw-r--r--. 1 root root 115M Mar 25 21:39 wldb01.ldb
-rw-r-----. 1 root root 8.0K Mar 25 21:40 crfconn.bdb
-rw-r-----. 1 root root 329M Mar 25 21:52 crfts.bdb
-rw-r-----. 1 root root 508M Mar 25 21:53 crfloclts.bdb
-rw-r-----. 1 root root  22G Mar 25 21:53 crfclust.bdb
-rw-r-----. 1 root root 392K Mar 25 21:53 __db.002
-rw-r-----. 1 root root  16M Mar 25 21:53 log.0000047848
-rw-r-----. 1 root root 504M Mar 25 21:53 crfhosts.bdb
-rw-r-----. 1 root root 650M Mar 25 21:53 crfcpu.bdb
-rw-r-----. 1 root root 534M Mar 25 21:53 crfalert.bdb
-rw-r-----. 1 root root  56K Mar 25 21:53 __db.006
-rw-r-----. 1 root root 1.2M Mar 25 21:53 __db.005
-rw-r-----. 1 root root 2.1M Mar 25 21:53 __db.004
-rw-r-----. 1 root root 2.6M Mar 25 21:53 __db.003

清理bdb文件

[root@wldb01 wldb01]# /u01/app/11.2.0/grid/bin/crsctl stop res ora.crf -init
CRS-2673: Attempting to stop 'ora.crf' on 'wldb01'
CRS-2677: Stop of 'ora.crf' on 'wldb01' succeeded
[root@wldb01 wldb01]# /u01/app/11.2.0/grid/bin/crsctl status res -t -init
--------------------------------------------------------------------------------
NAME           TARGET  STATE        SERVER                   STATE_DETAILS       
--------------------------------------------------------------------------------
Cluster Resources
--------------------------------------------------------------------------------
ora.asm
      1        ONLINE  ONLINE       wldb01                   Started             
ora.cluster_interconnect.haip
      1        ONLINE  ONLINE       wldb01                                       
ora.crf
      1        OFFLINE OFFLINE                                                   
ora.crsd
      1        ONLINE  ONLINE       wldb01                                       
ora.cssd
      1        ONLINE  ONLINE       wldb01                                       
ora.cssdmonitor
      1        ONLINE  ONLINE       wldb01                                       
ora.ctssd
      1        ONLINE  ONLINE       wldb01                   ACTIVE:0            
ora.diskmon
      1        OFFLINE OFFLINE                                                   
ora.drivers.acfs
      1        ONLINE  ONLINE       wldb01                                       
ora.evmd
      1        ONLINE  ONLINE       wldb01                                       
ora.gipcd
      1        ONLINE  ONLINE       wldb01                                       
ora.gpnpd
      1        ONLINE  ONLINE       wldb01                                       
ora.mdnsd
      1        ONLINE  ONLINE       wldb01                                       
[root@wldb01 wldb01]# rm -rf *.bdb
[root@wldb01 wldb01]# df -h
Filesystem            Size  Used Avail Use% Mounted on
/dev/sdw2              40G   22G   17G  58% /
tmpfs                  16G  219M   16G   2% /dev/shm
/dev/sdw1              50G   22G   26G  46% /u01
[root@wldb01 wldb01]# du -sh
53M     .
[root@wldb01 wldb01]# /u01/app/11.2.0/grid/bin/crsctl start res ora.crf -init
CRS-2672: Attempting to start 'ora.crf' on 'wldb01'
CRS-2676: Start of 'ora.crf' on 'wldb01' succeeded
[root@wldb01 wldb01]# /u01/app/11.2.0/grid/bin/crsctl status res -t -init
--------------------------------------------------------------------------------
NAME           TARGET  STATE        SERVER                   STATE_DETAILS       
--------------------------------------------------------------------------------
Cluster Resources
--------------------------------------------------------------------------------
ora.asm
      1        ONLINE  ONLINE       wldb01                   Started             
ora.cluster_interconnect.haip
      1        ONLINE  ONLINE       wldb01                                       
ora.crf
      1        ONLINE  ONLINE       wldb01                                       
ora.crsd
      1        ONLINE  ONLINE       wldb01                                       
ora.cssd
      1        ONLINE  ONLINE       wldb01                                       
ora.cssdmonitor
      1        ONLINE  ONLINE       wldb01                                       
ora.ctssd
      1        ONLINE  ONLINE       wldb01                   ACTIVE:0            
ora.diskmon
      1        OFFLINE OFFLINE                                                   
ora.drivers.acfs
      1        ONLINE  ONLINE       wldb01                                       
ora.evmd
      1        ONLINE  ONLINE       wldb01                                       
ora.gipcd
      1        ONLINE  ONLINE       wldb01                                       
ora.gpnpd
      1        ONLINE  ONLINE       wldb01                                       
ora.mdnsd
      1        ONLINE  ONLINE       wldb01
发表在 Oracle RAC | 标签为 | 评论关闭