Hi All,

I discovered that all of our tables logging mode is not enabled except the tables we created or reorganized after our DB installation and data load.

All Tablespaces logging modes are enabled And I do not know how our tables logging modes are disabled.

We are using Oracle 9.2.0.3 and Baan IV .

Anyone else having this situation?


Thanks

Caner

Sounds like someone turned it off manually. Maybe because for fast import? Lucky you, you got no disaster.

Find all tables that have no logging like:

SQL> select table_name from user_tables where logging = 'NO';

and change the tables to logging mode:

SQL> alter table xyz logging;


Hope this helps,
Dick

Hi Dick,

I got the list of logging disabled tables , and it is over 20000 and I have over 30000 logging mode disabled indexes .
Only 6000 tables and 10000 indexes logging modes are enabled.



I think this occured at data import time when we migrated to Oracle 9. by disabling Tablespaces logging modes for faster import , and Tablespaces logging modes enabled after that.

Will the tables logging modes override Tablespaces logging mode and not generate redo ?

Caner

I got the list of logging disabled tables , and it is over 20000 and I have over 30000 logging mode disabled indexes .
So use a script like:
SQL> select 'alter table '||table_name||' logging;'
2 from user_tables
3 where logging = 'NO';
Will the tables logging modes override Tablespaces logging mode and not generate redo ?
Table logging overrides tablespace logging mode. Tablespace logging mode is just a default for the tables.

Kind regards,
Dick

Table logging overrides tablespace logging mode. Tablespace logging mode is just a default for the tables.

You are right about this. But in Archive log mode this does not include delete , insert , update operations
I did some tests on my test server.
I created some tables with logging disabled on a logging enabled TBS and on a logging disabled TBS.

inserted some rows to these tables. and backedup the DB with rman, and inserted some more rows.
shutdown the DB and deleted related Tablespaces (datafiles).

mount the database , restore TBS with rman , recover TBS with rman. open the database and ALL the rows I inserted into logging disabled tables and logging disabled TBS came back.

I asked this to our local support center and they said that When using DB in ARCHIVE mode update, insert, delete operations are logged in redo's except alter operations.


Kind regards

Caner

Here is answer from Oracle metalink;



Although you can set the NOLOGGING attribute for a table, partition, index, or
tablespace, no-logging mode does not apply to every operation performed on the
schema object for which you set the NOLOGGING attribute. Only the following
operations can make use of no-logging mode:

direct load (SQL*Loader)

direct-load INSERT

CREATE TABLE ... AS SELECT

CREATE INDEX

ALTER TABLE ... MOVE PARTITION

ALTER TABLE ... SPLIT PARTITION

ALTER INDEX ... SPLIT PARTITION

ALTER INDEX ... REBUILD

ALTER INDEX ... REBUILD PARTITION

INSERT, UPDATE, and DELETE on LOBs in NOCACHE NOLOGGING mode stored out of line

All of these SQL statements can be parallelized. They can execute in logging or
no-logging mode for both serial and parallel execution.

Other SQL statements are unaffected by the NOLOGGING attribute of the schema
object. For example, the following SQL statements are unaffected by NOLOGGING
mode: UPDATE and DELETE (except on some LOBs, as noted above), conventional
path INSERT, and various DDL statements not listed above.

even in the case of say direct insert ( where no logging is applicable)

Some minimal logging is still done for marking new extents invalid, and
dictionary changes are always fully logged


If you have no further clarification to be made , please proceed to close the
tar