SQL & PL/SQL

Hi,

Even if you have tables with NLLOGGING minimal logging with respect to table structure changes are recorded, so in the case of recovery, you can recover the database with nologging tables from the last full backup but data changes after the full backup can not be recovered on the nologging tables.

Similar is the case with data guard where the data changes to the table will not be recorded and you have to load the data to the primary database after failover yourself.

Regards

It can be a difficult question to answer, since many things you tell the db to be nologging actually are not. In fact, the dba can set [url http://www.orafaq.com/wiki/Nologging_and_force_logging]force logging.

Yes, you have to assume a lot of work will result from nologging operations, no, other operations won't make be any more difficult.

See http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:5280714813869 for some illustration of possibilities and how to figure things out.

>
But what exactly will happen when a DBA is recovering a crashed database which had few NOLOGGING tables and data loaded in these tables with nologging ?
>

See a demonstration here:
http://uhesse.wordpress.com/2009/09/15/how-do-nologging-operations-affect-recovery/

>
Also, if we have a standby database maintained by transporting archived logs, what will be the case if the main (is it called primary) database has (media) crashed and we have to shift on the standby?
>

You should avoid this situation by saying

SQL> alter database force logging;

on the Primary - best before creation of the Standby. Else you will lose the objects created with nologging in case of a failover. If nologging operations have been done on the Primary already, but no damage there yet - better recreate the Standby after force logging on the Primary.

Kind regards
Uwe Hesse

http://uhesse.wordpress.com

Thanks for the replies guys.

Let me put forward a more precise question.

Our application has a bunch of tables which are used to process the end-of-day batch only. They are staging tables. Every night we load files (sometime file per table or sometimes multiple files per table) and then process that data to update our application data.
At the moment, these staging tables which just hold the data from incoming files are in LOGGING mode.
We have a standby database.
Now, if we have to move to the standby database, I am not at all worried about the data in these tables. I can fill that up with the files we have (we keep the files for a week).

What all I need is, all the tables, which are in LOGGING mode should be ready on the standby.
The tables in NOLOGGING mode should have the up-to-date structure. I can fill up the data and rebuild the indexes.

Does having few tables in NOLOGGING mode affect the recovery/standby of other tables (which are in LOGGING mode) ??

If we create a tablespace only for NOLOGGING tables, will that help??

Thanks

Any thoughts?
Please...

After failover, if your intact tables (no NOLOGGING operations against them happend) are not dependent on the now corrupted tables (NOLOGGING operations against them happend) there is no problem - if you don't need to use the now corrupted tables, that is.

But (big BUT), you must be sure that no other NOLOGGING operations took place on tables that now cannot be reproduced! If you cannot make that sure, your data will be lost inspite of the presence of a standby database. Do you want to bet your job on it?

Technically, your idea is possible.

Kind regards
Uwe Hesse

http://uhesse.wordpress.com

Hi Uwe Hesse,

You mentioned, "if your intaact tables are not dependant on now corrupted tables". What do you mean by 'dependant'?

There are few LOGGING tables and few NOLOGGING tables? why should the NOLOGGING tables affect the recoverability of the LOGGING tables?
I want to understand that bit.

On the standby or after recovering the database (with backup and archive/redo logs), I want all my LOGGING tables up-to-date with data and for NOLOGGING tables, I only need the table structure in place. No need of data and indexes on NOLOGGING tables (indexes can be rebuilt, data can be inserted).
What are the gaps in my idea/understanding ?
Where are potential areas which can make this idea fall?

Also, what will be the difference if I have saperate tablespace for NOLOGGING tables? and if I don't have saperate tablespace for NOLOGGING tables?

Regards

>
You mentioned, "if your intaact tables are not dependant on now corrupted tables". What do you mean by 'dependant'?

There are few LOGGING tables and few NOLOGGING tables? why should the NOLOGGING tables affect the recoverability of the LOGGING tables?
I want to understand that bit.

On the standby or after recovering the database (with backup and archive/redo logs), I want all my LOGGING tables up-to-date with data and for NOLOGGING tables, I only need the table structure in place. No need of data and indexes on NOLOGGING tables (indexes can be rebuilt, data can be inserted).
What are the gaps in my idea/understanding ?
Where are potential areas which can make this idea fall?

Also, what will be the difference if I have saperate tablespace for NOLOGGING tables? and if I don't have saperate tablespace for NOLOGGING tables?
>

Tables reference other tables sometimes. There are things like joins and foreign keys. I don't know your data model - you do.

If you are sure that you can reproduce everything done on the primary with NOLOGGING - go for it. But you will not get me recommend something that we officially do not recommend.

If you run a Data Guard configuration with a Primary without FORCE LOGGING, you do that at your own risk. One of these risks is that you don't necessarily know each and every NOLOGGING operation that somebody somewhen will do.

It doesn't matter whether you have a tablespace with NOLOGGING or not for the whole question. It doesn't matter why or where objects with NOLOGGING are loaded for the problem. It only matters that you are able to reproduce every change after a failover while your Standby Database is full of corrupted objects.

If you ask me:
1. Don't do it.
2. If you insist: Test that before implementing it and see if it works after failover the way you thought.
3. Don't do it even if your test was successful :-)

Kind regards
Uwe Hesse

http://uhesse.wordpress.com

Hi Uwe,

So basically, if we are using Dataguard, NOLOGGING is not a good idea.
What actually happens there? does the standby is not maintained at all? or only the NOLOGGING objects will be in a corrupt/unusable state?
Will the standby have the DDL changes on NOLOGGING objects?
Is it possible to find out such corrupt objects (if we are switching to the standby)?

Also, even if we are FORCE LOGGING, if we truncate a table or partition, will we brake the standby?

Thanks

>
So basically, if we are using Dataguard, NOLOGGING is not a good idea.
>
exactly.

>
What actually happens there? does the standby is not maintained at all?
>
No. It is maintained.

>
or only the NOLOGGING objects will be in a corrupt/unusable state?
>
yes.

>
Will the standby have the DDL changes on NOLOGGING objects?
>
yes.

>
Is it possible to find out such corrupt objects (if we are switching to the standby)?
>
You use them and get error messages.

>
Also, even if we are FORCE LOGGING, if we truncate a table or partition, will we brake the standby?
>
no. the truncate is reproduced on the standby.

Kind regards
Uwe Hesse

http://uhesse.wordpress.com