Share Knowledge: April 2014

Thursday, April 17, 2014

Membuat Backup Logic dengan Menggunakan Shell Scripting

Berikut ini Script sederhana untuk membuat logical backup dimana file backup akan langsung di zip dan backup file yang lebih dari dua hari akan di delete . Mudah-mudahan bermanfaat :)

[root@cl01nodsq endera_scripts]# cat expdb.sh
#!/bin/ksh
#
# Author : Ida Bagus Enderajana
# Website: http://cortex165.blogspot.com/
#
TODAY=$(date +%Y\%m\%d)
export HOST=`hostname`
export PATH=/usr/kerberos/bin:/usr/local/bin:/bin:/usr/bin:/u01/app/oracle/product/10.2.0/db_1/bin:/bin:/usr/sbin:/sbin:/usr/bin:/usr/ccs/bin:/usr/ucb:/usr/X11R6/lib
export ORACLE_HOME=/u01/app/oracle/product/10.2.0/db_1

# Create Directory for dmp files
EXPDIR=/u01/backup_data/${TODAY}
#RETENTION=25
if [ ! -d $EXPDIR ]
then
mkdir -p $EXPDIR
fi

# Create option for export
while getopts :s:u:r:f: opt;
do
case $opt in
s) ORACLE_SID=$OPTARG ;;
u) OWNERS=$OPTARG ;;
r) ROW=$OPTARG ;;
f) NFILE=$OPTARG ;;
\?) usage ;;
esac
done

FILE_NAME=$EXPDIR/bkp_"$NFILE"_$TODAY.dmp
#
#
exp userid=system/manager@$ORACLE_SID rows=$ROW owner=$OWNERS file=$EXPDIR/bkp_"$NFILE"_$TODAY.dmp log=$EXPDIR/bkp_"$NFILE"_$TODAY.log feedback=10000 buffer=10000000 consistent=y direct=y statistics=none
#
# Zip the file backup

gzip $FILE_NAME

find /u01/backup_data/ -ctime 2 -name '2012*' | xargs /bin/rm -rf

Kernel Parameters

Setelah sukses dengan instalasi Linux OS Versi 5 pastikan nilai-nilai parametrer dalam file /etc/sysctl.conf sesuai dengan rekomendasi dari oracle agar Instalasi RAC berjalan dengan lancar, login sebagai root untuk melakukan perubahan kernel, berikut ini nilai kernel yang di rekomendasikan oleh Oracle :

Setting Kernel untuk Oracle 11g R1:
kernel.shmall = 2097152
kernel.shmmax = 2147483648
kernel.shmmni = 4096
kernel.sem = 250 32000 100 128
fs.aio-max-size =1048576
fs.file-max = 6815744
net.ipv4.ip_local_port_range = 1024 65000
net.core.rmem_default = 262144
net.core.rmem_max = 4194304
net.core.wmem_default = 262144
net.core.wmem_max = 1048576

Setting Kernel untuk Oracle 11g R2:
kernel.shmall = 2097152
kernel.shmmax = 536870912
kernel.shmmni = 4096
kernel.sem = 250 32000 100 128
fs.aio-max-size =1048576
fs.file-max = 6815744
net.ipv4.ip_local_port_range = 9000 65500
net.core.rmem_default = 262144
net.core.rmem_max = 4194304
net.core.wmem_default = 262144
net.core.wmem_max = 1048576

Referensi : Oracle RAC Documentation

Bagaimana Cara Merubah IP Interconnect/Public Interface di Clusterware

Berikut step by step cara merubah IP Interconnect di Oracle RAC, Sebaiknya di lakukan di mesin test terlebih dahulu sebelum di implementasikan di mesin Production :)

Cek konfigurasi dari interfaces yang di gunakan oleh cluster saat ini :

$ $CRS_HOME/bin/oifcfg getif

Berikut ini contohnya :

$ $CRS_HOME/bin/oifcfg getif
eth4 192.168.99.0 global cluster_interconnect
pub_bond 10.165.77.0 global public

Stop the database and nodeapps on both nodes.

$ srvctl stop database -d NAMA_DATABASE
$ srvctl stop nodeapps -n NAMA_NODE1
$ srvctl stop nodeapps -n NAMA_NODE2

Dibawah ini contohnya :

$ srvctl stop database -d TESTDB
$ srvctl stop nodeapps -n TESTDB1
$ srvctl stop nodeapps -n TESTDB2

Untuk merubah interface adalah dengan delete interface yang di gunakan saat ini lalu tambahkan interface yang baru

$ $CRS_HOME/bin/oifcfg delif -global interface_name

Tambahkan interface Baru

$CRS_HOME/bin/oifcfg setif -global new_if/network_number:cluster_interconnect

Berikut ini contohnya :

