ORACLE ERRORS

1. ORA-00392 While duplicating the database

Ans:   We are recieving ORA-00392 error while trying to open a database after restoring and recovering a database in case of duplicating the database. When i trying to open the reset logs, i am getting the following error:
SQL> ALTER DATABASE OPEN resetlogs
ORA-00392: log 2 OF thread 1 IS being cleared, operation NOT allowed

SQL>ALTER DATABASE clear logfile GROUP 2;
SQL>ALTER DATABASE OPEN resetlogs;

Since the path on source and destination is different, it is trying to create a file under old destination which is not present on the target server. Solution to this is you should clear the busy onlinelog then later you need to rename the files with your current location. But when i tried clearing the log then gettting now different error:

SQL>ALTER DATABASE clear logfile GROUP 2;
ALTER DATABASE clear logfile GROUP 2
ORA-00344: unable TO re-create online log 'D:\ORACLE\ORADATA\LOG02B.DBF'
ORA-27040: file CREATE error, unable TO CREATE file
OSD-04002: unable TO OPEN file
O/S-Error: (OS 3) The system cannot find the path specified.

SQL>ALTER DATABASE RENAME file 'P:\ORACLE\ORADATA\LOG02A.DBF' TO 'D:\app\o
racle\fast_recovery_area\ONLINELOG\LOG02A.DBF';

SQL>ALTER DATABASE RENAME file 'P:\ORACLE\ORADATA\LOG01A.DBF' TO 'D:\app\o
acle\fast_recovery_area\ONLINELOG\LOG01A.DBF';

Now try opening the database with resetlogs, it will succeed
SQL>ALTER DATABASE OPEN resetlogs;

2. Toad: Cannot Load oci.dll: While Connecting

Ans:    I installed oracle 10g on windows 7 successfully. I found everything is working except toad. I can connect the database through SQL*plus but when I open toad, my oracle installation is not appearing in the “connect using” box.

When I go through the toad user guide, find the following information how it finds client:

1.      Toad first look in the toad command line for oci.dll

2.      If it is not found, then it checks the command line argument ORACLE-HOME

3.      If there is no toad home defined then toad will display the home that is set as the default home active in dropdown.

4.      To populate the drop down, toad searches the registry as follows:

5.      First, Toad reads the list of Oracle home names from the keys under HKEY_ LOCAL_MACHINE\SOFTWARE\ORACLE\ALL_HOMES

If no Oracle homes are found there, then the Oracle home is set to HKEY_ LOCAL_MACHINE\SOFTWARE\ORACLE

Otherwise, Toad finds the ORACLE_HOME value for each Oracle home, if it exists. Then Toad checks the system environment variable called PATH to see

whether it contains the "bin" folder under ORACLE_HOME. Toad selects the

Oracle home whose path appears first in PATH.

If Toad still hasn't found an Oracle home, it uses HKEY_LOCAL_ MACHINE\SOFTWARE\ORACLE.

6.      Toad looks for the client DLL in the "Bin" folder under the path found for the

Oracle home. If that fails, Toad looks for the ORACLE_HOME key under HKEY_LOCAL_ MACHINE\ORACLE\SOFTWARE, and look for the client dll in the "Bin" folder. If that also fails, Toad looks for the client dll in every \bin directory in PATH.

Sometimes the error “cannot load oci.dll” occurs whenever we try to connect with the oracle database by using the third-party tools such as Toad, sqltools and others. This error may occur because of the following reason:

1.      The oci.dll error may occur because you have not set the correct ORACLE_HOME and path in environment variables. 

2.      It might be possible that the oci.dll file may be corrupt or may not exist on the correct path.

3.      May be possible that oci.dll may not be correct version such as 32bit s/w will load a 32bit DDL. It cannot use a 64bit DLL for a 32bit executable. 

To solve this issue, consider the below points:

1.      Check the ORACLE_HOME and Path setting in the environment variable.

2.      Check the correct location of the oci.dll path. The path of the oci.dll file is $ORACLE_HOME\bin\oci.dll

3.      Check the oci.dll correct version.

I checked my registry, and my oracle installion does appear in a 10g folder within HKEY_

LOCAL_MACHINE\SOFTWARE\ORACLE.

In my case, I am facing this issue because of incompatible with 64 bit installation of oracle. Toad looks for oracle installation under HKEY_LOCAL_MACHINE/SOFTWARE/WOW6432NODE/ORACLE which is where, because that is where 32 bit installations are registered. So I installed a 32 bit version of oracle, then after I am able to open every thing including toad.

Conclusion: Toad works with both 32 bit and 64 bit oracle server where as toad only work with 32 bit client. If you need 64 bit client for other applications, you can install both 32 bit and 64 bit client on a single machine and just tell the toad to use the 32 bit client.

3. ORA-28002: the password will expire within 5 days

Ans:   Today one user written to me when he is trying to connect the database through toad getting the following error: ORA-28002: the password will expire within 5 days

