OUI-67076: opatch lsinventory Error

Faced an issue with command 'opatch lsinventory' and reports below error in log file.

Cause: OUI-67076:OracleHomeInventory was not able to create a lock file, probably due to a failed OPatch Session. The loaded inventory might not show correctly what you have in the Oracle Home.

Please check below 'opatch lsinventory' output.

$ $ORACLE_HOME/OPatch/opatch lsinventory
Oracle Interim Patch Installer version 11.2.0.3.0
Copyright (c) 2012, Oracle Corporation.  All rights reserved.
Oracle Home       : /u01/oracle/product/11.2.0
Central Inventory : /u01/oracle/product/oraInventory
   from           : /u01/oracle/product/11.2.0/oraInst.loc
OPatch version    : 11.2.0.3.0
OUI version       : 11.2.0.3.0
Log file location : /u01/oracle/product/11.2.0/cfgtoollogs/opatch/opatch2014-12-24_02-41-49AM_1.log
OracleHomeInventory was not able to create a lock file, probably due to a failed OPatch Session. The loaded inventory might not show correctly what you have in the Oracle Home.
Lsinventory Output file location : /u01/oracle/product/11.2.0/cfgtoollogs/opatch/lsinv/lsinventory2014-12-24_02-41-49AM.txt
..............................
..............................
OPatch Session completed with warnings.
OPatch completed with warnings.
$
$ more /u01/oracle/product/11.2.0/cfgtoollogs/opatch/opatch2014-12-24_02-41-49AM_1.log
[ 2:41:49 AM]    OPatch invoked as follows: 'lsinventory -invPtrLoc /u01/oracle/product/11.2.0/oraInst.loc '
[ 2:41:49 AM]    OUI-67077:
                             Oracle Home       : /u01/oracle/product/11.2.0
                             Central Inventory : /u01/oracle/product/oraInventory
                                from           : /u01/oracle/product/11.2.0/oraInst.loc
                             OPatch version    : 11.2.0.3.0
                             OUI version       : 11.2.0.3.0
                             OUI location      : /u01/oracle/product/11.2.0/oui
                             Log file location : /u01/oracle/product/11.2.0/cfgtoollogs/opatch/opatch2014-12-24_02-41-49AM_1.log
[2:41:49 AM]    Patch history file: /u01/oracle/product/11.2.0/cfgtoollogs/opatch/opatch_history.txt
[2:41:49 AM]    Starting LsInventorySession at Wed Dec 24 02:41:49 PST 2014
[2:41:49 AM]    OUI-67076:OracleHomeInventory was not able to create a lock file, probably due to a failed OPatch Session. The loaded inventory might not show correctly what you have in the Oracle Home.
[ 2:41:50 AM]    Lsinventory Output file location : /u01/oracle/product/11.2.0/cfgtoollogs/opatch/lsinv/lsinventory2014-12-24_02-41-49AM.txt
.................................
.................................
$

Solution:

1) Take a backup of $ORACLE_HOME/.patch_storage directory
$ cp -R $ORACLE_HOME/.patch_storage $ORACLE_HOME/.patch_storage_backup

2) Remove $ORACLE_HOME/.patch_storage/patch_locked
$ rm $ORACLE_HOME/.patch_storage/patch_locked

3) Create empty file $ORACLE_HOME/.patch_storage/patch_free
$ touch $ORACLE_HOME/.patch_storage/patch_free

4) Verify that "opatch lsinventory" no longer reports the error
$ opatch lsinventory

Issue fixed now, check below output.

$ $ORACLE_HOME/OPatch/opatch lsinventory
Oracle Interim Patch Installer version 11.2.0.3.0
Copyright (c) 2012, Oracle Corporation.  All rights reserved.
Oracle Home       : /u01/oracle/product/11.2.0
Central Inventory : /u01/oracle/product/oraInventory
   from           : /u01/oracle/product/11.2.0/oraInst.loc
OPatch version    : 11.2.0.3.0
OUI version       : 11.2.0.3.0
Log file location : /u01/oracle/product/11.2.0/cfgtoollogs/opatch/opatch2014-12-26_01-38-24AM_1.log
Lsinventory Output file location : /u01/oracle/product/11.2.0/cfgtoollogs/opatch/lsinv/lsinventory2014-12-26_01-38-24AM.txt
--------------------------------------------------------------------------------
Installed Top-level Products (2):
...........................
...........................
..........................
OPatch succeeded.
$

Thats it. Hope this will help you.. :)

Best Regards,
Chowdari.

Oracle Database 12cR1 installation (12.1.0.2) on linux

Follow below steps to install oracle database 12c Release 1 (12.1.0.2) software on Linux machine.

Prerequisite: Oracle Enterprise Linux 5.7 Installation

Download below selected Oracle 12.1.0.2 software (Two files) from edelivery.

Copy database software files to  /u01/1212SW location.

Start 1212DB VMachine

Follow below steps to install oracle 12cR1 database software and creation of ORCL database:

Create required group and User:

[root@1212db 1212SW]# groupadd dba
[root@1212db 1212SW]# useradd -g dba oracle
[root@1212db 1212SW]# passwd oracle
Changing password for user oracle.
New UNIX password:
BAD PASSWORD: it is based on a dictionary word
Retype new UNIX password:
passwd: all authentication tokens updated successfully.
[root@1212db 1212SW]#

