Oracle异常恢复前备份保护现场建议—FileSystem环境

无论是在各种会议上,还是在朋友/网友私下请教Oracle数据库恢复的问题之时,我都强调,如果你没有十足的把握,请你对您的现场进行备份,确保别对现场进行二次损坏。你不能恢复数据库,但绝对不能再次破坏数据库,给二次恢复增加难度.这里对恢复前备份提供一些指导思想和简单脚本,希望对大家有帮助.

哪些文件需要备份
熟悉数据库恢复的朋友可能都情况,Oracle在异常恢复的过程中主要修改的是system表空间里面数据,其他数据文件,redo数据,控制文件(当然由于redo,undo导致其他数据文件内部的block也可能发生改变)。在备份时间,备份空间允许的情况下,是对这些文件全部备份为好

完整备份文件

set lines 150
set pages 10000
select name from v$datafile
union all
select name from v$controlfile
union all
select member from v$logfile;

有些情况下:比如如果全部备份时间过长,备份空间不足等情况下,我们该如何备份,尽量减少因为异常恢复导致对原环境的损坏.备份最核心的system表空间,数据文件头,redo file,control file等数据,由于这个不是简单的拷贝操作,因此在生成备份语句同时,也生成还原语句,切不可生成了备份语句后,无恢复语句,导致后面还原故障现场难度增大.

无法全备情况下linux/unix数据库恢复前备份

set lines 150
set pages 10000
select 'dd if='||name||' of=&&back_dir/'||ts#||'_'||file#||'.dbf bs=1048576 count=10'
from v$datafile where ts#<>0
union all
select 'dd if='||name||' of=&&back_dir/'||ts#||'_'||file#||'.dbf' from v$datafile where ts#=0
union all
select 'dd if='||name||' of=&&back_dir/control0'||rownum||'.ctl' from v$controlfile
union all
select 'dd if='||member||' of=&&back_dir/'||thread#||'_'||a.group#||'_'||sequence#||'_'||substr(member,
instr(member,'/',-1)+1)  FROM v$log a, v$logfile b WHERE a.group# = B.GROUP#;

无法全备情况下linux/unix使用备份还原

set lines 150
set pages 1000
select 'dd of='||name||' if=&&back_dir/'||ts#||'_'||file#||'.dbf bs=1048576 count=10 conv=notrunc'
from v$datafile where ts#<>0
union all
select 'dd if='||name||' if=&&back_dir/'||ts#||'_'||file#||'.dbf' from v$datafile where ts#=0
union all
select 'dd of='||name||' if=&&back_dir/control0'||rownum||'.ctl' from v$controlfile
union all
select 'dd of='||member||' if=&&back_dir/'||thread#||'_'||a.group#||'_'||sequence#||'_'||substr(member,
instr(member,'/',-1)+1)    FROM v$log a, v$logfile b WHERE a.group# = B.GROUP#;

由于win路径斜杠不一样(/和\的区别),因此在无法全备情况下win备份语句

