Tag Archives: useful

Books: Oracle Data Guard 11gR2 Administration…

With all my pleasure I’m glad to announce my friend’s book  «Oracle Data Guard 11gR2 Administration Beginner’s Guide».

All you need to know about disaster recovery technique you’ll find at these pages!

DG_Image Table of Contents

Preface
Chapter 1: Getting Started
Chapter 2: Configuring the Oracle Data Guard Physical Standby Database
Chapter 3: Configuring Oracle Data Guard Logical Standby Database
Chapter 4: Oracle Data Guard Broker
Chapter 5: Data Guard Protection Modes
Chapter 6: Data Guard Role Transitions
Chapter 7: Active Data Guard, Snapshot Standby, and Advanced Techniques
Chapter 8: Integrating Data Guard with the Complete Oracle Environment
Chapter 9: Data Guard Configuration Patching
Chapter 10: Common Data Guard Issues
Chapter 11: Data Guard Best Practices
Pop Quiz Answers
Index

  • Preface

Продолжить чтение этой записи

Реклама

Useful scripts: Get DDL for jobs, created by DBMS_JOB…

Here is a script, which is dedicated to generate DDL commands to recreate jobs, created by DBMS_JOB package.

DECLARE
 CURSOR job_list
 IS
 SELECT JOB FROM dba_jobs;
mysql VARCHAR2 (32767);
BEGIN
 FOR job_id IN job_list
 LOOP
 mysql := '';
 DBMS_OUTPUT.put_line ('BEGIN');
 DBMS_OUTPUT.put_line ('DBMS_JOB.REMOVE(' || job_id.job || ');');
 DBMS_JOB.user_export (job_id.job, mysql);
 DBMS_OUTPUT.put_line (mysql);
 DBMS_OUTPUT.put_line ('END;');
 DBMS_OUTPUT.put_line ('/');
 END LOOP;
END;
/

Useful scripts: Log file switch frequency statistsic

Very nice and easy to use query.

SELECT trunc(first_time) "Date",
 to_char(first_time, 'Dy') "Day",
 count(1) "Total",
 SUM(decode(to_char(first_time, 'hh24'), '00', 1, 0)) "h0",
 SUM(decode(to_char(first_time, 'hh24'), '01', 1, 0)) "h1",
 SUM(decode(to_char(first_time, 'hh24'), '02', 1, 0)) "h2",
 SUM(decode(to_char(first_time, 'hh24'), '03', 1, 0)) "h3",
 SUM(decode(to_char(first_time, 'hh24'), '04', 1, 0)) "h4",
 SUM(decode(to_char(first_time, 'hh24'), '05', 1, 0)) "h5",
 SUM(decode(to_char(first_time, 'hh24'), '06', 1, 0)) "h6",
 SUM(decode(to_char(first_time, 'hh24'), '07', 1, 0)) "h7",
 SUM(decode(to_char(first_time, 'hh24'), '08', 1, 0)) "h8",
 SUM(decode(to_char(first_time, 'hh24'), '09', 1, 0)) "h9",
 SUM(decode(to_char(first_time, 'hh24'), '10', 1, 0)) "h10",
 SUM(decode(to_char(first_time, 'hh24'), '11', 1, 0)) "h11",
 SUM(decode(to_char(first_time, 'hh24'), '12', 1, 0)) "h12",
 SUM(decode(to_char(first_time, 'hh24'), '13', 1, 0)) "h13",
 SUM(decode(to_char(first_time, 'hh24'), '14', 1, 0)) "h14",
 SUM(decode(to_char(first_time, 'hh24'), '15', 1, 0)) "h15",
 SUM(decode(to_char(first_time, 'hh24'), '16', 1, 0)) "h16",
 SUM(decode(to_char(first_time, 'hh24'), '17', 1, 0)) "h17",
 SUM(decode(to_char(first_time, 'hh24'), '18', 1, 0)) "h18",
 SUM(decode(to_char(first_time, 'hh24'), '19', 1, 0)) "h19",
 SUM(decode(to_char(first_time, 'hh24'), '20', 1, 0)) "h20",
 SUM(decode(to_char(first_time, 'hh24'), '21', 1, 0)) "h21",
 SUM(decode(to_char(first_time, 'hh24'), '22', 1, 0)) "h22",
 SUM(decode(to_char(first_time, 'hh24'), '23', 1, 0)) "h23",
 round(count(1) / 24, 2) "Avg"
 FROM V$log_history
 group by trunc(first_time), to_char(first_time, 'Dy')
 Order by 1

Useful scripts: Catch a session, causing ‘cursor: pin S wait on X’…

For sure, every network from time to time aborts connection. Especially this commonly could be seen in WANs.

