How to Create a SQL-testcase Using the DBMS_SQLDIAG Package

SQL Test Case Builder is a diagnostic tool in 11g, back-ported to 10g, that is designed to be able to obtain a reproducible test case for developers, DBAs, or Oracle Support.

SQL Test Case Builder Process

·         Build and/or identify a data pump directory on your database tier host to export test case data to.

·         Identify the SQL_ID for the problematic SQL statement.

·         Run DBMS_SQLDIAG.EXPORT_SQL_TESTCASE to export a test case to your dump directory. 

·         Zip the test case dump data and upload to Oracle Support, or transfer to another system in your environment. 

·         Either the customer or Oracle Support uses DBMS_SQLDIAG.IMPORT_SQL_TESTCASE to import the test case into their environment.

   
Exporting the testcase
-----------------------------

Create directory that we’ll use for exporting the test case:

SQL> Create directory testcase_exp as '/u01/app/testcase';

Directory created.

Use v$sql to get the SQL identifier.

SQL> Select SQL_ID,SQL_TEXT from v$sql where SQL_ID='18z53kqpbwj69';

SQL_ID          SQL_TEXT
------------- ----------------------------------------
18z53kqpbwj69 Select * from departments

The parameter description of EXPORT_SQL_TESTCASE can be found in $ORACLE_HOME/rdbms/admin/dbmsdiag.sql

PARAMETERS:
directory (IN)                                                  - directory to store the various generated files
sql_id (IN)                                        - identifier of the statement in the cursor cache
 plan_hash_value (IN)                - plan hash value of a particula plan of the SQL
exportEnvironment (IN)            - TRUE if the compilation environment should be exported
exportMetadata (IN)                  - TRUE if the definition of the objects referenced in the SQL should be exported.
exportData (IN)                             - TRUE if the data of the objects referenced in the SQL should be exported.
 exportPkgbody (IN)                   - TRUE if the body of the packages referencedin the SQL should be exported.
samplingPercent (IN)                  - if exportData is TRUE, specify the sampling percentage to use to create the dump file
ctrlOptions (IN)                             - opaque control parameters
 timeLimit (IN)                               - how much time should we spend exporting the SQL test case
 testcaseName (IN)                     - an optional name for the SQL test case. This is used to prefix all the generated scripts.
 testcaseMetadata (OUT)          - the resulting test case

SQL> declare
  tc_out clob;
begin
   dbms_sqldiag.export_sql_testcase(directory=>'TESTCASE_EXP',
                                    sql_id=>'18z53kqpbwj69',
                                    testcase => tc_out);
end;
/ 

PL/SQL procedure successfully completed.

Or using an SQL-statement :

SQL> declare
  tc_out clob;
begin
   dbms_sqldiag.export_sql_testcase(directory=>'TESTCASE_EXP',
                                sql_text=>'Select * from departments',
                                testcase => tc_out);
end;
/

A SQL test case contains a set of files needed to help reproduce a SQL problem on a different machine.

It contains:

1. a dump file containing schemas objects and statistics (.dmp)
2. the explain plan for the statements (in advanced mode)
3. diagnostic information gathered on the offending statement
4. an import script to execute to reload the objects.
5. a SQL script to replay system statistics of the source
6. A table of content file describing the SQL test case metadata (xxxxmain.xml).

