_Goolen-CSDN博客

原创 mysqlimport: Error: 13, Can't get stat of '/root/xiaoming/import.data' (Errcode: 13), when using tab

mysqlimport导入数据的时候报错：[root@library xiaoming]# mysqlimport -uroot -pXXXXX -S /tmp/mysql_3301.sock --fields-terminated-by=" " --lines-terminated="0" --columns=a,b,c -d test /root/xiaoming/import.data

2014-04-19 15:48:14 1280 1

原创 ORA-30657: operation not supported on external organized table

要更新一个用户信息表的mail字段时，提示

2014-04-19 11:45:01 3169

原创 ORA-16433,ORA-01173恢复

同学打包上传了一个有问题的库，正好这几天没啥事，下载下来试着恢复了一下，恢复过程如下：不知道他之前做过什么操作，也不知道这个库是怎么损坏的，下载下来后大致看了一下打包文件信息如下：+++initorcl.ora文件内容：orcl.__oracle_base='D:\app\oracle'#ORACLE_BASE set from environment*.audit_fil

2014-04-17 17:07:53 3395

原创 ORA-01506: missing or illegal database name

一位同学的库启动报错：ORA-01506: missing or illegal database name最后发现是db_nameshe'zhi

2014-04-15 19:10:44 1657

原创 Error: couldn't connect to server 127.0.0.1:27017 at src/mongo/shell/mongo.js

mongodb所在的目录权限被更改导致mo'n'g意外

2014-04-15 19:06:32 1997

原创创建存储过程报ORA-00904

刚刚开发的同事来找我，在内网数据库上wei

2014-04-11 17:14:07 1191

原创 delete from file$ where file#=5

+++此次测试在归档模式下进行，通过delete file#来清除dbfile记录SQL> archive log list;Database log mode Archive ModeAutomatic archival EnabledArchive destination /opt/archOldest

2014-04-11 09:52:10 1190 3

原创通过dblink查询数据，commit和rollbak按钮会变亮

刚刚开发的同事问我，为什么他select一条语句，PL/SQL里的commit和rollbackan

2014-04-10 11:12:01 3546

转载 SCN与Oracle数据库恢复的关系

一、SCN类型1).System Checkpoint SCN当checkpoint完成后，ORACLE将System Checkpoint SCN号存放在控制文件中。我们可以通过下面SQL语句查询：select checkpoint_change# from v$database;2).Datafile Checkpoint SCN当一个检查点动作完成之后，Oracl

2014-04-09 19:50:28 951

原创 linux上修改主机名对oracle启动关闭的影响

2014-04-06 11:37:47 881

原创 show parameter数据来源

+++使用10046跟踪show parameter命令的数据来源SQL> oradebug setmypidStatement processed.SQL> oradebug event 10046 trace name context forever,level 12Statement processed.SQL> show parameter sgaNAME

2014-04-06 10:00:08 1270

原创经典sql

昨晚看到网友在群里发的一个sql，感觉

2014-04-06 09:41:19 548

原创 repair failure修复坏块

刚刚逛论坛，看到一个网友说他dd破坏的块用repair failure修复不了，我这里也实验一把试试测试之前先做一个备份RMAN> backup database;Starting backup at 12-MAR-14using target database control file instead of recovery catalogallocated channe

2014-03-12 14:38:12 1450

原创 corrupt block导致rman备份失败

用rman备份测试库的时候报错,信息如下：RMAN> backup database;Starting backup at 12-MAR-14allocated channel: ORA_DISK_1channel ORA_DISK_1: SID=5 device type=DISKchannel ORA_DISK_1: starting full datafile backu

2014-03-12 14:16:15 1260

原创 ORA-38104: Columns referenced in the ON Clause cannot be updated: "U"."USER_ID"

在用merge语句更新一个表的时候报错：SQL> merge into OUTSITE_USERINFO u 2 using tmp_update_newlib ut 3 on (u.user_id = ut.NEWCOURSE_ID) 4 when matched then 5 update set u.user_id = ut

2014-03-08 12:12:31 6627

原创 ORA-02020: too many database links in use