Create directories and give folder permissions in which Oracle software will be installed:

Unzip the 12c database software:

Before running runInstaller open a new terminal and run xhost + as a root user:

Start Installation with run runInstaller:

 Uncheck the Updates box, Click Next and then click yes

Select 'Create and configure a database' option and click Next

Select 'Desktop class' option and click Next

Here select oracle base, software location, datafile location, database edition, character set, DB name and give password then click NEXT

 Verifying all prerequisites.

 After successful of prerequisite verification then click Install  

Open new terminal and log in as root and run below root.sh script

After run the root.sh script then click OK 

Click OK to Continue

Database installation successful wizard.

Now check the database version.

That's it..Completed Oracle 12cR1 (12.1.0.2) database installation and created new ORCL database.

Hope this will help you!!! :)

Best Regards,

Chowdari

Related articles:  Installation of Oracle Database 11gR2 on Oracle Enterprise Linux 5.7

Cloning a New Virtual Machine from Existing VM Box

Script: Display SGA Statistics in Oracle Database

Below script is useful for find database SGA statistics.

rem -----------------------------------------------------------------------
rem Filename:   sga_stat.sql
rem Purpose:    Display database SGA statistics
rem created by: http://mbc-dba.blogspot.com/
rem -----------------------------------------------------------------------
prompt Recommendations:
prompt =======================
prompt* SQL Cache Hit rate ratio should be above 90%, if not then increase the Shared Pool Size.
prompt* Dict Cache Hit rate ratio should be above 85%, if not then increase the Shared Pool Size.
prompt* Buffer Cache Hit rate ratio should be above 90%, if not then increase the DB Block Buffer value.
prompt* Redo Log space requests should be less than 0.5% of redo entries, if not then increase log buffer.
prompt* Redo Log space wait time should be near to 0.
prompt
set serveroutput ON
DECLARE
      libcac number(10,2);
      rowcac number(10,2);
      bufcac number(10,2);
      redlog number(10,2);
      redoent number;
      redowaittime number;
BEGIN
select value into redlog from v$sysstat where name = 'redo log space requests';
select value into redoent from v$sysstat where name = 'redo entries';
select value into redowaittime from v$sysstat where name = 'redo log space wait time';
select 100*(sum(pins)-sum(reloads))/sum(pins) into libcac from v$librarycache;
select 100*(sum(gets)-sum(getmisses))/sum(gets) into rowcac from v$rowcache;
select 100*(cur.value + con.value - phys.value)/(cur.value + con.value) into bufcac
from v$sysstat cur,v$sysstat con,v$sysstat phys,v$statname ncu,v$statname nco,v$statname nph
where cur.statistic# = ncu.statistic#
        and ncu.name = 'db block gets'
        and con.statistic# = nco.statistic#
        and nco.name = 'consistent gets'
        and phys.statistic# = nph.statistic#
        and nph.name = 'physical reads';
dbms_output.put_line('SGA CACHE STATISTICS');
dbms_output.put_line('********************');
dbms_output.put_line('SQL Cache Hit rate = '||libcac);
dbms_output.put_line('Dict Cache Hit rate = '||rowcac);
dbms_output.put_line('Buffer Cache Hit rate = '||bufcac);
dbms_output.put_line('Redo Log space requests = '||redlog);
dbms_output.put_line('Redo Entries = '||redoent);
dbms_output.put_line('Redo log space wait time = '||redowaittime);
if
 libcac < 90  then dbms_output.put_line('*** HINT: Library Cache too low! Increase the Shared Pool Size.');
END IF;
if
 rowcac < 85  then dbms_output.put_line('*** HINT: Row Cache too low! Increase the Shared Pool Size.');
END IF;
if
 bufcac < 90  then dbms_output.put_line('*** HINT: Buffer Cache too low! Increase the DB Block Buffer value.');
END IF;
if
 redlog > 1000000 then dbms_output.put_line('*** HINT: Log Buffer value is rather low!');
END IF;
END;
/

Sample Output:

SGA CACHE STATISTICS
********************
SQL Cache Hit rate = 99.99
Dict Cache Hit rate = 99.62
Buffer Cache Hit rate = 33.6
Redo Log space requests = 649618
Redo Entries = 18292985697
Redo log space wait time = 2153535
*** HINT: Buffer Cache too low! Increase the DB Block Buffer value.


PL/SQL procedure successfully completed.

SQL>

Hope this will help you.. :)

Best Regards,
Chowdari

Script: Check RMAN Backup job Status

Below script is very useful for checking RMAN backup job status.

set pagesize 1000
set lin 400
COL COMPRESSION format 99.9
COL Input_GB FORMAT 99999.9999
COL Output_GB FORMAT 99999.99
COL Time FORMAT a10
alter session set nls_date_format='YYYY-MM-DD HH24:MI';
SELECT
        STATUS,
        START_TIME,
        input_type Type,
       COMPRESSION_RATIO Compression,
       INPUT_BYTES/1024/1024/1024 Input_GB ,
       OUTPUT_BYTES/1024/1024/1024 Output_GB,
       TIME_TAKEN_DISPLAY Time
FROM   V$RMAN_BACKUP_JOB_DETAILS
ORDER BY START_TIME;

