STEPS TO USE LOGMINER

Many of us are curious to know about remote mining. Source database mining in production database you might avoid. So remote mining comes. Remote mining means the database that analyze of redo log files or archived redo log files is not same as of source database- that generate redo/archived log files.

The system of using logminer in remote database is transfer the analysis redo or archived redo log files as well as tranfer the mining dictionary to the remote database.

In the following section I demonstrate an step by step idea to do the task.
Scenario: Online redo log file redo01 need to be analyzed in remote machine jupiter.
Step1 -Extract logminer dictionary: (On Source Database)
In source that is in production server build the dictionary by,
SQL>EXECUTE DBMS_LOGMNR_D.BUILD(OPTIONS=> DBMS_LOGMNR_D.STORE_IN_REDO_LOGS);
PL/SQL procedure successfully completed.

After executing this procedure in archive log mode database adds the dictionary information to the online redo logs, which, in turn, go into the archived logs.

This redo log file must have been created before the redo log file that you want to analyze, but should be as recent as possible.

You can find out which redo log files contain the start and end of an extracted dictionary. To do so, query the V$ARCHIVED_LOG view, by,
SQL> SELECT NAME FROM V$ARCHIVED_LOG WHERE DICTIONARY_BEGIN='YES';
NAME
--------------------------------------------------------------------------------
/oradata1/ARJU/archivelog/2008_08_24/o1_mf_1_1284_4c23hcd6_.arc

SQL> SELECT NAME FROM V$ARCHIVED_LOG WHERE DICTIONARY_END='YES';
NAME
--------------------------------------------------------------------------------
/oradata1/ARJU/archivelog/2008_08_24/o1_mf_1_1284_4c23hcd6_.arc

Step 2- Transfer the logminer dictionary and log for analysis into the mining database.

SQL> !scp /oradata1/ARJU/archivelog/2008_08_24/o1_mf_1_1284_4c23hcd6_.arc oracle@jupiter:
Password:
o1_mf_1_1284_4c23hcd 100% |*************************************************| 9465 KB 00:01

Then also transfer the redo log. Based on your requirement you can transfer archived log or online redo log. To see a defined time archived log query by select NAME from v$archived_log where completion_time >SYSDATE-1;
In this example I will analysis online redo log file.

SQL> !scp /oradata1/arju/ARJU/redo01.log oracle@jupiter:
Password:
redo03.log 100% |*************************************************| 51200 KB 00:08


Step 3- Specify the files for analysis.(In mining/remote database)

Here specify the logfile that need to mine plus specify the dictionary files.
SQL>EXECUTE DBMS_LOGMNR.ADD_LOGFILE( LOGFILENAME => '/export/home/oracle/redo01.log', OPTIONS => DBMS_LOGMNR.ADDFILE);

SQL>EXECUTE DBMS_LOGMNR.ADD_LOGFILE( LOGFILENAME => '/export/home/oracle/o1_mf_1_1284_4c23hcd6_.arc', OPTIONS => DBMS_LOGMNR.ADDFILE);

Step 4- Start the logminer session(In mining database)

Don't specify here DICTFILENAME option. Execute the following,
SQL> EXECUTE DBMS_LOGMNR.START_LOGMNR(OPTIONS => DBMS_LOGMNR.DICT_FROM_REDO_LOGS);

Step 5- Query V$LOGMNR_CONTENTS:
You can follow for query as in How to use Oracle Logminer to analysis Logfile

Step 6- End the LogMiner Session:
EXECUTE DBMS_LOGMNR.END_LOGMNR;

Related Post:- 

• How Oracle Logminer is used to analysis logfile
• Logminer Fails with ORA-01284, ORA-00308 

Read More

Truncate Or Delete Rows From Audit Trail Table SYS.AUD$

1)Only appropriate privileged user can do delete operation on SYS.AUD$ table. The user must have either of the following privileges.
-SYS user.
-DELETE ANY TABLE system privilege. (If O7_DICTIONARY_ACCESSIBILITY=TRUE)
-A user to whom SYS has granted the object privilege DELETE on SYS.AUD$ table.

