Database Technologies...!

Background When troubleshooting  any database performance issue, we need to understand that it can happen in two possible sequence, 1. Additional large utilization (load) on database server causing the delay in normal database operation. Which can be from newly added application component, query that is running abnormally (e.g plan change),  new parallel execution etc.. 2. A change or suboptimal operation on underlying hardware/software layer that causes the default load of the database to take more time than it usually does. Here database system is not the culprit but the victim. It is trying its best to perform as it used to, but a bottleneck in different layer that is not in the control of database is causing the delay. Example Below is a demonstration using iostat command in Solaris environment running Oracle database, which shows the metric differences while database system is experiencing performance delays. In both situation the result is the same, distorted db pe...

ORA-16433 is an error returned when a database cannot be opened. This can be a result of multiple conditions including, The inconsistency of the database Physical corruption Loss of dependent files or access issues etc.. More often than not the solution is to recover from available backups, but there can be situations where backups are not available, specially on UAT or Test setups. Although not the ideal scenario, in this type of situation you might want to open the databases even with inconsistency.  In Comes --> _ALLOW_RESETLOGS_CORRUPTION First, if you are in a position to use _ALLOW_RESETLOGS_CORRUPTION=TRUE, that means it has to be a critical moment, and when you come across more ora- errors while you are trying to reopen the databsae, that can be annoying. Note - _ALLOW_RESETLOGS_CORRUPTION=TRUE must only be used after Oracle support confirmation as a last resort. Also make sure to take cold backup before proceeding. Basic steps to be followed to resolve ORA 16433 af...

 Background Was trying to lock sys user on 12.2 RAC cluster which runs 5 CDB databases. On the first db, lock operation was successful but on the second database sys lock operation was failed with ORA-40365 as below, SQL> alter user sys account lock; alter user sys account lock * ERROR at line 1: ORA-40365: The SYS user cannot be locked while the password file is in its current format. SQL> Next step was to check the differences in password file configured between the databases. From the results, it was observed that in the database where lock operation was successful, password file format was 12.2 where as in the failing database password file was format 12. ora12c@racaprdb1:~$ srvctl config database -d OBDB | grep -i password Password file: +OBDATADG/OBDB/PASSWORD/orapwobdb ora12c@racaprdb1:~$ ora12c@racaprdb1:~$ orapwd describe file='+OBDATADG/OBDB/PASSWORD/orapwobdb'; Password file Description : format=12.2 ora12c@racaprdb1:~...

ORA-65036 can be returned when creating a PDB using the DBCA or the command line (SQLPLUS). Background is when creating a PDB Oracle depends on PDB$SEED to get the required template for the new PDB. If the PDB$SEED is not in ‘read only’ or ‘read write’ mode ora-65036 can be returned. Below is an example where error is returned with mounted state PDB$SEED, SQL> CREATE PLUGGABLE DATABASE pdb2 ADMIN USER pdb_adm IDENTIFIED BY Password1 CREATE_FILE_DEST='+DATA'; CREATE PLUGGABLE DATABASE pdb2 ADMIN USER pdb_adm IDENTIFIED BY Password1 CREATE_FILE_DEST='+DATA' * ERROR at line 1: ORA-65036: pluggable database PDB$SEED not open in required mode SQL> show pdbs; CON_ID CON_NAME OPEN MODE RESTRICTED ---------- ------------------------------ ---------- ---------- 2 PDB$SEED MOUNTED 3 ORCLPDB READ WRITE NO SQL> SQL> alter pluggable database PDB$SEED open; Pluggable databa...

