Adithya's Oracle DBA Blog: Oracle DB Session Killing scripts

Oracle DB Session Killing scripts

BEGIN
FOR r IN (select sid,serial# from v$session where username = 'EDBAR')
LOOP
EXECUTE IMMEDIATE 'alter system kill session ''' || r.sid || ',' || r.serial# || '''immediate;';
END LOOP;
END;

select 'alter system kill session ''' || r.sid || ',' || r.serial# || ''' immediate;' from v$session r where username = 'EDBAR';

select 'alter system kill session ''' || r.sid || ',' || r.serial# || ''' immediate;'
from v$session r where username ='EL1DVL11' and logon_time <sysdate-1/4

select 'alter system kill session ''' || r.sid || ',' || r.serial# || ''' immediate;' from v$session r where r.program like '%rman%';

select 'alter system kill session ''' || r.sid || ',' || r.serial# || ''' immediate;'
from v$session r where username ='EL1DVL11';

42 comments:

AnonymousFriday, February 26, 2021
#!/bin/ksh
#
PATH=/usr/bin:/bin:$PATH
export PATH
#------------------------------------------------------------------------------
export DBA_EMAIL=adithya.sirimalla@verizon.com
#------------------------------------------------------------------------------
export DB_U_NAME=UOTMPRD
export DB_U_N=$DB_U_NAME:

GIDtls=`grep -i "+ASM" "$ORATAB" | sed -e 's/ //g'`
DBDtls=`grep -i "$DB_U_N" "$ORATAB" | sed -e 's/ //g'`
GIHome="`echo ${GIDtls} | cut -d: -f2`"
export PATH=$PATH:/$GIHome/bin

Node=`olsnodes -l -n| cut -f2`
INnameCnt=0
INameCnt="`echo ${DBDtls} | cut -d: -f1|sed '/^\s*$/d'|wc -l`"
IHome="`echo ${DBDtls} | cut -d: -f2`"
export ORACLE_HOME="$IHome"
export ORACLE_SID=${DB_U_NAME}$Node
#export ORACLE_SID=$1
export PATH=$PATH:/$ORACLE_HOME/bin

#------------------------------------------
cd /opt/oracle/sirimad
sqlplus / AS SYSDBA <<EOF
spool /opt/oracle/sirimad/run_script.log
set echo on
set time on
set timing on
@ipm_12354727.sql
exit;
EOF
ReplyDelete
Replies
AnonymousFriday, March 05, 2021
CREATE USER GG_SCHEMA IDENTIFIED BY "XXXXXXXXX" DEFAULT TABLESPACE GGS_DATA TEMPORARY TABLESPACE TEMP
PROFILE SYSACCOUNT_PROFILE
ACCOUNT UNLOCK;
GRANT DBA TO GG_SCHEMA;
ALTER USER GG_SCHEMA DEFAULT ROLE ALL;
GRANT UNLIMITED TABLESPACE TO GG_SCHEMA;
ALTER USER GG_SCHEMA QUOTA UNLIMITED ON GGS_DATA;
GRANT connect,resource to gg_schema;
GRANT select any dictionary, select any table to gg_schema;
GRANT create table to gg_schema;
GRANT alter any table to gg_schema;
GRANT flashback any table to gg_schema;
GRANT execute on dbms_flashback to gg_schema;
GRANT execute on utl_file to gg_schema;
GRANT CREATE SESSION, ALTER SESSION TO gg_schema;
exec dbms_streams_auth.GRANT_admin_privilege('gg_schema')
GRANT become user to gg_schema;
ReplyDelete
Replies
AnonymousThursday, March 18, 2021
The SQL, accounts for any tblsps already encrypted.
Set linesize 200
Set pagesize 0
Set heading off
select 'alter database datafile '||chr(39)||df.name||chr(39)||' encrypt;' COMMAND from v$tablespace ts, v$datafile df where ts.ts#=df.ts# and (ts.name not in ('SYSTEM','SYSAUX') and ts.name not in (select value from gv$spparameter where name='undo_tablespace')) and
ts.ts# not in (select e.ts# FROM v$encrypted_tablespaces e) order by ts.name;
exit
i.e. OUTPUT:
alter database datafile '+DATAC1/{db_name}/DATAFILE/users.307.975552057' encrypt;

Put the above output in a script.
srvctl stop database -d {db_name}
srvctl start database -d {db_name} -o mount

Via sqlplus, you can run the above script:

Shutdown the database and start in open mode:
srvctl stop database -d {db_name}
srvctl start database -d {db_name}
ReplyDelete
Replies
AnonymousWednesday, May 05, 2021
elect sid "waiter",a.inst_id "Waiting_Inst", username, a.sql_id "Blocked sql",
b.sql_text,blocking_session "blocker",blocking_instance "Blocking_Inst",
seconds_in_wait "secs wait", wait_time
from gv$session a , gv$sql b
where state in ('WAITING')
and wait_class != 'Idle'
and a.sql_id=b.sql_id
and a.inst_id=b.inst_id and blocking_session is not null
order by seconds_in_wait desc;

--------

select sid "waiter",a.inst_id "Waiting_Inst", username, a.sql_id "Blocked sql",
b.sql_text,blocking_session "blocker",blocking_instance "Blocking_Inst",
seconds_in_wait "secs wait", wait_time
from gv$session a , gv$sql b
where state in ('WAITING')
and wait_class != 'Idle'
and a.sql_id=b.sql_id
and a.inst_id=b.inst_id and blocking_session is not null and blocking_session not in
(
select sid "waiter"
from gv$session a , gv$sql b
where state in ('WAITING')
and wait_class != 'Idle'
and a.sql_id=b.sql_id
and a.inst_id=b.inst_id and blocking_session is not null
)
order by seconds_in_wait desc;

======================
another one:

select
(select 'alter system kill session '|| '''' || sid || ',' || serial# ||',@'||inst_id|| ''' immediate ;'
from gv$session where sid=a.sid and inst_id = a.inst_id and username like '%' ),
(select '-- '||username || ' on inst_id ' || inst_id||',sid '||sid||',serial# '||serial#
from gv$session where sid=a.sid and inst_id = a.inst_id and username like '%') blocker,
'-- is blocking ',
(select '-- '||g.username || ' on inst_id ' || g.inst_id||',sid '||g.sid||',serial# '||g.serial#||' sql_id '||g.sql_id
from gv$session g--, gv\$sql s
where g.sid=b.sid and g.inst_id = b.inst_id and username like '%'
) blockee,
'-- lock type '||decode(a.type, 'MR', 'Media_recovery',
'RT', 'Redo_thread',
'UN', 'User_name',
'TX', 'Transaction',
'TM', 'Dml',
'UL', 'PLSQL User_lock',
'DX', 'Distrted_Transaxion',
'CF', 'Control_file',
'IS', 'Instance_state',
'FS', 'File_set',
'IR', 'Instance_recovery',
'ST', 'Diskspace Transaction',
'IV', 'Libcache_invalidation',
'LS', 'LogStaartORswitch',
'RW', 'Row_wait',
'SQ', 'Sequence_no',
'TE', 'Extend_table',
'TT', 'Temp_table',
'Nothing-')
||' lock duration '||b.ctime
from gv$lock a, gv$lock b
where a.block > 0
and b.request > 0
and a.id1 = b.id1
and a.id2 = b.id2
and b.ctime > 0
order by a.sid;
ReplyDelete
Replies
AnonymousMonday, May 10, 2021
Incremental deletes:
set define on
set serveroutput on line 1000 trimout on trimspool on
declare
done BOOLEAN := false;
max_delete_count CONSTANT NUMBER := 1000;
deleted_count NUMBER := 0;
begin
-- Purge old messages from the SAFMessage table
done := false;
deleted_count := 0;
while(not done)
loop
delete from safmessage where createtime < current_timestamp - 1/6 and rownum <= max_delete_count;
done := not (SQL%ROWCOUNT = max_delete_count);
deleted_count := deleted_count + SQL%ROWCOUNT;
COMMIT;
end loop;
dbms_output.put_line('Deleted ' || deleted_count || ' SAFMessage messages.');
-- Purge old messages from the SAFMessageDeliveryHistory table
done := false;
deleted_count := 0;
while(not done)
loop
delete from safmessagedeliveryhistory where createtime < current_timestamp - 1/6 and rownum <= max_delete_count;
done := not (SQL%ROWCOUNT = max_delete_count);
deleted_count := deleted_count + SQL%ROWCOUNT;
COMMIT;
end loop;
dbms_output.put_line('Deleted ' || deleted_count || ' SAFMessageDeliveryHistory messages.');
end;
ReplyDelete
Replies
AnonymousMonday, May 10, 2021
Drop OLD Partitions

CREATE OR REPLACE function abc.drop_old_partitions(
p_table_name in varchar2,
p_days_to_keep in number
)
return number
as
PRAGMA AUTONOMOUS_TRANSACTION;
drop_cnt number := 0;
x_last_partition exception;
pragma exception_init(x_last_partition, -14758);
begin
for rec in (select table_name, partition_name
from user_tab_partitions
where table_name = p_table_name
and partition_name like 'SYS%'
and get_high_value_as_date(table_name, partition_name) < sysdate - p_days_to_keep)
loop
begin
execute immediate 'alter table ' || rec.table_name ||
' drop partition ' || rec.partition_name;
drop_cnt := drop_cnt + 1;
exception
when x_last_partition then
null;
end;
end loop;

return drop_cnt;
end;
/
ReplyDelete
Replies
AnonymousMonday, May 10, 2021
CREATE OR REPLACE function abc.get_high_value_as_date(
p_table_name in varchar2,
p_partition_name in varchar2
) return date as
v_high_value varchar2(1024);
v_date date;
begin
select high_value into v_high_value from user_tab_partitions
where table_name = upper(p_table_name)
and partition_name = upper(p_partition_name);
execute immediate 'select ' || v_high_value || ' from dual' into v_date;
return v_date;
end;
/
ReplyDelete
Replies
AnonymousFriday, June 04, 2021
DGMGRL> VALIDATE DATABASE OPROPRD;
DGMGRL> VALIDATE DATABASE OPROSBY;
DGMGRL> validate database verbose OPROPRD;
DGMGRL> validate database verbose OPROSBY;
VALIDATE DATABASE SPFILE:
DGMGRL> VALIDATE DATABASE OPROSBY SPFILE;
DGMGRL> VALIDATE DATABASE OPROPRD SPFILE; (This command cannot be used for the primary database.)
VALIDATE NETWORK CONFIGURATION:
DGMGRL> VALIDATE NETWORK CONFIGURATION FOR ALL;
DGMGRL> VALIDATE STATIC CONNECT IDENTIFIER FOR OPROPRD;
DGMGRL> VALIDATE STATIC CONNECT IDENTIFIER FOR OPROSBY;
VALIDATE STATIC CONNECT IDENTIFIER:
DGMGRL> VALIDATE STATIC CONNECT IDENTIFIER FOR OPROPRD;
DGMGRL> VALIDATE STATIC CONNECT IDENTIFIER FOR OPROSBY;
VALIDATE DATABASE DATAFILE to Compare Data Files: (Takes time plan to do it at least couple of days before the switchover, for encrypted TBS/DF's you can do it)
DGMGRL> VALIDATE DATABASE OPROPRD DATAFILE ALL OUTPUT= OPROPRD_DBFILE.out;
DGMGRL> VALIDATE DATABASE OPROSBY DATAFILE ALL OUTPUT= OPROSBY_DBFILE.out;

IMPORTANT NOTE: backup cronjobs to be comment/uncomment on both PRIMARY and STANDBY
Pre tasks:
Note down services running on Primary, after the switchover you can validate the services in new Primary (old STNDBY/DR).
DGMGRL will take care of the services on both primary and standby
srvctl status service -d OPROPRD
show parameter job_queue_processes --->>update the values after the switchover in new primary
show parameter aq_tm_processes --->>update the values after the switchover in new primary

DGMGRL> validate database verbose OPROPRD;
DGMGRL> validate database verbose OPROSBY;

SQL> SELECT SWITCHOVER_STATUS FROM V$DATABASE;
DGMGRL> connect sys/sys@db

DGMGRL> switchover to OPROSBY;
Validate After Switchover:

DGMGRL> VALIDATE DATABASE OPROSBY;
DGMGRL> VALIDATE DATABASE OPROPRD;

ISSUES AFTER THE SWITCHOVER:
Unable to convert the Standby database to READ ONLY WITH APPLY
SQL> set linesize 200
SQL> select distinct instance_name, status, database_role, open_mode, host_name from gv$instance, gv$database;

DGMGRL> edit database OPROSBY set state=apply-off;
DGMGRL> edit database OPROSBY set state=apply-on;
SQL> set linesize 200
SQL> select NAME, DB_UNIQUE_NAME, OPEN_MODE, DATABASE_ROLE,FLASHBACK_ON, DATAGUARD_BROKER from gv$database;

ReplyDelete
Replies
AnonymousMonday, August 09, 2021
export ORACLE_SID=ABCD4
date
time rman << EOF > ABCD_bk.log
connect target /
run
{
allocate channel d1 type disk;
allocate channel d2 type disk;
allocate channel d3 type disk;
allocate channel d4 type disk;

backup as compressed backupset filesperset 2 format '/dbbackup01/ABCD/dgbkp/db_%U' database;
sql 'alter system archive log current';
backup as compressed backupset filesperset 50 format '/dbbackup01/ABCD/dgbkp/arch_%U' archivelog all;

backup current controlfile for standby format '/dbbackup01/ABCD/dgbkp/standby_control_%U';

sql 'alter system archive log current';

backup as compressed backupset filesperset 50 format '/dbbackup01/ABCD/dgbkp/arch_%U' archivelog all;
}
exit
EOF
date
ReplyDelete
Replies
AnonymousFriday, August 20, 2021
sids=(`cut -f1 -d: /var/opt/oracle/oratab | egrep -v "^#|^\*" | LANG=C sort -f | tr [:lower:] [:upper:]`)
ReplyDelete
Replies
AnonymousMonday, August 30, 2021
MAP ETMSUSER.AUTOPROCESSINSTANCE, TARGET GGS_PHX.exceptions, EXCEPTIONSONLY,
INSERTALLRECORDS,
COLMAP ( rep_name = @GETENV ('GGENVIRONMENT','GROUPNAME')
,table_name = @GETENV ('GGHEADER', 'TABLENAME')
,errno = @GETENV ('LASTERR', 'DBERRNUM')
,dberrmsg = @GETENV ('LASTERR', 'DBERRMSG')
,optype = @GETENV ('LASTERR', 'OPTYPE')
,errtype = @GETENV ('LASTERR', 'ERRTYPE')
,logrba = @GETENV ('GGHEADER', 'LOGRBA')
,logposition = @GETENV ('GGHEADER', 'LOGPOSITION')
,committimestamp = @GETENV ('GGHEADER', 'COMMITTIMESTAMP')
,create_date = @DATENOW()
,uk1=INSTANCEID
,uk2=LASTREADDATE
,uk3=LASTMODIFICATIONDATE
);

ReplyDelete
Replies
AnonymousMonday, August 30, 2021
CREATE TABLE GGS_OMH.EXCEPTIONS
(
REP_NAME VARCHAR2(8 BYTE),
TABLE_NAME VARCHAR2(61 BYTE),
ERRNO NUMBER,
DBERRMSG VARCHAR2(4000 BYTE),
OPTYPE VARCHAR2(20 BYTE),
ERRTYPE VARCHAR2(20 BYTE),
LOGRBA NUMBER,
LOGPOSITION NUMBER,
COMMITTIMESTAMP TIMESTAMP(6),
UK1 VARCHAR2(40 BYTE),
UK2 VARCHAR2(40 BYTE),
UK3 VARCHAR2(40 BYTE),
UK4 VARCHAR2(40 BYTE),
UK5 VARCHAR2(40 BYTE),
UK6 VARCHAR2(40 BYTE),
MODIFIED_DATE TIMESTAMP(6) DEFAULT NULL,
CREATE_DATE TIMESTAMP(6) DEFAULT SYSTIMESTAMP,
STATUS VARCHAR2(20 BYTE) DEFAULT 'OPEN'
)
TABLESPACE GGS_OWNER_DATA;
ReplyDelete
Replies
AnonymousMonday, September 20, 2021
select 'alter system kill session '''||sid||','||serial#||',@'||inst_id||''' immediate;'
from
(select a.inst_id "INST_ID",a.sql_id,a.username "USERNAME", a.sid "SID", a.serial# "SERIAL#",a.machine "MACHINE" ,b.spid "SPID" ,sysdate "SYSDATE", to_char(sysdate-a.last_call_et/24/60/60,'hh24:mi:ss') "Started"
from gv$session a, gv$process b
where a.paddr = b.addr
--and a.status = 'ACTIVE'
and a.username like 'ETMSDASH'
and a.inst_id=b.inst_id
--and a.last_call_et > 300
and a.command in (2,3, 6,7) -- for SELECT INSERT UPDATE and DELETE only
order by INST_ID);
ReplyDelete
Replies
AnonymousMonday, November 22, 2021
ABLE RXOWNER.EPCS_AUDITABLE_EVENTS,GETBEFORECOLS (ON UPDATE ALL, ON DELETE KEY);
TABLE RXOWNER.FACILITY_COMPOUND_NDC,GETBEFORECOLS (ON UPDATE ALL, ON DELETE KEY);
TABLE RXOWNER.FACILITY_STATUS_MATRIX,GETBEFORECOLS (ON UPDATE ALL, ON DELETE KEY);
TABLE RXOWNER.FACILITY_THREAD,GETBEFORECOLS (ON UPDATE ALL, ON DELETE KEY);
TABLE RXOWNER.FAX_HEARTBEAT,GETBEFORECOLS (ON UPDATE ALL, ON DELETE KEY);
TABLE RXOWNER.FAX_INBOUND_CONTACT,GETBEFORECOLS (ON UPDATE ALLEXCLUDING(FAX_IMAGE), ON DELETE KEY);
TABLE RXOWNER.FILEBUY_PAT_XREF,GETBEFORECOLS (ON UPDATE ALL, ON DELETE KEY);
TABLE RXOWNER.FILEBUY_PRES_XREF,GETBEFORECOLS (ON UPDATE ALL, ON DELETE KEY);
TABLE RXOWNER.FILL_STATE,GETBEFORECOLS (ON UPDATE ALL, ON DELETE KEY);
TABLE RXOWNER.FORMULARY,GETBEFORECOLS (ON UPDATE ALL, ON DELETE KEY);
ReplyDelete
Replies
AnonymousMonday, November 22, 2021
MAP RXOWNER.RXC_EXCEPN_TRANSACTION, TARGET RXOWNER.RXC_EXCEPN_TRANSACTION,&
COMPARECOLS(ON UPDATE KEYINCLUDING(LAST_UPDATED_DATE),ON DELETE KEY),&
RESOLVECONFLICT (UPDATEROWEXISTS, (DEFAULT,USEMAX(LAST_UPDATED_DATE))),&
RESOLVECONFLICT (UPDATEROWMISSING, (DEFAULT,OVERWRITE)),&
RESOLVECONFLICT (DELETEROWEXISTS, (DEFAULT,DISCARD)),&
RESOLVECONFLICT (DELETEROWMISSING, (DEFAULT,DISCARD));
MAP RXOWNER.RXC_EXCEPN_TRANSACTION #exception_handler();

MAP RXADMIN.IMZ_QUESTION, TARGET RXADMIN.IMZ_QUESTION,&
COMPARECOLS(ON UPDATE KEYINCLUDING(LAST_UPDATED_DATE),ON DELETE KEY),&
RESOLVECONFLICT (UPDATEROWEXISTS, (DEFAULT,USEMAX(LAST_UPDATED_DATE))),&
RESOLVECONFLICT (UPDATEROWMISSING, (DEFAULT,OVERWRITE)),&
RESOLVECONFLICT (DELETEROWEXISTS, (DEFAULT,DISCARD)),&
RESOLVECONFLICT (DELETEROWMISSING, (DEFAULT,DISCARD));
MAP RXADMIN.IMZ_QUESTION #exception_handler();

MAP RXADMIN.IMZ_QUESTION_ASSIGNMENT, TARGET RXADMIN.IMZ_QUESTION_ASSIGNMENT,&
COMPARECOLS(ON UPDATE KEYINCLUDING(LAST_UPDATED_DATE),ON DELETE KEY),&
RESOLVECONFLICT (UPDATEROWEXISTS, (DEFAULT,USEMAX(LAST_UPDATED_DATE))),&
RESOLVECONFLICT (UPDATEROWMISSING, (DEFAULT,OVERWRITE)),&
RESOLVECONFLICT (DELETEROWEXISTS, (DEFAULT,DISCARD)),&
RESOLVECONFLICT (DELETEROWMISSING, (DEFAULT,DISCARD));
MAP RXADMIN.IMZ_QUESTION_ASSIGNMENT #exception_handler();

ReplyDelete
Replies
AnonymousMonday, November 22, 2021
MACRO #exception_handler
BEGIN
,TARGET GGADMIN.exceptions
,COLMAP ( rep_name = @GETENV ('GGENVIRONMENT', 'GROUPNAME')
,table_name = @GETENV ('GGHEADER', 'TABLENAME')
,errno = @GETENV ('LASTERR', 'DBERRNUM')
,dberrmsg = @GETENV ('LASTERR', 'DBERRMSG')
,optype = @GETENV ('LASTERR', 'OPTYPE')
,errtype = @GETENV ('LASTERR', 'ERRTYPE')
,logrba = @GETENV ('GGHEADER', 'LOGRBA')
,logposition = @GETENV ('GGHEADER', 'LOGPOSITION')
,committimestamp = @GETENV ('GGHEADER', 'COMMITTIMESTAMP'))
,INSERTALLRECORDS
,EXCEPTIONSONLY;
END;
---
--- End of the macro
---
---
--- Start of Heartbeat Tables
---
MAP GGADMIN.GG_HEARTBEAT, TARGET GGADMIN.GG_HEARTBEAT,
INSERTMISSINGUPDATES,
COLMAP (USEDEFAULTS,
EXTRACT_NAME = @TOKEN ('EXTRACT_NAME'),
EXTRACT_TIME = @TOKEN ('EXTRACT_TIME'),
REPLICAT_NAME = @GETENV ('GGENVIRONMENT', 'GROUPNAME'),
REPLICAT_TIME = @DATE ('YYYY-MM-DD HH:MI:SS','JTS',@GETENV ('JULIANTIMESTAMP'))
);

MAP GGADMIN.GG_HEARTBEAT, TARGET GGADMIN.GG_HEARTBEAT_HIST,
INSERTALLRECORDS,
COLMAP (USEDEFAULTS,
EXTRACT_NAME = @TOKEN ('EXTRACT_NAME'),
EXTRACT_TIME = @TOKEN ('EXTRACT_TIME'),
REPLICAT_NAME = @GETENV ('GGENVIRONMENT', 'GROUPNAME'),
REPLICAT_TIME = @DATE ('YYYY-MM-DD HH:MI:SS','JTS',@GETENV ('JULIANTIMESTAMP'))
);
---
--- End of Heartbeat Tables
---
ReplyDelete
Replies
AnonymousMonday, November 29, 2021
#!/bin/ksh
# THIS SCRIPT WOULD COLLECT GG LAG ON OLTP INSTANCES
# use config file for username and password
# Created by Swamy Bondada
. /users/oracle/local/prod/config/usr_crdntls_dchg.cfg

if [[ $# -lt 4 ]]
then
echo "$0 {BEGIN_TIME<>} {END_TIME<>} {TEST_NAME}"
exit 1
fi

GGLOGDIR="/users/oracle/local/prod/sh/GG_LAG/logs/gg_lag"
DBNM="non"
BEGIN_TIME=$1
END_TIME=$2
TEST_NAME=$3
DB1=$4
DB2=$5
DB3=$6

cd ${GGLOGDIR}
mkdir ${TEST_NAME}

function gather_gg_lag_oltp
{
echo "HOSTNAME,RUNDATE,PROCESS_NAME,EPR_STATE,EPR_NAME,EPR_LAG_SECS,EPR_LAG_MINS,EPR_CHKPT_SECS,EPR_CHKPT_MINS" > ${GGLOGDIR}/oltp_gg_lag_$BEGIN_TIME.csv
sqlplus -s ${MNTRG_USR_NAME}/${MNTRG_USR_PASSWD}@non5<> ${GGLOGDIR}/oltp_gg_lag_$BEGIN_TIME.csv
set heading off
set echo off
set feedback off
set pages 0
set lines 1500
set trims on
set termout off
select HOSTNAME||','||to_char(RUNDATE,'dd-mon-yyyy hh24:mi:ss')||','||PROCESS_NAME||','||EPR_STATE||','||EPR_NAME||','||EPR_LAG_SECS||','||EPR_LAG_MINS||','||EPR_CHKPT_SECS||','||EPR_CHKPT_MINS from DBADMIN.GG_EPR_LAG_STATISTICS where RUNDATE between to_date('$BEGIN_TIME','yyyymmdd-hh24mi') and to_date('$END_TIME', 'yyyymmdd-hh24mi');
exit
!EOF
ReplyDelete
Replies
AnonymousMonday, November 29, 2021
}

function gather_gg_lag_ods
{
echo "HOSTNAME,RUNDATE,PROCESS_NAME,EPR_STATE,EPR_NAME,EPR_LAG_SECS,EPR_LAG_MINS,EPR_CHKPT_SECS,EPR_CHKPT_MINS" > ${GGLOGDIR}/ods_gg_lag_$BEGIN_TIME.csv
sqlplus -s ${MNTRG_USR_NAME}/${MNTRG_USR_PASSWD}@nwn5<> ${GGLOGDIR}/ods_gg_lag_$BEGIN_TIME.csv
set heading off
set echo off
set feedback off
set pages 0
set lines 1500
set trims on
set termout off
select HOSTNAME||','||to_char(RUNDATE,'dd-mon-yyyy hh24:mi:ss')||','||PROCESS_NAME||','||EPR_STATE||','||EPR_NAME||','||EPR_LAG_SECS||','||EPR_LAG_MINS||','||EPR_CHKPT_SECS||','||EPR_CHKPT_MINS
from DBADMIN.GG_EPR_LAG_STATISTICS where RUNDATE between to_date('$BEGIN_TIME','yyyymmdd-hh24mi') and to_date('$END_TIME', 'yyyymmdd-hh24mi');
exit
!EOF
}

function gather_gg_lag_aas
{
echo "HOSTNAME,RUNDATE,PROCESS_NAME,EPR_STATE,EPR_NAME,EPR_LAG_SECS,EPR_LAG_MINS,EPR_CHKPT_SECS,EPR_CHKPT_MINS" > ${GGLOGDIR}/aas_gg_lag_$BEGIN_TIME.csv
sqlplus -s ${MNTRG_USR_NAME}/${MNTRG_USR_PASSWD}@naason5<> ${GGLOGDIR}/aas_gg_lag_$BEGIN_TIME.csv
set heading off
set echo off
set feedback off
set pages 0
set lines 1500
set trims on
set termout off
select HOSTNAME||','||to_char(RUNDATE,'dd-mon-yyyy hh24:mi:ss')||','||PROCESS_NAME||','||EPR_STATE||','||EPR_NAME||','||EPR_LAG_SECS||','||EPR_LAG_MINS||','||EPR_CHKPT_SECS||','||EPR_CHKPT_MINS
from DBADMIN.GG_EPR_LAG_STATISTICS where RUNDATE between to_date('$BEGIN_TIME','yyyymmdd-hh24mi') and to_date('$END_TIME', 'yyyymmdd-hh24mi');
exit
!EOF
}

if [[ $DB1 = "OLTP" ]] || [[ $DB1 = "oltp" ]]; then
gather_gg_lag_oltp
elif [[ $DB1 = "ODS" ]] || [[ $DB1 = "ods" ]]; then
gather_gg_lag_ods
elif [[ $DB1 = "AAS" ]] || [[ $DB1 = "aas" ]]; then
gather_gg_lag_aas
else
echo
fi

ReplyDelete
Replies
AnonymousMonday, November 29, 2021
if [[ $DB2 = "OLTP" ]] || [[ $DB2 = "oltp" ]]; then
gather_gg_lag_oltp
elif [[ $DB2 = "ODS" ]] || [[ $DB2 = "ods" ]]; then
gather_gg_lag_ods
elif [[ $DB2 = "AAS" ]] || [[ $DB2 = "aas" ]]; then
gather_gg_lag_aas
else
echo
fi

if [[ $DB3 = "OLTP" ]] || [[ $DB3 = "oltp" ]]; then
gather_gg_lag_oltp
elif [[ $DB3 = "ODS" ]] || [[ $DB3 = "ods" ]]; then
gather_gg_lag_ods
elif [[ $DB3 = "AAS" ]] || [[ $DB3 = "aas" ]]; then
gather_gg_lag_aas
else
echo
fi

cd ${GGLOGDIR}

mv *.csv ${GGLOGDIR}/$TEST_NAME/.

zip -rp ${TEST_NAME}_GG_LAG.zip $TEST_NAME

rm -rf ${TEST_NAME}

echo "GG LAG FILES HAVE BEEN ZIPPED AT ${GGLOGDIR} as ${TEST_NAME}.zip"
exit 0

ReplyDelete
Replies
AdithyaThursday, April 07, 2022
https://us05web.zoom.us/j/83030055781?pwd=aWQwQyt5dVlhSGxLUUZZdkRZeTVDdz09
ReplyDelete
Replies
AdithyaFriday, April 08, 2022
This comment has been removed by the author.
ReplyDelete
Replies
AnonymousMonday, April 11, 2022
https://us06web.zoom.us/j/5552739509?pwd=b0pma1BhVjFtSDVqbnN4aFQrZk5Idz09
ReplyDelete
Replies
AnonymousSaturday, May 28, 2022
dbca -silent -createDatabase -templateName /u02/app/oracle/product/11.2.0/dbhome_3/assistants/dbca/templates/General_Purpose.dbc -gdbName ETMSAIPX -sid ETMSAIPX -sysPassword "M8rton_Jr" -systemPassword "M8rton_Jr" -dbsnmpPassword "M8rton_Jr" -emConfiguration NONE -storageType ASM -diskGroupName "DATAC1" -recoveryGroupName "RECOC1" -datafileJarLocation $ORACLE_HOME/assistants/dbca/templates -nodeinfo "uiiociphxp01-iufnq1,uiiociphxp01-iufnq2,uiiociphxp01-iufnq3,uiiociphxp01-iufnq4" -characterset AL32UTF8 -nationalCharacterSet UTF8 -obfuscatedPasswords false -sampleSchema false -asmSysPassword "V3R1zon#-" -totalMemory 6000
ReplyDelete
Replies
AnonymousTuesday, July 12, 2022
col file_name for a66
set pages 1000
set lines 123
set colsep |
set linesize 100 pages 100 trimspool on numwidth 14
col name format a65
col owner format a15
col "Used (GB)" format a15
col "Free (GB)" format a15
col "(Used) %" format a15
col "Size (M)" format a15
SELECT d.status "Status", d.tablespace_name "Name",
TO_CHAR(NVL(a.bytes / 1024 / 1024 /1024, 0),'99,999,990.90') "Size (GB)",
TO_CHAR(NVL(a.bytes - NVL(f.bytes, 0), 0)/1024/1024 /1024,'99999999.99') "Used (GB)",
TO_CHAR(NVL(f.bytes / 1024 / 1024 /1024, 0),'99,999,990.90') "Free (GB)",
TO_CHAR(NVL((a.bytes - NVL(f.bytes, 0)) / a.bytes * 100, 0), '990.00') "(Used) %"
FROM sys.dba_tablespaces d,
(select tablespace_name, sum(bytes) bytes from dba_data_files group by tablespace_name) a,
(select tablespace_name, sum(bytes) bytes from dba_free_space group by tablespace_name) f WHERE
d.tablespace_name = a.tablespace_name(+) AND d.tablespace_name = f.tablespace_name(+) AND NOT
(d.extent_management like 'LOCAL' AND d.contents like 'TEMPORARY')
UNION ALL
SELECT d.status
"Status", d.tablespace_name "Name",
TO_CHAR(NVL(a.bytes / 1024 / 1024 /1024, 0),'99,999,990.90') "Size (GB)",
TO_CHAR(NVL(t.bytes,0)/1024/1024 /1024,'99999999.99') "Used (GB)",
TO_CHAR(NVL((a.bytes -NVL(t.bytes, 0)) / 1024 / 1024 /1024, 0),'99,999,990.90') "Free (GB)",
TO_CHAR(NVL(t.bytes / a.bytes * 100, 0), '990.00') "(Used) %"
FROM sys.dba_tablespaces d,
(select tablespace_name, sum(bytes) bytes from dba_temp_files group by tablespace_name) a,
(select tablespace_name, sum(bytes_cached) bytes from v$temp_extent_pool group by tablespace_name) t
WHERE d.tablespace_name = a.tablespace_name(+) AND d.tablespace_name = t.tablespace_name(+) AND
d.extent_management like 'LOCAL' AND d.contents like 'TEMPORARY' order by 6 desc;

col file_name for a65
ReplyDelete
Replies
AnonymousTuesday, July 19, 2022
select DB_NAME,APP_DBA
FROM idba.APPLICATIONS
where db_name in
(select DB_NAME
from IDBA.PATCH_COMPLIANCE
where db_name in
(select db_name
from idba.oracle_databases
where bio_id=5) and patch_name='Critical_Patch_Update_2018_July') order by APP_DBA;

select DB_NAME,APP_DBA
FROM idba.APPLICATIONS
where db_name in
(select DB_NAME
from IDBA.PATCH_COMPLIANCE
where db_name in
(select db_name
from idba.oracle_databases
where bio_id=5) and patch_name='Critical_Patch_Update_2018_July') order by APP_DBA
ReplyDelete
Replies
AnonymousTuesday, July 19, 2022
exec dbms_stats.gather_table_stats('ELODBP05','LN_CTRAN_7007','SYS_P2259272',degree=>32);
exec dbms_stats.gather_table_stats('ELODBP05','LN_CTRAN_7007','SYS_P2259273',degree=>32);
exec dbms_stats.gather_table_stats('ELODBP05','LN_CTRAN_7007','SYS_P2263535',degree=>32);
exec dbms_stats.gather_table_stats('ELODBP05','LN_CTRAN_7007','SYS_P2269023',degree=>32);
exec dbms_stats.gather_table_stats('ELODBP05','LN_CTRAN_7007','SYS_P2266449',degree=>32);
exec dbms_stats.gather_table_stats('ELODBP05','LN_CTRAN_7007','SYS_P2266604',degree=>32);
exec dbms_stats.gather_table_stats('ELODBP05','LN_CTRAN_7007','SYS_P2268957',degree=>32);
exec dbms_stats.gather_table_stats('ELODBP05','LN_CTRAN_7007','SYS_P2263617',degree=>32);

ReplyDelete
Replies
AnonymousThursday, July 28, 2022
set echo on
set pages 500
set lin 5000
set timing on
set time on
spool 5hg8asavfutpn.log
alter session set current_schema=etmsuser;
alter session set statistics_level='ALL';

select processins0_.InstanceId as col_0_0_ from etmsuser.AUTOPROCESSINSTANCE processins0_ inner join etmsuser.AUTOEVENTTYPES eventtypes1_ on processins0_.InstanceId=eventtypes1_.InstanceId where eventtypes1_.element='&1';

select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST ADVANCED OUTLINE'));

spool off

ReplyDelete
Replies
AnonymousMonday, August 29, 2022
alias sid='. /opt/oracle/.local_profile'
[uiitwpd30-OPROTWP2] more .local_profile
##################################################################################################
# This is the (almost) MAINTENANCE-FREE version of the SID script.
# DBAs do not need to edit this if they added/deleted DBs,
# unless DB-specific scripts need to be
# ran or non-generic environment setting needs to be setup.
##################################################################################################

echo
echo "PLEASE CHOOSE FROM THE MENU"
PS3="Select Oracle Environment:"
host=`hostname -s`
nodenum=`olsnodes -n | grep $host | cut -f2 `
sids=(`cut -f1 -d: /etc/oratab | egrep -v "^#|^\*" | LANG=C sort -f | tr [:lower:] [:upper:]`)

## Menu
wid=`echo $COLUMNS`
export COLUMNS=20
select OSID in ${sids[*]}
do

if [ "$OSID" = "" ] ; then
continue
else

#- leading minus throws grep off
if [[ "$OSID" = "-MGMTDB" ]] ; then
SIDLen=`expr length $OSID - 1`
OSID=`expr substr $OSID 2 $SIDLen`
fi

#- find OH
if [ `expr substr $OSID 1 1` != "-" ] ; then
export ORACLE_HOME=`grep -wi "$OSID" /etc/oratab | cut -f2 -d:`
export ORACLE_SID=` grep -wi "$OSID" /etc/oratab | cut -f1 -d:`
else
export ORACLE_HOME=`grep -wi \\\"$OSID" /etc/oratab | cut -f2 -d:`
export ORACLE_SID=` grep -wi \\\"$OSID" /etc/oratab | cut -f1 -d:`
fi
SIDLen=`expr length $ORACLE_SID - 1`
SIDoratab=`expr substr $ORACLE_SID 1 $SIDLen`
if [ "$SIDoratab" = "+ASM" ] || [ "$SIDoratab" = "-MGMTD" ] ; then
export ORACLE_SID=${ORACLE_SID}
else
export ORACLE_SID=${ORACLE_SID}${nodenum}
fi
export BASE_PATH=${BASE_PATH:-$PATH}
export PATH=$BASE_PATH:$ORACLE_HOME/bin:$ORACLE_HOME/OPatch:$ORA_CRS_HOME/bin:$GRID_HOME/bin
export LD_LIBRARY_PATH=$ORACLE_HOME/lib
alias orahome="cd $ORACLE_HOME ; pwd"
break

fi

done
export COLUMNS=$wid

###############################################################
# Instance specific stuff below
# When referencing, omit instance number - use DBName
###############################################################
case $OSID in
OPROTWP)
export DB_NAME=$OSID
export ORACLE_UNQNAME=`$ORACLE_HOME/bin/srvctl config database |grep -w ${ORACLE_SID%?}`
export TNS_ADMIN=$ORACLE_HOME/network/admin/${ORACLE_UNQNAME}
;;

NTRAPTW)
export DB_NAME=$OSID
export ORACLE_UNQNAME=`$ORACLE_HOME/bin/srvctl config database |grep -w ${ORACLE_SID%?}`
export TNS_ADMIN=$ORACLE_HOME/network/admin/${ORACLE_UNQNAME}
;;

NTWARCH)
export DB_NAME=$OSID
export ORACLE_UNQNAME=`$ORACLE_HOME/bin/srvctl config database |grep -w ${ORACLE_SID%?}`
export TNS_ADMIN=$ORACLE_HOME/network/admin/${ORACLE_UNQNAME}
;;

*) ## Generic stuff here, SHOULD BE LAST OPTION
stty erase ^H
;;
esac

