GoldenGate 12c: Initial Configuration

In our last post we have seen “GoldenGate 12c: Installation for Oracle DB” on Linux platform. Now we will do initial configuration before configuring extract and replicat on source and target databases respectively. Here are the steps:

Create tablespace for GGS operations (on both Source & Target DB)

SQL> create tablespace GGS_DATA;

Create User for extract on source database (on both Source & Target DB)

SQL> create user GGSUSER identified by GGSUSER 
     default tablespace GGS_DATA 
     temporary tablespace TEMP;

Grant required permissions (on both Source & Target DB)

SQL> grant connect, resource to ggsuser;

SQL> grant select any dictionary, select any table to ggsuser;

SQL> grant create table to ggsuser;

SQL> grant flashback any table to ggsuser;

SQL> grant execute on dbms_flashback to ggsuser;

SQL> grant execute on utl_file to ggsuser;

SQL> grant create any table to ggsuser;

SQL> grant insert any table to ggsuser;

SQL> grant update any table to ggsuser;

SQL> grant delete any table to ggsuser;

SQL> grant drop any table to ggsuser;

SQL> grant ALTER ANY TABLE to ggsuser;

SQL> grant ALTER SYSTEM to ggsuser;

SQL> grant LOCK ANY TABLE to ggsuser;
     /*Only for initial load.*/

SQL> grant SELECT ANY TRANSACTION to ggsuser; 
     /*To extract changes from redo stored on ASM for classic extract.*/

Read more of this post


Securing Oracle Listeners against TNS Poison Attack by COST

The Oracle database component contains a vulnerability in the TNS listener service that has been referred to as (TNS Poison) in public discussions. The TNS listener service accepts unauthenticated remote registrations with the appropriate connect packet (COMMAND=SERVICE_REGISTER_NSGR). An unauthenticated attacker may be able to register a client using an already registered database’s instance name to perform a man-in-the-middle attack that allows the attack to sniff database traffic and inject database commands to the server. Joxean Koret was the first one to identify and provided code to produce man in middle attack by hacking the communication between client and DB server in 2008.

Oracle came with solution for this risk in 2012 April, as fix in code as well as securing listeners by COST (Class Of Secure Transports) and VNCR (Valid Node Checking for Registration). COST is supported solution for 10g to 11gR2 while from onwards VNCR is alternate to COST. Cost is little bit complex setup using subset of Oracle’s advanced security which was allowed to use without license only to securing listeners. VNCR is easy to configure compared to COST.

The class of secure transports (COST) parameters specifies a list of transports that are considered secure for administration and registration of a particular listener. The COST parameters identify which transports are considered secure for that installation and whether the administration of a listener requires secure transports. COST will not affect client connections utilizing other protocols.

IPC protocol support is similar to BEQ protocol support in that it can only be used when the client program and the Oracle server are installed on the same system. IPC protocol support differs from BEQ protocol support in that it can be used with Oracle Shared Server configurations. IPC protocol support requires a listener for its operation.

Here are steps to configure COST in 11g database.

Step -1 Create wallet directories on each node (as root)

mkdir -p /etc/oracle/wallets/COST

chown -R oracle:dba /etc/oracle/wallets

Read more of this post

GoldenGate 12c: Installation for Oracle DB

Oracle Goldengate is undoubtedly one of best replication solution for oracle to oracle as other supported databases with ease to configuration and manageability. Today we will explore installation of Oracle Golden Gate 12c for Oracle 11g database from OUI. We need to install software on both source and target servers.

Environment details

OEL 5.x x86-64 for source and target machines

Oracle databases for source and target

GolgenGate binary

Software Download

  1. Go to Url
  2. login using your id and accept license agreements
  3. Select a Product Pack – Oracle Fusion Middleware
  4. Platform – Linux x86-64
  5. Click on GO
  6. Choose “Oracle GoldenGate on Oracle v12.1.2.1 Media Pack for Linux x86-64
  7. Click Continue
  8. Click on Download

Installation user for installation

Read more of this post

Block Sequence Number & SCN

In addition to SCNs, Block Sequence Numbers which are used to distinguish updates within a block and same SCN. When a block is updated, Oracle records the current SCN in the block. In addition, Oracle also records a sequence number for the change i.e. an SCN+Sequence number identifies a change within a block. The basic idea is to store the current redo generating SCN in the cache header when making a change to a block. Since multiple changes to a block can be made at the same SCN, a sequence number is also stored in the cache header to differentiate between different changes at the same SCN. The sequence number is increased each time a change is made at the same SCN. The sequence number is reset to 1 when making a change at a higher SCN than the SCN currently in the block. This allows multiple changes to the same block to have the same SCN (though they will have different sequence numbers). An SCN is stored in the Data Block, signifying the SCN at which the latest (most recent) change to the block took place.


Let’s explore the SCN and sequence at the block level. We have one table TEST with two columns Name varchar2(40) and CITY varchar2(20).

SQL> create table TEST (NAME Varchar2(20) , SAL number);
Table created.

SQL> insert into Test values ('AMIT',100);
1 row created.

