| 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.
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
| 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.
|
|
|
|
| Re: Confusion about nologging [message #610673 is a reply to message #610668] |
Sat, 22 March 2014 13:17 |
 |
Michel Cadot
Messages: 68728
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.  ]
[Updated on: Sat, 22 March 2014 13:19] Report message to a moderator |
|
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
