Oracle Dataguard on Standart Edition

=================
|| Primary ||
=================

1. Disable db_recovery_file_dest = ‘’ and Check parameter db_recovery_file_dest
SQL> ALTER SYSTEM SET db_recovery_file_dest=’’ SCOPE=BOTH;
SQL> sho parameter db_recovery_file_dest;

2. Alter log_archive_dest and Check parameter log_archive_dest
SQL> ALTER SYSTEM SET log_archive_dest=’/oradata/archive’ SCOPE=BOTH;
SQL> sho parameter log_archive_dest

3. Check Archived log
SQL> ARCHIVE LOG LIST;

4. Switch log file
SQL> ALTER SYSTEM SWITCH LOGFILE;

5. Check Archived log and file
SQL> ARCHIVE LOG LIST;
$ ls /oradata/archive

6. Backup Database and Archived.log (RMAN):
cd $ORACLE_HOME/bin
$ ./rman target

RMAN> CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT ‘/oradata/backup/%d_%T_%u_s%s_p%p’

RMAN> run {
allocate channel c1 type disk;
allocate channel c2 type disk;
allocate channel c3 type disk;
backup incremental level 0 tag full_backup
format ‘/oradata/backup/%d_%T_%u_s%s_p%p’ database;
backup incremental level 0 tag full_backup
format ‘/oradata/backup/%d_%T_%u_s%s_p%p’ (current controlfile);
backup archivelog all
format ‘/oradata/backup/%d_%T_%u_s%s_p%p’ delete input;
delete obsolete;
}

8. Switch log file
SQL> ALTER SYSTEM SWITCH LOGFILE;

9. Check Archived log and file
SQL> ARCHIVE LOG LIST;
$ ls /oradata/archive

10. Create some transaction to create archive
SQL> grant connect, resource to test identified by test;
SQL> create table test.testing as select * from dba_objects;
SQL> select count(1) from test.testing;

11. Switch log file
SQL> ALTER SYSTEM SWITCH LOGFILE;

12. Check Archived log and file
SQL> ARCHIVE LOG LIST;
$ ls /oradata/archive

13. RUN {
ALLOCATE CHANNEL d1 DEVICE TYPE DISK;
BACKUP DATAFILE 1 FORMAT ‘/u01/backups/%U’, ‘/u02/backups/%U’;
}

13. Create Standby controlfile from primary database:
SQL> ALTER DATABASE CREATE STANDBY CONTROLFILE AS ‘/home/oracle/ctlstdby.ctl’;

14. Create pfile from spfile;
-------------------------------------------------------------------------
13. Shutdown database

14. Copy initprotecal.ora, orapwprotecal, control standby(ctlstdby.ctl), onlinelog, datafile, tempfile

=================
|| Standby ||
=================

1. Edit pfile, change parameter *.control_files to *.control_files=’/u02/oradata/ctlfile/ctlstdby.ctl’

2. Check validation of pfile parameter

3. Startup nomount pfile=’$ORACLE_HOME/dbs/initprotecal.ora’

4. Startup mount
SQL> alter database mount standby database;

=================
|| crontab ||
=================

on primary :
----------------------------------------------------------------------------------
$ cd /home/oracle

$ vi move_standby.sh =>
rsync -e ssh -Pazv /oradata/flash_recovery_area/protecal/archivelog/ oracle@stby-db:/oradata/flash_recovery_area/protecal/archivelog/
rsync -e ssh -Pazv /oradata/archive/ oracle@stby-db:/oradata/archive/

$ chmod 775 move_standby.sh
$ crontab -e => */3 * * * * /home/oracle/move_standby.sh
$ crontab -l

on standby :
----------------------------------------------------------------------------------
$ cd /home/oracle

$ vi manual_standby.sh =>
ORACLE_SID=protecal
ORACLE_BASE=/oracle/app
ORACLE_HOME=/oracle/app/product/ora10.2
export LD_LIBRARY_PATH=$ORACLE_HOME/lib
export TNS_ADMIN=$ORACLE_HOME/network/admin
export PATH=$PATH:$HOME/bin:$ORACLE_HOME/bin:/sbin:.
sqlplus -s “/nolog” <<-EOF
conn /as sysdba
recover standby database;
AUTO

$ chmod 775 manual_standby.sh
$ crontab -e => */5 * * * * /home/oracle/manual_standby.sh
$ crontab -l

purging mail (oracle and root) => as root on primary and standby
----------------------------------------------------------------------------------
# cd /var/spool/mail
# touch oracle.new
# vi mail_oracle.sh => cp --reply=yes /var/spool/mail/oracle.new /var/spool/mail/oracle
# chmod 775 mail_oracle.sh
# crontab -e => */10 * * * * /var/spool/mail/mail_oracle.sh
# crontab -l

