ORACLE Instance XFF (pid = 18) – Error 600 encountered while recovering transaction

分享一次由于一个表异常导致数据库报类似:ORACLE Instance XFF (pid = 18) – Error 600 encountered while recovering transaction故障的案例
一个10.2.0.4的数据库,正常运行的库突然出现如下错误

Sun Apr 07 11:07:12 2019
Thread 1 advanced to log sequence 602883 (LGWR switch)
  Current log# 3 seq# 602883 mem# 0: L:\ORADATA\XFF\REDO03.LOG
Sun Apr 07 11:10:38 2019
Thread 1 advanced to log sequence 602884 (LGWR switch)
  Current log# 1 seq# 602884 mem# 0: L:\ORADATA\XFF\REDO01.LOG
Sun Apr 07 11:11:56 2019
Errors in file c:\oracle\product\10.2.0\admin\XFF\udump\XFF_ora_22956.trc:
ORA-00600: 内部错误代码, 参数: [ktspgfb-1], [], [], [], [], [], [], []

Sun Apr 07 11:12:46 2019
Errors in file c:\oracle\product\10.2.0\admin\XFF\udump\XFF_ora_27408.trc:
ORA-00600: 内部错误代码, 参数: [kcbnew_3], [0], [1], [168354056], [], [], [], []

Sun Apr 07 11:13:57 2019
Errors in file c:\oracle\product\10.2.0\admin\XFF\udump\XFF_ora_6632.trc:
ORA-00600: 内部错误代码, 参数: [ktspgfb-1], [], [], [], [], [], [], []

过一段时间报,然后实例直接crash

Tue Apr 09 07:47:35 2019
ORACLE Instance XFF (pid = 18) - Error 600 encountered while recovering transaction (1, 1) on object 113718002.
Tue Apr 09 07:47:35 2019
Errors in file c:\oracle\product\10.2.0\admin\XFF\bdump\XFF_smon_12948.trc:
ORA-00600: internal error code, arguments: [kcbgcur_3], [168454497], [8], [4], [0], [], [], []

Tue Apr 09 07:55:23 2019
Errors in file c:\oracle\product\10.2.0\admin\XFF\bdump\XFF_pmon_22652.trc:
ORA-00474: SMON process terminated with error

Tue Apr 09 07:55:24 2019
PMON: terminating instance due to error 474
Tue Apr 09 07:55:24 2019
Errors in file c:\oracle\product\10.2.0\admin\XFF\bdump\XFF_lgwr_28608.trc:
ORA-00474: SMON process terminated with error

Tue Apr 09 07:55:34 2019
Errors in file c:\oracle\product\10.2.0\admin\XFF\bdump\XFF_psp0_12544.trc:
ORA-00474: SMON process terminated with error

Tue Apr 09 07:55:34 2019
Errors in file c:\oracle\product\10.2.0\admin\XFF\bdump\XFF_j000_5216.trc:
ORA-00474: SMON process terminated with error

Tue Apr 09 07:55:35 2019
Errors in file c:\oracle\product\10.2.0\admin\XFF\bdump\XFF_ckpt_28204.trc:
ORA-00474: SMON process terminated with error

Tue Apr 09 07:55:36 2019
Errors in file c:\oracle\product\10.2.0\admin\XFF\bdump\XFF_mman_9320.trc:
ORA-00474: SMON process terminated with error

Tue Apr 09 07:55:44 2019
Errors in file c:\oracle\product\10.2.0\admin\XFF\bdump\XFF_q002_24384.trc:
ORA-00474: SMON process terminated with error

Tue Apr 09 07:55:53 2019
Errors in file c:\oracle\product\10.2.0\admin\XFF\bdump\XFF_reco_24124.trc:
ORA-00474: SMON process terminated with error

根据以上报错,数据库crash的原因是由于undo异常导致,通过对undo进行重建,解决掉异常undo,但是业务运行之后,一样的问题又重现,最后通过分析确认是对象异常导致

SQL> create table XFF.T_XIFENFEI_xff as select * from XFF.T_XIFENFEI;
create table XFF.T_XIFENFEI_xff as select * from XFF.T_XIFENFEI
                                                           *
ERROR at line 1:
ORA-00600: internal error code, arguments: [kcbz_check_objd_typ], [0], [0], [1], [], [], [], []

屏蔽相关block obj的check之后
SQL> create table XFF.T_XIFENFEI_xff as select * from XFF.T_XIFENFEI;
create table XFF.T_XIFENFEI_xff as select * from XFF.T_XIFENFEI
                                                           *
ERROR at line 1:
ORA-00600: internal error code, arguments: [ktspScanInit-l1], [], [], [], [],[], [], []

比较明显该表对象出现逻辑异常,通过基于rowid的方式对该表数据进行抽取

