Oracle联机日志文件与归档文件详细介绍

管理联机日志文件:

联机日志文件以组为单位工作

数据库正常工作至少需要2组日志

联机日志记录所有数据块的变化,用来做实例recover

同一组下的成员之间是镜像关系

more情况日志成员写满redo时发生切换

日志切换时优先覆盖sequence#最小的组

成员的位置和数量,由控制文件中的指针决定

查看日志组的工作状态:

select * from v$log;
SQL> set pagesize 200
SQL> set linesize 200
SQL> 
SQL> select * from v$log;
    GROUP#    THREAD#  SEQUENCE#      BYTES  BLOCKSIZE    MEMBERS ARC STATUS           FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME     CON_ID
---------- ---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- --------- ------------ --------- ----------
         1          1         49   52428800        512          1 NO  INACTIVE              17377140 20-NOV-22     17377187 20-NOV-22          0
         2          1         50   52428800        512          1 NO  INACTIVE              17377187 20-NOV-22     17401476 20-NOV-22          0
         3          1         51   52428800        512          1 NO  CURRENT               17401476 20-NOV-22   1.8447E+19                    0
SQL> col NEXT_CHANGE# for 999999999999999999999999999999999
SQL> select * from v$log;
    GROUP#    THREAD#  SEQUENCE#      BYTES  BLOCKSIZE    MEMBERS ARC STATUS           FIRST_CHANGE# FIRST_TIM                       NEXT_CHANGE# NEXT_TIME     CON_ID
---------- ---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- --------- ---------------------------------- --------- ----------
         1          1         49   52428800        512          1 NO  INACTIVE              17377140 20-NOV-22                           17377187 20-NOV-22          0
         2          1         50   52428800        512          1 NO  INACTIVE              17377187 20-NOV-22                           17401476 20-NOV-22          0
         3          1         51   52428800        512          1 NO  CURRENT               17401476 20-NOV-22               18446744073709551615                    0
SQL> 

查看日志的物理信息:

select * from v$logfile;
SQL> 
SQL> select * from v$logfile;
    GROUP# STATUS  TYPE
---------- ------- -------
MEMBER
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
IS_     CON_ID
--- ----------
         3         ONLINE
/u02/oradata/CDB1/redo03.log
NO           0
         2         ONLINE
/u02/oradata/CDB1/redo02.log
NO           0
         1         ONLINE
/u02/oradata/CDB1/redo01.log
NO           0
SQL> 

手工切换日志:

alter system switch logfile;

手工产生检查点:

alter system checkpoint;

Scott/tiger 脚本在系统:

[oracle@oracle-db-19c admin]$ pwd

/u01/app/oracle/product/19.3.0/dbhome_1/rdbms/admin

[oracle@oracle-db-19c admin]$ ls -ltr utlsampl.sql

-rw-r--r--. 1 oracle oinstall 3978 May 29 2017 utlsampl.sql

[oracle@oracle-db-19c admin]$

日志切换的历史:

SQL> 
SQL> select * from v$log_history;
     RECID      STAMP    THREAD#  SEQUENCE# FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# RESETLOGS_CHANGE# RESETLOGS     CON_ID
