LogMiner Viewer

Using LogMiner Viewer to Perform a Logical Recovery:
Purpose:
In this module, you will learn how to use LogMiner to analyze your redo log files so that you can logically recover your database.
http://www.oracle.com/technology/obe/obe9ir2/obe-ha/logminer/logminer.htm

Logminer

Logminer:

LogMiner is an Oracle utility which uses a dictionary meta data (online , from a flat file, or from redologs ) to scan the redo/archive logs and generates a set of SQL statements which would have the same effect on the database as applying the corresponding redo record.

Logminer can be used to mine the logs of the current database or a remote database.

The following steps are needed to analyze logs of a remote database.

1) Build the Dictionary file of the production database and scp to the mining/test server.
2) Get the list of archive logs which you want to mine from production server and scp to the mining/test server.
3) Add Redo Log Files for mining
4) Start LogMiner
5) Query V$LOGMNR_CONTENTS
6) End the LogMiner Session

Follow the below steps:
Production:
1: Log in as sys user
2: execute sys.dbms_logmnr_d.build ('PROD_dict.ora', '/oracle/admin/PROD/utldir');
3: Determine the archive log sequences that are needed by querying v$archived_log on the remote database. (Look at first_time, next_time values).
Ex: /u006/archive/PROD/PROD_1_4829_651194874.arc
4: send the above 2 files mentioned in step 2 and 3 to Mining/Test Server using scp/ftp/sftp etc.

Mining/Test Server:
1: Log in as sys user
2: exec sys.dbms_logmnr.add_logfile('/export/home/oracle/logminer/archive/PROD_1_4829_651194874.arc');
3: exec sys.Dbms_Logmnr.start_logmnr(dictfilename => '/export/home/oracle/logminer/dict/PROD_dict.ora');
4: Query the V$LOGMNR_CONTENTS to see the mined rows
5: exec sys.DBMS_LOGMNR.end_logmnr;

Note: Do not execute step 5 until you have finished quering V$LOGMNR_CONTENTS.

Query Rewrite Feature in Materialized Views

Query Rewrite Feature in Materialized Views:
--------------------------------------------------------------
Setup the Environment:
This includes creating a MV_CAPABILITIES_TABLE and a customised function to test this feature on Materialized views.

create table MV_CAPABILITIES_TABLE
( statement_id varchar(30) ,
mvowner varchar(30) ,
mvname varchar(30) ,
capability_name varchar(30) ,
possible character(1) ,
related_text varchar(2000) ,
related_num number ,
msgno integer ,
msgtxt varchar(2000) ,
seq number) ;

The above table can be created by executing the below script:
$ORACLE_HOME/rdbms/admin/utlxmv.sql

You can create a customised function to generate query rewrite functionalities reports on materialized views.
Refer the below URL to create a function called my_mv_capabilities.
http://www.sqlsnippets.com/en/topic-12884.html

Create/Test the Query Rewrite Feature of Materialized Views:
Create a table called dept:
SQL> create table dept (id number, name varchar2(20));
SQL> insert into dept values (1, 'Finance');
SQL> insert into dept values (2, 'Admin');
SQL> insert into dept values (3, 'HR');
SQL> commit;

Create a test mview:
SQL> Create materialized view test DISABLE QUERY REWRITE as select id,name from dept;

Generate Report:
SQl> set long 5000
SQL> select my_mv_capabilities( 'TEST', 'REWRITE' ) as mv_report from dual ;

MV_REPORT
--------------------------------------------------------------------------------
Not Capable of:
REWRITE

REWRITE_FULL_TEXT_MATCH
query rewrite is disabled on the materialized view

REWRITE_PARTIAL_TEXT_MATCH
query rewrite is disabled on the materialized view

REWRITE_GENERAL
query rewrite is disabled on the materialized view

The above report clarifies that the MV has no query rewrite feature available.

Enable Query Rewrite:
SQL>alter materialized view test enable query rewrite;

Generate Report:
SQl> set long 5000
SQL> select my_mv_capabilities( 'TEST', 'REWRITE' ) as mv_report from dual ;

MV_REPORT
--------------------------------------------------------------------------------
Capable of:
REWRITE

REWRITE_FULL_TEXT_MATCH

REWRITE_PARTIAL_TEXT_MATCH

REWRITE_GENERAL

The above report clarifies that the MV has no query rewrite feature enabled.

