jump to navigation

Swingbench Oracle Single Node September 26, 2008

Posted by setijoagus in Oracle Tools, oracle.
Tags: , ,
add a comment

1. Swingbench adalah free load generator yang ditulis dengan Java oleh Dominic Giles yang bekerja di Oracle UK.
SH – Sales History application based on the Oracle-supplied SH schema. A read-only benchmark.
CC and SOE – OLTP-type applications
Stress Test – basic INSERT/UPDATE/DELETE/SELECT test.
2. File dapat di download di http://www.dominicgiles.com/downloads.html
3. Dapatkan swingbench yang terakhir, misalnya : swingbench230376.zip
4. Extract Swingbench
[oracle@test ~]$ pwd
/home/oracle
[oracle@test ~]$ unzip /u01/backup/swingbench230376.zip
5. download juga java, saya menggunakan jre-6u5-linux-i586-rpm.bin
[oracle@test ~]$ su – root
[root@test ~]# chmod a+x jre-6u5-linux-i586-rpm.bin
[root@test ~]# ./jre-6u5-linux-i586-rpm.bin
[root@test ~]# rpm -ivh jre-6u5-linux-i586.rpm
6. edit environtment Swingbench
[oracle@test ~]$ pwd
/home/oracle
[oracle@test ~]$ cd swingbench
[oracle@test swingbench]$ pwd
/home/oracle/swingbench
[oracle@test swingbench]$ vi swingbench.env
7. Environtment Swingbench, yang perlu diedit adalah (sesuaikan dengan mesin anda)
export JAVAHOME=/usr/java/jre1.6.0_05
export SWINGHOME=/home/oracle/swingbench
8. Menyiapkan test, pada kasus ini saya ingin melakukan test order entry, dimana pembuatan tablespace, schema dan user telah disiapkan oleh swingbench.
[oracle@test swingbench]$ cd bin/
[oracle@test bin]$ pwd
/home/oracle/swingbench/bin
[oracle@test bin]$ ./oewizard


9. Hasil setelah klik button finisih.

10. Untuk mengaktifkan grafik CPU dan disk IO, jalankan perintah seperti di bawah ini :
[oracle@test bin]$ pwd
/home/oracle/swingbench/bin
[oracle@test bin]$ ./cpumonitor

11. Langkah berikutnya adalah menjalankan swingbench untuk mengukur performance dari database kita, dengan mensimulasikan jika ada sejumlah user yang menjalankan order entry secara bersamaan.
[oracle@test bin]$ pwd
/home/oracle/swingbench/bin
[oracle@test bin]$ ./swingbench sample/oeconfig.xml

12. Yang perlu diperhatikan pada saat menjalankan swingbench adalah user, password dan connect string sudah benar.

13. Saatnya, kita mulai pertualangan untuk melakukan performance tuning, semisal “Number of Users” diisi 5.
klik tombol start

14. Semakin Besar TPM, TPS dan semakin kecil RTM (Response Time milliseconds) berarti semakin baik.

Oracle 11g RAC (ASM) to single instance (non ASM) – clone database September 26, 2008

Posted by setijoagus in Oracle Backup, oracle.
Tags: , , ,
2 comments

Clone database from Oracle 11g RAC (ASM) to single instance (non ASM)
===============================================================
komputer source
===============================================================
1. Setting RMAN
[oracle@node1 bin]$ cd $ORACLE_HOME/bin
[oracle@node1 bin]$ ./rman target / NOCATALOG
Recovery Manager: Release 11.1.0.6.0 – Production on Tue Sep 23 15:57:07 2008
Copyright (c) 1982, 2007, Oracle. All rights reserved.
connected to target database: NODE (DBID=711660360)
using target database control file instead of recovery catalog
RMAN> configure controlfile autobackup on;
RMAN> configure backup optimization on;
RMAN> host;
[oracle@node1 bin]$ su – root
Password:
[root@node1 ~]# mkdir /u03/backup/node/ -p
[root@node1 ~]# chown oracle.oinstall /u03 -R
[root@node1 ~]# exit
exit
host command complete
RMAN> configure channel device type disk format ‘/u03/backup/node/%d-id-%I-%s.bkp’;
RMAN> exit;

