11gR2 RAC Step By Step Configuration On VMWare part1

I successfully configured 11gr2 RAC on Oracle Enterprise Linux 5.6 (64 bit)  on my laptop , for that i  inspired the article posted by mohamed azar on his blog
http://mohamedazar.wordpress.com
But during the installation i stucked in a lot of stage , that i want to share here.

I configured the whole setup in my windows 7 (i5-64 bit) laptop having  8GB RAM (i suggest 8GB even you can see many blogs and website telling that they configured under 6GB , i fed up with many stages as my LAP goes slow). For the entire set up you will require minimum of  90GB free space in your hard disk.
30 GB for each node and a 30GB for shared storage ( In 11gr2 we can store datafiles , OCR and voting disk in ASM  )

I divide the entire stage into 5 parts

1. Vmware installation
2. Nodes configuration on vmware
3. Installing and configuring Oracle Enterprise Linux 5.6 on VMware + Preparing VMware clone for node2
3. GRID configuration (  In 11gr2 clusterware is renamed as GRID)
5. Rdbms instance configuration

I indebted  Mohamed azar for his effort and here i am using his documents with some alteration.

STAGE 1

1. Vmware configuration

1. First you have to download vmware server , here i used the version "VMware-server-2.0.2". You can download the software free from vmware website , for that you have to register on vmware website after that you will get the link for download. Make sure to save the  the appropriate serial number that you got from vmware website during registration.
So to get VMWare Server free serial number or product key, simply complete this registration. You can get for free up to 100 serial numbers at one go without any  charges or fees.

After getting downloaded (size should be near to 520MB) ,install the software on you laptop.
It is very simple to install  just go forward with each stage as its says..

 Installing VMware Server

Once you download the VMware Server 2.0 file, double click on the file to start the installation

The above screen should come up when you double click on the installation file. wait a few minutes until the installation wizard comes up:

  

click on the Next button.

accept the license agreement on the following screen, and click Next

  
 On the next window choose the destination folder.The default path should be fine.

Click on the next button.

Next, you will need to choose  the storage path for your virtual machines. The default path is  again,

As you can see, you also need to specify the full qualified domain of the machine VMware server will be hosted on. the default settings are fine with me, because I will be using VMware on my local computer. also uncheck “Allow virtual machines to start and stop automatically with the system” that is if you are not going to be using VMware server 2.0 regularly.

click next, after you enter the settings. Next, you will need to choose where you will want to have the shortcuts to launch VMware server. click next, after that.

on the  next screen, you are ready for the installation, click on Install.

After a while through the installation, VMware server installation wizard should ask you for the registration key:

  

Enter your own registration key you got on your e-mail from VMware. click Enter after that.

the installation of VMware Server 2.0 should be completed.

click on Finish.

restart the computer to launch VMware server for  the first time. 

ORA-1652: unable to extend temp segment by x in tablespace  YYY

Today i faced ORA-1652 error on my production environment ,  this type of issue is common with TEMP tablespace but here it comes on non-TEMP tablespace

i am curious about this error , because my datafiles  are in ASM with autoextensible  ON.
there may be no  chance for getting ORA-1652 error .

Later i find out the root cause of the problem , as my datafile belonging to the corresponding
tablespace  reached the maximum limit of allowed size .

As my tablepace is created as SFT (small file tablespace) , there should be a limitation of 32 GB
for each datafile (assuming that block size was 8k)

So i added one more datafile in affected tablespace using following query

alter tablespace yyy add datafile '+DATA' size 5000M;

And the issue got resolved.

Some Useful oracle query for every dba

1. To find the DDL of schema's in Oracle Database Command to find the DDL of Schema's:

select dbms_metadata.get_ddl( 'USER','username' ) from dual

     UNION ALL

     select dbms_metadata.get_granted_ddl('SYSTEM_GRANT','username') from

dual

          UNION ALL

          select dbms_metadata.get_granted_ddl('OBJECT_GRANT','username')

from dual

               UNION ALL

               select dbms_metadata.get_granted_ddl('ROLE_GRANT','username')

from dual;

2.how to trace a querry before executing that

Enable SQL TRACE for a SQL*Plus session (this starts tracing for an individual session):

sql> alter session set SQL_TRACE true;

now run your querry

find the hint of trace file generated using following querry

Select spid, s.sid,s.serial#, s.username, p.program

from v$process p, v$session s

where p.addr = s.paddr

and s.sid = (select sid from v$mystat where rownum=1);

--- the aboe query will returns the sid of the given session

Disable SQL TRACE for the SQL*Plus session:

alter session set SQL_TRACE false;

3.See what SQL users are running on the system

select a.sid, a.serial#, b.sql_text from v$session a, v$sqlarea b

where a.sql_address=b.address

and a.username='SFMS';

4.Here is a query that gives us a list of blocking sessions and the sessions that they are blocking:

select

   blocking_session,

   sid,

   serial#,

   wait_class,

   seconds_in_wait

from

   v$session

where

   blocking_session is not NULL

order by

   blocking_session;

BLOCKING_SESSION        SID    SERIAL# WAIT_CLASS           SECONDS_IN_WAIT

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

             148        135      61521 Idle                              64

In this case, we find that session 148 is blocking session 135 and has been for 64 seconds. We would then want to find out who is running session 148, and go find them and see why they are having a problem.

note:-blocking_session is availabe from 10g onwards

5.Identify the blocking session

   select l1.sid, ' IS BLOCKING ', l2.sid

    from v$lock l1, v$lock l2

   where l1.block =1 and l2.request > 0

   and l1.id1=l2.id1

   and l1.id2=l2.id2;

Detailed inforamation

select s1.username || '@' || s1.machine

    || ' ( SID=' || s1.sid || ' )  is blocking '

   || s2.username || '@' || s2.machine || ' ( SID=' || s2.sid || ' ) ' AS blocking_status

    from v$lock l1, v$session s1, v$lock l2, v$session s2

    where s1.sid=l1.sid and s2.sid=l2.sid

   and l1.BLOCK=1 and l2.request > 0

   and l1.id1 = l2.id1

   and l2.id2 = l2.id2 ;

identifieying the content of locked row

