Archive | Oracle Administration RSS feed for this section

MOVE UNDO TABLESPACE

3 Jan

1. periksa parameter UNDO
SQL> show parameter undo
NAME                                 TYPE        VALUE
———————————— ———– ——————————
undo_management                      string      AUTO
undo_retention                       integer     900
undo_tablespace                      string      UNDOTBS4
Continue reading

MOVE TABLESPACE TEMP

3 Jan

1. periksa besaran TABLESPACE TEMP
SQL> select TABLESPACE_NAME, BYTES_USED, BYTES_FREE from V$TEMP_SPACE_HEADER;
TABLESPACE_NAME                BYTES_USED BYTES_FREE
—————————— ———- ———-
TEMP                           3.4193E+10  165675008
Continue reading

error ORA-00257

9 Oct

1. ERROR: ORA-00257
SQL> conn scott/tiger
ERROR:
ORA-00257: archiver error. Connect internal only, until freed.

2. shutdown database normal
SQL> shutdown;
Database closed.
Database dismounted.
ORACLE instance shut down.
Continue reading

error ORA-16014

9 Oct

error ORA-16014

1. error ORA-16014
SQL> startup;
ORACLE instance started.
Total System Global Area 1519898624 bytes
Fixed Size 2144624 bytes
Variable Size 872416912 bytes
Database Buffers 637534208 bytes
Redo Buffers 7802880 bytes
Database mounted.
ORA-16014: log 2 sequence# 2 not archived, no available destinations
ORA-00312: online log 2 thread 1: ‘/u02/perak/redo02.log’
Continue reading

Apa beda Oracle DBA sama Oracle Developer

1 Mar

sumber : mailing list = indo-oracle@yahoogroups.com
peulis = yulius_wibowo@yahoo.com
saya sadur untuk menambah wawasan kita bersama

==============
A. Oracle DBA:
==============
0. Memahami konsep Oracle Database
(saya pakai nomor urut “0” karena ini sbg pondasi utamanya)
– Minimal istilah2 yg dipakai spt: instance, database, datafile, controlfile, log file, SGA, PGA,listener,…
Anda bisa memperlajarinya sendiri atau ikut kursus.

1. Instalasi software berikut patching dan upgradenya
contoh:
– Instalasi Oracle binary/software (pada server, mungkin juga pada client )
– Apply patch kalau ada bug
– Upgrade versi
– Optional: Migrasi ke platform yang berbeda
– Optional: Cloning (misal: dari lingkungan “production” ke “development”)

2. Konfigurasi Instance & Database
contoh:
– Penentuan nama instance, nama database
– Pengaturan besarnya memory (SGA, PGA, SharedPool, Buffer Pool,…)
– Pengaturan listener: service name,port number,… berikut start/stop-nya
– Pengaturan koneksi dari client dan atau dari server aplikasi (misal: dedicated atau shared server)
– Optional: Konfigurasi RAC, Standby database/Data Guard …
– …

3. Pengaturan struktur database, baik secara logical maupun physical
contoh:
Logical:
– Tablespace : alokasi besarnya tablespace, penentuan cara pengalokasian extentnya, pengaturan quotanya, …
– Segment : alokasi extent spt. table, index, materialized view, …
– …

Physical:
– Pengaturan lokasi, besar dan jumlah datafiles, tempfiles, logfiles, archived log files,…
– …

4. Pengaturan security
contoh:
– pembuatan user
– pembuatan role
– pengaturan privilege
– pengaturan profile
– pengaturan ekspirasi password
– pengaturan penggunaan resource (Resource Manager)
– pengaturan quota
– …

4. Performance Tuning
– Pengaturan optimizer di level instance
– Optimasi memory (shared pool, buffer pool, log buffer, SGA, PGA, …)
– Optimasi I/O (distribusi datafiles, log files)
– Monitoring performance: bisa manual pakai statspack, awr atau pakai Database Control/OEM
– …

5. Backup & Recovery
– Menentukan backup dan recovery strategy/plan
– Physical backup (offline/online)
– Logical backup (exp/imp, expdp/impdp)
– Recovery database jika terjadi kerusakan (comple recovery, incomplete recovery)
– Optional: Penentuan disaster recovery plan
– Optional: Penggunaan tools dari Oracle (RMAN) atau third-party tools utk backup/restore ke tape/disk
– …

6. Troubleshooting
– Masalah koneksi dari client ke database server, atau dari server aplikasi ke database server
– Masalah alokasi space kalau kurang
– …

7. Support user & developer
– Paham dan bisa menggunakan SQL & PL/SQL
– Membantu developer dalam hal tuning aplikasi (SQL atau PL/SQL)
– …

