Shell script to export all APEX applications as SQL scripts

Environment: Oracle 11gR2 EE, Application Express 4.2.2, Oracle Linux 6.4 64-bit, Java JDK 7u21

The APEX software contains a Java class which can be used to script or schedule the export of one or more APEX applications. I will show you in this post how to do this.

First, I unzipped the APEX software to /u01/app/apex. The Java class file is called “APEXExport.class” and can be found in the folder /u01/app/apex/utilities/oracle/apex.

You also need a Java JDK. I installed Java 7 JDK (update 21) under /u01/app/java/java7.

You might need to unlock (and set a password) for the APEX schema owner (in my case, APEX_040200). I’m not sure if it’s possible to export the applications with another user.

This is how my script looks like:

#!/bin/sh
# script to export all ApEx applications as sql scripts

CLASSPATH=$CLASSPATH:/u01/app/oracle/product/11.2.0/db_1/oui/jlib/classes12.jar; export CLASSPATH
BACKUPDIR=/u05/apex_backup

cd /u01/app/apex/utilities

/u01/app/java/java7/bin/java oracle.apex.APEXExport -db localhost:1521:ora11g01 -user apex_040200 -password <password> -instance

mv f*.sql $BACKUPDIR

if [ -f $BACKUPDIR/apex_export.log ]
then
    cat $BACKUPDIR/apex_export.log|mail -s "APEX export - `hostname -s`" matthiash@mydomain.com
fi

You need to include the file classes12.jar in your CLASSPATH or the JDBC connection won’t work.

The -instance setting will export all your applications. To limit the number of applications, you can also use -workspaceid or -applicationid. Check the readme.txt file under the utilities folder for more information.

To schedule the script with cron, I added the following line to my crontab file:

00 22 * * * /home/oracle/scripts/apex_export.sh 1>/u05/apex_backup/apex_export.log 2>&1

This will execute the script each night at 22:00 and mail the log file to the mail address inside the script.

Happy scripting! :-)

Matthias

Advertisements

Configuration of HugePages for Oracle database 11.2.0.3 on Oracle Linux 6.4

Environment: Oracle database 11.2.0.3 Enterprise Edition, Oracle Linux 6.4 64-bit

HugePages can give a performance boost on Linux systems with large SGA sizes. However, this is not set up by default, and the configuration can sometimes be a bit tricky.

This is how I did it on a system with a 4GB SGA size:

1) Disable Automatic Memory Management (AMM)

AMM is not compatible with HugePages. I disabled it by setting the following memory-related initialization parameters:

ALTER SYSTEM SET memory_max_target=0 SCOPE=SPFILE;

ALTER SYSTEM SET memory_target=0 SCOPE=BOTH;

ALTER SYSTEM SET sga_max_size=4016M SCOPE=SPFILE;

ALTER SYSTEM SET sga_target=4016M SCOPE=BOTH;

+ restart the instance

2) Calculate the number of HugePages needed

The size of one HugePage can be found as follows:

$ cat /proc/meminfo|grep Hugepagesize
Hugepagesize: 2048 kB

The amount of HugePages that you need can be found with the following script:

#!/bin/bash
#
# hugepages_settings.sh
#
# Linux bash script to compute values for the
# recommended HugePages/HugeTLB configuration
#
# Note: This script does calculation for all shared memory
# segments available when the script is run, no matter it
# is an Oracle RDBMS shared memory segment or not.
# Check for the kernel version
KERN=`uname -r | awk -F. '{ printf("%d.%d\n",$1,$2); }'`
# Find out the HugePage size
HPG_SZ=`grep Hugepagesize /proc/meminfo | awk {'print $2'}`
# Start from 1 pages to be on the safe side and guarantee 1 free HugePage
NUM_PG=1
# Cumulative number of pages required to handle the running shared memory segments
for SEG_BYTES in `ipcs -m | awk {'print $5'} | grep "[0-9][0-9]*"`
do
   MIN_PG=`echo "$SEG_BYTES/($HPG_SZ*1024)" | bc -q`
   if [ $MIN_PG -gt 0 ]; then
      NUM_PG=`echo "$NUM_PG+$MIN_PG+1" | bc -q`
   fi