[oracle@oggOra1 testcase]$ pwd
/u01/app/testcase
[oracle@oggOra1 testcase]$ ls -ltrh
total 364K
-rw-r--r-- 1 oracle oinstall 1.2K Jul 20 19:59 oratcb2_0090000D0001sql.xml
-rw-r--r-- 1 oracle oinstall 2.8K Jul 20 19:59 oratcb2_0090000D0001README.txt
-rw-r--r-- 1 oracle oinstall  637 Jul 20 19:59 oratcb2_0090000D0001ol.xml
-rw-r--r-- 1 oracle oinstall   65 Jul 20 19:59 oratcb2_0090000D0001ts.xml
-rw-r--r-- 1 oracle oinstall 4.8K Jul 20 19:59 oratcb2_0090000D0001dpexp.sql
-rw-r--r-- 1 oracle oinstall 1.3K Jul 20 20:00 oratcb2_0090000D0001dpexp.log
-rw-r----- 1 oracle oinstall 208K Jul 20 20:00 oratcb2_0090000D0001dpexp.dmp
-rw-r--r-- 1 oracle oinstall 2.5K Jul 20 20:00 oratcb2_0090000D0001xpl.txt
-rw-r--r-- 1 oracle oinstall  406 Jul 20 20:00 oratcb2_0090000D0001xpls.sql
-rw-r--r-- 1 oracle oinstall  695 Jul 20 20:00 oratcb2_0090000D0001xplo.sql
-rw-r--r-- 1 oracle oinstall  432 Jul 20 20:00 oratcb2_0090000D0001xplf.sql
-rw-r--r-- 1 oracle oinstall  87K Jul 20 20:00 oratcb2_0090000D0001.trc
-rw-r--r-- 1 oracle oinstall  847 Jul 20 20:00 oratcb2_0090000D0001ssimp.sql
-rw-r--r-- 1 oracle oinstall 1.9K Jul 20 20:00 oratcb2_0090000D0001main.xml
-rw-r--r-- 1 oracle oinstall 4.4K Jul 20 20:00 oratcb2_0090000D0001dpimp.sql

Importing the testcase in another database
-------------------------------------------------------

SQL> connect / as sysdba
SQL> create directory testcase_imp as '<imp_tc-dir>';
SQL> grant read,write on directory testcase_imp to <user>;

SQL> connect <user>/<password>

SQL> begin
       dbms_sqldiag.import_sql_testcase(directory=>'TESTCASE_IMP',
                             filename =>'oratcb2_0090000D0001main.xml');
     end;
     /

Note:
-  You should not run TCB under user SYS
-  By default for TCB, the data is NOT exported. In some case data is required, for example, to diagnose wrong result problem. To export data, call export_sql_testcase() with
                exportData=>TRUE

-  The data will be imported by default, unless turned OFF by
                importData=>FALSE

-  TCB includes PL/SQL package spec by default , but not the PL/SQL package body. You may need to have the package body as well, for example,to invoke the PL/SQL functions. 
            To export PL/SQL package body, call export_sql_testcase() with
                exportPkgbody=>TRUE
            To import PL/SQL package body, call import_sql_testcase() with
                importPkgbody=>TRUE

 Note: How to grant another user than SYS to use export_sql_testcase from cursor cache ( SQL_ID) the following grant are needed
SQL> grant select on V_$SQL to <USER>;
SQL> grant select on V_$SQL_BIND_CAPTURE to <USER>;

SCRIPT to skip ORA-8103 ORA-1410 ORA-1578 ORA-600 when reading a TABLE

The purpose of this plsql script is to create a new table based on a table that is producing errors such as ORA-8103 or ORA-1410 or ORA-1578. The script skips the blocks/rows producing those errors.
This is done when there is not option to restore the table from a backup like applying media recovery or recovering the table from an export or other source.
The first option to skip an ORA-1578 error is to use the DBMS_REPAIR script and decide to create a new table using create table as select (CTAS); however if for any reason that does not work use the plsql in this document instead.

Run sqlplus with SYS or TABLE owner user

sqlplus '/ as sysdba'
or
sqlplus <table owner> / password

SKIP ORA-1578 ORA-8103 ORA-1410
=================================
Create a new table based on the table that is producing errors with no rows:

SQL> create table <new table name> as select * from  <original table name> where  1=2;

Create the table to keep track of ROWIDs pointing to affected rows:

SQL> create table bad_rows (row_id rowid ,oracle_error_code number);

set serveroutput on

