Joords Oracle DBA blog

Instance Caging - Managing Multiple Database Instances on a Single Server

noreply@blogger.com (Jos van den Oord) — Tue, 31 Jan 2012 13:50:00 +0000

Instance caging is another small but useful feature of Oracle Database 11g Release 2. Thanks to it the database resource manager is able, to limit the number of CPUs that can be used by a given instance simultaneously. This feature allows Oracle DBAs to easily manage Oracle Instance CPU consumption.

There are two typical approaches to instance caging for a server:

Over-provisioning:
In this approach, the sum of the CPU limits for each instance exceeds the actual number of CPUs on the system.
Partitioning:In this approach, the sum of allocate CPUs for each instance is equal to the number of CPUs on the server

Most of the servers with multiple database are pollout conform the "over-provisioning" method.

The default value of the CPU_COUNT = 0 # default setting results in Dynamic CPUs Reconfiguration. In this senario the Oracle Database continuously monitors the number of CPUs reported by the operating system and uses the current count. The database dynamically detects any change in the number of available CPUs and reallocates internal resources. On most platforms, the database automatically adjusts the value of the CPU_COUNT initialization parameter to the number of available CPUs.

If CPU_COUNT is set to a value other than 0, then Oracle Database will use this count rather than the actual number of CPUs, thus disabling dynamic CPU reconfiguration.

REMARK; Setting CPU_COUNT to a value greater than the current number of CPUs results in an error. However, if CPU_COUNT is set to a value greater than the current number of CPUs in the initialization parameter file, then CPU_COUNT is capped to the current number of CPUs.

Enabling Instance Caging

To enable instance caging, do the following for each instance on the server:

Set the cpu_count initialization parameter.
This is a dynamic parameter, and can be set with the following statement:
```
ALTER SYSTEM SET CPU_COUNT = 4;
```
Enable the Resource Manager by assigning a resource plan, and ensure that the resource plan has CPU directives, using the mgmt_p1 through mgmt_p8 parameters.

When Resource Manager is enabled, setting CPU_COUNT limits the CPU utilization to approximately CPU_COUNT processors.

Instance caging works by throttling database instance processes that require more CPUs than the instance is entitled to. Wait event resmgr:cpu quantum" may appear in the AWR reports. If throttling becomes significant then increase CPU_COUNT or move this or other databases from the node.

Instance Caging validation

How do you validate that caging is correctly setup

select value from v$parameter where name ='cpu_count' 
and (isdefault='FALSE' or ismodified != 'FALSE');

Query return the current value if modified else nothing.

select name from v$rsrc_plan where cpu_managed='ON' and is_top_plan='TRUE';

If a row returned, then the plan is active.

Playing and monitoring

A database server with only four physical cores. for the four databases we have assigned CPU_COUNT = 3. The maximum percentage of CPU that a single database instance can consume at any point in time is its own limit divided by the sum of limits for all active database instances.

In this example is this 25% [3/(3+3+3+3)=3/12=1/4] => 25 percent.

If only two instances are active and CPU bound then a third instance, which is not caged, will be able to consume 50 percent [4/(3+3+4)=4/10] => 40 persent.

To burn all the available CPU resources, Started four sessions executing the following and use vmstat to validate

PLSQL block

DECLARE
  n NUMBER;
BEGIN
  WHILE (TRUE)
  LOOP
    n:= dbms_random.random();
  END LOOP;
END;
/

VMSTAT

procs ------------memory------------ --swap-- ----io---- --system-- -----cpu-----
 r  b  swpd     free   buff    cache  si   so   bi    bo    in   cs   us sy id wa
 5  0     0  6037164  71568  5476620   0    0   66   198  1033  823  100  0  0  0
 5  0     0  6037164  71572  5476616   0    0    0   158  1021  801  100  0  0  0
 5  0     0  6037164  71572  5476616   0    0    0    48  1016  750  100  0  0  0

Start in another session the following PL/SQL block

PLSQL block

DECLARE
  l_sql VARCHAR2(100) := 'ALTER SYSTEM SET cpu_count = ';
BEGIN
  EXECUTE IMMEDIATE l_sql || '4';
  dbms_lock.sleep(10);
  EXECUTE IMMEDIATE l_sql || '3';
  dbms_lock.sleep(10);
  EXECUTE IMMEDIATE l_sql || '2';
  dbms_lock.sleep(10);
  EXECUTE IMMEDIATE l_sql || '1';
  dbms_lock.sleep(10);
  EXECUTE IMMEDIATE l_sql || '0';
END; 
/

VMSTAT