2. Test dengan menambahkan record
a. tambahan record.
SQL> conn scott/tiger;
SQL> insert into dept values(61,’IT’,’surabaya’);
SQL> insert into dept values(62,’IT’,'madiun’);
SQL> commit;
SQL> select * from dept;
DEPTNO DNAME LOC
———- ————– ————-
61 IT surabaya
62 IT madiun
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
6 rows selected.
SQL> exit;
c. list tablespace
[oracle@node1 ~]$ sqlplus / as sysdba
SQL> column tablespace_name format a10
SQL> column file_name format a55
SQL> select tablespace_name, file_name from dba_data_files;
TABLESPACE FILE_NAME
———- ——————————————————-
USERS +DATA1/node/datafile/users.259.666184225
UNDOTBS1 +DATA1/node/datafile/undotbs1.258.666184225
SYSAUX +DATA1/node/datafile/sysaux.257.666184225
SYSTEM +DATA1/node/datafile/system.256.666184225
EXAMPLE +DATA1/node/datafile/example.267.666184357
UNDOTBS2 +DATA1/node/datafile/undotbs2.268.666184499
6 rows selected.
SQL> exit;
3. Lakukan backup ditambah dengan archive log
a. lakukan full backup
[oracle@node1 bin]$ cd $ORACLE_HOME/bin
[oracle@node1 bin]$ rman target /
Recovery Manager: Release 11.1.0.6.0 – Production on Fri Sep 26 11:12:43 2008
Copyright (c) 1982, 2007, Oracle. All rights reserved.
connected to target database: NODE (DBID=711660360)
RMAN> BACKUP INCREMENTAL LEVEL 0 DATABASE plus archivelog delete input TAG=full_backup_260908;

b. delete obsolote backup dan lihat daftar backupset
RMAN> delete noprompt obsolete;
RMAN> list backupset;
List of Backup Sets
===================
BS Key Type LV Size Device Type Elapsed Time Completion Time
——- —- — ———- ———– ———— —————
11 Full 1.16G DISK 00:00:41 26-SEP-08
BP Key: 15 Status: AVAILABLE Compressed: NO Tag: TAG20080926T111320
Piece Name: /home/oracle/clone/0bjri7qg_1_2
List of Datafiles in backup set 11
File LV Type Ckp SCN Ckp Time Name
—- — —- ———- ——— —-
1 Full 1033174 26-SEP-08 +DATA1/node/datafile/system.256.666184225
2 Full 1033174 26-SEP-08 +DATA1/node/datafile/sysaux.257.666184225
3 Full 1033174 26-SEP-08 +DATA1/node/datafile/undotbs1.258.666184225
4 Full 1033174 26-SEP-08 +DATA1/node/datafile/users.259.666184225
5 Full 1033174 26-SEP-08 +DATA1/node/datafile/example.267.666184357
6 Full 1033174 26-SEP-08 +DATA1/node/datafile/undotbs2.268.666184499
7 Full 1033174 26-SEP-08

BS Key Type LV Size Device Type Elapsed Time Completion Time
——- —- — ———- ———– ———— —————
15 Full 17.98M DISK 00:00:02 26-SEP-08
BP Key: 17 Status: AVAILABLE Compressed: NO Tag: TAG20080926T111741
Piece Name: /home/oracle/clone/controlfile
SPFILE Included: Modification time: 26-SEP-08
SPFILE db_unique_name: NODE
Control File Included: Ckp SCN: 1034682 Ckp time: 26-SEP-08

BS Key Type LV Size Device Type Elapsed Time Completion Time
——- —- — ———- ———– ———— —————
21 Full 17.98M DISK 00:00:02 26-SEP-08
BP Key: 23 Status: AVAILABLE Compressed: NO Tag: TAG20080926T115138
Piece Name: +DATA1/node/autobackup/2008_09_26/s_666445898.325.666445901
SPFILE Included: Modification time: 26-SEP-08
SPFILE db_unique_name: NODE
Control File Included: Ckp SCN: 1041704 Ckp time: 26-SEP-08

BS Key Type LV Size Device Type Elapsed Time Completion Time
——- —- — ———- ———– ———— —————
24 Incr 0 1.16G DISK 00:00:41 26-SEP-08
BP Key: 26 Status: AVAILABLE Compressed: NO Tag: TAG20080926T115816
Piece Name: /u03/backup/node/NODE-id-711660360-25.bkp
List of Datafiles in backup set 24
File LV Type Ckp SCN Ckp Time Name
—- — —- ———- ——— —-
1 0 Incr 1066400 26-SEP-08 +DATA1/node/datafile/system.256.666184225
2 0 Incr 1066400 26-SEP-08 +DATA1/node/datafile/sysaux.257.666184225
3 0 Incr 1066400 26-SEP-08 +DATA1/node/datafile/undotbs1.258.666184225
4 0 Incr 1066400 26-SEP-08 +DATA1/node/datafile/users.259.666184225
5 0 Incr 1066400 26-SEP-08 +DATA1/node/datafile/example.267.666184357
6 0 Incr 1066400 26-SEP-08 +DATA1/node/datafile/undotbs2.268.666184499

BS Key Size Device Type Elapsed Time Completion Time
——- ———- ———– ———— —————
25 32.00K DISK 00:00:00 26-SEP-08
BP Key: 27 Status: AVAILABLE Compressed: NO Tag: FULL_BACKUP_260908
Piece Name: /u03/backup/node/NODE-id-711660360-26.bkp

List of Archived Logs in backup set 25
Thrd Seq Low SCN Low Time Next SCN Next Time
—- ——- ———- ——— ———- ———
1 59 1066370 26-SEP-08 1066474 26-SEP-08
2 44 1066373 26-SEP-08 1066471 26-SEP-08

BS Key Type LV Size Device Type Elapsed Time Completion Time
——- —- — ———- ———– ———— —————
26 Full 17.98M DISK 00:00:02 26-SEP-08
BP Key: 28 Status: AVAILABLE Compressed: NO Tag: TAG20080926T115910
Piece Name: +DATA1/node/autobackup/2008_09_26/s_666446350.334.666446353
SPFILE Included: Modification time: 26-SEP-08
SPFILE db_unique_name: NODE
Control File Included: Ckp SCN: 1066483 Ckp time: 26-SEP-08

c. copykan backupset fullbackup ke local file system, dimana terlihat pada langkah diatas
BS (Backup Set) no 11 adalah BS full backup
[oracle@node1 bin]$ mkdir /home/oracle/clone/ -p
[oracle@node1 bin]$ exit
RMAN> backup backupset 24 format ‘/home/oracle/clone/%U’;

d. juga lakukan backup secara manual spfile dan control file
backup spfile include current controlfile format ‘/home/oracle/clone/controlfile’;
RMAN> backup spfile include current controlfile format ‘/home/oracle/clone/controlfile’;
RMAN> exit
e. periksa hasil backup backupset dan controlfile yang akan dibutuhkan untuk clone
[oracle@node1 bin]$ ls /home/oracle/clone/
0pjriaep_1_2 controlfile
f. pindahkan hasil backup ke komputer target
[oracle@node1 bin]$ cd /home/oracle/clone/
[oracle@node1 clone]$ scp 0pjriaep_1_2 controlfile 172.20.141.153:/home/oracle/clone
oracle@172.20.141.153’s password:

===============================================================
komputer target
Sudah diinstall oracle database software
===============================================================
4. Menyiapkan directory admin oracle, datafile oracle dan oratab.
a. menyiapkan folder admin oracle clone
[oracle@test ~]$ mkdir -p /u01/app/oracle/admin/node/adump
[oracle@test ~]$ mkdir -p /u01/app/oracle/admin/node/bdump
[oracle@test ~]$ mkdir -p /u01/app/oracle/admin/node/cdump
[oracle@test ~]$ mkdir -p /u01/app/oracle/admin/node/dpdump
[oracle@test ~]$ mkdir -p /u01/app/oracle/admin/node/pfile
[oracle@test ~]$ mkdir -p /u01/app/oracle/admin/node/scripts
[oracle@test ~]$ mkdir -p /u01/app/oracle/admin/node/udump
[oracle@test ~]$ mkdir -p /u01/app/oracle/flash_recovery_area
b. menyiapkan folder datafile dan archive oracle clone
[oracle@test ~]$ mkdir -p /u01/oradata/node
[oracle@test ~]$ mkdir -p /u01/oradata/node/controlfile/

[oracle@test ~]$ mkdir -p /u01/archive/node
c. menyiapkan oratab
[oracle@test db_1]$ echo “node:/u01/app/oracle/product/11.1.0/db_1:N” >> /etc/oratab
[oracle@test db_1]$ ORACLE_SID=node; export ORACLE_SID
[oracle@test db_1]$ oraenv
ORACLE_SID = [node] ?
The Oracle base for ORACLE_HOME=/u01/app/oracle/product/11.1.0/db_1 is /u01/app/oracle

5. Menyiapkan Oracle Password File oracle clone
[oracle@test db_1]$ orapwd file=$ORACLE_HOME/dbs/orapwnode password=ora
6. Restore SPFILE
[oracle@test ~]$ rman
Recovery Manager: Release 11.1.0.6.0 – Production on Sat Sep 26 04:38:49 2008
Copyright (c) 1982, 2007, Oracle. All rights reserved.
RMAN> connect target;
connected to target database (not started)
RMAN> restore spfile from ‘/home/oracle/clone/controlfile’;
Starting restore at 26-SEP-08
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=98 device type=DISK
channel ORA_DISK_1: restoring spfile from AUTOBACKUP /home/oracle/clone/controlfile
channel ORA_DISK_1: SPFILE restore from AUTOBACKUP complete
Finished restore at 26-SEP-08

RMAN> exit
Recovery Manager complete.
[oracle@test ~]$ sqlplus / as sysdba
SQL> create pfile from spfile;
File created.
SQL> exit

7. Edit PFILE
a. asli pfile
[oracle@test ~]$ vi $ORACLE_HOME/dbs/initnode.ora
node2.__db_cache_size=322961408
node1.__db_cache_size=281018368
node2.__java_pool_size=4194304
node1.__java_pool_size=12582912
node2.__large_pool_size=4194304
node1.__large_pool_size=4194304
node1.__oracle_base=’/u01/app/oracle’#ORACLE_BASE set from environment
node2.__oracle_base=’/u01/app/oracle’#ORACLE_BASE set from environment
node2.__pga_aggregate_target=293601280
node1.__pga_aggregate_target=343932928
node2.__sga_target=562036736
node1.__sga_target=511705088
node2.__shared_io_pool_size=0
node1.__shared_io_pool_size=0
node2.__shared_pool_size=222298112
node1.__shared_pool_size=205520896
node2.__streams_pool_size=0
node1.__streams_pool_size=0
*.audit_file_dest=’/u01/app/oracle/admin/node/adump’
*.audit_trail=’db’
*.cluster_database_instances=2
*.cluster_database=true
*.compatible=’11.1.0.0.0′
*.control_files=’+DATA1/node/controlfile/current.261.666184329′,’+DATA1/node/controlfile/current.260.666184329′
*.db_block_size=8192
*.db_create_file_dest=’+DATA1′
*.db_domain=”
*.db_name=’node’
*.db_recovery_file_dest=’+DATA1′
*.db_recovery_file_dest_size=2147483648
*.diagnostic_dest=’/u01/app/oracle’
*.dispatchers=’(PROTOCOL=TCP) (SERVICE=nodeXDB)’
node1.instance_number=1
node2.instance_number=2
*.log_archive_dest_1=’location=/u03/archive01′
*.log_archive_format=’%t_%s_%r.dbf’
*.memory_target=852492288
*.open_cursors=300
*.processes=150
*.remote_listener=’LISTENERS_NODE’
*.remote_login_passwordfile=’exclusive’
node2.thread=2
node1.thread=1
node1.undo_tablespace=’UNDOTBS1′
node2.undo_tablespace=’UNDOTBS2′
b. del semua informasi, sisakan 1 node (example del node2.*)
c. del informasi cluster
*.cluster_database_instances=2
*.cluster_database=true
*.remote_listener=’LISTENERS_NODE’
node1.instance_number=1
node1.thread=1
d. add ASM to non-ASM
*.db_file_name_convert=’+DATA1′,’/u01/oradata/node’
*.log_file_name_convert=’+DATA1′,’/u01/oradata/node’
e. menjadi :
*.__db_cache_size=281018368
*.__java_pool_size=12582912
*.__large_pool_size=4194304
*.__streams_pool_size=0
*.audit_file_dest=’/u01/app/oracle/admin/node/adump’
*.compatible=’11.1.0.0.0′
*.control_files=’/u01/oradata/node/controlfile/control01.ctl’,'/u01/oradata/node/controlfile/control02.ctl’
*.db_block_size=8192
*.db_create_file_dest=’/u01/oradata/node’
*.db_domain=”
*.db_file_name_convert=’+DATA1′,’/u01/oradata/node’
*.db_name=’node’
*.db_recovery_file_dest=’/u01/app/oracle/flash_recovery_area’
*.db_recovery_file_dest_size=2147483648
*.dispatchers=’(PROTOCOL=TCP) (SERVICE=nodeXDB)’
*.log_file_name_convert=’+DATA1′,’/u01/oradata/node’
*.open_cursors=300
*.__pga_aggregate_target=343932928
*.processes=150
*.remote_login_passwordfile=’exclusive’
*.__sga_target=511705088
*.__shared_io_pool_size=0
*.__shared_pool_size=205520896
*.__oracle_base=’/u01/app/oracle’#ORACLE_BASE set from environment
*.audit_trail=’db’
*.diagnostic_dest=’/u01/app/oracle’
*.log_archive_dest_1=’location=/u01/archive/node/archive01′
*.log_archive_format=’%t_%s_%r.dbf’
*.memory_target=852492288
*.undo_tablespace=’UNDOTBS1′
8. create SPFILE dari PFILE yang sudah diedit
[oracle@test ~]$ ORACLE_SID=node; export ORACLE_SID
[oracle@test ~]$ sqlplus / as sysdba
SQL*Plus: Release 11.1.0.6.0 – Production on Sat Sep 26 07:19:04 2008
Copyright (c) 1982, 2007, Oracle. All rights reserved.
Connected to an idle instance.
SQL> create spfile from pfile;
File created.

SQL> startup nomount;
ORACLE instance started.
Total System Global Area 853716992 bytes
Fixed Size 1303244 bytes
Variable Size 566234420 bytes
Database Buffers 281018368 bytes
Redo Buffers 5160960 bytes
SQL> exit

9. Restore Controlfile
[oracle@test ~]$ su – root
Password:
[root@test ~]# chown oracle.oinstall /u01 -R
[oracle@test ~]$ rman target /
RMAN> restore controlfile from ‘/home/oracle/clone/controlfile’;
Starting restore at 26-SEP-08
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=153 device type=DISK

channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:28
output file name=/u01/oradata/node/controlfile/control01.ctl
output file name=/u01/oradata/node/controlfile/control02.ctl
Finished restore at 26-SEP-08
10. Restore Database
RMAN> alter database mount;
database mounted
released channel: ORA_DISK_1
RMAN> restore database;
Starting restore at 26-SEP-08
Starting implicit crosscheck backup at 26-SEP-08
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=153 device type=DISK
Crosschecked 8 objects
Finished implicit crosscheck backup at 26-SEP-08

Starting implicit crosscheck copy at 26-SEP-08
using channel ORA_DISK_1
Finished implicit crosscheck copy at 26-SEP-08

searching for all files in the recovery area
cataloging files…
no files cataloged

using channel ORA_DISK_1

channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00001 to +DATA1/node/datafile/system.256.666184225
channel ORA_DISK_1: restoring datafile 00002 to +DATA1/node/datafile/sysaux.257.666184225
channel ORA_DISK_1: restoring datafile 00003 to +DATA1/node/datafile/undotbs1.258.666184225
channel ORA_DISK_1: restoring datafile 00004 to +DATA1/node/datafile/users.259.666184225
channel ORA_DISK_1: restoring datafile 00005 to +DATA1/node/datafile/example.267.666184357
channel ORA_DISK_1: restoring datafile 00006 to +DATA1/node/datafile/undotbs2.268.666184499
channel ORA_DISK_1: reading from backup piece /home/oracle/clone/0pjriaep_1_2
channel ORA_DISK_1: piece handle=/home/oracle/clone/0pjriaep_1_2 tag=TAG20080926T115816
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:17:56
Finished restore at 26-SEP-08
RMAN> exit
Recovery Manager complete.

11. Recover Database
[oracle@test ~]$ sqlplus / as sysdba
SQL> recover database until cancel using backup controlfile;
ORA-00279: change 1066400 generated at 09/26/2008 11:58:17 needed for thread 1
ORA-00289: suggestion : /u01/archive/node/archive011_59_666184331.dbf
ORA-00280: change 1066400 for thread 1 is in sequence #59
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
CANCEL
Media recovery cancelled.

SQL> alter database open resetlogs;
Database altered.

12. test apakah data terakhir sudah dipindahkan ?
SQL> conn scott/tiger
Connected.
SQL> select * from dept;
DEPTNO DNAME LOC
—— ————– ————-
61 IT surabaya
62 IT madiun
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON

6 rows selected.
SQL> exit

13. membuat listener.ora
[oracle@test ~]$ vi $ORACLE_HOME/network/admin/listener.ora
LISTENER_NODE =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 172.20.141.153)(PORT = 1521)(IP = FIRST))
)
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC))
)
)
)

14. menghidupkan listener.ora
[oracle@test ~]$ lsnrctl start LISTENER_NODE

15. membuat tnsnames.ora
[oracle@test ~]$ vi $ORACLE_HOME/network/admin/tnsnames.ora
NODE =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 172.20.141.153)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = node)
(INSTANCE_NAME = node)
)
)

16. test connection
[oracle@test ~]$ sqlplus scott/tiger@node
17. bersih – bersih data rman di target komputer
RMAN> delete noprompt force backup;
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=126 device type=DISK
List of Backup Pieces
BP Key BS Key Pc# Cp# Status Device Type Piece Name
——- ——- — — ———– ———– ———-
15 11 1 2 EXPIRED DISK /home/oracle/clone/0bjri7qg_1_2
17 15 1 1 EXPIRED DISK /home/oracle/clone/controlfile
23 21 1 1 EXPIRED DISK +DATA1/node/autobackup/2008_09_26/s_666445898.325.666445901
26 24 1 1 EXPIRED DISK /u03/backup/node/NODE-id-711660360-25.bkp
29 24 1 2 AVAILABLE DISK /home/oracle/clone/0pjriaep_1_2
27 25 1 1 EXPIRED DISK /u03/backup/node/NODE-id-711660360-26.bkp
28 26 1 1 EXPIRED DISK +DATA1/node/autobackup/2008_09_26/s_666446350.334.666446353
30 27 1 1 EXPIRED DISK +DATA1/node/autobackup/2008_09_26/s_666446563.339.666446565
31 28 1 1 AVAILABLE DISK /u01/app/oracle/flash_recovery_area/NODE/autobackup/2008_09_29/o1_mf_s_666680379_4fzzlpmm_.bkp
deleted backup piece
backup piece handle=/home/oracle/clone/0bjri7qg_1_2 RECID=15 STAMP=666443807
deleted backup piece
backup piece handle=/home/oracle/clone/controlfile RECID=17 STAMP=666443862
deleted backup piece
backup piece handle=+DATA1/node/autobackup/2008_09_26/s_666445898.325.666445901 RECID=23 STAMP=666445900
deleted backup piece
backup piece handle=/u03/backup/node/NODE-id-711660360-25.bkp RECID=26 STAMP=666446299
deleted backup piece
backup piece handle=/home/oracle/clone/0pjriaep_1_2 RECID=29 STAMP=666446518
deleted backup piece
backup piece handle=/u03/backup/node/NODE-id-711660360-26.bkp RECID=27 STAMP=666446349
deleted backup piece
backup piece handle=+DATA1/node/autobackup/2008_09_26/s_666446350.334.666446353 RECID=28 STAMP=666446352
deleted backup piece
backup piece handle=+DATA1/node/autobackup/2008_09_26/s_666446563.339.666446565 RECID=30 STAMP=666446565
deleted backup piece
backup piece handle=/u01/app/oracle/flash_recovery_area/NODE/autobackup/2008_09_29/o1_mf_s_666680379_4fzzlpmm_.bkp RECID=31 STAMP=666680390
Deleted 9 objects

