Oracle Simple DBA Tasks

I recently started working heavily on Oracle database DBA stuff, export, import convert to arabic characterset ..etc.

So this blog will list all information regarding these tasks and how to accomplish inorder to be reference in any future similar dba tasks.

First of all whenver you try to make any dba you have to connect as sysdba user privilages for example

Open SQLPlus, and login as sys/sys as ssydba

- Export database

exp userid=dbuser/dbuserpassword file=c:\anyfile.dmp

- Import dump

If you need to import this dump file into another user you have to do the following:

1- If user not created, create new user

create user user_name identified by password

2- grant him dba privilages.

grant dba to user_name;

3- Import database:

imp file=c:\anyfile.dmp fromuser=dbuser touser=user_name userid=dbuser/dbuserpassword

Also if this user needed to be removed run the following:

DROP USER ahmaddb CASCADE;


Another needed task was converting the characterset to be arabic enabled.

1- First full backup should be taken from database.
2- You should shutdown database and make restricted session as follows:


SHUTDOWN IMMEDIATE;
STARTUP MOUNT;

ALTER SYSTEM ENABLE RESTRICTED SESSION;

ALTER DATABASE CHARACTER SET AR8MSWIN1256;

and by these steps your database engine will be arabic enabled.

One note is that when arabic is needed to be enabled on client machines having oracle client only, you should just change regsitry value on NLS_LANG as follows:

go to:

HKEY_LOCAL_MACHINE-->SOFTWARE-->ORACLE_HOME

Open NLS_LANG parameter and put AR8MSWIN1256 value instead of current one and this should be same as server value.

Debugging a Windows Service

When creating windows services using .Net we always face problems while debugging it, since the attach process approach of debugging is on my opinion useless. The following is code snippet i found on internet that will enable windows service debugging and will make it running like any other desktop application.


1- Create a windows service.



2- Put this code at Program.cs on Main method:


Service1 obj= new Service1();
obj.start();
do
{
System.Threading.Thread.Sleep(1000);
} while (true);


The new main method will look like this:

static void Main()
{
Service1 email = new Service1();
email.AnyMethod();
do
{
System.Threading.Thread.Sleep(1000);
} while (true);
ServiceBase[] ServicesToRun;
ServicesToRun = new ServiceBase[]
{
new Service1()
};
ServiceBase.Run(ServicesToRun);
}



Where Service1 that you create intance from it your service name, and AnyMethod is any method that you create inside the service class.


When you run the service you will notice that the debugging is breaking at the AnyMethod that you just created at your service.

Database Log file Shrink

Some times i come across this need (shrinking database log file) and always do some research on internet to find the solution, but the problem is in most cases the info is not complete, so i am listing the steps below to be refrence for me and others in future :)

Step1: Taking backup from log file

BACKUP LOG myDBName WITH TRUNCATE_ONLY

Step2: Getting files logical name

EXEC sp_helpfile

it will be the first column on the list

Step3: Shrinking the database

DBCC
SHRINKFILE
(, 2000)
WITH NO_INFOMSGS

Notes:

1- Using the logical name of logfile not physical when shrinking.
2- Taking backup of log file before proceed (second step).

Hope this helps,

Salam.,