Month: August 2007

XSLT Programming

Published on by AdvaitLeave a comment

XSLT = XSL Transformations

XSLT is the most important part of XSL. XSL stands for EXtensible Stylesheet Language.

XSLT is used to transform an XML document into another XML document, or another type of document that is recognized by a browser, like HTML and XHTML. Normally XSLT does this by transforming each XML element into an (X)HTML element.

With XSLT you can add/remove elements and attributes to or from the output file. You can also rearrange and sort elements, perform tests and make decisions about which elements to hide and display, and a lot more.

A common way to describe the transformation process is to say that XSLT transforms an XML source-tree into an XML result-tree.

XSLT uses XPATH for navigating and searching inside an XML file. XPath is a language for finding information in an XML document. XPath is used to navigate through elements and attributes in an XML document.

For starting with XSLT programming, you need to know XPATH fundamentals. I will go through XPATH fundamentals briefly and then I will explain XSLT programming.

XPATH Fundamentals:

Sample XML file:


<LOB NAME="FUSION" Id="1">
<ISSUE_TYPE NAME="Techstack Issues" Id="1">
<QUESTION TYPE="TEXT" MANDATORY="FALSE" READONLY="FALSE" DEPENDENT_ON="NULL" PARENT="NULL" VALUE="e.g. Oracle RDBMS 10.2.0.2.0" DEPENDENT_VALUE="NULL" DISPLAY="Version/Drop of Techkstack" ID="3" NAME="it_ver" SIZE="50" ISCOMMON="TRUE" />
<QUESTION TYPE="LOV" MANDATORY="TRUE" READONLY="FALSE" DEPENDENT_ON="NULL" PARENT="TRUE" VALUE="NULL" ISCOMMON="TRUE" DEPENDENT_VALUE="NULL" DISPLAY="Is this a documentation bug?" ID="4" NAME="it_isdocbug" SIZE="2">
<OPTION SELECTED="FALSE" DISPLAY="YES" ID="YES" VALUE="YES" />
<OPTION SELECTED="FALSE" DISPLAY="NO" ID="NO" VALUE="NO" />
</QUESTION>
<QUESTION TYPE="TEXT" MANDATORY="TRUE" READONLY="FALSE" DEPENDENT_ON="it_isdocbug" PARENT="NULL" VALUE="NULL" DEPENDENT_VALUE="NO" DISPLAY="Please provide a minimal standalone reproducible testcase" ID="5" NAME="it_testcase" SIZE="1000" ISCOMMON="TRUE" />
<QUESTION TYPE="LOV" MANDATORY="TRUE" READONLY="FALSE" DEPENDENT_ON="it_isdocbug" PARENT="TRUE" VALUE="NULL" ISCOMMON="TRUE" DEPENDENT_VALUE="YES" DISPLAY="Doc bug is related to?" ID="6" NAME="it_docbugto" SIZE="2">
<OPTION SELECTED="FALSE" DISPLAY="Online Help" ID="ONLINE" VALUE="ONLINE" />
<OPTION SELECTED="FALSE" DISPLAY="Developement Guide" ID="DEVGUIDE" VALUE="DEVGUIDE" />
<OPTION SELECTED="FALSE" DISPLAY="Other" ID="OTHER" VALUE="OTHER" />
</QUESTION>
<ISSUE_SUB_TYPE NAME="Oracle RDBMS" Id="1">
<BUG_PROD_ID VALUE="5" />
<BUG_DB_COMP VALUE="RDBMS" />
<BUG_DB_SUBCOMP VALUE="NULL" />
<QUESTION TYPE="TEXT" MANDATORY="TRUE" READONLY="FALSE" DEPENDENT_ON="it_isdocbug" PARENT="NULL" VALUE="NULL" ISCOMMON="FALSE" DEPENDENT_VALUE="NO" DISPLAY="Please provide trace files/Stack trace if applicable" ID="1" NAME="ist_q1" SIZE="1000" />
</ISSUE_SUB_TYPE>
<ISSUE_SUB_TYPE NAME="Oracle Middleware Extension" Id="2">
<BUG_PROD_ID VALUE="2314" />
<BUG_DB_COMP VALUE="NULL" />
<BUG_DB_SUBCOMP VALUE="NULL" />
</ISSUE_SUB_TYPE>
<ISSUE_SUB_TYPE Id="3" NAME="BPEL">
<BUG_PROD_ID VALUE="1669" />
<BUG_DB_COMP VALUE="NULL" />
<BUG_DB_SUBCOMP VALUE="NULL" />
</ISSUE_SUB_TYPE>
</ISSUE_TYPE>
</LOB>


XSLT Syntax:

Expression Description
nodename Selects all child nodes of the named node
/ Selects from the root node
// Selects nodes in the document from the current node that match the selection no matter where they are
. Selects the current node
.. Selects the parent of the current node
@ Selects attributes

Examples:

To reach to NAME attribute of ISSUE_TYPE element we can use following command

LOB/ISSUE_TYPE/@NAME. Similarly we can use the following commands as well

Path Expression Result
/LOB/ISSUE_TYPE/ISSUE_SUB_TYPE[0] Selects the first ISSUE_SUB_TYPE element that is the child of the ISSUE_TYPE element.
Note: IE5 and later has implemented that [0] should be the first node, but according to the W3C standard it should have been [1]!!
/LOB/ISSUE_TYPE/ISSUE_SUB_TYPE[last()] Selects the last ISSUE_SUB_TYPE element that is the child of the ISSUE_TYPE element
/LOB/ISSUE_TYPE/ISSUE_SUB_TYPE[last()-1] Selects the last but one ISSUE_SUB_TYPE element that is the child of the ISSUE_TYPE element
/LOB/ISSUE_TYPE/ISSUE_SUB_TYPE[position()<3] Selects the first two ISSUE_SUB_TYPE elements that are children of the ISSUE_TYPE element
//ISSUE_TYPE[@NAME] Selects all the ISSUE_TYPE elements that have an attribute named NAME
//ISSUE_SUB_TYPE[@NAME=’BPEL’] Selects all the ISSUE_SUB_TYPE elements that have an attribute named NAME with a value of ‘BPEL’
/ISSUE_TYPE/ISSUE_SUB_TYPE[@ID=3] Selects all the ISSUE_SUB_TYPE elements of the ISSUE_TYPE element that have a ID attribute with a value equal to 3

Wildcard Description
* Matches any element node
@* Matches any attribute node
node() Matches any node of any kind

For more information on XPATH syntax and examples check XPATH Tutorial

XSLT programing:

The above XML can be transformed into HTML as given below. Please note the columns and the values and compare it will XML elements. We can vary the columns based on our requirements.

14.jpg

The code for the same is as given below.

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


