DBA Blogs

AWS EC2 API tools: Create snapshot & Check Data in snapshot

Surachart Opun - Wed, 2015-05-27 02:38
After installed AWS EC2 API tools,  It's time for example create/delete snapshot. 
- Creating snapshot.
ubuntu@ip-x-x-x-x~$ ec2-describe-volumes
VOLUME  vol-41885f55    8       snap-d00ac9e4   ap-southeast-1a in-use  2015-05-26T09:07:04+0000        gp2     24
ATTACHMENT      vol-41885f55    i-d6cdb71a      /dev/sda1       attached        2015-05-26T09:07:04+0000        true
ubuntu@ip-x-x-x-x:~$ ec2-create-snapshot  -d vol-41885f55-$(date +%Y%m%d%H%M) vol-41885f55
SNAPSHOT        snap-b20a8c87   vol-41885f55    pending 2015-05-27T05:46:58+0000                843870022970    8       vol-41885f55-201505270546
ubuntu@ip-x-x-x-x:~$ ec2-describe-snapshots
SNAPSHOT        snap-b20a8c87   vol-41885f55    pending 2015-05-27T05:46:58+0000        0%      843870022970    8       vol-41885f55-201505270546
ubuntu@ip-x-x-x-x:~$ ec2-create-snapshot  -d vol-41885f55-$(date +%Y%m%d%H%M) vol-41885f55
SNAPSHOT        snap-bea0d28b   vol-41885f55    pending 2015-05-27T05:50:11+0000                843870022970    8       vol-41885f55-201505270550
ubuntu@ip-x-x-x-x:~$ ec2-describe-snapshots
SNAPSHOT        snap-b20a8c87   vol-41885f55    completed       2015-05-27T05:46:58+0000        100%    843870022970    8       vol-41885f55-201505270546
SNAPSHOT        snap-bea0d28b   vol-41885f55    completed       2015-05-27T05:50:11+0000        100%    843870022970    8       vol-41885f55-201505270550- Deleting snapshot (delete snap-b20a8c87). 
ubuntu@ip-x-x-x-x:~$ ec2-describe-snapshots  |head -1| awk '{print $2}'|xargs ec2-delete-snapshot
SNAPSHOT        snap-b20a8c87
ubuntu@ip-x-x-x-x:~$ ec2-describe-snapshots
SNAPSHOT        snap-bea0d28b   vol-41885f55    completed       2015-05-27T05:50:11+0000        100%    843870022970    8       vol-41885f55-201505270550How to check data in "snap-bea0d28b"? Checking idea on AWS, look like we must create Volume from snapshot and attach it to Instance.
- Creating Volume > Attach to Instance and Mount.
ubuntu@ip-x-x-x-x:~$ ec2-describe-volumes
VOLUME  vol-41885f55    8       snap-d00ac9e4   ap-southeast-1a in-use  2015-05-26T09:07:04+0000        gp2     24
ATTACHMENT      vol-41885f55    i-d6cdb71a      /dev/sda1       attached        2015-05-26T09:07:04+0000        true
ubuntu@ip-x-x-x-x:~$ ec2-describe-availability-zones
AVAILABILITYZONE        ap-southeast-1a available       ap-southeast-1
AVAILABILITYZONE        ap-southeast-1b available       ap-southeast-1
ubuntu@ip-x-x-x-x:~$ ec2-create-volume -s 8 --snapshot snap-bea0d28b -z ap-southeast-1a
VOLUME  vol-d15087c5    8       snap-bea0d28b   ap-southeast-1a creating        2015-05-27T06:24:00+0000        standard
ubuntu@ip-x-x-x-x:~$ ec2-describe-volumes
VOLUME  vol-41885f55    8       snap-d00ac9e4   ap-southeast-1a in-use  2015-05-26T09:07:04+0000        gp2     24
ATTACHMENT      vol-41885f55    i-d6cdb71a      /dev/sda1       attached        2015-05-26T09:07:04+0000        true
VOLUME  vol-d15087c5    8       snap-bea0d28b   ap-southeast-1a available       2015-05-27T06:24:00+0000        standard
ubuntu@ip-x-x-x-x:~$ sudo fdisk -l
Disk /dev/xvda: 8589 MB, 8589934592 bytes
255 heads, 63 sectors/track, 1044 cylinders, total 16777216 sectors
Units = sectors of 1 * 512 = 512 bytes
Sector size (logical/physical): 512 bytes / 512 bytes
I/O size (minimum/optimal): 512 bytes / 512 bytes
Disk identifier: 0x00000000
    Device Boot      Start         End      Blocks   Id  System
/dev/xvda1   *       16065    16771859     8377897+  83  Linux
ubuntu@ip-x-x-x-x:~$ ec2-attach-volume vol-d15087c5 -i  i-d6cdb71a  -d sdf
ATTACHMENT      vol-d15087c5    i-d6cdb71a      sdf     attaching       2015-05-27T06:31:16+0000
ubuntu@ip-x-x-x-x:~$ sudo fdisk -l
Disk /dev/xvda: 8589 MB, 8589934592 bytes
255 heads, 63 sectors/track, 1044 cylinders, total 16777216 sectors
Units = sectors of 1 * 512 = 512 bytes
Sector size (logical/physical): 512 bytes / 512 bytes
I/O size (minimum/optimal): 512 bytes / 512 bytes
Disk identifier: 0x00000000
    Device Boot      Start         End      Blocks   Id  System
