rman backup intergated dengan net backup

barangkali berguna, di sini saya akan sedikit membahas
rman backup ke tape dengan menggunakan third party net backup
1. install net backup client
2. test backup file system di client ke tape dengan menggunakan
   net backup, untuk memastikan komunikasi antara server media        dengan client tidak bermasalah
3. install netbackup's oracle add-on
4. di net backup master
   a. buat sebuah policy baru contoh CL-SERVER1-RMAN-DEMO
   b. pilih policy type "Oracle"
   c. dalam tab client di net backup master pilih client  
   d. dan pastikan bahwa backup type "Application Backup"
   e. save policy
5. di server database
   a. perlu restart database hanya sekali saja untuk mengenali/link       database ke net backup agent
   b. login sebagai user oracle
   c. sebagai user oracle jalankan script
      /usr/openv/netbackup/bin/oracle_link
6. test backup database
   di server database
   $ rman target=/
   RMAN> run {
      2> ALLOCATE CHANNEL c1 DEVICE TYPE sbt;
      3> send 'NB_ORA_POLICY=CL-SERVER1-RMAN-DEMO,NB_ORA_SERV=NBU-         MASTER-SRV';
      4> BACKUP current controlfile;
      5> release channel c1;}
   kalau bingung coba langsung jalankan script :
      
   RMAN> run {
      2> ALLOCATE CHANNEL c1 DEVICE TYPE sbt;
    
3> BACKUP current controlfile;
      4> release channel c1;}

Note :
pastikan di $ORACLE_HOME/lib sudah ada libobk.so yang merupakan link dari /usr/openv/netbackup/bin/oracle_link
dan jangan lupa juga LD_LIBRARY_PATH nya mengarah ke $ORACLE_HOME/lib

Migrate 32-Bit Oracle to 64-Bit Oracle

32 Bit Machine 

1. Run SHUTDOWN IMMEDIATE on the database: 

   SQL> SHUTDOWN IMMEDIATE

2. Perform a full offline backup of the database. This backup is used only as a preventive action incase there is problem in removing OLAP options.  

3. Startup database again to remove OLAP options.

   SQL> STARTUP 

Run this script to remove OLAP options from the database :

----> Remove OLAP Catalog

SQL> @?\olap\admin\catnoamd.sql

----> Remove OLAP API

SQL> @?\olap\admin\olapidrp.plb

SQL> @?\olap\admin\catnoxoq.sql

----> Deinstall APS - OLAP AW component

SQL> @?\olap\admin\catnoaps.sql

SQL> @?\olap\admin\cwm2drop.sql

----> Recompile everything

SQL> @?\rdbms\admin\utlrp.sql

4. Shutdown the database and perform another full offline backup of the  database. This backup will be restored and converted to 64-bit database on 64-bit mechine. 

64 Bit Machine

1. Install the Oracle 10.2.0.1 64-bit version on 64-bit mechine.

2. Patch 10.2.0.1 32-bit software to 10.2.0.4 64-bit software

3. Copy configuration files from Oracle 32-bit mechine to a location outside of the old Oracle home (including init and password file).

   @ORACLE_HOME/database/

4. For changing wordsize from a 32-bit Oracle version to a 64-bit Oracle version, Oracle recommends doubling the size of parameters such as:

SHARED_POOL_SIZE

SHARED_POOL_RESERVED_SIZE

LARGE_POOL_SIZE

So, set new values of those parameters to double of current values in init password file (currently init file used on production is $ORACLE_HOME/database/initSERP_INFOR.ora)

5.  Copy offline backup files, which is backed up after removing OLAP options, from 32 bit mechine to the same location and path of 64 bit mechine.

6. Start SQL*Plus and connect to the database instance AS SYSDBA. 

    Startup using the edited PFILE using this command :

    SQL> STARTUP UPGRADE pfile= (edited pfile)

7. Set the system to spool results to a log file for later verification of success: 

    SQL> SPOOL catoutw.log

    If you want to see the output of the script you will run on your screen, then you can also issue a SET ECHO ON statement: 

    SQL> SET ECHO ON