$ $ORA_CRS_HOME/bin/oifcfg getif
eth4 192.168.99.0 global cluster_interconnect
pub_bond 10.165.199.0 global public
$ $ORA_CRS_HOME/bin/oifcfg delif -global eth4
$ $ORA_CRS_HOME/bin/oifcfg getif
pub_bond 10.165.77.0 global public
$ $CRS_HOME/bin/oifcfg setif -global int_bond/192.168.99.0:cluster_interconnect
Confirm the changes have taken place.
$ $CRS_HOME/bin/oifcfg getif
int_bond 192.168.99.0 global cluster_interconnect
pub_bond 10.165.77.0 global public

Merubah Public Interface and VIP

Jika yang dirubah adalah Public interface maka akan merubah VIP (Virtual IP). Merubah VIP termasuk juga memodifikasi tiap node.

Berikut ini Step-stepnya :

$ srvctl stop nodeapps -n TESTDB1
$ srvctl stop nodeapps -n TESTDB2

Confirm current configuration.

$ $CRS_HOME/bin/oifcfg getif
eth1 10.165.77.0 global public
eth4 192.168.99.0 global cluster_interconnect

Ubah public interface dari eth1 ke pub_bond

$ $CRS_HOME/bin/oifcfg delif -global eth1
$ $ORA_CRS_HOME/bin/oifcfg getif
eth4 192.168.99.0 global cluster_interconnect
$ $ORA_CRS_HOME/bin/oifcfg setif -global pub_bond/10.165.77.0:public
$ srvctl modify nodeapps -n TESTDB1 -A 10.165.77.152/255.255.255.0/pub_bond
PRKO-2117 : This command should be executed as the system privilege user.

[oracle@nycdb01 ~]$ su -
Password:
[root@nycdb01 oracle]# srvctl modify nodeapps -n nycdb01 -A 10.165.77.152/255.255.255.0/pub_bond
[root@nycdb01 oracle]# srvctl modify nodeapps -n nycdb02 -A 10.164.227.151/255.255.255.0/pub_bond

Verifikasi

# $CRS_HOME/bin/crs_stat -p ora.TESTDB1.vip | grep USR_ORA_IF
USR_ORA_IF=pub_bond
# $CRS_HOME/bin/crs_stat -p ora.TESTDB2.vip | grep USR_ORA_IF
USR_ORA_IF=pub_bond

Reference: Oracle Clusterware Documentation, Metalink Notes

Thursday, April 10, 2014

Trik Jika Lupa password Root di mesin HP-UX

Lupa password root ? . . . he he he bikin panik emang apalagi klo itu adalah mesin production, berikut ini pengalaman saya waktu lupa password root di mesin dan OS nya juga HP-UX , dibawah ini adalah tampilan yang saya capture ketika booting langsung dari management console . Proses penggantian passwordnya ada pada bagian yang saya bikin tebal hurufnya (perl script di bagian bawah ) . Berikut ini proses lengkapnya mudah-mudahan bermanfaat :

EFI Boot Manager ver 1.10 [14.62] Firmware

Loading device drivers

EFI Boot Manager ver 1.10 [14.62]

/----------------------------------\
/------------------------------------\| System Overview |
| Boot Menu || hp server rx4640 |
| HP-UX Primary Boot: 0/1/1/0.1.0 | Serial #: SGH4606770 |
| Core LAN Gb A | |
| HP-UX Alternate Boot: 0/1/1/1.0.0 | System Firmware: 3.17 [4511] |
| EFI Shell [Built-in] | BMC Version: 3.52 |
| Internal Bootable DVD | MP Version: E.03.15 |
| Core LAN Gb B | Installed Memory: 4096 MB |
| --------------------------------- | |
| Boot Configuration | CPU Logical |
| System Configuration | Module CPUs
| Security Configuration | 0 1 1.6 GHz Active |
| | 1 1 1.6 GHz Active |
\------------------------------------/ |
|
| |
| |
\------------

Use <^|v> to scroll <ENTER> to Select <ESC> or <X/x> for Previous Menu

Loading.: HP-UX Alternate Boot: 0/1/1/1.0.0
Starting: HP-UX Alternate Boot: 0/1/1/1.0.0

(C) Copyright 2004 Hewlett-Packard Development Company, L.P.All rights reserved

HP-UX Boot Loader for IPF -- Revision 2.027

Press Any Key to interrupt Autoboot
\EFI\HPUX\AUTO ==> boot vmunix -lq
Seconds left till autoboot - 4
Type 'help' for help

HPUX> help
-- HPUX bootloader for IPF Help --

