Blog Aggregator - Amardeep Sidhu

Today one of my friend called me up and said that she is not able to do a login from the Enterprise Manager of Oracle in Windows operating system. She wanted to test some backup from the EM and for that, a host login is must from the EM. Some how this was not working […]

As I mentioned on my personal blog I will be moving the more technical posts over to this site and keeping the old site as more of a personal weblog. Of course, there will be some posts that are both personal and technical… I hope to start moving things over in the next week, […]

I was just reading Tonguc’s blog and I found that Alberto Dellera, who is a very frequent contributor on AskTom has started his own website and I am sure that there will be a lot of goodies about Oracle that me and every one would found there. Just added this to my list.Also on the same post of […]

I came back last night from the program. It was okay. All the participants were happy and I guess that’s some thing which is more than enough for me. Though it was really difficult for me to continue as I am not feeling well. There is a huge amount of effort that I had to […]

How to check LAN status on HP-UX

On rx6600 Itanium Integrity servers there are 6 lan (network) interface card .sometimes It’s mind boggling to figure out on HP-UX server which lan/network card are connected to network (active) .

I have plugged in network cable on to LAN/network interface card but not sure which network card is active before assigning ip address and trying to bring that lan/network interface up. So I searched on google and came up with nothing. Finally poking around with landmain commands and trying each of the options I was able to figure out to check active lan card on HP-UX servers.

For checking if Ethernet cable is plugged into which lan/network interface card use lanadmin command as described below:

Under commands type lan and then ppa and enter the lan interface card instance number, since primary lan netwok card (instance 0) is already in use , try with lan instance number in sequence like 1or next available

Type display to display the Lan interface status, to check if the network cable is plugged into the lan interface card look into the field Operation status if Operation status is down then network cable is not plugged into the lan interface card. Here is the next example where network cable is plugged into the lan interface card 4 (instance 4) :

Clearly operation status value is listed as up, once you determined the network cable is plugged into the lan interface card you can bring the network interface up and assign ip address to make it available on the network.

How to stop/start virtual machine in HP-UX

Itanium Integrity HP-UX server supports running virtual machines as guest operating systems. As of December 2007 following guest operating systems can be configured to run as virtual machines on HP-UX integrity servers :

Supported Virtual Machine guest operating systems:

• HP UX 11i v2 May 2005 (or later)
  
• HP-UX 11i v3
  
• Microsoft Windows Server 2003® Enterprise or Datacenter OS Editions + SP1 or SP2 for 64 bit Itanium based systems (including Japanese version)
  
• Red Hat Enterprise Linux Advanced Platform (RHEL AP) 4.4 and 4.5 for HP Integrity servers
  
• Novell SUSE® Linux Enterprise Server (SLES 10 SP 1) for HP Integrity servers
  

I am going to list command that is useful for stopping, starting and checking the status of virtual machines on HP-UX host

Checking status of virtual machine on HP-UX

To check the status of virtual machines on HP-UX host run the following command:

Starting virtual machines on HP-UX:

Virtual machines hostA and hostB are running HP-UX and they are currently not running.To start the virtual machine on HP-UX run the hpvmstart command:

Stopping virtual machines on HP-UX:

Before stopping the virtual machines, it’s always good to shutdown the HP-UX servers to avoid filesystem corruption. Technically stopping virtual machines means powering off the machines so carefully shutdown the virtual guest operating system before stopping the virtual machines.

The answer to this question is yes, as Oracle Database 11g provides a new diagnostic tool called SQL Test Case Builder. In this article, we explain what SQL Test Case Builder is, and how to use it with examples.

Why SQL Test Case Builder?

For most SQL problems, the single most important factor for a speedy bug resolution is to obtain a reproducible test case. However, this is normally the longest and most painful step for customers. The goal of the SQL Test Case Builder (TCB) is to automatically gather as much information as possible related to a SQL incident (problem) and package it in a way that allows a developer or a support engineer to reproduce the problem on his or her own machine quickly.

