Active Database Duplication in 11g

RMAN has the ability to duplicate, or clone, a database from a backup or from an active database. It is possible to create a duplicate database on a remote server with the same file structure, a remote server will a different file structure or the local server with a different file structure.

Active database duplication requires that the auxiliary instance have a password file.  This is because the source database will be connecting directly to the auxiliary database, requiring a password file.  One other key is that you want to use the same SYSDBA password as the source database . Also the destination server requires static listener configuration in a "listener.ora" file. 

Active Database Duplication

Oracle 11g introduced the ability to create duplicate databases directly without the need for a backup. This is known as active database duplication. The process is similar to the backup-based duplication, with a few exceptions.First, and most obviously, you don't need a backup of the source system, but it does have to be in ARCHIVELOG mode.

Here i done my experiments in the same machine .

C:\Users\Admin>rman target sys/sys@db11g catalog rman/rman@catdb auxiliary sys/sys@clonedb

Recovery Manager: Release 11.2.0.1.0 - Production on Fri Jan 17 11:25:30 2014

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

connected to target database: DB11G (DBID=305973962)

connected to recovery catalog database

connected to auxiliary database: CLONE11G (not mounted)

RMAN> DUPLICATE TARGET DATABASE TO CLONE11G from active database;

Starting Duplicate Db at 17-JAN-14

allocated channel: ORA_AUX_DISK_1

channel ORA_AUX_DISK_1: SID=189 device type=DISK

contents of Memory Script:

{

   sql clone "alter system set  db_name =

 ''DB11G'' comment=

 ''Modified by RMAN duplicate'' scope=spfile";

   sql clone "alter system set  db_unique_name =

 ''CLONE11G'' comment=

 ''Modified by RMAN duplicate'' scope=spfile";

   shutdown clone immediate;

   startup clone force nomount

   backup as copy current controlfile auxiliary format  'D:\DATA\CLONE11G\CONTROL01.CTL';

   restore clone controlfile to  'D:\DATA\CLONE11G\CONTROL02.CTL' from

 'D:\DATA\CLONE11G\CONTROL01.CTL';

   alter clone database mount;

}

executing Memory Script

sql statement: alter system set  db_name =  ''DB11G'' comment= ''Modified by RMAN duplicate'' scope=spfile

sql statement: alter system set  db_unique_name =  ''CLONE11G'' comment= ''Modified by RMAN duplicate'' scope=spfile

Oracle instance shut down

Oracle instance started

Total System Global Area     534462464 bytes

Fixed Size                     2177456 bytes

Variable Size                348128848 bytes

Database Buffers             176160768 bytes

Redo Buffers                   7995392 bytes

Starting backup at 17-JAN-14

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=160 device type=DISK

channel ORA_DISK_1: starting datafile copy

copying current control file

output file name=C:\APP\ADMIN\PRODUCT\11.2.0\DBHOME_1\DATABASE\SNCFDB11G.ORA tag=TAG20140117T112848 RECID=1 STAMP=837084529

channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:03

Finished backup at 17-JAN-14

Starting restore at 17-JAN-14

allocated channel: ORA_AUX_DISK_1

channel ORA_AUX_DISK_1: SID=156 device type=DISK

channel ORA_AUX_DISK_1: copied control file copy

Finished restore at 17-JAN-14

database mounted

contents of Memory Script:

{

   set newname for datafile  1 to

 "D:\DATA\CLONE11G\SYSTEM01.DBF";

   set newname for datafile  2 to

 "D:\DATA\CLONE11G\SYSAUX01.DBF";

   set newname for datafile  3 to

 "D:\DATA\CLONE11G\UNDOTBS01.DBF";

   set newname for datafile  4 to

 "D:\DATA\CLONE11G\USERS01.DBF";

   backup as copy reuse

   datafile  1 auxiliary format

 "D:\DATA\CLONE11G\SYSTEM01.DBF"   datafile

 2 auxiliary format

 "D:\DATA\CLONE11G\SYSAUX01.DBF"   datafile

 3 auxiliary format

 "D:\DATA\CLONE11G\UNDOTBS01.DBF"   datafile

 4 auxiliary format

 "D:\DATA\CLONE11G\USERS01.DBF"   ;

   sql 'alter system archive log current';

}

executing Memory Script

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

Starting backup at 17-JAN-14

using channel ORA_DISK_1

channel ORA_DISK_1: starting datafile copy

input datafile file number=00001 name=D:\DATA\DB11G\SYSTEM01.DBF

output file name=D:\DATA\CLONE11G\SYSTEM01.DBF tag=TAG20140117T112857

channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:25

channel ORA_DISK_1: starting datafile copy

input datafile file number=00002 name=D:\DATA\DB11G\SYSAUX01.DBF

output file name=D:\DATA\CLONE11G\SYSAUX01.DBF tag=TAG20140117T112857

channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:15

channel ORA_DISK_1: starting datafile copy

input datafile file number=00003 name=D:\DATA\DB11G\UNDOTBS01.DBF

