Saturday, 9 April 2016

ASmLib download

$ rpm -qa | grep asmlib

$ /usr/sbin/oracleasm status
Checking if ASM is loaded: yes
Checking if /dev/oracleasm is mounted: yes

If the ASMLib RPM's are missing in your environment.

Please download the appropriate rpms for your version.

http://www.oracle.com/technetwork/server-storage/linux/asmlib/ol6-1709075.html

Change Hostnames in RAC setup

1> Capture Resource Definitions

Before doing anything, we should capture resource definitions from the current CRS resources. This is an optional step, but it will simplify configuration later.

A single resource definition can be captured with a command $ORA_CRS_HOME/bincrs_stat -p . Here is a small shell script to capture that for every resource and save it into a .cap file. As you will see later these files can be used to easily recreate resources:

for res in `$ORA_CRS_HOME/bin/crs_stat -p | grep "^NAME=" | cut -d = -f 2` ; do
    $ORA_CRS_HOME/bin/crs_stat -p $res >/opt/oracle/resources/$res.cap
done


2> Stop Clusterware

Now you can stop Oracle Clusterware on all nodes using $ORA_CRS_HOME/bin/crsctl stop crs, and then change hostnames. Note that this will stop all databases, listeners, and other resources registered within CRS, so this is the time when outage starts.

3> Rename Hosts
Ask SA to change Hostnames

Please note the following important points with respect to changing hostname.
1> Make sure that aliases in /etc/hosts are amended.
2> Don’t forget to change aliases for VIPs and private IPs. This is not strictly required but you are better off following the standard naming convention (-priv and -vip for interconnect and virtual IP respectively) unless you have really good reason not to. Note that at this stage you should be also able to change IP addresses as well. I didn’t try it, but it should work.
3> Also make sure DNS configuration is also changed by your SA, if your applications use DNS to resolve hostnames.

4> Modify $ORA_CRS_HOME/install/rootconfig

$ORA_CRS_HOME/install/rootconfig is called as part of the root.sh script run after Oracle Clusterware installation. We have to modify it so that it uses different host names.

Generally, you would simply change every appearance of the old hostnames to the new hostnames. If you want to do that in vi, use :%s/old_node/new_node/g. Be careful not to change existing non-relevant parts of the script matching your old hostname. The variables that should be changed are

    CRS_HOST_NAME_LIST
    CRS_NODE_NAME_LIST
    CRS_PRIVATE_NAME_LIST
    CRS_NODELIST
    CRS_NODEVIPS

The latter might need modification if you also change IPs.

At this stage, you can also change your OCR and voting disks locations. The following lines should be changed:

CRS_OCR_LOCATIONS={OCR path},{OCR mirror path}
CRS_VOTING_DISKS={voting disk1 path},{voting disk2 path},{voting disk3 path}

You can also change your cluster name via the variable CRS_CLUSTER_NAME.
5> Cleanup OCR and Voting Disks

You should clear OCR and voting disks, otherwise, the script will refuse to format them. This can be done using dd. In the example below I have mirrored OCR and 3 voting disks:

dd if=/dev/zero of= bs=1024k
dd if=/dev/zero of={OCR2 path} bs=1024k
dd if=/dev/zero of={voting1 path} bs=1024k
dd if=/dev/zero of={voting2 path} bs=1024k
dd if=/dev/zero of={voting3 path} bs=1024k

6> “Break” Clusterware Configuration

rootconfig has some protection  — it checks if Clusterware has been already configured and, if it has, it exits without doing any harm. One way to “break” the configuration and make this script run for a second time is to delete the file /etc/oracle/ocr.loc. (Note that this is a Linux-specific location; other Unix variants might have different path. On HP-UX, for example, it’s something like /var/opt/oracle/ocr.log if I recall correctly.)
Run $ORA_CRS_HOME/install/rootconfig

If everything has gone alright, you should be able to run $ORA_CRS_HOME/install/rootconfig as the root user without any issues. If there are problems, follow the standard CRS troubleshooting procedure — checking /var/log/messages and $ORA_CRS_HOME/log/{nodename} et cetera.

Note that this should be done on every node one by one — sequentially. On the last node of the cluster, the script will try to configure the VIPs, and there is a known bug here if you use a private range IP for VIP. This can be easily fixed by running $ORA_CRS_HOME/bin/vipca manually in graphical mode (i.e. you will need $DISPLAY configured correctly).
Verify Clusterware Configuration and Status

This is a simple check to make sure that all nodes are up and have VIP components configured correctly:

[root@mch10 bin]# $ORA_CRS_HOME/bin/crs_stat -t
Name           Type           Target    State     Host
------------------------------------------------------------
ora.mch10.gsd   application    ONLINE    ONLINE    mch10
ora.mch10.ons   application    ONLINE    ONLINE    mch10
ora.mch10.vip   application    ONLINE    ONLINE    mch10
ora.mch11.gsd   application    ONLINE    ONLINE    mch11
ora.mch11.ons   application    ONLINE    ONLINE    mch11
ora.mch11.vip   application    ONLINE    ONLINE    mch11

7> Adding Listener Resources to CRS

There are two ways to do this — you can either use netca to configure the listener from scratch (you might need to clean it up from listener.ora first), or you can change the configuration manually and register it with CRS from command line. I’ll show how to that manually — obviously, the preferred way when it comes to the real environments. ;-)

First of all, we will need to change the $ORACLE_HOME/network/admin/listener.ora file, and you will probably want to change tnsnames.ora at the same time. You need to replace old node aliases with new ones, and change the IPs if they are used instead of aliases, and if you changed them above during clusterware reconfiguration.

Note that depending on how your LOCAL_LISTENER and REMOTE_LISTENER init.ora parameters are set, you might need to change them: if they reference connections descriptors from tnsname.ora, then only the latter should be changed, but if there are full connection descriptors, they should also be modified).

You should also change listener names to reflect new hostnames. Usually, listeners are named as LISTENER_{hostname}, and you should keep this convention again unless you have a very good reason not to. Do that on both nodes if you don’t have a shared ORACLE_HOME.

Now it’s time to get back to the .cap files with the CRS resource definitions we captured when we began. The files we are interested in are in format ora.{hostname}.LISTENER_{HOSTNAME}.lsnr.cap. In my case, one of them is ora.vs10a.LISTENER_VS10A.lsnr (my old hostname was vs10a). If you changed listener names above, you would need to amend it there as well — NAME=ora.mch10.LISTENER_VS10.lsnr, and rename the file according to the new host name following the same naming convention.

Your VIP name has probably changed, so this line should be modified as well: REQUIRED_RESOURCES=ora.mch10.vip. And finally, the hosting member will change: HOSTING_MEMBERS=mch10. Check the whole file carefully — you should simply modify the old hostname to the new one in both lower and upper case.

Now it’s time to register the resource — the crs_register command does just that. This command specifies the resource name to register and the directory where the .cap file is located. It should be named exactly like resource name plus a “.cap” extension. Each node’s listener can be added from the same node. It’s important that the content of the .cap file is modified appropriately. Assuming I have files ora.mch10.LISTENER_VS10.lsnr and ora.mch11.LISTENER_VS11.lsnr in directory /opt/oracle/A/resources, I run:

$ORA_CRS_HOME/bin/crs_register ora.mch10.LISTENER_VS10.lsnr -dir /opt/oracle/A/resources
$ORA_CRS_HOME/bin/crs_register ora.mch11.LISTENER_VS11.lsnr -dir /opt/oracle/A/resources

Now the output from crs_stat -t should be:

Name           Type           Target    State     Host
------------------------------------------------------------
ora....10.lsnr application    OFFLINE   OFFLINE
ora.mch10.gsd   application    ONLINE    ONLINE    mch10
ora.mch10.ons   application    ONLINE    ONLINE    mch10
ora.mch10.vip   application    ONLINE    ONLINE    mch10
ora....11.lsnr application    OFFLINE   OFFLINE
ora.mch11.gsd   application    ONLINE    ONLINE    mch11
ora.mch11.ons   application    ONLINE    ONLINE    mch11
ora.mch11.vip   application    ONLINE    ONLINE    mch11

It’s now time to start the listeners:

$ORA_CRS_HOME/bin/srvctl start nodeapps -n mch10
$ORA_CRS_HOME/bin/srvctl start nodeapps -n mch11

crs_stat -t should show the listeners online:

Name           Type           Target    State     Host
------------------------------------------------------------
ora....10.lsnr application    ONLINE    ONLINE    mch10
ora.mch10.gsd   application    ONLINE    ONLINE    mch10
ora.mch10.ons   application    ONLINE    ONLINE    mch10
ora.mch10.vip   application    ONLINE    ONLINE    mch10
ora....11.lsnr application    ONLINE    ONLINE    mch11
ora.mch11.gsd   application    ONLINE    ONLINE    mch11
ora.mch11.ons   application    ONLINE    ONLINE    mch11
ora.mch11.vip   application    ONLINE    ONLINE    mch11

8.> Adding ASM Instances to CRS

This step is optional, and it you don’t use ASM, skip it.

Unfortunately, we can’t simply use .cap files to register ASM resources. There are more pieces required and the only way I could find to register ASM instances is to use srvctl which is, actually, a more supported option. This is simple:

$ORACLE_HOME/bin/srvctl add asm -n mch10 -i ASM1 -o $ORACLE_HOME
$ORACLE_HOME/bin/srvctl add asm -n mch11 -i ASM1 -o $ORACLE_HOME
$ORACLE_HOME/bin/srvctl start asm -n mch10
$ORACLE_HOME/bin/srvctl start asm -n mch11

There is a catch — sometimes I had to prefix the name of the ASM instance with a “+” (i.e. making it like -i +ASM1) and sometimes no plus-sign was required.

crs_stat -t should show now:

Name           Type           Target    State     Host
------------------------------------------------------------
ora....SM1.asm application    ONLINE    ONLINE    mch10
ora....10.lsnr application    ONLINE    ONLINE    mch10
ora.mch10.gsd   application    ONLINE    ONLINE    mch10
ora.mch10.ons   application    ONLINE    ONLINE    mch10
ora.mch10.vip   application    ONLINE    ONLINE    mch10
ora....SM2.asm application    ONLINE    ONLINE    mch11
ora....11.lsnr application    ONLINE    ONLINE    mch11
ora.mch11.gsd   application    ONLINE    ONLINE    mch11
ora.mch11.ons   application    ONLINE    ONLINE    mch11
ora.mch11.vip   application    ONLINE    ONLINE    mch11

9.> Register Databases

For each database, you need to register a database resource. Then, for every instance, you need to register an instance resource. So for database A, my two-node cluster, I use:

$ORACLE_HOME/bin/srvctl add database -d A -o $ORACLE_HOME
$ORACLE_HOME/bin/srvctl add instance -d A -i A1 -n mch10
$ORACLE_HOME/bin/srvctl add instance -d A -i A2 -n mch11
$ORACLE_HOME/bin/srvctl start database -d A

10.> Finally, crs_stat -t should show all resources online:

Name           Type           Target    State     Host
------------------------------------------------------------
ora.A.A1.inst  application    ONLINE    ONLINE    mch10
ora.A.A2.inst  application    ONLINE    ONLINE    mch11
ora.A.db       application    ONLINE    ONLINE    mch10
ora....SM1.asm application    ONLINE    ONLINE    mch10
ora....10.lsnr application    ONLINE    ONLINE    mch10
ora.mch10.gsd   application    ONLINE    ONLINE    mch10
ora.mch10.ons   application    ONLINE    ONLINE    mch10
ora.mch10.vip   application    ONLINE    ONLINE    mch10
ora....SM2.asm application    ONLINE    ONLINE    mch11
ora....11.lsnr application    ONLINE    ONLINE    mch11
ora.mch11.gsd   application    ONLINE    ONLINE    mch11
ora.mch11.ons   application    ONLINE    ONLINE    mch11
ora.mch11.vip   application    ONLINE    ONLINE    mch11

11.> Other Resources

If you had other resources like services, user VIPs, or user-defined resources, you will probably be fine using the crs_register command to get them back into CRS. I didn’t try it, but it should work.
Final Check

To make sure that everything is working, you should at least reboot every node and see if everything comes up.

I don’t know if that operation is considered to be supported. The only slippery bit is modifying the $ORA_CRS_HOME/install/rootconfig file, because it’s usually created by the Universal Installer. Another tricky place is the “unusual” registration of listeners. Otherwise, all the commands are pretty much usual stuff, I think. Good luck!

Opatch Permission libraries Issue

I see that all the files which should be owned by root are owned by grid user on node C.

As ROOT user, please run below command on node C - and share " ls -alrt /usr/products/grid/grid_home/12.1.0.2/lib"

/crs/install/rootcrs.sh -prepatch
/rdbms/install/rootadd_rdbms.sh
/crs/install/rootcrs.sh -postpatch

Thanks,

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

unlock and re-lock the GI  to  verify whether permissions remains same or getting changed

As root:

# $GRID_HOME/crs/install/rootcrs.pl -unlock -crshome $GRID_HOME            >>>>>>>stop the crs 
# $GRID_HOME/crs/install/rootcrs.pl -patch>>>>>>>starts the crs

And, then share the " ls -alrt /usr/products/grid/grid_home/12.1.0.2/lib"

Add Node

1> Compare the mentioned file in the new and old environment: "/etc/sysctl.conf" file.
2> Compare the mentioned file in the new and old environment: "/etc/security/limits.conf" file.
3> Compare the mentioned file in the new and old environment "/etc/pam.d/login" file.
4> Create the necessary oracle groups and users.
5> chkconfig ntpd status
6> Create the .profile for setting the environment for grid and oracle user (Compare the mentioned file in the new and old environment)
"/home/oracle/.bash_profile" file and "/home/grid/.bash_profile"
"/home/oracle/grid_env" and "/home/grid/grid_env"
"/home/oracle/db_env" and "/home/grid/db_env"
7> uname -rm
8> All necessary ASM libraries and packages
9> Ping all the IPs of the New_Node
ping -c 3 112-rac1
ping -c 3 112-rac1-priv
ping -c 3 112-rac1-vip

10> ASM must be configured and "#oracleasm listdisks" command on the New_Node should show all the disks.
Few command reference : oracleasm listdisks, oracleasm init, oracleasm scandisks

11> Configure secure shell for oracle user on all nodes

From oracle_home/oui/bin on existing Node -
./runSSHSetup.sh -user oracle -hosts "Existing_Node New_Node" -advanced -exverify

12> Verify New_Node (HWOS)

From grid_home on existing Node
$GRID_HOME/bin/cluvfy stage -post hwos -n New_Node > /u02/hwos.log

13> Verify Peer (REFNODE)

From grid_home on existing Node
$GRID_HOME/bin/cluvfy comp peer -refnode existing Node -n New_Node -orainv oinstall -osdba dba -verbose > /u02/comppeer.log

14> Verify New_Node (New_Node PRE)

From grid_home on existing Node
$GRID_HOME/bin/cluvfy stage -pre nodeadd -n New_Node -fixup -verbose > /u02/fixup.log

14> Extend Clusterware

Run “addNode.sh”

a) [oracle@existing Node bin]$ ./addNode.sh -silent "CLUSTER_NEW_NODES={New_Node}" "CLUSTER_NEW_VIRTUAL_HOSTNAMES={New_Node-vip}"
From root user on New_Node :::

