Oracle DBA – A lifelong learning experience

Archive for the ‘ASM’ Category

New ASM power levels in 11.2.0.2 and beyond

Posted by John Hallas on February 13, 2015

I recently saw the following command in a script that was to be run and thought an error had been made and the power level should have been 5 not 500.

ALTER DISKGROUP DATA REBALANCE POWER 500;

Upon doing some research it was not a mistype but a new method of disk balancing which came in from 11.2.0.2

Previously setting the power limit from 0 to 11 basically caused an additional number of ARBx process to be created to match the power level and these were removed once the rebalance had finished.

That was a nice simple situation which I had no problem with. The range was adequate and I normally used between 4 and 7 depending on the usage of the system and any performance impact that might be caused. The impact was easy to monitor using a variety of tools as top or glance on a *nix platform

Now from 11GR2 onwards and when a database has disk group ASM compatibility set to 11.2.0.2 or greater the operational range of values is 0 to 1024 for the rebalance power. Note that if the value of the POWER clause is specified larger than 11 for a disk group with ASM compatibility set to less than 11.2.0.2, then a warning is displayed and a POWER value equal to 11 is used for rebalancing. Second point to note is that if a disk group is altered to a higher RDBMS value this operation cannot be reversed.

So what does that mean in practise? Well in my eyes it seems to be a basic change but it now seems very hard, well-nigh impossible to see the impact that the re-balance is having on the server and consequently I do not see the advantages of it other than possibly on massively high-end systems. Read the rest of this entry »

Posted in 11g new features, ASM, Oracle | Tagged: , , | 2 Comments »

Stopping one ASM listener in Flex ASM environment takes down ASM instance

Posted by John Hallas on January 6, 2015

Stopping one ASM listener in Flex ASM environment takes down ASM instance

This is a heads-up about behaviour we are seeing during OAT testing on a 12c environment. We are running OAT tests on a new 12.1.0.2 Grid Infrastructure environment using Flex ASM (OEL 5.8) and when taking down one of the two ASMNETLSNR listeners on one of the nodes, the ASM instance running on that node is taken down. We assume this is due to a hard dependency between the listener and the ASM instance. Unfortunately this does not give us a great deal of resilience: as we have two ASMNETLSNR listeners normally running on each node we would expect to be able to lose one of them without losing an ASM instance.

ps -ef | grep tns
root 769 2 0 Nov10 ? 00:00:00 [netns]
oracle 8386 1 0 Nov19 ? 00:00:09 /app/gridsoft/12.1.0.2/bin/tnslsnr ASMNET1LSNR_ASM -no_crs_notify -inherit
oracle 8389 1 0 Nov19 ? 00:00:12 /app/gridsoft/12.1.0.2/bin/tnslsnr LISTENER_DG -inherit
oracle 8500 1 0 Nov19 ? 00:00:51 /app/gridsoft/12.1.0.2/bin/tnslsnr LISTENER -no_crs_notify -inherit
oracle 8621 1 0 Nov19 ? 00:00:13 /app/gridsoft/12.1.0.2/bin/tnslsnr LISTENER_SCAN1 -no_crs_notify -inherit
oracle 117429 1 0 13:57 ? 00:00:00 /app/gridsoft/12.1.0.2/bin/tnslsnr ASMNET2LSNR_ASM -no_crs_notify -inherit 

We are stopping the listener in a consistent and standard manner

srvctl stop listener ASMNET... -n node_name –f

 

Oracle did refer us to the following note.
Bug 14155526 : [12100-LIN64] BC: FAILED TO STOP ASM LISTENER WITH DEPENDENCY OF “ORA.ASM” RES >> this bug is closed as not bug . since its expected behavior.

This is expected behavior. ASM cannot be stopped on the node where the CRSD PE is running.

However we thought that you should be able to take an ASM listener down without forcing the database down as well. We have HAIP with two ASM Listeners per node. If we take one of the ASM listeners on a node down surely the other ASM Listener should be able to service the ASM instance on that node without the need for ASM going down. So why is there a hard dependency for the ASM listener?

 

Oracle said that this was expected behaviour but we still did not agree. They took the view that it works as per the design – confirmed by their dev team.

Based on the internal   <a href="https://support.oracle.com/epmos/faces/BugDisplay?id=14347014">Bug 14347014</a> : LNX64-12.1-SRVM-FPF:NEED AN OPTION TO REMOVE ASM LISTENER ALSO UPDATE DEPENDENCY

there is option provided by dev team to remove asm listener and update the dependency

A new command

srvctl update listener [-listener  -asm -remove [-force]]

is provided to remove asm listener and update dependency

note: srvctl update listener -listener listener_asm3 -remove -asm [-force]

If '-remove' and '-asm' are not specified together, then we should error out

 

We were  still not satisfied that this is how it should work if there are multiple ASM listeners/interfaces.
I can understand that if there is only one interface for the ASM instances to communicate on via the ASM listener, then it could be argued that taking the listener down should take down the non-critical ASM instances. But we have set up HAIP and are utilising two network interfaces, the whole point of this configuration is to provide greater resilience, therefore we should be able to lose one listener, either through failure or a need to restart it, without killing ASM instances.
It looks like the hard dependencies have been configured so that ASM will fail if ANY of the ASM listeners are not present, it should be configured so that ASM should only fail if ALL of the ASM listeners are not present.

Oracle have now confirmed that in 12.2 there will be an enhancement to change the dependencies between ASM and the ASM listener.

If you are testing resilience between nodes on a new 12c cluster then it might be worth stopping an ASM listener and seeing what the impact is.

Posted in 12c new features, ASM, Oracle | Tagged: , | 3 Comments »

My bizarre question of 2015 already

Posted by John Hallas on January 5, 2015

To be honest it was asked 2 years ago in a blog about ASM and rebalancing and someone asked the following question

Can I upgrade ASM from 10205 to 11203 while ASM rebalancing is in progress?
and the answer, unsurprisingly, was
No.
Why would anyone even think of asking that question never mind contemplating the idea of running an upgrade under such circumstances.
Needless to say my next blog post is about ASM related activities – coming very shortly

Posted in ASM, Oracle | Tagged: | Leave a Comment »

Oracle Restart – going , going , gone?

Posted by John Hallas on January 13, 2014

Oracle restart is an 11GR2 feature which ensures that all services on a standalone installation start up in the correct order. As such it seems to work well. One bugbear I have with it is that it changes the order of entries in the /etc/oratab file. Personally I like my oratab to be ordered in terms of database (most used first), ASM, then agents. In that way when I logon to a box and it automatically sets the SID it picks up the first entry which is commonly the database I want to work with.

Technically it is the oraagent process, which is new in 11GR2 which updates the oratab file, but as that sits under the ohasd directory in the GI home then I consider it to be part of the Restart process. As a bye you can make your oratab file read only as long as you remember to change permissions when adding a new databases or removing one.

To the main purpose of this post. It appears that Oracle Restart is deprecated in 12c although no replacement is on the horizon.

http://docs.oracle.com/cd/E16655_01/server.121/e17642/deprecated.htm#BABBEHJH

“Oracle Restart is deprecated in Oracle Database 12c. Oracle Restart is currently restricted to manage single-instance Oracle databases and Oracle ASM instances only, and is subject to desupport in future releases. Oracle continues to provide Oracle ASM as part of the Oracle Grid Infrastructure installation for Standalone and Cluster deployments.”

That is a bit of a shock, as it is still fully documented in the Oracle 12c docs for installation etc. The MoS note Support Impact of the Deprecation Announcement of Oracle Restart with Oracle Database 12c (Doc ID 1584742.1) gives more detail and tantalisingly seems to suggest that the deprecation may be revoked.

“While typically, a feature is only deprecated once the superseding feature has been announced, on occasion this order cannot be kept. In this case, one may assume that the deprecation announcement will last – without effect – until the superseding feature has been released or the deprecation is revoked. In case of the deprecation announcement of Oracle Restart, the superseding feature has not been released yet. Therefore, Oracle will maintain Oracle Restart in its current form and will continue to fix BUGs for this product until Oracle Restart is officially de-supported, which would constitute the removal of the code.”

