Standby Database

Way to Install Stand by database in Oracle Oracle 8i

If you choose to implement a managed recovery environment, then you must connect the primary and standby database through Net8. Log file should be small for data lose.Always copy the data file first then startup the database and create the control file for standby database. Time stamp of the control file should be higher than data file.

How to create standby control file from Primary Database

ALTER DATABASE CREATE STANDBY CONTROLFILE AS '/oracle/temp/stbycf.ctl';

Configuring the tnsnames.ora file on the PRIMARY site:

cd $ORACLE_HOME/network/admin

Add the following entry:
Standby_service_name =(DESCRIPTION=
(ADDRESS=(PROTOCOL=tcp) (POST=port_number) (HOST=host_name))
(CONNECT_DATA=(SID=standby_sid))
)
Example:
abc.office.abc.com=(DESCRIPTION=
(ADDRESS=
(PROTOCOL=tcp)
(PORT=1521)
(HOST=op2op))
(CONNECT_DATA=
(SID=TEST))
)

Configuring Init.ora file in the PRIMARY Database:


LOG_ARCHIVE_DEST_n = maximum 5 location you can mention'

LOG_ARCHIVE_DEST_1 ='LOCATION=/oracle/arc/'

(Example: LOG_ARCHIVE_DEST_1 ='LOCATION=/oracle/arc  MANDATORY')

LOG_ARCHIVE_DEST_1 = 'LOCATION=/oracle/arch  mandatory'
LOG_ARCHIVE_DEST_2 ='SERVICE=abc.office.abc.com mandatory REOPEN=60'

(MANDATORY OR OPTIONAL ---DEFAULT IS OPTIONAL USUALLY LOCAL DEST IS MANDATORY REOPEN =s (seconds) where s is the interger option specifies the minimum number of seconds before the archive process should try to re access a failed destination.REOPEN applies all error not just OPEN error. Default values is 300)


LOG_ARCHIVE_DEST_STATE_1 = ENABLE
LOG_ARCHIVE_DEST_STATE_2 = ENABLE


Configuring Init.ora file in the STANDBY Database:

COMPATIBLE = 8.1.6

CONTROL_FILES = /oracle/standby/stbycf.ctl

DATABASE_NAME=TEST

LOG_ARCHIVE_DEST=/oracle/standby/arch_dest (or same as production)

LOG_ARCHIVE_FORMAT =arc_%t_%s.arc (or same as production)

LOG_ARCHIVE_TRACE = 6