b) [root@New_Node oraInventory]# ./orainstRoot.sh
From root user on New_Node :::

c) [root@New_Node oraInventory]# cd /u01/app/11.2.0/grid/
[root@New_Node grid]# ./root.sh

If successful, clusterware daemons, the listener, the ASM instance, etc. should be started

d) [oracle@New_Node ~]$ crsctl check crs
e) [oracle@New_Node ~]$ crs_stat -t -v
f) [oracle@New_Node ~]$ olsnodes -n
Existing Node  1
Existing_Node 2
New_Node  3

g)[oracle@New_Node ~]$ srvctl status asm -a
ASM is running on Existing_Node,New_Node,existing Node
ASM is enabled.

h) [oracle@New_Node ~]$ ocrcheck
i) [oracle@New_Node ~]$ crsctl query css votedisk

15) Verify New_Node (New_Node POST)

[oracle@existing Node u02]$ $GRID_HOME/bin/cluvfy stage -post nodeadd -n New_Node -verbose > /u02/clusterpost.log

Friday, 8 April 2016

ASM Block Header 0 Corruption

Reference: How To Restore/Repair/Fix An Overwritten (KFBTYP_INVALID) ASM Disk Header (First 4K) 10.2.0.5, 11.1.0.7, 11.2 And Onwards (Doc ID 1088867.1) 

To confirm if just block 0 is corrupted or there are more blocks, please execute this script and upload .out files
kfed.sh
-----
#! /bin/sh
rm /tmp/kfed_DH.out /tmp/kfed_FS.out /tmp/kfed_BK.out /tmp/kfed_FD.out /tmp/kfed_DD.out
for i in `ls /dev/emc*`
do
echo $i >> /tmp/kfed_DH.out
kfed read $i >> /tmp/kfed_DH.out
echo $i >> /tmp/kfed_FS.out
kfed read $i blkn=1 >> /tmp/kfed_FS.out
echo $i >> /tmp/kfed_BK.out
kfed read $i aun=1 blkn=254 >> /tmp/kfed_BK.out
echo $i >> /tmp/kfed_FD.out
kfed read $i aun=2 blkn=1 >> /tmp/kfed_FD.out
echo $i >> /tmp/kfed_DD.out
kfed read $i aun=2 blkn=2 >> /tmp/kfed_DD.out
done
----

That way we can see which au's are affected

Please, upload asmdu output too:
amdu -diskstring '' -dump

and the OS logs

Additionally, please upload:
1) full ASM alertlog

2) Please connect to ASM instance, generate and upload /tmp/ASM_#.html file as result of output from v$asm_disk and v$asm_diskgroup:

---------------------- cut -------------------------
         spool /tmp/ASM_<#>.html
         set markup HTML on
         set pagesize 1000
         set lines 500
         alter session set nls_date_format='DD-MON-YYYY HH24:MI:SS';
         select sysdate "Date and Time" from dual;
         select 'HOSTNAME ASSOCIATED WITH THIS ASM INSTANCE: ==)> ' , MACHINE " " from v$session where program like '%SMON%';

         select * from v$asm_diskgroup order by 1;
         select * from v$asm_disk order by 1, 2, 3;
         select * from gv$asm_operation order by 1;
         select * from v$version where banner like '%Database%' order by 1;
         select * from gv$asm_client order by 1;
         select group_number, name, value FROM v$asm_attribute where NAME like 'disk_repair_time';

         show parameter asm
         show parameter size
         show parameter proc
         show parameter cluster
         show parameter instance_type
         show parameter instance_name

         show parameter pfile

         show sga

         spool off

--------------------- end cut -----------------------

Grid Install on Windows for Oracle Restart

1. Oracle software owner account creation

 Create a user oracle and assign Administrator group

Check that you can connect to Windows server with the oracle user account.

2.  Oracle software file system creation

On my server I have assigned letter H to new file system with following commands run with Administrator:

C:\Users\Administrator> diskpart

Microsoft DiskPart version 6.2.9200

Copyright (C) 1999-2012 Microsoft Corporation.
On computer: W12DC02

DISKPART> list disk

  Disk ###  Status         Size     Free     Dyn  Gpt
  --------  -------------  -------  -------  ---  ---
  Disk 0    Online           25 GB      0 B
  Disk 1    Online           25 GB    25 GB
  Disk 2    Online           10 GB    10 GB
  Disk 3    Online           10 GB    10 GB

DISKPART> select disk 1

Disk 1 is now the selected disk.

DISKPART> create partition extended

DiskPart succeeded in creating the specified partition.

DISKPART> create partition logical

DiskPart succeeded in creating the specified partition.

DISKPART>


DISKPART> list volume

  Volume ###  Ltr  Label        Fs     Type        Size     Status     Info
  ----------  ---  -----------  -----  ----------  -------  ---------  --------
  Volume 0     D   VBOXADDITIO  CDFS   CD-ROM        55 MB  Healthy
  Volume 1     E                       CD-ROM          0 B  No Media
  Volume 2         System Rese  NTFS   Partition    350 MB  Healthy    System
  Volume 3     C                NTFS   Partition     24 GB  Healthy    Boot
  Volume 4                      RAW    Partition     24 GB  Healthy

DISKPART> select volume 4

Volume 4 is the selected volume.

DISKPART> format fs=ntfs

  100 percent completed

DiskPart successfully formatted the volume.

DISKPART> assign letter=h

DiskPart successfully assigned the drive letter or mount point.

DISKPART> detail partition

Partition 1
Type  : 07
Hidden: No
Active: No
Offset in Bytes: 2097152

  Volume ###  Ltr  Label        Fs     Type        Size     Status     Info
  ----------  ---  -----------  -----  ----------  -------  ---------  --------
* Volume 4     H                NTFS   Partition     24 GB  Healthy

DISKPART> exit

Leaving DiskPart...
PS C:\Users\Administrator> cmd.exe
Microsoft Windows [Version 6.2.9200]
(c) 2012 Microsoft Corporation. All rights reserved.

C:\Users\Administrator>dir h:
 Volume in drive H has no label.
 Volume Serial Number is E837-1047

 Directory of H:\

File Not Found

C:\Users\Administrator>

ASM storage configuration

Run following commands with Administrator account from a Powershell session:

PS C:\Users\Administrator> diskpart

Microsoft DiskPart version 6.2.9200

Copyright (C) 1999-2012 Microsoft Corporation.
On computer: W12DC02

DISKPART> list disk

  Disk ###  Status         Size     Free     Dyn  Gpt
  --------  -------------  -------  -------  ---  ---
  Disk 0    Online           25 GB      0 B
  Disk 1    Online           25 GB  1024 KB
  Disk 2    Online           10 GB    10 GB
  Disk 3    Online           10 GB    10 GB

DISKPART> select disk 2

Disk 2 is now the selected disk.

DISKPART> create partition extended

DiskPart succeeded in creating the specified partition.

DISKPART> create partition logical

DiskPart succeeded in creating the specified partition.

DISKPART> select disk 3

Disk 3 is now the selected disk.

DISKPART> create partition extended

DiskPart succeeded in creating the specified partition.

DISKPART> create partition logical

DiskPart succeeded in creating the specified partition.

DISKPART> list volume

  Volume ###  Ltr  Label        Fs     Type        Size     Status     Info
  ----------  ---  -----------  -----  ----------  -------  ---------  --------
  Volume 0     D   VBOXADDITIO  CDFS   CD-ROM        55 MB  Healthy
  Volume 1     E                       CD-ROM          0 B  No Media
  Volume 2         System Rese  NTFS   Partition    350 MB  Healthy    System
  Volume 3     C                NTFS   Partition     24 GB  Healthy    Boot
  Volume 4     H                NTFS   Partition     24 GB  Healthy
  Volume 5                      RAW    Partition      9 GB  Healthy
