Life Cycle of SQL Tuning - SQL Profile

 

 

 

Oracle Enterprise Manager 11g - OEM 11g Log file location

 

$ORACLE_HOME/sysman/config/emInstanceMapping.properties

This file will tell you the internal ID (OMS name) of the OMS, and a pointer to the file with all the port and directory details used for this OMS.

EMGC_OMS1=/app/oracle/gc_inst/em/EMGC_OMS1/emgc.properties

Once you know the location of the instance specific files of the OMS, everything else will fall into place. Take note of three key properties in the file, which will help you find the log files

EM_INSTANCE_HOME	EM_INSTANCE_HOME=/app/oracle/gc_inst/em/EMGC_OMS1
EM_DOMAIN_NAME	EM_DOMAIN_NAME=GCDomain
EM_DOMAIN_HOME	EM_DOMAIN_HOME=/app/oracle/gc_inst/user_projects/domains/GCDomain
EM_WEBTIER_INSTHOME	EM_WEBTIER_INSTHOME=/app/oracle/gc_inst/WebTierIH1
The OMS application log files	<EM_INSTANCE_HOME>/sysman/log
	/oracle/gc_inst/em/EMGC_OMS1/sysman/log
OMS Java Application log files	<EM_DOMAIN_HOME>/servers/<OMS ID>/logs
	/oracle/gc_inst/user_projects/domains/GCDomain/servers/EMGC_OMS1/logs
The Application stack log files:	<EM_DOMAIN_HOME>/servers/EMGC_ADMINSERVER/logs
	/oracle/gc_inst/user_projects/domains/GCDomain/servers/EMGC_ADMINSERVER/logs
The Apache (HTTP Server) log files	<EM_WEBTIER_INSTHOME>/diagnostics/logs/OHS/ohs1
	/app/oracle/gc_inst/WebTierIH1/diagnostics/logs/OHS/ohs1

 

Reference: https://blogs.oracle.com/oem/entry/differences_between_em10g_and

universallogcollector.pl - extended of diagcollection.pl

11gR2 Universal Collection is expanded diagcollection.pl to collect

GI
ASM
database (RAC) diagnostics (logfile, trace file etc).

The goal is to reduce back and forth information request between Oracle Support and customers.

The tool collects information from local node only, it needs to be executed on all cluster nodes.
 
Once finishes, it will generate files in current directory. Only one file needs to be uploaded per node, the name of the file is highlighted on screen and default to allData_<nodename>_<timestamp>.tar.gz

Q: What command should i run if runInstaller fail?
A: Run the command: ./universallogcollector.pl --collect --install

Q: What command should i run to collect ASM/RAC trace files?
A: Run the command: /exports/universallogcollector/universallogcollector.pl --collect --excl "vendor, acfs, invt, crs"
   to exclude some of the unrelated log collection and save your time.

Q: What command should i run to collect CRS trace files?
A: Run the command: /exports/universallogcollector/universallogcollector.pl --collect --excl "base, invt,home, vendor, acfs"

Q: What command should i run to check system information?
A: Run command: ./universallogcollector.pl --collect --excl "crs,ocr,core,base,invt,home,vendor,acfs"
   this will only collect system log and system configuration data.

Q: I have serval Oracle homes, will the tool collect them for me?
A: Yes, the tool support multiple Oracle home. it wiill check oraInventory files and if
   multiple Oracle Home detected, it will collect them one by one.

 

Example:

