Error 1031 received logging on to the standby

ISSUE

Error 1031 received logging on to the standby
ORA-01031: insufficient privileges
PING[ARC0]: Heartbeat failed to connect to standby 'dgp'. Error is 1031.

PRIMARY
=======
Primary database Alert log error:
--------------------------------
Error 1031 received logging on to the standby
ORA-01031: insufficient privileges
PING[ARC0]: Heartbeat failed to connect to standby 'dgp'. Error is 1031.

Chech DR sync and see the difference.

THREAD PR-ARCHIVED STBY-ARCHIVED STBY-APPLIED SHIPPING GAP(PR -> STBY) APPLIED GAP(STBY -> STBY)
------ ----------- ------------- ------------ ------------------------ -------------------------

set pages 999 lines 999

col MESSAGE for a100
select to_char(timestamp,'YYYY-MON-DD HH24:MI:SS')||' '||message||severity from gv$dataguard_status where severity in ('Error','Fatal') order by timestamp;

show parameter log_archive_dest_state_2;

LISTNER VERIFICATION FROM PRIMATY DB
------------------------------------
select dest_id,status,error from v$archive_dest where dest_name='LOG_ARCHIVE_DEST_2';

FIND GAP
--------
select thread#,low_sequence#,high_sequence# from gv$archive_log;

ps -ef |grep tns
lsnrctl status

DR
==
DR database Alert log error:
----------------------------
Error 1031 received logging on to the standby

set pages 999 lines 999

col MESSAGE for a100
select to_char(timestamp,'YYYY-MON-DD HH24:MI:SS')||' '||message||severity from gv$dataguard_status where severity in ('Error','Fatal') order by timestamp;

select inst_id,process,status,thread#,sequence#,block#,blocks from gv$managed_standby;

PROCESS STATUS
------- ------------
RFS IDLE
RFS IDLE
RFS IDLE
MRP0 WAIT_FOR_LOG

checking log transfer and apply
-------------------------------
SELECT SEQUENCE#,FIRST_TIME,NEXT_TIME,APPLIED FROM gV$ARCHIVED_LOG ORDER BY SEQUENCE#;
select count(*) from GV$ARCHIVED_LOG where applied='NO';

Redo transfer was not happening. When we checked in the
v$managed_process data dictionary view, we could see that RFS was not starting.

Here,
ora-01031 usually appears when some sysdba session failes to authenticate
Check that the primary and standby are using a password file
and remote_login_passwordfile is set to SHARED or EXCLUSIVE,
and that the SYS password is same in the password files.

1.The RFS process was not started in the standby which indicates that standby is not receiving any redo information from the primary.

2.In the primary alert log file we could see the errors indicating that the primary is not able to communicate with the standby instance. The error observed was "Error 1031 received logging on to the standby".

3.The time stamp of the password file on the primary and the standby was different. This indicated the possibility of having the wrong password file in the standby.

SOLUTION:
=========
For the redo transfer to take place efficiently, the password file on standby should be a copy from the primary and renamed standby.We can use v$pwd_file_users data dictionary view to check if the password file is used

1.copy the password file from the primary to the standby and renamed the password file in the following format ie orapw<sid> .

2.Restar the media recovery process on the standby.

Once the above steps are completed we could see that redo shipping and redo apply is taking place.

Copy the latest Password file from available PRIMARY Node to rest of PRIMARY and STANDBY nodes:
----------------------------------------------------------------------------------------------
Primary (Node 1)
----------------
cd $ORACLE_HOME/dbs
ls -lrt
scp orapw<sid> oracle@PROD_NODE_2_hostname:/oracle/home/dbs

scp orapw<sid> oracle@DR_NODE_1_hostname:/oracle/home/dbs
scp orapw<sid> oracle@DR_NODE_2_hostname:/oracle/home/dbs

or

DR
==
select * from gv$pwfile_users;

we have to check sec_case_sensitive_logon parameter on primary and standby.

SQL> show parameter sec_case_sensitive_logon;

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
sec_case_sensitive_logon boolean FALSE
SQL>

We have to recreate the passwd file or copy Primary server to Standby server.

In cause sec_case_sensitive_logon parameter value is true, we have to use below orapwd command.
orapwd file=$ORACLE_HOME/dbs/orapwPROD password=password123 entries=10 ignorecase=y

In cause sec_case_sensitive_logon parameter value is false, we have to use below orapwd command.
orapwd file=$ORACLE_HOME/dbs/orapwPROD password=password123 entries=10

DR
==
cancelling mrp process:
alter database recover managed standby database cancel;

starting mrp process:
alter database recover managed standby database disconnect from session;

select inst_id,process,status,thread#,sequence#,block#,blocks from gv$managed_standby;

Chech DR sync and see the difference.

http://troubleurheart.blogspot.in/2011/01/monitor-dataguard-status.html
http://alazydbasblog.blogspot.in/2012/04/steps-to-resolve-error-1031-received.html
http://bitbach.wordpress.com/2010/08/27/pingarc1-heartbeat-failed-to-connect-to-standby-dgp-error-is-1031/
www.dbaglobe.com/2011/01/monitor-dataguard-status.html
http://muthuappsdba.blogspot.in/2012/12/error-16191-pingarc2-heartbeat-failed.html

Source : Internet

ASM Diskgroup Scripts

oracle instance process running on the server
---------------------------------------------
ps -ef | grep pmon

Set the environment for the database
------------------------------------
uname