2)Before deleting any rows you may want to archive the table. You can achive this by creating a table from SYS.AUD$ and export that. Don't export SYS.AUD$ directly.
SQL>CREATE TABLE AUDIT_RECORD TABLESPACE users as select * from SYS.AUD$;
Now export the table as,
SQL> host exp tables=AUDIT_RECORD file=audit_record.dmp

3)To delete all records from audit trail table SYS.AUD$ issue,
SQL>DELETE FROM SYS.AUD$;

To delete all records of particular audited table from the audit trail issue,
SQL>DELETE FROM sys.aud$ WHERE obj$name='&table_nmae';

But deleting in this way will not reduce size on the system tablespace or aud$ table. In order to reduce size follow section 4.

4)Truncate audit table to reduce size.
SQL>CONN / as sysdba
SQL>TRUNCATE TABLE sys.aud$;

Truncate uses the DROP STORAGE clause but keeps only minextents extents, thus only 1 extent

Related Post:-

• Basic Database Auditing
• Moving SYS.AUD$ To Another Tablespace
• How To MOve Audit Table Out Of  SYSTEM Tablespace
• How To Reorganize Audit Trail SYS.AUD$ Table

Read More

How To Reorganize Audit Trail AUD.SYS$ Table

You may want to reorganize your auditing table if you optionally delete records from it regularly. In the following steps it is described.
1)Enable restricted session.
In order to ensure consistency in the auditing table temporary disable auditing activity. You can do this by opening database with STARTUP RESTRICT or during open make the system as restricted session.

SQL> connect / as sysdba
Connected.

SQL> alter system enable restricted session;
System altered.

Check if sessions are still connected by,
SQL> select sid, serial#, username from v$session;

If necessary kill these sessions with,
SQL> alter system kill session 'sid , serial#';

2)Copy SYS.AUD$ table.
SQL>CREATE TABLE audit_record TABLESPACE USERS as select * from SYS.AUD$;
You now can take a dump of audit_record.

3)Truncate SYS.AUD$ table.
SQL> truncate table sys.aud$;
Table truncated.

SQL> select count(*) from SYS.AUD$;
COUNT(*)
--------
0

4)Copy the rows back to SYS.AUD$.
SQL> insert into sys.aud$ select * from audit_record;

You can also import it if you exported it in step 2.

5)Drop the audit_record table(optional).
SQL>DROP TABLE audit_record;

Related Post:-

• Basic Database Auditing
• Moving SYS.AUD$ To Another Tablespace
• How To MOve Audit Table Out Of  SYSTEM Tablespace
• Truncate or Delete Rows From Audit Trail Tablespace

Read More

Move Audit Table Out Of SYSTEM Tablespace

Oracle strongly recommended to use DBMS_AUDIT_MGMT.SET_AUDIT_TRAIL_LOCATION
provided with the DBMS_MGMT package in order to move audit trail table out of SYSTEM tablespace. But the procedure SET_AUDIT_TRAIL_LOCATION by default is not available until 11g. It is available in 11g.

However with manual script you can move SYS.AUD$ table out of SYSTEM tablespace. But you need to remember moving AUD$ out of SYSTEM tablespace is not a
supported procedure. Oracle does not support changing ownership of AUD$, or any
triggers on it.

Below is the scripts that you can do as your own risk,
Step 01: Connect to database as SYS user.
conn / as sysdba

Step 02: Create tablespace where audit file will reside.
create tablespace AUDIT_TBS
datafile '/oradata2/datafile/aud01.dbf' size 10M autoextend on maxsize unlimited;

Step 03: Create audit table inside AUDIT_TBS
create table aud_aux tablespace AUDIT_TBS
as select * from aud$ where 1 = 2;
Note that no rows will be created in this state.

Step 04: Rename the original Audit table.
rename AUD$ to AUD$$;

Step 05: Rename the aud_aux to AUD$
rename aud_aux to aud$;

Step 06: Create Index on the AUD$ table.
create index aud_i
on aud$(sessionid, ses$tid)
tablespace AUDIT_TBS;

Related Post:-

• Basic Database Auditing
• Moving SYS.AUD$ To Another Tablespace
• How To Reorganize Audit Trail SYS.AUD$ Table
• Truncate or Delete Rows From Audit Trail Tablespace

Read More

Moving SYS.AUD$ To Another Tablespace

Oracle stores audit trail records in the SYS.AUD$ base data dictionary table which resides in SYSTEM tablespace.

