Tweet
Building Tablespace Growth Report from Grid Data
Use SQL Query below at Grid sysman database to locate growth of tablespaces over time. The returned results data could be used under EXCEL to generate graphs. Read more…
Oracle Roles and Users audit report
I have often used following procedure to create list of roles & corresponding users mapped into it. This was then used to email business owners as well as for user accesss auditing. Read more…
Shell Script to Generate Daily/Weekly AWR reports (Email)
Create .run_awr with following details :
"TNS-connect-string : recipient-list : hrs of AWR snapshot" [oracle@ ~]$ cat .run_awr prod:root@oracledbasupport.co.uk:11 I added this script in my crontab for a daily emails:
Dataguard : Applying CPU(Cirital Patch Update) to Dataguard Environment
I have One Primary database and 2 Physical Standby Databases under RHEL 5. Here is a process to patch them with JULY CPU update “Critical Patch Update Release 10.2.0.4 for UNIX Released July 13, 2010″
Script to Auto Start Primary/Standby Database under Linux
The following represents the Oracle recommended method for automating database startup and shutdown of Oracle 10g instances.
Trend Oracle log history : How much archives created per day/week or in an hour
Count of archive files and size of the redo generated by day
SELECT A.*, ROUND (A.Count# * B.AVG# / 1024 / 1024) Daily_Avg_Mb FROM ( SELECT TO_CHAR (First_Time, 'YYYY-MM-DD') DAY, COUNT (1) Count#, MIN (RECID) Min#, MAX (RECID) Max# FROM v$log_history GROUP BY TO_CHAR (First_Time, 'YYYY-MM-DD') ORDER BY 1 DESC) A, (SELECT AVG (BYTES) AVG#, COUNT (1) Count#, MAX (BYTES) Max_Bytes, MIN (BYTES) Min_Bytes FROM v$log) B;
What is Oracle OPatch, How to use OPatch & List patches
Patch is an Oracle supplied utility to assist you with the process of applying interim patches to Oracle’s software. OPatch is a Java-based utility which requires the Oracle Universal Installer to be installed. It is platform independent and runs on all supported operating systems. Read more…
Database hanging due to TX-Transaction (row lock contention)
My Statspack has following details.
Enqueue activity DB/Inst: Snaps: 1791-1808
-> only enqueues with waits are shown
-> Enqueue stats gathered prior to 10g should not be compared with 10g data
-> ordered by Wait Time desc, Waits desc
Identifying Blocking Locks/ Contention & Resolving Database Hanging Issue
One of my 10.2.0.1 database was hanging despite allocating ample opf resources. The CPU, Memory,Disk accesses were normal but under heavy load server would freeze for no valid reason. I had to raise TAR with Oracle.
The Oracle 10g Scheduler
Oracle 10g’s DBMS_SCHEDULER provides significant improvements over DBMS_JOB for scheduling jobs and tasks. Let’s take an overview of new functionalities that the Oracle Scheduler provides for DBAs.
Best way to check patches on Metalink
http://metalink.oracle.com
Using Patches -> simple search with following details
Write a Message to the Alert Log
One can use UTL_FILE else OS editors/utilities. The best is using undocumented KSDWRT procedure of the DBMS_SYSTEM package.
Oracle RDBMS Release Nomenclature
Before 9i
| 8 | RDBMS Version Number |
| 1 | New Features Release Number |
| 5 | Maintenance Release Number |
| 1 | Generic Patch Set Number |
| 2 | Platform Patch Set Number |
Display execution plan in SQLPLUS – plan_table script
I was trying to execute following SQL through client side tools and found network delays were contributing to the SQl execution times. This SQL normally returns in less than 50 millisec and now taking more than 700 Msec. I am addicted to TOAD -TORA GUI created Execution plans and was looking for a an easy way to get execution times as well as plans on sqlplus. Here is how you do it .
Display partition information for a specified index
Displays partition information for the specified index, or all indexes. Read more…
Undo Retention , Undo Optimization
undo_retention parameter will not guarantee a RETENTION unless you define a RETENTION GUARANTEE CLAUSE on tablespace level Read more…
Do I need to reorganise my table?
It’s a tricky question. I have explained here by using a 33GB SAP BW driving table called VBAP which needed a major work. At end I had to rebuild this table using Quest Shareplex.
Oracle Undocumented Parameters
select ksppinm
from x$ksppi
where substr(ksppinm,1,1) = ‘_’;
ORA-00312: Message 312 not found; Solution
Applies to: Oracle Server – Enterprise Edition – Version: 9.2.0.6.0
This problem can occur on any platform.
Symptoms
When NLS_LANGUAGE is set to ENGLISH and NLS_TERRITORY is set to AUSTRALIA, the text related to Archival messages are not displayed. The following contents are shown in the Alert log :
Flashback Query
Oracle Flashback Query allows users to see a consistent view of the database as it was at a point in the past. This functionality allows comparative reporting over time and recovery from logical corruptions.
Sid from Unix Process ID
Here is a situation, This afternoon I have seen my CPU hitting through the roof on a Live system. There are about 6 Oracle instances and one is chewing more than 90% of CPU time.
How to enable trace in Oracle
1. Enable trace at instance level
Put the following line in init.ora. It will enable trace for all sessions and the background
processes
Can't convert a Table Space into Read Only Mode
Reason : Active x’actions running on the table space
Locate SQL_TEXT and Session Address running alter Tablspace Command Read more…
Enable Archivelog, Put Database into ARCHIVE mode
repA>SELECT LOG_MODE FROM SYS.V$DATABASE;
LOG_MODE
————
NOARCHIVELOG
repA> alter system set log_archive_dest_1=’location=/opt/oracle/oraarchive/repA/’
scope=spfile;
Oracle Data Pump
- Data Pump runs only on the server side.
- You may initiate the export from a client but the job(s) themselves will run inside an Oracle server.
- There are no dump files (expdat.dmp) or log files that will be created on your local machine
