Benefits and consequences of the NOLOGGING option

Oracle 2013. 11. 1. 19:49

I still find confusion out there about the LOGGING and NOLOGGING clauses when performing DML and DDL operations, the reality is that the NOLOGGING clause will work only on particular conditions, but all regular inserts,updates and deletes will still log the operations.

The benefits of the NOLOGGING option are:

  • Will save disk space when the archive option is enabled.
  • Will largely reduce I/O on the redologs.
  • Will reduce the time it takes to complete the operation.

Please note that NOLOGGING operations will only reduce -not eliminate- the logging.

Lets see an example -

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
--   First: we create an empty table with the nologging clause
SQL>  create table logging_example nologging as select * from dba_objects where 1=2;
 
Table created.
 
--Now let's enable the statistics and perform a couple of tests:
 
SQL> set autotrace on statistics
SQL> set timing on
 
-- insert the records the traditional way
SQL> alter system flush buffer_cache;  --clean the cache to compare the speeds in equal conditions
 
System altered.
 
Elapsed: 00:00:01.49
 
SQL> insert into logging_example select * from dba_objects;
 
50864 rows created.
 
Elapsed: 00:00:01.59
 
Statistics
----------------------------------------------------------
 0  recursive calls
 5250  db block gets
 6766  consistent gets
 982  physical reads
5636712  redo size --without the APPEND hint
 670  bytes sent via SQL*Net to client
 586  bytes received via SQL*Net from client
 3  SQL*Net roundtrips to/from client
 1  sorts (memory)
 0  sorts (disk)
 50864  rows processed
 
-- insert the records with the APPEND hint (nologging)
SQL> alter system flush buffer_cache;  --clean the cache to compare the speeds in equal conditions
 
System altered.
 
Elapsed: 00:00:01.06
 
SQL> insert /*+ append */  into logging_example select * from dba_objects;
 
50864 rows created.
 
Elapsed: 00:00:00.59
 
Statistics
----------------------------------------------------------
 0  recursive calls
 743  db block gets
 5374  consistent gets
 944  physical reads
2200  redo size --with the APPEND hint
 654  bytes sent via SQL*Net to client
 604  bytes received via SQL*Net from client
 3  SQL*Net roundtrips to/from client
 1  sorts (memory)
 0  sorts (disk)
 50864  rows processed

We can see that there is a big difference on the redo size generated by each insert, there are many post and articles on the internet  that show the speed benefits when using the NOLOGGING option, but here I mainly want to clarify that a regular insert (no APPEND hint) will still generate redologs even if the table have been created with the NOLOGGING option.

What happens to the data after a restore when a nologging operation was performed on it?

I will present some scenarios to show the consequences when we need to perform a restore after a nologging transaction, this way we will know what to expect and we can better prepare ourselves in case of a disaster.

I took a full database backup, now I will create several tables with different options to see what happens after a restore, you might see some surprises here!

Scenarios:

  • Table “create as select” with the nologging option (table_ctas_nologging).
  • Regular table “create as select” (table_ctas_logging)
  • A nologging table created empty, and a regular (logging) insert (table_ctas_nologging_insert)
  • Table created with nologging, then two inserts, one with and one without logging (table_insert_mixed)
  • Regular logging table, with a nologging index (table_ctas_index_nologging)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
SQL> create table table_ctas_nologging nologging as select * from dba_objects;
 
Table created.
 
SQL> create table table_ctas_logging as select * from dba_objects;
 
Table created.
 
SQL> create table table_ctas_nologging_insert nologging as select * from dba_objects where 1=2;
 
Table created.
 
SQL>  insert into table_ctas_nologging_insert  select * from dba_objects;
 
50864 rows created.
 
SQL> commit;
 
Commit complete.
 
SQL> create table table_insert_mixed nologging as select * from dba_objects where 1=2;
 
Table created.
 
SQL> insert into table_insert_mixed select * from dba_objects;
 
50866 rows created.
 
SQL> insert into table_insert_mixed select /*+ append */ * from dba_objects;
 
50866 rows created.
 
SQL> commit;
 
Commit complete.
 
SQL> select count(*) from table_insert_mixed;
 
 COUNT(*)
----------
 101732
 
SQL> create table table_ctas_index_nologging as select * from dba_objects;
 
Table created.
 
SQL> create index IDXNOLOG  on  table_ctas_index_nologging (object_id)  nologging;
 
Index created.

Now I will shutdown the database and restore the tablespace from the backup.
Next is an extract from RMAN

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
rman target /
 
