• Oracle 联机日志文件及归档文件

    管理联机日志文件：

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

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

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

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

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

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

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

    查看日志组的工作状态：

    select * from v$log;

    1. SQL> set pagesize 200
    2. SQL> set linesize 200
    3. SQL> 
    4. SQL> select * from v$log;
    5.  
    6.     GROUP#    THREAD#  SEQUENCE#      BYTES  BLOCKSIZE    MEMBERS ARC STATUS           FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME     CON_ID
    7. ---------- ---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- --------- ------------ --------- ----------
    8.          1          1         49   52428800        512          1 NO  INACTIVE              17377140 20-NOV-22     17377187 20-NOV-22          0
    9.          2          1         50   52428800        512          1 NO  INACTIVE              17377187 20-NOV-22     17401476 20-NOV-22          0
    10.          3          1         51   52428800        512          1 NO  CURRENT               17401476 20-NOV-22   1.8447E+19                    0
    11.  
    12. SQL> col NEXT_CHANGE# for 999999999999999999999999999999999
    13. SQL> select * from v$log;
    14.  
    15.     GROUP#    THREAD#  SEQUENCE#      BYTES  BLOCKSIZE    MEMBERS ARC STATUS           FIRST_CHANGE# FIRST_TIM                       NEXT_CHANGE# NEXT_TIME     CON_ID
    16. ---------- ---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- --------- ---------------------------------- --------- ----------
    17.          1          1         49   52428800        512          1 NO  INACTIVE              17377140 20-NOV-22                           17377187 20-NOV-22          0
    18.          2          1         50   52428800        512          1 NO  INACTIVE              17377187 20-NOV-22                           17401476 20-NOV-22          0
    19.          3          1         51   52428800        512          1 NO  CURRENT               17401476 20-NOV-22               18446744073709551615                    0
    20.  
    21. SQL>

    查看日志的物理信息：

    select * from v$logfile;

    1.  
    2. SQL> 
    3. SQL> select * from v$logfile;
    4.  
    5.     GROUP# STATUS  TYPE
    6. ---------- ------- -------
    7. MEMBER
    8. --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
    9. IS_     CON_ID
    10. --- ----------
    11.          3         ONLINE
    12. /u02/oradata/CDB1/redo03.log
    13. NO           0
    14.  
    15.          2         ONLINE
    16. /u02/oradata/CDB1/redo02.log
    17. NO           0
    18.  
    19.          1         ONLINE
    20. /u02/oradata/CDB1/redo01.log
    21. NO           0
    22.  
    23.  
    24. 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]$

    日志切换的历史：

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

    1. alter database add logfile '/u02/oradata/CDB1/redo04.log' size 100M;
    2. alter database add logfile '/u02/oradata/CDB1/redo05.log' size 100M;
    1. [oracle@oracle-db-19c admin]$ sqlplus / as sysdba
    2.  
    3. SQL*Plus: Release 19.0.0.0.0 - Production on Mon Nov 21 14:35:04 2022
    4. Version 19.3.0.0.0
    5.  
    6. Copyright (c) 1982, 2019, Oracle.  All rights reserved.
    7.  
    8.  
    9. Connected to:
    10. Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
    11. Version 19.3.0.0.0
    12.  
    13. SQL> set pagesize 200
    14. SQL> set linesize 200
    15. SQL> 
    16. SQL> column STATUS for a15
    17. SQL> column TYPE for a15
    18. SQL> column MEMBER for a30
    19. SQL>  select * from v$logfile;
    20.  
    21.     GROUP# STATUS          TYPE            MEMBER                         IS_     CON_ID
    22. ---------- --------------- --------------- ------------------------------ --- ----------
    23.          3                 ONLINE          /u02/oradata/CDB1/redo03.log   NO           0
    24.          2                 ONLINE          /u02/oradata/CDB1/redo02.log   NO           0
    25.          1                 ONLINE          /u02/oradata/CDB1/redo01.log   NO           0
    26.  
    27. SQL> alter database add logfile '/u02/oradata/CDB1/redo04.log' size 100m;
    28.  
    29. Database altered.
    30.  
    31. SQL> alter database add logfile '/u02/oradata/CDB1/redo05.log' size 100m;
    32.  
    33. Database altered.
    34.  
    35. SQL>  select * from v$logfile;
    36.  
    37.     GROUP# STATUS          TYPE            MEMBER                         IS_     CON_ID
    38. ---------- --------------- --------------- ------------------------------ --- ----------
    39.          3                 ONLINE          /u02/oradata/CDB1/redo03.log   NO           0
    40.          2                 ONLINE          /u02/oradata/CDB1/redo02.log   NO           0
    41.          1                 ONLINE          /u02/oradata/CDB1/redo01.log   NO           0
    42.          4                 ONLINE          /u02/oradata/CDB1/redo04.log   NO           0
    43.          5                 ONLINE          /u02/oradata/CDB1/redo05.log   NO           0
    44.  
    45. SQL> select * from v$log;
    46.  
    47.     GROUP#    THREAD#  SEQUENCE#      BYTES  BLOCKSIZE    MEMBERS ARC STATUS          FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME     CON_ID
    48. ---------- ---------- ---------- ---------- ---------- ---------- --- --------------- ------------- --------- ------------ --------- ----------
    49.          1          1         52   52428800        512          1 NO  CURRENT              17441850 21-NOV-22   1.8447E+19                    0
    50.          2          1         50   52428800        512          1 NO  INACTIVE             17377187 20-NOV-22     17401476 20-NOV-22          0
    51.          3          1         51   52428800        512          1 NO  INACTIVE             17401476 20-NOV-22     17441850 21-NOV-22          0
    52.          4          1          0  104857600        512          1 YES UNUSED                      0                      0                    0
    53.          5          1          0  104857600        512          1 YES UNUSED                      0                      0                    0
    54.  
    55. SQL> alter system switch logfile;
    56.  
    57. System altered.
    58.  
    59. SQL> select * from v$log;
    60.  
    61.     GROUP#    THREAD#  SEQUENCE#      BYTES  BLOCKSIZE    MEMBERS ARC STATUS          FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME     CON_ID
    62. ---------- ---------- ---------- ---------- ---------- ---------- --- --------------- ------------- --------- ------------ --------- ----------
    63.          1          1         52   52428800        512          1 NO  ACTIVE               17441850 21-NOV-22     17444860 21-NOV-22          0
    64.          2          1         50   52428800        512          1 NO  INACTIVE             17377187 20-NOV-22     17401476 20-NOV-22          0
    65.          3          1         51   52428800        512          1 NO  INACTIVE             17401476 20-NOV-22     17441850 21-NOV-22          0
    66.          4          1         53  104857600        512          1 NO  CURRENT              17444860 21-NOV-22   1.8447E+19                    0
    67.          5          1          0  104857600        512          1 YES UNUSED                      0                      0                    0
    68.  
    69. SQL> alter system checkpoint;
    70.  
    71. System altered.
    72.  
    73. SQL> select * from v$log;
    74.  
    75.     GROUP#    THREAD#  SEQUENCE#      BYTES  BLOCKSIZE    MEMBERS ARC STATUS          FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME     CON_ID
    76. ---------- ---------- ---------- ---------- ---------- ---------- --- --------------- ------------- --------- ------------ --------- ----------
    77.          1          1         52   52428800        512          1 NO  INACTIVE             17441850 21-NOV-22     17444860 21-NOV-22          0
    78.          2          1         50   52428800        512          1 NO  INACTIVE             17377187 20-NOV-22     17401476 20-NOV-22          0
    79.          3          1         51   52428800        512          1 NO  INACTIVE             17401476 20-NOV-22     17441850 21-NOV-22          0
    80.          4          1         53  104857600        512          1 NO  CURRENT              17444860 21-NOV-22   1.8447E+19                    0
    81.          5          1          0  104857600        512          1 YES UNUSED                      0                      0                    0
    82.  
    83. SQL> alter database drop logfile group 1;
    84.  
    85. Database altered.
    86.  
    87. SQL> alter database drop logfile group 2;
    88.  
    89. Database altered.
    90.  
    91. SQL> alter database drop logfile group 3;
    92.  
    93. Database altered.
    94.  
    95. SQL> select * from v$log;
    96.  
    97.     GROUP#    THREAD#  SEQUENCE#      BYTES  BLOCKSIZE    MEMBERS ARC STATUS          FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME     CON_ID
    98. ---------- ---------- ---------- ---------- ---------- ---------- --- --------------- ------------- --------- ------------ --------- ----------
    99.          4          1         53  104857600        512          1 NO  CURRENT              17444860 21-NOV-22   1.8447E+19                    0
    100.          5          1          0  104857600        512          1 YES UNUSED                      0                      0                    0
    101.  
    102. SQL> show con_name;
    103.  
    104. CON_NAME
    105. ------------------------------
    106. CDB$ROOT
    107. SQL>

    删除无用组：

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

    移动日志文件

    1.数据库要mount

    1. shutdown immediate
    2. 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;

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

    1. alter database add logfile member '/u02/oradata/CDB1/redo04a.log' to group 4;
    2. alter database add logfile member '/u02/oradata/CDB1/redo05a.log' to group 5;

    1. SQL> select * from v$log;
    2.  
    3.     GROUP#    THREAD#  SEQUENCE#      BYTES  BLOCKSIZE    MEMBERS ARC STATUS          FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME     CON_ID
    4. ---------- ---------- ---------- ---------- ---------- ---------- --- --------------- ------------- --------- ------------ --------- ----------
    5.          4          1         53  104857600        512          1 NO  CURRENT              17444860 21-NOV-22   1.8447E+19                    0
    6.          5          1          0  104857600        512          1 YES UNUSED                      0                      0                    0
    7.  
    8. SQL> select * from v$logfile;
    9.  
    10.     GROUP# STATUS          TYPE            MEMBER                         IS_     CON_ID
    11. ---------- --------------- --------------- ------------------------------ --- ----------
    12.          4                 ONLINE          /u02/oradata/CDB1/redo04.log   NO           0
    13.          5                 ONLINE          /u02/oradata/CDB1/redo05.log   NO           0
    14.  
    15. SQL> alter database add logfile '/u02/oradata/CDB1/redo06.log' size 100m;
    16.  
    17. Database altered.
    18.  
    19. SQL> select * from v$log;
    20.  
    21.     GROUP#    THREAD#  SEQUENCE#      BYTES  BLOCKSIZE    MEMBERS ARC STATUS          FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME     CON_ID
    22. ---------- ---------- ---------- ---------- ---------- ---------- --- --------------- ------------- --------- ------------ --------- ----------
    23.          1          1          0  104857600        512          1 YES UNUSED                      0                      0                    0
    24.          4          1         53  104857600        512          1 NO  CURRENT              17444860 21-NOV-22   1.8447E+19                    0
    25.          5          1          0  104857600        512          1 YES UNUSED                      0                      0                    0
    26.  
    27. SQL> select * from v$logfile;
    28.  
    29.     GROUP# STATUS          TYPE            MEMBER                         IS_     CON_ID
    30. ---------- --------------- --------------- ------------------------------ --- ----------
    31.          1                 ONLINE          /u02/oradata/CDB1/redo06.log   NO           0
    32.          4                 ONLINE          /u02/oradata/CDB1/redo04.log   NO           0
    33.          5                 ONLINE          /u02/oradata/CDB1/redo05.log   NO           0
    34.  
    35. SQL> 
    36.  
    37. SQL> alter database add logfile member '/u02/oradata/CDB1/redo01.log' to group 1;
    38.  
    39. Database altered.
    40.  
    41. SQL> select * from v$logfile;
    42.  
    43.     GROUP# STATUS          TYPE            MEMBER                         IS_     CON_ID
    44. ---------- --------------- --------------- ------------------------------ --- ----------
    45.          1                 ONLINE          /u02/oradata/CDB1/redo06.log   NO           0
    46.          1 INVALID         ONLINE          /u02/oradata/CDB1/redo01.log   NO           0
    47.          4                 ONLINE          /u02/oradata/CDB1/redo04.log   NO           0
    48.          5                 ONLINE          /u02/oradata/CDB1/redo05.log   NO           0
    49.  
    50. SQL> 
    51.  
    52. SQL> select * from v$log;
    53.  
    54.     GROUP#    THREAD#  SEQUENCE#      BYTES  BLOCKSIZE    MEMBERS ARC STATUS          FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME     CON_ID
    55. ---------- ---------- ---------- ---------- ---------- ---------- --- --------------- ------------- --------- ------------ --------- ----------
    56.          1          1          0  104857600        512          2 YES UNUSED                      0                      0                    0
    57.          4          1         53  104857600        512          1 NO  CURRENT              17444860 21-NOV-22   1.8447E+19                    0
    58.          5          1          0  104857600        512          1 YES UNUSED                      0                      0                    0
    59.  
    60. SQL> alter database add logfile member '/u02/oradata/CDB1/redo04b.log' to group 4,'/u02/oradata/CDB1/redo05b.log' to group 5;
    61.  
    62. Database altered.
    63.  
    64. SQL> select * from v$log;
    65.  
    66.     GROUP#    THREAD#  SEQUENCE#      BYTES  BLOCKSIZE    MEMBERS ARC STATUS          FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME     CON_ID
    67. ---------- ---------- ---------- ---------- ---------- ---------- --- --------------- ------------- --------- ------------ --------- ----------
    68.          1          1          0  104857600        512          2 YES UNUSED                      0                      0                    0
    69.          4          1         53  104857600        512          2 NO  CURRENT              17444860 21-NOV-22   1.8447E+19                    0
    70.          5          1          0  104857600        512          2 YES UNUSED                      0                      0                    0
    71.  
    72. SQL> select * from v$logfile;
    73.  
    74.     GROUP# STATUS          TYPE            MEMBER                         IS_     CON_ID
    75. ---------- --------------- --------------- ------------------------------ --- ----------
    76.          1                 ONLINE          /u02/oradata/CDB1/redo06.log   NO           0
    77.          1 INVALID         ONLINE          /u02/oradata/CDB1/redo01.log   NO           0
    78.          4 INVALID         ONLINE          /u02/oradata/CDB1/redo04b.log  NO           0
    79.          4                 ONLINE          /u02/oradata/CDB1/redo04.log   NO           0
    80.          5                 ONLINE          /u02/oradata/CDB1/redo05.log   NO           0
    81.          5 INVALID         ONLINE          /u02/oradata/CDB1/redo05b.log  NO           0
    82.  
    83. 6 rows selected.
    84.  
    85. SQL> alter system switch logfile;
    86.  
    87. System altered.
    88.  
    89. SQL> /
    90.  
    91. System altered.
    92.  
    93. SQL> /
    94.  
    95. System altered.
    96.  
    97. SQL> select * from v$logfile;
    98.  
    99.     GROUP# STATUS          TYPE            MEMBER                         IS_     CON_ID
    100. ---------- --------------- --------------- ------------------------------ --- ----------
    101.          1                 ONLINE          /u02/oradata/CDB1/redo06.log   NO           0
    102.          1                 ONLINE          /u02/oradata/CDB1/redo01.log   NO           0
    103.          4                 ONLINE          /u02/oradata/CDB1/redo04b.log  NO           0
    104.          4                 ONLINE          /u02/oradata/CDB1/redo04.log   NO           0
    105.          5                 ONLINE          /u02/oradata/CDB1/redo05.log   NO           0
    106.          5                 ONLINE          /u02/oradata/CDB1/redo05b.log  NO           0
    107.  
    108. 6 rows selected.
    109.  
    110. SQL>

    数据库的归档模式：

    查看数据库归档是否

    1. archive log list
    2. select log_mode from v$database;
    1. SQL> 
    2. SQL> archive log list
    3. Database log mode              No Archive Mode
    4. Automatic archival             Disabled
    5. Archive destination            /u01/app/oracle/product/19.3.0/dbhome_1/dbs/arch
    6. Oldest online log sequence     65
    7. Current log sequence           67
    8. SQL>

     如何从非归档模式到归档模式：

    • Step1: 正常停库
    • Step2:启动到mount模式下
    • Step3:执行alter database archivelog命令
    • Step4:将数据库打开为open状态
    • Step5:进行 日志文件转换 alter system switch logfile;
    • Step6:去到归档日志路径下，/u01/app/oracle/product/19.3.0/dbhome_1/dbs/arch 执行tree命令。如下所示：
    1. SQL> archive log list
    2. Database log mode              No Archive Mode
    3. Automatic archival             Disabled
    4. Archive destination            /u01/app/oracle/product/19.3.0/dbhome_1/dbs/arch
    5. Oldest online log sequence     65
    6. Current log sequence           67
    7. SQL> 
    8. SQL> 
    9. SQL> shutdown immediate
    10. Database closed.
    11. Database dismounted.
    12. ORACLE instance shut down.
    13. SQL> 
    14. SQL> startup mount
    15. ORACLE instance started.
    16.  
    17. Total System Global Area  629145392 bytes
    18. Fixed Size                  9137968 bytes
    19. Variable Size             197132288 bytes
    20. Database Buffers          415236096 bytes
    21. Redo Buffers                7639040 bytes
    22. Database mounted.
    23. SQL> alter database archivelog;
    24.  
    25. Database altered.
    26.  
    27. SQL> alter database open;
    28.  
    29. Database altered.
    30.  
    31. SQL> archive log list
    32. Database log mode              Archive Mode
    33. Automatic archival             Enabled
    34. Archive destination            /u01/app/oracle/product/19.3.0/dbhome_1/dbs/arch
    35. Oldest online log sequence     65
    36. Next log sequence to archive   67
    37. Current log sequence           67
    38. SQL> 
    39.  
    40. SQL> 
    41. SQL> select * from v$log;
    42.  
    43.     GROUP#    THREAD#  SEQUENCE#      BYTES  BLOCKSIZE    MEMBERS ARC
    44. ---------- ---------- ---------- ---------- ---------- ---------- ---
    45. STATUS           FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME     CON_ID
    46. ---------------- ------------- --------- ------------ --------- ----------
    47.          1          1         66  104857600        512          2 YES
    48. INACTIVE              17557877 21-NOV-22     17631852 22-NOV-22          0
    49.  
    50.          4          1         65  104857600        512          2 YES
    51. INACTIVE              17557842 21-NOV-22     17557877 21-NOV-22          0
    52.  
    53.          5          1         67  104857600        512          2 NO
    54. CURRENT               17631852 22-NOV-22   1.8447E+19                    0
    55.  
    56.  
    57. SQL> 
    58. SQL> alter system switch logfile;
    59.  
    60. System altered.
    61.  
    62. SQL> alter system switch logfile;
    63.  
    64. System altered.
    65.  
    66. SQL> select * from v$log;
    67.  
    68.     GROUP#    THREAD#  SEQUENCE#      BYTES  BLOCKSIZE    MEMBERS ARC
    69. ---------- ---------- ---------- ---------- ---------- ---------- ---
    70. STATUS           FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME     CON_ID
    71. ---------------- ------------- --------- ------------ --------- ----------
    72.          1          1         69  104857600        512          2 NO
    73. CURRENT               17641141 22-NOV-22   1.8447E+19                    0
    74.  
    75.          4          1         68  104857600        512          2 YES
    76. ACTIVE                17641099 22-NOV-22     17641141 22-NOV-22          0
    77.  
    78.          5          1         67  104857600        512          2 YES
    79. ACTIVE                17631852 22-NOV-22     17641099 22-NOV-22          0
    80.  
    81.  
    82. SQL> 
    83.  
    84.  
    85. [oracle@oracle-db-19c dbs]$ tree
    86. .
    87. ├── arch
    88. │   ├── 1_68_1119711914.dbf
    89. │   └── 1_67_1119711914.dbf
    90. ├── hc_cdb1.dat
    91. ├── init.ora
    92. ├── lkCDB1
    93. ├── orapwcdb1
    94. ├── orapwcdb1_bkp20221119
    95. └── spfilecdb1.ora
    96.  
    97. 1 directory, 8 files
    98. [oracle@oracle-db-19c dbs]$

    打开归档：

    1. shutdown immediate
    2. startup mount

    修改存档的终点：

    1. SQL> archive log list
    2. Database log mode              Archive Mode
    3. Automatic archival             Enabled
    4. Archive destination            /u01/app/oracle/product/19.3.0/dbhome_1/dbs/arch
    5. Oldest online log sequence     67
    6. Next log sequence to archive   69
    7. Current log sequence           69
    8. SQL> 
    9. SQL> alter system set log_archive_dest_1='location=/u01/app/oracle/product/19.3.0/dbhome_1/dbs/arc_maxwell_dest1/';
    10.  
    11. System altered.
    12.  
    13. SQL> alter system switch logfile;
    14.  
    15. System altered.
    16.  
    17. SQL> 
    18.  
    19. [oracle@oracle-db-19c dbs]$ tree
    20. .
    21. ├── arch
    22. │   ├── 1_67_1119711914.dbf
    23. │   └── 1_68_1119711914.dbf
    24. ├── arc_maxwell_dest1
    25. │   └── 1_69_1119711914.dbf
    26. ├── hc_cdb1.dat
    27. ├── init.ora
    28. ├── lkCDB1
    29. ├── orapwcdb1
    30. ├── orapwcdb1_bkp20221119
    31. └── spfilecdb1.ora
    32.  
    33. 2 directories, 9 files
    34. [oracle@oracle-db-19c dbs]$ pwd
    35. /u01/app/oracle/product/19.3.0/dbhome_1/dbs
    36. [oracle@oracle-db-19c dbs]$

    手动归档：

    alter system archive log sequence 67  to '/u01/app/oracle/product/19.3.0/dbhome_1/dbs/arc_maxwell_dest1/';

     

    --查看归档
    -- v$archived_log

    -- 查看归档路径
    -- v$archive_dest

  • 相关阅读:
    盘点常见的动态内存的错误
    1panel 搭建多个网站
    mysql学习笔记-底层原理详解
    〖Python网络爬虫实战㊲〗- JavaScript 逆向实战（一）
    【leetcode】不含重复字符的最长子字符串
    Vue中的生命周期
    Java Web 33道面试题汇总
    unity3d客户端框架 基于类对象池的可回收变量 代码实现
    【Qt控件之QTableWidget】使用及技巧
    成都瀚网科技有限公司：抖店精选联盟怎么用？
  • 原文地址：https://blog.csdn.net/u011868279/article/details/127962644