Insights and Strategies for DBA

Thursday, 9 August 2012

Log Miner

LogMiner Utility THROUGH read the online redolog files & archive file for the purpose of audit ,these file contain the data script

Using Log Miner utility, you can query the contents of online redo log files and archived log files. Because LogMiner provides a well-defined, easy-to-use, and comprehensive relational interface to redo log files, it can be used as a powerful data audit tool, as well as a tool for sophisticated data analysis.

LogMiner Configuration

There are three basic objects in a LogMiner configuration that you should be familiar with: the source database, the LogMiner dictionary, and the redo log files containing the data of interest:

The source database is the database that produces all the redo log files that you want LogMiner to analyze.

The LogMiner dictionary allows LogMiner to provide table and column names, instead of internal object IDs, when it presents the redo log data that you request.

LogMiner uses the dictionary to translate internal object identifiers and datatypes to object names and external data formats. Without a dictionary, LogMiner returns internal object IDs and presents data as binary data.

For example, consider the following the SQL statement:

INSERT INTO HR.JOBS(JOB_ID, JOB_TITLE, MIN_SALARY, MAX_SALARY) VALUES('IT_WT','Technical Writer', 4000, 11000);

Without the dictionary, LogMiner will display:

insert into "UNKNOWN"."OBJ# 45522"("COL 1","COL 2","COL 3","COL 4") values

(HEXTORAW('45465f4748'),HEXTORAW('546563686e6963616c20577269746572'),

HEXTORAW('c229'),HEXTORAW('c3020b'));

The redo log files contain the changes made to the database or database dictionary.

LogMiner Dictionary Options

LogMiner requires a dictionary to translate object IDs into object names when it returns redo data to you. LogMiner gives you three options for supplying the dictionary:

Using the Online Catalog

Oracle recommends that you use this option when you will have access to the source database from which the redo log files were created and when no changes to the column definitions in the tables of interest are anticipated. This is the most efficient and easy-to-use option.

Extracting a LogMiner Dictionary to the Redo Log Files

Oracle recommends that you use this option when you do not expect to have access to the source database from which the redo log files were created, or if you anticipate that changes will be made to the column definitions in the tables of interest.

Extracting the LogMiner Dictionary to a Flat File

This option is maintained for backward compatibility with previous releases. This option does not guarantee transactional consistency. Oracle recommends that you use either the online catalog or extract the dictionary from redo log files instead.

Using the Online Catalog

This option can be used when the LogMiner session is started.To direct LogMiner to use the dictionary currently in use for the database, specify the online catalog as your dictionary source when you start LogMiner, as follows:

It is most efficient and easy-to-use way when LogMiner has access to the database to which log file belong and no column definition changes are expected in the tables of interest. This option can be used when the LogMiner session is started as follows:

begin

dbms_logmnr.start_logmnr( options => dbms_logmnr.dict_from_online_catalog );

end;

SQL> EXECUTE DBMS_LOGMNR.START_LOGMNR(OPTIONS => DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG);

Extracting a LogMiner Dictionary to the Redo Log Files

To extract a LogMiner dictionary to the redo log files, the database must be open and in ARCHIVELOG mode and archiving must be enabled. While the dictionary is being extracted to the redo log stream, no DDL statements can be executed. Therefore, the dictionary extracted to the redo log files is guaranteed to be consistent (whereas the dictionary extracted to a flat file is not).

Use this option when using LogMiner in a database to which log file don't belong and column definition changes are expected in the tables of interest. For this to work you will have to embed the dictionary information into the redo logs at the database from where the redo logs for analysis are coming.

begin

dbms_logmnr_d.build( options => dbms_logmnr_d.store_in_redo_logs );

end;

This may embed the dictionary info in multiple log files. Use following sql statements to see where the dictionary embedding starts and where it ends.

SELECT NAME FROM V$ARCHIVED_LOG WHERE DICTIONARY_BEGIN='YES';

SELECT NAME FROM V$ARCHIVED_LOG WHERE DICTIONARY_END='YES';

You will have to add these files and the files between them in the LogMiner analysis.

To extract dictionary information to the redo log files, use the DBMS_LOGMNR_D.BUILD procedure with the STORE_IN_REDO_LOGS option. Do not specify a filename or location.

SQL> EXECUTE DBMS_LOGMNR_D.BUILD(OPTIONS=> DBMS_LOGMNR_D.STORE_IN_REDO_LOGS);

Extracting the LogMiner Dictionary to a Flat File

When the LogMiner dictionary is in a flat file, fewer system resources are used than when it is contained in the redo log files. Oracle recommends that you regularly back up the dictionary extract to ensure correct analysis of older redo log files.

1. Set the initialization parameter, UTL_FILE_DIR, in the initialization parameter file. For example, to set UTL_FILE_DIR to use /oracle/database as the directory where the dictionary file is placed, enter the following in the initialization parameter file:

UTL_FILE_DIR = /oracle/database

2. Start the Database

SQL> startup

3. Execute the PL/SQL procedure DBMS_LOGMNR_D.BUILD. Specify a filename for the dictionary and a directory path name for the file. This procedure creates the dictionary file. For example, enter the following to create the file dictionary.ora in /oracle/database:

SQL> EXECUTE DBMS_LOGMNR_D.BUILD('dictionary.ora','/oracle/database/',DBMS_LOGMNR_D.STORE_IN_FLAT_FILE);

Redo Log File Options

To mine data in the redo log files, LogMiner needs information about which redo log files to mine.

You can direct LogMiner to automatically and dynamically create a list of redo log files to analyze, or you can explicitly specify a list of redo log files for LogMiner to analyze, as follows:

Automatically

If LogMiner is being used on the source database, then you can direct LogMiner to find and create a list of redo log files for analysis automatically. Use the CONTINUOUS_MINE option when you start LogMiner.

Manually

Use the DBMS_LOGMNR.ADD_LOGFILE procedure to manually create a list of redo log files before you start LogMiner. After the first redo log file has been added to the list, each subsequently added redo log file must be from the same database and associated with the same database RESETLOGS SCN. When using this method, LogMiner need not be connected to the source database.

Example: Finding All Modifications in the Current Redo Log File (CATALOG)

The easiest way to examine the modification history of a database is to mine at the source database and use the online catalog to translate the redo log files. This example shows how to do the simplest analysis using LogMiner.

Step 1 Specify the list of redo log files to be analyzed.

Specify the redo log files which you want to analyze.

SQL> EXECUTE DBMS_LOGMNR.ADD_LOGFILE(LOGFILENAME => '/usr/oracle/ica/log1.ora',OPTIONS => DBMS_LOGMNR.NEW);

SQL> EXECUTE DBMS_LOGMNR.ADD_LOGFILE(LOGFILENAME => '/u01/oracle/ica/log2.ora',OPTIONS => DBMS_LOGMNR.ADDFILE);

Step 2 Start LogMiner.

Start LogMiner and specify the dictionary to use.

SQL> EXECUTE DBMS_LOGMNR.START_LOGMNR(OPTIONS => DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG);

Step 3 Query the V$LOGMNR_CONTENTS view.

Note that there are four transactions (two of them were committed within the redo log file being analyzed, and two were not). The output shows the DML statements in the order in which they were executed; thus transactions interleave among themselves.

SQL> SELECT username AS USR, (XIDUSN || '.' || XIDSLT || '.' || XIDSQN) AS XID,SQL_REDO, SQL_UNDO

FROM V$LOGMNR_CONTENTS WHERE username IN ('HR', 'OE');

USR XID SQL_REDO SQL_UNDO

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

HR 1.11.1476 set transaction read write;

