1024问

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

Mathilda ·
更新时间:2024-09-20
· 641 次阅读

管理联机日志文件：

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

数据库正常工作至少需要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联机日志文件内容请搜索软件开发网以前的文章或继续浏览下面的相关文章希望大家以后多多支持软件开发网！



日志文件 Oracle

1024 个赞
编辑 举报
需要 登录 后方可回复, 如果你还没有账号请 注册新账号
相关文章
Python File(文件) 方法
Emily 2020-05-26
734
Web 创建设计
Claire 2021-05-26
585
Oracle建表与创建序列详细实例
Bonita 2023-07-20
896
Oracle的PDB数据库创建DIRECTORY时遇到ORA-65254问题及解决方法
Pascall 2023-07-20
1404
Oracle Users表空间重命名问题解决
Octavia 2023-07-20
1480
Oracle数据库安装教程并实现公网远程连接(内网穿透)
Dianthe 2023-07-20
221
Oracle数据库实现远程访问方法
Karima 2023-07-20
1744
Oracle压缩表空间的基本步骤
Nita 2023-07-20
1495
Oracle中实现类似于Mysql中的Field()函数功能
Calandra 2023-07-20
853
oracle分区表创建(自动按年、月、日分区)实战记录
Roselani 2023-07-20
1764
Oracle数据迁移MySQL的三种简单方法
Brigitte 2023-07-20
1330
Oracle Database 23c新特性之关联更新和删除示例详解
Tia 2023-07-20
862
oracle中sql%rowcount的作用详解
Isleta 2023-07-20
497
oracle行转列与列转行的几种方式汇总
Hadara 2023-07-20
420
Oracle一行拆分为多行方法实例
Leona 2023-07-20
132
Oracle常用函数超详细整理
Carmen 2023-07-20
1472
Oracle中时间日期转化函数to_date和to_char的具体使用
Dara 2023-07-20
1782
oracle中ORA-12514问题解决方法
Bonnie 2023-07-20
585
12类Oracle日期函数超全面总结
Tamara 2023-07-20
1893
oracle11g数据库常用操作实例总结
Fawn 2023-07-20
129