7.3.23

how to select nextval from sequence?

How to select the next value from the sequence?

select xxxx.nextval from dual;

where XXXX is your sequence name.

NEXTVAL is a function used in oracle to get the next value of the sequence.

No current value exists for the sequence until the Oracle NEXVAL function has been called at least once.

A sequence is a database object, Which helps in generating a unique value(integer).

One sequence can serve multiple users to generate a unique value.

Sequences can be used to generate primary key values automatically as well.

Check This

2.3.23

Which two actions would reduce the job's elapsed time?

A. Increasing the priority of the job class to which the job belongs
B. Increasing the job's relative priority within the Job class to which it belongs
C. Increasing the resource allocation for the consumer group mapped to the scheduler job's job class
within the plan mapped to the scheduler window
D. Moving the job to an existing higher-priority scheduler window with the same schedule and duration
E. Increasing the value of the JOB_QUEUE_PROCESSES parameter
F. Increasing the priority of the scheduler window to which the job belongs

Correct Answer: BC

Explanation: B: Job priorities are used only to prioritize among jobs in the same class.
Note: Group jobs for prioritization
Within the same job class, you can assign priority values of 1-5 to individual jobs so that if two jobs in the
class are scheduled to start at the same time, the one with the higher priority takes precedence. This
ensures that you do not have a less important job preventing the timely completion of a more important one.
C: Set resource allocation for member jobs
Job classes provide the link between the Database Resource Manager and the Scheduler, because each
job class can specify a resource consumer group as an attribute. Member jobs then belong to the specified
consumer group and are assigned resources according to settings in the current resource plan.

16.1.22

How many sessions are allowed on the Oracle database?

The theoretical real number of maximum sessions depends on the horsepower of your server.

You determine the runtime maximum number of sessions with the SESSIONS parameter, which derives the PROCESSES parameter.

SQL> show parameter sessions

To see the existing maximum number of sessions, try this query:

select current_utilization, limit_value from gv$resource_limit where resource_name='sessions';

Try this to show info about both:

    select resource_name, current_utilization, max_utilization, limit_value 
    from v$resource_limit 
    where resource_name in ('sessions', 'processes');

1.1.22

ORA-00240 control file enqueue held for more than XXX seconds

Description： control file enqueue held for more than string seconds

Cause 1:-

The current process did not release the control file enqueue within the maximum allowed time.

Cause 2:-

This issue was investigated in unpublished Bug by oracle 7570453 - [3 RAC NODE] ORA-00240 WHEN STARTUP AFTER UPGRADE TO 10.2.0.4.0 which was closed as Not a Bug. The Developers confirmed that this is just a warning to let the DBA know that a CF enqueue is being held for more than 120 seconds.

This is not an error, and error will occur if a CF enqueue is held for more than 900 seconds (15 minutes) and this is not the case here.

The message occurs when there are many datafiles in the database and DBWriter is taking too much time to release the CF enqueue due to having to open these datafiles.

Action： 1. Reissue any commands that failed and contact Oracle Support Services with the incident information.

2. The recommendation will be to reduce this time by reducing the number of database files you have. It is more recommended having less but bigger datafiles instead of smaller but lots of datafiles.

We aware to check alert log for sure.

how to Take care of Oracle Audit

What are Oracle 10days rule??

Bug 20639511 ORA-00240: CONTROL FILE ENQUEUE HELD FOR MORE THAN 120 SECONDS

Description

RELEASE NOTES:
 
 The bug is know to cause issues while deleting large number of files from ASM dectory
 
Rediscovery Notes
 
 Server Process of asmcmd rm command with -rf option holds enqueues for long time.
 
Workaround
 
  Kill the ongoing asmcmd rm command with -rf option and delete files one by one

Flex: ORA-00240: Control File Enqueue Held for More Than 120 Seconds (Doc ID 2434297.1)

Cause - This is due to Bug 25445289 (FLEX: ORA-00240: CONTROL FILE ENQUEUE HELD FOR MORE THAN 120 SECONDS) and the bug 25445289 is closed as duplicate of Bug 25368979
(FLEX: ASM STALLS IN ALTER DISKGROUP ALL MOUNT, ONLY 1 OF 3 COMPLETE IN 30 MIN)