* Volume 6                      RAW    Partition      9 GB  Healthy

DISKPART> exit

Leaving DiskPart...
PS C:\Users\Administrator>

Windows PowerShell
Copyright (C) 2012 Microsoft Corporation. All rights reserved.

PS C:\Users\Administrator> cd F:\winx64_12102_grid\grid\asmtool
PS F:\winx64_12102_grid\grid\asmtool> dir


    Directory: F:\winx64_12102_grid\grid\asmtool


Mode                LastWriteTime     Length Name
----                -------------     ------ ----
-----        11/09/2014     09:56      11776 asmtool.exe
-----        11/09/2014     09:56      25088 asmtoolg.exe
-----        30/04/2012     01:57     608080 msvcp100.dll
-----        30/04/2012     01:57     829264 msvcr100.dll
-----        22/05/2012     11:13     362496 msvcrt.dll
-----        11/09/2014     09:56       4608 oraasmtoolus.msb
-----        02/08/2014     02:46    1755136 oracore12.dll
-----        11/09/2014     10:51     117248 oraimr12.dll
-----        04/08/2014     16:17    1041408 oranls12.dll
-----        04/08/2014     15:53     241664 orasnls12.dll
-----        04/08/2014     15:53     105472 oraunls12.dll
-----        02/08/2014     02:46       9728 orauts.dll


PS F:\winx64_12102_grid\grid\asmtool>
PS F:\winx64_12102_grid\grid\asmtool> .\asmtool -list
NTFS                             \Device\Harddisk0\Partition1              350M
NTFS                             \Device\Harddisk0\Partition2            25248M
NTFS                             \Device\Harddisk1\Partition1            25597M
                                 \Device\Harddisk2\Partition1            10237M
                                 \Device\Harddisk3\Partition1            10237M
PS F:\winx64_12102_grid\grid\asmtool> .\asmtool -add  \Device\Harddisk2\Partition1 ORCLDISK1
PS F:\winx64_12102_grid\grid\asmtool> .\asmtool -add  \Device\Harddisk3\Partition1 ORCLDISK2
PS F:\winx64_12102_grid\grid\asmtool> .\asmtool -list
NTFS                             \Device\Harddisk0\Partition1              350M
NTFS                             \Device\Harddisk0\Partition2            25248M
NTFS                             \Device\Harddisk1\Partition1            25597M
ORCLDISK1                        \Device\Harddisk2\Partition1            10237M
ORCLDISK2                        \Device\Harddisk3\Partition1            10237M
PS F:\winx64_12102_grid\grid\asmtool>

The 2 10 Gb raw disk partitions are now ready to be used by ASM.

3. Verify Oracle Restart host cluvfy

cluvfy stage -pre  hacfg -verbose

4. Software  and ASM disk locations

/u01/app/oraInventory             --> Inventory
/u01/app/1202/grid                --> GRID_HOME     Version 12.1.0.2
/u01/app/oracle/product/11204/ors --> ORACLE_HOME
/u01/app/oracle                   --> ORACLE_BASE   Version 11.2.0.4

ASM Disk protection:
# ls -l  /dev/asm_data_11g_disk1
brw-rw----. 1 oracle oinstall 8, 17 Sep  5 15:41 /dev/asm_data_11g_disk1

5. Install 12c Oracle Grid Infracstructure for a Standalone Server

[oracle@test grid]$ ./runInstaller
  -->  Install and confgure Oracle Grid Infr. for a Standalone Server
  --> Create ASM device : DG DATA
..
[root@test12 app]# /u01/app/1202/grid/root.sh

6. Verify DG
[oracle@test12 ~]$ asmcmd lsdg
State    Type    Rebal  Sector  Block       AU  Total_MB  Free_MB  Req_mir_free_MB  Usable_file_MB  Offline_disks  Voting_files  Name
MOUNTED  EXTERN  N         512   4096  1048576     10236     8712                0            8712              0             N  DATA/


Wednesday, 27 January 2016

To investigate and fix ASM disk header corruption

How To Restore/Repair/Fix An Overwritten (KFBTYP_INVALID) ASM Disk Header (First 4K) 10.2.0.5, 11.1.0.7, 11.2 And Onwards (Doc ID 1088867.1) 

To confirm if just block 0 is corrupted or there are more blocks, please execute this script and upload .out files
kfed.sh
-----
#! /bin/sh
rm /tmp/kfed_DH.out /tmp/kfed_FS.out /tmp/kfed_BK.out /tmp/kfed_FD.out /tmp/kfed_DD.out
for i in `ls /dev/emc*`
do
echo $i >> /tmp/kfed_DH.out
kfed read $i >> /tmp/kfed_DH.out
echo $i >> /tmp/kfed_FS.out
kfed read $i blkn=1 >> /tmp/kfed_FS.out
echo $i >> /tmp/kfed_BK.out
kfed read $i aun=1 blkn=254 >> /tmp/kfed_BK.out
echo $i >> /tmp/kfed_FD.out
kfed read $i aun=2 blkn=1 >> /tmp/kfed_FD.out
echo $i >> /tmp/kfed_DD.out
kfed read $i aun=2 blkn=2 >> /tmp/kfed_DD.out
done
----

That way we can see which au's are affected

Please, upload asmdu output too:
amdu -diskstring '' -dump

and the OS logs

Additionally, please upload:
1) full ASM alertlog

2) Please connect to ASM instance, generate and upload /tmp/ASM_#.html file as result of output from v$asm_disk and v$asm_diskgroup:

---------------------- cut -------------------------
         spool /tmp/ASM_<#>.html
         set markup HTML on
         set pagesize 1000
         set lines 500
         alter session set nls_date_format='DD-MON-YYYY HH24:MI:SS';
         select sysdate "Date and Time" from dual;
         select 'HOSTNAME ASSOCIATED WITH THIS ASM INSTANCE: ==)> ' , MACHINE " " from v$session where program like '%SMON%';

         select * from v$asm_diskgroup order by 1;
         select * from v$asm_disk order by 1, 2, 3;
         select * from gv$asm_operation order by 1;
         select * from v$version where banner like '%Database%' order by 1;
         select * from gv$asm_client order by 1;
         select group_number, name, value FROM v$asm_attribute where NAME like 'disk_repair_time';

         show parameter asm
         show parameter size
         show parameter proc
         show parameter cluster
         show parameter instance_type
         show parameter instance_name

         show parameter pfile

         show sga

         spool off

--------------------- end cut -----------------------

Steps to Convert RAC to Non-RAC Setup

Summary of Steps to Convert RAC to Non-RAC Setup:

There are several scenarios for this situation:

1. Converting RAC instances to non-RAC instances permanently without keeping the Oracle Clusterware.
2. Converting RAC instances to non-RAC instances permanently, but still keeping the Oracle Clusterware.
3. Converting RAC instances to non-RAC instances temporarily, so the production DB can continue running while troubleshooting of RAC issues is in progress.

In all cases, the Clusterware should be shutdown on other nodes to avoid conflicts of the operations.

1. Converting RAC instances to non-RAC instances permanently without keeping the Oracle Clusterware.

a.) Shutdown clusterware on ALL nodes with root user.
b.) Run rootdelete and rootdeinstall with root user.
c.) Run installer and remove the Clusterware home with crs user.
d.) Install a new single instance home with oracle user. Also a separate ASM home if preferred.
e.) As oracle user, remove the listener using netca from the OLD home. Create a new local listener using netca from the NEW home. The listener will not listen to VIP anymore. Change existing tnsnames.ora files on server and/or clients to use host IP instead of VIP.
f.) Configure ASM using dbca from the new home with oracle user. Follow the instruction from dbca to create non-RAC CSS using “localconfig add” with root user.
g.) With oracle user, copy the pfile/spfile from the old DB home to the new DB home, remove all the parameters for other instances in the pfile/spfile.