-------- supported commands ---
boot [kernel] - boots HPUX kernel
help [-d] - help screen (-d to list debug commands)
mmap - show current EFI memory map
ls [-aRdnlogrtucpFLis] [path...]
ll [-aRdnlogrtucpFLis] [path...] (same as ls -alF)
setauto [-d] [str] - sets AUTO file (-d to delete AUTO file)
showauto - shows AUTO file
ver - prints version numbers
exit - exits bootloader

HPUX> hpux -iS
> System Memory = 4083 MB
loading section 0
........................................................ (complete)
loading section 1
............. (complete)
loading symbol table
loading System Directory (boot.sys) to MFS
....
loading MFSFILES directory (bootfs) to MFS
...................
Launching /stand/vmunix
SIZE: Text:28475K + Data:6495K + BSS:5239K = Total:40209K

Console is on a Serial Device
Booting kernel...

HP-UX will call firmware in physical-addressing mode

Memory Class Setup
-------------------------------------------------------------------------
Class Physmem Lockmem Swapmem
-------------------------------------------------------------------------
System : 4083 MB 4083 MB 4083 MB
Kernel : 4083 MB 4083 MB 4083 MB
User : 3642 MB 3172 MB 3184 MB
-------------------------------------------------------------------------

Loaded ACPI revision 2.0 tables.
NOTICE: cachefs_link(): File system was registered at index 4.
NOTICE: nfs3_link(): File system
Boot device's HP-UX HW path is: 0/1/1/1.0.0

System Console is on the Built-In Serial Interface
iether0: INITIALIZING HP AB352-60001 PCI/PCI-X 1000Base-T Dual-port Core at hard
ware path 0/1/2/0
iether1: INITIALIZING HP AB352-60001 PCI/PCI-X 1000Base-T Dual-port Core at hard
ware path 0/1/2/1
iether2: INITIALIZING HP A7012-60001 PCI/PCI-X 1000Base-T Dual-port Adapter at h
ardware path 0/2/1/0
iether3: INITIALIZING HP A7012-60001 PCI/PCI-X 1000Base-T Dual-port Adapter at h
ardware path 0/2/1/1
Logical volume 64, 0x3 configured as ROOT
Logical volume 64, 0x2 configured as SWAP
Logical volume 64, 0x2 configured as DUMP
Swap device table: (start & size given in 512-byte blocks)
entry 0 - major is 64, minor is 0x2; start = 0, size = 16777216
Starting the STREAMS daemons-phase 1
Checking root file system.
log replay in progress
replay complete - marking super-block as CLEAN
Root check done.
Create STCP device files
Starting the STREAMS daemons-phase 2
$Revision: vmunix: B11.23_LR FLAVOR
Memory Information:
physical page size = 4096 bytes, logical page size = 4096 bytes
Physical: 4181616 Kbytes, lockable: 2883876 Kbytes, available: 3366532 Kbyte
s

/sbin/ioinitrc:
/dev/vg00/lvol1:log replay in progress
/dev/vg00/lvol1:replay complete - marking super-block as CLEAN
/sbin/krs_sysinit:
* The module 'gvid_info' has been loaded.
* The module 'rng' has been loaded.
* The module 'ipf' has been loaded.
* The module 'dmphdsalua' has been loaded.
* The module 'dmph
* The module 'dmpjbod' has been loaded.
* The module 'dmpapf' has been loaded.
* The module 'dmpapg' has been loaded.
* The module 'dmpap' has been loaded.
* The module 'dmpaaa' has been loaded.
* The module 'dmpaa' has been loaded.

insf: Installing special files for ipmi instance 0 address 250/0

INIT: Overriding default level with level 'S'

INIT: SINGLE USER MODE

