将表的日志属性设置为nologging将会为直接路径运算生成最少的重做日志,对于正常的DML运算并不生效。什么样的操作才算是直接路径运算呢?请看下面的几个例子:
1. 插入数据时使用hint,如insert /*+ append */
2. 使用valus字句的查询中加入hint, 如insert /*+ append_values */
3. 使用create table .. as select 方式创建表
下面做个演示
1. 首先用常规方法创建一个表emp并修改为nologging模式
[oracle@snow ~]$ sqlplus u1/u1@pdb1
SQL*Plus: Release 12.1.0.1.0 Production on Mon May 4 20:20:28 2015
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Last Successful login time: Mon May 04 2015 20:10:17 -04: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
U1@pdb1 > create table emp(username varchar2(50));
Table created.
U1@pdb1 > col table_name for a10
U1@pdb1 > select table_name,logging from tabs where table_name='EMP';
TABLE_NAME LOG
---------- ---
EMP YES
U1@pdb1 > alter table emp nologging;
Table altered.
U1@pdb1 > select table_name,logging from tabs where table_name='EMP';
TABLE_NAME LOG
---------- ---
EMP NO
2. 使用直接路径加载的方式插入数据,注意/*+append */
U1@pdb1 > insert /*+append */ into emp(username) select last_name from hr.employees;
107 rows created.
U1@pdb1 > commit;
Commit complete.
3. 确认emp表所属的用户,所在数据文件
U1@pdb1 > col tablespace_name for a10
U1@pdb1 > col file_name for a60
U1@pdb1 > col owner for a5
U1@pdb1 > set line 100
U1@pdb1 > select t.owner,t.table_name,t.tablespace_name,t.logging,f.file_name
2 from dba_tables t, dba_data_files f
3 where t.tablespace_name=f.tablespace_name
4 and t.tablespace_name='USERS'
5 and t.table_name='EMP'
6 and t.owner='U1';
OWNER TABLE_NAME TABLESPACE LOG FILE_NAME
----- ---------- ---------- --- ------------------------------------------------------------
U1 EMP USERS NO /home/oracle/dbfile/cdb/pdb1/SAMPLE_SCHEMA_users01.dbf
4. 在rman中使用report unrecoverable会查询出不被保护的数据文件,就是我们刚才查询的结果。可以看出来,nologging+append的方式背后是一个安全隐患。为了避免这个隐患扩大可以为该数据文件做一个备份。
[oracle@snow ~]$ rman target u1/u1
Recovery Manager: Release 12.1.0.1.0 - Production on Mon May 4 20:22:13 2015
Copyright (c) 1982, 2013, Oracle and/or its affiliates. All rights reserved.
connected to target database: CDB (DBID=1995215983)
RMAN> report unrecoverable;
Report of files that need backup due to unrecoverable operations
File Type of Backup Required Name
---- ----------------------- -----------------------------------
10 full or incremental /home/oracle/dbfile/cdb/pdb1/SAMPLE_SCHEMA_users01.dbf
RMAN> backup datafile 10;
Starting backup at 04-MAY-15
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=33 device type=DISK
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00010 name=/home/oracle/dbfile/cdb/pdb1/SAMPLE_SCHEMA_users01.dbf
channel ORA_DISK_1: starting piece 1 at 04-MAY-15
channel ORA_DISK_1: finished piece 1 at 04-MAY-15
piece handle=/u01/app/oracle/product/12.1.0.1/db_1/dbs/3jq64aou_1_1 tag=TAG20150504T203958 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 04-MAY-15
Starting Control File and SPFILE Autobackup at 04-MAY-15
piece handle=/u01/app/oracle/product/12.1.0.1/db_1/dbs/c-1995215983-20150504-0b comment=NONE
Finished Control File and SPFILE Autobackup at 04-MAY-15
RMAN> report unrecoverable; <==备份过一次后,再次report就不会有记录了。也就是说安全了!
Report of files that need backup due to unrecoverable operations
File Type of Backup Required Name
---- ----------------------- -----------------------------------
nologging与数据同步工具:
nologging与数据同步工具是一对相斥的对象,如=DataGuard,Oracle GoldenGate都不例外。nologging为了性能的提升尽可能的少产生redo,甚至变成一个unrecoverable的状态。数据同步工具恰恰相反,为了捕捉所有数据变化的细节不但要开启force logging,甚至要开启supplemental log(OGG)。 究竟怎样选择是一个取舍问题,看业务所需了。