done
# Finish with results
case $KERN in
   '2.4') HUGETLB_POOL=`echo "$NUM_PG*$HPG_SZ/1024" | bc -q`;
          echo "Recommended setting: vm.hugetlb_pool = $HUGETLB_POOL" ;;
   '2.6') echo "Recommended setting: vm.nr_hugepages = $NUM_PG" ;;
    *) echo "Unrecognized kernel version $KERN. Exiting." ;;
esac
# End

$ ./hugepages_settings.sh
Recommended setting: vm.nr_hugepages = 2013

3) Set the number of HugePages in /etc/sysctl.conf

Add the following to /etc/sysctl.conf:

vm.nr_hugepages = 2013

4) Set the memory limits for user “oracle”

The memory limits for user “oracle” can be calculated by multiplying the number of HugePages with the Hugepagesize (so, in my case, 2013*2048 = 4122624).

Add this number to /etc/security/limits.conf:

oracle soft memlock 4122624
oracle hard memlock 4122624

5) Set USE_LARGE_PAGES

By setting the initialization parameter USE_LARGE_PAGES, you can force the instance to only start up when enough HugePages are available.

ALTER SYSTEM SET USE_LARGE_PAGES=only SCOPE=SPFILE;

6) Reboot the system

Now reboot the server. You can check if large pages are being used in 2 ways:

$ cat /proc/meminfo|grep HugePages

AnonHugePages: 237568 kB
HugePages_Total: 2013
HugePages_Free: 1532
HugePages_Rsvd: 1528
HugePages_Surp: 0

Or check the alert.log file for the database instance:

Starting ORACLE instance (normal)
****************** Large Pages Information *****************
Parameter use_large_pages = ONLY

Total Shared Global Region in Large Pages = 4018 MB (100%)

Large Pages used by this instance: 2009 (4018 MB)
Large Pages unused system wide = 4 (8192 KB) (alloc incr 16 MB)
Large Pages configured system wide = 2013 (4026 MB)
Large Page size = 2048 KB
***********************************************************

Good luck! :-)

 

Matthias

Oracle 11g Flashback examples

Environment: Oracle database 11.2.0.3.0 Enterprise Edition.

This is a quick overview (mostly for myself ;-)) of the flashback features that are available with Oracle 11g.

— query table data from specific point in time

SELECT * FROM matthiash
AS OF TIMESTAMP
TO_TIMESTAMP('2013-05-03 14:00:00', 'YYYY-MM-DD HH24:MI:SS');

— query table data from one hour ago

SELECT * FROM matthiash
AS OF TIMESTAMP
(SYSTIMESTAMP - INTERVAL '60' MINUTE);

— query all versions of one or more records

SELECT versions_startscn, versions_starttime,
versions_endscn, versions_endtime,
versions_xid, versions_operation,m_id
FROM matthiash
VERSIONS BETWEEN TIMESTAMP
TO_TIMESTAMP('2013-05-03 13:00:00', 'YYYY-MM-DD HH24:MI:SS')
AND TO_TIMESTAMP('2013-05-03 14:45:00', 'YYYY-MM-DD HH24:MI:SS')

— get the user name of the user who modified records

GRANT SELECT ANY TRANSACTION TO matthiash;

SELECT xid, logon_user
FROM flashback_transaction_query
WHERE xid IN (
SELECT versions_xid FROM matthiash VERSIONS BETWEEN TIMESTAMP
TO_TIMESTAMP('2013-05-03 13:00:00', 'YYYY-MM-DD HH24:MI:SS')
AND TO_TIMESTAMP('2013-05-03 14:45:00', 'YYYY-MM-DD HH24:MI:SS')
)
AND xid = HEXTORAW('08000800C6070000');

— flashback entire table to specific point in time

ALTER TABLE matthiash ENABLE ROW MOVEMENT

FLASHBACK TABLE matthiash
TO TIMESTAMP TO_TIMESTAMP('2013-05-03 13:00:00', 'YYYY-MM-DD HH24:MI:SS');

— creating restore point and flashback table to restore point

CREATE RESTORE POINT before_table_update;

