script to take ddl of a role

 script to take DDL of a role 

set long 100000

set longchunksize 200

set heading off

set feedback off

set echo off

set verify off

undefine role

select (case

when ((select count(*)

from dba_roles

where role = '&&role') > 0)

then dbms_metadata.get_ddl ('ROLE', '&&role')

else to_clob ('Role does not exist')

end ) Extracted_DDL from dual

UNION ALL

select (case

when ((select count(*)

from dba_role_privs

where grantee = '&&role') > 0)

then dbms_metadata.get_granted_ddl ('ROLE_GRANT', '&&role')

end ) from dual

UNION ALL

select (case

when ((select count(*)

from dba_role_privs

where grantee = '&&role') > 0)

then dbms_metadata.get_granted_ddl ('DEFAULT_ROLE', '&&role')

end ) from dual

UNION ALL

select (case

when ((select count(*)

from dba_sys_privs

where grantee = '&&role') > 0)

then dbms_metadata.get_granted_ddl ('SYSTEM_GRANT', '&&role')

end ) from dual

UNION ALL

select (case

when ((select count(*)

from dba_tab_privs

where grantee = '&&role') > 0)

then dbms_metadata.get_granted_ddl ('OBJECT_GRANT', '&&role')

end ) from dual;        

set linesize 80 pagesize 14 feedback on verify on

Post Refresh privs execution after refreshing the environment

ORAENV_ASK=NO
export ORACLE_SID=$1
. oraenv -s
ORAENV_ASK=YES
sqlplus "/as sysdba" <<EOF
set echo on
set head on
set feedback on
set verify on
set time on
CREATE  PROFILE REFRESH_TEMP 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       10
PASSWORD_LIFE_TIME          UNLIMITED
PASSWORD_REUSE_TIME         UNLIMITED
PASSWORD_REUSE_MAX          UNLIMITED
PASSWORD_LOCK_TIME          1
PASSWORD_GRACE_TIME         7;
spool user_create.log
@user_create.sql
spool off
spool password.log
@pass_profile.sql
spool off
spool privileges.log
@privs.sql
spool off
drop profile REFRESH_TEMP ;
spool synonyms.log
@synonyms.sql
spool off
exit
EOF

echo " COMPLETED "

Backup of Privs as Pre Refresh script

Make Pre-Refresh script.sql as below