set lines 150
set pages 10000
select 'dd if='||name||' of=&&back_dir\'||ts#||'_'||file#||'.dbf bs=1048576 count=10'
from v$datafile where ts#<>0
union all
select 'dd if='||name||' of=&&back_dir\'||ts#||'_'||file#||'.dbf' from v$datafile where ts#=0
union all
select 'dd if='||name||' of=&&back_dir\control0'||rownum||'.ctl' from v$controlfile
union all
select 'dd if='||member||' of=&&back_dir\'||thread#||'_'||a.group#||'_'||sequence#||'_'||substr(member,
instr(member,'\',-1)+1)   FROM v$log a, v$logfile b WHERE a.group# = B.GROUP#;

在无法全备情况下win还原语句

set lines 150
set pages 1000
select 'dd of='||name||' if=&&back_dir\'||ts#||'_'||file#||'.dbf bs=1048576 count=10 conv=notrunc'
from v$datafile where ts#<>0
union all
select 'dd if='||name||' if=&&back_dir\'||ts#||'_'||file#||'.dbf' from v$datafile where ts#=0
union all
select 'dd of='||name||' if=&&back_dir\control0'||rownum||'.ctl' from v$controlfile
union all
select 'dd of='||member||' if=&&back_dir\'||thread#||'_'||a.group#||'_'||sequence#||'_'||substr(member,
instr(member,'\',-1)+1)    FROM v$log a, v$logfile b WHERE a.group# = B.GROUP#;

这里提供win环境下dd命令程序win环境dd命令工具

备注:对于asm情况异常情况恢复,备份情况请不要参考该文章,具体请见后续文章,具体见Oracle异常恢复前备份保护现场建议—ASM环境

kfed恢复误删除磁盘组

在某些情况下,可能因为误操作,不小先drop diskgroup,这个时候千万别紧张,出现此类故障,可以通过kfed进行完美恢复(数据0丢失).如果进一步损坏了相关asm disk,那后续恢复就很麻烦了,可能需要使用dul扫描磁盘来进行抢救性恢复,而且可能导致数据丢失.
创建测试磁盘组xifenfei

[grid@xifenfei ~]$ sqlplus / as sysasm
SQL*Plus: Release 11.2.0.4.0 Production on Thu Apr 30 15:12:08 2015
Copyright (c) 1982, 2013, Oracle.  All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Automatic Storage Management option
SQL>  select name,path,header_status from v$asm_disk;
NAME                           PATH                           HEADER_STATU
------------------------------ ------------------------------ ------------
                               /dev/asm-disk3                 CANDIDATE
DATA_0000                      /dev/asm-disk1                 MEMBER
DATA_0001                      /dev/asm-disk2                 MEMBER
SQL> create diskgroup xifenfei external redundancy disk '/dev/asm-disk3';
Diskgroup created.
SQL> select name,path,header_status from v$asm_disk;
NAME                           PATH                           HEADER_STATU
------------------------------ ------------------------------ ------------
XIFENFEI_0000                  /dev/asm-disk3                 MEMBER
DATA_0000                      /dev/asm-disk1                 MEMBER
DATA_0001                      /dev/asm-disk2                 MEMBER

使用/dev/asm-disk3这个磁盘创建磁盘组xifenfei

创建表,存储在xifenfei磁盘组中

[oracle@xifenfei ~]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Thu Apr 30 15:14:55 2015
Copyright (c) 1982, 2013, Oracle.  All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options
SQL> create tablespace xifenfei datafile '+xifenfei' size 100M;
Tablespace created.
SQL> select name from v$datafile;
NAME
--------------------------------------------------------------------------------
+DATA/xifenfei/datafile/system.256.878224279
+DATA/xifenfei/datafile/sysaux.257.878224279
+DATA/xifenfei/datafile/undotbs1.258.878224279
+DATA/xifenfei/datafile/users.259.878224279
+XIFENFEI/xifenfei/datafile/xifenfei.256.878397315
SQL> create table t_xifenfei tablespace xifenfei
  2  as select * from dba_objects;
Table created.
SQL> select count(*) from t_xifenfei;
  COUNT(*)
----------
     86259

通过在磁盘组中创建表空间,从而实现表xifenfei存放在测试磁盘组中

尝试删除磁盘组xifenfei

SQL> drop diskgroup xifenfei;
drop diskgroup xifenfei
*
ERROR at line 1:
ORA-15039: diskgroup not dropped
ORA-15053: diskgroup "XIFENFEI" contains existing files
SQL> drop diskgroup xifenfei  including contents;
drop diskgroup xifenfei  including contents
*
ERROR at line 1:
ORA-15039: diskgroup not dropped
ORA-15027: active use of diskgroup "XIFENFEI" precludes its dismount
[grid@xifenfei ~]$ asmcmd
ASMCMD> lsof
DB_Name   Instance_Name  Path
xifenfei  xifenfei       +data/xifenfei/controlfile/current.260.878224379
xifenfei  xifenfei       +data/xifenfei/datafile/sysaux.257.878224279
xifenfei  xifenfei       +data/xifenfei/datafile/system.256.878224279
xifenfei  xifenfei       +data/xifenfei/datafile/undotbs1.258.878224279
xifenfei  xifenfei       +data/xifenfei/datafile/users.259.878224279
xifenfei  xifenfei       +data/xifenfei/onlinelog/group_1.261.878224381
xifenfei  xifenfei       +data/xifenfei/onlinelog/group_2.262.878224383
xifenfei  xifenfei       +data/xifenfei/onlinelog/group_3.263.878224385
xifenfei  xifenfei       +data/xifenfei/tempfile/temp.264.878224395
xifenfei  xifenfei       +xifenfei/xifenfei/datafile/xifenfei.256.878397315

由于xifenfei磁盘组被实例使用,因此磁盘组无法删除,报ORA-15027错误
由于xifenfei磁盘组中有文件,因此磁盘组无法删除,报ORA-15053错误
如果这两个阻止你误删除磁盘组的警告依然不能救你,那我也不好多说啥了,只能向我一样继续往下

关闭数据库实例,删除磁盘组

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> drop diskgroup xifenfei;
drop diskgroup xifenfei
*
ERROR at line 1:
ORA-15039: diskgroup not dropped
ORA-15053: diskgroup "XIFENFEI" contains existing files
SQL> drop diskgroup xifenfei  including contents;
Diskgroup dropped.
SQL>  select name,path,header_status from v$asm_disk;
NAME                           PATH                           HEADER_STATU
------------------------------ ------------------------------ ------------
                               /dev/asm-disk3                 FORMER
DATA_0000                      /dev/asm-disk1                 MEMBER
DATA_0001                      /dev/asm-disk2                 MEMBER
SQL> alter diskgroup xifenfei mount;
alter diskgroup xifenfei mount
*
ERROR at line 1:
ORA-15032: not all alterations performed
ORA-15017: diskgroup "XIFENFEI" cannot be mounted
ORA-15063: ASM discovered an insufficient number of disks for diskgroup
"XIFENFEI"

磁盘组被drop之后,无法正常mount,mount之时报ORA-15063凑无

kfed恢复删除磁盘组

[grid@xifenfei ~]$ kfed read /dev/asm-disk3 >/tmp/disk3-0-0
[grid@xifenfei ~]$ kfed  read /dev/asm-disk3  blkn=1 >/tmp/disk3-0-1
[grid@xifenfei ~]$ kfed  read /dev/asm-disk3  aun=1 >/tmp/disk3-1-0
通过vi修改这些/tmp/disk3-*中的部分值
[grid@xifenfei ~]$ kfed merge /dev/asm-disk3 text=/tmp/disk3-0-0
[grid@xifenfei ~]$ kfed merge /dev/asm-disk3  blkn=1 text=/tmp/disk3-0-1
[grid@xifenfei ~]$ kfed merge /dev/asm-disk3 aun=1 text=/tmp/disk3-1-0

查询修复后的asm disk

SQL> col path for a30
SQL> set lines 150
SQL> select name,path,header_status from v$asm_disk;
NAME                           PATH                           HEADER_STATU
------------------------------ ------------------------------ ------------
                               /dev/asm-disk3                 MEMBER
DATA_0000                      /dev/asm-disk1                 MEMBER
DATA_0001                      /dev/asm-disk2                 MEMBER

尝试mount xifenfei 磁盘组

SQL> alter diskgroup xifenfei mount;
Diskgroup altered.
SQL> select name,path,header_status from v$asm_disk;
NAME                           PATH                           HEADER_STATU
------------------------------ ------------------------------ ------------
XIFENFEI_0000                  /dev/asm-disk3                 MEMBER
DATA_0000                      /dev/asm-disk1                 MEMBER
DATA_0001                      /dev/asm-disk2                 MEMBER

测试恢复后磁盘组

SQL> startup
ORACLE instance started.
Total System Global Area  952020992 bytes
Fixed Size                  2258960 bytes
Variable Size             306186224 bytes
Database Buffers          637534208 bytes
Redo Buffers                6041600 bytes
Database mounted.
Database opened.
SQL>  select count(*) from t_xifenfei;
  COUNT(*)
----------
     86259

这里证明,当磁盘组被误删除后,立即停止进一步损坏,可以通过kfed进行完美恢复
如果您遇到此类情况,无法解决请联系我们，提供专业ORACLE数据库恢复技术支持
Phone:17813235971    Q Q:107644445QQ咨询惜分飞    E-Mail:dba@xifenfei.com

dbms_diskgroup拷贝block/datafile

对于asm而言,如果我们要操作里面的数据文件,虽然从11.2开始有asmcmd的cp可能进行拷贝,但是如果想对数据文件中的某个block进行拷贝出来asm到文件系统(或者拷贝某个文件系统的block到asm中),还是比较麻烦的事情(请见:bbed修改ASM中数据)。其实oracle官方提供了dbms_diskgroup这个包,可以通过sqlplus直接操作asm里面的block/datafile,非常方便,这里简单列举几个例子:

dbms_diskgroup获取asm中文件属性

SQL> declare
  2  v_filename varchar2(4000);
  3  v_filetype number;
  4  v_filesize number;
  5  v_lbks number;
  6  v_typename varchar2(4000);
  7  begin
  8  dbms_output.enable(5000);
  9  v_filename := '&file_name';
 10  dbms_diskgroup.getfileattr(v_filename,v_filetype,v_filesize,v_lbks);
 11  select decode(v_filetype,1,'Control File',2,'Data File',3,'Online Log File',4,'Archive Log',5,'Trace File',6,'Temporary File',
 12  7,'Not Used',8,'Not Used',9,'Backup Piece',10,'Incremental Backup Piece',11,'Archive Backup Piece',12,'Data File Copy',
 13  13,'Spfile',14,'Disaster Recovery Configuration',15,'Storage Manager Disk',16,'Change Tracking File',17,'Flashback Log File',
 14  18,'DataPump Dump File',19,'Cross Platform Converted File',20,'Autobackup',21,'Any OS file',22,'Block Dump File',
 15  23,'CSS Voting File',24,'CRS') into v_typename from dual;
 16  dbms_output.put_line('File: '||v_filename); dbms_output.new_line;
 17  dbms_output.put_line('Type: '||v_filetype||' '||v_typename); dbms_output.new_line;
 18  dbms_output.put_line('Size (Logical Block Size): '||v_filesize); dbms_output.new_line;
 19  dbms_output.put_line('Logical Block Size: '||v_lbks); dbms_output.new_line;
 20  end;
 21  /
Enter value for file_name: +DATA/xifenfei/datafile/system.256.878224279
old   9: v_filename := '&file_name';
new   9: v_filename := '+DATA/xifenfei/datafile/system.256.878224279';
File: +DATA/xifenfei/datafile/system.256.878224279
Type: 12 Data File Copy
Size (Logical Block Size): 94720
Logical Block Size: 8192
PL/SQL procedure successfully completed.

创建测试表t_xifenfei

SQL> create table t_xifenfei tablespace users
  2  as select 'www.xifenfei.com' xifenfei from dual;
Table created.
SQL> select rowid,xifenfei from t_xifenfei;
ROWID              XIFENFEI
------------------ ----------------
AAAVU3AAEAAAACrAAA www.xifenfei.com
SQL> select
  2   dbms_rowid.rowid_relative_fno(rowid) rel_fno,
  3  dbms_rowid.rowid_block_number(rowid )block_no
  4  from t_xifenfei;
   REL_FNO   BLOCK_NO
---------- ----------
         4        171
SQL> alter system checkpoint;
System altered.
SQL> /
System altered.
SQL> alter system switch logfile;
System altered.
SQL> select name from v$datafile where file#=4;
NAME
--------------------------------------------------------------------------------
+DATA/xifenfei/datafile/users.259.878224279

dbms_diskgroup拷贝asm datafile 4 block 171

SQL> declare
  2  v_AsmFilename varchar2(4000);
  3  v_FsFilename varchar2(4000);
  4  v_offstart number;
  5  v_numblks number;
  6  v_filetype number;
  7  v_filesize number;
  8  v_lbks number;
  9  v_typename varchar2(4000);
 10  v_pblksize number;
 11  v_handle number;
 12  begin
 13  dbms_output.enable(500000);
 14  v_AsmFilename := '&ASM_File_Name';
 15  v_offstart := '&block_to_extract';
 16  v_numblks := '&number_of_blocks_to_extract';
 17  v_FsFilename := '&FileSystem_File_Name';
 18  dbms_diskgroup.getfileattr(v_AsmFilename,v_filetype,v_filesize,v_lbks);
 19  dbms_diskgroup.open(v_AsmFilename,'r',v_filetype,v_lbks,v_handle,v_pblksize,v_filesize);
 20  dbms_diskgroup.close(v_handle);
 21  select decode(v_filetype,1,'Control File',2,'Data File',3,'Online Log File',4,'Archive Log',5,'Trace File',6,'Temporary File',
 22  7,'Not Used',8,'Not Used',9,'Backup Piece',10,'Incremental Backup Piece',11,'Archive Backup Piece',12,'Data File Copy',
 23  13,'Spfile',14,'Disaster Recovery Configuration',15,'Storage Manager Disk',16,'Change Tracking File',17,'Flashback Log File',
 24  18,'DataPump Dump File',19,'Cross Platform Converted File',20,'Autobackup',21,'Any OS file',22,'Block Dump File',
 25  23,'CSS Voting File',24,'CRS') into v_typename from dual;
 26  dbms_output.put_line('File: '||v_AsmFilename); dbms_output.new_line;
 27  dbms_output.put_line('Type: '||v_filetype||' '||v_typename); dbms_output.new_line;
 28  dbms_output.put_line('Size (in logical blocks): '||v_filesize); dbms_output.new_line;
 29  dbms_output.put_line('Logical Block Size: '||v_lbks); dbms_output.new_line;
 30  dbms_output.put_line('Physical Block Size: '||v_pblksize); dbms_output.new_line;
 31  dbms_diskgroup.patchfile(v_AsmFilename,v_filetype,v_lbks,v_offstart,0,v_numblks,v_FsFilename,v_filetype,1,1);
 32  end;
 33  /
Enter value for asm_file_name: +DATA/xifenfei/datafile/users.259.878224279
old  14: v_AsmFilename := '&ASM_File_Name';
new  14: v_AsmFilename := '+DATA/xifenfei/datafile/users.259.878224279';
Enter value for block_to_extract: 171
old  15: v_offstart := '&block_to_extract';
new  15: v_offstart := '171';
Enter value for number_of_blocks_to_extract: 1
old  16: v_numblks := '&number_of_blocks_to_extract';
new  16: v_numblks := '1';
Enter value for filesystem_file_name: /tmp/xifenfei.dbf
old  17: v_FsFilename := '&FileSystem_File_Name';
new  17: v_FsFilename := '/tmp/xifenfei.dbf';
File: +DATA/xifenfei/datafile/users.259.878224279
Type: 12 Data File Copy
Size (in logical blocks): 640
Logical Block Size: 8192
Physical Block Size: 512
PL/SQL procedure successfully completed.
[grid@xifenfei ~]$ ls -l /tmp/xifenfei.dbf
-rw-r----- 1 grid oinstall 16384 Apr 28 15:55 /tmp/xifenfei.dbf

这里注意拷贝出来的block size 为8192,由于默认写了block 0信息，因此这里显示大小为2*block size=16384

bbed修改拷贝出来block内容

SQL> select dump('xifenfei',16) from dual;
DUMP('XIFENFEI',16)
-------------------------------------
Typ=96 Len=8: 78,69,66,65,6e,66,65,69
SQL> select dump('XIFENFEI',16) from dual;
DUMP('XIFENFEI',16)
-------------------------------------
Typ=96 Len=8: 58,49,46,45,4e,46,45,49
[oracle@xifenfei tmp]$ bbed filename='/tmp/xifenfei.dbf' blocksize=8192
Password:
BBED: Release 2.0.0.0.0 - Limited Production on Tue Apr 28 16:24:35 2015
Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.
************* !!! For Oracle Internal Use only !!! ***************
BBED> show
        FILE#           0
        BLOCK#          1
        OFFSET          0
        DBA             0x00000000 (0 0,1)
        FILENAME        /tmp/xifenfei.dbf
        BIFILE          bifile.bbd
        LISTFILE
        BLOCKSIZE       8192
        MODE            Browse
        EDIT            Unrecoverable
        IBASE           Dec
        OBASE           Dec
        WIDTH           80
        COUNT           512
        LOGFILE         log.bbd
        SPOOL           No
BBED> map
 File: /tmp/xifenfei.dbf (0)
 Block: 1                                     Dba:0x00000000
------------------------------------------------------------
 KTB Data Block (Table/Cluster)
 struct kcbh, 20 bytes                      @0
 struct ktbbh, 96 bytes                     @20
 struct kdbh, 14 bytes                      @124
 struct kdbt[1], 4 bytes                    @138
 sb2 kdbr[1]                                @142
 ub1 freespace[8024]                        @144
 ub1 rowdata[20]                            @8168
 ub4 tailchk                                @8188
BBED> p *kdbr[0]
rowdata[0]
----------
ub1 rowdata[0]                              @8168     0x2c
BBED> d /v offset 8168
 File: /tmp/xifenfei.dbf (0)
 Block: 1       Offsets: 8168 to 8191  Dba:0x00000000
-------------------------------------------------------
 2c000110 7777772e 78696665 6e666569 l ,...www.xifenfei
 2e636f6d 020624bc                   l .com..$.
 <16 bytes per line>
BBED> r /x c
BBED-00200: invalid keyword (r)
BBED> x /rc
rowdata[0]                                  @8168
----------
flag@8168: 0x2c (KDRHFL, KDRHFF, KDRHFH)
lock@8169: 0x00
cols@8170:    1
col   0[16] @8171: www.xifenfei.com
BBED> set mode edit
        MODE            Edit
BBED> set offset 8171
        OFFSET          8171
BBED> set count 32
        COUNT           32
BBED> d
 File: /tmp/xifenfei.dbf (0)
 Block: 1                Offsets: 8171 to 8191           Dba:0x00000000
------------------------------------------------------------------------
 10777777 2e786966 656e6665 692e636f 6d020624 bc
 <32 bytes per line>
BBED>
BBED> set offset +5
        OFFSET          8176
BBED> d
 File: /tmp/xifenfei.dbf (0)
 Block: 1                Offsets: 8176 to 8191           Dba:0x00000000
------------------------------------------------------------------------
 78696665 6e666569 2e636f6d 020624bc
 <32 bytes per line>
BBED> m /x 58494645
 File: /tmp/xifenfei.dbf (0)
 Block: 1                Offsets: 8176 to 8191           Dba:0x00000000
------------------------------------------------------------------------
 58494645 6e666569 2e636f6d 020624bc
 <32 bytes per line>
BBED> set offset +4
        OFFSET          8180
BBED> d
 File: /tmp/xifenfei.dbf (0)
 Block: 1                Offsets: 8180 to 8191           Dba:0x00000000
------------------------------------------------------------------------
 6e666569 2e636f6d 020624bc
 <32 bytes per line>
BBED> m /x 4e464549
 File: /tmp/xifenfei.dbf (0)
 Block: 1                Offsets: 8180 to 8191           Dba:0x00000000
------------------------------------------------------------------------
 4e464549 2e636f6d 020624bc
 <32 bytes per line>
BBED> d /v offset 8168
 File: /tmp/xifenfei.dbf (0)
 Block: 1       Offsets: 8168 to 8191  Dba:0x00000000
-------------------------------------------------------
 2c000110 7777772e 58494645 4e464549 l ,...www.XIFENFEI
 2e636f6d 020624bc                   l .com..$.
 <16 bytes per line>
BBED> x /rc
rowdata[0]                                  @8168
----------
flag@8168: 0x2c (KDRHFL, KDRHFF, KDRHFH)
lock@8169: 0x00
cols@8170:    1
col   0[16] @8171: www.XIFENFEI.com
BBED> sum apply
Check value for File 0, Block 1:
current = 0x3060, required = 0x3060

这里通过bbed把拷贝出来的datafile 4 block 171中的www.xifenfei.com修改为www.XIFENFEI.com

dbms_diskgroup拷贝os block to asm datafile 4 block 171

SQL> declare
  2  v_FsFileName varchar2(4000);
  3  v_AsmFileName varchar2(4000);
  4  v_FsFileType number;
  5  v_AsmFileType number;
  6  v_offstart number;
  7  v_filesize number;
  8  v_lbks number;
  9  v_typename varchar2(4000);
 10  v_handle number;
 11  error number;
 12  txt varchar2(4000);
 13  begin
 14  dbms_output.enable(500000);
 15  v_FsFileName := '&file_with_patched_block';
 16  v_AsmFileName := '&file_to_patch_in_ASM';
 17  v_offstart := '&block_to_patch';
 18  dbms_diskgroup.getfileattr(v_AsmFileName,v_AsmFileType,v_filesize,v_lbks);
 19  select decode(v_AsmFileType,1,'Control File',2,'Data File',3,'Online Log File',4,'Archive Log',5,'Trace File',6,'Temporary File',
 20  7,'Not Used',8,'Not Used',9,'Backup Piece',10,'Incremental Backup Piece',11,'Archive Backup Piece',12,'Data File Copy',
 21  13,'Spfile',14,'Disaster Recovery Configuration',15,'Storage Manager Disk',16,'Change Tracking File',
 22  17,'Flashback Log File',
 23  18,'DataPump Dump File',19,'Cross Platform Converted File',20,'Autobackup',21,'Any OS file',22,'Block Dump File',
 24  23,'CSS Voting File',24,'CRS') into v_typename from dual;
 25  dbms_output.put_line('File: '||v_AsmFileName); dbms_output.new_line;
 26  dbms_output.put_line('Type: '||v_AsmFileType||' '||v_typename); dbms_output.new_line;
 27  dbms_output.put_line('Size: '||v_filesize); dbms_output.new_line;
 28  dbms_output.put_line('Logical Block Size: '||v_lbks); dbms_output.new_line;
 29  dbms_diskgroup.patchfile(v_FsFileName,12,v_lbks,1,0,1,v_AsmFileName,v_AsmFileType,v_offstart,0);
 30  end;
 31  /
Enter value for file_with_patched_block: /tmp/xifenfei.dbf
old  15: v_FsFileName := '&file_with_patched_block';
new  15: v_FsFileName := '/tmp/xifenfei.dbf';
Enter value for file_to_patch_in_asm: +DATA/xifenfei/datafile/users.259.878224279
old  16: v_AsmFileName := '&file_to_patch_in_ASM';
new  16: v_AsmFileName := '+DATA/xifenfei/datafile/users.259.878224279';
Enter value for block_to_patch: 171
old  17: v_offstart := '&block_to_patch';
new  17: v_offstart := '171';
File: +DATA/xifenfei/datafile/users.259.878224279
Type: 12 Data File Copy
Size: 640
Logical Block Size: 8192
PL/SQL procedure successfully completed.

验证修改block是否正确

SQL> shutdown abort
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
Total System Global Area  952020992 bytes
Fixed Size                  2258960 bytes
Variable Size             306186224 bytes
Database Buffers          637534208 bytes
Redo Buffers                6041600 bytes
Database mounted.
Database opened.
SQL> select rowid,xifenfei from t_xifenfei;
ROWID              XIFENFEI
------------------ ----------------
AAAVU3AAEAAAACrAAA www.XIFENFEI.com

dbms_diskgroup拷贝asm datafile to os

SQL> declare
  2  v_AsmFileName varchar2(4000);
  3  v_FsFileName varchar2(4000);
  4  v_filetype number;
  5  v_filesize number;
  6  v_lbks number;
  7  v_typename varchar2(4000);
  8  v_pblksize number;
  9  v_handle number;
 10  begin
 11  dbms_output.enable(500000);
 12  v_AsmFileName := '&ASM_file_name';
 13  v_FsFileName := '&FileSystem_file_name';
 14  dbms_diskgroup.getfileattr(v_AsmFileName,v_filetype,v_filesize,v_lbks);
 15  dbms_diskgroup.open(v_AsmFileName,'r',v_filetype,v_lbks,v_handle,v_pblksize,v_filesize);
 16  dbms_diskgroup.close(v_handle);
 17  select decode(v_filetype,1,'Control File',2,'Data File',3,'Online Log File',4,'Archive Log',5,'Trace File',6,'Temporary File',
 18  7,'Not Used',8,'Not Used',9,'Backup Piece',10,'Incremental Backup Piece',11,'Archive Backup Piece',12,'Data File Copy',
 19  13,'Spfile',14,'Disaster Recovery Configuration',15,'Storage Manager Disk',16,'Change Tracking File',17,'Flashback Log File',
 20  18,'DataPump Dump File',19,'Cross Platform Converted File',20,'Autobackup',21,'Any OS file',22,'Block Dump File',
 21  23,'CSS Voting File',24,'CRS') into v_typename from dual;
 22  dbms_output.put_line('File: '||v_AsmFileName); dbms_output.new_line;
 23  dbms_output.put_line('Type: '||v_filetype||' '||v_typename); dbms_output.new_line;
 24  dbms_output.put_line('Size (in logical blocks): '||v_filesize); dbms_output.new_line;
 25  dbms_output.put_line('Logical Block Size: '||v_lbks); dbms_output.new_line;
 26  dbms_output.put_line('Physical Block Size: '||v_pblksize); dbms_output.new_line;
 27  dbms_diskgroup.patchfile(v_AsmFileName,v_filetype,v_lbks,1,0,v_filesize,v_FsFileName,2,1,1);
 28  end;
 29  /
Enter value for asm_file_name: +DATA/xifenfei/datafile/users.259.878224279
old  12: v_AsmFileName := '&ASM_file_name';
new  12: v_AsmFileName := '+DATA/xifenfei/datafile/users.259.878224279';
Enter value for filesystem_file_name: /tmp/users01.dbf
old  13: v_FsFileName := '&FileSystem_file_name';
new  13: v_FsFileName := '/tmp/users01.dbf';
File: +DATA/xifenfei/datafile/users.259.878224279
Type: 12 Data File Copy
Size (in logical blocks): 640
Logical Block Size: 8192
Physical Block Size: 512
PL/SQL procedure successfully completed.
[grid@xifenfei ~]$ ls -l /tmp/users01.dbf
-rw-r----- 1 grid oinstall 5251072 Apr 28 16:39 /tmp/users01.dbf

通过上述几个简单例子说明:dbms_diskgroup可以看asm file的属性,可以拷贝asm中的datafile中的某个block到os,也可以从os拷贝到asm,可以从asm中直接拷贝文件文件到os等功能

Oracle 12.1.0.2 的OLAP API组件无效的处理过程

数据库从11.2.0.3.x升级到12.1.0.2.3之时出现如下错误,Oracle OLAP API组件无效

Oracle Database 12.1 Post-Upgrade Status Tool           04-21-2015 10:58:50
Component                               Current         Version  Elapsed Time
Name                                    Status          Number   HH:MM:SS
Oracle Server                          UPGRADED      12.1.0.2.0  00:36:20
JServer JAVA Virtual Machine              VALID      12.1.0.2.0  00:08:19
Oracle Real Application Clusters          VALID      12.1.0.2.0  00:00:02
Oracle Workspace Manager                  VALID      12.1.0.2.0  00:01:25
OLAP Analytic Workspace                   VALID      12.1.0.2.0  00:00:24
OLAP Catalog                         OPTION OFF      11.2.0.3.0  00:00:00
Oracle OLAP API
    ORA-00600: internal error code, arguments: [qkaQknLTPruneKaf:1], [], [], []
Oracle OLAP API                         INVALID      12.1.0.2.0  00:01:37
Oracle XDK                                VALID      12.1.0.2.0  00:00:56
Oracle Text                               VALID      12.1.0.2.0  00:01:14
Oracle XML Database                       VALID      12.1.0.2.0  00:03:13
Oracle Database Java Packages             VALID      12.1.0.2.0  00:00:18
Oracle Multimedia                         VALID      12.1.0.2.0  00:03:03
Spatial                                UPGRADED      12.1.0.2.0  00:06:41
Final Actions                                                    00:10:10
Total Upgrade Time: 01:24:48

这里主要是OLAP API组件无效,OLAP Catalog组件官方已经有明确说明,12c里面已经不支持,可以升级之后把其卸载。分析alert日志，发现ora-600[qkaQknLTPruneKaf:1]错误

Tue Apr 21 10:15:55 2015
SERVER COMPONENT id=CATPROC: timestamp=2015-04-21 10:15:54
Tue Apr 21 10:16:06 2015
SERVER COMPONENT id=RDBMS: status=UPGRADED, version=12.1.0.2.0, timestamp=2015-04-21 10:16:06
Tue Apr 21 10:16:53 2015
Thread 1 advanced to log sequence 15 (LGWR switch)
  Current log# 9 seq# 15 mem# 0: /oradata/redo/zjrpt/redo09.dbf
Tue Apr 21 10:24:27 2015
SERVER COMPONENT id=JAVAVM: status=VALID, version=12.1.0.2.0, timestamp=2015-04-21 10:24:27
Tue Apr 21 10:24:44 2015
### queuing purge of JIT compilation due to creation of 700010c40827818 oracle/xml/util/XMLUtil
### queuing purge of JIT compilation due to creation of 700010c508b7910 oracle/xdb/XMLType
Tue Apr 21 10:25:10 2015
### jox_purge_jit pid 41746460 pdb 0
Tue Apr 21 10:25:27 2015
SERVER COMPONENT id=XML: status=VALID, version=12.1.0.2.0, timestamp=2015-04-21 10:25:27
Tue Apr 21 10:25:33 2015
Shared IO Pool defaulting to 512MB. Trying to get it from Buffer Cache for process 42139652.
Tue Apr 21 10:25:52 2015
SERVER COMPONENT id=APS: status=VALID, version=12.1.0.2.0, timestamp=2015-04-21 10:25:52
SERVER COMPONENT id=AMD: status=OPTION OFF, version=11.2.0.3.0, timestamp=2015-04-21 10:25:53
Tue Apr 21 10:27:08 2015
SERVER COMPONENT id=CONTEXT: status=VALID, version=12.1.0.2.0, timestamp=2015-04-21 10:27:08
Tue Apr 21 10:27:55 2015
XDB installed.
XDB initialized.
Tue Apr 21 10:30:22 2015
SERVER COMPONENT id=XDB: status=VALID, version=12.1.0.2.0, timestamp=2015-04-21 10:30:22
Tue Apr 21 10:30:42 2015
SERVER COMPONENT id=CATJAVA: status=VALID, version=12.1.0.2.0, timestamp=2015-04-21 10:30:42
Tue Apr 21 10:32:07 2015
SERVER COMPONENT id=OWM: status=VALID, version=12.1.0.2.0, timestamp=2015-04-21 10:32:07
SERVER COMPONENT id=RAC: status=VALID, version=12.1.0.2.0, timestamp=2015-04-21 10:32:10
Tue Apr 21 10:35:15 2015
SERVER COMPONENT id=ORDIM: status=VALID, version=12.1.0.2.0, timestamp=2015-04-21 10:35:15
Tue Apr 21 10:37:59 2015
Thread 1 cannot allocate new log, sequence 16
Private strand flush not complete
  Current log# 9 seq# 15 mem# 0: /oradata/redo/zjrpt/redo09.dbf
Tue Apr 21 10:38:02 2015
Thread 1 advanced to log sequence 16 (LGWR switch)
  Current log# 10 seq# 16 mem# 0: /oradata/redo/zjrpt/redo10.dbf
Tue Apr 21 10:41:58 2015
SERVER COMPONENT id=SDO: status=UPGRADED, version=12.1.0.2.0, timestamp=2015-04-21 10:41:58
Tue Apr 21 10:42:58 2015
Errors in file /u04/oracle/app/oracle/diag/rdbms/zjrpt/zjrpt1/trace/zjrpt1_ora_39125322.trc  (incident=48369):
ORA-00600: internal error code, arguments: [qkaQknLTPruneKaf:1], [], [], [], [], [], [], [], [], [], [], []
Incident details in: /u04/oracle/app/oracle/diag/rdbms/zjrpt/zjrpt1/incident/incdir_48369/zjrpt1_ora_39125322_i48369.trc
Tue Apr 21 10:44:09 2015
Errors in file /u04/oracle/app/oracle/diag/rdbms/zjrpt/zjrpt1/incident/incdir_48369/zjrpt1_ora_39125322_i48369.trc:
ORA-00339: archived log does not contain any redo
ORA-00334: archived log: '/oradata/redo/zjrpt/redo14.dbf'
ORA-00600: internal error code, arguments: [qkaQknLTPruneKaf:1], [], [], [], [], [], [], [], [], [], [], []
Tue Apr 21 10:44:09 2015
Dumping diagnostic data in directory=[cdmp_20150421104409], requested by (instance=1, osid=14811583), summary=[incident=48369].
Use ADRCI or Support Workbench to package the incident.
See Note 411.1 at My Oracle Support for error and packaging details.
Tue Apr 21 10:44:09 2015
SERVER COMPONENT id=XOQ: status=INVALID, version=12.1.0.2.0, timestamp=2015-04-21 10:44:11
Tue Apr 21 10:44:12 2015
SERVER COMPONENT id=ACTIONS_BGN: timestamp=2015-04-21 10:44:12
Tue Apr 21 10:52:06 2015
SERVER COMPONENT id=CATREQ_BGN: timestamp=2015-04-21 10:52:06
Tue Apr 21 10:54:06 2015
SERVER COMPONENT id=CATREQ_END: timestamp=2015-04-21 10:54:06
Tue Apr 21 10:54:23 2015
SERVER ACTION=UPGRADE id=: Upgraded from 11.2.0.3.0
SERVER COMPONENT id=ACTIONS_END: timestamp=2015-04-21 10:54:23
SERVER COMPONENT id=UPGRD_END: timestamp=2015-04-21 10:54:23

这里提示比较明显,XOQ组件由于ORA-600错误,未升级成功

分析trace文件

Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP, Advanced Analytics
and Real Application Testing options
ORACLE_HOME = /u04/oracle/app/oracle/product/12.1
System name: AIX
Node name: zjddrpt5
Release: 1
Version: 7
Machine: 00CB9D064C00
Instance name: zjrpt1
Redo thread mounted by this instance: 1
Oracle process number: 40
Unix process pid: 36765856, image: oracle@zjddrpt5 (TNS V1-V3)
*** 2015-04-21 11:27:29.567
*** SESSION ID:(2258.29003) 2015-04-21 11:27:29.567
*** CLIENT ID:() 2015-04-21 11:27:29.567
*** SERVICE NAME:(SYS$USERS) 2015-04-21 11:27:29.567
*** MODULE NAME:(catcon(pid=3473480)) 2015-04-21 11:27:29.567
*** CLIENT DRIVER:(SQL*PLUS) 2015-04-21 11:27:29.567
*** ACTION NAME:(non-CDB::@cmpupmsc.sql) 2015-04-21 11:27:29.567
[TOC00000]
Jump to table of contents
Dump continued from file: /u04/oracle/app/oracle/diag/rdbms/zjrpt/zjrpt1/trace/zjrpt1_ora_36765856.trc
[TOC00001]
ORA-00600: internal error code, arguments: [qkaQknLTPruneKaf:1], [], [], [], [], [], [], [], [], [], [], []
[TOC00001-END]
[TOC00002]
========= Dump for incident 192322 (ORA 600 [qkaQknLTPruneKaf:1]) ========
*** 2015-04-21 11:27:29.615
dbkedDefDump(): Starting incident default dumps (flags=0x2, level=3, mask=0x0)
[TOC00003]
----- Current SQL Statement for this session (sql_id=5s1pbm9ykxmjx) -----
SELECT COUNT(*) FROM DBA_TAB_PRIVS WHERE GRANTEE='OLAP_XS_ADMIN' AND ((PRIVILEGE='SELECT' AND OWNER='SYS' AND
TABLE_NAME='XS$OLAP_POLICY' AND COMMON='YES') OR (PRIVILEGE='SELECT' AND OWNER='SYS' AND
TABLE_NAME='DBA_ROLES' AND COMMON='YES') OR (PRIVILEGE='EXECUTE' AND OWNER='SYS' AND TABLE_NAME='DBMS_XDS'
AND COMMON='YES'))
[TOC00004]
----- PL/SQL Stack -----
----- PL/SQL Call Stack -----
object line object
handle number name
700010c76ef4290 171 procedure SYS.XOQ_VALIDATE
700010c67f9aa90 2 anonymous block
[TOC00004-END]
[TOC00003-END]
[TOC00005]
----- Call Stack Trace -----
skdstdst <- ksedst1 <- ksedst <- dbkedDefDump <- ksedmp
<- ksfdmp <- dbgexPhaseII <- dbgexProcessError <- dbgeExecuteForError <- dbgePostErrorKGE
<- 1244 <- dbkePostKGE_kgsf <- kgeadse <- kgerinv_internal <- kgerinv
<- kgeasnmierr <- qkaQknLTPruneKaf <- 780 <- qkaQknPruneKaf <- qknProjPushNode_Int
<- qknProjPushNode <- qkeWalkAllQueryNode <- qknProjPushStmt <- qkeProjPrune <- qkadrv2
<- opitca <- kksFullTypeCheck <- rpiswu2 <- kksLoadChild <- kxsGetRuntimeLock
<- kksfbc <- kkspbd0 <- kksParseCursor <- opiosq0 <- opipls
<- opiodr <- rpidrus <- skgmstack <- rpidru <- rpiswu2
<- rpidrv <- psddr0 <- psdnal <- pevm_EXECC <- pfrinstr_EXECC
<- pfrrun_no_tool <- pfrrun <- plsql_run <- peicnt <- kkxexe
<- opiexe <- kpoal8 <- opiodr <- ttcpip <- opitsk
<- opiino <- opiodr <- opidrv <- sou2o <- opimai_real
<- ssthrdmain <- main <- start <-

这里可以大概看出来，出现该问题，可能和SYS.XOQ_VALIDATE有关系.既然OLAP组件无效,那么我们是否可以尝试删除组件,然后重装OLAP组件
卸载OLAP组件

sqlplus /nolog
SQL> conn / as sysdba
SQL> spool remove_olap.log
----> Remove OLAP Catalog
SQL> @?/olap/admin/catnoamd.sql
----> Remove OLAP API
SQL> @?/olap/admin/olapidrp.plb
SQL> @?/olap/admin/catnoxoq.sql
----> Deinstall APS - OLAP AW component
SQL> @?/olap/admin/catnoaps.sql
----> Recompile invalids
SQL> @?/rdbms/admin/utlrp.sql
SQL> spool off

安装OLAP组件

SQL> @?/olap/admin/olap.sql SYSAUX TEMP

分析安装日志发现创建用户错误

old 3: execute immediate 'select tablespace_name from dba_tablespaces where tablespace_name = upper(''&1'')
and contents = ''PERMANENT''' into :default_ts;
new 3: execute immediate 'select tablespace_name from dba_tablespaces where tablespace_name =
upper(''SYSAUX'') and contents = ''PERMANENT''' into :default_ts;
old 10: execute immediate 'select tablespace_name from dba_tablespaces where tablespace_name = upper(''&2'')
and contents = ''TEMPORARY''' into :temp_ts;
new 10: execute immediate 'select tablespace_name from dba_tablespaces where tablespace_name =
upper(''TEMP'') and contents = ''TEMPORARY''' into :temp_ts;
PL/SQL procedure successfully completed.
Session altered.
old 10: || ' default tablespace ' || '&1'
new 10: || ' default tablespace ' || 'SYSAUX'
old 11: || ' temporary tablespace ' || '&2'
new 11: || ' temporary tablespace ' || 'TEMP'
old 12: || ' quota unlimited on ' || '&1';
new 12: || ' quota unlimited on ' || 'SYSAUX';
DECLARE
*
ERROR at line 1:
ORA-28003: password verification for the specified password failed
ORA-20008: Password must contain at least one digit, one character
ORA-06512: at line 8
…………………………
ERROR at line 1:
ORA-01435: user does not exist

通过分析olap对应的创建语句,发现会在olap安装过程中,如果没有olapsys用户,数据库会自己创建该用户

DECLARE
  isthere     NUMBER;
BEGIN
  select count(*) into isthere from dba_users where username ='OLAPSYS';
  if isthere = 0 then
    execute immediate 'create user olapsys identified by no_password'
                  || ' password expire account lock'
                  || ' default tablespace ' || '&1'
                  || ' temporary tablespace ' || '&2'
                  || ' quota unlimited on ' || '&1';
  end if;
END;
/

这里比较明显的显示了创建用户olapsys 状态为Lock，密码为no_password,根据错误提示,应该是该密码不符合要求,对其进行测试

zjddrpt5:/u04/oracle/app/oracle/product/12.1/olap/admin$sqlplus / as sysdba
SQL*Plus: Release 12.1.0.2.0 Production on Wed Apr 22 16:40:25 2015
Copyright (c) 1982, 2014, Oracle.  All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP, Advanced Analytics
and Real Application Testing options
SQL> create user xifenfei identified by no_password;
create user xifenfei identified by no_password
*
ERROR at line 1:
ORA-28003: password verification for the specified password failed
ORA-20008: Password must contain at least one digit, one character
SQL> create user xifenfei identified by "1qsx!qaz";
User created.

分析密码验证原因

SQL> select * from dba_profiles where profile='DEFAULT';
PROFILE                        RESOURCE_NAME                    RESOURCE LIMIT                                              COM
------------------------------ -------------------------------- -------- -------------------------------------------------- ---
DEFAULT                        COMPOSITE_LIMIT                  KERNEL   UNLIMITED                                          NO
DEFAULT                        SESSIONS_PER_USER                KERNEL   UNLIMITED                                          NO
DEFAULT                        CPU_PER_SESSION                  KERNEL   UNLIMITED                                          NO
DEFAULT                        CPU_PER_CALL                     KERNEL   UNLIMITED                                          NO
DEFAULT                        LOGICAL_READS_PER_SESSION        KERNEL   UNLIMITED                                          NO
DEFAULT                        LOGICAL_READS_PER_CALL           KERNEL   UNLIMITED                                          NO
DEFAULT                        IDLE_TIME                        KERNEL   UNLIMITED                                          NO
DEFAULT                        CONNECT_TIME                     KERNEL   UNLIMITED                                          NO
DEFAULT                        PRIVATE_SGA                      KERNEL   UNLIMITED                                          NO
DEFAULT                        FAILED_LOGIN_ATTEMPTS            PASSWORD UNLIMITED                                          NO
DEFAULT                        PASSWORD_LIFE_TIME               PASSWORD UNLIMITED                                          NO
DEFAULT                        PASSWORD_REUSE_TIME              PASSWORD UNLIMITED                                          NO
DEFAULT                        PASSWORD_REUSE_MAX               PASSWORD UNLIMITED                                          NO
DEFAULT                        PASSWORD_VERIFY_FUNCTION         PASSWORD VERIFY_FUNCTION_11G                                NO
DEFAULT                        PASSWORD_LOCK_TIME               PASSWORD UNLIMITED                                          NO
DEFAULT                        PASSWORD_GRACE_TIME              PASSWORD UNLIMITED                                          NO
16 rows selected.

因为数据库在创建用户之时未指定profile,而默认的profile密码设置了验证,因此olap安装脚本创建olapsys用户的密码为no_password无法成功,因此该用户无法创建.基于此，那我尝试人工创建olapsys用户,然后再执行olap.sql 试试看

SQL> create user olapsys identified by "1qsx!qaz" password
   2 expire account lock default tablespace SYSAUX temporary tablespace TEMP  quota unlimited on SYSAUX;
User created.
SQL> @?/olap/admin/olap.sql SYSAUX TEMP

分析执行日志文件,发现均为对象not exist情况的错误,类似

ORA-04043: object COAD_ADVICE_T does not exist
ORA-04043: object COAD_ADVICE_REC does not exist
ORA-01432: public synonym to be dropped does not exist
ORA-00942: table or view does not exist
ORA-01432: public synonym to be dropped does not exist
ORA-00942: table or view does not exist

这里证明安装过程未出错,查询无效对象

select owner,object_name,object_type from dba_objects where status=’INVALID’
[/sql]
也未发现任何sys/system/olapsys中发现任何无效对象,但是OLAP API依然无效,陷入了误解中

SQL> select COMP_ID,COMP_NAME,VERSION,STATUS from dba_registry;
COMP_ID
------------------------------
COMP_NAME
--------------------------------------------------------------------------------
VERSION                        STATUS
------------------------------ ----------------------
SDO
Spatial
12.1.0.2.0                     VALID
ORDIM
Oracle Multimedia
12.1.0.2.0                     VALID
COMP_ID
------------------------------
COMP_NAME
--------------------------------------------------------------------------------
VERSION                        STATUS
------------------------------ ----------------------
XDB
Oracle XML Database
12.1.0.2.0                     VALID
CONTEXT
Oracle Text
COMP_ID
------------------------------
COMP_NAME
--------------------------------------------------------------------------------
VERSION                        STATUS
------------------------------ ----------------------
12.1.0.2.0                     VALID
OWM
Oracle Workspace Manager
12.1.0.2.0                     VALID
CATALOG
COMP_ID
------------------------------
COMP_NAME
--------------------------------------------------------------------------------
VERSION                        STATUS
------------------------------ ----------------------
Oracle Database Catalog Views
12.1.0.2.0                     VALID
CATPROC
Oracle Database Packages and Types
12.1.0.2.0                     VALID
COMP_ID
------------------------------
COMP_NAME
--------------------------------------------------------------------------------
VERSION                        STATUS
------------------------------ ----------------------
JAVAVM
JServer JAVA Virtual Machine
12.1.0.2.0                     VALID
XML
Oracle XDK
12.1.0.2.0                     VALID
COMP_ID
------------------------------
COMP_NAME
--------------------------------------------------------------------------------
VERSION                        STATUS
------------------------------ ----------------------
CATJAVA
Oracle Database Java Packages
12.1.0.2.0                     VALID
APS
OLAP Analytic Workspace
COMP_ID
------------------------------
COMP_NAME
--------------------------------------------------------------------------------
VERSION                        STATUS
------------------------------ ----------------------
12.1.0.2.0                     VALID
XOQ
Oracle OLAP API
12.1.0.2.0                     INVALID
RAC
COMP_ID
------------------------------
COMP_NAME
--------------------------------------------------------------------------------
VERSION                        STATUS
------------------------------ ----------------------
Oracle Real Application Clusters
12.1.0.2.0                     VALID
13 rows selected.

到这一步已经超过了我的能力和大连gcs的能力范围,sr升级到美国olap研发团队,配合他们进行分析

CREATE OR REPLACE PROCEDURE xoq_validate_verbose IS
compat VARCHAR2(30);
dummy_num NUMBER;
dummy_out_1_str VARCHAR2(100);
dummy_out_2_str VARCHAR2(100);
ok BOOLEAN := TRUE;
BEGIN
-- check compatible
SELECT value INTO compat FROM v$parameter WHERE name='compatible';
IF NOT (substr(compat,1,3) >= '9.2' OR substr(compat,1,2) >= '10') THEN
ok := FALSE;
END IF;
dbms_output.put_line('compatible:'||compat||' ok:'|| case when ok then 'True' else 'False' end);
IF ok THEN
--check for errors during installation/upgrade
BEGIN
SELECT 0 INTO dummy_num from sys.registry$error
WHERE identifier='XOQ'AND rownum <=1;
-- at least one install error was found so component is invalid
ok := FALSE;
dbms_output.put_line('xoq errors during installation/upgrade. Query sys.registry$error WHERE identifier euqal to XOQ. ok:'|| case when ok then 'True' else 'False' end);
EXCEPTION
WHEN NO_DATA_FOUND THEN
dbms_output.put_line('No install errors were found so component remains valid. ok:'|| case when ok then 'True' else 'False' end);
NULL;
END;
END IF;
IF ok THEN
-- check that dependent component XDB is valid
IF dbms_registry.is_valid('XDB', dbms_registry.release_version) != 1 THEN
ok := FALSE;
dbms_output.put_line('dependent component XDB is invalid. ok:'|| case when ok then 'True' else 'False' end);
END IF;
END IF;
IF ok THEN
-- check that expected XDB resources are there
IF NOT (dbms_xdb.existsresource('/OLAP_XDS/dsclass.xml') AND
dbms_xdb.existsresource('/olap_data_security/public/acls') AND
dbms_xdb.existsresource('/xds/dsd')) THEN
ok := FALSE;
dbms_output.put_line('expected XDB resources are not there. ok:'|| case when ok then 'True' else 'False' end);
END IF;
END IF;
IF ok THEN
-- check that installed library is valid
BEGIN
SELECT 0 INTO dummy_num FROM DBA_LIBRARIES
WHERE STATUS = 'INVALID' AND rownum <=1 AND
OWNER='SYS' AND LIBRARY_NAME = 'DBMS_OLAPI_LIB';
-- at least one object is invalid so component is invalid
ok := FALSE;
dbms_output.put_line('installed library is invalid. ok:'|| case when ok then 'True' else 'False' end);
EXCEPTION
WHEN NO_DATA_FOUND THEN
-- no invalid objects were found so component remains valid
NULL;
END;
END IF;
IF ok THEN
-- check very basic OLAP API function (including load of shared library)
BEGIN
dummy_num := OlapiBootstrap2(compat, dummy_out_1_str, dummy_out_2_str);
EXCEPTION
WHEN OTHERS THEN
ok := FALSE;
DBMS_OUTPUT.PUT_LINE(sqlerrm);
dbms_output.put_line('Error during OlapiBootstrap2. ok:'|| case when ok then 'True' else 'False' end);
END;
END IF;
IF ok THEN
-- check that Java classes are loaded successfully
BEGIN
SELECT 0 INTO dummy_num FROM dba_objects
WHERE owner = 'SYS' AND
status = 'INVALID' AND
object_type = 'JAVA CLASS' AND
object_name LIKE 'oracle/AWXML/%';
-- at least one class is invalid so component is invalid
ok := FALSE;
dbms_output.put_line('oracle/AWXML/.. Java class invalid. ok:'|| case when ok then 'True' else 'False' end);
EXCEPTION
WHEN NO_DATA_FOUND THEN
-- no invalid components were found so component remains valid
NULL;
END;
END IF;
IF ok THEN
-- check that installed types, packages, and procedures are valid
BEGIN
SELECT 0 INTO dummy_num FROM DBA_OBJECTS
WHERE STATUS = 'INVALID' AND rownum <=1 AND
OWNER='SYS' AND OBJECT_NAME IN
('DBMS_CUBE_ADVISE','DBMS_CUBE_ADVISE_SEC','DBMS_CUBE',
'DBMS_CUBE_EXP','GENDATABASEINTERFACE','GENCONNECTIONINTERFACE',
'GENSERVERINTERACE','GENMDMPROPERTYIDCONSTANTS',
'GENMDMCLASSCONSTANTS','GENMDMOBJECTIDCONSTANTS',
'GENMETADATAPROVIDERINTERFACE','GENCURSORMANAGERINTERFACE',
'GENDATATYPEIDCONSTANTS','GENDEFINITIONMANAGERINTERFACE',
'GENDATAPROVIDERINTERFACE','DBMS_AW_XML','DBMS_CUBE_UTIL',
'COAD_ADVICE_T','COAD_ADVICE_REC','GENOLAPIEXCEPTION',
'GENINTERFACESTUB', 'GENINTERFACESTUBSEQUENCE',
'GENRAWSEQUENCE','GENWSTRINGSEQUENCE',
'DBMS_CUBE_UTIL_EXT_MD_T','DBMS_CUBE_UTIL_EXT_MD_R',
'OLAPIHANDSHAKE2','OLAPIBOOTSTRAP2');
-- at least one object is invalid so component is invalid
ok := FALSE;
dbms_output.put_line('Olap type, packages or procedure is invalid. ok:'|| case when ok then 'True' else 'False' end);
EXCEPTION
WHEN NO_DATA_FOUND THEN
-- no invalid objects were found so component remains valid
NULL;
END;
END IF;
IF ok THEN
-- check for expected role
BEGIN
SELECT 0 INTO dummy_num FROM DBA_ROLES
WHERE ROLE = 'OLAP_XS_ADMIN';
EXCEPTION
WHEN NO_DATA_FOUND THEN
ok := FALSE;
dbms_output.put_line('OLAP_XS_ADMIN role is missing. ok:'|| case when ok then 'True' else 'False' end);
END;
END IF;
IF ok THEN
SELECT COUNT(*) INTO dummy_num FROM DBA_TAB_PRIVS WHERE GRANTEE='OLAP_XS_ADMIN' AND (
(PRIVILEGE='EXECUTE' AND OWNER='SYS' AND TABLE_NAME='AWM_CREATEXDSFOLDER') OR
(PRIVILEGE='SELECT' AND OWNER='SYS' AND TABLE_NAME='DBA_ROLES') OR
(PRIVILEGE='SELECT' AND OWNER='SYS' AND TABLE_NAME='DBA_XDS_INSTANCE_SETS') OR
(PRIVILEGE='EXECUTE' AND OWNER='SYS' AND TABLE_NAME='DBMS_XDS') OR
(PRIVILEGE='EXECUTE' AND OWNER='SYS' AND TABLE_NAME='DBMS_XS_DATA_SECURITY_EVENTS') OR
(PRIVILEGE='EXECUTE' AND OWNER='SYS' AND TABLE_NAME='DBMS_XS_PRIVID_LIST') OR
(PRIVILEGE='EXECUTE' AND OWNER='SYS' AND TABLE_NAME='DBMS_XS_ROLESET_EVENTS_INT') OR
(PRIVILEGE='EXECUTE' AND OWNER='SYS' AND TABLE_NAME='DBMS_XS_SECCLASS_EVENTS') OR
(PRIVILEGE='EXECUTE' AND OWNER='SYS' AND TABLE_NAME='DBMS_XS_SECCLASS_INT') OR
(PRIVILEGE='EXECUTE' AND OWNER='XDB' AND TABLE_NAME='DBMS_XDB') OR
(PRIVILEGE='UPDATE' AND OWNER='XDB' AND TABLE_NAME='XDB$ACL') OR
(PRIVILEGE='DELETE' AND OWNER='XDB' AND TABLE_NAME='XDB$ACL') OR
(PRIVILEGE='INSERT' AND OWNER='XDB' AND TABLE_NAME='XDB$ACL') OR
(PRIVILEGE='SELECT' AND OWNER='XDB' AND TABLE_NAME='XDB$ACL') OR
(PRIVILEGE='DELETE' AND OWNER='XDB' AND TABLE_NAME='XS$DATA_SECURITY') OR
(PRIVILEGE='UPDATE' AND OWNER='XDB' AND TABLE_NAME='XS$DATA_SECURITY') OR
(PRIVILEGE='INSERT' AND OWNER='XDB' AND TABLE_NAME='XS$DATA_SECURITY') OR
(PRIVILEGE='SELECT' AND OWNER='XDB' AND TABLE_NAME='XS$DATA_SECURITY') OR
(PRIVILEGE='DELETE' AND OWNER='XDB' AND TABLE_NAME='XS$PRINCIPALS') OR
(PRIVILEGE='UPDATE' AND OWNER='XDB' AND TABLE_NAME='XS$PRINCIPALS') OR
(PRIVILEGE='SELECT' AND OWNER='XDB' AND TABLE_NAME='XS$PRINCIPALS') OR
(PRIVILEGE='INSERT' AND OWNER='XDB' AND TABLE_NAME='XS$PRINCIPALS') OR
(PRIVILEGE='DELETE' AND OWNER='XDB' AND TABLE_NAME='XS$SECURITYCLASS') OR
(PRIVILEGE='INSERT' AND OWNER='XDB' AND TABLE_NAME='XS$SECURITYCLASS') OR
(PRIVILEGE='UPDATE' AND OWNER='XDB' AND TABLE_NAME='XS$SECURITYCLASS') OR
(PRIVILEGE='SELECT' AND OWNER='XDB' AND TABLE_NAME='XS$SECURITYCLASS'));
IF dummy_num != 26 THEN
ok := FALSE;
dbms_output.put_line('OLAP_XS_ADMIN does necessary privs. ok:'|| case when ok then 'True' else 'False' end);
END IF;
END IF;
END;
/
SQL> set serveroutput on size 10000
SQL> exec xoq_validate_verbose;
compatible:12.1.0.2.0 ok:True
xoq errors during installation/upgrade. Query sys.registry$error WHERE
identifier euqal to XOQ. ok:False
PL/SQL procedure successfully completed.
SQL> set long 12000
SQL> set lines 1200
SQL> set pages 1200
select * from sys.registry$error WHERE identifier ='XOQ';
SQL>
USERNAME                                                                                                                                                                                                                                                         TIMESTAMP
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ---------------------------------------------------------------------------
SCRIPT
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
IDENTIFIER                                                                                                                                                                                                                                                       MESSAGE                                                                          STATEMENT
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------- --------------------------------------------------------------------------------
SYS                                                                                                                                                                                                                                                              21-APR-15 11.28.35.000000 AM
/u04/oracle/app/oracle/product/12.1/olap/admin/xoqroles.sql
XOQ                                                                                                                                                                                                                                                              ORA-00600: internal error code, arguments: [qkaQknLTPruneKaf:1], [], [], [], [], begin
                                                                                                                                                                                                                                                                  [], [], [], [], [], [], []                                                       xoq_validate;
                                                                                                                                                                                                                                                                                                                                                  exception
                                                                                                                                                                                                                                                                                                                                                  when others then
                                                                                                                                                                                                                                                                                                                                                   sys.dbms_registry.invalid('XOQ');
                                                                                                                                                                                                                                                                                                                                                  end;
SYS                                                                                                                                                                                                                                                              21-APR-15 11.28.35.000000 AM
/u04/oracle/app/oracle/product/12.1/olap/admin/xoqroles.sql
XOQ                                                                                                                                                                                                                                                              [], [], [], [], [], [], [], []                                                   as above

继续处理

SQL> delete from sys.registry$error where identifier='XOQ';
2 rows deleted.
SQL> commit;
Commit complete.
SQL> exec xoq_validate_verbose ;
compatible:12.1.0.2.0 ok:True
No install errors were found so component remains valid. ok:True
expected XDB resources are not there. ok:False
PL/SQL procedure successfully completed.
SQL> select * from sys.registry$error WHERE identifier ='XOQ';
no rows selected
@?/olap/admin/xoqrelod.sql;
@?/rdbms/admin/utlrp.sql
  SQL> select comp_name, status, substr(version,1,10) as version from dba_registry;
COMP_NAME                                           STATUS                 VERSION
--------------------------------------------------- ---------------------- --------------------
Spatial                                             VALID                  12.1.0.2.0
Oracle Multimedia                                   VALID                  12.1.0.2.0
Oracle XML Database                                 VALID                  12.1.0.2.0
Oracle Text                                         VALID                  12.1.0.2.0
Oracle Workspace Manager                            VALID                  12.1.0.2.0
Oracle Database Catalog Views                       VALID                  12.1.0.2.0
Oracle Database Packages and Types                  VALID                  12.1.0.2.0
JServer JAVA Virtual Machine                        VALID                  12.1.0.2.0
Oracle XDK                                          VALID                  12.1.0.2.0
Oracle Database Java Packages                       VALID                  12.1.0.2.0
OLAP Analytic Workspace                             VALID                  12.1.0.2.0
Oracle OLAP API                                     VALID                  12.1.0.2.0
Oracle Real Application Clusters                    VALID                  12.1.0.2.0
13 rows selected.

至此处理完成OLAP API组件终于变成VALID状态.出现此类问题,是否由于设置了default profile文件的密码验证函数导致,还是oracle 12.1.0.2.3的bug导致不能确定.这里到这里让我知道,在数据库升级过程中,最好检查default profile中密码验证配置,免得导致用户无法创建的错误(在升级中有些组件需要重建用户)

通过Administration Assistant for Windows配置win服务和实例关联性

在一些win系统的Oracle数据库中,大家都知道,Oracle启动前需要先启动服务,但是偶尔还有这两种需求:
1. 启动Oracle实例服务,但是不想启动数据库实例(特别是在有些情况下,数据库因为某种错误一启动到open直接报错,可能导致系统僵死
2. 在主机关闭或者服务关闭(重启)之时,希望数据库能够正常关闭后,而不是直接终止实例.
由于win平台的特殊性,Oracle也对其进行了特殊处理,提供了专门的工具(Administration Assistant for Windows)处理此类问题:
启动Administration Assistant for Windows
Administration Assistant for Windows

选择需要配置的数据库服务
2

右键选择启动/关闭选项
3

配置服务启动时是否启动数据库实例
4

配置服务关闭时是否关闭数据库以及关闭数据库的方式
5

通过上述类似配置可以控制在Oracle服务启动之时实例是否启动,在Oracle服务关闭之时实例是否关闭(以及关闭的方式),建议配置在关闭服务之时,使用immediate(立即关闭)方式关闭数据库,确保数据库安全

Quick Reference to Patch Numbers for Database PSU, SPU(CPU), Bundle Patches and Patchsets—201504

Patchsets
 l12.1.0.2 (12.1.0.2.0 PATCH SET FOR ORACLE DATABASE SERVER)  17694377
 11.2.0.4 (11.2.0.4.0 PATCH SET FOR ORACLE DATABASE SERVER)  13390677
 11.2.0.3 (11.2.0.3.0 PATCH SET FOR ORACLE DATABASE SERVER)  10404530
 11.2.0.2 (11.2.0.2.0 PATCH SET FOR ORACLE DATABASE SERVER)  10098816
 11.1.0.7 (11.1.0.7.0 PATCH SET FOR ORACLE DATABASE SERVER)  6890831
 10.2.0.5 (10.2.0.5 PATCH SET FOR ORACLE DATABASE SERVER)  8202632
 d10.2.0.4 (10.2.0.4.0 PATCH SET FOR ORACLE DATABASE SERVER)  6810189
 e10.2.0.3 (10.2.0.3 PATCH SET FOR ORACLE DATABASE SERVER)  5337014
 10.2.0.2 (10.2.0.2 PATCH SET FOR ORACLE DATABASE SERVER)  4547817
 10.1.0.5 (10.1.0.5 PATCH SET FOR ORACLE DATABASE SERVER)
  4505133
 10.1.0.4 (10.1.0.4 PATCH SET FOR ORACLE DATABASE SERVER)
  4163362
 10.1.0.3 (10.1.0.3 PATCH SET FOR ORACLE DATABASE SERVER)
  3761843
 9.2.0.8 (9.2.0.8 PATCH SET FOR ORACLE DATABASE SERVER)
  4547809
 9.2.0.7 (9.2.0.7 PATCH SET FOR ORACLE DATABASE SERVER)
  4163445
 9.2.0.6 (9.2.0.6 PATCH SET FOR ORACLE DATABASE SERVER)
  3948480
 9.2.0.5 (ORACLE 9I DATABASE SERVER RELEASE 2 – PATCH SET 4 VERSION 9.2.0.5.0)
  3501955
 9.2.0.4 (9.2.0.4 PATCH SET FOR ORACLE DATABASE SERVER)
  3095277
 9.2.0.3 (9.2.0.3 PATCH SET FOR ORACLE DATABASE SERVER)
  2761332
 9.2.0.2 (9.2.0.2 PATCH SET FOR ORACLE DATABASE SERVER)
  2632931
 9.0.1.5 (9.0.1.5 PATCHSET)
  3301544
 9.0.1.4 (9.0.1.4 PATCH SET FOR ORACLE DATABASE SERVER)
  2517300
 9.0.1.3 (9.0.1.3. PATCH SET FOR ORACLE DATA SERVER)
  2271678
 8.1.7.4 (8.1.7.4 PATCH SET FOR ORACLE DATA SERVER)
  2376472
 8.1.7.3 (8.1.7.3 PATCH SET FOR ORACLE DATA SERVER)
  2189751
 8.1.7.2 (8.1.7.2.1 PATCH SET FOR ORACLE DATA SERVER)
  1909158

 

PSU, SPU(CPU), Bundle Patches

 
12.1.0.2
 Description  PSU    GI PSU  Bundle Patch (Windows 32bit & 64bit)
 APR2015  20299023 (12.1.0.2.3)  20485724 (12.1.0.2.3)  20684004 (12.1.0.2.4)
 JAN2015  19769480 (12.1.0.2.2)  19954978 (12.1.0.2.2)  19720843 (12.1.0.2.1)
 OCT2014  19303936 (12.1.0.2.1)  19392646 (12.1.0.2.1)  N/A

 

12.1.0.1
 Description  PSU  GI PSU   Bundle Patch (Windows64bit)  Bundle Patch (Windows32bit)
 APR2015  20299016 (12.1.0.1.7)   j20485762 / k19971331(12.1.0.1.7) 20558101 (12.1.0.1.18)
 JAN2015  19769486 (12.1.0.1.6)  j19971324 / k19971331 (12.1.0.1.6) 20160748 (12.1.0.1.16)
 OCT2014  19121550 (12.1.0.1.5)  j19392372 / k19392451 (12.1.0.1.5) 19542943 (12.1.0.1.14)
 JUL2014  18522516 (12.1.0.1.4)  j18705901 / k18705972 (12.1.0.1.4) 19062327 (12.1.0.1.11)
 APR2014  18031528 (12.1.0.1.3)  j18139660 / k18413105  (12.1.0.1.3) 18448604 (12.1.0.1.7)
 JAN2014  17552800 (12.1.0.1.2)  17735306 (12.1.0.1.2) 17977915 (12.1.0.1.3)
 OCT2013  17027533 (12.1.0.1.1)  17272829 (12.1.0.1.1)  17363796 (12.1.0.1.1)  17363795 (12.1.0.1.1)

 

11.2.0.4
 Description  PSU  SPU(CPU)  GI PSU  Bundle Patch (Windows 32bit & 64bit)
 APR2015  20299013 (11.2.0.4.6)  20299015  20485808 (11.2.0.4.6)  20544696 (11.2.0.4.15)
 JAN2015  19769489 (11.2.0.4.5)  19854503  19955028 (11.2.0.4.5)  20127071 (11.2.0.4.12)
 OCT2014  19121551 (11.2.0.4.4)  19271443  19380115 (11.2.0.4.4)  19651773 (11.2.0.4.10)
 JUL2014  18522509 (11.2.0.4.3)  18681862  18706472 (11.2.0.4.3)  18842982 (11.2.0.4.7)
 APR2014  18031668 (11.2.0.4.2)  18139690  18139609 (11.2.0.4.2)  18296644 (11.2.0.4.4)
 JAN2014  17478514 (11.2.0.4.1)  17551709  N/A  17987366 (11.2.0.4.1)

 

11.2.0.3
 Description  PSU  SPU(CPU)  GI PSU  Bundle Patch (Windows64bit)  Bundle Patch(Windows32bit)
 APR2015  20299017 (11.2.0.3.14)  20299010  20485830 (11.2.0.3.14)  20420395  20420394
 JAN2015  19769496 (11.2.0.3.13)  19854461  19971343 (11.2.0.3.13)  20233168  20233167
 OCT2014  19121548 (11.2.0.3.12)  19271438  19440385 (11.2.0.3.12)  19618575  19618574
 JUL2014  18522512 (11.2.0.3.11)  18681866  18706488 (11.2.0.3.11)  18940194  18940193
 APR2014  18031683 (11.2.0.3.10)  18139695  18139678 (11.2.0.3.10)  18372244  18372243
 JAN2014  17540582 (11.2.0.3.9)  17478415  17735354 (11.2.0.3.9)  18075406  17906981
 OCT2013  16902043 (11.2.0.3.8)  17082364  17272731 (11.2.0.3.8)  17363850  17363844
 JUL2013  16619892 (11.2.0.3.7)  16742095  16742216 (11.2.0.3.7)  16803775  16803774
 APR2013  16056266 (11.2.0.3.6)  16294378  16083653 (11.2.0.3.6)  16345834  16345833
 JAN2013  14727310 (11.2.0.3.5)  14841409  14727347 (11.2.0.3.5)  16042648  16042647
 OCT2012  14275605 (11.2.0.3.4)  14390252  14275572 (11.2.0.3.4)  14613223  14613222
 JUL2012  13923374 (11.2.0.3.3)  14038787  13919095 (11.2.0.3.3)  14223718  14223717
 APR2012  13696216 (11.2.0.3.2)  13632717  13696251 (11.2.0.3.2)  13885389  13885388
 JAN2012  13343438 (11.2.0.3.1)  13466801  13348650 (11.2.0.3.1)  13413168  13413167

 

11.2.0.2
 Description  PSU   SPU(CPU)  GI PSU  Bundle Patch (Windows64bit)  Bundle Patch(Windows32bit)
 aOCT2013  17082367 (11.2.0.2.12)  17082375  17272753 (11.2.0.2.12)  17363838  17363837
 JUL2013  16619893 (11.2.0.2.11)  16742100  16742320 (11.2.0.2.11)  16345852  16345851
 APR2013  16056267 (11.2.0.2.10)  16294412  16166868 (11.2.0.2.10)  16345846  16345845
 JAN2013  14727315 (11.2.0.2.9)  14841437  14841385 (11.2.0.2.9)  16100399  16100398
 OCT2012  14275621 (11.2.0.2.8)  14390377  14390437 (11.2.0.2.8)  14672268  14672267
 JUL2012  13923804 (11.2.0.2.7)  14038791  14192201 (11.2.0.2.7)  14134043  14134042
 APR2012  13696224 (11.2.0.2.6)  13632725  13696242 (11.2.0.2.6)  13697074  13697073
 JAN2012  13343424 (11.2.0.2.5)  13343244  13653086 (11.2.0.2.5)  13413155  13413154
 OCT2011  12827726 (11.2.0.2.4)  12828071  12827731 (11.2.0.2.4)  13038788  13038787
 JUL2011  12419331 (11.2.0.2.3)  12419321  12419353 (11.2.0.2.3)  12714463  12714462
 APR2011  11724916 (11.2.0.2.2)  11724984  12311357 (11.2.0.2.2)  11896292  11896290
 JAN2011  10248523 (11.2.0.2.1)  N/A  N/A  10432053  10432052
 
11.2.0.1
 Description  PSU  CPU  Bundle Patch (Windows64bit)  Bundle Patch (Windows32bit)
 aJUL2011  12419378 (11.2.0.1.6)  12419278  12429529  12429528
 APR2011  11724930 (11.2.0.1.5)  11724991  11731176  11883240
 JAN2011  10248516 (11.2.0.1.4)  10249532  10432045  10432044
 OCT2010  9952216 (11.2.0.1.3)  9952260  10100101  10100100
 JUL2010  9654983 (11.2.0.1.2)  9655013  9736865  9736864
 APR2010  9352237 (11.2.0.1.1)  9369797  N/A  N/A
 
11.1.0.7
 Description  PSU  SPU(CPU)  Bundle Patch (Windows64bit)  Bundle Patch (Windows32bit)
bAPR2015  20299012 (11.1.0.7.23)  20299020  20420391  20420390
bJAN2015  19769499 (11.1.0.7.22)  19854433  20126915  20126914
bOCT2014  19152553 (11.1.0.7.21)  19274522  19609034  19609032
bJUL2014  18522513 (11.1.0.7.20)  18681875  18944208  18944207
bAPR2014  18031726 (11.1.0.7.19)  18139703  18372258  18372257
bJAN2014  17465583 (11.1.0.7.18)  17551415  17906936  17906935
bOCT2013  17082366 (11.1.0.7.17)  17082374  17363760  17363759
bJUL2013  16619896 (11.1.0.7.16)  16742110  16803788  16803787
bAPR2013  16056268 (11.1.0.7.15)  16308394  16345862  16345861
bJAN2013  14739378 (11.1.0.7.14)  14841452  15848067  15848066
 bOCT2012  14275623 (11.1.0.7.13)  14390384  14672313  14672312
 JUL2012  13923474 (11.1.0.7.12)  14038803  14109868  14109867
 APR2012  13621679 (11.1.0.7.11)  13632731  13715810  13715809
 JAN2012  13343461 (11.1.0.7.10)  13343453  13460956  13460955
 OCT2011  12827740 (11.1.0.7.9)  12828097  12914916  12914915
 JUL2011  12419384 (11.1.0.7.8)  12419265  12695278  12695277
 APR2011  11724936 (11.1.0.7.7)  11724999  11741170  11741169
 JAN2011  10248531 (11.1.0.7.6)  10249534  10350788  10350787
 OCT2010  9952228  (11.1.0.7.5)  9952269  9773825  9773817
 JUL2010  9654987 (11.1.0.7.4)  9655014  9869912  9869911
 APR2010  9352179 (11.1.0.7.3)  9369783  9392335  9392331
 JAN2010  9209238 (11.1.0.7.2)  9114072  9166861  9166858
 OCT2009  8833297 (11.1.0.7.1)  8836375  8928977  8928976
 JUL2009  N/A  8534338  8553515  8553512
 APR2009  N/A  8290478  8343070  8343061
 
11.1.0.6
 Description  CPU  Bundle Patch (Windows64bit)  Bundle Patch (Windows32bit)
 aJUL2009  8534378  8563155  8563154
 APR2009  8290402  8333657  8333655
 JAN2009  7592335  7631981  7631980
 OCT2008  7375639  7378393  7378392
 JUL2008  7150417  7210197  7210195
 APR2008  6864063  6867180  6867178
 
10.2.0.5
 Description  PSU  SPU(CPU)  Bundle Patch (Windows64bit)  Bundle Patch (Windows32bit)  Bundle Patch(WindowsItanium)
abJUL2013  16619894 (10.2.0.5.12)  16742123  16803782  16803780  16803781
 bAPR2013  16056270 (10.2.0.5.11)  16270946  16345857  16345855  16345856
 bJAN2013  14727319 (10.2.0.5.10)  14841459  15848062  15848060  15848061
 bOCT2012  14275629 (10.2.0.5.9)  14390396  14553358  14553356  14553357
 bJUL2012  13923855 (10.2.0.5.8)  14038805  14134053  14134051  14134052
 bAPR2012  13632743 (10.2.0.5.7)  13632738  13654815  13654814  13870404
 JAN2012  13343471 (10.2.0.5.6)  13343467  b13460968 b13460967  N/A
 bOCT2011  12827745 (10.2.0.5.5)  12828105  c12914913  12914911  N/A
 JUL2011  12419392 (10.2.0.5.4)  12419258  12429524  12429523  N/A
 APR2011  11724962 (10.2.0.5.3)  11725006  12328269  12328268  N/A
 JAN2011  10248542 (10.2.0.5.2)  10249537  10352673  10352672  N/A
 OCT2010  9952230 (10.2.0.5.1)  9952270  10099855  10058290  N/A
 
10.2.0.4
 Description  PSU  SPU(CPU)  Bundle Patch (Windows32bit)  Bundle Patch (Windows64bit)  Bundle Patch(WindowsItanium)
 bgJUL2013  16619897 (10.2.0.4.17)  16742253  N/A  N/A  N/A
 bgAPR2013  16056269 (10.2.0.4.16)  16270931  N/A  N/A  N/A
 bgJAN2013  14736542 (10.2.0.4.15)  14841471  N/A  N/A  N/A
bgOCT2012  14275630 (10.2.0.4.14)  14390410  N/A  N/A  N/A
bgJUL2012  13923851 (10.2.0.4.13)  14038814  N/A  N/A  N/A
 abAPR2012  12879933 (10.2.0.4.12)  12879926  13928775  13928776  N/A
 JAN2012  12879929 (10.2.0.4.11)  12879912  b13654060  N/A  N/A
 bOCT2011  12827778 (10.2.0.4.10)  12828112  12914908  12914910  12914909
 JUL2011  12419397 (10.2.0.4.9)  12419249  12429519  12429521  12429520
 APR2011  11724977 (10.2.0.4.8)  11725015  12328501  12328503  12328502
 JAN2011  10248636 (10.2.0.4.7)  10249540  10349197  10349200  10349198
 OCT2010  9952234 (10.2.0.4.6)  9952272  10084980  10084982  10084981
 JUL2010  9654991 (10.2.0.4.5)  9655017  9777076  9777078  9777077
 APR2010  9352164 (10.2.0.4.4)  9352191  9393548  9393550  9393549
 JAN2010  9119284 (10.2.0.4.3)  9119226  9169457  9169460  9169458
 OCT2009  8833280 (10.2.0.4.2)  8836308  8880857  8880861  8880858
 JUL2009  8576156 (10.2.0.4.1)  8534387  8559466  8559467  8541782
 APR2009  N/A  8290506  8307237  8307238  8333678
 JAN2009  N/A  7592346  7584866  7584867  N/A
 OCT2008  N/A  7375644  7386320  7386321  N/A
 JUL2008  N/A  7150470  7218676  7218677  N/A
 
10.2.0.3
 Description  CPU (Unix/Linux)  Bundle Patch (Windows32bit)  Bundle Patch (WindowsItanium)  Bundle Patch (Windows64bit)
 aJAN2009  7592354  7631956  7631958  7631957
 OCT2008  7369190  7353782  7353784  7353785
 JUL2008  7150622  7252496  7252497  7252498
 APR2008  6864068  6867054  6867055  6867056
 JAN2008  6646853  6637237  6637238  6637239
 OCT2007  6394981  6430171  6430173  6430174
 JUL2007  6079591  6116131  6038242  6116139
 APR2007  5901891  5948242  5916262  5948243
 JAN2007  5881721  5846376  5846377  5846378
 
10.2.0.2
 Description  CPU (Unix/Linux)  Bundle Patch (Windows32bit)   Bundle Patch (Windows64bit)  Bundle Patch (WindowsItanium)
 iJAN2009  7592355  N/A  N/A  N/A
 hOCT2008  7375660  N/A  N/A  N/A
 hJUL2008  7154083  N/A  N/A  N/A
 hAPR2008  6864071  N/A  N/A  N/A
 aJAN2008  6646850  N/A  N/A  N/A
 fOCT2007  6394997  6397028  6397030  6397029
 JUL2007  6079588  6013105  6013121  6013118
 APR2007  5901881  5912173  5912179  5912176
 JAN2007  5689957  5716143  5699839  5699824
 OCT2006  5490848  5502226  5500921  5500894
 JUL2006  5225799  5251025  5251028  5251026
 APR2006  5079037  5140461  5140567  5140508
 
10.2.0.1
 Description  CPU (Unix/Linux)  Bundle Patch (Windows32bit)  Bundle Patch (Windows64bit)  Bundle Patch (WindowsItanium)
 APR2007  5901880  N/A  N/A  N/A
 JAN2007  5689937  5695784  5695786  5695785
 OCT2006  5490846  5500927  5500954  5500951
 JUL2006  5225798  5239698  5239701  5239699
 APR2006  5049080  5059238  5059261  5059251
 JAN2006  4751931  4751539  4770480  4751549
 
10.1.0.5
 Description  CPU (Unix/Linux)  Bundle Patch (Windows32bit)   Bundle Patch (WindowsItanium)
 JAN2012  13343482  13413002  13413003
 OCT2011  12828135  12914905  12914906
 JUL2011  12419228  12429517  12429518
 APR2011  11725035  11731119  11731120
 JAN2011  N/A  N/A  N/A
 OCT2010  9952279  10089559  10089560
 JUL2010  9655023  9683651  9683652
 APR2010  9352208  9390288  9390289
 JAN2010  9119261  9187104  9187105
 OCT2009  8836540  8785211  8785212
 JUL2009  8534394  8656224  8656226
 APR2009  8290534  8300356  8300360
 JAN2009  7592360  7486619  7586049
 OCT2008  7375686  7367493  7367494
 JUL2008  7154097  7047034  7047037
 APR2008  6864078  6867107  6867108
 JAN2008  6647005  6637274  6637275
 OCT2007  6395024  6408393  6408394
 JUL2007  6079585  6115804  6115818
 APR2007  5901877  5907304  5907305
 JAN2007  5689908  5716295  5634747
 OCT2006  5490845  5500883  5500885
 JUL2006  5225797  5251148  5251140
 APR2006  5049074  5057606  5057609
 JAN2006  4751932  4882231  4882236
 
10.1.0.4
 Description  CPU (Unix/Linux)  Bundle Patch (Windows32bit)  Bundle Patch (WindowsItanium)
 APR2007  5901876  5909871  5909879
 JAN2007  5689894  5695771  5695772
 OCT2006  5490844  5500878  5500880
 JUL2006  5225796  5239736  5239737
 APR2006  5049067  5059200  5059227
 JAN2006  4751928  4751259  4745040
 OCT2005  4567866  4579182  4579188
 JUL2005  4392423  4440706  4404600
 APR2005  4210374  4287619  4287611
 
10.1.0.3
 Description  CPU (Unix/Linux)  Bundle Patch (Windows32bit)  Bundle Patch (WindowsItanium)
 JAN2007  5923277  N/A  N/A
 OCT2006  5566825  N/A  N/A
 JUL2006  5435164  N/A  N/A
 APR2006  5158022  N/A  N/A
 JAN2006  4751926  4741077  4741084
 OCT2005  4567863  4567518  4567523
 JUL2005  4392409  4389012  4389014
 APR2005  4193286  4269715  4158888
 JAN2005  4003062  4074232  3990812
 
10.1.0.2
 Description  CPU (Unix/Linux)  Bundle Patch (Windows32bit)  Bundle Patch (WindowsItanium)
 APR2005  4193293  4181849  4213305
 JUL2005  4400766  4388944  4388948
 JAN2005  4003051  4104364  4083038
 
9.2.0.8
 Description  CPU (Unix/Linux)  Bundle Patch (Windows32bit)  Bundle Patch (WindowsItanium)
 JUL2010  9655027  9683644  9683645
 APR2010  9352224  9390286  N/A
 JAN2010  9119275  9187106  N/A
 OCT2009  8836758  8785185  8785186
 JUL2009  8534403  8427417  8427418
 APR2009  8290549  8300340  8300346
 JAN2009  7592365  7703210  7703212
 OCT2008  7375695  7394394  7394402
 JUL2008  7154111  7047026  7047029
 APR2008  6864082  6867138  6867139
 JAN2008  6646842  6637265  6637266
 OCT2007  6395038  6417013  6417014
 JUL2007  6079582  6130293  6130295
 APR2007  5901875  5916268  5916275
 JAN2007  N/A  N/A  N/A
 OCT2006  5490859  5652380  5639519
 
9.2.0.7
 Description  CPU (Unix/Linux)  Bundle Patch (Windows32bit)  Bundle Patch (WindowsItanium)
 JUL2007  6079579  6146759  6146748
 APR2007  5901872  5907274  5907275
 JAN2007  5689875  5654905  5654909
 OCT2006  5490841  5500873  5500874
 JUL2006  5225794  5250980  5250981
 APR2006  5049060  5064365  5064364
 JAN2006  4751923  4751528  4741074
 OCT2005  4567854  4579590  4579599
 JUL2005  4547566  N/A  N/A
 
9.2.0.6
 Description  CPU (Unix/Linux)  Bundle Patch (Windows32bit)  Bundle Patch (WindowsItanium)
 OCT2006  5490840  5500865  5500871
 JUL2006  5225793  5239794  5239793
 APR2006  5049051  5059614  5059615
 JAN2006  4751921  4751261  4751262
 OCT2005  4567846  4579093  4579097
 JUL2005  4392392  4445852  4401917
 APR2005  4193295  4269928  4213298
 
9.2.0.5
 Description  CPU (Unix/Linux)  Bundle Patch (Windows32bit)  Bundle Patch (WindowsItanium)
 OCT2006  5689708  N/A  N/A
 JUL2006  5435138  N/A  N/A
 APR2006  5219762  N/A  N/A
 OCT2005  4560421  N/A  N/A
 JUL2005  4392256  4387563  4391819
 APR2005  4193299  4195791  4214192
 JAN2005  4003006  4104374  3990809
 
9.2.0.4
 Description  CPU (Unix/Linux)  Bundle Patch (Windows32bit)  Bundle Patch (WindowsItanium)
 JAN2005  4002994  4104369  4083202
 
8.1.7.4
 Description  CPU (Unix/Linux)  Bundle Patch (Windows32bit)
 JAN2007  5689799  5686514
 OCT2006  5490835  5496067
 JUL2006  5225788  5236412
 APR2006  5045247  5057601
 JAN2006  4751906  4751570
 OCT2005  4560405  4554818
 JUL2005  4392446  4437058
 APR2005  4193312  4180163
 JAN2005  4002909  3921893

OJVM PSU Patches

 
12.1.0.2
 Description  OJVM PSU (Linux/Unix)  OJVM BP (Windows)  Combo OJVM + DB PSU  Combo OJVM + GI PSU  Combo OJVM +  DB BP
 APR2015  20415564 (12.1.0.2.3)  20391199 (12.1.0.2.2)  20834354  20834538  20834553
 JAN2015  19877336 (12.1.0.2.2)  20225938 (12.1.0.2.1)  20132434  20132450  20132462
 OCT2014 (12.1.0.2.1)  19282028  19791366  19791375  19791399

 
12.1.0.1
 Description  OJVM PSU (Linux/Unix)  OJVM BP (Windows)  Combo OJVM + DB PSU  Combo OJVM + GI PSU  Combo OJVM +  DB BP  Generic JDBC
 APR2015 (12.1.0.1.3)  20406245  20225909  20834568  20834579  N/A  N/A
 JAN2015 (12.1.0.1.2)  19877342  20225916  20132482  20132489  N/A  N/A
 OCT2014 (12.1.0.1.1)  19282024  19801531  19791363  19791360  N/A  19852357

 
11.2.0.4
 Description  OJVM PSU (Linux/Unix)  OJVM BP (Windows)   Combo OJVM + DB PSU  Combo OJVM + DB SPU  Combo OJVM + GI PSU  Combo OJVM +  DB BP  Generic JDBC
 APR2015 (11.2.0.4.3)  20406239  20225988  20834611  20834597  20834621  N/A N/A
 JAN2015 (11.2.0.4.2)  19877440  20225982  20132580  20132517  20132615  N/A N/A
 OCT2014 (11.2.0.4.1)  19282021 19799291  19791364  19791358  19791420  N/A 19852360

 
11.2.0.3
 Description  OJVM PSU (Linux/Unix)  OJVM BP (Windows) Combo OJVM + DB PSU  Combo OJVM + DB SPU Combo OJVM + GI PSU  Combo OJVM +  DB BP  Generic JDBC
 APR2015 (11.2.0.3.3) 20406220  20391185 20834670 20834653  20834686  N/A
 JAN2015 (11.2.0.3.2) 19877443  20227195 20132646 20132635  20132651  N/A  N/A
 OCT2014 (11.2.0.3.1) 19282015  19806120 19791427 19791426 19791428  N/A 19852361

 

 
11.1.0.7
Description OJVM PSU (Linux/Unix)  OJVM BP (Windows) Combo OJVM + DB PSU Combo OJVM + DB SPU  Combo OJVM + GI PSU Combo OJVM +  DB BP Generic JDBC
 APR2015 (11.1.0.7.3)  20406213  20391156  20834724  20834712   N/A  N/A  N/A
 JAN2015 (11.1.0.7.2)  19877446  20227146  20132677  20132669   N/A  N/A  N/A
 OCT2014 (11.1.0.7.1)  19282002  19806118  19791436  19791434   N/A  N/A  19852363

 

11g 使用 alter user identified by values password 恢复历史密码

在11.1之前的版本,很多人可能都知道，可以通过alter user identified by values password 来还原oracle 数据库历史密码,但是在11g中出现几个问题:
1. dba_users中无password记录（值为空)，这个问题可以通过直接查询user$.password依然有记录

SQL> select password from dba_users where username='SYS';
PASSWORD
------------------------------
SQL> select password from user$ WHERE name='SYS';
PASSWORD
------------------------------
8A8F025737A9097A

2.在11.1开始user$中的SPARE4有值,从而使得数据库密码区分大小写，参考blog:关于ORACLE 11G密码大小写敏感猜想(USER$.SPARE4)

SQL>  select SPARE4  from user$ WHERE name='SYS';
SPARE4
--------------------------------------------------------------------------------
S:C7C81BBE7760B5BBB3973F0971AA36C737BF6DCC4A34FE925CE70B0739BD

现在就存在疑问,在11G版本中,如何来还原Oracle数据库用户历史密码呢？,这里通过试验的方式证明,alter user identified by values后面值可以是user$.password 也可以是user$.SPARE4，只是两者在密码大小写上有区别,具体试验如下：

创建测试用户xifenfei

[oracle@localhost ~]$ ss
SQL*Plus: Release 11.2.0.4.0 Production on Fri Apr 10 16:00:03 2015
Copyright (c) 1982, 2013, Oracle.  All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> select * from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
PL/SQL Release 11.2.0.4.0 - Production
CORE    11.2.0.4.0      Production
TNS for Linux: Version 11.2.0.4.0 - Production
NLSRTL Version 11.2.0.4.0 - Production
SQL> select sysdate "www.xifenfei.com" from dual;
www.xifen
---------
10-APR-15
SQL> create user xifenfei identified by oracle;
User created.
SQL> grant create session to xifenfei;
Grant succeeded.
SQL> conn xifenfei/oracle
Connected.
SQL> conn xifenfei/ORACLE
ERROR:
ORA-01017: invalid username/password; logon denied
Warning: You are no longer connected to ORACLE.
SQL> conn / as sysdba
Connected.
SQL> show parameter sec_case_sensitive_logon ;
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
sec_case_sensitive_logon             boolean     TRUE

这里由于sec_case_sensitive_logon参数默认为true,因此密码区分大小写

修改数据库密码

SQL> select spare4,password from user$ where name='XIFENFEI';
SPARE4
--------------------------------------------------------------------------------
PASSWORD
------------------------------
S:6E34E993900317BBFD6289E4AE619D634AA6AD804C765A3DEE1CCABCC50D
1BA871FA3B1C3F45
SQL> alter user xifenfei identified by xifenfei;
User altered.
SQL> select spare4,password from user$ where name='XIFENFEI';
SPARE4
--------------------------------------------------------------------------------
PASSWORD
------------------------------
S:A75A184EA2767488E698C443E97CB2473B46A9C80C2C61833BA867CB8B17
1682CAA2339F770F
SQL> conn xifenfei/xifenfei
Connected.
SQL> conn xifenfei/XIFENFEI
ERROR:
ORA-01017: invalid username/password; logon denied
Warning: You are no longer connected to ORACLE.
SQL> conn xifenfei/oracle
ERROR:
ORA-01017: invalid username/password; logon denied

这里把xifenfei用户的密码从oracle修改为xifenfei

尝试values user$.password恢复以前密码

SQL> conn / as sysdba
Connected.
SQL> alter user xifenfei identified by values '1BA871FA3B1C3F45';
User altered.
SQL> select spare4,password from user$ where name='XIFENFEI';
SPARE4
--------------------------------------------------------------------------------
PASSWORD
------------------------------
1BA871FA3B1C3F45
SQL> conn xifenfei/oracle
Connected.
SQL> conn xifenfei/ORACLE
Connected.
SQL> conn xifenfei/xifenfei
ERROR:
ORA-01017: invalid username/password; logon denied
Warning: You are no longer connected to ORACLE.

通过该方式还原上次密码后，发现user$.SPARE4为空,也就使得Oracle不再区分密码大小写.

尝试values user$.spare4恢复以前密码

SQL> conn / as sysdba
Connected.
SQL> alter user xifenfei identified by xifenfei;
User altered.
SQL> select spare4,password from user$ where name='XIFENFEI';
SPARE4
--------------------------------------------------------------------------------
PASSWORD
------------------------------
S:48A11864AD633E904126C20E8C374A4AA45D87BB005D35AD2B10766E8E11
1682CAA2339F770F
SQL> conn xifenfei/xifenfei
Connected.
SQL> conn xifenfei/oracle
ERROR:
ORA-01017: invalid username/password; logon denied
Warning: You are no longer connected to ORACLE.
SQL> conn xifenfei/XIFENFEI
ERROR:
ORA-01017: invalid username/password; logon denied
SQL> alter user xifenfei identified by values '6E34E993900317BBFD6289E4AE619D634AA6AD804C765A3DEE1CCABCC50D';
SP2-0640: Not connected
SQL> conn / as sysdba
Connected.
SQL> alter user xifenfei identified by values '6E34E993900317BBFD6289E4AE619D634AA6AD804C765A3DEE1CCABCC50D';
alter user xifenfei identified by values '6E34E993900317BBFD6289E4AE619D634AA6AD804C765A3DEE1CCABCC50D'
*
ERROR at line 1:
ORA-00600: internal error code, arguments: [kzsviver:2], [], [], [], [], [],
[], [], [], [], [], []
--少写了S:,直接报ORA-600错误，怀疑S:是spare4列的某种标识
SQL>
SQL>
SQL>
SQL> alter user xifenfei identified by values 'S:6E34E993900317BBFD6289E4AE619D634AA6AD804C765A3DEE1CCABCC50D';
User altered.
SQL> select spare4,password from user$ where name='XIFENFEI';
SPARE4
--------------------------------------------------------------------------------
PASSWORD
------------------------------
S:6E34E993900317BBFD6289E4AE619D634AA6AD804C765A3DEE1CCABCC50D
SQL> conn xifenfei/oracle
Connected.
SQL> conn xifenfei/ORACLE
ERROR:
ORA-01017: invalid username/password; logon denied
Warning: You are no longer connected to ORACLE.
SQL> conn xifenfei/xifenfei
ERROR:
ORA-01017: invalid username/password; logon denied

这里发现通过values user$.spare4恢复以前密码后,user$.password列为空,但是密码依旧区分大小写。这里可以看出来，user$.password项以后可能取消掉,为了兼容性,因此Oracle在后续版本中依旧保留.

关于oracle 11G中恢复以前密码操作总结
1. 通过values user$.password恢复以前密码后,不区分大小写
2. 通过values user$.spare4恢复以前密码后,区分大小写
3. 目前两种方式都可以实现11g恢复以前密码，但是推荐使用user$.spare4值修改

正常关闭数据库sequence cache不为0 sequence不跳跃

在Oracle中sequence使用很频繁，而大部分使用都配合了cache一起使用,那在sequence+cache一起使用的过程中,如果数据库正常关闭sequence.nextval如果变化,如果数据库异常关闭sequence.nextval又如何变化？这里通过试验进行了证明,结论为：在有cache的sequence中,正常关闭数据库sequence不会出现跳跃,异常关闭数据库很可能导致sequence出现跳跃
创建sequence测试

[oracle@localhost ~]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Sun Apr 5 15:44:23 2015
Copyright (c) 1982, 2013, Oracle.  All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> select sysdate "www.xifenfei.com" from dual;
www.xifen
---------
05-APR-15
SQL> create sequence seq_xifenfei
  2   minvalue 1
  3   maxvalue 100000
  4   start with 1
  5   increment by 1
  6   cache 100;
Sequence created.
SQL> select SEQ_XIFENFEI.nextval from dual;
   NEXTVAL
----------
         1
SQL> /
   NEXTVAL
----------
         2
SQL> /
   NEXTVAL
----------
         3
SQL> select object_id from dba_objects where object_name='SEQ_XIFENFEI';
 OBJECT_ID
----------
     87549
SQL>  SELECT MINVALUE,HIGHWATER,INCREMENT$, CYCLE#,ORDER$,CACHE from seq$ where obj#=87549;
  MINVALUE  HIGHWATER INCREMENT$     CYCLE#     ORDER$      CACHE
---------- ---------- ---------- ---------- ---------- ----------
         1        101          1          0          0        100

这里创建了一个名为SEQ_XIFENFEI的sequence,并且nextval已经查询到3,而且cache值设置为100,object_id为87549.

正常关闭数据库测试sequence.nextval变化

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
Total System Global Area 1570009088 bytes
Fixed Size                  2253584 bytes
Variable Size             469765360 bytes
Database Buffers         1090519040 bytes
Redo Buffers                7471104 bytes
Database mounted.
Database opened.
SQL> SELECT MINVALUE,HIGHWATER,INCREMENT$, CYCLE#,ORDER$,CACHE from seq$ where obj#=87549;
  MINVALUE  HIGHWATER INCREMENT$     CYCLE#     ORDER$      CACHE
---------- ---------- ---------- ---------- ---------- ----------
         1          4          1          0          0        100
SQL> select SEQ_XIFENFEI.nextval from dual;
   NEXTVAL
----------
         4
SQL> SELECT MINVALUE,HIGHWATER,INCREMENT$, CYCLE#,ORDER$,CACHE from seq$ where obj#=87549;
  MINVALUE  HIGHWATER INCREMENT$     CYCLE#     ORDER$      CACHE
---------- ---------- ---------- ---------- ---------- ----------
         1        104          1          0          0        100

正常关闭数据库启动后,sequence.nextval依然在上次基础之上增加，并为出现跳跃现象.

异常关闭数据库测试sequence.nextval变化

SQL> shutdown  abort;
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
Total System Global Area 1570009088 bytes
Fixed Size                  2253584 bytes
Variable Size             469765360 bytes
Database Buffers         1090519040 bytes
Redo Buffers                7471104 bytes
Database mounted.
Database opened.
SQL> SELECT MINVALUE,HIGHWATER,INCREMENT$, CYCLE#,ORDER$,CACHE from seq$ where obj#=87549;
  MINVALUE  HIGHWATER INCREMENT$     CYCLE#     ORDER$      CACHE
---------- ---------- ---------- ---------- ---------- ----------
         1        104          1          0          0        100
SQL> select SEQ_XIFENFEI.nextval from dual;
   NEXTVAL
----------
       104
SQL> SELECT MINVALUE,HIGHWATER,INCREMENT$, CYCLE#,ORDER$,CACHE from seq$ where obj#=87549;
  MINVALUE  HIGHWATER INCREMENT$     CYCLE#     ORDER$      CACHE
---------- ---------- ---------- ---------- ---------- ----------
         1        204          1          0          0        100

异常关闭数据库启动后,sequence.nextvla发生跳跃从本该5直接跳跃到了104.

跟踪数据库正常关闭过程

SQL> alter session set events '10046 trace name context forever, level 4';
Session altered.
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
Total System Global Area 1570009088 bytes
Fixed Size                  2253584 bytes
Variable Size             469765360 bytes
Database Buffers         1090519040 bytes
Redo Buffers                7471104 bytes
Database mounted.
Database opened.
SQL>  SELECT MINVALUE,HIGHWATER,INCREMENT$, CYCLE#,ORDER$,CACHE from seq$ where obj#=87549;
  MINVALUE  HIGHWATER INCREMENT$     CYCLE#     ORDER$      CACHE
---------- ---------- ---------- ---------- ---------- ----------
         1        105          1          0          0        100
SQL> select SEQ_XIFENFEI.nextval from dual;
   NEXTVAL
----------
       105
SQL> SELECT MINVALUE,HIGHWATER,INCREMENT$, CYCLE#,ORDER$,CACHE from seq$ where obj#=87549;
  MINVALUE  HIGHWATER INCREMENT$     CYCLE#     ORDER$      CACHE
---------- ---------- ---------- ---------- ---------- ----------
         1        205          1          0          0        100

再次证明了正常关闭数据库后,sequence.nextval未发生跳跃.

分析trace文件

PARSING IN CURSOR #139819144537744 len=129 dep=1 uid=0 oct=6 lid=0 tim=1428220381105111
hv=2635489469 ad='bc6e6c30' sqlid='4m7m0t6fjcs5x'
update seq$ set increment$=:2,minvalue=:3,maxvalue=:4,cycle#=:5,order$=:6,cache=:7,
highwater=:8,audit$=:9,flags=:10 where obj#=:1
END OF STMT
BINDS #139819144537744:
 Bind#0
  oacdty=02 mxl=22(02) mxlc=00 mal=00 scl=00 pre=00
  oacflg=10 fl2=0001 frm=00 csi=00 siz=24 off=0
  kxsbbbfp=bc41e288  bln=22  avl=02  flg=09
  value=1
 Bind#1
  oacdty=02 mxl=22(02) mxlc=00 mal=00 scl=00 pre=00
  oacflg=10 fl2=0001 frm=00 csi=00 siz=24 off=0
  kxsbbbfp=bc41e29a  bln=22  avl=02  flg=09
  value=1
 Bind#2
  oacdty=02 mxl=22(02) mxlc=00 mal=00 scl=00 pre=00
  oacflg=10 fl2=0001 frm=00 csi=00 siz=24 off=0
  kxsbbbfp=bc41e2ac  bln=22  avl=02  flg=09
  value=100000
 Bind#3
  oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
  oacflg=00 fl2=0001 frm=00 csi=00 siz=48 off=0
  kxsbbbfp=7f2a2edc2da8  bln=22  avl=01  flg=05
  value=0
 Bind#4
  oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
  oacflg=00 fl2=0001 frm=00 csi=00 siz=0 off=24
  kxsbbbfp=7f2a2edc2dc0  bln=22  avl=01  flg=01
  value=0
 Bind#5
  oacdty=02 mxl=22(02) mxlc=00 mal=00 scl=00 pre=00
  oacflg=10 fl2=0001 frm=00 csi=00 siz=24 off=0
  kxsbbbfp=bc41e2be  bln=22  avl=02  flg=09
  value=100
 Bind#6
  oacdty=02 mxl=22(03) mxlc=00 mal=00 scl=00 pre=00
  oacflg=10 fl2=0001 frm=00 csi=00 siz=24 off=0
  kxsbbbfp=bc41e2d0  bln=22  avl=03  flg=09
  value=105     <     ----SEQ_XIFENFEI.nextval值
 Bind#7
  oacdty=01 mxl=32(32) mxlc=00 mal=00 scl=00 pre=00
  oacflg=10 fl2=0001 frm=01 csi=852 siz=32 off=0
  kxsbbbfp=bc41e2e2  bln=32  avl=32  flg=09
  value="--------------------------------"
 Bind#8
  oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
  oacflg=00 fl2=0001 frm=00 csi=00 siz=48 off=0
  kxsbbbfp=7f2a2edc2d60  bln=22  avl=01  flg=05
  value=0
 Bind#9
  oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
  oacflg=00 fl2=0001 frm=00 csi=00 siz=0 off=24
  kxsbbbfp=7f2a2edc2d78  bln=22  avl=04  flg=01
  value=87549                             <---这个就是我们的sequence(SEQ_XIFENFEI)
EXEC #139819144537744:c=0,e=650,p=0,cr=1,cu=2,mis=0,r=1,dep=1,og=4,plh=1935744642,tim=1428220381108066
CLOSE #139819144537744:c=0,e=2,dep=1,type=3,tim=1428220381108119

这里我们找出来了为什么数据库正常关闭sequence.nextval在有cache的情况下,未发生跳跃:因为在数据库正常关闭的情况下,会触发一个update seq$的操作,把当前的sequence.nextval的值更新到seq$.highwater中,从而使得sequence在有cache的情况下，数据库正常关闭未出现nextval跳跃(currval也同样不跳跃)；而在数据库异常关闭之时，数据库不能及时将sequence.nextval更新到eq$.highwater从而引起sequence cache中的值丢失，从而可能出现了sequence使用cache导致跳跃的情况
关于另外一篇关于sequence cache减小update seq$频率的测试,请见:关于oracle sequence一些小测试

9.2.0.8 exp导出dmp导入报Segmentation fault/段错误故障解决

在9.2.0.8 rac环境中,使用exp导出来dmp文件任何报错(按单个表，按用户导出,使用tns方式远程exp导出)，包括重启数据库后导出，无法导入到其他数据库中(本库,tns方式远程导入,ftp传输到远程导入,9i/10g/11g版本)报错类似有setillegal instruction(coredump)，段错误，Segmentation fault等，以下列出来几个报错信息

--导入11.2.0.2版本
Import: Release 11.2.0.2.0 - Production on Fri Mar 27 20:09:51 2015
Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Export file created by EXPORT:V09.02.00 via conventional path
import done in ZHS16GBK character set and AL16UTF16 NCHAR character setillegal instruction(coredump)
--导入11.2.0.4版本
Import: Release 11.2.0.4.0 - Production on Thu Apr 2 21:12:18 2015
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Export file created by EXPORT:V09.02.00 via conventional path
Warning: the objects were exported by UNIONDB, not by you
import done in ZHS16GBK character set and AL16UTF16 NCHAR character set段错误
--导入10.2.0.5版本
Import: Release 10.2.0.5.0 - Production on Thu Apr 2 21:44:47 2015
Copyright (c) 1982, 2007, Oracle.  All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Export file created by EXPORT:V09.02.00 via conventional path
Warning: the objects were exported by UNIONDB, not by you
import done in US7ASCII character set and AL16UTF16 NCHAR character set
import server uses ZHS16GBK character set (possible charset conversion)
export client uses ZHS16GBK character set (possible charset conversion)段错误

增加trace=y跟踪分析

Trace file /u01/app/oracle/diag/rdbms/qsng/qsng/trace/qsng_ora_16772.trc
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_1
System name: Linux
Node name: localhost.localdomain
Release: 2.6.32-300.10.1.el5uek
Version: #1 SMP Wed Feb 22 17:37:40 EST 2012
Machine: x86_64
Instance name: qsng
Redo thread mounted by this instance: 1
Oracle process number: 50
Unix process pid: 16772, image: oracle@localhost.localdomain (TNS V1-V3)
*** 2015-04-02 21:23:11.764
*** SESSION ID:(72.16901) 2015-04-02 21:23:11.764
*** CLIENT ID:() 2015-04-02 21:23:11.764
*** SERVICE NAME:(SYS$USERS) 2015-04-02 21:23:11.764
*** MODULE NAME:(imp@localhost.localdomain (TNS V1-V3)) 2015-04-02 21:23:11.764
*** ACTION NAME:() 2015-04-02 21:23:11.764
=====================
PARSING IN CURSOR #139814426861864 len=34 dep=1 uid=0 oct=42 lid=0 tim=1427980991763775 hv=3913151867 ad='7f29153b02c8' sqlid='14ys3d7nmvxbv'
ALTER SESSION SET SQL_TRACE = TRUE
END OF STMT
EXEC #139814426861864:c=0,e=65,p=0,cr=0,cu=0,mis=1,r=0,dep=1,og=1,plh=0,tim=1427980991756277
CLOSE #139814426861864:c=0,e=4,dep=1,type=0,tim=1427980991764938
=====================
PARSING IN CURSOR #139814426870416 len=67 dep=0 uid=0 oct=47 lid=0 tim=1427980991765036 hv=4244958165 ad='54d651fb0' sqlid='78wmnnryh9uyp'
BEGIN EXECUTE IMMEDIATE 'ALTER SESSION SET SQL_TRACE = TRUE'; END;
END OF STMT
EXEC #139814426870416:c=2999,e=10041,p=0,cr=0,cu=0,mis=1,r=1,dep=0,og=1,plh=0,tim=1427980991765033
CLOSE #139814426870416:c=0,e=24,dep=0,type=0,tim=1427980991765547
=====================
PARSING IN CURSOR #139814426870416 len=50 dep=0 uid=0 oct=47 lid=0 tim=1427980991767424 hv=2924529365 ad='5450bc4b0' sqlid='8ppuwaur51jqp'
BEGIN SYS.DBMS_EXPORT_EXTENSION.SET_STATSON; END;
END OF STMT
PARSE #139814426870416:c=1999,e=1828,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=1,plh=0,tim=1427980991767422
=====================
PARSING IN CURSOR #139814426240744 len=41 dep=1 uid=0 oct=42 lid=0 tim=1427980991768326 hv=2301085140 ad='0' sqlid='g63fjfq4kgjfn'
alter session set TIMED_STATISTICS = TRUE
END OF STMT
PARSE #139814426240744:c=0,e=206,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=0,plh=0,tim=1427980991768325
EXEC #139814426240744:c=1000,e=5348,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=0,plh=0,tim=1427980991773803
CLOSE #139814426240744:c=0,e=4,dep=1,type=0,tim=1427980991773879
EXEC #139814426870416:c=2000,e=6363,p=0,cr=0,cu=0,mis=0,r=1,dep=0,og=1,plh=0,tim=1427980991773926
=====================
PARSING IN CURSOR #139814426340472 len=37 dep=1 uid=0 oct=3 lid=0 tim=1427980991776092 hv=1398610540 ad='553f2f718' sqlid='grwydz59pu6mc'
select text from view$ where rowid=:1
END OF STMT
PARSE #139814426340472:c=1000,e=824,p=0,cr=0,cu=0,mis=1,r=0,dep=1,og=4,plh=0,tim=1427980991776091
=====================
PARSING IN CURSOR #139814426336856 len=210 dep=2 uid=0 oct=3 lid=0 tim=1427980991791474 hv=864012087 ad='547f6c0c0' sqlid='96g93hntrzjtr'
select /*+ rule */ bucket_cnt, row_cnt, cache_cnt, null_cnt, timestamp#, sample_size, minimum, maximum,
 distcnt, lowval, hival, density, col#, spare1, spare2, avgcln from hist_head$ where obj#=:1 and intcol#=:2
END OF STMT
PARSE #139814426336856:c=1000,e=1332,p=0,cr=0,cu=0,mis=1,r=0,dep=2,og=3,plh=0,tim=1427980991791472
EXEC #139814426336856:c=2000,e=21773,p=0,cr=0,cu=0,mis=1,r=0,dep=2,og=3,plh=2239883476,tim=1427980991813432
FETCH #139814426336856:c=0,e=78,p=0,cr=2,cu=0,mis=0,r=0,dep=2,og=3,plh=2239883476,tim=1427980991813580
STAT #139814426336856 id=1 cnt=0 pid=0 pos=1 obj=448 op='TABLE ACCESS BY INDEX ROWID HIST_HEAD$ (cr=2 pr=0 pw=0 time=108 us)'
STAT #139814426336856 id=2 cnt=0 pid=1 pos=1 obj=450 op='INDEX RANGE SCAN I_HH_OBJ#_INTCOL# (cr=2 pr=0 pw=0 time=98 us)'
CLOSE #139814426336856:c=0,e=5,dep=2,type=3,tim=1427980991824838
EXEC #139814426336856:c=0,e=32,p=0,cr=0,cu=0,mis=0,r=0,dep=2,og=3,plh=2239883476,tim=1427980991825112
FETCH #139814426336856:c=0,e=15,p=0,cr=2,cu=0,mis=0,r=0,dep=2,og=3,plh=2239883476,tim=1427980991825174
CLOSE #139814426336856:c=0,e=4,dep=2,type=3,tim=1427980991825230
EXEC #139814426340472:c=5000,e=49516,p=0,cr=4,cu=0,mis=1,r=0,dep=1,og=4,plh=3684871272,tim=1427980991825784
FETCH #139814426340472:c=0,e=74,p=0,cr=2,cu=0,mis=0,r=1,dep=1,og=4,plh=3684871272,tim=1427980991825935
STAT #139814426340472 id=1 cnt=1 pid=0 pos=1 obj=69 op='TABLE ACCESS BY USER ROWID VIEW$ (cr=1 pr=0 pw=0 time=59 us cost=1 size=15 card=1)'
CLOSE #139814426340472:c=1000,e=79,dep=1,type=0,tim=1427980991826064
=====================
PARSING IN CURSOR #139814426240888 len=50 dep=0 uid=0 oct=3 lid=0 tim=1427980991827030 hv=2950256760 ad='548d195c8' sqlid='gnhvzburxkq3s'
SELECT value from sys.exu8opt where parameter = :1
END OF STMT
PARSE #139814426240888:c=7999,e=52316,p=0,cr=6,cu=0,mis=1,r=0,dep=0,og=1,plh=0,tim=1427980991827029
=====================
PARSING IN CURSOR #139814426339424 len=37 dep=1 uid=0 oct=3 lid=0 tim=1427980991827683 hv=1398610540 ad='553f2f718' sqlid='grwydz59pu6mc'
select text from view$ where rowid=:1
END OF STMT
PARSE #139814426339424:c=0,e=38,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,plh=3684871272,tim=1427980991827681
EXEC #139814426339424:c=0,e=23,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,plh=3684871272,tim=1427980991827862
FETCH #139814426339424:c=0,e=19,p=0,cr=2,cu=0,mis=0,r=1,dep=1,og=4,plh=3684871272,tim=1427980991827938
STAT #139814426339424 id=1 cnt=1 pid=0 pos=1 obj=69 op='TABLE ACCESS BY USER ROWID VIEW$ (cr=1 pr=0 pw=0 time=10 us cost=1 size=15 card=1)'
CLOSE #139814426339424:c=1000,e=79,dep=1,type=0,tim=1427980991828062
EXEC #139814426240888:c=2000,e=2039,p=0,cr=2,cu=0,mis=1,r=0,dep=0,og=1,plh=2941868576,tim=1427980991829442
FETCH #139814426240888:c=0,e=46,p=0,cr=0,cu=0,mis=0,r=1,dep=0,og=1,plh=2941868576,tim=1427980991829653
STAT #139814426240888 id=1 cnt=1 pid=0 pos=1 obj=0 op='FIXED TABLE FULL X$OPTION (cr=0 pr=0 pw=0 time=42 us cost=0 size=81 card=1)'
CLOSE #139814426240888:c=0,e=77,dep=0,type=0,tim=1427980991829862
CLOSE #139814426870416:c=0,e=15,dep=0,type=0,tim=1427980991830042
=====================
PARSING IN CURSOR #139814426870416 len=54 dep=0 uid=0 oct=47 lid=0 tim=1427980991830154 hv=1322764242 ad='54aea3138' sqlid='4jv63yp7dgjyk'
BEGIN SYS.DBMS_EXPORT_EXTENSION.SET_NO_OUTLINES; END;
END OF STMT
PARSE #139814426870416:c=0,e=67,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=0,tim=1427980991830153
=====================
PARSING IN CURSOR #139814426340328 len=48 dep=1 uid=0 oct=42 lid=0 tim=1427980991830368 hv=663246278 ad='0' sqlid='7kgh52cmshpf6'
alter session set CREATE_STORED_OUTLINES = FALSE
END OF STMT
PARSE #139814426340328:c=0,e=27,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=0,plh=0,tim=1427980991830367
EXEC #139814426340328:c=0,e=29,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=0,plh=0,tim=1427980991830510
CLOSE #139814426340328:c=0,e=3,dep=1,type=0,tim=1427980991830557
EXEC #139814426870416:c=0,e=338,p=0,cr=0,cu=0,mis=0,r=1,dep=0,og=1,plh=0,tim=1427980991830605
CLOSE #139814426870416:c=0,e=19,dep=0,type=1,tim=1427980991830823
=====================
PARSING IN CURSOR #139814426867336 len=59 dep=0 uid=0 oct=47 lid=0 tim=1427980991830946 hv=1151361180 ad='551bd30c0' sqlid='akubp592a0s4w'
BEGIN SYS.DBMS_EXPORT_EXTENSION.SET_NLS_NUMERIC_CHAR; END;
END OF STMT
PARSE #139814426867336:c=0,e=71,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=0,tim=1427980991830945
=====================
PARSING IN CURSOR #139814426339136 len=45 dep=1 uid=0 oct=42 lid=0 tim=1427980991831195 hv=1639245916 ad='0' sqlid='45sy639hv9u2w'
alter session set NLS_NUMERIC_CHARACTERS='.,'
END OF STMT
PARSE #139814426339136:c=0,e=23,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=0,plh=0,tim=1427980991831194
EXEC #139814426339136:c=0,e=38,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=0,plh=0,tim=1427980991831347
CLOSE #139814426339136:c=0,e=3,dep=1,type=0,tim=1427980991831396
EXEC #139814426867336:c=0,e=347,p=0,cr=0,cu=0,mis=0,r=1,dep=0,og=1,plh=0,tim=1427980991831438
=====================
PARSING IN CURSOR #139814426328016 len=37 dep=1 uid=0 oct=3 lid=0 tim=1427980991832676 hv=1398610540 ad='553f2f718' sqlid='grwydz59pu6mc'
select text from view$ where rowid=:1
END OF STMT
PARSE #139814426328016:c=0,e=39,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,plh=3684871272,tim=1427980991832675
EXEC #139814426328016:c=0,e=35,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,plh=3684871272,tim=1427980991832903
FETCH #139814426328016:c=1000,e=45,p=0,cr=2,cu=0,mis=0,r=1,dep=1,og=4,plh=3684871272,tim=1427980991833000
STAT #139814426328016 id=1 cnt=1 pid=0 pos=1 obj=69 op='TABLE ACCESS BY USER ROWID VIEW$ (cr=1 pr=0 pw=0 time=22 us cost=1 size=15 card=1)'
CLOSE #139814426328016:c=0,e=63,dep=1,type=1,tim=1427980991833122
=====================
PARSING IN CURSOR #139814426339280 len=35 dep=0 uid=0 oct=3 lid=0 tim=1427980991834448 hv=994821361 ad='5466a8e58' sqlid='3mwu4pcxnrj7j'
SELECT USERID,NAME FROM SYS.IMP9USR
END OF STMT
PARSE #139814426339280:c=2000,e=2133,p=0,cr=2,cu=0,mis=1,r=0,dep=0,og=1,plh=2709293936,tim=1427980991834446
EXEC #139814426339280:c=0,e=35,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=2709293936,tim=1427980991834763
FETCH #139814426339280:c=0,e=68,p=0,cr=2,cu=0,mis=0,r=1,dep=0,og=1,plh=2709293936,tim=1427980991835015
PARSE #139814426328016:c=0,e=23,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,plh=3684871272,tim=1427980991836151
EXEC #139814426328016:c=0,e=43,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,plh=3684871272,tim=1427980991836279
FETCH #139814426328016:c=0,e=20,p=0,cr=2,cu=0,mis=0,r=1,dep=1,og=4,plh=3684871272,tim=1427980991836349
CLOSE #139814426328016:c=0,e=16,dep=1,type=3,tim=1427980991836409
=====================
PARSING IN CURSOR #139814426320856 len=34 dep=0 uid=0 oct=3 lid=0 tim=1427980991837576 hv=3320476556 ad='551865ae8' sqlid='33pm4s32ynwwc'
SELECT NAME,VALUE FROM SYS.EXU9NLS
END OF STMT
PARSE #139814426320856:c=1000,e=1857,p=0,cr=2,cu=0,mis=1,r=0,dep=0,og=1,plh=415205717,tim=1427980991837575
EXEC #139814426320856:c=0,e=23,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=415205717,tim=1427980991837843
FETCH #139814426320856:c=0,e=83,p=0,cr=2,cu=0,mis=0,r=1,dep=0,og=1,plh=415205717,tim=1427980991838058
FETCH #139814426320856:c=0,e=20,p=0,cr=1,cu=0,mis=0,r=1,dep=0,og=1,plh=415205717,tim=1427980991838214
FETCH #139814426320856:c=0,e=16,p=0,cr=1,cu=0,mis=0,r=1,dep=0,og=1,plh=415205717,tim=1427980991838355
FETCH #139814426320856:c=0,e=16,p=0,cr=1,cu=0,mis=0,r=0,dep=0,og=1,plh=415205717,tim=1427980991838483
STAT #139814426320856 id=1 cnt=3 pid=0 pos=1 obj=98 op='TABLE ACCESS FULL PROPS$ (cr=5 pr=0 pw=0 time=72 us cost=2 size=84 card=3)'
CLOSE #139814426320856:c=0,e=8,dep=0,type=0,tim=1427980991838734
PARSE #139814426328016:c=0,e=20,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,plh=3684871272,tim=1427980991839322
EXEC #139814426328016:c=0,e=23,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,plh=3684871272,tim=1427980991839430
FETCH #139814426328016:c=0,e=17,p=0,cr=2,cu=0,mis=0,r=1,dep=1,og=4,plh=3684871272,tim=1427980991839495
CLOSE #139814426328016:c=0,e=15,dep=1,type=3,tim=1427980991839554
=====================
PARSING IN CURSOR #139814426320856 len=37 dep=0 uid=0 oct=3 lid=0 tim=1427980991842425 hv=2737225759 ad='54cd0a630' sqlid='as799jkjkdh0z'
SELECT COMPATIBLE FROM SYS.IMP9COMPAT
END OF STMT
PARSE #139814426320856:c=3000,e=3443,p=0,cr=2,cu=0,mis=1,r=0,dep=0,og=1,plh=1128103955,tim=1427980991842424
EXEC #139814426320856:c=0,e=39,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=1128103955,tim=1427980991842721
FETCH #139814426320856:c=4999,e=4966,p=0,cr=0,cu=0,mis=0,r=1,dep=0,og=1,plh=1128103955,tim=1427980991847851
CLOSE #139814426867336:c=0,e=18,dep=0,type=1,tim=1427980991848283
=====================
PARSING IN CURSOR #139814426863200 len=65 dep=0 uid=0 oct=47 lid=0 tim=1427980991848389 hv=1092642237 ad='5573014c0' sqlid='6mk0b8p0k0tdx'
BEGIN SYS.DBMS_EXPORT_EXTENSION.SET_IMP_TIMEZONE('+08:00'); END;
END OF STMT
PARSE #139814426863200:c=0,e=66,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=0,tim=1427980991848387
=====================
PARSING IN CURSOR #139814424939968 len=36 dep=1 uid=0 oct=42 lid=0 tim=1427980991849135 hv=938329428 ad='0' sqlid='gcqwsz0vyvjan'
alter session set TIME_ZONE='+08:00'
END OF STMT
PARSE #139814424939968:c=0,e=29,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=0,plh=0,tim=1427980991849133
EXEC #139814424939968:c=0,e=8,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=0,plh=0,tim=1427980991849270
CLOSE #139814424939968:c=0,e=3,dep=1,type=0,tim=1427980991849317
EXEC #139814426863200:c=0,e=882,p=0,cr=0,cu=0,mis=0,r=1,dep=0,og=1,plh=0,tim=1427980991849392
=====================
PARSING IN CURSOR #139814426317816 len=28 dep=0 uid=0 oct=3 lid=0 tim=1427980991850463 hv=1877579296 ad='550faead8' sqlid='7wf7949rym5j0'
SELECT DBTIMEZONE FROM DUAL
END OF STMT
PARSE #139814426317816:c=0,e=827,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=1,plh=1388734953,tim=1427980991850462
EXEC #139814426317816:c=0,e=38,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=1388734953,tim=1427980991850766
FETCH #139814426317816:c=0,e=10,p=0,cr=0,cu=0,mis=0,r=1,dep=0,og=1,plh=1388734953,tim=1427980991850897
STAT #139814426317816 id=1 cnt=1 pid=0 pos=1 obj=0 op='FAST DUAL (cr=0 pr=0 pw=0 time=2 us cost=2 size=0 card=1)'
CLOSE #139814426317816:c=0,e=6,dep=0,type=0,tim=1427980991851087
STAT #139814426320856 id=1 cnt=1 pid=0 pos=1 obj=0 op='HASH JOIN (cr=0 pr=0 pw=0 time=4973 us cost=0 size=2115 card=1)'
STAT #139814426320856 id=2 cnt=1 pid=1 pos=1 obj=0 op='FIXED TABLE FULL X$KSPPI (cr=0 pr=0 pw=0 time=3099 us cost=0 size=81 card=1)'
STAT #139814426320856 id=3 cnt=982 pid=1 pos=2 obj=0 op='FIXED TABLE FULL X$KSPPCV (cr=0 pr=0 pw=0 time=1112 us cost=0 size=203400 card=100)'
CLOSE #139814426320856:c=0,e=191,dep=0,type=0,tim=1427980991891702
STAT #139814426339280 id=1 cnt=1 pid=0 pos=1 obj=22 op='TABLE ACCESS CLUSTER USER$ (cr=2 pr=0 pw=0 time=68 us cost=1 size=18 card=1)'
STAT #139814426339280 id=2 cnt=1 pid=1 pos=1 obj=11 op='INDEX UNIQUE SCAN I_USER# (cr=1 pr=0 pw=0 time=34 us cost=0 size=0 card=1)'
CLOSE #139814426339280:c=0,e=123,dep=0,type=0,tim=1427980991891866
CLOSE #139814426863200:c=0,e=17,dep=0,type=0,tim=1427980991891919

这里分析，未发现任何错误，导出程序直接报Segmentation fault终止

原因分析
通过各种方式导入都报类似错误，初步确定是dmp文件异常，而dmp异常有几种可能性：
1.exp程序异常导致dmp文件异常，这里使用tns方式导出，排除该种可能
2.数据库内存出现异常导致dmp文件异常，重启数据库依旧，排除该种可能
3.因为测试过多种平台，版本，本地，远程导入，排除兼容性，远程传输损坏等
4.因为以前正常，突然异常，坏可以exp程序调用包异常，这种故障不能排除

解决方案
鉴于此，通过expcat.sql重新安装相关包，发现执行这个之后，exp导出程序，再imp导入一切正常
执行catexp.sql不影响生产库，只影响exp/imp程序

conn / as sysdba
@?/rdbms/admin/catexp.sql

这里再次提醒我们看到exp导出成功,不能证明imp一定ok,最好通过imp进行导入测试

InMemory评估利器—Oracle Database InMemory Advisor

我想大家都可能有一个疑问,数据库从以前版本升级到12C,计划使用in-memory特性,那我怎么评估哪些表放进去合适,放进去后,整体性能又会提升多少,需要in-memory内存为多大？这些问题如果人工去判断不一定准确,而且可能有很多遗落,Oracle也考虑到了这一点,因此发布了Oracle Database InMemory Advisor,从而来比较简单的实现数据库使用In-Memory简单配置.Oracle Database InMemory Advisor主要通过DBMS_INMEMORY_ADVISOR包分析 Active Session History (ASH), Automatic Workload Repository (AWR) and SQL Tuning Sets (STS)信息进行分析,给出建议.

Oracle Database InMemory Advisor使用前提
1.数据库版本为11.2.0.3及其以上版本(compatibility>=11.2.0.3)
2.需要有Database Tuning pack liscense支持(仅仅是liscense,不是功能)

安装Oracle Database InMemory Advisor

[oracle@localhost xff]$ unzip imadvisor.zip
Archive:  imadvisor.zip
  inflating: instimadv.sql
  inflating: dbmsimadv.sql
  inflating: prvtimadv.plb
  inflating: dbmsimadvint.plb
  inflating: prvtimadvint.plb
  inflating: schmimadv.sql
  inflating: imadvisor_version.sql
  inflating: imadvisor_load_report_templates.sql
  inflating: imadvisor_clone_view.sql
  inflating: imadvisor_analyze_and_report.sql
  inflating: imadvisor_spool_debug.sql
  inflating: imadvisor_export.sql
  inflating: imadvisor_DataPump.sql
  inflating: imadvisor_awr_augment_export.sql
  inflating: imadvisor_awr_augment_import.sql
  inflating: imadvisor_awr_augment_tables.sql
  inflating: imadvisor_fetch_recommendations.sql
[oracle@localhost xff]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Wed Mar 18 16:21:08 2015
Copyright (c) 1982, 2013, Oracle.  All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> @instimadv
Welcome to the Oracle Database In-Memory Advisor (DBMS_INMEMORY_ADVISOR)
installation.
The Oracle Database In-Memory Advisor is licensed as part of the Oracle Tuning Pack.
Do you currently have a valid Oracle Tuning Pack license with this database (Y/N)? Y
DBMS_INMEMORY_ADVISOR uses Active Session History (ASH), Automatic Workload
Repository (AWR) and optionally SQL Tuning Sets (STS) to determine which
tables, partitions and subpartitions to place In Memory for optimized analytics
processing performance.  DBMS_INMEMORY_ADVISOR produces a recommendation report
and a SQLPlus script to implement its recommendations.
This installation script will create user IMADVISOR and add object
definitions to the schema including the DBMS_INMEMORY_ADVISOR package.  This
installation script creates user IMADVISOR using the
IDENTIFIED BY password method. If you prefer to use either the
IDENTIFIED EXTERNALLY or IDENTIFIED GLOBALLY method, abort this installation
by pressing ^C. Then create user IMADVISOR using your preferred
method.  Add no objects to the IMADVISOR schema.  Then run this
installation script again.
These actions will be taken on the database to which you are currently
connected.
Please enter the connection ID for the current database?
qsng
This installation script creates a new Oracle database user and schema
named IMADVISOR for the operation of DBMS_INMEMORY_ADVISOR...
Please enter the password for user IMADVISOR?
For confirmation, please re-enter the password for user IMADVISOR?
Available tablespaces:
TABLESPACE_NAME
------------------------------
FSDATA
MYDATA
SYSAUX
SYSTEM
TEMP
UNDOTBS1
USERS
Please enter the default tablespace name for user IMADVISOR?
users
The In-Memory Advisor uses the Oracle directory object IMADVISOR_DIRECTORY
by default.
If you wish to create the IMADVISOR_DIRECTORY object, please enter an OS host
directory path for the IMADVISOR_DIRECTORY object.
If not, please press ENTER to continue.
? /tmp/xff
Connecting to IMADVISOR @ qsng..
Enter password:
Connected.
No errors.
No errors.
No errors.
No errors.
No errors.
No errors.
DBMS_INMEMORY_ADVISOR installation successful.
Users who will use the DBMS_INMEMORY_ADVISOR package must be GRANTed
EXECUTE on the DBMS_INMEMORY_ADVISOR package.
Please enter a comma separated list of Oracle Database users to whom
you wish EXECUTE on the DBMS_INMEMORY_ADVISOR package to be GRANTed?
SYS
GRANT EXECUTE ON dbms_inmemory_advisor TO SYS
While logged in as IMADVISOR or with sufficient privileges, you can
GRANT EXECUTE ON DBMS_INMEMORY_ADVISOR to additional users as needed.
DBMS_INMEMORY_ADVISOR installation and setup complete.
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

Oracle Database InMemory Advisor生成报告

[oracle@localhost xff]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Wed Mar 18 16:30:57 2015
Copyright (c) 1982, 2013, Oracle.  All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> @imadvisor_analyze_and_report
Specify the IM task name
The IM Advisor generates a report as  imadvisor_<taskname>.html file
in the current working directory
The sql file is generated as imadvisor_sql_<taskname>.sql
Enter value for im_task_name: xifenfei
IM Task name Specified: xifenfei
Enter begin time for report:
--    Valid input formats:
--      To specify absolute begin time:
--        [MM/DD[/YY]] HH24:MI[:SS]
--        Examples: 02/23/03 14:30:15
--                  02/23 14:30:15
--                  14:30:15
--                  14:30
--      To specify relative begin time: (start with '-' sign)
--        -[HH24:]MI
--        Examples: -1:15  (SYSDATE - 1 Hr 15 Mins)
--                  -25    (SYSDATE - 25 Mins)
Enter value for begin_time: -3
Report begin time specified: -3
old 102:   lbtime_in  := nvl('&&begin_time', '-60');
new 102:   lbtime_in  := nvl('-3', '-60');
old 104:   :btime := to_char( begin_time, '&&imadvisor_time_format' );
new 104:   :btime := to_char( begin_time, 'YYYY-MON-DD HH24:MI:SS.FF' );
Enter duration in minutes starting from begin time:
Defaults to SYSDATE - begin_time
Enter value for duration:
Report duration specified:
old   8:   begin_time := to_timestamp(:btime, '&&imadvisor_time_format');
new   8:   begin_time := to_timestamp(:btime, 'YYYY-MON-DD HH24:MI:SS.FF');
old  18:   duration   := nvl('&&duration', since_begin_time);
new  18:   duration   := nvl('', since_begin_time);
old  29:   :etime := to_char( end_time, '&&imadvisor_time_format' );
new  29:   :etime := to_char( end_time, 'YYYY-MON-DD HH24:MI:SS.FF' );
Using 2015-MAR-18 16:28:22.000000000 as report begin time
Using 2015-MAR-18 16:31:22.000000000 as report end time
IM Advisor: Adding Statistics..
IMADVISOR: Finished Adding Statistics
IMADVISOR: Finished Executing the task
IM Advisor: Generating Recommendations..
imadvisor_xifenfei.html
imadvisor_sql_xifenfei.html
imadvisor_object_xifenfei.html
imadvisor_xifenfei.sql
'Fetching recommendation files for task xifenfei'
IM Advisor generated report in imadvisor_xifenfei.html
IM Advisor genreated DDL script in imadvisor_xifenfei.sql
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@localhost xff]$ ls -ltr *xifenfei*
-rw-r--r-- 1 oracle oinstall   887 Mar 18 16:33 imadvisor_xifenfei.sql
-rw-r--r-- 1 oracle oinstall 30175 Mar 18 16:33 imadvisor_xifenfei.html
-rw-r--r-- 1 oracle oinstall 13576 Mar 18 16:33 imadvisor_sql_xifenfei.html
-rw-r--r-- 1 oracle oinstall  8931 Mar 18 16:33 imadvisor_object_xifenfei.html
-rw-r--r-- 1 oracle oinstall  3405 Mar 18 16:33 imadvisor_auxiliary_xifenfei.html

这里输入的Task name为:xifenfei,Oracle Database InMemory Advisor结果
imadvisor_xifenfei.html是InMemory Advisor的一个整体描述
imadvisor_xifenfei.sql是InMemory Advisor生成的表级别的INMEMORY语句,可以直接通过@方式执行,或者修改后执行
imadvisor_sql_xifenfei.html主要是InMemory Advisor中关于sql的分析报告
imadvisor_object_xifenfei.html是InMemory Advisor中建议InMemory处理的对象分析报告
imadvisor_auxiliary_xifenfei.html 是一个辅助的总结

卸载Oracle Database InMemory Advisor

[oracle@localhost ~]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Wed Mar 18 21:55:49 2015
Copyright (c) 1982, 2013, Oracle.  All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> drop user imadvisor cascade;
User dropped.

补充内容
1.在某些情况下,可能无法直接创建用户imadvisor,或者创建目录imadvisor_directory,可以通过类似命令创建,并修改instimadv.sql脚本屏蔽掉交互式安装

create user IMADVISOR identified by Oracle_123 DEFAULT TABLESPACE system;
CREATE OR REPLACE DIRECTORY imadvisor_directory AS '/tmp/xff/txt';
GRANT READ, WRITE ON DIRECTORY imadvisor_directory TO IMADVISOR;

2.在执行@imadvisor_analyze_and_report生成报告,可能会遇到ORA-24817: Unable to allocate the given chunk for current lob operation,解决方案为：

1. Copy the original script to a new location to serve as a backup.
2. Edit the script imadvisor_fetch_recommendations.sql and change the line in the script:
SET LONGCHUNKSIZE 2000000000
to
SET LONGCHUNKSIZE 32767
3. Save and run the script.

参考文档
Oracle Database InMemory Advisor (Doc ID 1965343.1)
Using Inmemory Advisor Errors When Running Imadvisor_analyze_and_report (Doc ID 1987462.1)