Statspack auf dem RAC

Wenn man das Statspack auf einem RAC benutzen will, kann es laut Jonathan Lewis dazu kommen, dass sich die einzelnen Instanzen beim Zugriff auf die DB gegenseitig locken.
"Now there’s no reason why Statspack should lock up a RAC cluster – in principle. But this was an 8-node cluster and if you set up the automatic job to take snapshots by running the default spauto.sql script all eight nodes could start running every hour on the hour – so they would all be fighting constantly for every block of every table and index in the Statspack schema.  (I’m exaggerating for effect, of course, but not by much). You might not notice the global cache contention in a 2-node cluster but eight nodes could, indeed, “lock up the system” at this point."
Sein Artikel bezieht sich zwar auf die 10g, aber es sieht so aus, als könnte es dieses Risiko in der 11g und höher weiterhin geben.
Auch ist RAC mit 2 Nodes wohl nicht gefährdet. Ich habe trotzdem ein wenig weiter gegraben. Hier http://www.oracle-class.com/?p=2384 ist ein guter Artikel, in dem beschrieben wird, wie man das Statspack auf einem 4-Node-RAC ausführen lässt. Die Anleitung bezieht sich ebenfalls auf Jonathan Lewis und nutzt sehr anschaulich DBMS_SCHEDULER und Services um die einzelnen Nodes gezielt anzusprechen.
Die Skripte lassen sich - geringfügig angepasst - so nutzen.

CRSCTL – RAC-Statusausgabe und -verwertung

Eine einfache Möglichkeit, den Status eines RAC auszugeben, bietet CRSCTL.

D:\>crsctl status resource –t 

oder kürzer:

D:\>crsctl stat res –t
--------------------------------------------------------------------------------
NAME           TARGET  STATE        SERVER                   STATE_DETAILS
--------------------------------------------------------------------------------
Local Resources
--------------------------------------------------------------------------------
ora.LISTENER.lsnr
               ONLINE  OFFLINE      rac2
--------------------------------------------------------------------------------
Cluster Resources
--------------------------------------------------------------------------------
ora.LISTENER_SCAN3.lsnr
      1        ONLINE  OFFLINE
ora.orcl.db
      1        OFFLINE OFFLINE                               Instance Shutdown
      2        OFFLINE OFFLINE

Dieser Befehl liefert - gesteuert durch den Parameter "-t" eine eingeschränkte tabellarische Ansicht aller Ressourcen. "-h" liefert möglicher weitere Parameter.

     resName [...]     Ein oder mehrere durch Leerzeichen getrennte Ressourcennamen
     -w                Ressourcenfilter (Beispiel: "TYPE = ora.database.type")
     -p                Gibt statische Konfiguration aus
     -v                Gibt Laufzeitkonfiguration aus\r
     -e                Wertet Sonderwerte einer Ressourceninstanz aus\r
     -f                Gibt vollständige Konfiguration aus\r
     -l                Gibt alle Kardinalitäts- und Grad-Member aus
     -g                Prüft, ob Ressourcen registriert sind\r
     -k                Kardinalitäts-ID\r
     -d                Grad-ID\r
     -n                Servername\r
     -s                Ruft Zielserver für Umspeichern ab\r
     -t                Tabellarische Anzeige

Will man das Ergebnis verskripten, ist die tabellarische Ausgabe ungeeignet. Man lässt besser den Schalter „-t“ weg.

D:\>crsctl stat res
NAME=ora.LISTENER.lsnr
TYPE=ora.listener.type
TARGET=ONLINE
STATE=OFFLINE

NAME=ora.LISTENER_SCAN3.lsnr
TYPE=ora.scan_listener.type
TARGET=ONLINE
STATE=OFFLINE

NAME=ora.orcl.db
TYPE=ora.database.type
TARGET=OFFLINE, OFFLINE
STATE=OFFLINE, OFFLINE

Dieses Ergebnis kann man schon besser in z.B. der Powershell verarbeiten.
Die Ausgabe ist allerdings im Normalfall allumfassend.

Filter - Ich will nur Probleme sehen!

Wenn man nicht die Dinge sehen will, die in Ordnung sind, sondern nur die, die Probleme bedeuten, kann die Ausgabe mit „-w“ gefiltert werden.

D:\skripte>crsctl stat res -t -w "STATE != ONLINE"

Oder

D:\skripte>crsctl stat res -t -w "TARGET != ONLINE"

Das lässt sich hervorragend kombinieren:

