Month: August 2015

Oracle ACE Associate Award

arcsdegeoLeave a comment

I have been awarded the Oracle ACE Associate Award, this award is given to elite technical professionals in Oracle technology, for their expertise in addition to their contribution within Oracle community.

I am very happy to say that i am the First Saudi National IT Professional to receive such distinction 🙂  , and among the best of Oracle ACE’s from around the world.

My Oracle ACE Profile:

https://apex.oracle.com/pls/apex/f?p=19297:4:::NO:4:P4_ID:13500

Oracle ACE News Letter for the Month of September 2015: 

http://www.oracle.com/us/dm/nsl100458614-na-us-nl-newsl3-ev-2672873.html?elq_mid=26233&sh=2214262326151412682615251372222&cmid=WWMK14057711MPP002C010

Oracle ACE Associate

Emad Al-Mousa Oracle ACE Profile

Oracle ACE News Letter

الحمد لله رب العالمين

Performing Import Data Pump with EXCLUDE=STATISTICS

arcsdegeoLeave a comment

some DBA’s use EXCLUDE=STATISTICS parameter in their import datapump command in order to speed up the import process.

*** example of impdp command:
impdp power_user/power43b Directory=DUMP_DIR Dumpfile=Exp_schema.dmp Logfile=Exp.log EXCLUDE=STATISTICS
However, in reality indexes created while importing are “analyzed” automatically, and this is performed by design. Only tables that are not going to be “analyzed” while importing.

the reason behind that is there is a hidden parameter called “_optimizer_compute_index_stats” is set to “TRUE“.
its not advisable that you manipulate Oracle hidden parameters, you need to consult Oracle and open SR with them in case you need their consultation.

to check that indexes were analyzed after the import, use the sql query:

SQL> select OWNER, INDEX_NAME, TABLE_NAME, LAST_ANALYZED from dba_indexes where OWNER=’power_user’;

Have A Nice Summmmmmmer…

Summer-Sun-3

ORA-00604: error occurred at recursive SQL level 1 ORA-06598: insufficient INHERIT PRIVILEGES privilege ORA-06512: at “XDB.DBMS_CSX_INT”, line 1 while upgrading to 12c

arcsdegeoLeave a comment

while upgrading your database to 12c , its very important that you monitor your alert.log file to check for errors and potential problems.

to find out the location of you alert log file, query the following:

SQL> select * form v$diag_info;

one of the databases that i have upgraded, i found the following error being thrown in the alert.log file:

XDB SGA reset to NULL.

ORA-00604: error occurred at recursive SQL level 1

ORA-06598: insufficient INHERIT PRIVILEGES privilege

ORA-06512: at “XDB.DBMS_CSX_INT”, line 1

this error can be ignored, to ensure that your XDB component is ok run the utilrip after the upgrade is completed:

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

make sure that the XDB is fine by querying:

SQL> select COMP_NAME,VERSION,STATUS from dba_registry;

so basically the error is not dangerous but you need to verify that everything is fine.

*************************************** …….*****************************************   🙂

Oracle Spatial famous error ORA-13203: failed to read USER_SDO_GEOM_METADATA view

arcsdegeoLeave a comment

One of the common errors faced by DBA’s/Oracle Spatial analysts is the following error:

ORA-29855: error occurred in the execution of ODCIINDEXCREATE routine

ORA-13203: failed to read USER_SDO_GEOM_METADATA view

ORA-13203: failed to read USER_SDO_GEOM_METADATA view

ORA-06512: at “MDSYS.SDO_INDEX_METHOD_10I”, line 10

the reason i am writing about it is to help the community and new analysts to Oracle spatial to fix this problem in speedy manner.

SO the solution:

before creating the spatial index you need to update(insert) in the USER_SDO_GEOM_METADATA view, this is required for each new layer being created.

USER_SDO_GEOM_METADATA contains metadata information for all spatial tables owned by the user.the view contains information about, for example:

insert into MDSYS.user_sdo_geom_metadata

(table_name, column_name, diminfo, srid)

values (‘GEO_TST’,’SHAPE1′, sdo_dim_array(sdo_dim_element(‘X’,0,20,0.005), sdo_dim_element(‘Y’,0,20,0.005) ), null );

Also, make sure that when you create the index afterwards…the account you are using has explicit “CREATE TABLE” & “CREATE SEQUENCE” privileges granted to it. Otherwise you will receive the following errors:

ORA-13249: internal error in Spatial index: [mdidxrbd]
ORA-13249: Error in Spatial index: index build failed
ORA-13249: Error in R-tree: [mdrcrtscrt]
ORA-13231: failed to create index table [MDRS_13A71$] during R-tree creation
ORA-13249: Stmt-Execute Failure: CREATE SEQUENCE “HR”.”MDRS_13A71$” ORDER START WITH 1 CACHE 100
ORA-29400: data cartridge error
ORA-01031: insufficient privileges
ORA-06512: at “MDSYS.SDO_INDEX_METHOD_10I”, line 10

 

 

TRANSFORM=DISABLE_ARCHIVE_LOGGING in Oracle 12c

arcsdegeoLeave a comment

a new parameter was introduced in Oracle 12c  for the datapump operations. this parameter is TRANSFORM=DISABLE_ARCHIVE_LOGGING

To explore this parameter let me first start importing a schema in my database without specifying the parameter:

my current database is in “archive log mode”

select LOG_MODE , force_logging from v$database;

query for archive log mode

The time taken for this import was 2 hours !!!

And, 55 GB of archive storage was generated during the import.

Now, i will re-import again using the parameter TRANSFORM=DISABLE_ARCHIVE_LOGGING in my below command:

nohup $ORACLE_HOME/bin/impdp “‘/as sysdba ‘”  DIRECTORY=EXP_DIR DUMPFILE=SCHEMA_JUNE28.dmp transform=disable_archive_logging:y logfile=IMP_disable_archive.log  &

the time taken to finish the import was 1 hour 28 minutes !!! half an hour less the the normal standard import operation.

And, the storage of the archive log was around 30 GB.

The conclusion of this exercise is that the new parameter introduced in 12c will benefit in 2 things:

  • faster import process completion
  • less storage for archive generation

Remark: make sure that when you use this parameter ,that if you have a logical standby setup for the database, that the database is in “archive log mode” and “force logging” is enabled.