sql plan baseline简单介绍

Posted on 2019 年 04 月 05 日 by 惜分飞

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.

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

Posted on 2019 年 03 月 29 日 by 惜分飞

联系：手机/微信(+86 17813235971) QQ(107644445)

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

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

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,相关报错消失,安排数据导出重建库

ORA-00322 ORA-00312恢复

Posted on 2019 年 03 月 29 日 by 惜分飞

联系：手机/微信(+86 17813235971) QQ(107644445)

标题：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正常,安排导出数据导入数据,恢复完成

利用VNCR修复CVE-2012-1675漏洞

Posted on 2019 年 03 月 26 日 by 惜分飞

联系：手机/微信(+86 17813235971) QQ(107644445)

标题：利用VNCR修复CVE-2012-1675漏洞

随着对安全的重视,TNS Listener远程数据投毒漏洞(CVE-2012-1675)被很多单位要求进行整改,而且级别是高危

如果是11.2.0.4之前版本,特别是在rac环境中,如果要整改该问题相对比较麻烦,需要通过配置Oracle wallet来实现,配置比较复杂,而且还要重启实例,影响比较大,具体参考:Using Class of Secure Transport (COST) to Restrict Instance Registration in Oracle RAC (Doc ID 1340831.1),对于单机环境直接参考以前文章:Oracle Database Server ‘TNS Listener’远程数据投毒漏洞(CVE-2012-1675)的解决方案.从11.2.0.4开始Oracle引入了Valid Node Checking For Registration (VNCR)新特性,可以通过简单的配置即可完成该漏洞修复
在listener.ora文件中增加(grid/oracle用户)

VALID_NODE_CHECKING_REGISTRATION_LISTENER=ON
VALID_NODE_CHECKING_REGISTRATION_LISTENER_SCAN1=ON
REGISTRATION_INVITED_NODES_LISTENER_SCAN1=(hisdb1,hisdb2)

重启监听

LSNRCTL>set current_listener listener_name
LSNRCTL>stop
LSNRCTL>start

验证是否生效
1.在远程机器尝试远程注册

alter system set remote_listener='(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.115.15)(PORT=1521))' scope=memory;

2.观察监听日志
VNCR

类似这样证明已经生效.

补充说明

VALID_NODE_CHECKING_REGISTRATION_listener_name
Values:

OFF/0 - Disable VNCR//禁用VNCR，此功能不会对注册过来的service进行check;

ON/1/LOCAL - The default. Enable VNCR. All local machine IPs can register.
启用VNCR,默认只允许本机的所有IP的服务注册到本监听,可通过REGISTRATION_INVITED_NODES参数添加其他有必要的服务器;

SUBNET/2 - All machines in the subnet are allowed registration.//指定子网内的服务器可以注册过来


REGISTRATION_INVITED_NODES_listener-name 
控制允许链接过来的节点，可以通过IP地址/主机名/网段来指定

Values are valid IPs, valid hosts, a subnet using CIDR notation (for ip4/6), or wildcard (*) for ipv4. 
For example:REGISTRATION_INVITED_NODES_Listener=(net-vm1, 127.98.45.209, 127.42.5.*)

Note 
that when an INVITED list is set, it will automatically include the 
machine's local IP in the list. There is no need to include it.

--11.2.0.4和12c不一样之处
在12.1 RAC数据库上，listener的参数VALID_NODE_CHECKING_REGISTRATION_listener_name默认设置为SUBNET / 2，
即子网中的所有计算机都允许注册.所以12c默认不能解决CVE-2012-1675漏洞

参考文档
Valid Node Checking For Registration (VNCR) (Doc ID 1600630.1)
How to Enable VNCR on RAC Database to Register only Local Instances (Doc ID 1914282.1)

12C数据库遭遇ORA-600 16703

Posted on 2019 年 03 月 11 日 by 惜分飞

联系：手机/微信(+86 17813235971) QQ(107644445)

标题：12C数据库遭遇ORA-600 16703

关于tab$异常的问题,我们在10G(10g数据库遭遇ORA-600 16703),11G(警告：互联网中有oracle介质被注入恶意程序导致—ORA-600 16703)中有发现,近期在12C版本中也发现该问题.再次提醒请注意检查安装介质的md5值和现在运行的库中是否有相关恶意程序
数据库版本

alert日志报错

