52
Ausgewählte PL/SQL Packages (Teil 2) DBMS_QOPATCH DBMS_SPACE DBMS_SERVICE DBMS_SERVICE DBMS_FLASHBACK_ARCHIVE Ulrike Schwinn (Follow @uschwinn) Business Unit Database ORACLE Deutschland B.V. & Co. KG.

Ausgewählte PL/SQL Packages (2)

Embed Size (px)

Citation preview

Page 1: Ausgewählte PL/SQL Packages (2)

Ausgewählte PL/SQL Packages (Teil 2)DBMS_QOPATCH DBMS_SPACE DBMS_SERVICE DBMS_SERVICE DBMS_FLASHBACK_ARCHIVE

Ulrike Schwinn (Follow @uschwinn)Business Unit Database

ORACLE Deutschland B.V. & Co. KG.

Page 2: Ausgewählte PL/SQL Packages (2)

1. DBMS_XDB_CONFIG – XDB Port Konfiguration

2. DBMS_COMPRESSION – Compression Ratio, Komprimierte Zeilen

3. DBMS_REDEFINITION – Online Neudefinition von Tabellen

In Teil 1 ...

2 Copyright © 2014, Oracle and/or its affiliates. All rights reserved.

4. DBMS_SQL_MONITOR – Real Time Monitoring

5. DBMS_PARALLEL_EXECUTE – Manuelle Parallellisierung

Folien dazu unter

http://apex.oracle.com/folien

Schlüsselwort: webinar_packages1

Page 3: Ausgewählte PL/SQL Packages (2)

1. DBMS_QOPATCH – Informationen zu Patches mit PL/SQL

2. DBMS_SPACE – Platzverbrauch abschätzen

3. DBMS_SERVICE – Services anlegen, löschen, ändern

Heutige Agenda

3 Copyright © 2014, Oracle and/or its affiliates. All rights reserved.

4. DBMS_FLASHBACK_ARCHIVE – Flashback Data Archive Funktionen

Page 4: Ausgewählte PL/SQL Packages (2)

4 Copyright © 2014, Oracle and/or its affiliates. All rights reserved.

Etwas Grundsätzliches

Page 5: Ausgewählte PL/SQL Packages (2)

Wie findet man obsolete Packages?

� Upgrade Guide 12c Release 1 (12.1) in Kapitel 8 “Deprecated and Desupported Features for Oracle Database 12c”

5 Copyright © 2014, Oracle and/or its affiliates. All rights reserved.

Page 6: Ausgewählte PL/SQL Packages (2)

Wie findet man obsolete Packages?SELECT name FROM

(SELECT synonym_name name FROM dba_synonyms@system11g

WHERE table_name in

(SELECT object_name name FROM dba_objects@system11g

WHERE object_type = 'PACKAGE')

MINUS

SELECT synonym_name name FROM dba_synonyms

WHERE table_name IN

6 Copyright © 2014, Oracle and/or its affiliates. All rights reserved.

WHERE table_name IN

(SELECT object_name name FROM dba_objects

WHERE object_type = 'PACKAGE'))

DBMS_DBLINK

DBMS_EXPFIL

DBMS_RLMGR

STATSPACK …

Page 7: Ausgewählte PL/SQL Packages (2)

Wie findet man PL/SQL Objekte überhaupt?

� Data Dictionary

– ALL_OBJECTS mit object_type=PACKAGE|PACKAGE_BODY|FUNCTION|PROCEDURE

– ALL_SOURCE mit type=PACKAGE|PACKAGE_BODY|FUNCTION|PROCEDURE

Mit SQL Developer 4.0

7 Copyright © 2014, Oracle and/or its affiliates. All rights reserved.

� Mit SQL Developer 4.0

– Funktion Find Database Objects

Page 8: Ausgewählte PL/SQL Packages (2)

Im SQL Developer

8 Copyright © 2014, Oracle and/or its affiliates. All rights reserved.

Page 9: Ausgewählte PL/SQL Packages (2)

Im SQL Developer

9 Copyright © 2014, Oracle and/or its affiliates. All rights reserved.

Page 10: Ausgewählte PL/SQL Packages (2)

10 Copyright © 2014, Oracle and/or its affiliates. All rights reserved.

Das Package DBMS_QOPATCH

Page 11: Ausgewählte PL/SQL Packages (2)

