Tuesday, July 28, 2009

Change Auto tune status manually on 11g

berikut adalah cara untuk menonaktifkan dan mengaktifkan auto tune management di
11g secara manual

execute script berikut untuk mendapatkan list auto task window :

/* Formatted on 2009/07/28 15:47 (Formatter Plus v4.8.8) */
SELECT a.window_name, a.window_active, a.autotask_status, a.optimizer_stats,
a.segment_advisor, a.sql_tune_advisor, a.health_monitor
FROM dba_autotask_window_clients a;

MONDAY_WINDOW
TUESDAY_WINDOW
WEDNESDAY_WINDOW
THURSDAY_WINDOW
FRIDAY_WINDOW
SATURDAY_WINDOW
SUNDAY_WINDOW

BEGIN
dbms_auto_task_admin.enable(client_name => 'auto space advisor', operation => NULL, window_name => 'SATURDAY_WINDOW');
END;

BEGIN
dbms_auto_task_admin.disable(client_name => 'auto optimizer stats collection', operation => NULL, window_name => 'THURSDAY_WINDOW');
END;


Sunday, July 26, 2009

managed archived log at standby database, shipped status

Agar disk tidak penuh di primary karean archivelog yang sudah di kirim ke standby, maka
anggap saja archive log tersebut sudah obsolete. Oleh karena itu archive tersebut bisa di delete atau di moving
ke tempat lain tapi khusus archive dengan status sudah dikirim ke standby. dengan menggunkan shell
script berikut :

[oracle@ora11g script]$ more checkarchive

export ORACLE_HOME=/oracle/apps/product/11.1.0/db_1
export ORACLE_SID=ORADEV
/oracle/apps/product/11.1.0/db_1/bin/sqlplus -s "/ as sysdba" '<<' EOF
set term off
set head off
set trims on
set pages 0
set feedback off
set linesize 1000
spool /oracle/home/script/checkarchtransport.txt

select name from v\$archived_log
where name is not null
and dest_id = 1
and sequence# in (
select sequence# from v\$archived_log
where name is not null
and dest_id = 2);

spool off
exit
EOF

while read i; do rm -rf "$i" ; sleep 2; done '<' checkarchtransport.txt

mudah2an bermanfaat :)

Installation RAC on Enterprise Linux 4 at VMWARE

Dear All,
Bagi yang belum tau dan mau belajar tentang installation Oracle Real Application Cluster (RAC) bisa
buat simulasinya dan real dengan menggunakan vmware + OS nya Linux Redhat EL4 dengan panduan
nya ada di link berikut :

http://sr3.telmos.ru/dsvolk/wmimages/linux_x86/10g/RAC/install_guide_oracle_rac10g_El4.pdf

tinggal download dan ikutin sajah step2 nya, kemudian kalo mau install di PC maka sediakan sajah memory
yang cukup, kisaran 3 GB lah idealnya.... selamat mencoba !!! :)

Tuesday, July 21, 2009

Oracle Application server clustering middletier

berikut adalah beberapa script yang mungkin berguna untuk memanage cluster
oracle application server :

listinstances
listclusters
joincluster -cl clisterApps -i Webapps1.web-ora-1.com
joincluster -cl clusterApps -i Webapps1.web-ora-1.com
start -cl clusterApps


Friday, July 10, 2009

After Installation Oracle Apps 11i

Setelah installation oracle apps, apa yang harus dilakukan :
http://orapps11i.com:8000
password standarnya :
sysadmin/sysadmin
operation/welcome


$ bash
$ Cd /u01/apps11i/prodappl/
/u01/apps11i/prodappl/APPSPROD_orapps11i.env
$ . ./.APPSPROD_orapps11i.env
. APPSPROD_orapps11i.env
adadmin

matiin
cd $COMMON_TOP
cd admin
cd scripts
adstpall.sh apps/apps
PROD_orapps11i.env
u01/oracle/proddb/9.2.0/appsutil/scripts

[oracle@orapps11i PROD_orapps11i]$ addlnctl.sh stop PROD (listener)
addbctl.sh stop PROD
[oracle@orapps11i PROD_orapps11i]$ addbctl.sh stop immediate

Tuesday, July 7, 2009

Patch and Upgrade DB 11.1.0.6 to 11.1.0.7