Sample Output:

STATUS                START_TIME                      TYPE          COMPRESSION    INPUT_GB   OUTPUT_GB    TIME
----------------------- -------------------------------------           -------------     ------------------------ ----------------   -------------------    ----------
COMPLETED      2014-11-13 22:27                    DB INCR         24.5                9366.1675    381.88            14:29:11
COMPLETED      2014-11-14 22:27                    DB INCR         35.1                8868.0660    252.77            15:46:57
RUNNING             2014-11-16 00:18                   DB INCR          5.6                  1918.7863    339.77            08:35:29

Hope this will help you.. :)

Best Regards,
Chowdari

impdp - ORA-31640 ORA-31693 ORA-19505 ORA-27037

When I am trying to import schema, The impdp is failed to open dump file. see below script I am using.

$ cat parfile.par
USERID='/ as sysdba'
DIRECTORY=IMP_DP
DUMPFILE=SMARTSUPPY_SCHEMA.dmp
logfile=SMARTSUPPY_SCHEMA_09nov2014.log
parallel=10
SCHEMAS='SMARTSUPPY_SCHEMA'

I got below error while import..

ORA-31693: Table data object "SMARTSUPPY_SCHEMA"."BPM_TRANS_90000":"P297" failed to load/unload and is being skipped due to error:
ORA-31640: unable to open dump file "/export/APSDBSR-1351/SMARTSUPPY_SCHEMA.dmp" for read
ORA-19505: failed to identify file "/export/APSDBSR-1351/SMARTSUPPY_SCHEMA.dmp"
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
. . imported "SMARTSUPPY_SCHEMA"."BPM_TRANS_90000":"P315"      265.3 MB 1484775 rows
. . imported "SMARTSUPPY_SCHEMA"."TRC_HOP_T_90000":"P139"      219.7 MB 1402774 rows

The issue is due to I am using PARALLEL=10, so the dump file directory not being accessible from all nodes in the RAC. When we use PARALLEL > 1 option the child thread will be started in other node to complete the job faster, when job started in other node the dump file can't access the file. Due to this we receive this error. To fix the issue either you place the dump file to be accessed from all the nodes in that RAC environment or use cluster=N option.

Added cluster=N parameter in parfile and started the import again.

$ cat parfile.par
USERID='/ as sysdba'
DIRECTORY=IMP_DP
DUMPFILE=SMARTSUPPY_SCHEMA.dmp
logfile=SMARTSUPPY_SCHEMA_09nov2014.log
parallel=10
SCHEMAS='SMARTSUPPY_SCHEMA'
CLUSTER=N
$

