分类目录归档:ORACLE 12C

重建oraInventory解决ORA-20001

数据库启动报ORA-20001: Latest xml inventory is not loaded into table错误

Completed: ALTER DATABASE OPEN
2018-01-23T23:46:27.924841+08:00
CJQ0 started with pid=54, OS id=6653 
2018-01-23T23:46:31.705550+08:00
Unable to obtain current patch information due to error: 20001,
  ORA-20001: Latest xml inventory is not loaded into table
ORA-06512: at "SYS.DBMS_QOPATCH", line 777
ORA-06512: at "SYS.DBMS_QOPATCH", line 864
ORA-06512: at "SYS.DBMS_QOPATCH", line 2222
ORA-06512: at "SYS.DBMS_QOPATCH", line 740
ORA-06512: at "SYS.DBMS_QOPATCH", line 2247

===========================================================
Dumping current patch information
===========================================================
Unable to obtain current patch information due to error: 20001
===========================================================

查询相关sql报错

SYS@xffdb>select * from OPATCH_XML_INV ;
ERROR:
ORA-29913: error in executing ODCIEXTTABLEFETCH callout
ORA-29400: data cartridge error
KUP-04004: error while reading file /u03/app/oracle/product/12.2.0.1/dbhome/QOpatch/qopiprep.bat



no rows selected

Elapsed: 00:00:00.58


SYS@xffdb>select xmltransform(dbms_qopatch.get_opatch_lsinventory(), dbms_qopatch.GET_OPATCH_XSLT()) from dual ;
ERROR:
ORA-20001: Latest xml inventory is not loaded into table
ORA-06512: at "SYS.DBMS_QOPATCH", line 777
ORA-06512: at "SYS.DBMS_QOPATCH", line 864
ORA-06512: at "SYS.DBMS_QOPATCH", line 2222
ORA-06512: at "SYS.DBMS_QOPATCH", line 740
ORA-06512: at "SYS.DBMS_QOPATCH", line 2247



no rows selected

Elapsed: 00:00:00.63

datapatch -prereq报错

[oracle@xifenfei ~]$ $ORACLE_HOME/OPatch/datapatch -prereq
SQL Patching tool version 12.2.0.1.0 Production on Tue Jan 23 18:11:32 2018
Copyright (c) 2012, 2017, Oracle.  All rights reserved.

Connecting to database...OK
Note:  Datapatch will only apply or rollback SQL fixes for PDBs
       that are in an open state, no patches will be applied to closed PDBs.
       Please refer to Note: Datapatch: Database 12c Post Patch SQL Automation
       (Doc ID 1585822.1)

Queryable inventory could not determine the current opatch status.
Execute 'select dbms_sqlpatch.verify_queryable_inventory from dual'
and/or check the invocation log
/u03/app/oracle/cfgtoollogs/sqlpatch/sqlpatch_4909_2018_01_23_18_11_32/sqlpatch_invocation.log
for the complete error.

Prereq check failed, exiting without installing any patches.

Please refer to MOS Note 1609718.1 and/or the invocation log
/u03/app/oracle/cfgtoollogs/sqlpatch/sqlpatch_4909_2018_01_23_18_11_32/sqlpatch_invocation.log
for information on how to resolve the above errors.

SQL Patching tool complete on Tue Jan 23 18:11:45 2018

分析qopiprep.bat文件

cd $ORACLE_HOME
PATH=/bin:/usr/bin
export PATH

# sed tried to convert from one encoding to other in presence of LC_ALL
# or LANG settings. Since opatch returning UTF-8 based encoding we do not
# need such a conversion. So safely skip it
LANG=en_US.UTF-8
export LANG

LC_ALL=''
export LC_ALL

# Option: "-retry 0" avoids retries in case of locked inventory.

# Option: "-invPtrLoc" is required for non-central-inventory
# locations. $OPATCH_PREP_LSINV_OPTS which may set by users
# in the environment to configure special OPatch options 
# ("-jdk" is another good candidate that may require configuration!).

# Option: "-all" gives information on all Oracle Homes
# installed in the central inventory.  With that information, the
# patches of non-RDBMS homes could be fetched. 