Manual patch dan upgrade tanpa dbua 11g:
1. set ORACLE_BASE, ORACLE_HOME, ORACLE_SID
2. matikan
listener (lsnrctl stop),
console (emctl stop dbconsole),
database (shutdown immediate)
3. ./runInstaller
4. check opatch
$ORACLE_HOME/Opatch/opatch lsinventory -detail
5.
SQL> STARTUP UPGRADE
SQL> SPOOL upgrade_info.log
SQL> @?/rdbms/admin/utlu111i.sql
SQL> SPOOL OFF

6.
SQL> STARTUP UPGRADE
SQL> SPOOL patch.log
SQL> @?/rdbms/admin/catupgrd.sql
SQL> SPOOL OFF

7. check error di file patch.log
8.
SQL> SHUTDOWN IMMEDIATE
SQL> STARTUP
9. recompile
SQL> @?/rdbms/admin/utlrp.sql
10. check status registry (harus valid semua)
SQL> SELECT COMP_NAME, VERSION, STATUS FROM SYS.DBA_REGISTRY;


Script to delete applied archived log on standby

di standby database kadang disk penuh oleh archive log, bagaimana kita memanage nya
1. keep archive log yang belum di applied
2. delete archive yang sudah di applied
kalau kita dalam environtment logical standby database dapat menggunakan package dari
oracle tetapi jika kita dalam lingkungan standby database mau tidak mau harus manual atau bisa saja dengan menggunakan script sebagai berikut :

#!/usr/bin/ksh
ARCH_DIR=/archive/orcl

#take the log number to be applied
LogNo=`tail -30000 /oracle/app/oracle/product/10.2.0/admin/orcl/bdump/alert* | grep "Media Recovery Log" | cut -d " " -f 4 | cut -d "_" -f 5 | tail -1 `
echo "Oracle applied LogNo is $LogNo"

#extract 10
let SecLogNo=${LogNo}-10
echo "new backlog log No: $SecLogNo"

#delete small numbers from this in arch dir
cd $ARCH_DIR
for i in `ls *.arc`
do

Newi=`echo $i | cut -d "_" -f 4`

if [ $Newi -lt $SecLogNo ] ; then
echo "$i to be deleted..."
rm $i
fi
done


Sunday, July 5, 2009

PL/SQL Best Practices

source Quest Knowledge Expert :
1. Iterative Processing BP - Loop Exit
jangan menggunakan exit atau return untuk keluar dari sebuah FOR loop
jangan menggunakan exit untuk statement WHILE loop

Friday, July 3, 2009

Problem datafile at standby database

untuk datafile yang corrupt di standby database misal file_id 43 currupt
soluasinya :
1. alter database recover automatic standby database allow 1 corruption;
(lihat di alert log)

di primary
2. select file_name, tablespace_name from dba_data_files where file_id= 43
3. alter tablespace test begin backup;

copy file dengan file_id
43 ke standby database

primary :
4.
alter tablespace test end backup;

standby
5.
alter database recover managed standby database disconnect from session;

jika error nya karena

ora-600 [3o20]
1. di standby
tambahkan hidden parameter _disable_recoverable_recovery=true
2. apply patch
patch number 7197445


Thursday, July 2, 2009

RMAN Backup Script

Lupa!!! .... lupa .. lupa .. lupa .. lupa .. lupa lagi script rman nyaa (kuburan mode=on), biar gak lupa gw posting ah di blog :

run
{
set controlfile autobackup format for device type disk to '/oracle/autobackup/ctlf_%F';
configure controlfile autobackup on;
CONFIGURE COMPRESSION ALGORITHM 'ZLIB';
CONFIGURE DEVICE TYPE DISK PARALLELISM 2 BACKUP TYPE TO COMPRESSED BACKUPSET;
crosscheck backup;
delete noprompt expired backup;
allocate channel c1 type disk;
allocate channel c2 type disk;
backup database format='/oracle/backup/df_%T_%U' include current controlfile;
sql 'alter system archive log current';
backup archivelog all format '/oracle/backup/arch_%T_%U';
delete force copy of archivelog all completed before 'sysdate-1';
backup current controlfile format '/oracle/backup/ctlf_%T_%U';
release channel c1;
release channel c2;
}