HR 1.11.1476 insert into "HR"."EMPLOYEES"( delete from "HR"."EMPLOYEES"

"EMPLOYEE_ID","FIRST_NAME", where "EMPLOYEE_ID" = '306'

"LAST_NAME","EMAIL", and "FIRST_NAME" = 'Mohammed'

"PHONE_NUMBER","HIRE_DATE", and "LAST_NAME" = 'Sami'

"JOB_ID","SALARY", and "EMAIL" = 'MDSAMI'

"COMMISSION_PCT","MANAGER_ID", and "PHONE_NUMBER" = '1234567890'

"DEPARTMENT_ID") values and "HIRE_DATE" = TO_DATE('10-JAN-2003

('306','Mohammed','Sami', 13:34:43', 'dd-mon-yyyy hh24:mi:ss')

'MDSAMI', '1234567890', and "JOB_ID" = 'HR_REP' and

TO_DATE('10-jan-2003 13:34:43', "SALARY" = '120000' and

'dd-mon-yyyy hh24:mi:ss'), "COMMISSION_PCT" = '.05' and

'HR_REP','120000', '.05', "DEPARTMENT_ID" = '10' and

'105','10'); ROWID = 'AAAHSkAABAAAY6rAAO';

OE 1.1.1484 set transaction read write;

OE 1.1.1484 update "OE"."PRODUCT_INFORMATION" update "OE"."PRODUCT_INFORMATION"

set "WARRANTY_PERIOD" = set "WARRANTY_PERIOD" =

TO_YMINTERVAL('+05-00') where TO_YMINTERVAL('+01-00') where

"PRODUCT_ID" = '1799' and "PRODUCT_ID" = '1799' and

"WARRANTY_PERIOD" = "WARRANTY_PERIOD" =

TO_YMINTERVAL('+01-00') and TO_YMINTERVAL('+05-00') and

ROWID = 'AAAHTKAABAAAY9mAAB'; ROWID = 'AAAHTKAABAAAY9mAAB';

OE 1.1.1484 update "OE"."PRODUCT_INFORMATION" update "OE"."PRODUCT_INFORMATION"

set "WARRANTY_PERIOD" = set "WARRANTY_PERIOD" =

TO_YMINTERVAL('+05-00') where TO_YMINTERVAL('+01-00') where

"PRODUCT_ID" = '1801' and "PRODUCT_ID" = '1801' and

"WARRANTY_PERIOD" = "WARRANTY_PERIOD" =

TO_YMINTERVAL('+01-00') and TO_YMINTERVAL('+05-00') and

ROWID = 'AAAHTKAABAAAY9mAAC'; ROWID ='AAAHTKAABAAAY9mAAC';

HR 1.11.1476 insert into "HR"."EMPLOYEES"( delete from "HR"."EMPLOYEES"

"EMPLOYEE_ID","FIRST_NAME", "EMPLOYEE_ID" = '307' and

"LAST_NAME","EMAIL", "FIRST_NAME" = 'John' and

"PHONE_NUMBER","HIRE_DATE", "LAST_NAME" = 'Silver' and

"JOB_ID","SALARY", "EMAIL" = 'JSILVER' and

"COMMISSION_PCT","MANAGER_ID", "PHONE_NUMBER" = '5551112222'

"DEPARTMENT_ID") values and "HIRE_DATE" = TO_DATE('10-jan- 2003

('307','John','Silver', 13:41:03', 'dd-mon-yyyy hh24:mi:ss')

'JSILVER', '5551112222', and "JOB_ID" ='105' and

"DEPARTMENT_ID"

TO_DATE('10-jan-2003 13:41:03', = '50' and ROWID =

'AAAHSkAABAAAY6rAAP';

'dd-mon-yyyy hh24:mi:ss'),

'SH_CLERK','110000', '.05',

'105','50');

OE 1.1.1484 commit;

HR 1.15.1481 set transaction read write;

HR 1.15.1481 delete from "HR"."EMPLOYEES" insert into "HR"."EMPLOYEES"(

where "EMPLOYEE_ID" = '205' and "EMPLOYEE_ID","FIRST_NAME",

"FIRST_NAME" = 'Shelley' and "LAST_NAME","EMAIL","PHONE_NUMBER",

"LAST_NAME" = 'Higgins' and "HIRE_DATE", "JOB_ID","SALARY",

"EMAIL" = 'SHIGGINS' and "COMMISSION_PCT","MANAGER_ID",

"PHONE_NUMBER" = '515.123.8080' "DEPARTMENT_ID") values

and "HIRE_DATE" = TO_DATE( ('205','Shelley','Higgins',

'07-jun-1994 10:05:01', and 'SHIGGINS','515.123.8080',

'dd-mon-yyyy hh24:mi:ss') TO_DATE('07-jun-1994 10:05:01',

and "JOB_ID" = 'AC_MGR' 'dd-mon-yyyy hh24:mi:ss'),

and "SALARY"= '12000' 'AC_MGR','12000',NULL,'101','110');

and "COMMISSION_PCT" IS NULL

and "MANAGER_ID"

= '101' and "DEPARTMENT_ID" =

'110' and ROWID =

'AAAHSkAABAAAY6rAAM';

OE 1.8.1484 set transaction read write;

OE 1.8.1484 update "OE"."PRODUCT_INFORMATION" update "OE"."PRODUCT_INFORMATION"

set "WARRANTY_PERIOD" = set "WARRANTY_PERIOD" =

TO_YMINTERVAL('+12-06') where TO_YMINTERVAL('+20-00') where

"PRODUCT_ID" = '2350' and "PRODUCT_ID" = '2350' and

"WARRANTY_PERIOD" = "WARRANTY_PERIOD" =

TO_YMINTERVAL('+20-00') and TO_YMINTERVAL('+20-00') and

ROWID = 'AAAHTKAABAAAY9tAAD'; ROWID ='AAAHTKAABAAAY9tAAD';

HR 1.11.1476 commit;

Step 4 End the LogMiner session.

SQL> EXECUTE DBMS_LOGMNR.END_LOGMNR();

Example of Mining Without Specifying the List of Redo Log Files Explicitly

The previous example explicitly specified the redo log file or files to be mined. However, if you are mining in the same database that generated the redo log files, then you can mine the appropriate list of redo log files by just specifying the time (or SCN) range of interest. To mine a set of redo log files without explicitly specifying them, use the DBMS_LOGMNR.CONTINUOUS_MINE option to the DBMS_LOGMNR.START_LOGMNR procedure, and specify either a time range or an SCN range of interest.

Example : Mining Redo Log Files in a Given Time Range (CATALOG)

This example assumes that you want to use the data dictionary extracted to the redo log files.

Step 1 Determine the timestamp of the redo log file that contains the start of the data dictionary.

SQL> SELECT NAME, FIRST_TIME FROM V$ARCHIVED_LOG WHERE SEQUENCE# = (SELECT MAX(SEQUENCE#)

FROM V$ARCHIVED_LOG WHERE DICTIONARY_BEGIN = 'YES');

NAME FIRST_TIME

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

/usr/oracle/data/db1arch_1_207_482701534.dbf 10-jan-2003 12:01:34

Step 2 Display all the redo log files that have been generated so far.

This step is not required, but is included to demonstrate that the CONTINUOUS_MINE option works as expected, as will be shown in Step 4.

SQL> SELECT FILENAME name FROM V$LOGMNR_LOGS

WHERE LOW_TIME > '10-jan-2003 12:01:34';

NAME

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

/usr/oracle/data/db1arch_1_207_482701534.dbf

/usr/oracle/data/db1arch_1_208_482701534.dbf

/usr/oracle/data/db1arch_1_209_482701534.dbf

/usr/oracle/data/db1arch_1_210_482701534.dbf

Step 3 Start LogMiner.

Start LogMiner by specifying the dictionary to use and the COMMITTED_DATA_ONLY, PRINT_PRETTY_SQL, and CONTINUOUS_MINE options.

SQL> EXECUTE DBMS_LOGMNR.START_LOGMNR

(STARTTIME => '10-jan-2003 12:01:34',

ENDTIME => SYSDATE,

OPTIONS => DBMS_LOGMNR.DICT_FROM_REDO_LOGS +

DBMS_LOGMNR.COMMITTED_DATA_ONLY +

DBMS_LOGMNR.PRINT_PRETTY_SQL +

DBMS_LOGMNR.CONTINUOUS_MINE);

Step 4 Query the V$LOGMNR_LOGS view.

This step shows that the DBMS_LOGMNR.START_LOGMNR procedure with the CONTINUOUS_MINE option includes all of the redo log files that have been generated so far, as expected. (Compare the output in this step to the output in Step 2.)

SQL> SELECT FILENAME name FROM V$LOGMNR_LOGS;

NAME

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

/usr/oracle/data/db1arch_1_207_482701534.dbf

/usr/oracle/data/db1arch_1_208_482701534.dbf

/usr/oracle/data/db1arch_1_209_482701534.dbf

/usr/oracle/data/db1arch_1_210_482701534.dbf

Step 5 Query the V$LOGMNR_CONTENTS view.

To reduce the number of rows returned by the query, exclude all DML statements done in the sys or system schema. (This query specifies a timestamp to exclude transactions that were involved in the dictionary extraction.)

Note that all reconstructed SQL statements returned by the query are correctly translated.

SQL> SELECT USERNAME AS usr,(XIDUSN || '.' || XIDSLT || '.' || XIDSQN) as XID, SQL_REDO

FROM V$LOGMNR_CONTENTS WHERE SEG_OWNER IS NULL OR SEG_OWNER NOT IN ('SYS', 'SYSTEM')

AND TIMESTAMP > '10-jan-2003 15:59:53';

USR XID SQL_REDO

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

SYS 1.2.1594 set transaction read write;

SYS 1.2.1594 create table oe.product_tracking (product_id number not null,

modified_time date,

old_list_price number(8,2),

old_warranty_period interval year(2) to month);

SYS 1.2.1594 commit;

SYS 1.18.1602 set transaction read write;

SYS 1.18.1602 create or replace trigger oe.product_tracking_trigger

before update on oe.product_information

for each row

when (new.list_price <> old.list_price or

new.warranty_period <> old.warranty_period)

declare

begin

insert into oe.product_tracking values

(:old.product_id, sysdate,

:old.list_price, :old.warranty_period);

end;

SYS 1.18.1602 commit;

OE 1.9.1598 update "OE"."PRODUCT_INFORMATION"

set

"WARRANTY_PERIOD" = TO_YMINTERVAL('+08-00'),

"LIST_PRICE" = 100

where

"PRODUCT_ID" = 1729 and

"WARRANTY_PERIOD" = TO_YMINTERVAL('+05-00') and

"LIST_PRICE" = 80 and

ROWID = 'AAAHTKAABAAAY9yAAA';

OE 1.9.1598 insert into "OE"."PRODUCT_TRACKING"

values

"PRODUCT_ID" = 1729,

"MODIFIED_TIME" = TO_DATE('13-jan-2003 16:07:03',

'dd-mon-yyyy hh24:mi:ss'),

"OLD_LIST_PRICE" = 80,

"OLD_WARRANTY_PERIOD" = TO_YMINTERVAL('+05-00');

OE 1.9.1598 update "OE"."PRODUCT_INFORMATION"

set

"WARRANTY_PERIOD" = TO_YMINTERVAL('+08-00'),

"LIST_PRICE" = 92

where

"PRODUCT_ID" = 2340 and

"WARRANTY_PERIOD" = TO_YMINTERVAL('+05-00') and

"LIST_PRICE" = 72 and

ROWID = 'AAAHTKAABAAAY9zAAA';

OE 1.9.1598 insert into "OE"."PRODUCT_TRACKING"

values

"PRODUCT_ID" = 2340,

"MODIFIED_TIME" = TO_DATE('13-jan-2003 16:07:07',

'dd-mon-yyyy hh24:mi:ss'),

"OLD_LIST_PRICE" = 72,

"OLD_WARRANTY_PERIOD" = TO_YMINTERVAL('+05-00');

OE 1.9.1598 commit;

Step 6 End the LogMiner session.

SQL> EXECUTE DBMS_LOGMNR.END_LOGMNR();

Extracting the LogMiner Dictionary to a Flat File

This article is prepared using "Extracting the LogMiner Dictionary to a Flat File" as it requires a little bit of setup and I wanted to demonstrate that.

$ mkdir -p /u01/apps/logminer_dir

$ sqlplus / as sysdba

The location where dictionary will be created should be set in utl_file_dir initialization

parameter.

SQL> alter system set utl_file_dir='/u01/apps/logminer_dir' scope=spfile;

System altered.

shutdown immediate

startup

show parameter utl_file_dir

NAME TYPE VALUE

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

utl_file_dir string /u01/apps/logminer_dir

Normally oracle records the change vector in the redo log files i.e. just the information that is required to reconstruct the operation at recovery time. If you want additional information in the redo log then you need to enable supplemental logging prior to generating log files that will be analyzed by LogMiner. Therefore, at the very least, we will enable minimal supplemental logging, as the following SQL statement shows:

SQL> SELECT SUPPLEMENTAL_LOG_DATA_MIN FROM V$DATABASE;

SUPPLEME

--------

/* Minimum supplemental logging is not enabled. */

SQL> ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;

Database altered.

SQL> SELECT SUPPLEMENTAL_LOG_DATA_MIN FROM V$DATABASE;

SUPPLEME

--------

YES

/* Minimum supplemental logging is now enabled. */

SQL> alter system switch logfile;

System altered.

SQL> select g.group# , g.status , m.member

from v$log g, v$logfile m

where g.group# = m.group#

and g.status = 'CURRENT';

GROUP# STATUS MEMBER

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

2 CURRENT +DG1/ora10g/onlinelog/group_2.264.718794539

/* start fresh with a new log file which is the group 2.*/

SQL> create table scott.test_logmnr (id number, name varchar2(10) );

Table created.

SQL> begin

dbms_logmnr_d.build

(

dictionary_filename => 'dictionary.dic',

dictionary_location => '/u01/apps/logminer_dir',

options => dbms_logmnr_d.store_in_flat_file

);

end;

PL/SQL procedure successfully completed.

This has recorded the dictionary information into the file

"/u01/apps/logminer_dir/dictionary.dic".

Now lets make a few user data changes by logging in as user SCOTT.

SQL> conn scott/tiger

connected.

SQL> insert into test_logmnr values (1,'TEST1');

1 row created.

SQL> insert into test_logmnr values (2,'TEST2');

1 row created.

SQL> commit;

Commit complete.

SQL> update test_logmnr set name = 'TEST';

2 rows updated.

SQL> commit;

Commit complete.

SQL> delete from test_logmnr;

2 rows deleted.

SQL> commit;

Commit complete.

After these changes lets log back in as sysdba and start LogMiner session.

SQL> conn / as sysdba

connected.

SQL> select g.group# , g.status , m.member

from v$log g, v$logfile m

where g.group# = m.group#

and g.status = 'CURRENT';

GROUP# STATUS MEMBER

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

2 CURRENT +DG1/ora10g/onlinelog/group_2.264.718794539

Log group 2 is still current, that means all of the changes we made are in this redo group.

SQL> begin

dbms_logmnr.add_logfile

(

logfilename => '+DG1/ora10g/onlinelog/group_2.264.718794539',

options => dbms_logmnr.new

);

end;

PL/SQL procedure successfully completed.

DBMS_LOGMNR.ADD_LOGFILE builds up a list of redo log files for LogMiner analysis.

The first file is added with the options => dbms_logmnr.new and rest are added

with the options => dbms_logmnr.addfile

SQL> select filename from v$logmnr_logs;

FILENAME

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

+DG1/ora10g/onlinelog/group_2.264.718794539

Dictionary view v$logmnr_logs contains the list of log files that are added

via DBMS_LOGMNR.ADD_LOGFILE.

SQL> begin

dbms_logmnr.start_logmnr

(

dictfilename => '/u01/apps/logminer_dir/dictionary.dic',

options => dbms_logmnr.print_pretty_sql +

dbms_logmnr.no_sql_delimiter +

dbms_logmnr.ddl_dict_tracking

);

end;

PL/SQL procedure successfully completed.

DBMS_LOGMNR.START_LOGMNR starts a LogMiner session. It will populate the dictionary view v$logmnr_contents with the contents of log files in the list we built with DBMS_LOGMNR.ADD_LOGFILE.

v$logminer_contents is only accessible to the current session which has started LogMiner and only until the DBMS_LOGMNR.END_LOGMNR is called. There could be many options provided with START_LOGMNR which affects the data representation in v$logmnr_contents e.g.

dbms_logmnr.print_pretty_sql will format the sql statements to enhance readability.

dbms_logmnr.no_sql_delimiter will omit the ";" from the end of the sql statements which is useful when sql are meant to be re-executed in PL/SQL routines.

dbms_logmnr.ddl_dict_tracking tracks the DDL statements in the log files.

SQL> DROP TABLE myLogAnalysis;

Table dropped.

SQL> create table myLogAnalysis

select * from v$logmnr_contents;

Table created.

Its always better to copy contents of v$logmnr_contents to a user table and then perform

the analysis as it is quite expensive to query v$logmnr_contents. Moreover, the user table

can be indexed for better query performance.

SQL> begin

DBMS_LOGMNR.END_LOGMNR();

end;

PL/SQL procedure successfully completed.

DBMS_LOGMNR.END_LOGMNR() ends the LogMiner session and v$logmnr_contents is no more accessible but our user table myLogAnalysis is still available which is a copy of v$logmnr_contents.

set lines 1000

set pages 500

column scn format a6

column username format a8

column seg_name format a11

column sql_redo format a33

column sql_undo format a33

The below query will show the changes made by the user SCOTT and either the table is

TEST_LOGMNR or there is no table at all i.e. transaction start and transaction end

statements etc.

The output below shows the system change number for the change, the segment on which the

change was made, the sql statement to redo the change and the sql statement to undo the

change.

select scn , seg_name , sql_redo , sql_undo

from myLogAnalysis

where username = 'SCOTT'

AND (seg_owner is null OR seg_owner = 'SCOTT')

SCN SEG_NAME SQL_REDO SQL_UNDO

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

639968 TEST_LOGMNR create table scott.test_logmnr

(id number,

name varchar2(10)

)

640039 set transaction read write

640039 TEST_LOGMNR insert into "SCOTT"."TEST_LOGMNR" delete from "SCOTT"."TEST_LOGMNR"

values where

"ID" = 1, "ID" = 1 and

"NAME" = 'TEST1' "NAME" = 'TEST1' and

ROWID = 'AAAM7vAAEAAAALcAAA'

640041 TEST_LOGMNR insert into "SCOTT"."TEST_LOGMNR" delete from "SCOTT"."TEST_LOGMNR"

values where

"ID" = 2, "ID" = 2 and

"NAME" = 'TEST2' "NAME" = 'TEST2' and

ROWID = 'AAAM7vAAEAAAALcAAB'

640044 commit

640047 set transaction read write

640047 TEST_LOGMNR update "SCOTT"."TEST_LOGMNR" update "SCOTT"."TEST_LOGMNR"

set set

"NAME" = 'TEST' "NAME" = 'TEST1'

where where

"NAME" = 'TEST1' and "NAME" = 'TEST' and

ROWID = 'AAAM7vAAEAAAALcAAA' ROWID = 'AAAM7vAAEAAAALcAAA'

640047 TEST_LOGMNR update "SCOTT"."TEST_LOGMNR" update "SCOTT"."TEST_LOGMNR"

set set

"NAME" = 'TEST' "NAME" = 'TEST2'

where where

"NAME" = 'TEST2' and "NAME" = 'TEST' and

ROWID = 'AAAM7vAAEAAAALcAAB' ROWID = 'AAAM7vAAEAAAALcAAB'

640050 commit

640052 set transaction read write

640058 TEST_LOGMNR delete from "SCOTT"."TEST_LOGMNR" insert into "SCOTT"."TEST_LOGMNR"

where values

"ID" = 1 and "ID" = 1,

"NAME" = 'TEST' and "NAME" = 'TEST'

ROWID = 'AAAM7vAAEAAAALcAAA'

640058 TEST_LOGMNR delete from "SCOTT"."TEST_LOGMNR" insert into "SCOTT"."TEST_LOGMNR"

where values

"ID" = 2 and "ID" = 2,

"NAME" = 'TEST' and "NAME" = 'TEST'

ROWID = 'AAAM7vAAEAAAALcAAB'

640066 commit.

The following restrictions apply:

The following are not supported:

Data types LONG and LOB

Simple and nested abstract data types ( ADTs)

Collections (nested tables and VARRAYs)

Object Refs

Index Organized Tables (IOTs)

DBMS_LOGMNR_D.STORE_IN_FLAT _FILE

DBMS_LOGMNR_D.STORE_IN_REDO _LOGS

----with flat file option

SQL> EXECUTE DBMS_LOGMNR_D.BUILD( DICTIONARY_FILENAME => 'mydictionaryname.ora',

DICTIONARY_LOCATION => '/usr/mydictionary/location');

----OR

SQL> EXECUTE DBMS_LOGMNR_D.BUILD( 'flatdictionary.ora', '/oracle/logminor/',

options => DBMS_LOGMNR_D.STORE_IN_FLAT _FILE);

--with redo logs option

SQL> EXECUTE DBMS_LOGMNR_D.BUILD( options => DBMS_LOGMNR_D.STORE_IN_REDO _LOGS);

If you get the following error:

SQL> EXECUTE DBMS_LOGMNR_D.BUILD(OPTIONS=>DBMS_LOGMNR_D.STORE_IN_REDO_LOGS);

BEGIN DBMS_LOGMNR_D.BUILD(OPTIONS=>DBMS_LOGMNR_D.STORE_IN_REDO_LOGS); END;

ERROR at line 1:

ORA-01347: Supplemental log data no longer found

ORA-06512: at "SYS.DBMS_LOGMNR_D", line 2562

ORA-06512: at "SYS.DBMS_LOGMNR_D", line 2617

ORA-06512: at "SYS.DBMS_LOGMNR_D", line 12

ORA-06512: at line 1

First check if you have SUPPLEMENTAL Logging enabled,

SQL> SELECT SUPPLEMENTAL_LOG_DATA_MIN FROM V$DATABASE;

If not,

SQL> ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;

(Adding or Removing the Redo Log Files for Analysis)

You can specify the redo log files as following examples with the mount or nomount option of startup.

---- for new log file or the first one.

SQL> EXECUTE DBMS_LOGMNR.ADD_LOGFILE( LOGFILENAME => '/oracle/archivelogs/log_01 _132_6576654328.ora',

OPTIONS => DBMS_LOGMNR.NEW);

-----for adding an additional log file.

SQL> EXECUTE DBMS_LOGMNR.ADD_LOGFILE( LOGFILENAME => '/oracle/archivelogs/log_01 _133_6576654328.ora',

OPTIONS => DBMS_LOGMNR.ADDFILE);

---- for removing a log file.

SQL> EXECUTE DBMS_LOGMNR.ADD_LOGFILE(LOGFILENAME => '/oracle/archivelogs/log_01 _133_6576654328.ora',

OPTIONS => DBMS_LOGMNR.REMOVEFILE);

(-----Starting LogMiner-------)

After you have create a dictionary file and specify which redo log files to analyze, you can start LogMiner and begin your analysis. Take the following steps:

----To start Log Miner with flat dictionary:

SQL> EXECUTE DBMS_LOGMNR.START_LOGMNR( DICTFILENAME =>'/oracle/database/dictionary .ora');

----To start Log Miner with using dictionary from redo logs:

SQL> EXECUTE DBMS_LOGMNR.START_LOGMNR(OPTIONS =>DBMS_LOGMNR.DICT_FROM_REDO _LOGS);

----To start Log Miner with using Online Catalog Dictionary:

SQL> EXECUTE DBMS_LOGMNR.START_LOGMNR(OPTIONS =>DBMS_LOGMNR.DICT_FROM_ONLINE _CATALOG);

----To start Log Miner using starting and ending time:

SQL> EXECUTE DBMS_LOGMNR.START_LOGMNR( DICTFILENAME => '/oracle/flatdictionary.ora',

STARTTIME => TO_DATE('01-Jan-1998 08:30:00', 'DD-MON-YYYY HH:MI:SS')

ENDTIME => TO_DATE('01-Jan-1998 08:45:00', 'DD-MON-YYYY HH:MI:SS'));

-----To start Log Miner using the SCN number:

SQL> EXECUTE DBMS_LOGMNR.START_LOGMNR( DICTFILENAME => '/oracle/dictionary.ora',

STARTSCN => 100,

ENDSCN => 150);

----To start Log Miner using the following OPTIONs:

COMMITTED_DATA_ONLY

SKIP_CORRUPTION

DDL_DICT_TRACKING

NO_DICT_RESET_ONSELECT

DICT_FROM_ONLINE_CATALOG

DICT_FROM_REDO_LOGS

SQL> EXECUTE DBMS_LOGMNR.START_LOGMNR( OPTIONS => DBMS_LOGMNR.DDL_DICT_TRACKING +

DBMS_LOGMNR.NO_DICT_RESET _ONSELECT +

DBMS_LOGMNR.DICT_FROM_REDO _LOGS);

Now you should be able to do this:

SQL> SELECT count(*) FROM v$logmnr_contents;

SQL> DESC v$logmnr_contents

Querying LogMiner

(EXAMPLES of how to read from v$logmnr_contents)

To read the log file, you need to do the following query.

SQL> COL table_name FORMAT a20

SQL> SELECT sql_redo FROM SYS.V$LOGMNR_CONTENTS;

To query the V$LOGMNR_CONTENTS view to see changes done by a specific user:

SQL> SELECT sql_redo, sql_undo FROM V$LOGMNR_CONTENTS

WHERE USERNAME = 'SAASUB' AND TABLE_NAME = 'EVENT';

SQL> SELECT rownum, sql_redo FROM V$LOGMNR_CONTENTS

WHERE sql_redo like '%SAABUD%' and sql_redo NOT like '%SYS%' and

rownum < 10;

--with time stamp

SQL> SELECT 'Row Number: ' || rownum, 'Date-Time: ' || to_char(timestamp,'DD-MM HH24:MI:SS'),

'Transaction on table: ' ||table_name || '--->' ||

SUBSTR(sql_redo,1,20) FROM V$LOGMNR_CONTENTS

WHERE sql_redo like '%SAABUD%' AND

sql_redo NOT like '%SYS%' AND rownum < 10;

To determine which tables were modified in the range of time.

SQL> SELECT seg_owner, seg_name, count(*) AS Hits

FROM V$LOGMNR_CONTENTS WHERE seg_name NOT LIKE '%$'

GROUP BY seg_owner, seg_name;

SQL> SELECT rownum, to_char(timestamp,'DD-MM HH24:MI:SS') as "Date/Time",

table_name, SUBSTR(sql_redo,1,40) FROM V$LOGMNR_CONTENTS

WHERE sql_redo like '%SAABUD%' AND sql_redo NOT like '%SYS%';

To determine who drop any objects.

SQL> SELECT rownum, to_char(timestamp,'DD-MM HH24:MI:SS') as "Date/Time",

table_name, SUBSTR(sql_redo,1,40) FROM V$LOGMNR_CONTENTS

WHERE sql_redo like '%SAABUD%' AND

sql_redo NOT like '%SYS%' AND

UPPER(sql_redo) like '%DROP%';

Ending LogMiner

To end the log miner.

SQL> EXECUTE DBMS_LOGMNR.END_LOGMNR;

Showing Only Committed Transactions

When you use the COMMITTED_DATA_ONLY option to DBMS_LOGMNR.START_LOGMNR, only rows belonging to committed transactions are shown in the V$LOGMNR_CONTENTS view. This enables you to filter out rolled back transactions, transactions that are in progress, and internal operations.

To enable this option, specify it when you start LogMiner, as follows:

SQL> EXECUTE DBMS_LOGMNR.START_LOGMNR(OPTIONS => DBMS_LOGMNR.COMMITTED_DATA_ONLY);

SQL> SELECT (XIDUSN || '.' || XIDSLT || '.' || XIDSQN) AS XID,

USERNAME, SQL_REDO FROM V$LOGMNR_CONTENTS WHERE USERNAME != 'SYS'

AND SEG_OWNER IS NULL OR SEG_OWNER NOT IN ('SYS', 'SYSTEM');

Filtering Data By Time :

To filter data by time, set the STARTTIME and ENDTIME parameters. The procedure expects date values. Use the TO_DATE function to specify date and time, as in this example:

SQL> execute DBMS_LOGMNR.START_LOGMNR (DICTFILENAME =>'c:\dict\dictionary.ora',

STARTTIME => TO_DATE('20-Jul-2011 04:30:00', 'DD-MON-YYYY HH:MI:SS'),

ENDTIME => TO_DATE('20-Jul-2011 04:45:00', 'DD-MON-YYYY HH:MI:SS'));

If no STARTTIME or ENDTIME parameters are specified, the entire redo log is read from start to end, for each SELECT statement issued.

The timestamps should not be used to infer ordering of redo records. we can infer the order of redo records by using the SCN.

Filtering Data By SCN :

To filter data by SCN (system change number), use the STARTSCN and ENDSCN parameters, as in this example:

SQL> execute DBMS_LOGMNR.START_LOGMNR (dictfilename=>'c:\dict\dictionary.ora', STARTSCN => 100, ENDSCN => 150);

The STARTSCN and ENDSCN parameters override the STARTTIME and ENDTIME parameters in situations where all are specified.

If no STARTSCN or ENDSCN parameters are specified, the entire redo log is read from start to end, for each SELECT statement issued.

DEMONSTRATION Current Redo Log File (CATALOG)

Constants for ADD_LOGFILE Options flag

NEW

DBMS_LOGMNR.NEW purges the existing list of logfiles, if any. Place the logfile specified in the list of logfiles to be analyzed.

ADDFILE

DBMS_LOGMNR.ADDFILE adds this logfile to the list of logfiles to be analyzed. This only works if there was at least one invocation of ADD_LOGFILE with the Options parameter set to NEW.

REMOVEFILE

DBMS_LOGMNR.REMOVEFILE removes the logfile from the list of logfiles to be analyzed. This has no effect if the logfile was not previously added to the list.

===========================================================================

STEP 1:

conn sys/oracle@orcl as sysdba

(ALL LOGFILE)

EXECUTE DBMS_LOGMNR.ADD_LOGFILE(LOGFILENAME => 'D:\oracle\product\10.2.0\oradata\orcl\REDO01.LOG',OPTIONS => DBMS_LOGMNR.ADDFILE);

EXECUTE DBMS_LOGMNR.ADD_LOGFILE(LOGFILENAME => 'D:\oracle\product\10.2.0\oradata\orcl\REDO02.LOG',OPTIONS => DBMS_LOGMNR.ADDFILE);

EXECUTE DBMS_LOGMNR.ADD_LOGFILE(LOGFILENAME => 'D:\oracle\product\10.2.0\oradata\orcl\REDO03.LOG',OPTIONS => DBMS_LOGMNR.ADDFILE);

===========================================================================

STEP 2:

EXECUTE DBMS_LOGMNR.START_LOGMNR(OPTIONS => DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG);

===========================================================================

STEP 3:

SELECT username AS USR, (XIDUSN || '.' || XIDSLT || '.' || XIDSQN) AS XID,SQL_REDO, SQL_UNDO

FROM V$LOGMNR_CONTENTS WHERE username IN ('HR','SCOTT');

===========================================================================

STEP 4:

EXECUTE DBMS_LOGMNR.END_LOGMNR();

===========================================================================

STEP 5:

conn scott/tiger

create table scott.test_logmnr (id number,name varchar2(10) );

insert into test_logmnr values (1,'TEST1');

insert into test_logmnr values (2,'TEST2');

commit;

update test_logmnr set name = 'TEST';

commit;

delete from test_logmnr;

commit;

===========================================================================

STEP 6:

select * from hr.employees where department_ID=60;

update hr.employees set salary=salary+2000 where department_ID=60;

===========================================================================

STEP 7:

conn sys/oracle@orcl as sysdba

EXECUTE DBMS_LOGMNR.ADD_LOGFILE(LOGFILENAME => 'D:\oracle\product\10.2.0\oradata\orcl\REDO01.LOG',OPTIONS => DBMS_LOGMNR.ADDFILE);

EXECUTE DBMS_LOGMNR.ADD_LOGFILE(LOGFILENAME => 'D:\oracle\product\10.2.0\oradata\orcl\REDO02.LOG',OPTIONS => DBMS_LOGMNR.ADDFILE);

EXECUTE DBMS_LOGMNR.ADD_LOGFILE(LOGFILENAME => 'D:\oracle\product\10.2.0\oradata\orcl\REDO03.LOG',OPTIONS => DBMS_LOGMNR.ADDFILE);

EXECUTE DBMS_LOGMNR.START_LOGMNR(OPTIONS => DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG);

SELECT username AS USR, (XIDUSN || '.' || XIDSLT || '.' || XIDSQN) AS XID,SQL_REDO, SQL_UNDO

FROM V$LOGMNR_CONTENTS WHERE username IN ('HR','SCOTT');

EXECUTE DBMS_LOGMNR.END_LOGMNR();

===========================================================================

EXECUTE DBMS_LOGMNR.START_LOGMNR(OPTIONS => DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG);

EXECUTE DBMS_LOGMNR.ADD_LOGFILE(LOGFILENAME => 'D:\oracle\product\10.2.0\oradata\orcl\REDO01.LOG',OPTIONS => DBMS_LOGMNR.REMOVEFILE);

EXECUTE DBMS_LOGMNR.ADD_LOGFILE(LOGFILENAME => 'D:\oracle\product\10.2.0\oradata\orcl\REDO02.LOG',OPTIONS => DBMS_LOGMNR.REMOVEFILE);

EXECUTE DBMS_LOGMNR.ADD_LOGFILE(LOGFILENAME => 'D:\oracle\product\10.2.0\oradata\orcl\REDO03.LOG',OPTIONS => DBMS_LOGMNR.REMOVEFILE);

EXECUTE DBMS_LOGMNR.END_LOGMNR();

===========================================================================

set echo on

set feed on

-- add a first file to the logminer list

begin

dbms_logmnr.add_logfile( logfilename=> '/usr/tmp/logmine/PLYX84670.arc',

options=> dbms_logmnr.new);

end;

-- now add the rest of the files

begin

dbms_logmnr.add_logfile( logfilename=> '/usr/tmp/logmine/PLYX84670.arc', options=> dbms_logmnr.addfile);

dbms_logmnr.add_logfile( logfilename=> '/usr/tmp/logmine/PLYX84671.arc', options=> dbms_logmnr.addfile);

dbms_logmnr.add_logfile( logfilename=> '/usr/tmp/logmine/PLYX84672.arc', options=> dbms_logmnr.addfile);

dbms_logmnr.add_logfile( logfilename=> '/usr/tmp/logmine/PLYX84673.arc', options=> dbms_logmnr.addfile);

dbms_logmnr.add_logfile( logfilename=> '/usr/tmp/logmine/PLYX84674.arc', options=> dbms_logmnr.addfile);

dbms_logmnr.add_logfile( logfilename=> '/usr/tmp/logmine/PLYX84675.arc', options=> dbms_logmnr.addfile);

dbms_logmnr.add_logfile( logfilename=> '/usr/tmp/logmine/PLYX84676.arc', options=> dbms_logmnr.addfile);

dbms_logmnr.add_logfile( logfilename=> '/usr/tmp/logmine/PLYX84677.arc', options=> dbms_logmnr.addfile);

dbms_logmnr.add_logfile( logfilename=> '/usr/tmp/logmine/PLYX84678.arc', options=> dbms_logmnr.addfile);

dbms_logmnr.add_logfile( logfilename=> '/usr/tmp/logmine/PLYX84679.arc', options=> dbms_logmnr.addfile);

end;

-- execute logminer

begin

dbms_logmnr.start_logmnr( dictfilename=>'/usr/tmp/dictionary.ora');

end;

ANALYZE using TIME

-- ==================

-- begin

-- dbms_logmnr.start_logmnr( DICTFILENAME=> '/oracle/dictionary.ora',

-- STARTTIME=> to_date('01-Jan-1998 08:30:00', 'DD-MON-YYYY HH:MI:SS'),

-- ENDTIME=> to_date('01-Jan-1998 08:45:00', 'DD-MON-YYYY HH:MI:SS'));

-- end;

-- /

-- ANALYZE using SCN

-- =================

-- begin

-- dbms_logmnr.start_logmnr( dictfilename=> '/oracle/dictionary.ora',

-- STARTSCN=> 100,

-- ENDSCN=> 150);

-- end;

-- /

Extracting the LogMiner Dictionary to a Flat File

1st TIME STEP

alter system set utl_file_dir='D:\logminer\' scope=spfile;

shutdown immediate

startup

show parameter utl_file_dir

SELECT SUPPLEMENTAL_LOG_DATA_MIN FROM V$DATABASE;

ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;

SELECT SUPPLEMENTAL_LOG_DATA_MIN FROM V$DATABASE;

execute dbms_logmnr_d.build('dictionary.dic','D:\logminer\',options=>dbms_logmnr_d.store_in_flat_file);

----Add logfile

execute dbms_logmnr.add_logfile (logfilename => 'D:\Archive\ARC_1_1_779021390.DBF',options => dbms_logmnr.addfile);

execute dbms_logmnr.add_logfile (logfilename => 'D:\Archive\ARC_1_2_779021390.DBF',options => dbms_logmnr.addfile);

execute dbms_logmnr.add_logfile (logfilename => 'D:\Archive\ARC_1_3_779021390.DBF',options => dbms_logmnr.addfile);

select filename from v$logmnr_logs;

execute dbms_logmnr.start_logmnr(dictfilename=>'D:\logminer\dictionary.dic',options=>dbms_logmnr.print_pretty_sql+dbms_logmnr.no_sql_delimiter+dbms_logmnr.ddl_dict_tracking);

create table mylog as select * from v$logmnr_contents;

execute DBMS_LOGMNR.END_LOGMNR();

set lines 1000

set pages 500

column scn format a6

column username format a8

column seg_name format a11

column sql_redo format a33

column sql_undo format a33

select scn , seg_name , sql_redo , sql_undo from mylog where username = 'SCOTT'

AND (seg_owner is null OR seg_owner = 'SCOTT');

==============================================================

2nd TIME STEP

conn scott/tiger

create table scott.test_logmnr (id number,name varchar2(10) );

insert into test_logmnr values (1,'TEST1');

insert into test_logmnr values (2,'TEST2');

commit;

update test_logmnr set name = 'TEST';

commit;

delete from test_logmnr;

commit;

conn / as sysdba

select max(FIRST_CHANGE#),name from v$archived_log group by name order by 2;

execute dbms_logmnr.add_logfile (logfilename => 'D:\ARCHIVE\ARC_1_1_779021390.DBF',options => dbms_logmnr.new);

execute dbms_logmnr.add_logfile (logfilename => 'D:\ARCHIVE\ARC_1_2_779021390.DBF',options => dbms_logmnr.addfile);

execute dbms_logmnr.add_logfile (logfilename => 'D:\ARCHIVE\ARC_1_3_779021390.DBF',options => dbms_logmnr.addfile);

execute dbms_logmnr.add_logfile (logfilename => 'D:\ARCHIVE\ARC_1_4_779021390.DBF',options => dbms_logmnr.addfile);

execute dbms_logmnr.add_logfile (logfilename => 'D:\ARCHIVE\ARC_1_5_779021390.DBF',options => dbms_logmnr.addfile);

execute dbms_logmnr.add_logfile (logfilename => 'D:\ARCHIVE\ARC_1_6_779021390.DBF',options => dbms_logmnr.addfile);

execute dbms_logmnr.add_logfile (logfilename => 'D:\ARCHIVE\ARC_1_7_779021390.DBF',options => dbms_logmnr.addfile);

execute dbms_logmnr.add_logfile (logfilename => 'D:\ARCHIVE\ARC_1_8_779021390.DBF',options => dbms_logmnr.addfile);

execute dbms_logmnr.add_logfile (logfilename => 'D:\ARCHIVE\ARC_1_9_779021390.DBF',options => dbms_logmnr.addfile);

execute dbms_logmnr.add_logfile (logfilename => 'D:\ARCHIVE\ARC_1_10_779021390.DBF',options => dbms_logmnr.addfile);

execute dbms_logmnr.add_logfile (logfilename => 'D:\ARCHIVE\ARC_1_11_779021390.DBF',options => dbms_logmnr.addfile);

execute dbms_logmnr.add_logfile (logfilename => 'D:\ARCHIVE\ARC_1_12_779021390.DBF',options => dbms_logmnr.addfile);

execute dbms_logmnr.add_logfile (logfilename => 'D:\ARCHIVE\ARC_1_13_779021390.DBF',options => dbms_logmnr.addfile);

execute dbms_logmnr.add_logfile (logfilename => 'D:\ARCHIVE\ARC_1_14_779021390.DBF',options => dbms_logmnr.addfile);

execute dbms_logmnr.add_logfile (logfilename => 'D:\ARCHIVE\ARC_1_15_779021390.DBF',options => dbms_logmnr.addfile);

execute dbms_logmnr.add_logfile (logfilename => 'D:\ARCHIVE\ARC_1_16_779021390.DBF',options => dbms_logmnr.addfile);

execute dbms_logmnr.add_logfile (logfilename => 'D:\ARCHIVE\ARC_1_17_779021390.DBF',options => dbms_logmnr.addfile);

select filename from v$logmnr_logs;

execute dbms_logmnr.start_logmnr(dictfilename=>'D:\logminer\dictionary.dic',options=>dbms_logmnr.print_pretty_sql+dbms_logmnr.no_sql_delimiter+dbms_logmnr.ddl_dict_tracking);

drop table mylog;

create table mylog as select * from v$logmnr_contents;

execute DBMS_LOGMNR.END_LOGMNR();

set lines 1000

set pages 500

column scn format a6

column username format a8

column seg_name format a11

column sql_redo format a33

column sql_undo format a33

select scn , seg_name , sql_redo , sql_undo from mylog where username = 'SCOTT'

AND (seg_owner is null OR seg_owner = 'SCOTT');

execute dbms_logmnr.add_logfile (logfilename => 'D:\ARCHIVE\ARC_1_40_779021390.DBF',options => dbms_logmnr.removefile);

execute dbms_logmnr.add_logfile (logfilename => 'D:\ARCHIVE\ARC_1_41_779021390.DBF',options => dbms_logmnr.removefile);

execute dbms_logmnr.add_logfile (logfilename => 'D:\ARCHIVE\ARC_1_42_779021390.DBF',options => dbms_logmnr.removefile);

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

SIMPLE STEPS.----

-------- Create table by user SCOTT
create table abc (id number(10, name varchar2(10));
insert into abc values (1,'ARUN');
alter system switch logfile;
insert into abc values (2,'MOHAN');
alter system switch logfile;
insert into abc values (3,'KARAN');
alter system switch logfile;
update abc set name='TEST' where id=2;
alter system switch logfile;
delete from abc where id in (1,2);
alter system switch logfile;

Note: - You don't know about When / Where and What DML,DDL performing on your database so co-ordinating with development team else as per your guess yesterday performing any
DDL and DML and delete or insert or drop some important objects then in case follow below the steps to recover , register the archive in a logminer as per your guess not sure.
And Give the time as per you guess.

----------------------- Prerequisites ----------------------------

Supplemental logging must be enabled prior to the redo/archive logs are being generated - this option will put additional information to those logs which will be analyzed by LogMiner later.

SQL>ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;
SQL>SELECT SUPPLEMENTAL_LOG_DATA_MIN FROM V$DATABASE;

How do I do that?

We need to do all these being a sys user. Otherwise, some special roles will be required explicitely - EXECUTE_CATALOG_ROLE and SELECT ANY TRANSACTION.

--------- Add Archivelog

EXECUTE DBMS_LOGMNR.ADD_LOGFILE('/u01/app/oracle/archive_standby/1_325_837208662.dbf', DBMS_LOGMNR.ADDFILE);
EXECUTE DBMS_LOGMNR.ADD_LOGFILE('/u01/app/oracle/archive_standby/1_326_837208662.dbf', DBMS_LOGMNR.ADDFILE);
EXECUTE DBMS_LOGMNR.ADD_LOGFILE('/u01/app/oracle/archive_standby/1_327_837208662.dbf', DBMS_LOGMNR.ADDFILE);
EXECUTE DBMS_LOGMNR.ADD_LOGFILE('/u01/app/oracle/archive_standby/1_328_837208662.dbf', DBMS_LOGMNR.ADDFILE);
EXECUTE DBMS_LOGMNR.ADD_LOGFILE('/u01/app/oracle/archive_standby/1_329_837208662.dbf', DBMS_LOGMNR.ADDFILE);

---------Step-2: Start LogMiner with data dictionary information

LogMiner requires data dictionary information to translate Object ID (kept in redo/archive logs) to Object Names when it returns data as a part of data analysis.

The dictionary options are -

1. Using the Online Catalog
2. Extracting a LogMiner Dictionary to the Redo Log Files
3. Extracting a LogMiner Dictionary to the Redo Log Files

I used the online catalog option as I could use the database during off peak hours for log analysis.

EXECUTE DBMS_LOGMNR.START_LOGMNR( -
OPTIONS => DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG);

----------- On our database streams configured and works! To stop LogMiner session we have to stop the capture process.
select capture_name, capture_user from dba_capture;

---------- Step-3: Query LogMiner view to retrieve desired information
The main source of data is V$LOGMNR_CONTENTS. Just describe the view and queried as I wanted.

SELECT OPERATION, SQL_REDO, SQL_UNDO, TIMESTAMP
FROM V$LOGMNR_CONTENTS
WHERE table_name='ABC'
AND TIMESTAMP BETWEEN TO_DATE('26-01-2009 12:00:00','dd-mm-yyyy hh24:mi:ss') AND TO_DATE('26-01-2014 14:00:00','dd-mm-yyyy hh24:mi:ss') ORDER BY TIMESTAMP;

--------- Step-4: Close LogMiner
EXECUTE DBMS_LOGMNR.END_LOGMNR();

Wednesday, 8 August 2012

MATERIALIZED VIEW (Advance Replication)

----SYNTAX

CREATE MATERIALIZED VIEW view-name

BUILD [IMMEDIATE | DEFERRED]

REFRESH [FAST | COMPLETE | FORCE ]

ON [COMMIT | DEMAND ]

[[ENABLE | DISABLE] QUERY REWRITE]

[ON PREBUILT TABLE]

SELECT ...;

Table 1 Refresh Modes

Refresh Mode	Description
ON COMMIT	Refresh occurs automatically when a transaction that modified one of the materialized view's detail tables commits. This can be specified as long as the materialized view is fast refreshable (in other words, not complex). The ON COMMIT privilege is necessary to use this mode
ON DEMAND	Refresh occurs when a user manually executes one of the available refresh procedures contained in the DBMS_MVIEW package (REFRESH, REFRESH_ALL_MVIEWS, REFRESH_DEPENDENT)

Table 2 Refresh Options

Refresh Option	Description
COMPLETE	Refreshes by recalculating the materialized view's defining query.
FAST	Applies incremental changes to refresh the materialized view using the information logged in the materialized view logs, or from a SQL*Loader direct-path or a partition maintenance operation.
FORCE	Applies FAST refresh if possible; otherwise, it applies COMPLETE refresh.
NEVER	Indicates that the materialized view will not be refreshed with refresh mechanisms.

For Complete Refresh, the refresh duration will be in the FULLREFRESHTIM column of the DBA_MVIEW_ANALYSIS. For Fast Refresh duration, it will be in the INCREFRESHTIM column.

Both values are in seconds.

SELECT mview_name, last_refresh_date, fullrefreshtim, increfreshtim

FROM dba_mview_analysis

WHERE owner='SCOTT';

-----Parameter
optimizer_mode = choose, first_rows, or all_rows
job_queue_interval = 3600
job_queue_processes = 1
query_rewrite_enabled = true
query_rewrite_integrity = enforced

# Referesh
exec dbms_mview.refresh('abc_mv'); //Mannual complete refresh
EXECUTE DBMS_MVIEW.REFRESH('emp_dept_sum','F'); //Mannual fast (incremental) refresh (F denotes Fast)
Execute DBMS_MVIEW.REFRESH_DEPENDENT // Refreshes all table-based Oracle materialized views.

-----Test Demontration Non wirtable

create table abc as select * from scott.dept;
create table abc_prim as select * from scott.dept;
alter table abc_prim add constraint PK_abc_PRIM primary key (DEPTNO);

-create materialized view abc_mv as select * from abc; //Error because create materialize view to need base table one primary Key.
-create materialized view abc_mv as select * from abc_prim; //ONLY View not a writable

SQL> insert into dept_mv values(50,'TESTING','MUNICH'); insert into dept_mv values(50,'TESTING','MUNICH')
*
ERROR at line 1:
ORA-01732: data manipulation operation not legal on this view

drop materialized view abc_mv;

--------Test Demontration Updateable MVIEW (Not Refresh Perform DML)
Advantages:
Can be updated even when disconnected from the master site or master materialized view site.
Requires fewer resources than multimaster replication.
Are refreshed on demand. Hence the load on the network might be reduced compared to using multimaster replication because multimaster replication synchronises changes at regular intervalls.

create table abc_prim as select * from scott.dept;
alter table abc_prim add constraint PK_abc_PRIM primary key (DEPTNO);
create materialized view abc_mv FOR UPDATE as select * from abc_prim;
select * from abc_prim;
select * from abc_mv;
insert into abc_mv values(50,'TESTING','MUNICH');
select * from abc_prim;
select * from abc_mv;
select * from abc_mv;
select * from abc_prim

drop table abc_prim;
insert into abc_mv values(50,'TESTING','MUNICH');
select * from abc_prim;
select * from abc_mv;

drop materialized view abc_mv;

----Automatic fast refresh of materialized views (ON COMMIT REFERESH) (with refresh view log)
1.
create table abc_prim as select * from scott.dept;
alter table abc_prim add constraint PK_abc_PRIM primary key (DEPTNO);
create materialized view log on abc_prim;
create materialized view abc_mv REFRESH FAST ON COMMIT DISABLE QUERY REWRITE
as select * from scott.abc_prim;

select * from abc_prim;
select * from abc_mv;
select * from MLOG$_abc_prim;

insert into abc_prim values(50,'TEST','MUNICH');

select * from abc_prim;
select * from abc_mv;
select * from MLOG$_abc_prim;

commit;

select * from abc_prim;
select * from abc_mv;
select * from MLOG$_abc_prim;

2.
CREATE MATERIALIZED VIEW abc_mv1
ENABLE QUERY REWRITE
AS SELECT *
FROM abc_prim
GROUP BY deptno
PCTFREE 5
PCTUSED 60
NOLOGGING PARALLEL 5
TABLESPACE users
STORAGE (INITIAL 50K NEXT 50K)
USING INDEX STORAGE (INITIAL 25K NEXT 25K)
REFRESH FAST
START WITH SYSDATE
NEXT SYSDATE + 1/12;

# Purge MV log
-select * from MLOG$_abc_prim;
-insert into abc_prim values(50,'TEST','MUNICH');
-select * from MLOG$_abc_prim;
-execute DBMS_MVIEW.PURGE_LOG( master => 'T', num => 9999, flag => 'delete' ) ;
-select * from MLOG$_abc_prim;

----------REFRESH GROUPS - CLUBBING RELATED VIEWS

Metalink: http://www.databasejournal.com/features/oracle/article.php/10893_2200191_2/Manually-Refreshing-Materialized-Views-and-Creating-Refresh-Groups-in-Oracle.htm

Metalink: http://www.sqlsnippets.com/en/topic-12880.html

MAKE Make a Refresh Group
ADD Add materialized view to the refresh group
SUBTRACT Remove materialized view from the refresh group
REFRESH Manually refresh the group
CHANGE Change refresh interval of the refresh group
DESTROY Remove all materialized views from the refresh group and delete the refresh group

DBMS_REFRESH - Procedure MAKE

The MAKE procedure is used to create a new Refresh group.

We will make a refresh group my_group_1:
SQL> execute DBMS_REFRESH.MAKE(
name => 'my_group_1',
list => ' mv_market_rate, mv_dealer_rate',
next_date => sysdate,
interval => 'sysdate+1/48');

my_group_1 has two views in its group, mv_market_rate and mv_dealer_rate. Both of these views will be refreshed at an interval of 30 minutes
DBMS_REFRESH - Procedure ADD

Add a snapshot/materialized view to the already existing refresh group:
SQL> execute DBMS_REFRESH.ADD(
name => 'my_group_1',
list => 'mv_borrowing_rate');

------Monitoring materialized views
select
substr(job,1,4) "Job", substr(log_user,1,5) "user",
substr(schema_user,1,5) "schema",
substr(to_char(last_date,'DD-MM-YYYY HH24:MI'),1,16) "last_date",
substr(to_char(next_date,'DD-MM-YYYY HH24:MI'),1,16) "next_date",
substr(broken,1,2) "B" ,substr(failures,1,6) "failed",
substr(what,1,20) "command"
From dba_jobs;

-------Refresh Materialized Views

If a materialized view is configured to refresh on commit, you should never need to manually refresh it, unless a rebuild is necessary. Remember, refreshing on commit is a very intensive operation for volatile base tables. It makes sense to use fast refreshes where possible.

For on demand refreshes, you can choose to manually refresh the materialized view or refresh it as part of a refresh group.

The following code creates a refresh group defined to refresh every minute and assigns a materialized view to it.

BEGIN

DBMS_REFRESH.make(

name => 'SCOTT.MINUTE_REFRESH',

list => '',

next_date => SYSDATE,

interval => '/*1:Mins*/ SYSDATE + 1/(60*24)',

implicit_destroy => FALSE,

lax => FALSE,

job => 0,

rollback_seg => NULL,

push_deferred_rpc => TRUE,

refresh_after_errors => TRUE,

purge_option => NULL,

parallelism => NULL,

heap_size => NULL);

END;

BEGIN

DBMS_REFRESH.add(

name => 'SCOTT.MINUTE_REFRESH',

list => 'SCOTT.EMP_MV',

lax => TRUE);

END;

A materialized view can be manually refreshed using the DBMS_MVIEW package.

EXEC DBMS_MVIEW.refresh('EMP_MV');

Rather than using a refresh group, you can schedule DBMS_MVIEW.REFRESH called using the ORACLE SCHEDULER

Monday, 6 August 2012

Backup Samba Server with Datewise directory

This summary is not available. Please click here to view the post.

BACKUP CRON JOB

RMAN> show all;

RMAN configuration parameters for database with db_unique_name xxxxx are:
CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 15 DAYS;
CONFIGURE BACKUP OPTIMIZATION OFF;
CONFIGURE DEFAULT DEVICE TYPE TO DISK;
CONFIGURE CONTROLFILE AUTOBACKUP ON;
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '+FRA/bsquat/autobackup/%F';
----OR-----
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '/mnt/OracleBackup/BSQUAT/RmanOnline/%F';
CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO COMPRESSED BACKUPSET;
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1;
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1;
CONFIGURE CHANNEL 1 DEVICE TYPE DISK CONNECT '*';
CONFIGURE MAXSETSIZE TO UNLIMITED;
CONFIGURE ENCRYPTION FOR DATABASE OFF;
CONFIGURE ENCRYPTION ALGORITHM 'AES128';
CONFIGURE COMPRESSION ALGORITHM 'BASIC' AS OF RELEASE 'DEFAULT' OPTIMIZE FOR LOAD TRUE;
CONFIGURE ARCHIVELOG DELETION POLICY TO BACKED UP 1 TIMES TO DISK;
CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/u01/app/oracle/product/11.2.0/dbhome_1/dbs/snapcf_bsquat1.f';

------------------------------------------------ Limit Backup Piece size --------------------

RMAN> CONFIGURE CHANNEL 1 DEVICE TYPE DISK CONNECT 'sys/xxxx@target' MAXPIECESIZE = 500M;

----Optional to default auto backup location of Controlfile Configuration .---

RMAN> backup current controlfile format '/mnt/OracleBackup/BSQUAT/RmanOnline/FRA_%d_%T_%U.ctl';
RMAN> backup as backupset spfile format '/mnt/OracleBackup/BSQUAT/RmanOnline/FRA_%d_%T_%U.spfile';

Single Instance RMAN Configuration :

RMAN> show all;

using target database control file instead of recovery catalog

RMAN configuration parameters for database with db_unique_name VOLDEV are:

CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 15 DAYS;
CONFIGURE BACKUP OPTIMIZATION OFF;
CONFIGURE DEFAULT DEVICE TYPE TO DISK;
CONFIGURE CONTROLFILE AUTOBACKUP ON;
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '/mnt/OracleBackup/voldev/RmanOnline/%F';
CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO COMPRESSED BACKUPSET;
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1;
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1;
CONFIGURE CHANNEL 1 DEVICE TYPE DISK FORMAT '/mnt/OracleBackup/voldev/RmanOnline/%d_%D%M%Y_s%s_p%p';
CONFIGURE MAXSETSIZE TO UNLIMITED;
CONFIGURE ENCRYPTION FOR DATABASE OFF;
CONFIGURE ENCRYPTION ALGORITHM 'AES128';
CONFIGURE COMPRESSION ALGORITHM 'BASIC' AS OF RELEASE 'DEFAULT' OPTIMIZE FOR LOAD TRUE;
CONFIGURE ARCHIVELOG DELETION POLICY TO BACKED UP 1 TIMES TO DISK;
CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/u01/app/oracle/product/11.2.0/dbhome_1/dbs/snapcf_voldev.f';

RMAN CRON JOB:

[oracle@vol-oracledev ~]$ crontab -l

00 22 * * 5 /fra/backupScript/backupFRIDAY.sh >> /fra/backupScript/backupFRIDAY.log 2>&1

00 22 * * 1-4 /fra/backupScript/backupMON2THU.sh >> /fra/backupScript/backupMON2THU.log 2>&1

----Script 1
backupFRIDAY.sh

#!/bin/bash
export DATE=$(date +%Y-%m-%d__%A_%H@%M@%S)
export ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1
export ORACLE_SID=voldev
export PATH=$PATH:$ORACLE_HOME/bin
#export NLS_DATE_FORMAT=.DD-MON-YY HH24:MI:SS.
rman catalog rman/xxxx@voldev target sys/xxxxx@voldev msglog /fra/flash_recovery_area/VOLDEV/onlinelog/logs/rman_INCR_0_bk__voldev_${DATE}.log <<EOF

RUN

{
backup incremental level 0 device type disk tag '%TAG' database;
backup device type disk tag '%TAG' archivelog all not backed up;
crosscheck backup;
delete noprompt expired backup;
delete noprompt obsolete device type disk;
crosscheck archivelog all;
delete noprompt expired archivelog all;
}

EXIT;

EOF

-----Script 2
backupMON2THU.sh

#!/bin/bash
export DATE=$(date +%Y-%m-%d__%A_%H@%M@%S)
export ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1
export ORACLE_SID=voldev
export PATH=$PATH:$ORACLE_HOME/bin
#export NLS_DATE_FORMAT=.DD-MON-YY HH24:MI:SS.
rman catalog rman/xxxx@voldev target sys/xxxxx@voldev msglog /fra/flash_recovery_area/VOLDEV/onlinelog/logs/rman_INCR_1_bk__voldev_${DATE}.log <<EOF

RUN

{
backup incremental level 1 device type disk tag '%TAG' database;
backup device type disk tag '%TAG' archivelog all not backed up;
}

EXIT;

EOF

Wednesday, 1 August 2012

ORA-02185: a token other than WORK follows COMMIT

Problem Generate:-
abc.txt
update table xxx set sal=sal+100 where empid=1;
update table xxx set sal=sal+100 where empid=2;
update table xxx set sal=sal+100 where empid=3;
commit;
update table xxx set sal=sal+100 where empid=4;
update table xxx set sal=sal+100 where empid=5;
update table xxx set sal=sal+100 where empid=6;
commit
update table xxx set sal=sal+100 where empid=7;
update table xxx set sal=sal+100 where empid=8;
commit;

spool '/u01/log.log'
@ abc.txt
spool off

check log.log file & see the error

1 row updated

update table xxx set sal=sal+100 where empid=7;
*
ERROR at line 2:
ORA-02185: a token other than WORK follows COMMIT

Solution:-
check your code and see if you add (:) colon instead of ( ; ) semicolon after the COMMIT in your code.

Tuesday, 31 July 2012

TYPE OF SEGMENTS IN ORACLE

Data blocks are used by Oracle in all I/O operations. A segment is composed of one or more extents, but all the data in a table or an index must be contained within a single segment.
Database objects, such as tables and indexes, are held in specific segments. You do not specifically create segments--they are automatically created to support different types of storage.
Types of segments
There are four types of segments used by Oracle:

Data segment: A data segment is created each time you create a table. The number and size of extents
for a data segment is specified in the CREATE TABLE statement.

Index segment: An index segment is created each time you create an index. The number and size of
extents for an index segment is specified in the CREATE INDEX statement. (Ques.Use index)

Temporary segment: A temporary segment may be necessary to provide temporary storage for database
operations, such as sorting. Temporary segments are allocated for the temporary
tablespace of users who require the additional space for temporary storage.

Rollback segment: A rollback segment contains information needed by your Oracle database to roll back
transactions, if necessary. Rollback segments are allocated to the database and cannot
be directly accessed by users or database administrators.

Linux Partion Steps

1.df -h
2.fdisk -l //CHECK THE SPACE
3.fdisk /dev/sda //CHECK THE HD SPACE
4.p check the partion, n new partion ,d for delete partion & enter partion number,m for help
5.e extended partion ,p primary partion 3,logical many partion.
6.p check the partion.
7.w write to disk & exit
8.partprobe /dev/sda
9.mkfs -t ext3 /dev/sda8
10.mkdir /u03
11. cd /
12. ll
13.mount /dev/sda8 /u03
14.df -h
15.vim /etc/fstab //add /u03 entry in this file
/dev/sda8 /u03 ext3 defaults 0 0

16.df -h
17.partprobe /dev/sda
18.mount -a

RHEL / CentOS Support 4GB or more RAM ( memory )

If you have 4 GB or more RAM use the Linux kernel compiled for PAE capable machines. Your machine may not show up total 4GB ram. All you have to do is install PAE kernel package.

This package includes a version of the Linux kernel with support for up to 64GB of high memory. It requires a CPU with Physical Address Extensions (PAE).
The non-PAE kernel can only address up to 4GB of memory. Install the kernel-PAE package if your machine has more than 4GB of memory (>=4GB).
How Do I Install PAE kernel?

To install PAE kernel, use yum command:
# yum install kernel-PAE

Output:
Loading "installonlyn" plugin
Setting up Install Process
Setting up repositories
Reading repository metadata in from local files
Parsing package install arguments
Resolving Dependencies
--> Populating transaction set with selected packages. Please wait.
---> Downloading header for kernel-PAE to pack into transaction set.
kernel-PAE-2.6.18-8.1.15. 100% |=========================| 207 kB 00:00
---> Package kernel-PAE.i686 0:2.6.18-8.1.15.el5 set to be installed
--> Running transaction check
Dependencies Resolved
=============================================================================
Package Arch Version Repository Size
=============================================================================
Installing:
kernel-PAE i686 2.6.18-8.1.15.el5 updates 12 M
Transaction Summary
=============================================================================
Install 1 Package(s)
Update 0 Package(s)
Remove 0 Package(s)
Total download size: 12 M
Is this ok [y/N]: y
Downloading Packages:
(1/1): kernel-PAE-2.6.18- 100% |=========================| 12 MB 00:12
Running Transaction Test
Finished Transaction Test
Transaction Test Succeeded
Running Transaction
Installing: kernel-PAE ######################### [1/1]
Installed: kernel-PAE.i686 0:2.6.18-8.1.15.el5
Complete!

Just reboot the server and make sure you boot with PAE kernel i.e. 2.6.18-8.1.15.el5PAE:
# reboot

MULTIPLE DBWn PARAMETERE DEFINE BECAUSE FREE BUFFER WAITS IN (DB BUFFER CACHE)

This means we are waiting for a free buffer but there are none available in the cache because there are too many dirty buffers in the cache. Causes :

1. Buffer cache is too small

Solutn: - Use AMM/ASMM
- Increase Db buffer cache size as per
- ADDM recommendation
- v$db_cache_advice

2. DBWR is slow in writing modified buffers to disk and is unable to keep up to the write requests.
Solutn: - Increase I/O bandwidth by striping the datafiles.
- If asynchronous I/O is supported .
--Enable asynchronous I/O (DISK_ASYNCH_IO=true)
---- if multiple CPU's are there,
Increase the no. of database writers (DB_WRITER_PROCESSES)
else
Configure I/O slaves (set DBWR_IO_SLAVES to non zero )
else
Configure I/O slaves (set DBWR_IO_SLAVES to non zero )

3. Large sorts and full table scans are filling the cache with modified blocks faster than the DBWR is able to write to disk.
Solutn: - Pre-sorting or reorganizing data can help

=======================================================================

WHEN THE SGA SIZE IS MORE CONSUME AND AT A TIME ONE DBWn PROCESS ,ALL BUFFER CACHE DATA IS
WRITE ON DATAFILE BUT IN CASE YOU WILL EXECUTE SOME QUERIES BUT PROCESS IS NOT ALLOCATED
THE SPACE ON BUFFER CACHE ,SO MULTIPLE DBWn IS WORK FAST TWO OR MORE PROCESS IS WRITE A DATA
ON DATAFILE PARALLELY AND PROCESS ALLOCATE A SPACE ON BUFFER CACHE.

PARAMETER IS DEFINE ON THIS FILE:-
( Oracle I_O Slave Waits dbwr parallel DML )

SHOW PARAMETER DB_WRITER_%

SHOW PARAMETER DBWR_IO_SLAVES

Parameter type Integer

Default value 0

Parameter class Static

Range of values 0 to operating system-dependent

DBWR_IO_SLAVES is relevant only on systems with only one database writer process (DBW0). It specifies the number of I/O server processes used by the DBW0 process. The DBW0 process and its server processes always write to disk. By default, the value is 0 and I/O server processes are not used.

If you set DBWR_IO_SLAVES to a nonzero value, the number of I/O server processes used by the ARCH and LGWR processes is set to 4. However, the number of I/O server processes used by Recovery Manager is set to 4 only if asynchronous I/O is disabled (either your platform does not support asynchronous I/O or disk_asynch_io is set to false.

Typically, I/O server processes are used to simulate asynchronous I/O on platforms that do not support asynchronous I/O or that implement it inefficiently. However, you can use I/O server processes even when asynchronous I/O is being used. In that case the I/O server processes will use asynchronous I/O.

I/O server processes are also useful in database environments with very large I/O throughput, even if asynchronous I/O is enabled.

NOTE:-
======
IT MEANS THE "DBWR_IO_SLAVES" INITIAL 2 OR 3 PROCESS ARE EXCUTES PARALLELY
IF THE "DBWR_IO_SLAVES" NOT INITIALIZED THE PROCESS ARE EXECUTED IN A QUEUE
IN THIS WORK ON "DB_WRITER_PROCESS" PARAMETER INITIAL FIRST .

TABLESPACE TRANSPORTATION FROM DATABASE VINAY TO ORCL1

[oracle@localhost ~]$ export ORACLE_SID=vinay
[oracle@localhost ~]$ sqlplus '/ as sysdba'

SQL*Plus: Release 10.2.0.1.0 - Production on Fri Sep 24 12:37:22 2010

Copyright (c) 1982, 2005, Oracle. All rights reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options

SQL> select name from v$database;

NAME
---------
vinay

SQL> select status from v$instance;

STATUS
------------
OPEN

12:25:33 SQL> select username,default_tablespace from dba_users;

--make sure sid will tablespace name and sid.dbf file will be the same name of tablespace then tablespace can be created only...

12:26:02 SQL> create tablespace sid datafile '/u01/app/oracle/oradata/vinay/sid1.dbf' size 10m;
Tablespace created.

12:27:57 SQL> create user tts_user identified by tts default tablespace sid;
User created.

--now we will check this user has been created in the same tablespace which we have just created..

12:28:32 SQL> select username,default_tablespace from dba_users;

USERNAME DEFAULT_TABLESPACE
------------------------------ ------------------------------
MGMT_VIEW SYSTEM
SYS SYSTEM
SYSTEM SYSTEM
DBSNMP SYSAUX
SYSMAN SYSAUX
HR USERS
TTS_USER SID
OUTLN SYSTEM
MDSYS SYSAUX
ORDSYS SYSAUX
EXFSYS SYSAUX

--Now we will check our created tablespace is online or offline of readonly so that....????

12:29:50 SQL> select tablespace_name,status from dba_tablespaces;

TABLESPACE_NAME STATUS
------------------------------ ---------
SYSTEM ONLINE
UNDOTBS1 ONLINE
SYSAUX ONLINE
TEMP ONLINE
USERS ONLINE
EXAMPLE ONLINE
TBSALERT ONLINE
TTS READ ONLY
SID ONLINE

9 rows selected.

--after checking it online we will make it Read Only Istead of online...

12:30:17 SQL> alter tablespace sid read only;
Tablespace altered.

12:30:33 SQL> exec dbms_tts.transport_set_check('SID');

PL/SQL procedure successfully completed.

--THIS THE PACKAGE WE ARE RUNNING HERE TO verify is it able to transport or not(package)...IF IT IS SUCCESSFULLY COMPLETED THEN WE CAN MOVE TO ANY DATABASE...WE WILL ALSO CHECK THROUGH THIS STATMENT TO CHECK THE VOILATIONS IF ANY IT WILL SHOW OR IT WILL SAY NO ROWS SELECTED MEANS WE CAN GO AHEAD SUSSESSFULLY...

12:31:03 SQL> select * from transport_set_violations;

no rows selected

--it will check is there any object created by sys in current tablespace or not if yes then it wont allow..

12:31:20 SQL> host pwd
/home/oracle

12:31:29 SQL> host exp file=sid.dmp transport_tablespace=Y tablespaces=sid

Export: Release 10.2.0.1.0 - Production on Fri Sep 24 12:32:01 2010

Copyright (c) 1982, 2005, Oracle. All rights reserved.

Username: / as sysdba

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
Export done in US7ASCII character set and AL16UTF16 NCHAR character set
server uses WE8ISO8859P1 character set (possible charset conversion)
Note: table data (rows) will not be exported
About to export transportable tablespace metadata...
For tablespace SID ...
. exporting cluster definitions
. exporting table definitions
. exporting referential integrity constraints
. exporting triggers
. end transportable tablespace metadata export
Export terminated successfully without warnings.

12:32:18 SQL> alter tablespace sid online;

Tablespace altered.

12:32:54 SQL>

[oracle@localhost ~]$ export ORACLE_SID=orcl1
[oracle@localhost ~]$ sqlplus '/ as sydba'

SQL*Plus: Release 10.2.0.1.0 - Production on Fri Sep 24 12:37:15 2010

Copyright (c) 1982, 2005, Oracle. All rights reserved.

SQL*Plus: Release 10.2.0.1.0 - Production

Copyright (c) 1982, 2005, Oracle. All rights reserved.
[oracle@localhost ~]$ sqlplus '/ as sysdba'

SQL*Plus: Release 10.2.0.1.0 - Production on Fri Sep 24 12:37:22 2010

Copyright (c) 1982, 2005, Oracle. All rights reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options

--THIS IS VERY IMPORTANT STEP TO COPY THE sid1.dbf FILE FROM FIRST DATABSE & PAST IT INTO SECOND DATABASE...
--now go to oradata folder n from first database copy the first datafile sid1.dbf and past into second database .

SQL> select name from v$database;

NAME
---------
ORCL1

SQL> select status from v$instance;

STATUS
------------
OPEN

SQL> create user tts_user identified by tts_user; --Here we will create same user which has been created in first db.

User created.

SQL> grant connect,resource to tts_user;

Grant succeeded.

SQL> select tablespace_name from dba_tablespaces;

TABLESPACE_NAME
------------------------------
SYSTEM
UNDOTBS1
SYSAUX
TEMP
USERS
EXAMPLE

6 rows selected.

--we confirmed it no such table space availabe which we created above in current databse...

--Now we r going to import the datafile & tablespace so make sure here we will give orcl1 which is current database....

SQL> host imp file=sid.dmp transport_tablespace=Y tablespaces=sid datafiles='/u01/app/oracle/oradata/orcl1/sid1.dbf'

Import: Release 10.2.0.1.0 - Production on Fri Sep 24 12:42:03 2010

Copyright (c) 1982, 2005, Oracle. All rights reserved.

Username: / as sysdba

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options

Export file created by EXPORT:V10.02.01 via conventional vinay
About to import transportable tablespace(s) metadata...
import done in US7ASCII character set and AL16UTF16 NCHAR character set
import server uses WE8ISO8859P1 character set (possible charset conversion)
. importing SYS's objects into SYS
. importing SYS's objects into SYS
Import terminated successfully without warnings.

SQL> select tablespace_name,status from dba_tablespaces;

TABLESPACE_NAME STATUS
------------------------------ ---------
SYSTEM ONLINE
UNDOTBS1 ONLINE
SYSAUX ONLINE
TEMP ONLINE
USERS ONLINE
EXAMPLE ONLINE
SID READ ONLY

7 rows selected.

SQL> alter tablespace sid read write;

Tablespace altered.

SQL> conn tts_user/tts_user
Connected.
-----------------------------one more time practice detials given below date was-5-feb-2011-------------

[oracle@localhost ~]$ export ORACLE_SID=orcl2
[oracle@localhost ~]$ sqlplus '/as sysdba'

SQL*Plus: Release 10.2.0.1.0 - Production on Sat Feb 5 19:10:33 2011

Copyright (c) 1982, 2005, Oracle. All rights reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options

SQL> conn hr/hr
Connected.
SQL> conn as sysdba
Enter user-name: sys
Enter password:
Connected.
SQL> show user
USER is "SYS"
SQL> select name from v$database;

NAME
---------
ORCL2

SQL> select status from v$instance;

STATUS
------------
OPEN

SQL> select username,default_tablespace from dba_users;

USERNAME DEFAULT_TABLESPACE
------------------------------ ------------------------------
MDSYS SYSAUX
ORDSYS SYSAUX
EXFSYS SYSAUX
DMSYS SYSAUX
DBSNMP SYSAUX
SCOTT USERS
WMSYS SYSAUX
TSMSYS USERS
BI USERS
PM USERS
MDDATA USERS

USERNAME DEFAULT_TABLESPACE
------------------------------ ------------------------------
IX USERS
CTXSYS SYSAUX
ANONYMOUS SYSAUX
SH USERS
OUTLN SYSTEM
DIP USERS
OE USERS
HR USERS
SYSMAN SYSAUX
XDB SYSAUX
ORDPLUGINS SYSAUX

USERNAME DEFAULT_TABLESPACE
------------------------------ ------------------------------
MGMT_VIEW SYSTEM
SI_INFORMTN_SCHEMA SYSAUX
OLAPSYS SYSAUX
SYS SYSTEM
SYSTEM SYSTEM

27 rows selected.

SQL> Create TableSpace Saba datafile '/u01/app/oracle/oradata/orcl2/saba1.dbf' size 10m;

Tablespace created.

SQL> create user sabau identified by salim default tablespace saba;

User created.

SQL> select username,default_tablespace from dba_users;

USERNAME DEFAULT_TABLESPACE
------------------------------ ------------------------------
MDSYS SYSAUX
ORDSYS SYSAUX
EXFSYS SYSAUX
DMSYS SYSAUX
DBSNMP SYSAUX
SCOTT USERS
WMSYS SYSAUX
TSMSYS USERS
SABAU SABA
BI USERS
PM USERS

USERNAME DEFAULT_TABLESPACE
------------------------------ ------------------------------
MDDATA USERS
IX USERS
CTXSYS SYSAUX
ANONYMOUS SYSAUX
SH USERS
OUTLN SYSTEM
DIP USERS
OE USERS
HR USERS
SYSMAN SYSAUX
XDB SYSAUX

USERNAME DEFAULT_TABLESPACE
------------------------------ ------------------------------
ORDPLUGINS SYSAUX
MGMT_VIEW SYSTEM
SI_INFORMTN_SCHEMA SYSAUX
OLAPSYS SYSAUX
SYS SYSTEM
SYSTEM SYSTEM

28 rows selected.

SQL> host emctl start dbconsole
TZ set to Asia/Calcutta
Oracle Enterprise Manager 10g Database Control Release 10.2.0.1.0
Copyright (c) 1996, 2005 Oracle Corporation. All rights reserved.
http://localhost.localdomain:5500/em/console/aboutApplication
- An instance of Oracle Enterprise Manager 10g Database Control is already running.

SQL> alter tablespace saba read only;

Tablespace altered.

SQL> exe dbms_tts.transport_set_check('saba');
SP2-0734: unknown command beginning "exe dbms_t..." - rest of line ignored.
SQL> exec dbms_tts.transport_set_check('saba');

PL/SQL procedure successfully completed.

SQL> select *from transport_set_violations;

no rows selected

SQL> host pwd
/home/oracle

SQL> host exp file=saba.dmp transport_tabaspace=Y tablespaces=saba;
LRM-00101: unknown parameter name 'transport_tabaspace'

EXP-00019: failed to process parameters, type 'EXP HELP=Y' for help
EXP-00000: Export terminated unsuccessfully

SQL> host exp file=saba.dmp transport_tablespace=Y tablespaces=saba;

Export: Release 10.2.0.1.0 - Production on Sat Feb 5 19:29:08 2011

Copyright (c) 1982, 2005, Oracle. All rights reserved.

Username: / as sysdba

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
Export done in US7ASCII character set and AL16UTF16 NCHAR character set
server uses WE8ISO8859P1 character set (possible charset conversion)
Note: table data (rows) will not be exported
About to export transportable tablespace metadata...
For tablespace SABA ...
. exporting cluster definitions
. exporting table definitions
. exporting referential integrity constraints
. exporting triggers
. end transportable tablespace metadata export
Export terminated successfully without warnings.

SQL> alter tablespace saba online;

Tablespace altered.

SQL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
[oracle@localhost ~]$ export ORACLE_SID=orcl1
[oracle@localhost ~]$ sqlplus '/as sysdba'

SQL*Plus: Release 10.2.0.1.0 - Production on Sat Feb 5 19:32:58 2011

Copyright (c) 1982, 2005, Oracle. All rights reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options

SQL> select name from v$database;

NAME
---------
ORCL1

SQL> select status from v$instance;

STATUS
------------
OPEN

SQL> create user sabau identified by salim;

User created.

SQL> Grant connect,resource to sabau;

Grant succeeded.

SQL> select tablespace_name from dba_tablespaces;

TABLESPACE_NAME
------------------------------
SYSTEM
UNDOTBS1
SYSAUX
TEMP
USERS
EXAMPLE
SAMDBA

7 rows selected.

SQL> host imp file=saba.dmp transport_tablespace=Y tablespaces=saba datafiles='/u01/app/oracle/oradata/orcl1/saba1.dbf';

Import: Release 10.2.0.1.0 - Production on Sat Feb 5 19:52:28 2011

Copyright (c) 1982, 2005, Oracle. All rights reserved.

Username: / as sysdba

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options

Export file created by EXPORT:V10.02.01 via conventional vinay
About to import transportable tablespace(s) metadata...
import done in US7ASCII character set and AL16UTF16 NCHAR character set
import server uses WE8ISO8859P1 character set (possible charset conversion)
. importing SYS's objects into SYS
. importing SYS's objects into SYS
Import terminated successfully without warnings.

SQL> select tablespace_name , status from dba_tablespaces;

TABLESPACE_NAME STATUS
------------------------------ ---------
SYSTEM ONLINE
UNDOTBS1 ONLINE
SYSAUX ONLINE
TEMP ONLINE
USERS ONLINE
EXAMPLE ONLINE
SAMDBA ONLINE
SABA READ ONLY

8 rows selected.

SQL> alter tablespace saba read write;

Tablespace altered.

SQL> conn sabau/salim
Connected.
SQL> create table saba(R Number);

Table created.

SQL> Insert Into saba values(1);

1 row created.

SQL> /

1 row created.

SQL> commit;

Commit complete.

SQL> exit;
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
[oracle@localhost ~]$
---Run Programm successufully.....

Monday, 30 July 2012

DB BUFFER CACHE

Latches are internal memory structures that coordinate access to shared resources. Locks (also known as enqueues) are different from latches, the key difference being that enqueues, as the name suggests, provide a FIFO queueing mechanism, while latches do not. On the other hand, latches are held very briefly and locks are usually held longer.

In Oracle SGA, the buffer cache is the memory area into which data blocks are read. (If Automatic Shared Memory Management [ASMM] is in use, part of the shared pool can be tagged as KGH:NO ALLOC and remapped to the buffer cache area too.)

Each buffer in the buffer cache has an associated element in the buffer header array, externalized as x$bh. Buffer headers keep track of various attributes and state of buffers in the buffer cache. This buffer header array is allocated in the shared pool. The buffer headers are chained together in a doubly-linked list and linked to a hash bucket. There are many hash buckets, and their number is derived and governed by the _db_block_hash_buckets parameter). Access to these hash chains (both to inspect and change) is protected by cache-buffers-chains latches.

Furthermore, buffer headers can be linked and delinked from hash buckets dynamically.

Here is a simple algorithm to access a buffer (I had to deliberately cut out so as not to deviate too much from our primary discussion):

1. Hash the data block address (DBAs: a combination of tablespace, file_id and block_id) to find hash bucket.
2. Get the latch protecting the hash bucket.
3. If success,
walk the hash chain (hash bucket),
reading buffer headers (shared pool) to see if a specific version of the block is already in the chain.
If found, access the buffer in buffer cache, with protection of buffer pin/unpin actions.

If not found,
then find a free buffer in buffer cache,
unlink the buffer header for that buffer from its current chain,
link that buffer header with this hash chain,
release the latch and read block in to that free buffer in buffer cache with buffer header pinned.

4. If not success, spin for spin_count times and go to step 2. If that latch was not got with spinning, then sleep (with exponentially increasing sleep time with an upper bound), wakeup, and go to step 2.

Obviously, latches are playing crucial role in controlling access to critical resources such as the hash chain. My point is that repeated access to a few buffers can increase latch activity.

There are many CBC latch children. The parameter _db_block_hash_latches controls the number of latches and is derived from the buffer cache size. Furthermore, in Oracle 10g, shareable latches are used; and inspecting a hash chain necessitates acquiring latches in shared mode, which is compatible with other shared-mode operations. Note that these undocumented parameters are usually sufficient, and changes to these parameters must get approval from Oracle support.

Wednesday, 25 July 2012

Index Rebuild

Check The Delete Leaf/Rows for the purpose of cosume Indexes & query take more time
after that rebuild when index usage more than 20.

* INDEX_STATS
* USER_INDEXES

select 'Analyze Index '||table_owner||'.'||index_name||' Validate Structure ; '
from user_indexes
where table_owner='SCOTT' order by index_name;

Select Name,(DEL_LF_ROWS_LEN/LF_ROWS_LEN)*100 as index_usage From index_stats Where LF_ROWS_LEN!=0 order by Name ;

spool c:\temp\indexusage.log
ANALYZE INDEX <index_name> COMPUTE STATISTICS;
Analyze Index scott.<index_name> Validate Structure ;

Select Name,(DEL_LF_ROWS_LEN/LF_ROWS_LEN)*100 as index_usage From index_stats Where LF_ROWS_LEN!=0 order by Name ;
ANALYZE INDEX <index_name> COMPUTE STATISTICS;

Analyze Index scott.<index_name> Validate Structure ;

Select Name,(DEL_LF_ROWS_LEN/LF_ROWS_LEN)*100 as index_usage From index_stats Where LF_ROWS_LEN!=0 order by Name ;
ANALYZE INDEX <index_name> COMPUTE STATISTICS;

Analyze Index scott.<index_name> Validate Structure ;

Select Name,(DEL_LF_ROWS_LEN/LF_ROWS_LEN)*100 as index_usage From index_stats Where LF_ROWS_LEN!=0 order by Name ;
ANALYZE INDEX <index_name> COMPUTE STATISTICS;

Analyze Index scott.<index_name> Validate Structure ;

Select Name,(DEL_LF_ROWS_LEN/LF_ROWS_LEN)*100 as index_usage From index_stats Where LF_ROWS_LEN!=0 order by Name ;
ANALYZE INDEX <index_name> COMPUTE STATISTICS;

Analyze Index scott.<index_name> Validate Structure ;

Select Name,(DEL_LF_ROWS_LEN/LF_ROWS_LEN)*100 as index_usage From index_stats Where LF_ROWS_LEN!=0 order by Name ;
spool off;

alter index scott.<index_name> rebuild ;

alter index scott.<index_name> rebuild online;

ALTER INDEX YYYYY REBUILD TABLESPACE INDX;

Tuesday, 24 July 2012

Windows Server Memory Limits

1) Windows Server 2008 Web

2) Windows Server 2008 Standard

3) Windows Server 2008 Enterprise

4) Windows Server 2008 Datacenter