RMAN> delete noprompt force copy;
released channel: ORA_DISK_1
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=126 device type=DISK
List of Archived Log Copies for database with db_unique_name NODE
=====================================================================
Key Thrd Seq S Low Time
——- —- ——- – ———
102 2 1 A 29-SEP-08
Name: /u01/archive/node/archive012_1_666680175.dbf

deleted archived log
archived log file name=/u01/archive/node/archive012_1_666680175.dbf RECID=102 STAMP=666680349
Deleted 1 objects

RMAN> exit
sumber :
http://www.idevelopment.info/data/Oracle/DBA_tips/Automatic_Storage_Management/ASM_30.shtml

ORACLE 11g : MEMORY_TARGET not supported on this system (ORA-00845) September 26, 2008

Posted by setijoagus in oracle, oracle Performance.
Tags: , ,
add a comment

ORACLE 11g : MEMORY_TARGET not supported on this system (ORA-00845)

pada saat mengatur memory di oracle 11g linux khususnya parameter
memory_target misalkan diinginkan memory_target = 2,5 G, saya menemukan
error :
SQL> ORA-00845: MEMORY_TARGET not supported on this system
ternyata hal itu berhubungan dengan /dev/shm, artinya /dev/shm tidak
dikonfigurasi dengan nilai yang cukup untuk mengalokasikan parameter
memory_target.

