What is LOGGING,NOLOGGING and FORCE LOGGING
Nologging:
NOLOGGING can be used to minimize the amount of redo generated by Oracle. NOLOGGING affect the recoverability.
2. CTAS (create table as select)
A tablespace or the entire database is either in force logging or no force logging mode. To see which it is, run:
SQL> SELECT force_logging FROM v$database;
NOLOGGING can be used to minimize the amount of redo generated by Oracle. NOLOGGING affect the recoverability.
Only the following operations can make use of nologging:
1.SQL*Loader in direct mode the +append hint
1.SQL*Loader in direct mode the +append hint
Additionally, a direct load with SQL*Loader and a direct load insert can also make use of nologging. The direct load insert is a special form of the insert statement that uses the /*+ append */ hint.
Note: Even though direct path load reduces the generation of redo, it is not totally eliminated. That's because those inserts still generate undo which in turn generates redo.
Note: Even though direct path load reduces the generation of redo, it is not totally eliminated. That's because those inserts still generate undo which in turn generates redo.
That means that ordinary inserts, updates and deletes always generate redo, no matter if the underlying table or index is specifed with nologging or not.
If there is an index on the table, and an +append insert is made on the
table, the indexes will produce redo. This can be circumvented by
setting the index to unusable and altering the session's skip_unusable_indexes to true.
Nologging can be overriden at tablespace level using alter tablespace ... force logging.
Nologging has no effect if the database is in force logging mode (which can be controlled with
alter database force [no] logging mode). 2. CTAS (create table as select)
Create table table_name as select * from table_name;
3.ALTER TABLE statements (move/add/split/merge/modify partitions)
- alter table ... move partition
- alter table ... split partition
- alter table ... add partition (if hash partition)
- alter table ... merge partition
- alter table ... modify partition
coalesce subpartition
rebuild unusable indexes
4.ALTER INDEX statements (move/add/split/merge partitions)
5.Check any datafile or tablespace contains BEGIN backup mode
6. INSERT, UPDATE, and DELETE on LOBs in NOCACHE NOLOGGING mode stored out of line
To create a table in NOLOGGING mode:
SQL> CREATE TABLE t1 (c1 NUMBER) NOLOGGING;
5.Check any datafile or tablespace contains BEGIN backup mode
6. INSERT, UPDATE, and DELETE on LOBs in NOCACHE NOLOGGING mode stored out of line
To create a table in NOLOGGING mode:
SQL> CREATE TABLE t1 (c1 NUMBER) NOLOGGING;
Table created.
To enable NOLOGGING for a table:
ALTER TABLE t1 NOLOGGING;
To enable NOLOGGING for a table:
ALTER TABLE t1 NOLOGGING;
Table altered.
Force logging:
Force logging:
FORCE LOGGING can be used on tablespace or database level to force logging of changes to the redo. This may be required for sites that are mining log data, using Oracle Streams or using Data Guard (standby databases). oracle introduced the ALTER DATABASE FORCE LOGGING command in Oracle 9i R2.
Some data definition language statements (such as CREATE TABLE)
allow the NOLOGGING clause, which causes some database operations not to
generate redo records in the database redo log. The NOLOGGING setting
can speed up operations that can be easily recovered outside of the
database recovery mechanisms, but it can negatively affect media
recovery and standby databases.
Oracle Database lets you force the writing of redo records even when NOLOGGING has been specified in DDL statements. The database never generates redo records for temporary tablespaces and temporary segments, so forced logging has no affect for objects.
Oracle Database lets you force the writing of redo records even when NOLOGGING has been specified in DDL statements. The database never generates redo records for temporary tablespaces and temporary segments, so forced logging has no affect for objects.
A tablespace or the entire database is either in force logging or no force logging mode. To see which it is, run:
SQL> SELECT force_logging FROM v$database;
FOR ---
NO
or
SQL> SELECT tablespace_name, force_logging FROM dba_tablespaces;
or
SQL> SELECT tablespace_name, force_logging FROM dba_tablespaces;
TABLESPACE_NAME FOR ------------------------------ ---
SYSTEM NO ...
To enable force logging:
SQL> ALTER DATABASE force logging;
To enable force logging:
SQL> ALTER DATABASE force logging;
Database altered.
SQL> ALTER TABLESPACE users FORCE LOGGING;
SQL> ALTER TABLESPACE users FORCE LOGGING;
Tablespace altered.
To disable:
SQL> ALTER DATABASE no force logging;
To disable:
SQL> ALTER DATABASE no force logging;
Database altered.
SQL> ALTER TABLESPACE users NO FORCE LOGGING;
SQL> ALTER TABLESPACE users NO FORCE LOGGING;
Tablespace altered.
Note:The database never generates redo records for temporary tablespaces
and temporary segments, so forced logging has no affect for objects.
Controlling the Writing of Redo Records:
For some database operations, you can control whether the database generates redo records. Without redo, no media recovery is possible. However, suppressing redo generation can improve performance, and may be appropriate for easily recoverable operations. An example of such an operation is a CREATE TABLE...AS SELECT statement, which can be repeated in case of database or instance failure.
Specify the NOLOGGING clause in the CREATE TABLESPACE statement if you wish to suppress redo when these operations are performed for objects within the tablespace. If you do not include this clause, or if you specify LOGGING instead, then the database generates redo when changes are made to objects in the tablespace. Redo is never generated for temporary segments or in temporary tablespaces, regardless of the logging attribute.
The logging attribute specified at the tablespace level is the default attribute for objects created within the tablespace. You can override this default logging attribute by specifying LOGGING or NOLOGGING at the schema object level--for example, in a CREATE TABLE statement.
If you have a standby database, NOLOGGING mode causes problems with the availability and accuracy of the standby database. To overcome this problem, you can specify FORCE LOGGING mode. When you include the FORCE LOGGING clause in the CREATE TABLESPACE statement, you force the generation of redo records for all operations that make changes to objects in a tablespace. This overrides any specification made at the object level.
If you transport a tablespace that is in FORCE LOGGING mode to another database, the new tablespace will not maintain the FORCE LOGGING mode.
For some database operations, you can control whether the database generates redo records. Without redo, no media recovery is possible. However, suppressing redo generation can improve performance, and may be appropriate for easily recoverable operations. An example of such an operation is a CREATE TABLE...AS SELECT statement, which can be repeated in case of database or instance failure.
Specify the NOLOGGING clause in the CREATE TABLESPACE statement if you wish to suppress redo when these operations are performed for objects within the tablespace. If you do not include this clause, or if you specify LOGGING instead, then the database generates redo when changes are made to objects in the tablespace. Redo is never generated for temporary segments or in temporary tablespaces, regardless of the logging attribute.
The logging attribute specified at the tablespace level is the default attribute for objects created within the tablespace. You can override this default logging attribute by specifying LOGGING or NOLOGGING at the schema object level--for example, in a CREATE TABLE statement.
If you have a standby database, NOLOGGING mode causes problems with the availability and accuracy of the standby database. To overcome this problem, you can specify FORCE LOGGING mode. When you include the FORCE LOGGING clause in the CREATE TABLESPACE statement, you force the generation of redo records for all operations that make changes to objects in a tablespace. This overrides any specification made at the object level.
If you transport a tablespace that is in FORCE LOGGING mode to another database, the new tablespace will not maintain the FORCE LOGGING mode.
Comments
Post a Comment