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

Follow

Get every new post delivered to your Inbox.