Solution:- Bug 25368979 fixed in 18.1. Apply interim patch 25368979, if available for your platform and Oracle version.
If no patch exists for your version, please contact Oracle Support for a backport request.

Source:- Oracle

Below by ChatGPT

The error message "ORA-00240 control file enqueue held for more than XXX seconds" in Oracle indicates that a process is waiting for a control file enqueue lock for an extended period of time. This can occur due to various reasons, such as high system load, contention, or issues with the control file itself. Here are some possible solutions to address this error:

Check system load: Monitor the system load and resource usage. If the system is heavily loaded, you may need to reduce the workload or allocate more resources to alleviate the contention.
Investigate contention: Identify if there are any other processes or transactions that might be causing contention for the control file enqueue lock. You can use Oracle's dynamic performance views, such as V$SESSION and V$LOCK, to identify the blocking sessions and take appropriate actions to resolve the contention.
Verify control file accessibility: Ensure that the control file is accessible and not corrupted. Check the file permissions, disk space, and integrity of the control file. If necessary, restore a backup of the control file or recreate it.
Increase resources: If the system consistently experiences high contention for the control file enqueue, consider increasing the number of control files in the Oracle database configuration. Having multiple control files can help distribute the load and reduce contention.
Optimize database performance: Review the database performance and identify any potential bottlenecks or resource-intensive operations. Optimizing queries, tuning database parameters, and implementing appropriate indexing strategies can help improve overall performance and reduce contention.
Restart the database: In some cases, restarting the database can help resolve temporary contention issues. However, ensure that all necessary precautions are taken, such as ensuring a clean shutdown and performing a thorough analysis of the underlying cause before resorting to a restart.
Seek expert assistance: If the issue persists or you are unsure about the appropriate actions to take, consider involving a database administrator or Oracle support for further analysis and resolution.

It's important to note that the specific solution may vary depending on the underlying cause of the contention. Analyzing the system logs, and performance metrics, and consulting with experienced Oracle professionals can help in identifying and resolving the issue effectively.

14.9.21

How to Design a Good Oracle RAC Set-up on a QA Server for Developers?

Please help on this. Please suggest how to do achieve this in low cost?

5.8.21

What is Oracle RAC and 5 Key Points to remember about Oracle RAC

What is Oracle RAC

Oracle RAC stands for Real Application clusters which is a high availability solution from Oracle. It is one of the best solutions till now in all database technologies from Oracle. Oracle RAC using shared everything architecture. In Oracle RAC, All nodes(instances) access the same data and perform read/write operations. Basically, Oracle RAC achieves zero downtime. If one Node(instance) in Oracle RAC got crashed in Oracle RAC it will switch transactions to another Node(instance). The main beauty of Oracle RAC is you can add and remove nodes(instance) on your needs so you can scale up/down. Oracle RAC works with clustering which has advantages and gives you to avoid a single point of failure.

More on Oracle RAC

source:- oracle

5 Key Points to remember for Oracle RAC:-

1. Oracle RAC, achieve better performance and high availability as compare to other present solution in the market.

2. Oracle RAC, scale up/down feature helps to control the cost. You can easily add and remove nodes from RAC environment In a high load situation you can add one or more nodes and when no load can delete the node. This management is very easy.

3. Oracle RAC, Works well with data consistency as all nodes updates data separately and users still see real-time data.

4. Oracle RAC, Easy to manage to patch as all node(instance) is independent on resources.

5. Oracle RAC, When one node(instance) got crashed all connection moves to the next available node(instance). Please note that only select statements move.

Oracle RAC.

Let us know your thoughts in the comment section. Keep sharing :)

WARNING:COULD NOT LOWER THE ASYNCH I/O LIMIT TO 160 FOR SQL DIRECT I/O

WARNING: COULD NOT LOWER THE ASYNCH I/O LIMIT TO 160 FOR SQL DIRECT I/O

