Using XML 07: SELECT, Part 2/3

Because the EXTRACT [1] and EXTRACTVALUE [2] functions are deprecated in 11gR2 [10], you have to use the XMLQuery [3] and XMLTable [4] (respectively).

XMLQuery [1]

XMLQUERY lets you query XML data in SQL statements. It takes an XQuery expression as a string literal, an optional context item, and other bind variables and returns the result of evaluating the XQuery expression.

The more simple syntax is

XMLQuery(XQuery_string PASSING expr RETURNING CONTENT)

where [1]

• XQuery_string is a complete XQuery expression, including prolog
• The expr is an expression returning an XMLType or an instance of a SQL scalar data type that is used as the context for evaluating the XQuery expression. 
• RETURNING CONTENT indicates that the result from the XQuery evaluation is either an XML 1.0 document or a document fragment conforming to the XML 1.0 semantics.

or in simple words

• XMLQueryis the search path
• "expr" is the XML column of the table 

Here there are 3 examples, so you can compare the output, with the one of EXTRACT function provided in the previous post

SQL> SELECT rownum, t.*, 
  2> XMLQuery('/alertlog' PASSING t.XML_COLUMN  RETURNING CONTENT) extract FROM mytab t

SQL> SELECT rownum, t.*, 
  2> XMLQuery('/alertlog/msg' PASSING t.XML_COLUMN  RETURNING CONTENT) extract FROM mytab t

SQL> SELECT rownum, t.*,
  2> XMLQuery('/alertlog/msg/txt' PASSING t.XML_COLUMN  RETURNING CONTENT) extract FROM mytab t

Here other two examples

As you can see, also if there is a little difference in the syntax between XMLQuery and EXTRACT function, the outptu is the same. The XMLQuery function returns the TAGs. The same thing of the EXTRACT function.

In the next post XMLTable


References
[1] https://docs.oracle.com/database/122/SQLRF/EXTRACT-XML.htm#SQLRF00640
[2] https://docs.oracle.com/database/122/SQLRF/EXTRACTVALUE.htm#SQLRF06173
[3] https://docs.oracle.com/database/122/SQLRF/XMLQUERY.htm#SQLRF06209
[4] https://docs.oracle.com/database/122/SQLRF/XMLTABLE.htm#SQLRF06232
[5] http://allthingsoracle.com/xquery-for-absolute-beginners-part-1-xpath/
[6] http://allthingsoracle.com/xquery-for-absolute-beginners-part-2-xquery-and-the-oracle-database/
[7] https://www.youtube.com/watch?v=n_dEb6myFH4
[8] https://www.youtube.com/watch?v=5d4fZVxFx20
[9] Pro Oracle SQL, pag 481 written by Morton, Osborne, Shamsudeen [Apress]
[10] http://www.oracle.com/technetwork/database/features/xmldb/xmlqueryoptimize11gr2-168036.pdf (PDF)

Part06 Index Part08

Using XML 06: SELECT, Part 1/3

In the last two posts [3][4], I was shown how to insert a record (an XML document) in an XML column. Here I want to show how to select data inside an XML column

In order to SELECT data, you have to use some functions. Two of these are EXTRACT [1] and EXTRACTVALUE [2]

EXTRACT [1]

It applies to a VARCHAR2 XPath string and returns an XMLType instance containing an XML fragment. You can specify an absolute XPath_string with an initial slash or a relative XPath_string by omitting the initial slash. If you omit the initial slash, the context of the relative path defaults to the root node.

EXTRACT (<XMLType_instance>, <XPATH_string>)

I start using just EXTRACT(XML_COLUMN, '/alertlog')

The result is the entire XML content. This is because I chosen the root node. So, what I expect if I use EXTRACT(XML_COLUMN, '/alertlog/msg') is the EXTRACT column without the root node. And in fact:

A similar things I expect if I use EXTRACT(XML_COLUMN, '/alertlog/msg/txt'). I mean, the output should be only the "txt" element.

In all cases, only one row is returned (yes there is one row in the table).

EXTRACTVALUE [2]

The EXTRACTVALUE function takes as arguments an XMLType instance and an XPath expression and returns a scalar value of the resultant node. The result must be a single node and be either a text node, attribute, or element. If the result is an element, then the element must have a single text node as its child, and it is this value that the function returns. You can specify an absolute XPath_string with an initial slash or a relative XPath_string by omitting the initial slash. If you omit the initial slash, the context of the relative path defaults to the root node.

EXTRACTVALUE (<XMLType_instance>, <XPATH_string>)

I have to follow the manual above: The result must be a single node and be either a text node, attribute, or element. If the result is an element, then the element must have a single text node as its child, and it is this value that the function returns. This is why I had previous exception. If I specify just an element, then...