8. Bisa menggunakan tools di level OS sesuai dgn platform yg dipakai
contoh:
– UNIX/LINUX: ls, cp, ps, vmstat, ipcs,…
– Bisa buat shell script (biasanya dipakai utk tugas2 yg rutin)
– …

====================
B. Oracle Developer:
====================

- Menguasai SQL & PL/SQL serta tool-nya (minimal SQL*Plus)
– Paham akan konsep client/server dan atau N-Tier
– Diharapkan bisa menginstal Oracle Client software atau Oracle Developer (Forms/Report)
berikut konfigurasi utk dapat konek ke database server (minimal paham istilah tnsnames, sid, servicename, database)
– Optional: Kalau menggunakan Oracle Forms/Report: mengerti soal Oracle Application Server (Oracle HTTP Server(Apache), Servlet, JSP, CGI, OC4J,…)
– Optional: bisa menggunakan bahasa pemrograman lainya (Java/PHP/VB…)
– …

startup database & listener oracle 11g ubuntu 9.04

4 Jan

1. login as oracle user
ubs@ubs-desktop:~$ sudo su – root
root@ubs-desktop:~# su – oracle

2. startup lsnrctl
oracle@ubs-desktop:~$ lsnrctl start
LSNRCTL for Linux: Version 11.1.0.6.0 – Production on 04-JAN-2010 09:24:04
Copyright (c) 1991, 2007, Oracle. All rights reserved.
Starting /u01/app/oracle/product/11.1.0/db_1/bin/tnslsnr: please wait…
TNSLSNR for Linux: Version 11.1.0.6.0 – Production
System parameter file is /u01/app/oracle/product/11.1.0/db_1/network/admin/listener.ora
Log messages written to /u01/app/oracle/diag/tnslsnr/ubs-desktop/listener/alert/log.xml
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=ubs-desktop)(PORT=1521)))
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=ubs-desktop)(PORT=1521)))
STATUS of the LISTENER
————————
Alias LISTENER
Version TNSLSNR for Linux: Version 11.1.0.6.0 – Production
Start Date 04-JAN-2010 09:24:06
Uptime 0 days 0 hr. 0 min. 0 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /u01/app/oracle/product/11.1.0/db_1/network/admin/listener.ora
Listener Log File /u01/app/oracle/diag/tnslsnr/ubs-desktop/listener/alert/log.xml
Listening Endpoints Summary…
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=ubs-desktop)(PORT=1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
The listener supports no services
The command completed successfully
Continue reading

ORA-01031: insufficient privileges

4 Jan

ORA-01031: insufficient privileges

oracle@ubs-desktop:~$ sqlplus / as sysdba
SQL*Plus: Release 11.1.0.6.0 – Production on Mon Jan 4 09:25:38 2010
Copyright (c) 1982, 2007, Oracle. All rights reserved.
ERROR:
ORA-01031: insufficient privileges

Continue reading

The listener supports no services

27 Feb

Kegagalan listener start “The listener supports no services”

1. pada saat instance database sudah up dan listener juga start, kadang kita menemukan error “The listener supports no services”
[oracle@stby11g ~]$ lsnrctl status
LSNRCTL for Linux: Version 11.1.0.6.0 – Production on 27-FEB-2009 20:19:31
Copyright (c) 1991, 2007, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=172.20.22.21)(PORT=1521)))
STATUS of the LISTENER
————————
Alias LISTENER
Version TNSLSNR for Linux: Version 11.1.0.6.0 – Production
Start Date 27-FEB-2009 20:19:22
Uptime 0 days 0 hr. 0 min. 9 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /u01/app/oracle/product/11.1.0/db_1/network/admin/listener.ora
Listener Log File /u01/app/oracle/diag/tnslsnr/stby11g/listener/alert/log.xml
Listening Endpoints Summary…
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=172.20.22.21)(PORT=1521)))
The listener supports no services
The command completed successfully

2. hal ini terjadi karena default install linux /etc/hosts-nya adalah
127.0.0.1 localhost.localdomain localhost stby11g  edit dan sesuaikan seperti di bawah ini :

127.0.0.1 localhost.localdomain localhost

atau lengkapnya seperti berikut :

[oracle@stby11g ~]$ su – root
Password:
[root@stby11g ~]# vi /etc/hosts
# Do not remove the following line, or various programs
# that require network functionality will fail.
127.0.0.1 localhost.localdomain localhost
# Public
172.20.22.21 stby11g.localdomain stby11g
[root@stby11g ~]# exit

