Configuring SSH with putty

Recently i joined a project where i had to access multiple servers. We had almost 20 linux boxes which include Development, QA and production databases. I had to frequently login and out of these servers and entering the username and password everytime has become a pain. I use PuTTy tool to access these servers and i have the latest version of PuTTy installed in my widows 7 laptop. The laptop was a company issued one and most of the time i connect these servers through LAN or VPN.

Then i realized i can enable SSH authentication on my PuTTy and to make that work i had to make a few changes on my laptop and remote machines i wanted to login. Here are the steps to enable SSH authentication from my laptop to remote linux servers.

1. At first i had to install PuTTy on my laptop. PuTTy installation for windows can be dowloaded from Putty website, http://www.putty.org/

putty1

2. Once you downloaded the windows installer, double click on it and install the software.

putty2

3. Click on run and start the installation and specify where you want to install the software. I chose the defalut location, which is C:\Program Files (x86)\PuTTY.

putty3

4.  You can opt for a start menu folder as well.

putty5

5.  Create shortcuts/quick menu items if you want.

putty6

6.  Complete the installation by clicking on Finish.

putty7

7. Now you have putty installed on your machine and ready to use.

8. SSH works on puplic key encrtyption and we have to create a public key and a private key. This can be done by using the puttygen.exe tool. For this first navigae to the putty installation folder and for me it is C:\Program Files (x86)\PuTTY.

9. Open puttygen.exe

putty8

10. click on generate button and move your mouse on the designated area to generate random key.

putty9-1

11. At the top of the screen we can see the public key which can be copied to all the servers we want to login. We can use save private key and save it to the local computer somewhere others don’t have access. I kept it in my documents folder so that noone else can access.

putty10

12. Puttygen asks for a passphrase to protect the private key, I didn’t give any because I’m planning to use it in my company laptop which no one else has access.

putty11

13. Once you are done with that , you can save the private key in a secure location.

putty12

14. Now we have the public and private keys and we have to deploy the pucblic key on all the remote hosts. for that open the putty tool and enter the host details. Please make sure you enter your hostname as username@hostname to prevent putty from asking your user id every time.

putty13

15. Once you are done with your host details and saved the details, go to the SSH menu on the left side and then click on Auth sub menu. In the Auth window we have an option to select the private key and select the private key we just saved. Dont forget to go back to the sessions menu and save again.

putty14

16. Now we have to configure the remote server to accept our connection. For that, login to the remote server and run the following commands in your home directory.

$ mkdir .ssh

$ chmod 700 .ssh

$ cd .ssh

$ touch authorized_keys

$ chmod 700 authorized_keys

then open the authorized_keys in vi editor,

vi authorized_keys

Once youy are in the vi screen press ‘o’ (small oh) and then paste the contents of the public key. The press escape and then ‘:wq’ to save the contents.

putty16

17. Now you can exit from the remote connections and open the putty again. Load the connections and click on open. “Whola” you can login without a password and you have a secure login.

 

Advertisements

AWR report from SQL Developer 4.0

I found a cool feature in SQL Developer 4.0 today. I was just toying with the new installation on my machine and noticed a Reports tab on the left side of IDE.

There is a new report category called ” Data Dictionary Reports” and another sub-category ASH and AWR. We can find options to generate ASH and AWR report under this sub-menu.

You can find the report tab details below and when i clicked on Last AWR report it pulled the latest AWR report from the repository.

pic_1

 

Here is a screenshot of the AWR report , and it was very quick and easy.

 

pic_2

 

Hope this helps..

Installing Oracle 11g Express edition on windows

Recently we inducted a group of college graduates into our team. My colleague Rahul and I were asked to give them few sessions or oracle performance engineering. Once we were done with the training our managers asked us to evaluate their performance by checking how they approach and solve some basic performance issues.

We realized that they need oracle databases to be installed on their individual PCs to simulate and solve these performance issues. But it was not practical for us to go and install a database in each one of their machines, so we created a PPT to show them how to download and install oracle 11g express edition on their desktops.

Here is a step by step process of installing oracle 11g R2 express edition from OTN with screenshots. Before going to install this version we need to know what oracle 11g Express edition is and what features are available. Oracle has a detailed documentation on this in their website and I will point some important points you should know in the flowing paragraphs.