— Remove cluster_database and cluster_database_instances parameters.
— Remove undo_tablespace parameter for the other instances.
— Remove remote_listener and local_listener parameters if present.

h.) Startup new listener and ASM with oracle user. Make sure ASM diskgroups are mounted.
i.) With oracle user, startup the database in mount stage and execute
alter database disable thread ;
alter database open;
j.) After opening database you can drop the redolog groups and/or tablespaces which are for other instances.
k.) With oracle user, modify the ORACLE_HOME on /etc/oratab. And remove instance_number and thread parameters in the pfile/spfile.
l.) With oracle user, run installer to remove the OLD ORACLE_HOME.

2. Converting RAC instances to non-RAC instances permanently, but still keeping the Oracle Clusterware.

In this scenario, it is also recommended to just install a Single Instance home and then start ASM and Database instances from the new home. (If preferred, a separate single-instance ASM home can be installed.) So, the inventory can be in sync of the changes, and this could prevent related problems in the future.

a.) Leave the Oracle Clusterware as it is.
b.) With oracle user, install a new SI home (runInstaller gives you option to install RAC enabled home or SI home). Also a separate ASM home if preferred.
c.) With root user, stop Clusterware on all node except the current node. Stop DB/ASM instances and remove instance registries in the OCR using the srvctl from the old home with crs user.
$ srvctl remove instance -d -i
$ srvctl remove database -d
$ srvctl remove asm -n [-i ]

d.) With oracle user, remove listeners using netca from the old home.
e.) With oracle user, create a new listener using netca from the new home. If the listener will not listen to VIP anymore, change existing tnsnames.ora files on server and/or clients to use host IP instead of VIP.
f.) With oracle user, configure single instance ASM using dbca from the new home.
g.) Convert and start DB instance from the new home. See step g to l in scenario #1.
h.) If preferred, register DB to the OCR.

3. Converting RAC instances to non-RAC instances temporarily, so the production DB can continue running while troubleshooting of RAC issues is in progress.

** Please note that while staying in this transition status, please DO NOT apply any RDBMS patch without first converting back to RAC.
a.) Shutdown all instances including ASM and DB instances in RAC environment on ALL nodes with oracle user.
b.) Shutdown all the listeners on ALL nodes with oracle user.
c.) With oracle user, relink Oracle executable with rac_off option. (For both ASM and DB homes)
$ make -f ins_rdbms.mk rac_off
$ make -f ins_rdbms.mk ioracle

d.) With oracle user, remove all parameters for other instances in the pfile/spfile.
— Remove cluster_database and cluster_database_instances parameters. (For both ASM and DB)
— Remove undo_tablespace parameter for the other instances. (For DB only)

e.) With oracle user, startup listener and ASM. Make sure ASM diskgroups are mounted.
f.) With oracle user, startup the database in mount stage and execute
alter database disable thread ;
alter database open;

g.) After opening database you can drop the redolog groups and/or tablespaces which are for other instances.
h.) Disable autostart of ASM/DB/Clusterware. (Re-enable them after fixing the CRS/RAC issue)
$ srvctl modify database -d -y manual
$ srvctl disable asm -n
# crsctl disable crs

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Ref:
How to Convert RAC ASM/DB instances to non-RAC ASM/DB instances (Doc ID 759868.1)
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
1) Verify database running status on all nodes.
[oracle@host01 ~]$ srvctl status database -d RAC
Instance RAC1 is running on node host01
Instance RAC2 is running on node host02

2) Stop database using srvctl

[oracle@host01 ~]$ srvctl stop database -d RAC

3) Remove the database entry from crs

[oracle@host01 ~]$ srvctl remove instance -d RAC -i RAC2
Remove instance RAC2 from the database RAC? (y/[n]) y
[oracle@host01 ~]$ srvctl remove instance -d RAC -i RAC1

Remove instance RAC1 from the database RAC? (y/[n]) y

4) Start the database on first instance

[oracle@host01 ~]$ dba

SQL*Plus: Release 11.1.0.7.0 - Production on Wed Sep 16 20:01:54 2011Copyright (c) 1982, 2008, Oracle.  All rights reserved.Connected to an idle instance.

SQL> startup
ORACLE instance started.
Total System Global Area 2.6724E+10 bytes
Fixed Size      2160272 bytes
Variable Size   1.4764E+10 bytes
Database Buffers  1.1811E+10 bytes
Redo Buffers    146423808 bytes
Database mounted.
Database opened.

SQL> alter system set cluster_database=false scope=spfile;

System altered.

SQL> alter system set cluster_database_instances=1 scope=spfile;

System altered.

SQL> alter database disable thread 2;

Database altered.

5) Delete the unwanted thread and redo logfiles

SQL> select thread#, group# from v$log order by 1;

THREAD# GROUP#
———- ———-
1 5
1 6
1 18
1 16
1 14
1 13
1 10
2 12
2 15
2 11
2 17

THREAD# GROUP#
———- ———-
2 9
2 8
2 7
2 19

15 rows selected.

SQL> alter database drop logfile group 12;

Database altered.

SQL> alter database drop logfile group 15;

Database altered.

SQL> alter database drop logfile group 11;

Database altered.

SQL> alter database drop logfile group 17;

Database altered.

SQL> alter database drop logfile group 9;

Database altered.

SQL> alter database drop logfile group 8;

Database altered.

SQL> alter database drop logfile group 7;

Database altered.

SQL> alter database drop logfile group 19;

Database altered.

SQL> select thread#, group# from v$log order by 1;

THREAD# GROUP#
———- ———-
1 5
1 6
1 10
1 18
1 14
1 16
1 13

7 rows selected.

SQL>

6) Drop the unwanted undo tablespace

SQL> drop tablespace UNDOTBS2 including contents and datafiles;

Tablespace dropped.

7) Create pfile from spfile

SQL> create pfile from spfile;

File created.

SQL> shut immediate ;
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> exit

Disconnected from Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit ProductionWith the Partitioning, Real Application Clusters, OLAP, Data Miningand Real Application Testing options[oracle@host01 ~]$

8) Remove all references of second instance from the pfile and start the instance using pfile. Later you can again create spfile from pfile.

— removed all RAC2 references.

9) Startup the database and make sure all look good.
[oracle@host01 dbs]$ dba

SQL*Plus: Release 11.1.0.7.0 – Production on Wed Sep 16 20:09:28 2011

Copyright (c) 1982, 2008, Oracle. All rights reserved.

Connected to an idle instance.

SQL> create spfile from pfile;

File created.

SQL> startup
ORACLE instance started.

Total System Global Area 2.6724E+10 bytes
Fixed Size 2160272 bytes
Variable Size 1.4764E+10 bytes
Database Buffers 1.1811E+10 bytes
Redo Buffers 146423808 bytes
Database mounted.
Database opened.
SQL>

How to enable/disable RAC (ON/OFF)

Use the following steps to disable RAC (known as RAC OFF):

1. Log in as the Oracle software owner (which is typically the UNIX account oracle) in all nodes.
2. Shut down all the instances from all the nodes using a NORMAL or IMMEDIATE option.
3. Change the working directory to $ORACLE_HOME/rdbms/lib: cd $ORACLE_HOME/rdbms/lib
4. Run the following make command to relink the Oracle binaries without the RAC option: make -f ins_rdbms.mk rac_off

This normally runs for few minutes and should not pose any errors.
5. . Now relink the Oracle binaries: make -f ins_rdbms.mk ioracle

Now the Oracle binaries are relinked with the RAC OFF option. You may have to edit the init.ora or SPFILE parameters accordingly. If errors occur in step 4, you may need to contact Oracle Support and log a service request with the trace and log files.