procs ------------memory------------ --swap-- ----io---- --system-- -----cpu-----
 r  b  swpd     free   buff    cache  si   so   bi    bo    in   cs   us sy id wa
 5  0      0 3695176  80856  7780352   0    0    0    96  1019  783  100  0  0  0
 5  0      0 3695176  80856  7780352   0    0    0   170  1023  795  100  0  0  0
 5  0      0 3695184  80856  7780352   0    0    0   156  1028  795  100  0  0  0
 5  0      0 3695184  80860  7780348   0    0    0   120  1021  795  100  0  0  0
 5  0      0 3694928  80860  7780348   0    0    2   168  1029  810  100  0  0  0
 3  0      0 3694928  80860  7780348   0    0    0   214  1029  939   77  0 23  0
 3  0      0 3694928  80864  7780344   0    0    0   118  1020  961   75  0 25  0
 3  0      0 3694928  80864  7780344   0    0    2   152  1026  961   75  0 25  0
 3  0      0 3694596  80868  7780340   0    0    2   158  1027  975   75  0 25  0
 3  0      0 3694612  80868  7780340   0    0    0   142  1031  979   75  0 25  0
 3  0      0 3694612  80868  7780340   0    0    2   164  1024  963   75  0 25  0
 3  0      0 3694616  80872  7780336   0    0    0   358  1079  961   52  0 49  0
 2  0      0 3694616  80872  7780336   0    0    0   120  1021  909   50  0 50  0
 2  0      0 3697312  80872  7780336   0    0    0   162  1025  952   50  0 50  0
 2  0      0 3694744  80876  7780332   0    0    0   142  1027  948   50  0 50  0
 1  0      0 3694744  80876  7780332   0    0    0   120  1021  954   40  0 60  0
 1  0      0 3694748  80876  7780332   0    0    0   234  1034  953   26  0 74  0
 1  0      0 3694748  80876  7780332   0    0    0   134  1021  921   26  0 74  0
 1  0      0 3696484  80876  7780332   0    0    0   120  1020  954   26  0 74  0
 1  0      0 3696476  80880  7780328   0    0    2   196  1035  996   26  0 74  0
 5  0      0 3696476  80880  7780328   0    0    0   112  1020  643   96  0  4  0
 6  0      0 3696484  80880  7780328   0    0    0   216  1040  778  100  0  0  0
 5  0      0 3696484  80884  7780324   0    0    0   160  1021  763  100  0  0  0
 5  0      0 3696484  80884  7780324   0    0    0   112  1020  775  100  0  0  0
 5  0      0 3696468  80888  7780320   0    0    0   156  1026  785  100  0  0  0

Overprovisioning, stacking - packing as many database instance as possible onto a singel database server, will reduce the idle CPU and wast CPU, but instance caging will ensure that in worse case possible an instance can still acquire a predictable amount of CPU resource.

ASM Processes parameter setting is dependent on the number of databases that connect to ASM

noreply@blogger.com (Jos van den Oord) — Fri, 20 Jan 2012 08:22:00 +0000

Setting init.oraparameter ASM instance
Use default values sesettings. Only processes parameter may need modification.
Processes parameter setting is dependent on the number of databases that connect to ASM processes= 25 + 15n, where n = # databases connected to ASM.
If max processes is reache on ASM, this indicaties offten on hanging processes of cluster agents or RMAN processes

Oracle Goldengate first acquaintance

noreply@blogger.com (Jos van den Oord) — Mon, 05 Dec 2011 11:00:00 +0000

The Oracle's strategic solution for real time data integration. Oracle GoldenGate provides low-impact capture, routing, transformation, and delivery of transactional data across heterogeneous environments in real time

Oracle GoldenGate (OGG) Presentation Transfer-Solutions

GoldenGate Quick Start Tutorials from Gavin SoorMa

RAC Configuration Audit Tool - RACcheck

noreply@blogger.com (Jos van den Oord) — Wed, 16 Nov 2011 01:52:00 +0000

RACcheck

RACcheck is a RAC Configuration Audit tool designed to audit various important configuration settings within a Real Application Clusters (RAC), Oracle Clusterware (CRS), Automatic Storage Management (ASM) and Grid Infrastructure environment.
RAC Configuration Audit Tool audits configuration settings within the following categories:

OS kernel parameters
OS packages
Many other OS configuration settings important to RAC.
CRS/Grid Infrastructure
RDBMS
ASM
Database parameters
Many other database configuration settings important to RAC.

Information:
RACcheck - RAC Configuration Audit Tool [ID 1268927.1]

Tools:

Oracle Cloud File System - Grid Infrastructure ASM features “ACFS”

noreply@blogger.com (Jos van den Oord) — Tue, 01 Nov 2011 00:42:00 +0000

Licensing policy for ASM Cluster File System (ACFS), now called Oracle Cloud File Systerm, is free to use in combination with any Oracle RDBMS version. De files must have a relation with the RDBMS, including non-Oracle files related to the RDBMS application. See the citation below

Citation from the Features and Editions page:

A restricted use license to use Oracle Cloud File System is included with all editions of the database specifically for storing Oracle Database-related configuration files, including Oracle Database software binaries and homes, Oracle Database software administrative files, and Oracle Database software diagnostic files. Customers wishing to store their own data files, or non-Oracle Database software files including data files, binaries, administrative files, and diagnostic files, in Oracle Cloud File System must separately license Oracle Cloud File System.

WARNING: Subscription for node down event still pending in Listener log

noreply@blogger.com (Jos van den Oord) — Wed, 26 Oct 2011 13:55:00 +0000

In the listener log file you constantly get the following warning message.

WARNING: Subscription for node down event still pending.

The warning messages are related to the Oracle TNS Listener's default subscription to the Oracle Notification Service (ONS). This subscription to ONS is introduced in Oracle 10g for RAC environment. Listener subscription to ONS is useful to use advanced features like Fast Application Notification events(FAN) , Fast Application Fail over (FAF) and Fast Connection Failover (FCN) in RAC. So in a non-RAC environment subscription to ONS is not needed. So in a standalone system we can disable it and thus avoid warning message.

Solve the WARNING message in the listener log.
Disable subscription for listener to ONS. This can be done by setting the following parameter in the listener.ora.

SUBSCRIBE_FOR_NODE_DOWN_EVENT_{listener_name}=OFF

Where {listener_name} should be replaced with the actual listener name configured in the LISTENER.ORA file.

After that you need to stop and start the listener by,

lsnrctl stop
lsnrctl start

Alternatively you can reload the listener if availability is important to you. Do it just by,

lsnrctl reload

This will prevent the messages from being written to the log file.

Point of Interest Oracle GoldenGate

