AskDba.org Weblog - Page 24 of 34 - Oracle DBA Journey to Cloud, DevOps and Big Data..

Index Access best approach?? Not Always…

There is a common misconception that Index access is the best access method for query execution. This approach leads to people concentrate on removing Full Table Scans (FTS) from the Query execution plan. I recently worked on a performance tuning issue where I found such scenario. Basically customer was trying to generate report for 3 years and query was running for more then 8 hours without producing any results. We were asked to look into this problem.

Following diagnostic data was collected to diagnose the issue.
1)    10046 trace at level 12
2)    Execution plan from V$SQL_PLAN (Database version is 9.2)
3)    Explain plan for offending query

From 10046 trace and V$session_wait, I found out query was waiting on “db file sequential read” wait event for table SCOTT_BILL. This indicated that we were reading this table using index rowid’s. Please find below explain plan for the problematic query.

SQL> @?/rdbms/admin/utlxpls.sql
PLAN_TABLE_OUTPUT

------------------------------------------------------------------------------------
| Id  | Operation                         |  Name          | Rows  | Bytes | Cost  |
------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                  |                |     1 |   161 | 66436 |
|   1 |  SORT ORDER BY                    |                |     1 |   161 | 66433 |
|*  2 |   FILTER                          |                |       |       |       |
|   3 |    NESTED LOOPS                   |                |     1 |   161 | 66424 |
|   4 |     NESTED LOOPS                  |                |     1 |   141 | 66421 |
|   5 |      NESTED LOOPS                 |                |     1 |   126 | 66419 |
|   6 |       NESTED LOOPS                |                |   391 | 27761 | 65246 |
|*  7 |        TABLE ACCESS FULL          | SCOTT_PO        | 21716 |   254K|    98 |
|   8 |        TABLE ACCESS BY INDEX ROWID| SCOTT_POIC      |     1 |    59 |     3 |
|*  9 |         INDEX RANGE SCAN          | SCOTT_POIC_MK3  |     1 |       |     2 |
|* 10 |       TABLE ACCESS BY INDEX ROWID | SCOTT_BILL      |     1 |    55 |     3 |
|* 11 |        INDEX RANGE SCAN           | SCOTT_BILL_FK7  |     1 |       |     2 |
|* 12 |      TABLE ACCESS BY INDEX ROWID  | SCOTT_CUST      |     1 |    15 |     2 |
|* 13 |       INDEX RANGE SCAN            | SCOTT_CUST_FK1  |     1 |       |     1 |
|  14 |     TABLE ACCESS BY INDEX ROWID   | SCOTT_DSST      |     1 |    20 |     3 |
|* 15 |      INDEX UNIQUE SCAN            | SCOTT_DSST_PK   |     1 |       |     2 |
|  16 |       SORT AGGREGATE              |                |     1 |    17 |       |
|  17 |        FIRST ROW                  |                |     1 |    17 |     4 |
|* 18 |         INDEX RANGE SCAN (MIN/MAX)| SCOTT_DSST_PK   |   166M|       |     4 |
|  19 |    SORT AGGREGATE                 |                |     1 |    36 |       |
|* 20 |     INDEX RANGE SCAN              | SCOTT_POIC_UK1  |     1 |    36 |     3 |
------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("SYS_ALIAS_5"."POIC_SEQ_NO"= (SELECT /*+ */ MAX("B"."POIC_SEQ_NO")
              FROM "MAT"."SCOTT_POIC" "B" WHERE "B"."POIC_PO_SEQ_NO"=:B1 AND "B"."POIC_PO_NO"=:B2
              AND "B"."POIC_ITEM_CODE"=:B3 AND "B"."POIC_PITM_SEQ_NO"=:B4 AND
              NVL("B"."POIC_RQMT_NO",0)=NVL(:B5,0) AND "B"."POIC_CTRT_TYPE"='BAS'))
   7 - filter(NVL("SCOTT_PO"."PO_COMP_CODE",'01')='01')
   9 - access("SCOTT_PO"."PO_NO"="SYS_ALIAS_5"."POIC_PO_NO" AND
              "SCOTT_PO"."PO_SEQ_NO"="SYS_ALIAS_5"."POIC_PO_SEQ_NO" AND
              "SYS_ALIAS_5"."POIC_CTRT_TYPE"='BAS')
       filter("SYS_ALIAS_5"."POIC_CTRT_TYPE"='BAS')
  10 - filter("SYS_ALIAS_6"."BILL_ASRV_DATE">=TO_DATE('2008-04-01 00:00:00',
              'yyyy-mm-dd hh24:mi:ss') AND "SYS_ALIAS_6"."BILL_ASRV_DATE"<=TO_DATE('2008-11-30
              23:59:59', 'yyyy-mm-dd hh24:mi:ss') AND NVL("SYS_ALIAS_6"."BILL_COMP_CODE",'01')='01
              ' AND "SYS_ALIAS_6"."BILL_SRV_TYPE"='S' AND
              "SYS_ALIAS_6"."BILL_EXCISE_FORM_SERIES_FLAG" IS NULL AND
              NVL("SYS_ALIAS_6"."BILL_EXCISE_FORM_SERIES_FLAG",'X')<>'N' AND
              "SYS_ALIAS_6"."BILL_INER_CODE" IS NULL AND
              "SYS_ALIAS_6"."BILL_ITEM_CODE"="SYS_ALIAS_5"."POIC_ITEM_CODE" AND
              "SYS_ALIAS_6"."BILL_PITM_SEQ_NO"="SYS_ALIAS_5"."POIC_PITM_SEQ_NO")
  11 - access("SYS_ALIAS_6"."BILL_PO_NO"="SYS_ALIAS_5"."POIC_PO_NO" AND
              "SYS_ALIAS_6"."BILL_PO_SEQ_NO"="SYS_ALIAS_5"."POIC_PO_SEQ_NO")
       filter(NVL("SYS_ALIAS_6"."BILL_RQMT_NO",0)=NVL("SYS_ALIAS_5"."POIC_RQMT_NO",0
              ))
  12 - filter(NVL("SCOTT_CUST"."CUST_COMP_CODE",'01')='01' AND
              "SCOTT_CUST"."CUST_INER_CODE" IS NULL)
  13 - access("SCOTT_CUST"."CUST_SRV_NO"="SYS_ALIAS_6"."BILL_SRV_NO")
  15 - access("SYS_ALIAS_6"."BILL_SRV_NO"="C"."DSST_SRV_NO" AND
              "C"."DSST_PROC_GROUP"=1 AND "C"."DSST_PROCESS"='PSRV' AND "C"."DSST_SEQ_NO"=
              (SELECT /*+ */ MAX("D"."DSST_SEQ_NO") FROM "MAT"."SCOTT_DSST" "D" WHERE
              "D"."DSST_PROCESS"='PSRV' AND "D"."DSST_PROC_GROUP"=1 AND "D"."DSST_SRV_NO"=:B1))
  18 - access("D"."DSST_SRV_NO"=:B1 AND "D"."DSST_PROC_GROUP"=1 AND
              "D"."DSST_PROCESS"='PSRV')
  20 - access("B"."POIC_PO_NO"=:B1 AND "B"."POIC_PO_SEQ_NO"=:B2 AND
              "B"."POIC_ITEM_CODE"=:B3 AND "B"."POIC_PITM_SEQ_NO"=:B4 AND
              "B"."POIC_CTRT_TYPE"='BAS')
       filter("B"."POIC_ITEM_CODE"=:B1 AND "B"."POIC_PITM_SEQ_NO"=:B2 AND
              NVL("B"."POIC_RQMT_NO",0)=NVL(:B3,0) AND "B"."POIC_CTRT_TYPE"='BAS')

Looking at explain plan (and plan from V$SQL_PLAN) confirmed that we were using index SCOTT_BILL_FK7. Plan shows that CBO estimates only 1 row for this operation. But since we have been on this wait even for close to 8 hours, it was clear that we are accessing more then 1 row.Normally this query was run for small range(date column) and used to complete in 10 mins. e.g Below is explain plan for the same query with 4 day’s range.

SQL> @?/rdbms/admin/utlxpls.sql
PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------------

-----------------------------------------------------------------------------------------
| Id  | Operation                             |  Name           | Rows  | Bytes | Cost  |
-----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                      |                 |     1 |   161 | 28876 |
|   1 |  SORT ORDER BY                        |                 |     1 |   161 | 28873 |
|*  2 |   FILTER                              |                 |       |       |       |
|*  3 |    TABLE ACCESS BY INDEX ROWID        | SCOTT_CUST       |     1 |    15 |     2 |
|   4 |     NESTED LOOPS                      |                 |     1 |   161 | 28864 |
|   5 |      NESTED LOOPS                     |                 |     1 |   146 | 28862 |
|   6 |       NESTED LOOPS                    |                 |     1 |   134 | 28861 |
|   7 |        NESTED LOOPS                   |                 |    56 |  4200 | 28581 |
|*  8 |         TABLE ACCESS BY INDEX ROWID   | SCOTT_BILL       |  2123 |   114K| 22212 |
|*  9 |          INDEX RANGE SCAN             | SCOTT_BILL_MK10  | 26081 |       |    85 |
|  10 |         TABLE ACCESS BY INDEX ROWID   | SCOTT_DSST       |     1 |    20 |     3 |
|* 11 |          INDEX UNIQUE SCAN            | SCOTT_DSST_PK    |     1 |       |     2 |
|  12 |           SORT AGGREGATE              |                 |     1 |    17 |       |
|  13 |            FIRST ROW                  |                 |     1 |    17 |     4 |
|* 14 |             INDEX RANGE SCAN (MIN/MAX)| SCOTT_DSST_PK    |   166M|       |     4 |
|* 15 |        TABLE ACCESS BY INDEX ROWID    | SCOTT_POIC       |     1 |    59 |     5 |
|* 16 |         INDEX RANGE SCAN              | SCOTT_POIC_MK3   |     1 |       |     2 |
|* 17 |       TABLE ACCESS BY INDEX ROWID     | SCOTT_PO         |     1 |    12 |     1 |
|* 18 |        INDEX UNIQUE SCAN              | SCOTT_PO_PK      |     1 |       |       |
|* 19 |      INDEX RANGE SCAN                 | SCOTT_CUST_FK1   |     1 |       |     1 |
|  20 |    SORT AGGREGATE                     |                 |     1 |    36 |       |
|* 21 |     INDEX RANGE SCAN                  | SCOTT_POIC_UK1   |     1 |    36 |     3 |
-----------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("SYS_ALIAS_5"."POIC_SEQ_NO"= (SELECT /*+ */ MAX("B"."POIC_SEQ_NO") FROM
              "MAT"."SCOTT_POIC" "B" WHERE "B"."POIC_PO_SEQ_NO"=:B1 AND "B"."POIC_PO_NO"=:B2 AND
              "B"."POIC_ITEM_CODE"=:B3 AND "B"."POIC_PITM_SEQ_NO"=:B4 AND
              NVL("B"."POIC_RQMT_NO",0)=NVL(:B5,0) AND "B"."POIC_CTRT_TYPE"='BAS'))
   3 - filter(NVL("SCOTT_CUST"."CUST_COMP_CODE",'01')='01' AND
              "SCOTT_CUST"."CUST_INER_CODE" IS NULL)
   8 - filter(NVL("SYS_ALIAS_6"."BILL_COMP_CODE",'01')='01' AND
              "SYS_ALIAS_6"."BILL_SRV_TYPE"='S' AND "SYS_ALIAS_6"."BILL_EXCISE_FORM_SERIES_FLAG" IS
              NULL AND NVL("SYS_ALIAS_6"."BILL_EXCISE_FORM_SERIES_FLAG",'X')<>'N' AND
              "SYS_ALIAS_6"."BILL_INER_CODE" IS NULL)
   9 - access("SYS_ALIAS_6"."BILL_ASRV_DATE">=TO_DATE('2008-04-01 00:00:00', 'yyyy-mm-dd
              hh24:mi:ss') AND "SYS_ALIAS_6"."BILL_ASRV_DATE"<=TO_DATE('2008-04-05 23:59:59',
              'yyyy-mm-dd hh24:mi:ss'))
  11 - access("SYS_ALIAS_6"."BILL_SRV_NO"="C"."DSST_SRV_NO" AND "C"."DSST_PROC_GROUP"=1
              AND "C"."DSST_PROCESS"='PSRV' AND "C"."DSST_SEQ_NO"= (SELECT /*+ */
              MAX("D"."DSST_SEQ_NO") FROM "MAT"."SCOTT_DSST" "D" WHERE "D"."DSST_PROCESS"='PSRV' AND
              "D"."DSST_PROC_GROUP"=1 AND "D"."DSST_SRV_NO"=:B1))
  14 - access("D"."DSST_SRV_NO"=:B1 AND "D"."DSST_PROC_GROUP"=1 AND
              "D"."DSST_PROCESS"='PSRV')
  15 - filter("SYS_ALIAS_6"."BILL_PITM_SEQ_NO"="SYS_ALIAS_5"."POIC_PITM_SEQ_NO" AND
              NVL("SYS_ALIAS_6"."BILL_RQMT_NO",0)=NVL("SYS_ALIAS_5"."POIC_RQMT_NO",0))
  16 - access("SYS_ALIAS_6"."BILL_PO_NO"="SYS_ALIAS_5"."POIC_PO_NO" AND
              "SYS_ALIAS_6"."BILL_PO_SEQ_NO"="SYS_ALIAS_5"."POIC_PO_SEQ_NO" AND
              "SYS_ALIAS_6"."BILL_ITEM_CODE"="SYS_ALIAS_5"."POIC_ITEM_CODE" AND
              "SYS_ALIAS_5"."POIC_CTRT_TYPE"='BAS')
  17 - filter(NVL("SCOTT_PO"."PO_COMP_CODE",'01')='01')
  18 - access("SCOTT_PO"."PO_NO"="SYS_ALIAS_5"."POIC_PO_NO" AND
              "SCOTT_PO"."PO_SEQ_NO"="SYS_ALIAS_5"."POIC_PO_SEQ_NO")
  19 - access("SCOTT_CUST"."CUST_SRV_NO"="SYS_ALIAS_6"."BILL_SRV_NO")
  21 - access("B"."POIC_PO_NO"=:B1 AND "B"."POIC_PO_SEQ_NO"=:B2 AND
              "B"."POIC_ITEM_CODE"=:B3 AND "B"."POIC_PITM_SEQ_NO"=:B4 AND "B"."POIC_CTRT_TYPE"='BAS')
       filter("B"."POIC_ITEM_CODE"=:B1 AND "B"."POIC_PITM_SEQ_NO"=:B2 AND
              NVL("B"."POIC_RQMT_NO",0)=NVL(:B3,0) AND "B"."POIC_CTRT_TYPE"='BAS')

Note: cpu costing is off

We can see that we used SCOTT_BILL_MK10 index here which was on the date column. This is also clear from predicate information available from explain plan.

   9 - access("SYS_ALIAS_6"."BILL_ASRV_DATE">=TO_DATE('2008-04-01 00:00:00', 'yyyy-mm-dd
              hh24:mi:ss') AND "SYS_ALIAS_6"."BILL_ASRV_DATE"<=TO_DATE('2008-04-05 23:59:59',
              'yyyy-mm-dd hh24:mi:ss'))

Coming back to our case, we see that index SCOTT_BILL_FK7 is being used here.This index is created on BILL_PO_NO, BILL_PO_SEQ_NO and BILL_RQMT_NO columns.

From explain plan, we see that following condition is used due to which we access the index

   11 - access("SYS_ALIAS_6"."BILL_PO_NO"="SYS_ALIAS_5"."POIC_PO_NO" AND
              "SYS_ALIAS_6"."BILL_PO_SEQ_NO"="SYS_ALIAS_5"."POIC_PO_SEQ_NO")
       filter(NVL("SYS_ALIAS_6"."BILL_RQMT_NO",0)=NVL("SYS_ALIAS_5"."POIC_RQMT_NO",0
              ))

Checking the column values, we see following

COLUMN_NAME          NUM_DISTINCT  NUM_NULLS
------------------------------ ------------ ----------
BILL_PO_NO             31841        636
BILL_PO_SEQ_NO          4       1296
BILL_RQMT_NO           437   27333280

I believe lot of null records present in the table along with NVL function usage is causing this issue. We can see from above data, that column BILL_RQMT_NO has 27 million null records. As B-Tree index does not index Null columns(B-tree indexes index null columns too if index is a concatenated index and the other column has non-null value. Refer to this article from Richard Foote). CBO assumed that only 1 row will be returned and it choose index SCOTT_BILL_FK7 as best access method. But because of NVL function, this returns more rows instead of just one row. This is my hypothesis and not sure , if its correct 🙂
As a quick fix , I suggested using FULL hint on SCOTT_BILL table so that we can perform a Full table Scan (FTS). This helped and query completed in 28 mins. If we look at long term solution, having a default value of 0 for this column could also be looked at. But all these things need to be decided in consideration with application design.

This is also a reminder to a fact that index access is not always the best access method. It is suitable if we are looking for very small data set and could become problem if data set increases.Whereas Full table Scan (FTS) perform better in such cases as we perform multiblock I/O.

17 December, 2008 by Amit Bansal Performance 6

How To Change/Restore User Password in 11G

Oracle 11g introduces Case-sensitive passwords for database authentication. Along with this if you wish to change the password (temporarily) and reset it back to old , you will find that password field in dba_users is empty. Prior to 11g we could use following technique to change/restore password

SQL> create user amit identified by amit;

User created.

SQL> grant create session to amit;

Grant succeeded.

SQL> conn sys as sysdba
Enter password:
Connected.
SQL> select username,password from dba_users where username='AMIT';

USERNAME                       PASSWORD
------------------------------ ------------------------------
AMIT                           9DEC0D889E8E9A6B

SQL> alter user amit identified by abc;

User altered.

SQL> conn amit/abc
Connected.
SQL> conn sys as sysdba
Enter password:
Connected.
SQL> alter user amit identified by values '9DEC0D889E8E9A6B';

User altered.

SQL> conn amit/amit
Connected.

In 11g if you query password field, it will return NULL.

SQL> select username,password from dba_users where username='AMIT';

USERNAME                       PASSWORD
------------------------------ ------------------------------
AMIT

Let’s first see Case-sensitive password feature in 11g and then steps to change/restore passwords

SQL> create user amit identified by AMIT;

User created.

SQL> grant connect,resource to amit;

Grant succeeded.

SQL> conn amit/amit
ERROR:
ORA-01017: invalid username/password; logon denied

Warning: You are no longer connected to ORACLE.
SQL> conn amit/AMIT
Connected.

This behavior is controlled by “sec_case_sensitive_logon” initialization paramter. If the value is true then it will enforce case sensitive passwords

SQL> select NAME,VALUE from V$SPPARAMETER where NAME='sec_case_sensitive_logon';

NAME                                     VALUE
---------------------------------------- --------------------
sec_case_sensitive_logon                 TRUE

SQL> conn / as sysdba
Connected.
SQL> alter system set sec_case_sensitive_logon=false;

System altered.

SQL> conn amit/amit
Connected.
SQL> conn / as sysdba
Connected.
SQL> alter system set sec_case_sensitive_logon=true;

System altered.

SQL> conn amit/amit
ERROR:
ORA-01017: invalid username/password; logon denied

Warning: You are no longer connected to ORACLE.
SQL> conn amit/AMIT
Connected.

Now to reset the password in 11g, we need to query spare4 column in user$ table

SQL> select spare4 from user$ where name='AMIT';

SPARE4
--------------------------------------------------------------------------------
S:2D058976AE8FAD8ECFCDB93835ACEE94C83EDE19169209155BB81FEE7DBB

SQL> alter user amit identified by abc12;

User altered.

SQL> conn amit/abc12
Connected.
SQL> conn / as sysdba
Connected.
SQL> alter user amit identified by values 'S:2D058976AE8FAD8ECFCDB93835ACEE94C83EDE19169209155BB81FEE7DBB';

User altered.

SQL> conn amit/abc12
ERROR:
ORA-01017: invalid username/password; logon denied

Warning: You are no longer connected to ORACLE.
SQL> conn amit/AMIT
Connected.

As per Metalink Note 429465.1 , view DBA_USERS has new column PASSWORD_VERSIONS rendered as follows:

decode(length(u.password),16,'10G ',NULL)||NVL2(u.spare4, '11G ' ,NULL)
for example:

SQL> select USERNAME, PASSWORD_VERSIONS from dba_users where rownum <5;

USERNAME                       PASSWORD
------------------------------ --------
SYS                            10G 11G
SYSTEM                         10G 11G
OUTLN                          10G 11G
DIP                            10G 11G

In this case it means both old and new-style hash values are available for the users, the new hash value is stored in the USER$.SPARE4 column, as long as this remains NULL it means the password has not been changed since the migration and the user will have the old case insensitive password.

SQL> create user test identified by test;

User created.

SQL> select USERNAME, PASSWORD_VERSIONS from dba_users where username in (‘AMIT’,’TEST’);

USERNAME                       PASSWORD
—————————— ——–
AMIT                           11G
TEST                           10G 11G

As I had reset password using only spare4 string, password will be case -sensitive irrespective of setting for sec_case_sensitive_logon parameter value. i.e why we see value of “11G” for user Amit.

Update

When resetting the password, we need to also query password column from user$ column if we wish to use case-insensitive feature in future. i.e In my above example I used only spare4 column value to reset the password. Now if I set sec_case_sensitive_logon=false , I will not be able to connect.

SQL> alter system set sec_case_sensitive_logon=false;

System altered.

SQL> conn amit/amit
ERROR:
ORA-01017: invalid username/password; logon denied

In case we wish to use both, we need to set identified by values ‘S:spare4;password’. As I didnot use password field while resetting, I find that password field in user$ is empty. To correct it, I had to change the password again.

SQL> select password,spare4 from user$ where name='AMIT';

PASSWORD                       SPARE4
------------------------------ ----------------------------------------------------------------------
                               S:2D058976AE8FAD8ECFCDB93835ACEE94C83EDE19169209155BB81FEE7DBB

SQL>  alter system set sec_case_sensitive_logon=true;

System altered.

SQL> alter user amit identified by AMIT;

User altered.

SQL> select password,spare4 from user$ where name='AMIT';

PASSWORD                       SPARE4
------------------------------ ----------------------------------------------------------------------
9DEC0D889E8E9A6B               S:F5DEBF680433864AA5A744C2368D8677A120939D083D74A2F6E99C5952AE

So to reset the password, following needs to be used.

SQL> select password,spare4 from user$ where name='AMIT';

PASSWORD                       SPARE4
------------------------------ ----------------------------------------------------------------------
9DEC0D889E8E9A6B               S:F5DEBF680433864AA5A744C2368D8677A120939D083D74A2F6E99C5952AE

SQL> alter user amit identified by values 'S:F5DEBF680433864AA5A744C2368D8677A120939D083D74A2F6E99C5952AE;9DEC0D889E8E9A6B';

User altered.

Thanks to Laurent for pointing this. You can see his article for more information.You can use below code to get the password script

select 'alter user '||name||' identified by values '''||password||''';' from user$ where spare4 is null and password is not null
union
select 'alter user '||name||' identified by values '''||spare4||';'||password||''';' from user$ where spare4 is not null and password is not null;

21 November, 2008 by Amit Bansal 11g 11

Tom Kyte Seminar in Bangalore, India

All India Oracle user Group (AIOUG) has arranged for Seminar by Tom Kyte on 18th December 2008.

Venue– The Good Shepherd Auditorium
Residency Road- Museum Road Junction
Opp. St. Joseph’s P.U. College
Bangalore – 560 025

SEMINAR TOPICS

Storage Techniques
Effective Indexing
Reorganizing objects

If you register before 20th November, you can save Rs 1000 🙂 You can find details here

18 November, 2008 by Amit Bansal Random 5

10g RAC Tuning :Useful Link

I came across useful link which contains 10g RAC Tuning tips from Joel Goodman (Oracle). It discusses RAC Instance Recovery concepts and key things to look out for while tuning RAC (Real Application Clusters) Databases. Must watch for anyone who is learning RAC.

You can find the recorded webcast here. You can also become member of oracleracsig to find many such webcasts/resources on RAC.

5 November, 2008 by Amit Bansal 10g 0

Full Database Export Failing With ORA-00980

While trying to do a full export for 10.2.0.3 database, we were getting following errors

About to export the entire database ...
. exporting tablespace definitions
. exporting profiles
. exporting user definitions
. exporting roles
. exporting resource costs
. exporting rollback segment definitions
. exporting database links
. exporting sequence numbers
. exporting directory aliases
. exporting context namespaces
. exporting foreign function library names
. exporting PUBLIC type synonyms
EXP-00008: ORACLE error 980 encountered
ORA-00980: synonym translation is no longer valid
EXP-00000: Export terminated unsuccessfully

So as to debug the problem, we set errorstack for ORA-00980 as follows and tried exporting again

alter system set events '980 trace name errorstack level 3';

Export job again failed with error but this time it generated a trace file in user_dump_dest location. We got the following sql as failing statement in tracefile

ORA-00980: synonym translation is no longer valid
Current SQL statement for this session:
SELECT SYNNAM, DBMS_JAVA.LONGNAME(SYNNAM2) SYNNAM2,DBMS_JAVA.LONGNAME(SYNTAB) SYNTAB,TABOWN, TABNODE, PUBLIC$, SYNOWN, SYNOWNID,
 TABOWNID, SYNOBJNO FROM   SYS.EXU9PTS      WHERE  TABOWNID = :1 ORDER  BY SYNTIME

A possible cause is that the package DBMS_JAVA is INVALID or absent in database so the public synonym DBMS_JAVA is no longer valid.Checking DBA_REGISTRY confirms that the JVM is in invalid state.

SQL> select COMP_NAME,VERSION,STATUS from dba_registry;
CATALOG  SYS      VALID        10.2.0.3.0 Oracle Database Catalog Views
CATPROC  SYS      VALID        10.2.0.3.0 Oracle Database Packages and Types
JAVAVM   SYS      INVALID      10.2.0.3.0 JServer JAVA Virtual Machine <--- Invalid state

We used Note 276554.1 – “How to Reload the JVM in 10.1.0.X and 10.2.0.X” in order to correct this problem.

Key things to look when diagnosing export/import issues

1)Check if you are using correct syntax. Use exp help=y or imp help=y to get the correct syntax.

2)Check if you are using correct exp/imp utility (version) against the database. Use following metalink note for compatibility matrix

Note 132904.1 – Compatibility Matrix for Export And Import Between Different Oracle Versions
3)Check DBA_REGISTRY view to see if the components are in valid state.
4) Try setting errorstack if you are facing “ORA-“ error.
5)Try setting trace=y parameter in export/import. This will generate a trace file (similar to sql trace) which can be used to diagnose the problems

e.g exp system/passwd full=y file=expdat.dmp log=exp.log trace=y

22 October, 2008 by Amit Bansal 10g

Question:Sizing ASM LUN

We received following question from Mr Orlando through our Contact Page

How about a discussion on how to size our LUNS to use with ASM. For instance we have a Dell MD3000 with 44x300GB HDDs (protected by RAID-10) and we’re planning to create LUNs of 2TB on the Storage, and deliver them to Windows 2003. for ASM to use what is common around the world? Just pass the 2TB Luns to Windows or maybe create several 500GB and give them to ASM. This is a RAC10g install, Oracle 10g Standard Edition.

Please find below our response to the question. We would also like to hear from others on their experiences and any other suggestions/opinions they may gave.

Our Response

As per 10gR2 ASM Best Practices document (available on ASM OTN Homepage), we can add Disk having size up to 2^32 Mb as ASM Disk (Minimum being 4 Mb). Therefore technically 2 Tb Disk/LUN can be added to the ASM Diskgroup but we would not recommend so.

Only advantage of having 2 Tb LUN would be reduced ASM Disks resulting in easier management. But there are some pitfalls too. We believe most of the problems will be seen during Disk Rebalance.

Suppose we have 3 TB of data on Two ASM Disks of 2 Tb each. Now we need to add new disk of 2Tb.In this case 1 Tb of data movement will happen and it will impact DB performance . Rebalance operation can also happen in case we are using Normal/High Redundancy and Disk failure occurs (due to Hardware problem or issues like header or block corruption), then failure group would be in Hung State. This needs to be corrected by adding a new Disk .

This would again require large amount of data movement (1.5 Tb in this example) and will take longer time to finish.

Lun size of 300-500 Gb should be fine for VLDB configuration. Having separate diskgroup for DATA and FRA (Flash Recover Area) is also recommended approach. One more thing which can be tried out in case of multi-terabyte database is to change the Allocation Unit size for ASM Diskgroup.Each Allocation Unit consumes some amount of memory in ASM SGA for storing the metadata information. Very Large Databases (VLDB) which can have sizes in TeraBytes will have too many AU’s allocated and also memory requirement for ASM instance will also increase. You can read more about this in our previous post Allocation Unit and Extents In ASM

8 September, 2008 by Amit Bansal 10g 0

Index Access best approach?? Not Always…

How To Change/Restore User Password in 11G

Tom Kyte Seminar in Bangalore, India

10g RAC Tuning :Useful Link

Full Database Export Failing With ORA-00980

Question:Sizing ASM LUN

Disclaimer!!

Recent Comments

Trending

Subscribe to Blog via Email