We faced this error in our 10.2.0.2 client-side database server trace logs. Users were not able to access the application and even they could not start the related services on the application server.

When we checked the DB server we found that there was no space left on ORACLE_HOME. Further checking leads us to huge trace files generated and we talking about 20GB. So for a temporary fix, we deleted few largest trace files and some old trace files, as well as the most important thing, is to maintain the state of the application up and running. After this application was back on track.

A little history:-

This server was restored from the crash and localit247.com missed to implement the log purging for this new DB server. So we needed to the RCA.

Below is information shared with the application Team:-

Faced Issue: Users were not able to access the application and cannot start the related services.

Root Cause: No space left for drive causing the server to halt. Oracle tracing logs under DB_INSTANCE > udump folder were the culprit of storage consumption.

Temporary fix: Deleted few largest files.

We checked for "WARNING:COULD NOT LOWER THE ASYNCH I/O LIMIT TO 160 FOR SQL DIRECT I/O" and found that it's because of the bug. The best way to check that is on the oracle support website. We keep checking on another famous site like uatdb.com

and few good blogs for "WARNING:COULD NOT LOWER THE ASYNCH I/O LIMIT TO 160 FOR SQL DIRECT I/O".

There are two solutions provided by oracle on the support website for this bug:-

1. Upgrade to 11.2.0.2 or higher

2. Apply 10.2.0.5.2 PSU where the fix for Bug 9772888 has been included.

You need to open a service request with oracle on oracle support if not able to find any patch as per your platform or you may implement temporary by creating a batch script to delete all *.trc files older than 15-30days under UDUMP folder for 10G database.

In our case, we are already in the progress to move the database to Oracle 19C. So we choose to implement a batch script to delete the trace file.

Hope this helps you in handling “WARNING:COULD NOT LOWER THE ASYNCH I/O LIMIT TO 160 FOR SQL DIRECT I/O”. Keep sharing..

21.7.21

How to Stop and Disable Firewall on CentOS 8

Most of the time this task is done by system admins but if you work in a small organization like me(God helps you) :) then you need to do many system admin tasks by yourself.

I am writing this blog because recently faced issue similar issue when we moved one of the 19C databases to Azure cloud. After setting up the database application team was not able to reach the database. I did some checking and found that the Linux admin missed disabling the firewall on the DB server. So I decided to stop the firewall on DB server by myself.

Sharing steps with you all, so may help you on "How to Stop and to Disable Firewall on CentOS 8"

There may be many methods but I used firewall-cmd to stop the firewall.

Man Page DESCRIPTION for firewall-cmd

NAME

firewall-cmd - firewalld command line client

SYNOPSIS

firewall-cmd [OPTIONS...]

DESCRIPTION

firewall-cmd is the command-line client of the firewall daemon. It provides an interface to manage the runtime and permanent configurations.

The runtime configuration in firewalld is separated from the permanent configuration. This means that things can

get changed in the runtime or permanent configuration

Note: You need the root user or any user to have SUDO to do this change.

Steps to top and Disable Firewall on CentOS 8:-

1. Check the Status of the firewall using Firewalld

[root@TEST-D01 ~]# firewall-cmd --state

running

2. Stop firewall using Firewalld

[root@TEST-D01 ~]# systemctl stop firewalld

3. Check the Status of firewall using Firewalld

[root@TEST-D01 ~]# firewall-cmd --state

not running

Keep Sharing :)

13.7.21

How to Change the Timezone in Linux

Easy steps to change timezone on "CentOS Stream release 8"

It's very simple to change the timezone of Linux machines. Sometimes as DBA you need to change the timezone on cloud environments when you handling multiple sites on the cloud.

Just follow the below steps to "Change the Timezone in Linux" :-

1) login with root or any sudo user to your machine and check current timezone.

[root@TEST-D01 ~]# timedatectl

Local time: Tue 2021-07-13 03:40:06 EEST

Universal time: Tue 2021-07-13 00:40:06 UTC

RTC time: Tue 2021-07-13 00:40:06

Time zone: Europe/Kiev (EEST, +0300)

