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.

Find out who is issuing wrong passwords which locks Oracle account using Auditing option

I get several calls on finding out how user accounts are locked and how can we track the host server and OS account name which issued wrong passwords. Here is one method of doing this if you have auditing enabled.

1)      Make sure that auditing is enabled and audit_trail is OS. You could find several documents on how to do auditing using DBA option and later querying aud$ table or checking aud file created if it is audit_trail is OS.

SQL> show parameter audit

NAME                                 TYPE        VALUE

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

audit_file_dest                      string      /audit/ppos1

audit_sys_operations                 boolean     TRUE

audit_syslog_level                   string

audit_trail                          string      OS

2)      Connect as sysdba and issue audit command

[oracle@admtrac001:/backup/arac/audit] $ sqlplus '/ as sysdba'

SQL*Plus: Release 10.2.0.4.0 - Production on Tue Mar 6 13:40:50 2012

Copyright (c) 1982, 2007, Oracle.  All Rights Reserved.

Connected to:

Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production

With the Partitioning, Real Application Clusters, OLAP, Data Mining

and Real Application Testing options

SQL>  audit session by WASADMIN by session whenever not successful;

Audit succeeded.

In 11g, you need to do “by access” instead of by session ( audit session by WASADMIN by access whenever not successful)

3)      Look unsuccessful login attempts by looking for Error Code: 1017 in *.aud files at /audit/ppos1 directory.

$ grep 'RETURNCODE\:\[4\] \"1017\"' *.aud

ora_madhu.aud:SESSIONID:[7] "8167751" ENTRYID:[1] "1" STATEMENT:[1] "1" USERID:[5] "WASADMIN" USERHOST:[10] "admtrac001" TERMINAL:[5] "pts/3" ACTION:[3] "100" RETURNCODE:[4] "1017" COMMENT$TEXT:[20] "Authenticated by: OS" OS$USERID:[6] "oracle"

4)      Stop auditing when you got enough information by connecting as SYSDBA

SQL> noaudit session by WASADMIN;

Noaudit succeeded.

So in my case it was OS user, oracle which was trying to connect as WASADMIN with wrong password.

 

Duplicating Oracle 11g database using RMAN ACTIVE BACKUP

I hope you knew that we can duplicate one Oracle 11g database to a new server using a latest RMAN option which eliminates the need to backup to tape or shared file system and restore this rman backup at new target location. In Oracle 11g, Oracle has introduced new ACTIVE DATABASE DUPLICATE option to create a copy of source database.

Assume that you have one Oracle 11.2 database named “mango” running on mango.tarikida domain and you want to restore this database to an instance named orange in a  new server named orange.tarikida. Data file systems in mango are /db0X/mango and data file systems in orange are /db0X/orange.

1)      Login to server orange.tarikida as oracle user and set env variables

oracle@orange dbs]$ . oraenv

ORACLE_SID = [orange] ?

The Oracle base remains unchanged with value /apps/oracle

2)      Create password file in $ORACLE_HOME/dbs in orange

oracle@orange dbs]$ orapwd file=orapworange password=passw0rd

3)      Start listener LISTENER_orange in server orange.

[oracle@orange dbs]$ lsnrctl start LISTENER_orange

LSNRCTL for Linux: Version 11.2.0.3.0 - Production on 05-AUG-2012 16:02:07

Copyright (c) 1991, 2011, Oracle.  All rights reserved.

Starting /apps/oracle/product/11.2.0/db/bin/tnslsnr: please wait...

TNSLSNR for Linux: Version 11.2.0.3.0 - Production

System parameter file is /apps/oracle/product/11.2.0/db/network/admin/listener.ora

Log messages written to /apps/oracle/diag/tnslsnr/orange/listener_orange/alert/log.xml

Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=orange)))

Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.168.72)(PORT=7388)))

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=orange)))

4)      Verify that you can perform tnsping to mango and orange from servers mango and orange. Please make sure that you perform this step on both servers.