root@dbsrvr1/exports/sivtest>/exports/universallogcollector/universallogcollector.pl --collect Production Copyright 2004, 2010, Oracle. All rights reserved. Universal Log Collector tool Version 1.4 ######### The following diagnostic archives will be created in the local directory if it's not excluded. etcData_dbsrvr1_20130910_0245.tar.gz -> oraInst.loc, oratab and /etc/oracle or /var/opt/oracle(platform dependent). crshomeData_dbsrvr1_20130910_0245.tar.gz -> logs, traces and cores from CRS Home.                                              Note: core files will be packaged only with the --core option. ocrData_dbsrvr1_20130910_0245.tar.gz -> ocrdump, ocrcheck etc. chmosData_dbsrvr1_20130910_0245.tar.gz -> Cluster Health Monitor (OS) data. coreData_dbsrvr1_20130910_0245.tar.gz -> contents of CRS core files in text format. osData_dbsrvr1_20130910_0245.tar.gz -> logs from Operating System. baseData_dbsrvr1_20130910_0245.tar.gz -> logs from CRS Base & Oracle Base(s). invtData_dbsrvr1_20130910_0245.tar.gz -> logs from Oracle installation log. orahomeData_dbsrvr1_20130910_0245.tar.gz -> logs from Oracle Home(s) log. sysconfig_dbsrvr1_20130910_0245.txt -> system config info for cpu, memory, swap, network and disks. crsresStatus_dbsrvr1_20130910_0245.txt -> outputs from "crsctl stat res -t -f [-init]" vendorData_dbsrvr1_20130910_0245.tar.gz -> vendor clusterware logs if present. acfsData_dbsrvr1_20130910_0245.tar.gz -> logs from acfs log. allData_dbsrvr1_20130910_0245.tar.gz -> a summary tarball for all above logs. ######### Collecting CRS home data Collecting information from core dump files No corefiles found Collecting OCR data Collecting Etc Oralce data cp: /var/opt/oracle/oprocd/check/port: Operation not supported on socket cp: /var/opt/oracle/oprocd/stop/port: Operation not supported on socket cp: /var/opt/oracle/oprocd/fatal/port: Operation not supported on socket Collecting CRS base & Oracle base(s) data CRS base not specified or invalid, will try to get correct CRS base Get valid CRS base "/app/oracle" and will collect it. Collecting Oracle home data from "/app/oracle/product/11.1.0/crs_1" Collecting Oracle home data from "/app/oracle/product/11.1.0/db_1" Collecting Oracle home data from "/app/oracle/product/11.1.0/asm_1" Collecting Oracle home data from "/app/oracle/product/em/agent11g" Collecting Oracle home data from "/app/oracle/product/11.2.0.3/client" Collecting Oracle home data from "/app/oracle/product/11.2.0.3/db" Collecting OS logs Collecting Oracle installation logs Collecting vendor cluster logs tar: cannot open var/opt/cmom/cmomd.log Collecting sysconfig data Collecting CRS resource status Done #########Universal Log Collection Finished.#######     Please upload ONLY allData_dbsrvr1_20130910_0245.tar.gz to Oracle Support!

root@usfsuad1/exports/universallogcollector/test_run/run2>/exports/universallogcollector/universallogcollector.pl -help Production Copyright 2004, 2010, Oracle. All rights reserved. Universal Log Collector tool Version 1.4 universallogcollector     --collect         [--crs] For collecting crs diag information.         [--install] For collecting install logs when failed with installation special before root.sh.         [--excl] Exclude specified logs, support crs, ocr, etc, base, home, sys, inv, acfs and vend.         [--adr] For collecting diag information for ADR; specify ADR location.         [--chmos] For collecting Cluster Health Monitor (OS) data.         [--all] Default. For collecting all diag information.         [--core] UNIX only. Package core files with CRS data.         [--crshome] Argument that specifies the CRS Home location.         [--orahome] Argument that specifies the RDBMS Home to collect DB traces and alert logs.             delimited by ",", if empty, default to all RDBMS Homes         [--chmoshome] Argument that specifies the location for collecting Cluster Health Monitor (OS) information.         [--afterdate] UNIX only. Collects archives from the specified date. Specify in MM/DD/YYYY format.         [--aftertime] Supported with -adr option. Collects archives after the specified time. Specify in YYYYMMDDHHMISS24 format.         [--beforetime] Supported with -adr option. Collects archives before the specified date. Specify in YYYYMMDDHHMISS24 format.         [--incidenttime] Collects Cluster Health Monitor (OS) data from the specified time. Specify in MM/DD/YYYY24HH:MM:SS format.             If not specified, Cluster Health Monitor (OS) data generated in past 24 hours will be collected         [--incidentduration] Collects Cluster Health Monitor (OS) data for the duration after the specified time. Specify in HH:MM format.             If not specified, all Cluster Health Monitor (OS) data after incidenttime are collected     NOTE:         You can also do the following:             universallogcollector.pl --collect --crs --crshome <CRS Home>             universallogcollector.pl --collect --excl "crs, ocr, base, acfs"     --clean        cleans up the diagnosability information gathered by this script     --coreanalyze  UNIX only. Extracts information from core files and stores it in a text file

