Tuesday, November 28, 2023

ORA-02149: Specified partition does not exist

If you are performing a datapump export and you are returned following error for one or more tables, this means that probably a simultaneous partition maintenance operation is in progress during this time this table was accessed for export.  ORA-31693, ORA-02354, ORA-02149

ORA-31693: Table data object "<TABLE_OWNER>."<TABLE_NAME>":"<PARTITION_NAME>" failed to load/unload and is being skipped due to error: 

ORA-02354: error in exporting/importing data 

ORA-02149: Specified partition does not exist 

Saturday, November 25, 2023

RMAN-20208: UNTIL CHANGE is before RESETLOGS change

If you are performing a SCN or time based recovery and RMAN-20208 is returned, It means that there was a SCN or time-based recovery previously done after which database was opened with reset logs, and now database has a new incarnation number. If you once again want to perform a recovery for any reason, or previously done incomplete recovery was not until the point where we wanted database to be, you need to reset the incarnation of the database and repeat the incomplete recovery process. 

Friday, November 3, 2023

Database Point in Time Recovery of a Pluggable Database and Conventional Databases

Starting 12c with the introduction of pluggable databases, we can perform point-in-time recovery either at CDB (container database) level or at individual pluggable databases level, in a multi-container CDB. CDB level recovery is same as conventional database point-in-time recovery that existed prior to 12c. In this document I will explain how to perform point-in-time recovery of a CDB (that could be a non-container database, or a multi-container database with one or more PDBs); and also, recovery of an individual PDB in a multi-container CDB database.  

Thursday, October 5, 2023

Performing Flashback Restore using SCN or Time

This document explains how to enable database flashback, creating restore points and performing recovery using guaranteed restore point. In the following I will explain how we can perform a time based, or SCN based flashback restore. Before proceeding further, make sure that flashback is enabled, and flashback logs are also getting created in the fast recovery area. Flashback logs are managed automatically, and oldest flashback logs are purged by oracle if there is a space crunch sensed in the fast recovery area. Oracle will try to keep flashback logs until the time (in the past) specified in the parameter db_flashback_retention_target. However, this value is only a soft limit and specified in minutes.

Monday, August 21, 2023

ORA-16789: standby redo logs configured incorrectly

Creating standby redo log files is a recommended way of configuring dataguard in Oracle database environment. If there is any discrepancy found in standby redo log configuration, you might face ORA-16789 warning. While checking status of dataguard configuration through DGMGRL command, following is that you might see as an example, in a dataguard broker configuration.

Thursday, August 10, 2023

ORA-12578: TNS:wallet open failed


[oracle]$ sqlplus
SQL*Plus: Release Production on Fri Apr 14 08:50:59 2023
Copyright (c) 1982, 2014, Oracle.  All rights reserved.
Enter user-name: sys as sysdba
Enter password:

Sunday, June 25, 2023

ORA-16000: database or pluggable database open for read-only access

 I faced this error message when I tried to drop a table from my PDB which was opened in read-write mode without any restriction.

SQL> drop table test;
drop table test
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-16000: database or pluggable database open for read-only access
 SQL> show pdbs
---------- ------------------------------ ---------- ---------------------------
 3                PDB1                      READ WRITE      NO

Saturday, June 10, 2023

ORA-65179: cannot keep datafiles for a pluggable database that is not unplugged

You should use “including datafiles” clause while dropping a pluggable database otherwise ORA-65179 would be returned

SQL> drop pluggable database testpdb;
drop pluggable database testpdb
ERROR at line 1:
ORA-65179: cannot keep datafiles for a pluggable database that is not unplugged

Saturday, June 3, 2023

ORA-65342: source pluggable database has unrecovered transaction

Starting 12.2, if you are planning to clone a PDB locally or remotely, you can do that while source PDB is open in read-write mode (hot cloning). In 12.1, if you start your CREATE PLUGGABLE DATABASE command while a transaction is already active in the source PB, you will be returned ORA-65342. Therefore, the better option is to close the source PDB, and then start it as read-only so that no transactions could hinder the cloning process