8. Run utlirp.sql: 

    SQL> @$ORACLE_HOME/rdbms/admin/utlirp.sql

    The utlirp.sql script recompiles existing PL/SQL modules in the format required by the new database. 

9. Check the validity of the DBMS_STANDARD package:

    SQL> select status from dba_objects

    where object_name='DBMS_STANDARD'

    and object_type='PACKAGE'

    and owner='SYS';

10. If the package is invalid, recompile it:

    SQL> alter package dbms_standard compile;

11. Run the following SQL statement to check for invalid objects:

    SQL> select owner, object_name, object_type from dba_objects

    where status <> 'VALID';

12. Turn off the spooling of script results to the log file: 

    SQL> SPOOL OFF

    Then, check the spool file and verify that the packages and procedures  compiled successfully. You named the spool file in Step 15; the suggested name was catoutw.log. Correct any problems you find in this file (for example, compile any invalid objects)

    If you specified SET ECHO ON, then you may want to SET ECHO OFF now: 

    SQL> SET ECHO OFF

13. Shutdown and restart the database without upgrade options : 

    SQL> SHUTDOWN IMMEDIATE

    SQL> STARTUP

***

Setting log miner for tracking transaction

2. Steps in a Typical LogMiner Session
1) Enable Supplemental Logging
Database level:
SQL> ALTER DATABASE ADD SUPPLEMENTAL LOG DATA; -- enable minimal database level supplemental logging
SQL> ALTER DATABASE ADD SUPPLEMENTAL LOG DATA (ALL) COLUMNS; -- Database level : ALL system-generated uncondititional supplemental log group
SQL> ALTER DATABASE ADD SUPPLEMENTAL LOG DATA (PRIMARY KEY) COLUMNS; -- PRIMARY KEY system-generated uncondititional supplemental log group
SQL> ALTER DATABASE ADD SUPPLEMENTAL LOG DATA (UNIQUE) COLUMNS; -- UNIQUE system-generated conditional supplemental log group
SQL> ALTER DATABASE ADD SUPPLEMENTAL LOG DATA (FOREIGN KEY) COLUMNS; -- FOREIGN KEY system-generated conditional supplemental log group

To disable all database supplemental logging, you must first disable any identification key logging that has been enabled, then disable minimal supplemental logging. The following example shows the correct order:

SQL> ALTER DATABASE ADD SUPPLEMENTAL LOG DATA (PRIMARY KEY) COLUMNS;
SQL> ALTER DATABASE ADD SUPPLEMENTAL LOG DATA (UNIQUE) COLUMNS;
SQL> ALTER DATABASE DROP SUPPLEMENTAL LOG DATA (PRIMARY KEY) COLUMNS;
SQL> ALTER DATABASE DROP SUPPLEMENTAL LOG DATA (UNIQUE) COLUMNS;
SQL> ALTER DATABASE DROP SUPPLEMENTAL LOG DATA;

Table level:
SQL> ALTER TABLE HR.EMPLOYEES ADD SUPPLEMENTAL LOG DATA (ALL) COLUMNS;
SQL> ALTER TABLE HR.EMPLOYEES ADD SUPPLEMENTAL LOG DATA (PRIMARY KEY) COLUMNS;
SQL> ALTER TABLE HR.EMPLOYEES ADD SUPPLEMENTAL LOG DATA (UNIQUE) COLUMNS;
SQL> ALTER TABLE HR.EMPLOYEES ADD SUPPLEMENTAL LOG GROUP emp_parttime (EMPLOYEE_ID, LAST_NAME, DEPARTMENT_ID) ALWAYS; -- User-defined unconditional log groups
SQL> ALTER TABLE HR.EMPLOYEES ADD SUPPLEMENTAL LOG GROUP emp_fulltime (EMPLOYEE_ID, LAST_NAME, DEPARTMENT_ID); -- User-defined conditional supplemental log groups

2) Extract a LogMiner Dictionary (unless you plan to use the online catalog)
* Specify use of the online catalog by using the DICT_FROM_ONLINE_CATALOG option when you start LogMiner.
SQL> EXECUTE DBMS_LOGMNR.START_LOGMNR(OPTIONS => DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG);