查询一个sql报错如下：ERROR at line 113:ORA-02020: too many database links in useCause:The maximum number of active connections to remote databases per user logon has been reached. Action:If the user

2014-02-28 14:17:49 1107

原创 rac1启动报错CRS-2674: Start of 'ora.rac.db' on 'rac1' failed

登录rac1，发现实例已经down了，重新启动也报错，如下+++alter：Fri Jan 17 01:00:34 2014ERROR: slave communication error with ASM; terminating process 6443Errors in file /opt/app/oracle/diag/rdbms/rac/rac1/trace/rac1_l

2014-01-17 11:16:34 9715

原创登录数据库hang住

2014-01-15 12:38:23 3668

原创修改controlfile block size

我们知道，缺省情况下，controlfile 的block size为16kSQL> select * from v$version where rownum BANNER--------------------------------------------------------------------------------Oracle Database 11g Enterp

2014-01-15 10:14:06 1114

原创 ORA-06512: at "DBSNMP.BSLN_INTERNAL"

收到报警邮件Sun Jan 12 00:00:04 2014Errors in file /tol/app/oracle/admin/coredb/diag/rdbms/dg_115/coredb/trace/coredb_j000_16208.trc:ORA-12012: error on auto execute of job 12696ORA-06502: PL/SQL: n

2014-01-14 09:52:29 1064

原创 Service "SYS$SYS.KUPC$C_1_20140110224935.RAC" has 1 instance(s)

2014-01-11 09:45:41 11465

原创 ORA-07445: exception encountered: core dump [qctosop()+1504]