Effect of query rewrite on PLAN:
A very good analysis is available below:
http://www.sqlsnippets.com/en/topic-12918.html

How Query Rewrite can be enabled from database level:
Following 2 initialization parameters can enable this feature in database:
query_rewrite_enabled string TRUE
query_rewrite_integrity string enforced

Convert Single Instance Database to RAC and ASM

Convert a Single Instance Database to Oracle RAC 10g on RHEL3:

Learn how to convert a single-instance database to Oracle Real Application Clusters (RAC) 10g on Red Hat Enterprise Linux 3 (RHEL3), step by step.
Contents
Overview
Step 1: Preliminary Installation
Step 2: Migrate Your Database to ASM
Step 3: Install Cluster Ready Services (CRS) Software
Step 4: Install Oracle RAC Software
Step 5: Post Installation
Step 6: Test Transparent Application Failover (TAF)Conclusion

Download for this article:
Oracle Database 10g Enterprise Edition and Clusterware for Linux x86
Oracle ASMlib Drivers

For those with limited hands-on experience, implementing Oracle RAC 10g can be an intimidating prospect. But it doesn't have to be that way.

In this guide, I'll provide the precise procedures for converting a single-instance Oracle 10g database to Oracle RAC on RHEL3. We'll use Oracle Automatic Storage Management (ASM), an integrated file system and a volume management solution built into the Oracle database kernel, for RAC database storage because it simplifies database storage management tasks while offering storage reliability and performance.

To summarize on a very high level, there are several ways to convert your database to RAC. If the database is small, you can consider installing a new RAC database on ASM and export/import your data from your current database to the RAC database. For a larger database, the preferred method would be to use RMAN to migrate the database files over to ASM.

The method demonstrated here is a two-phase approach:

first, migrating the data to ASM, and
second, converting the single-instance database to RAC.

If you are new to ASM, I would recommend taking this migration path to get familiar with ASM before leaping into ASM and RAC at the same time. (For more information about Oracle ASM, visit the Oracle ASM Product Center or refer to the documentation.)

This guide requires a basic understanding of RAC architecture and some familiarity with managing and administering Oracle Database and Red Hat Enterprise Linux. Refer to the documentation for details.

The detailed stepwise guidance is available at the below URL:
http://www.oracle.com/technology/pub/articles/chan_sing2rac_install.html

The above URL holds a beautiful document to migrate a single instance database from Cluster file system to ASM and to RAC.

Optimizer Hints

Oracle10g New Optimizer Hints:
With the 10g database release, there are many new optimizer hints available to control the optimization rules. Here are few for your understanding. Follow the URL and get an idea about the Oracle Hints.
http://www.fast-track.cc/10g_24.htm

Create Materialized View

Create Materialized View:

CREATE MATERIALIZED VIEW TEST.DW_CUSTOMERLIFECYCLE
ON PREBUILT TABLE WITH REDUCED PRECISION
REFRESH COMPLETE
START WITH TO_DATE('02-Jun-2009 02:00:00','dd-mon-yyyy hh24:mi:ss')
NEXT TRUNC(SYSDATE+1) + 2/24
WITH PRIMARY KEY
AS
SELECT ORGANIZATIONUNITID UPOID,STATUS,EVENTNAME,EFFECTIVEFROM,EFFECTIVETO,
C.CREATEDBY,C.CREATIONDATE,C.LASTMODIFIEDBY,C.lastmodifiedDate
FROM customerlifecycle@repo C, DUAL@repo C;

This creates a materialized view and schedule a job by default and uses a prebuilt table. Hence make sure a table is already created. The materialized view created will do complete refresh as defined in the script.

Materialized View Refresh

Oracle Database Materialized View Refresh:
---------------------------------------------------------------
Refresh Option:
--------------------------
COMPLETE
C
Refreshes by recalculating the materialized view's defining query when atomic refresh=TRUE and COMPLETE is the same as FORCE if atomic refresh=FALSE.
FAST
F
Refreshes by incrementally applying changes to the detail tables.
FORCE
?
Uses the default refresh method. If the default refresh method is FORCE, it tries to do a fast refresh. If that is not possible, it does a complete refresh.
ALWAYS
A
Unconditionally does a complete refresh.

Refresh operations:
----------------------------
DBMS_MVIEW.REFRESH
Refresh one or more materialized views.

