if not able to create user with particular name and getting user or role exist

select role from DBA_roles;

->Take a backup of the role

-> Drop the role ( Which has the same name as user)

-> Create the user

Note : Roles are not listed in all_objects or dba_objects.

Script to clone one user and his privilages to another user

rem --------------------------------------------------------------------------------------------------------
rem Name                : CLONEUSER.SQL
rem
rem Compatible versions : 10.x and above
rem
rem Description         : This script will generate SQL file with DDLs for user creation, associated grants
rem                       and privilegs (roles, system privileges, object privileges and tablespace quotas)
rem
rem prerequisites       : - You must login with DBA role to execute this script
rem                       - Package DBMS_METADATA must be in valid status
rem
rem --------------------------------------------------------------------------------------------------------
rem
----------------------
-- Setting SQL prompt:
----------------------
whenever sqlerror exit;
set serveroutput on long 100000000 pagesize 60 linesize 150 heading off feedback off verify off echo off
--------------------------------------------------
-- Handling Exception for Insufficient privileges:
--------------------------------------------------
declare
        privusercnt     number;
begin
    select count(username)
    into   privusercnt
    from   user_role_privs
    where  granted_role = 'DBA'
    and    username = ora_login_user;
    if ( privusercnt = 0 ) then
      raise_application_error
         (-20001, 'ERROR !'
          || CHR (10)
          || '~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~'
          || CHR (10)
          || 'INFO: Insufficient privileges. You must login with DBA role to execute this script'
          || CHR (10)
          || '~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~'
          || CHR (10)
          || CHR (10)
          || 'Terminating the script...'
         );
    end if;
end;
/
-------------------------------------------------------
-- Handling Exception for Invaid DBMS_METADATA package:
-------------------------------------------------------
declare
        invalidstatcnt  number;
begin
    select count(status)
    into   invalidstatcnt
    from   dba_objects
    where  object_name = 'DBMS_METADATA'
    and    status <> 'VALID';
    if ( invalidstatcnt > 0 ) then
      raise_application_error
         (-20001, 'ERROR !'
          || CHR (10)
          || '~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~'
          || CHR (10)
          || 'INFO: Package SYS.DBMS_METADATA is not in valid status. Please recompile'
          || '      the package and package body, and try again.'
          || CHR (10)
          || '~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~'
          || CHR (10)
          || CHR (10)
          || 'Terminating the script...'
         );
    end if;
end;
/
column dbname noprint new_value db_name
column fdate noprint new_value datestamp

select name dbname,
       to_char(sysdate,'yyyymmddhh24miss') fdate
from   v$database
/
prompt ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
prompt ATTENTION:
prompt
prompt (1) A NULL | ENTER for source user will abort the script.
prompt (2) A NULL | ENTER for target user will keep the same source user name as target user.
prompt (3) To abort the script execution, please use CTRL+d
prompt ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
prompt
accept sourceuser prompt "Enter source user for generating DDL : "
----------------------------------------------
-- Handling Exception for Invalid source user:
----------------------------------------------
declare
        sourceusercount number;
begin
    select count(username)
    into   sourceusercount
    from   dba_users
    where  username = upper(trim('&&sourceuser'));
    if ( sourceusercount = 0 ) then
      raise_application_error
         (-20002, 'ERROR !'
          || CHR (10)
          || '~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~'
          || CHR (10)
          || 'INFO: Invalid username specified. Please enter a valid username'
          || CHR (10)
          || '~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~'
          || CHR (10)
          || CHR (10)
          || 'Terminating the script...'
         );
    end if;
end;
/
prompt
accept targetuser prompt "Enter target user for generating DDL : "
prompt

------------------------------------------------------
-- Setting Transform Parameters for the DDL Transform:
------------------------------------------------------
begin
    dbms_metadata.set_transform_param (dbms_metadata.session_transform, 'SQLTERMINATOR', true);
    dbms_metadata.set_transform_param (dbms_metadata.session_transform, 'PRETTY', true);
end;
/
------------------
-- Generating DDL:
------------------
spool user_ddl_&&sourceuser._&&db_name._&&datestamp..sql