---------- ---------- ---------- ---------- ------------- --------- ------------ ----------------- --------- ----------
         1 1119712290          1          1       1920977 02-NOV-22      1944454           1920977 02-NOV-22          0
         2 1119712328          1          2       1944454 02-NOV-22      1955924           1920977 02-NOV-22          0
         3 1119712336          1          3       1955924 02-NOV-22      1957140           1920977 02-NOV-22          0
         4 1119712346          1          4       1957140 02-NOV-22      1958419           1920977 02-NOV-22          0
         5 1119712357          1          5       1958419 02-NOV-22      1959722           1920977 02-NOV-22          0
         6 1119712367          1          6       1959722 02-NOV-22      1961083           1920977 02-NOV-22          0
         7 1119712377          1          7       1961083 02-NOV-22      1962537           1920977 02-NOV-22          0
         8 1119712388          1          8       1962537 02-NOV-22      1964005           1920977 02-NOV-22          0
         9 1119712397          1          9       1964005 02-NOV-22      1965452           1920977 02-NOV-22          0
        10 1119712406          1         10       1965452 02-NOV-22      1966859           1920977 02-NOV-22          0
        11 1119712428          1         11       1966859 02-NOV-22      1970703           1920977 02-NOV-22          0
        12 1119712448          1         12       1970703 02-NOV-22      1974659           1920977 02-NOV-22          0
        13 1119712501          1         13       1974659 02-NOV-22      2003600           1920977 02-NOV-22          0
        14 1119712743          1         14       2003600 02-NOV-22      2017766           1920977 02-NOV-22          0
        15 1119712747          1         15       2017766 02-NOV-22      2017835           1920977 02-NOV-22          0
        16 1119712771          1         16       2017835 02-NOV-22      2026749           1920977 02-NOV-22          0
        17 1119712794          1         17       2026749 02-NOV-22      2030586           1920977 02-NOV-22          0
        18 1119712849          1         18       2030586 02-NOV-22      2049115           1920977 02-NOV-22          0
        19 1119713144          1         19       2049115 02-NOV-22      2088868           1920977 02-NOV-22          0
        20 1119713229          1         20       2088868 02-NOV-22      2100727           1920977 02-NOV-22          0
        21 1119713288          1         21       2100727 02-NOV-22      2139342           1920977 02-NOV-22          0
        22 1119713358          1         22       2139342 02-NOV-22      2146949           1920977 02-NOV-22          0
        23 1119713375          1         23       2146949 02-NOV-22      2150697           1920977 02-NOV-22          0
        24 1119713427          1         24       2150697 02-NOV-22      2153047           1920977 02-NOV-22          0
        25 1119713571          1         25       2153047 02-NOV-22      2163312           1920977 02-NOV-22          0
        26 1119713996          1         26       2163312 02-NOV-22      2264654           1920977 02-NOV-22          0
        27 1120428105          1         27       2264654 02-NOV-22      2282920           1920977 02-NOV-22          0
        28 1120428219          1         28       2282920 10-NOV-22      2300480           1920977 02-NOV-22          0
        29 1120428255          1         29       2300480 10-NOV-22      2318708           1920977 02-NOV-22          0
        30 1120831239          1         30       2318708 10-NOV-22      2347108           1920977 02-NOV-22          0
        31 1120831269          1         31       2347108 15-NOV-22      2366475           1920977 02-NOV-22          0
        32 1120850877          1         32       2366475 15-NOV-22      2397054           1920977 02-NOV-22          0
        33 1120917613          1         33       2397054 15-NOV-22      2425816           1920977 02-NOV-22          0
        34 1120938664          1         34       2425816 16-NOV-22      2465509           1920977 02-NOV-22          0
        35 1120980380          1         35       2465509 16-NOV-22      2575796           1920977 02-NOV-22          0
        36 1121000407          1         36       2575796 17-NOV-22      2601035           1920977 02-NOV-22          0
        37 1121014857          1         37       2601035 17-NOV-22      2629640           1920977 02-NOV-22          0
        38 1121086814          1         38       2629640 17-NOV-22      2668852           1920977 02-NOV-22          0
        39 1121089000          1         39       2668852 18-NOV-22      2771290           1920977 02-NOV-22          0
        40 1121102371          1         40       2771290 18-NOV-22     17019560           1920977 02-NOV-22          0
        41 1121161284          1         41      17019560 18-NOV-22     17140444           1920977 02-NOV-22          0
        42 1121161517          1         42      17140444 19-NOV-22     17156193           1920977 02-NOV-22          0
        43 1121164942          1         43      17156193 19-NOV-22     17277271           1920977 02-NOV-22          0
        44 1121180422          1         44      17277271 19-NOV-22     17311973           1920977 02-NOV-22          0
        45 1121249328          1         45      17311973 19-NOV-22     17337542           1920977 02-NOV-22          0
        46 1121250083          1         46      17337542 20-NOV-22     17351079           1920977 02-NOV-22          0
        47 1121263201          1         47      17351079 20-NOV-22     17377098           1920977 02-NOV-22          0
        48 1121263201          1         48      17377098 20-NOV-22     17377140           1920977 02-NOV-22          0
        49 1121263203          1         49      17377140 20-NOV-22     17377187           1920977 02-NOV-22          0
        50 1121281218          1         50      17377187 20-NOV-22     17401476           1920977 02-NOV-22          0
        51 1121349638          1         51      17401476 20-NOV-22     17441850           1920977 02-NOV-22          0
