ORA-00230: operation disallowed: snapshot control file enqueue unavailable

Backup job failed because RMAN cannot make a snapshot control file. The message stack is as follows:

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of backup command at 06/25/2017 22:48:44
ORA-00230: operation disallowed: snapshot control file enqueue unavailable

When RMAN needs to back up or resynchronize from the control file, it first creates a snapshot or consistent image of the control file. If one RMAN job is already backing up the control file while another needs to create a new snapshot control file, then you may see the following message:

waiting for snapshot control file enqueue

To determine which job is holding the conflicting enqueue:

SELECT s.SID,
       USERNAME   AS "User",
       PROGRAM,
       MODULE,
       ACTION,
       LOGON_TIME "Logon",
       l.*,
       'alter system kill session '||''''||s.SID||','||s.SERIAL#||','||'@'||s.INST_ID||''''||' immediate;'
  FROM GV$SESSION s, GV$ENQUEUE_LOCK l
 WHERE l.SID = s.SID
   AND l.TYPE = 'CF'
   AND l.ID1 = 0
   AND l.ID2 = 2;

You should see output similar to the following:

SID User Program              Module                    Action           Logon
--- ---- -------------------- ------------------- ---------------- ---------
18 SYS rman@testdb (TNS V1-V3) backup full datafile: c30000110 STARTED 25-JUN-17

After you have determined which job is creating the enqueue, you can do one of the following:

• Wait until the job holding the enqueue completes
• kill current job and execute backup again

ORA-04021: Timeout occurred while waiting to lock object

When developer is trying to recompile PL/SQL package, session hangs until get this error:

ORA-04021: timeout occurred while waiting to lock object

Basically, while someone or something else (a scheduled job perhaps?) is executing the package, then you won’t be able to perform the recompile. To get around this, you need to identify the locking session and kill it.

Executing this script as SYS (or another user with the appropriate privileges) will prompt you for the package name and reveal the culprit(s):

SELECT s.sid,
       l.lock_type,
       l.mode_held,
       l.mode_requested,
       l.lock_id1,
       'alter system kill session '''|| s.sid|| ','|| s.serial#|| ''' immediate;' kill_sid
FROM   dba_lock_internal l,
       v$session s
WHERE  s.sid = l.session_id
AND    UPPER(l.lock_id1) LIKE '%&package_name%'
AND    l.lock_type = 'Body Definition Lock'
/

NOTE: If your dba_lock_internal view doesn’t exist, you can create this by running: $ORACLE_HOME/rdbms/admin/catblock.sql
check what is doing this session

SELECT s.sid,
       NVL(s.username, 'ORACLE PROC') username,
       s.osuser,
       p.spid os_pid,
       s.program,
       t.sql_text
FROM   v$session s,
       v$sqltext t,
       v$process p
WHERE  s.sql_hash_value = t.hash_value
AND    s.paddr = p.addr
AND    s.sid = &session_id
AND    t.piece = 0 -- optional to list just the first line
ORDER BY s.sid, t.hash_value, t.piece
/

run kill command from the output of first script

alter system kill session 'SID,SERIAL#' immediate;

after that developer can recompile package.

A lot of failed login attempts in the last 30 minutes

Oracle Enterprise manager shows errors:

A lot of failed login attempts in the last 30 minutes

Checking:

select USERNAME, ACTION_NAME, RETURNCODE, count(*)
   from DBA_AUDIT_SESSION
  where TIMESTAMP > (sysdate - 2 / 24)
    and RETURNCODE != 0
  group by USERNAME, ACTION_NAME, RETURNCODE
  order by count(*);

USERNAME                       ACTION_NAME                  RETURNCODE
------------------------------ ---------------------------- ----------
  COUNT(*)
----------
MGMT_VIEW                      LOGON                             28000
        12

return code is 28000, this mean : ora-2800: the account is locked.
tet's check :

select USERNAME, PASSWORD_VERSIONS,account_status  from dba_users where username='MGMT_VIEW';

USERNAME                       PASSWORD ACCOUNT_STATUS
------------------------------ -------- --------------------------------
MGMT_VIEW                      10G 11G  LOCKED(TIMED)

In 11g, by default the password expired time is set to 180 days(in 10g it is unlimited).

select * from dba_profiles WHERE RESOURCE_NAME='PASSWORD_LIFE_TIME';
PROFILE                        RESOURCE_NAME                    RESOURCE LIMIT
------------------------------ -------------------------------- -------- -----
DEFAULT                        PASSWORD_LIFE_TIME               PASSWORD  180

Ok, let's correct this:

ALTER PROFILE DEFAULT limit  PASSWORD_LIFE_TIME UNLIMITED;

profile altered.

select * from dba_profiles WHERE RESOURCE_NAME='PASSWORD_LIFE_TIME';
PROFILE                        RESOURCE_NAME                    RESOURCE LIMIT
------------------------------ -------------------------------- -------- -----
DEFAULT                        PASSWORD_LIFE_TIME               PASSWORD  UNLIMITED

This account which already in LOCKED status didn't back to normal auto. We need to do below:

alter user MGMT_VIEW account unlock;

User altered.

select USERNAME, PASSWORD_VERSIONS,account_status  from dba_users where username='MGMT_VIEW';

USERNAME                       PASSWORD ACCOUNT_STATUS
------------------------------ -------- --------------------------------
MGMT_VIEW                      10G 11G  OPEN

that's all. Thank you ;)