SELECT NAME, SCN, TIME
FROM V$RESTORE_POINT;

UPDATE matthiash SET m_id=0;

FLASHBACK TABLE matthiash.matthiash
TO RESTORE POINT before_table_update;

— recover dropped table from recycle bin (note: does not work for all types of tables)

DROP TABLE matthiash;

SELECT * FROM recyclebin;

FLASHBACK TABLE matthiash TO BEFORE DROP;

FLASHBACK TABLE "BIN$29FWNr5ICjbgQ68BEqzYLw==$0" TO BEFORE DROP RENAME TO matthiash;

— recover entire database to restore point, SCN or point in time

CREATE RESTORE POINT before_table_drop GUARANTEE FLASHBACK DATABASE;

DROP TABLE matthiash PURGE;

SHUTDOWN IMMEDIATE;
STARTUP MOUNT;

FLASHBACK DATABASE TO RESTORE POINT BEFORE_TABLE_DROP;

FLASHBACK DATABASE TO SCN 46963;

FLASHBACK DATABASE TO TIME "TO_TIMESTAMP('2013-05-03 15:00:00', 'YYYY-MM-DD HH24:MI:SS')";

ALTER DATABASE OPEN RESETLOGS;

Matthias

Measuring Oracle I/O performance using DBMS_RESOURCE_MANAGER.CALIBRATE_IO

Environment: Oracle database 11.2.0.3.0, Oracle Linux 6.4

Oracle 11g has a procedure named “CALIBRATE_IO” which you can use to measure the I/O performance of your Oracle system. You can use this procedure to evaluate disk subsystem layout or configuration changes.

The procedure is part of the DBMS_RESOURCE_MANAGER PL/SQL package. Only users with the SYSDBA privilege can execute this procedure. You also need to enable ASYNCH_IO for your datafiles.

Note: do not run this on a live production system. For optimal results, use this on a quiet system with no other users connected.

This is an example with user SYS as SYSDBA (you have to enable DBMS_OUTPUT to see the results):

DECLARE
  lat  INTEGER;
  iops INTEGER;
  mbps INTEGER;
BEGIN
  DBMS_RESOURCE_MANAGER.CALIBRATE_IO(
      1 /* # of disks */
      , 10 /* maximum tolerable latency in milliseconds */
      , iops /* I/O rate per second */
      , mbps /* throughput, MB per second */
      , lat  /* actual latency in milliseconds */
  );
  DBMS_OUTPUT.PUT_LINE('max_iops = ' || iops);
  DBMS_OUTPUT.PUT_LINE('latency  = ' || lat);
  DBMS_OUTPUT.PUT_LINE('max_mbps = ' || mbps);
END;
/

On my system, the total execution time was about 8 minutes.
The output is as follows:

max_iops = 2588
latency = 8
max_mbps = 241

The results can also be found in the system table DBA_RSRC_IO_CALIBRATE.

HTH,
Matthias

OPatch warnings after installation of PSU 11.2.0.3.6 on Oracle 11.2.0.3.0

Environment: Oracle Database 11.2.0.3.0, Oracle Linux 6.4, OPatch v. 11.2.0.3.4

I was installing Patch Set Update 11.2.0.3.6 (as part of the CPU for April 2013) on a freshly installed Oracle 11.2.0.3.0 database, when I noticed the following warnings in the OPatch log file:

OPatch found the word “warning” in the stderr of the make command.
Please look at this stderr. You can re-run this make command.
Stderr output:
ins_precomp.mk:19: warning: overriding commands for target `pcscfg.cfg’
/u01/app/oracle/product/11.2.0/db_1/precomp/lib/env_precomp.mk:2160: warning: ignoring old commands for target `pcscfg.cfg’
/u01/app/oracle/product/11.2.0/db_1/precomp/lib/ins_precomp.mk:19: warning: overriding commands for target `pcscfg.cfg’
/u01/app/oracle/product/11.2.0/db_1/precomp/lib/env_precomp.mk:2160: warning: ignoring old commands for target `pcscfg.cfg’

The database seemed fine, though. I contacted Oracle support just in case, and it was confirmed as a known issue. The document with ID 1448337.1 has more information on it. So basically, these warnings can be safely ignored…

 