51 rows selected.
SQL>

监控日志切换频率:

select to_char(FIRST_TIME,'yyyymmddhh24') FIRST_TIME,count(*) from v$log_history group by to_char(FIRST_TIME,'yyyymmddhh24') order by 1;

放大logfile成员的尺寸:

alter database add logfile '/u02/oradata/CDB1/redo04.log' size 100M;
alter database add logfile '/u02/oradata/CDB1/redo05.log' size 100M;
[oracle@oracle-db-19c admin]$ sqlplus / as sysdba
SQL*Plus: Release 19.0.0.0.0 - Production on Mon Nov 21 14:35:04 2022
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle.  All rights reserved.
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
SQL> set pagesize 200
SQL> set linesize 200
SQL> 
SQL> column STATUS for a15
SQL> column TYPE for a15
SQL> column MEMBER for a30
SQL>  select * from v$logfile;
    GROUP# STATUS          TYPE            MEMBER                         IS_     CON_ID
---------- --------------- --------------- ------------------------------ --- ----------
         3                 ONLINE          /u02/oradata/CDB1/redo03.log   NO           0
         2                 ONLINE          /u02/oradata/CDB1/redo02.log   NO           0
         1                 ONLINE          /u02/oradata/CDB1/redo01.log   NO           0
SQL> alter database add logfile '/u02/oradata/CDB1/redo04.log' size 100m;
Database altered.
SQL> alter database add logfile '/u02/oradata/CDB1/redo05.log' size 100m;
Database altered.
SQL>  select * from v$logfile;
    GROUP# STATUS          TYPE            MEMBER                         IS_     CON_ID
---------- --------------- --------------- ------------------------------ --- ----------
         3                 ONLINE          /u02/oradata/CDB1/redo03.log   NO           0
         2                 ONLINE          /u02/oradata/CDB1/redo02.log   NO           0
         1                 ONLINE          /u02/oradata/CDB1/redo01.log   NO           0
         4                 ONLINE          /u02/oradata/CDB1/redo04.log   NO           0
         5                 ONLINE          /u02/oradata/CDB1/redo05.log   NO           0
SQL> select * from v$log;
    GROUP#    THREAD#  SEQUENCE#      BYTES  BLOCKSIZE    MEMBERS ARC STATUS          FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME     CON_ID
---------- ---------- ---------- ---------- ---------- ---------- --- --------------- ------------- --------- ------------ --------- ----------
         1          1         52   52428800        512          1 NO  CURRENT              17441850 21-NOV-22   1.8447E+19                    0
         2          1         50   52428800        512          1 NO  INACTIVE             17377187 20-NOV-22     17401476 20-NOV-22          0
         3          1         51   52428800        512          1 NO  INACTIVE             17401476 20-NOV-22     17441850 21-NOV-22          0
         4          1          0  104857600        512          1 YES UNUSED                      0                      0                    0
         5          1          0  104857600        512          1 YES UNUSED                      0                      0                    0
SQL> alter system switch logfile;
System altered.
SQL> select * from v$log;
    GROUP#    THREAD#  SEQUENCE#      BYTES  BLOCKSIZE    MEMBERS ARC STATUS          FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME     CON_ID
---------- ---------- ---------- ---------- ---------- ---------- --- --------------- ------------- --------- ------------ --------- ----------
         1          1         52   52428800        512          1 NO  ACTIVE               17441850 21-NOV-22     17444860 21-NOV-22          0
         2          1         50   52428800        512          1 NO  INACTIVE             17377187 20-NOV-22     17401476 20-NOV-22          0
         3          1         51   52428800        512          1 NO  INACTIVE             17401476 20-NOV-22     17441850 21-NOV-22          0
         4          1         53  104857600        512          1 NO  CURRENT              17444860 21-NOV-22   1.8447E+19                    0
         5          1          0  104857600        512          1 YES UNUSED                      0                      0                    0
SQL> alter system checkpoint;
System altered.
SQL> select * from v$log;
    GROUP#    THREAD#  SEQUENCE#      BYTES  BLOCKSIZE    MEMBERS ARC STATUS          FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME     CON_ID