*/2 * * * * rsync -e ssh -Pazv /home/oracle/test/ oracle@rumah:/home/oracle/hapus/

A. Setting ssh and scp on primary and standby
B. Configure Primary and standby

note: nama mesin dan domain dah gw rubah..tolong disesuaikan dengan mesin anda

node 2 :ssh-keygen -t rsa
ssh-keygen -t dsa

node 1 :ssh-node_1 cat /oracle/.ssh/id_rsa.pub

Node 2
---------
[oracle@rumah ~]$ ssh-keygen -t rsa
[oracle@rumah ~]$ ssh-keygen -t dsa

Node 1
---------
[oracle@vmwareas ~]$ ssh-keygen -t rsa
[oracle@vmwareas ~]$ ssh-keygen -t dsa

Node 1
----------
[oracle@vmwareas ~]$ ssh node_1 cat /home/oracle/.ssh/id_rsa.pub >> /home/oracle/.ssh/authorized_keys

[oracle@vmwareas ~]$ ssh node_1 cat /home/oracle/.ssh/id_dsa.pub >> /home/oracle/.ssh/authorized_keys

Node 2
---------
[oracle@vmwareas ~]$ ssh node_2 cat /home/oracle/.ssh/id_rsa.pub >> /home/oracle/.ssh/authorized_keys

[oracle@vmwareas ~]$ ssh node_2 cat /home/oracle/.ssh/id_dsa.pub >> /home/oracle/.ssh/authorized_keys

Node 1
----------
[oracle@vmwareas ~]$ ssh vmwareas cat /home/oracle/.ssh/id_rsa.pub >> /home/oracle/.ssh/authorized_keys

[oracle@vmwareas ~]$ ssh vmwareas cat /home/oracle/.ssh/id_dsa.pub >> /home/oracle/.ssh/authorized_keys

[oracle@vmwareas ~]$ ssh rumah cat /home/oracle/.ssh/id_rsa.pub >> /home/oracle/.ssh/authorized_keys

[oracle@vmwareas ~]$ ssh rumah cat /home/oracle/.ssh/id_dsa.pub >> /home/oracle/.ssh/authorized_keys

Node 2
----------
[oracle@vmwareas ~]$ ssh vmwareas cat /home/oracle/.ssh/id_rsa.pub >> /home/oracle/.ssh/authorized_keys

[oracle@vmwareas ~]$ ssh vmwareas cat /home/oracle/.ssh/id_dsa.pub >> /home/oracle/.ssh/authorized_keys

Instalasi 10g di HP-UX Itanium

pemeriksaan memory dan swap

# /usr/contrib/bin/machinfo | grep -i Memory
# /usr/sbin/swapinfo -a

pemeriksaan kapasitas direktori /tmp

# bdf /tmp

untuk melihat sisa/penggunaan hardisk

# bdf

Memeriksa konfigurasi jaringan

- Konfigurasi name resolution
1.memeriksa file /etc/hosts untuk di samakan dengan file nsswitch.conf
# cat /etc/nsswitch.conf | grep hosts

2.verifikasi hostname
# hostname

3.verifikasi nama domain
# domainname

4.verifikasi file host memiliki nama host yang qualified
# cat /etc/hosts | grep `eval hostname`

example output:
192.168.100.16 myhost.us.mycompany.com myhost
127.0.0.1 localhost localhost.localdomain

jika file tidak berisi nama host yang benar, silahkan untuk dirubah

memperbaharui parameter kernel
-menggunakan kcweb application
# /usr/sbin/kcweb -F

membuat group, user, dan direktori
- grup dan user yang akan dibuat :
grup oracle inventory(oinstall)
grup OSDBA(dba)
owner software oracle(oracle)
user tanpa privileged(nobody)

1.cek keberadaan grup pada sistem
# more /var/opt/oracle/oraInst.loc

jika file tersebut ada dan menghasilkan output :

inventory_loc=/u01/app/oracle/oraInventory
inst_group=oinstall

maka oracle inventori sudah memiliki grup oinstall

2.jika diperlukan, buat grup oinstall
# /usr/sbin/groupadd oinstall

3.buat grup OSDBA jika belum ada
# /usr/sbin/groupadd dba

4.periksa apakah user oracle sudah terbentuk dan memiliki grup yang benar
# id oracle

jika user oracle sudah ada dan menampilkan informasi seperti dibawah ini, maka oinstall adalah grup utama dan dba adalah
grup kedua dari user oracle