D:\skripte>crsctl stat res -t -w "(TARGET != ONLINE OR STATE != ONLINE)"
--------------------------------------------------------------------------------
NAME           TARGET  STATE        SERVER                   STATE_DETAILS
--------------------------------------------------------------------------------
Local Resources
--------------------------------------------------------------------------------
ora.LISTENER.lsnr
               ONLINE  OFFLINE      rac2
ora.gsd
               OFFLINE OFFLINE      rac1
               OFFLINE OFFLINE      rac2
ora.ons
               ONLINE  OFFLINE      rac2
--------------------------------------------------------------------------------
Cluster Resources
--------------------------------------------------------------------------------
ora.LISTENER_SCAN3.lsnr
      1        ONLINE  OFFLINE
ora.orcl.db
      1        OFFLINE OFFLINE                               Instance Shutdown
      2        OFFLINE OFFLINE

GSD und ONS sind uninteressant? OK:

D:\skripte>crsctl stat res -t -w "((TARGET != ONLINE) OR (STATE != ONLINE)) AND (NAME != ora.gsd) AND (NAME != ora.ons)"

Besser als NAME ist die Verwendung von TYPE:

D:\skripte>crsctl stat res -t -w "((TARGET != ONLINE) OR (STATE != ONLINE)) AND (TYPE != ora.gsd.type) AND (TYPE != ora.ons.type)"
--------------------------------------------------------------------------------
NAME           TARGET  STATE        SERVER                   STATE_DETAILS
--------------------------------------------------------------------------------
Local Resources
--------------------------------------------------------------------------------
ora.LISTENER.lsnr
               ONLINE  OFFLINE      rac2
-------------------------------------------------------------------------------
Cluster Resources
--------------------------------------------------------------------------------
ora.LISTENER_SCAN3.lsnr
      1        ONLINE  OFFLINE
ora.orcl.db
      1        OFFLINE OFFLINE                               Instance Shutdown
      2        OFFLINE OFFLINE 

Und das Ganze noch einmal ohne "-t":

D:\skripte>crsctl stat res -w "((TARGET != ONLINE) OR (STATE != ONLINE)) AND (TYPE != ora.gsd.type) AND (TYPE != ora.ons.type)"
NAME=ora.LISTENER.lsnr
TYPE=ora.listener.type
TARGET=ONLINE
STATE=OFFLINE

NAME=ora.LISTENER_SCAN3.lsnr
TYPE=ora.scan_listener.type
TARGET=ONLINE
STATE=OFFLINE

NAME=ora.orcl.db
TYPE=ora.database.type
TARGET=OFFLINE, OFFLINE
STATE=OFFLINE, OFFLINE

Weiterverarbeitung in der Powershell

Auf Grundlage einer gefilterten crsctl-Ausgabe lassen sich verschiedene Möglichkeiten umsetzen.
So könnte man einen Listener, der OFFLINE gegangen ist, neu starten oder den Admin per Email über Probleme benachrichtigen lassen.
Hier ein sehr minimalistisches Beispiel:

$a = crsctl stat res -w "((TARGET != ONLINE) OR (STATE != ONLINE)) AND ((TYPE = ora.listener.type) OR (TYPE = ora.scan_listener.type))";
$to      = "admin@meinefirma.de"
$from    = "rac@meinefirma.de"
$smtp    = "email.meinefirma.de"
$body    = '';
$subject = '';
 
if($a){
    $subject = "Listener OFFLINE!";
    $body    = "Einer oder mehrere Listener sind offline!`r`n`r`n"
    $body   += [string]$a -replace " ", "`r`n"
}
else{}

send-mailmessage -to $to -from $from -body $body -subject $subject -smtpserver $smtp

Das Skript führt zunächst crsctl aus und filtert nach Listenern, die nicht ONLINE sind.
Die Rückgabe wird in eine Email gepackt und versendet.

Korrupte Blöcke bekämpfen mit DBMS_REPAIR

Eine plötzliche Blockcorruption ist keine angenehme Überraschung. Wenn sich die Malaise nicht per Recovery beheben lässt, kann dbms_repair helfen.
Also.
Der Admin stellt auf einmal fest, dass Tabelle xyz_table dank einer blockcorruption nicht komplett lesbar ist oder sich nicht mit dpexp/exp exportieren lässt. (z.B.: ORA-31693: ...ORA-02354: ...ORA-00600: internal error code, arguments: [25027], [4], [394435]...)
Ein Recovery wird verworfen, da die Koruption schon zu lange existiert und andere Daten verloren gingen.
Die Lösung ist DBMS_REPAIR:
Zunächst werden die korrupten Blöcke mit dbms_repair markiert, damit die betroffene Tabelle wieder komplett gelesen/exportiert werden kann.
DBMS_REPAIR benötigt dafür eine Repairtabelle. Diese legt man hiermit an:

SET SERVEROUTPUT ON
DECLARE num_corrupt INT;
BEGIN

num_corrupt := 0;

DBMS_REPAIR.CHECK_OBJECT (
     SCHEMA_NAME => 'BEISPIEL',
     OBJECT_NAME => 'XYZ_TABLE',
     REPAIR_TABLE_NAME => 'REPAIR_TABLE',
     CORRUPT_COUNT =>  num_corrupt);
DBMS_OUTPUT.PUT_LINE('number corrupt: ' || TO_CHAR (num_corrupt));

END;
/

Anschließend jagt man DBMS_REPAIR über die kaputte Tabelle:

SET SERVEROUTPUT ON
DECLARE num_corrupt INT;
BEGIN

num_corrupt := 0;

DBMS_REPAIR.CHECK_OBJECT (
     SCHEMA_NAME => 'BEISPIEL',
     OBJECT_NAME => 'XYZ_TABLE',
     REPAIR_TABLE_NAME => 'REPAIR_TABLE',
     CORRUPT_COUNT =>  num_corrupt);
DBMS_OUTPUT.PUT_LINE('number corrupt: ' || TO_CHAR (num_corrupt));

END;
/

Jetzt kann man in der Repairtabelle nachsehen, was im Argen liegt:

SELECT OBJECT_ID, TABLESPACE_ID tsid,
   RELATIVE_FILE_ID fileid, BLOCK_ID,
   CORRUPT_TYPE, CORRUPT_DESCRIPTION, REPAIR_DESCRIPTION
FROM REPAIR_TABLE;

Hier die ausführliche CORRUPT_DESCRIPTION:

Block Checking: DBA = 16780739, Block Type = KTB-managed data block

data header at 0xf25ec07c
kdbchk: row locked by non-existent transaction
        table=0   slot=1
        lockid=6   ktbbhitc=3

Jetzt, wo man weiß, welche Block-ID – in diesem Fall die 3523 – betroffen ist, kann man mit diesem Select sogar den Inhalt finden und anzeigen:

SELECT * FROM XYZ_TABLE
WHERE DBMS_ROWID.ROWID_BLOCK_NUMBER(rowid) = 3523

Das sieht wirklich kaputt aus... aber mit ein wenig Kenntnis des Datenmodells und der Anwendung weiß man jetzt vielleicht sogar, wie man an die korrekten Daten kommt.
Anschließend kann die Tabelle korrigiert werden - z.B. mittels DBMS_REPAIR.FIX_CORRUPT_BLOCKS().

Wie stoppt man ein "EXPIRE(GRACE)" nach einer PROFILE-Panne?

Wenn man das Default-Profile ändert oder der falschen Usergruppe das falsche Profile zuordnet, haben ganz schnell eine Menge User ein ablaufendes Passwort.

SQL >select count(*) from dba_users
  2  where  account_status = 'EXPIRE(GRACE)';

  COUNT(*)
----------
        78

1 Zeile wurde ausgewählt.

Jetzt ist guter Rat teuer. Ein "alter user account_status 'OPEN';" gibt es nicht. Die Passwörter laufen ab. Man kann nichts dagegen tun, außer das Passwort zu ändern.
Da man die Passwörter aller User für gewöhnlich nicht kennt, hilft hier nur die Passwortänderung mit dem Passwordhash.
Diesen bekommt man in 11g aus der sys.user$. Mit diesem Skript lässt man sich in sqlplus gleich noch ein sql-Skript erstellen, welches die Passworthashes schreibt und damit den Accountstatus zurücksetzt:

rem unexpire_users.sql, MW, 18.06.2013

spool alter_user_unexpire.sql
set echo off
set heading off
set linesize 200
set feedback off

SELECT 'ALTER USER ' || DU.USERNAME || ' IDENTIFIED BY VALUES '''|| SU.PASSWORD ||''';' 
FROM SYS.USER$ SU
INNER JOIN SYS.DBA_USERS DU ON DU.USERNAME = SU.NAME
WHERE DU.ACCOUNT_STATUS = 'EXPIRED(GRACE)'
ORDER BY DU.PROFILE, DU.DEFAULT_TABLESPACE, DU.USERNAME;

spool off
exit

Und jetzt das erstellte SQL-Skript ausführen und fertig:

SQL >@alter_user_unexpire
...
...
SQL >select count(*) from dba_users
  2  where  account_status = 'EXPIRE(GRACE)';

  COUNT(*)
----------
         0

1 Zeile wurde ausgewählt.