System clock synchronized: no

NTP service: n/a

RTC in local TZ: no

My Current timezone is "Europe/Kiev (EEST, +0300)".

So Let's change the timezone to Asia.

2) You can get the list of all timezones in Asia by "timedatectl list-timezones |grep Asia"

[root@TEST-D01 ~]# timedatectl list-timezones |grep Asia

Asia/Aden

Asia/Almaty

Asia/Amman

Asia/Anadyr

Asia/Aqtau

Asia/Aqtobe

Asia/Ashgabat

Asia/Atyrau

Asia/Baghdad

Asia/Bahrain

Asia/Baku

Asia/Bangkok

Asia/Barnaul

Asia/Beirut

Asia/Bishkek

Asia/Brunei

Asia/Chita

Asia/Choibalsan

Asia/Colombo

Asia/Damascus

Asia/Dhaka

Asia/Dili

Asia/Dubai

Asia/Dushanbe

Asia/Famagusta

Asia/Gaza

Asia/Hebron

Asia/Ho_Chi_Minh

Asia/Hong_Kong

Asia/Hovd

Asia/Irkutsk

Asia/Jakarta

Asia/Jayapura

Asia/Jerusalem

Asia/Kabul

Asia/Kamchatka

Asia/Karachi

Asia/Kathmandu

Asia/Khandyga

Asia/Kolkata

Asia/Krasnoyarsk

Asia/Kuala_Lumpur

Asia/Kuching

Asia/Kuwait

Asia/Macau

Asia/Magadan

Asia/Makassar

Asia/Manila

Asia/Muscat

Asia/Nicosia

Asia/Novokuznetsk

Asia/Novosibirsk

Asia/Omsk

Asia/Oral

Asia/Phnom_Penh

Asia/Pontianak

Asia/Pyongyang

Asia/Qatar

Asia/Qostanay

Asia/Qyzylorda

Asia/Riyadh

Asia/Sakhalin

Asia/Samarkand

Asia/Seoul

Asia/Shanghai

Asia/Singapore

Asia/Srednekolymsk

Asia/Taipei

Asia/Tashkent

Asia/Tbilisi

Asia/Tehran

Asia/Thimphu

Asia/Tokyo

Asia/Tomsk

Asia/Ulaanbaatar

Asia/Urumqi

Asia/Ust-Nera

Asia/Vientiane

Asia/Vladivostok

Asia/Yakutsk

Asia/Yangon

Asia/Yekaterinburg

Asia/Yerevan

Now We have the list of all timezones in Asia. Let's set it e.g. to Asia/Qatar.

3. To change the timezone from Europe/Kiev to Asia/Qatar. just run

[root@TEST-D01 ~]# timedatectl set-timezone Asia/Qatar

[root@TEST-D01 ~]# timedatectl

Local time: Tue 2021-07-13 04:02:48 +03

Universal time: Tue 2021-07-13 01:02:48 UTC

RTC time: Tue 2021-07-13 01:02:49

Time zone: Asia/Qatar (+03, +0300)

System clock synchronized: no

NTP service: n/a

RTC in local TZ: no

30.11.20

ORA-00907: missing right parenthesis ! while creating view

ORA-00907: missing right parenthesis

"ORA-00907: missing right parenthesis" can happen if you really missing it in your SQL, But if that is the case you will not search on google.com :).

One of Our oracle SQL developers reported that while creating a view on one of the oracle database were facing "ORA-00907: missing right parenthesis". We started troubleshooting and found that it's a bug from the oracle in 12.2 databases. The solution is just to apply the patch or change the view of the code.

CREATE OR REPLACE FORCE VIEW "TEST_V" ("TEST_COL1", "TEST_COL2") AS

SELECT

-----

FROM TEST_T A WHERE TEST_COL1 IS NOT NULL

GROUP BY

-----

REGEXP_REPLACE(LISTAGG(TEST_COL1,';' ON OVERFLOW TRUNCATE) WITHIN GROUP (ORDER BY TEST_COL1),'([^;]+)(;\1)*(;|$)', '\1\3') AS TEST,

