oracle redo日志恢复 |
您所在的位置:网站首页 › oracle日志查看 › oracle redo日志恢复 |
Oracle备份恢复中,redo的恢复相对来说还是比较简单的,只要保证每组的成员不止一个,出现问题的几率就相当小,即使出了问题我们也可按照不同的方法将他们恢复,所以如果碰到日志文件损坏,完全不必紧张!按照下面的方法来做,基本上都能搞定!
试验一:用命令清空日志组方法
1、 查看原来表中数据 SQL> conn test/test Connected. SQL> select * from test;
TEL ---------- 1 2 3 2、插入新数据 SQL> insert into test values(4); 1 row created. SQL> commit; Commit complete. SQL> 3、 正常关闭数据库 4、 利用os command删除所有redo文件 5、 启动数据库 SQL> startup ORACLE instance started. Total System Global Area 353862792 bytes Fixed Size 730248 bytes Variable Size 285212672 bytes Database Buffers 67108864 bytes Redo Buffers 811008 bytes Database mounted. ORA-00313: open failed for members of log group 1 of thread 1 ORA-00312: online log 1 thread 1: '/T3/ORACLE/oradata/ORA9/redo01.log' 6、 查看当前日志状态 SQL> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS ---------- ---------- ---------- ---------- ---------- --- ---------------- FIRST_CHANGE# FIRST_TIME ------------- ---------- 1 1 2 104857600 1 YES INACTIVE 487837 01-9月 -05
2 1 4 104857600 1 NO CURRENT 487955 01-9月 -05
3 1 3 104857600 1 YES INACTIVE 487839 01-9月 -05 看来redo01.log不是当前日志,对于这类非当前日志可以直接clear,系统会重新自动生成一个redo文件
7、SQL> alter database clear logfile group 1; Database altered. 7、 继续启动db SQL> alter database open; alter database open * ERROR at line 1: ORA-00313: open failed for members of log group 2 of thread 1 ORA-00312: online log 2 thread 1: '/T3/ORACLE/oradata/ORA9/redo02.log' 8、 看来redo也得恢复,但是redo02是当前redo,直接clear是不行的 SQL> alter database clear logfile group 2; alter database clear logfile group 2 * ERROR at line 1: ORA-00350: log 2 of thread 1 needs to be archived ORA-00312: online log 2 thread 1: '/T3/ORACLE/oradata/ORA9/redo02.log' 9、 按照oracle的某些做法也是不行的 SQL> alter database clear unarchived logfile group 2 unrecoverable datafile; alter database clear unarchived logfile group 2 unrecoverable datafile * ERROR at line 1: ORA-00313: open failed for members of log group 2 of thread 1 ORA-00312: online log 2 thread 1: '/T3/ORACLE/oradata/ORA9/redo02.log' ORA-27037: unable to obtain file status SVR4 Error: 2: No such file or directory Additional information: 3 10、 尝试从其他冷被分cp过来一个,再作clear,还是不行 SQL> host cp /T3/ORACLE/oradata2/ORA9/redo02.log /T3/ORACLE/oradata/ORA9/
SQL> alter database clear unarchived logfile group 2 unrecoverable datafile; alter database clear unarchived logfile group 2 unrecoverable datafile * ERROR at line 1: ORA-00322: log 2 of thread 1 is not current copy ORA-00312: online log 2 thread 1: '/T3/ORACLE/oradata/ORA9/redo02.log' SQL> 11、 但是对于非当前日志就都可以,下面看看redo03 SQL> alter database clear logfile group 3;
Database altered.
结论:
如果数据库是正常shutdown,非当前日志都可以直接clear来重新生成,而且不丢失数据,因为正常关闭db,数据已经写入dbf文件了。唯独当前日志不可以,当前日志必须用其他方法恢复,不管是不是正常关闭,
方法二:用cancel模式恢复数据库
前面的出错提示,步骤都一样,唯独恢复的方法不一样 SQL> startup ORACLE instance started. Total System Global Area 353862792 bytes Fixed Size 730248 bytes Variable Size 285212672 bytes Database Buffers 67108864 bytes Redo Buffers 811008 bytes Database mounted. ORA-00313: open failed for members of log group 1 of thread 1 ORA-00312: online log 1 thread 1: '/T3/ORACLE/oradata/ORA9/redo01.log' 看看丢失了哪些redo SQL> host ls /T3/ORACLE/oradarta/ORA9/redo* /T3/ORACLE/oradarta/ORA9/redo*: No such file or directory 看来redo都丢了 直接recover SQL> recover database until cancel;
Media recovery complete. 这个时候redo还没有生成 SQL> host ls /T3/ORACLE/oradata/ORA9/redo* /T3/ORACLE/oradata/ORA9/redo*: No such file or directory 启动数据库 SQL> alter database open ; alter database open * ERROR at line 1: ORA-01589: must use RESETLOGS or NORESETLOGS option for database open
SQL> alter database open resetlogs; Database altered. (注意,这里必须用resetlogs,否则会错误的
SQL> alter database open noresetlogs;
alter database open noresetlogs
*
ERROR at line 1:
ORA-00313: open failed for members of log group 1 of thread 1
ORA-00312: online log 1 thread 1: '/T3/ORACLE/oradata/ORA9/redo01.log'
SQL>
Resetlogs其实就是根据控制文件让系统自动重新生成redo,如果noresetlog的话,就不会重新生成redo,缺少了文件,db自然无法启动) SQL> host ls /T3/ORACLE/oradata/ORA9/redo* /T3/ORACLE/oradata/ORA9/redo01.log /T3/ORACLE/oradata/ORA9/redo02.log /T3/ORACLE/oradata/ORA9/redo03.log SQL> 检验 SQL> select * from test.test;
TEL ---------- 1 2 3 4 SQL> 数据一点儿都没有丢失
结论:
如果数据库是正常关闭的,用recover database until cancel可以轻松恢复或者说重新建立所有的redo,不再区分是否是当前日志,而且由于正常关闭,不会丢失任何数据,唯一可能丢失的情况就是如果日志还没有归档
这种恢复方法由于要resetlogs,所以在恢复完成后,日志清零,以前的备份不再起作用,所以建议立即备份
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /T3/ORACLE/arch
Oldest online log sequence 0
Next log sequence to archive 1
Current log sequence 1
SQL>
实验三:通过重新生成控制文件来恢复redo
前面的都一样,只是处理方法不一样
SQL> startup
ORACLE instance started.
Total System Global Area 353862792 bytes
Fixed Size 730248 bytes
Variable Size 285212672 bytes
Database Buffers 67108864 bytes
Redo Buffers 811008 bytes
Database mounted.
ORA-00313: open failed for members of log group 1 of thread 1
ORA-00312: online log 1 thread 1: '/T3/ORACLE/oradata/ORA9/redo01.log'
SQL> alter database backup controlfile to trace;
Database altered.
SQL> shutdown immediate
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.
SQL>
2、 修改一下刚才生成的那个文件 CREATE CONTROLFILE REUSE DATABASE "ORA9" RESETLOGS ARCHIVELOG -- SET STANDBY TO MAXIMIZE PERFORMANCE MAXLOGFILES 50 MAXLOGMEMBERS 5 MAXDATAFILES 100 MAXINSTANCES 1 MAXLOGHISTORY 226 LOGFILE GROUP 1 '/T3/ORACLE/oradata/ORA9/redo01.log' SIZE 100M, GROUP 2 '/T3/ORACLE/oradata/ORA9/redo02.log' SIZE 100M, GROUP 3 '/T3/ORACLE/oradata/ORA9/redo03.log' SIZE 100M -- STANDBY LOGFILE DATAFILE '/T3/ORACLE/oradata/ORA9/system01.dbf', '/T3/ORACLE/oradata/ORA9/undotbs01.dbf', '/T3/ORACLE/oradata/ORA9/cwmlite01.dbf', '/T3/ORACLE/oradata/ORA9/drsys01.dbf', '/T3/ORACLE/oradata/ORA9/example01.dbf', '/T3/ORACLE/oradata/ORA9/indx01.dbf', '/T3/ORACLE/oradata/ORA9/odm01.dbf', '/T3/ORACLE/oradata/ORA9/tools01.dbf', '/T3/ORACLE/oradata/ORA9/users01.dbf', '/T3/ORACLE/oradata/ORA9/xdb01.dbf', '/T3/ORACLE/oradata/ORA9/test01.dbf' CHARACTER SET ZHS16GBK ; 另存为一个脚本,运行他 SQL> @clone.sql Control file created. SQL> alter database open resetlogs; Database altered. SQL> 搞定……………
结论:这种方法的关键是重新创建控制文件,后面的步骤和前面的道理一样的
前面的三种方法都是假设db是正常关闭的,数据已经写入数据库文件中,所以不会由数据存在redo种,所以clear的话也不会有数据丢失 |
今日新闻 |
点击排行 |
|
推荐新闻 |
图片新闻 |
|
专题文章 |
CopyRight 2018-2019 实验室设备网 版权所有 win10的实时保护怎么永久关闭 |