SQL> create table XFF.T_XIFENFEI_new
  2  as
  3  select * from XFF.T_XIFENFEI where 1=0;

Table created.

SQL> set serveroutput on
SQL> set concat off
SQL> DECLARE
  2   nrows number;
  3   rid rowid;
  4   dobj number;
  5   ROWSPERBLOCK number;
  6  BEGIN
  7   ROWSPERBLOCK:=1000;
  8   nrows:=0;
  9   select data_object_id  into dobj
 10   from dba_objects
 11   where owner = 'XFF'
 12   and object_name = 'T_XIFENFEI'
 13   ;
 14   for i in (select relative_fno, block_id, block_id+blocks-1 totblocks
 15             from dba_extents
 16             where owner = 'XFF'
 17               and segment_name = 'T_XIFENFEI'
 18            order by extent_id)
 19   loop
 20     for br in i.block_id..i.totblocks loop
 21      for j in 1..ROWSPERBLOCK loop
 22      begin
 23        rid := dbms_rowid.ROWID_CREATE(1,dobj,i.relative_fno, br , j-1);
 24        insert into XFF.T_XIFENFEI_NEW
 25        select /*+ ROWID(A) */ *
 26        from XFF.T_XIFENFEI A
 27        where rowid = rid;
 28        if sql%rowcount = 1 then nrows:=nrows+1; end if;
 29        if (mod(nrows,10000)=0) then commit; end if;
 30      exception when others then null;
 31      end;
 32      end loop;
 33    end loop;
 34   end loop;
 35   COMMIT;
 36   dbms_output.put_line('Total rows: '||to_char(nrows));
 37  END;
 38  /
Total rows: 227000

PL/SQL procedure successfully completed.

再次观察数据库恢复正常,也不再crash和报错,恢复完成

发表在 Oracle备份恢复 | 标签为 , , , , , | 留下评论

linux资源限制导致数据库异常

一起由于liunx系统资源限制导致数据库无法启动案例分享
数据库启动报ORA-01157错

SQL> startup
ORACLE instance started.

Total System Global Area 3340451840 bytes
Fixed Size		    2217952 bytes
Variable Size		 1862273056 bytes
Database Buffers	 1459617792 bytes
Redo Buffers		   16343040 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 5 - see DBWR trace file
ORA-01110: data file 5: '/home/oracle/oradata/XIFENFEI.dbf'

该错误一般是由于文件丢失或者路径错误导致

alert日志显示

Sun Apr 07 20:57:03 2019
ALTER DATABASE OPEN
Sun Apr 07 20:57:03 2019
Errors in file /dbdata/oracle/diag/rdbms/orcl/orcl/trace/orcl_dbw0_2681.trc:
ORA-01157: cannot identify/lock data file 5 - see DBWR trace file
ORA-01110: data file 5: '/home/oracle/oradata/XIFENFEI.dbf'
ORA-27092: size of file exceeds file size limit of the process
Additional information: 262144
Additional information: 262145
Errors in file /dbdata/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_2802.trc:
ORA-01157: cannot identify/lock data file 5 - see DBWR trace file
ORA-01110: data file 5: '/home/oracle/oradata/XIFENFEI.dbf'
ORA-1157 signalled during: ALTER DATABASE OPEN...
Sun Apr 07 20:57:04 2019
Errors in file /dbdata/oracle/diag/rdbms/orcl/orcl/trace/orcl_m000_2804.trc  (incident=38578):
ORA-00600: internal error code, arguments: [kcidr_io_check_common_6], [10], 
     [/home/oracle/oradata/XIFENFEI.dbf], [8192], [2], [5], [], [], [], [], [], []
ORA-27092: size of file exceeds file size limit of the process

这里看到提示ORA-27092: size of file exceeds file size limit of the process
查看系统limit配置

[oracle@XFF ~]$ ulimit -a
core file size          (blocks, -c) unlimited
data seg size           (kbytes, -d) 640000
scheduling priority             (-e) 0
file size               (blocks, -f) 2097152
pending signals                 (-i) 128489
max locked memory       (kbytes, -l) unlimited
max memory size         (kbytes, -m) unlimited
open files                      (-n) 131072
pipe size            (512 bytes, -p) 8
POSIX message queues     (bytes, -q) 819200
real-time priority              (-r) 0
stack size              (kbytes, -s) 10240
cpu time               (seconds, -t) unlimited
max user processes              (-u) 131072
virtual memory          (kbytes, -v) unlimited
file locks                      (-x) unlimited

一般操作系统block size为1k,这里限制文件大小为2097152=(2G)
查看文件

[oracle@XFF ~]$ ls -l /home/oracle/oradata/XIFENFEI.dbf
-rw-r-----. 1 oracle oinstall 2147491840 Apr  7 19:04 /home/oracle/oradata/XIFENFEI.dbf

