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';

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 11G Shrinking Temp Tablespace

There are a few methods to shrink the temporary tablespace in Oracle 11G. In some cases you can directly resize or shrink the tablespace. If that is not possible then create a new temporary tablespace, reassign the default temp space and drop the old tablespace.


SELECT tablespace_name, file_name, bytes
FROM dba_temp_files WHERE tablespace_name like 'TEMP%';


alter database tempfile '/the/full/path/to/temp01.dbf' resize 256M;
alter database tempfile '/the/full/path/to/temp01.dbf' resize 256M

-- Can give -
ERROR at line 1:
ORA-03297: file contains used data beyond requested RESIZE value

-- If that fails, on 11G do -
alter tablespace TEMP shrink space keep 256M;

-- If that fails do

CREATE TEMPORARY TABLESPACE temp2
TEMPFILE '/datapath/temp2_01.dbf' SIZE 5M REUSE
AUTOEXTEND ON NEXT 1M MAXSIZE unlimited
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M;

 ALTER DATABASE DEFAULT TEMPORARY TABLESPACE temp2;

DROP TABLESPACE temp INCLUDING CONTENTS AND DATAFILES;

CREATE TEMPORARY TABLESPACE temp
2  TEMPFILE '/datapath/temp01.dbf' SIZE 256M REUSE
3  AUTOEXTEND ON NEXT 128M MAXSIZE unlimited
4  EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M;

ALTER DATABASE DEFAULT TEMPORARY TABLESPACE temp;

DROP TABLESPACE temp2 INCLUDING CONTENTS AND DATAFILES;

From http://stackoverflow.com/questions/1824572/how-to-shrink-temp-tablespace-in-oracle

Disable Password expiry for Oracle Accounts

To disable password expiry for Oracle accounts find whihc profile is in use and then change the default timeout on the profile. If the users are already expired and locked the password must be reset and the account unlocked.


select * from dba_users; -- find the users and which profile they use

-- change the DEFAULT profile to not expire
ALTER PROFILE DEFAULT LIMIT
  PASSWORD_LIFE_TIME UNLIMITED;

-- could also add FAILED_LOGIN_ATTEMPTS UNLIMITED

Oracle 11gR2 Audit Trail Cleanup scripts

Initialise the Cleanup -
Do this once.
Watch out for tablespace problems in SYSAUX tablespace. Even if SYSAUX has auto extend ON

BEGIN
DBMS_AUDIT_MGMT.INIT_CLEANUP(
AUDIT_TRAIL_TYPE => DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD,
DEFAULT_CLEANUP_INTERVAL => 12 );
END;
/

Set a clean_up batch size

BEGIN
DBMS_AUDIT_MGMT.SET_AUDIT_TRAIL_PROPERTY(
AUDIT_TRAIL_TYPE => DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD,
AUDIT_TRAIL_PROPERTY => DBMS_AUDIT_MGMT.DB_DELETE_BATCH_SIZE,
AUDIT_TRAIL_PROPERTY_VALUE => 100000);
END;
/

Run a manual audit trail cleanup
BEGIN
DBMS_AUDIT_MGMT.CLEAN_AUDIT_TRAIL(
AUDIT_TRAIL_TYPE => DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD,
USE_LAST_ARCH_TIMESTAMP => TRUE );
END;
/

Schedule a purge job

BEGIN
DBMS_AUDIT_MGMT.CREATE_PURGE_JOB (
AUDIT_TRAIL_TYPE => DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD,
AUDIT_TRAIL_PURGE_INTERVAL => 12,
AUDIT_TRAIL_PURGE_NAME => 'Standard_Audit_Trail_PJ',
USE_LAST_ARCH_TIMESTAMP => TRUE );
END;
/

purge interval is in hours

Oracle 11gR2 Disabling Fast Recovery Area (FRA)

Disabling the Fast Recovery Area

If Flashback Database is enabled, then disable it before you disable the fast recovery area.
ALTER DATABASE FLASHBACK OFF;
If you are using fast recovery area for archive logs, then set the initialization parameter LOG_ARCHIVE_DEST_n to use a non-fast recovery area location.
eg.
LOG_ARCHIVE_DEST_1='LOCATION=USE_DB_RECOVERY_FILE_DEST'
ALTER SYSTEM SET LOG_ARCHIVE_DEST_1='LOCATION=/ORACLE/DBS/';

Disable the fast recovery area initialization parameter.
ALTER SYSTEM SET DB_RECOVERY_FILE_DEST='';

(From Oracle Database Backup and Recovery User's Guide)

Oracle ORA-00904 "ORA_ROWSCN": invalid identifier

Oracle ORA-00904 "ORA_ROWSCN": invalid identifier occurs when querying a table with Fine Grained Auditing (FGA) Policy defined using sqldeveloper.

The fix is to go to preferences -> database -> Object Viewer and deselect the checkbox for use ROWSCN

Shrinking UNDO Tablespace in Oracle 11gR2

To shrink UNDO tablespace in Oracle first create the new undo tablespace, then alter the database to use the new undo tablespace. This will require enough available disk space to create the new tablspace. Then drop the old tablespace to free up the disk. Commands are provided below

CREATE UNDO TABLESPACE undotbs2
DATAFILE '/u02/datafile/undotbs02.dbf'
         SIZE 50M AUTOEXTEND ON NEXT 50M;

Tablespace created.



ALTER SYSTEM SET UNDO_TABLESPACE=UNDOTBS2 SCOPE=BOTH;
System altered.

DROP TABLESPACE undotbs1 INCLUDING CONTENTS AND DATAFILES;

Recovering Oracle Enterprise Manager with Audit Vault Console after disk full.

On RHEL5 with Oracle 11gR2 Audit Vault Server if hard disk fills up then the Oracle Enterprise Manager (OEM) server.xml file has all its contents removed and the dbconsole won't start.

To fix do -

Disable Database Vault
(From Oracle Database Vault Admin Guide 11gR2)

stop the dbconsole if it is up.

sqlplus sys as sysoper

SHUTDOWN NORMAL;
EXIT


cd $ORACLE_HOME/rdbms/lib
make -f ins_rdbms.mk dv_off ioracle

sqlplus sys as sysoper

STARTUP

Recreate the EM
export $ORACLE_HOSTNAME=[host]
make sure SYS, SYSMAN, DBSNMP users have CREATE SESSION grant

emca -deconfig dbcontrol db -repos drop
emca -config dbcontrol db -repos create

Then log in to Enterprise Manager to verify it is recreated correctly.

Recreate the Audit Vault app.
avca deploy_av
This updates OC4J config files and then errors
avca redeploy

## Had to deploy_av again, using
avca deploy_av -sid <sid> -dbalias <sid>

eg
avca deploy_av -sid av.host.com -dbalias av -avconsoleport 5500

Otherwise avca start_av and other avca commands returned "Internal Error".
Also avorcldb add_source -help returned an error saying add_source command is invalid.

Then log in to av console at [emhost]:[em_port]/av

Now go and find all server.xml files and create a backup of them.