DBMS_MVIEW.REFRESH_ALL_MVIEWS
Refresh all materialized views.

DBMS_MVIEW.REFRESH_DEPENDENT
Refresh all table-based materialized views that depend on a specified detail table or list of detail tables.

Required Initialization Parameters for Warehouse Refresh:
---------------------------------------------------------
JOB_QUEUE_PROCESSES
The number of background processes. Determines how many materialized views can be refreshed concurrently.

JOB_QUEUE_INTERVAL
In seconds, the interval between which the job queue scheduler checks to see if a new job has been submitted to the job queue.

UTL_FILE_DIR
Determines the directory where the refresh log is written. If unspecified, no refresh log will be created

Note:These packages also create a log which, by default, is called refresh.log and is useful in helping to diagnose problems during the refresh process. This log file can be renamed by calling the procedure DBMS_OLAP.SET_LOGFILE_NAME ('log filename').

DBMS_SNAPSHOT
---------------------
DBMS_SNAPSHOT enables you to refresh snapshots that are not part of the same refresh group and purge logs.

Note: DBMS_MVIEW is a synonym for DBMS_SNAPSHOT. This synonym may be used in the future with data warehousing.

Examples:
Complete Refresh:
SQL> execute DBMS_SNAPSHOT.REFRESH( 'MV_EMP','c');
PL/SQL procedure successfully completed.

Fast Refresh
SQL> execute DBMS_SNAPSHOT.REFRESH( 'MV_EMP','f');
PL/SQL procedure successfully completed.

Force Refresh
SQL> execute DBMS_SNAPSHOT.REFRESH( 'MV_EMP','?');
PL/SQL procedure successfully completed.

Also provided with DBMS_SNAPSHOT is the REFRESH_ALL procedure. This procedure refreshes all materialized views, which were defined using the automatic refreshes.
SQL> execute DBMS_SNAPSHOT.REFRESH_ALL;
PL/SQL procedure successfully completed.

Oracle Password

Handle Oracle Password:
-----------------------------------
Do you know what to do in case as a DBA you forgot the Oracle database accounts' password.

There is a very simple way to handle this issue. Now let me explain the situation here. As a DBA you might be handling thousands of Oracle Database Accounts and might be maintaining the passwords. Sometimes you might needs some account passwords to handle the issue of application users and everybody knows it is very hard to remember application users' passwords. So in such situations when you need the password and you do not have it. Also you do not want to change the password as it might affect the application users and you do not want to spend time sending emails to application users to collect the password. There you go..An easiest way to handle such Oracle passwords:

SQL> Select username,password from DBA_USERS where username='user';

Output will be:
Username and encrypted password. Save these information.

SQL> Alter user "user" identified by xyz123;

Output will be:
The user's password is changed to xyz123.

Now connect the user with password xyz123 and complete your maintenance acitivity.

After your maintenance activity is over, Change the password to the old password and you are all set. No issues for anybody.

SQL> alter user "user" identified by values 'encrypted password which you collected earlier';
SQL>Select username,password from DBA_USERS where username='user';

You are all set and it matches with the old information.

Let me give you an example for your ref:

SQL> select username, password from dba_users where username='SQLTXPLAIN';
USERNAME PASSWORD
------------------------------ ------------------------------
SQLTXPLAIN 04F1603C1F2033F3
SQL> conn SQLTXPLAIN/webex123
Connected.
SQL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit ProductionWith the Partitioning, Real Application Clusters, OLAP, Data Miningand Real Application Testing options

sqlplus '/as sysdba'
SQL*Plus: Release 10.2.0.4.0 - Production on Thu May 14 13:12:07 2009
Copyright (c) 1982, 2007, Oracle. All Rights Reserved.
Connected to:Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit ProductionWith the Partitioning, Real Application Clusters, OLAP, Data Miningand Real Application Testing options
SQL> alter user SQLTXPLAIN identified by test123;
User altered.
SQL> select username, password from dba_users where username='SQLTXPLAIN';
USERNAME PASSWORD
------------------------------ ------------------------------
SQLTXPLAIN 6CFD89ED56C7C349
SQL> conn SQLTXPLAIN/test123
Connected.
SQL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit ProductionWith the Partitioning, Real Application Clusters, OLAP, Data Miningand Real Application Testing options

