(1)关闭数据库
shutdown immediate;(2)启动到mount
startup mount;(3)修改归档
alter database archivelog;(4)查看是否归档
archive log list;
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /archlog/mlbdb1
Oldest online log sequence 10
Next log sequence to archive 12
Current log sequence 12
SQL>(5)将数据库打开
alter database open;(6)归档默认存储位置
show parameter db_recovery_file_dest;
SQL> show parameter db_recovery_file_dest;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest string /oracle/fast_recovery_area
db_recovery_file_dest_size big integer 2000M
SQL>(7)修改归档默认存储位置
SQL> alter system setlog_archive_dest_1='location=/archlog/mlbdb1'scope=spfile;
SQL> alter system setlog_archive_format='mlbdb1_%t_%s_%r.dbf'scope=spfile;
四、重做日志组及其管理
1.添加一个重做日志组
第一种方法:
SQL> alter database add logfile group 4('/oradata01/mlbdb1/redo04.log','/oradata01/mlbdb1/redo04_b.log') size 500M;
Database altered.
第二种方法:
SQL> alter database add logfile ('/oradata01/mlbdb1/redo05.log','/oradata01/mlbdb1/redo05_b.log') size 500M;
Database altered.
验证是否添加成功
select * from v$logfile;
2.查询当前重做日志组的使用情况
SQL> SELECT t.group#,sequence#,bytes,members,archived,status FROM v$log t;
GROUP# SEQUENCE# BYTES MEMBERS ARCHIVED STATUS
---------- ---------- ---------- ---------- -------- ----------------
1105242880002 YES INACTIVE
2115242880002 YES INACTIVE
3125242880002 NO CURRENT
405242880002 YES UNUSED
505242880002 YES UNUSED
3.删除联机重做日志组
SQL> alter database drop logfile group 4,group 5;
Database altered.
SQL> SELECT t.group#,sequence#,bytes,members,archived,status FROM v$log t;
GROUP# SEQUENCE# BYTES MEMBERS ARCHIVED STATUS
---------- ---------- ---------- ---------- -------- ----------------
1105242880002 YES INACTIVE
2115242880002 YES INACTIVE
3125242880002 NO CURRENT
select * from v$logfile;
SQL> col MEMBER format a30
SQL>select * from v$logfile;
GROUP# STATUS TYPE MEMBER IS_RECOVERY_DEST_FILE
---------- ------- ------- ------------------------------ ---------------------
3 ONLINE /oradata01/mlbdb1/redo03.log NO
2 ONLINE /oradata01/mlbdb1/redo02.log NO
1 ONLINE /oradata01/mlbdb1/redo01.log NO
1 INVALID ONLINE /oradata01/mlbdb1/redo01_b.log NO
2 INVALID ONLINE /oradata01/mlbdb1/redo02_b.log NO
3 INVALID ONLINE /oradata01/mlbdb1/redo03_b.log NO
注意:日志文件必须手工删除
rm -rf /oradata01/mlbdb1/redo04.log
rm -rf /oradata01/mlbdb1/redo04_b.log
rm -rf /oradata01/mlbdb1/redo05.log
rm -rf /oradata01/mlbdb1/redo05_b.log
五、重做日志成员及维护
1.向重做日志组1、2、3添加一个重做日志成员
alter database add logfile member
'/oradata01/mlbdb1/redo03_c.log' to group 3,
'/oradata01/mlbdb1/redo02_c.log' to group 2,
'/oradata01/mlbdb1/redo01_c.log' to group 1;
2.验证日志组的成员数结果
SQL>select group#,sequence#,bytes,members,archived,status from v$log;
GROUP# SEQUENCE# BYTES MEMBERS ARCHIVED STATUS
---------- ---------- ---------- ---------- -------- ----------------
1105242880003 YES INACTIVE
2115242880003 YES INACTIVE
3125242880003 NO CURRENT
3.验证添加的重做日志组以及对应成员信息
SQL>select * from v$logfile;
GROUP# STATUS TYPE MEMBER IS_RECOVERY_DEST_FILE
---------- ------- ------- ------------------------------ ---------------------
3 ONLINE /oradata01/mlbdb1/redo03.log NO
2 ONLINE /oradata01/mlbdb1/redo02.log NO
1 ONLINE /oradata01/mlbdb1/redo01.log NO
1 INVALID ONLINE /oradata01/mlbdb1/redo01_b.log NO
2 INVALID ONLINE /oradata01/mlbdb1/redo02_b.log NO
3 INVALID ONLINE /oradata01/mlbdb1/redo03_b.log NO
3 INVALID ONLINE /oradata01/mlbdb1/redo03_c.log NO
2 INVALID ONLINE /oradata01/mlbdb1/redo02_c.log NO
1 INVALID ONLINE /oradata01/mlbdb1/redo01_c.log NO
9 rows selected
4.删除联机重做日志成员
删除日志组中的一个日志成员
alter database drop logfile member '/oradata01/mlbdb1/redo01_c.log';
验证是否删除成功
SQL>select * from v$logfile where group#=1;
GROUP# STATUS TYPE MEMBER IS_RECOVERY_DEST_FILE
---------- ------- ------- ------------------------------ ---------------------
1 ONLINE /oradata01/mlbdb1/redo01.log NO
1 INVALID ONLINE /oradata01/mlbdb1/redo01_b.log NO
删除日志成员的限制:
1.如果要删除的日志成员是重做日志组最后一个有效的成员,则不能删除,如该日志组中只有一个日志成员。
2.如果该日志组当前正在使用,在日志切换前不能删该组中的成员。
3.如果数据库正在运行在ARCHIVELOG模式,并且要删除的日志成员所属的日志组没有被归档,该组中的日志成员不能被删除。
六、重设联机重做日志的大小
1.查询当前的重做日志组信息
select group#, sequence#, bytes / 1024 / 1024, members, status from v$log;
2.查看日志组成员信息
select * from v$logfile;
3.删除该日志组
alter database drop logfile group 2;
alter system switch logfile;select group#,sequence#,bytes,members,status from v$log;
alter database checkpoit;select group#,sequence#,bytes,members,status from v$log;
4.重建重做日志组2并修改重做日志文件的大小
alter database add logfile group 2('/oradata01/mlbdb1/redo01_a.log',
'/oradata01/mlbdb1/redo01_b.log') size 1024m;
验证是否添加成功,以及日志成员的大小
select group#,sequence#,bytes,members,status from v$log;
5.清除联机重做日志
alter database clear logfile group n;
6.日志切换和检查点事件
alter system switch logfile;
就会触发日志切换。如果处于归档模式,则在将当前写满的日志组归档完成前,不会使用新的重做日志组。
7.发生检查点
alter system checkpoint;
检查点是ORACLE为了减少数据库实例恢复时间而设置得一个事件,当该事件发生时,
LGWR进程将重做日志缓冲区中的数据写入重做日志文件中,
而同时通知DBWR进程将数据库高速缓存中的已经提交的数据写入数据文件,
所以检查点事件越频繁,则用于数据库恢复的重做数据就越少。
检查点事件也修改数据文件头信息和控制文件信息,以记录检查点的SCN。