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';
#!/bin/ksh
ReplyDelete#
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
CREATE USER GG_SCHEMA IDENTIFIED BY "XXXXXXXXX" DEFAULT TABLESPACE GGS_DATA TEMPORARY TABLESPACE TEMP
ReplyDeletePROFILE 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;
The SQL, accounts for any tblsps already encrypted.
ReplyDeleteSet 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}
elect sid "waiter",a.inst_id "Waiting_Inst", username, a.sql_id "Blocked sql",
ReplyDeleteb.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;
Incremental deletes:
ReplyDeleteset 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;
Drop OLD Partitions
ReplyDeleteCREATE 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;
/
CREATE OR REPLACE function abc.get_high_value_as_date(
ReplyDeletep_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;
/
DGMGRL> VALIDATE DATABASE OPROPRD;
ReplyDeleteDGMGRL> 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;
export ORACLE_SID=ABCD4
ReplyDeletedate
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
sids=(`cut -f1 -d: /var/opt/oracle/oratab | egrep -v "^#|^\*" | LANG=C sort -f | tr [:lower:] [:upper:]`)
ReplyDeleteMAP ETMSUSER.AUTOPROCESSINSTANCE, TARGET GGS_PHX.exceptions, EXCEPTIONSONLY,
ReplyDeleteINSERTALLRECORDS,
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
);
CREATE TABLE GGS_OMH.EXCEPTIONS
ReplyDelete(
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;
select 'alter system kill session '''||sid||','||serial#||',@'||inst_id||''' immediate;'
ReplyDeletefrom
(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);
ABLE RXOWNER.EPCS_AUDITABLE_EVENTS,GETBEFORECOLS (ON UPDATE ALL, ON DELETE KEY);
ReplyDeleteTABLE 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);
MAP RXOWNER.RXC_EXCEPN_TRANSACTION, TARGET RXOWNER.RXC_EXCEPN_TRANSACTION,&
ReplyDeleteCOMPARECOLS(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();
MACRO #exception_handler
ReplyDeleteBEGIN
,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
---
#!/bin/ksh
ReplyDelete# 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
}
ReplyDeletefunction 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
if [[ $DB2 = "OLTP" ]] || [[ $DB2 = "oltp" ]]; then
ReplyDeletegather_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
https://us05web.zoom.us/j/83030055781?pwd=aWQwQyt5dVlhSGxLUUZZdkRZeTVDdz09
ReplyDeleteThis comment has been removed by the author.
ReplyDeletehttps://us06web.zoom.us/j/5552739509?pwd=b0pma1BhVjFtSDVqbnN4aFQrZk5Idz09
ReplyDeletedbca -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
ReplyDeletecol file_name for a66
ReplyDeleteset 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
select DB_NAME,APP_DBA
ReplyDeleteFROM 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
exec dbms_stats.gather_table_stats('ELODBP05','LN_CTRAN_7007','SYS_P2259272',degree=>32);
ReplyDeleteexec 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);
set echo on
ReplyDeleteset 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
alias sid='. /opt/oracle/.local_profile'
ReplyDelete[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
ReplyDeleteselect '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
impdp \
ReplyDeleteDIRECTORY=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
ReplyDeleteimpdp \
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
DECLARE (Syntax for export)
ReplyDeletehdnl 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;
/
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
ReplyDeleteexport ORACLE_BASE=/oracle01/app/oracle
ReplyDeleteexport 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
select
ReplyDeletesql_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
);
ReplyDeleteSQL> 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;
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
ReplyDeleteWHERE A.TABLE_NAME LIKE 'AUD%' and A.TABLE_NAME=B.SEGMENT_NAME and A.PARTITION_NAME=B.PARTITION_NAME;
alias coraenv='cat /var/opt/oracle/oratab|grep -v '\''^#'\''|grep -v '\''^$'\''|awk -F: '\''{print $1}'\'';source /usr/bin/oraenv'
ReplyDeleteSELECT 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
ReplyDeleteWHERE A.TABLE_NAME LIKE 'AUD%' and A.TABLE_NAME=B.SEGMENT_NAME and A.PARTITION_NAME=B.PARTITION_NAME;
SQL> SELECT do.owner,do.object_name ,
ReplyDeletes.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#
;