Monday, 20 July 2020

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 "

Add Data file

 ************************************************** Database status Tablespace Usage ASM Disk Group Usage(RAC) Backup Filesystem Usage Alert...