Schedule SQL Server Trace

 https://www.mssqltips.com/sqlservertip/1715/scheduling-a-sql-server-profiler-trace/

gyrfalcon.bi_test.trc_dexter

job

SqlTrace

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

SQL SERVER grant user

 grant select on ps_lake_demand_utilization  to [engg_demand_util];

Oracle EBS domain change

 https://support.oracle.com/epmos/faces/DocumentDisplay?_afrLoop=446100599746504&id=759225.1&_afrWindowMode=0&_adf.ctrl-state=ewe2g0oce_53

E-Business Suite Applications Technology Stack Steps To Change the Domain Name for an E-Business Suite 11i / R12 Instance (Doc ID 759225.1)

Oracle ebs recreate context_file

https://abduulwasiq.blogspot.com/2013/09/if-we-lost-contextfile.html

Oracle display current time to a different timezone

 SELECT username, TO_CHAR(
         FROM_TZ( CAST( created AS TIMESTAMP ), 'AMERICA/PHOENIX' )
           AT TIME ZONE 'ASIA/MANILA',
         'YYYY-MM-DD HH24:MI:SS TZH:TZM TZR'
       ) AS MANILA_time, created
FROM   dba_users where username like 'RO_%' or username='ORA_AUDIT' order by 1;

SELECT  TO_CHAR(
         FROM_TZ( CAST( sysdate AS TIMESTAMP ), 'AMERICA/PHOENIX' )
           AT TIME ZONE 'ASIA/MANILA',
         'YYYY-MM-DD HH24:MI:SS TZH:TZM TZR'
       ) AS manila_time, sysdate
FROM   dual;

eTrace delete test

create table Rowcounter (
rowcountt int,
timestampcol datetime default current_timestamp);

create job

Batchdelete

declare @row as int

declare @ctr as int = 0

declare @subj as varchar(300)

select @row = count(tdid) from dxT_TDHeader

while @ctr <= @row

begin

delete top(50000) from dxt_TDItem where tdid in (select tdid from dxT_TDHeader)

insert into rowcounter(rowcountt) values(@@rowcount)

set @ctr = @ctr + 50000

end

select * from rowcounter;