[root@node1 ~]# df -h
Filesystem Size Used Avail Use% Mounted on
……… —- —- —– —- ———-
tmpfs 1.8G 1.0G 747M 74% /dev/shm
……… —- —- —– —- ———-

maka langkah yang harus dilakukan adalah :

1. periksa apakah /dev/shm masih ada isisnya ?, jika ya sebaiknya instance database sudah di shutdown
[root@node1 ~]# ls /dev/shm
……… —————————
ora_node1_491529_0 ora_node1_491529_1 ora_node1_491529_10
……… —————————

2. umount tmpfs dan buat yang baru
[root@node1 ~]# umount tmpfs
[root@node1 ~]# mount -t tmpfs shmfs -o size=2663m /dev/shm
[root@node1 ~]# df -h
Filesystem Size Used Avail Use% Mounted on
……… —- —- —– —- ———-
shmfs 2.7G 2.0G 622M 77% /dev/shm
……… —- —- —– —- ———-

3. supaya setiap kali reboot dijalankan, biasanya dipasang di /etc/rc.d/rc.local
[root@node1 ~]# vi /etc/rc.d/rc.local
#!/bin/sh
#
# This script will be executed *after* all the other init scripts.
# You can put your own initialization stuff in here if you don’t
# want to do the full Sys V style init stuff.
touch /var/lock/subsys/local
umount tmpfs
mount -t tmpfs shmfs -o size=2663m /dev/shm
4. supaya setiap kali reboot dijalankan, juga biasanya dipasang di/etc/fstab
[root@node1 ~]# vi /etc/fstab
tmpfs /dev/shm tmpfs size=2663M 0 0