http://www.microsoft.com/en-us/server-cloud/windows-server/2008-r2-datacenter.aspx

Windows support for physical memory is dictated by hardware limitations, licensing, operating system data structures, and driver compatibility. The Memory Limits for Windows Releases page in MSDN documents the limits for different Windows versions, and within a version, by SKU.

You can see physical memory support licensing differentiation across the server SKUs for all versions of Windows. For example, the 32-bit version of Windows Server 2008 Standard supports only 4GB, while the 32-bit Windows Server 2008 Datacenter supports 64GB. Likewise, the 64-bit Windows Server 2008 Standard supports 32GB and the 64-bit Windows Server 2008 Datacenter can handle a whopping 2TB. There aren't many 2TB systems out there, but the Windows Server Performance Team knows of a couple, including one they had in their lab at one point. Here's a screenshot of Task Manager running on that system:

The maximum 32-bit limit of 128GB, supported by Windows Server 2003 Datacenter Edition, comes from the fact that structures the Memory Manager uses to track physical memory would consume too much of the system's virtual address space on larger systems. The Memory Manager keeps track of each page of memory in an array called the PFN database and, for performance, it maps the entire PFN database into virtual memory. Because it represents each page of memory with a 28-byte data structure, the PFN database on a 128GB system requires about 980MB. 32-bit Windows has a 4GB virtual address space defined by hardware that it splits by default between the currently executing user-mode process (e.g. Notepad) and the system. 980MB therefore consumes almost half the available 2GB of system virtual address space, leaving only 1GB for mapping the kernel, device drivers, system cache and other system data structures, making that a reasonable cut off:

That's also why the memory limits table lists lower limits for the same SKU's when booted with 4GB tuning (called 4GT and enabled with the Boot.ini's /3GB or /USERVA, and Bcdedit's /Set IncreaseUserVa boot options), because 4GT moves the split to give 3GB to user mode and leave only 1GB for the system. For improved performance, Windows Server 2008 reserves more for system address space by lowering its maximum 32-bit physical memory support to 64GB.

The Memory Manager could accommodate more memory by mapping pieces of the PFN database into the system address as needed, but that would add complexity and possibly reduce performance with the added overhead of map and unmap operations. It's only recently that systems have become large enough for that to be considered, but because the system address space is not a constraint for mapping the entire PFN database on 64-bit Windows, support for more memory is left to 64-bit Windows.

The maximum 2TB limit of 64-bit Windows Server 2008 Datacenter doesn't come from any implementation or hardware limitation, but Microsoft will only support configurations they can test. As of the release of Windows Server 2008, the largest system available anywhere was 2TB and so Windows caps its use of physical memory there.

Labels

Thursday, 9 August 2012

Wednesday, 8 August 2012

Monday, 6 August 2012

Wednesday, 1 August 2012

Tuesday, 31 July 2012

Monday, 30 July 2012

Wednesday, 25 July 2012

Tuesday, 24 July 2012