When this happens while a session is fetching data or using some other object through db-link, PMON for some reason (from time to time) is unable to kill this session, so session stucks, leading to ‘cursor: pin S wait on X’ wait event.

It’s quite difficult to find out, which session you need to kill to release memory lock, so here is the query for this:

SELECT *
 FROM v$session
 WHERE sid IN
 (SELECT TO_NUMBER (SUBSTR (TO_CHAR (RAWTOHEX (p2raw)), 1, 8),
 'XXXXXXXX') sid
 FROM v$session
 WHERE event = 'cursor: pin S wait on X')

The following screenshot shows reducing Latch/mutex waits:

Useful scripts: Get a list of objects to be moved for a data file resize

Every DBA from time to time wants to reduce a size of a data file, thinking ‘There is a lot of free space in datafile, why don’t to resize it?’ But the secret is that shrinking tables is not very helpful in this case — datafile least size will be limited to the block, where the last table’s/index’s block resides.

So, the solution is to relocate used blocks to free gaps between blocks.

We can achieve this by several ways:

  • ALTER TABLE …. MOVE or INDEX REDUILD;
  • use DBMS_REDIFINITION package;
  • Export table, purge it and import again with the help of DataPump.

But prior we need to get a list of objects, which need to be relocated to make datafile reduce possible. The following query show such objects:

DECLARE
   V_FILE_ID       NUMBER;
   V_BLOCK_SIZE    NUMBER;
   V_RESIZE_SIZE   NUMBER;
BEGIN
   V_FILE_ID := &FILE_ID;
   V_RESIZE_SIZE := &RESIZE_FILE_TO;
   SELECT BLOCK_SIZE
     INTO V_BLOCK_SIZE
     FROM V$DATAFILE
    WHERE FILE# = V_FILE_ID;

   DBMS_OUTPUT.PUT_LINE ('.');
   DBMS_OUTPUT.PUT_LINE ('.');
   DBMS_OUTPUT.PUT_LINE ('.');
   DBMS_OUTPUT.PUT_LINE (
         'OBJECTS IN FILE '
      || V_FILE_ID
      || ' THAT MUST MOVE IN ORDER TO RESIZE THE FILE TO '
      || V_RESIZE_SIZE
      || ' BYTES');
   DBMS_OUTPUT.PUT_LINE (
      '=======================================================================');
   FOR my_record
      IN (  SELECT DISTINCT
                   (   OWNER
                    || '.'
                    || SEGMENT_NAME
                    || ' - OBJECT TYPE = '
                    || SEGMENT_TYPE)
                      ONAME
              FROM DBA_EXTENTS
             WHERE (block_id + blocks - 1) * V_BLOCK_SIZE > V_RESIZE_SIZE
                   AND FILE_ID = V_FILE_ID
          ORDER BY 1)
   LOOP
      DBMS_OUTPUT.PUT_LINE (my_record.ONAME);
   END LOOP;
END;
/

Useful scripts: Table list with stale statistics

This script is intended for daily use to get tables where percentage of changed records is above 10%:

SELECT DT.OWNER,
       DT.TABLE_NAME,
       ROUND ( (DELETES + UPDATES + INSERTS) / NUM_ROWS * 100) PERCENTAGE
FROM   dba_tables dt, ALL_TAB_MODIFICATIONS atm
WHERE      DT.OWNER = ATM.TABLE_OWNER
       AND DT.TABLE_NAME = ATM.TABLE_NAME
       AND NUM_ROWS > 0
       AND ROUND ( (DELETES + UPDATES + INSERTS) / NUM_ROWS * 100) >= 10
ORDER BY 3 desc

Also you can avoid system schemas by adding:

AND OWNER NOT IN ('SYS','SYSTEM','DBSNMP','OSMMON','PERFSTAT')

Useful scripts: Gather some session statistics…