Aufgaben und Funktionen

� Patch Informationen über OPATCH abfragen

opatch lsinventory -XML

� In 12c nun auch in PL/SQL mit DBMS_QOPATCH

� Einige wichtige Funktionen:– GET_OPATCH_BUGS: Bug Liste für bestimmmte Patch Nummer

11 Copyright © 2014, Oracle and/or its affiliates. All rights reserved.

– GET_OPATCH_ LIST: Patch Liste installierter Patches

– GET_OPATCH_LSINVENTORY: Gesamte opatch Inventory

– IS_PATCH_INSTALLED: Abfrage auf einzelne Patches

� Ausgabe in XML

Page 12: Ausgewählte PL/SQL Packages (2)

Gesamtes Inventory in XML FormatSQL> set long 2000000000 pagesize 0 linesize 32000

SQL> SELECT DBMS_QOPATCH.GET_OPATCH_LSINVENTORY FROM dual;

GET_OPATCH_LSINVENTORY

------------------------------------------------------------

<?xml version="1.0" encoding="CP850" standalone='yes'?>

<InventoryInstance>

12 Copyright © 2014, Oracle and/or its affiliates. All rights reserved.

<InventoryInstance>

<oracleHome> <UId>OracleHome-75909c1d-7269-45d1-802e-

75deb9104053</UId> <targetTypeId>oracle_home</targetTypeId>

<patchingModel>oneoff</patchingModel>

<path>/opt/oracle/product/12.1.0/db</path>

<targetTypeId>oracle_home</targetTypeId>

<inventoryLocation>/opt/oraInventory</inventoryLocation> …

Page 13: Ausgewählte PL/SQL Packages (2)

Formatierte Ausgabe von Einzelpatcheshttp://sql-plsql-de.blogspot.co.uk/2014/02/sql-anstatt-opatch-lsinventory-mit.html

SQL> set long 2000000000 pagesize 0 linesize 32000

SQL> SELECT patch_id, apply_time, rollbackable

FROM xmltable

('//patch' passing DBMS_QOPATCH.GET_OPATCH_LSINVENTORY

COLUMNS

patch_id number PATH 'patchID',

13 Copyright © 2014, Oracle and/or its affiliates. All rights reserved.

patch_id number PATH 'patchID',

apply_time timestamp with time zone PATH 'appliedDate',

rollbackable varchar2(10) PATH 'rollbackable')

PATCH_ID APPLY_TIME ROLLBACKAB

---------- ----------------------------------- ----------

16527374 08.11.13 14:02:02,000000 +01:00 true

17027533 08.11.13 10:24:12,000000 +01:00 true

Page 14: Ausgewählte PL/SQL Packages (2)

Abfrage auf einzelne PatchesSQL> SELECT DBMS_QOPATCH.IS_PATCH_INSTALLED(17027533) FROM dual;

DBMS_QOPATCH.IS_PATCH_INSTALLED(17027533)

-----------------------------------------

<patchInfo><patchID xmlns:xsi="http://www.w3.org/2001/XMLSchema-

instance">17027533</patchID><appliedDate

xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">2013-10-

22T14:00:51+02:00</appliedDate><sqlPatch

14 Copyright © 2014, Oracle and/or its affiliates. All rights reserved.

22T14:00:51+02:00</appliedDate><sqlPatch

xmlns:xsi="http://www.w3.org/2001/XMLSchema-

instance">true</sqlPatch><sqlPatchDatabaseStartupMode

xmlns:xsi="http://www.w3.org/2001/XMLSchema-

instance">normal</sqlPatchDatabaseStartupMode></patchInfo>

Page 15: Ausgewählte PL/SQL Packages (2)

15 Copyright © 2014, Oracle and/or its affiliates. All rights reserved.

Das Package DBMS_SPACE

Page 16: Ausgewählte PL/SQL Packages (2)

Aufgaben und Funktionen

�Über DBA_SEGMENTS hinaus ... – Informiert detailliert über den Speicherplatz von Segmenten

– Auch für genaue Analyse von Securefiles geeignet

– Schnittstelle für automatischen Segment Advisor

– Trendberechnungen

16 Copyright © 2014, Oracle and/or its affiliates. All rights reserved.

– Trendberechnungen

– Anzeige der abhängigen Objekte

�Anwendungsbeispiele– Compression Tests zur Analyse des genauen

