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.
As you all may know, ASMLib is a recommended module for Oracle databases running on Linux platforms and it is an embedded module for UEK (Oracle Unbreakable Kernel) users. Oracle announced that (two years ago or so) they will no longer maintain ASMLib for Red Hat compatible kernel but this does not mean that they abandon the project, rather it is mainly related to the effort they need to put in maintaining the module for multiple kernel versions.
A while ago, Oracle added a new feature to ASMLib allowing ASM devices to choose between physical or logical block sizes in I/O operations. This should be, I believe, a fail-back mechanism for SSD devices used as ASM disks. Many SSD devices use 4K block size however Linux still uses default 512 bytes logical block size for those devices in the same way it does for motor disks. In early releases of ASMLib (
oracleasm-support-2.1.7 and earlier), ASM uses physical block size (4K for SSDs). This is still the default behavior in
oracleasm-support-2.1.8 but now sysadmin can choose between physical and logical blocksize by using
[-b|-p] switches in
- -b|—logical-blocks sets logical blocksize usage
- -p|—physical-blocks set physical blocksize usage
Those switches set
/sys/module/oracleasm/parameters/use_logical_block_size which is default to be
false (use physical blocksize). So that
asmlib module can use the value to decide which block size to use.
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
- firefox 10 (Although we have discuss to install latest version of it in Oracle Enterprise Linux for Home User: Install Latest Firefox Version )
- uek2 vanilla kernel branched from Linux Kernel Release 3.0.36
- Transparent Huge Pages
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
[root@localhost yum.repos.d]$ reboot
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
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
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
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.
I have announced my webinar on Oracle ZFS Storage Appliance for Exadata Backup & Recovery in NYOUG DBA webinar series. You can download webinar slides and voice recod from NYOUG web site
It is always the case that in my Linux VM installations I run out of root file system space and need to grow it (I can hear you experienced admin, I should create another filesystem for my use and not use root file system for my joy). Here are the steps to grow root filesystem for Oracle Linux running on VMware:
Click on the wrench symbol to go to your virtual machine configuration menu
Choose Hard Disk (SCSI) menu to increase the size of an availabe virtual disk (in this case your root file system disk)
Now you can start your Oracle Linux on VM*.
First step at guest OS (Oracle Linux) is to grow disk partition size using fdisk. Here are the steps assuming that your root file system is on /dev/sda2 partition
- fdisk /dev/sda
- d (to delete)
- 2 (to delete partition 2)
- n (to create a new partition)
- p (to create a new partition with primary type)
- 2 (to create a new primary partition with id 2)
- Accept all defaults (to create a new primary partition with default offset and fully covering virtual disk you have already provisioned)
- w (write partition table)
Once you are done with disk partition modification rebounce Oracle Linux to ensure partition tables are read by Linux.
Once the guest OS is back, resize physical volume using pvresize. We will add 6GB (slightly less maybe) to existing physical partition using pvresize command
pvresize --setphysicalvolumesize 31G /dev/sda2
Now we can grow logical volume by 6GB
lvresize -L +6G /dev/VolGroup00/LogVol00
Final step is to grow file system online using resize2fs
You are done. You can continue filling root file system 🙂
* Before starting it is a good decision to protect your existing VM guest by using VMware snapshots.
In Friday December 14, 2012 at 12:00 PM -1:00 PM EDT I will be giving a webinar for NYOUG SIG with the following abstract
When it comes to the backup and recovery infrastructure of the Exadata Database Machine, conventional solutions often have only limited performance to keep up with Exadata throughput, whereas Oracle ZFS Storage Appliance can be configured as a very fast, capable, and easy-to-manage backup and recovery solution for any Exadata environment. In this session Husnu Sensoy will describe some of the configuration possibilities of the ZFS Storage Appliance to create a flexible backup and recovery environment for Exadata, as well as various tuning do’s and dont’s to maximize your backup and recovery performance.
Join me to hear more about ZFS Storage Appliance and Exadata.
It is a joy to use Linux for any purpose. Although Oracle Linux is mainly designed for enterprise stack, a stable operating system for a technical user is always a need.
However default configurations and program versions are not suitable for daily use always. One example of this is Firefox 3.0.6 available in Oracle Linux (or Red Hat). Here is how you can upgrade it to 17.0
Download latest Firefox release from Mozilla web page. Then unpack and install it by simply following.
tar -xjvf firefox-17.0.tar.bz2 cp -r firefox /opt/ ln -sf /opt/firefox/firefox /usr/bin/firefox
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
Before starting to deal with large volumes of data problems on Oracle R Enterprise (ORE) you need to perform a couple of configurations over your Oracle Linux and Oracle Database systems. Here is the recipe:
- Ensure that you have the following lines in your oracle users .bash_profile file
export R_HOME=/usr/lib64/R export PATH=/usr/bin:$PATH
- Ensure that you have already installed libpng.x86_64 and libpng-devel.x86_64 packages on your Oracle Linux otherwise issue to install them.
yum install libpng.x86_64 libpng-devel.x86_64
- Switch to root and issue R. Once you are in R session, install two prerequisites of ORE:
- Ensure that your database is 126.96.36.199 otherwise refer you need to apply several database patches:
- Go to Oracle R Enterprise Download Page and download Oracle R Enterprise Server Install for Oracle Database on Linux 64-bit (91M) and Oracle R Enterprise Client Supporting Packages for Linux 64-bit Platform (1M) (ore-server-linux-x86-64-1.1.zip and ore-supporting-linux-x86-64-1.1.zip) under Oracle R Enterprise Downloads (v1.1) section
- Unzip the file by issuing
unzip ore-server-linux-x86-64-1.1.zip ore-supporting-linux-x86-64-1.1.zip
- At this point ensure that your database to support Oracle R Enterprise is up and running
install.shin order to create ORE libraries and database objects into SYS and RQSYS schemas.
cd server ./install.sh
Oracle R Enterprise 1.1 Server Installation.
Copyright (c) 2012, Oracle and/or its affiliates. All rights reserved.
Do you wish to proceed? [yes]
Checking R ................... Pass
Checking R libraries ......... Pass
Checking ORACLE_HOME ......... Pass
Checking ORACLE_SID .......... Pass
Checking sqlplus ............. Pass
Checking ORE ................. Pass
Choosing RQSYS tablespaces
PERMANENT tablespace to use for RQSYS [SYSAUX]:
TEMPORARY tablespace to use for RQSYS [TEMP]:
R_HOME = /usr/lib64/R
R_LIBS_USER = /u01/app/oracle/product/11.2.0/dbhome_1/R/library
ORACLE_HOME = /u01/app/oracle/product/11.2.0/dbhome_1
ORACLE_SID = orcl
PERMANENT tablespace = SYSAUX
TEMPORARY tablespace = TEMP
Installing libraries ......... Pass
Installing RQSYS ............. Pass
Installing ORE packages ...... Pass
Creating ORE script .......... Pass
NOTE: To use ORE functionality, a database user with RQROLE role,
a few more grants and synonyms is required. A complete list of
requirements is available in rquser.sql. There is also a demo
script demo_user.sh creating a new user RQUSER.
To use embedded R functionality, an RQADMIN role is required.
Please, consult the documentation for more information on various
- Finally install some required R libraries/packages by using
install.packagescommand in R. Ensure that user (
rootwill do that) you will start R has a write permission on
install.packages("/home/oracle/Desktop/server/ORE_1.1_R_x86_64-unknown-linux-gnu.tar.gz", repos = NULL) install.packages("/home/oracle/Desktop/server/OREbase_1.1_R_x86_64-unknown-linux-gnu.tar.gz", repos = NULL) install.packages("/home/oracle/Desktop/server/OREeda_1.1_R_x86_64-unknown-linux-gnu.tar.gz", repos = NULL) install.packages("/home/oracle/Desktop/server/OREgraphics_1.1_R_x86_64-unknown-linux-gnu.tar.gz", repos = NULL) install.packages("/home/oracle/Desktop/server/OREstats_1.1_R_x86_64-unknown-linux-gnu.tar.gz", repos = NULL) install.packages("/home/oracle/Desktop/server/ORExml_1.1_R_x86_64-unknown-linux-gnu.tar.gz", repos = NULL) install.packages("/home/oracle/Desktop/supporting/ROracle_1.1-2_R_x86_64-unknown-linux-gnu.tar.gz", repos = NULL)
- Finally start a R session (ensure that
$ORACLE_HOME/libis in your
LD_LIBRARY_PATHbefore starting R session) and load ORE library
library(ORE) Loading required package: OREbase Loading required package: ROracle Loading required package: DBI Attaching package: 'OREbase' The following object(s) are masked from 'package:base': cbind, data.frame, eval, interaction, order, paste, pmax, pmin, rbind, table Loading required package: OREstats Loading required package: MASS Loading required package: OREgraphics Loading required package: OREeda Loading required package: ORExml