Spelling Numbers

Wouldn't be nice to be able to display numbers in their spelling textual format? There are a lot of applications which require such a feature: billing, bookkeeping systems etc. Well, if we remember right there is a special suffix for specifying the mask to convert a date using the TO_CHAR function. That special suffix is SP. However, being designed to work with date types it's not very obvious how it can be used for plain numbers. Well, even it's not very often used we can convert a number into a date representation using the Julian format and then spell it using the SP prefix. For example:

SQL> select to_char(to_date(34, 'j'), 'jspth') spell from dual;

SPELL
-------------
thirty-fourth

SQL> select to_char(to_date(1023, 'j'), 'jspth') spell from dual;

SPELL
-------------------------
one thousand twenty-third

Lovely! Nevertheless, there are some limitations here, and some of them are quite ugly therefore they should be carefully analyzed before deciding to use this approach into a production system. The following are some of the problems you might encounter:

1. it's not suitable for multi-language applications. The SP prefix always speaks in English, despite of the current NLS settings.
2. It cannot be used to display large numbers. In fact, the maximum number which can be spelled using this approach is exactly 5373484. It's quite small for a bookkeeping system, right?
3. How about negative numbers or zero? Forget it! It will not work!

SQLPLUS and Custom Languages

Even I'm from Romania, I don't like how the translation of various messages sounds into my own language. However, sometimes it's useful to enable such a feature if, for example, the error messages which come from the Oracle server must be displayed into the client application but using the native language. So, at the very beginning you might think it's enough just to issue an “ALTER SESSION SET NLS_LANGUAGE=ROMANIAN;” but most of the time you'll find that nothing changes. This is because Oracle needs some additional translation files which, of course, must be installed. To install them you'll need the well known Oracle Universal Installer and you have to provide -addLangs as a parameter. You'll be led into a special wizard page like the one shown below:


After choosing and installing the preferred language we can go on and test if now SQLPLUS speaks our lingo.

SQL> ALTER SESSION SET NLS_LANGUAGE=romanian;

Sesiune modificata.

SQL> select * from no_table;
select * from no_table
           *
EROARE la linia 1:
ORA-00942: tabelul sau vizualizarea nu exist?

SQL> select eroare bre;
select eroare bre
             *
EROARE la linia 1:
ORA-00923: cuvantul cheie FROM nu este g?sit in locul a?teptat

So far so good! Nevertheless, as I previously said the translated messages are quite funny. By the way, I already found three translation mistakes into the above output but I will let Romanian readers to find them. Aaaa, I almost forgot to say: those strange question marks are in place there because my database character set is not appropriate for displaying romanian characters therefore please don't count this as a translation error. :)

CANCEL if you want to work!

Ohoo, this is quite nice! Usually, you wouldn't expect to make something to work unless you specify CANCEL, right? One interesting case is when you want to recreate the control file with the RESETLOGS option. The scenario is simple, you have an old backup which contains all your datafiles and archivelogs and, in addition, you was smart enough to backup the control file to trace. Now supposing you lost all current datafiles and the current redolog files. You have to restore and recover from your backup. The first thing is to restore your datafiles to the known location, to start the instance in the nomount state and to issue the CREATE CONTROLFILE command from the trace file. After the command is successfully executed your instance will be brought into the mount state using the fresh created control file. Now, the next step is to use the RECOVER DATABASE command. Bellow is a sample output:

SQL> startup nomount
ORACLE instance started.

Total System Global Area  264241152 bytes
Fixed Size                  1218868 bytes
Variable Size              75499212 bytes
Database Buffers          184549376 bytes
Redo Buffers                2973696 bytes
CREATE CONTROLFILE REUSE DATABASE "DDB" RESETLOGS  ARCHIVELOG
    MAXLOGFILES 16
    MAXLOGMEMBERS 3
    MAXDATAFILES 100
    MAXINSTANCES 8
    MAXLOGHISTORY 292
LOGFILE
  GROUP 1 (
    '/opt/oracle/oradata/DDB/onlinelog/o1_mf_1_2pdco23c_.log',
    '/opt/oracle/flash_recovery_area/DDB/onlinelog/o1_mf_1_2pdco49x_.log'
  ) SIZE 50M,
  GROUP 2 (
    '/opt/oracle/oradata/DDB/onlinelog/o1_mf_2_2pdco6j0_.log',
    '/opt/oracle/flash_recovery_area/DDB/onlinelog/o1_mf_2_2pdco8ns_.log'
  ) SIZE 50M,
  GROUP 3 (
    '/opt/oracle/oradata/DDB/onlinelog/o1_mf_3_2pdcoby5_.log',
    '/opt/oracle/flash_recovery_area/DDB/onlinelog/o1_mf_3_2pdcof0o_.log'
  ) SIZE 50M
