SQLTXPLAIN

Oracle SQLTXPLAIN:
-----------------------------------
SQLTXPLAIN is a tool that generates a comprehensive report about a SQL statement which has been provided as input to the tool. The report includes the explain plan for a given SQL statement, cost-based optimizer statistics for the objects accessed by the statement, and details of related objects. The output HTML report produced can be used to analyze and diagnose SQL performance issues (SQL tuning).

Unlike SQL statements "Explain Plan" and "Autotrace", SQLTXPLAIN offers complete environment and configuration reports relating the SQL we exmaines. The report is categorized as followings.

Environment
SQL Identification
SQL Statement
Explain Plan
Observations
Object Dependency
Objects
Tables
Indexes
Table Columns
Index Columns
Constraints
Column Histograms
Metadata
Tablespaces
Initialization Parameters
Optimizer Trace (EVENT 10053)
Tool Configuration Parameters

During the installation of SQLTXPLAIN, the following parameters will be asked for value.
Specify SQLTXPLAIN password:

Enter value for host_string: (For instance, it your db name is prod, then input @prod)
Enter value for application_schema: (the schema under which you examine sql statement)
Enter value for default_tablespace:
Enter value for temporary_tablespace:

SQLT inputs one SQL DML statement provided as one of these methods:
1. XPLAIN: As a stand-alone SQL in a flat text file.
2. XTRACT: As a memory-resident SQL, identified by its hash_value or sql_id.
3. XECUTE: As a stand-alone script that contains one SQL, together with the declaration and values of its bind variables.
4. XTRXEC: As a combination of XTRACT and XECUTE, this method gets a SQL from memory and executes both XTRACT and XECUTE.

SQLT provides the following benefits:
1. Consistent snapshot of most commonly used pieces of information associated to a SQL analysis and tuning effort, like CBO statistics and performance dynamic views. For most of these objects, inconsistent snapshots of different objects would render the analysis inaccurate or inconclusive.
2. Reduce the number of trips (thus overall time) between people involved in the SQL analysis and tuning. This is specially relevant in systems where the person doing the analysis is external to the organization or does not have full access to the system experiencing poor performance.
3. Preserves CBO statistics and initialization parameters in effect at the time of the analysis, making it easier to reverse the environment to a known state. Further more, the associated CBO statistics can easily be migrated to a similar system in order to try to replicate the same plan good or bad, thus the query performance.
4. Since the tool is installed into its own schema and makes use of temporary objects for most of its data structures, it is lite and moderate intrusive. The schema owner SQLTXPLAIN only gets granted a small subset of roles and attributes in order to generate SQL analysis results. This SQLTXPLAIN schema owner does not read application data itself.

The below metalink note will gove you the full details regarding SQLTXPLAIN tool and its usage. The software is also available in the same note.

215187.1: SQLT (SQLTXPLAIN) - Enhanced Explain Plan and related diagnostic information for one SQL

For 9i, 10g and 11g instances, download tool from sqlt.zip. Read intructions.txt included in zip file.
For output samples (9i, 10g and 11g), download sqlt_sample.zip
For 8i instances, download and use old tool from sqlt_old.zip

Oracle Statspack

Oracle STATSPACK installation steps:
Refer the below link for statspack installation and scheduling the same for automatic statspack data collection.
http://www.dba-oracle.com/t_statspack_installation_steps.htm

Overview of STATSPACK Scripts:
The STATSPACK scripts have completely changed. All of the STATSPACK scripts are located in the $ORACLE_HOME/rdbms/admin directory. Here is the link to understand the importance of the scripts.
http://www.dba-oracle.com/t_statspack_install_scripts.htm

Oracle Statspack Maintenance:
The Statspack data maintenance scripts must be run as the PERFSTAT user.

SPPURGE.SQL: Purges a limited range of Snapshot IDs for a given database instance.
SPTRUNC.SQL: Truncates all performance data in Statspack tables
SPUEXP.PAR: An export parameter file supplied for exporting the whole PERFSTAT user.

This is how you can know what is going on with the statspack owner and tablespace usage.