Oracle Database 11g Express Edition (Oracle Database XE) is an entry-level, small-footprint database based on the Oracle Database 11g Release 2 code base.  It’s free to develop, deploy, and distribute; fast to download; and simple to administer.

With respect to growing needs for storage, Oracle Database 11g XE now offers a full 11GBfor user data alone, which is almost threefold increase since previous 4GB limit in Oracle Database 10g XE. Other hardware restrictions remain the same with CPU usage capped at one physical core and memory at 1GB.

1. Go to Oracle Technology Network (OTN) website http://www.otn.oracle.com. Log in if you already has an account or create a new account.

Picture1

Picture2

2. Go to downloads tab and click on database menu. You will find a submenu titled as “Database 11g Express Edition”, click on it.

Picture3

Picture4

3. Accept the license agreement and download the version according to the version of OS you have. Here we downloaded the windows 32 bit version.

Picture5

4. Once the download is completed, unzip the file and double click/right click on the setup.msi file and run it as administrator.

Picture6

Picture7

5. After launching the setup.msi file, you see the Install Shield wizard screen while the file loads, like this

Picture8

6. Click the Next button to start the installation.

Picture9

7. The second dialog box is the license information. Click the I accept the terms in the license agreement and then the Next button.

Picture10

8. The third dialog box asks where you want to install the software or provide an override location. If you accept the default location, click the Next button to continue.

Picture11

9. The fourth dialog box lets you specify the TNS, MTS, and HTTP Ports.

Picture12

10. The fifth dialog box lets you enter the password for the SYS and SYSTEM users.

Picture17

11. The sixth dialog box lets you see the configuration options you’ve chosen. If you are okay with click next.

Picture13

12. The seventh dialog box lets you see the progress of your installation.

Picture14

13. The last dialog box lets you see that you’ve installed the product. Click the Finish button to complete the installation.

Picture15

14. You’ve now installed Oracle Database 11g Express Edition. You can start the command line by typing sqlplus system/password in command shell or the Get Started option in your Program Menus, as shown below.

Picture18

15. You should see this console when you launch Get Started page.

Picture16

16. Now you can navigate to the screens you want and start to work on oracle !!

AWR report analysis – part 1

This is the first part of my AWR report analysis blogs. This is my understanding and how I approach an AWR report for proactive/reactive performance tuning. If you find any errors or issues in the articles, please leave a comment below.

AWR is an acronym for Automatic Workload Repository which provides vital database information for analyzing and troubleshooting. Just like any other software products oracle also have instrumentation code integrated to it. Even though this adds a little bit of overhead it helps us to troubleshoot the product efficiently.

AWR is introduced in oracle 10g and prior to that Statspack reports were used for database troubleshooting. And the list of metrics captured by oracle is listed in v$metricname.

The AWR report contains the following set of information

  1. Wait events information.
  2. Time model statistics indicating the amount of DB time associated with a process from the V$SESS_TIME_MODEL and V$SYS_TIME_MODEL views.
  3. Active Session History statistics from the V$ACTIVE_SESSION_HISTORY view.
  4. System and session statistics from the V$SYSSTAT and V$SESSTAT views.
  5. Object usage statistics.
  6. Resource intensive SQL statements.

Many oracle troubleshooting utilities use AWR data for troubleshooting. Oracle utilities using AWR generated information are

  1. Automatic Database Diagnostic Monitor
  2. SQL Tuning Advisor
  3. Undo Advisor
  4. Segment Advisor

Oracle Snapshots

Oracle AWR reports uses snapshots to collect/record data from database. A snapshot is a set of information regarding performance and resource usage of the database over a period of time.

Oracle collects information all the while and save this data while the snapshot happens. Users can alter the snapshot interval based on their requirements. Users can also generate custom snapshots by explicitly running the snapshot command either through OEM or any oracle client software.

We can check the AWR snapshot interval and retention time by using the following command.

SELECT * FROM DBA_HIST_WR_CONTROL

Untitled

Retention Time – Time for which the snapshot information will be stored in the Database.

Default Snapshot Interval time – 1Hr

Default Retention Time – 7 days

Oracle Process responsible for taking snapshots  is MMON (Memory Monitor).

 Baselines

A baseline is defined a range of snapshots that can be used to compare to other pairs of snapshots. The Oracle database server will exempt the snapshots assigned to a specific baseline from the automated purge routine. Thus, the main purpose of a baseline is to preserve typical runtime statistics in the AWR repository. We can run the AWR snapshot reports on the preserved baseline snapshots at any time and compare them to recent snapshots contained in the AWR.

