MATERIALED VIEW --- ANALYZE MVIEWS

 ANALYZE MVIEWS

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

  

To analyzes the Materialized View to find out if it can use fast refresh, 

use the DBMS_MVIEW.EXPLAIN_MVIEW procedure which uses table MV_CAPABILITIES_TABLE.

1. Execute utlxmv.sql to create the MV_CAPABILITIES_TABLE .

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

You must run the script in the same schema of the materialized view.

SQL> @$ORACLE_HOME/rdbms/admin/utlxmv.sql

2. Execute the DBMS_MVIEW.EXPLAIN_MVIEW procedure using the select statement of create Materialized View.

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

SQL> EXEC DBMS_MVIEW.EXPLAIN_MVIEW ('<Use SELECT statement of the materialized view >');

Or if you have created the materialized view, use the materialized view name.

SQL> EXEC DBMS_MVIEW.EXPLAIN_MVIEW ('< Materialized view name>');

3. Run the following query to find out if you can use the fast refresh and if not why cen be found in the MSGTXT column.

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

SQL> set linesize 220

SQL> SELECT SUBSTR(capability_name,1,30)AS capability_name ,  possible, SUBSTR(related_text,1,10)AS related_text,

 SUBSTR(msgtxt,1,90)AS msgtxt FROM MV_CAPABILITIES_TABLE where capability_name like 'REFRESH%'ORDER BY seq;

Example:

~~~~~~~~~~

SQL> @ORACLE_HOME/rdbms/admin/utlxmv.sql

SQL> EXEC DBMS_MVIEW.EXPLAIN_MVIEW ('SELECT * FROM <USER_NAME>.<Table_name>');

SQL> set linesize 220

SQL> SELECT SUBSTR(capability_name,1,30)AS capability_name ,  possible, SUBSTR(related_text,1,10)AS related_text,

 SUBSTR(msgtxt,1,90)AS msgtxt FROM MV_CAPABILITIES_TABLE where capability_name like 'REFRESH%'ORDER BY seq;

 

CAPABILITY_NAME                P RELATED_TEXT     MSGTXT

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

REFRESH_COMPLETE               Y

REFRESH_FAST                   N

REFRESH_FAST_AFTER_INSERT      N <USER_NAME>.<Table_name>       the detail table does not have a materialized view log

REFRESH_FAST_AFTER_ONETAB_DML  N                 see the reason why REFRESH_FAST_AFTER_INSERT is disabled

REFRESH_FAST_AFTER_ANY_DML     N                 see the reason why REFRESH_FAST_AFTER_ONETAB_DML is disabled

REFRESH_FAST_PCT               N                 PCT is not possible on any of the detail tables in the materialized view

6 rows selected.

ora-12026 invalid filter column detected in materialized VIEW

 ora-12026 invalid filter column detected

Error code: ORA-12026

Description: invalid filter column detected

Cause: One or more of the specified filter columns did not exist or was a primary key column or a primary key based object identifier.

Action: Ensure that all specified filter columns exist in the master table and ensure that primary key columns or primary key based object identifiers are not included 

        in the list of filter columns.

Solution: Remove the columns other than the PK columns from sequence list

          and DO complete refresh of the MVIEW

  

DBCA and DBUA are unable to start in Oracle 12.1.0.2 on IBM AIX

DBCA and DBUA are unable to start in Oracle 12.1.0.2  /12.2.0.1 on IBM AIX

Problem Description:

Today I performed a new installation of Oracle 12.1.0.2 on IBM AIX 7.1 in a single instance environment. After a successful Oracle Software instllation I tried to execute "dbca" utility (Database Configuration Assistant) in order to create the database and I recevied the following error:

$ ./dbca
UnsatisfiedLinkError exception loading native library: njni12
java.lang.UnsatisfiedLinkError: njni12 (rtld: 0712-001 Symbol CreateIoCompletionPort was referenced
from module /oravl01/oracle/app/product/12.1.0.2/dbhome_1/lib/libnjni12.so(), but a runtime definition
of the symbol was not found.
rtld: 0712-001 Symbol GetMultipleCompletionStatus was referenced
from module /oravl01/oracle/app/product/12.1.0.2/dbhome_1/lib/libnjni12.so(), but a runtime definition
of the symbol was not found.
rtld: 0712-002 fatal error: exiting.)
Exception in thread "main" java.lang.UnsatisfiedLinkError: oracle/net/common/NetGetEnv.get(Ljava/lang/String;)Ljava/lang/String;
at oracle.net.config.Config.getNetDir(Unknown Source)
at oracle.net.config.Config.initConfig(Unknown Source)
at oracle.net.config.Config.(Unknown Source)
at oracle.sysman.assistants.util.NetworkUtils.(NetworkUtils.java:298)
at oracle.sysman.assistants.util.step.StepContext.(StepContext.java:388)
at oracle.sysman.assistants.dbca.backend.Host.(Host.java:1055)
at oracle.sysman.assistants.dbca.backend.Host.(Host.java:1048)
at oracle.sysman.assistants.dbca.wizard.UIHost.(UIHost.java:241)
at oracle.sysman.assistants.dbca.wizard.InteractiveHost.getInstance(InteractiveHost.java:78)
at oracle.sysman.assistants.dbca.Dbca.getHost(Dbca.java:208)
at oracle.sysman.assistants.dbca.Dbca.execute(Dbca.java:121)
at oracle.sysman.assistants.dbca.Dbca.main(Dbca.java:226)
$ smit aio
Warning: Missing charsets in String to FontSet conversion
Warning: Cannot convert string "-dt-interface system-medium-r-normal-l*-*-*-*-*-*-*-*" to type FontSet
$