Platzverbrauchs

– Unterstützung bei SQL Statement Analyse

Page 17: Ausgewählte PL/SQL Packages (2)

Wichtige Komponenten im Einzelnen

Genaue Speicheranalyse (im ASSM) SPACE_USAGE

Freier Platz (im ASSM) UNUSED_SPACE

Kosten für Index/Tabellen Create CREATE_[INDEX/TABLE]_COST

Abhängige Objekte OBJECT_DEPENDENT_SEGMENTS

17 Copyright © 2014, Oracle and/or its affiliates. All rights reserved.

Abhängige Objekte OBJECT_DEPENDENT_SEGMENTS

Objekt Wachstum OBJECT_GROWTH_TREND

Segment Advisor automatischSegment Advisor Empfehlungen

AUTO_SPACE_ADVISOR_JOB_PROCASA_RECOMMENDATIONS

Page 18: Ausgewählte PL/SQL Packages (2)

Automatic Segment Space Management (ASSM)� Freier und genutzter Platz liegt als Bitmap vor.� Ein Bitmap beschreibt wie voll ein Block ist wie z.B.

(0-25, 25-50, 50-75, 75-100%) � Die Funktion SPACE_USAGE bestimmt den genauen Platzverbrauch

� Geignet für – TABLE, TABLE PARTITION, BMB BMB BMB BMB BMB

18 Copyright © 2014, Oracle and/or its affiliates. All rights reserved.

– TABLE, TABLE PARTITION,TABLE SUBPARTITION,INDEX,INDEX PARTITION/SUBPARTITIONCLUSTER, LOBs

BMBBMBBMB

BLOCK

BMB BMB BMB BMB

Page 19: Ausgewählte PL/SQL Packages (2)

SPACE_USAGE für Tabellen

.

set serveroutput ondeclare

v_unformatted_blocks number;v_unformatted_bytes number;v_fs1_blocks number;v_fs1_bytes number;v_fs2_blocks number;v_fs2_bytes number;v_fs3_blocks number;v_fs3_bytes number;v_fs4_blocks number;v_fs4_bytes number;v_full_blocks number;v_full_bytes number;