* Extract database dictionary information to the redo log files.
SQL> EXECUTE DBMS_LOGMNR_D.BUILD(OPTIONS=> DBMS_LOGMNR_D.STORE_IN_REDO_LOGS);

* Extract database dictionary information to a flat file.
"UTL_FILE_DIR = /oracle/database" must be put in initial parameter file first to enable directory access.
SQL> EXECUTE DBMS_LOGMNR_D.BUILD('dictionary.ora', '/oracle/database/', DBMS_LOGMNR_D.STORE_IN_FLAT_FILE);
SQL> EXECUTE DBMS_LOGMNR_D.BUILD('dictionary.ora', '/oracle/database/');

3) Specify Redo Log Files for Analysis
SQL> EXECUTE DBMS_LOGMNR.ADD_LOGFILE(LOGFILENAME => '/oracle/logs/log1.f', OPTIONS => DBMS_LOGMNR.NEW);
SQL> EXECUTE DBMS_LOGMNR.ADD_LOGFILE(LOGFILENAME => '/oracle/logs/log2.f', OPTIONS => DBMS_LOGMNR.ADDFILE);
SQL> EXECUTE DBMS_LOGMNR.ADD_LOGFILE(LOGFILENAME=>'/oracle/logs/log2.f');
SQL> EXECUTE DBMS_LOGMNR.REMOVE_LOGFILE(LOGFILENAME => '/oracle/logs/log2.f');

To use online catalog and DBMS_LOGMNR.CONTINUOUS_MINE option in START_LOGMNR, it is not necessary to specify redo log files manually, it is automatically.


4) Start LogMiner
The OPTIONS parameter to DBMS_LOGMNR.START_LOGMNR:
* DICT_FROM_ONLINE_CATALOG
* DICT_FROM_REDO_LOGS
* CONTINUOUS_MINE
* COMMITTED_DATA_ONLY
* SKIP_CORRUPTION
* NO_SQL_DELIMITER
* PRINT_PRETTY_SQL
* NO_ROWID_IN_STMT
* DDL_DICT_TRACKING

SQL> ALTER SESSION SET NLS_DATE_FORMAT = 'DD-MON-YYYY HH24:MI:SS';
SQL> EXECUTE DBMS_LOGMNR.START_LOGMNR( -
STARTTIME => '01-Jan-2003 08:30:00', -
ENDTIME => '01-Jan-2003 08:45:00', -
OPTIONS => DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG + -
DBMS_LOGMNR.CONTINUOUS_MINE);
SQL> EXECUTE DBMS_LOGMNR.START_LOGMNR(OPTIONS => DBMS_LOGMNR.COMMITTED_DATA_ONLY);
SQL> EXECUTE DBMS_LOGMNR.START_LOGMNR(options => DBMS_LOGMNR.SKIP_CORRUPTION);
SQL> EXECUTE DBMS_LOGMNR.START_LOGMNR(STARTSCN => 621047, ENDSCN => 625695, OPTIONS => DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG + DBMS_LOGMNR.CONTINUOUS_MINE);
SQL> EXECUTE DBMS_LOGMNR.START_LOGMNR(DICTFILENAME =>'/oracle/database/dictionary.ora');
SQL> EXECUTE DBMS_LOGMNR.START_LOGMNR(OPTIONS => DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG + DBMS_LOGMNR.COMMITTED_DATA_ONLY);

5) Query V$LOGMNR_CONTENTS
* LogMiner populates the view only in response to a query against it. You must successfully start LogMiner before you can query V$LOGMNR_CONTENTS.
* When a SQL select operation is executed against the V$LOGMNR_CONTENTS view, the redo log files are read sequentially.
* Every time you query V$LOGMNR_CONTENTS, LogMiner analyzes the redo log files for the data you request.
* The amount of memory consumed by the query is not dependent on the number of rows that must be returned to satisfy a query.
* The time it takes to return the requested data is dependent on the amount and type of redo log data that must be mined to find that data.

