Confusion about Oracle Patches?

Whenever a bug is being detected and confirmed for fix, Oracle engineers raise a request for bug engineers / development to provide a patch for customer. There are different types of patch requests in order to fix the issue while making sure not to disturb already existing patch in the target environment. Few patches/patchsets are proactively recommended for security, stability, performance and environmental changes (i.e. DST) perspective.

Whenever someone asks me regarding patching I used to confuse a lot and read oracle documentation every time. When the question came again the same level of confusion appear pushing me into loop of reading and forgetting the concepts of patch terminologies. So this time I have decided to share my understanding so it would be easy to recall and enhance based on feedbacks and queries.

When a bug is detected in UNIX, the patch fix for this bug is given via object files. These object files have an extension of “.o” . These “.o” files are stored in an “archive library”. The patch contains Static Libraries and Shared Libraries. The Executables are generated using static libraries and linked using shared libraries. These Executables are generated on the fly in oracle binary, where the patch is being applied.

Whereas, in Windows there is no concept of “.o” files at all. There are only DLL’s (Dynamic Linked Libraries) and Executables in the library. The difference is that these Executables are generated at the patch shipment site, and not in the machine where patching to be done. So when customer applies a patch fix, the Oracle Executables those already exist in the target Oracle Home will be replaced with Oracle Executables from the patch. This action results in the older patch fix to be lost. This nature of patch applying in Windows makes it impossible to create individual patches for bug fixes. Instead, many patches are clubbed together and a “bundle patch” is released. All the Bundle patches in Windows are cumulative in a particular release. It means that fixes from previous Oracle security alerts and bundle patches are included.

Before jumping to types of patches/patchsets, let’s understand few terminologies related to patch.

Label – For internal references Oracle developers use a name or identifier for a set of changes (e.g. the fixing of a bug) in the source code; the output of a label is a set of C files (*.c, *.h etc)

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.*/

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 11.2.0.4 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

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 11.2.0.4.9 databases for source and target

GolgenGate binary 12.1.2.1.0

Software Download

1. Go to Url https://edelivery.oracle.com
   
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

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.

SQL> SELECT DBMS_ROWID.ROWID_TO_ABSOLUTE_FNO(ROWID,'SYS','TEST') FNO, DBMS_ROWID.ROWID_BLOCK_NUMBER (ROWID) BLK, NAME,SAL FROM SYS.TEST ;

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 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.

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 01.25.59.000000000 AM               1274785            1274785
14-AUG-12 01.25.56.000000000 AM               1274784            1274784
14-AUG-12 01.25.51.000000000 AM               1274782            1274783
14-AUG-12 01.25.51.000000000 AM               1274782            1274782
14-AUG-12 01.25.48.000000000 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.

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;

MAX(SCN_WRP) MAX(SCN_BAS)

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

          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 */
  

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

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:

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.

ORA-01078/ LRM-00123: Troubleshoot Junk Characters in Oracle Initialization File

So many times we stuck into situation where we were helpless to start database instance using simple oracle text initialization file for auxiliary instance or some test instance. Usually this is very common whenever we copy the sample or source parameter file from some html or formatted document editor, which allow Unicode text. Frustrating thing is that even after checking test file on normal vi editor or notepad couple of time will also not allow to get rid of that mess-up in parameter file. In this situation there are multiple ways to fix the issue.

• Copy the content into notepad and then copy back to the unix console to see if it help. Focus on special characters like ‘, ‘ , “,” etc . Autosuggest options of smart word editors usually convert actual characters into these formatted charters. As soon as you copy the full parameter file content in to notepad or any other low end text editor it’s not that difficult to manually correct it until unless the size of configuration file is large.

 

• Other way is to use od (Octal Dump), a very old utility of unix OS.

 

Today we will demonstrate the troubleshooting of junc character by using od as well as conventional method.

 

Here we have created two text parameter files and one with ‘ ‘  instead of  ‘ ‘

[oracle@maskmt11g.lgk.nmk] ls -lrt initMASK*

-rw-r–r– 1 oracle oinstall 912 May 6 16:46 initMASK11G.ora_correct

-rw-r–r– 1 oracle oinstall 916 May 6 16:54 initMASK11G.ora

[oracle@maskmt11g.lgk.nmk] diff initMASK11G.ora_correct initMASK11G.ora

17c17

< *.db_name=‘mask11g‘

—

> *.db_name=‘mask11g‘

 

Let’s try to start instance using this corrupted parameter file.

SQL> startup nomount pfile=’/home/oracle/initMASK11G.ora’

LRM-00123: invalid character 128 found in the input file

ORA-01078: failure in processing system parameters

Syncing Standby Database Using SCN Based Backup

