Setting , Resetting/Unsetting Database Parameters You can query the documented parameters with the query below select name,value,isdefault,isses_modifiable,issys_modifiable, isinstance_modifiable,isdeprecated, from v$parameter You can query the list of undocumented parameters with the script mentioned before “how to view list of hidden parameters” Also you can query v$parameter2 as the same way you query the v$parameter view to view the session specific parameter values. […]

Enabling supplemental log data

Enabling supplemental log data The following steps check whether supplemental log data is enabled and, if not, enable it. Enter the following command: select supplemental_log_data_min, supplemental_log_data_pk from v$database; If the command returns YES or IMPLICIT, supplemental log data is already enabled. For example, SUPPLEME SUP ——– — YES NO indicates that supplemental log data is […]

North Africa Timezone changes around Ramadan

North Africa Timezone changes around Ramadan Symptom: North Africa clock changes around Ramadan. e.g., Morocco, that is included in DSTv34 which is not included in the current Oracle Database version ( 12.2 and 12.1 ) Oracle Database Cloud Schema Service – Version N/A and later: Updated DST Transitions and New Time Zones in Oracle RDBMS […]

ORA-38706: Cannot turn on FLASHBACK DATABASE logging.

ORA-38706: Cannot turn on FLASHBACK DATABASE logging. Symptom: SQL> alter database flashback on; alter database flashback on * ERROR at line 1: ORA-38706: Cannot turn on FLASHBACK DATABASE logging. ORA-38707: Media recovery is not enabled. SQL> show parameter recovery NAME TYPE VALUE ———————————— ———– —————————— db_recovery_file_dest string +FLASH db_recovery_file_dest_size big integer 16G recovery_parallelism integer 0 […]

ORA-38774 while disable archive

ORA-38774 while disable archive Symptom: SQL> Alter database noarchivelog; Alter database noarchivelog * ERROR at line 1: ORA-38774: cannot disable media recovery – flashback database is enabled SQL> Select name from v$restore_point; no rows selected SQL> select name,flashback_on from v$database; NAME FLASHBACK_ON ——— —————— cdb01 YES Solution: SQL> alter database flashback off; SQL> select name,flashback_on […]

ORA-12514 caused by Archive log destination full in Oracle 19C:

ORA-12514 caused by Archive log destination full in Oracle 19C: Symptom: ORA-12514: TNS:listener does not currently know of service requested in connect descriptor [oracle@oraclevm01 dump]$ tnsping pdb01 Used TNSNAMES adapter to resolve the alias Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = oraclevm01)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = […]

ORA-12034: materialized view log on “TEST”.”tab01″ younger than last refresh

Symptoms: SQL> SELECT job, what mview_name, TO_CHAR (last_date, ‘mm/dd/yyyy hh24:mi:ss’) last_execution, TO_CHAR (next_date, ‘mm/dd/yyyy hh24:mi:ss’) next_execution, INTERVAL FROM dba_jobs WHERE what LIKE ‘dbms_refresh.refresh%’ ; JOB MVIEW_NAME LAST_EXECUTION NEXT_EXECUTION INTERVAL 207 dbms_refresh.refresh(‘”TEST”.”TST_REFRESH_GROUP”‘); 01/01/4000 00:00:00 trunc(add_months(sysdate,12)) ORA-12048: error encountered while refreshing materialized view “TEST”.”Test_MV” ORA-06512: at line 35 ORA-06512: at “SYS.DBMS_SNAPSHOT_KKXRCA”, line 3012 ORA-12034: materialized view log […]

Different sizes of ASM disks in ASM diskgroup

Reference: With Oracle, asmca allows me to create a diskgroup with differently sized disks, whereas asmca does not:       With, it is also not possible to add a disk with a different size to an already existing disk group.   What works though is to replace an […]

kfod.bin: No such file or directory

Symptom: oracle@oraclevm01: /home/oracle % : asmcmd /u01/app/oracle/product/12.1.0/db_1/bin/kfod: line 22: /home/oracle/%ORACLE_HOME%/bin/kfod.bin: No such file or directory Use of uninitialized value $clus_mode in scalar chomp at /u01/app/oracle/product/12.1.0/db_1/lib/ line 5002. Use of uninitialized value $clus_mode in string eq at /u01/app/oracle/product/12.1.0/db_1/lib/ line 5030. Use of uninitialized value $clus_mode in string eq at /u01/app/oracle/product/12.1.0/db_1/lib/ line 5079. Use of uninitialized value […]

Use Oracle date function to implement alert in shell script

  1         Practice: calculate diff of DB time column in shell script 1.1        Practice: oracle@oraclevm01: /monitoring/bin  % : more #!/bin/bash lag=$(sqlplus -s  TESTUSER/oracle@pstg <<END set pagesize 0 feedback off verify off heading off echo off; select ROUND(TO_NUMBER(cast(target_commit as date)-cast(source_commit as date))*24*60) as lag_time from TESTUSER.HEARTBEAT exit; END ) echo $lag […]