Oracle logging (online/offline logs)

View method: select name, log_mode from v $ database;

The Oracle database has a redo log of the connected machine. This log records changes made to the database, such as insertion, deletion, and data update. All these operations are recorded in the online redo log. Generally, a database must have at least two online redo log groups. When an online redo log group is full, log switching occurs. At this time, online redo log group 2 becomes the currently used log. When online redo log group 2 is full, log switching occurs again. Write online redo log group 1, and then repeat it.
If the database is in non-archive mode, online logs are discarded during Switching. In archive mode, logs are archived When switching. For example, currently online redo log 1 is used. When 1 is full, log switching occurs and online redo log 2 is started, at this time, the content of online redo log 1 will be copied to another specified directory. This directory is called the archive directory, and the copied file is called the archive redo log.
You can perform catastrophic recovery only when the database is running in archive mode.
Differences between the archive log mode and the non-archive log Mode
In non-archive mode, only cold backup can be performed, and only full backup can be performed during recovery. data during the last full backup to system error cannot be recovered.
Archive mode supports hot backup, Incremental backup, and partial recovery.
You can use archive log list to check whether the ARCHIVE mode is not the ARCHIVE mode when the ARCHIVE mode is in the status.

Change archive mode to non-archive mode:
1) SQL> SHUTDOWN NORMAL/IMMEDIATE;
2) SQL> START MOUNT;
3) SQL> ALTER DATABASE NOARCHIVELOG;
4) SQL> ALTER DATABASE OPEN;

Enable automatic archiving: LOG_ARCHIVE_START = TRUE
In archive mode, the log file group cannot be overwritten. When the log file is full, if no manual archiving is performed, the system suspends until the archiving is complete.
At this time, you can only read but not write.
Close and restart the archiving log process during running
SQL> ARCHIVE LOG STOP
SQL> ARCHIVE LOG START

Manual archiving: LOG_ARCHIVE_START = FALSE
Archive Current Log File
SQL> ALTER SYSTEM ARCHIVE LOG CURRENT;
Archive log files numbered 052
SQL> ALTER SYSTEM ARCHIVE LOG SEQUENCE 052;
Archive all log files
SQL> ALTER SYSTEM ARCHIVE LOG ALL;
Change the log archiving target
SQL> ALTER SYSTEM ARCHIVE LOG CURRENT TO '& path ';

1 ------------------------------------------------------
A dba told me that no LOG can be written as follows:
Alert tablename nologgin;
Insert into tablename (select ...)
Note: The insert statement must be followed by the 'select... 'statement. Otherwise, the statement is invalid.

2 ------------------------------------------------------
The NOLOGING option does not write redo logs only in direct-load mode. The nomal DML is invalid.

3 ------------------------------------------------------
Alter table tb_txn_log nologging, and then change it back to alter table tb_txn_log logging mode.
Will affect online transactions

4 ------------------------------------------------------
Insert into test1 nologging select * from dba_objects
Alter table test1 nologging
Insert into test1 select * from dba_objects

5 ------------------------------------------------------

Nologging usage
Many friends mistakenly write SQL statements or add NOLOGGING to table attributes, so they can not generate logs. This is a misunderstanding.

During data migration or loading of a large amount of data into the database, the insert or modification of a large amount of data often causes redo log sync to wait, resulting in slow database performance.
Because many of my friends have misunderstandings about NOLOGGING, many people add nologging after SQL when inserting data. This method is used to prevent the operation from collecting birthday records. But no effect.
The usage of nologging is discussed here:

Database operations do not generate redo records in the following situations:
1. When using the direct load method of SQL * load, redo records are not used.
2. Using the direct insert method, that is, using the append method insert (insert append can achieve direct path loading, which is much faster than the conventional loading method. However, when inserting an append statement, the Exclusive lock of the "6" type will block all DML statements on the table. Therefore, exercise caution when running the business. After the append option is used, the insert data is directly added to the end of the table, instead of inserting data in the idle block of the table.
Append increases the data insertion speed.
The function of allocating space on the table's high water level is to stop using the free space in the table's extent.
Append belongs to direct insert. In archive mode, append + table nologging reduces the number of logs, append reduces the number of logs in non-archive mode, and append inserts only produce a small number of undo logs.
Instead of looking for free blocks in freelist, add data directly to table HWM .)
3. create table... as select
4. create index
5. alter table... move partition
6. alter table... split partition
7. alter index... split partition
8. alter index... rebuild
9. alter index... rebuild partition
10. INSERT, UPDATE, and DELETE on LOBs in nocache nologging mode stored out of line