Friday, May 26, 2023

ORA-65040: operation not allowed from within a pluggable database

 Starting 12c, after introduction of pluggable databases, there are certain operations that can be performed only at the root container level. If you try to execute that operation/SQL within the PDB, you will receive ORA-65040. In this case you must ensure that the command you are executing is being executed at the root container level. Following is only one example of such operations that can only be performed from the root container.

Friday, May 19, 2023

Listener log file is empty and logging not happening

If listener log file is empty and no logging records are being logged in the log file, this probably means that someone truncate listener log file on the fly without following proper process. In Windows OS, you can not modify or truncate a file (listener log file in this case) that is in use by another proceess, however, Linux based OS would let you do this. After that, logging would no longer continue. Here I have explained the proper way of rotating listener log file. These instructions can be used for listener log file rotation as well as solving the issue of empty listener log file discussed here.

Tuesday, May 9, 2023

TNS-12508: TNS:listener could not resolve the COMMAND given

If you are in process of rotating listener log file  and you receive TNS-12508 while disabling log_status, this means you have imposed admin restrictions in listener log file for this listener. Same error would be returned for any other settings you would want to change using “set” command from lsnrctl utility. In the following I have reproduced this error and explained how to set and unset admin restrictions in listner log file.

Rotating Listener Log File

Rotating database log files is a common and day-to-day practice for DBAs. Rotation of logs is a process whereby we rename (and archive/compress) current log files so that new log files get created. Listener’s log file is also part of this practice because size of listener log file increases very fast, and it may increase beyond several GBs within a couple of months. If listener log file is not rotated, this may sometimes cause initial connection to database become slow. Therefore, this is very important to rotate listener log file. In the following I will explain how we rotate listener log file.

Saturday, April 15, 2023

ORA-46697: Keystore password required

SQL> show pdbs
    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 MYPDB1                           READ WRITE NO
SQL> create pluggable database testpdb from MYPDB1;
create pluggable database testpdb from MYPDB1
ERROR at line 1:
ORA-46697: Keystore password required.

Saturday, April 8, 2023

Investigating "read by other session" Wait Event

This article explains about the wait event “read by other session”. In this article I will explain how to further investigate about SQLs and segments involved in this wait event. This should be noted that unless this wait event is the result of slow IO performance, this wait event has to be tuned at the application side or by tuning the segment. To find out which SQLs and which table/index are causing this wait, we can query v$active_session_history (or DBA_HIST_ACTIVE_SESS_HISTORY); SAMPLE_TIME column can be used in WHERE clause to restrict rows for the event details during a specific period. Alternatively, we can also query v$session_wait to find out sessions currently waiting on this wait event.

Tuesday, March 21, 2023

ORA-19910: can not change recovery target incarnation in control file

If you are trying to resolve ORA-19909 on your physical standby database which is out of sync with primary because of ORA-19909, and you face ORA-19910, the probably cause is that redo apply process is still active. This was exactly the case with me when I tried to reset the incarnation number of my physical primary database. In the following you can see the exact command and error message returned.

Monday, March 20, 2023

Error ORA-235 occurred during an un-locked control file transaction

Error ORA-235 occurred during an un-locked control file transaction.  This
error can be ignored.  The control file transaction will be retried.
RFS[2]: Opened log for thread 2 sequence 40781 dbid 2440526278 branch 1089779374
RFS[3]: Opened log for thread 1 sequence 40695 dbid 2440526278 branch 1089779374
RFS[4]: Opened log for thread 1 sequence 40693 dbid 2440526278 branch 1089779374
RFS[1]: Opened log for thread 2 sequence 40779 dbid 2440526278 branch 1089779374
Tue Mar 21 04:11:38 2023
Archived Log entry 66837 added for thread 2 sequence 40781 rlc 1089779374 ID 0x0 dest 2:
Tue Mar 21 04:11:40 2023
Archived Log entry 66838 added for thread 1 sequence 40693 rlc 1089779374 ID 0x0 dest 2:
Tue Mar 21 04:11:40 2023
Archived Log entry 66840 added for thread 1 sequence 40695 rlc 1089779374 ID 0x0 dest 2:
Tue Mar 21 04:11:43 2023