ORAENV_ASK=NO
export ORACLE_SID=$1
. oraenv -s
ORAENV_ASK=YES
sqlplus -s "/as sysdba" <<EOF
set echo off
set head off
set verify off
set feedback off
set pages 0
set lines 200
spool user_create.sql
select 'create user '||USERNAME||' identified by Welcome#123 default tablespace '||DEFAULT_TABLESPACE||' temporary tablespace '||TEMPORARY_TABLESPACE||' profile REFRESH
_TEMP ;' from dba_users ;
select 'alter user '||USERNAME||'  profile REFRESH_TEMP  ;' from dba_users ;
spool off
spool pass_profile.sql
select 'alter user '||name||' identified by values '''||password||''' ;' from sys.user$ where password is not null;
select 'alter user '||USERNAME||'  profile '||profile||' ;' from dba_users ;
spool off
spool privs.sql
select 'create role '||role||' ;' from dba_roles ;
select 'grant '||PRIVILEGE||' to '||GRANTEE||''||ADMIN||'  ;' from (select grantee,PRIVILEGE,(case ADMIN_OPTION when 'YES' then ' WITH ADMIN OPTION' end ) ADMIN from db
a_sys_privs ) ;
select 'grant '||GRANTED_ROLE||' to '||GRANTEE||''||ADMIN||'  ;' from (select grantee,GRANTED_ROLE,(case ADMIN_OPTION when 'YES' then ' WITH ADMIN OPTION' end ) ADMIN f
rom dba_role_privs ) ;
select 'grant '||PRIVILEGE||' on '||OWNER||'.'||TABLE_NAME||' to '||GRANTEE||''||ADMIN||'  ;'  from (select grantee,PRIVILEGE,owner,table_name,(case GRANTABLE when 'YES
' then ' WITH GRANT OPTION' end ) ADMIN from dba_tab_privs ) where GRANTEE not in ('SYS') ;
spool off
spool synonyms.sql
select 'create or replace synonym '||owner||'.'||synonym_name||' for '||table_owner||'.'||table_name||' ;' from dba_synonyms where owner not in ('PUBLIC','SYS','SYSTEM'
,'DBSNMP','APPQOSSYS') and table_owner not in ('SYSTEM','DBAMONITOR','APPQOSSYS','SYS') and DB_LINK is null ;
select 'create or replace synonym '||owner||'.'||synonym_name||' for '||table_owner||'.'||table_name||'@'||DB_LINK||' ;' from dba_synonyms where owner not in ('PUBLIC',
'SYS','SYSTEM','DBSNMP','APPQOSSYS') and table_owner not in ('SYSTEM','DBAMONITOR','APPQOSSYS','SYS') and DB_LINK is not null ;
select 'create or replace public synonym '||synonym_name||' for '||table_owner||'.'||table_name||' ;' from dba_synonyms where owner in ('PUBLIC') and table_owner not in
 ('SYSTEM','DBAMONITOR','APPQOSSYS','SYS') and DB_LINK is null ;
select 'create or replace public synonym '||synonym_name||' for '||table_owner||'.'||table_name||'@'||DB_LINK||' ;' from dba_synonyms where owner in ('PUBLIC') and tabl
e_owner not in ('SYSTEM','DBAMONITOR','APPQOSSYS','SYS') and DB_LINK is not null ;
spool off
exit
EOF
echo " COMPLETED "

how to find DDL of privilege of user

DDL of privilege of  user


select 'grant ' || privilege ||' on ' || owner || '.' || table_name || ' to "' || grantee ||'"'|| decode(grantable,'YES',' WITH GRANT OPTION',null) || ';' "TXT" 
from dba_tab_privs where grantee in (select username from dba_users where username  in ('xxxxx'));

Lists all locked objects.

SET LINESIZE 500
SET PAGESIZE 1000
SET VERIFY OFF
COLUMN owner FORMAT A20
COLUMN username FORMAT A20
COLUMN object_owner FORMAT A20
COLUMN object_name FORMAT A30
COLUMN locked_mode FORMAT A15

SELECT lo.session_id AS sid,
       s.serial#,
       NVL(lo.oracle_username, '(oracle)') AS username,
       o.owner AS object_owner,
       o.object_name,
       Decode(lo.locked_mode, 0, 'None',
                             1, 'Null (NULL)',
                             2, 'Row-S (SS)',
                             3, 'Row-X (SX)',
                             4, 'Share (S)',
                             5, 'S/Row-X (SSX)',
                             6, 'Exclusive (X)',
                             lo.locked_mode) locked_mode,
       lo.os_user_name
FROM   v$locked_object lo
       JOIN dba_objects o ON o.object_id = lo.object_id
       JOIN v$session s ON lo.session_id = s.sid
ORDER BY 1, 2, 3, 4;

Lists all objects being accessed in the schema

SET LINESIZE 255
SET VERIFY OFF
COLUMN object FORMAT A30
SELECT a.object,
       a.type,
       a.sid,
       b.serial#,
       b.username,
       b.osuser,
       b.program
FROM   v$access a,
       v$session b
WHERE  a.sid    = b.sid
AND    a.owner  = DECODE(UPPER('&1'), 'ALL', a.object, UPPER('&1'))
AND    a.object = DECODE(UPPER('&2'), 'ALL', a.object, UPPER('&2'))
ORDER BY a.object;

11gR2 Clusterware Startup Sequence (RAC)

This description is also from the MOS note stated above

Short summary of the startup sequence: INIT spawns init.ohasd (with respawn) which in turn starts the OHASD process (Oracle High Availability Services Daemon).  This daemon spawns 4 processes.

Level 1: OHASD Spawns:

    cssdagent         - Agent responsible for spawning CSSD.
    orarootagent     - Agent responsible for managing all root owned ohasd resources.
    oraagent         - Agent responsible for managing all oracle owned ohasd resources.
    cssdmonitor        - Monitors CSSD and node health (along wth the cssdagent).
   

Level 2: OHASD rootagent spawns:
    CSDD (ora.cssd)     - Cluster Synchronization Services
    CRSD(ora.crsd)     - Primary daemon responsible for managing cluster resources.
    CTSSD(ora.ctssd)     - Cluster Time Synchronization Services Daemon
    Diskmon(ora.diskmon)
    ACFS (ASM Cluster File System) Drivers

Level 2: OHASD oraagent spawns:

    MDNSD(ora.mdnsd)     - Used for DNS lookup
    GIPCD(ora.gipcd)     - Used for inter-process and inter-node communication
    GPNPD(ora.gpnpd)     - Grid Plug & Play Profile Daemon
    EVMD(ora.evmd)     - Event Monitor Daemon
    ASM(ora.asm)     - Resource for monitoring ASM instances

Level 3: CRSD spawns:

    orarootagent     - Agent responsible for managing all root owned crsd resources.
    oraagent         - Agent responsible for managing all oracle owned crsd resources.

Level 4: CRSD rootagent spawns:

    Network resource     - To monitor the public network
    SCAN VIP(s)     - Single Client Access Name Virtual IPs
    Node VIPs         - One per node
    ACFS Registery     - For mounting ASM Cluster File System
    GNS VIP (optional)     - VIP for GNS

Level 4: CRSD oraagent spawns:

    ASM Resouce     - ASM Instance(s) resource
    Diskgroup         - Used for managing/monitoring ASM diskgroups.
    DB Resource     - Used for monitoring and managing the DB and instances
    SCAN Listener     - Listener for single client access name, listening on SCAN VIP
    Listener         - Node listener listening on the Node VIP
    Services         - Used for monitoring and managing services
    ONS         - Oracle Notification Service
    eONS         - Enhanced Oracle Notification Service
    GSD         - For 9i backward compatibility
    GNS (optional)     - Grid Naming Service - Performs name resolution