Friday, May 31, 2013

NFS Mount Point disconnect Causes Instance to Hang on AIX

Recently we saw an issue with slow sqlplus login response time when we had a NFS file system mount disconnected due to a network issue in AIX environment. Instead of regular subsecond response, it took more than 5 minutes to complete the login when we had this issue.

This is an issue with the way in which the system call getcwd is implemented within AIX.
As long as the NFS mount point has at least one other parent directory besides the root directory,
this problem will not occur, regardless of whether the remote file system is reachable or not.

For example, if your current NFS mount point is called /oracle_depot, the fix would be to rename the mount point to something like /nfs/oracle_depot.

# unmount /oracle_depot
# mkdir /nfs
# mv /oracle_depot /nfs
# mount nfsmaster01:/oracle_share /oracle_depot/nfs
Be sure to make a similar configuration change within smit, so that it will survive a reboot.

Wednesday, May 29, 2013

ORA-06512 on Materialized view refresh job

Recently we had following error in  one of our production databases which uses Materialized view and I could see following ORA messages in its alert log and we noticed that it is occurring due to a privilege issue.

Tue Mar 19 11:14:00 2013
Errors in file /orasw/pmqc1/app/oracle/diag/diag/rdbms/pmqc1/pmqc1/trace/pmqc1_j007_22675516.trc:
ORA-12012: error on auto execute of job 103
ORA-12008: error in materialized view refresh path
ORA-00942: table or view does not exist
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2563
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2776
ORA-06512: at "SYS.DBMS_IREFRESH", line 685
ORA-06512: at "SYS.DBMS_REFRESH", line 195
ORA-06512: at line 1
Tue Mar 19 11:16:00 2013

Contents of trace file

Trace file /orasw/pmqc1/app/oracle/diag/diag/rdbms/pmqc1/pmqc1/trace/pmqc1_j007_22675516.trc
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
ORACLE_HOME = /orasw/pmqc1/app/oracle/product/11.2.0/db
System name:    AIX
Node name:      prdmqcx001
Release:        1
Version:        6
Machine:        00F65A334C00
Instance name: pmqc1
Redo thread mounted by this instance: 1
Oracle process number: 109
Unix process pid: 22675516, image: oracle@prdmqcx001 (J007)


*** 2013-03-19 11:13:59.952
*** SESSION ID:(2063.11133) 2013-03-19 11:13:59.952
*** CLIENT ID:() 2013-03-19 11:13:59.952
*** SERVICE NAME:(SYS$USERS) 2013-03-19 11:13:59.952
*** MODULE NAME:() 2013-03-19 11:13:59.952
*** ACTION NAME:() 2013-03-19 11:13:59.952