###############################################################
# Closer
###############################################################
export PS1="[`hostname | cut -f1 -d.`-$ORACLE_SID] "
## Set admin alias
expr match $ORACLE_HOME '$.*1[12]$' > /dev/null
if [ $? -eq 0 ] ; then
alias admin="cd /opt/oracle/product/diag/*/*/$ORACLE_SID ; pwd"
else
alias admin='cd /opt/oracle/product/admin/ ; pwd'
fi

if tty -s ; then
echo
echo " ORACLE_HOME is $ORACLE_HOME"
echo " ORACLE_SID is $ORACLE_SID"; echo
fi

chkSpace
ReplyDelete
Replies
AdithyaThursday, September 15, 2022

select 'grant '||privilege||' on '||owner||'.'||table_name||' to '||grantee||';'
from dba_tab_privs where owner in ('ELODBA06') order by privilege;

select 'create public synonym '||synonym_name ||' for '||table_owner||'.'||table_name||';'
from dba_synonyms where table_owner in('ELODBA06') and owner='PUBLIC';

select 'create synonym '||owner||'.'||synonym_name ||' for '||table_owner||'.'||table_name||';'
from dba_synonyms where table_owner in('ELODBA06');

impdp \
DIRECTORY=CHG0333515 \
METRICS=Y \
PARALLEL=32 \
JOB_NAME=ES0SDDBS1839_ELODBA06 \
dumpfile=expdp_CHG0333515_POELO02_ELODBP06_BSE_SCHEMA_04DEC2018_%U.dmp \
logfile=SHARED_DATAPUMP_DIR:impdp_ES0SDDBS-1839_AOELO05_ELODBA06_17DEC2018.log \
REMAP_SCHEMA=ELODBP01:ELODBA01,ELODBP02:ELODBA02,ELODBP06:ELODBA06,ELODBP05:ELODBA05,ELODBP04:ELODBA04 \
TABLE_EXISTS_ACTION=REPLACE

