Category: Oracle
-
How to drop an Oracle database without DBCA
If, when you come to remove an Oracle database, you find that DBCA is not available perhaps due to some installation issue or missing files then do not fret as it is still possible to remove the database by following these steps… 1 – Set the Oracle SID D:\Oracle\product\11.2.0\dbhome_1\BIN>set ORACLE_SID=TESTDB 2 – Connect to the database…
-
Kill Oracle processes in Unix
Found this article from Donald Burleson (Burleson Consulting) on his website which gives a very good example of how to identify and kill processes associated with a specific ORACLE_SID. ps -ef|grep $ORACLE_SID| grep -v grep|awk ‘{print $2}’|xargs -i kill -9 {} Check out the original article and take a look around his website as there are other…
-
ORA-10873: file 1 needs to be either taken out of backup mode or media recovered
Over the weekend, one of our Windows Oracle database servers suffered a power failure the same time it was being backed up by CommVault Oracle client. This left the database in a state where it wouldn’t start up, to rectify this, the following steps should help. Open a DOS window. set ORACLE_SID=<database SID> sqlplus / as…
-
ERROR: Cannot find the file bin\ONSCLIENT.dll in the ORACLE_HOME
Trying to use the deinstall tool packaged with the Oracle 11.2.0.1 client on one particular server failed and displayed the following output. C:\oracle\product\11.2.0\client_2\deinstall>deinstall Checking for required files and bootstrapping … Please wait … ERROR: Cannot find the file bin\ONSCLIENT.dll in the ORACLE_HOME C:\oracle\product\11.2.0\client_2. You must download the standalone version of the deinstall tool from OTN to…
-
How to Enable/Disable Archive Log Mode in Oracle 11g
It is advisable to backup your database before either enabling or disabling archive log mode as a precaution. Enabling archive log mode Verify the current archive log mode. We can see that database log mode is “No Archive Mode”. Also worth noting is the destination used for archiving. In order to see where that location…
-
SQLTools: “OCI8: Cannot allocate OCI handle” when starting
When starting SQLTools, a colleague was getting an error message “OCI8: Cannot allocate OCI handle”. Helping to investigate this, I found some guidance on the web with suggestions such as: Run the app with Administrator privileges Delete the OCI.DLL Oracle DLL from the Windows directory Neither of the above worked (the OCI.DLL wasn’t even present in my…
-
SnipIT No 4: Run an Oracle SQL script from DOS prompt
SnipIT is a series of short blogs containing useful information in an easily digestible format. To run an Oracle SQL script file from a DOS prompt (Command Line) you execute sqlplus passing credentials and the database name along with the script to run. @sqlplus.exe <username>/<password>@<database name> @<script filename> @sqlplus.exe scott/tiger@testdb @browseUser.sql Additionally, you can pass…
-
ORA-19502: write error on file
Description: Adding a new tablespace threw the following Oracle error, the sql file contained the following statement: PROMPT Creating Tablespace TS_SCHMASK2 create tablespace TS_SCHMASK2 datafile ‘/data/oradata/FTEST2/TS_SCHMASK201.DBF’ SIZE 32000M REUSE autoextend on next 1000M, ‘/data/oradata/FTEST2/TS_SCHMASK202.DBF’ SIZE 32000M REUSE autoextend on next 1000M, ‘/data/oradata/FTEST2/TS_SCHMASK203.DBF’ SIZE 32000M REUSE autoextend on next 1000M, ‘/data/oradata/FTEST2/TS_SCHMASK204.DBF’ SIZE 20000M REUSE autoextend on next…
-
How to identify the Tablespace names from an EXPDP file
If you have been given a datapump dump file to import you may not have been given the export log file or a list of schemas and tablespaces contained in that file. Fortunately all is not lost, datapump provides an option to create a text-based file containing the SQL DDL statements used to import the file…
-
ORA-25017: cannot reference NEW ROWID for movable rows in before triggers
Introduction A couple of triggers were throwing the following error which was strange as in other databases the same code works fine. The problematic database is based on an anonymised customer database and was not a copy of a working internal DB. Error: ORA-25017 Description: cannot reference NEW ROWID for movable rows in before triggers…