Settings

We need to enable some basic parameters and some privilege for running the AWR report.

Parameters to be set:

TIMED_STATISTICS = TRUE

STATISTICS_LEVEL = TYPICAL or ALL

Required Privileges:

EXECUTE on DBMS_WORKLOAD_REPOSITORY Package

SELECT_CATALOG_ROLE

We can modify the snapshot and baseline setting using the following commands. You need privilege to run these commands. You can run these commands from any oracle clients.

MODIFY_SNAPSHOT_SETTINGS

DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS

(   retention   IN  NUMBER DEFAULT NULL,

interval    IN  NUMBER DEFAULT NULL,

dbid        IN  NUMBER DEFAULT NULL);

CREATE_SNAPSHOT

DBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT

(   flush_level IN VARCHAR2 DEFAULT ‘TYPICAL’) RETURN NUMBER;

FLUSH_LEVEL – Typical or All

CREATE_BASELINE

DBMS_WORKLOAD_REPOSITORY.CREATE_BASELINE

(   start_snap_id    IN  NUMBER,

end_snap_id      IN  NUMBER,

baseline_name    IN  VARCHAR2,

dbid             IN  NUMBER DEFAULT NULL);

Statistics gathering issue

This issue came up few weeks ago. I was asked to look into a time consuming batch job and the job was running for 5-6 hours every day and client wanted to reduce the execution time. This is what the job does every day

  1. Drop the indexes
  2. Load data into the tables
  3. Recreate the indexes
  4. Gather statistics

This looked fine to me at first. The stats procedure was running for a few hours and the client was getting worried on the execution time. Once I tuned the queries in loading procedure I decided to work on the stats gathering script.

Here is a copy of stats gathering script I got. I could see a lot of issues with the way it is written and executing.

stats_before

Some of the issues are

  1. The procedure collects the stats of all the tables irrespective of whether it is really changed or not.
  2. It does the same with all indexes
  3. Here index stats are collected twice; once when the indexes are created and secondly by the procedure.
  4. Parallelism is not used.

Here is the modified script and know what it got completed a lot faster!!

stats_after

Oracle Cardinality hint

Recently I came across a query where the cost was comparatively high. I was wondering what could be the issue for a simple query to have such a high cost. This query has been called many times during the execution and this has considerably increased over all execution time of the process. The query was placed inside a procedure which has a collection as input parameter. This collection is used to filter data from a table with 544K rows.

The business logic was such that we can pass only 5 members to the collection at a time. I was surprised to see oracle does a FTS on the table to fetch 5 rows from an indexed column. I thought of digging a little bit deeper and find the root cause.

Original Query

pic1

Plan

pic2

Here, oracle calculates the size of collection as 8168 but we have only 5 elements in the collection. I thought of adding the cardinality hint and provide the optimizer some more information.

Modified Query with cardinality hint.

pic3

Plan

pic4

We can clearly see that oracle is calculating the collection size properly and able to generate the best plan. we were able to get a great performance difference with this small changes.

How oracle plan baselines saved our day!!

We had an interesting performance issue in our project yesterday. Our senior performance DBA Joe was able to fix it very quickly. I will explain what the issue was and how Joe analyzed it step by step and resolved it using Oracle plan baselines.

It all happened when we noticed one of our batch job is using too much of CPU and time, a quick OEM analysis proved that we had considerably high resource usage while the batch job was running. Most of the resource was consumed by a SQL query

pic1

The plan control told us that we have two execution plans and oracle is picking the cheaper plan.

Oracle used the plan with least cost but it was using the wrong index to scan the tables.  And once he found the problems it is about devising the solutions.

He had 3 options

  1. Ask developers to rewrite the code and deploy it.
  2. Add a hint and direct oracle to use the correct index
  3. Use plan baseline to make oracle use the right plan

The first two options were ruled out because and code change has to follow a deployment lifecycle and might get weeks to hit production. So he immediately chose the third option.

pic2

So he just asked oracle to choose the second plan using the SQL plan baseline, and Bingo!!  The query started running smoothly.

You can read more about the plan baselines below

http://docs.oracle.com/cd/B28359_01/server.111/b28274/optplanmgmt.htm#BABEAFGG