************************************************************
Warning: The SCN headroom for this database is only 45 days!
************************************************************
************************************************************
Warning: The SCN headroom for this database is only 45 days!
************************************************************
Picked broadcast on commit scheme to generate SCNs
Fri Jan 25 11:34:55 2019
Starting background process TMON
Fri Jan 25 11:34:55 2019
TMON started with pid=104, OS id=47282
Fri Jan 25 11:34:55 2019
Thread 1 advanced to log sequence 11942 (thread open)
Thread 1 opened at log sequence 11942
Current log# 5 seq# 11942 mem# 0: +DATA/xifenfei/ONLINELOG/redo01_01.log
Successful open of redo thread 1
Fri Jan 25 11:34:55 2019
MTTR advisory is disabled because FAST_START_MTTR_TARGET is not set
Fri Jan 25 11:34:55 2019
Network Resource Management enabled for Process LG00 (pid 451491) for Exadata I/O
Fri Jan 25 11:34:55 2019
SMON: enabling cache recovery
Errors in file /u01/oracle/app/oracle/diag/rdbms/xifenfei/xifenfei1/trace/xifenfei1_ora_46443.trc (incident=394486):
ORA-00600: internal error code, arguments: [16703], [1403], [20], [], [], [], [], [], [], [], [], []
Use ADRCI or Support Workbench to package the incident.
See Note 411.1 at My Oracle Support for error and packaging details.
Fri Jan 25 11:34:56 2019
Errors in file /u01/oracle/app/oracle/diag/rdbms/xifenfei/xifenfei1/trace/xifenfei1_ora_46443.trc:
ORA-00704: bootstrap process failure
ORA-00600: internal error code, arguments: [16703], [1403], [20], [], [], [], [], [], [], [], [], []
Fri Jan 25 11:34:56 2019
Errors in file /u01/oracle/app/oracle/diag/rdbms/xifenfei/xifenfei1/trace/xifenfei1_ora_46443.trc:
ORA-00704: bootstrap process failure
ORA-00704: bootstrap process failure
ORA-00600: internal error code, arguments: [16703], [1403], [20], [], [], [], [], [], [], [], [], []
Fri Jan 25 11:34:56 2019
Errors in file /u01/oracle/app/oracle/diag/rdbms/xifenfei/xifenfei1/trace/xifenfei1_ora_46443.trc:
ORA-00704: bootstrap process failure
ORA-00704: bootstrap process failure
ORA-00600: internal error code, arguments: [16703], [1403], [20], [], [], [], [], [], [], [], [], []
Fri Jan 25 11:34:56 2019
Error 704 happened during db open, shutting down database
USER (ospid: 46443): terminating the instance due to error 704
Fri Jan 25 11:34:57 2019
Instance terminated by USER, pid = 46443

处理思路基本上和其他两个版本一样,都是对tab$进行修复,然后直接open数据库,但是在这次的恢复中遇到ORA-00600 kdfReserveSingle_1错误
ORA-600 kdfReserveSingle_1

SQL> alter database open ;
alter database open
*
ERROR at line 1:
ORA-01092: ORACLE instance terminated. Disconnection forced
ORA-00600: internal error code, arguments: [kdfReserveSingle_1], [0], [65280],
[], [], [], [], [], [], [], [], []
Process ID: 319852
Session ID: 2401 Serial number: 24555

---alert日志
Starting background process TMON
Fri Jan 25 20:51:41 2019
TMON started with pid=94, OS id=322554
Fri Jan 25 20:51:41 2019
Thread 1 advanced to log sequence 11943 (thread open)
Thread 1 opened at log sequence 11943
Current log# 6 seq# 11943 mem# 0: +DATA/xifenfei/ONLINELOG/redo02_01.log
Successful open of redo thread 1
Fri Jan 25 20:51:41 2019
MTTR advisory is disabled because FAST_START_MTTR_TARGET is not set
Fri Jan 25 20:51:41 2019
Network Resource Management enabled for Process LG00 (pid 319754) for Exadata I/O
Fri Jan 25 20:51:41 2019
SMON: enabling cache recovery
Fri Jan 25 20:51:41 2019
[319852] Successfully onlined Undo Tablespace 2.
Undo initialization finished serial:0 start:88159754 end:88160264 diff:510 ms (0.5 seconds)
Verifying minimum file header compatibility (11g) for tablespace encryption..
Verifying 11g file header compatibility for tablespace encryption completed
Fri Jan 25 20:51:41 2019
SMON: enabling tx recovery
Fri Jan 25 20:51:41 2019
Database Characterset is ZHS16GBK
Errors in file /u01/oracle/app/oracle/diag/rdbms/xifenfei/xifenfei1/trace/xifenfei1_ora_319852.trc (incident=466446):
ORA-00600: internal error code, arguments: [kdfReserveSingle_1], [0], [65280], [], [], [], [], [], [], [], [], []
Use ADRCI or Support Workbench to package the incident.
See Note 411.1 at My Oracle Support for error and packaging details.
Fri Jan 25 20:51:42 2019
Errors in file /u01/oracle/app/oracle/diag/rdbms/xifenfei/xifenfei1/trace/xifenfei1_ora_319852.trc:
ORA-00600: internal error code, arguments: [kdfReserveSingle_1], [0], [65280], [], [], [], [], [], [], [], [], []
Fri Jan 25 20:51:42 2019
Errors in file /u01/oracle/app/oracle/diag/rdbms/xifenfei/xifenfei1/trace/xifenfei1_ora_319852.trc:
ORA-00600: internal error code, arguments: [kdfReserveSingle_1], [0], [65280], [], [], [], [], [], [], [], [], []
Error 600 happened during db open, shutting down database
USER (ospid: 319852): terminating the instance due to error 600
Fri Jan 25 20:51:43 2019
Instance terminated by USER, pid = 319852
ORA-1092 signalled during: alter database open…
opiodr aborting process unknown ospid (319852) as a result of ORA-1092
Fri Jan 25 20:51:49 2019
ORA-1092 : opitsk aborting process

这个错误主要是由于12c的tab$和以前版本比有一些特殊,导致开始恢复不全,通过继续修复tab$之后数据库正常open.
相关文章汇总
tab$恢复错误汇总
 12C数据库遭遇ORA-600 16703
10G数据库遭遇ORA-600 16703
ORA-600 16703故障解析—tab$表被清空
 警告：互联网中有oracle介质被注入恶意程序导致—ORA-600 16703