-- STANDBY LOGFILE
DATAFILE
  '/opt/oracle/oradata/DDB/datafile/o1_mf_system_2rlg719d_.dbf',
  '/opt/oracle/oradata/DDB/datafile/o1_mf_undotbs1_2rlg71l3_.dbf',
  '/opt/oracle/oradata/DDB/datafile/o1_mf_sysaux_2rlg714v_.dbf',
  '/opt/oracle/oradata/DDB/datafile/o1_mf_users_2rlg71kk_.dbf'
CHARACTER SET WE8ISO8859P1
 ;

SQL> select open_mode from v$database;

OPEN_MODE
----------
MOUNTED

SQL> recover database using backup controlfile;
ORA-00279: change 793731 generated at 12/22/2006 12:00:32 needed for thread 1
ORA-00289: suggestion :
/opt/oracle/flash_recovery_area/DDB/archivelog/1_1_609804799.dbf
ORA-00280: change 793731 for thread 1 is in sequence #1


Specify log: {=suggested | filename | AUTO | CANCEL}

ORA-00279: change 794266 generated at 12/22/2006 12:09:00 needed for thread 1
ORA-00289: suggestion :
/opt/oracle/flash_recovery_area/DDB/archivelog/1_2_609804799.dbf
ORA-00280: change 794266 for thread 1 is in sequence #2
ORA-00278: log file
'/opt/oracle/flash_recovery_area/DDB/archivelog/1_1_609804799.dbf' no longer
needed for this recovery


Specify log: {=suggested | filename | AUTO | CANCEL}

ORA-00279: change 794268 generated at 12/22/2006 12:09:03 needed for thread 1
ORA-00289: suggestion :
/opt/oracle/flash_recovery_area/DDB/archivelog/1_3_609804799.dbf
ORA-00280: change 794268 for thread 1 is in sequence #3
ORA-00278: log file
'/opt/oracle/flash_recovery_area/DDB/archivelog/1_2_609804799.dbf' no longer
needed for this recovery


Specify log: {=suggested | filename | AUTO | CANCEL}

ORA-00279: change 794271 generated at 12/22/2006 12:09:07 needed for thread 1
ORA-00289: suggestion :
/opt/oracle/flash_recovery_area/DDB/archivelog/1_4_609804799.dbf
ORA-00280: change 794271 for thread 1 is in sequence #4
ORA-00278: log file
'/opt/oracle/flash_recovery_area/DDB/archivelog/1_3_609804799.dbf' no longer
needed for this recovery


Specify log: {=suggested | filename | AUTO | CANCEL}

ORA-00308: cannot open archived log
'/opt/oracle/flash_recovery_area/DDB/archivelog/1_4_609804799.dbf'
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3


SQL> ALTER DATABASE OPEN RESETLOGS;
ALTER DATABASE OPEN RESETLOGS
*
ERROR at line 1:
ORA-01113: file 1 needs media recovery
ORA-01110: data file 1:
'/opt/oracle/oradata/DDB/datafile/o1_mf_system_2rlg719d_.dbf'

Uppsy! File 1 needs media recovery? Why?
Because, when I have been asked for the last log archive I didn't specify CANCEL. From the Oracle server point of view, the recover process was unsuccessful. So, the correct approach is:

SQL> recover database until cancel using backup controlfile;
ORA-00279: change 794271 generated at 12/22/2006 12:09:07 needed for thread 1
ORA-00289: suggestion :
/opt/oracle/flash_recovery_area/DDB/archivelog/1_4_609804799.dbf
ORA-00280: change 794271 for thread 1 is in sequence #4


Specify log: {=suggested | filename | AUTO | CANCEL}
CANCEL
Media recovery cancelled.
SQL> ALTER DATABASE OPEN RESETLOGS;

Database altered.

So, explicit CANCEL does matter in this case!

Should I Delete All Archives In One Shoot?

I was just wondering what is the best way to delete the backed up archives using RMAN. I have to choose between DELETE INPUT and DELETE ALL INPUT options.
In my environment, all archives are written on two different locations. If I am going to use the DELETE ALL INPUT option then after the archives from one of the two locations are backed up, all the corresponding archive files are deleted from all available locations. If I am going to use DELETE INPUT option then only the backed up archives are deleted, just from one of the two locations.
Now, I must admit that using the BACKUP ARCHIVELOG ALL command with DELETE ALL INPUT sounds to me a little bit scary as after the successful completion of it I will end up with just one backup set with all backed up archives but no archives redundancy. This backup set becomes the single point of failure if a database recovery must take place and those archives are required.
So, the other approach is to use the DELETE INPUT option only. The below figure shows what's happening in this case:


At T0 moment, there are archives not backed up on both locations. The BACKUP command creates the “Bacup Set1” and deletes the corresponding archives from the LOCATION 1. So, now I have archives on LOCATION 2 and into the backup set as well (redundancy still 2). Suppose that after new archives are generated a new backup is taken. The following figure depicts what will happen in this case:


As it can be noticed, the next BACKUP command creates the “Backup Set2” which contains all archives from the LOCATION2 not deleted by the previous BACKUP command and, in addition, the new generated archive logs from the LOCATION1. These backed up archives are deleted but the redundancy level for them is still two. Much safer, right?

Don't Forget to Restore Your Read-only Tablespaces

I know, this is a basic one! But, me personally, I don't restore/recover databases everyday therefore is quite easy to forget some basics. So, supposing that you have lost all datafiles and some of them were part of some read-only tablespaces then it is important to remember that, by default, RMAN will not restore any datafiles from those read-only tablespaces. Hopefully, RMAN will display a warning, something like this: “datafile X not processed because file is read-only”, but the restore operation will go on without problems. So, in order to restore all datafiles including the ones from the read-only tablespaces then the correct command is: “RESTORE DATABASE CHECK READONLY;”.

Does any Datablock Change Generates Flashback Logs?

In the “Database Backup and Recovery Basics” book from the 10g Release 2 (10.2) documentation, in the sub-chapter “5.3.7 Performance Tuning for Flashback Database” there is a remark which says:

“Queries do not change data and thus do not contribute to logging activity for Flashback Database.”

Hmm... indeed they do not change actual business data but they can change database blocks in the so called “block cleanout” process and, in such cases, they actually generate redolog information.

My first assumption was that “Flashback database” engine detects on the very low level any change in data blocks, either as a consequence of a DML or as a consequence of an internal data block maintenance process such as the “block cleanout” operation.

So, I've decided to test if the “block cleanout” process does or does not generate flashback logs. The scenario is simple: my test database has a datablock size of 8K so I will create a dummy table so that each of its rows to fit into one oracle datablock. Then I will insert enough records into this table so that, in the end, my transaction to simply give up cleaning out all modified blocks. In the end I will force a full table scan against the sample table in order to perform the cleanout process. During these operations I will closely look to some of my session statistics.

Here we go:

1.first of all, some checking and some rights are needed:

SQL> connect / as sysdba
Connected.
SQL> show parameter db_block_size

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_block_size                        integer     8192
SQL> select flashback_on from v$database;

FLASHBACK_ON
------------------
YES

SQL> grant select on v_$mystat to alek;

Grant succeeded.

SQL> grant select on v_$statname to alek;

Grant succeeded.

2.now, lets create our test environment:

10:57:46 SQL> connect alek
Enter password:
Connected.
10:57:55 SQL> create table muci (col1 char(2000), col2 char(2000), col3 char(2000));

Table created.

10:58:12 SQL> select name, value from v$mystat natural join v$statname
where regexp_like(name, '(.*flash.*)|(redo size)');

NAME                                                                  VALUE
---------------------------------------------------------------- ----------
physical reads for flashback new                                          6
redo size                                                              9564
flashback log writes                                                      0

10:58:22 SQL> insert into muci select 'a', 'b', 'c' from all_objects;

40710 rows created.

11:01:21 SQL> commit;

Commit complete.

11:01:37 SQL> select name, value from v$mystat natural join v$statname
where regexp_like(name, '(.*flash.*)|(redo size)');

NAME                                                                  VALUE
---------------------------------------------------------------- ----------
physical reads for flashback new                                      41526
redo size                                                         263629980
flashback log writes                                                      0

Once again after a minute:

11:01:45 SQL> select name, value from v$mystat natural join v$statname
where regexp_like(name, '(.*flash.*)|(redo size)');

NAME                                                                  VALUE
---------------------------------------------------------------- ----------
physical reads for flashback new                                      41526
redo size                                                         263629980
flashback log writes                                                      0

There's no change! Go on with the clean out process:

11:05:29 SQL> select count(1) from muci;

 COUNT(1)
----------
    40710

11:06:11 SQL> select name, value from v$mystat natural join v$statname
where regexp_like(name, '(.*flash.*)|(redo size)');

