Problem solving dbconsole oracle 10g

Anda cek direktori /OH/oc4j/j2ee instead of OC4J_DBConsole_yy.co.id_xxxx ?

Try renaming that directory to OC4J_DBConsole_yy.co.id_xxxx anda coba untuk stop dan start dbconsole.

Berikut cara simple untuk drop and recreate repository.

#drop (menghapus dbconsole yang sudah ada)
emca -deconfig dbcontrol db -repos drop

#create (membuat baru dbconsole)
emca -config dbcontrol db -repos create

#recreate (configurasi ulang dbconsole)
emca -config dbcontrol db -repos recreate

Semoga bermanfaat & selamat mencoba

Skrip untuk membuat synonym dari object

Ketik skrip berikut untuk membuat perintah synonym:

Select 'Create Public synonym ' || object_name || ' for ' || object_name || '; '
From user_objects
Where object_type IN ('TABLE','VIEW','FUNCTION','PROCEDURE')

Hasil dari query copy aja lansung pada query editor, kemudian jalankan.

Selamat mencoba...

PHP and oci8 on Ubuntu

OCI8 is an extension for providing APIs to Oracle database management system. However, installing OCI8 is not just easy like apt-get since it requires SDK offered by Oracle and its distribution license prohibits the inclusion to linux distribution. That means we have to download and compile it by ourselves. Fortunately, it is a little bit easy for Ubuntu.

1. Install PEAR and PECL
   

   sudo apt-get install php-pear
   

2. Download Oracle Instant Client. You need at least Basic and SDK.
   

3. Extract the archives.
   

   sudo mkdir -p /opt/oracle
   cd /opt/oracle
   sudo unzip instantclient-basic-linux-x86-64-10.2.0.3-20070103.zip
   sudo unzip instantclient-sdk-linux-x86-64-10.2.0.3-20070103.zip
   sudo mv /opt/oracle/instantclient_10_2 /opt/oracle/instantclient
   

4. Create symbolic links.
   

   cd /opt/oracle/instantclient
   sudo ln -s libclntsh.so.10.1 libclntsh.so
   sudo ln -s libocci.so.10.1 libocci.so
   

5. Add instant client to system ld.
   

   sudo su -
   echo /opt/oracle/instantclient > /etc/ld.so.conf.d/oracle-instantclient
   

6. Compile oci8.
   

   sudo pecl install oci8
   

7. Enter instantclient,/opt/oracle/instantclient when you are prompted.
   

8. Enable the extension.
   

   sudo su -
   echo extension=oci8.so >> /etc/php5/apache2/php.ini
   

9. Restart apache2.
   

   sudo /etc/init.d/apache2 restart
   

Install Oracle Application Server di CentOS 5

1. Login Sebagi root

/usr/sbin/groupadd oinstall
/usr/sbin/groupadd dba
/usr/sbin/groupadd oper
useradd -g oinstall -G dba oracle
passwd oracle
mkdir -p /u01/app/oracle/product/j2ee_10_1_2
chown -R oracle.oinstall /u01

Edit /etc/redhat-release dan tambahkan ganti dengan kalimat sebagai berikut:
Red Hat Linux release 4.0 (Nathan)

2. Setting Sistem Parameter
Edit /etc/sysctl.conf dan tambahkan baris berikut:

kernel.shmall = 2097152
kernel.shmmax = 2147483648
kernel.shmmni = 4096
# semaphores: semmsl, semmns, semopm, semmni
kernel.sem = 256 32000 100 142
fs.file-max = 131072
net.ipv4.ip_local_port_range = 10000 65000
kernel.msgmni = 2878
kernel.msgmax = 8192
kernel.msgmnb = 65535

Kemudian eksekusi dengan perintah sbb:
[root@localhost /]# /sbin/sysctl -p

Edit /etc/pam.d/login dan tambahkan baris berikut:
session    required     pam_limits.so

Edit /etc/security/limits.conf dan tambahkan baris berikut:

oracle    soft  nproc  2047
oracle    hard  nproc  16384
oracle    soft  nofile  1024
oracle    hard  nofile  65536

