Oracle Database Patching Procedure

 

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

How to change password of SYS user on ASM

 

For changing the SYS password on ASM:

In 11.2 we can use asmcmd to change the password as follows · In single instance environment $ asmcmd ASMCMD> passwd sys Enter old password (optional): ****** Enter new password: ****** · In Cluster environment ASMCMD> orapwusr --modify --password sys Enter password: ******

We can not change the password for ASM databases via alter user command.
The password should be the one provided when the password file was created,also REMOTE_LOGIN_PASSWORDFILE should be set to EXCLUSIVE on all instances.

If you want to change the password then you would need to recreate the password file using the orapwd utility

How to exclude disk on Linux ASMLib

 

Requirement:

We need the 2 asm disk groups (DG_UAT_DATA_01, DG_UAT_FLASH_01) to be mounted only on +ASM that runs on ServerB.
I see that the disks for these 2 asm disk groups (ORCL:VOL_05_C8, ORCL:VOL_05_C9, ORCL:VOL_05_CE) are presented to both servers (ServerA1 and ServerB) and that ServerB has the 2 disk groups mounted.
I also know that these servers are non-clustered (ie, these are non-RAC ASM instances).

We need to do is to disable ASM on ServerA to discover these 3 asmlib disks that correspond to those 2 asm disk groups.

Solution:

The ASMLIB team will have to do something to disable ServerA to discover these 3 asmlib disks (ie, oracleasm listdisks from ServerA o no longer lst these 3 asmlib disks) but without affecting their discovery in ServerB.
If ASMLIB rpms do not longer lists these 3 disks on ServerA, then ASM on ServerA also will not be presented with them and therefore these will no longer be listed in v$asm_disk view.
 

1. Edit /etc/sysconfig/oracleasm file and you can add the non-required disk in below field:

ORACLEASM_SCANEXCLUDE=

e.g. ORACLEASM_SCANEXCLUDE="sdb sdc"

It will ensure that those disk are not scanned while you perform oracleasm scandisk

2. Restart the ASMLib

Analogy: Restore & Recovery to bone fracture

 

Restore and recovery are analogous to the healing process when you break a bone.

Restoring is similar to the process of setting the broken bone back to its original position.
This is like restoring the datafiles from a backup and placing them in their original locations.

Recovering a datafile is similar to the healing process that recovers the bone back to its state before it was broken.
When you recover your datafiles, you apply transactions (stored in the redo files) to get the datafiles back to the state they were in before the media failure took place.

Traffic analogy: Unix – CPU metric Run queue length

 

The traffic analogy

A single-core CPU is like a single lane of traffic. Imagine you are a bridge operator ... sometimes your bridge is so busy there are cars lined up to cross. You want to let folks know how traffic is moving on your bridge. A decent metric would be how many cars are waiting at a particular time. If no cars are waiting, incoming drivers know they can drive across right away. If cars are backed up, drivers know they're in for delays.

So, Bridge Operator, what numbering system are you going to use? How about:

• 0.00 means there's no traffic on the bridge at all. In fact, between 0.00 and 1.00 means there's no backup, and an arriving car will just go right on.
• 1.00 means the bridge is exactly at capacity. All is still good, but if traffic gets a little heavier, things are going to slow down.
• over 1.00 means there's backup. How much? Well, 2.00 means that there are two lanes worth of cars total -- one lane's worth on the bridge, and one lane's worth waiting. 3.00 means there are three lane's worth total -- one lane's worth on the bridge, and two lanes' worth waiting. Etc.

= load of 1.00

= load of 0.50

= load of 1.70

This is basically what CPU load is. "Cars" are processes using a slice of CPU time ("crossing the bridge") or queued up to use the CPU.

Unix refers to this as the run-queue length: the sum of the number of processes that are currently running plus the number that are waiting (queued) to run.

Like the bridge operator, you'd like your cars/processes to never be waiting. So, your CPU load should ideally stay below 1.00. Also like the bridge operator, you are still ok if you get some temporary spikes above 1.00 ... but when you're consistently above 1.00, you need to worry.

Courtesy: http://blog.scoutapp.com/articles/2009/07/31/understanding-load-averages

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.

SRVCTL vs CRSCTL Difference between SRVCTL and CRSCTL

 

Difference on using SRVCTL and CRSCTL commands:

Use SRVCTL to manage Oracle-supplied resources such as
Listener,
Instances,
Disk groups,
Networks.

Use CRSCTL for managing Oracle Clusterware and its resources.
 
Oracle strongly discourages directly manipulating Oracle-supplied resources (resources whose names begin with ora) using CRSCTL.  This could adversely impact the cluster configuration.

If resource name begins with ora then use SRVCTL

Oracle OEM Grid 11g Setting up Notification Rule for Alert Escalation via E-mail

 

Access this from OEM Grid Right Top Corner –> Preferences –> Notification Rule –> Metrics

Then on the Actions tab, check the Send Email box

OEM 11g: Setting up Repeat Notification alert

 

There is a Repeat Notification option on OEM Grid, which can be useful on monitoring.

It will ensure that alerts are not missed. Since by design OEM Grid will notify only once.

OEM Grid will not page again on the threshold until it goes under the threshold and then exceeds it again.

What it serves:

  When a metric or availability alert is neither acknowledged nor cleared it will keep notify 3 more times in frequency of  four hours.

It can be seen at the Setup –> Notification Methods.

How to: when did the given database schema,last refresh using impdp or import or SQL Loader?

 

Option 1:

If schema refresh is carried out by dropping schema then Created column in DBA_USERS will give the answer.

Select username,created from dba_users where username='USR40'; USERNAME                       CREATED ------------------------------          -------------------- USR40                          28-APR-2013 17:28:16

Option 2:

If schema refresh is carried out by dropping the tables, then we can check the created and last_ddl_time in DBA_OBJECTS

SELECT OWNER,OBJECT_NAME,CREATED,LAST_DDL_TIME FROM DBA_OBJECTS WHERE OWNER='USR40' OWNER                          OBJECT_NAME                              CREATED              LAST_DDL_TIME ------------------------------ ---------------------------------------- -                       ------------------- -------------------- USR40                          XPK_ASE_USR                               28-APR-2013 18:42:09 28-APR-2013 18:42:09 USR40                          XPK_ASE_USR_ACC                       28-APR-2013 18:42:09 28-APR-2013 18:42:09 USR40                          XPK_ASE_USR_ACC_K                   28-APR-2013 18:42:09 28-APR-2013 18:42:10

Oracle SQL PL/SQL Formatting tool

 

I just interested in finding some useful formatting tool for the SQL and PL/SQL.

Now, I found the Instant SQL Formatter http://www.dpriver.com/pp/sqlformat.htm

ORA-00922: missing or invalid option on changing the user password

 

SQL> alter user SIVA identified by 1arkspur; alter user SIVA identified by 1arkspur * ERROR at line 1: ORA-00922: missing or invalid option

Solution:

Please try with double quotes for the password starting with number or having special characters

SQL> alter user SIVA identified by "1arkspur"; User altered.

How to upload big large file to Oracle Support for Service Request

 

If file is big and you want to upload it, you can upload the files to the Oracle FTP site,

ftp.oracle.com.
Login information:
user: anonymous
passwd: <Your Email Address>

Change the directory to /support/incoming
cd support/incoming

Create a new directory
mkdir <Your TAR No#>
cd <Your TAR No#>

Set the FTP in binary mode
binary
hash on

Upload the files:
put <file>
put <file>

quit