ReplyDelete
Replies
AdithyaSunday, September 18, 2022
impdp \
DIRECTORY=CHG0333515 \
METRICS=Y \
PARALLEL=16 \
JOB_NAME=ES0SDDBS1839_ERRORTBLS \
dumpfile=expdp_CHG0333515_POELO02_ELODBP06_BSE_SCHEMA_04DEC2018_%U.dmp \
logfile=SHARED_DATAPUMP_DIR:impdp_ES0SDDBS1839_AOELO05_ELODBA06_ERROR_TBLS_17DEC2018.log \
REMAP_SCHEMA=ELODBP01:ELODBA01,ELODBP02:ELODBA02,ELODBP06:ELODBA06,ELODBP05:ELODBA05,ELODBP04:ELODBA04 \
TABLE_EXISTS_ACTION=TRUNCATE \
TABLES=ELODBP06.MD_BCH, \
ELODBP06.MD_TRAN_BUS_EVNT, \
ELODBP06.MD_TRAN_PYLD_ARC, \
ELODBP06.MD_TRAN_PYLD, \
ELODBP06.MD_BCH_SKEY_SRC_SYS_LOG, \
ELODBP06.MD_BCH_SRC_SYS_STAT_LOG, \
ELODBP06.OT_DATA_CFWRK_LOG, \
ELODBP06.OT_PRCS_DTAST_RQST_QUE, \
ELODBP06.AG_CE, \
ELODBP06.AG_CE_EXP, \
ELODBP06.AG_CE_GRP, \
ELODBP06.AG_CE_GRP_LN, \
ELODBP06.AG_CE_LN_CLM_CLCTN, \
ELODBP06.AG_CE_PRCDS, \
ELODBP06.AG_CE_GRP_LN_ASSC, \
ELODBP06.DC_DATA_CRTN, \
ELODBP06.DC_DATA_CRTN_TBL, \
ELODBP06.MD_BCH_ARC, \
ELODBP06.MD_TRAN_BUS_EVNT_ARC, \
ELODBP06.DC_DATA_CRTN_TBL_DATA, \
ELODBP06.DC_DATA_CRTN_EVNT, \
ELODBP06.DC_DATA_CRTN_ERR_LOG
ReplyDelete
Replies
AdithyaSunday, September 18, 2022

