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
    评论
智慧校园整体解决方案是响应国家教育信息化政策,结合教育改革和技术创新的产物。该方案以物联网、大数据、人工智能和移动互联技术为基础,旨在打造一个安全、高效、互动且环保的教育环境。方案强调从数字化校园向智慧校园的转变,通过自动数据采集、智能分析和按需服务,实现校园业务的智能化管理。 方案的总体设计原则包括应用至上、分层设计和互联互通,确保系统能够满足不同用户角色的需求,并实现数据和资源的整合与共享。框架设计涵盖了校园安全、管理、教学、环境等多个方面,构建了一个全面的校园应用生态系统。这包括智慧安全系统、校园身份识别、智能排课及选课系统、智慧学习系统、精品录播教室方案等,以支持个性化学习和教学评估。 建设内容突出了智慧安全和智慧管理的重要性。智慧安全管理通过分布式录播系统和紧急预案一键启动功能,增强校园安全预警和事件响应能力。智慧管理系统则利用物联网技术,实现人员和设备的智能管理,提高校园运营效率。 智慧教学部分,方案提供了智慧学习系统和精品录播教室方案,支持专业级学习硬件和智能化网络管理,促进个性化学习和教学资源的高效利用。同时,教学质量评估中心和资源应用平台的建设,旨在提升教学评估的科学性和教育资源的共享性。 智慧环境建设则侧重于基于物联网的设备管理,通过智慧教室管理系统实现教室环境的智能控制和能效管理,打造绿色、节能的校园环境。电子班牌和校园信息发布系统的建设,将作为智慧校园的核心和入口,提供教务、一卡通、图书馆等系统的集成信息。 总体而言,智慧校园整体解决方案通过集成先进技术,不仅提升了校园的信息化水平,而且优化了教学和管理流程,为学生、教师和家长提供了更加便捷、个性化的教育体验。
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

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

抵扣说明:

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

余额充值