oracle@orange dbs]$ tnsping mango

TNS Ping Utility for Linux: Version 11.2.0.3.0 - Production on 05-AUG-2012 16:01:45

Copyright (c) 1997, 2011, Oracle.  All rights reserved.

Used parameter files:

/apps/oracle/product/11.2.0/db/network/admin/sqlnet.ora

Used TNSNAMES adapter to resolve the alias

Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.168.71)(PORT = 7387))) (CONNECT_DATA = (SERVICE_NAME = mango.tarikida)))

OK (10 msec)

[oracle@orange admin]$ tnsping orange

TNS Ping Utility for Linux: Version 11.2.0.3.0 - Production on 05-AUG-2012 16:21:23

Copyright (c) 1997, 2011, Oracle.  All rights reserved.

Used parameter files:

/apps/oracle/product/11.2.0/db/network/admin/sqlnet.ora

Used TNSNAMES adapter to resolve the alias

Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.168.72)(PORT = 7388))) (CONNECT_DATA = (SERVICE_NAME = orange)))

OK (0 msec)

5)      Create a dummy initorange.ora file in ORACLE_HOME/dbs at server orange with just following lines. Please add correct values.

DB_NAME=orange

CONTROL_FILES=

DB_BLOCK_SIZE=

DB_FILE_NAME_CONVERT=

LOG_FILE_NAME_CONVERT=

DIAGNOSTIC_DEST=

COMPATIBLE=11.2.0

6)      Start oracle instance orange in nomount mode in server orange.

[oracle@orange dbs]$ pwd

/apps/oracle/product/11.2.0/db/dbs

[oracle@orange dbs]$ sqlplus '/ as sysdba'

SQL*Plus: Release 11.2.0.3.0 Production on Sun Aug 5 16:03:03 2012

Copyright (c) 1982, 2011, Oracle.  All rights reserved.

Connected to an idle instance.

SQL> startup nomount pfile=initorange.ora

ORACLE instance started.

Total System Global Area  263049216 bytes

Fixed Size                  2227456 bytes

Variable Size              96469760 bytes

Database Buffers          159383552 bytes

Redo Buffers                4968448 bytes

SQL> exit

Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

7)      Start rman for duplicating database

[oracle@orange dbs]$ rman

Recovery Manager: Release 11.2.0.3.0 - Production on Sun Aug 5 16:04:22 2012

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

RMAN> connect TARGET sys/passw0rd@mango;  -- mango is my source

connected to target database: MANGO (DBID=3989853245)

RMAN> connect AUXILIARY sys/passw0rd@orange;  -- orange is my new instance

connected to auxiliary database: ORANGE (not mounted)

RMAN> duplicate TARGET DATABASE TO orange FROM ACTIVE DATABASE SPFILE PARAMETER_VALUE_CONVERT 'mango','orange'  SET DB_FILE_NAME_CONVERT 'mango','orange' SET LOG_FILE_NAME_CONVERT 'mango','orange';

For a small database above task should finish in less than 15 minutes.

 

ORA-01008: not all variables bound while doing RMAN backup

Recovery Manager: Release 11.2.0.2.0 - Production on Tue Jan 17 15:00:22 2012

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

RMAN>

connected to target database: MADM (DBID=xvxvxvxv)

RMAN>

connected to recovery catalog database

RMAN> 2> 3> 4> 5> 6> 7> 8> 9>

DBGSQL:     TARGET> select  nvl(max(al.recid), '0'),nvl(max(al.recid), 0)   into  :txtparmvalue, :parmvalue   from  v$archived_log al  where  al.status in ('X', 'A')    and  al.is_recovery_dest_file = 'YES'    and  al.creator = 'RMAN'

DBGSQL:        sqlcode = 1008

allocated channel: t1

channel t1: SID=1726 device type=SBT_TAPE