output file name=D:\DATA\CLONE11G\UNDOTBS01.DBF tag=TAG20140117T112857

channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:03

channel ORA_DISK_1: starting datafile copy

input datafile file number=00004 name=D:\DATA\DB11G\USERS01.DBF

output file name=D:\DATA\CLONE11G\USERS01.DBF tag=TAG20140117T112857

channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01

Finished backup at 17-JAN-14

sql statement: alter system archive log current

contents of Memory Script:

{

   backup as copy reuse

   archivelog like  "D:\ARCHIVE2\ARC0000000022_0836304397.0001" auxiliary format

 "D:\CLONE_ARCHIVE\ARC0000000022_0836304397.0001"   ;

   catalog clone archivelog  "D:\CLONE_ARCHIVE\ARC0000000022_0836304397.0001";

   switch clone datafile all;

}

executing Memory Script

Starting backup at 17-JAN-14

using channel ORA_DISK_1

channel ORA_DISK_1: starting archived log copy

input archived log thread=1 sequence=22 RECID=21 STAMP=837084582

output file name=D:\CLONE_ARCHIVE\ARC0000000022_0836304397.0001 RECID=0 STAMP=0

channel ORA_DISK_1: archived log copy complete, elapsed time: 00:00:01

Finished backup at 17-JAN-14

cataloged archived log

archived log file name=D:\CLONE_ARCHIVE\ARC0000000022_0836304397.0001 RECID=21 STAMP=837084585

datafile 1 switched to datafile copy

input datafile copy RECID=1 STAMP=837084585 file name=D:\DATA\CLONE11G\SYSTEM01.DBF

datafile 2 switched to datafile copy

input datafile copy RECID=2 STAMP=837084585 file name=D:\DATA\CLONE11G\SYSAUX01.DBF

datafile 3 switched to datafile copy

input datafile copy RECID=3 STAMP=837084585 file name=D:\DATA\CLONE11G\UNDOTBS01.DBF

datafile 4 switched to datafile copy

input datafile copy RECID=4 STAMP=837084585 file name=D:\DATA\CLONE11G\USERS01.DBF

contents of Memory Script:

{

   set until scn  1453391;

   recover

   clone database

    delete archivelog

   ;

}

executing Memory Script

executing command: SET until clause

Starting recover at 17-JAN-14

using channel ORA_AUX_DISK_1

starting media recovery

archived log for thread 1 with sequence 22 is already on disk as file D:\CLONE_ARCHIVE\ARC0000000022_0836304397.0001

archived log file name=D:\CLONE_ARCHIVE\ARC0000000022_0836304397.0001 thread=1 sequence=22

media recovery complete, elapsed time: 00:00:00

Finished recover at 17-JAN-14

contents of Memory Script:

{

   shutdown clone immediate;

   startup clone nomount;

   sql clone "alter system set  db_name =

 ''CLONE11G'' comment=

 ''Reset to original value by RMAN'' scope=spfile";

   sql clone "alter system reset  db_unique_name scope=spfile";

   shutdown clone immediate;

   startup clone nomount;

}

executing Memory Script

database dismounted

Oracle instance shut down

connected to auxiliary database (not started)

Oracle instance started

Total System Global Area     534462464 bytes

Fixed Size                     2177456 bytes

Variable Size                348128848 bytes

Database Buffers             176160768 bytes

Redo Buffers                   7995392 bytes

sql statement: alter system set  db_name =  ''CLONE11G'' comment= ''Reset to original value by RMAN'' scope=spfile

sql statement: alter system reset  db_unique_name scope=spfile

Oracle instance shut down

connected to auxiliary database (not started)

Oracle instance started

Total System Global Area     534462464 bytes

Fixed Size                     2177456 bytes

Variable Size                348128848 bytes

Database Buffers             176160768 bytes

Redo Buffers                   7995392 bytes

sql statement: CREATE CONTROLFILE REUSE SET DATABASE "CLONE11G" RESETLOGS ARCHIVELOG

  MAXLOGFILES     16

  MAXLOGMEMBERS      3

  MAXDATAFILES      100

  MAXINSTANCES     8

  MAXLOGHISTORY      292

 LOGFILE

  GROUP  1 ( 'D:\DATA\CLONE11G\REDO01.LOG' ) SIZE 50 M  REUSE,

  GROUP  2 ( 'D:\DATA\CLONE11G\REDO02.LOG' ) SIZE 50 M  REUSE,

  GROUP  3 ( 'D:\DATA\CLONE11G\REDO03.LOG' ) SIZE 50 M  REUSE

 DATAFILE

  'D:\DATA\CLONE11G\SYSTEM01.DBF'

 CHARACTER SET WE8MSWIN1252

contents of Memory Script:

{

   set newname for tempfile  1 to

 "D:\DATA\CLONE11G\TEMP01.DBF";

   switch clone tempfile all;

   catalog clone datafilecopy  "D:\DATA\CLONE11G\SYSAUX01.DBF",

 "D:\DATA\CLONE11G\UNDOTBS01.DBF",

 "D:\DATA\CLONE11G\USERS01.DBF";

   switch clone datafile all;

}

