Ittichai Chammavanijakul
Oracle Application Express (APEX) Certification Exam
Oracle is working toward having the first certification exam for Oracle APEX. This “Oracle Application Express Developer Certified Expert” pilot program is intended for intermediate and advanced APEX developers. You will have to indicate the relevant APEX trainings you’ve completed, years of APEX experiences and, of course, the agreement to provide feedback in order to participate. You can see details about this from Joel Kallman’s blog.
I think this is a good thing for APEX community because it shows Oracle’s commitment to this tool.
BTW, I’m very curious about how the tests will be conducted. Personally I think there should be more of actual development hands-on works rather than doing multiple choices.
11gR2 New Feature – File Watchers
The File Watcher is a scheduler object that starts a job whenever files whose attributes met the defined criteria arrived on a system. These criteria include the name, location, and other properties of a file. When the file watcher detects the arrival of the designated file, it raises a file-arrival event. The event message, which has all information on the newly-arrived file, can then be used to process the file.
This new feature simplifies the configurations of the most common triggering event in the data load/batch processing which is to detect the arrival of files.
File Watcher configuration
Setup a new database account to manage the file watcher.
SQL> create user watcher_user identified by watcher_pwd quota unlimited on users; User created SQL> grant connect to watcher_user; Grant succeeded. SQL> grant EXECUTE on SYS.SCHEDULER_FILEWATCHER_RESULT to watcher_user; Grant succeeded.
Other grants needed to complete the tests:
grant create table, create procedure, create job to watcher_user; grant execute on dbms_lock to watcher_user; grant execute on dbms_system to watcher_user; grant manage scheduler to watcher_user; SQL> create or replace directory STAGING_DIR as '/home/oracle/staging'; Directory created. SQL> grant read, write on directory staging_dir to watcher_user; Grant succeeded.
Now as a new watcher_user, we will configure the File Watcher.
1. Create a credential using the OS privilege for file access.
begin dbms_scheduler.create_credential( credential_name => 'watch_credential', username => 'oracle', password => 'oracle'); end; /
2. Create a table to store data processed from file.
create table t_staging_files( upload_timestamp timestamp, file_name varchar2(100), file_size number, contents clob );
3. The procedure will process file data and put into a database table.
create or replace procedure process_files
(payload IN sys.scheduler_filewatcher_result)
is
l_clob clob;
l_bfile bfile;
dest_offset INTEGER := 1;
src_offset INTEGER := 1;
src_csid NUMBER := NLS_CHARSET_ID ('AL32UTF8');
lang_context INTEGER := dbms_lob.default_lang_ctx;
warning INTEGER;
begin
insert into t_staging_files (
upload_timestamp , file_name, file_size, contents)
values(
payload.file_timestamp,
payload.directory_path || '/' || payload.actual_file_name,
payload.file_size,
empty_clob()
) returning contents into l_clob;
l_bfile := bfilename('STAGING_DIR', payload.actual_file_name);
dbms_lob.fileopen(l_bfile);
dbms_lob.loadclobfromfile (
l_clob,
l_bfile,
dbms_lob.getlength(l_bfile),
dest_offset,
src_offset,
src_csid,
lang_context,
warning
);
dbms_lob.fileclose(l_bfile);
end;
/
4. Create a Program object with a Metadata argument.
begin
dbms_scheduler.create_program (
program_name => 'file_watcher',
program_type => 'stored_procedure',
program_action => 'process_files',
number_of_arguments => 1,
enabled => false);
dbms_scheduler.define_metadata_argument (
program_name => 'file_watcher',
metadata_attribute => 'event_message',
argument_position => 1);
dbms_scheduler.enable('file_watcher');
end;
/
PL/SQL procedure successfully completed.
5. Create a File Watcher
begin
dbms_scheduler.create_file_watcher(
file_watcher_name => 'my_file_watcher',
directory_path => '/home/oracle/staging',
file_name => '*',
credential_name => 'watch_credential',
destination => null,
enabled => false);
end;
/
PL/SQL procedure successfully completed.
6. Create an Event-Based Job that references the File Watcher.
begin
dbms_scheduler.create_job(
job_name => 'staging_file_job',
program_name => 'file_watcher',
event_condition => 'tab.user_data.file_size > 10',
queue_spec => 'my_file_watcher',
auto_drop => false,
enabled => false);
dbms_scheduler.set_attribute('staging_file_job','parallel_instances',true);
end;
/
7. Enable all objects
begin
dbms_scheduler.enable('my_file_watcher,staging_file_job');
end;
/
8. Perform validation
$ echo "Hello World Hello World" > /home/oracle/staging/test_file.txt
After waiting for about 10-15 minutes,
col UPLOAD_TIMESTAMP format a20 col FILE_NAME format a20 col CONTENTS format a20 select * from t_staging_files; UPLOAD_TIMESTAMP FILE_NAME FILE_SIZE CONTENTS -------------------- -------------------- ---------- ----------------------- 13-OCT-09 01.42.04.0 /home/oracle/staging 23 Hello World Hello World 00000 PM /test_file.txt
By default, the file watcher checks for the arrival of files every 10 minutes. You can adjust this interval as follows:
as SYS user
begin
DBMS_SCHEDULER.SET_ATTRIBUTE('FILE_WATCHER_SCHEDULE','REPEAT_INTERVAL','FREQ=MINUTELY;INTERVAL=2');
end;
/
You can view information about file watchers by querying the views *_SCHEDULER_FILE_WATCHERS.
col FILE_WATCHER_NAME format a20 col DIRECTORY_PATH format a20 col FILE_NAME format a5 col CREDENTIAL_NAME format a17 SELECT file_watcher_name, directory_path, file_name, credential_name FROM dba_scheduler_file_watchers; FILE_WATCHER_NAME DIRECTORY_PATH FILE_ CREDENTIAL_NAME -------------------- -------------------- ----- ----------------- MY_FILE_WATCHER /home/oracle/staging * WATCH_CREDENTIAL
References:
Oracle 11gR2 document: Starting a Job When a File Arrives on a System
Starting a Job When a File Arrives on a System11gR2 New Feature – DBFS Database File System
The DBFS (Database File System), one of the new 11gR2 features, takes advantage of the SecureFiles feature (which is new in 11gR1). The SecureFiles provide powerful file storage features (including de-duplication, compression, etc.) which removes performance barrier to storing files in the database. This stands in contrast to LOB (now called the BasicFiles). Not only does the DBFS provide the standard file system interface (path names, directories and links) to store and access files in the database, its benefits also include security and performance from using SecureFiles.
DBFS configuration
1. Follow the DBFS prerequisites and installation instructions here.
2. Install the FUSE package.
Since oracle user will do sudo, add it into /etc/sudoers.
$ tar -xzvf fuse-2.7.3.tar.gz $ cd fuse-2.7.3 $ ./configure --prefix=/usr/src/kernels/`uname -r`-`uname -p` $ make $ sudo su # make install # /sbin/depmod # /sbin/modprobe fuse # chmod 666 /dev/fuse # echo "/sbin/modprobe fuse" >> /etc/rc.modules
3. Create a database user and a tablespace to store data.
SQL> create user dbfs_admin identified by dbfs_admin;
SQL> grant create session, resource, create view to dbfs_admin;
SQL> grant dbfs_role to dbfs_admin;
SQL> create tablespace tbs_dbfs datafile '/u1/oradata/DB11LNX/dbfs.dbs' size 100m;
The tablespace must be the Automatic Segment Space Management (ASSM) in order to use the SecureFiles. This is the default setting in 11g.
3. Create a file system using dbfs_create_filesystem.sql (located at $ORACLE_HOME/rdbms/admin).
The dbfs_create_filesystem.sql creates a partitioned file system which stores data in the multiple physical segments. The files will be distributed randomly in these partitions. This way it gives the best performance and scalability.
$ cd $ORACLE_HOME/rdbms/admin
SQL> conn dbfs_admin/dbfs_admin
SQL> @dbfs_create_filesystem.sql tbs_dbfs staging_area
Note that the last argument (e.g., staging_area) will be visible as the name of the file system.
When creating a new file system, a new partitioned table will be created having its name from the file system’s name with T_ prefix.
SQL> select table_name, partition_name from user_tab_partitions where table_name like '%STAGING_AREA'; TABLE_NAME PARTITION_NAME ----------------------------- ------------------------------ T_STAGING_AREA SYS_P141 T_STAGING_AREA SYS_P142 T_STAGING_AREA SYS_P143 T_STAGING_AREA SYS_P144 T_STAGING_AREA SYS_P145 T_STAGING_AREA SYS_P146 T_STAGING_AREA SYS_P147 T_STAGING_AREA SYS_P148 T_STAGING_AREA SYS_P149 T_STAGING_AREA SYS_P150 T_STAGING_AREA SYS_P151 T_STAGING_AREA SYS_P152 T_STAGING_AREA SYS_P153 T_STAGING_AREA SYS_P154 T_STAGING_AREA SYS_P155 T_STAGING_AREA SYS_P156 16 rows selected.
4. Verify by copying files into the exposed file system.
SQL> conn dbfs_admin/dbfs_admin SQL> !cat ~/dbfs_show_content.sql col pathname format a40 col pathtype format a10 col contents format a20
select pathname, pathtype, utl_raw.cast_to_varchar2(filedata) as contents from dbfs_content order by std_creation_time;
This shows the default directories currently in database.
SQL> @~/dbfs_show_content.sql PATHNAME PATHTYPE CONTENTS ------------------------------ ---------- ------------------------------ /staging_area directory /staging_area/.sfs directory /staging_area/.sfs/RECYCLE directory /staging_area/.sfs/attributes directory /staging_area/.sfs/content directory /staging_area/.sfs/snapshots directory /staging_area/.sfs/tools directory 7 rows selected.
Let’s create a directory named test_dir. Please note that base directory name staing_area is from the file system created previously. The dbfs_client can be executed from any systems which meet the prerequisite requirements mentioned in the step 1.
{client}$ dbfs_client dbfs_admin@DB11LNX --command mkdir dbfs:/staging_area/test_dir
Password: dbfs_admin
A new directory test_dir is now visible as a new record.
SQL> @~/dbfs_show_content.sql PATHNAME PATHTYPE CONTENTS ------------------------------ ---------- ------------------------------ /staging_area directory /staging_area/.sfs directory /staging_area/.sfs/RECYCLE directory /staging_area/.sfs/attributes directory /staging_area/.sfs/content directory /staging_area/.sfs/snapshots directory /staging_area/.sfs/tools directory /staging_area/test_dir directory 8 rows selected.
Copy a file into it.
{client}$ echo "hello world" > /tmp/dbfs_file
{client}$ dbfs_client dbfs_admin@DB11LNX --command cp /tmp/dbfs_file dbfs:/staging_area/test_dir
Password: dbfs_admin
/tmp/dbfs_file -> dbfs:/staging_area/test_dir/dbfs_file
SQL> @~/dbfs_show_content.sql PATHNAME PATHTYPE CONTENTS ---------------------------------------- ---------- ------------------------------ /staging_area directory /staging_area/.sfs directory /staging_area/.sfs/attributes directory /staging_area/.sfs/tools directory /staging_area/.sfs/snapshots directory /staging_area/.sfs/RECYCLE directory /staging_area/.sfs/content directory /staging_area/test_dir directory /staging_area/test_dir/dbfs_file file hello world
9 rows selected.
Optionally you can also mount this file system on the client, so the file operations can be done without invoking dbfs_client every time.
Create a mount point. (*)
{client}# mkdir /mnt/dbfs
{client}# chown oracle:dba /mnt/dbfs
{client}# chmod 755 /mnt/dbfs
$ dbfs_client dbfs_admin/dbfs_admin@B11LNX /mnt/dbfs password: bdfs_admin :
Somehow on my test system, the prompt never returned even though the file system is mounted successfully. So in order to test it, I left this window open, and execute the remaining of commands in a new window. Note that now I can perform all standard Unix file/directory syntaxes to this mount point.
{client}$ echo "hello world 2" > /tmp/dbfs_file2
{client}$ cp /tmp/dbfs_file2 /mnt/dbfs/staging_area/test_dir
SQL> @~/dbfs_show_content.sql PATHNAME PATHTYPE CONTENTS ---------------------------------------- ---------- ------------------------------ /staging_area directory /staging_area/.sfs directory /staging_area/.sfs/attributes directory /staging_area/.sfs/tools directory /staging_area/.sfs/snapshots directory /staging_area/.sfs/RECYCLE directory /staging_area/.sfs/content directory /staging_area/test_dir directory /staging_area/test_dir/dbfs_file file hello world /staging_area/test_dir/dbfs_file2 file hello world2
10 rows selected.
There are a lot of administrative options you can do from here including using Oracle wallet so no password will be prompted when mounting a DBFS store. You can also mount DBFS through the fstab. See instructions in the Oracle 11gR2’s DBFS File System Client.
One of the claimed benefits of using DBFS is I/O throughput performance in a range of 5-7 GB/sec. I plan to perform performance tests and report the results in the next post. Stay tune!.
(*) Initially, I received this error when trying to run dbfs_client.
dbfs_client: error while loading shared libraries: libfuse.so.2: cannot open shared object file: No such file or directory
By creating a softlink to the /lib folder, it’s solved the problem.
# ln -s /usr/src/kernels/2.6.18-92.el5-i686/lib/libfuse.so.2 /lib/libfuse.so.2
OOW09 Summary and Rookie Mistake
It is good to be back home. The OOW09 trip was exceedingly beyond expectation especially since this was my first OOW. In general, the technical sessions were very good. Most of the time, however, their contents were very basic, I suppose, to accommodate a large audience of diverse skill levels and interests. Besides networking with colleagues and vendors, I would say that the informal settings of the Unconference, and interactions with product managers and developers of DEMOgrounds are what I enjoyed the most about the conference. I made a rookie mistake of registering too many sessions with only short breaks between them. Because of that, at the end of each day, I felt very exhausted. I will definitely plan better for the next one.
Here are a couple of highlights from the conference:
- One of the pictures I took at the event was used in the Oracle OpenWorld Blog’s Wrapping Things Up.
Attendees walking to the keynote
- SF is decidedly an iPhone town.
- In almost every conference I’ve been to, there are always groups of people dressing weird and shouting out something no one really understands or cares about. (You have to ask them in person if you want to know what they really want.) This one is no exception. I still have no idea what this group was doing on the street in front of the Moscone West.
????
- I’m a pen-addict. I’ve not realized this until I found, as I was packing and getting ready to return home, almost 40 pens collected during the visits to vendor exhibits throughout the week. It is my habit to grab pens when stopping by and talking to the vendors. Please help.
- A long line attracts people even if they have no clue what it is for. I’ve encountered this a couple of times when people came behind me as I was standing in line. After 5 minutes of waiting, they would ask me, “What are we waiting for?”
OOW09 Day 4
Today all is about interesting new features of Oracle 11gR2 including Grid Plug and Play (GPnP), Grid Naming Service (GNS), Simple Client Access Name (SCAN), DBFS (Database File System) and so on. I have to admit that all of these are very overwhelming. Some of new features will change the way we’ve done things in the past. It seems like the more you hear about them, the more questions you’re having.
Arup had a great tip about the availability of Oracle product managers, architect and developers at th DEMOgrounds. I had a chance to stop by at the database booths to ask about SCAN and DBFS. The developers there are very patiently helpful talking time to explain things to me clearly. One of them even gave me his business card so I can contact him directly. I’ve spent almost an hour there having lot of questions answered (but still lot of things I didn’t quite grasp it yet). I’ll soon blog about what I’ve learned after trying them out.
Oracle DEMOgrounds
You can view my OOW’s pictures on my Flickr page.
OOW09 Day 3
Today I’ve decided to skip the morning’s keynotes and went for the Unconference. I’ve never attended an Unconference before, but have heard many good things about it from fellow bloggers. And yes they’re absolutely right!. Today’s morning session is the “What’s New in Eleven … Dot Two (that Oracle won’t be talking about)” conducted by Oracle ACE Director Daniel Morgan. (You can’t not go just by reading that session’s subject.) ;-) The informal atmosphere and a smaller number of participants encourage more interaction which is very great. Daniel is a great resource. His web site – Morgan’s Library has wealth of information about Oracle especially new features in 11g both R1 and R2 (see New in 11gR1 & 11gR2 tabs from his site). I’ll definitely go back for more of Unconferences.
On a lighter note, I (and a lot of people) came to one of the evening sessions 5 minutes late. But somehow the way seats were arranged, it made it very difficult to get into the empty chairs so most people including myself ended up standing in the back of the room. I would assume that in a conference room setting, they would arrange to have at least the access in the middle and on both sides. This way people can get in/out more easily. May be the facility folks thought that if we added more chairs on the sides (leaving no room on the wall side), we would be able to accommodate more people. Brilliant! Ha? This good intention backfired because even though there are more chairs in the room, now there are more empty seats (less utilization) because of less access. Um… I don’t know where I’m going from here. But somehow I feel there is a lesson learned here that good intentions are not enough to accomplish a task, a good design is needed too.
Bad Seat Arrangement?
OOW09 Day 2
Today all my sessions are pretty much centered around Oracle ASM. Even though all sessions started with basic information about ASM (what is, why, and so on), I found the Q&A time at the end more interesting. One of the sessions – “Extending Oracle ASM in Release 11.2 to Manage All Data” has the panel of the ASM development team for Q & A. It is awesome to see these people who are responsible for this cool product face-to-face.
Oracle ASM Development Team
A common question I’ve heard over and over is that how many diskgroups Oracle recommends in a deployment. The panel said that regardless of the number of databases, still only two diskgroups are recommended as the best practice – one diskgroup for work area (e.g., data files), other for recovery (e.g., flash recovery). The exception of more-than-two diskgroups is if organization has ILM strategy – putting disks of different tiers into different diskgroups based on requirement and strategy.
A quick note from the ASM development team while they’re talking about the ACFS snapshot is that they’re working on more of data services around ACFS such as storage replication. We should hear about it soon.
OOW09 Day 1
My first day at OOW 2009 was filled with excitement intellectually and physically. Let’s talk about the physical part first. As I’m staying at Marriott hotel in Oakland, I have to take BART from Oakland to Powell station. Unfortunately, I did not realize that only on Sunday the train station near my hotel won’t be open until 8am. So by the time of a train ride and VERY fast walking (BTW, I’m just recovering from cold) to the Moscone center, I’ve arrived about 20 minutes late for the first session. 
Anyway after that the rest of the day is fun.
My first session is the Demystifying Oracle RAC workload management by Alex Gorbachev. I really enjoyed his presentation especially demos on the connection load balancing (CLB). I’ve learned techniques how to control the CLB’s behaviors in both pre-10gR1 and post-10gR2. His presentation is a must-see if you did not attend his session. At least, you will know what “goodness” (or badness) in the context of CLB means.
Arup Nanda showed us in his Oracle RAC Performance Tuning the real case studies of RAC performance issues, and how he systematically tackled them. Besides good planning, he recommended that don’t jump into a conclusion by just looking at the AWR wait names without actual diagnostic (a specific sample in the session is that don’t get fooled by “gc” waits considering them as the interconnect issues).
At the end of the day, not only I stopped by at the demo of the full-rack of Exadata version 2 in the Moscone North to see this product in action, but I also had a chance to say hello to Dan as he is now a part of the X-Team.
Here are some pictures from the events:
Oracle Open World 2009 at the Moscone Center
Oracle Banners outside the Moscone Center
Registration Counters
View of the Moscone Center from Yerba Buena Gardens
Attendees are walking to the keynote
Attendees are heading to keynote's conference room
Goodies at the Welcome Reception
APEX Listener Released as an Early Adopters
Oracle APEX Listener has been released as an early adopters. It is an alternative to Oracle HTTP Server (OHS) and Oracle Embedded PL/SQL Gateway (EPG) as web server to APEX database engine. You may find more information about this at the Oracle’s APEX Listener Download site, or Dimitri’s or John’s blogs.
I just configured the APEX listener with Oracle WebLogic 11g as I would like to compare it with the existing OHS environment. The instructions coming with the download for WebLogic is very straightforward except the last one which asks to recursively copy the apex/images directory to the webserver’s /i. My initial setup shows the logon screen but no images displayed.
I have to admit that I’m a WebLogic novice. My simple workaround to fix this image issue is to make all APEX images into a new application called “i”. This simply can be done by creating a war file of all images under apex/images directory.
F:\DOWNLOAD\apex_3.1.2\apex\images>jar -cvf i.war *.*
Using this i.war file, you just simply install it as a normal application.
If anyone out there have better approaches, please feel free to share.
The Access Control Lists to Network Services (e.g., UTL_HTTP, UTL_SMTP, UTL_TCP, etc.) in Oracle 11g
This is one of the 11g features I read it once when it was first released but did not see its significance until now. Last week we just migrated an application from 9i to 11g. During a test of the send mail package using UTL_SMTP, we got this error, “ORA-24247: network access denied by access control list (ACL).” After a quick search, I’m in luck because I found a lot of articles written about this new 11g feature. However, I particularly find these two well-written concepts and samples from Arup Nanda’s Access Control Lists for UTL_TCP/HTTP/SMTP and Oracle-Base’s Fine-Grained Access to Network Services in Oracle Database 11g Release 1 very helpful.
My sample here is from our test case:
1. The send mail package which executes the UTL_SMTP failed.
TEST_USER SQL> exec pkg_LoadStatus.SendMail('user@company.com', 'Test Subject', 'Hello World');
ORA-24247: network access denied by access control list (ACL)
ORA-06512: at "SYS.UTL_TCP", line 17
ORA-06512: at "SYS.UTL_TCP", line 246
ORA-06512: at "SYS.UTL_SMTP", line 115
ORA-06512: at "SYS.UTL_SMTP", line 138
ORA-06512: at "pkg_LoadStatus", line 283
ORA-06512: at line 3
2. To fix it, an ACL has to be created.
The principal is the user or role to be added into this ACL. In this case, the TEST_USER account is added during the ACL creation. This field is case sensitive.
SQL> connect / as sysdba begin dbms_network_acl_admin.create_acl ( acl => 'Mail_UTL_Access.xml', description => 'Mail UTL Network Access', principal => 'TEST_USER', is_grant => TRUE, privilege => 'connect', start_date => null, end_date => null ); commit; end; /
The description of each variable is clearly described in the Oracle-Base’s article.
3. Verify a newly-created ACL.
SQL> SELECT any_path
FROM resource_view
WHERE any_path like '/sys/acls/%.xml';
ANY_PATH
--------------------------------------------------------------------------------
/sys/acls/Mail_UTL_Access.xml
/sys/acls/OLAP_XS_ADMIN/OLAP_XS_ADMIN602a67cf3684e24e04403ba6c65c6_acl.xml
/sys/acls/OLAP_XS_ADMIN/OLAP_XS_ADMIN602a67cf36e4e24e04403ba6c65c6_acl.xml
/sys/acls/OLAP_XS_ADMIN/OLAP_XS_ADMIN602a67cf3724e24e04403ba6c65c6_acl.xml
/sys/acls/OLAP_XS_ADMIN/OLAP_XS_ADMIN602a67cf3764e24e04403ba6c65c6_acl.xml
/sys/acls/all_all_acl.xml
/sys/acls/all_owner_acl.xml
/sys/acls/bootstrap_acl.xml
/sys/acls/ro_all_acl.xml
/sys/acls/ro_anonymous_acl.xml
4. Optionally you can add more users or roles into this ACL by using the add_privilege procedure. This is similar to the create_acl procedure except no description. Sample shown here is to add ADMIN_ADMIN_ROLE role.
begin dbms_network_acl_admin.add_privilege ( acl => 'Mail_UTL_Access.xml', principal => 'APP_ADMIN_ROLE', is_grant => TRUE, privilege => 'connect', start_date => null, end_date => null); commit; end; /
5. Add a host and port range allowed.
begin dbms_network_acl_admin.assign_acl ( acl => 'Mail_UTL_Access.xml', host => 'smtp.company.com', lower_port => 1, upper_port => 1024); commit; end; /
6. Test the send mail package again. This time there is no error, and the recipient receives email.
TEST_USER SQL> exec pkg_LoadStatus.SendMail('user@company.com', 'Test Subject', 'Hello World');
PL/SQL procedure successfully completed
Online SAN Storage Migration for Oracle 11g RAC database with ASM
It has been awhile since my last post. My pathetic excuses are all pretty much mentioned here.
Last month we’ve worked with the storage team to migrate the SAN storage of our Oracle 11gR1 database to a new one. The drive of migration is mainly for SAN consolidation which is, of course, ultimately for cost saving. In addition to migrating the ASM disk groups storing database’s data files, all clusterware files (OCR and voting disk) must be migrated too. The rebalance feature in Oracle ASM makes data migration very easy and seamless. And since the clusterware files have redundancy, they can be seamlessly migrated as well. With 11gR1, all migration tasks can be performed online.
Prerequisites/Assumptions:
- New SAN LUNs/disks are already visible to all RAC nodes. In case of the disks for ASM diskgroups, they are already discovered by ASM. The minimum numbers and permissions of the OCR and voting disks must be met.
- It is recommended to perform the migration tasks during off-peak hours or even better if during planned maintenance window period.
Note that the sample shown here is specific to my environment (11.1.0.7 on Solaris 10 with dual-pathing to Hitachi SAN, and OCR and voting disks are on raw devices).
SAN Migration of the ASM diskgroups
If you’re more comfortable with GUI, all tasks here can be accomplished using the Enterprise Manager.
1. Add new disks to ASM diskgroups.
ALTER DISKGROUP PMDW_DG1 ADD DISK '/dev/rdsk/c4t60060E80056FB30000006FB300000823d0s6' NAME PMDW_DG1_0003, '/dev/rdsk/c4t60060E80056FB30000006FB300000826d0s6' NAME PMDW_DG1_0004, '/dev/rdsk/c4t60060E80056FB30000006FB300000829d0s6' NAME PMDW_DG1_0005 REBALANCE POWER 11;
We go with the rebalance power of 11 which is full throttle because it is planned maintenance.
2. Check rebalance status from Enterprise Manager or v$ASM_OPERATION.
3. When rebalance completes, drop the old disks.
ALTER DISKGROUP PMDW_DG1 DROP DISK PMDW_DG1_0000, PMDW_DG1_0001, PMDW_DG1_0002 REBALANCE POWER 11;
When adding or removing several disks, it is recommend to add or remove all disks at once. This is to reduce the number of the rebalance operations that are needed for storage changes.
SAN Migration of the OCR Files
1. Backup all OCR-related files.
# {CRS_HOME}/bin/ocrcheck
Status of Oracle Cluster Registry is as follows :
Version : 2
Total space (kbytes) : 921332
Used space (kbytes) : 4548
Available space (kbytes) : 916784
ID : 776278942
Device/File Name : /dev/rdsk/c4t50060E800000000000002892000003F8d0s6
Device/File integrity check succeeded
Device/File Name : /dev/rdsk/c4t50060E800000000000002892000003F9d0s6
Device/File integrity check succeeded
Backup the /var/opt/oracle/ocr.loc file:
# cp ocr.loc ocr.loc.old
Manually backup OCR:
# {CRS_HOME}/bin/ocrconfig -manualbackup
2. As root, run the following commands to replace OCR files. This change can be performed on-line, and will be reflected across the entire cluster.
# {CRS_HOME}/bin/ocrconfig -replace ocr /dev/rdsk/c4t60060E80056FB30000006FB300001014d0s6
# {CRS_HOME}/bin/ocrconfig -replace ocrmirror /dev/rdsk/c4t60060E80056FB30000006FB300001015d0s6
3. Verify the new configuration.
Check new ocr.loc file updated:
# cat /var/opt/oracle/ocr.loc #Device/file /dev/rdsk/c4t50060E800000000000002892000003F9d0s6 getting replaced by device /dev/rdsk/c4t60060E80056FB30000006FB300001015d0s6 ocrconfig_loc=/dev/rdsk/c4t60060E80056FB30000006FB300001014d0s6 ocrmirrorconfig_loc=/dev/rdsk/c4t60060E80056FB30000006FB300001015d0s6
Check OCR:
# {CRS_HOME}/bin/ocrcheck
Status of Oracle Cluster Registry is as follows :
Version : 2
Total space (kbytes) : 921332
Used space (kbytes) : 4548
Available space (kbytes) : 916784
ID : 776278942
Device/File Name : /dev/rdsk/c4t60060E80056FB30000006FB300001014d0s6
Device/File integrity check succeeded
Device/File Name : /dev/rdsk/c4t60060E80056FB30000006FB300001015d0s6
Device/File integrity check succeeded
Cluster registry integrity check succeeded
Logical corruption check succeeded
SAN Migration of the voting disks
1. Backup the voting disks.
Query the original locations:
# /opt/oracrs/bin/crsctl query css votedisk 0. 0 /dev/rdsk/c4t50060E800000000000002892000003FBd0s6 1. 0 /dev/rdsk/c4t50060E800000000000002892000003FCd0s6 2. 0 /dev/rdsk/c4t50060E800000000000002892000003FFd0s6
Backup voting disks using dd:
dd if={voting_disk_name} of={backup_file_name}
Example,
dd if=/dev/rdsk/c4t50060E800000000000002892000003FBd0s6 of=/tmp/voting1
2. Move voting disks.
Starting with 11.1 onwards, the voting disk migration can be performed on-line.
# /opt/oracrs/bin/crsctl delete css votedisk /dev/rdsk/c4t50060E800000000000002892000003FBd0s6 # /opt/oracrs/bin/crsctl add css votedisk /dev/rdsk/c4t60060E80056FB30000006FB300001017d0s6 # /opt/oracrs/bin/crsctl delete css votedisk /dev/rdsk/c4t50060E800000000000002892000003FCd0s6 # /opt/oracrs/bin/crsctl add css votedisk /dev/rdsk/c4t60060E80056FB30000006FB300001018d0s6 # /opt/oracrs/bin/crsctl delete css votedisk /dev/rdsk/c4t50060E800000000000002892000003FFd0s6 # /opt/oracrs/bin/crsctl add css votedisk /dev/rdsk/c4t60060E80056FB30000006FB300001019d0s6
3. Verify the new configuration.
# /opt/oracrs/bin/crsctl query css votedisk 0. 0 /dev/rdsk/c4t60060E80056FB30000006FB300001017d0s6 1. 0 /dev/rdsk/c4t60060E80056FB30000006FB300001018d0s6 2. 0 /dev/rdsk/c4t60060E80056FB30000006FB300001019d0s6
Reference:
Metalink #428681.1: OCR / Vote disk Maintenance Operations: (ADD/REMOVE/REPLACE/MOVE), including moving from RAW Devices to Block Devices