begindbms_space.space_usage('SCOTT','EMPLOYEE','table',

19 Copyright © 2014, Oracle and/or its affiliates. All rights reserved.

dbms_space.space_usage('SCOTT','EMPLOYEE','table',v_unformatted_blocks,v_unformatted_bytes,v_fs1_blocks,v_fs1_bytes,v_fs2_blocks,v_fs2_bytes,v_fs3_blocks,v_fs3_bytes,v_fs4_blocks,v_fs4_bytes, v_full_blocks,v_full_bytes);

dbms_output.put_line('Unformatted Blocks = ' || v_unformatted_blocks);dbms_output.put_line('Unformatted Bytes = ' || v_unformatted_bytes);dbms_output.put_line('FS1 Bytes (at least 0 to 25% free space) = ' || v_fs1_bytes);dbms_output.put_line('FS1 Blocks(at least 0 to 25% free space) = ' || v_fs1_blocks);dbms_output.put_line('FS2 Bytes (at least 25 to 50% free space)= ' || v_fs2_bytes);dbms_output.put_line('FS2 Blocks(at least 25 to 50% free space)= ' || v_fs2_blocks);dbms_output.put_line('FS3 Bytes (at least 50 to 75% free space) = ' || v_fs3_bytes);dbms_output.put_line('FS3 Blocks(at least 50 to 75% free space) = ' || v_fs3_blocks);dbms_output.put_line('FS4 Bytes (at least 75 to 100% free space) = ' || v_fs4_bytes);dbms_output.put_line('FS4 Blocks(at least 75 to 100% free space)= ' || v_fs4_blocks);dbms_output.put_line('Full Blocks in segment = ' || v_full_blocks);dbms_output.put_line('Full Bytes in segment = ' || v_full_bytes);

end;

Page 20: Ausgewählte PL/SQL Packages (2)

Ergebnis

.Unformatted Blocks = 0Unformatted Bytes = 0FS1 Bytes (at least 0 to 25% free space) = 8192FS1 Blocks(at least 0 to 25% free space) = 1FS2 Bytes (at least 25 to 50% free space)= 0FS2 Blocks(at least 25 to 50% free space)= 0FS3 Bytes (at least 50 to 75% free space) = 0FS3 Blocks(at least 50 to 75% free space) = 0FS4 Bytes (at least 75 to 100% free space) = 425984FS4 Blocks(at least 75 to 100% free space)= 52

20 Copyright © 2014, Oracle and/or its affiliates. All rights reserved.

FS4 Blocks(at least 75 to 100% free space)= 52Full Blocks in segment = 11070Full Bytes in segment = 90685440

PL/SQL procedure successfully completed.

Page 21: Ausgewählte PL/SQL Packages (2)

DBMS_SPACE für SecureFiles

DBMS_SPACE.SPACE_USAGE(segment_owner IN VARCHAR2, segment_name IN VARCHAR2, segment_type IN VARCHAR2, segment_size_blocks OUT NUMBER, segment_size_bytes OUT NUMBER, used_blocks OUT NUMBER, used_bytes OUT NUMBER, expired_blocks OUT NUMBER, expired_bytes OUT NUMBER,

21 Copyright © 2014, Oracle and/or its affiliates. All rights reserved.

� Unbedingt separates LOB Segment mitberücksichtigen! � SHRINK Kommando gibt es nicht mit SecureFiles!

expired_bytes OUT NUMBER, unexpired_blocks OUT NUMBER, unexpired_bytes OUT NUMBER, partition_name IN VARCHAR2 DEFAULT NULL);

Page 22: Ausgewählte PL/SQL Packages (2)

Wie groß ist eine Tabelle?

� Umfangreiches Beispiel, das alle möglichen Segmentarten berücksichtigt

� http://sql-plsql-de.blogspot.co.uk/2009/01/wie-gro-ist-eigentlich-eine-tabelle.html

– Funktioniert in 11g

– Man muß als Eigentümer angemeldet sein

22 Copyright © 2014, Oracle and/or its affiliates. All rights reserved.

– Man muß als Eigentümer angemeldet sein

SQL> SELECT * FROM table(get_space_info('SECURE_LOB'));

SQL> SELECT * FROM table (get_space_info('SALES'));

Page 23: Ausgewählte PL/SQL Packages (2)

Welche Segmente sind abhängig? � Angabe von Segmenttypen als Zahl (1=Tabelle, 2=Nested Table usw.)

SQL> SELECT * FROM table(DBMS_SPACE.OBJECT_DEPENDENT_SEGMENTS(objowner => 'SH',objname => 'CUSTOMERS',partname => null,objtype => 1));

SEGMENT_OWNER SEGMENT_NAME SEGMENT_TYPE

23 Copyright © 2014, Oracle and/or its affiliates. All rights reserved.

SEGMENT_OWNER SEGMENT_NAME SEGMENT_TYPE-------------------- ---------------------------- --------------------TABLESPACE_NAME PARTITION_NAME LOB_COLUMN_NAME--------------- ------------------------- --------------------SH CUSTOMERS TABLEUSERSSH CUSTOMERS_PK INDEXEXAMPLESH CUSTOMERS_GENDER_BIX INDEX

Page 24: Ausgewählte PL/SQL Packages (2)

Im SQL Developer

24 Copyright © 2014, Oracle and/or its affiliates. All rights reserved.

Page 25: Ausgewählte PL/SQL Packages (2)

Größenprognose für Tabellen und Indizes SQL> exec DBMS_SPACE.CREATE_TABLE_COST(

tablespace_name =>'USERS',colinfos => create_table_cost_columns(create_table_cost_colinfo('NUMBER',4),

create_table_cost_colinfo('VARCHAR2',30),create_table_cost_colinfo('NUMBER',6),create_table_cost_colinfo('NUMBER',4)),

row_count => 221184,pct_free => 10,used_bytes => :used_bytes,alloc_bytes => :alloc_bytes);

25 Copyright © 2014, Oracle and/or its affiliates. All rights reserved.

alloc_bytes => :alloc_bytes);

SQL> SELECT :used_bytes, :alloc_bytes FROM dual;

:USED_BYTES :ALLOC_BYTES----------- ------------

11771904 12582912

� Größenprognose auch für Indizes möglich (über DDL Angabe)

Page 26: Ausgewählte PL/SQL Packages (2)

Im EM

26 Copyright © 2014, Oracle and/or its affiliates. All rights reserved.

Page 27: Ausgewählte PL/SQL Packages (2)