文件大小为2097160>2097152,导致异常

设置系统对文件大小限制2097152kb

[root@XFF ~]# ulimit -f 102400000
[root@XFF ~]# su - oracle
[oracle@XFF ~]$ ulimit -a
core file size          (blocks, -c) unlimited
data seg size           (kbytes, -d) 640000
scheduling priority             (-e) 0
file size               (blocks, -f) 102400000
pending signals                 (-i) 128489
max locked memory       (kbytes, -l) unlimited
max memory size         (kbytes, -m) unlimited
open files                      (-n) 131072
pipe size            (512 bytes, -p) 8
POSIX message queues     (bytes, -q) 819200
real-time priority              (-r) 0
stack size              (kbytes, -s) 10240
cpu time               (seconds, -t) unlimited
max user processes              (-u) 131072
virtual memory          (kbytes, -v) unlimited
file locks                      (-x) unlimited

重启数据库,open成功

SQL> shutdown immediate;      
ORA-01109: database not open


Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area 3340451840 bytes
Fixed Size		    2217952 bytes
Variable Size		 1862273056 bytes
Database Buffers	 1459617792 bytes
Redo Buffers		   16343040 bytes
Database mounted.
Database opened.
发表在 Oracle备份恢复 | 标签为 , , | 留下评论

sql plan baseline简单介绍

Oracle 11g开始,提供了一种新的固定执行计划的方法,即SQL plan baseline,中文名SQL执行计划基线(简称基线),可以认为是OUTLINE(大纲)或者SQL PROFILE的改进版本,基本上它的主要作用可以归纳为如下两个:
1、稳定给定SQL语句的执行计划,防止执行环境或对象统计信息等因子的改变对SQL语句的执行计划产生影响
2、减少数据库中出现SQL语句性能退化的概率,理论上不允许一条语句切换到一个比已经执行过的执行计划慢很多的新的执行计划上(可以通过OPTIMIZER_USE_SQL_PLAN_BASELINE实现)
3、sql baseline对于sql 大小写,sql空格可以生效,但是对于非绑定变量sql,如果使用不同变量无法生效(无force_matching功能)
确认当前无sql baseline启用

SQL> select sql_handle,plan_name,dbms_lob.substr(sql_text,60,1) sql_text,ACCEPTED from dba_sql_plan_baselines;

no rows selected

创建场景
模拟一个表有index,如果再不强制的情况下,查询直接使用index,但是我这边要通过sql baseline模拟使用走全表扫描,实现不修改sql的情况下直接修改执行计划

SQL> create table t_xifenfei tablespace users as select * from dba_objects;

Table created.

SQL> create index i_xifenfei on t_xifenfei(object_id) tablespace users;

Index created.

SQL> execute dbms_stats.gather_table_stats('SYS','T_XIFENFEI',CASCADE=>TRUE);

PL/SQL procedure successfully completed.

SQL> select OBJECT_NAME from T_XIFENFEI where OBJECT_ID=100;

OBJECT_NAME
--------------------------------------------------------------------------------------------------------------
ORA$BASE

SQL> SELECT * FROM table(dbms_xplan.display_cursor);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------
SQL_ID  b9hj14ntjgmtr, child number 0
-------------------------------------
select OBJECT_NAME from T_XIFENFEI where OBJECT_ID=100

Plan hash value: 1926396081

------------------------------------------------------------------------------------------
| Id  | Operation                   | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |            |       |       |     2 (100)|          |
|   1 |  TABLE ACCESS BY INDEX ROWID| T_XIFENFEI |     1 |    30 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | I_XIFENFEI |     1 |       |     1   (0)| 00:00:01 |
------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("OBJECT_ID"=100)


19 rows selected.


SQL> select /*+FULL(T_XIFENFEI)*/OBJECT_NAME from T_XIFENFEI where OBJECT_ID=100;

OBJECT_NAME
--------------------------------------------------------------------------------------------------------------
ORA$BASE

SQL> SELECT * FROM table(dbms_xplan.display_cursor);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------
SQL_ID  aqgv7stwu6w5t, child number 0
-------------------------------------
select /*+FULL(T_XIFENFEI)*/OBJECT_NAME from T_XIFENFEI where
OBJECT_ID=100

Plan hash value: 548923532

--------------------------------------------------------------------------------
| Id  | Operation         | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |            |       |       |   349 (100)|          |
|*  1 |  TABLE ACCESS FULL| T_XIFENFEI |     1 |    30 |   349   (1)| 00:00:05 |
--------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("OBJECT_ID"=100)


19 rows selected.

从shared pool->library cache中直接加载sql plan baseline

