Rename logical & physical MSSQL files

This post will provide guidance on how to amend the logical and physical file names of a MSSQL database.When a copy of a database is restored as a new database, the logical file names will remain the same as the source database.Firstly, check the current logical and physical file names: USE master GO SELECT name AS [Logical_name], physical_name AS [File_Path], […]

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 and verify the name D:\Oracle\product\11.2.0\dbhome_1\BIN>sqlplus […]

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 useful gems there.

Change the logical name of MSSQL database/transaction log files

When a database backup is restored in MSSQL, the physical filenames can be amended during the restore process but the logical names for the source database and transaction log files are kept. It is a simple step to amend these however… First, find the current names of the files where <database_name> is the name of the restored DB. SELECT database_id, […]

ERROR: Cannot find the file bin\ONSCLIENT.dll in the ORACLE_HOME

Trying to use the deinstall tool packaged with the Oracle 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 deconfigure/deinstall this Oracle Home. cannot […]

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 is mapped to we can […]

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 Windows folder), or any others […]

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/[email protected] @browseUser.sql Additionally, you can pass values to the script being […]

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 1000M; Symptom: SQL*Plus: Release […]