Create table with NOLOGGING

[sukimac]

We are about to implement a standby database as part of backup/recovery strategy. Based on the advice of our consultant, tables have been created with the NOLOGGING attribute. CREATE TABLE NOLOGGING will not be propagated to the Standby db. I understand that NOLOGGING means that the creation of the table (and any indexes created b/c of constraints) will not be logged in the redo log files. What are the disadvantages to allow LOGGING? Thanks for the help.

[jmodic]

If you cretae table with nologging option the CREATE TABLE command will be propagated to your standby database and the table will be created there, however the rows you inserted in your primary database *during the table creation* (NOLOGGING makes sence only if you create table with "CREATE TABLE .... AS SELECT ...") will not be propagated to your standby, so your table will be empty initially at your standby database. All rows that will be inserted afterwards will be propagated to your standby database normaly.

About disadvantages of allowing logging: redo logs will be created for initialy inserted rows, that is the only disadvantige. If you can afford to reload them in case of media recovery, then nologging is OK. But in your case, where you plan to use standby database, I would avoid any nologging or direct load operation.

HTH,