That’s Me
|
Oracle Certified Professional
|
|
7.3, 8i, 9i,10g, 11g
|
|
11i Apps DBA OCP
|
|
10g RAC OCE
|
|
Certified GoldenGate Implementation Specialist
|
|
10g OCM
|
|
11g OCM
|
Feedback
1,650,000 hits
Thanks A MILLION for your support!
Please send me your valuable feedback and suggestions
|
asmcmd is a command line tool that we can use to manage Oracle ASM instances, ASM disk groups, files and dierctories, templates very much as in the same way we would do while working with say a UNIX file system.
ASMCMD can be launched in interactive or non-interactive modes and we need to first ensure that our environment points to the “Grid Infrastructure Home” as in 11g R2, ASM is not part of the standard database software installation. Also chec that the ORACLE_SID points to the ASM instance which should be running.
Let us have a quick look at some of the useful command options which are now available with 11g Release 2.
lsct: Lists information about current Oracle ASM clients from the V$ASM_CLIENT view.
ASMCMD> lsct
DB_Name Status Software_Version Compatible_version Instance_Name Disk_Group
+ASM CONNECTED 11.2.0.1.0
Continue reading ASMCMD examples
In 10g Enterprise Manager Grid Control, we can create corrective action or ‘fixit’ jobs which can automatically run if say a metric threshold is crossed or a particular event occurs. For example, we may have situations where we monitor the disk space used by the archive log files and as soon as the disk space free or available comes below a threshold, we would like another action to automatically occur which in this case could be to backup the archive log files to tape and then delete them to free up the disk space.
Read more about this feature in this worked example …….
We can now use SQL*PLUS type substitution variables in RMAN like &1, &2 and so on and the same can be linked to shell scripts as shown below with the new clause USING where we are passing the Incremental Level and the backup tag to the actual RMAN command.
Also note that in 11g we can run RMAN scripts using the ‘@’ like a SQL script
testbkp.rcv
connect target sys/oracle
run{
backup incremental level &1
database
tag &2;
}
exit;
testbkp.sh
#! /bin/ksh
export level=$1
export tag=$2_`date +%d%b%y`
rman @testbkp.rcv using $level $tag
Note the actual RMAN command that is run …….. it adds a date along with the tag name (parameter 2) and runs a level 2 backup as the first parameter was ’2′
apex:/u01/oracle> ./testbkp.sh 2 dly_inc_bkp
Recovery Manager: Release 11.1.0.6.0 – Production on Tue Sep 22 11:28:05 2009
Copyright (c) 1982, 2007, Oracle. All rights reserved.
RMAN> connect target *
2> run{
3> backup incremental level 2
4> database
5> tag dly_inc_bkp_22Sep09;
6> }
7> exit;
connected to target database:
Continue reading 11g RMAN Substitution Variables
11g Release 2 Edition-based redefinition enables you to upgrade or change the database component of an application while it is in use, thereby minimising or eliminating down time which was earlier required when an object like a procedure required to be modified, but we could not do it without any outage as the application was accessing the procedure or package which needed to be modified.
An edition is like a workspace or private environment where database objects are redefined. When we are satisfied with the change that we have made, those changes in the edition can be then rolled out to all the application users.
Let us see an example of this where we create a procedure, make some changes to the procedure in a new edition and then make those changes visible to other database users.
SQL> GRANT CREATE ANY EDITION, DROP ANY EDITION to sh;
Grant succeeded.
SQL> conn sh/sh
Connected.
SQL> CREATE
Continue reading 11g Release 2 Edition-based Redefinition
Prior to Oracle 11g, the default behaviour was to make statistics available for the optimizer to use as soon as they were gathered.
While statistics are required to enable the optimizer to generate optimal execution plans, sometimes just by gathering fresh statistics tried and trusted execution plans can abruptly change and thereby adversely affect application performance.
In Oracle 11g we can now ‘defer’ the publication of statistics until they have been tried and tested and once we have confirmed that the execution plans are correct and optimal. Statistics remain in the pending state until they are published and the parameter optimizer_use_pending_statistics (default value FALSE) which when set at the session level will enables us to test the pending statistics independently of other database sessions.
Let us look at a test case using the SALES table in the SH schema.
If we see the query below, it appears that the data for the column CHANNEL_ID
Continue reading 11g Pending and Published Statistics
In versions prior to Oracle 11g, we were very likely to have faced the error shown below especially if we used range partitioning and the partition column was a date field.
We needed to ensure that we precreated all the partitions before hand based on the expected values of data that would be inserted (or updated) in a table.
ERROR at line 1:
ORA-14400: inserted partition key does not map to any partition
While we could use the MAXVALUE clause to create a ‘catch-all’ partition, this would not help us when we would like to perform any partition maintenance operations based on a date range or if wanted to use the partitioning feature to perform some kind of data archiving at the partition level.
11g Interval Partitioning
In Oracle 11g, the creation of partitions (for range) is automated and partitions are created as and when needed and takes the task of managing the creation of new
Continue reading 11g Interval Partitioning
Analyze the impact of change on SQL statements using the SQL Performance Analyzer
In the earlier post, we had discussed the Database Capture and Replay component of 11g Real Application Testing – (http://gavinsoorma.wordpress.com/2009/09/11/11g-real-application-testing-making-changes-with-confidence/)
We can also analyse the impact of upgrading from 10.2.0.4 to 11.1.0.6 at the SQL statement level by using the other component of 11g Real Application Testing which is called SQL Performance Analyzer or SPA.
We will use the following two SQL statements to create a SQL Tuning Set (STS) in the 10.2.0.4 environment. This STS is then transported to the 11g target environment and then we will use the GUI Database Control to replay the statements in the STS and generate a Performance Analysis report which will help us identify if any statements have improved or digressed in the changed environment.
SELECT /*+TEST_SPA*/ p.prod_name, s.time_id, t.week_ending_day, SUM(s.amount_sold)
FROM sales s, products p, times t
WHERE s.time_id=t.time_id
AND s.prod_id = p.prod_id
GROUP BY p.prod_name, s.time_id,
Continue reading 11g Real Application Testing – Part 2
CASE STUDY:
Using 11g Real Application Testing to analyse the impact of making the following changes:
1) Upgrade the database from 10g to 11g
2) Convert from single instance database to a two node RAC database
3) Convert from file system based storage to 11g ASM
Overview of Real Application Testing
One of the best new features in Oracle 11g is the Real Application Testing option which enables us to test the result of a change without actually implementing the same in a production environment. Changes such as database version upgrades, optimizer changes, hardware and storage changes all carry with them an element of risk and uncertainty. Using this feature much of that uncertainty and guesswork can be avoided.
Real Application Testing comprises of two components:
a) Database Replay
b) SQL Performance Analyzer (SPA)
SPA enables us to test the impact of a change at the level of a particular SQL statement to see if any improvements or regressions occurred in the execution of the
Continue reading 11g Real Application Testing – making changes with confidence
Although table compression was introduced in Oracle 9i and 10g, it was aimed more at bulk load operations for data warehouse environments. The overhead associated with compression and uncompression made it unsuitable for OLTP type shops.
In Oracle 11g, Advanced Compression (which is an additional licensed feature) includes OLTP compression capabilities as well as compression of unstructured data like images and text with Secure Files and also direct compression of the export dump file generated by Data Pump which is also directly read without any uncompress while doing an import.
The compression feature has been greatly enhanced so as to remove any overhead associated with dealing with compressed data while performing any DML activities.The COMPRESS FOR ALL OPERATIONS keyword needs to be used for enabling OLTP data compression.
While Oracle does claim compression ratios of 3:1, we were able to see clearly 2:1 ratios in terms of storage reductions and no real
Continue reading Oracle 11g Advanced Compression
I have created a simple but very useful Unix shell script driven menu to enable easy monitoring of any number of Oracle databases from a single central location. This covers most of the normal DBA day to day standard monitoring requirements as well as a good interface for aiding in performance tuning.
Set up is pretty simple requiring a single user called MONITOR in each database with SELECT privilege on the data dictionary tables.
Drop me an email at gavin.soorma@bankwest.com.au so that I can send you the zip file along with the installation instructions.
A few screen shots of the various menu options are shown below
#######################################################
[DBA Menu - PRDU010]
Continue reading DBA Monitoring Menu
|
That’s Us
|
|
24x7 Remote/Onsite DBA Support
|
|
Performance Auditing
|
|
Security Auditing
|
|
High Availability Solutions
|
|
Disaster Recovery
|
|
Database Upgrades & Migrations
|
|
Data Migration using GoldenGate
|
|
More about our services ...
|
|
Popular Posts