cat /etc/oratab (Linux/AIX)
(OR)
cat /var/opt/oracle/oratab

export ORACLE_SID=<INSTANCE_NAME>
export ORACLE_HOME=/usr/app/oracle/product/10.2.0/
export PATH=$PATH:$ORACLE_HOME/bin

Database Details
----------------
sqlplus "/as sysdba"

set pages 9999 lines 300
col OPEN_MODE for a10
col HOST_NAME for a20
select name DB_NAME,INSTANCE_NAME,HOST_NAME,DATABASE_ROLE,OPEN_MODE,version DB_VERSION,LOGINS,
to_char(STARTUP_TIME,'DD-MON-YYYY HH24:MI:SS') "DB UP TIME" from v$database,gv$instance;

ASM Diskgroup Utilization Script
--------------------------------
SET ECHO        OFF
SET FEEDBACK    6
SET HEADING     ON
SET LINESIZE    180
SET PAGESIZE    500
SET TERMOUT     ON
SET TIMING      OFF
SET TRIMOUT     ON
SET TRIMSPOOL   ON
SET VERIFY      OFF
CLEAR COLUMNS
CLEAR BREAKS
CLEAR COMPUTES
COLUMN disk_group_name        FORMAT a12           HEAD 'Disk Group Name'
COLUMN disk_file_path         FORMAT a45           HEAD 'Path'
COLUMN disk_file_name         FORMAT a12           HEAD 'File Name'
COLUMN disk_file_fail_group   FORMAT a12           HEAD 'Fail Group'
COLUMN total_mb               FORMAT 999,999,999   HEAD 'File Size (MB)'
COLUMN used_mb                FORMAT 999,999,999   HEAD 'Used Size (MB)'
COLUMN pct_used               FORMAT 999.99        HEAD 'Pct. Used'
BREAK ON report ON disk_group_name SKIP 1
COMPUTE sum LABEL ""              OF total_mb used_mb ON disk_group_name
COMPUTE sum LABEL "Grand Total: " OF total_mb used_mb ON report
SELECT
    NVL(a.name, '[CANDIDATE]')                       disk_group_name
  , b.path                                           disk_file_path
  , b.name                                           disk_file_name
  , b.failgroup                                      disk_file_fail_group
  , b.total_mb                                       total_mb
  , (b.total_mb - b.free_mb)                         used_mb
  , ROUND((1- (b.free_mb / b.total_mb))*100, 2)      pct_used
FROM v$asm_diskgroup a ,v$asm_disk b
where a.group_number (+) =b.group_number
ORDER BY a.name
/

To find the free space in an ASM disk:
--------------------------------------
set pages 9999 lines 900
select group_number, disk_number, name, failgroup, create_date, path, total_mb,free_mb from v$asm_disk;

To find the free space in an ASM diskgroup:
-------------------------------------------
set pages 9999 lines 900
select name, group_number, name, type, state, total_mb, free_mb from v$asm_diskgroup;

To see the current ASM operations in Progress:
----------------------------------------------
set pages 9999 lines 900
select group_number, operation, state, power, actual, sofar, est_work, est_rate, est_minutes from v$asm_operation;

ASM Diskgroup Information
-----------------------------
set pages 9999 lines 900
set head off
select 'Diskgroup Information' from dual;
set head on
column name format a15
column DG# format 99
select group_number DG#, name, state, type, total_mb, free_mb, round(free_mb/total_mb*100,2) pct_free from
v$asm_diskgroup;
   
Monitor space used in ASM Disk Groups
-------------------------------------
SET LINESIZE  145
SET PAGESIZE  9999
SET VERIFY    off
COLUMN group_name             FORMAT a20           HEAD 'Disk Group|Name'
COLUMN sector_size            FORMAT 99,999        HEAD 'Sector|Size'
COLUMN block_size             FORMAT 99,999        HEAD 'Block|Size'
COLUMN allocation_unit_size   FORMAT 999,999,999   HEAD 'Allocation|Unit Size'
COLUMN state                  FORMAT a11           HEAD 'State'
COLUMN type                   FORMAT a6            HEAD 'Type'
COLUMN total_mb               FORMAT 999,999,999   HEAD 'Total Size (MB)'
COLUMN used_mb                FORMAT 999,999,999   HEAD 'Used Size (MB)'
COLUMN pct_used               FORMAT 999.99        HEAD 'Pct. Used'
break on report on disk_group_name skip 1
compute sum label "Grand Total: " of total_mb used_mb on report
SELECT
    name                                     group_name
  , sector_size                              sector_size
  , block_size                               block_size
  , allocation_unit_size                     allocation_unit_size
  , state                                    state
  , type                                     type
  , total_mb                                 total_mb
  , (total_mb - free_mb)                     used_mb
  , ROUND((1- (free_mb / total_mb))*100, 2)  pct_used
FROM
    v$asm_diskgroup
ORDER BY
    name
/

Disk Group Name   File Name     File Size (MB) Used Size (MB) Free Size (MB) Pct. Used
-------------------- -------------------- -------------------- -------------- -------------- -------

Space used by database files.No other files found for cleanup.
There are 2 undo tablespaces for this database. Reclaimed some freespace from non-default undo tablespace

(i.e:"UNDOTBS1".)

Current status:
---------------
ORADATA diskgroup having xxxgb of freespace out of yyyygb.