impdp \
DIRECTORY=CHG0333515 \
METRICS=Y \
PARALLEL=32 \
JOB_NAME=ES0SDDBS1839_ELODBA06 \
dumpfile=expdp_CHG0333515_POELO02_ELODBP06_BSE_SCHEMA_04DEC2018_%U.dmp \
logfile=SHARED_DATAPUMP_DIR:impdp_ES0SDDBS-1839_AOELO05_ELODBA06_17DEC2018.log \
REMAP_SCHEMA=ELODBP01:ELODBA01,ELODBP02:ELODBA02,ELODBP06:ELODBA06,ELODBP05:ELODBA05,ELODBP04:ELODBA04 \
TABLE_EXISTS_ACTION=REPLACE

impdp \
DIRECTORY=CHG0333515 \
METRICS=Y \
PARALLEL=5 \
dumpfile=expdp_CHG0333515_POELO02_ELODBP06_BSE_SCHEMA_04DEC2018_%U.dmp \
logfile=SHARED_DATAPUMP_DIR:ES0SDDBS-1839_AOELO05_ELODBA06.log \
REMAP_SCHEMA=ELODBP01:ELODBA01,ELODBP02:ELODBA02,ELODBP06:ELODBA06,ELODBP05:ELODBA05,ELODBP04:ELODBA04 \
content=metadata_only \
TABLES=ELODBP06.MD_BCH, \
ELODBP06.MD_TRAN_BUS_EVNT, \
ELODBP06.MD_TRAN_PYLD_ARC, \
ELODBP06.MD_TRAN_PYLD, \
ELODBP06.MD_BCH_SKEY_SRC_SYS_LOG, \
ELODBP06.MD_BCH_SRC_SYS_STAT_LOG, \
ELODBP06.OT_DATA_CFWRK_LOG, \
ELODBP06.OT_PRCS_DTAST_RQST_QUE, \
ELODBP06.AG_CE, \
ELODBP06.AG_CE_EXP, \
ELODBP06.AG_CE_GRP, \
ELODBP06.AG_CE_GRP_LN, \
ELODBP06.AG_CE_LN_CLM_CLCTN, \
ELODBP06.AG_CE_PRCDS, \
ELODBP06.AG_CE_GRP_LN_ASSC, \
ELODBP06.DC_DATA_CRTN, \
ELODBP06.DC_DATA_CRTN_TBL, \
ELODBP06.MD_BCH_ARC, \
ELODBP06.MD_TRAN_BUS_EVNT_ARC, \
ELODBP06.DC_DATA_CRTN_TBL_DATA, \
ELODBP06.DC_DATA_CRTN_EVNT, \
ELODBP06.DC_DATA_CRTN_ERR_LOG, \
SQLFILE=ES0SDDBS_1839_AOELO05_ERROR_TBLS.sql
ReplyDelete
Replies
AdithyaThursday, October 06, 2022
DECLARE (Syntax for export)
hdnl NUMBER;
BEGIN
hdnl := DBMS_DATAPUMP.OPEN( operation => 'EXPORT', job_mode => 'SCHEMA', job_name=> NULL,version=>'12.1');
DBMS_DATAPUMP.ADD_FILE( handle => hdnl, filename => 'tm_refresh_tmtbls_06082022.dmp', directory => 'DATA_PUMP_DIR', filetype => dbms_datapump.ku$_file_type_dump_file);
DBMS_DATAPUMP.ADD_FILE( handle => hdnl, filename => 'tm_refresh_tmtbls_06082022.log', directory => 'DATA_PUMP_DIR', filetype => dbms_datapump.ku$_file_type_log_file);
dbms_datapump.metadata_filter( handle => hdnl,name => 'SCHEMA_EXPR',value => '= ''TMTBLS''');
dbms_datapump.metadata_filter(handle => hdnl,name => 'NAME_EXPR',value => 'IN (''TERRITORY'',''TERRITORY_VERSION'',''WORKSPACE'',''TERRITORY_RULE'',''RULE'',''RULE_GEO'',''RULE_VERTICAL'',''RULE_DUNS'',''HOME_SEGMENT'',''ANALYTICS_QUERY'',''ANALYTICS_FIELD'',''ANALYTICS_FIELD_VALUE'',''REMARKS'',''TERRITORY_CONTACT'')', object_type => 'TABLE');
DBMS_DATAPUMP.START_JOB(hdnl);
END;
/
Above is the syntax , Make sure to change the dump file name , logfile name and the tables list , schema name accordingly.