Based on the auditing option this SYS.AUD$ table grows out of order inside the SYSTEM tablespace and must have records deleted from it or be truncated, otherwise it will take up all the room in the SYSTEM tablespace. This deleting and truncating of the SYS.AUD$ table will fragment the SYSTEM tablespace.

So it may be one's choice to move the audit table out of the SYSTEM tablespace and then enable auditing without touching SYSTEM tablespace.

Until 11g the way to move out SYS.AUD$ is not supported. If you want to do it you have to do it manually which is discussed in How to move audit table out of SYSTEM tablespace which is not supported. 'Not supported' means Oracle Support cannot involve development if problems arise because of the triggers you put on the AUD$ table.

In 11g you can do it by the DBMS_AUDIT_MGMT.SET_AUDIT_TRAIL_LOCATION provided with the DBMS_MGMT package.

Related Post :- 

• Basic Database Auditing 
• How To Move Audit Table Out Of SYSTEM Tablespace
• How To Reorganize Audit Trail SYS.AUD$ Table
• Truncate or Delete Rows From Audit Trail Tablespace

Read More

Basic Database Auditing

Auditing

Auditing is the monitoring and recording of selected user database actions. It can be based on individual actions, such as the type of SQL statement executed, or on combinations of factors that can include user name, application, time, and so on. Security policies can trigger auditing when specified elements in an Oracle database are accessed or altered, including the contents within a specified object.

Why we need it?

Enable future accountability for current actions taken in a particular schema, table, or row, or affecting specific content.
Deter users (or others) from inappropriate actions based on that accountability.
Investigate suspicious activity.
Notify an auditor that an unauthorized user is manipulating or deleting data and that the user has more privileges than expected which can lead to reassessing user authorizations.
Monitor and gather data about specific database activities.
Detect problems with an authorization or access control implementation.
For example, you can create audit policies that you expect will never generate an audit record because the data is protected in other ways. However, if these policies do generate audit records, then you will know the other security controls are not properly implemented.

Oracle allows audit options to be focused or broad, enabling you to audit the following:

Successful statement executions, unsuccessful statement executions, or both.
Statement executions once in each user session or once every time the statement is executed.
Activities of all users or of a specific user

Auditing Types

1.Statement Auditing.(DDL statement and DML statements)
2.Privilege Auditing(system privilege)
3.Schema Object Auditing
4.Fine-Grained Auditing(Monitor Data Acess based on Content)

Audit Records and Audit Trails

Audit records include information about the operation that was audited, the user performing the operation, and the date and time of the operation. Audit records can be stored in either a data dictionary table, called the database audit trail, or in operating system files, called an operating system audit trail.

The two general types of auditing are standard auditing, which is based on privileges, schemas, objects, and statements, and fine-grained auditing. Standard audit records can be written either to DBA_AUDIT_TRAIL (the sys.aud$ table) or to the operating system. Fine-grained audit records are written to DBA_FGA_AUDIT_TRAIL (the sys.fga_log$ table) and the DBA_COMMON_AUDIT_TRAIL view, which combines standard and fine-grained audit log records.

When Audit Records Created?

SQL statements inside PL/SQL program units are individually audited, as necessary, when the program unit is executed.

The generation and insertion of an audit trail record is independent of a user transaction being committed. That is, even if a user transaction is rolled back, the audit trail record remains committed.

Statement and privilege audit options in effect at the time a database user connects to the database remain in effect for the duration of the session. Setting or changing statement or privilege audit options in a session does not take effect in that session. The modified statement or privilege audit options take effect only when the current session ends and a new session is created.

In contrast, changes to schema object audit options become effective for current sessions immediately.

Operations by the SYS user and by users connected through SYSDBA or SYSOPER can be fully audited with the AUDIT_SYS_OPERATIONS initialization parameter. Every successful SQL statement from SYS is audited. This specialized form of auditing audits all actions performed by every user with the SYSDBA privilege and writes only to an operating system location. It is not dependent on the standard auditing parameter, AUDIT_TRAIL.

Related Post:- 

• Moving SYS.AUD$ To Another Tablespace
• How To Move Audit Table Out Of SYSTEM Tablespace
• How To Reorganize Audit Trail SYS.AUD$ Table
• Truncate or Delete Rows From Audit Trail Tablespace