3. reload listener
[oracle@stby11g ~]$ lsnrctl reload
LSNRCTL for Linux: Version 11.1.0.6.0 – Production on 27-FEB-2009 20:29:33
Copyright (c) 1991, 2007, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=172.20.22.21)(PORT=1521)))
The command completed successfully

4. status listener sekarang
[oracle@stby11g ~]$ lsnrctl status
LSNRCTL for Linux: Version 11.1.0.6.0 – Production on 27-FEB-2009 20:29:35
Copyright (c) 1991, 2007, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=172.20.22.21)(PORT=1521)))
STATUS of the LISTENER
————————
Alias LISTENER
Version TNSLSNR for Linux: Version 11.1.0.6.0 – Production
Start Date 27-FEB-2009 20:27:48
Uptime 0 days 0 hr. 1 min. 46 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /u01/app/oracle/product/11.1.0/db_1/network/admin/listener.ora
Listener Log File /u01/app/oracle/diag/tnslsnr/stby11g/listener/alert/log.xml
Listening Endpoints Summary…
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=172.20.22.21)(PORT=1521)))
Services Summary…
Service “stby11g” has 1 instance(s).
Instance “stby11g”, status READY, has 1 handler(s) for this service…
Service “stby11g_XPT” has 1 instance(s).
Instance “stby11g”, status READY, has 1 handler(s) for this service…
The command completed successfully

Menghapus archive log dengan paksa

24 Feb

1. dimana letak archive log ?
[oracle@stby11g ~]$ sqlplus / as sysdba
SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /u01/archive/stby11g
Oldest online log sequence 0
Next log sequence to archive 0
Current log sequence 15

2. hapus
[oracle@stby11g ~]$ cd /u01/archive/stby11g
[oracle@stby11g stby11g]$ ls
1_14_679442351.arc 1_11_679442351.arc 1_9_679442351.arc
1_12_679442351.arc 1_10_679442351.arc 1_13_679442351.arc
[oracle@stby11g stby11g]$ rm *
[oracle@stby11g stby11g]$ ls

3. periksa archive log lewat rman menggunakan crosscheck copy
[oracle@prmr11g prmr11g]$ rman target /
RMAN> crosscheck copy;
Starting implicit crosscheck backup at 24-FEB-09
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=115 device type=DISK
Finished implicit crosscheck backup at 24-FEB-09

Starting implicit crosscheck copy at 24-FEB-09
using channel ORA_DISK_1
Finished implicit crosscheck copy at 24-FEB-09

searching for all files in the recovery area
cataloging files…
cataloging done

List of Cataloged Files
=======================
File Name: /u01/app/oracle/flash_recovery_area/stby11g/STBY11G/archivelog/2009_02_20/o1_mf_1_3_4sx7gsl5_.arc
File Name: /u01/app/oracle/flash_recovery_area/stby11g/STBY11G/archivelog/2009_02_20/o1_mf_1_4_4sxj42j8_.arc
File Name: /u01/app/oracle/flash_recovery_area/stby11g/STBY11G/archivelog/2009_02_20/o1_mf_1_2_4sx6k6kl_.arc

released channel: ORA_DISK_1
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=115 device type=DISK
validation failed for archived log
archived log file name=/u01/archive/stby11g/1_9_679442351.arc RECID=3 STAMP=679675797
validation failed for archived log
archived log file name=/u01/archive/stby11g/1_10_679442351.arc RECID=5 STAMP=679675812
validation failed for archived log
archived log file name=/u01/archive/stby11g/1_11_679442351.arc RECID=2 STAMP=679675797
validation failed for archived log
archived log file name=/u01/archive/stby11g/1_12_679442351.arc RECID=4 STAMP=679675806
validation failed for archived log
archived log file name=/u01/archive/stby11g/1_13_679442351.arc RECID=6 STAMP=679675821
validation failed for archived log
archived log file name=/u01/archive/stby11g/1_14_679442351.arc RECID=1 STAMP=679675771
Crosschecked 6 objects

