Posts

ORA-00600 [qeselupdpre_20]

, , , ,

This error is triggered when trying to update a column in a table that is part of a correlated subquery involving the same table. This issue was encountered in Oracle databases of versions equal to or greater than 12.1 but below 12.2, with 12.1.0.2 being the confirmed affected version.

Description:
The error was caused by a regression that prevented the correct marking of the compare column in cases where there is a correlation column from the same table but a different view. This regression led to the ORA-00600 [qeselupdpre_20] error when executing certain update statements within the specified version range of Oracle databases.

Version Affected:
The problem affects Oracle database versions from 12.1 to 12.2, with the specific version 12.1.0.2 (Server Patch Set) confirmed to experience this issue. It’s crucial to note that this problem does not extend to versions 12.2 and above.

Workaround:
Unfortunately, there is no available workaround for the ORA-00600 [qeselupdpre_20] error in the affected versions of the Oracle database. Users encountering this issue are advised to proceed directly to the fix provided by Oracle.

Fixed:
The fix for the bug causing the ORA-00600 [qeselupdpre_20] error is first included in Oracle database version 12.2.0.1 (Base Release). Users experiencing this problem should consider upgrading their Oracle database to version 12.2.0.1 or later to resolve this issue.

/0 Comments/by
, , , , , , ,

If you require a certain number of backups to be retained, you can set the retention policy based on the redundancy option. This option requires that a specified number of backups be cataloged before any backup is identified as obsolete. The default retention policy has a redundancy of 1, which means that only one backup of a file must exist at any given time. A backup is deemed obsolete when a more recent version of the same file has been backed up. For example, you can use the following command to configure a redundancy recovery policy of 2:

CONFIGURE RETENTION POLICY TO REDUNDANCY 2;

/0 Comments/by

Improving instance recovery performance

, , , , ,

You query V$INSTANCE_RECOVERY view and consistently receive an OPTIMAL_LOGFILE_SIZE value that is greater than the size of your smallest online redo log file. The OPTIMAL_LOGFILE_SIZE column of the V$INSTANCE_RECOVERY view can be used to determine the appropriate size for all of the online redo log files in your database. If the value of the OPTIMAL_LOGFILE_SIZE column is greater than the size of your smallest online redo log file, you should change the size of all online redo log files to be at least this value. In addition, the FAST_START_MTTR_TARGET initialization parameter simplifies the configuration of recovery time from instance or system failure. After adjusting the size of your online redo log files, you may be able to adjust the value of this initialization parameter for better performance. This is done by rerunning the MTTR advisor after changing the size of your online redo log file to achieve more optimal results. However, running the MTTR advisor is not the best option in this situation for improving instance recovery performance.

 

/0 Comments/by

Multi-process Multi-thread

, ,

Starting in Oracle Database 12c, the multithreaded Oracle model enables specific Oracle processes to execute as operating system threads in separate address spaces. Setting up an Oracle database instance for using multi-process multi-thread architecture is done by starting up the Oracle database instance with the THREADED_EXECUTION initialization parameter set to TRUE.

The multi-process multi-thread model requires a password file for SYSDBA authentication while starting the database instance. Without using the SYS password

an  ORA-1031: insufficient privileges error will be triggered on startup.

When the THREADED_EXECUTION parameter is set to TRUE, you must set the DEDICATED_THROUGH_BROKER_LISTENER parameter to ON in your listener.ora file.

When that parameter is set, the listener knows that it should not spawn an OS process when a connect request is received; instead, it passes the request to the database so that a database thread is spawned and answers the connection.

 

 

 

/0 Comments/by

My lectures in 2021

, , , , ,
Oracle Groundbreakers EMEA 2021
Michigan Oracle Users Summit 2021 –
/by

OML4R

, ,

Oracle Machine Learning for R (OML4R) is an R API that makes the open-source R statistical programming language and environment ready for enterprise and big data. Designed for significant data problems, OML4R integrates R with Oracle Database.
R users can run R commands and scripts for statistical and graphical analyses on data stored in the Oracle Database. In addition, they can develop, refine and deploy R scripts that leverage the parallelism and scalability of the database to automate data analysis.

For more information, see: https://docs.oracle.com/en/database/oracle/machine-learning/oml4r/1.5.1/tasks.html

/0 Comments/by

Automated Generation of Materialized Views

, , ,

The paper below describes an automated system that generates, selects, verifies, and maintains materialized views in the Oracle RDBMS; it presents a novel technique, called the extended covering subexpression algorithm, for the automated generation of materialized views. An extensive set of experiments is described that demonstrates the feasibility and efficiency of this approach. This system has been fully implemented and will be deployed on the Oracle Autonomous Database on the Cloud.

For more information see https://dl.acm.org/doi/abs/10.14778/3415478.3415533

/0 Comments/by

V$TRANSACTION

, ,

V$TRANSACTION lists the active transactions in the system.

(i) The following columns together point to a transaction. (i.e.) The combination of the following should give a unique transaction id for that database.

XIDUSN – Undo segment number
XIDSLOT – NUMBER Slot number
XIDSQN – NUMBER Sequence number

(ii) The following columns explain the number of undo blocks / undo records used per transaction.
USED_UBLK – Number of undo blocks used
USED_UREC – Number of undo records used

In the case of transaction rollback, the above columns will estimate the number of undo blocks that need to be rolled back.

The number of undo records and undo blocks (USED_UREC and USED_UBLK) decreases while the transaction rolls back. When they reach 0, the transaction disappears from v$transaction.

The following query can be used to monitor the transaction rollback.
SELECT A.SID, A.USERNAME, B.XIDUSN, B.USED_UREC, B.USED_UBLK
FROM V$SESSION A, V$TRANSACTION B
WHERE A.SADDR=B.SES_ADDR;

(iii) The STATUS following column explains the status of a transaction.

ACTIVE – Explains the transaction is active.

Before performing a normal/transactional shutdown, YOU can check this view to understand any ACTIVE transactions.
SELECT XIDUSN, XIDSLT, XIDSEQ , SES_ADDR, STATUS FROM V$TRANSACTION;

 

/0 Comments/by

Will objects be imported with the INMEMORY attribute?

, ,

You can import database objects that are enabled for the IM column store using the TRANSFORM=INMEMORY:y option of the ‘impdp’ command. With this option, Data Pump keeps the IM column store clause for all objects that have one. When the TRANSFORM=INMEMORY:n option is specified, Data Pump drops the IM column store clause from all objects that have one.

You can also use the TRANSFORM=INMEMORY_CLAUSE:string option to override the IM column store clause for a database object in the dump file during import. For example, you can use this option to change the IM column store compression for a database object being imported.

Example:

transform=inmemory_clause:\”INMEMORY MEMCOMPRESS FOR DML PRIORITY CRITICAL\”

Note the \ characters are necessary to “escape” the double quotes so they are not stripped by the OS.

 

Reference:

Oracle Database In-Memory Option (DBIM) Basics and Interaction with Data Warehousing Features (Doc ID 1903683.1)

/0 Comments/by

Oracle BUG DB RU 19.12.0.0.210720 or DB RUR 19.11.1.0.210720

, , , ,

After applying DB RU 19.12.0.0.210720 or DB RUR 19.11.1.0.210720, you may notice that the Block Change Tracking (BCT) file is getting created of a size that is not in line with the database size.
Oracle recommends that you apply interim one-off Patch 33185773 to correct this problem(s) in the RU/RURs indicated above.
Note the fix for this issue has been included in the Oct2021 quarterly RU/RURs.

/0 Comments/by