channel t1: Veritas NetBackup for Oracle - Release 6.5 (2009050106)

DBGSQL:     TARGET> select  nvl(max(al.recid), '0'),nvl(max(al.recid), 0)   into  :txtparmvalue, :parmvalue   from  v$archived_log al  where  al.status in ('X', 'A')    and  al.is_recovery_dest_file = 'YES'    and  al.creator = 'RMAN'

DBGSQL:        sqlcode = 1008

released channel: t1

RMAN-00571: ===========================================================

RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============

RMAN-00571: ===========================================================

RMAN-03002: failure of backup command at 01/17/2012 15:00:36

RMAN-03014: implicit resync of recovery catalog failed

ORA-01008: not all variables bound

RMAN>

Recovery Manager complete.

I noticed that resync catalog or crosscheck archivelog all did not help in this case and this error appears due to a BUG in 11.2.0.2. You can read more about this issue at Oracle Metalink doc ID: 1280447.1

Current fix is to flush shared_pool and you will be able to start rman backup again.

SQL> alter system flush shared_pool;

ORA-29701: unable to connect to Cluster Manager while starting ASM

I prefer to install Oracle binaries using Silent Cloning method and manually create database instances using my custom scripts. 99% percent of the time I follow this method to build production databases at my work and use 1% only in case I evaluate a new version of Oracle. I need to admit that I never managed an ASM instance really and all our RAC installations are non ASM based with Veritas SFRAC as the shared disk sub system.  Since I don’t want to be left out of this ASM band wagon, last week I started building a new ASM instance from scratch without going through Oracle Installer GUI and DBCA. Everything went smooth till I mount the ASM instance. Then I came across ORA-29701. Please note that this is a non CRS server and I don't plan to run a RAC instance here.

echo $ORACLE_SID
+ASM
$ sqlplus '/ as sysdba'

SQL*Plus: Release 10.2.0.4.0 - Production on Fri Jan 6 15:42:21 2012

Copyright (c) 1982, 2007, Oracle.  All Rights Reserved.

Connected to an idle instance.

SQL> startup

ORA-29701: unable to connect to Cluster Manager

ORA-29701. Oracle uses cssd daemon for database instances which uses ASM to communicate with ASM instance. This is needed even if you don't plan to have a RAC instance or Oracle Clusterware installed on this node. Since I did not build ASM using Oracle GUI, Oracle did not add or configure cssd daemon to the OS stack. So  I need to login as root and configure it manually as follows

# cd $ORACLE_HOME/bin

# localconfig add

This step will does the task for you and you can start your ASM instance without any issues.

RMAN-06059: expected archived log not found, lost of archived log compromises recoverability

Sometimes our RMAN archive log backups hit with following RMAN-06059 error.

sent command to channel: t1

Starting backup at 06-dec-2011 11:04:32

current log archived

released channel: t1

RMAN-00571: ===========================================================

RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============

RMAN-00571: ===========================================================

RMAN-03002: failure of backup command at 12/06/2011 11:05:30

RMAN-06059: expected archived log not found, lost of archived log compromises recoverability

ORA-19625: error identifying file /arch/ppos/arch/ppos_712666108_1_20221.arc

ORA-17503: ksfdopn:4 Failed to open file /arch/ppos/arch/ppos_712666108_1_20221.arc

ORA-17500: ODM err:File does not exist

RMAN>

Here is what we could do to resolve this issue.

export ORACLE_SID=ppos1

prdposx501:oracle:ppos1:/orasw/static/oradba/bin/rman: rman target /

Recovery Manager: Release 10.2.0.4.0 - Production on Tue Dec 6 11:36:11 2011

Copyright (c) 1982, 2007, Oracle.  All rights reserved.

connected to target database: PPOS (DBID=XXXXX31740)

RMAN> connect rcvcat rman_username/password@PRRMAN

connected to recovery catalog database

RMAN> crosscheck archivelog all;