Wednesday, May 29, 2024

ORA-00821: Specified value of sga_target 5120M is too small, needs to be at least 12352M

There could be situations where you are trying to start your database instance, however, it returns an error message instead of starting up. Such errors are mostly related to init parameters and must be fixed before retrying to start the instance. Issue could be related to an invalid value of a parameter or syntax error in the parameter file. In the following I will explain one of the errors that are related to an invalid value of parameter SGA_TARGET.

Friday, May 24, 2024

ORA-04036: PGA memory used by the instance exceeds PGA_AGGREGATE_LIMIT

This is a very critical error message that many applications started seeing since 12c. Prior to 12c, PGA of a server process could grow unabatedly. After the advent of PGA_AGGREGATE_LIMIT, PGA usage for all connected sessions could be limited to avoid enormous growth of PGA memory due to bad PL/SQL code (or due to an Oracle bug causing a process memory leak). Whenever total PGA usage by all sessions tries to go beyond the value set in this parameter, ORA-04036 is returned to the session and also logged in the alert log file (and trace file is also generated) . Alert log file could log error message similar to the following.

Monday, May 20, 2024

ORA-13792: This operation requires a database link

There could be other reasons for this message to appear while you are trying to execute something in a standby database (or perhaps on primary database), however, in my case, this message was returned when I was trying to create an SQL Tuning Advisor task and wanted to submit for advisor’s recommendations. While doing so, I did not realize that I was actually trying to execute this on physical standby database. Following is the entire error stack that was returned.

Friday, March 29, 2024

ORA-00371: not enough shared pool memory, should be at least 16341008384 byte

SQL> startup pfile=initaux.ora 

ORA-00371: not enough shared pool memory, should be at least 16341008384 bytes  

Whenever we start an oracle database instance, it requires memory to allocate some memory components of the SGA so that instance could serve the database and database sessions. If we do not specify size of memory components, we might face errors during instance startup. sga_target is the basing parameter to specify before we start an instance and other memory components like shared pool, buffer cache, or log buffer cache are allocated from the memory allocated to SGA.

Wednesday, January 10, 2024

ORA-56747: invalid value ... for parameter sga_target

SQL> alter system set sga_target=4257m; 

alter system set sga_target=4257m 


ERROR at line 1: 

ORA-02097: parameter cannot be modified because specified value is invalid 

ORA-56747: invalid value 4463788032 for parameter sga_target; must be smaller 

than parameter sga_target of the root container 

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.

Popular Posts - All Times