SQL> set serveroutput on
SQL>  DECLARE
  2      ret PLS_INTEGER;
  3    BEGIN
  4      ret := dbms_spm.load_plans_from_cursor_cache(sql_id          => 'b9hj14ntjgmtr',
  5                                                   plan_hash_value => null);
  6      dbms_output.put_line(ret || ' SQL plan baseline(s) created');
  7    END;
  8    /
1 SQL plan baseline(s) created

PL/SQL procedure successfully completed.

SQL> select sql_handle,plan_name,dbms_lob.substr(sql_text,60,1) sql_text,ACCEPTED from dba_sql_plan_baselines;

SQL_HANDLE                     PLAN_NAME
------------------------------ ------------------------------
SQL_TEXT
--------------------------------------------------------------------------------------------------------------
ACC
---
SQL_ed6b78bdb7b643ad           SQL_PLAN_fuuvsrqvvchxd04acd9ab
select OBJECT_NAME from T_XIFENFEI where OBJECT_ID=100
YES

SQL> select * from table(dbms_xplan.display_cursor('b9hj14ntjgmtr','',''));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------
SQL_ID  b9hj14ntjgmtr, child number 1
-------------------------------------
select OBJECT_NAME from T_XIFENFEI where OBJECT_ID=100

Plan hash value: 1926396081

------------------------------------------------------------------------------------------
| Id  | Operation                   | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |            |       |       |     2 (100)|          |
|   1 |  TABLE ACCESS BY INDEX ROWID| T_XIFENFEI |     1 |    30 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | I_XIFENFEI |     1 |       |     1   (0)| 00:00:01 |
------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("OBJECT_ID"=100)

Note
-----
   - SQL plan baseline SQL_PLAN_fuuvsrqvvchxd04acd9ab used for this statement


23 rows selected.

利用第一个baseline的sql_handle创建新执行计划的baseline

SQL> set serveroutput on
SQL> DECLARE
  2    ret pls_integer;
  3   begin
  4   ret := DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE (
  5   sql_id=>'aqgv7stwu6w5t',
  6   plan_hash_value=>548923532,sql_handle=>'SQL_ed6b78bdb7b643ad'
  7   );
  8  dbms_output.put_line(ret || ' SQL plan baseline(s) created');
  9   end;
 10   /
1 SQL plan baseline(s) created

PL/SQL procedure successfully completed.

SQL>  select sql_handle,plan_name,dbms_lob.substr(sql_text,60,1) sql_text,ACCEPTED from dba_sql_plan_baselines;

SQL_HANDLE                     PLAN_NAME
------------------------------ ------------------------------
SQL_TEXT
--------------------------------------------------------------------------------------------------------------
ACC
---
SQL_ed6b78bdb7b643ad           SQL_PLAN_fuuvsrqvvchxd04acd9ab
select OBJECT_NAME from T_XIFENFEI where OBJECT_ID=100
YES

SQL_ed6b78bdb7b643ad           SQL_PLAN_fuuvsrqvvchxdf0c521d1
select OBJECT_NAME from T_XIFENFEI where OBJECT_ID=100
YES

删除第一个baseline

SQL> set serveroutput on
SQL> DECLARE
  2    ret pls_integer;
  3   begin
  4   ret := DBMS_SPM.drop_sql_plan_baseline (  sql_handle=>'SQL_ed6b78bdb7b643ad'
     ,plan_name=>'SQL_PLAN_fuuvsrqvvchxd04acd9ab');
  5  dbms_output.put_line(ret || ' SQL plan baseline(s) created');
  6   end;
  7   /
1 SQL plan baseline(s) created

PL/SQL procedure successfully completed.


SQL> select sql_handle,plan_name,dbms_lob.substr(sql_text,60,1) sql_text,ACCEPTED from dba_sql_plan_baselines;

SQL_HANDLE                     PLAN_NAME
------------------------------ ------------------------------
SQL_TEXT
--------------------------------------------------------------------------------------------------------------
ACC
---
SQL_ed6b78bdb7b643ad           SQL_PLAN_fuuvsrqvvchxdf0c521d1
select OBJECT_NAME from T_XIFENFEI where OBJECT_ID=100
YES

验证baseline生效,实现sql语句执行计划的改变

SQL> alter system flush shared_pool;

System altered.

SQL> select OBJECT_NAME from T_XIFENFEI where OBJECT_ID=100;

OBJECT_NAME
--------------------------------------------------------------------------------------------------------------
ORA$BASE

SQL>  select * from table(dbms_xplan.display_cursor('b9hj14ntjgmtr','',''));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------
SQL_ID  b9hj14ntjgmtr, child number 1
-------------------------------------
select OBJECT_NAME from T_XIFENFEI where OBJECT_ID=100

Plan hash value: 548923532

