Find password cracker in 11g

2011/07/04 by Leave a Comment

在11g中默认启用了对登录注销操作LOGON/LOGOFF的审计，详见<11g默认审计选项>。利用这一点我们可以很方便地从审计日志中找出数据库中的密码暴力破解者。如以下演示:

C:\Users\Maclean Liu>sqlplus system/try_password@G11R2

SQL*Plus: Release 11.2.0.1.0 Production on Mon Jul 4 21:37:44 2011

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

ERROR:
ORA-01017: invalid username/password; logon denied

select username,userhost,terminal,timestamp,action_name,os_process
  from dba_audit_trail
 where returncode = 1017
 order by timestamp desc;

USERNAME             USERHOST                                 TERMINAL             TIMESTAMP          ACTION_NAME       OS_PROCESS
-------------------- ---------------------------------------- -------------------- ------------------ ----------------  ------------
SYSTEM               WORKGROUP\MACLEANLIU-PC                  MACLEANLIU-PC        04-JUL-11          LOGON             4240:2700

Script:

set linesize 140 pagesize 1400
col os_username for a30
col userhost for a30
col terminal for a30

select os_username,userhost,terminal,username,count(*)
  from dba_audit_trail
 where returncode = 1017
 group by os_username,userhost,username,terminal
 having count(*)>10
 /

注意对于LOGON PER SECOND很高的数据库，如果应用程序配置文件中的数据库用户密码不正确，同时应用在短期内发起大量会话登录数据库的话可能引发频繁的dc_users字典缓存锁，用户登录无法成功，乃至整个实例hang住，该问题具体可见<Row Cache lock Problem>。针对该问题如果是在11g中的话，可以利用以上脚本快速找到因密码不正确登录失败的数据库用户名，从而减少排查时间。

Filed Under: Oracle, Oracle脚本script Tagged With: , , ,

Script:Speed Up Large Index Create or Rebuild

2011/06/30 by 11 Comments

以下脚本可以用于加速大表索引的创建或重建

SQL> select * from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
PL/SQL Release 11.2.0.2.0 - Production
CORE    11.2.0.2.0      Production
TNS for Linux: Version 11.2.0.2.0 - Production
NLSRTL Version 11.2.0.2.0 - Production

SQL> select * from global_name;

GLOBAL_NAME
--------------------------------------------------------------------------------
www.askmac.cn

-- Script Tested above 10g
-- Create a new temporary segment tablespace specifically for creating the index.
-- CREATE TEMPORARY TABLESPACE tempindex tempfile 'filename' SIZE 20G ;
-- ALTER USER username TEMPORARY TABLESPACE tempindex;

REM PARALLEL_EXECUTION_MESSAGE_SIZE can be increased to improve throughput.
REM but need restart instance,and should be same in RAC environment
REM this doesn't make sense,unless high parallel degree

-- alter system set parallel_execution_message_size=65535 scope=spfile;

alter session set workarea_size_policy=MANUAL;
alter session set workarea_size_policy=MANUAL;

alter session set db_file_multiblock_read_count=512;
alter session set db_file_multiblock_read_count=512;

--In conclusion, in order to have the least amount of direct operations and
--have the maximum possible read/write batches these are the parameters to set:

alter session set events '10351 trace name context forever, level 128';

REM set sort_area_size to 700M or 1.6 * table_size
REM 10g bug need to set sort_area_size twice
REM remember large sort area size doesn't mean better performance
REM sometimes you should reduce below setting,and then sort may benefit from disk sort
REM and attention to avoid PGA swap

alter session set sort_area_size=734003200;
alter session set sort_area_size=734003200;

REM set sort area first,and then set SMRC for parallel slave
REM Setting this parameter can activate our previous setting of sort_area_size
REM and we can have large sort multiblock read counts.

alter session set "_sort_multiblock_read_count"=128;
alter session set "_sort_multiblock_read_count"=128;

alter session enable parallel ddl;

create [UNIQUE] index ...     [ONLINE] parallel [Np] nologging;

alter index .. logging;
alter index .. noparallel;

--TRY below underscore parameter while poor performance 

--alter session set "_shrunk_aggs_disable_threshold"=100; 

REM   _newsort_type=2 only works if the patch for bug:4655998 has been applied
REM   The fix for bug:4655998 has been included in the 10.2.0.4 patchset.
REM   got worse in most cases

--alter session set "_newsort_type" = 2; 
OR  
--alter session set "_newsort_enabled"=false;                        then use Sort V1 algorithm,got worse in most cases

rem !!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!IMPORTANT!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
rem If the performance of a query has degraded and the majority of the
rem time is spent in the function kghfrempty, and the function that called
rem kghfrempty was kxsfwa called from kksumc, then you may be encountering
rem this problem.
rem Workaround:
rem Reducing sort_area_size may help by reducing the amount of memory that
rem each sort allocates, particularly if the IO subsystem is underutilized.
rem The performance of some queries that involved large sorts degraded due
rem to the memory allocation pattern used by sort.
rem !!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!

REM setting below parameter only if you are loading data into new system
REM you should restore them after loading
--alter session set db_block_checking=false;
--alter system set db_block_checksum=false;
Filed Under: Oracle, Oracle脚本script Tagged With: ,

Backup Script:Expdp Schema to ASM Storage

2011/06/20 by 2 Comments

Below is a shell script  which backup dmpfile to ASM storage everyday:

#!/bin/bash

#asmcmd mkdir DATA/ASM_DATAPUMP_BACKUP
#create directory ASM_DATAPUMP_BACKUP as '+DATA/ASM_DATAPUMP_BACKUP';
#grant read,write on directory ASM_DATAPUMP_BACKUP to system;
#create directory LOGDIR as '/s01/logdir';
#grant read,write on directory LOGDIR to system;
#create directory DMPBACKUP as '/s01/dmpbackup';
#grant read,write on directory DMPBACKUP to system;

export ORACLE_HOME=/s01/oracle/product/11.2.0/dbhome_1;
export ORA_CRS_HOME=/s01/grid;
export ORACLE_SID=PROD1;
export PATH=$ORACLE_HOME/bin:/s01/grid:/usr/kerberos/bin:/usr/local/bin:/bin:/usr/bin:/home/oracle/bin:/home/oracle/bin:$ORACLE_HOME/OPatch;
thisday=`date "+%Y""%m""%d"`;
expfilename='maclean'$thisday'.dmp';
explogname='maclean'$thisday'.log';
yesterday=`date -d "1 days ago" +%Y%m%d`;
yesterdayfile='maclean'$yesterday'.dmp';
expdp system/system schemas=maclean directory=ASM_DATAPUMP_BACKUP dumpfile=$expfilename logfile=LOGDIR:$explogname;

#TRANSFER FILE FROM ASM TO FILESYSTEM

sqlplus / as sysdba <<EOF
exec DBMS_FILE_TRANSFER.COPY_FILE('ASM_DATAPUMP_BACKUP','$expfilename','DMPBACKUP','$expfilename');
EOF

export ORACLE_SID=+ASM1;
export ORACLE_HOME=/s01/grid;
$ORACLE_HOME/bin/asmcmd rm DATA/ASM_DATAPUMP_BACKUP/$yesterdayfile;
#/usr/bin/find /s01/dmpbackup -mtime 1 -name 'maclean*.dmp'  -exec /bin/rm -rf {} \;
#asmcmd cp DATA/ASM_DATAPUMP_BACKUP/$expfilename /s01/dmpbackup;
Filed Under: Oracle, Oracle数据恢复、数据库恢复、灾难恢复, Oracle脚本script Tagged With: ,

Script:列出失效索引或索引分区

2011/06/09 by 2 Comments

以下脚本可用于列出数据库中的失效的索引、索引分区、子分区:


REM list of the unusable index,index partition,index subpartition in Database 

Select owner, index_name, status
  From dba_indexes
 where status = 'UNUSABLE'
   and owner not in ('SYS','SYSTEM',
                     'SYSMAN',
                     'EXFSYS',
                     'WMSYS',
                     'OLAPSYS',
                     'OUTLN',
                     'DBSNMP',
                     'ORDSYS',
                     'ORDPLUGINS',
                     'MDSYS',
                     'CTXSYS',
                     'AURORA$ORB$UNAUTHENTICATED',
                     'XDB',
                     'FLOWS_030000',
                     'FLOWS_FILES')
 order by 1, 2 
/

select index_owner, index_name, partition_name
  from dba_ind_partitions
 where status ='UNUSABLE'
   and index_owner not in ('SYS',
                           'SYSTEM',
                           'SYSMAN',
                           'EXFSYS',
                           'WMSYS',
                           'OLAPSYS',
                           'OUTLN',
                           'DBSNMP',
                           'ORDSYS',
                           'ORDPLUGINS',
                           'MDSYS',
                           'CTXSYS',
                           'AURORA$ORB$UNAUTHENTICATED',
                           'XDB',
                           'FLOWS_030000',
                           'FLOWS_FILES') order by 1,2
/

Select
       Index_Owner
     , Index_Name
     , partition_name
     , SUBPARTITION_NAME
 From 
       DBA_IND_SUBPARTITIONS
Where
       status = 'UNUSABLE'  
       and index_owner not in ('SYS',
                           'SYSTEM',
                           'SYSMAN',
                           'EXFSYS',
                           'WMSYS',
                           'OLAPSYS',
                           'OUTLN',
                           'DBSNMP',
                           'ORDSYS',
                           'ORDPLUGINS',
                           'MDSYS',
                           'CTXSYS',
                           'AURORA$ORB$UNAUTHENTICATED',
                           'XDB',
                           'FLOWS_030000',
                           'FLOWS_FILES') order by 1, 2
/
Filed Under: Oracle, Oracle脚本script Tagged With: , ,

Script:列出数据库中5%以上链式行的表