Initially I have given him solution to set the PASSWORD_LIFE_TIME of the profile assigned user to UNLIMITED then change the password to avoid it permanently or simply change the password to avoid it temporarily.

SQL> SELECT profile FROM dba_users WHERE username = 'HRMS';

SQL> SELECT resource_name, LIMIT from dba_profiles where

     profile='DEFAULT';

SQL> SELECT * from  dba_profiles where profile='DEFAULT';

SQL> ALTER PROFILE DEFAULT LIMIT PASSWORD_LIFE_TIME UNLIMITED;

SQL> SELECT ACCOUNT_STATUS FROM DBA_USERS WHERE

     USERNAME='HRMS';

Then connect with particular accounts and change the password.

SQL> PASSWORD

Changing password for HRMS

Old password:

New password:

Retype new password:

Password changed

Again he has written to me that he has changed every thing to UNLIMITED still getting ORA-28002 and asking can you please tell me why it is automatic getting locked after few minutes.

I am unlocking the account 

SQL> Alter user MCR account unlock

Then trying to connect through Toad connect mcr/password 

Result: ORA-2800 account locked.

SQL> select resource_name,liMit from dba_profiles where profile='DEFAULT';

RESOURCE_NAME                 LIMIT

--------------                -------------

COMPOSITE_LIMIT               UNLIMITED

SESSIONS_PER_USER             UNLIMITED

CPU_PER_SESSION               UNLIMITED

CPU_PER_CALL                  UNLIMITED

LOGICAL_READS_PER_SESSION     UNLIMITED

LOGICAL_READS_PER_CALL        UNLIMITED

IDLE_TIME                     UNLIMITED

CONNECT_TIME                  UNLIMITED

PRIVATE_SGA                   UNLIMITED

FAILED_LOGIN_ATTEMPTS         UNLIMITED

PASSWORD_LIFE_TIME            UNLIMITED

PASSWORD_REUSE_TIME           UNLIMITED

PASSWORD_REUSE_MAX            UNLIMITED

PASSWORD_VERIFY_FUNCTION      NULL

PASSWORD_LOCK_TIME            UNLIMITED

PASSWORD_GRACE_TIME           UNLIMITED

SQL> select username,account_Status,expiry_date from dba_users;

USERNAME    ACCOUNT_STATUS    EXPIRY_D

--------    --------------    --------

MGMT_VIEW   OPEN

SYS         OPEN

SYSTEM      OPEN

SCOTT       OPEN

HRMS        OPEN

MCR         OPEN

BACKUPUSER OPEN

BT_WM_USR   OPEN

DBSNMP      EXPIRED(GRACE)    08/06/13

SYSMAN      EXPIRED(GRACE)    08/06/13

FLOWS_FILES EXPIRED & LOCKED 30/03/10

MDSYS       EXPIRED & LOCKED 30/03/10

ORDSYS      EXPIRED & LOCKED 30/03/10

WMSYS       EXPIRED & LOCKED 30/03/10

APPQOSSYS   EXPIRED & LOCKED 30/03/10

WKSYS       EXPIRED & LOCKED 03/12/12

WK_TEST     EXPIRED & LOCKED 03/12/12

ORDDATA     EXPIRED & LOCKED 30/03/10

CTXSYS      EXPIRED & LOCKED 03/12/12

WKPROXY     EXPIRED & LOCKED 03/12/12

ORDPLUGINS EXPIRED & LOCKED 30/03/10

OWBSYS      EXPIRED & LOCKED 30/03/10

ORACLE_OCM EXPIRED & LOCKED 30/03/10

MDDATA      EXPIRED & LOCKED 30/03/10

DIP         EXPIRED & LOCKED 30/03/10

SPATIAL_CSW_ADMIN_USR EXPIRED & LOCKED 30/03/10

SPATIAL_WFS_ADMIN_USR EXPIRED & LOCKED 30/03/10

Solution:

In fact I thought the “PASSWORD_LIFE_TIME” value changed to UNLIMITED would resolve this problem. After searching oracle support, I found this note [ID 162818.1].

The PASSWORD_LIFE_TIME limit of a profile is measured from the last time an account's password was changed or the account creation time if the password has never been changed. These are the dates USER$.PTIME and USER$.CTIME respectively.

SQL> select username, profile,account_status,expiry_date, created

from dba_users

where default_tablespace  not in ('SYSAUX','SYSTEM')

 --and expiry_date is not null

 and account_status not like '%LOCKED' order by expiry_date;

In fact it is NOT measured since from the current time. Therefore, any accounts affected by the changed profile whose last password change time was more than PASSWORD_LIFE_TIME days ago immediately expire and enter their grace period on their next connection, issuing the ORA-28002 warning.

SQL> ALTER PROFILE test LIMIT PASSWORD_LIFE_TIME UNLIMITED;

This change only affects accounts those have not entered their grace period. The accounts that have entered their grace period will have to change their passwords.