ORADBCA | Page 2

Home

Using WGET to download My Oracle Support Patches

Posted on 2015 年 8 月 19 日 by oradbca

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

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

	username=dba@oradbca.com

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

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

创建lvm Found duplicate PV的错误

Posted on 2015 年 3 月 26 日 by oradbca

在创建lvm的时候，报一下错误：

[root@oradbca ~]#  vgcreate vg_data /dev/sddlmaa
  Found duplicate PV YjFs3QZw30enKplsoQ5YmFFnDV08Owyy: using /dev/sdb not /dev/sddlmaa
  Found duplicate PV YjFs3QZw30enKplsoQ5YmFFnDV08Owyy: using /dev/sdc not /dev/sdb
  Found duplicate PV YjFs3QZw30enKplsoQ5YmFFnDV08Owyy: using /dev/sdd not /dev/sdc
  Found duplicate PV YjFs3QZw30enKplsoQ5YmFFnDV08Owyy: using /dev/sde not /dev/sdd
  Found duplicate PV YjFs3QZw30enKplsoQ5YmFFnDV08Owyy: using /dev/sddlmaa not /dev/sde
  Found duplicate PV YjFs3QZw30enKplsoQ5YmFFnDV08Owyy: using /dev/sdb not /dev/sddlmaa
  Found duplicate PV YjFs3QZw30enKplsoQ5YmFFnDV08Owyy: using /dev/sdc not /dev/sdb
  Found duplicate PV YjFs3QZw30enKplsoQ5YmFFnDV08Owyy: using /dev/sdd not /dev/sdc
  Found duplicate PV YjFs3QZw30enKplsoQ5YmFFnDV08Owyy: using /dev/sde not /dev/sdd
  Volume group "vg_data" successfully created

虽然创建成功了，，但是感觉有点别扭。

同时创建的盘也不是我多路径上的盘

[root@oradbca ~]# pvdisplay 
  Found duplicate PV YjFs3QZw30enKplsoQ5YmFFnDV08Owyy: using /dev/sdb not /dev/sddlmaa
  Found duplicate PV YjFs3QZw30enKplsoQ5YmFFnDV08Owyy: using /dev/sdc not /dev/sdb
  Found duplicate PV YjFs3QZw30enKplsoQ5YmFFnDV08Owyy: using /dev/sdd not /dev/sdc
  Found duplicate PV YjFs3QZw30enKplsoQ5YmFFnDV08Owyy: using /dev/sde not /dev/sdd
  --- Physical volume ---
  PV Name               /dev/sde
  VG Name               vg_data
  PV Size               2.44 TiB / not usable 4.00 MiB
  Allocatable           yes 
  PE Size               4.00 MiB
  Total PE              639999
  Free PE               367
  Allocated PE          639632
  PV UUID               YjFs3Q-Zw30-enKp-lsoQ-5YmF-FnDV-08Owyy

原因是：默认的扫描设置是扫描所有的磁盘，由于多路径的问题，多块磁盘前面的元数据信息是一致的，导致PV信息相同

所以直接修改lvm的配置文件，修改扫描的策略就可以了。

我的fdisk -l 内容


Disk /dev/sda: 1199.1 GB, 1199101181952 bytes
255 heads, 63 sectors/track, 145782 cylinders
Units = cylinders of 16065 * 512 = 8225280 bytes
Sector size (logical/physical): 512 bytes / 512 bytes
I/O size (minimum/optimal): 512 bytes / 512 bytes
Disk identifier: 0x0000a399

   Device Boot      Start         End      Blocks   Id  System
/dev/sda1   *           1          26      204800   83  Linux
Partition 1 does not end on cylinder boundary.
/dev/sda2              26        8185    65536000   82  Linux swap / Solaris
/dev/sda3            8185      145783  1105255424   8e  Linux LVM

Disk /dev/sdb: 2684.4 GB, 2684354560000 bytes
255 heads, 63 sectors/track, 326354 cylinders
Units = cylinders of 16065 * 512 = 8225280 bytes
Sector size (logical/physical): 512 bytes / 512 bytes
I/O size (minimum/optimal): 512 bytes / 512 bytes
Disk identifier: 0x00000000


Disk /dev/sdc: 2684.4 GB, 2684354560000 bytes
255 heads, 63 sectors/track, 326354 cylinders
Units = cylinders of 16065 * 512 = 8225280 bytes
Sector size (logical/physical): 512 bytes / 512 bytes
I/O size (minimum/optimal): 512 bytes / 512 bytes
Disk identifier: 0x00000000


Disk /dev/mapper/VolGroup-LogVol00: 1131.8 GB, 1131774214144 bytes
255 heads, 63 sectors/track, 137597 cylinders
Units = cylinders of 16065 * 512 = 8225280 bytes
Sector size (logical/physical): 512 bytes / 512 bytes
I/O size (minimum/optimal): 512 bytes / 512 bytes
Disk identifier: 0x00000000