/dev/xvda1   *       16065    16771859     8377897+  83  Linux
Disk /dev/xvdf: 8589 MB, 8589934592 bytes
255 heads, 63 sectors/track, 1044 cylinders, total 16777216 sectors
Units = sectors of 1 * 512 = 512 bytes
Sector size (logical/physical): 512 bytes / 512 bytes
I/O size (minimum/optimal): 512 bytes / 512 bytes
Disk identifier: 0x00000000
    Device Boot      Start         End      Blocks   Id  System
/dev/xvdf1   *       16065    16771859     8377897+  83  Linux
ubuntu@ip-x-x-x-x:~$

ubuntu@ip-x-x-x-x:~$ df
Filesystem     1K-blocks    Used Available Use% Mounted on
/dev/xvda1       8115168 1212140   6467752  16% /
none                   4       0         4   0% /sys/fs/cgroup
udev              503188      12    503176   1% /dev
tmpfs             101632     332    101300   1% /run
none                5120       0      5120   0% /run/lock
none              508144       0    508144   0% /run/shm
none              102400       0    102400   0% /run/user
ubuntu@ip-x-x-x-x:~$ sudo mount /dev/xvdf1 /mnt/
ubuntu@ip-x-x-x-x:~$ ls -l /mnt/
total 92
drwxr-xr-x   2 root root  4096 May 26 09:35 bin
drwxr-xr-x   3 root root  4096 Mar 25 11:52 boot
drwxr-xr-x   5 root root  4096 Mar 25 11:53 dev
drwxr-xr-x 105 root root  4096 May 26 09:35 etc
drwxr-xr-x   3 root root  4096 May 26 09:07 home
lrwxrwxrwx   1 root root    33 Mar 25 11:51 initrd.img -> boot/initrd.img-3.13.0-48-generic
drwxr-xr-x  21 root root  4096 May 26 09:35 lib
drwxr-xr-x   2 root root  4096 Mar 25 11:50 lib64
drwx------   2 root root 16384 Mar 25 11:53 lost+found
drwxr-xr-x   2 root root  4096 Mar 25 11:50 media
drwxr-xr-x   2 root root  4096 Apr 10  2014 mnt
drwxr-xr-x   2 root root  4096 Mar 25 11:50 opt
drwxr-xr-x   2 root root  4096 Apr 10  2014 proc
drwx------   3 root root  4096 May 26 09:07 root
drwxr-xr-x   3 root root  4096 Mar 25 11:53 run
drwxr-xr-x   2 root root  4096 May 26 09:35 sbin
drwxr-xr-x   2 root root  4096 Mar 25 11:50 srv
drwxr-xr-x   2 root root  4096 Mar 13  2014 sys
drwxrwxrwt   6 root root  4096 May 27 05:38 tmp
drwxr-xr-x  10 root root  4096 Mar 25 11:50 usr
drwxr-xr-x  12 root root  4096 Mar 25 11:52 var
lrwxrwxrwx   1 root root    30 Mar 25 11:51 vmlinuz -> boot/vmlinuz-3.13.0-48-generic
ubuntu@ip-x-x-x-x:~$ ls /mnt/
bin  boot  dev  etc  home  initrd.img  lib  lib64  lost+found  media  mnt  opt  proc  root  run  sbin  srv  sys  tmp  usr  var  vmlinuz
ubuntu@ip-x-x-x-x:~$ df -h
Filesystem      Size  Used Avail Use% Mounted on
/dev/xvda1      7.8G  1.2G  6.2G  16% /
none            4.0K     0  4.0K   0% /sys/fs/cgroup
udev            492M   12K  492M   1% /dev
tmpfs           100M  332K   99M   1% /run
none            5.0M     0  5.0M   0% /run/lock
none            497M     0  497M   0% /run/shm
none            100M     0  100M   0% /run/user
/dev/xvdf1      7.8G  1.2G  6.2G  16% /mnt- After checking data, We can unmount and remove it.
ubuntu@ip-x-x-x-x:~$ sudo umount /mnt
ubuntu@ip-x-x-x-x:~$
ubuntu@ip-x-x-x-x:~$
ubuntu@ip-x-x-x-x:~$ df
Filesystem     1K-blocks    Used Available Use% Mounted on
/dev/xvda1       8115168 1212140   6467752  16% /
none                   4       0         4   0% /sys/fs/cgroup
udev              503188      12    503176   1% /dev
tmpfs             101632     332    101300   1% /run
none                5120       0      5120   0% /run/lock
none              508144       0    508144   0% /run/shm
none              102400       0    102400   0% /run/user

ubuntu@ip-x-x-x-x:~$ sudo fdisk -l
Disk /dev/xvda: 8589 MB, 8589934592 bytes
255 heads, 63 sectors/track, 1044 cylinders, total 16777216 sectors
Units = sectors of 1 * 512 = 512 bytes
Sector size (logical/physical): 512 bytes / 512 bytes
I/O size (minimum/optimal): 512 bytes / 512 bytes
Disk identifier: 0x00000000
    Device Boot      Start         End      Blocks   Id  System
/dev/xvda1   *       16065    16771859     8377897+  83  Linux
Disk /dev/xvdf: 8589 MB, 8589934592 bytes
255 heads, 63 sectors/track, 1044 cylinders, total 16777216 sectors
Units = sectors of 1 * 512 = 512 bytes
Sector size (logical/physical): 512 bytes / 512 bytes
I/O size (minimum/optimal): 512 bytes / 512 bytes
Disk identifier: 0x00000000
    Device Boot      Start         End      Blocks   Id  System
/dev/xvdf1   *       16065    16771859     8377897+  83  Linux
ubuntu@ip-x-x-x-x:~$
ubuntu@ip-x-x-x-x:~$ ec2-describe-volumes
VOLUME  vol-41885f55    8       snap-d00ac9e4   ap-southeast-1a in-use  2015-05-26T09:07:04+0000        gp2     24
ATTACHMENT      vol-41885f55    i-d6cdb71a      /dev/sda1       attached        2015-05-26T09:07:04+0000        true
VOLUME  vol-d15087c5    8       snap-bea0d28b   ap-southeast-1a in-use  2015-05-27T06:24:00+0000        standard
ATTACHMENT      vol-d15087c5    i-d6cdb71a      sdf     attached        2015-05-27T06:31:16+0000        false

ubuntu@ip-x-x-x-x:~$ ec2-detach-volume vol-d15087c5 -i  i-d6cdb71a
ATTACHMENT      vol-d15087c5    i-d6cdb71a      sdf     detaching       2015-05-27T06:31:16+0000
ubuntu@ip-x-x-x-x:~$ ec2-describe-volumes
VOLUME  vol-41885f55    8       snap-d00ac9e4   ap-southeast-1a in-use  2015-05-26T09:07:04+0000        gp2     24
ATTACHMENT      vol-41885f55    i-d6cdb71a      /dev/sda1       attached        2015-05-26T09:07:04+0000        true
VOLUME  vol-d15087c5    8       snap-bea0d28b   ap-southeast-1a in-use  2015-05-27T06:24:00+0000        standard
ATTACHMENT      vol-d15087c5    i-d6cdb71a      sdf     detaching       2015-05-27T06:31:16+0000        false
ubuntu@ip-x-x-x-x:~$ ec2-describe-volumes
VOLUME  vol-41885f55    8       snap-d00ac9e4   ap-southeast-1a in-use  2015-05-26T09:07:04+0000        gp2     24
ATTACHMENT      vol-41885f55    i-d6cdb71a      /dev/sda1       attached        2015-05-26T09:07:04+0000        true
VOLUME  vol-d15087c5    8       snap-bea0d28b   ap-southeast-1a available       2015-05-27T06:24:00+0000        standard
ubuntu@ip-x-x-x-x:~$ ec2-delete-volume vol-d15087c5
VOLUME  vol-d15087c5
ubuntu@ip-x-x-x-x:~$ ec2-describe-volumes
VOLUME  vol-41885f55    8       snap-d00ac9e4   ap-southeast-1a in-use  2015-05-26T09:07:04+0000        gp2     24
ATTACHMENT      vol-41885f55    i-d6cdb71a      /dev/sda1       attached        2015-05-26T09:07:04+0000        trueLook like it's easy to use and adapt with script.
Categories: DBA Blogs

AWS EC2 API tools: Installation

Surachart Opun - Wed, 2015-05-27 02:08
AWS EC2 API tools help too much for Amazon EC2 to register and launch instances, manipulate security groups, and more. Someone asked me to backup EC2 instance. I thought to use it for backup script. Anyway, No need to explain more how to install Amazon EC2 API tools on Ubuntu? Just say thank for  EC2StartersGuide. I fellow this link and installed it easily. Additional, I used this Link for more idea about java.
- Adding Repository and Install EC2 API tools.
ubuntu@ip-x-x-x-x:~$ sudo apt-add-repository ppa:awstools-dev/awstools
 Up to date versions of several tools from AWS.
 Use this repository by:
 sudo apt-add-repository ppa:awstools-dev/awstools
 sudo apt-get update
 sudo apt-get install ec2-api-tools