prompt
prompt -- (1). DDL for user creation:
-------------------------------------
prompt
prompt -- Notes:
prompt
prompt -- (1) Please edit the password, default tablespace, temporary tablespace for target user.
prompt -- (2) If not, all these attributes will be the same as source user.
prompt -- (3) If you are using 11g database, please make sure that the encripted passoword dont have any space | line break
prompt
col userddl format a600
select trim(replace (dbms_metadata.get_ddl('USER',upper(trim('&&sourceuser')))
  ,'USER "'||upper(trim('&&sourceuser'))||'"','USER "'||upper(trim(nvl('&&targetuser','&&sourceuser')))||'"')) userddl
from dual
where exists
  (select 'x'
  from dba_users drp
  where drp.username = upper(trim('&&sourceuser')))
/
set linesize 110
prompt
prompt -- (2). DDL for tablespace quotas:
-----------------------------------------
prompt
prompt -- Note: - Please verify and edit the target tablespace names, quotas by checking contents.
prompt
select 'ALTER USER '
  ||replace(username,upper(trim('&&sourceuser')),upper(trim(nvl('&&targetuser','&&sourceuser'))))
  ||' QUOTA '
  ||decode(max_bytes,-1,'UNLIMITED',max_bytes)
  ||' ON '
  ||tablespace_name
  ||';' ddl
from dba_ts_quotas
where username = upper(trim('&&sourceuser'))
and  exists
  (select 'x'
  from dba_ts_quotas drp
  where drp.username = upper(trim('&&sourceuser')))
/
/*
----------------------------------------------------------------------------------------------------------
Note:
-----
The below SQL can be useed instead of the above TABLESPACE QUOTA generation command
this will generate the DDL in PL/SQL block format.

select replace (dbms_metadata.get_granted_ddl('TABLESPACE_QUOTA',upper(trim('&&sourceuser')))
  ,'"'||upper(trim('&&sourceuser'))||'"','"'||upper(trim(nvl('&&targetuser','&&sourceuser')))||'"') ddl
from dual
where exists
  (select 'x'
  from dba_ts_quotas drp
  where drp.username = upper(trim('&&sourceuser')))
/
----------------------------------------------------------------------------------------------------------
*/
prompt
prompt -- (3). DDL for roles:
-----------------------------
select replace (dbms_metadata.get_granted_ddl('ROLE_GRANT',upper(trim('&&sourceuser' )))
  ,'"'||upper(trim('&&sourceuser'))||'"','"'||upper(trim(nvl('&&targetuser','&&sourceuser')))||'"') ddl
from dual
where exists
  (select 'x'
  from dba_role_privs drp
  where drp.grantee = upper(trim('&&sourceuser')))
/
-- DDL for alter user with default role
--
select replace (dbms_metadata.get_granted_ddl('DEFAULT_ROLE',upper(trim('&&sourceuser' )))
  ,'"'||upper(trim('&&sourceuser'))||'"','"'||upper(trim(nvl('&&targetuser','&&sourceuser')))||'"') ddl
from dual
where exists
  (select 'x'
  from dba_role_privs drp
  where drp.grantee = upper(trim('&&sourceuser')))
/

prompt
prompt -- (4). DDL for system privileges:
-----------------------------------------
select replace (dbms_metadata.get_granted_ddl('SYSTEM_GRANT',upper(trim( '&&sourceuser')))
  ,'"'||upper(trim('&&sourceuser'))||'"','"'||upper(trim(nvl('&&targetuser','&&sourceuser')))||'"') ddl
from dual
where exists
  (select 'x'
  from dba_sys_privs drp
  where drp.grantee = upper(trim('&&sourceuser')))
/

prompt
prompt -- (5). DDL for object privileges:
-----------------------------------------
select replace (dbms_metadata.get_granted_ddl('OBJECT_GRANT',upper(trim( '&&sourceuser')))
  ,'"'||upper(trim('&&sourceuser'))||'"','"'||upper(trim(nvl('&&targetuser','&&sourceuser')))||'"') ddl
from dual
where exists
  (select 'x'
  from dba_tab_privs drp
  where drp.grantee = upper(trim('&&sourceuser')))
/

spool off