After an investigation in MOS (My Oracle Support) I found the following article:

AIX 12.1.0.2 Installation Fails with "rtld: 0712-001 Symbol CreateIoCompletionPort was referenced " (Doc ID 1949184.1)

What I found in the article is that this issue is a known issue for Oracle 12.1.0.2 in IBM AIX on POWER Systems (64-bit).

SOLUTION:

The solution, as described in Doc ID 1949184.1 is actually a very simple one, all you need to do is enable to IOCP (I/O Completion Ports). The steps to do that are:

1.

Log in as root and run the following command:
# smitty iocp
Select Change / Show Characteristics of I/O Completion Ports.

2. 
Change configured state at system restart from Defined to Available.
Restart the machine

3.
Run the lsdev command to confirm the IOCP status is set to Available:

$ lsdev | grep iocp
iocp0 Available I/O Completion Ports

4.
Relink Oracle Home. In order to do that, connect with the Oracle software owner user and then:

Set the ORACLE_HOME environment variable

Execute $ORACLE_HOME/bin/relink all

5. 
That's it ! Now the "dbca" and "dbua" runs successfuly 

Oracle Binaries installation failed with Error in CreateOUIProcess(): 13 : Permission denied

Oracle Binaries  installation failed with Error in CreateOUIProcess(): 13 : Permission denied


Error in CreateOUIProcess(): 13 : Permission denied”


CAUSE
~~~~~~~~~~

/tmp directory is mounted with noexec option.


You can verify this with following command:

mount
on /tmp type ext3 (rw,noexec,nosuid,nodev)

SOLUTION
~~~~~~~~~~~~~
1) Consult your System Administrator and Remove “noexec” on /tmp folder
For example, to temporarily disable the noexec flag:

mount -o remount,exec /tmp
 or

2) Redirect temp directory to other location which has exec and later retry installation

Example-

export TMP=/u01/oracle
export TMPDIR=$TMP



Oracle support reference document (Doc ID 1671840.1)

Enabling Flashback Database and checking the status

Enabling Flashback Database and checking the status
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Flashback status of a database can be checked from the below query and system parameters.
-          SQL> select NAME,FLASHBACK_ON from v$database; -          SQL> archive log list -          SQL> show parameter undo_retention -          SQL> show parameter DB_FLASHBACK_RETENTION_TARGET -          SQL> show parameter  db_recovery_file_dest -          SQL> show parameter  db_recovery_file_dest_size

Enabling Flashback Database
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
1. you can either revert the entire database to a prior point in time by restoring a backup and doing point-in-time recovery,
2. or you can enable Flashback Database.

When you enable Flashback Database, the database generates flashback logs in the fast recovery area.
These logs are used to flash back the database to a specified time.
During usual operation, the database occasionally logs images of data blocks to the flashback logs.
The database automatically creates, deletes, and resizes flashback logs.


command to check if Flashback Database is enabled for your target database:
SELECT FLASHBACK_ON FROM V$DATABASE;

To enable Flashback Database:
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Ensure that you configure a fast recovery area and that the database is running in ARCHIVELOG mode.


Connect Oracle Recovery Manager (RMAN) to the target database

Optionally, specify the length of the desired flashback window (in minutes) by setting the DB_FLASHBACK_RETENTION_TARGET initialization parameter.
The default value for this parameter is 1440 minutes, which is one day.
The following command specifies that the flashback window must be 3 days.


ALTER SYSTEM SET DB_FLASHBACK_RETENTION_TARGET=4320;

Enable the Flashback Database feature for the whole database using the following command:

ALTER DATABASE FLASHBACK ON;

You can also execute the commands in this section by connecting to the target database using SQL*Plus instead of RMAN.