Script ini di gunakan untuk menulis data ke TAPE :
#!/bin/sh
#--------------------------------------------------------------------#
# Script untuk menulis data ke tape #
# #
# Author : Ida Bagus Enderajana #
# #
#--------------------------------------------------------------------#
cd /backup/
for GZ in `ls *.gz`
do
echo "`date`: WRITING $GZ to TAPE..."
tar -cvf /dev/rmt/0n $GZ
done
# Hapus data yang sudah di backup
rm /backup/*.gz
echo "`date` : BACKUP TO TAPE COMPLETE ... "
Wednesday, March 21, 2012
Cara me-rename atau merubah datafile Tablespace System
Berikut ini step-step cara merename tablespace system (saya melakukan simulasi ini di vmware ) :
Cek lokasi file dari tablespace system
----------------------------------------------------
SQL> select file_name from dba_data_files where tablespace_name='SYSTEM';
FILE_NAME
--------------------------------------------------------------------------------
/export/home/oracle/sysdata/system01.dbf
Shutdown Database (Cleanup Shutdown)
-----------------------------------------------------------
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>exit
-bash-3.00$ pwd
/export/home/oracle/tempfile
-bash-3.00$ cd ..
-bash-3.00$ ls
Desktop archives bebek.txt local.login oradata sysdata
Documents backup flash_recovery_area local.profile product tempfile
admin backup02 local.cshrc oraInventory scripts
-bash-3.00$ cd sysdata
-bash-3.00$ ls
system01.dbf
-bash-3.00$ mv system01.dbf ../oradata/
-bash-3.00$ ls
-bash-3.00$
Startup Database dalam mode exsclusive
-------------------------------------------------------
-bash-3.00$ sqlplus "/as sysdba"
SQL*Plus: Release 10.2.0.2.0 - Production on Mon Jun 28 12:41:38 2010
Copyright (c) 1982, 2005, Oracle. All Rights Reserved.
Connected to an idle instance.
SQL> startup exclusive mount;
ORACLE instance started.
Total System Global Area 658505728 bytes
Fixed Size 1281936 bytes
Variable Size 176160880 bytes
Database Buffers 478150656 bytes
Redo Buffers 2912256 bytes
Database mounted.
Alter database file system
-------------------------------------------
SQL> alter database rename file '/export/home/oracle/sysdata/system01.dbf' to '/export/home/oracle/oradata/system01.dbf';
Database altered.
Shutdown Database
-------------------------------------
SQL> shutdown;
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.
SQL>
Startup Normal Database
--------------------------------------
SQL> startup;
ORACLE instance started.
Total System Global Area 658505728 bytes
Fixed Size 1281936 bytes
Variable Size 176160880 bytes
Database Buffers 478150656 bytes
Redo Buffers 2912256 bytes
Database mounted.
Database opened.
Cek datafile system untuk melihat perubahan datafile system
-------------------------------------------------------------------------------------
SQL> select file_name from dba_data_files where tablespace_name='SYSTEM';
FILE_NAME
--------------------------------------------------------------------------------
/export/home/oracle/oradata/system01.dbf
SQL>
Note : Lakukan di mesin test terlebih dahulu sebelum melakukan di production
Cek lokasi file dari tablespace system
----------------------------------------------------
SQL> select file_name from dba_data_files where tablespace_name='SYSTEM';
FILE_NAME
--------------------------------------------------------------------------------
/export/home/oracle/sysdata/system01.dbf
Shutdown Database (Cleanup Shutdown)
-----------------------------------------------------------
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>exit
-bash-3.00$ pwd
/export/home/oracle/tempfile
-bash-3.00$ cd ..
-bash-3.00$ ls
Desktop archives bebek.txt local.login oradata sysdata
Documents backup flash_recovery_area local.profile product tempfile
admin backup02 local.cshrc oraInventory scripts
-bash-3.00$ cd sysdata
-bash-3.00$ ls
system01.dbf
-bash-3.00$ mv system01.dbf ../oradata/
-bash-3.00$ ls
-bash-3.00$
Startup Database dalam mode exsclusive
-------------------------------------------------------
-bash-3.00$ sqlplus "/as sysdba"
SQL*Plus: Release 10.2.0.2.0 - Production on Mon Jun 28 12:41:38 2010
Copyright (c) 1982, 2005, Oracle. All Rights Reserved.
Connected to an idle instance.
SQL> startup exclusive mount;
ORACLE instance started.
Total System Global Area 658505728 bytes
Fixed Size 1281936 bytes
Variable Size 176160880 bytes
Database Buffers 478150656 bytes
Redo Buffers 2912256 bytes
Database mounted.
Alter database file system
-------------------------------------------
SQL> alter database rename file '/export/home/oracle/sysdata/system01.dbf' to '/export/home/oracle/oradata/system01.dbf';
Database altered.
Shutdown Database
-------------------------------------
SQL> shutdown;
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.
SQL>
Startup Normal Database
--------------------------------------
SQL> startup;
ORACLE instance started.
Total System Global Area 658505728 bytes
Fixed Size 1281936 bytes
Variable Size 176160880 bytes
Database Buffers 478150656 bytes
Redo Buffers 2912256 bytes
Database mounted.
Database opened.
Cek datafile system untuk melihat perubahan datafile system
-------------------------------------------------------------------------------------
SQL> select file_name from dba_data_files where tablespace_name='SYSTEM';
FILE_NAME
--------------------------------------------------------------------------------
/export/home/oracle/oradata/system01.dbf
SQL>
Note : Lakukan di mesin test terlebih dahulu sebelum melakukan di production
Cek Blocking Session
Berikut ini query untuk mengecek Blocking Session :
/* Formatted on 10/3/2011 4:59:51 PM (QP5 v5.163.1008.3004) */
SET PAGES 24 LINES 100 FEED ON ECHO OFF TERMOUT ON HEAD ON
COLUMN SID_LOCKER FORMAT A15 TRUNC
COLUMN SID_WAITER FORMAT A15 TRUNC
COLUMN OS_LOCKER FORMAT A15 TRUNC
COLUMN OS_WAITER FORMAT A15 TRUNC
COLUMN LOCKER_SCHEMA FORMAT A15 TRUNC
COLUMN WAITER_SCHEMA FORMAT A15 TRUNC
COLUMN WAITER_PID FORMAT A10
COLUMN LOCKER_PID FORMAT A10
COLUMN SQL_TEXT_WAITER FORMAT A100 WRAP
COLUMN DATABASE NOPRINT NEW_VALUE DATABASE
COLUMN DATUM_ZEIT NOPRINT NEW_VALUE DATUM_ZEIT
SET TERMOUT OFF ECHO OFF FEED OFF
SET TERMOUT ON
TTITLE CENTER 'Current Lock-Waits' SKIP 2
SELECT /*+ ordered
no_merge(L_WAITER)
no_merge(L_LOCKER) use_hash(L_LOCKER)
no_merge(S_WAITER) use_hash(S_WAITER)
no_merge(S_LOCKER) use_hash(S_LOCKER)
use_nl(O)
use_nl(U)
*/
S_LOCKER.SID SID_LOCKER,
S_WAITER.SID SID_WAITER,
S_LOCKER.OSUSER OS_LOCKER,
S_LOCKER.USERNAME LOCKER_SCHEMA,
S_LOCKER.PROCESS LOCKER_PID,
S_WAITER.OSUSER OS_WAITER,
S_WAITER.USERNAME WAITER_SCHEMA,
S_WAITER.PROCESS WAITER_PID,
'Table lock (TM): ' || U.NAME || '.' || O.NAME || ' - Mode held: '
|| DECODE (L_LOCKER.LMODE,
0, 'None', /* same as Monitor */
1, 'Null', /* N */
2, 'Row-S (SS)', /* L */
3, 'Row-X (SX)', /* R */
4, 'Share', /* S */
5, 'S/Row-X (SSX)', /* C */
6, 'Exclusive', /* X */
'???: ' || TO_CHAR (L_LOCKER.LMODE))
|| ' / Mode requested: '
|| DECODE (L_WAITER.REQUEST,
0, 'None', /* same as Monitor */
1, 'Null', /* N */
2, 'Row-S (SS)', /* L */
3, 'Row-X (SX)', /* R */
4, 'Share', /* S */
5, 'S/Row-X (SSX)', /* C */
6, 'Exclusive', /* X */
'???: ' || TO_CHAR (L_WAITER.REQUEST))
SQL_TEXT_WAITER
FROM V$LOCK L_WAITER,
V$LOCK L_LOCKER,
V$SESSION S_WAITER,
V$SESSION S_LOCKER,
OBJ$ O,
USER$ U
WHERE S_WAITER.SID = L_WAITER.SID
AND L_WAITER.TYPE IN ('TM')
AND S_LOCKER.sid = L_LOCKER.sid
AND L_LOCKER.ID1 = L_WAITER.ID1
AND L_WAITER.REQUEST > 0
AND L_LOCKER.LMODE > 0
AND L_WAITER.ADDR != L_LOCKER.ADDR
AND L_WAITER.ID1 = O.OBJ#
AND U.USER# = O.OWNER#
UNION
SELECT /*+ ordered
no_merge(L_WAITER)
no_merge(L_LOCKER) use_hash(L_LOCKER)
no_merge(S_WAITER) use_hash(S_WAITER)
no_merge(S_LOCKER) use_hash(S_LOCKER)
no_merge(L1_WAITER) use_hash(L1_WAITER)
no_merge(O) use_hash(O)
*/
/* now the (usual) row-locks TX */
S_LOCKER.SID SID_LOCKER,
S_WAITER.SID SID_WAITER,
S_LOCKER.OSUSER OS_LOCKER,
S_LOCKER.USERNAME LOCKER_SCHEMA,
S_LOCKER.PROCESS LOCK_PID,
S_WAITER.OSUSER OS_WAITER,
S_WAITER.USERNAME WAITER_SCHEMA,
S_WAITER.PROCESS WAITER_PID,
'TX: ' || O.SQL_TEXT SQL_TEXT_WAITER
FROM V$LOCK L_WAITER,
V$LOCK L_LOCKER,
V$SESSION S_WAITER,
V$SESSION S_LOCKER,
V$_LOCK1 L1_WAITER,
V$OPEN_CURSOR O
WHERE S_WAITER.SID = L_WAITER.SID
AND L_WAITER.TYPE IN ('TX')
AND S_LOCKER.sid = L_LOCKER.sid
AND L_LOCKER.ID1 = L_WAITER.ID1
AND L_WAITER.REQUEST > 0
AND L_LOCKER.LMODE > 0
AND L_WAITER.ADDR != L_LOCKER.ADDR
AND L1_WAITER.LADDR = L_WAITER.ADDR
AND L1_WAITER.KADDR = L_WAITER.KADDR
AND L1_WAITER.SADDR = O.SADDR
AND O.HASH_VALUE = S_WAITER.SQL_HASH_VALUE
/
TTITLE OFF
COLUMN SID_LOCKER CLEAR
COLUMN SID_WAITER CLEAR
COLUMN OS_LOCKER CLEAR
COLUMN OS_WAITER CLEAR
COLUMN LOCKER_SCHEMA CLEAR
COLUMN WAITER_SCHEMA CLEAR
COLUMN WAITER_PID CLEAR
COLUMN LOCKER_PID CLEAR
COLUMN SQL_TEXT_WAITER CLEAR
COLUMN DATABASE CLEAR
COLUMN DATUM_ZEIT CLEAR
Source : Internet
/* Formatted on 10/3/2011 4:59:51 PM (QP5 v5.163.1008.3004) */
SET PAGES 24 LINES 100 FEED ON ECHO OFF TERMOUT ON HEAD ON
COLUMN SID_LOCKER FORMAT A15 TRUNC
COLUMN SID_WAITER FORMAT A15 TRUNC
COLUMN OS_LOCKER FORMAT A15 TRUNC
COLUMN OS_WAITER FORMAT A15 TRUNC
COLUMN LOCKER_SCHEMA FORMAT A15 TRUNC
COLUMN WAITER_SCHEMA FORMAT A15 TRUNC
COLUMN WAITER_PID FORMAT A10
COLUMN LOCKER_PID FORMAT A10
COLUMN SQL_TEXT_WAITER FORMAT A100 WRAP
COLUMN DATABASE NOPRINT NEW_VALUE DATABASE
COLUMN DATUM_ZEIT NOPRINT NEW_VALUE DATUM_ZEIT
SET TERMOUT OFF ECHO OFF FEED OFF
SET TERMOUT ON
TTITLE CENTER 'Current Lock-Waits' SKIP 2
SELECT /*+ ordered
no_merge(L_WAITER)
no_merge(L_LOCKER) use_hash(L_LOCKER)
no_merge(S_WAITER) use_hash(S_WAITER)
no_merge(S_LOCKER) use_hash(S_LOCKER)
use_nl(O)
use_nl(U)
*/
S_LOCKER.SID SID_LOCKER,
S_WAITER.SID SID_WAITER,
S_LOCKER.OSUSER OS_LOCKER,
S_LOCKER.USERNAME LOCKER_SCHEMA,
S_LOCKER.PROCESS LOCKER_PID,
S_WAITER.OSUSER OS_WAITER,
S_WAITER.USERNAME WAITER_SCHEMA,
S_WAITER.PROCESS WAITER_PID,
'Table lock (TM): ' || U.NAME || '.' || O.NAME || ' - Mode held: '
|| DECODE (L_LOCKER.LMODE,
0, 'None', /* same as Monitor */
1, 'Null', /* N */
2, 'Row-S (SS)', /* L */
3, 'Row-X (SX)', /* R */
4, 'Share', /* S */
5, 'S/Row-X (SSX)', /* C */
6, 'Exclusive', /* X */
'???: ' || TO_CHAR (L_LOCKER.LMODE))
|| ' / Mode requested: '
|| DECODE (L_WAITER.REQUEST,
0, 'None', /* same as Monitor */
1, 'Null', /* N */
2, 'Row-S (SS)', /* L */
3, 'Row-X (SX)', /* R */
4, 'Share', /* S */
5, 'S/Row-X (SSX)', /* C */
6, 'Exclusive', /* X */
'???: ' || TO_CHAR (L_WAITER.REQUEST))
SQL_TEXT_WAITER
FROM V$LOCK L_WAITER,
V$LOCK L_LOCKER,
V$SESSION S_WAITER,
V$SESSION S_LOCKER,
OBJ$ O,
USER$ U
WHERE S_WAITER.SID = L_WAITER.SID
AND L_WAITER.TYPE IN ('TM')
AND S_LOCKER.sid = L_LOCKER.sid
AND L_LOCKER.ID1 = L_WAITER.ID1
AND L_WAITER.REQUEST > 0
AND L_LOCKER.LMODE > 0
AND L_WAITER.ADDR != L_LOCKER.ADDR
AND L_WAITER.ID1 = O.OBJ#
AND U.USER# = O.OWNER#
UNION
SELECT /*+ ordered
no_merge(L_WAITER)
no_merge(L_LOCKER) use_hash(L_LOCKER)
no_merge(S_WAITER) use_hash(S_WAITER)
no_merge(S_LOCKER) use_hash(S_LOCKER)
no_merge(L1_WAITER) use_hash(L1_WAITER)
no_merge(O) use_hash(O)
*/
/* now the (usual) row-locks TX */
S_LOCKER.SID SID_LOCKER,
S_WAITER.SID SID_WAITER,
S_LOCKER.OSUSER OS_LOCKER,
S_LOCKER.USERNAME LOCKER_SCHEMA,
S_LOCKER.PROCESS LOCK_PID,
S_WAITER.OSUSER OS_WAITER,
S_WAITER.USERNAME WAITER_SCHEMA,
S_WAITER.PROCESS WAITER_PID,
'TX: ' || O.SQL_TEXT SQL_TEXT_WAITER
FROM V$LOCK L_WAITER,
V$LOCK L_LOCKER,
V$SESSION S_WAITER,
V$SESSION S_LOCKER,
V$_LOCK1 L1_WAITER,
V$OPEN_CURSOR O
WHERE S_WAITER.SID = L_WAITER.SID
AND L_WAITER.TYPE IN ('TX')
AND S_LOCKER.sid = L_LOCKER.sid
AND L_LOCKER.ID1 = L_WAITER.ID1
AND L_WAITER.REQUEST > 0
AND L_LOCKER.LMODE > 0
AND L_WAITER.ADDR != L_LOCKER.ADDR
AND L1_WAITER.LADDR = L_WAITER.ADDR
AND L1_WAITER.KADDR = L_WAITER.KADDR
AND L1_WAITER.SADDR = O.SADDR
AND O.HASH_VALUE = S_WAITER.SQL_HASH_VALUE
/
TTITLE OFF
COLUMN SID_LOCKER CLEAR
COLUMN SID_WAITER CLEAR
COLUMN OS_LOCKER CLEAR
COLUMN OS_WAITER CLEAR
COLUMN LOCKER_SCHEMA CLEAR
COLUMN WAITER_SCHEMA CLEAR
COLUMN WAITER_PID CLEAR
COLUMN LOCKER_PID CLEAR
COLUMN SQL_TEXT_WAITER CLEAR
COLUMN DATABASE CLEAR
COLUMN DATUM_ZEIT CLEAR
Source : Internet
Script untuk membuat Baseline
Di setiap project kita harus mempunyai BASELINE yang bertujuan sebagai backup untuk semua system yang kita handle dalam suatu project . BASELINE ini biasanya dilakukan setiap kali kita akan melakukan sebuah perubahan yang signifikan dalam sebuah system kita harus melaukuan backup BASELINE untuk keperluan Rollback incase aktivitas perubahan mengalami kegagalan. BASELINE ini adalah backup yang bersifat consistent (cold backup) dimana backup seperti ini paling powerfull dan mudah dalam restorisasinya.
Berikut ini contoh BASELINE script untuk untuk applikasi dan database (oracle) yang pernah saya buat di project yang saya handle :
#!/bin/sh
#------------------------------------------------------------------------#
# This script will backup the database and application files #
# #
#------------------------------------------------------------------------#
# Author: Sidnei B & Ida Bagus Enderajana (LCMG) [2006] #
#------------------------------------------------------------------------#
#
#--------------------------------------------------------------------------------------------#
# Environment for the oracle database #
#--------------------------------------------------------------------------------------------#
ORACLE_BASE=/u01/app/oracle; export ORACLE_BASE #
ORACLE_HOME=$ORACLE_BASE/product/9.2.0; export ORACLE_HOME #
ORACLE_SID=DBNAme; export ORACLE_SID #
ORACLE_OWNER=oracle; export ORACLE_OWNER #
ORACLE_TERM=vt220; export ORACLE_TERM #
PATH=$PATH:$ORACLE_HOME/bin:/usr/bin:/opt/bin:/usr/ccs/bin; export PATH #
LD_LIBRARY_PATH=$ORACLE_HOME/lib: export LD_LIBRARY_PATH #
#--------------------------------------------------------------------------------------------#
#------------------------------------------------#
# Append this to the end of the baseline number #
#------------------------------------------------#
BASELINE_NUM=$1 # the front name for the current baseline being taken
BASELINE_DIR=$2 # directory that the files will be stored into
BASELINE_DATE=`date | sed -e "s/:/_/g" | sed -e "s/ /_/g"`
#----------------------------#
# TEST for the entry name #
#----------------------------#
if [ "$BASELINE_NUM" = "" ];then
echo "missing the baseline name ....."
echo "usage:<$0 <baseline_(number)> <backup dir> example:baseline_eventlink.sh baseline_4 /backup"
exit 1
fi
#--------------------------------------#
# TEST for the backup directory entry #
#--------------------------------------#
if [ "$BASELINE_DIR" = "" ];then
echo "missing the backup directory name ....."
echo "usage:<$0 <baseline_(number)> <backup dir> example:baseline_eventlink.sh baseline_4 /backup"
exit 1
fi
BASELINE_FNAME="EL_${BASELINE_NUM}_${BASELINE_DATE}"
COMPLETE_BASE_PATH=$BASELINE_DIR/$BASELINE_FNAME
if [ ! -d $COMPLETE_BASE_PATH ];then
mkdir $COMPLETE_BASE_PATH
fi
#-------------------------------------------------#
# Decide on the TNS #
#-------------------------------------------------#
HOSTNAME=`hostname`
HOST_TYPE=`echo "$HOSTNAME" | grep "nelp"`
if [ "$HOST_TYPE" = "" ];then
#-- Means its the test server --#
TNS_TYPE="DBTEST1"
else
# Means its the production server #
TNS_TYPE="DBPROD"
fi
echo "#-----------------------------------------------#"
echo "# WELCOME TO THE EVENTLINK BASELINE SCRIPT #"
echo "#-----------------------------------------------#"
echo "# The script will be baselineing the following #"
echo "# *- Database files #"
echo "# *- Log files #"
echo "# *- Control files #"
echo "# *- Application Files #"
echo "#-----------------------------------------------#"
#----------------------------------------------------#
# These are the directories that we need to baseline #
#----------------------------------------------------#
echo "## ... creating a baseline apps file with name $BASELINE_FNAME_APPS.tar"
tar -cvf ${COMPLETE_BASE_PATH}/${BASELINE_FNAME}_APPS.tar /data2/eventlink/data/node_packages
#----------------------------------------------------------#
# These are the database files that we need to baseline #
#----------------------------------------------------------#
DATA_DBF=`echo "select file_name from dba_data_files;" | sqlplus baseline/baseline@$TNS_TYPE | grep -i dbf `
DATA_LOG=`echo "select member from v\\$logfile;" | sqlplus baseline/baseline@$TNS_TYPE | grep -i log `
DATA_CTR=`echo "select name from v\\$controlfile;" | sqlplus baseline/baseline@$TNS_TYPE | grep -i ctl `
#------------------------------------------------#
# Shutting down the database server #
#------------------------------------------------#
#
LOOP=1
while [ 1 ]
do
clear
echo "#-------------------------------------------------#"
echo "# Would you like to shutdown the databasae server #"
echo "#-------------------------------------------------#"
echo "# -* ENTER Y for yes #"
echo "# -* ENTER N for no #"
echo "#-------------------------------------------------#"
echo ">>>>: "
read OPT
if [ "$OPT" = "Y" ];then
echo "We are shuting down the database server..."
sleep 10
#------------------------------------
# Dont do this just yet
# $ORACLE_HOME/bin/dbshut
# $ORACLE_HOME/bin/lsnrctl stop
#------------------------------------
break
fi
if [ "$OPT" = "N" ];then
echo "Database server not being shutdown ... exiting script ..."
exit 0
else
echo "INVALID OPTION PLEASE CHOOSE AGAIN"
fi
done
#---------------------------------------------------------------#
# Loop through all the of the database files that have been #
# found from the sql commands and add them into the tar file #
#---------------------------------------------------------------#
FLAG=0
for ORACLE_FILES in `echo "$DATA_DBF $DATA_LOG $DATA_CTR"`
do
if [ $FLAG -eq 0 ];then
echo "----------------------------------------------------------------------"
echo " creating file EL_ORACLE_BACKUP_`hostname`.tar adding + $ORACLE_FILES "
echo "----------------------------------------------------------------------"
tar -cvf ${COMPLETE_BASE_PATH}/EL_ORACLE_BACKUP_`hostname`.tar $ORACLE_FILES
FLAG=1
else
echo "----------------------------------------------------------------------"
echo " creating file EL_ORACLE_BACKUP_`hostname`.tar append + $ORACLE_FILES "
echo "----------------------------------------------------------------------"
tar -rvf ${COMPLETE_BASE_PATH}/EL_ORACLE_BACKUP_`hostname`.tar $ORACLE_FILES
fi
done
echo "-------------------------------------------------"
echo " BASELINE for EL HOST `hostname` ended at `date` "
echo "-------------------------------------------------"
#-----------------#
# End script #
#-----------------#
exit 0
Berikut ini contoh BASELINE script untuk untuk applikasi dan database (oracle) yang pernah saya buat di project yang saya handle :
#!/bin/sh
#------------------------------------------------------------------------#
# This script will backup the database and application files #
# #
#------------------------------------------------------------------------#
# Author: Sidnei B & Ida Bagus Enderajana (LCMG) [2006] #
#------------------------------------------------------------------------#
#
#--------------------------------------------------------------------------------------------#
# Environment for the oracle database #
#--------------------------------------------------------------------------------------------#
ORACLE_BASE=/u01/app/oracle; export ORACLE_BASE #
ORACLE_HOME=$ORACLE_BASE/product/9.2.0; export ORACLE_HOME #
ORACLE_SID=DBNAme; export ORACLE_SID #
ORACLE_OWNER=oracle; export ORACLE_OWNER #
ORACLE_TERM=vt220; export ORACLE_TERM #
PATH=$PATH:$ORACLE_HOME/bin:/usr/bin:/opt/bin:/usr/ccs/bin; export PATH #
LD_LIBRARY_PATH=$ORACLE_HOME/lib: export LD_LIBRARY_PATH #
#--------------------------------------------------------------------------------------------#
#------------------------------------------------#
# Append this to the end of the baseline number #
#------------------------------------------------#
BASELINE_NUM=$1 # the front name for the current baseline being taken
BASELINE_DIR=$2 # directory that the files will be stored into
BASELINE_DATE=`date | sed -e "s/:/_/g" | sed -e "s/ /_/g"`
#----------------------------#
# TEST for the entry name #
#----------------------------#
if [ "$BASELINE_NUM" = "" ];then
echo "missing the baseline name ....."
echo "usage:<$0 <baseline_(number)> <backup dir> example:baseline_eventlink.sh baseline_4 /backup"
exit 1
fi
#--------------------------------------#
# TEST for the backup directory entry #
#--------------------------------------#
if [ "$BASELINE_DIR" = "" ];then
echo "missing the backup directory name ....."
echo "usage:<$0 <baseline_(number)> <backup dir> example:baseline_eventlink.sh baseline_4 /backup"
exit 1
fi
BASELINE_FNAME="EL_${BASELINE_NUM}_${BASELINE_DATE}"
COMPLETE_BASE_PATH=$BASELINE_DIR/$BASELINE_FNAME
if [ ! -d $COMPLETE_BASE_PATH ];then
mkdir $COMPLETE_BASE_PATH
fi
#-------------------------------------------------#
# Decide on the TNS #
#-------------------------------------------------#
HOSTNAME=`hostname`
HOST_TYPE=`echo "$HOSTNAME" | grep "nelp"`
if [ "$HOST_TYPE" = "" ];then
#-- Means its the test server --#
TNS_TYPE="DBTEST1"
else
# Means its the production server #
TNS_TYPE="DBPROD"
fi
echo "#-----------------------------------------------#"
echo "# WELCOME TO THE EVENTLINK BASELINE SCRIPT #"
echo "#-----------------------------------------------#"
echo "# The script will be baselineing the following #"
echo "# *- Database files #"
echo "# *- Log files #"
echo "# *- Control files #"
echo "# *- Application Files #"
echo "#-----------------------------------------------#"
#----------------------------------------------------#
# These are the directories that we need to baseline #
#----------------------------------------------------#
echo "## ... creating a baseline apps file with name $BASELINE_FNAME_APPS.tar"
tar -cvf ${COMPLETE_BASE_PATH}/${BASELINE_FNAME}_APPS.tar /data2/eventlink/data/node_packages
#----------------------------------------------------------#
# These are the database files that we need to baseline #
#----------------------------------------------------------#
DATA_DBF=`echo "select file_name from dba_data_files;" | sqlplus baseline/baseline@$TNS_TYPE | grep -i dbf `
DATA_LOG=`echo "select member from v\\$logfile;" | sqlplus baseline/baseline@$TNS_TYPE | grep -i log `
DATA_CTR=`echo "select name from v\\$controlfile;" | sqlplus baseline/baseline@$TNS_TYPE | grep -i ctl `
#------------------------------------------------#
# Shutting down the database server #
#------------------------------------------------#
#
LOOP=1
while [ 1 ]
do
clear
echo "#-------------------------------------------------#"
echo "# Would you like to shutdown the databasae server #"
echo "#-------------------------------------------------#"
echo "# -* ENTER Y for yes #"
echo "# -* ENTER N for no #"
echo "#-------------------------------------------------#"
echo ">>>>: "
read OPT
if [ "$OPT" = "Y" ];then
echo "We are shuting down the database server..."
sleep 10
#------------------------------------
# Dont do this just yet
# $ORACLE_HOME/bin/dbshut
# $ORACLE_HOME/bin/lsnrctl stop
#------------------------------------
break
fi
if [ "$OPT" = "N" ];then
echo "Database server not being shutdown ... exiting script ..."
exit 0
else
echo "INVALID OPTION PLEASE CHOOSE AGAIN"
fi
done
#---------------------------------------------------------------#
# Loop through all the of the database files that have been #
# found from the sql commands and add them into the tar file #
#---------------------------------------------------------------#
FLAG=0
for ORACLE_FILES in `echo "$DATA_DBF $DATA_LOG $DATA_CTR"`
do
if [ $FLAG -eq 0 ];then
echo "----------------------------------------------------------------------"
echo " creating file EL_ORACLE_BACKUP_`hostname`.tar adding + $ORACLE_FILES "
echo "----------------------------------------------------------------------"
tar -cvf ${COMPLETE_BASE_PATH}/EL_ORACLE_BACKUP_`hostname`.tar $ORACLE_FILES
FLAG=1
else
echo "----------------------------------------------------------------------"
echo " creating file EL_ORACLE_BACKUP_`hostname`.tar append + $ORACLE_FILES "
echo "----------------------------------------------------------------------"
tar -rvf ${COMPLETE_BASE_PATH}/EL_ORACLE_BACKUP_`hostname`.tar $ORACLE_FILES
fi
done
echo "-------------------------------------------------"
echo " BASELINE for EL HOST `hostname` ended at `date` "
echo "-------------------------------------------------"
#-----------------#
# End script #
#-----------------#
exit 0
Tuesday, March 20, 2012
Membuat Tabel partisi berdasarkan tanggal dan sub partisi berdasarkan jam
Saya pernah mendapat permintaan dari manajemen untuk memecahkan masalah query data di tabel yang di partisi dimana mereka ingin bisa melakukan query bukan hanya berdasarkan tanggal saja (dimana tabelnya memamng di partisi berdasarkan tanggal seperti kebanyakan tabel-tabel lainnya), nah saat query di lakukan berdasarkan partisi waktu yang di perlukan cukup lama mengingat data yang sangat besar . untuk mengakali hal ini maka saya buat sub partisi berdasarkan jam untuk meminimalisasi range data yang akan di query sehingga query akan lebih cepat karena di query ke level sub partisi (sub partisi berdasarkan jam ). berikut ini contoh sederhana untuk membuat partisi berdasarkan tanggal dan sub partisi berdasarkan jam :
CREATE TABLE SCOTT.BEBEK99
(
Nomor NUMBER,
Timestamps DATE,
JAM CHAR(2 BYTE)
)
TABLESPACE USERS
PCTUSED 0
PCTFREE 10
INITRANS 1
MAXTRANS 255
STORAGE (
BUFFER_POOL DEFAULT
)
PARTITION BY RANGE (timestamps)
SUBPARTITION BY LIST (JAM)
(
PARTITION P1 VALUES LESS THAN (TO_DATE(' 2011-07-07 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
NOLOGGING
NOCOMPRESS
TABLESPACE USERS
PCTFREE 10
INITRANS 1
MAXTRANS 255
STORAGE (
BUFFER_POOL DEFAULT
)
( SUBPARTITION P1_SP1 VALUES ('00') TABLESPACE USERS,
SUBPARTITION P1_SP2 VALUES ('01') TABLESPACE USERS,
SUBPARTITION P1_SP3 VALUES ('02') TABLESPACE USERS,
SUBPARTITION P1_SP4 VALUES ('03') TABLESPACE USERS,
SUBPARTITION P1_SP5 VALUES ('04') TABLESPACE USERS,
SUBPARTITION P1_SP6 VALUES ('O5') TABLESPACE USERS,
SUBPARTITION P1_SP7 VALUES ('06') TABLESPACE USERS,
SUBPARTITION P1_SP8 VALUES ('07') TABLESPACE USERS,
SUBPARTITION P1_SP9 VALUES ('08') TABLESPACE USERS,
SUBPARTITION P1_SP10 VALUES ('09') TABLESPACE USERS,
SUBPARTITION P1_SP11 VALUES ('10') TABLESPACE USERS,
SUBPARTITION P1_SP12 VALUES ('11') TABLESPACE USERS,
SUBPARTITION P1_SP13 VALUES ('12') TABLESPACE USERS,
SUBPARTITION P1_SP14 VALUES ('13') TABLESPACE USERS,
SUBPARTITION P1_SP15 VALUES ('14') TABLESPACE USERS,
SUBPARTITION P1_SP16 VALUES ('15') TABLESPACE USERS,
SUBPARTITION P1_SP17 VALUES ('16') TABLESPACE USERS,
SUBPARTITION P1_SP18 VALUES ('17') TABLESPACE USERS,
SUBPARTITION P1_SP19 VALUES ('18') TABLESPACE USERS,
SUBPARTITION P1_SP20 VALUES ('19') TABLESPACE USERS,
SUBPARTITION P1_SP21 VALUES ('20') TABLESPACE USERS,
SUBPARTITION P1_SP22 VALUES ('21') TABLESPACE USERS,
SUBPARTITION P1_SP23 VALUES ('22') TABLESPACE USERS,
SUBPARTITION P1_SP24 VALUES ('23') TABLESPACE USERS ),
PARTITION P2 VALUES LESS THAN (TO_DATE(' 2011-07-08 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
NOLOGGING
NOCOMPRESS
TABLESPACE USERS
PCTFREE 10
INITRANS 1
MAXTRANS 255
STORAGE (
BUFFER_POOL DEFAULT
)
( SUBPARTITION P2_SP1 VALUES ('00') TABLESPACE USERS,
SUBPARTITION P2_SP2 VALUES ('01') TABLESPACE USERS,
SUBPARTITION P2_SP3 VALUES ('02') TABLESPACE USERS,
SUBPARTITION P2_SP4 VALUES ('03') TABLESPACE USERS,
SUBPARTITION P2_SP5 VALUES ('04') TABLESPACE USERS,
SUBPARTITION P2_SP6 VALUES ('O5') TABLESPACE USERS,
SUBPARTITION P2_SP7 VALUES ('06') TABLESPACE USERS,
SUBPARTITION P2_SP8 VALUES ('07') TABLESPACE USERS,
SUBPARTITION P2_SP9 VALUES ('08') TABLESPACE USERS,
SUBPARTITION P2_SP10 VALUES ('09') TABLESPACE USERS,
SUBPARTITION P2_SP11 VALUES ('10') TABLESPACE USERS,
SUBPARTITION P2_SP12 VALUES ('11') TABLESPACE USERS,
SUBPARTITION P2_SP13 VALUES ('12') TABLESPACE USERS,
SUBPARTITION P2_SP14 VALUES ('13') TABLESPACE USERS,
SUBPARTITION P2_SP15 VALUES ('14') TABLESPACE USERS,
SUBPARTITION P2_SP16 VALUES ('15') TABLESPACE USERS,
SUBPARTITION P2_SP17 VALUES ('16') TABLESPACE USERS,
SUBPARTITION P2_SP18 VALUES ('17') TABLESPACE USERS,
SUBPARTITION P2_SP19 VALUES ('18') TABLESPACE USERS,
SUBPARTITION P2_SP20 VALUES ('19') TABLESPACE USERS,
SUBPARTITION P2_SP21 VALUES ('20') TABLESPACE USERS,
SUBPARTITION P2_SP22 VALUES ('21') TABLESPACE USERS,
SUBPARTITION P2_SP23 VALUES ('22') TABLESPACE USERS,
SUBPARTITION P2_SP24 VALUES ('23') TABLESPACE USERS )
)
NOCOMPRESS
NOCACHE
NOPARALLEL
MONITORING;
Cara untuk test tabelnya
-----------------------------------------------
insert into bebek99 values (1,sysdate,convert_timestamp)
select * from bebek99
select * from bebek99 partition (p1)
select * from bebek99 subpartition (p1_sp16)
CREATE TABLE SCOTT.BEBEK99
(
Nomor NUMBER,
Timestamps DATE,
JAM CHAR(2 BYTE)
)
TABLESPACE USERS
PCTUSED 0
PCTFREE 10
INITRANS 1
MAXTRANS 255
STORAGE (
BUFFER_POOL DEFAULT
)
PARTITION BY RANGE (timestamps)
SUBPARTITION BY LIST (JAM)
(
PARTITION P1 VALUES LESS THAN (TO_DATE(' 2011-07-07 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
NOLOGGING
NOCOMPRESS
TABLESPACE USERS
PCTFREE 10
INITRANS 1
MAXTRANS 255
STORAGE (
BUFFER_POOL DEFAULT
)
( SUBPARTITION P1_SP1 VALUES ('00') TABLESPACE USERS,
SUBPARTITION P1_SP2 VALUES ('01') TABLESPACE USERS,
SUBPARTITION P1_SP3 VALUES ('02') TABLESPACE USERS,
SUBPARTITION P1_SP4 VALUES ('03') TABLESPACE USERS,
SUBPARTITION P1_SP5 VALUES ('04') TABLESPACE USERS,
SUBPARTITION P1_SP6 VALUES ('O5') TABLESPACE USERS,
SUBPARTITION P1_SP7 VALUES ('06') TABLESPACE USERS,
SUBPARTITION P1_SP8 VALUES ('07') TABLESPACE USERS,
SUBPARTITION P1_SP9 VALUES ('08') TABLESPACE USERS,
SUBPARTITION P1_SP10 VALUES ('09') TABLESPACE USERS,
SUBPARTITION P1_SP11 VALUES ('10') TABLESPACE USERS,
SUBPARTITION P1_SP12 VALUES ('11') TABLESPACE USERS,
SUBPARTITION P1_SP13 VALUES ('12') TABLESPACE USERS,
SUBPARTITION P1_SP14 VALUES ('13') TABLESPACE USERS,
SUBPARTITION P1_SP15 VALUES ('14') TABLESPACE USERS,
SUBPARTITION P1_SP16 VALUES ('15') TABLESPACE USERS,
SUBPARTITION P1_SP17 VALUES ('16') TABLESPACE USERS,
SUBPARTITION P1_SP18 VALUES ('17') TABLESPACE USERS,
SUBPARTITION P1_SP19 VALUES ('18') TABLESPACE USERS,
SUBPARTITION P1_SP20 VALUES ('19') TABLESPACE USERS,
SUBPARTITION P1_SP21 VALUES ('20') TABLESPACE USERS,
SUBPARTITION P1_SP22 VALUES ('21') TABLESPACE USERS,
SUBPARTITION P1_SP23 VALUES ('22') TABLESPACE USERS,
SUBPARTITION P1_SP24 VALUES ('23') TABLESPACE USERS ),
PARTITION P2 VALUES LESS THAN (TO_DATE(' 2011-07-08 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
NOLOGGING
NOCOMPRESS
TABLESPACE USERS
PCTFREE 10
INITRANS 1
MAXTRANS 255
STORAGE (
BUFFER_POOL DEFAULT
)
( SUBPARTITION P2_SP1 VALUES ('00') TABLESPACE USERS,
SUBPARTITION P2_SP2 VALUES ('01') TABLESPACE USERS,
SUBPARTITION P2_SP3 VALUES ('02') TABLESPACE USERS,
SUBPARTITION P2_SP4 VALUES ('03') TABLESPACE USERS,
SUBPARTITION P2_SP5 VALUES ('04') TABLESPACE USERS,
SUBPARTITION P2_SP6 VALUES ('O5') TABLESPACE USERS,
SUBPARTITION P2_SP7 VALUES ('06') TABLESPACE USERS,
SUBPARTITION P2_SP8 VALUES ('07') TABLESPACE USERS,
SUBPARTITION P2_SP9 VALUES ('08') TABLESPACE USERS,
SUBPARTITION P2_SP10 VALUES ('09') TABLESPACE USERS,
SUBPARTITION P2_SP11 VALUES ('10') TABLESPACE USERS,
SUBPARTITION P2_SP12 VALUES ('11') TABLESPACE USERS,
SUBPARTITION P2_SP13 VALUES ('12') TABLESPACE USERS,
SUBPARTITION P2_SP14 VALUES ('13') TABLESPACE USERS,
SUBPARTITION P2_SP15 VALUES ('14') TABLESPACE USERS,
SUBPARTITION P2_SP16 VALUES ('15') TABLESPACE USERS,
SUBPARTITION P2_SP17 VALUES ('16') TABLESPACE USERS,
SUBPARTITION P2_SP18 VALUES ('17') TABLESPACE USERS,
SUBPARTITION P2_SP19 VALUES ('18') TABLESPACE USERS,
SUBPARTITION P2_SP20 VALUES ('19') TABLESPACE USERS,
SUBPARTITION P2_SP21 VALUES ('20') TABLESPACE USERS,
SUBPARTITION P2_SP22 VALUES ('21') TABLESPACE USERS,
SUBPARTITION P2_SP23 VALUES ('22') TABLESPACE USERS,
SUBPARTITION P2_SP24 VALUES ('23') TABLESPACE USERS )
)
NOCOMPRESS
NOCACHE
NOPARALLEL
MONITORING;
Cara untuk test tabelnya
-----------------------------------------------
insert into bebek99 values (1,sysdate,convert_timestamp)
select * from bebek99
select * from bebek99 partition (p1)
select * from bebek99 subpartition (p1_sp16)
Subscribe to:
Posts (Atom)