Categotry Archives: 安装

在线打补丁 opatch apply online

0

Posted on by

All OPatch versions after 11.1.0.6 are Online Patch aware.

The syntax to install an Online Patch is:

opatch apply online -connectString <SID>:<USERNAME>:<PASSWORD>:<NODE>


example:

$ opatch apply online -connectString db11202:sys:oracle -invPtrLoc /u01/app/oracle/product/11.2.0/dbhome_1/oraInst.loc
Invoking OPatch 11.2.0.1.4

Oracle Interim Patch Installer version 11.2.0.1.4
Copyright (c) 2010, Oracle Corporation. All rights reserved.

Oracle Home : /u01/app/oracle/product/11.2.0/dbhome_1
Central Inventory : /u01/app/oraInventory
from : /u01/app/oracle/product/11.2.0/dbhome_1/oraInst.loc
OPatch version : 11.2.0.1.4
OUI version : 11.2.0.2.0
OUI location : /u01/app/oracle/product/11.2.0/dbhome_1/oui
Log file location : /u01/app/oracle/product/11.2.0/dbhome_1/cfgtoollogs/opatch/opatch2011-01-27_16-14-21PM.log

Patch history file: /u01/app/oracle/product/11.2.0/dbhome_1/cfgtoollogs/opatch/opatch_history.txt

The patch should be applied/rolled back in '-all_nodes' mode only.
Converting the RAC mode to '-all_nodes' mode.
ApplySession applying interim patch '10188727' to OH '/u01/app/oracle/product/11.2.0/dbhome_1'

Running prerequisite checks…
Provide your email address to be informed of security issues, install and
initiate Oracle Configuration Manager. Easier for you if you use your My
Oracle Support Email address/User Name.
Visit http://www.oracle.com/support/policies.html for details.
Email address/User Name: 

You have not provided an email address for notification of security issues.
Do you wish to remain uninformed of security issues ([Y]es, [N]o) [N]: y

OPatch detected non-cluster Oracle Home from the inventory and will patch the local system only.

Backing up files and inventory (not for auto-rollback) for the Oracle Home
Backing up files affected by the patch '10188727' for restore. This might take a while…
Backing up files affected by the patch '10188727' for rollback. This might take a while…

Patching component oracle.rdbms, 11.2.0.2.0…
The patch will be installed on active database instances.
Installing and enabling the online patch 'bug10188727.pch', on database 'db11202'.

ApplySession adding interim patch '10188727' to inventory

Verifying the update…
Inventory check OK: Patch ID 10188727 is registered in Oracle Home inventory with proper meta-data.
Files check OK: Files from Patch ID 10188727 are present in Oracle Home.

OPatch succeeded.

For RAC you can list all of the instances:

opatch apply online -connectString <SID>:<USERNAME>:<PASSWORD>:<NODE1>,<SID2>:<USERNAME>:<PASSWORD>:<NODE2>,…

The USERNAME and PASSWORD are those of a user that has SYSDBA privileges. 
The USERNAME and PASSWORD can be left blank if the OS user applying the patch has the SYSDBA privilege. 
To apply a patch only to local in the RAC environment, it is necessary to use '-local'.

 

 

 

参考:RDBMS Online Patching Aka Hot Patching (文档 ID 761111.1)

Using WGET to download My Oracle Support Patches

0

Posted on by

 oracle的补丁可以在linux上通过wget方式来下载,避免了下载到windows在拉到服务器上的繁琐过程。

可以直接在linux下新建一个可执行文件:

	username=dba@oradbca.com

	password=xxxxxxxxx
wget -c --secure-protocol=TLSv1 --http-user=$username --http-password=$password --no-check-certificate --output-document=p13390677_112040_Linux-x86-64_1of7.zip "https://updates.oracle.com/Orion/Services/download/p13390677_112040_Linux-x86-64_1of7.zip?aru=16716375&patch_file=p13390677_112040_Linux-x86-64_1of7.zip" -o p13390677_112040_Linux-x86-64_1of7.log

 

执行这个文件,然后就可以看p13390677_112040_Linux-x86-64_1of7.log 这个文件的下载进度了。

XDB is INVALID in DBA_REGISTRY after Fresh Installation

