Disable AMM for an ASM instance

At work, we have come across some ASM instances throwing ORA-4031 messages.  According to this MOS Doc it could be due to a conflict with Huge Pages being set on the system.

Disabling AMM for ASM is sorta tricky.  I found an Exadata note on MOS that describes how to do it.  I was successful doing this on a test machine.

These steps have been verified on 11.2.0.4.

alter system set sga_target=400M sid='*' scope=spfile;
alter system set pga_aggregate_target=200M sid='*' scope=spfile;
alter system set memory_target=0 sid='*' scope=spfile;
alter system set memory_max_target=0 sid='*' scope=spfile /* required workaround */;
alter system reset memory_max_target sid='*' scope=spfile;

Reference
MOS Doc including steps for disabling AMM for ASM

Apex Prevent Lost Updates with Checksum

So this post is more of a placeholder for my notes, than a truly educational resource. I suppose if you are looking for a barebones explanation of what is need to accomplish this task, then this may work.

So the issue is how to prevent lost updates when dealing with Apex pages. In other words, you read a page into your browser, and while you are making changes in the page, somebody else goes in and changes the database. If you proceed to save your changes, you could overwrite what somebody else has done, thus creating the phenomenon of lost updates.

So to handle this, store a checksum of all your page items as a hidden field on the page. You can use the following PL/SQL package to generate the checksum.

wwv_flow_item.MD5(pageitem1,pageitem2,...,pageitem50);

Now, when you go to save your data, generate another checksum and compare it to your saved checksum. If they are the same, then you are good to go. If they are different, you should raise an error and prompt the user to re-enter the data, first re-reading from the database.

Moving an Oracle Database to ASM

In this post I would like to describe the process for moving all of your Oracle database files into ASM. I will cover moving them all, control files, redo, temp, spfile, and of course data.

There are many advantages to moving files into ASM, but I think one of the coolest is that it is a cluster filesystem. So, with all your files loaded into ASM, it becomes very easy to start your database on a different host that has the same disk group mounted. Having a standby database in ASM opens up even more possibilities, but more on that in a future post.

Copy the datafiles

I have used this technique to move datafiles to a different SAN. It can be done while the database is up and running. You can allocate multiple channels to get a parallel effect.

backup as copy to destination '+DATA' database;

Move the controlfile into ASM

At this point, you need to start your outage.

Re-point to a controlfile in ASM

alter system set control_files = '+DATA' scope=spfile;

shutdown immediate;

startup nomount

--controlfile will be restored from its original location to +DATA. 
restore controlfile from '/home/oracle/app/oradata/ATW/control01.ctl';

Mount the database, rename the tempfile (it will be recreated when we open the database

alter database mount;

run {
set newname for tempfile 1 to '+DATA';

switch tempfile all;
}

Rename the datafiles to use the ASM copies, recover and open the database.

--This command renames the datafiles to the most recent image copies, which should be the ones we created with our backup command
switch database to copy;

recover database;

alter database open;

At this point all your temp files and data files should be in ASM

select name from v$tempfile;
select name from v$datafile;

Create redo logs in ASM

alter database add logfile '+DATA';
alter database add logfile '+DATA';
alter database add logfile '+DATA';

select member from v$logfile;

After you have created the necessary number of redo logs in ASM, switch logs and wait for the old logs to become inactive, then drop them.

select group#,status from v$log;

alter system switch logfile;

--as they become active, you can drop each group
alter database drop logfile group 1;
alter database drop logfile group 2;
alter database drop logfile group 3;