THE Wiki

User Tools

Site Tools

Trace: • db2 • arch • microsoft_office • opentext_archive_server
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 [2025/07/03 10:38] – [Sessions (to kill?)] mduerschoracle [2026/09/11 07:22] (current) – [Environement] mduersch
Line 1: Line 1:
 ====== ORACLE ====== ====== ORACLE ======
  
 +
 +==== EMCC ====
 +
 +  oracle@mytesthost:/oracle/product/agent/agent_24.1.0.0.0/bin # ./emctl control agent runCollection mytesthost:host   DiscoverTargets
 +  Oracle Enterprise Manager 24ai Release 1
 +  Copyright (c) 1996, 2024 Oracle Corporation.  All rights reserved.
 +  ---------------------------------------------------------------
 +  EMD runCollection completed successfully
 +
 +  oracle@mytesthost:/oracle/product/agent/agent_24.1.0.0.0/bin # ./emctl upload agent
 +  Oracle Enterprise Manager 24ai Release 1
 +  Copyright (c) 1996, 2024 Oracle Corporation.  All rights reserved.
 +  ---------------------------------------------------------------
 +  EMD upload completed successfully
 +
 +
 +  $AGENT_HOME/bin/emctl getmetric agent sorame01.intern.hessen.de,host,Load
 +  $AGENT_HOME/bin/emctl getmetric agent sorame01.intern.hessen.de,host,CPUUsage 
 +  $AGENT_HOME/bin/emctl getmetric agent sorame01.intern.hessen.de,host,Processes
 +  $AGENT_HOME/bin/emctl listplugins agent -type all 
 +  $AGENT_HOME/bin/emctl pingOMS
 +  $AGENT_HOME/bin/emctl upload agent
 +  $AGENT_HOME/bin/emctl status agent
 +  
 +  
 +EMCC Log Files:
 +
 +  1- EM Agent NOHUP file. (emagent.nohup)
 +  2- EM Agent log file. (gcagent.log)
 +  3- EM Agent Perl Trace file. (emagent_perl.trc)
 +  4- Standard emctl command operation log file. (emctl.log)
 +  5- EM Agent SDK trace file. (gcagent_sdk.trc)
 +  6- EM Agent MDU log. (gcagent_mdu.log) 
 +  7- EM OMS main trace file for the OMS Console (emgc) application. (emoms.trc)
 +  8- EM OMS main log file for the OMS Console (emgc) application. (emoms.log)
 +  9- EM OMS main log file for the OMS pbs (empbs) application. (emoms_pbs.log)
 +  10- EM OMS main trace file for the OMS pbs (empbs) application. (emoms_pbs.tc)
 +
 +Promoted Discovered Error und emcli
 +
 +  SQL> connect / as sysdba
 +  SQL> show parameter job_queue_processes;  --->Verify the current value of job queue process.
 +  SQL> alter system set job_queue_processes = 0; (Note: Please track the existing value before making it to 0)
 +  SQL> connect sysman/
 +  SQL> exec emd_maintenance.remove_em_dbms_jobs;
 +  SQL>exec gc_interval_partition_mgr.partition_maintenance; 
 +  SQL> connect / as sysdba
 +  SQL> alter system set job_queue_processes = 300; (Note: Set the value which was verified before making it to 0)
 +  SQL> connect sysman/
 +  SQL> exec emd_maintenance.submit_em_dbms_jobs;
 +  SQL> commit;
 +
 +
 +
 +==== CDB PDB ====
 +
 +Befehle 
 +  SHOW CON_NAME
 +  ALTER SESSION SET CONTAINER=pdb1;
 +  SELECT NAME, CON_ID, DBID, CON_UID, GUID FROM V$CONTAINERS ORDER BY CON_ID; 
 +  SELECT PDB_ID, PDB_NAME, STATUS FROM DBA_PDBS ORDER BY PDB_ID;
 +  SELECT NAME, OPEN_MODE, RESTRICTED, OPEN_TIME FROM V$PDBS;
 +
 +==== 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 ====
  