7. You can check the log using – select * from table(rdsadmin.rds_file_util.read_text_file(p_directory => 'DATA_PUMP_DIR',p_filename => 'tm_refresh_extdata_05192022.log'));

8. if there are multiple schema exports you need to run export multiple times with above syntax.

9. Shipping of file

BEGIN
DBMS_FILE_TRANSFER.PUT_FILE(
source_directory_object => 'DATA_PUMP_DIR',
source_file_name => 'tm_refresh_tmtbls_06082022.dmp',
destination_directory_object => 'DATA_PUMP_DIR',
destination_file_name => 'tm_refresh_tmtbls_06082022.dmp',
destination_database => 'ple_uat_rds'
);
END;
/

10. now in PLE run below for import. Please change schema name, tables list , dumpfile name accordingly. You can also run above sql to check the log.

DECLARE
hdnl NUMBER;
BEGIN
hdnl := DBMS_DATAPUMP.OPEN( operation => 'IMPORT', job_mode => 'SCHEMA', job_name=> NULL,version=>'12.1');
DBMS_DATAPUMP.ADD_FILE( handle => hdnl, filename => 'tm_refresh_tmtbls_06082022.dmp', directory => 'DATA_PUMP_DIR', filetype => dbms_datapump.ku$_file_type_dump_file);
DBMS_DATAPUMP.ADD_FILE( handle => hdnl, filename => 'tm_refresh_tmtbls_06082022.log', directory => 'DATA_PUMP_DIR', filetype => dbms_datapump.ku$_file_type_log_file);
dbms_datapump.metadata_filter( handle => hdnl,name => 'SCHEMA_EXPR',value => '= ''TMTBLS''');
dbms_datapump.metadata_filter(handle => hdnl,name => 'NAME_EXPR',value => 'IN (''TERRITORY'',''TERRITORY_VERSION'',''WORKSPACE'',''TERRITORY_RULE'',''RULE'',''RULE_GEO'',''RULE_VERTICAL'',''RULE_DUNS'',''HOME_SEGMENT'',''ANALYTICS_QUERY'',''ANALYTICS_FIELD'',''ANALYTICS_FIELD_VALUE'',''REMARKS'',''TERRITORY_CONTACT'')', object_type => 'TABLE');
DBMS_DATAPUMP.SET_PARAMETER(hdnl,'TABLE_EXISTS_ACTION','REPLACE');
DBMS_DATAPUMP.START_JOB(hdnl);
END;
/

