Oracle Quick Tips you should not miss to know!!

Author: Simon Bakker

In this article we will discuss some great tips that will ease your life as a database administrator by solving some of the most common yet frustrating situations while handling Oracle databases.

Invalid Username/Password; Logon Denied:

Problem:

Sometimes we face the frustrating situation where we try to login to login to database remotely with sys user but the access is denied even though we are entering the right password.

Solution:

First of all you need to make sure that your password file exists as sometimes we get this error if the password file does not exist.

Another reason can be when the password supplied does not match the one in the password file and hence the authentication is rejected.

Yet another reason for this problem may be that your password file might have been changed since the instance was started.

This situation is also caused when REMOTE_LOGIN_PASSWORDFILE is set to NONE in your database thus preventing the privileged from getting login remotely to database. You can solve this problem by updating this parameter.

1. First of all shutdown database.
2. Now move the database password file and create a new password file.
   orapwd file= password= entries=
3. Now set the REMOTE_LOGIN_PASSWORDFILE parameter to EXCLUSIVE
4. Startup database

Changing default port on 10g Listener File:

Problem:

Most of us assume that it is necessary to change the default port on the Listener file in 10g. However in general this is not necessary. This is because Oracle 10g prevents you from putting listener in password protected mode because only Oracle can bounce listener by default. However one should keep in mind that a bug in Oracle 10g listener may utilize whole CPU.

Solution:

You can fix this problem by adding SUBSCRIBE_FOR_NODE_DOWN_EVENT_LISTENER=OFF entry in listener.ora file.

RMAN Backup Taking Too Long:

Problem:

Being database administrator we usually take RMAN backup almost daily. Sometimes it happens that the backup taking 4-5 hours in normal days starts taking more than 24 hours.

Solution:

1. You can use RC_BACKUPSET to ensure that there is not major change with checking backup size.
   
2. You can also use V$BACKUP_ASYNC_IO or V$BACKUP_SYNC_IO views for checking RMAN performance. These views show performance information about ongoing and recently completed RMAN backup. The EFFECTIVE_BYTES_PER_SECOND column in rows where TYPE column is set to AGGREGATE represents the rate at which the objects are being backed up or recovered in bytes per second.
   
3. Check ARCHIVELOG GENERATION for normal back up and the backup taking too long as it might be possible that the RMAN is now having more data to be backup. Below scrip can be helpful in this regard.

SELECT TO_CHAR(SYSDATE,'DAY') DAY, SUM(BLOCKS*BLOCK_SIZE)/1024/1024/1024 GB ,
COUNT(*) ARCHIVELOG_NUMBER
FROM V$ARCHIVED_LOG
WHERE FIRST_TIME>=TRUNC(SYSDATE,'DD')

Stopping Job for forgot JOBID:

Problem:

Sometimes it happens that we forget the JOBID for a specific job and the job need to be stopped due to some reason.

Solution:

You can stop the running job by removing it from DBA_JOBS. Below steps will help you do it

1. First of all find job number from DBA_JOBS by running below command
   SELECT JOB,WHAT FROM DBA_JOBS;
2. Remove job, use job value from step1. (exec dbms_job.remove(&job);
3. Finalize remove with commit command. (commit;)

Cloning Database from one server to another on Windows:

Problem:

Sometimes our requirement is to efficiently clone our Oracle database from one server to another on the same windows and bringing up the database.

Solution:

A number of options are available for efficient cloning of Oracle databases. You can either use the Grid Control or OUI for cloning purpose. However Grid Control Clone feature is by far one of the easiest and efficient solution to this problem. Read Again!!

 More Oracle Articles, Database Articles and DBA Tips
   Database Security: Step by step guideline
   Oracle 11g Enhanced Optimizer Statistics Maintenance!!
   Amazing Tips to Fix Broken Oracle ASM Instance!!
   Inside Oracle Temporary Tablespace!!
   Oracle: Achieving performance goal - Part I