标签云
asm恢复 bbed bootstrap$ dul In Memory kcbzib_kcrsds_1 kccpb_sanity_check_2 kfed MySQL恢复 ORA-00312 ORA-00607 ORA-00704 ORA-01110 ORA-01555 ORA-01578 ORA-08103 ORA-600 2131 ORA-600 2662 ORA-600 2663 ORA-600 3020 ORA-600 4000 ORA-600 4137 ORA-600 4193 ORA-600 4194 ORA-600 16703 ORA-600 kcbzib_kcrsds_1 ORA-600 KCLCHKBLK_4 ORA-15042 ORA-15196 ORACLE 12C oracle dul ORACLE PATCH Oracle Recovery Tools oracle加密恢复 oracle勒索 oracle勒索恢复 oracle异常恢复 Oracle 恢复 ORACLE恢复 ORACLE数据库恢复 oracle 比特币 OSD-04016 YOUR FILES ARE ENCRYPTED 勒索恢复 比特币加密文章分类
- Others (2)
- 中间件 (2)
- WebLogic (2)
- 操作系统 (102)
- 数据库 (1,664)
- DB2 (22)
- MySQL (72)
- Oracle (1,527)
- Data Guard (51)
- EXADATA (8)
- GoldenGate (21)
- ORA-xxxxx (159)
- ORACLE 12C (72)
- ORACLE 18C (6)
- ORACLE 19C (14)
- ORACLE 21C (3)
- Oracle 23ai (7)
- Oracle ASM (65)
- Oracle Bug (8)
- Oracle RAC (52)
- Oracle 安全 (6)
- Oracle 开发 (28)
- Oracle 监听 (28)
- Oracle备份恢复 (558)
- Oracle安装升级 (90)
- Oracle性能优化 (62)
- 专题索引 (5)
- 勒索恢复 (77)
- PostgreSQL (18)
- PostgreSQL恢复 (6)
- SQL Server (27)
- SQL Server恢复 (8)
- TimesTen (7)
- 达梦数据库 (2)
- 生活娱乐 (2)
- 至理名言 (11)
- 虚拟化 (2)
- VMware (2)
- 软件开发 (37)
- Asp.Net (9)
- JavaScript (12)
- PHP (2)
- 小工具 (20)
-
最近发表
- 通过alert日志分析客户自行对一个数据库恢复的来龙去脉和点评
- ORA-12514: TNS: 监听进程不能解析在连接描述符中给出的SERVICE_NAME
- ORA-01092 ORA-00604 ORA-01558故障处理
- ORA-65088: database open should be retried
- Oracle 19c异常恢复—ORA-01209/ORA-65088
- ORA-600 16703故障再现
- 数据库启动报ORA-27102 OSD-00026 O/S-Error: (OS 1455)
- .[metro777@cock.li].Elbie勒索病毒加密数据库恢复
- 应用连接错误,初始化mysql数据库恢复
- RAC默认服务配置优先节点
- Oracle 19c RAC 替换私网操作
- 监听报TNS-12541 TNS-12560 TNS-00511错误
- drop tablespace xxx including contents恢复
- Linux 8 修改网卡名称
- 如何修改集群的公网信息(包括 VIP) (Doc ID 1674442.1)
- 如何在 oracle 集群环境下修改私网信息 (Doc ID 2103317.1)
- ORA-600 [kcvfdb_pdb_set_clean_scn: cleanckpt] 相关bug
- ORA-600 krhpfh_03-1210故障处理
- 19c库启动报ORA-600 kcbzib_kcrsds_1
- DBMS_SESSION.set_context提示ORA-01031问题解决
标签归档:redo异常
Oracle 12c redo 丢失恢复
模拟redo丢失
对数据库的一个pdb模拟事务操作,然后abort库,并且删除所有redo,模拟生产环境redo丢失的case
[oracle@ora1221 oradata]$ ss SQL*Plus: Release 12.2.0.0.3 Production on Wed Jun 15 10:13:20 2016 Copyright (c) 1982, 2016, Oracle. All rights reserved. Connected to an idle instance. SQL> startup ORACLE instance started. Total System Global Area 2516582400 bytes Fixed Size 8260048 bytes Variable Size 671090224 bytes Database Buffers 1828716544 bytes Redo Buffers 8515584 bytes Database mounted. Database opened. SQL> SQL> SQL> set pages 100 SQL> show pdbs; CON_ID CON_NAME OPEN MODE RESTRICTED ---------- ------------------------------ ---------- ---------- 2 PDB$SEED READ ONLY NO 3 PDB1 MOUNTED 4 PDB2 MOUNTED SQL> select con_id,file#,checkpoint_change# from v$datafile_header order by 1; CON_ID FILE# CHECKPOINT_CHANGE# ---------- ---------- ------------------ 1 1 1500157 1 3 1500157 1 4 1500157 1 7 1500157 2 5 1371280 2 6 1371280 2 8 1371280 3 9 1499902 3 12 1499902 3 11 1499902 3 10 1499902 4 15 1499903 4 14 1499903 4 13 1499903 4 16 1499903 15 rows selected. SQL> alter PLUGGABLE database pdb1 open; Pluggable database altered. SQL> show pdbs; CON_ID CON_NAME OPEN MODE RESTRICTED ---------- ------------------------------ ---------- ---------- 2 PDB$SEED READ ONLY NO 3 PDB1 READ WRITE NO 4 PDB2 MOUNTED SQL> alter session set container=pdb1; Session altered. SQL> create user chf identified by oracle; User created. SQL> grant dba to chf; Grant succeeded. SQL> create table chf.t_pdb1_xifenfei as select * from dba_objects; Table created. SQL> delete from chf.t_pdb1_xifenfei; 72426 rows deleted. --另外一个节点 [oracle@ora1221 ~]$ ss SQL*Plus: Release 12.2.0.0.3 Production on Wed Jun 15 10:19:21 2016 Copyright (c) 1982, 2016, Oracle. All rights reserved. Connected to: Oracle Database 12c Enterprise Edition Release 12.2.0.0.3 - 64bit Production SQL> shutdown abort ORACLE instance shut down. [oracle@ora1221 orcl12c2]$ ls redo* redo01.log redo02.log redo03.log [oracle@ora1221 orcl12c2]$ rm redo0* [oracle@ora1221 orcl12c2]$ ls -l redo* ls: cannot access redo*: No such file or directory
尝试启动数据库
[oracle@ora1221 orcl12c2]$ ss SQL*Plus: Release 12.2.0.0.3 Production on Wed Jun 15 10:26:20 2016 Copyright (c) 1982, 2016, Oracle. All rights reserved. Connected to an idle instance. SQL> startup mount; ORACLE instance started. Total System Global Area 2516582400 bytes Fixed Size 8260048 bytes Variable Size 671090224 bytes Database Buffers 1828716544 bytes Redo Buffers 8515584 bytes Database mounted. 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/orcl12c2/redo01.log' ORA-27037: unable to obtain file status Linux-x86_64 Error: 2: No such file or directory Additional information: 7
使用隐含参数启动
----pfile里面增加 _allow_error_simulation=TRUE _allow_resetlogs_corruption=true ~ SQL> shutdown immediate; ORA-01109: database not open Database dismounted. ORACLE instance shut down SQL> startup pfile='/tmp/pfile' mount ORACLE instance started. Total System Global Area 2516582400 bytes Fixed Size 8260048 bytes Variable Size 671090224 bytes Database Buffers 1828716544 bytes Redo Buffers 8515584 bytes Database mounted. SQL> alter database open resetlogs; alter database open resetlogs * ERROR at line 1: ORA-00603: ORACLE server session terminated by fatal error ORA-01092: ORACLE instance terminated. Disconnection forced ORA-00600: internal error code, arguments: [kcbzib_kcrsds_1], [], [], [], [], [], [], [], [], [], [], [] Process ID: 36797 Session ID: 16 Serial number: 24277
继续重启库
ORA-600 kcbzib_kcrsds_1错误尝试重启数据库,如果不行,考虑使用bbed修改文件头信息
SQL> startup mount pfile='/tmp/pfile' ORACLE instance started. Total System Global Area 2516582400 bytes Fixed Size 8260048 bytes Variable Size 671090224 bytes Database Buffers 1828716544 bytes Redo Buffers 8515584 bytes Database mounted. SQL> recover database until cancel; ORA-00283: recovery session canceled due to errors ORA-16433: The database or pluggable database must be opened in read/write mode. SQL> alter database backup controlfile to trace as '/tmp/ctl'; alter database backup controlfile to trace as '/tmp/ctl' * ERROR at line 1: ORA-16433: The database or pluggable database must be opened in read/write mode.
重建控制文件
SQL> startup nomount pfile='/tmp/pfile' ORACLE instance started. Total System Global Area 2516582400 bytes Fixed Size 8260048 bytes Variable Size 671090224 bytes Database Buffers 1828716544 bytes Redo Buffers 8515584 bytes SQL> CREATE CONTROLFILE REUSE DATABASE "orcl12c2" RESETLOGS NOARCHIVELOG 2 MAXLOGFILES 50 3 MAXLOGMEMBERS 5 4 MAXDATAFILES 100 5 MAXINSTANCES 1 6 MAXLOGHISTORY 226 7 LOGFILE 8 GROUP 1 '/u01/app/oracle/oradata/orcl12c2/redo01.log' SIZE 200M, 9 GROUP 2 '/u01/app/oracle/oradata/orcl12c2/redo02.log' SIZE 200M, 10 GROUP 3 '/u01/app/oracle/oradata/orcl12c2/redo03.log' SIZE 200M 11 DATAFILE 12 '/u01/app/oracle/oradata/orcl12c2/system01.dbf', 13 '/u01/app/oracle/oradata/orcl12c2/sysaux01.dbf', 14 '/u01/app/oracle/oradata/orcl12c2/undotbs01.dbf', 15 '/u01/app/oracle/oradata/orcl12c2/pdbseed/system01.dbf', 16 '/u01/app/oracle/oradata/orcl12c2/pdbseed/sysaux01.dbf', 17 '/u01/app/oracle/oradata/orcl12c2/users01.dbf', 18 '/u01/app/oracle/oradata/orcl12c2/pdbseed/undotbs01.dbf', 19 '/u01/app/oracle/oradata/orcl12c2/pdb1/system01.dbf', 20 '/u01/app/oracle/oradata/orcl12c2/pdb1/sysaux01.dbf', 21 '/u01/app/oracle/oradata/orcl12c2/pdb1/undotbs01.dbf', 22 '/u01/app/oracle/oradata/orcl12c2/pdb1/users01.dbf', 23 '/u01/app/oracle/oradata/orcl12c2/pdb2/system01.dbf', 24 '/u01/app/oracle/oradata/orcl12c2/pdb2/sysaux01.dbf', 25 '/u01/app/oracle/oradata/orcl12c2/pdb2/undotbs01.dbf', 26 '/u01/app/oracle/oradata/orcl12c2/pdb2/users01.dbf' 27 CHARACTER SET AL32UTF8 28 ; Control file created. SQL> recover database until cancel; ORA-00283: recovery session canceled due to errors ORA-01610: recovery using the BACKUP CONTROLFILE option must be done SQL> recover database until cancel using backup controlfile; ORA-00279: change 1500161 generated at 06/15/2016 10:40:42 needed for thread 1 ORA-00289: suggestion : /u01/app/oracle/product/12.2.0/db_2/dbs/arch1_1_914582438.dbf ORA-00280: change 1500161 for thread 1 is in sequence #1 Specify log: {<RET>=suggested | filename | AUTO | CANCEL} /u01/app/oracle/oradata/orcl12c2/redo01.log Log applied. Media recovery complete. SQL> alter database open resetlogs; Database altered. 1 <strong>open过程alert日志</strong> 1 <strong>open pdb1</strong> 1 SQL> alter PLUGGABLE database pdb1 open; Pluggable database altered.
pdb1 open alert日志
2016-06-15T11:13:39.423057+08:00 alter PLUGGABLE database pdb1 open PDB1(3):Autotune of undo retention is turned on. 2016-06-15T11:13:39.495559+08:00 PDB1(3):Endian type of dictionary set to little PDB1(3):[40547] Successfully onlined Undo Tablespace 2. PDB1(3):Undo initialization finished serial:0 start:371149831 end:371149872 diff:41 ms (0.0 seconds) PDB1(3):Database Characterset for PDB1 is AL32UTF8 PDB1(3):********************************************************************* PDB1(3):WARNING: The following temporary tablespaces in container(PDB1) PDB1(3): contain no files. PDB1(3): This condition can occur when a backup controlfile has PDB1(3): been restored. It may be necessary to add files to these PDB1(3): tablespaces. That can be done using the SQL statement: PDB1(3): PDB1(3): ALTER TABLESPACE <tablespace_name> ADD TEMPFILE PDB1(3): PDB1(3): Alternatively, if these temporary tablespaces are no longer PDB1(3): needed, then they can be dropped. PDB1(3): Empty temporary tablespace: TEMP PDB1(3):********************************************************************* PDB1(3):Opatch validation is skipped for PDB PDB1 (con_id=0) PDB1(3):Opening pdb with no Resource Manager plan active Pluggable database PDB1 opened read write Completed: alter PLUGGABLE database pdb1 open
open pdb2
SQL> alter PLUGGABLE database pdb2 open; alter PLUGGABLE database pdb2 open * ERROR at line 1: ORA-00600: internal error code, arguments: [17090], [], [], [], [], [], [], [], [], [], [], []
分析alert日志和trace文件
--alert日志部分 PDB1(3):alter PLUGGABLE database pdb2 open PDB1(3):ORA-65118 signalled during: alter PLUGGABLE database pdb2 open... 2016-06-15T11:28:57.439963+08:00 PDB1(3):Unified Audit: Audit record write to table failed due to ORA-25153. Writing the audit record to OS spillover file. Please grep in the trace files for ORA-25153 for more diagnostic information. Errors in file /u01/app/oracle/diag/rdbms/orcl12c2/orcl12c2/trace/orcl12c2_ora_40547.trc (incident=29073) (PDBNAME=PDB1): ORA-00600: internal error code, arguments: [17090], [], [], [], [], [], [], [], [], [], [], [] PDB1(3):Incident details in: /u01/app/oracle/diag/rdbms/orcl12c2/orcl12c2/incident/incdir_29073/orcl12c2_ora_40547_i29073.trc PDB1(3):***************************************************************** PDB1(3):An internal routine has requested a dump of selected redo. PDB1(3):This usually happens following a specific internal error, when PDB1(3):analysis of the redo logs will help Oracle Support with the PDB1(3):diagnosis. PDB1(3):It is recommended that you retain all the redo logs generated (by PDB1(3):all the instances) during the past 12 hours, in case additional PDB1(3):redo dumps are required to help with the diagnosis. PDB1(3):***************************************************************** 2016-06-15T11:28:59.123041+08:00 PDB1(3):Use ADRCI or Support Workbench to package the incident. See Note 411.1 at My Oracle Support for error and packaging details. 2016-06-15T11:28:59.945667+08:00 Dumping diagnostic data in directory=[cdmp_20160615112859], requested by (instance=1, osid=40547), summary=[incident=29073]. 2016-06-15T11:35:59.987419+08:00 PDB1(3): alter PLUGGABLE database pdb2 open PDB1(3):ORA-65118 signalled during: alter PLUGGABLE database pdb2 open... --trace部分 PARSING IN CURSOR #0x7f051a3d7650 len=118 dep=1 uid=0 oct=3 lid=0 tim=372490287736 hv=1128335472 ad='0x6ca82f00' sqlid='gu930gd1n223h' select tablespace_name, tablespace_size, allocated_space, free_space, con_id from cdb_temp_free_space order by con_id END OF STMT EXEC #0x7f051a3d7650:c=0,e=144,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,plh=2538033465,tim=372490287732 FETCH #0x7f051a3d7650:c=0,e=290,p=0,cr=14,cu=0,mis=0,r=0,dep=1,og=4,plh=2538033465,tim=372490288109 WAIT #0x7f051bd5f870: nam='Disk file operations I/O' ela= 52 FileOperation=2 fileno=0 filetype=36 obj#=402 tim=372490288373 WAIT #0x7f051bd5f870: nam='Disk file operations I/O' ela= 17 FileOperation=8 fileno=1 filetype=36 obj#=402 tim=372490288577 WAIT #0x7f051bd5f870: nam='Disk file operations I/O' ela= 3 FileOperation=8 fileno=1 filetype=36 obj#=402 tim=372490288655 WAIT #0x7f051bd5f870: nam='Disk file operations I/O' ela= 690 FileOperation=8 fileno=1 filetype=36 obj#=402 tim=372490289365 WAIT #0x7f051bd5f870: nam='Disk file operations I/O' ela= 6 FileOperation=8 fileno=1 filetype=36 obj#=402 tim=372490289470 WAIT #0x7f051bd5f870: nam='Disk file operations I/O' ela= 445 FileOperation=8 fileno=1 filetype=36 obj#=402 tim=372490289934 WAIT #0x7f051bd5f870: nam='Disk file operations I/O' ela= 3 FileOperation=8 fileno=1 filetype=36 obj#=402 tim=372490289983 WAIT #0x7f051bd5f870: nam='Disk file operations I/O' ela= 375 FileOperation=8 fileno=1 filetype=36 obj#=402 tim=372490290374 WAIT #0x7f051bd5f870: nam='Disk file operations I/O' ela= 6 FileOperation=8 fileno=1 filetype=36 obj#=402 tim=372490290453 WAIT #0x7f051bd5f870: nam='Disk file operations I/O' ela= 367 FileOperation=8 fileno=1 filetype=36 obj#=402 tim=372490290839 WAIT #0x7f051bd5f870: nam='Disk file operations I/O' ela= 3 FileOperation=8 fileno=1 filetype=36 obj#=402 tim=372490290882 WAIT #0x7f051bd5f870: nam='Disk file operations I/O' ela= 355 FileOperation=8 fileno=1 filetype=36 obj#=402 tim=372490291252 WAIT #0x7f051bd5f870: nam='Disk file operations I/O' ela= 4 FileOperation=8 fileno=1 filetype=36 obj#=402 tim=372490291298 WAIT #0x7f051bd5f870: nam='Disk file operations I/O' ela= 276 FileOperation=8 fileno=1 filetype=36 obj#=402 tim=372490291590 WAIT #0x7f051bd5f870: nam='Disk file operations I/O' ela= 1 FileOperation=8 fileno=1 filetype=36 obj#=402 tim=372490291614 WAIT #0x7f051bd5f870: nam='Disk file operations I/O' ela= 256 FileOperation=8 fileno=1 filetype=36 obj#=402 tim=372490291879 WAIT #0x7f051bd5f870: nam='Disk file operations I/O' ela= 2 FileOperation=8 fileno=1 filetype=36 obj#=402 tim=372490291903 WAIT #0x7f051bd5f870: nam='Disk file operations I/O' ela= 261 FileOperation=8 fileno=1 filetype=36 obj#=402 tim=372490292172 WAIT #0x7f051bd5f870: nam='Disk file operations I/O' ela= 30 FileOperation=3 fileno=0 filetype=36 obj#=402 tim=372490292225 WAIT #0x7f051bd5f870: nam='Disk file operations I/O' ela= 934 FileOperation=8 fileno=1 filetype=36 obj#=402 tim=372490293171 WAIT #0x7f051bd5f870: nam='Disk file operations I/O' ela= 3 FileOperation=8 fileno=1 filetype=36 obj#=402 tim=372490293208 WAIT #0x7f051bd5f870: nam='Disk file operations I/O' ela= 245 FileOperation=8 fileno=1 filetype=36 obj#=402 tim=372490293465 WAIT #0x7f051bd5f870: nam='Disk file operations I/O' ela= 262 FileOperation=8 fileno=1 filetype=36 obj#=402 tim=372490293755 WAIT #0x7f051bd5f870: nam='Disk file operations I/O' ela= 1 FileOperation=8 fileno=1 filetype=36 obj#=402 tim=372490293780 WAIT #0x7f051bd5f870: nam='Disk file operations I/O' ela= 250 FileOperation=8 fileno=1 filetype=36 obj#=402 tim=372490294039 WAIT #0x7f051bd5f870: nam='Disk file operations I/O' ela= 256 FileOperation=8 fileno=1 filetype=36 obj#=402 tim=372490294323 WAIT #0x7f051bd5f870: nam='Disk file operations I/O' ela= 8 FileOperation=5 fileno=0 filetype=36 obj#=402 tim=372490294359 2016-06-15T11:36:00.055196+08:00 Incident 29074 created, dump file: /u01/app/oracle/diag/rdbms/orcl12c2/orcl12c2/incident/incdir_29074/orcl12c2_ora_40547_i29074.trc ORA-00600: internal error code, arguments: [17090], [], [], [], [], [], [], [], [], [], [], []
从中可以判断出来是由于CDB$ROOT的未增加tempfile导致
SQL> alter tablespace temp add tempfile '/u01/app/oracle/oradata/orcl12c2/temp01.dbf' reuse; Tablespace altered. SQL> alter PLUGGABLE database pdb2 open; Pluggable database altered.
查看数据库恢复情况
SQL> show pdbs; CON_ID CON_NAME OPEN MODE RESTRICTED ---------- ------------------------------ ---------- ---------- 2 PDB$SEED READ ONLY NO 3 PDB1 READ WRITE NO 4 PDB2 READ WRITE NO SQL> select con_id,file#,checkpoint_change#,resetlogs_change# from v$datafile_header; CON_ID FILE# CHECKPOINT_CHANGE# RESETLOGS_CHANGE# ---------- ---------- ------------------ ----------------- 1 1 2500167 1500164 1 3 2500167 1500164 1 4 2500167 1500164 2 5 1371280 1341067 2 6 1371280 1341067 1 7 2500167 1500164 2 8 1371280 1341067 3 9 2501017 1500164 3 10 2501017 1500164 3 11 2501017 1500164 3 12 2501017 1500164 4 13 2502748 1500164 4 14 2502748 1500164 4 15 2502748 1500164 4 16 2502748 1500164 15 rows selected.
至此基本上测试完成在在cdb环境中丢失redo的恢复。在生产中,需要把temp加全,并且建议重建数据库
发表在 ORACLE 12C, Oracle备份恢复
标签为 kcbzib_kcrsds_1, ORA-16433, ORA-600 17090, ORA-600 kcbzib_kcrsds_1, redo异常
评论关闭
深入分析一次ORA-00314错误
运行在win平台上的oracle 10G数据库,因为主机蓝屏,使用pe拷贝出来相关数据库文件,redo文件,控制文件,在恢复数据库过中遇到ORA-00314错误,而无法继续下去
恢复报错
SQL> recover database ORA-00283: 恢复会话因错误而取消 ORA-00314: 日志 3 (用于线程 1) 要求的序号 1366 与 1363 不匹配 ORA-00312: 联机日志 3 线程 1: 'DC:\ORADATA\INTERLIB\REDO03.LOG' 00314, 00000, "log %s of thread %s, expected sequence# %s doesn't match %s" // *Cause: The online log is corrupted or is an old version. // *Action: Find and install correct version of log or reset logs.
这里提示可以大概看出来,数据库进行恢复的时候,需要sequence 为1366的日志,但是与现在的1363不匹配。通过上面的解释我们很可能知道是redo文件异常或者弄错了redo文件
分析Oracle Database Recovery Check结果
控制文件scn相关信息,从这里可以看到数据库的控制文件scn为47714860,checkpoint scn为47711411
控制文件中关于数据库文件是scn为47711411,而且stop_scn为null
数据文件头scn是47711411,而且fuzzy为yes
redo的相关信息,这里我们可以看出当前redo的sequence为1366,first_scn为47711411,redo的写入顺序是redo01->redo02->redo03->redo01
通过这里整体分析,我们可以知道,数据库为非正常关闭,恢复应该从redo03(sequence 1366)开始进行恢复,但是为什么出现ORA-00314呢?通过dump redo header继续分析
dump redo header 分析
这个里面我们可以看到redo01的sequence 为16进制554等于10进制的1364,scn的范围为:0x000002d6e4ab-0x000002d7455b
这个里面我们可以看到redo02的sequence 为16进制555等于10进制的1365,scn的范围为:0x000002d7455b-0x000002d804b3
这个属于异常redo,注意第一个seq: 0×00000556等于十进制的1366(也就是表示该redo的sequence为1366),”Seq# 0000001363,SCN 0x000002d5e1c6-0x000002d6e4ab”表示在redo的文件头有部分信息记录的为sequence为1633,另外这里表示该文件最大scn为0x000002d6e4ab,和redo01的最小scn(0x000002d6e4ab-0x000002d7455b)/redo01的”Prev scn: 0×0000.02d5e1c6″与现在看到的redo03中的最小scn匹配.
通过这里可以明白,在主机蓝屏的时候由于某种异常,导致redo03中的部分信息修改为了sequence为1366,但是部分信息依然保留它上次的sequence为1363的信息,导致数据库在重新恢复的时候无法正常成功.
处理方法
该故障是由于current redo异常导致,根据经验(具体参考:ORACLE REDO各种异常恢复),一般使用隐含参数屏蔽前滚,然后强制拉库,绝大部分情况能够拉库成功,如果人品不好可能需要使用其他隐含参数甚至bbed等方式处理
ORACLE 12C redo异常恢复测试—打上patch恢复完全
在上一篇中,我们删除redo,然后通过非常规方法使得cdb open成功,但是pdb无法正常mount,通过查询mos确定是bug 16784143,具体见:ORACLE 12C redo异常恢复测试—部分pdb未正常open,通过给数据库打上12.1.0.3的patch之后,发现已经损坏的数据库的pdb无法正常open,但是在损坏之前如果是打上补丁的数据库其pdb可以正常open
patch信息
C:\Users\XIFENFEI>E:\oracle\product\11.2.0\dbhome_1\OPatch\opatch lspatches 17977915;WINDOWS DB BUNDLE PATCH 12.1.0.1.3 (64bit): (17977915)
session 1
XIFENFEI_CDB$ROOT@SYS> show con_name; CON_NAME ------------------------------ CDB$ROOT XIFENFEI_CDB$ROOT@SYS> create table t_xifenfei as select * from dba_users; 表已创建。 XIFENFEI_CDB$ROOT@SYS> delete from t_xifenfei; 已删除 35 行。 XIFENFEI_CDB$ROOT@SYS>
session 2
XIFENFEI_CDB$ROOT@SYS> show con_name; CON_NAME ------------------------------ PDB1 XIFENFEI_CDB$ROOT@SYS> create table t_xifenfei as select * from dba_users; 表已创建。 XIFENFEI_CDB$ROOT@SYS> delete from t_xifenfei; 已删除 36 行。 XIFENFEI_CDB$ROOT@SYS>
session 3
XIFENFEI_CDB$ROOT@SYS> alter session set container=pdb2; 会话已更改。 XIFENFEI_CDB$ROOT@SYS> alter database open; 数据库已更改。 XIFENFEI_CDB$ROOT@SYS> show con_name; CON_NAME ------------------------------ PDB2 XIFENFEI_CDB$ROOT@SYS>
session 4
XIFENFEI_CDB$ROOT@SYS> shutdown abort; ORACLE 例程已经关闭。
删除所有redo,并启动数据库
C:\Users\XIFENFEI>sqlplus / as sysdba SQL*Plus: Release 12.1.0.1.0 Production on 星期日 3月 30 21:07:55 2014 Copyright (c) 1982, 2013, Oracle. All rights reserved. 已连接到空闲例程。 idle> startup mount; ORACLE 例程已经启动。 Total System Global Area 400846848 bytes Fixed Size 2440024 bytes Variable Size 289408168 bytes Database Buffers 100663296 bytes Redo Buffers 8335360 bytes 数据库装载完毕。 idle> alter database open; alter database open * 第 1 行出现错误: ORA-00313: 无法打开日志组 3 (用于线程 1) 的成员 ORA-00312: 联机日志 3 线程 1: 'E:\APP\XIFENFEI\ORADATA\XIFENFEI\REDO03.LOG' ORA-27041: 无法打开文件 OSD-04002: 无法打开文件 O/S-Error: (OS 2) 系统找不到指定的文件。 尝试恢复数据库 1 idle> select status,group# from v$log; STATUS GROUP# ---------------- ---------- INACTIVE 1 CURRENT 3 INACTIVE 2 idle> recover database until cancel; ORA-00279: 更改 2821739 (在 03/30/2014 20:58:39 生成) 对于线程 1 是必需的 ORA-00289: 建议: E:\APP\XIFENFEI\FAST_RECOVERY_AREA\XIFENFEI\ARCHIVELOG\2014_03_30\O1_MF_1_36_%U_ .ARC ORA-00280: 更改 2821739 (用于线程 1) 在序列 #36 中 指定日志: {<RET>=suggested | filename | AUTO | CANCEL} auto ORA-00308: 无法打开归档日志 'E:\APP\XIFENFEI\FAST_RECOVERY_AREA\XIFENFEI\ARCHIVELOG\2014_03_30\O1_MF_1_36_%U _.ARC' ORA-27041: 无法打开文件 OSD-04002: 无法打开文件 O/S-Error: (OS 2) 系统找不到指定的文件。 ORA-00308: 无法打开归档日志 'E:\APP\XIFENFEI\FAST_RECOVERY_AREA\XIFENFEI\ARCHIVELOG\2014_03_30\O1_MF_1_36_%U _.ARC' ORA-27041: 无法打开文件 OSD-04002: 无法打开文件 O/S-Error: (OS 2) 系统找不到指定的文件。 ORA-01547: 警告: RECOVER 成功但 OPEN RESETLOGS 将出现如下错误 ORA-01194: 文件 1 需要更多的恢复来保持一致性 ORA-01110: 数据文件 1: 'E:\APP\XIFENFEI\ORADATA\XIFENFEI\SYSTEM01.DBF' idle> alter database open resetlogs; alter database open resetlogs * 第 1 行出现错误: ORA-01194: 文件 1 需要更多的恢复来保持一致性 ORA-01110: 数据文件 1: 'E:\APP\XIFENFEI\ORADATA\XIFENFEI\SYSTEM01.DBF' idle> alter system set "_allow_resetlogs_corruption"=true scope=spfile; 系统已更改。 idle> shutdown immediate ORA-01109: 数据库未打开 已经卸载数据库。 ORACLE 例程已经关闭。 idle> startup mount; ORACLE 例程已经启动。 Total System Global Area 400846848 bytes Fixed Size 2440024 bytes Variable Size 289408168 bytes Database Buffers 100663296 bytes Redo Buffers 8335360 bytes 数据库装载完毕。 idle> recover database until cancel; ORA-00279: 更改 2821739 (在 03/30/2014 20:58:39 生成) 对于线程 1 是必需的 ORA-00289: 建议: E:\APP\XIFENFEI\FAST_RECOVERY_AREA\XIFENFEI\ARCHIVELOG\2014_03_30\O1_MF_1_36_%U_ .ARC ORA-00280: 更改 2821739 (用于线程 1) 在序列 #36 中 指定日志: {<RET>=suggested | filename | AUTO | CANCEL} cancel ORA-01547: 警告: RECOVER 成功但 OPEN RESETLOGS 将出现如下错误 ORA-01194: 文件 1 需要更多的恢复来保持一致性 ORA-01110: 数据文件 1: 'E:\APP\XIFENFEI\ORADATA\XIFENFEI\SYSTEM01.DBF' ORA-01112: 未启动介质恢复 idle> alter database open resetlogs; alter database open resetlogs * 第 1 行出现错误: ORA-01092: ORACLE instance terminated. Disconnection forced ORA-00704: bootstrap process failure ORA-00704: bootstrap process failure ORA-00600: internal error code, arguments: [2662], [0], [2821746], [0], [2822649], [4194545], [], [], [], [], [], [] 进程 ID: 6880 会话 ID: 242 序列号: 3 idle> startup mount; SP2-0642: SQL*Plus 内部错误状态 2133, 上下文 3114:0:0 继续执行将不安全 ORA-03114: 未连接到 ORACLE --使用bbed解决dul问题,出现新问题 idle> alter database open; alter database open * 第 1 行出现错误: ORA-01092: ORACLE instance terminated. Disconnection forced ORA-00600: internal error code, arguments: [4194], [61], [4], [], [], [], [], [], [], [], [], [] 进程 ID: 5932 会话 ID: 242 序列号: 3 idle> ALTER SYSTEM SET UNDO_MANAGEMENT='MANAUL' SCOPE=SPFILE; 系统已更改。 idle> SHUTDOWN IMMEDIATE; ORA-01109: 数据库未打开 已经卸载数据库。 ORACLE 例程已经关闭。 idle> startup mount; ORACLE 例程已经启动。 Total System Global Area 400846848 bytes Fixed Size 2440024 bytes Variable Size 281019560 bytes Database Buffers 109051904 bytes Redo Buffers 8335360 bytes 数据库装载完毕。 idle> alter database open; 数据库已更改。 [/sql <strong>open pdb</strong> 1 idle> alter session set container=pdb1; 会话已更改。 idle> alter database open; 数据库已更改。 idle> alter session set container=pdb2; 会话已更改。 idle> alter database open; 数据库已更改。 idle> alter session set container=pdb1; 会话已更改。
ORACLE 12C的bug估计不会太少,急于上12C的朋友,强烈建议打上最先patch,少踩一些雷