2011/06/03 by 4 Comments

以下脚本用于列出数据库中chained/migrated rows达到5%的表，注意查询结果来源于统计信息，如果数据库长期没有gather_stats则结果不真实:

REM List Tables with > 5 % chained rows and > 500 total rows 

  SELECT owner,
         table_name,
         pct_free,
         ROUND (100 * chain_cnt / num_rows, 0) chain_pct
    FROM sys.dba_all_tables
   WHERE ROUND (100 * chain_cnt / num_rows, 0) > 5
         AND owner NOT IN
                ('SYS',
                 'SYSTEM',
                 'SYSMAN',
                 'EXFSYS',
                 'WMSYS',
                 'OLAPSYS',
                 'OUTLN',
                 'DBSNMP',
                 'ORDSYS',
                 'ORDPLUGINS',
                 'MDSYS',
                 'CTXSYS',
                 'AURORA$ORB$UNAUTHENTICATED',
                 'XDB',
                 'FLOWS_030000',
                 'FLOWS_FILES')
         AND num_rows IS NOT NULL
         AND num_rows > 500
ORDER BY 1, 2
/

REM List Table Partitions with > 5 % chained rows and > 500 total rows 

  SELECT table_owner,
         table_name,
         partition_name,
         pct_free,
         ROUND (100 * chain_cnt / num_rows, 0) chain_pct
    FROM sys.dba_tab_partitions
   WHERE ROUND (100 * chain_cnt / num_rows, 0) > 5
         AND table_owner NOT IN
                ('SYS',
                 'SYSTEM',
                 'SYSMAN',
                 'EXFSYS',
                 'WMSYS',
                 'OLAPSYS',
                 'OUTLN',
                 'DBSNMP',
                 'ORDSYS',
                 'ORDPLUGINS',
                 'MDSYS',
                 'CTXSYS',
                 'AURORA$ORB$UNAUTHENTICATED',
                 'XDB',
                 'FLOWS_030000',
                 'FLOWS_FILES')
         AND num_rows IS NOT NULL
         AND num_rows > 500
ORDER BY 1, 2
/
Filed Under: Oracle, Oracle脚本script Tagged With: ,

了解Oracle Critical Patch Update

2011/06/02 by 2 Comments

Oracle Critical Patch Update是什么？

Critical Patch Update(以下简称CPU)，是Oracle在2005年开始引入的产品安全更新策略。一般来说CPU包含了Oracle产品安全漏洞的修复补丁集(set of security bug fix)。CPU最早的雏形出现在2005年，该项目致力于为客户周期性地提供累积性的补丁以修复安全漏洞。

通常CPU补丁会在每季度开始第一个月的15号发布，按照发布日期的不同可以划分为：

  • January :    CPU JAN
  • April :          CPU APR
  • July :           CPU JUL
  • October :    CPU OCT

存在以下3种类型的CPU补丁：

  • Normal CPU：在10.2.0.2之前所有的CPU均是Normal CPU
  • Molecular CPU：Molecular解释为分子，从10.2.0.3开始以后版本的CPU patches均以Molecular格式发布，之后我们会介绍Normal/Molecular格式的区别
  • CPU Bundle Patch：由于在Windows平台无法利用替换共享库文件后relink的方式来更新Oracle binary，所以Oracle特别针对Windows发布区别于Unix上Normal/Molecular CPU的CPU Bundle patch(也因此Bundle Patch会别较大)。Windows bundle patches通常每一个季度都会发布

接下来我们通过2个实例来了解Normal CPU与Molecular CPU之间的区别。Linux x86平台上的CPUJAN2009 for 9.2.0.8的bug#补丁号为7592365。我们可以通过该补丁号从My Oracle Support上下载到压缩为zip的补丁包，试着将该压缩包解压后我们会发现该CPU补丁包的目录结构类似于一个one-off patch(一次性补丁):

$cd 7592365
$ls
/etc     /files       readme

之前已经介绍过了从10.2.0.3开始以后版本的CPU patches均以Molecular格式发布。我们选取Linux x86平台上的CPUAPR2009 for 10.2.0.4为Molecular CPU的示例，下载并解压该CPU后会发现补丁包目录下有不少以Patch number为名的子目录，这就是Molecular-分子式的寓意所在，其实你也可以简单地理解为是对散装的安全补丁打了包：

$cd 8290506
$ls
7155248  7155251  7155254  7375613  7609058  8309592  8309637  cpu_root.sh
7155249  7155252  7197583  7375617  8290506  8309623  8309639  patchmd.xml
7155250  7155253  7375611  7609057  8309587  8309632  8309642  README.html

以上每一个数字代表一个molecules，称作分子补丁
注意！一个molecules可能包含有多个小的fix！！

Normal CPU与Molecular CPU间的差异还表现在所包含的补丁类型上。Normal CPU也被叫做Classic CPU即传统CPU，不同于molecular CPU，Normal CPU不仅包含安全漏洞修复，针对于特定的产品、产品版本及平台还可能包含了非安全的补丁。

而Molecular CPU(在MOS上有时也被叫做New format CPU)从10.2.0.3开始改变了既往Normal CPU的习惯，Molecular CPU仅仅包含安全漏洞补丁(security bug fixes)，这是目前CPU与另一种补丁更新策略Patch Set Update(PSU)间的主要区别之一(PSU在格式上类似于Normal CPU)，CPU专门负责修复安全漏洞，而PSU往往会包含CPU(INCLUDES CPU)。

第一个以Molecular形式发布的是CPU是CPUJUL2007(DB-10.2.0.3-MOLECULE-013-CPUAPR2007):

此外根据Oracle Product lifetime的介绍CPU的发布遵循几个原则：

  1. CPU仅为最新的patchset补丁集发布
  2. 对于之前的patchset补丁集存在一个宽限期，在此宽限期内仍会针对老的patchset发布CPU，关于这个宽限期(grace period)在MOS文档<Database, FMW, EM Grid Control, and OCS Software Error Correction Support Policy [ID 209768.1]>中有详细描述，实际上如Fusion Middleware、Application等Oracle产品的维护保障期也受到该宽限期的影响，以下摘录Database部分的附录:
Grace Period: up to 1 year, minimum 3 months.
You have up to one year from the release of a patch set on the first platform (currently Linux x86) to plan for
and install the new patch set. During that year we will create new bug fixes for the previous patch set.
This grace period is effective with the release of 10.2.0.4.
For example, 10.2.0.4 was released first on Linux x86. The release date was 22 February 2008.
Until 22 February 2009 we will create new fixes for both 10.2.0.3 and 10.2.0.4.
After that date new fixes for 10.2.0.3 will cease on all platforms and we will only create new fixes for 10.2.0.4.
Grace period for current patch sets can be found on Metalink in Note 742060.1

Exceptions:
3 Month minimum grace period: Since the release of a patch set on different platforms happens over time,
not all platforms will be supported for error correction for the full year. Because of this,
we will always support the previous patch set for error correction for at least 3 months.
For example, if the initial release of patchset A.x.y.z is on January 1st on Linux x86 and the same patch set
is released on Univac on November1, Oracle will still provide new patches on Univac A.x.y.z-1 until
the end of January of the next year. Outside of the specific exceptions listed below,
CPUs will NOT be provided beyond the initial 12-month grace period.

Bundle patches for Windows: Oracle releases patches for Windows via periodic patch bundles instead of
interim patches. Patch bundles are released periodically (at least quarterly), and include the security fixes
from that quarter’s Critical Patch Update.

举例来说10R2上的CPUJAN2009发布时有10.2.0.3和10.2.0.4这2个版本的，因为当时10.2.0.3还在宽限期内；而到了CPUAPR2009也就是三个月后，10.2.0.3的宽限期也超过了，所以10GR2上的CPUAPR2009只有10.2.0.4一个版本的了。

在Unix平台上10.2.0.3之前(包含9iR2,10gR1,10.2.0.2)，因为当时是以Normal格式发布的CPU，用户apply CPU时要么不打，要打就必须打上整个CPU，这导致出现补丁冲突(conflict patch)的概率大大提高了。依照当时的support流程，在Oracle发布CPU的4周内用户若发现CPU与现有patch间存在冲突，那么可以提交Service Request让Oracle开发部门去开发出一个超集合并(superset merge)的CPU版本，若用户在超过4周后才提交SR那么会被告知等下一次CPU的发布，Oracle在接到开发合并版本CPU的要求后会在以后的2周内(也就是CPU发布的第六周)发布用户需要的merged cpu。CPUJAN2009发布于2009年1月15日，假设我是一家对数据库安全性要求极其严格的公司，我希望实施该CPUJAN2009以提高自身数据库的安全，那么如果我在1月15日即发现CPUJAN2009与现有补丁存在冲突并通过MOS向oracle报告了该冲突问题，那么Oracle理论上会在2009年的2月28日向我提供相应的超集合并补丁；若我在2月15日才刚刚发现冲突的存在，那么我将不得不等待下一次CPU的发布，在这个假设中是4月15日，也就是2个月之后。

实施Normal CPU的原子性要求给用户和Oracle Support都带来了不小的工作量，为了缓解这种矛盾，Molecular CPU应运而生。

从10.2.0.3开始发布的Molecular CPU在apply时没有如Normal CPU那样强的原子性要求，即我们可以安装Molecular CPU中所包含的一部分安全补丁，而跳过一些存在冲突的安全补丁。此外因为Molecular CPU的特有格式，patch conflict补丁冲突仅可能发生在某个特定的分子补丁(molecule)上，而不会整个补丁包都存在冲突。针对这部分存在冲突的分子补丁(一般来说就是普通的one-off patch)，用户可以随时向Oracle支持部分提出合并patch的请求，这打破了Normal CPU所造成的不便。如上文所述Molecular CPU仅针对最新的补丁集(patchset)或仍处在宽限期(grace period)的补丁集发布。