Segment Advisor

� 2 verschiedene Funktionsweisen: – Automatic Segment Advisor (in der Automated Maintenance Task)

– Segment Advisor manuell (im EM oder DBMS_ADVISOR)

� Dabei werden berücksichtigt ... – Tablespaces, Tabellen, Partitionen, zug. Indizes und Large Objects

Was ist anders bei Automatic Segment Advisor?

28 Copyright © 2014, Oracle and/or its affiliates. All rights reserved.

� Was ist anders bei Automatic Segment Advisor? – Segmente mit hoher Wachstumsrate und hoher Aktivität in einem

Tablespace, der das Critical oder Warning Threshold überschreitet.

– Zusätzlich: OLTP Compression Empfehlungen für ausreichend große Tabellen mit mindestens 3 Indizes

Page 28: Ausgewählte PL/SQL Packages (2)

Segment Advisor nutzen

� Automatic Segment Advisor in Automated Maintenance Task oder mit

� Segment Advisor (manuell) mit DBMS_ADVISOR

declare …

begin

-- Erzeugen einer Task

SQL> execute DBMS_SPACE.AUTO_SPACE_ADVISOR_JOB_PROC;

29 Copyright © 2014, Oracle and/or its affiliates. All rights reserved.

-- Erzeugen einer Task

DBMS_ADVISOR.CREATE_TASK (…);

-- Angabe von Advisortyp (Segment,Access etc) und Objekttyp

DBMS_ADVISOR.CREATE_OBJECT (…);

-- zusätzliche Parameter

DBMS_ADVISOR.SET_TASK_PARAMETER(…);

-- Ausführung

DBMS_ADVISOR.EXECUTE_TASK(…);

end;

Page 29: Ausgewählte PL/SQL Packages (2)

Segment Advisor - Ergebnisse

� EM Homepage und Drill Down oder

SQL> SELECT tablespace_name, segment_owner, segment_name,

recommendations, C1 Kommando

FROM TABLE (DBMS_SPACE.ASA_RECOMMENDATIONS(all_runs => 'TRUE',

show_manual => 'TRUE', show_findings => 'FALSE'));

TABLESPACE_NAME SEGMENT_OWNE SEGMENT_NAME

------------------------------ ------------ ----------------------

RECOMMENDATIONS

30 Copyright © 2014, Oracle and/or its affiliates. All rights reserved.

RECOMMENDATIONS

-----------------------------------------------------------------

KOMMANDO

------------------------------------------------------------------

EXAMPLE SH CUST_BIG

Enable row movement of the table SH.CUST_BIG and perform shrink, estimated

savings is 247990931 bytes.

alter table "SH"."CUST_BIG" shrink space

...

Page 30: Ausgewählte PL/SQL Packages (2)

Segment Advisor im EM

31 Copyright © 2014, Oracle and/or its affiliates. All rights reserved.

Page 31: Ausgewählte PL/SQL Packages (2)

32 Copyright © 2014, Oracle and/or its affiliates. All rights reserved.

Das Package DBMS_SERVICE

Page 32: Ausgewählte PL/SQL Packages (2)

Datenbank Services

� Services beschreiben Anwendungen/spezielle Funktionen

� Jede Datenbank verfügt über einen oder mehrere Services, die im Datenbank Repository gespeichert sind.

� Wenn eine Instanz startet, registriert sie sich mit einem Listener, indem sie einen Service oder mehrere Services nutzt.

33 Copyright © 2014, Oracle and/or its affiliates. All rights reserved.

sie einen Service oder mehrere Services nutzt.

� Der Listener wird über die dynamische Registrierung des PMONs informiert und leitet dann die Verbindungen zwischen Client und Instanz weiter.

Page 33: Ausgewählte PL/SQL Packages (2)

Einsatz von Services

� Unabdingbar in RAC und Data Guard Umgebungen

� Wichtige Informationsquelle beim Monitoren und Tunen– V$ Views wie v$session,

v$service_event etc.

– AWR Report

– SQL Monitoring etc.

34 Copyright © 2014, Oracle and/or its affiliates. All rights reserved.

– SQL Monitoring etc.

– Scheduler Jobs

� Wichtiger Bestandteil des Database Resource Managers

� Unabdingbar in 12c mit Multitenant Architektur!– Zugriff über Datenbank Service (nicht Instancename)