uid=440(oracle) gid=200(oinstall) groups=201(dba),202(oper)

5. - jika user oracle sudah ada tapi grup utama bukan oinstal atau bukan member dari grup dba.
# /usr/sbin/usermod -g oinstall -G dba oracle
- jika user oracle belum ada
# /usr/sbin/useradd -g oinstall -G dba oracle

6.buat password untuk user oracle
#passwd oracle

7.melihat keberadaan user nobody
# id nobody

jika tidak ada user nobody, maka buat user nobody
# /usr/sbin/useradd nobody

- direktori yang akan dibuat :
Oracle base directory
Oracle inventory directory
Oracle home directory

membuat oracle base directory dan menspesifikasikan owner, grup dan permision
# mkdir -p /mount_point/app/oracle_sw_owner
# chown -R oracle:oinstall /mount_point/app/oracle_sw_owner
# chmod -R 775 /mount_point/app/oracle_sw_owner

jika mount point /u01 dan user untuk oracle software owner adalah oracle, maka oracle base directory adalah

/u01/app/oracle

tambahan jika ingin menempatkan flash recovery area dan file data pada mount point yang berbeda

Database file directory:

# mkdir /mount_point/oradata
# chown oracle:oinstall /mount_point/oradata
# chmod 775 /mount_point/oradata

Recovery file directory (flash recovery area):

# mkdir /mount_point/flash_recovery_area
# chown oracle:oinstall /mount_point/flash_recovery_area
# chmod 775 /mount_point/flash_recovery_area

Mengkonfigurasi/set environment user oracle

1. Buka X terminal baru
2. setting agar aplikasi X window dapat berjalan dalam sistem

$ xhost fully_qualified_remote_host_name atau xhost +

sebagai contoh:

$ xhost somehost.us.acme.com

3. jika belum login kedalam sistem dimana akan diinstall oracle maka loginlah dengan user oracle
4. jika tidak login dengan user oracle, maka rubahlah menjadi user oracle
$ su - oracle

5. untuk melihat shell yang aktif
$ echo $SHELL

6. buka file startup shell user oracle

- C shell (csh or tcsh):
% vi .login

- Bash shell di redhat
$ vi .bash_profile

- Bourne shell atau korn shell
$ vi .profile

7. edit file untuk mendefinisikan mode pembuatan file default dan setting ORACLE_BASE dan ORACLE_SID environment variable
umask 022
ORACLE_SID=KPPDB
ORACLE_BASE=/oracle10gr2/app
ORACLE_HOME=$ORACLE_BASE/product/10.2
export ORACLE_SID ORACLE_BASE ORACLE_HOME

8. simpan file dan keluar dari editor

9.untuk menjalankan script startup shell
- C shell (csh or tcsh):
% source ./.login

- Bash shell di redhat
$ . ./.bash_profile

- Bourne shell atau korn shell
$ . ./.profile

10.jika tidak melakukan instalasi pada sistem lokal, dan untuk membuat aplikasi X berjalan di sistem lokal
- Bourne, bash atau korn shell:

$ DISPLAY=local_host:0.0 ; export DISPLAY

- C shell:

% setenv DISPLAY local_host:0.0

11.jika direktori /tmp kurang dari 400 MB
- periksa dengan bdf untuk melihat mount point yang ada.
- buat direktori temporary dan set permissionnya
$ su - root
# mkdir /mount_point/tmp
# chmod a+wr /mount_point/tmp
# exit
- setting tmp evironment variables

Bourne, Bash, or Korn shell:

$ TMP=/mount_point/tmp
$ TMPDIR=/mount_point/tmp
$ export TMP TMPDIR

C shell:

% setenv TMP /mount_point/tmp
% setenv TMPDIR /mount_point/tmp

12.untuk memastikan semua environment diset dengan benar, jalankan perintah

$ umask
$ env | more

pastikan tampilan yang dihasilkan adalah nilai 22,022,atau 0022 dan semua environment variables memiliki nilai yang
benar.

Mounting CD/DVD

1. jalankan dari user root
$ su - root

2. unmount DVD
# /usr/sbin/umount /SD_DVD
/SD_DVD adalah contoh mount untuk DVD

3. masukkan CD pada disc drive, jalankan perintah
# /usr/sbin/mount -F cdfs -o rr /dev/dsk/cxtydz /SD_DVD

contoh, /SD_DVD adalah mount point untuk CD dan /dev/dsk/cxtydz adalah nama device dari disc drive,
sebagai contoh /dev/dsk/c0t2d0

4. Jika Oracle universal installer menampilkan kotak dialog lokasi disk, masukkan path mount point disk
contoh /SD_DVD