INIT: Running /sbin/sh
#
#
#
# perl
sh: perl: not found
# mountall
mount:all was either ignored or not found
# mountall
mountall: /dev/vg00/lvol8 has to be fsck'd
mountall: diagnostics from fsck
vxfs fsck: sanity check: /dev/vg00/lvol8 needs checking
mountall: /dev/vg00/lvol8 was fsck'd and fixed
mountall: /dev/vg00/lvol10 has to be fsck'd
mountall: diagnostics from fsck
vxfs fsck: sanity check: /dev/vg00/lvol10 needs checking
mountall: /dev/vg00/lvol10 was fsck'd and fixed
mountall: /dev/vg00/lvol9 has to be fsck'd
mountall: diagnostics from fsck
vxfs fsck: sanity check: /dev/vg00/lvol9 needs checking
mountall: /dev/vg00/lvol9 was fsck'd and fixed
mountall: /dev/vg00/lvol7 has to be fsck'd
mountall: diagnostics from fsck
vxfs fsck: sanity check: /dev/vg00/lvol7 needs checking
mountall: /dev/vg00/lvol7 was fsck'd and fixed
mountall: cannot fsck /dev/vg02/lvol1
mountall: diagnostics from fsck
vxfs fsck: sanity check failed: cannot open /dev/vg02/lvol1: No such device or a
ddress
mountall: cannot mount /dev/vg02/lvol1
mountall: cannot fsck /dev/vg03/lvol1
mountall: diagnostics from fsck
vxfs fsck: sanity check failed: cannot open /dev/vg03/lvol1: No such device or a
ddress
mountall: cannot mount /dev/vg03/lvol1
mountall: cannot fsck /dev/vg01/lvol1
mountall: diagnostics from fsck
vxfs fsck: sanity check failed: cannot open /dev/vg01/lvol1: No such device or a
ddress
mountall: cannot mount /dev/vg01/lvol1
mountall: /dev/vg00/lvol4 has to be fsck'd
mountall: diagnostics from fsck
vxfs fsck: sanity check: /dev/vg00/lvol4 needs checking
mountall: /dev/vg00/lvol4 was fsck'd and fixed
mountall: /dev/vg00/lvol6 has to be fsck'd
mountall: diagnostics from fsck
vxfs fsck: sanity check: /dev/vg00/lvol6 needs checking
mountall: /dev/vg00/lvol6 was fsck'd and fixed
mountall: /dev/vg00/lvol5 has to be fsck'd
mountall: diagnostics from fsck
vxfs fsck: sanity check: /dev/vg00/lvol5 needs checking
mountall: /dev/vg00/lvol5 was fsck'd and fixed
mountall: cannot fsck /dev/vg03/lvol2
mountall: diagnostics from fsck
vxfs fsck: sanity check failed: cannot open /dev/vg03/lvol2: No such device or a
ddress
mountall: cannot mount /dev/vg03/lvol2
mountall: cannot fsck /dev/vg01/lvol4
mountall: diagnostics from fsck
vxfs fsck: sanity check failed: cannot open /dev/vg01/lvol4: No such device or a
ddress
mountall: cannot mount /dev/vg01/lvol4
mountall: cannot fsck /dev/vg01/lvol2
mountall: diagnostics from fsck
vxfs fsck: sanity check failed: cannot open /dev/vg01/lvol2: No such device or a
ddress
mountall: cannot mount /dev/vg01/lvol2
mountall: cannot fsck /dev/vg01/lvol3
mountall: diagnostics from fsck
vxfs fsck: sanity check failed: cannot open /dev/vg01/lvol3: No such device or a
ddress
mountall: cannot mount /dev/vg01/lvol3
mountall: /dev/vg00/lvol11 has to be fsck'd
mountall: diagnostics from fsck
vxfs fsck: sanity check: /dev/vg00/lvol11 needs checking
mountall: /dev/vg00/lvol11 was fsck'd and fixed
mountall: /dev/vg00/lvol12 has to be fsck'd
mountall: diagnostics from fsck
vxfs fsck: sanity check: /dev/vg00/lvol12 needs checking
mountall: /dev/vg00/lvol12 was fsck'd and fixed
#
#
# which perl
/usr/bin/perl
# perl -e "print crypt
#
# perl -e "print crypt("abcd1234",hello);"
heYm88sS40TRc#
#
#
#
# cd /
# ls
.ICEauthority core lost+found tcb
.TTauthority core.pfs_umount make_sys_image.log tmp
.Xauthority data net u01
.secure data2 null
DATA-PROTECTOR dev opt usr
SD_CDROM etc root var
bin home sbin
comptel lib stand
# cd tvb
sh: tvb: not found.
# cd tcb
# ls
files
# cd files
# ls
auth devassign ttys
# cd auth
# ls
A G M S Y e k q v
B H N T Z f l r w
C I O U a g m s
D J P V b h n system y
E K Q W c i o t z
F L R X d j p u
# cd roo
sh: roo: not found.
# cd r
# ls
root
# vi root
I don't know what kind of terminal you are on - all I have is 'unknown'.
[Using open mode]
"root" 6 lines, 229 characters
root:u_name=root:u_id#0:\
:u_pwd=di65XSzl/YwRY:\
:u_bootauth:u_auditid#0:\
:u_auditflag#1:\
:u_bootauth:u_auditid#0:\
:u_auditflag#1:\
:u_succhg#1176192280:u_pswduser=root:u_suclog#1176194918:u_suctty=consol
e:\
:u_unsuclog#1177043350:u_numunsuclog#17:u_lock@:chkent:
:u_succhg#1176192280:u_pswduser=root:u_suclog#1176194918:u_suctty=consol
e:\
:q!

# export TERM=vt100
# vi root
"root" 6 lines, 229 characters
"root" 6 lines, 229 characters
root:u_name=root:u_id#0:\
:u_pwd=heYm88sS40TRc:\
:u_bootauth:u_auditid#0:\
:u_auditflag#1:\
:u_succhg#1176192280:u_pswduser=root:u_suclog#1176194918:u_suctty=consol