.
.
.
ubuntu@ip-x-x-x-x:~$ sudo apt-get update
ubuntu@ip-x-x-x-x:~$ sudo apt-get install ec2-api-tools
ubuntu@ip-x-x-x-x:~$ sudo apt-get install -y openjdk-7-jre
ubuntu@ip-x-x-x-x:~$ file $(which java)
/usr/bin/java: symbolic link to `/etc/alternatives/java'
ubuntu@ip-x-x-x-x:~$ file /etc/alternatives/java
/etc/alternatives/java: symbolic link to `/usr/lib/jvm/java-7-openjdk-amd64/jre/bin/java'- Adding variables in ~/.bashrs file. We should have "Access Key" - Security Credentials.
ubuntu@ip-x-x-x-x:~$ vi ~/.bashrc
.
.
.
export EC2_KEYPAIR=***
export EC2_URL=https://ec2.ap-southeast-1.amazonaws.com
export EC2_PRIVATE_KEY=$HOME/.ec2/pk-***.pem
export EC2_CERT=$HOME/.ec2/cert-***.pem
export JAVA_HOME=/usr/lib/jvm/java-7-openjdk-amd64/jre

ubuntu@ip-x-x-x-x:~$ source ~/.bashrc- If everything's all right. Time to use it.
ubuntu@ip-x-x-x-x:~$ ec2-describe-regions
REGION  eu-central-1    ec2.eu-central-1.amazonaws.com
REGION  sa-east-1       ec2.sa-east-1.amazonaws.com
REGION  ap-northeast-1  ec2.ap-northeast-1.amazonaws.com
REGION  eu-west-1       ec2.eu-west-1.amazonaws.com
REGION  us-east-1       ec2.us-east-1.amazonaws.com
REGION  us-west-1       ec2.us-west-1.amazonaws.com
REGION  us-west-2       ec2.us-west-2.amazonaws.com
REGION  ap-southeast-2  ec2.ap-southeast-2.amazonaws.com
REGION  ap-southeast-1  ec2.ap-southeast-1.amazonaws.comubuntu@ip-x-x-x-x:~$ ec2-describe-availability-zones
AVAILABILITYZONE        ap-southeast-1a available       ap-southeast-1
AVAILABILITYZONE        ap-southeast-1b available       ap-southeast-1
Categories: DBA Blogs

Recover Oracle Undo Tablespace without Backup

Pakistan's First Oracle Blog - Sun, 2015-05-24 21:10
Woke up with an issue regarding a Oracle 10.2.0 database on Linux complaining about an Undo file on startup.


sqlplus '/ as sysdba'

SQL*Plus: Release 10.2.0.3.0 - Production on Fri May 22 20:11:07 2015

Copyright (c) 1982, 2006, Oracle.  All Rights Reserved.

Connected to an idle instance.

SQL> startup pfile='init.ora'
ORACLE instance started.

Total System Global Area 2801795072 bytes
Fixed Size                  2075504 bytes
Variable Size            1275069584 bytes
Database Buffers         1509949440 bytes
Redo Buffers               14700544 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 244 - see DBWR trace file
ORA-01110: data file 244: '/test/ORADATATEST/test/test_undo2a.dbf'


SQL> show parameter undo

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
undo_management                      string      MANUAL
undo_retention                       integer     21600
undo_tablespace                      string      test_UNDO02
SQL>



SQL> drop tablespace test_UNDO02  including contents and datafiles;
drop tablespace test_UNDO02  including contents and datafiles
*
ERROR at line 1:
ORA-01548: active rollback segment '_SYSSMU4$' found, terminate dropping tablespace

 Check for active rollback segments:

 select segment_id, segment_name,status,tablespace_name from dba_rollback_segs where status not in ('ONLINE','OFFLINE');



Set the following parameter in the pfile.



*._offline_rollback_segments=(_SYSSMU4$)


And now try dropping UNDO tablespace again.

drop tablespace test_UNDO02  including contents and datafiles;

Tablespace dropped.

Now create a new UNDO tablespace:

create UNDO tablespace test_UNDO05 datafile '/test/oradata18/test/test_undo05_file1.dbf' size 500m autoextend on next 1m maxsize 1500m;


Tablespace created.



SQL> create UNDO tablespace test_UNDO05 datafile '/test/oradata18/test/test_undo05_file1.dbf' size 500m autoextend on next 1m maxsize 1500m;


Tablespace created.



SQL> startup pfile='inittest.ora'
ORACLE instance started.

Total System Global Area 2801795072 bytes
Fixed Size                  2075504 bytes
Variable Size            1392510096 bytes
Database Buffers         1392508928 bytes
Redo Buffers               14700544 bytes
Database mounted.
Database opened.

All good now.
Categories: DBA Blogs

ORA-01403 No Data Found Error

VitalSoftTech - Tue, 2015-05-19 20:29
What is the cause of the "ORA-01403 no data found" error? What can be done to solve this?
Categories: DBA Blogs

What is the purpose of the NOFILENAMECHECK parameter?

VitalSoftTech - Mon, 2015-05-18 17:50
I am duplicating my database onto a remote host. In the command, the NOFILENAMECHECK parameter is used. What is its purpose?
Categories: DBA Blogs

RMAN-06563: control file or SPFILE must be restored using FROM AUTOBACKUP

VitalSoftTech - Tue, 2015-05-05 09:54
I am trying to restore the controlfile but and getting the RMAN-06563 error. I know I have a backup on the controlfile but RMAN is not able to see it.
Categories: DBA Blogs