It would be reasonable to think that Oracle intended having a replacement ready for the initial release of 12C but it was not available in time and will come out in 12CR2

Posted in 12c new features, ASM, Oracle | Tagged: , , , | Leave a Comment »

expdp to ASM and exclude table syntax – two workarounds

Posted by John Hallas on September 2, 2013

A short entry showing two problems that I have come across recently with datapump export and the  workarounds I used.

Using ASM disk for exports

Quite often it is hard to find sufficient space to export a large amount of data, especially if trying to use a filesystem. However with the growing use of ASM there is often plenty of disk space to be found in that area. The stumbling block is that the ASM area cannot hold trace files, audit files, alert logs, export files, tar files, and core files and therefore you get an error message even though you have created an export directory on the ASM disk group as requested. This is the same from 11GR1 through to 12C

I have created the directory and also created the folder within the ASM diskgroup

asmcmd –p

mkdir ‘+DATA/john_exp’

sqlplus

create directory john_exp as ‘‘+DATA/john_exp’  -- grant permissions if necessary

expdp schemas=system directory=john_exp dumpfile=john_exp.dmp content=metadata_only  logfile=john_exp.log

Export: Release 11.2.0.3.0 - Production on Mon Sep 2 13:13:59 2013

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

Username: / as sysdba

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

With the Partitioning, Automatic Storage Management, OLAP, Data Mining

and Real Application Testing options

ORA-39002: invalid operation

ORA-39070: Unable to open the log file.

ORA-29283: invalid file operation

ORA-06512: at "SYS.UTL_FILE", line 536

ORA-29283: invalid file operation

The trick is to use another, probably existing directory, such as DATA_PUMP_DIR which is placed on a filesystem  and point the logfile there, using the syntax ‘logfile=directory:logfile’. An alternative option is to use the expdp parameter nologfile=yes

expdp schemas=system directory=john_exp dumpfile=john_exp.dmp content=metadata_only  logfile=data_pump_dir:john_exp.log

Export: Release 11.2.0.3.0 - Production on Mon Sep 2 13:14:20 2013

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

Username: / as sysdba

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

With the Partitioning, Automatic Storage Management, OLAP, Data Mining

and Real Application Testing options

