Init parameterRedo, undo & archiving

LOG_BUFFER

What it controls

Size of the redo log buffer.

Know this before you change it

The default is sized from the SGA and is almost always fine. Raise it only if log buffer space waits show up.

Check and change it

1-- Current value on every instance2SELECT inst_id, name, value, isdefault, ismodified3FROM gv$parameter4WHERE name = 'log_buffer';5 6-- Value stored in the spfile7SELECT sid, value FROM v$spparameter WHERE name = 'log_buffer' AND isspecified = 'TRUE';8 9-- Static: saved in the spfile, takes effect after a restart10ALTER SYSTEM SET log_buffer = 64M SCOPE = SPFILE SID = '*';11 12-- Remove it from the spfile to go back to the default at the next restart13ALTER SYSTEM RESET log_buffer SCOPE = SPFILE SID = '*';

Open in the parameter referenceOracle's reference

More in Redo, undo & archiving

• UNDO_RETENTIONSeconds of committed undo Oracle tries to keep, for long queries and Flashback Query.
• UNDO_TABLESPACEWhich undo tablespace the instance uses. Each RAC instance has its own.
• LOG_ARCHIVE_DEST_NWhere archived redo goes: a local location or a Data Guard standby service. Numbered 1 to 31.
• LOG_ARCHIVE_FORMATFile name pattern for archived logs written to a plain directory.
• ARCHIVE_LAG_TARGETForces a log switch after this many seconds, even when the database is quiet.
• FAST_START_MTTR_TARGETTarget seconds for crash recovery. Oracle writes dirty blocks early enough to meet it.