2015-10-21
目录
一、概述
二、数据字典
1.v$log视图：重做日志组
2.v$logfile视图：重做日志组成员
3.归档模式
三、重做日志组
1.添加重做日志组
2.查看重做日志组
3.删除重做日志组
四、重做日志成员
1.添加重做日志成员
2.查看重做日志成员
3.删除重做日志成员
五、日志切换、检查点事件
一、概述
Oracle引入重做日志的原因：数据恢复。为了提高I/O效率，Oracle引入了重做日志结构，典型重做日志结构由3个重做日志组组成，每个重做日志组中有2个重做日志成员（重做日志文件）。数据库系统会先使用重做日志组1，该组写满后切换到重做日志组2，再写满后切换到重做日志组3，然后循环使用重做日志组。Oracle规定每个数据库实例至少有2个重做日志组，每个重做日志组至少有1个重做日志文件。如果没有启动归档日志，一个循环结束，再次使用先前的重做日志组时会以覆盖的方式写入数据。
二、数据字典
1.v$log视图
SQL> desc v$log;
SQL> select group#,thread#,bytes,members,archived,status from v$log;
2.v$logfile视图
SQL> desc v$logfile;
SQL> col member for a50; SQL> select group#,status,type,member from v$logfile;
3.归档模式
查看归档模式
SQL> archive log list;
启用归档模式
//关闭数据库 SQL> shutdown immediate; //启动数据库到mount状态 SQL> startup nomount; SQL> alter database mount; //设置归档模式 SQL> alter database archivelog; //查看归档模式 SQL> archive log list;
//查看归档目录 SQL> show parameter db_recovery_file_dest;
三、重做日志组
1.添加重做日志组
ALTER DATABASE [database_name]
ADD LOGFILE [GROUP number] filename SIZE n
[,ADD LOGFILE [GROUP number] filename SIZE n......]
SQL> alter database add logfile group 4 2 (‘/u01/app/oracle/oradata/orcl/redo04a.log‘, 3 ‘/u01/app/oracle/oradata/orcl/redo04b.log‘) 4 size 11m;
2.查看重做日志组
SQL> select group#,thread#,sequence#,bytes,members,archived,status from v$log;
SQL> col member for a50; SQL> select group#,status,type,member from v$logfile;
3.删除重做日志组
ALTER DATABASE [database_name]
DROP LOGFILE {GROUP n| (‘filename‘ [,‘filename‘]...)}
{GROUP n| (‘filename‘ [,‘filename‘]...)}...
SQL> alter database drop logfile group 4;
验证
SQL> col member for a50; SQL> select group#,status,type,member from v$logfile;
四、重做日志成员
1.添加重做日志成员
ALTER DATABASE [database_name]
ADD LOGFILE MEMBER
[‘filename‘ [REUSE] [,‘filename‘ [REUSE]] ...
TO {GROUP n | (‘filename‘ [,‘filename‘])......}
]...
SQL> alter database add logfile member 2 ‘/u01/app/oracle/oradata/orcl/redo01a.log‘ to group 1, 3 ‘/u01/app/oracle/oradata/orcl/redo02a.log‘ to group 2, 4 ‘/u01/app/oracle/oradata/orcl/redo03a.log‘ to group 3;
2.查看重做日志成员
SQL> col member for a50; SQL> select group#,status,type,member from v$logfile;
3.删除重做日志成员
ALTER DATABASE [database_name]
DROP LOGFILE MEMBER ‘filename‘ [,‘filename‘] ....
SQL> alter database drop logfile member 2 ‘/u01/app/oracle/oradata/orcl/redo02a.log‘, 3 ‘/u01/app/oracle/oradata/orcl/redo03a.log‘;
验证
SQL> col member for a50; SQL> select group#,status,type,member from v$logfile;
五、日志切换、检查点事件
1.日志切换
SQL> alter system switch logfile;
验证
SQL> select group#,thread#,sequence#,bytes,members,archived,status from v$log;
2.检查点事件
SQL> alter system checkpoint;
原文：http://www.cnblogs.com/cenliang/p/4898162.html