SQL> SELECT OPERATION, SQL_REDO, SQL_UNDO
SQL> FROM V$LOGMNR_CONTENTS
SQL> WHERE SEG_OWNER = 'OE' AND SEG_NAME = 'ORDERS' AND
SQL> OPERATION = 'DELETE' AND USERNAME = 'RON';

SQL> SELECT SQL_REDO FROM V$LOGMNR_CONTENTS
SQL> WHERE SEG_NAME = 'EMPLOYEES' AND
SQL> SEG_OWNER = 'HR' AND
SQL> OPERATION = 'UPDATE' AND
SQL> DBMS_LOGMNR.MINE_VALUE(REDO_VALUE, 'HR.EMPLOYEES.SALARY') >
SQL> 2*DBMS_LOGMNR.MINE_VALUE(UNDO_VALUE, 'HR.EMPLOYEES.SALARY');


6) End the LogMiner Session
SQL> EXECUTE DBMS_LOGMNR.END_LOGMNR;


3. Accessing LogMiner Operational Information in Views
* V$LOGMNR_DICTIONARY
Shows information about a LogMiner dictionary file that was created using the STORE_IN_FLAT_FILE option to DBMS_LOGMNR.START_LOGMNR.
* V$LOGMNR_LOGS
Shows information about specified redo log files.
* V$LOGMNR_PARAMETERS
Shows information about optional LogMiner parameters, including starting and ending system change numbers (SCNs) and starting and ending times.
* V$DATABASE, DBA_LOG_GROUPS, ALL_LOG_GROUPS, USER_LOG_GROUPS, DBA_LOG_GROUP_COLUMNS, ALL_LOG_GROUP_COLUMNS, USER_LOG_GROUP_COLUMNS
Shows information about the current settings for supplemental logging.


4. Examples
* Examples of Mining by Explicitly Specifying the Redo Log Files of Interest
* Example 1: Finding All Modifications in the Last Archived Redo Log File
SQL> SELECT NAME FROM V$ARCHIVED_LOG WHERE FIRST_TIME = (SELECT MAX(FIRST_TIME) FROM V$ARCHIVED_LOG);
SQL> EXECUTE DBMS_LOGMNR.ADD_LOGFILE(LOGFILENAME => '/usr/oracle/data/db1arch_1_16_482701534.dbf', OPTIONS => DBMS_LOGMNR.NEW);
SQL> EXECUTE DBMS_LOGMNR.START_LOGMNR(OPTIONS => DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG);
SQL> SELECT username AS USR, (XIDUSN || '.' || XIDSLT || '.' || XIDSQN) AS XID, SQL_REDO, SQL_UNDO FROM V$LOGMNR_CONTENTS WHERE username IN ('HR', 'OE');
SQL> EXECUTE DBMS_LOGMNR.END_LOGMNR();

* Example 2: Grouping DML Statements into Committed Transactions
SQL> SELECT NAME FROM V$ARCHIVED_LOG WHERE FIRST_TIME = (SELECT MAX(FIRST_TIME) FROM V$ARCHIVED_LOG);
SQL> EXECUTE DBMS_LOGMNR.ADD_LOGFILE(LOGFILENAME => '/usr/oracle/data/db1arch_1_16_482701534.dbf', OPTIONS => DBMS_LOGMNR.NEW);
SQL> EXECUTE DBMS_LOGMNR.START_LOGMNR(OPTIONS => DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG + DBMS_LOGMNR.COMMITTED_DATA_ONLY);
SQL> SELECT username AS USR, (XIDUSN || '.' || XIDSLT || '.' || XIDSQN) AS XID, SQL_REDO, SQL_UNDO FROM V$LOGMNR_CONTENTS WHERE username IN ('HR', 'OE');
SQL> EXECUTE DBMS_LOGMNR.END_LOGMNR();