In Oracle dataguard environment sometime we face issues with slow network pushing us into situation where the changes are piled up and normal redo shipping speed is not sufficient to cover the gap within required business timelines or might be archives/redo never shipped to standby database because of unavailability of standby database and you don’t have enough space on primary site to hold all required archives for recovery. In those situations you might take a decision clear space on primary site after moving archives to tape. Later on when standby will be available FAL service would not able to fetch the gap. One option is to restore those archives to standby site and register those archives while another option, which is of intermediate complexity in case we don’t have identical physical structure in both sites.

Today we will demonstrate recovery of standby database with SCN based incremental backup in environment where we have shared filesystem on primary site while standby database is hosting files in ASM.

1. Check the minimum SCN on standby database from where the changes need to be applied.
   

   Standby Site
   

   SQL> select min (checkpoint_change#) from v$datafile_header order by 1;

   MIN(CHECKPOINT_CHANGE#)
   ———————–
   109182027

How Many SCAN Listeners?

Today we will explore little more about SCAN listeners. The common question comes in mind like: How many SCAN listeners could be configured in a multi-node RAC environment? , How many SCAN listeners would actually be required and how many would be enough? Etc.

Let’s find out the answers for these common queries.

I am assuming that by now most of DBAs are pretty comfortable with SCAN listeners; in case need detailed explanation then following URL would a good choice to visit here. As usual, awesome documentation from Oracle for SCAN in 11g as well as 12c releases.

Basically SCAN Listeners are introduced to create another connection handler on top of existing node listeners to overcome the failover and load balancing issues till 11gR1 architecture for RAC. Till 11gR1 Node listeners were defined on VIPs to facilitate NACK (Negative Acknowledgement) to resolve the issue of TCP timeouts.

Pre 11gR2 database environments, clients may take up to 2 minutes to decide (on TCP level) a node is down. This is purely because of the TCP Timeouts, which can differ from platform to platform. These 2 minutes are unacceptable, and it was a good thing of Oracle to understand and address this issue. Oracle designed a virtual IP address to be assigned to the public interface. Under normal circumstances, the VIP will be located to its designated NIC, and the listener will be bound to this VIP. Whenever there is a need to failover the VIP to another node in the cluster, when clients want to connect to this VIP (due to tns alias addresses pointing to this VIP), the VIP will immediately respond (because it has failed over and the TCP stack is running against it), and the client is able to get negative acknowledgement (NACK) confirming that no listener is active at its designated port. Within few seconds the client will know and fail over to the alternative address in its TNS alias. This makes failover a lot faster.

From 11gR2 onwards oracle enhanced the administration as well as availability of database from client’s perspective, load balancing etc by introducing SCAN (Single Client Access Name). With SCAN, clients could use SCAN-NAME (resolved by 3 VIPs, for default configuration) rather than list of all rac nodes in connect string. By default SCAN listeners (defined on SCAN VIPs) created as first point which co-ordinate with Node Listeners (defined on VIPs), so even if failure of node scan listener running on that node (if any) would be relocated to any surviving node while available SCAN listeners would be serving as normal.

In practice any complex environment designed with multi-tier architecture involving connection handling via connection pool mechanism. It reduces connectivity time for application or web based end users. Normally 3 SCANs are capable enough to handle hundreds of new connections in a span of few seconds. Still if you feel that default 3 SCAN listeners are not enough for your environment then you have an option to add few more scan listeners. GNS based dynamic IP scheme still has no way to change number of SCAN listeners in your environment. Today we will elaborate the process of adding one extra scan listener in our DNS based static IP configuration.

Step by Step Installation of OEM 12c

Few important things to be consider before going for OEM 12c on your environment. First EM 12c is more resource demanding as compared to 10g & 11g versions. Second It’s re-engineered for better monitoring and administration capabilities and not compatible with older versions of agents, so the day you rolling 12c in your environment, you have to install 12c agent on all targets.

Package Requirements for Oracle Management Service

In our case we are setting up EM 12C on Oracle Linux 5.x 64 bit

binutils-2.17.50.0.6

gcc -4.1.1

glibc-common-2.3.4

glibc-devel-2.5-49 (32-bit version as well as the 64-bit version)

libaio-0.3.106

libstdc++ -4.1.1

libXtst-1.0.1-3.1(x86_64)

make-3.81

rng-utils-2.0

setarch-1.6

sysstat-5.0.5

xorg-x11-utils     (This package is required only for GUI-based interactive installation, and not for silent installation)

Cloning Oracle Home in RAC

Cloning of Oracle software is an easy and fast way to achieve standardization across organization where all efforts put on one environment and after testing etc that environment could be used as a source of binaries. A simple tar ball could be shipped to all other servers and then untar on destination environment as a new home or replace the existing oracle home based on availability of space on destination environment.

There could be different situations where we could use this method in a little bit twisted way to achieve desired result. Possible scenarios are:

• Lost filesystem hosting oracle database software, so either new installation or cloning of software from surviving nodes in case of RAC or some other environment in case of standalone environment.
• Enterprise wide periodic patching activity where cloning could save a lot of efforts by just building an image and clone it across enterprise.
• Building new environments during migration databases across of datacenters
• Node edition in RAC also uses cloning technique

Step By Step Installation of 11.2.0.3.0 Standalone RDBMS Software

Going forward with 11gR2 oracle recommending out of place upgrade so providing full installer for major upgrades (i.e. 11.2.0.1.0 , 11.2.0.2.0, 11.2.0.3.0) instead of patchsets. Then we can apply bundle patch or custom patches based on our requirement.

For 11.2.0.3.0, software is available on MOS with patch number 10404530 and could be easily downloadable. This patch contains total 7 files but we need only p10404530_112030_platform_1of7.zip & p10404530_112030_platform_2of7.zip (2.32 GB) to install DB software. This patch is actually full release and could be used for upgrade as well as new installation.

Prerequisite RPMs

binutils-2.17.50.0.6

compat-libstdc++-33-3.2.3

elfutils-libelf-0.125

elfutils-libelf-devel-0.125

elfutils-libelf-devel-static-0.125

gcc-4.1.2

gcc-c++-4.1.2

glibc-2.5-24

glibc-common-2.5

glibc-devel-2.5

glibc-headers-2.5

kernel-headers-2.6.18

ksh-20060214

libaio-0.3.106

libaio-devel-0.3.106

libgcc-4.1.2

libgomp-4.1.2

libstdc++-4.1.2

libstdc++-devel-4.1.2

make-3.81

numactl-devel-0.9.8.i386

sysstat-7.0.2

unixODBC-devel-2.2.11-7.1.i386.rpm

unixODBC-2.2.11-7.1.i386.rpm

Note: 64 bit installation also searches for i386 unixodbc rpms.

GI (Oracle Restart) Upgrade from 11.2.0.1 to 11.2.0.3

Recently I have upgraded Single node GI (Oracle Restart) from 11.2.0.1.0 to 11.2.0.3.0 and thought to share with you. Though it’s not something very interesting to share yet not that bad at all.J

In this blog I’ll use GI or Grid Infrastructure, which is also Oracle Restart in our case. Here are details of GI (Oracle Restart):

Grid Infra Version: 11.2.0.1.0

Grid Home: /u01/app/oracle/product/11.2.0/grid

Host Name: mask11g

Storage: ASM

DB Version: 11.2.0.1.0

Purpose: Upgrade GI from 11.2.0.1.0 to 11.2.0.3.0

Path for upgrade: Out of place (New location on same server) followed by removal of old GI home.

Going forward from 11gR2 oracle recommend to go with out of place upgrade though in place upgrade is still available. I tried both and both are almost similar except the few minor things. In place upgrade enables you to upgrade an existing installation of GI into the same directory by replacing the existing installation files. The patch set application requires more downtime and is not recommended. This upgrade type requires less disk space.

For out of place upgrade we need almost 5 GB extra space during upgrade and we would be able to release space by removing the old grid software.

In order to make it easily understandable as a step by step approach I am attaching snapshots of upgrade process.

Software is available on MOS with patch number 10404530 and could be easily downloadable. This patch contains total 7 files but we need only p10404530_112030_platform_3of7.zip (933 MB) to complete GI upgrade. This patch is actually full release so it could be used to perform new installation as well as upgrade from old versions.

I would suggest you to read the instructions from all aspects before you test the upgrade on your environment. I couldn’t share more details or else this blog looks like upgrade companion doc J.

1. Download and unzip the p10404530_112030_platform_3of7.zip into staging location on your server.
2. Take backup of existing environment
3. Upgrade the GI software
4. Once upgrade is done then you could remove the old GI installation as it’s of no use or could leave it as it is in case you have plenty of space on your server.

DB migration to ASM Storage

Why ASM? It’s debatable point between DBAs and Storage experts. Industry wide Oracle users could be agreed on few points:

1. Reduction in administration overhead.
   
2. Better I/O as compared to placement of datafiles over filesystem.
   
3. Online expansion and migration to faster disks.
   
4. Storage management similar to RAC databases.
   

ASM providing placement of data in chucks (called Allocation Units) along with beauty of automatically rebalances across available disks. Today I am here to share demo of DB migration from filesystem to ASM. Normally there could be few possible ways to achieve migration activity. This is tough to say the best way for migration without actually understanding business requirement.

• Migration of physical database
  
  • Take image copy of database in ASM storage and switch database to copy.
    
  • Take RMAN backup and restore it in ASM disks followed by switch copy.
    
  • Duplicate database into ASM diskgroup.
    
• Migration with physical replication
  
  • Dataguard or Transportable tablespace
    
• Migration with logical replication setup
  
  • Any replication method (Golden Gate, Streams, shareplex etc) to setup parallel environment and perform switchover.
    

Migration using physical standby could involve less downtime and easy performing. Not everyone can afford this approach as it involves extra hardware resources.

Today we will demonstrate migration from filesystem to ASM using RMAN. Apart from straight we will also try to restore spfile and control file from autobackup. This method is one the scenario where we lost our spfile or controlfile. Once controlfile would be available, so the information of backups as well. Although backups could also be registered if required.

What happened when tablespace/database placed into backup mode?

User managed backup or physical backup without RMAN is still one of the popular method for backup and still part of mostly disk to disk backup methods (BCV or SRDF copy). Today I am going to discuss what happens when we place tablespace/database into backup mode.

Fractured Block

Unit of I/O for any system is block size and for oracle database it’s 2K/4K/8K/16K/32K while for any operating system it could be lower than that. One risk in making online backups is the possibility of inconsistent data within a block. Assume that OS I/O block size is 512 bytes while we used DB block size as 8K.

This means to copy utility of OS would take 16 I/Os to take backup of one DB block, while during the backup, DBWR could write 8K in one I/O.

So assume a scenario where copy utility is busy taking backup of 8^th portion of particular block while DBWR came to overwrite same DB block (i.e. all 16 OS block pieces in one DB block). Because copy utility was still busy on taking backup of remaining blocks so it took new image from OS piece 9^th onwards. From database perspective that backup copy of db block (collection of 16 OS blocks) has been fractured as its half or the OS blocks are old and remaining half are new. The result is called a fractured block, meaning that the data contained in this block is not consistent at a given SCN.

Oracle’s internal protection mechanism against fractured block

To overcome this danger Oracle introduced a protection feature which will be activate once you trigger ‘alter tablespace/database begin backup’ command. Following internal operations happened with immediate effect after triggering begin backup for tablespace

Redo Block Size

Redo logs or online redo log files are crucial files for any oracle database to be operational with durability of transaction. All change vectors must be written to current redo log (except nologging and few exceptional settings where database is running purely for performance benchmark purpose)

Redo log files always default to a block size that is equal to the physical sector size of the disk. Historically, this has typically been 512 bytes (512B). Beginning with Oracle Database 11g Release 2, you can specify the block size of online redo log files with the BLOCKSIZE keyword in the CREATE DATABASE, ALTER DATABASE, and CREATE CONTROLFILE statements. The permissible block sizes are 512, 1024, and 4096.

So we are going to check if we can try with this cool option. Let’s check the current block size of existing redo log files from block_size column of v$log view.

Can I Clear Current Redo Logfile while it’s required for recovery??

Normally we need to clear redo log if its corrupted and oracle is not able to reuse it. It may already be archived or may not be. In both cases we need to clear it to allow oracle instance to re-use it. You have to make active redolog to inactive by manual checkpoint or by switching logfile (if possible).

Commands would be

ALTER DATABASE CLEAR UNARCHIVED LOGFILE GROUP <group number>;

ALTER DATABASE CLEAR LOGFILE GROUP <group number>;

In case of corruption in current redo log following two scenarios could take place

1) Database instance crashed with Ora- 600 error; You can’t do much as now you need to check your last successful backup and all archives/incremental backup to perform incomplete recovery.

or

2)  Database still up and running (chances are less but possible); So could we try to clear the corrupted current redo log??

Let’s try to mimic the situation (except corruption)

Little insight of child cursor part -1

Simple queries against demo schema HR under 10g database.

==============================================

SQL> select * from emp,dept where emp.ename=’SMITH’ and dept.deptno=emp.deptno;

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO DEPTNO DNAME LOC
———- ———- ——— ———- ——— ———- ———- ———- ———- ————– ————-
7369 SMITH CLERK 7902 17-DEC-80 800 20 20 RESEARCH DALLAS

SQL> select * from emp,dept where emp.ename=’CLARK’ and dept.deptno=emp.deptno;

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO DEPTNO DNAME LOC
———- ———- ——— ———- ——— ———- ———- ———- ———- ————– ————-
7782 CLARK MANAGER 7839 09-JUN-81 2450 10 10 ACCOUNTING NEW YORK

SQL> select * from emp,dept where emp.ename=’AMIT’ and dept.deptno=emp.deptno;

no rows selected

———————————————————————————————

Listing of child cursor attached to same parent cursor. Oracle creates child cursor for every set of bind variable pair. In that case I was changing ename with SMITH, CLARK, & AMIT. So every time oracle created one child cursor to hold bind variables. Oracle tries to prepare a different execution plan for