Shulou(Shulou.com)05/31 Report--

This article introduces the relevant knowledge of "what are the database logging modes". In the operation process of actual cases, many people will encounter such difficulties. Next, let Xiaobian lead you to learn how to deal with these situations! I hope you can read carefully and learn something!

I. Log recording mode (LOGGING, FORCE LOGGING, NOLOGGING)1. Meaning of the three

LOGGING: Log information to the online redo log file when a database object is created. LOGGING is actually an attribute of an object that indicates whether REDO logs are logged when the object is created, including whether REDO logs are logged when DML is done. NOLOGGING is generally not recommended on tables, but can be used when creating indexes or doing large data imports.

FORCE LOGGING: In short, force logging, that is, generating log information for all operations in the database and writing that information to an online redo log file.

NOLOGGING: As opposed to LOGGING and FORCE LOGGING, log as little information as possible to an online log file.

FORCE LOGGING can be set at the database level, at the table space level, and LOGGING and NOLOGGING can be set at the data object level.

Force logging mode is required when using DATA GUARD. Note: FORCE LOGGING does not record more logs than general LOGGING, database in FORCE LOGGING

The NOLOGGING option will not be valid in the state because NOLOGGING will destroy the recoverability of DATAGUARD.FORCE LOGGING forces the database to log in any state.

2. Relationship to archive and non-archive modes

There is no equivalence between logging mode and archiving mode, which refers to whether logs generated by the system are archived or not.

In archive mode, logs are recorded to log files and archived. In non-archiving mode, logs are also recorded to log files, but they are not archived and are easy to lose logs.

Logging mode, whether archived or non-archived, allows logging, forced logging, or less logging.

LOGGING or FORCE LOGGING in archive mode supports media recovery, while NOLOGGING mode does not.

NOLOGGING mode operation produces significantly less logging than LOGGING mode, i.e. NOLOGGING mode minimizes logging.

3. Priority:

When the database uses FORCE LOGGING, it has the highest priority, followed by FORCE LOGGING at the table space level. That is, when NOLOGGING is specified for an object and FORCE LOGGING is the logging mode at the table space or database level, this option does not work until FORCE LOGGING at the table space or database level is removed.

It is generally recommended to set the entire database to FORCE LOGGING or FORCE LOGGING based on the table space level, rather than both.

When a database or table space uses non-enforced logging mode, journaling priorities from low to high are: database, table space, data object

II. Usage of the three 1.LOGGING mode

This is the default mode for logging, regardless of whether the database is in archive mode or not, and does not change the default logging mode at the table space and object level.

For temporary tablespaces no logging will be done to the online redo log file.

2. NOLOGGING mode

This pattern is not about not logging, but minimizing the amount of logging generated. NOLOGGING is typically used in the following situations

SQL*Loader in direct mode

INSERT /*+APPEND*/ ...

CTAS

ALTER TABLE statements (move/add/split/merge partitions)

CREATE INDEX

ALTER INDEX statements (move/add/split/merge partitions)

NOLOGGING is related to table schema, insert schema, database runtime schema (archived/unarchived):

The following describes only the log generation when inserting data in append mode, such as inserting data in the following mode

insert /*+append+*/ into tb_name select colnam1,colname2 from table_name;

Database in archive mode

When the table schema is logging, redo is generated regardless of whether append schema is used. When the table schema is nlogging, redo is not generated except append schema.

Database is in non-archive mode

In either logging or nologing mode, append does not generate redo, while no append generates redo.

2. FORCE LOGGING

When the database is set to Force Logging mode, the database records all operations except temporary tablespaces or temporary rollback segments, ignoring specified parameters such as NOLOGGING.

Modify it by doing the following

ALTER DATABASE FORCE LOGGING;

ALTER TABLESPACE tablespace_name FORCE LOGGING;

Cancel FORCE LOGGING mode

ALTER DATABASE NO FORCE LOGGING;

ALTER TABLESPACE tablespace_name NO FORCE LOGGING;

3. View log patterns at different levels

View archive mode, logging mode for database level logs

SQL> select log_mode,force_logging from v$database;

LOG_MODE FORCE_LOGGING

------------ -------------

ARCHIVELOG NO

View logging patterns at the table space level

SQL> select tablespace_name,logging,force_logging from dba_tablespaces;

TABLESPACE_NAME LOGGING FORCE_LOGGING

--------------- --------- -------------

SYSTEM LOGGING NO

UNDOTBS1 LOGGING NO

SYSAUX LOGGING NO

TEMP NOLOGGING NO

USERS LOGGING NO

View object-level logging patterns

scott@ORCL> select table_name,logging from user_tables;

TABLE_NAME LOG

------------------------------ ---

TB_A YES

TB_B YES

4. Transition of logging mode

a. Database switches from non-mandatory day mode to mandatory log mode

sys@ORCL> alter database force logging;

b. Database switches from forced logging mode to non-forced logging mode

sys@ORCL> alter database no force logging;

c. Tablespace level switches from forced logging mode to non-forced logging mode

sys@ORCL> alter tablespace no force logging;

d. Tablespace level switches from non-forced logging mode to forced logging mode

sys@ORCL> alter tablespace no force logging;

e. Object Level Logging Mode

sys@ORCL> alter table tb_a nlogging; --do not log mode

sys@ORCL> alter table tb_a logging; --Use logging mode

3. LOB segment related to NOLOGGING and LOGGING

Calculate the size of all LOBs:

SELECT SUM(BYTES)/1024/1024/1024 as LOB_Size

FROM user_segments

WHERE segment_type='LOBSEGMENT';

Calculate the size of all nlogs

SELECT SUM(s.BYTES)/1024/1024/1024 as Nologging_size

FROM user_segments s,user_lobs l

WHERE s.segment_type='LOBSEGMENT'

AND l.LOGGING='NO' AND l.SEGMENT_NAME=s.SEGMENT_NAME;

Count the number of nlogging/logging on LOB segments

SELECT COUNT(*) FROM user_lobs WHERE logging='NO';

SELECT COUNT(*) FROM user_lobs WHERE logging='YES';

IV. IMPDP does not write redo1. Close force logging

alter database no force logging;

2. Close the table logging property

alter table scott.t_test nologging;

3.nologfile=Y Import

impdp '\/ as sysdba\' dumpfile=xxx.dmp tables=scott.t_test NOLOGFILE=Y

"What are the database logging patterns" content is introduced here, thank you for reading. If you want to know more about industry-related knowledge, you can pay attention to the website. Xiaobian will output more high-quality practical articles for everyone!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.