--------------------------------------------------------------------------------
| Id  | Operation         | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |            |       |       |   349 (100)|          |
|*  1 |  TABLE ACCESS FULL| T_XIFENFEI |     1 |    30 |   349   (1)| 00:00:05 |
--------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("OBJECT_ID"=100)

Note
-----
   - SQL plan baseline SQL_PLAN_fuuvsrqvvchxdf0c521d1 used for this statement


22 rows selected.

利用coe脚本利用baseline快速绑定sql执行计划

SQL> set serveroutput on
SQL> DECLARE
  2    ret pls_integer;
  3   begin
  4   ret := DBMS_SPM.drop_sql_plan_baseline (  sql_handle=>'SQL_ed6b78bdb7b643ad',
      plan_name=>'SQL_PLAN_fuuvsrqvvchxdf0c521d1');
  5  dbms_output.put_line(ret || ' SQL plan baseline(s) created');
  6   end;
  7   /
1 SQL plan baseline(s) created

PL/SQL procedure successfully completed.

SQL> select sql_handle,plan_name,dbms_lob.substr(sql_text,60,1) sql_text,ACCEPTED from dba_sql_plan_baselines;

no rows selected


SQL> alter system flush shared_pool;

System altered.

SQL> select OBJECT_NAME from T_XIFENFEI where OBJECT_ID=100;

OBJECT_NAME
--------------------------------------------------------------------------------------------------------------
ORA$BASE

SQL> select /*+FULL(T_XIFENFEI)*/OBJECT_NAME from T_XIFENFEI where OBJECT_ID=100;

OBJECT_NAME
--------------------------------------------------------------------------------------------------------------
ORA$BASE

SQL> select sql_id,sql_text from v$sql where sql_text like '%from T_XIFENFEI where OBJECT_ID=100%';

SQL_ID
-------------
SQL_TEXT
--------------------------------------------------------------------------------------------------------------
aqgv7stwu6w5t
select /*+FULL(T_XIFENFEI)*/OBJECT_NAME from T_XIFENFEI where OBJECT_ID=100

7jdqvvnpxb9z5
select sql_id,sql_text from v$sql where sql_text like '%from T_XIFENFEI where OBJECT_ID=100%'

b9hj14ntjgmtr
select OBJECT_NAME from T_XIFENFEI where OBJECT_ID=100


SQL> select sql_id,PLAN_HASH_VALUE from v$sql where sql_id in('b9hj14ntjgmtr','aqgv7stwu6w5t');

SQL_ID        PLAN_HASH_VALUE
------------- ---------------
aqgv7stwu6w5t       548923532
b9hj14ntjgmtr      1926396081

SQL>  select * from table(dbms_xplan.display_cursor('aqgv7stwu6w5t','',''));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------
SQL_ID  aqgv7stwu6w5t, child number 0
-------------------------------------
select /*+FULL(T_XIFENFEI)*/OBJECT_NAME from T_XIFENFEI where
OBJECT_ID=100

Plan hash value: 548923532

--------------------------------------------------------------------------------
| Id  | Operation         | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |            |       |       |   349 (100)|          |
|*  1 |  TABLE ACCESS FULL| T_XIFENFEI |     1 |    30 |   349   (1)| 00:00:05 |
--------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("OBJECT_ID"=100)


19 rows selected.

SQL>  select * from table(dbms_xplan.display_cursor('b9hj14ntjgmtr','',''));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------
SQL_ID  b9hj14ntjgmtr, child number 0
-------------------------------------
select OBJECT_NAME from T_XIFENFEI where OBJECT_ID=100

Plan hash value: 1926396081

------------------------------------------------------------------------------------------
| Id  | Operation                   | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |            |       |       |     2 (100)|          |
|   1 |  TABLE ACCESS BY INDEX ROWID| T_XIFENFEI |     1 |    30 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | I_XIFENFEI |     1 |       |     1   (0)| 00:00:01 |
------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("OBJECT_ID"=100)


19 rows selected.




SQL> @coe_load_sql_baseline.sql

Parameter 1:
ORIGINAL_SQL_ID (required)

Enter value for 1: b9hj14ntjgmtr

Parameter 2:
MODIFIED_SQL_ID (required)

Enter value for 2: aqgv7stwu6w5t


     PLAN_HASH_VALUE          AVG_ET_SECS
-------------------- --------------------
           548923532                 .003

Parameter 3:
PLAN_HASH_VALUE (required)

Enter value for 3: 548923532



SQL> select sql_handle,plan_name,dbms_lob.substr(sql_text,60,1) sql_text,ACCEPTED from dba_sql_plan_baselines;

SQL_HANDLE                     PLAN_NAME
------------------------------ ------------------------------
SQL_TEXT
--------------------------------------------------------------------------------
ACC
---
SQL_ed6b78bdb7b643ad           SQL_PLAN_fuuvsrqvvchxdf0c521d1
select OBJECT_NAME from T_XIFENFEI where OBJECT_ID=100
YES