* Example 3: Formatting the Reconstructed SQL
SQL> SELECT NAME FROM V$ARCHIVED_LOG WHERE FIRST_TIME = (SELECT MAX(FIRST_TIME) FROM V$ARCHIVED_LOG);
SQL> EXECUTE DBMS_LOGMNR.ADD_LOGFILE(LOGFILENAME => '/usr/oracle/data/db1arch_1_16_482701534.dbf', OPTIONS => DBMS_LOGMNR.NEW);
SQL> EXECUTE DBMS_LOGMNR.START_LOGMNR(OPTIONS => DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG + DBMS_LOGMNR.COMMITTED_DATA_ONLY + DBMS_LOGMNR.PRINT_PRETTY_SQL);
SQL> SELECT username AS USR, (XIDUSN || '.' || XIDSLT || '.' || XIDSQN) AS XID, SQL_REDO FROM V$LOGMNR_CONTENTS;
SQL> SELECT username AS USR, (XIDUSN || '.' || XIDSLT || '.' || XIDSQN) AS XID, SQL_UNDO FROM V$LOGMNR_CONTENTS;
SQL> EXECUTE DBMS_LOGMNR.END_LOGMNR();

* Example 4: Using the LogMiner Dictionary in the Redo Log Files
SQL> SELECT NAME, SEQUENCE# FROM V$ARCHIVED_LOG WHERE FIRST_TIME = (SELECT MAX(FIRST_TIME) FROM V$ARCHIVED_LOG);

Find a redo log file that contains the end of the dictionary extract.
SQL> SELECT NAME, SEQUENCE#, DICTIONARY_BEGIN d_beg, DICTIONARY_END d_end FROM V$ARCHIVED_LOG WHERE SEQUENCE# = (SELECT MAX (SEQUENCE#) FROM V$ARCHIVED_LOG WHERE DICTIONARY_END = 'YES' and SEQUENCE# <= 210); Find the redo log file that contains the start of the data dictionary extract that matches the end of the dictionary found in the previous step. SQL> SELECT NAME, SEQUENCE#, DICTIONARY_BEGIN d_beg, DICTIONARY_END d_end FROM V$ARCHIVED_LOG WHERE SEQUENCE# = (SELECT MAX (SEQUENCE#) FROM V$ARCHIVED_LOG WHERE DICTIONARY_BEGIN = 'YES' and SEQUENCE# <= 208); Specify the list of the redo log files of interest. SQL> EXECUTE DBMS_LOGMNR.ADD_LOGFILE(LOGFILENAME => '/usr/oracle/data/db1arch_1_210_482701534.dbf', OPTIONS => DBMS_LOGMNR.NEW);
SQL> EXECUTE DBMS_LOGMNR.ADD_LOGFILE(LOGFILENAME => '/usr/oracle/data/db1arch_1_208_482701534.dbf');
SQL> EXECUTE DBMS_LOGMNR.ADD_LOGFILE(LOGFILENAME => '/usr/oracle/data/db1arch_1_207_482701534.dbf');
Query the V$LOGMNR_LOGS view to display the list of redo log files to be analyzed, including their timestamps.
SQL> SELECT FILENAME AS name, LOW_TIME, HIGH_TIME FROM V$LOGMNR_LOGS;

SQL> EXECUTE DBMS_LOGMNR.START_LOGMNR(OPTIONS => DBMS_LOGMNR.DICT_FROM_REDO_LOGS + DBMS_LOGMNR.COMMITTED_DATA_ONLY + DBMS_LOGMNR.PRINT_PRETTY_SQL);
SQL> SELECT USERNAME AS usr, 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';
SQL> SELECT SQL_REDO FROM V$LOGMNR_CONTENTS WHERE XIDUSN = 1 and XIDSLT = 2 and XIDSQN = 1594;
SQL> EXECUTE DBMS_LOGMNR.END_LOGMNR();

