Server Performance Data Collection

1. Disk

2. CPU

3. Network … Etc

There are a number of times in which you find yourself needing performance data. These can include benchmarking, monitoring a system’s general heath or trying to determine what your system was doing at some time in the past. Sometimes you just want to know what the system is doing right now. Depending on what you’re doing, you often end up using different tools, each designed to for that specific situation.

Unlike most monitoring tools that either focus on a small set of statistics, format their output in only one way, run either interatively or as a daemon but not both, collectl tries to do it all. You can choose to monitor any of a broad set of subsystems which currently include buddyinfo, cpu, disk, inodes, infiniband, lustre, memory, network, nfs, processes, quadrics, slabs, sockets and tcp.

By default it shows cpu, network and disk stats in brief format. The key point of this format is all output appears on a single line making it much easier to spot spikes or other anomalies in the output:

[oracle@Mudhalvan] collectl

while writing to an NFS mounted filesystem, collectl displays interrupts, memory usage and nfs activity with timestamps. Keep in mind that you can mix and match any data and in the case of brief format you simply need to have a window wide enough to accommodate your output.
[oracle@Mudhalvan] collectl -sjmf -oT

You can also display the same information in verbose format, in which case you get a single line for each type of data at the expense of more screen real estate, as can be seen in this example of network data during NFS writes. Note how you can actually see the network traffic stall while waiting for the server to physically write the data.
[oracle@Mudhalvan] collectl -sn –verbose -oT
[oracle@Mudhalvan] collectl -sJ -oTm

Output can also be saved in a rolling set of logs for later playback or displayed interactively in a variety of formats. If all that isn’t enough there are additional mechanisms for supplying data to external tools by generating output as s-expressions, a format of choice for some tools such as supermon or in another format called list-expressions. This output can be written to a file or sent over a socket. You can even create files in space-separated format for plotting with external packages like gnuplot or colplot, part of the collectl utilities project, which provides a web-based interface to gnuplot.

Collectl runs on all linux distros (it’s included as part of Fedora) and only requires perl. If the perl Time::Hires module is installed, you will be able to use fractional intervals and display timestamps in msecs. If the Compress::Zlib module is installed the recorded data will be compressed and therefore use on average 90% less storage when recording to a file. Also note that the above links are not for RPMs. If you’d rather work with RPMs there are far too many versions out there to link to and so I’m sorry to say you’re on your own.

Did you know there was an inconsistency in the way Linux reported disk metrics that wasn’t even noticed/fixed until the 2.6-14 kernel was released? Collectl did. Or how about the fact that network stats may not accurately reported by most network monitoring tools at one second intervals? See this page for a description of the problem and how you can get more accurate stats by simply running collectl at a sub-second interval.

You can have the updated version here

Data Guard Background processes

DMON – The Data Guard Broker process.

SNP – The snapshot process.

MRP – Managed recovery process – For Data Guard, the background process that applies archived redo log to the standby database.

ORBn – performs the actual rebalance data extent movements in an Automatic Storage Management instance. There can be many of these at a time, called ORB0, ORB1, and so forth.

OSMB – is present in a database instance using an Automatic Storage Management disk group. It communicates with the Automatic Storage Management instance.

RFS – Remote File Server process – In Data Guard, the remote file server process on the standby database receives archived redo logs from the primary database.

QMN – Queue Monitor Process (QMNn) – Used to manage Oracle Streams Advanced Queuing.

How to set Data Protection Mode of a Primary Database

Step 1 Select a data protection mode that meets your availability, performance and data protection requirements.

Step 2 Verify that redo transport is configured to at least one standby database The value of the LOG_ARCHIVE_DEST_n database initialization parameter that corresponds to the standby database must include the redo transport attributes listed for the data protection mode that you are moving to.

Maximum Protection, Availability – AFFIRM,SYNC, DB_UNIQUE_NAME
Maximum Performance – NOAFFIRM, ASYNC, DB_UNIQUE_NAME

Step 3 Verify that the DB_UNIQUE_NAME database initialization parameter has been set to a unique name on the primary and standby database.

Step 4 Verify that the LOG_ARCHIVE_CONFIG database initialization parameter has been defined on the primary and standby database and that its value includes a DG_CONFIG list that includes the DB_UNIQUE_NAME of the primary and standby database.

SQL> ALTER SYSTEM SET LOG_ARCHIVE_CONFIG=’DG_CONFIG=(MMPRY,MMSTB)’;

Step 5 Shut down the primary database and restart it in mounted mode if the protection mode is being set to Maximum Protection or being changed from Maximum Performance to Maximum Availability. If the primary database is an Oracle Real Applications Cluster, shut down all of the instances and then start and mount a single instance.
For example:
SQL> SHUTDOWN IMMEDIATE;
SQL> STARTUP MOUNT;

Step 6 Set the data protection mode.
Execute the following SQL statement on the primary database:
SQL> ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE {AVAILABILITY | PERFORMANCE | PROTECTION};

Step 7 Open the primary database.
SQL> ALTER DATABASE OPEN;