noreply@blogger.com (Jos van den Oord) — Wed, 26 Oct 2011 13:00:00 +0000

Point of Interest for Oracle GoldenGate

View more presentations from joord01.

Oracle performance and Time Measurements Within a Virtual Machine

noreply@blogger.com (Jos van den Oord) — Fri, 21 Oct 2011 13:27:00 +0000

Customers often ask to what extent they can trust performance measurements and timing results of the Oracle database within a virtual machine. To fully understand the performance of applications running in a virtual machine, we can give some general guidance.
See for the guideance this link Timekeeping In VirtualMachines

The following items on the ESX server is a key element that must be taken in detecting performance problems in databases that run on VMware:

Over allocation on CPU,
Resource pools, which for the next ESX cluster is planned (depending on setting, this is a ratio (shares) or a longer warranty (reservation))
CPU power management (allowing for lower CPU / clock speed for variable)
CPU affinity (linking VMs to CPUs)

The question, given the above points, is actually what is the meaning of performance measurements and numbers in a guest O / S. This applies in my view not only for VMWare, but also for other solutions.VMWare has a document that on pages 20 and 21 has explained , see the link Timekeeping In VirtualMachines

An example, what is the meaning of a statement that 360 seconds CPU is consumed as it is unclear whether the ESX cluster at that time was 100%, or CPU power management is enabled. What is the meaning of a statement that yesterday and before 360 seconds duration time was in seconds and today 720 seconds (while the number of disk and bufferreads equal).
The answer depends on the last points mentioned regarding the settings of ESX.

Twee remedies tegen systeemuitval en datacorruptie [RAC en Data Guard]

noreply@blogger.com (Jos van den Oord) — Fri, 05 Aug 2011 10:12:00 +0000

Twee remedies tegen systeemuitval en datacorruptie

View more presentations from joord01

Right Availability in RAC environment Playing with Oracleclusterware infrastructure components

noreply@blogger.com (Jos van den Oord) — Tue, 05 Jul 2011 10:02:00 +0000

Right Availability in RAC environment. Playing with Oracle clusterware infrastructure components

View more presentations from joord01

Global Cache Service Processes in a Oracle Cluster (RAC) with Multiple Databases

noreply@blogger.com (Jos van den Oord) — Sun, 22 May 2011 08:13:00 +0000

Creating a cluster with a single pool of storage managed by Oracle ASM provides the infrastructure to manage multiple databases whether they are single instance databases or Oracle RAC databases.

With Oracle RAC databases, you can adjust the number of instances and which nodes run instances for a given database, based on workload requirements. Features such as cluster-managed services allow you to manage multiple workloads on a single database or across multiple databases. It is important to properly manage the capacity in the cluster when adding work. The processes that manage the cluster—including processes both from Oracle Clusterware and database—must be able to obtain CPU resources in a timely fashion and must be given higher priority in the system.

Oracle recommends that the number of real time Global Cache Service Processes (LMSn) on a server is less than or equal to the number of processors. (Note that this is the number of recognized CPUs that includes cores. For example, a dual-core CPU is considered to be two CPUs.).

Consolidating many small databases into a cluster, you may want to reduce the number of LMSn created by the Oracle RAC instance. By default, Oracle Database calculates the number of processes based on the number of CPUs it finds on the server. This calculation may result in more LMSn processes than is needed for the Oracle RAC instance. One LMS process may be sufficient for up to 4 CPUs.To reduce the number of LMSn processes, set the GC_SERVER_PROCESSES initialization parameter minimally to a value of 1. Add a process for every four CPUs needed by the application. In general, it is better to have few busy LMSn processes. Oracle Database calculates the number of processes when the instance is started, and you must restart the instance if you want to change the value.

RMAN Configuration in Data Guard [Archivelog Deletion Policy]

noreply@blogger.com (Jos van den Oord) — Fri, 20 May 2011 09:07:00 +0000

Follow these steps to configure RMAN backups in Data Guard environment. When you want to managed the archivelog deletion policy from one centralpoint.

CONFIGURE ARCHIVELOG DELETION POLICY

Possible options :



APPLIED ON STANDBY -  enables flash recovery area to delete archivelogs that are applied on mandatory standby. 



NONE       -  enables flash recovery area to delete archivelogs that are backed up to tertiary device and that are obsolete based on the configured  backup retention policy. This is the default configuration.



CLEAR       -  clears the deletion policy and returns the specified configuration to default value. The default value is NONE.

Viewing the Current Deletion Policy

To view the current setting (APPLIED ON STANDBY, CLEAR, NONE) for a database, issue the following query :

SELECT NAME, VALUE FROM V$RMAN_CONFIGURATION WHERE
NAME LIKE '%ARCHIVELOG DELETION POLICY%';



NAME VALUE

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

ARCHIVELOG DELETION POLICY TO APPLIED ON STANDBY

You can also find the existing configuration using the RMAN SHOW ARCHIVELOG

DELETION POLICY command:

RMAN> SHOW ARCHIVELOG DELETION POLICY

RMAN configuration parameters are:

CONFIGURE ARCHIVELOG DELETION POLICY TO APPLIED ON STANDBY;

When Backup is taken form the Standby Database.

Issue the following command on the primary database:

RMAN> CONFIGURE ARCHIVELOG DELETION POLICY TO NONE;

The following commands should be issued, after connecting to each of the other standby database servers.

RMAN> CONFIGURE ARCHIVELOG DELETION POLICY TO APPLIED ON STANDBY;

Reconfiguring the Deletion Policy After a Role Transition