Matthias

 

Upgrade APEX 4.1.1 to 4.2.1 on Oracle Linux with GlassFish

Environment: Oracle Linux 6.3, Oracle database 11.2.0.3.0 EE, APEX 4.1.1.00.23, GlassFish 3.1.2.2

This blog post explains how you can upgrade your Application Express installation from version 4.1.1 to version 4.2.1, with the APEX Listener deployed under GlassFish.

Steps:

1) download the software

You can download APEX 4.2.1 here. I downloaded the “all languages” zip file. Please note that the documentation is no longer included with the software. You can download the documentation separately here.

2) copy and extract the software

Now copy and extract the apex_4.2.1.zip file to your server. I use “unzip” to unzip the zip file. This will create a folder named “apex”. I usually keep this folder on my server in a permanent location, for example /u01/app/apex.

3) check the tablespace requirements

You need about 275MB of free space in the APEX tablespace (mine is called “APEX”) and 100MB of free space in the SYSTEM tablespace.

4) stop the APEX Listener

Before you start with the upgrade, you need to stop the APEX Listener to make sure no one is using the software during the upgrade.

To stop the APEX Listener under GlassFish, you can do it as follows:

– connect to the GlassFish administration console (by default this is port 4848 using SSL).

– log in with the “admin” user.

– click on Standalone Instances => Applications.

– select the “apex” application and select “Disable” from the “More Actions” drop down list.

The APEX Listener should now be stopped on your GlassFish instance.

5) start the upgrade

To upgrade the APEX software, you need to run the “apexins.sql” sql script from within the “apex” folder that you extracted above. You need to connect as SYS as SYSDBA with the oracle software owner (mine is called “oracle”). Make sure the “apex” folder is writeable!

apexins.sql needs 4 parameters: the name of the tablespace for the APEX user (in my case: APEX), the name of the tablespace for the FLOWS_FILES user (in my case: APEX), the name of the TEMP tablespace (in my case: TEMP) and the name of the virtual image directory (which is “/i/”).

$ su - oracle
$ cd /u01/app/apex
$ sqlplus /nolog
SQL*Plus: Release 11.2.0.3.0 Production on Thu Jan 3 11:21:39 2013

Copyright (c) 1982, 2011, Oracle. All rights reserved.

SQL> connect / as sysdba
Connected.
SQL> @apexins.sql APEX APEX TEMP /i/

The upgrade will now start. This will create a new schema called “APEX_040200”. The upgrade can take some time, depending on the speed of your server. On my server (with fairly recent hardware), it took about 17 minutes. After the upgrade, check the installation log file (in the same folder where you exectuted the apexins.sql file) to make sure no errors were encountered during the process.

6) verify the installation

I always verify my APEX installation after a upgrade by querying dba_registry:

$ sqlplus /nolog

SQL*Plus: Release 11.2.0.3.0 Production on Thu Jan 3 12:06:21 2013

Copyright (c) 1982, 2011, Oracle.  All rights reserved.

SQL> conn / as sysdba
Connected.
SQL> select status from dba_registry where comp_id='APEX';

STATUS
-------------------------------------------------------
VALID

7) copy the images

Next, copy the new and updated images from your installation folder to the GlassFish docroot (you might take a backup folder of the original docroot/i folder just in case):

