av一区二区在线观看_亚洲男人的天堂网站_日韩亚洲视频_在线成人免费_欧美日韩精品免费观看视频_久草视

您的位置：首頁技術文章
文章詳情頁

Oracle聯機日志文件與歸檔文件詳細介紹

瀏覽：17日期：2023-03-12 15:25:37

管理聯機日志文件：

聯機日志文件以組為單位工作

數據庫正常工作至少需要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   52428800512  1 NO  INACTIVE      17377140 20-NOV-22     17377187 20-NOV-22  0
 2  1 50   52428800512  1 NO  INACTIVE      17377187 20-NOV-22     17401476 20-NOV-22  0
 3  1 51   52428800512  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   52428800512  1 NO  INACTIVE      17377140 20-NOV-22   17377187 20-NOV-22  0
 2  1 50   52428800512  1 NO  INACTIVE      17377187 20-NOV-22   17401476 20-NOV-22  0
 3  1 51   52428800512  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   52428800512  1 NO  CURRENT      17441850 21-NOV-22   1.8447E+19    0
 2  1 50   52428800512  1 NO  INACTIVE     17377187 20-NOV-22     17401476 20-NOV-22  0
 3  1 51   52428800512  1 NO  INACTIVE     17401476 20-NOV-22     17441850 21-NOV-22  0
 4  1  0  104857600512  1 YES UNUSED      0      0    0
 5  1  0  104857600512  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   52428800512  1 NO  ACTIVE       17441850 21-NOV-22     17444860 21-NOV-22  0
 2  1 50   52428800512  1 NO  INACTIVE     17377187 20-NOV-22     17401476 20-NOV-22  0
 3  1 51   52428800512  1 NO  INACTIVE     17401476 20-NOV-22     17441850 21-NOV-22  0
 4  1 53  104857600512  1 NO  CURRENT      17444860 21-NOV-22   1.8447E+19    0
 5  1  0  104857600512  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   52428800512  1 NO  INACTIVE     17441850 21-NOV-22     17444860 21-NOV-22  0
 2  1 50   52428800512  1 NO  INACTIVE     17377187 20-NOV-22     17401476 20-NOV-22  0
 3  1 51   52428800512  1 NO  INACTIVE     17401476 20-NOV-22     17441850 21-NOV-22  0
 4  1 53  104857600512  1 NO  CURRENT      17444860 21-NOV-22   1.8447E+19    0
 5  1  0  104857600512  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  104857600512  1 NO  CURRENT      17444860 21-NOV-22   1.8447E+19    0
 5  1  0  104857600512  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  104857600512  1 NO  CURRENT      17444860 21-NOV-22   1.8447E+19    0
 5  1  0  104857600512  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  104857600512  1 YES UNUSED      0      0    0
 4  1 53  104857600512  1 NO  CURRENT      17444860 21-NOV-22   1.8447E+19    0
 5  1  0  104857600512  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  104857600512  2 YES UNUSED      0      0    0
 4  1 53  104857600512  1 NO  CURRENT      17444860 21-NOV-22   1.8447E+19    0
 5  1  0  104857600512  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  104857600512  2 YES UNUSED      0      0    0
 4  1 53  104857600512  2 NO  CURRENT      17444860 21-NOV-22   1.8447E+19    0
 5  1  0  104857600512  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聯機日志文件內容請搜索以前的文章或繼續瀏覽下面的相關文章希望大家以后多多支持！

標簽： Oracle
相關文章：
主站蜘蛛池模板： 一本色道久久综合亚洲精品高清 | 一区二区福利视频 | a级在线| 99久久99| 精品久久久久一区二区国产 | 中文字幕精品一区二区三区精品 | 国产黄色麻豆视频 | www.国产.com| 欧美一区在线看 | 日本午夜在线视频 | 中文字幕第49页 | 91国内精精品久久久久久婷婷 | 日韩av免费看 | 日本精品国产 | 色888www视频在线观看 | 羞羞网站免费观看 | 中国一级大黄大片 | 一级大片网站 | 亚洲综合在线播放 | 亚洲精品一区二区三区 | 日一区二区| 成人精品鲁一区一区二区 | 99久久中文字幕三级久久日本 | 中文字幕一区二区三区精彩视频 | 男女免费网站 | 精品国产乱码久久久久久影片 | 色爱综合 | 高清一区二区视频 | 成人性视频免费网站 | a级毛片基地 | 99热碰| a视频在线观看 | 国产99热在线 | 手机在线一区二区三区 | 黄色免费在线观看网站 | 午夜精品一区二区三区在线视频 | 久久久精品网站 | 99精品视频在线观看 | 日本午夜免费福利视频 | 亚洲香蕉 | 久草在线 |