Oracle’s NOLOGGING

转载:http://jakub.wartak.pl/blog/?page_id=107

Oracle’s NOLOGGING

This is collected information about NOLOGGING mainly from oracle forums (I’m not the author of it, pasting it only for my private reference):

Redo generation is a vital part of the Oracle recovery mechanism. Without it crashed instances will not recover and will not start in a consistent state. Excessive LOGGING is the result of excessive work on the database.

The Oracle® Database Administrator’s Guide 10g Release 2 say regarding the main benefits of the NOLOGGING option:
• Space is saved in the redo log files
• The time it takes to create the table is decreased
• Performance improves for parallel creation of large tables

“A very important rule with respect to data is to never put yourselft into an unrecoverable situation. The importance of this guideline cannot be stressed enough, but it does not mean that you can never use time saving or performance enhancing options. “

Oracle gave the user the ability to limit redo generation on tables and indexes by setting them in NOLOGGING mode. NOLOGGING affect the recoverability and before going into how to limit the redo generation it is important to clear the misunderstanding that NOLOGGING is the way out of redo generation, this are some points regarding it:

• NOLOGGING is designed to handle bulk inserts for data which can be re-produced.
• Writing to undo blocks causes generation of redo regardless of LOGGING status.
• LOGGING should not be disabled on a primary database if it has one or more standby databases. For this reason oracle introduced the ALTER DATABASE FORCE LOGGING command in Oracle 9i R2. This means that the NOLOGGING attribute will not have any effect on the segments if the database is in FORCE LOGGING MODE. NOLOGGING can be also override at tablespace level using ALTER TABLESPACE … FORCE LOGGING.
• Any change to the database dictionary will cause LOGGING. This will happen to protect the data dictionary. One example is if we allocated a space above the HWM for a table, and the system fail in the middle of one INSERT /*+ APPEND */ , the Oracle will need to rollback that data dictionary update. There will be redo generated but it is to protect the data dictionary, not your newly inserted data (Oracle just undo the space allocation if it fails, your data will disappear).
• Data which are not logged can not be recovered. The data should be backed up after the modification.
• Tables and indexes should be set back to LOGGING mode when the NOLOGGING is no longer needed.
• NOLOGGING is not needed for Direct Path Insert if the database is in NO Archive log mode.
• NOLOGGING should not be used for the data which can not be reproduced. If data which can not be reloaded was loaded using NOLOGGING and the database crashes before backing this data up, the data can not be recovered.
• NOLOGGING does not apply to UPDATE and DELETE.
• NOLOGGING will work during certain situations but subsequent DML will generate redo. Some of these situations are: direct load INSERT (using APPEND hint), CREATE TABLE … AS SELECT, CREATE INDEX.
• If the LOGGING or NOLOGGING clause is not specified when creating a table, partition, or index the default to the LOGGING attribute will be the LOGGING attribute of the tablespace in which it resides.

Disabling Logging (NOLOGGING)

Logging can be disabled at the table level or the tablespace level. If it is done at the tablespace level then every newly created index or table in this tablespace will be in NOLOGGING mode you can have logging tables inside a NOLOGGING tablespace). A table or an index can be created with NOLOGGING mode or it can be altered using ALTER TABLE/INDEX NOLOGGING. It is important to note that just because an index or a table was created with NOLOGGING does not mean that redo generation has been stopped for this table or index. NOLOGGING is active in the following situations and while running one of the following commands but not after that. This is not a full list:

• DIRECT LOAD (SQL*Loader)
• DIRECT LOAD INSERT (using APPEND hint)
• CREATE TABLE … AS SELECT
• CREATE INDEX
• ALTER TABLE MOVE
• ALTER TABLE … MOVE PARTITION
• ALTER TABLE … SPLIT PARTITION
• ALTER TABLE … ADD PARTITION (if HASH partition)
• ALTER TABLE … MERGE PARTITION
• ALTER TABLE … MODIFY PARTITION
o ADD SUBPARTITON
o COALESCE SUBPARTITON
o REBUILD UNUSABLE INDEXES
• ALTER INDEX … SPLIT PARTITION
• ALTER INDEX … REBUILD
• ALTER INDEX … REBUILD PARTITION

Logging is stopped only while one of the commands above is running, so if a user runs this:

• ALTER INDEX new_index NOLOGGING.

The actual rebuild of the index does not generate redo (all data dictionary changes associated with the rebuild will do) but after that any DML on the index will generate redo this includes direct load insert on the table which the index belongs to.

Here is another example to make this point more clear:

• CREATE TABLE new_table_nolog_test NOLOGGING(….);

All the following statements will generate redo despite the fact the table is in NOLOGGING mode:

• INSERT INTO new_table_nolog_test …,
• UPDATE new_table_nolog_test SET …,
• DELETE FROM new_table_nolog_test ..

The following will not generate redo (except from dictionary changes and indexes):