DECLARE
  TYPE RowIDTab IS TABLE OF ROWID INDEX BY BINARY_INTEGER;

  CURSOR c1 IS select /*+ index(tab1) */ rowid
  from <original table name> tab1
  where <indexed column> is NOT NULL;
  r RowIDTab;
  rows NATURAL := 20000;
  bad_rows number := 0 ;
  errors number;
  error_code number;
  myrowid rowid;
BEGIN
  OPEN c1;
  LOOP
   FETCH c1 BULK COLLECT INTO r LIMIT rows;
   EXIT WHEN r.count=0;
   BEGIN
    FORALL i IN r.FIRST..r.LAST SAVE EXCEPTIONS
     insert into <new table name>
     select /*+ ROWID(A) */ <list of columns from table (ie col1, col2,..)> OR * for all columns
     from <original table name> A where rowid = r(i);
   EXCEPTION
   when OTHERS then
    BEGIN
     errors := SQL%BULK_EXCEPTIONS.COUNT;
     FOR err1 IN 1..errors LOOP
      error_code := SQL%BULK_EXCEPTIONS(err1).ERROR_CODE;
      if error_code in (1410, 8103, 1578) then
       myrowid := r(SQL%BULK_EXCEPTIONS(err1).ERROR_INDEX);
       bad_rows := bad_rows + 1;
       insert into bad_rows values(myrowid, error_code);
      else
       raise;
      end if;
     END LOOP;
    END;
   END;
   commit;
  END LOOP;
  commit;
  CLOSE c1;
  dbms_output.put_line('Total Bad Rows: '||bad_rows);
END;
/

Notes:

- Replace the next values in the plsql script by the values of the affected table: <original table name>, <indexed column>, <list of columns from table (ie col1, col2,..)>

- The idea is to get the rowid's from an existent index, then get all the columns from the table for each rowid and insert these rows into the new table. Using the "index" hint, allows the optimizer to choose the most appropriated index to scan the table based on the indexed column.

- Make sure that the select in the plsql is using an index. One way to verify if the index is used is to get an execution plan from sqlplus:

set autotrace trace explain
select /*+ index(tab1) */ rowid from <original table name> tab1 where <indexed column> is NOT NULL;

- Note that the plsql executes an INSERT for 20000 rows and COMMIT. If it is required to change this, adjust the value of rows. e.g.:

rows NATURAL := 50000; -> to insert 50000 rows in one execution of INSERT and commit every 50000 records.

- If 'Total Bad Rows:' displays 0 and it is known for certain that there is a block incorrect on disk that is causing the ORA-8103, , then it means that the block is empty (no rows) and there is not data loss.

SKIP ORA-600 in a Table
=========================
This is useful when the ORA-600 is produced by a non-existent chained row (invalid nrid) like ORA-600 [kdsgrp1] and when event 10231 does not work.

SQL> drop table bad_rows;

SQL> create table bad_rows (row_id ROWID ,oracle_error_code number);

Create the new empty table:

SQL> create table &&new_table as select * from &&affected_table where 1=2;

set serveroutput on

declare
  n number:=0;
  bad_rows number := 0;
  error_code number;
  ora600 EXCEPTION;
  PRAGMA EXCEPTION_INIT(ora600, -600);
begin
  for i in (select rowid rid from  &&affected_table)  loop
  begin
    insert into  &&new_table
     select *
     from &&affected_table
     where rowid=i.rid;
     n:=n+1;
  exception
    when ora600 then
     bad_rows := bad_rows + 1;
     insert into bad_rows values(i.rid,600);
     commit;
    when others then
     error_code:=SQLCODE;
     bad_rows := bad_rows + 1;
     insert into bad_rows values(i.rid,error_code);
     commit;
  end;
  end loop;
  dbms_output.put_line('Total Bad Rows: '||bad_rows);
  dbms_output.put_line('Total Good rows: '||n);
end;
/
 
