Using SSH to forward ports

Ports on remote machines can be forwarded over a SSH tunnel using the ssh command line tool. Using the ssh config file it is possible to set up many ports to be forwarded at the same time running one command. Putty can be used to achieve similar results on Windows.

To forward a port the syntax is the ssh command followed by the local port then the local host then remote port and host
ssh -L 8080:me.local.org:80 myuser@s1.remote.org

To forward many ports at once created a config file in ~/.ssh/config as follows

host localme
  HostName s1.remote.org
  User myuser
  LocalForward 8080 me.local.org 80
  LocalForward 8443 me.local.org 443

Then run ssh localme and give the password for myuser

Oracle ORA-02082 creating database link

Creating a database link in oracle gives the error -
create database link orcl connect to myuser identified by oracle using 'orcl';


SQL Error: ORA-02082: a loopback database link must have a connection qualifier
02082. 00000 -  "a loopback database link must have a connection qualifier"
*Cause:    attempt to create a database link with the same name as the
           current database.
*Action:   a loopback database link needs a trailing qualifier, e.g.
           MYDB.DEV.US.ORACLE.COM@INST1  - the '@INST1' is the qualifier

To fix use
create database link orcl@loopback connect to myuser identified by oracle using 'orcl';

Querying Oracle for Fine Grained Auditing settings

An earlier post about Fine Grained Auditing shows how to enable FGA in an Oracle 11gR2 database - http://notastrophe.blogspot.com/2013/01/oracle-11gr2-fine-grained-auditing-fga.html . But how do you tell what FGA policies are applied. Typically to query audit settings you query DBA_STMT_AUDIT_OPTS, DBA_PRIV_AUDIT_OPTS and DBA_OBJ_AUDIT_OPTS, and these are shown in OEM's audit page and can be queried from there, but FGA requires a different query -

SELECT PNAME, PCOL,  FROM SYS.FGA$;

Gives the policy name and affected columns.

SELECT DBUID, CLIENTID, POLICYNAME FROM SYS.FGA_LOG$;

Gives audit trail of user, client id and audited policy. Can also get query details and object schema and name

Query against the regular audit log can be done against DBA_AUDIT_TRAIL and SYS.AUD$

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".