Also this, is a valid statement

As you see in this case, only onw row is returned. Anyway, the documentation says:

The EXTRACT (XML) function is deprecated. It is still supported for backward compatibility. However, Oracle recommends that you use the XMLQUERY function instead. See XMLQUERY for more information [1]

The EXTRACTVALUE function is deprecated. It is still supported for backward compatibility. However, Oracle recommends that you use the XMLTABLE function, or the XMLCAST and XMLQUERY functions instead. See XMLTABLE, XMLCAST, and XMLQUERY for more information [2]

In the next post. we see how to use the XMLQuery function in order to obtain the same result

References

[1] https://docs.oracle.com/database/122/SQLRF/EXTRACT-XML.htm#SQLRF00640
[2] https://docs.oracle.com/database/122/SQLRF/EXTRACTVALUE.htm#SQLRF06173
[3] https://orasal.blogspot.it/2017/08/using-xml-04-insert-into-table.html
[4] https://orasal.blogspot.it/2017/08/using-xml-04-insert-into-table-part-22.html

Part05 Index Part07

Using XML 05: INSERT into table, Part 2/2

In the last post, I tried to insert an extract of the alertlog.xml into an XML column

INSERT INTO mytab VALUES (1, XMLType(bfilename('MYXMLDIR', 'mylog.xml'), nls_charset_id('AL32UTF8')));

and I had the following exception

ERROR at line 1:
ORA-31061: XDB error: XML event error
ORA-19202: Error occurred in XML processing
In line 1 of orastream:
LPX-00225: end-element tag "txt" does not match start-element tag ""

Really I aspected this error, because I'm not sure if my "tail" command created a "well formed" XML alert log file. So I have to check it.

Here you see that the extract of the alertlog.xml begin with two closed TAG "</txt>" and "</msg>", so I have to remove those first two lines and then reinsert my log file. This is how the file starts after the modifications:

Now I can do a new insert

Ouch! Another error: LPX-00245. If I check the contents of the log.xml....

...there is a TAG </msg>", but it appears more times, so it isn't a root node. I have to add a root TAG as the first and last line. I choose "<alertlog></alertlog>" TAG

At this point, I expect that my log.xml file is well formed and that the new insert should be OK.

Yessss. I inserted my first XML row. Now I want to see what there is in this column

Only the first character are shown. This is because the XML is managed by SQL*Plus as a LONG type, so I have to modify the "long" settings

(Some output was deleted)

Conclusions (until now)

1. The insert of an XML document into an XML column will insert just one row.
2. The XML type check automatically if the XML document is well formed [1]

References

[1] https://docs.oracle.com/cd/B19306_01/appdev.102/b14259/xdb01int.htm#BABCCCJI

Part 04 Index Part06

Using XML 04: INSERT into table, Part 1/2

What I want to do in this post is to try to insert an XML document into a table. Because in Oracle 11g+ there is an XML version of the alert.log, I take just some lines from it and save the output in a file. I save the new file in the /tmp/mylog.xml file.

Now I create a table with just two columns. One will be the primary key, the other one will be an XML column type

 I run a very simple insert, trying to put the "this-is-a-test" string in the XML column.

The exception LPX-00210 is what exactly I expect: if you remember, a "well formed" XML document, must begin with angular parentheses (<).

In order to insert the XML content of the "mylog.log" file into MYTAB table, what I have really to do, is to run the following statement:

INSERT INTO mytab
VALUES (1, XMLType(bfilename('MYXMLDIR', 'mylog.xml'), nls_charset_id('AL32UTF8')));

Because of this, I have to introduce "XMLType" and "bfilename" first.

BFILENAME [1]

BFILENAME ('<oracle_directory>', '<filename>')

BFILENAME returns a BFILE locator that is associated with a physical LOB binary file on the server file system.
'oracle_directory' is a database object that serves as an alias for a full path name on the server file system where the files are actually located. 'filename' is the name of the file in the server file system.

You must create the directory object and associate a BFILE value with a physical file before you can use them as arguments to BFILENAME in a SQL or PL/SQL statement.

Defining XMLType Tables and Columns [2][3][5]

You can use a simple INSERT operation in SQL or PL/SQL to load an XML document into the database. Before the document can be stored as an XMLType column or table, you must convert it into an XMLType instance using one of the XMLType constructors.

XMLType constructors allow an XMLType instance to be created from different sources, including VARCHAR, CLOB, and BFILE values.

You can use XMLType as you would any other SQL data type. For example, you can create an XMLType table or view, or an XMLType column in a relational table.

You can use XMLType in PL/SQL stored procedures for parameters, return values, and variables. XMLType is an Oracle Database object type, so you can also create a table of XMLType object instances.

