Archive for the ‘Oracle’ Category
Troubleshooting for startup inconsistent DB
for e.g. database unable up cause of datafile corruption
SQL> startup ORACLE instance started. Total System Global Area 1610612736 bytes Fixed Size 2177912 bytes Variable Size 396149896 bytes DATABASE Buffers 1207959552 bytes Redo Buffers 4325376 bytes DATABASE mounted. ORA-01113: file 2 needs media recovery ORA-01110: DATA file 2: '/oradata/oracle/ts/undotbs01.
commonly the database unable be startup if UNDO or SYSTEM table space need recovery, if these happen we need correct temporary workaround to handle this, all workaround for each tablespace datafile is similar
do the recovery
SQL> recover DATABASE; ORA-00279: CHANGE 7516226638 generated at 05/05/2008 12:32:11 needed FOR thread 1 ORA-00289: suggestion : /oradata/oracle/ts/arc/1_42_653916655.dbf ORA-00280: CHANGE 7516226638 FOR thread 1 IS IN sequence #42 Specify log: {ret=suggested | filename | AUTO | CANCEL} AUTO ORA-00308: cannot open archived log ‘/oradata/oracle/ts/arc/1_42_653916655.dbf’ ORA-27037: unable TO obtain file STATUS SVR4 Error: 2: No such file OR directory Additional information: 3
Backup Listener log
In database production, the log of listener was grow rapidly, some times if you move the “listener.log” while the listener is up and running, listener will stop writing to log file after you move/delete the “listener.log”, it won’t create new log file.
The listener log status will be ‘off’, You need to start the “listener log” again manually. For this reason you must do this procedure.
bash> lsnrctl stat
LSNRCTL for Solaris: Version 9.2.0.6.0 - Production on 09-MAR-2008 12:43:22 Copyright (c) 1991, 2002, Oracle Corporation. All rights reserved. Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC))) STATUS of the LISTENER ------------------------ Alias LISTENER Version TNSLSNR for Solaris: Version 9.2.0.6.0 - Production Start Date 11-SEP-2007 03:34:17 Uptime 180 days 9 hr. 9 min. 4 sec Trace Level off Security OFF SNMP OFF Listener Parameter File /oracle/app/product/9.2.0/network/admin/listener.ora Listener Log File /oracle/app/product/9.2.0/network/log/listener.log Listening Endpoints Summary... (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC))) (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=HOSTNAME)(PORT=1521))) Services Summary... Service "PLSExtProc" has 1 instance(s). Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service... Service "DB_SID.domain.com" has 2 instance(s). Instance "DB_SID", status UNKNOWN, has 1 handler(s) for this service... Instance "DB_SID", status READY, has 1 handler(s) for this service... The command completed successfully
bash> lsnrctl
LSNRCTL> SET log_status off Connecting TO (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC))) LISTENER parameter "log_status" SET TO OFF The command completed successfully
bash> mv /oracle/app/product/9.2.0/network/log/listener.log /backup/listener.log_9-March-2008
LSNRCTL> set log_status on Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC))) LISTENER parameter "log_status" set to ON The command completed successfully
Oracle Heterogeneous Configuration Service (ORACLE to MySQL)
PART I : Setting up ODBC driver
bash> rpm –ivh mysql-connector-odbc-3.51.12-linux-i686.rpm
bash> /etc/init.d/mysqld start Initializing MySQL database: [ OK ]
bash> ODBCConfig
bash> vi /etc/odbc.ini
[trans] Driver = MySQL ODBC 3.51 Driver DATABASE = kampus DESCRIPTION = punya_kampus PWD = password PORT = 3306 SERVER = localhost UID = root
Show Locking Proccess
SQL> SELECT s1.username || '@' || s1.machine|| ' ( SID=' || s1.sid || ' ) is blocking '|| s2.username || '@' || s2.machine || ' ( SID=' || s2.sid || ' ) ' AS blocking_status, s1.SQL_ID FROM v$lock l1, v$session s1, v$lock l2, v$session s2 WHERE s1.sid=l1.sid AND s2.sid=l2.sid AND l1.BLOCK=1 AND l2.request > 0 AND l1.id1 = l2.id1 AND l2.id2 = l2.id2;
Moving & Recreate Temp Tablespace
I Recreate the temp tablespace coz my temp tablespace was corrupt,
you can do to the following bellow
- create temporary tablespace temp2 tempfile ‘/rdbms/temp01/temp2.dbf’ size 10M extent management local uniform size 1M;
- alter database default temporary tablespace temp2;
- drop tablespace temp including contents and datafiles;
now change back to temp again with following step bellow:
- create temporary tablespace temp tempfile ‘/rdbms/temp01/temp2_01.dbf’ size 1024M extent management local uniform size 1M;
- alter database default temporary tablespace temp;
- drop tablespace temp2 including contents and datafiles;
- alter tablespace temp add tempfile ‘/rdbms/temp01/temp2_02.dbf’ size 1024M;
ORA 29807 – specified operator does not exist
Yesterday,
During Installation of Oracle database 9i base on RHEL 4 ES,
I got Found error occurred when create the database using dbca,
and it keeps failing at the same point (Creating data dictionary Views)
This the error number Detail “ORA 29807 – specified operator does not exist”
Workaround;
just ignore the error and DBCA proceeds normally. Once the database is ready,
login as SYS and run this script manually:
$ORACLE_HOME/rdbms/admin/prvtxml.plb
SQL> @?/rdbms/admin/prvtxml.plb
kill sesion oracle in windows
path: …/Oracle Home/bin/orakill.exe or find manually
command: orakill <sid> <spid>
example: C:\Oracle9\bin\orakill ORCL 2760
Sql*Loader
Preview a little ’bout sql*loader
SQL*Loader loads data from external files into tables of an Oracle database,
SQL*Loader Can manipulate the data before loading it, using SQL functions,
SQL*Loader Can load data into multiple tables during the same load session, and many more..
SQL*Loader takes as input a control file (*.ctl), which controls the behavior of SQL*Loader, and one or more datafiles. Output of the SQL*Loader is an Oracle database (where the data is loaded), a log file, a bad file, and potentially a discard file.
Requirement..
1. Table
2. Control File
3. Flat File / Data File (optional)