SKIP ORA-600 in IOT
====================
This is useful when the ORA-600 is produced by a non-existent chained row (invalid nrid) like ORA-600 [kdsgrp1] and when event 10231 does not work for an Index Organized Table (IOT).

SQL> drop table bad_rows;
SQL> create table bad_rows (row_id UROWID,oracle_error_code number);

Create the new empty table:

SQL>  create table &&new_table as select * from &&affected_table where 1=2;

set serveroutput on
declare
  n number:=0;
  bad_rows number := 0;
  error_code number;
  ora600 EXCEPTION;
  PRAGMA EXCEPTION_INIT(ora600, -600);
begin
  for i in (select /*+ INDEX_FFS(IOT_1) */ rowid rid from  &&affected_table IOT_1)  loop
  begin
    insert into  &&new_table
     select *
     from &&affected_table
     where rowid=i.rid;
     n:=n+1;
  exception
    when ora600 then
     bad_rows := bad_rows + 1;
     insert into bad_rows values(i.rid,600);
     commit;
    when others then
     error_code:=SQLCODE;
     bad_rows := bad_rows + 1;
     insert into bad_rows values(i.rid,error_code);
     commit;
  end;
  end loop;
  dbms_output.put_line('Total Bad Rows: '||bad_rows);
  dbms_output.put_line('Total Good rows: '||n);
end;
/

Creating Primary/Normal database from Standby backup.

Assuming you have taken backup from standby database.  Now you need to create standalone database using standby backup