Starting "SYS"."SYS_EXPORT_SCHEMA_01":  /******** AS SYSDBA schemas=system directory=john_exp dumpfile=john_exp.dmp content=metadata_only logfile=data_pump_dir:john_exp.log

Processing object type SCHEMA_EXPORT/USER

Processing object type SCHEMA_EXPORT/SYSTEM_GRANT

Processing object type SCHEMA_EXPORT/ROLE_GRANT

Processing object type SCHEMA_EXPORT/DEFAULT_ROLE

….

******************************************************************************

Dump file set for SYS.SYS_EXPORT_SCHEMA_01 is:

+DATA/john_exp/john_exp.dmp

Job "SYS"."SYS_EXPORT_SCHEMA_01" successfully completed at 13:16:49

Syntax to exclude a table in a data pump export

After spending ages trying to get the syntax of an EXCLUDE table parameter working correctly from the command line of a Linux server I finally gave up and actually read the manual. There I found the following helpful advice :-

Some operating systems require that quotation marks on the command line be preceded by an escape character, such as the backslash. If the backslashes were not present, the command-line parser that Export uses would not understand the quotation marks and would remove them, resulting in an error. In general, Oracle recommends that you place such statements in a parameter file because escape characters are not necessary in parameter files.

My example was a schema export but we did not need a very large table to be included. This is the syntax which should work and indeed did when using a parameter file

expdp schemas=sm7 directory=data_pump_dir  dumpfile=sm7.dmp logfile=sm7 content=all exclude=table:”=’SYSATTACHMENT'”

LRM-00116: syntax error at 'table:' following '='

The working parameter file was called by expdp parfile =sm7.par

schemas=sm7

directory=data_pump_dir

content=all

dumpfile=sm7.dmp

logfile=sm7.log

exclude=table:”=’SYSATTACHMENT'”

For information this is the revised syntax that works from the command line. However I for one will be using the parameter file in future

expdp schemas=sm7 directory=data_pump_dir  dumpfile=sm7.dmp logfile=sm7 content=all exclude=table:\”=\’SYSATTACHMENT\’\”

Posted in ASM, Oracle | Tagged: , , , , , | Leave a Comment »

Adding new ASM disks – what is best practise?

Posted by John Hallas on January 15, 2013

According to My Oracle Support note – “How To Add a New Disk(s) to An Existing Diskgroup on RAC (Best Practices). [ID 557348.1]” you should create a test diskgroup using new storage before adding it to an existing diskgroup. That seems eminently sensible, although it is not something I normally do. It proves you can access the disk, and if there is a conflict (i.e the disk is already mapped and in use elsewhere) you are not risking your production DATA diskgroup. I have pasted the note info at the bottom of this post but basically you just create  a new diskgroup and add the new disk to it. If all is OK then drop the diskgroup and add the new disk to your existing diskgroup.

However the downside of that is that you can hit non-published bug:12398300 which is a duplicate of bug:12356910 (also non-published). Diskgroup Mount Hangs with RBAL Waiting on ‘GPnP Get Item’ and ‘enq: DD – contention’ [ID 1375505.1].  Note: This issue so far has been reported on RAC 11.2.0.2.3 and 11.2.0.3 environments which is where we saw it (RAC – 11.2.0.3 clusterware – 11.2.0.1 rdbms)

Simply the ALTER DISKGROUP MOUNT just hangs and has to be interrupted (CTRL-C). No errors in the ASM alert log

Killing the ora.gpnpd on the node when ASM is blocked in the gpnp wait, permits not having  to stop the ASM instance. For details, please see Note:1392934.1. Otherwise, restart the ASM instance that is causing the lock condition.

The fix will be included in future 11.2.0.3.x Patch Set Updates (PSUs) but no patches yet exists (at the time this article was written – Nov.9.2011). Also likely there will be patch requests for the fix to be included on top of existing 11.2.0.2 PSU, but none yet exist.

So I think I will stick with what I have always done, best practise or not.

--From Node 1

. oraenv

-- specify ASM instance from node 1

+ASM1

-- sudo -u oracle sqlplus may not work when run the first time so run:

sudo -u oracle ls

sudo -u oracle sqlplus / as sysasm

CREATE DISKGROUP TEST EXTERNAL REDUNDANCY DISK '' [DISK ''];

SELECT STATE, NAME FROM V$ASM_DISKGROUP;

-- from node 2

. oraenv

+ASM2

sudo -u oracle sqlplus / as sysasm

&nbsp;

ALTER DISKGROUP TEST MOUNT;

SELECT STATE, NAME FROM V$ASM_DISKGROUP;

&nbsp;

-- if all ok then

-- from node 2

alter diskgroup test dismount;

&nbsp;

-- from node 1

DROP DISKGROUP TEST;

Now we can add the disk to the desired diskgroup safely.

&nbsp;

-- From node 1

. oraenv

+ASM1

sudo -u oracle sqlplus / as sysasm

-- check disks visible in v$asm_disk

-- header_status should be CANDIDATE or FORMER

set lines 120 pages 100

column path format a20

SELECT name, path, mode_status, state, header_status, os_mb, free_mb

FROM v$asm_disk ORDER BY name, path;

-- check diskgroups

select GROUP_NUMBER,NAME,STATE,TOTAL_MB,FREE_MB from v$asm_diskgroup;

-- add disks to appropriate diskgroups

alter diskgroup x add disk '/dev/hdiskX';

-- monitor rebalance

set lines 170

select * from v$asm_operation;

select GROUP_NUMBER,NAME,STATE,TOTAL_MB,FREE_MB from v$asm_diskgroup;

Posted in ASM, Oracle | Tagged: , , , , | 2 Comments »

Managing OCR and voting disks

Posted by John Hallas on November 28, 2012

This is basically a set of notes I wrote for myself about adding new voting disks and OCR disks to a sandpit RAC cluster as part of testing for migration between HP XP disk array and HP 3PAR disk array. The o/s was HPUX with 11.1.0.7 database.

 View status of OCR disks and Voting disks

sudo /app/oracle/product/crs/bin/ocrcheck

Status of Oracle Cluster Registry is as follows :

         Version                  :          2

         Total space (kbytes)     :     306972

         Used space (kbytes)      :       5880

         Available space (kbytes) :     301092

         ID                       :  746041401

         Device/File Name         : /dev/oracle/disk500

                                    Device/File integrity check succeeded

         Device/File Name         : /dev/oracle/disk501

                                    Device/File integrity check succeeded

          Cluster registry integrity check succeeded

          Logical corruption check succeeded

crsctl query css votedisk

0.     0    /dev/oracle/disk502

1.     0    /dev/oracle/disk503

2.     0    /dev/oracle/disk504

Located 3 voting disk(s).

Add a new OCR disk

Backup first  (the 10GR1 command format still works)

sudo /app/oracle/product/crs/bin/ocrconfig -export /home/oracle/ocr_backup -s online

Owned by root

-rw——-   1 root       sys         136140 Nov 27 08:15 /home/oracle/ocr_backup

As this is an 11GR1 cluster we will use the 11GR1 format

sudo /app/oracle/product/crs/bin/ocrconfig -manualbackup

dhpor43     2012/11/27 08:23:00     /app/oracle/product/crs/cdata/SANDPITR1/backup_20121127_082300.ocr

Listing the backups shows the recent backups

sudo /app/oracle/product/crs/bin/ocrconfig -showbackup

dhpor43     2012/11/27 06:36:27     /app/oracle/product/crs/cdata/SANDPITR1/backup00.ocr

dhpor43     2012/11/27 02:36:27     /app/oracle/product/crs/cdata/SANDPITR1/backup01.ocr

dhpor43     2012/11/26 22:36:27     /app/oracle/product/crs/cdata/SANDPITR1/backup02.ocr

dhpor43     2012/11/25 02:36:27     /app/oracle/product/crs/cdata/SANDPITR1/day.ocr

dhpor43     2012/11/24 06:36:27     /app/oracle/product/crs/cdata/SANDPITR1/week.ocr

dhpor43     2012/11/27 08:23:00     /app/oracle/product/crs/cdata/SANDPITR1/backup_20121127_082300.ocr

I have 3 disks available (all at 1Gb,  which is easily enough for either a voting or OCR disk)

Free ASM disks and their paths

==============================

Header    Mode     Path                      Disk Size

——— ——– ————————- ———

CANDIDATE ONLINE   /dev/oracle/disk507             1Gb

CANDIDATE ONLINE   /dev/oracle/disk508             1Gb

CANDIDATE ONLINE   /dev/oracle/disk509             1Gb

sudo /app/oracle/product/crs/bin/ocrconfig -replace ocrmirror /dev/oracle/disk507

ocrcheck

Status of Oracle Cluster Registry is as follows :

         Version                  :          2

         Total space (kbytes)     :     306972

         Used space (kbytes)      :       5908

         Available space (kbytes) :     301064

         ID                       :  746041401

         Device/File Name         : /dev/oracle/disk500

                                    Device/File integrity check succeeded

         Device/File Name         : /dev/oracle/disk507 disk replaced (was disk501)

                                    Device/File integrity check succeeded

         Cluster registry integrity check succeeded

However the replaced disk is not available although 507 has been removed from the list of candidate disks

Free ASM disks and their paths

==============================

Header    Mode     Path                      Disk Size

——— ——– ————————- ———

CANDIDATE ONLINE   /dev/oracle/disk508             1Gb

CANDIDATE ONLINE   /dev/oracle/disk509             1Gb

 

sudo /app/oracle/product/crs/bin/ocrconfig -replace ocr /dev/oracle/disk508

app/oracle/product/11.1.0/asm/bin $ocrcheck

Status of Oracle Cluster Registry is as follows :

         Version                  :          2

         Total space (kbytes)     :     306972

         Used space (kbytes)      :       5908

         Available space (kbytes) :     301064

         ID                       :  746041401

         Device/File Name         : /dev/oracle/disk508

                                    Device/File integrity check succeeded

         Device/File Name         : /dev/oracle/disk507

                                    Device/File integrity check succeeded

Add a new voting disk

sudo /app/oracle/product/crs/bin/crsctl add css votedisk /dev/oracle/disk500 –force
/app/oracle/product/crs/bin/crsctl add css votedisk /dev/oracle/disk509 -force

 Now formatting voting disk: /dev/oracle/disk500.

Successful addition of voting disk /dev/oracle/disk500.

app/oracle/product/11.1.0/asm/bin $sudo /app/oracle/product/crs/bin/crsctl query css  votedisk

 0.     0    /dev/oracle/disk502

 1.     0    /dev/oracle/disk503

 2.     0    /dev/oracle/disk504

 3.     0    /dev/oracle/disk509

 4.     0    /dev/oracle/disk500

Located 5 voting disk(s).

crsctl delete css votedisk  /dev/oracle/disk509 

Successful deletion of voting disk /dev/oracle/disk509.

crsctl delete css votedisk  /dev/oracle/disk500 

Successful deletion of voting disk /dev/oracle/disk500.

sudo /app/oracle/product/crs/bin/crsctl query css  votedisk

 0.     0    /dev/oracle/disk502

 1.     0    /dev/oracle/disk503

 2.     0    /dev/oracle/disk504

Located 3 voting disk(s).

My follow up actions are to see if Linux performs in the same manner and what the difference is on a 11GR2 cluster. Finally I want to understand why the released disk retained their header and if there is any way of avoiding  having to dd the header. I expect that asmlib on Linux will prove different

 

Posted in ASM, Oracle | Tagged: , , , , | 2 Comments »

Balancing ASM disks in one command – the benefits

Posted by John Hallas on June 5, 2012

As a response to my previous post about adding and dropping ASM disks with one command Emre Baransel asked a question as to whether it was more efficient or not to do it as one or two commands. It was easier to add  a second post complete with screen shots rather than just a simple reply.

My test system was a 3.5Tb database spread across 10 512Gb luns.

The first choice was to add and drop using a single command Read the rest of this entry »

Posted in ASM, Oracle | Tagged: , , , | 3 Comments »

ASM – Adding and dropping disks in one command

Posted by John Hallas on May 29, 2012

One of the possibilities within ASM that is not widely documented is the opportunity to add disks, drop disks and set the rebalance power all in one command.

One might wonder when you might be both adding and dropping disks simultaneously from the same disk group. Recently we have been migrating to a new storage array and this command comes in very useful in that situation

alter diskgroup data add disk '/dev/oracle/disk100','dev/oracle/disk101','dev/oracle/disk102'

drop disk '/dev/oracle/disk197','dev/oracle/disk198','dev/oracle/disk199'

rebalance power 5;

 

Posted in ASM | Tagged: , , , , | 5 Comments »

Test Case for 11gR2 Role Separation issue on HP-UX – help wanted

Posted by John Hallas on May 6, 2012

On any HPUX (11:31) system where grid infrastructure has been applied with 2 software owners – in our case grid and oracle  – oracle’s best practise for GI implementations – RAC or standalone systems.

 The standard  unix account we are using  is , testuser (although in this case he is in the DBA group).

 This user is a member of the following groups :

 uid=664(testuser) gid=500(dba)

 Test Server       = server

Test database     = dbatest

 Login to the testuser user

  1. setsid to set Oracle environment to dbatest

 The ASM luns are owned by the grid user as below :

  

[testuser@server][dbatest]/dev/grid $ls -ltr
total 0
crw-rw----   1 grid       asmdba      13 0x00000a Apr 27 12:38 disk002
crw-rw----   1 grid       asmdba      13 0x000009 Apr 27 22:00 disk001
crw-rw----   1 grid       asmdba      13 0x00000b Apr 28 12:44 disk003

 logon onto unix server as testuser

Login into the database as “sqlplus / as sysdba”

Set database environment variables as appropriate (OH, SID,PATH)

 Database bounced to clear cache out (shared pool flush would also work) Read the rest of this entry »

Posted in ASM, Oracle | Tagged: , , , | 2 Comments »

 
Follow

Get every new post delivered to your Inbox.

Join 231 other followers