ASM


Back to the basics: correct parameter from SPFILE in ASM

When you change the size of memory and forget to update SGA_MAX_SIZE it might happen:

oracle@anjo01:/home/oracle [] srvctl start database -d p05ltm02
PRCR-1079 : Failed to start resource ora.p05ltm02.db
CRS-5017: The resource action "ora.p05ltm02.db start" encountered the following error:
ORA-01078: failure in processing system parameters
ORA-00823: Specified value of sga_target greater than sga_max_size

As this is a clustered database with ASM, here the easiest way to solve:

1. Find the full path of the SPFILE on ASM

oracle@anjo01:/home/oracle [p05ltm02_1] srvctl config database -d p05ltm02 -a
Database unique name: p05ltm02
Database name: ltm02
Oracle home: /oracle/app/product/11.2.0.4
Oracle user: oracle
Spfile: +DGDATA/p05ltm02/spfileltm02.ora
Domain:
Start options: open
Stop options: immediate
Database role: PRIMARY
Management policy: AUTOMATIC
Server pools: sp_ha01
Database instances:
Disk Groups: DGDATA,DGFRA
Mount point paths:
Services: p05ltm02_app.anjo01
Type: RACOneNode
Online relocation timeout: 30
Instance name prefix: p05ltm02
Candidate servers: anjo01,anjo02
Database is enabled
Database is policy managed

2. Create a PFILE from SPFILE on the local filesystem

oracle@anjo01:/home/oracle [p05ltm02_1] sqh
SQL*Plus: Release 11.2.0.4.0 Production on Tue Feb 2 09:33:47 2016
Copyright (c) 1982, 2013, Oracle.  All rights reserved.
Connected to an idle instance.

SQL> create pfile='/tmp/pfile.txt' from spfile='+DGDATA/p05ltm02/spfileltm02.ora';
File created.

SQL> exit
Disconnected

3. Correct the damn parameter

oracle@anjo01:/home/oracle [p05ltm02_1] vi /tmp/pfile.txt

4. Recreate the SPFILE on ASM:

oracle@anjo01:/home/oracle [p05ltm02_1] sqh
SQL*Plus: Release 11.2.0.4.0 Production on Tue Feb 2 09:34:44 2016
Copyright (c) 1982, 2013, Oracle.  All rights reserved.
Connected to an idle instance.

SQL> create spfile='+DGDATA/p05ltm02/spfileltm02.ora' from pfile='/tmp/pfile.txt';
File created.

SQL> exit
Disconnected

5. And finally restart the database:

oracle@anjo01:/home/oracle [p05ltm02_1] srvctl start database -d p05ltm02

Add path to ASMCMD prompt

Just discovered this morning how to show the path on the ASMCMD tool and could not be easier:

anjovm2:/home/grid:+ASM2 $ asmcmd -p
ASMCMD [+] > cd dgdata
ASMCMD [+dgdata] > cd db12c
ASMCMD [+dgdata/db12c] > ls
CONTROLFILE/
DATAFILE/
ONLINELOG/
PARAMETERFILE/
TEMPFILE/
spfiledb12c.ora
ASMCMD [+dgdata/db12c] > cd datafile
ASMCMD [+dgdata/db12c/datafile] >

Find orphan ASM files 5

These days I’ve been doing the migration of some BI databases into a private cloud, about 7 TB of data in total. We made sure that before starting there was enough disk space.

However, in the middle of the largest “duplicate from active database”, it stops. When trying to restart, it complaints that disk group space was exhausted.

After some investigation, I found out that for a previous DB, which made 1.8 TB in datafiles, it was using above 3 TB in ASM. A closer investigation and I could see several copies of some datafiles. I believe it come from the fact that there were network problems, duplicates that were repeated with some interval between, rolling forward with RMAN incremental backup, switch to copy… a whole mess. 🙂

Then come the problem:

– how to make a easy way to detect all orphan files on ASM?

The first solution that come to mind was to spool the list of data files and, from asmcmd, output all the files for the problematic DB with the command:

asmcmd ls dgdata/O01XXXXX/datafile > /tmp/f_asm.txt

Then put both files as external tables into a database and query them. But there were several steps and was hard work.

