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

Oracle XMLType: exctractvalue vs. extract

I always thought that

exctarctvalue(xml, '/Node')

is equivalent to

xml.extract('/Node/text()').getstringval()

Usually it is true, but in some cases it is not. When node contains special characters like &, >, < etc... then results are different. Let me give an example:

SQL> set define off
SQL> select aa.a.extract('Node/text()').getStringVal() extr
  2       , extractvalue(aa.a, 'Node') extractval
  3    from (select XMLRoot(
  4              XMLElement("Node", 'test & < and >'),
  5             VERSION '1.0') a from dual) aa;

EXTR
----------------------------------------
EXTRACTVAL
----------------------------------------
test &amp; &lt; and &gt;
test & < and >

First look on documentation even confirms my expectations:
"extractValue – This takes an XPath expression and returns the corresponding leaf node. The XPath expression passed to extractValue should identify a single attribute or an element that has precisely one text node child. The result is returned in the appropriate SQL data type. Function extractValue is essentially a shortcut for extract plus either getStringVal() or getNumberVal()."
XML DB Developer's Guide - b14259.pdf page 1-17.

But there is small note on that:
"Note: Function extractValue and XMLType method getStringVal() differ in their treatment of entity encoding. Function extractValue unescapes any encoded entities; method getStringVal() returns the data with entity encoding intact."
XML DB Developer's Guide - b14259.pdf page 4-10.

Cheers, Paweł

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ł

ORA-03297 on empty datafile

Today I've noticed strange behavior of Oracle 10gR2 (10.2.0.3) while shrinking datafile. I had empty datafile (no objects) and i couldn't resize it. I was getting ORA-03297: file contains used data beyond requested RESIZE value while DBA_EXTENTS show no rows for that file. I've discovered that there are objects in Recycle Bin that were located on that file. After purging recycle bin file was shirked successfully.
Let me make an example:

SQL> create tablespace tb_test
  2  datafile 'c:\temp\tb_test.dbf' size 100 K autoextend on;
Tablespace created.
SQL> select count(*) from dba_extents where tablespace_name='TB_TEST';
  COUNT(*)                                                                      
----------                                                                      
         0                                                                      
SQL> select bytes from v$datafile where name = 'C:\TEMP\TB_TEST.DBF';
     BYTES                                                                      
----------                                                                      
    106496                                                                      
SQL> create table t_big tablespace tb_test as
  2  select a.* from all_objects a, dba_users for_cartesian;
Table created.
SQL> select count(*) from dba_extents where tablespace_name='TB_TEST';
  COUNT(*)                                                                      
----------                                                                      
        34                                                                      
SQL> select bytes from v$datafile where name = 'C:\TEMP\TB_TEST.DBF';
     BYTES                                                                      
----------                                                                      
  19963904

Now we have datafile quite big. Let's drop table and shrink file:

SQL> drop table t_big;
Table dropped.
SQL> select count(*) from dba_extents where tablespace_name='TB_TEST';
  COUNT(*)                                                                      
----------                                                                      
         0                                                                      
SQL> alter database datafile 'c:\temp\tb_test.dbf' resize 100 K;
alter database datafile 'c:\temp\tb_test.dbf' resize 100 K
*
ERROR at line 1:
ORA-03297: file contains used data beyond requested RESIZE value 

So. even there is nothing reported in DBA_EXTENT, file cannot be shinked. Let's purge recyclebin and try again:

SQL> purge recyclebin;
Recyclebin purged.
SQL> alter database datafile 'c:\temp\tb_test.dbf' resize 100 K;
Database altered.

Now works fine! Don't forget to clear after tests :)

SQL> drop tablespace tb_test including contents and datafiles;
Tablespace dropped.

Cheers, Paweł