http://dbasanthosh.wordpress.com/2012/05/
-----------------------------------------
SET LINESIZE 145
SET PAGESIZE 9999
SET VERIFY off
COLUMN disk_group_name FORMAT a20 HEAD 'Disk Group Name'
COLUMN disk_file_path FORMAT a20 HEAD 'Path'
COLUMN disk_file_name FORMAT a20 HEAD 'File Name'
COLUMN total_mb FORMAT 999,999,999 HEAD 'File Size (MB)'
COLUMN used_mb FORMAT 999,999,999 HEAD 'Used Size (MB)'
COLUMN free_mb FORMAT 999,999,999 HEAD 'Free Size (MB)'
COLUMN pct_used FORMAT 999.99 HEAD 'Pct. Used'
break on report on disk_group_name skip 1
compute sum label “” of total_mb used_mb free_mb on disk_group_name
compute sum label “Grand Total: ” of total_mb used_mb free_mb on report
SELECT NVL(a.name, '[CANDIDATE]') disk_group_name,b.path disk_file_path,b.name disk_file_name,
b.total_mb total_mb,(b.total_mb – b.free_mb) used_mb,b.free_mb free_mb,
ROUND((1- (b.free_mb / b.total_mb))*100, 2) pct_used FROM v$asm_diskgroup a RIGHT OUTER JOIN v$asm_disk b
USING (group_number) ORDER BY a.nameUSING (group_number) ORDER BY a.name

V$ASM_DISK Header_Status:
------------------------
UNKNOWN - Automatic Storage Management disk header has not been read

CANDIDATE - Disk is not part of a disk group and may be added to a disk group with the ALTER DISKGROUP statement

INCOMPATIBLE - Version number in the disk header is not compatible with the Automatic Storage Management software version.

PROVISIONED - Disk is not part of a disk group and may be added to a disk group with the ALTER DISKGROUP

statement. The PROVISIONED header status is different from the CANDIDATE header status in that PROVISIONED implies that an additional platform-specific action has been taken by an administrator to make the disk available for Automatic Storage Management.

MEMBER - Disk is a member of an existing disk group. No attempt should be made to add the disk to a different disk

group. The ALTER DISKGROUP statement will reject such an addition unless overridden with the FORCE option

FORMER - Disk was once part of a disk group but has been dropped cleanly from the group. It may be added to a new disk group with the ALTER DISKGROUP statement.

CONFLICT - Automatic Storage Management disk was not mounted due to a conflict

FOREIGN - Disk contains data created by an Oracle product other than ASM. This includes datafiles, logfiles, and OCR disks.

ASM Tablespace Utilization Scripts

http://files.meetup.com/1729503/1729503/All_about_ASM.pdf

ps -ef | grep pmon
ps -ef | grep tns
ps -ef | grep d.bin

Set the environment for the database
------------------------------------
uname
cat /etc/oratab OR cat /var/opt/oracle/oratab
export ORACLE_SID=<DBNAME>
export ORACLE_HOME=<>
export PATH=$PATH:$ORACLE_HOME/bin

To Check Database
-----------------
sqlplus "/as sysdba"
set pages 9999 lines 300
col OPEN_MODE for a10
col HOST_NAME for a10
select name DB_NAME,INSTANCE_NAME,HOST_NAME,OPEN_MODE,version DB_VERSION,DATABASE_STATUS,DATABASE_ROLE,LOGINS,to_char(STARTUP_TIME,'DD-MON-YYYY HH24:MI:SS') "DB UP TIME" from v$database,gv$instance;

ASM Space Report
----------------

SET LINESIZE 150
SET PAGESIZE 9999
SET VERIFY off
COLUMN group_name             FORMAT a20           HEAD 'Disk Group|Name'
COLUMN sector_size            FORMAT 99,999        HEAD 'Sector|Size'
COLUMN block_size             FORMAT 99,999        HEAD 'Block|Size'
COLUMN allocation_unit_size   FORMAT 999,999,999   HEAD 'Allocation|Unit Size'
COLUMN state                  FORMAT a11           HEAD 'State'
COLUMN type                   FORMAT a6            HEAD 'Type'
COLUMN total_mb               FORMAT 999,999,999   HEAD 'Total Size (GB)'
COLUMN free_mb                FORMAT 999,999,999   HEAD 'Free Size (GB)'
COLUMN used_mb                FORMAT 999,999,999   HEAD 'Used Size (GB)'
COLUMN pct_used               FORMAT 999.99        HEAD 'Pct. Used'

SELECT
    distinct name                            group_name
  , sector_size                              sector_size
  , block_size                               block_size
  , allocation_unit_size                  allocation_unit_size
  , state                                        state
  , type                                         type
  , round(total_mb/1024)                              "total_gb"
  , round(free_mb/1024)                               "free_gb"
  , round((total_mb - free_mb) / 1024)          "used_gb"
  , round((1- (free_mb / total_mb))*100, 2)  "pct_used"
from v$asm_diskgroup ORDER BY name
/