executing Memory Script

executing command: SET NEWNAME

renamed tempfile 1 to D:\DATA\CLONE11G\TEMP01.DBF in control file

cataloged datafile copy

datafile copy file name=D:\DATA\CLONE11G\SYSAUX01.DBF RECID=1 STAMP=837084606

cataloged datafile copy

datafile copy file name=D:\DATA\CLONE11G\UNDOTBS01.DBF RECID=2 STAMP=837084606

cataloged datafile copy

datafile copy file name=D:\DATA\CLONE11G\USERS01.DBF RECID=3 STAMP=837084606

datafile 2 switched to datafile copy

input datafile copy RECID=1 STAMP=837084606 file name=D:\DATA\CLONE11G\SYSAUX01.DBF

datafile 3 switched to datafile copy

input datafile copy RECID=2 STAMP=837084606 file name=D:\DATA\CLONE11G\UNDOTBS01.DBF

datafile 4 switched to datafile copy

input datafile copy RECID=3 STAMP=837084606 file name=D:\DATA\CLONE11G\USERS01.DBF

contents of Memory Script:

{

   Alter clone database open resetlogs;

}

executing Memory Script

database opened

Finished Duplicate Db at 17-JAN-14

RMAN> exit

Note:- 

• Auxiliary instance must be available through Oracle Net if you are duplicating from an ACTIVE database.
• Rman automatically creates  a default server parameter file for the auxiliary instance if the  auxiliary instance was not started with a server parameter file.
• 
  

Retention Policy in rman REDUNDANCY or RECOVERY WINDOW , which one is effective ?

What is  Retention Policy in RMAN ?
Setting the RETENTION POLICY in RMAN keep track about the backup files and tells the report whether they are obsoleted and whether it is needed to backup the database or datafile.
Notes:- 

• If you have flash recovery configured then the database automatically deletes unnecessary files from the flash recovery area based on its internal disk quota rules. The disk quota rules are distinct from the backup retention policy rules, but the database will never delete files in violation of the retention policy to satisfy the disk quota.If you don't have flash recovery configured then the database does not delete any file even they are obsolete.

How to set retention policy in RMAN ? 

The configuration parameter RETENTION POLICY can be set by CONFIGURE RETENTION POLICY  TO .... 

What is the use of crosschek and obsolete command ?

A backup becomes obsolete based on retention policy, that it is not needed for recovery.A backup becomes expired only when RMAN perform CROSSCHECK and can't find the file. (A most common is file is deleted by OS). Obsolete means "not needed," whereas expired means "not found."

Issue the following RMAN command to crosscheck backup of an Oracle Database,

RMAN> crosscheck backup of database;

using channel ORA_DISK_1

crosschecked backup piece: found to be ‘EXPIRED’

backup piece handle=D:\STAGE\RACDEVD1_503\DATAFILE_BACKUP recid=79 stamp=475839783

crosschecked backup piece: found to be ‘EXPIRED’

backup piece handle=D:\STAGE\RACDEVD1_504\DATAFILE_BACKUP recid=137 stamp=475839784

crosschecked backup piece: found to be ‘AVAILABLE’

backup piece handle=D:\DGSTAGE\DPLCU5L5_1_1 recid=132 stamp=475839755

crosschecked backup piece: found to be ‘AVAILABLE’

backup piece handle=D:\DGSTAGE\DQLCU62F_1_1 recid=133 stamp=475839756Crosschecked 4 objects

As we see the RMAN crosscheck command compares the RMAN  catalog entries with the actual OS files and reports to locate "expired" or "obsolete" RMAN catalog entries.

Once a RMAN crosscheck identified expired, deleted  entries you can run an RMAN delete command to remove these entries to synchronize the RMAN catalog / controlfile  with the real database files:
RMAN> delete expired backup;
Identify which backups are obsolete and no longer needed for recovery

RMAN> REPORT OBSOLETE;
Delete obsolete backup information from RMAN repository.
RMAN> DELETE OBSOLETE;

How can we set retention policy in RMAN ?

There is two mutually exclusive options for setting a retention policy , redundancy and recovery window.

To set Retention policy to  redundancy 2 copies,

RMAN> CONFIGURE RETENTION POLICY TO REDUNDANCY 2;

set Retention Policy to  Recovery Window of 7 days,

RMAN>CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 7 DAYS;

Whenever retention policy is set to redundancy of 2 copies the rman at least retains latest 2 copies of each datafile. If I took 3rd backup of datafile 3 then 1st backup of datafile 3 become obsolete. 

If you specify a recovery window of 7 days. That means rman will not obsolete any backup needed for recovery to any point in the last seven days. Check. But what's the subtle detail?

Suppose you take a full backup on 1 Sep, then start taking incremental backups going forward. Now we're down to 10 Sep which puts that full backup 3 days earlier than the recovery window. However, it is still the most recent full backup and so is needed to recover into any time during the 7 day recovery window. Therefore, it will NOT be marked obsolete.