prompt
prompt Post steps:
prompt ~~~~~~~~~~~
prompt INFO: (1) Please review, edit and execute the script user_ddl_&&sourceuser._&&db_name._&&datestamp..sql in target database.
prompt INFO: (2) Please remove space | line break from the SQL file, before execution
prompt
------------------------
-- Resetting SQL prompt:
------------------------
prompt
prompt

undefine sourceuser targetuser db_name datestamp
whenever sqlerror continue;
set heading on feedback on verify on echo on
exit;

Script to drop all objects in a schema

drop_all_objects_of_selected_user.sql

 REM name: drop_objects_of_selected_user.sql
  REM usage:@drop_objects_of_selected_user.sql
  REM -------------------------------------------------------------------------
  REM requirements: dba-role
  REM -------------------------------------------------------------------------
  REM -------------------------------------------------------------------------
  REM Purpose:
  REM This script drops all objects (except DATABASE LINK) of a user, which must
  REM be typed in after this script was started. Only one user can be specified
  REM for each run. The user typed in must be different to SYS or SYSTEM.
  REM -------------------------------------------------------------------------
  REM -------------------------------------------------------------------------
select 'Attention ! This script will drop all objects of the user you type in !' ATTENTION from dual;
select 'If you are not sure to finish, so break with ctrl+d !' ATTENTION from dual;
accept user_to_clean prompt "Enter user to drop all his objects: "
set linesize 120 heading off termout off pagesize 2000 recsep 0 trimspool on
set feedback off echo off show off ver off
set serveroutput on
spool drop_all_objects_of_selected_user_l1.sql
declare
        cnt_user_objects number;
        ora_version number;
begin
        dbms_output.enable;
        select max(to_number(substr(version,1,1))) into ora_version from dba_registry;
        IF ora_version > 4 THEN
--              dbms_output.put_line('This is Oracle-version lower than version 10');
                dbms_output.put_line('set feedback off echo off show off ver off');
                dbms_output.put_line('set linesize 120 heading off termout off pagesize 2000 trimspool on');
                dbms_output.put_line('spool drop_all_objects_of_selected_user_l2.sql');
                dbms_output.put_line('select '||'''spool drop_all_objects_of_user_'''||'||'||'''&&user_to_clean'''||'||'||'''.log'''||' from dual;');
                dbms_output.put_line('select '||'''drop table "'''||'||'||'owner'||'||'||'''"."'''||'||'||'table_name'||'||'||'''" cascade constraints;'''||' from dba_tables where owner=upper('||'''&&user_to_clean'''||') AND owner not in ('||'''SYS'''||','||'''SYSTEM'''||');');
                dbms_output.put_line('select '||'''drop '''||'||'||'object_type'||'||'||''' "'''||'||'||'owner'||'||'||'''"."'''||'||'||'object_name'||'||'||'''";'''||' from dba_objects where owner=upper('||'''&&user_to_clean'''||') AND owner not in ('||'''SYS'''||','||'''SYSTEM'''||') AND object_type not in ('||'''TABLE'''||','||'''DATABASE LINK'''||','||'''INDEX'''||','||'''TRIGGER'''||','||'''PACKAGE BODY'''||','||'''LOB'''||');');
                dbms_output.put_line('select '||'''spool off'''||' from dual;');
                dbms_output.put_line('spool off');
                dbms_output.put_line('@@drop_all_objects_of_selected_user_l2.sql');
        ELSE