SQL> select OWNER, sum(bytes)/1024/1024 "Size in MB" from dba_segments where TABLESPACE_NAME='PERFSTAT' group by OWNER order by sum(bytes)/1024/1024 desc;
OWNER Size in MB
------------------------------ ----------
PERFSTAT 21238.6094
1 row selected.

This is how we can know how long the maintenance has not been done on statspack data. This will also give an idea about the statspack data availability.

SQL> select to_char(min(SNAP_TIME), 'YYYYMMDD HH24:MI:SS') "Oldest Snap", to_char(max(SNAP_TIME), 'YYYYMMDD HH24:MI:SS') "Latest Snap", round(max(SNAP_TIME)-min(SNAP_TIME)) "sppurge run before x days" from stats$snapshot;
Oldest Snap Latest Snap sppurge run before x days
----------------- ----------------- -------------------------
20071025 15:00:01 20090526 10:15:03 579

Based on the above information you can decide whether you want to keep the data or do some maintenance like purging old statspack data to avoid the growth of the tablespace.

Logical vs Physical Standby database

Logical vs Physical Standby database:
------------------------------------------------
The primary functional difference between logical and physical standby database setups is that:

Physical standby database: This is always an exact structural duplicate of the master database.
Logical standby database: Permits you to add additional objects (tables, indexes, etc) to the database.
Logical standby database: Logical standby is based on newer technologies (logical standby is new in Oracle 9.2) and tends to be generally regarded as more tempramental than physical standby.

Physical standby database: This is physically identical to the primary database on a block-for-block basis. It's maintained in managed recovery mode to remain current and can be set to read only. Archive logs are copied and applied.
Logical standby database: This is logically identical to the primary database. It is updated using SQL statements.

Physical Standby database: Achieved by copying the archivle log files.
Logical Standby database: Makes the dml statements using archivelogs generated and applies to the secondary database.

Physical Standby database: Archived redo logs and FTP'ed directly to the standby database which is always running in "recover" mode. Upon arrival, the archived redo logs are applied directly to the standby database.
Logical standby database: This uses LogMiner techniques to transform the archived redo logs into native DML statements (insert, update, delete). This DML is transported and applied to the standby database.
Logical standby database: This can have additional materialized views and indexes added for faster performance.

Learn more how to create physical or logical standby databases for your enterprise.
http://www.orafaq.com/node/957

sed Unix Command

Sed:
-------------
How to use sed, a special editor for modifying files automatically. If you want to write a program to make changes in a file, sed is the tool to use.

There are a few programs that are the real workhorse in the Unix toolbox. These programs are simple to use for simple applications, yet have a rich set of commands for performing complex actions. Don't let the complex potential of a program keep you from making use of the simpler aspects. This chapter, like all of the rest, start with the simple concepts and introduces the advanced topics later on. A note on comments. When I first wrote this, most versions of sed did not allow you to place comments inside the script. Lines starting with the '#' characters are comments. Newer versions of sed may support comments at the end of the line as well.

Find more about sed at:
http://www.grymoire.com/Unix/Sed.html

Find Unix Command

FIND:
--------------
This topic covers the various options of unix command called "FIND". Following are the Table of Contents:
Introduction to find
Problems with other methods
The Simplest Example
"Using find with other commands'
Using xargs with find
Looking for files with particular names
Looking for files by type
Looking for files by sizes
Searching for old files
Searching for files by permission
Owners and groups
Find and commands
Find and Cpio
Using Find to Execute Commands
File comparisons
Expressions
Keeping find from going too far
Fast Find

The below link covers all the above listed table of contents:

http://www.grymoire.com/Unix/Find.html#uh-6

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.

Data Guard Protection Modes

Oracle 10G Data Guard Protection Modes:
---------------------------------------------------------
This chapter contains the following sections:

• Data Guard Protection Modes
• Setting the Data Protection Mode of a Primary Database

Data Guard Protection Modes:
This section describes the Data Guard protection modes.
In these descriptions, a synchronized standby database is meant to be one that meets the minimum requirements of the configured data protection mode and that does not have a redo gap.

Maximum Availability:
This protection mode provides the highest level of data protection that is possible without compromising the availability of a primary database. Transactions do not commit until all redo data needed to recover those transactions has been written to the online redo log and to at least one synchronized standby database. If the primary database cannot write its redo stream to at least one synchronized standby database, it operates as if it were in maximum performance mode to preserve primary database availability until it is again able to write its redo stream to a synchronized standby database.

