nologging in primary - how to maintain standby

[rajabalachandra]

hi,

i have a question.

consider the scenario:

i am the dba.

i have a primary database and a standby database.

my standby database is updated with the archived logs
from the primary database.

if any of my users update the primary database with
nologging option, the standby database becomes outdated.

is there any privilege to prevent the users from using
the nologging option?

or

is there any query to identify that an user has updated
with nologging option.
(at what time or which datafiles/tablespace got affected).


-Raja

[Kutrovsky]

You can use v$datafile -> UNRECOVERABLE_TIME to see the files that needs to be re-transfered to the standby site.

In 9i you can use "alter tablespace force logging;" to prevent users from making nologging changes.

Cheers,

[jmodic]

You would probably want to force the logging on the whole database, not only on particular tablespaces. So this wariation of the above command is probably more handy:

alter DATABASE force logging;

[rajabalachandra]

hi,

thank you for the response!

is there any option in oracle8i to force logging?

-Raja

[hrishy]

Hi

In 8i..you do not have this option

regards
Hrishy

[julian]

Originally posted by jmodic
You would probably want to force the logging on the whole database, not only on particular tablespaces. So this wariation of the above command is probably more handy:

alter DATABASE force logging;

BTW, does anyone know how to undo that command?

[jmodic]

Originally posted by julian
BTW, does anyone know how to undo that command?

In SQL Manual it says you can use the same command with "NO" option to reverse the effect of FORCE LOGGING, eg

 alter database NO force logging;

Did you have any problems with the sugested method? I have tested it and it behaved quite as expected.

Anyway, if the above does not work for some reason, somewhat more "brute force" method would be to recreate the controlfile without FORCE LOGGING option in it.

[julian]

I was not aware of the correct syntax. Thanks.