Blog Archives

How to Move USER_SDO_GEOM_METADATA like a Grandpa

Sometimes moving a small amount of data in Oracle database requires more work than the actual task you need to complete. DBAs always have their tools (PL/SQL Developer Text Importer is my favorite one) to move a small amount of data between databases.
But when it comes to nested tables this might be a bit challenging. USER_SDO_GEOM_METADATA table (for those of you who are not familiar with it, it is the catalog information of spatial layers in an Oracle database) has no exception to that. Here how it is easy to handle it
The structure of USER_SDO_GEOM_METADATA table has the following structure

SQL> desc user_sdo_geom_metadata
 Name					   Null?    Type
 ----------------------------------------- -------- ----------------------------
 TABLE_NAME				   NOT NULL VARCHAR2(32)
 COLUMN_NAME				   NOT NULL VARCHAR2(1024)
 DIMINFO					    MDSYS.SDO_DIM_ARRAY
 SRID						    NUMBER

Obviously DIMINFO column is the problematic part since it is in SDO_DIM_ARRAY type. Now create a temporary table by unfolding this nested table at the source database site.

CREATE TABLE temp_metadata AS 
  SELECT t.table_name, 
         t.column_name, 
         d.*, 
         t.srid 
  FROM   user_sdo_geom_metadata t, 
         TABLE(t.diminfo) d; 

Once you are done check the structure of temp_metadata table which only contains primitive SQL types for the columns

SQL> desc temp_metadata
 Name					   Null?    Type
 ----------------------------------------- -------- ----------------------------
 TABLE_NAME					    VARCHAR2(32)
 COLUMN_NAME					    VARCHAR2(1024)
 SDO_DIMNAME					    VARCHAR2(64)
 SDO_LB 					    NUMBER
 SDO_UB 					    NUMBER
 SDO_TOLERANCE					    NUMBER
 SRID						    NUMBER

Choose your favorite tool to move this table to the target database since it contains just a few rows. Then as the final step import this data into USER_SDO_GEOM_METADATA table at the target database (Ensure that there is no other record with the same table_name.column_name key on the target USER_SDO_GEOM_METADATA table otherwise trigger on it will warn you)

insert into user_sdo_geom_metadata
SELECT m.table_name, m.column_name,
   CAST(MULTISET(SELECT sdo_dimname,sdo_lb,SDO_UB,sdo_tolerance
                 FROM temp_metadata n
                 WHERE n.table_name = m.table_name and n.column_name = m.column_name)
   AS SDO_DIM_ARRAY) as diminfo,
   m.srid
FROM (select distinct table_name,column_name,srid from temp_metadata) m;
commit;

You are done! Check everything works fine at the application site.

Advertisements

Upgrade to Oracle Enterprise Linux 5.9 and/so UEK2

I have upgraded one of my Oracle Linux boxes to Oracle Linux Release 5.9 which introduces several new things (Release Notes). Just to mention three of them

Download and update yum repository file

[root@localhost yum.repos.d]$ cd /etc/yum.repos.d
[root@localhost yum.repos.d]$ wget http://public-yum.oracle.com/public-yum-el5.repo
[root@localhost yum.repos.d]$ mv public-yum-el5.repo.1 public-yum-el5.repo
[root@localhost yum.repos.d]$ grep -B 5 enabled=1 public-yum-el5.repo
[el5_latest]
name=Oracle Linux $releasever Latest ($basearch)
baseurl=http://public-yum.oracle.com/repo/OracleLinux/OL5/latest/$basearch/
gpgkey=http://public-yum.oracle.com/RPM-GPG-KEY-oracle-el5
gpgcheck=1
enabled=1
--
[ol5_u9_base]
name=Oracle Linux $releasever Update 9 installation media copy ($basearch)
baseurl=http://public-yum.oracle.com/repo/OracleLinux/OL5/9/base/$basearch/
gpgkey=http://public-yum.oracle.com/RPM-GPG-KEY-oracle-el5
gpgcheck=1
enabled=1
--
[ol5_UEK_latest]
name=Latest Unbreakable Enterprise Kernel for Oracle Linux $releasever ($basearch)
baseurl=http://public-yum.oracle.com/repo/OracleLinux/OL5/UEK/latest/$basearch/
gpgkey=http://public-yum.oracle.com/RPM-GPG-KEY-oracle-el5
gpgcheck=1
enabled=1

We are ready to upgrade

[root@localhost yum.repos.d]$ yum update

Rebounce Linux

[root@localhost yum.repos.d]$ reboot

Before