---------- ---------- ---------- ---------- ---------- ---------- --- --------------- ------------- --------- ------------ --------- ----------
         1          1         52   52428800        512          1 NO  INACTIVE             17441850 21-NOV-22     17444860 21-NOV-22          0
         2          1         50   52428800        512          1 NO  INACTIVE             17377187 20-NOV-22     17401476 20-NOV-22          0
         3          1         51   52428800        512          1 NO  INACTIVE             17401476 20-NOV-22     17441850 21-NOV-22          0
         4          1         53  104857600        512          1 NO  CURRENT              17444860 21-NOV-22   1.8447E+19                    0
         5          1          0  104857600        512          1 YES UNUSED                      0                      0                    0
SQL> alter database drop logfile group 1;
Database altered.
SQL> alter database drop logfile group 2;
Database altered.
SQL> alter database drop logfile group 3;
Database altered.
SQL> select * from v$log;
    GROUP#    THREAD#  SEQUENCE#      BYTES  BLOCKSIZE    MEMBERS ARC STATUS          FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME     CON_ID
---------- ---------- ---------- ---------- ---------- ---------- --- --------------- ------------- --------- ------------ --------- ----------
         4          1         53  104857600        512          1 NO  CURRENT              17444860 21-NOV-22   1.8447E+19                    0
         5          1          0  104857600        512          1 YES UNUSED                      0                      0                    0
SQL> show con_name;
CON_NAME
------------------------------
CDB$ROOT
SQL> 

删除无用组:

alter database drop logfile group 1;
alter database drop logfile group 2;
alter database drop logfile group 3;

移动日志文件

1.数据库要mount

shutdown immediate
startup mount

2.目标文件要存在

mv /u02/oradata/CDB1/redo04.log /home/oracle/redo04.log

3.修改控制文件中的指针

alter database rename file '/u02/oradata/CDB1/redo04.log' to '/home/oracle/redo04.log';

4.打开数据库

alter database open;

日志文件的多路复用:在同一组下使用多个成员,每组当中只由一个成员可用,数据库就可以正常工作。

alter database add logfile member '/u02/oradata/CDB1/redo04a.log' to group 4;
alter database add logfile member '/u02/oradata/CDB1/redo05a.log' to group 5;
SQL> select * from v$log;
    GROUP#    THREAD#  SEQUENCE#      BYTES  BLOCKSIZE    MEMBERS ARC STATUS          FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME     CON_ID
---------- ---------- ---------- ---------- ---------- ---------- --- --------------- ------------- --------- ------------ --------- ----------
         4          1         53  104857600        512          1 NO  CURRENT              17444860 21-NOV-22   1.8447E+19                    0
         5          1          0  104857600        512          1 YES UNUSED                      0                      0                    0
SQL> select * from v$logfile;
    GROUP# STATUS          TYPE            MEMBER                         IS_     CON_ID
---------- --------------- --------------- ------------------------------ --- ----------
         4                 ONLINE          /u02/oradata/CDB1/redo04.log   NO           0
         5                 ONLINE          /u02/oradata/CDB1/redo05.log   NO           0
SQL> alter database add logfile '/u02/oradata/CDB1/redo06.log' size 100m;
Database altered.
SQL> select * from v$log;
    GROUP#    THREAD#  SEQUENCE#      BYTES  BLOCKSIZE    MEMBERS ARC STATUS          FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME     CON_ID
---------- ---------- ---------- ---------- ---------- ---------- --- --------------- ------------- --------- ------------ --------- ----------
         1          1          0  104857600        512          1 YES UNUSED                      0                      0                    0
         4          1         53  104857600        512          1 NO  CURRENT              17444860 21-NOV-22   1.8447E+19                    0
         5          1          0  104857600        512          1 YES UNUSED                      0                      0                    0
SQL> select * from v$logfile;
    GROUP# STATUS          TYPE            MEMBER                         IS_     CON_ID
---------- --------------- --------------- ------------------------------ --- ----------
         1                 ONLINE          /u02/oradata/CDB1/redo06.log   NO           0
         4                 ONLINE          /u02/oradata/CDB1/redo04.log   NO           0
         5                 ONLINE          /u02/oradata/CDB1/redo05.log   NO           0
