Oracle Using Query Rewrite for Data Warehouses

Oracle can use computed aggregrates and joins from materialized views to improve performance on a database query by rewriting a query against the warehouse to make use of the view. To allow this to happen the view must support query rewrite and the warehouse itself must support query rewrite. Be aware that the materialized view must be fresh, or the related integrity session parameters relaxed to allow the rewrite to happen.

To enable query rewrite on the view do
CREATE MATERIALIZED VIEW my_view_mv
ENABLE QUERY REWRITE AS
SELECT .....

To enable query rewrite ensure QUERY_REWRITE_ENABLED = TRUE for the session or the system.

Basic query rewrite documentation for Oracle 11g R2
http://docs.oracle.com/cd/E11882_01/server.112/e25554/qrbasic.htm
Advanced query rewrite documentation for Oracle 11g R2
http://docs.oracle.com/cd/E11882_01/server.112/e25554/qradv.htm

Make sure the warehouse supports the creation of the materialized view -
http://docs.oracle.com/cd/E11882_01/server.112/e25554/basicmv.htm
http://docs.oracle.com/cd/E11882_01/server.112/e25554/advmv.htm

Oracle Audit Vault Database Firewall 12.1 Errors with network card on Oracle Virtual Machine

To get round a problem with Oracle Audit Vault Database Firewall appliance not finding the network interface after completing the initial configuration it is necessary to change the type of network of the NIC. The problem is seen when AVDF boots for the first time after the install, a screen is shown for selecting a management interface, even though this has been configured already, the NIC hardware address shows as 00:00:00:00:00:00 instead of the valid address. Dropping to a console and searching for the hardware device shows no Ethernet controller. If there are multiple NICs defined they will all be listed as 00:00:00:00:00:00.

For how to debug audit vault in the console look at http://notastrophe.blogspot.com/2013/06/oracle-audit-vault-database-firewall.html .
The command lspci | grep -i Ether xx shows no results, and ifconfig -a shows eth0 with HWADDR of 00:00:00:00:00:00

To get past this problem its necessary to use Fully Virtualized network configuration in OVM for the initial install. Then after the first reboot (just after the IP Config screen), shutdown the VM and change to Paravirtualized networking. Trying to do the install with Paravirtualized networking will fail because the install detects no interfaces. A Fully Virtualized configuration shows on OVM 2.0 as "ioemu" in the Network Interface column on the Network configuration page, Paravirtualized configuration shows as "netfront".

Oracle Audit Vault Database Firewall 12.1 run-level hot key

During Oracle Audit Vault Database Firewall 12.1 install you can change from the default interface to view the console (and any error messages) by using a hot-key and another key to get the interface back. From the console you can quit the running service to get to the management interface.
This helps a lot to debug Oracle AVDF install problems or errors.


To change from the interface to the console hit alt+F9
To revert hit alt+F1.
To skip over services that are hung hit ctrl+c from the console.

Once the management interface is up you can drop to a login using ctrl+alt+f2.
Login is root or support. Make sure you've set root and support passwords from the management interface first