Line 40: Line 130:
          or network_service_banner like '%Crypto-checksumming service adapter%')          or network_service_banner like '%Crypto-checksumming service adapter%')
   and TYPE <> 'BACKGROUND';    and TYPE <> 'BACKGROUND'; 
 +
 +==== Audit Trail ====
 +
 +  SELECT os_username AS "OS User", 
 +       username AS "DB User", 
 +       userhost AS "Machine", 
 +       timestamp AS "Date", 
 +       returncode AS "Error Code"  
 +  FROM dba_audit_session 
 +  WHERE returncode != 0 ORDER BY timestamp DESC;  
  
 ==== Grants by User ==== ==== Grants by User ====
Line 322: Line 422:
 ==== Dataguard related ==== ==== Dataguard related ====
  
 +   SELECT DEST_ID, DESTINATION, STATUS, GAP_STATUS, ARCHIVED_THREAD#, ARCHIVED_SEQ#, APPLIED_THREAD#, APPLIED_SEQ#, FROM V$ARCHIVE_DEST_STATUS WHERE STATUS <> 'INACTIVE'
 +  
    select database_role, open_mode from v$database;    select database_role, open_mode from v$database;
    select processm, status, sequence# from v$managed_standby;    select processm, status, sequence# from v$managed_standby;
Line 339: Line 441:
    select * from v$block_change_tracking;     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;
 +
 +   # Anfragen
 +   col DB_UNIQUE_NAME for a10
 +   col OPEN_MODE for a20
 +   col DATABASE_ROLE for a20
 +   col LOG_MODE for a10
 +   set lines 200
 +   select DBID,NAME,LOG_MODE,OPEN_MODE,DATABASE_ROLE,SWITCHOVER_STATUS,DB_UNIQUE_NAME,RESETLOGS_TIME from V$database;
 +
 +   # Transport log
 +   select process, pid, status from v$managed_standby where process='RFS';
 +
 +
 +
    # Paramter abfragen    # Paramter abfragen
    ==========================    ==========================
Line 419: Line 535:
 restore archivelog from sequence 9301 until sequence 9311; restore archivelog from sequence 9301 until sequence 9311;
   RMAN> restore archivelog from sequence 9301 until sequence 9311;   RMAN> restore archivelog from sequence 9301 until sequence 9311;
 +  
 +Backup Pieces:
 +
 +  column handle format a40
 +  column size_mb format a30  
 +  column status format a30
 +  column completion_time a40
 +  SELECT handle, bytes / 1024 / 1024 / 1024 AS size_gb, status, completion_time from  v$backup_piece where handle like '%full%' 
  
 ==== Invalid Components ==== ==== Invalid Components ====
Line 518: Line 642:
    V$SESSION - This gives detailed session information about all connected Oracle users.    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.    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;    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;
 +
 +==== Long running Statements v$session_longops ====
 +  
 +  col opname for a20
 +  col username for a20
 +  col prozent for clear 
 +  col time_remaining for clear 
 +
 +  SELECT opname, username, 
 +     to_char(start_time,'DD-MON-YYYY HH24:MI:SS'),
 +     (sofar/totalwork)*100 prozent, time_remaining 
 +     FROM v$session_longops
 +     WHERE time_remaining>0;
 +  
 +
 ==== RESSOURCES ==== ==== RESSOURCES ====
  
Line 532: Line 670:
    select LOG_MODE from v$database;    select LOG_MODE from v$database;
    select name from V$database;    select name from V$database;
 +   # flashbackquery
 +   select count(1) from somewhere 
 +                 AS OF TIMESTAMP TO_TIMESTAMP('2025-12-12 16:00', 'YYYY-MM-DD HH24:MI:SS') 
 +           where 
 +                 something is NULL;
  
 ==== GRANTS ==== ==== GRANTS ====
Line 644: Line 787:
   select PATCH_ID, PATCH_UID, STATUS, ACTION_TIME, DESCRIPTION from DBA_REGISTRY_SQLPATCH;   select PATCH_ID, PATCH_UID, STATUS, ACTION_TIME, DESCRIPTION from DBA_REGISTRY_SQLPATCH;
  
 +  ./datapatch -purge_old_metadata
 +  https://mikedietrichde.com/2025/02/28/final-blog-post-about-datapatch-cleanup-and-purge/ 
 +  
  
 ====== ORACLE 11G ====== ====== ORACLE 11G ======
oracle.1751539130.txt.gz · Last modified: by mduersch