Query to find ASM Freespace with Redundancy

Below Query will show how much space is available to use in case of High or Normal Redundancy

TOTAL_MB:- Refers to Total Capacity of the Diskgroup

FREE_MB :- Refers to raw Free Space Available in Diskgroup in MB.

FREE_MB = (TOTAL_MB – (HOT_USED_MB + COLD_USED_MB))

REQUIRED_MIRROR_FREE_MB :- Indicates how much free space is required in an ASM disk group to restore redundancy after the failure of an ASM disk or ASM failure group.In exadata it is the disk capacity of one failure group.

USABLE_FILE_MB :- Indicates how much space is available in an ASM disk group considering the redundancy level of the disk group.

Its calculated as :-

USABLE_FILE_MB=(FREE_MB – REQUIRED_MIRROR_FREE_MB ) / 2 –> For Normal Redundancy

USABLE_FILE_MB=(FREE_MB – REQUIRED_MIRROR_FREE_MB ) / 3 –> For High Redundancy

Query to Run:

column total format 999,999 Heading "Total(G)"

column free format 999,999 Heading "Free (G)"

column Mirror_GB format 999,999 Heading "Space Used |for Mirroring(G)"

column Usable_GB format 999,999 Heading "Space Available |to Use(G)"

column pct format 999.0 Heading "% Free |in DG" 

column pct2 format 999.0 Heading "Real % Free |in DG" 

column type format a10

column name format a20

set linesize 200

set colsep '|'

prompt

Prompt "NOTE **** Incase of High or Normal Redundancy the Usable Space is lower than actual shown because of Mirroring *****"

prompt

select name,type, TOTAL_MB/1024 total, FREE_MB/1024 free, REQUIRED_MIRROR_FREE_MB/1024 Mirror_GB, USABLE_FILE_MB/1024 Usable_GB ,100-((total_MB-FREE_MB)/total_mb)*100 pct, 100-((total_MB-USABLE_FILE_MB)/total_mb)*100 pct2  from v$asm_diskgroup;

Sample Output :

"NOTE **** Incase of High or Normal Redundancy the Usable Space is lower than actual shown because of Mirroring *****"

                    |          |        |        |     Space Used |Space Available |% Free |Real % Free

NAME                |TYPE      |Total(G)|Free (G)|for Mirroring(G)|       to Use(G)|  in DG|       in DG

--------------------|----------|--------|--------|----------------|----------------|-------|------------

DATA1              |HIGH      | 260,496|  57,951|          14,472|          14,493|   22.2|         5.6

REDO1              |HIGH      |  65,124|  33,322|           3,618|           9,901|   51.2|        15.2

oracle : Scheduling ASH reports through Crontab

I was asked to capture ASH reports every 5 minutes for an ongoing Database issue. Below is the process to schedule it through Crontab

Script to capture the ASH reports every 5 Minutes

$ cat ash.ksh

#!/bin/bash

export TZ=US/Central

dateString=`date +%d-%b-%Y_%H:%M:%S`

sqlplus -s / as sysdba << EOD1

define report_type = 'html'

define begin_time = '-5'

define duration = ''

define report_name = '/u01/user/local/reports/ashrpt.${dateString}.html'

@?/rdbms/admin/ashrpt

exit

EOF

To run it through Cron

0,5,10,15,20,22,25,30,32,35,40,45,50,55 * * * * /u01/user/local/ash/ash.ksh > /u01/user/local/reports/log/ash_collect.log 1>/dev/null 2>&1

ORACLE RAC : TERMINATING THE INSTANCE DUE TO ERROR 304

 After refreshing my QA database using RMAN DUPLICATE, my instance startup was failing with the below error

USER (ospid: 60897): terminating the instance due to error 304

Instance terminated by USER, pid = 60897

Wed Mar 06 02:14:34 2019

Starting ORACLE instance (normal)

Looking into the spfile, I noticed the database was pulling wrong instance_number and thread numbers even though the DB configuration was correct

$ srvctl config database -d oradb

Database unique name: oradb

Database name: oradb

Oracle home: /opt/app/oradb/oracle/product/11.2.0.4

Oracle user: oradb

Spfile: +oradb_DATA/oradb/spfileoradb.ora

Domain: db.abc.com

Start options: open

Stop options: immediate

Database role: PRIMARY

Management policy: AUTOMATIC

Server pools: oradb

Database instances: oradb1,oradb2

Disk Groups: oradb_DATA,oradb_FRA,oradb_REDO1,oradb_REDO2

Mount point paths: 

Services: oradb_1_2.db.abc.com,oradb_2_1.db.abc.com

Type: RAC

Database is administrator managed

From the pfile I created from the current spfile I could see 

*.instance_number=2

*.thread=2

To resolve this, bring down the complete database and just start the failing instance, in our case instance 1

srvctl start instance -d oradb -i oradb1

Once the instance is started, login to SQL and run below

SQL> alter system set instance_number=1 scope=spfile sid='oradb1';

System altered.

SQL> alter system set thread=1 scope=spfile sid='oradb1';

System altered.

SQL> alter system set undo_tablespace='UNDO01' sid='oradb1';

System altered.

shutdown the instance and start the complete database

SQL> shutdown immediate

srvctl start database -d oradb

Hope this resolves your issue. 

ORA-12537: TNS:connection closed - Oracle RAC 11g and above

 The client was getting Below error while connecting to the RAC database

sqlplus test@RACDB_1

SQL*Plus: Release 11.2.0.4.0 - Production on Tue Sep 22 11:34:07 2020

Copyright (c) 1982, 2007, Oracle.  All Rights Reserved.

ERROR:

ORA-12537: TNS:connection closed

Issue : 

The Oracle Binary permissions got changed and were not allowing the connections

Current Permissions under Oracle Home

[oracle@Node1 ~]$ ls -lrt $ORACLE_HOME/bin/oracle

-rwxr-sr-x 1 oracle asmadmin 243043788 Jun  2 01:12 oracle

It should be set to 6751 and should look like "-rwsr-s--x"

But doing chmod 6751 on oracle binary was not setting the correct permissions

[oracle@Node1 bin]$ chmod 6751 oracle

[oracle@Node1 bin]$ ls -lrt $ORACLE_HOME/bin/oracle

-rwsr-x--x 1 oracle asmadmin 243043788 Jun  2 01:12 oracle

Running below as RDBMS database Owner user helped, in this case, "oracle" user

1) Stop the database instance where the permissions got changed

 srvctl stop instance -d RACDB -i RACDB1

 2) Run as Oracle Database owner, in this case its Oracle OS user.  

[oracle@Node1 ~]$ $GRID_HOME/bin/setasmgidwrap o=$ORACLE_HOME/bin/oracle

3) The permissions got changed and resolved the connection issues

[oracle@Node1 ~]$ ls -lrt $ORACLE_HOME/bin/oracle

-rwsr-s--x 1 oracle asmadmin 243043788 Jun  2 01:12 oracle

Hope this resolves your issue..