NAME                                                                  VALUE
---------------------------------------------------------------- ----------
physical reads for flashback new                                      41526
redo size                                                         266561132
flashback log writes                                                      0

Trying once again after some minutes within the same session reveals no changes, therefore we can conclude that flashback logs were not generated even asynchronously after some time.

11:06:21 SQL> select name, value from v$mystat natural join v$statname
where regexp_like(name, '(.*flash.*)|(redo size)');

NAME                                                                  VALUE
---------------------------------------------------------------- ----------
physical reads for flashback new                                      41526
redo size                                                         266561132
flashback log writes                                                      0

Thats interesting! As it can be seen, the cleanup process has, indeed, generated redolog information which means that it changed some header information from the cleaned out datablocks but no flashback logs were produced.

So, as far as these tests are concerned the above statement from the Oracle official documentation seems to be correct!

Playing Around with “Flashback Database”

“Flashback Database” is a new feature available in 10g databases and it can be used to rewind the content of the database to a previous moment in time. The key elements of this feature are:

• flashback logs
• restore points

By default the database runs with the “flashback database” feature disabled. I can always find out if this feature is activated or not by querying the V$DATABASE view.

SQL> SELECT FLASHBACK_ON FROM V$DATABASE;

FLASHBACK_ON
------------------
NO

There are some important requirements which must be fulfilled in order to start using this feature:

• a “Flash Recovery Area” must be setup;
• the database must run in ARCHIVELOG mode;

To enable flashback database the following steps should be followed:

1.clean shutdown of the database:

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

2.startup the database in mount state:

SQL> startup mount
ORACLE instance started.

Total System Global Area  264241152 bytes
Fixed Size      1218868 bytes
Variable Size     79693516 bytes
Database Buffers   180355072 bytes
Redo Buffers      2973696 bytes
Database mounted.

3. enable the flashback database feature:

SQL> alter database flashback on;

Database altered.

4.open the database:

SQL> alter database open;

Database altered.

Now, that the “Flashback Database” is enabled I can monitor and estimate the size of flashback logs by using the V$FLASHBACK_DATABASE_LOG and V$FLASH_RECOVERY_AREA_USAGE views. I can perform some massive updates/inserts and to query those two views afterwards to see how the effective and estimate flashback log sizes are affected.

SQL> select * from v$flashback_database_log;

OLDEST_FLASHBACK_SCN OLDEST_FL RETENTION_TARGET FLASHBACK_SIZE ESTIMATED_FLASHBACK_SIZE
-------------------- --------- ---------------- -------------- ------------------------
            624609 19-DEC-06             1440        8192000                283582464

SQL> select * from v$flash_recovery_area_usage;

FILE_TYPE    PERCENT_SPACE_USED PERCENT_SPACE_RECLAIMABLE NUMBER_OF_FILES
------------ ------------------ ------------------------- ---------------
CONTROLFILE                 .33                         0               1
ONLINELOG                  7.32                         0               3
ARCHIVELOG                  .61                         0               1
BACKUPPIECE               77.72                     25.84               8
IMAGECOPY                     0                         0               0
FLASHBACKLOG                .38                         0               1

6 rows selected.

The above listing reveals that currently I have a flashback log file of ~8M and the estimated flashback log size is ~270M.
OK, lets see this feature in action. I am going to create a restore point, to perform some inserts and to flashback the database to the restore point.

SQL> create restore point before_update;

Restore point created.

SQL> create table alek.fback_test as select * from all_objects;

Table created.

SQL> select count(1) from alek.fback_test;

COUNT(1)
----------
   49337

Now, I want to rewind the database back to the restore point. This will imply a shutdown, a startup in mount state, the corresponding FLASHBACK command and the opening of the database with the RESETLOGS clause.

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.

Total System Global Area  264241152 bytes
Fixed Size                  1218868 bytes
Variable Size              88082124 bytes
Database Buffers          171966464 bytes
Redo Buffers                2973696 bytes
Database mounted.
SQL> flashback database to restore point before_update;

Flashback complete.

SQL> alter database open resetlogs;

Database altered.

SQL> select count(1) from alek.fback_test;
select count(1) from alek.fback_test
                        *
ERROR at line 1:
ORA-00942: table or view does not exist

Everything went just perfect! The database was restored as it was when the restore point was defined. However, if my business requirements decide the need to restore the database to the defined restore point only and not between now and the time the restore point was defined then a better approach is to use guaranteed restore points with flashback database disabled. In this case the amount of generated flashback logs is smaller and the performance of an application which often modifies data is improved.
So, if I am going to use the above approach the first thing to be done is to disable the flashback database feature. This can be done with the database open.

