Showing posts with label ASM. Show all posts
Showing posts with label ASM. Show all posts

Thursday, 2 September 2021

OCR / Vote disk Maintenance Operations: (ADD/REMOVE/REPLACE/MOVE) ( Doc ID 428681.1 )

 refer metalink OCR / Vote disk Maintenance Operations: (ADD/REMOVE/REPLACE/MOVE) ( Doc ID 428681.1 )

- No comments:
Labels:

Friday, 23 August 2019

asm lib configuration on mapper design



ORACLEASM_SCANORDER="mpath dm" (OR ORACLEASM_SCANORDER="dm")

I found this on various documents on oracle site , still we will crosscheck with oracle once .



Also the below document

(How to Force ASM to Scan the Multipathed Device First using ASMLIB/oracleasm on RHEL 5/OL 5 and RHEL 6/OL 6 (Doc ID 1679409.1)


ASMLib/oracleasm can only be implemented on Linux OS.  When using multipathing, you want to FORCE oracleasm to scan the multipathed devices first

Below is the process mentioned

On all nodes:
1)  Shutdown all (DB and ASM):
You should stop all before restart ASMLib (eg: DB, ASM and HAS (crsctl stop crs for RAC) if you are in 11.2 - crsctl stop has) 

Then, on each node, one at a time, do the following steps that are dependent on the type of multipathing software:
2)  For backup, make a copy of your current /etc/sysconfig/oracleasm and /etc/sysconfig/oracleasm-_dev_oracleasm files
a) If using DM-Multipathing or Linux OS Native Multipathing:
1. Then, you want to change the following parameters in the  /etc/sysconfig/oracleasm-_dev_oracleasm file to which the /etc/sysconfig/oracleasm file is really linked to:

For example:

lrwxrwxrwx 1 root root 24 Jun 13 09:58 /etc/sysconfig/oracleasm -> oracleasm-_dev_oracleasm

# ORACLEASM_SCANORDER: Matching patterns to order disk scanning
ORACLEASM_SCANORDER="mpath dm" (OR ORACLEASM_SCANORDER="dm")

# ORACLEASM_SCANEXCLUDE: Matching patterns to exclude disks from scan
ORACLEASM_SCANEXCLUDE="sd"



 b) If using  EMC PowerPath Multipathing:
2. Then, you want to change the following parameters in the  /etc/sysconfig/oracleasm-_dev_oracleasm file to which the /etc/sysconfig/oracleasm file is really linked to:

For example:

lrwxrwxrwx 1 root root 24 Jun 13 09:58 /etc/sysconfig/oracleasm -> oracleasm-_dev_oracleasm

# ORACLEASM_SCANORDER: Matching patterns to order disk scanning
ORACLEASM_SCANORDER="emcpower"

# ORACLEASM_SCANEXCLUDE: Matching patterns to exclude disks from scan
ORACLEASM_SCANEXCLUDE="sd"

- No comments:
Labels:

Thursday, 4 October 2018

ASM spfile

ASM spfile in a disk group

Starting with ASM version 11.2, the ASM spfile can be stored in an ASM disk group. Indeed, during a new ASM installation, the Oracle Universal Installer (OUI) will place the ASM spfile in the disk group that gets created during the installation. This is true for both Oracle Restart (single instance environments) and Cluster installations. It should be noted that the first disk group created during the installation is the default spfile location, but not a requirement. The spfile can still be on a file system, in say $ORACLE_HOME/dbs directory.

New ASMCMD commands

To support this feature, new ASMCMD commands were introduced to back up, copy and move the ASM spfile. The commands are:
  • spbackup - backs up an ASM spfile to a backup file. The backup file is not a special file type and is not identified as an spfile.
  • spcopy - copies an ASM spfile from the source location to an spfile in the destination location.
  • spmove - moves an ASM spfile from source to destination and automatically updates the GPnP profile.

The SQL commands CREATE PFILE FROM SPFILE and CREATE SPFILE FROM PFILE are still valid for the ASM spfile stored in the disk group.

ASM spfile in disk group DATA

In my environment, the ASM spfile is (somewhere) in the disk group DATA. Let's find it:

$ asmcmd find --type ASMPARAMETERFILE +DATA "*"
+DATA/ASM/ASMPARAMETERFILE/REGISTRY.253.822856169

As we can see, the ASM spfile is in a special location and it has ASM file number 253. The ASM spfile stored in the disk group is a registry file, and will always be the ASM metadata file number 253.

Of course, we see the same thing from the sqlplus:

$ sqlplus / as sysasm

SQL> show parameter spfile

NAME   TYPE   VALUE
------ ------ -------------------------------------------------
spfile string +DATA/ASM/ASMPARAMETERFILE/registry.253.822856169

SQL>

Let's make a backup of that ASM spfile.

$ asmcmd spbackup +DATA/ASM/ASMPARAMETERFILE/REGISTRY.253.822856169 /tmp/ASMspfile.backup

And check out the contents of the file:

$ strings /tmp/ASMspfile.backup
+ASM.__oracle_base='/u01/app/grid'#ORACLE_BASE set from in memory value
+ASM.asm_diskgroups='RECO','ACFS'#Manual Mount
*.asm_power_limit=1
*.large_pool_size=12M
*.remote_login_passwordfile='EXCLUSIVE'

As we can see, this is a copy of the ASM spfile, that includes the parameters and associated comments.

ASM spfile discovery

So, how can the ASM instance read the spfile on startup, if the spfile is in a disk group that is not mounted yet? Not only that - the ASM doesn't really know which disk group has the spfile, or even if the spfile is in a disk group. And what is the value of the ASM discovery string?

The ASM Admin guide says this on the topic:

When an Oracle ASM instance searches for an initialization parameter file, the search order is:
  1. The location of the initialization parameter file specified in the Grid Plug and Play (GPnP) profile.
  2. If the location has not been set in the GPnP profile, then the search order changes to:
    1. SPFILE in the Oracle ASM instance home (e.g. $ORACLE_HOME/dbs/spfile+ASM.ora)
    2. PFILE in the Oracle ASM instance home

This does not tell us anything about the ASM discovery string, but at least it tells us about the spfile and the GPnP profile. It turns out the ASM discovery string is also in the GPnP profile. Here are the values from an Exadata environment:

$ gpnptool getpval -p=profile.xml -asm_dis -o-
o/*/*
$ gpnptool getpval -p=profile.xml -asm_spf -o-
+DBFS_DG/spfileASM.ora

There is no GPnP profile in a single instance set up, so this information is in the ASM resource (ora.asm), stored in the Oracle Local Repository (OLR). Here are the values from a single instance environment:

$ crsctl stat res ora.asm -p | egrep "ASM_DISKSTRING|SPFILE"
ASM_DISKSTRING=
SPFILE=+DATA/ASM/ASMPARAMETERFILE/registry.253.822856169

So far so good. Now the ASM knows where to look for ASM disks and where the spfile is. But the disk group is not mounted yet, as the ASM instance still hasn't started up, so how can ASM read the spfile?

The trick is in the ASM disk headers. To support the ASM spfile in a disk group, two new fields were added to the ASM disk header:
  • kfdhdb.spfile - Allocation unit number of the ASM spfile.
  • kfdhdb.spfflg - ASM spfile flag. If this value is 1, the ASM spfile is on this disk in allocation unit kfdhdb.spfile.

As part of the disk discovery process, the ASM instance reads the disk headers and looks for the spfile information. Once it finds the disks that have the spfile, it can read the actual initialization parameters.

Let's have a look at my disk group DATA. First check the disk group state and redundancy

$ asmcmd lsdg -g DATA | cut -c1-26
Inst_ID  State    Type
     1  MOUNTED  NORMAL

The disk group is mounted and the redundancy is normal. This means the ASM spfile will be mirrored, so we should see two disks with kfdhdb.spfileand kfdhdb.spfflg values set. Let's have a look:

$ for disk in `asmcmd lsdsk -G DATA --suppressheader`
> do
> echo $disk
> kfed read $disk | grep spf
> done
/dev/sdc1
kfdhdb.spfile:                       46 ; 0x0f4: 0x0000002e
kfdhdb.spfflg:                        1 ; 0x0f8: 0x00000001
/dev/sdd1
kfdhdb.spfile:                     2212 ; 0x0f4: 0x000008a4
kfdhdb.spfflg:                        1 ; 0x0f8: 0x00000001
/dev/sde1
kfdhdb.spfile:                        0 ; 0x0f4: 0x00000000
kfdhdb.spfflg:                        0 ; 0x0f8: 0x00000000

As we can see, two disks have the ASM spfile.

Let's check the contents of the Allocation Unit 46 on disk /dev/sdc1:

$ dd if=/dev/sdc1 bs=1048576 skip=46 count=1 | strings
+ASM.__oracle_base='/u01/app/grid'#ORACLE_BASE set from in memory value
+ASM.asm_diskgroups='RECO','ACFS'#Manual Mount
*.asm_power_limit=1
*.large_pool_size=12M
*.remote_login_passwordfile='EXCLUSIVE'
1+0 records in
1+0 records out
1048576 bytes (1.0 MB) copied, 0.0352732 s, 29.7 MB/s

The AU 46 on disk /dev/sdc1 indeed contains the ASM spfile.

ASM spfile alias block

In addition to the new ASM disk header fields, there is a new metadata block type - KFBTYP_ASMSPFALS - that describes the ASM spfile alias. The ASM spfile alias block will be the last block in the ASM spfile.

Let's have a look at the last block of the Allocation Unit 46:

$ kfed read /dev/sdc1 aun=46 blkn=255
kfbh.endian:                          1 ; 0x000: 0x01
kfbh.hard:                          130 ; 0x001: 0x82
kfbh.type:                           27 ; 0x002: KFBTYP_ASMSPFALS
kfbh.datfmt:                          1 ; 0x003: 0x01
kfbh.block.blk:                     255 ; 0x004: blk=255
kfbh.block.obj:                     253 ; 0x008: file=253
kfbh.check:                   806373865 ; 0x00c: 0x301049e9
kfbh.fcn.base:                        0 ; 0x010: 0x00000000
kfbh.fcn.wrap:                        0 ; 0x014: 0x00000000
kfbh.spare1:                          0 ; 0x018: 0x00000000
kfbh.spare2:                          0 ; 0x01c: 0x00000000
kfspbals.incarn:              822856169 ; 0x000: 0x310bc9e9
kfspbals.blksz:                     512 ; 0x004: 0x00000200
kfspbals.size:                        3 ; 0x008: 0x0003
kfspbals.path.len:                    0 ; 0x00a: 0x0000
kfspbals.path.buf:                      ; 0x00c: length=0

There is not much in this metadata block. Most of the entries have the block header info (fields kfbh.*). The actual ASM spfile alias data (fields kfspbals.*) has only few entries. The spfile file incarnation (822856169) is part of the file name (REGISTRY.253.822856169), the block size is 512 (bytes) and the file size is 3 blocks. The path info is empty, meaning I don't actually have the ASM spfile alias.

Let's create one. I will first create a pfile from the existing spfile and then create the spfile alias from that pfile.

$ sqlplus / as sysasm

SQL> create pfile='/tmp/pfile+ASM.ora' from spfile;

File created.

SQL> shutdown abort;
ASM instance shutdown

SQL> startup pfile='/tmp/pfile+ASM.ora';
ASM instance started

Total System Global Area 1135747072 bytes
Fixed Size                  2297344 bytes
Variable Size            1108283904 bytes
ASM Cache                  25165824 bytes
ASM diskgroups mounted

SQL> create spfile='+DATA/spfileASM.ora' from pfile='/tmp/pfile+ASM.ora';

File created.

SQL> exit

Looking for the ASM spfile again shows two entries:

$ asmcmd find --type ASMPARAMETERFILE +DATA "*"
+DATA/ASM/ASMPARAMETERFILE/REGISTRY.253.843597139
+DATA/spfileASM.ora

We now see the ASM spfile itself (REGISTRY.253.843597139) and its alias (spfileASM.ora). Having a closer look at spfileASM.ora confirms this is indeed the alias for the registry file:

$ asmcmd ls -l +DATA/spfileASM.ora
Type              Redund  Striped  Time             Sys  Name
ASMPARAMETERFILE  MIRROR  COARSE   MAR 30 20:00:00  N    spfileASM.ora => +DATA/ASM/ASMPARAMETERFILE/REGISTRY.253.843597139

Check the ASM spfile alias block now:

$ kfed read /dev/sdc1 aun=46 blkn=255
kfbh.endian:                          1 ; 0x000: 0x01
kfbh.hard:                          130 ; 0x001: 0x82
kfbh.type:                           27 ; 0x002: KFBTYP_ASMSPFALS
kfbh.datfmt:                          1 ; 0x003: 0x01
kfbh.block.blk:                     255 ; 0x004: blk=255
kfbh.block.obj:                     253 ; 0x008: file=253
kfbh.check:                  2065104480 ; 0x00c: 0x7b16fe60
kfbh.fcn.base:                        0 ; 0x010: 0x00000000
kfbh.fcn.wrap:                        0 ; 0x014: 0x00000000
kfbh.spare1:                          0 ; 0x018: 0x00000000
kfbh.spare2:                          0 ; 0x01c: 0x00000000
kfspbals.incarn:              843597139 ; 0x000: 0x32484553
kfspbals.blksz:                     512 ; 0x004: 0x00000200
kfspbals.size:                        3 ; 0x008: 0x0003
kfspbals.path.len:                   13 ; 0x00a: 0x000d
kfspbals.path.buf:        spfileASM.ora ; 0x00c: length=13

Now we see that the alias file name appears in the ASM spfile alias block. Note the new incarnation number, as this is a new ASM spfile, created from the pfile.

Conclusion

Starting with ASM version 11.2, the ASM spfile can be stored in an ASM disk group. To support this feature, we now have new ASMCMD commands and, under the covers, we have new ASM metadata structures.
- No comments:
Labels:

Wednesday, 26 September 2018

Migrating a DiskGroup to New Disk(s)

Migrating a DiskGroup to New Disk(s)




After the previous demonstration of adding a new DiskGroup, I now demonstrate migrating the DiskGroup to a new pair of disks.

First, I create a table in the Tablespace on that DiskGroup.

[root@node1 ~]# su - oracle
-sh-3.2$ sqlplus

SQL*Plus: Release 11.2.0.2.0 Production on Sun Aug 24 22:17:28 2014

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

Enter user-name: hemant/hemant

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options

SQL> create table new_tbs_tbl                        
  2  tablespace new_tbs
  3  as select * from dba_objects 
  4  /

Table created.

SQL> select segment_name, bytes/1048576
  2  from user_segments
  3  where tablespace_name = 'NEW_TBS'
  4  /

SEGMENT_NAME
--------------------------------------------------------------------------------
BYTES/1048576
-------------
NEW_TBS_TBL
            9


SQL> select file_name, bytes/1048576
  2  from dba_data_files
  3  where tablespace_name = 'NEW_TBS'
  4  /

FILE_NAME
--------------------------------------------------------------------------------
BYTES/1048576
-------------
+DATA3/racdb/datafile/new_tbs.256.855792859
          100


SQL>


Next, I verify that the DiskGroup is currently on disk asmdisk.7 and that the two new disks that I plan to migrate the DiskGroup to are available as asmdisk.8 and asmdisk.9  (yes, unfortunately, they are on /fra, instead of /data1 or /data2 because I have run out of disk space in /data1 and /data2).
This I do from node1 :

SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options
-sh-3.2$ exit
logout

[root@node1 ~]# 
[root@node1 ~]# su - grid
-sh-3.2$ sqlplus 

SQL*Plus: Release 11.2.0.2.0 Production on Sun Aug 24 22:22:32 2014

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

Enter user-name: / as sysasm

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - Production
With the Real Application Clusters and Automatic Storage Management options

SQL> l
  1  select d.name, d.path
  2  from v$asm_disk d, v$asm_diskgroup g
  3  where d.group_number=g.group_number
  4* and g.name = 'DATA3'
SQL> /

NAME
------------------------------
PATH
--------------------------------------------------------------------------------
DATA3_0000
/data1/asmdisk.7


SQL> 
SQL> !sh
sh-3.2$ ls -l /fra/asmdisk*
-rwxrwxr-x 1 grid oinstall 1024000000 Aug 24 22:06 /fra/asmdisk.8
-rwxrwxr-x 1 grid oinstall 1024000000 Aug 24 22:07 /fra/asmdisk.9
sh-3.2$


Note how the ownership and permissions are set for the two new disks (see my previous post).

I now add the two new disks.

sh-3.2$ exit
exit

SQL> show parameter power

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
asm_power_limit                      integer     1
SQL> alter diskgroup data3  add disk '/fra/asmdisk.8', '/fra/asmdisk.9';

Diskgroup altered.

SQL> 
SQL> select * from v$asm_operation;

GROUP_NUMBER OPERA STAT      POWER     ACTUAL      SOFAR   EST_WORK   EST_RATE
------------ ----- ---- ---------- ---------- ---------- ---------- ----------
EST_MINUTES ERROR_CODE
----------- --------------------------------------------
           3 REBAL RUN           1          1          1        101         60
          1


SQL>


With ASM_POWERLIMIT set to 1, Oracle ASM automatically starts a REBALANCE operation.  However, since I did *not* drop the existing asmdisk.7, Oracle will still continue to use it.

After a while, I confirm that the REBALANCE has completed.  I can now drop asmdisk.7.  Unfortunately, this will trigger a new REBALANCE !

SQL> l
  1* select * from v$asm_operation
SQL> /

no rows selected

SQL> 
SQL> l
  1  select d.name, d.path
  2  from v$asm_disk d, v$asm_diskgroup g
  3  where d.group_number=g.group_number
  4* and g.name = 'DATA3'
SQL> /

NAME
------------------------------
PATH
--------------------------------------------------------------------------------
DATA3_0000
/data1/asmdisk.7

DATA3_0002
/fra/asmdisk.9

DATA3_0001
/fra/asmdisk.8


SQL> 
SQL> alter diskgroup data3 drop disk '/data1/asmdisk.7';
alter diskgroup data3 drop disk '/data1/asmdisk.7'
*
ERROR at line 1:
ORA-15032: not all alterations performed
ORA-15054: disk "/DATA1/ASMDISK.7" does not exist in diskgroup "DATA3"


SQL> alter diskgroup data3 drop disk 'DATA3_0000';

Diskgroup altered.

SQL> 
SQL> select * from v$asm_operation;

GROUP_NUMBER OPERA STAT      POWER     ACTUAL      SOFAR   EST_WORK   EST_RATE
------------ ----- ---- ---------- ---------- ---------- ---------- ----------
EST_MINUTES ERROR_CODE
----------- --------------------------------------------
           3 REBAL RUN           1          1          2        102        120
          0


SQL> 
SQL> l
  1* select * from v$asm_operation
SQL> 
SQL> /

GROUP_NUMBER OPERA STAT      POWER     ACTUAL      SOFAR   EST_WORK   EST_RATE
------------ ----- ---- ---------- ---------- ---------- ---------- ----------
EST_MINUTES ERROR_CODE
----------- --------------------------------------------
           3 REBAL RUN           1          1         47        101         95
          0


SQL> /

no rows selected

SQL>


NOTE : Note how I must specify the Disk NAME (not the PATH) for the DROP. When I added disks asmdisk.8 and asmdisk.9, I could have given then meaningful names as well. Oracle has automatically named them.

Ideally, what I should have done is to use the ADD and DROP command together.  That way, I would have a single-pass REBALANCE required.

After a while, I run my validation queries on node2.


[root@node2 ~]# su - grid
-sh-3.2$ sqlplus

SQL*Plus: Release 11.2.0.2.0 Production on Sun Aug 24 22:42:39 2014

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

Enter user-name: / as sysasm

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - Production
With the Real Application Clusters and Automatic Storage Management options

SQL> select d.name, d.path
    from v$asm_disk d, v$asm_diskgroup g
    where d.group_number=g.group_number
   and g.name = 'DATA3'  2    3    4  
  5  
SQL> l
  1  select d.name, d.path
  2      from v$asm_disk d, v$asm_diskgroup g
  3      where d.group_number=g.group_number
  4*    and g.name = 'DATA3'
SQL> /

NAME
------------------------------
PATH
--------------------------------------------------------------------------------
DATA3_0002
/fra/asmdisk.9

DATA3_0001
/fra/asmdisk.8


SQL> 
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - Production
With the Real Application Clusters and Automatic Storage Management options
-sh-3.2$ exit
logout

[root@node2 ~]# su - oracle
-sh-3.2$ 
-sh-3.2$ sqlplus

SQL*Plus: Release 11.2.0.2.0 Production on Sun Aug 24 22:44:10 2014

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

Enter user-name: hemant/hemant

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options

SQL> select count(*) from new_tbs_tbl;

  COUNT(*)
----------
     72460

SQL>


I have now accessed the table, tablespace, diskgroup and disks from node2 successfully. Disk asmdisk.7 is no longer part of the DiskGroup.

I can physically remove disk asmdisk7 from the storage.


SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - Production
With the Real Application Clusters and Automatic Storage Management options
-sh-3.2$ exit
logout

[root@node1 ~]# cd /data1
[root@node1 data1]# ls -l asmdisk.7
-rwxrwxr-x 1 grid oinstall 2048000000 Aug 24 22:39 asmdisk.7
[root@node1 data1]# rm asmdisk.7
rm: remove regular file `asmdisk.7'? y
[root@node1 data1]# 
[root@node1 data1]# su - grid
-sh-3.2$ sqlplus

SQL*Plus: Release 11.2.0.2.0 Production on Sun Aug 24 22:50:18 2014

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

Enter user-name: / as sysasm

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - Production
With the Real Application Clusters and Automatic Storage Management options

SQL> set pages 60
SQL> col name format a15
SQL> col path format a20
SQL> select group_number, name, path
  2  from v$asm_disk
  3  order by 1,2;

GROUP_NUMBER NAME            PATH
------------ --------------- --------------------
           0                 /crs/voting.disk
           0                 /data1/votedisk.1
           0                 /data2/votedisk.2
           0                 /fra/votedisk.3
           1 DATA1_0000      /data1/asmdisk.1
           1 DATA1_0001      /data2/asmdisk.4
           2 DATA2_0000      /data1/asmdisk.2
           2 DATA2_0001      /data2/asmdisk.5
           2 DATA2_0002      /data2/asmdisk.6
           3 DATA3_0001      /fra/asmdisk.8
           3 DATA3_0002      /fra/asmdisk.9
           4 DATA_0000       /crs/ocr.configurati
                             on

           5 FRA_0000        /fra/fradisk.3
           5 FRA_0001        /fra/fradisk.2
           5 FRA_0002        /fra/fradisk.1
           5 FRA_0003        /fra/fradisk.4

16 rows selected.

SQL>

The disk asmdisk.7 is no longer part of the storage. (Remember : All my disks here are on NFS).
- No comments:
Labels:

Adding and Using a new DiskGroup for RAC

 Adding and Using a new DiskGroup for RAC


In 11gR2 Grid Infrastructure and RAC

On node1, I discover and add a disk to ASM.  NFS "devices" asmdisk.1 to asmdisk.6 are present as ASM Disks. asmdisk.7 has been added on NFS mount point /data1. (Disks asmdisk.3 to asmdisk.6 are on /data2)

I start on node1 in my Cluster

[root@node1 ~]# su - grid
-sh-3.2$ sqlplus 

SQL*Plus: Release 11.2.0.2.0 Production on Sat Aug 16 23:42:02 2014

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

Enter user-name: / as sysasm

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - Production
With the Real Application Clusters and Automatic Storage Management options

SQL> show parameter asm_diskstring

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
asm_diskstring                       string      /crs/*, /data1/*, /data2/*, /f
                                                 ra/*
SQL> !ls -l /data1/asm*
-rwxrwxr-x 1 grid oinstall 2048000000 Aug 16 23:42 /data1/asmdisk.1
-rwxrwxr-x 1 grid oinstall 2048000000 Aug 16 23:42 /data1/asmdisk.2
-rw-r--r-- 1 grid oinstall 2048000000 Aug 16 23:33 /data1/asmdisk.7

SQL> create diskgroup DATA3 disk '/data1/asmdisk.7';
create diskgroup DATA3 disk '/data1/asmdisk.7'
*
ERROR at line 1:
ORA-15018: diskgroup cannot be created
ORA-15072: command requires at least 2 regular failure groups, discovered only
1


SQL> create diskgroup DATA3 external redundancy disk '/data1/asmdisk.7';

Diskgroup created.

SQL> 
SQL> select group_number, name, total_mb
  2  from v$asm_diskgroup
  3  where name = 'DATA3'
  4  /

GROUP_NUMBER NAME                             TOTAL_MB
------------ ------------------------------ ----------
           5 DATA3                                1953

SQL>

I now have a new DiskGroup using External Redundancy with a single disk.  Is it visible at node2 ?

[root@node2 ~]# su - grid
-sh-3.2$ sqlplus 

SQL*Plus: Release 11.2.0.2.0 Production on Sat Aug 16 23:47:45 2014

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

Enter user-name: / as sysasm

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - Production
With the Real Application Clusters and Automatic Storage Management options

SQL> select group_number, name, total_mb 
  2  from v$asm_diskgroup
  3  where name = 'DATA3'
  4  /

GROUP_NUMBER NAME                             TOTAL_MB
------------ ------------------------------ ----------
           0 DATA3                                   0

SQL>

Why is the size not visible yet ?  Because, although the CREATE from node1 had also MOUNTed the Disk Group, it hasn't been mounted on node2 yet.

SQL> alter diskgroup DATA3 mount;

Diskgroup altered.

SQL> select group_number, name, total_mb
  2  from v$asm_diskgroup
  3  where name = 'DATA3'
  4  /

GROUP_NUMBER NAME                             TOTAL_MB
------------ ------------------------------ ----------
           5 DATA3                                1953

SQL>

Can I confirm the underlying disk ? 

SQL> select group_number, disk_number, header_status, state, total_mb
  2  from v$asm_disk
  3  where group_number = 5;

GROUP_NUMBER DISK_NUMBER HEADER_STATU STATE      TOTAL_MB
------------ ----------- ------------ -------- ----------
           5           0 MEMBER       NORMAL         1953

SQL>


What happens when I create a tablespace/datafile in this DiskGroup, from the instance on node1 ? 

SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - Production
With the Real Application Clusters and Automatic Storage Management options
-sh-3.2$ su - oracle
Password: 
-sh-3.2$ sqlplus '/ as sysdba'

SQL*Plus: Release 11.2.0.2.0 Production on Sun Aug 17 00:08:31 2014

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


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options

SQL> create tablespace NEW_TBS datafile '+DATA3';
create tablespace NEW_TBS datafile '+DATA3'
*
ERROR at line 1:
ORA-01119: error in creating database file '+DATA3'
ORA-15045: ASM file name '+DATA3' is not in reference form
ORA-17502: ksfdcre:5 Failed to create file +DATA3
ORA-15081: failed to submit an I/O operation to a disk


SQL>

Why do I get this error ? I could create a DiskGroup on the ASM Disk but I couldn't add a datafile ?  Let me check the permissions.

SQL> !sh
sh-3.2$ cd /data1
sh-3.2$ ls -l asmd*
-rwxrwxr-x 1 grid oinstall 2048000000 Aug 17 00:11 asmdisk.1
-rwxrwxr-x 1 grid oinstall 2048000000 Aug 17 00:11 asmdisk.2
-rw-r--r-- 1 grid oinstall 2048000000 Aug 17 00:11 asmdisk.7
sh-3.2$ su grid
Password: 
sh-3.2$ pwd
/data1
sh-3.2$ ls -l asmd*
-rwxrwxr-x 1 grid oinstall 2048000000 Aug 17 00:12 asmdisk.1
-rwxrwxr-x 1 grid oinstall 2048000000 Aug 17 00:12 asmdisk.2
-rw-r--r-- 1 grid oinstall 2048000000 Aug 17 00:12 asmdisk.7
sh-3.2$ chmod 775 asmdisk.7
sh-3.2$ ls -l asmdisk.7
-rwxrwxr-x 1 grid oinstall 2048000000 Aug 17 00:12 asmdisk.7
sh-3.2$

The oinstall group that is used by "oracle" did not have write permissions. Let me go back to Oracle now after having granted the permissions. 

sh-3.2$ exit
exit
sh-3.2$ exit
exit

SQL> l
  1* create tablespace NEW_TBS datafile '+DATA3'
SQL> /

Tablespace created.

SQL>

The CREATE TABLESPACE has succeeded.  I can verify the datafile and the ASM file from node2 now.

-sh-3.2$ id
uid=500(grid) gid=1001(oinstall) groups=1001(oinstall),1011(asmdba)
-sh-3.2$ sqlplus

SQL*Plus: Release 11.2.0.2.0 Production on Sun Aug 17 00:17:19 2014

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

Enter user-name: / as sysasm

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - Production
With the Real Application Clusters and Automatic Storage Management options

SQL> select group_number, file_number, bytes/1048576, type, redundancy
  2  from v$asm_file
  3  where group_number=5;

GROUP_NUMBER FILE_NUMBER BYTES/1048576
------------ ----------- -------------
TYPE                                                             REDUND
---------------------------------------------------------------- ------
           5         256    100.007813
DATAFILE                                                         UNPROT


SQL> 
SQL> exit
suDisconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - Production
With the Real Application Clusters and Automatic Storage Management options
 -sh-3.2$ 
-sh-3.2$ su - oracle
Password: 
-sh-3.2$ sqlplus 
 
SQL*Plus: Release 11.2.0.2.0 Production on Sun Aug 17 00:19:34 2014

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

Enter user-name: / as sysdba

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options

SQL> select file_name, bytes/1048576 from dba_data_files
  2  where tablespace_name = 'NEW_TBS';

FILE_NAME
--------------------------------------------------------------------------------
BYTES/1048576
-------------
+DATA3/racdb/datafile/new_tbs.256.855792859
          100


SQL>

Now, I have the new DataFile visible in ASM and the Database on the New DiskGroup.
- No comments:
Labels:

ASM, DiskGroup, AU Size, Tablespace and Table Extents



Creating a new DiskGroup, specifying the Allocation Unit Size for it, creating a Tablespace in a Pluggable Database and creating multiple Segments (Tables) with multiple Extents.

I have added a new disk to my 12.1 VM.

SQL> select path, name, total_mb, header_status, state, group_number
  2  from v$asm_disk
  3  order by 1
  4  /

PATH
--------------------------------------------------------------------------------
NAME                             TOTAL_MB HEADER_STATU STATE    GROUP_NUMBER
------------------------------ ---------- ------------ -------- ------------
/dev/asm-disk1
DATA_0000                            5114 MEMBER       NORMAL              1

/dev/asm-disk2
DATA_0001                            5114 MEMBER       NORMAL              1

/dev/asm-disk3
FRA_0000                             5114 MEMBER       NORMAL              2

/dev/asm-disk4
OCRVOTE_0000                         5114 MEMBER       NORMAL              3

/dev/asm-disk5
DATA_0002                           12284 MEMBER       NORMAL              1

/dev/asm-disk6
                                        0 CANDIDATE    NORMAL              0


6 rows selected.

SQL>

The new disk is /dev/asm-disk6.
I create an ASM DiskGroup with AU Size of 1MB.

SQL> create diskgroup NEWDG_1M_AU external redundancy
  2  disk '/dev/asm-disk6'
  3  attribute 'compatible.asm'='12.1', 'compatible.rdbms'='12.1','au_size'='1M';

Diskgroup created.

SQL>
SQL> select path, name, total_mb, header_status, state, group_number
  2  from v$asm_disk
  3  order by 1
  4  /

PATH
--------------------------------------------------------------------------------
NAME                             TOTAL_MB HEADER_STATU STATE    GROUP_NUMBER
------------------------------ ---------- ------------ -------- ------------
/dev/asm-disk1
DATA_0000                            5114 MEMBER       NORMAL              1

/dev/asm-disk2
DATA_0001                            5114 MEMBER       NORMAL              1

/dev/asm-disk3
FRA_0000                             5114 MEMBER       NORMAL              2

/dev/asm-disk4
OCRVOTE_0000                         5114 MEMBER       NORMAL              3

/dev/asm-disk5
DATA_0002                           12284 MEMBER       NORMAL              1

/dev/asm-disk6
NEWDG_1M_AU_0000                     2149 MEMBER       NORMAL              4


6 rows selected.

SQL>
SQL> select group_number, name, sector_size, block_size, allocation_unit_size, state
  2  from v$asm_diskgroup
  3  order by 1
  4  /

GROUP_NUMBER NAME                           SECTOR_SIZE BLOCK_SIZE
------------ ------------------------------ ----------- ----------
ALLOCATION_UNIT_SIZE STATE
-------------------- -----------
           1 DATA                                   512       4096
             1048576 MOUNTED

           2 FRA                                    512       4096
             1048576 MOUNTED

           3 OCRVOTE                                512       4096
             1048576 MOUNTED

           4 NEWDG_1M_AU                            512       4096
             1048576 MOUNTED


SQL>

I then create a Tablespace in a Pluggable Database specifically on this DG, instead of the default location for new Tablespace datafiles.

SQL> alter session set container=PDB;

Session altered.

SQL> show parameter db_create_file_dest

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_create_file_dest                  string      +DATA
SQL>
SQL> create tablespace NEWTS_ON_1M_AU
  2  datafile '+NEWDG_1M_AU'
  3  extent management local autoallocate segment space management auto;

Tablespace created.

SQL>
SQL> select group_number, name, total_mb, usable_file_mb
  2  from v$asm_diskgroup
  3  order by 1
  4  /

GROUP_NUMBER NAME                             TOTAL_MB USABLE_FILE_MB
------------ ------------------------------ ---------- --------------
           1 DATA                                22512          12865
           2 FRA                                  5114           2851
           3 OCRVOTE                              5114           4820
           4 NEWDG_1M_AU                          2149           1987

SQL>
SQL> drop tablespace NEWTS_ON_1M_AU including contents and datafiles;

Tablespace dropped.

SQL> select group_number, name, total_mb, usable_file_mb
  2  from v$asm_diskgroup
  3  order by 1
  4  /

GROUP_NUMBER NAME                             TOTAL_MB USABLE_FILE_MB
------------ ------------------------------ ---------- --------------
           1 DATA                                22512          12865
           2 FRA                                  5114           2851
           3 OCRVOTE                              5114           4820
           4 NEWDG_1M_AU                          2149           2089

SQL>
SQL> create tablespace NEWTS_ON_1M_AU
  2  datafile '+NEWDG_1M_AU'
  3  extent management local autoallocate segment space management auto;

Tablespace created.

SQL> select group_number, name, total_mb, usable_file_mb
  2  from v$asm_diskgroup
  3  order by 1
  4  /

GROUP_NUMBER NAME                             TOTAL_MB USABLE_FILE_MB
------------ ------------------------------ ---------- --------------
           1 DATA                                22512          12865
           2 FRA                                  5114           2851
           3 OCRVOTE                              5114           4820
           4 NEWDG_1M_AU                          2149           1987

SQL>
SQL> select file_name, bytes/1048576, user_bytes/1048576
  2  from dba_data_files
  3  where tablespace_name = 'NEWTS_ON_1M_AU'
  4  /

FILE_NAME
--------------------------------------------------------------------------------
BYTES/1048576 USER_BYTES/1048576
------------- ------------------
+NEWDG_1M_AU/RAC/44BBC69CE8F552AEE053334EA8C07365/DATAFILE/newts_on_1m_au.256.95
3594325
          100                 99


SQL>

Thus, we can see that the Usable Space in the Disk Group is less than the DiskGroup size, even with External Redundancy that specifies no mirroring of ASM Extents. The Usable space in the Disk Group is 1987MB after creating a 100MB datafile.

Bytes available in the datafile are 99MB because a Locally Managed Tablespace reserves space for the Extent Map in the datafile header blocks.  The datafile, belonging to a Tablespace in a Pluggable Database has a path that is identified by the DB_UNIQUE_NAME (RAC) and the PDB GUID as the identifier (44BBC69CE8F552AEE053334EA8C07365)

Now, I create 5 small tables, each with 5 extents of 64KB in the Tablespace.  I use DEFERRED_SEGMENT_CREATION=FALSE  to force creation of the Segment and allocation of all the Extents.

SQL> connect hemant/hemant@PDB
Connected.
SQL> alter session set deferred_segment_creation=FALSE;

Session altered.

SQL> create table t1 (col_1 number) tablespace NEWTS_ON_1M_AU storage (minextents 5);

Table created.

SQL> create table t2 (col_1 number) tablespace NEWTS_ON_1M_AU storage (minextents 5);

Table created.

SQL> create table t3 (col_1 number) tablespace NEWTS_ON_1M_AU storage (minextents 5);

Table created.

SQL> create table t4 (col_1 number) tablespace NEWTS_ON_1M_AU storage (minextents 5);

Table created.

SQL> create table t5 (col_1 number) tablespace NEWTS_ON_1M_AU storage (minextents 5);

Table created.

SQL>
SQL>
SQL> connect system/manager@PDB

Connected.
SQL> select sum(bytes)/1024,  count(*)
  2  from dba_extents
  3  where tablespace_name = 'NEWTS_ON_1M_AU'
  4  /

SUM(BYTES)/1024   COUNT(*)
--------------- ----------
          25600         25

SQL> select file_name, bytes/1048576, user_bytes/1048576
  2  from dba_data_files
  3  where tablespace_name = 'NEWTS_ON_1M_AU'
  4  /

FILE_NAME
--------------------------------------------------------------------------------
BYTES/1048576 USER_BYTES/1048576
------------- ------------------
+NEWDG_1M_AU/RAC/44BBC69CE8F552AEE053334EA8C07365/DATAFILE/newts_on_1m_au.256.95
3594325
          100                 99


SQL> select group_number, name, total_mb, usable_file_mb
  2  from v$asm_diskgroup
  3  order by 1
  4  /

GROUP_NUMBER NAME                             TOTAL_MB USABLE_FILE_MB
------------ ------------------------------ ---------- --------------
           1 DATA                                22512          12865
           2 FRA                                  5114           2832
           3 OCRVOTE                              5114           4820
           4 NEWDG_1M_AU                          2149           1987

SQL>


Thus, a short demo of :
1.  Creating a new ASM DiskGroup with a specified AU Size
2.  Creating a Tablespace in the new DG (not being the default location)
3.  Creating multiple tables with pre-allocated Extents
4.  Verifying the Usable Space in the DiskGroup and Datafile
.
- No comments:
Labels:
Subscribe to: Posts (Atom)

Featured post

Restircted session due to sync filed with ora-65177

Application is unable to connect the database due to restricted session. sql> show pdbs; SQL> show con_name CON_NAME -----------------...

  • user permissions script for each of the databases
      Use the following script for this purpose for each database.  REF :This script is also present in the link https://www.sqlservercentral.co...
  • listener service register
    alter system set local_listener='(ADDRESS = (PROTOCOL = TCP)(HOST = demo.localdomain)(PORT = 1531))'; alter system set local_listene...
  • user ddl
    set head off REM set long 150 set linesize 150 set longchunksize 150 set pages 0 set long 99999999 set feedback off set echo off ...