Think about what this does in the textbook backup routine of a full backup every 7 days, with incremental backups the other 6 days. We take a full backup on day 1. We take incremental backups on days 2 - 7. We take a full backup again on Day 8, and incrementals on days 9 - 14. 

Day  =  1  2  3  4  5  6  7  8  9 10 11 12 13 14

Bkup =  F  I   I   I  I   I   I  F   I   I   I   I   I    I

On day 14, our recovery window reaches back to day 7, and the only way to recover to day 7 is to use the full backup from day 1 and the incrementals from 2 through 7. So even though we have a 7 day recovery window, we actually have 14 days worth of backups that are yet to go obsolete. On day 15, we can obsolete and drop everything prior to the full backup on Day 8. Thus the Recovery Window retention policy doesn't just keep the last N days of the backup. It calculates the point-of-recovery (POR) value, accounting the current date and the number of the days mentioned in the retention policy. 

The default retention policy is REDUNDANCY = 1

you can disable the retention policy by setting,

RMAN>CONFIGURE RETENTION POLICY TO NONE;

If the retention policy is configured to NONE, then REPORT OBSOLETE and DELETE OBSOLETE do not consider any backups to be obsolete

Conclusion :- 
When having a rman retention policy based on REDUNDANCY is a bad idea ,if you intend to incomplete recover your database to a previous time in the past it's really a good idea to switch to a retention policy based on a "RECOVERY WINDOW" instead. 

what is snapshot controlfile and when we can make use of it??

RMAN needs to get a consistent view of the control file when it sets out to make backup of every datafile. It only needs to know the most recent checkpoint information and file schematic information at the time backup begins. After the backup starts, RMAN needs this information to stay consistent for duration of the backup operation; in other words, it needs a read consistent view of the control file. With the constant updates from the database, this is nearly impossible - unless RMAN were to lock the control file for the duration of the backup. But that would mean the database could not advance checkpoint or switch logs or produce new archive logs. Impossible.
To get around this, RMAN uses the snapshot control file, an exact copy of your control file that is only used by RMAN during backup and resync operations. At the beginning of these operations, RMAN refreshes the snapshot control file from the actual control file, thus putting a momentary lock on the control file. Then, RMAN switches to the snapshot and uses it for the duration of the backup; in this way, it has read consistency without holding up database activity.

ie , When RMAN needs to resynchronize from a read-consistent version of the control file, it creates a temporary snapshot control file. RMAN needs a snapshot control file only when resynchronizing with the recovery catalog or when making a backup of the current control file.

The default value for the snapshot control file is platform-specific and depends on the Oracle home. For example, the default filename on some UNIX platforms in Oracle Database 10g is $ORACLE_HOME/dbs/snapcf_@.f. Note that if you have a flash recovery area configured, the default location for the snapshot control file isnot the flash recovery area.

You can see the current snapshot location by running the SHOW command. This example shows a snapshot location that is determined by the default rule:

RMAN> SHOW SNAPSHOT CONTROLFILE NAME;

CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/oracle/dbs/snapcf_trgt.f'; # default

This example shows a snapshot control file that has a nondefault filename:

RMAN>  SHOW SNAPSHOT CONTROLFILE NAME;

CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/oracle/oradata/trgt/snap_trgt.ctl';

Setting the Location of the Snapshot Control File

Use the CONFIGURE SNAPSHOT CONTROLFILE NAME TO 'filename' command to change the name of the snapshot control file. Subsequent snapshot control files that RMAN creates use the specified filename.

For example, start RMAN and then enter:

RMAN>CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/oracle/oradata/trgt/snap_trgt.ctl';

If one RMAN job is already backing up the control file while another needs to create a new snapshot control file, you may see the following message:

waiting for snapshot control file enqueue

Under normal circumstances, a job that must wait for the control file enqueue waits for a brief interval and then successfully retrieves the enqueue. Recovery Manager makes up to five attempts to get the enqueue and then fails the job. The conflict is usually caused when two jobs are both backing up the control file, and the job that first starts backing up the control file waits for service from the media manager.

what to take care in RAC environment ? 

Identical copies of the control file must be maintained on every node that participates in the RAC backup process. Therefore, each node must have an identical directory location to store a snapshot of the current control file ,So it is necessary in RAC environments that the snapshot controlfile is located on shared storage. Otherwise your RMAN backup may fail.For this reason you can put the snapshot controlfile into ASM .

For example 

RMAN> configure snapshot controlfile name to '+FRA/snapcf_oralin.f';  

When it will get generated? 

Basically the snapshot controlfile is created when oracle needs a "read-consistent version" of the controlfile.In my case I have a catalog database which is synchronized at the beginning of the backup so the snapshot controlfile is created at the beginning of the backup.If you backup with rman without a catalog it will be at the end of the backup.

Is the snapshot control file ever needed during recovery? 

Short and right answer is "no". 

Read the definition of the (RMAN) "Snapshot Control File" below.