Use the following steps to enable RAC (known as RAC ON):

1. Log in as the Oracle software owner (typically the UNIX account oracle) in all nodes.
2. Shut down all the instances from all the nodes using a NORMAL or IMMEDIATE option.
3. Change the working directory to $ORACLE_HOME/rdbms/lib:
4. cd $ORACLE_HOME/rdbms/lib Run the following make command to relink the Oracle binaries without the RAC option:
make -f ins_rdbms.mk rac_on

This normally runs for a few minutes and should not pose any errors.
5. Now relink the Oracle binaries:
make -f ins_rdbms.mk ioracle

Now the Oracle binaries are relinked with the RAC ON option. You may need to edit the init.ora or SPFILE parameters accordingly. If any errors occur in step 4, you may need to contact Oracle Support and log a service request with the trace and log files.

Sunday, 24 January 2016

Understanding and How To Gather & Backup ASM/ACFS Metadata

1. ASM metadata

An ASM instance manages metadata needed to make ASM files available to Oracle databases and other ASM clients. ASM metadata is stored in the disk groups – in the metadata blocks.

Some ASM metadata is at the fixed position in every ASM disk, and is referred to as physically addressed metadata. Other ASM metadata is organised in files (directories) and is referred to as virtually addressed metadata. The virtually addressed metadata files are managed like any other ASM files – they get mirrored as per the file type redundancy policy, are subject to rebalance and can grow as needed.

Each ASM disk has the metadata that may be relevant to that disk only or to the whole disk group. For example, the ASM disk header is relevant to that disk only, while  the Partnership and Status Table (PST) is relevant to the whole disk group.

2. Physically addressed metadata

The physical ASM metadata are the following structures:

  • Disk header
  • Free Space Table (FST)
  • Allocation Table (AT)
  • Partnership and Status Table (PST)

The disk header, allocation table and free space table are called physically addressed ASM metadata, because ASM instance refers to this data via a disk number and the block number within a disk. The partnership and status table is a special case, as ASM refers to it via allocation unit number, not via disk/block number.

Allocation units 0 on every ASM disk will always have the disk header (block 0), the Free Space Table (block 1) and the Allocation Table - in the rest of the allocation unit 0 blocks.

The ASM disk header contains the disk number, disk group redundancy, disk name, disk group name, disk size, etc.

The Free Space Table (FST) indicates which allocation table blocks may have free space in them. It contains an array of bit patterns indexed by allocation table block number. Location of the first block of the free space table is in the disk header field kfdhdb.fstlocn.

The ASM allocation table (AT) has one entry for every allocation unit (AU) on the disk. Location of the first block of the allocation table is in the disk header – field kfdhdb.altlocn.

The Partnership and Status Table (PST) contains the information about all ASM disks in the disk group – disk numbers, their status, their partner disks, heartbeat info, the failure group info, etc. Each ASM disk contains an allocation unit reserved for storing the PST - typically that is allocation unit number 1. Only few disks have the actual PST data – in external redundancy group we only have one PST table, in a normal redundancy disk group we have up to 3 copies of the PST, and in a high redundancy disk group we have up to 5 copies of the PST.

3. Virtually addressed metadata

The rest of the disk group metadata is organized in ASM metadata files (directories). That is how ASM keeps track of the database files, ASM disks, ASM instance recovery data, etc. An ASM metadata file is maintained like any other file stored in ASM, which means that its extents are allocated across all available disks, that they are mirrored (in a normal and high redundancy disk group) and that its extents can be moved around when the disk group is rebalanced. ASM file numbers between 1 and 255 are reserved for ASM metadata files.
The virtually addressed metadata are the following structures:
  • File Directory
  • Disk Directory
  • Active Change Directory (ACD)
  • Continuing Operations Directory (COD)
  • Template Directory
  • Alias Directory
  • ADVM Volume Directory
  • Disk Used Space Directory
  • Attributes Directory
  • ASM User Directory and User Group Directory
  • Staleness Directory and Staleness Registry
  • Password directory
ASM metadata file names and their locations are not externalized and cannot be queried directly, but we can peek at them via X$KFFXP fixed table. The following query shows the ASM metadata file numbers, their names and allocation unit count for each file in a disk group (in this case in the disk group number 2):
SQL> select
  NUMBER_KFFXP "ASM file number",
  DECODE (NUMBER_KFFXP, 1, 'File directory',
    2, 'Disk directory',
    3, 'Active change directory',
    4, 'Continuing operations directory',
    5, 'Template directory',
    6, 'Alias directory',
    7, 'AVD volume file directory',
    8, 'Disk free space directory',
    9, 'Attributes directory',
    10, 'ASM user directory',
    11, 'ASM user group directory',
    12, 'Staleness directory',
    13, 'Password directory',
    253, 'ASM spfile',
    254, 'Stale bitmap space registry ',
    255, 'Oracle Cluster Repository ')
  "ASM metadata file name",
  count(AU_KFFXP) "Allocation units"
from X$KFFXP
where GROUP_KFFXP = 2    -- disk group 2
  and NUMBER_KFFXP < 256 -- ASM metadata files
group by NUMBER_KFFXP
;

ASM file number ASM metadata file name          Allocation units
--------------- ------------------------------- ----------------
              1 File directory                                 3
              2 Disk directory                                 3
              3 Active change directory                       69
              4 Continuing operations directory                6
              5 Template directory                             3
              6 Alias directory                                3
              8 Disk free space directory                      3
              9 Attributes directory                           3
             12 Staleness directory                            3
            253 ASM spfile                                     2
            254 Stale bitmap space registry                    3
            255 Oracle Cluster Repository                     83

12 rows selected.

SQL>
As we can see, the disk group does not have to have all metadata files.

4. ASM metadata blocks

ASM metadata is organized in ASM blocks, with the default block size of 4KB. Note that ASM blocks are independent and not related to Oracle database blocks or their sizes. An ASM block consists of a common block header and the rest of ASM metadata that would be block type specific.
The following are the ASM metadata block types:
  • KFBTYP_DISKHEAD - The ASM disk header - the very first block in every ASM disk. A copy of this block will be in the second last Partnership and Status Table (PST) block (in ASM version 11.1.0.7 and later). The copy of this block will also be in the very first block in Allocation Unit 11, for disk groups with COMPATIBLE.ASM=12.1 or higher.
  • KFBTYP_FREESPC - The Free Space Table block.
  • KFBTYP_ALLOCTBL - The Allocation Table block.
  • KFBTYP_PST_META - The Partnership and Status Table (PST) block. The PST blocks 0 and 1 will be of this type.
  • KFBTYP_PST_DTA - The PST blocks with the actual PST data.
  • KFBTYP_PST_NONE - The PST block with no PST data. Remember that Allocation Unit 1 (AU1) on every disk is reserved for the PST, but only some disks will have the PST data.
  • KFBTYP_HBEAT - The heartbeat block, in the PST.
  • KFBTYP_FILEDIR - The File Directory block.
  • KFBTYP_INDIRECT - The Indirect File Directory block, containing a pointer to another file directory block.
  • KFBTYP_LISTHEAD - The Disk Directory block. The very first block in the ASM disk directory. The field kfdhdb.f1b1locn in the ASM disk header will point the the allocation unit whose block 0 will be of this type.
  • KFBTYP_DISKDIR - The rest of the blocks in the Disk Directory will be of this type.
  • KFBTYP_ACDC - The Active Change Directory (ACD) block. The very first block of the ACD will be of this type.
  • KFBTYP_CHNGDIR - The blocks with the actual ACD data.
  • KFBTYP_COD_BGO - The Continuing Operations Directory (COD) block for background operations data.
  • KFBTYP_COD_RBO - The COD block that marks the rollback operations data.
  • KFBTYP_COD_DATA - The COD block with the actual rollback operations data.
  • KFBTYP_TMPLTDIR - The Template Directory block.
  • KFBTYP_ALIASDIR - The Alias Directory block.
  • KFBTYP_SR - The Staleness Registry block.
  • KFBTYP_STALEDIR - The Staleness Directory block.
  • KFBTYP_VOLUMEDIR -The ADVM Volume Directory block.
  • KFBTYP_ATTRDIR -The Attributes Directory block.
  • KFBTYP_USERDIR - The User Directory block.
  • KFBTYP_GROUPDIR - The User Group Directory block.
  • KFBTYP_USEDSPC - The Disk Used Space Directory block.
  • KFBTYP_ASMSPFALS -The ASM spfile alias block.
  • KFBTYP_PASWDDIR - The ASM Password Directory block.
  • KFBTYP_INVALID - Not an ASM metadata block.