ASM Disk Space Usage Report In Detail
-------------------------------------
SET LINESIZE 150
SET PAGESIZE 9999
SET VERIFY off
COLUMN disk_group_name FORMAT a15 HEAD 'Disk Group Name'
COLUMN disk_file_path FORMAT a50 HEAD 'Path'
COLUMN disk_file_name FORMAT a20 HEAD 'File Name'
COLUMN disk_file_fail_group FORMAT a20 HEAD 'Fail Group'
COLUMN total_mb FORMAT 999,999,999 HEAD 'File Size (MB)'
COLUMN used_mb FORMAT 999,999,999 HEAD 'Used Size (MB)'
COLUMN pct_used FORMAT 999.99 HEAD 'Pct. Used'
break on report on disk_group_name skip 1
compute sum label 'Disk Group' of total_mb used_mb on disk_group_name
compute sum label 'ASM Disk Group Total Size in MB' of total_mb used_mb on report
SELECT
NVL(a.name, '[CANDIDATE]') disk_group_name
, b.path disk_file_path
, b.name disk_file_name
, b.failgroup disk_file_fail_group
, b.total_mb total_mb
, (b.total_mb - b.free_mb) used_mb
, ROUND((1- (b.free_mb / b.total_mb))*100, 2) pct_used
FROM v$asm_diskgroup a RIGHT OUTER JOIN v$asm_disk b USING (group_number) where b.header_status = 'MEMBER' 
ORDER BY a.name
/

OTHER ASM QUERIES
-----------------

set pages 9999 lines 900
col path for a50
select path,label, group_number, disk_number, NAME,TYPE, state, header_status,mount_status, mount_date,create_date, round(TOTAL_MB/1024) "TOTAL_GB", round(FREE_MB/1024) "FREE_GB",round((TOTAL_MB-FREE_MB) * 100 / TOTAL_MB,2) || '%' "USED_PERCENTAGE", SYSDATE from v$asm_disk order by path;

set pages 9999 lines 900
col path for a50
select path,label, group_number, disk_number, NAME,TYPE, state, header_status,mount_status, mount_date, round(TOTAL_MB/1024) "TOTAL_GB",  round(FREE_MB/1024) "FREE_GB", round((TOTAL_MB-FREE_MB) * 100 / TOTAL_MB,2) || '%' "USED_PERCENTAGE", SYSDATE from v$asm_diskgroup order by path;

SELECT GROUP_NUMBER, NAME, TYPE, STATE, ceil (total_mb/1024) TOTAL_GB,ceil (free_mb/1024) FREE_GB,ceil ((required_mirror_free_mb)/1024),ceil ((usable_file_mb)/1024), SYSDATE FROM V$ASM_DISKGROUP order by NAME;

select table_name from dict where table_name like '%ASM%';

Datafiles of a particular TableSpace
------------------------------------
set pages 9999 lines 300
col tablespace_name for a30
col file_name for a80
select tablespace_name,file_name,bytes/1024/1024 Size_MB,autoextensible,maxbytes/1024/1024 MAXSIZE_MB from dba_data_files where tablespace_name='&TABLESPACE_NAME' order by 1,2;

ASM Disk Database Files Report
------------------------------
set pages 9999 lines 300
col full_alias_path for a70
col file_type for a15

select concat('+'||gname, sys_connect_by_path(aname, '/')) full_alias_path,
system_created, alias_directory, file_type
from ( select b.name gname, a.parent_index pindex, a.name aname,
a.reference_index rindex , a.system_created, a.alias_directory,
c.type file_type
from v$asm_alias a, v$asm_diskgroup b, v$asm_file c
where a.group_number = b.group_number
and a.group_number = c.group_number(+)
and a.file_number = c.file_number(+)
and a.file_incarnation = c.incarnation(+)
)
start with (mod(pindex, power(2, 24))) = 0
and rindex in
( select a.reference_index
from v$asm_alias a, v$asm_diskgroup b
where a.group_number = b.group_number
and (mod(a.parent_index, power(2, 24))) = 0
and a.name = '&DATABASE_NAME'
)
connect by prior rindex = pindex;

All schema object details in a tablespace
-----------------------------------------
set pages 9999 lines 300
col tablespace_name format a15
col segment_name format a40
col segment_type format a20
col PARTITION_NAME format a20
col mb format 999,999,999
select owner
, tablespace_name
, segment_name
, segment_type
, PARTITION_NAME
, ceil(sum(bytes) / 1024 / 1024) "Size in MB"
from dba_segments
where tablespace_name like '&tablespace_name'
group by segment_name
order by ceil(sum(bytes) / 1024 / 1024) desc
/

TABLESPACE DDL
--------------
set pagesize 0
SET LONG 9999999
select dbms_metadata.get_ddl('TABLESPACE','&TABLESPACE_NAME') FROM DUAL;

To resize a datafile (ASM)
---------------------------
ALTER DATABASE DATAFILE '&FILE_NAME' RESIZE 4096M;
ALTER DATABASE DATAFILE '&FILE_NAME' AUTOEXTEND ON MAXSIZE 8G;

To add a new datafile in a tablespace (ASM)
--------------------------------------------
BEFORE ADDING A DATAFILE WE SHOULD CHECK FOR DUPLICATE DATAFILE For datafile
select tablespace_name,file_name from dba_data_files where file_name like '%&datafilename%';

ALTER TABLESPACE <TABLESPACE NAME> ADD DATAFILE '+DATA' SIZE 100M AUTOEXTEND ON NEXT 100M MAXSIZE UNLIMITED;

To Create a new tablespace (ASM)
---------------------------------
CREATE TABLESPACE <TABLESPACE NAME> DATAFILE '+DATA' SIZE 100M AUTOEXTEND ON NEXT 100M MAXSIZE UNLIMITED;

