分类目录归档：Oracle

kfed恢复误删除磁盘组

发表于 2015 年 5 月 1 日 由 惜分飞

在某些情况下,可能因为误操作,不小先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

发表在 Oracle ASM, 非常规恢复 | 标签为 , , , , , , | 评论关闭

dbms_diskgroup拷贝block/datafile

发表于 2015 年 4 月 29 日 由 惜分飞

对于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 ASM | 标签为 , , , , , | 评论关闭

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

发表于 2015 年 4 月 23 日 由 惜分飞

数据库从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中密码验证配置,免得导致用户无法创建的错误(在升级中有些组件需要重建用户)

发表在 Oracle安装升级 | 标签为 , , , | 评论关闭