RDBMS Version: 10g.
Script shows following session statistic values:
  • PGA Memory, in MB;
  • CPU, used by session;
  • Hard Parse, %;
  • Physical read bytes, in MB;
  • Physical write bytes, in MB;
  • Redo size, in MB;
  • Received from client, in MB;
  • Sent to client, in MB.
  SELECT Logon_time,
         (SELECT ROUND (VALUE / 1024 / 1024, 2)
            FROM v$sesstat
           WHERE STATISTIC# = 25 AND v$sesstat.SID = v$session.sid)
            AS "PGA Memory, in MB",
         (SELECT VALUE
            FROM v$sesstat
           WHERE STATISTIC# = 12 AND v$sesstat.SID = v$session.sid)
            AS "CPU, used by session",
         ROUND ( (SELECT VALUE
                    FROM v$sesstat
                   WHERE STATISTIC# = 339 AND v$sesstat.SID = v$session.sid)
                / (SELECT DECODE (VALUE, 0, 1, VALUE)
                     FROM v$sesstat
                    WHERE STATISTIC# = 338 AND v$sesstat.SID = v$session.sid),
                2)
            AS "Hard Parse, %",
         (SELECT ROUND (VALUE / 1024 / 1024, 2)
            FROM v$sesstat
           WHERE STATISTIC# = 58 AND v$sesstat.SID = v$session.sid)
            AS "Physical read bytes, in MB",
         (SELECT ROUND (VALUE / 1024 / 1024, 2)
            FROM v$sesstat
           WHERE STATISTIC# = 66 AND v$sesstat.SID = v$session.sid)
            AS "Physical write bytes, in MB",
         (SELECT ROUND (VALUE / 1024 / 1024, 2)
            FROM v$sesstat
           WHERE STATISTIC# = 139 AND v$sesstat.SID = v$session.sid)
            AS "Redo size, in MB",
         (SELECT ROUND (VALUE / 1024 / 1024, 2)
            FROM v$sesstat
           WHERE STATISTIC# = 344 AND v$sesstat.SID = v$session.sid)
            AS "Received from client, in MB",
         (SELECT ROUND (VALUE / 1024 / 1024, 2)
            FROM v$sesstat
           WHERE STATISTIC# = 343 AND v$sesstat.SID = v$session.sid)
            AS "Sent to client, in MB",
         SID,
         SERIAL#,
         v$session.STATUS,
         PROGRAM,
         USER#,
         USERNAME,
         COMMAND,
         OWNERID,
         OSUSER,
         PROCESS,
         MACHINE,
         OBJECT_NAME
    FROM    v$session
         LEFT OUTER JOIN
            DBA_OBJECTS
         ON v$session.ROW_WAIT_OBJ# = dba_objects.object_ID
   WHERE v$session.LOGON_TIME BETWEEN TRUNC (SYSDATE) AND SYSDATE
         --AND v$session.STATUS = 'ACTIVE'
ORDER BY 5 DESC

Oracle 11g have changed statistac ID’s there for I put the same script for 11g:

SELECT Logon_time,
       (SELECT ROUND (VALUE / 1024 / 1024, 2)
          FROM v$sesstat
         WHERE STATISTIC# = 35 AND v$sesstat.SID = v$session.sid)
          AS "PGA Memory, in MB",
       (SELECT VALUE
          FROM v$sesstat
         WHERE STATISTIC# = 17 AND v$sesstat.SID = v$session.sid)
          AS "CPU, used by session",
       ROUND ( (SELECT VALUE
                  FROM v$sesstat
                 WHERE STATISTIC# = 584 AND v$sesstat.SID = v$session.sid)
              / (SELECT DECODE (VALUE, 0, 1, VALUE)
                   FROM v$sesstat
                  WHERE STATISTIC# = 583 AND v$sesstat.SID = v$session.sid),
              2)
          AS "Hard Parse, %",
       (SELECT ROUND (VALUE / 1024 / 1024, 2)
          FROM v$sesstat
         WHERE STATISTIC# = 83 AND v$sesstat.SID = v$session.sid)
          AS "Physical read bytes, in MB",
       (SELECT ROUND (VALUE / 1024 / 1024, 2)
          FROM v$sesstat
         WHERE STATISTIC# = 96 AND v$sesstat.SID = v$session.sid)
          AS "Physical write bytes, in MB",
       (SELECT ROUND (VALUE / 1024 / 1024, 2)
          FROM v$sesstat
         WHERE STATISTIC# = 185 AND v$sesstat.SID = v$session.sid)
          AS "Redo size, in MB",
       (SELECT ROUND (VALUE / 1024 / 1024, 2)
          FROM v$sesstat
         WHERE STATISTIC# = 590 AND v$sesstat.SID = v$session.sid)
          AS "Received from client, in MB",
       (SELECT ROUND (VALUE / 1024 / 1024, 2)
          FROM v$sesstat
         WHERE STATISTIC# = 589 AND v$sesstat.SID = v$session.sid)
          AS "Sent to client, in MB",
       SID,
       SERIAL#,
       v$session.STATUS,
       PROGRAM,
       USER#,
       USERNAME,
       COMMAND,
       OWNERID,
       OSUSER,
       PROCESS,
       MACHINE,
       OBJECT_NAME
  FROM    v$session
       LEFT OUTER JOIN
          DBA_OBJECTS
       ON v$session.ROW_WAIT_OBJ# = dba_objects.object_ID
 WHERE v$session.LOGON_TIME BETWEEN TRUNC (SYSDATE) AND SYSDATE
       --AND v$session.STATUS = 'ACTIVE'
ORDER BY 5 DESC
%d такие блоггеры, как: