(3 comments, 8 posts)
Harry Dragstra is Senior Oracle DBA. He is specialized in Oracle RAC, Oracle Virtualization, and has a a strong interest in APEX development and PL/SQL.
Posts by Harry Dragstra
Backup of a 11gR2 RAC database is similar to that of a single instance 11gR2 database. There is an important difference though… assuming that you – like me – schedule a non-RAC backup by cron on the database node, using local RMAN scripting. If the node you scheduled the backup on, is down, your backup simply cannot run!
So it’s obviously wrong to use the same processing method on a RAC cluster node that at the start of the backup might be offline, purposely or not. To make sure my RMAN backup will still be running when all but one node is offline, I use DBMS_SCHEDULER in combination with RMAN scripting, placed in a directory that’s shared by all nodes.Don’t change your RMAN backup script, but…
Not in scope now is the RMAN scripting itself… as long as you don’t use explicit connections or parallelism, and do use the FRA on ASM for all your recovery files, your code can remain the same, with one notable exception…. As of 11gR2 RAC, Oracle wants your snapshot controlfile to be on a shared location [ID 1472171.1]… I use the FRA on ASM, but an ACFS mounted directory will also do.
If you are familiar with 11gR2 Grid Infrastructure More >
Question from one of my customers: “Please help me find an update statement that’s able to re-order a primary key after a delete.. and by the way.. you are not allowed to use PL/SQL, triggers or a sequence, because we cannot change anything in the app schema”. Updating a PK seemed a bit odd, but then I was told about a composite PK over 2 columns, with the second column a number, meant to always start with 0 and ascending per value in the first column, and – most importantly! – without any holes in de numbering.
At first I couldn’t see a way to use pure SQL for this request, and thought of PL/SQL loops and using a temporary table. But after some time I came up with a solution, using row_number() and rowid. I think it’s a nice example of being pressed to think in sets instead of row based processing, with the solution being short, fast, and rather elegant. Try it yourself!
– create test table
create table t1( name varchar2(100), seqnr number(4), note varchar2(30));
alter table t1 add ( constraint t_pk primary key ( name, seqnr));
– insert test data
insert into t1 values ('harry', 0, 'test record 100');
insert into t1 values ('harry', 1, 'test record 101');
insert into t1 values More >
I wanted to compare the content of two tables with identical layout quickly with the minus set operator, but ran into a couple of errors, caused by clob and blob colums. As I found out, set operators ( union, minus, intersect ) in combination with clobs or blobs, are not supported, so this explained the problems I encountered.
But there’s a way to use this combination anyway, as long as you’re satisfied with comparing just the first 2000 bytes, and the length of your clobs/blobs.
First of all, to show this work around, I will create some tables containing at least one clob and one blob:
In (rman) incrementally updated backups, only incremental backups are done after the first full backup to the Fast Recovery Area. From 11gR2 on the incremental backup pieces will get the same tag as the datafile copies, and that’s actually different behaviour from pre- 11gR2 versions of the database.
I stumbled on this different behaviour when a backup script, that worked perfectly on 11gR2, got ported to 10gR2 and 11gR1 ( 11.1.7 ). The script contains a system cleanup of all incrementals, done before a certain date, specifying the tag of the copy [ 'COPY_DATABASE' ]. On 11gR2 the inc1 backups got deleted fine, but the delete on 10gR2 and 11gR1 just wouldn’t work.
By checking v$backup_piece…
select distinct tag
…and getting no result, it became clear that the tag of the copy datafiles, was’t also the tag of the incrementals.
Once I knew, it was easy to correct the problem.
I just added the tag of my copy datafiles to the incremental backup, and that did the trick. All incrementals that were supposed to get cleared from the pre-11gR2 system, were deleted. The incrementals without tag, already on the system, were deletedMore >
If you, like me, like to use (rman) incrementally updated backups, a copy of all datafiles will be present in the Fast Recovery Area. That becomes quite handy if you are in sudden need of extra disk space for your database, and the FRA still has ample space left. By switching one or more datafiles to the copy in FRA, you can very quickly use this extra disk space for your database, while not even having to shut down. I developed a view to generate all the rman commands to execute the switch to FRA, and the switch back to it’s original location and name. Use it at your own discretion.
CREATE OR REPLACE FORCE VIEW rman_switch_datafiles
||'Â switch datafile '||file_id||' to copy in FRAÂ '||chr(10)
||'sql ''alter tablespace '||tablespace_name||' offline'';' ||chr(10)
||'switch datafile '||file_id||' to copy;' ||chr(10)
||'recover datafile '||file_id||';' ||chr(10)
||'sql ''alter tablespace '||tablespace_name||' online'';'||chr(10)
, More >
One of our customers had this request: Can you make a backup of my development database that I can use to restore ( the same database ) to a couple of months back in time?
This customer uses Standard Edition [ SE ]Â 11gR1 database on Windows, and has a backup regime where tape backups older than 6 weeks(!) are reused for new backups. Online backups to disk are used, but are only useful for a recovery to at most 7 days back in time.
My first idea was to just take a cold backup to disk, and use this backup to restore. But this isn’t very elegant, and quite hard to automate. I decided to investigate the 11g possibilities of setting a restorepoint within an archival backup, and using this restorepoint to “flashback” the database back in time. Note that I can’t use the real flashback technology because Oracle Enterprise Edition [ EE ] is the only version that supports flashback.