标签云
asm恢复 bbed bootstrap$ dul kcbzib_kcrsds_1 kccpb_sanity_check_2 kcratr_nab_less_than_odr MySQL恢复 obet ORA-00312 ORA-00704 ORA-00742 ORA-01110 ORA-01200 ORA-01555 ORA-01578 ORA-01595 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-600 kcratr_nab_less_than_odr 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 比特币加密文章分类
- MongoDB (1)
- MongoDB恢复 (1)
- Others (2)
- 中间件 (2)
- WebLogic (2)
- 操作系统 (112)
- 数据库 (1,885)
- DB2 (22)
- MySQL (82)
- Oracle (1,710)
- Data Guard (53)
- EXADATA (8)
- GoldenGate (24)
- ORA-xxxxx (168)
- ORACLE 12C (72)
- ORACLE 18C (6)
- ORACLE 19C (15)
- ORACLE 21C (3)
- Oracle 23ai (8)
- Oracle ASM (73)
- Oracle Bug (8)
- Oracle RAC (56)
- Oracle 安全 (6)
- Oracle 开发 (28)
- Oracle 监听 (29)
- Oracle备份恢复 (663)
- Oracle安装升级 (108)
- Oracle性能优化 (62)
- 专题索引 (5)
- 勒索恢复 (91)
- PostgreSQL (37)
- pdu工具 (7)
- PostgreSQL恢复 (13)
- SQL Server (34)
- SQL Server恢复 (14)
- TimesTen (7)
- 达梦数据库 (5)
- 达梦恢复 (3)
- 生活娱乐 (2)
- 至理名言 (11)
- 虚拟化 (2)
- VMware (2)
- 软件开发 (51)
- Asp.Net (9)
- JavaScript (12)
- PHP (2)
- 小工具 (34)
- MongoDB (1)
-
最近发表
- 恢复案例分享:win丢失数据文件恢复
- 记录19c ASM磁盘掉线触发ORA-59048故障处理
- aix平台Oracle数据库由于断电无法启动故障处理
- 断电引起ORA-600 3005故障处理
- 记录一次云存储资源到期之后引起的数据库恢复
- Firebird 损坏数据库数据抢救工具—firebirdext
- Oracle 19c 202610补丁(RUs+OJVM)-19.33
- 由于con$.con#值达到极限,触发数据库增加约束报ORA-600 12807错误
- MongoDB 离线提取工具—mongoext
- .bres勒索病毒加密数据库dmp文件可恢复
- ORA-600 krsm_mrp_rebuild_lf_list.lno_mismatch
- 记录一次0丢失的ORA-00354: 损坏重做日志块标头故障恢复
- 不当数据库恢复操作导致一个月数据丢失
- obet快速修复oracle 位图损坏块
- 不太常见的10.2.0.1的oracle redo损坏恢复
- obet forcecopy功能抢救硬件故障中的数据文件
- kcratr_nab_less_than_odr和system坏块故障处理
- 通过obet 恢复system坏块,打开数据库
- obet dbv功能完整说明
- 分享一例运行在aix上的sap系统数据库恢复过程
分类目录归档:ORA-xxxxx
ORA-00001: unique constraint (PERFSTAT.STATS$SQL_SUMMARY_PK) violated
出现如下错误(ORA-00001: unique constraint (PERFSTAT.STATS$SQL_SUMMARY_PK) violated)
Oracle9i Enterprise Edition Release 9.2.0.6.0 - 64bit Production With the Partitioning option JServer Release 9.2.0.6.0 - Production ORACLE_HOME = /oracle9/app/product/9.2.0 System name: AIX Node name: zwq_bill_1 Release: 1 Version: 6 Machine: 00F64FF34C00 Instance name: bill1 Redo thread mounted by this instance: 1 Oracle process number: 30 Unix process pid: 46531060, image: oracle@zwq_bill_1 (J000) *** SESSION ID:(218.47085) 2012-04-02 19:30:45.561 *** 2012-04-02 19:30:45.561 ORA-12012: error on auto execute of job 1 ORA-00001: unique constraint (PERFSTAT.STATS$SQL_SUMMARY_PK) violated ORA-06512: at "PERFSTAT.STATSPACK", line 1361 ORA-06512: at "PERFSTAT.STATSPACK", line 2471 ORA-06512: at "PERFSTAT.STATSPACK", line 91 ORA-06512: at line 1
这个是oracle的一个Bug 2784796,提供解决方法有
1.run the statspack at level 0
2.restart the instance
3.set cursor sharing to exact (probably not feasible)
4.禁用主键,创建合适非唯一index
ALTER TABLE PERFSTAT.STATS$SQL_SUMMARY MODIFY CONSTRAINT STATS$SQL_SUMMARY_PK DISABLE NOVALIDATE;
5.修改STATS$V_$SQLXS视图
分析思路如下:
1)根据主键冲突找到主键包含列(spctab.sql)
create table STATS$SQL_SUMMARY
(snap_id number(6) not null
,dbid number not null
,instance_number number not null
,text_subset varchar2(31) not null
,sql_text varchar2(1000)
,sharable_mem number
,sorts number
,module varchar2(64)
,loaded_versions number
,fetches number
,executions number
,loads number
,invalidations number
,parse_calls number
,disk_reads number
,buffer_gets number
,rows_processed number
,command_type number
,address raw(8)
,hash_value number
,version_count number
,cpu_time number
,elapsed_time number
,outline_sid number
,outline_category varchar2(64)
,child_latch number
--注意下面5列构成主键
,constraint STATS$SQL_SUMMARY_PK primary key
(snap_id, dbid, instance_number, hash_value, text_subset)
using index tablespace &&tablespace_name
storage (initial 1m next 1m pctincrease 0)
,constraint STATS$SQL_SUMMARY_FK foreign key (snap_id, dbid, instance_number)
references STATS$SNAPSHOT on delete cascade
)tablespace &&tablespace_name
storage (initial 1m next 1m pctincrease 0) pctfree 5 pctused 40;
2)找到该表插入数据(spcpkg.sql)
insert into stats$sql_summary
( snap_id
, dbid
, instance_number
, text_subset
, sharable_mem
, sorts
, module
, loaded_versions
, fetches
, executions
, loads
, invalidations
, parse_calls
, disk_reads
, buffer_gets
, rows_processed
, command_type
, address
, hash_value
, version_count
, cpu_time
, elapsed_time
, outline_sid
, outline_category
, child_latch
)
select l_snap_id
, p_dbid
, p_instance_number
, substrb(sql_text,1,31)
, sharable_mem
, sorts
, module
, loaded_versions
, fetches
, executions
, loads
, invalidations
, parse_calls
, disk_reads
, buffer_gets
, rows_processed
, command_type
, address
, hash_value
, version_count
, cpu_time
, elapsed_time
, outline_sid
, outline_category
, child_latch
from stats$v$sqlxs
where is_obsolete = 'N'
and ( buffer_gets > l_buffer_gets_th
or disk_reads > l_disk_reads_th
or parse_calls > l_parse_calls_th
or executions > l_executions_th
or sharable_mem > l_sharable_mem_th
or version_count > l_version_count_th
);
3)找出stats$v$sqlxs对象(spcusr.sql)
create or replace view STATS$V_$SQLXS as
select max(sql_text) sql_text
, sum(sharable_mem) sharable_mem
, sum(sorts) sorts
, min(module) module
, sum(loaded_versions) loaded_versions
, sum(fetches) fetches
, sum(executions) executions
, sum(loads) loads
, sum(invalidations) invalidations
, sum(parse_calls) parse_calls
, sum(disk_reads) disk_reads
, sum(buffer_gets) buffer_gets
, sum(rows_processed) rows_processed
, max(command_type) command_type
, address address
, hash_value hash_value
, count(1) version_count
, sum(cpu_time) cpu_time
, sum(elapsed_time) elapsed_time
, max(outline_sid) outline_sid
, max(outline_category) outline_category
, max(is_obsolete) is_obsolete
, max(child_latch) child_latch
from v$sql
group by hash_value, address;
create or replace public synonym STATS$V$SQLXS for STATS$V_$SQLXS;
4)通过这里可以看出,要是的STATS$SQL_SUMMARY主键不重复,只要是的STATS$V_$SQLXS查询出来的记录唯一,所以解决方案就是在STATS$V_$SQLXS视图中增加下列条件,确保查询出来的记录唯一,从而不会发生主键冲突
where ( plan_hash_value > 0 or executions > 0 or parse_calls > 0 or disk_reads > 0 or buffer_gets > 0 )
该bug在10g中修复,对于不能及时升级的数据库,建议采用第五种方法解决问题,比较治标治本,对业务基本上无影响
linux中不能ping通hostname可能存在问题
不能ping通hostname
[oracle@xifenfei ~]$ hostname xifenfei [oracle@xifenfei ~]$ ping xifenfei ping: unknown host xifenfei [oracle@xifenfei ~]$ nslookup > xifenfei ;; connection timed out; no servers could be reached > exit [oracle@xifenfei ~]$ more /etc/hosts # Do not remove the following line, or various programs # that require network functionality will fail. 127.0.0.1 xifenfei1 localhost.localdomain localhost
10G中存在问题
[oracle@xifenfei ~]$ sqlplus / as sysdba SQL*Plus: Release 10.2.0.1.0 - Production on Tue Apr 3 01:54:22 2012 Copyright (c) 1982, 2005, Oracle. All rights reserved. Connected to an idle instance. --sqlplus连接非常慢 SQL> startup ORA-00600: internal error code, arguments: [keltnfy-ldmInit], [46], [1], [], [], [], [], [] --ORA-00600[keltnfy-ldmInit]错误
11G中存在问题
[oracle@xifenfei ~]$ sqlplus / as sysdba SQL*Plus: Release 11.2.0.3.0 Production on Tue Apr 3 02:02:29 2012 Copyright (c) 1982, 2011, Oracle. All rights reserved. Connected. SQL> startup ORA-00119: invalid specification for system parameter LOCAL_LISTENER ORA-00130: invalid listener address '(ADDRESS=(PROTOCOL=TCP)(HOST=xifenfei)(PORT=1521))' --因为不能ping通xifenfei,提示LOCAL_LISTENER错误 --修改pfile文件 local_listener='(ADDRESS=(PROTOCOL=TCP)(HOST=127.0.0.1)(PORT=1521))' --启动数据库 SQL> startup pfile='/tmp/pfile' ORACLE instance started. Total System Global Area 368263168 bytes Fixed Size 1345016 bytes Variable Size 306186760 bytes Database Buffers 54525952 bytes Redo Buffers 6205440 bytes Database mounted. Database opened. [oracle@xifenfei ~]$ ps -ef|grep ora_ oracle 5960 1 0 02:09 ? 00:00:00 ora_pmon_ora11g oracle 5964 1 0 02:09 ? 00:00:00 ora_psp0_ora11g oracle 5970 1 1 02:09 ? 00:00:00 ora_vktm_ora11g oracle 5976 1 0 02:09 ? 00:00:00 ora_gen0_ora11g oracle 5980 1 0 02:09 ? 00:00:00 ora_diag_ora11g oracle 5984 1 0 02:09 ? 00:00:00 ora_dbrm_ora11g oracle 5988 1 0 02:09 ? 00:00:00 ora_dia0_ora11g oracle 5992 1 0 02:09 ? 00:00:00 ora_mman_ora11g oracle 5996 1 0 02:09 ? 00:00:00 ora_dbw0_ora11g oracle 6000 1 0 02:09 ? 00:00:00 ora_lgwr_ora11g oracle 6004 1 0 02:09 ? 00:00:00 ora_ckpt_ora11g oracle 6008 1 0 02:09 ? 00:00:00 ora_smon_ora11g oracle 6012 1 0 02:09 ? 00:00:00 ora_reco_ora11g oracle 6016 1 0 02:09 ? 00:00:00 ora_mmon_ora11g oracle 6020 1 0 02:09 ? 00:00:00 ora_mmnl_ora11g oracle 6028 1 0 02:09 ? 00:00:00 ora_s000_ora11g oracle 6055 1 0 02:10 ? 00:00:00 ora_p000_ora11g oracle 6059 1 0 02:10 ? 00:00:00 ora_p001_ora11g oracle 6063 1 0 02:10 ? 00:00:00 ora_arc0_ora11g oracle 6069 1 0 02:10 ? 00:00:00 ora_arc1_ora11g oracle 6073 1 0 02:10 ? 00:00:00 ora_arc2_ora11g oracle 6077 1 0 02:10 ? 00:00:00 ora_arc3_ora11g oracle 6081 1 0 02:10 ? 00:00:00 ora_qmnc_ora11g oracle 6089 1 0 02:10 ? 00:00:00 ora_q000_ora11g oracle 6093 1 0 02:10 ? 00:00:00 ora_q001_ora11g oracle 6141 1 0 02:11 ? 00:00:00 ora_d000_ora11g oracle 6145 1 2 02:11 ? 00:00:00 ora_cjq0_ora11g --数据库启动正常 [oracle@xifenfei ~]$ sqlplus / as sysdba SQL*Plus: Release 11.2.0.3.0 Production on Tue Apr 3 02:10:37 2012 Copyright (c) 1982, 2011, Oracle. All rights reserved. Connected. SQL> show parameter local_listener; ORA-01012: not logged on Process ID: 0 Session ID: 0 Serial number: 0 --sqlplus不能操作,而且sqlplus登录非常慢
监听异常
--监听配置文件
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.10)(PORT = 1522))
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1522))
)
)
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = ora11g)
(ORACLE_HOME = /u01/oracle/oracle/product/11.2.0/db_1)
(SID_NAME = ora11g)
)
)
--启动监听
[oracle@xifenfei ~]$ lsnrctl start
LSNRCTL for Linux: Version 11.2.0.3.0 - Production on 03-APR-2012 02:19:52
Copyright (c) 1991, 2011, Oracle. All rights reserved.
Starting /u01/oracle/oracle/product/11.2.0/db_1/bin/tnslsnr: please wait...
TNSLSNR for Linux: Version 11.2.0.3.0 - Production
System parameter file is /u01/oracle/oracle/product/11.2.0/db_1/network/admin/listener.ora
Log messages written to /u01/oracle/diag/tnslsnr/xifenfei/listener/alert/log.xml
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.1.10)(PORT=1522)))
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1522)))
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.1.10)(PORT=1522)))
--一直处于等待状态
--客户端登录
C:\Users\XIFENFEI>sqlplus sys/xifenfei@192.168.1.10:1522/ora11g
SQL*Plus: Release 11.2.0.3.0 Production on 星期二 4月 3 12:48:15 2012
Copyright (c) 1982, 2011, Oracle. All rights reserved.
--一直hang住
[root@xifenfei admin]# netstat -an|grep 1522
tcp 0 0 192.168.1.10:1522 0.0.0.0:* LISTEN
tcp 260 0 192.168.1.10:1522 192.168.1.1:51977 ESTABLISHED
tcp 0 0 192.168.1.10:24317 192.168.1.10:1522 ESTABLISHED
tcp 198 0 192.168.1.10:1522 192.168.1.10:24317 ESTABLISHED
--这里显示已经连接
建议:在修改主机名时要慎重,修改的时候要确保/etc/hosts和/etc/sysconfig/network文件都被正确修改
永久表空间出现临时段不能扩展原因探讨
数据库版本
SQL> select * from v$version; BANNER ----------------------------------------------------------------------- Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production PL/SQL Release 11.2.0.3.0 - Production CORE 11.2.0.3.0 Production TNS for Linux: Version 11.2.0.3.0 - Production NLSRTL Version 11.2.0.3.0 - Production
创建5M测试表空间
SQL> CREATE TABLESPACE T_1652 DATAFILE '/tmp/t_1652_01.dbf' size 5M 2 AUTOEXTEND OFF LOGGING PERMANENT EXTENT MANAGEMENT LOCAL AUTOALLOCATE 3 SEGMENT SPACE MANAGEMENT AUTO blocksize 8192; Tablespace created.
测试CTAS
SQL> create table CHF.T_XIFENFEI TABLESPACE T_1652 as
2 select LPAD('XIFENFEI',1024,'F') "C_XFF" from dual connect by level <=3500;
create table CHF.T_XIFENFEI TABLESPACE T_1652 as
*
ERROR at line 1:
ORA-01652: unable to extend temp segment by 128 in tablespace T_1652
SQL> create table CHF.T_XIFENFEI TABLESPACE T_1652 as
2 select LPAD('XIFENFEI',1024,'F') "C_XFF" from dual connect by level <=3000;
Table created.
测试CREATE INDEX
SQL> create index chf.i_xifenfei on chf.t_xifenfei(c_xff)
2 tablespace t_1652;
create index chf.i_xifenfei on chf.t_xifenfei(c_xff)
*
ERROR at line 1:
ORA-01658: unable to create INITIAL extent for segment in tablespace T_1652
SQL> Select MAX(d.bytes) total_bytes,
2 nvl(SUM(f.Bytes), 0) free_bytes,
3 d.file_name,
4 MAX(d.bytes) - nvl(SUM(f.bytes), 0) used_bytes,
5 from DBA_FREE_SPACE f , DBA_DATA_FILES d
6 where f.tablespace_name(+) = d.tablespace_name
7 and f.file_id(+) = d.file_id
8 and d.tablespace_name = 'T_1652'
9 group by d.file_name;
TOTAL_BYTES FREE_BYTES FILE_NAME USED_BYTES
----------- ---------- ------------------------- ----------
5242880 0 /tmp/t_1652_01.dbf 5242880
SQL> drop table chf.t_xifenfei purge;
Table dropped.
SQL> create table CHF.T_XIFENFEI TABLESPACE T_1652 as
2 select LPAD('XIFENFEI',1024,'F') "C_XFF" from dual connect by level <=2000;
Table created.
SQL> Select MAX(d.bytes) total_bytes,
2 nvl(SUM(f.Bytes), 0) free_bytes,
3 d.file_name,
4 MAX(d.bytes) - nvl(SUM(f.bytes), 0) used_bytes,
5 from DBA_FREE_SPACE f , DBA_DATA_FILES d
6 where f.tablespace_name(+) = d.tablespace_name
7 and f.file_id(+) = d.file_id
8 and d.tablespace_name = 'T_1652'
9 group by d.file_name;
TOTAL_BYTES FREE_BYTES FILE_NAME USED_BYTES
----------- ---------- ------------------------- ----------
5242880 1048576 /tmp/t_1652_01.dbf 4194304
SQL> create index chf.i_xifenfei on chf.t_xifenfei(c_xff)
2 tablespace t_1652;
create index chf.i_xifenfei on chf.t_xifenfei(c_xff)
*
ERROR at line 1:
ORA-01652: unable to extend temp segment by 128 in tablespace T_1652
SQL> ALTER DATABASE DATAFILE '/tmp/t_1652_01.dbf' RESIZE 10M;
Database altered.
SQL> create index chf.i_xifenfei on chf.t_xifenfei(c_xff)
2 tablespace t_1652;
Index created.
测试MOVE
SQL> drop table chf.t_xifenfei purge;
Table dropped.
SQL> create table CHF.T_XIFENFEI TABLESPACE T_1652 as
2 select LPAD('XIFENFEI',1024,'F') "C_XFF" from dual connect by level <=3500;
Table created.
SQL> alter table chf.t_xifenfei move;
alter table chf.t_xifenfei move
*
ERROR at line 1:
ORA-01652: unable to extend temp segment by 128 in tablespace T_1652
SQL> Select MAX(d.bytes) total_bytes,
2 nvl(SUM(f.Bytes), 0) free_bytes,
3 d.file_name,
4 MAX(d.bytes) - nvl(SUM(f.bytes), 0) used_bytes,
5 from DBA_FREE_SPACE f , DBA_DATA_FILES d
6 where f.tablespace_name(+) = d.tablespace_name
7 and f.file_id(+) = d.file_id
8 and d.tablespace_name = 'T_1652'
9 group by d.file_name;
TOTAL_BYTES FREE_BYTES FILE_NAME USED_BYTES
----------- ---------- ------------------------- ----------
10485760 4194304 /tmp/t_1652_01.dbf 6291456
SQL> ALTER DATABASE DATAFILE '/tmp/t_1652_01.dbf' RESIZE 15M;
Database altered.
SQL> alter table chf.t_xifenfei move;
Table altered.
这里可以发现CTAS,CREATE INDEX,MOVE操作都有个共同点:需要一次性创建一个较大SEGMENT,但是这个SEGMENT的创建过程是在数据库中逐渐实现(非初始化指定大小)。
也就是说,ORACLE对这些对象的处理方法是:对于这样的segment先当作临时段处理,当处理完成后,再把这些在永久表空间中的临时段转换为永久段;所以当这些永久表空间中的临时段在扩展的时候,遇到该永久表空间不足,而该段目前还是临时段(在永久表空间中的临时段),就出现了ORA-01652提示一个永久表空间unable to extend temp segment

加我微信(17813235971)
加我QQ(107644445)

