模板
教程
环境
性能
功能
管理
_disable_logging对于非归档数据库的影响
首先确定数据库当前是非归档模式： clearcase/" target="_blank" >cccccc; MARGIN: 0cm 0cm 0pt; LINE-HEIGHT: 20pt; mso-line-height-rule: exactly">lunar@TSMISC01> archive log list Database log mode No Archive Mode Automatic archival Enabled Archive destination /oracle/oradata/TSMISC01/archive Oldest online log sequence 0 之前被删除或者被初始化过 Current log sequence 158 lunar@TSMISC01> 查看online redo log 的相关信息： lunar@TSMISC01> select * from v$log; GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM ---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- --------- 1 1 0 2097152 2 YES UNUSED 0 2 1 158 104857600 2 NO CURRENT 49355946 12-APR-06 3 1 157 104857600 1 NO INACTIVE 49355943 12-APR-06 Elapsed: 00:00:00.00 lunar@TSMISC01> select * from v$logfile; GROUP# STATUS TYPE MEMBER ---------- ------- ------- -------------------------------------------------- 1 ONLINE /oracle/oradata/TSMISC01/redo11.log 2 ONLINE /oracle/oradata/TSMISC01/redo02.log 3 ONLINE /oracle/oradata/TSMISC01/redo03.log 2 ONLINE /oracle/oradata/TSMISC01/redo02_2.log 1 ONLINE /oracle/oradata/TSMISC01/redo12.log Elapsed: 00:00:00.00 lunar@TSMISC01> 从这些信息我们看到，group 1 是新建的组，因此其sequence#是0,而status是 unused。还可以看到，group 2是当前数据库正在使用的online redo logfile。 现在，检查数据库中_disable_logging参数的设置： lunar@TSMISC01> @get_param Enter value for par: disable_logging old 14: x.ksppinm like '%_&par%' new 14: x.ksppinm like '%_disable_logging%' NAME VALUE ISDEFAULT ISMOD ISADJ ----------------------------------- ------------------------- --------- ---------- ----- _disable_logging FALSE FALSE FALSE FALSE Elapsed: 00:00:00.00 lunar@TSMISC01> 可见，目前_disable_logging为false。 我们可以使用alter system 命令修改这个参数，并且可以使用scope=memory（仅在当前的系统中生效）或者scope=both（在当前的系统和spfile中都做相应的设置）来指定，让这个参数立刻生效： lunar@TSMISC01> alter system set "_disable_logging"=true scope=memory; System altered. Elapsed: 00:00:00.02 lunar@TSMISC01> show parameter disable_logging NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ _disable_logging boolean TRUE lunar@TSMISC01> 现在我们来作个测试表，创建一个大表，并向其中插入10000000 条记录，然后看看系统是否发生了日志切换，还是会发生其他什么异常情况： lunar@TSMISC01> @big_table 10000000 Table dropped. Elapsed: 00:00:01.33 Table created. Elapsed: 00:00:00.13 Table altered. Elapsed: 00:00:00.02 old 3: l_rows number := &1; new 3: l_rows number := 10000000; old 9: where rownum <= &1; new 9: where rownum <= 10000000; PL/SQL procedure successfully completed. Elapsed: 00:01:29.89 Table altered. Elapsed: 00:01:33.17 PL/SQL procedure successfully completed. Elapsed: 00:03:12.77 COUNT(*) ---------- 10000000 Elapsed: 00:00:02.05 lunar@TSMISC01> 现在看看日志文件的情况： lunar@TSMISC01> select * from v$log; GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM ---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- --------- 1 1 162 2097152 2 NO INACTIVE 49413664 12-APR-06 2 1 161 104857600 2 NO INACTIVE 49374533 12-APR-06 3 1 163 104857600 1 NO CURRENT 49414128 12-APR-06 Elapsed: 00:00:00.19 lunar@TSMISC01> select * from v$logfile; GROUP# STATUS TYPE MEMBER ---------- ------- ------- -------------------------------------------------- 1 ONLINE /oracle/oradata/TSMISC01/redo11.log 2 ONLINE /oracle/oradata/TSMISC01/redo02.log 3 ONLINE /oracle/oradata/TSMISC01/redo03.log 2 ONLINE /oracle/oradata/TSMISC01/redo02_2.log 1 ONLINE /oracle/oradata/TSMISC01/redo12.log Elapsed: 00:00:00.04 lunar@TSMISC01> 我们看到日志已经发生了切换，数据库可以继续操作，创建大表前数据库的“当前日志是group MILY: 宋体; mso-bidi-font-family: 宋体">1. _disable_logging对于非归档数据库的影响