By setting this configuration on each of the other standby databases (where backups are not being taken), it will enable automatic deletion of archived logs on this standby database that have been applied to all other remote standby destinations. By default, this configuration requires that at least one remote destination is set to mandatory. Archived logs are deleted if space in the Flash Recovery Area needs to be reclaimed for new files.

Note: Mandatory standby destination can impact the primary database if the standby destination cannot be reached.

Configure RMAN standby database where backups are taken:

Issue the following command on the physical standby where the backup will be taken.

RMAN> CONFIGURE DELETION POLICY TO NONE;

Specify when the archived logs can be deleted with the CONFIGURE ARCHIVELOG DELETION POLICY COMMAND. Since the logs are backed up at the standby site, you should specified the NONE option for the log deletion policy. This will enable automatic deletion of archived logs on the standby database that are outside of the retention period or that are already backed up to tape, if additional space is needed for new backups or archived logs.

On all the other physical standby databases where backups are note taken and the primary database.

RMAN> CONFIGURE ARCHIVELOG DELETION POLICY TO APPLIED ON STANDBY;

Note: That in the event of a switchover or failover, the database role changes and the appropriate CONFIGURE commands must be re-executed on new primary and standby databases.

More Information:

ASM required mirror free space to accommodate disk failures

noreply@blogger.com (Jos van den Oord) — Thu, 19 May 2011 19:59:00 +0000

Blogpost of Joel Goodman Monday, March 28, 2011 28/03/2011

Many ASM administrators have little or no experience with ASM mirroring, because they use External Redundancy for their diskgroups and ASM generally maintains one copy for each AU in this case. Tracking free space within a diskgroup in such a case is simple. Use the following details from view V$ASM_DISKGROUP to examine the redundancy, offline state and free space requirements for mirror recovery:

TOTAL_MB => indicates the total space in the diskgroup
FREE_MB => indicates the total free space  in the diskgroup
REQUIRED_MIRROR_FREE_MB => indicates free space required in the diskgroup to restore redundancy by copying allocation units as described above.
USABLE_FILE_MB => indicates how much of the FREE_MB may be safely used whilst leaving enough free space for mirror copy recovery in the case of disk failure
TYPE => indicates the redundancy attribute of the diskgroup.
OFFLINE_DISKS => indicates how many disks are offline in the diskgroup

Note: the LSDG command in the ASMCMD utility will provide the same information.

The crucial aspect of administering the diskgroups when ASM mirrors the AUs, is having enough free space to re-copy the AUs when a loss occurs. This is not a concern when external redundancy is used, but it is for normal and high redundancy, and requires that the ASM administrator be aware of the free space needs.

Use Chrome plugin to make Oracle AWR reports (formattter) friendly

noreply@blogger.com (Jos van den Oord) — Mon, 02 May 2011 05:32:00 +0000

Blogpost of Tom Kyte Thursday, April 28, 2011

An "AWR Formatter" written by a friend of mine, Tyler Muth. It's pretty cool - works as a Chrome plugin - and it makes an AWR report a little more 'friendly' to use. It creates hot links for many of the wait events (so you know what they mean) and it summarizes up a lot of stuff - making the AWR report a lot more "interactive". Check it out and give him feedback on it if you have time.

Installation:

Open this URL in Google Chrome: http://dl.dropbox.com/u/4131944/AWR-Format/AWR-Format.crx
Choose “Install”
Go to Tools > Extensions and check the “Allow access to file URLs” box. This is critical as it only works on local files.

You can watch a screencast of it in action here.

More information:

If you need a “demo” AWR Report , you can download this report

Use Firefox Quick Searches to quickly access Oracle related information: DOCs, SRs, NOTEs, BUGs etc…”

noreply@blogger.com (Jos van den Oord) — Thu, 28 Apr 2011 05:27:00 +0000

Customize your Firefox to use acronym and contents of mentioned Metalink Note displayed without the need to open Metalink web site. “mn 1269139.1″ (mn was an acronym for Metalink Note)

How to do this see the blog of Oleksandr Denysenko's Blog

Patch for Enterprise Manager Database Control 10.2.0.4 Or 10.2.0.5

noreply@blogger.com (Jos van den Oord) — Tue, 19 Apr 2011 20:30:00 +0000

The Problem
Enterprise Manager Database Control with Oracle Database 10.2.0.4 and 10.2.0.5 " The DBCONSOLE" will not work. The root certificate used to secure communications via the Secure Socket Layer (SSL) protocol will expire on 31-Dec-2010 00:00:00.

ATTENTION - Enterprise Manager Database Control 10.2.0.4 Or 10.2.0.5 - Patch Required from 31-Dec-2010 onwards [ID 1217493.1]

Solving this problem
If you plan to configure Database Control with either of these Oracle Database releases, Oracle strongly recommends that you apply Patch 8350262 to your Oracle Home installations before you configure Database Control. Configuration of Database Control is typically done when you create or upgrade Oracle Database, or if you run Enterprise Manager Configuration Assistant (EMCA) in standalone mode.

Bug 9981011: VARIOUS ORACLE PROCESSES CONSUMING LARGE QUANTITIES OF FILE DESCIPTORS (NFILES)

noreply@blogger.com (Jos van den Oord) — Tue, 19 Apr 2011 18:10:00 +0000

After upgrading to the 10.2.0.5.0 release the database is encounter a file handle leak.  The leak is seen in production and development instances. The leak happens on different processes (emagent and MMNL process are the current ones).

Test on growing of openfiles:

lsof -p 10140 |wc -l => 600
lsof -p 10140 |wc -l => 759

Now we wait for the sollution of oracle to solve this bug.