select do.object_name,

   row_wait_obj#, row_wait_file#, row_wait_block#, row_wait_row#,

    dbms_rowid.rowid_create ( 1, ROW_WAIT_OBJ#, ROW_WAIT_FILE#, ROW_WAIT_BLOCK#, ROW_WAIT_ROW# )

    from v$session s, dba_objects do

    where sid=143

    and s.ROW_WAIT_OBJ# = do.OBJECT_ID ;

if your output is like this

OBJECT_NAME     ROW_WAIT_OBJ# ROW_WAIT_FILE# ROW_WAIT_BLOCK# ROW_WAIT_ROW# DBMS_ROWID.ROWID_C

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

TSTLOCK                 88519             16          171309             0 AAAVnHAAQAAAp0tAAA

Then obtain the locked row by

select * from tstlock where rowid='AAAVnHAAQAAAp0tAAA' ;

FOO BAR

--- ---

1   a

6.redo log switch per day

select trunc(completion_time) rundate

,count(*) logswitch

,round((sum(blocks*block_size)/1024/1024)) "REDO PER DAY (MB)"

from v$archived_log

group by trunc(completion_time)

order by 1 desc;

7.High Physical Read Sessions

Select OSUSER os_user,username,PROCESS pid, ses.SID sid,SERIAL#,PHYSICAL_READS,

BLOCK_CHANGES

from v$session ses,

v$sess_io sio

where ses.SID = sio.SID

and username is not null

and status='ACTIVE'

order by PHYSICAL_READS;

8. dbms_stats.gather_table_stats - Stats Gather Dynamic Sql script for tables

SELECT    ' exec sys.dbms_stats.gather_table_stats (ownname=>'''
       || owner
       || ''',tabname=>'''
       || table_name
       || ''', estimate_percent=>SYS.DBMS_STATS.AUTO_SAMPLE_SIZE, method_opt=>''FOR ALL COLUMNS SIZE 1'',degree=>8,cascade=>true,no_invalidate=>false);'
  FROM dba_tables
WHERE table_name IN
          ('DEPT','BONUS','EMP','SALGRADE')
   AND owner = 'SCOTT';

9. You can check freespace in undo tablesapce with :

select sum(bytes)/1024/1024 "available M in RBS" from dba_undo_extents where status='EXPIRED';

Blocks are expired when undo_retention is past after the commit/rollback of the transaction.

10. how to calculate network bandwidth in data guard configuration

SELECT DT,
SUM(RB*8/3600000000*1.3) Mbps_REQ_A_DAY,
MIN(RB*8/3600000000*1.3) MIN_Mbps_REQ_AN_HOUR,
MAX(RB*8/3600000000*1.3) MAX_Mbps_REQ_AN_HOUR ,
AVG(RB*8/3600000000*1.3) AVG_Mbps_REQ_AN_HOUR
FROM
(
SELECT TRUNC (COMPLETION_TIME) DT,
TO_CHAR (COMPLETION_TIME,'HH24') HH,
SUM(BLOCKS*BLOCK_SIZE) RB
FROM
V$ARCHIVED_LOG
WHERE COMPLETION_TIME > SYSDATE-5
AND DEST_ID=1
GROUP BY TRUNC(COMPLETION_TIME),
TO_CHAR (COMPLETION_TIME, 'HH24')
)
GROUP BY DTorder by DT; 

11. Query to find out features not available in particular database edition

SELECT Parameter,Value FROM V$OPTION Where Value = 'FALSE';

How to Prevent a User to Drop Own Objects

An Oracle user is always granted to drop their own objects. To prevent a user to drop their own objects, we can use DDL triggers.

Here's sample trigger that will prevent MAHI user to drop EMP table:

connect as sys user

sql>CREATE OR REPLACE TRIGGER trigger_prevent_drop BEFORE DROP ON DATABASE
BEGIN
IF ora_dict_obj_type = 'TABLE'
AND ora_dict_obj_owner = 'MAHI'
AND ora_login_user = 'MAHI'
AND ora_dict_obj_name='EMP'
THEN
raise_application_error (-20000, 'YOU CAN NOT DROP EMP TABLE!');
END IF;
END;
/

Trigger created.

then login as mahi user and try to drop emp table

SQL> conn mahi
Enter password:
Connected.
SQL> drop table emp;
drop table emp
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-20000: YOU CAN NOT DROP EMPLOYEES TABLE!
ORA-06512: at line 7

Creating a simulation of block corruption and recovering it through RMAN in linux

1.Before proceeding with block corruption simulation take the full database backup along with full archive log through rman
[oracle@oralinux ~]$ rman target sys/sys
RMAN>backup database plus archive log ;

2.create a table named mytab for testing purpose
SQL> create table mytab tablespace users as select * from tab;
Table created.
SQL> select count(*) from mytab;
COUNT(*)
----------
3645

3.Generate script for corrupting the block
SQL> set heading off
SQL> set lines 113
SELECT 'dd of=' f.file_name ' bs=8192 conv=notrunc seek='
to_number(S.HEADER_BLOCK + 1) ' << EOF',
'CORRUPT corrupt CORRUPT corrupt CORRUPT corrupt CORRUPT corrupt CORRUPT CORRUPT CORRUPT CORRUPT corrupt corrupt ',
'EOF'
FROM DBA_SEGMENTS S, dba_data_files f, dba_tables t
WHERE f.tablespace_name = t.tablespace_name
and S.SEGMENT_NAME = t.table_name
and t.table_name = 'MYTAB'
and S.OWNER = t.owner
and t.owner = 'SYS';

The output will be something like this

dd of=/oracle/oracle10/oradata/linx/users01.dbf bs=8192 conv=notrunc seek=420 << EOF CORRUPT corrupt CORRUPT corrupt CORRUPT corrupt CORRUPT corrupt CORRUPT CORRUPT CORRUPT CORRUPT corrupt corrupt EOF SQL> exit

4.Copy the dd command generated and execute from shell prompt
[oracle@oralinux dbs]$ dd of=/oracle/oracle10/oradata/linx/users01.dbf bs=8192 conv=notrunc seek=420 << EOF > CORRUPT corrupt CORRUPT corrupt CORRUPT corrupt CORRUPT corrupt CORRUPT CORRUPT CORRUPT CORRUPT corrupt corrupt
> EOF
0+1 records in
0+1 records out
112 bytes (112 B) copied, 0.000115064 seconds, 973 kB/s
[oracle@oralinux dbs]$

SQL> select count(*) from mytab;
COUNT(*)
----------
3645
SQL> alter system flush buffer_cache;
System altered.
SQL> /
System altered.
SQL> /
System altered.
SQL> select count(*) from mytab;
select count(*) from mytab
*ERROR at line 1:
ORA-01578: ORACLE data block corrupted (file # 4, block # 420)
ORA-01110: data file 4: '/oracle/oracle10/oradata/linx/users01.dbf'
SQL>

Now check the dynamic view $database_block_corruption for any logical block corruption
SQL> select * from v$database_block_corruption;
no rows selected
SQL>

From my experience i observed that this view will get populated during the backup validation
Through rman , now validate the backup by following command.

RMAN> backup validate check logical database archivelog all;
Starting backup at 03-FEB-12
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=159 devtype=DISK
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
input datafile fno=00001 name=/oracle/oracle10/oradata/linx/system01.dbf
input datafile fno=00003 name=/oracle/oracle10/oradata/linx/sysaux01.dbf
input datafile fno=00005 name=/oracle/oracle10/oradata/linx/example01.dbf
input datafile fno=00002 name=/oracle/oracle10/oradata/linx/undotbs01.dbf
input datafile fno=00004 name=/oracle/oracle10/oradata/linx/users01.dbf
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:25
channel ORA_DISK_1: starting archive log backupset
channel ORA_DISK_1: specifying archive log(s) in backup set
input archive log thread=1 sequence=11 recid=1 stamp=774233518
input archive log thread=1 sequence=12 recid=2 stamp=774233578
input archive log thread=1 sequence=13 recid=3 stamp=774233871
input archive log thread=1 sequence=14 recid=4 stamp=774233952
input archive log thread=1 sequence=15 recid=5 stamp=774234059
input archive log thread=1 sequence=16 recid=6 stamp=774234160
input archive log thread=1 sequence=17 recid=7 stamp=774234573
input archive log thread=1 sequence=18 recid=8 stamp=774234575
input archive log thread=1 sequence=19 recid=9 stamp=774234730
input archive log thread=1 sequence=20 recid=10 stamp=774234811
input archive log thread=1 sequence=21 recid=11 stamp=774234842
input archive log thread=1 sequence=22 recid=12 stamp=774234939
input archive log thread=1 sequence=23 recid=13 stamp=774235147
input archive log thread=1 sequence=24 recid=14 stamp=774235192
input archive log thread=1 sequence=25 recid=15 stamp=774235215
input archive log thread=1 sequence=26 recid=16 stamp=774235261
input archive log thread=1 sequence=27 recid=17 stamp=774235294
input archive log thread=1 sequence=28 recid=18 stamp=774235334
input archive log thread=1 sequence=29 recid=19 stamp=774236122
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:02
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
including current control file in backupset
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:02
Finished backup at 03-FEB-12
RMAN>

SQL> select * from v$database_block_corruption;
FILE# BLOCK# BLOCKS CORRUPTION_CHANGE# CORRUPTIO
---------- ---------- ---------- ------------------ ---------
4 420 1 0 CORRUPT

Note:-Starting with Oracle 9i we can use RMANto check a database for both physically and logically corrupt blocks.

5.Now recover the corrupted block by the following command
Rman > blockrecover datafile 4 block 420;
you can also do
rman > blockrecover corruption list;  (all blocks from v$database_block_corruption)
I closed the window before copying the output of this command , however rman will
Recover the block corruption and finally it will goes through media recovery from the archive log files .
You can see the view v$database_block_corruption still contain the information about the corrupted block , and it will vanish when you will run the ‘backup validate check logical database archivelog all;’ command again .

dba_data_files , dba_segments and dba_free_space

Dba_data_files:
The dba_data_files means total size of the data file. The data file size is total number of the dba_free_space + dba_segments.
SQL> select tablespace_name,sum(bytes)/(1024*1024*1024) from dba_data_files group by tablespace_name order by 1;

This example display the total size of the data file:
SQL> select sum(bytes)/(1024*1024*1024) from dba_data_files;
SUM(BYTES)/(1024*1024*1024)
---------------------------
6817.83142

Dba_segments:
The dba_segments means used size of the data file.
SQL> select tablespace_name,sum(bytes)/(1024*1024*1024) from dba_segments group by tablespace_name order by 1;
This example display the total size of the segments:
SQL> select sum(bytes)/(1024*1024*1024) from dba_segments;
SUM(BYTES)/(1024*1024*1024)
---------------------------
5273.55779

Dba_free_space:
The dba_free_space means free size of the data file.
SQL> select tablespace_name,sum(bytes)/(1024*1024*1024)from dba_free_space group by tablespace_name order by 1;

This example display the total size of the free space:
SQL> select sum(bytes)/(1024*1024*1024) from dba_free_space;
SUM(BYTES)/(1024*1024*1024)
---------------------------
1516.29922

Instead of using Dba_data_files , Dba_segments and Dba_free_space you can also use sm$ts_avail , sm$ts_used , sm$ts_free respectively.
As I mentioned earlier sum of dba_free_space and dba_segments should meet dba_data_files . But from our observation it can not meet our requirement (1516.29922+5273.55779=6789.85 only )
As I googled why it Happen, I got some thing like this.
First 8 blocks of each datafiles were not used ( may be some header information is stored ) Because their entries were neither present in the dba_extents nor in dba_free_spaces..
which lead to the difference of few KBs
[dba_data_file - ( dba_segments + dba_free_space ) ] -> few KBs
But for production database the difference is in GBs ..so the above conclusion is not the only way which is leading to the difference.

ORA-02020: too many database links in use

Each session in your database has a limited number of database links that can be opened at the same time.If you access a database link in a session, then the link remains open until you close the session.

Cause for ora-02020: The current session has exceeded the INIT parameter 'open_links' maximum.

Action: Increase the open_links limit, or free up some open links by committing or rolling back the transaction and canceling open cursors that reference remote databases.

OPEN_LINKS cannot be modified in real-time, so you will have to change it in the spfile or pfile and bounce the database.

SQL> show parameter open_links

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
open_links integer 4
open_links_per_instance integer 4

SQL>alter system set open_links=10 scope=spdile;

Then bounce the database

NOTE:You can also try to close the dblink if possible

SQL>ALTER SESSION CLOSE DATABASE LINK linkname;

If you are not sure how many database links are opened up concurrently by your session's database application, you can query v$dblink.

SQL> select in_transaction, count(*) from v$dblink group by in_transaction;

IN_ COUNT(*)
--- ----------
YES 1