Oracle DBA Lite: an introduction to Oracle administration, part 7

Uploaded by roughsealtd on 20.01.2011

When you’re in charge of a production database, many tasks are repetitive and a
large part of the job consists in automating these tasks,
and scheduling them. Oracle comes with its own scheduler, which you manage
with procedures contained in a package called
dbms_scheduler. It is associated with many dictionary views that you can list
by looking in the dictionary view for everything that is named DBA_SCHED something.
I am going to show you how to simply use the Oracle scheduler, but you may want
to read more. Like everything Oracle, it has lots of features – and can be complicated.
With dbms_scheduler you associate a program to run with a time frequency, and the
association is called a job. I won’t talk about it, but instead of a time frequency
you can trigger program execution on
events, such as the apparition of a file in a directory, or
dependencies, which means running a program after another one that has succeeded, or perhaps
Why use the Oracle scheduler rather than the operating system scheduler? If you are administering
Oracle both on Linux and Windows systems, it will be more consistent. But more importantly,
if the database is down for any reason, an externally scheduled database task will fail,
and may be skipped depending on how it was coded. With the internal scheduler, it will
run when possible.
You can take an action and create a program by assigning it a name,
you can take a time and a frequency and create a schedule by assigning it a name,
and you can use the named program and the named schedule to create a job.
Or you can directly associate an action to a job, or time and a frequency, in any combination.
As usual, it’s a matter of reusability and flexibility – if you refer to something
that is used at multiple places by an alias, which is exactly
what are both a defined program and a defined schedule, you can change them more easily.
For DBA tasks, usually the programs aren’t called from many places, but you may have
to shift when they are run. This is why I’ll directly
associate actions to jobs, but I shall create schedules.
When you install Oracle Express, it comes with some pre-scheduled tasks.
You can find them by checking view dba_scheduler_job_log, which records jobs that have actually run.
There are two jobs.
GATHER_STATS_JOB collects information such as the number of rows in tables or the selectivity
of indexes that is used by the query optimizer to determine how best to run a query.
PURGE_LOG actually purges the very same table we have been querying.
JOB_CLASS isn’t something I’ll talk about; it’s mostly a way to assign a lower priority
to maintenance tasks that run in the background.
But the main view to query is actually dba_scheduler_jobs, where you can find the program name
and the schedule name, if both a program and a schedule were defined.
I have added a condition on STATE to my query because you’ll notice that there are some
other inactive jobs that are defined – they are related to Enterprise Edition options.
You find schedules in dba_scheduler_schedules, and you see here that DAILY_PURGE_SCHEDULE
is an alias for firing something every day at
3 am.
Just like having an alarm clock.
But it brings an interesting question because if you remember the query on dba_scheduler_jobs
the schedule name for GATHER_STATS_JOB was given as MAINTENANCE_WINDOWS_GROUP. There
is no such thing in the view that lists schedules.
Windows and windows groups are a different notion. You notice the WINDOWS_GROUP that
incites us to query the view called dba_scheduler_wingroup_members.
The group is composed of two windows, weeknight_window and weekend_window.
The gory detail is to be found in dba_scheduler_windows.
Weeknight_window starts at 10pm from Monday to Friday, and it extends for 8 hours.
Weekend_window starts at midnight on Friday, and it lasts 2 days.
So, to try to visualize everything,
the week-night window starts every evening of the week, and extends into the following
morning until 6am.
The week-end window covers Saturday and Sunday, and we mustn’t forget that we have
daily_purge_schedule which is an alarm at 3am every morning.
At the start of every defined window, the scheduler looks for tasks to run in that window,
and dynamically creates a temporary schedule. Like with job classes, you can also play on
priorities with windows, which are designed for long running tasks.
Now, let’s check the action associated with program PURGE_LOG_PROG.
It’s a procedure from the dbms_scheduler package.
But if you take a look at the package documentation, you won’t find it. AUTO_PURGE isn’t documented.
However, if you check the documentation for the purge procedure, you’ll find some information.
Navigating the Oracle documentation is an important DBA skill...
Unless you want to click on the "backup database" option in the menu every evening before going
back home, you’ll probably want to schedule backups and that’s what I’m going to show
you now.
There are some differences between Linux and Windows, let’s start with Linux.
The first thing to do is connect as oracle and create a directory for your own scripts
Then copy the provided backup script to this directory, because you need to modify it.
The first change required is to define HOME, that is only used as the location of the logfile.
If the scheduler runs the script, the environment variable will not be set so you must set it
to a location that is writable by user oracle.
The second change is that you must remove anything that asks for user interaction.
There are a number of "read" commands in the script. When something such as Y or N is expected,
decide on whatever looks reasonable to you but nobody will be there to answer any question.
Once you have modified the script, connect as user SYSTEM – it’s important, because
as SYS you cannot run an external program.
I am going to create first a schedule by calling procedure CREATE_SCHEDULE, first giving a
name to the schedule,
then specifying the first time when it fires, trunc() applied to sysdate plus one gives
me midnight today, and if I add one sixth of
a day it will be 4am,
and I will repeat it every day at 4am – for testing purposes you may want to set
something else.
Then I call CREATE_JOB, assign a name to the job, and associate it to the schedule I have
just created, say that the type is EXECUTABLE, which means that it is an external program
as opposed to a stored procedure, and give as job_action the full path to the modified
backup script.
One thing that is important is that you must say that enabled is TRUE, otherwise the job
will be created but not activated.
And you’re done.
If you’re impatient to see what it gives you can run the job immediately by calling
the RUN_JOB procedure.
You can check that everything is ready by querying dba_scheduler_jobs, checking when
all scheduled jobs are expected to run next.
If you are running this in a sqlplus console, you may want to limit the width of the time
Tomorrow morning (or now if you have just run the job) you can check that it went smoothly
by checking dba_scheduler_job_log, and I’m
going to limit to what happened today.
Some formatting will make it look better if you are running the query under sqlplus.
If the status shows failure, you need to query dba_scheduler_job_run_details, error messages
appear in column additional_info and I am going to rank messages most recent one first
for each job or sub-job.
I wrap my query in another query so as only to display the most recent failure message

