Create READ_ONLY on EBS

 FINAL ACTION PLAN

###################
  - Perform pre-health checks including huge pages health check if DB needs bounce - Use ATOM
  - Perform Autoconfig Health Check
  - Ensure that blackout is set
==> Take hot backup

      1. Take GRP backup using below command
         export OHSUPG_TOP=/autofs/upgrade/ohsupg
         $OHSUPG_TOP/bin/backup_db.sh -mode flashback -tag <RFC#>

      2. MT Backup : Take code tree backup.
    
==> Check adop status and check if there are any pending sessions
    - adop -status
==> If there are no pending sessions, please start prepare phase.
- adop phase=prepare
==> Source patch file edition and proceed with below steps to verify user and grants from patch file edition.
1: Login to DB Node as sys user

Note:READ_ONLY is already existing
--enable edition for new user
    alter user READ_ONLY enable editions;

2: GRANT CONNECT,RESOURCE TO READ_ONLY;

3: Grant READ_ONLY only access to READ_ONLY user to view all tables/views/seqence

Log into sqlplus As APPS user

set heading off pages 0 trimspool on lines 120 feedback off echo off termout off
spool /ood_repository/cm/scripts/3-B9YN3GP/createselect.sql
SELECT 'Grant select on APPS.'||object_name||' to READ_ONLY;' from dba_objects where owner='APPS' and object_TYPE in ('TABLE','VIEW','SEQUENCE');
SELECT  'Grant select on '||owner||'.'||table_name||' to READ_ONLY;' FROM dba_tab_privs WHERE privilege='SELECT' and grantee= 'APPS';
SELECT  'Grant execute on '||owner||'.'||table_name||' to READ_ONLY;' FROM dba_tab_privs WHERE privilege='EXECUTE' and grantee= 'APPS';
SELECT  'Grant execute on APPS.'||object_name||' to READ_ONLY;' FROM dba_objects WHERE object_type in ('PROCEDURE','FUNCTION','PACKAGE','PACKAGE BODY') and owner= 'APPS';
spool off

4: Create synonym for READ_ONLY

set heading off pages 0 trimspool on lines 120 feedback off echo off termout off
spool /ood_repository/cm/scripts/3-B9YN3GP/createsyn.sql
select 'create or replace synonym READ_ONLY.'||SYNONYM_NAME||' for '||TABLE_OWNER||'.'||table_name||';' from dba_synonyms where owner='APPS' and table_name in (select table_name from dba_tab_privs where privilege in ('SELECT','EXECUTE') and grantee='READ_ONLY');
SELECT  'create or replace synonym READ_ONLY.'||object_name||' for APPS.'||object_name||';' FROM dba_objects WHERE object_type in ('PROCEDURE','FUNCTION','PACKAGE','PACKAGE BODY','TABLE','VIEW','SEQUENCE') and owner= 'APPS';
spool off

6: Login as apps and run below
@/ood_repository/cm/scripts/3-B9YN3GP/createselect.sql

7: Login as apps user and run below
@/ood_repository/cm/scripts/3-B9YN3GP/createsyn.sql

Verifications
###############
Run below commands to verify READ_ONLY  user able to view all granted objects.

Connect READ_ONLY/<password>

SQL> select count(*),object_type from dba_objects where owner='READ_ONLY' group by object_type;
SQL> SELECT USERNAME,DEFAULT_TABLESPACE,TEMPORARY_TABLESPACE,PROFILE FROM DBA_USERS WHERE USERNAME='READ_ONLY';
SQL> select count(*) from dba_tab_privs where GRANTEE='APPS';
SQL> select count(*) FROM  dba_objects WHERE OWNER='APPS' and OBJECT_TYPE in ('PROCEDURE','FUNCTION','PACKAGE','PACKAGE BODY');
SQL> select count(*) FROM  dba_objects WHERE OWNER='READ_ONLY' and OBJECT_TYPE in ('PROCEDURE','FUNCTION','PACKAGE','PACKAGE BODY');



==> Run finalize
- adop phase=finalize

==> Run cutover ( Offline phase )
- adop phase=cutover mtrestart=yes
- adop phase=cleanup
- adop -status

==> Check and compile new invalids
==>  Perform post checks.

- Perform post-health checks including huge pages health check if DB needs bounce - Use ATOM
- Ensure that blackout is unset

- Take a count/list of invalid objects