e:\ :u_unsuclog#1177043350:u_numunsuclog#17:u_lock@:chkent:
~

~
~
~
"root" 6 lines, 229 characters
#
#
#
# su -
********************************************************************
* *
* ATTENTION *
* *
* This system is for the use of authorized users only. *
* Individuals using this computer system without authority or in *
* excess of their authority are subject to having all of their *
* activities on this system monitored and recorded by system *
* personnel. Anyone using this system expressly consents to such *
* monitoring and is advised that if such monitoring reveals *
* possible evidence of criminal activity system personnel may *
* provide the evidence of such monitoring to law enforcement *
* officials. *
* *
********************************************************************
You have mail.

Value of TERM has been set to "vt100".
WARNING: YOU ARE SUPERUSER !!

NOTE: Your root shell will bie if inactive for 60 minutes.
unknown:/root>
unknown:/root>
unknown:/root>
unknown:/root>
unknown:/root>passwd
Changing password for root
Old password:
Last successful password change for root: Tue Apr 10 15:04:40 2007
Last unsuccessful password change for root: NEVER

New password:
Password too short - must be at least 8 characters
New password:
Re-enter new password:
Passwd successfully changed
unknown:/root>

SCRIPT FOR CREATING PARTITIONED TABLES

Di bawah ini script buatan Rampant TechPress Inc yang sangat berguna untuk mengcreate ulang table partition, untuk script dibawah ini ada beberapa bagian yang saya modifikasi sesuai dengan keperluan, berikut ini script lengkapnya

/* Copyright © 2004, 2008 by Rampant TechPress Inc.*/

REM

REM part_tab_rct.sql
REM

REM FUNCTION: SCRIPT FOR CREATING PARTITIONED TABLES
REM

REM This script can be run by any user .
REM

REM This script is intended to run with Oracle8i.
REM

REM Running this script will in turn create a script to
REM build all the partitioned tables owned by the user in the database.
REM This created script, create_part_table.sql, can be run by any user
REM with the 'CREATE TABLE' system privilege.
REM

REM NOTE: The script will NOT include constraints on tables. This
REM script will also NOT capture tables created by user 'SYS'.
REM

REM Only preliminary testing of this script was performed. Be sure to test
REM it completely before relying on it.
REM

set verify off
set feedback off
set echo off;
set pagesize 0 lines 132 embedded on

set termout on
select 'Creating table build script...' from dual;

--- drop table t_temp;

create table t_temp
(lineno NUMBER, tb_owner VARCHAR2(30), tb_name VARCHAR2(30),
text VARCHAR2(2000))
/
drop sequence lineno_seq;
create sequence lineno_seq start with 1 increment by 1 nocache nocycle;

