丢失当前使用的重做的日志情况下，把数据库恢复到正常状态，并且尽量把数据丢失的风险
降到最低。oracle防止数据丢失的机制也非常灵活到位，当丢失当前的redo日志文件，oracle要求
把它归档完成了才能删除，归档的前提就是把当前的日志组切换到另外一个日志文件组。以下是
实验的过程：
----恢复丢失当前重做日志文件组的数据库：
---查看日志文件组信息：
PROD>select group#,member from v$logfile;
GROUP# MEMBER
---------- --------------------------------------------------
3 /u01/app/oracle/oradata/PROD/redo03.log
2 /u01/app/oracle/oradata/PROD/redo02.log
1 /u01/app/oracle/oradata/PROD/redo01.log
1 /u01/app/oracle/oradata/PROD/redo01b.log
2 /u01/app/oracle/oradata/PROD/redo02b.log
3 /u01/app/oracle/oradata/PROD/redo03b.log
6 rows selected.
#共有3个日志组6个日子成员。
---查看当前的日志文件组：
PROD>select group#,status from v$log;
GROUP# STATUS
---------- ----------------
1 CURRENT
2 UNUSED
3 UNUSED
#当前使用的是第1个日志组。
---查看测试表中的记录：
PROD>select * from scott.test;
X Y
---------- ----------
1 10
2 20
#共有两条记录。
---往测试表中插入两条测试数据记录：
PROD>insert into scott.test values(3,30);
1 row created.
PROD>insert into scott.test values(4,40);
1 row created.
PROD>commit;
Commit complete.
#插入测试数据完成。
---再次查看尝试表中的记录：
PROD>select * from scott.test;
X Y
---------- ----------
1 10
2 20
3 30
4 40
#现在共有4条记录。
---删除当前使用的日志组：
PROD>!ls /u01/app/oracle/oradata/PROD/redo01.log
ls: /u01/app/oracle/oradata/PROD/redo01.log: No such file or directory
PROD>!ls /u01/app/oracle/oradata/PROD/redo01b.log
ls: /u01/app/oracle/oradata/PROD/redo01b.log: No such file or directory
已经删除第一个日志文件组。
---查看日志的归档状态：
PROD>select group#,members,status,archived
2 from v$log;
GROUP# MEMBERS STATUS ARC
---------- ---------- ---------------- ---
1 2 CURRENT NO
2 2 UNUSED YES
3 2 UNUSED YES
---切换日志：
PROD>alter system switch logfile;
System altered.
#已经切换。
---再次查看日志组的归档情况。
PROD>select group#,members,status,archived
2 from v$log;
GROUP# MEMBERS STATUS ARC
---------- ---------- ---------------- ---
1 2 ACTIVE NO
2 2 CURRENT NO
3 2 UNUSED YES
#由于已经删除了第一个日志文件组，导致不能正常完成归档。
---尝试删除第一个日志文件组：
PROD>alter database drop logfile group 1;
alter database drop logfile group 1
*
ERROR at line 1:
ORA-00350: log 1 of instance PROD (thread 1) needs to be archived
ORA-00312: online log 1 thread 1: '/u01/app/oracle/oradata/PROD/redo01.log'
ORA-00312: online log 1 thread 1: '/u01/app/oracle/oradata/PROD/redo01b.log'
#警告必须让第一个个日志文件组完成归档才能删除。
---关闭数据库并尝试重启数据库：
PROD>shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
PROD>startup
ORACLE instance started.
Total System Global Area 835104768 bytes
Fixed Size 2257840 bytes
Variable Size 507513936 bytes
Database Buffers 322961408 bytes
Redo Buffers 2371584 bytes
Database mounted.
ORA-03113: end-of-file on communication channel
Process ID: 16396
Session ID: 1 Serial number: 5
PROD>select status from v$instance;
ERROR:
ORA-03114: not connected to ORACLE
#不能正常打开数据库，无法连接数据。
--尝试再次关闭实例：
PROD>shutdown immediate;
ORA-24324: service handle not initialized
ORA-01041: internal error. hostdef extension doesn't exist
PROD>
---退出实例并尝试打开到mount状态：
PROD>quit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@enmo ~]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Tue Nov 22 21:08:07 2016
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to an idle instance.
PROD>startup mount;
ORACLE instance started.
Total System Global Area 835104768 bytes
Fixed Size 2257840 bytes
Variable Size 507513936 bytes
Database Buffers 322961408 bytes
Redo Buffers 2371584 bytes
Database mounted.
---把数据库调至到非归档模式下进而删除第一个丢失的日志文件组：
PROD>alter database noarchivelog;
Database altered.
PROD>archive log list;
Database log mode No Archive Mode
Automatic archival Disabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 2
Current log sequence 3
PROD>
#如果不是非归档模式，依然不能在oracle系统层删除丢失的日志文件。
---删除第一个日志文件组：
PROD>alter database drop logfile group 1;
Database altered.
---查看日志文件组的归档状态：
PROD>select group#,members,archived,status from v$log;
GROUP# MEMBERS ARC STATUS
---------- ---------- --- ----------------
3 2 YES UNUSED
2 2 NO CURRENT
---重新添加第一个日志文件组两个日志文件成员：
PROD>alter database add logfile group 1
2 ('/u01/app/oracle/oradata/PROD/redo01.log',
3 '/u01/app/oracle/oradata/PROD/redo01b.log')
4 size 50M;
Database altered.
--同时查看alert日志文件的记录：
Tue Nov 22 21:19:06 2016
alter database add logfile group 1
('/u01/app/oracle/oradata/PROD/redo01.log',
'/u01/app/oracle/oradata/PROD/redo01b.log')
size 50M
Completed: alter database add logfile group 1
('/u01/app/oracle/oradata/PROD/redo01.log',
'/u01/app/oracle/oradata/PROD/redo01b.log')
size 50M
#添加日志文件组成功。
---查看日志文件组与成员信息：
PROD>select group#,member from v$logfile;
GROUP# MEMBER
---------- --------------------------------------------------
3 /u01/app/oracle/oradata/PROD/redo03.log
2 /u01/app/oracle/oradata/PROD/redo02.log
1 /u01/app/oracle/oradata/PROD/redo01.log
1 /u01/app/oracle/oradata/PROD/redo01b.log
2 /u01/app/oracle/oradata/PROD/redo02b.log
3 /u01/app/oracle/oradata/PROD/redo03b.log
6 rows selected.
#日志添加成功。
---再次查看日志文件组的归档情况：
PROD>select group#,members,archived,status from v$log;
GROUP# MEMBERS ARC STATUS
---------- ---------- --- ----------------
1 2 YES UNUSED
3 2 YES UNUSED
2 2 NO CURRENT
---尝试打开数据库：
PROD>alter database open;
Database altered.
---尝试查看Scott用户的数据记录是否丢失：
PROD>select * from scott.test;
X Y
---------- ----------
1 10
2 20
3 30
4 40
#数据库已经恢复，并无数据丢失。
来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/31392094/viewspace-2128923/，如需转载，请注明出处，否则将追究法律责任。
转载于:http://blog.itpub.net/31392094/viewspace-2128923/
313
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