Step 1:
      Create init ( minimum required parameter )
              ----------------------------------------------------------------------------
              DB_NAME=MASTERDB                               # Database name of standby/primary
              DB_UNIQUE_NAME="MASTERDBSNGS"         # can be set for differentiation
              control_files='\<path><>.CTL'
               log_file_name_convert = '<your current standby REDO location >,< your new host location >' ( # optional )
              ----------------------------------------------------------------------------
 
Step 2:
       $ export ORACLE_SID=MASTERDBSNGS
       $ sqlplus / as sysdba
         sql> startup nomount;

Step 3:
In another session

           $ export ORACLE_SID=MASTERDBSNGS
           $ rman target /

         RMAN> restore controlfile from '<backup location\ctl.BKP';
         RMAN> alter database mount;
         RMAN> catalog start with '< backup piece location>';

          RMAN      
          run {
                   allocate channel t1 type disk;
                   allocate channel t2 type disk;
                   set newname for datafile 1 to '<  new location\ DF name> ';
                   set newname for datafile 2 to '<  new location\ DF name> ';
                    ...
                   set until sequence 212;  # this is to set as per your requirement          
                   restore database;
                   switch datafile all;
                   recover database;
             }

Step 4:  alter database open resetlogs.

if log_file_name_convert is set at init , during database open, ORL will be created as per its value.

example: Init.ora

log_file_name_convert = '\ORADATA\MASTDBSNG\REDO\','\ORADATA\MASTDBSNGS\REDO\' 

else, at mount stage, you need to  rename the ORL location as below otherwise while " Alter database open resetlogs" throw file not found error

           @ mount stage

            SQL> alter database rename file '<old file location and name>' to '<new location and name>';
            SQL> alter database open resetlogs;

Disk Space of Temporary Tablespace (Sparse files)

A temporary tablespace contains schema objects only for the duration of a session. Temporary data files (temp files) in Oracle are a special type of data file. Oracle will use temporary files to store the intermediate results of a large sort operation, hash operations, global temporary table data, or result set, when there is insufficient memory to hold it all in RAM. Temp files are always set to NOLOGGING mode, which means that they never have redo generated for them. Media recovery does not recognize temp files.


When you create or resize temp files, they are not always guaranteed allocation of disk space for the file size specified. On file systems such as Linux and UNIX, temp files are created as sparse files. In this case, disk blocks are allocated not at file creation or resizing, but as the blocks are accessed for the first time.

When you check the space on operating system level you see that commands like 'du' and 'df' do not show a significant change of allocated file. free space on the one hand. On the other hand a 'ls -l <path and file>' shows a filesize as specified in the 'create' or 'alter' command.

It is possible that a file created with 2G initially allocates only 1M on disk.

SQL> set timing on

SQL> alter tablespace TEMP01 add tempfile '/u01/app/oracle/oradata/ORCL1/tempfiles/temp02.dbf' size 2G;

Tablespace altered.

Elapsed: 00:00:00.52

SQL> select file_name,ceil(bytes / 1024 / 1024) "size MB" from   dba_temp_files where  tablespace_name = 'TEMP01';

FILE_NAME                                         size MB

------------------------------------------------------------ ------------

/u01/app/oracle/oradata/ORCL1/tempfiles/temp02.dbf              2,048

[oracle@oggOra1 tempfiles]$ ls -lth

total 1.1M

-rw-r----- 1 oracle oinstall 2.1G Jul 11 01:45 temp02.dbf

Sparse files have different apparent file sizes (the maximum size to which they may expand) and actual file sizes (how much space is allocated for data on disk). To check the file's apparent size, just run:

[oracle@oggOra1 tempfiles]$ du -h --apparent-size temp02.dbf

2.1G  temp02.dbf

And, to check the actual size of the file on disk:

[oracle@oggOra1 tempfiles]$ du -h temp02.dbf

1.1M  temp02.dbf

As you can see, although the apparent size of the file is 2G , its "actual" size is really 1M—that's because due to the nature and beauty of sparse files, it will "expand" arbitrarily to minimize the space required to store its contents.

Sparse file

In computer science, a sparse file is a type of computer file that attempts to use file system space more efficiently when blocks allocated to the file are mostly empty. This is achieved by writing brief information (metadata) representing the empty blocks to disk instead of the actual "empty" space which makes up the block, using less disk space. The full block size is written to disk as the actual size only when the block contains "real" (non-empty) data.

When reading sparse files, the file system transparently converts metadata representing empty blocks into "real" blocks filled with zero bytes at runtime. The application is unaware of this conversion.

Advantages

The advantage of sparse files is that storage is only allocated when actually needed: disk space is saved, and large files can be created even if there is insufficient free space on the file system.

Disadvantages

Disadvantages are that sparse files may become fragmented; file system free space reports may be misleading; filling up file systems containing sparse files can have unexpected effects; and copying a sparse file with a program that does not explicitly support them may copy the entire, uncompressed size of the file, including the sparse, mostly zero sections which are not on disk -- losing the benefits of the sparse property in the file. 

Caution: Sparse files enable fast temp file creation and resizing; however, the disk could run out of space later when the temp files are accessed.

Usage of sparse files is not a bug. Therefore there is no possibility to tell Oracle not to use sparse files for the temporary tablespace if the operating system offers sparse file functionality.

If you want to avoid that files do allocate less space on disk than they are defined:

After copying the sparse 2G file to newtemp02.dbf and creating the temporary tablespace using that tempfile with the REUSE option.  Assured that tempfile has allocated all of its file system space and our database actually has 2G of temporary space to work with.

SQL> Drop tablespace TEMP01;

Tablespace dropped.

[oracle@oggOra1 tempfiles]$ cp --sparse=never temp02.dbf newtemp02.dbf

[oracle@oggOra1 tempfiles]$ ls -ltrh

total 2.1G

-rw-r----- 1 oracle oinstall 2.1G Jul 11 01:45 temp02.dbf

-rw-r----- 1 oracle oinstall 2.1G Jul 11 02:18 newtemp02.dbf

[oracle@oggOra1 tempfiles]$ du -h temp0*

2.1G  newtemp02.dbf

1.1M  temp02.dbf

SQL> Create temporary tablespace TEMP01 tempfile '/u01/app/oracle/oradata/ORCL1/tempfiles/newtemp02.dbf' reuse;

Thanks :)

Ahmed Fathi