##################################### BOBBY #############################################
ls -ltrh | grep -i "Feb 11"
===================================
ERROR: unrecoverable error ORA-29701 raised in ASM I/O path; terminating process 64234
you need to increase the below parameter value in clmprd asm instace pfile
current it is : *._asm_hbeatiowait =120
need to increase 180
*._asm_hbeatiowait=180
===================================
Have a look in the file '/var/adm/messages' for hardware errors.
select status , error from v$archive_dest where des_id=2; ==> dg errors
/var/opt/oracle/oratab
sys/n3wy0rk@wflprd
G_H: /u01/app/11.2.0/grid/bin ==> crsctl stat res -t
/opt/xts/sudo/bin/sudo -u oepmprd -i bash ==>
/opt/xts/sudo/bin/sudo -u ofisprd -i bash
/opt/xts/sudo/bin/sudo -u ocogprd -i bash
BKP :
nohup /u01/app/oracle/admin/dba/rman/rman_backup_rman.ksh xistracs level0 /u01/app/oracle/admin/dba/rman /migration/Oracle_backup > /tmp/level0_xistracs.log 2>&1 &
copy :
====
scp exp_clmprd_refer_data_08-Sep-2018.log prasadbh@10.5.99.24:/u02/orabackup_NFS/mat_imp/pump
copy bkp:
ls -ltr *20181208*
cp *20181213* /migration/Oracle_Backup/actimruatdatz01/level0/13dec
nohup cp *20181208* /migration/Oracle_Backup/actimruatdatz01/level0/13dec &
%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
SQL> set echo on
chmod 775 20180105170001.sql
SPOOL /tmp/BP/20180615100101.log
set serveroutput on
set time on
set echo on
alter session set current_schema=CDW_CLASS_INCREMENTAL;
SPOOL OFF
SQL> show error
%%%%%%%%%%%%%%%%%%%%%%%%%%
oracle@bascdwtsddatz01$ ls -l "REQ000000790158.sql"
-rw-r--r-- 1 oracle oinstall 609 Jul 12 12:04 REQ000000790158.sql
oracle@bascdwtsddatz01$ chmod 655 REQ000000790158.sql
oracle@bascdwtsddatz01$ ls -l "REQ000000790158.sql"
-rw-r-xr-x 1 oracle oinstall 609 Jul 12 12:04 REQ000000790158.sql
=====> alter session set current_schema=ABC ===> set serveroutput on ==> set time on ======
*** Process count :
ps -ef | grep ora | wc -l
touch listener.log
gzip listener.log
ls -ltrh | grep "May 5"
ls -rlth (with size)
===== Alert log ==
grep ORA- alert.log|more
cat alert_pbstsb.log |grep ORA-01821|sort|uniq
grep ORA-20001 alert_ibiuat.log
$ vi alert_sid.log
i) Now go the last line by pressing (capital G)
ii) now press ?(question Mark) (its like CTRL+F in windows) and then followed by ORA- and ENTER.
iii) for scrolling upwards (capital N)
for scrolling downwards (small n)
==> for 1 week alert log data : ===>
awk '/Jul 16/,/Jul 24/' alert_ibidev.log | sed '$d' >/tmp/Bobby.log
cat /var/opt/oracle/oratab
ls -1 | wc -l
find *.trc -mtime +7 -exec rm {} \;
find *.trc -mtime +7 (7 is num of days)
gunzip /var/log/mail.log.gz
++++++++++++++++++
****** Super fast long dump file deletion find . -name '*.aud' -mtime +15 -exec rm {} \; **********
du -ah /u01/app/oracle
find /u01/app/oracle/admin/clmtsd/adump/*trc.gz -mtime +45 -exec rm {} \;
find /u01/app/oracle/diag/rdbms/wfltsd/wfltsd/trace/*.gz -mtime +45 -exec ls -ltr {} \;
##################
find /u01/app/oracle/diag/rdbms/wfltsd/wfltsd/trace/*.trc -mtime +45 -exec ls -ltr {} \;
find /u01/app/oracle/diag/rdbms/wfltsd/wfltsd/trace/*.trc -mtime +45 -exec gzip {} \;
first check with ls -ltr and then give 1st command
===========================CPU commands ==================
prtstat
vmstat 3 3
sar
top
prstat -s rss
ipcs -a
Solaris load avg:
ipcs -a
sar 2 5
mpstat
vmstat -p 3
uptime
vmstat 3 10
====================OEM ==============
./emctl stop agent;
./emctl clearstate agent;
./emctl start agent;
./emctl upload agent
/u01/app/oracle/agent12c/core/12.1.0.5.0/bin
export EDITOR=vi
stty columns 120
======== > Account Lock <===
AU_XSBUTIL
select username,account_status from dba_users where account_status like '%LOCK%';
select username,account_status from dba_users where username='CDW_BO';
SQL > alter user CDW_BO account unlock;
====> Procedure checks <==============
SQL> select owner, object_name, object_type from ALL_OBJECTS where object_name = 'STP_INSERT_XAG_DRI_LINK_PG';
select DBMS_METADATA.GET_DDL('PACKAGE','STP_INSERT_XAG_DRI_LINK_PG','XAG') from ALL_OBJECTS where object_name = 'STP_INSERT_XAG_DRI_LINK_PG';
=====> Schema <============
select owner, sum(bytes)/1024/1024/1024 schema_size_gig from dba_segments group by owner;
select username from dba_users where username like '%CLAIM_EVENT%';
alter session set current_schema=CDW;
select sum(bytes)/1024/1024/1024 as size_in_gig, segment_type from dba_segments where owner='IMANAGE' group by segment_type;
TABLE_NAME :
===========
select TABLE_NAME ,STATUS,OWNER from dba_tables where OWNER='CDW_CLASS'
SQL> select TABLE_NAME ,STATUS,OWNER from dba_tables where table_name='PBS_DATA';
* select OBJECT_NAME ,OWNER from dba_objects where OBJECT_NAME like '%LINK%' and owner='XAG'
SQL> select segment_name,segment_type,bytes/1024/1024 MB from dba_segments where segment_type='TABLE' and segment_name='PBS_DATA';
SEGMENT_NAME SEGMENT_TYPE MB
-------------------- ------------------ ----------
DIM_CALENDAR_DAY TABLE 12
#################### BLOB + CLOB OBJECTS IN DATABAE ####################
select TABLE_NAME,COLUMN_NAME,DATA_TYPE,DATA_LENGTH FROM DBA_TAB_COLUMNS WHERE DATA_TYPE LIKE '_LOB' AND OWNER LIKE '%CLAIM_EVENT%'
1) SELECT SUM(DBMS_LOB.GetLength("COLUMN_NAME"))/1024/1024/1024 AS SizeGB FROM OWNER.TABLE * (columname will get from the dba_lobs view)
+
2) select segment_name,segment_type,bytes/1024/1024/1024 GB from dba_segments where segment_type='TABLE' and segment_name='CLAIM_EVENT_PROCESS_REQUEST';
add 1+2 = full table size
SQL> select OWNER, TABLE_NAME, COLUMN_NAME, DATA_TYPE FROM ALL_TAB_COLUMNS a where a.DATA_TYPE in ('CLOB','BLOB','NCLOB','BFILE') and TABLE_NAME='CLAIM_EVENT_PROCESS_RESPONSE;
SQL> select TABLE_NAME,OWNER,COLUMN_NAME,SEGMENT_NAME from dba_lobs where TABLE_NAME='CLAIM_EVENT_PROCESS_RESPONSE';
SQL> select s.segment_name, s.partition_name, bytes/1048576 "Size (MB)" from dba_segments s, dba_lobs l
where s.segment_name = l.segment_name and s.owner = 'CLAIM_EVENT' order by s.segment_name, s.partition_name;
########################### Sequence + last number of sequence ####################
SQL> select SEQUENCE_NAME,LAST_NUMBER FROM all_sequences WHERE SEQUENCE_NAME ='EACC_R2_TRUST_FUND_SEQ';
SQl> select sequence_name, last_number from dba_sequences where sequence_owner = user;
SQL> select SEQUENCE_NAME,LAST_NUMBER from dba_sequences where SEQUENCE_OWNER='&OWNER' AND SEQUENCE_NAME='&SEQ_NAME';
queue= sequence name
last_number= current sequence number.
************ INDEX *********
select table_name, index_name from dba_ind_columns where table_owner='CLAIM_EVENT' order by table_name;
select table_name, index_name from dba_ind_columns where table_name='CLAIM_EVENT' order by table_name;
========================after import check if indexes and constraints as same in primary and backup table=============
SQL>
SQL>
col INDEX_NAME for a40
col TABLE_NAME for a40
col COLUMN_NAME for a40
select
b.uniqueness, a.index_name, a.table_name, a.column_name
from all_ind_columns a, all_indexes b
where a.index_name=b.index_name
and a.table_name = upper('CLAIM_EVENT_A')
order by a.table_name, a.index_name, a.column_position;
=====> User MAp by sudhir ======>
select * from DBA_ROLE_PRIVS where GRANTEE=upper('&user');
WADHWAN
> grant rolename to DESWALN_11528521;
select grantee,table_name,privilege from dba_tab_privs where grantee='&rolename';
: : Grant Select on all tables in a schema : :
Select 'GRANT SELECT ON HR.'||Table_Name||' TO SCOTT;' From All_Tables Where Owner='HR';
=============== Partitions ====
select a.TABLE_NAME ,a.PARTITION_NAME,a.TABLESPACE_NAME, b.PARTITIONING_TYPE from dba_part_tables b ,dba_tab_partitions a where a.TABLE_NAME=b.TABLE_NAME
and a.TABLE_NAME='DIM_TRANSACTION';
############### CONSTRAINTS ############### (use all_constraints )
SELECT * FROM user_cons_columns WHERE table_name = 'CLAIM_EVENT';
select table_name , CONSTRAINT_NAME FROM all_constraints WHERE table_name='CLAIM_EVENT_PROCESS_REQUEST_A';
======================================check if constraints are same in both tables=========
SQL> select OWNER,CONSTRAINT_NAME,CONSTRAINT_TYPE,TABLE_NAME,STATUS from dba_constraints where OWNER='CLAIM_EVENT' and TABLE_NAME='CLAIM_EVENT_A';
SQL> select OWNER,CONSTRAINT_NAME,CONSTRAINT_TYPE,TABLE_NAME from dba_constraints where OWNER='CLAIM_EVENT' and TABLE_NAME='CLAIM_EVENT';
=
select OWNER,CONSTRAINT_NAME,CONSTRAINT_TYPE,TABLE_NAME,status from dba_constraints where OWNER='CLAIM_EVENT' and TABLE_NAME='CLAIM_EVENT_PROCESS_RESPONSE';
alter table CLAIM_EVENT.CLAIM_EVENT_PROCESS_RESPONSE ENABLE constraint FK_CLAIM_EVENT_PROCESS_RESPONS;
********** alter table owner.tablename ENABLE/DISABLE constraint constraint_name; *******************
SQL> select DIRECTORY_NAME, DIRECTORY_PATH from dba_directories where DIRECTORY_NAME = 'DATA_PUMP_DIR';
====================== DB UPTIME
select instance_name, to_char(startup_time,'mm/dd/yyyy hh24:mi:ss') as startup_time from v$instance;
srvctl status database -d epmprd -v -f
select INSTANCE_NAME,HOST_NAME,to_char(startup_time,'DD-Mon-yy HH24:MI:SS'),STATUS,LOGINS,DATABASE_STATUS from gv$instance;
=============== Clearing /u01 mount point ==========
df -h .
find /u01/app/oracle/admin/imrdev/udump/*.trc -mtime +1 -exec ls -ltr {} \; |wc -l
find /u01/app/oracle/admin/imrprd/udump/*.trc -mtime +15 -exec rm {} \;
df -h .
gunzip /var/log/mail.log.gz
====================================================================================================
:: DB_size::
========================================================================================================
select name,open_mode,database_role,log_mode,current_scn from v$database;
select (a.data_size+b.temp_size+c.redo_size+d.controlfile_size)/1024 "total_size in GB"
from ( select sum(bytes)/1024/1024 data_size from dba_data_files) a,( select nvl(sum(bytes),0)/1024/1024 temp_size
from dba_temp_files ) b,(select sum(bytes)/1024/1024 redo_size from sys.v_$log ) c,( select sum(BLOCK_SIZE*FILE_SIZE_BLKS)/1024/1024
controlfile_size
from v$controlfile) d;
================== 9i =================================
SELECT SUM (bytes) / 1024 / 1024 / 1024 AS GB FROM dba_data_files;
Gives the size occupied by data in this database or Database usage details.
SELECT SUM (bytes)/1024/1024/1024 AS GB FROM dba_segments;
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------
*****************************************************************************************************************************************************************
*********************************************************************************************************************************************************************
TABLESPACE RELATED
*****************************************************************************************************************************************************************
*****************************************************************************************************************************************************************
List information about tablespace to which datafiles belong ::
SELECT FILE_NAME,TABLESPACE_NAME,BYTES,AUTOEXTENSIBLE, MAXBYTES,INCREMENT_BY FROM DBA_DATA_FILES;
######### TS+ DF ##################
select tablespace_name, file_name from dba_data_files union select tablespace_name, file_name from dba_temp_files /
Datafiles info
==============
set lines 300
set pages 300
col file_name for a55
select file_name,bytes/1024/1024/1024 GB ,autoextensible, Maxbytes/1024/1024/1024 from dba_data_files where tablespace_name='GGS' order by file_id;
always use this script in IMRPRD
so that you can get the file names in order
QUERY TO CHECK THE TABLESPACE SIZE USED AND FREE ::--
======================================================================================
select * from dba_tablespace_usage_metrics;
========
SELECT /* + RULE */ df.tablespace_name "Tablespace",
df.bytes / (1024 * 1024) "Size (MB)",
SUM(fs.bytes) / (1024 * 1024) "Free (MB)",
Nvl(Round(SUM(fs.bytes) * 100 / df.bytes),1) "% Free",
Round((df.bytes - SUM(fs.bytes)) * 100 / df.bytes) "% Used"
FROM dba_free_space fs,
(SELECT tablespace_name,SUM(bytes) bytes
FROM dba_data_files
GROUP BY tablespace_name) df
WHERE fs.tablespace_name (+) = df.tablespace_name
GROUP BY df.tablespace_name,df.bytes
UNION ALL
SELECT /* + RULE */ df.tablespace_name tspace,
df.total ,
df.bytes_free,
Nvl(Round(( df.total - df.bytes_used) * 100 / df.total), 1),
Round((df.total - df.bytes_free) * 100 / df.total)
FROM dba_temp_files fs,
(SELECT tablespace_name, sum(bytes_free)/ (1024 * 1024) bytes_free,sum(bytes_used)/ (1024 * 1024) bytes_used, ( sum(bytes_free)/ (1024 * 1024) + sum(bytes_used)/ (1024 * 1024) ) total
FROM v$temp_space_header
GROUP BY tablespace_name) df
WHERE fs.tablespace_name (+) = df.tablespace_name
GROUP BY df.tablespace_name,df.total,df.bytes_free,df.bytes_used
ORDER BY 4 DESC;
=============================================================================================================================
TABLESPACE WITH FREE PCT SPACE BY DEEPAK :
=============
select df.tablespace_name tspace,
df.bytes/(1024*1024) tot_ts_size,
(df.bytes - (df.allocated - fs.free_space))/(1024*1024) free_ts_size,
df.bytes/(1024*1024)-(df.bytes - (df.allocated - fs.free_space))/(1024*1024) usedspace,
round(((df.bytes - (df.allocated - fs.free_space))/df.bytes) * 100) tc_pct
from (select tablespace_name, sum(bytes) free_space
from dba_free_space
group by tablespace_name ) fs ,
(select tablespace_name,
sum(decode(autoextensible,'NO',bytes,maxbytes)) bytes,
sum(user_bytes) allocated
from dba_data_files
group by tablespace_name ) df
where fs.tablespace_name = df.tablespace_name;
===>
show parameter undo
col FILE_NAME for a80
set lines 199 pages 999
select FILE_NAME,BYTES/(1024*1024*1024),AUTOEXTENSIBLE,MAXBYTES/(1024*1024*1024)from dba_data_files where TABLESPACE_NAME='UNDOTBS1';
Check Table Space Free Space with files in GB
_______________________________________
set linesize 300
set pagesize 300
col TABLESPACE_NAME for a30
col FILE_NAME for a46
SELECT B.TABLESPACE_NAME,
B.FILE_NAME,
ROUND (B.BYTES / 1024 / 1024/1024, 2) "SIZE GB",
ROUND (B.MAXBYTES / 1024 / 1024 / 1024, 2) "MAX SIZE GB",
ROUND (SUM (A.BYTES) / 1024 / 1024 / 1024, 2) "FREE SPACE GB",
CASE
WHEN MAXBYTES = 0
THEN
ROUND (SUM (A.BYTES) / 1024 / 1024 / 1024, 2)
ELSE
ROUND (
( (B.MAXBYTES - B.BYTES) + SUM (A.BYTES)) / 1024 / 1024 / 1024, 2)
END
"TOTAL FREE SPACE GB"
FROM DBA_FREE_SPACE A, DBA_DATA_FILES B
WHERE A.FILE_ID = B.FILE_ID AND B.TABLESPACE_NAME IN ('XMA_DATA')
GROUP BY B.TABLESPACE_NAME,
B.FILE_NAME,
B.BYTES,
B.MAXBYTES
ORDER BY 1, 2;
===============================================================================================================================
TEMP -> usage
SQL> select file_name , TABLESPACE_NAME from DBA_TEMP_FILES;
SELECT A.tablespace_name tablespace, D.mb_total,
SUM (A.used_blocks * D.block_size) / 1024 / 1024 mb_used,
D.mb_total - SUM (A.used_blocks * D.block_size) / 1024 / 1024 mb_free
FROM v$sort_segment A,
(
SELECT B.name, C.block_size, SUM (C.bytes) / 1024 / 1024 mb_total
FROM v$tablespace B, v$tempfile C
WHERE B.ts#= C.ts#
GROUP BY B.name, C.block_size
) D
WHERE A.tablespace_name = D.name
GROUP by A.tablespace_name, D.mb_total;
======
SELECT S.sid || ',' || S.serial# sid_serial, S.username, S.osuser, P.spid, S.module,
P.program, SUM (T.blocks) * TBS.block_size / 1024 / 1024 mb_used, T.tablespace,
COUNT(*) statements
FROM v$sort_usage T, v$session S, dba_tablespaces TBS, v$process P
WHERE T.session_addr = S.saddr
AND S.paddr = P.addr
AND T.tablespace = TBS.tablespace_name
GROUP BY S.sid, S.serial#, S.username, S.osuser, P.spid, S.module,
P.program, TBS.block_size, T.tablespace
ORDER BY mb_used;
******** FREE SIZE OF TEMP ******
SELECT A.tablespace_name tablespace, D.mb_total,
SUM (A.used_blocks * D.block_size) / 1024 / 1024 mb_used,
D.mb_total - SUM (A.used_blocks * D.block_size) / 1024 / 1024 mb_free
FROM v$sort_segment A,
(
SELECT B.name , C.block_size , SUM (C.bytes) / 1024 / 1024 mb_total
FROM v$tablespace B, v$tempfile C
WHERE B.ts#= C.ts#
GROUP BY B.name, C.block_size
) D
WHERE A.tablespace_name = D.name
GROUP by A.tablespace_name, D.mb_total;
Adding datafile IN ASM
----------------------
SQL> alter tablespace STATSPACK_DATA add datafile '+DATA' size 1G
Tablespace altered.
alter tablespace CLAIM_EVENT_DATA add datafile '+DATA' size 30G autoextend off
alter tablespace ODS_LARGE_DATA add datafile '+DATA' size 10G autoextend off
+DATA/clmtsa/datafile/claim_event_data.285.974270209
/u08/cdwdata/ibiprd/cdw_warehouse_data_24.dbf
SQL> alter database datafile '/u25/oradata/imrprd/data01/users03.dbf' resize 8g
alter database datafile '+DATA/ibiuat/datafile/imr_ods_data.376.949974073' resize 15g
Database altered.
New Datafile additon to TS:
==================
SQL> alter tablespace XAG_DATA add datafile '/u06/oradata/imrsec/data01/xag_data69.dbf' size 30g autoextend off
alter tablespace XMA_DATA add datafile '/u33/oradata/imrprd/data01/xma_data_99.dbf' size 30g autoextend off
alter tablespace AUDIT_DATA add datafile '/u06/oradata/rdcdev/audit_data5.dbf' size 2g autoextend off
SQL> alter tablespace PEGA_DATA add datafile '+DATA' size 30g
alter tablespace PBS_DATA add datafile '+DATA' size 30g
alter tablespace CLASS_TRIGGERS_DATA add datafile '/u23/oradata/imrprd/data01/class_triggers_data_13.dbf' size 7g ;
mkdir -p /u32/oradata/imrprd/data01/
SQL> alter database datafile '/u23/oradata/imrprd/data01/class_triggers_data_13.dbf' resize 7g
/u10/oradata/imrfof/data01/xma_data_04.dbf
Database altered.
+DATA/clmtsc/datafile/statspack_data.274.961837443
SQL> alter tablespace USERS add datafile '+DATA/clmtrn/datafile/users.264.875111507' size 2G;
imr_ods_data.376.949974073
/u11/oradata/imrfof/data01/xag_data_34.dbf
==========: ORA-01691: unable to extend lob segment ================
SYSTEM alter tablespace SYSTEM add datafile '+DATA' size 1g
alter tablespace PBS_DATA add datafile '+DATA' size 30g
Wed Oct 17 15:42:17 2018
Completed: alter tablespace pbs_data add datafile '+DATA' size 5g
We
alter tablespace STAGING_HISTORY_DATA add datafile '+DATA' size 8g;
=============================================================== Auto Extend off/on On Datafiles on Oracle Database =====================================================================
To Turn Off Auto Extend On Data files :
select 'alter database datafile '''||file_name||''' AUTOEXTEND OFF;' from dba_data_files where autoextensible='YES';
To Turn On Auto Extend On Data files :
select 'alter database datafile '''||file_name||''' AUTOEXTEND ON;' from dba_data_files where autoextensible='NO';
ASM Space Report
----------------
SET LINESIZE 150
SET PAGESIZE 9999
SET VERIFY off
COLUMN group_name FORMAT a20 HEAD 'Disk Group|Name'
COLUMN sector_size FORMAT 99,999 HEAD 'Sector|Size'
COLUMN block_size FORMAT 99,999 HEAD 'Block|Size'
COLUMN allocation_unit_size FORMAT 999,999,999 HEAD 'Allocation|Unit Size'
COLUMN state FORMAT a11 HEAD 'State'
COLUMN type FORMAT a6 HEAD 'Type'
COLUMN total_mb FORMAT 999,999,999 HEAD 'Total Size (GB)'
COLUMN free_mb FORMAT 999,999,999 HEAD 'Free Size (GB)'
COLUMN used_mb FORMAT 999,999,999 HEAD 'Used Size (GB)'
COLUMN pct_used FORMAT 999.99 HEAD 'Pct. Used'
SELECT
distinct name group_name
, sector_size sector_size
, block_size block_size
, allocation_unit_size allocation_unit_size
, state state
, type type
, round(total_mb/1024) "total_gb"
, round(free_mb/1024) "free_gb"
, round((total_mb - free_mb) / 1024) "used_gb"
, round((1- (free_mb / total_mb))*100, 2) "pct_used"
from v$asm_diskgroup ORDER BY name
/
============================= files in DG
SQL>
col ASM_DG for a20
col TYPE for a20
select 'Data Files' type,substr(name,1,instr(name,'/',1,2)-1) asm_dg,count(*) cnt
from v$datafile
group by substr(name,1,instr(name,'/',1,2)-1)
union
select 'Temp Files',substr(name,1,instr(name,'/',1,2)-1) asm_dg, count(*) cnt
from v$tempfile
group by substr(name,1,instr(name,'/',1,2)-1)
union
select 'Redo Member',substr(member,1,instr(member,'/',1,2)-1) asm_dg, count(*) cnt
from v$logfile
group by substr(member,1,instr(member,'/',1,2)-1)
/
File Type ASM Disk Location Number of Files
-------------- -------------------- ---------------
Data Files +DATA_CM01/visx 239
Redo Member +RECO_CM01/visx 6
Temp Files +DATA_CM01/visx 5
=========================================
select * from v$flash_recovery_area_usage;
BKP point also
Archive log list;
select name from v$datafile;
select name from v$tempfile;
select member from v$logfile;
select * from v$log;
Sql> select group#, member from v$logfile order by group#, member;
********************************************************************************
How much redo generated for a month:
SELECT SUM_ARCH.DAY,
SUM_ARCH.GENERATED_MB,
SUM_ARCH_DEL.DELETED_MB,
SUM_ARCH.GENERATED_MB - SUM_ARCH_DEL.DELETED_MB "REMAINING_MB"
FROM ( SELECT TO_CHAR (COMPLETION_TIME, 'DD/MM/YYYY') DAY,
SUM (ROUND ( (blocks * block_size) / (1024 * 1024*1024), 2))
GENERATED_MB
FROM V$ARCHIVED_LOG
WHERE ARCHIVED = 'YES'
GROUP BY TO_CHAR (COMPLETION_TIME, 'DD/MM/YYYY')) SUM_ARCH,
( SELECT TO_CHAR (COMPLETION_TIME, 'DD/MM/YYYY') DAY,
SUM (ROUND ( (blocks * block_size) / (1024 * 1024*1024), 2))
DELETED_MB
FROM V$ARCHIVED_LOG
WHERE ARCHIVED = 'YES' AND DELETED = 'YES'
GROUP BY TO_CHAR (COMPLETION_TIME, 'DD/MM/YYYY')) SUM_ARCH_DEL
WHERE SUM_ARCH.DAY = SUM_ARCH_DEL.DAY(+)
ORDER BY TO_DATE (DAY, 'DD/MM/YYYY');
Per Day archive generation :
===========================
select trunc(COMPLETION_TIME,'DD') Day, thread#,
round(sum(BLOCKS*BLOCK_SIZE)/1024/1024/1024) GB,
count(*) Archives_Generated from v$archived_log
group by trunc(COMPLETION_TIME,'DD'),thread# order by 1;
========Log switches per hour in a week========
SET PAGESIZE 90
SET LINESIZE 150
set heading on
column "00:00" format 9999
column "01:00" format 9999
column "02:00" format 9999
column "03:00" format 9999
column "04:00" format 9999
column "05:00" format 9999
column "06:00" format 9999
column "07:00" format 9999
column "08:00" format 9999
column "09:00" format 9999
column "10:00" format 9999
column "11:00" format 9999
column "12:00" format 9999
column "13:00" format 9999
column "14:00" format 9999
column "15:00" format 9999
column "16:00" format 9999
column "17:00" format 9999
column "18:00" format 9999
column "19:00" format 9999
column "20:00" format 9999
column "21:00" format 9999
column "22:00" format 9999
column "23:00" format 9999
SELECT * FROM (
SELECT * FROM (
SELECT TO_CHAR(FIRST_TIME, 'DD/MM') AS "DAY"
, SUM(TO_NUMBER(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '00', 1, 0), '99')) "00:00"
, SUM(TO_NUMBER(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '01', 1, 0), '99')) "01:00"
, SUM(TO_NUMBER(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '02', 1, 0), '99')) "02:00"
, SUM(TO_NUMBER(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '03', 1, 0), '99')) "03:00"
, SUM(TO_NUMBER(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '04', 1, 0), '99')) "04:00"
, SUM(TO_NUMBER(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '05', 1, 0), '99')) "05:00"
, SUM(TO_NUMBER(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '06', 1, 0), '99')) "06:00"
, SUM(TO_NUMBER(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '07', 1, 0), '99')) "07:00"
, SUM(TO_NUMBER(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '08', 1, 0), '99')) "08:00"
, SUM(TO_NUMBER(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '09', 1, 0), '99')) "09:00"
, SUM(TO_NUMBER(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '10', 1, 0), '99')) "10:00"
, SUM(TO_NUMBER(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '11', 1, 0), '99')) "11:00"
, SUM(TO_NUMBER(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '12', 1, 0), '99')) "12:00"
, SUM(TO_NUMBER(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '13', 1, 0), '99')) "13:00"
, SUM(TO_NUMBER(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '14', 1, 0), '99')) "14:00"
, SUM(TO_NUMBER(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '15', 1, 0), '99')) "15:00"
, SUM(TO_NUMBER(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '16', 1, 0), '99')) "16:00"
, SUM(TO_NUMBER(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '17', 1, 0), '99')) "17:00"
, SUM(TO_NUMBER(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '18', 1, 0), '99')) "18:00"
, SUM(TO_NUMBER(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '19', 1, 0), '99')) "19:00"
, SUM(TO_NUMBER(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '20', 1, 0), '99')) "20:00"
, SUM(TO_NUMBER(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '21', 1, 0), '99')) "21:00"
, SUM(TO_NUMBER(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '22', 1, 0), '99')) "22:00"
, SUM(TO_NUMBER(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '23', 1, 0), '99')) "23:00"
FROM V$LOG_HISTORY
WHERE extract(year FROM FIRST_TIME) = extract(year FROM sysdate)
GROUP BY TO_CHAR(FIRST_TIME, 'DD/MM')
) ORDER BY TO_DATE(extract(year FROM sysdate) || DAY, 'YYYY DD/MM') DESC
) WHERE ROWNUM <8;
===== AVG of log switches per day ==============
SELECT trunc(first_time) DAY,
count(*) NB_SWITCHS,
trunc(count(*)*log_size/1024) TOTAL_SIZE_KB,
to_char(count(*)/24,'9999.9') AVG_SWITCHS_PER_HOUR
FROM v$loghist,
(select avg(bytes) log_size from v$log) GROUP BY trunc(first_time),log_size ;
*****************************************************************************************************************************************************************
*****************************************************************************************************************************************************************
BACKUP RELATED
*****************************************************************************************************************************************************************
*****************************************************************************************************************************************************************
RMAN BACKUPS QUERY
set lines 180
set pages 180
col STATUS for a24
col START_TIME for a20
col END_TIME for a20
col TIME_TAKEN_DISPLAY for a20
col OUTPUT_BYTES_DISPLAY for a15
col input_type for a20
select session_key,
input_type,
status,
to_char(start_time,'yyyy-mm-dd hh24:mi') start_time,
to_char(end_time,'yyyy-mm-dd hh24:mi') end_time,
output_bytes_display,
time_taken_display
from v$rman_backup_job_details
--where input_type like '%DB FULL%'
order by session_key asc;
=============================================================================
======================================= TIME FOR BACKUP ========================================
col END_TIME for a20
col START_TIME for a20
col DOW for a20
set lines 220
set pages 1000
col cf for 9,999
col df for 9,999
col elapsed_seconds heading "ELAPSED|SECONDS"
col i0 for 9,999
col i1 for 9,999
col l for 9,999
col output_mbytes for 9,999,999 heading "OUTPUT|MBYTES"
col session_recid for 999999 heading "SESSION|RECID"
col session_stamp for 99999999999 heading "SESSION|STAMP"
col status for a10 trunc
col time_taken_display for a10 heading "TIME|TAKEN"
col output_instance for 9999 heading "OUT|INST"
select
to_char(j.start_time, 'yyyy-mm-dd hh24:mi:ss') start_time,
to_char(j.end_time, 'yyyy-mm-dd hh24:mi:ss') end_time,
(j.output_bytes/1024/1024/1024) output_gb, j.status, j.input_type,
decode(to_char(j.start_time, 'd'), 1, 'Sunday', 2, 'Monday',
3, 'Tuesday', 4, 'Wednesday',
5, 'Thursday', 6, 'Friday',
7, 'Saturday') dow,
j.elapsed_seconds, j.time_taken_display,
x.cf, x.df, x.i0, x.i1, x.l,
ro.inst_id output_instance
from v$RMAN_BACKUP_JOB_DETAILS j
left outer join (select
d.session_recid, d.session_stamp,
sum(case when d.controlfile_included = 'YES' then d.pieces else 0 end) CF,
sum(case when d.controlfile_included = 'NO'
and d.backup_type||d.incremental_level = 'D' then d.pieces else 0 end) DF,
sum(case when d.backup_type||d.incremental_level = 'D0' then d.pieces else 0 end) I0,
sum(case when d.backup_type||d.incremental_level = 'I1' then d.pieces else 0 end) I1,
sum(case when d.backup_type = 'L' then d.pieces else 0 end) L
from
v$BACKUP_SET_DETAILS d
join v$BACKUP_SET s on s.set_stamp = d.set_stamp and s.set_count = d.set_count
where s.input_file_scan_only = 'NO'
group by d.session_recid, d.session_stamp) x
on x.session_recid = j.session_recid and x.session_stamp = j.session_stamp
left outer join (select o.session_recid, o.session_stamp, min(inst_id) inst_id
from Gv$RMAN_OUTPUT o
group by o.session_recid, o.session_stamp)
ro on ro.session_recid = j.session_recid and ro.session_stamp = j.session_stamp
where j.start_time > trunc(sysdate)-&NUMBER_OF_DAYS
order by j.start_time
/
===============================================================================================
Query to check the incremental Backup Details
----------------------------------------------
select incremental_level,
incremental_change#,
checkpoint_change#,
blocks
from v$backup_datafile;
==========================================================================================================================================================
-------creating RESTORE POINT -------------------
SQL> Create restore point BEFORE_UPGRADE_CRQ_24471 guarantee flashback database;
Restore point created.
SQL> alter session set current_schema=pegarules;
Session altered.
*****************************************************************************************************************************************************************
*****************************************************************************************************************************************************************
Clearing FRA/ARCHIVELOG LOCATION RELATED +FRA
*****************************************************************************************************************************************************************
*****************************************************************************************************************************************************************
Deleting backups based on tags:
================
DELETE BACKUP TAG='TAG20190221T200040';
DELETE BACKUP COMPLETED BEFORE 'SYSDATE-2' DEVICE TYPE DISK;
crosscheck backup;
delete expired backup;
delete archivelog all backed up 1 times to DEVICE TYPE disk;
****************************
Standby DB-Recovery ::
ALTER SYSTEM SET DB_RECOVERY_FILE_DEST_SIZE =2G SCOPE=BOTH ;
LIST BACKUP OF ARCHIVELOG ALL;
RMAN> delete archivelog until time 'sysdate-1' backed up 1 times to device type disk;
The following command can be used to manage the backup of the archive log when storage space needs to be released.
RMAN> DELETE BACKUP OF archivelog UNTIL TIME=’sysdate-5′;
given by deepak :
================
DELETE ARCHIVELOG ALL COMPLETED BEFORE 'sysdate-1'; ( force archive delete )
you can also run below after deleting the archives
CROSSCHECK ARCHIVELOG ALL;
DELETE EXPIRED ARCHIVELOG ALL;
given by sudhir:
================
DELETE FORCE NOPROMPT ARCHIVELOG UNTIL TIME 'SYSDATE-3' BACKED UP 1 TIMES TO DEVICE TYPE DISK;
DELETE FORCE ARCHIVELOG UNTIL TIME 'SYSDATE-1' BACKED UP 1 TIMES TO DEVICE TYPE DISK;
--> incase any backups of level 1 or level 0 are failed then we need to take the archive logs backup and do the delete input for example
check the format 'u02/orabackup/backups/pbstsa/rman_backup/ora_arch_%d_%s_%T_%c_%p' and then run below command
BACKUP ARCHIVELOG ALL FORMAT '/u02/orabackup/ibiuat/rman_backup/ora_arch_%d_%s_%T_%c_%p' delete input;
FRA checkup used space
========================
set lines 100
col name format a60
select name
, floor(space_limit / 1024 / 1024) "Size MB"
, ceil(space_used / 1024 / 1024) "Used MB"
from v$recovery_file_dest
order by name
/
=======================
DB Backup Mount point full:
SESSION_KEY INPUT_TYPE STATUS START_TIME END_TIME OUTPUT_BYTES_DI TIME_TAKEN_DISPLAY
----------- -------------------- ------------------------ -------------------- -------------------- --------------- --------------------
9199 ARCHIVELOG COMPLETED 2018-05-10 19:11 2018-05-10 19:16 19.96G 00:04:56
9208 DB INCR COMPLETED 2018-05-11 19:01 2018-05-11 19:18 32.25G 00:17:09
9227 DB INCR COMPLETED 2018-05-14 19:02 2018-05-14 19:48 63.45G 00:45:51
9228 DB INCR FAILED 2018-05-14 19:01 2018-05-15 00:49 130.57G 05:48:35
9247 DB INCR COMPLETED 2018-05-15 19:01 2018-05-16 02:01 197.76G 07:00:15
9277 ARCHIVELOG COMPLETED 2018-05-22 14:34 2018-05-22 14:47 92.94G 00:12:31
9280 DB INCR COMPLETED 2018-05-22 19:01 2018-05-22 19:24 12.54G 00:22:41
9289 DB INCR COMPLETED 2018-05-23 19:01 2018-05-23 19:15 23.39G 00:14:12
9298 DB INCR COMPLETED 2018-05-24 19:02 2018-05-25 02:17 206.42G 07:15:20
9307 DB INCR COMPLETED 2018-05-25 19:01 2018-05-25 19:14 17.19G 00:12:49
9316 DB INCR COMPLETED 2018-05-26 19:02 2018-05-27 02:09 206.20G 07:06:35
9335 DB INCR FAILED 2018-05-28 19:01 2018-05-28 19:17 21.88G 00:16:41
FULL BACKUP CLEAR ::
==================
delete backup completed before 'sysdate-5'
after above command plz check full backups is there or not with LEVEL 0
LIST BACKUP OF DATABASE;
RMAN> delete backup completed before 'sysdate-9';
rman> list backup;
using channel ORA_DISK_1
using channel ORA_DISK_2
List of Backup Pieces
BP Key BS Key Pc# Cp# Status Device Type Piece Name
------- ------- --- --- ----------- ----------- ----------
6929701 6929690 1 1 AVAILABLE DISK /u02/orabackup/backups/pbstsa/rman_backup/ora_df_PBSTSA_11119_20180524_1_1
6929702 6929691 1 1 AVAILABLE DISK /u02/orabackup/backups/pbstsa/rman_backup/ora_df_PBSTSA_11120_20180524_1_1
6929703 6929692 1 1 AVAILABLE DISK /u02/orabackup/backups/pbstsa/rman_backup/ora_df_PBSTSA_11121_20180524_1_1
Do you really want to delete the above objects (enter YES or NO)?
======================================================================
with % USED FRA SPACE
=======================
col name for a32
col size_m for 999,999,999
col used_m for 999,999,999
col pct_used for 999
SELECT name
, ceil( space_limit / 1024 / 1024) SIZE_M
, ceil( space_used / 1024 / 1024) USED_M
, decode( nvl( space_used, 0),
0, 0
, ceil ( ( space_used / space_limit) * 100) ) PCT_USED
FROM v$recovery_file_dest
ORDER BY name
/
*******************************************clearing +FRA by deleting the arvhice logs*********************************************************************************************************************
delete archivelog until time 'sysdate-3';
RMAN> delete archivelog until time 'sysdate-2' backed up 1 times to device type disk;
RMAN> DELETE ARCHIVELOG ALL COMPLETED BEFORE ‘sysdate-1’;
RMAN> DELETE ARCHIVELOG ALL BACKED UP 2 TIMES to disk;
RMAN> DELETE NOPROMPT ARCHIVELOG UNTIL SEQUENCE = 3790;
Delete Archivelog Backups
The following command can be used to manage the backup of the archive log when storage space needs to be released.
RMAN> DELETE BACKUP OF archivelog UNTIL TIME=’sysdate-5′;
=====================================================================================================================================
Query to check the incremental Backup Details
----------------------------------------------
select incremental_level,
incremental_change#,
checkpoint_change#,
blocks
from v$backup_datafile;
============================================
*****************************************************************************************************************************************************************
*****************************************************************************************************************************************************************
USER RELATED
*****************************************************************************************************************************************************************
*****************************************************************************************************************************************************************
Creating a user with read only access to perticular schemas
===========================================================
SQL> create user neer identified by neer;
SQL> grant create session to neer ;
dineshg_11528203
Select a.grantee User_name, a.granted_role role, b.privilege from DBA_ROLE_PRIVS a, DBA_SYS_PRIVS b where
a.granted_role=b.grantee and a.grantee='KAUSHIKV_5001082'
SELECT * FROM DBA_SYS_PRIVS;
SELECT * FROM DBA_TAB_PRIVS;
select * from dba_roles;
grant CLAIM_EVENT_READ_ONLY to KSINGHAL20_11527032;
--> For this to find out role which is related to schema ,use below query
select role from dba_roles where role like '%READ_ONLY%';
***********************************
--> For this to find out role which is related to schema ,use below query
select role from dba_roles where role like '%DEBUG%';
select privilege,role from role_sys_privs where role like 'JAVADEBUGPRIV';
*************************************
--> In Xchanging for new user creation any privilige we need create role first (in case role doesn't exists) and then assign this role to user.
SQL> create role PBS_DATASTORE_READ_ONLY;
SQL > select 'grant select on PBS_DATASTORE.'||table_name|| ' to dineshg_11528203;' from dba_tables where owner='PBS_DATASTORE';
SQL> grant PBS_DATASTORE_READ_ONLY to dineshg_11528203;
-- here PBS_DATASTORE_READ_ONLY is role name.
--> If any new table is created in schema to which access is needed then we need to add this table for SELECT/UPDATE roles in the schema.
---- Grant select on <New table name> to schema_Read_only_role;
Here I have created the script with the help of spool by selecting the tables of the HR schemas
SQL> SPOOL C:\select_privs.sql
SQL> select 'grant select on REPOSITORY.'||table_name|| ' to ;' from dba_tables where owner='REPOSITORY';
SQL> select 'grant select on hr.'||view_name|| ' to neer;' from dba_views where owner='HR';
SQL> spool off
Now , we will check the spool “select _privs.sql” and prepare this as script for grant permission.
SQL> @C:\select_privs.sql
The script “select_privs.sql” script after modification is .
grant select on hr.REGIONS to neer;
grant select on hr.LOCATIONS to neer;
grant select on hr.DEPARTMENTS to neer;
grant select on hr.JOBS to neer;
grant select on hr.EMPLOYEES to neer;
grant select on hr.JOB_HISTORY to neer;
grant select on hr.COUNTRIES to neer;
grant select on hr.EMP_DETAILS_VIEW to neer;
Given role to users ::
=====================
Select :
=======
select t.role,t.owner,t.privilege,t.table_name,r.grantee from role_tab_privs t ,dba_role_privs r where
t.role=r.granted_role and r.grantee like '%KAUSHIKV_5001082%'
Update :
=======
col PRIVILEGE for a10
select t.role,t.owner,t.privilege,t.table_name,r.grantee from role_tab_privs t ,dba_role_privs r where
t.role=r.granted_role and t.privilege ='UPDATE' and r.grantee like '%CSINGH%'
If you wish to grant select on dictionary views then:
SQL> conn / as sysdba
SQL> grant select any dictionary to user_read_only;
If you wish the read_only user could select ddl of any objects belongs to any schema then:
SQL> grant SELECT_CATALOG_ROLE to user_read_only;
SQL> conn user_read_only
SQL> select dbms_metadata.get_ddl('TABLE','EMPLOYEES','HR') from dual;
DBMS_METADATA.GET_DDL('TABLE','EMPLOYEES','HR')
--------------------------------------------------------------------------------
CREATE TABLE "HR"."EMPLOYEES"
( "EMPLOYEE_ID" NUMBER(6,0),
"FIRST_NAME"
application schema sizes
============================
select owner,sum(bytes)/1024/1024/1024 schema_size_gig,tablespace_name
from dba_segments
where owner not in ('SYSTEM', 'XDB', 'SYS', 'TSMSYS', 'MDSYS', 'EXFSYS', 'WMSYS', 'ORDSYS', 'OUTLN', 'DBSNMP')
group by owner,tablespace_name;
( OR )
select distinct owner, tablespace_name
from dba_segments
where owner in (select username from dba_users where default_tablespace not in ('SYSTEM','SYSAUX'));
total objects in a schema
============================
select owner, tablespace_name, count(*)
from dba_segments
group by owner, tablespace_name
order by owner, tablespace_name;
object wise
============================
select substr(owner,1,20) owner, substr(segment_name,1,30) object_name,
segment_type type
from dba_segments
--where owner not in ('SYS', 'SYSTEM')
where tablespace_name not in('SYSTEM','SYSAUX')
/
*****************************************************************************************************************************************************************
*****************************************************************************************************************************************************************
ORACLE JOB SCHEDULER RELATED
*****************************************************************************************************************************************************************
*****************************************************************************************************************************************************************
job scheduler
===================
To stop and reschedule a job we need to Stop the job from OEM
Administration > oracle_jobs > search for job with schema name and job name then stop the run.
--------------------------------------------------------------------------------------------------------
SQL> SELECT JOB_NAME, STATE FROM DBA_SCHEDULER_JOBS WHERE JOB_NAME = 'CDW_ETL_JOB';
SELECT JOB_NAME, STATE FROM DBA_SCHEDULER_JOBS WHERE JOB_NAME = 'CDW_CLASS_COPY_CHAIN_JOB';
SELECT JOB_NAME, START_DATE END_DATE,ENABLED,NEXT_RUN_DATE,STATE FROM DBA_SCHEDULER_JOBS where job_name in ('CDW_CLASS_COPY_CHAIN_JOB','CDW_ETL_JOB');
set linesize 800
select LOG_ID,LOG_DATE,OWNER,JOB_NAME,STATUS,ERROR#,REQ_START_DATE,ACTUAL_START_DATE,RUN_DURATION,SESSION_ID,CPU_USED,ADDITIONAL_INFO
from DBA_SCHEDULER_JOB_RUN_DETAILS where job_name in ('CDW_CLASS_COPY_CHAIN_JOB','CDW_ETL_JOB');
SELECT JOB_NAME, START_DATE END_DATE,ENABLED,NEXT_RUN_DATE,STATE FROM DBA_SCHEDULER_JOBS where job_name in ('CDW_CLASS_COPY_CHAIN_JOB','CDW_ETL_JOB');
select JOB,NEXT_DATE,LOG_USER,SCHEMA_USER,LAST_DATE,INTERVAL,LOG_USER from dba_jobs;
SELECT JOB_NAME, SCHEDULE_NAME, SCHEDULE_TYPE, ENABLED,STATE,OWNER FROM DBA_SCHEDULER_JOBS WHERE JOB_NAME in ('CDW_CLASS_COPY_CHAIN_JOB','CDW_ETL_JOB');
CDW_CLASS_COPY_CHAIN_JOB
CDW_ETL_JOB
BEGIN
DBMS_SCHEDULER.ENABLE ('CDW_ETL_JOB');
END;
/
select job_name,state from dba_scheduler_jobs where job_name in ('CDW_ETL_JOB','CDW_CLASS_COPY_CHAIN_JOB');
'
########################## “The whole database is suddenly slow – where to start?” #################
The simplest query for determining database state performance wise would be this:
SQL> select event, state, count(*) from v$session_wait group by event, state order by 3 desc;
== I decode the “WAITED FOR xyz TIME” wait states to “WORKING” and “On CPU / runqueue”.
select
count(*),
CASE WHEN state != 'WAITING' THEN 'WORKING'
ELSE 'WAITING'
END AS state,
CASE WHEN state != 'WAITING' THEN 'On CPU / runqueue'
ELSE event
END AS sw_event
FROM
v$session_wait
GROUP BY
CASE WHEN state != 'WAITING' THEN 'WORKING'
ELSE 'WAITING'
END,
CASE WHEN state != 'WAITING' THEN 'On CPU / runqueue'
ELSE event
END
ORDER BY
1 DESC, 2 DESC
/
Background======
select
count(*),
CASE WHEN state != 'WAITING' THEN 'WORKING'
ELSE 'WAITING'
END AS state,
CASE WHEN state != 'WAITING' THEN 'On CPU / runqueue'
ELSE event
END AS sw_event
FROM
v$session
WHERE
type = 'USER'
AND status = 'ACTIVE'
GROUP BY
CASE WHEN state != 'WAITING' THEN 'WORKING'
ELSE 'WAITING'
END,
CASE WHEN state != 'WAITING' THEN 'On CPU / runqueue'
ELSE event
END
ORDER BY
1 DESC, 2 DESC
/
===================================== ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired ====================
* . Below are the sesions holding the table, kindly confirm to kill them.
Given by Deepak Try this we need all sessions with date(Logon_time).
SELECT a.object, a.type, a.sid, s.serial#, s.username, s.program, s.logon_time
FROM v$access a, v$session s WHERE a.sid = s.sid
AND a.owner = 'CLAIM_EVENT'
AND a.object = 'CLAIM_EVENT_PROCESS_REQUEST';
for granting enable disable constraints in CLMTSA
SQL> grant alter on CLAIM_EVENT.CLAIM_EVENT_PROCESS_REQUEST to MAINT_PURG;
Grant succeeded
SQL> grant create any index to MAINT_PURG;
Grant succeeded.
=============== which SQL is being executed:
SQL> select sql_hash_value, count(*) from v$session
where status = 'ACTIVE' group by sql_hash_value order by 2 desc;
==
SQL> select sql_text,users_executing from v$sql where hash_value = 966758382;
####################################### CURRENT SESSION INFO ###############
set pages 400
set lines 300
COLUMN instance_name FORMAT a6 HEADING 'Instance'
COLUMN sid FORMAT 999999 HEADING 'SID'
COLUMN serial_id FORMAT 99999999 HEADING 'Serial ID'
COLUMN session_status FORMAT a9 HEADING 'Status'
COLUMN oracle_username FORMAT a10 HEADING 'Oracle User'
COLUMN os_username FORMAT a10 HEADING 'O/S User'
COLUMN os_pid FORMAT a8 HEADING 'O/S PID'
COLUMN session_machine FORMAT a20 HEADING 'Machine' TRUNC
COLUMN session_program FORMAT a40 HEADING 'Session Program' TRUNC
column Inactive_Time_In_Hours format 99999 Heading 'Inactive_Time_In_HOUR'
column active_Time_In_Hours format 99999 Heading 'active_Time_In_HOUR'
BREAK ON instance_name SKIP PAGE
SELECT
i.instance_name instance_name
, s.sid sid
, s.serial# serial_id
, s.status session_status
, s.username oracle_username
, s.osuser os_username
, p.spid os_pid
,Round(s.last_call_et/60,2) active_Time_In_Min
,s.machine session_machine
, s.program session_program
FROM
gv$session s
INNER JOIN gv$process p ON (s.paddr = p.addr AND s.inst_id = p.inst_id)
INNER JOIN gv$instance i ON (p.inst_id = i.inst_id)
where S.USERNAME IS NOT NULL
and s.username not in('SYS','SYSTEM') and s.username is not null and s.status='ACTIVE'
ORDER BY
i.instance_name
, s.USERNAME;
===================== Total sessions count with ACTIVE + INACTIVE ======================================
SELECT s.machine
, s.username
, count(decode(s.STATUS, 'ACTIVE', 1)) as active_con
, count(decode(s.STATUS, 'INACTIVE', 1)) as inactive_con
, count(*) as total_con
FROM v$session s
WHERE type <> 'BACKGROUND'
GROUP BY username, machine
ORDER BY total_con DESC;
===========================
set lines 130
col "logon" format a16
col username format a10
col spid format 99999
col osuser format a10
col sid for 999
col MACHINE for a30
col PROGRAM for a20
select a.username,a.sid,a.process ,b.spid,to_char(a.logon_time,'dd:mm:yyyy hh24:mi')
"logon", a.status,a.osuser,a.machine,a.program from v$session a, v$process b
where a.paddr=b.addr and a.username is not null and status='ACTIVE';
======= current sqls with sql_id + sqltext ===============
set pages 50000 lines 32767
col program format a40
col sql_text format a130
select b.sid,b.status,b.last_call_et,b.program,c.sql_id,c.sql_text from v$session b,v$sqlarea c
where b.sql_id=c.sql_id
/
Run SQL Tuning Advisor for the SQL_ID
SQL> @?/rdbms/admin/sqltrpt.sql
######################## BLOCKING SESSIONS ########################
col WAIT_CLASS for a20
select blocking_session, sid, serial#, wait_class, seconds_in_wait from v$session where blocking_session is not NULL order by blocking_session ;
Select (select username from v$session where sid=a.sid) blocker,
a.sid,' is blocking ',(select username from v$session where sid=b.sid) blockee,b
.sid from v$lock a, v$lock b where a.block = 1 and b.request > 0 and a.id1 = b.id1 and a.id2 = b.id2;
############################################################################
LongRunning Session GV$
set echo off linesize 200 pages 1000 head on feedback on
col username format a10
col start_time format a15
col curr_time format a15
col osuser format a10
col opname format a10
col target format a25
col tremain format 999999.99
col elamin format 999999.99
select a.sid,substr(b.username,1,10) username,substr(b.osuser,1,10) osuser,to_char(a.start_time,'dd-mon:hh24:mi:ss') start_time,
to_char(sysdate,'dd-mon:hh24:mi:ss') curr_time,a.opname,a.target,a.totalwork,a.sofar,(a.elapsed_Seconds)/60 elamin,
a.time_remaining/60 tremain
from v$session_longops a,
v$session b
where a.totalwork<>a.sofar
and a.sid=b.sid
order by 3
/
========> Given By Deepak
set lines 300
SELECT S.SID,S.SERIAL#,S.USERNAME,S.STATUS, s.sql_id,S.LAST_cALL_ET/60 "Min",S.OSUSER,S.MACHINE
FROM V$SESSION S,V$PROCESS P
WHERE S.PADDR=P.ADDR
AND S.USERNAME IS NOT NULL
AND S.STATUS='ACTIVE'
and S.LAST_cALL_ET/60>60 order by S.LAST_cALL_ET/60;
===== WFLPRD
SQL> select count(*) from PEGARULES.pc_assign_workbasket where pxassignedoperatorid='IMRResponseWB' and pxcreatedatetime like '30-AUG-18%';
COUNT(*)
----------
0
select sql_text from v$sqlarea where SQL_ID='&sqlid';
*******
alter system kill session '1421,14693';
***************************************
killing sessions lot at a time given by arun
select 'alter system kill session ' || '''' || sid || ',' || serial# || '''' || ' immediate;' from v$session
where username='XAG_EB2B' and status='ACTIVE';
=========RAMN LONG RUNNING =======
select b.sid, b.serial#, a.spid, b.client_info,b.LOGON_TIME from v$process a, v$session b where a.addr=b.paddr and client_info like 'rman%';
======> This will give table level Locks with SID:
select c.owner, c.object_name, c.object_type, b.sid, b.serial#, b.status, b.osuser, b.machine
from v$locked_object a , v$session b , dba_objects c where b.sid = a.session_id and a.object_id = c.object_id;
=====> SQL TEXT FROM SQL ID ::
set lines 300
select a.sid,a.program,b.sql_text from v$session a, v$sqltext b where a.sql_hash_value = b.hash_value and a.sid=5496
order by a.sid,hash_value,piece;
SQL> select sid, serial#,SQL_ADDRESS, status,PREV_SQL_ADDR from v$session where sid='76';
select sid, serial#,SQL_ADDRESS, status,PREV_SQL_ADDR ,WAIT_TIME from v$session where sid='344';
SQL> select SQL_ID,sql_text from v$sqltext where address='00000004EB782658';
0kkhhb2w93cx0
###################################################################################
SQL_TEXT : This gives final text and SID ******
==========
select a.sid,a.program,b.sql_text from v$session a, v$sqltext b where a.sql_hash_value = b.hash_value
set lines 300
select a.sid,a.program,b.sql_text from v$session a, v$sqltext b where a.sql_hash_value = b.hash_value and a.sid=6273
order by a.sid,hash_value,piece;
No.of connections:
=================
show parameter sessions
SQL> SELECT COUNT(*) FROM v$session WHERE STATUS = 'ACTIVE';
>select resource_name, current_utilization, max_utilization, limit_value from v$resource_limit where resource_name in ('sessions', 'processes');
> select count(*),sum(decode(status, 'ACTIVE',1,0)) from v$session where type= 'USER'
It will give time how long its running :
=======================================
SELECT S.SID,S.SERIAL#,S.USERNAME,S.STATUS, s.sql_id,S.LAST_cALL_ET/60 "Min",S.OSUSER,S.MACHINE
FROM V$SESSION S,V$PROCESS P WHERE S.PADDR=P.ADDR
AND S.USERNAME IS NOT NULL and status='ACTIVE' and S.LAST_cALL_ET/60>2 ORDER BY S.USERNAME ;
=== Locks (OBJECT LEVEL LOCKS)
SELECT B.Owner, B.Object_Name, A.Oracle_Username, A.OS_User_Name,A.LOCKED_MODE
FROM V$Locked_Object A, All_Objects B
WHERE A.Object_ID = B.Object_ID;
============= BLOCKGING SESSIONS
Select (select username from v$session where sid=a.sid) blocker,
a.sid,' is blocking ',(select username from v$session where sid=b.sid) blockee,b
.sid from v$lock a, v$lock b where a.block = 1 and b.request > 0 and a.id1 = b.id1 and a.id2 = b.id2;
SQL_TEXT:
select a.sid,a.program,b.sql_text from v$session a, v$sqltext b where a.sql_hash_value = b.hash_value and a.sid=146 order by a.sid,hash_value,piece;
===================
SELECT p.program, p.spid
FROM v$session s, v$process p
WHERE s.paddr = p.addr
AND p.spid IN (10);
all sql text running in database::
==================================
select sesion.sid,
sesion.username,
optimizer_mode,
hash_value,
address,
cpu_time,
elapsed_time,
sql_text
from v$sqlarea sqlarea, v$session sesion
where sesion.sql_hash_value = sqlarea.hash_value
and sesion.sql_address = sqlarea.address
and sesion.username is not null
/
==================== 9i L-R ===========================
select sid,serial#,username,LAST_CALL_ET/60 from v$session;
=============================================
select sql_text,sql_fulltext from v$sql where sql_id='85b24mzqpbqyb';
===========
first check rman validation is running or not it will show like valid in the output of ps -ef | grep rman
then run long runing query and kill rman session
ps -ef|grep rman
get it from v$session
1429 8311
alter system kill session '1429,8311';
alter system kill session '43,53377';
alter system kill session '343,20835';
alter system kill session '943,47335';
select sid,serial# from gv$session where sid='638';
alter system kill session '
Rman remaining time refresh .. Try this query..
-------------------------------------------------
set lines 200
col opname format a30
set pages 200
SELECT USERNAME,SID, SERIAL#, CONTEXT,SOFAR, TOTALWORK,ROUND(SOFAR/TOTALWORK*100,2)
"%_COMPLETE" FROM V$SESSION_LONGOPS WHERE OPNAME LIKE 'RMAN%' AND TOTALWORK != 0 AND SOFAR <> TOTALWORK;
set lines 200
col opname format a30
set pages 200
SELECT USERNAME,SID, SERIAL#, CONTEXT,SOFAR, TOTALWORK,ROUND(SOFAR/TOTALWORK*100,2)
"%_COMPLETE" FROM V$SESSION_LONGOPS WHERE OPNAME LIKE 'RMAN%' AND TOTALWORK != 0 AND SOFAR <> TOTALWORK;
=============================
all sql text running in database::
==================================
select sesion.sid,
sesion.username,
optimizer_mode,
hash_value,
address,
cpu_time,
elapsed_time,
sql_text
from v$sqlarea sqlarea, v$session sesion
where sesion.sql_hash_value = sqlarea.hash_value
and sesion.sql_address = sqlarea.address
and sesion.username is not null
/
select sid, serial#,SQL_ADDRESS, status,PREV_SQL_ADDR from v$session where sid='xxxx' //(enter the sid value)
select sql_text from v$sqltext where address='XXXXXXXX';
==================================================================================================================
imruat(winsp)
SET MARKUP HTML ON
SPOOL C:\TEMP\MYOUTPUT.XLS
SQL QUERY
SPOOL OFF
=================================================================================================================================================================
query to find only perticular lines from one time to another time in alertlog file .
awk '/May 21/,/May 22/' alert_OPPMPRD.log | sed '$d' > /tmp/May21_alert.log
==================================================================================================================
CDW_CLASS.CDC_CONTROL_INS_UPD_TRIGGER
select name,open_mode from v$database;
select OWNER ,TRIGGER_NAME, STATUS from ALL_TRIGGERS where TRIGGER_NAME like '%CDC_CONTROL_INS_UPD_TRIGGER%'
ALTER TRIGGER CDW_CLASS.CDC_CONTROL_INS_UPD_TRIGGER ENABLE;
====================================
********************************** Materlized Views Refresh ********************
SELECT MVIEW_NAME, STALENESS, LAST_REFRESH_TYPE, COMPILE_STATE FROM DBA_MVIEWS ORDER BY MVIEW_NAME;
select MVIEW_NAME from all_mviews where MVIEW_NAME like '%MV_CLAIM_STATUS%' ;
select MVIEW_NAME , REFRESH_MODE ,LAST_REFRESH_DATE ,LAST_REFRESH_TYPE from dba_mviews where MVIEW_NAME like '%MV_CLAIM_STATUS%' +MV_BROKER_CONTACT_DETAILS
===Lingeswari
SQL> select object_name,owner,object_type from dba_objects where object_name='MV_BROKER_CONTACT_DETAILS';
SQL> exec dbms_mview.refresh('CDW.MV_BROKER_CONTACT_DETAILS','C');
EXEC DBMS_MVIEW.refresh('EMP_MV');
EXECUTE DBMS_MVIEW.REFRESH('CDW.MV_CLAIM_STATUS','C');
*******************************************************
Refresh Option Description
COMPLETE Refreshes by recalculating the materialized view's defining query.
FAST Applies incremental changes to refresh the materialized view using the information logged in the materialized view logs, or from a SQL*Loader direct-path or a partition maintenance operation.
FORCE Applies FAST refresh if possible; otherwise, it applies COMPLETE refresh.
NEVER Indicates that the materialized view will not be refreshed with refresh mechanisms.
=================================================
Triggers :
select OWNER ,TRIGGER_NAME, STATUS from ALL_TRIGGERS where TRIGGER_NAME like '%AW_DROP_TRG%';
ALTER TRIGGER CDW_CLASS.CDC_CONTROL_INS_UPD_TRIGGER ENABLE;
ALTER TRIGGER CDW_CLASS.CDC_CONTROL_INS_UPD_TRIGGER DISABLE;
get DDL of Triggers:
select trigger_body from user_triggers where trigger_name like '%CDC%';
SELECT DBMS_METADATA.GET_DDL('TABLE',u.table_name)
FROM USER_TABLES u;
========================================
========> Flash Back issue <=============
SQL> show parameter flashback
SQL> select FLASHBACK_ON from v$database;
SQL> show parameter db_recovery;
SQL> select space_used/(1024*1024*1024),space_limit/(1024*1024*1024) from v$recovery_file_dest;
SQL> select * from v$flash_recovery_area_usage;
========================> INDEX INFO <===========================
************ INDEX *********
select table_name, index_name from dba_ind_columns where table_owner='CLAIM_EVENT' order by table_name;
SELECT table_name , owner FROM user_tables where table_name='CLAIM_EVENT';
select INDEX_NAME,TABLE_OWNER,TABLE_NAME,LAST_ANALYZED FROM DBA_INDEXES WHERE INDEX_NAME='PROCESS_REQUEST_SID_IDX';
select INDEX_NAME,TABLE_OWNER,TABLE_NAME,LAST_ANALYZED , STATUS FROM DBA_INDEXES WHERE INDEX_NAME='CE_PROCESS_REQUEST_IDX1';
Gather stats::
exec dbms_stats.gather_table_stats(ownname=>'CLAIM_EVENT',tabname=>'CLAIM_EVENT',estimate_percent =>dbms_stats.auto_sample_size,no_invalidate=>FALSE,cascade=>true,degree=>8);
===================================================================================================================================================================
======> clmtsa , clmtsd , clmprddr1 Bounce Activity <=========================
CLMTSD , CLMTSA +ASM => srvctl start asm
CLMTSA =>
cat /var/opt/oracle/oratab
/opt/xts/sudo/bin/sudo -u oracle -i bash
/opt/xts/sudo/bin/sudo -u grid -i bash
lsnrctl status LISTENER
1) Stop DB
select name,open_mode,database_role,log_mode,to_char(current_scn) from v$database;
SQL> shut immediate;
2) OEM DOWN ==> cd /u01/app/oracle/agent12c/core/12.1.0.5.0/bin/
CLMTSA => ./emctl stop agent
./emctl status agent
Check Cluster as grid user ::
==> cd /u01/app/grid/product/11.2.0/grid
CLMTSA => ./crsctl stat res -t
3) Stop ASM Than Stop cluster
Connect as Grid user ::
CLMTSA => . oraenv
ORACLE_SID = [grid] ? +ASM
===> cd /u01/app/grid/product/11.2.0/grid/bin
=> ./crsctl stat res -t
Stop ASM
sqlplus / as sysasm
SQL> select instance_name,status from v$instance;
SQL> shut immediate;
CLMTSA => crsctl stop has
CLMTSA => prstat -a ( No ORACLE & GRID process should not run)
================================================== STOPPED ============================
CLMPRDDR1 => Standby DOWN ( # MRP Should be stop)
SQL> select process,status,sequence# from v$managed_standby;
SQL> shut immediate;
=> ./emctl stop agent
CLMPRDDR1 => sqlplus / as sysasm
SQL> select instance_name,status from v$instance;
INSTANCE_NAME STATUS
---------------- ------------
+ASM STARTED
SQL> shutdown immediate;
CLMPRDDR1 => ./crsctl stat res -t
CLMPRDDR1 => crsctl stop has
$prstat -a
===============================================================================================
START PROCESS
======================================================================================================
1) Start Cluster
2) Start ASM
3) Start DATABASE
4) Start OEM Agent
5) Check LISTENER STATUS
CLMPRDDR1 => ./crsctl start has
CLMPRDDR1 => ./crsctl stat res -t
CLMPRDDR1 => sqlplus / as sysasm
ASM SQL> select INSTANCE_NAME,HOST_NAME,STATUS,STARTUP_TIME from v$instance;
DB UP ==> SQL> startup mount;
SELECT THREAD#, MAX(SEQUENCE#) AS "LAST_APPLIED_LOG" FROM V$LOG_HISTORY GROUP BY THREAD#;
set head off
set echo off
set feedback off
SELECT (ARCH.SEQUENCE# - APPL.SEQUENCE#) difference
FROM
(SELECT DISTINCT THREAD# ,SEQUENCE# FROM V$ARCHIVED_LOG WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$ARCHIVED_LOG GROUP BY THREAD#)) ARCH
,
(SELECT DISTINCT THREAD# ,SEQUENCE# FROM V$LOG_HISTORY WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$LOG_HISTORY GROUP BY THREAD#)) APPL
WHERE
ARCH.THREAD# = APPL.THREAD#
ORDER BY 1;
MRP Check :
SQL> select process,status,sequence# from v$managed_standby;
MPR Start :
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;
SQL> select process,status,sequence# from v$managed_standby;
CLMPRDDR1 => lsnrctl status LISTNER
CLMPRDDR1 => ./emctl start agent
*ASM Checks ::
==========
sqlplus / as sysasm
SQL> select name Diskgroup,round(total_mb/1024,2) "Total_TB",round(free_mb/1024,2) "Free_GB",round(((free_mb/total_mb)*100),2) "Available%" from v$asm_diskgroup;
SQL> select name,state,GROUP_NUMBER from v$asm_Diskgroup;
================================> STANDBY ARCHIVE CHECKS <=================================
SOURCE_DIR="/u03/orarch/imrprd/"
DEST_DIR="/u04/prd_arch_logs/"
crontab -l|grep dr
RDCPRD
PBSPRD
========================= REDO PER DAY + ARCHIVES PER DAY =================================
REDO:
SELECT A.*, ROUND (A.Count# * B.AVG# / 1024 / 1024) Daily_Avg_Mb
FROM ( SELECT TO_CHAR (First_Time, 'YYYY-MM-DD') DAY,
COUNT (1) Count#,
MIN (RECID) Min#,
MAX (RECID) Max#
FROM v$log_history
GROUP BY TO_CHAR (First_Time, 'YYYY-MM-DD')
ORDER BY 1 DESC) A, (SELECT AVG (BYTES) AVG#,
COUNT (1) Count#,
MAX (BYTES) Max_Bytes,
MIN (BYTES) Min_Bytes
FROM v$log) B;
DAY COUNT# MIN# MAX# DAILY_AVG_MB
--------------------------------------------------------------------------- ---------- ---------- ---------- ------------
2018-10-04 155 173138 173292 31000
2018-10-03 375 172763 173137 75000
2018-10-02 182 172581 172762 36400
2018-10-01 188 172393 172580 37600
2018-09-30 186 172207 172392 37200
2018-09-29 186 172021 172206 37200
2018-09-28 185 171836 172020 37000
2018-09-27 260 171576 171835 52000
2018-09-26 186 171390 171575 37200
2018-09-25 190 171200 171389 38000
2018-09-24 187 171013 171199 37400
2018-09-23 85 170928 171012 17000
2018-09-22
========
SELECT THREAD#, MAX(SEQUENCE#) AS "LAST_APPLIED_LOG" FROM V$LOG_HISTORY GROUP BY THREAD#;
SELECT SUM_ARCH.DAY,
SUM_ARCH.GENERATED_MB,
SUM_ARCH_DEL.DELETED_MB,
SUM_ARCH.GENERATED_MB - SUM_ARCH_DEL.DELETED_MB "REMAINING_MB"
FROM ( SELECT TO_CHAR (COMPLETION_TIME, 'DD/MM/YYYY') DAY,
SUM (ROUND ( (blocks * block_size) / (1024 * 1024*1024), 2))
GENERATED_MB
FROM V$ARCHIVED_LOG
WHERE ARCHIVED = 'YES'
GROUP BY TO_CHAR (COMPLETION_TIME, 'DD/MM/YYYY')) SUM_ARCH,
( SELECT TO_CHAR (COMPLETION_TIME, 'DD/MM/YYYY') DAY,
SUM (ROUND ( (blocks * block_size) / (1024 * 1024*1024), 2))
DELETED_MB
FROM V$ARCHIVED_LOG
WHERE ARCHIVED = 'YES' AND DELETED = 'YES'
GROUP BY TO_CHAR (COMPLETION_TIME, 'DD/MM/YYYY')) SUM_ARCH_DEL
WHERE SUM_ARCH.DAY = SUM_ARCH_DEL.DAY(+)
ORDER BY TO_DATE (DAY, 'DD/MM/YYYY');
18/06/2018 122.58 41.32 81.26
19/06/2018 210.51 69.71 140.8
20/06/2018 336.63 112.21 224.42
21/06/2018 297.39 99.13 198.26
22/06/2018 300.27 100.09 200.18
23/06/2018 128.28 42.76 85.52
24/06/2018 127.8 42.6 85.2
25/06/2018 346.02 115.34 230.68
*************************************************** STANDBY CHECK *********************************************************************************************************
SQL> select name, open_mode, database_role from v$database;
select max(sequence#) from v$log_history;
Check redo received and applied on standby:
==========================================
SELECT SEQUENCE#, APPLIED FROM V$ARCHIVED_LOG;
SELECT THREAD# ,SEQUENCE# FROM V$ARCHIVED_LOG WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$ARCHIVED_LOG GROUP BY THREAD#) ORDER BY 1;
Identify the missing archive log file.
SELECT THREAD#, LOW_SEQUENCE#, HIGH_SEQUENCE# FROM V$ARCHIVE_GAP;
MRP CHECKS ::
============
set head off
set echo off
set feedback off
SELECT (ARCH.SEQUENCE# - APPL.SEQUENCE#) difference
FROM
(SELECT DISTINCT THREAD# ,SEQUENCE# FROM V$ARCHIVED_LOG WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$ARCHIVED_LOG GROUP BY THREAD#)) ARCH
,
(SELECT DISTINCT THREAD# ,SEQUENCE# FROM V$LOG_HISTORY WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$LOG_HISTORY GROUP BY THREAD#)) APPL
WHERE
ARCH.THREAD# = APPL.THREAD#
ORDER BY 1;
************************************************************************************************************************************************************
===> Befor Db up checklist :: <=========================
Pande, Deepak:
before opening in mount mode run below in database
select * from v$recover_file;
select distinct(status) from dba_tablespaces;
select distinct(status) from dba_data_files;
select name from v$datafile where status ='OFFLINE'
all tablespace should be online
all datafiles should be AVAILABLE
If v$recover_file returns some output then recovery is needed
If v$recover_file returns some output then recovery is needed
for recovery at sql prompt give recover database;
select * from dba_tablespace_usage_metrics;
select instance_name, to_char(startup_time,'mm/dd/yyyy hh24:mi:ss') as startup_time from v$instance;
select * from v$recover_file;
select distinct(status) from dba_tablespaces;
select distinct(status) from dba_data_files;
select name from v$datafile where status ='OFFLINE'
=============
====================================================== TNS ISSUES ================================================
* DB and Listener check alert log also.
1) ping 10.145.2.146 , ping actwf2tscdatz01
actwf2tscdatz01 is alive
2) telnet actwf2tscdatz01 1521
3) tnsping wfltsc
4)
SQL> connect XCS_WORKFLOW/UK_xcs#1@'(DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = actwf2tscdatz01)(PORT = 1521)))
(CONNECT_DATA = (SERVER =DEDICATED) (SERVICE_NAME = wfltsc.xchanging.com)))'
Connected.
SQL>
connect ETL_CWT/e7tc77@'(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=basimrfofdatz01)(PORT=1521)))'
=====================
================== DB GROWTH RATE (DAILY + WEEKLY) ======
SET LINESIZE 200
SET PAGESIZE 200
COL "Database Size" FORMAT a13
COL "Used Space" FORMAT a11
COL "Used in %" FORMAT a11
COL "Free in %" FORMAT a11
COL "Database Name" FORMAT a13
COL "Free Space" FORMAT a12
COL "Growth DAY" FORMAT a11
COL "Growth WEEK" FORMAT a12
COL "Growth DAY in %" FORMAT a16
COL "Growth WEEK in %" FORMAT a16
SELECT
(select min(creation_time) from v$datafile) "Create Time",
(select name from v$database) "Database Name",
ROUND((SUM(USED.BYTES) / 1024 / 1024 ),2) || ' MB' "Database Size",
ROUND((SUM(USED.BYTES) / 1024 / 1024 ) - ROUND(FREE.P / 1024 / 1024 ),2) || ' MB' "Used Space",
ROUND(((SUM(USED.BYTES) / 1024 / 1024 ) - (FREE.P / 1024 / 1024 )) / ROUND(SUM(USED.BYTES) / 1024 / 1024 ,2)*100,2) || '% MB' "Used in %",
ROUND((FREE.P / 1024 / 1024 ),2) || ' MB' "Free Space",
ROUND(((SUM(USED.BYTES) / 1024 / 1024 ) - ((SUM(USED.BYTES) / 1024 / 1024 ) - ROUND(FREE.P / 1024 / 1024 )))/ROUND(SUM(USED.BYTES) / 1024 / 1024,2 )*100,2) || '% MB' "Free in %",
ROUND(((SUM(USED.BYTES) / 1024 / 1024 ) - (FREE.P / 1024 / 1024 ))/(select sysdate-min(creation_time) from v$datafile),2) || ' MB' "Growth DAY",
ROUND(((SUM(USED.BYTES) / 1024 / 1024 ) - (FREE.P / 1024 / 1024 ))/(select sysdate-min(creation_time) from v$datafile)/ROUND((SUM(USED.BYTES) / 1024 / 1024 ),2)*100,3) || '% MB' "Growth DAY in %",
ROUND(((SUM(USED.BYTES) / 1024 / 1024 ) - (FREE.P / 1024 / 1024 ))/(select sysdate-min(creation_time) from v$datafile)*7,2) || ' MB' "Growth WEEK",
ROUND((((SUM(USED.BYTES) / 1024 / 1024 ) - (FREE.P / 1024 / 1024 ))/(select sysdate-min(creation_time) from v$datafile)/ROUND((SUM(USED.BYTES) / 1024 / 1024 ),2)*100)*7,3) || '% MB' "Growth WEEK in %"
FROM (SELECT BYTES FROM V$DATAFILE
UNION ALL
SELECT BYTES FROM V$TEMPFILE
UNION ALL
SELECT BYTES FROM V$LOG) USED,
(SELECT SUM(BYTES) AS P FROM DBA_FREE_SPACE) FREE
GROUP BY FREE.P;
=====
=================================================== ASM DISK ADD ===============================================
for this u need to connect as grid user
select name,PATH,HEADER_STATUS from v$asm_disk order by path;
alter diskgroup DATA add disk '/dev/asm/oradata32' name DATA_0032 rebalance power 10;
SELECT MOUNT_STATUS,HEADER_STATUS,MODE_STATUS,STATE,OS_MB,TOTAL_MB,FREE_MB,NAME,PATH FROM V$ASM_DISK;
==> select OPERATION, STATE, POWER, SOFAR, EST_WORK, EST_RATE, EST_MINUTES from V$ASM_OPERATION;
==========================
^^^^^^^^^^ PACKAGE not PACKAGEBODY ^^^^^^^^^^^^
SQL> select dbms_metadata.get_ddl('PACKAGE','PKG_CDW_BATCH_JOB','CDW_BATCH') FROM DUAL;
DBMS_METADATA.GET_DDL('PACKAGE','PKG_CDW_BATCH_JOB','CDW_BATCH')
--------------------------------------------------------------------------------
CREATE OR REPLACE PACKAGE "CDW_BATCH"."PKG_CDW_BATCH_JOB"
-- +--------------
------------------------------------------------------------------------------------------------------
++++++========================== OEM AGENT ERROR ===========================
stop agent
mv /u01/app/oracle/agent12c/agent_inst/sysman/emd/state/* /tmp/BPOEMBKP/
$ ./emctl clearstate agent
1. Shutdown the agent or kill any leftover processes
$ $AGENT_HOME/agent_inst/bin/emctl stop agent
$ ps -ef | grep java | grep '<agent based dir>'
$ ps -ef | grep perl
$ kill -9 <Process id>
2. Move old files from $AGENT_HOME/agent_inst/sysman/emd/state/* to a new directory
e.g
$ mv $AGENT_HOME/agent_inst/sysman/emd/state/* /u01/tmp/
3. Execute clearstate agent:
$ $AGENT_HOME/agent_inst/bin/emctl/emctl clearstate agent
4. Start the agent:
$AGENT_HOME/agent_inst/bin/emctl/emctl start agent
============================ EXPORT SCHEMA ===============
SQL> CREATE OR REPLACE DIRECTORY NEW_PUMP AS '/u02/orabackup/pump';
SQL> GRANT READ, WRITE ON DIRECTORY NEW_PUMP TO system;
>select DIRECTORY_NAME ,DIRECTORY_PATH from dba_directories;
PUMP /u02/orabackup/pump
nohup expdp \"/ as sysdba\" DIRECTORY=PUMP dumpfile=CDW_CLASS_INCREMENTAL_expdp.dmp
logfile=CDW_CLASS_INCREMENTAL_expdp.log schemas=CDW,CDW_CLASS_INCREMENTAL &
*****************************************************************************************************************************
The correct mount options that need to be used for NFS volumes on Linux are:
rsize=32k, wsize=32k, hard, actimeo=0
==============Schema refresh ==================
SELECT 'DROP ' || OBJECT_TYPE || ' ' || OWNER || '.' || OBJECT_NAME || ';'
FROM DBA_OBJECTS
WHERE OWNER = 'CDW_CLASS_INCREMENTAL' and OBJECT_TYPE <> 'DATABASE LINK';
Below are the query to find out the tablespace name and datafiles name and size.
set linesize 300
set pagesize 100
col file_name for a50
tablespace_name for a20
select distinct TABLESPACE_NAME from dba_segments where owner='<schema name>';
select tablespace_name,file,size/1024/1024/1024 "SIZE_IN_GB" from dba_data_files where tablespace_name='<tablespace name>';
Create the same name of tablespace with datafile and size on target database before import
Please start the import
Once import completed, please compare the object count on source and target database for validation.
SELECT OBJECT_TYPE, STATUS, COUNT(*) FROM DBA_OBJECTS;
The best way I believe is the take a spool of the grants and execute them once after the import is complete
==> ROLES
select 'grant '||PRIVILEGE||' on '||OWNER||'.'||TABLE_NAME||' to XXTEST;' from dba_tab_privs where GRANTEE='XXTEST' ;
===============================================
SQL> ALTER SYSTEM SET COMPATIBLE = '9.0.0' SCOPE=SPFILE;
=================================RESTORING DATABASE to GRP / ROLLBACK DB TO GRP==========================================
Flashback to the guaranteed restore point
SQL> select current_scn from v$database;
SQL> shutdown immediate;
SQL> startup mount;
SQL> select * from v$restore_point;
SQL> flashback database to restore point GRP_NAME;
SQL> alter database open resetlogs;
SQL> select current_scn from v$database;
=======================================
Recyclebin:
=====
check owner is correct or not .
select OWNER,OBJECT_NAME,ORIGINAL_NAME,OPERATION,CREATETIME ,DROPTIME from dba_recyclebin where ORIGINAL_NAME like 'CWT_ETL_TBL_1HR_1' owner='ETL_CWT';
===============
Use the below steps before shutdown that clmtrn database tomorrow
First, back up the crontab before shutdown the database
crontab -l > my_cron_backup.txt
Then you can empty it:
crontab -r
To restore the cron after starting the database
crontab my_cron_backup.txt
crontab -l
===================================================
============== DATAFILES RESIZE ===================
SQL> select 'alter database datafile '''||file_name||''' resize ' ||
ceil( (nvl(hwm,1)*&&blksize)/1024/1024 ) || 'm;' cmd
from dba_data_files a,
( select file_id, max(block_id+blocks-1) hwm
from dba_extents
group by file_id ) b
where a.file_id = b.file_id(+)
and ceil( blocks*&&blksize/1024/1024) -
ceil( (nvl(hwm,1)*&&blksize)/1024/1024 ) > 0
/
> sho praemeter block_size
============================
ROW COUNT + OBJECTS COUNT for TSC
=======================================================
> row count
set time on pages 1000
select OWNER,TABLE_NAME,num_rows COUNTER from dba_tables where owner not in
('APEX_030200','SQLTXADMIN','SYSMAN','SCOTT','OLAPSYS','MDSYS','ORDSYS','XDB','CTXSYS','SYS','SYSTEM','OWBSYS','ORACLE_OCM','PUBLIC','APPQOSSYS','EXFSYS','OUTLN','OPS$ORACLE','ORDPLUGINS','DBSNMP','ORDDATA','MIST','WMSYS','SQLTXPLAIN','PERFSTAT')
order by owner,TABLE_NAME;
+++++++++++++++
> object count :
select owner,object_type,count(*),status from dba_objects group by owner,object_type,status order by 1;
select owner,object_type,count(*),status from dba_objects where owner not in
('APEX_030200','SQLTXADMIN','SYSMAN','OLAPSYS','MDSYS','ORDSYS','XDB','CTXSYS','SYS','SYSTEM','OWBSYS','ORACLE_OCM','PUBLIC','APPQOSSYS','EXFSYS','OUTLN','OPS$ORACLE','ORDPLUGINS','DBSNMP','ORDDATA','MIST','WMSYS','SQLTXPLAIN')
group by owner,object_type,status order by 1;
===============================================
================= EXPLIAN PLAN ====================
explain plan for select nvl(min(tblclaimco0_.COMMENT_TIMESTAMP), SYSDATE) as col_0_0_ from REPOSITORY.TBLCLAIMCOMMENTHEADER tblclaimco0_;
select plan_table_output
from table(dbms_xplan.display('plan_table',null,'ALL'));
STALE INDEXES:
select stale_stats,last_analyzed from dba_ind_statistics where table_owner='REPOSITORY' and table_name='TBLCLAIMCOMMENTHEADER' ;
select * from DBA_TAB_COL_STATISTICS where owner ='REPOSITORY' and table_name='TBLCLAIMCOMMENTHEADER';
select table_name, OWNER, stale_stats, last_analyzed from dba_tab_statistics where stale_stats='YES' and OWNER='REPOSITORY';
===================== LOAD TEST ===================
========== final query =========
set lines 180
set echo on
set colsep '|'
set long 399999
set lines 300 pages 300
col sql_text for a78
col USERNAME for a15
SELECT a.sql_text, b.sid,b.username,b.status,b.LAST_CALL_ET active_time_sec FROM v$sqltext a, v$session b
WHERE a.address = b.sql_address
AND a.hash_value = b.sql_hash_value AND b.status='ACTIVE' ORDER BY b.sid,a.piece;
==================== FINAL BY DEEPAK =================
select LAST_LOAD_TIME, ELAPSED_TIME/1000000, MODULE, SQL_TEXT ,LAST_ACTIVE_TIME from v$sql where LAST_LOAD_TIME like '%2019-01-04%'
order by LAST_LOAD_TIME desc;
====================================================== Last Inserts in db ========================
desc all_tab_modifications ==> inserts updates , delete
select TABLE_NAME,INSERTS,to_char(TIMESTAMP,'DD:MM:YY hh24:mi:ss') time from all_tab_modifications where TABLE_NAME='CLAIM_EVENT_PROCESS_REQUEST';
============================
===================== STATS GATHER WITH BACKUP =============
--create table for stat's backup
begin
dbms_stats.create_stat_table(ownname => 'SYSTEM',stattab => 'bkp_stats_181218_TBLCLAIM',tblspace => 'USERS');
end;
/
--taking backup of current stats of table
begin
dbms_stats.export_table_stats(ownname=>'REPOSITORY',tabname=>'TBLCLAIMCOMMENTHEADER',stattab => 'bkp_stats_181218_TBLCLAIM',statid => 'repos_tblclaim',statown => 'SYSTEM',cascade=>true);
end;
/
--gather stats
EXEC SYS.DBMS_STATS.GATHER_TABLE_STATS( 'repository','TBLCLAIMCOMMENTHEADER', CASCADE => TRUE ,degree => 47,estimate_percent =>DBMS_STATS.AUTO_SAMPLE_SIZE, method_opt => 'FOR ALL COLUMNS SIZE AUTO');
********************************************* IO Contention PT ***********************************************************
in AWR IO STATS ALSO GIVE SOME IDEA:
desc v$filestat + iostat + vol_grp
> select name,PHYRDS,PHYWRTS,READTIM,WRITETIM from v$filestat a , v$datafile b where a.file# = b.file# order by READTIM desc;
NAME PHYRDS PHYWRTS READTIM WRITETIM
------------------------------------------------------------ ---------- ---------- ---------- ----------
+DATA/ibiuat/datafile/undotbs1.258.949962237 2049374 25773291 5281946 9859863
+DATA/ibiuat/datafile/cdw_warehouse_indx.284.949966409 2936133 472456 5215601 2312
+DATA/ibiuat/datafile/cdw_warehouse_data.274.949964921 6159937 175348 5204899 4285
+DATA/ibiuat/datafile/cdw_warehouse_data.270.949964289 6696958 177707 5182428 4698
===================================== CRONTAB DISABLE ==============
Use the below steps before shutdown that clmprd database tomorrow
First, back up the crontab before shutdown the database
crontab -l > my_cron_backup.txt
Then you can empty it:
crontab -r
To restore the cron after starting the database
crontab my_cron_backup.txt
crontab -l
===================
!!!!!!!!!!!!!" IMRPRD:- XAG_DATA'S TABLES & TABLESPACE GROWTH DETAILS !!!!!!!!!!!!
---
--
--
set colsep '|'
column segment_name format a35
COLUMN allocated_bytes FORMAT a25
COLUMN USED_MB FORMAT a25
COLUMN FREE_MB FORMAT a25
COLUMN TOTAL_MB FORMAT a25
COLUMN per_free FORMAT a25
COLUMN tablespace FORMAT a25
set pagesize 200;
set linesize 200;
set lines 200;
set pages 200;
col owner format a12;
col segment_name format a35
col segment_type format a15;
col tablespace_name format a20;
-- SIZE OF SEGMENT
--================
select sysdate,owner, TABLESPACE_NAME,segment_name,sum(bytes/1024/1024) MB from dba_Segments where segment_type='TABLE' AND TABLESPACE_name='XAG_DATA' group by owner,sysdate,segment_name,tablespace_name
order by segment_name;
--SIZE OF TABLESPACE
--==================
SELECT F.TABLESPACE_NAME tablespace,
TO_CHAR ((T.TOTAL_SPACE - F.FREE_SPACE),'999999,999999') "USED_MB",
TO_CHAR (F.FREE_SPACE, '999999,999999') "FREE_MB",
TO_CHAR (T.TOTAL_SPACE, '999999,999999') "TOTAL_MB",
TO_CHAR ((ROUND ((F.FREE_SPACE/T.TOTAL_SPACE)*100)),'9999999')||' %' PER_FREE
FROM (
SELECT TABLESPACE_NAME,
ROUND (SUM (BLOCKS*(SELECT VALUE/1024
FROM V\\$PARAMETER
WHERE NAME = 'db_block_size')/1024)
) FREE_SPACE
FROM DBA_FREE_SPACE
GROUP BY TABLESPACE_NAME
) F,
(
SELECT TABLESPACE_NAME,
ROUND (SUM (BYTES/1048576)) TOTAL_SPACE
FROM DBA_DATA_FILES
GROUP BY TABLESPACE_NAME
) T
WHERE F.TABLESPACE_NAME = T.TABLESPACE_NAME and t.tablespace_name='XAG_DATA';
spool off;
=================================
********************************************************** DATA GUARD **************************************************************************************************
primay : SELECT MESSAGE FROM V$DATAGUARD_STATUS;
SELECT to_char(CURRENT_SCN) FROM V$DATABASE;
SQL> select name, open_mode, database_role from v$database;
select max(sequence#) from v$log_history;
Check redo received and applied on standby:
==========================================
SELECT SEQUENCE#, APPLIED FROM V$ARCHIVED_LOG;
SELECT SEQUENCE#, APPLIED ,COMPLETION_TIME ,STAMP FROM V$ARCHIVED_LOG where COMPLETION_TIME like '%06-JUL-18%';
SELECT THREAD# ,SEQUENCE# FROM V$ARCHIVED_LOG WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$ARCHIVED_LOG
GROUP BY THREAD#) ORDER BY 1;
select process,status,sequence# from v$managed_standby;
Identify the missing archive log file.
SELECT THREAD#, LOW_SEQUENCE#, HIGH_SEQUENCE# FROM V$ARCHIVE_GAP;
SQL> select thread#, max (sequence#) from v$archived_log where APPLIED='YES' group by thread#;
THREAD# MAX(SEQUENCE#)
———- ————–
1 22
2 14
3 12
************************************************************************************************************************************************************
Crosscheck this :
Check redo received and applied on standby.
SELECT ARCH.THREAD# , ARCH.SEQUENCE# , APPL.SEQUENCE# , (ARCH.SEQUENCE# – APPL.SEQUENCE#) FROM
(SELECT THREAD# ,SEQUENCE# FROM V$ARCHIVED_LOG WHERE (THREAD#,FIRST_TIME ) IN
(SELECT THREAD#,MAX(FIRST_TIME) FROM V$ARCHIVED_LOG GROUP BY THREAD#)) ARCH,
(SELECT THREAD# ,SEQUENCE# FROM V$LOG_HISTORY WHERE (THREAD#,FIRST_TIME ) IN
(SELECT THREAD#,MAX(FIRST_TIME) FROM V$LOG_HISTORY GROUP BY THREAD#)) APPL
WHERE
ARCH.THREAD# = APPL.THREAD#
ORDER BY 1;
Identify the missing archive log file.
SELECT THREAD#, LOW_SEQUENCE#, HIGH_SEQUENCE# FROM V$ARCHIVE_GAP;
col NAME for a70
select SEQUENCE#,THREAD#,STATUS,APPLIED,NAME from v$archived_log where APPLIED='YES' order by NAME;
col NAME for a70
select SEQUENCE#,THREAD#,STATUS,APPLIED,NAME from v$archived_log where APPLIED='NO';
col NAME for a70
select SEQUENCE#,THREAD#,STATUS,APPLIED,NAME from v$archived_log where APPLIED='YES' AND SEQUENCE# BETWEEN 80886 AND 81999;
use first query
=========================
set head off
set echo off
set feedback off
SELECT (ARCH.SEQUENCE# - APPL.SEQUENCE#) difference
FROM
(SELECT DISTINCT THREAD# ,SEQUENCE# FROM V$ARCHIVED_LOG WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$ARCHIVED_LOG GROUP BY THREAD#)) ARCH
,
(SELECT DISTINCT THREAD# ,SEQUENCE# FROM V$LOG_HISTORY WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$LOG_HISTORY GROUP BY THREAD#)) APPL
WHERE
ARCH.THREAD# = APPL.THREAD#
ORDER BY 1;
=================== ARCH Difference query================================
SELECT ARCH.THREAD# "Thread", ARCH.SEQUENCE# "Last Sequence Received",
APPL.SEQUENCE# "Last Sequence Applied", (ARCH.SEQUENCE# - APPL.SEQUENCE#) "Difference"
FROM
(SELECT THREAD# ,SEQUENCE# FROM V$ARCHIVED_LOG WHERE (THREAD#,FIRST_TIME ) IN
(SELECT THREAD#,MAX(FIRST_TIME) FROM V$ARCHIVED_LOG GROUP BY THREAD#)) ARCH,
(SELECT THREAD# ,SEQUENCE# FROM V$LOG_HISTORY WHERE (THREAD#,FIRST_TIME ) IN
(SELECT THREAD#,MAX(FIRST_TIME) FROM V$LOG_HISTORY GROUP BY THREAD#)) APPL
WHERE
ARCH.THREAD# = APPL.THREAD#
ORDER BY 1;
==========================================
errors
select status , error from v$archive_dest where des_id=2;
select process,client_process,status,thread#,sequence#,block#,blocks from v$managed_standby;
SQL> select process,client_process,status,thread#,sequence#,block#,blocks from v$managed_standby;
PROCESS CLIENT_P STATUS THREAD# SEQUENCE# BLOCK# BLOCKS
--------- -------- ------------ ---------- ---------- ---------- ----------
ARCH ARCH CONNECTED 0 0 0 0
ARCH ARCH CONNECTED 0 0 0 0
ARCH ARCH CONNECTED 0 0 0 0
ARCH ARCH CONNECTED 0 0 0 0
MRP0 N/A WAIT_FOR_LOG 1 169866 0 0
RFS LGWR RECEIVING 1 169990 242070 8
RFS ARCH IDLE 0 0 0 0
SELECT SEQUENCE#, APPLIED FROM V$ARCHIVED_LOG where APPLIED='NO' ;
alter database recover managed standby database cancel
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE disconnect from session
alter database recover managed standby database disconnect from session;
============ REAL TIME APPLY =======
select DEST_ID,dest_name,status,type,srl,recovery_mode from v$archive_dest_status where dest_id=1;
select database_role , open_mode from v$database;
DATABASE_ROLE
----------------
PHYSICAL STANDBY
SQL> select name ,open_mode from v$database;
NAME OPEN_MODE
--------- --------------------
WFLPRD READ ONLY WITH APPLY
SQL> select dest_id , recovery_mode from v$archive_dest_status where dest_id=1;
DEST_ID RECOVERY_MODE
---------- -----------------------
1 MANAGED
===========================