(Optionally, set this parameter to an integer value to see the progression of the archiving of redo logs to the standby site.Oracle writes an audit trail of the archived logs received from the primary database into a trace file.The parameter controls output generated by the archiving process (ARCn and foreground processes).0 is default.
STANDBY_ARCHIVE_DEST = /oracle/standby/arch_dest/ (or same as production)

Configuring the listener.ora file on the STANDBY site:

cd $ORACLE_HOME/network/admin

Add the follwing entry:

STANDBY_LISTENER = (ADDRESS_LIST=
(ADDRESS=(PROTOCOL=tcp) (POST=port_number) (HOST=host_name))
)

Example:

STANDBY_LISTENER = (ADDRESS_LIST=
(ADDRESS=(PROTOCOL=tcp) (POST=1521) (HOST=op2op))
)

SID_LIST_STANDBY_LISTENER = (SID_LIST=
(SID_DESC=(SID_NAME=standby_sid_name) (ORACLE_HOME/oracle_home))
)

Example:

SID_LIST_STANDBY_LISTENER = (SID_LIST=
(SID_DESC=(SID_NAME=TEST) (ORACLE_HOME/oracle))
)

Stop and restart the listner:

Lsnrctl start

Lsnrctl status

How to create make standby database in constant recovery managed mode

SQL*plus> RECOVER MANAGED STANDBY DATABASE;

You can make standby database from recovery managed mode to read only and back to managed mode

ALTER DATABASE OPEN READ ONLY;

RECOVER MANAGED STANDBY DATABASE;

Open Standby Database Command

ALTER DATABASE ACTIVATE STANDBY DATABASE;

You can also set "RCP mode to copy redolog file from one database to another


Say script name is standby.sh and the contains inside of the scripts is below:

export ORACLE_SID=databasename
export ORACLE_HOME=/app/oracle/product/8.1.6

export PATH=$ORACLE_HOME/bin:$PATH
export HOME=/where the shell script is present

remsh  -l oracle production hostname 'ls -rt1   /archive destenation' egrep|  -v '.Z' | tail  -1 > $HOME/.maxnum

[example : remsh - oracle tcs3op 'ls -rt1 /oracle/Backup/arch' | egrep -v '.Z' | tail -1 > $HOME.maxnum]
if [ ! -a $HOME/.maxnum ] then #print "Can not get maxnum'" exit 1 fi
let max=$(cat $HOME/.maxnum | cut -d. -f1 | cut -c4-)
let curr=$(cat $HOME/.curnum)
print "**********************" >> $HOME/stand.loh
While  (( curr < max)) ; do
let curr=curr+1
rcp hostname:/log_archive_dest directory.arc /standby machine archive directory [can be same ]
print `date` "databasename${$curr}.arc  >>  $HOME/standby.log
done
if [ -f $ORACLE_HOME/bin/svrmgrl ] then
   DBAMGR="svrmgrl"
else
   DBAMGR='sqldba lmode=y"
fi

$ORACLE_HOME/bin/$DBMGR  <<  EOF
connect  internal
set autorecovery on
recover standby database;
exit;
EOF

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Trace On and Off

You can set sql trace at session level ,instance level and session id level:
1. session level
SQL*Plus> Alter session set sql_trace=true;
SQL*Plus> Alter database set TIME_STATISTICS=TRUE;
After that find out the latest .trc file from user_dump_dest and run tkprof command.
2.Instance level
shutdown the database
init.ora sql_trace=true.
startup the database
3.ID level
Select username,sid,serial# from v$session;
execute sys.dbms_system.set_sql_trace_in_session(&SID ,&SERIAL , TRUE);
Under user_dump_dest rename the latest .trc file. (ls -lrt Unix command last file )
execute sys.dbms_system.set_sql_trace_in_session(&SID ,&SERIAL , FALSE);

filename.trc is the filename.trc
tkprof outputfilename sys=no explain=system/password
tkprof outputfilename sys=no aggregate=yes sort ='(prsela,exeela,fchela)'

 

 

 

 

 

 

Export and Import Parfile and command

Export par file

USERID=userid/password
BUFFER=10000000
LOG=anyfilename.log
FULL=Y
FILE=export filename.dmp
GRANTS=Y
INDEXES=Y

exp parfile=filename.par

New Feature in Oracle 8i you can extract data selective way.
Example:
USERID=userid/password
BUFFER=10000000
LOG=anyfilename.log
FULL=N
FILE=exportfilename.dmp
GRANTS=Y
INDEXES=Y
Tables=(Owner.Table Name)
QUERY="where job='DBA' and sal  <  1000"
In line mode use  \
QUERY=\"where job=\'DBA\' and sal\<1000\"

Put \ for every wild character

    If your file system is limited size you can create number of export files based on the file system size.
  • Example:
    USERID=userid/password
    BUFFER=10000000
    LOG=anyfilename.log
    FULL=N
    FILE=/directory name/exportfile1.dmp , /another directory name/exportfile2.dmp
    SILESIZE=2099999744 [2GB size] or depending on file size     GRANTS=Y
    INDEXES=Y
    Import parfile

    USERID=sys/change_on_install
    log=ps.log
    FULL=N
    FILE=psexp.dmp
    GRANTS=Y
    COMMIT=Y
    INDEXES=Y
    IGNORE=Y
    FROMUSER=PS
    TOUSER=PS

    imp parfile=filename.par

    To get all the parameters type at the unix level imp help=y or exp help=y

     

     

     

     

     

     

     

     

    Explain Plan script

    First create explain table .Goto $ORACLE_HOME/rdbms/admin/@utlxpls.sql
    This will create plan_table in your schema.
    Then put this command before your sql select statement
    explain plan set statement_id='your name'
    for select ......
    Then run the select statement below which will show you the index access process.


    select  lpad('  ',2*(level-1))||operation||'  '||options||'  '||object_name
    ||'  '||decode(id,0,'Cost= '||position)  "Query Plan"
    from plan_table
    start with id = 0
    and statement_id ='yourshortname'
    connect by prior id = parent_id
    and statement_id ='yourshortname'
    /

     

     

     

    Create Dblink,Synonymn,Grant,Role

    CREATE DATABASE LINK &DB_LINK  [dblink name]
       CONNECT TO &USERNAME [target database username]  IDENTIFIED BY &PASSWORD USING '&CONNECT';

    create public synonym  [actual tablename] for  [wner.actual table name];

    grant {select,update,insert,all ..etc} on to {role or user};
    grant  [role]  to user;

    Example:

    create public synonym PLAN_TABLE for Actual owner.PLAN_TABLE;

    grant select on owner.plan_table to user;

    create role (rolename say admin);

    grant select on owner.plan_table to admin;

    grant admin to user;


     

     

     

    Setup Nonarchive log to Archivelog and Hotbackup

    Modify INIT.ORA file.Make sure the following parameters are seted correctly

     LOG_ARCHIVE_START=true
     LOG_ARCHIVE_DEST =/arch/arch #where the log files is going to write
     LOG_ARCH_FORMAT = %t%s or do not mention let it be as default.
    t=timestamp s=sequencenumber

    GOTO svrmgrl
    connect internal;


    STARTUP MOUNT
    ALTER DATABASE ARCHIVELOG;
    ALTER DATABASE OPEN;
    ALTER SYSTEM SWITCH LOGFILE;
    ALTER SYSTEM SWITCH LOGFILE;
    ARCHIVELOG MODE

    A REDO log file CANNOT be reused until REDO log file has been physically backed up by the ARCH background process.

    The most recent changes to the database are available at any time for instance recovery and the copies of old REDO LOGs can be used for media failure.

    Every Redo log file can be identified by a unique Log Sequence Number.Log Sequence Number begin with 1 and increment by 1 every time a new redo log file is opened.

    Database can be backed up while still on-line

    Alter tablespace Begin backup;
    goto operating system  [unix or DOS]  lable and copy tar backup
    Alter tablespace End backup;

    To see status,log sequence number of current on-line log files

    SVRMGRL>ARCHIVE LOG LIST
    Database log mode  Archive Mode
    Automatic archival   Enabled
    Archive destination   /arch/arch_log
    Oldest online log sequence   9719
    Next log sequence to archive   9722
    Current log sequence     9722

     

     

     

    Tablespace and Datafiles

    You can see all the Tablespace name and Datafiles in this table:
    DBA_TABLESPACES;

    DBA_DATA_FILES;

    CREATE TABLESPACE
    Before creating any tablespace the data volume should be present.
    An example: If you have created a data volume in RAW partion 4MB.When ever you wants to create a tablespace in that volume you must use maximum size of the volume.Say if have created a tablespace of 2MB out of 4MB volume, in future you can not use another 2MB unless drop the tablespace and recreate again.Thats the disadvantages in RAW partitions for storage usages.

    Syntax:

    CREATE TABLESPACE tablespace_name
      DATAFILE filespec
       [ AUTOEXTEND { OFF
        | ON [NEXT integer [K|M]]
        [MAXSIZE [UNLIMITED | integer [K|M]] } ]

    [, filespec ...]
    [DEFAULT STORAGE storage_clause]
    [ONLINE | OFFLINE]
    [PERMANENT | TEMPORARY]

    create tablespace ANALYST
       datafile '/dev/vx/rdsk/array1/analyst' size 10477568
      default storage (initial 8192 next 8192 maxextents 249 pctincrease 1);

    Alter tablespace ANALYST
       add datafile ‘datafile '/dev/vx/rdsk/array1/analyst' size 10477568;

    You can also alter the datafile:

    alter database resize '/datafile.dbf' 20MB;
    create tablespace ANALYST
       datafile '/dev/vx/rdsk/array1/analyst.dbf’ size 2MB
       default storage (initial 8192 next 8192 maxextents 249 pctincrease 1);

    Pctincrease 1 means SMON will coalase automatically .Usually we put pctincrease 0 by default is 50

    Initial extent + 50%of Initial extent = next extent for pctincrease 50

    Initial extent + 0.Initial extent = Next extent which is same as Initial extent for pctincrease 0

    DROP TABLESPACE tablespace name
       [INCLUDING CONTENTS [CASCADE CONSTRAINTS]]

     

     

     

    Tablespace and Datafiles

    Note: Minimum one rollback segment required for database creation:
    You can see all the rollback segments in this table:
    DBA_ROLLBACK_SEGS;

    CREATE ROLLBACK SEGMENT:

    Create public rollback segment BIG_ROLL
    tablespace ANALYST
    storage ( initial 1M
        next 1M
        optimal 15M
        minextents 10
        maxextents 249);

    ONLINE rollback segment:
    ALTER ROLLBACK SEGMENT BIG_ROLL ONLINE;

    DROP ROLLBACK SEGMENT:

    ALTER ROLLBACK SEGMENT BIG_ROLL OFFLINE;

    DROP PUBLIC ROLLBACK SEGMENT BIG_ROLL;

    To use specific rollback segment for a transaction :

    SET TRANSACTION USE ROLLBACK SEGMENT BIG_ROLL;

    NOTE:
    When you take a rollback segment offline,it does not actually go offline until all the activities transactions in it have
    completed.Between the time when you attempt to take it offline and when it actually is offline,its status in
    DBA_ROLLBACK_SEGS remains ONLINE,but it is not used for new transactions.To determine whether any rollback
    segments for an instance are in this state,use this query:

    SELECT NAME,XACTS ‘ACTIVE TRANSACTIONS’
    FROM V$ROLLNAME,V$ROLLSTAT
    WHERE STATUS = ‘PENDING OFFLINE’
    AND V$ROLLNAME.USN = V$ROLLSTAT.USN;

    NAME    ACTIVE TRANSACTIONS
    BIG_ROLL    3

    Deffered Rollback Segments:

    When a tablespace goes offline,usually due to media failure, such as transactions cannot be rolled back immediately,a deffered rollback segment is created.
    Contains entries that could not be applied to a tablespace,so they can be applied when the tablespace
    comes back online.

    Deffered rollback segments disappear when the tablespace are recovered
    Automatically created in the system tablespace
    Format is similar to rollback segments,except that it contains only rollback entries for a given tablespace
    SELECT segment_name,segment_type,tablespace_name
    FROM sys.dba_segments
    WHERE segment_type = ‘DIFFERED ROLLBACK’;

     

     

     

     

     

    To check tablespace spaces

    free_space.sql

    col segment_name format a20 word_wrapped
    col segment_type format a15 word_wrapped

    select segment_name,segment_type,extents,max_extents
    from dba_segments
    where extents>=max_extents*0.8 and
        segment_type<>'CACHE';

    Rem ==============================================
    Rem = Segments blowing out TS in the next extend =
    Rem===============================================

    col segment_name format a25 word_wrapped
    col segment_type format a15 word_wrapped
    col tablespace_name format a15 word_wrapped
    col "NEXT(MB)" format 9,999.99
    col max_free format 9,999.99

    select s.segment_name,
         s.segment_type,
         s.tablespace_name,
         s.next_extent/1024/1024 "NEXT(MB)",
         f.max_free/1024/1024 max_free
    from dba_segments s,  (select tablespace_name,max(bytes) max_free
            from dba_free_space
           group by tablespace_name) f
    where s.tablespace_name=f.tablespace_name and
         s.next_extent>=f.max_free;

    Rem =============================
    Rem = These TSs need coalescing =
    Rem =============================

    select tablespace_name, percent_extents_coalesced
    from dba_free_space_coalesced
    where percent_extents_coalesced <> 100;

     

     

     

     

     

    List of Datafile , Logfile, controlfiles

    select * from v$datafile;

    select * from v$log;

    select * from v$controlfile;

    To check all other parameters .Goto SVRMGRL connect internal

    SVRMGRL> show parameters

    It will display all the init.ora parameters and the values. You can also select selective parameters

    SVRMGRL> show parameters log* all the parameters start with log will display similarly db*

     

     

     

     

     

     

     

     

     

     

    Count tables data

    Run this script in the SQL*Plus command it will generate a another scripts for count tables.Then run that script you will
    get the result;

    set pagesize 0
    spool table_count.sql
    select ‘select count(*), ‘||’’’’||table_name||’’’’||’ from ‘||owner||’.’||table_name||’;’ from dba_tables
    where owner not in (‘SYS’,’SYSTEM’)
    spool off;

     

     

     

     

     

     

     

     

     

     

     

     

     

    Check last SQL

    select sql_text
         from v$sqltext_with_newlines
         where address in (
          select sql_address from v$session where sid in
            (select sid from v$session where Username='&username')
            and hash_value in (
          select sql_hash_value from v$session where sid in
            ( select sid from v$session where
          Username='&username'))
         order by piece

     

     

     

     

     

     

     

     

     

    Find Out all the Users and the Process

     

    Set linesize 1000;

    select s.sid,
    nvl(decode(s.sql_hash_value,0,' ','*'),'?')||nvl(s.process,'?') process,
    nvl(p.spid,'?') spid,
    s.username username, nvl(substr(s.osuser,1,8),'?') osuser,
    replace(replace(nvl(substr(s.terminal,1,7),'?'),'PC',''),'tty','') term,
    decode(s.status,'ACTIVE','R','INACTIVE','W','KILLED','Z') stat,
    decode(s.lockwait,NULL,'N','Y') lw,
    nvl(decode(s.command,1,'CRT TABL',2,'INSERT',3,'SELECT',4,'CRT CLST',
    5,'ALT CLST',6,'UPDATE',7,'DELETE',8,'DROP',9,'CRT INDX',
    10,'DRP INDX',11,'ALT INDX',12,'DRP TABL',15,'ALT TABL',
    17,'GRANT',18,'REVOKE',19,'CRT SYNM',20,'DRP SYNM',21,
    'CRT VIEW',22,'DRP VIEW',26,'LOC TBLE',27,'NOTHING',28,
    'RENAME',29,'COMMENT',30,'AUDIT',31,'NOAUDIT',32,'CRT XTDB',
    33,'DRP XTDB',34,'CRT DBSE',35,'ALT DBSE',36,'CRT RSEG',
    37,'ALT RSEG',38,'DRP RSEG',39,'CRT TBSP',40,'ALT TBSP',
    41,'DRP TBSP',42,'ALT SESS',43,'ALT USER',44,'COMMIT',
    45,'ROLLBACK',46,'SVEPOINT','IDLE'),'?') com,
    substr(nvl(s.machine,'?'),1,9) mach,
    decode(p.latchwait,NULL,'N','Y') latchw,
    decode(least(instr(p.program,'TCP'),1),1,'TCP',
    decode(least(instr(p.program,'TNS'),1),1,'TNS',
    decode(least(instr(p.program,'Pip'),1),1,'Pip',' '))) Type
    from v$session s, v$process p
    where addr = paddr
    and p.background is NULL
    union
    select s.sid,
       nvl(decode(s.sql_hash_value,0,' ','*'),'?')||nvl(s.process,'?') process,
       nvl(p.spid,'?') spid,
       p.username username, nvl(substr(s.osuser,1,8),'?') osuser,
       replace(replace(substr(p.terminal,1,7),'PC',''),'tty','') term,
       decode(s.status,'ACTIVE','R','INACTIVE','W','KILLED','Z') stat,
       decode(s.lockwait,NULL,'N','Y') lw,
       nvl(decode(s.command,1,'CRT TABL',2,'INSERT',3,'SELECT',4,'CRT CLST',
        5,'ALT CLST',6,'UPDATE',7,'DELETE',8,'DROP',9,'CRT INDX',
        10,'DRP INDX',11,'ALT INDX',12,'DRP TABL',15,'ALT TABL',
        17,'GRANT',18,'REVOKE',19,'CRT SYNM',20,'DRP SYNM',21,
        'CRT VIEW',22,'DRP VIEW',26,'LOC TBLE',27,'NOTHING',28,
        'RENAME',29,'COMMENT',30,'AUDIT',31,'NOAUDIT',32,'CRT XTDB',
        33,'DRP XTDB',34,'CRT DBSE',35,'ALT DBSE',36,'CRT RSEG',
        37,'ALT RSEG',38,'DRP RSEG',39,'CRT TBSP',40,'ALT TBSP',
        41,'DRP TBSP',42,'ALT SESS',43,'ALT USER',44,'COMMIT',
        45,'ROLLBACK',46,'SVEPOINT'),'?') com,
       substr(nvl(s.machine,'?'),1,9) mach,
       decode(p.latchwait,NULL,'N','Y') latchw,
       decode(least(instr(p.program,'TCP'),1),1,'TCP',
       decode(least(instr(p.program,'TNS'),1),1,'TNS',
       decode(least(instr(p.program,'Pip'),1),1,'Pip',' '))) Type
    from v$session s, v$process p
    where addr = paddr
    and p.background is not NULL
    order by 1
    /

     

     

     

     

     

     

    How to create Controlfile or Cloning Database

     

    First go to SVREMRL> connect  internal
    SVRMGRL> alter database backup control file to trace;
    Goto ORACLE_HOME/rdbms/log directory and rename the file as a create_control.sql script and modify the script.It Should looks like this.
    ls  -lrt
    last file rename
    Or you can find this file at udump directory.
    Vi that file and delete all the lines up STARTUP  NOMOUNT
    and change hilighted part.Your vi file should look like this except the LOGFILE and DATAFILE directory.
    Copy all the datafile to a proper destination where you wants to create another database.

    i) Should not move original control file
    ii) Should not move any logfile.

    Makesure init.ora and the directory mention for controlfile creation is present.

    If this instance is in the same oracle then shoutdown first and then copy all the datafiles
    set the SID export=ORACLE_SID=new database name;
    STARTUP NOMOUNT
    CREATE CONTROLFILE  SET  DATABASE  "HRSAND" RESETLOGS  NOARCHIVELOG
        MAXLOGFILES 8
        MAXLOGMEMBERS 4
        MAXDATAFILES 1021
        MAXINSTANCES 1
        MAXLOGHISTORY 100
    LOGFILE
        GROUP 1 '/hrsand_log1/log01.dbf' SIZE 10M,
        GROUP 2 '/hrsand_log1/log02.dbf' SIZE 10M,
        GROUP 3 '/hrsand_log1/log03.dbf' SIZE 10M
    DATAFILE
       '/hrsand_dat1/system.dbf',
       '/hrsand_rbs1/rollback.dbf',
       '/hrsand_dat1/data.dbf',
       '/hrsand_ind1/index.dbf';
    # Recovery is required if any of the datafiles are restored backups,
    # or if the last shutdown was not normal or immediate.
    # Database can now be opened normally.
    ALTER DATABASE OPEN RESETLOGS;
    SVRMGRL> connect internal;

    @create_control.sql

    If your GLOBAL_NAME is defined the you should rename the GLOBAL_NAME.

    alter database rename GLOBAL_NAME to ;
    Once the database is created you can startup the other database.

     

     

     

     

     

     

    Background Processes

     

  • 4 mandatory background process is required
      PMON
    • Cleans up abnormally terminated connection
    • Rolls back uncommited transactions
    • Releases locks held by a terminated process
    • Frees SGA resources allocated to the failed process
    • Restarts failed shared server and dispatchers process
      SMON
    • Performs automatically instance recovery
    • Reclaims space used by temporary segments no longer in use
    • Merges continuous areas of free space in the datafiles
      LGWR
    • Writes redo log entries to disk
        LGWR writes redo log buffer entries to the redo log files when?
      • A commit occurs
      • The redo log buffer pool becames one-third full
      • The DBWR completes cleaning the buffer blocks at a checkpoint
      • An LGWR timeout occurs
      DBWR
    • Writes all the data to datafiles
    • When check point occurs DBWR flushes Database Buffer Cache
    • LOG_CHECKPOINT_TIMEOUT=10000 [seconds] You can put any value
    • LOG_CHECKPOINT_INTERVAL=200000 [seconds]
      Optional Background process ARCH
    • Copies online redo log files to tape or disk for future use in the event of media failure recovery is needed
    • Operates only when a log switch occurs
    • ARCH process is optional and is only needed when in Archive mode

     

     

     

     

     

     

     

    Oracle8i LogMiner Introduction

    Problem Action
    Megabytes of log files are being generated on my database server. I propose to use Oracle8I's new log-analysis tool, LogMiner.

    What is Oracle writing into it's redo log files ?

    To find out what Oracle is writing into the redo log files, use Oracle8i's new log-analysis tool, LogMiner.

    Log files store all the data needed to perform a database recovery and record every changes made to the database structure. With "every change" we mean every INSERT, UPDATE and DELETE !!!

    Until Oracle8I there was no built-in tool for helping administrators read and interpret the redo-log files. Sending a dump-file of the logfiles to Oracle Support was the only solution. Oracle8i provides a powerful new tool, LogMiner.

    LogMiner is actually a set of PL/SQL packages and dynamic (V$) views. The packages are installed with the installation of the database. The views however will be created on the moment you startup LogMiner by using the DBMS_LOGMNR.START_LOGMNR procedure.

    LogMiner can be used against Online or Offline log files from either the 'current' database or a 'foreign' database.

    The most important package is DBMS_LOGMNR.START_LOGMNR. This one will create the necessary views you can query later on. To be able to read the views you will have to setup a meta-data file before starting LogMiner.

    Step 1 : Create a meta-data file
    The procedure DBMS_LOGMNR_D.BUILD uses the database package UTL_FILE. Therefore you need to add a parameter UTL_FILE_DIR in the init<SID>.ora file pointing to a directory on the server.

    Under /home/oracle create a directory called logmonitor. init.ora file add utl_file_dir=/home/oracle/logmonitor Eg. UTL_FILE_DIR = (/home/oracle/logmonitor)

    Restart your database and create the meta-data file. 

    SQL> CONNECT SYS/ 
SQL> EXECUTE dbms_logmnr_d.build(Dictionary_FileName=>'Dictionary.ora',Dictionary_Location=>'/home/oracle/logmonitor');
    It will create a file dictionary.ora under /home/oracle/logmonitor directory. You can also examine other database archive logfile. You have to ftp the lofile in binary mode then add that logfile. Make sure Oracle version is same in both the machine. Step 2 : Create the list of 'to examine logfiles'
    Scenario 1 : Check the online redo log files 
    A.  Create List :
----------------------

SQL> EXECUTE dbms_logmnr.add_logfile(Options=>dbms_logmnr.new,LogFileName=>'F:\Oracle\Oradata\plnts\Redo01.log');

B.   Add other logfiles to the list :   
--------------------------------------------
SQL> EXECUTE dbms_logmnr.add_logfile(Options=>dbms_logmnr.addfile,LogFileName=>'F:\Oracle\Oradata\plnts\Redo02.log');

SQL> EXECUTE dbms_logmnr.add_logfile(Options=>dbms_logmnr.addfile,LogFileName=>'F:\Oracle\Oradata\plnts\Redo03.log');
    Scenario 2 : Check the offline log files 
    A.  Create List :
----------------------

SQL> EXECUTE dbms_logmnr.add_logfile(Options=>dbms_logmnr.new,
 
LogFileName=>'G:\Oracle\Oraarch\plnts\ARCARC07108.001');

B.   Add other logfiles to the list :   
--------------------------------------------
SQL> EXECUTE dbms_logmnr.add_logfile(Options=>dbms_logmnr.addfile,
 
LogFileName=>' 'G:\Oracle\Oraarch\plnts\ARCARC07109.001');

SQL> EXECUTE dbms_logmnr.add_logfile(Options=>dbms_logmnr.addfile,
 
LogFileName=>' 'G:\Oracle\Oraarch\plnts\ARCARC07110.001');
    Continue of step 2...
    About the list. You decide yourself which logfile, and how many logfiles you will examine. I suggest to create a list of just one logfile and to check the contents of this one before adding another one.

    You will find out LogMiner is not that easy. LogMiner is production but is missing a GUI-interface.

    Note: You can remove a log file from the list by using 'dbms_logmnr.removefile' instead of using 'dbms_logmnr.addfile'.

    Step 3 : Start "LOGMINER"
    Scenario 1 : no limitations 
    SQL> EXECUTE dbms_logmnr.start_logmnr(DictFileName=>'C:\Oracle\UtilFileDir\Dictionary.ora');
    Scenario 2 : limit the search to a specific time range during which someone made his changes to the database 
    SQL> EXECUTE dbms_logmnr.start_logmnr(DictFileName=>'C:\Oracle\UtilFileDir\Dictionary.ora'
                      , StartTime=>TO_DATE('15-Jan-2000 08:00:00','DD-MON-RRRR HH:MI:SS')
                      , EndTime=>TO_DATE('15-Jan-2000 08:30:00','DD-MON-RRRR HH:MI:SS')
                     );

Parameters of "dbms_logmnr.start_logmnr" :

  StartScn      Default 0,
  EndScn        Default 0,
  StartTime     Default '01-Jan-1988',
  EndTime       Default '01-Jan-2988',
  DictFileName  Default '',
  Options       Default 0   (= a debug flag, not yet being used)

    Step 4 : Examine the view "v$logmnr_contents"

    FINALLY THE RESULTS OF ALL YOUR WORK !!!

    Examine the view 'v$logmnr_contents' for more information 

    SELECT sql_redo FROM v$logmnr_contents;
    If you want to know more information about the modification someone was doing on a specific table then use the following query : 
    SQL> SELECT sql_redo FROM v$logmnr_contents WHERE username='SCOTT' AND tablename='USERS';
    It might be useful to describe the view 'v$logmnr_contents'. 
    SQL> DESC v$logmnr_contents

    Remarks

    The output from LogMiner is the contents of the view "v$logmnr_contents'. The output is only visible during the life of the session which runs the procedure 'dbms_logmrn.start_logmnr'. This is because all the LogMiner memory is in PGA memory, so it is neither visible to other sessions, not is it persistent.

    So the moment you logoff or you invoke the procedure 'dbms_logmnr.end_logmnr' the PGA will be cleared and the information is no longer available.

    Dbms_logmnr and dbms_logmnr_d call kernel C modules directly. There is very little of the functionality visible to the user in the dbmslogmnr.sql.

    There is no GUI-interface.

    		•