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.
At the moment all tables are LOGGING, therefore our archivelogs are (I think unnecessarily) huge and our restore (tested on a non-production database) takes an awfully long time. We are looking for ways to reduce both.
If we switch the staging tables to NOLOGGING mode and take a cold backup, these objects will be included in the backup. However, afterwards 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 later restore from the latest cold backup and apply the archive logs, 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.