Oracle DBA – A lifelong learning experience

Archive for the ‘ASM’ Category

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

 

ALTER DISKGROUP TEST MOUNT;

SELECT STATE, NAME FROM V$ASM_DISKGROUP;

 

-- if all ok then

-- from node 2

alter diskgroup test dismount;

 

-- from node 1

DROP DISKGROUP TEST;

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

 

-- 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 in a single command one command Read the rest of this entry »

Posted in ASM, Oracle | Tagged: , , , | 1 Comment »

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 »

Huge asm_rbal_trace file with text UNINDENT OF DISK

Posted by John Hallas on May 3, 2012

You may notice a large and growing trace file in diag dest which contains numerous lines starting with the phrase “NOTE: Unident of disk ” followed by a disk path.

*** 2012-04-17 18:31:10.759
NOTE:Unident of disk:/dev/oracle_hr/rdisk130
NOTE:Unident of disk:/dev/oracle_hr/rdisk131
NOTE:Unident of disk:/dev/oracle_hr/rdisk132
NOTE:Unident of disk:/dev/oracle_hr/rdisk133
NOTE:Unident of disk:/dev/oracle_hr/rdisk134
NOTE:Unident of disk:/dev/oracle_hr/rdisk135
NOTE:Unident of disk:/dev/oracle_hr/rdisk136
NOTE:Unident of disk:/dev/oracle_hr/rdisk137
NOTE:Unident of disk:/dev/oracle_hr/rdisk138
NOTE:Unident of disk:/dev/oracle_hr/rdisk139
NOTE:Unident of disk:/dev/oracle_hr/rdisk129

*** 2012-04-17 19:05:20.540
NOTE:Unident of disk:/dev/oracle_hr/rdisk130
NOTE:Unident of disk:/dev/oracle_hr/rdisk131
NOTE:Unident of disk:/dev/oracle_hr/rdisk132
NOTE:Unident of disk:/dev/oracle_hr/rdisk133
NOTE:Unident of disk:/dev/oracle_hr/rdisk134
NOTE:Unident of disk:/dev/oracle_hr/rdisk135
NOTE:Unident of disk:/dev/oracle_hr/rdisk136
NOTE:Unident of disk:/dev/oracle_hr/rdisk137
NOTE:Unident of disk:/dev/oracle_hr/rdisk138
NOTE:Unident of disk:/dev/oracle_hr/rdisk139
NOTE:Unident of disk:/dev/oracle_hr/rdisk129 Read the rest of this entry »

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

Curing unevenly balanced ASM diskgroups to reduce poor file distribution

Posted by John Hallas on May 2, 2012

Back in Nov 2011 I posted a question on the Oracle-L mailing group about my perception that ASM disk rebalances seemed to be required on DATA diskgroups in ASM  (never FRA, presumably because the FRA had lots of similar sized objects (flashlogs archivelogs  etc)) and even after rebalancing there seemed to be a permanent imbalance between disks of the same size. I was querying the effectiveness of the rebalancing operation.

There was some good responses including a script that Dave Herring had created, based on  key MOS articles: 818171.1 (Identifying Files with Imbalances), 351117.1 (Troubleshooting ASM Space Issues), 367445.1 (Advanced Balance and Space Report on ASM). Manual rebalancing  would normally not be required, because ASM automatically rebalances disk groups when their configuration changes. You might want to do a manual rebalance operation if you want to control the speed of what would otherwise be an automatic rebalance operation.

I have been chasing this down and have now come across Bug 7699985: UNBALANCED DISTRIBUTION OF FILES ACROSS DISKS.

It appears to be a problem in 11.1.0.7 and I know a number of sites have reported it. Despite  repeated manual rebalance operations there can be a variance of up to 10% between different disks of the same diskgroup, even if they are the same size. It is fixed in 11.2.0.1.  The workaround is to set the _asm_imbalance_tolerance parameter to be 0 rather than the default of 3.This controls the hundredths of a percentage of inter-disk imbalance to tolerate. Then rebalance the disks manually and reset the parameter back again to 3 as you don’t need to leave it  balancing all the time.

SQL> @asm_imbalance   (from Report the Percentage of Imbalance in all Mounted Diskgroups (Doc ID 367445.1)

@asm_imbalance.sql

 Columns Described in Script               Percent Minimum
                                 Percent Disk Size Percent  Disk Diskgroup
Diskgroup Name                 Imbalance  Varience    Free Count Redundancy
------------------------------ --------- --------- ------- ----- ----------
DATA                                 9.0        .5    15.2    84 EXTERN

SYS@+ASM SQL>l
    Select dg.name,dg.allocation_unit_size/1024/1024 "AU(Mb)",min(d.free_mb) Min,
    max(d.free_mb) Max, avg(d.free_mb) Avg
    from v$asm_disk d, v$asm_diskgroup dg
    where d.group_number = dg.group_number
    group by dg.name, dg.allocation_unit_size/1024/1024
SY@+ASM SQL>/

NAME                               AU(Mb)        MIN        MAX    AVG
------------------------------ ---------- ---------- ---------- ------
DATA                                    1       7728      11599   8026

alter diskgroup data rebalance power 4;

NAME                               AU(Mb)        MIN        MAX    AVG
------------------------------ ---------- ---------- ---------- ------
DATA                                    1       7734      11483   8026 – no difference

alter system set "_asm_imbalance_tolerance"=0;
alter diskgroup data rebalance power 4;
NAME                               AU(Mb)        MIN        MAX        AVG
------------------------------ ---------- ---------- ---------- ----------
DATA                                    1       8020       8062   8026  - Now nicely rebalanced

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

The Mother of all ASM scripts

Posted by John Hallas on March 6, 2012

Back in 2009 I posted a script which I found very useful to review ASM disks. I gave that post the low-key title of The ASM script of all ASM scripts. Now that script has been improved I have to go a bit further with the hyperbole and we have the The Mother of all ASM scripts.  If it ever gets improved then the next post will just be called ‘Who’s the Daddy’.

I have been using the current script across all our systems for the last 3 years and I find it very useful, a colleague, Allan Webster, has added a couple of improvements and it is now better than before.

The improvements show current disk I/O statistics and a breakdown of the types of files in each disk group and the total sizes of that filetype. The I/O statistics are useful when you have a lot of databases, many of which are test and development and so you do not look at them as that often. It just gives a quick overview that allows you to get a feel if anything is wrong and to see what the system is actually doing. There are also a few comments at the beginning defining the various ASM views available. Read the rest of this entry »

Posted in ASM, Oracle, scripts | Tagged: , , | 7 Comments »

 
Follow

Get every new post delivered to your Inbox.

Join 189 other followers