seeing alert log from sql prompt

the following command can be used to see the alert log from sql prompt.

SQL> select message_text from X$DBGALERTEXT where rownum <= 50;

Recovery of Online Redo Log: Thread 1 Group 6 Seq 53021 Reading mem 0
Mem# 0: /n01/oradata1/prp15/prp15z1_sb_redo6_01.log
Standby controlfile consistent with primary
RFS[16]: Selected log 15 for thread 2 sequence 53013 dbid -1291669901 branch 803

Archived Log entry 106105 added for thread 2 sequence 53012 ID 0xb30fface dest 3

Media Recovery Waiting for thread 2 sequence 53013 (in transit)
Recovery of Online Redo Log: Thread 2 Group 15 Seq 53013 Reading mem 0

Mem# 0: /n01/oradata1/prp15/prp15z2_sb_redo15_01.log
Media Recovery Waiting for thread 1 sequence 53022 (in transit)
Recovery of Online Redo Log: Thread 1 Group 5 Seq 53022 Reading mem 0
Mem# 0: /n01/oradata1/prp15/prp15z1_sb_redo5_01.log
Standby controlfile consistent with primary
RFS[16]: Selected log 16 for thread 2 sequence 53014 dbid -1291669901 branch 803

Archived Log entry 106107 added for thread 2 sequence 53013 ID 0xb30fface dest 3
Media Recovery Waiting for thread 2 sequence 53014 (in transit)
Recovery of Online Redo Log: Thread 2 Group 16 Seq 53014 Reading mem 0
Mem# 0: /n01/oradata1/prp15/prp15z2_sb_redo16_01.log
Media Recovery Waiting for thread 1 sequence 53023 (in transit)
Recovery of Online Redo Log: Thread 1 Group 6 Seq 53023 Reading mem 0
Mem# 0: /n01/oradata1/prp15/prp15z1_sb_redo6_01.log



Query to check nls_characterset parameter of database

sqlplus / as sysdba

SQL*Plus: Release Production on Mon Apr 4 00:39:24 2016

Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release – 64bit Production
With the Partitioning, Real Application Clusters, OLAP, Data Mining
and Real Application Testing options

SQL> SELECT value$ FROM sys.props$ WHERE name = ‘NLS_CHARACTERSET’;




Under Standing The Oracle RowID


Every row in every table has a physical address. The address of a row is determined from a combination of the following:

  • Datafile number
  • Block number
  • Location of the row within the block
  • Object number

You can display the address of a row in a table by querying the ROWID pseudo-column—for example:

SQL> create table TAB1 as select rownum id, rpad(‘x’,100) c1, rpad(‘y’,200) c2 from dba_objects where rownum<10;

Table created.



The ROWID pseudo-column value isn’t physically stored in the database. Oracle calculates its value when

you query it. The ROWID contents are displayed as base-64 values that can contain the characters A–Z, a– z, 0–9, +, and /. You can translate the ROWID value into meaningful information via theDBMS_ROWID package.

For example,to display the file number, block number, and row number in which a row is stored,issue this statement:

SQL> select id,dbms_rowid.rowid_relative_fno(rowid) file_num,dbms_rowid.rowid_block_number(rowid) block_num,dbms_rowid.rowid_row_number(rowid) row_num from tab1;

———- ———- ———- ———-
1     1          101705                             0
2     1          101705                            1
3      1          101705                            2
4      1          101705                              3
5      1           101705                             4
6     1           101705                              5
7     1           101705                             6
8     1           101705                              7
9     1           101705                              8

9 rows selected.

You can use the ROWID value in the SELECT and WHERE clauses of a SQL statement. In most cases, the ROWID uniquely identifies a row. However, it’s possible to have rows in different tables that are stored in the same cluster and so contain rows with the same ROWID.

HugePages vs Automatic Memory Management in Oracle Databases

Why are HugePages incompatible with Automatic Memory Management in Oracle Databases?

Just few words to answer this straightforward question:

You cannot use Automatic Memory Management (AMM) while implementing HugePages, because AMM works on dynamic SGA and PGA tuning. For example AMM can unmap the unused SGA space and map it to PGA and back. With HugePages contiguous pages are preallocated and cannot be used for anything else but for System V shared memory (for example, SGA).

When you use AMM, the entire SGA memory is allocated by creating files under /dev/shm. When Oracle Database allocates SGA with AMM, HugePages are not reserved. To use HugePages you must disable AMM.


“Touch Count” for Buffer Cache

