ITPub博客

+++10gR2如果修改了主机名，没有更改/etc/hosts相应的信息，则数据库在关闭和启动的时候都会报错[oracle@oracle ~]$ sqlplus / as sysdbaSQL*Plus: Release 10.2.0.1.0 - Production on Sun Apr 6 19:19:30 2014Copyright (c) 1982, 2005, Oracle.  All rights reserved.Connected.SQL> SQL> SQL...

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

昨晚看到同学在群里发的一个sql，感觉不错，记录一下，sql由Jordan  提供：SQL> select replace(reverse(sys_connect_by_path(reverse(rownum || '*' || lv || '=' || rpad(rownum * lv, 2)),'/ ')),'/')  2  from (select level lv from dual connect by level < 10)  3  where lv...

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

用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 backup setchannel ORA_DISK_1: specifying datafile(s) in backup setinput datafile file number...

在用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.NEWL...

查询一个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 has no open cursors, the current SQL statement accesses more than the maximum allow...

登录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_lgwr_6443.trc:+++alterlogFri Jan 17 01:00:38 2014PMON (ospid: 6395): terminating...

登录测试数据库，发现普通用户登录就hang住没反应[oracle@rac2 ~]$ sqlplus scott/tigerSQL*Plus: Release 11.2.0.1.0 Production on Wed Jan 15 19:22:25 2014Copyright (c) 1982, 2009, Oracle.  All rights reserved.。。。。hang在这里没有反应用sys登录没有异常查看hang住的session 的eventSQL> select ...

我们知道，缺省情况下，controlfile 的block size为16kSQL> select * from v$version where rownum <=1;BANNER--------------------------------------------------------------------------------Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production[oracle@localhost goolen]$ dbfsize ...

今早偶然发现数据库监听多了一个奇怪的serviceRHEL 5.4 ，oracle 11gR2 rac+++如下： [oracle@rac1 ~]$ lsnrctl statusLSNRCTL for Linux: Version 11.2.0.1.0 - Production on 11-JAN-2014 17:24:56Copyright (c) 1991, 2009, Oracle.  All rights reserved.。。。。。。Service "+ASM" has 1 instance...

[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 ~]$ srvctl start database -d racPRCD-1027 : Failed to retrieve database racPRCR-1070 : Failed to chec...

今天在恢复一套测试库的时候遇到了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.arcORA-00280: change 1981277 for thread 1 is in sequence #45Specify log: {<RET>=sugges...

[oracle@goolen ~]$ sqlplus / as sysdbaSQL*Plus: Release 10.2.0.1.0 - Production on Tue Dec 31 20:05:37 2013Copyright (c) 1982, 2005, Oracle.  All rights reserved.Connected to:Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - ProductionWith the Partitioning, OLAP and Data Mining o...

前面一篇测试了“存在非空索引情况下的段头损坏的恢复” 今天测试一下，如果没有非空索引的情况下，段头坏了，该怎么去恢复数据建立测试环境：SQL> create table goolen as select * from dba_objects;Table created.+++goolen表的总行数为72181行，一会恢复的时候需要对比行数SQL> select count(*) from goo...

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

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

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

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

今天在测试库上创建表的时候，报了个错误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 bl...