Archivelog in Oracle Database

Today we are going to learn about the most important element of Oracle Database which come’s much important after it’s backbone.Being Oracle DBA we must aware of Archive logs.Now we are going to have look at the technical definition of archive log. An archived redo log file is a copy of one of the filled members of a […]

Read More

Spfile and Pfile in Oracle

Today we are going to have look on the topic of “PFile and SPFile”. This is the most important element of the Oracle database. Being Oracle DBA we must aware about “PFile and SPFile”. This file plays an important role in  Oracle database. Let’s have look at the technical definition of “PFile and SPFile”. It will be […]

Read More

ora-02097 parameter cannot be modified because specified value is invalid

You might get ORA-02097 while changing PGA_AGGREGATE_LIMIT. SQL> show parameter PGA_AGGREGATE_LIMIT NAME TYPE VALUE ———————————— ——————————– —————————— pga_aggregate_limit big integer 2G SQL> SQL> alter system set PGA_AGGREGATE_LIMIT=5G scope=both; alter system set PGA_AGGREGATE_LIMIT=5G scope=both * ERROR at line 1: ORA-02097: parameter cannot be modified because specified value is invalid ORA-00093: pga_aggregate_limit must be between 6000M and […]

Read More

Purging AUDIT TRAIL RECORDS -AUD$ Table

Today we are going to have look at the database security’s phase. In this phase, we are going to have look at Purging AUDIT TRAIL RECORDS The parameterAUDIT_TRAIL_TYPE is specified using one of three constants. DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD: Standard audit trail (AUD$). DBMS_AUDIT_MGMT.AUDIT_TRAIL_FGA_STD: Fine-grained audit trail (FGA_LOG$). DBMS_AUDIT_MGMT.AUDIT_TRAIL_DB_STD: Both standard and fine-grained audit trails. If you want […]

Read More

Apply Patch without shutting the Database

Most of the time, we can shut down the database and listener for patching the database home. But there is a method to apply the patch without shutting down the database and listener. Checking the database and listener running [oracle@oem 12834800]$ ps -ef|grep pmon oracle 16816 1 0 03:11 ? 00:00:00 ora_pmon_PROD4 oracle 18385 1 […]

Read More

MONITORING ALERT LOG

Today we are going to have look at the “MONITORING ALERT LOG ORA-ERRORS”. With help of this script DBA can monitor all the ORA-Errors from the alert log and used to send to our mailboxes, he schedules this through a cronjob. Being DBA we all are aware of the importance of Alert Log. For managing the backup […]

Read More

How to find database growth on a Monthly wise

Today we are going to have look at the Database growth. With the help of the given query. DBA can find the monthly growth of there database. Sometimes little information is too much useful.       select to_char(CREATION_TIME,’RRRR’) year, to_char(CREATION_TIME,’MM’) month, round(sum(bytes)/1024/1024/1024) GB from   v$datafile group by  to_char(CREATION_TIME,’RRRR’),   to_char(CREATION_TIME,’MM’) order by   1, 2; Output […]

Read More

SQLT in Oracle

SQLTXPLAIN, also known as SQLT, is a tool provided by Oracle Server Technologies Center of Expertise – ST CoE. SQLT inputs one SQL statement and outputs a set of diagnostics files. These files are commonly used to diagnose SQL statements performing poorly. SQLT connects to the database and collects execution plans, Cost-based Optimizer CBO statistics, […]

Read More

Drop the SQLT in Oracle Database

Uninstalling SQLT removes the SQLT repository and all SQLTXPLAIN/SQLTXADMIN schema objects. SQLTXPLAIN and SQLTXADMIN users also gets dropped. To uninstall SQLT simply execute sqlt/install/sqdrop.sql connected as SYS. [oracle@localhost ~]$ cd /home/oracle/sqlt/install [oracle@localhost install]$ sqlplus / as sysdba SQL*Plus: Release 12.2.0.1.0 Production on Wed Feb 14 04:31:10 2018 Copyright (c) 1982, 2016, Oracle. All rights reserved. […]

Read More

How to increase number of database writer in Oracle Database

Database Writer Process (DBW) The database writer process (DBW) writes the contents of database buffers to data files. DBW processes write modified buffers in the database buffer cache to disk. Although one database writer process (DBW0) is adequate for most systems, you can configure additional processes—DBW1-DBW9, DBWa-DBWz and BW36-BW99—to improve write performance if your system […]

Read More