Mamun's DBA Blog: How to Install & Run ORAchk

To run orachk follow the steps below...

Step 1. Download the ORAchk zip from MOS DOC ID 1268927.2 if not have downloaded.

Note: Download the 2^nd one.

Step 2. Check if the directory “/opt/oracle.SupportTools/orachk” exists

     a. If the directory does not exist, create it and unzip the zip file to it.

     b. If the directory exists but ORAchk isn't installed, unzip the zip file.

Step 3. After you’ve completed the installation, you’re ready to run ORAchk from the OS command line as root

    ./orachk –a –o verbose

Step 4. Then get the report as zip by following the directed location at the end.

Mamun's DBA Blog: How To Check Undo Tablespace Size and Free Size in Oracle

First Connect through SQLPlus then,

SQL> select a.tablespace_name, SIZEMB, USAGEMB, (SIZEMB - USAGEMB) FREEMB
     from (select sum(bytes) / 1024 / 1024 SIZEMB, b.tablespace_name from dba_data_files a,
     dba_tablespaces b where a.tablespace_name = b.tablespace_name and b.contents = 'UNDO'
     group by b.tablespace_name) a, (select c.tablespace_name, sum(bytes) / 1024 / 1024 USAGEMB
     from DBA_UNDO_EXTENTS c where status <> 'EXPIRED'group by c.tablespace_name) b
     where a.tablespace_name = b.tablespace_name;

TABLESPACE_NAME     SIZEMB    USAGEMB        FREEMB
--------------- ---------- ---------- -------------------------------------------------
UNDOTBS1                        15360         50.6875           15309.3125
UNDOTBS2                        15360         40.5625           15319.4375

Mamun's DBA Blog: How To Check Redolog File Size in Oracle

Here we go for fetch the size of all redo logs...
SQL> set linesize 300
SQL> column REDOLOG_FILE_NAME format a45
SQL> SELECT
        a.GROUP#,
        a.THREAD#,
        a.SEQUENCE#,
        a.ARCHIVED,
        a.STATUS,
        b.MEMBER    AS REDOLOG_FILE_NAME,
        (a.BYTES/1024/1024) AS SIZE_MB
    FROM v$log a
    JOIN v$logfile b ON a.Group#=b.Group#
    ORDER BY a.GROUP# ASC;

    GROUP#    THREAD#  SEQUENCE#  ARC           STATUS                    REDOLOG_FILE_NAME                    SIZE_MB
---------- ---------- ---------- --- --------- --------------------------------------------- ---------------------------------------------------------------------
         1               1               17309           NO             CURRENT   +RECO/ieclive/onlinelog/group_1.257.860629021        200
         1               1               17309           NO              CURRENT   +DATA/ieclive/onlinelog/group_1.266.860629021       200
         2               1               17306          YES             INACTIVE  +RECO/ieclive/onlinelog/group_2.259.860629023        200
         2               1               17306          YES             INACTIVE  +DATA/ieclive/onlinelog/group_2.268.860629023        200
         3               2               30917          NO              CURRENT   +DATA/ieclive/onlinelog/group_3.261.860629087        200
         3               2               30917          NO              CURRENT   +RECO/ieclive/onlinelog/group_3.261.860629087        200
         4               2               30914         YES              INACTIVE  +DATA/ieclive/onlinelog/group_4.270.860629089         200
         4               2               30914         YES              INACTIVE  +RECO/ieclive/onlinelog/group_4.262.860629089         200
         5               1               17307         YES              INACTIVE  +RECO/ieclive/onlinelog/group_5.258.860629023         200
         5               1               17307         YES              INACTIVE  +DATA/ieclive/onlinelog/group_5.272.860629023         200
  

Mamun's DBA Blog: Gathering Table Stats in Oracle11g

Hello Visitors!!!

• When we gather stats we are updating Optimizer to choose optimal execution plan which enhance the performance of the database.
• We can gather stats at database level,schema level, Table level,Index level.

Table Level Stats :

SQL> Begin DBMS_STATS.GATHER_TABLE_STATS (
         ownname =>'SCOTT',
         tabname =>'EMPLOYEE',
         degree => 2,
         cascade => TRUE,
         METHOD_OPT => 'FOR COLUMNS SIZE AUTO',
         estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE);
         END;
         /


Parameters Defination:-

• Owner-name = Schema Name which holds the table.
• Tabname = Table Name
• Degree  = When you set degree to some value oracle will invoke slave process which speedup the performance.
• cascade = The cascade option can be used to analyze al the indexes.
• METHOD_OPT (FOR COLUMNS SIZE AUTO) = Auto is the Oracle default and this option will analyze histograms  on what the optimizer considers to be "important columns". The optimizer does this by examining your current workload when you run the dbms_stats command,interrogating all current SQL in the library cache to see which columns might benefit from a histogram.
•  estimate_percent = This argument is a new way to allow Oracle's dbms_stats to automatically   estimate  the "best" percentage of a segment to sample when gathering statistics.

Mamun's DBA Blog: How to Check Oracle Database Listener Port Status from Sql & OS Level

Hello Everyone!

You can check listener status in windows environment by using Sql & Os using Netstat.


SQL> select * from gv_$listener_network;

  

C:\Users\oracle> netstat -na |find "1521"

  TCP    0.0.0.0:1521                0.0.0.0:0                       LISTENING
  TCP    10.10.20.244:1521      10.10.20.244:49928     TIME_WAIT
  TCP    10.10.20.244:1521      10.10.20.244:49929     TIME_WAIT
  TCP    10.10.20.244:1521      10.10.20.244:49933     TIME_WAIT
  TCP    10.10.20.244:1521      10.10.20.244:49934     TIME_WAIT
  TCP    10.10.20.244:1521      10.10.20.244:49939     TIME_WAIT
  TCP    10.10.20.244:1521      10.10.20.244:49947     TIME_WAIT
  TCP    10.10.20.244:1521      10.10.20.244:50037     TIME_WAIT
  TCP    10.10.20.244:1521      10.10.20.246:50605     ESTABLISHED
  TCP    10.10.20.244:1521      10.10.20.246:50613     ESTABLISHED
  TCP    10.10.20.244:1521      10.10.20.246:55062     ESTABLISHED
  TCP    10.10.20.244:1521      10.10.20.246:57671     ESTABLISHED
  TCP    10.10.20.244:1521      10.10.20.246:57675     ESTABLISHED
  TCP    10.10.20.244:1521      10.10.20.246:57680     ESTABLISHED



C:\Users\oracle> netstat -a -o -n

Active Connections

  Proto  Local Address    Foreign Address       State           PID
  TCP    0.0.0.0:135            0.0.0.0:0              LISTENING       752
  TCP    0.0.0.0:445            0.0.0.0:0              LISTENING       4
  TCP    0.0.0.0:1158           0.0.0.0:0              LISTENING       2828
  TCP    0.0.0.0:1521           0.0.0.0:0              LISTENING       1984
  TCP    0.0.0.0:1831           0.0.0.0:0              LISTENING       14504
  TCP    0.0.0.0:3389           0.0.0.0:0              LISTENING       5316