从理论上讲在实施新的Molecular CPU时，一般不会出现如Normal CPU那样opatch报整个补丁都存在冲突的现象，取而代之冲突会存在于个别molecule分子补丁上。在此情形下用户可以跳过存在冲突的molecule，以便安装剩余的无冲突的安全补丁，并申请对已安装的one-off patch和存在冲突的molecule实施合并。one-off patch merge是Oracle Support日常的客户服务项目，所以不用担心得不到merge patch，当然这仍是在最新补丁集或宽限期的前提下，举例来说如果现在我们去申请10.2.0.3上的patch merge则很可能被Oracle Support以要求升级为由来拒绝。
cpu_molecule

此外我们需要铭记CPU补丁总是累加(cumulative)的，这一点同PSU(Patch Set Update)恰恰不同！新的PSU补丁可能未包含之前发布的PSU补丁内容，而CPU补丁总是包含所有之前的CPU内容。举例来说10.2.0.4.5即10204上的PSU5就没有包含10.2.0.4.4(PSU4)中的所有fix，这要求我们在安装PSU5时以PSU4为基础(Patch Set Update PSU 10.2.0.4.5 is an overlay PSU whose base PSU is 10.2.0.4.4. This patch can only be applied in an Oracle home for which PSU 10.2.0.4.4 has already been installed)；而10.2.0.4上的CPUAPR2011就会包含CPUJAN2011及之前的所有补丁内容。

因为传统CPU与Molecular CPU在格式上的差异，所以它们在apply时的步骤亦不相同。Normal CPU会在apply之前将所有旧的CPU全都回滚掉，以保持自身能被打上。而Molecular CPU则不那么简单粗暴，它只需要apply其所包含的新的molecules分子补丁即可，即如果之前有安装过老的CPU，那么老的cpu补丁是不动的。

同时CPU补丁的内容还会被包含在今后发布的Patch Set或Patch Set Update(PSU)中(CPU molecules in PSU)，注意针对如9.2.0.8这样的最终补丁集，Oracle将不再发布新的Patchset或PSU；10.2.0.5作为10g的最终版本今后将不会再有Patchset发布，但包含了CPU的PSU仍会被发布。

很多朋友都会要问CPU补丁是否是必须要安装的？实际上并没有一个强制要求安装CPU的理由，Oracle仅仅是强烈推荐实施这些补丁以降低潜在的安全风险并降低受到骇客入侵成功的概率。

安装CPU与安装普通的one-off patch或PSU没有太大的区别，同样要使用著名的opatch工具。Normal CPU具有强的原子性要求，所以我们不可能去不完整(partial)的安装一个Normal CPU。而对于10.2.0.3后出现的Molecular CPU则没有这种限制，Molecular CPU总是由一定数量的molecules分子补丁组成，注意实际上每一个molecules还可能包含了一个或多个的小的Fix。虽然我们在没有补丁冲突的情况下，也可以选择仅安装CPU中的一个子集的molecules，但Oracle强烈推荐尽可能安装整个CPU。

我们在安装Normal CPU时使用和安装one-off patch同样简单的”opatch apply”命令。在安装Molecular CPU时的命令要负责一些，在不同需求下可能分为:

1.
安装CPU中所有的molecules

$./opatch napply <patch_location> -skip_subset -skip_duplicate

-skip_subset意为跳过那些已安装补丁的子集(subset patches--patches under  that are subsets of patches
installed in the ORACLE_HOME)

-skip_duplicate，跳过已安装过的molecule(provides the additional benefit of detecting when a molecule
patch has already been applied, as in the case of a previous CPU, and to skip application of it.
This reduces the length of time required to do the n-apply CPU installation and minimizes
the overall change to the Oracle home)

2.
安装CPU中的部分molecules

$ ./opatch napply 8290506 -id 7155248,7155249,7155250 -skip_subset -skip_duplicate

以上意为apply patch 7155248,7155249,7155250 

Invoking OPatch 11.2.0.1.3
Oracle Interim Patch Installer version 11.2.0.1.3
Copyright (c) 2010, Oracle Corporation.  All rights reserved.
UTIL session
Oracle Home       : /s01/db_1
Central Inventory : /s01/oraInventory
   from           : /etc/oraInst.loc
OPatch version    : 11.2.0.1.3
OUI version       : 10.2.0.4.0
OUI location      : /s01/db_1/oui
Log file location : /s01/db_1/cfgtoollogs/opatch/opatch2011-06-02_22-37-02PM.log

Patch history file: /s01/db_1/cfgtoollogs/opatch/opatch_history.txt

Invoking utility "napply"
Checking conflict among patches...
Checking if Oracle Home has components required by patches...
Checking skip_duplicate
Checking skip_subset
Checking conflicts against Oracle Home...
OPatch continues with these patches:   7155250  7155249  7155248  

Do you want to proceed? [y|n]
y
User Responded with: Y

Running prerequisite checks...

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

Please shutdown Oracle instances running out of this ORACLE_HOME on the local system.
(Oracle Home = '/s01/db_1')

Is the local system ready for patching? [y|n]
y
User Responded with: Y
Backing up files affected by the patch 'NApply' for restore. This might take a while...

Applying patch 7155250...

ApplySession applying interim patch '7155250' to OH '/s01/db_1'
Backing up files affected by the patch '7155250' for rollback. This might take a while...

Patching component oracle.rdbms, 10.2.0.4.0...
Updating archive file "/s01/db_1/lib/libserver10.a"  with "lib/libserver10.a/kupp.o"
Copying file to "/s01/db_1/rdbms/admin/prvtbpp.plb"
ApplySession adding interim patch '7155250' to inventory

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

Applying patch 7155249...

ApplySession applying interim patch '7155249' to OH '/s01/db_1'
Backing up files affected by the patch '7155249' for rollback. This might take a while...

Patching component oracle.rdbms, 10.2.0.4.0...
Copying file to "/s01/db_1/rdbms/admin/prvtdefr.plb"
ApplySession adding interim patch '7155249' to inventory

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

Applying patch 7155248...

ApplySession applying interim patch '7155248' to OH '/s01/db_1'
Backing up files affected by the patch '7155248' for rollback. This might take a while...

Patching component oracle.rdbms, 10.2.0.4.0...
Copying file to "/s01/db_1/rdbms/lib/env_rdbms.mk"
ApplySession adding interim patch '7155248' to inventory

Verifying the update...
Inventory check OK: Patch ID 7155248 is registered in Oracle Home inventory with proper meta-data.
Files check OK: Files from Patch ID 7155248 are present in Oracle Home.
Running make for target ioracle
Running make for target iextjob
Running make for target iextjobo
The local system has been patched and can be restarted.
UtilSession: N-Apply done.
OPatch succeeded.

另外我们可以使用opatch lsinventory -bugs_fixed命令列出已安装的CPU/PSU

$ ./opatch lsinventory -bugs_fixed

List of Bugs fixed by Installed Patches:
Bug        Fixed by  Installed at                   Description
            Patch
---        --------  ------------                   -----------
8309642    8309642   Thu Jun 02 22:54:51 CST 2011   DB-10.2.0.4-MOLECULE-018-CPUAPR2009
8309639    8309639   Thu Jun 02 22:54:48 CST 2011   DB-10.2.0.4-MOLECULE-019-CPUAPR2009
8309637    8309637   Thu Jun 02 22:54:45 CST 2011   DB-10.2.0.4-MOLECULE-020-CPUAPR2009
8309632    8309632   Thu Jun 02 22:54:42 CST 2011   DB-10.2.0.4-MOLECULE-017-CPUAPR2009
8309623    8309623   Thu Jun 02 22:54:39 CST 2011   DB-10.2.0.4-MOLECULE-016-CPUAPR2009
8309592    8309592   Thu Jun 02 22:54:35 CST 2011   DB-10.2.0.4-MOLECULE-015-CPUAPR2009
8309587    8309587   Thu Jun 02 22:54:30 CST 2011   DB-10.2.0.4-MOLECULE-014-CPUAPR2009
7150470    8290506   Thu Jun 02 22:54:26 CST 2011   MLR BUG FOR 10.2.0.4 FOR CPUJUL2008
7375644    8290506   Thu Jun 02 22:54:26 CST 2011   MLR BUG FOR 10.2.0.4 FOR CPUOCT2008
7592346    8290506   Thu Jun 02 22:54:26 CST 2011   CPUJAN2009 DATABASE 10.2.0.4
8290506    8290506   Thu Jun 02 22:54:26 CST 2011   CPUAPR2009 DATABASE 10.2.0.4
7609058    7609058   Thu Jun 02 22:54:21 CST 2011   DB-10.2.0.4-MOLECULE-013-CPUJAN2009
7609057    7609057   Thu Jun 02 22:54:17 CST 2011   DB-10.2.0.4-MOLECULE-012-CPUJAN2009
7375617    7375617   Thu Jun 02 22:54:14 CST 2011   DB-10.2.0.4-MOLECULE-0011-CPUOCT2008
7375613    7375613   Thu Jun 02 22:54:11 CST 2011   DB-10.2.0.4-MOLECULE-0010-CPUOCT2008
7375611    7375611   Thu Jun 02 22:54:07 CST 2011   DB-10.2.0.4-MOLECULE-009-CPUOCT2008
7197583    7197583   Thu Jun 02 22:54:03 CST 2011   DB-10.2.0.4-MOLECULE-008-CPUJUL2008
7155254    7155254   Thu Jun 02 22:54:00 CST 2011   DB-10.2.0.4-MOLECULE-007-CPUJUL2008
7155253    7155253   Thu Jun 02 22:53:35 CST 2011   DB-10.2.0.4-MOLECULE-006-CPUJUL2008
7155252    7155252   Thu Jun 02 22:53:13 CST 2011   DB-10.2.0.4-MOLECULE-005-CPUJUL2008
7155251    7155251   Thu Jun 02 22:53:07 CST 2011   DB-10.2.0.4-MOLECULE-004-CPUJUL2008
7155250    7155250   Thu Jun 02 22:53:02 CST 2011   DB-10.2.0.4-MOLECULE-003-CPUJUL2008
7155249    7155249   Thu Jun 02 22:52:58 CST 2011   DB-10.2.0.4-MOLECULE-002-CPUJUL2008
7155248    7155248   Thu Jun 02 22:52:54 CST 2011   DB-10.2.0.4-MOLECULE-001-CPUJUL2008