By default, an XMLType table or column can contain any well-formed XML document XMLType is an abstract data type that provides different storage models to best fit your data and your use of it. 

XMLType tables and columns can be stored in these ways (there are two possibilities):

• The table is relational, with an XMLType column and a non-XMLType column.
• The table is of data type XMLType.

http://docs.oracle.com/database/122/ADXDB/how-to-use-XML-DB.htm#ADXDB6338

This section describe an XMLType constructor [4]:

constructor function XMLType(
   xmlData    IN clob
 , schema     IN varchar2 := NULL
 , validated  IN number   := 0
 , wellformed IN number   := 0
)
return self as result deterministic;

constructor function XMLType(
   xmlData    IN varchar2
 , schema     IN varchar2 := NULL
 , validated  IN number   := 0
 , wellformed IN number   := 0
)
return self as result deterministic;

constructor function XMLType(
   xmlData    IN blob
 , csid       IN number   /* The character set id of input XML data */
 , schema     IN varchar2 := NULL
 , validated  IN number   := 0
 , wellformed IN number   := 0
)
return self as result deterministic;

constructor function XMLType(
   xmlData    IN bfile
 , csid       IN number   /* The character set id of input XML data */
 , schema     IN varchar2 := NULL
 , validated  IN number   := 0
 , wellformed IN number   := 0
)
return self as result deterministic;

How you can see, XMLType constructor accept as the first parameter a CLOB, a VARCHAR2, a BinaryLOB or BinaryFILE. This is why you read above.

XMLType constructors allow an XMLType instance to be created from different sources, including VARCHAR, CLOB, and BFILE values.

Also, if you pass to the constructor a BLOB or a BFILE, you must specify the CHARACTER_SET (csid value). But if you are using a CLOB or VARCHAR2, you haven't specify this. I suppose that this is because in case of "CHARACTER", the right set is derived directly from da database NLS_CHARCTER_SET parameter.

So I can write (in the more simple form, that is, passing the first two parameters):

XMLType (xmlData IN blob, csid IN number)