+++今天下午收到一封报警邮件，alter日志里检测到有ORA-07445错误+++alter日志信息Fri Jan 10 16:21:41 2014Exception [type: SIGSEGV, Address not mapped to object] [ADDR:0x170] [PC:0x244A0CE, qctosop()+1504] [flags: 0x0, count:

2014-01-10 17:17:13 2603

原创 11g rac启动失败一例

[root@rac1 ~]# /opt/app/grid/product/11.2.0/bin/crsctl start crsCRS-4640: Oracle High Availability Services is already activeCRS-4000: Command Start failed, or completed with errors.[grid@rac1

2014-01-10 10:59:00 5420

原创 ORA-01446: cannot select ROWID from

一位同事跟我说查询一个sql报错ORA-01446SQL> select user_id, 2 dbms_rowid.rowid_relative_fno(rowid)rel_fno, 3 dbms_rowid.rowid_block_number(rowid)blockno, 4 dbms_rowid.rowid_row_number(rowid)rowno 5

2014-01-09 14:46:28 4194

原创 ORA-08103: object no longer exists

昨天恢复完测试库ORA-600(3020)错误后，数据库可以正常open但是使用过程中又出现了新的错误http://blog.csdn.net/goolenblog/article/details/17798171SQL> create table goolen as select * from dba_objects;Table created.SQL> insert

2014-01-04 11:26:57 6821

原创 ORA-600(3020)

今天在恢复一套测试库的时候遇到了ora-600(3020)SQL> recover datafile 6;ORA-00279: change 1981277 generated at 01/03/2014 14:24:15 needed for thread 1ORA-00289: suggestion : /opt/arch/archive_1_45_834576965.arcO

2014-01-03 19:50:56 1532

原创 recover deleted file on ext4

if the filesystem is ext4 ,the ext3grep tool can't recover the deleted files,but we can use other tool to recover that.for example:SQL> select name from v$datafile;NAME------------------

2013-12-31 16:25:29 1237

原创 recover deleted file on ext3

2013-12-31 16:17:04 922

原创没有非空索引情况下段头块损坏的恢复

前面一篇我们介绍了“存在非空索引情况下的段头损坏的恢复”http://blog.csdn.net/goolenblog/article/details/17586133今天我们来测试一下，如果没有非空索引的情况下，段头坏了，该怎么去恢复数据建立测试环境：SQL> create table goolen as select * from dba_objects;Table c

2013-12-27 20:34:27 865

原创 ORA-600(4000)

oracle 11gR2 启动报错：Database mounted.ORA-01092: ORACLE instance terminated. Disconnection forcedORA-00704: bootstrap process failureORA-00600: internal error code, arguments: [4000], [1], [], []

2013-12-27 15:50:06 1426

原创 Oracle 11g DataGuard (Automatic Block Media Repair)

在oracle 11g DataGuard 中引入Automatic Block Media Repair，自动的块恢复如果在主库发现有损坏的数据块，该特性将通过后台进程ABMR自动将物理备库(physical standby)上的好的数据块传输到主库(primary database)上恢复同样的若物理备库上发现数据块损坏那么也可以利用到以上特性来修复此特性默认是开启的S

2013-12-27 11:40:50 1063

原创增强的blockrecover

blockrecover命令是从oracle 9i引入的，可以针对单个数据块的恢复命令如下：RMAN> blockrecover datafile 6 block 14;而在oracle11g推出的recover corruption list命令，更是简化了数据坏块的修复复杂度只要是在v$database_block_corruption视图中记录的坏块，都会试图修复

2013-12-27 10:55:21 804

原创存在多个tnsnames文件也会引起连接错误

今天处理了一个tnsnames文件配置的问题，让我又想起一个跟tnsnames文件相关的知识点tnsnames文件是可以存在多个路径下面，oracle会根据顺序选择下面我们模拟一下$HOME/.tnsnames.ora/etc/tnsnames.ora$ORACLE_HOME/network/admin/tnsnames.ora+++我们默认都会去配置这个文件这

2013-12-26 22:17:13 796

原创 tnsnames配置缺少空格导致的ORA-12154

今天一位网友找我说用sqlplus远程连接总是报错，连接不上，最终检查发现是他本机的tnsnames文件配置格式不对导致的以下是我模拟的环境，如下：[oracle@localhost admin]$ sqlplus scott/tiger@goolenSQL*Plus: Release 11.2.0.1.0 Production on Thu Dec 26 20:23:45 201

2013-12-26 20:41:07 826

原创存在非空索引情况下的段头损坏的恢复

创建一个测试表：SQL> create table goolen as select * from dba_objects;Table created.SQL> alter table goolen add constraint pk_objd primary key (object_id);Table altered.SQL> select count(*) from

2013-12-26 16:39:06 1190

原创 ORA-01578: ORACLE data block corrupted

今天在测试库上创建表的时候，报了个错误SQL> create table abc as select * from dba_objects;create table abc as select * from dba_objects *ERROR at line 1:ORA-01578: ORACLE data b

2013-12-26 11:37:29 2960

原创单实例oracle从文件系统迁移到ASM

今天需要在asm环境下测试一个工具，手头缺少asm环境的测试库，故把一个文件系统上的单实例迁移到asm上来做测试，顺便记录一下安装配置asm实例的步骤这里不再重复1,查看asm实例状态[oracle@goolen ~]$ export ORACLE_SID=+ASM [oracle@goolen ~]$ sqlplus / as sysdba

2013-12-14 11:37:04 1142

原创 ORA-00600(kkdoilsn1)

今天DG备库报600错误Tue Dec 10 17:04:49 2013Errors in file /tol/app/oracle/admin/coredb/diag/rdbms/dg_111/coredb/trace/coredb_ora_29573.trc (incident=68329):ORA-00600: 内部错误代码, 参数: [kkdoilsn1], [], [],

2013-12-10 17:45:01 995 1

原创 ORA-00600(kdsgrp1)

今天早上受到一封alter日志的报警邮件alter日志如下:Errors in file /tol/app/oracle/admin/coredb/diag/rdbms/dg_111/coredb/trace/coredb_ora_9686.trc (incident=67937):ORA-00600: 内部错误代码, 参数: [kdsgrp1], [], [], [], [], [

2013-12-04 14:32:09 1605 1

原创 bbed修改varchar数据

bbed更改data block里的varchar型数据SQL> show userUSER is "SCOTT"SQL> create table goolen (id number,name varchar2(50));Table created.SQL> insert into goolen values(1,'你是猪');1 row created.

2013-11-29 10:49:55 758

空空如也

空空如也