This mode ensures that no data loss will occur if the primary database fails, but only if a second fault does not prevent a complete set of redo data from being sent from the primary database to at least one standby database.

Maximum Performance:
This protection mode provides the highest level of data protection that is possible without affecting the performance of a primary database. This is accomplished by allowing transactions to commit as soon as all redo data generated by those transactions has been written to the online log. Redo data is also written to one or more standby databases, but this is done asynchronously with respect to transaction commitment, so primary database performance is unaffected by delays in writing redo data to the standby database(s).

This protection mode offers slightly less data protection than maximum availability mode and has minimal impact on primary database performance.

This is the default protection mode.

Maximum Protection
This protection mode ensures that zero data loss occurs if a primary database fails. To provide this level of protection, the redo data needed to recover a transaction must be written to both the online redo log and to at least one synchronized standby database before the transaction commits. To ensure that data loss cannot occur, the primary database will shut down, rather than continue processing transactions, if it cannot write its redo stream to at least one synchronized standby database.

Because this data protection mode prioritizes data protection over primary database availability, Oracle recommends that a minimum of two standby databases be used to protect a primary database that runs in maximum protection mode to prevent a single standby database failure from causing the primary database to shut down.

Setting the Data Protection Mode of a Primary Database
Perform the following steps to change the data protection mode of a primary database:
Step 1 Select a data protection mode that meets your availability, performance and data protection requirements.

Step 2 Verify that redo transport is configured to at least one standby database
The value of the LOG_ARCHIVE_DEST_n database initialization parameter that corresponds to the standby database must include the redo transport attributes listed below for the data protection mode that you are moving to.

If the primary database has more than one standby database, only one of those standby databases must use the redo transport settings.

The standby database must also have a standby redo log.

Required Redo Transport Attributes for Data Protection Modes

Maximum Availability:

• AFFIRM
• SYNC
• DB_UNIQUE_NAME

Maximum Performance:

• NOAFFIRM
• ASYNC
• DB_UNIQUE_NAME

Maximum Protection:

• AFFIRM
• SYNC
• DB_UNIQUE_NAME

Step 3 Verify that the DB_UNIQUE_NAME database initialization parameter has been set to a unique name on the primary and standby database.

For example, if the DB_UNIQUE_NAME parameter has not been defined on either database, the following SQL statements might be used to assign a unique name to each database.

Execute this SQL statement on the primary database:

SQL> ALTER SYSTEM SET DB_UNIQUE_NAME='CHICAGO' SCOPE=SPFILE;

Execute this SQL statement on the standby database:

SQL> ALTER SYSTEM SET DB_UNIQUE_NAME='BOSTON' SCOPE=SPFILE;

Step 4 Verify that the LOG_ARCHIVE_CONFIG database initialization parameter has been defined on the primary and standby database and that its value includes a DG_CONFIG list that includes the DB_UNIQUE_NAME of the primary and standby database.

For example, if the LOG_ARCHIVE_CONFIG parameter has not been defined on either database, the following SQL statement could be executed on each database to configure the LOG_ARCHIVE_CONFIG parameter:

SQL> ALTER SYSTEM SET LOG_ARCHIVE_CONFIG='DG_CONFIG=(CHICAGO,BOSTON)';

Step 5 Shut down the primary database and restart it in mounted mode if the protection mode is being set to Maximum Protection or being changed from Maximum Performance to Maximum Availability. If the primary database is an Oracle Real Applications Cluster, shut down all of the instances and then start and mount a single instance.
For example:

SQL> SHUTDOWN IMMEDIATE;
SQL> STARTUP MOUNT;

Step 6 Set the data protection mode.
Execute the following SQL statement on the primary database:

SQL> ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE {AVAILABILITY PERFORMANCE PROTECTION};

If the primary database is an Oracle Real Applications Cluster, any instances stopped in Step 5 can now be restarted.

Step 7 Open the primary database.
If the database was restarted in Step 5, open the database:
SQL> ALTER DATABASE OPEN;

Step 8 Confirm that the primary database is operating in the new protection mode.
Perform the following query on the primary database to confirm that it is operating in the new protection mode:
SQL> SELECT PROTECTION_MODE FROM V$DATABASE;

