ADD Datafile
============
alter tablespace P_G01_DATA_02 add datafile '/u01/data155/oradata/ODSPRD/P_G01_DATA_02_036.dbf' size 256m autoextend on next 256m maxsize 30G;
AWR interval checking
====================
set linesize 100
col snap_interval format a20
col retention format a20
col topnsql format a20
select * from dba_hist_wr_control;
Get all the nodes:
-----------------
sh-3.2$ . grid.env
sh-3.2$ olsnodes
KILL SESISION
=============
SET LINESIZE 100
COLUMN spid FORMAT A10
COLUMN username FORMAT A10
COLUMN program FORMAT A45
SELECT s.inst_id,
s.sid,
s.serial#,
p.spid,
s.username,
s.program
FROM gv$session s
JOIN gv$process p ON p.addr = s.paddr AND p.inst_id = s.inst_id
WHERE s.type != 'BACKGROUND';
SQL> ALTER SYSTEM KILL SESSION 'sid,serial#';
SQL> ALTER SYSTEM KILL SESSION 'sid,serial#,@inst_id';
SQL> ALTER SYSTEM KILL SESSION 'sid,serial#' IMMEDIATE;
select 'alter system kill session '||sid||','||serial#||' immediate;' from gv$session where username like '%OSS_READ%'
mailx testing
=============
echo "test" | mailx -s "Sessions killed for" abc@cable.com
2PC transaction:
===============
SQL> select local_tran_id, global_tran_id, state,mixed,advice from dba_2pc_pending order by local_tran_id;
=====================================
TPP SR 3-9713538031 - Undo corruption
=====================================
GGS_MON QUERIES
===============
05,15,25,35,45,55 * * * * /ggs/scripts/ggs_mon_status.ksh > /ggs/scripts/ggs_mon_status.log_`hostnam
Select PROGRAM, STATUS, NAME From GGS_MON.GGS_MON Where Status not LIKE 'BLACKOUT' AND Status not LIKE 'RUNNING';
Select PROGRAM,NAME,LAG,CHKP From GGS_MON.GGS_MON Where Status not LIKE 'BLACKOUT' AND LAG > THRESHOLD_LAG OR CHKPT > THRESHOLD_CHKPT;
Select PROGRAM, NAME, LAST_UPDATED From GGS_MON.GGS_MON Where Status not LIKE 'BLACKOUT' AND SYSDATE - LAST_UPDATED > 1/24;
list cluster nodes:
===================
olsnodes -n
SYSMAN Pwd
==========
[pacdcappmon1::/u01/app/oracle/12.1.0.3/Middleware/oms/bin]$ ./emcli login -username=SYSMAN
Enter password :
Login successful
Have used the same password in pacdcappmon1.cable.com
Other password
sysman/obiwan102
--default dbsnmp passwd: m0n1t0r ..axbcast!
agent secure passwd: axbcast!
OCR BACKUP
==========
ocrconfig -showbackup
./olsnodes -n -i
Cluster status
==============
/u01/app/11.2.0.2/grid/bin > ./crsctl check crs
./crsctl stat res -t
crsctl check crs
./crsctl start crs
./crsctl query css votedisk
srvctl status service -d psymprcsf1
srvctl config service -s psymprcsfo -d psymprcsf1
./crsctl check cluster -all
Cluster log
/u01/app/11.2.0.3/grid/log/tdsdb-cmc-a3p/cssd/ocssd.log
ALTER SYSTEM SET service_names='TDS_ADM_RO','TDS_APP_CR_RO','TDS_APP_CR_RW','TDS_ETL_RW','DDP_APP_RO' SCOPE=MEMORY
ALTER SYSTEM SET service_names='DDP_APP_RO','TDS_ADM_RO','TDS_APP_CR_RO','TDS_APP_CR_RW','TDS_ETL_RW','WS_BILL_CR_RO' SCOPE=MEMORY SID='CTDSPRA3';
crsctl stop crs -f
DB status :
srvctl status database -d savdev1l -v
srvctl config service -d psymprop
srvctl status service -d psymprop
srvctl status service -d lmtprs2 -s LMT_RO
srvctl status scan_listener
Cluster Config backup
=====================
$GRID_HOME/bin/crsctl stat res -t
$GRID_HOME/bin/crsctl stat res -p
$GRID_HOME/bin/crsctl query css votedisk
$GRID_HOME/bin/ocrcheck
$GRID_HOME/bin/oifcfg getif
$GRID_HOME/bin/srvctl config nodeapps -a
$GRID_HOME/bin/srvctl config scan
$GRID_HOME/bin/srvctl config asm -a
$GRID_HOME/bin/srvctl config listener -l <listener-name> -a
$DB_HOME/bin/srvctl config database -d <dbname> -a
$DB_HOME/bin/srvctl config service -d <dbname> -s <service-name> -v
srvctl config service -d psymprjl -s psymprjl1fo
========================================================================================================================================================
| Heath Checking Queries |
========================================================================================================================================================
Finding Locked Objects :
*===============*
SELECT b.inst_id,b.session_id AS sid,NVL(b.oracle_username, ‘(oracle)’) AS username,a.owner AS object_owner,a.object_name,
Decode(b.locked_mode, 0, ‘None’,1, ‘Null (NULL)’,2, ‘Row-S (SS)’,3, ‘Row-X (SX)’,4, ‘Share (S)’,5, ‘S/Row-X (SSX)’,6, ‘Exclusive (X)’,
b.locked_mode) locked_mode,b.os_user_name
FROM dba_objects a, gv$locked_object b
WHERE a.object_id = b.object_id
ORDER BY 1, 2, 3, 4;
Display Long Operation :
*===============*
SELECT s.inst_id,
s.sid,
s.serial#,
s.username,
s.module,
ROUND(sl.elapsed_seconds/60) || ':' || MOD(sl.elapsed_seconds,60) elapsed,
ROUND(sl.time_remaining/60) || ':' || MOD(sl.time_remaining,60) remaining,
ROUND(sl.sofar/sl.totalwork*100, 2) progress_pct
FROM gv$session s,
gv$session_longops sl
WHERE s.sid = sl.sid
AND s.inst_id = sl.inst_id
AND s.serial# = sl.serial#;
Monitoring Memory:
*=============*
SELECT a.inst_id, NVL(a.username,’(oracle)’) AS username, a.module, a.program,
Trunc(b.value/1024) AS memory_kb
FROM gv$session a, gv$sesstat b, gv$statname c
WHERE a.sid = b.sid AND a.inst_id = b.inst_id AND b.statistic# = c.statistic#
AND b.inst_id = c.inst_id AND c.name = ‘session pga memory’
AND a.program IS NOT NULL
ORDER BY b.value DESC
Monitor Session Undo Information :
*=======================*
SELECT s.inst_id, s.username, s.sid, s.serial#, t.used_ublk, t.used_urec, rs.segment_name, r.rssize, r.status
FROM gv$transaction t, gv$session s, gv$rollstat r, dba_rollback_segs rs
WHERE s.saddr = t.ses_addr AND s.inst_id = t.inst_id AND t.xidusn = r.usn AND t.inst_id = r.inst_id AND rs.segment_id = t.xidusn
ORDER BY t.used_ublk DESC;
Monitoring Session Wait:
*===============*
SELECT s.inst_id, NVL(s.username, ‘(oracle)’) AS username, s.sid, s.serial#, sw.event, sw.wait_class, sw.wait_time, sw.seconds_in_wait, sw.state
FROM gv$session_wait sw, gv$session s
WHERE s.sid = sw.sid
AND s.inst_id = sw.inst_id
ORDER BY sw.seconds_in_wait DESC;
Monitoring Session RAC :
*=================*
SELECT NVL(s.username, ‘(oracle)’) AS username, s.inst_id, s.osuser, s.sid, s.serial#, p.spid, s.lockwait, s.status, s.module, s.machine, s.program,
TO_CHAR(s.logon_Time,’DD-MON-YYYY HH24:MI:SS’) AS logon_time
FROM gv$session s, gv$process p
WHERE s.paddr = p.addr AND s.inst_id = p.inst_id
ORDER BY s.username, s.osuser;
select count(s.status) "INACTIVE SESSIONS > 1HOUR "
from gv$session s, v$process p
where
p.addr=s.paddr and
s.last_call_et > 25200 and
s.status='INACTIVE';
At Database level: You have to monitor all the cluster logs, event logs, asm logs and rdbms logs
*===========================================================*
Cluster (ORA_CRS_HOME) and all related log files:
CRS alert log file
CRS logs: log/hostname/crsd
CSS logs: log/hostname/cssd
EVM logs: log/hostname/evmd & /log/hostname/evm/log
SRVM logs: log/hostname/client
OPMN logs: opmn/logs
Resource specific logs - /log/hostname/racg
ORACLE_HOME
Resource spec logs - /log/hostname/racg
SRVM logs- log/hostname/client
ASM
alert_SID.log : location: ORACLE_HOME/rdbms/log
Trace files:
bdump - background_dump_dest
cdump - core_dump_dest
udump - user_dump_dest
listener_<NODE>.log : ORACLE_HOME/network/log
=============================================================================================================================================================
ASM commands:
============
alter diskgroup <diskgroup name>
add disk '<new device physical name 1>', .., '<new device physical name N>' ====>>>>> adding disks belongs to new SAN
drop disk <old disk logical name 1>, <old disk logical name 2>, ..,<old disk logical name N> ==>>>>>>> droping the disks from old SAN
rebalance power <#>;
/etc/init.d/oracleasm listdisks
select GROUP_NUMBER,HEADER_STATUS,STATE,TOTAL_MB,FREE_MB,PATH from v$asm_disk where PATH='ORCL:LV_ASM05';
Rebalance time
==============;
select INST_ID, OPERATION, STATE, POWER, SOFAR, EST_WORK, EST_RATE, EST_MINUTES from GV$ASM_OPERATION;
PESPPRETDATA DISMOUNTED
ASM disk config queries :
=========================
SPOOL ASM_FIRST<instance#>.SPOOL ASM_FIRST<instance#>.HTML
SET MARKUP HTML ON
set echo on
set pagesize 200
alter session set nls_date_format='DD-MON-YYYY HH24:MI:SS';
select 'THIS ASM REPORT WAS GENERATED AT: ==)> ' , sysdate " " from dual;
select 'HOSTNAME ASSOCIATED WITH THIS ASM INSTANCE: ==)> ' , MACHINE " " from v$session where program like '%SMON%';
select * from v$asm_diskgroup;
SELECT * FROM V$ASM_DISK ORDER BY GROUP_NUMBER,DISK_NUMBER;
SELECT GROUP_NUMBER,DISK_NUMBER, NAME, TOTAL_MB, FREE_MB FROM V$ASM_DISK ORDER BY GROUP_NUMBER,DISK_NUMBER;
SELECT COUNT (PXN_KFFXP), DISK_KFFXP, GROUP_KFFXP FROM X$KFFXP GROUP BY DISK_KFFXP, GROUP_KFFXP ORDER BY GROUP_KFFXP,DISK_KFFXP;
SELECT * FROM V$ASM_CLIENT;
select * from V$ASM_ATTRIBUTE;
select * from v$asm_operation;
select * from gv$asm_operation
SELECT * FROM V$ASM_CLIENT;
select * from v$version;
show parameter asm
show parameter cluster
show parameter instance_type
show parameter instance_name
show parameter spfile
show sga
spool off
kfod status=TRUE asm_diskstring='/dev/asm_disk*' disk=all dscvgroup=TRUE OP=all
/etc/init.d/oracleasm querydisk T1_ASM05
select length(label),label,group_number, name,state from v$asm_disk where group_number=7;
connect / as sysasm
alter diskgroup PSYMQAOC1DATA rebalance power 10;
alter diskgroup data1 mount;
--RMAN BAackup list
====================
set pages 250
ttitle "Daily Backup........"
select DB NAME,dbid,
NVL(TO_CHAR(max(backuptype_db),'DD/MM/YYYY HH24:MI'),'01/01/0001:00:00') DBBKP,
NVL(TO_CHAR(max(backuptype_arch),'DD/MM/YYYY HH24:MI'),'01/01/0001:00:00') ARCBKP
from (
select a.name DB,dbid,
decode(b.bck_type,'D',max(b.completion_time),'I',
max(b.completion_time)) BACKUPTYPE_db,
decode(b.bck_type,'L',
max(b.completion_time)) BACKUPTYPE_arch
from rc_database a,bs b
where a.db_key=b.db_key
and b.bck_type is not null
and b.bs_key not in(Select bs_key from rc_backup_controlfile
where AUTOBACKUP_DATE is not null or AUTOBACKUP_SEQUENCE is not null)
and b.bs_key not in(select bs_key from rc_backup_spfile)
group by a.name,dbid,b.bck_type
) group by db,dbid
ORDER BY least(to_date(DBBKP,'DD/MM/YYYY HH24:MI'),
to_date(ARCBKP,'DD/MM/YYYY HH24:MI'));
PROD - Anar@kali1
non PROD - Disco@chali1
/usr/local/bin/sudo su - oracle
/usr/local/bin/su - oracle
--Block corruption:
===================
SELECT tablespace_name, segment_type, owner, segment_name FROM dba_extents
WHERE file_id = 2 and 11451 between block_id AND block_id + blocks - 1;
select * from v$database_block_corruption;
Stats gathering:
===============
exec dbms_stats.gather_schema_stats(ownname =>'DDPTDM', options => 'GATHER AUTO',estimate_percent => dbms_stats.auto_sample_size, method_opt =>'for all columns size rep
eat', cascade => true, degree => 12);
Wait events
===========
SELECT NVL(a.event, 'ON CPU') AS event,
COUNT(*) AS total_wait_time
FROM gv$active_session_history a
WHERE a.sample_time > SYSDATE - 5/(24*60) -- 5 mins
GROUP BY a.event
ORDER BY total_wait_time DESC;
--ARCHIVE LOG summary
=====================
set lines 120;
set pages 999;
SELECT
to_char(first_time,'YYYY-MON-DD') day,
to_char(sum(decode(to_char(first_time,'HH24'),'00',1,0)),'9999') "00",
to_char(sum(decode(to_char(first_time,'HH24'),'01',1,0)),'9999') "01",
to_char(sum(decode(to_char(first_time,'HH24'),'02',1,0)),'9999') "02",
to_char(sum(decode(to_char(first_time,'HH24'),'03',1,0)),'9999') "03",
to_char(sum(decode(to_char(first_time,'HH24'),'04',1,0)),'9999') "04",
to_char(sum(decode(to_char(first_time,'HH24'),'05',1,0)),'9999') "05",
to_char(sum(decode(to_char(first_time,'HH24'),'06',1,0)),'9999') "06",
to_char(sum(decode(to_char(first_time,'HH24'),'07',1,0)),'9999') "07",
to_char(sum(decode(to_char(first_time,'HH24'),'08',1,0)),'9999') "0",
to_char(sum(decode(to_char(first_time,'HH24'),'09',1,0)),'9999') "09",
to_char(sum(decode(to_char(first_time,'HH24'),'10',1,0)),'9999') "10",
to_char(sum(decode(to_char(first_time,'HH24'),'11',1,0)),'9999') "11",
to_char(sum(decode(to_char(first_time,'HH24'),'12',1,0)),'9999') "12",
to_char(sum(decode(to_char(first_time,'HH24'),'13',1,0)),'9999') "13",
to_char(sum(decode(to_char(first_time,'HH24'),'14',1,0)),'9999') "14",
to_char(sum(decode(to_char(first_time,'HH24'),'15',1,0)),'9999') "15",
to_char(sum(decode(to_char(first_time,'HH24'),'16',1,0)),'9999') "16",
to_char(sum(decode(to_char(first_time,'HH24'),'17',1,0)),'9999') "17",
to_char(sum(decode(to_char(first_time,'HH24'),'18',1,0)),'9999') "18",
to_char(sum(decode(to_char(first_time,'HH24'),'19',1,0)),'9999') "19",
to_char(sum(decode(to_char(first_time,'HH24'),'20',1,0)),'9999') "20",
to_char(sum(decode(to_char(first_time,'HH24'),'21',1,0)),'9999') "21",
to_char(sum(decode(to_char(first_time,'HH24'),'22',1,0)),'9999') "22",
to_char(sum(decode(to_char(first_time,'HH24'),'23',1,0)),'9999') "23"
from
gv$log_history
where FIRST_TIME > sysdate - 14
GROUP by
to_char(first_time,'YYYY-MON-DD')
Order by 1 ;
-------------------------------------------------------------------------------------
select a.inst_id, a.group#, b.thread#, a.member
from gv$logfile a, gv$log b
where a.group#=b.group#
and a.inst_id=b.inst_id
and b.thread#=(select value from v$parameter where name = 'thread')
and a.inst_id=( select instance_number from v$instance);
AVG LOG SIZE PER DAY
============================
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
;
AVG LOG SWITCH PER DAY
======================
col c1 format a10 heading "Month"
col c2 format a25 heading "Archive Date"
col c3 format 99999 heading "Switches"
compute AVG of C on A
compute AVG of C on REPORT
break on A skip 1 on REPORT skip 1
select
to_char(trunc(first_time), 'Month') c1,
to_char(trunc(first_time), 'Day : DD-Mon-YYYY') c2,
count(*) c3
from
v$log_history
where
trunc(first_time) > last_day(sysdate-100) +1
group by
trunc(first_time);
Partition:
=========
Move partition :
alter table tbms_voip.cdr_data move partition P_CD_20140901 tablespace P_CDR_CDATA_02 compress parallel 4 nologging;
Drop partition :
ALTER TABLE legaldemand.cutm_load DROP PARTITION P_CUTM_LOAD_20120801;
Exp & Imp in nohup
==================
nohup impdp parfile=import1.par &
ASM Disk creation:
==================
create diskgroup backup_dg EXTERNAL REDUNDANCY DISK
'/dev/mapper/t2_asm14',
'/dev/mapper/t2_asm15',
'/dev/mapper/t2_asm16'
ATTRIBUTE 'au_size'='4M',
'compatible.asm' = '11.2',
'compatible.rdbms' = '11.2'
Listener :
=========
alter system set remote_listener=null scope=both sid='*'
alter system set remote_listener=scoutprd-grid.cmc.co.delaver.cable.net:1521 scope=both sid='*'
alter system register;
Stop Scan listener
==================:
srvctl status scan_listener
srvctl stop scan_listener
srvctl start scan_listener
[ossripper24/23.cmc.co.ndcwest.cable.net:+ASM2:/opt/home/oracle]
Before bounce :
Instance CSCTPR1 is running on node ossripper23 with online services SCT_RO,SCT_RW. Instance status: Open.
Instance CSCTPR2 is running on node ossripper24 with online services SCT_LDR,SCT_RO,SCT_RW. Instance status: Open.
After bounce :
Instance CSCTPR1 is running on node ossripper23 with online services SCT_RO,SCT_RW. Instance status: Open.
Instance CSCTPR2 is running on node ossripper24 with online services SCT_LDR,SCT_RO,SCT_RW. Instance status: Open.
Scan Listener status before & after bounce :
SCAN Listener LISTENER_SCAN1 is enabled
SCAN listener LISTENER_SCAN1 is running on node ossripper24
SCAN Listener LISTENER_SCAN2 is enabled
SCAN listener LISTENER_SCAN2 is running on node ossripper23
SCAN Listener LISTENER_SCAN3 is enabled
SCAN listener LISTENER_SCAN3 is running on node ossripper23
RECOVERY_FILE_DEST
Invalid objects/Inex/Partition :
-------------------------------
SELECT SUBSTR(OBJECT_name,1,20),object_type,STATUS,OBJECT_TYPE,LAST_DDL_TIME from dba_objects where status<>'VALID' ORDER BY LAST_DDL_TIME;
SELECT OBJECT_name, object_type,STATUS from dba_objects where object_status<>'VALID'; where owner='VGOVIN9510K';
SELECT index_owner, index_name, partition_name, tablespace_name FROM dba_ind_PARTITIONS WHERE status = 'UNUSABLE' and index_owner='TAGDBA';
SELECT 'alter index '||index_owner||'.'||index_name ||' rebuild partition '||PARTITION_NAME||' TABLESPACE '||tablespace_name ||';' FROM dba_ind_partitions
WHERE status = 'UNUSABLE' and index_owner='VGOVIN9510K';
SELECT 'alter index '||owner||'.'||index_name||' rebuild tablespace '||tablespace_name ||';' FROM dba_indexes WHERE status = 'UNUSABLE';
alter table "DDPTDM"."IDST_CUST_TALLY" add PARTITION P_99253 VALUES(99253);
SELECT * FROM dba_indexes where owner='DDPTDM' and table_name='IDST_CUST'
SELECT DBMS_METADATA.get_ddl (INDEX, XIE3IDST_CUST, DDPTDM) FROM user_objects;
SQL> SELECT 'alter index '||owner||'.'||index_name||' rebuild tablespace '||tablespace_name ||';' FROM dba_indexes WHERE status = 'UNUSABLE';
alter index DDPTABLES.IDST_CUST_RATE_FUNC_INDEX1 rebuild tablespace DDPTABLES_D;
SELECT 'alter index '||table_name||'.'||index_name||' rebuild tablespace '||tablespace_name ||';' FROM dba_indexes WHERE status = 'UNUSABLE';
select 'alter index '||index_owner||'.'||index_name||' rebuild subpartition '||subpartition_name||' online;' from dba_ind_subpartitions where status='UNUSABLE';
select 'alter index '||index_owner||'.'||index_name||' rebuild partition '||partition_name||' online;' from dba_ind_partitions where status='UNUSABLE';
'ALTERINDEX'||TABLE_NAME||'.'||INDEX_NAME||'REBUILDTABLESPACE'||TABLESPACE_NAME||';'
------------------------------------------------------------------------------------------------------------------------
alter index IDST_CUST_RATE.IDST_CUST_RATE_FUNC_INDEX1 rebuild tablespace DDPTABLES_D;
export OPATCH_DEBUG=TRUE
Inventory rebuild
=================
cd /u01/app/oracle/product/11.2.0.4/db1/oui/bin/
./runInstaller -silent -detachHome ORACLE_HOME="/u01/app/oracle/product/11.2.0.4/db1" ORACLE_HOME_NAME="OraDb11g_home1" "CLUSTER_NODES={ecustdb-ch2-a1s-vip,ecustdb-ch2-a2s-vip}" LOCAL_NODE="ecustdb-ch2-a2s-vip"
./runInstaller -silent -attachHome ORACLE_HOME="/u01/app/oracle/product/11.2.0.4/db1" ORACLE_HOME_NAME="OraDb11g_home1" "CLUSTER_NODES={ecustdb-ch2-a1s-vip,ecustdb-ch2-a2s-vip}" LOCAL_NODE="ecustdb-ch2-a2s-vip"
475 at /etc/oraInst.loc
476 cat /etc/oraInst.loc
477 ls -ld /u01/app/oraInventory
478 ./runInstaller -silent -detachHome ORACLE_HOME="/u01/app/oracle/product/11.2.0.4/db1" ORACLE_HOME_NAME="OraDb11g_home1" "CLUSTER_NODES={ecustdb-ch2-a1s-vip,ecustdb-ch2-a2s-vip}"
479 /u01/app/oracle/product/11.2.0.4/db1/oui/bin/runInstaller -detachHome -noClusterEnabled ORACLE_HOME=/u01/app/oracle/product/11.2.0.4/db1 "INVENTORY_LOCATION=/u01/app/oraInventory" LOCAL_NODE=espdb-wc-a1
480 pwd
481 cd /u01/app/oracle/product/11.2.0.4/db1
Table Partitions :
-----------------
select PARTITION_NAME,HIGH_VALUE,TABLE_OWNER,TABLE_NAME from dba_tab_partitions where TABLE_NAME='TBLNTLOGGING'
Flash back restore point :
==========================
SELECT NAME, SCN, TIME, DATABASE_INCARNATION#,GUARANTEE_FLASHBACK_DATABASE,STORAGE_SIZE FROM V$RESTORE_POINT;
FRA USAGE
=========
select * from V$FLASH_RECOVERY_AREA_USAGE;
select name, floor(space_limit / 1024 / 1024) "Size MB" , ceil(space_used / 1024 / 1024) "Used MB" from v$recovery_file_dest order by name
Session query list :
====================
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 s.sid, s.username,s.machine,s.osuser, cpu_time,(elapsed_time/1000000)/60 as minutes,sql_text from gv$sqlarea a, gv$session s where s.sql_id = a.sql_id and s.machine like '####';
select count(*),s.machine from gv$sqlarea a, gv$session s group by s.machine;
select a.SID, a.SERIAL#, c.OBJECT_NAME from v$session a, v$locked_object b, user_objects c where a.SID=b.SESSION_ID and b.OBJECT_ID=c.OBJECT_ID;
select a.SID, a.SERIAL#, c.OBJECT_NAME from gv$session a, v$locked_object b, user_objects c where a.SID=b.SESSION_ID and b.OBJECT_ID=c.OBJECT_ID;
DBMS Job-status
===============
select JOB,BROKEN,SCHEMA_USER from dba_jobs where SCHEMA_USER='COMMON_DIM';
alter system set job_queue_processes=0 scope=both;
recover database until change 12206738633929;
export HISTTIMEFORMAT='%F %T'
history
Cluster instance status checking:
================================
select inst_id,instance_name,status,host_name,to_char(startup_time,'dd-mon-yyyy hh-mi-am') startup_time from gv$instance order by 1;
select INSTANCE_NAME,HOST_NAME,STARTUP_TIME,STATUS from gv$instance order by 1;
select name,open_mode,database_role from v$database;
show parameter db_unique_name
show parameter service
show parameter cluster_database;
Standby DB
==========
select SEQUENCE#,PROCESS,STATUS,THREAD# from gv$managed_standby where PROCESS like '%MRP%';
prompt +++++++++++LAST PRODUCTION SEQUENCE GENERATED++++++++++++++++++
select to_char(sysdate,'dd-mon-yyyy hh24:mi:ss') Date from dual;
SELECT distinct SEQUENCE# "Last Sequence Generated", THREAD# "Thread"
FROM V$ARCHIVED_LOG WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$ARCHIVED_LOG GROUP BY THREAD#)
ORDER BY 1;
set time on
set lines 200
prompt ++++++++++++++++++++LAST SEQUENCE RECIEVED FROM PRODUCTION and APPLIED ON STANDBY+++++++++++++++++++++++++
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#
;
prompt ++++++++++++++++++++CHECK FOR GAP AT STANDBY+++++++++++++++++++++++++
SELECT THREAD#, LOW_SEQUENCE#, HIGH_SEQUENCE# FROM V$ARCHIVE_GAP;
exit
AWR
===
@$ORACLE_HOME/rdbms/admin/awrrpt.sql
Nohub
=====
nohup /path_to_dir/myscript_here.sh &
Compile
=======
invalid.sql
Set heading off;
set feedback off;
set echo off;
Set lines 999;
Spool run_invalid.sql
select
'ALTER ' || OBJECT_TYPE || ' ' ||
OWNER || '.' || OBJECT_NAME || ' COMPILE;'
from
dba_objects
where
status = 'INVALID'
and
object_type in ('PACKAGE','FUNCTION','PROCEDURE')
;
spool off;
set heading on;
set feedback on;
set echo on;
@run_invalid.sql
=================================================================================================================
select substr(session_key,1,20),
Substr(input_type,1,20),
status,
to_char(start_time,'yyyy-mm-dd hh24:mi') start_time,
to_char(end_time,'yyyy-mm-dd hh24:mi') end_time,
substr(output_bytes_display,1,30),
substr(time_taken_display,1,30)
from v$rman_backup_job_details
order by session_key asc;
CONFIGURE CHANNEL DEVICE TYPE 'SBT_TAPE' FORMAT clear;
select * from V$RMAN_CONFIGURATION;
crosscheck backupset;
DELETE OBSOLETE;
DELETE EXPIRED BACKUP;
CROSSCHECK BACKUP;
configure retention policy to recovery window of 1 day
configure retention policy to none;
delete backup;
==================================================================================================================
Oracle-scripts learning=> http://www.oracle.com/technetwork/articles/linux/saternos-scripting-088882.html
Check standby from primary
==========================
show parameter log_archive_config
select * from v$dataguard_config;
DB instance startup time :
==========================
select to_char(startup_time,'dd/mm/yyyy hh:mm') from dual;
MRP process:
===========
select SEQUENCE#,PROCESS,STATUS,THREAD# from gv$managed_standby where PROCESS like '%MRP%';
SELECT THREAD#, LOW_SEQUENCE#, HIGH_SEQUENCE# FROM V$ARCHIVE_GAP;
=====================================================================================================================================================
Query tuning
============
select st.sql_id,plan_hash_value,to_char(begin_interval_time,'dd-mon-yyyy hh24:mi'),to_char(end_interval_time,'dd-mon-yyyy hh24:mi'),
elapsed_time_total/60000000,executions_total,rows_processed_total,sql_text from dba_hist_snapshot sp,dba_hist_sqlstat sh,dba_hist_sqltext st
where sp.snap_id=sh.snap_id and sh.sql_id=st.sql_id and st.sql_id='30ubrsbw12tf0' order by sp.snap_id desc
select st.sql_id,plan_hash_value,to_char(begin_interval_time,'dd-mon-yyyy hh24:mi'),to_char(end_interval_time,'dd-mon-yyyy hh24:mi'),
elapsed_time_total/60000000,executions_total,rows_processed_total,sql_text from dba_hist_snapshot sp,dba_hist_sqlstat sh,dba_hist_sqltext st
where sp.snap_id=sh.snap_id and sh.sql_id=st.sql_id and upper(st.sql_text) like '%AMVI_CNTRCT_ADMNSTRTV_HST%' order by sp.snap_id desc
Hist Plan
****************
column xms_child_number heading Ch|ld format 9 print
column xms_id heading Op|ID format 999
column xms_id2 heading Op|ID format a6
column xms_pred heading Pr|ed format a2
column xms_optimizer heading Optimizer|Mode format a10
column xms_plan_step heading Operation for a55
column xms_object_name heading Objcect|Name for a30
column xms_opt_cost heading Optimizer|Cost for 9999999
column xms_opt_card heading "Optim rows|from step" for 999999999
column xms_opt_bytes heading "Optim bytes|from step" for 999999999
column xms_predicate_info heading "Predicate Information (identified by operation id):" format a100 word_wrap
break on xms_child_number skip 1
select
--child_number xms_child_number,
case when access_predicates is not null then 'A' else ' ' end ||
case when filter_predicates is not null then 'F' else ' ' end xms_pred,
id xms_id,
lpad(' ',depth*1,' ')||operation || ' ' || options xms_plan_step,
object_name xms_object_name,
-- search_columns,
cost xms_opt_cost,
cardinality xms_opt_card,
bytes xms_opt_bytes,
optimizer xms_optimizer
-- other_tag,
-- other,
-- distribution,
-- access_predicates,
-- filter_predicates
from
dba_hist_sql_plan
where
plan_hash_value in ('2088223536')
============================================================================================================================
EXPORT DUMP
===========
nohup exp \'/ as sysdba\' file=3106_export_schemas.dmp log=3106_export_schemas.log owner=ATLANTA_DS,HOUSTON_DS &
CREATE TABLESAPCE
=================
CREATE UNDO TABLESPACE UNDOTBS2 datafile 'C:\ORACLEXE\APP\ORACLE\ORADATA\XE\UNDOTBS2.DBF' size 50M;
DROP TABLESPACE undotbs1 INCLUDING CONTENTS AND DATAFILES;
set lines 10000
column name format a10
UNDO SEGMENTS
=============
SELECT a.name,b.status
FROM v$rollname a,v$rollstat b
WHERE a.usn = b.usn
AND a.name IN (
SELECT segment_name
FROM dba_segments
WHERE tablespace_name = 'UNDOTBS2'
);
RECYCLEBIN
==========
SELECT OBJECT_NAME, ORIGINAL_NAME, TYPE FROM RECYCLEBIN;
PURGE TABLE int_admin_emp;
SELECT object_name, original_name FROM dba_recyclebin WHERE owner = 'HR';
Parallel Degree for PX Queries with Resource Manager (Doc ID 1321099.1)
Packing trace files
===================
Example to pack all trc files from this date-time use below cmd -
grep '2012-10-12 22:54' *.trc |awk -F: '{print $1}' |uniq |xargs tar cvf trace.tar
gzip trace.tar
============
alter tablespace P_G01_DATA_02 add datafile '/u01/data155/oradata/ODSPRD/P_G01_DATA_02_036.dbf' size 256m autoextend on next 256m maxsize 30G;
AWR interval checking
====================
set linesize 100
col snap_interval format a20
col retention format a20
col topnsql format a20
select * from dba_hist_wr_control;
Get all the nodes:
-----------------
sh-3.2$ . grid.env
sh-3.2$ olsnodes
KILL SESISION
=============
SET LINESIZE 100
COLUMN spid FORMAT A10
COLUMN username FORMAT A10
COLUMN program FORMAT A45
SELECT s.inst_id,
s.sid,
s.serial#,
p.spid,
s.username,
s.program
FROM gv$session s
JOIN gv$process p ON p.addr = s.paddr AND p.inst_id = s.inst_id
WHERE s.type != 'BACKGROUND';
SQL> ALTER SYSTEM KILL SESSION 'sid,serial#';
SQL> ALTER SYSTEM KILL SESSION 'sid,serial#,@inst_id';
SQL> ALTER SYSTEM KILL SESSION 'sid,serial#' IMMEDIATE;
select 'alter system kill session '||sid||','||serial#||' immediate;' from gv$session where username like '%OSS_READ%'
mailx testing
=============
echo "test" | mailx -s "Sessions killed for" abc@cable.com
2PC transaction:
===============
SQL> select local_tran_id, global_tran_id, state,mixed,advice from dba_2pc_pending order by local_tran_id;
=====================================
TPP SR 3-9713538031 - Undo corruption
=====================================
GGS_MON QUERIES
===============
05,15,25,35,45,55 * * * * /ggs/scripts/ggs_mon_status.ksh > /ggs/scripts/ggs_mon_status.log_`hostnam
Select PROGRAM, STATUS, NAME From GGS_MON.GGS_MON Where Status not LIKE 'BLACKOUT' AND Status not LIKE 'RUNNING';
Select PROGRAM,NAME,LAG,CHKP From GGS_MON.GGS_MON Where Status not LIKE 'BLACKOUT' AND LAG > THRESHOLD_LAG OR CHKPT > THRESHOLD_CHKPT;
Select PROGRAM, NAME, LAST_UPDATED From GGS_MON.GGS_MON Where Status not LIKE 'BLACKOUT' AND SYSDATE - LAST_UPDATED > 1/24;
list cluster nodes:
===================
olsnodes -n
SYSMAN Pwd
==========
[pacdcappmon1::/u01/app/oracle/12.1.0.3/Middleware/oms/bin]$ ./emcli login -username=SYSMAN
Enter password :
Login successful
Have used the same password in pacdcappmon1.cable.com
Other password
sysman/obiwan102
--default dbsnmp passwd: m0n1t0r ..axbcast!
agent secure passwd: axbcast!
OCR BACKUP
==========
ocrconfig -showbackup
./olsnodes -n -i
Cluster status
==============
/u01/app/11.2.0.2/grid/bin > ./crsctl check crs
./crsctl stat res -t
crsctl check crs
./crsctl start crs
./crsctl query css votedisk
srvctl status service -d psymprcsf1
srvctl config service -s psymprcsfo -d psymprcsf1
./crsctl check cluster -all
Cluster log
/u01/app/11.2.0.3/grid/log/tdsdb-cmc-a3p/cssd/ocssd.log
ALTER SYSTEM SET service_names='TDS_ADM_RO','TDS_APP_CR_RO','TDS_APP_CR_RW','TDS_ETL_RW','DDP_APP_RO' SCOPE=MEMORY
ALTER SYSTEM SET service_names='DDP_APP_RO','TDS_ADM_RO','TDS_APP_CR_RO','TDS_APP_CR_RW','TDS_ETL_RW','WS_BILL_CR_RO' SCOPE=MEMORY SID='CTDSPRA3';
crsctl stop crs -f
DB status :
srvctl status database -d savdev1l -v
srvctl config service -d psymprop
srvctl status service -d psymprop
srvctl status service -d lmtprs2 -s LMT_RO
srvctl status scan_listener
Cluster Config backup
=====================
$GRID_HOME/bin/crsctl stat res -t
$GRID_HOME/bin/crsctl stat res -p
$GRID_HOME/bin/crsctl query css votedisk
$GRID_HOME/bin/ocrcheck
$GRID_HOME/bin/oifcfg getif
$GRID_HOME/bin/srvctl config nodeapps -a
$GRID_HOME/bin/srvctl config scan
$GRID_HOME/bin/srvctl config asm -a
$GRID_HOME/bin/srvctl config listener -l <listener-name> -a
$DB_HOME/bin/srvctl config database -d <dbname> -a
$DB_HOME/bin/srvctl config service -d <dbname> -s <service-name> -v
srvctl config service -d psymprjl -s psymprjl1fo
========================================================================================================================================================
| Heath Checking Queries |
========================================================================================================================================================
Finding Locked Objects :
*===============*
SELECT b.inst_id,b.session_id AS sid,NVL(b.oracle_username, ‘(oracle)’) AS username,a.owner AS object_owner,a.object_name,
Decode(b.locked_mode, 0, ‘None’,1, ‘Null (NULL)’,2, ‘Row-S (SS)’,3, ‘Row-X (SX)’,4, ‘Share (S)’,5, ‘S/Row-X (SSX)’,6, ‘Exclusive (X)’,
b.locked_mode) locked_mode,b.os_user_name
FROM dba_objects a, gv$locked_object b
WHERE a.object_id = b.object_id
ORDER BY 1, 2, 3, 4;
Display Long Operation :
*===============*
SELECT s.inst_id,
s.sid,
s.serial#,
s.username,
s.module,
ROUND(sl.elapsed_seconds/60) || ':' || MOD(sl.elapsed_seconds,60) elapsed,
ROUND(sl.time_remaining/60) || ':' || MOD(sl.time_remaining,60) remaining,
ROUND(sl.sofar/sl.totalwork*100, 2) progress_pct
FROM gv$session s,
gv$session_longops sl
WHERE s.sid = sl.sid
AND s.inst_id = sl.inst_id
AND s.serial# = sl.serial#;
Monitoring Memory:
*=============*
SELECT a.inst_id, NVL(a.username,’(oracle)’) AS username, a.module, a.program,
Trunc(b.value/1024) AS memory_kb
FROM gv$session a, gv$sesstat b, gv$statname c
WHERE a.sid = b.sid AND a.inst_id = b.inst_id AND b.statistic# = c.statistic#
AND b.inst_id = c.inst_id AND c.name = ‘session pga memory’
AND a.program IS NOT NULL
ORDER BY b.value DESC
Monitor Session Undo Information :
*=======================*
SELECT s.inst_id, s.username, s.sid, s.serial#, t.used_ublk, t.used_urec, rs.segment_name, r.rssize, r.status
FROM gv$transaction t, gv$session s, gv$rollstat r, dba_rollback_segs rs
WHERE s.saddr = t.ses_addr AND s.inst_id = t.inst_id AND t.xidusn = r.usn AND t.inst_id = r.inst_id AND rs.segment_id = t.xidusn
ORDER BY t.used_ublk DESC;
Monitoring Session Wait:
*===============*
SELECT s.inst_id, NVL(s.username, ‘(oracle)’) AS username, s.sid, s.serial#, sw.event, sw.wait_class, sw.wait_time, sw.seconds_in_wait, sw.state
FROM gv$session_wait sw, gv$session s
WHERE s.sid = sw.sid
AND s.inst_id = sw.inst_id
ORDER BY sw.seconds_in_wait DESC;
Monitoring Session RAC :
*=================*
SELECT NVL(s.username, ‘(oracle)’) AS username, s.inst_id, s.osuser, s.sid, s.serial#, p.spid, s.lockwait, s.status, s.module, s.machine, s.program,
TO_CHAR(s.logon_Time,’DD-MON-YYYY HH24:MI:SS’) AS logon_time
FROM gv$session s, gv$process p
WHERE s.paddr = p.addr AND s.inst_id = p.inst_id
ORDER BY s.username, s.osuser;
select count(s.status) "INACTIVE SESSIONS > 1HOUR "
from gv$session s, v$process p
where
p.addr=s.paddr and
s.last_call_et > 25200 and
s.status='INACTIVE';
At Database level: You have to monitor all the cluster logs, event logs, asm logs and rdbms logs
*===========================================================*
Cluster (ORA_CRS_HOME) and all related log files:
CRS alert log file
CRS logs: log/hostname/crsd
CSS logs: log/hostname/cssd
EVM logs: log/hostname/evmd & /log/hostname/evm/log
SRVM logs: log/hostname/client
OPMN logs: opmn/logs
Resource specific logs - /log/hostname/racg
ORACLE_HOME
Resource spec logs - /log/hostname/racg
SRVM logs- log/hostname/client
ASM
alert_SID.log : location: ORACLE_HOME/rdbms/log
Trace files:
bdump - background_dump_dest
cdump - core_dump_dest
udump - user_dump_dest
listener_<NODE>.log : ORACLE_HOME/network/log
=============================================================================================================================================================
ASM commands:
============
alter diskgroup <diskgroup name>
add disk '<new device physical name 1>', .., '<new device physical name N>' ====>>>>> adding disks belongs to new SAN
drop disk <old disk logical name 1>, <old disk logical name 2>, ..,<old disk logical name N> ==>>>>>>> droping the disks from old SAN
rebalance power <#>;
/etc/init.d/oracleasm listdisks
select GROUP_NUMBER,HEADER_STATUS,STATE,TOTAL_MB,FREE_MB,PATH from v$asm_disk where PATH='ORCL:LV_ASM05';
Rebalance time
==============;
select INST_ID, OPERATION, STATE, POWER, SOFAR, EST_WORK, EST_RATE, EST_MINUTES from GV$ASM_OPERATION;
PESPPRETDATA DISMOUNTED
ASM disk config queries :
=========================
SPOOL ASM_FIRST<instance#>.SPOOL ASM_FIRST<instance#>.HTML
SET MARKUP HTML ON
set echo on
set pagesize 200
alter session set nls_date_format='DD-MON-YYYY HH24:MI:SS';
select 'THIS ASM REPORT WAS GENERATED AT: ==)> ' , sysdate " " from dual;
select 'HOSTNAME ASSOCIATED WITH THIS ASM INSTANCE: ==)> ' , MACHINE " " from v$session where program like '%SMON%';
select * from v$asm_diskgroup;
SELECT * FROM V$ASM_DISK ORDER BY GROUP_NUMBER,DISK_NUMBER;
SELECT GROUP_NUMBER,DISK_NUMBER, NAME, TOTAL_MB, FREE_MB FROM V$ASM_DISK ORDER BY GROUP_NUMBER,DISK_NUMBER;
SELECT COUNT (PXN_KFFXP), DISK_KFFXP, GROUP_KFFXP FROM X$KFFXP GROUP BY DISK_KFFXP, GROUP_KFFXP ORDER BY GROUP_KFFXP,DISK_KFFXP;
SELECT * FROM V$ASM_CLIENT;
select * from V$ASM_ATTRIBUTE;
select * from v$asm_operation;
select * from gv$asm_operation
SELECT * FROM V$ASM_CLIENT;
select * from v$version;
show parameter asm
show parameter cluster
show parameter instance_type
show parameter instance_name
show parameter spfile
show sga
spool off
kfod status=TRUE asm_diskstring='/dev/asm_disk*' disk=all dscvgroup=TRUE OP=all
/etc/init.d/oracleasm querydisk T1_ASM05
select length(label),label,group_number, name,state from v$asm_disk where group_number=7;
connect / as sysasm
alter diskgroup PSYMQAOC1DATA rebalance power 10;
alter diskgroup data1 mount;
--RMAN BAackup list
====================
set pages 250
ttitle "Daily Backup........"
select DB NAME,dbid,
NVL(TO_CHAR(max(backuptype_db),'DD/MM/YYYY HH24:MI'),'01/01/0001:00:00') DBBKP,
NVL(TO_CHAR(max(backuptype_arch),'DD/MM/YYYY HH24:MI'),'01/01/0001:00:00') ARCBKP
from (
select a.name DB,dbid,
decode(b.bck_type,'D',max(b.completion_time),'I',
max(b.completion_time)) BACKUPTYPE_db,
decode(b.bck_type,'L',
max(b.completion_time)) BACKUPTYPE_arch
from rc_database a,bs b
where a.db_key=b.db_key
and b.bck_type is not null
and b.bs_key not in(Select bs_key from rc_backup_controlfile
where AUTOBACKUP_DATE is not null or AUTOBACKUP_SEQUENCE is not null)
and b.bs_key not in(select bs_key from rc_backup_spfile)
group by a.name,dbid,b.bck_type
) group by db,dbid
ORDER BY least(to_date(DBBKP,'DD/MM/YYYY HH24:MI'),
to_date(ARCBKP,'DD/MM/YYYY HH24:MI'));
PROD - Anar@kali1
non PROD - Disco@chali1
/usr/local/bin/sudo su - oracle
/usr/local/bin/su - oracle
--Block corruption:
===================
SELECT tablespace_name, segment_type, owner, segment_name FROM dba_extents
WHERE file_id = 2 and 11451 between block_id AND block_id + blocks - 1;
select * from v$database_block_corruption;
Stats gathering:
===============
exec dbms_stats.gather_schema_stats(ownname =>'DDPTDM', options => 'GATHER AUTO',estimate_percent => dbms_stats.auto_sample_size, method_opt =>'for all columns size rep
eat', cascade => true, degree => 12);
Wait events
===========
SELECT NVL(a.event, 'ON CPU') AS event,
COUNT(*) AS total_wait_time
FROM gv$active_session_history a
WHERE a.sample_time > SYSDATE - 5/(24*60) -- 5 mins
GROUP BY a.event
ORDER BY total_wait_time DESC;
--ARCHIVE LOG summary
=====================
set lines 120;
set pages 999;
SELECT
to_char(first_time,'YYYY-MON-DD') day,
to_char(sum(decode(to_char(first_time,'HH24'),'00',1,0)),'9999') "00",
to_char(sum(decode(to_char(first_time,'HH24'),'01',1,0)),'9999') "01",
to_char(sum(decode(to_char(first_time,'HH24'),'02',1,0)),'9999') "02",
to_char(sum(decode(to_char(first_time,'HH24'),'03',1,0)),'9999') "03",
to_char(sum(decode(to_char(first_time,'HH24'),'04',1,0)),'9999') "04",
to_char(sum(decode(to_char(first_time,'HH24'),'05',1,0)),'9999') "05",
to_char(sum(decode(to_char(first_time,'HH24'),'06',1,0)),'9999') "06",
to_char(sum(decode(to_char(first_time,'HH24'),'07',1,0)),'9999') "07",
to_char(sum(decode(to_char(first_time,'HH24'),'08',1,0)),'9999') "0",
to_char(sum(decode(to_char(first_time,'HH24'),'09',1,0)),'9999') "09",
to_char(sum(decode(to_char(first_time,'HH24'),'10',1,0)),'9999') "10",
to_char(sum(decode(to_char(first_time,'HH24'),'11',1,0)),'9999') "11",
to_char(sum(decode(to_char(first_time,'HH24'),'12',1,0)),'9999') "12",
to_char(sum(decode(to_char(first_time,'HH24'),'13',1,0)),'9999') "13",
to_char(sum(decode(to_char(first_time,'HH24'),'14',1,0)),'9999') "14",
to_char(sum(decode(to_char(first_time,'HH24'),'15',1,0)),'9999') "15",
to_char(sum(decode(to_char(first_time,'HH24'),'16',1,0)),'9999') "16",
to_char(sum(decode(to_char(first_time,'HH24'),'17',1,0)),'9999') "17",
to_char(sum(decode(to_char(first_time,'HH24'),'18',1,0)),'9999') "18",
to_char(sum(decode(to_char(first_time,'HH24'),'19',1,0)),'9999') "19",
to_char(sum(decode(to_char(first_time,'HH24'),'20',1,0)),'9999') "20",
to_char(sum(decode(to_char(first_time,'HH24'),'21',1,0)),'9999') "21",
to_char(sum(decode(to_char(first_time,'HH24'),'22',1,0)),'9999') "22",
to_char(sum(decode(to_char(first_time,'HH24'),'23',1,0)),'9999') "23"
from
gv$log_history
where FIRST_TIME > sysdate - 14
GROUP by
to_char(first_time,'YYYY-MON-DD')
Order by 1 ;
-------------------------------------------------------------------------------------
select a.inst_id, a.group#, b.thread#, a.member
from gv$logfile a, gv$log b
where a.group#=b.group#
and a.inst_id=b.inst_id
and b.thread#=(select value from v$parameter where name = 'thread')
and a.inst_id=( select instance_number from v$instance);
AVG LOG SIZE PER DAY
============================
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
;
AVG LOG SWITCH PER DAY
======================
col c1 format a10 heading "Month"
col c2 format a25 heading "Archive Date"
col c3 format 99999 heading "Switches"
compute AVG of C on A
compute AVG of C on REPORT
break on A skip 1 on REPORT skip 1
select
to_char(trunc(first_time), 'Month') c1,
to_char(trunc(first_time), 'Day : DD-Mon-YYYY') c2,
count(*) c3
from
v$log_history
where
trunc(first_time) > last_day(sysdate-100) +1
group by
trunc(first_time);
Partition:
=========
Move partition :
alter table tbms_voip.cdr_data move partition P_CD_20140901 tablespace P_CDR_CDATA_02 compress parallel 4 nologging;
Drop partition :
ALTER TABLE legaldemand.cutm_load DROP PARTITION P_CUTM_LOAD_20120801;
Exp & Imp in nohup
==================
nohup impdp parfile=import1.par &
ASM Disk creation:
==================
create diskgroup backup_dg EXTERNAL REDUNDANCY DISK
'/dev/mapper/t2_asm14',
'/dev/mapper/t2_asm15',
'/dev/mapper/t2_asm16'
ATTRIBUTE 'au_size'='4M',
'compatible.asm' = '11.2',
'compatible.rdbms' = '11.2'
Listener :
=========
alter system set remote_listener=null scope=both sid='*'
alter system set remote_listener=scoutprd-grid.cmc.co.delaver.cable.net:1521 scope=both sid='*'
alter system register;
Stop Scan listener
==================:
srvctl status scan_listener
srvctl stop scan_listener
srvctl start scan_listener
[ossripper24/23.cmc.co.ndcwest.cable.net:+ASM2:/opt/home/oracle]
Before bounce :
Instance CSCTPR1 is running on node ossripper23 with online services SCT_RO,SCT_RW. Instance status: Open.
Instance CSCTPR2 is running on node ossripper24 with online services SCT_LDR,SCT_RO,SCT_RW. Instance status: Open.
After bounce :
Instance CSCTPR1 is running on node ossripper23 with online services SCT_RO,SCT_RW. Instance status: Open.
Instance CSCTPR2 is running on node ossripper24 with online services SCT_LDR,SCT_RO,SCT_RW. Instance status: Open.
Scan Listener status before & after bounce :
SCAN Listener LISTENER_SCAN1 is enabled
SCAN listener LISTENER_SCAN1 is running on node ossripper24
SCAN Listener LISTENER_SCAN2 is enabled
SCAN listener LISTENER_SCAN2 is running on node ossripper23
SCAN Listener LISTENER_SCAN3 is enabled
SCAN listener LISTENER_SCAN3 is running on node ossripper23
RECOVERY_FILE_DEST
Invalid objects/Inex/Partition :
-------------------------------
SELECT SUBSTR(OBJECT_name,1,20),object_type,STATUS,OBJECT_TYPE,LAST_DDL_TIME from dba_objects where status<>'VALID' ORDER BY LAST_DDL_TIME;
SELECT OBJECT_name, object_type,STATUS from dba_objects where object_status<>'VALID'; where owner='VGOVIN9510K';
SELECT index_owner, index_name, partition_name, tablespace_name FROM dba_ind_PARTITIONS WHERE status = 'UNUSABLE' and index_owner='TAGDBA';
SELECT 'alter index '||index_owner||'.'||index_name ||' rebuild partition '||PARTITION_NAME||' TABLESPACE '||tablespace_name ||';' FROM dba_ind_partitions
WHERE status = 'UNUSABLE' and index_owner='VGOVIN9510K';
SELECT 'alter index '||owner||'.'||index_name||' rebuild tablespace '||tablespace_name ||';' FROM dba_indexes WHERE status = 'UNUSABLE';
alter table "DDPTDM"."IDST_CUST_TALLY" add PARTITION P_99253 VALUES(99253);
SELECT * FROM dba_indexes where owner='DDPTDM' and table_name='IDST_CUST'
SELECT DBMS_METADATA.get_ddl (INDEX, XIE3IDST_CUST, DDPTDM) FROM user_objects;
SQL> SELECT 'alter index '||owner||'.'||index_name||' rebuild tablespace '||tablespace_name ||';' FROM dba_indexes WHERE status = 'UNUSABLE';
alter index DDPTABLES.IDST_CUST_RATE_FUNC_INDEX1 rebuild tablespace DDPTABLES_D;
SELECT 'alter index '||table_name||'.'||index_name||' rebuild tablespace '||tablespace_name ||';' FROM dba_indexes WHERE status = 'UNUSABLE';
select 'alter index '||index_owner||'.'||index_name||' rebuild subpartition '||subpartition_name||' online;' from dba_ind_subpartitions where status='UNUSABLE';
select 'alter index '||index_owner||'.'||index_name||' rebuild partition '||partition_name||' online;' from dba_ind_partitions where status='UNUSABLE';
'ALTERINDEX'||TABLE_NAME||'.'||INDEX_NAME||'REBUILDTABLESPACE'||TABLESPACE_NAME||';'
------------------------------------------------------------------------------------------------------------------------
alter index IDST_CUST_RATE.IDST_CUST_RATE_FUNC_INDEX1 rebuild tablespace DDPTABLES_D;
export OPATCH_DEBUG=TRUE
Inventory rebuild
=================
cd /u01/app/oracle/product/11.2.0.4/db1/oui/bin/
./runInstaller -silent -detachHome ORACLE_HOME="/u01/app/oracle/product/11.2.0.4/db1" ORACLE_HOME_NAME="OraDb11g_home1" "CLUSTER_NODES={ecustdb-ch2-a1s-vip,ecustdb-ch2-a2s-vip}" LOCAL_NODE="ecustdb-ch2-a2s-vip"
./runInstaller -silent -attachHome ORACLE_HOME="/u01/app/oracle/product/11.2.0.4/db1" ORACLE_HOME_NAME="OraDb11g_home1" "CLUSTER_NODES={ecustdb-ch2-a1s-vip,ecustdb-ch2-a2s-vip}" LOCAL_NODE="ecustdb-ch2-a2s-vip"
475 at /etc/oraInst.loc
476 cat /etc/oraInst.loc
477 ls -ld /u01/app/oraInventory
478 ./runInstaller -silent -detachHome ORACLE_HOME="/u01/app/oracle/product/11.2.0.4/db1" ORACLE_HOME_NAME="OraDb11g_home1" "CLUSTER_NODES={ecustdb-ch2-a1s-vip,ecustdb-ch2-a2s-vip}"
479 /u01/app/oracle/product/11.2.0.4/db1/oui/bin/runInstaller -detachHome -noClusterEnabled ORACLE_HOME=/u01/app/oracle/product/11.2.0.4/db1 "INVENTORY_LOCATION=/u01/app/oraInventory" LOCAL_NODE=espdb-wc-a1
480 pwd
481 cd /u01/app/oracle/product/11.2.0.4/db1
Table Partitions :
-----------------
select PARTITION_NAME,HIGH_VALUE,TABLE_OWNER,TABLE_NAME from dba_tab_partitions where TABLE_NAME='TBLNTLOGGING'
Flash back restore point :
==========================
SELECT NAME, SCN, TIME, DATABASE_INCARNATION#,GUARANTEE_FLASHBACK_DATABASE,STORAGE_SIZE FROM V$RESTORE_POINT;
FRA USAGE
=========
select * from V$FLASH_RECOVERY_AREA_USAGE;
select name, floor(space_limit / 1024 / 1024) "Size MB" , ceil(space_used / 1024 / 1024) "Used MB" from v$recovery_file_dest order by name
Session query list :
====================
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 s.sid, s.username,s.machine,s.osuser, cpu_time,(elapsed_time/1000000)/60 as minutes,sql_text from gv$sqlarea a, gv$session s where s.sql_id = a.sql_id and s.machine like '####';
select count(*),s.machine from gv$sqlarea a, gv$session s group by s.machine;
select a.SID, a.SERIAL#, c.OBJECT_NAME from v$session a, v$locked_object b, user_objects c where a.SID=b.SESSION_ID and b.OBJECT_ID=c.OBJECT_ID;
select a.SID, a.SERIAL#, c.OBJECT_NAME from gv$session a, v$locked_object b, user_objects c where a.SID=b.SESSION_ID and b.OBJECT_ID=c.OBJECT_ID;
DBMS Job-status
===============
select JOB,BROKEN,SCHEMA_USER from dba_jobs where SCHEMA_USER='COMMON_DIM';
alter system set job_queue_processes=0 scope=both;
recover database until change 12206738633929;
export HISTTIMEFORMAT='%F %T'
history
Cluster instance status checking:
================================
select inst_id,instance_name,status,host_name,to_char(startup_time,'dd-mon-yyyy hh-mi-am') startup_time from gv$instance order by 1;
select INSTANCE_NAME,HOST_NAME,STARTUP_TIME,STATUS from gv$instance order by 1;
select name,open_mode,database_role from v$database;
show parameter db_unique_name
show parameter service
show parameter cluster_database;
Standby DB
==========
select SEQUENCE#,PROCESS,STATUS,THREAD# from gv$managed_standby where PROCESS like '%MRP%';
prompt +++++++++++LAST PRODUCTION SEQUENCE GENERATED++++++++++++++++++
select to_char(sysdate,'dd-mon-yyyy hh24:mi:ss') Date from dual;
SELECT distinct SEQUENCE# "Last Sequence Generated", THREAD# "Thread"
FROM V$ARCHIVED_LOG WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$ARCHIVED_LOG GROUP BY THREAD#)
ORDER BY 1;
set time on
set lines 200
prompt ++++++++++++++++++++LAST SEQUENCE RECIEVED FROM PRODUCTION and APPLIED ON STANDBY+++++++++++++++++++++++++
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#
;
prompt ++++++++++++++++++++CHECK FOR GAP AT STANDBY+++++++++++++++++++++++++
SELECT THREAD#, LOW_SEQUENCE#, HIGH_SEQUENCE# FROM V$ARCHIVE_GAP;
exit
AWR
===
@$ORACLE_HOME/rdbms/admin/awrrpt.sql
Nohub
=====
nohup /path_to_dir/myscript_here.sh &
Compile
=======
invalid.sql
Set heading off;
set feedback off;
set echo off;
Set lines 999;
Spool run_invalid.sql
select
'ALTER ' || OBJECT_TYPE || ' ' ||
OWNER || '.' || OBJECT_NAME || ' COMPILE;'
from
dba_objects
where
status = 'INVALID'
and
object_type in ('PACKAGE','FUNCTION','PROCEDURE')
;
spool off;
set heading on;
set feedback on;
set echo on;
@run_invalid.sql
=================================================================================================================
select substr(session_key,1,20),
Substr(input_type,1,20),
status,
to_char(start_time,'yyyy-mm-dd hh24:mi') start_time,
to_char(end_time,'yyyy-mm-dd hh24:mi') end_time,
substr(output_bytes_display,1,30),
substr(time_taken_display,1,30)
from v$rman_backup_job_details
order by session_key asc;
CONFIGURE CHANNEL DEVICE TYPE 'SBT_TAPE' FORMAT clear;
select * from V$RMAN_CONFIGURATION;
crosscheck backupset;
DELETE OBSOLETE;
DELETE EXPIRED BACKUP;
CROSSCHECK BACKUP;
configure retention policy to recovery window of 1 day
configure retention policy to none;
delete backup;
==================================================================================================================
Oracle-scripts learning=> http://www.oracle.com/technetwork/articles/linux/saternos-scripting-088882.html
Check standby from primary
==========================
show parameter log_archive_config
select * from v$dataguard_config;
DB instance startup time :
==========================
select to_char(startup_time,'dd/mm/yyyy hh:mm') from dual;
MRP process:
===========
select SEQUENCE#,PROCESS,STATUS,THREAD# from gv$managed_standby where PROCESS like '%MRP%';
SELECT THREAD#, LOW_SEQUENCE#, HIGH_SEQUENCE# FROM V$ARCHIVE_GAP;
=====================================================================================================================================================
Query tuning
============
select st.sql_id,plan_hash_value,to_char(begin_interval_time,'dd-mon-yyyy hh24:mi'),to_char(end_interval_time,'dd-mon-yyyy hh24:mi'),
elapsed_time_total/60000000,executions_total,rows_processed_total,sql_text from dba_hist_snapshot sp,dba_hist_sqlstat sh,dba_hist_sqltext st
where sp.snap_id=sh.snap_id and sh.sql_id=st.sql_id and st.sql_id='30ubrsbw12tf0' order by sp.snap_id desc
select st.sql_id,plan_hash_value,to_char(begin_interval_time,'dd-mon-yyyy hh24:mi'),to_char(end_interval_time,'dd-mon-yyyy hh24:mi'),
elapsed_time_total/60000000,executions_total,rows_processed_total,sql_text from dba_hist_snapshot sp,dba_hist_sqlstat sh,dba_hist_sqltext st
where sp.snap_id=sh.snap_id and sh.sql_id=st.sql_id and upper(st.sql_text) like '%AMVI_CNTRCT_ADMNSTRTV_HST%' order by sp.snap_id desc
Hist Plan
****************
column xms_child_number heading Ch|ld format 9 print
column xms_id heading Op|ID format 999
column xms_id2 heading Op|ID format a6
column xms_pred heading Pr|ed format a2
column xms_optimizer heading Optimizer|Mode format a10
column xms_plan_step heading Operation for a55
column xms_object_name heading Objcect|Name for a30
column xms_opt_cost heading Optimizer|Cost for 9999999
column xms_opt_card heading "Optim rows|from step" for 999999999
column xms_opt_bytes heading "Optim bytes|from step" for 999999999
column xms_predicate_info heading "Predicate Information (identified by operation id):" format a100 word_wrap
break on xms_child_number skip 1
select
--child_number xms_child_number,
case when access_predicates is not null then 'A' else ' ' end ||
case when filter_predicates is not null then 'F' else ' ' end xms_pred,
id xms_id,
lpad(' ',depth*1,' ')||operation || ' ' || options xms_plan_step,
object_name xms_object_name,
-- search_columns,
cost xms_opt_cost,
cardinality xms_opt_card,
bytes xms_opt_bytes,
optimizer xms_optimizer
-- other_tag,
-- other,
-- distribution,
-- access_predicates,
-- filter_predicates
from
dba_hist_sql_plan
where
plan_hash_value in ('2088223536')
============================================================================================================================
EXPORT DUMP
===========
nohup exp \'/ as sysdba\' file=3106_export_schemas.dmp log=3106_export_schemas.log owner=ATLANTA_DS,HOUSTON_DS &
CREATE TABLESAPCE
=================
CREATE UNDO TABLESPACE UNDOTBS2 datafile 'C:\ORACLEXE\APP\ORACLE\ORADATA\XE\UNDOTBS2.DBF' size 50M;
DROP TABLESPACE undotbs1 INCLUDING CONTENTS AND DATAFILES;
set lines 10000
column name format a10
UNDO SEGMENTS
=============
SELECT a.name,b.status
FROM v$rollname a,v$rollstat b
WHERE a.usn = b.usn
AND a.name IN (
SELECT segment_name
FROM dba_segments
WHERE tablespace_name = 'UNDOTBS2'
);
RECYCLEBIN
==========
SELECT OBJECT_NAME, ORIGINAL_NAME, TYPE FROM RECYCLEBIN;
PURGE TABLE int_admin_emp;
SELECT object_name, original_name FROM dba_recyclebin WHERE owner = 'HR';
Parallel Degree for PX Queries with Resource Manager (Doc ID 1321099.1)
Packing trace files
===================
Example to pack all trc files from this date-time use below cmd -
grep '2012-10-12 22:54' *.trc |awk -F: '{print $1}' |uniq |xargs tar cvf trace.tar
gzip trace.tar