3.
回滚CPU中的部分molecules

$ ./opatch nrollback  -id 7155248,7155249,7155250 

This will roll back patches 7155248,7155249,7155250 that have been installed under the ORACLE_HOME.
If a patch is not installed, it does not have any impact and roll back skips the patch.

Invoking OPatch 11.2.0.1.3
Oracle Interim Patch Installer version 11.2.0.1.3
Copyright (c) 2010, Oracle Corporation.  All rights reserved.
UTIL session
Oracle Home       : /s01/db_1
Central Inventory : /s01/oraInventory
   from           : /etc/oraInst.loc
OPatch version    : 11.2.0.1.3
OUI version       : 10.2.0.4.0
OUI location      : /s01/db_1/oui
Log file location : /s01/db_1/cfgtoollogs/opatch/opatch2011-06-02_22-41-49PM.log
Patch history file: /s01/db_1/cfgtoollogs/opatch/opatch_history.txt
Invoking utility "nrollback"
Patches will be rolled back in the following order:
   7155248   7155249   7155250

Running prerequisite checks...
The following patch(es) will be rolled back: 7155248  7155249  7155250
OPatch detected non-cluster Oracle Home from the inventory and will patch the local system only.
Please shutdown Oracle instances running out of this ORACLE_HOME on the local system.
(Oracle Home = '/s01/db_1')
Is the local system ready for patching? [y|n]
y
User Responded with: Y
Backing up files affected by the patch 'NRollback' for restore. This might take a while...
Rolling back patch 7155248...
RollbackSession rolling back interim patch '7155248' from OH '/s01/db_1'
Patching component oracle.rdbms, 10.2.0.4.0...
Copying file to "/s01/db_1/rdbms/lib/env_rdbms.mk"
RollbackSession removing interim patch '7155248' from inventory
Rolling back patch 7155249...
RollbackSession rolling back interim patch '7155249' from OH '/s01/db_1'
Patching component oracle.rdbms, 10.2.0.4.0...
Copying file to "/s01/db_1/rdbms/admin/prvtdefr.plb"
RollbackSession removing interim patch '7155249' from inventory
Rolling back patch 7155250...
RollbackSession rolling back interim patch '7155250' from OH '/s01/db_1'
Patching component oracle.rdbms, 10.2.0.4.0...
Updating archive file "/s01/db_1/lib/libserver10.a"  with "lib/libserver10.a/kupp.o"
Copying file to "/s01/db_1/rdbms/admin/prvtbpp.plb"
RollbackSession removing interim patch '7155250' from inventory
Running make for target iextjob
Running make for target iextjobo
Running make for target ioracle
The local system has been patched and can be restarted.
UtilSession: N-Rollback done.
OPatch succeeded.

安装CPU补丁除去以上列出的命令外还可以参考MOS文档<OPatch Utility Guide – 10.2 [ID 554417.1]>和
<Critical Patch Update – Introduction to Database n-Apply CPUs [ID 438314.1]>。

完成以上opatch操作后针对既有的数据库(已经创建在使用的数据库)还需要在数据库级别运行数据字典升级脚本:

SQL> select * from global_name;
GLOBAL_NAME
--------------------------------------------------------------------------------
www.askmac.cn

1.
针对传统的Normal CPU运行

@?/rdbms/admin/catcpu.sql

2.
针对Molecular CPU补丁需要运行
sqlplus /nolog
SQL> CONNECT / AS SYSDBA

@?/rdbms/admin/catbundle cpu apply

cd $ORACLE_HOME/cpu/view_recompile
sqlplus /nolog
SQL> CONNECT / AS SYSDBA

SQL> @recompile_precheck_jan2008cpu.sql
SQL> QUIT

cd $ORACLE_HOME/cpu/view_recompile
sqlplus /nolog
SQL> CONNECT / AS SYSDBA
SQL> SHUTDOWN IMMEDIATE

SQL> STARTUP UPGRADE

SQL> @view_recompile_jan2008cpu.sql
SQL> SHUTDOWN;
SQL> STARTUP;

SQL> @?/rdbms/admin/utlrp

SQL> QUIT

以上字典升级工作的步骤可以从补丁包自带的README.HTML网页中找到，另外你可以参考MOS文档<Introduction To Oracle Database catbundle.sql [ID 605795.1]>。

虽然Oracle宣称其发布的每一个CPU都经过广泛和长时间的测试，但实际Oracle不可能具体到每一个用户的环境中去做测试，所以贸然实施CPU还是可能有一定风险的。Oracle推荐用户在将CPU安装到生产系统之前，首先在自己客制化的环境中充分测试安装CPU所可能带来的影响。

我们可以从Critical Patch Update Advisory上找到Oracle产品相关的安全风险信息，作为是否实施CPU补丁的依据之一。此外随CPU附带的文档将是用户所能找到最为详细的同时也是最有用的安全信息来源。

Reference:

Database, FMW, EM Grid Control, and OCS Software Error Correction Support Policy [ID 209768.1]

<OPatch Utility Guide – 10.2 [ID 554417.1]>

<Critical Patch Update – Introduction to Database n-Apply CPUs [ID 438314.1]>

<Introduction To Oracle Database catbundle.sql [ID 605795.1]>。

http://www.oracle.com/technetwork/topics/security/whatsnew/index.html

PS：如果对PSU有兴趣，可以读一读Kamusis的Notes for Oracle Database PSU/CPU

Filed Under: Oracle, Oracle脚本script Tagged With: , ,

Script:列出没有主键或唯一索引的表

2011/06/02 by Leave a Comment

以下脚本可以用于列出数据库中没有主键的表，已排除了系统schema:

REM List tables with no primary key

SELECT owner, table_name
  FROM dba_tables
 WHERE 1 = 1
       AND owner NOT IN
              ('SYS',
               'SYSTEM',
               'SYSMAN',
               'EXFSYS',
               'WMSYS',
               'OLAPSYS',
               'OUTLN',
               'DBSNMP',
               'ORDSYS',
               'ORDPLUGINS',
               'MDSYS',
               'CTXSYS',
               'AURORA$ORB$UNAUTHENTICATED',
               'XDB',
               'FLOWS_030000',
               'FLOWS_FILES')
MINUS
SELECT owner, table_name
  FROM dba_constraints
 WHERE constraint_type = 'P'
       AND owner NOT IN
              ('SYS',
               'SYSTEM',
               'SYSMAN',
               'EXFSYS',
               'WMSYS',
               'OLAPSYS',
               'OUTLN',
               'DBSNMP',
               'ORDSYS',
               'ORDPLUGINS',
               'MDSYS',
               'CTXSYS',
               'AURORA$ORB$UNAUTHENTICATED',
               'XDB',
               'FLOWS_030000',
               'FLOWS_FILES')
/

以下脚本可以用于列出数据库中没有唯一约束或索引的表，已排除了系统schema:

REM List tables with no unique key or index 

SELECT owner, table_name
  FROM dba_all_tables
 WHERE 1 = 1
       AND owner NOT IN
              ('SYS',
               'SYSTEM',
               'SYSMAN',
               'EXFSYS',
               'WMSYS',
               'OLAPSYS',
               'OUTLN',
               'DBSNMP',
               'ORDSYS',
               'ORDPLUGINS',
               'MDSYS',
               'CTXSYS',
               'AURORA$ORB$UNAUTHENTICATED',
               'XDB',
               'FLOWS_030000',
               'FLOWS_FILES')
MINUS
SELECT owner, table_name
  FROM dba_constraints
 WHERE constraint_type = 'U'
       AND owner NOT IN
              ('SYS',
               'SYSTEM',
               'SYSMAN',
               'EXFSYS',
               'WMSYS',
               'OLAPSYS',
               'OUTLN',
               'DBSNMP',
               'ORDSYS',
               'ORDPLUGINS',
               'MDSYS',
               'CTXSYS',
               'AURORA$ORB$UNAUTHENTICATED',
               'XDB',
               'FLOWS_030000',
               'FLOWS_FILES')
MINUS
SELECT owner, table_name
  FROM dba_indexes
 WHERE uniqueness = 'UNIQUE'
       AND owner NOT IN
              ('SYS',
               'SYSTEM',
               'SYSMAN',
               'EXFSYS',
               'WMSYS',
               'OLAPSYS',
               'OUTLN',
               'DBSNMP',
               'ORDSYS',
               'ORDPLUGINS',
               'MDSYS',
               'CTXSYS',
               'AURORA$ORB$UNAUTHENTICATED',
               'XDB',
               'FLOWS_030000',
               'FLOWS_FILES')
/
Filed Under: Oracle, Oracle脚本script Tagged With: , , , ,

Script:收集ASM诊断信息

2011/05/30 by 4 Comments

收集ASM诊断信息最佳的工具仍是RDA，对于不能使用RDA的环境可以采用如下脚本:

 

spool asm_diag1.txt
set pagesize 1000
set lines 500
col "Group Name"   form a25
col "Disk Name"    form a30
col "State"  form a15
col "Type"   form a7
col "Free GB"   form 9,999
alter session set nls_date_format='DD-MON-YYYY HH24:MI:SS';
select sysdate "Date and Time" from dual;