Schemas in a tablespace
-----------------------
set pages 999
col "size MB" format 999,999,999
col "Objects" format 999,999,999
select obj.owner "Owner"
, obj_cnt "Objects"
, decode(seg_size, NULL, 0, seg_size) "size MB"
from (select owner, count(*) obj_cnt from dba_objects group by owner) obj
, (select owner, ceil(sum(bytes)/1024/1024) seg_size
from dba_segments group by owner) seg
where obj.owner = seg.owner(+)
order by 3 desc ,2 desc, 1
/

V$ASM_DISK Header_Status:
------------------------
UNKNOWN - Automatic Storage Management disk header has not been read

CANDIDATE - Disk is not part of a disk group and may be added to a disk group with the ALTER DISKGROUP statement

INCOMPATIBLE - Version number in the disk header is not compatible with the Automatic Storage Management software

version.

PROVISIONED - Disk is not part of a disk group and may be added to a disk group with the ALTER DISKGROUP

statement. The PROVISIONED header status is different from the CANDIDATE header status in that PROVISIONED implies

that an additional platform-specific action has been taken by an administrator to make the disk available for

Automatic Storage Management.

MEMBER - Disk is a member of an existing disk group. No attempt should be made to add the disk to a different disk

group. The ALTER DISKGROUP statement will reject such an addition unless overridden with the FORCE option

FORMER - Disk was once part of a disk group but has been dropped cleanly from the group. It may be added to a new

disk group with the ALTER DISKGROUP statement.

CONFLICT - Automatic Storage Management disk was not mounted due to a conflict

FOREIGN - Disk contains data created by an Oracle product other than ASM. This includes datafiles, logfiles, and

OCR disks.

ASM Space Report
----------------
SET ECHO OFF
SET FEEDBACK 6
SET HEADING ON
SET LINESIZE 180
SET PAGESIZE 500
SET TERMOUT ON
SET TIMING OFF
SET TRIMOUT ON
SET TRIMSPOOL ON
SET VERIFY OFF
CLEAR COLUMNS
CLEAR BREAKS
CLEAR COMPUTES
COLUMN disk_group_name FORMAT a12 HEAD 'Disk Group Name'
COLUMN disk_file_path FORMAT a45 HEAD 'Path'
COLUMN disk_file_name FORMAT a12 HEAD 'File Name'
COLUMN disk_file_fail_group FORMAT a12 HEAD 'Fail Group'
COLUMN total_mb FORMAT 999,999,999 HEAD 'File Size (MB)'
COLUMN used_mb FORMAT 999,999,999 HEAD 'Used Size (MB)'
COLUMN pct_used FORMAT 999.99 HEAD 'Pct. Used'
BREAK ON report ON disk_group_name SKIP 1
COMPUTE sum LABEL "" OF total_mb used_mb ON disk_group_name
COMPUTE sum LABEL "Grand Total: " OF total_mb used_mb ON report
SELECT
NVL(a.name, '[CANDIDATE]') disk_group_name
, b.path disk_file_path
, b.name disk_file_name
, b.failgroup disk_file_fail_group
, b.total_mb total_mb
, (b.total_mb - b.free_mb) used_mb
, ROUND((1- (b.free_mb / b.total_mb))*100, 2) pct_used
FROM v$asm_diskgroup a ,v$asm_disk b
where a.group_number (+) =b.group_number
ORDER BY a.name;

To find the free space in an ASM disk:
--------------------------------------
select group_number, disk_number, name, failgroup, create_date, path, total_mb,free_mb from v$asm_disk;

To find the free space in an ASM diskgroup:
-------------------------------------------
select name, group_number, name, type, state, total_mb, free_mb from v$asm_diskgroup;

To see the current ASM operations in Progress:
----------------------------------------------
select group_number, operation, state, power, actual, sofar, est_work, est_rate, est_minutes from v$asm_operation;

set head off
select 'Diskgroup Information' from dual;
set head on
column name format a15
column DG# format 99
select group_number DG#, name, state, type, total_mb, free_mb, round(free_mb/total_mb*100,2) pct_free from v$asm_diskgroup;

Monitor space used in ASM Disk Groups
-------------------------------------
SET LINESIZE 145
SET PAGESIZE 9999
SET VERIFY off
COLUMN group_name FORMAT a20 HEAD 'Disk Group|Name'
COLUMN sector_size FORMAT 99,999 HEAD 'Sector|Size'
COLUMN block_size FORMAT 99,999 HEAD 'Block|Size'
COLUMN allocation_unit_size FORMAT 999,999,999 HEAD 'Allocation|Unit Size'
COLUMN state FORMAT a11 HEAD 'State'
COLUMN type FORMAT a6 HEAD 'Type'
COLUMN total_mb FORMAT 999,999,999 HEAD 'Total Size (MB)'
COLUMN used_mb FORMAT 999,999,999 HEAD 'Used Size (MB)'
COLUMN pct_used FORMAT 999.99 HEAD 'Pct. Used'
break on report on disk_group_name skip 1
compute sum label "Grand Total: " of total_mb used_mb on report
SELECT
name group_name
, sector_size sector_size
, block_size block_size
, allocation_unit_size allocation_unit_size
, state state
, type type
, total_mb total_mb
, (total_mb - free_mb) used_mb
, ROUND((1- (free_mb / total_mb))*100, 2) pct_used
FROM
v$asm_diskgroup
ORDER BY
name
/

Disk Group Name File Name File Size (MB) Used Size (MB) Free Size (MB) Pct. Used
-------------------- -------------------- -------------------- -------------- -------------- -------