If you encounter ORA-44787 when switching to PDB using alter session set container , main reason for same can be the unavailability of default PDB service. When a PDB is enabled it does starts its own default PDB service with service name set to the PDB name. Although it is possible to create new service for PDB using DBMS_SERVICE.create_service , it is required to keep the default service up and running as it is used by internal operations. If you have stopped the default service it is the main reason for this particular error. Below is a simple example where stopping of default PDB service leads to ORA-44787 Creating the new service SQL> alter session set container=ORCLPDB; Session altered. SQL> BEGIN DBMS_SERVICE.create_service( service_name => 'new_service', network_name => 'NEW_SERVICE' ); END; 2 3 4 5 6 7 / PL/SQL procedure successfully completed. SQL> exec dbms_service.start_service('new_serv...

Below are the steps to enable Archivelog mode on Oracle RAC 12c. Stop the database as grid (Both instances) srvctl stop database -d ORCL Start the database in mount mode srvctl start database -d ORCL -o mount Login to one db instances and enable archive log mode, ora12c@racbprdb2:~$ sqlplus sys as sysdba SQL*Plus: Release 12.2.0.1.0 Production on Sun Aug 9 11:25:57 2020 Copyright (c) 1982, 2016, Oracle. All rights reserved. Enter password: Connected to: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production SQL> SQL> select name, open_mode from v$database; NAME OPEN_MODE --------- -------------------- ORCL MOUNTED SQL> ALTER DATABASE ARCHIVELOG; Database altered. SQL> Restart the database, grid12c@racbprdb1:~$ srvctl stop database -d ORCL grid12c@racbprdb1:~$ grid12c@racbprdb1:~$ srvctl start database -d ORCL grid12c@racbprdb1:~$ Check the archivelog mode SQL> sel...

Setup and Backup script When using Oracle multi-tenant architecture there can be situations where it is required to setup Dataguard only for single PDB, skipping the log replication of other PDBs. Below steps can be used to achieve the above configuration on Oracle 12C  setup. The steps mainly stays same to a normal dataguard setup but main difference comes during the recovery where it is required to skip the pdb which is not in the dataguard configuration. Primeary Side PDB configuration is as below. Rman backup script is used to backup the CDB root, PDB$SEED and the PDB1. SQL> show pdbs; CON_ID CON_NAME OPEN MODE RESTRICTED ---------- ------------------------------ ---------- ---------- 2 PDB$SEED READ ONLY NO 3 PDB1 READ WRITE NO 4 PDB2 READ WRITE NO SQL> RMAN backup script --> run { ALLOCATE CHANNEL CH1 DEVICE TYPE DISK FORMAT '/tmp_01/onlback/bk_mis_u%u_s%...

Below are the steps that is required to move Oracle RAC VIPs and SCAN IPs between RAC nodes. IP Setup of the RAC as follows, ##Oracle RAC VIP## 10.10.38.70 racbprdb1-vip racbprdb1-vip.domain.int 10.10.38.71 racbprdb2-vip racbprdb2-vip.domain.int ##Oracle SCAN IP## 10.10.38.72 scan-test scan-test.domain.int 10.10.38.73 scan-test scan-test.domain.int 10.10.38.74 scan-test scan-test.domain.int 1. Check the current network config grid12c@racbprdb1:~$ srvctl config network Network 1 exists Subnet IPv4: 10.10.38.0/255.255.254.0/ipmp0, static Subnet IPv6: Ping Targets: Network is enabled Network is individually enabled on nodes: Network is individually disabled on nodes: grid12c@racbprdb1:~$ grid12c@racbprdb1:~$ srvctl config scan SCAN name: scan-test, Network: 1 Subnet IPv4: 10.10.38.0/255.255.254.0/ipmp0, static Subnet IPv6: SCAN 1 IPv4 VIP: 10.10.38.72 SCAN VIP is enabled. SCAN VIP is individua...

When an Oracle PDB is created it starts up a default service with the PDB name. For direct connections to    PDB this service can be used. But there are situations where new service name is required in PDB level, common example is after a PDB restoration/clone, in order to enable application connections without changes in connection string. Setting service_names parameter in PDB level is not possible hence it is not an option in multitenant setup. How to create a new PDB service on Oracle 12C Check the current services, here only the default service is running, SQL> show pdbs; CON_ID CON_NAME OPEN MODE RESTRICTED ---------- ------------------------------ ---------- ---------- 3 NFACTORPDB READ WRITE NO SQL> select service_id, name, pdb from dba_Services; SERVICE_ID NAME PDB ---------- ---------------------------------------- ---------- 7 nfactorpdb NFACTORPDB SQL> ...

Alter session set container - <pdb_name> can be used to switch between pluggable databases of multitenant Oracle setup. With Oracle multitenant being Oracles preferred implementation method with non-cdb architecture is deprecated in Oracle 12c - (Refer screen below ,  https://docs.oracle.com/database/121/UPGRD/deprecated.htm#BABDBCJI ), alter session set container does become a common command to use. Alter session set container example oracle@pcitdb:~$ sqlplus sys as sysdba SQL*Plus: Release 12.2.0.1.0 Production on Wed Jul 22 16:57:54 2020 Copyright (c) 1982, 2016, Oracle. All rights reserved. Enter password: Connected to: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production SQL> set lines 1000 SQL> show pdbs; CON_ID CON_NAME OPEN MODE RESTRICTED ---------- ------------------------------ ---------- ---------- 2 PDB$SEED READ ONLY NO 3 PRMUATPDB READ WRITE ...

What is Wait for unread message on broadcast channel wait event? Wait for unread message on broadcast channel is a wait event where source destination communication is involved and the database is waiting for a reply from remote party. Mostly this wait is seen related to data pump export (expdp) or import (impdp) operations. Although categorized in idle wait class "Wait for unread message on broadcast channel" can become the blocking session for some other sessions. Common causes for stuck sessions waiting on this wait event includes, Broken databases links, where remote database is not responding. Abnormally killed remote processes causing the sessions to wait. When a session is waiting on "Wait for unread message on broadcast channel" it can become a blocking session for dependent sessions. Most probably these sessions will be waiting on "library cache pin" or related concurrency wait events. The blocking_session column of (g)v$session view...

My Query Suddenly Slow - The Background My query is suddenly slow, or the database is suddenly slow. For any DBA this is a common complaint from our beloved users, no matter what database server being used this is an inevitable. So what should be the approach to troubleshoot this type of request from an angry user who has already passed the deadline to provide a report? (Normally they all start after the deadline and hope the database will do the magic ::D) Below are set of questions, tasks that can be performed on Oracle, to correctly identify and fix the situation. My Query Suddenly Slow - The Questioner 1. Slow? Is it for single user, subset of users or all the users (Application) If it is a particular query executed by one user, or particular department accessing the system via particular floor, we can always start looking for external clues, such as network delays, a bad application query impacting a particular function etc.. If the all the backend users  and ap...

What is ORA-12751? ORA-12751 is a generic error which can occur during a resource exhausted condition. Oracle definition for the particular error is below. ora12c@racaprdb1:~$ oerr ora 12751 ora-12751 cpu time or run time policy violation" // *Document: NO // *Cause: A piece of code ran longer than it is supposed to // *Action: If this error persists, contact Oracle Support Services. ora12c@racaprdb1:~$ ORA-12751 example In the given example one of the rac databases was not accepting connections intermittently. Alert log contained ora-12751 errors as below and TNS timeouts were also recorded. 2020-07-14T12:59:34.407693+05:30 MOBPDB(3):minact-scn: useg scan erroring out with error e:12751 2020-07-14T13:02:23.311541+05:30 Thread 1 advanced to log sequence 145866 (LGWR switch) Current log# 2 seq# 145866 mem# 0: +MOBREDO1DG/MOBCDB/ONLINELOG/group_2.258.989494005 Current log# 2 seq# 145866 mem# 1: +MOBREDO2DG/MOBCDB/ONLINELOG/group_2.2...

Oracle does have multiple parameters which govern how undo management happens in database. Each parameter applies in different stages, below we will discuss on each of these. What is undo_management parameter? Undo_managments is the parameter which defines how the undo extents are managed in the database. This can be either AUTO (which is the default setting 11g onwards) or manual. When set to auto Oracle does allocate, retain and free up undo extents, which is the recommended method. Undo extents that are managed by Oracle can be in one of below states, ACTIVE – Undo extents of uncommitted active queries. UNEXPIRED - Undo extents of completed queries, but still within the undo_retention value defined. EXPIERED - Undo extents of completed queries, but still within the undo_retention time period. What is _undo_autotune parameter? Simply put _undo_autotune , is   a parameter that will try to override the undo_retention parameter. When _undo_autotune is...

Prior to Oracle 12C when ever a database migration which involves an IP change was planned, we used to query the session history to find out the possible sources from which connection are established to databases. So that application changes (connections strings) required and firewall rules to be implemented can be identified. Normally a variant of below query was used which is based on dba_hist_active_sess_history (licensable), SELECT machine, program, username, COUNT (*) FROM (SELECT a.machine, a.program, b.username FROM dba_hist_active_sess_history a, dba_users b WHERE username IS NOT NULL AND A.USER_ID = B.USER_ID AND machine != '<hostname>') GROUP BY machine, program, username ORDER BY 1, 2, 3; Among the identified sources, one of the main contributor was  db_links in many of the distributed systems. Although the information returned from above query was informative, we alway...

This post is more or less for self reference. Mainly in the hope of avoiding the struggle with ', " and ' ' during the job_action definition. :) So the lesson i always forget, when defining stats jobs never try to define the job action inside DBMS_SCHEDULER.CREATE_JOB, insted create a procedure first and then create stored procedure job type. Create the job based on stats parameters required, e.g. - create or replace procedure sunday_stats is BEGIN DBMS_STATS.GATHER_SCHEMA_STATS ('SCOTT',method_opt=>'FOR ALL COLUMNS SIZE REPEAT',degree=>1,granularity=>'AUTO',cascade=>true); END; / Create the job using the above procedure with required frequency, BEGIN SYS.DBMS_SCHEDULER.CREATE_JOB ( job_name => 'scott_stat_sunday' ,start_date => SYSTIMESTAMP ,repeat_interval => 'FREQ=WEEKLY;BYDAY=SUN;BYHOUR=11;BYMINUTE=21;BYSECOND=0' ,end_date => NULL ...

When a comparatively slow performance of particular query/process is observed, one of the first things to do is to check for any plan changes. Below are the steps taken when a plan change was identified and rectified by loading sql plan from AWR via SQL Tuning Set.( STS). Check whether a plan change is impacting the performance for a given sql_id in question, select ss.snap_id, ss.instance_number node, begin_interval_time, sql_id, plan_hash_value, nvl(executions_delta,0) execs, (elapsed_time_delta/decode(nvl(executions_delta,0),0,1,executions_delta))/1000000 avg_etime, (buffer_gets_delta/decode(nvl(buffer_gets_delta,0),0,1,executions_delta)) avg_lio from DBA_HIST_SQLSTAT S, DBA_HIST_SNAPSHOT SS where sql_id = '<<SQL_ID>>' and ss.snap_id = S.snap_id and ss.instance_number = S.instance_number and executions_delta > 0 order by 1, 2, 3; Below is a scenario where Oracle is using a bad plan, with higher execution time and cost. Wit...

In a SPARC Solaris system (Ldom) which runs multiple database instances on 12.2.0.2, the alerts logs were flooded with  ORA-27103 [ ] and ORA-6544 [pevm_peruws_callback-1] [27103] error messages. Database operations were impacted, new connection requests were starting to timeout. 2020-06-01T12:47:06.635714+05:30 Errors in file /u01/app/oracle/diag/rdbms/memdb/memdb1/trace/memdb1_dbw0_19325.trc: ORA-27103: internal error Additional information: 11818 Additional information: 9 ORA-27103: internal error SVR4 Error: 11: Resource temporarily unavailable Additional information: 7175 Additional information: 1114112 Errors in file /u01/app/oracle/diag/rdbms/memdb/memdb1/trace/memdb1_dbw0_19325.trc (incident=173114) (PDBNAME=CDB$ROOT): ORA-27103 [] [] [] [] [] [] [] [] [] [] [] [] Incident details in: /u01/app/oracle/diag/rdbms/memdb/memdb1/incident/incdir_173114/memdb1_dbw0_19325_i173114.trc 2020-06-01T12:47:06.651393+05:30 Errors in file /u...