select * from v$asm_diskgroup order by 1;
select * from v$asm_disk order by 1, 2, 3;
select * from gv$asm_operation order by 1;
select * from v$version where banner like '%Database%' order by 1;
select * from gv$asm_client order by 1;

prompt

prompt ASM Disk Groups
prompt ===============

select group_number  "Group"
,      name          "Group Name"
,      state         "State"
,      type          "Type"
,      total_mb/1024 "Total GB"
,      free_mb/1024  "Free GB"
from   v$asm_diskgroup
/

prompt
prompt ASM Disks
prompt ==============

col "Group"          form 999
col "Disk"           form 999
col "Header"         form a9
col "Mode"           form a8
col "Redundancy"     form a10
col "Failure Group"  form a10
col "Path"           form a19

 select group_number  "Group"
,      disk_number   "Disk"
,      header_status "Header"
,      mode_status   "Mode"
,      state         "State"
,      redundancy    "Redundancy"
,      total_mb      "Total MB"
,      free_mb       "Free MB"
,      name          "Disk Name"
,      failgroup     "Failure Group"
,      path          "Path"
from   v$asm_disk
order by group_number
,        disk_number

/

prompt
prompt Instances currently accessing these diskgroups
prompt ==============================================

select c.group_number  "Group"
,      g.name          "Group Name"
,      c.instance_name "Instance"
from   v$asm_client c
,      v$asm_diskgroup g
where  g.group_number=c.group_number
/

prompt
prompt Report the Percentage of Imbalance in all Mounted Diskgroups
prompt ==============================================
select dfail, count(dfail) from
(
select disk, count(failgroup) as dfail
from x$kfdpartner, v$asm_disk where
number_kfdpartner=disk_number and grp=group_number
group by disk, failgroup
)
group by dfail; 

select g.name as "GROUP", d.name as "DISK", d.failgroup, fcnt, pcnt,
decode(pcnt - fcnt, 0, 'MUST', 'SHOULD') as action from
(select gnum, DISK1, failgroup, count(failgroup) as fcnt from
(select gnum, DISK1
from
(
select d.group_number as gnum, disk as disk1,
count(distinct failgroup) as dfail
from x$kfdpartner, v$asm_disk_stat d where
number_kfdpartner=disk_number and grp=d.group_number
and active_kfdpartner=1
group by d.group_number, disk
), v$asm_disk_stat
where dfail < 3
and disk1=disk_number
and gnum=group_number),
x$kfdpartner, v$asm_disk_stat d where
number_kfdpartner=disk_number and grp=d.group_number and grp=gnum
and disk1=disk
and active_kfdpartner=1
group by gnum, disk1, failgroup),
(select grp, disk, count(disk) as pcnt from x$kfdpartner where
active_kfdpartner=1 group by grp, disk),
v$asm_diskgroup_stat g, v$asm_disk_stat d
where gnum=grp and gnum=g.group_number and gnum=d.group_number and
disk=disk1 and disk=disk_number and
((fcnt = 1 and (pcnt - fcnt) > 3) or ((pcnt - fcnt) = 0))
/

col TYPE form a15
col FILE_NUMBER form 9999 head FILE_NUM
col GROUP_NUMBER form 9999 head GR_NUM
col GB for 9999.99

select GROUP_NUMBER   ,
 FILE_NUMBER          ,
 COMPOUND_INDEX       ,
 INCARNATION          ,
 BLOCK_SIZE           ,
 BLOCKS               ,
 BYTES/1024/1024/1024 GB ,
 TYPE                 ,
 STRIPED              ,
 CREATION_DATE        ,
 MODIFICATION_DATE
from v$asm_file
where TYPE != 'ARCHIVELOG'
/

prompt
prompt free ASM disks and their paths
prompt ===========================
select header_status , mode_status, path from V$asm_disk
where header_status in ('FORMER','CANDIDATE')
/

show parameter asm
show parameter size
show parameter proc
show parameter cluster
show parameter instance_type
show parameter instance_name

show parameter pfile

show sga

spool off

 

Code to be run on the ASM instance. Use file asmdebug.sql

 

set newpage none
set linesize 100
spool /tmp/asmdebug.out
--
-- Get a timestamp
select rpad('>', 10, '>'), to_char(sysdate, 'MON DD HH24:MM:SS') from dual;
--
-- Diskgroup information
set head off
select 'Diskgroup Information' from dual;
set head on
column name format a15
column DG# format 99
select group_number DG#, name, state, type, total_mb, free_mb from
v$asm_diskgroup;
--
-- Get the # of Allocation Units  per DG
set head off
select 'Number of AUs per diskgroup' from dual;
set head on
select count(number_kfdat) AU_count, group_kfdat DG# from x$kfdat
group by group_kfdat;
--
-- Get the # of Allocation Units  per DiskGroup and Disk
set head off
select 'Number of AUs per Diskgroup,Disk' from dual;
col "group#,disk#" for a30
set head on
select count(*)AU_count, GROUP_KFDAT||','||number_kfdat "group#,disk#" from x$kfdat group by GROUP_KFDAT,number_kfdat;
--
-- Get the # of allocated (V) and free (F) Allocation Units
set head off
select 'Number of allocated (V) and free (F) Allocation Units' from dual;
col "VF" for a2
set head on
select GROUP_KFDAT "group#", number_kfdat "disk#", v_kfdat "VF", count(*)
from x$kfdat
group by GROUP_KFDAT, number_kfdat, v_kfdat;
--
-- Get the # of Allocation Units per ASM file
set head off
select 'Number of AUs per ASM file ordered by AU count for metadata only'
from dual;
set head on
select count(XNUM_KFFXP) AU_count,  NUMBER_KFFXP file#, GROUP_KFFXP DG# from x$kffxp where NUMBER_KFFXP < 256
group by NUMBER_KFFXP, GROUP_KFFXP
order by count(XNUM_KFFXP) ;
--
-- Get the # of Allocation Units per ASM file by file alias.  Change the
-- system_created Y|N depending if you want the short or long ASM name
set head off
select 'Number of AUs per ASM file ordered by AU count.  This is for non
metadata' from dual;
col name format a60
set head on
select GROUP_KFFXP, NUMBER_KFFXP, name, count(*)
from x$kffxp, v$asm_alias
where GROUP_KFFXP=GROUP_NUMBER and NUMBER_KFFXP=FILE_NUMBER and
system_created='Y'
     group by GROUP_KFFXP, NUMBER_KFFXP, name
     order by GROUP_KFFXP, NUMBER_KFFXP;
--
-- Get partner information.  This is really only useful if redundancy is other than
-- external.
set head off
select 'The following shows the disk to partner relationship.  This is really only
useful if using normal or high redundancy.' from dual;
set head on
select grp DG#, disk, NUMBER_KFDPARTNER partner, PARITY_KFDPARTNER parity, ACTIVE_KFDPARTNER active
from x$kfdpartner;
--
-- Another look at file utilization.
set head off
set linesize 132
select 'bytes is the sum of AUs with data in them * 1024^2
space is the sum of all AUs allocated for this file * 1024^2'
from dual;
set head on
col Name format a60
select f.group_number, f.file_number, bytes, space, space/(1024*1024) "InMB", a.name "Name"
from v$asm_file f, v$asm_alias a
where f.group_number=a.group_number and f.file_number=a.file_number
    and system_created='Y'
    order by f.group_number, f.file_number;
--
-- Get robust disk information
set linesize 400
col failgroup format a20
col label format a20
col name format a40
col path format a40
set head off
select 'Robust disk information' from dual;
set head on
select GROUP_NUMBER, DISK_NUMBER, INCARNATION, MOUNT_STATUS, HEADER_STATUS, MODE_STATUS, STATE, LIBRARY, TOTAL_MB, FREE_MB,
NAME, FAILGROUP, LABEL, PATH, CREATE_DATE, MOUNT_DATE, READS,
WRITES, READ_ERRS, WRITE_ERRS, READ_TIME, WRITE_TIME, BYTES_READ, BYTES_WRITTEN
from v$asm_disk;
--
spool off

 

 

Code to be executed on the database instances using this ASM instance. Use file rdbmsdebug.sql

 

set newpage none
spool /tmp/rdbmsdebug.out
--
-- Get a timestamp
select rpad('>', 10, '>'), to_char(sysdate, 'MON DD HH24:MM:SS') from dual;
--
-- Get datafile information as the database sees it
set head off
select 'V$DATAFILE information' from dual;
set head on
set linesize 132
col name format a60
select file#, name, block_size, blocks, bytes, bytes/(1024*1024) "InMB",
status from v$datafile;
--
-- Get controlfile information as the database sees it
set head off
select 'V$CONTROLFILE information' from dual;
set head on
select * from v$controlfile;
--
-- Get archivelog information as the database sees it
set head off
select 'GV$ARCHIVED_LOG information' from dual;
set head on
select name, thread#, sequence#, blocks*block_size "size", status
status from gv$archived_log
order by thread#,sequence#;
--
-- Get redolog information as the database sees it
set head off
select 'v$log and v$logfile information' from dual;
set head on
col member format a60
select a.group#, member, thread#, sequence#, bytes, a.status
from v$log a, v$logfile b where a.group# = b.group#
order by thread#;
--
-- Get tempfiles information as the database sees it
set head off
select 'GV$TEMPFILE information' from dual;
set head on
col name format a60
select INST_ID,TS#,FILE#, RFILE#,NAME, CREATION_CHANGE# , CREATION_TIME,  STATUS, BYTES, BLOCKS, CREATE_BYTES, BLOCK_SIZE
from gv$tempfile order by inst_id, file#;
spool off

 

Other items to collect:

System error logs
Alert logs from all database and ASM instances
All recent trace files from all databases involved and all of the ASM instances
All “.trc” files from the ASM instances

 

check ASM disk space

 

1) Determine which (if any) disks contain no free space (ie are below the threshold)
select group_kfdat "group #",
       number_kfdat "disk #",
       count(*) "# AU's"
from x$kfdat a
where v_kfdat = 'V'
and not exists (select *
                from x$kfdat b
                where a.group_kfdat = b.group_kfdat
                and a.number_kfdat = b.number_kfdat
                and b.v_kfdat = 'F')
group by GROUP_KFDAT, number_kfdat;

If no rows are returned ... the following query can also be used

select disk_number "Disk #", free_mb
from v$asm_disk
where group_number = *** disk group number ***
order by 2;

If rows are returned from the first query ... or FREE_MB is less than 100mb in the second ... then there is probably insufficient disk space to allow a rebalance to occur ... Note the Disk #'s for later

2) Determine which files have allocation units on the disk(s) that are on exhausted disks

select name, file_number
from v$asm_alias
where group_number in (select group_kffxp
                                           from x$kffxp
                                           where group_kffxp=*** disk group number ***
                                           and disk_kffxp in (*** disk list from #1 above ***)
                                           and au_kffxp != 4294967294
                                           and number_kffxp >= 256)
and file_number in (select number_kffxp
                                  from x$kffxp
                                  where group_kffxp=*** disk group number ***
                                  and disk_kffxp in (*** disk list from #1 above ***)
                                  and au_kffxp != 4294967294
                                  and number_kffxp >= 256)
and system_created='Y';

3) Free up space so that the rebalance can occur

Using the file list from #2 above ... we will need to either drop or move tablespace(s)/datafile(s) such that all disks that are exhausted have at least 100mb free ...

NOTE ... the AU count above ... should relate to 1mb AU size ... so if a single file ... with at least 100 au's can be dropped or moved ... this
should be sufficient to free up enough space to allow the rebalance to occur

Droppable tablespaces may be things like:
    * temporary tablespaces
    * index tablespaces (assuming you know how to rebuild the indexes)

If none of the tablespaces are droppable then the tablespace(s)/datafile(s) will need to be
    * moved to another diskgroup (at least temporarily) ...
    * dropped using RMAN (with the database shutdown) and will be restored later

   Note 330103.1  How to Move Asm Database Files From one Diskgroup To Another ?

4) Check to see if there is sufficient FREE_MB on the problem disks