At a very high-level, SQL Test Case Builder can be seen as a way to export a SQL. Currently, Oracle export (expdp) takes a schema or a set of tables and exports all the dependents objects. SQL Test Case Builder provides the same service but takes a SQL statement as input.

What’s Inside Test Case Builder?

The main input of SQL Test Case Builder is a SQL object. A SQL object is defined as the SQL text plus all the information required to compile it on a particular database instance (this contains the parsing user name, for example).

Logically, a SQL test case appears as a script containing all the necessary commands to recreate the objects, the user, the statistics, and the environment.

Within the Oracle Diagnosability infrastructure, TCB compiles the problem SQL in a special capture mode to obtain the set of objects to export. A test case captures two types of information:

1. Permanent information
• SQL text
• PL/SQL functions, procedures, packages
• Statistics
• Bind variables
• Compilation environment
• User information (like privileges)
• SQL profiles, stored outlines, or other SQL Management Objects
• Meta data on all the objects involved
• Optimizer statistics
• The execution plan information
• The table content (sample or full). This is optional.
2. Transient information

For most of the SQL test cases, the permanent information above is enough to reproduce a problem. There are however cases where this is not enough and additional information about the context in which this SQL was compiled is required. Therefore, in addition to the permanent information, SQL Test Case Builder captures transient information, e.g. information that is only available as part of the compilation of the SQL statement. This includes dynamic sampling results, cached information, some run time information, like the actual degree of parallelism used, etc.

As part of creating a SQL test case, the SQL object is reloaded and all the diagnostic information available generated and gathered automatically. This information will be made available to Oracle support and developers.

How do I use the SQL Test Case Builder?

The task of creating a SQL test case can be performed in two ways:

• From EM (Enterprise Manager), where TCB is invoked on user-demand via IPS (Incident Packaging Service) after a SQL incident occurred. The user can also manually create an incident for a problem query for building test case purpose.
• From SQLPLUS, where you can directly invoke one of the PL/SQL API functions in the SQL Diagnostic package. We will give examples of using the APIs below.

All the new PL/SQL procedures supporting SQL Test Case Builder are part of a new PL/SQL package called dbms_sqldiag (see dbmsdiag.sql for details). The two main features related to TCB in this package are export and import test cases.

• Procedure dbms_sqldiag.export_sql_testcase exports a SQL test case for a given SQL statement to a given directory.
• Procedure dbms_sqldiag.import_sql_testcase imports a test case from a given directory.

To build (or export) a test case, the simplest form would be something like:

     dbms_sqldiag.export_sql_testcase(       directory  => ‘TCB_DIR_EXP’,       sql_text   => ’select count(*) from sales’,       testcase   => tco)

Here directory and sql_text are inputs which specify where the test case will be stored, and the problem query statement, respectively. Testcase specifies the test case metadata as output.

For security reason, the user data are not exported by default. You have the option to set exportData to TRUE to include the data. You can also set samplingPercent if you are exporting with data. To protect users proprietary codes, TCB will not export PL/SQL package body by default.

Once the test case has been built, you can copy all the files under the export directory to your test environment. Note there is a file called xxxxxxxxmain.xml, for example, oratcb1_03C600800001main.xml, which contains the metadata of the test case.

Now importing the test case can be as simple as:

     dbms_sqldiag.import_sql_testcase(       directory => ‘TEST_DIR,       filename => ‘oratcb1_03C600800001main.xml’)

To verify that the test case is successfully rebuilt, you can just issue an explain command for the problem query. However, if you want to actully run the query, then you need to have the data available.

You can refer to dbmsdiag.sql for more information about other options available for these procedures.

Example - We now show the typical steps of using TCB by a sample query with materialized view. In this exmaple, we set the exportData option to TRUE, so we can re-run the same query after the TCB task is completed.

1. Setup