Note that the KFBTYP_INVALID is not an actual block type stored in ASM metadata block. Instead, ASM will return this if it encounters a block where the type is not one of the valid ASM metadata block types. For example if the ASM disk header is corrupt, say zeroed out, ASM will report it as KFBTYP_INVALID. We will also see the same when reading such block with the kfed tool.
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
1) Connect to your ASM instance(s) and execute the first 3 scripts below (on each node if this is RAC configuration).

2) Also, execute the script #4  (located below) through ASMCMD to gather complementary metadata information (on each node if this is RAC configuration)
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Script #1 (Generic ASM metadata):

SPOOL ASM<#>_GENERIC_ASM_METADATA.html
-- ASM VERSIONS 10.1, 10.2, 11.1,  11.2 & 12.1
SET MARKUP HTML ON
SET ECHO ON

SET PAGESIZE 200

ALTER SESSION SET NLS_DATE_FORMAT='DD-MON-YYYY HH24:MI:SS';

SELECT 'THIS ASM REPORT WAS GENERATED AT: ==)> ' , SYSDATE " "  FROM DUAL;
SELECT 'INSTANCE NAME: ==)> ' , INSTANCE_NAME " " FROM V$INSTANCE;

SELECT 'HOSTNAME ASSOCIATED WITH THIS ASM INSTANCE: ==)> ' , MACHINE " " FROM V$SESSION WHERE PROGRAM LIKE '%SMON%';

SELECT * FROM V$INSTANCE;

SELECT * FROM GV$INSTANCE;

SELECT * FROM V$ASM_DISKGROUP;

SELECT * FROM  V$ASM_DISK ORDER BY GROUP_NUMBER,DISK_NUMBER;

SELECT SUBSTR(D.NAME,1,16) AS ASMDISK, D.MOUNT_STATUS, D.STATE,
     DG.NAME AS DISKGROUP FROM V$ASM_DISKGROUP DG, V$ASM_DISK D
     WHERE DG.GROUP_NUMBER = D.GROUP_NUMBER;


SELECT * FROM V$ASM_CLIENT;

 SELECT DG.NAME AS DISKGROUP, SUBSTR(C.INSTANCE_NAME,1,12) AS INSTANCE,
    SUBSTR(C.DB_NAME,1,12) AS DBNAME, SUBSTR(C.SOFTWARE_VERSION,1,12) AS SOFTWARE,
    SUBSTR(C.COMPATIBLE_VERSION,1,12) AS COMPATIBLE
    FROM V$ASM_DISKGROUP DG, V$ASM_CLIENT C
    WHERE DG.GROUP_NUMBER = C.GROUP_NUMBER;

SELECT * FROM V$ASM_ATTRIBUTE;

SELECT * FROM V$ASM_OPERATION;
SELECT * FROM GV$ASM_OPERATION;


SELECT * FROM V$VERSION;


SELECT * FROM   V$ASM_ACFSSNAPSHOTS;
SELECT * FROM   V$ASM_ACFSVOLUMES;
SELECT * FROM   V$ASM_FILESYSTEM;
SELECT * FROM   V$ASM_VOLUME;
SELECT * FROM   V$ASM_VOLUME_STAT;

SELECT * FROM   V$ASM_USER;
SELECT * FROM   V$ASM_USERGROUP;
SELECT * FROM   V$ASM_USERGROUP_MEMBER;

SELECT * FROM   V$ASM_DISK_IOSTAT;
SELECT * FROM   V$ASM_DISK_STAT;
SELECT * FROM   V$ASM_DISKGROUP_STAT;

SELECT * FROM   V$ASM_TEMPLATE;

SHOW PARAMETER

SHOW SGA

!echo "SELECT '" > /tmp/GPNPTOOL.SQL 2> /dev/null
! $ORACLE_HOME/bin/gpnptool get >> /tmp/GPNPTOOL.SQL 2>> /dev/null
!echo "'  FROM DUAL;" >> /tmp/GPNPTOOL.SQL 2>> /dev/null
! cat /tmp/GPNPTOOL.SQL
SET ECHO OFF

--DISPLAYS INFORMATION ABOUT THE CONTENTS OF THE SPFILE.
SELECT *  FROM  V$SPPARAMETER  ORDER BY 2;
SELECT *  FROM  GV$SPPARAMETER  ORDER BY 3;

--DISPLAYS INFORMATION ABOUT THE INITIALIZATION PARAMETERS THAT ARE CURRENTLY IN EFFECT IN THE INSTANCE.
SELECT *  FROM  V$SYSTEM_PARAMETER ORDER BY 2;
SELECT *  FROM  GV$SYSTEM_PARAMETER ORDER BY 3;


-- 12C ACFS VIEWS

SELECT * FROM  V$ASM_ACFS_ENCRYPTION_INFO;
SELECT * FROM  V$ASM_ACFSREPL;
SELECT * FROM  V$ASM_ACFSREPLTAG;
SELECT * FROM  V$ASM_ACFS_SEC_ADMIN;
SELECT * FROM  V$ASM_ACFS_SEC_CMDRULE;
SELECT * FROM  V$ASM_ACFS_SEC_REALM;
SELECT * FROM  V$ASM_ACFS_SEC_REALM_FILTER;
SELECT * FROM  V$ASM_ACFS_SEC_REALM_GROUP;
SELECT * FROM  V$ASM_ACFS_SEC_REALM_USER;
SELECT * FROM  V$ASM_ACFS_SEC_RULE;
SELECT * FROM  V$ASM_ACFS_SEC_RULESET;
SELECT * FROM  V$ASM_ACFS_SEC_RULESET_RULE;
SELECT * FROM  V$ASM_ACFS_SECURITY_INFO;
SELECT * FROM  V$ASM_ACFSTAG;

-- 12C ASM AUDIT VIEWS

SELECT * FROM  V$ASM_AUDIT_CLEAN_EVENTS;
SELECT * FROM  V$ASM_AUDIT_CLEANUP_JOBS;
SELECT * FROM  V$ASM_AUDIT_CONFIG_PARAMS;
SELECT * FROM  V$ASM_AUDIT_LAST_ARCH_TS;

-- 12C ASM ESTIMATE VIEW

SELECT * FROM  V$ASM_ESTIMATE;
SELECT * FROM  GV$ASM_ESTIMATE;


SPOOL OFF

EXIT

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Script #2 (ASM Files & ASM Alias):


 spool asm<#>_alias+files.html
-- ASM Versions 10.1, 10.2, 11.1,  11.2 & 12.1
SET MARKUP HTML ON
set echo on

set pagesize 200

COLUMN BYTES FORMAT  9999999999999999

alter session set nls_date_format='DD-MON-YYYY HH24:MI:SS';

select 'THIS ASM REPORT WAS GENERATED AT: ==)> ' , sysdate " "  from dual;


select 'HOSTNAME ASSOCIATED WITH THIS ASM INSTANCE: ==)> ' , MACHINE " " from v$session where program like '%SMON%';

