- 浏览: 956737 次
- 性别:
- 来自: 杭州
文章分类
最新评论
-
孤星119:
好熟悉的数据库字段啊, 上家公司做的项目每天都跟这些字段打招呼 ...
Oracle exp compress参数引起的空间浪费 -
itspace:
quxiaoyong 写道遇到个问题,网上一搜,全他妈这篇文章 ...
数据库连接错误ORA-28547 -
quxiaoyong:
遇到个问题,网上一搜,全他妈这篇文章。你转来转去的有意思吗?
数据库连接错误ORA-28547 -
hctech:
关于version count过高的问题,不知博主是否看过ey ...
某客户数据库性能诊断报告 -
itspace:
invalid 写道写的不错,我根据这个来安装,有点理解错误了 ...
AIX 配置vncserver
事情起源:
客户需求:数据文件过大,客户业务表格清空数据之后需要缩小数据文件
SQL> select file_id,max(BLOCK_ID) from dba_extents where OWNER='TERSA' and SEGMENT_NAME='AA065' group by file_id;
FILE_ID MAX(BLOCK_ID)
---------- -------------
4 102537
SQL> select max(BLOCK_ID) from dba_extents where file_id=4;
MAX(BLOCK_ID)
-------------
102537
SQL> truncate table TERSA.AA065;
Table truncated.
SQL> select max(BLOCK_ID) from dba_extents where file_id=4;
MAX(BLOCK_ID)
-------------
22793
SQL> select 22793*8192 from dual;
22793*8192
----------
186720256
SQL> !ls -l /lank/db/lank/users01.dbf
-rw-r----- 1 ora10g dba 841490432 Nov 14 13:18 /lank/db/lank/users01.dbf
客户将其缩小至200m
SQL> alter database datafile '/lank/db/lank/users01.dbf' resize 200m;
Database altered.
谁知!!!出于业务原因,该库在指定时间进行定期闪回,由于4号文件进行了resize缩小数据文件操作,闪回不支持
SQL> flashback database to restore point p1;
flashback database to restore point p1
*
ERROR at line 1:
ORA-38766: cannot flashback data file 4; file resized smaller
ORA-01110: data file 4: '/lank/db/lank/users01.dbf'
这时,同事将4号文件offline,将数据库进行了强制闪回,这里需要注意的是闪回之后如果打开数据库,4号文件的数据将全部丢失,同事看到此错误时,心里没底了。
SQL> alter database datafile 4 offline;
Database altered.
SQL> flashback database to restore point p1;
flashback database to restore point p1
*
ERROR at line 1:
ORA-38795: warning: FLASHBACK succeeded but OPEN RESETLOGS would get error
below
ORA-01245: offline file 4 will be lost if RESETLOGS is done
ORA-01110: data file 4: '/lank/db/lank/users01.dbf'
于是打算今天的数据不进行闪回,将数据库恢复至最新状态
SQL> recover database;
ORA-00279: change 11000524201524 generated at 11/14/2011 13:09:10 needed for
thread 1
ORA-00289: suggestion : /lank/db/arch/1_425_764441717.dbf
ORA-00280: change 11000524201524 for thread 1 is in sequence #425
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
auto
ORA-00279: change 11000524205605 generated at 11/14/2011 13:10:25 needed for
thread 1
ORA-00289: suggestion : /lank/db/arch/1_426_764441717.dbf
ORA-00280: change 11000524205605 for thread 1 is in sequence #426
ORA-00278: log file '/lank/db/arch/1_425_764441717.dbf' no longer needed for
this recovery
。。。
Log applied.
Media recovery complete.
SQL> alter database datafile 4 online;
Database altered.
悲剧的是数据库打开时,出现[3619]错误
SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-00600: internal error code, arguments: [3619], [1], [0], [], [], [], [], []
后台日志显示:
Mon Nov 14 13:56:34 CST 2011
Errors in file /app/admin/lank/udump/lank_ora_5394.trc:
ORA-00600: internal error code, arguments: [3619], [1], [0], [], [], [], [], []
ORA-600 signalled during: alter database open...
跟踪文件显示:
Media Recovery drop redo thread 1
File 1 (stop scn 11000524225166) completed recovery at checkpoint scn 11000524225166
File 3 (stop scn 11000524225166) completed recovery at checkpoint scn 11000524225166
File 5 (stop scn 11000524225166) completed recovery at checkpoint scn 11000524225166
File 6 (stop scn 11000524225166) completed recovery at checkpoint scn 11000524225166
File 7 (stop scn 11000524225166) completed recovery at checkpoint scn 11000524225166
File 8 (stop scn 11000524225166) completed recovery at checkpoint scn 11000524225166
File 9 (stop scn 11000524225166) completed recovery at checkpoint scn 11000524225166
File 10 (stop scn 11000524225166) completed recovery at checkpoint scn 11000524225166
File 11 (stop scn 11000524225166) completed recovery at checkpoint scn 11000524225166
*** 2011-11-14 13:56:34.778
ksedmp: internal or fatal error
ORA-00600: internal error code, arguments: [3619], [1], [0], [], [], [], [], []
Current SQL statement for this session:
进一步检查相关文件的ckpt
SQL> select file#,checkpoint_change#,last_change#,status from v$datafile;
FILE# CHECKPOINT_CHANGE# LAST_CHANGE# STATUS
---------- ------------------- ------------------- -------
1 11000524225166 11000524225166 SYSTEM
3 11000524225166 11000524225166 ONLINE
4 11000524201524 ONLINE
5 11000524225166 11000524225166 ONLINE
6 11000524225166 11000524225166 ONLINE
7 11000524225166 11000524225166 ONLINE
8 11000524225166 11000524225166 ONLINE
9 11000524225166 11000524225166 ONLINE
10 11000524225166 11000524225166 ONLINE
11 11000524225166 11000524225166 ONLINE
10 rows selected.
SQL> select file#,fuzzy,checkpoint_change# from v$datafile_header;
FILE# FUZ CHECKPOINT_CHANGE#
---------- --- -------------------
1 NO 11000524225166
3 NO 11000524225166
4 NO 11000524225167
5 NO 11000524225166
6 NO 11000524225166
7 NO 11000524225166
8 NO 11000524225166
9 NO 11000524225166
10 NO 11000524225166
11 NO 11000524225166
10 rows selected.
SQL> select checkpoint_change#,last_change# from v$datafile;
CHECKPOINT_CHANGE# LAST_CHANGE#
----------------------- ---------------------
11000524225167 11000524225167
11000524225167 11000524225167
11000524225167 11000524225167
11000524225167 11000524225167
11000524225167 11000524225167
11000524225167 11000524225167
11000524225167 11000524225167
11000524225167 11000524225167
11000524225167 11000524225167
11000524225167 11000524225167
10 rows selected.
SQL>
SQL> select file#,fuzzy,checkpoint_change# from v$datafile_header;
FILE# FUZ CHECKPOINT_CHANGE#
---------- --- -----------------------
1 NO 11000524225166
3 NO 11000524225166
4 NO 11000524225167
5 NO 11000524225166
6 NO 11000524225166
7 NO 11000524225166
8 NO 11000524225166
9 NO 11000524225166
10 NO 11000524225166
11 NO 11000524225166
10 rows selected.
SQL> alter database backup controlfile to trace noresetlogs;
Database altered.
cp /lank/db/lank/control01.ctl /lank/db/lank/control01.ctl_bak
重建控制文件,并进行数据库recover
SQL> recover database;
Media recovery complete.
SQL> select file#,fuzzy,checkpoint_change# from v$datafile_header;
FILE# FUZ CHECKPOINT_CHANGE#
---------- --- -----------------------
1 NO 11000524225167
3 NO 11000524225167
4 NO 11000524225167
5 NO 11000524225167
6 NO 11000524225167
7 NO 11000524225167
8 NO 11000524225167
9 NO 11000524225167
10 NO 11000524225167
11 NO 11000524225167
10 rows selected.
SQL> select checkpoint_change#,last_change# from v$datafile;
CHECKPOINT_CHANGE# LAST_CHANGE#
----------------------- ---------------------
11000524225167 11000524225167
11000524225167 11000524225167
11000524225167 11000524225167
11000524225167 11000524225167
11000524225167 11000524225167
11000524225167 11000524225167
11000524225167 11000524225167
11000524225167 11000524225167
11000524225167 11000524225167
11000524225167 11000524225167
10 rows selected.
SQL>
SQL> alter database open;
Database altered.
客户需求:数据文件过大,客户业务表格清空数据之后需要缩小数据文件
SQL> select file_id,max(BLOCK_ID) from dba_extents where OWNER='TERSA' and SEGMENT_NAME='AA065' group by file_id;
FILE_ID MAX(BLOCK_ID)
---------- -------------
4 102537
SQL> select max(BLOCK_ID) from dba_extents where file_id=4;
MAX(BLOCK_ID)
-------------
102537
SQL> truncate table TERSA.AA065;
Table truncated.
SQL> select max(BLOCK_ID) from dba_extents where file_id=4;
MAX(BLOCK_ID)
-------------
22793
SQL> select 22793*8192 from dual;
22793*8192
----------
186720256
SQL> !ls -l /lank/db/lank/users01.dbf
-rw-r----- 1 ora10g dba 841490432 Nov 14 13:18 /lank/db/lank/users01.dbf
客户将其缩小至200m
SQL> alter database datafile '/lank/db/lank/users01.dbf' resize 200m;
Database altered.
谁知!!!出于业务原因,该库在指定时间进行定期闪回,由于4号文件进行了resize缩小数据文件操作,闪回不支持
SQL> flashback database to restore point p1;
flashback database to restore point p1
*
ERROR at line 1:
ORA-38766: cannot flashback data file 4; file resized smaller
ORA-01110: data file 4: '/lank/db/lank/users01.dbf'
这时,同事将4号文件offline,将数据库进行了强制闪回,这里需要注意的是闪回之后如果打开数据库,4号文件的数据将全部丢失,同事看到此错误时,心里没底了。
SQL> alter database datafile 4 offline;
Database altered.
SQL> flashback database to restore point p1;
flashback database to restore point p1
*
ERROR at line 1:
ORA-38795: warning: FLASHBACK succeeded but OPEN RESETLOGS would get error
below
ORA-01245: offline file 4 will be lost if RESETLOGS is done
ORA-01110: data file 4: '/lank/db/lank/users01.dbf'
于是打算今天的数据不进行闪回,将数据库恢复至最新状态
SQL> recover database;
ORA-00279: change 11000524201524 generated at 11/14/2011 13:09:10 needed for
thread 1
ORA-00289: suggestion : /lank/db/arch/1_425_764441717.dbf
ORA-00280: change 11000524201524 for thread 1 is in sequence #425
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
auto
ORA-00279: change 11000524205605 generated at 11/14/2011 13:10:25 needed for
thread 1
ORA-00289: suggestion : /lank/db/arch/1_426_764441717.dbf
ORA-00280: change 11000524205605 for thread 1 is in sequence #426
ORA-00278: log file '/lank/db/arch/1_425_764441717.dbf' no longer needed for
this recovery
。。。
Log applied.
Media recovery complete.
SQL> alter database datafile 4 online;
Database altered.
悲剧的是数据库打开时,出现[3619]错误
SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-00600: internal error code, arguments: [3619], [1], [0], [], [], [], [], []
后台日志显示:
Mon Nov 14 13:56:34 CST 2011
Errors in file /app/admin/lank/udump/lank_ora_5394.trc:
ORA-00600: internal error code, arguments: [3619], [1], [0], [], [], [], [], []
ORA-600 signalled during: alter database open...
跟踪文件显示:
Media Recovery drop redo thread 1
File 1 (stop scn 11000524225166) completed recovery at checkpoint scn 11000524225166
File 3 (stop scn 11000524225166) completed recovery at checkpoint scn 11000524225166
File 5 (stop scn 11000524225166) completed recovery at checkpoint scn 11000524225166
File 6 (stop scn 11000524225166) completed recovery at checkpoint scn 11000524225166
File 7 (stop scn 11000524225166) completed recovery at checkpoint scn 11000524225166
File 8 (stop scn 11000524225166) completed recovery at checkpoint scn 11000524225166
File 9 (stop scn 11000524225166) completed recovery at checkpoint scn 11000524225166
File 10 (stop scn 11000524225166) completed recovery at checkpoint scn 11000524225166
File 11 (stop scn 11000524225166) completed recovery at checkpoint scn 11000524225166
*** 2011-11-14 13:56:34.778
ksedmp: internal or fatal error
ORA-00600: internal error code, arguments: [3619], [1], [0], [], [], [], [], []
Current SQL statement for this session:
进一步检查相关文件的ckpt
SQL> select file#,checkpoint_change#,last_change#,status from v$datafile;
FILE# CHECKPOINT_CHANGE# LAST_CHANGE# STATUS
---------- ------------------- ------------------- -------
1 11000524225166 11000524225166 SYSTEM
3 11000524225166 11000524225166 ONLINE
4 11000524201524 ONLINE
5 11000524225166 11000524225166 ONLINE
6 11000524225166 11000524225166 ONLINE
7 11000524225166 11000524225166 ONLINE
8 11000524225166 11000524225166 ONLINE
9 11000524225166 11000524225166 ONLINE
10 11000524225166 11000524225166 ONLINE
11 11000524225166 11000524225166 ONLINE
10 rows selected.
SQL> select file#,fuzzy,checkpoint_change# from v$datafile_header;
FILE# FUZ CHECKPOINT_CHANGE#
---------- --- -------------------
1 NO 11000524225166
3 NO 11000524225166
4 NO 11000524225167
5 NO 11000524225166
6 NO 11000524225166
7 NO 11000524225166
8 NO 11000524225166
9 NO 11000524225166
10 NO 11000524225166
11 NO 11000524225166
10 rows selected.
SQL> select checkpoint_change#,last_change# from v$datafile;
CHECKPOINT_CHANGE# LAST_CHANGE#
----------------------- ---------------------
11000524225167 11000524225167
11000524225167 11000524225167
11000524225167 11000524225167
11000524225167 11000524225167
11000524225167 11000524225167
11000524225167 11000524225167
11000524225167 11000524225167
11000524225167 11000524225167
11000524225167 11000524225167
11000524225167 11000524225167
10 rows selected.
SQL>
SQL> select file#,fuzzy,checkpoint_change# from v$datafile_header;
FILE# FUZ CHECKPOINT_CHANGE#
---------- --- -----------------------
1 NO 11000524225166
3 NO 11000524225166
4 NO 11000524225167
5 NO 11000524225166
6 NO 11000524225166
7 NO 11000524225166
8 NO 11000524225166
9 NO 11000524225166
10 NO 11000524225166
11 NO 11000524225166
10 rows selected.
SQL> alter database backup controlfile to trace noresetlogs;
Database altered.
cp /lank/db/lank/control01.ctl /lank/db/lank/control01.ctl_bak
重建控制文件,并进行数据库recover
SQL> recover database;
Media recovery complete.
SQL> select file#,fuzzy,checkpoint_change# from v$datafile_header;
FILE# FUZ CHECKPOINT_CHANGE#
---------- --- -----------------------
1 NO 11000524225167
3 NO 11000524225167
4 NO 11000524225167
5 NO 11000524225167
6 NO 11000524225167
7 NO 11000524225167
8 NO 11000524225167
9 NO 11000524225167
10 NO 11000524225167
11 NO 11000524225167
10 rows selected.
SQL> select checkpoint_change#,last_change# from v$datafile;
CHECKPOINT_CHANGE# LAST_CHANGE#
----------------------- ---------------------
11000524225167 11000524225167
11000524225167 11000524225167
11000524225167 11000524225167
11000524225167 11000524225167
11000524225167 11000524225167
11000524225167 11000524225167
11000524225167 11000524225167
11000524225167 11000524225167
11000524225167 11000524225167
11000524225167 11000524225167
10 rows selected.
SQL>
SQL> alter database open;
Database altered.
发表评论
-
buffer cache 的内部结构
2020-03-18 14:21 499BUFFER CACHE作为数据块的 ... -
Oracle OMC介绍
2020-03-18 13:19 431Oracle管理云服务(OMC)的大数据平台,自动收集的企业 ... -
参加Oracle勒索病毒防范专题培训会议
2019-09-27 17:15 4082019年7月22日,受邀参加Oracle勒索病毒防范专题培训 ... -
记一次内存换IO的Oracle优化
2019-09-27 16:50 776某客户数据库从P595物理 ... -
如何定位Oracle SQL执行计划变化的原因
2019-07-03 14:49 1329性能优化最难的是能够 ... -
如何定位Oracle SQL执行计划变化的原因
2018-10-30 09:24 1185性能优化最难的是能够 ... -
数据库性能优化目标
2018-10-08 10:59 457从数据库性能优化的场 ... -
数据库无法打开的原因及解决办法
2018-10-05 20:45 1888数据库的启动是一个相当复杂的过程。比如,Oracle在启动之前 ... -
怎么样彻底删除数据库?
2018-09-18 11:10 539Oracle提供了drop database命令用来删除数据库 ... -
Oracle减少日志量的方法
2018-09-10 10:17 793LGWR进程将LOG BUFFER中的 ... -
如何快速关闭数据库
2018-09-09 13:14 1187“一朝被蛇咬,十年怕井绳”。在没被“蛇”咬之前,很多DBA喜欢 ... -
关于《如何落地智能化运维》PPT
2018-05-17 10:19 1076在DTCC 2018发表《如何落地智能化运维》演讲,主要内容如 ... -
记录在redhat5.8平台安装oracle11.2容易忽视的几个问题
2018-05-11 19:58 523问题一:ping不通问题 在虚拟机上安装好linux系统后, ... -
《Oracle DBA实战攻略》第一章
2018-05-11 10:42 877即日起,不定期更新《OracleDBA实战攻略》一书电子版,请 ... -
Oracle 12c新特性
2018-05-11 10:33 840查询所有pdb [oracle@gj4 ~]$ sqlplu ... -
关于修改memory_target的值后数据库无法启动的问题
2017-02-28 12:24 3913操作系统:RHEL6.5 数据库版本:11.2.0.4 ... -
10g rac安装error while loading shared libraries libpthread.so.0 问题
2017-02-28 12:22 62311g rac安装在二节点跑脚本一般会报此错误: 解决这个问 ... -
记一次Oracle会话共享模式故障处理过程
2017-02-27 19:16 745故障简述 XXX第八人民医院HIS数据库7月13日11点左右从 ... -
RESMGR:cpu quantum等待事件处理过程
2017-02-27 18:23 2433由于数据库上线过程中出现大量的RESMGR:cpu quant ... -
谈谈log file sync
2014-03-19 14:18 1672数据库中的log file sync等待事件指的是,当user ...
相关推荐
在oracle里面运行一下,解决Exception java.sql.SQLException ORA-00600 内部错误代码
ORA-00600 [16703].docx
ora-00600 [1403]错误的原理以及解决方案
关于oracle做恢复操作时启动数据库报错,通常是由于rman做了恢复操作导致的报错. 通过继续执行恢复指令而恢复数据库,成功启动数据库.
ORA-00600:internalerrorcode,arguments:[kcblasm_1],[103],[],[],[],[],[],[] TueAug1209:20:17CST2014 Errorsinfile/u01/app/oracle/admin/orcl/udump/orcl_ora_29974.trc: ORA-00600:internalerrorcode,arguments...
NULL 博文链接:https://gembler.iteye.com/blog/346242
记录一次通过隐含参数的恢复报错处理
ORA-00600: 内部错误代码, 参数: [kcratr_nab_less_than_odr], [1], [13], [1990],这是我自己整理的,已经试验过了
在运行查询SELECT * FROM V$SESSION 会出现ORA-29275:部分多字节字符的错误,这是什么原因开始我不得其解,网上也没有介绍什么好办法。本文给出答案。
在非法关机以后,Oracle数据库经常会出现这个错误: EXP-00056:ORACLE错误...报错ORA-00600: 内部错误代码,参数: [kcratr1_lostwrt], [], [], [], [], [], [], [] 这个错误就不用具体研究是什么意思了,下面是解决
ora-00604 错误 解决 方法 ora-00604 错误 解决 方法 ora-00604 错误 解决 方法 ora-00604 错误 解决 方法 ora-00604 错误 解决 方法ora-00604 错误 解决 方法
使用工具IMPDP导入数据时ORA-39002、ORA-39070错误排查。使用工具IMPDP导入数据时ORA-39002、ORA-39070错误排查 使用工具IMPDP导入数据时ORA-39002、ORA-39070错误排查
客户端进行连接的时候,系统不定期出现ora-12520,ora-12516的连接问题, 问题解决方案建议: 1、增加process和session的连接数。 2、检查连接的应用,是不是有没有释放的连接。 3、将修改参数local_listener中的vip为...
oracle数据库ora-01152和ora-01110的解决办法
如何解决ORACLE系统Ora-00600错误故障.pdf
oracle数据库优化之后,报错报错“ora-00838”的处理方法
Patch 8922013: ORA-00600: INTERNAL ERROR CODE, ARGUMENTS: [17059], [0X93953434]
oracle启动失败,ORA-00702报错,windows,linux系统下解决办法
用oracle数据库新建连接时遇到ora-12505,此问题解决后又出现ora-12519错误,郁闷的半天,经过一番折腾问题解决,下面小编把我的两种解决方案分享给大家,仅供参考。 解决方案一: 今天工作时在新建连接的时候遇到...