SQL> connnect / as sysdbaConnected.SQL>SQL> create or replace directory TCB_DIR_EXP as  2  ‘/net/tiger/apps/tcb_exp’;Directory created.SQL>SQL> grant dba to apps;Grant succeeded.SQL>SQL> connect apps/appsConnected.SQL>SQL> create materialized view scp_mvu  2  parallel 2  3  as  4  select          p.prod_name, c.cust_gender,  5                  max(s.amount_sold) max_amount_sold  6  from            sales s, products p, customers c  7  where           s.prod_id = p.prod_id  8  and             s.cust_id = c.cust_id  9  group by        p.prod_name, c.cust_gender;

Materialized view created.

SQL>SQL> desc scp_mvu; Name                                      Null?    Type —————————————– ——– ———— PROD_NAME                                 NOT NULL VARCHAR2(50) CUST_GENDER                                        CHAR(1) MAX_AMOUNT_SOLD                                    NUMBER

SQL>SQL> select * from scp_mvu where max_amount_sold > 7000 order by 3;

PROD_NAME                                          C MAX_AMOUNT_SOLD————————————————– - —————Joseph Sportcoat                                   F          7400.8Kenny Cool Leather Skirt                           M            7708Leather Boot-Cut Trousers                          M            8184

3 rows selected.

2. Export as user APPS

SQL> connect apps/appsConnected.

SQL>SQL> Rem define the problem SQL statementSQL> create or replace package define_vars is  2    sql_stmt1     varchar2(2000) := q’# select * from scp_mvu  3                                        where max_amount_sold > 7000  4                                        order by 3  5                                      #’;  6  end;  7  /

Package created.SQL> SQL> set serveroutput onSQL>SQL> declare  2    tco           clob;  3  begin  4    — Export test case  5    dbms_sqldiag.export_sql_testcase  6    (  7      directory           => ‘TCB_DIR_EXP’,  8      sql_text            => define_vars.sql_stmt1,  9      user_name           => ‘APPS’, 10      exportData          => TRUE, 11      testcase            => tco 12    ); 13  14  end; 15  /

PL/SQL procedure successfully completed.SQL>SQL> Rem Drop MV before importingSQL> drop materialized view scp_mvu;

Materialized view dropped.


At this stage, the export procedure has successfully completed. The next commands prepare a directory for import purpose. The directory could be on a different machine.


SQL> conn / as sysdbaConnected.SQL> create or replace directory TCB_DIR_IMP  2  as ‘/net/lion/test/tcb_imp’;Directory created.SQL>SQL> grant dba to test;Grant succeeded.


As the export has finished successfully, you can now transfer all the files under TCB_DIR_EXP to a directory in test environment, for example, TCB_DIR_IMP as created above. Again, look up and make note of the TCB metadata file xxxxxxxxmain.xml, which will be used below.

3. Import as user TEST

SQL> connect test/testConnected.SQL>SQL> set serveroutput onSQL>SQL> begin  2    — Import test case  3    dbms_sqldiag.import_sql_testcase  4    (  5      directory           => ‘TCB_DIR_IMP’,  6      filename            => ‘oratcb3_05e803500001main.xml’,  7      importData          => TRUE  8    );  9  10  end; 11  /

PL/SQL procedure successfully completed.

4. Verification. This is to check that now all relevant objects were imported successfully.

SQL> desc scp_mvu; Name                                      Null?    Type —————————————– ——– ———— PROD_NAME                                 NOT NULL VARCHAR2(50) CUST_GENDER                                        CHAR(1) MAX_AMOUNT_SOLD                                    NUMBERSQL>SQL> select * from scp_mvu where max_amount_sold > 7000 order by 3;

PROD_NAME                                          C MAX_AMOUNT_SOLD————————————————– - —————Joseph Sportcoat                                   F          7400.8Kenny Cool Leather Skirt                           M            7708Leather Boot-Cut Trousers                          M            8184

3 rows selected.

Finally, we also have good news for 10g users: SQL Test Case Builder has been backported to 10.2.0.4!