– Name der PDB als Service

Page 34: Ausgewählte PL/SQL Packages (2)

Erzeugen von Services

� Graphisch mit Net Configuration Assistant/Net Manager– Einträge in tnsnames.ora, listener.ora, sqlnet.ora

� DBMS_SERVICE (meist im Single Instance Umfeld)– Keine graphische Implementierung

� srvctl (für Oracle Clusterware, Restart, Oracle Global Data Services)

35 Copyright © 2014, Oracle and/or its affiliates. All rights reserved.

Page 35: Ausgewählte PL/SQL Packages (2)

Aufgaben im Einzelnen

Erzeugen CREATE_SERVICE (*)

Löschen DELETE_SERVICE (ab 12c (*))

Disconnect aller Sessions bzgl Service DISCONNECT_SESSION

Attribute Ändern MODIFY_SERVICE (*)

36 Copyright © 2014, Oracle and/or its affiliates. All rights reserved.

Attribute Ändern MODIFY_SERVICE (*)

Starten START_SERVICE (*)

Stoppen STOP_SERVICE (*)

(*) Obsolet im Oracle Clusterware, Oracle Restart und Oracle Global Data Services.Umfeld (12c)

Page 36: Ausgewählte PL/SQL Packages (2)

Services in DBA_SERVICES

SQL> SELECT name, network_name FROM dba_services;

NAME NETWORK_NAME

------------------------- ------------------------------

SYS$BACKGROUND

SYS$USERS

orclXDB orclXDB

37 Copyright © 2014, Oracle and/or its affiliates. All rights reserved.

� orcl: Default Service Name = globale Datenbank Name

� SYS$BACKGROUND und SYS$USERS: interne Services

� orclXDB: Service zur Nutzung der XDB

� highprio: user definierter Service

orclXDB orclXDB

orcl orcl

highprio highprio.de.oracle.com

Page 37: Ausgewählte PL/SQL Packages (2)

Einfaches Monitoring

SQL> SELECT service_name, stat_name, value FROM v$service_stats

WHERE service_name IN ('orcl','TEST','XYZ');

SERVICE_NAME STAT_NAME VALUE

-------------- ----------------- ----------

XYZ logons cumulative 1

orcl logons cumulative 201

� Views: dba_services, v$services, v$service_stats und v$session

38 Copyright © 2014, Oracle and/or its affiliates. All rights reserved.

orcl logons cumulative 201

TEST logons cumulative 1

XYZ user calls 17

orcl user calls 588456

TEST user calls 17

XYZ DB time 56341

orcl DB time 881701076

TEST DB time 60114

XYZ DB CPU 16000 ...

Page 38: Ausgewählte PL/SQL Packages (2)

Verwendung

� Service anlegen (auch mit weiteren Parametern)

� Service starten (z.B. auch Trigger after startup on database) exec DBMS_SERVICE.START_SERVICE('highprio');

SQL> exec DBMS_SERVICE.CREATE_SERVICE(

SERVICE_NAME => 'highprio', -- intern

NETWORK_NAME => 'highprio.de.oracle.com'); -- zur Verbindung

39 Copyright © 2014, Oracle and/or its affiliates. All rights reserved.

� Connect (z.B mit Easy Connect)

� Verbindungsabbau der Sessions

sqlplus benutzer/passwort@host:port/highprio.de.oracle.com

exec DBMS_SERVICE.DISCONNECT_SESSION (

service_name => 'highprio',

disconnect_option => DBMS_SERVICE.POST_TRANSACTION);

Page 39: Ausgewählte PL/SQL Packages (2)

40 Copyright © 2014, Oracle and/or its affiliates. All rights reserved.

Das Package DBMS_FLASHBACK_ARCHIVE

Page 40: Ausgewählte PL/SQL Packages (2)

Aufgaben und Funktionen � Wozu überhaupt all diese Flashback Technologien?

– Abfragen um Informationen aus der Vergangenheit zu selektieren

– Rollback von Transaktionen

– Wiederherstellen von gelöschten Tabellen

– Zurücksetzen der gesamten Datenbank ohne Point-in-Time Recovery

Zusätzlich seit Oracle 11g: Versionierung der Daten und Schutz vor

41 Copyright © 2014, Oracle and/or its affiliates. All rights reserved.