Recovery Manager: Release 10.2.0.4.0 - Production on Wed Aug 25 17:32:20 2010
 
Copyright (c) 1982, 2007, Oracle.  All rights reserved.
 
connected to target database: ORCL (DBID=1247573001)
 
RMAN> shutdown immediate
 
using target database control file instead of recovery catalog
database closed
database dismounted
Oracle instance shut down
 
RMAN> startup mount;
 
Oracle instance started
database mounted
 
Total System Global Area     285212672 bytes
 
Fixed Size                     1267068 bytes
Variable Size                155191940 bytes
Database Buffers             125829120 bytes
Redo Buffers                   2924544 bytes
 
RMAN> restore tablespace users;
 
Starting restore at 25-AUG-10
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=152 devtype=DISK
 
channel ORA_DISK_1: starting datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
restoring datafile 00004 to +DATA/orcl/datafile/users.259.719792191
channel ORA_DISK_1: reading from backup piece +DATA/orcl/backupset/2010_08_25/nnndf0_tag20100825t171657_0.272.727982219
channel ORA_DISK_1: restored backup piece 1
piece handle=+DATA/orcl/backupset/2010_08_25/nnndf0_tag20100825t171657_0.272.727982219 tag=TAG20100825T171657
channel ORA_DISK_1: restore complete, elapsed time: 00:00:05
Finished restore at 25-AUG-10
 
RMAN> recover tablespace users;
 
Starting recover at 25-AUG-10
using channel ORA_DISK_1
 
starting media recovery
media recovery complete, elapsed time: 00:00:05
 
Finished recover at 25-AUG-10
 
RMAN> alter database open;
 
database opened

Now lets see the status of the tables:

1
2
3
4
5
6
7
SQL> select count(*) from table_ctas_nologging ;
 select count(*) from table_ctas_nologging
 *
 ERROR at line 1:
 ORA-01578: ORACLE data block corrupted (file # 4, block # 404)
 ORA-01110: data file 4: '+DATA/orcl/datafile/users.259.719792191'
 ORA-26040: Data block was loaded using the NOLOGGING option

That doesn’t look good, lets see the next table

1
2
3
4
5
SQL> select count(*) from table_ctas_logging ;
 
COUNT(*)
----------
50863

Good, no problem here, the next scenario is more interesting, the table was created with the NOLOGGING option, but the inserts were done without the APPEND hint

1
2
3
4
5
SQL> select count (*) from table_ctas_nologging_insert;
 
COUNT(*)
----------
50864

Good, no problem here, now let’s see our table with half data inserted with logging and half with nologging

1
2
3
4
5
6
7
SQL> select count(*) from table_insert_mixed;
select count(*) from table_insert_mixed
*
ERROR at line 1:
ORA-01578: ORACLE data block corrupted (file # 4, block # 4363)
ORA-01110: data file 4: '+DATA/orcl/datafile/users.259.719792191'
ORA-26040: Data block was loaded using the NOLOGGING option

Wow, the whole table is unredable!

Now lets see the table with the NOLOGGING  index .

1
2
3
4
5
<pre>SQL> select  count(*) from table_ctas_index_nologging;
 
COUNT(*)
----------
50865

Ok, thats nice, the table is accessible, but what happend if we try to use the index?

1
2
3
4
5
6
7
SQL>  select object_id from table_ctas_index_nologging  where object_id=1;
select object_id from table_ctas_index_nologging  where object_id=1
*
ERROR at line 1:
ORA-01578: ORACLE data block corrupted (file # 4, block # 2821)
ORA-01110: data file 4: '+DATA/orcl/datafile/users.259.719792191'
ORA-26040: Data block was loaded using the NOLOGGING option

I tried to rebuil the index but I was still getting the same error message, at the end I was forced to drop it and recreate it.

Conclusions:

  • Use the NOLOGGING option only on temporary/working/staging tables.
  • Always perform a backup after a NOLOGGING operation.
  • Unless explicitly indicated, DDLs like CTAS and DMLs like inserts will log all operations.

FROM ORACLE DOCUMENTATION:

NOLOGGING is supported in only a subset of the locations that support LOGGING. Only the following operations support the NOLOGGING mode:

DML:

  • Direct-path INSERT (serial or parallel) resulting either from an INSERT or aMERGE statement. NOLOGGING is not applicable to any UPDATE operations resulting from the MERGE statement.
  • Direct Loader (SQL*Loader)

DDL:

  • CREATE TABLE … AS SELECT
  • CREATE TABLE … LOB_storage_clause … LOB_parameters … NOCACHE | CACHE READS
  • ALTER TABLE … LOB_storage_clause … LOB_parameters … NOCACHE | CACHE READS(to specify logging of newly created LOB columns)
  • ALTER TABLE … modify_LOB_storage_clause … modify_LOB_parameters … NOCACHE |CACHE READS (to change logging of existing LOB columns)
  • ALTER TABLE … MOVE
  • ALTER TABLE … (all partition operations that involve data movement)
    • ALTER TABLE … ADD PARTITION (hash partition only)
    • ALTER TABLE … MERGE PARTITIONS
    • ALTER TABLE … SPLIT PARTITION
    • ALTER TABLE … MOVE PARTITION
    • ALTER TABLE … MODIFY PARTITION … ADD SUBPARTITION
    • ALTER TABLE … MODIFY PARTITION … COALESCE SUBPARTITION
  • CREATE INDEX
  • ALTER INDEX … REBUILD
  • ALTER INDEX … REBUILD [SUB]PARTITION
  • ALTER INDEX … SPLIT PARTITION

For objects other than LOBs, if you omit this clause, then the logging attribute of the object defaults to the logging attribute of the tablespace in which it resides.

For LOBs, if you omit this clause, then:

  • If you specify CACHE, then LOGGING is used (because you cannot have CACHENOLOGGING).
  • If you specify NOCACHE or CACHE READS, then the logging attribute defaults to the logging attribute of the tablespace in which it resides.

NOLOGGING does not apply to LOBs that are stored internally (in the table with row data). If you specify NOLOGGING for LOBs with values less than 4000 bytes and you have not disabled STORAGE IN ROW, then Oracle ignores the NOLOGGING specification and treats the LOB data the same as other table data.

 

reference : http://bit.ly/1aYFZw3

저작자표시 비영리 동일조건 (새창열림)

'Oracle' 카테고리의 다른 글

Oracle 11g Enterprise Option 내용  (0) 2014.02.12
Oracle Trim Function  (0) 2013.11.15
Certification Information for Oracle Database on Microsoft Windows x64 (64-bit)  (0) 2013.08.29
[Oracle] Current Interoperability Support Situation  (0) 2013.08.29
Oracle Database 12c : Tom’s Top 12 Things About the Latest Generation of Database Technology  (0) 2013.08.21
:     

TISTORY에 Login하려면 여기를 누르세요.


Certification Information for Oracle Database on Microsoft Windows x64 (64-bit)

Oracle 2013. 8. 29. 12:34

Oracle DBMS의 Version별 OS Platform Certification 정보입니다.

 

Oracle 12c는 Windows 2003이 아예 빠져 버렸네요. 아무래도 Windows 2003의 지원 정책 때문인 것으로 사료됩니다.

 

 

12.1

11.2

11.1

10.2

  • Do not install Oracle Database 10g Release 2 on Windows Server 2008, Server 2008 R2, 7, nor Vista using the 10.2.0.1 version. You must use the 10.2.0.4 (64-bit) x64 version specifically created for these operating systems. After installation, for Windows 7 and Windows Server 2008 R2, patchset 10.2.0.5 or higher must be applied.  For more information on installation see the release notes sections on the installation media:

 

출처 : Oracle Support - 문서 ID 1307195.1 발췌

저작자표시 비영리 동일조건 (새창열림)

'Oracle' 카테고리의 다른 글

Oracle Trim Function  (0) 2013.11.15
Benefits and consequences of the NOLOGGING option  (0) 2013.11.01
[Oracle] Current Interoperability Support Situation  (0) 2013.08.29
Oracle Database 12c : Tom’s Top 12 Things About the Latest Generation of Database Technology  (0) 2013.08.21
A Comparison of Oracle's DATE and TIMESTAMP Datatypes  (0) 2013.04.09
:     

TISTORY에 Login하려면 여기를 누르세요.


[Oracle] Current Interoperability Support Situation

Oracle 2013. 8. 29. 12:28

Current Interoperability Support Situation

The matrix below summarizes client and server combinations that are supported for the most commonly used product versions. A full matrix appears at the end of this document.

New interoperability problems will only be investigated if BOTH releases involved are covered by a valid support contract at the time that the issue is reported .
eg:

A 11.1.0 client to an 11.2.0 server issue requires the customer to have a valid Extended Support contract for the 11.1.0 client in order for Oracle to investigate it.

Client
Version		 Server Version
12.1.0 11.2.0 11.1.0 10.2.0 10.1.0 9.2.0
12.1.0 Yes Yes ES ES #7 No #3 No #3
11.2.0 Yes Yes ES ES #7 No Was #5
11.1.0 ES ES ES ES #7 Was #6 Was #5
10.2.0 ES #7 ES #7 ES #7 Was Was Was #5
10.1.0(#4) No Was #6 Was #6 Was Was Was
9.2.0 No#8 Was #5 Was #5 Was #5 Was Was

Key:
Yes Supported
LES Limited extended support previously applied to 9.2 releases but has now ended.
ES Supported but fixes only possible for customers with Extended Support .
Was Was a supported combination but one of the releases is no longer covered by any of Premier Support , Primary Error Correct support , Extended Support nor Extended Maintenance Support so fixes are no longer possible.
No Has never been Supported
Specific Notes:
  • #1 - See Note:207319.1
  • #2 - An ORA-3134 error is incorrectly reported if a 10g client tries to connect to an 8.1.7.3 or lower server. See Note:3437884.8 .
  • #3 - An ORA-3134 error is correctly reported when attempting to connect to this version.
  • #4 - There are problems connecting from a 10g client to 8i/9i where one is EBCDIC based. See Note:3564573.8
  • #5 - For connections between 10.2 (or higher) and 9.2 the 9.2 end MUST be at 9.2.0.4 or higher. Connections between 10.2 (or higher) and 9.2.0.1, 9.2.0.2 or 9.2.0.3 have never been supported.
  • #6 - For connections between 11.1 (or higher) database server and 10.1 / 10.2 database server across a database link the 10g end MUST be at 10.1.0.5 / 10.2.0.2 (or higher) respectively in order to use PLSQL between those versions. (This does not apply to normal 11g clients to 10g database server only server to server and a few very specific client products, such as Oracle Forms). See Note:4511371.8 for more details.
  • #7 - For database link connections between 11.1 (or higher) and 10.2 the 10g end MUST be at 10.2.0.2 (or higher) in order to use PLSQL between those versions. See Note:4511371.8 for more details.
  • #8 - Attempting to connect from 9.2 to 12.1 will fail with an "ORA-28040: No matching authentication protocol" error.
General Notes:
  1. For database links between different Oracle versions connections must be supported in BOTH directions in the matrix above.
    eg: As 11.2 -> 10.1 is not supported then database links between these version are not supported in either direction.
  2. Unsupported combinations may appear to work but can encounter errors for particular operations. The fact that they appear to work should not be relied upon - issues on unsupported combinations will not be investigated.
  3. Since new database servers are compatible with a limited set of older OCI clients, it may not be necessary to upgrade the client software when upgrading the database. However, some new features may not work without upgrading the client software. For example, an Oracle 10.2 client is able to connect to an 11.2 database, but is not able to take advantage of newer features such as Client Result Cache (introduced in 11.1).
  4. Oracle Applications , or other Oracle products, may have supported configurations not listed in the matrix above.
  5. The matrix above also applies between different platforms and between 32/64 bit releases of Oracle client / server except where any Oracle platform desupport notice indicates otherwise .
  6. Unix BEQUEATH (BEQ) connections are NOT supported between different releases. eg: Client 10.2 is not supported to make an Oracle Net connection to a 11.2 server using the BEQ protocol adapter regardless of the interoperability support listed above. See Note:364252.1 for more details.

 

Oracle Client Version별로 Oracle DB Server Version에 Support하는데 있어서 상호 호환성 여부를 정리한 자료입니다.

 

출처 : Oracle Support

저작자표시 비영리 동일조건 (새창열림)

'Oracle' 카테고리의 다른 글

Benefits and consequences of the NOLOGGING option  (0) 2013.11.01
Certification Information for Oracle Database on Microsoft Windows x64 (64-bit)  (0) 2013.08.29
Oracle Database 12c : Tom’s Top 12 Things About the Latest Generation of Database Technology  (0) 2013.08.21
A Comparison of Oracle's DATE and TIMESTAMP Datatypes  (0) 2013.04.09
Comparison Timestamp vs. Date Data Type  (0) 2013.04.09
:     

TISTORY에 Login하려면 여기를 누르세요.

◀ PREV : [1] : [···] : [5] : [6] : [7] : [8] : [9] : [10] : [11] : [···] : [28] : NEXT ▶

티스토리툴바