{"id":134,"date":"2011-11-14T09:49:44","date_gmt":"2011-11-14T08:49:44","guid":{"rendered":"https:\/\/oracle-in-zen.de\/?p=134"},"modified":"2018-06-01T16:47:46","modified_gmt":"2018-06-01T15:47:46","slug":"tabellengrose-sys-aud-sys-fga_log","status":"publish","type":"post","link":"https:\/\/oracle-in-zen.de\/?p=134","title":{"rendered":"Tabellengr\u00f6\u00dfe SYS.AUD$, SYS.FGA_LOG$"},"content":{"rendered":"<p><span class=\"dropcap2\">S<\/span>t\u00e4ndige Vergr\u00f6\u00dferung des SYSTEM Tablespaces in einer Oracle 11g DB.<\/p>\n<p>Dies kann durch aktiviertes Auditing auftreten. Sollte die Datenbank mit Hilfe des DBCA erstellt worden sein, wird der Parameter AUDIT_TRAIL=DB gesetzt.<br \/>\n<!--more--><br \/>\nProtokolliert\u00a0wird in diesem Fall in der Datenbank bzw. in der Tabelle\u00a0SYS.AUD$. Die erh\u00f6hte Sicherheit ist eine gute Sache, allerdings wurde seitens Oracle keine Bereinigung oder Archivierung gesetzt.<\/p>\n<p>Sollte das Audit nicht ben\u00f6tigt werden:<\/p>\n<pre>alter system set AUDIT_TRAIL=NONE scope=spfile;\r\nshutdown immediate\r\nstartup\r\ntruncate table SYS.AUD$;\r\ntruncate table SYS.FGA_LOG$; -- bef\u00fcllt bei fine-grained auditing<\/pre>\n<p>Wird Wert auf das Audit gelegt, sollte ein regelm\u00e4\u00dfiges leeren \u00a0bzw. archivieren von\u00a0SYS.AUD$ und SYS.FGA_LOG$ eingebaut werden:<\/p>\n<p>leeren Prozedur:<\/p>\n<pre>create or replace procedure leeren_audit_trail (30) as leeren_datum date;\r\nbegin\r\n  leeren_datum := trunc(sysdate-date);\r\n  dbms_system.ksdwrt(2,&#039;Leeren Audit Trail bis &#039; || leeren_datum || &#039; beginnt&#039;);\r\n  delete from sys.aud$ where ntimestamp# &lt; leeren_datum;\r\n  commit;\r\n  dbms_system.ksdwrt(2,&#039;Leeren Audit Trail bis &#039; || leeren_datum || &#039; abgeschlossen&#039;);\r\nend;\r\n\/<\/pre>\n<p>und anschlie\u00dfende Ausf\u00fchrung per Job:<\/p>\n<pre>BEGIN\r\n  sys.dbms_scheduler.create_job(\r\n    job_name =&gt; &#039;leeren_audit&#039;,\r\n    job_type =&gt; &#039;plsql_block&#039;,\r\n    job_action =&gt; &#039;begin leeren_audit_trail(1); end;&#039;,\r\n    schedule_name =&gt; &#039;maintenance_window_group&#039;,\r\n    job_class =&gt; &#039;&quot;default_job_class&quot;&#039;,\r\n    auto_drop =&gt; false,\r\n    enabled =&gt; true);\r\nEND;\r\n\/<\/pre>\n<p>Weitere offizielle Oracle Infos zu AUDIT_TRAIL:<\/p>\n<ul>\n<li><code>none<\/code>\u00a0or\u00a0<code>false<\/code><\/li>\n<\/ul>\n<p style=\"padding-left: 30px;\">Disables database auditing.<\/p>\n<ul>\n<li>os<\/li>\n<\/ul>\n<p style=\"padding-left: 30px;\">Enables database auditing and directs all audit records to the operating system&#8217;s audit trail.<\/p>\n<ul>\n<li><code>db<\/code>\u00a0 or \u00a0<code>true<\/code><\/li>\n<\/ul>\n<p style=\"padding-left: 30px;\">Enables database auditing and directs all audit records to the database audit trail (the\u00a0<code>SYS.AUD$<\/code>\u00a0table).<\/p>\n<ul>\n<li>db_extended<\/li>\n<\/ul>\n<p style=\"padding-left: 30px;\">Enables database auditing and directs all audit records to the database audit trail (the\u00a0<code>SYS.AUD$<\/code>\u00a0table). In addition, populates the\u00a0<code>SQLBIND<\/code>\u00a0and\u00a0<code>SQLTEXT<\/code>\u00a0CLOB columns of the<code>SYS.AUD$<\/code>\u00a0table.<\/p>\n<p>&nbsp;<\/p>\n<p>Noch mehr offizielle Infos zum Thema DB Audit:<br \/>\n<a title=\"About Auditing\" href=\"http:\/\/download.oracle.com\/docs\/cd\/E11882_01\/network.112\/e16543\/auditing.htm#DBSEG006\" target=\"_blank\">http:\/\/download.oracle.com\/docs\/cd\/E11882_01\/network.112\/e16543\/auditing.htm#DBSEG006<\/a><\/p>\n","protected":false},"excerpt":{"rendered":"<p>St\u00e4ndige Vergr\u00f6\u00dferung des SYSTEM Tablespaces in einer Oracle 11g DB. Dies kann durch aktiviertes Auditing auftreten. Sollte die Datenbank mit Hilfe des DBCA erstellt worden sein, wird der Parameter AUDIT_TRAIL=DB gesetzt.<\/p>\n","protected":false},"author":1,"featured_media":588,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"zakra_page_container_layout":"customizer","zakra_page_sidebar_layout":"customizer","zakra_remove_content_margin":false,"zakra_sidebar":"customizer","zakra_transparent_header":"customizer","zakra_logo":0,"zakra_main_header_style":"default","zakra_menu_item_color":"","zakra_menu_item_hover_color":"","zakra_menu_item_active_color":"","zakra_menu_active_style":"","zakra_page_header":true,"footnotes":""},"categories":[11],"tags":[12,14,13,17,18,16,15],"class_list":["post-134","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-database-2","tag-11g","tag-audit","tag-database","tag-leeren","tag-script","tag-sicherheit","tag-system"],"_links":{"self":[{"href":"https:\/\/oracle-in-zen.de\/index.php?rest_route=\/wp\/v2\/posts\/134","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/oracle-in-zen.de\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/oracle-in-zen.de\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/oracle-in-zen.de\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/oracle-in-zen.de\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=134"}],"version-history":[{"count":72,"href":"https:\/\/oracle-in-zen.de\/index.php?rest_route=\/wp\/v2\/posts\/134\/revisions"}],"predecessor-version":[{"id":136,"href":"https:\/\/oracle-in-zen.de\/index.php?rest_route=\/wp\/v2\/posts\/134\/revisions\/136"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/oracle-in-zen.de\/index.php?rest_route=\/wp\/v2\/media\/588"}],"wp:attachment":[{"href":"https:\/\/oracle-in-zen.de\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=134"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/oracle-in-zen.de\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=134"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/oracle-in-zen.de\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=134"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}