SQL> alter system flush shared_pool ;

System altered.

SQL> select OBJECT_NAME from T_XIFENFEI where OBJECT_ID=100;

OBJECT_NAME
--------------------------------------------------------------------------------
ORA$BASE

SQL>  select * from table(dbms_xplan.display_cursor('b9hj14ntjgmtr','',''));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID  b9hj14ntjgmtr, child number 1
-------------------------------------
select OBJECT_NAME from T_XIFENFEI where OBJECT_ID=100

Plan hash value: 548923532

--------------------------------------------------------------------------------
| Id  | Operation         | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |            |       |       |   349 (100)|          |
|*  1 |  TABLE ACCESS FULL| T_XIFENFEI |     1 |    30 |   349   (1)| 00:00:05 |

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("OBJECT_ID"=100)

Note
-----
   - SQL plan baseline SQL_PLAN_fuuvsrqvvchxdf0c521d1 used for this statement


22 rows selected.

sql空格和大小写改变不影响baseline效果

SQL>  select * from table(dbms_xplan.display_cursor('dwfxd7x6kwx6u','',''));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID  dwfxd7x6kwx6u, child number 1
-------------------------------------
select     OBJECT_NAME from T_XIFENFEI where OBJECT_ID=100

Plan hash value: 548923532

--------------------------------------------------------------------------------
| Id  | Operation         | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |            |       |       |   349 (100)|          |
|*  1 |  TABLE ACCESS FULL| T_XIFENFEI |     1 |    30 |   349   (1)| 00:00:05 |

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("OBJECT_ID"=100)

Note
-----
   - SQL plan baseline SQL_PLAN_fuuvsrqvvchxdf0c521d1 used for this statement


22 rows selected.

SQL> select     OBJECT_NAME from T_xifenfei where OBJECT_ID=100;

OBJECT_NAME
--------------------------------------------------------------------------------
ORA$BASE

SQL> select sql_id,sql_text from v$sql where sql_text like '%from T_xifenfei where OBJECT_ID=100%';

SQL_ID
-------------
SQL_TEXT
--------------------------------------------------------------------------------
5spn2x6ac44af
select sql_id,sql_text from v$sql where sql_text like '%from T_xifenfei where OB
JECT_ID=100%'

8tytmh8r6w80n
select     OBJECT_NAME from T_xifenfei where OBJECT_ID=100


SQL>  select * from table(dbms_xplan.display_cursor('8tytmh8r6w80n','',''));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID  8tytmh8r6w80n, child number 1
-------------------------------------
select     OBJECT_NAME from T_xifenfei where OBJECT_ID=100

Plan hash value: 548923532

--------------------------------------------------------------------------------
| Id  | Operation         | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |            |       |       |   349 (100)|          |
|*  1 |  TABLE ACCESS FULL| T_XIFENFEI |     1 |    30 |   349   (1)| 00:00:05 |

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("OBJECT_ID"=100)

Note
-----
   - SQL plan baseline SQL_PLAN_fuuvsrqvvchxdf0c521d1 used for this statement


22 rows selected.

但是sql变量不一样导致baseline失效

SQL>  select * from table(dbms_xplan.display_cursor('fp9u8wkp5cuw1','',''));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID  fp9u8wkp5cuw1, child number 0
-------------------------------------
select OBJECT_NAME from T_XIFENFEI where OBJECT_ID=101

Plan hash value: 1926396081

------------------------------------------------------------------------------------------

| Id  | Operation                   | Name       | Rows  | Bytes | Cost (%CPU)|
Time     |

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
------------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT            |            |       |       |     2 (100)|         |
|   1 |  TABLE ACCESS BY INDEX ROWID| T_XIFENFEI |     1 |    30 |     2   (0)|00:00:01 |
|*  2 |   INDEX RANGE SCAN          | I_XIFENFEI |     1 |       |     1   (0)|
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
00:00:01 |

------------------------------------------------------------------------------------------


Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("OBJECT_ID"=101)


19 rows selected.
发表在 Oracle性能优化 | 留下评论

非归档数据库异常恢复一例

由于存储故障,数据库为非归档模式,通过Oracle数据库异常恢复检查脚本(Oracle Database Recovery Check)收集信息确认数据库redo异常
2


Thu Mar 28 11:36:13 2019
ALTER DATABASE RECOVER    CONTINUE DEFAULT  
Media Recovery Log /u01/app/oracle/flash_recovery_area/ORCL/archivelog/2019_03_28/o1_mf_1_5397869_%u_.arc
Thu Mar 28 11:36:13 2019
Errors with log /u01/app/oracle/flash_recovery_area/ORCL/archivelog/2019_03_28/o1_mf_1_5397869_%u_.arc
Errors in file /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_pr00_17611.trc:
ORA-00308:cannot open archived log
    '/u01/app/oracle/flash_recovery_area/ORCL/archivelog/2019_03_28/o1_mf_1_5397869_%u_.arc'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
