Posts

When patching an Oracle home, you might run into problems. OPatch might not behave as expected. It is helpful to know where to look for error messages or find additional information to pass on to Oracle Support if you want to log an SR for your problem. So OPatch maintains logs for apply, rollback, and lsinventory operations. The OPatch log files are located in the $ORACLE_HOME/cfgtoollogs/opatch. Each time you run OPatch, a new log file is created, and each log file is tagged with the operation’s timestamp. OPatch maintains an index of processed commands and log files in the opatch_history.txt file – and that is also in the above-mentioned $ORACLE_HOME/cfgtoollogs/opatch directory. So if you change the directory to $ORACLE_HOME/cfgtoollogs/opatch, you’ll see that every time you run OPatch, a log file is created with a date stamp. And then, at the bottom, you’ll see an opatch_history.txt file. look at the file, you’ll see a record of each time you ran the opatch apply, opatch rollback, or lsinventory command. The DBMS_QOPATCH package provides a PL/SQL or a SQL interface to view the installed database patches.

The package returns the patch and patch metadata information available as part of the “opatch lsinventory -xml” command in real-time. So it’s basically a way to see which patches are applied to your database home but from a PL/SQL or a SQL interface. So you basically get an XML-formatted return of your patch information. The DBMS_QOPATCH package allows users to query what patches are installed from SQL*Plus, write wrapper programs to create reports and do validation checks across multiple environments, and also to check patches installed on cluster nodes from a single location. If you log into SQL*Plus as the sys user and then perform select DBMS_QOPATCH.GET_OPATCH_LSINVENTORY from dual. And you’ll see just lots of XML information – which you can use an XML parser to make sense of it.

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.

 

Today, 18th January 2022, The Database patch bundles were released.
All the details on MOS in Doc ID 19202201.9 and Doc ID 21202201.9 are recommended to be installed on production systems.

 

Oracle Groundbreakers EMEA 2021 
Michigan Oracle Users Summit 2021 

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;