� Zusätzlich seit Oracle 11g: Versionierung der Daten und Schutz vor Zugriff mit Flashback Data Archive

� Seit 11.2.0.4 steht die Verwendung von Flashback Data Archive ohne Optimierung in allen Editionen oder mit Optimierung mit Advanced Compression Option zur Verfügung!

Page 41: Ausgewählte PL/SQL Packages (2)

� Archive einrichten

� Aktivierung über einzelne Tabellen

CREATE FLASHBACK ARCHIVE fla_10y TABLESPACE tbs1

QUOTA 100G RETENTION 10 YEAR;

Flashback Data Archive Einrichten

ALTER FLASHBACK DATA ARCHIVE archiv1 SET DEFAULT; …

42 Copyright © 2014, Oracle and/or its affiliates. All rights reserved.

� Aktivierung über einzelne Tabellen

� Zugriff

CREATE TABLE test(ang_nr NUMBER,… ) FLASHBACK ARCHIVE fla_10y

ALTER TABLE emp FLASHBACK ARCHIVE;

SELECT * FROM emp AS OF TIMESTAMP TO_TIMESTAMP('29.10.07 14:01', 'dd.mm.yy hh24:mi')

WHERE ename = 'KING'

Page 42: Ausgewählte PL/SQL Packages (2)

Monitoren

SQL> SELECT * FROM user_flashback_archive_tables;

TABLE_NAME OWNER_NAME FLASHBACK_ARCHIVE_NA

------------------------------ ---------- --------------------

ARCHIVE_TABLE_NAME STATUS

----------------------------------------------------- ---------

43 Copyright © 2014, Oracle and/or its affiliates. All rights reserved.

CUSTOMERS SH FLA_10Y

SYS_FBA_HIST_110158 ENABLED

COUNTRIES SH FLA_10Y

SYS_FBA_HIST_92375 ENABLED

Page 43: Ausgewählte PL/SQL Packages (2)

Hintergrund� Basis der Archive sind Undo-Informationen

– Datenbank muß automatisches Undo-Management nutzen

� Undo-Informationen werden nicht gelöscht, bis diese archiviert wordensind

� Daten werden asynchron von einem eigenen Hintergrundprozess(fbda) erfasst

44 Copyright © 2014, Oracle and/or its affiliates. All rights reserved.

(fbda) erfasst– Default Capture Intervall 5 Minuten– Capture Intervall wird in Abhängigkeit von der Systemauslastung

automatisch geändert– Der Prozess versucht, die Undo-Informationen aus dem Buffer Cache zu

lesen

Page 44: Ausgewählte PL/SQL Packages (2)

� Nicht alle Änderungen werden unterstützt – allerdings– ADD, DROP, RENAME, MODIFY Column– ADD, DROP, RENAME Constraint– DROP, TRUNCATE Partition– RENAME, TRUNCATE Table

� Alle anderen Änderungen möglich mit

Wichtig zu wissen

45 Copyright © 2014, Oracle and/or its affiliates. All rights reserved.

� Alle anderen Änderungen möglich mit– DBMS_FLASHBACK_ARCHIVE.DISASSOCIATE_FBA

– DBMS_FLASHBACK_ARCHIVE.REASSOCIATE_FBA

� Abfragen auch über DDL Änderungen hinweg

Page 45: Ausgewählte PL/SQL Packages (2)

� FDA auf logischer Applikationsebene

12c: Ein Kommando für mehrere Tabellen

execute DBMS_FLASHBACK_ARCHIVE.REGISTER_APPLICATION(

application_name =>'APP1', flashback_archive_name =>'FLA_10Y')

execute DBMS_FLASHBACK_ARCHIVE.ADD_TABLE_TO_APPLICATION

(application_name=>'APP1', table_name=>'T1', schema_name=>'SH')

46 Copyright © 2014, Oracle and/or its affiliates. All rights reserved.

execute DBMS_FLASHBACK_ARCHIVE.ADD_TABLE_TO_APPLICATION

(application_name=>'APP1', table_name=>'T2', schema_name=>'SH')

execute DBMS_FLASHBACK_ARCHIVE.ENABLE_APPLICATION(

application_name=>'APP1')

Page 46: Ausgewählte PL/SQL Packages (2)

� Falls FDA eingeschaltet ist auch ein Locking möglich

12c: Sogar Locking möglich