SQL> commit;
Commit complete.


FNO               BLK                 NAME        SAL
---------- ---------- -------------------- ----------
1               86505                 AMIT        100

So we identified that table containing record where NAME=’AMIT’ in block number 86505 of file number (absolute) 1. Now we will try to perform some transactions followed by block dump to see the change in SCN and sequence number pair at block level.
Read more of this post

Read Consistency & SCN

Read consistency is one of the prime conditions for RDBMS, under ACID property “I”, stand for isolation. The isolation property ensures that the concurrent execution of transactions result in a system state that would be obtained if transactions were executed serially, i.e. one after the other. Providing isolation is the main goal of concurrency control. Depending on concurrency control method, the effects of an incomplete transaction might not even be visible to another transaction.

Managing Read Consistency

Oracle Database automatically provides read consistency to a query so that all the data that the query sees comes from a single point in time (statement-level read consistency). Oracle Database can also provide read consistency to all of the queries in a transaction (transaction-level read consistency). Oracle Database uses the information maintained in its rollback segments to provide these consistent views. The rollback segments contain the old values of data that have been changed by uncommitted or recently committed transactions.

As a query enters the execution stage, the current system change number (SCN) is determined. As data blocks are read on behalf of the query, only blocks written with the observed SCN are used. Blocks with changed data (more recent SCNs) are reconstructed from data in the rollback segments, and the reconstructed data is returned for the query. Therefore, each query returns all committed data with respect to the SCN recorded at the time that query execution began. Changes of other transactions that occur during a query’s execution are not observed, guaranteeing that consistent data is returned for each query.
Read more of this post

SCN on Scale of Time & SCN of a ROW

In the last post we have discussed about SCN structure and generation schemes, now we will see relation of SCN with timestamp and association with data populated in database tables.

SCN to TIMESTAMP: SCN is actually generated based on timestamp as well. So it’s easy to check timestamp from SCN and vice versa. Handy conversion features are available only from Oracle 10g onwards, but still from older versions Oracle had system object SYS.SMON_SCN_TIME, which can provide you the same information after some simple calculations.

In 10g onwards, there are two built-in functions to give the timestamp and SCN mapping details timestamp_to_scn() & scn_to_timestamp()

SQL> select scn_to_timestamp(current_scn-rownum) tm,
timestamp_to_scn(scn_to_timestamp(current_scn-rownum)) scn, current_scn-rownum
from v$database
connect by level <=5;

TM                                        SCN         CURRENT_SCN-ROWNUM
----------------------------------- ----------------- ------------------
14-AUG-12 AM               1274785            1274785
14-AUG-12 AM               1274784            1274784
14-AUG-12 AM               1274782            1274783
14-AUG-12 AM               1274782            1274782
14-AUG-12 AM               1274781            1274781

Oracle Documentation is saying that both functions actually trying to give approximate SCN or timestamp value. Here are the definitions:

TIMESTAMP_TO_SCN: takes as an argument a timestamp value and returns the approximate system change number (SCN) associated with that timestamp. The returned value is of datatype NUMBER. This function is useful any time you want to know the SCN associated with a particular timestamp.
Read more of this post

SCN Structure & Generation Schemes

We have discussed about SCN in our last post. Let’s explore about structure of SCN as well as generation/propagation schemes.

SCN Structure

SCN is a 6 Byte (48 bit) number with max value is 281,474,976,710,656 (281 Trillion) and represented as 2 parts – SCN_BASE and SCN_WRAP. An SCN_BASE is a 4 Byte (32 bit) number and SCN_WRAP is a 2 Byte (16 bit) number. Whenever SCN_BASE reaches its maximum (2 power 32 = 4294967296), SCN_WRAP is increased by one and SCN_BASE will be reset to 0. This continues until SCN_WRAP reaches its maximum, i.e. 2 power 16 = 65536.

SCN = (SCN_WRAP * 2^32) + SCN_BASE

SQL> select max(scn_wrp),max(SCN_bas) from smon_scn_time group by scn_wrp;


------------ ------------

          0       2227514


Even if the SCN value does reach its maximum, then SCN will be reset to 0, thus causing a new incarnated database. So, all your old backups and archived logs become useless and you need to take fresh backups. From 12c onwards Oracle might use 8 Byte SCN format.

Current SCN can be obtained by either of the following queries:

  • select dbms_flashback.get_system_change_number scn from dual;
  • select current_scn from v$database;
  • select scn_wrp*power(2,32) +SCN_bas SCN from smon_scn_time where scn_bas=( select max(scn_bas) SCN_BASE from sys.smon_scn_time); /* This query will show delayed output so only used for older releases where aforesaid methods will not work */

Read more of this post

Role of SCN in Instance Recovery & Media Recovery

In last post we have discussed the different types of checkpoints and association of SCN. Also we have examined the status of datafile headers wrt End (checkpoint) SCN after a clean shutdown. In this post we are examining role of SCN in instance recovery and media recovery.

SCN after an Instance Crash

In this session we are going to demonstrate the behavior of the checkpoints after an instance crash, the following SQL creates a table (which performs an implicit commit) and inserts a row of data into it without a commit:

SQL> create table dummytable (x number) tablespace test;
Table created.

SQL> insert into dummytable values(100);
1 row created.

During subsequent startup after instance crash Oracle will realize before opening database that value for Last_Change# column for datafiles are not showing SCN value , which means END Checkpoint SCN was not done, which would be indication for Oracle that instance recovery is required.

Let’s mimic instance failure and then startup mount to examine the situation

Read more of this post

Checkpoint & SCN

Checkpoint is a data structure that indicates the “checkpoint position“, determined by the oldest dirty buffer in the database buffer cache. In terms of Oracle’s clock this position is actually the SCN in the redo stream where instance recovery must begin. The checkpoint position acts as a pointer to the redo stream and is stored in the control file and in each data file header. Whenever we say checkpoint happened we mean that The writing of modified database buffers in the database buffer cache to disk. A successful checkpoint guarantees that all database changes up to the checkpoint SCN have been recorded in the datafiles and SCNs recorded in the file headers guarantee that all changes made to database blocks prior to that SCN are already written to disk. As a result, only those changes made after the checkpoint need to be applied during recovery.

Checkpoints triggered on following conditions:

  • Every 3 seconds (Incremental Checkpoint)
  • When Logswitch happened
  • When instance shutdown normal/transactional/immediate
  • Whenever Alter Tablespace [Offline Normal| Read Only| Begin Backup]
  • Controlled by internal checkpoint forced by recovery related parameters i.e. Fast_Start_MTTR_Target etc.

Purpose of Checkpoints

Oracle Database uses checkpoints to achieve the following goals:

  • Reduce the time required for recovery in case of an instance or media failure
  • Ensure that dirty buffers in the buffer cache are written to disk regularly
  • Ensure that all committed data is written to disk during a consistent shutdown

When Oracle Database Initiates Checkpoints

The checkpoint process (CKPT) is responsible for writing checkpoints to the data file headers and control file. Implementing full checkpoint every time would be a costly operation and a major bottleneck for concurrency, so Oracle using different types of checkpoints based on different purposes:
Read more of this post

System Change Number (SCN)

From day one of my DBA career I used to listen SCN or System Change Number or System Commit Number so many times yet I haven’t found any reliable source to find details of SCN for RDBMS concept point of view. So I am thinking to share some information about SCN which I got from different sources and am convinced too.

What is SCN?

The system change number (SCN) is a logical, internal timestamp used by the Oracle Database. It’s basically to make sure that database system will align to its primary goal to align with ACID property. When users generate updates, the RDBMS records the SCN (“time”) at which the update took place. The SCN is a monotonically increasing sequence. It gets bumped up by some events, but several events may share the same SCN, which is another way of saying that they “happened at the same time” with respect to the RDBMS. The database uses SCNs to query and track changes. For example, if a transaction updates a row, then the database records the SCN at which this update occurred. Other modifications in this transaction typically have the same SCN. When a transaction commits, the database records an SCN for this commit. Multiple transactions that commit at the same time may share the same SCN.

In single-instance Oracle, the System Global Area maintains and increments SCNs from an instance that has mounted the database in exclusive mode. In RAC the SCN maintained globally. Its implementation may vary from platform to platform. The SCN may be handled by the Distributed Lock Manager, by the Lamport SCN scheme, or by using a hardware clock or dedicated SCN server. We will discuss Lamport SCN Generation in coming discussions.
Read more of this post

Frits Hoogland Weblog

IT Technology; Oracle, linux, TCP/IP and other stuff I find interesting

Pierre blog

Pierre Forstmann Oracle Database blog


Oracle databases, storage and the high-performance world of flash memory

Future Veterans

Ramblings about Oracle

Ranjeet Srivastava

Smile! You’re at the best blog ever

Kevin Closson's Blog: Platforms, Databases and Storage

Platform, Database and Storage Topics

Real Life Database / SQL Experiences : An Oracle Blog from Vivek Sharma

Being an Oracle Professional, I like to share all my Real Life Performance Tuning Challenges and Experiences. The Content and Views on this site are my own and not necessarily those of Oracle. While, I write on my real life experiences, the resolutions mentioned are solely mine. Comments / Criticisms are always a welcome.


Dominic Brooks on Oracle Performance, Tuning, Data Quality & Sensible Design ... (Now with added Sets Appeal)

ASM Support Guy

Just Another Crazy Oracle DBA

Exadata Certification

Just Another Crazy Oracle DBA

Carlos Sierra's Tools and Tips

Tools and Tips for Oracle Performance and SQL Tuning

Sangram keshari's Oracle Blog

The Fusion Middleware Administration & Database Administration Blog

Amit Saraswat

Just Another Crazy Oracle DBA

Oracle Scratchpad

Just another Oracle weblog

The Tom Kyte Blog

Just Another Crazy Oracle DBA

Hemant's Oracle DBA Blog

Just Another Crazy Oracle DBA

Uwe Hesse

about Database Technology

Richard Foote's Oracle Blog

Focusing Specifically On Oracle Indexes, Database Administration and Some Great Music