Oracle DB – LeoSunny

Setting , Resetting/Unsetting Database Parameters

October 30, 2020October 30, 2020 leosunny

https://coskan.wordpress.com/2007/03/07/setting-or-resetting-unsetting-parameters/ https://community.oracle.com/tech/developers/discussion/4027755/need-to-change-hidden-parameter-to-its-default-value https://community.oracle.com/mosc/discussion/3774329/11-2-0-3-many-hidden-parameters-in-init-ora 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

October 28, 2020 leosunny

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

October 13, 2020 leosunny

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.

October 13, 2020 leosunny

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

October 13, 2020 leosunny

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:

October 13, 2020October 13, 2020 leosunny

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

October 13, 2020October 13, 2020 leosunny

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

October 7, 2020 leosunny

Reference: https://community.oracle.com/tech/apps-infra/discussion/4198843/different-sizes-of-asm-disks-in-asm-diskgroup https://docs.oracle.com/cd/E11882_01/server.112/e18951/asmdiskgrps.htm#OSTMG94124 https://oracle-base.com/articles/10g/automatic-storage-management-10g With Oracle 11.2.0.4, asmca allows me to create a diskgroup with differently sized disks, whereas asmca 12.2.0.1 does not: With 12.2.0.1, 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

September 7, 2020 leosunny

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/asmcmdbase.pm line 5002. Use of uninitialized value $clus_mode in string eq at /u01/app/oracle/product/12.1.0/db_1/lib/asmcmdbase.pm line 5030. Use of uninitialized value $clus_mode in string eq at /u01/app/oracle/product/12.1.0/db_1/lib/asmcmdbase.pm line 5079. Use of uninitialized value […]

Use Oracle date function to implement alert in shell script

September 4, 2020 leosunny

1 Practice: calculate diff of DB time column in shell script 1.1 Practice: check_diff_time.sh oracle@oraclevm01: /monitoring/bin % : more check_diff_time.sh #!/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 https://unix.stackexchange.com/questions/85180/assigning-the-output-of-a-sql-query-to-variable https://stackoverflow.com/questions/17774405/bash-sql-query-outputs-to-variable […]