ASH Report in Oracle

 ASH Report in Oracle

Introduction

The ASH Report in Oracle contains recent information on active sessions sampled every second,The AWR and ADDM report will taken every one hour genetates and one hour old report will not generate and will not help in diagnosis issues that are in the current database

The ASH Report in Oracle report is an integral part of the oracle database self-management framework and is extremely useful for diagnosing performance problems

The ASH Report in Oracle gathers sampled data at the session level rather than at the instance level by capturing statastics for only active sessions ,ASH report collects a manageable set of data

ASH Report in Oracle these are the parameters below are the following

• SQL identifier of a SQL statement
• 
  
• Object number,file number,and block number
• 
  
• Wait event identifier and parameters
• 
  
• Session identifier and session serial number
• 
  
• Module and action name
• 
  
• Client identifier of the session

Let us start the process

In this blog i am going to explain ASH Report in Oracle  in single instance database and Rac database and also i am going to tell detailed in ASH report manually 

The AWR and ADDM report will taken every one hour genetates Report

Here i am using  ORCL database and ORCL instance in this example

The ashrpt.sql SQL script generates an HTML or text report that displays ASH information for a specified duration.

To generate an ASH report:

Login to sqlplus / as sysdba

Step 1: At the SQL prompt, enter:

SQL>@$ORACLE_HOME/rdbms/admin/ashrpt.sql

or 

SQL@?/rdbms/admin/ashrpt.sql

Step 2.: Specify whether you want an HTML or a text report:

Enter value for report_type: html

In this example, a html report is chosen.

Step 3:. Specify the begin time in minutes before the system date:

 

Enter value for begin_time: -10

In this example, 10 minutes before the current time is selected.

Step 4.: Enter the duration in minutes that the report for which you want to capture ASH information from the begin time.

Enter value for duration:

In this example, the default duration of system date minus begin time is accepted.

Step 5: Specify the Report  name, or accept the default report name:

Enter value for report_name:

Using the report name : / u01/ash.html

In this example, ASH report ash.html is generated. The report will gather ASH information beginning from 10 minutes before the current time and ending at the current time.

To generate an ASH report in an Oracle RAC environment:

Step1: At the SQL prompt, enter:

SQL@$ORACLE_HOME/rdbms/admin/ashrpti.sql

Step2: Specify whether you want an HTML or a text report:

Enter value for report_type: html

In this example, an HTML report is chosen.

Step 3: A list of available database IDs and instance numbers are displayed:

Instances in this Workload Repository schema

DB Id         Inst Num   DB Name      Instance Host

----------- -------- ------------ ------------ ------------

2206262418 2    ORCLorcl localhost1660

2206262418 1    OMALL341 omallt341 cha341

2206262418 2    OMALL341 omallt342 cha342

2206262418 3   OMALL341 omallt343 cha343

2206262418 4    OMALL341 omallt344 cha344

Enter the values for the database identifier (dbid) and instance number (inst_num):

Enter value for dbid: 2206262418

Using database id: 2206262418

Enter instance numbers. Enter 'ALL' for all instances in an Oracle

RAC cluster or explicitly specify list of instances (e.g., 1,2,3).

Defaults to current instance.

Enter value for inst_num: ALL

Using instance number(s): ALL

Step 4: Specify the begin time in minutes before the system date:

Enter value for begin_time: -1:10

In this example, 1 hour and 10 minutes before the current time is selected.

Step 5:. Enter the duration in minutes that the report for which you want to capture ASH information from the begin time:

Enter value for duration: 10

In this example, the duration is set to 10 minutes.

Step 6:. Specify the slot width in seconds that will be used in the Activity Over Time section of the report:

Enter value for slot_width:

In this example, the default value is accepted.

Step 7: Follow the instructions as explained in the subsequent prompts and enter values for the following report targets:

* target_session_id

* target_sql_id

* target_wait_class

* target_service_hash

* target_module_name

* target_action_name

* target_client_id

* target_plsql_entry

Step 8: Enter a report name, or accept the default report name:

Enter value for report_name:

Using the report name ashrpt_rac_0210_0232.txt

In this example, the default name is accepted and an ASH report named ashrpt_rac_0210_0232 is generated. The report will gather ASH information on all instances belonging to the database with a database ID value of 2206262418 beginning from 10 minutes before the current time and ending at the current time.

Note: Info on ASH Report in Oracle itmay be differ in your environment like production,testing,development etc

THANKS FOR VIEWING MY BLOG FOR MORE UPDATES FOLLOW ME OR SUBSCRIBE ME

Linux Commands Online Test MCQ for Advanced Level

Linux Commands Quiz

Linux Basic Commands Online Test

 

Linux Commands Tutorial

 Linux Commands Tutorial

Introduction:In this Blog i am going to explain  Linux Commands Tutorial  Linux is a Unix-Like operating system. All the Linux/Unix commands are run in the terminal provided by the Linux system. This terminal is just like the command prompt of Windows OS. Linux/Unix commands are case-sensitive. The terminal can be used to accomplish all Administrative tasks

Let us Start the Process Linux Commands Tutorial

1)logname: It shows current username

# logname

2)pwd: Present working directory which means current folder in linux

# pwd

3)date: it display system date and time

# date

4)clear: to clear the screen

 # clear

5)cal: current month calendar we can call month and year along with cal command  ex: cal 5 1982  which displays may month 1982

 # cal

6)uname:  it dispalys the present os name.

 # uname

7)uname -r:  displays kernal version

 

# uname -r

8)hostname: displays server name

 # hostname

9)hostname -i: display server ip address

# hostname -i

10)who: it shows list  of connect user in that server

 

# who

11)who am i:  current logged user info

# who am i 

12)su:  switch username from one user into another user   ex:su - username(oracle is the username)

 # su - oracle 

13)exit:  logout from current user logged in

 # exit

14)man command name: help of the given command

# man

WORKING WITH FILES:

1)cat:  which is use to create a new file or open to  view existing files

# cat chaitanya.txt

2)cat>>filename: appending data to these file

# cat >> chaitanya.txt

3)$touch filename: which  create an empty file with zero kb

# touch chaitanya.txt

FILE DELETION:

 1)rm: to delete the file

# rm chaitanya1.txt

 2)rm -rf: delete the files forcibly 

# rm - rf chaitanya2.txt

 3)rm -i: delete the file which asks the  user conformation 

 # rm - i chaitanya.txt

 4)rm file1 file2: deleting multiple files

# rm chaitanya1.txt  chaitanya2.txt

WORKING WITH DIRECTORIES:

1)mkdir: To create a new directory (Directory is  nothing  but a drive or folders like windows) in linux (here i am using  Linux Commands Tutorial chaitanya is the directory name)

 # mkdir chaitanya  

2)cd dirname: to change a directory (one directory to another directory)

# cd orabackup

3)cd .. : to moves from  current directory into previous directory

# cd ..

4)cd: to come out entire directory

#  cd 

5)cd /: it changes the root directory

# cd /

REMOVING DIRECTORIES:

1)rmdir: to removing directory it must be empty( here i am using Linux Commands Tutorial chaitanya is the directory in that chaitanya directory it should be empty then only it will be deleted)

# rmdir chaitanya

2)rm -r direcname: it deletes recursively to entire structure

# rm - r chaitanya

3)rm -ri direcname: it deletes recursively to entire structure it will ask the user confirmation (here iam using  Linux Commands Tutorial chaitanya is the directory)

 

 # rm - ri

4)rm -rf direcname: it deletes recursively to entire structure with forcibly

# rm -rf

COPY COMMANDS

1)cp sourcefile targetfile: copying file from existing location into  new location

# cp chaitanya1.txt   chaitanya3.txt

# cp chaitanya3.doc  chaitanya03new.doc

# ls - l * .doc( it will displays all doc related files)

# ls - l *.txt (it will displays all text related files)

COPY MULTIPLE FILES SIMULTANEUOSLY INTO ANOTHER DIRECTORY

# cp main.txt demo.txt libc.txt     backup

If backup is located in /home/project, enter:

#  cp main.txt demo.txt libc.txt     /home/project backup

COPY A FILE TO ANOTHER DIRECTORY  

To copy a file from your current directory into another directory called /tmp/, 

# cp filename /tmp

# ls /tmp/filename

# cd /tmp

# ls

COPYING ALL FILES

The star wildcard represents anything i.e. all files. To copy all the files in a directory to a new directory, enter:

# cp *  /u01/oracle/backup

The star wildcard represents anything whose name ends with the .doc extension. So, to copy all the document files (*.doc) in a directory to a new directory, enter:

# cp *.doc  /u01/oracle/backup

To copy a directory, including all its files and subdirectories, to another directory, enter (copy directories recursively):

# cp -R *  /u01/oracle/backup

To copy a file to a new file and preserve the modification date, time, and access control list associated with the source file, enter:

# cp -p chaitanya.txt  /dir1/dir2/

$ cp -p filename /path/to/new/location/myfile

MOVING COMMANDS

1)mv old file newlocation: using these command we can move a file from one folder into  another  folder or we can rename file also

Move mainc.txt defh.txt  files to /u01/oracle/oracbackup/ directory:

# mv mainc.txt defh.txt   /u01/oracle/orabackup/

Move all C files in current directory to subdirectory orabackup :

# mv *.c orabackup

Move all files in subdirectory orabackup to current directory :

# mv orabackup/* .

Rename file main.c to main.backup:

# mv main.c main.backup

Rename directory bak to bak3:

# mv bak bak3

 

Update - move when main.c is newer:

# mv -u main.c bak

#

Move main.c and prompt before overwrite bak/main.c:

# mv -v main.c bak

'bak/main.c' -> 'bak/main.c'

#

VIEWING FILE COMMANDS

1)ls : it's listing files which are in that directory with  ascending order

# ls

2)ls -a: listed all files including  hidden files also

# ls -a 

3)ls -l: listed all files in long list  format i.e 9 field

# ls -1

4)ls -t: listed all files based on date and time of creation

# ls -t

5)ls -r: listed all files on descending order or sin reverse

# ls -r

6)ls -R: listed all files recursively

# ls -R

CREATING HIDDEN FILES

1)cat>.filename

# cat > .chaitanya9.txt

2)mv filename .filename

# mv chaitanya1.txt  .chaitanya6.txt

3)mkdir .dirname

# mkdir  .chaitanya

UNHIDE FILES

1)mv .chaitanya chaitanya

# mv  .chaitanya chaitanya

2)mv .dirname dirname

# mv . chaitanya chaitanya

Note: Info on  Linux Commands Tutorial it may be differ in your environment like production,testing development and naming conventions etc

THANKS FOR VIEWING MY BLOG FOR MORE UPDATES FOLLOW ME OR SUBSCRIBE ME

Oracle 18c New Features

 Oracle 18c New Features

Introduction

In this blog i am going to explain three new Oracle 18c New Features which is useful for oracle database administrators 

SET ROWLIMIT 

ORAVERSION UTILITY

SET FEEDBACK OPTION

SET ROWLIMIT command enables users to set a limit for the number of rows displayed for a query.

for limiting number  of rows to 5 in Oracle 18c New Features

SQL> set rowlimit 5

SQL> select username from dba_users;

USERNAME

—————————

SYS

SYSTEM

ABHIRAM

CHAITANYA

MANASA

5 rows selected. (rowlimit reached)

for limiting number  of rows to 3

SQL> set rowlimit 3

SQL> select username from dba_users;

USERNAME

————————

SYS

SYSTEM

CHAITANYA

3 rows selected. (rowlimit reached)

Oraversion Utility in Oracle 18c New Features

A new utility Oraversion has been introduced in Oracle 18c New Features Which provides Oracle database version/release related information.

oraversion -help

This program prints release version information.

These are its possible arguments:

-compositeVersion: Print the full version number: a.b.c.d.e.

-baseVersion: Print the base version number: a.0.0.0.0.

-majorVersion: Print the major version number: a.

-buildStamp: Print the date/time associated with the build.

-buildDescription: Print a description of the build.

-help: Print this message.

$ oraversion -compositeVersion

18.3.0.0.0

 

$ oraversion -baseVersion

18.0.0.0.0

 

$ oraversion -majorVersion

18

 

$ oraversion -buildStamp

180628094320

 

$ oraversion -buildDescription

Release_Update

In Oracle 18c New Features, we can display the sql_id of the sql queries we are running using set feedback option.

By default it will be OFF.

SQL> set feedback ON SQL_ID

SQL> select name from v$database;

NAME

———

ORA18C

1 row selected.

SQL_ID: 0btb065ytg2v0 –  > This is the sql_id of the query . 

Note : Info on  Oracle 18c New Features it maybe differ in your environment like production,testing,development etc and naming conventions

THANKS FOR VIEWING MY BLOG FOR MORE UPDATES FOLLOW ME OR SUBSCRIBE ME 

Crontab Linux

Crontab Linux

Introduction:

Crontab Linux system has a useful task scheduler named crontab, to automate the scheduled to run the process as a Root, the Unix Scheduling tool, The Cron tab commands opens the cron table for editing.The cron table is the list of tasks scheduled to run at regulartime intervals on the system

The Daemon which reads the crontab and executes the commands at the right time is called Crontab Linux for instance you can automate like backup,schedule updates andsynchroniztion files and many more

In Crontab Linux Oracle DBA jobs to be managed

Analyze Database

Rman backups

Datapump jobs

Hot/cold backups

Archive log backups

House keeping jobs --Cleaning alert log/Trace files

Gathering stats

Delete old log files

Send out any notification email such as newsletter,passwords,expiration mail

Regular cleaned up of cached data

Crontab Linux is used to automate sytem mainatanace

Let us Start the process

Crontab Linux Format

MIN   HOUR   DOM  MON   DOW   CMD

Field Description     Allowed Value

MIN Minute field      0 to 59

HOUR Hour field       0 to 23

DOM Day of Month       1-31

MON Month field        1-12

DOW Day Of Week          0-6

CMD Command       Any command to be executed.

 

Asterics(*) : use for matching

Define Range : Allows you to define range with the help of hypen like 1-10 or 30 -40 or jan-mar,mon-wed