select * from v$asm_alias;

select * from v$asm_file;

show parameter asm
show parameter cluster
show parameter instance_type
show parameter instance_name
show parameter spfile

show sga

spool off

exit

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~


Script #3 (ASM Full Path Alias Directory):

spool asm<#>_full_path_alias_directory.html
-- ASM Versions 10.1, 10.2, 11.1,  11.2 & 12.1
SET MARKUP HTML ON
set echo on

set pagesize 200

alter session set nls_date_format='DD-MON-YYYY HH24:MI:SS';

select 'THIS ASM REPORT WAS GENERATED AT: ==)> ' , sysdate " "  from dual;


select 'HOSTNAME ASSOCIATED WITH THIS ASM INSTANCE: ==)> ' , MACHINE " " from v$session where program like '%SMON%';

SELECT CONCAT('+'||GNAME, SYS_CONNECT_BY_PATH(ANAME, '/'))
 FULL_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
 CONNECT BY PRIOR RINDEX = PINDEX;


spool off

exit

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Script #4 (ASMCMD commands to gather complementary metadata information):


################################################################################################
### Script Name:  asmcmd_script.sh                                                                                     ###   
################################################################################################
###  The next script generates additional ASM metadata information thru the ASMCMD interface ###
################################################################################################
###  Author: Esteban D. Bernal                                                               ###
################################################################################################
###  Property: Oracle Corporation                                                            ###
################################################################################################


echo "ASMCMD commands to gather complementary metadata information:"    > /tmp/asmcmd_script.out                2> /tmp/asmcmd_script.out
echo "=================================="       >> /tmp/asmcmd_script.out               2>> /tmp/asmcmd_script.out
echo "                                  "       >> /tmp/asmcmd_script.out               2>> /tmp/asmcmd_script.out
asmcmd -p ls -ls        >> /tmp/asmcmd_script.out               2>> /tmp/asmcmd_script.out
echo "=================================="       >> /tmp/asmcmd_script.out               2>> /tmp/asmcmd_script.out
echo "                                  "       >> /tmp/asmcmd_script.out               2>> /tmp/asmcmd_script.out
asmcmd -p lsattr        >> /tmp/asmcmd_script.out               2>> /tmp/asmcmd_script.out
echo "=================================="       >> /tmp/asmcmd_script.out               2>> /tmp/asmcmd_script.out
echo "                                  "       >> /tmp/asmcmd_script.out               2>> /tmp/asmcmd_script.out
asmcmd -p lsct  >> /tmp/asmcmd_script.out               2>> /tmp/asmcmd_script.out
echo "=================================="       >> /tmp/asmcmd_script.out               2>> /tmp/asmcmd_script.out
echo "                                  "       >> /tmp/asmcmd_script.out               2>> /tmp/asmcmd_script.out
asmcmd -p lsdg  >> /tmp/asmcmd_script.out               2>> /tmp/asmcmd_script.out
echo "=================================="       >> /tmp/asmcmd_script.out               2>> /tmp/asmcmd_script.out
echo "                                  "       >> /tmp/asmcmd_script.out               2>> /tmp/asmcmd_script.out
asmcmd -p lsdsk >> /tmp/asmcmd_script.out               2>> /tmp/asmcmd_script.out
echo "=================================="       >> /tmp/asmcmd_script.out               2>> /tmp/asmcmd_script.out
echo "                                  "       >> /tmp/asmcmd_script.out               2>> /tmp/asmcmd_script.out
asmcmd -p lsof  >> /tmp/asmcmd_script.out               2>> /tmp/asmcmd_script.out
echo "=================================="       >> /tmp/asmcmd_script.out               2>> /tmp/asmcmd_script.out
echo "                                  "       >> /tmp/asmcmd_script.out               2>> /tmp/asmcmd_script.out
asmcmd -p lsod  >> /tmp/asmcmd_script.out               2>> /tmp/asmcmd_script.out
echo "=================================="       >> /tmp/asmcmd_script.out               2>> /tmp/asmcmd_script.out
echo "                                  "       >> /tmp/asmcmd_script.out               2>> /tmp/asmcmd_script.out
asmcmd -p iostat        >> /tmp/asmcmd_script.out               2>> /tmp/asmcmd_script.out
echo "=================================="       >> /tmp/asmcmd_script.out               2>> /tmp/asmcmd_script.out
echo "                                  "       >> /tmp/asmcmd_script.out               2>> /tmp/asmcmd_script.out
asmcmd -p dsget >> /tmp/asmcmd_script.out               2>> /tmp/asmcmd_script.out
echo "=================================="       >> /tmp/asmcmd_script.out               2>> /tmp/asmcmd_script.out
echo "                                  "       >> /tmp/asmcmd_script.out               2>> /tmp/asmcmd_script.out
asmcmd -p lsop  >> /tmp/asmcmd_script.out               2>> /tmp/asmcmd_script.out
echo "=================================="       >> /tmp/asmcmd_script.out               2>> /tmp/asmcmd_script.out
echo "                                  "       >> /tmp/asmcmd_script.out               2>> /tmp/asmcmd_script.out
asmcmd -p spget >> /tmp/asmcmd_script.out               2>> /tmp/asmcmd_script.out
echo "=================================="       >> /tmp/asmcmd_script.out               2>> /tmp/asmcmd_script.out
echo "                                  "       >> /tmp/asmcmd_script.out               2>> /tmp/asmcmd_script.out
asmcmd -p  lstmpl       >> /tmp/asmcmd_script.out               2>> /tmp/asmcmd_script.out
echo "=================================="       >> /tmp/asmcmd_script.out               2>> /tmp/asmcmd_script.out
echo "                                  "       >> /tmp/asmcmd_script.out               2>> /tmp/asmcmd_script.out
asmcmd -p   lsusr       >> /tmp/asmcmd_script.out               2>> /tmp/asmcmd_script.out
echo "=================================="       >> /tmp/asmcmd_script.out               2>> /tmp/asmcmd_script.out
echo "                                  "       >> /tmp/asmcmd_script.out               2>> /tmp/asmcmd_script.out
asmcmd -p  lsgrp        >> /tmp/asmcmd_script.out               2>> /tmp/asmcmd_script.out
echo "=================================="       >> /tmp/asmcmd_script.out               2>> /tmp/asmcmd_script.out
echo "                                  "       >> /tmp/asmcmd_script.out               2>> /tmp/asmcmd_script.out
asmcmd -p   lspwusr     >> /tmp/asmcmd_script.out               2>> /tmp/asmcmd_script.out
echo "=================================="       >> /tmp/asmcmd_script.out               2>> /tmp/asmcmd_script.out
echo "                                  "       >> /tmp/asmcmd_script.out               2>> /tmp/asmcmd_script.out
asmcmd -p   volinfo -a  >> /tmp/asmcmd_script.out               2>> /tmp/asmcmd_script.out
echo "=================================="       >> /tmp/asmcmd_script.out               2>> /tmp/asmcmd_script.out
echo "                                  "       >> /tmp/asmcmd_script.out               2>> /tmp/asmcmd_script.out
##############################################################################################################

Note: On Windows environments the “2> /tmp/asmcmd_script.out” or “2>> /tmp/asmcmd_script.out” statements is not required, thus it can be omitted.

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
[grid@node02 ~]$ . oraenv
ORACLE_SID = [+ASM] ? +ASM
The Oracle base remains unchanged with value /u01/app/grid
[grid@node02 ~]$
[grid@node02 ~]$ ./asmcmd_script.sh
[grid@node02 ~]$


Output file:

[grid@node02 ~]$ ls -l /tmp/asmcmd_script.out
-rw-r--r-- 1 grid oinstall 15873 Nov  6 11:03 /tmp/asmcmd_script.out


 Ref: