1重做日志文件
<h1>重做日志文件组优化</h1>
<h2>关于重做日志的重要知识</h2>
<ul>
<li>重做日志文件状态说明
<code>select * from v$logfile;</code></li>
</ul>
<table>
<thead>
<tr>
<th style="text-align: left;">状态</th>
<th style="text-align: left;">说明</th>
</tr>
</thead>
<tbody>
<tr>
<td style="text-align: left;">空白</td>
<td style="text-align: left;">文件正在使用</td>
</tr>
<tr>
<td style="text-align: left;">stale</td>
<td style="text-align: left;">文件内容不完全</td>
</tr>
<tr>
<td style="text-align: left;">invalid</td>
<td style="text-align: left;">文件不可以被访问</td>
</tr>
<tr>
<td style="text-align: left;">deleted</td>
<td style="text-align: left;">文件已不再有用了</td>
</tr>
</tbody>
</table>
<ul>
<li>添加删除单个重做日志成员</li>
</ul>
<pre><code>-- 添加当个重做日志组成员
alter database add logfile member '+DATA' to group 1 ;
alter database add logfile member '/home/oracle/backup/disk4/redo01d.log' to group 1 ;
-- 删除重做日志组成员
alter database drop logfile member '+DATA/ITPUXDB/ONLINELOG/group_1.294.1073420339' ;
alter database drop logfile member '/home/oracle/backup/disk4/redo01d.log' ;</code></pre>
<ul>
<li>查看报警文件</li>
</ul>
<pre><code>-- 查看这个参数的值，12c以前的版本oracle存放警告日志文件的路径
show parameter background_dump_dest;
/opt/oracle/app/oracle/product/19c/dbhome_1/rdbms/log
-- Diag Trace文本警告日志文件，Diag AlertXML警告日志文件
select * from v$diag_info;
/opt/oracle/app/oracle/diag/rdbms/itpuxdb/itpuxdb1/trace</code></pre>
<pre><code># 查看一个文本后面的20行，可以看到警告日志文件有更新
[oracle@oracle trace]$ tail -n 20 alert_orcl.log
orcl_lgwr_3320.trm、orcl_lgwr_41676.trc后台进程追踪文件
orcl_ora_41705.trm、orcl_fenc_3338.trm用户进程追踪文件</code></pre>
<ul>
<li>
<p>日志交换、强制检查点
<a href="#test">下面有说</a></p>
</li>
<li>初始化联机重做日志文件、不归档已经破坏的联机重做日志文件</li>
</ul>
<pre><code>-- 风险极高，P95
alter database clear logfile group 组号;
alter database clear unarchived logfile group 组号;</code></pre>
<h2>重做日志配置的应用实例：5组，3个成员，15M</h2>
<ul>
<li>查看重做日志文件的情况</li>
</ul>
<pre><code>select group#,sequence#,members,bytes,status,archived from v$log;--当前三组，每组一个成员
select * from v$logfile;--全部重做日志文件放成在同一目录下
SQL> select group#,sequence#,members,bytes,status,archived from v$log;
GROUP# SEQUENCE# MEMBERS BYTES STATUS ARC
---------- ---------- ---------- ---------- ---------------- ---
1 462 3 15728640 INACTIVE YES
2 463 3 15728640 CURRENT NO
3 459 3 15728640 INACTIVE YES
4 460 3 15728640 INACTIVE YES
5 461 3 15728640 INACTIVE YES</code></pre>
<ul>
<li>旧的日志文件路径：</li>
</ul>
<pre><code>/u01/app/oracle/oradata/orcl/redo01.log
/u01/app/oracle/oradata/orcl/redo02.log
/u01/app/oracle/oradata/orcl/redo03.log</code></pre>
<ul>
<li>更新配置的办法：增加到5组，每组3成员，分别存放在不同的路径</li>
</ul>
<pre><code>/home/oracle/backup/disk3/ORCL/onlinelog
/home/oracle/backup/disk6/ORCL/onlinelog
/home/oracle/backup/disk9/ORCL/onlinelog</code></pre>
<ul>
<li>开始添加：每做一个操作，都需要实际去确认是否生效</li>
</ul>
<pre><code>-- 添加group=4的组
alter database add logfile group 4
('/home/oracle/backup/disk3/ORCL/onlinelog/redo04a.log',
'/home/oracle/backup/disk6/ORCL/onlinelog/redo04b.log',
'/home/oracle/backup/disk9/ORCL/onlinelog/redo04c.log')
size 15M;
-- 添加group=5的组
alter database add logfile group 5
('/home/oracle/backup/disk3/ORCL/onlinelog/redo05a.log',
'/home/oracle/backup/disk6/ORCL/onlinelog/redo05b.log',
'/home/oracle/backup/disk9/ORCL/onlinelog/redo05c.log')
size 15M;</code></pre>
<ul>
<li>新建的已经完成，现删除旧的再重建，INACTIVE状态的直接删除重建即可，如ACTIVE、CURRENT的需要进行切换</li>
</ul>
<pre><code>alter database drop logfile group 1;
alter database add logfile group 1
('/home/oracle/backup/disk3/ORCL/onlinelog/redo01a.log',
'/home/oracle/backup/disk6/ORCL/onlinelog/redo01b.log',
'/home/oracle/backup/disk9/ORCL/onlinelog/redo01c.log')
size 15M;
alter database drop logfile group 2;
alter database add logfile group 2
('/home/oracle/backup/disk3/ORCL/onlinelog/redo02a.log',
'/home/oracle/backup/disk6/ORCL/onlinelog/redo02b.log',
'/home/oracle/backup/disk9/ORCL/onlinelog/redo02c.log')
size 15M;</code></pre>
<p><strong><div id='test'>日志交换、强制检查点</div></strong></p>
<ul>
<li>
<p>活动状态的日志删除需要进行切换，其他操作同上</p>
<pre><code>alter system switch logfile;--第一次切换后是ACTIVE状态，第二次切换才变为INACTIVE状态
alter system switch logfile;--书上的说法不对，切换还是活动状态，应该用强制检查点
alter system checkpoint;--强制检查点将活动的日志置为不活动</code></pre>
</li>
<li>删除旧的第三组日志，重新创建第三组日志</li>
</ul>
<pre><code>alter database drop logfile group 3 ;
alter database add logfile group 3
('/home/oracle/backup/disk3/ORCL/onlinelog/redo03a.log',
'/home/oracle/backup/disk6/ORCL/onlinelog/redo03b.log',
'/home/oracle/backup/disk9/ORCL/onlinelog/redo03c.log')
size 15M;</code></pre>
<ul>
<li>查看变更后的情况，旧的物理日志文件需要清理</li>
</ul>
<pre><code>select group#,sequence#,members,bytes,status,archived from v$log order by group#;
select * from v$logfile order by group#,member ;</code></pre>
<h2>OMF管理重做日志文件</h2>