Instalasi

1. jika dari DVD
$ /mount_point/db/runInstaller

2. jika dari hard disk, masuk ke path software dan jalankan
$ ./runInstaller

Syncronize archive log

Artikel ini berhubungan dengan artikel gw yang mengenai merubah ukuran archived log pada database, apabila kita telah membackup archived log maka kita perlu menghapusnya secara fisik pada server kita, dan setelah kita menghapus file archive secara fisik dari server/OS maka pada database harus dilakukan sinkronisasi?.

Langkah-langkah teknis :

[root@alt2007 archivelog]# pwd
/app/oracle/flash_recovery_area/MCC2007/archivelog

[root@alt2007 archivelog]# ls -al
total 44
drwxr-x--- 11 oracle oinstall 4096 Feb 15 00:53 .
drwxr----- 5 oracle oinstall 4096 Apr 2 2007 ..
drwxr-x--- 2 oracle oinstall 4096 Feb 5 23:40 2008_02_05
drwxr-x--- 2 oracle oinstall 4096 Feb 6 23:00 2008_02_06
drwxr-x--- 2 oracle oinstall 4096 Feb 7 23:00 2008_02_07
drwxr-x--- 2 oracle oinstall 4096 Feb 8 17:34 2008_02_08
drwxr-x--- 2 oracle oinstall 4096 Feb 11 23:00 2008_02_11
drwxr-x--- 2 oracle oinstall 4096 Feb 12 23:10 2008_02_12
drwxr-x--- 2 oracle oinstall 4096 Feb 13 23:00 2008_02_13
drwxr-x--- 2 oracle oinstall 4096 Feb 14 23:56 2008_02_14
drwxr-x--- 2 oracle oinstall 4096 Feb 15 15:13 2008_02_15

[root@alt2007 archivelog]# rm -rf /app/oracle/flash_recovery_area/MCC2007/
archivelog/2008_02_0*
[root@alt2007 bin]# ./rman target gunawan/mccdba@mcc2007
Recovery Manager: Release 10.1.0.3.0 - Production
Copyright (c) 1995, 2004, Oracle. All rights reserved.
connected to target database: MCC2007 (DBID=3021959133)
RMAN> crosscheck copy;
====================== potong =========================================
validation succeeded for archived log
archive log filename=/app/oracle/oradata/mcc2007/archive/1_39940_614016670.dbf recid=39806 stamp=647924450
validation succeeded for archived log
archive log filename=/app/oracle/oradata/mcc2007/archive/1_39941_614016670.dbf recid=39807 stamp=647928023
validation succeeded for archived log
archive log filename=/app/oracle/oradata/mcc2007/archive/1_39942_614016670.dbf recid=39808 stamp=647931658
validation succeeded for archived log
archive log filename=/app/oracle/oradata/mcc2007/archive/1_39943_614016670.dbf recid=39809 stamp=647935254
validation succeeded for archived log
archive log filename=/app/oracle/oradata/mcc2007/archive/1_39944_614016670.dbf recid=39810 stamp=647938818
validation succeeded for archived log
archive log filename=/app/oracle/oradata/mcc2007/archive/1_39945_614016670.dbf recid=39811 stamp=647942443
validation succeeded for archived log
archive log filename=/app/oracle/oradata/mcc2007/archive/1_39946_614016670.dbf recid=39812 stamp=647946018
validation succeeded for archived log
archive log filename=/app/oracle/oradata/mcc2007/archive/1_39947_614016670.dbf recid=39813 stamp=647949632
validation succeeded for archived log
archive log filename=/app/oracle/oradata/mcc2007/archive/1_39948_614016670.dbf recid=39814 stamp=647953230
Crosschecked 598 objects

RMAN> delete noprompt expired copy;

=================potong===============

deleted archive log
archive log filename=/app/oracle/flash_recovery_area/MCC2007/archivelog/2008_02_13/
o1_mf_1_39521_3v5nhc3n_.arc recid=39387 stamp=646597083
deleted archive log
archive log filename=/app/oracle/flash_recovery_area/MCC2007/archivelog/2008_02_13/
o1_mf_1_39522_3v5qv763_.arc recid=39388 stamp=646600535
deleted archive log
archive log filename=/app/oracle/flash_recovery_area/MCC2007/archivelog/2008_02_13/
o1_mf_1_39523_3v5v29vv_.arc recid=39389 stamp=646603834
deleted archive log
archive log filename=/app/oracle/flash_recovery_area/MCC2007/archivelog/2008_02_13/
o1_mf_1_39524_3v5v9zsj_.arc recid=39390 stamp=646604080
deleted archive log
archive log filename=/app/oracle/flash_recovery_area/MCC2007/archivelog/2008_02_13/
o1_mf_1_39525_3v5y0n6k_.arc recid=39391 stamp=646606852
deleted archive log
archive log filename=/app/oracle/flash_recovery_area/MCC2007/archivelog/2008_02_13/
o1_mf_1_39526_3v61jfn6_.arc recid=39392 stamp=646610430
deleted archive log
archive log filename=/app/oracle/flash_recovery_area/MCC2007/archivelog/2008_02_13/
o1_mf_1_39527_3v6518sb_.arc recid=39393 stamp=646614041
Deleted 176 EXPIRED objects
RMAN>

