https://community.oracle.com/thread/666455
1]In Primary, if a table "A" is in NOLOGGING, redo's are not generated(except the dictionary stuff) if INSERT is performed with "APPEND" hint. Isn't it?
(900 rows as inserted with append hint into A)
2]So if there is a standby, while the archive logs are applied the table "A" isn't populated with the data. (isn't it ?)
3]On Primary, if i "DELETE from A where id=3",
what would happen while that particular archive log is applied on the standby(which has the table but no data 'coz of nologging and append ?
What would happen if after step2, i open the standby in read only and perform
TIA,
JJ
(900 rows as inserted with append hint into A)
2]So if there is a standby, while the archive logs are applied the table "A" isn't populated with the data. (isn't it ?)
3]On Primary, if i "DELETE from A where id=3",
what would happen while that particular archive log is applied on the standby(which has the table but no data 'coz of nologging and append ?
What would happen if after step2, i open the standby in read only and perform
select * from A;Will it through error? or give "no rows selected" ?
TIA,
JJ
Average User Rating: No ratings (0 ratings)
Average User Rating
No ratings
(0 ratings)
1. Re: What happens in Standby after a nologging operation on Primary DB ?
mbobak Jun 4, 2008 12:25 PM (in response to 569701)
JJ,
I don't have standby db setup to test it, but I'm pretty sure you'll see ORA-26040 'data block was loaded with NOLOGGING option'. This is the same error you'd get on the primary if you did media recovery using a backup that was taken before the nologging operation.
To avoid this, set force_logging to TRUE on your primary database.
-Mark2. Re: What happens in Standby after a nologging operation on Primary DB ?
639907 Jun 4, 2008 4:42 PM (in response to 569701)
Hi J.J,
This is part of a material I'm preparing about this topic:
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.).
Regards,Note: Please be free to comment or correct it.
Francisco Munoz Alvarez3. Re: What happens in Standby after a nologging operation on Primary DB ?
rayb12617 Apr 30, 2010 1:10 AM (in response to 639907)
I understand the process of nologging on a single operation on a primary database.
I would like to know what happens when appliying redo to a standby database afet having performed such an operation on the primary (ex alter index ... NOLOGGING) and then performing normal updates.
Will application of redo logs fail altogether? What would the error code be?
Will application of redo logs fail only for the affected object? or tablespace?
or will the logs get applied anyway and actually corrupt the stanby?
Thanks
RayB4. Re: What happens in Standby after a nologging operation on Primary DB ?
mbobak Apr 30, 2010 2:46 AM (in response to rayb12617)Hi Ray,
Hmm...this thread is almost a year old....
Anyhow, to answer your questions:RayB wrote:
Hmm...I'm not sure you do....let me explain why I think that.
I understand the process of nologging on a single operation on a primary database.I would like to know what happens when appliying redo to a standby database afet having performed such an operation on the primary (ex alter index ... NOLOGGING) and then performing normal updates.
This isn't a valid example. Altering an index to nologging has no effect on normal DML on that index. All index updates will log. It would affect a rebuild, in that the rebuild would not log. But normal DML would be unaffected.
But, suppose you altered a heap table to be nologging. What then? Well, still, normal DML would log, and there'd be no effect. If however, after setting nologging, you did a direct load insert, i.e., insert /*+ append */, then those changes would not be logged. However, block invalidation records are generated for those new blocks.Will application of redo logs fail altogether? What would the error code be?
No, at redo application time, there would be no error.Will application of redo logs fail only for the affected object? or tablespace?
Redo application will not fail.or will the logs get applied anyway and actually corrupt the stanby?
Essentially, yes. The redo gets applied, but, that redo, instead of having the data changes that occurred on primary, have block invalidation records in them. So, the blocks are allocated to the table and marked as invalid. If/when you try to read them on the standby, you'll get ORA-26040.
If you have concerns about this, you should consider enabling force logging.
Hope that helps,
-Mark5. Re: What happens in Standby after a nologging operation on Primary DB ?
ajallen Apr 30, 2010 2:51 AM (in response to 569701)
RayB,
Since it appears you are using dataguard, please execute the following query and post the results here.
SELECT FORCE_LOGGING FROM V$DATABASE;6. Re: What happens in Standby after a nologging operation on Primary DB ?
rayb12617 Apr 30, 2010 5:53 PM (in response to ajallen)The answer is "NO".
Thanks for the answer, I keep forgetting about the v$database object.
RayB7. Re: What happens in Standby after a nologging operation on Primary DB ?
rayb12617 Apr 30, 2010 5:57 PM (in response to mbobak)I meant an operation like "alter index [rebuild | move] nologging;" which I suspect will not log the rebuild operation but continue logging after the operation completes.
Otherwise, thanks for conferming that corruption will occur on the standby.
RayB8. Re: What happens in Standby after a nologging operation on Primary DB ?
ajallen Apr 30, 2010 6:39 PM (in response to rayb12617)You need to run ALTER DATABASE FORCE LOGGING; So that NOLOGGING will be ignored. Else your standby database can get corrupted by not seeing all changes made in the primary. See the entry by F.Munoz Alvarez, above.9. Re: What happens in Standby after a nologging operation on Primary DB ?
user11204758 Jan 26, 2011 12:54 AM (in response to ajallen)
So what's the process if i need to failover to the standby database where nologging has been used in the primary database?10. Re: What happens in Standby after a nologging operation on Primary DB ?
jgarry Jan 26, 2011 3:11 AM (in response to user11204758)
[url http://download.oracle.com/docs/cd/B19306_01/server.102/b14239/scenarios.htm#i1015738]Buy more personal lubricant.11. Re: What happens in Standby after a nologging operation on Primary DB ?
Hemant K Chitale Jan 26, 2011 11:12 AM (in response to jgarry)
Joel,
I almost flagged your post as s_p_a_m until I looked at what the URL actually was !
Hemant K Chitale
