如果自己搞不定可以找诗檀软件专业ORACLE数据库修复团队成员帮您恢复!
诗檀软件专业数据库修复团队
服务热线 ： 13764045638 QQ号:47079569 邮箱：service@parnassusdata.com
RMAN备份和从丢失的重做日志文件进行用户管理恢复
在一些情况下，丢失重做日志文件是一个灾难， 因为重做日志文件包含数据库的变化，这意味着丢失了重做日志文件，你会永远丢失那些变化，看这些重做日志丢失的情况，学会恰当地应对。
这是一系列可能的重做日志故障，以及带有每一步骤说明和解释的解决方法。
- 情景 1: 从丢失的复用重做日志恢复
- 情景 2: 从丢失的Inactive 群的重做日志恢复
- 情景 3: 从丢失的Current 群的重做日志恢复
- 情景 4: 从丢失的Active 群的重做日志恢复
现在一步一步地测试上面的情景。
情景 1: 从丢失的复用重做日志文件恢复
当任何组的复用一个重做日志损坏或不小心删除，LGWR 过程忽略它，将信息写入到唯一可用的重做日志，下面的情景进行了解释，以便更容易理解主要的概念:
- 为每个重做日志群添加新的重做日志，查询所有的重做日志和他们的状态:
SQL> select b.group#, a.status, b.status, b.member from v$log a, v$logfile b where a.group#=b.group# order by 1,2;
GROUP# STATUS STATUS MEMBER ---------- ---------------- ------- -------------------------------- 1 CURRENT /u01/oracle/product/10.2.0/db_1/ora data/testdb/redo01.log 2 INACTIVE /u01/oracle/product/10.2.0/db_1/ora data/testdb/redo02.log 3 ACTIVE /u01/oracle/product/10.2.0/db_1/ora data/testdb/redo03.log
- 每组添加一个重做日志：
SQL> alter database add logfile member '/u02/oradata/testdb/redo01.log' to group 1; Database altered. SQL> alter database add logfile member '/u02/oradata/testdb/redo02.log' to group 2; Database altered. SQL> alter database add logfile member '/u02/oradata/testdb/redo03.log' to group 3; Database altered.
- 再次查询两个视图，你会看到每个重做日志处于无效状态，因为它们是新创建的。
SQL> select b.group#, a.status, b.status, b.member from v$log a, v$logfile b where a.group#=b.group# order by 1,2;
GROUP# STATUS STATUS MEMBER ---------- ---------------- ------- -------------------------------- 1 CURRENT /u01/oracle/product/10.2.0/db_1/ora data/testdb/redo01.log 1 CURRENT INVALID /u02/oradata/testdb/redo01.log 2 INACTIVE INVALID /u02/oradata/testdb/redo02.log 2 INACTIVE /u01/oracle/product/10.2.0/db_1/ora data/testdb/redo02.log 3 ACTIVE INVALID /u02/oradata/testdb/redo03.log 3 ACTIVE /u01/oracle/product/10.2.0/db_1/ora data/testdb/redo03.log
6 rows selected.
- 执行人工重做日志切换，使这些重做日志可用，再次查询视图:
SQL> alter system switch logfile; System altered. SQL> / System altered. SQL> / System altered. SQL> select b.group#, a.status, b.status, b.member from v$log a, v$logfile b where a.group#=b.group# order by 1,2;
GROUP# STATUS STATUS MEMBER ---------- ---------------- ------- ----------------------------------- 1 CURRENT /u01/oracle/product/10.2.0/db_1/ora data/testdb/redo01.log 1 CURRENT /u02/oradata/testdb/redo01.log 2 INACTIVE /u02/oradata/testdb/redo02.log <.....output trimmed ......> <.....output trimmed ......> 6 rows selected.
- 删除 OS中的一个重做日志文件，关闭数据库，重启，切换重做日志文件，再次查询视图:
SQL> host rm -rf /u02/oradata/testdb/redo01.log SQL> shut abort SQL> startup SQL> alter system switch logfile; System altered. SQL> select b.group#, a.archived, a.status, b.status, b.member from v$log a, v$logfile b where a.group#=b.group# order by 1,2;
GROUP# ARC STATUS STATUS MEMBER ---------- --- ---------------- ------- ---------------------------- 1 NO CURRENT /u01/oracle/product/10.2.0/db_1/ora data/testdb/redo01.log 1 NO CURRENT INVALID /u02/oradata/testdb/redo01.log 2 NO INACTIVE /u02/oradata/testdb/redo02.log <......output trimmed ......> <......output trimmed ......> 6 rows selected.
- 通过删除并再次创建来重建重做日志，因为该重做日志存在于当前使用的重做日志群，所以，你不能删除它，于是，切换重做日志文件，再次尝试:
SQL> alter database drop logfile member '/u02/oradata/testdb/redo01.log'; alter database drop logfile member '/u02/oradata/testdb/redo01.log' * ERROR at line 1: ORA-01609: log 1 is the current log for thread 1 - cannot drop members ORA-00312: online log 1 thread 1: '/u01/ORACLE/product/10.2.0/db_1/oradata/testdb/redo01.log' ORA-00312: online log 1 thread 1: '/u02/oradata/testdb/redo01.log' SQL> alter system switch logfile; System altered. SQL> alter database drop logfile member '/u02/oradata/testdb/redo01.log'; Database altered. SQL>
- 现在添加新的重做日志文件到同一个组:
SQL> alter database add logfile member '/u02/oradata/testdb/redo01.log' to group 1; Database altered. SQL> select b.group#, a.archived, a.status, b.status, b.member from v$log a, v$logfile b where a.group#=b.group# order by 1,2;
GROUP# ARC STATUS STATUS MEMBER ---------- --- ---------------- ------- ---------------------------- 1 NO INACTIVE /u01/oracle/product/10.2.0/db_1/ora data/testdb/redo01.log 1 NO INACTIVE INVALID /u02/oradata/testdb/redo01.log <......output trimmed ......> <......output trimmed ......>
6 rows selected.
- 执行人工重做日志文件，激活重做日志文件:
SQL> alter system switch logfile; System altered. SQL> / System altered. SQL> select b.group#, a.archived, a.status, b.status, b.member from v$log a, v$logfile b where a.group#=b.group# order by 1,2;
GROUP# ARC STATUS STATUS MEMBER ---------- --- ---------------- ------- ---------------------------- 1 NO CURRENT /u01/oracle/product/10.2.0/db_1/ora data/testdb/redo01.log 1 NO CURRENT /u02/oradata/testdb/redo01.log
6 rows selected.