4. hapus data archive log lewat rman menggunakan delete noprompt expired copy
RMAN> delete noprompt expired copy;
released channel: ORA_DISK_1
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=115 device type=DISK
List of Archived Log Copies for database with db_unique_name STBY11G
=====================================================================
Key Thrd Seq S Low Time
——- —- ——- – ———
3 1 9 X 23-FEB-09
Name: /u01/archive/stby11g/1_9_679442351.arc
5 1 10 X 23-FEB-09
Name: /u01/archive/stby11g/1_10_679442351.arc
2 1 11 X 23-FEB-09
Name: /u01/archive/stby11g/1_11_679442351.arc
4 1 12 X 23-FEB-09
Name: /u01/archive/stby11g/1_12_679442351.arc
6 1 13 X 24-FEB-09
Name: /u01/archive/stby11g/1_13_679442351.arc
1 1 14 X 24-FEB-09
Name: /u01/archive/stby11g/1_14_679442351.arc
deleted archived log
archived log file name=/u01/archive/stby11g/1_9_679442351.arc RECID=3 STAMP=679675797
deleted archived log
archived log file name=/u01/archive/stby11g/1_10_679442351.arc RECID=5 STAMP=679675812
deleted archived log
archived log file name=/u01/archive/stby11g/1_11_679442351.arc RECID=2 STAMP=679675797
deleted archived log
archived log file name=/u01/archive/stby11g/1_12_679442351.arc RECID=4 STAMP=679675806
deleted archived log
archived log file name=/u01/archive/stby11g/1_13_679442351.arc RECID=6 STAMP=679675821
deleted archived log
archived log file name=/u01/archive/stby11g/1_14_679442351.arc RECID=1 STAMP=679675771
Deleted 6 EXPIRED objects

5. periksa ulang
RMAN> crosscheck copy;
released channel: ORA_DISK_1
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=115 device type=DISK
specification does not match any archived log in the recovery catalog
RMAN> exit

jika anda jalankan perintah sql, seperti dibawah ini maka isi v$archived_log, seolah – olah masih ada
SQL> select name from v$archived_log;
NAME
——————————————————————————–
6 rows selected.

hal ini berhubungan dengan :
1) V$ARCHIVED_LOG
2) V$CONTROLFILE_RECORD_SECTION

deatil bisa diperiksa di
http://forums.oracle.com/forums/thread.jspa?threadID=621999&start=0&tstart=0

Moving Tablespaces Oracle

2 Jan

source : http://home.galileo.edu/~obonilla/writing/oracle/
Kadangkala pada saat kita membuat / install database, create tablespace-nya pakai default tablespace “USERS”, tetapi kemudian hari karena suatu hal (tablespace users berkembang tanpa kendali) maka diinginkan untuk memindahkan tablespaceke suatu tablespace baru. Atau alasan lain seperti memisahkan skema tablespace “PRODUCTION” ke “DEVELOPMENT”.

Test….test….
1. create tablespace
$ sqlplus / as sysdba
sql> CREATE TABLESPACE development DATAFILE
‘/u01/oradata/test/datafile/development01.dbf’ SIZE 50M AUTOEXTEND OFF
LOGGING
ONLINE
PERMANENT
EXTENT MANAGEMENT LOCAL AUTOALLOCATE
BLOCKSIZE 8K
SEGMENT SPACE MANAGEMENT AUTO
FLASHBACK ON;

2. create user
sql> CREATE USER development IDENTIFIED BY password
DEFAULT TABLESPACE development
TEMPORARY TABLESPACE TEMP
PROFILE DEFAULT
ACCOUNT UNLOCK;
sql> GRANT RESOURCE TO development;
sql> GRANT CONNECT TO development;
sql> ALTER USER development DEFAULT ROLE ALL;
sql> GRANT CREATE ANY VIEW TO development;
sql> GRANT UNLIMITED TABLESPACE TO development;
sql> GRANT ALTER SESSION TO development;
sql> GRANT SELECT ANY DICTIONARY TO development;
sql> EXIT;

3. export dmp
$ exp userid=production/password file=test.dmp log=test.txt

4. edit dmp pada file test.dmp
dimana setiap kata “PRODUCTION” harus diganti “DEVELOPMENT”, tetapi karena keterbatasan untuk membuka file besar, maka harus displit dulu.
$ split -b100m test.dmp
$ ls
test.dmp xad xah xal xap xat xax xbb xbf xbj xbn xbr
xaa xae xai xam xaq xau xay xbc xbg xbk xbo xbs
xab xaf xaj xan xar xav xaz xbd xbh xbl xbp
xac xag xak xao xas xaw xba xbe xbi xbm xbq

a. LINUX editor : emacs
b. Windows editor : NOTEPAD++

rest_dmp1

atau :
c. Windows Tools : A.F.9 http://www.fauland.com
tools cari dan replace, tanpa open file, tools ini yang saya sarankan
thank’s u/ rekanku (Eko Yuliantoro R) atas info nya

rest_dmp21

5. restore dmp
$ imp system/password FROMUSER=production TOUSER=development FILE=test.dmp LOG=test.txt

Follow

Get every new post delivered to your Inbox.