The max file limit has been reached so the db will have to be shutdown or the

process killed, if possible, to free up files.The leak happen over time and manifests itself when the limits are reached as error ORA-27041.

The sollotion
VARIOUS ORACLE PROCESSES CONSUMING LARGE QUANTITIES OF FILE DESCIPTORS (NFILES) Patch 9981011. For us we need the Patch download for Oracle 10.2.0.5 on HP-UX Itanium.

This patch is also suitable for a 10.2.0.5.1 home, there are no conflicts with 10.2.0.5.1

Flash Recovery Area backup to disk with RMAN

noreply@blogger.com (Jos van den Oord) — Thu, 31 Mar 2011 08:19:00 +0000

Is it possible to do a RMAN backup of the Flash Recovery Area (FRA) to disk. The standard method by RMAN is that this is only possible with the use of a SBT channel/library. The RMAN command 'BACKUP RECOVERY AREA' works only with SBT channels, meaning a tertiary storage such as tape.

Oracle has introduce an emulator for SBT library to do a backup of the FRA to disk. The RMAN disksbt library, which emulates a SBT library (but it writes the backups to a disk location). This disksbt, can be used to test backup the FRA to a disk location

For Example:

RMAN> run { allocate channel dev1 type sbt parms='SBT_LIBRARY=oracle.disksbt, 
ENV=(BACKUP_DIR=/home/oracle)'; 
crosscheck backup device type sbt_tape; 
delete noprompt expired backup; 
}

Oracle RDBMS Patch set 11.2.0.2 new behaviour on datafile and Automatic Memory Management

noreply@blogger.com (Jos van den Oord) — Sun, 27 Mar 2011 21:29:00 +0000

New Behaviour for datafile:

In patch set 11.2.0.2 a new behaviour for datafile write errors has been implemented.With this release ANY write error to a datafile will cause the instance to abort.

Before 11.2.0.2 those errors usually led to an offline datafile if the database operates in archivelog mode (your production database do, don’t they?!) and the datafile does not belong to the SYSTEM tablespace. Internal discussion found this behaviour not up-to-date and alligned with RAC systems and modern storages.
Therefore it has been changed and a new underscore parameter got introduced.

_DATAFILE_WRITE_ERRORS_CRASH_INSTANCE=TRUE

This is the default setting´and the new behaviour beginning with Oracle 11.2.0.2

If you would like to revert to the pre-11.2.0.2 behaviour you’ll have to set in your init.ora/spfile this parameter to false. But keep in mind that there’s a reason why this has been changed.
More info in MOSC Note: 7691270.8

New Behaviour for Automatic Mamory Management (AMM):

Unfortunately a “new feature” in 11gR2 turns AMM on unless a hidden parameter called _memory_imm_mode_without_autosga is set to FALSE.

This means if you have disabled AMM , upgrades to 11gR2, you can find that AMM has reappeared, even if you think that you have disabled it. [Oracle11gR2 AMM restart]

There is a MOSC note about AMM in 11gR2 that notes that this regression back to AMM is the expected behavior in 11.2 for immediate memory allocation requests and that Oracle added this behavior as a 11gR2 new feature when automatic memory management was disabled.

More info MOSC Note:1269139.1, “SGA Re-Sizes Occurring Despite AMM/ASMM Being Disabled(MEMORY_TARGET/SGA_TARGET=0)”

Clusterware References 10g

noreply@blogger.com (Jos van den Oord) — Thu, 24 Mar 2011 22:50:00 +0000

Clusterware References;
Pro Oracle Database 10g RAC on Linux

Metalink Notes

259301.1	CRS and 10g RAC
	This note contains a useful awk script to improve the output of crs_stat -ls
436067.1	Windows CRS_STAT script to display long names correctly
309541.1	How to start/stop the 10g CRS Clusterware
263897.1	How to stop Cluster Ready Services (CRS)
298073.1	How to remove CRS auto start and restart for a RAC instance
295871.1	How to verify if CRS install is valid
316583.1	VIPCA fails complaining that interface is not public
341214.1	How to cleanup after a failed (or successful) Oracle Clusterware installation
280589.1	How to install Oracle 10g CRS on a cluster where one or more nodes are not to be configured with CRS immediately
357808.1	CRS Diagnostics
272331.1	CRS 10g Diagnostic Guide
330358.1	CRS 10g R2 Diagnostic Collection Guide
331168.1	Oracle Clusterware consolidated logging in 10gR2
342590.1	CRS logs not being written
357808.1	Diagnosability for CRS/EVM/RACG
459694.1	Procwatcher: Script to Monitor and Examine Oracle and CRS Processes
289690.1	Data Gathering for Troubleshooting RAC and CRS issues
265769.1	Troubleshooting CRS Reboots
240001.1	Troubleshooting CRS root.sh problems (10g RAC)
239989.1	10g RAC - Stopping Reboot Loops when CRS problems occur
294430.1	CSS Timeout Computation in 10g RAC
284752.1	10gRAC: Steps to Increase CSS Misscount, Reboottime and Disktimeout
462616.1	Reconfiguring the CSS disktimeout of 10gR2 Clusterware for proper LUN failover
293819.1	Placement of voting and OCR disk file in 10g RAC
317628.1	How to replace a corrupt OCR mirror file
452486.1	Moving OCR and Voting Disk to another location
399482.1	How to recreate OCR/Voting disk accidentally deleted
358620.1	How to recreate OCR/Voting disk in 10gR1/R2 RAC
279793.1	How to Restore a Lost Voting Disk in 10g
264847.1	How to Configure Virtual IPs for 10g RAC
283684.1	How to change interconnect/public interface IP subnet in a 10g cluster
276434.1	Modifying the VIP or VIP Hostname of an Oracle 10g Clusterware Node
294336.1	Changing the check interval for the Oracle 10g VIP
219361.1	Troubleshooting Instance Evictions (ORA-29740)
297498.1	Resolving Instance Evictions on Windows platforms
315125.1	What to check if the Cluster Synchronization Services daemon (OCSSD) does not start
270512.1	Adding a node to a 10g RAC Cluster
269320.1	Removing a node from a 10g RAC Cluster
338706.1	Cluster Ready Services (CRS) rolling upgrade
399031.1	Step-by-step installation of Oracle Clusterware one-off and bundle patches for Oracle 10g
401783.1	Changes in Oracle Clusterware after applying 10.2.0.3 Patchset
405820.1	Known Issues After Applying 10.2 CRS bundle patches
316817.1	Cluster Verification Utility (CLUVFY) FAQ
372358.1	Shared disk check with the Cluster Verification Utility
338924.1	CLUVFY Fails with error - could not find a suitable set of interfaces for VIPs