3. Setting Oracle Environment
Edit /home/oracle/.bash_profile dan tambahkan baris berikut:
# Configurasi untuk Oracle Application
TMP=/tmp; export TMP
TMPDIR=$TMP; export TMPDIR
ORACLE_BASE=/u01/app/oracle; export ORACLE_BASE
ORACLE_HOME=$ORACLE_BASE/product/j2ee_10_1_2; export ORACLE_HOME
ORACLE_TERM=xterm; export ORACLE_TERM
PATH=/usr/sbin:$ORACLE_HOME/bin:$PATH; export PATH
PATH=$PATH:$ORACLE_HOME/dcm/bin:$ORACLE_HOME/opmn/bin; export PATH
PATH=$PATH:$ORACLE_HOME/Apache/Apache/bin; export PATH
export DISPLAY=:0.0

if [ $USER = "oracle" ]; then
if [ $SHELL = "/bin/ksh" ]; then
  ulimit -p 16384
  ulimit -n 16384
else
  ulimit -u 16384 -n 16384
fi
fi

PS1="`hostname`> "
set -o emacs
set filec

4. Cek module rpm yang dibutuhkan untuk instalasi oracle
Catatan: Sejak RHEL 5 (OEL 5, Centos 5) pdksh package diganti menjadi ksh
copy paste di konsole perintah berikut:
rpm -q binutils gcc glibc glibc-headers glibc-kernheaders glibc-devel
compat-libstdc++ cpp compat-gcc make compat-db compat-gcc-c++
compat-libstdc++ compat-libstdc++-devel setarch sysstat pdksh libaio
libaio-devel --qf '%{name}.%{arch}\n'|sort

Berikut daftar paket yang dibutuhkan:
binutils.i386
compat-gcc-7.3-2.96.128.i386
compat-gcc-c++-7.3-2.96.128.i386
compat-libstdc++-7.3-2.96.128.i386
compat-libstdc++-devel-7.3-2.96.128.i386
cpp.i386
gcc.i386
gcc-c++.i386
glibc.i386
glibc-common.i386
glibc-devel.i386
glibc-headers.i386
glibc-kernheaders.i386
libstdc++.i386
libstdc++-devel.i386
libaio
libai-devel.i386
pdksh.i386
setarch.i386
sysstat.i386

Berikut adalah cara untuk insallasi paket rpm:
cd /media/DVD2-IL_04-2008/CentOS/
# rpm -Uvh libXP-1*
# rpm -Uvh compat-db-4*
# rpm -Uvh openmotif-2*

Catatan : Jika paket tidak ditemukan silahkan download paket di Centos Linux 5
Untuk Install paket yang diperlukan oleh Oracle Application hampir sama dengan Oracle Database

5. Memulai proses instalasi oracle
Jika anda sudah mempunyai Paket Oracle Application dalam bentuk cd
cp -rf /media/Disk1/application_server /home/oracle/

Jika anda sudah punya paket dalam bentuk format yang lain
cpio -idmv <>>> Could not execute auto check for display colors using command /usr/X11R6/bin/xdpyinfo. Check if the DISPLAY variable is set.
Checking if CPU speed is above 450 MHz.    Actual 3000 MHz    Passed

Some optional pre-requisite checks have failed (see above). Continue? (y/n) [n]

Anda pilih ya atau tekan y, karena 256 colors untuk proses intalasi sudah cukup

Install Database Oracle 10g R2 di CentOS 5

1. Login sebagai root:

/usr/sbin/groupadd oinstall
/usr/sbin/groupadd dba
/usr/sbin/groupadd oper
useradd -g oinstall -G dba oracle
passwd oracle
mkdir -p /u01/app/oracle/product/10.2.0/db_1
chown -R oracle.oinstall /u01
mkdir -p /u02/oradata
chown -R oracle.oinstall /u02

Edit /etc/redhat-release dan tambahkan ganti dengan kalimat sebagai berikut:
Red Hat Linux release 4.0 (Nathan)

2. Setting Sistem Parameter
Edit /etc/sysctl.conf dan tambahkan baris berikut:

kernel.shmall = 2097152
kernel.shmmax = 2147483648
kernel.shmmni = 4096
kernel.sem = 250 32000 100 128
fs.file-max = 65536
net.ipv4.ip_local_port_range = 1024 65000
net.core.rmem_default = 262144
net.core.rmem_max = 262144
net.core.wmem_default = 262144
net.core.wmem_max = 262144

Kemudian eksekusi dengan perintah sbb:
[root@localhost /]# /sbin/sysctl -p

Edit /etc/pam.d/login dan tambahkan baris berikut:
session    required     pam_limits.so

Edit /etc/security/limits.conf dan tambahkan baris berikut:

oracle    soft  nproc  2047
oracle    hard  nproc  16384
oracle    soft  nofile  1024
oracle    hard  nofile  65536

3. Setting Oracle Environment
Edit /home/oracle/.bash_profile dan tambahkan baris berikut:

TMP=/tmp; export TMP
TMPDIR=$TMP; export TEMPDIR

ORACLE_BASE=/u01/app/oracle; export ORACLE_BASE
ORACLE_HOME=$ORACLE_BASE/product/10.2.0/db_1; export ORACLE_HOME
ORACLE_SID=orcl; export ORACLE_SID
ORACLE_TERM=xterm; export ORACLE_TERM
LD_LIBRARY_PATH=$ORACLE_HOME/lib; export LD_LIBRARY_PATH
PATH=/usr/sbin:$PATH; export PATH
PATH=$ORACLE_HOME/bin:$PATH; export PATH
export DISPLAY=:0.0

4. Cek module rpm yang dibutuhkan untuk instalasi oracle
Catatan: Sejak RHEL 5 (OEL 5, Centos 5) pdksh package diganti menjadi ksh
copy paste di konsole perintah berikut:
rpm -q binutils gcc glibc glibc-headers glibc-kernheaders glibc-devel
compat-libstdc++ cpp compat-gcc make compat-db compat-gcc-c++
compat-libstdc++ compat-libstdc++-devel setarch sysstat pdksh libaio
libaio-devel --qf '%{name}.%{arch}\n'|sort

Berikut daftar paket yang dibutuhkan:
binutils.i386
compat-gcc-7.3-2.96.128.i386
compat-gcc-c++-7.3-2.96.128.i386
compat-libstdc++-7.3-2.96.128.i386
compat-libstdc++-devel-7.3-2.96.128.i386
cpp.i386
gcc.i386
gcc-c++.i386
glibc.i386
glibc-common.i386
glibc-devel.i386
glibc-headers.i386
glibc-kernheaders.i386
libstdc++.i386
libstdc++-devel.i386
libaio
libai-devel.i386
pdksh.i386
setarch.i386
sysstat.i386

Berikut adalah cara untuk insallasi paket rpm:
cd /media/DVD2-IL_04-2008/CentOS/
# rpm -Uvh libXP-1*
# rpm -Uvh compat-db-4*
# rpm -Uvh openmotif-2*

Catatan : Jika paket tidak ditemukan silahkan download paket di Centos Linux 5

5. Memulai proses instalasi oracle
cp -rf /media/oracle_database_10g/database /home/oracle/database

xhost +
xhost localhost:oracle
su - oracle
cd /home/oracle/database
./runInstaller

Recover REDOLOG

SQL> alter database open;

alter database open

*

ERROR at line 1:

ORA-00322: log 5 of thread 1 is not current copy

ORA-00312: online log 5 thread 1: 'blablabla/blablabla/db01/redo/redo05.log'

Bueno. No importa. Acabo de hacer un backup en frío, así que:

SQL> alter database drop logfile group 5;

alter database drop logfile group 5

*

ERROR at line 1:

ORA-00350: log 5 of instance db01 (thread 1) needs to be archived

ORA-00312: online log 5 thread 1: 'blablabla/blablabla/db01/redo/redo05.log'

¡Coño! Pues quito el archive y ya está:

SQL> alter system archive log stop;

System altered.

SQL>  alter database drop logfile group 5;

alter database drop logfile group 5

