The post explains how to use Real-Time SQL Monitoring to monitor queries. The Oracle 11g Database or later versions have a new interface to monitor long-running SQL commands. The feature is called Real-Time SQL Monitoring. By default, SQL monitoring is automatically started when a SQL command runs parallel, or when it has consumed at least five seconds of the CPU or I/O time in a single execution. The MONITOR hint can also be used to turn on SQL Monitoring for a SQL statement. SQL Monitoring … [Read more...] about Beginners Guide to Monitoring SQL Statements with Real-Time SQL Monitoring in Oracle Database
oracle
Understanding Real-Time SQL Monitoring in Oracle 11g
The real-time SQL monitoring is an Oracle database 11g new feature that enables you to monitor the performance of SQL statements while they are executing. Real-time SQL monitoring is automatically started when a SQL command runs in parallel or when it has consumed at least 5 seconds of CPU or I/O time in a single execution. PREREQUISITES statistics_level = TYPICAL or ALL control_management_pack_access = DIAGNOSTIC+TUNING HINTS RELATED TO REAL-TIME SQL MONITORING MONITOR: To force … [Read more...] about Understanding Real-Time SQL Monitoring in Oracle 11g
How to convert NON-OMF to OMF files ( Oracle Managed File conversion – NON-ASM )
1. Who Can Use Oracle Managed Files? Oracle Managed Files are most useful for the following types of databases: A logical volume manager that supports striping/RAID and dynamically extensible logical volumes. A file system that provides large, extensible files Low end or test databases. Initialization Parameters That Enable Oracle Managed Files for Datafiles, Redo and Controlfiles: DB_CREATE_FILE_DEST DB_CREATE_ONLINE_LOG_DEST_n DB_RECOVERY_FILE_DEST The name that is used for … [Read more...] about How to convert NON-OMF to OMF files ( Oracle Managed File conversion – NON-ASM )
ORA-28007: the password cannot be reused
The Problem You attempt to alter a user's password to a previously used one and get the following error when attempting to reuse the old password: SQL> alter user med identified by med * ERROR at line 1: ORA-28007: the password cannot be reused The password 'med' has been assigned to this user previously. The Solution The user is assigned a profile that defines the number of changes required before a password can be reused. The parameter in the profile is the limit … [Read more...] about ORA-28007: the password cannot be reused
Beginners Guide to Oracle Password Management Policy
Overview Password Management is setup by DBAs using Oracle Profiles. A general recommendation is to assign only those schemas a profile with password aging and expiration features if the users have the ability to independently change their password from their end-user application when the password expires, usually, this means the application they use must have correctly implemented the OCIPasswordChange() OCI call such as sqlplus. A Profile is setup with the required password parameters and … [Read more...] about Beginners Guide to Oracle Password Management Policy
Understanding Device Persistence and Oracle ASMLib
Basics This post describes some advantages the Linux specific ASM library provided by Oracle (herein "ASMLib") brings to the administration of a Linux system running Oracle. Linux often presents the challenge of disk name persistence. Change the storage configuration and a disk that appeared as /dev/sdg yesterday can appear as /dev/sdh after a reboot today. How can these changes be isolated so that they do not affect ASM? Why Not Let ASM Scan All Disks? ASM scans all disks it is allowed to … [Read more...] about Understanding Device Persistence and Oracle ASMLib
ASMLib-Managed Disks on Multipathed iSCSI Targets are not Discovered after Server Reboot in CentOS/RHEL 7
The Problem Oracle Real Application Clusters (RAC) servers run on CentOS/RHEL 7 and utilizing ASMLib-managed iSCSI multipathed disks. Upon server reboot, Oracle ASMLib disks are not automatically discovered at boot time causing Oracle Cluster Ready Services (CRS) to fail to start. ASM disks are, however, correctly discovered when manually running the oracleasm scandisks command soon after the server reboot. Following is the oracleasm configuration # cat … [Read more...] about ASMLib-Managed Disks on Multipathed iSCSI Targets are not Discovered after Server Reboot in CentOS/RHEL 7
How to load SELinux Module For Oracleasm
Question: How to load the SELinux module for oracleasm? When searching for the selinux oracleasm module with the locate command it may return as if the file exists: # locate /oracleasm.pp /etc/selinux/targeted/modules/active/modules/oracleasm.pp However, when running the command semodule to load the oracleasm.pp module it fails: # semodule --verbose -i /etc/selinux/targeted/modules/active/modules/oracleasm.pp Attempting to install module … [Read more...] about How to load SELinux Module For Oracleasm
SLES 12: Database Startup Error with ORA-27300 ORA-27301 ORA-27303 While Starting using Srvctl
The Problem 12.1 RAC database error while starting via srvctl, alert log showing database startup and further goes down: alert_ORCL2.log inside [oracle base]/diag/rdbms/[db name]/[SID name]/trace: Fri Nov 03 15:06:25 2017 Adjusting the default value of parameter parallel_max_servers from 960 to 486 due to the value of parameter processes (600) Starting ORACLE instance (normal) (OS id: 19684) . . Fri Nov 03 15:10:45 2017 Process startup failed, error stack: Fri Nov 03 15:10:45 … [Read more...] about SLES 12: Database Startup Error with ORA-27300 ORA-27301 ORA-27303 While Starting using Srvctl
New Connections to the Database lead to ORA-12518 or TNS-12518
The Problem ORA-12518 errors in the listener log when new users are connecting to the Database. Example is as follows: TNS-12518: TNS:listener could not hand off client connection TNS-12547: TNS:lost contact TNS-12560: TNS:protocol adapter error TNS-00517: Lost contact IBM/AIX RISC System/6000 Error: 32: Broken pipe This is an intermittent problem that would likely occur at peak database usage times. The Solution There are two possible causes: If using DEDICATED … [Read more...] about New Connections to the Database lead to ORA-12518 or TNS-12518