DECLARE
CURSOR tab_cursor(tab_name VARCHAR2) IS select table_name,
PARTITIONING_TYPE,
SUBPARTITIONING_TYPE,
PARTITION_COUNT,
DEF_SUBPARTITION_COUNT,
PARTITIONING_KEY_COUNT,
SUBPARTITIONING_KEY_COUNT,
DEF_TABLESPACE_NAME
from dba_part_tables
where table_name=upper(tab_name);
--
CURSOR col_cursor (c_tab VARCHAR2) IS select
column_name,
data_type,
data_length,
data_precision,
data_scale,
nullable
from dba_tab_columns
where table_name = c_tab
order by column_id;
--
CURSOR part_key (c_tab VARCHAR2) is select column_name,
column_position
from dba_part_key_columns
where trim(name)=c_tab
and trim(object_type)='TABLE'
order by column_position;
--
CURSOR part_cursor (c_tab VARCHAR2) IS select PARTITION_NAME,
SUBPARTITION_COUNT,
HIGH_VALUE,
HIGH_VALUE_LENGTH,
PARTITION_POSITION,
TABLESPACE_NAME
from dba_tab_partitions
where table_name=c_tab
order by partition_position;
--
CURSOR subpart_key (c_tab VARCHAR2) IS select column_name,
column_position
from dba_subpart_key_columns
where trim(name)=c_tab
and trim(object_type)='TABLE'
order by column_position;
--
CURSOR subpart_tbsp (c_tab VARCHAR2) IS select unique tablespace_name
from dba_tab_subpartitions
where table_name=c_tab;
--
CURSOR subpart_cursor (c_tab VARCHAR2, tab_part VARCHAR2) IS select
SUBPARTITION_NAME,
tablespace_name
from dba_tab_subpartitions
where table_name=c_tab
and partition_name=tab_part
order by subpartition_position;
--
CURSOR subpart_stor (c_tab varchar2, tab_part varchar2) IS select
initial_extent,
next_extent,
pct_increase
from dba_tab_subpartitions
where table_name=c_tab
and partition_name=tab_part
and subpartition_position=1;
--
lv_table_name dba_part_tables.table_name%TYPE;
lv_partitioning_type dba_part_tables.partitioning_type%TYPE;
lv_subpartitioning_type dba_part_tables.subpartitioning_type%TYPE;
lv_partition_count dba_part_tables.partition_count%TYPE;
lv_subpartition_count dba_part_tables.def_subpartition_count%TYPE;
lv_partition_key_count dba_part_tables.partitioning_key_count%TYPE;
lv_subpartition_key_count dba_part_tables.subpartitioning_key_count%TYPE;
lv_tablespace_name dba_part_tables.def_tablespace_name%TYPE;
lv_column_name dba_tab_columns.column_name%TYPE;
lv_data_type dba_tab_columns.data_type%TYPE;
lv_data_length dba_tab_columns.data_length%TYPE;
lv_data_precision dba_tab_columns.data_precision%TYPE;
lv_data_scale dba_tab_columns.data_scale%TYPE;
lv_nullable dba_tab_columns.nullable%TYPE;
lv_pkey_column_name dba_part_key_columns.column_name%TYPE;
lv_pkey_column_number dba_part_key_columns.column_position%TYPE;
lv_spkey_column_name dba_subpart_key_columns.column_name%TYPE;
lv_spkey_column_number dba_subpart_key_columns.column_position%TYPE;
lv_part_COMPOSITE dba_tab_partitions.composite%TYPE;
lv_PARTITION_NAME dba_tab_partitions.partition_name%TYPE;
lv_part_SUBPARTITION_COUNT dba_tab_partitions.subpartition_count%TYPE;
lv_HIGH_VALUE dba_tab_partitions.high_value%TYPE;
lv_HIGH_VALUE_LENGTH dba_tab_partitions.high_value_length%TYPE;
lv_PARTITION_POSITION dba_tab_partitions.partition_position%TYPE;
lv_part_TABLESPACE_NAME dba_tab_partitions.tablespace_name%TYPE;
lv_SUBPARTITION_NAME dba_tab_subpartitions.SUBPARTITION_NAME%TYPE;
lv_spart_TABLESPACE_NAME dba_tab_subpartitions.TABLESPACE_NAME%TYPE;
lv_subpartition_position dba_tab_subpartitions.subpartition_position%TYPE;
lv_subp_initial_extent dba_tab_subpartitions.initial_extent%TYPE;
lv_subp_next_extent dba_tab_subpartitions.next_extent%TYPE;
lv_subp_pct_increase dba_tab_subpartitions.pct_increase%TYPE;
lv_first_rec BOOLEAN;
lv_string VARCHAR2(2000);
tbsp_string VARCHAR2(2000);
tmp_string VARCHAR2(2000);
nul_cnt number;
sp_cnt number;
flg VARCHAR2(32);
fl VARCHAR2(32);
minex VARCHAR2(32);
maxex VARCHAR2(32);
--
procedure write_out(p_name VARCHAR2,
p_string VARCHAR2) is
begin
insert into t_temp (lineno, tb_name, text)
values (lineno_seq.nextval,p_name,rtrim(p_string,chr(32)));
end;
--
BEGIN
OPEN tab_cursor('&tab_name');
LOOP
FETCH tab_cursor INTO lv_table_name,
lv_partitioning_type,
lv_subpartitioning_type,
lv_partition_count,
lv_subpartition_count,
lv_partition_key_count,
lv_subpartition_key_count,
lv_tablespace_name;
EXIT WHEN tab_cursor%NOTFOUND;
lv_string := 'DROP TABLE '|| lower(lv_table_name)||';';
write_out(lv_table_name, lv_string);
lv_first_rec := TRUE;
lv_string := 'CREATE TABLE '|| lower(lv_table_name)||' (';
write_out(lv_table_name, lv_string);
lv_string := null;
OPEN col_cursor(lv_table_name);
nul_cnt:=0;
LOOP
FETCH col_cursor INTO lv_column_name,
lv_data_type,
lv_data_length,
lv_data_precision,
lv_data_scale,
lv_nullable;
EXIT WHEN col_cursor%NOTFOUND;
if (lv_first_rec) then
lv_first_rec := FALSE;
else
lv_string := ',';
end if;
if ((lv_data_type = 'NUMBER') and (lv_data_precision>0)) then
lv_string := lv_string || lower(lv_column_name) ||
' ' || lv_data_type ||'('||lv_data_precision||','||
nvl(lv_data_scale,0)||')';
elsif ((lv_data_type = 'FLOAT') and (lv_data_precision>0)) then
lv_string := lv_string || lower(lv_column_name) ||
' ' || lv_data_type ||'('||lv_data_precision||')';
else
lv_string := lv_string || lower(lv_column_name) ||
' ' || lv_data_type;
end if;
if ((lv_data_type = 'CHAR') or (lv_data_type = 'VARCHAR2')) then
lv_string := lv_string || '(' || lv_data_length || ')';
end if;
if (lv_nullable = 'N') then
nul_cnt:=nul_cnt+1;
lv_string := lv_string || ' constraint ck_'||lv_table_name||'_'||nul_cnt||' NOT NULL';
end if;
write_out(lv_table_name, lv_string);
END LOOP;
CLOSE col_cursor;
lv_string := ')';
write_out(lv_table_name, lv_string);
lv_string := NULL;
nul_cnt:=0;
--
OPEN part_key(lv_table_name);
IF lv_partition_key_count>1 THEN
LOOP
FETCH part_key INTO lv_pkey_column_name,lv_pkey_column_number;
exit when part_key%NOTFOUND;
if nul_cnt=0 THEN
tmp_string:=lv_pkey_column_name;
nul_cnt:=nul_cnt+1;
else
tmp_string:=tmp_string||','||lv_pkey_column_name;
nul_cnt:=nul_cnt+1;
dbms_output.put_line(tmp_string);
end if;
END LOOP;
end if;
if lv_partition_key_count=1 THEN
FETCH part_key INTO lv_pkey_column_name,lv_pkey_column_number;
tmp_string:=lv_pkey_column_name;
end if;
CLOSE part_key;
--
lv_string:='PARTITION BY RANGE ('||tmp_string||')';
write_out(lv_table_name, lv_string);
lv_string := NULL;
tmp_string:=NULL;
--
if lv_subpartition_key_count>0 THEN
OPEN subpart_key(lv_table_name);
if lv_subpartition_key_count=1 THEN
FETCH subpart_key INTO lv_spkey_column_name,lv_spkey_column_number;
tmp_string:=lv_spkey_column_name;
else
nul_cnt:=0;
LOOP
FETCH subpart_key INTO lv_spkey_column_name,lv_spkey_column_number;
exit when subpart_key%NOTFOUND;
if nul_cnt=0 THEN
tmp_string:=lv_spkey_column_name;
nul_cnt:=nul_cnt+1;
dbms_output.put_line(tmp_string);
else
tmp_string:=tmp_string||','||lv_spkey_column_name;
nul_cnt:=nul_cnt+1;
end if;
END LOOP;
CLOSE subpart_key;
end if;
--
if lv_subpartition_count>1 then
OPEN subpart_tbsp(lv_table_name);
nul_cnt:=0;
LOOP
FETCH subpart_tbsp INTO lv_spart_tablespace_name;
exit when subpart_tbsp%NOTFOUND;
dbms_output.put_line(lv_spart_tablespace_name);
if nul_cnt=0 then
tbsp_string:=lv_spart_tablespace_name;
nul_cnt:=nul_cnt+1;
else
tbsp_string:=tbsp_string||','||lv_spart_tablespace_name;
nul_cnt:=nul_cnt+1;
end if;
END LOOP;
end if;
CLOSE subpart_tbsp;
--
lv_string := 'SUBPARTITION BY HASH ('||tmp_string||')';
write_out(lv_table_name, lv_string);
lv_string := 'SUBPARTITIONS '||to_char(lv_subpartition_count);
write_out(lv_table_name, lv_string);
lv_string := 'STORE IN ('||tbsp_string||')';
write_out(lv_table_name, lv_string);
lv_string := null;
end if;
--
OPEN part_cursor(lv_table_name);
if lv_partition_count=1 THEN
FETCH part_cursor INTO lv_PARTITION_NAME,
lv_part_SUBPARTITION_COUNT,
lv_HIGH_VALUE,
lv_HIGH_VALUE_LENGTH,
lv_PARTITION_POSITION,
lv_part_TABLESPACE_NAME;