Automatic Storage Management

ASM using ASMLib and Raw Devices:
--------------------------------------------
This is a beutiful document which covers almost the full setup for an ASM (Automatic Storage Management ) environment. For your reference it covers the following:

• Introduction
• Partition the Disks
• ASMLib Installation
• Raw Device Setup
• ASM Creation
• Database Creation
• Switching from Raw Devices to ASMLib
• Switching from ASMLib to Raw Devices
• Performance Comparison

http://www.oracle-base.com/articles/10g/ASMUsingASMLibAndRawDevices.php#raw_device_setup

Automatic Storage Management (ASM) in Oracle Database 10g:
-------------------------------------------------------------- ---------------
This document covers a small part of Oracle Automatic Storage Management (ASM) Commands to Manage an ASM instance and database. Also You will get a quick idea how to Migrate a Oracle single instance database to ASM using RMAN.

http://www.oracle-base.com/articles/10g/AutomaticStorageManagement10g.php

Commanding ASM (Automatic Storage Management)
----------------------------------------------------------
Access, transfer, and administer ASM files without SQL commands.

Oracle Database 10g Release 1 introduced Automatic Storage Management (ASM), a new framework for managing Oracle database files. ASM provided a foundation for highly efficient storage management with kernelized asynchronous I/O, direct I/O, redundancy, striping, and an easy way to manage storage. ASM includes volume management functionality similar to that of a generic logical volume manager (LVM).

Managing ASM through SQL interfaces in Oracle Database 10g Release 1 posed a challenge for system administrators who were not very familiar with SQL and preferred a more conventional command-line interface. In Oracle Database 10g Release 2, you have an option to manage the ASM files by using ASMCMD—a powerful and easy-to-use command-line tool.

In Oracle Database 10g Release 1, ASM disk groups are not visible outside the database for regular file system administration tasks such as copying and creating directories. In Oracle Database 10g Release 2, however, you can transfer the files from ASM to locations outside of the disk groups via FTP and through a Web browser using HTTP.

This article shows you how to use these enhancements in ASM to accomplish everyday tasks.
http://www.oracle.com/technology/oramag/oracle/06-mar/o26asm.html

Set asmcmd prompt display:
You can invoke asmcmd with the -p option to display the current path, very similar to the UNIX/Linux.
PS1="
`hostname`*\${ORACLE_SID}-\${PWD}
>"
export PS1

fred:/u01/app/oracle/admin>

asmcmd command line history:
The asmcmd utility does not provide a command history with the up-arrow key. With rlwrapinstalled, this can be fixed by adding the following entry to the ~oracle/.bashrc file:

alias asmcmd='rlwrap asmcmd'

Automatic Storage Manager (ASM) Enhancements in Oracle Database 11g Release 1:
----------------------------------------------------------------------------
http://www.oracle-base.com/articles/11g/AsmEnhancements_11gR1.php

SQL Help for ASM (Automatic Storage Management):
--------------------------------------------------------------
Create Diskgroup:
Use the CREATE DISKGROUP clause to create a collection of disks. Oracle Database manages a disk group as a logical unit and evenly spreads each file across the disks to balance I/O. Oracle Database also automatically distributes database files across all available disks in disk groups and rebalances storage automatically whenever the storage configuration changes.

This statement creates a disk group, assigns one or more disks to the disk group, and mounts the disk group for the first time. If you want Automatic Storage Management to mount the disk group automatically in subsequent instances, you must add the disk group name to the value of the ASM_DISKGROUPS initialization parameter in the initialization parameter file. If you use an SPFILE, then the disk group is added to the initialization parameter automatically.
http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/statements_5008.htm#i2153287

Alter Diskgroup:
The ALTER DISKGROUP statement lets you perform a number of operations on a disk group or on the disks in a disk group.
http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/statements_1006.htm#i2166968

Drop Diskgroup:
The DROP DISKGROUP statement lets you drop an Automatic Storage Management disk group along with all the files in the disk group. Automatic Storage Management first ensures that no files in the disk group are open. It then drops the disk group and all its member disks and clears the disk header.
http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/statements_8013.htm#i2152629

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>