where (I'm using the AL32UTF8 CHARACTER_SET in my DBs)

xmlData ::= BFILENAME ('<oracle_directory>', '<filename>') (see BFILENAME above)

csid    ::= NLS_CHARSET_ID('AL32UTF8')

I can rewrite the XMLType like this

XMLType (BFILENAME ('<oracle_directory>', '<filename>'), NLS_CHARSET_ID ('AL32UTF8'))

At this point, I'm ready to insert my alert log file in MYTAB table. First I create the Oracle directory

then I populate the table

Should I expect the LPX-00225 exception? Maybe yes.

(To be continued)

References
[1] https://docs.oracle.com/database/122/SQLRF/BFILENAME.htm#SQLRF00610
[2] http://docs.oracle.com/database/122/ADXDB/how-to-use-XML-DB.htm#ADXDB4049
[3] http://docs.oracle.com/database/122/ADXDB/how-to-use-XML-DB.htm#ADXDB6338
[4] https://docs.oracle.com/database/122/ARPLS/XMLTYPE.htm#ARPLS71992
[5] https://docs.oracle.com/cd/B19306_01/appdev.102/b14259/xdb01int.htm#BABCCCJI

Part03 Index Part05

Using XML 03: Introduction, Part 3/3

In this post I continue the introduction of an XML document.

XML Namespaces

XML Namespaces provide a method to avoid element name conflicts.

Please, see [1]: they explain in really simple way what the "XML Namespace are.

XML and XPath [2][3]

The XPath language is based on a tree representation of the XML document, and provides the ability to navigate around the tree, selecting nodes by a variety of criteria. It is used to navigate through elements and attributes.

Based on the previosu XML document, you can generalyze it as follow:

XML documents have a hierarchical structure and can conceptually be interpreted as a tree structure, called an XML tree.

XML documents must contain a root element (one that is the parent of all other elements). All elements in an XML document can contain sub elements, text and attributes. The tree represented by an XML document starts at the root element and branches to the lowest level of elements.

XPath defines a syntax named XPath expressions that identifies one or more internal components (elements, attributes, etc.) of an XML document. In popular use (though not in the official specification), an XPath expression is often referred to simply as "an XPath".

Syntax and semantics (XPath 1.0) [2]

The most important kind of expression in XPath is a location path. A location path consists of a sequence of location steps. Each location step has three components:

• An axis. An "axis" specifiers indicate navigation direction within the tree representation of the XML document. The axes available are

https://en.wikipedia.org/wiki/XPath#Axis_specifiers

• A node test

https://en.wikipedia.org/wiki/XPath#Node_tests

• Zero or more predicates.

https://en.wikipedia.org/wiki/XPath#Predicates

In the table below we have listed some XPath expressions and the result of the expressions:

References

[1] https://www.w3schools.com/xml/xml_namespaces.asp
[2] https://en.wikipedia.org/wiki/XPath
[3] https://www.w3schools.com/xml/xml_xpath.asp
[4] https://en.m.wikipedia.org/wiki/XML_tree

Part02 Index Part 04

Using XML 02: Introduction, Part 2/3

In the previous post, I introduced an XML file, in its more simple form. Here I would like to be a little more formal. As usual, check the References section to read the sources for more information.

Consider following XML document (I took the information in [1] and [2])

XML Tree Structure

How wrote in [3], a general structure of an XML document is

<root>
  <child>
    <subchild>.....</subchild>
  </child>
</root>


The terms parent, child, and sibling are used to describe the relationships between elements. Parent have children. Children have parents. Siblings are children on the same level (brothers and sisters). All elements can have text content (Death Times Three) and attributes (category="Nero Wolfe").

Self-Describing Syntax

XML uses a much self-describing syntax.

A prolog defines the XML version and the character encoding:

<?xml version="1.0" encoding="UTF-8"?&gt;

The next line is the root element of the document:

<bibliography>

The next line starts a <book> element:

<book category="Nero Wolfe">

The <book> elements have 3 child elements: <title>, <publisher>, <year>.

<Title lang="en">Death Times Three</Title>

<Publisher>Bantam Books</Publisher>

<year month="December">1985</year>

The next line ends the book element:

</book>

XML Syntax Rules [5]

The syntax rules of XML are very simple and logical. The rules are easy to learn and easy to use. If an XML document conforms to the syntax rules below, then it said to be "Well Formed".

XML Documents Must Have a Root Element

<bibliography>
 <book category="Nero Wolfe">

  <Title lang="en">Death Times Threegt;/Title>
  <Publisher>Bantam Books>/Publisher>
  <year month="December">1985>/year>
 </book>
<bibliography>

The XML Prolog

The XML prolog is optional. If it exists, it must come first in the document: 

<?xml version="1.0" encoding="UTF-8"?>

All XML Elements Must Have a Closing Tag

<bibliography>
 <book category="Nero Wolfe">
  <Title lang="en">Death Times Three</Title>
  <Publisher>Bantam Books</Publisher>
  <year month="December">1985</year>
 </book>
</bibliography>

XML Tags are Case Sensitive

<bibliography>
 <book category="Nero Wolfe">
  <Title lang="en">Death Times Three</Title>
  <Publisher>Bantam Books</Publisher>
  <year month="December">1985</year>
 </book>
</bibliography>

XML Elements Must be Properly Nested

<bibliography>
 <book category="Nero Wolfe">
  <Title lang="en">Death Times Three</Title>
  <Publisher>Bantam Books</Publisher>
  <year month="December">1985</year>
 </book>
</bibliography>

XML Attribute Values Must be Quoted

<bibliography>
 <book category="Nero Wolfe">
  <Title lang="en">Death Times Three</Title>
  <Publisher>Bantam Books</Publisher>
  <year month="December">1985</year>
 </book>
</bibliography>

You can use double quotes or single quotes. If the attribute value itself contains double quotes you can use single quotes.

Entity References

Some characters have a special meaning in XML.

You have to use the letters (the column on the left) instead of the symbols (central column).

Comments in XML

The XML comment is optional. If it exists, it must follow rules

• <!-- This is a comment -->
• Two dashes in the middle of a comment are not allowed.

XML Stores New Line as LF [5]

• Windows applications store a new line as carriage return and line feed (CR+LF)
• Unix and Mac OSX uses LF
• Old Mac systems use CR
• XML stores a new line as LF

References
[1] https://en.wikipedia.org/wiki/Rex_Stout_bibliography
[1] https://it.wikipedia.org/wiki/Rex_Stout
[2] https://en.m.wikipedia.org/wiki/XML_tree
[3] https://www.w3schools.com/xml/xml_tree.asp
[4] https://www.w3schools.com/xml/xml_namespaces.asp
[5] https://www.w3schools.com/xml/xpath_syntax.asp
[6] https://www.w3schools.com/xml/xml_xpath.asp
[7] https://en.m.wikipedia.org/wiki/XML_tree
[8] https://en.wikipedia.org/wiki/XPat

Part01 Index Part03

Removing an ADVM resource from CRS

Some days ago I have had some issue on a CRS resource.

Facts

I created a DB on ASM. Also, I use the ACFS (ASM Cluster File System) in order to store archived log and others logs like the alert.log

This DB was created on a clone environment. The day after I created the DB on production. So I had two independent installations and configurations but, one is the clone of the other.

Usually, the correct steps are:

1. Install and configure the DB in production
2. Clone the DB
3. Start the clone

In this case instead:

1. Created a DB on the clone
2. Created a DB in production
3. Synchronized the DB clone starting from DB in production

Following picture try to explain what I mean

Symptoms

Because of this order, the Logical Volume on prod and clone are different.

What the CRS knows is:

[clone01.+ASM1]/sbRdbms/oracle-$acfsutil registry –l |grep -i llvm
Device : /dev/asm/lvllvmpak-486 : Mount Point : /dbLLVMp/akLLVMp01 : Options : none : Nodes : all : Disk Group : DGLLVMPFS : Volume : LVLLVMPAK
Device : /dev/asm/lvllvmpfs-486 : Mount Point : /dbLLVMp/fsLLVMp01 : Options : none : Nodes : all : Disk Group : DGLLVMPFS : Volume : LVLLVMPFS

On the DGLLVMPFS Disk Group, there two Logical Volume: LBLLVMPAK for the archived log with /dev/asm/lvllvmpak-486 device and LBLLVMPFS for the log files with /dev/asm/lvllvmpfs-486 device.

But because the DG are imported from the production, they had different pieces of information

[clone01.+ASM1]/sbRdbms/oracle-$asmcmd volinfo -G DGLLVMPFS -a
Diskgroup Name: DGLLVMPFS

         Volume Name: LVLLVMPAK
         Volume Device: /dev/asm/lvllvmpak-34
         State: DISABLED
         Size (MB): 102400
         Resize Unit (MB): 64
         Redundancy: UNPROT
         Stripe Columns: 8
         Stripe Width (K): 1024
         Usage: ACFS
         Mountpath: /dbLLVMp/akLLVMp01

         Volume Name: LVLLVMPFS
         Volume Device: /dev/asm/lvllvmpfs-34
         State: DISABLED
         Size (MB): 51008
         Resize Unit (MB): 64
         Redundancy: UNPROT
         Stripe Columns: 8
         Stripe Width (K): 1024
         Usage: ACFS
         Mountpath: /dbLLVMp/fsLLVMp01

That is, there is always a DGLLVMPFS Disk Group with the LBLLVMPAK and LBLLVMPFS Logical Volume, but in this case, the former device is /dev/asm/lvllvmpak-34 and the latter is /dev/asm/lvllvmpfs-34.

The status of the resources are

[clone01.+ASM1]/sbRdbms/oracle-$cstat |grep -i llvm
===================================================================================================
HA Resource                         Resource Type                  Target     State
===================================================================================================
ora.DGLLVMPDB01.dg                  ora.diskgroup.type             ONLINE     ONLINE on clone01
ora.DGLLVMPDB01.dg                  ora.diskgroup.type             ONLINE     ONLINE on clone02
ora.DGLLVMPDB02.dg                  ora.diskgroup.type             ONLINE     ONLINE on clone01
ora.DGLLVMPDB02.dg                  ora.diskgroup.type             ONLINE     ONLINE on clone02
ora.DGLLVMPFS.LVLLVMPAK.advm        ora.volume.type                ONLINE     UNKNOWN on clone01
ora.DGLLVMPFS.LVLLVMPAK.advm        ora.volume.type                ONLINE     UNKNOWN on clone02
ora.DGLLVMPFS.LVLLVMPFS.advm        ora.volume.type                ONLINE     UNKNOWN on clone01
ora.DGLLVMPFS.LVLLVMPFS.advm        ora.volume.type                ONLINE     UNKNOWN on clone02
ora.DGLLVMPFS.dg                    ora.diskgroup.type             ONLINE     ONLINE on clone01
ora.DGLLVMPFS.dg                    ora.diskgroup.type             ONLINE     ONLINE on clone02
ora.LISTENER_LLVMP.lsnr             ora.listener.type              OFFLINE    OFFLINE
ora.LISTENER_LLVMP.lsnr             ora.listener.type              ONLINE     ONLINE on clone02
ora.llvmp.db                        ora.database.type              OFFLINE    OFFLINE
ora.llvmp.db                        ora.database.type              OFFLINE    OFFLINE
ora.llvmp.llvmp_batch.svc           ora.service.type               OFFLINE    OFFLINE
ora.dgllvmpfs.lvllvmpak.acfs        ora.acfs.type                  OFFLINE    OFFLINE
ora.dgllvmpfs.lvllvmpak.acfs        ora.acfs.type                  OFFLINE    OFFLINE
ora.dgllvmpfs.lvllvmpfs.acfs        ora.acfs.type                  OFFLINE    OFFLINE
ora.dgllvmpfs.lvllvmpfs.acfs        ora.acfs.type                  ONLINE     OFFLINE

This outptu is reformatted using the script you can find here. (the script in the post is updated. The one I used here is an old version, so it will be a little different).

As you can see, the STATE of the resources is UNKNOW.

Solution

There is a Doc ID on MOS, that I followed in order to resolve the problem: "ODA: How to Remove an ADVM CRS Resource When It has not Any Associated ADVM Volume in /dev/asm/* (Doc ID 2174600.1) (PDF)". Also there are a couple of command I used from the "Automatic Storage Management Administrator's Guide"

So, the steps I made was:

1. Disable the Logical Volume
2. Deregister the file system
3. Delete the Resource from crs
4. Enable the Logical Volume
5. Register the file system

1) Disable the Logical Volume