ReplyDelete
Replies
AdithyaTuesday, October 11, 2022
dbca -silent -createDatabase -templateName /u02/app/oracle/product/11.2.0/dbhome_3/assistants/dbca/templates/General_Purpose.dbc -gdbName ETMSAIPX -sid ETMSAIPX -sysPassword "M8rton_Jr" -systemPassword "M8rton_Jr" -dbsnmpPassword "M8rton_Jr" -emConfiguration NONE -storageType ASM -diskGroupName "DATAC1" -recoveryGroupName "RECOC1" -datafileJarLocation $ORACLE_HOME/assistants/dbca/templates -nodeinfo "uiiociphxp01-iufnq1,uiiociphxp01-iufnq2,uiiociphxp01-iufnq3,uiiociphxp01-iufnq4" -characterset AL32UTF8 -nationalCharacterSet UTF8 -obfuscatedPasswords false -sampleSchema false -asmSysPassword "V3R1zon#-" -totalMemory 6000

ReplyDelete
Replies
AdithyaSunday, November 13, 2022
export ORACLE_BASE=/oracle01/app/oracle
export ORACLE_HOME=$ORACLE_BASE/product/11.2.0.4
export PATH=$ORACLE_HOME/bin:$OBK_HOME/bin:/opt/bin:/usr/bin:/etc:/usr/sbin:/usr/ucb:$HOME/bin:/usr/bin/X11:/sbin:/usr/css/bin:/usr/lbin:.
export ORACLE_SID=smus
DATE=`date +%Y%m%d%H%M`
rman target sys/pmussys01@pmus AUXILIARY=sys/pmussys01 catalog prman/prcatprod01@prcat 1>/oracle01/scripts/PMUS_TO_SMUS/PMUS_to_SMUS_$DATE.log 2>>/oracle01/scripts/PMUS_TO_SMUS/PMUS_to_SMUS_$DATE.log <<!
ALLOCATE CHANNEL FOR MAINTENANCE DEVICE TYPE DISK;
RUN {
ALLOCATE AUXILIARY CHANNEL A1 TYPE DISK;
ALLOCATE AUXILIARY CHANNEL A2 TYPE DISK;
ALLOCATE AUXILIARY CHANNEL A3 TYPE DISK;
duplicate target database to smus until time "to_date('2017-08-29:06:00:00', 'yyyy-mm-dd:hh24:mi:ss')" NOFILENAMECHECK;
}
exit
!

