RMAN TIP: Avoiding ORA-27211

I'm using RMAN to backup only to disk. Usualy when I have to delete obsolete backup I run command:

delete obsolete device type disk;

It is anoying to type device type disk every time, but

delete obsolete;

causes:

RMAN-03002: failure of delete command at 09/12/2005 08:44:27
ORA-19554: error allocating device, device type: SBT_TAPE, device name:
ORA-27211: Failed to load Media Management Library

The reason is that some time ago I was testing backups to tape. There is simple solution:

Configure CHANNEL device type 'SBT_TAPE' clear;

It will remove information about tape device configuration, so all commands will use default device: disk.

Cheers, Paweł

Moving/Adding Control file when using SPFile

Adding controlfile or moving to new location is tricky when your database was started with server parameter file (spfile). It is because You cannot edit spfile when database is not running.
Method 1 – preferred by me:

1. alter system parameter to point to new (planed) location of controlfiles

   alter system set control_files
   = '/dir1/CONTROL01.CTL'
   , '/dir2/CONTROL02.CTL'
   , '/dir3/CONTROL03.CTL'
   scope=spfile;

2. shutdown database,
3. copy one of controlfile to new locations,
4. startup database.

Method 2:

1. shutdown database,
2. copy one of controlfile to new locations,
3. copy spfile to pfile:

   create pfile from spfile;

4. manually edit pfile,
5. copy pfile back to spfile:

   create spfile from pfile;

6. startup database.

Cheers, Paweł

Autobackup of SPFILE and CONTROLFILE

Even if you do not use RMAN to take backups of your database, you might find it useful to take snapshots of your SPFILE and CONTROLFILE automatically. You can configure Recovery Manager (RMAN) to automatically create copies of CONTROLFILE and SPFILE when you make changes to database such as:

• adding/dropping a new tablespace or datafile
• altering the state of a tablespace or datafile (online/offline/read only)
• adding a new online redo log,
• renaming a file
• adding a new redo thread
• and many more...

To setup autobackup just start RMAN and connect to database

RMAN target=/

or

RMAN target=sys/password@db

set directory and format of backup files:

RMAN> CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO 'C:\Backup\%F';

In name of autbackup file you have to use %F. It will be expanded to C-XXXXXXXXX-YYYYMMDD-NN, where:

• XXXXXXXXX – database id
• YYYYMMDD – day, when backuped
• NN – change number during day, starts with 00, and represented in hexadecimal

Activate autobackups:

RMAN> CONFIGURE CONTROLFILE AUTOBACKUP ON;

Now autobackups are active, and every time you change database structure controlfile will be automatically backed up.
If you use RMAN for backups it will create copy of SPFILE or CONTROLFILE also after each backup operation.
I've tested it on Oracle 10g (10.2) but it should work in Oracle since version 8i. Next time i'll write how to restore SPFILE or CONTROLFILE from these backup.

Cheers, Paweł

Update: How to restore CONTROLFILE and SPFILE from RMAN autobackups

RMAN connection to Catalog - updated

I must say that I was completely wrong in my post RMAN connection to Catalog. In fact when connection to Catalog fails, RMAN quits and rest of script is ignored. So I've changed this. RMAN connect only to Target Database during backup process, and after that RMAN is run second time. This time it connects to Target Database and Catalog Database, then run command RESYNC CATALOG to force synchronization of control file with Catalog. It should work now.

Cheers, Paweł

Blogger Beta Backup tool

Finally i found time to prepare solution to backup my blog. I've decided to do that as I had problems to backup my blog using available tools. To archive that I use Blogger Beta Atom feeds, few tables in Oracle, and PL/SQL package to read blog posts, parse them using XML DB features and store in DB tables.

Requirements

• Oracle 10g (tested on Oracle XE on Windows and Oracle Enterprise 10.2.0.2 on Windows). Oracle9i should work also, but it's not tested.
• Blogs maintained by Beta Blogger and hosted on blogspot.com. You must allow to publish full content of your posts and comments.
• Direct connection to Internet

Installation

1. Download installation package
2. Create new Oracle schema and grant to it:
   1. roles CONNECT and RESOURCE
   2. EXECUTE on SYS.UTL_HTTP
3. run script install.sql

Usage

First You have to register blog you want to backup. Use BLOGSPOT_BACKUP.register_blog function:

begin
 dbms_output.put_line(
 BLOGSPOT_BACKUP.register_blog
 ('Software Engineer Thoughts', 'http://pbarut.blogspot.com/'));
 commit;
end;

To download all your posts with comments andl labels use this PL/SQL block:

begin 
 BLOGSPOT_BACKUP.synchronize_blog(null, false); 
 BLOGSPOT_BACKUP.synchronize_labels(null, null); 
 BLOGSPOT_BACKUP.synchronize_comments(null, null, false); 
 commit; 
end;

For more information take a look into BLOGSPOT_BACKUP specification.

Data storage

Table name	Desription
BLOGS	Blogs registered for backup.
BLOG_POSTS	Blog posts downloaded.
BLOG_LABELS	Labels associated to blog posts.
BLOG_COMMENTS	Comments related to blog posts.
FEED_CACHE	Used speed up tests. In tests phase feeds are read from this table instead of downloading every time.

End notes

I'm publishing this tool, as some of you might find it useful. It should work on all beta blogger blogs, but I do not give ant warranty. In case of any problems fill free to contact me.

Regards, Paweł

RMAN connection to Catalog

Today my nightly backups were not taken. I had an OS script that started backup using this command:

rman target=sysop/password@targetdb catalog=rman/password@catdb 
      log=rman.log append @rman.txt

The reason was that network was down and there was no connection to catdb. It leaded to error and RMAN did not start at all. So I’ve changed my command line to:

rman target=sysop/password@targetdb 
      log=rman.log append @rman.txt

And added a line in rman.txt

connect catalog rman/password@catdb;

Next time there will be no connection to catdb backup will be done anyway. It will be not registered in catalog only. Catalog will be synchronized on next backup procedure or next time you connect to target and catalog an issue any command that operate on catalog.

Cheers, Paweł