SQL> alter database flashback off;

Database altered.

However, switching back is not possible when the database is in the OPEN state:

SQL> alter database flashback on;
alter database flashback on
*
ERROR at line 1:
ORA-38759: Database must be mounted by only one instance and not open.

Another important thing to notice is that I cannot create the first guaranteed restore point with the database open. If trying to do so I'll get an error message as shown below:

SQL> create restore point before_delivery guarantee flashback database;
create restore point before_delivery guarantee flashback database
*
ERROR at line 1:
ORA-38784: Cannot create restore point 'BEFORE_DELIVERY'.
ORA-38787: Creating the first guaranteed restore point requires mount mode 
when flashback database is off.

The error message is quite self explanatory therefore the next step would be to cleanly shutdown the database and to start it in the mount state.

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.

Total System Global Area  264241152 bytes
Fixed Size                  1218868 bytes
Variable Size              88082124 bytes
Database Buffers          171966464 bytes
Redo Buffers                2973696 bytes
Database mounted.
SQL> create restore point before_delivery guarantee flashback database;

Restore point created.

SQL> alter database open;

Database altered.

Now, lets simulate the same scenario as we did with the flashback database feature enabled.

SQL> create table alek.fback_test as select * from all_objects;

Table created.

SQL> select count(1) from alek.fback_test;

COUNT(1)
----------
   49337

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.

Total System Global Area  264241152 bytes
Fixed Size                  1218868 bytes
Variable Size              88082124 bytes
Database Buffers          171966464 bytes
Redo Buffers                2973696 bytes
Database mounted.
SQL> flashback database to restore point before_delivery;

Flashback complete.

SQL> alter database open resetlogs;

Database altered.

SQL> select count(1) from alek.fback_test;
select count(1) from alek.fback_test
                        *
ERROR at line 1:
ORA-00942: table or view does not exist

Lovely, it works as expected! Now, I'm wondering what happen with my guaranteed restore point.

SQL> select name, time, guarantee_flashback_database from v$restore_point;

NAME                           TIME                                     GUA
------------------------------ ---------------------------------------- ---
BEFORE_DELIVERY                19-DEC-06 05.01.55.000000000 PM          YES
BEFORE_UPDATE                  19-DEC-06 03.54.19.000000000 PM          NO

It is still there. The guaranteed restore points are never automatically age out therefore the only option I have in order to delete it is to use the DROP RESTORE POINT command.

SQL> drop restore point before_delivery;

Restore point dropped.

SQL> select name, time, guarantee_flashback_database from v$restore_point;

NAME                           TIME                                     GUA
------------------------------ ---------------------------------------- ---
BEFORE_UPDATE                  19-DEC-06 03.54.19.000000000 PM          NO

Now, the ugly part of this is that I've end up with the previous situation when no guaranteed restore points were defined and the first one must be always created with the database started in the mount state.

SQL> create restore point before_delivery guarantee flashback database;
create restore point before_delivery guarantee flashback database
*
ERROR at line 1:
ORA-38784: Cannot create restore point 'BEFORE_DELIVERY'.
ORA-38787: Creating the first guaranteed restore point requires mount mode 
when flashback database is off.

The workaround would be to create a second guaranteed restore point and to delete the previous one so that at least one guaranteed restore point to be defined. However, the drawback of this is that I have to maintain this guaranteed restore point if my goal is to maximize the availability of the database.

After the evaluation of this feature I can say what I like and what I don't like about it:

I like:

• the straightforward way of configuring and using it;
• the possibility to estimate the size of flashback logs through the V$FLASHBACK_DATABASE_LOG view;
• much easier to use than the classical database incomplete recovery;

I don't like:

• the potential performance issues on a productive system which performs a lot of changes in the underlying data.
• the fact that i cannot use it to rewind the content of just one or several tablespaces. If the database hosts more than one application then all of them will be affected therefore the remaining option will still remain the TPITR. The “Flashback Database” feature is closer to the “Database incomplete recovery” than the TPITR feature.
• From my point of view this feature suits well into test and/or developing environments were the likelihood of making mistakes is greater in comparison with the production environment which is supposed to be used in a restrictive and controlled way. The main benefit of using it in production systems could be seen from the application deployment strategy perspective. Before running an update script as part of the application delivery process the DBA can define a guaranteed restore point and if something nasty happens within that script then he/she can easily rewind the database as it was at the time the restore point was defined. However, the fact that the database should be shutdown doesn't sound very well to me as precious cached data from the SGA will be lost.