execute DBMS_FLASHBACK_ARCHIVE.LOCK_DOWN_APPLICATION(

application_name => 'APP1')

SQL> update t1 set country_id=100 ….

47 Copyright © 2014, Oracle and/or its affiliates. All rights reserved.

*

ERROR at line 1:

ORA-55622: DML, ALTER and CREATE UNIQUE INDEX operations are

not allowed on table "SH"."COPY_COUNTRIES"

Page 47: Ausgewählte PL/SQL Packages (2)

12c: Temporal Validity und FDA

� Die Gültigkeit einer Tabellenzeile wird überzusätzliche Spalten wie “Gültig Von" und "Gültig Bis“ definiert

� SQL Abfrage "zu einem Zeitpunkt" mit gültigen Rows möglich

� Nützlich für ILM oder Flashback Abfragen

Bei Änderungen

48 Copyright © 2014, Oracle and/or its affiliates. All rights reserved.

Bei Änderungenwerden Spalten

"Gültig Von" und "Gültig Bis"geschrieben Abfragen erfassen nur

"gültige" Zeilen

Page 48: Ausgewählte PL/SQL Packages (2)

� Hinzufügen von “hidden Spalten” durch CREATE/ALTER

12c: Valid Time Temporal

ALTER TABLE emp_neu ADD PERIOD FOR emp_valid;

Table created.

SELECT column_name, hidden_column, data_type

FROM user_tab_cols WHERE table_name='EMP_NEU';

49 Copyright © 2014, Oracle and/or its affiliates. All rights reserved.

COLUMN_NAME HID DATA_TYPE

--------------------------- --- --------------------------

EMP_VALID YES NUMBER

EMP_VALID_START YES TIMESTAMP(6) WITH TIME

ZONE

EMP_VALID_END YES TIMESTAMP(6) WITH TIME

ZONE

...

EMPNO NO NUMBER

Page 49: Ausgewählte PL/SQL Packages (2)

� Mit DBMS_FLASHBACK_ARCHIVE für alle Queries in der Session

Valid Time Temporal mit FDA

execute DBMS_FLASHBACK_ARCHIVE.ENABLE_AT_VALID_TIME(

LEVEL => 'ASOF',

QUERY_TIME => TO_DATE('20-JAN-2007', 'DD-MON-YYYY'))

execute DBMS_FLASHBACK_ARCHIVE.ENABLE_AT_VALID_TIME(

LEVEL => 'ALL')

50 Copyright © 2014, Oracle and/or its affiliates. All rights reserved.

� Kein AS OF beim SELECT mehr erforderlich …

SELECT * from emp_neu WHERE ename = 'Miller';

LEVEL => 'ALL')

Page 50: Ausgewählte PL/SQL Packages (2)

� Import von weiteren zusätzlichen Historien Informationen mit einer temporären Hilfstabelle

– CREATE_HISTORY_TEMP_TABLE

– IMPORT_HISTORY

� User Kontext Informationen tracken über

Weitere Neuigkeiten in 12c

51 Copyright © 2014, Oracle and/or its affiliates. All rights reserved.

� User Kontext Informationen tracken über– SET_SYS_CONTEXT

– GET_SYS_CONTEXT

Page 51: Ausgewählte PL/SQL Packages (2)

Zusammenfassung

Package Einsatz

DBMS_QOPATCH Patch Informationen mit PL/SQL

DBMS_SPACE Segment Speicherplatz ausgeben

52 Copyright © 2014, Oracle and/or its affiliates. All rights reserved.

DBMS_SERVICE DB Services anlegen und verwalten

DBMS_FLASHBACK_ARCHIVE Flashback Data Archive nutzen

Page 52: Ausgewählte PL/SQL Packages (2)

� Alle Veranstaltungen und Links unter– tinyurl.com/oraclebudb

� Homepage der deutschsprachigen DBA Tipps: blogs.oracle.com/dbacommunity_deutsch

� Dojos: tinyurl.com/dojoonline

Nützliche Informationen und Links

53 Copyright © 2014, Oracle and/or its affiliates. All rights reserved.

� Dojos: tinyurl.com/dojoonline

� Alles auf einen Blick: tinyurl.com/oraclebudb

� Nächstes Webseminar zum Thema PL/SQL Packages am 05. Juni

� Download: apex.oracle.com/folienSchlüsselwort: webinar_packages2