THE Wiki

This page is read only. You can view the source, but not change it. Ask your administrator if you think this is wrong.
====== ORACLE ======

==== 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'; 


oder

   --------------------------------------------------------
   -- free.sql
   --
   -- This SQL Plus script lists freespace by tablespace
   --------------------------------------------------------
   
   
   column dummy noprint
   column pct_used format 999.9 heading "%|Used"
   column name format a16 heading "Tablespace Name"
   column Kbytes format 999,999,999 heading "KBytes"
   column used format 999,999,999 heading "Used"
   column free format 999,999,999 heading "Free"
   column largest format 999,999,999 heading "Largest"
   column max_size format 999,999,999 heading "MaxPoss|Kbytes"
   column pct_max_used format 999.9 heading "%|Max|Used"
   break on report
   compute sum of kbytes on report
   compute sum of free on report
   compute sum of used on report
   
   select (select decode(extent_management,'LOCAL','*','') from dba_tablespaces where tablespace_name =
   b.tablespace_name) || nvl(b.tablespace_name,
   nvl(a.tablespace_name,'UNKOWN')) name,
   kbytes_alloc kbytes,
   kbytes_alloc-nvl(kbytes_free,0) used,
   nvl(kbytes_free,0) free,
   ((kbytes_alloc-nvl(kbytes_free,0))/
   kbytes_alloc)*100 pct_used,
   nvl(largest,0) largest,
   nvl(kbytes_max,kbytes_alloc) Max_Size,
   decode( kbytes_max, 0, 0, (kbytes_alloc/kbytes_max)*100) pct_max_used
   from ( select sum(bytes)/1024 Kbytes_free,
   max(bytes)/1024 largest,
   tablespace_name
   from sys.dba_free_space
   group by tablespace_name ) a,
   ( select sum(bytes)/1024 Kbytes_alloc,
   sum(maxbytes)/1024 Kbytes_max,
   tablespace_name
   from sys.dba_data_files
   group by tablespace_name
   union all
   select sum(bytes)/1024 Kbytes_alloc,
   sum(maxbytes)/1024 Kbytes_max,
   tablespace_name
   from sys.dba_temp_files
   group by tablespace_name )b
   where a.tablespace_name (+) = b.tablespace_name
   order by &1
   /

   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 ====

  execute dbms_workload_repository. modify_snapshot_settings( interval => 15, retention => 21600);

==== Rollback Transactions====

   column local_tran_id  format a22 heading 'Local Txn Id'
   column global_tran_id format a52 heading 'Global Txn Id'
   column state          format a16 heading 'State'
   column mixed          format a5  heading 'Mixed'
   column advice         format a5  heading 'Advice'        
   
   select
      local_tran_id,
      global_tran_id,
      state,mixed,advice
   from  
      dba_2pc_pending
   order 
      by local_tran_id
   / 
   select local_tran_id, state from DBA_2PC_PENDING where state='prepared'; 
   select 'rollback force '''||local_tran_id||''';' from DBA_2PC_PENDING where state='prepared'; 

==== Memory ====

The rule of thumb for the pga_aggregate_limit is:
    PGA_AGGREGATE_LIMIT =(original PGA_AGGREGATE_LIMIT value) + ((maximum number of connected processes) * 4M)

==== Index ====

https://logic.edchen.org/how-to-resolve-ora-08102-index-key-not-found/


==== ADRCI ====

==== VMWare WWNs für ESX Team beim Plattenumhängen ====
ls -l /dev/disk/by-id/wwn* | awk -F 'wwn-0x' '{print $2}' | tr -d './' | sed s/-\>/,/ | tr -d ' ' > linux-diskinfos.csv

==== PLAN HASH SQL ID ====

SELECT sql_id, plan_hash_value, substr(sql_text,1,40) sql_text FROM v$sql;
Select SQL_ID, SQL_TEXT from V$SQLSTATS Where SQL_ID='71c8abf33ab64';

Links:

EMCC Tipps:
[[https://apex.oracle.com/pls/apex/germancommunities/dbacommunity/tipp/5661/index.html|Lizenzsicherheit bei Management Packs]]

Adminstuff:

Check DB Status:
    SELECT INSTANCE_NAME, STATUS, DATABASE_STATUS FROM V$INSTANCE;

The utlrp.sql script recompiles all invalid objects. Run the script immediately after installation, to ensure that users do not encounter invalid objects. Run the utlrp.sql script, where Oracle_home is the Oracle home path:

  SQL> @?/rdbms/admin/utlrp.sql

DBA:

  * [[rman duplicate]]
  * [[performance memory etc]]

Performance:

force_matching_signature is a hash value for what the statement would look like if literals used for column values were replaced by system-generated bind variables and all the redundant white space were removed

  select force_matching_signature, count(*)
  from   v$sqlstats
  where  force_matching_signature > 0
  group  by force_matching_signature
  having count(*) > 10
  order  by 2 desc


Recovery:

  View-Name			Kontext
  v$backup			Online-Backup
  v$datafile_header		Online-Backup
  v$datafile			Beschreibung Datafiles
  v$logfile			Beschreibung Redolog-Gruppen 
  v$log				Beschreibung Redolog-Member
  v$controlfile			Beschreibung Controlfiles
  v$controlfile_record_section	Controlfile Record Section
  v$tablespace			Beschreibung TS
  v$archived_log		alle Archived Redologs
  v$recover_file		Datendateien die ein Recovery benötigen
  v$recovery_log		Archived Logs die für ein Recovery benötigt werden
  v$recovery_file_status	Datendateien die ein Recovery benötigen
  v$recovery_status		DB Recovery Informationen
  DBA_DATA_FILES		Beschreibung Datafiles

==== View Ivalid ====
  select owner, status, object_type from dba_objects where object_name='MYVIEW'
  
  in diesem Fall Rechteproblem:
  grant select on TABLESPACE.TABLE to USER;
  alter view USER.VIEW compile; 
  

==== Überwachung ====

   echo "ORA-00600: TEST fuer Ueberwachung" >> /oracle/$ORACLE_SID/admin/bdump/alert_$ORACLE_SID.log

==== Dataguard related ====

   select database_role, open_mode from v$database;
   select processm, status, sequence# from v$managed_standby;
   select NAME, OPEN_MODE, DATABASE_ROLE from v$database;
   select name, db_unique_name, database_role, switchover_status, open_mode from v$database;
   # recover mode ausschalten
   ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
   # open
   ALTER DATABASE OPEN; 
   # recover mode einschalten
   ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT NODELAY;
   select protection_mode, protection_level from v$database;
   select flashback_on from v$database;
   select * from v$dataguard_config order by db_unique_name;
   select * from v$database_incarnation;
   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;
   # Paramter abfragen
   ==========================
   set linesize 212
   set pagesize 1024
   col name format a40
   col value format a120

   select name, value
      from
   v$system_parameter
     where
   name in ( 'standby_file_management', 'db_recovery_file_dest', 'db_unique_name',
              'service_names', 'fal_client', 'log_archive_format', 'log_archive_config',
              'db_flashback_retention_target', 'log_archive_dest_1', 'log_archive_dest_2',
              'log_archive_dest_3', 'log_archive_dest_4', 'log_archive_dest_state_1',
              'log_archive_dest_state_2', 'log_archive_dest_state_3', 'log_archive_dest_state_4',
              'fal_server', 'db_file_name_convert', 'log_file_name_convert',
              'db_recovery_file_dest_size')
    order by 1;
    
   # ARCHIVE DEST ABSCHALTEN
   alter system set log_archive_dest_state_2 = defer;
   alter system reset log_archive_dest_2 sid='*' scope=both; 
   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 ====

  select COMP_NAME, STATUS from dba_registry;

==== RMAN commands ====

  SELECT OPERATION, STATUS, MBYTES_PROCESSED, START_TIME, END_TIME from V$RMAN_STATUS;

  rman target /
  RMAN> report unrecoverable;
  RMAN> list archivelog all;
  RMAN> report schema;
  RMAN> list backup;
  RMAN> show all
  RMAN> list incarnation of database;

restore archivelog from sequence 9301 until sequence 9311;
  RMAN> restore archivelog from sequence 9301 until sequence 9311;

==== Invalid Components ====

Upgrading to 19.17...

  COMP_ID COMP_NAME VERSION_FULL STATUS
  ---------- ----------------------------------- ------------------------- ----------
  CATALOG Oracle Database Catalog Views 19.17.0.0.0 INVALID
  CATPROC Oracle Database Packages and Types 19.17.0.0.0 INVALID
  OWM Oracle Workspace Manager 19.17.0.0.0 INVALID
  XDB Oracle XML Database 19.17.0.0.0 INVALID

  @?/rdbms/admin/dbmsstat.sql
  @?/rdbms/admin/utlrp.sql

  OBJECT_NAME
  -------------------------------------------
  KUPU$UTILITIES_INT
  KUPW$WORKER
  KUPM$MCP
  KUPF$FILE 

  @?/rdbms/admin/dpload.sql
  @?/rdbms/admin/utlrp.sql

DONE


==== Incomplete Recovery to LogSequenceNumber: (not SCN) ====

  RMAN> RUN
  2> {
  3> SET UNTIL SEQUENCE 29;
  4> RESTORE DATABASE;
  5> RECOVER DATABASE;
  6> }

==== Incomplete Recovery to Timestamp from alert log ====

  RMAN> RUN
  2> {
  3> SET until time "to_date('11/15/2022 11:59:43','mm/dd/yyyy hh24:mi:ss')";
  4> RESTORE DATABASE;
  5> RECOVER DATABASE;
  6> }
  SYS@SID>alter database open RESETLOGS;



==== Backup Types: ====

  The following example creates a level 0 incremental backup to serve as a base for an incremental backup strategy:
    BACKUP INCREMENTAL LEVEL 0 DATABASE;
  The following example creates a level 1 cumulative incremental backup:
    BACKUP INCREMENTAL LEVEL 1 CUMULATIVE DATABASE;
  The following example creates a level 1 differential incremental backup:
    BACKUP INCREMENTAL LEVEL 1 DATABASE;



==== databaselink using existing network config ====


  CREATE DATABASE LINK ONETST2TWOTST CONNECT TO scott IDENTIFIED BY tiger USING 'TWOTST';
  SYS@ONETST>CREATE DATABASE LINK ONETST2TWOTST CONNECT TO scott IDENTIFIED BY tiger USING 'TWOTST';
  Database link created.


==== sqlplus design example: ====
 

   Set linesize 200
   Set pagesize 100
   COLUMN object_name FORMAT A50
   COLUMN object_type FORMAT A30
   COLUMN owner FORMAT A30
   spool /tmp/objexts.txt
   select objet_name, ojbect_type, owner from dba_objects;
   spool off


==== Spool: ====


  spool /tmp/myfile.lst
    $something to spool
  spool off


==== Performance Views: ==== 
important performance views nach  http://www.dba-oracle.com/t
   V$SYSSTAT - This is the main accumulator for many database-wide statistics.  It is used to compute the
   V$LIBRARYCACHE - This view contain performance statistics about all activities in the library cache.  These include cache_misses and cache_pins.
   V$SESSION_WAIT - This shows all Oracle sessions that are waiting on internal resources
   V$SGASTAT - This shows internal SGA statistics such as free_memory.
   V$SQLAREA - This view contain valuable information about executing SQL including the parse_count and the number of times the SQL was executed.
   V$WAITSTAT - This gives details about any sessions that are waiting on Oracle resources
   V$SESSION - This gives detailed session information about all connected Oracle users.
   V$SESSION_EVENT - This is used to show the number of session waits and session timeouts.


   SELECT username, elapsed_time, plsql_exec_time, sql_text, cpu_time, rm_last_action, rm_last_action_reason, rm_last_action_time, rm_consumer_group FROM v$sql_monitor WHERE username is not null;
==== RESSOURCES ====

  SYS@DB>select RESOURCE_NAME, CURRENT_UTILIZATION, MAX_UTILIZATION, LIMIT_VALUE from V$RESOURCE_LIMIT where     RESOURCE_NAME in('sessions', 'processes');
  RESOURCE_NAME            CURRENT_UTILIZATION MAX_UTILIZATION LIMIT_VALUE
  ------------------------ ------------------- --------------- ----------------------------------------
  processes                                592             800        800
  sessions                                 369             675       1280

==== SELECTS ==== 
   select LOG_MODE from v$database;
   select name from V$database;

==== GRANTS ====
   grant unlimited tablespace to scott;

==== Views ==== 
   $rman_backup_subjob_details
   v$rman_backup_job_details
   v$backup_set_details
   v$backup_piece_details
   v$backup_copy_details
   $backup
   v$recovery_status
   v$recovery_file_status
   v$backup_set
   v$backup_piece
   v$backup_datafile
   v$backup_redolog
   v$backup_corruption
   v$backup_device
   v$backup_spfile
   v$backup_sync_io
   v$backup_async_io
   v$recover_file
   v$rman_status
   v$rman_output
   v$backup_datafile_details
   v$backup_controlfile_details
   v$backup_archivelog_details
   v$backup_spfile_details
   v$backup_datafile_summary
   v$backup_controlfile_summary
   v$backup_archivelog_summary
   v$backup_spfile_summary
   v$backup_set_summary
   v$recovery_progress
   v$rman_backup_type
   v$rman_configuration


==== ALTER SYSTEM ====
    
    alter system set undo_tablespace=undotwo;
    alter system set undo_management=auto scope = spfile;

    ALTER SYSTEM SET job_queue_processes=4 SCOPE=SPFILE
    ALTER SYSTEM SET sga_max_size=300 SCOPE=SPFILE
    
    alter system set sga_max_size=300M scope=spfile;
    alter system set sga_target=300M scope=both;
    
    alter system set undo_tablespace = undotwo scope=both;

    ALTER DATABASE MOVE DATAFILE '/oracle/data4/TWOTST/data.dbf' TO '/oracle/data4/TWOTST/datafile/data_001.dbf';

    ALTER TABLESPACE DATA ADD DATAFILE '/oracle/data4/TWOTST/datafile/data_002.dbf' SIZE 20M
    
    ALTER TABLESPACE [Tablespacename] ADD DATAFILE '/oracle/data[X]/[DB]/datafile/[Datafilename].dbf' SIZE 128 M AUTOEXTEND ON NEXT 128 M MAXSIZE unlimited;
    
    ALTER DATABASE DATAFILE
    '/oracle/data4/TWOTST/datafile/data_002.dbf' AUTOEXTEND ON
    NEXT 10 M
    MAXSIZE 1024 M
    
    ALTER TABLESPACE TEMP ADD TEMPFILE '/oracle/data4/TWOTST/datafile/temp_002.dbf' SIZE 512M AUTOEXTEND ON NEXT 128M MAXSIZE 1024 M;
    
    ALTER TABLESPACE temp TEMPFILE '/oracle/data4/TWOTST/datafile/temp_001.dbf' OFFLINE;
    ALTER DATABASE TEMPFILE '/oracle/data4/TWOTST/datafile/temp_001.dbf' DROP INCLUDING DATAFILES;


==== ALTER TABLEPACE ====

    ALTER TABLESPACE [Tablespacename] ADD DATAFILE '/oracle/data[X]/[DB]/datafile/[Datafilename].dbf' SIZE 128 M AUTOEXTEND ON NEXT 128 M MAXSIZE unlimited;

==== CREATE ====


    create undo tablespace UNDOTWO datafile '/oracle/data4/TWOTST/datafile/undotwo_001.dbf' size 1024M;


    CREATE TABLESPACE DATABYHORA
      DATAFILE '/oracle/data4/TWOTST/datafile/databyhora.dbf'
      SIZE 50M
    EXTENT MANAGEMENT LOCAL
    SEGMENT SPACE MANAGEMENT AUTO
    UNIFORM SIZE 128 K

==== Environement ====

  BUK=MYBUK
  ORACLE_SID=TWOTST
  ORA_NLS10=/oracle/product/19.16.0.0.0p1/nls/data
  ORACLE_BASE=/oracle/product
  SCHEMA_PASSWD=/oracle/etc/pwdMYBUK
  ORACLE_SW=/oracle/product/19.16.0.0.0p1
  ORACLE_HOME=/oracle/product/19.16.0.0.0p1

=== patching ===

Prüfe installieter database patches

  SET LINESIZE 300
  COLUMN PATCH_ID  FORMAT 9999999999
  COLUMN PATCH_UID  FORMAT 9999999999
  COLUMN STATUS  FORMAT A25
  COLUMN VERSION  FORMAT A20
  COLUMN BUNDLE_SERIES  FORMAT A15
  COLUMN BUNDLE_ID  FORMAT 9999999999 
  COLUMN ACTION_TIME FORMAT A30
  COLUMN DESCRIPTION FORMAT A60

  select PATCH_ID, PATCH_UID, STATUS, ACTION_TIME, DESCRIPTION from DBA_REGISTRY_SQLPATCH;


====== ORACLE 11G ======

Files: [[http://www.oracle.com/technology/software/products/database/index.html]]\\
Check supported OS. Otherwise: NO FUN at all.

Create Groups, User and set password:\\
groupadd oinstall\\
groupadd dba\\
groupadd oper\\
groupadd asmadmin\\
useradd -g oinstall -G dba,oper,asmadmin oracle\\
passwd oracle\\

PGA: von Session aufgebauter Bereich.
SGA: SystemBereich (System Global Area)

  SQL> alter system kill session '140,118';

System wurde geõndert.

  SQL> select sid, serial#, osuser, machine from v$session where username ='SYSTEM';
  
Verbinden mit Datenbank via sqlplus

  ./sqlplus "/as sysdba"
  ./sqlplus system@SID
  ./sqlplus system/oracle (nimmt ORACLE_SID)  
  ./sqlplus "sys@SID as sysdba" (syspw remote als sysdba) - greift auf tsnnames.ora zum auflösen zu. Benötigt ORAPWD<SID>.ora in $OH/database
  
Listener Starten:

  C:\Dokumente und Einstellungen\Administrator>lsnrctl start LISTENER
  LSNRCTL for 32-bit Windows: Version 11.2.0.1.0 - Production on 27-SEP-2011 13:20:19
  Copyright (c) 1991, 2010, Oracle.  All rights reserved.
  tnslsnr wird gestartet: Bitte warten...

CONNECT & SHUTDOWN:

  C:\Dokumente und Einstellungen\Administrator>sqlplus sys@KURS15 as sysdba
  SQL*Plus: Release 11.2.0.1.0 Production on Di Sep 27 13:49:56 2011
  Copyright (c) 1982, 2010, Oracle.  All rights reserved.
  Kennwort eingeben:
  Verbunden mit:
  Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
  SQL> shutdown immediate
  
  shutdown transactional
  abort (eher nicht zu empfehlen. Quick and Dirty)
  
  startup (pfile=, spfile=) (ggf. sinnvol für Patchen, wenn Speicher vergrößert sein muss. -> Keine Auswirkungen auf normale Installation)
  startup nomount
  startup mount
  alter database open;
  
Remote Connect zulassen:
...

SQL-Statements:\\
  SQL>  select * from DBA_FREE_SPACE;
  SQL>  select * from DBA_DATA_FILES;
  SQL>  select * from DBA_TABLESPACES;
  SQL>  select FILE_NAME, AUTOEXTENSIBLE, MAXBYTES from DBA_DATA_FILES;
  SQL>  select * from v$datafile;
  SQL>  select * from dict where TABLE_NAME like 'V%';
  SQL>  select * from v$instance; (sicherstellen, dass man mit der richtingen DB-Instanz verbunden ist)
-- select * from dict where table_name like '%USER%' and not like 'USER%';
-- desc ALL_USERS;
desc DBA_USERS;
-- select username as NAME, user_id ID, authentication_type as AUTH, created as DATUM from DBA_USERS; 
select username, account_status from DBA_USERS; 
-- select * from all_users;
-- select * from DBA_USERS_WITH_DEFPWD;
select * from dba_data_files;
select * from DBA_TS_QUOTAS where username = 'MICHAEL';

PL_SQL example:
  set serveroutput on;
  DECLARE v_file_name  VARCHAR2(100);

  BEGIN

  select name INTO v_file_name from v$dbfile where file# = 1;
  dbms_output.put_line('Hallo Welt, die Datei heisst: '||v_file_name);

  END;
  
  
Skripte im RDBMS Admin verzeichnis.  


ADmin Kram:

alter TABLESPACE
drop TABLESPACE data INCLUDING CONTENTS AND DATAFILES;

alter TABLESPACE app_data
add datafile '/path/to/db02.dbf'
size=200M
autoextend on
next 10M
maxsize 500M;

alter database
datafile '/path/to/db02.dbf'
resize=200M;

(tablespace offline nehmen:)
alter TABLESPACE app_data
rename
datafile '/path/to/db02.dbf'
to       '/2ndpath/to/db02.dbf';
(Datei darunter muss gemoved werden)

Table Space anlegen:
  CREATE TABLESPACE test_data DATAFILE 'D:\oradata\KURS1\testdata01.dbf' size 20M ,'D:\oradata\KURS1\testdata02.dbf'\\ 
  size 20M autoextend on maxsize 100M extent management local segment space management auto;

Eine Parameter anpassen (autoextend)
  ALTER DATABASE DATAFILE 'D:\oradata\KURS1\TESTDATA01.dbf' AUTOEXTEND ON;

Eine zusätliche Datei hinzufügen
  ALTER TABLESPACE users ADD DATAFILE 'D:\oradata\KURS1\users03.dbf' size 5M autoextend on;

Erweitern einer bestehenden Datendatei:
  ALTER DATABASE DATAFILE 'D:\oradata\KURS1\TESTDATA01.dbf' resize 111M;

User anlegen/updaten und mit neuem Tablespace anlegen:  
  create user test identified by oracle default tablespace test_data;
  alter user test default tablespace test_dummy;
  
  
Partition: (Festlegen anhand eines Sortierkriteriums welche Werte in welche "virtuelle Tabelle" kommen - kostet extra)

Testweise befüllen und Truncate einer DB
insert into dummy (NAME) VALUES ('AAAAAAAAAAAAAAAAAAAAAAAAAAAAAA');
set serveroutput on
DECLARE
n_count    NUMBER;

BEGIN
for i IN 1..20
LOOP
  INSERT INTO dummy SELECT * FROM dummy;
  SELECT COUNT(*) INTO n_count FROM dummy;
  DBMS_output.put_line('Aktuell '||n_count||' Zeilen');
END LOOP;
END;


set timing on
--DELETE FROM DUMMY;
TRUNCATE TABLE DUMMY; -- impliziert commit -> kein rollback mehr moeglich
select count(*) from dummy;

User anlegen:
  create user michael identified by oracle default tablespace test_data\\ 
  temporary tablespace temp QUOTA 15m ON test_data;
  
Alle Objekte die dem User gehören:
  select * from cat;

Userrechte: 
  grant sysdba to testuser; <- nur mit sysdba (sqlplus, system kann das nicht)
  select * from V$parameter where name like 'remote%';
  select * from v$pwfile_users;
  grant execute on dbms_pipe to public; <- schlechte idee ;)
  grant update (first_name, salary) on employee to karen WITH GRANT OPTION;
  grant select, insert, update, delete, references on dummy2 to michael2;
  
  select * from dba_sys_privs where GRANTEE = 'MICHAEL';
select * from tab_sys_privs where GRANTEE = 'MICHAEL';
select * from col_sys_privs where GRANTEE = 'MICHAEL';


Export für einen User

C:\Dokumente und Einstellungen\Administrator>exp

Export: Release 11.2.0.1.0 - Production on Do Sep 29 15:13:08 2011

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.


Benutzername: system
Kennwort:                             <

Angemeldet bei: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
Puffergr÷▀e f³r Array-Fetch eingeben: 4096 >             <

Datei exportieren: EXPDAT.DMP > c:\schemaexport.dmp

(1)E(Gesamt-DB), (2)U(Benutzer) od. (3)T(abellen): (2)U > 2

Berechtigungen exportieren (ja/nein): ja > ja

Tabellendaten exportieren (ja/nein): ja > ja

Extents komprimieren                            < (ja/nein): ja > ja

Exportieren in WE8MSWIN1252-Zeichensatz und AL16UTF16-NCHAR-Zeichensatz durchgef³hrt

Spezifizierte Benutzer werden gleich exportiert ...
Zu exportierende Benutzer: (RETURN f³r Abbruch) > kurs

Zu exportierende Benutzer: (RETURN f³r Abbruch) >

. Prozedurale Pre-Schema-Objekte und -Aktionen werden exportiert
. Fremdfunktions-Bibliotheksnamen f³r Benutzer KURS werden exportiert
. Synonyme vom Typ PUBLIC werden exportiert
. Synonyme vom Typ PRIVATE werden exportiert
. Objekttypdefinitionen f³r Benutzer KURS  werden exportiert
KURS Objekte werden gleich exportiert ...
. Datenbank-Links werden exportiert
. Sequenzzahlen werden exportiert
. Clusterdefinitionen werden exportiert
. Tabellen von KURS werden gleich exportiert ... ³ber 'Conventional Path'
. . Export der Tabelle                          DUMMY          4 Zeilen exportiert
. Synonyme werden exportiert
. Views werden exportiert
. Gespeicherte Prozeduren werden exportiert
. Operatoren werden exportiert
. Referentielle Integritõts-Constraints werden exportiert
. Trigger werden exportiert
. Indextypen werden exportiert
. Bitmap-, funktionale und erweiterbare Indizes werden exportiert
. Exportieren von Posttable-Aktionen
. Materialized Views werden exportiert
. Snapshot-Log werden exportiert
. Exportieren von Job-Queues
. Exportieren von Refresh-Gruppen und untergeordneten Gruppen
. Dimensionen werden exportiert
. Prozedurale Post-Schema-Objekte und -Aktionen werden exportiert
. Statistiken werden exportiert
Export erfolgreich ohne Warnungen beendet.

C:\Dokumente und Einstellungen\Administrator>


C:\Dokumente und Einstellungen\Administrator>exp system/oracle@KURS1 owner=kurs file=c:\kurs1.dmp log=c:\kurs1exp.log

Export: Release 11.2.0.1.0 - Production on Do Sep 29 15:19:52 2011

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.


Angemeldet bei: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
Exportieren in WE8MSWIN1252-Zeichensatz und AL16UTF16-NCHAR-Zeichensatz durchgef³hrt

Spezifizierte Benutzer werden gleich exportiert ...
. Prozedurale Pre-Schema-Objekte und -Aktionen werden exportiert
. Fremdfunktions-Bibliotheksnamen f³r Benutzer KURS werden exportiert
. Synonyme vom Typ PUBLIC werden exportiert
. Synonyme vom Typ PRIVATE werden exportiert
. Objekttypdefinitionen f³r Benutzer KURS  werden exportiert
KURS Objekte werden gleich exportiert ...
. Datenbank-Links werden exportiert
. Sequenzzahlen werden exportiert
. Clusterdefinitionen werden exportiert
. Tabellen von KURS werden gleich exportiert ... ³ber 'Conventional Path'
. . Export der Tabelle                          DUMMY          4 Zeilen exportiert
. Synonyme werden exportiert
. Views werden exportiert
. Gespeicherte Prozeduren werden exportiert
. Operatoren werden exportiert
. Referentielle Integritõts-Constraints werden exportiert
. Trigger werden exportiert
. Indextypen werden exportiert
. Bitmap-, funktionale und erweiterbare Indizes werden exportiert
. Exportieren von Posttable-Aktionen
. Materialized Views werden exportiert
. Snapshot-Log werden exportiert
. Exportieren von Job-Queues
. Exportieren von Refresh-Gruppen und untergeordneten Gruppen
. Dimensionen werden exportiert
. Prozedurale Post-Schema-Objekte und -Aktionen werden exportiert
. Statistiken werden exportiert
Export erfolgreich ohne Warnungen beendet.

csv-Ausgabe
   select LPAD(name,30)||LPAD(vorname,30) from kurs.dummy;
   select NAME||';'||VORNAME from kurs.dummy;
   
Daten in Tabelle laden
  load data
  infile 'dummy.csv'
  into TABLE KURS.DUMMY2
  FIELDS TERMINATED BY ';' OPTIONALLY ENCLOSED BY '"'
  TRAILING NULLCOLS
  (NACHNAME,VORNAME)
  
  
Offline Backup:
  shutdown immediate
  copy files /oradata/.../
  
  
Online Backup RMAN:
RMAN> show all;

RMAN configuration parameters for database with db_unique_name DB11G are:
CONFIGURE RETENTION POLICY TO REDUNDANCY 1; # default
CONFIGURE BACKUP OPTIMIZATION OFF; # default
CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default
CONFIGURE CONTROLFILE AUTOBACKUP OFF; # default
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F'; # default
CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO BACKUPSET; # default
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE MAXSETSIZE TO UNLIMITED; # default
CONFIGURE ENCRYPTION FOR DATABASE OFF; # default
CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default
CONFIGURE COMPRESSION ALGORITHM 'BASIC' AS OF RELEASE 'DEFAULT' OPTIMIZE FOR LOAD TRUE ; # default
CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default
CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/u01/app/oracle/product/11.2.0/dbhome_1/dbs/snapcf_DB11G.f'; # default


Control-File-Sicherung (nicht RMAN-Katalog) 
- Autobackup von OFF auf ON 

shutdown immediate
startup mount
alter database archivelog;
alter database open;

RMAN> connect target

run {
allocate channel dev1 type disk;
backup full database
include current controlfile
format '/oracle/backup/db11g/%d_DBF_ONLINE_s%s_p%p_t%t'
tag='ONLINE_FULL_BACKUP'
filesperset=2;
release channel dev1;
}


RMAN> sql "alter system switch logfile";
SQL-Anweisung: alter system switch logfile

RMAN> run {
2> allocate channel dev1 type disk;
3> backup archivelog all not backed up 2 times
4> tag = 'ARCH_BACKUP'
5> format='D:\backup\Kurs1\%d_s%s_p%p_t%t';
6> release channel dev1;
7> }


RMAN> run {
2> allocate channel dev1 type disk;
3> backup archivelog all delete input
4> tag = 'ARCH_DEL_BACKUP'
5> format='D:\backup\Kurs1\%d_DARCH_s%s_p%p_t%t';
6> release channel dev1;
7> }


RECOVERY:

RMAN> connect target

Bei Zieldatenbank angemeldet: KURS1 (DBID=3090761539, nicht ge÷ffnet)

RMAN> restore database;

Starten restore um 30.09.11
Kontrolldatei der Zieldatenbank wird anstelle des Recovery-Katalogs verwendet
Zugewiesener Kanal: ORA_DISK_1
Kanal ORA_DISK_1: SID=133 Device-Typ=DISK


restore redo
alter database open; -> error