0

Posted on by

  在新安装XDB后,一些对象为失效

SQL> select comp_name, version, status from dba_registry;

COMP_NAME                          VERSION                 STATUS
---------------------------------- ----------------------- ----------
Oracle XML Database                11.2.0.2.0              INVALID
Oracle Database Catalog Views      11.2.0.2.0              VALID
Oracle Database Packages and Types 11.2.0.2.0              VALID
JServer JAVA Virtual Machine       11.2.0.2.0              VALID
Oracle XDK                         11.2.0.2.0              VALID
Oracle Database Java Packages      11.2.0.2.0              VALID

SQL> select object_type,object_name, owner from dba_objects
 where status ='INVALID' and
 owner in ('SYS','SYSTEM','XDB')
 order by owner, object_name, object_type;

OBJECT_TYPE         OBJECT_NAME                    OWNER
------------------- ------------------------------ --------
PACKAGE BODY        DBMS_METADATA                  SYS
PACKAGE BODY        DBMS_NETWORK_ACL_ADMIN         SYS
PACKAGE BODY        DBMS_XS_PRINCIPAL_EVENTS_INT   SYS
PACKAGE BODY        XS$CATVIEW_UTIL                SYS
PACKAGE BODY        DBMS_CSX_INT                   XDB
PACKAGE BODY        DBMS_RESCONFIG                 XDB
PACKAGE BODY        DBMS_XDB                       XDB
PACKAGE BODY        DBMS_XDBRESOURCE               XDB
PACKAGE BODY        DBMS_XDBUTIL_INT               XDB
PACKAGE BODY        DBMS_XDBZ0                     XDB
PACKAGE BODY        DBMS_XMLDOM                    XDB
PACKAGE BODY        DBMS_XMLPARSER                 XDB
PACKAGE BODY        DBMS_XMLSCHEMA                 XDB
PACKAGE BODY        DBMS_XSLPROCESSOR              XDB
PACKAGE BODY        XIMETADATA_PKG                 XDB

15 rows selected.

SQL> select owner, schema_url
 from dba_xml_schemas
 order by 1,2;

OWNER    SCHEMA_URL
-------- ----------------------------------------------------------
XDB      http://xmlns.oracle.com/xdb/XDBResource.xsd
XDB      http://xmlns.oracle.com/xdb/XDBSchema.xsd

2 rows selected.

SQL> select any_path from resource_view;
ERROR:
ORA-01002: fetch out of sequence

 

MOS:

SOLUTION

To implement the solution, please execute the following steps:

– Disable "password complexity verification" feature
 

SQL> connect / as sysdba

 

drop function verify_function;
ALTER PROFILE DEFAULT LIMIT PASSWORD_VERIFY_FUNCTION null;


– Grant execute permissions on DBMS_SQL, DBMS_LOB, UTL_FILE to PUBLIC or XDB user
 

SQL> connect / as sysdba

 

grant execute on dbms_sql to xdb;
grant execute on dbms_lob to xdb;
grant execute on utl_file to xdb;


– Reload (using xdbrelod.sql) or Reinstall (deinstall and install) XML DB following the steps from Note 1292089.1
– Finally, if required, run utlrp.sql:
 

SQL> @?/rdbms/admin/utlrp.sql


XML DB and all SYS objects are valid now:
 

SQL> select comp_name, version, status from dba_registry;

 

COMP_NAME                          VERSION                 STATUS
———————————- ———————– ———-
Oracle XML Database                11.2.0.2.0              VALID
Oracle Database Catalog Views      11.2.0.2.0              VALID
Oracle Database Packages and Types 11.2.0.2.0              VALID
JServer JAVA Virtual Machine       11.2.0.2.0              VALID
Oracle XDK                         11.2.0.2.0              VALID
Oracle Database Java Packages      11.2.0.2.0              VALID

SQL> select object_type,object_name, owner from dba_objects
where status ='INVALID' and
owner in ('SYS','SYSTEM','XDB')
order by owner, object_name, object_type;

no rows selected.

 

删除XDB组件导致的对象失效

0

Posted on by

 在删除XDB的时候,导致一些sys一些对象失效:

版本为:11.2.0.1 to 11.2.0.2

SQL> select COMP_ID, COMP_NAME, VERSION, STATUS from dba_registry;

COMP_ID COMP_NAME                            VERSION      STATUS
------- ------------------------------------ ------------ --------
CONTEXT Oracle Text                          11.2.0.2.0   VALID
EXF     Oracle Expression Filter             11.2.0.2.0   VALID
OWM     Oracle Workspace Manager             11.2.0.2.0   VALID
CATALOG Oracle Database Catalog Views        11.2.0.2.0   INVALID
CATPROC Oracle Database Packages and Types   11.2.0.2.0   INVALID
RAC     Oracle Real Application Clusters     11.2.0.2.0   VALID
JAVAVM  JServer JAVA Virtual Machine         11.2.0.2.0   VALID
XML     Oracle XDK                           11.2.0.2.0   VALID
CATJAVA Oracle Database Java Packages        11.2.0.2.0   VALID

9 rows selected.

SQL> select OWNER, OBJECT_NAME, OBJECT_TYPE from dba_objects where status = 'INVALID';

OWNER    OBJECT_NAME                     OBJECT_TYPE 
-------- ------------------------------- ------------------
SYS      ALL_XML_SCHEMAS                 VIEW
SYS      ALL_XML_SCHEMAS2                VIEW
SYS      KU$_XMLSCHEMA_VIEW              VIEW
SYS      KU$_EXP_XMLSCHEMA_VIEW          VIEW
SYS      KU$_XMLSCHEMA_ELMT_VIEW         VIEW
SYS      KU$_OPQTYPE_VIEW                VIEW
SYS      KU$_TABLE_XMLSCHEMA_VIEW        VIEW
SYS      KU$_COLUMN_VIEW                 VIEW
SYS      KU$_PCOLUMN_VIEW                VIEW
SYS      KU$_NT_PARENT_VIEW              VIEW
SYS      KU$_FHTABLE_VIEW                VIEW
SYS      KU$_10_2_FHTABLE_VIEW           VIEW
SYS      KU$_10_1_FHTABLE_VIEW           VIEW
SYS      KU$_PFHTABLE_VIEW               VIEW
SYS      KU$_10_1_PFHTABLE_VIEW          VIEW
SYS      KU$_ACPTABLE_VIEW               VIEW
SYS      KU$_IOTABLE_VIEW                VIEW
SYS      KU$_10_1_IOTABLE_VIEW           VIEW
SYS      KU$_PIOTABLE_VIEW               VIEW
SYS      KU$_10_1_PIOTABLE_VIEW          VIEW
SYS      KU$_XMLSCHEMA_TYPES_VIEW        VIEW
SYS      KU$_TTS_TYPES_VIEW              VIEW
SYS      KU$_VIEW_VIEW                   VIEW
SYS      KU$_M_VIEW_FH_VIEW              VIEW
SYS      KU$_M_VIEW_PFH_VIEW             VIEW
SYS      KU$_M_VIEW_IOT_VIEW             VIEW
SYS      KU$_M_VIEW_PIOT_VIEW            VIEW
SYS      KU$_M_VIEW_LOG_FH_VIEW          VIEW
SYS      KU$_M_VIEW_LOG_PFH_VIEW         VIEW
SYS      KU$_CLUSTER_VIEW                VIEW
SYS      DBMS_METADATA                   PACKAGE BODY
SYS      DBMS_METADATA_INT               PACKAGE BODY
SYS      DBMS_METADATA_UTIL              PACKAGE BODY

33 rows selected.

 

需要手动重新运行一些脚本,重建对象

SQL> connect / as sysdba

-- Make XDB Dummy views
start ?/rdbms/admin/catxdbdv.sql

-- update Data Pump related objects and KU$_ views

start ?/rdbms/admin/dbmsmeta.sql
start ?/rdbms/admin/dbmsmeti.sql
start ?/rdbms/admin/dbmsmetu.sql
start ?/rdbms/admin/dbmsmetb.sql
start ?/rdbms/admin/dbmsmetd.sql
start ?/rdbms/admin/dbmsmet2.sql
start ?/rdbms/admin/catmeta.sql
start ?/rdbms/admin/prvtmeta.plb
start ?/rdbms/admin/prvtmeti.plb
start ?/rdbms/admin/prvtmetu.plb
start ?/rdbms/admin/prvtmetb.plb
start ?/rdbms/admin/prvtmetd.plb
start ?/rdbms/admin/prvtmet2.plb
start ?/rdbms/admin/catmet2.sql

