archive log

最新推荐文章于 2021-06-24 11:50:05 发布

原创最新推荐文章于 2021-06-24 11:50:05 发布 · 654 阅读

0 ·

CC 4.0 BY-SA版权

oracle 专栏收录该内容

10 篇文章

订阅专栏

本文详细介绍了如何在Oracle环境下管理数据库日志，包括添加日志成员、查看日志信息、开启归档模式、日志切换及使用dba_hist_log视图检查日志状态。同时展示了如何通过命令调整日志文件的存储位置和组别，以及在不同状态下执行的SQL命令。

idle> select * from v$log;

GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE MEMBERS ARCHIVED STATUS FIRST_CHANGE# FIRST_TIME NEXT_CHANGE# NEXT_TIME
1 1 166 52428800 512 1 NO INACTIVE 3245716 2013/12/16 22:20:33 3269932 2013/12/17 14:31:52
3 1 168 52428800 512 1 NO CURRENT 3303501 2013/12/18 14:29:46 281474976710655

2 1 167 52428800 512 1 NO INACTIVE 3269932 2013/12/17 14:31:52 3303501 2013/12/18 14:29:46

我们看到一共有三组日志，分别是group1，group2，group3，oracel 建议每组日志里有1个以上的成员（member），而我们目前的环境是分别都只有1个成员

下面添加成员：语法1：ALTER DATABASE ADD LOGFILE MEMBER '/oracle/dbs/log2b.rdo' TO GROUP 2;

语法2：ALTER DATABASE ADD LOGFILE MEMBER '/oracle/dbs/log2c.rdo' TO ('/oracle/dbs/log2a.rdo', '/oracle/dbs/log2b.rdo');

idle> select * from v$logfile;

GROUP# STATUS TYPE MEMBER IS_
---------- ------- ------- --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ---
1 ONLINE /u01/app/oracle/oradata/ocp/redo01.log NO
2 ONLINE /u01/app/oracle/oradata/ocp/redo02.log NO
3 ONLINE /u01/app/oracle/oradata/ocp/redo03.log NO

idle> ALTER DATABASE ADD LOGFILE MEMBER '/u01/app/oracle/oradata/ocp/testlog/redo01.log' TO GROUP 1;

Database altered.

idle> ALTER DATABASE ADD LOGFILE MEMBER '/u01/app/oracle/oradata/ocp/testlog/redo02.log' TO GROUP 2;

Database altered.

idle> ALTER DATABASE ADD LOGFILE MEMBER '/u01/app/oracle/oradata/ocp/testlog/redo03.log' TO GROUP 3;

Database altered.

一般同一组的不通成员所在的位置不会在一起，我这边是测试环境就直接放在了testlog下区分下

下面来看下创建后的文件

idle> select * from v$log;

GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME
---------- ---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- --------- ------------ ---------
1 1 166 52428800 512 2 NO INACTIVE 3245716 16-DEC-13 3269932 17-DEC-13
3 1 168 52428800 512 2 NO CURRENT 3303501 18-DEC-13 2.8147E+14
2 1 167 52428800 512 2 NO INACTIVE 3269932 17-DEC-13 3303501 18-DEC-13

idle> select * from v$logfile;

GROUP# STATUS TYPE MEMBER IS_
---------- ------- ------- --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ---
1 ONLINE /u01/app/oracle/oradata/ocp/redo01.log NO
2 ONLINE /u01/app/oracle/oradata/ocp/redo02.log NO
3 ONLINE /u01/app/oracle/oradata/ocp/redo03.log NO
1 INVALID ONLINE /u01/app/oracle/oradata/ocp/testlog/redo01.log NO
2 INVALID ONLINE /u01/app/oracle/oradata/ocp/testlog/redo02.log NO
3 INVALID ONLINE /u01/app/oracle/oradata/ocp/testlog/redo03.log NO

查看数据库是否开启archive

idle> archive log list
Database log mode No Archive Mode
Automatic archival Disabled
Archive destination /u01/app/oracle/product/11.2.0/dbs/arch
Oldest online log sequence 166
Current log sequence 168

idle> select log_mode from v$database;

LOG_MODE
------------
NOARCHIVELOG

要开启归档模式，需要先把数据库停止shutdown，然后开始到mount状态，开启归档，最后open数据库。

idle> startup mount
ORACLE instance started.

Total System Global Area 1068937216 bytes
Fixed Size 2235208 bytes
Variable Size 272630968 bytes
Database Buffers 788529152 bytes
Redo Buffers 5541888 bytes
Database mounted.
idle> alter database archivelog
2 ;

Database altered.

idle> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /u01/app/oracle/product/11.2.0/dbs/arch
Oldest online log sequence 166
Next log sequence to archive 168
Current log sequence 168

idle> alter database open;

Database altered.

日志切换，当写满一个文件会自动切换，也可以用命令手动切换

idle> alter system switch logfile;

System altered.

dba_hist_log视图可以查看之前的log是在哪个group及是否归档

idle> select group#,sequence#,bytes,members,ARCHIVED,status from dba_hist_log order by sequence# desc;

GROUP# SEQUENCE# BYTES MEMBERS ARC STATUS
---------- ---------- ---------- ---------- --- ----------------
3 174 52428800 2 NO CURRENT
2 173 52428800 2 YES INACTIVE
1 172 52428800 2 YES INACTIVE
2 167 52428800 1 NO CURRENT
2 167 52428800 1 NO CURRENT
2 167 52428800 1 NO CURRENT
2 167 52428800 1 NO CURRENT
2 167 52428800 1 NO CURRENT
1 166 52428800 1 NO INACTIVE
1 166 52428800 1 NO INACTIVE
1 166 52428800 1 NO CURRENT
1 166 52428800 1 NO INACTIVE
1 166 52428800 1 NO INACTIVE
1 166 52428800 1 NO INACTIVE
3 165 52428800 1 NO CURRENT
3 165 52428800 1 NO INACTIVE
3 165 52428800 1 NO INACTIVE
3 165 52428800 1 NO INACTIVE
3 165 52428800 1 NO INACTIVE