Failed REFRESH STATEMENT
/* MV_REFRESH (DEL) */ DELETE FROM "MV_ADMIN"."TEST" SNA$ WHERE "ROW_ID" IN (SELECT /*+ NO_MERGE  HASH_SJ  */ * FROM (SELECT  CHARTOROWID("MAS$"."M_ROW$$") RID$     FROM "ENTERPRISE_TMO_PRODUCTION_NEW
_"."MLOG$_TEST" "MAS$"   WHERE "MAS$".SNAPTIME$$ > :B_ST0 ) AS OF SNAPSHOT(:B_SCN) MAS$)  AND "MARKER" = 1

*** 2013-03-19 11:13:59.992

*** 2013-03-19 11:14:00.001
ORA-12012: error on auto execute of job 103
ORA-12008: error in materialized view refresh path
ORA-00942: table or view does not exist
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2563
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2776
ORA-06512: at "SYS.DBMS_IREFRESH", line 685
ORA-06512: at "SYS.DBMS_REFRESH", line 195
ORA-06512: at line 1

In our case, MV_ADMIN.TEST materialized view is created with UNION of following tables.
Enterprise_2012_enterprise_tes.TEST
Enterprise_2013_enterprise_tes.TEST
Enterprise_tmo_production_new_.TEST

From the following query you could see that MV_ADMIN schema user does not have select privileges to Enterprise_tmo_production_new_ MLOG$_TEST mview log table. This could be the reason for failures. Now you might ask why it did not fail yesterday or even today after I manually refreshed it from command line. I need to assume that most of the time system is using full refresh and we get error when it try to do a fast refresh.

select grantee,owner,table_name,privilege from dba_tab_privs where table_name like 'MLOG$_TEST' and grantee='MV_ADMIN';

GRANTEE                        OWNER                          TABLE_NAME                     PRIVILEGE
------------------------------ ------------------------------ ------------------------------ ----------------------------------------
MV_ADMIN                       ENTERPRISE_2012_ENTERPRISE_TES MLOG$_TEST                     SELECT
MV_ADMIN                       ENTERPRISE_2010_ENTERPRISE_TES MLOG$_TEST                     SELECT
MV_ADMIN                       ENTERPRISE_2011_ENTERPRISE_TES MLOG$_TEST                     SELECT
MV_ADMIN                       ENTERPRISE_2013_ENTERPRISE_TES MLOG$_TEST                     SELECT
MV_ADMIN                       PRODUCT_DEV_QC10               MLOG$_TEST                     SELECT
MV_ADMIN                       SEATTLE_PRODUCT_DEV_DB         MLOG$_TEST                     SELECT

Just to follow Oracle’s recommendation, I granted select privileges and I did not get the ORA error anymore

OPS$ORACLE@pmqc1> grant select on ENTERPRISE_TMO_PRODUCTION_NEW_.MLOG$_TEST to MV_ADMIN;

Grant succeeded.

OPS$ORACLE@pmqc1> select grantee,owner,table_name,privilege from dba_tab_privs where table_name like 'MLOG$_TEST' and grantee='MV_ADMIN';

GRANTEE                        OWNER                          TABLE_NAME                     PRIVILEGE
------------------------------ ------------------------------ ------------------------------ ----------------------------------------
MV_ADMIN                       ENTERPRISE_TMO_PRODUCTION_NEW_ MLOG$_TEST                     SELECT
MV_ADMIN                       ENTERPRISE_2012_ENTERPRISE_TES MLOG$_TEST                     SELECT
MV_ADMIN                       ENTERPRISE_2010_ENTERPRISE_TES MLOG$_TEST                     SELECT
MV_ADMIN                       ENTERPRISE_2011_ENTERPRISE_TES MLOG$_TEST                     SELECT
MV_ADMIN                       ENTERPRISE_2013_ENTERPRISE_TES MLOG$_TEST                     SELECT
MV_ADMIN                       PRODUCT_DEV_QC10               MLOG$_TEST                     SELECT
MV_ADMIN                       SEATTLE_PRODUCT_DEV_DB         MLOG$_TEST                     SELECT

Tuesday, May 28, 2013

How to find Materialized view refresh details

Let me briefly explain what is Materialized View before going into details of how to find refresh details.

We all know that VIEW is a logical table based on one of more tables or views from either local or remote database. A View does not use any segments in database and contain no data by itself. We see real time data in a VIEW. But Materialized view is a database object that stores the results of a query and it can be from local database or from remote database and use data segment to store the data. Earlier it was called as snapshots. This object gets refreshed on a periodical basis either through a DBMS refresh job or via command line. Advantage with Materialized View is faster query response and drawback is slightly old data.

I am not going into details on how to create a Materialized view as it is not in current scope of this article.
Following query will help us to find out last time Materialized view was refreshed and what type of refresh ( ex: COMPLETE, FAST, FORCE) was used.

SQL> select owner,mview_name,refresh_mode,refresh_method,last_refresh_type,last_refresh_date,staleness from dba_mviews;

OWNER                          MVIEW_NAME                     REFRES REFRESH_ LAST_REF LAST_REFRESH_DATE   STALENESS
------------------------------ ------------------------------ ------ -------- -------- ------------------- -------------------
SYSMAN                         MGMT_ECM_MD_ALL_TBL_COLUMNS    DEMAND FORCE    COMPLETE 2012-12-13 09:15:33 FRESH
MV_ADMIN                       ALL_LISTS                      DEMAND FAST     FAST     2013-05-24 08:24:29 FRESH
MV_ADMIN                       AUDIT_LOG                      DEMAND FAST     COMPLETE 2013-05-24 08:25:09 STALE
MV_ADMIN                       USER_CONTACT_INFO              DEMAND FAST     FAST     2013-05-24 08:24:40 NEEDS_COMPILE
MV_ADMIN                       ENV_REQ_BUG                    DEMAND FAST     FAST     2013-05-24 08:24:29 NEEDS_COMPILE
MV_ADMIN                       PRODUCT_BUG                    DEMAND FAST     FAST     2013-05-24 08:24:34 NEEDS_COMPILE
MV_ADMIN                       PRODUCT_CYCL_FOLD              DEMAND FAST     FAST     2013-05-24 08:24:39 FRESH
MV_ADMIN                       PRODUCT_CYCLE                  DEMAND FORCE    FAST     2013-05-24 08:24:34 FRESH
MV_ADMIN                       PRODUCT_REQ                    DEMAND FAST     FAST     2013-05-24 08:24:34 FRESH
MV_ADMIN                       PRODUCT_TEST                   DEMAND FORCE    FAST     2013-05-24 08:24:39 FRESH
MV_ADMIN                       PRODUCT_TESTCYCL               DEMAND FAST     FAST     2013-05-24 08:24:39 FRESH
MV_ADMIN                       CYCLE                          DEMAND FAST     FAST     2013-05-24 08:24:40 COMPILATION_ERROR
MV_ADMIN                       REQ                            DEMAND FAST     FAST     2013-05-24 08:24:40 NEEDS_COMPILE
MV_ADMIN                       TESTCYCL                       DEMAND FAST     FAST     2013-05-24 08:24:50 COMPILATION_ERROR
MV_ADMIN                       COMPONENT                      DEMAND FAST     FAST     2013-05-24 08:24:34 FRESH
MV_ADMIN                       CYCL_FOLD                      DEMAND FAST     FAST     2013-05-24 08:24:39 FRESH
MV_ADMIN                       DATA_REQ_BUG                   DEMAND FAST     FAST     2013-05-24 08:24:24 NEEDS_COMPILE
MV_ADMIN                       AUDIT_PROPERTIES               DEMAND FAST     FAST     2013-05-24 08:26:16 FRESH
MV_ADMIN                       TEST                           DEMAND FAST     FAST     2013-05-24 08:24:14 FRESH
MV_ADMIN                       BUG                            DEMAND FAST     FAST     2013-05-24 08:24:40 NEEDS_COMPILE
MV_ADMIN                       USERS                          DEMAND FAST     FAST     2013-05-24 08:25:40 FRESH
MV_ADMIN                       GROUPS                         DEMAND FAST     FAST     2013-05-24 08:27:15 FRESH


Another query to find time taken for refresh

SQL> SELECT mview_name, last_refresh_date "START_TIME",
   CASE
      WHEN fullrefreshtim <> 0 THEN
         LAST_REFRESH_DATE + fullrefreshtim/60/60/24
      WHEN increfreshtim <> 0 THEN
         LAST_REFRESH_DATE + increfreshtim/60/60/24
      ELSE
         LAST_REFRESH_DATE
   END "END_TIME",
   fullrefreshtim, increfreshtim
FROM all_mview_analysis;

MVIEW_NAME                     START_TIME          END_TIME            FULLREFRESHTIM INCREFRESHTIM
------------------------------ ------------------- ------------------- -------------- -------------
MGMT_ECM_MD_ALL_TBL_COLUMNS    2012-12-13 09:15:33 2012-12-13 09:15:36              3             0
ALL_LISTS                      2013-05-24 08:29:31 2013-05-24 08:29:32              0             1
AUDIT_LOG                      2013-05-24 08:28:55 2013-05-24 08:30:17             82            17
USER_CONTACT_INFO              2013-05-24 08:29:41 2013-05-24 08:29:42              0             1
ENV_REQ_BUG                    2013-05-24 08:29:31 2013-05-24 08:29:31              0             0
PRODUCT_BUG                    2013-05-24 08:29:36 2013-05-24 08:29:36              0             0
PRODUCT_CYCL_FOLD              2013-05-24 08:29:41 2013-05-24 08:29:41              0             0
PRODUCT_CYCLE                  2013-05-24 08:29:36 2013-05-24 08:29:36              0             0
PRODUCT_REQ                    2013-05-24 08:29:36 2013-05-24 08:29:36              0             0
PRODUCT_TEST                   2013-05-24 08:29:41 2013-05-24 08:29:42              0             1
PRODUCT_TESTCYCL               2013-05-24 08:29:41 2013-05-24 08:29:41              0             0
CYCLE                          2013-05-24 08:29:41 2013-05-24 08:29:42              0             1
REQ                            2013-05-24 08:29:41 2013-05-24 08:29:41              0             0
TESTCYCL                       2013-05-24 08:29:51 2013-05-24 08:29:53              0             2
COMPONENT                      2013-05-24 08:29:36 2013-05-24 08:29:37              0             1
CYCL_FOLD                      2013-05-24 08:29:41 2013-05-24 08:29:41              0             0
DATA_REQ_BUG                   2013-05-24 08:29:25 2013-05-24 08:29:25              0             0
AUDIT_PROPERTIES               2013-05-24 08:26:16 2013-05-24 08:26:47              0            31
TEST                           2013-05-24 08:24:14 2013-05-24 08:24:56             42            25
BUG                            2013-05-24 08:29:41 2013-05-24 08:29:55              0            14
USERS                          2013-05-24 08:25:40 2013-05-24 08:25:40              0             0
GROUPS                         2013-05-24 08:27:15 2013-05-24 08:27:15              0             0

Following command can be used to perform a manual refresh

SQL> exec dbms_snapshot.refresh('MV_ADMIN.AUDIT_LOG','C');

C or c for Complete, f for Fast refresh, ? for Force Refresh (Will try Fast Refresh first and if issues will use Complete)
A or a is for Always Refresh which is same as Complete.
If we don’t specify any refresh option it uses default method used while creating this MVIEW.

Friday, May 24, 2013

Creating View againt other user's view

I thought of sharing one small issue I experienced yesterday  while I was trying to help an end user who is not that good in writing sql with hints.

This user had access to some views which are granted to him via a role and user has create view privileges.  Since his queries to this view is running for hours and he is not good in listening to my advises about using hints ( he claim his SAS tool is not passing hints. I suspect it is not correct),  I thought I could help him by creating a view in his schema so that others won’t see it and he could execute it and query will finish in less than 30 minutes instead of 8 hours or longer.

So I created a script like this and executed as a user with DBA privileges. User name obscured to hide the identity of the real user.

SQL> CREATE OR REPLACE FORCE VIEW ABCDE.DONT_CONTACT_VIEW      
(
BAN,
PCS_NUM
)
AS
SELECT /*+ PARALLEL(a,16) full(a) */ BAN, PCS_NUM
    FROM   CIRADM.CUSTOMER_PREFERENCE_VW a
    WHERE DO_NOT_CALL          = 'Y'
     OR         DO_NOT_EMAIL         = 'Y'
     OR         DO_NOT_MAIL                = 'Y'
     OR         DO_NOT_SMS           = 'Y'
     OR         DO_NOT_VOICEMAIL     = 'Y'
WITH READ ONLY;

I got following message.
Warning: View created with compilation errors.

SQL> desc ABCDE.DONT_CONTACT_VIEW;
ERROR:
ORA-24372: invalid object for describe

Then I realized Oracle’s rules on creating views on views. A user needs to have access to a view or table via direct grant  and not through ROLE privileges to create  a view on these objects . And that is the reason I am getting above message. One way to avoid this issue to grant select privileges on CIRADM.CUSTOMER_PREFERENCE_VW as follows and then execute create view script.
Grant select on CIRADM.CUSTOMER_PREFERENCE_VW to ABCDE;

Or create this view in CIRADM schema and grant select privileges to ABCDE user.

Under SQL, if a user can select another user's table and has the privilege to create a view, the create view works. Yet, a create view on the other user's table generates ORA-00942 if the select
privilege has been granted to a role and not directly. This is true for procedures too.