Bugs

5849200	CRS LOGS ARE NOT BEING WRITTEN
5137401	OPROCD LOGFILE IS CLEARED AFTER A REBOOT
	Fixed in Oracle 10.2.0.4+ and 11.1.0.6+

White Papers

Using Oracle Clusterware to Protect Oracle Application Server	Roland Knapp
Using Oracle Clusterware to Protect Third Party Applications	Philip Newlan
Using Oracle Clusterware to Protect a Single-Instance Oracle Database	Philip Newlan

OEM/OMS 11gR1 problem monitoring the SCAN listener GRID/RAC 11gR2

noreply@blogger.com (Jos van den Oord) — Sun, 20 Mar 2011 22:59:00 +0000

Regarding to the scan listener is the relocation not being handled correctly by Oracle Enterprise Manager(OEM) 11gR1 (shows target as down after relocate!), Oracle has solved this by the following bugs/fixes:

Apply Patch 10118817 on OMS: BUG 10118817 - SCAN LISTENERS NOT BEING DISPLAYED CORRECTLY IN GC CONSOLE AFTER FAILOVER
(this is a generic platform fix)

Apply Patch 10392806 on AGENT: Bug 10392806: METADATA CHANGES FOR BUG 10118817
(this patch is available on linux x86/x86-64)

Usually after these patches, the fix should work, if all the post install steps, agent patches are applied correctly.
There is one more scenario in the bug 10118817 where in the cluster target metrics in EM "crs_event" and "resource_status" are present in $AGENT_HOME/<host>/sysman/emd/collection/cluster_<ClusterName>.xml. You will have to remove these metrics, as mentioned in the bug.

Keep in mind those patches are conflicing with the PSU 2 patch. There is a request by Oracle to solve this also for the PSU patch 2.If installed the PSU 2 patch you can not use this patch .

Oracle Locator Express - OLE

noreply@blogger.com (Jos van den Oord) — Thu, 03 Mar 2011 17:04:00 +0000

Oracle Locator Express is a replacement tool for the standard Oracle Home Selector. It sits in the system tray and allows you to easily switch between multiple Oracle Homes with just one click.

Oracle Locator Express is also compatible with Oracle Instant client. What's new in this version: Version 2.0.1 fixes a bug where the version of the Oracle client was displayed incorrectly for a 11gr2 client

Update: UTL_DBWS and Consuming Web services in Oracle

noreply@blogger.com (Jos van den Oord) — Wed, 02 Mar 2011 19:16:00 +0000

Using UTL_DBWS to make a Database Callout to a Document Style Web service.
Update of the UTL_DBWS – Consuming Web services in Oracle

First, download the latest copy of the dbwsclient.jar file:

Pre 10g: dbws-callout-utility.zip (10.1.2)
10g: dbws-callout-utility-10R2.zip (10.1.3.0)
10g & 11g latest: dbws-callout-utility-10131.zip (10.1.3.1)
Extract the jar file from the zip file into the $ORACLE_HOME/sqlj/lib directory.

The jar file can be loaded into the SYS schema for everyone to access, or into an individual schema that needs access to the web client.

# Load into the SYS schema.

export PATH=/u01/app/oracle/product/10.2.0/db_1/bin:$PATH
cd /u01/app/oracle/product/10.2.0/db_1/sqlj/lib

# 10gR2

loadjava -u sys/password -r -v -f -genmissing -s -grant public dbwsclientws.jar dbwsclientdb102.jar

# 11g

loadjava -u sys/password -r -v -f -genmissing -s -grant public dbwsclientws.jar dbwsclientdb11.jar

# Load into an individual schema.

export PATH=/u01/app/oracle/product/10.2.0/db_1/bin:$PATH
cd /u01/app/oracle/product/10.2.0/db_1/sqlj/lib

# 10gR2

loadjava -u scott/tiger -r -v -f -genmissing dbwsclientws.jar dbwsclientdb102.jar

# 11g

loadjava -u scott/tiger -r -v -f -genmissing dbwsclientws.jar dbwsclientdb11.jar

SELECT owner, status, count(*) FROM DBA_OBJECTS
  WHERE OBJECT_TYPE='JAVA CLASS'
  GROUP BY owner, status;

Execute as sys user