--              dbms_output.put_line('This is Oracle-version 10. Note: Objects are dropped with purge-option!');
                dbms_output.put_line('set feedback off echo off show off ver off');
                dbms_output.put_line('set linesize 120 heading off termout off pagesize 2000 trimspool on');
                dbms_output.put_line('spool drop_all_objects_of_selected_user_l2.sql');
                dbms_output.put_line('select '||'''spool drop_all_objects_of_user_'''||'||'||'''&&user_to_clean'''||'||'||'''.log'''||' from dual;');
                dbms_output.put_line('select '||'''drop table "'''||'||'||'owner'||'||'||'''"."'''||'||'||'table_name'||'||'||'''" cascade constraints purge;'''||' from dba_tables where owner=upper('||'''&&user_to_clean'''||') AND owner not in ('||'''SYS'''||','||'''SYSTEM'''||');');
                dbms_output.put_line('select '||'''drop '''||'||'||'object_type'||'||'||''' "'''||'||'||'owner'||'||'||'''"."'''||'||'||'object_name'||'||'||'''";'''||' from dba_objects where owner=upper('||'''&&user_to_clean'''||') AND owner not in ('||'''SYS'''||','||'''SYSTEM'''||') AND object_type not in ('||'''TABLE'''||','||'''DATABASE LINK'''||','||'''INDEX'''||','||'''TRIGGER'''||','||'''PACKAGE BODY'''||','||'''LOB'''||');');
                dbms_output.put_line('select '||'''spool off'''||' from dual;');
                dbms_output.put_line('spool off');
                dbms_output.put_line('@@drop_all_objects_of_selected_user_l2.sql');
        END IF;
        EXCEPTION
        WHEN OTHERS THEN
                dbms_output.put_line(SQLERRM);
END;
/
spool off
@@drop_all_objects_of_selected_user_l1.sql
set termout on
set serveroutput on
declare
        cnt_user_objects number;
begin
dbms_output.enable;
select count(*) into cnt_user_objects from dba_objects where owner=upper('&&user_to_clean');
        IF cnt_user_objects > 0
           THEN
                dbms_output.put_line('Please restart this script again ! There are objects left in schema '||'&&user_to_clean'||' !');
           ELSE
                dbms_output.put_line('All objects of user '||'&&user_to_clean'||' were dropped !');
        END IF;
        EXCEPTION
        WHEN OTHERS THEN
                dbms_output.put_line(SQLERRM);
END;
/
!rm drop_all_objects_of_selected_user_l1.sql
!rm drop_all_objects_of_selected_user_l2.sql

##########################
                                                                                V1-V3)
backup_abc.log
2013-01-07 02:43:19 +0100( 22310. 0) SESSION  OUT > RMAN-06005: connected to target database: QMCRMDBP (DBID=1706730871)
2013-01-07 02:43:19 +0100( 22310. 0) SESSION  OUT > RMAN-06008: connected to recovery catalog database
2013-01-07 02:43:19 +0100( 22310. 0) SESSION  OUT > 2>
2013-01-07 02:43:19 +0100( 22310. 0) SESSION  OUT >
2013-01-07 02:43:19 +0100( 22310. 0) SESSION  OUT > 19>
2013-01-07 02:43:19 +0100( 22310. 0) SESSION  OUT >
2013-01-07 02:43:19 +0100( 22310. 0) SESSION  OUT > 20>
2013-01-07 02:43:19 +0100( 22310. 0) SESSION  OUT > 23>
2013-01-07 02:48:16 +0100( 22310. 0) SESSION  OUT > RMAN-08512: waiting for snapshot control file enqueue
2013-01-07 02:53:31 +0100( 22310. 0) SESSION  OUT > RMAN-08512: waiting for snapshot control file enqueue
2013-01-07 02:58:46 +0100( 22310. 0) SESSION  OUT > RMAN-08512: waiting for snapshot control file enqueue
2013-01-07 03:04:01 +0100( 22310. 0) SESSION  OUT > RMAN-08512: waiting for snapshot control file enqueue
2013-01-07 03:09:16 +0100( 22310. 0) SESSION  OUT > RMAN-08512: waiting for snapshot control file enqueue
2013-01-07 03:14:31 +0100( 22310. 0) SESSION  OUT > RMAN-08512: waiting for snapshot control file enqueue
2013-01-07 03:19:46 +0100( 22310. 0) SESSION  OUT > RMAN-08512: waiting for snapshot control file enqueue
2013-01-07 03:25:01 +0100( 22310. 0) SESSION  OUT > RMAN-08512: waiting for snapshot control file enqueue
2013-01-07 03:30:15 +0100( 22310. 0) SESSION  OUT > RMAN-08512: waiting for snapshot control file enqueue
2013-01-07 03:35:30 +0100( 22310. 0) SESSION  OUT > RMAN-08512: waiting for snapshot control file enqueue
2013-01-07 03:40:45 +0100( 22310. 0) SESSION  OUT > RMAN-08512: waiting for snapshot control file enqueue
2013-01-07 03:46:00 +0100( 22310. 0) SESSION  OUT > RMAN-08512: waiting for snapshot control file enqueue
2013-01-07 03:46:21 +0100( 22310. 0) SESSION  OUT > RMAN-20029: cannot make a snapshot control file
2013-01-07 03:46:21 +0100( 22310. 0) SESSION  OUT > RMAN-00571: ===========================================================
2013-01-07 03:46:21 +0100( 22310. 0) SESSION  OUT > RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
2013-01-07 03:46:21 +0100( 22310. 0) SESSION  OUT > RMAN-00571: ===========================================================
2013-01-07 03:46:21 +0100( 22310. 0) SESSION  OUT > RMAN-03002: failure of configure command at 01/07/2013 03:46:21
2013-01-07 03:46:21 +0100( 22310. 0) SESSION  OUT > RMAN-03014: implicit resync of recovery catalog failed
2013-01-07 03:46:21 +0100( 22310. 0) SESSION  OUT > RMAN-03009: failure of full resync command on default channel at 01/07/2013 03:46:21
2013-01-07 03:46:21 +0100( 22310. 0) SESSION  OUT > ORA-00230: operation disallowed: snapshot control file enqueue unavailable