• INSERT /*+APPEND+/ …
• ALTER TABLE new_table_nolog_test MOVE …
• ALTER TABLE new_table_nolog_test MOVE PARTITION …

To activate the NOLOGGING for one of the ALTER commands above add the NOLOGGING clause after the end of the ALTER command.

For example:

• ALTER TABLE new_table_nolog_test MOVE PARTITION parti_001 TABLESPACE new_ts_001 NOLOGGING;

The same applies for CREATE INDEX but for CREATE TABLE the NOLOGGING should come after the table name.

Example:

• CREATE TABLE new_table_nolog_test NOLOGGING AS SELECT * FROM big_table;

“It is a common mistake to add the NOLOGGING option at the end of the SQL (Because oracle will consider it an alias and the table will generate a lot of logging).”

To user Direct Path Load in SQL*Loader you must run the $ORACLE_HOME/rdbms/admin/catldr.sql script. before your first sqlldr is run in direct path mode. To run sqlldr in direct path mode use direct=true.

Note: Even though direct path load reduces the generation of redo, it is not totally eliminated. That’s because those inserts still generate undo which in turn generates redo.

If there is an index on the table, and an +APPEND INSERT is made on the table, the indexes will produce redo. This can be circumvented by setting the index to unusable and altering the session’s (before 10g you only can set this at session level, after 10g you also can set this parameter at instance level) skip_unusable_indexes to true (This doesn’t apply to UNIQUE indexes.).

Tanel Poder note (found somewhere):
Regards to caching/nocaching I have been dealing with a huge challenge in
one project – when you’d cache your LOBs, they’d be are always logged – with
incoming data feeds hundreds of megabytes per second the logging overhead
gets too large – “luckily” we can tolerate small data loss in case of
disaster – we are planning to use NOCACHE NOLOGGING LOBs, storage snapshots,
and incremental RMAN backups.

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/21584437/viewspace-718895/,如需转载,请注明出处,否则将追究法律责任。

转载于:http://blog.itpub.net/21584437/viewspace-718895/

  • 0
    点赞
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
### 回答1: Oracle中的"append nologging"是一种数据插入方式,它可以在插入数据时不写入日志文件,从而提高数据插入的效率。但是,这种方式也会带来一定的风险,因为如果系统崩溃或出现故障,这些未记录的数据将无法恢复。因此,在使用"append nologging"时需要谨慎考虑,并根据具体情况进行选择。 ### 回答2: 在Oracle数据库中,"append nologging"是一个用于插入数据的选项。当我们使用这个选项时,会告知Oracle不要将数据更改记录的日志信息写入日志文件中。 使用"append nologging"选项有以下几个优点: 1. 提高插入性能:由于不需要将每个插入操作的详细信息写入日志文件,可以大大减少写操作对性能的影响。这对于大批量数据插入操作特别有效,可以显著提高插入速度。 2. 减少日志文件大小:由于没有记录每个插入操作的日志,可以减少日志文件的大小。这对于需要保留日志一段时间的数据库来说,可以显著减少存储空间的使用。 3. 简化恢复过程:由于没有详细的插入操作日志,恢复过程可以更简单。在某些情况下,可以通过简单的回滚操作来还原数据。 然而,使用"append nologging"选项也存在一些风险和限制: 1. 不能进行点恢复:由于没有详细的插入操作日志,当发生故障时无法进行点恢复。如果需要恢复到插入操作之前的状态,只能进行完全恢复。 2. 必须小心使用:"append nologging"选项要谨慎使用,必须仔细评估数据的重要性和对插入操作的恢复需求。如果数据丢失将会造成严重问题,应该避免使用此选项。 3. 仅适用于插入操作:"append nologging"选项只适用于插入操作,对其他数据操作(如更新和删除)无效。 总之,"append nologging"选项是Oracle数据库中一个能够提高插入性能和减少存储空间使用的选项,但使用时需要注意数据的重要性和对插入操作的恢复需求。 ### 回答3: 在Oracle数据库中,"append nologging"是一种表级别的选项,用于指定在数据插入操作中不生成任何日志信息。通过使用"append nologging"选项,可以提高数据插入的性能。 当我们执行插入操作时,默认情况下,Oracle会将插入的数据写入日志文件中,以确保数据的持久性和安全性。然而,对于一些大规模的数据插入操作,特别是对于临时或者不重要的数据,生成日志文件可能会成为性能瓶颈。这时,我们可以选择使用"append nologging"选项,该选项会禁止生成日志文件,从而提高插入操作的性能。 使用"append nologging"选项需要谨慎,因为它可能会导致数据丢失的风险。由于没有生成日志文件,一旦系统发生故障或者崩溃,这些没有被记录的数据将无法恢复。因此,在使用"append nologging"选项时,需要确保数据的重要性和可恢复性,并做好相应的数据备份和恢复策略。 可以通过以下语法在Oracle中使用"append nologging"选项: ``` INSERT /*+ APPEND NOLOGGING */ INTO table_name (column1, column2, ...) VALUES (value1, value2, ...); ``` 在上述语句中,通过在INSERT语句中添加"/*+ APPEND NOLOGGING */"注释来启用"append nologging"选项。然后,我们可以指定要插入的表名和对应的列和值。 需要注意的是,使用"append nologging"选项并不会对查询和更新操作产生影响。它只适用于插入操作,并且仅在一些特定的情况下才建议使用,例如临时表、快速数据装载等。 总之,"append nologging"是Oracle数据库中的一个选项,用于指定在数据插入操作中不生成任何日志信息,从而提高插入操作的性能。但是,需要谨慎使用,并做好相应的数据备份和恢复策略。
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值