* Example 5: Tracking DDL Statements in the Internal Dictionary
SQL> SELECT NAME, SEQUENCE# FROM V$ARCHIVED_LOG WHERE FIRST_TIME = (SELECT MAX(FIRST_TIME) FROM V$ARCHIVED_LOG);
SQL> SELECT NAME, SEQUENCE#, DICTIONARY_BEGIN d_beg, DICTIONARY_END d_end FROM V$ARCHIVED_LOG WHERE SEQUENCE# = (SELECT MAX (SEQUENCE#) FROM V$ARCHIVED_LOG WHERE DICTIONARY_END = 'YES' and SEQUENCE# <> SELECT NAME, SEQUENCE#, DICTIONARY_BEGIN d_beg, DICTIONARY_END d_end FROM V$ARCHIVED_LOG WHERE SEQUENCE# = (SELECT MAX (SEQUENCE#) FROM V$ARCHIVED_LOG WHERE DICTIONARY_BEGIN = 'YES' and SEQUENCE# <= 208); Make sure you have a complete list of redo log files. SQL> SELECT NAME FROM V$ARCHIVED_LOG WHERE SEQUENCE# >= 207 AND SEQUENCE# <= 210 ORDER BY SEQUENCE# ASC; SQL> EXECUTE DBMS_LOGMNR.ADD_LOGFILE(LOGFILENAME => '/usr/oracle/data/db1arch_1_210_482701534.dbf', OPTIONS => DBMS_LOGMNR.NEW);
SQL> EXECUTE DBMS_LOGMNR.ADD_LOGFILE(LOGFILENAME => '/usr/oracle/data/db1arch_1_209_482701534.dbf');
SQL> EXECUTE DBMS_LOGMNR.ADD_LOGFILE(LOGFILENAME => '/usr/oracle/data/db1arch_1_208_482701534.dbf');
SQL> EXECUTE DBMS_LOGMNR.ADD_LOGFILE(LOGFILENAME => '/usr/oracle/data/db1arch_1_207_482701534.dbf');
SQL> EXECUTE DBMS_LOGMNR.START_LOGMNR(OPTIONS => DBMS_LOGMNR.DICT_FROM_REDO_LOGS + DBMS_LOGMNR.DDL_DICT_TRACKING + DBMS_LOGMNR.COMMITTED_DATA_ONLY + DBMS_LOGMNR.PRINT_PRETTY_SQL);
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';
SQL> EXECUTE DBMS_LOGMNR.END_LOGMNR();

* Example 6: Filtering Output by Time Range
Create a list of redo log files to mine.
--
-- my_add_logfiles
-- Add all archived logs generated after a specified start_time.
--
CREATE OR REPLACE PROCEDURE my_add_logfiles (in_start_time IN DATE) AS
CURSOR c_log IS
SELECT NAME FROM V$ARCHIVED_LOG
WHERE FIRST_TIME >= in_start_time;

count pls_integer := 0;
my_option pls_integer := DBMS_LOGMNR.NEW;

BEGIN
FOR c_log_rec IN c_log
LOOP
DBMS_LOGMNR.ADD_LOGFILE(LOGFILENAME => c_log_rec.name,
OPTIONS => my_option);
my_option := DBMS_LOGMNR.ADDFILE;
DBMS_OUTPUT.PUT_LINE('Added logfile ' || c_log_rec.name);
END LOOP;
END;
/

SQL> EXECUTE my_add_logfiles(in_start_time => '13-jan-2003 14:00:00');

Query the V$LOGMNR_LOGS to see the list of redo log files.
SQL> SELECT FILENAME name, LOW_TIME start_time, FILESIZE bytes FROM V$LOGMNR_LOGS;
SQL> EXECUTE DBMS_LOGMNR.START_LOGMNR(STARTTIME => '13-jan-2003 15:00:00', ENDTIME => '13-jan-2003 16:00:00', OPTIONS => DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG + DBMS_LOGMNR.COMMITTED_DATA_ONLY + DBMS_LOGMNR.PRINT_PRETTY_SQL);
SQL> SELECT TIMESTAMP, (XIDUSN || '.' || XIDSLT || '.' || XIDSQN) AS XID, SQL_REDO FROM V$LOGMNR_CONTENTS WHERE SEG_OWNER = 'OE';
SQL> EXECUTE DBMS_LOGMNR.END_LOGMNR();