<font color="#999999"><?xml version="1.0" encoding="iso-8859-1"?>
<!-- DWXMLSource="test.xml" -->
&lt;!DOCTYPE xsl:stylesheet  [
<!ENTITY nbsp   " ">
<!ENTITY copy   "©">
<!ENTITY reg    "®">
<!ENTITY trade  """>
<!ENTITY mdash  "">
<!ENTITY ldquo  "">
<!ENTITY rdquo  "">
<!ENTITY pound  "£">
<!ENTITY yen    "¥">
<!ENTITY euro   "¬">
]>
<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
<xsl:output method="html" encoding="iso-8859-1" doctype-public="-//W3C//DTD XHTML 1.0 Transitional//EN" doctype-system="http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"/>
<xsl:template match="LOB">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1"/>
<title>Untitled Document</title>
</head>
<body>
<table border="1"  cellpadding="2" cellspacing="0" width="100%">
<tr>
<td align="center"><b>Issue Type</b></td>
<td align="center"><b>Sub Issue Type</b></td>
<td align="center"><b>Question</b></td>
<td align="center"><b>Mandatory</b></td>
<td align="center"><b>Field</b></td>
<td align="center"><b>Question ID</b></td>
<td align="center"><b>Question Name</b></td>
<td align="center"><b>Dependent On</b></td>
<td align="center"><b>Dependent Value</b></td>
</tr>
<xsl:for-each select="ISSUE_TYPE/QUESTION">
<tr>
<td><xsl:value-of select="../@NAME"/></td>
<td></td>
<td><xsl:value-of select="@DISPLAY"/></td>
<xsl:if test="@MANDATORY='TRUE'">
<td>Yes</td>
</xsl:if>
<xsl:if test="@MANDATORY='FALSE'">
<td>No</td>
</xsl:if>
<td><xsl:if test="@READONLY='TRUE' and @TYPE != 'LOV'">
<input type="{@TYPE}" readonly="{@READONLY}" id="{@ID}" name="{@NAME}" size="28" value="{@VALUE}"/>
</xsl:if>
<xsl:if test="@READONLY='FALSE' and @TYPE != 'LOV'">
<input type="{@TYPE}" id="{@ID}" name="{@NAME}" size="28" value="{@VALUE}"/>
</xsl:if>
<xsl:if test="@TYPE='LOV'">
<select id="@ID" name="@NAME">
<xsl:for-each select="OPTION">
<option value="@VALUE" id="@ID"><xsl:value-of select="@DISPLAY"/></option>
</xsl:for-each>
</select>
</xsl:if>
</td>
<td><xsl:value-of select="@ID"/> </td>
<td><xsl:value-of select="@NAME"/> </td>
<xsl:if test="@DEPENDENT_ON != 'NULL'">
<xsl:variable name="dependent_on" select="@DEPENDENT_ON"/>
<xsl:for-each select="/LOB/ISSUE_TYPE/QUESTION">
<xsl:if test="@NAME=$dependent_on">
<td><xsl:value-of select="@DISPLAY"/></td>
</xsl:if>
</xsl:for-each>
<td><xsl:value-of select="@DEPENDENT_VALUE"/></td>
</xsl:if>
<xsl:if test="@DEPENDENT_ON = 'NULL'">
<td><xsl:value-of select="@DEPENDENT_ON"/></td>
<td><xsl:value-of select="@DEPENDENT_VALUE"/></td>
</xsl:if>
</tr>
</xsl:for-each>
<xsl:for-each select="ISSUE_TYPE/ISSUE_SUB_TYPE/QUESTION">
<tr>
<td><xsl:value-of select="../../@NAME"/></td>
<td><xsl:value-of select="../@NAME"/></td>
<td><xsl:value-of select="@DISPLAY"/></td>
<xsl:if test="@MANDATORY='TRUE'">
<td>Yes</td>
</xsl:if>
<xsl:if test="@MANDATORY='FALSE'">
<td>No</td>
</xsl:if>
<td><xsl:if test="@READONLY='TRUE' and @TYPE != 'LOV'">
<input type="{@TYPE}" readonly="{@READONLY}" id="{@ID}" name="{@NAME}" size="28" value="{@VALUE}"/>
</xsl:if>
<xsl:if test="@READONLY='FALSE' and @TYPE != 'LOV'">
<input type="{@TYPE}" id="{@ID}" name="{@NAME}" size="28" value="{@VALUE}"/>
</xsl:if>
<xsl:if test="@TYPE='LOV'">
<select id="@ID" name="@NAME">
<xsl:for-each select="OPTION">
<option value="@VALUE" id="@ID"><xsl:value-of select="@DISPLAY"/></option>
</xsl:for-each>
</select>
</xsl:if>
</td>
<td><xsl:value-of select="@ID"/> </td>
<td><xsl:value-of select="@NAME"/> </td>
<xsl:if test="@DEPENDENT_ON != 'NULL'">
<xsl:variable name="dependent_on" select="@DEPENDENT_ON"/>
<xsl:for-each select="/LOB/ISSUE_TYPE/QUESTION">
<xsl:if test="@NAME=$dependent_on">
<td><xsl:value-of select="@DISPLAY"/></td>
</xsl:if>
</xsl:for-each>
<td><xsl:value-of select="@DEPENDENT_VALUE"/></td>
</xsl:if>
<xsl:if test="@DEPENDENT_ON = 'NULL'">
<td><xsl:value-of select="@DEPENDENT_ON"/></td>
<td><xsl:value-of select="@DEPENDENT_VALUE"/></td>
</xsl:if>
</tr>
</xsl:for-each>
</table>
</body>
</html>
</xsl:template>
</xsl:stylesheet></font>

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

References: http://www.w3schools.com/xsl/xsl_transformation.asp

Advertisement

ORA-04091: Table is Mutating

Published on by Advait15 Comments

Many a times you must have encountered the issue ORA-04091: table SYSTEM.TEST1 is mutating, trigger/function may not see it.

This issue basically persists in PLSQL triggers. At one time I was also hit by the same issue. One of my friend “Nikhil Tungare – PLSQL Guru” helped me to get rid of this issue.

Here is how the error can be reproducible.

SQL> create table test1
2  (col1 varchar2(10),
3  col2 number);

Table created.

SQL> create table test1_audit
2  (col1 number,
3  time date);    

Table created.

SQL> create or replace trigger mutat_trig
2  after insert on test1
3  for each row
4  declare
5  id number;
6  begin
7  select col2 into id from test1
8  where col2 = :NEW.col2;
9  insert into test1_audit values (id, sysdate);
10  end;
11  /

Trigger created.

SQL> insert into test1 values(‘test’,1);
insert into test1 values(‘test’,1)
*
ERROR at line 1:
ORA-04091: table SYSTEM.TEST1 is mutating, trigger/function may not see it
ORA-06512: at “SYSTEM.MUTAT_TRIG”, line 4
ORA-04088: error during execution of trigger ‘SYSTEM.MUTAT_TRIG’

The reason for this error is because, you have a table and you are inserting a row. Now as soon as you insert a row, a trigger is fired which will select the inserted data. This causes a problem because the data is inconsistent, it is not yet commited. Oracle engine allows only commited data to be queried.

This problem comes with row level trigger only, because row level trigger will gets fired immidiately as soon as you insert a row in a table. Statement level trigger will get fired after every statement.

To get rid of this problem and still use row level trigger, we have the solution as given below.

We need to create following triggers to avoide this issue.

1. After RowLevel

2. After Statement Level

Also we need to declare a global variable to store the value of ID that we will select. This global variable should be declared in package, so that we can access it when ever required.

Create a package, which will hold global variable

SQL> CREATE OR REPLACE PACKAGE test_package AS
2  id test1.col2%TYPE;
3  END;
4  /

Package created.

1) Creating After Row level trigger to populate the global variable with required value

SQL> create or replace trigger mutat_trig
2  after insert on test1
3  for each row
4  begin
5    test_package.id := :new.col2;
6  end;
7  /

Trigger created.

2) Create a After Statement level trigger to insert into test1_audit table.

SQL> create or replace trigger insert_audit
2  after insert on test1
3  begin
4  insert into test1_audit values (test_package.id, sysdate);
5  end;
6  /

Trigger created.

Trying to insert value now.

SQL> insert into test1 values(‘test’,1);

1 row created.

SQL> commit;

Commit complete.

SQL> select * from test1_audit;

      COL1 TIME
———- ———
1 30-AUG-07

So always avoid selecting the value from the table, which is not yet committed. Thanks for Nikhil for helping me figure out this very closely.

Oracle Database 11g New Features – ASM

Published on by Advait1 Comment

Oracle Database 11g extends the functionality of Automatic Storage Management (ASM)in following areas

ASM Fast Mirror resynchronization:

When we take a disk offline in case the disk is corrupted or database is not able to read or write from the disk. In case of Oracle database 10g, oracle engine use to balance the other disks with the content of offline disk. This process was a relatively costly operation, and could take hours to complete, even if the disk failure was only a transient failure.
Oracle Database 11g introduces the ASM Fast Mirror Resync feature that significantly reduces the time required to resynchronize a transient failure of a disk. When a disk goes off line oracle engine doesn’t balance other disk, instead ASM tracks the allocation units that are modified during the outage. The content present in the failed disk is tracked by other disk and any modification that is made to the content of failed disk is actually made in other available disks. Once we get the disk back and attach it, the data belonging to this disk and which got modified during that time will get resynchronized back again. This avoids the heavy re-balancing activity. Following thigs should be noted for this feature

1) This feature requires that the redundacy level for the disk should be set to NORMAL or HIGH

2) The disk has to be take offline and should not be dropped.

3) You need to set DISK_REPAIR_TIME parameter, which gives the time it takes for the disk to get repaired. The setting for this attribute determines the duration of a disk outage that ASM tolerates while still being able to resynchronize after you complete the repair. The default time for this is set to 3.6 hours

To take the disk offline use
ALTER DISKGROUP … OFFLINE DISKS command

Example: ALTER DISKGROUP dgroupA OFFLINE DISKS IN FAILGROUP controller2 DROP AFTER 5H;

Repair time for the disk is associated with disk group. You can override the repair time of disk group using following command

ALTER DISKGROUP dgroupA SET ATTRIBUTE ‘DISK_REPAIR_TIME’=’3H’;

If you cannot repair a failure group that is in the offline state, you can use the ALTER DISKGROUP DROP DISKS IN FAILGROUP command with the FORCE option. This ensures that data originally stored on these disks is reconstructed from redundant copies of the data and stored on other disks in the same disk group.

ASM Preferred Mirror Read:

When you configure ASM failure groups, ASM in Oracle Database 10g always reads the primary copy of a mirrored allocation unit. It may be more efficient for a node to read from a failure group allocation unit that is closest to the node, even if it is a secondary allocation unit.

This is especially true in RAC-extended cluster configurations, where reading from a local copy of an allocation unit provides improved performance.

With Oracle Database 11g, you can do this by configuring preferred mirror read using the new ASM_PREFERRED_READ_FAILURE_GROUPS initialization parameter to specify a list of preferred mirror read names.

This is a multivalued parameter and should contain a comma-delimited string of failure group names. The failure group names specified should be prefixed with its disk group name and a “.” character.
A new column, PREFERRED_READ, has been added to the V$ASM_DISK view. If the disk group that the disk is in pertains to a preferred read failure group, the value of this column is Y.

ASM Scalability and Performance Enhancements:

In 11g ASM supports variable size allocation units for file. Sizes for allocation units are 1, 4, 16, 64MB. For example when we first create a file in ASM, it will begin with an allocation unit of 1MB and as the size of the file increases and crosses the pre-determined file size threshold, ASM uses next allocation units size for assigning allocations. Advantage with this type of architecture is that there will be less number of allocation pointers needed to describe the file, less memory is requiired to map the memory unit allocation in shared memory.

Using variable size allocation units enables you to deploy Oracle databases using ASM that are several hundred terabytes (TB), even several petabytes (PB) in size. The management of variable size allocation units is completely automated and does not require manual administration.
Also if there are some non contiguous small allocation units are left inbetween the large blocks then those are defragmented during rebalancing operation. Also ASM will also automatically defragment during allocation, if the desired size is unavailable, thereby potentially affecting allocation times, but offering much faster file opens, given the reduction in the memory required to store file allocation units.

SYSASM:

Oracle Database 11g introduces a new privilage SYSASM to manage ASM specific task. SYSASM is in DBA installation group. Current release 1 of Oracle 11g will allow the user to use SYSDBA provilage to manage ASM task, but going forward in next release SYSDBA privilage will be restricted to ASM instance. However, SYSDBA will be available for all other database administration.

You can use the combination of CREATE USER and GRANT SYSMAN SQL statements from an ASM instance to create a new SYSASM user. This is possible as long as the name of the user is an existing OS username.

A new column SYSASM is been added in the V$PWFILE_USERS.

ASM Disk Group Compatibility:

Basically there are 2 level of compatibility setting for database. Lets clarify the same here.

RDBMS compatibility:

This is the minimum compatible version of the RDBMS instance that would allow the instance to mount the disk group. This compatibility dictates the format of messages that are exchanged between the ASM and database (RDBMS) instances. An ASM instance can support different RDBMS clients running at different compatibility settings.
Database instances are typically run from a different Oracle home than the ASM instance. This implies that the database instance may be running a different software version than the ASM instance. When a database instance first connects to an ASM instance, it negotiates the highest version that they both can support.

ASM compatibility:

It is the persistent compatibility setting controlling the format of data structures of ASM metadata on disk. The ASM compatibility level of a disk group must always be greater than or equal to the RDBMS compatibility level of the same disk group. ASM compatibility is concerned only with the format of the ASM metadata.
For example, the ASM compatibility of a disk group can be set to 11.0, whereas its RDBMS compatibility could be 10.1. This implies that the disk group can be managed only by the ASM software whose software version is 11.0 or later, whereas any database client whose software version is later than or equal to 10.1 can use that disk group.

The compatibility of a disk group is an irreversible operation. Once you set the compatibility, you can not bring it down. You can further advanced it.

Installing Oracle Data Mining Manually

Published on by Advait1 Comment

Oracle Data Mining is one of the component of Oracle Database. When we create a database using DBCA it creates this component automatically. Even when we go for basic installation there is an option to select or de-select Oracle data mining. While doing Advanced installation, you need to go to “Custom” option on “Select Installation Type” screen and select Oracle Data Mining check box.

For more information please check Installation link.

Below are the simple steps for installing Oracle Data Mining manually using the scripts. This post is based on metalink Note ID: 420791.1

1) Install Oracle Data Mining

This is done by running a simple script dminst.sql present in ORACLE_HOME/rdbms/admin in case of
10.2.X database and in ORACLE_HOME/dm/admin in case of 10.1.X database.

This script needs 2 inputs. First is the name of SYSAUX tablespace name and second is TEMP tablespace name.

SQL> @$ORACLE_HOME/rdbms/admin/dminst.sql SYSAUX TEMP

2) odmpatch.sql should be run to make ODM at 10.2.0.X patch release level.

SQL> @$ORACLE_HOME/rdbms/admin/odmpatch.sql

3) Compile the invalids

SQL> @$ORACLE_HOME/rdbms/admin/utlrp.sql

Check the component in DBA_REGISTRY

SQL> select COMP_NAME,VERSION,STATUS from dba_registry where COMP_NAME=’Oracle Data Mining’;

COMP_NAME
——————————————————————————–
VERSION                        STATUS
—————————— ———————————
Oracle Data Mining
10.2.0.3.0                     VALID

Using Javascript DOM in Web Applications

Published on by Advait1 Comment

Many times while creating the web applications its not always a good idea to store even a
small information in database tables. For Example if you have drop downs in your application and you want to populate the same from the lookup table in database. You web application, possibly written in JSP have to make a database connection, get the value and populate the drop down.

Also there may be the situations, where you have 2 drop downs and depending on the value of first one, you have to populate the second drop down. In this kind of requirement, situation is worst. Here you have to make 2 connections one after the another and in second connection, the value of first drop down needs to be passed. Benefit for using this kind of architecture is simple design but at the expence of performance. To make you application efficient and to achieve good performance it will be good it we go for DOM in javascript. Below, I have given a very simple implementation of our drop down example.

In my case here is the requirement. I have 3 drop downs.

  1. Status
  2. Closing Code
  3. sub-closing Code

Situation is, when first drop down “status” is changed, it should populate the value for 2nd drop down “closing code”. When a value from 2nd drop down is selected, 3rd should get populated.

This is achieved using DOM in javascript. DOM stands for “Document Object Modeling”. Instead of using database table for storing the value, we user XML file for storing the values and XML is stored at server side. We parse the XML and populate the drop down from its values. Below screen shot shows the drop down required.

10.jpg

Here when I change the status drop down it should change the Closing Code values and on changing the closing code value it should change sub-Closing Code values. For this I created a XML file as given below.
================================================================


<?xml version="1.0" encoding="iso-8859-1"?>
<closing_codes>
<status value="11" active="y">
<issue_type value="OPEN ISSUE" active="y">
<sub_issue_type value="OPEN ISSUE" active="y"/>
</issue_type>
</status>
<status value="30" active="y">
<issue_type value="INFORMATION REQUESTED" active="y">
<sub_issue_type value="INFORMATION REQUESTED" active="y"/>
</issue_type>
</status>
<status value="40" active="y">
<issue_type value="BASE BUG NOT A BUG" active="y">
<sub_issue_type value="BASE BUG NOT A BUG" active="y"/>
</issue_type>
<issue_type value="EMS ISSUE" active="y">
<sub_issue_type value="EMS SETUP ISSUE" active="y"/>
<sub_issue_type value="EMS PRODUCT ISSUE" active="y"/>
</issue_type>
<issue_type value="HARDWARE ISSUE" active="y">
<sub_issue_type value="DISPLAY SERVER ISSUE" active="y"/>
<sub_issue_type value="INSUFFICENT DISK SPACE" active="y"/>
<sub_issue_type value="INSUFFICIENT SWAP" active="y"/>
<sub_issue_type value="INSUFFICIENT IO" active="y"/>
<sub_issue_type value="INSUFFICENT MEMORY" active="y"/>
<sub_issue_type value="MEDIA FAILURE" active="y"/>
<sub_issue_type value="HOST NOT REACHABLE" active="y"/>
</issue_type>

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

Having this XML placed at correct location, next step is to load XML and get the contents
from it. Below javascript code shows loading of XMl file.

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


function loadXML(xmlFile){
if (document.implementation &amp;&amp; document.implementation.createDocument)
{
xmlDoc = document.implementation.createDocument("", "", null);
xmlDoc.onload = verify;
}
else if (window.ActiveXObject)
{
xmlDoc = new ActiveXObject("Microsoft.XMLDOM");
xmlDoc.onreadystatechange = function () {
if (xmlDoc.readyState == 4) verify()
};
}
xmlDoc.load(xmlFile);
}

================================================================
The function is supplied with an arguement of XML file location and name in the form of URL.
In you main JSP code you can call the function as given below

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


<SCRIPT language="javascript">
xmlfile = "http://mfgops.us.oracle.com:8890/ops/bug_closing/closing_codes.xml";
function populate_closing_code()
{
loadXML(xmlfile);
}
</SCRIPT>

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

The function is mainly meant to work in IE as well as mozilla and netscape. For more explanation on the function please refer to the below URL

http://www.quirksmode.org/dom/importxml.html

Once the XML is loaded and available for accesssing, next step is to use the values in XML and populate the drop down.

Here logic we are using is to populate the “Closing Code” drop down with the “value” attribute of <issue_type> element and based on the value selected for closing code drop down, sub closing code drop down will be populated with “value” attribute of <sub_issue_type> sub elemment present in side the selected <issue_type> element.

To clarify the above point, let me explain by example.

Suppose the users selects the status drop down for status with value as 40 then, closing code drop down should get populated with values available in <issue_type> element inside that <status> element(having value 40) and sub-Closing Code drop down will et populated with values from <sub_issue_type> inside each <issue_type> element when the closing Code drop down is changed.

Below is function for populating closing code drop down.


function verify()
{
var i, n_elems, elems = xmlDoc.getElementsByTagName("issue_type");
var j = 0;
n_elems = elems.length;
document.forms['data'].closing_code.options.length = 0;
for (i = 0; i &lt; n_elems; i++)
{
if(document.forms['data'].status.selectedIndex == 0)
{
if(elems[i].parentNode.getAttribute("value") == 11)
{

document.forms['data'].closing_code.options[j]= new Option(elems[i].getAttribute("value"),elems[i].getAttribute("value"));
j++;
}
}
if(document.forms['data'].status.selectedIndex == 1)
{
if(elems[i].parentNode.getAttribute("value") == 30)
{

document.forms['data'].closing_code.options[j]= new Option(elems[i].getAttribute("value"),elems[i].getAttribute("value"));
j++;
}
}
if(document.forms['data'].status.selectedIndex == 2)
{
if(elems[i].parentNode.getAttribute("value") == 40)
{

document.forms['data'].closing_code.options[j]= new Option(elems[i].getAttribute("value"),elems[i].getAttribute("value"));
j++;
}
}
if(document.forms['data'].status.selectedIndex == 3)
{
if(elems[i].parentNode.getAttribute("value") == 66)
{

document.forms['data'].closing_code.options[j]= new Option(elems[i].getAttribute("value"),elems[i].getAttribute("value"));
j++;
}
}
if(document.forms['data'].status.selectedIndex == 4)
{
if(elems[i].parentNode.getAttribute("value") == 80)
{

document.forms['data'].closing_code.options[j]= new Option(elems[i].getAttribute("value"),elems[i].getAttribute("value"));
j++;
}
}
}

populate_sub_closing_code(document.forms['data'].closing_code.options[document.forms['data'].closing_code.selectedIndex].value);
}

Note here that the name of the function is verify, which should be same as we have in loadMXL function.

“xmlDoc.onload = verify;” | “if (xmlDoc.readyState == 4) verify()”

Here forms[‘data’] is the name of the form I am using in my JSP.

Status is the name of status drop down.

closing_code is the name of closing code drop down.

populate_sub_closing_code is the function we call from verify function to populate the sub-Closing code drop down. Why this is required? I will let you know this later.

Important point here is we are populating the closing code by calling verify function. But who is calling verify fucntion? Its loadXML. Who is calling loadXML? its populate_closing_code function we defined in out JSP. How populate_closing_code function is called? when we change the status. So for the status drop down we can have following fucntion.

onchange=”populate_closing_code(this.value);”

Based on this.value and if loop we can get the values from XML and populate closing code drop down.

Here the status drop down contains 5 values so the indexes will be mapped as given below.

value Index
11 0
30 1
40 2
66 3
80 4

So when index 2 is selected the value to be searched in XML is 40.

We can refer to the XML file elements using following

elems = xmlDoc.getElementsByTagName(“issue_type”);

Here we are getting all the elements of the name “issue_type”

To refer to the parent element of issue type, which is status we have to use

elems[i].parentNode.

To get the attribute of an element we have a method called


getAttribute("Attribute Name").

if(document.forms['data'].status.selectedIndex == 2)        // Checking the index seleced for status drop down
{
if(elems[i].parentNode.getAttribute("value") == 40)   // check for value of 40 in XML file for <status> element
{

document.forms['data'].closing_code.options[j]= new Option(elems[i].getAttribute("value"),elems[i].getAttribute("value"));  // populating closing_code drop down by appending value
j++;
}
}

This loop will run for the whole XML and populate closing_code drop down with “value” attribute of <issue_type> element which comes under the status that we select in status drop down.

Coming to populating the Sub-Closing Code drop down. This drop down is to be populated when there is change in value of closing code drop down. So we have to define a event on Closing Code drop down as given below.

onchange=”populate_sub_closing_code(this.value)”

So when we change the value in closing Code drop down this event will be triggered and it will call the function populate_sub_closing_code(). Below is the code for that.

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


function populate_sub_closing_code(current_value)
{
var i, n_elems, elems = xmlDoc.getElementsByTagName("sub_issue_type");
var j = 0;
n_elems = elems.length;
document.forms['data'].sub_closing_code.options.length = 0;
while(''+current_value.charAt(0)==' ')
current_value=current_value.substring(1,current_value.length);
while(''+current_value.charAt(current_value.length-1)==' ')
current_value=current_value.substring(0,current_value.length-1);
for (i = 0; i &lt; n_elems; i++)
{
while(''+elems[i].parentNode.getAttribute("value").charAt(0)==' ')
elems[i].parentNode.getAttribute("value")=elems[i].parentNode.getAttribute("value").substring(1,elems[i].parentNode.getAttribute("value").length);
while(''+elems[i].parentNode.getAttribute("value").charAt(elems[i].parentNode.getAttribute("value").length - 1)==' ')
elems[i].parentNode.getAttribute("value")=elems[i].parentNode.getAttribute("value").substring(0,elems[i].parentNode.getAttribute("value").length-1);
if(elems[i].parentNode.getAttribute("value") == current_value)
{

document.forms['data'].sub_closing_code.options[j]= new Option(elems[i].getAttribute("value"),elems[i].getAttribute("value"));
j++;
}
}
}

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

Here we are again refering to the XML as

elems = xmlDoc.getElementsByTagName(“sub_issue_type”);

The two while loop are for removing the spaces from the selected values we get from the
closing code drop down. Based on these values we will search the XML for <issue_type> element and get all the <sub_issue_type> elements listed inside that.

For example if the status selected is 40, then closing code will have values

BASE BUG NOT A BUG

EMS ISSUE

HARDWARE ISSUE

If a user select HARDWARE ISSUE from closing code drop down, sub closing code drop down should contain following values

DISPLAY SERVER ISSUE

INSUFFICENT DISK SPACE

INSUFFICIENT SWAP

INSUFFICIENT IO

INSUFFICENT MEMORY

MEDIA FAILURE

HOST NOT REACHABLE

To reach to these values we will search for HARDWARE ISSUE. current_value will give the selected value of drop down we are providing as an arguement.

elems[i].parentNode.getAttribute(“value”) == current_value is the actual comparison.

Note here that we are getting all the elements with <sub_issue_type> and not <issue_type> as in case of closing code. So when we say elems[i].parentNode.getAttribute(“value”), it will get the value attribute of all <issue_type> elements of every <sub_issue_type> element.

So this will populate Sub closing code.

One last thing to mention. As I said populate_sub_closing_code function is also called from verify function. This is because suppose we change the status from lets say 30 to 40. In that case Closing code drop down will get populated with required values. Here we can see that there is change in values, but populate_sub_closing_code function will not get triggered automatically. For this user needs to changes the values of closing code drop down explicitly. What we want is as soon as status is changed, closing drop down should get the required value and first value will get automatically selected. Based on this we
want the Sub Closing Code should get populated with required value (corresponding to first value of closing code drop down).

For this we are calling populate_sub_closing_code function from verify. So changing status drop down will trigger changing value for Closing Code and also Sub Closing Code.

Summarizing:

1) Create XML with the required format

2) In main JSP


<SCRIPT language="javascript">
xmlfile = "http://mfgops.us.oracle.com:8890/ops/bug_closing/closing_codes.xml";
function populate_closing_code()
{
loadXML(xmlfile);
}
</SCRIPT>

loadXML function is present in some other Javascript file. You can include the JS script file in
your main JSP as given below.

<script language=”javascript” src=”/ops/bug_closing/jsxml.js” type=”text/javascript”></script>

3) In the extrnal JS file (jsxml.js), create write below functions

function loadXML(xmlFile)
function verify()
function populate_sub_closing_code(current_value)

4) Include an event for status drop down

onchange=”populate_closing_code(this.value);”

5) Include an event for Closing drop down

onchange=”populate_sub_closing_code(this.value);”

Done !!

Javascript – Hide/Unhide the content

Published on by AdvaitLeave a comment

Javascript Development:

Developing a smart tool and a good user interface is really crucial. As a core DBA or Apps DBA we never think of any core programming exercise. But during the journey through database, we may come at a stage where you are required or expected to develop some really cool tools using JSP and Javascript combo.

Off course the language to be used is totally upto the developer to decide, unless there are some constraints enforced by the environment. Here I am describing a simple javascript funda, I used, while I was developing a tool to be used internally by our division.

Requirement:

The tool was basically to see the list of bugs logged by users, to update them and close them. This involves getting the data from database and showing it in UI. User will be entering the information and same has to be updated in database.

Now when a bug information is supposed to be viewed, it will be chunk of information shown to the DBA, which will contain all the details neccessary for DBA to work on. In my tool I used a link which can hide or show this information when a DBA queries the bug. I thought, its not always a good idea to show big information as soon as the bug is opened in the tool.

Below screen shot you can see what exactly I meant.

21.jpg

Here if you see the second label, which is “Bug Description”, it gives a long description of the contents of the bug. Also as and when the users and DBAs are updating the bugs the content get appended to this and it grows big. So better way is to hide the same and show when user wants to see. Below is the screen, when a user click on show/hide link and content is visible.

22.jpg

Now if you see, full content is visible. This simple, but amazing functionality can be implemented using javascript. Also javascript works at client side so no overhead on the application. No additional resource or network traffic utilization. Totaly works on client browser.

How it works?

The code for fetching the details of the bug content and displaying the same is been written in JSP and javascript is added to make this particular functionality work. The code in javascript, which fetches the information and displays the same is as given below.

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


<tr>

<td width=20%><P ALIGN=Right>Bug Description</P></td>
<td width=80%><P ALIGN=left>&lt;a href="#" <font color="#008000"><strong>onClick="showHide('prevUpdates');</strong></font>">Show/Hide</a></P></td>

</TR>
<tr>
<td width=20%><P ALIGN=left></P></td>
<strong><td id="prevUpdates" style="display:none"></strong>
<font color="#0000ff"><table BGCOLOR=#f2f2f5 align=left >
<% while(results.next()) { %>
<tr>
<td id="lineno" width=80%><P ALIGN=left><font face='Tahoma' size=2 ><%= results.getString("comments")%></font></P></td>
</tr>
<%}%>
</table></font>
<strong></td></strong>
</tr>

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

The line in the bold is the line which gives the name to the region and the lines in the blue color is the one which fetches the information and shows on JSP page.

As you can see in the green color that on clicking the link “Show/Hide”, it calls for showHide javascript and passes prevUpdates, which is the ID of the region we want to show or hide. Below is the javascript, which can hide or show the content when user clicks in “Hide/show” link.

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


function showHide(elem){
var el = eval(elem +'.style');
if (el.display == "none"){
el.display = "block";
} else {
el.display = "none";
}
}

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

Here elem will take the ID prevUpdates, which we supplied while calling the script. So what the script is doing is, when style=”display:none” is set and user clicks “Show/Hide” link its making display property as block, meaning that content will be shown. And when the content is being shown (display property is block), and user clicks the link, our javascript is making display property as none, that means the content will not be shown (hidden).

Such a functionality can be easily implemented using javascript, with not performance hit. In the next post, I will be covering “How to populate drop downs using XML file”.

RMAN – Recovery Manager for Oracle Database 10g

Published on by Advait16 Comments

WHAT IS RMAN ?

Recovery Manager is a tool that: manages the process of creating backups and also manages the process of restoring and recovering from them.

WHY USE RMAN ?

  • No extra costs …Its available free
  • RMAN introduced in Oracle 8 it has become simpler with newer versions and easier than user managed backups
  • Proper security
  • You are 100% sure your database has been backed up.
  • Its contains detail of the backups taken etc in its central repository
  • Facility for testing validity of backups also commands like crosscheck to Check the status of backup.
  • Faster backups and restores compared to backups without RMAN
  • RMAN is the only backup tool which supports incremental backups.
  • Oracle 10g has got further optimized incremental backup which has resulted in improvement of performance during backup and recovery time
  • Parallel operations are supported
  • Better querying facility for knowing different details of backup
  • No extra redo generated when backup is taken..compared to online backup without RMAN which results in saving of space in hard disk
  • RMAN an intelligent tool
  • Maintains repository of backup metadata
  • Remembers backup set location
  • Knows what need to backed up
  • Knows what is required for recovery
  • Knows what backup are redundant

UNDERSTANDING THE RMAN ARCHITECTURE

  • An oracle RMAN comprises of RMAN EXECUTABLE This could be present and fired even through client side TARGET DATABASE This is the database which needs to be backed up
  • RECOVERY CATALOG Recovery catalog is optional otherwise backup details are stored in target database controlfile
  • It is a repository of information queried and updated by Recovery Manager
  • It is a schema or user stored in Oracle database
  • One schema can support many databases
  • It contains information about physical schema of target database datafile and archive log, backup sets and pieces

Recovery catalog is a must in following scenarios

  • In order to store scripts
  • For tablespace point in time recovery

Media Management Software

Media Management software is a must if you are using RMAN for storing backup in tape drive directly.

Backups in RMAN

Oracle backups in RMAN are of the following type

RMAN complete backup OR RMAN incremental backup

These backups are of RMAN proprietary nature

IMAGE COPY

Its again a kind of backup. The advantage of uing Image copy is its not in RMAN proprietary format.

Backup Format

RMAN backup is not in oracle format but in RMAN format. Oracle backup comprises of backup sets and it consists of backup pieces. Backup sets are logical entity. In oracle 9i it gets stored in a default location.

There are two type of backup sets

  1. Datafile backup sets,
  2. Archivelog backup sets

One more important point of data file backup sets is it do not include empty blocks. A backup set would contain many backup pieces. A single backup piece consists of physical files which are in RMAN proprietary format.

You can go to RMAN prompt by just typing rman. RMAN executable is present in ORACLE_HOME/bin location.

bash-2.05$ rman

Recovery Manager: Release 10.2.0.1.0 – Production on Mon Jul 23 02:16:55 2007

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

RMAN>

You can use target connect to connect to database. The database it will connect to depends on the environment variable ORACLE_HOME.

RMAN> connect target

connected to target database: ORCL (DBID=1156435946)

Alternatively you can use “rman TARGET SYS/oracle@test NOCATALOG” to connect to the RMAN of “test” instance.

Here we will be using target database control file to store all the information required for RMAN, like backupsets and backup image information etc.

Backup Database:

RMAN> shutdown immediate

using target database control file instead of recovery catalog
database closed
database dismounted
Oracle instance shut down

RMAN> startup mount

connected to target database (not started)
Oracle instance started
database mounted

Total System Global Area    1258291200 bytes

Fixed Size                     1978336 bytes
Variable Size                318771232 bytes
Database Buffers             922746880 bytes
Redo Buffers                  14794752 bytes

RMAN> backup database
2> ;

Starting backup at 23-JUL-07
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=155 devtype=DISK
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
input datafile fno=00001 name=/dy/oracle/product/oradata/orcl/system01.dbf
input datafile fno=00003 name=/dy/oracle/product/oradata/orcl/sysaux01.dbf
input datafile fno=00005 name=/dy/oracle/product/oradata/orcl/example01.dbf
input datafile fno=00002 name=/dy/oracle/product/oradata/orcl/undotbs01.dbf
input datafile fno=00004 name=/dy/oracle/product/oradata/orcl/users01.dbf
channel ORA_DISK_1: starting piece 1 at 23-JUL-07
channel ORA_DISK_1: finished piece 1 at 23-JUL-07
piece handle=/dy/oracle/product/flash_recovery_area/ORCL/backupset/2007_07_23/

o1_mf_nnndf_TAG20070723T031355_3b8zv57d_.bkp tag=TAG20070723T031355 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:01:36
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
including current control file in backupset
including current SPFILE in backupset
channel ORA_DISK_1: starting piece 1 at 23-JUL-07
channel ORA_DISK_1: finished piece 1 at 23-JUL-07
piece handle=/dy/oracle/product/flash_recovery_area/ORCL/backupset/2007_07_23/

o1_mf_ncsnf_TAG20070723T031355_3b8zy7xr_.bkp tag=TAG20070723T031355 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:06
Finished backup at 23-JUL-07
Creating RMAN Catalog

You can also create a repository for RMAN to store all this information. Repository will be just another small database which can store the catalog information. Creating a catalog is a 3 step process

1) Create database which will hold the catalog. Else you can use the existing database also. All you need is to create a seperate tablespace for holding the information about RMAN catalog.

2) Create RMAN tablespace and RMAN user

SQL> create tablespace rman_tbs datafile ‘/dy/oracle/product/db10g/dbf/rman01.dbf’ size 500M EXTENT MANAGEMENT LOCAL segment SPACE MANAGEMENT AUTO ;

Tablespace created.

SQL> create user rman identified by rman
2  default tablespace rman_tbs
3  TEMPORARY TABLESPACE TEMPTS1
  4  QUOTA UNLIMITED ON rman_tbs account unlock;

User created.

After creating user, you need to grant RECOVERY_CATALOG_OWNER role to that user.

3) Create RMAN catalog.

bash-2.05$ rman catalog rman/rman@test

Recovery Manager: Release 10.2.0.1.0 – Production on Mon Jul 23 04:37:10 2007

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

connected to recovery catalog database

RMAN> create catalog

recovery catalog created

For registering the database, you need to get connected to database as well as catalog at the same time. Here is how you can do.

RMAN> connect target

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-06004: ORACLE error from recovery catalog database: RMAN-20001: target database not found in recovery catalog

The above error is because the database we connected to is not found in the catalog database. We can register the database in catalog.

RMAN> register database;

database registered in recovery catalog
starting full resync of recovery catalog
full resync complete

Registering database will also resynch the information present in the target database control file and catalog database. Since we have taken 1 backup early, it will synchup that information with RMAN catalog. We can check the same using LIST BACKUP command at RMAN prompt.

RMAN> list backup
2> ;

List of Backup Sets
===================

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
——- —- — ———- ———– ———— —————
18      Full    573.48M    DISK        00:01:26     23-JUL-07
BP Key: 20   Status: AVAILABLE  Compressed: NO  Tag: TAG20070723T031355
Piece Name: /dy/oracle/product/flash_recovery_area/ORCL/backupset/2007_07_23/

o1_mf_nnndf_TAG20070723T031355_3b8zv57d_.bkp
List of Datafiles in backup set 18
File LV Type Ckp SCN    Ckp Time  Name
—- — —- ———- ——— —-
1       Full 562487     23-JUL-07 /dy/oracle/product/oradata/orcl/system01.dbf
2       Full 562487     23-JUL-07 /dy/oracle/product/oradata/orcl/undotbs01.dbf
3       Full 562487     23-JUL-07 /dy/oracle/product/oradata/orcl/sysaux01.dbf
4       Full 562487     23-JUL-07 /dy/oracle/product/oradata/orcl/users01.dbf
5       Full 562487     23-JUL-07 /dy/oracle/product/oradata/orcl/example01.dbf

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
——- —- — ———- ———– ———— —————
19      Full    6.80M      DISK        00:00:05     23-JUL-07
BP Key: 21   Status: AVAILABLE  Compressed: NO  Tag: TAG20070723T031355
Piece Name: /dy/oracle/product/flash_recovery_area/ORCL/backupset/2007_07_23/

o1_mf_ncsnf_TAG20070723T031355_3b8zy7xr_.bkp
Control File Included: Ckp SCN: 562487       Ckp time: 23-JUL-07
SPFILE Included: Modification time: 23-JUL-07

RMAN Command line

Backing up the Controlfile and Spfile

The control file can be automatically backed up after each RMAN backup and database structure change as a way to protect the RMAN repository (when we are not using a seperate catalog for RMAN).
RMAN> configure controlfile autobackup on;

Backing up control file

RMAN> backup current controlfile;

Creating Image copy of all datafiles in database

These are the image copies and are stored in ORACLE format and not in RMAN format. Backupsets and backuppieces are stored in internal RMAN format. Hence these image copies can be used for manual restore and recovery as well.

RMAN> backup as copy database;

Creating backupsets of all datafiles in database

If you specify BACKUP AS BACKUPSET, then RMAN stores its backups in backup sets. A backup set, consisting of one or more backup pieces, contains the physical file data being backed up.  This backupset is written in a format that only RMAN can access. Only RMAN can create and restore backup sets. Backup sets can be written to disk or tape, and they are the only type of backup which RMAN can use to write backups to tape.

RMAN> backup as backupset database;

Backup individual tablespace as backupsets

RMAN> backup tablespace system, HTMLDB;

Backup individual tablespace as image copies

RMAN> backup as copy tablespace system;

Backup individual files as image copies

RMAN> backup as copy datafile ‘/dy/oracle/product/db10g/dbf/system01.dbf’;

Backup individual files as Backupsets

RMAN> backup datafile ‘/dy/oracle/product/db10g/dbf/system01.dbf’;

Backup archivelogs

We can backup the archive logs according to the output of some search condition. Example we want to backup only those archivelogs which starts with “ARCH_616814159_”.

RMAN> backup acrchivelog like ‘%ARCH_616814159_%’;

Copy archivelogs from some time stamp.

Suppose we want to copy the archivelogs of last 2 days, then we can use the following commands.

RMAN> BACKUP ARCHIVELOG from time ‘sysdate-2’;

Backup all archivelog files

RMAN> Backup archivelog all;

Backup archivelog between some time.

RMAN> BACKUP ARCHIVELOG FROM TIME ‘SYSDATE-30’ UNTIL TIME ‘SYSDATE-7’;

Specifying copies while backing up.

RMAN> backup copies 2 datafile ‘/dy/oracle/product/db10g/dbf/cs_tbs01.dbf’;

Remember that copies option cannot be used with image copies. It can be used only with backupsets.

Giving tags to backups

RMAN> BACKUP TAG ‘weekly_full_db_bkup’ DATABASE MAXSETSIZE 100M;

Backing up backupsets
RMAN> BACKUP BACKUPSET ALL;

Backup imagecopies

RMAN> Backup as copy backupset all;

List Imagecopies

RMAN> list copy;

List Backupsets

RMAN> list backup;

Restoring and Recovering the database

Use the RESTORE and RECOVER commands for RMAN restore and recovery of physical database files.

RMAN> STARTUP FORCE MOUNT;
RESTORE DATABASE;
RECOVER DATABASE;
ALTER DATABASE OPEN;

Recovering Current Tablespaces

RMAN> SQL ‘ALTER TABLESPACE users OFFLINE’;
RESTORE TABLESPACE users;
RECOVER TABLESPACE users;
SQL ‘ALTER TABLESPACE users ONLINE;

Recovering Current Datafiles

RMAN> SQL ‘ALTER DATABASE DATAFILE 7 OFFLINE’;
RESTORE DATAFILE 7;
RECOVER DATAFILE 7;
SQL ‘ALTER DATABASE DATAFILE 7 ONLINE’;

Recovering Individual Data Blocks

RMAN can recover individual corrupted datafile blocks. When RMAN performs a complete scan of a file for a backup, any corrupted blocks are listed in V$DATABASE_BLOCK_CORRUPTION. Corruption is usually reported in alert logs, trace files or results of SQL queries. Use BLOCKRECOVER to repair all corrupted blocks:

RMAN> BLOCKRECOVER CORRUPTION LIST;

You can also recover individual blocks, as shown in this example:
RMAN> BLOCKRECOVER DATAFILE 7 BLOCK 233, 235 DATAFILE 4 BLOCK 101;

Managing RMAN repository

As you know that if you do not use a recovery catalog, then control file of the target database is used as RMAN repository and eventually after some time the control file records for RMAN information will get overwritten.
Set this initialization parameter in the parameter file of the target database to determine how long records are kept:

CONTROL_FILE_RECORD_KEEP_TIME = <number_of_days_to_keep>

You can configure a retention policy to be used by RMAN to determine which backups are considered obsolete.  This allows you to remove files from the repository that are no longer needed to meet your retention requirements.  This policy can be based on a recovery window (the maximum number of days into the past for which you can recover) or redundancy (how many copies of each backed-up file to keep).

Two Parameters are used to set retention policies.

You can specify the days days between the current time and the earliest point of recoverability, this is called RECOVERY WINDOW. RMAN does not consider any full or level 0 incremental backup as obsolete if it falls within the recovery window.
Alternatively the REDUNDANCY parameter will instruct to store the number of copies of backup in RMAN repository. If the number of backups for a specific datafile or control file exceeds the REDUNDANCY setting considers the extra backups as obsolete.

RMAN> CONFIGURE RETENTION POLICY TO REDUNDANCY 3;

This will make the REDUNDANCY setting to 3. Meaning that it will at max store 3 copies of backups and ikmages of datafile. Any more images or backups are consider obsolete.

When we run the below command

RMAN> CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 7 DAYS;

it will configure new retention policy based on the RECOVERY WINDOW of 7 days. This means that all the backups which falls outside this window will be considered obsolute. So in this case you need to have backup scheduled every week to have atleast 1 valid backup.

Remember that at any point of time, only one policy can be active. It can be either REDUNDANCY or RECOVERY WINDOW.

When you change the retention policy to another one, it will suspend the previous policy as shown below.

RMAN> CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 7 DAYS;

old RMAN configuration parameters:
CONFIGURE RETENTION POLICY TO REDUNDANCY 3;
new RMAN configuration parameters:
CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 7 DAYS;
new RMAN configuration parameters are successfully stored
starting full resync of recovery catalog
full resync complete

Cross checking the available backups

RMAN> CROSSCHECK BACKUP;

Crosscheck is needed when an archivelog file or backup is manually removed, i.e., not deleted by RMAN.  This command ensures that data about backups in the recovery catalog or control file is synchronized with corresponding data on disk or in the media management catalog.  The CROSSCHECK command operates only on files that are recorded in the recovery catalog or the control file.

The CROSSCHECK command does not delete any files that it is unable to find, but updates their repository records to EXPIRED. Then, you can run DELETE EXPIRED to remove the repository records for all expired files as well as any existing physical files whose records show the status EXPIRED.

If some backup pieces or copies were erroneously marked as EXPIRED, for example, because the media manager was misconfigured, then after ensuring that the files really do exist in the media manager, run the CROSSCHECK BACKUP command again to restore those files to AVAILABLE status.

RMAN> CROSSCHECK COPY;

This will validate the image copies.

Deleting the backups

The DELETE command removes RMAN backups and copies from DISK marks the records in control file as DELETED or removes the records from the recovery catalog (if you use a catalog).

RMAN> DELETE BACKUPSET 101, 102, 103;

RMAN> delete controlfilecopy ‘%ctl’;  # Pattern search is allowed.

RMAN> delete archivelog until sequence 20;

This will delete all the archives from the oldest one till the sequence we have specified.

RMAN> delete backup of tablespace system;

RMAN> DELETE ARCHIVELOG ALL BACKED UP 2 TIMES TO DEVICE TYPE DISK;

List commands

RMAN> list backup;

RMAN> list copy;

RMAN> list incarnation of database;

RMAN> list expired backupset;

RMAN> list expired copy;

RMAN> list backup of tablespace sysaux;

RMAN> list copy of datafile 3;

Reporting in RMAN

RMAN> report need backup;

Reports which database files need to be backed up to meet a configured or specified retention policy

RMAN> report unrecoverable;

Reports which database files require backup because they have been affected by some NOLOGGING operation such as a direct-path insert

RMAN> report need backup recovery window of 7 days;

Displays objects requiring backup to satisfy a recovery window-based retention policy.

Suppose in the above command we want to skip a perticular tablespace like perfstat then we can use the below command.

RMAN> report need backup recovery window of 2 days database skip tablespace perfstat;

RMAN> report need backup redundancy 3;

Displays objects requiring backup to satisfy a redundancy-based retention policy.

RMAN> report need backup days 7;

Displays files that require more than n days’ worth of archived redo log files for recovery.

RMAN> report need backup incremental 7;

Displays files that require application of more than n incremental backups for recovery.

RMAN> report need backup redundancy 2 datafile 3;

Gives report of files with less than 2 redundant backups.

RMAN> report need backup tablespace htmldb;

Report of files that must be backed up to satisfy current retention policy for this tablespace (htmldb)

RMAN> report need backup incremental 2;

Report of files that need more than 2 incrementals during recovery

RMAN> report need backup device type disk;

Report of files that must be backed up to satisfy current retention policy for database

RMAN> report obsolete;
Shows backups that are obsolete according to the current retention policy.

You can add the options RECOVERY WINDOW and REDUNDANCY with this command as given below.

RMAN> report obsolete recovery window of 3 days;

RMAN> report obsolete redundancy 2;

RMAN> report schema;

This command lists and displays information about the database files.

RMAN> report schema at time ‘sysdate – 14’;

This command gives report on schema 14 days ago.

RMAN> report schema at SCN 10000;

This gives report on schema at scn 10000.

RMAN> report schema at sequence 55 thread 1;

Gives report of schema at sequence 55.

RMAN configuration

RMAN> show all;

RMAN configuration parameters are:
CONFIGURE RETENTION POLICY TO REDUNDANCY 1; # default
CONFIGURE BACKUP OPTIMIZATION OFF; # default
CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default
CONFIGURE CONTROLFILE AUTOBACKUP ON;
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO ‘%F’; # default
CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO BACKUPSET; # default
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE MAXSETSIZE TO UNLIMITED; # default
CONFIGURE ENCRYPTION FOR DATABASE OFF; # default
CONFIGURE ENCRYPTION ALGORITHM ‘AES128’; # default
CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default
CONFIGURE SNAPSHOT CONTROLFILE NAME TO ‘/dy/oracle/product/db10g/dbs/snapcf_test.f’; # default

We can change each of these parameters as per our requirements.

You can return any setting to its default value by using CONFIGURE… CLEAR

RMAN> CONFIGURE BACKUP OPTIMIZATION CLEAR;

RMAN> CONFIGURE RETENTION POLICY CLEAR;

By default, RMAN sends all backups to an operating system specific directory on disk. So default setting for DEVICE TYPE is DISK. You can configure to make backups by default on tape or any other device as given below.

RMAN> CONFIGURE DEFAULT DEVICE TYPE TO sbt;

You can configure backup sets or image copies as the default for a perticular device type, using either of the following commands:

RMAN> CONFIGURE DEVICE TYPE DISK BACKUP TYPE TO COPY; # Default becomes image copies
RMAN> CONFIGURE DEVICE TYPE DISK BACKUP TYPE TO BACKUPSET; # Default becomes uncompressed

You can configure RMAN to use compressed backupsets by default on a particular device type, by using the CONFIGURE DEVICE TYPE command with the BACKUP TYPE TO COMPRESSED BACKUPSET option, as shown in the following examples.

RMAN> CONFIGURE DEVICE TYPE DISK BACKUP TYPE TO COMPRESSED BACKUPSET;

RMAN> CONFIGURE DEVICE TYPE sbt BACKUP TYPE TO COMPRESSED BACKUPSET;

To disable compression you can use below command

RMAN> CONFIGURE DEVICE TYPE DISK BACKUP TYPE TO BACKUPSET;

RMAN> CONFIGURE DEVICE TYPE sbt BACKUP TYPE TO BACKUPSET;

You can configure the parallelism for a device type.

RMAM> CONFIGURE DEVICE TYPE sbt PARALLELISM 2;

You can turn on/off controlfile autobackup using

RMAN> configure controlfile autobackup off;

RMAN> configure controlfile autobackup on;

We can configure RMAN channel to write backups and images in a specific format.

The following command configures RMAN to write disk backups to the /backup directory

CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT ‘/backup/ora_df%t_s%s_s%p’;

%t is replaced with a four byte time stamp,
%s with the backup set number, and
%p with the backup piece number.

You can also configure format for controlfile autobackup as well.
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE deviceSpecifier TO ‘string’;

For example, you can run the following command:

CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO ‘?/oradata/cf_%F’;

Here %F will be replaced by following

c-IIIIIIIIII-YYYYMMDD-QQ, where:

IIIIIIIIII stands for the DBID.
YYYYMMDD is a time stamp of the day the backup is generated
QQ is the hex sequence that starts with 00 and has a maximum of FF

You can clear the format using following command.
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK CLEAR;

References:

Metalink Note ID: 360416.1

iselfschooling

http://download-uk.oracle.com/docs/cd/B19306_01/backup.102/b14192/toc.htm

Upgrading database to 10.2.0.3.0

Published on by Advait4 Comments

Introduction and scope:

This document is intended for those which are having 10.2.0.2 database installation and intended to upgrade the same to 10.2.0.3, by applying 10.2.0.3 patchset.

This document is based on Metalink note ID 316900.1. Follow the below procedure for upgrading the database from 10.2.0.2 to 10.2.0.3

Pre-Requiste:

We are assuming that you have already have a database at 10.2.0.2 level. This patchset has to be applied over and above 10.2.0.2.

Pre Installation Steps:

  1. Download patch p5337014_10203_LINUX.zip (download patch according to your operating system like windows, solaris..) and copy it to your linux machine
  2. Unzip patch as “unzip p5337014_10203_LINUX.zip”
  3. Set ORACLE_HOME and ORACLE_SID to your database (10.2.0.2) on which you are planning to apply this patchset .
  • ORACLE_HOME = /slot01/oracle/db/tech_st/10.2.0
  • ORACLE_SID = mz1st121
  • export ORACLE_HOME ORACLE_SID

4. Stop database and database listener
5. Take a cold backup of your complete database.

Update Oracle Time Zone Definitions

Run utltzuv2.sql

SQL> @utltzuv2.sql

If the output shows “TIMEZONE data is consistent with the latest version 3 transition rules No need to validate TIMEZONE data” then we can simply skip other actions related to this and continue next step.

Else if the time zone definations are not consistant, please apply the patch 5601428 before proceeding.

Database 10.2.0.3 Patch Set Installation Steps:

Set the ORACLE_HOME and ORACLE_SID Environment Variables
Stop all process
Shutdown database

Login as oracle user, make sure that DISPLAY variable is set correctly and check xclock command is opening a clock. Change directory to patchset unziped directory and run the following command

1.jpg

bash-2.05$ ./runInstaller -invPtrLoc /slot01/oracle/db/tech_st/10.2.0/oraInst.Loc

Choose the correct ORACLE_HOME name and location. Remember that you are upgrading the existing 10.2.0.2 ORACLE_HOME.

Other screens are simple and self explanatory.

Check opatch version after ORACLE_HOME is upgraded.

bash-2.05$ cd $ORACLE_HOME
bash-2.05$ cd OPatch/
bash-2.05$ opatch version
Invoking OPatch 10.2.0.3.0

OPatch Version: 10.2.0.3.0

OPatch succeeded.

Now we have upgraded the ORACLE_HOME to 10.2.0.3. Next thing is to upgrade the database from 10.2.0.2 to 10.2.0.3. For this you need to open the database in upgrade.

  1. Mount the database and check the parameters first and check the parameters SHARED_POOL_SIZE and JAVA_POOL_SIZE. Values for these parameters should be atleast 150M. If not please set the values to atleast 150M.
  2. shutdown and startup again in upgrade mode.

SQL> startup upgrade
ORACLE instance started.

Total System Global Area 427819008 bytes
Fixed Size 2029464 bytes
Variable Size 398461032 bytes
Database Buffers 25165824 bytes
Redo Buffers 2162688 bytes
Database mounted.
Database opened.
SQL> spool upgrade.log
SQL> @?/rdbms/admin/catupgrd.sql

2.jpg

SQL> spool off

Once the upgrade is done. Check any errors in the spooled file. If every thing is OK you can proceed to recompile the invalids using utlrp.sql script present in ORACLE_HOME/rdbms/admin directory.

After recompiling shutdown the database and startup for allowing access to the users.