Generally I would consider the (RMAN) "Snapshot Control File" worthless as it knows *nothing* about the backup (sets) currently being completed/made 

Meaning the last backup metadata is not *in* the "Snapshot Control File", as it was "snapped" before the back started/completed.

*HOWEVER* it is a copy of the controlfie at the beginning of your current backup and while it knows nothing about the current backup, it "still" has knowledge/metadata about all previous RMAN backups (not deleted, marked missing or obsolete by RMAN).

I can invision a scenario where I lost *ALL* controlfiles on disk and on tape *AND* I do not use a RMAN Recovery Catalog. I would try and use the last known available RMAN "Snapshot Control File" to restore my database from an *older* (than the Snapshot Control File) backup and recovery my database.

  

Increasing Processes, Sessions and Transactions in Oracle

If your maximum number of process and/or sessions exceeds the limit ,oracle will throws following errors 
ORA-12516: TNS:listener could not find available handler with matching protocol stack
ORA-00020: maximum number of processes (%s) exceeded .
When this occurs, the service handlers for the TNS listener become "Blocked" and no new connections can be made.

Below query gives present allocation 
SQL>select name,value from v$parameter where name in ('processes','sessions','transactions');  

Below quey gives current number of process
SQL>select count(*) from v$process; 

Below quey gives current number of session
SQL>select count(*) from v$session;

Below quey gives current number of transaction
SQL>select count(*) from v$transaction;

General formula to calculate process,session and transaction
PROCESSES = 40 to Operating System Dependant
SESSIONS = (1.1 * PROCESSES) + 5
TRANSACTIONS = 1.1 * SESSIONS

ie For increasing process parameter you should consider increasing sesson and transactions parameter as well.

if we need to change do the following ,

SQL>alter system set processes = 1000 scope = spfile;

SQL>alter system set sessions = 1105 scope = spfile;

SQL>alter system set transactions = 1215 scope = spfile;

SQL>shutdown immediate;

SQL>startup;

For checking the current utilization , maximum utilization of process and session
SQL>SELECT upper(resource_name) as resource_name,current_utilization,max_utilization,initial_allocation FROM v$resource_limit WHERE resource_name in ('processes', 'sessions');

How to change the redo log size in RAC

There is no any ALTER... command to resize the REDO logs in orace. So if you want to resize your REDO logs you will need to create a new group with a new size and then drop the old one.

Let's say you have this situation in your RAC for two nodes: ( say rac1 and rac2) .From rac1 node do the following ,

SQL> select GROUP#,MEMBERS,BYTES/1024/1024 MB_Size,status from v$log;
    GROUP#    THREAD#    MEMBERS    MB_SIZE STATUS
   ----------      ----------         ----------      ---------- ----------------
             1                      1                     2                 50 CURRENT
             2                      1                     2                 50 INACTIVE
             3                      2                     2                 50 INACTIVE
             4                      2                     2                 50 CURRENT

and you want to resize all your groups. Lets say you want to set 100M instead of 50M.

Action plan:
1. Add new REDO groups with a new size.

SQL> ALTER DATABASE ADD LOGFILE THREAD 1 GROUP 5 ( '+DATA','+FLASH') SIZE 100M;
SQL> ALTER DATABASE ADD LOGFILE THREAD 1 GROUP 6 ( '+DATA','+FLASH') SIZE 100M;
SQL> ALTER DATABASE ADD LOGFILE THREAD 2 GROUP 7 ( '+DATA','+FLASH') SIZE 100M;
SQL> ALTER DATABASE ADD LOGFILE THREAD 2 GROUP 8 ( '+DATA','+FLASH') SIZE 100M;

Mentioned commands will create 4 new groups with two members in each. Change the volume groups (+DATA and +FLASH) according to your environment .

2. Now you should wait till the Group 1/2/3/4 will start to be INACTIVE so you would be able to drop them. Also you can speed up this process by executing:

SQL> alter system switch logfile;
SQL> alter system checkpoint;

3. To DROP the old groups.

SQL> ALTER DATABASE DROP LOGFILE GROUP 1;

In busy DB it is possible to see something like this during drop operation:
ORA-01623: log 1 is current log for instance RPTDB (thread 1) - cannot drop

in that case you should execute the following again:
SQL> alter system switch logfile;
or
SQL> alter system checkpoint;
or
just continue your tries to drop it.

Note: - I tried to drop a redo log group but it is always CURRENT or ACTIVE. I done alter system switch logfile and alter system checkpoint  repeatedly but it does not become inactive . I overcome this issue by executing  alter system switch logfile  on the other node (rac2).Once all your old GROUPs will be dropped your output would be look like this:

SQL> select GROUP#,MEMBERS,BYTES/1024/1024 MB_Size from v$log;

 GROUP#    THREAD#    MEMBERS    MB_SIZE
 ----------     ----------         ----------     ----------
             5                    1                     2         100
             6                    1                     2         100
             7                    2                     2         100
             8                    2                     2         100

