删除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)

Installing and Using Standby Statspack in 11g

0

Posted on by

11g active dataguard 生成的awr信息是主库的awr,而不是dataguard的awr信息,不能生成备库的awr,但是可以生产statspack。

1. Standby Statspack Usage

1.1 Statspack Installation 

The perfstat schema and statspack related objects must be present on the primary and standby prior to installing standby statspack. 

If the perfstat user does not exist in the primary site then you will need to create the perfstat schema using the following:

sqlplus / as sysdba
SQL> @?/rdbms/admin/spcreate.sql

 

The standby statspack installation script (sbcreate.sql) creates the standby statspack schema to hold the standby snapshots. The script asks for:

1.  A password for stdbyperf user

2.  Default tablespace

3.  Temporary tablespace

The script creates the ‘stdbyperf’ user and assigns it the selected default and temporary table space. After the user has been created it calls sbaddins.sql to add the first standby instance to the configuration.
Example:

SQL> @?/rdbms/admin/sbcreate

Note: Due to Bug 11899453, ensure global_names is set to 'FALSE' on the Standby Database while you run this Script. See Note 11899453.8 for some more Details.

 

1.2 Add an Instance to Statspack Configuration

Log in to the primary as the 'stdbyperf' user and run the script sbaddins.sql to add a standby instance to the configuration. The script asks for:

1.  The TNS alias of the standby database instance

2.  The password of the perfstat user on the standby site

The script then creates a private database link to the perfstat schema on the standby site and a separate gathering package for each standby instance. Example:

SQL> connect stdbyperf/your_password 
SQL> @?/rdbms/admin/sbaddins
Input inst2_alias as the tns alias.

 

1.3 Collect Performance Data from a Standby Instance

The script sbaddins.sql creates a separate PL SQL package for each standby instance. Run the snap procedure of those packages to collect the performance data. The statspack_<instance_name>.snap procedure accesses the data dictionary and stats$ views on the standby database via database link connected to the original perfstat user und stores the data to stats$ tables on the primary instance. For example, while the standby is opened read only, login to the primary database and create the snap:

SQL> connect stdbyperf/your_password
SQL> exec statspack_<db_unique_name>_<instance_name>.snap

 

1.4 Generate Standby Statistics Report

The script sbreport.sql generates the standby statistics report. The script asks for: database id, instance number, high and low snapshots id to create the report. Example:

SQL>@?/rdbms/admin/sbreport

 

1.5 Purge a Set of Snapshots

The script sbpurge.sql purges a set of snapshots. The script asks for database id, instance number, low and high snapshots ids. The script purges all snapshots between the low and high snapshot ids for the given instance. Example:

SQL>@?/rdbms/admin/sbpurge

 

1.6 Delete an Instance from the Configuration

The script sbdelins.sql deletes an instance from the configuration, and deletes the associated PL SQL package. The scripts asks for instance name. The snapshots are not automatically purged when the instance is deleted. After deleting the instance, you are not able to generate reports for that instance. Example:

SQL> @?/rdbms/admin/sbdelins

 

1.7 Drop Statspack Schema

The script sbdrop.sql drops the stdbyperf user and tables. The script must be run when connected to SYS (or internal). Example:

SQL> connect / as sysdba
SQL> @?/rdbms/admin/sbdrop

 

 

2. New Statistics Collected

Two new sections are added to standby statspack report: (1) Recovery Progress Stats, and (2) Managed Standby Stats. An example is provided below.

Recovery Progress Stats DB/Inst: MADISON/madison1 End Snap: 2
-> End Snapshot Time: 20-Jun-07 13:59:29
-> ordered by Item, Recovery Start Time desc
Recovery Start Time Item Sofar Units Redo Timestamp
——————- —————– ————– ——- ——————
08-Jun-07 11:58:15 Active Apply Rate 8,420 KB/sec
08-Jun-07 11:58:15 Active Time 4,291 Seconds
08-Jun-07 11:58:15 Apply Time per Lo 267 Seconds
08-Jun-07 11:58:15 Average Apply Rat 3 KB/sec
08-Jun-07 11:57:15 Average Apply Rat 955 KB/sec
08-Jun-07 11:58:15 Checkpoint Time p 0 Seconds
08-Jun-07 11:58:15 Elapsed Time 1,044,073 Seconds
08-Jun-07 11:57:15 Elapsed Time 6 Seconds
08-Jun-07 11:58:15 Last Applied Redo 15,273,580 SCN+Tim 20-Jun-07 13:59:29
08-Jun-07 11:57:15 Last Applied Redo 13,945,701 SCN+Tim 08-Jun-07 11:56:16
08-Jun-07 11:58:15 Log Files 16 Files
08-Jun-07 11:57:15 Log Files 33 Files
08-Jun-07 11:58:15 Redo Applied 3,181 Megabyt
08-Jun-07 11:57:15 Redo Applied 6 Megabyt
————————————————————-