tail -20 /oracle01/scripts/PMUS_TO_SMUS/PMUS_to_SMUS_$DATE.log | mail -s "PMUS to SMUS RMAN Refresh Completed" Adithya.Si
ReplyDelete
Replies
AnonymousThursday, January 19, 2023
select
sql_id,
(case command_type
when 1 then 'CREATE TABLE'
when 2 then 'INSERT'
when 3 then 'SELECT'
when 6 then 'UPDATE'
when 7 then 'DELETE'
when 9 then 'Create Index'
when 11 then 'ALTER INDEX'
when 25 then 'ALTER PROCEDURE'
when 26 then 'LOCK Table'
when 42 then 'ALTER_SESSION'
when 44 then 'COMMIT'
when 45 then 'Rollback'
when 46 then 'Savepoint'
when 47 then 'PL/SQL BLOCK'
when 48 then 'Set transaction'
when 50 then 'Explain'
when 62 then 'Analyze table'
when 90 then 'Set Constraints'
when 170 then 'Call'
when 189 then 'Merge'
end ) Command_Type,
executions,
ELAPSED_TIME_delta,
ELAP_SEC,
pio_per_exec,
lio_per_exec
from (
select
q.sql_id,H.command_type
, sum(q.EXECUTIONS_DELTA) executions
, round(sum(ELAPSED_TIME_delta)/1000000,2) ELAPSED_TIME_delta
, round((sum(ELAPSED_TIME_delta)/greatest(sum(executions_delta),1)/1000000),2) ELAP_SEC
, round(sum(DISK_READS_delta)/greatest(sum(executions_delta),1),2) pio_per_exec
, round(sum(BUFFER_GETS_delta)/greatest(sum(executions_delta),1),2) lio_per_exec
from dba_hist_sqlstat q, dba_hist_snapshot s, dba_hist_sqltext H
where s.snap_id = q.snap_id
and s.begin_interval_time>=sysdate-1
--and q.Module='JDBC Thin Client'
and q.parsing_schema_name like '$USER%'
and s.dbid = q.dbid
and s.instance_number = q.instance_number
and q.dbid=h.dbid
and q.sql_id=H.sql_id
group by q.sql_id,h.command_type
)
) GROUP BY Command_Type ORDER BY 1
);
ReplyDelete
Replies
AdithyaWednesday, June 14, 2023

