AWS / Azure / GCP Cloud | Database | Data Analytics | LakeHouse | Machine Learning | Shrenik Parekh

Oracle error 1013 during filling up usn avail cache: One of the Oracle RAC database environment, I used support for Oracle 11gR2 was facing performance issue. User were not able to insert record in a timely manner. Update was also very slow across entire RAC environment. Customer experience was not satisfactory and system wide performance was impacted. Time to generate AWR, ASH for the Oracle RAC environment. Fun was started, after I looked at the top timed events in AWR, I was able to see the wait class for concurrency , cluster and other, such as Concurrency - row cache lock , Cluster - gc buffer busy acquire , Cluster - gc buffer busy acquire, Other - enq: US - contention , Cluster - gc current block busy, Other - enq: TA - contention . Top timed Foreground Events was also reflecting same wait event in the same class. Top Timed Background Event was System I/O, control file sequential read. SysStat and Global messaging – RAC was reflecting high GCS received on one of the nod...

What is foregraound and backgroup wait events?: Its *who* is doing the waiting that determines foreground or background. *You* run a query that does a full scan... its a *foreground* db file scattered read. *SMON* runs a query that a full scan... its a *background* db file scattered read, then there *might* be some interaction between the two, for example: *You* do a commit, its foreground wait for 'log file sync'. You've asked LGWR to write some data, so you have a foreground wait but you are waiting on a background process to complete. LGWR dispatches the I/O off to the OS and it is now waiting for the I/O to complete. So it has a background wait of "log file parallel write". You could argue that *you* are also effectively waiting on that as well, but we don't make that interpretation because it wasn't you that issued the I/O. Eventually the IO completes and LGWR is done, so its no longer waiting. Shortly thereafter, LGWR is going to let you know that t...

Use case for Oracle Goldengate to replication source: Logical replication from source to target, means SQL statement executed to propagate changes to target. Source and target database is heterogeneous, i.e. Oracle to SQL server , Oracle to MySQL. Source and target database is homogeneous i.e. Oracle to Oracle, SQL server to SQL server. Source and target database object structure is different . Rollback source transaction using Goldengate utility reverse. One column on source is mapped to different column on target. Prevent deletion of rows on target i.e. if rows are deleted from source and should not be deleted on target using Oracle Goldengate  Event Market Interface. Only part of the row need to replicated from source to target. Filtered data is replicated from source to target using SQLEXE function. Prevent modification of certain data on target when source data is modified, and log data modification using Oracle Goldengate Event Market Interface. ...

Transaction Isolation Levels: Isolation level can be change for a single session or for all subsequent connections with the SET TRANSACTION statement. To set the server's default isolation level for all connections, use the --transaction-isolation option on the command line or in an option file. Each isolation level has different locking strategies. READ UNCOMMITTED : SELECT statements are performed in a nonlocking fashion, but a possible earlier version of a row might be used. Thus, using this isolation level, such reads are not consistent. This is also called a dirty read. READ COMMITT ED : First UPDATE acquires an x-lock on each row that it reads and releases those for rows that it does not modify. Each consistent read, even within the same transaction, sets and reads its own fresh snapshot. If you use READ COMMITTED , you must use row-based binary logging . REPEATABLE READ : Default for InnoDB - the first UPDATE acquires an x-lock on each row that it reads and do...

Assistant: Download Reference for Oracle Database/GI PSU, SPU(CPU), Bundle Patches, Patchsets and Base Releases (Doc ID 2118136.2) It will ask for following What would you like to download? Oracle Database Base Releases Oracle Database Patchsets Oracle Database Release Updates (RU) and Release Update Revisions (RUR) (Versions 12.2 & higher) Oracle Database PSU, SPU(CPU), Bundle Patches (Versions 12.1 & lower) OJVM RU/PSU/Bundle Patches Latest Available Microsoft Windows Patches Please select the version: Here you will select version And you will be able to see Description, PSU, SPU (CPU), GI PSU, and Bundle Patch (Windows 32 and 64 bit)

Innodb Settings: innodb_buffer_pool_size : The size in bytes of the buffer pool , the memory area where InnoDB caches table and index data. innodb_buffer_pool_instances : The number of regions that the InnoDB buffer pool is divided into . For systems with buffer pools in the multi-gigabyte range, dividing the buffer pool into separate instances can improve concurrency , by reducing contention as different threads read and write to cached pages. Increase up to 16 if you have large number of CPU cores. innodb_sort_buffer_size : Specifies the size of sort buffers in BYTES , used to sort data during creation of an InnoDB index . Default is 1048576. innodb_flush_method : O_DIRECT - uses O_DIRECT (or directio() on Solaris) to open the data files , and uses fsync() to flush both the data and log files . Bypass OS cache . Good choice for most IO subsystems, default - NULL innodb_flush_log_at_trx_commit : With this value, the contents of the InnoDB log...

Select File System for maintenance: As pre-12.2 Oracle E-Business Suite releases has one file system, there was no choice about where maintenance activities had to be carried out from. Use of a dual file system in Release 12.2 has required the introduction of a Configuration Change Detector, which automatically detects when changes are made to one file system and replicates them to the other. Patching cycle is in progress: During this time maintenance tasks should be performed on the patch file system . If for some reason you need to perform such tasks on the run file system, you must either complete or abort the patching cycle first. No patching cycle is in progress: Maintenance tasks should be performed on run file system , then clone the run file system to the patch file system using the adop fs_clone command OR adop will run this command automatically in the prepare phase of the next patching cycle, which could increase time for patching cycle. Generating Files: File Ty...