*

ERROR at line 1:

ORA-00350: log 5 of instance db01 (thread 1) needs to be archived

ORA-00312: online log 5 thread 1: 'blablabla/blablabla/db01/redo/redo05.log'

A ver si archivando a mano…

SQL> archive log all

ORA-16038: log 5 sequence# 1875 cannot be archived

ORA-00312: online log 5 thread 1: 'blablabla/blablabla/db01/redo/redo05.log'SQL> ALTER DATABASE OPEN;

ALTER DATABASE OPEN

*

ERROR at line 1:

ORA-00322: log 5 of thread 1 is not current copy

ORA-00312: online log 5 thread 1: 'blablabla/blablabla/db01/redo/redo05.log'

¡Joder! Esto se pone feo… ¿Cómo me cepillo el redolog file pocho sin necesidad de recuperar desde RMAN?

A ver si así…

SQL> SHUTDOWN IMMEDIATE;

ORA-01109: database not open

Database dismounted.

ORACLE instance shut down.

SQL> startup mount;

ORA-32004: obsolete and/or deprecated parameter(s) specified

ORACLE instance started.

Total System Global Area 1610612736 bytes

Fixed Size                  1262068 bytes

Variable Size             201330188 bytes

Database Buffers         1392508928 bytes

Redo Buffers               15511552 bytes

Database mounted.

SQL> recover database until cancel;

Media recovery complete.

SQL> alter database open resetlogs;

Database altered.

SQL> alter system archive log start;

System altered.

SQL> alter system switch logfile;

System altered.

SQL>  alter system switch logfile;

System altered.

SQL>  alter system switch logfile;

System altered.

SQL>  alter system switch logfile;

System altered.

SQL>  alter system switch logfile;

System altered.

SQL>  alter system switch logfile;

System altered.

SQL> select sysdate from dual;

SYSDATE

--------

08/10/07

ORA-01157+ORA-01110

G:\Documents and Settings\Administrator>sqlplus /nolog

SQL*Plus: Release 10.2.0.1.0 - Production on 星期一 9月 18 11:14:18 2006

Copyright (c) 1982, 2005, Oracle. All rights reserved.

SQL> connect system/1234 as sysdba;
已連線.

SQL> shutdown immediate;
ORA-01109: 尚未開啟此一資料庫

資料庫已卸載.
已關閉 ORACLE 執行處理.

SQL> startup;
ORACLE 執行處理已啟動.

Total System Global Area 293601280 bytes
Fixed Size 1248600 bytes
Variable Size 142607016 bytes
Database Buffers 142606336 bytes
Redo Buffers 7139328 bytes
資料庫已掛載.
ORA-01157: 無法識別/鎖定資料檔 6 - 請參閱 DBWR 追蹤檔
ORA-01110: 資料檔 6: 'C:\ORACLE\PRODUCT\10.2.0\ORADATA\USER\TEST.DBF'

SQL>startup mount;

SQL> recover database until cancel;
ORA-00283: 發生錯誤, 取消復原階段作業
ORA-01110: 資料檔 6: 'C:\ORACLE\PRODUCT\10.2.0\ORADATA\USER\TEST.DBF'
ORA-01157: 無法識別/鎖定資料檔 6 - 請參閱 DBWR 追蹤檔
ORA-01110: 資料檔 6: 'C:\ORACLE\PRODUCT\10.2.0\ORADATA\USER\TEST.DBF'

SQL> select name from v$datafile;

NAME
--------------------------------------------------------------------------------

C:\ORACLE\PRODUCT\10.2.0\ORADATA\USER\SYSTEM01.DBF
C:\ORACLE\PRODUCT\10.2.0\ORADATA\USER\UNDOTBS01.DBF
C:\ORACLE\PRODUCT\10.2.0\ORADATA\USER\SYSAUX01.DBF
C:\ORACLE\PRODUCT\10.2.0\ORADATA\USER\USERS01.DBF
C:\ORACLE\PRODUCT\10.2.0\ORADATA\USER\EXAMPLE01.DBF
C:\ORACLE\PRODUCT\10.2.0\ORADATA\USER\TEST.DBF