SQL> column object_name format a30
SQL> SELECT do.owner,do.object_name ,
s.row_wait_obj# ,
s.row_wait_file# ,
s.row_wait_block#,
s.row_wait_row# ,
dbms_rowid.rowid_create ( 1, data_object_id, rfile#, ROW_WAIT_BLOCK#, ROW_WAIT_ROW# )
FROM gv$session s,
dba_objects do,
v$datafile v
WHERE s.blocking_session = 582
AND s.row_wait_obj# = do.object_id
AND s.row_wait_file# = v.file#
;

SELECT B.Owner, B.Object_Name, A.Oracle_Username, A.OS_User_Name
FROM V$Locked_Object A, All_Objects B
WHERE A.Object_ID = B.Object_ID;
ReplyDelete
Replies
AdithyaSaturday, October 07, 2023
SELECT A.TABLE_OWNER,A.TABLE_NAME,A.TABLESPACE_NAME,A.PARTITION_NAME,A.PARTITION_POSITION,A.HIGH_VALUE,A.NUM_ROWS,B.BYTES/1024/1024 "SIZE_MB" FROM DBA_TAB_PARTITIONS A, DBA_SEGMENTS B
WHERE A.TABLE_NAME LIKE 'AUD%' and A.TABLE_NAME=B.SEGMENT_NAME and A.PARTITION_NAME=B.PARTITION_NAME;
ReplyDelete
Replies
AnonymousMonday, November 06, 2023
alias coraenv='cat /var/opt/oracle/oratab|grep -v '\''^#'\''|grep -v '\''^$'\''|awk -F: '\''{print $1}'\'';source /usr/bin/oraenv'
ReplyDelete
Replies
AdithyaSunday, March 03, 2024
SELECT A.TABLE_OWNER,A.TABLE_NAME,A.TABLESPACE_NAME,A.PARTITION_NAME,A.PARTITION_POSITION,A.HIGH_VALUE,A.NUM_ROWS,B.BYTES/1024/1024 "SIZE_MB" FROM DBA_TAB_PARTITIONS A, DBA_SEGMENTS B
WHERE A.TABLE_NAME LIKE 'AUD%' and A.TABLE_NAME=B.SEGMENT_NAME and A.PARTITION_NAME=B.PARTITION_NAME;
ReplyDelete
Replies
AnonymousThursday, April 18, 2024
SQL> SELECT do.owner,do.object_name ,
s.row_wait_obj# ,
s.row_wait_file# ,
s.row_wait_block#,
s.row_wait_row# ,
dbms_rowid.rowid_create ( 1, data_object_id, rfile#, ROW_WAIT_BLOCK#, ROW_WAIT_ROW# )
FROM gv$session s,
dba_objects do,
v$datafile v
WHERE s.blocking_session = 1028
AND s.row_wait_obj# = do.object_id
AND s.row_wait_file# = v.file#
;
ReplyDelete
Replies
AnonymousMonday, September 30, 2024
CREATE AUDIT POLICY ACTIONS_BY_PRIVILEGED_USERS ACTIONS ALL ONLY TOPLEVEL;
AUDIT POLICY ACTIONS_BY_PRIVILEGED_USERS BY SYS, SYSKM, SYSTEM, SYSBACKUP, SYSRAC, SYSDG, PUBLIC;
AUDIT POLICY ACTIONS_BY_PRIVILEGED_USERS BY USERS WITH GRANTED ROLES DBA;
AUDIT POLICY ORA_CIS_RECOMMENDATIONS;
AUDIT POLICY ORA_DATABASE_PARAMETER;
ReplyDelete
Replies
AnonymousSunday, October 06, 2024
export PATH
alias sid='cat /etc/oratab|grep -v '\''^#'\''|grep -v '\''^$'\''|awk -F: '\''{print $1}'\'';source /usr/local/bin/oraenv;echo " ORACLE_HOME : "$ORACLE_HOME;echo " ORACLE_SID : "$ORACLE_SID'
alias sqldba='sqlplus / as sysdba'
alias trc='cd /u01/app/oracle/diag/rdbms/*/$ORACLE_SID/trace'
ReplyDelete
Replies

Add comment

Oracle DB Session Killing scripts

42 comments:

Oracle DBA

Blog Archive