THE Wiki

User Tools

Site Tools

Trace: • oracle • raspberry_pi • linux_lessons_learned • java_keystore • alsa_advanced_linux_sound_architecture
oracle

Differences

This shows you the differences between two versions of the page.

Link to this comparison view

Both sides previous revisionPrevious revision
Next revision		Previous revision
oracle [2024/07/17 08:07] – [Dataguard related] mduerschoracle [2025/08/14 09:19] (current) – [ACLs] mduersch
Line 1: Line 1:
 ====== ORACLE ====== ====== ORACLE ======
  
-==== Tablesoace Usage ====+==== ACLs ==== 
 + 
 +ACLs abfragen: 
 + 
 +  SET LINESIZE 150 
 +  COLUMN host FORMAT A40 
 +  COLUMN acl FORMAT A50 
 +  SELECT host, lower_port, upper_port, acl 
 +  FROM   dba_network_acls 
 +  ORDER BY host; 
 + 
 + 
 + 
 +  SET LINESIZE 150 
 +  COLUMN acl FORMAT A50 
 +  COLUMN principal FORMAT A20 
 +  COLUMN privilege FORMAT A10 
 +  SELECT acl, 
 +       principal, 
 +       privilege, 
 +       is_grant, 
 +       TO_CHAR(start_date, 'DD-MON-YYYY') AS start_date, 
 +       TO_CHAR(end_date, 'DD-MON-YYYY') AS end_date 
 +  FROM   dba_network_acl_privileges 
 +  ORDER BY acl, principal, privilege; 
 + 
 +Source: https://oracle-base.com/articles/12c/fine-grained-access-to-network-services-enhancements-12cr1 
 +==== Spool ==== 
 + 
 +Spool output as csv 
 + 
 +  connect <user> 
 +  set markup csv on; 
 +  spool /tmp/data.out 
 +  select * from <table>; 
 +  spool off; 
 + 
 +==== Sessions ==== 
 + 
 + SQL> set line 1000 
 + SQL> col NETWORK_SERVICE_BANNER for a100 
 + SQL> select SID,SERIAL#,NETWORK_SERVICE_BANNER from V$SESSION_CONNECT_INFO order by SID,SERIAL#; 
 +  SID SERIAL# NETWORK_SERVICE_BANNER 
 +  ---------- ----------  ---------------------------------------------------------------------------------------------------- 
 +  76 0 
 +  80 0 
 +  133 15475 TCP/IP NT Protocol Adapter for Linux: Version 19.0.0.0.0 - Production 
 +  133 15475 Encryption service for Linux: Version 19.0.0.0.0 - Production 
 +  133 15475 AES256 Encryption service adapter for Linux: Version 19.0.0.0.0 - Production        ###　Make sure it     is encrypted with AES256 ### 
 +  133 15475 Crypto-checksumming service for Linux: Version 19.0.0.0.0 - Production 
 + 
 +Besser: 
 +  SELECT 
 +   sid, 
 +   username, 
 +   schemaname, 
 +   osuser, 
 +   terminal, 
 +   program 
 +  FROM sys.gv_$session s 
 +  WHERE (inst_id, sid) not in 
 +    (SELECT inst_id, sid 
 +       FROM sys.gv_$session_connect_info 
 +        WHERE network_service_banner like '%Encryption service adapter%' 
 +         or network_service_banner like '%Crypto-checksumming service adapter%') 
 +  and TYPE <> 'BACKGROUND';  
 + 
 +==== Grants by User ==== 
 + 
 +  set long 200000 pages 0 lines 131 
 +  column meta format a121 word_wrapped 
 +  select dbms_metadata.get_ddl('USER', '&&username') meta from dual; 
 +  select dbms_metadata.get_granted_ddl('OBJECT_GRANT', '&&username') meta from dual; 
 +  select dbms_metadata.get_granted_ddl('ROLE_GRANT', '&&username') meta from dual; 
 +  select dbms_metadata.get_granted_ddl('SYSTEM_GRANT', '&&username') meta from dual; 
 + 
 +==== DBexport ==== 
 + 
 + 
 +  DBexport sid=SIDNAME mod=dpump "CONTENT=METADATA_ONLY"  
 +  expdp -help 
 +  
 +==== Tables ==== 
 + 
 +size of table  
 + 
 +   SELECT SUM(bytes)/1024/1024 AS "Table Size (MB)" FROM user_segments WHERE segment_name='&tablename'; 
 + 
 +==== Tablespace Usage ====
  
    select sum(round(bytes/1024/1024/1024)) as Summe_Size_GB, sum(round(MAXBYTES/1024/1024/1024)) as Summe_Max_Size_GB from dba_data_files where TABLESPACE_NAME='DATA';     select sum(round(bytes/1024/1024/1024)) as Summe_Size_GB, sum(round(MAXBYTES/1024/1024/1024)) as Summe_Max_Size_GB from dba_data_files where TABLESPACE_NAME='DATA'; 