Space used by database files.No other files found for cleanup.
There are 2 undo tablespaces for this database. Reclaimed some freespace from non-default undo tablespace (i.e:"UNDOTBS1".)

Current status:
---------------
ORADATA diskgroup having xxx gb of freespace out of yyyy gb.

http://dbasanthosh.wordpress.com/2012/05/
-----------------------------------------
SET LINESIZE 145
SET PAGESIZE 9999
SET VERIFY off
COLUMN disk_group_name FORMAT a20 HEAD 'Disk Group Name'
COLUMN disk_file_path FORMAT a20 HEAD 'Path'
COLUMN disk_file_name FORMAT a20 HEAD 'File Name'
COLUMN total_mb FORMAT 999,999,999 HEAD 'File Size (MB)'
COLUMN used_mb FORMAT 999,999,999 HEAD 'Used Size (MB)'
COLUMN free_mb FORMAT 999,999,999 HEAD 'Free Size (MB)'
COLUMN pct_used FORMAT 999.99 HEAD 'Pct. Used'
break on report on disk_group_name skip 1
compute sum label “” of total_mb used_mb free_mb on disk_group_name
compute sum label “Grand Total: ” of total_mb used_mb free_mb on report
SELECT NVL(a.name, '[CANDIDATE]') disk_group_name,b.path disk_file_path,b.name disk_file_name,
b.total_mb total_mb,(b.total_mb – b.free_mb) used_mb,b.free_mb free_mb,
ROUND((1- (b.free_mb / b.total_mb))*100, 2) pct_used FROM v$asm_diskgroup a RIGHT OUTER JOIN v$asm_disk b
USING (group_number) ORDER BY a.nameUSING (group_number) ORDER BY a.name
/

ASM PATCHING

Install Instructions
====================
Download ASM Patch

Check DR sync status

shutdown all Applications related to databases

PROD
====
Blackout all the sevices on database server Node 1 and Node 2 (OEM)

Capture the pre-shutdown status at OS level.
Capture the pre-shutdown status at Database level.

shutdown all the instances on the database server (Node 1)
shutdown ASM instance on server (Node 1)

shutdown all the instances on the database server (Node 2)
shutdown ASM instance on server (Node 2)

Install Patch on Node 1
-----------------------
set environment variables to asm
export ORACLE_HOME=<>
cd <PATCH_NUMBER>
$ORACLE_HOME/Opatch/opatch apply -local

ps -ef|grep asm

. oraenv
+ASM

Install Patch on Node 2
-----------------------
set environment variables to asm
export ORACLE_HOME=<>
cd <PATCH_NUMBER>
$ORACLE_HOME/Opatch/opatch apply -local

ps -ef|grep asm

. oraenv
+ASM

Validate whether the Patches are applied or not
-----------------------------------------------
$ORACLE_HOME/Opatch/opatch lsinventory

start ASM instance on server (Node 1)
start all the instances on the database server (Node 1)

start ASM instance on server (Node 2)
start all the instances on the database server (Node 2)

start the listeners and validate the connections (Node 1)
start the listeners and validate the connections (Node 2)

Database Validation
-------------------
validate all the Database services for all databases (Node 1)
validate all the Database services for all databases (Node 2)

Application Validation
----------------------
Startup the applications and validate and inform Application team
Application team should validate.

DR
==
Same steps as in PROD

Check DR sync status

FALLBACK Plan
=============
set environment variables

Rollback Patches on both Node 1 and Node 2
$ORACLE_HOME/Opatch/opatch -id <PATCH_NO> rollback

Database Validation
-------------------
validate all the Database services for all databases (Node 1)
validate all the Database services for all databases (Node 2)

Application Validation
----------------------
Startup the applications and validate and inform Application team

Application team should validate.

ASM Disk Add / Drop

How to add / drop disk in ASM
http://sanoralife.blogspot.in/2012/02/how-to-adddrop-disk-in-asm.html

Its not that quite often your SA asks you to release one of the disks that you been using it for a while for your database but if they do then you need to know how to remove the disk and add a new disk from your ASM.

Here are the steps to Add and Drop a Disk from ASM Diskgroup. Note that asm_power_limit parameter once set is permanent as it writes to both spfile as well (check alert log when you do alter session set asm_power_limit = 10 command to see that it converts it into scope=both).

asm_power_limit parameter
-------------------------
In simple words, Think that you are increasing the work force to finish the job (re-balancing the disk(s)) quicker. You can read more in Oracle Docs.

Steps to Add a Disk:
--------------------
1.Assign the disk to ORACLEASM
/etc/init.d/oracleasm createdisk ASM_DATA_10 /dev/mapper/mpath1p1

2.ScanDisk in ALL NODES
/etc/init.d/oracleasm scandisks

3.Check the header_status in v$asm_disks to make sure that the disk can be added (check below for each status description).
a.Login as sqlplus / as sysasm – note SYSASM here… (for 11g)
b.set lines 200
select group_number, substr(path,1,20) path, substr(name,1,20) disk_name, total_mb, free_mb, state, header_status, mode_status from v$asm_disk;

4.Now Add the Disk to diskgroup.
alter diskgroup ASM_DATA_DISK add disk 'ORCL:ASM_DATA_DISK'; --At this point the new disk is added to the group and Re-Balancing is occurring in the background (basically spreading the data across all the disks along with the new one in that diskgroup)

5.Check the status in v$asm_operations for rebalancing action.
select * from gv$asm_operation;

Now, You are ready to start using the new disk.

