LOGIN into ORACLE DB with out PASSWORD

LOGIN into ORACLE DB with out PASSWORD 

STEP 1:  First, we extract the encrypted password:

select 
'alter user "'||username||'" identified by values '''||extract(xmltype(dbms_metadata.get_xml('USER',username)),'//USER_T/PASSWORD/text()').getStringVal()||''';'  old_password 
from 
   dba_users
where
username = ‘FRED’;
 
OLD_PASSWORD
--------------------------------------------------------------------
alter user "FRED" identified by values '15EC3EC6EAF863C';

STEP 2:  You can now change FRED’s password and sign-on for testing:

alter user FRED identified by FLINTSTONE;
connect fred/flintstone;
select stuff from tables;

STEP 3:  When you have completed your testing you can set-back the original encrypted password using the output from the query in step 1:

alter user "FRED" identified by values '15EC3EC6EAF863C';

The above works only for case insensitive passwords (10g).

To retrieve 10g and 11g hashes, use the following:

set lin 200 hea off longc 1000000 long 1000000 feed off; exec
DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'SQLTERMINATOR',TRUE); 
select replace(DBMS_METADATA.GET_DDL('USER','SCOTT'),'CREATE USER','ALTER USER') from dual; 
ALTER USER "SCOTT" IDENTIFIED BY VALUES'S:F0091E6EDDBA71592E8E9A40B1459492C3E7778B5194A5358A0122DF8FA7;F894844C34402B67'
      DEFAULT TABLESPACE "USERS"
      TEMPORARY TABLESPACE "TEMP";

Login without password

LOGIN iwith out PASSWORD 

STEP 1:  First, we extract the encrypted password:

select 
'alter user "'||username||'" identified by values '''||extract(xmltype(dbms_metadata.get_xml('USER',username)),'//USER_T/PASSWORD/text()').getStringVal()||''';'  old_password 
from 
   dba_users
where
username = ‘FRED’;
 
OLD_PASSWORD
--------------------------------------------------------------------
alter user "FRED" identified by values '15EC3EC6EAF863C';

STEP 2:  You can now change FRED’s password and sign-on for testing:

alter user FRED identified by FLINTSTONE;
connect fred/flintstone;
select stuff from tables;

STEP 3:  When you have completed your testing you can set-back the original encrypted password using the output from the query in step 1:

alter user "FRED" identified by values '15EC3EC6EAF863C';

The above works only for case insensitive passwords (10g).

To retrieve 10g and 11g hashes, use the following:

set lin 200 hea off longc 1000000 long 1000000 feed off; exec
DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'SQLTERMINATOR',TRUE); 
select replace(DBMS_METADATA.GET_DDL('USER','SCOTT'),'CREATE USER','ALTER USER') from dual; 
ALTER USER "SCOTT" IDENTIFIED BY VALUES'S:F0091E6EDDBA71592E8E9A40B1459492C3E7778B5194A5358A0122DF8FA7;F894844C34402B67'
      DEFAULT TABLESPACE "USERS"
      TEMPORARY TABLESPACE "TEMP";

SIMPLE DESCRIPTION about PATCHES

SIMPLE DESCRIPTION about PATCHES

1.Critical Patch Update (CPU)

2.Patch Set Update (PSU)

Critical Patch Update (CPU) now refers to the overall release of security fixes each quarter rather than the cumulative database security patch for the quarter. Think of the CPU as the overarching quarterly release and not as a single patch.

Patch Set Updates (PSU) are the same cumulative patches that include both the security fixes and priority fixes. The key with PSUs is they are minor version upgrades (e.g., 11.2.0.1.1 to 11.2.0.1.2). Once a PSU is applied, only PSUs can be applied in future quarters until the database is upgraded to a new base version.

Security Patch Update (SPU) terminology is introduced in the October 2012 Critical Patch Update as the term for the quarterly security patch. SPU patches are the same as previous CPU patches, just a new name. For the database, SPUs can not be applied once PSUs have been applied until the database is upgraded to a new base version.

Bundle Patches are the quarterly patches for Windows and Exadata which include both the quarterly security patches as well as recommended fixes.

Keeping your Oracle database software up to date is a critical and time-consuming task for DBAs. For many years now, Oracle has been releasing Critical Patch Updates on a quarterly basis. These patches, as the name implies, contain critical updates to the software, often released in response to a newly found security vulnerability. More recently, Oracle has also been releasing Patch Set Updates on a quarterly basis. These also contain important fixes to the Oracle software. However, there is confusion about the difference between the two and more importantly, confusion about which one needs to be applied. So whats the difference and which one should you apply?

According to Oracle Support article ID 1446582.1: Frequently Asked Questions (FAQ) Patching Oracle Database Server:

“A PSU is a collection of proactive, stabilizing cumulative patches for a particular product version (base release or patch set). PSUs are cumulative and include all of the security fixes from CPU patches, plus additional fixes. Critical Patch Updates are the primary means of releasing security fixes for Oracle products. CPUs are cumulative with respect to prior CPUs and generally contain only security fixes.”

So, there you have it. CPUs are smaller and more focused than PSU and mostly deal with security issues. PSUs contain bug fixes AND they contain the security fixes from the CPU. When you download a PSU, it will tell you which CPU it contains. PSUs are on the same quarterly schedule as the Critical Patch Updates (CPU), specifically the Tuesday closest to the 17th of January, April, July, and October. One thing to keep in mind, however, is that once a PSU has been installed, the recommended way to get future security content is to apply subsequent PSUs. Reverting from PSU back to CPU, while possible, would require significant effort and so is not advised. So with this in mind, why would someone choose to apply a CPU rather than a PSU? I suppose for folks who are concerned only with security fixes and not functionality fixes, a CPU-only approach may be best. It does seem to be the more conservative approach as a CPU is (in theory) less like to cause trouble than a PSU, simply because it has less code changes in it.

10g Data Guard, Physical Standby Creation, step by step

10g Data Guard, Physical Standby Creation, step by step


Creating a Data Guard Physical Standby environment, 

General Review.
Manually setting up a Physical standby database is a simple task when all prerequisites and setup steps are carefully met and executed.
In this example I did use 2 hosts, that host a RAC database. All RAC preinstall requisites are then in place and no additional configuration was
necessary to implement Data Guard Physical Standby manually. Note that using Enterprise Manager Grid Control Data Guard Physical Standby
can be implemented from the Grid Control Console easily. Still, this exercise provide a degree of familiarity with Data Guard.


The Enviroment
2 Linux servers, Oracle Distribution 2.6.9-55 EL i686 i386 GNU/Linux
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0
ssh is configured for user oracle on both nodes
Oracle Home is on identical path on both nodes


Implementation notes:
Once you have your primary database up and running these are the steps to follow:
1. Enable Forced Logging
2. Create a Password File
3. Configure a Standby Redo Log
4. Enable Archiving
5. Set Primary Database Initialization Parameters


Having followed these steps to implement the Physical Standby you need to follow these steps:
1. Create a Control File for the Standby Database
2. Backup the Primary Database and transfer a copy to the Standby node.
3. Prepare an Initialization Parameter File for the Standby Database
4. Configure the listener and tnsnames to support the database on both nodes
5. Set Up the Environment to Support the Standby Database on the standby node.
6. Start the Physical Standby Database
7. Verify the Physical Standby Database Is Performing Properly


Step by Step Implementation of a Physical Standby Environment
Primary Database Steps
Primary Database General View
SQL> select name from v$database;
NAME
---------
WHITEOWL

SQL> select file_name from dba_data_files;
FILE_NAME
--------------------------------------------------------------------------------
/vmasmtest/od01/WHITEOWL/WHITEOWL/datafile/o1_mf_users_310mzml9_.dbf
/vmasmtest/od01/WHITEOWL/WHITEOWL/datafile/o1_mf_sysaux_310mzm34_.dbf
/vmasmtest/od01/WHITEOWL/WHITEOWL/datafile/o1_mf_undotbs1_310mzmk2_.dbf
/vmasmtest/od01/WHITEOWL/WHITEOWL/datafile/o1_mf_system_310mzm27_.dbf
/vmasmtest/od01/WHITEOWL/WHITEOWL/datafile/o1_mf_test2_3117h15v_.dbf
/vmasmtest/od01/WHITEOWL/WHITEOWL/datafile/o1_mf_test3_3117h8nv_.dbf
/vmasmtest/od01/WHITEOWL/WHITEOWL/datafile/o1_mf_test4_3117hk7d_.dbf
7 rows selected.

SQL> select name from v$database;
NAME
---------
WHITEOWL

SQL> show parameters unique
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_unique_name string whiteowl
Enable Forced Logging
In order to implement Standby Database we enable 'Forced Logging'.
This option ensures that even in the event that a 'nologging' operation is done, force logging takes precedence and all operations are logged
into the redo logs.


SQL> ALTER DATABASE FORCE LOGGING;
Database altered.


Create a Password File
A password file must be created on the Primary and copied over to the Standby site. The sys password must be identical on both sites. This is
a key pre requisite in order to be able to ship and apply archived logs from Primary to Standby.
cd $ORACLE_HOME/dbs
[vmractest1] > orapwd file=orapwwhiteowl password=oracle force=y

Configure a Standby Redo Log
A Standby Redo log is added to enable Data Guard Maximum Availability and Maximum Protection modes. It is important to configure the
Standby Redo Logs (SRL) with the same size as the online redo logs.
In this example I'm using Oracle Managed Files, that's why I don't need to provide the SRL path and file name. If you are not using OMF's
you then must pass the full qualified name.

SQL> select * from v$logfile;
GROUP# STATUS TYPE MEMBER IS_
---------- ------- ------- ---------------------------------------------------------------------- ---
3 ONLINE /vmasmtest/od01/WHITEOWL/WHITEOWL/onlinelog/o1_mf_3_310n22jj_.log NO
2 ONLINE /vmasmtest/od01/WHITEOWL/WHITEOWL/onlinelog/o1_mf_2_310n21sx_.log NO
1 ONLINE /vmasmtest/od01/WHITEOWL/WHITEOWL/onlinelog/o1_mf_1_310n215q_.log NO

SQL> select bytes from v$log;
BYTES
----------
52428800
52428800
52428800

SQL> ALTER DATABASE ADD STANDBY LOGFILE GROUP 4 SIZE 50M;
Database altered.

SQL> ALTER DATABASE ADD STANDBY LOGFILE GROUP 5 SIZE 50M;
Database altered.

SQL> ALTER DATABASE ADD STANDBY LOGFILE GROUP 6 SIZE 50M;
Database altered.

SQL> select * from v$logfile
2 /
GROUP# STATUS TYPE MEMBER IS_
---------- ------- ------- ---------------------------------------------------------------------- ---
3 ONLINE /vmasmtest/od01/WHITEOWL/WHITEOWL/onlinelog/o1_mf_3_310n22jj_.log NO
2 ONLINE /vmasmtest/od01/WHITEOWL/WHITEOWL/onlinelog/o1_mf_2_310n21sx_.log NO
1 ONLINE /vmasmtest/od01/WHITEOWL/WHITEOWL/onlinelog/o1_mf_1_310n215q_.log NO
4 STANDBY /vmasmtest/od01/WHITEOWL/WHITEOWL/onlinelog/o1_mf_4_3gznjc9v_.log NO
5 STANDBY /vmasmtest/od01/WHITEOWL/WHITEOWL/onlinelog/o1_mf_5_3gznnrh0_.log NO
6 STANDBY /vmasmtest/od01/WHITEOWL/WHITEOWL/onlinelog/o1_mf_6_3gznrwd7_.log NO
6 rows selected.

Enable Archiving
On 10g you can enable archive log mode by mounting the database and executing the archivelog command:

SQL> startup mount;
ORACLE instance started.
Total System Global Area 285212672 bytes
Fixed Size 1218992 bytes
Variable Size 75499088 bytes
Database Buffers 205520896 bytes
Redo Buffers 2973696 bytes
Database mounted.

SQL> alter database archivelog;
Database altered.

SQL> alter database open;
Database altered.

SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /vmasmtest/whiteowl/archdest/arch
Oldest online log sequence 92
Next log sequence to archive 94
Current log sequence 94


Set Primary Database Initialization Parameters
Data Guard must use spfile, in order to configure it we create and configure the standby parameters on a regular pfile, and once it is ready we
convert it to an spfile.
Several init.ora parameters control the behavior of a Data Guard environment. In this example the Primary database init.ora is configured so
that it can hold both roles, as Primary or Standby.

SQL> create pfile='/oradisk/app01/oracle/product/10gDB/dbs/pfilewhiteowl.ora' from spfile;
File created.

Edit the pfile to add the standby parameters, here shown highlighted:
db_name='whiteowl'
db_unique_name='whiteowl'
LOG_ARCHIVE_CONFIG='DG_CONFIG=(whiteowl,blackowl)'
control_files='/vmasmtest/od01/WHITEOWL/WHITEOWL/controlfile/o1_mf_310n1xf0_.ctl'
LOG_ARCHIVE_DEST_1=
'LOCATION=/vmasmtest/whiteowl/archdest/
VALID_FOR=(ALL_LOGFILES,ALL_ROLES)
DB_UNIQUE_NAME=whiteowl'
LOG_ARCHIVE_DEST_2=
'SERVICE=blackowl LGWR ASYNC
VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE)
DB_UNIQUE_NAME=blackowl'
LOG_ARCHIVE_DEST_STATE_1=ENABLE
LOG_ARCHIVE_DEST_STATE_2=ENABLE
REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE
LOG_ARCHIVE_FORMAT=%t_%s_%r.arc
LOG_ARCHIVE_MAX_PROCESSES=30

# Standby role parameters --------------------------------------------------------------------
*.fal_server=blackowl
*.fal_client=whiteowl
*.standby_file_management=auto
*.db_file_name_convert='BLACKOWL/BLACKOWL','WHITEOWL/WHITEOWL'
*.log_file_name_convert='/vmasmtest/od01/BLACKOWL/BLACKOWL/','/vmasmtest/od01/WHITEOWL/WHITEOWL/'

# ---------------------------------------------------------------------------------------------
audit_file_dest='/oradisk/app01/oracle/admin/whiteowl/adump'
background_dump_dest='/oradisk/app01/oracle/admin/whiteowl/bdump'
core_dump_dest='/oradisk/app01/oracle/admin/whiteowl/cdump'
user_dump_dest='/oradisk/app01/oracle/admin/whiteowl/udump'
compatible='10.2.0.1.0'
db_block_size=8192
db_create_file_dest='/vmasmtest/od01/WHITEOWL'
db_domain=''
db_file_multiblock_read_count=16
job_queue_processes=10
open_cursors=300
pga_aggregate_target=94371840
processes=150
remote_login_passwordfile='EXCLUSIVE'
sga_target=283115520
undo_management='AUTO'
undo_tablespace='UNDOTBS1'
Once the new parameter file is ready we create from it the spfile:

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> startup nomount pfile='/oradisk/app01/oracle/product/10gDB/dbs/pfilewhiteowl.ora';
ORACLE instance started.
Total System Global Area 285212672 bytes
Fixed Size 1218992 bytes
Variable Size 92276304 bytes
Database Buffers 188743680 bytes
Redo Buffers 2973696 bytes

SQL> create spfile from pfile='/oradisk/app01/oracle/product/10gDB/dbs/pfilewhiteowl.ora';
File created.

SQL> shutdown immediate;
ORA-01507: database not mounted
ORACLE instance shut down.

SQL> startup;
ORACLE instance started.
Total System Global Area 285212672 bytes
Fixed Size 1218992 bytes
Variable Size 92276304 bytes
Database Buffers 188743680 bytes
Redo Buffers 2973696 bytes
Database mounted.
Database opened.

Standby Database Steps
Create a Control File for the Standby Database
The standby database will use a control file that is generated on the primary database

SQL> startup mount;
ORACLE instance started.
Total System Global Area 285212672 bytes
Fixed Size 1218992 bytes
Variable Size 92276304 bytes
Database Buffers 188743680 bytes
Redo Buffers 2973696 bytes
Database mounted.

SQL> ALTER DATABASE CREATE STANDBY CONTROLFILE AS
'/oradisk/app01/oracle/product/10gDB/dbs/blackowl.ctl';
Database altered.

SQL> ALTER DATABASE OPEN;
Database altered.
Backup the Primary Database and transfer a copy to the Standby node.
Generate a script to copy datafiles

SQL> set pages 50000 lines 120 head off veri off flush off ti off

SQL> spool cpfiles

SQL> select 'scp -p '||file_name||' $v_dest' from dba_data_files;
scp -p /vmasmtest/od01/WHITEOWL/WHITEOWL/datafile/o1_mf_users_310mzml9_.dbf $v_dest
scp -p /vmasmtest/od01/WHITEOWL/WHITEOWL/datafile/o1_mf_sysaux_310mzm34_.dbf $v_dest
scp -p /vmasmtest/od01/WHITEOWL/WHITEOWL/datafile/o1_mf_undotbs1_310mzmk2_.dbf $v_dest
scp -p /vmasmtest/od01/WHITEOWL/WHITEOWL/datafile/o1_mf_system_310mzm27_.dbf $v_dest
scp -p /vmasmtest/od01/WHITEOWL/WHITEOWL/datafile/o1_mf_test2_3117h15v_.dbf $v_dest
scp -p /vmasmtest/od01/WHITEOWL/WHITEOWL/datafile/o1_mf_test3_3117h8nv_.dbf $v_dest
scp -p /vmasmtest/od01/WHITEOWL/WHITEOWL/datafile/o1_mf_test4_3117hk7d_.dbf $v_dest
7 rows selected.

SQL> select 'scp -p '||file_name||' $v_dest' from dba_temp_files;
scp -p /vmasmtest/od01/WHITEOWL/WHITEOWL/datafile/o1_mf_temp_310n2bnj_.tmp $v_dest

SQL> select 'scp -p '||member||' $v_dest' from v$logfile;
scp -p /vmasmtest/od01/WHITEOWL/WHITEOWL/onlinelog/o1_mf_3_310n22jj_.log $v_dest
scp -p /vmasmtest/od01/WHITEOWL/WHITEOWL/onlinelog/o1_mf_2_310n21sx_.log $v_dest
scp -p /vmasmtest/od01/WHITEOWL/WHITEOWL/onlinelog/o1_mf_1_310n215q_.log $v_dest
scp -p /vmasmtest/od01/WHITEOWL/WHITEOWL/onlinelog/o1_mf_4_3gznjc9v_.log $v_dest
scp -p /vmasmtest/od01/WHITEOWL/WHITEOWL/onlinelog/o1_mf_5_3gznnrh0_.log $v_dest
scp -p /vmasmtest/od01/WHITEOWL/WHITEOWL/onlinelog/o1_mf_6_3gznrwd7_.log $v_dest
6 rows selected.

SQL> spool off
Shutdown the database, edit the script to add the v_dest location, and execute it.
[vmractest1] > mv cpfiles.lst cpfiles

#!/bin/ksh
v_dest=vmractest2:/oradisk/od01/BLACKOWL/datafile/
scp -p /vmasmtest/od01/WHITEOWL/WHITEOWL/datafile/o1_mf_users_310mzml9_.dbf $v_dest
scp -p /vmasmtest/od01/WHITEOWL/WHITEOWL/datafile/o1_mf_sysaux_310mzm34_.dbf $v_dest
scp -p /vmasmtest/od01/WHITEOWL/WHITEOWL/datafile/o1_mf_undotbs1_310mzmk2_.dbf $v_dest
scp -p /vmasmtest/od01/WHITEOWL/WHITEOWL/datafile/o1_mf_system_310mzm27_.dbf $v_dest
scp -p /vmasmtest/od01/WHITEOWL/WHITEOWL/datafile/o1_mf_test2_3117h15v_.dbf $v_dest
scp -p /vmasmtest/od01/WHITEOWL/WHITEOWL/datafile/o1_mf_test3_3117h8nv_.dbf $v_dest
scp -p /vmasmtest/od01/WHITEOWL/WHITEOWL/datafile/o1_mf_test4_3117hk7d_.dbf $v_dest
scp -p /vmasmtest/od01/WHITEOWL/WHITEOWL/datafile/o1_mf_temp_310n2bnj_.tmp $v_dest
v_dest= vmractest2:/oradisk/od01/BLACKOWL/onlinelog/
scp -p /vmasmtest/od01/WHITEOWL/WHITEOWL/onlinelog/o1_mf_3_310n22jj_.log $v_dest
scp -p /vmasmtest/od01/WHITEOWL/WHITEOWL/onlinelog/o1_mf_2_310n21sx_.log $v_dest
scp -p /vmasmtest/od01/WHITEOWL/WHITEOWL/onlinelog/o1_mf_1_310n215q_.log $v_dest
scp -p /vmasmtest/od01/WHITEOWL/WHITEOWL/onlinelog/o1_mf_4_3gznjc9v_.log $v_dest
scp -p /vmasmtest/od01/WHITEOWL/WHITEOWL/onlinelog/o1_mf_5_3gznnrh0_.log $v_dest
scp -p /vmasmtest/od01/WHITEOWL/WHITEOWL/onlinelog/o1_mf_6_3gznrwd7_.log $v_dest


On the standby node create the required directories to get the datafiles
[root@vmractest2 root]# mkdir -p /oradisk/od01/BLACKOWL/datafile/
[root@vmractest2 root]# mkdir -p /oradisk/od01/BLACKOWL/onlinelog/
[root@vmractest2 root]# chown -R oracle:dba /oradisk/od01


On the primary node execute the script to copy the database while the main database is down (or in backup mode)
[vmractest1] > chmod 700 cpfiles
[vmractest1] > ./cpfiles
o1_mf_users_310mzml9_.dbf 100% 2801MB 7.0MB/s 06:37
o1_mf_sysaux_310mzm34_.dbf 100% 340MB 7.4MB/s 00:45
o1_mf_undotbs1_310mzmk2_.dbf 100% 100MB 8.0MB/s 00:12
o1_mf_system_310mzm27_.dbf 100% 490MB 7.0MB/s 01:09
o1_mf_test2_3117h15v_.dbf 100% 100MB 6.5MB/s 00:15
o1_mf_test3_3117h8nv_.dbf 100% 100MB 6.0MB/s 00:16
o1_mf_test4_3117hk7d_.dbf 100% 100MB 6.4MB/s 00:15
o1_mf_temp_310n2bnj_.tmp 100% 433MB 5.8MB/s 01:14
o1_mf_3_310n22jj_.log 100% 50MB 7.5MB/s 00:06
o1_mf_2_310n21sx_.log 100% 50MB 8.4MB/s 00:05
o1_mf_1_310n215q_.log 100% 50MB 8.8MB/s 00:05
o1_mf_4_3gznjc9v_.log 100% 50MB 7.7MB/s 00:06
o1_mf_5_3gznnrh0_.log 100% 50MB 8.2MB/s 00:06
o1_mf_6_3gznrwd7_.log 100% 50MB 4.9MB/s 00:10


Prepare an Initialization Parameter File for the Standby Database

Copy and edit the primary init.ora to set it up for the standby role
*.db_name='whiteowl'
*.db_unique_name='blackowl'
*.audit_file_dest='/oradisk/app01/oracle/admin/blackowl/adump'
*.background_dump_dest='/oradisk/app01/oracle/admin/blackowl/bdump'
*.core_dump_dest='/oradisk/app01/oracle/admin/blackowl/cdump'
*.user_dump_dest='/oradisk/app01/oracle/admin/blackowl/udump'
*.compatible='10.2.0.1.0'
*.control_files='/oradisk/od01/BLACKOWL/controlfile/blackowl_01.ctl','/oradisk/od01/BLACKOWL/controlfile/blackowl_02.ctl'
*.db_block_size=8192
*.db_create_file_dest='/oradisk/od01/BLACKOWL'
*.db_domain=''
*.db_file_multiblock_read_count=16
*.db_file_name_convert='/oradisk/od01/BLACKOWL/datafile/','/vmasmtest/od01/WHITEOWL/WHITEOWL/datafile/'
*.log_file_name_convert='/oradisk/od01/BLACKOWL/onlinelog/','/vmasmtest/od01/WHITEOWL/WHITEOWL/onlinelog/'
*.fal_server='whiteowl'
*.fal_client='blackowl'
*.job_queue_processes=10
*.LOG_ARCHIVE_CONFIG='DG_CONFIG=(whiteowl,blackowl)'
*.LOG_ARCHIVE_DEST_1='LOCATION=/oradisk/od01/BLACKOWL/archives/
VALID_FOR=(ALL_LOGFILES,ALL_ROLES)
DB_UNIQUE_NAME=blackowl'
*.LOG_ARCHIVE_DEST_2='SERVICE=whiteowl LGWR ASYNC
VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE)
DB_UNIQUE_NAME=whiteowl'
*.LOG_ARCHIVE_DEST_STATE_1='ENABLE'
*.LOG_ARCHIVE_DEST_STATE_2='ENABLE'
*.LOG_ARCHIVE_FORMAT='%t_%s_%r.arc'
*.LOG_ARCHIVE_MAX_PROCESSES=30
*.open_cursors=300
*.pga_aggregate_target=94371840
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.sga_target=283115520
*.standby_file_management='auto'
*.undo_management='AUTO'
*.undo_tablespace='UNDOTBS1'


Create all required directories for dump directories and archived log destination
[vmractest2] > mkdir -p /oradisk/app01/oracle/admin/blackowl/adump
{oracle} /oradisk/app01/oracle/product/10gDB/dbs [vmractest2] > mkdir -p /oradisk/app01/oracle/admin/blackowl/bdump
{oracle} /oradisk/app01/oracle/product/10gDB/dbs [vmractest2] > mkdir -p /oradisk/app01/oracle/admin/blackowl/cdump
{oracle} /oradisk/app01/oracle/product/10gDB/dbs [vmractest2] > mkdir -p /oradisk/app01/oracle/admin/blackowl/udump
{oracle} /oradisk/app01/oracle/product/10gDB/dbs [vmractest2] > mkdir -p /oradisk/od01/BLACKOWL/archives/


Copy from the primary the standby controlfile to its destination
[vmractest1] > scp -p blackowl.ctl vmractest2:/oradisk/od01/BLACKOWL/controlfile/blackowl_02.ctl
blackowl.ctl 100% 6992KB 7.2MB/s 00:00
[vmractest1] > scp -p blackowl.ctl vmractest2:/oradisk/od01/BLACKOWL/controlfile/blackowl_01.ctl
blackowl.ctl 100% 6992KB 6.9MB/s 00:00


Configure the listener and tnsnames to support the database on both nodes


Configure listener.ora on both servers to hold entries for both databases
# ON VMRACTEST1
LISTENER_VMRACTEST =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = vmractest1)(PORT = 1522)(IP = FIRST))
)
)
SID_LIST_LISTENER_VMRACTEST =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = whiteowl)
(ORACLE_HOME = /oradisk/app01/oracle/product/10gDB )
(SID_NAME = whiteowl)
)
)

# ON VMRACTEST2
LISTENER_VMRACTEST =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = vmractest2)(PORT = 1522)(IP = FIRST))
)
)
SID_LIST_LISTENER_VMRACTEST =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = blackowl)
(ORACLE_HOME = /oradisk/app01/oracle/product/10gDB )
(SID_NAME = blackowl)
)
)



Configure tnsnames.ora on both servers to hold entries for both databases

# ON VMRACTEST1
LISTENER_VMRACTEST =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = vmractest1)(PORT = 1522)(IP = FIRST))
)
)
WHITHEOWL =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = vmractest1)(PORT = 1522))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = whiteowl)
)
)
BLACKOWL =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = vmractest2)(PORT = 1522))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = blackowl)
)
)


# ON VMRACTEST2
LISTENER_VMRACTEST =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = vmractest2)(PORT = 1522)(IP = FIRST))
)
)
BLACKOWL =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = vmractest2)(PORT = 1522))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = blackowl)
)
)
WHITHEOWL =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = vmractest1)(PORT = 1522))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = whiteowl)
)
)


Start the listener and check tnsping on both nodes to both services
[vmractest1.partnergsm.co.il] > tnsping whiteowl
TNS Ping Utility for Linux: Version 10.2.0.1.0 - Production on 19-SEP-2007 15:10:00
Copyright (c) 1997, 2005, Oracle. All rights reserved.

Used parameter files:
/oradisk/app01/oracle/product/10gDB/network/admin/sqlnet.ora
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST =
vmractest1)(PORT = 1522)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME =
whiteowl)))
OK (10 msec)
{oracle} /oradisk/app01/oracle/product/10gDB/network/admin

[vmractest1.partnergsm.co.il] > tnsping blackowl
TNS Ping Utility for Linux: Version 10.2.0.1.0 - Production on 19-SEP-2007 15:10:09
Copyright (c) 1997, 2005, Oracle. All rights reserved.


Used parameter files:
/oradisk/app01/oracle/product/10gDB/network/admin/sqlnet.ora
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST =
vmractest2)(PORT = 1522)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME =
blackowl)))
OK (10 msec)


Set Up the Environment to Support the Standby Database on the standby node.

Copy the password file from Primary to Standby, sys password must be identical

[vmractest1]> scp orapwwhiteowl
vmractest2:/oradisk/app01/oracle/product/10gDB/dbs/orapwblackowl
orapwwhiteowl 100% 1536 4.0MB/s 00:00


Setup the environment variables to point to the Satndby database
ORACLE_HOME=/oradisk/app01/oracle/product/10gDB
ORACLE_SID=blackowl


Startup nomount the Standby database and generate an spfile
{oracle} /oradisk/app01/oracle/product/10gDB/dbs [vmractest2] > sqlplus / as sysdba
SQL*Plus: Release 10.2.0.1.0 - Production on Wed Sep 19 16:17:18 2007
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to an idle instance.

SQL> startup nomount pfile='/oradisk/app01/oracle/product/10gDB/dbs/initblackowl.ora'
ORACLE instance started.
Total System Global Area 285212672 bytes
Fixed Size 1218992 bytes
Variable Size 92276304 bytes
Database Buffers 188743680 bytes
Redo Buffers 2973696 bytes

SQL> create spfile from pfile='/oradisk/app01/oracle/product/10gDB/dbs/initblackowl.ora';
File created.

SQL> shutdown immediate;
ORA-01507: database not mounted
ORACLE instance shut down.
Startup mount the Standby database and perform recovery

SQL> startup mount
ORACLE instance started.
Total System Global Area 285212672 bytes
Fixed Size 1218992 bytes
Variable Size 92276304 bytes
Database Buffers 188743680 bytes
Redo Buffers 2973696 bytes
Database mounted.

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;
Database altered.

The alert log of the standby will show the operations taking place
…
…
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION
Wed Sep 19 16:46:26 2007
Attempt to start background Managed Standby Recovery process (blackowl)
MRP0 started with pid=47, OS id=12498
Wed Sep 19 16:46:26 2007
MRP0: Background Managed Standby Recovery process started (blackowl)
Managed Standby Recovery not using Real Time Apply
Clearing online redo logfile 1 /oradisk/od01/BLACKOWL/onlinelog/o1_mf_1_310n215q_.log
Clearing online log 1 of thread 1 sequence number 95
Deleted Oracle managed file /oradisk/od01/BLACKOWL/onlinelog/o1_mf_1_310n215q_.log
Wed Sep 19 16:46:32 2007
Completed: ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION
Wed Sep 19 16:46:33 2007
Clearing online redo logfile 1 complete
Clearing online redo logfile 2 /oradisk/od01/BLACKOWL/onlinelog/o1_mf_2_310n21sx_.log
Clearing online log 2 of thread 1 sequence number 96
Deleted Oracle managed file /oradisk/od01/BLACKOWL/onlinelog/o1_mf_2_310n21sx_.log
Clearing online redo logfile 2 complete
Clearing online redo logfile 3 /oradisk/od01/BLACKOWL/onlinelog/o1_mf_3_310n22jj_.log
Clearing online log 3 of thread 1 sequence number 94
Deleted Oracle managed file /oradisk/od01/BLACKOWL/onlinelog/o1_mf_3_310n22jj_.log
Clearing online redo logfile 3 complete
Media Recovery Waiting for thread 1 sequence 96


Start the Primary Database
The alert log of the primary will show how it recognize the standby and start shipping archived logs
******************************************************************
LGWR: Setting 'active' archival for destination LOG_ARCHIVE_DEST_2
******************************************************************
Wed Sep 19 16:01:07 2007
LNS: Standby redo logfile selected for thread 1 sequence 100 for destination
LOG_ARCHIVE_DEST_2
Wed Sep 19 16:01:07 2007
Successfully onlined Undo Tablespace 1.
Wed Sep 19 16:01:07 2007
SMON: enabling tx recovery
Wed Sep 19 16:01:09 2007
Database Characterset is AL32UTF8
replication_dependency_tracking turned off (no async multimaster replication found)
Starting background process QMNC
QMNC started with pid=21, OS id=13864
Wed Sep 19 16:01:12 2007
Completed: ALTER DATABASE OPEN
Wed Sep 19 16:01:13 2007
ARCq: Standby redo logfile selected for thread 1 sequence 99 for destination
LOG_ARCHIVE_DEST_2
Wed Sep 19 16:05:05 2007
Thread 1 advanced to log sequence 101
Current log# 1 seq# 101 mem# 0:
/vmasmtest/od01/WHITEOWL/WHITEOWL/onlinelog/o1_mf_1_310n215q_.log
Wed Sep 19 16:05:06 2007
LNS: Standby redo logfile selected for thread 1 sequence 101 for destination
LOG_ARCHIVE_DEST_2


Verify the Physical Standby Database Is Performing Properly

Check archived redo log on Standby

SQL> show parameters db_unique_name;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_unique_name string blackowl

SQL> l
1* SELECT NAME FROM V$DATABASE

SQL> SELECT SEQUENCE#, FIRST_TIME, NEXT_TIME FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#;
SEQUENCE# FIRST_TIM NEXT_TIME
---------- --------- ---------
96 19-SEP-07 19-SEP-07
97 19-SEP-07 19-SEP-07
98 19-SEP-07 19-SEP-07
99 19-SEP-07 19-SEP-07
100 19-SEP-07 19-SEP-07
Switch logfiles on Primary

SQL> alter system switch logfile;
System altered.

SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /vmasmtest/whiteowl/archdest/
Oldest online log sequence 100
Next log sequence to archive 102
Current log sequence 102

SQL> alter system switch logfile;
System altered.

SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /vmasmtest/whiteowl/archdest/
Oldest online log sequence 101
Next log sequence to archive 103
Current log sequence 103
Check archived redo log on Standby

SQL> SELECT SEQUENCE#, FIRST_TIME, NEXT_TIME FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#;
SEQUENCE# FIRST_TIME NEXT_TIME
---------- -------------- --------------
96 19/09/07 09:35 19/09/07 09:45
97 19/09/07 09:45 19/09/07 15:20
98 19/09/07 15:20 19/09/07 15:48
99 19/09/07 15:48 19/09/07 16:00
100 19/09/07 16:00 19/09/07 16:05
101 19/09/07 16:05 19/09/07 16:08
102 19/09/07 16:08 19/09/07 16:08
7 rows selected.



Reference:
Oracle® Data Guard Concepts and Administration
10g Release 2 (10.2)
Part Number B14239-04
http://download.oracle.com/docs/cd/B19306_01/server.102/b14239/create_ps.htm#SBYDB00210

SIMPLE and SAMPLE PT(Performance Tuning) scripts of database in ORACLE

SIMPLE and SAMPLE PT(Performance Tuning) scripts of database in ORACLE


The database monitoring is required for the following reason:
– Smooth running of production
– Keeping an eye on development
– Database performance
– In Support of an SLA (service level agreement)


Types of DB Monitoring
1. Status
2. Performance
3. Trend Analysis


Status Monitoring:
Monitor the current status of an event and reports when it exceeds a defined threshold.
Database:
– Database/Listener
– Monitor Alert. log Message on regular basis.
– Check all last night backup is successful.
– Tablespace/Datafiles full or Fragmented.
– Identify bad growth of segment.
– Identify at least 1 top resource consuming query
– Monitor Locking
– Check Maximum Extent about to be reached.
– Redo log Tracking
– UNDO and Temp Segment Free space.
– Monitor Running Job
– Tracking DB User/Session Information.
– Important Object Information

OS:
– SGA/PGA information
– CPU Usage Information
– Memory Utilization
– Disk Utilization



Performance Monitoring:

Monitor a defined set of performance statistics. This is done in an effort to maintain the best possible DB performance.

Trend Analysis Monitoring:
Collect the historical data for specified events and analyze these data on schedule basis to reveal any potential problems. For Example watching growth of data in a tablespace and 

predicting when it will fill.
Apart from the above checklist some of the other checklist a DBA are using. It is depend on the requirement. I am mentioning here some of the related query and scripts. It is fully 

related to DB Monitoring Purpose.
Note: Keep every one informed specially your senior or Junior DBA, System Admin, Manager and do not forget to document very important update.
Database Information:

Track OS Reboot Time:
net statistics server
systeminfo | find “Up Time” — to find system last uptime
systeminfo | find “System Boot Time” — to find system boot time
net statistics workstation | find “Statistics” Workstation Statistics for \\A5541TAG-WKS –perticular workstation statistics

Database and Instance Last start time:
SELECT to_char(startup_time,’DD-MON-YYYY HH24:MI:SS’) “DB Startup Time”
FROM sys.v_$instance;
SELECT SYSDATE-logon_time “Days”, (SYSDATE-logon_time)*24 “Hours”
from sys.v_$session where sid=1;

Track Database Version:
SELECT * from v$version;
Track Database Name and ID information:
SELECT DBID, NAME FROM V$DATABASE;‎
Track Database Global Name information:
SELECT * FROM GLOBAL_NAME;‎

Track Database Instance name:
SELECT INSTANCE_NAME FROM V$INSTANCE;‎
Track Database Host Details:
SELECT UTL_INADDR.GET_HOST_ADDRESS, UTL_INADDR.GET_HOST_NAME FROM DUAL;

Track Database Present Status:
SELECT created, RESETLOGS_TIME, Log_mode FROM V$DATABASE;
DB Character Set Information:
Select * from nls_database_parameters;

Track Database default information:
Select username, profile, default_tablespace, temporary_tablespace from dba_users;

Track Total Size of Database:
select a.data_size+b.temp_size+c.redo_size “Total_Size (GB)”
from ( select sum(bytes/1024/1024/1024) data_size
from dba_data_files ) a, ( select nvl(sum(bytes/1024/1024/1024),0) temp_size
from dba_temp_files ) b, ( select sum(bytes/1024/1024/1024) redo_size
from sys.v_$log ) c;

Total Size of Database with free space:
Select round(sum(used.bytes) / 1024 / 1024/1024 ) || ‘ GB’ “Database Size”,round(free.p / 1024 / 1024/1024) || ‘ GB’ “Free space”
from (select bytes from v$datafile
union all
select bytes from v$tempfile
union all
select bytes from v$log) used, (select sum(bytes) as p from dba_free_space) free group by free.p;
Track Database Structure:
select name from sys.v_$controlfile
/
select group#,member from sys.v_$logfile
/
Select F.file_id Id, F.file_name name, F.bytes/(1024*1024) Mbyte,
decode(F.status,’AVAILABLE’,'OK’,F.status) status, F.tablespace_name Tspace
from sys.dba_data_files F
order by tablespace_name;

Tablespace/Datafile/Temp/UNDO Information:

Track Tablespace Used/Free Space:
SELECT /* + RULE */ df.tablespace_name “Tablespace”, df.bytes / (1024 * 1024) “Size (MB)”,
SUM(fs.bytes) / (1024 * 1024) “Free (MB)”, Nvl(Round(SUM(fs.bytes) * 100 / df.bytes),1) “% Free”, Round((df.bytes – SUM(fs.bytes)) * 100 / df.bytes) “% Used”
FROM dba_free_space fs, (SELECT tablespace_name,SUM(bytes) bytes
FROM dba_data_files
GROUP BY tablespace_name) df
WHERE fs.tablespace_name (+) = df.tablespace_name
GROUP BY df.tablespace_name,df.bytes
UNION ALL
SELECT /* + RULE */ df.tablespace_name tspace,
fs.bytes / (1024 * 1024), SUM(df.bytes_free) / (1024 * 1024), Nvl(Round((SUM(fs.bytes) – df.bytes_used) * 100 / fs.bytes), 1), Round((SUM(fs.bytes) – df.bytes_free) * 100 / 

fs.bytes)
FROM dba_temp_files fs, (SELECT tablespace_name,bytes_free,bytes_used
FROM v$temp_space_header
GROUP BY tablespace_name,bytes_free,bytes_used) df
WHERE fs.tablespace_name (+) = df.tablespace_name
GROUP BY df.tablespace_name,fs.bytes,df.bytes_free,df.bytes_used
ORDER BY 4 DESC;

Track all Tablespaces with free space < 10%
Select a.tablespace_name,sum(a.tots/1048576) Tot_Size, sum(a.sumb/1024) Tot_Free, sum(a.sumb)*100/sum(a.tots) Pct_Free, ceil((((sum(a.tots) * 15) – (sum(a.sumb)*100))/85 

)/1048576) Min_Add
from (select tablespace_name,0 tots,sum(bytes) sumb
from dba_free_space a
group by tablespace_name
union
Select tablespace_name,sum(bytes) tots,0 from dba_data_files
group by tablespace_name) a group by a.tablespace_name
having sum(a.sumb)*100/sum(a.tots) < 10
order by pct_free;

Track Tablespace Fragmentation Details:
Select a.tablespace_name,sum(a.tots/1048576) Tot_Size,
sum(a.sumb/1048576) Tot_Free, sum(a.sumb)*100/sum(a.tots) Pct_Free,
sum(a.largest/1024) Max_Free,sum(a.chunks) Chunks_Free
from ( select tablespace_name,0 tots,sum(bytes) sumb,
max(bytes) largest,count(*) chunks
from dba_free_space a
group by tablespace_name
union
select tablespace_name,sum(bytes) tots,0,0,0 from dba_data_files
group by tablespace_name) a group by a.tablespace_name
order by pct_free;

Track Non-Sys owned tables in SYSTEM Tablespace:
SELECT owner, table_name, tablespace_name FROM dba_tables WHERE tablespace_name = 'SYSTEM' AND owner NOT IN ('SYSTEM', 'SYS', 'OUTLN');

Track Default and Temporary Tablespace:
SELECT * FROM DATABASE_PROPERTIES where PROPERTY_NAME like '%DEFAULT%';
select username,temporary_tablespace,default_tablespace from dba_users where username='HRMS'; –for Particular User
Select default_tablespace,temporary_tablespace,username from dba_users; –for All Users
Track DB datafile used and free space:
SELECT SUBSTR (df.NAME, 1, 40) file_name,dfs.tablespace_name, df.bytes / 1024 / 1024 allocated_mb, ((df.bytes / 1024 / 1024) – NVL (SUM (dfs.bytes) / 1024 / 1024, 0)) 

used_mb,
NVL (SUM (dfs.bytes) / 1024 / 1024, 0) free_space_mb
FROM v$datafile df, dba_free_space dfs
WHERE df.file# = dfs.file_id(+)
GROUP BY dfs.file_id, df.NAME, df.file#, df.bytes,dfs.tablespace_name
ORDER BY file_name;

Track Datafile with Archive Details:
SELECT NAME, a.status, DECODE (b.status, 'Active', 'Backup', 'Normal') arc, enabled, bytes, change#, TIME ARCHIVE FROM sys.v_$datafile a, sys.v_$backup b WHERE 

a.file# = b.file#;
Track Datafiles with highest I/O activity:
Select * from (select name,phyrds, phywrts,readtim,writetim
from v$filestat a, v$datafile b
where a.file#=b.file#
order by readtim desc) where rownum 0
ORDER BY elapsed_seconds;
Track Running RMAN backup status:
SELECT SID, SERIAL#, CONTEXT, SOFAR, TOTALWORK,
ROUND(SOFAR/TOTALWORK*100,2) “%_COMPLETE”
FROM V$SESSION_LONGOPS
WHERE OPNAME LIKE ‘RMAN%’ AND OPNAME NOT LIKE ‘%aggregate%’
AND TOTALWORK != 0 AND SOFAR != TOTALWORK;

Monitor Import Rate:
Oracle Import Utility usually takes hours for very large tables and we need to track the execution of Oracle Import Process. Below option can help you monitor the rate at which 

rows are being imported from a running import job.
select substr(sql_text,instr(sql_text,’into “‘),30) table_name,
rows_processed, round((sysdate-to_date(first_load_time,’yyyy-mm-dd hh24:mi:ss’))*24*60,1) minutes,
trunc(rows_processed/((sysdate-to_date(first_load_time,’yyyy-mm-dd hh24:mi:ss’))*24*60)) rows_per_minute
from sys.v_$sqlarea
where sql_text like ‘insert %into “%’ and command_type = 2 and open_versions > 0;
Database SGA Report:

Monitor SGA Information:
SELECT SUM(VALUE)/1024/1024 “Size in MB” from SYS.v_$sga;
select NAME, BYTES from v$sgastat order by NAME;

Monitor Shared Pool Information:
select to_number(value) shared_pool_size, sum_obj_size, sum_sql_size, sum_user_size,
(sum_obj_size + sum_sql_size+sum_user_size)* 1.3 min_shared_pool
from (select sum(sharable_mem) sum_obj_size
from v$db_object_cache where type ‘CURSOR’),
(select sum(sharable_mem) sum_sql_size from v$sqlarea),
(select sum(250 * users_opening) sum_user_size from v$sqlarea), v$parameter
where name = ‘shared_pool_size’;

Monitor PGA Information:
Select st.sid “SID”, sn.name “TYPE”, ceil(st.value / 1024 / 1024/1024) “GB”
from v$sesstat st, v$statname sn where st.statistic# = sn.statistic#
and sid in (select sid from v$session where username like UPPER(‘hrms’))
and upper(sn.name) like ‘%PGA%’ order by st.sid, st.value desc;

Monitor CPU Usage Information:
select ss.username, se.SID, VALUE/100 cpu_usage_seconds
from v$session ss, v$sesstat se, v$statname sn where se.STATISTIC# = sn.STATISTIC#
and NAME like ‘%CPU used by this session%’ and se.SID = ss.SID
and ss.status=’ACTIVE’ and ss.username is not null order by VALUE desc;

Disk I/O Report:
WITH totreadwrite AS (SELECT SUM (phyrds) phys_reads, SUM (phywrts) phys_wrts FROM v$filestat)
SELECT NAME, phyrds, phyrds * 100 / trw.phys_reads read_pct,
phywrts, phywrts * 100 / trw.phys_wrts write_pct
FROM totreadwrite trw, v$datafile df, v$filestat fs
WHERE df.file# = fs.file# ORDER BY phyrds DESC;

IO Usage for a Query:
select b.sql_text “Statement “, a.Disk_reads “Disk Reads”, a.executions “Executions”,
a.disk_reads/decode(a.executions,0,1,a.executions) “Ratio”,c.username
from v$sqlarea a, v$sqltext_with_newlines b,dba_users c
where a.parsing_user_id = c.user_id and a.address=b.address and a.disk_reads>100000
order by a.disk_reads desc,b.piece;

Display the System write batch size:
SELECT kviival write_batch_size
FROM x$kvii
WHERE kviidsc = ‘DB writer IO clump’ OR kviitag = ‘kcbswc’

Monitor Disk I/O Contention:
select NAME, PHYRDS “Physical Reads”,
round((PHYRDS / PD.PHYS_READS)*100,2) “Read %”, PHYWRTS “Physical Writes”,
round(PHYWRTS * 100 / PD.PHYS_WRTS,2) “Write %”, fs.PHYBLKRD+FS.PHYBLKWRT “Total Block I/O’s” from ( select sum(PHYRDS) PHYS_READS, sum(PHYWRTS) 

PHYS_WRTS
from v$filestat ) pd, v$datafile df, v$filestat fs
where df.FILE# = fs.FILE#
order by fs.PHYBLKRD+fs.PHYBLKWRT desc;
DB Locks/Blocks/Blocker Details:

Track Block session in oracle 9i/10g
‎select s1.username || ‘@’ || s1.machine || ‘ ( SID=’ || s1.sid || ‘ ) is blocking ‘ || s2.username || ‘@’ || s2.machine || ‘ ( SID=’ || s2.sid || ‘ ) ‘ AS blocking_status from gv$lock l1, gv

$session s1, gv$lock l2, gv$session s2 where s1.sid = l1.sid and s2.sid = l2.sid and l1.BLOCK = 1 and l2.request > 0 and l1.id1 = l2.id1 and l2.id2 = l2.id2;
select do.object_name, row_wait_obj#, row_wait_file#, row_wait_block#, row_wait_row#,
dbms_rowid.rowid_create(1, ROW_WAIT_OBJ#, ROW_WAIT_FILE#, ROW_WAIT_BLOCK#, ROW_WAIT_ROW#)
from gv$session s, dba_objects do
where sid = 543 and s.ROW_WAIT_OBJ# = do.OBJECT_ID;
For detail description of blocking you can run this on your Oracle-Home
oracle-home\rdbms\admin\utllockt.sql
Select process,sid, blocking_session from v$session where blocking_session is not null; –in 10g

Track Locked Session & Blocked:
PROMPT Blocked and Blocker Sessions
select /*+ ORDERED */ blocker.sid blocker_sid, blocked.sid blocked_sid ,
TRUNC(blocked.ctime/60) min_blocked, blocked.request
from (select *from v$lock
where block != 0 and type = ‘TX’) blocker, v$lock blocked
where blocked.type=’TX’ and blocked.block = 0 and blocked.id1 = blocker.id1;

Track Database Lock:
Select /*+ ORDERED */ l.sid, l.lmode,
TRUNC(l.ctime/60) min_blocked, u.name||’.'||o.NAME blocked_obj
from (select * from v$lock
where type=’TM’ and sid in (select sid
from v$lock where block!=0)) l, sys.obj$ o, sys.user$ u
where o.obj# = l.ID1 and o.OWNER# = u.user#;

Track the Session Waiting for Lock:
SELECT holding_session bsession_id, waiting_session wsession_id, b.username busername, a.username wusername, c.lock_type TYPE, mode_held, mode_requested, lock_id1, lock_id2
FROM sys.v_$session b, sys.dba_waiters c, sys.v_$session a
WHERE c.holding_session = b.sid AND c.waiting_session = a.sid;

Track Blocker Details:
SELECT sid, serial#, username, osuser, machine
FROM v$session
WHERE sid IN (select sid from v$lock
where block != 0 and type = ‘TX’);

Users/Sessions/Processes Details:


Average Wait Time for Particular Event:
SELECT EVENT, TOTAL_WAITS, TOTAL_TIMEOUTS, TIME_WAITED, round(AVERAGE_WAIT,2) “Average Wait”
from v$system_event order by TOTAL_WAITS;
Sessions Waiting On A Particular Wait Event:
SELECT count(*), event
FROM v$session_wait
WHERE wait_time = 0 AND event NOT IN (‘smon timer’,'pipe get’,'wakeup time manager’, ‘pmon timer’,'rdbms ipc message’, ‘SQL*Net message from client’)
GROUP BY event ORDER BY 1 DESC;
Track Logon time of DB user and OS user:
Select to_char(logon_time,’dd/mm/yyyy hh24:mi:ss’),osuser,status,schemaname,machine from v$session where type !=’BACKGROUND’; ‎

Track all Session User Details:
select sid, serial#,machine, status, osuser,username from v$session where username!=’NULL’;
Track Active Session User Details:
SELECT SID, Serial#, UserName, Status, SchemaName, Logon_Time FROM V$Session WHERE Status= ‘ACTIVE’ AND UserName IS NOT NULL;

Track Active User Details:
SELECT s.inst_id, s.sid, s.serial#, p.spid, s.username, s.program FROM gv$session s JOIN gv$process p ON p.addr = s.paddr AND p.inst_id = s.inst_id WHERE s.type != 

‘BACKGROUND’;
Report OS Process ID for each session:
SELECT ses.username || ‘(‘ || ses.sid || ‘)’ users, acc.owner owner, acc.OBJECT OBJECT, ses.lockwait, prc.spid os_process
FROM v$process prc, v$access acc, v$session ses
WHERE prc.addr = ses.paddr AND ses.sid = acc.sid;
Show Username and SID/SPID with Program Name:
select sid,name,value from v$spparameter where isspecified=’TRUE’;‎
SELECT SID, Serial#, UserName, Status, SchemaName, Logon_Time FROM V$Session
WHERE Status= ‘ACTIVE’ AND UserName IS NOT NULL; –to find active session
SELECT s.inst_id, s.sid, s.serial#, p.spid, s.username, s.program –active users details
FROM gv$session s JOIN gv$process p ON p.addr = s.paddr AND p.inst_id = s.inst_id
WHERE s.type != ‘BACKGROUND’;

Track Current Transaction in Database:
‎‎select a.sid, a.username, b.xidusn, b.used_urec, b.used_ublk from v$session a, v$transaction b
where a.saddr = b.ses_addr;‎

Important Object Information:

 Database Object Information:
Select owner,object_type,count(*) from dba_objects Where owner not IN (‘SYS’,'MDSYS’,'CTXSYS’,'HR’,'ORDSYS’,'OE’,'ODM_MTR’,'WMSYS’,'XDB’,'QS_WS’, 

‘RMAN’,'SCOTT’,'QS_ADM’,'QS_CBADM’, ‘ORDSYS’,'OUTLN’,'PM’,'QS_OS’,'QS_ES’,'ODM’,'OLAPSYS’,'WKSYS’,'SH’,'SYSTEM’,'ORDPLUGINS’,'QS’,'QS_CS’)
Group by owner,object_type order by owner;
Query to Find 5 largest object in Database:
SELECT * FROM (select SEGMENT_NAME, SEGMENT_TYPE, BYTES/1024/1024/1024 GB, TABLESPACE_NAME from dba_segments order by 3 desc ) WHERE ROWNUM = 

sysdate – 7
Track Mviews Not Refreshed since last Week:
Select mview_name from user_mviews where LAST_REFRESH_DATE < sysdate – 7;