ERROR at line 12:

ORA-00907: missing right parenthesis

This view we were able to create in 12.1 but not in 12.2 so we in the last asked developers to change the view. That is how we handle "ORA-00907: missing right parenthesis" issue. If you are facing the same issue and are not able to find any clue, just try to create it on the older version. The actual information regarding the bug is not available yet but we update soon we will find it.

Oracle Means company, not database :) Keep sharing...

26.11.20

Oracle Fail Safe Compatibility with 19c

Is oracle fail Safe is not compatible with the windows 2019?

The answer is yes.

Below is the compatibility Matrix.:-

Note that version 4.1.0 is already on extended support.

Also, check "Oracle Fail Safe is deprecated with Oracle Database 19c".

Oracle Mean company, not database :)

2.11.20

ORA-65114: space usage in container is too high

I was creating tablespace in a newly created PDB and was facing " ORA-65114: space usage in the container is too high "

ORA-65114: space usage in the container is too high

So it happens when you created PDB with a storage option less than you need. Below is the example, I used to create PDB:

create pluggable database TEST admin user admin identified by "oracle" default tablespace USERS

datafile '/u02/oradata/TEST/pdb1_users01.dbf' size 250m autoextend on

storage (maxsize 1g max_shared_temp_size 1g)

file_name_convert=('/u02/oradata/TEST/datafile/','/u02/oradata/TEST/TEST');

Want to know drawback of oracle ? check this

This mean oracle will not go beyond 1G. As I used MAX to 1G and below is the error.

Below is the error:-

SQL> CREATE TABLESPACE "TEST" LOGGING DATAFILE '/u02/oradata/TEST.DBF' SIZE 750M AUTOEXTEND ON NEXT 8M MAXSIZE 2048M EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO;

CREATE TABLESPACE "TEST" LOGGING DATAFILE '/u02/oradata/TEST.DBF' SIZE 750M AUTOEXTEND ON NEXT 8M MAXSIZE 2048M EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO;

ERROR at line 1:

ORA-65114: space usage in container is too high

Solution:-

Just increase the storage limit of the pluggable database. Run the following SQL in the current PDB to limit the size of all datafiles of that PDB:

SQL> ALTER PLUGGABLE DATABASE STORAGE UNLIMITED;

Pluggable database altered.

OR if you want a limited SIZE, You can still define the size.

SQL> alter pluggable database storage (MAXSIZE 120G);

Note:- If not defined storage option by default of MAX_PDB_STORAGE is no limit.

How to check the max size of PLUGGABLE DATABASE STORAGE?

To check value in current PDB, Conenct to PDB and run below :

select PROPERTY_VALUE FROM database_properties WHERE property_name = 'MAX_PDB_STORAGE';

To check value in current CDB for all PDB'S, Conenct to CDB and run below :

select PROPERTY_NAME,PROPERTY_VALUE,DESCRIPTION,CON_ID FROM cdb_properties WHERE property_name = 'MAX_PDB_STORAGE';

Oracle Mean company, not database :)

If it helps. Please comment and let us know :)

OPatch failed with error code 73

I was upgrading 19.3 to 19.7 and applying the below patch and during the patch, I faced the error "OPatch failed with error code 73:-

30805684: OJVM RELEASE UPDATE 19.7.0.0.0

Note:- All instances were down.

Please check below opatch process :-

opatch apply

Oracle Interim Patch Installer version 12.2.0.1.21

Oracle Home : /u01/app/oracle/product/19.0.0/dbhome_1

Central Inventory : /u01/app/oraInventory

from : /u01/app/oracle/product/19.0.0/dbhome_1/oraInst.loc

OPatch version : 12.2.0.1.21

OUI version : 12.2.0.7.0

Logfile location: /u01/app/oracle/product/19.0.0/dbhome_1/cfgtoollogs/opatch/opatch2020-10-19_16-26-35PM_1.log

Verifying environment and performing prerequisite checks...

Prerequisite check "CheckActiveFilesAndExecutables" failed.

The details are:

Following active executables are not used by opatch process :

