Wednesday, January 29, 2014
Wednesday, October 16, 2013
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
Wednesday, October 09, 2013
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
Tuesday, October 08, 2013
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
Thursday, September 26, 2013
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.
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
Wednesday, September 11, 2013
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 ######### etcData_dbsrvr1_20130910_0245.tar.gz -> oraInst.loc, oratab and /etc/oracle or /var/opt/oracle(platform dependent). Collecting CRS home data Please upload ONLY allData_dbsrvr1_20130910_0245.tar.gz to Oracle Support! |
| root@usfsuad1/exports/universallogcollector/test_run/run2>/exports/universallogcollector/universallogcollector.pl -help universallogcollector --collect [--crshome] Argument that specifies the CRS Home location. [--afterdate] UNIX only. Collects archives from the specified date. Specify in MM/DD/YYYY format. NOTE: --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.
Friday, August 30, 2013
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
Friday, August 02, 2013
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
Tuesday, July 23, 2013
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.
Wednesday, May 01, 2013
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 |
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'
|
Thursday, April 11, 2013
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
Thursday, February 28, 2013
ORA-00922: missing or invalid option on changing the user password
| SQL> alter user SIVA identified by 1arkspur; |
Solution:
Please try with double quotes for the password starting with number or having special characters
| SQL> alter user SIVA identified by "1arkspur"; |
Wednesday, January 30, 2013
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
Friday, November 23, 2012
Startup of the databases fail with ORA-00119, ORA-00132
Errors:
ORA-00119: invalid specification for system parameter REMOTE_LISTENER
ORA-00132: syntax error or unresolved network name 'usfsuad-scan.folsom.calwin.eds.com:60000'
| SQL> startup mount ORA-00119: invalid specification for system parameter REMOTE_LISTENER ORA-00132: syntax error or unresolved network name ‘myDBsrvr-scan.siva.com:60000' SQL> exit |
Resolution:
To fix the issue, make sure EZCONNECT is in effective sqlnet.ora: NAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT)
Oracle uses an easy connect naming method to register Oracle Instances in SCAN Listeners by default. Therefore EZCONNECT keyword should be present in sqlnet.ora file.
Alternative:
Change the remote_listener setting to a tnsnames resolvable name allows the database to start.
Ex: remote_listener='myDBsrvr-scan.siva.com:60000' to be changed to testDB.siva
References:
11gR2 Database Fails to Start With Error ORA-00132: syntax error or unresolved network name 'scanname:1521' [ID 1318026.1]
SCAN: ORA-00119, ORA-00132 Incorrect Value for REMOTE_LISTENER [ID 1254929.1]
Tuesday, November 20, 2012
Data Deduplication
Data Deduplication
It eliminates the of data and offer bandwidth optimization and storage space savings.
Environment it suits for;
- File systems
- Low rate Databases
- Virtualizations
- LAN/ SAN
- Archive
- Backup ( where more redundant data are there )
Deployment:
Source ( Before ):
Filesystem
Virtualization
Remote office Branch Office
Target ( After ):
Database
LAN / SAN
For example a typical email system might contain 100 instances of the same one megabyte (MB) file attachment. Each time the email platform is backed up, all 100 instances of the attachment are saved, requiring 100 MB storage space. With data deduplication, only one instance of the attachment is actually stored; the subsequent instances are referenced back to the saved copy for deduplication ratio of roughly 100 to 1.
Why deduplicate data?
Eliminating redundant data can significantly shrink storage requirements and improve bandwidth efficiency. Because primary storage has gotten cheaper over time, enterprises typically store many versions of the same information so that new work can re-use old work. Some operations like Backup store extremely redundant information. Deduplication lowers storage costs since fewer disks are needed, and shortens backup/recovery times since there can be far less data to transfer. In the context of backup and other nearline data, we can make a strong supposition that there is a great deal of duplicate data. The same data keeps getting stored over and over again consuming a lot of unnecessary storage space (disk or tape), electricity (to power and cool the disk or tape drives), and bandwidth (for replication), creating a chain of cost and resource inefficiencies within the organization
How does data deduplication work?
Deduplication segments the incoming data stream, uniquely identifies the data segments, and then compares the segments to previously stored data. If an incoming data segment is a duplicate of what has already been stored, the segment is not stored again, but a reference is created to it. If the segment is unique, it is stored on disk.
For example, a file or volume that is backed up every week creates a significant amount of duplicate data. Deduplication algorithms analyze the data and can store only the compressed, unique change elements of that file. This process can provide an average of 10-30 times or greater reduction in storage capacity requirements, with average backup retention policies on normal enterprise data. This means that companies can store 10TB to 30TB of backup data on 1 TB of physical disk capacity, which has huge economic benefits.
In-line deduplication
This is the process where the deduplication hash calculations are created on the target device as the data enters the device in real time. If the device spots a block that it already stored on the system it does not store the new block, just references to the existing block. The benefit of in-line deduplication over post-process deduplication is that it requires less storage as data is not duplicated. On the negative side, it is frequently argued that because hash calculations and lookups takes so long, it can mean that the data ingestion can be slower thereby reducing the backup throughput of the device. However, certain vendors with in-line deduplication have demonstrated equipment with similar performance to their post-process deduplication counterparts.
Target deduplication is the process of removing duplicates of data in the secondary store. Generally this will be a backup store such as a data repository or a virtual tape library
- File Level Deduplication
- Block Level Deduplication
Thursday, November 01, 2012
Creating Hard link and Soft Links in Unix
| Hard Link | Soft Link / symbolic Link |
| If the inode entries are the same and if the count is 2 then they are hard linked. | if you see filename --> linked_directory |
| la –lia | ls -a |
| ln <source> <target file> (without the option –s which stands for soft link). | ln -s <source> <target file> (with the option –s which stands for soft link). |
| It is essentially create identical copies, No matter which one you update, both of them will get updated | |
| both links must reside on the same filesystem, the source file must exist | that are not applicable to softlinks (source and target can be on seperate file systems, source does not have to exist, etc.) additional I/O necessary to complete file access, additional storage taken up by softlink file's data |
| A "soft" link does not increase the inode count for the referenced file, a "hard" link does. If the count = 1, it's a soft link. If the count is > 1, it's a hard link. |
Example of Viewing existing Hard link
ls –lia
1. Same iNode number : 245787
2. After permission column it lists 2
| oracle@test:/app/oracle/product/11.2.0.3/grid/network/admin $ ls -lia |
Example of viewing SoftLink
| oracle@test1:/app/grid/product/11.2.0.3/network/admin $ ls -lia |
Purging Oracle ADR (Automatic Diagnostic Repository) logs
When ADR logs become unexpectedly large, or too many (as in too many databases for one host), then manual purging is necessary. Following Scripts will be of handy during this time.
|
# -- Customize the following variables $ORACLE_HOME/bin/adrci <<_EOF |
Daily check on the database before business volume picks up
Session Count
| SQL> INST_ID Session Count |
Long Operation – Time Remaining is greater than Zero.
| SQL> l |
DBMS Jobs
| SELECT job, |
Buffer Gets
Replace the v.sql text with the frequent SQL statements important to your application.
| SELECT v.inst_id, |
Subscribe to:
Posts (Atom)