The second solution was to use v$asm_file and v$asm_alias together with queries to v$datafile, v$logfile and v$controlfile. This from the database instance, even if the documentation states that:
“In a database instance, V$ASM_FILE displays no rows.”
(http://docs.oracle.com/cd/E11882_01/server.112/e25513/dynviews_1029.htm#REFRN30172)

For this solution I’ve based on a query from http://aychin.wordpress.com/2011/07/26/dfdg-script-to-display-asm-disk-usage-information-and-files/

And the solution is:

DEFINE ASMGROUP="DGDATA"
 
set linesize 200
set pagesize 50000
col reference_index noprint
col type format a15
col files format a80

WITH v_asmgroup AS (SELECT group_number FROM v$asm_diskgroup WHERE name='&ASMGROUP'),
     v_parentindex AS (SELECT parent_index 
                    FROM v$asm_alias 
              WHERE group_number = (SELECT group_number FROM v_asmgroup) 
                AND alias_index=0),
  v_asmfiles AS (SELECT file_number, type 
              FROM v$asm_file 
           WHERE group_number = (SELECT group_number FROM v_asmgroup)),
 v_dbname AS (SELECT '/'||upper(db_unique_name)||'/' dbname from v$database)
SELECT 'rm '|| files files FROM -- this line show the delete command
(
  SELECT '+&ASMGROUP'||files files, type 
  FROM (SELECT upper(sys_connect_by_path(aa.name,'/')) files, aa.reference_index, b.type
        FROM (SELECT file_number,alias_directory,name, reference_index, parent_index 
        FROM v$asm_alias) aa,
             (SELECT parent_index FROM v_parentindex) a,
             (SELECT file_number, type FROM v_asmfiles) b
  WHERE aa.file_number=b.file_number(+)
    AND aa.alias_directory='N'
   -- missing PARAMETERFILE, DATAGUARDCONFIG
   AND b.type in ('DATAFILE','ONLINELOG','CONTROLFILE','TEMPFILE')
  START WITH aa.PARENT_INDEX=a.parent_index
  CONNECT BY PRIOR aa.reference_index=aa.parent_index)
  WHERE substr(files,instr(files,'/',1,1),instr(files,'/',1,2)-instr(files,'/',1,1)+1) = (select dbname FROM v_dbname)
MINUS (
  SELECT upper(name) files, 'DATAFILE' type FROM v$datafile
    UNION ALL 
  SELECT upper(name) files, 'TEMPFILE' type FROM v$tempfile
    UNION ALL
 SELECT upper(name) files, 'CONTROLFILE' type FROM v$controlfile WHERE name like '+&ASMGROUP%'
    UNION ALL
 SELECT upper(member) files, 'ONLINELOG' type FROM v$logfile WHERE member like '+&ASMGROUP%'
)
);

FILES
--------------------------------------------------------------------------------
rm +DGDATA/O01XXXXX/CONTROLFILE/CURRENT.800.807811333

So then I just need to run from asmcmd:
ASMCMD> rm +DGDATA/O01XXXXX/CONTROLFILE/CURRENT.800.807811333

Because WITH clause does not work on MOUNTED databases (like a standby) – SQL Statement Using ‘WITH’ Clause Fails with ORA-01219 [ID 1166073.1] , here the version without the WITH clause:

DEFINE ASMGROUP="DGDATA"
 
set linesize 200
set pagesize 50000
col reference_index noprint
col type format a15
col files format a80

SELECT 'rm '|| files files FROM -- this line show the delete command
(
  SELECT '+&ASMGROUP'||files files, type 
  FROM (SELECT upper(sys_connect_by_path(aa.name,'/')) files, aa.reference_index, b.type
        FROM (SELECT file_number,alias_directory,name, reference_index, parent_index 
        FROM v$asm_alias) aa,
             (SELECT parent_index FROM (SELECT parent_index 
                    FROM v$asm_alias 
              WHERE group_number = (SELECT group_number FROM v$asm_diskgroup WHERE name='&ASMGROUP') 
                AND alias_index=0)) a,
             (SELECT file_number, type FROM (SELECT file_number, type 
                                       FROM v$asm_file 
                                    WHERE group_number = (SELECT group_number FROM v$asm_diskgroup WHERE name='&ASMGROUP'))) b
  WHERE aa.file_number=b.file_number(+)
    AND aa.alias_directory='N'
   -- missing PARAMETERFILE, DATAGUARDCONFIG
   AND b.type in ('DATAFILE','ONLINELOG','CONTROLFILE','TEMPFILE')
  START WITH aa.PARENT_INDEX=a.parent_index
  CONNECT BY PRIOR aa.reference_index=aa.parent_index)
  WHERE substr(files,instr(files,'/',1,1),instr(files,'/',1,2)-instr(files,'/',1,1)+1) = (select dbname FROM (SELECT '/'||upper(db_unique_name)||'/' dbname from v$database))
MINUS (
  SELECT upper(name) files, 'DATAFILE' type FROM v$datafile
    UNION ALL 
  SELECT upper(name) files, 'TEMPFILE' type FROM v$tempfile
    UNION ALL
 SELECT upper(name) files, 'CONTROLFILE' type FROM v$controlfile WHERE name like '+&ASMGROUP%'
    UNION ALL
 SELECT upper(member) files, 'ONLINELOG' type FROM v$logfile WHERE member like '+&ASMGROUP%'
)
);