/u01/app/oracle/product/19.0.0/dbhome_1/bin/oracle

Following active executables are used by opatch process :

UtilSession failed: Prerequisite check "CheckActiveFilesAndExecutables" failed.

Log file location: /u01/app/oracle/product/19.0.0/dbhome_1/cfgtoollogs/opatch/opatch2020-10-19_16-26-35PM_1.log

OPatch failed with error code 73

In Logs:-

Following active executables are not used by opatch process :/u01/app/oracle/product/19.0.0/dbhome_1/bin/oracle

Following active executables are used by opatch process : Prerequisite check "CheckActiveFilesAndExecutables" failed.

The details are:

Following active executables are not used by opatch process : /u01/app/oracle/product/19.0.0/dbhome_1/bin/oracle

Solution:-

This was happening because there was one session that was still connected to sqlplus binaries.

ps -ef|grep sqlplus

Killed the session and successfully completed it.

opatch apply

Oracle Interim Patch Installer version 12.2.0.1.21

Oracle Home : /u01/app/oracle/product/19.0.0/dbhome_1

Central Inventory : /u01/app/oraInventory from

: /u01/app/oracle/product/19.0.0/dbhome_1/oraInst.loc

OPatch version : 12.2.0.1.21

OUI version : 12.2.0.7.0

Log file location : /u01/app/oracle/product/19.0.0/dbhome_1/cfgtoollogs/opatch/opatch2020-10-19_16-33-39PM_1.log

Verifying environment and performing prerequisite checks...

OPatch continues with these patches: 30805684

Do you want to proceed? [y|n] y

User Responded with: Y

All checks passed.

Please shutdown Oracle instances running out of this ORACLE_HOME on the local system.

(Oracle Home = '/u01/app/oracle/product/19.0.0/dbhome_1')

Is the local system ready for patching? [y|n] y

User Responded with: Y

Backing up files...

Applying interim patch '30805684' to OH '/u01/app/oracle/product/19.0.0/dbhome_1'

Patching component oracle.javavm.server, 19.0.0.0.0...

Patching component oracle.javavm.server.core, 19.0.0.0.0...

Patching component oracle.rdbms.dbscripts, 19.0.0.0.0...

Patching component oracle.rdbms, 19.0.0.0.0...

Patch 30805684 successfully applied.

Log file location: /u01/app/oracle/product/19.0.0/dbhome_1/cfgtoollogs/opatch/opatch2020-10-19_16-33-39PM_1.log

OPatch succeeded.

Note:- Make sure there are no binaries used during applying the patch.

Keep sharing.

Oracle Mean company, not database :)

7.3.23

How to select the next value from the sequence?

2.3.23

Which two actions would reduce the job's elapsed time?

Correct Answer: BC

16.1.22

How many sessions are allowed on the Oracle database?

1.1.22

ORA-00240 control file enqueue held for more than XXX seconds

Bug 20639511 ORA-00240: CONTROL FILE ENQUEUE HELD FOR MORE THAN 120 SECONDS

Description

Flex: ORA-00240: Control File Enqueue Held for More Than 120 Seconds (Doc ID 2434297.1)

14.9.21

How to Design a Good Oracle RAC Set-up on a QA Server for Developers?

5.8.21

What is Oracle RAC

source:- oracle5 Key Points to remember for Oracle RAC:-

WARNING: COULD NOT LOWER THE ASYNCH I/O LIMIT TO 160 FOR SQL DIRECT I/O

A little history:-

Below is information shared with the application Team:-

There are two solutions provided by oracle on the support website for this bug:-

21.7.21

How to Stop and Disable Firewall on CentOS 8

NAME

DESCRIPTION

Steps to top and Disable Firewall on CentOS 8:-

13.7.21

How to Change the Timezone in Linux

Easy steps to change timezone on "CentOS Stream release 8"

30.11.20

ORA-00907: missing right parenthesis

26.11.20

2.11.20

Below is the error:-

Solution:-

If it helps. Please comment and let us know :)

source:- oracle

5 Key Points to remember for Oracle RAC:-