sqlplus '/as sysdba'
SQL*Plus: Release 10.2.0.4.0 - Production on Thu May 14 13:12:43 2009
Copyright (c) 1982, 2007, Oracle. All Rights Reserved.
Connected to:Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit ProductionWith the Partitioning, Real Application Clusters, OLAP, Data Miningand Real Application Testing options

SQL> alter user SQLTXPLAIN identified by values '04F1603C1F2033F3';
User altered.
SQL> conn SQLTXPLAIN/webex123
Connected.
SQL> show user
USER is "SQLTXPLAIN"
SQL> select username, password from dba_users where username='SQLTXPLAIN';
USERNAME PASSWORD
------------------------------ ------------------------------
SQLTXPLAIN 04F1603C1F2033F3
SQL>

Terminating Oracle Sessions

Terminating Sessions and Cleaning Up Processes:
Purpose
This module describes how you can terminate sessions and clean up session-related operating system processes.

Topics:
This module discusses the following:
Overview
Terminating Sessions Using SQL*Plus
Terminating Sessions Using Enterprise Manager
Terminating Session-related Operating System Processes on UNIX
Terminating Session-related Operating System Processes on Windows

Terminating Sessions Using SQL*Plus:
You can terminate sessions with the ALTER SYSTEM KILL command. When you issue the ALTER SYSTEM KILL session command, you must specify the session's index number and serial number. To identify the session index number (sid) and serial number of a session, query the V$SESSION dynamic performance view as shown below. The value of the STATUS column will be ACTIVE when the session is making a SQL call to Oracle. It will be INACTIVE if it is not making a SQL call to Oracle.

Identify the correct session and terminate the session by performing the steps below:

1.Invoke SQL*Plus.

2.Query V$SESSION supplying the username for the session you want to terminate:

SELECT SID, SERIAL#, STATUS, SERVERFROM V$SESSIONWHERE USERNAME = ''

3.Execute the ALTER SYSTEM command to terminate the session:

ALTER SYSTEM KILL SESSION ''

4.Query V$SESSION:

SELECT SID, SERIAL#, STATUS, SERVERFROM V$SESSIONWHERE USERNAME = ''

5.After PMON has cleaned up after the session, the row is removed from V$SESSION:

SELECT SID, SERIAL#, STATUS, SERVERFROM V$SESSIONWHERE USERNAME = ''

Terminating Sessions Using Enterprise Manager:
Identify the correct session and terminate the session by performing the steps below.
Note: Oracle Enterprise Manager 9.2 was used in the examples.
1.Select START > Programs > Oracle > OraHome92 > Enterprise Manager Console. Select Launch Standalone and click OK.

2.Expand Databases. Expand your database. Expand Instance and select Sessions. Identify the session you want to terminate.

3.Expand Sessions. Select the session you want to terminate and click Kill Session:

4.Select Immediate and click OK:

5.STATUS changes to KILLED and SERVER changes to PSEUDO:

6.The row is removed after PMON has cleaned up after the session.

Terminating Session-related Operating System Processes on UNIX:
The PMON background process will clean up after any user session you terminate with the ALTER SYSTEM KILL SESSION command. You can kill the session-related operating system process by performing the steps outlined below:

1.Invoke SQL*Plus.

2.Issue the following query to determine the operating system process identifier (spid):
SELECT spidFROM v$processWHERE NOT EXISTS (SELECT 1
FROM v$session WHERE paddr = addr);

3.If you are unable to identify the operating system process identifier (spid) from the query in step 2, you can issue the following query to help identify the correct session:
SELECT s.sid, s.serial#, p.spid
FROM v$process p, v$session s
WHERE p.addr = s.paddr
AND s.username = '';

4.At the operating system prompt, issue the kill command and supply the operating system process identifier (spid):
kill

Terminating Session-related Operating System Processes on Windows:
The PMON background process will clean up after any user session you terminate with the ALTER SYSTEM KILL SESSION command. You can kill the session-related operating system process by performing the steps outlined below:

1.Invoke SQL*Plus.

2.Issue the following query to determine the operating system process identifier (spid) or thread:
SELECT spid, s.osuser, s.programFROM v$process p, v$session sWHERE p.addr = s.paddr;

3.At the operating system prompt, issue the orakill command. Supply the SID and the thread which you obtained from the SPID column in step 2:
orakill

Database Administration

Oracle 10g Database Administration:
Nice steps to Manage a 10G database. Almost all management steps for a 10G database is available in the below link.
http://www.oracle-dba-online.com/