The paper is written by Craig A. Shallahamer ( Thanks to the author for sharing such an interestng information.

Author has explained the algorithm for managing buffers in Oracle buffer cache. Now obviously all the buffers cannot stay in buffer cache because of the limited size of cache. So buffers which are older or non-popular should go out of the buffer cache. The algorithm is all about the criteria for buffer cache to stay or leave buffer cache.

Initially with older release, Oracle started with LRU (Least Recently Used) algorithm. Conceptually LRU list is nothing but a list of pointers to buffer blocks. When a buffer block is touched, the pointer moves towards the most recently end of LRU chain. Remember that its the pointer which moves up the chain, buffer blocks are never moved. The general idea is to keep the more popular blocks in buffer cache and ask less popular block to leave.

But this algorithm was having some issues. For example a full table scan !! This will get all the buffers into the buffer cache making other important buffers to leave the cache. For example if the buffer cache is having 500 blocks and full table scan is getting 600 blocks in buffer cache, all the popular blocks will go away.

To overcome this problem, Oracle came with a modified LRU algorithem. This modified LRU algorithm takes care of full table scan by keep the buffers of full table scan at the LRU end of LRU chain and also it will allow only limited number of blocks to be put in LRU end of LRU chain. This will avoid flooding buffer cache with huge number of buffers from full table scan. Also along with this algorithm, Oracle implemented multiple buffer pools – KEEP BUFFER POOL, RECYCLE BUFFER POOL and DEFAULT BUFFER POOL. As you must be knowing, the data which is used frequently and should remain in buffer cache should be placed in KEEP BUFFER POOL, buffer that should not stay for longer in buffer cache should be placed in RECYCLE BUFFER POOL and if we dont specifically mention any buffer pool duing table creation, it will go in DEFAULT POOL.

With this background lets move towards understanding the touch count algorithm.

Buffer cache Management

Before understanding the complex logic, we should understand the needs for touch count algorithm. Why does Oracle needs to have such a complex alogrithm in place? We have already seen that modified LRU algorithm takes care of full table scan. But if you think again we have another issue – Large index range scan? Imaging large number of index leaf blocks flowing into the buffer cache. This issue wont be addressed by modified LRU.

Also with growing sizes for buffer cache, better and better performance requirements and more control Oracle introduced touch count algorithm.

For smooth and effective buffer cache operation, a very fast and very flexible algorithm must be implemented that essentially forces every buffer to seriously earn the right to remain in buffer cache. Touch count algorithm makes it very difficult for buffer to simply remain in buffer cache. It’s almost cruel how many hurdles a buffer must continually jump to simply remain in buffer cache.

At the core of touch count algorithm are the harsh requirements placed on each buffer to not only remain in cache, but to remain in MRU end of LRU list.Conceptually touch count algorithm does this by assigning each buffer a counter. Each time a block is touched its counter is incremented. The value of this counter is used to assign popularity to the blocks. But its not staright forward. Keep reading !!

Touch Count Algotrithm

Oracle does lot of nasty things to make it very difficult for a buffer to remain in buffer cache. Lets understand each concepts

Mid-point Insertion

Each LRU is divided into two basic areas or region; a hot region and a cold region. All buffers in hot region is called hot buffers and all buffers in cold region is called cold buffers.There is a midpoint marker between hot region and cold region. This mid point marker moves in order to make sure that correct number of buffers are present in each regions. This mid pointer is not associated with any buffer.

By default Oracle divides LRU equally between hot and cold region. That means, 50% of the buffers will be in hot region and 50% will be in cold region. However we can change the default setting by changing a hidden parameter _db_percent_hot_default. If we increase this parameter then buffers in hot region will increase, that is buffers above mid point wil increase.

When a server process reads a block from disk into the buffer cache, it is placed in the middle of LRU chain, that is, between hot region and cold region. This is called mid-point insertion and is the fundamental concept in touch count algorithm. These buffer has to earn there way into hot region by increasing the touch count. Frequent access to these block will increase there touch count and block will move in the hot region.

Touch Count Increment

Now we know that when a server process reads a new block from disk into the buffer cache, where exactly its going to put the block. Lets now consider how the tocuh count increases for blocks.

Therotically when ever a buffer is touched, its touch count should increase. but practically Oracle does not let that happen. Think of a situation where some processes needs some blocks in buffer very frequently for some period, and after that period that block is not so required. In that case Oracle process might access the block so frequently that within a second its touch count will grow huge and it will become eligible to be placed in hot region. Not only that, it might become eligible to stay in hot region. Since this block wont be used later, we dont want this block to stay just because it was used for initial period of time.

To overcome this problem, Oracle only allows buffer’s touch count to be incremented, at most, once every 3 seconds. Again, thi 3 scond is default timing by Oracle, which can be changed using hidden parameter _db_aging_touch_time.

When a touch count is incremented buffer pointer should move. But movement of buffer pointer is independent of touch count increment. Also for any activity in memory area oracle needs a latch for assuring cache consistency. But there is an exception here !! For updating touch count, Oracle does not use latch and buffer block can be modified while touch count is getting incremented. But more interesting is that, two processes may increment the touch count to same value, and when this happens Oracle assures the worst that could happen is the touch count is not actually incremented every time a buffer is touched and that no cache corruption will result.

Buffer Movement

As mentioned previously, when a buffer is brought into the buffer cache its placed in the middle of hot region and cold region. Unlike LRU algorithm, touch count algorithm will not move the block to hot region just because its touched. Yes, its touch count will probably be incremented.

When a server process if looking for the free buffer to place the content of datafile block into buffer cache or when the DBWR is looking for dirty buffer to write to disk, and if the buffer’s touch count is observed to be greater then 2, its moved to MRU end of LRU list. This default threshold of block movement is controlled by hidden parameter _db_aging_hot_criteria.

Oracle touch count implementation is tough on buffers !! When buffer is moved to MRU end of LRU list, its touch count is set to zero. So this buffer which is newly brought into the hot region has hit touch count reset to 0 immediately. For this block to remain in the hot region its touch count should be incremented significantly. So if this block is really accessed frequenctly, its touch count will increase automatically and it will servive in hot region.

Hot and Cold Movement

If a buffer is moved from cold region to hot region, the mid point marker has to shift to accomodate correct number of blocks in hot a cold region. So 1 block from hot region will be forced into the cold region which is least frequently used and which belongs to LRU end of LRU list. When this happens the touch count of that block is reset to 1. Even if the buffer’s touch count is 250, after moving to cold region its touch count is reset to 1. This threshold crossing touch count reset value is controlled by the instance parameter _db_aging_cool_count. This means the buffer must be all touched again to make its move to the hot region. This is how the blocks are moved and managed in buffer cache.

For more information on performance tuning buffer cache using the above parameters check the reference section below.


ALL ABOUT ORACLE’S TOUCH COUNT ALGORITHM – Criag A. Shallahamer (Version 4a, January 5, 2004)

ARCHIVE_LAG_TARGET in data gurard


Parameter type Integer
Default value 0 (disabled)
Range of values 0 or any integer in [60, 7200]
Basic No
Real Application Clusters Multiple instances should use the same value.

Its a common requirement (or service level agreement) that a standby database be kept less than ‘n’ minutes behind its associated primary. More accurately, the requirement is normally a specification of the amount of the data that is allowed to be lost in the event of a disaster on the primary.

Up until now, this normally meant careful consideration of redo log sizes, or some scheduled task to archive the current log file at regular intervals. 9i introduces a new parameter to achieve this for us:

From the documentation:

Setting the ARCHIVE_LAG_TARGET Initialization Parameter When you set the ARCHIVE_LAG_TARGET initialization parameter, you cause Oracle to examine an instance’s current online redo log periodically. If the following conditions are met the instance will switch the log:

The current log was created prior to n seconds ago, and the estimated archival time for the current log is m seconds (proportional to the number of redo blocks used in the current log), where n + m exceeds the value of the ARCHIVE_LAG_TARGET initialization parameter.
The current log contains redo records.
In an Oracle Real Application Clusters environment, the instance also kicks other threads into switching and archiving logs if they are falling behind. This can be particularly useful when one instance in the cluster is more idle than the other instances (as when you are running a 2-node primary/secondary configuration of Oracle Real Application Clusters).

Initialization parameter ARCHIVE_LAG_TARGET specifies the target of how many seconds of redo the standby could lose in the event of a primary shutdown or crash. It also provides an upper limit of how long (in the number of seconds) the current log of the primary database can span. Because the estimated archival time is also considered, this is not the exact log switch time.

The following initialization parameter setting sets the log switch interval to 30 minutes (a typical value).



once you issued the DROP PLUGGABLE DATABASE command you can’t reuse a previously taken backup of this particular PDB anymore and recover the PDB into this existing CDB. Actually only the meta information in the controlfile or the RMAN catalog will be deleted. But archive logs and backup still persist.

This is the sample error message you’ll see when you try to recover a dropped pluggable database:

RMAN> restore pluggable database pdb2drop;

Starting restore at 01-JUN-15
using channel ORA_DISK_1
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of restore command at 06/01/2015 10:10:40
RMAN-06813: could not translate pluggable database pdb2drop

A more convenient way to restore a PDB once the magic command (DROP PLUGGABLE DATABASE) has been issued is  recovering it into an auxiliary container database and unplug/plug it.

MOS Note  explains how to workaround this issue:

MOS Note: 2034953.1
How to Restore Dropped PDB in Multitenant

In brief this MOS Note describes how to:

  • Create an auxiliary container database
  • Recover the backup (yes, you will have to have a backup of your container database) including this particular PDB
  • Unplug the PDB after recovery has been finished and plug it back into the original CDB