Step 8 Confirm that the primary database is operating in the new protection mode.

SQL> SELECT PROTECTION_MODE FROM V$DATABASE;

Stored Outlines:
A stored outline is a collection of hints associated with a specific SQL statement that allows a standard execution plan to be maintained, regardless of changes in the system environment or associated statistics.
Plan stability is based on the preservation of execution plans at a point in time where the performance of a statement is considered acceptable.
The outlines are stored in the OL$, OL$HINTS, and OL$NODES tables, but the [USER|ALL|DBA]_OUTLINES and [USER|ALL|DBA]_OUTLINE_HINTS views should be used to display information about existing outlines.
Under normal running the optimizer chooses the most suitable execution plan for the current circumstances.
By using a stored outline you may be forcing the optimizer to choose a substandard execution plan, so you should monitor the affects of your stored outlines over time to make sure this isn’t happening. As a DBA you already know what works well today may not tomorrow with the same plan.

– Grant the necessary privileges.
CONN sys/password AS SYSDBA
GRANT CREATE ANY OUTLINE TO SCOTT;
GRANT EXECUTE_CATALOG_ROLE TO SCOTT;

The following example uses the CREATE OUTLINE statement to create an outline for a specified SQL statement. The statement is assigned to an outline category called SCOTT_OUTLINES to ease administration. If the category is not specified the outline is assigned to the default category.

CONN scott/tiger

— Create an outline for a specific SQL statement.
CREATE OUTLINE emp_dept FOR CATEGORY scott_outlines
ON SELECT e.empno, e.ename, d.dname FROM emp e, dept d WHERE e.deptno = d.deptno;

— Check the outline as been created correctly.
COLUMN name FORMAT A30
SELECT name, category, sql_text FROM user_outlines WHERE category = ‘SCOTT_OUTLINES’;

Do simple search for ocr.loc

/var/opt/oracle/ocr.loc
or
/etc/ocr.loc
or
# ocrcheck

Method 1: Alter Temp Tablespace and the Tempfile

SQL> ALTER ADD TEMPFILE REUSE;

Note : Method 1 will work on 99% if fails then go with the method 2 and it is clean

Method 2: Create a New Temporary Tablespace and recreate the old one

Create New Temporary Tablespace Temp1

SQL> create temporary tablespace temp1 tempfile ‘/u01/oracle/oratemp.dbf’ size 10m;

Make Temp1 as default temporary tablespace and drop the original default temporary tablespace

SQL>alter database default temporary tablespace temp1;

SQL> drop tablespace temp including contents and datafiles;

Now create temporay tablespace with original name and make it as default tablespace of database

SQL>create temporary tablespace temporary tempfile ‘/database/temp01.dbf’ size 500m;

SQL>alter database default temporary tablespace temporary;

Drop the temp1 tablespace

sql>drop tablespace temp1 including contents and datafiles;

DB Optimizer – SQL Performance Tool

http://vimeo.com/13678043

Opatch version including the PSU Patch Level

OS Software Level:
opatch lsinventory -bugs_fixed | grep PSU | sort
Database Level:
column comments format a30;
select to_char(ACTION_TIME,’DD-MON-YY’) action_date, COMMENTS
from registry$history
order by action_time;

CHECKPOINT – CHECKPOINTS:
- data dict: V$INSTANCE_RECOVERY, V$LOG, V$LOG_HISTORY

- init para: log_checkpoint_interval, log_checkpoint_timeout,
log_checkpoints_to_alert

- checkpoints DO NOT cause log switches, but log switches cause checkpoints.

- LGWR or CKPT writes the redo log sequence to the datfile headers and control files AND tells the DBWR to write dirty buffers from the dirty buffer write queue (buffer cache) to disk
- It is a record indicating the point in the redo log where all DB changes prior to this point have been saved in the datafiles.

- manual checkpointing:
– alter system checkpoint

- V$INSTANCE_RECOVERY: lowest value in last four columns controls checkpoints
– redo log file size
– log_checkpoint_timeout
– log_checkpoint_interval
– fast_start_io_target

- log_checkpoint_interval
– redo log blocks (OS blocks not DB blocks) written before a checkpoint
– If set greater than redo log file size, checkpoints occur at log switches
– ignored if set to zero

- log_checkpoint_timeout
– number of seconds since last checkpoint before another is performed
– ignored if set to zero
– default = 1800 seconds (30 minutes)
- log_checkpoints_to_alert
– if true, write checkpoints to alert log

- to decrease checkpoints:
– set log_checkpoint_interval larger than the size of the online redo logs
– eliminate time-based checkpoints by setting log_checkpoint_timeout = 0
– increase size of online redo logs

ERROR:
ORA-1118 – cannot add any more data files: limit of XXX exceeded
The value of db_files has been reached. It must be increased.

SOLUTION:
modify values in the control file:
1. alter database backup controlfile to trace;
2. change MAXDATAFILES in backed-up controlfile
3. shutdown database
4. modify db_files in init file for consistency
5. startup nomount;
6. execute backed-up controlfile
7. alter database open;