Need a Database Sandbox on Solaris or Linux on your Desktop? Set it up using Oracle Virtualbox in 10 mins

VitalSoftTech - Sat, 2015-05-02 09:38
Oracle VM Virutal box runs as a virtual machine on your Windows, Mac, Linux or Solaris desktop. A number of Guest operating systems are supported. It takes only a few minutes to download the software and import pre-created templates and you are up and running in less that 10 mins. If necessary you can download […]
Categories: DBA Blogs

Solving RMAN-06059: Errror identifying file

VitalSoftTech - Mon, 2015-04-27 10:43
When using RMAN to backup archivelog files, an error “RMAN-06059: Errror identifying file ” is returned. Previously the backups have been running successfully prior to this.
Categories: DBA Blogs

GoldenGate ggsci returns “ld.so.1: ggsci: fatal: relocation error” after GoldenGate 12c upgrade

VitalSoftTech - Sun, 2015-04-12 00:20
When invoking ggsci after GoldenGate 12c upgrade why is the "ld.so.1: ggsci: fatal: relocation error" returned?
Categories: DBA Blogs

Upgrading your Grid Infrastructure to 12c? Some things to watch out for!

VitalSoftTech - Thu, 2015-04-09 00:19
Ready to upgrade your Grid Infrastructure and RAC database to 12c? Make sure to go through the following items so that your upgrade does not fail.
Categories: DBA Blogs

LPAR and Oracle Database

Pakistan's First Oracle Blog - Tue, 2015-04-07 20:30
What is LPAR?

LPAR stands for Logical Partitioning and it's a feature of IBM's operating system AIX (Also available in Linux). By abstracting all the physical devices in a system, LPAR creates a virtualized computing environment.

In a server; the processor, memory, and storage are divided into multiple sets. Each set in a server consist of resources like processor, memory and storage. Each set is called as LPAR.

One server can have many LPARs operating at the same time. These LPARs communicate with each other as if they are on separate machines.

What is DLPAR?

DLPAR stands for Dynamic Logical Partitioning and with DLPAR the LPARs can be configured dynamically without restart. With DLPAR, memory, CPU and storage can be moved between LPARs on the fly.

What is HMC?

HMC stands for Hardware Management Console. The Hardware Management Console (HMC) is interface which is used to manage the LPARs. Its Java based and can be used to manage many systems.

If LPAR is in shared processor mode, without the following fix, LPAR may see excessive CPu usage: 


APARs for WAITPROC IDLE LOOPING CONSUMES CPU:
IV01111 AIX 6.1 TL05 if before SP08 (fixed in SP08)
IV06197 AIX 6.1 TL06 if before SP07 (fixed in SP07)
IV10172 AIX 6.1 TL07 if before SP02 (fixed in SP02)
IV09133 AIX 7.1 TL00 if before SP05 (fixed in SP05)
IV10484 AIX 7.1 TL01 if before SP02 (fixed in SP02)

This problem can effect POWER7 systems running any level of Ax720 firmware prior to Ax720_101. But it is recommended to update to the latest available firmware. If required, AIX and Firmware fixes can be obtained from IBM Support Fix Central:
http://www-933.ibm.com/support/fixcentral/main/System+p/AIX
Categories: DBA Blogs

Adding Log Groups on Oracle DataGuard Database

VitalSoftTech - Mon, 2015-04-06 15:12
When adding Redo logs to a database which has a DataGuard associated with it, the log files have to manually be added to the destination. Below are the steps on how to add them to both the source and the DataGuard databases. Adding Log Groups on Oracle DataGuard Database On the source first determine the […]
Categories: DBA Blogs

SQLcl, a revolution for SQL*Plus users

DBA Scripts and Articles - Thu, 2015-04-02 09:34

What is SQLcl ? SQLcl is a new command line interface like SQL*PLUS coming along with SQL Developper 4.1 Early Adopter. It is a lightweight tool (only 11MB) developed by the SQL Developer team fully compatible with Windows and Unix/Linux. You don’t need to install it so it is totally portable. The tool does not need … Continue reading SQLcl, a revolution for SQL*Plus users

The post SQLcl, a revolution for SQL*Plus users appeared first on Oracle DBA Scripts and Articles (Montreal).

Categories: DBA Blogs

SQLcl, a revolution for SQL*Plus users

DBA Scripts and Articles - Thu, 2015-04-02 09:34

What is SQLcl ? SQLcl is a new command line interface like SQL*PLUS coming along with SQL Developper 4.1 Early Adopter. It is a lightweight tool (only 11MB) developed by the SQL Developer team fully compatible with Windows and Unix/Linux. You don’t need to install it so it is totally portable. The tool does not need … Continue reading SQLcl, a revolution for SQL*Plus users

The post SQLcl, a revolution for SQL*Plus users appeared first on Oracle DBA Scripts and Articles (Montreal).

Categories: DBA Blogs