已選取 6 個資料列.

SQL> alter database create datafile 'C:\ORACLE\PRODUCT\10.2.0\ORADATA\USER\TEST.DBF';

已更改資料庫.

SQL> recover database until cancel;
ORA-00279: 必須變更 1170455 建立於 09/07/2006 09:54:52 上 (對於繫線 1 而言)
ORA-00289: 建議 :
C:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA\USER\ARCHIVELOG\2006_09_18\O1_MF_1_51_%U_.ARC
ORA-00280: 變更 1170455 (屬於繫線 1) 是在序號 #51

指定日誌: {=suggested | filename | AUTO | CANCEL}
atuo
ORA-00308: 無法開啟存檔日誌 'atuo'
ORA-27041: 無法開啟檔案
OSD-04002: 無法開啟檔案
O/S-Error: (OS 2) 系統找不到指定的檔案。

指定日誌: {=suggested | filename | AUTO | CANCEL}
C:\oracle\product\10.2.0\oradata\user\redo01.log
ORA-00310: 存檔日誌的順序號碼為 50; 需要的順序號碼為 51
ORA-00334: 存檔日誌: 'C:\ORACLE\PRODUCT\10.2.0\ORADATA\USER\REDO01.LOG'

ORA-01547: 警告: RECOVER 成功但 OPEN RESETLOGS 將有如下的錯誤
ORA-01152: 檔案 1 不是自一個夠早的備份中復原回來的
ORA-01110: 資料檔 1: 'C:\ORACLE\PRODUCT\10.2.0\ORADATA\USER\SYSTEM01.DBF'

SQL> alter database open;
alter database open
*
ERROR 在行 1:
ORA-01589: 必須為要開啟的資料庫使用 RESETLOGS 或 NORESETLOGS 選項

SQL> alter database open resetlogs;
alter database open resetlogs
*
ERROR 在行 1:
ORA-01152: 檔案 1 不是自一個夠早的備份中復原回來的
ORA-01110: 資料檔 1: 'C:\ORACLE\PRODUCT\10.2.0\ORADATA\USER\SYSTEM01.DBF'

SQL> recover database until cancel;
ORA-00279: 必須變更 1170455 建立於 09/07/2006 09:54:52 上 (對於繫線 1 而言)
ORA-00289: 建議 :
C:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA\USER\ARCHIVELOG\2006_09_18\O1_MF_1_

51_%U_.ARC
ORA-00280: 變更 1170455 (屬於繫線 1) 是在序號 #51

指定日誌: {=suggested | filename | AUTO | CANCEL}
C:\oracle\product\10.2.0\oradata\user\redo03.log
ORA-00310: 存檔日誌的順序號碼為 49; 需要的順序號碼為 51
ORA-00334: 存檔日誌: 'C:\ORACLE\PRODUCT\10.2.0\ORADATA\USER\REDO03.LOG'

ORA-01547: 警告: RECOVER 成功但 OPEN RESETLOGS 將有如下的錯誤
ORA-01152: 檔案 1 不是自一個夠早的備份中復原回來的
ORA-01110: 資料檔 1: 'C:\ORACLE\PRODUCT\10.2.0\ORADATA\USER\SYSTEM01.DBF'

SQL> recover database until cancel;
ORA-00279: 必須變更 1170455 建立於 09/07/2006 09:54:52 上 (對於繫線 1 而言)
ORA-00289: 建議 :
C:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA\USER\ARCHIVELOG\2006_09_18\O1_MF_1_51_%U_.ARC
ORA-00280: 變更 1170455 (屬於繫線 1) 是在序號 #51

指定日誌: {=suggested | filename | AUTO | CANCEL}
C:\oracle\product\10.2.0\oradata\user\redo02.log
已套用日誌.
媒體恢復執行完成.

SQL> alter database open;
alter database open
*
ERROR 在行 1:
ORA-01589: 必須為要開啟的資料庫使用 RESETLOGS 或 NORESETLOGS 選項

SQL> alter database open resetlogs;

已更改資料庫.

SQL>