Steps to Drop a Disk:
---------------------
6.Drop the old Disk
a.alter diskgroup ASM_DATA__DISK drop disk ASM_DATA_01; --At this point, all the data on disk that you are dropping is being distributed to other disks within that diskgroup.
b.select *from gv$asm_operation; --to check the status on rebalancing while dropping the disk

7.Once dropped from ASM, delete from oracleasm library:
# /etc/init.d/oracleasm deletedisk ASM_DATA_01

8.ScanDisk in ALL NODES
/etc/init.d/oracleasm scandisks

ASMLib Troubleshooting

ASMLib Troubleshooting in Linux
http://linuxpkd.blogspot.in/2011/07/oracle-asmlib-troubleshooting-in-linux.html


This is the guide will help you how to do troubleshoot the oracleasmlib step by step;

1. rpm -qa | grep asm ---> It should be shows the list of installed oracle asm package

# rpm -qa |grep asm
oracleasm-support-2.0.3-1
oracleasmlib-2.0.2-1
oracleasm-2.6.9-22.ELsmp-2.0.3-1

2. rpm -ql oracleasm-support ---> which would show the scripts location.
# rpm -ql oracleasm-support
/etc/init.d/oracleasm
/etc/sysconfig/oracleasm
/usr/lib/oracleasm/oracleasm_debug_link
/usr/sbin/asmscan
/usr/sbin/asmtool
/etc/init.d/oracleasm is the command used to configure the ASM and scanning , configuring, litsing and querying the
disks from ASM. this script calls the asmtool and asmscan command.as per the scanning devices, there is possibility to
modify the file in /etc/sysconfig/oracleasm or else the excludes some of the functions in this file.

3. and ensure the oracleasm module to loaded to kernel.
[root@test1a ~]# lsmod | grep -i oracleasm
oracleasm 84136 1
[root@test1a ~]#

4. Verify the modules info as like,
[root@test1a ~]# modinfo oracleasm
filename: /lib/modules/2.6.18-194.el5/kernel/drivers/addon/oracleasm/oracleasm.ko
description: Kernel driver backing the Generic Linux ASM Library.
author: Joel Becker <joel.becker@oracle.com>
version: 2.0.5
license: GPL
srcversion: 6D09F6DEC4890E127C660DD
depends:
vermagic: 2.6.18-194.el5 SMP mod_unload gcc-4.1

5. Make sure the disks which are using, kernel should knows by the device in place of /dev or /etc/partitions. All the asmlib disks
should be partition before to create disk.

6. as the configuration part of ASMLib,run /etc/init.d/oracleasm configure as below,
[root@test1a ~]# /etc/init.d/oracleasm configure
Configuring the Oracle ASM library driver.
This will configure the on-boot properties of the Oracle ASM library
driver. The following questions will determine whether the driver is
loaded on boot and what permissions it will have. The current values
will be shown in brackets ('[]'). Hitting <ENTER> without typing an
answer will keep that current value. Ctrl-C will abort.
Default user to own the driver interface [oracle]:
Default group to own the driver interface [dba]:
Start Oracle ASM library driver on boot (y/n) [y]: y
Fix permissions of Oracle ASM disks on boot (y/n) [y]: y
Writing Oracle ASM library driver configuration: [ OK ]
Scanning system for ASM disks: [ OK ]

7. after oracleasm configured the status of the output should be like below,
[root@test1a ~]# /etc/init.d/oracleasm status
Checking if ASM is loaded: [ OK ]
Checking if /dev/oracleasm is mounted: [ OK ]

8. all the configured ASM disks should exitsts the default mount point location.
[root@test1a ~]# ls -l /dev/oracleasm
total 0
drwxr-xr-x 1 root root 0 Jul 18 16:52 disks
drwxrwx--- 1 oracle dba 0 Jul 18 16:52 iid
[root@test1a ~]# ls -l /dev/oracleasm/disks
total 0
brw-rw---- 1 oracle dba 120, 112 Jul 18 16:52 ARCHLOG
brw-rw---- 1 oracle dba 120, 224 Jul 18 16:52 DATA1
brw-rw---- 1 oracle dba 120, 208 Jul 18 16:52 DATA2

9. create a disk using by createdisk parameters:
#/etc/init.d/oracleasm createdisk VOL1 /dev/mapper/mapth1

10. For scanning the createdisk or ASM disks as below,
[root@test1a ~]# /etc/init.d/oracleasm scandisks
Scanning system for ASM disks: [ OK ]

11. For lists out the ASM disk which is under in ASM, use the below command,
[root@test1a ~]# /etc/init.d/oracleasm listdisks
ARCHLOG
DATA1
DATA2
DATA3
LOG1
LOG2
LOG3
LOG4

12. To query the asm disks, whether it is in VALID or notVALID state.
[root@test1a ~]# /etc/init.d/oracleasm querydisk LOG1
Disk "LOG1" is a valid ASM disk on device [120, 48]

13. To remove or delete the ASM disks, use below command,
[root@test1a ~]# /etc/init.d/oracleasm deletedisk LOG1

14. /usr/sbin/oracleasm-discover -- it will discover the asm configured disks with that associated name.
[root@test1a ~]# /usr/sbin/oracleasm-discover

15. how to identify the configured ASM disk to physical Disk name as tricky step use the blkid command.
[root@test1a ~]# /sbin/blkid

ASMLib disk Mapping to physical device