Merubah ukuran archived Log

Disaat kita menentukan ukuran dari archived log, ternyata kita terlalu kecil dalam menentukan ukurannya
yang dapat menyebabkan database ‘down’, apabila itu terjadi maka kita perlu untuk memperbesar ukuran dari
archived log tersebut.

Langkah-langkah teknis :

SQL> ALTER SYSTEM SET DB_RECOVERY_FILE_DEST_SIZE=60G SCOPE=BOTH;

note : lakukan proses backup archived log secara rutin dan hapus dari database

Alter Block Size for tablespace

Disaat Ukuran ‘default’ dari Block tablespace tidak mencukupi kebutuhan dari database kita, maka diperlukan ukuran block yang lebih besar, biasanya ini diperlukan untuk database yang menjalankan proses yang besar seperti warehouse dan mining.

langkah-langkah teknis :

SQL> show parameter db_block_size;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_block_size integer 8192

SQL> show parameter db_cache_size;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
__db_cache_size big integer 164M
db_cache_size big integer 0

SQL> alter system set db_16k_cache_size = 80M;

System altered.

Create Job Scheduller

Langsung aja deh, gw lagi males pake kalimat pembukaan, kali ini gw mau kasih contoh untuk membuat ‘job scheduller’ pada database oracle.

Sample Code

BEGIN
sys.dbms_scheduler.create_job(
job_name => ‘“GUNAWAN”."CUSTOMERNAMECARDUPDATE"’,
job_type => ‘PLSQL_BLOCK’,
job_action => ‘insert into customer_name_card
select * from customer_name_card@mccconect
where orig_input_date >= ‘’1-oct-2000’’’,
start_date => to_timestamp_tz(’2006-12-20 17:17:00 +7:00’, ‘YYYY-MM-DD HH24:MI:SS TZH:TZM’),
job_class => ‘DEFAULT_JOB_CLASS’,
comments => ‘Mengupdate customer_name_card’,
auto_drop => FALSE,
enabled => FALSE);
sys.dbms_scheduler.set_attribute( name => ‘“GUNAWAN”."CUSTOMERNAMECARDUPDATE"’, attribute => ‘logging_level’, value => DBMS_SCHEDULER.LOGGING_FULL);
sys.dbms_scheduler.enable( ‘“GUNAWAN”."CUSTOMERNAMECARDUPDATE”’ );
END;

BEGIN
sys.dbms_scheduler.create_job(
job_name => ‘“GUNAWAN”."IMPORTAIG"’,
job_type => ‘PLSQL_BLOCK’,
job_action => ‘insert into master_trx
select custnum, ‘’4. jan-mar06’’ as periode, ‘’CREDIT CARD’’ as tndrtype
from mcc_tm_tdrtypebycpc
where (periode between ‘’2006-01’’ and ‘’2006-03’’) and
(tndrtype in (’’2’’,’’3’’,’’4’’,’’5’’,’’6’’,’’7’’,’’8’’,’’9’’,’’10’’,’’11’’)) and
(custnum not in (select distinct custnum from mcc_tm_tdrtypebycpc_unik))
group by custnum’,
start_date => to_timestamp_tz(’2007-02-17 20:00:00 +7:00’, ‘YYYY-MM-DD HH24:MI:SS TZH:TZM’),
job_class => ‘DEFAULT_JOB_CLASS’,
comments => ‘import for aig’,
auto_drop => FALSE,
enabled => TRUE);
END;

Restore SPFile from backup

lansung juga deh.....tanpa basa-basi, soalnya gw lagi males basa-basi yang nantinya malah jadi basi .... smile

Apabila kita ingin mengembalikan SPFile yang terakhir kita backup

Langkah-langkah teknis :

SQL> shutdown immediate;
SQL> restore spfile from ‘/app/oracle/flash_recovery_area/DB2006/autobackup/2007_01_29/
o1_mf_s_613129956_2vtrcoxr_.bkp’;
SQL> startup;

Selesai......