Sourece:-  http://eugene-dba.blogspot.in/2012/10/change-redo-log-size-in-rac.html

How to upgrade oracle client from 10.2.0.3 to 10.2.0.4

Recently i got a request  from my developer  team to install oracle client version 10.2.0.4.

Here is the step that i followed to achieve the  same.

1. First install oracle client version 10.2.0.3 on you computer 

2.Download oracle 10.2.0.4 patch set (6810189) from oracle website.

   For example i downloaded p6810189_10204_Win32 for my windows 32 bit system .

Note:- There is no separate patch set for oracle client, the same patch for oracle database software  can be used for oracle client .

4.Extract the patch set  and run the setup.exe file . On the specify home details  window

choose the appropriate oracle home that you want to upgrade and click next

Once the installation become finished check the version of the client software by tnsping command 

hope it help somebody :) 

How to properly shutdown oracle rac database and its clusterware components in 11gR2

The first step to shutdown any database whether it is RAC or single instance is to stop all the applications that are accessing the Database. DBA should also stop Oracle application like Enterprise Manager Grid Control and Database Listener which access database for monitoring and database connections.

1.Stop Enterprise Manager/Grid Control
[oracle@rac1 ~]$ emctl status dbconsole
Environment variable ORACLE_UNQNAME not defined. Please set ORACLE_UNQNAME to database unique name.
[oracle@rac1 ~]$
[oracle@rac1 ~]$ export ORACLE_UNQNAME=myrac
[oracle@rac1 ~]$  emctl status dbconsole
Oracle Enterprise Manager 11g Database Control Release 11.2.0.1.0
Copyright (c) 1996, 2009 Oracle Corporation.  All rights reserved.
https://rac1.mydomain:1158/em/console/aboutApplication
Oracle Enterprise Manager 11g is running.
------------------------------------------------------------------
Logs are generated in directory /app/dbHome/rac1_myrac/sysman/log
[oracle@rac1 ~]$ emctl stop dbconsole
Oracle Enterprise Manager 11g Database Control Release 11.2.0.1.0
Copyright (c) 1996, 2009 Oracle Corporation.  All rights reserved.
https://rac1.mydomain:1158/em/console/aboutApplication
Stopping Oracle Enterprise Manager 11g Database Control ... ...  Stopped.
[oracle@rac1 ~]$ emctl status dbconsole
Oracle Enterprise Manager 11g Database Control Release 11.2.0.1.0
Copyright (c) 1996, 2009 Oracle Corporation.  All rights reserved.
https://rac1.mydomain:1158/em/console/aboutApplication
Oracle Enterprise Manager 11g is not running.
[oracle@rac1 ~]$

2.Stop Node listener 
[root@rac1 ~]# srvctl status listener
Listener LISTENER is enabled
Listener LISTENER is running on node(s): rac1,rac2
For checking the status on specific nodes use the following command,
[root@rac1 ~]# srvctl status listener -n rac1  
Listener LISTENER is enabled on node(s): rac1
Listener LISTENER is running on node(s): rac1
[root@rac1 ~]# ps -ef | grep tns
root        13     2  0 Oct28 ?        00:00:00 [netns]
grid     14547     1  0 09:32 ?        00:00:00 /app/gridHome/bin/tnslsnr LISTENER -inherit
oracle   14640 14218  0 09:34 pts/2    00:00:00 grep tns
grid     32208     1  0 Oct28 ?        00:00:01 /app/gridHome/bin/tnslsnr LISTENER_SCAN1 -inherit
[root@rac1 ~]# srvctl stop listener  --> this command will stop the node listener on entire 
node, if you want to do it on specific node use following command,
srvctl stop listener -n node1
[root@rac1 ~]# srvctl status listener
Listener LISTENER is enabled
Listener LISTENER is not running
[root@rac1 ~]# ps -ef | grep tns
root        13     2  0 Oct28 ?        00:00:00 [netns]
oracle   14704 14218  0 09:34 pts/2    00:00:00 grep tns
grid     32208     1  0 Oct28 ?        00:00:01 /app/gridHome/bin/tnslsnr LISTENER_SCAN1 -inherit
[root@rac1 ~]#
Note:- Now you can see  only SCAN listener is running  on this node

3.Stopping database on all node
[root@rac1 ~]#  srvctl status database -d MYRAC  
Instance myrac1 is running on node rac1
Instance myrac2 is running on node rac2
[root@rac1 ~]# srvctl stop database -d MYRAC
The above command will shutdown all the instance within the cluster . To shutdown specific instance use the following syntax
srvctl stop database -d db_name –i instance_name
[root@rac1 ~]# srvctl status database -d MYRAC
Instance myrac1 is not running on node rac1
Instance myrac2 is not running on node rac2
[root@rac1 ~]# ps -ef  | grep pmon
oracle   15161 14218  0 09:53 pts/2    00:00:00 grep pmon
grid     31716     1  0 Oct28 ?        00:00:07 asm_pmon_+ASM1
[root@rac1 ~]#