Managed Standby Stats DB/Inst: MADISON/madison1 End Snap: 2
-> End Snapshot Time: 20-Jun-07 13:59:29
-> ordered by Process
Process pid Status Resetlog Id Thread Seq Block Num
———– ———- ———— ———– —— ——- ———–
Client Proc Client pid Blocks Delay(mins)
———– ———- ————– ————–
ARCH 29360 CLOSING 624693241 2 59 18433
ARCH 29360 340 0
ARCH 29358 CLOSING 624693241 1 57 94209
ARCH 29358 1,596 0
ARCH 29356 CLOSING 624693241 1 56 903169
ARCH 29356 1,835 0
ARCH 29354 CLOSING 624693241 2 61 919553
ARCH 29354 770 0
MRP0 30839 APPLYING_LOG 624693241 2 62 57
N/A N/A 2,097,152 0
RFS 28886 IDLE 0 0 0 0
N/A 16388 0 0
RFS 28875 IDLE 624693241 2 62 2164
LGWR 16137 432 0
RFS 30192 IDLE 624693241 1 58 51
LGWR 2092 1 0
RFS 28892 IDLE 0 0 0 0
UNKNOWN 16384 0 0
RFS 30326 IDLE 0 0 0 0
N/A 2268 0 0
————————————————————-

 

3. List of New Scripts and Short Descriptions

All those Scripts below are located in $ORACLE_HOME/rdbms/admin

sbcreate.sql – Install standby statspack
sbcusr.sql – Called from sbcreate.sql to create the schema
sbctab.sql – Called from sbcreate.sql to create tables holding snapshots
sbaddins.sql – Called from sbcreate.sql to add a standby database instance to the configuration.

sbaddins.sql – Add a standby database instance to the configuration
sbcpkg.sql – Called from sbaddins.sql to create the instance specific statspack package

sblisins.sql – List instances in the standby statspack configuration

sbreport.sql – Create a standby statistics report
sbrepcon.sql – Called from sbreport.sql to get the report configuration
sbrepins.sql – Called from sbreport.sql to create the actual report

sbpurge.sql – Purge a set of snapshots identified by low and high snapshot ids

sbdelins.sql – Delete an instance from the standby statspack configuration

sbdrop.sql – Drop the stdbyoperf user and tables of the standby statspack
sbdtab.sql – Called from sbdrop.sql to drop tables
sbdusr.sql – Called from sbdrop.sql to drop user, must run from an account that connects to internal (SYS)

 

摘自:Installing and Using Standby Statspack in 11g (文档 ID 454848.1)

Exclude (Automatic) Statistics Gathering from external objects

0

Posted on by

数据库在自动收集统计信息时,报如下错误:

ORA-20011: Approximate NDV failed:
ORA-29913: error in executing ODCIEXTTABLEOPEN callout
ORA-29400: data cartridge error

检查到是一个外部表收集统计信息的错误

 

管方的建议是:

用DBMS_STATS.LOCK_TABLE_STATS 不让他收集统计信息

或者直接drop table

 

但由于外部表又不能删除,只好

DBMS_STATS.LOCK_TABLE_STATS ('ownname','tabname');

 

有没有lock可以通过这个视图查看

SELECT stattype_locked FROM dba_tab_statistics;

 

在11gr2 Grid 上更改时区

0

Posted on by

grid 11.2.0.2 之后的版本(包含11.2.0.2),时区的配置文件是

 $GRID_HOME/crs/install/s_crsconfig_<nodename>_env.txt 这个文件中的

 

Grid Time Zone Setting

Once OS default timezone is changed, make sure:

1. For 11.2.0.1, shell environment variable TZ is set correctly for grid user and root.
2. For 11.2.0.2 and above, TZ entry in $GRID_HOME/crs/install/s_crsconfig_<nodename>_env.txt sets to correct time zone.

 

 

OS Default Timezone Setting

Linux

To change: /usr/sbin/timeconfig

To display current setting:

cat /etc/sysconfig/clock
ZONE="America/Los_Angeles"
UTC=true
ARC=false

To find out all valid setting: ls -l /usr/share/zoneinfo

Anything that appears in this directory is valid to use, for example, CST6CDT and America/Chicago. 
 
Note: the "Zone" field in /etc/sysconfig/clock could be different than what's in /usr/share/zoneinfo in OL6.3/RHEL6.3, the one from /usr/share/zoneinfo should be used in $GRID_HOME/crs/install/s_crsconfig_<nodename>_env.txt

 

hp-ux 

To display current setting:

cat /etc/default/tz
PST8PDT

To change: set_parms timezone  
 
To find out all valid setting: ls -l /usr/lib/tztab

 

Solaris

To display current setting:

grep TZ /etc/TIMEZONE
TZ=US/Pacific

To change on x64, modify TIMEZONE, also run "rtc -z US/pacific; rtc -c"

To find out all valid settings: ls -l /usr/share/lib/zoneinfo

   

AIX

To display current setting:

grep TZ /etc/environment
TZ=GMT

Refer to the following URL for more about timezone on AIX:

http://publib.boulder.ibm.com/infocenter/pseries/v5r3/index.jsp?topic=/com.ibm.aix.files/doc/aixfiles/environment.htm

 

参考:How To Change Timezone for 11gR2 Grid Infrastructure (文档 ID 1209444.1)