Disk /dev/sdd: 2684.4 GB, 2684354560000 bytes
255 heads, 63 sectors/track, 326354 cylinders
Units = cylinders of 16065 * 512 = 8225280 bytes
Sector size (logical/physical): 512 bytes / 512 bytes
I/O size (minimum/optimal): 512 bytes / 512 bytes
Disk identifier: 0x00000000


Disk /dev/sde: 2684.4 GB, 2684354560000 bytes
255 heads, 63 sectors/track, 326354 cylinders
Units = cylinders of 16065 * 512 = 8225280 bytes
Sector size (logical/physical): 512 bytes / 512 bytes
I/O size (minimum/optimal): 512 bytes / 512 bytes
Disk identifier: 0x00000000


Disk /dev/sddlmaa: 2684.4 GB, 2684354560000 bytes
255 heads, 63 sectors/track, 326354 cylinders
Units = cylinders of 16065 * 512 = 8225280 bytes
Sector size (logical/physical): 512 bytes / 512 bytes
I/O size (minimum/optimal): 512 bytes / 512 bytes
Disk identifier: 0x00000000


Disk /dev/mapper/vg_data-lv_data: 2682.8 GB, 2682811056128 bytes
255 heads, 63 sectors/track, 326166 cylinders
Units = cylinders of 16065 * 512 = 8225280 bytes
Sector size (logical/physical): 512 bytes / 512 bytes
I/O size (minimum/optimal): 512 bytes / 512 bytes
Disk identifier: 0x00000000

修改文件 /etc/lvm/lvm.conf

由原来的

	filter = [ "a/.*/" ]

修改成

	filter = [ "a/sddl.*/","a/sda.*/", "r/sd.*/" ]

a 是允许 b是拒绝

由于我在sda上也有lvm 所以单独允许。

修改后允许 vgscan -v 重建缓存

运行lvmdiskscan 查看当前的访问路径

[root@whljk ~]# lvmdiskscan 
  /dev/ram0            [      16.00 MiB] 
  /dev/sddlmaa         [       2.44 TiB] LVM physical volume
  /dev/root            [       1.03 TiB] 
  /dev/ram1            [      16.00 MiB] 
  /dev/sda1            [     200.00 MiB] 
  /dev/vg_data/lv_data [       2.44 TiB] 
  /dev/ram2            [      16.00 MiB] 
  /dev/sda2            [      62.50 GiB] 
  /dev/ram3            [      16.00 MiB] 
  /dev/sda3            [       1.03 TiB] LVM physical volume
  /dev/ram4            [      16.00 MiB] 
  /dev/ram5            [      16.00 MiB] 
  /dev/ram6            [      16.00 MiB] 
  /dev/ram7            [      16.00 MiB] 
  /dev/ram8            [      16.00 MiB] 
  /dev/ram9            [      16.00 MiB] 
  /dev/ram10           [      16.00 MiB] 
  /dev/ram11           [      16.00 MiB] 
  /dev/ram12           [      16.00 MiB] 
  /dev/ram13           [      16.00 MiB] 
  /dev/ram14           [      16.00 MiB] 
  /dev/ram15           [      16.00 MiB] 
  2 disks
  18 partitions
  1 LVM physical volume whole disk
  1 LVM physical volume

正常显示

ORA-08104: this index object ##### is being online built or rebuilt

Posted on 2015 年 1 月 27 日 by oradbca

这个错误由于在异常终止online rebuild的时候，还没来得及清理相应的临时段和标志位，系统认为online rebuild操作还在进行造成的。

根据文章 Session Was Killed During The Rebuild Of Index ORA-08104 (文档 ID 375856.1)说明

1.等smon清理

2.在10g之后用dbms_repair.online_index_clean手工清理这些信息，在Oracle 9i下，需要打Bug 3805539 后才可以使用该工具

DECLARE
RetVal BOOLEAN;
OBJECT_ID BINARY_INTEGER;
WAIT_FOR_LOCK BINARY_INTEGER;
BEGIN
OBJECT_ID := 53367;
WAIT_FOR_LOCK := NULL;
RetVal := SYS.DBMS_REPAIR.ONLINE_INDEX_CLEAN ();
COMMIT;
END;
/

3.修改字典数据

（1）先查看ind$ flags 标志，如果不正确，就减去512.

sql>update ind$ set flags=flags-512 where obj#=<object id>;

（2）drop journal table，这个步骤可能会报资源忙，因为有大量的日志正在插入，可以反复重试一下。

sql>drop table <owner>.sys_journal_<object_id>;

注意：

这个步骤不能反，如果先删除sys_journal_<object_id>临时表，然后再修改index的flags状态，则会报出ora-600 [4610]号错误，即数据字典不一致的错误。

XDB is INVALID in DBA_REGISTRY after Fresh Installation

Posted on 2014 年 11 月 14 日 by oradbca

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

SQL> select comp_name, version, status from dba_registry;

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

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

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

15 rows selected.

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

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

2 rows selected.

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

MOS:

SOLUTION

To implement the solution, please execute the following steps:

– Disable "password complexity verification" feature

	SQL> connect / as sysdba

		drop function verify_function;

		ALTER PROFILE DEFAULT LIMIT PASSWORD_VERIFY_FUNCTION null;

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

	SQL> connect / as sysdba

		grant execute on dbms_sql to xdb;

		grant execute on dbms_lob to xdb;

		grant execute on utl_file to xdb;

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

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

XML DB and all SYS objects are valid now:

	SQL> select comp_name, version, status from dba_registry;

		COMP_NAME                          VERSION                 STATUS

		———————————- ———————– ———-

		Oracle XML Database                11.2.0.2.0              VALID

		Oracle Database Catalog Views      11.2.0.2.0              VALID

		Oracle Database Packages and Types 11.2.0.2.0              VALID

		JServer JAVA Virtual Machine       11.2.0.2.0              VALID

		Oracle XDK                         11.2.0.2.0              VALID

		Oracle Database Java Packages      11.2.0.2.0              VALID
	
		SQL> select object_type,object_name, owner from dba_objects

		where status ='INVALID' and

		owner in ('SYS','SYSTEM','XDB')

		order by owner, object_name, object_type;
	
		no rows selected.

XDB is INVALID after ORA-31084 ORA-43853 errors during install

Posted on 2014 年 11 月 14 日 by oradbca

新安装的XDB组件状态是INVALID的，报下面的错：

SQL> set echo on
SQL> -- @catqm.sql    
SQL>
SQL> @?/rdbms/admin/catqm xdbpwd XDB TEMP NO
SQL> Rem
...
...

SQL> declare
  2    ACLXSD BFILE := dbms_metadata_hack.get_bfile('acl.xsd.11.2');
  3    ACLURL VARCHAR2(2000) := 'http://xmlns.oracle.com/xdb/acl.xsd';
  4  begin
  5  xdb.dbms_xmlschema.registerSchema(ACLURL, ACLXSD, FALSE, FALSE, FALSE, TRUE,
  6                                    FALSE, 'XDB',
  7                                   options => DBMS_XMLSCHEMA.REGISTER_BINARYXML);
  8  
  9  end;
 10  /
declare
*
ERROR at line 1:
ORA-31084: error while creating table "XDB"."XDB$ACL" for element "acl"
ORA-43853: SECUREFILE lobs cannot be used in non-ASSM tablespace "XDB"
ORA-06512: at "XDB.DBMS_XMLSCHEMA_INT", line 37
ORA-06512: at "XDB.DBMS_XMLSCHEMA", line 65
ORA-06512: at "XDB.DBMS_XMLSCHEMA", line 136
ORA-06512: at line 5

SQL> 
SQL> -- Disable XRLS hierarchy priv check for xdb$acl and xdb$schema tables
SQL> BEGIN
  2     xdb.dbms_xdbz.disable_hierarchy('XDB', 'XDB$ACL');
  3     xdb.dbms_xdbz.disable_hierarchy('XDB', 'XDB$SCHEMA');
  4  END;
  5  /
BEGIN
*
ERROR at line 1:
ORA-31061: XDB error: DBMS_XDBZ.DISABLE_HIERARCHY
ORA-06512: at "XDB.DBMS_XDBZ0", line 131
ORA-06512: at "XDB.DBMS_XDBZ0", line 802
ORA-29329: Table not of type XMLType
ORA-06512: at "XDB.DBMS_XDBZ", line 66
ORA-06512: at line 2

SQL> -- INSERT bootstrap AND root acl's
SQL> DECLARE
  2    b_abspath          VARCHAR2(200);
  3    b_data             VARCHAR2(2000);
...
 99     retbool := dbms_xdb.createresource(ro_abspath, ro_data);
100  END;
101  /
DECLARE
*
ERROR at line 1:
ORA-31114: XDB configuration has been deleted or is corrupted
ORA-06512: at "XDB.DBMS_XDB", line 174
ORA-06512: at line 96

...

查了MOS：

If COMPATIBLE is set to 11.2 or higher, XDB installation defaults to SecureFiles for XMLType storage regardless of the value of the 4th argument passed to catqm.sql (YES OR NO).

在11.2版本后，默认的表空间segment_space_management为AUTO

SQL> select tablespace_name, extent_management, segment_space_management
  2  from dba_tablespaces;

TABLESPACE_NAME EXTENT_MAN SEGMEN
--------------- ---------- ------
SYSTEM          LOCAL      MANUAL
SYSAUX          LOCAL      AUTO
UNDOTBS1        LOCAL      MANUAL
TEMP            LOCAL      MANUAL
USERS           LOCAL      AUTO
XDB             LOCAL      MANUAL

6 rows selected.

SQL> @?/rdbms/admin/catqm xdbpwd SYSAUX TEMP NO

2022年8月
一	二	三	四	五	六	日
« 九
1	2	3	4	5	6	7
8	9	10	11	12	13	14
15	16	17	18	19	20	21
22	23	24	25	26	27	28
29	30	31