Check Oracle Linux Version

[oracle@localhost ~]$ cat /etc/oracle-release
Oracle Linux Server release 5.8

Check kernel version

[oracle@localhost ~]$ uname -a
Linux localhost.localdomain 2.6.32-300.32.2.el5uek #1 SMP Tue Aug 28 10:15:29 EDT 2012 x86_64 x86_64 x86_64 GNU/Linux

Check your memory page size

[oracle@localhost ~]$ getconf PAGESIZE
4096

After

Once you are done after reboot, check Oracle Linux Version

[oracle@localhost ~]$ cat /etc/oracle-release
Oracle Linux Server release 5.9

Check kernel version

[oracle@localhost ~]$ uname -a
Linux localhost.localdomain 2.6.39-300.28.1.el5uek #1 SMP Tue Feb 5 10:15:29 PST 2013 x86_64 x86_64 x86_64 GNU/Linux

Check your memory page size

[oracle@localhost ~]$ getconf PAGESIZE
4096

Ooops!!! No change ?

[root@localhost ~]$ cat /sys/kernel/mm/transparent_hugepage/enabled 
[always] madvise never

Actually transparent huge page allocation is enabled but it deserves a separate discussion

How to Disable Automatic SGA Management on 11g Release 2

Login database using

sqlplus / as sysdba

Dump the pfile using existing spfile

SQL> create pfile='/home/oracle/Desktop/init.ora' from spfile;

File created.

Oracle will generate implicit parameters defining current sizing of the memory regions in SGA and write them at the top of generated pfile

orcl.__db_cache_size=79691776
orcl.__java_pool_size=4194304
orcl.__large_pool_size=4194304
orcl.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment
orcl.__pga_aggregate_target=142606336
orcl.__sga_target=272629760
orcl.__shared_io_pool_size=0
orcl.__shared_pool_size=167772160
orcl.__streams_pool_size=4194304
.
.
.
*.memory_target=414187520
.
.
.

Edit the file to disable AMM and enable static SGA configuration by simply removing memory_target, oracle_base and shared_io_pool_size parameters and doing some make up

*.db_cache_size=79691776
*.java_pool_size=4194304
*.large_pool_size=4194304
*.pga_aggregate_target=142606336
*.sga_target=272629760
*.shared_pool_size=167772160
*.streams_pool_size=4194304
.
.
.

Rebounce database instance by using modified pfile.

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> startup pfile='/home/oracle/Desktop/init.ora';
ORACLE instance started.

Total System Global Area  271400960 bytes
Fixed Size                  2227544 bytes
Variable Size             180355752 bytes
Database Buffers           83886080 bytes
Redo Buffers                4931584 bytes
Database mounted.
Database opened.

Once you ensure that new pfile works, recreate new spfile using this pfile (refer to How to move ASM spfile to a different disk group [ID 1082943.1] if your target is ASM diskgroup)

SQL> create spfile using pfile='/home/oracle/Desktop/init.ora';

File created.

Finally rebounce the database by using new spfile

SQL> startup force;
ORACLE instance started.

Total System Global Area  271400960 bytes
Fixed Size                  2227544 bytes
Variable Size             180355752 bytes
Database Buffers           83886080 bytes
Redo Buffers                4931584 bytes
Database mounted.
Database opened.

Oracle Day 2012 Presentation

We are back from OOW 2012 and now it is time to broadcast what we learn in San Francisco. So as a TROUG Founder I will be presenting my Open World Session in Turkey this time. Join me if you wish to hear more about data mining, optimisation,etc. this time in Turkish.

Here are session details:

  • What: Veri Madenciligi Veritabaninda Yapilir: Uygulamalariyla Oracle R Enterprise ve Oracle Data Mining Opsiyonu (Database Data Mining: Practical Enterprise R and Oracle Advanced Analytics)
  • When: 15th of November 2012 14:20 – 14:50
  • Where: Istanbul Kongre Merkezi, Taskisla Caddesi Harbiye 34367, Istanbul/Turkiye

Join me and learn that BI is not about filtering tables, rotating tables, combining and aggregating tables without knowing the actual reason of doing them 🙂

By the way here are other TROUG Sessions in Oracle Day 2012

Open World 2012 Session: Slides are Available Now !!!

Here is my presentation.
Enjoy it !!!

Open World 2012 Session

The time came and it is Oracle Open World time once again. I fly San Francisco next week to join the largest technology event in the world. This will be the Complex Event Processing, Advanced Analytics and Data Warehousing year for me in Open World.

