Accurate statistics are essential for Oracle database performance, enabling the query optimizer to create efficient execution plans. For a table like test, which may experience frequent data changes, automating statistics collection ensures consistent query performance without manual effort. This article provides a corrected PL/SQL script to create an Oracle Scheduler job for gathering statistics on […]
Physical reads in Oracle databases, where data is retrieved directly from disk, can significantly impact query performance and system efficiency. High physical read activity often signals inefficient buffer cache usage or suboptimal SQL execution plans. By leveraging Oracle’s DBA_HIST_SQLSTAT and V$SESSTAT views, database administrators can pinpoint resource-intensive SQL statements and sessions, enabling targeted performance tuning. […]
Parallel indexing in Oracle databases can significantly enhance query performance by leveraging multiple CPU cores to process operations concurrently. However, improper use of parallelism can lead to resource contention or inefficiencies. This article outlines how to identify, monitor, and manage parallel indexes and tables in an Oracle database to optimize performance. Below, we provide corrected […]
Lock and blocking sessions in an Oracle database can significantly impact performance, causing delays and resource contention. The “Oracle Database Lock and Blocking Sessions Script” provides a comprehensive set of SQL queries to identify, analyze, and resolve locking issues. This blog post explains the purpose, functionality, and usage of the scripts, with corrections to errors […]
Maintaining the health of an Oracle database is critical for ensuring performance, availability, and compliance. The “Oracle Database Health Check Script” provides a comprehensive set of SQL queries and commands to monitor key database components, including instance status, tablespace usage, ASM disk groups, backups, jobs, and more. This blog post explains the script’s purpose, functionality, […]
Oracle Automatic Storage Management (ASM) is a powerful feature for managing database storage, and monitoring disk group usage is essential for ensuring optimal performance and preventing storage-related issues. The “Oracle ASM Disk Group Usage Script” provides a straightforward way to check the disk group usage in an Oracle ASM environment, helping administrators track available space […]
Monitoring user logon activities in an Oracle database is critical for security and auditing purposes. The “Oracle Database User Logon Mail Script With DBLink” is a powerful tool designed to track user logins across databases and send email notifications with relevant details. This script leverages Oracle’s database link (DBLink) feature to access remote database information […]
Oracle Database is one of the most robust and widely used database management systems globally. When writing SQL or PL/SQL code, you may encounter situations where data types don’t align perfectly. This is where Oracle implicit conversion comes into play. But what exactly is Oracle implicit conversion, how does it function, and why should you […]
In the realm of oracle database management, engineers often rely on robust tools to streamline their workflows and enhance productivity. Two such indispensable tools are Oracle SQL Developer and Oracle SQLcl (command line). Both tools serve distinct purposes but are equally valuable in an engineer’s toolkit. Whether you prefer the graphical interface of SQL Developer […]
I recently encountered a problem like the following, you can follow the steps below for a solution 🙂
Hi folks, I decided to write a practical article for the Undo retention calculation. The transaction undo information generated in the database is stored in the rollback segments until the commit or rollback is performed. Sometimes our long-running queries in the database are met with the error ”ORA-01555:Snapshot Too Old”.This is due to the fact […]
I recently had to face such a problem. For those who give up, the answer is below 🙂 exec dbms_Stats.gather_schema_stats(‘TESTSCHEMA’); ERROR at line 1:ORA-20000: Schema ” TESTSCHEMA” does not exist or insufficient privilegesORA-06512: at “SYS.DBMS_STATS”, line 38330ORA-06512: at “SYS.DBMS_STATS”, line 38289ORA-06512: at “SYS.DBMS_STATS”, line 8593ORA-06512: at “SYS.DBMS_STATS”, line 38172ORA-06512: at “SYS.DBMS_STATS”, line 38317ORA-06512: at line […]
Unable to allocate memory for new incident error in file /u01/app/oracle/diag/rdbms/instance_name/instance_name/trace/instance_name_m72034.trc:ORA-04036: PGA memory used by the instance exceeds PGA_AGGREGATE_LIMIT I had a same problem and solved it by doing the following. See what the current pga_aggregate_limit is using this (in case you want to revert your changes): show parameter pga_aggregate_limit; alter system set pga_aggregate_limit = […]
Hi Folks,I recently saw wait events on the single instance database, the root cause of the problem was caused by the undo event “Wait for a undo record”. There are cases where parallel process recovery is not as fast as serial process recovery, because the PQ secondary can interfere with each other’s operation by competing […]
You can’t close an extract that is a long running transaction with Golden gate. Let’s find out how it’s done. ./ggsci You can check extract GGSCI (dev) 21> info all GGSCI (dev) 21> send extract name showtrans tabular GGSCI (dev) 21> stop extractname STOP request pending. There are open, long-running transactions. Before you stop Extract, […]
None of the ASM Ctl commands are working and are idle.when checked with ps -ef, you can see that the ASM instance is working and the database is also working well.But we have a problem, the Cluster high availability services are not working. -> asmcmd lsdgASMCMD-8102: no connection to Oracle ASM; command requires Oracle ASM […]
ERROR at line 1:ORA-09817: Write to audit file failed.SVR4 Error: 49: Disc quota exceededAdditional information: 9ORA-01013: user requested cancel of current operation Solution You should check your disk space. Example cd /u01/app/oracle/admin/orcl/adump/ rm *.aud or solaris -> find /u01/app/oracle/admin/orcl/adump/ -name *.aud -mtime +0 -exec rm -rf {} \;
When starting an Oracle Instance, the following messages are displayed in the warning log files, ORA-700 errors are called flexible claims to note that something unexpected has happened, but not fatal to a transaction or moment, so the transaction can proceed without any damage. Cause PGA_AGGREGATE_TARGET specified is highErrors in file /u01/app/oracle/diag/rdbms/bugra/BUGRA/trace/BUGRA_ora_14488.trc (incident=948025):ORA-00700: soft internal […]
We have prepared a script to learn the fra status of database instances Oracle cloud control. It caused the ORA-02020 error because the open_links parameter was insufficient. SQL> select * from dual@dbllink; ORA-02020: too many database links in use This is because both the OPEN_LINKS and the OPEN_LINKS_PER_INSTANCE server parameter are too small. SQL> show […]
If there is a gap problem in your standby dataguard environment and you are getting this error, you are in the right post. This error is a generic error and your password file indicates a problem. It’s time to check your file. Let’s get started. First of check, do a bidirectional connection test and make […]
I encountered such an error in Enterprise Manager Cloud Control 13C In the Cloud Control Manager Target , you may see issue of rack showing status as “Down” when running “show status” button Please follow the steps below for solition.. Go to the directory where Agent in is installed. <AGENT-HOME>/bin/emctl stop agent Take a backup […]
Today one of the Physical standby database environment faced an case where the Media Recovery MRP process got crashed and the standby database got out of sync. This post describes how to repair multiple or single-correct file of data files. Mon Jul 28 10:26:04 2020MRP0: Background Media Recovery terminated with error 1111ORA-01157: cannot identify/lock data […]
If you get an error like below while running root.sh file on the installation screen, you need to restart the installation. [root@db~]#/oracle/grid18c/root.shPerforming root user operation for Oracle 18c The following environment variables are set as:ORACLE_OWNER= ora18cORACLE_HOME= /oracle/grid18c Enter the full pathname of the local bin directory: [/usr/local/bin]:The contents of “dbhome” have not changed. No need […]
To change the NLS_DATE_FORMAT setting Use the below command, it will help to solve your problems for after restart database. Also, if you do not want to do this at the instance level, you can make session NLS_DATE_FORMAT based on sql queries. ALTER SESSION SET NLS_DATE_FORMAT=’DD-MM-YYYY’; [oraetkb@sp000dba01 ~]$ sqlplus / as sysdba SQL*Plus: Release 12.2.0.1.0 […]
In this post, Pluggable database ORA-01157 error we will examine.I encountered an error on when opening the database like below The data file may have been renamed at the operating system level, moved to a different directory or disk drive with or without awareness.In this case, restore and recover the data file or move the […]
When I wanted to change the password on the pluggable database user, I got an error like the one below. SQL> alter user test identified by test; alter user test identified by test ERROR at line 1:ORA-65066: The specified changes must apply to all containers View connection information. SQL> show con_nameCON_NAMEPLUG1 Login as sysdba SQL> […]
Today I will talk about a problem I encountered in pdbs “ORA-01033” error on Oracle Container Database. Query existing databases running on the container. SELECT inst_id,con_id,name,open_mode FROM gv$pdbs ORDER BY con_id,inst_id If the databases are “ALTER PLUGGABLE DATABASE ALL OPEN” If you opened it with the command, you just opened it on the relevant cdb. […]
When doing data pump operation with Oracle, you can get an error like below if schema to import is not authorized.This is very common error which comes due to insufficient privileges on tablespace. ORA-39083: Object type TABLE:”TEST”.”TEST1″ failed to create with error: ORA-01950: no privileges on tablespace ‘USERS’ SQL>select USERNAME, DEFAULT_TABLESPACE from dba_users where USERNAME=’test’; […]
When the control file file is deleted or its location changes, when you try to run oracle database, we encountered an error like below. SQL> startupORACLE instance started.ORA-00205: error in identifying control file, check alert log for more info See control file location show parameter control_files NAME TYPE VALUE control_files string +DATAC/TESTDB/CONTROLFILE/cu rrent.814.1034955455 Confirm the […]
On the Oracle GoldenGate app, you may encounter an error like below when logging in with the goldengate user. The error I encountered occurred in the 11.2.0.4 database on the Oracle 19c GoldenGate version. Oracle Bug fixed in 12.1.2.1.0 and on later versions. Correct it by adding DBMS_STREAMS_ADM.set_tag for 11g DB. Oracle will continue to […]
While working on an Oracle ASM instance, we may encounter a spfile creation error like the one below. SQL> create pfile=’/u01/ora/inittkitest3.ora’ from spfile; create pfile=’/u01/ora/inittkitest3.ora’ from spfile * ERROR at line 1: ORA-01565: error in identifying file ‘?/dbs/[email protected]’ ORA-27037: unable to obtain file status Linux-x86_64 Error: 2: No such file or directory Additional information: 3 […]
The solution for Oracle Database insufficient privileges generic error is as follows. [grid@testdb1 ~]$ srvctl start asm; [grid@testdb1 ~]$ crsctl stat res -t SQL> startup ORA-01078: failure in processing system parameters ORA-01565: error in identifying file ‘+DATA/ORCL/PARAMETERFILE/spfileORCL.ora’ ORA-17503: ksfdopn:2 Failed to open file +DATA/ORCL/PARAMETERFILE/spfileORCL.ora ORA-15056: additional error message ORA-17503: ksfdopn:2 Failed to open file +DATA/ORCL/PARAMETERFILE/spfileORCL.ora […]
ORA-03113: end-of-file on communication channel Process ID: 194555 Session ID: 1537 Serial number: 3 ERROR: failed to establish dependency between database TEDAS and diskgroup resource ora.DATAC1.dg SQL> shu immediate; SQL> alter database nomount; SQL> alter database mount; SQL> select * from v$log; SQL> alter database clear unarchived logfile group 50; SQL> alter database clear unarchived […]