Define Multiple Ranges: Alows you to define various ranges with command separated like apr-jun,oct-dec

Crontab Linux Commands

How to Add /Modify crontab job  with the help of cron tab command

$ crontab -u -e 

To List the Crontab jobs 

$ crontab -l

To Remove the Crontab tasks 

$ crontab -r

To add or update job in crontab 

$ crontab -e

Command to edit others users crontab

$ crontab -u username -e

Command to view crontab entries of current user

$ crontab -l

Command to view crontab entries of a specific user

crontab -u username -l

Here Some of the Crontab Examples

Crontab Linux to do the various scheduling jobs. Below given command execute at 7 AM and 5 PM daily.

0 7,17 * * * /orabackup/scripts/scriptjob.sh

Command to execute a cron after every 5 minutes.

*/5* * * * * /orabackup/scripts/scriptjob.sh

Cron scheduler command helps you to execute the task on every Monday at 5 AM. This command is helpful for doing weekly tasks like system clean-up.

0 5 * * mon  /orabackup/scripts/scriptjob.sh

Command run your script on 3 minutes interval.

*/3 * * * * /orabackup/scripts/scriptjob.sh

Command to schedule a cron to which executes for a specific month. This command to run tasks run in Feb, June and September months. Sometimes we need to schedule a task to execute a select monthly task.

* * * feb,jun,sep * /orabackup/scripts/scriptjob.sh

Command to execute on selected days. This example will run each Monday and Wednesday at 5 PM.

0 17 * * mon,wed  /orabackup/scripts/scriptjob.sh

This command allows cron to execute on first Saturday of every month.

0 2 * * sat  [ $(date +%d) -le 06 ] && /orabackup/scripts/scriptjob.sh

Command to run a script for 6 hours interval so it can be configured like below.

0 */6 * * * /orabackup/scripts/scriptjob.sh

This command schedule a task to execute twice on Monday and Tuesday. Use the following settings to do it.

0 4,17 * * mon,tue /orabackup/scripts/scriptjob.sh

Command schedule a cron to execute after every 15 Seconds.

* * * * * /orabackup/scripts/scriptjob.sh

* * * * *  sleep 15; /orabackup/scripts/scriptjob.sh

Command to schedule tasks on a yearly basis.

@yearly timestamp is= to "0 0 5 1 *". This executes the task in the fifth minute of every year. You can use it to send for new year greetings.

@yearly /orabackup/scripts/scriptjob.sh

Command tasks to execute on a monthly basis.

@monthly timestamp is similar to "0 0 1 * *". This command expression allows the execution of a task in the first minute of the month.

@monthly /orabackup/scripts/scriptjob.sh

Command to execute multiple tasks using a single cron.

* * * * * /orabackup/scripts/scriptjob.sh; /orabackup/scripts/scriptjob2.sh

Command to schedule tasks to execute on a weekly basis.

@weekly timestamp is similar to "0 0 4 * sun". This is used to perform the weekly tasks like the system cleanup etc.

@weekly /bin/orabackup/scripts/scriptjob.sh

Task will be scheduled to execute on a daily basis.

@daily timestamp is similar to "0 2 * * *". It executes the task in the second minute of every day.

@daily /orabackup/scripts/scriptjob.sh

Allows tasks to execute on an hourly.

@hourly timestamp is similar to "0 * * * *". This command executes a task in the first minute of every hour.

@hourly /orabackup/scripts/scriptjob.sh

Allows tasks to execute on system reboot.

@reboot expression is useful for those tasks that the system wants to run on your system startup. This is helpful to begin tasks background automatically.

@reboot /orabackup/scripts/scriptjob.sh

we use shell scripts to create jobs

.sh /.ksh (types of shell are korn(IBM-AIX) / bash (linux c shell)

scripts in /orabackup/scripts

Sample Scenario

32  11  *  *  */orabackups/script/scriptjob.sh

create the script

$ vi scriptjob.sh

#!/bin/sh

find /archives/prod-name " *.arc" -mtime +1-exec gzip{ }\;

find /archives/prod-name " *.gz"  mtime +7 -exec rm-rf{ }\;

now save  :wq!

Give execution permission

$ chmod +x scriptjob.sh

edit the crontab

$ crontab -e

# give an entry as follows 

30  11  4  6  *  / orabackup/script/scriptjob.sh

then save it

*   *  *  *  *  * command to executed

Min  (0-59)

  Hour      (0-23)

     Day of Month  (1-31)

               month        (1-12)

       Day of the week   (0-6)  (0/7 is sunday  sunday =0 or7)

Note : Info on Crontab Linux it may be differ in yourenvironment like production,testing,development and naming conventions etc

THANKS FOR VIEWING MY BLOG FOR MORE UPDATES FOLLOW ME OR SUBSCRIBE ME