execute dbms_java.grant_permission('DBAPRD4','SYS:java.util.PropertyPermission','http.proxySet','write');
execute dbms_java.grant_permission('DBAPRD4','SYS:java.util.PropertyPermission','http.proxyHost', 'write');
execute dbms_java.grant_permission('DBAPRD4','SYS:java.util.PropertyPermission','http.proxyPort', 'write');
execute dbms_java.grant_permission('DBAPRD4','SYS:java.lang.RuntimePermission', 'accessClassInPackage.sun.util.calendar','');
execute dbms_java.grant_permission('DBAPRD4','SYS:java.lang.RuntimePermission','getClassLoader','');
execute dbms_java.grant_permission('DBAPRD4','SYS:java.net.SocketPermission','*','connect,resolve');
execute dbms_java.grant_permission('DBAPRD4','SYS:java.util.PropertyPermission','*','read,write');
execute dbms_java.grant_permission('DBAPRD4','SYS:java.lang.RuntimePermission','setFactory','');

Connect as DBAPRD4 user

CREATE OR REPLACE FUNCTION get_joke RETURN VARCHAR2
 AS
   service_    dbaprd4.utl_dbws.SERVICE;
   call_        dbaprd4.utl_dbws.CALL;
   service_qname   dbaprd4.utl_dbws.QNAME;
   port_qname     dbaprd4.utl_dbws.QNAME;
   xoperation_qname  dbaprd4.utl_dbws.QNAME;
   xstring_type_qname  dbaprd4.utl_dbws.QNAME;
   response    sys.XMLTYPE;
   request     sys.XMLTYPE;
 BEGIN
   service_qname := dbaprd4.utl_dbws.to_qname(null, 'getJoke');
   service_      := dbaprd4.utl_dbws.create_service(service_qname);
   call_         := dbaprd4.utl_dbws.create_call(service_);
   dbaprd4.utl_dbws.set_target_endpoint_address(call_, 'http://interpressfact.net/webservices/getjoke.asmx');
   dbaprd4.utl_dbws.set_property( call_, 'SOAPACTION_USE', 'TRUE');
   dbaprd4.utl_dbws.set_property( call_, 'SOAPACTION_URI', 'http://interpressfact.net/webservices/getJoke');
   dbaprd4.utl_dbws.set_property( call_, 'OPERATION_STYLE', 'document');
   request := sys.XMLTYPE(''||'Excuses-10 ');
   response :=dbaprd4.utl_dbws.invoke(call_, request);
   return response.extract('//getJokeResult/child::text()', 'xmlns="http://interpressfact.net/webservices/"').getstringval();
 END;
 /

SELECT get_joke FROM dual;

-- # Test webservice address http://interpressfact.net/webservices/getjoke.asmx
-- on 11g issue with ACL
/*
-- Issue with ACL used sys then no issue with acl
  DBMS_NETWORK_ACL_ADMIN
  DBMS_NETWORK_ACL_UTILITY

  ERROR at line 1:
  ORA-29532: Java call terminated by uncaught Java exception: HTTP transport
  error: javax.xml.soap.SOAPException: java.security.PrivilegedActionException:
  javax.xml.soap.SOAPException: Message send failed:
  HTTPClient.AuthSchemeNotImplException: NTLM
  ORA-06512: at "DBAPRD4.UTL_DBWS", line 404
  ORA-06512: at "DBAPRD4.UTL_DBWS", line 401
  ORA-06512: at "DBAPRD4.GET_JOKE", line 25
*/

Solving the security issue in Oracle by setting up an ACL
or use the user sys to execute the function.

BEGIN
  DBMS_NETWORK_ACL_ADMIN.create_acl (
    acl          => 'test_acl_file.xml', 
    description  => 'A test of the ACL functionality',
    principal    => 'DBAPRD4',
    is_grant     => FALSE, 
    privilege    => 'connect',
    start_date   => SYSTIMESTAMP,
    end_date     => NULL);

  COMMIT;
END;
/

BEGIN
  DBMS_NETWORK_ACL_ADMIN.assign_acl (
    acl         => 'test_acl_file.xml',
    host        => '*', 
    lower_port  => NULL,
    upper_port  => NULL); 

  COMMIT;
END;
/

BEGIN
  DBMS_NETWORK_ACL_ADMIN.unassign_acl (
    acl         => 'test_acl_file.xml',
    host        => '192.168.2.3', 
    lower_port  => 80,
    upper_port  => NULL); 

  COMMIT;
END;
/

An other function with shakespeare phrase

create or replace function get_shakespeare(p_phrase in varchar2) 
    RETURN varchar2
AS  
  l_service                 utl_dbws.SERVICE;
  l_call                    utl_dbws.CALL;
  l_service_qname           utl_dbws.QNAME;
  l_port_qname              utl_dbws.QNAME;
  l_operation_qname         utl_dbws.QNAME;
  l_string_type_qname       utl_dbws.QNAME;
  l_namespace               varchar2(1000);
  l_retx                    sys.xmltype;
  l_xml_string              sys.xmltype;