Want to SPEED Up Your Database Tasks? DBMS_PARALLEL_EXECUTE to the rescue!

VitalSoftTech - Thu, 2015-03-26 05:56
Use DBMS_PARALLEL_EXECUTE to divide one huge task into multiple small tasks that can be executed at the same time. See how many ways are there to divide.
Categories: DBA Blogs

Showing Interval Partitons Code in DBMS_METADATA.GET_DDL

Pakistan's First Oracle Blog - Tue, 2015-03-17 23:32

-- If you want to display the system generated partitions as part of the CREATE TABLE DDL, then set the EXPORT parameter of the dbms_metadata to true.

-- The default behavior of "DBMS_METADATA.GET_DDL" is that it does not show Interval Partitions created by the system for interval partitioned tables and indexes.

-- In the case of Interval Partitioning, New Partitions are created automatically when corresponding row is inserted.  This newly created partition information will be displayed in "DBA_TAB_PARTITIONS" dictionary view. However when the DDL is queried using function "DBMS_METADATA.GET_DDL", then this information is not shown.


Demo:  (Following was tested on the Oracle 12c, and it should be valid for Oracle 11g too.)

-- Create table with interval partition.

CREATE TABLE mytabwithInterval
(mydate DATE,
 mynum NUMBER)
PARTITION BY RANGE (mydate) INTERVAL (NUMTOYMINTERVAL(1,'MONTH'))
 (PARTITION P_20150301  VALUES LESS THAN (TO_DATE(' 2015-03-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')));


-- Insert some data to generate interval partitions.

INSERT INTO mytabwithInterval VALUES (TO_DATE('2015-01-01', 'YYYY-MM-DD'),1);

INSERT INTO mytabwithInterval VALUES (TO_DATE('2015-02-01', 'YYYY-MM-DD'),2);

INSERT INTO mytabwithInterval VALUES (TO_DATE('2015-03-01', 'YYYY-MM-DD'),3);

INSERT INTO mytabwithInterval VALUES (TO_DATE('2015-04-01', 'YYYY-MM-DD'),3);
COMMIT;

-- check partition information in dictionary table

col partition_name format a20
select partition_name from user_tab_partitions where table_name='MYTABWITHINTERVAL';


-- To see default behavior of dbms_metadata:


set long 100000
set pagesize 50
col DDL format a120

SELECT DBMS_METADATA.GET_DDL('TABLE' ,'MYTABWITHINTERVAL','SYS') FROM DUAL;


-- To see it with export option:


exec dbms_metadata.set_transform_param(dbms_metadata.SESSION_TRANSFORM,'EXPORT',true);
SELECT DBMS_METADATA.GET_DDL('TABLE' ,'MYTABWITHINTERVAL','SYS') FROM DUAL;


OUTPUT:


Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL> set lines 181
SQL> set pages 100
SQL> CREATE TABLE mytabwithInterval
(mydate DATE,
 mynum NUMBER)
PARTITION BY RANGE (mydate) INTERVAL (NUMTOYMINTERVAL(1,'MONTH'))
 (PARTITION P_20150301  VALUES LESS THAN (TO_DATE(' 2015-03-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')));
  2    3    4    5 
Table created.

SQL> INSERT INTO mytabwithInterval VALUES (TO_DATE('2015-01-01', 'YYYY-MM-DD'),1);

INSERT INTO mytabwithInterval VALUES (TO_DATE('2015-02-01', 'YYYY-MM-DD'),2);

INSERT INTO mytabwithInterval VALUES (TO_DATE('2015-03-01', 'YYYY-MM-DD'),3);

INSERT INTO mytabwithInterval VALUES (TO_DATE('2015-04-01', 'YYYY-MM-DD'),3);
COMMIT;
1 row created.

SQL> SQL>
1 row created.

SQL> SQL>
1 row created.

SQL> SQL>
1 row created.

SQL>

Commit complete.

SQL> col partition_name format a20
select partition_name from user_tab_partitions where table_name='MYTABWITHINTERVAL';SQL>

PARTITION_NAME
--------------------
P_20150301
SYS_P561
SYS_P562

SQL>


SQL>
SQL>
SQL> set long 100000
set pagesize 50
col DDL format a120

SELECT DBMS_METADATA.GET_DDL('TABLE' ,'MYTABWITHINTERVAL','SYS') FROM DUAL;
SQL> SQL> SQL> SQL>

