Few Oracle 11g RAC R2 srvctl commands:
1.With 11g R2 you can mount the database.This will be very helpful for data guard environments
example :
srvctl start database -d databasename -o mount
2. With 11g R2 its possible to start and stop the listener.
example :
srvctl start listener -l prod_listener
3.With 11g R2 its possible to start multiple service.
example :
srvctl start service -d databasename -s "service1,service_prod"
4. With 11g R2 its possible to start asm diskgroups
example :
srvctl start diskgroup -g "DATA,FRA"
5. With 11g R2 its possible to start RAC asm instance.
example :
srvctl start asm
Thursday, October 7, 2010
Oracle 11g Automatic Diagonistic Repository
Oracle 11g Automatic Diagonistic Repository
Automatic Diagnostic Repository (ADR)
ADR is a file-based repository for database diagnostic data such as traces, incident dumps and packages, the alert log, Health Monitor reports, core dumps, and more.
Problems and Incidents:
A problem is a critical error in the database. Problems are tracked in ADR. Each problem is identified by a unique problem ID and has a problem key, which is a set of attributes that describe the problem.
An incident is a single occurrence of a problem. When a problem occurs multiple times, as is often the case, an incident is created for each occurrence.
Automatic Daigonistic Repository Command Line Tool: ADRCI
Few ADRCI commands:
Purge the ADRCI data:
purge -age 10080 -type alert
purge -age 10080 -type incident
purge -age 10080 -type trace
purge -age 10080 -type cdump
Show Commands:
show alert
show base
show home
show homes
show problem
show report
show incident
How to upload diagnostic data to Oracle Support :
First collect the data in an incident package. When you create an incident package, you select one or more problems to add to the incident package.
IPS CREATE PACKAGE INCIDENT:
ips create package incident
example:
ips create package incident 1234132432 in /tmp
IPS CREATE PACKAGE PROBLEM:
ips create package problem
example:
ips create package problem 132432 in /tmp
Trace file location in 11g:
To view the trace files location you can query the database view V$DIAG_INFO
The V$DIAG_INFO view lists all important ADR locations including:
ADR Base: Path of ADR base
ADR Home: Path of ADR home for the current database instance
Diag Trace: Location of the text alert log and background/foreground process trace files
Diag Alert: Location of an XML version of the alert log
Default Trace File: Path to the trace file for your session. SQL Trace files are written here.
Automatic Diagnostic Repository (ADR)
ADR is a file-based repository for database diagnostic data such as traces, incident dumps and packages, the alert log, Health Monitor reports, core dumps, and more.
Problems and Incidents:
A problem is a critical error in the database. Problems are tracked in ADR. Each problem is identified by a unique problem ID and has a problem key, which is a set of attributes that describe the problem.
An incident is a single occurrence of a problem. When a problem occurs multiple times, as is often the case, an incident is created for each occurrence.
Automatic Daigonistic Repository Command Line Tool: ADRCI
Few ADRCI commands:
Purge the ADRCI data:
purge -age 10080 -type alert
purge -age 10080 -type incident
purge -age 10080 -type trace
purge -age 10080 -type cdump
Show Commands:
show alert
show base
show home
show homes
show problem
show report
show incident
How to upload diagnostic data to Oracle Support :
First collect the data in an incident package. When you create an incident package, you select one or more problems to add to the incident package.
IPS CREATE PACKAGE INCIDENT:
ips create package incident
example:
ips create package incident 1234132432 in /tmp
IPS CREATE PACKAGE PROBLEM:
ips create package problem
example:
ips create package problem 132432 in /tmp
Trace file location in 11g:
To view the trace files location you can query the database view V$DIAG_INFO
The V$DIAG_INFO view lists all important ADR locations including:
ADR Base: Path of ADR base
ADR Home: Path of ADR home for the current database instance
Diag Trace: Location of the text alert log and background/foreground process trace files
Diag Alert: Location of an XML version of the alert log
Default Trace File: Path to the trace file for your session. SQL Trace files are written here.
Monday, January 25, 2010
11g R2 AWR reports
With oracle 11g R2 AWR reports have lot more information.
--OS related information like CPU's ,CORES,SOCKETS.
Example:
Host CPU (CPUs: 8 Cores: 4 Sockets: 2)
--Memory Statistics details
Example:
Host Mem (MB)
SGA use (MB)
PGA use (MB)
% Host Mem used for SGA+PGA
--Wait Events Statistics new details:
Foreground Wait Class
Foreground Wait Events
Background Wait Evengs
Wait Event Histogram Details
--OS related information like CPU's ,CORES,SOCKETS.
Example:
Host CPU (CPUs: 8 Cores: 4 Sockets: 2)
--Memory Statistics details
Example:
Host Mem (MB)
SGA use (MB)
PGA use (MB)
% Host Mem used for SGA+PGA
--Wait Events Statistics new details:
Foreground Wait Class
Foreground Wait Events
Background Wait Evengs
Wait Event Histogram Details
Wednesday, October 28, 2009
11g CRS commands
My 11g CRS commands reference
$crsctl check cluster
$crsctl stop cluster
$crsctl start cluster
$crsctl stop crs -wait - I like like this command
$crsctl enable crs
$crsctl disable crs
$crsctl check crs
$crsctl check crsd
$crsctl check evmd
$crsctl check cssd
$crsctl query css votedisk
$crsctl add css votedisk
$crsctl delete css votedisk
$crsctl check cluster
$crsctl stop cluster
$crsctl start cluster
$crsctl stop crs -wait - I like like this command
$crsctl enable crs
$crsctl disable crs
$crsctl check crs
$crsctl check crsd
$crsctl check evmd
$crsctl check cssd
$crsctl query css votedisk
$crsctl add css votedisk
$crsctl delete css votedisk
Patches with 11g
With 11g you can query the cluster ware patch details. This will make patching easier for dba.If you have more than 2 nodes then this will really help :).
$crsctl query crs softwareversion (Node1 or Node8).
Active version:
Active version is the lowest version in your environment. This will help if you have more than 3-4 nodes.
$crsctl query crs activeversion:
$crsctl query crs softwareversion (Node1 or Node8).
Active version:
Active version is the lowest version in your environment. This will help if you have more than 3-4 nodes.
$crsctl query crs activeversion:
Oracle 11g ASM R2 new features
Today I was reading the new features document on Oracle 11g R2. Oracle has introduce lot of cool features like asmcmd enhasments,diskgroup parameters.This week I am planning to test these parameters on my test box.Looks like DBA life with oracle 11g will be very easy and hope it will not have any bugs.
Disk Group parameters:
-au_size Not its possible to set the au size to 1M,2M,4M,8M...64M.
-access control Wow....access control at the ASM level
-compatible.rdbms
-compatible.asm
Force Disk Group Drop:
DROP DISKGROUP DATA01 FORCE INCLUDING CONTENTS;
With 10g we have to format the disks sometimes.
ASMCMD commands:
-Now we can stop, start the asm instance through ASMCMD.
-Disk group creation from ASMCMD
Disk Group parameters:
-au_size Not its possible to set the au size to 1M,2M,4M,8M...64M.
-access control Wow....access control at the ASM level
-compatible.rdbms
-compatible.asm
Force Disk Group Drop:
DROP DISKGROUP DATA01 FORCE INCLUDING CONTENTS;
With 10g we have to format the disks sometimes.
ASMCMD commands:
-Now we can stop, start the asm instance through ASMCMD.
-Disk group creation from ASMCMD
Sunday, March 8, 2009
How to Trace Unix System Calls for a Process
The following platforms support a trace utility that can be used to identify what a process is doing:
O/S Version Trace Utility
Sun Solaris 2.x, Unixware 7.0 truss, e.g.:
Unixware 7.0
$ truss -aefo
O/S Version Trace Utility
Sun Solaris 2.x, Unixware 7.0 truss, e.g.:
Unixware 7.0
$ truss -aefo
Tuning through 10046 Trace
To Enable Trace
Set these initialization parameters for your trace session to guarantee the integrity of the trace file
alter session set max_dump_file_size=unlimited;
ALTER session SET timed_statistics = true;
alter session set STATISTICS_LEVEL = ALL ;
alter session set “_rowsource_execution_statistics” = true
In order to seperate your produced trace file easily from the others at user_dump_dest folder of Oracle
alter session set tracefile_identifier = SQL_Trace ;
To start tracing from this session
Alter session set SQL_Trace = true ; or
ALTER SESSION SET EVENTS ‘10046 TRACE NAME CONTEXT FOREVER, LEVEL 8′; or
ALTER SESSION SET EVENTS ‘10053 TRACE NAME CONTEXT FOREVER, LEVEL 1′; or
EXECUTE DBMS_SYSTEM.SET_EV(session_id, serial_id, 10046, level, ”);
Run the application that you want to trace, any SQL(s) or any PL/SQL block(s)
select sysdate, user from dual;
To Disable Trace
Alter session set SQL_Trace = false ;
ALTER SESSION SET EVENTS ‘10046 TRACE NAME CONTEXT OFF’;
ALTER SESSION SET EVENTS ‘10053 TRACE NAME CONTEXT OFF’;
EXECUTE DBMS_SYSTEM.SET_EV(session_id, serial_id, 10046, 0, ”);
Formatting Your Trace Files
Use the TKPROF command to format your trace files into readable output. This is the TKPROF syntax:
OS> tkprof tracefile outputfile [options]
tracefile Name of the trace output file (input for TKPROF)
outputfile Name of the file to store the formatted results
When the TKPROF command is entered without any arguments, it generates a usage message together with a description of all TKPROF options. See the next slide for a full listing. This is the output that you get when you enter the TKPROF command without any arguments:
Usage: tkprof tracefile outputfile [explain= ] [table= ]
[print= ] [insert= ] [sys= ] [sort= ]
By default, the .trc file is named after the SPID.
Set these initialization parameters for your trace session to guarantee the integrity of the trace file
alter session set max_dump_file_size=unlimited;
ALTER session SET timed_statistics = true;
alter session set STATISTICS_LEVEL = ALL ;
alter session set “_rowsource_execution_statistics” = true
In order to seperate your produced trace file easily from the others at user_dump_dest folder of Oracle
alter session set tracefile_identifier = SQL_Trace ;
To start tracing from this session
Alter session set SQL_Trace = true ; or
ALTER SESSION SET EVENTS ‘10046 TRACE NAME CONTEXT FOREVER, LEVEL 8′; or
ALTER SESSION SET EVENTS ‘10053 TRACE NAME CONTEXT FOREVER, LEVEL 1′; or
EXECUTE DBMS_SYSTEM.SET_EV(session_id, serial_id, 10046, level, ”);
Run the application that you want to trace, any SQL(s) or any PL/SQL block(s)
select sysdate, user from dual;
To Disable Trace
Alter session set SQL_Trace = false ;
ALTER SESSION SET EVENTS ‘10046 TRACE NAME CONTEXT OFF’;
ALTER SESSION SET EVENTS ‘10053 TRACE NAME CONTEXT OFF’;
EXECUTE DBMS_SYSTEM.SET_EV(session_id, serial_id, 10046, 0, ”);
Formatting Your Trace Files
Use the TKPROF command to format your trace files into readable output. This is the TKPROF syntax:
OS> tkprof tracefile outputfile [options]
tracefile Name of the trace output file (input for TKPROF)
outputfile Name of the file to store the formatted results
When the TKPROF command is entered without any arguments, it generates a usage message together with a description of all TKPROF options. See the next slide for a full listing. This is the output that you get when you enter the TKPROF command without any arguments:
Usage: tkprof tracefile outputfile [explain= ] [table= ]
[print= ] [insert= ] [sys= ] [sort= ]
By default, the .trc file is named after the SPID.
Optimizer parameters tuning
Optimizer parameters tuning:
Most of the optimizer parameter are set to a default value and these parameters effect most of the queries plan. If we set these parameters we can improve the SQL performance a lot.
• optimizer_index_cost_adj - This is an important CBO parameter because it adjusts the propensity of the CBO to favor index access over full-table scan access. The smaller the value, the more like that the CBO will use an available index.
• optimizer_index_caching - This is the parameter that tells Oracle how much of your index is likely to be in the RAM data buffer cache. The setting for optimizer_index_caching effects the CBOs decision to use an index for a table join (nested loops), or to favor a full-table scan.
Most of the optimizer parameter are set to a default value and these parameters effect most of the queries plan. If we set these parameters we can improve the SQL performance a lot.
• optimizer_index_cost_adj - This is an important CBO parameter because it adjusts the propensity of the CBO to favor index access over full-table scan access. The smaller the value, the more like that the CBO will use an available index.
• optimizer_index_caching - This is the parameter that tells Oracle how much of your index is likely to be in the RAM data buffer cache. The setting for optimizer_index_caching effects the CBOs decision to use an index for a table join (nested loops), or to favor a full-table scan.
Friday, November 7, 2008
PARALLEL QUERY
What is Oracle Parallel Query ?
When Oracle has to perform a legitimate, large, full-table scan, Oracle Paralle Query can make a dramatic difference in the response time. Using OPQ, Oracle partitions the table into logical chunks.
Once the table has been partitioned into pieces, Oracle fires off parallel query slaves (sometimes called factotum processes), and each slave simultaneously reads a piece of the large table. Upon completion of all slave processes,
Oracle passes the results back to a parallel query coordinator, which will reassemble the data, perform a sort if required, and return the results back to the end user. Oracle Parallel Query can give you almost infinite scalability, so very large full-table scans that used to take many minutes can now be completed with sub-second response times.
Requriement to Enable parallel query.
1.SMP server with multiple CPUs.
2.Enable parallel_max_servers instance parameter.
What is Symmetric Multiprocessing (SMP)?
Symmetric Multiprocessing, or SMP, is the term used to describe a computer system that is equipped with more than one processor, and also equipped with an operating system capable of distributing load evenly over those processors.
How this can benifit Cameo Database
Parallel Execution of the schema stats.
Parallel load
1.Parallel Execution for a full table scan.
Examples of Parallel Query:
Parallel Query Syntax:
Create table Syntax:
create table c_district_backup parallel (degree 3)
as
select *
from c_district
/
Sunday, July 27, 2008
PL/SQL TUNING
Following is the procedure as how to collect PL/SQL trace and tune them.
Step 1:Enable Specific Subprograms
Enable specific subprograms with one of the two methods:
Enable a subprogram by compiling it with the debug option:
Alter session set PLSQL_DEBUG=true;
Create or Replace ……
Recompile the specific subprogram with the debug option:
Step 2 and 3:Identify a Trace Level and Start Tracing
Specify the trace level by using
Dbms_trace.set_plsql_trace:
Execute the code to be traced:
Step 4:Turn Off Tracing
Remember to turn tracing off by using the
Dbms_trace.Clear_plsql_trace procedure.
Eg:EXECUTE DBMS_TRACE.CLEAR_PLSQL_TRACE
Step 5:Examine the Trace Information
Examine the trace information.
Call tracing writes out the program unit type,name and stack number.
Execption tracing write out the trace number.
Tune the SQL statements from the trace
Rewrite the procedure if its required.
Oracle RAC Wait Events
RAC Differences
The main difference to keep in mind when monitoring a RAC database versus a single-instance database, is the buffer cache and its operation. In a RAC environment the buffer cache is global across all instances in the cluster and hence the processing differs. When a process in a RAC database needs to modify or read data, Oracle will first check to see if it already exists in the local buffer cache. If the data is not in the local buffer cache the global buffer cache will be reviewed to see if another instance already has it in their buffer cache. In this case the remote instance will send the data to the local instance via the high-speed interconnect, thus avoiding a disk read. Monitoring a RAC database often means monitoring this situation and the amount of requests going back and forth over the RAC interconnect. The most common wait events related to this are gc cr request and gc buffer busy.
gc cr request
This wait event, also known as global cache cr request prior to Oracle 10g, specifies the time it takes to retrieve the data from the remote cache. High wait times for this wait event often are because of:
RAC Traffic Using Slow Connection - typically RAC traffic should use a high-speed interconnect to transfer data between instances, however, sometimes Oracle may not pick the correct connection and instead route traffic over the slower public network. This will significantly increase the amount of wait time for the gc rc request event. The oradebug command can be used to verify which network is being used for RAC traffic:
SQL> oradebug setmypid
SQL> oradebug ipc
This will dump a trace file to the location specified by the user_dump_dest Oracle parameter containing information about the network and protocols being used for the RAC interconnect.
Inefficient Queries � poorly tuned queries will increase the amount of data blocks requested by an Oracle session. The more blocks requested typically means the more often a block will need to be read from a remote instance via the interconnect.
gc buffer busy
This wait event, also known as global cache buffer busy prior to Oracle 10g, specifies the time the remote instance locally spends accessing the requested data block. This wait event is very similar to the buffer busy waits wait event in a single-instance database and are often the result of:
Hot Blocks - multiple sessions may be requesting a block that is either not in buffer cache or is in an incompatible mode. Deleting some of the hot rows and re-inserting them back into the table may alleviate the problem. Most of the time the rows will be placed into a different block and reduce contention on the block. The DBA may also need to adjust the pctfree and/or pctused parameters for the table to ensure the rows are placed into a different block.
Inefficient Queries � as with the gc cr request wait event, the more blocks requested from the buffer cache the more likelihood of a session having to wait for other sessions. Tuning queries to access fewer blocks will often result in less contention for the same block.
The main difference to keep in mind when monitoring a RAC database versus a single-instance database, is the buffer cache and its operation. In a RAC environment the buffer cache is global across all instances in the cluster and hence the processing differs. When a process in a RAC database needs to modify or read data, Oracle will first check to see if it already exists in the local buffer cache. If the data is not in the local buffer cache the global buffer cache will be reviewed to see if another instance already has it in their buffer cache. In this case the remote instance will send the data to the local instance via the high-speed interconnect, thus avoiding a disk read. Monitoring a RAC database often means monitoring this situation and the amount of requests going back and forth over the RAC interconnect. The most common wait events related to this are gc cr request and gc buffer busy.
gc cr request
This wait event, also known as global cache cr request prior to Oracle 10g, specifies the time it takes to retrieve the data from the remote cache. High wait times for this wait event often are because of:
RAC Traffic Using Slow Connection - typically RAC traffic should use a high-speed interconnect to transfer data between instances, however, sometimes Oracle may not pick the correct connection and instead route traffic over the slower public network. This will significantly increase the amount of wait time for the gc rc request event. The oradebug command can be used to verify which network is being used for RAC traffic:
SQL> oradebug setmypid
SQL> oradebug ipc
This will dump a trace file to the location specified by the user_dump_dest Oracle parameter containing information about the network and protocols being used for the RAC interconnect.
Inefficient Queries � poorly tuned queries will increase the amount of data blocks requested by an Oracle session. The more blocks requested typically means the more often a block will need to be read from a remote instance via the interconnect.
gc buffer busy
This wait event, also known as global cache buffer busy prior to Oracle 10g, specifies the time the remote instance locally spends accessing the requested data block. This wait event is very similar to the buffer busy waits wait event in a single-instance database and are often the result of:
Hot Blocks - multiple sessions may be requesting a block that is either not in buffer cache or is in an incompatible mode. Deleting some of the hot rows and re-inserting them back into the table may alleviate the problem. Most of the time the rows will be placed into a different block and reduce contention on the block. The DBA may also need to adjust the pctfree and/or pctused parameters for the table to ensure the rows are placed into a different block.
Inefficient Queries � as with the gc cr request wait event, the more blocks requested from the buffer cache the more likelihood of a session having to wait for other sessions. Tuning queries to access fewer blocks will often result in less contention for the same block.
Thursday, July 12, 2007
exp/imp compatibility matrix
1. Migration to Oracle9i release 2 - 9.2.0.x : -------------------------------------------
Direct migration with a full database export and full database import is only supported if the source database is:
- Oracle7 : 7.3.4
- Oracle8 : 8.0.6
- Oracle8i: 8.1.7
- Oracle9i: 9.0.12.
Migration to Oracle10g release 1 - 10.1.0.x : --------------------------------------------- Direct migration with a full database export and full database import is only supported if the source database is:
- Oracle8 : 8.0.6
- Oracle8i: 8.1.7
- Oracle9i: 9.0.1 or 9.2.03.
Migration to Oracle10g release 2 - 10.2.0.x : ---------------------------------------------
Note that you must first apply the specified minimum patch release (or any higher patch release) ! Direct migration with a full database export and full database import is only supported if the source database is:
- Oracle8i : 8.1.7.4
- Oracle9i : 9.0.1.4 (or higher) or 9.2.0.4 (or higher)
- Oracle10g: 10.1.0.2 (or higher)
Examples:1.
From 8.1.7.4 to 9.2.0.7: Full database export with the 8.1.7.4 export utility, and full database import with the 9.2.0.7 import utility is a supported migration method.
2. From 8.0.5.0 to 10.1.0.2: Full database export with the 8.0.5.0 export utility, and full database import with the 10.1.0.2 import utility is *NOT* a supported migration method.
Possible alternatives:
a. First upgrade the 8.0.5.0 database to 8.0.6.0, apply latest patchset 8.0.6.3 and afterwards you can migrate with a full database export with the 8.0.6.3 export utility, and a full database import with the 10.1.0.2 import utility.
b. Or do a full database export with the 8.0.5.0 export utility, pre-create the users in the Oracle10g datatabase, and do a schema level import with the 10.1.0.2 import utility.3. From 9.2.0.1 to 10.2.0.1: First apply the 9.2.0.4 patchset (or any higher patchset release, such as 9.2.0.8). Full database export with the 9.2.0.4 export utility (resp. 9.2.0.8), and full database import with the 10.2.0.1 import utility is a supported migration method.
Direct migration with a full database export and full database import is only supported if the source database is:
- Oracle7 : 7.3.4
- Oracle8 : 8.0.6
- Oracle8i: 8.1.7
- Oracle9i: 9.0.12.
Migration to Oracle10g release 1 - 10.1.0.x : --------------------------------------------- Direct migration with a full database export and full database import is only supported if the source database is:
- Oracle8 : 8.0.6
- Oracle8i: 8.1.7
- Oracle9i: 9.0.1 or 9.2.03.
Migration to Oracle10g release 2 - 10.2.0.x : ---------------------------------------------
Note that you must first apply the specified minimum patch release (or any higher patch release) ! Direct migration with a full database export and full database import is only supported if the source database is:
- Oracle8i : 8.1.7.4
- Oracle9i : 9.0.1.4 (or higher) or 9.2.0.4 (or higher)
- Oracle10g: 10.1.0.2 (or higher)
Examples:1.
From 8.1.7.4 to 9.2.0.7: Full database export with the 8.1.7.4 export utility, and full database import with the 9.2.0.7 import utility is a supported migration method.
2. From 8.0.5.0 to 10.1.0.2: Full database export with the 8.0.5.0 export utility, and full database import with the 10.1.0.2 import utility is *NOT* a supported migration method.
Possible alternatives:
a. First upgrade the 8.0.5.0 database to 8.0.6.0, apply latest patchset 8.0.6.3 and afterwards you can migrate with a full database export with the 8.0.6.3 export utility, and a full database import with the 10.1.0.2 import utility.
b. Or do a full database export with the 8.0.5.0 export utility, pre-create the users in the Oracle10g datatabase, and do a schema level import with the 10.1.0.2 import utility.3. From 9.2.0.1 to 10.2.0.1: First apply the 9.2.0.4 patchset (or any higher patchset release, such as 9.2.0.8). Full database export with the 9.2.0.4 export utility (resp. 9.2.0.8), and full database import with the 10.2.0.1 import utility is a supported migration method.
Wednesday, May 9, 2007
Subscribe to:
Posts (Atom)
