查看: 239|回复: 2

3、Current redo log丢失或损坏的恢复,会丢失数据

[复制链接]
论坛徽章:
1
Oracle研习者高级
日期:2019-07-25 14:10:03
发表于 2019-6-5 00:56 | 显示全部楼层 |阅读模式

tm SQL Linux RAC ETL

SQL> set pagesize 1000
SQL> set linesize 2000
SQL> select * from v$log;

    GROUP#    THREAD#  SEQUENCE#      BYTES  BLOCKSIZE    MEMBERS ARC STATUS         FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME
---------- ---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- --------- ------------ ---------
   1      1        4   52428800    512    1 NO  CURRENT        1292296 05-JUN-19   2.8147E+14
   2      1        2   52428800    512    1 NO  INACTIVE         1292288 05-JUN-19      1292291 05-JUN-19
   3      1        3   52428800    512    1 NO  INACTIVE         1292291 05-JUN-19      1292296 05-JUN-19

SQL> select * from v$logfile;

    GROUP# STATUS  TYPE    MEMBER                                 IS_
---------- ------- ------- --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ---
   3     ONLINE  /u01/app/oracle/oradata/orcl/redo03.log                          NO
   2     ONLINE  /u01/app/oracle/oradata/orcl/redo02.log                          NO
   1     ONLINE  /u01/app/oracle/oradata/orcl/redo01.log                          NO

[root@orcl tmp]# dd if=/dev/null of=/u01/app/oracle/oradata/orcl/redo01.log bs=512 count=20;
0+0 records in
0+0 records out
0 bytes (0 B) copied, 0.000607886 s, 0.0 kB/s
[root@orcl tmp]#

SQL> shutdown immediate;
ORA-03113: end-of-file on communication channel
Process ID: 11315
Session ID: 1 Serial number: 7

继续使用添加隐藏参数pfile启动库,然后推进scn号
SQL> startup pfile='/tmp/pfile.ora' mount;
ORACLE instance started.

Total System Global Area 1252663296 bytes
Fixed Size        2252824 bytes
Variable Size     738201576 bytes
Database Buffers    503316480 bytes
Redo Buffers        8892416 bytes
Database mounted.
SQL> show parameter spfile;

NAME             TYPE  VALUE
------------------------------------ ----------- ------------------------------
spfile             string
SQL>
SQL> alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-01139: RESETLOGS option only valid after an incomplete database recovery

SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-00313: open failed for members of log group 1 of thread 1
ORA-00312: online log 1 thread 1: '/u01/app/oracle/oradata/orcl/redo01.log'
ORA-27048: skgfifi: file header information is invalid
Additional information: 13


SQL> alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-01139: RESETLOGS option only valid after an incomplete database recovery

SQL> recover database until cancel;
ORA-00279: change 1292296 generated at 06/05/2019 00:34:19 needed for thread 1
ORA-00289: suggestion : /data/arch/1_4_1010104268.dbf
ORA-00280: change 1292296 for thread 1 is in sequence #4

Specify log: {<RET>=suggested | filename | AUTO | CANCEL}

ORA-00308: cannot open archived log '/data/arch/1_4_1010104268.dbf'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3

ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below
ORA-01194: file 1 needs more recovery to be consistent
ORA-01110: data file 1: '/u01/app/oracle/oradata/orcl/system01.dbf'


SQL> alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-01092: ORACLE instance terminated. Disconnection forced
ORA-00600: internal error code, arguments: [2662], [0], [1292303], [0],
[1292482], [4194432], [], [], [], [], [], []
Process ID: 11421
Session ID: 1 Serial number: 5

SQL> alter session set events '10015 trace name adjust_scn level 1';

Session altered.

SQL> alter database open;

Database altered.

回复

使用道具 举报

论坛徽章:
6
kettle徽章
日期:2015-08-06 17:02:17数据陷阱解读徽章
日期:2015-08-13 15:21:46投资理财徽章
日期:2015-11-12 14:25:33R研习者中级
日期:2015-12-17 11:38:03投资理财徽章
日期:2015-12-24 15:51:21Oracle研习者高级
日期:2019-07-25 14:10:03
发表于 2019-6-8 22:12 | 显示全部楼层
我执行到最后会报
ORA-03113: end-of-file on communication channel
Process ID: 16219
Session ID: 191 Serial number: 3
回复 支持 反对

使用道具 举报

新浪微博达人勋 yangeoo  未实名认证
论坛徽章:
11
Oracle研习者高级
日期:2014-02-25 09:59:45Web课程徽章
日期:2018-08-14 10:39:59Java徽章
日期:2018-02-08 16:11:57JS课程徽章
日期:2016-11-10 15:45:52Java徽章
日期:2016-09-01 10:00:53Hadoop研习者初级
日期:2016-03-03 15:28:11R研习者中级
日期:2014-12-24 15:26:59Hadoop研习者初级
日期:2014-09-19 14:12:00Oracle研习者初级
日期:2014-09-19 14:07:02Oracle研习者初级
日期:2014-09-19 14:06:28spark徽章
日期:2019-05-23 14:16:12
发表于 2019-6-8 23:10 | 显示全部楼层
确实有这个问题,楼主找到好办法后共享下
回复 支持 反对

使用道具 举报

您需要登录后才可以回帖 登录 | 立即注册 新浪微博登陆

本版积分规则

 

GMT+8, 2019-10-15 06:34 , Processed in 1.376089 second(s), 36 queries .

关闭

扫一扫加入
本版微信群