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.

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

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.

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.