Oracle Application DBA Portal

R12.2. Start and Stop Procedure of Services

noreply@blogger.com (Srinivasan .R) — Wed, 20 Sep 2023 23:32:00 -0700

R12.2. Start and Stop Procedure of Services

R12.2. Services Start and Stop Procedure

All components - Application (Middle Tier)

START

$INST_TOP/admin/scripts

adstrtal.sh apps/****

Enter Weblogic Server Password: *****

STOP

$INST_TOP/admin/scripts

adstpal.sh apps/****

Enter Weblogic Server Password : *****

Individual Components: Application(Middle Tier)

$INST_TOP/admin/scripts

when we want to stop all the services using adstpal.sh we use adstpal.sh apps/apps<pwd> but in R12.2 it will ask Weblogic Server Password

We need to provide Weblogic admin password

Component	Command
Node Manager	$adnodemgrctl.sh start Enter Weblogic Admin Password:
Weblogic Admin Server	$adadminsrvctl.sh start Enter Weblogic Admin Password:
Application Listener	$adadlctl start
Oracle Process Manager	$adopmnctl.sh start
Apache Services	$adapchctl.sh start
Managed Server for OACORE Services	$admanagedsrvctl.sh start oacore_server1 Enter Weblogic Admin Password:
Managed Server for FormsServices	$admanagedsrvctl.sh start forms_server1 Enter Weblogic Admin Password:
Managed Server for Fusion MiddleWare Services	$admanagedsrvctl.sh start oafm_server1 Enter Weblogic Admin Password:
Managed Server for Forms web Services	$admanagedsrvctl.sh start forms-c4ws_server1 Enter Weblogic Admin Password:
Concurrent Manager Service	$adcmctl.sh start apps/apps
Fullfillment Serer Services	$jtffmctl.sh start

Stop

When we want to stop adstpal.sh apps/apps again it is going to ask weblogic password

Component	Command
Fullfillment Serer Services	$jtffmctl.sh stop
Concurrent Manager Service	$adcmctl.sh stop apps/apps
Managed Server for Forms web Services	$admanagedsrvctl.sh stop forms-c4ws_server1 Enter Weblogic Admin Password:
Managed Server for Fusion MiddleWare Services	$admanagedsrvctl.sh stop oafm_server1 Enter Weblogic Admin Password:
Managed Server for FormsServices	$admanagedsrvctl.sh stop forms_server1 Enter Weblogic Admin Password:
Managed Server for OACORE Services	$admanagedsrvctl.sh stop oacore_server1 Enter Weblogic Admin Password:
Apache Services	$adapchctl.sh stop
Oracle Process Manager	$adopmnctl.sh stop
Application Listener	$adadlctl stop
Weblogic Admin Server	$adadminsrvctl.sh stop Enter Weblogic Admin Password:
Node Manager	$adnodemgrctl.sh stop Enter Weblogic Admin Password:

Happy Learning
--Sreene

R12.2 Changing APPS or APPLSYS Password on R12.2 Instance.

noreply@blogger.com (Srinivasan .R) — Wed, 20 Sep 2023 23:28:00 -0700

R12.2 Changing APPS or APPLSYS Password on R12.2 Instance.

Note:- These steps must be carried out on the RUN environment file system.

1. Shut down the application tier services using the

$INST_TOP/admin/scripts/adstpall.sh

2. Change the APPLSYS password.

FNDCPASS apps/Currentapps 0 Y system/manager SYSTEM APPLSYS Newpassword

3. Start AdminServer using the $INST_TOP/admin/scripts/adadminsrvctl.sh

Note:-. Don't start any other application tier services.

4. Change the “apps” password in WLS Datasource as follows:

URL Link : - http://R122.appsdbaportal:7002/console

Click Lock & Edit in Change Center.

In the Domain Structure tree, expand Services, then select Data Sources

On the “Summary of JDBC Data Sources” page, select EBSDataSource

On the “Settings for EBSDataSource” page, select the Connection Pool tab.

Enter the new password in the “Password” field.

Enter the new password in the “Confirm Password” field.

Click Save.

Click Activate Changes in Change Center.

Check whether you are able to connect to DB using apps

sqlplus apps/ from application tier.

Before Starting all the services.

RUN Autoconfig.

cd $ADMIN_SCRIPTS_HOME
sh adautocfg.sh

Now start the services.

Start all the application tier services using the
$INST_TOP/admin/scripts/adstrtal.sh script.

./adstrtal.sh apps/newpassword

6. Verify the WLS Datastore changes as follows:

Log in to WLS Administration Console.
In the Domain Structure tree, expand Services, then select Data Sources.
On the “Summary of JDBC Data Sources” page, select EBSDataSource.
On the “Settings for EBSDataSource” page, select Monitoring > Testing.
Select “oacore_server1″.
Click Test DataSource
Look for the message “Test of EBSDataSource on server oacore_server1 was successful”.

Happy Learning
--Sreene

Oracle E-Busines Suite R12 : Find all the profile options recently changed

noreply@blogger.com (Srinivasan .R) — Wed, 16 Oct 2019 09:49:00 -0700

Oracle E-Business Suite R12 : Find all the profile options recently changed

For Oracle E-Business Suite R12 execute the following sql to get all the profile options recently changed.

select p.profile_option_name SHORT_NAME, n.user_profile_option_name "PROFILE NAME",
decode(v.level_id, 10001, 'Site', 10002, 'Application',
10003, 'Responsibility', 10004, 'User', 10005, 'Server',
10007, 'SERVRESP', 'UnDef') LEVEL_SET,
decode(to_char(v.level_id), '10001', '',
'10002', app.application_short_name, '10003', rsp.responsibility_key,
'10005', svr.node_name, '10006', org.name, '10004', usr.user_name,
'10007', 'Serv/resp', 'UnDef') "CONTEXT", v.profile_option_value VALUE, v.LAST_UPDATE_DATE  
from fnd_profile_options p,
fnd_profile_option_values v,
fnd_profile_options_tl n,
fnd_user usr,
fnd_application app,
fnd_responsibility rsp,
fnd_nodes svr,
hr_operating_units org
where p.profile_option_id = v.profile_option_id (+)
and p.profile_option_name = n.profile_option_name
--and upper(n.user_profile_option_name) like upper('BNE%')
--and trunc(v.LAST_UPDATE_DATE) > trunc(sysdate-170)
and usr.user_id (+) = v.level_value
and rsp.application_id (+) = v.level_value_application_id
and rsp.responsibility_id (+) = v.level_value
and app.application_id (+) = v.level_value
and svr.node_id (+) = v.level_value
and org.organization_id (+) = v.level_value
and v.LAST_UPDATE_DATE is not null 
order by last_update_date desc, short_name, level_set;

For Oracle E-Busines Suite 11i execute the following sql to get all the profile options recently changed.

SELECT pot.user_profile_option_name "Profile"
 , DECODE( a.profile_option_value
          , '1', '1 (may be "Yes")'
          , '2', '2 (may be "No")'
          , a.profile_option_value) "Value"
 , DECODE( a.level_id
          , 10001, 'Site'
          , 10002, 'Appl'
          , 10003, 'Resp'
          , 10004, 'User'
          , '????') "Level"
 , DECODE( a.level_id
          , 10002, e.application_name
          , 10003, c.responsibility_name
          , 10004, d.user_name
          , '-') "Location", a.LAST_UPDATE_DATE  
FROM applsys.fnd_application_tl e
 , applsys.fnd_user d   , applsys.fnd_responsibility_tl c
 , applsys.fnd_profile_option_values a   , applsys.fnd_profile_options b
 , applsys.fnd_profile_options_tl pot
WHERE pot.profile_option_name = b.profile_option_name
AND UPPER(pot.user_profile_option_name) LIKE UPPER('BNE%')
 AND b.application_id = a.application_id (+)
 AND b.profile_option_id = a.profile_option_id (+)
 AND a.level_value = c.responsibility_id (+)
 AND a.level_value = d.user_id (+)   AND a.level_value = e.application_id(+)
 AND e.LAST_UPDATE_DATE is not null
 --AND( UPPER( e.application_name) LIKE UPPER( 'username%')
 --OR UPPER( c.responsibility_name) LIKE UPPER( 'username%')
 --OR UPPER( d.user_name) LIKE UPPER( '%username%'))
 ORDER BY a.LAST_UPDATE_DATE desc, "Profile", "Level", "Location", "Value";

Happy Learning
--Sreene

Oracle EBS R12.2.5 New Features

noreply@blogger.com (Srinivasan .R) — Tue, 22 Aug 2017 05:18:00 -0700

Oracle EBS R12.2.5 New Features

Oracle E-Business Suite R12.2.5 has been around for a while now. If you are still using an older version, it is time to upgrade. Having the most up to date version of EBS guarantees that you are utilizing the latest and greatest features that Oracle has to offer.

E-Business Suite R12.2.5, Patch 19676458 with 9 Zip files with 4.3GB in total size.

Preliminary Pre-Requirements for R12.2.5 Upgrade

Oracle E-Business Suite 12.2.5 Release Update Pack requires Fusion Middleware Technology Stack (FMW) 11.1.1.7 (11gR1 PS6) or higher.
Apply the Latest AD and TXK Delta Release Update Packs
Minimum RDBMS Version is 11.2.0.4 or Higher

New and Changed Features of Oracle Application DBA and Technology Stack (TXK)

Enhanced adop user interface

Category	Description	Parmameters
Changed UI	The UI of the adop utility has been significantly enhanced, to display more selective information on the console. Messages, prompts and other elements have also been extensively refined to increase the ease of use of the various patching commands.	Dependent on operation

New adop monitoring and validation features

Category	Description	Parameters
New Features	Progress of an online patching cycle can be followed by running the new Online Patching Monitoring utility (adopmon). This utility can be used to follow the overall progress of a patching cycle, as well as identifying the various individual adop actions being taken.	$ adopmon
New Features	Before you start a new patching cycle by running the prepare phase, you can optionally check your system's readiness by running adop with the 'validate' option. If you do this while a patching cycle is in progress, validation will take place for the cutover phase.	$ adop -validate

Support for new EBS Installation Central Inventory

Category	Description	Parameters
New Feature	Support for an instance-specific EBS Installation Central Inventory has been introduced as an option for the application tier on UNIX platforms. The inventory is identified by /oraInventory/oraInst.loc. This feature is useful where multiple Oracle E-Business Suite installations exist on the same host, helping to avoid issues when fs_clone is run simultaneously on different instances. To use the EBS Installation Central Inventory, all application tier Oracle Homes registered in the global inventory for the instance must be migrated to the new inventory.	Not applicable

Oracle WebLogic Server performance improvements

Category	Desciption	Parameters
New Options	A new -DserverType=wlx start argument for managed servers reduces their memory footprint, by preventing startup of the Enterprise JavaBeans (EJB), Java EE Connector Architecture (JCA), and Java Message Service (JMS) services.	-DserverType=wlx
New Options	To reduce oacore startup time, the Portlet Producer libraries are no longer deployed to the EBS domain. A new context variable,s_deploy_portlet, has been introduced to cater for cases where portlet-related configuration is required, such as in instances needing Webcenter integration.	s_deploy_portlet
New Mode	The default value of s_forms-c4wsstatus is now set to 'Disabled'.Thus, the formsc4-ws servers are no longer started during a 'start all' operation.	s_forms-c4wsstatus

New 'dualfs' option in standard cloning

Category	Description	Parameters
New Option	A new 'dualfs' option is available when performing a standard clone, as well as while adding a new node. With the 'dualfs' option, both the run and patch file systems are cloned and configured in a single operation.	dualfs

The following products are obsolete in Release 12.2.5:
Oracle Demand Planning
Oracle Manufacturing Scheduling

12.2 Online Patching Known Issues

To avoid the possibility of data inconsistencies, certain Oracle E-Business Suite product functions are currently not available while an online patching cycle is in progress.
The following table provides a full list of affected applications and functions:

Application	Affected Functions
Payroll	Users will not be able to define Fast Formulas or use the Fast Formula Assistant. Users will not be able to create, update, or delete US Cities. Users will, however, be able to use the City form for all operations, unless the patch being applied updates City-related data: the form will be unavailable only when the patch delivers or modifies City-related data. Pension Calculation Setup cannot be used. US localization earnings and deduction setup cannot be used. Quick Paint Reports cannot be used.
Incentive Compensation	Transaction collection process for new mappings will not be available and any changed mapping will continue to use previous mapping rules. Users will not be able to run the "Synchronize Classification Rulesets" program. Users will not be able to use the "Formula Generation" feature. Users will not be able to specify new formulas or changes to compensation rules.

If you try to run such an unavailable function, you will receive an error as follows:

Reference:Note 1983050.1, 12.2.5 Readme
Note 2050998.1, Application DBA and TXK Release note

Happy Learning
--Sreene

EBS R12.2.4 Apps cloning Error (RC-50208)

noreply@blogger.com (Srinivasan .R) — Fri, 19 Jun 2015 11:52:00 -0700

EBS R12.2.4 Apps cloning Error (RC-50208)

The issue, main cause and resolution is shared below :
Currently :-
E-Business Suite Environment Information
----------------------------------------
RUN File System : /opt/oracle/apps/VIS124/fs2/EBSapps/appl
PATCH File System : /opt/oracle/apps/VIS124/fs1/EBSapps/appl
Non-Editioned File System : /opt/oracle/apps/VIS124/fs_ne
DB Host: appsdbaportal.blogspot.in Service/SID: VIS124

Target Server Clone error :-
Issue : During the EBS R12.2.4 apps tier cloning, we faced issues regarding the domain deployment for the run edition. Exact error as per clone log :
RC-50208: Exception in method TimedProcessProcess.run
Raised by oracle.apps.ad.util.TimedProcess$TimedProcessProcess: java.io.IOException: Cannot run program "/opt/apps/VIR122/fs2/FMW_Home/webtier/perl/bin/perl": error=2, No such file or directory

Cause : The patch-backup.xml file inside the “/patch_wls1036/registry” directory pointed towards the wrong RUN_BASE in source system.

Resolution : Change the below parameter to point to source RUN_BASE

Source Server Clone error:-
/opt/oracle/apps/VIS124/fs1/FMW_Home/modules/javax.mail_1.1.0.0_1-4-1.jar
To
/opt/oracle/apps/VIS124/fs2/FMW_Home/modules/javax.mail_1.1.0.0_1-4-1.jar

Where RUN_BASE=/opt/oracle/apps/VIS124/fs2 and PATCH_BASE= /opt/oracle/apps/VIS124/fs1

After correcting the parameter rerun the adpreclone and start the apps tier clone from Source server to Target server

Happy Learning
--Sreene

Upgrade Oracle Database 10g to 11g R1 (11.1.X)

noreply@blogger.com (Srinivasan .R) — Thu, 28 May 2015 03:04:00 -0700

Upgrade Oracle Database 10g to 11g R1 (11.1.X)

Note:-
1. Direct Upgrade is possible from 10.2.0.1 and higher
2. There are two methods to upgrade database
a) Database Upgrade Assistant (DBUA) and
b) Manual Upgrade
We are going to use Manual Upgrade
3. Install 11g database software in different ORACLE_HOME from source Database

Upgrade Steps
1. Prepare to Upgrade
1.1 Install 11g in different (new) ORACLE_HOME with following consideration
a) When prompted for Upgrade an Existing Database (Select NO)

b) On Select Configuration Option select Install Software Only

Step:1 Pre-upgrade

1. Analyze database using pre-upgrade information tool ( utlu111i.sql sql script available at 11g_oracle_homne/rdbms/admin/utlu111i.sql run it from source database i.e. 10g)

sqlplus “/as sysdba”
SQL>spool upgrade_info.log
SQL>@/11g_oracle_home/rdbms/admin/utlu111i.sql

2. Check the output of the Pre-Upgrade Information Tool in upgrade_info.log and fix any issues
Obsolete/Deprecated Parameters: [Update Oracle Database 11.1 init.ora or spfile]
**************************************************
–> “background_dump_dest” replaced by “diagnostic_dest”
–> “user_dump_dest” replaced by “diagnostic_dest”
–> “core_dump_dest” replaced by “diagnostic_dest”

To fix this obsolete parameter, comment out from initialization parameter file and replace with new parameter like comment above three deprecated parameters and add *.diagnostic_dest

WARNING: –> Database is using an old timezone file version.
…. Patch the 10.2.0.1.0 database to timezone file version 4
…. BEFORE upgrading the database. Re-run utlu111i.sql after
…. patching the database to record the new timezone file version.

To find time zone file version on source database (10g) run

SQL> select * from v$timezone_file;
If time zone file version is less than 4 then apply time zone patch 5632264 manually for 10.2.0.1 database (more on How to manually upgrade time zone file version on database coming soon … )

3. Check invalid objects
SQL> select object_name, owner, object_type from all_objects where status like ‘INVALID';
4. Check version & status of all database components
SQL>select comp_name,version, status from dba_registry;

5. Take backup of source database i.e. 10g database

6. If you are using spfile, create pfile
SQL> create pfile from spfile ;

This will create pfile in 10g $ORACLE_HOME/dbs/init[SID].ora
7. Copy initialization file (pfile) from source (10g) to target (11g)

8. Adjust initialization parameter specific to 11g like
a) Remove *.background_dump_dest, *.core_dump_dest, *.user_dump_dest and add
*.diagnostic_dest=’/11g_base’ (11g Base Directory)
b) Change
*.compatible=’10.2.0.1.0′
to
*.compatible=’11.1.0′
.
.
Step:2. Upgrade Database
1. Shut down source database (10g) – Your downtime starts here
sqlplus “/as sysdba”

SQL>shutdown immediate
2. Set your environment variables to Oracle Database 11g Release 1 (11.1) :
export ORACLE_HOME=/u01/oracle/11gbase/11.1.0
export ORACLE_SID=TEST
export PATH=$ORACLE_HOME/bin:$PATH

3. Start Upgrade
sqlplus “/as sysdba”
SQL> startup upgrade
Check shared_pool & java_pool size, to set new values
SQL>alter system set java_pool_size=512M;
SQL>alter system set shared_pool_size=800M;
SQL> spool upgrade.log
SQL> @?/rdbms/admin/catupgrd.sql (Upgrade process shut down database after catupgrd.sql)
Check alert log in 11g at
/11gBase_Install/diag/rdbms/[sid]/[SID]/trace/alert_[SID].log

4. Run Post-Upgrade Status Tool provides a summary of the upgrade
SQL> startup

SQL>@?/rdbms/admin/utlu111s.sql
If you hit error like”ORA-00001: unique constraint (SYS.I_DIANA_VERSION) violated” check metalink note # 744693.1

5. Perform upgrade actions that do not require the database to be in UPGRADE mode
SQL>@?/rdbms/admin/catuppst.sql

6. Compile Invalid Objects
SQL>@?/rdbms/admin/utlrp.sql
Check invalid objects
SQL> select count(*) from dba_objects where status like ‘INVALID';
.
.
Step:-3 Post Upgrade steps
1. Check status of database components
SQL>select comp_name,version, status from dba_registry;

2. Copy tnsnames.ora, listener.ora, sqlnet.ora and include file from source (10g) oracle_home to target (11g) oracle_home

3. Back up upgraded (11g) database.

Happy Learning
--Sreene

Oracle EBS R12.2 Architecture

noreply@blogger.com (Srinivasan .R) — Mon, 18 May 2015 12:15:00 -0700

Happy Learning
--Sreene

Cloning the database from 11.2.0.2 to 11.2.0.3 on a new server

noreply@blogger.com (Srinivasan .R) — Wed, 29 Apr 2015 12:09:00 -0700

Cloning the database from 11.2.0.2 to 11.2.0.3 on a new server

RMAN Backup source server in 11.2.0.2.0
++++++++++++++++++++++++++++++++
Recovery Manager: Release 11.2.0.2.0 - Production on Fri AUG 14 15:15:59 2014
RMAN> backup database plus archivelog delete input;
Starting backup at 14-AUG-14
current log archived
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=52 device type=DISK
channel ORA_DISK_1: starting archived log backup set
channel ORA_DISK_1: specifying archived log(s) in backup set
input archived log thread=1 sequence=17 RECID=1 STAMP=788540852
input archived log thread=1 sequence=18 RECID=2 STAMP=788541371
channel ORA_DISK_1: starting piece 1 at 14-AUG-14
channel ORA_DISK_1: finished piece 1 at 14-AUG-14
piece handle=/u01/app/oracle/flash_recovery_area01/DBUPG/backupset/o1_mf_annnn_TAG20140713T151612_800shf7w_.bkp tag=TAG20140713T151612 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
channel ORA_DISK_1: deleting archived log(s)
archived log file name=/u01/app/oracle/flash_recovery_area01/DBUPG/archivelog/o1_mf_1_17_800rz40y_.arc RECID=1 STAMP=788540852
archived log file name=/u01/app/oracle/flash_recovery_area01/DBUPG/archivelog/o1_mf_1_18_800shcsd_.arc RECID=2 STAMP=788541371
Finished backup at 14-AUG-14
Starting backup at 14-AUG-14
using channel ORA_DISK_1
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00001 name=+DS8000_DG/dbupg/datafile/system.271.788537119
input datafile file number=00002 name=+DS8000_DG/dbupg/datafile/sysaux.272.788537167
input datafile file number=00003 name=+DS8000_DG/dbupg/datafile/undotbs1.273.788537199
input datafile file number=00004 name=+DS8000_DG/dbupg/datafile/users.275.788537229
channel ORA_DISK_1: starting piece 1 at 14-AUG-14
channel ORA_DISK_1: finished piece 1 at 14-AUG-14
piece handle=/u01/app/oracle/flash_recovery_area01/DBUPG/backupset/o1_mf_nnndf_TAG20140713T151614_800shgw5_.bkp tag=TAG20140713T151614 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:35
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
including current control file in backup set
including current SPFILE in backup set
channel ORA_DISK_1: starting piece 1 at 14-AUG-14
channel ORA_DISK_1: finished piece 1 at 14-AUG-14
piece handle=/u01/app/oracle/flash_recovery_area01/DBUPG/backupset/o1_mf_ncsnf_TAG20140713T151614_800sjm29_.bkp tag=TAG20140713T151614 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 14-AUG-14
Starting backup at 14-AUG-14
current log archived
using channel ORA_DISK_1
channel ORA_DISK_1: starting archived log backup set
channel ORA_DISK_1: specifying archived log(s) in backup set
input archived log thread=1 sequence=19 RECID=3 STAMP=788541412
channel ORA_DISK_1: starting piece 1 at 14-AUG-14
channel ORA_DISK_1: finished piece 1 at 14-AUG-14
piece handle=/u01/app/oracle/flash_recovery_area01/DBUPG/backupset/o1_mf_annnn_TAG20140713T151652_800sjnf7_.bkp tag=TAG20140713T151652 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
channel ORA_DISK_1: deleting archived log(s)
archived log file name=/u01/app/oracle/flash_recovery_area01/DBUPG/archivelog/o1_mf_1_19_800sjn5q_.arc RECID=3 STAMP=788541412
Finished backup at 14-AUG-14
RMAN> backup current controlfile;
Starting backup at 14-AUG-14
using channel ORA_DISK_1
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
including current control file in backup set
channel ORA_DISK_1: starting piece 1 at 14-AUG-14
channel ORA_DISK_1: finished piece 1 at 14-AUG-14
piece handle=/u01/app/oracle/flash_recovery_area01/DBUPG/backupset/o1_mf_ncnnf_TAG20140713T153435_800tkwl2_.bkp tag=TAG20140713T153435 comment=NONE
channel ORA_DISK_1: backup set
complete, elapsed time: 00:00:01
Finished backup at 14-AUG-14
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
Create a spfile :
+++++++++++
*.control_files='+DS8000_DG/dbclone/controlfile/Current.277.788541913'
*.db_name='dbupg'
*.db_unique_name='dbclone'
*.audit_file_dest='/u01/app/oracle/admin/dbclone/adump'
*.audit_trail='db'
*.compatible='11.2.0.0.0'
*.db_block_size=8192
*.db_create_file_dest='+MMC'
*.db_domain=''
*.db_recovery_file_dest_size=107374182400
*.db_recovery_file_dest='/u01/app/oracle/flash_recovery_area01'
*.diagnostic_dest='/u01/app/oracle'
*.log_file_name_convert='+DS8000_DG','+MMC'
*.memory_target=1031798784
*.open_cursors=300
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
Make backup available on new server:
++++++++++++++++++++++++++++
SQL*Plus: Release 11.2.0.3.0 Production on Fri Jul 13 15:33:24 2012
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup nomount
ORACLE instance started.
Total System Global Area 1027182592 bytes
Fixed Size                  2227936 bytes
Variable Size             599785760 bytes
Database Buffers          419430400 bytes
Redo Buffers                5738496 bytes
SQL> show parameter db_n
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_name                              string      dbupg
SQL> show parameter db_un
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_unique_name                       string      dbclone
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
Restore the Controlfile :
++++++++++++++++
RMAN> restore controlfile from '/u01/app/oracle/flash_recovery_area01/DBUPG/backupset/o1_mf_ncnnf_TAG20140713T153435_800tkwl2_.bkp';
Starting restore at 13-JUL-12
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=290 device type=DISK
channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:02
output file name=+DATA/dbclone/controlfile/current.277.788541913
Finished restore at 13-JUL-12
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
Start the service in mount :
++++++++++++++++++++
RMAN> startup mount
database is already started
database mounted
released channel: ORA_DISK_1
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
Restore the datafile :
+++++++++++++++
run {
SET NEWNAME FOR DATABASE TO '+DATA';
restore database ;
}

OR
+++

run
{
set newname for datafile 00001 to '+DATA/BDMDEV3/datafile/system.257.795697657';
set newname for datafile 00002 to '+DATA/BDMDEV3/datafile/undotbs1.256.795697551';
set newname for datafile 00003 to '+DATA/BDMDEV3/datafile/sysaux.264.795698741';
set newname for datafile 00004 to '+DATA/BDMDEV3/datafile/users.258.795698093';
set newname for datafile 00005 to '+DATA/BDMDEV3/datafile/ts_admin.261.795698675';
set newname for datafile 00006 to '+DATA/BDMDEV3/datafile/idx_ts_admin.262.795698701';
set newname for datafile 00007 to '+DATA/BDMDEV3/datafile/ts_subj.265.795698757';
set newname for datafile 00008 to '+DATA/BDMDEV3/datafile/idx_ts_subj.266.795698759';
set newname for datafile 00009 to '+DATA/BDMDEV3/datafile/ts_prot.267.795698763';
set newname for datafile 00010 to '+DATA/BDMDEV3/datafile/idx_ts_prot.268.795698765';
set newname for datafile 00011 to '+DATA/BDMDEV3/datafile/ts_gen.260.795698581';
set newname for datafile 00012 to '+DATA/BDMDEV3/datafile/idx_ts_gen.259.795698385';
set newname for datafile 00013 to '+DATA/BDMDEV3/datafile/ts_obs.269.795698769';
set newname for datafile 00014 to '+DATA/BDMDEV3/datafile/idx_ts_obs.270.795698773';
set newname for datafile 00015 to '+DATA/BDMDEV3/datafile/ts_flow.271.795698775';
set newname for datafile 00016 to '+DATA/BDMDEV3/datafile/idx_ts_flow.272.795698777';
set newname for datafile 00017 to '+DATA/BDMDEV3/datafile/ts_usracc.273.795698777';
set newname for datafile 00018 to '+DATA/BDMDEV3/datafile/idx_ts_usracc.274.795698779';
set newname for datafile 00019 to '+DATA/BDMDEV3/datafile/ts_ds_clob.275.795698779';
set newname for datafile 00020 to '+DATA/BDMDEV3/datafile/idx_ts_ds_clob.279.795698785';
set newname for datafile 00021 to '+DATA/BDMDEV3/datafile/ts_col_clob.276.795698781';
set newname for datafile 00022 to '+DATA/BDMDEV3/datafile/idx_ts_col_clob.280.795698785';
set newname for datafile 00023 to '+DATA/BDMDEV3/datafile/ts_lab.277.795698781';
set newname for datafile 00024 to '+DATA/BDMDEV3/datafile/users.263.795698727';
set newname for datafile 00025 to '+DATA/BDMDEV3/datafile/idx_ts_lab.278.795698783';
restore database;
switch datafile all;
}
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
Recover the datafile :
+++++++++++++++
RMAN> recover database;
Starting recover at 14-AUG-14
using channel ORA_DISK_1
starting media recovery
media recovery complete, elapsed time: 00:00:01
Finished recover at 14-AUG-14
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
Startup with upgrade option :
++++++++++++++++++++++
SQL*Plus: Release 11.2.0.3.0 Production on Fri Aug 14 15:39:31 2014
SQL> alter database open resetlogs upgrade;
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
Now you can upgrade your database:
++++++++++++++++++++++++++++
$ cd $ORACLE_HOME/rdbms/admin
SQL> set echo on
SQL> SPOOL upgrade.log
SQL> @catupgrd.sql
SQL> spool off

Once This is completed, the database will automatically Shut down.

$ cd $ORACLE_HOME/rdbms/admin
$ sqlplus "/as sysdba"
SQL>startup
SQL> @utlu112s.sql
SQL> @utlrp.sql

+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
After upgrade database you can change the database name using NID

nid TARGET=SYS/xxxxxx DBNAME=BDMDEV3 SETNAME=YES

Happy Learning
--Sreene

Oracle12c Datafile moved Online

noreply@blogger.com (Srinivasan .R) — Sun, 26 Apr 2015 11:54:00 -0700

Oracle 12c Datafile moved Online
--------------------------------------------
In 12.1.0, a data file can now be moved online while it is open and being accessed, all these are possible now:

Renaming an Online Datafile
Relocating an Online Datafile
Copying an Online Datafile
Relocating an Online Datafile and Overwriting an Existing File
Relocating an Online Datafile to Oracle ASM
Moving a File from One ASM Location to Another ASM Location

Command :-
>>>>alter database move datafile ‘old_location’ to ‘new_location';

>>>>select file_name from dba_data_files;

Happy Learning
--Sreene

Oracle12c PSU Apply

noreply@blogger.com (Srinivasan .R) — Sun, 26 Apr 2015 11:26:00 -0700

What is PSU?
An Oracle PSU contains recommended bug fixes and "proactive" cumulative patches, a nice change that makes it simple for the DBA to chose to apply "priority" patches.

In Oracle 12c you don't need to use carbundle PSU apply ,This is all done using datapatch command.

cd $ORACLE_HOME/opatch
./datapatch

To check the PSU applied to your database using the following SQL statement.
set lines 256
set trimout on
set tab off
column ACTION_TIME format a10
column DESCRIPTION format a20
select PATCH_ID,PATCH_UID,VERSION,FLAGS,ACTION,STATUS,ACTION_TIME,DESCRIPTION from DBA_REGISTRY_SQLPATCH;

Oracle Support:
Datapatch Known Issues (Doc ID 1609718.1)
Datapatch: Database 12c Post Patch SQL Automation (Doc ID 1585822.1).

Happy Learning
--Sreene

Oracle AS 10g null protocol issue

noreply@blogger.com (Srinivasan .R) — Mon, 27 Feb 2012 23:49:00 -0800

Strange error in Oracle Application Server 10g 10.1.3.3
I have tried to start it using opmnctl startall and it finish with following error:

12/02/26 17:52:09 Oracle Containers for J2EE 10g (10.1.3.3.0) initialized
12/02/26 17:52:09 WARNING: OC4J will not send ONS ProcReadyPort messages to opmn for service: OC4JServiceInfo id: null protocol: jms hostname: null port: 126
08 description: null
12/02/26 17:52:09 null hostname was null

I got the solution....
Problem was with jms.sate file located in

$ORACLE_HOME/j2ee/home/persistence
$ORACLE_HOME/j2ee/[other OC4J]/persistence
it got corrupted may be during some faulty oc4j stop process. I deleted jms.sate file and restared the OAS Services ... it worked.

Happy Learning
--Sreene

(Re)Securing Oracle Application Server Control

noreply@blogger.com (Srinivasan .R) — Tue, 6 Sep 2011 00:02:00 -0700

If you’re running an Oracle Application Server 10g instance you are probably familiar with Oracle Enterprise Manager Application Server Control. If not, go back to the manual. This is not a how-to on setting it up or using it. If you want to know how to secure it and refresh the certificate when it expires, read on.

Application Server Control is installed with Application Server 10g and typically runs on a port like 1810. By default it uses the non-secure http protocol. Since your whole application server is controlled through this interface, you probably want to secure it. The instructions below will generate a self signed certificate and get your Application Server Control up and running with https.

As usual this post is written for Oracle Application Server 10g on UNIX. Always review the documentation for your release before trying any of these steps.

Securing Application Server Control

Oracle has provided a simple way to secure Application Server Control.

Note: If $ORACLE_HOME/bin is not in your path you will need to provide this path to emctl.

1. Connect to the command line on the application server and set all the appropriate environment variables for your application instance.

2. Run the command emctl stop iasconsole to stop Application Server Control.

3. Run the command emctl secure em to secure Application Server control. This will perform a few steps including generating a self-signed secure certificate.

4. Run emctl start iasconsole to start Application Server Control.

If all goes well you will now be able to connect to your Application Server Control instance on the same port as before but now with the https protocol. In most browsers you will need to specify ‘https://’ in the URL.

Depending on your browser settings you may get a warning when accessing the site that the secure certificate was not issued by a trusted company. That is normal with a self-signed certificate. You can either tell your browser to trust the certificate or simply disregard the warning when it appears.

Renewing the Certificate

By default the certificate created in the steps above will only be good for six months. Once the cert goes stale you will probably get a warning that the certificate date is invalid. You may additionally get some java errors like below.

When this happens you can simply re-secure Application Server Control with the same steps above. This will create a new certificate which will be valid for another six months.

Some Potential Problems

If the certificate has expired you will likely get a java error like this:

IOException in sending Request :: javax.net.ssl.SSLException: SSL handshake failed: X509CertExpiredErr

If this happens simply re-secure Application Server Control with the instructions above.

Sometimes Application Server Control will not shut down properly and you may get an error like this:

IOException in sending Request :: javax.net.ssl.SSLException: SSL handshake failed: SSLIOClosedOverrideGoodbyeKiss

If this happens you will probably have to kill the enterprise manager process (look for a process called emagent) and re-secure again.

oracle, oracle application server, oracle security

Happy Learning
--Sreene

How to compile Apps Schema (invalid objects) in R12 ?

noreply@blogger.com (Srinivasan .R) — Tue, 8 Jun 2010 01:21:00 -0700

How to compile Apps Schema (invalid objects) in R12 ?

Quick tip on how to compile apps schema (or invalid objects in database) in Oracle applications R12

You can compile invalid objects (or Apps Schema) using below three methods (compiling invalid is not limited to these three methods)

1. From Database Tier
a) Login as database tier user (ovisr12 in my case)
b) Set environment variable (under $INSTALL_DIR/db/tech_st/[10.2.0 or 11.1.0]/[sid]_[hostname].env)
c) cd $ORACLE_HOME/rdbms/admin
d) sqlplus “/as sysdba”
e) SQL> @utlrp.sql

2. From application tier (using adadmin)
a) Login as application tier user (avisr12 in my case)
b) Set environment variable (under $INSTALL_DIR/apps/apps_st/appl/APPS[sid]_[hostname].env)
c) admin
d) option 3 “compile/reload Applications Database Entities menu
e) option 1 “Compile Apps Schema”

3. From SQL (Individual objects)
a) Identity Invalid Object “SQL> select object_name, owner, object_type from all_objects where status like ‘INVALID’ ”
b) SQL> alter [object] [object_name] compile;

Related

How to compile Invalid Objects [Metalink Note 143734.1]

Workflow Notification Mailer Setup

noreply@blogger.com (Srinivasan .R) — Fri, 29 Jan 2010 01:26:00 -0800

Workflow Notification Mailer Setup
NOTIFICATION MAILER ARCHITECTURE

The main component of the Oracle Workflow Notification Mailer is the executable
WFMAIL. This is a server side program that queries the database for any pending
notifications. It then dispatches these notifications by calling sendmail for UNIX and the MAPI APIs for Microsoft Windows NT. The notification mailer also queries the local inbox for incoming messages. These messages are validated and then passed to the database for response processing. To configure Workflow Notification Mailer we have to do OS level setup (Sendmail) and Application level setup.

1. OS Level Setup
2. Application level Configuration

1. OS (Oracle Enterprise Linux 4 update 7) level Setup
At Os level we need to do bellow setups before configuring Application level
Sendmail should be installed by default when you install RedHat Linux. If it is not then you need to install the Sendmail RPM’s with the Red Hat distribution
Sendmail RPM’s (In Oracle Enterprise Linux 4 update 7)

Note: By default Oracle Enterprise Linux 4 update 7 have above all RPM’s

A. Configuring Sendmail

Edit the /etc/mail/sendmal.mc file
Look for this bellow line in this file
DAEMON_OPTIONS (`Port=smtp,Addr=127.0.0.1, Name=MTA')dn

Add 1 line with SMTP port Number and Server IP to this above line

DAEMON_OPTIONS (`Port=25,Addr=192.168.1.61, Name=MTA')dnl

Here 25 is SMTP Port Number, 192.168.1.61 is Server IP

After adding his like file look like this

DAEMON_OPTIONS(`Port=smtp,Addr=127.0.0.1, Name=MTA')dnl
DAEMON_OPTIONS(`Port=25,Addr=192.168.1.61, Name=MTA')dnl

Save the file

Rebuild /etc/sendmail.cf from the revised /etc/mail/sendmail.mc
$m4 /etc/mail/sendmail.mc > /etc/mail/sendmail.cf

Restart the sendmail services
$ /etc/init.d/sendmail restart
B.Starting Sendmail
You can use the chkconfig command to get sendmail configured to start at boot time
$chkconfig sendmail on
The services should now be set at the appropriate run levels and then checked to ensure they are correct.

#chkconfig --level 2345 sendmail on

To check run level
#chkconfig --list sendmail
C. Check the sendmail services
$telnet
$telnet 192.168.1.61 25

Now sendmail is working

D. Dovecot IMAP Server
Now that the sendmail server has been setup to allow the sending of emails, we need to configure a means for the user to retrieve any emails that are waiting for them on the server. One of the packages that do this is dovecot, which handles POP and IMAP mailboxes in clear text or with link encryption (POPS and IMAPS); IMAPS is the preferred mail protocol for MUAs.
Install Dovecot RPM if not installed

E: Start the Dovecot services
Start the services from Command

$ /etc/init.d/dovecot restart
Or
Start Dovecot services from Server

Start the dovecot services then save

F.Check the Dovecot services
$telnet 192.168.1.61 143

G. Check the mails are going to your mail ID from Linux command line
$ mail –s “Hi this is from WF Server “ yourID@gmail.com
If u gets this mail to your mail Id It then send mail is working fine
H. Create DISCARD & PROCESS folders
Login to the Application User (testappl)
Under testappl user home it will be one mail folder. Under mail folder you create DISCARD & PROCESS folders

2. Application level Configuration

1. We can configure workflow notification mailer from Oracle Application Manager (OAM)

2. There are two kinds of Notification Outbound & Inbound in Workflow Mailer

3. For Outbound Notification, CM (Concurrent Manager) node should be able to connect to SMTP (Simple Mail Transfer Protocol) server/relay.

4. For Inbound Notification (Optional), CM node should be able to connect to IMAP (Internet Message Access Protocol) Server.

5. Workflow Notification Mailer in background run as Concurrent Manager (Workflow Mailer Service, Workflow Agent Listener Service)

Enable Workflow Agent Listener and Notification Mailer concurrent

programs

Schedule the Workflow Background process, Notification Mailer concurrent requests

Workflow Mailer Service, Workflow Agent Listener Service should be started

Configure Workflow Notification Mailer
Login to Oracle Application Manager
Go to Site Map

1. Click on Next

2. Click on Next

3. Here give Application User name, password and Test Mail Id
Give PROCESS and DISCARD folders (which we have created in application server) name in EMail Processing

4. Click on Next

5. Give Submit Date and time then click on Next

6. Click on Next

7. Here give user name and click on Send Test Message

Click on Next

8. Click on finish

B. STARTUP NOTIFICATION MAILER

The mailer program can be started from command line or through a concurrent
Program called "Notification Mailer". When the notification mailer is started as a concurrent request in Oracle Applications, the OS process is run by the Applications Manager (APPLMGR) user.

To start it from the application, submit the "Notification Mailer" Concurrent request.

To start it from command line on UNIX, execute the following command:

Go to FND_TOP/bin/

$WFMAIL apps/Pass 0 Y $FND_TOP/resource/wfmail.cfg

Log file for Workflow Mailer Notification are at $APPLCSF/$APPLLOG/

Happy Learning
G.Venugopal Achari

Data Dictionary Info

noreply@blogger.com (Srinivasan .R) — Mon, 3 Aug 2009 23:37:00 -0700

Data Dictionary Info
Here are some scripts related to Data Dictionary Info .

Dictionary Cache
DATA DICTIONARY CACHE NOTES:

Parameter - Name of the parameter that determines the number of entries in the data dictionary cache.
Gets - Total number of requests for information on the data object.
Getmisses - Number of data requests resulting in cache misses.
% Cache Misses - Miss Ratio
Count - Total number of entries in the cache
Usage - Number of cache entries that contain valid data.

This report shows the DC cache statistics (a part of the shared pool). Whenever the database parses a SQL statement, it scans the text for syntax and semantic correctness. The semantic check requires cross-referencing of the information in the SQL statement and the data dictionary, including the table names, columns-to-date relationships, column names, data types, and security access privileges. To resolve the relationships, Oracle uses the data dictionary cache in the SGA. When the data sought in not in the cache, Oracle executes SQL statements to retrieve the data dictionary information from the SYSTEM tablespace. These statements for data dictionary information represent one type of recursive SQL statement. To increase the size available to the dictionary cache, increase the size of the shared pool area (via the SHARED_POOL_SIZE init.ora parameter.) Missing a get on the data dictionary or shared pool area of the SGA is more costly than missing a get on a data buffer or waiting for a redo buffer. If these parameters look familiar - you are probably recalling them from Oracle v6 (when you tuned these in the init.ora file)

select PARAMETER,
GETS,
GETMISSES,
round(GETMISSES/GETS,2)*100 "% Cache Misses",
COUNT,
USAGE
from v$rowcache
where GETS > 0
order by (GETMISSES/GETS)*100 desc

Latch Gets/Misses
LATCH GET AND MISS NOTES:

Latch Name - Name of the latch
Gets - Number of times obtained wait
Misses - Number of time obtained with wait but failed first try
Gets / Misses % - Ratio of misses to gets
Immediate Gets - Number of times obtained with no wait
Immediate Misses - Number of times failed to get with no wait

select NAME,
GETS,
MISSES,
round(((GETS-MISSES)*100) / GETS , 2) "Gets/Misses %",
IMMEDIATE_GETS,
IMMEDIATE_MISSES
from v$latch
where GETS != 0
or IMMEDIATE_MISSES > 0
order by ((GETS-MISSES) / GETS) desc

Redo Log Buffer

noreply@blogger.com (Srinivasan .R) — Mon, 3 Aug 2009 23:24:00 -0700

Redo Log Buffer
Here are some scripts related to Redo Log Buffer .

Contention
REDO LOG CONTENTION NOTES:

Redo Latch Name - Name of the latch
Gets - Number of times gotten wait
Misses - Number of times gotten wait but failed first try
Sleeps - Number of times slept when wanted wait
Immediate Gets - Number of times gotten without wait
Immediate Misses - Number of times failed to get without wait

Heavy access to the redo log buffer can result in contention for redo log prompt buffer latches.

select NAME,
GETS,
MISSES,
SLEEPS,
IMMEDIATE_GETS,
IMMEDIATE_MISSES
from v$latch
where NAME in ('redo allocation','redo copy')

Statistics
REDO BUFFER NOTES:

Parameter - Name of the parameter
Value - Current value for the parameter

select NAME,
VALUE
from v$sysstat
where NAME like 'redo%'
and VALUE > 0

Contention/Locking

noreply@blogger.com (Srinivasan .R) — Mon, 3 Aug 2009 23:20:00 -0700

Contention/Locking
Here are some scripts related to Contention/Locking .

Lock Info
LOCK INFORMATION NOTES:

OS User - Name of operating system user
OS PID - Operating system process id
Oracle User - Name of Oracle user
Oracle ID - Oracle session id
Lock Type - Type of lock
Lock Held - Current lock held
Lock Requested - Type of lock requested
Status - Status of object (Blocking, Not Blocking, Global)
Object Owner - Owner of the object
Object Name - Name of the object

select OS_USER_NAME os_user,
PROCESS os_pid,
ORACLE_USERNAME oracle_user,
l.SID oracle_id,
decode(TYPE,
'MR', 'Media Recovery',
'RT', 'Redo Thread',
'UN', 'User Name',
'TX', 'Transaction',
'TM', 'DML',
'UL', 'PL/SQL User Lock',
'DX', 'Distributed Xaction',
'CF', 'Control File',
'IS', 'Instance State',
'FS', 'File Set',
'IR', 'Instance Recovery',
'ST', 'Disk Space Transaction',
'TS', 'Temp Segment',
'IV', 'Library Cache Invalidation',
'LS', 'Log Start or Switch',
'RW', 'Row Wait',
'SQ', 'Sequence Number',
'TE', 'Extend Table',
'TT', 'Temp Table', type) lock_type,
decode(LMODE,
0, 'None',
1, 'Null',
2, 'Row-S (SS)',
3, 'Row-X (SX)',
4, 'Share',
5, 'S/Row-X (SSX)',
6, 'Exclusive', lmode) lock_held,
decode(REQUEST,
0, 'None',
1, 'Null',
2, 'Row-S (SS)',
3, 'Row-X (SX)',
4, 'Share',
5, 'S/Row-X (SSX)',
6, 'Exclusive', request) lock_requested,
decode(BLOCK,
0, 'Not Blocking',
1, 'Blocking',
2, 'Global', block) status,
OWNER,
OBJECT_NAME
from v$locked_object lo,
dba_objects do,
v$lock l
where lo.OBJECT_ID = do.OBJECT_ID
AND l.SID = lo.SESSION_ID

SQL Lock Info
SQL ASSOCIATED WITH LOCK NOTES:

Oracle User - Name of the oracle user
SID - Oracle session id
Serial# - Serial# of the process
Type - Resource type (RW - Row wait enqueue lock, TM - DML enqueue lock, TX - Transaction enqueue lock, UL - User supplied lock)
Held - Type of lock held
Requested - Type of lock requested
ID1 - Resource identifier #1
ID2 - Resource identifier #2
SQL - SQL statement

select sn.USERNAME,
m.SID,
sn.SERIAL#,
m.TYPE,
decode(LMODE,
0, 'None',
1, 'Null',
2, 'Row-S (SS)',
3, 'Row-X (SX)',
4, 'Share',
5, 'S/Row-X (SSX)',
6, 'Exclusive') lock_type,
decode(REQUEST,
0, 'None',
1, 'Null',
2, 'Row-S (SS)',
3, 'Row-X (SX)',
4, 'Share',
5, 'S/Row-X (SSX)',
6, 'Exclusive') lock_requested,
m.ID1,
m.ID2,
t.SQL_TEXT
from v$session sn,
v$lock m ,
v$sqltext t
where t.ADDRESS = sn.SQL_ADDRESS
and t.HASH_VALUE = sn.SQL_HASH_VALUE
and ((sn.SID = m.SID and m.REQUEST != 0)
or (sn.SID = m.SID and m.REQUEST = 0 and LMODE != 4 and (ID1, ID2) in
(select s.ID1, s.ID2
from v$lock S
where REQUEST != 0
and s.ID1 = m.ID1
and s.ID2 = m.ID2)))
order by sn.USERNAME, sn.SID, t.PIECE

Full Table Scans

noreply@blogger.com (Srinivasan .R) — Mon, 3 Aug 2009 23:15:00 -0700

Full Table Scans
Here are some scripts related to Full Table Scans .

System Statistics (Table)SYSTEM STATISTICS (TABLE) NOTES:

Statistic Name - Name of the statistic
Bytes - Size

This query provides information on the full table scan activity. If your application is OLTP only, having long full table scans can be an indicator of having missing or incorrect indexes or untuned SQL.
Table fetch by rowid reflect the cumulative number of rows fetched from tables using a TABLE ACCESS BY ROWID operation.
Table fetch continued row reflect the cumulative number of continued rows fetched. This value is incremented when accessing a row that is longer than a block in length and when accessing "migrated" rows. Migrated rows are rows that were relocated from their original location to a new location because of an update that increased their size to the point where they could no longer be accommodated inside their original block. Access to migrated rows will cause this statistic's value to increment only if the access is performed by ROWID. Full table scans of tables that contain migrated rows do not cause this counter to increment.
Table scan blocks gotten reflect the cumulative number of blocks read for full table scans.
Table scans rows gotten reflect the cumulative number of rows read for full table scans.
Table scans (cache partitions) is used with the Parallel Query Option. The number of RowID ranges corresponds to the number of simultaneous query server processes that scan the table.
Table scans (long scans) indicate a full scan of a table that has > 5 database blocks.
Table scans (rowid ranges) is used with the Parallel Query Option. The number of RowID ranges corresponds to the number of simultaneous query server processes that scan the table.
Table scans (short scans) indicate a full scan of a table that has <= 5 database blocks.

select NAME,
VALUE
from v$sysstat
where NAME like '%table'

Process Table Scans
PROCESS TABLE SCAN NOTES:

User Process - Name of user process
Long Scans - Full scan of a table that has > 5 database blocks.
Short Scans - Full scan of a table that has <= 5 database blocks.
Row Retrieved - Cumulative number of rows read for full table scans.

select ss.username||'('||se.sid||') ' "User Process",
sum(decode(name,'table scans (short tables)',value)) "Short Scans",
sum(decode(name,'table scans (long tables)', value)) "Long Scans",
sum(decode(name,'table scan rows gotten',value)) "Rows Retreived"
from v$session ss,
v$sesstat se,
v$statname sn
where se.statistic# = sn.statistic#
and (name like '%table scans (short tables)%'
or name like '%table scans (long tables)%'
or name like '%table scan rows gotten%')
and se.sid = ss.sid
and ss.username is not null
group by ss.username||'('||se.sid||') '

Process Table Scans (Avg)
PROCESS TABLE SCAN (AVERAGE) NOTES:

User Process - Name of user process
Short Scans - Number of short scans (<= 5 blocks)
Long Scans - Number of long scans (> 5 blocks)
Rows Retrieved - Number of rows retrieved
Long Scans Length - Average long scan length (i.e. full table scan of > 5 blocks)

select ss.username||'('||se.sid||') ' "User Process",
sum(decode(name,'table scans (short tables)',value)) "Short Scans",
sum(decode(name,'table scans (long tables)', value)) "Long Scans",
sum(decode(name,'table scan rows gotten',value)) "Rows Retreived",
round((sum(decode(name,'table scan rows gotten',value)) - (sum(decode(name,'table scans (short tables)',value)) * 5)) / (sum(decode(name,'table scans (long tables)', value))),2) "Long Scans Length"
from v$session ss,
v$sesstat se,
v$statname sn
where se.statistic# = sn.statistic#
and (name like '%table scans (short tables)%'
or name like '%table scans (long tables)%'
or name like '%table scan rows gotten%')
and se.sid = ss.sid
and ss.username is not null
group by ss.username||'('||se.sid||') '
having sum(decode(name,'table scans (long tables)', value)) != 0
order by 3 desc

SENDMAIL configuration

noreply@blogger.com (Srinivasan .R) — Thu, 25 Jun 2009 06:03:00 -0700

SENDMAIL Configuration tested on RedHat 4 update 5
-------------------------------------------------------------------
Follow the below link:

http://www.owlriver.com/tips/sendmail-tip/

1> Apply the patch sendmail-cf-8.13.1-3.2.el4.i386.rpm which is available in 3rd cd
rpm -Uvh sendmail-cf-8.13.1-3.2.el4.i386.rpm

2> To be able to hand email TO it (on port 25), you will need packages:
rpm -q sendmail-cf m4

3> If not present, use your install media and install them

4> Edit /etc/mail/sendmail.mc with your favorite text editor. Toward the bottom of that
file is this stanza:
dnl This changes sendmail to only listen on the loopback device 127.0.0.1
dnl and not on any other network devices. Comment this out if you want
dnl to accept email over the network.
DAEMON_OPTIONS(`Port=smtp,Addr=127.0.0.1, Name=MTA')
... Note that the string "dnl " at the left margin is how comments are added to
sendmail.mc -- this is an artifact of the ancient origins of sendmail and the m4 macro
processor
Make it read as the text says "to accept email over the network":
dnl DAEMON_OPTIONS(`Port=smtp,Addr=127.0.0.1, Name=MTA')
(this is the last line quoted in the first stanza, now commented out.)

4. rebuild /etc/sendmail.cf from the revised /etc/mail/sendmail.mc thus:
m4 /etc/mail/sendmail.mc > /etc/sendmail.cf
(This is considered Unix deep magic -- but all it does is have the m4 macro processor use
the sendmail-cf recipies, and make a new well-formed sendmail.cf. Note that lower in the
sendmail.mc file is an option to specify what hostname your mail server uses -- useful for
pretending to be a server with a resolvable name.)
(Note also that /etc/sendmail.cf moves to become /etc/mail/sendmail.cf with sendmail-
8.12.x and so that command will become m4 /etc/mail/sendmail.mc >
/etc/mail/sendmail.cf in later versions. But there are many other changes, including a
separate non-privleged child process.)

5. Tell sendmail to listen all the time (in so-called daemon mode) instead of delivering
just its own local mail queue just once an hour.
Edit /etc/sysconfig/sendmail
and change:
DAEMON=no
to:
DAEMON=yes

6. And restart sendmail
cd /etc/mail
make
service sendmail restart
(Note: The make step is needed in some variants of Red Hat, as the automatic execution
of the make is missing in some releases of the initscript package.)

7. Confirm it locally:
netstat -an | grep 25 | grep tcp
yields:
tcp 0 0 0.0.0.0:25 0.0.0.0:* LISTEN
(which says the host is listening on ALL interfaces for connections ...)
[I undid step 5 and 6 -- and reran this test -- it yields -- nothing -- that it is NOT able to be
connected to externally on port 25 -- and so THAT port is safe from a remote direct
attack.]

8. Test locally (I list YOUR part OUT of []):
telnet localhost 25
[... banner]
HELO LOCALHOST
[... response]
QUIT
It went:
[root@couch herrold]# telnet localhost 25
Trying 127.0.0.1...
Connected to localhost.
Escape character is '^]'.
220 couch.basement.net ESMTP Sendmail 8.11.6/8.11.6; Sat, 17
Nov 2001 17:10:39 -0500
HELO localhost
250 couch.basement.net Hello
IDENT:+yf0DRAJjMc1ZxKCEIv/Y7hCZQtbC55G@couch.basement.net
[127.0.0.1], pleased to meet you
QUIT
221 2.0.0 couch.basement.net closing connection
Connection closed by foreign host.
$

9. The acid test -- From another host test it (we'll restate the conversation, and hide the
remote end's responses initially for clarity):
telnet couch.basement.net 25
HELO localhost
QUIT
Or more elaborately (I highlight your input in red):
[herrold@router herrold]$ telnet couch.basement.net 25
Trying 172.16.33.101...
Connected to 172.16.33.101.
Escape character is '^]'.
220 couch.basement.net ESMTP Sendmail 8.11.6/8.11.6; Sat, 17
Nov 2001 17:36:05 -0500
HELO localhost
250 couch.basement.net Hello router.basement.net
[172.16.33.2], pleased to meet you
MAIL FROM:
553 5.5.4 godzilla@localhost... Real domain name required for sender address
MAIL FROM:
250 2.1.0 godzilla@microsoft.com... Sender ok
RCPT TO:
250 2.1.5 root@localhost... Recipient ok
DATA
354 Enter mail, end with "." on a line by itself
Linux rules.
.
250 2.0.0 fAHMajY07056 Message accepted for delivery
QUIT
221 2.0.0 couch.basement.net closing connection
Connection closed by foreign host.
[herrold@router herrold]$
.... that was successful -- note that the 'domain must resolve' rule for senders kicked in on
the first part of the "MAIL FROM" offer -- this is an anti-spam feature. [Note the name
look up's working -- I have working residence DHCP and DNS services on Linux ... ].
The X- header is added by procmail.
There is a forward rule in place for root, as recommended in an earlier post; Out at the
end recipient host, we find:
Return-Path:
Received: from couch.basement.net
(dhcp065-024-xx-xx.columbus.rr.com [65.24.xx.xx])
by swampfox.owlriver.com (8.11.6/8.11.6) with ESMTP id
fAHMbGu07271 for ; Sat, 17 Nov
2001 17:37:16 -0500
Received: from localhost (router.basement.net [172.16.33.2])
by couch.basement.net (8.11.6/8.11.6) with SMTP id
fAHMajY07056
for root@localhost; Sat, 17 Nov 2001 17:37:06 -0500
Date: Sat, 17 Nov 2001 17:37:06 -0500
From: godzilla@microsoft.com
Message-Id: <200111172237.fAHMajY07056@couch.basement.net>
X-Loop: herrold@owlriver.com
Linux rules.
Read the received lines from the bottom up --
We forged the sender as "godzilla@microsoft.com" -- spammers do this all the time,
nothing new there; and offered to root@localhost -- The forward rule handed it for
delivery to herrold@owlriver.com. Seems to work fine
As this demonstrated, this is probably not enough to suffice in production. Do NOT
connect this to the outside world -- a bit more needs to be done --- As set, it will receive
mail only with a final (as to the originator's perspective) destination at the localhost --
/etc/mail/access and /etc/mail/local-host-names need attention to receive mail for
handling for others. But again, this is beyond the scope of this presentation.

10> To send a mail
sendmail –v sriasan@gmail.com – use gmail, I had spam issues with yahoo.com
this is to test sendmail
ctrl –d or .
check gmail for the mail

Rollback Segments

noreply@blogger.com (Srinivasan .R) — Mon, 22 Jun 2009 01:59:00 -0700

Rollback Segments
Here are some scripts related to Rollback Segments .

Segments
ROLLBACK INFORMATION NOTES:

Segment Name - Name of the rollback segment.
Owner - Owner of the rollback segment.
Tablespace - Name of the tablespace containing the rollback segment.
Segment ID - ID number of the rollback segment.
File ID - ID number of the block containing the segment header.
Block ID - Starting block number of the extent.
Initial Extent - Initial extent size in bytes.
Next Extent - Secondary extent size in bytes.
Min Extents - Minimum number of extents.
Max Extents - Maximum number of extents.
PCT Increase - Percent increase for extent size.
Status - ONLINE if the segment is online, or PENDING OFFLINE if the segment is going offline but some active (distributed) transactions are using the rollback segment. When the transaction(s) complete, the segment goes OFFLINE.
Instance - Instance this rollback segment belongs to (Parallel Server), or NULL for a single-instance system .

select SEGMENT_NAME,
OWNER,
TABLESPACE_NAME,
SEGMENT_ID,
FILE_ID,
BLOCK_ID,
INITIAL_EXTENT,
NEXT_EXTENT,
MIN_EXTENTS,
MAX_EXTENTS,
PCT_INCREASE,
STATUS,
INSTANCE_NUM
from dba_rollback_segs
order by SEGMENT_NAME

Transactions
ROLLBACK STATISTIC (TRANSACTION TABLES) NOTES:

Statistic Name - Name of the statistic
Value - Current value

The name of the consistent changes statistic is misleading. It does not indicate the number of updates (or changes), but rather, the number of times a consistent get had to retrieve and "old" version of a block because of updates that occurred after the cursor had been opened. As of Oracle7.3, a more accurate statistic was added. Named data blocks consistent reads - undo records applied; the new statistic gives the actual number of data records applied.
The consistent gets statistic reflects the number of accesses made to the block buffer to retrieve data in a consistent mode. Most accesses to the buffer are done with the consistent get mechanism, which uses the SCN (System Change Number) to make sure the data being read has not changed sine the query was started.
The data blocks consistent reads - undo records applied statistic reflects the number of updates (or changes) applied.

select NAME,
VALUE
from v$sysstat
where name in (
'consistent gets',
'consistent changes',
'transaction tables consistent reads - undo records applied',
'transaction tables consistent read rollbacks',
'data blocks consistent reads - undo records applied',
'no work - consistent read gets',
'cleanouts only - consistent read gets',
'rollbacks only - consistent read gets',
'cleanouts and rollbacks - consistent read gets')
order by NAME

Contention
ROLLBACK CONTENTION NOTES:

Segment Name - Name of the rollback segment.
Seg# - Rollback segment number.
Gets - Number of header gets.
Waits - Number of header waits.
Hit Ratio - Ratio of gets to waits. This should be >= 99%.
Active Transactions - Number of active transactions.
Writes - Number of bytes written to rollback segment.

Hit Ratio should be >= 99% - if not, consider adding additional rollback segments.
Check the system undo header, system undo block, undo header, undo block statistics under "Wait Statistics" for additional information on rollback contention.

select b.NAME,
a.USN seg#,
GETS,
WAITS,
round(((GETS-WAITS)*100)/GETS,2) hit_ratio,
XACTS active_transactions,
WRITES
from v$rollstat a,
v$rollname b
where a.USN = b.USN

Growth
ROLLBACK EXTENDING AND SHRINKAGE NOTES:

Rollback Segment - Name of rollback segment.
Seg# - Rollback segment number.
Size - Size in bytes of the rollback segment.
OptSize - Optimal size of rollback segment.
HWM - High Water Mark of rollback segment size.
Extends - Number of times rollback segment was extended to have a new extent.
Wraps - Number of times rollback segment wraps from one extent to another.
Shrinks - Number of times rollback segment shrank, eliminating one or more additional extents each time.
Average Shrink - Total size of freed extents divided by number of shrinks.
Average Active - Current average size of active extents, where "active" extents have uncommitted transaction data.
Status - ONLINE if the segment is online, or PENDING OFFLINE if the segment is going offline but some active (distributed) transactions are using the rollback segment. When the transaction(s) complete, the segment goes OFFLINE.

select NAME,
a.USN,
RSSIZE,
OPTSIZE,
HWMSIZE,
EXTENDS,
WRAPS,
SHRINKS,
AVESHRINK,
AVEACTIVE,
STATUS
from v$rollstat a ,
v$rollname b
where a.USN=b.USN
order by NAME

User Information

noreply@blogger.com (Srinivasan .R) — Mon, 22 Jun 2009 01:20:00 -0700

User Information
Here are some scripts related to User Information .

User Objects
USER OBJECT NOTES:

Username - Owner of the object(s)
Tabs - Table(s)
Inds - Index(es)
Syns - Synonym(s)
Views - Views(s)
Seqs - Sequence(s)
Procs - Procedure(s)
Funcs - Function(s)
Pkgs - Packages(s)
Trigs - Trigger(s)
Deps - Dependencies

select USERNAME,
count(decode(o.TYPE#, 2,o.OBJ#,'')) Tabs,
count(decode(o.TYPE#, 1,o.OBJ#,'')) Inds,
count(decode(o.TYPE#, 5,o.OBJ#,'')) Syns,
count(decode(o.TYPE#, 4,o.OBJ#,'')) Views,
count(decode(o.TYPE#, 6,o.OBJ#,'')) Seqs,
count(decode(o.TYPE#, 7,o.OBJ#,'')) Procs,
count(decode(o.TYPE#, 8,o.OBJ#,'')) Funcs,
count(decode(o.TYPE#, 9,o.OBJ#,'')) Pkgs,
count(decode(o.TYPE#,12,o.OBJ#,'')) Trigs,
count(decode(o.TYPE#,10,o.OBJ#,'')) Deps
from obj$ o,
dba_users u
where u.USER_ID = o.OWNER# (+)
group by USERNAME
order by USERNAME

Invalid Objects
INVALID OBJECT NOTES:

Owner - Owner of the object
Object Type - Type of object
Object Name - Name of the object
Status - Status of the object

select OWNER,
OBJECT_TYPE,
OBJECT_NAME,
STATUS
from dba_objects
where STATUS = 'INVALID'
order by OWNER, OBJECT_TYPE, OBJECT_NAME

Object Modification
OBJECT MODIFICATION NOTES: (Modified in last 7 days)

Owner - Owner of the object
Object Name - Name of the object
Object Type - Type of the object
Last Modified - Last modification date/time
Created - Object creation date/time
Status - Status of the object

select OWNER,
OBJECT_NAME,
OBJECT_TYPE,
to_char(LAST_DDL_TIME,'MM/DD/YYYY HH24:MI:SS') last_modified,
to_char(CREATED,'MM/DD/YYYY HH24:MI:SS') created,
STATUS
from dba_objects
where (SYSDATE - LAST_DDL_TIME) < 7
order by LAST_DDL_TIME DESC

User Privileges
USER PRIVILEGES NOTES:

Grantee - Grantee name, user or role receiving the grant
Granted Role - Granted role name
Admin - Grant was with the ADMIN option
Default - Role is designated as a DEFAULT ROLE for the user
Privilege - System privilege

select rp.GRANTEE,
GRANTED_ROLE,
rp.ADMIN_OPTION,
DEFAULT_ROLE,
PRIVILEGE
from dba_role_privs rp, dba_sys_privs sp
where rp.GRANTEE = sp.GRANTEE
and rp.GRANTEE not in ('SYS','SYSTEM','DBA')
order by rp.GRANTEE, GRANTED_ROLE, PRIVILEGE

Hit/Miss Ratios

noreply@blogger.com (Srinivasan .R) — Mon, 22 Jun 2009 01:12:00 -0700

Hit/Miss Ratios
Here are some scripts related to Hit/Miss Ratios .

Buffer Hit Ratio
BUFFER HIT RATIO NOTES:

Consistent Gets - The number of accesses made to the block buffer to retrieve data in a consistent mode.
DB Blk Gets - The number of blocks accessed via single block gets (i.e. not through the consistent get mechanism).
Physical Reads - The cumulative number of blocks read from disk.

Logical reads are the sum of consistent gets and db block gets.
The db block gets statistic value is incremented when a block is read for update and when segment header blocks are accessed.
Hit Ratio should be > 80%, else increase DB_BLOCK_BUFFERS in init.ora

select sum(decode(NAME, 'consistent gets',VALUE, 0)) "Consistent Gets",
sum(decode(NAME, 'db block gets',VALUE, 0)) "DB Block Gets",
sum(decode(NAME, 'physical reads',VALUE, 0)) "Physical Reads",
round((sum(decode(name, 'consistent gets',value, 0)) +
sum(decode(name, 'db block gets',value, 0)) -
sum(decode(name, 'physical reads',value, 0))) /
(sum(decode(name, 'consistent gets',value, 0)) +
sum(decode(name, 'db block gets',value, 0))) * 100,2) "Hit Ratio"
from v$sysstat

Data Dict Hit Ratio
DATA DICTIONARY HIT RATIO NOTES:

Gets - Total number of requests for information on the data object.
Cache Misses - Number of data requests resulting in cache misses

Hit Ratio should be > 90%, else increase SHARED_POOL_SIZE in init.ora

select sum(GETS),
sum(GETMISSES),
round((1 - (sum(GETMISSES) / sum(GETS))) * 100,2)
from v$rowcache

SQL Cache Hit Ratio
SQL CACHE HIT RATIO NOTES:

Pins - The number of times a pin was requested for objects of this namespace.
Reloads - Any pin of an object that is not the first pin performed since the object handle was created, and which requires loading the object from disk.

Hit Ratio should be > 85%

select sum(PINS) Pins,
sum(RELOADS) Reloads,
round((sum(PINS) - sum(RELOADS)) / sum(PINS) * 100,2) Hit_Ratio
from v$librarycache

Library Cache Miss Ratio
LIBRARY CACHE MISS RATIO NOTES:

Executions - The number of times a pin was requested for objects of this namespace.
Cache Misses - Any pin of an object that is not the first pin performed since the object handle was created, and which requires loading the object from disk.

Hit Ratio should be < 1%, else increase SHARED_POOL_SIZE in init.ora

select sum(PINS) Executions,
sum(RELOADS) cache_misses,
sum(RELOADS) / sum(PINS) miss_ratio
from v$librarycache

Session Statistics

noreply@blogger.com (Srinivasan .R) — Fri, 19 Jun 2009 01:56:00 -0700

Session Statistics
Here are some scripts related to Session Statistics .

Session I/O By User
SESSION I/O BY USER NOTES:

Username - Name of the Oracle process user
OS User - Name of the operating system user
PID - Process ID of the session
SID - Session ID of the session
Serial# - Serial# of the session
Physical Reads - Physical reads for the session
Block Gets - Block gets for the session
Consistent Gets - Consistent gets for the session
Block Changes - Block changes for the session
Consistent Changes - Consistent changes for the session

select nvl(ses.USERNAME,'ORACLE PROC') username,
OSUSER os_user,
PROCESS pid,
ses.SID sid,
SERIAL#,
PHYSICAL_READS,
BLOCK_GETS,
CONSISTENT_GETS,
BLOCK_CHANGES,
CONSISTENT_CHANGES
from v$session ses,
v$sess_io sio
where ses.SID = sio.SID
order by PHYSICAL_READS, ses.USERNAME

CPU Usage By Session
CPU USAGE BY SESSION NOTES:

Username - Name of the user
SID - Session id
CPU Usage - CPU centiseconds used by this session (divide by 100 to get real CPU seconds)

select nvl(ss.USERNAME,'ORACLE PROC') username,
se.SID,
VALUE cpu_usage
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
order by VALUE desc

Resource Usage By User
RESOURCE USAGE BY USER NOTES:

SID - Session ID
Username - Name of the user
Statistic - Name of the statistic
Value - Current value

select ses.SID,
nvl(ses.USERNAME,'ORACLE PROC') username,
sn.NAME statistic,
sest.VALUE
from v$session ses,
v$statname sn,
v$sesstat sest
where ses.SID = sest.SID
and sn.STATISTIC# = sest.STATISTIC#
and sest.VALUE is not null
and sest.VALUE != 0
order by ses.USERNAME, ses.SID, sn.NAME

Session Stats By Session
SESSION STAT NOTES:

Username - Name of the user
SID - Session ID
Statistic - Name of the statistic
Usage - Usage according to Oracle

select nvl(ss.USERNAME,'ORACLE PROC') username,
se.SID,
sn.NAME stastic,
VALUE usage
from v$session ss,
v$sesstat se,
v$statname sn
where se.STATISTIC# = sn.STATISTIC#
and se.SID = ss.SID
and se.VALUE > 0
order by sn.NAME, se.SID, se.VALUE desc

Cursor Usage By Session
CURSOR USAGE BY SESSION NOTES:

Username - Name of the user
Recursive Calls - Total number of recursive calls
Opened Cursors - Total number of opened cursors
Current Cursors - Number of cursor currently in use

select user_process username,
"Recursive Calls",
"Opened Cursors",
"Current Cursors"
from (
select nvl(ss.USERNAME,'ORACLE PROC')||'('||se.sid||') ' user_process,
sum(decode(NAME,'recursive calls',value)) "Recursive Calls",
sum(decode(NAME,'opened cursors cumulative',value)) "Opened Cursors",
sum(decode(NAME,'opened cursors current',value)) "Current Cursors"
from v$session ss,
v$sesstat se,
v$statname sn
where se.STATISTIC# = sn.STATISTIC#
and (NAME like '%opened cursors current%'
or NAME like '%recursive calls%'
or NAME like '%opened cursors cumulative%')
and se.SID = ss.SID
and ss.USERNAME is not null
group by nvl(ss.USERNAME,'ORACLE PROC')||'('||se.SID||') '
)
orasnap_user_cursors
order by USER_PROCESS,"Recursive Calls"

User Hit Ratios
USER HIT RATIO NOTES:

Username - Name of the user
Consistent Gets - The number of accesses made to the block buffer to retrieve data in a consistent mode.
DB Blk Gets - The number of blocks accessed via single block gets (i.e. not through the consistent get mechanism).
Physical Reads - The cumulative number of blocks read from disk.

Logical reads are the sum of consistent gets and db block gets.
The db block gets statistic value is incremented when a block is read for update and when segment header blocks are accessed.
Hit ratio should be > 90%

select USERNAME,
CONSISTENT_GETS,
BLOCK_GETS,
PHYSICAL_READS,
((CONSISTENT_GETS+BLOCK_GETS-PHYSICAL_READS) / (CONSISTENT_GETS+BLOCK_GETS)) Ratio
from v$session, v$sess_io
where v$session.SID = v$sess_io.SID
and (CONSISTENT_GETS+BLOCK_GETS) > 0
and USERNAME is not null
order by ((CONSISTENT_GETS+BLOCK_GETS-PHYSICAL_READS) / (CONSISTENT_GETS+BLOCK_GETS))

Tablespace Information

noreply@blogger.com (Srinivasan .R) — Fri, 19 Jun 2009 00:54:00 -0700

Tablespace Information
Here are some scripts related to Tablespace Information .

Information
TABLESPACE INFORMATION NOTES:

Tablespace Name - Name of the tablespace
Initial Extent - Default initial extent size
Next Extent - Default incremental extent size
Min Extents - Default minimum number of extents
Max Extents - Default maximum number of extents
PCT Increase - Default percent increase for extent size
Status - Tablespace status: ONLINE, OFFLINE, or INVALID (tablespace has been dropped)
Contents - Type of tablespace. This column will have 'TEMPORARY' (v7.3+) for dedicated temporary tablespaces, and 'PERMANENT' for tablespaces that can store both temporary sort segments and permanent objects.

select TABLESPACE_NAME,
INITIAL_EXTENT,
NEXT_EXTENT,
MIN_EXTENTS,
MAX_EXTENTS,
PCT_INCREASE,
STATUS,
CONTENTS
from dba_tablespaces
order by TABLESPACE_NAME

Coalesced Exts
WAIT STATISTIC NOTES:

Tablespace Name - Name of tablespace
Total Extents - Total number of free extents in tablespace
Extents Coalesced - Total number of coalesced free extents in tablespace
% Extents Coalesced - Percentage of coalesced free extents in tablespace
Total Bytes - Total number of free bytes in tablespace
Bytes Coalesced - Total number of coalesced free bytes in tablespace
Total Blocks - Total number of free oracle blocks in tablespace
Blocks Coalesced - Total number of coalesced free Oracle blocks in tablespace
% Blocks Coalesced - Percentage of coalesced free Oracle blocks in tablespace

select TABLESPACE_NAME,
TOTAL_EXTENTS,
EXTENTS_COALESCED,
PERCENT_EXTENTS_COALESCED,
TOTAL_BYTES,
BYTES_COALESCED,
TOTAL_BLOCKS,
BLOCKS_COALESCED,
PERCENT_BLOCKS_COALESCED
from dba_free_space_coalesced
order by TABLESPACE_NAME

Usage
TABLESPACE USAGE NOTES:

Tablespace Name - Name of the tablespace
Bytes Used - Size of the file in bytes
Bytes Free - Size of free space in bytes
Largest - Largest free space in bytes
Percent Used - Percentage of tablespace that is being used - Careful if it is more than 85%

select a.TABLESPACE_NAME,
a.BYTES bytes_used,
b.BYTES bytes_free,
b.largest,
round(((a.BYTES-b.BYTES)/a.BYTES)*100,2) percent_used
from
(
select TABLESPACE_NAME,
sum(BYTES) BYTES
from dba_data_files
group by TABLESPACE_NAME
)
a,
(
select TABLESPACE_NAME,
sum(BYTES) BYTES ,
max(BYTES) largest
from dba_free_space
group by TABLESPACE_NAME
)
b
where a.TABLESPACE_NAME=b.TABLESPACE_NAME
order by ((a.BYTES-b.BYTES)/a.BYTES) desc

Users Default (SYSTEM)
SYSTEM TABLESPACE USAGE NOTES:

Username - Name of the user
Created - User creation date
Profile - Name of resource profile assigned to the user
Default Tablespace - Default tablespace for data objects
Temporary Tablespace - Default tablespace for temporary objects

Only SYS, SYSTEM and possibly DBSNMP should have their default tablespace set to SYSTEM.

select USERNAME,
CREATED,
PROFILE,
DEFAULT_TABLESPACE,
TEMPORARY_TABLESPACE
from dba_users
order by USERNAME

Objects in SYSTEM TS
OBJECTS IN SYSTEM TABLESPACE NOTES:

Owner - Owner of the object
Object Name - Name of object
Object Type - Type of object
Tablespace - Tablespace name
Size - Size (bytes) of object

Any user (other than SYS, SYSTEM) should have their objects moved out of the SYSTEM tablespace

select OWNER,
SEGMENT_NAME,
SEGMENT_TYPE,
TABLESPACE_NAME,
BYTES
from dba_segments
where TABLESPACE_NAME = 'SYSTEM'
and OWNER not in ('SYS','SYSTEM')
order by OWNER, SEGMENT_NAME

Freespace/Largest Ext
FREE, LARGEST, & INITIAL NOTES:

Tablespace - Name of the tablespace
Total Free Space - Total amount (bytes) of freespace in the tablespace
Largest Free Extent - Largest free extent (bytes) in the tablespace

select TABLESPACE_NAME,
sum(BYTES) Total_free_space,
max(BYTES) largest_free_extent
from dba_free_space
group by TABLESPACE_NAME

Disk I/O, Events, Waits

noreply@blogger.com (Srinivasan .R) — Fri, 19 Jun 2009 00:50:00 -0700

Disk I/O, Events, Waits
Here are some scripts related to Disk I/O, Events, Waits .

Datafile I/O
DATAFILE I/O NOTES:

File Name - Datafile name
Physical Reads - Number of physical reads
Reads % - Percentage of physical reads
Physical Writes - Number of physical writes
Writes % - Percentage of physical writes
Total Block I/O's - Number of I/O blocks

Use this report to identify any "hot spots" or 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

SGA Stats
SGA STAT NOTES:

Statistic Name - Name of the statistic
Bytes - Size

select NAME,
BYTES
from v$sgastat
order by NAME

Sort Stats
SORT NOTES:

Sort Parameter - Name of the sort parameter
Value - Number of sorts

sorts (memory) - The number of sorts small enough to be performed entirely in sort areas without using temporary segments.
sorts (disk) - The number of sorts that were large enough to require the use of temporary segments for sorting.
sorts (rows) - Number of sorted rows

The memory area available for sorting is set via the SORT_AREA_SIZE and SORT_AREA_RETAINED_SIZE init.ora parameters.

select NAME,
VALUE
from v$sysstat
where NAME like 'sort%'

All Events
SYSTEM EVENT (ALL) NOTES:

Event Name - Name of the event
Total Waits - Total number of waits for the event
Total Timeouts - Total number of timeouts for the event
Time Waited - The total amount of time waited for this event, in hundredths of a second
Average Wait - The average amount of time waited for this event, in hundredths of a second

select EVENT,
TOTAL_WAITS,
TOTAL_TIMEOUTS,
TIME_WAITED,
round(AVERAGE_WAIT,2) "Average Wait"
from v$system_event
order by TOTAL_WAITS

All Statistics
SYSTEM STATISTICS (ALL) NOTES:

Stat# - Number of the statistic
Name - Name of the statistic
Class - Statistic class: 1 (User), 2 (Redo), 4 (Enqueue), 8 (Cache), 16 (OS), 32 (Parallel Server), 64 (SQL), 128 (Debug)
Value - Value of the statistic

select STATISTIC#,
NAME,
CLASS,
VALUE
from v$sysstat

Wait Stats
WAIT STATISTIC NOTES:

Class - Class of block subject to contention
Count - Number of waits by this OPERATION for this CLASS of block
Time -Sum of all wait times for all the waits by this OPERATION for this CLASS of block

Data Blocks - Usually occurs when there are too many modified blocks in the buffer cache; reduce contention by adding DBWR processes.
Free List - May occur if multiple data loading programs run simultaneously.
Segment Header - May occur when may full table scans execute simultaneously with data loading processes; aggravated by the parallel options. Reschedule data loading jobs to reduce contention;
Sort Block - Rarely seen except when the Parallel Query option is used; reduce contention by reducing the degree of parallelism or decreasing the SORT_AREA_SIZE init.ora parameter setting.
Undo Block - Very rarely occurs; may be caused by multiple users updating records in the same data block at a very fast rate; contention can usually be resolved by increasing the PCTFREE of the tables being modified.
Undo Header - May occur if there are not enough rollback segments to support the number of concurrent transactions.

select CLASS,
COUNT,
TIME
from v$waitstat
order by CLASS