Year: 2015

How to create a plug-gable database in Oracle 12c Multi-tenant Architecture ?

arcsdegeoLeave a comment

One of the fundamental questions for the Oracle DBA who is exploring Oracle 12c new multi-tenant architecture is how to create a pluggable database.

here i am exploring 2 methods……Let us start.

Method 1: using SQL Developer

After connecting to the database, you need to add the “DBA” option.

In sql developer go to “view”——–> then choose “DBA.

Then add the already defined database connection:

DBA database connection in SQL Developer

And choose “OK”
Then, right click on “Container Database” and choose “Create_Pluggable Database” option

container database in sql developer

create pluggable database GUI

The associated SQL definition output:

CREATE PLUGGABLE DATABASE DUMMY_PDB ADMIN USER DUMMY_ADMIN IDENTIFIED BY dummy55

FILE_NAME_CONVERT=(

‘/oracle/DCE/DCE/pdbseed/’, ‘/oracle/DCE/DCE/dummypdb/’

)

STORAGE (

MAXSIZE 50M

MAX_SHARED_TEMP_SIZE UNLIMITED

)

When I hit “Apply”, I received the error ORA-65113: value of MAX_PDB_STORAGE property  for the PDB is too low

create pluggable database error

When I increased the value to 900 MB, the execution was successful

create pluggable database success

You will find that the new pluggable database has been created successfully:

pluggable database tree added sql developer

Method 2: using Traditional SQL Statement

—Creating Pluggable database from “seed”

SQL> CREATE PLUGGABLE DATABASE sales_pdb

ADMIN USER dbapower IDENTIFIED BY fofo431

ROLES = (dba)

DEFAULT TABLESPACE TS_SALES_DATA_01

DATAFILE ‘/oracle/DB45/sapdata1/sales01.dbf’ SIZE 100M AUTOEXTEND OFF

FILE_NAME_CONVERT = (‘/oracle/DB45//DB45/pdbseed/’,

‘/oracle/DB45//DB45/salespdb/’)

STORAGE (MAXSIZE 1G)

PATH_PREFIX = ‘/oracle/DB45/DB45//salespdb/’;

sqlplus error for Oracle 12c in IBM AIX : rtld: 0712-001 Symbol CreateIoCompletionPort was referenced

arcsdegeoLeave a comment

I have received the below error when I tried using sqlplus on IBM AIX server with Oracle 12c installed on it and upgraded:

sqlplus ‘/as sysdba’

exec(): 0509-036 Cannot load program sqlplus because of the following errors:

rtld: 0712-001 Symbol CreateIoCompletionPort was referenced

      from module /oracle/oradb/product/12.1.0/lib/libttsh12.so(), but a runtime definition

      of the symbol was not found.

rtld: 0712-001 Symbol GetMultipleCompletionStatus was referenced

      from module /oraclex/oradbp07/product/12.1.0/lib/libttsh12.so(), but a runtime definition

      of the symbol was not found.

rtld: 0712-002 fatal error: exiting.

Solution

Change the IOCP IBM AIX parameter from “defined” to “available”, this action requires root privilege

# smitty iocp
Select Change / Show Characteristics of I/O Completion Ports.
Change configured state at system restart from Defined to Available

Remark: it is a prerequisite to change IOCP parameter before upgrading the database to 12c in IBM AIX environment.

I hope this helps………

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.

Presenting at the Saudi National GIS Symposium in 2014

arcsdegeoLeave a comment

last year i have presented at the ” 9th Saudi National GIS Symposium“.  In this conference i talked about GIS Database Security which is a unique topic, as it mixes IT & GIS fields in enterprise systems.

The presentation tackled many topics such as : the architecture of Security layers in enterprise IT GIS system, implementing best security practices in GIS database using Oracle Security products (Network Encryption, Label Security, Access Controls).

عماد الموسى

عماد الموسى - مؤتمر

http://www.saudigis.org/

Creating 12c database using DBCA (Database Configuration Assistant)

arcsdegeoLeave a comment

In this blog article i am providing overview steps in creating a fresh “NEW” oracle 12c database in Unix environment.

After installing oracle database 12c binaries successfully, proceed with the following steps and there are 2 paths for the database creation you can follow one of them depending on your requirements.

PATH Number 1

cd $ORACLE_HOME/bin

export DISPLAY=PC_machine_name:0.0

/usr/bin/X11/xclock

dbca

The database configuration assistant will be invoked with “Create Database” option.

1

Click “Next”

Choose and insert the required information such as:

Global Database Name:

Storage Type: “File System” for Non-ASM environment.

Database Files Location:

Character Set (be careful and choose the correct one, once you create the database you can’t change the default character set of the database).

2

3

Now, if you want to choose the “Advanced Mode”

PATH Number 2

You will choose a template based on type of applications working against your database.

The General Purpose or Transaction template and the Data Warehouse template create a database with the COMPATIBLE initialization parameter set to 12.1.0.2.0

1

2

3

You can either choose to configure enterprise manager or not.

4

I choose to have both “SYS” and “SYSTEM” having the same password (you can change system password later on if you want to)

5 6 7

You can enable database vault or label security while creating the database

8

For memory setup I choose “automatic memory management”

9

For process I choose the default one (300 processes)

10

For connection mode I choose “Shared Server Mode” to serve multiple application simultaneously

11

Choose the “create database” and I have chosen to “generate database creation script” for reference.

12

Review the database creation summary

13

Exploring Multithreading Setup For Oracle 12c in UNIX environment

arcsdegeo1 Comment

Normally when we execute the grep unix command to list oracle processes, you will find all “detailed” oracle process:

ps -ef | grep [o]ra_

unix-oracle-process

this is the normal process architecture in Oracle database hosted on Unix environment prior to 12c release.

In oracle 12c you can switch to Multi-threaded architecture by adding a new parameter in your init.ora file.

go to $ORACLE_HOME/dbs  and edit your init.ora (text init file in case you are not configuration SPFILE) and add the parameter:

threaded_execution=TRUE

threaded_executtion parameter

Then, create a password file per the documentation: https://geodatamaster.wordpress.com/2015/05/25/oracle-12c-database-password-file/

Then bounce the Oracle instance (shutdown/startup) the database.

Please note that the OS authentication won’t’ work anymore after changing the mode to “multi-threading”, and you will receive the below error:

sqlplus error

If you face problems connecting to the database, you can connect internally through Bequeath protocol: http://www.dbi-services.com/index.php/blog/entry/multithreaded-12c-and-connect-as-sysdba

After enabling multi-threading, the oracle process on the server  will look like this:

unix oracle process multithreading

Interesting part that in IBM AIX environment the Oracle database process using topas command shows the database is consuming around 97% of the CPU ….. This will requires more investigation from my side 😐

IBM AIX topas for multithreading