ORA-308 signalled during: ALTER DATABASE RECOVER    CONTINUE DEFAULT  ...
ALTER DATABASE RECOVER    CONTINUE DEFAULT  
Media Recovery Log /u01/app/oracle/flash_recovery_area/ORCL/archivelog/2019_03_28/o1_mf_1_5397869_%u_.arc
Errors with log /u01/app/oracle/flash_recovery_area/ORCL/archivelog/2019_03_28/o1_mf_1_5397869_%u_.arc
Errors in file /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_pr00_17611.trc:
ORA-00308:cannot open archived log
   '/u01/app/oracle/flash_recovery_area/ORCL/archivelog/2019_03_28/o1_mf_1_5397869_%u_.arc'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
ORA-308 signalled during: ALTER DATABASE RECOVER    CONTINUE DEFAULT  ...

Thu Mar 28 11:38:44 2019
ALTER DATABASE RECOVER  datafile 5,6  
Media Recovery Start
Serial Media Recovery started
Recovery of Online Redo Log: Thread 1 Group 3 Seq 5397870 Reading mem 0
  Mem# 0: /u01/app/oracle/oradata/orcl/redo03.log
ORA-279 signalled during: ALTER DATABASE RECOVER  datafile 5,6  ...
Thu Mar 28 11:39:08 2019
ALTER DATABASE RECOVER    CONTINUE DEFAULT  
Media Recovery Log /u01/app/oracle/flash_recovery_area/ORCL/archivelog/2019_03_28/o1_mf_1_5397870_%u_.arc
Errors with log /u01/app/oracle/flash_recovery_area/ORCL/archivelog/2019_03_28/o1_mf_1_5397870_%u_.arc
ORA-308 signalled during: ALTER DATABASE RECOVER    CONTINUE DEFAULT  ...
ALTER DATABASE RECOVER CANCEL 
Errors in file /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_17605.trc  (incident=365041):
ORA-00600: internal error code, arguments: [3051], [82], [], [], [], [], [], [], [], [], [], []
ORA-600 signalled during: ALTER DATABASE RECOVER CANCEL ...

通过屏蔽一致性,强制打开库,报kgegpa错误,实例启动失败

Database Characterset is ZHS16GBK
No Resource Manager plan active
Exception[type:SIGSEGV, Address not mapped to object][ADDR:0x319C0CF3] [PC:0x2297740, kgegpa()+40] [flags: 0x0, count:1]
Exception[type:SIGSEGV, Address not mapped to object][ADDR:0x319C0CF3] [PC:0x229596B, kgebse()+279][flags: 0x2, count:2]
Exception[type:SIGSEGV, Address not mapped to object][ADDR:0x319C0CF3] [PC:0x229596B, kgebse()+279][flags: 0x2, count:2]
Thu Mar 28 11:43:15 2019
PMON (ospid: 17939): terminating the instance due to error 397
Instance terminated by PMON, pid = 17939

处理上述错误相关undo,启动数据库报ORA-00600 4193,ORA-00600 4137, ORA-00600 6006

Thu Mar 28 11:50:37 2019
Errors in file /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_p001_18267.trc  (incident=373059):
ORA-00600: internal error code, arguments: [6006], [1], [], [], [], [], [], [], [], [], [], []
Incident details in: /u01/app/oracle/diag/rdbms/orcl/orcl/incident/incdir_373059/orcl_p001_18267_i373059.trc
Stopping background process MMON
Trace dumping is performing id=[cdmp_20190328115038]
Errors in file /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_smon_18247.trc  (incident=372995):
ORA-00600: internal error code, arguments: [6006], [1], [], [], [], [], [], [], [], [], [], []
Incident details in: /u01/app/oracle/diag/rdbms/orcl/orcl/incident/incdir_372995/orcl_smon_18247_i372995.trc
Errors in file /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_smon_18247.trc  (incident=372996):
ORA-00600: internal error code, arguments: [4137], [34.22.4206895], [0], [0], [], [], [], [], [], [], [], []
Incident details in: /u01/app/oracle/diag/rdbms/orcl/orcl/incident/incdir_372996/orcl_smon_18247_i372996.trc
Errors in file /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_18263.trc  (incident=373044):
ORA-00600: internal error code, arguments: [4193], [], [], [], [], [], [], [], [], [], [], []
Incident details in: /u01/app/oracle/diag/rdbms/orcl/orcl/incident/incdir_373044/orcl_ora_18263_i373044.trc
ORACLE Instance orcl (pid = 16) - Error 600 encountered while recovering transaction (34, 22).
Errors in file /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_smon_18247.trc:
ORA-00600: internal error code, arguments: [4137], [34.22.4206895], [0], [0], [], [], [], [], [], [], [], []

