Database Technologies...!

 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...

Hello, This blog contains various concepts and troubleshooting steps related to multiple database technologies ranging from on-premises Oracle 9i database to Oracle cloud, DB2 and AWS. Idea is to share the knowledge on topics which are lightly documented, so that the site can help some one out during critical system troubleshooting. Contact -- Oracle Community Profile (Whiz Contributor) Mail - dbbloggermail@gmail.com 

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...

This is a step by step guide on OpenEdge Replication setup with 2 nodes. Here I ll be setting up replication for Openedge 10.2B databases on RHEL 6.2 on top of Virtualbox. My replication environment will be configured for automatic transition, (DR conversion to primary) and will be using Defer Agent Startup (DR database can be created while production is running - minimum production timeout) method. With OpenEdge DR option comes as a separate installable add-on products for database. It has two variants. 1. OpenEdge Replication. 2. OpenEdge Replication Plus. (Provides the possibility to perform read-only actions on DR DB) Virtualbox Setup Install OS (Mine - RHEL 6.2) and  Progress Openedge database (Mine -OpenEdge Enterprise DB 10.2B) and the Fathom replication product (Mine - OpenEdge Replication plus). After installation check with ‘showcfg’ on proenv prompt, should display both the products. Clone or manually create another duplicate server.  M...