#######################

Solution:

SQL> SELECT vs.sid, vs.username, vs.program, vs.module, TO_CHAR(vs.logon_time, 'DD-MON-YYYY HH24:MI:SS')
FROM v$session vs, v$enqueue_lock vel
WHERE vs.sid = vel.sid AND vel.type = 'CF' AND vel.id1 = 0 AND vel.id2 = 2
  2    3    4
SQL> /
          SID USERNAME                       PROGRAM              MODULE                         TO_CHAR(VS.LOGON_TIME,'DD-MON
---------- ------------------------------ -------------------- ------------------------------ -----------------------------
        24 SYS                            rman@s96qmcr0d0 (TNS backup full datafile           24-NOV-2012 06:27:36
                                           V1-V3)


SQL> select SID,SERIAL#,USERNAME,COMMAND,PROCESS,PROGRAM from v$session where sid=24;
 
       SID    SERIAL# USERNAME                COMMAND PROCESS                  PROGRAM              LOGON_TIM
---------- ---------- -------------------- ---------- ------------------------ -------------------- ---------
        24      10203 SYS                           0 29489                    rman@s96qmcr0d0 (TNS 24-NOV-12V1-V3)
       
       
SQL> alter system kill session '24,10203';
alter system kill session '24,10203'
*
ERROR at line 1:
ORA-00031: session marked for kill

      SID    SERIAL# USERNAME                          COMMAND PROCESS                  PROGRAM              STATUS
---------- ---------- ------------------------------ ---------- ------------------------ -------------------- --------
        24      10203 SYS                                     0 29489                    rman@s96qmcr0d0 (TNS KILLED

SELECT P.SPID, S.SID, S.SERIAL#
FROM V$PROCESS P, V$SESSION S
WHERE P.ADDR = S.PADDR
AND S.SID =24
-> Kill the process id from os level

-> Resync catalog;
-> Triger fresh backup.

RMAN error 00230 and 00230 to backup.

Issue:


##########################
                                                                                V1-V3)
backup_abc.log
2013-01-07 02:43:19 +0100( 22310. 0) SESSION  OUT > RMAN-06005: connected to target database: QMCRMDBP (DBID=1706730871)
2013-01-07 02:43:19 +0100( 22310. 0) SESSION  OUT > RMAN-06008: connected to recovery catalog database
2013-01-07 02:43:19 +0100( 22310. 0) SESSION  OUT > 2>
2013-01-07 02:43:19 +0100( 22310. 0) SESSION  OUT >
2013-01-07 02:43:19 +0100( 22310. 0) SESSION  OUT > 19>
2013-01-07 02:43:19 +0100( 22310. 0) SESSION  OUT >
2013-01-07 02:43:19 +0100( 22310. 0) SESSION  OUT > 20>
2013-01-07 02:43:19 +0100( 22310. 0) SESSION  OUT > 23>
2013-01-07 02:48:16 +0100( 22310. 0) SESSION  OUT > RMAN-08512: waiting for snapshot control file enqueue
2013-01-07 02:53:31 +0100( 22310. 0) SESSION  OUT > RMAN-08512: waiting for snapshot control file enqueue
2013-01-07 02:58:46 +0100( 22310. 0) SESSION  OUT > RMAN-08512: waiting for snapshot control file enqueue
2013-01-07 03:04:01 +0100( 22310. 0) SESSION  OUT > RMAN-08512: waiting for snapshot control file enqueue
2013-01-07 03:09:16 +0100( 22310. 0) SESSION  OUT > RMAN-08512: waiting for snapshot control file enqueue
2013-01-07 03:14:31 +0100( 22310. 0) SESSION  OUT > RMAN-08512: waiting for snapshot control file enqueue
2013-01-07 03:19:46 +0100( 22310. 0) SESSION  OUT > RMAN-08512: waiting for snapshot control file enqueue
2013-01-07 03:25:01 +0100( 22310. 0) SESSION  OUT > RMAN-08512: waiting for snapshot control file enqueue
2013-01-07 03:30:15 +0100( 22310. 0) SESSION  OUT > RMAN-08512: waiting for snapshot control file enqueue
2013-01-07 03:35:30 +0100( 22310. 0) SESSION  OUT > RMAN-08512: waiting for snapshot control file enqueue
2013-01-07 03:40:45 +0100( 22310. 0) SESSION  OUT > RMAN-08512: waiting for snapshot control file enqueue
2013-01-07 03:46:00 +0100( 22310. 0) SESSION  OUT > RMAN-08512: waiting for snapshot control file enqueue
2013-01-07 03:46:21 +0100( 22310. 0) SESSION  OUT > RMAN-20029: cannot make a snapshot control file
2013-01-07 03:46:21 +0100( 22310. 0) SESSION  OUT > RMAN-00571: ===========================================================
2013-01-07 03:46:21 +0100( 22310. 0) SESSION  OUT > RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
2013-01-07 03:46:21 +0100( 22310. 0) SESSION  OUT > RMAN-00571: ===========================================================
2013-01-07 03:46:21 +0100( 22310. 0) SESSION  OUT > RMAN-03002: failure of configure command at 01/07/2013 03:46:21
2013-01-07 03:46:21 +0100( 22310. 0) SESSION  OUT > RMAN-03014: implicit resync of recovery catalog failed
2013-01-07 03:46:21 +0100( 22310. 0) SESSION  OUT > RMAN-03009: failure of full resync command on default channel at 01/07/2013 03:46:21
2013-01-07 03:46:21 +0100( 22310. 0) SESSION  OUT > ORA-00230: operation disallowed: snapshot control file enqueue unavailable

#######################

Solution:
SELECT vs.sid, vs.username, vs.program, vs.module, TO_CHAR(vs.logon_time, 'DD-MON-YYYY HH24:MI:SS') FROM v$session vs, v$enqueue_lock vel WHERE vs.sid = vel.sid AND vel.type = 'CF' AND vel.id1 = 0 AND vel.id2 = 2



          SID USERNAME                       PROGRAM              MODULE                         TO_CHAR(VS.LOGON_TIME,'DD-MON
---------- ------------------------------ -------------------- ------------------------------ -----------------------------
        24 SYS                            rman@s96qmcr0d0 (TNS backup full datafile           24-NOV-2012 06:27:36
                                           V1-V3)


select SID,SERIAL#,STATUS,USERNAME,COMMAND,PROCESS,PROGRAM from v$session where sid=121;
 
       SID    SERIAL# USERNAME                COMMAND PROCESS                  PROGRAM              LOGON_TIM
---------- ---------- -------------------- ---------- ------------------------ -------------------- ---------
        24      10203 SYS                           0 29489                    rman@s96qmcr0d0 (TNS 24-NOV-12V1-V3)
       
       
SQL> alter system kill session '24,10203';
alter system kill session '24,10203'
*
ERROR at line 1:
ORA-00031: session marked for kill

select SID,SERIAL#,STATUS,USERNAME,COMMAND,PROCESS,PROGRAM from v$session where sid=121;

      SID    SERIAL# USERNAME                          COMMAND PROCESS                  PROGRAM              STATUS
---------- ---------- ------------------------------ ---------- ------------------------ -------------------- --------
        24      10203 SYS                                     0 29489                    rman@s96qmcr0d0 (TNS KILLED

SELECT P.SPID, S.SID, S.SERIAL#
FROM V$PROCESS P, V$SESSION S
WHERE P.ADDR = S.PADDR
AND S.SID =24
-> Kill the process id from os level

-> Resync catalog;

-> Trigger fresh backup.

Patching steps and details PSU

 Master link

http://www.oracleportal.org/

================



Very good link for oracle patching
===========================


What is Patch


http://www.oracleportal.org/knowledge-base/oracle-database/database-concepts/installation-and-patching/patching/patches.aspx



=================
OPATCH



http://www.oracleportal.org/knowledge-base/oracle-database/database-concepts/installation-and-patching/patching/opatch-utility.aspx

=================

Apply PSU Patch in Oracle
* You Must have two thing to apply PSU Patch :
1-Lastest version For Optach.
2-PSU Patch that you want to apply.


Steps:

$ORACLE_HOME/OPatch/opatch version
Output will be:
Invoking OPatch 11.2.0.1.7
OPatch Version:  11.2.0.1.7
OPatch succeeded

1-you need to Update latest version For Optach, to do this :

cd $ORACLE_HOME

cp -r Optach/ /u01/backup/Optach

**make sure you in ORACLE_HOME

rm -rf $ORACLE_HOME/Optach

unzip Optach_that_you_download_from_MOS inside $ORACLE_HOME.

Patch Number :6880880

2-Check Optach After Doing Above Steps :

$ORACLE_HOME/OPatch/opatch version
Output will be:
Invoking OPatch 11.2.0.3.0
OPatch Version:  11.2.0.3.0
OPatch succeeded

3-Apply PSU Patch By Doing the Following, for example :

unzip p13923374_11203_.zip
cd 13923374
opatch apply

Answer the question that you been asked by Oracle

4- Post Installation Steps :

cd $ORACLE_HOME/rdbms/admin
sqlplus /nolog
 SQL> CONNECT / AS SYSDBA
SQL> STARTUP
SQL> @catbundle.sql psu apply
SQL> QUIT


==============

                                                                     How to Apply Patch

========================================================================

This is the latest Patch Set Update for the 11.2.0.2.0 RDBMS Home.

The OPatch utility version should be higher than 11.2.0.1.3 to apply the patch.

How to check and update the opatch utility?

1-you need to Update latest version For Optach, to do this :

cd $ORACLE_HOME

cp -r Optach/ /u01/backup/Optach

**make sure you in ORACLE_HOME

rm -rf $ORACLE_HOME/Optach

unzip Optach_that_you_download_from_MOS  inside $ORACLE_HOME.

Patch Number :6880880

=================================

Go to support.oracle.com and download the 12827726 Patch Set Update

Transfer the file on to the remote server

[I copied it to the /oratemp01/11202 directory on the remote server] and unzip the file using

unzip p12827726_112020_Linux-x86-64.zip

Set the environment variables.

I am going demonstrate by applying the patch to the 11.2.0.2.0 RDBMS Home.

export ORACLE_HOME=/u01/app/oracle/product/11.2.0/db11202_s

export PATH=$ORACLE_HOME/bin:$PATH:$ORACLE_HOME/OPatch

========================

Determine whether any currently installed one-off patches conflict with the PSU patch as follows:

cd /oratemp01/11202/

[Note: This is the directory where I copied the psu zip file and unzipped]

================================

Run the following command

opatch prereq CheckConflictAgainstOHWithDetail -phBaseDir ./12827726

=================================

Here is the output and you can notice that the pre-req checks succeed

Invoking OPatch 11.2.0.1.8

Oracle Interim Patch Installer version 11.2.0.1.8

Copyright (c) 2011, Oracle Corporation. All rights reserved.

PREREQ session

Oracle Home : /u01/app/oracle/product/11.2.0/db11202_s

Central Inventory : /u01/app/oracle/oraInventory

from : /etc/oraInst.loc

OPatch version : 11.2.0.1.8

OUI version : 11.2.0.2.0

Log file location : /u01/app/oracle/product/11.2.0/db11202_s/cfgtoollogs/opatch/opatch2011-11-10_09-21-35AM.log

Invoking prereq "checkconflictagainstohwithdetail"

Prereq "checkConflictAgainstOHWithDetail" passed.

OPatch succeeded.

==================

If you are applying the patch to the RDBMS_HOME;

shutdown all the databases associated with that RDBMS_HOME.

Apply the patch by issuing the following command.

You may be asked for your metalink credentials.

====================

cd 12827726

opatch apply

================================

[oracle@xyz 12827726]$ opatch apply

Invoking OPatch 11.2.0.1.8

Oracle Interim Patch Installer version 11.2.0.1.8

Copyright (c) 2011, Oracle Corporation. All rights reserved.

Oracle Home : /u01/app/oracle/product/11.2.0/db11202_s

Central Inventory : /u01/app/oracle/oraInventory

from : /etc/oraInst.loc

OPatch version : 11.2.0.1.8

OUI version : 11.2.0.2.0

Log file location : /u01/app/oracle/product/11.2.0/db11202_s/cfgtoollogs/opatch/opatch2011-11-10_09-21-45AM.log

Applying interim patch '12827726' to OH '/u01/app/oracle/product/11.2.0/db11202_s'

Verifying environment and performing prerequisite checks...

Do you want to proceed? [y|n]

y

User Responded with: Y

All checks passed.

Provide your email address to be informed of security issues, install and

initiate Oracle Configuration Manager. Easier for you if you use your My

Oracle Support Email address/User Name.

Visit http://www.oracle.com/support/policies.html for details.

Email address/User Name:

You have not provided an email address for notification of security issues.

Do you wish to remain uninformed of security issues ([Y]es, [N]o) [N]: y

Please shutdown Oracle instances running out of this ORACLE_HOME on the local system.

(Oracle Home = '/u01/app/oracle/product/11.2.0/db11202_s')

Is the local system ready for patching? [y|n]

y

User Responded with: Y

Backing up files...

Patching component oracle.rdbms.rsf, 11.2.0.2.0...

Patching component oracle.rdbms, 11.2.0.2.0...

Copying file to "/u01/app/oracle/product/11.2.0/db11202_s/psu/11.2.0.2.4/catpsu.sql"

Copying file to "/u01/app/oracle/product/11.2.0/db11202_s/psu/11.2.0.2.4/catpsu_rollback.sql"

Copying file to "/u01/app/oracle/product/11.2.0/db11202_s/cpu/scripts/patch_8837510.sql"

Copying file to "/u01/app/oracle/product/11.2.0/db11202_s/cpu/scripts/emdb_recomp_invalids.sql"

Patching component oracle.sysman.console.db, 11.2.0.2.0...

Patching component oracle.sysman.oms.core, 10.2.0.4.3...

Patching component oracle.ldap.rsf, 11.2.0.2.0...

Patching component oracle.rdbms.dv, 11.2.0.2.0...

Patching component oracle.rdbms.dbscripts, 11.2.0.2.0...

Patching component oracle.sysman.plugin.db.main.repository, 11.2.0.2.0...

Patching component oracle.rdbms.rman, 11.2.0.2.0...

Patching component oracle.sdo.locator, 11.2.0.2.0...

patch 12827726 successfully applied

Log file location: /u01/app/oracle/product/11.2.0/db11202_s/cfgtoollogs/opatch/opatch2011-11-10_09-21-45AM.log

OPatch succeeded.

=====================================

STARTUP the databases with the newly patched RDBMS_HOME,

set ORACLE_SID variable and load modified sql files into the database.

Perform these steps

export ORACLE_HOME=/u01/app/oracle/product/11.2.0.2/db11202_r

export PATH=$ORACLE_HOME/bin:$ORACLE_HOME/OPatch:$PATH

cd $ORACLE_HOME/rdbms/admin

sqlplus / as sysdba

At the sql prompt enter the following commands:

@?/rdbms/admin/catbundle.sql psu apply

QUIT

=======================================