I run this step from [1]:

[clone01.+ASM1]/sbRdbms/oracle-$/sbOraGrid/12.1.0.2/grid/bin/asmcmd voldisable -G DGLLVMPAK -a
[clone01.+ASM1]/sbRdbms/oracle-$/sbOraGrid/12.1.0.2/grid/bin/asmcmd voldisable -G DGLLVMPFS -a

[clone02.+ASM2]/sbRdbms/oracle-$/sbOraGrid/12.1.0.2/grid/bin/asmcmd voldisable -G DGLLVMPAK -a
[clone02.+ASM2]/sbRdbms/oracle-$/sbOraGrid/12.1.0.2/grid/bin/asmcmd voldisable -G DGLLVMPFS -a

Please, pay attention. I run the commands on all nodes of the RAC.

2) Deregister the file system

This command come from [1]

[root@clone01 ~]#/sbin/acfsutil registry -d /dev/asm/lvllvmpfs-486

I run the command as root user. After this, the ADVM (ASM Dynamic Volume Manager) for LVLLVMFS, disappear. It only exists the ADVM resource for the AK Logical Volume.

[mdapora2d01-TP.+ASM1]/sbRdbms/oracle-$cstat |grep -i llvm
===================================================================================================
HA Resource                         Resource Type                  Target     State
===================================================================================================
ora.DGLLVMPDB01.dg                  ora.diskgroup.type             ONLINE     ONLINE on clone01
ora.DGLLVMPDB01.dg                  ora.diskgroup.type             ONLINE     ONLINE on clone02
ora.DGLLVMPDB02.dg                  ora.diskgroup.type             ONLINE     ONLINE on clone01
ora.DGLLVMPDB02.dg                  ora.diskgroup.type             ONLINE     ONLINE on clone02
ora.DGLLVMPFS.LVLLVMPAK.advm        ora.volume.type                OFFLINE    UNKNOWN on clone01
ora.DGLLVMPFS.LVLLVMPAK.advm        ora.volume.type                OFFLINE    UNKNOWN on clone02
ora.DGLLVMPFS.LVLLVMPFS.advm        ora.volume.type                OFFLINE    UNKNOWN on clone01
ora.DGLLVMPFS.LVLLVMPFS.advm        ora.volume.type                OFFLINE    UNKNOWN on clone02
ora.DGLLVMPFS.dg                    ora.diskgroup.type             ONLINE     ONLINE on clone01
ora.DGLLVMPFS.dg                    ora.diskgroup.type             ONLINE     ONLINE on clone02
ora.LISTENER_LLVMP.lsnr             ora.listener.type              OFFLINE    OFFLINE
ora.LISTENER_LLVMP.lsnr             ora.listener.type              ONLINE     ONLINE on clone02
ora.llvmp.db                        ora.database.type              OFFLINE    OFFLINE
ora.llvmp.db                        ora.database.type              OFFLINE    OFFLINE
ora.llvmp.llvmp_batch.svc           ora.service.type               OFFLINE    OFFLINE
ora.dgllvmpfs.lvllvmpak.acfs        ora.acfs.type                  OFFLINE    OFFLINE
ora.dgllvmpfs.lvllvmpak.acfs        ora.acfs.type                  OFFLINE    OFFLINE