SQL> 
SQL> alter database add logfile member '/u02/oradata/CDB1/redo01.log' to group 1;
Database altered.
SQL> select * from v$logfile;
    GROUP# STATUS          TYPE            MEMBER                         IS_     CON_ID
---------- --------------- --------------- ------------------------------ --- ----------
         1                 ONLINE          /u02/oradata/CDB1/redo06.log   NO           0
         1 INVALID         ONLINE          /u02/oradata/CDB1/redo01.log   NO           0
         4                 ONLINE          /u02/oradata/CDB1/redo04.log   NO           0
         5                 ONLINE          /u02/oradata/CDB1/redo05.log   NO           0
SQL> 
SQL> select * from v$log;
    GROUP#    THREAD#  SEQUENCE#      BYTES  BLOCKSIZE    MEMBERS ARC STATUS          FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME     CON_ID
---------- ---------- ---------- ---------- ---------- ---------- --- --------------- ------------- --------- ------------ --------- ----------
         1          1          0  104857600        512          2 YES UNUSED                      0                      0                    0
         4          1         53  104857600        512          1 NO  CURRENT              17444860 21-NOV-22   1.8447E+19                    0
         5          1          0  104857600        512          1 YES UNUSED                      0                      0                    0
SQL> alter database add logfile member '/u02/oradata/CDB1/redo04b.log' to group 4,'/u02/oradata/CDB1/redo05b.log' to group 5;
Database altered.
SQL> select * from v$log;
    GROUP#    THREAD#  SEQUENCE#      BYTES  BLOCKSIZE    MEMBERS ARC STATUS          FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME     CON_ID
---------- ---------- ---------- ---------- ---------- ---------- --- --------------- ------------- --------- ------------ --------- ----------
         1          1          0  104857600        512          2 YES UNUSED                      0                      0                    0
         4          1         53  104857600        512          2 NO  CURRENT              17444860 21-NOV-22   1.8447E+19                    0
         5          1          0  104857600        512          2 YES UNUSED                      0                      0                    0
SQL> select * from v$logfile;
    GROUP# STATUS          TYPE            MEMBER                         IS_     CON_ID
---------- --------------- --------------- ------------------------------ --- ----------
         1                 ONLINE          /u02/oradata/CDB1/redo06.log   NO           0
         1 INVALID         ONLINE          /u02/oradata/CDB1/redo01.log   NO           0
         4 INVALID         ONLINE          /u02/oradata/CDB1/redo04b.log  NO           0
         4                 ONLINE          /u02/oradata/CDB1/redo04.log   NO           0
         5                 ONLINE          /u02/oradata/CDB1/redo05.log   NO           0
         5 INVALID         ONLINE          /u02/oradata/CDB1/redo05b.log  NO           0
6 rows selected.
SQL> alter system switch logfile;
System altered.
SQL> /
System altered.
SQL> /
System altered.
SQL> select * from v$logfile;
    GROUP# STATUS          TYPE            MEMBER                         IS_     CON_ID
---------- --------------- --------------- ------------------------------ --- ----------
         1                 ONLINE          /u02/oradata/CDB1/redo06.log   NO           0
         1                 ONLINE          /u02/oradata/CDB1/redo01.log   NO           0
         4                 ONLINE          /u02/oradata/CDB1/redo04b.log  NO           0
         4                 ONLINE          /u02/oradata/CDB1/redo04.log   NO           0
         5                 ONLINE          /u02/oradata/CDB1/redo05.log   NO           0
         5                 ONLINE          /u02/oradata/CDB1/redo05b.log  NO           0
6 rows selected.
SQL> 

数据库的归档模式:

查看数据库归档是否

archive log list
select log_mode from v$database;

打开归档:

shutdown immediate
startup mount

--v$archived_log
--v$archive_dest

到此这篇关于Oracle联机日志文件与归档文件详细介绍的文章就介绍到这了,更多相关Oracle联机日志文件内容请搜索以前的文章或继续浏览下面的相关文章希望大家以后多多支持!

若文章对您有帮助,帮忙点个赞!

0
0
发布时间 2022-11-24 06:05:22
0 条回复(回复会通过微信通知作者)
点击加载更多评论
登录 后再进行评论
(微信扫码即可登录,无需注册)