Alternatively consider TFA – Trace File Analyzer.

How to register a database to use RMAN catalog using OEM Grid 11g

 

1. Login Oracle Enterprise Manager.
2. Select Target –> Chose the database
3. Go Availability tab and click "Recovery Catalog Settings" under Backup/Recovery Setup.
4. Choose "Use Recovery Catalog" option.
5. Click "Add Recovery Catalog" button to add recovery catalog.
6. And fill required fields such as host, port,sid, username, etc

 

Oracle 11g Interactive Quick Reference guide

 

I just came across an essential Oracle 11g Interactive Quick Reference guide – which has the key dictionary views, background processes and architecture diagram.
Hope you may find it useful.  It can be downloaded from

http://www.oracle.com/webapps/dialogue/ns/dlgwelcome.jsp?p_ext=Y&p_dlg_id=9575302&src=7027600&Act=54

Oracle Instance & Database Structure

Oracle 11g New features

 

11g New features	Basic Testing	Advanced Testing	On Demand	comments
11g New DBA Features
Partitioning for tables	X			Interval, system, Composite, virtual column, reference
New load balancing utilities	X			web-server, listener
New table Data Type "simple_integer"	X
Improved table/index compression	X
Faster DML triggers	X
Improved NFS data file management	X
Server side connection pooling	X
Incident Packaging Service (IPS)	X
Feature Based Patching	X
New parameters & deprecated and obsolete parameters	X
11g High Availability & RAC new features
Oracle 11g RAC parallel upgrades		X
Oracle RAC load balancing advisor		X
ADDM for RAC		X
Optimized RAC cache fusion protocols		X
Hot patching	If available	If available		Oracle has not created any required patches using "hot Patching"
Data Guard supports "Flashback Standby".	X	X
Data Guard Load Balancing		X
Streams enhancements		X
11g language & PL/SQL New Features
PL/SQL "continue" keyword	X
Disabled state for PL/SQL	X
Improved native Java & PL/SQL compilers.	X
Easy PL/SQL compiling	X
Oracle 11g XML Enhancements & data storage	X
Improved PL/SQL stored procedure	X
object-oriented DDL keyword	X
New Trigger features	X
New high-performance LOB features.	X
Enhanced Read only tables	X
Table trigger firing order	X
Oracle 11g SQL New Features
The /*+result_cache*/ SQL hint	X
XML SQL queries	X
SQL Replay	X
Improved optimizer statistics collection speed	X
SQL execution Plan Management	X
Dynamic SQL	X
Fully Automatic SQL Tuning	X
Improved SQL Access Advisor	X
New Oracle11g Performance and Tuning	X
SQL Performance Analyzer (SPA)	X
Multi-column hybrid histograms	X
CBO correlating statistics	X
Automatic Diagnostic Repository (ADR)	X
Automatic Memory Tuning	X
Resource Manager	X
ADDM for RAC	X	X
Faster sorting	X
SQL Plan Manageability (SQL Plan baselines)	X
AWR Baselines	X
Oracle 11g Security & Auditing new features
Database Replay	X
Secure Passwords	X
Auditing Defaults in 11g	X
RMAN Catalog Security Model			X
Database Administrator Security Setup	X
Transparent Data Encryption (TDE) enhancements			X
TDE and Logminer			X
Encrypted Tablespaces			X
Encrypted LOB columns using Oracle SecureFiles			X
Hardware Secure Module versus Oracle Wallet			X
TDE and Logical Standby Database			X
FGAC for UTL_SMTP, UTL_TCP and UTL_HTTP			X
VPD configuration			X
Application Contexts configuration			X
11g Flashback New Features
Optimized UNDO BACKUP	X
Tracking transactional changes using FLASHBACK DATA	X
Flashback Data Archive	X
Flashing back transactions:using DBMS_FLASHBACK	X
11g ASM enhancements
ASM Fast Mirror Resync-not for external		X
ASM Preferred Mirror Read		X
ASM Performance Enhancements-increase extents size		X
ASM Disk Group compatibility-db and asm level		X
ASM Disk Group Checks enhanced		X
SYSASM ROLE on ASM instance		X
ASMCMD new features		X
11g RMAN enhancements
RMAN Compatibility 10g and 11g	X
Duplicate target DB from active DB	X
Create Standby from duplicate command	X
Merge Catalog functionality	X