5. misalkan diinginkan memory_target = 2,5 G
SQL> show parameter memory
NAME TYPE VALUE
————————————————-
hi_shared_memory_address integer 0
memory_max_target big integer 2512M
memory_target big integer 2512M
shared_memory_address integer 0

Move Control File ASM IN Oracle 11g RAC September 26, 2008

Posted by setijoagus in Oracle Administration, oracle.
Tags: , , , ,
add a comment

Bagaimana cara memindahkan controlfile oracle 11g pada ASM ?
1. periksa letak control_file
[oracle@node1 ~]$ ORACLE_SID=node1; export ORACLE_SID
[oracle@node1 /]$ sqlplus / as sysdba
SQL> startup;
SQL> SHOW PARAMETER CONTROL_FILES;
NAME TYPE VALUE
—————————————————————————–
control_files string
+DATA1/rac/controlfile/current
.264.649098781
SQL> exit;

2. lakukan backup dengan rman
[oracle@node1 ~]$ rman nocatalog target /
RMAN> backup current controlfile format ‘/home/oracle/control.bkp’;
Starting backup at 15-MAR-08
using channel ORA_DISK_1
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
including current control file in backup set
channel ORA_DISK_1: starting piece 1 at 15-MAR-08
channel ORA_DISK_1: finished piece 1 at 15-MAR-08
piece handle=/home/oracle/control.bkp tag=TAG20080315T154017 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:03
Finished backup at 15-MAR-08
RMAN> exit
Recovery Manager complete.