* Examples of Mining Without Specifying the List of Redo Log Files Explicitly
* Example 1: Mining Redo Log Files in a Given Time Range
SQL> SELECT NAME, FIRST_TIME FROM V$ARCHIVED_LOG WHERE SEQUENCE# = (SELECT MAX(SEQUENCE#) FROM V$ARCHIVED_LOG WHERE DICTIONARY_BEGIN = 'YES');
SQL> SELECT FILENAME name FROM V$LOGMNR_LOGS WHERE LOW_TIME > '10-jan-2003 12:01:34';
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);
SQL> SELECT FILENAME name FROM V$LOGMNR_LOGS;
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';
SQL> EXECUTE DBMS_LOGMNR.END_LOGMNR();

* Example 2: Mining the Redo Log Files in a Given SCN Range
SQL> SELECT CHECKPOINT_CHANGE#, CURRENT_SCN FROM V$DATABASE;
SQL> EXECUTE DBMS_LOGMNR.START_LOGMNR(STARTSCN => 56453576, ENDSCN => 56454208, OPTIONS => DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG + DBMS_LOGMNR.COMMITTED_DATA_ONLY + DBMS_LOGMNR.PRINT_PRETTY_SQL + DBMS_LOGMNR.CONTINUOUS_MINE);
SQL> SELECT FILENAME name, LOW_SCN, NEXT_SCN FROM V$LOGMNR_LOGS;
SQL> SELECT NAME FROM V$ARCHIVED_LOG WHERE SEQUENCE# = (SELECT MAX(SEQUENCE#) FROM V$ARCHIVED_LOG);
SQL> SELECT SCN, (XIDUSN || '.' || XIDSLT || '.' || XIDSQN) as XID, SQL_REDO FROM V$LOGMNR_CONTENTS WHERE SEG_OWNER NOT IN ('SYS', 'SYSTEM');
SQL> EXECUTE DBMS_LOGMNR.END_LOGMNR();

* Example 3: Using Continuous Mining to Include Future Values in a Query
SQL> EXECUTE DBMS_LOGMNR.START_LOGMNR(STARTTIME => SYSDATE, ENDTIME => SYSDATE + 5/24, OPTIONS => DBMS_LOGMNR.CONTINUOUS_MINE + DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG);
SQL> SET ARRAYSIZE 1;
SQL> SELECT USERNAME AS usr, SQL_REDO FROM V$LOGMNR_CONTENTS WHERE SEG_OWNER = 'HR' AND TABLE_NAME = 'EMPLOYEES';
SQL> EXECUTE DBMS_LOGMNR.END_LOGMNR();


* Scenario 1: Using LogMiner to Track Changes Made by a Specific User
SQL> EXECUTE DBMS_LOGMNR.ADD_LOGFILE(LOGFILENAME => 'log1orc1.ora', OPTIONS => DBMS_LOGMNR.NEW);
SQL> EXECUTE DBMS_LOGMNR.ADD_LOGFILE(LOGFILENAME => 'log2orc1.ora', OPTIONS => DBMS_LOGMNR.ADDFILE);
SQL> EXECUTE DBMS_LOGMNR.START_LOGMNR(DICTFILENAME => 'orcldict.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'));
SQL> SELECT SQL_REDO, SQL_UNDO FROM V$LOGMNR_CONTENTS WHERE USERNAME = 'joedevo' AND SEG_NAME = 'salary';
SQL> DBMS_LOGMNR.END_LOGMNR( );

* Scenario 2: Using LogMiner to Calculate Table Access Statistics
SQL> EXECUTE DBMS_LOGMNR.START_LOGMNR(STARTTIME => TO_DATE('07-Jan-2003 08:30:00','DD-MON-YYYY HH:MI:SS'), ENDTIME => TO_DATE('21-Jan-2003 08:45:00','DD-MON-YYYY HH:MI:SS'), DICTFILENAME => '/usr/local/dict.ora');
SQL> SELECT SEG_OWNER, SEG_NAME, COUNT(*) AS Hits FROM V$LOGMNR_CONTENTS WHERE SEG_NAME NOT LIKE '%$' GROUP BY SEG_OWNER, SEG_NAME ORDER BY Hits DESC;
SQL> DBMS_LOGMNR.END_LOGMNR( );