With following command, I remove/deregister the last resource.

[root@clone01 ~]#/sbin/acfsutil registry -d /dev/asm/lvllvmpak-486

3) Delete the Resource from crs

These commands come from [2]:

[clone01.+ASM1]/sbRdbms/oracle-$/sbOraGrid/12.1.0.2/grid/bin/crsctl delete resource ora.DGLLVMPFS.LVLLVMPFS.advm -f -unsupported

[clone01.+ASM1]/sbRdbms/oracle-$cstat |grep -i llvm
ora.DGLLVMPDB01.dg                  ora.diskgroup.type             ONLINE     ONLINE on clone01
ora.DGLLVMPDB01.dg                  ora.diskgroup.type             ONLINE     ONLINE on clone02
ora.DGLLVMPDB02.dg                  ora.diskgroup.type             ONLINE     ONLINE on clone01
ora.DGLLVMPDB02.dg                  ora.diskgroup.type             ONLINE     ONLINE on clone02
ora.DGLLVMPFS.LVLLVMPAK.advm        ora.volume.type                OFFLINE    UNKNOWN on clone01
ora.DGLLVMPFS.LVLLVMPAK.advm        ora.volume.type                OFFLINE    UNKNOWN on clone02
ora.DGLLVMPFS.dg                    ora.diskgroup.type             ONLINE     ONLINE on clone01
ora.DGLLVMPFS.dg                    ora.diskgroup.type             ONLINE     ONLINE on clone02
ora.LISTENER_LLVMP.lsnr             ora.listener.type              OFFLINE    OFFLINE
ora.LISTENER_LLVMP.lsnr             ora.listener.type              ONLINE     ONLINE on clone02
ora.llvmp.db                        ora.database.type              OFFLINE    OFFLINE
ora.llvmp.db                        ora.database.type              OFFLINE    OFFLINE
ora.llvmp.llvmp_batch.svc           ora.service.type               OFFLINE    OFFLINE