Line 60: Line 148:
    /    /
  
 +   Source: https://blog.zeddba.com/2019/05/31/online-redo-log-switch-frequency-map/ 
 +
 +==== Logswitches per Hour ====
 +
 +   set pages 999 lines 400
 +   col h0 format 999
 +   col h1 format 999
 +   col h2 format 999
 +   col h3 format 999
 +   col h4 format 999
 +   col h5 format 999
 +   col h6 format 999
 +   col h7 format 999
 +   col h8 format 999
 +   col h9 format 999
 +   col h10 format 999
 +   col h11 format 999
 +   col h12 format 999
 +   col h13 format 999
 +   col h14 format 999
 +   col h15 format 999
 +   col h16 format 999
 +   col h17 format 999
 +   col h18 format 999
 +   col h19 format 999
 +   col h20 format 999
 +   col h21 format 999
 +   col h22 format 999
 +   col h23 format 999
 +   SELECT TRUNC (first_time) "Date", inst_id, 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 gv$log_history
 +   WHERE thread# = inst_id
 +   AND first_time > sysdate -7
 +   GROUP BY TRUNC (first_time), inst_id, TO_CHAR (first_time, 'Dy')
 +   ORDER BY 1,2;
 +
 +Source: https://blog.zeddba.com/2019/05/31/online-redo-log-switch-frequency-map/
 +
 +
 +==== Sessions (to kill?) ====
 +
 +  set lines 200
 +  set pages 999
 +
 +  col username for a12
 +  col osuser for a16
 +  col terminal for a12
 +  col machine for a14
 +  col program for a32
 +  --col INST_ID for a8
 +  --col SID for a8
 +  col INST_ID for 9
 +  col SID for 99999999
 +  col LOGON_TIME for a19
 +  col HOST_NAME for a10
 +  col STATUS for a10
 +  col terminal for a20
 +  col STATE for a12 
 +  col SID_SERIAL# for a16
 +
 +  select SID||','||SERIAL# SID_SERIAL#,USERNAME,STATUS
 +  ,OSUSER,MACHINE, substr(PROGRAM,1,32) PROGRAM, to_char(LOGON_TIME,'DD.MM.yyyy HH24:MI:SS')LOGON_TIME
 +   from gv$session
 +   where type='USER';
 +
 +   # Danach ggf:   
 +   ALTER SYSTEM KILL SESSION 'sid,serial#';
  
 ==== AWR ==== ==== AWR ====
Line 100: Line 281:
 ls -l /dev/disk/by-id/wwn* | awk -F 'wwn-0x' '{print $2}' | tr -d './' | sed s/-\>/,/ | tr -d ' ' > linux-diskinfos.csv ls -l /dev/disk/by-id/wwn* | awk -F 'wwn-0x' '{print $2}' | tr -d './' | sed s/-\>/,/ | tr -d ' ' > linux-diskinfos.csv
  
-==== SQL ID ====+==== PLAN HASH SQL ID ====
  
 SELECT sql_id, plan_hash_value, substr(sql_text,1,40) sql_text FROM v$sql; SELECT sql_id, plan_hash_value, substr(sql_text,1,40) sql_text FROM v$sql;
Line 135: Line 316:
   order  by 2 desc   order  by 2 desc
  
-   
  
 Recovery: Recovery:
Line 184: Line 364:
    select * from v$database_incarnation;    select * from v$database_incarnation;
    select * from v$archived_log order by recid;    select * from v$archived_log order by recid;
 +   select * from v$block_change_tracking; 
    select dest_id, destination, status, error, transmit_mode from v$archive_dest where destination is not null;    select dest_id, destination, status, error, transmit_mode from v$archive_dest where destination is not null;
    # Paramter abfragen    # Paramter abfragen
Line 207: Line 388:
    # ARCHIVE DEST ABSCHALTEN    # ARCHIVE DEST ABSCHALTEN
    alter system set log_archive_dest_state_2 = defer;    alter system set log_archive_dest_state_2 = defer;
-   alter system set log_archive_dest_2 = ''; +   alter system reset log_archive_dest_2 sid='*' scope=both;  
-   alter system set log_archive_config = '';+   alter system reset log_archive_config sid='*' scope=both; 
  
 +
 +   set pagesize 100
 +   set linesize 100
 +   colum segment_name format A50
 +   colum object_name format A50
 +
 +   # Größe von Index Tabelle bestimmen    
 +   select 
 +     segment_name, 
 +     round( bytes/1024/1024/1024,1) as GB
 +   from 
 +     dba_segments 
 +   where 
 +     owner like 'GA_%' and 
 +     segment_type='INDEX';
 +
 +   # Erstelldatum der Indexe    
 +   select 
 +     object_name, 
 +       created 
 +   from 
 +     dba_objects 
 +   where 
 +     owner like 'GA_%' and 
 +     object_type='INDEX'
 +   order by created;
 +  
 +   # Größe und Zahl der vorhandenen / bereits duplizierten Tablepsaces
 +   select 
 +     segment_type, 
 +     round(sum(bytes)/1024/1024/1024,1) as GB 
 +   from 
 +     dba_segments 
 +   where 
 +    owner like 'GA_%'
 +   group by segment_type;
  
 ==== Selects ==== ==== Selects ====
oracle.1721203672.txt.gz · Last modified: 2024/07/17 08:07 by mduersch