4.Stopping ASM instances and clusterware components
Now we have to shutdown asm instances and its clusterware components . As of 11gR2, we don't have to shutdown asm instances and its node apps separately . Upto 11gR1 we have to shutdown these process separately using following commands ,
Use below command to shutdown ASM instances on all nodes
srvctl stop asm -n node
Use below command to shutdown node apps on all RAC nodes
srvctl stop nodeapps -n node
Use below command to shutdown Oracle clusterware on all RAC nodes
crsctl stop crs
Please note that using above command will stop Oracle High availability services (OHAS) and Clustware stack in a single command

From 11g R2, you can do this in two steps

First stop the clusterware stack on all nodes in the cluster 

[root@rac1 ~]# crsctl stop cluster -all
CRS-2673: Attempting to stop 'ora.crsd' on 'rac1'
CRS-2673: Attempting to stop 'ora.crsd' on 'rac2'
CRS-2790: Starting shutdown of Cluster Ready Services-managed resources on 'rac2'
CRS-2673: Attempting to stop 'ora.LISTENER.lsnr' on 'rac2'
CRS-2673: Attempting to stop 'ora.LISTENER_SCAN3.lsnr' on 'rac2'
CRS-2673: Attempting to stop 'ora.LISTENER_SCAN2.lsnr' on 'rac2'
CRS-2673: Attempting to stop 'ora.OCR.dg' on 'rac2'
CRS-2673: Attempting to stop 'ora.DATA.dg' on 'rac2'
CRS-2673: Attempting to stop 'ora.FRA.dg' on 'rac2'
CRS-2790: Starting shutdown of Cluster Ready Services-managed resources on 'rac1'
CRS-2673: Attempting to stop 'ora.LISTENER.lsnr' on 'rac1'
CRS-2673: Attempting to stop 'ora.LISTENER_SCAN1.lsnr' on 'rac1'
CRS-2673: Attempting to stop 'ora.OCR.dg' on 'rac1'
CRS-2673: Attempting to stop 'ora.DATA.dg' on 'rac1'
CRS-2673: Attempting to stop 'ora.FRA.dg' on 'rac1'
CRS-2677: Stop of 'ora.LISTENER.lsnr' on 'rac1' succeeded
CRS-2673: Attempting to stop 'ora.rac1.vip' on 'rac1'
CRS-2677: Stop of 'ora.rac1.vip' on 'rac1' succeeded
CRS-2677: Stop of 'ora.LISTENER_SCAN1.lsnr' on 'rac1' succeeded
CRS-2673: Attempting to stop 'ora.scan1.vip' on 'rac1'
CRS-2677: Stop of 'ora.scan1.vip' on 'rac1' succeeded
CRS-2677: Stop of 'ora.LISTENER.lsnr' on 'rac2' succeeded
CRS-2673: Attempting to stop 'ora.rac2.vip' on 'rac2'
CRS-2677: Stop of 'ora.rac2.vip' on 'rac2' succeeded
CRS-2677: Stop of 'ora.LISTENER_SCAN2.lsnr' on 'rac2' succeeded
CRS-2677: Stop of 'ora.LISTENER_SCAN3.lsnr' on 'rac2' succeeded
CRS-2673: Attempting to stop 'ora.scan3.vip' on 'rac2'
CRS-2677: Stop of 'ora.scan3.vip' on 'rac2' succeeded
CRS-2677: Stop of 'ora.OCR.dg' on 'rac1' succeeded
CRS-2677: Stop of 'ora.FRA.dg' on 'rac1' succeeded
CRS-2677: Stop of 'ora.DATA.dg' on 'rac1' succeeded
CRS-2673: Attempting to stop 'ora.asm' on 'rac1'
CRS-2677: Stop of 'ora.asm' on 'rac1' succeeded
CRS-2673: Attempting to stop 'ora.eons' on 'rac1'
CRS-2673: Attempting to stop 'ora.ons' on 'rac1'
CRS-2677: Stop of 'ora.ons' on 'rac1' succeeded
CRS-2673: Attempting to stop 'ora.net1.network' on 'rac1'
CRS-2677: Stop of 'ora.net1.network' on 'rac1' succeeded
CRS-2677: Stop of 'ora.eons' on 'rac1' succeeded
CRS-2792: Shutdown of Cluster Ready Services-managed resources on 'rac1' has completed
CRS-2677: Stop of 'ora.crsd' on 'rac1' succeeded
CRS-2673: Attempting to stop 'ora.cssdmonitor' on 'rac1'
CRS-2673: Attempting to stop 'ora.ctssd' on 'rac1'
CRS-2673: Attempting to stop 'ora.evmd' on 'rac1'
CRS-2673: Attempting to stop 'ora.asm' on 'rac1'
CRS-2677: Stop of 'ora.OCR.dg' on 'rac2' succeeded
CRS-2677: Stop of 'ora.cssdmonitor' on 'rac1' succeeded
CRS-2677: Stop of 'ora.evmd' on 'rac1' succeeded
CRS-2677: Stop of 'ora.DATA.dg' on 'rac2' succeeded
CRS-2677: Stop of 'ora.FRA.dg' on 'rac2' succeeded
CRS-2673: Attempting to stop 'ora.asm' on 'rac2'
CRS-2677: Stop of 'ora.asm' on 'rac2' succeeded
CRS-2673: Attempting to stop 'ora.eons' on 'rac2'
CRS-2673: Attempting to stop 'ora.net1.network' on 'rac2'
CRS-2677: Stop of 'ora.net1.network' on 'rac2' succeeded
CRS-2677: Stop of 'ora.ctssd' on 'rac1' succeeded
CRS-2677: Stop of 'ora.asm' on 'rac1' succeeded
CRS-2673: Attempting to stop 'ora.cssd' on 'rac1'
CRS-2677: Stop of 'ora.cssd' on 'rac1' succeeded
CRS-2673: Attempting to stop 'ora.diskmon' on 'rac1'
CRS-2677: Stop of 'ora.diskmon' on 'rac1' succeeded
CRS-2675: Stop of 'ora.eons' on 'rac2' failed
CRS-2679: Attempting to clean 'ora.eons' on 'rac2'
CRS-2681: Clean of 'ora.eons' on 'rac2' succeeded
CRS-2792: Shutdown of Cluster Ready Services-managed resources on 'rac2' has completed
CRS-2677: Stop of 'ora.crsd' on 'rac2' succeeded
CRS-2673: Attempting to stop 'ora.cssdmonitor' on 'rac2'
CRS-2673: Attempting to stop 'ora.ctssd' on 'rac2'
CRS-2673: Attempting to stop 'ora.evmd' on 'rac2'
CRS-2673: Attempting to stop 'ora.asm' on 'rac2'
CRS-2677: Stop of 'ora.cssdmonitor' on 'rac2' succeeded
CRS-2677: Stop of 'ora.evmd' on 'rac2' succeeded
CRS-2677: Stop of 'ora.ctssd' on 'rac2' succeeded
CRS-2677: Stop of 'ora.asm' on 'rac2' succeeded
CRS-2673: Attempting to stop 'ora.cssd' on 'rac2'
CRS-2677: Stop of 'ora.cssd' on 'rac2' succeeded
CRS-2673: Attempting to stop 'ora.diskmon' on 'rac2'
CRS-2677: Stop of 'ora.diskmon' on 'rac2' succeeded
[root@rac1 ~]#
 Note:- If you want to stop Clustwerware stack on local node use the following command