ASMLib disk Mapping to physical device
www.the-playground.de/joomla/index.php?option=com_content&view=article&id=306:ma

# oracleasm querydisk /dev/mapper/vol001
Device "/dev/mapper/vol001" is marked an ASM disk with the label "VOL001"

But what if you want to know what physical device is hidden behind the name of an ASMLib disk?
# oracleasm listdisks
VOL001
VOL002
VOL003
VOL004
...

The first attempt doesn't reveal too much:
# oracleasm querydisk VOL001
Disk "VOL001" is a valid ASM disk

# oracleasm querydisk -v -d VOL001
Disk "VOL001" is a valid ASM disk on device /dev/dm-4[253,4]

# oracleasm querydisk -v -p VOL001
Disk "ORC0001" is a valid ASM disk
/dev/mapper/vol001: LABEL="VOL001" TYPE="oracleasm"

ASM Troubleshooting

# /proc/partitions - shows the major minor numbers for the device at OS level

# asmcmd lsdsk -p

# /etc/init.d/oracleasm status
# /etc/init.d/oracleasm listdisks

# ls -l /dev/oracleasm
# ls -l /dev/oracleasm/disks

# oracleasm query disk /dev/mapper/<ASMLABEL>

# fdisk -l /dev/mapper/<ASMLABEL>
# fdisk -l /dev/sd-10 ----- OS level

Set the environment for the asm
-------------------------------
uname
cat /etc/oratab OR cat /var/opt/oracle/oratab
export ORACLE_SID=<DBNAME>
export ORACLE_HOME=<>
export PATH=$PATH:$ORACLE_HOME/bin

ps -ef| grep asm

. oraenv
+ASM

ASM Space Report
----------------
SET LINESIZE 150
SET PAGESIZE 9999
SET VERIFY off
COLUMN group_name FORMAT a20 HEAD 'Disk Group|Name'
COLUMN sector_size FORMAT 99,999 HEAD 'Sector|Size'
COLUMN block_size FORMAT 99,999 HEAD 'Block|Size'
COLUMN allocation_unit_size FORMAT 999,999,999 HEAD 'Allocation|Unit Size'
COLUMN state FORMAT a11 HEAD 'State'
COLUMN type FORMAT a6 HEAD 'Type'
COLUMN total_mb FORMAT 999,999,999 HEAD 'Total Size (MB)'
COLUMN used_mb FORMAT 999,999,999 HEAD 'Used Size (MB)'
COLUMN pct_used FORMAT 999.99 HEAD 'Pct. Used'

SELECT
distinct name group_name
, sector_size sector_size
, block_size block_size
, allocation_unit_size allocation_unit_size
, state state
, type type
, total_mb total_mb
, (total_mb - free_mb) used_mb
, ROUND((1- (free_mb / total_mb))*100, 2) pct_used
FROM gv$asm_diskgroup ORDER BY name
/

ASM Disk Space Usage Report In Detail
-------------------------------------
SET LINESIZE 150
SET PAGESIZE 9999
SET VERIFY off
COLUMN disk_group_name FORMAT a15 HEAD 'Disk Group Name'
COLUMN disk_file_path FORMAT a17 HEAD 'Path'
COLUMN disk_file_name FORMAT a20 HEAD 'File Name'
COLUMN disk_file_fail_group FORMAT a20 HEAD 'Fail Group'
COLUMN total_mb FORMAT 999,999,999 HEAD 'File Size (MB)'
COLUMN used_mb FORMAT 999,999,999 HEAD 'Used Size (MB)'
COLUMN pct_used FORMAT 999.99 HEAD 'Pct. Used'
break on report on disk_group_name skip 1
compute sum label 'Disk Group' of total_mb used_mb on disk_group_name
compute sum label 'ASM Disk Group Total Size in MB' of total_mb used_mb on report
SELECT
NVL(a.name, '[CANDIDATE]') disk_group_name
, b.path disk_file_path
, b.name disk_file_name
, b.failgroup disk_file_fail_group
, b.total_mb total_mb
, (b.total_mb - b.free_mb) used_mb
, ROUND((1- (b.free_mb / b.total_mb))*100, 2) pct_used
FROM v$asm_diskgroup a RIGHT OUTER JOIN v$asm_disk b USING (group_number) where b.header_status <> 'PROVISIONED'
ORDER BY a.name
/

OTHER ASM QUERIES
-----------------
set pages 9999 lines 900

SELECT GROUP_NUMBER, NAME, TYPE, STATE, ceil (total_mb/1024) TOTAL_GB,ceil (free_mb/1024) FREE_GB,
ceil ((required_mirror_free_mb)/1024),ceil ((usable_file_mb)/1024), SYSDATE
FROM V$ASM_DISKGROUP order by NAME;

SELECT GROUP_NUMBER, NAME, TYPE, STATE, ceil (total_mb/1024) TOTAL_GB,ceil (free_mb/1024) FREE_GB,
ceil ((required_mirror_free_mb)/1024),ceil ((usable_file_mb)/1024), SYSDATE
FROM GV$ASM_DISKGROUP order by NAME;

select GROUP_NUMBER,DISK_NUMBER,TOTAL_MB,FREE_MB,NAME,MOUNT_STATUS from v$asm_disk;
select INST_ID,GROUP_NUMBER,DISK_NUMBER,TOTAL_MB,FREE_MB,NAME,MOUNT_STATUS from gv$asm_disk;

select table_name from dict where table_name like '%ASM%';