As you can see, because I removed the DGLLVMPFS Logical Volume, it disappeared. Only the DGLLVMPAK is still present. I remove it running following command

[clone01.+ASM1]/sbRdbms/oracle-$/sbOraGrid/12.1.0.2/grid/bin/crsctl delete resource ora.DGLLVMPFS.LVLLVMPFS.advm -f -unsupported

4) Enable the Logical Volume

Really this step should be the last, but Oracle permitted me so I enabled the Logical Volume before to register the ASCF. This is because an LV is just a container and it could be empty

This is the picture until now. The ADVM and ACFS resources are missing:

[mdapora2d01-TP.+ASM1]/sbRdbms/oracle-$cstat |grep -i llvm
ora.DGLLVMPDB01.dg                  ora.diskgroup.type             ONLINE     ONLINE on clone01
ora.DGLLVMPDB01.dg                  ora.diskgroup.type             ONLINE     ONLINE on clone02
ora.DGLLVMPDB02.dg                  ora.diskgroup.type             ONLINE     ONLINE on clone01
ora.DGLLVMPDB02.dg                  ora.diskgroup.type             ONLINE     ONLINE on clone02
ora.DGLLVMPFS.dg                    ora.diskgroup.type             ONLINE     ONLINE on clone01
ora.DGLLVMPFS.dg                    ora.diskgroup.type             ONLINE     ONLINE on clone02
ora.LISTENER_LLVMP.lsnr             ora.listener.type              OFFLINE    OFFLINE
ora.LISTENER_LLVMP.lsnr             ora.listener.type              ONLINE     ONLINE on clone02
ora.llvmp.db                        ora.database.type              OFFLINE    OFFLINE
ora.llvmp.db                        ora.database.type              OFFLINE    OFFLINE
ora.llvmp.llvmp_batch.svc           ora.service.type               OFFLINE    OFFLINE

[clone01.+ASM1]/sbRdbms/oracle-$asmcmd volenable -G DGLLVMPFS –a
[clone01.+ASM1]/sbRdbms/oracle-$asmcmd volenable -G DGLLVMPAK –a

[mdapora2d01-TP.+ASM1]/sbRdbms/oracle-$cstat |grep -i llvm
ora.DGLLVMPDB01.dg                  ora.diskgroup.type             ONLINE     ONLINE on clone01
ora.DGLLVMPDB01.dg                  ora.diskgroup.type             ONLINE     ONLINE on clone02
ora.DGLLVMPDB02.dg                  ora.diskgroup.type             ONLINE     ONLINE on clone01
ora.DGLLVMPDB02.dg                  ora.diskgroup.type             ONLINE     ONLINE on clone02
ora.DGLLVMPFS.LVLLVMPAK.advm        ora.volume.type                ONLINE     ONLINE on clone01
ora.DGLLVMPFS.LVLLVMPAK.advm        ora.volume.type                OFFLINE    OFFLINE
ora.DGLLVMPFS.LVLLVMPFS.advm        ora.volume.type                ONLINE     ONLINE on clone01
ora.DGLLVMPFS.LVLLVMPFS.advm        ora.volume.type                OFFLINE    OFFLINE
ora.DGLLVMPFS.dg                    ora.diskgroup.type             ONLINE     ONLINE on clone01
ora.DGLLVMPFS.dg                    ora.diskgroup.type             ONLINE     ONLINE on clone02
ora.LISTENER_LLVMP.lsnr             ora.listener.type              OFFLINE    OFFLINE
ora.LISTENER_LLVMP.lsnr             ora.listener.type              ONLINE     ONLINE on clone02
ora.llvmp.db                        ora.database.type              OFFLINE    OFFLINE
ora.llvmp.db                        ora.database.type              OFFLINE    OFFLINE
ora.llvmp.llvmp_batch.svc           ora.service.type               OFFLINE    OFFLINE

