Online DBA

<<Back to DB Administration Main Page How to Enable Oracle  Client Side and  Server side Tracing to investigate Connection Issues Enabling Oracle Net Client Side Tracing To enable oracle SQL *Net client tracing enter following lines in sqlnet.ora file. TRACE_LEVEL_CLIENT = SUPPORT TRACE_UNIQUE_CLIENT = on TRACE_LEVEL_SERVER = SUPPORT TRACE_DIRECTORY_CLIENT = C:\TEMP TRACE_FILE_CLIENT = CLIENT TRACE_DIRECTORY_SERVER = C:\TEMP TRACE_FILE_SERVER = SERVER TRACE_TIMESTAMP_CLIENT = ON DIAG_ADR_ENABLED= OFF NOTE: If TNS_ADMIN variable is set in your environment then, enter the above lines in sqlnet.ora file available under TNS_ADMIN location Enabling Oracle Net Server Side Tracing To enable oracle Net server side tracing enter following lines in sqlnet.ora file. TRACE_LEVEL_CLIENT = SUPPORT TRACE_UNIQUE_CLIENT = on TRACE_LEVEL_SERVER = SUPPORT TRACE_DIRECTORY_CLIENT = C:\TEMP TRACE_FILE_CLIENT = CLIENT TRACE_DIRECTORY_SERVER = C:\TEMP TRACE_FILE_SERVER = SERVER TR

<<Back to DB Administration Main Page If you don't want to enable auditing but still interested to find the last DDL and/or DML statement executed against any table, you can find it using below command Finding latest DML time on a table SQL>select max(ora_rowscn), scn_to_timestamp(max(ora_rowscn)) from SCOTT.EMP Finding latest DDL time on a table SQL> select object_name,object_type,to_char(created,'DD-MM-YY HH24:MI:SS')created,to_char(last_ddl_time,'DD-MM-YY HH24:MI:SS')last_ddl from user_objects where object_name='TEST'

<<Back to DB Administration Main Page Managing TNS_ADMIN  Variable in env.ora S tarting from 12cR2 you can manage TNS_ADMIN variable in $ORACLE_HOME/env.ora file   ls -lrt $ORACLE_HOME |grep env.ora -rw-r--r--  1 oracle dba   852 Aug 18  2015 env.ora By default, the $ORACLE_HOME/env.ora is empty. If you leave this file untouched and nothing else is set, the default $ORACLE_HOME/network/admin location is read. $cat env.ora # Copyright (c) 2015, Oracle and/or its affiliates. All rights reserved. # # NAME #   env.ora # # FUNCTION #   Oracle ENVironment variables persistency file template # # NOTES #   This is a generic file for defining environment variables. #   It facilitates the requirement to maintain persistency of environment #   variables across multiple instances in lieu of setting the variables in #   the environment. # #   Note that setting a variable in the environment takes precedence over its #   definition in this file. # # SYNTAX #   <ENVIRONMENT VARIAB

<<Back to DB Administration Main Page ACTION: To use block size of (0MB), set initialization parameter db_8k_cache_size. dbca -silent -createDatabase -responseFile $ORACLE_HOME/assistants/dbca/dbca_ORCL.rsp [FATAL] [DBT-11101] The block size (0MB) for the tablespace SYSAUX does not match the configured block size (0MB).    ACTION: To use block size of (0MB), set initialization parameter db_8k_cache_size. Cause: database being created with db_block_size=16K parameter, while the tablespace was using 8K block_size in the template used for database creation. Solution: Ensure to use correct db_block_size for each tablespace in the create database script. If you are creating database with 16K block size use 16k block size for tablespaces too.

<<Back to Oracle DataGuard Main Page RMAN-08137: WARNING: archived log not deleted, needed for standby or upstream capture process happening on Physical Standby Database Cause In one of my  Physical standby database version 12.1.0.2 the  above error appeared. After spending an hour looking various RMAN setting and configuration I found that value of log_archive_dest_state_2 parameter was not enabled, which eventually caused this issue.   SQL> show parameter dest_state_2 NAME                                 TYPE        VALUE ------------------------------------ ----------- ------------------------------ log_archive_dest_state_2             string      RESET Solution: Enable parameter  log_archive_dest_state_2 SQL> alter system set log_archive_dest_state_2=enable; System altered. SQ L> show parameter dest_state_2 NAME                                 TYPE        VALUE ------------------------------------ ----------- ------------------------------ log_ar

<<Back to Oracle DataGuard Main Page RMS0 Process is being killed on Standby Database continuously Primary drc log Data Guard Broker Status Summary:   Type                        Name                             Severity  Status   Configuration               DG_ORCLDBQ                        Warning  ORA-16607   Primary Database            ORCLDBQP                            Error  ORA-16825   Physical Standby Database   ORCLDBQS                          Warning  ORA-16824   Standby drc log Process RSM0, PID = -796688808, will be killed 11/08/2019 01:46:51 Creating process RSM0 11/08/2019 01:47:40 Process RSM0 re-created with PID = -796688832 11/08/2019 06:40:57 Process RSM0, PID = -796688808, will be killed 11/08/2019 06:42:27 Creating process RSM0 11/08/2019 06:42:35 Process RSM0 re-created with PID = -796688832 11/08/2019 06:47:37 Process RSM0, PID = -796688808, will be killed 11/08/2019 06:48:56 Creating process RSM0 11/08/2019 07:00:23 Solution : Increase the  Opera

<<Back to Oracle DataGuard Main Page When License Oracle Active Dataguard (ADG) feature You can use Oracle Data Guard as long as you are holding license for EE (DG license is included in Oracle Database Enterprise Edition). However Active Data Guard feature requires additional license on top. You need ADG license if you are using any or all of the features from the list below. Using BCT (Block Change Tracking) at standby Running redo apply services while an User PDB is open in read only Running Multi Instance redo apply -RAC Rolling Upgrade Using Active Data Guard Automatic Block Repair Global Data Services Real-Time Cascading (transferring the redo to the cascaded destination without waiting for the redo to be archived to a standby redo log file.) Active Data Guard Far Sync Starting from 18c (At least I test with 18c) you can have PDB$SEED and CDB$ROOT in read only without having ADG license.  Lets have a quick test: At Standby Database   set line 200

<<Back to DB Administration Main Page How to Patch oracle 18c/19c Non RAC (Single Instance) database NOTE: Starting from 18c Oracle calls Patch Set Update (PSU), as Release Update (RU). S tep1> Download the Patch Download the patch to be applied from oracle support Step2> Transfer the patch on the sever and unzip it using unzip utility Note: I am using OCT2019 PSU(RU) together with OJVM patch p30112122_180000_Linux-x86-64.zip  <= OCT DB PSU (DB RU) p30133603_180000_Linux-x86-64.zip  <= OCT JAVA Patch $unzip p30112122_180000_Linux-x86-64.zip -d /OCT_2019_PSU/ $unzip p30133603_180000_Linux-x86-64.zip -d /OCT_2019_PSU/ Step3> Verify Utility the Opatch version OPatch Utility: You must use the OPatch utility version as described in README file for the patch. Oracle recommends that you use the latest released OPatch version for 18c, which is available for download from My Oracle Support patch 6880880 by selecting the 18.0.0.0.0 release. $export PATH=$PATH