#crsctl stop cluster

[root@rac1 ~]# crsctl check crs
CRS-4638: Oracle High Availability Services is online
CRS-4535: Cannot communicate with Cluster Ready Services
CRS-4530: Communications failure contacting Cluster Synchronization Services daemon
CRS-4534: Cannot communicate with Event Manager
[root@rac1 ~]#
Here you can see only Oracle High Availability Services is in online , Stop Oracle High availability service demon on each node in the cluster.
[root@rac1 ~]# crsctl stop has
CRS-2791: Starting shutdown of Oracle High Availability Services-managed resources on 'rac1'
CRS-2673: Attempting to stop 'ora.mdnsd' on 'rac1'
CRS-2673: Attempting to stop 'ora.gpnpd' on 'rac1'
CRS-2677: Stop of 'ora.mdnsd' on 'rac1' succeeded
CRS-2677: Stop of 'ora.gpnpd' on 'rac1' succeeded
CRS-2673: Attempting to stop 'ora.gipcd' on 'rac1'
CRS-2677: Stop of 'ora.gipcd' on 'rac1' succeeded
CRS-2793: Shutdown of Oracle High Availability Services-managed resources on 'rac1' has completed
CRS-4133: Oracle High Availability Services has been stopped.
[root@rac1 ~]#
You have to execute the same command 'crsctl stop has'  on all nodes in the cluster , Here i executed the same command on my node2
[root@rac2 ~]#  crsctl stop has
CRS-2791: Starting shutdown of Oracle High Availability Services-managed resources on 'rac2'
CRS-2673: Attempting to stop 'ora.gpnpd' on 'rac2'
CRS-2673: Attempting to stop 'ora.mdnsd' on 'rac2'
CRS-2677: Stop of 'ora.gpnpd' on 'rac2' succeeded
CRS-2673: Attempting to stop 'ora.gipcd' on 'rac2'
CRS-2677: Stop of 'ora.mdnsd' on 'rac2' succeeded
CRS-2677: Stop of 'ora.gipcd' on 'rac2' succeeded
CRS-2793: Shutdown of Oracle High Availability Services-managed resources on 'rac2' has completed
CRS-4133: Oracle High Availability Services has been stopped.

[root@rac2 ~]#

[root@rac1 ~]# crsctl check cluster -all

CRS-4639: Could not contact Oracle High Availability Services
CRS-4000: Command Check failed, or completed with errors.
[root@rac1 ~]#

Finally we cleanly shut-downed our two node oracle rac  . Hope these steps helps somebody :)