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 11.1.0.7 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

SOFAR TOTALWORK UNITS TIME_REMAINING ELAPSED_SECONDS MESSAGE
---------- ---------- -------------------------------- -------------- --------------- ------------------------------------------------------------
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 11.1.0.7 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:


DECLARE
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
BEGIN
h1 := DBMS_DATAPUMP.attach('SYS_IMPORT_FULL_01', 'SYS'); -- job name and owner
dbms_datapump.get_status(h1,
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 = ' ||
to_char(js.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;
DBMS_DATAPUMP.detach(h1);
end;
/

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 11.2.0.2 downloadUpdates option

(originally published at Pythian)

The new functionality to download latest patches or either apply saved ones
came with installer of Oracle 11.2.0.2 (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:
oracle.installer.autoupdates...
MYORACLESUPPORT_...
AUTOUPDATES_MYORACLESUPPORT_...

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 \
AUTOUPDATES_MYORACLESUPPORT_USERNAME=... \
AUTOUPDATES_MYORACLESUPPORT_PASSWORD=... \
oracle.installer.autoupdates.option=MYORACLESUPPORT_DOWNLOAD \
oracle.installer.autoupdates.downloadUpdatesLoc=/tmp/updates

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: 11.2.0.2.0
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:

oracle.installer.autoupdates.option=OFFLINE_UPDATES
oracle.installer.autoupdates.downloadUpdatesLoc=/tmp/updates

To try the option I downloaded one of the patches for 11.2.0.2 (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 \
AUTOUPDATES_MYORACLESUPPORT_USERNAME=... \
AUTOUPDATES_MYORACLESUPPORT_PASSWORD=... \
oracle.installer.autoupdates.option=OFFLINE_UPDATES \
oracle.installer.autoupdates.downloadUpdatesLoc=/home/oracle/install/patches

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

I replaced AUTOUPDATES_MYORACLESUPPORT_... with MYORACLESUPPORT_... parameters
and executed it once more:

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

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!

Monday, October 25, 2010

chopt utility

(originally published at Pythian)
There is a new tool came with Oracle 11g Rel.2 that helps to modify options in
installed oracle home (Enabling and Disabling Database Options)
It is very simple and straightforward utility that recompiles database kernel
with different flags.
[oracle@r1 bin]$ chopt
usage:
chopt <enable|disable> <option>
options:
dm = Oracle Data Mining RDBMS Files
dv = Oracle Database Vault option
lbac = Oracle Label Security
olap = Oracle OLAP
partitioning = Oracle Partitioning
rat = Oracle Real Application Testing
e.g. chopt enable rat

There is no "list" command that can show installed options for the selected home
although it can be helpful and useful to have such option.

Initially enabled options can possibly be seen in $ORACLE_HOME/install/make.log file
(flags were set as compilation options for ins_rdbms.mk) but it can be changed over time
and not always relfect the current configuration.

Knowing that there is XML file for properties of oracle home wouldn't be
$ORACLE_HOME/inventory/ContentsXML/oraclehomeproperties.xml
a good place to track list of oracle home configured options?

And finally the tool does not take into account several options at once, it simply ignores
all parameter after the second one:

[oracle@r1 bin]$ chopt enable dm dv

Writing to /u01/app/oracle/product/11.2.0/dbhome_1/install/enable_dm.log...
/usr/bin/make -f /u01/app/oracle/product/11.2.0/dbhome_1/rdbms/lib/ins_rdbms.mk dm_on
ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1
/usr/bin/make -f /u01/app/oracle/product/11.2.0/dbhome_1/rdbms/lib/ins_rdbms.mk ioracle
ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1/
[oracle@r1 bin]$


Have a good day!

Thursday, October 7, 2010

Redo Transport Compression

originally published at Pythian

I was co-presenting recently at Sydney Oracle Meetup
about Data Guard Compression and in preparation I did
some tests to see how it works for 11.2.0.1 (Linux x86-64)

...and I am still not able to see it working.

There is a note
"Redo Transport Compression in a Data Guard Environment [ID 729551.1]"
on MOS how to set compression for redo transport destination
and for log archive gaps but none of those settings helped to see compression
reported neither in trace files nor in v$archived_log view (compressed column).

Possibly at first compression for archive logs was mentioned in the
"Archivelog compression?" post but even "alter database archivelog compress enable"
works in 11.2.0.1 (still not documented)
it does not affect compression of archive logs although archivelog_compression in
v$database changed to ENABLED.

Later on I checked ability of alter database... to influence compression of archive logs
in 11.1.0.6 and 11.1.0.7 (Linux x86-64) and got positive results:

in both versions archive logs of 50M were decreased in size to ~10M
and trace file for ARCH process was updated with information about ratio
of compression:
Archivelog compression complete.
Input: 50969088 bytes Output: 10307875 bytes
Compression Performance: 79.78 percent or 1.62 bits per byte


But for 11g Rel.2 database created from predefined templates compression did not work.
Does it require some special additional options or specific hidden parameters configuration
in addition to mentioned in the note COMPRESSION=ENABLE and "_redo_transport_compress_all"
(which by default set to TRUE)?

Definitely truth is out there...

Have a good day!

Saturday, June 5, 2010

Silent installation of Enteprise Manager Grid Control 11g

(originally posted at Pythian)
One of the first questions that I asked myself when EM GC 11g came out is how would a silent
installation go especially taking into account the use of WebLogic server as the http server.

But before any attempt of silent installation I started the GC 11g GUI installation to local VM machine
(Oracle Enterprise Linux 5.4 64-bit) with an allocated 2Gb of memory and got errors since
WebLogic server was not installed there.

I downloaded it from edelivery.oracle.com and unpacked zip file - got one jar file! Not bad at all!
But where are all those runInstaller, stage, & other directories? There was not even a sample silent
response file. This is where documentation is helpful, and based on Creating a Silent Installation,
I created response file silent.xml




and tried to start installation but got an error that java should be at least 1.6 version.
After installation of JRE out of jre-6u20-linux-x64-rpm.bin I changed the symbolic link
and pointed new version java to /usr/bin/java and completed silent installation of
WebLogic Server 10.3.0.2:

java -jar wls1032_generic.jar -mode=silent -silent_xml=silent.xml -log=install.log
Extracting 0%.............................................................................100%
May 6, 2010 9:33:34 PM java.util.prefs.FileSystemPreferences$2 run
INFO: Created user preferences directory.

Intstalling Grid Control requires running a database where the repository will be created.
Oracle RDBMS 11.2 was installed silently as well and that installation is even more silent
than previous versions since there is no progress bar now for 11.2.


./runInstaller -silent \
-responseFile /home/oracle/install/db112/database/response/db_install.rsp \
oracle.install.option=INSTALL_DB_SWONLY \
oracle.install.db.InstallEdition=EE \
security_updates_via_myoraclesupport=false \
decline_security_updates=true \
oracle.install.db.DBA_GROUP=dba \
oracle.install.db.OPER_GROUP=dba \
ORACLE_BASE="/u01/app/oracle" \
ORACLE_HOME="/u01/app/oracle/product/11.2.0/dbhome_1"
Starting Oracle Universal Installer...


Database emrep was created on file system taking into account database parameter requirements
for the repository database at Setting Database Initialization Parameters

./dbca -silent -createDatabase -gdbName emrep -templateName General_Purpose.dbc \
-emConfiguration none -datafileDestination /u01/oradata \
-sysPassword sys_pwd -systemPassword sys_pwd -storageType FS \
-initParams filesystemio_options=setall,job_queue_processes=10,log_buffer=10485760,\
open_cursors=300,processes=500,session_cached_cursors=200 \
-totalMemory 600


One pre-requisite is to have undo tablespace not less than 200Mb. I increased the size of the datafile, followed with a database control deletion from the created database:

emca -deconfig dbcontrol db -repos drop -silent -SYS_PWD sys_pwd -SYSMAN_PWD sys_pwd -SID emrep -PORT 1521

Before proceeding with further GC silent installation I checked Prerequisites
and recognized that "soft nofile" limits for an oracle user are required to be set to 4096 instead of 1024
recommended for oracle 11.2 RDBMS installation.
After change of kernel parameters I put values to response file for silent installation of Grid Control 11:

RESPONSEFILE_VERSION=2.2.1.0.0
SECURITY_UPDATES_VIA_MYORACLESUPPORT=false
DECLINE_SECURITY_UPDATES=true
INSTALL_UPDATES_SELECTION="skip"
ORACLE_MIDDLEWARE_HOME_LOCATION=/u01/app/oracle/product/gc11
ORACLE_INSTANCE_HOME_LOCATION=/u01/app/oracle/product/gc11/gc_inst
WLS_ADMIN_SERVER_USERNAME=weblogic
WLS_ADMIN_SERVER_PASSWORD=Mon10day10
WLS_ADMIN_SERVER_CONFIRM_PASSWORD=Mon10day10
NODE_MANAGER_PASSWORD=Mon10day10
NODE_MANAGER_CONFIRM_PASSWORD=Mon10day10
DATABASE_HOSTNAME="oel7.pythian"
LISTENER_PORT="1521"
SERVICENAME_OR_SID="emrep"
SYS_PASSWORD="sys_pwd"
MANAGEMENT_TABLESPACE_LOCATION="/u01/oradata/emrep/mgmt.dbf"
CONFIGURATION_DATA_TABLESPACE_LOCATION="/u01/oradata/emrep/mgmt_ecm_depot1.dbf"
JVM_DIAGNOSTICS_TABLESPACE_LOCATION="/u01/oradata/emrep/mgmt_ad4j.dbf"
START_OMS=true
SYSMAN_PASSWORD=Mon10day10
SYSMAN_CONFIRM_PASSWORD=Mon10day10
ALLOW_ONLY_SECURE_ACCESS_TO_CONSOLE=true
AGENT_REGISTRATION_PASSWORD=Mon10day10
AGENT_REGISTRATION_CONFIRM_PASSWORD=Mon10day10
FROM_LOCATION="../oms/Disk1/stage/products.xml"
DEINSTALL_LIST={"oracle.sysman.top.oms","11.1.0.1.0"}
TOPLEVEL_COMPONENT={"oracle.sysman.top.oms","11.1.0.1.0"}
COMPONENT_LANGUAGES={"en"}
b_upgrade=false
EM_INSTALL_TYPE="NOSEED"


Installation ran almost all the way through but failed at OMS Configuration Assistant step:

Running Configuration assistant "OMS Configuration"
Executing the OMSCA command...
Check the OMS Configuration Assistant logs at: /u01/app/oracle/product/gc11/oms11g/cfgtoollogs/omsca
Configuration assistant "OMS Configuration" Failed


In the latest log in /u01/app/oracle/product/gc11/oms11g/cfgtoollogs/cfgfw directory
I found the reason of the failure:

Check the OMS Configuration Assistant logs at:
/u01/app/oracle/product/gc11/oms11g/cfgtoollogs/omsca
INFO: oracle.sysman.top.oms:/u01/app/oracle/product/gc11/oms11g/bin/omsca:
line 40: /bin/java: No such file or directory INFO: oracle.sysman.top.oms:
Check the OMS Configuration Assistant logs at:
/u01/app/oracle/product/gc11/oms11g/cfgtoollogs/omsca


Further check of omsca code unveiled use of WebLogic configuration script
/u01/app/oracle/product/gc11/wlserver_10.3/common/bin/commEnv.sh
where there was code block responsible for JAVA_HOME and JAVA_VENDOR variables.

I simply put values for the variables to the script:
JAVA_HOME="/usr/java/jre1.6.0_20"
JAVA_VENDOR="Sun"


# Reset JAVA_HOME, JAVA_VENDOR and PRODUCTION_MODE unless JAVA_HOME
# and JAVA_VENDOR are pre-defined.
if [ -z "${JAVA_HOME}" -o -z "${JAVA_VENDOR}" ]; then
# Set up JAVA HOME
JAVA_HOME=""
# Set up JAVA VENDOR, possible values are
#Oracle, HP, IBM, Sun ...
JAVA_VENDOR=
# PRODUCTION_MODE, default to the development mode
PRODUCTION_MODE=""
fi


I reinstalled WebLogic, recreated database, removed entries from inventory.xml and started installation again.
It was finished successfully and I completed it with execution of allroot.sh script under root
from /u01/app/oracle/product/gc11/oms11g

Have a good day!