Here you can see that only the ADVM on the first node is ONLINE. They will be ONLINE on the second node after the following command:

[clone02.+ASM2]/sbRdbms/oracle-$asmcmd volenable -G DGLLVMPFS –a
[clone02.+ASM2]/sbRdbms/oracle-$asmcmd volenable -G DGLLVMPFS –a


5) Register the file system

There is just this last step. I run the acfsutil registry again

[root@clone01 ~]# /sbin/acfsutil registry -a /dev/asm/lvllvmpak-34 /dbLLVMp/akLLVMp01
acfsutil registry: mount point /dbLLVMp/akLLVMp01 successfully added to Oracle Registry

[root@clone01 ~]# /sbin/acfsutil registry -a /dev/asm/lvllvmpfs-34 /dbLLVMp/fsLLVMp01
acfsutil registry: mount point /dbLLVMp/fsLLVMp01 successfully added to Oracle Registry

Remember that you need the root privileges to run acfsutil. Otherwise, you have following error (yes I run it with oracle user, sigh)

[clone02.+ASM2]/sbRdbms/oracle-$/sbin/acfsutil registry -a /dev/asm/lvllvmpfs-34 /dbLLVMp/fsLLVMp01
PRCN-2018 : Current user oracle is not a privileged user
acfsutil registry: ACFS-03111: unable to add ACFS mount /dbLLVMp/fsLLVMp01 within Oracle Registry

At this point all resources are ONLINE

[mdapora2d02-TP.+ASM2]/sbRdbms/oracle-$cstat |grep -i llvm
ora.DGLLVMPDB01.dg                  ora.diskgroup.type             ONLINE     ONLINE on clone01
ora.DGLLVMPDB01.dg                  ora.diskgroup.type             ONLINE     ONLINE on clone02
ora.DGLLVMPDB02.dg                  ora.diskgroup.type             ONLINE     ONLINE on clone01
ora.DGLLVMPDB02.dg                  ora.diskgroup.type             ONLINE     ONLINE on clone02
ora.DGLLVMPFS.LVLLVMPAK.advm        ora.volume.type                ONLINE     ONLINE on clone01
ora.DGLLVMPFS.LVLLVMPAK.advm        ora.volume.type                ONLINE     ONLINE on clone02
ora.DGLLVMPFS.LVLLVMPFS.advm        ora.volume.type                ONLINE     ONLINE on clone01
ora.DGLLVMPFS.LVLLVMPFS.advm        ora.volume.type                ONLINE     ONLINE on clone02
ora.DGLLVMPFS.dg                    ora.diskgroup.type             ONLINE     ONLINE on clone01
ora.DGLLVMPFS.dg                    ora.diskgroup.type             ONLINE     ONLINE on clone02
ora.LISTENER_LLVMP.lsnr             ora.listener.type              ONLINE     ONLINE on clone01
ora.LISTENER_LLVMP.lsnr             ora.listener.type              ONLINE     ONLINE on clone02
ora.dgllvmpfs.lvllvmpak.acfs        ora.acfs.type                  ONLINE     ONLINE on clone01
ora.dgllvmpfs.lvllvmpak.acfs        ora.acfs.type                  ONLINE     ONLINE on clone02
ora.dgllvmpfs.lvllvmpfs.acfs        ora.acfs.type                  ONLINE     ONLINE on clone01
ora.dgllvmpfs.lvllvmpfs.acfs        ora.acfs.type                  ONLINE     ONLINE on clone02
ora.llvmp.db                        ora.database.type              ONLINE     ONLINE on clone01
ora.llvmp.db                        ora.database.type              ONLINE     ONLINE on clone02
ora.llvmp.llvmp_batch.svc           ora.service.type               ONLINE     ONLINE on clone01

Acknowledgments

Special thanks to Roberto (my colleague) who supported me during this activity

References

[1] Automatic Storage Management Administrator's Guide
[2] ODA: How to Remove an ADVM CRS Resource When It has not Any Associated ADVM Volume in /dev/asm/* (Doc ID 2174600.1)
[3] Ch.11 Introducing Oracle ACFS and Oracle ADVM
[4] acfsutil registry command
[5] asmcmd volenable/disable
[6] crsctl command