DBSID=$ORACLE_SID
ORABASE=`$ORACLE_HOME/bin/orabasehome`

rm -rf $ORABASE/rdbms/log/xml_file_$DBSID.xml
$ORACLE_HOME/OPatch/opatch lsinventory -xml  $ORABASE/rdbms/log/xml_file_$DBSID.xml -retry 0 -invPtrLoc $ORACLE_HOME/oraInst.loc >> $ORABASE/rdbms/log/stout_$DBSID.txt
cat $ORABASE/rdbms/log/xml_file_$DBSID.xml | sed 's/^ *//' | tr '\n' ' '
echo "UIJSVTBOEIZBEFFQBL"
rm $ORABASE/rdbms/log/xml_file_$DBSID.xml
rm $ORABASE/rdbms/log/stout_$DBSID.txt

这里主要是$ORACLE_HOME/OPatch/opatch lsinventory可能异常,测试该功能
qopatch_log日志

[oracle@xifenfei ~]$ tail -f /u03/app/oracle/product/12.2.0.1/dbhome/QOpatch/qopatch_log.log
 LOG file opened at 01/23/18 18:48:55

KUP-05007:   Warning: Intra source concurrency disabled because the preprocessor option is being used.

Field Definitions for table OPATCH_XML_INV
  Record format DELIMITED BY NEWLINE
  Data in file has same endianness as the platform
  Reject rows with all null fields

  Fields in Data Source: 

    XML_INVENTORY                   CHAR (100000000)
      Terminated by "UIJSVTBOEIZBEFFQBL"
      Trim whitespace same as SQL Loader
KUP-04004: error while reading file /u03/app/oracle/product/12.2.0.1/dbhome/QOpatch/qopiprep.bat
KUP-04017: OS message: Error 0
KUP-04017: OS message: LsInventorySession failed: RawInventory gets null OracleHomeInfo
cat: /u03/app/oracle/product/12.2.0.1/dbhome/rdbms/log/xml_file_xffdb.xml: No such file or direc
KUP-04118: operation "pipe read", location "skudmir:2"

opatch lsinventory验证

[oracle@xifenfei ~]$ /u03/app/oracle/product/12.2.0.1/dbhome/OPatch/opatch lsinventory
Oracle Interim Patch Installer version 12.2.0.1.6
Copyright (c) 2018, Oracle Corporation.  All rights reserved.


Oracle Home       : /u03/app/oracle/product/12.2.0.1/dbhome
Central Inventory : /u01/app/oraInventory
   from           : /u03/app/oracle/product/12.2.0.1/dbhome/oraInst.loc
OPatch version    : 12.2.0.1.6
OUI version       : 12.2.0.1.4
Log file location : /u03/app/oracle/product/12.2.0.1/dbhome/cfgtoollogs/opatch/opatch2018-01-23_23-50-29PM_1.log

List of Homes on this system:

  Home name= OraDB12Home1, Location= "/u01/app/oracle/product/12.2.0/dbhome_1"
LsInventorySession failed: RawInventory gets null OracleHomeInfo

OPatch failed with error code 73

现在到这一步,可以确定判断opatch lsinventory运行异常,导致DBMS_QOPATCH无法正常工作,而引起opatch异常的原因是由于RawInventory gets null OracleHomeInfo
分析inventory.xml 文件

[oracle@xifenfei ContentsXML]$ cat inventory.xml 

<?xml version="1.0" standalone="yes" ?>
<!-- Copyright (c) 1999, 2016, Oracle and/or its affiliates.
All rights reserved. -->
<!-- Do not modify the contents of this file by hand. -->
<INVENTORY>
<VERSION_INFO>
   <SAVED_WITH>12.2.0.1.0</SAVED_WITH>
   <MINIMUM_VER>2.1.0.6.0</MINIMUM_VER>
</VERSION_INFO>
<HOME_LIST>
<HOME NAME="OraDB12Home1" LOC="/u01/app/oracle/product/12.2.0/dbhome_1" TYPE="O" IDX="1"/>
</HOME_LIST>
<COMPOSITEHOME_LIST>
</COMPOSITEHOME_LIST>
</INVENTORY>

因为该机器上安装过三个版本的oracle,12.2 beta,11.2.0.4,12.2.0.1,现在oracle home只有第一个beta的,因此这个部分肯定异常,导致后面的12.2正式版无法获取到oraclehome
重建oraInventory

[oracle@xifenfei app]$ cd $ORACLE_HOME/oui/bin
[oracle@xifenfei bin]$ ./runInstaller -silent -ignoreSysPrereqs -attachHome ORACLE_HOME="/u01/app/oracle/product/12.2.0/dbhome_1" ORACLE_HOME_NAME="OraDB12betaHome1"
Starting Oracle Universal Installer...

Checking swap space: must be greater than 500 MB.   Actual 3935 MB    Passed
The inventory pointer is located at /etc/oraInst.loc
'AttachHome' was successful.
[oracle@xifenfei bin]$ ./runInstaller -silent -ignoreSysPrereqs -attachHome ORACLE_HOME="/u02/app/oracle/product/11.2.0.4/dbhome" ORACLE_HOME_NAME="OraDb11g_home1"
Starting Oracle Universal Installer...

Checking swap space: must be greater than 500 MB.   Actual 3935 MB    Passed
The inventory pointer is located at /etc/oraInst.loc
'AttachHome' was successful.
[oracle@xifenfei bin]$ ./runInstaller -silent -ignoreSysPrereqs -attachHome ORACLE_HOME="/u03/app/oracle/product/12.2.0.1/dbhome" ORACLE_HOME_NAME="OraDb122g_home1"
Starting Oracle Universal Installer...

Checking swap space: must be greater than 500 MB.   Actual 3935 MB    Passed
The inventory pointer is located at /etc/oraInst.loc
'AttachHome' was successful.

--验证inventory.xml 文件
[oracle@xifenfei ContentsXML]$ cat inventory.xml 

<?xml version="1.0" standalone="yes" ?>
<!-- Copyright (c) 1999, 2018, Oracle and/or its affiliates.
All rights reserved. -->
<!-- Do not modify the contents of this file by hand. -->
<INVENTORY>
<VERSION_INFO>
   <SAVED_WITH>12.2.0.1.4</SAVED_WITH>
   <MINIMUM_VER>2.1.0.6.0</MINIMUM_VER>
</VERSION_INFO>
<HOME_LIST>
<HOME NAME="OraDB12betaHome1" LOC="/u01/app/oracle/product/12.2.0/dbhome_1" TYPE="O" IDX="1"/>
<HOME NAME="OraDb11g_home1" LOC="/u02/app/oracle/product/11.2.0.4/dbhome" TYPE="O" IDX="2"/>
<HOME NAME="OraDb122g_home1" LOC="/u03/app/oracle/product/12.2.0.1/dbhome" TYPE="O" IDX="3"/>
</HOME_LIST>
<COMPOSITEHOME_LIST>
</COMPOSITEHOME_LIST>
</INVENTORY>

验证opatch lsinventory

[oracle@xifenfei bin]$ opatch lsinv
Oracle Interim Patch Installer version 12.2.0.1.6
Copyright (c) 2018, Oracle Corporation.  All rights reserved.


Oracle Home       : /u03/app/oracle/product/12.2.0.1/dbhome
Central Inventory : /u01/app/oraInventory
   from           : /u03/app/oracle/product/12.2.0.1/dbhome/oraInst.loc
OPatch version    : 12.2.0.1.6
OUI version       : 12.2.0.1.4
Log file location : /u03/app/oracle/product/12.2.0.1/dbhome/cfgtoollogs/opatch/opatch2018-01-24_00-19-55AM_1.log

Lsinventory Output file location : /u03/app/oracle/product/12.2.0.1/dbhome/cfgtoollogs/opatch/lsinv/lsinventory2018-01-24_00-19-55AM.txt

--------------------------------------------------------------------------------
Local Machine Information::
Hostname: xifenfei
ARU platform id: 226
ARU platform description:: Linux x86-64

Installed Top-level Products (1): 

Oracle Database 12c                                                  12.2.0.1.0
There are 1 products installed in this Oracle Home.


There are no Interim patches installed in this Oracle Home.


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

OPatch succeeded.

验证dbms_qopatch工作正常

[oracle@xifenfei ContentsXML]$ $ORACLE_HOME/OPatch/datapatch -prereq 
SQL Patching tool version 12.2.0.1.0 Production on Wed Jan 24 00:21:48 2018
Copyright (c) 2012, 2017, Oracle.  All rights reserved.

Connecting to database...OK
Note:  Datapatch will only apply or rollback SQL fixes for PDBs
       that are in an open state, no patches will be applied to closed PDBs.
       Please refer to Note: Datapatch: Database 12c Post Patch SQL Automation
       (Doc ID 1585822.1)
Determining current state...done
Adding patches to installation queue and performing prereq checks...done
Installation queue:
  For the following PDBs: CDB$ROOT PDB$SEED
    Nothing to roll back
    Nothing to apply

SQL Patching tool complete on Wed Jan 24 00:21:55 2018
SYS@xffdb>select dbms_sqlpatch.verify_queryable_inventory from dual;

VERIFY_QUERYABLE_INVENTORY
--------------------------------------------------------------------------------------------------
OK

Elapsed: 00:00:01.03
SYS@xffdb>select xmltransform(dbms_qopatch.get_opatch_lsinventory(), dbms_qopatch.GET_OPATCH_XSLT()) from dual ;

XMLTRANSFORM(DBMS_QOPATCH.GET_OPATCH_LSINVENTORY(),DBMS_QOPATCH.GET_OPATCH_XSLT())
----------------------------------------------------------------------------------------------------------

Oracle Querayable Patch Interface 1.0
-----------------------------------------


Elapsed: 00:00:01.09
SYS@xffdb>

通过修复错误的oraInventory解决ORA-20001问题

发表在 ORA-xxxxx, ORACLE 12C | 标签为 , , , , | 评论关闭

18c新特性:Scalable Sequences(自适应序列)

作为18c的新特性,其实在oracle 12.2 引入了Scalable Sequences作为一个隐藏特性,为了改善在高并发系统中,特别是使用seq作为index,大量插入记录导致index的争用,oracle自己实现了以前我们需要人工去自定义seq的方式(instance+sid+seq的类似算法方式),该功能将在oracle 18c中正式推出,我这里带领大家先体会下
SCALE/NOSCALE
When SCALE is specified, a numeric offset is affixed to the beginning of the sequence. This offset if of the form iii||sss||, where,
iii denotes a three digit instance offset given by (instance_id % 100) + 100,
sss denotes a three digits session offset given by (session_id % 1000), and
|| is the concatenation operator

EXTEND/NOEXTEND
When EXTEND is specified with the SCALE keyword, the generated sequence values are all of length (x+y), where x is the length of the scalable offset (default 6), and y is the maximum number of digits in the sequence maxvalue/minvalue. Thus, for an ascending sequence with maxvalue 100 and SCALABLE EXTEND specified, the generated sequence values are of the form iii||sss||001, iii||sss||002, …,iii||sss||100
The default setting for the SCALE clause is NOEXTEND. With the NOEXTEND setting, the generated sequence values are at most as wide as the maximum number of digits in the sequence maxvalue/minvalue. This setting is useful for integration with existing applications where sequences are used to populate fixed width columns. On invocation of NEXTVAL on a sequence with SCALABLE NOEXTEND specified, a user error is thrown if the generated value requires more digits of representation than the sequence’s maxvalue/minvalue.

SCALE的算法就是(instance_id % 100)(_kqdsn_instance_digits) + 100||(session_id % 1000)(_kqdsn_cpu_digits)+seq(EXTEND/NOEXTEND确定是否固定宽度)

Scalable Sequences语法

CREATE | ALTER SEQUENCE [ schema. ]sequence
[ { INCREMENT BY | START WITH } integer
| { MAXVALUE integer | NOMAXVALUE }
| { MINVALUE integer | NOMINVALUE }
| { CYCLE | NOCYCLE }
| { CACHE integer | NOCACHE }
| { ORDER | NOORDER }
| { SCALE {EXTEND | NOEXTEND} | NOSCALE}
]

Scalable Sequences测试

[oracle@xifenfei ~]$ sqlplus / as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on Tue Jan 23 02:33:49 2018

Copyright (c) 1982, 2016, Oracle.  All rights reserved.


Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production


BANNER                                                                               CON_ID
-------------------------------------------------------------------------------- ----------
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production              0
PL/SQL Release 12.2.0.1.0 - Production                                                    0
CORE    12.2.0.1.0      Production                                                        0
TNS for Linux: Version 12.2.0.1.0 - Production                                            0
NLSRTL Version 12.2.0.1.0 - Production                                                    0


SQL> set line 150 
SQL> col "Paramete"  for a30 
SQL> col "Session Value"  for a20
SQL> col "Instance Value" for a20
SQL> col "Is Default?" for a20
SQL> SELECT a.ksppinm "Parameter", b.ksppstvl "Session Value", c.ksppstvl "Instance Value",c.ksppstdf "Default?" 
  2  FROM x$ksppi a, x$ksppcv b, x$ksppsv c WHERE a.indx = b.indx  AND a.indx = c.indx  AND lower(a.ksppinm) 
  3  in ('_kqdsn_instance_digits', '_kqdsn_cpu_digits');

Parameter                                                Session Value        Instance Value       Default?
-------------------------------------------------------- -------------------- -------------------- ---------
_kqdsn_instance_digits                                   2                    2                    TRUE
_kqdsn_cpu_digits                                        3                    3                    TRUE

SQL> select instance_number from v$instance;

INSTANCE_NUMBER
---------------
              1

SQL> select sys_context('userenv','sid') from dual;

SYS_CONTEXT('USERENV','SID')
----------------------------------------------------------------------------
275


SQL> create sequence seq_xff start with 1 increment by 1 minvalue 1 maxvalue 100 scale extend;

Sequence created.

SQL> select seq_xff.nextval from dual;

   NEXTVAL
----------
 101275001

验证效果

--另外一个会话
[oracle@xifenfei ~]$ sqlplus / as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on Tue Jan 23 02:45:14 2018

Copyright (c) 1982, 2016, Oracle.  All rights reserved.


Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

SQL> select sys_context('userenv','sid') from dual;

SYS_CONTEXT('USERENV','SID')
---------------------------------------------------------
4

SQL> select instance_number from v$instance;

INSTANCE_NUMBER
---------------
              1

SQL> select seq_xff.nextval from dual;

   NEXTVAL
----------
 101004002

这里可以看出来seq的值是固定长度的.而且随着sid或者inst_id 不同而不同,从而实现减少大量数据集中在一个block而引起的各种争用
测试scale noextend

SQL> create sequence seq_xifenfei start with 1 increment by 1 minvalue 1 maxvalue 100 scale noextend;

Sequence created.

SQL> select seq_xifenfei.nextval from dual;
select seq_xifenfei.nextval from dual
       *
ERROR at line 1:
ORA-64603: NEXTVAL cannot be instantiated for seq_xifenfei. Widen the sequence
by 4 digits or alter sequence with SCALE EXTEND.


SQL> drop sequence seq_xifenfei;

Sequence dropped.

SQL> create sequence seq_xifenfei start with 1 increment by 1 minvalue 1 maxvalue 1000000 scale noextend;

Sequence created.

SQL>  select seq_xifenfei.nextval from dual;

   NEXTVAL
----------
   1010041

这里可以看出来scale noextend的长度是随着seq值的改变而改变,而且max值不能小于seq本身长度.

发表在 ORACLE 12C, ORACLE 18C | 标签为 , | 评论关闭

ORA-20001: Latest xml inventory is not loaded into table

12.2数据库启动报ORA-20001错

2018-01-22T04:51:19.574350-05:00
Unable to obtain current patch information due to error: 20001, 
  ORA-20001: Latest xml inventory is not loaded into table
ORA-06512: at "SYS.DBMS_QOPATCH", line 777
ORA-06512: at "SYS.DBMS_QOPATCH", line 864
ORA-06512: at "SYS.DBMS_QOPATCH", line 2222
ORA-06512: at "SYS.DBMS_QOPATCH", line 740
ORA-06512: at "SYS.DBMS_QOPATCH", line 2247

===========================================================
Dumping current patch information
===========================================================
Unable to obtain current patch information due to error: 20001
===========================================================

patch日志

