Database patch set details on one server of serveral homes

=======================================================================================
Database patch set details on one server of serveral homes
How to gather OPatch inventory for all RAC Oracle Homes, regardless of role separation. 
(Doc ID 1922360.1)
========================================================================================
#!/bin/bash
# File: ListAllOraHomesInv.sh  (Author: Bob Clare, Oracle Support Engineer)
# Created: 21-Mar-15  Modified: 19-Sep-16
# Checks Oracle Central/Global Inventory & Local Registry (along w/OraTab & 
# OraGCHomeList files) to list all active Oracle Homes & Instances, then finds 
# each Home's Owner and runs OPatch from each Home as the Owner, filtering for 
# just the key info.  LFD is Loc[ation] File Directory, OCI is Oracle Central 
# Inventory, OLR is Oracle Local Registry, rest should be self-explanatory!  
export PATH=/sbin:/bin:/usr/sbin:/usr/bin ; Script=$(basename $0)
(($EUID)) && { echo "Script $Script must be run as root!" ; exit 1 ; }
# Above check for root uses a "bash-ism", but remainder is also ksh-compliant. 
{ case $(uname) in SunOS ) OCILFD=/var/opt/oracle ; OLRLFD=$OCILFD ;; * )
OCILFD=/etc ; OLRLFD=$OCILFD/oracle ;; esac ; OCILOC=$OCILFD/oraInst.loc
OEMHL=$OCILFD/oragchomelist ; OLRLOC=$OLRLFD/olr.loc ; ORATAB=$OCILFD/oratab
DL=$(printf '%80s' | tr ' ' '=') ; echo $DL ; [ -r $ORATAB ] && { echo \
"Active Oracle instances (from $ORATAB):" ; cut -d# -f1 $ORATAB | cut -d: \
-f-3 -s | sort | cat -n ; echo $DL ; echo "Oracle Homes (of Active Oracle \
instances from $ORATAB):" ; cut -d# -f1 $ORATAB | cut -d: -f2 -s | sort -u | \
cat -n ; echo $DL ; } ; [ -r $OCILOC ] && { echo "Oracle Central Inventory \
Location Pointer: $OCILOC" ; OCIDIR=$(grep _loc= $OCILOC | cut -d= -f2)
[ -d $OCIDIR ] && { echo "Oracle Central Inventory Directory: $OCIDIR"
OCIXML=$OCIDIR/ContentsXML/inventory.xml ; [ -r $OCIXML ] && { echo "Oracle \
Homes in Central Inventory, except EM plugins" ; echo "(from $OCIXML):"
egrep -v 'REMOVED|sbin|plugins' $OCIXML | grep "HOME NAME=" | cut -d\" -f2,4 \
| tr '"' '=' | sort ; echo $DL ; } ; } ; } ; [ -r $OLRLOC ] && { echo "Oracle \
Local Registry Location Pointer: $OLRLOC" ; CRS_HOME=$(grep home= $OLRLOC | \
cut -d= -f2) ; [ -d $CRS_HOME ] && { echo "CRS Home: $CRS_HOME" ; CRSCFG=\
$CRS_HOME/crs/install/crsconfig_params ; [ -r $CRSCFG ] && { CRS_OWNER=\
$(grep OWNER $CRSCFG | cut -d= -f2) ; [ -n "$CRS_OWNER" ] && { echo "CRS Home \
Owner: $CRS_OWNER" ; OPATCH=$CRS_HOME/OPatch/opatch ; [ -x $OPATCH ] && su \
$CRS_OWNER -c "$OPATCH lsinv -oh $CRS_HOME -all | awk '/^List of Oracle Homes:\
$/,/^$/ { print }'" | egrep -v 'sbin|plugins' ; echo $DL ; } ; } ; } ; }
[ -r $ORATAB ] && { for OH in $(cut -d# -f1 $ORATAB | cut -d: -f2 -s | sort -u)
do OH_OWNER=$(ls -dl $OH | awk '{ print $3 }') ; [ "$OH_OWNER" == "root" ] && \
OH_OWNER=$CRS_OWNER ; ls -dl $OH ; OPATCH=$OH/OPatch/opatch ; [ -x $OPATCH ] \
&& su $OH_OWNER -c "$OPATCH lsinv -oh $OH | egrep '^Ce|^I|^O|^P|^T|from|node'"
echo $DL ; done ; } ; [ -r $OEMHL ] && { echo "Oracle Enterprise Manager OMS \
and/or Agent Homes in $OEMHL :" ; cat -bs $OEMHL ; echo $DL ; { for OH in $(\
cut -d: -f1 $OEMHL | sort -u) ; do [ -d $OH ] && { ls -dl $OH ; OH_OWNER=$(ls \
-dl $OH | awk '{ print $3 }') ; OPATCH=$OH/OPatch/opatch ; [ -x $OPATCH ] && \
su $OH_OWNER -c "$OPATCH lsinv -oh $OH | egrep '^Ce|^E|^I|^O|^P|^T|from|node'"
echo $DL ; } ; done ; } ; } ; } | tee /tmp/OPinv.log
# EOF

Tablespace space details script with temp

set pages 100
col Name for a20
SELECT 
/* PERMANENT*/
   d.tablespace_name "Name", 
   TO_CHAR(NVL(a.bytes / 1024 / 1024, 0),'99,999,990.900') "Allocated Size (Mo)", 
   TO_CHAR(NVL(a.bytes - NVL(f.bytes, 0), 0)/1024/1024,'99,999,999.999') "Used (Mo)",
  TO_CHAR(NVL(a.maxbytes / 1024 / 1024, 0),'99,999,990.900') "MAX Size (Mo)",
  TO_CHAR(NVL((a.bytes - NVL(f.bytes, 0)) / a.maxbytes * 100, 0), '990.00')  "Used % (including Autoextend)" 
FROM 
  sys.dba_tablespaces d, 
  (select tablespace_name, sum(bytes) bytes,sum(decode(autoextensible,'YES',maxbytes,bytes)) maxbytes  
from dba_data_files group by tablespace_name) a, 
  (select tablespace_name, sum(bytes) bytes from dba_free_space group by tablespace_name) f 
WHERE 
 d.tablespace_name = a.tablespace_name(+) AND 
 d.tablespace_name = f.tablespace_name(+) AND NOT 
 (d.extent_management like 'LOCAL' AND d.contents like 'TEMPORARY') 
UNION ALL 
/* TEMPORARY*/
 SELECT   
 A.tablespace_name "Name" , 
      TO_CHAR(NVL(D.mb_total, 0),'99,999,990.900') "Allocated Size (Mo)",
       TO_CHAR(NVL(SUM (A.used_blocks * D.block_size),0)/1024/1024,  '99,999,990.999') "Used (Mo)", 
       TO_CHAR(NVL(M.maxbytes/1024/1024, 0),'99,999,990.900') "MAX Size (Mo)",
        to_char(nvl((SUM (A.used_blocks * D.block_size)/1024/1024 ) / D.mb_total *100,0),'990.00')  "Used % (including Autoextend)"
FROM     v$sort_segment A,
         (
         SELECT   B.name, C.block_size, SUM (C.bytes) / 1024 / 1024 mb_total
         FROM     v$tablespace B, v$tempfile C
         WHERE    B.ts#= C.ts#
         GROUP BY B.name, C.block_size
         ) D,
 ( select tablespace_name,sum(decode(autoextensible,'YES',maxbytes,bytes)) maxbytes  
from dba_temp_files group by tablespace_name) M
WHERE    
 A.tablespace_name = D.name
 and D.name= M.tablespace_name
GROUP by 
A.tablespace_name, D.mb_total,M.maxbytes
order by 5 desc ;

Exadata scripts blog - very good

https://unknowndba.blogspot.com/2018/04/a-shortcut-to-all-scripts-provided-by.html

Openings in HCL -- STORAGE and BACKUP ADMIN

Storage / Backup Admin L2 & L3

Job Reference no:


2008116/50/10860

Position:


Storage / Backup Admin

Location:


Chennai & Noida

Education:


BE/BTech, B.Sc, Diploma, ME/MTech, M.Sc, MCA

Job Description:


EMC Storage Admin ( L2 & L3)



* Experience on Enterprise-class Storage from EMC (Cx Series and Symmetrix)
* Fiber Switches/Directors from Cisco, Brocade, or McData (Zoning / VSAN / ISL setup )
* Lun Management, Monitoring and Replication using ECC/ SYMCLI / SRDF
* Hands-on with PowerPath and Snapview/ Snapclone



NetApps Storage Admin (L2 & L3)



* Experience on Network Appliance devices Data Ontap 6.x and 7.x
* Working knowledge of Enterprise-class Fiber Switch/Director Hardware from Cisco, Brocade, or McData required
* Good Exposure on Monitoring and Replication Strategies (DFM, Snaphot, SnapMirror)
* Strong Storage concepts on LUN mapping/creation on NetApp devices, F/C Switch Config management



Hitachi/HP XP Storage Admin(L2 & L3)



* Minimum 4-6 Years relevant working knowledge of Enterprise-class Storage from Hitachi
* Working knowledge of Enterprise-class Fiber Switch/Director Hardware from Cisco, Brocade and/ or McData required
* Hands-on Experience with Veritas DMP or HDLM
* Good Exposure on Monitoring and Replication Strategies (Shadow Image, True Copy, USP)
* Strong Storage concepts on LUN mapping/creation, F/C Switch Configuration and Zoning



HP EVA Storage Admin(L2 & L3)



* Minimum 4-6 Years relevant working knowledge of HP EVA Storage
* Working knowledge of Enterprise-class Fiber Switch/Director Hardware from Cisco, Brocade and/ or McData required
* Hands-on Experience with Veritas DMP or HDLM
* Good Exposure on Monitoring and Replication Strategies (Shadow Image, True Copy, USP)
* Strong Storage concepts on LUN mapping/creation, F/C Switch Configuration and Zoning.



Veritas Netbackup Admin (L2 & L3)



* 3+ years hands on experience on Veritas NetBackup, preferably version 6.0 and above Unix and/or Windows systems administration experience
* Hands on with Enterprise level SAN tape libraries from ADIC, StorageTek or Quantum.
* Good exposure on advanced backup functions like Synthetic Backups, NDMP backups, Disk Staging or VTL implementation
* Excellent Communication skills and ability to support 24x7 critical operations



Required Skills:

· Should have good communication and customer interaction skills.

· Global exposure in large environments would be preferred.

· Must be open for 24x7 operations

Years of Experience


Total : 4-10 Years of experience





Interviews for this position will be held on 24th July,11 (Sunday)

At the following venue:



HCL Technologies Ltd.

SEZ Unit, ETA Techno Park

4th Floor, Block–1,

No. 33, Old Mahabalipuram Road

Navalur Village

Chengalpet Taluk

Kancheepuram Dist,

Chennai – 603 103



Time – 10:00 AM to 4:00 PM

Openings in HCL - Oracle & Sql DBA

Submit Resumes to -> nagarajupeeta@hcl.com


Database Administration(Oracle & MSSQL)- L2/L3 – Chennai

Position: Oracle Database Administrators

Location: Chennai
Education: BE / B.Tech / MCA / B.Sc / B.com
Job Description for Oracle DBA
Minimum 4 yrs relevant experience on 11g / 10g Oracle Database Administration
Should have worked extensively on Production systems
Experience with Database Migration, Installation, Upgrades and Patching
Experience on HA
Database Cloning
Backup and Recovery Strategies using RMAN
Flash Recovery Features & Flash back query
Managing Alerts and Notifications
Black-out Administration
Cross platform transportable Tablespaces
Proficient with Oracle RAC (Real Application Clusters)*
Cluster Maintenance (Failover / Fail-back) at the Operating System Level
Knowledge of Data Guard / Gateways
Exp : 4-10 Years of experience

Position: MSSQL Database Administrators
Location:Chennai
Education: BE / B.Tech / MCA / B.Sc / B.com
Job Description for SQL DBA
Minimum 4 yrs relevant experience on SQL Server Database Administration
Should have worked extensively on Production systems
Installation & Configuration of MS SQL Server 2005
Performance monitoring (SQL profiler, Performance dashboard, Perfmon, Spotlight, etc)
Performance tuning existing code with developers .
Database fragmentation & compression
Apply Patches & Hot fix
Database Mirroring (Build, Roll, maintenance & troubleshooting)
Replication (Transactional & Peer to Peer)
Clustering ,Backups & restores
Server monitoring (MOM, Tivoli, etc)
Data modeling
SQL Server Integration Services (SSIS)
experience on operational incident and project case
Experience on SQL Server 2008 would be added advantage

Exp : 4 -10 Years of experience
Interviews on 23rd July,11 (Saturday) 10am - 4 pm

Venue:

HCL,
SEZ Unit, ETA Techno Park,
4th Floor, Block–1, No. 33, Old Mahabalipuram Road,
Navalur Village, Chengalpet Taluk, Kancheepuram Dist,
Chennai – 6031

DBA Datadictionary

Here I list the Dictionary Views that can one oracle dba & appsdba use most of the times.

Data Dictionary
Which users are in the database password file:
V$PWFILE_USERS

Where values set in the init.ora file can be viewed – all parameters:
V$PARAMETER

Script used to create the objects that comprise the data dictionary:
catalog.sql

To grant a special role to users so they can look at DBA views:
SELECT_CATALOG_ROLE

Information about all database objects in the database:
DBA_OBJECTS

Information about all tables in the database:
DBA_TABLES

Information about all indexes in the database:
DBA_INDEXES

Information about all views (including dictionary views) in the database:
DBA_VIEWS

Information about all sequences in the database:
DBA_SEQUENCES

Information about all users in the database:
DBA_USERS

Information about all constraints in the database:
DBA_CONSTRAINTS

Information about all table columns that have constraints on them:
DBA_CONS_COLUMNS

Information about all columns that have indexes on them in the database:
DBA_IND_COLUMNS

Information about all columns in all the tables in the database:
DBA_TAB_COLUMNS


Information about all the roles in the database:
DBA_ROLES

Information about all object privileges in the database:
DBA_TAB_PRIVS

Information about all system privileges granted to all users in the database:
DBA_SYS_PRIVS

Displays all PL/SQL source code in the database:
DBA_SOURCE

Information about all triggers in the database:
DBA_TRIGGERS

Information about object privileges granted to roles
ROLE_TAB_PRIVS

Information about system privileges granted to roles
ROLE_SYS_PRIVS

Information about roles granted to roles
ROLE_ROLE_PRIVS

Information about all tablespaces in the database:
DBA_TABLESPACES

Information about all profiles in the database:
DBA_PROFILES


For all parameters?
V$PARAMETER

General information about the database mounted to your instance:
V$DATABASE

Most information about the performance of the database is kept here:
V$SYSSTAT

Most information about the performance for individual user sessions is stored here:
V$SESSION , V$SESSTAT

Information about online redo logs (2)
V$LOG, V$LOGFILE

Information about datafiles
V$DATAFILE

Basic information about control files, and the two columns it has:
V$CONTROLFILE. STATUS / NAME

An object you can query to obtain a listing of all data dictionary objects (4)
CATALOG, CAT, DICTIONARY, DICT.

When the control file was created, Sequence Number, most recent SCN:
V$DATABASE

Information stored in different sections of the control file, Sequence Number:
V$CONTROLFILE_RECORD_SECTION

To see the names and locations of all control files in the db? (2)
V$PARAMETER. V$CONTROLFILE


Tablespace and Datafiles
Temporary Segments:
Name, tablespace location, and owner of temporary segments:
DBA_SEGMENTS

Size of temporary tablespaces, current number of extents allocated to sort segments, and sort segment high-water mark information. Space usage allocation for temporary segments:
V$SORT_SEGMENT

Types of sorts that are happening currently on the database
V$SORT_USAGE

To see the username corresponding with the session:
V$SESSION

Information about every datafile in the database associated with a temporary tablespace:
DBA_TEMP_FILES

Similar to DBA_TEMP_FILES, this performance view gives Information about every datafile in the database associated with a temporary tablespace:
V$TEMPFILE

Storage Structures
A summary view, contains all types of segments and their storage parameters, space utilization settings:
DBA_SEGMENTS

Tablespace quotas assigned to users:
DBA_TS_QUOTAS

Segment name, type, owner, total bytes of extent, name of tablespace storing the extent:
DBA_EXTENTS

The location and amount of free space by tablespace name:
DBA_FREE_SPACE

The location of free space in the tablespace that has been coalesced:
DBA_FREE_SPACE_COALESCED

Information about datafiles for every tablespace
DBA_DATAFILES

Performance view for information for datafiles for every tablespace
V$DATAFILE

To see the total amount of space allocated to a table?
DBA_EXTENTS

Table creation timestamp, information about the object ID:
DBA_OBJECTS

High water mark, all storage settings for a table, and statistics collected as part of the analyze (for row migration) operation on that table
DBA_TABLES

Information about every column in every table:
DBA_TAB_COLUMNS

To determine how many columns are marked unused for later removal?
DBA_UNUSED_COL_TABS

To find the number of deleted index entries ?
INDEX_STATS

To determine the columns on a table that have been indexed:
DBA_ID_COLUMNS

The dynamic view to show whether the index is being used in a meaningful way?
V$OBJECT_USAGE

To see whether a constraint exists on a particular column?
DBA_CONS_COLUMNS
To see the constraints associated with a particular table:
DBA_CONSTRAINTS

To find the username, ID number, (encrypted) password, default and temporary tablespace information, user profile of a user, password expiry date:
DBA_USERS

To all objects, which objects belong to which users, how many objects a user has created?
DBA_OBJECTS

Resource-usage parameters for a particular profile:
DBA_PROFILES

Identifies all resources in the database and their corresponding cost:
RESOURCE_COST

Identifies system resource limits for individual users:
USER_RESOURCE_LIMITS

Shows all system privileges:
DBA_SYS_PRIVS

Show all object privileges:
DBA_TAB_PRIVS

Shows all privileges in this session available to you as the current user:
SESSION_PRIVS

Views for audits currently taking place are created by this script:
cataudit.sql

a list of audit entries generated by the exists option of the audit command:
DBA_AUDIT_EXISTS

A list of audit entries generated for object audits:
DBA_AUDIT_OBJECT

A list of audit entries generated by session connects and disconnects:
DBA_AUDIT_SESSION

A list of audit entries generated by statement options of the audit command:
DBA_AUDIT_STATEMENT

A list of all entries in the AUD$ table collected by the audit command:
DBA_AUDIT_TRAIL

To determine the roles available in the database, the names of all the roles on the database and if a password is required to use each role:
DBA_ROLES

Names of all users and the roles granted to them:
DBA_ROLE_PRIVS

All the roles and the roles that are granted to them:
ROLE_ROLE_PRIVS

Which system privileges have been granted to a role:
DBA_SYS_PRIVS

All the system privileges granted only to roles:
ROLE_SYS_PRIVS

All the object privileges granted only to roles:
ROLE_TAB_PRIVS

All the roles available in the current session:
SESSION_ROLES

Which object privilege has been granted to a role:
DBA_TAB_PRIVS

To display the value of the NLS_CHARACTERSET parameter:
NLS_DATABASE_PARAMETERS

TIME