Read More

Examples Of Different Type Of View

Creating a View: Example The following statement creates a view of the sample table employees named emp_view. The view shows the employees in department 20 and their annual salary:

CREATE VIEW emp_view AS
   SELECT last_name, salary*12 annual_salary
   FROM employees
   WHERE department_id = 20;

The view declaration need not define a name for the column based on the expression salary*12, because the subquery uses a column alias (annual_salary) for this expression. 

Creating a View with Constraints: Example The following statement creates a restricted view of the sample table hr.employees and defines a unique constraint on the email view column and a primary key constraint for the view on the emp_id view column:

CREATE VIEW emp_sal (emp_id, last_name,
      email UNIQUE RELY DISABLE NOVALIDATE,
   CONSTRAINT id_pk PRIMARY KEY (emp_id) RELY DISABLE NOVALIDATE)
   AS SELECT employee_id, last_name, email FROM employees;

 
Creating an Updatable View: Example The following statement creates an updatable view named clerk of all clerks in the employees table. Only the employees' IDs, last names, department numbers, and jobs are visible in this view, and these columns can be updated only in rows where the employee is a kind of clerk:

CREATE VIEW clerk AS
   SELECT employee_id, last_name, department_id, job_id
   FROM employees
   WHERE job_id = 'PU_CLERK'
      or job_id = 'SH_CLERK'
      or job_id = 'ST_CLERK';

This view lets you change the job_id of a purchasing clerk to purchasing manager (PU_MAN):

UPDATE clerk SET job_id = 'PU_MAN' WHERE employee_id = 118;

Create same view WITH CHECK OPTION

CREATE VIEW clerk AS
   SELECT employee_id, last_name, department_id, job_id 
   FROM employees
   WHERE job_id = 'PU_CLERK' 
      or job_id = 'SH_CLERK' 
      or job_id = 'ST_CLERK'
   WITH CHECK OPTION;

Creating a Join View: Example  A join view is one whose view subquery contains a join. If at least one column in the join has a unique index, then it may be possible to modify one base table in a join view. You can query USER_UPDATABLE_COLUMNS to see whether the columns in a join view are updatable. For example:

CREATE VIEW locations_view AS
   SELECT d.department_id, d.department_name, l.location_id, l.city
   FROM departments d, locations l
   WHERE d.location_id = l.location_id;

SELECT column_name, updatable 
   FROM user_updatable_columns
   WHERE table_name = 'LOCATIONS_VIEW';
COLUMN_NAME                    UPD
------------------------------ -------------------
DEPARTMENT_ID                  YES
DEPARTMENT_NAME           YES
LOCATION_ID                        NO
CITY                                       NO

In the preceding example, the primary key index on the location_id column of the locations table is not unique in the locations_view view. Therefore, locations is not a key-preserved table and columns from that base table are not updatable.

INSERT INTO locations_view VALUES
   (999, 'Entertainment', 87, 'Roma');
INSERT INTO locations_view VALUES
*
ERROR at line 1:
ORA-01776: cannot modify more than one base table through a join view
 

Creating a Read-Only View: Example The following statement creates a read-only view named customer_ro of the oe.customers table. Only the customers' last names, language, and credit limit are visible in this view:
CREATE VIEW customer_ro (name, language, credit)
      AS SELECT cust_last_name, nls_language, credit_limit
      FROM customers
      WITH READ ONLY;

 
Creating an Object View: Example The following example shows the creation of the type inventory_typ in the oc schema, and the oc_inventories view that is based on that type:
CREATE TYPE inventory_typ
 OID '82A4AF6A4CD4656DE034080020E0EE3D'
 AS OBJECT
    ( product_id          NUMBER(6)
    , warehouse           warehouse_typ
    , quantity_on_hand    NUMBER(8)
    ) ;
/
CREATE OR REPLACE VIEW oc_inventories OF inventory_typ
 WITH OBJECT OID (product_id)
 AS SELECT i.product_id,
           warehouse_typ(w.warehouse_id, w.warehouse_name, w.location_id),
           i.quantity_on_hand
    FROM inventories i, warehouses w
    WHERE i.warehouse_id=w.warehouse_id; 

Read More