log generation Options

[ntadba]

Hello

I used impdp to import meta-data then put the tables in nologgin mode. After that I started import data_only option . Tables went in loggin mode. Does impdp overwrite nologging to logging mode how to overcome this problem

Regards
Nitin

[HAL9000]

"Does impdp overwrite nologging to logging mode "

You can't do nologging for data pump imports.

Youi can, however, use the undocumented parm _disable_logging:


"With _disable_logging = true, the call to write redo is still made, but the actual file-write it not made. Thus you want to make the redo files extremely large as checkpoints still occur when the redo logs are nominally full. "

http://www.dba-oracle.com/t_8i_hidden_disa..._parameters.htm

[mbobak]

"Does impdp overwrite nologging to logging mode "

You can't do nologging for data pump imports.

Youi can, however, use the undocumented parm _disable_logging:
"With _disable_logging = true, the call to write redo is still made, but the actual file-write it not made. Thus you want to make the redo files extremely large as checkpoints still occur when the redo logs are nominally full. "

http://www.dba-oracle.com/t_8i_hidden_disa..._parameters.htm

Well, first, I'm no datapump expert, but, this seems to imply (though does not directly say) that nologging will work with datapump:
http://download.oracle.com/docs/cd/B19306_...t.htm#sthref241
(See the second "Note" under "Invoking datapump import".


As to _disable_logging=TRUE, that's really, really bad advice! Undocumented parameters are bad to begin with, but this one, in particular, is a disaster waiting to happen. Don't set it, ever. Just don't. With _disable_logging set to TRUE, an instance crash or a shutdown abort WILL trash your database!!

-Mark

[burleson]

Hi Martk,

>> As to _disable_logging=TRUE, that's really, really bad advice! Don't set it, ever. Just don't. With _disable_logging set to TRUE, an instance crash or a shutdown abort WILL trash your database!!

I see your point, especially for the naive and reckless, but I'm not sure that Nitin is untrained.

In the hands of a properly trained, experienced DBA, NOLOGGING is a gold mine and I must respectfully diagree with you about these dangers!

When I rebuild a large system, I will

- Full backup
- Load everything I can in PARALLEL NOLOGGONG mode (during the maintenance window),
- Full backup - Finally, take a full system backup right before bringing the system online.

Most DBA's call it a "backup sandwich", it's quite common in my world where you have super-tight maintenance windows and every second counts.

[mbobak]

Hi Martk,

>> As to _disable_logging=TRUE, that's really, really bad advice! Don't set it, ever. Just don't. With _disable_logging set to TRUE, an instance crash or a shutdown abort WILL trash your database!!

I see your point, especially for the naive and reckless, but I'm not sure that Nitin is untrained.

In the hands of a properly trained, experienced DBA, NOLOGGING is a gold mine and I must respectfully diagree with you about these dangers!

When I rebuild a large system, I will

- Full backup
- Load everything I can in PARALLEL NOLOGGONG mode (during the maintenance window),
- Full backup - Finally, take a full system backup right before bringing the system online.

Most DBA's call it a "backup sandwich", it's quite common in my world where you have super-tight maintenance windows and every second counts.

Don,

Please don't confuse NOLOGGING with _disable_logging=TRUE. I have no problem at all with NOLOGGING operations. I agree, you need to understand how it works, and make sure your backup strategy accounts for it, but, it can be a very good thing. However, that's quite different from _disable_logging=TRUE, which should never be set on any database that you care about. _disable_logging=TRUE is FAR MORE DANGEROUS than NOLOGGING.

-Mark

[burleson]

Hi Mark,

>> I have no problem at all with NOLOGGING operations. . . . _disable_logging=TRUE is FAR MORE DANGEROUS than NOLOGGING.

Are you saying this because it is at the system level?

So long as it's used within a backup sandwich, why the issue?

[mbobak]

Because it's functionally very different.

The main things to consider about NOLOGGING:
- Supported feature of Oracle
- Works only with direct load insert
- Still maintains minimal logging, i.e., space transactions, extent invalidation records for loaded extents, etc.

Contrast with _disable_logging=TRUE:
- Undocumented, unsupported parameter
- all redo writes to log buffer still happen
- writes of redo buffer down to disk are disabled.
- Not limited to direct load insert. All operations are no longer protected by redo on disk.

The big difference is, if your instance dies, (crash or shutdown abort) there's no data in the online redo logs to even do instance recovery. This is very bad. Your database could suffer a corruption from which you can't recover.

I'll say it again:

Never, ever, set _disable_logging=TRUE on any database you care about.

Hope that helps,

-Mark

[mbobak]

No responses?

[burleson]

Hi Mark,

>> No Responses?

Sorry, I've been off the air for several weeks (some countries have no internet!).

>> The big difference is, if your instance dies, (crash or shutdown abort) there's no data in the online redo logs to even do instance recovery. This is very bad. Your database could suffer a corruption from which you can't recover.

Yes, but if I took a full backup first, and I get an instance crash, I simply restore and start over.

Sorry, Mark, I still don't see the danger here? (in the hands of an experienced DBA)

What am I missing?

[mbobak]

Hi Don,

Welcome back.

Here's what I'm saying:

Assume archivelog mode is active, and we start w/ _disable_logging=FALSE:

Time T1: Take a backup of the entire database.
Time T2: Set _disable_logging = TRUE, bounce database. (I'm assuming it requires a bounce.)

Now, from time T2, any transaction against any segment will NOT be recorded in online redo logs. I haven't actually used this option, so, I'm not sure if the archive log switching mechanism will switch logs or not, but, since the online redos don't have anything useful, the archives, which are simply copies of the online redo, also won't have anything useful in them.

Time T3: set _disable_logging = FALSE, and bounce, making sure to do a non-aborted shutdown.
Time T4: startup database, take backup.
Time T5 backup complete.

That all looks good, yes? However, after time T2, and until you complete the second backup at time T5, you're at risk. If your database crashes, the best you can do is recover to T2. Also, even if you don't have a problem, you need to bounce twice to implement this.

And that of course, is if you're vigilant about backing up before/after any _disable_logging work. My biggest fear would be that someone turns it on, and leaves it on, and then can't recover.

Compare that to NOLOGGING operations, where no bounce is required (this is huge for production databases), and the worst case, only those segments that were undergoing nologging operations are affected by any crash/recovery situation, the rest of the database is intact to current point in time.

Hope that helps,

-Mark

[burleson]

Hi Mark,

Good discussion.

For me, the core issue is getting a large reorg/maintenance done within a tight downtime window.

I've had to reorg 80 gig databases in a six hour window, and anything to improve throughput is useful.

Dangerous? No, not if you are peoperly trained and careful, IMHO.

Of course, this is predicated on the assumption that _disable_logging = TRUE is faster, enough to compensate for the time required for the backup sandwich and the bounces.

>> I haven't actually used this option, so, I'm not sure if the archive log switching mechanism will switch logs or not, but, since the online redos don't have anything useful, the archives, which are simply copies of the online redo, also won't have anything useful in them.

Yes, but that's OK, if the reorg will happen nn hours faster.

>> Also, even if you don't have a problem, you need to bounce twice to implement this.

Sure, that's fine.

>> where no bounce is required (this is huge for production databases),

Why is it "huge"? It takes just a few minutes, right? Especially with the new fast startup.

>> My biggest fear would be that someone turns it on, and leaves it on, and then can't recover.

Sure, but that's malfeasence, and not something anyone would expect from a professional.

It gets back to an informational issue that I've seen over-and-over:

Do we have a responsibility to protect the reckless, un-trained or inept DBA?

I say no!

I've had people ask me to put in (derogatory word removed) disclaimers (which I will do), but it's like the nonsense on product warning labels. Like this!

http://www.dumb.com/productwarnings.htm

Liquid Plummer - Warning: Do not reuse the bottle to store beverages.

Windex - Do not spray in eyes.

Bowl Fresh - Safe to use around pets and children, although it is not recommended that either be permitted to drink from toilet.

What do you think, Mark?

Do we have a responsibility to protect the reckless, un-trained or inept DBA?

If so, how?

[Ben Prusinski Or...]

Good discussion and I learned a few things on the logged versus non-logged operations for import and export.

I do think its best to err on the side of caution and as a DBA I believe in the never assume and be risk vigilant mantra.

I have been at customer sites where the lead DBA was careless and caused a production site outage taken an entire corporate site down for several days and getting negative press as a result. Needless to say the DBA was fired on the spot and is now not working as a DBA for that company.


Measure twice and cut once and trust but verify.

Just the other day I was finishing a review course on Oracle 10g Backup and Recovery and learned some new things on Oracle Secure Backup and the instructor was talking to a client who had lost their SQL Server database and the client just re-installed SQL Server without a backup! Talk about data loss and poor procedures!

[burleson]

Hi Ben,

>> I do think its best to err on the side of caution

Stop being so diplomatic! It's not clear who you disagree with, me or Mark?

>> Needless to say the DBA was fired on the spot and is now not working as a DBA for that company.

They got another job as a DBA? Ouch!

I would have fired the DBA manager.

>> Talk about data loss and poor procedures!

OK, but who should be to blame? The DBA, or the manager who hired an incompetant DBA?

The core problem is that corporations are hiring people who have not made a commitment to the IT profession, saving a fee bucks by hiring underqualified people without the requisite education (IT or CS degrees).

I guarantee, there is no graduate with an AACSB IT degree who has not been taught proper B&R procedures. It just doesn't happen, if you bhire peoperly educated professionals!

Ah, but I should not complain, as I make my money cleaning-up after them!

http://www.google.com/search?&q=oracle+emergency+support

[Jim Prather]

Don,

I'm an application DBA and I think it's very important to understand that the _disable_logging option could trash the whole instance. Many times imp and impdp are used to deal with a schema at a time and NOT the whole instance. If I'm not mistaken, the risk of NOLOGGING would be limited to just the imp or impdp going on to the given schema, right?

In any case, I cannot seem to get impdp to recognize my _disable_logging=TRUE option anyway. Is there any other way to speed up the impdp?

[burleson]

Hi Jim,

>> I cannot seem to get impdp to recognize my _disable_logging=TRUE option anyway.

That's a hidden init.ora parm, and you should ONLY consider it if you open an SR, and get permisison from Oracle technical support.

Bottom, line, you can use it, BUT:

1 - You MUST take a full backup before starting the import
2 - There must be no other activity, other than the import
3 - If imp crashes you MUST restore from the backup

There are other ways to speed up imports, try here:

http://www.dba-oracle.com/oracle_tips_load_speed.htm

Set commit=n – For tables that can afford not to commit until the end of the load, this option provides a significant performance increase. Larger tables may not be suitable for this option due to the required rollback/undo space.

Set indexes=n – Index creation can be postponed until after import completes, by specifying indexes=n. If indexes for the target table already exist at the time of execution, import performs index maintenance when data is inserted into the table. Setting indexes=n eliminates this maintenance overhead.

Use the buffer parameter – By using a larger buffer setting, import can do more work before disk access is performed.