DBMS_METADATA.GET_DDL('TABLE','MYTABWITHINTERVAL','SYS')
--------------------------------------------------------------------------------

  CREATE TABLE "SYS"."MYTABWITHINTERVAL"
   (    "MYDATE" DATE,
    "MYNUM" NUMBER
   ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
  STORAGE(
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "SYSTEM"
  PARTITION BY RANGE ("MYDATE") INTERVAL (NUMTOYMINTERVAL(1,'MONTH'))
 (PARTITION "P_20150301"  VALUES LESS THAN (TO_DATE(' 2015-03-01 00:00:00', 'SYY
YY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
 NOCOMPRESS LOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "SYSTEM" )


SQL> SQL> exec dbms_metadata.set_transform_param(dbms_metadata.SESSION_TRANSFORM,'EXPORT',true);
SELECT DBMS_METADATA.GET_DDL('TABLE' ,'MYTABWITHINTERVAL','SYS') FROM DUAL;

PL/SQL procedure successfully completed.

SQL>

DBMS_METADATA.GET_DDL('TABLE','MYTABWITHINTERVAL','SYS')
--------------------------------------------------------------------------------

  CREATE TABLE "SYS"."MYTABWITHINTERVAL"
   (    "MYDATE" DATE,
    "MYNUM" NUMBER
   ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
  STORAGE(
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "SYSTEM"
  PARTITION BY RANGE ("MYDATE") INTERVAL (NUMTOYMINTERVAL(1,'MONTH')) TRANSITION
 ("P_20150301")
 (PARTITION "P_20150301"  VALUES LESS THAN (TO_DATE(' 2015-03-01 00:00:00', 'SYY
YY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
 NOCOMPRESS LOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "SYSTEM" ,
 PARTITION "SYS_P561"  VALUES LESS THAN (TO_DATE(' 2015-04-01 00:00:00', 'SYYYY-
MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
 NOCOMPRESS LOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "SYSTEM" ,
 PARTITION "SYS_P562"  VALUES LESS THAN (TO_DATE(' 2015-05-01 00:00:00', 'SYYYY-
MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
 NOCOMPRESS LOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "SYSTEM" )

Enjoy!!!
Categories: DBA Blogs

Oracle APEX_WEB_SERVICE REST API call

Kubilay Çilkara - Wed, 2015-03-04 12:15
In this post I will try to show you how I used the Oracle Apex and the APEX_WEB_SERVICE  PL/SQL package to quickly send a request to a public Internet API and how I handled the response. The code below was written during a 'Hackday' and hasn't been extensively tested.

My use case is integrating Oracle Apex with the public Mendeley REST API for Mendeley Catalog Search.

The idea was to build an application in Oracle Apex to query the Mendeley REST API Catalog with a keyword. Mendeley REST API gives JSON response so I used PL/JSON to parse it.  I hear in Oracle 12c JSON is going to be a native data-type. My Oracle Apex host is running Oracle 11g and I had to use PL/JSON for ease.

To cut it short here is how the Mendeley Catalog Search on Oracle Apex application look  like. (Click image to go to app or visit http://apex2.enciva.co.uk/apex/f?p=864:2






To integrate with Mendeley REST API from Oracle Apex, I used one PL/SQL function and one procedure.

I used the function to obtain the Mendeley REST API Client Credentials Authorisation flow token and the procedure to do make the API request to Mendeley Catalog Search and to handle the response.

Here is the MENDELEY_CALL PL/SQL function I created:

This function returns the Client Credentials Authorisation Flow token from the Mendeeley REST API

create or replace function mendeley_call (p_id in varchar2)
return varchar2
is
v_token varchar2(1000);
token varchar2(1000);
jtoken json;
v_grant_type varchar2(400:= 'client_credentials';
v_client_id varchar2(500:= p_id;
v_client_secret varchar2(500:= '<put_your_mendeley_client_secret_here>';
v_scope varchar2(300:= 'all';
begin

/*----------Setting Headers----------------------------------------*/                                      
apex_web_service.g_request_headers(1).name := 'Content-Type';
apex_web_service.g_request_headers(1).Value := 'application/x-www-form-urlencoded; charset=utf-8';
/*-----------------------------------------------------------------*/

token := apex_web_service.make_rest_request
    (
      p_url         => 'https://api.mendeley.com/oauth/token'
    p_http_method => 'POST'
    p_parm_name   => apex_util.string_to_table('grant_type:client_id:client_secret:scope')
    p_parm_value  => apex_util.string_to_table(v_grant_type||':'||v_client_id||':'||v_client_secret||':'

||v_scope)
    p_wallet_path => 'file:/home/app/oracle/product/11.2.0/dbhome_1/owm/wallets/oracle'
    p_wallet_pwd  => '<put_your_oracle_wallet_password_here>'
    );
-- debug
-- dbms_output.put_line(token);
jtoken := json(token);
v_token := json_ext.get_string(jtoken,'access_token');
-- debug
-- dbms_output.put_line(v_token);
return v_token;
EXCEPTION
WHEN OTHERS THEN
   raise_application_error(-20001,'An error was encountered - '||SQLCODE||' -ERROR- '||SQLERRM);
end;



Here is the anonymous procedure which I put into a PL/SQL region on the Oracle Apex page:

This procedure incorporates the function above and makes the request and handles the response from the Mendeley REST API

Note how the procedure calls the function MENDELEY_CALL (above) to load the variable v_token. 

DECLARE
  v_token  VARCHAR2(599:= mendeley_call(put_your_mendeley_client_id_here);
  v_search VARCHAR2(500);
  mendeley_document NCLOB;
  v_status VARCHAR2(100);
  obj json_list;
  v_id VARCHAR2(100);
  v_title NVARCHAR2(1000);
  v_abstract NCLOB;--varchar2(32000);
  v_link     VARCHAR2(1000);
  v_source   VARCHAR2(500);
  v_type     VARCHAR2(100);
  v_pct_hit  VARCHAR2(10);
  v_rows     NUMBER(10);
  v_batch_id NUMBER(10);
BEGIN
  -- Oracle Wallet
  utl_http.set_wallet('file:/home/app/oracle/product/11.2.0/dbhome_1/owm/wallets/oracle'

'my_secret_password');
  -- Set Authorisation headers and utf8
  -- the following lilne is necessary if you need to use languages other than latin and 
  -- you will use APEX_WEB_SERVICE package 
  utl_http.set_body_charset('UTF-8');
  -- build the Authorisation header
  apex_web_service.g_request_headers(1).name  := 'Content-Type';
  apex_web_service.g_request_headers(1).value := 'application/jsonrequest';
  apex_web_service.g_request_headers(1).name  := 'Authorization';
  apex_web_service.g_request_headers(1).value := 'Bearer '||v_token||'';
  
  -- Make the request and load the response into a CLOB 
  mendeley_document := apex_web_service.make_rest_request 
      
        p_url => 'https://api.mendeley.com:443/search/catalog' 
      p_http_method => 'GET' 
      p_parm_name => apex_util.string_to_table('title:limit'
      p_parm_value => apex_util.string_to_table('Mendeley:10'
      );
  -- Load the response to JSON_LIST PL/JSON object
  obj := json_list(mendeley_document);
  -- Start extracting values from the JSON and writhe some HTML
  -- Traverse over JSON_LIST extract elements you like
  FOR IN 1..obj.count
  LOOP
    v_id       := json_ext.get_string(json(obj.get(i)),'id');
    v_title    := json_ext.get_string(json(obj.get(i)),'title');
    v_abstract := json_ext.get_string(json(obj.get(i)),'abstract');
    v_link     := json_ext.get_string(json(obj.get(i)),'link');
    v_source   := json_ext.get_string(json(obj.get(i)),'source');
    v_type     := json_ext.get_string(json(obj.get(i)),'type');
    -- write extracted data
   dbms_output.put_line(v_title||' ==> '||v_abstract);
   END LOOP;
 END;

 END;

This shows how easy is, in this case using one function and one procedure to make a REST API request to an external Web Service from Oracle Apex. 
Categories: DBA Blogs

Oracle 12c Deprecated Database Parameters

VitalSoftTech - Mon, 2015-03-02 15:24
When upgrading to Oracle 12c it's good to be familiar with the deprecated parameters. These are not really desupported, just that they will be de-supported in future versions of the Database. Read more ..
Categories: DBA Blogs

Test MySQL on AWS quickly

Kubilay Çilkara - Thu, 2015-02-19 02:20
Using sysbench to performance test AWS RDS MySQL hardware is an easy three step  operation. Sysbench creates synthetic tests and they are done on a 1 mil row 'sbtest' table that sysbench creates in the MySQL database you indicate. The test doesn't intrude with your database schema, and it doesn't use your data, so it is quite safe. The test is an OLTP test trying to simulate event operations in the database as it runs various, SELECT, INSERT, UPDATE and DELETE requests on it's own 'sbtest' table.The results  of the tests are metrics like transactions per second, number of events, elapsed time etc. See man pages for description and Google it, it is a  popular testing tool. Other things you can set it up to do are to control how many requests (events) you want it to execute in a given time or you can tell it to keep on executing infinite requests until you stop it, or destruction testing. Is a very flexible testing tool with many options including throtling concurrency.You can be up and running with 3 commands on a unix system as follows.  Download sysbench tool (doing this on ubuntu)sudo apt-get install sysbenchCreate a table with 1 mil rowssysbench --test=oltp --oltp-table-size=1000000 --mysql-host={your rds host url} --db-driver=mysql --mysql-user={your rds root user} --mysql-password={password} --mysql-db={your mysql database name} prepareTest with different parameterssysbench --test=oltp --oltp-table-size=1000000 --mysql-host={your rds host url} --db-driver=mysql --mysql-user={your rds root user} --mysql-password={password} --mysql-db={your mysql database name} --max-time=60 --num-threads=550 runWarning: Synthetic  tests will just give you the ability of the hardware at a given standard set of requests and DML operations. There are no way an indication of what will happen to your database if the real workload increases beacause of the applications. Application Load Testing is something else, applications are  complex!  Database Workload is dependent on the application generated workload from real users using the system and is very hard to simulate that in a test. It is not imppossible If you use a database, such as Oracle which has the capability of recording and replaying its production database workload - called Automatic Workload Repository (AWR). In MySQL I couldn't find  so far a way to do this. But sysbench synthetic tests gave me the ability to quickly benchmark and baseline a MySQL database capabilities on different AWS Amazon hardware, something is better than nothing I suppose. 
Categories: DBA Blogs

Pages

Subscribe to Oracle FAQ aggregator - DBA Blogs