Write REF CURSOR to file
This is just a very simple procedure that makes writing a lot of files using UTL_FILE much simpler. It can be used to easily create simple CSVs or fixed-length files.
I’ve shown it here in a package body; it’s up to you to create a package spec.
To use it, all you need to do is create a query that concatenates all the data into a single string up to 4000 characters long. If you’re writing a CSV you need to concatenate commas and quotes as appropriate.
CREATE OR REPLACE PACKAGE BODY packagename AS-- If no records are found in the cursor, no file is created.
PROCEDURE write_cursor_to_file
(outputdir IN VARCHAR2
,filename IN VARCHAR2
,headerline IN VARCHAR2
,refcursor IN SYS_REFCURSOR
) IS-- increase to make faster but use more memory;
-- decrease to use less memory but run slower
BATCHSIZE CONSTANT INTEGER := 100;
TYPE varr_type IS TABLE OF VARCHAR2(4000) INDEX BY BINARY_INTEGER;
varr varr_type;
outf UTL_FILE.FILE_TYPE;
BEGIN
-- note: don't open the file unless we actually get some
-- records back from the cursor
LOOP
FETCH write_cursor_to_file.refcursor
BULK COLLECT INTO varr
LIMIT BATCHSIZE;
EXIT WHEN varr.COUNT = 0;-- We have some records to write. Have we opened the file yet?
IF NOT UTL_FILE.IS_OPEN (outf) THEN
outf := UTL_FILE.fopen
(file_location => write_cursor_to_file.outputdir
,file_name => write_cursor_to_file.filename
,open_mode => 'A'
,max_linesize => 4000);
UTL_FILE.put_line (outf, write_cursor_to_file.headerline);
END IF;-- Write the batch of records to the file
FOR i IN 1..varr.COUNT LOOP
UTL_FILE.put_line (outf, varr(i));
END LOOP;END LOOP;
CLOSE write_cursor_to_file.refcursor;
IF UTL_FILE.IS_OPEN (outf) THEN
UTL_FILE.fclose (outf);
END IF;
END write_cursor_to_file;PROCEDURE sample IS
OUTPUTDIR CONSTANT VARCHAR2(100) := 'MY_DIR_NAME';
FILENAME CONSTANT VARCHAR2(100) := 'my_file_name.csv';
HEADERLINE CONSTANT VARCHAR2(4000) := 'Name,Address,Date of Birth';
refcursor SYS_REFCURSOR;
BEGIN
OPEN refcursor FOR
select '"' || name || '"'
|| ',"' || address || '"'
|| ',' || TO_CHAR(dob,'DD-Mon-YYYY')
from persons;write_cursor_to_file
(outputdir => OUTPUTDIR
,filename => FILENAME
,headerline => HEADERLINE
,refcursor => refcursor
);
END sample;END packagename;
/
It appends to the file if it finds it. This makes it easy to write the result of several queries to the same file.
Note: if you're on Oracle 8i or earlier, you'll need to add a replacement for SYS_REFCURSOR, e.g. TYPE my_sys_refcursor IS REF CURSOR; either at the top of the package, or if you want to make the write_cursor_to_file procedure public, put the type definition in your package spec.
AUSOUG Perth Conference 2011 Day Two
The final day at Burswood was just as enjoyable as day one. Well done to all the AUSOUG committee!
I started with two Apex talks – first, Scott Wesley on Apex 4.1 Security. Personally I very much enjoyed the unique presentation style. You can experience it for yourself here. After that, Mark Lancaster from Queensland gave his analysis of the changes from Apex 4.0 to 4.1, and commented on a number of new features that have been added or improved.
Just before lunch I caught “Tips and Best Practices for DBAs” by Francisco Munoz Alvarez, who spoke less about actual DBA tasks (as I was expecting) but more about the “soft” skills – attitude, professionalism, working in a team, delegating tasks, and automating everything.
After lunch Vinod Patel moderated a discussion panel comprising Debra Lilley, Tim Hall, Connor McDonald, Penny Cookson, Chris Muir, and a guy from Oracle (whose name escapes me for the moment) – and they were plied with many questions about the art of presenting. It was encouraging to hear what they had to say, both about their success and their failure stories. I think I got away with taking this photo without them noticing :)
I took in Graham Wood‘s final presentation, a live demo of Exadata. He demonstrated how blazingly fast it is for loading huge amounts of data in a very short time (e.g. 500GB in about 10 minutes IIRC) and running horrible queries even faster (e.g. multiple full table scans with self joins, running in mere seconds). It was very impressive, although it did highlight that to get the full benefit of Exadata, some queries may need to be rewritten. For example, a big report you’re running now might get a modest x10 or x20 speed improvement on Exadata, but after rewriting you could get on the order of x100 to x200 speed improvements! If you don’t believe me, go ask Graham yourself :)
The day ended with Connor McDonald‘s talk, A Year in Purgatory – Diary of an 11.2 RAC Upgrade. It held a lot of promise, but unfortunately I was called away to an emergency at work so I missed most of it. I was quite disappointed to miss that one. By the way, Connor is now blogging – at connormcdonald.wordpress.com. Finally!
I’ve enjoyed each AUSOUG conference since 2000, and this year was one of the best in my opinion. It was great to catch up with colleagues, network with Oracle nerds and professionals, and get inspired by a variety of talks on topics I’m interested in.
In addition, the last few years I’ve also presented. This has been a good experience which I intend to continue. I hope that with practice I’ll get much better at it.
AUSOUG Perth Conference 2011 Day One
I had a most enjoyable* day today at the Oracle conference at Burswood, Perth.
* This is significant in light of the fact that the start of the day was quite hectic, having had to drop the car off to get a new alternator (the electrics died on the way home from work yesterday, so this morning I called RAC out to get the car started and charge the battery up a bit. I managed to drive the car within 100m of Ultratune, then it died completely. So I left the car in the traffic and walked the rest of the way, borrowed some kind of block box that was like a CPR machine for cars.), thereafter going with Rosalie to drop the eldest at her school, so she could then gift me a lift to Burswood.
I got to the conference in time to hear Graham Wood from Oracle talk about DB Time-based Oracle Performance Tuning, which was excellent, then Tim Hall on Edition-Based Redefinition which led to some excellent discussions with both Tim and colleagues from my current client.
My talk on a simple ETL method using just SQL seemed to go well and there were some good questions, comments and suggestions. If you missed something it was probably because I talk too fast – if you want to look something up from the talk you can look at the slides here, or have a play with a full SQL*Plus demo [ETL-by-SQL-sqlplus-demo.zip]. I also blogged about this technique back in February.
I finished the day with Graham Wood’s second talk, on Oracle Hidden Features – which indeed included info on some Oracle features that I’d either not yet come across, or had forgotten about. Doug Burns blogged about this presentation earlier. The bit about external tables, especially the new 11g feature for running the source file through a preprocessor, was particularly interesting.
I’m looking forward to the second day tomorrow.
Googlebot, Apex Session IDs, and Cookies
Recently I had a bit of a saga with a public-facing website running on Oracle Apex (www.foothillschurch.org.au, if you’re curious) getting hammered by Googlebot. We went live with a new version of the site, but I’d forgotten to make sure that all the links set the Session ID to 0 (zero).
What is this session ID?
Every visit to an Apex application needs to have a session. Each unique session is recorded in the database, and keeps track of the state of all the variables for the various pages you visit. Normally, the session is identified by a Session ID which is embedded in the “p” parameter of the URL.
For example, if you visit the page:
http://www.foothillschurch.org.au/apex/f?p=102:1
You’ll notice that the “p” parameter only specifies the app ID (102) and the page ID (1). What apex does is responds with a 302 temporary redirect, that tells the client to redirect to a new URL containing a newly generated session ID, e.g.:
http://www.foothillschurch.org.au/apex/f?p=102:1:45164531548964:::::
Behind the scenes, it’s not just changing the URL – it’s also sending a cookie to the client to be used for subsequent calls. I’ll get back to this later.
Whenever you navigate around a normal apex site, the session ID gets copied into each link so that the user’s session is preserved. If you manually change or remove the session ID from the URL, apex will redirect you to a newly created session ID.
In case you’re wondering, there’s no significant security risk behind the exposed session ID – no-one can “hijack” your session, even if they copy your session ID directly. That’s because there’s a cookie behind the scenes with a secret value that must match up with the session ID, and only someone with sufficient access to the database server could get enough data to do that.
If you store the URL containing a session ID (e.g. in your bookmarks) and re-use it much later, your session will have expired – in which case apex will create a new session, and 302 temporary redirect you to a new URL with the new session ID. Therefore, users can safely bookmark any page in apex.
But now we come, finally, to Googlebot, that little rascal. Now, we would like our public-facing site to be indexed by Google, so we need all the pages of the site that have relevant info to be crawlable.
The way Googlebot works, normally, is that it starts from a link to your page (e.g. on another website, or in a sitemap you submit to Google), e.g.
http://www.foothillschurch.org.au/apex/f?p=102:1
It checks that the URL is not forbidden by your robots.txt, and sends a request to your server for it. If the response is 200 OK and has a body, Googlebot indexes the page contents, extracts any links from it, and crawls them. Actually, it doesn’t crawl them straight away – it just adds them onto the end of a queue somewhere to be crawled later.
If the response is a 4xx (permanent error) or 5xx (temporary error), Googlebot seems to put the URL back on the queue for a few more goes before it gives up.
If the response is a 3xx redirect, and this is the kicker, Googlebot does not always perform the redirect straight away. It may take the new URL and just add it onto the end of the queue to be crawled later. It seems to me (based on what I’ve seen in my apache logs) that if the URL is different from the first, Googlebot will queue it up for later; but if the URL is identical, it will usually try it straight away.
Are You Ready? Upcoming Conference in Perth
Just a quick note to draw attention to the lineup for AUSOUG National Conference – 3-4 November at the luxurious Burswood Resort here in Perth. The usual suspects will be there, for example:
- Chris Muir on Angels
- Tim Hall on a potential Killer extension
- Scott Wesley with Deeper Discussions (on Apex Security)
- Penny Cookson helping Oracle devs and Java devs work happily together
- Connor McDonald on A Year In Purgatory
- and myself :)
Signs of the coming Apocalypse? Who knows….
Plan your trip (and arrange your accommodation early, if you need it) and your conference schedule today!
Installing Oracle 11gXE on Amazon Elastic Cloud
There is officially now no good excuse for not running your own instance of Oracle 11g and getting into Apex 4 development, or just playing with SQL and PL/SQL.
- Oracle 11g XE is free.
- You can rent the hardware to run it by the hour, at a fraction of the cost of most online hosting services.
- It’s easy to install – just use this step-by-step guide!
1. Gather your software.
You’ll need:
- Oracle Database Express Edition 11g Release 2 for Linux x64.
The file name will be oracle-xe-11.2.0-1.0.x86_64.rpm.zip, and is about 308MB. - PuTTY
- WinSCP (or any SCP/SFTP client for your OS)
2. Get your Amazon EC2 account.
3. Launch Instance.
4. Set up your key pair.
5. Upload the Oracle installer to the instance.
6. Login to your instance.
7. Install Oracle.
http://download.oracle.com/docs/cd/E17781_01/install.112/e18802/toc.htm
If you just want to know what to type, here is basically all you need (type the commands after the “#” – don’t type the “#” itself though):
# unzip /root/oracle-xe-11.2.0-1.0.x86_64.rpm.zip # rpm -ivh /root/Disk1/oracle-xe-11.2.0-1.0.x86_64.rpm # /etc/init.d/oracle-xe configure
This script will prompt you for the Apex port and the database port – just press Enter to accept the defaults (8080 and 1521, respectively). It will then ask for the password for SYS and SYSTEM. Finally, it will ask you if you want it to run on startup. Accept the default (yes).
8. Disable the firewall.
# chkconfig iptables off # service iptables stop
This disables the internal firewall running in your linux instance.
9. Log into Apex.
Open your browser and point it at your instance. Take the Public DNS and add :8080/apex/apex_admin, e.g.:
http://ec2-72-44-48-28.compute-1.amazonaws.com:8080/apex/apex_admin
The login name is admin, and the password is (for now) the same password you entered for the SYS and SYSTEM accounts earlier.
It will prompt you to change the password. It has to be a fairly secure – you’ll need at least a letter, number and symbol, and you can’t include the login name. After changing your password, you’ll need to log in again but using the new password.
You now have a fully-fledged Oracle Application Express environment, version 4.0.
10. Create a Workspace.
The last thing you need to do before you can start creating applications and stuff is to create a workspace. Click “Create Workspace” and answer the prompts to create the workspace along with its own schema and admin user account.
Log out of Apex Administration, and log into the newly created workspace – and you will now be able to start creating applications, tables, and what-have-you. You can also use Apex to write code (such as procedures and packages), but I suggest you use Oracle SQL Developer for this kind of thing because it’s a bit more powerful.
From now on, to get back into your Apex development environment, you can use this url:
http://ec2-72-44-48-28.compute-1.amazonaws.com:8080/apex
Have fun!
Oh, by the way: when you’ve had your fun, and you want to stop your instance (which at about 34c/h may cost around $60 per week if you leave it running 24×7), don’t forget to terminate it from AWS Management Console. Before you do, however, you can take a snapshot of it in its current state so you can restore from it later. (Before this, you might want to shut down the Oracle database within the instance first – although I’ve found it quite easy to recover an instance which was snapshotted while the database was up and running). To do that, right-click on the instance in AWS Management Console, and select “Create Image (EBS AMI)”. It may take some time for the image to be created. When the image creation is complete, you can then Terminate the instance. The AMI will take up a bit of storage that will cost a dollar or two per month; and you can now spin up as many copies of your machine whenever you want.
Update:
Lew posted an excellent introduction to PuTTY and SSH for AWS Newbies that I heartily recommend. It was posted in 2009 but still applies.
MERGE to table with Virtual Columns + Error Log Table = ORA-03113
This is just in case you come across this. It appears to be an Oracle bug. I don’t know if it’s been fixed in a later release. I haven’t isolated a simple repeatable test case yet, so I don’t know if the scenario here includes all the relevant details or not.
Version: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 – 64bit Production
Scenario:
- MERGE into a table that has one or more virtual columns.
- The MERGE statement has a “LOG ERRORS” clause.
- The error log table is a standard one created using DBMS_ERRLOG.create_error_log.
Other factors that may or may not be involved are:
- the target table is in a different schema
- the merge statement queries another table with the same name in the current schema
- the merge statement includes WHEN MATCHED THEN UPDATE, DELETE WHERE, WHEN NOT MATCHED THEN INSERT cluases
- the merge statement has a WHERE clause on the WHEN NOT MATCHED clause
Result:
ORA-03113: end-of-file on communication channel and/or ORA-03114: not connected to ORACLE
This happens whether the MERGE is executed or just explain-plan’ed – so it shouldn’t be related to the actual data in either the source or destination tables.
After creating the log table, but before the merge, drop the columns from the error log that are virtual columns in the target table.
Quick and simple frequency analysis
I use this simple query quite often when exploring the data in a table in any Oracle database (from Oracle v8 onwards):
select q.*, 100 * ratio_to_report(c) over () rtr from (select distinct v, count(*) over (partition by v) c from ( select MYCOLUMN v from MYTABLE )) q order by c desc;
Just substitute the table name for “MYTABLE” and the column you’re interested in for “MYCOLUMN”. This gives a frequency analysis of values, e.g.:
V C RTR ======== ====== ============= INACTIVE 401001 92.9254049544 ACTIVE 30529 7.0745950455
V is the value from the column. C is the count of how many times that value appeared. RTR is the % ratio to the total. The first row indicates the most popular value.
If it’s a very large table and you want quicker results, you can run the analysis over a smaller sample easily, just by adding the SAMPLE keyword:
... select MYCOLUMN v from MYTABLE SAMPLE(1) ...
Add business days
It starts out as a fairly simple, innocent business requirement. Create a report to list records meeting some criteria, one of which is:
“List only records where today’s date is more than 35 business days after the due date from the record.”
When you delve deeper you find that querying the table with “DUE_DATE + 35 < SYSDATE” is not going to cut it – “business days” do not include weekends. You might start with something similar to this. But even that’s not good enough, because business days should not include public holidays. How do you code that?
So, here’s my solution.
1. We need to know what days are public holidays for the region. In our case this application is only applicable for a single region, so we use a simple table:
CREATE TABLE holidays (holiday_date DATE PRIMARY KEY);
We create a simple form for users to enter new holidays every year, and give someone the job of making sure it’s up-to-date every year when the public holidays are announced.
2. Create a view that lists all non-business days – i.e. list all weekends and public holidays. To retain reasonable performance, we limit our solution to dates in the years 2000 to 2050.
CREATE VIEW non_business_days AS
SELECT TO_DATE('01012000','DDMMYYYY') + ROWNUM * 7
AS day -- Saturdays 2000 to 2050
FROM DUAL CONNECT BY LEVEL <= 2661
UNION ALL
SELECT to_date('02012000','DDMMYYYY') + ROWNUM * 7
AS day -- Sundays 2000 to 2050
FROM DUAL CONNECT BY LEVEL <= 2661
UNION ALL
SELECT holiday_date FROM holidays;
3. Now, when we need to take a date and add x business days to it, we query this table to find all the non-business-days that are applicable, e.g.:
SELECT day
,COUNT(*) OVER (ORDER BY day
ROWS BETWEEN UNBOUNDED PRECEDING
AND CURRENT ROW)
AS count_so_far
,(day - p_date) AS base_days
FROM NON_BUSINESS_DAYS
WHERE day > p_date;
If you run this query and examine each row in order of day, if you take base_days and subtract count_so_far, when the result is less than x, then base_days – count_so_far is the number of extra days we need to add to the holiday’s date to give us the answer. You’ll find this logic in the function below.
In our final solution, we’ll also need to UNION in the date parameter as well, for the case where there are no holidays between the starting date and the number of business days requested.
Here’s our function to take any date (at least, any date between 2000 and 2050) and add x business days (positive or negative):
FUNCTION add_working_days (p_date IN DATE, p_working_days IN NUMBER)
RETURN DATE IS
l_date DATE;
BEGIN
IF p_date IS NULL OR p_working_days IS NULL THEN
RETURN NULL;
END IF;
IF p_working_days != TRUNC(p_working_days) THEN
RAISE_APPLICATION_ERROR(-20000,
'add_working_days: cannot handle fractional p_working_days ('
|| p_working_days || ')');
END IF;
IF p_working_days > 0 THEN
SELECT MAX(day + p_working_days - (base_days - count_so_far))
INTO l_date
FROM (SELECT day
,COUNT(*) OVER (ORDER BY day
ROWS BETWEEN UNBOUNDED PRECEDING
AND CURRENT ROW)
AS count_so_far
,(day - p_date) AS base_days
FROM NON_BUSINESS_DAYS
WHERE day > p_date
UNION
SELECT p_date, 0, 0 FROM DUAL
)
WHERE base_days - count_so_far < p_working_days;
ELSIF p_working_days < 0 THEN
SELECT MIN(day - (ABS(p_working_days) - (base_days - count_so_far)))
INTO l_date
FROM (SELECT day
,COUNT(*) OVER (ORDER BY day DESC
ROWS BETWEEN UNBOUNDED PRECEDING
AND CURRENT ROW)
AS count_so_far
,(p_date - day) AS base_days
FROM NON_BUSINESS_DAYS
WHERE day < p_date
UNION
SELECT p_date, 0, 0 FROM DUAL
)
WHERE base_days - count_so_far < ABS(p_working_days);
ELSE
l_date := p_date;
END IF;
RETURN l_date;
END add_working_days;
Test cases (these are some public holidays in Western Australia):
insert into holidays values (to_date('27/12/2010','DD/MM/YYYY');
insert into holidays values (to_date('28/12/2010','DD/MM/YYYY');
insert into holidays values (to_date('03/01/2011','DD/MM/YYYY');
insert into holidays values (to_date('26/01/2011','DD/MM/YYYY');
– Expected: 06/01/2011
select cls_util.add_working_days(to_date('13/12/2010','DD/MM/YYYY')
,15) from dual;
– Expected: 31/01/2011
select cls_util.add_working_days(to_date('25/01/2011','DD/MM/YYYY')
,3) from dual;
– Expected: 13/12/2010
select cls_util.add_working_days(to_date('06/01/2011','DD/MM/YYYY')
,-15) from dual;
– Expected: 25/01/2011
select cls_util.add_working_days(to_date('31/01/2011','DD/MM/YYYY')
,-3) from dual;
Converting instance-store to EBS-backed on Amazon
For a long time my disaster recovery procedure for my Amazon EC2-based web site was:
- Find an Oracle AMI that has 10g XE with Apex pre-installed, and start up an instance with it.
- Create a volume from a backup snapshot and attach it to the new instance.
- Log into the instance, shut down apache and oracle, then delete all the oracle data files, apache config files, and a few other bits and pieces.
- Create symbolic links for the deleted bits and pieces (including the oracle data files) that point to the attached volume.
- Start up oracle and apache and test.
- Switch the elastic IP over to the new instance.
This procedure has been tested and retested multiple times, and came in useful once when I almost lost the site (actually, it just went unresponsive due to some general problems at Amazon, but at the time I thought it’d had gone down).
Last week I logged in to the AWS Management Console to do a routine backup-and-restore test, but discovered a problem: it couldn’t find the Oracle 10g XE AMI. Searches on the image ID and various keywords, across all the Amazon regions, returned no results. Searches on “oracle” brought back a number of options but none of them close to what I required. I enquired with Amazon and they responded that the AMIs are supplied by Oracle and had been removed. I discovered this meant that not only could I not start up an instance using one of these images, I also couldn’t point to my running instance and “start up another like this” – because this requires access to the original AMI that was used to start it.
The machine image which I was trying to find is (at least, as of today 5 Jul 2011) still referred to here: http://aws.amazon.com/amis/Oracle/1720 “Oracle Database 10g Release 2 Express Edition – 32 Bit” so I raised a question on the oracle forum (http://forums.oracle.com/forums/thread.jspa?messageID=9707298�) and sent an email to Bill Hodak at Oracle who was named in the description of the AMI. He replied he would see if he could find out what had happened to it.
At this point I was hoping that my running instance wouldn’t go down, because I didn’t know if I’d be able to restore from backup. My backup consisted solely of snapshots of just the data – the rest of the OS was supposed to be provided by the AMI.
Meanwhile, asam replied to my oracle forum thread, suggesting I create my own AMI. A bit of googling yielded this result, which proved very helpful: http://webkist.wordpress.com/2010/03/16/creating-an-amazon-ec2-ebs-ami-from-a-running-instance/ “Creating an Amazon EC2 EBS AMI from a running instance”. I followed the instructions, slightly modified as follows:
- Use AWS Management Console to create a new volume
- Attach the volume to my running instance and mount it:
# mkdir /u03 # mount -t ext3 /dev/sdf /u03
- Move everything from the old volume so that it all sits under / again instead of via symbolic links
- Synchronize the filesystem to the new volume:
# rsync -a --delete --progress -x / /u03
- When rsync has completed, fix up the devices:
# MAKEDEV -d /u03/dev -x console # MAKEDEV -d /u03/dev -x zero # MAKEDEV -d /u03/dev -x null
- Unmount the volume:
# umount /u03
- Get the EC2 X.509 cert and private key from the “Security Credentials” area under “Account” in AWS Management Console.
- Download the Amazon EC2 API tools:
http://aws.amazon.com/developertools/351 - I needed java to run the API tools, so download the rpm:
jre-6u26-linux-i586.rpm – e.g. from http://www.oracle.com/technetwork/java/javase/downloads/jre-6u26-download-400751.html - Upload the EC2 X.509 cert and private key, the Amazon EC2 API tools, and the java rpm to the instance. Unzip and install the API tools and the java rpm.
- Set up all the required environment variables (replace xxxwith the appropriate bits from the relevant file names):
# export EC2_CERT=/root/cert-xxx.pem # export EC2_PRIVATE_KEY=/root/pk-xxx.pem # export EC2_HOME=(path-to-ec2-stuff) # export JAVA_HOME=(path-to-java-stuff) # export PATH=$PATH:$EC2_HOME/bin
- Set up a symbolic link so that the EC2 tools can find java:
# ln -s (path-to-java-stuff) /usr/bin/java
- Back in the AWS Management Console, create a snapshot of the volume.
- In the instance, run this command (this is the only command you can’t do in the management console, which is what all that rigmarole about installing the API tools was all about):
# ec2-register --snapshot snap-xxx --description "my ami description" --name "my ami name" --ramdisk ari-yyy --kernel aki-zzz --region ap-southeast-1
You can get the snapshot, ramdisk and kernel identifiers from the AWS Management Console. (my instance was running in Singapore, so my region is ap-southeast-1)
- Back in AWS Management Console, I see my new AMI has been created. All I have to do now is select it, click Launch Instance, and a copy of my site is up and running.
I startup the database and see if it’s working. Unfortunately it isn’t – a bit more investigation revealed that the Oracle listener was not responding to requests. lsnrctl status reveals that it is still using the old internal IP address from the original instance – but this is a new instance with a different internal IP address.
To fix this, I edit listener.ora to correct the IP:
# cd /usr/lib/oracle/xe/app/oracle/product/10.2.0/server/network/admin # chmod +w listener.ora # vi listener.ora
The IP address is listed as the “Private IP Address” on the instance in AWS Management Console.
# lsnrctl start
After that, it’s all working – and very soon I will have a much simpler (and hopefully somewhat less reliant on the kindness of big corporations) disaster recovery process. I just need to work out the simplest way to restore the data from backup to the new instance. I’ll probably just create a new volume from a backup snapshot, attach it to the instance, and copy all the data across.
UPDATE: With an EBS-backed volume, I can now create a new AMI from the running instance whenever I want – it takes a complete snapshot of the instance, from which I can then create new instances. So my disaster recovery procedure is much simpler than it was before :)