kalo
CONFIGURE COMPRESSION ALGORITHM 'ZLIB';
CONFIGURE DEVICE TYPE DISK PARALLELISM 2 BACKUP TYPE TO COMPRESSED BACKUPSET;
itu untuk di 11g nah kalo di 10g ato dibawahnya
ilangin aja configure diatasnya.
configure di set supaya hasil backup nya bisa lebih ngehemat space(disk)/tape

beberapa check dan script yang digunakan dalam Recovery Manager (RMAN) :
RMAN> list backup by file;
RMAN> report obsolete;
RMAN> list incarnation;
RMAN> show all;
RMAN> list backup;
RMAN> list archivelog all;

Auto Startup Database and Listener Oracle

Jika server mati mendadak (kayak jantungan aja) bisa karena crash hardware
atau mendadak reboot sendiri (biasanya overload resource utilisasi) , kemudian
server nyala kembali atau dinyalakan. Di dalam nya ada database oracle (single instance)untuk dapat meng UP kan database biasanya manual oleh seseorang (?)
coba kalo gak ada yang bisa UP kan database (capee dech!!!). Ya dah mari kita
buat auto startup saja database, CARANYA (Jreeenggg) :

login sebagai root :

rubah oratab
1.
# vi /etc/oratab
ORADEV:/oracle/apps/product/11.1.0/db_1:N
menjadi
ORADEV:/oracle/apps/product/11.1.0/db_1:Y

2. masuk ke init.d (biasanya service OS yang auto restart (CMIIW) :D
# cd /etc/init.d/

buat file (contoh nama file oraclestart)

# vi oraclestart

isi dengan script di bawah

Note :  line script di bawah harus selalu disertakan!

#!/bin/bash
#
# oracle Init file for starting and stopping
# Oracle Database. Script is valid for 10g and 11g versions.
#
# chkconfig: 35 80 30
# description: Oracle Database startup script





#!/bin/bash
#
# oracle Init file for starting and stopping
# Oracle Database. Script is valid for 10g and 11g versions.
#
# chkconfig: 35 80 30
# description: Oracle Database startup script

# Source function library.

. /etc/rc.d/init.d/functions

ORACLE_OWNER="oracle"
ORACLE_HOME="ORACLE_HOME=/oracle/apps/product/11.1.0/db_1"

case "$1" in
start)
echo -n $"Starting Oracle DB:"
su - $ORACLE_OWNER -c "$ORACLE_HOME/bin/dbstart $ORACLE_HOME"
echo "OK"
;;
stop)
echo -n $"Stopping Oracle DB:"
su - $ORACLE_OWNER -c "$ORACLE_HOME/bin/dbshut $ORACLE_HOME"
echo "OK"
;;
*)
echo $"Usage: $0 {start|stop}"
esac

3. rubah privilege file oraclestart
chmod 750 /etc/init.d/oraclestart

4. konfigurasi execution ke yang spesifik
chkconfig --add oraclestart --level 0356

5. ujicoba
kalo di linux pada saat server process lihat service/process apa saja
yang di startup
nah pasti dech ada si ORACLESTART

jadi deeech, so dba bisa tidur dengan nyenyak.. besoknya baru investigasi sambil
nanya nanya mbak meta (metalink)

Wednesday, July 1, 2009

Check status recovery (database able open resetlogs)

terkadang kalau kita melakukan restore dan incomplete recovery
misal recovery until cancel bagaimana cara check nya bahwa hasil recover nya itu
dapat diopen resetlogs, bagaimana caranya ngecheck ... ya begindang :

set pagesize 20000
set linesize 180
set pause off
set serveroutput on
set feedback on
set echo on
set numformat 999999999999999
Spool recovery_info.txt
select substr(name, 1, 50), status from v$datafile;
select substr(name,1,40), recover, fuzzy, checkpoint_change# from v$datafile_header; select GROUP#,substr(member,1,60) from v$logfile;
select * from v$recover_file;

select distinct status from v$backup; (NO)
select hxfil FILENUMBER,fhsta STATUS,fhscn SCN,fhrba_Seq SEQUENCE
from x$kcvfh; ( harus 1 row )
select distinct (fuzzy) from v$datafile_header; (fuzzy harus NO)

spool off
exit


select distinct status from v$backup; (NO)
select hxfil FILENUMBER,fhsta STATUS,fhscn SCN,fhrba_Seq SEQUENCE from x$kcvfh; ( harus 1 row )
select distinct (fuzzy) from v$datafile_header; (fuzzy harus NO)