【故障處理】ORA-18008: cannot find OUTLN schema暨OUTLN使用者的建立

secooler發表於2009-09-18
有同事反映,在sqlplus中使用connect連線到具體使用者時連續報下面的錯誤:“ORA-18008: cannot find OUTLN schema”和“SP2-0575: Use of Oracle SQL feature not in SQL92 Entry Level.”。
現將這個問題的現象、原因和處理方法記錄在此。

1.問題現象
當使用sqlplus登陸資料庫之後,無法使用connect連線到具體的使用者中,報錯資訊如下
ora10g@secDB /home/oracle$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.3.0 - Production on Fri Sep 18 10:35:54 2009

Copyright (c) 1982, 2006, Oracle.  All Rights Reserved.



Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit Production
With the Partitioning, Oracle Label Security, OLAP and Data Mining Scoring Engine options


sys@ora10g>
sys@ora10g> conn sec/sec
Error accessing PRODUCT_USER_PROFILE
Warning:  Product user profile information not loaded!
You may need to run PUPBLD.SQL as SYSTEM
ERROR:
ORA-18008: cannot find OUTLN schema


Error accessing package DBMS_APPLICATION_INFO
SP2-0575: Use of Oracle SQL feature not in SQL92 Entry Level.
Connected.
sec@ora10g>
使用oerr工具檢視一下上面兩個報錯資訊的解釋
sys@ora10g> !oerr ora 18008
18008, 00000, "cannot find OUTLN schema"
// *Cause:  The database creation script. that creates this schema must not
//          have been executed.
// *Action: Review the log files to see what happened when the database
//          was created.

sys@ora10g> !oerr sp2 0575
00575,0, "Use of Oracle SQL feature not in SQL92 %s Level.\n"
// *Cause:  A SQL statement was attempted that is not FIPS compliant.
//          May also occur if a SQL*Plus feature e.g. SET AUTOTRACE
//          that uses Oracle-specific SQL was turned on when you are
//          using FIPS flagging.
// *Action: Use SET FLAGGER and turn FIPS compliance checking OFF,
//          or rewrite the statement.


2.問題原因
OUTLN使用者被誤刪除。

3.處理方法
手工建立這個OUTLN使用者,並賦予需要的許可權。
1)建立OUTLN使用者
sys@ora10g> create user outln identified by outln default tablespace system temporary tablespace temp profile default account unlock;

User created.

2)授予角色
sys@ora10g> grant resource to outln;

Grant succeeded.

sys@ora10g> alter user outln default role all;

User altered.

3)授予系統許可權
sys@ora10g> grant unlimited tablespace, execute any procedure, create session to outln;

Grant succeeded.

4)授予物件角色,將SYS使用者下的OUTLN_PKG包的執行許可權授權給OUTLN
sys@ora10g> grant execute on sys.outln_pkg to outln;

Grant succeeded.

建立完這個使用者後,也可以考慮將另外一個正常庫的OUTLN使用者下的資料庫物件遷移到這個使用者中。

再次測試連線性,問題已經得到有效解決。
sys@ora10g> conn sec/sec
Connected.

4.除了上面的方法外,建立OUTLN使用者也可以參考Metalink上的指令碼來完成
1)10gR2上的建立指令碼可以從Metalink的這個文章中得到
Subject:     Script. to create user OUTLN in 10.2
      Doc ID:     422983.1     Type:     SCRIPT
      Modified Date :     04-SEP-2008     Status:     PUBLISHED

2)整理後的指令碼如下:
ora10g@secDB /home/oracle$ cat cre_outln102.sql
set serveroutput on

DECLARE
   user_exists EXCEPTION;
   outln_user           NUMBER;
   outln_tables         NUMBER;
   extra_outln_tables   NUMBER;
   DDL_CURSOR           INTEGER;
BEGIN
   SELECT   COUNT ( * )
     INTO   outln_user
     FROM   user$
    WHERE   name = 'OUTLN';

   SELECT   COUNT ( * )
     INTO   outln_tables
     FROM   obj$
    WHERE   name IN ('OL$', 'OL$HINTS', 'OL$NODES')
            AND owner# = (SELECT   user#
                            FROM   user$
                           WHERE   name = 'OUTLN');

   SELECT   COUNT ( * )
     INTO   extra_outln_tables
     FROM   obj$
    WHERE       name NOT IN ('OL$', 'OL$HINTS', 'OL$NODES')
            AND type# = 2
            AND owner# = (SELECT   user#
                            FROM   user$
                           WHERE   name = 'OUTLN');


   DDL_CURSOR := DBMS_SQL.open_cursor;

   IF outln_user = 0
   THEN
      DBMS_SQL.parse (DDL_CURSOR,
                      'create user outln identified by outln',
                      DBMS_SQL.native);
      DBMS_SQL.parse (
         DDL_CURSOR,
         'grant connect, resource, execute any procedure to outln',
         DBMS_SQL.native
      );
      DBMS_SQL.parse (
         DDL_CURSOR,
            'create table outln.ol$ ( '
         || 'ol_name varchar2(30), '
         || 'sql_text long, '
         || 'textlen number, '
         || 'signature raw(16), '
         || 'hash_value number, '
         || 'hash_value2 number, '
         || 'category varchar2(30), '
         || 'version varchar2(64), '
         || 'creator varchar2(30), '
         || 'timestamp date, '
         || 'flags number, '
         || 'hintcount number, '
         || 'spare1 number, '
         || 'spare2 varchar2(1000))',
         DBMS_SQL.native
      );
      DBMS_SQL.parse (
         DDL_CURSOR,
            'create table outln.ol$hints ( '
         || 'ol_name varchar2(30), '
         || 'hint# number, '
         || 'category varchar2(30), '
         || 'hint_type number, '
         || 'hint_text varchar2(512), '
         || 'stage# number, '
         || 'node# number, '
         || 'table_name varchar2(30), '
         || 'table_tin number, '
         || 'table_pos number, '
         || 'ref_id number, '
         || 'user_table_name varchar2(64), '
         || 'cost FLOAT(126),'
         || 'cardinality FLOAT(126),'
         || 'bytes FLOAT(126),'
         || 'hint_textoff number, '
         || 'hint_textlen number,'
         || 'join_pred varchar2(2000),'
         || 'spare1 number, '
         || 'spare2 number, '
         || 'hint_string clob)',
         DBMS_SQL.native
      );
      DBMS_SQL.parse (
         DDL_CURSOR,
            'create table outln.ol$nodes ( '
         || 'ol_name varchar2(30), '
         || 'category varchar2(30), '
         || 'node_id number, '
         || 'parent_id number, '
         || 'node_type number, '
         || 'node_textlen number, '
         || 'node_textoff number, '
         || 'node_name varchar2(64))',
         DBMS_SQL.native
      );
      DBMS_SQL.parse (
         DDL_CURSOR,
         'create unique index outln.ol$name ' || 'on outln.ol$(ol_name)',
         DBMS_SQL.native
      );
      DBMS_SQL.parse (
         DDL_CURSOR,
         'create unique index outln.ol$signature '
         || ' on outln.ol$(signature,category)',
         DBMS_SQL.native
      );
      DBMS_SQL.parse (
         DDL_CURSOR,
         'create unique index outln.ol$hnt_num '
         || ' on outln.ol$hints(ol_name, hint#)',
         DBMS_SQL.native
      );
      DBMS_OUTPUT.put_line ('OUTLN CREATION SUCCESSFUL');
   ELSE
      IF outln_tables != 3 OR extra_outln_tables != 0
      THEN
         DBMS_OUTPUT.put_line ('ERROR - OUTLN USER ALREADY EXISTS');
         RAISE user_exists;
      ELSE
         DBMS_OUTPUT.put_line ('OUTLN CREATION SUCCESSFUL');
      END IF;
   END IF;
EXCEPTION
   WHEN user_exists
   THEN
      RAISE;
END;
/      


3)使用上面的指令碼進行建立
sys@ora10g> @cre_outln102.sql
OUTLN CREATION SUCCESSFUL

PL/SQL procedure successfully completed.

這種建立方法既高效又完整。並且其中也蘊含著很多小技巧,慢慢體會吧。

4)如果您的資料庫版本是8i和9i的,可以參考Metalink上關於8i和9i建立OUTLN使用者的指令碼。
9i的參考文件如下:
Subject:     Script. to create user OUTLN in 9i
      Doc ID:     240478.1     Type:     SCRIPT
      Modified Date :     08-DEC-2008     Status:     PUBLISHED

8i的參考文件如下:
Subject:     Script. to create user OUTLN in 8i
      Doc ID:     98572.1     Type:     BULLETIN
      Modified Date :     10-JUN-2003     Status:     PUBLISHED      

5.就這個OUTLN使用者擴充套件一下,有沒有想知道OUTLN這個使用者存在意義是什麼呢?任何事物都是有存在意義的。
摘錄Metalink上有關OUTLN的一系列疑問的Q&A,其中描述的非常的清楚,我就不再贅述了。請閱……
Subject:     What is the OUTLN User?
      Doc ID:     1071358.6     Type:     BULLETIN
      Modified Date :     30-MAR-2009     Status:     PUBLISHED

What is the OUTLN user?
=======================
 
This document will address these topics:

1. Why is the user OUTLN created by Oracle?
2. What are stored outlines and what are they good for?
3. What does OUTLN user own?
4. If it is dropped by accident, can it be created on the fly?


1. Why is the user OUTLN created by Oracle?
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

The OUTLN user is created automatically during installation of Oracle.
The create user outln statement appears in SQL.BSQ that is run at database
creation time to initiate the datadictionary, as such the OUTLN user is an
integral part of the database. This user is granted connect, resource, and
execute any procedure privileges. It is also set to locked and expired
since no end-user connections should be made to this acount except for
maintenance. The database administrator should change the password for
the OUTLN schema just as for the SYS and SYSTEM schemas, also make sure the
account is locked and only unlock it in case a DBA needs access to it for
maintenance operations.


2. What are stored outlines and what are they good for?
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Stored outlines support a very usefull feature: Optimizer Plan Stability

After carefully tuning an application, you might want to ensure that the
optimizer generates the same execution plan whenever the same SQL statements
are executed. Plan stability allows you to maintain the same execution plans
for the same SQL statements, regardless of changes to the database such as
re-analyzing tables, adding or deleting data, modifying a table's columns,
constraints, or indexes, changing the system configuration, or even upgrading
to a new version of the optimizer.

The CREATE OUTLINE statement creates a stored outline, which contains a set of
attributes that the optimizer uses to create an execution plan. Stored outlines
can also be created automatically by setting the system parameter
CREATE_STORED_OUTLINES to TRUE.

The system parameter USE_STORED_OUTLINES can be set to TRUE, FALSE, or a
category name to indicate whether to make use of existing stored outlines for
queries that are being executed. The OUTLN_PKG package provides procedures used
for managing stored outlines.
 
Oracle adds the OUTLN user schema to support Plan Stability. The OUTLN user
acts as a place to centrally manage metadata associated with stored outlines.
 
You cannot create this user on the fly. The user OUTLN makes use of the package
OUTLN_PKG which is used to manage stored outlines and their outline categories.

The package sys.outln_pkg is created by script. "dbmsol.sql" in the
$ORACLE_HOME/rdbms/admin directory. The "dbmsol.sql" script. is called from
"catproc.sql". "prvtol.plb" creates the body of "outln_pkg"; it is also called
from catproc.sql.


3. What does OUTLN user own?
~~~~~~~~~~~~~~~~~~~~~~~~~~~~

The outline user ows some tables that are the stored outlines metadata
repository, typically these are OL$, OL$HINTS and OL$NODES and some indexes.

In case of an upgrade or migration, the upgrade or migration scripts will take
care of any changes to the OUTLN respository tables and associated package.

4. If it is dropped by accident, can it be created on the fly?
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

First of all: you should never try to drop the OUTLN user (in the same way as you
should not drop the SYSTEM user) but if you accidently did you can recreate
it by running the same commands from the file sql.bsq again to create the outln
user (do NOT run sql.bsq directly!), issue grants, create tables and indexes,
note that "M_IDEN" stands for the length of an identifier which is 30 for current
releases, "M_CSIZ" is 2000. For some versions support has created an automated
script. to restore the outln schema and its contents.

Naturally you will have lost all stored outlines when you drop the OUTLN schema,
also, since the kernel makes implicit assumptions on its existence , you will get
the error ORA-18009 "one or more outline system tables do not exist" when you try
to issue outline related sql statements when the OUTLN schema is not there.

6.小結
透過這麼一個小小的問題就可以引申出那麼多有趣的知識,所以說:技術是一個自我陶醉的過程。
這裡只是拋個磚,更多的知識請朋友們一同來發掘。

故障處理結論:千萬不要再有意人為的將OUTLN使用者drop掉了。     

Goodluck to you.

-- The End --

來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/519536/viewspace-615000/,如需轉載,請註明出處,否則將追究法律責任。

相關文章