Tuesday, November 23, 2010

Data Pump monitoring

(originally published at Pythian)

During database maintenance one of the main questions is when long running processes
like export, import, backup, recovery, flashback, etc. are finished.

I usually use v$session_longops which allows to get some estimation of when process finished:

select message, time_remaining/3600 hrs from v$session_longops where sofar <> totalwork

After one of the recent Data Pump executions I tested import and export on Oracle Linux 64-bit
and found that for both export and import master processes value of message column in v$session_longops
stays something like "0 out of ... MB done" during the whole process even it is far away from the beginning.

However, there are Data Pump Worker processes that do the job and for export there is
information how many blocks scanned out of total number:

d117> @longops

---------- ---------- -------------------------------- -------------- --------------- ------------------------------------------------------------
16613 512501 Blocks 239 8 Rowid Range Scan: AG.T1: 16613 out of 512501 Blocks done

But for import I did not find any information in v$session_longops that could identify the progress
of work done by worker process.

In addition to that there some other observations seen during experiments on test database
(Oracle on Linux x86-64) with 4Gb table:

  1. Number of bytes written to export file stays ~4k up the end of the export even the file grown to several Gbytes.
  2. Percent done for worker status import set to 100% from the beginning and not changing.
  3. Number of completed bytes for import shows the size of the whole import while completed rows reflects number of processed rows.
  4. Number of processed rows for import does not necessary mean inserted rows since it is not being reset during stop_job action which rolls back all inserted to a table rows.
  5. If import stopped by stop_job and then resumed by start_job v$session_longops does not have information about import anymore.
  6. Time_remaining in v$session_longops is null for Data Pump Import master process.
  7. Short command naming can be used - stat for status, star for start_job. Shorter one - sta behaves as start_job and status in conjunction. Guess what "s" is up to ...
ORA-39004: invalid state
ORA-39016: Operation not supported when job is in EXECUTING state.
Are you sure you wish to stop this job ([yes]/no):

If there is no much useful information in v$session_longops and space for the segment
is already allocated how to get the same "rows completed" value of worker status
as in intercative mode but using sql script?

With small modification of example from the documentation I came with the PL/SQL block
to get value of one Data Pump Import worker status attributes:

ind NUMBER; -- Loop index
h1 NUMBER; -- Data Pump job handle
percent_done NUMBER; -- Percentage of job complete
job_state VARCHAR2(30); -- To keep track of job state
js ku$_JobStatus; -- The job status from get_status
ws ku$_WorkerStatusList; -- Worker status
sts ku$_Status; -- The status object returned by get_status
h1 := DBMS_DATAPUMP.attach('SYS_IMPORT_FULL_01', 'SYS'); -- job name and owner
dbms_datapump.ku$_status_job_error +
dbms_datapump.ku$_status_job_status +
dbms_datapump.ku$_status_wip, 0, job_state, sts);
js := sts.job_status;
ws := js.worker_status_list;
dbms_output.put_line('*** Job percent done = ' ||
dbms_output.put_line('restarts - '||js.restart_count);
ind := ws.first;
while ind is not null loop
dbms_output.put_line('rows completed - '||ws(ind).completed_rows);
ind := ws.next(ind);
end loop;

PL/SQL block will allow to not only monitor progress of Data Pump export/import but to perform
some actions based on results which definitely more flexible than Data Pump interactive mode.

Have a good day!

Monday, November 1, 2010

installer downloadUpdates option

(originally published at Pythian)

The new functionality to download latest patches or either apply saved ones
came with installer of Oracle (Software Updates Option)

It can be used not only through OUI but with silent installation too.
There are several options and variables available to get patches from MOS:

Putting it all together I got the following selection of options and parameters that was started and
successfully completed (operations and their status were logged to logs under oracle inventory):

./runInstaller -silent -responseFile /home/oracle/install/patchset1/database/response/db_install.rsp \
-downloadUpdates \
oracle.installer.autoupdates.option=MYORACLESUPPORT_DOWNLOAD \

There was nothing displayed for that execution but in log file there were following entries:

INFO: Retrieving list of updates
INFO: UpdateManager: retrieving value for key: UPDATE_VERSION
INFO: UpdateManager: retrieving value for key: UPDATE_VERSION value:
INFO: UpdateManager: retrieving value for key: UPDATE_BUGID
INFO: UpdateManager: retrieving value for key: UPDATE_BUGID value: 9794229
INFO: Connection to metalink was successful
INFO: No of components in db install : 136

Search on MOS found patch 9794229 called "PLACEHOLDER BUG FOR METADATA XML IN 11202"
which is just a container for possible critical and security patches in the future.
(Worth to note that after recent upgrade MOS exposed very helpful feature to get wget
script to download patches. You will see it in download box when you click on download button)

There is also option of installer to apply already downloaded and saved patches:


To try the option I downloaded one of the patches for (there are several dozens of them already),
put it under /home/oracle/install/patches, unpacked it there and executed silent installation
without previously used AUTOUPDATES_MYORACLESUPPORT_...:

./runInstaller -silent -responseFile /home/oracle/install/patchset1/database/response/db_install.rsp \
oracle.installer.autoupdates.option=OFFLINE_UPDATES \

but it failed with error:"[SEVERE] - My Oracle Support Username/Email Address Not Specified"

and executed it once more:

./runInstaller -silent -responseFile /home/oracle/install/patchset1/database/response/db_install.rsp \
oracle.installer.autoupdates.option=OFFLINE_UPDATES \

and another error appeared:
CAUSE: The location provided is not in the expected directory structure.
ACTION: For auto-updates to work in offline mode,
the location of the downloaded updates should be in a particular format.
Please refer to the doc for details.

The same happened when I executed OUI and chose the same option there.

Unfortunately, search in Oracle documentation had not returned anything but MOS
returned one document related to auto updates in Grid Control (ID 1099123.1) where similar
auto updates feature is used for Grid Control and there is patches.xml file that has all information about
patches and to what product they should be applied.

I copied patch 9794229 "PLACEHOLDER BUG FOR METADATA XML IN 11202" downloaded before and
to /home/oracle/install/patches and unzipped there but installer failed again even I got patches.xml file under the location.

Even there was no success for now to apply already downloaded patches I believe it will be revealed soon
since Auto Update feature can be useful especially when there is no any CPU yet
and installer can execute patches that you decided are applicable for your database environment.

Have a good day!