$ cp -rf /u01/app/apex/images/* /u01/app/glassfish/glassfish/nodes/localhost-domain1/node01/docroot/i/

8) start the APEX Listener

Finally, start the APEX Listener again (see under step 4) and check if all your applications work fine :-) You should now have a nicely upgraded APEX 4.2.1.00.08 installation!

HTH,

Matthias

 

Update:

If you are accessing network services from withing APEX, like LDAP authentication, print servers and such, you will need to grant the new APEX_040200 user network access or it won’t work. This is documented in the installation guide and is only needed for Oracle 11g. You can grant network access by running the following PL/SQL script with user SYS as SYSDBA:

DECLARE
ACL_PATH VARCHAR2(4000);
BEGIN
-- Look for the ACL currently assigned to '*' and give APEX_040200
-- the "connect" privilege if APEX_040200 does not have the privilege yet.
SELECT ACL INTO ACL_PATH FROM DBA_NETWORK_ACLS
WHERE HOST = '*' AND LOWER_PORT IS NULL AND UPPER_PORT IS NULL;
IF DBMS_NETWORK_ACL_ADMIN.CHECK_PRIVILEGE(ACL_PATH, 'APEX_040200',
'connect') IS NULL THEN
DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE(ACL_PATH,
'APEX_040200', TRUE, 'connect');
END IF;
EXCEPTION
-- When no ACL has been assigned to '*'.
WHEN NO_DATA_FOUND THEN
DBMS_NETWORK_ACL_ADMIN.CREATE_ACL('power_users.xml',
'ACL that lets power users to connect to everywhere',
'APEX_040200', TRUE, 'connect');
DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL('power_users.xml','*');
END;
/
COMMIT;

Turning off dynamic listener registration on non-RAC systems to protect against “Oracle TNS Listener Poison Attack”

Environment: Oracle database 11.2.0.3 64-bit, Oracle Linux 6.2 64-bit

Oracle recently released a security alert (CVE-2012-1675) where they warn against a possible “Oracle TNS Listener Poison Attack”. There is no real fix, but there are a number of workarounds that you can use to protect your listeners against unauthorised hijacking.

One of the workarounds involves turning off dynamic registration of database instances by the listener service. However, this workaround can only be used for stand-alone, non-RAC installations. For RAC, you can implement secure transports, which are explained in My Oracle Support document 1453883.1 (if you have access to Oracle support).

Dynamic registration is by default turned on in Oracle 11g. To turn it off, there are two things you need to modify in your $ORACLE_HOME/network/listener.ora file:

First, you need to add a description for all the database instances that the listener will handle. If you fail to do this, any clients trying to connect will receive “ORA-12154: TNS:could not resolve the connect identifier specified” errors after you turned off the dynamic registration.

This is an example for my instance “oratst.mydomain.com”:

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = oratst.mydomain.com)
      (ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_1)
      (SID_NAME = oratst)
    )
  )

Note: the GLOBAL_DBNAME and SID_NAME can be found by checking the Oracle initialization parameters db_name, db_domain and instance_name.

Next, to turn off dynamic registration, you need to add the following line to the listener.ora file:

DYNAMIC_REGISTRATION_LISTENER = OFF

This is how my full listener.ora file now looks like:

# listener.ora Network Configuration File: /u01/app/oracle/product/11.2.0/db_1/network/admin/listener.ora
# Generated by Oracle configuration tools.

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = oracle-tst.mydomain.com)(PORT = 1521))
    )
  )

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = oratst.mydomain.com)
      (ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_1)
      (SID_NAME = oratst)
    )
  )

ADR_BASE_LISTENER = /u01/app/oracle

# needed to solve a conflict with the ONS service installed in the OHS home
# see metalink note 284602.1
SUBSCRIBE_FOR_NODE_DOWN_EVENT_LISTENER = OFF

DYNAMIC_REGISTRATION_LISTENER = OFF

After this, use the listener control utility (lsnrctl) to reload your listener’s configuration, and check the status of the services and the dynamic registration:

>lsnrctl

LSNRCTL for Linux: Version 11.2.0.3.0 - Production on 24-MAY-2012 13:45:49

Copyright (c) 1991, 2011, Oracle.  All rights reserved.

Welcome to LSNRCTL, type "help" for information.

LSNRCTL> reload
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=oracle-tst.mydomain.com)(PORT=1521)))
The command completed successfully
LSNRCTL> services
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=oracle-tst.mydomain.com)(PORT=1521)))
Services Summary...
Service "oratst.mydomain.com" has 1 instance(s).
  Instance "oratst", status UNKNOWN, has 1 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:0 refused:0
         LOCAL SERVER
The command completed successfully
LSNRCTL> show dynamic_registration
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=oracle-tst.mydomain.com)(PORT=1521)))
LISTENER parameter "dynamic_registration" set to OFF
The command completed successfully

Note: the status “UNKNOWN” is normal when you are not using dynamic registration.

Finally, check if you can still connect to the database using a remote client!

HTH,
Matthias