ORA-01994: GRANT failed: password file missing or disabled

Environment: Database 11.2.0.2 , OS Solaris 10

Problem:
I hit this error when I was trying to give sysdba privilege to one of the database application user account which need to install BAM. I logged in locally from the host as sys user.

SQL> grant sysdba to abc_admin;

ORA-01994: GRANT failed: password file missing or disabled


Solution:

1. Create a password file from os:

orapwd file=$ORACLE_HOME/dbs/orapw$ORACLE_SID password=test2sys entries=5 force=y


2. Change the parameter REMOTE_LOGIN_PASSWORDFILE to EXCLUSIVE:

alter system set remote_login_passwordfile=exclusive scope=spfile;

note: need to bounce the DB to make it enabled.

3. Also change the owner and the permission of the password file if it is not under oracle:dba.

chown oracle:dba $ORACLE_HOME/dbs/orapw$ORACLE_SID

chmod 755 $ORACLE_HOME/dbs/orapw$ORACLE_SID

Moharaj - Warfaze

Project Guide for Oracle RAC Implementation

Project Guide for Oracle RAC Implementation

ORA-38307: object not in RECYCLE BIN

When I was trying to purge object under recycle bin I got following error.

SQL> purge TABLE BIN$TBffE+37D77gRAgAIPeSWg==$0;
purge TABLE BIN$TBffE+37D77gRAgAIPeSWg==$0
*
ERROR at line 1:
ORA-00933: SQL command not properly ended


So I used Double quotation to resolved the issue. But still I was getting another error like below.

SQL> purge TABLE "BIN$Y+tPc7dSNrzgRAgAIPeSWg==$0";
purge TABLE "BIN$Y+tPc7dSNrzgRAgAIPeSWg==$0"
*
ERROR at line 1:
ORA-38307: object not in RECYCLE BIN

To avoid this error I had to put owner.object_name.

SQL> purge TABLE scott."BIN$Y+tPc7dSNrzgRAgAIPeSWg==$0";

Table purged.


Finally I have created below procedure so that I can schedule it to run with given parameter. Parameter may be like purge objects older then 30 days.


create or replace procedure cln_recylebin( p_day in number)
is
begin
for i in (SELECT owner,object_name,type FROM dba_RECYCLEBIN where can_purge='YES'
and to_date(droptime,'yyyy-mm-dd:HH24:MI:SS') < sysdate-p_day) loop
dbms_output.put_line(i.type||' "'||i.object_name||'"');
execute immediate 'purge '||i.type||' '||i.owner||'."'||i.object_name||'"';
end loop;
end;
/


Now just call the procedure like:

SQL> exec cln_recylebin(30);