Oracle 11g ASM : FTP and HTTP Access September 28, 2008
Posted by setijoagus in Oracle Administration, oracle.Tags: ASM, http ftp, oracle
add a comment
###########################################
Oracle 11g ASM : FTP and HTTP Access
###########################################
1. run script catxdbdbca
[oracle@node1 ~]$ cd $ORACLE_HOME/rdbms/admin
[oracle@node1 admin]$ sqlplus / as sysdba
SQL*Plus: Release 11.1.0.6.0 – Production on Sun Sep 28 14:52:18 2008
Copyright (c) 1982, 2007, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 – Production
With the Partitioning, Real Application Clusters, OLAP, Data Mining
and Real Application Testing options
SQL> @catxdbdbca 7787 8080;
SQL> exit;
2. test FTP Access
[oracle@node1 admin]$ ftp node1 7787
Connected to node1.localdomain.
220- node1
Unauthorised use of this FTP server is prohibited and may be subject to civil and criminal prosecution.
220 node1 FTP Server (Oracle XML DB/Oracle Database) ready.
530 Please login with USER and PASS.
530 Please login with USER and PASS.
KERBEROS_V4 rejected as an authentication type
Name (node1:oracle): system
331 pass required for SYSTEM
Password:
230 SYSTEM logged in
Remote system type is Unix.
ftp> ls
227 Entering Passive Mode (172,20,141,151,13,24)
150 ASCII Data Connection
drw-r–r– 2 SYS oracle 0 AUG 03 01:49 OLAP_XDS
drw-r–r– 2 SYS oracle 0 SEP 23 04:13 home
drw-r–r– 2 SYS oracle 0 AUG 03 02:15 images
drw-r–r– 2 SYS oracle 0 AUG 03 01:49 olap_data_security
drw-r–r– 2 SYS oracle 0 AUG 03 01:35 public
drw-r–r– 2 SYS oracle 0 SEP 23 04:13 sys
-rw-r–r– 1 SYS oracle 0 SEP 28 07:52 xdbconfig.xml
226 ASCII Transfer Complete
ftp> cd sys/asm/DATA1/NODE/DATAFILE
250 CWD Command successful
ftp> ls
227 Entering Passive Mode (172,20,141,151,232,90)
150 ASCII Data Connection
-rw-r–r– 1 SYS oracle 734011392 SEP 28 07:59 SYSTEM.256.666184225
-rw-r–r– 1 SYS oracle 750788608 SEP 28 07:59 SYSAUX.257.666184225
-rw-r–r– 1 SYS oracle 346038272 SEP 28 07:59 UNDOTBS1.258.666184225
-rw-r–r– 1 SYS oracle 5251072 SEP 28 07:59 USERS.259.666184225
-rw-r–r– 1 SYS oracle 104865792 SEP 28 07:59 EXAMPLE.267.666184357
-rw-r–r– 1 SYS oracle 262152192 SEP 28 07:59 UNDOTBS2.268.666184499
-rw-r–r– 1 SYS oracle 2105344 SEP 28 07:59 SENIN_TS.338.666201891
-rw-r–r– 1 SYS oracle 2105344 SEP 28 07:59 senin_ts01.dbf
-rw-r–r– 1 SYS oracle 356524032 SEP 28 07:59 SOE.326.666460349
-rw-r–r– 1 SYS oracle 356524032 SEP 28 07:59 soe.dbf
-rw-r–r– 1 SYS oracle 713039872 SEP 28 07:59 SOEINDEX.321.666460359
-rw-r–r– 1 SYS oracle 713039872 SEP 28 07:59 soeindex.dbf
226 ASCII Transfer Complete
ftp> bye
3. test HTTP Access
4. jika instance not running, apakah ftp / http bisa ?
[oracle@node1 admin]$ srvctl stOP database -d node
[oracle@node1 admin]$ srvctl status database -d node
Instance node1 is not running on node node1
Instance node2 is not running on node node2
[oracle@node1 admin]$ ftp node1 7787
ftp: connect: Connection refused
ftp> bye
[oracle@node1 admin]$ ORACLE_SID=node1; export ORACLE_SID
[oracle@node1 admin]$ sqlplus / as sysdba
SQL> startup;
SQL> exit
[oracle@node1 admin]$ ftp node1 7787
Connected to node1.localdomain.
220- node1
Unauthorised use of this FTP server is prohibited and may be subject to civil and criminal prosecution.
220 node1 FTP Server (Oracle XML DB/Oracle Database) ready.
530 Please login with USER and PASS.
530 Please login with USER and PASS.
KERBEROS_V4 rejected as an authentication type
Name (node1:oracle): system
331 pass required for SYSTEM
Password:
230 SYSTEM logged in
Remote system type is Unix.
ftp> bye
221 QUIT Goodbye.
Oracle 11g RAC – ASM : asmcmd September 28, 2008
Posted by setijoagus in Oracle Administration, oracle.Tags: ASM, asmcmd, oracle
add a comment
###########################################
Oracle 11g RAC – ASM : asmcmd
###########################################
1. mengaktifkan asmcmd, dengan mengaktifkan ORACLE_SID yang benar.
[oracle@node1 ~]$ ORACLE_SID=node1; export ORACLE_SID
[oracle@node1 ~]$ asmcmd
ASMCMD-08103: failed to connect to ASM; ASMCMD running in non-connected mode
ASMCMD> exit
[oracle@node1 ~]$ ORACLE_SID=+ASM1; export ORACLE_SID
[oracle@node1 ~]$ asmcmd
ASMCMD>
ASMCMD> exit
[oracle@node1 ~]$ asmcmd -v
asmcmd version 11.1.0.6.0
2. perintah – perintah di asmcmd (ASM Commands)
cd : Changes the current directory to the specified directory.
cp : Enables you to copy files between ASM disk groups on a local instance and remote instances.
du : Displays the total disk space occupied by ASM files in the specified ASM directory and all of its subdirectories, recursively.
exit : Exits ASMCMD.
find : Lists the paths of all occurrences of the specified name (with wildcards) under the specified directory.
help : Displays the syntax and description of ASMCMD commands.
ls : Lists the contents of an ASM directory, the attributes of the specified file, or the names and attributes of all disk groups.
lsct : Lists information about current ASM clients.
lsdg : Lists all disk groups and their attributes.
lsdsk : Lists disks visible to ASM.
md_backup : Creates a backup of all of the mounted disk groups.
md_restore : Restores disk groups from a backup.
mkalias : Creates an alias for system-generated filenames.
mkdir : Creates ASM directories.
pwd : Displays the path of the current ASM directory.
remap : Repairs a range of physical blocks on a disk.
rm : Deletes the specified ASM files or directories.
rmalias : Deletes the specified alias, retaining the file that the alias points to.
3. perintah dasar asmcmd yang sering digunakan
[oracle@node1 ~]$ asmcmd
ASMCMD> ls
DATA1/
DATA2/
DATA3/
DATA4/
ASMCMD> cd +DATA1/NODE
ASMCMD> pwd
+DATA1/NODE
ASMCMD> ls
AUTOBACKUP/
CONTROLFILE/
DATAFILE/
ONLINELOG/
PARAMETERFILE/
TEMPFILE/
spfilenode.ora
ASMCMD> exit
[oracle@node1 ~]$
4. menghitung kapasitas disk, dimana
Used_MB – This value does not include mirroring.
Mirror_used_MB – This value includes mirroring.
[oracle@node1 ~]$ asmcmd
ASMCMD> du +DATA1
Used_MB Mirror_used_MB
3829 3829
ASMCMD> du +DATA1/NODE/DATAFILE
Used_MB Mirror_used_MB
3135 3135
ASMCMD> du +DATA1/NODE/CONTROLFILE
Used_MB Mirror_used_MB
48 48
5. informasi asm lainnya
ASMCMD> lsct
DB_Name Status Software_Version Compatible_version Instance_Name Disk_Group
node CONNECTED 11.1.0.6.0 11.1.0.0.0 node1 DATA1
ASMCMD> lsdg
State Type Rebal Sector Block AU Total_MB Free_MB Req_mir_free_MB Usable_file_MB Offline_disks Name
MOUNTED EXTERN N 512 4096 1048576 51199 47276 0 47276 0 DATA1/
MOUNTED EXTERN N 512 4096 1048576 51199 51106 0 51106 0 DATA2/
MOUNTED EXTERN N 512 4096 1048576 51199 51106 0 51106 0 DATA3/
MOUNTED EXTERN N 512 4096 1048576 102398 102305 0 102305 0 DATA4/
ASMCMD> lsdsk
Path
/dev/oracleasm/disks/VOL1
/dev/oracleasm/disks/VOL2
/dev/oracleasm/disks/VOL3
/dev/oracleasm/disks/VOL_1
ASMCMD> exit
6. asmcmd tidak mengenal huruf besar / kecil
[oracle@node1 ~]$ asmcmd -p
ASMCMD [+] > ls -la
State Type Rebal Name
MOUNTED EXTERN N DATA1/
MOUNTED EXTERN N DATA2/
MOUNTED EXTERN N DATA3/
MOUNTED EXTERN N DATA4/
ASMCMD [+] > ls -la /data1/node/controlfile
Type Redund Striped Time Sys Name
CONTROLFILE UNPROT FINE SEP 27 09:00:00 Y none => Current.260.666184329
CONTROLFILE UNPROT FINE SEP 27 09:00:00 Y none => Current.261.666184329
ASMCMD [+] > exit
Swingbench Oracle Single Node September 26, 2008
Posted by setijoagus in Oracle Tools, oracle.Tags: oracle, performance, swingbench
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: ASM, clone database, oracle 11g, RMAN
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: /dev/shm, memory target, oracle
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: ASM, control file, MOVE, oracle 11g, RMAN
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
oracle 11g RAC : mengubah menjadi mode archive log September 23, 2008
Posted by setijoagus in Oracle Administration, oracle.Tags: archive log, oracle 11g, RAC
add a comment
oracle 11g RAC : mengubah menjadi mode archive log
a. apakah database Instance masih hidup ?
[oracle@node1 ~]$ srvctl status database -d node
Instance node1 is running on node node1
Instance node2 is running on node node2
b. matikan database Instance
[oracle@node1 ~]$ srvctl stop database -d node
c. Menyiapkan salah satu database instance agar diset menjadi mode archive log, dengan mengirimkan perintah instance mount
[oracle@node1 ~]$ srvctl start instance -d node -i node1 -o mount
[oracle@node1 ~]$ srvctl status database -d node
Instance node1 is running on node node1
Instance node2 is not running on node node2
[oracle@node1 ~]$ sqlplus / as sysdba
SQL> alter database archivelog;
Database altered.
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination +DATA1
Oldest online log sequence 3
Current log sequence 4
SQL> exit
d. restart database instance
[oracle@node1 ~]$ srvctl stop instance -d node -i node1
[oracle@node1 ~]$ srvctl start database -d node
[oracle@node1 ~]$ srvctl status database -d node
Instance node1 is running on node node1
Instance node2 is running on node node2
oracle 11g RAC : Archivelog save in OCFS2 (version 2) September 23, 2008
Posted by setijoagus in Oracle Install, oracle.Tags: archive log, ocfs2, oracle 11g, RAC
1 comment so far
oracle 11g RAC : Archivelog save in OCFS2 (version 2)
Oracle merelease file system ocfs2 yang dapat digunakan untuk keperluan cluster file system, jenis file system ini selain untuk keperluan grid computing (10g, 11g) juga dapat digunakan untuk keperluan saya, yaitu mengumpulkan archive file.
Saya kutip dari http://en.wikipedia.org/wiki/OCFS2
“OCFS (Oracle Cluster File System) It is a shared storage file system developed by Oracle Corporation and released under the GNU General Public License.
OCFS2 (version 2) was integrated into the version 2.6.16 of Linux kernel. Initially, it was marked as “experimental” (Alpha-test) code. This restriction was removed in Linux version 2.6.19.
OCFS2 uses a distributed lock manager which resembles the OpenVMS DLM but is much simpler.”
1. menyiapkan harddisk untuk archive (saya simpan di Storage Area Network / vmware disk share)
[root@node1 ~]# fdisk -l /dev/sdf
Command (m for help): n
Command action
e extended
p primary partition (1-4)
p
Partition number (1-4): 1
First cylinder (1-49278, default 1):
Using default value 1
Last cylinder or +size or +sizeM or +sizeK (1-49278, default 49278):
Command (m for help): w
2. format ocfs2 (hanya di lakukan di salah satu node)
[root@node1 ~]# mkfs.ocfs2 -F -b 4K -C 32K -N 4 -L oradatafiles /dev/sdf1
3. mountpoint otomatis, tambahkan di /etc/fstab, di semua node
[root@node1 ~]# vi /etc/fstab
/dev/sdf1 /u03 ocfs2 _netdev,datavolume 0 0
[root@node2 ~]# vi /etc/fstab
/dev/sdf1 /u03 ocfs2 _netdev,datavolume 0 0
4. konfigurasi agar ocfs run pada saat booting, di semua node
[root@node1 ~]# /etc/init.d/o2cb configure
Configuring the O2CB driver.
This will configure the on-boot properties of the O2CB driver.
The following questions will determine whether the driver is loaded on
boot. The current values will be shown in brackets (‘[]‘). Hitting
<ENTER> without typing an answer will keep that current value. Ctrl-C
will abort.
Load O2CB driver on boot (y/n) [n]: y
Cluster to start on boot (Enter “none” to clear) [ocfs2]:
Writing O2CB configuration: OK
Cluster ocfs2 already online
[root@node2 ~]# /etc/init.d/o2cb configure
Configuring the O2CB driver.
This will configure the on-boot properties of the O2CB driver.
The following questions will determine whether the driver is loaded on
boot. The current values will be shown in brackets (‘[]‘). Hitting
<ENTER> without typing an answer will keep that current value. Ctrl-C
will abort.
Load O2CB driver on boot (y/n) [n]: y
Cluster to start on boot (Enter “none” to clear) [ocfs2]:
Writing O2CB configuration: OK
Cluster ocfs2 already online
5. hak & security
[root@node1 ~]# mkdir /u03
[root@node1 ~]# mount /dev/sdf1 /u03 -t ocfs2
[root@node1 ~]# chown oracle.oinstall /u03
[root@node1 ~]# chmod 775 /u03
[root@node1 ~]# ls -ld /u03
[root@node2 ~]# mkdir /u03
[root@node2 ~]# mount /dev/sdf1 /u03 -t ocfs2
[root@node2 ~]# chown oracle.oinstall /u03
[root@node2 ~]# chmod 775 /u03
[root@node2 ~]# ls -ld /u03
6. Mengubah mode archive log pada Oracle 11g RAC
a. apakah database Instance masih hidup ?
[oracle@node1 ~]$ srvctl status database -d node
Instance node1 is running on node node1
Instance node2 is running on node node2
b. matikan database Instance
[oracle@node1 ~]$ srvctl stop database -d node
c. Menyiapkan salah satu database instance agar diset menjadi mode archive log, dengan mengirimkan perintah instance mount
[oracle@node1 ~]$ srvctl start instance -d node -i node1 -o mount
[oracle@node1 ~]$ srvctl status database -d node
Instance node1 is running on node node1
Instance node2 is not running on node node2
[oracle@node1 ~]$ sqlplus / as sysdba
SQL> alter database archivelog;
Database altered.
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination +DATA1
Oldest online log sequence 3
Current log sequence 4
SQL> exit
d. restart database instance
[oracle@node1 ~]$ srvctl stop instance -d node -i node1
[oracle@node1 ~]$ srvctl start database -d node
[oracle@node1 ~]$ srvctl status database -d node
Instance node1 is running on node node1
Instance node2 is running on node node2
7. Mengarahkan archive log pada Oracle 11g RAC ke folder tertentu
SQL> alter system set log_archive_dest_1=’location=/u03/archive01′;
System altered.
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /u03/archive01
Oldest online log sequence 42
Next log sequence to archive 43
Current log sequence 43
SQL> exit;
oracle 11g RAC : Transparent Application Failover (TAF) September 23, 2008
Posted by setijoagus in Oracle Administration, oracle.Tags: failover, oracle 11g, TAF
add a comment
oracle 11g RAC : Transparent Application Failover (TAF)
TAF, secara singkat jika client connect instance di RAC, dan mendapatkan instance node1 kemudian node1 down, maka client akan dilayani oleh node yang masih hidup instancenya dalam contoh ini node2
lengkapnya saya cuplik dari : http://wiki.oracle.com/page/Transparent+Application+Failover+(TAF)?t=anon
A runtime failover for high-availability environments, such as Oracle9i Real Application Clusters and Oracle Fail Safe, that refers to the failover and re-establishment of application-to-service connections. It enables client applications to automatically reconnect to the database if the connection fails, and, optionally, resume a SELECT statement that was in progress. This reconnect happens automatically from within the Oracle Call Interface (OCI) library.
1. setup tnsnames.ora di semua node
[oracle@node1 ~]$ vi $ORACLE_HOME/network/admin/tnsnames.ora
NODE =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = node1-vip)(PORT = 1521))
(ADDRESS = (PROTOCOL = TCP)(HOST = node2-vip)(PORT = 1521))
(LOAD_BALANCE = yes)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = node)
(FAILOVER_MODE =
(TYPE = SELECT)
(METHOD = BASIC)
(RETRIES = 180)
(DELAY = 5)
)
)
)
[oracle@node2 ~]$ vi $ORACLE_HOME/network/admin/tnsnames.ora
NODE =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = node1-vip)(PORT = 1521))
(ADDRESS = (PROTOCOL = TCP)(HOST = node2-vip)(PORT = 1521))
(LOAD_BALANCE = yes)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = node)
(FAILOVER_MODE =
(TYPE = SELECT)
(METHOD = BASIC)
(RETRIES = 180)
(DELAY = 5)
)
)
)
2. test koneksi
a. scott login di oracle 11g RAC (sqlplus no 1)
[oracle@node1 ~]$ sqlplus scott/tiger@node
SQL> select * from dept;
DEPTNO DNAME LOC
———- ————– ————-
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
b. scott dapat instance berapa di oracle 11g RAC ? (sqlplus no 2)
[oracle@node1 ~]$ sqlplus system/oracle0@node
SQL> column instance_name format a10
SQL> column host_name format a10
SQL> select instance_name, host_name,
2 NULL AS failover_type,
3 NULL AS failover_method,
4 NULL AS failed_over
5 FROM v$instance
6 UNION
7 SELECT NULL, NULL, failover_type, failover_method, failed_over
8 FROM v$session
9 WHERE username = ‘SCOTT’;
INSTANCE_N HOST_NAME FAILOVER_TYPE FAILOVER_M FAI
———- ———- ————- ———- —
node2 node2
SELECT BASIC NO
c. instance node 2 shutdown
[oracle@node1 ~]$ srvctl status database -d node
Instance node1 is running on node node1
Instance node2 is running on node node2
[oracle@node1 ~]$ srvctl stop instance -d node -i node2
[oracle@node1 ~]$ srvctl status database -d node
Instance node1 is running on node node1
Instance node2 is not running on node node2
d. apakah session scott di node2 juga down ?, ternyata tidak (sqlplus no 1)
SQL> /
DEPTNO DNAME LOC
———- ————– ————-
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
e. lha ?, kalau begitu sekarang scott di layani oleh instance berapa ? (sqlplus no 2)
SQL> /
INSTANCE_N HOST_NAME FAILOVER_TYPE FAILOVER_M FAI
———- ———- ————- ———- —
node1 node1
SELECT BASIC YES
oracle 11g RAC : Starting and Stopping Instances and RAC databases with srvctl and sql September 23, 2008
Posted by setijoagus in Oracle Administration, oracle.Tags: oracle 11g, RAC, srvctl, startup shutdown
add a comment
oracle 11g RAC : Starting and Stopping Instances and RAC databases with srvctl and sql
==================
A. srvctl
==================
1. status database with srvctl
[oracle@node1 ~]$ srvctl status database -d node
Instance node1 is running on node node1
Instance node2 is running on node node2
2. Stopping Instances and RAC databases with srvctl
[oracle@node1 ~]$ srvctl stop database -d node
3. status database with srvctl
[oracle@node1 ~]$ srvctl status database -d node
Instance node1 is not running on node node1
Instance node2 is not running on node node2
4. Starting Instances and RAC databases with srvctl
[oracle@node1 ~]$ srvctl start database -d node
5. status database with srvctl
[oracle@node1 ~]$ srvctl status database -d node
Instance node1 is running on node node1
Instance node2 is running on node node2
6. Stopping Instances and RAC databases with srvctl (not all instance)
[oracle@node1 ~]$ srvctl stop instance -d node -i node2
7. status database with srvctl
[oracle@node1 ~]$ srvctl status database -d node
Instance node1 is running on node node1
Instance node2 is not running on node node2
8. Starting Instances and RAC databases with srvctl (not all instance)
[oracle@node1 ~]$ srvctl start instance -d node -i node2
9. status database with srvctl
[oracle@node1 ~]$ srvctl status database -d node
Instance node1 is running on node node1
Instance node2 is running on node node2
==================
B. sql command
==================
10. status database with sql
[oracle@node1 ~]$ sqlplus system/oracle0@node1
sql> column instance_name format a10
sql> column host_name format a10
sql> column archiver format a10
sql> column status format a10
sql> select instance_name, host_name, archiver, thread#, status from gv$instance;
INSTANCE_N HOST_NAME ARCHIVER THREAD# STATUS
———- ———- ———- ———- ———-
node1 node1 STARTED 1 OPEN
node2 node2 STARTED 2 OPEN
sql> exit;
11. Stopping Instances and RAC databases with sql (not all instance, in ex : node1)
[oracle@node1 ~]$ sqlplus sys/oracle0@node1 as sysdba
sql> shutdown;
sql> exit;
[oracle@node1 ~]$
12. status database with srvctl
[oracle@node1 ~]$ sqlplus system/oracle0@node2
sql> column instance_name format a10
sql> column host_name format a10
sql> column archiver format a10
sql> column status format a10
sql> select instance_name, host_name, archiver, thread#, status from gv$instance;
INSTANCE_N HOST_NAME ARCHIVER THREAD# STATUS
———- ———- ———- ———- ———-
node2 node2 STARTED 2 OPEN
sql> exit;
[oracle@node1 ~]$
13. Starting Instances and RAC databases with sql (not all instance, in ex : node1)
[oracle@node1 ~]$ sqlplus sys/oracle0@node1 as sysdba
SQL*Plus: Release 11.1.0.6.0 – Production on Tue Sep 23 13:46:08 2008
Copyright (c) 1982, 2007, Oracle. All rights reserved.
ERROR:
ORA-12521: TNS:listener does not currently know of instance requested in
connect descriptor
Enter user-name: / as sysdba
Connected to an idle instance.
SQL> startup;
ORACLE instance started.
Total System Global Area 853716992 bytes
Fixed Size 1303244 bytes
Variable Size 557845812 bytes
Database Buffers 289406976 bytes
Redo Buffers 5160960 bytes
Database mounted.
Database opened.
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 – Production
With the Partitioning, Real Application Clusters, OLAP, Data Mining
and Real Application Testing options
[oracle@node1 ~]$
=========================================
Note use command : sqlplus / as sysdba
=========================================
ERROR:
ORA-12521: TNS:listener does not currently know of instance requested in
connect descriptor
14. status database with sql
[oracle@node1 ~]$ sqlplus system/oracle0@node2
sql> column instance_name format a10
sql> column host_name format a10
sql> column archiver format a10
sql> column status format a10
sql> select instance_name, host_name, archiver, thread#, status from gv$instance;
INSTANCE_N HOST_NAME ARCHIVER THREAD# STATUS
———- ———- ———- ———- ———-
node2 node2 STARTED 2 OPEN
node1 node1 STARTED 1 OPEN