通过重建undo,相关报错消失,安排数据导出重建库

发表在 Oracle备份恢复 | 标签为 , , , | 留下评论

ORA-00322 ORA-00312恢复

数据库启动ORA-00322 ORA-00312错误,无法正常启动

Fri Mar 29 17:44:20 2019
ALTER DATABASE RECOVER  datafile 1  
Media Recovery Start
Serial Media Recovery started
Media Recovery failed with error 19909
ORA-283 signalled during: ALTER DATABASE RECOVER  datafile 1  ...
Fri Mar 29 17:44:20 2019
Errors in file c:\app\administrator\diag\rdbms\xff\xff\trace\xff_m000_5392.trc:
ORA-00322: log 1 of thread 1 is not current copy
ORA-00312: online log 1 thread 1: 'D:\APP\ADMINISTRATOR\ORADATA\xff\REDO01.LOG'
Errors in file c:\app\administrator\diag\rdbms\xff\xff\trace\xff_m000_5392.trc:
ORA-00322: log 2 of thread 1 is not current copy
ORA-00312: online log 2 thread 1: 'D:\APP\ADMINISTRATOR\ORADATA\xff\REDO02.LOG'
Errors in file c:\app\administrator\diag\rdbms\xff\xff\trace\xff_m000_5392.trc:
ORA-00322: log 3 of thread 1 is not current copy
ORA-00312: online log 3 thread 1: 'D:\APP\ADMINISTRATOR\ORADATA\xff\REDO03.LOG'

人工指定redo应用,报ORA-00600 3051错误

Fri Mar 29 17:56:33 2019
ALTER DATABASE RECOVER  datafile 1  
Media Recovery Start
Serial Media Recovery started
Recovery of Online Redo Log: Thread 1 Group 2 Seq 27542 Reading mem 0
  Mem# 0: D:\XIFENFEI\REDO02.LOG
ORA-279 signalled during: ALTER DATABASE RECOVER  datafile 1  ...
Fri Mar 29 17:56:49 2019
ALTER DATABASE RECOVER    LOGFILE 'D:\xifenfei\REDO02.log'  
Media Recovery Log D:\xifenfei\REDO02.log
Errors with log D:\xifenfei\REDO02.log
ORA-363 signalled during: ALTER DATABASE RECOVER    LOGFILE 'D:\xifenfei\REDO02.log'  ...
ALTER DATABASE RECOVER CANCEL 
Errors in file c:\app\administrator\diag\rdbms\xff\xff\trace\xff_ora_8532.trc  (incident=147928):
ORA-00600: ??????, ??: [3051], [82], [], [], [], [], [], [], [], [], [], []
Incident details in: c:\app\administrator\diag\rdbms\xff\xff\incident\incdir_147928\xff_ora_8532_i147928.trc

比较明显redo无法正常应用,通过屏蔽数据库一致性,强制拉库

Recovery of Online Redo Log: Thread 1 Group 1 Seq 1 Reading mem 0
  Mem# 0: D:\XIFENFEI\REDO01.LOG
Block recovery stopped at EOT rba 1.76.16
Block recovery completed at rba 1.76.16, scn 0.1073742057
Doing block recovery for file 3 block 272
Resuming block recovery (PMON) for file 3 block 272
Block recovery from logseq 1, block 72 to scn 1073742051
Recovery of Online Redo Log: Thread 1 Group 1 Seq 1 Reading mem 0
  Mem# 0: D:\XIFENFEI\REDO01.LOG
Block recovery completed at rba 1.72.16, scn 0.1073742052
Errors in file c:\app\administrator\diag\rdbms\xff\xff\trace\xff_smon_5144.trc:
ORA-01595: error freeing extent (16) of rollback segment (10))
ORA-00600: internal error code, arguments: [4194], [], [], [], [], [], [], [], [], [], [], []
Fri Mar 29 17:59:12 2019
Errors in file c:\app\administrator\diag\rdbms\xff\xff\trace\xff_mmon_13928.trc  (incident=149097):
ORA-00600: internal error code, arguments: [4194], [], [], [], [], [], [], [], [], [], [], []
Incident details in: c:\app\administrator\diag\rdbms\xff\xff\incident\incdir_149097\xff_mmon_13928_i149097.trc
Fri Mar 29 17:59:12 2019
Trace dumping is performing id=[cdmp_20190329175912]
Completed: alter database open resetlogs

通过重建undo,数据库open正常,安排导出数据导入数据,恢复完成

发表在 Oracle备份恢复 | 标签为 , , , | 留下评论