After spending a non-speaker year (2011), this year I will be presenting on Oracle Enterprise R and ODM by going over several use cases. Here are session details:

  • What: Database Data Mining: Practical Enterprise R and Oracle Advanced Analytics
  • When: 1st of October 2012 16:45 – 17:45
  • Where: Moscone West – 3016

Join me and learn that BI is not about filtering tables, rotating tables, combining and aggregating tables without knowing the actual reason of doing them 🙂

Book Review: Oracle Warehouse Builder 11gR2: Getting Started 2011

As being the embedded option of Oracle relational database engine, OWB is still my favourite ETL (or to correct ELT) tool. In addition, although Oracle positions ODI as their strategic tool for this purpose, they still keep investing on OWB  at least in 11g Release 2.

I have recently had the chance to review a new book by Packt Publishing, namely Oracle Warehouse Builder 11gR2: Getting Started 2011. Book is  a good introductory book for newbies willingness to learn more on OWB by playing with it in a playground.

Book is organised to let you build your own OWB environment and work on a toy problem namely ACME Toy and Gizmos. You design and create your source and target structures and implement you ELT.

The book will help those who wish to learn more on OWB and look for a practical guide.

Book Review: Oracle GoldenGate 11g Implementer’s Guide

goldengate

As being a consultant in VLDB domain, one of the most popular questions of today is “How can I feed my data warehouse/reporting environment in real-time?” Yet another one is “How can I offload my reporting activity over my OTP environment without generating any time gap between reality and what is being reported?” In addition, I am a bit depressed to see people taking CDC (Change Data Capture) products as DR (Disaster Recovery) solutions.

It was two years ago in ACED briefing when Thomas Kurain declared that GoldenGate is Oracle’s strategic real-time integration solution. After that, I have spent quite a few time to understand merits and drawbacks of this product. I have talked customers want to use it, already using it, and suffering it. Almost all sites annoyed with the same problems

  • It is very hard to configure GoldenGate
  • It is very hard to monitor&manage GoldenGate
  • Oracle documentation is still not sufficient for them.

To be honest it is hard to say that they are wrong.

Last week I have read John P. Jeffries’s Oracle GoldenGate 11g Implementer’s Guide and I can easily say that it is a nice piece of material built just to make reader a successful GoldenGate implementer. There is no dictionary-based definition of GoldenGate concepts like Extract, Trail File, Data Pump, etc. as it is in Oracle formal documentations. The book is structured in “Let me show what I define above” fashion. The book is full of details to show you the way of implementing up and running GoldenGate systems. However, I will continue to write on my favorite sections.

Chapter 6: Configuring Golden Gate for HA is on how to configure GoldenGate on a RAC database. Chapter covers to integrate GoldenGate with clusterware software to enable automatic failover. I have seen customer sites still writing custom scripts for this. Therefore, this chapter is a good how to for RAC implementers.

Chapter 8: Managing Oracle GoldenGate is a chapter mainly about reporting performance metrics and errors of functional GoldenGate systems. Chapter explains to report your GoldenGate errors, latency, and throughput and interpret them.

Chapter 9: Performance Tuning is on tuning GoldenGate using parallel extracts and replicates and tuning GoldenGate storage and network for best performance.

To sum up, if you are a newbie in GoldenGate but responsible with implementing a new GoldenGate environment or maintaining an existing one, this book might be a good resource for you.

An English Man in Istanbul

A gentle man… An Oracle expert … An esteemed community figure…
Yes. As the reader of this Oracle focused blog, you should figure out about whom I am talking. Last week (on Thursday), we have achieved to set first Turkey Oracle User Group (TROUG) Day at Bahcesehir University.

We have just discussed on the keynote speaker as the Turkish community members and agreed on one name: Jonathan Lewis
The problem was that as being such a popular figure in community, it was almost impossible to find a proper gap for our event in Jonathan’s schedule. As I have sent the invitation to him and asked for his attendance on one Sunday afternoon, Jonathan just replied that he would be with us and perform the keynote speech. Almost a month after our mailing, Jonathan was on stage at Bahcesehir University auditorium as our keynote speaker talking “just about joining two tables”.
It was amazing that we have finally achieved with this user group day and the man whose books are my Oracle library’s masterpiece was on stage.
Thank you Jonathan personally and as the founding member of TROUG for honoring us and hope to see you soon again…

My Open World 2010 Session

Hi there,

You can find my joint Solidify RMAN Backup with Oracle Open Storage 7000 presentation with my esteemed friend Orhan Bıyıklıoglu held yesterday in Open World 2010.