REM Check to verify that all components are valid
select COMP_ID, COMP_NAME, VERSION, STATUS from dba_registry;

COMP_ID COMP_NAME                            VERSION      STATUS
------- ------------------------------------ ------------ --------
CONTEXT Oracle Text                          11.2.0.2.0   VALID
EXF     Oracle Expression Filter             11.2.0.2.0   VALID
OWM     Oracle Workspace Manager             11.2.0.2.0   VALID
CATALOG Oracle Database Catalog Views        11.2.0.2.0   VALID
CATPROC Oracle Database Packages and Types   11.2.0.2.0   VALID
RAC     Oracle Real Application Clusters     11.2.0.2.0   VALID
JAVAVM  JServer JAVA Virtual Machine         11.2.0.2.0   VALID
XML     Oracle XDK                           11.2.0.2.0   VALID
CATJAVA Oracle Database Java Packages        11.2.0.2.0   VALID

9 rows selected.

select count(*) from dba_objects where status = 'INVALID'; -- no rows

  COUNT(*)
---------
        0

 

 

11.2 XDB Removal and Reinstall

0

Posted on by

  前段时间一个数据库发现没有安装XDB组件,查了下mos:

注意:在安装前和删除前必须重启数据库。

XDB Removal

The catnoqm.sql script drops XDB.

spool xdb_removal.log
set echo on;
connect / as sysdba
shutdown immediate;
startup
@?/rdbms/admin/catnoqm.sql
spool off;

Some XDB related objects in the SYS schema are not dropped during the removal of XDB.  Also, the SYS.KU$_% views will become invalid.  Please see the following document for cleaning up these objects:

(Doc ID 1269470.1) XDB Deinstallation script catnoqm.sql leads to Invalid SYS Objects 

可参考:删除XDB组件导致的对象失效 

 

XDB Installation

The catqm.sql script requires the following parameters be passed to it when run:

A. XDB user password

B. XDB user default tablespace

   * The SYSTEM, UNDO and TEMP tablespace cannot be specified.

   * The specified tablespace must already exist prior to running the script.

   * A tablespace other than SYSAUX should be specified, especially if you expect Oracle XML DB Repository to contain a large amount of data.

   * For example:

     create tablespace XDB
     datafile 'xxxxxxxxx.dbf' size 2000M
     extent management local uniform size 256K segment space management auto;

C. XDB user temporary tablespace

D. YES or NO

   * If YES is specified, the XDB repository will use SecureFile storage.

   * If NO is specified, LOBS will be used.

   * To use SecureFiles, compatibility must be set to 11.2.

   * The tablespace specified for the XDB repository must be using Automatic Segment Space Management (ASSM) for SecureFiles to be used.

 

The syntax to run catqm.sql is the following:
SQL> catqm.sql A B C D

For Example:
SQL> @?/rdbms/admin/catqm.sql xdb XDB TEMP YES

## IMPORTANT: You must shutdown and restart the database between removal and reinstall ##

 

spool xdb_install.log
set echo on;
connect / as sysdba
shutdown immediate;
startup;
@?/rdbms/admin/catqm.sql <XDB pwd> <XDB default tbs> <XDB temporary tbs> <YES or NO> — substitute the parameters with appropriate values
@?/rdbms/admin/utlrp.sql
spool off

其中在11.2版本后,默认的表空间segment_space_management为AUTO

参考:XDB is INVALID after ORA-31084 ORA-43853 errors during install   

 

在安装XDB后,有一些包为失效状态:

      sys.dbms_network_acl_admin, sys.dbms_xs_principal_events_int, sys.xs$catview_util, xdb.dbms_resconfig

可参考: XDB is INVALID in DBA_REGISTRY after Fresh Installation

 

参考:Master Note for Oracle XML Database (XDB) Install / Deinstall (文档 ID 1292089.1)