3. shutdown database dan startup dengan nomount
[oracle@node1 /]$ sqlplus / as sysdba
SQL> shutdown immediate;
SQL> startup nomount;
SQL> exit;

4. restore controlfile ke lokasi baru
[oracle@node1 ~]$ rman nocatalog target /
RMAN> restore controlfile to
‘+DATA3/rac/controlfile/current.264.649098781′ from
‘+DATA1/rac/controlfile/current.264.649098781′;
RMAN> exit
Recovery Manager complete.

5. periksa apakah file sudah ada di tempat yang baru ?
oracle@node1 ~]$ ORACLE_SID=+ASM1; export ORACLE_SID
[oracle@node1 ~]$ asmcmd -p
ASMCMD [+] > ls -la +DATA3/rac/controlfile/
Type Redund Striped Time Sys Name
CONTROLFILE UNPROT FINE MAR 15 15:00:00 Y none =>
current.258.649439353
ASMCMD [+] >

6. dari hasil no 5 ternyata nama file telah berubah dari :
current.264.649098781
menjadi :current.258.649439353

7. set dengan lokasi baru
SQL> startup nomount;
SQL> alter system set control_files=
‘+DATA3/rac/controlfile/current.258.649439353′ scope=spfile;
SQL> shutdown immediate;
SQL> startup;

8. periksa posisi controlfile sekarang
SQL> SHOW PARAMETER CONTROL_FILES;
NAME TYPE VALUE
—————————————————————————–
control_files string
+DATA3/rac/controlfile/current
.258.649439353