$ nohup impdp parfile=parfile.par &
[1] 27620
> nohup: ignoring input and appending output to `nohup.out'
$
$ jobs -l
[1]+ 27620 Running                 nohup impdp parfile=parfile.par &
$

$ tail -f SMARTSUPPY_SCHEMA_09nov2014.log
Master table "SYS"."SYS_IMPORT_SCHEMA_03" successfully loaded/unloaded
Starting "SYS"."SYS_IMPORT_SCHEMA_03":  /******** AS SYSDBA parfile=parfile.par
Processing object type SCHEMA_EXPORT/USER
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/TABLESPACE_QUOTA
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/SEQUENCE/SEQUENCE
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
. . imported "SMARTSUPPY_SCHEMA"."BPM_COMPONENTS_90000":"P475"  1.956 GB 6711857 rows
. . imported "SMARTSUPPY_SCHEMA"."BPM_COMPONENTS_90000":"P480"  1.965 GB 6794127 rows
. . imported "SMARTSUPPY_SCHEMA"."BPM_COMPONENTS_90000":"P474"  1.958 GB 6727517 rows
. . imported "SMARTSUPPY_SCHEMA"."BPM_COMPONENTS_90000":"P479"  1.921 GB 6616816 rows
. . imported "SMARTSUPPY_SCHEMA"."BPM_COMPONENTS_90000":"P478"  1.903 GB 6512491 rows
. . imported "SMARTSUPPY_SCHEMA"."BPM_COMPONENTS_90000":"P481"  1.909 GB 6618578 rows

Hope this will help you.. :)

Best Regards,
Chowdari

Script: Oracle Database Growth per Month

Here you can find two scripts those are database growth per month and tablespace level database growth.

The below script lists the details of database growth per month:

select to_char(creation_time, 'MM-RRRR') "Month", sum(bytes)/1024/1024/1024 "Growth in GB"
from sys.v_$datafile
where to_char(creation_time,'RRRR')='2014'
group by to_char(creation_time, 'MM-RRRR')
order by  to_char(creation_time, 'MM-RRRR');

Sample output from the script:

Month                                                                       Growth in GB
------------------------------------------------------------      -----------------
05-2014                                                                       101.588867
06-2014                                                                       525.609375
07-2014                                                                             57.5
09-2014                                                                               10
10-2014                                                                       31.0976563
11-2014                                                                               52

Below script is useful for tablespace level database growth:

select b.tsname tablespace_name , MAX(b.used_size_mb) cur_used_size_mb , round(AVG(inc_used_size_mb),2)avg_increas_mb  

from ( SELECT a.days,a.tsname , used_size_mb , used_size_mb - LAG (used_size_mb,1) OVER ( PARTITION BY a.tsname ORDER BY a.tsname,a.days) inc_used_size_mb

from ( SELECT TO_CHAR(sp.begin_interval_time,'MM-DD-YYYY') days  ,ts.tsname ,MAX(round((tsu.tablespace_usedsize* dt.block_size )/(1024*1024),2)) used_size_mb

from dba_hist_tbspc_space_usage  tsu , dba_hist_tablespace_stat  ts ,dba_hist_snapshot  sp, dba_tablespaces  dt   where tsu.tablespace_id= ts.ts# 

AND tsu.snap_id = sp.snap_id

AND ts.tsname = dt.tablespace_name AND sp.begin_interval_time > sysdate-7

GROUP BY TO_CHAR(sp.begin_interval_time,'MM-DD-YYYY'), ts.tsname 

ORDER BY ts.tsname, days ) a ) b GROUP BY b.tsname ORDER BY b.tsname;

Sample output from the script:

TABLESPACE_NAME                CUR_USED_SIZE_MB AVG_INCREAS_MB

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

DW_AGG                                       8150.31                                 46.94

DW_DAC_REP_DATA                    858.31                                   3.29

DW_DIM_DATA                             7078.81                                 6

DW_DIM_INDX                              3229.69                                3.89

DW_DIM_STG                                1202.38                                -9.61

DW_FACT_DATA                          304706.94                            894.51

DW_FACT_INDX                           32227.81                             183.04

DW_FACT_STG                             5483.81                                120.7

DW_INFA_DOMAIN_DATA              1                                      0

DW_INFA_REP_DATA                 3617.88                                2.43

DW_OTHER                                  14314.88                              83.68

DW_OTHER_INDX                        203.81                                  .71

PRD_BIPLATFORM                      7493                                     64.57

PRD_MDS                                        13.44                                    0

SYSAUX                                       7097.75                                 -3.94

SYSTEM                                       2408.31                                10.43

UNDOTBS1                                 62488.13                               7074.66

UNDOTBS2                                 13088.38                              -866.43

UNDOTBS3                                  81902                                   10326

USERS                                          2015.13                                  -2.68

Hope this scripts will help you. :)

Best Regards,

Chowdari

Adding disk to ASM disk group in oracle 11g

Following steps will help you to add disks to oracle ASM disk group.

Below query will identify disk groups information to add disks

SQL> SET LINESIZE  145
SQL> SET PAGESIZE  9999
SQL> SET VERIFY    off
SQL> COLUMN group_name             FORMAT a20           HEAD 'Disk Group|Name'
SQL> COLUMN sector_size            FORMAT 99,999        HEAD 'Sector|Size'
SQL> COLUMN block_size             FORMAT 99,999        HEAD 'Block|Size'
SQL> COLUMN allocation_unit_size   FORMAT 999,999,999   HEAD 'Allocation|Unit Size'
SQL> COLUMN state                  FORMAT a11           HEAD 'State'
SQL> COLUMN type                   FORMAT a6            HEAD 'Type'
SQL> COLUMN total_mb               FORMAT 999,999,999   HEAD 'Total Size (MB)'
SQL> COLUMN used_mb                FORMAT 999,999,999   HEAD 'Used Size (MB)'
SQL> COLUMN pct_used               FORMAT 999.99        HEAD 'Pct. Used'
SELECT
SQL>   2      name                                     group_name
  , sector_size                              sector_size
  3    4    , block_size                               block_size
  5    , allocation_unit_size                     allocation_unit_size
  6    , state                                    state
  7    , type                                     type
  8    , total_mb                                 total_mb
  9    , (total_mb - free_mb)                     used_mb
  , ROUND((1- (free_mb / total_mb))*100, 2)  pct_used
 10   11  FROM
 12      v$asm_diskgroup
 13  ORDER BY
 14      name
 15  /

Disk Group            Sector   Block   Allocation
Name                    Size    Size    Unit Size State       Type   Total Size (MB) Used Size (MB) Pct. Used
-------------------- ------- ------- ------------ ----------- ------ --------------- -------------- ---------
MKRSTDATA                512   4,096    4,194,304 MOUNTED     EXTERN         819,168        726,848     88.73
MKRSTFRA                 512   4,096    4,194,304 MOUNTED     EXTERN         819,168            192       .02
MKRSTREDO                512   4,096    4,194,304 MOUNTED     EXTERN          20,464          4,344     21.23
OCRVOTEDG                512   4,096    4,194,304 MOUNTED     NORMAL          15,348          1,040      6.78

Find ASM disk group number with using below query

SQL> select GROUP_NUMBER,NAME,STATE,TOTAL_MB,FREE_MB from  v$asm_diskgroup where name='MKRSTDATA';

GROUP_NUMBER NAME                           State       Total Size (MB)    FREE_MB
------------ ------------------------------ ----------- --------------- ----------
           2 MKRSTDATA                      MOUNTED             819,168      92320

Following query will help to find existing disk names and disk sizes for MKRSTDATA disk group

SQL> set lines 200 pages 200
col path for a30
select GROUP_NUMBER,HEADER_STATUS,STATE,TOTAL_MB,FREE_MB,PATH from v$asm_disk where GROUP_NUMBER=2;SQL> SQL>

GROUP_NUMBER HEADER_STATU State       Total Size (MB)    FREE_MB PATH
------------ ------------ ----------- --------------- ---------- ------------------------------
           2 MEMBER       NORMAL              102,396      11556 /dev/mapper/t2_fra07p1
           2 MEMBER       NORMAL              102,396      11544 /dev/mapper/t1_data05p1
           2 MEMBER       NORMAL              102,396      11536 /dev/mapper/t1_data06p1
           2 MEMBER       NORMAL              102,396      11532 /dev/mapper/t1_data01p1
           2 MEMBER       NORMAL              102,396      11548 /dev/mapper/t1_data07p1
           2 MEMBER       NORMAL              102,396      11532 /dev/mapper/t1_data03p1
           2 MEMBER       NORMAL              102,396      11536 /dev/mapper/t1_data02p1
           2 MEMBER       NORMAL              102,396      11536 /dev/mapper/t1_data04p1

8 rows selected.

Find available disks information

ASMCMD [+] > lsdsk
Path
/dev/mapper/t1_data01p1
/dev/mapper/t1_data02p1
/dev/mapper/t1_data03p1
/dev/mapper/t1_data04p1
/dev/mapper/t1_data05p1
/dev/mapper/t1_data06p1
/dev/mapper/t1_data07p1
/dev/mapper/t1_redo01p1
/dev/mapper/t1_redo02p1
/dev/mapper/t1_redo03p1
/dev/mapper/t1_redo04p1
/dev/mapper/t1_vote1p1
/dev/mapper/t1_vote2p1
/dev/mapper/t1_vote3p1
/dev/mapper/t2_fra01p1
/dev/mapper/t2_fra02p1
/dev/mapper/t2_fra03p1
/dev/mapper/t2_fra04p1
/dev/mapper/t2_fra05p1
/dev/mapper/t2_fra06p1
/dev/mapper/t2_fra07p1
ASMCMD [+] >

So here will add disks on MKRSTDATA disk group and group number 2. The size of the each disks are 100GB. So here I am adding additional 500GB to MKRSTDATA disk group. Request unix team to provide 5*100GB disks. After getting disks from Unix team verify CANDIDATE disks information.

Following query will help us to find CANDIDATE disks,

SQL> set lines 200 pages 200
col path for a30
select GROUP_NUMBER,HEADER_STATUS,STATE,TOTAL_MB,FREE_MB,PATH from v$asm_disk;SQL> SQL>

GROUP_NUMBER HEADER_STATU State       Total Size (MB)    FREE_MB PATH
------------ ------------ ----------- --------------- ---------- ------------------------------
           0 CANDIDATE    NORMAL                    0          0 /dev/mapper/t1_data12p1
           0 CANDIDATE    NORMAL                    0          0 /dev/mapper/t1_data11p1
           0 CANDIDATE    NORMAL                    0          0 /dev/mapper/t1_data10p1
           0 CANDIDATE    NORMAL                    0          0 /dev/mapper/t1_data09p1
           0 CANDIDATE    NORMAL                    0          0 /dev/mapper/t1_data08p1
           0 CANDIDATE    NORMAL                    0          0 /dev/mapper/t2_fra08p1
           3 MEMBER       NORMAL              102,396     102372 /dev/mapper/t2_fra06p1
           2 MEMBER       NORMAL              102,396      11556 /dev/mapper/t2_fra07p1
           3 MEMBER       NORMAL              102,396     102368 /dev/mapper/t2_fra04p1
           3 MEMBER       NORMAL              102,396     102376 /dev/mapper/t2_fra05p1
           3 MEMBER       NORMAL              102,396     102368 /dev/mapper/t2_fra03p1
           1 MEMBER       NORMAL                5,116       4772 /dev/mapper/t1_vote3p1
           1 MEMBER       NORMAL                5,116       4768 /dev/mapper/t1_vote1p1
           1 MEMBER       NORMAL                5,116       4768 /dev/mapper/t1_vote2p1
           4 MEMBER       NORMAL                5,116       4016 /dev/mapper/t1_redo01p1
           4 MEMBER       NORMAL                5,116       4032 /dev/mapper/t1_redo02p1
           4 MEMBER       NORMAL                5,116       4036 /dev/mapper/t1_redo04p1
           3 MEMBER       NORMAL              204,792     204744 /dev/mapper/t2_fra02p1
           4 MEMBER       NORMAL                5,116       4036 /dev/mapper/t1_redo03p1
           3 MEMBER       NORMAL              204,792     204748 /dev/mapper/t2_fra01p1
           2 MEMBER       NORMAL              102,396      11544 /dev/mapper/t1_data05p1
           2 MEMBER       NORMAL              102,396      11536 /dev/mapper/t1_data06p1
           2 MEMBER       NORMAL              102,396      11532 /dev/mapper/t1_data01p1
           2 MEMBER       NORMAL              102,396      11548 /dev/mapper/t1_data07p1
           2 MEMBER       NORMAL              102,396      11532 /dev/mapper/t1_data03p1
           2 MEMBER       NORMAL              102,396      11536 /dev/mapper/t1_data02p1
           2 MEMBER       NORMAL              102,396      11536 /dev/mapper/t1_data04p1

27 rows selected.

SQL> select GROUP_NUMBER,NAME,STATE,TOTAL_MB,FREE_MB from  v$asm_diskgroup where name='MKRSTDATA';

GROUP_NUMBER NAME                           State       Total Size (MB)    FREE_MB
------------ ------------------------------ ----------- --------------- ----------
           2 MKRSTDATA                      MOUNTED             819,168      92320

Here I am creating dummy disk group with provided disks because want to find out is they added 100GB of each disk or not.

Following query to create new ASM disk group

SQL> create diskgroup dummy external redundancy disk 

'/dev/mapper/t1_data08p1','/dev/mapper/t1_data09p1','/dev/mapper/t1_data10p1','/dev/mapper/t1_data11p1','/dev/mapper/t1_data12p1';

Diskgroup created.

SQL>
SQL> alter diskgroup dummy rebalance power 6;

Diskgroup altered.

SQL>

Note: The maximum power for a rebalancing operation on an ASM instance. The valid values range from 1 to 11, with 1 being the default. The higher the limit the more resources are allocated resulting in faster rebalancing operations. This value is also used as the default when the POWER  clause is omitted from a rebalance operation.

Below query shows newly added disk group with disks information, Confirmed that Unix team added 100GB of disks each.

SQL> SQL> set lines 200 pages 200
col path for a30
select GROUP_NUMBER,HEADER_STATUS,STATE,TOTAL_MB,FREE_MB,PATH from v$asm_disk;SQL> SQL>

GROUP_NUMBER HEADER_STATU State       Total Size (MB)    FREE_MB PATH
------------ ------------ ----------- --------------- ---------- ------------------------------
           0 CANDIDATE    NORMAL                    0          0 /dev/mapper/t2_fra08p1
           3 MEMBER       NORMAL              102,396     102372 /dev/mapper/t2_fra06p1
           2 MEMBER       NORMAL              102,396      11556 /dev/mapper/t2_fra07p1
           3 MEMBER       NORMAL              102,396     102368 /dev/mapper/t2_fra04p1
           3 MEMBER       NORMAL              102,396     102376 /dev/mapper/t2_fra05p1
           3 MEMBER       NORMAL              102,396     102368 /dev/mapper/t2_fra03p1
           1 MEMBER       NORMAL                5,116       4772 /dev/mapper/t1_vote3p1
           1 MEMBER       NORMAL                5,116       4768 /dev/mapper/t1_vote1p1
           1 MEMBER       NORMAL                5,116       4768 /dev/mapper/t1_vote2p1
           4 MEMBER       NORMAL                5,116       4016 /dev/mapper/t1_redo01p1
           4 MEMBER       NORMAL                5,116       4032 /dev/mapper/t1_redo02p1
           4 MEMBER       NORMAL                5,116       4036 /dev/mapper/t1_redo04p1
           3 MEMBER       NORMAL              204,792     204744 /dev/mapper/t2_fra02p1
           4 MEMBER       NORMAL                5,116       4036 /dev/mapper/t1_redo03p1
           3 MEMBER       NORMAL              204,792     204748 /dev/mapper/t2_fra01p1
           2 MEMBER       NORMAL              102,396      11544 /dev/mapper/t1_data05p1
           2 MEMBER       NORMAL              102,396      11536 /dev/mapper/t1_data06p1
           2 MEMBER       NORMAL              102,396      11532 /dev/mapper/t1_data01p1
           2 MEMBER       NORMAL              102,396      11548 /dev/mapper/t1_data07p1
           2 MEMBER       NORMAL              102,396      11532 /dev/mapper/t1_data03p1
           2 MEMBER       NORMAL              102,396      11536 /dev/mapper/t1_data02p1
           2 MEMBER       NORMAL              102,396      11536 /dev/mapper/t1_data04p1
           5 MEMBER       NORMAL              102,398     102387 /dev/mapper/t1_data12p1
           5 MEMBER       NORMAL              102,398     102386 /dev/mapper/t1_data11p1
           5 MEMBER       NORMAL              102,398     102386 /dev/mapper/t1_data10p1
           5 MEMBER       NORMAL              102,398     102386 /dev/mapper/t1_data09p1
           5 MEMBER       NORMAL              102,398     102387 /dev/mapper/t1_data08p1

27 rows selected.

Dropping dummy ASM disk group

SQL> drop diskgroup dummy;

Diskgroup dropped.

SQL> 
SQL>set lines 200 pages 200
col path for a30
select GROUP_NUMBER,HEADER_STATUS,STATE,TOTAL_MB,FREE_MB,PATH from v$asm_disk;SQL> SQL>

GROUP_NUMBER HEADER_STATU State       Total Size (MB)    FREE_MB PATH
------------ ------------ ----------- --------------- ---------- ------------------------------
           0 FORMER       NORMAL                    0          0 /dev/mapper/t1_data12p1
           0 FORMER       NORMAL                    0          0 /dev/mapper/t1_data11p1
           0 FORMER       NORMAL                    0          0 /dev/mapper/t1_data10p1
           0 FORMER       NORMAL                    0          0 /dev/mapper/t1_data09p1
           0 FORMER       NORMAL                    0          0 /dev/mapper/t1_data08p1
           0 CANDIDATE    NORMAL                    0          0 /dev/mapper/t2_fra08p1
           3 MEMBER       NORMAL              102,396     102372 /dev/mapper/t2_fra06p1
           2 MEMBER       NORMAL              102,396      11556 /dev/mapper/t2_fra07p1
           3 MEMBER       NORMAL              102,396     102368 /dev/mapper/t2_fra04p1
           3 MEMBER       NORMAL              102,396     102376 /dev/mapper/t2_fra05p1
           3 MEMBER       NORMAL              102,396     102368 /dev/mapper/t2_fra03p1
           1 MEMBER       NORMAL                5,116       4772 /dev/mapper/t1_vote3p1
           1 MEMBER       NORMAL                5,116       4768 /dev/mapper/t1_vote1p1
           1 MEMBER       NORMAL                5,116       4768 /dev/mapper/t1_vote2p1
           4 MEMBER       NORMAL                5,116       4016 /dev/mapper/t1_redo01p1
           4 MEMBER       NORMAL                5,116       4032 /dev/mapper/t1_redo02p1
           4 MEMBER       NORMAL                5,116       4036 /dev/mapper/t1_redo04p1
           3 MEMBER       NORMAL              204,792     204744 /dev/mapper/t2_fra02p1
           4 MEMBER       NORMAL                5,116       4036 /dev/mapper/t1_redo03p1
           3 MEMBER       NORMAL              204,792     204748 /dev/mapper/t2_fra01p1
           2 MEMBER       NORMAL              102,396      11544 /dev/mapper/t1_data05p1
           2 MEMBER       NORMAL              102,396      11536 /dev/mapper/t1_data06p1
           2 MEMBER       NORMAL              102,396      11532 /dev/mapper/t1_data01p1
           2 MEMBER       NORMAL              102,396      11548 /dev/mapper/t1_data07p1
           2 MEMBER       NORMAL              102,396      11532 /dev/mapper/t1_data03p1
           2 MEMBER       NORMAL              102,396      11536 /dev/mapper/t1_data02p1
           2 MEMBER       NORMAL              102,396      11536 /dev/mapper/t1_data04p1

27 rows selected.

SQL>

Now adding disks to existing MKRSTDATA disk group

SQL> alter diskgroup MKRSTDATA add disk '/dev/mapper/t1_data08p1','/dev/mapper/t1_data09p1','/dev/mapper/t1_data10p1','/dev/mapper/t1_data11p1','/dev/mapper/t1_data12p1'  rebalance power 11;

Diskgroup altered.

SQL> set lines 200 pages 200
col path for a30
select GROUP_NUMBER,HEADER_STATUS,STATE,TOTAL_MB,FREE_MB,PATH from v$asm_disk;SQL> SQL>

GROUP_NUMBER HEADER_STATU State       Total Size (MB)    FREE_MB PATH
------------ ------------ ----------- --------------- ---------- ------------------------------
           0 CANDIDATE    NORMAL                    0          0 /dev/mapper/t2_fra08p1
           3 MEMBER       NORMAL              102,396     102372 /dev/mapper/t2_fra06p1
           2 MEMBER       NORMAL              102,396      11864 /dev/mapper/t2_fra07p1
           3 MEMBER       NORMAL              102,396     102368 /dev/mapper/t2_fra04p1
           3 MEMBER       NORMAL              102,396     102376 /dev/mapper/t2_fra05p1
           3 MEMBER       NORMAL              102,396     102368 /dev/mapper/t2_fra03p1
           1 MEMBER       NORMAL                5,116       4772 /dev/mapper/t1_vote3p1
           1 MEMBER       NORMAL                5,116       4768 /dev/mapper/t1_vote1p1
           1 MEMBER       NORMAL                5,116       4768 /dev/mapper/t1_vote2p1
           4 MEMBER       NORMAL                5,116       4016 /dev/mapper/t1_redo01p1
           4 MEMBER       NORMAL                5,116       4032 /dev/mapper/t1_redo02p1
           4 MEMBER       NORMAL                5,116       4036 /dev/mapper/t1_redo04p1
           3 MEMBER       NORMAL              204,792     204744 /dev/mapper/t2_fra02p1
           4 MEMBER       NORMAL                5,116       4036 /dev/mapper/t1_redo03p1
           3 MEMBER       NORMAL              204,792     204748 /dev/mapper/t2_fra01p1
           2 MEMBER       NORMAL              102,396      11868 /dev/mapper/t1_data05p1
           2 MEMBER       NORMAL              102,396      11856 /dev/mapper/t1_data06p1
           2 MEMBER       NORMAL              102,396      11856 /dev/mapper/t1_data01p1
           2 MEMBER       NORMAL              102,396      11872 /dev/mapper/t1_data07p1
           2 MEMBER       NORMAL              102,396      11856 /dev/mapper/t1_data03p1
           2 MEMBER       NORMAL              102,396      11856 /dev/mapper/t1_data02p1
           2 MEMBER       NORMAL              102,396      11860 /dev/mapper/t1_data04p1
           2 MEMBER       NORMAL              102,396     101872 /dev/mapper/t1_data12p1
           2 MEMBER       NORMAL              102,396     101872 /dev/mapper/t1_data11p1
           2 MEMBER       NORMAL              102,396     101876 /dev/mapper/t1_data10p1
           2 MEMBER       NORMAL              102,396     101876 /dev/mapper/t1_data09p1
           2 MEMBER       NORMAL              102,396     101876 /dev/mapper/t1_data08p1

27 rows selected.

SQL>
SQL> SET LINESIZE  145
SQL> SET PAGESIZE  9999
SQL> SET VERIFY    off
SQL> COLUMN group_name             FORMAT a20           HEAD 'Disk Group|Name'
SQL> COLUMN sector_size            FORMAT 99,999        HEAD 'Sector|Size'
SQL> COLUMN block_size             FORMAT 99,999        HEAD 'Block|Size'
SQL> COLUMN allocation_unit_size   FORMAT 999,999,999   HEAD 'Allocation|Unit Size'
SQL> COLUMN state                  FORMAT a11           HEAD 'State'
COLUMN type                   FORMAT a6            HEAD 'Type'
SQL> SQL> COLUMN total_mb               FORMAT 999,999,999   HEAD 'Total Size (MB)'
SQL> COLUMN used_mb                FORMAT 999,999,999   HEAD 'Used Size (MB)'
SQL> COLUMN pct_used               FORMAT 999.99        HEAD 'Pct. Used'
SQL> SELECT
  2      name                                     group_name
  3    , sector_size                              sector_size
  4    , block_size                               block_size
  5    , allocation_unit_size                     allocation_unit_size
  , state                                    state
  6    7    , type                                     type
  8    , total_mb                                 total_mb
  9    , (total_mb - free_mb)                     used_mb
 10    , ROUND((1- (free_mb / total_mb))*100, 2)  pct_used
 11  FROM
 12      v$asm_diskgroup
 13  ORDER BY
 14      name
/ 15

Disk Group            Sector   Block   Allocation
Name                    Size    Size    Unit Size State       Type   Total Size (MB) Used Size (MB) Pct. Used
-------------------- ------- ------- ------------ ----------- ------ --------------- -------------- ---------
MKRSTDATA                512   4,096    4,194,304 MOUNTED     EXTERN       1,331,148        726,888     54.61
MKRSTFRA                 512   4,096    4,194,304 MOUNTED     EXTERN         819,168            192       .02
MKRSTREDO                512   4,096    4,194,304 MOUNTED     EXTERN          20,464          4,344     21.23
OCRVOTEDG                512   4,096    4,194,304 MOUNTED     NORMAL          15,348          1,040      6.78

SQL>

Done.. Hope this will help you. Feel free to comment here for any help required... :)

Best Regards,
Chowdai

CRS-4639 CRS-4124 Oracle High Availability Services startup failed in 11gR2 RAC

Today I have faced an issue with cluster OHASD service has not started automatically when server started. Then I tried to start cluster manually I received "CRS-4124: Oracle High Availability Services startup failed" error.

       
[oracle@rac1 ~]$ cd /u01/app/11.2.0/grid/bin
[oracle@rac1 bin]$ ./crsctl check crs
CRS-4639: Could not contact Oracle High Availability Services
[oracle@rac1 bin]$ 
[oracle@rac1 bin]$ su - root
Password: 
[root@rac1 ~]# cd /u01/app/11.2.0/grid/bin
[root@rac1 bin]# ./crsctl start crs
CRS-4124: Oracle High Availability Services startup failed
CRS-4000: Command Start failed, or completed with errors
[root@rac1 bin]#

Then I have verified cluster Oracle High Availability auto start-up is configured or not?

       
[root@rac1 bin]# ./crsctl config has
CRS-4622: Oracle High Availability Services autostart is enabled.
[root@rac1 bin]# 
 
 

Oracle High Availability Services auto-start is enabled. Then verified OLR accessibility on local node. Viewed ohasd.log and instance alert log files. Found below error messages and found as OHASD server was not running.

       
2014-08-29 18:15:18.620
[client(25949)]CRS-2302:Cannot get GPnP profile. Error CLSGPNP_NO_DAEMON (GPNPD daemon is not running).
2014-08-29 18:15:18.622
[client(25949)]CRS-1013:The OCR location in an ASM disk group is inaccessible. Details in /u01/app/11.2.0/grid/log/rac1/client/emcrsp.log.

 

Parallely I have checked with infrastructure team about this server. They identified some local script has hang. Now I have started OHASD service in background and it's able to start the CRS.

       
[root@rac1 ~]# nohup /etc/init.d/init.ohasd run &
[1] 20959
[root@rac1 ~]#

[root@rac1 bin]# ./crsctl start crs
CRS-4640: Oracle High Availability Services is already active
CRS-4000: Command Start failed, or completed with errors.
[root@rac1 bin]# 
[root@rac1 bin]# ./crsctl check crs
CRS-4638: Oracle High Availability Services is online
CRS-4537: Cluster Ready Services is online
CRS-4529: Cluster Synchronization Services is online
CRS-4533: Event Manager is online
[root@rac1 bin]#

Custer has started.

Go with below of some useful 11gR2 RAC high alert issues :-)

Listener INTERMEDIATE status with "Not All Endpoints Registered" in 11gR2 RAC
ORA-04031 unable to allocate 3896 bytes of shared memory in ASM Instance

Thats It. Hope this will help you... :)

Best Regards,
Chowdari.