If you try changing a static parameter value in the SPFILE used at instance startup, you would get the following error message: SQL> alter system set audit_trail=DB scope=both; * ERROR at line 1: ORA-02095: specified initialization parameter cannot be modified SCOPE=BOTH applies the modification at the instance level and in the spfile used at startup. Use the value that applies the modification in the spfile only, provided that the spfile was used at … [Read more...] about How to Modify Static Parameter value in spfile
oracle
Unable to instantiate disk “ASM_DISK” – error on running ‘oracleasm scandisks’ command
The Problem The new disk has been assigned to a host and the user needs to run a scandisk. But the command fails to instantiate new disks with the below error. # oracleasm scandisks Reloading disk partitions: done Cleaning any stale ASM disks... Scanning system for ASM disks... Instantiating disk "ASM_DISK_01" Unable to instantiate disk "ASM_DISK_01" Instantiating disk "ASM_DISK_02" Unable to instantiate disk "ASM_DISK_02" Instantiating disk "ASM_DISK_03" Unable to instantiate disk … [Read more...] about Unable to instantiate disk “ASM_DISK” – error on running ‘oracleasm scandisks’ command
Real-time SQL Monitoring for Developers – Oracle Database 19c New Feature
The real-time SQL monitoring is an 11g new feature that enables DBAs to monitor the performance of SQL statements while they are executing, Please refer the following post for details. Understanding Real-Time SQL Monitoring in Oracle 11g Beginners Guide to Monitoring SQL Statements with Real-Time SQL Monitoring in Oracle Database Because a primary job duty of database developers is to write and tune SQL statements, starting in Oracle Database 19c, Oracle allows database user to view their … [Read more...] about Real-time SQL Monitoring for Developers – Oracle Database 19c New Feature
Beginners Guide to Monitoring SQL Statements with Real-Time SQL Monitoring in Oracle Database
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
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