OraFAQ Forum: Server Administration » Confusion about nologging

Home » RDBMS Server » Server Administration » Confusion about nologging (Oracle 11.2.0.2 Enterprise 64 bit)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Confusion about nologging [message #610659]

Sat, 22 March 2014 10:52

d_seng
Messages: 78
Registered: November 2011
Location: UK

Member

Hi all,

We have a data warehouse (running in ARCHIVELOG mode) where approx. 75% of the tables (by volume and number) are transient staging tables. The data in these tables can be lost without any regret, as only the data from the DWH dimension and fact tables are referred to in subsequent refreshes. Of course, we need these staging tables/indexes to exist for a successful run.

If we switch the staging tables to NOLOGGING mode and take a cold backup, these objects will be included in the backup. However if we change the structures of any of these tables subsequently or create new tables (in NOLOGGING mode) or create new indexes on the new tables, etc., will these objects be backed up into the archive logs? Therefore, if we restore from a backup, will we get back these table/index definitions (not the data, of course)?

The confusion is stemming from the reasoning that all data dictionary operations are logged, regardless of whether the objects and the database are in logging mode. I'm not sure if data dictionary operations include ALTER TABLE ADD COLUMN, CREATE TABLE, CREATE INDEX, etc.

Unfortunately I don't have the setup or the skills to test this (I'm not exactly a DBA).

Thanks in advance.

Report message to a moderator

Re: Confusion about nologging [message #610660 is a reply to message #610659]

Sat, 22 March 2014 11:00

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

A restore from cold backup does not know about or care about any REDO logfiles that were created after the cold back.
All subsequent DDL changes will not be reflected in the restored database produced by cold backup.

Some, many, most Professional IT staff maintains application source code, including DDL changes, in code repository.
You should consider doing the same.

Report message to a moderator

Re: Confusion about nologging [message #610662 is a reply to message #610660]

Sat, 22 March 2014 11:16

d_seng
Messages: 78
Registered: November 2011
Location: UK

Member

Sorry I should have said "Therefore, if we restore from the latest cold backup and apply the archive logs, will we get back these table/index definitions (not the data, of course)?".

I am fully with your suggestion of applying the repository DDL to the database, but that would require selective application of database objects as the repository would contain all tables. Further, one would have to be sure that the deployments to the database only happen from the repository, which is a bit iffy in our organisation. Embarassed

If we could restore object definitions from the backup, it'd be a one stop shop, don't you agree?

Report message to a moderator

Re: Confusion about nologging [message #610663 is a reply to message #610662]

Sat, 22 March 2014 11:19

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

I agree

Report message to a moderator

Re: Confusion about nologging [message #610664 is a reply to message #610663]

Sat, 22 March 2014 11:22

d_seng
Messages: 78
Registered: November 2011
Location: UK

Member

Quote:

"Therefore, if we restore from the latest cold backup and apply the archive logs, will we get back these table/index definitions (not the data, of course)?"

So any thoughts on my original question then?

Cheers

Report message to a moderator

Re: Confusion about nologging [message #610665 is a reply to message #610664]

Sat, 22 March 2014 11:28

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

It is your database & you are free to (ab)use it any way you deem appropriate.

>"Therefore, if we restore from the latest cold backup and apply the archive logs, will we get back these table/index definitions (not the data, of course)?"
Rhetorical question below:
When was the last time YOU successfully completed task as stated above?

Report message to a moderator

Re: Confusion about nologging [message #610666 is a reply to message #610665]

Sat, 22 March 2014 11:43

d_seng
Messages: 78
Registered: November 2011
Location: UK

Member

Quote:

It is your database & you are free to (ab)use it any way you deem appropriate.

That's not a very helpful answer, is it?

Quote:

When was the last time YOU successfully completed task as stated above?

At the moment all tables are LOGGING, therefore our archivelogs are (I think unnecessarily) huge and our restore (tested on a non-production database, not done by ME, personally) takes an awfully long time. We are looking for ways to reduce both.

So the question is again "Therefore, if we restore from the latest cold backup and apply the archive logs, will we get back these (nologging) table/index definitions (not the data, of course)?"

Can I hope for a more constructive reply?

Report message to a moderator

Re: Confusion about nologging [message #610667 is a reply to message #610666]

Sat, 22 March 2014 11:58

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

With free advice, sometimes you get what you paid for it.

Report message to a moderator

Re: Confusion about nologging [message #610668 is a reply to message #610667]

Sat, 22 March 2014 12:13

d_seng
Messages: 78
Registered: November 2011
Location: UK

Member

Come on man, that's a very patronising reply. I thought this was a professional forum.

I have asked a very genuine question in a very polite manner, and seriously these are the kind of replies you come up with?

Please can anybody else care to spare a thought for my question?

Report message to a moderator

Re: Confusion about nologging [message #610672 is a reply to message #610668]

Sat, 22 March 2014 13:11

EdStevens
Messages: 1376
Registered: September 2013

Senior Member

I've never considered your basic question, but if I were faced with that issue in my workplace I'd set up a little test to see for myself exactly how the proposed actions behave.

So your database is a big data warehouse? So is one of mine. That's why I have a virtual 'sandbox' system installed on my desktop. These kinds of things don't require testing at volume. In fact, I have a virtual counterpart to every Linux server I have in the data center both prod and test. Databases aren't populated with app data, but software versions, patch levels, and directory structures, interserver networking - all are exact duplicates. I always have a system at hand to run experiments on without impacting either production or application development. I even use the very same desktop tools (putty, SQL Dev, OEM, SQL Navigator) as I use on the live systems. As far as my desktop tools are concerned, these 'sandbox' servers are just another server on my network.

Report message to a moderator

Re: Confusion about nologging [message #610673 is a reply to message #610668]

Sat, 22 March 2014 13:17

Michel Cadot
Messages: 68757
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

NOLOGGING applies only on your data, DDL are modification in SYS schema (in addition to physical modification) and are always logged, so the answer to your question is yes you will have the modification made on the table structure and indexes.
But I advise you to not trust me and make a simple test on a test database (you can download Oracle XE on your workstation which contains a database and can be installed very quickly, it will be useful for this specific question and the subsequent tests you will have to make).

[Edit; I see Ed think the same thing about test environment. Smile

]

[Updated on: Sat, 22 March 2014 13:19]

Report message to a moderator

Re: Confusion about nologging [message #610676 is a reply to message #610673]

Sat, 22 March 2014 13:28

d_seng
Messages: 78
Registered: November 2011
Location: UK

Member

Thanks Michel, the explanation sounds reasonable, however as you recommended, I'll not take your word for it Razz

I do have a database on my laptop, but I'm too chickened to perform a backup/restore on it (I am not a qualified DBA so I'll have to read the docs thoroughly), and the database contains far too many repositories for 3rd party tools.

But I'll see if I'm able to create another database and give it a try. I'll remember to post the results whenever I get to it.

Report message to a moderator

Re: Confusion about nologging [message #610779 is a reply to message #610676]

Mon, 24 March 2014 05:05

d_seng
Messages: 78
Registered: November 2011
Location: UK

Member

Dear all, please see this excellent post: http://www.dbi-services.com/index.php/blog/entry/the-consequences-of-nologging-in-oracle.

This clarified everything for me and the results suggest the way forward.

Report message to a moderator

Previous Topic:	create a distributed database on two servers
Next Topic:	Should table compression decrease redo?

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Mon May 05 08:02:48 CDT 2025