Friday, March 10, 2023

Read by Other Session - Oracle Wait Event

Prior to 10.1 version, this wait event was part of Buffer Busy Wait, however, after 10.1 this wait event was separated from buffer busy wait and is now visible in AWR reports. This wait event is reported if multiple sessions are waiting for the same data block to be read from the disk into the buffer cache. This could be alarming if this wait event is reported as one of the top wait events in the AWR reports. There could be different causes of this wait event to be at the top. In this article I will discuss how we can troubleshoot this wait event.

Friday, February 24, 2023

Recovering a Dropped Table using RMAN Backup

There are different ways to recover back a dropped table i.e. recovering from recycle bin if it is set to on, using flashback, importing from an export backup of the table, or using RMAN backup. In this article I will explain how to recover a table using RMAN backup. Database must be running in archivelog mode with RMAN backup available. Backup should have been from the time when table existed. Using this method, we can also recover tables to a previous state (keeping existing version of table) by providing SCN or timestamp.

Friday, February 10, 2023

Finding High CPU Consuming SQLs with Multiple Plans

 In a DBA’s life this is very common to see high CPU usage of a server. If this is happening most of the time and occurrence is common, you may think of increasing the number of CPUs of the system. This CPU busy rate may be observed in CPU usage percentage or CPU load average matrics. But if this is not a very commonly occurring issue of your system and someday you suddenly see high CPU usage, you may immediately want to kick out the sessions that are consuming high CPU to avoid a system hang and reboot (or node eviction in case of RAC).

How to Invalidate a Cursor to Load a New Plan

If an SQL is frequently in use and has different SQL plans historically for execution, there might be a scenario that current execution plan in use is a bad one. In that case you might want to get rid of this current plan so that optimizer could possibly load a better plan for next executions. In this case, you would need to invalidate the current cursor in shared pool. Following is the way how we invalidate a SQL cursor from share pool.

Friday, February 3, 2023

Moving Accidentally Created Datafile on Local File System in RAC to the Shared File System

 ORA-01157 is a common incident that happens when a DBA accidentally creates a datafile for a cluster database on the local file system of one of the nodes instead of creating on a shared file system or ASM. In this case, all instances other than the instance/node where fiel was locally created would report ORA-01157 and ORA-01110. Alert log file would show entires similar to the following.

Friday, January 20, 2023

ORA-01157: cannot identify/lock data file and ORA-01110

 ORA-01157 error along with ORA-01110 in alert log file means that a datafile that DBWR is trying to access (to read or write) is no longer available. One reason coule be that this file was accidentally removed, or file permissions were accidentally changed, thus hindering oracle processes to read/write this datafile. Another reason could be the file system - where this dataifle existed - is no longer mounted or available.

Friday, January 13, 2023

ORA-01017: invalid username/password during dataguard switchover

 If you have configured dataguard broker and you are performing database switchover using DGMGRL command, you should make sure that you connect with dataguard broker using a privileged user and supply password instead of using a forward slash (‘/’) to log  in and initiating the switchover. If you do not follow this procedure, you would be returned ORA-01017 because a privileged session with both primary and physical standby is required to initiate switchover (or failover). Following is an example of receiving the error.

Friday, January 6, 2023

Switchover to Physical Standby Database Using Dataguard Broker DGMGRL

Switching over to standby database when you are using dataguard broker is a very straightforward operation. You just need to initiatie dgmgrl command prompt, check status of standby database and simply issue switchover command. Always remember to log in to DGMGRL using password, not ‘dgmgrl /’. Otherwise you will receive ORA-01017. Also make sure that password file of primary was successfully copied to standby database after your last SYS password reset, otherwise you may face ORA-16467 during switchover.

Popular Posts - All Times