if lv_string is not null then
write_out(lv_table_name, lv_string);
end if;
ELSE
nul_cnt:=0;
LOOP
FETCH part_cursor INTO lv_PARTITION_NAME,
lv_part_SUBPARTITION_COUNT,
lv_HIGH_VALUE,
lv_HIGH_VALUE_LENGTH,
lv_PARTITION_POSITION,
lv_part_TABLESPACE_NAME;
exit when part_cursor%NOTFOUND;
lv_string:='PARTITION '||lv_partition_name||' VALUES LESS THAN ('||lv_high_value||')';
if nul_cnt=0 then
lv_string:='('||lv_string;
else
lv_string:=','||lv_string;
end if;
write_out(lv_table_name, lv_string);
lv_string:=fl||flg||minex||maxex;
if lv_string is not null then
write_out(lv_table_name, lv_string);
end if;

nul_cnt:=nul_cnt+1;
--
if lv_part_subpartition_count>0 THEN
OPEN subpart_cursor(lv_table_name,lv_partition_name);
sp_cnt:=0;
LOOP
FETCH subpart_cursor INTO lv_SUBPARTITION_NAME,
lv_spart_TABLESPACE_NAME;
exit when subpart_cursor%NOTFOUND;
if sp_cnt=0 THEN
lv_string:='(SUBPARTITION '||lv_subpartition_name||
' TABLESPACE '||lv_spart_tablespace_name;
write_out(lv_table_name, lv_string);
else
lv_string:=',SUBPARTITION '||lv_subpartition_name||
' TABLESPACE '||lv_spart_tablespace_name;
write_out(lv_table_name, lv_string);
end if;
sp_cnt:=sp_cnt+1;
END LOOP;
lv_string:=')';
write_out(lv_table_name, lv_string);
lv_string := null;
close subpart_cursor;
end if;
END LOOP;
close part_cursor;
end if;
--
lv_string := ')'||chr(10)||'/';
write_out(lv_table_name, lv_string);
lv_string:=' ';
write_out(lv_table_name, lv_string);
END LOOP;
CLOSE tab_cursor;
END;
/

