Experience: is what you get soon after you need it.

Experience: is what you get soon after you need it.

Rasul Allah (sal Allahu alaihi wa sallam) said: "Restore the trusts of those who trust you, and deal not falsely with him who deals falsely with you." [Abu Dawud, Tirmidhi]

Search This Blog

Tuesday, June 28, 2011

ORA-28000: the account is locked

Trying to connect using APPS_JDBC_URL
Exception occurred: java.sql.SQLException: ORA-28000: the account is locked

We see from the logs that the account we are using is locked.

so login into the db and check the default profile for the user:
SELECT username, account_status,profile FROM dba_users WHERE username= 'APPS'

APPS LOCKED DEFAULT

SELECT resource_name,resource_type,limit FROM dba_profiles WHERE profile='DEFAULT' AND RESOURCE_NAME ='FAILED_LOGIN_ATTEMPTS'


FAILED_LOGIN_ATTEMPTS PASSWORD 10


So after 10 attempts the account gets locked.


You can unlock and live with it :

SQL>
alter user apps account unlock;

or

Increase the FAILED_LOGIN_ATTEMPTS limit in the default profile

alter profile default limit FAILED_LOGIN_ATTEMPTS 50;


or

create a new whole profile and assign it to the user

Sunday, June 26, 2011

duplicate database without target & catalog

You are trying to do duplicate the database using the prod db backup without using rman catalog and target database clause.

You have to be in 11.2.0.1

Restore script:


$ORACLE_HOME/bin/rman msglog /ora_backup/u0001/cloning/dupdb_Refresh.${TODAY}_${TIME}.log << EOF
connect auxiliary /
run {
allocate auxiliary channel dsk1 type disk ;
allocate auxiliary channel dsk2 type disk ;
duplicate database to DUPDB backup location '/ora_backup/u0001/cloning/' nofilenamecheck;
release channel dsk1;
release channel dsk2;
}
EOF



here the drawback I found out is you can use only one location in the "backup location clause"

i.e all your backups should be in one location to restore from if not you will get the below error:


$ORACLE_HOME/bin/rman msglog /ora_backup/u0001/cloning/dupdb_Refresh.${TODAY}_${TIME}.log << EOF
connect auxiliary /
run {
allocate auxiliary channel dsk1 type disk ;
allocate auxiliary channel dsk2 type disk ;
duplicate database to DUPDB backup location "'/ora_backup/u0001/cloning/','/ora_backup/u0002/cloning/'" nofilenamecheck;
release channel dsk1;
release channel dsk2;
}
EOF






Recovery Manager: Release 11.2.0.1.0 - Production on Sun Jun 26 12:40:19 2011

Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.

RMAN>
connected to auxiliary database: DUPDB (not mounted)

RMAN> 2> 3> 4> 5> 6> 7>
allocated channel: dsk1
channel dsk1: SID=2946 device type=DISK

allocated channel: dsk2
channel dsk2: SID=1 device type=DISK

Starting Duplicate Db at 06/26/2011 12:40:21
released channel: dsk1
released channel: dsk2
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of Duplicate Db command at 06/26/2011 12:40:21
RMAN-05579: CONTROLFILE backup not found in '/ora_backup/u0001/cloning/','/ora_backup/u0002/cloning/'

RMAN>

Recovery Manager complete.



RMAN-05579: CONTROLFILE backup not found in '/ora_backup/u0001/cloning/','/ora_backup/u0002/cloning/'



even though the backup of controlfile is in the specified location:
channel dsk1: specifying datafile(s) in backup set
including current control file in backup set
channel dsk1: starting piece 1 at 06/24/2011 14:42:06
channel dsk1: finished piece 1 at 06/24/2011 14:42:09
piece handle=/ora_backup/u0001/cloning/Proddb_20110624_115025_s7200_p1_10mfmmtt tag=PRODB_FULL comment=NONE



can be avoided by copying all the backup files in one location, I have to follow up with oracle support to see if there is any workaround for this.

Tuesday, June 14, 2011

kill Data pump job - data pump job status

if you are running the expdp job running in the foreground, then use ctrl ^c

ctrl ^c
Export> STOP_JOB=IMMEDIATE
Are you sure you wish to stop this job ([yes]/no): yes


or use
kill_job

also u can check the job status from the DB as below:

SELECT owner_name, job_name, operation, job_mode,state, attached_sessions FROM dba_datapump_jobs

once you find the job_name use the below to stop the job:


exec DBMS_DATAPUMP.STOP_JOB ( );
ex:

SQL> DECLARE
   var99 NUMBER;
BEGIN
    var99 := DBMS_DATAPUMP.ATTACH('SYS_EXPORT_FULL_02','SYS');
    DBMS_DATAPUMP.STOP_JOB (var99,1,0);
    END;
    /

PL/SQL procedure successfully completed.