KUP-05007:   Warning: Intra source concurrency disabled because the preprocessor option is being used.

Field Definitions for table OPATCH_XML_INV
  Record format DELIMITED BY NEWLINE
  Data in file has same endianness as the platform
  Reject rows with all null fields

  Fields in Data Source:

    XML_INVENTORY                   CHAR (100000000)
      Terminated by "UIJSVTBOEIZBEFFQBL"
      Trim whitespace same as SQL Loader

DBMS_QOPATCH包是提供PLSQL/SQL接口方式访问数据库补丁安装情况,oracle在启动的时候会去调用这个包查询patch情况,由于ORA-20001 Latest XML inventory is not loaded into table.导致失败,从而出现上述启动错误。
主要相关的sql

CREATE OR REPLACE DIRECTORY "OPATCH_LOG_DIR" AS '/u01/app/oracle/product/11.2.0/dbhome_1/QOpatch';
CREATE OR REPLACE DIRECTORY "OPATCH_SCRIPT_DIR" AS '/u01/app/oracle/product/11.2.0/dbhome_1/QOpatch';

CREATE TABLE "SYS"."OPATCH_XML_INV"
   (    "XML_INVENTORY" CLOB
   )
   ORGANIZATION EXTERNAL
    ( TYPE ORACLE_LOADER
      DEFAULT DIRECTORY "OPATCH_SCRIPT_DIR"
      ACCESS PARAMETERS
      ( RECORDS DELIMITED BY NEWLINE CHARACTERSET UTF8
      DISABLE_DIRECTORY_LINK_CHECK
      READSIZE 8388608
      preprocessor opatch_script_dir:'qopiprep.bat'
      BADFILE opatch_script_dir:'qopatch_bad.bad'
      LOGFILE opatch_log_dir:'qopatch_log.log'
      FIELDS TERMINATED BY 'UIJSVTBOEIZBEFFQBL'
      MISSING FIELD VALUES ARE NULL
      REJECT ROWS WITH ALL NULL FIELDS
      (
        xml_inventory    CHAR(100000000)
      )
        )
      LOCATION
       ( "OPATCH_SCRIPT_DIR":'qopiprep.bat'
       )
    )
   REJECT LIMIT UNLIMITED;

解决方案

---方案1
SQL> alter system set "_disable_directory_link_check"=true scope=spfile;

System altered.

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area 2432696320 bytes
Fixed Size                  8795664 bytes
Variable Size             654313968 bytes
Database Buffers         1761607680 bytes
Redo Buffers                7979008 bytes
Database mounted.
Database opened.
SQL> SELECT a.ksppinm "Parameter",
  2  b.ksppstvl "Session Value",
  3  c.ksppstvl "Instance Value"
  4  FROM x$ksppi a,
  5  x$ksppcv b,
  6  x$ksppsv c
  7  WHERE a.indx = b.indx
  8  AND a.indx = c.indx
  9  AND a.ksppinm LIKE '/_disable_direc%' escape '/' ;

Parameter
--------------------------------------------------------------------------------
Session Value
--------------------------------------------------------------------------------
Instance Value
--------------------------------------------------------------------------------
_disable_directory_link_check
TRUE
TRUE


SQL> exit

$ORACLE_HOME/sqlpatch/datapatch

---方案2
SQL> create or replace directory OPATCH_LOG_DIR as '<complete path to QOpatch>';
Directory created.

SQL> create or replace directory OPATCH_SCRIPT_DIR as '<complete path to QOpatch>';
Directory created.

---方案3
cd $ORACLE_HOME/sqlpatch

./datapatch -verbose

---方案4
chmod 775 -r $ORACLE_HOME/rdbms/log
 

$ORACLE_HOME/OPatch/datapatch -prereq 

参考MOS
Queryable Patch Inventory – Issues/Solutions for ORA-20001: Latest xml inventory is not loaded into table (Doc ID 1602089.1)
12.2:DB Alert.log shows ORA-20001: Latest xml inventory,ORA-06512: at “SYS.DBMS_QOPATCH”,”KUP-04004: error while reading file” (Doc ID 2323937.1)

发表在 ORACLE 12C | 标签为 , | 评论关闭