as you can see, good SQL skills are also helpful when you are a DBA.
Now for Windows.
The first, and very important, step is to start the service OracleJobSchedulerXE which
isn’t started by default. Without it, nothing will work.
Then it’s like with Linux, you should copy the backup script, backup.bat that is under
BIN, to a different directory because you also
need to modify it.
Look for the set /p and pause commands, and remove them, assuming whichever you think
appropriate where you are supposed to provide an answer.
You need to understand the script to modify it safely.
Then I’m going to do something a bit different from before. I have told you that when you
take a backup you should save it elsewhere in case the server fails.
’m going to write a very simple Powershell script that first runs the backup script,
then gets and formats the date,
then zips everything that was created today and writes it to drive G, which I assume
o be a network drive.
Once again, backup.bat here is the suitably modified script, which needs no user interaction.
Then I save my script as backup_and_save.ps1.
Once again I am going to schedule the backup as user SYSTEM.
When I create the schedule, it’s exactly the same statement as I used under Linux,
as it is pure Oracle syntax.
The difference is when I create the job, because what I actually run isn’t backup_and_save.ps1,
but powershell that is given the script as argument.
It would be similar with a .bat script, except that in that case I would need to invoke cmd.exe.
I cannot say that the action is a program with its list of arguments – I must specify
separately program and arguments.
What I need to say is that my program will take one argument,
and I must also set enabled to FALSE because I cannot enable a job as long as all the
arguments for the action or program aren’t specified.
Then I set the argument value by specifying the job name, the position of the argument,
and the argument value, in that case the path to my .ps1 file.
Then I can enable the job and I’m done.
Everything else works the same as previously.
I have said at the end of the third video in this series that it is important to be
able to monitor and predict growth trends, and
I am going to show you how you can do it.
You have seen that Oracle collects statistics every evening by default, and very importantly
it computes, or sometimes estimates, the number of rows in every table, overwriting the
previous value. At the same time, by querying dictionary views
to check storage allocation, you can get the number
of Kilobytes (or megabytes, if your tables are big) used by a table and its associated
You can save this to a table,
and with help from the scheduler save it regularly.
That will tell you how space relates to say a number of customers or a number of registered
users, will help you monitor past growth and discuss
trends with figures that are meaningful for people
who know the business, but not Oracle.
The first thing you need to do is connect as SYS
and grant to user SYSTEM the SELECT privilege on dba_tables, dba_indexes and dba_segments;
I assume for the sake of simplicity that you aren’t using large objects (or LOBs) in
your tables.
I am going to create a stored procedure that queries these views and even when I am a DBA
I need the privileges to be granted directly to my account to refer to table or views in
a stored procedure, even if I can access these
tables and views.
Then I am going to create, as user SYSTEM, a table to store a date, an owner, a table
name, a number of rows, the size of data proper
and the size of indexes.
I create this table in tablespace sysaux, since this isn’t user data.
Next, I write a query that retrieves from DBA_TABLES and DBA_SEGMENTS the identification
of a table, its number of rows and the size of the associated
data segment, and I choose to exclude schemas SYS and SYSTEM
– you are free to select what you want to include or exclude.
This is the first part of the query,
in the second part I get the size information for the indexes that have been created on
the table.
This will result in one row for each table with its size and number of rows,
and one row with each index and its size.
Beware that this is a relatively simple query and if you are using some advanced storage
options it may need some rewrite.
Then I am going to use the previous query,
use it in a FROM clause,
and sum up all the sizes, so as to have a single row per table.
I insert the result of this query into the table I have just created,
I wrap the insert statement into a procedure and I am ready to schedule it.
This time I don’t create any schedule, I specify everything in the CREATE_JOB call,
giving the job_type as STORED_PROCEDURE and scheduling it for every Sunday,
starting next Sunday, at 6am. No need to check sizes every day, I’m interested by long
term trends.
What else?
You can monitor performance, once again using the scheduler to regularly collect information.
Just watch my two-part video on Oracle Performance Monitoring, it explains how to set it up.
This ends this series on Oracle administration.
There are many, many other things to Oracle administration, but I have presented what
I think is really important, and if you have well
understood the concepts, with a little practice
the three of you who have watched all the videos in this series will probably become
honorable junior DBAs.
Read the docs,
hone your SQL and scripting skills,
experiment, try variations on what I have shown, have fun,
and thank you for your attention.