set heading off trimspool on
spool create_part_table.sql

select text
from T_temp
order by tb_name, lineno;

spool off

drop table t_temp;
set verify on
set feedback on
set termout on
set pagesize 22 lines 80

Insert Menggunakan Metode Exchange Partition

Berikut ini contoh sederhana penggunaan metode exchange partition :

Create table destination
------------------------------
CREATE TABLE call_BEBEK (
id NUMBER(12,6),
v1 VARCHAR2(10),
data VARCHAR2(100)
)
PARTITION BY RANGE(id) (
PARTITION P_1 VALUES LESS THAN (2)
) TABLESPACE users;

set serveroutput on;
set heading off;
set feedback off;
set linesize 800;
set pagesize 999;
declare
v_no number:=1;
begin
DBMS_OUTPUT.ENABLE(1000000);
for i in 1..31 loop
dbms_output.put_line('ALTER TABLE call_bebek ADD PARTITION P_'||to_char(v_no+i) ||' VALUES LESS THAN ('||TO_CHAR (v_no+1+i)||') TABLESPACE USERS;');
dbms_output.put_line(' ');

end loop;
end;

ALTER TABLE call_bebek ADD PARTITION P_2 VALUES LESS THAN (3) TABLESPACE USERS;

ALTER TABLE call_bebek ADD PARTITION P_3 VALUES LESS THAN (4) TABLESPACE USERS;

ALTER TABLE call_bebek ADD PARTITION P_4 VALUES LESS THAN (5) TABLESPACE USERS;

ALTER TABLE call_bebek ADD PARTITION P_5 VALUES LESS THAN (6) TABLESPACE USERS;

ALTER TABLE call_bebek ADD PARTITION P_6 VALUES LESS THAN (7) TABLESPACE USERS;

ALTER TABLE call_bebek ADD PARTITION P_7 VALUES LESS THAN (8) TABLESPACE USERS;

ALTER TABLE call_bebek ADD PARTITION P_8 VALUES LESS THAN (9) TABLESPACE USERS;

ALTER TABLE call_bebek ADD PARTITION P_9 VALUES LESS THAN (10) TABLESPACE USERS;

Create local Index
---------------------------
CREATE index bebek_id on call_bebek(id) local tablespace users;

create table source
--------------------------
CREATE TABLE call_temp_BEBEK (
id NUMBER(12,6),
v1 VARCHAR2(10),
data VARCHAR2(100)
)TABLESPACE USERS;

Insert Data
----------------------------
INSERT /*+ append ordered full(s1) use_nl(s2) */
INTO call_temp_bebek
SELECT
TRUNC((ROWNUM-1)/500,6),
TO_CHAR(ROWNUM),
RPAD('X',100,'X')
FROM
all_tables s1,
all_tables s2
WHERE
ROWNUM <= 10000;

Update record to syncronize with table partition
-----------------------------------------------------
Update call_temp_bebek
set ID=8

Insert using exchange command (Metode ini berjalan dua arah dari partition ke non partition atau sebaliknya dengan perintah yang sama dibawah ini)
------------------------------------------------------------------------------------------------------------------------------------------------------
ALTER TABLE CALL_BEBEK
EXCHANGE PARTITION P_8 WITH TABLE call_temp_bebek;

ALTER TABLE sales
EXCHANGE PARTITION feb97 WITH TABLE sales_feb97
WITHOUT VALIDATION;

Home