Posts

Agent Silent installation

-
Agent Silent installation steps:- We have two ways to download the agent either we can download from Metalink or we can download through OEM NOTE:- If you do not enter agent port while installing , then either default port 3872 or any free port between 1830 and 1849 will be taken   #################################################################### ## copyright (c) 1999, 2012 Oracle. All rights reserved.          ## ##                                                                ## ## Specify values for the variables listed below to customize     ## ## your installation.                                             ## ##                                                                ## ## Each variable is associated with a comment. The comment        ## ## identifies the variable type.                                  ## ##                                                                ## ## Please specify the values in the following format:
Post a Comment
Read more

Memory utilization on Oracle database

-
use following query to calculate the memory used by the each session: set lines 375 pages 375 col session for a45 SELECT to_char(ssn.sid, '9999') || ' - ' || nvl(ssn.username, nvl(bgp.name, 'background')) || nvl(lower(ssn.machine), ins.host_name) "SESSION", ssn.username, ssn.status, to_char(prc.spid, '999999999') "PID/THREAD", to_char((se1.value/1024)/1024, '999G999G990D00') || ' MB' " CURRENT SIZE", to_char((se2.value/1024)/1024, '999G999G990D00') || ' MB' " MAXIMUM SIZE" FROM v$sesstat se1, v$sesstat se2, v$session ssn, v$bgprocess bgp, v$process prc, v$instance ins, v$statname stat1, v$statname stat2 WHERE se1.statistic# = stat1.statistic# and stat1.name = 'session pga memory' AND se2.statistic# = stat2.statistic# and stat2.name = 'session pga memory max' AND se1.sid = ssn.sid AND se2.sid = ssn.sid AND ssn.paddr = bgp.paddr (+) AND ssn.paddr = prc.addr (+)  order b
Post a Comment
Read more

To check Drive free/used space on Windows server

-
To check Windows drives free/used space: Please save this as Diskspace.ps1 on any server, and you can access from there. ==== Function Formatting { param([string]$VolumeNam,[string]$siz,[string]$Free,[string]$freper) $obj = New-Object PSObject $obj | Add-Member NoteProperty name($VolumeNam) $obj | Add-Member NoteProperty capacityGB($siz) $obj | Add-Member NoteProperty FreespaceGB($Free) $obj | Add-Member NoteProperty %free($freper) write-output  $obj | ft -autosize  } $computer=read-host "Enter Server Name " $name=read-host "Enter Drive Name " if(!$name) { $dp=Get-WmiObject win32_volume -computername $computer | Where-Object {$_.drivetype -eq 3} foreach ($item in $dp) { $Fr=[math]::round(($item.Freespace)/1073741824,2) $capacit=[math]::round(($item.capacity)/1073741824,2) if($capacit -eq 0) { $used="None" $freeper="100%" } else { $used=[math]::round(($capacit-$fr),2) $freeper=[math]::round(($fr/$capacit)*100,2) Formatting $item.name $capacit $Fr
Post a Comment
Read more

CPU Utilization

-
Follow below queries to find CPU utilization: 1) SET LINESIZE 145 SET PAGESIZE 9999 COLUMN sid FORMAT 99999 HEADING 'SID' COLUMN serial_id FORMAT 999999 HEADING 'Serial#' COLUMN session_status FORMAT a9 HEADING 'Status' JUSTIFY right COLUMN oracle_username FORMAT a12 HEADING 'Oracle User' JUSTIFY right COLUMN os_username FORMAT a9 HEADING 'O/S User' JUSTIFY right COLUMN os_pid FORMAT 9999999 HEADING 'O/S PID' JUSTIFY right COLUMN session_program FORMAT a20 HEADING 'Session Program' TRUNC COLUMN session_machine FORMAT a14 HEADING 'Machine' JUSTIFY right TRUNC COLUMN cpu_value FORMAT 999,999,999,999 HEADING 'CPU' SELECT s.sid sid , s.serial# serial_id , lpad(s.status,9) session_status , lpad(s.username,12) oracle_username , lpad(s.osuser,9) os_username , lpad(p.spid,7) os_pid , s.program session_program , lpad(s.machine,14) session_machine , sstat.value cpu_value FROM v$process p , v$session s , v$sesstat sstat , v$s
Post a Comment
Read more

ARCHIVE LOG

-
For sizing of archive log destination,  1.it should be twice the size of maximum archive generated per day. 2.compressed backup of archive logs comes to 30 % of actual archive log size. ============================================================== Simple query to find the archive generated ============================================================== select trunc(completion_time),count(1),round(count(1)*200/1024) from v$archived_log group by  trunc(completion_time) order by  trunc(completion_time); Query to give size of archive generated perday in GB ============================================================ SELECT A.*, Round(A.Count#*B.AVG#/1024/1024/1024) Daily_Avg_GB 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; Query to give a
Post a Comment
Read more

Patching Issues

-
Datapatch -verbose error: 1) ---------- 1)Opatch apply was successful. 2)opatch lsinventory was showing latest patch applied in the inventory. But, When I give datapatch –verbose to apply modified sqls into database as part of post patch steps, we were getting  D:\oracle\product\12.1.0.2\dbhome\OPatch>datapatch -verbose  SQL Patching tool version 12.1.0.2.0 Production on Sat Feb 18 01:13:34 2017  Copyright (c) 2012, 2017, Oracle. All rights reserved.  Log file for this invocation: D:\oracle\product\12.1.0.2\dbhome\cfgtoollogs\sqlpatch\sqlpatch_4772_2017_02_18_01_13_34\sqlpatch_invocation.log  Connecting to database...OK  Bootstrapping registry and package to current versions...done  Queryable inventory could not determine the current opatch status.  Execute 'select dbms_sqlpatch.verify_queryable_inventory from dual'  and/or check the invocation log  D:\oracle\product\12.1.0.2\dbhome\cfgtoollogs\sqlpatch\sqlpatch_4772_2017_02_18_01_13_34\sqlpatch_invocation.log  for the compl
Post a Comment
Read more

Disable Active Dataguard in 11g

-
Need to modify hidden parameter "_query_on_physical" to false to disable active dataguard. Query to check parameter value: SET PAUSE ON SET PAUSE 'Press Return to Continue' SET PAGESIZE 60 SET LINESIZE 300 COLUMN ksppinm FORMAT A50 COLUMN ksppstvl FORMAT A50 SELECT   ksppinm,   ksppstvl FROM   x$ksppi a,   x$ksppsv b WHERE   a.indx=b.indx AND   substr(ksppinm,1,1) = '_' and a.ksppinm like '_%physical%'; ORDER BY ksppinm / KSPPINM                                           ,KSPPSTVL --------------------------------------------------,-------------------------------------------------- _query_on_physical                                ,TRUE SQL> alter system set "_query_on_physical"=False scope=spfile; SQL> SQL> SQL> shut immediate; Database closed. Database dismounted. ORACLE instance shut down. SQL> startup; ORACLE instance started. Total System Global Area, 422670336,bytes Fixed Size              ,   1336960,bytes Variable Size  
Post a Comment
Read more