select disk_number "Disk #", free_mb
from v$asm_disk
where disk_group = *** disk group number ***
and disk_number in (*** disk list from #1 above ***)
order by 2;

 

Check Diskgroup Balance

If you want to perform an imbalance check for all mounted diskgroups, run the script in MOS Note 367445.1.

If you want to determine if you already have imbalance for a file in an existing diskgroup, use the following query:

select disk_kffxp, sum(size_kffxp) from x$kffxp where group_kffxp=AAA and number_kffxp=BBB and lxn_kffxp=0 group by disk_kffxp order by 2;

Breakdown of input/output is as follows:

  • AAA is the group_number in v$asm_alias
  • BBB is file_number in v$asm_alias
  • disk_kffxp gives us the disk number.
  • size_kffxp is used such that we account for variable sized extents.
  • sum(size_kffxp) provides the number of AUs that are on that disk.
  • lxn_kffxp is used in the query such that we go after only the primary extents, not secondary extents

If you want to check balance from an IO perspective, query the statistics in v$asm_disk_iostat before and after running a large SQL statement. For example, if the running a large query that does just reads, the reads and read_bytes columns should be roughly the same for all disks in the diskgroup.

Filed Under: Oracle, Oracle ASM, Oracle脚本script Tagged With: , ,

Script:收集Oracle备份恢复信息

2011/05/27 by 2 Comments

我们在诊断Oracle backup restore问题时总是希望能获得足够的诊断信息，一般来说RDA会是一个最好的诊断信息收集工具，但是有时候客户会很反感使用RDA(不信任感)，这里我们提供一段专门用来收集oracle备份恢复信息的脚本。

运行以下脚本需要设置合理的”ORACLE_HOME、ORACLE_SID”环境变量，并设置NLS_DATE_FORMAT环境变量，如

NLS_DATE_FORMAT="DD-MON-RRRR HH24:MI:SS"
export NLS_DATE_FORMAT

以”rman target /”登陆并运行:

spool log to rman_report.log
set echo on
show all;
report schema;
list incarnation;
list backup summary;
list backup;
list copy;
report need backup;
report obsolete;
restore database preview;
spool log off

以下脚本在sqlplus中以sysdba身份执行，执行要求数据库至少处于mounted已加载状态下；注意该原始脚本是只读readonly的，它仅仅是读取数据字典，不会造成危害，当然请确保你的脚本来源!!

spool results01.txt
set echo on feedback on time on timing on pagesize 100 linesize 80 numwidth 13
show user
alter session set nls_date_format = 'DD-MON-YYYY HH24:MI:SS';
select * from v$version;
select to_char(sysdate, 'DD-MON-YYYY HH24:MI:SS') as current_date from dual;
column name format a30
column value format a49
select name, value from v$parameter where isdefault='FALSE' order by 1;
column parameter format a30
column value format a49
select * from v$nls_parameters order by parameter;
column name format a10
select dbid, name,
       to_char(created, 'DD-MON-YYYY HH24:MI:SS') created,
       open_mode, log_mode,
       to_char(checkpoint_change#, '999999999999999') as checkpoint_change#,
       controlfile_type,
       to_char(controlfile_change#, '999999999999999') as controlfile_change#,
       to_char(controlfile_time, 'DD-MON-YYYY HH24:MI:SS') controlfile_time,
       to_char(resetlogs_change#, '999999999999999') as resetlogs_change#,
       to_char(resetlogs_time, 'DD-MON-YYYY HH24:MI:SS') resetlogs_time
from v$database;
select * from v$instance;
archive log list;
select * from v$thread order by thread#;
select * from v$log order by first_change#;
column member format a45
select * from v$logfile;
column name format a79
select '#' || ts.name || '#' as tablespace_name, ts.ts#,
       '#' || df.name || '#' as filename, df.file#, df.status, df.enabled, df.creation_change#,
       to_char(df.creation_time, 'DD-MON-YYYY HH24:MI:SS') as creation_time,
       to_char(df.checkpoint_change#, '999999999999999') as checkpoint_change#,
       to_char(df.checkpoint_time, 'DD-MON-YYYY HH24:MI:SS') as checkpoint_time,
       to_char(df.offline_change#, '999999999999999') as offline_change#,
       to_char(df.online_change#, '999999999999999') as online_change#,
       to_char(df.online_time, 'DD-MON-YYYY HH24:MI:SS') as online_time,
       to_char(df.unrecoverable_change#, '999999999999999') as online_change#,
       to_char(df.unrecoverable_time, 'DD-MON-YYYY HH24:MI:SS') as online_time,
       to_char(df.bytes, '9,999,999,999,990') as bytes, block_size
from v$datafile df, v$tablespace ts
where ts.ts# = df.ts#
and ( df.status <> 'ONLINE'
or    df.checkpoint_change# <> (select checkpoint_change# from v$database) );
select '#' || ts.name || '#' as tablespace_name, ts.ts#,
       '#' || dh.name || '#' as filename, dh.file#, dh.status, dh.error, dh.
fuzzy, dh.creation_change#,
       to_char(dh.creation_time, 'DD-MON-YYYY HH24:MI:SS') as creation_time,
       to_char(dh.checkpoint_change#, '999999999999999') as checkpoint_change#,
       to_char(dh.checkpoint_time, 'DD-MON-YYYY HH24:MI:SS') as checkpoint_time,
       to_char(dh.resetlogs_change#, '999999999999999') as resetlogs_change#,
       to_char(dh.resetlogs_time, 'DD-MON-YYYY HH24:MI:SS') as resetlogs_time,
       to_char(dh.bytes, '9,999,999,999,990') as bytes
from v$datafile_header dh, v$tablespace ts
where ts.ts# = dh.ts#
and ( dh.status <> 'ONLINE'
or    dh.checkpoint_change# <> (select checkpoint_change# from v$database) );
select * from v$tempfile;
select HXFIL File_num,substr(HXFNM,1,60) file_name, FHTNM tablespace_name,
       FHTYP type, HXERR validity,
       FHSCN SCN, FHTIM SCN_Time, FHSTA status,
       FHTHR Thread, FHRBA_SEQ Sequence
from X$KCVFH
--where HXERR > 0
order by HXERR, FHSTA, FHSCN, HXFIL;
column error format a15
select error, fuzzy, status, checkpoint_change#,
       to_char(checkpoint_time, 'DD-MON-YYYY HH24:MI:SS') as checkpoint_time,
       count(*)
from v$datafile_header
group by error, fuzzy, status, checkpoint_change#, checkpoint_time
order by checkpoint_change#, checkpoint_time;
select * from V$INSTANCE_RECOVERY;
select * from v$recover_file order by change#;
select * from dba_tablespaces where status <> 'ONLINE';
SELECT * FROM database_properties order by property_name;
select *
from X$KCCLH, (select min(checkpoint_change#) df_min_scn,
min(checkpoint_change#) df_max_scn
               from v$datafile_header
               where status='ONLINE') df
where LHLOS in (select first_change# from v$log)
or df.df_min_scn between LHLOS and LHNXS
or df.df_max_scn between LHLOS and LHNXS;
select * from v$backup where status <> 'NOT ACTIVE';
select ADDR, XIDUSN, XIDSLOT, XIDSQN,
       UBAFIL, UBABLK, UBASQN,
       START_UBAFIL, START_UBABLK, START_UBASQN,
       USED_UBLK, STATUS
from   v$transaction;
select * from v$archive_gap;
select * from v$archive_dest_status where recovery_mode <> 'IDLE';
column USED_GB format 999,990.999
column USED% format 990.99
column RECLAIM_GB format 999,990.999
column RECLAIMABLE% format 990.99
column LIMIT_GB format 999,990.999
select frau.file_type as type,
       frau.percent_space_used/100 * rfd.space_limit /1024/1024/1024 "USED_GB",
       frau.percent_space_used "USED%",
       frau.percent_space_reclaimable "RECLAIMABLE%",
       frau.percent_space_reclaimable/100 * rfd.space_limit /1024/1024/1024 "RECLAIM_GB",
       frau.number_of_files "FILES#"
from   v$flash_recovery_area_usage frau,
       v$recovery_file_dest rfd
order by file_type;
select name,
       space_limit/1024/1024/1024 "LIMIT_GB",
       space_used/1024/1024/1024 "USED_GB",
       space_used/space_limit*100 "USED%",
       space_reclaimable/1024/1024/1024 "RECLAIM_GB",
       number_of_files "FILE#"
from   v$recovery_file_dest;
select * from v$backup_corruption;
select * from v$copy_corruption order by file#, block#;
select * from v$database_block_corruption order by file#, block#;
SELECT f.file#, f.name,
       e.tablespace_name, e.segment_type, e.owner, e.segment_name,
       c.file#, c.block#, c.blocks, c.corruption_change#, c.corruption_type
FROM dba_extents e, V$database_block_corruption c, v$datafile f
WHERE c.file# = f.file#
and   e.file_id = c.file#
and   c.block# between e.block_id AND e.block_id + e.blocks - 1;
select * from v$database_incarnation;
select * from v$rman_configuration;
select s.recid as bs_key, p.recid as bp_key, p.status, p.tag, p.device_type,
       p.handle, p.media, p.completion_time, p.bytes
from   v$backup_piece p, v$backup_set s
where  p.set_stamp = s.set_stamp
and    s.controlfile_included='YES'
order by p.completion_time;
select s.recid as bs_key, p.recid as bp_key, p.status, p.tag, p.device_type,
       p.handle, p.media, p.completion_time, f.absolute_fuzzy_change#, p.bytes
from   v$backup_datafile f, v$backup_piece p, v$backup_set s
where  p.set_stamp = s.set_stamp
and    f.set_stamp = s.set_stamp
and    p.handle is not null
and    f.file# = 1
order by p.completion_time;
SELECT
  session_recid,
  input_bytes_per_sec_display,
  output_bytes_per_sec_display,
  time_taken_display,
  end_time
FROM v$rman_backup_job_details
ORDER BY end_time;
select * from v$filestat;
column EBS_MB format 9,990.99
column TOTAL_MB format 999,990.99
select SID, SERIAL, FILENAME, EFFECTIVE_BYTES_PER_SECOND/1024/1024 as EBS_MB,
      OPEN_TIME, CLOSE_TIME, ELAPSED_TIME, TOTAL_BYTES/1024/1024 as TOTAL_MB,
      STATUS, MAXOPENFILES, buffer_size, buffer_count
from v$backup_async_io
where close_time >= sysdate-3
order by close_time;
select SID, SERIAL, FILENAME, EFFECTIVE_BYTES_PER_SECOND/1024/1024 as EBS_MB,
      OPEN_TIME, CLOSE_TIME, ELAPSED_TIME, TOTAL_BYTES/1024/1024 as TOTAL_MB,
      STATUS, MAXOPENFILES, buffer_size, buffer_count
from v$backup_sync_io
where close_time >= sysdate-3;
select * from v$controlfile_record_section order by type;
select to_char(rownum) || '. ' || output rman_output from v$rman_output;
select * from v$rman_status where trunc(end_time) > trunc(sysdate)-3;
select protection_mode, protection_level from v$database;
select * from v$recovery_progress;
select s.client_info,
       sl.message,
       sl.sid, sl.serial#, p.spid,
       round(sl.sofar/sl.totalwork*100,2) "% Complete"
from   v$session_longops sl, v$session s, v$process p
where  p.addr = s.paddr
and    sl.sid=s.sid
and    sl.serial#=s.serial#
and    opname LIKE 'RMAN%'
and    opname NOT LIKE '%aggregate%'
and    totalwork != 0
and    sofar <> totalwork;
select AL.*,
       DF.min_checkpoint_change#, DF.min_checkpoint_time
from v$archived_log AL,
     (select min(checkpoint_change#) min_checkpoint_change#,
             min(checkpoint_time) min_checkpoint_time
      from v$datafile_header
      where status='ONLINE') DF
where DF.min_checkpoint_change# between AL.first_change# and AL.next_change#
order by AL.first_change#;
select * from v$asm_diskgroup;
select * from v$asm_disk;
select * from v$flashback_database_log;
select * from v$flashback_database_logfile order by first_change# desc;
select * from v$flashback_database_stat order by begin_time desc;
select * from v$restore_point;
select * from v$rollname;
select * from v$undostat;
select * from dba_rollback_segs;
spool off
Filed Under: Oracle, Oracle数据恢复、数据库恢复、灾难恢复, Oracle脚本script Tagged With: , , , ,

Script to Collect DB Upgrade/Migrate Diagnostic Information (dbupgdiag.sql)

2011/03/16 by Leave a Comment

This script is intended to provide a user friendly output to diagnose the status of the database either before (or) after upgrade. The script will create a file called db_upg_diag__.log.

-- - - - - - - - - - - - - - Script begins here - - - - - - - - - - - - - - 

col TODAY    NEW_VALUE    _DATE     
col VERSION NEW_VALUE _VERSION
set termout off
select to_char(SYSDATE,'fmMonth DD, YYYY') TODAY from DUAL;
select version from v$instance;
set termout on
set echo off
set feedback off
set head off
set verify off
Prompt
PROMPT Enter location for Spooled output:
Prompt
DEFINE log_path = &1
column timecol new_value timestamp
column spool_extension new_value suffix
SELECT to_char(sysdate,'dd-Mon-yyyy_hhmi') timecol,'.log' spool_extension FROM 
sys.dual;
column output new_value dbname
SELECT value || '_' output FROM v$parameter WHERE name = 'db_name';
spool &log_path/db_upg_diag_&&dbname&&timestamp&&suffix
set linesize 150
set pages 100
set trim on
set trims on
col Compatible for a35
col comp_id for a12
col comp_name for a40
col org_version for a11
col prv_version for a11
col owner for a12
col object_name for a40
col object_type for a40
col Wordsize for a25
col Metadata for a8
col 'Initial DB Creation Info' for a35
col 'Total Invalid JAVA objects' for a45
col 'Role' for a30
col 'User Existence' for a27
col "JAVAVM TESTING" for a15
Prompt
Prompt
set feedback off head off
select LPAD('*** Start of LogFile ***',50) from dual;
select LPAD('Oracle Database Upgrade Diagnostic Utility',44)||
 LPAD(TO_CHAR(SYSDATE, 'MM-DD-YYYY HH24:MI:SS'),26) from dual;
Prompt
Prompt ===============
Prompt Database Uptime
Prompt ===============
SELECT to_char(startup_time, 'HH24:MI DD-MON-YY') "Startup Time" 
FROM v$instance;
Prompt
Prompt =================
Prompt Database Wordsize
Prompt =================
SELECT distinct('This is a ' || (length(addr)*4) || '-bit database') "WordSize" 
FROM v$process;
Prompt
Prompt ================
Prompt Software Version
Prompt ================
SELECT * FROM v$version;
Prompt
Prompt =============
Prompt Compatibility
Prompt =============
SELECT 'Compatibility is set as '||value Compatible 
FROM v$parameter WHERE name ='compatible';
Prompt
Prompt ================
Prompt Component Status
Prompt ================
Prompt
SET SERVEROUTPUT ON;
DECLARE

ORG_VERSION varchar2(12);
PRV_VERSION varchar2(12);
P_VERSION VARCHAR2(10);

BEGIN 

SELECT version INTO p_version 
FROM registry$ WHERE cid='CATPROC' ;

IF SUBSTR(p_version,1,5) = '9.2.0' THEN

DBMS_OUTPUT.PUT_LINE(RPAD('Comp ID', 8) ||RPAD('Component',35)|| 
 RPAD('Status',10) ||RPAD('Version', 15));

DBMS_OUTPUT.PUT_LINE(RPAD(' ',8,'-') ||RPAD(' ',35,'-')|| 
 RPAD(' ',10,'-') ||RPAD(' ',15,'-'));

FOR x in (SELECT SUBSTR(dr.comp_id,1,8) comp_id,
 SUBSTR(dr.comp_name,1,35) comp_name, 
 dr.status Status,SUBSTR(dr.version,1,15) version
 FROM dba_registry dr,registry$ r
 WHERE dr.comp_id=r.cid and dr.comp_name=r.cname
 ORDER BY 1)

LOOP

DBMS_OUTPUT.PUT_LINE(RPAD(SUBSTR(x.comp_id,1,8),8) || 
 RPAD(SUBSTR(x.comp_name,1,35),35)||
 RPAD(x.status,10) || RPAD(x.version, 15));
END LOOP;

ELSIF SUBSTR(p_version,1,5) != '9.2.0' THEN

DBMS_OUTPUT.PUT_LINE(RPAD('Comp ID', 8) ||RPAD('Component',35)||  
 RPAD('Status',10) ||RPAD('Version', 15)||
 RPAD('Org_Version',15)||RPAD('Prv_Version',15));

DBMS_OUTPUT.PUT_LINE(RPAD(' ',8,'-') ||RPAD(' ',35,'-')|| 
 RPAD(' ',10,'-')||RPAD(' ',15,'-')||RPAD(' ',15,'-')||
 RPAD(' ',15,'-'));

FOR y in (SELECT SUBSTR(dr.comp_id,1,8) comp_id,
 SUBSTR(dr.comp_name,1,35) comp_name, dr.status Status, 
 SUBSTR(dr.version,1,11) version,org_version,prv_version
 FROM dba_registry dr,registry$ r
 WHERE dr.comp_id=r.cid and dr.comp_name=r.cname
 ORDER BY 1)

LOOP

DBMS_OUTPUT.PUT_LINE(RPAD(substr(y.comp_id,1,8), 8) || 
 RPAD(substr(y.comp_name,1,35),35)||RPAD(y.status,10) ||
 RPAD(y.version, 15)||RPAD(y.org_version,15)||RPAD(y.prv_version,15));

END LOOP;

END IF;
END;
/
SET SERVEROUTPUT OFF
Prompt
Prompt
Prompt ======================================================
Prompt List of Invalid Database Objects Owned by SYS / SYSTEM
Prompt ======================================================
Prompt
set head on
SELECT case count(object_name)
WHEN 0 THEN 'There are no Invalid Objects'
ELSE 'There are '||count(object_name)||' Invalid objects'
END "Number of Invalid Objects"
FROM dba_objects 
WHERE status='INVALID'
AND owner in ('SYS','SYSTEM');
Prompt
DOC 
################################################################

 If there are no Invalid objects below will result in zero rows.

################################################################
#
Prompt
set feedback on
SELECT owner,object_name,object_type 
FROM dba_objects 
WHERE status='INVALID' 
AND owner in ('SYS','SYSTEM')
ORDER BY owner,object_type;
set feedback off
Prompt
Prompt ================================
Prompt List of Invalid Database Objects
Prompt ================================
Prompt
set head on
SELECT case count(object_name)
WHEN 0 THEN 'There are no Invalid Objects'
ELSE 'There are '||count(object_name)||' Invalid objects'
END "Number of Invalid Objects"
FROM dba_objects 
WHERE status='INVALID'
AND owner not in ('SYS','SYSTEM');
Prompt
DOC
################################################################

 If there are no Invalid objects below will result in zero rows.

################################################################
#
Prompt
set feedback on
SELECT owner,object_name,object_type 
FROM dba_objects 
WHERE status='INVALID' 
AND owner not in ('SYS','SYSTEM')
ORDER BY owner,object_type;
set feedback off
Prompt
Prompt ==============================================================
Prompt Identifying whether a database was created as 32-bit or 64-bit
Prompt ==============================================================
Prompt
DOC 
###########################################################################

 Result referencing the string 'B023' ==> Database was created as 32-bit
 Result referencing the string 'B047' ==> Database was created as 64-bit
 When String results in 'B023' and when upgrading database to 10.2.0.3.0 
 (64-bit) , For known issue refer below articles

 Note 412271.1 ORA-600 [22635] and ORA-600 [KOKEIIX1] Reported While 
 Upgrading Or Patching Databases To 10.2.0.3
 Note 579523.1 ORA-600 [22635], ORA-600 [KOKEIIX1], ORA-7445 [KOPESIZ] and 
 OCI-21500 [KOXSIHREAD1] Reported While Upgrading To 11.1.0.6

###########################################################################
#
Prompt
SELECT SUBSTR(metadata,109,4) "Metadata",
CASE SUBSTR(metadata,109,4)
WHEN 'B023' THEN 'Database was created as 32-bit'
WHEN 'B047' THEN 'Database was created as 64-bit'
ELSE 'Metadata not Matching'
END "Initial DB Creation Info"
FROM sys.kopm$;
Prompt
Prompt ===================================================
Prompt Number of Duplicate Objects Owned by SYS and SYSTEM
Prompt ===================================================
Prompt
Prompt Counting duplicate objects ....
Prompt
SELECT count(1) 
FROM dba_objects 
WHERE object_name||object_type in 
 (SELECT object_name||object_type  
 from dba_objects 
 where owner = 'SYS') 
and owner = 'SYSTEM'; 
Prompt
Prompt =========================================
Prompt Duplicate Objects Owned by SYS and SYSTEM
Prompt =========================================
Prompt
Prompt Querying duplicate objects ....
Prompt
SELECT object_name, object_type 
FROM dba_objects 
WHERE object_name||object_type in 
 (SELECT object_name||object_type  
 FROM dba_objects 
 WHERE owner = 'SYS') 
AND owner = 'SYSTEM'; 
Prompt
DOC

################################################################################

 If any objects found please follow below article.
 Note 1030426.6 How to Clean Up Duplicate Objects Owned by SYS and SYSTEM schema
 Read the Exceptions carefully before taking actions.

################################################################################
#
Prompt
Prompt ================
Prompt JVM Verification
Prompt ================
Prompt
SET SERVEROUTPUT ON
DECLARE

V_CT NUMBER;
P_VERSION VARCHAR2(10);

BEGIN

-- If so, get the version of the JAVAM component
EXECUTE IMMEDIATE 'SELECT version FROM registry$ WHERE cid=''JAVAVM'' 
 AND status <> 99' INTO p_version;

SELECT count(*) INTO v_ct FROM dba_objects
WHERE object_type LIKE '%JAVA%' AND owner='SYS';

IF SUBSTR(p_version,1,5) = '8.1.7' THEN
 IF v_ct>=6787 THEN
 DBMS_OUTPUT.PUT_LINE('JAVAVM - Installed properly');
 ELSE
 DBMS_OUTPUT.PUT_LINE('JAVAVM - Not Installed properly');
 END IF;
ELSIF SUBSTR(p_version,1,5) = '9.0.1' THEN
 IF v_ct>=8585 THEN
 DBMS_OUTPUT.PUT_LINE('JAVAVM - Installed properly');
 ELSE
 DBMS_OUTPUT.PUT_LINE('JAVAVM - Not Installed properly');
 END IF;
ELSIF SUBSTR(p_version,1,5) = '9.2.0' THEN
 IF v_ct>=8585 THEN
 DBMS_OUTPUT.PUT_LINE('JAVAVM - Installed properly');
 ELSE
 DBMS_OUTPUT.PUT_LINE('JAVAVM - Not Installed properly');
 END IF;
ELSIF SUBSTR(p_version,1,6) = '10.1.0' THEN
 IF v_ct>=13866 THEN
 DBMS_OUTPUT.PUT_LINE('JAVAVM - Installed properly');
 ELSE
 DBMS_OUTPUT.PUT_LINE('JAVAVM - Not Installed properly');
 END IF;
ELSIF SUBSTR(p_version,1,6) = '10.2.0' THEN
 IF v_ct>=14113 THEN
 DBMS_OUTPUT.PUT_LINE('JAVAVM - Installed properly');
 ELSE
 DBMS_OUTPUT.PUT_LINE('JAVAVM - Not Installed properly');
 END IF;
END IF;

EXCEPTION WHEN NO_DATA_FOUND THEN
 DBMS_OUTPUT.PUT_LINE('JAVAVM - NOT Installed. Below results can be ignored');

END;
/
SET SERVEROUTPUT OFF
Prompt
Prompt ================================================
Prompt Checking Existence of Java-Based Users and Roles
Prompt ================================================
Prompt
DOC

################################################################################

 There should not be any Java Based users for database version 9.0.1 and above.
 If any users found, it is faulty JVM.

################################################################################
#

Prompt
SELECT CASE count(username)
WHEN 0 THEN 'No Java Based Users'
ELSE 'There are '||count(*)||' JAVA based users'
END "User Existence"
FROM dba_users WHERE username LIKE '%AURORA%' AND username LIKE '%OSE%';
Prompt
DOC

###############################################################

 Healthy JVM Should contain Six Roles. 
 If there are more or less than six role, JVM is inconsistent.

###############################################################
#

Prompt
SELECT CASE count(role)
WHEN 0 THEN 'No JAVA related Roles'
ELSE 'There are '||count(role)||' JAVA related roles'
END "Role"
FROM dba_roles 
WHERE role LIKE '%JAVA%';
Prompt
Prompt Roles
Prompt
SELECT role FROM dba_roles WHERE role LIKE '%JAVA%';
set head off
Prompt
Prompt =========================================
Prompt List of Invalid Java Objects owned by SYS
Prompt =========================================
SELECT CASE count(*) 
 WHEN 0 THEN 'There are no SYS owned invalid JAVA objects'
 ELSE 'There are '||count(*)||' SYS owned invalid JAVA objects'
 END "Total Invalid JAVA objects"
FROM dba_objects 
WHERE object_type LIKE '%JAVA%' 
AND status='INVALID' 
AND owner='SYS';
Prompt
DOC

#################################################################

 Check the status of the main JVM interface packages DBMS_JAVA 
 and INITJVMAUX and make sure it is VALID.
 If there are no Invalid objects below will result in zero rows.

#################################################################
#
Prompt
set feedback on
SELECT owner,object_name,object_type
FROM dba_objects 
WHERE object_type LIKE '%JAVA%' 
AND status='INVALID' 
AND owner='SYS';
set feedback off
Prompt
Prompt INFO: Below query should succeed with 'foo' as result.
set heading on
select dbms_java.longname('foo') "JAVAVM TESTING" from dual;
set heading off
Prompt 

set feedback off head off
select LPAD('*** End of LogFile ***',50) from dual;
set feedback on head on
Prompt
spool off
Prompt
set heading off
set heading off
set feedback off
select 'Upload db_upg_diag_&&dbname&&timestamp&&suffix from "&log_path" directory' 
from dual;
set heading on
set feedback on
Prompt
-- - - - - - - - - - - - - - - - Script ends here - - - - - - - - - - - - - -
Filed Under: Oracle, Oracle脚本script Tagged With: , ,

沪ICP备14014813号-2

沪公网安备 31010802001379号