[Oracle] How-To

17 thoughts on “[Oracle] How-To”

• Olivier says:

  04/02/2020 at 09:25

  ORA-00265: instance recovery required, cannot set ARCHIVELOG mode
  *Cause: The database either crashed or was shutdown with the ABORT
  option. Media recovery cannot be enabled because the online
  logs may not be sufficient to recover the current datafiles.
  *Action: Open the database and then enter the SHUTDOWN command with the
  NORMAL or IMMEDIATE option.

  SQL> startup mount 
  SQL> ALTER DATABASE OPEN
  SQL> shutdown immediate
  

  Reply
• Olivier says:

  03/03/2020 at 13:51

  ORA-29516: Aurora assertion failure: Assertion failure...
  

  Update DBCA response file to add the following init param :

  INITPARAMS=JAVA_JIT_ENABLED=FALSE
  

  Reply
• Olivier says:

  24/06/2020 at 09:13

  Check/Change the port for EM (Enterprise manager)

  select dbms_xdb_config.gethttpsport () from dual;
  

  exec dbms_xdb_config.sethttpsport (5502);
  

  By default the port is 5500 and EM is available on https://yourserver:5500/em

  See also : http://www.ludovicocaldara.net/dba/tag/start-dbconsole-12c/

  Reply
• OLIVIER COMBE says:

  11/01/2021 at 15:42

  Trace files

  Start adrci

  adrci
  

  Check current retention period :

  adrci> show control
  

  SHORTP_POLICY: Number of hours after which to purge ADR contents that have a short life. Default is 720 (30 days).
  LONGP_POLICY: Number of hours after which to purge ADR contents that have a long life. Default is 8760 (365 days).

  Update policies :

  adrci> set control (SHORTP_POLICY = 168)
  adrci> set control (LONGP_POLICY = 672)
  adrci> show control
  

  which means we set the long life keep time as 28 days and the short life keep time as 7 days.

  adrci> purge -age 10080
  

  This command can delete all trace files older than 10080 minutes (7 days) in current home immediately. You don’t have to wait background processes to do the job.

  Reply
• OLIVIER COMBE says:

  11/01/2021 at 15:51

  Delete old archive logs

  Start RMAN :

  rman target /
  

  Check files :

  RMAN>crosscheck archivelog all;
  

  Delete expired files :

  RMAN>delete noprompt expired archivelog all;
  

  Delete all files :

  RMAN>delete archivelog until time 'SYSDATE-10';
  

  without prompt :

  RMAN>delete noprompt archivelog until time 'SYSDATE-10';
  

  Reply
• OLIVIER COMBE says:

  25/02/2021 at 11:56

  Check oracle version :

  SELECT * FROM PRODUCT_COMPONENT_VERSION;
  
  Select * from v$version;
  

  Reply
• OLIVIER COMBE says:

  26/02/2021 at 08:40

  Retreive DB/Instance info :

  select sys_context('userenv','db_name') from dual;
  
  SELECT sys_context('userenv','instance_name') FROM dual;
  
  select sys_context('userenv', 'server_host') from dual;
  
  select ora_database_name from dual;
  
  select * from global_name;
  
  select instance from v$thread;
  
  select *  from v$database;
  

  Reply
• OLIVIER COMBE says:

  04/06/2021 at 09:26

  enable audit trail (for failed login attempt)

  audit session whenever not successful 
  
  audit_trail="true"
  
  select * from SYS.AUD$ 
  
  
  select 
     os_username,
     username,
     terminal,
     to_char(timestamp,'MM-DD-YYYY HH24:MI:SS')
  from
     dba_audit_trail;
  

  Reply
• OLIVIER COMBE says:

  26/07/2021 at 12:29

  Oracle Compatible version

  Show :

  SQL> SELECT name, value FROM v$parameter WHERE name = 'compatible';
  

  Update :

  SQL> alter system set COMPATIBLE='19.0.0' scope=spfile;
  SQL> shutdown immediate
  SQL> startup 
  

  See : https://mikedietrichde.com/2019/04/17/when-and-how-should-you-change-compatible/

  Reply
• OLIVIER COMBE says:

  26/07/2021 at 12:43

  Oracle Optimizer

  Check versions:

  select optimizer_feature_enable, count(*)
  from v$system_fix_control
  group by optimizer_feature_enable
  order by 1 asc;
  

  Check param :

  SELECT name, value FROM v$parameter WHERE name = 'optimizer_features_enable';
  

  Update System

  alter system set optimizer_features_enable='19.1.0.1' scope=both;
  

  Update Session

  alter session set optimizer_features_enable='11.2.0.3';
  

  Hint

  select /*+ optimizer_features_enable('9.2.0') */ ...
  

  See http://www.dba-oracle.com/t_optimizer_features_enable.htm

  Reply
• OLIVIER COMBE says:

  04/03/2022 at 08:17

  Check CDB state:

  SELECT CDB FROM V$DATABASE;
  

  Check PDB state:

  SELECT NAME, OPEN_MODE, RESTRICTED, OPEN_TIME FROM V$PDBS;
  

  Reply
• OLIVIER COMBE says:

  17/03/2022 at 17:56

  SQLPlus & CDB/PDB :

  With only SID:

  export ORACLE_SID=<DB_NAME>
  
  sqlplus sys/password as sysdba
  

  SQL> SELECT NAME, OPEN_MODE FROM V$PDBS;
  
  NAME        OPEN_MODE
  ------------------------
  PDB$SEED    READ ONLY
  
  <PDB_NAME>  READ WRITE
  

  With SID and PDB_SID:

  export ORACLE_SID=<DB_NAME>
  export ORACLE_PDB_SID=<PDB_NAME>
  
  sqlplus sys/password as sysdba
  

  SQL> SELECT NAME, OPEN_MODE FROM V$PDBS;
  
  NAME        OPEN_MODE
  ------------------------
  <PDB_NAME>  READ WRITE
  

  Reply
  • OLIVIER COMBE says:

    13/06/2023 at 12:47

    SQL> show con_name
    

    display current CDB or PDB name

    Reply
• OLIVIER COMBE says:

  21/04/2022 at 15:51

  Auto start pdb :
  https://logic.edchen.org/how-to-auto-open-pdb-when-startup-cdb/

  sqlplus / as sysdba << EOF
  alter pluggable database all open;
  /
  alter pluggable database all save state;
  /
  CREATE OR REPLACE TRIGGER  startup_pluggable_dbs
  AFTER STARTUP ON DATABASE
  BEGIN
     EXECUTE IMMEDIATE 'alter pluggable database all open';
  END;
  /
  EOF
  
  

  Reply
• OLIVIER COMBE says:

  02/03/2023 at 09:31

  Find restart history

  SELECT host_name, instance_name,
  TO_CHAR(startup_time, 'DD-MM-YYYY HH24:MI:SS') startup_time,
  FLOOR(sysdate-startup_time) days
  FROM sys.v_$instance;
  

  Reply
• OLIVIER COMBE says:

  04/07/2023 at 08:46

  Set oracle parameters (sessions, cursors, transactions, etc.)
  https://www.funoracleapps.com/2022/10/how-to-increase-processessessions-and.html

  Reply
• OLIVIER COMBE says:

  06/01/2025 at 09:50

  Enable OEM (Oracle Enterprise Manager) for PDB

  https://oradbdev.mathiasmagnusson.com/2023/03/14/enable-enterprise-manager-express/

  Connect to PDB:

  exec dbms_xdb_config.sethttpsport(5501)
  

  Reply