Begin
  l_namespace       := 'http://xmlme.com/WebServices';
  l_service_qname   := sys.utl_dbws.to_qname(l_namespace, 'Shakespeare');
  l_service         := sys.utl_dbws.create_service(HTTPURITYPE('http://www.xmlme.com/WSShakespeare.asmx?WSDL'), l_service_qname);
  l_port_qname      :=  sys.utl_dbws.to_qname(l_namespace, 'ShakespeareSoap');
  l_operation_qname := sys.utl_dbws.to_qname(l_namespace, 'GetSpeech');
  l_call            := sys.utl_dbws.create_call(l_service, l_port_qname, l_operation_qname);
  sys.utl_dbws.set_property(l_call, 'SOAPACTION_USE', 'TRUE');
  sys.utl_dbws.set_property(l_call_, 'SOAPACTION_URI', 'http://xmlme.com/WebServices/GetSpeech'); 
  sys.utl_dbws.set_property(l_call_, 'ENCODINGSTYLE_URI', 'http://schemas.xmlsoap.org/soap/encoding/');
  sys.utl_dbws.set_property(l_call_, 'OPERATION_STYLE', 'document');
  l_string_type_qname := sys.utl_dbws.to_qname('http://www.w3.org/2001/XMLSchema', 'string');
  sys.utl_dbws.add_parameter(l_call, 'Request', l_string_type_qname, 'ParameterMode.IN');
  sys.utl_dbws.set_return_type(l_call, l_string_type_qname);
  l_xml_string := xmltype('
    
      '||p_phrase||'  -- the searche for text --
    ');
  l_retx := sys.utl_dbws.invoke(call_Handle => l_call_,request => l_xml_string);
  return l_retx.extract('/*').getstringval();
  sys.utl_dbws.release_service(l_service);
end;
/

Examples:

SELECT dbaprd4.get_shakespeare('To be, or not to be') FROM dual;
SELECT dbaprd4.get_shakespeare('O Romeo, Romeo! wherefore art thou Romeo') FROM dual;
SELECT dbaprd4.get_shakespeare('Let's kill all the lawyers') FROM dual;
SELECT dbaprd4.get_shakespeare('Good night sweet prince') FROM dual;

HP DataProtector supports Oracle ACFS

noreply@blogger.com (Jos van den Oord) — Wed, 23 Feb 2011 07:16:00 +0000

Great Oracle ACFS is now supported by HP Dataprotector. See the Platform and integration support matrix link in this blog

Question

Is it possible to backup Oracle ACFS with HP DATAPROTECTOR. Currently we can't see the mount point that we have created on the system with the HP DATAPROTECTOR GUI. We used Data Protector 6.11 [dd - Jan 12,2011]

Answer from HP Support

It is Supported with Data Protector 6.11 patches: PHSS_41457 / PHSS_41458 / DPLNX_00136 / DPSOL_00430.

HP Information

HP dataProtector 6.11 - Platform and integration support matrix date: 11Februari 2011.

Oracle 11.2 Clusterware commands and Deprecated CRS commands 10g

noreply@blogger.com (Jos van den Oord) — Wed, 02 Feb 2011 06:44:00 +0000

Deprecated Command	Equivalent 11.2 Command
==================	========================
crs_stat	crsctl stat resource
crsctl check cssd	crsctl check css
crsctl check crsd	crsctl check crs
crsctl check evmd	crsctl check crs
crs_register	crsctl add resource
crs_unregister	crsctl delete resource
crs_start	crsctl start resource
crs_stop	crsctl stop resource or crsctl stop cluster
crs_getperm	crsctl getperm resource
crs_profile	crsctl add resource
crs_relocate	crsctl relocate resource
crs_setperm	crsctl setperm resource
crsctl debug log	crsctl set log or crsctl set trace
crsctl set css votedisk	crsctl add css votedisk
crsctl start resources	crsctl start resource
crsctl stop resources	crsctl stop resource

Joords Oracle DBA blog

Instance Caging - Managing Multiple Database Instances on a Single Server

There are two typical approaches to instance caging for a server:

Enabling Instance Caging

Instance Caging validation

Playing and monitoring

ASM Processes parameter setting is dependent on the number of databases that connect to ASM

Oracle Goldengate first acquaintance

RAC Configuration Audit Tool - RACcheck

RACcheck

Oracle Cloud File System - Grid Infrastructure ASM features “ACFS”

WARNING: Subscription for node down event still pending in Listener log

WARNING: Subscription for node down event still pending.

SUBSCRIBE_FOR_NODE_DOWN_EVENT_{listener_name}=OFF

Point of Interest Oracle GoldenGate

Oracle performance and Time Measurements Within a Virtual Machine

Twee remedies tegen systeemuitval en datacorruptie [RAC en Data Guard]

Right Availability in RAC environment Playing with Oracleclusterware infrastructure components

Global Cache Service Processes in a Oracle Cluster (RAC) with Multiple Databases

RMAN Configuration in Data Guard [Archivelog Deletion Policy]

CONFIGURE ARCHIVELOG DELETION POLICY

Viewing the Current Deletion Policy

When Backup is taken form the Standby Database.

Configure RMAN standby database where backups are taken:

ASM required mirror free space to accommodate disk failures

Use Chrome plugin to make Oracle AWR reports (formattter) friendly

Installation:

More information:

Use Firefox Quick Searches to quickly access Oracle related information: DOCs, SRs, NOTEs, BUGs etc…”

Patch for Enterprise Manager Database Control 10.2.0.4 Or 10.2.0.5

Bug 9981011: VARIOUS ORACLE PROCESSES CONSUMING LARGE QUANTITIES OF FILE DESCIPTORS (NFILES)

Flash Recovery Area backup to disk with RMAN

Oracle RDBMS Patch set 11.2.0.2 new behaviour on datafile and Automatic Memory Management

New Behaviour for datafile:

New Behaviour for Automatic Mamory Management (AMM):

Clusterware References 10g

Clusterware References; Pro Oracle Database 10g RAC on Linux

Metalink Notes

Bugs

White Papers

OEM/OMS 11gR1 problem monitoring the SCAN listener GRID/RAC 11gR2

Oracle Locator Express - OLE

Update: UTL_DBWS and Consuming Web services in Oracle

HP DataProtector supports Oracle ACFS

Question

Answer from HP Support

HP Information

Oracle 11.2 Clusterware commands and Deprecated CRS commands 10g

Clusterware References;
Pro Oracle Database 10g RAC on Linux