Expanding an oracle standby database

If you have an application using both a primary and a secondary database you might experienced the error  ORA-279. This can occur if you expand your primary database with new data files without updating your standby database.

When the archive log containing the expansion gets applied to your standby database you will see the following error in your alert log:

Parallel Media Recovery started
ORA-279 signalled during: ALTER DATABASE RECOVER  database until time '2018-04-23:09:12:17' using backup controlfile  ...
ALTER DATABASE RECOVER    CONTINUE DEFAULT
Media Recovery Log /archive-file
Mon Apr 23 09:44:37 2018
File #72 added to control file as 'UNNAMED00072'. Originally created as:
'newly-added-file.dbf'
Recovery interrupted!

The fix to this problem is to tell the standby database about the new file.

Standby database> sqlplus / as sysdba

• alter database create datafile 'UNNAMED00072' as 'newly-added-file.dbf';

And now re-run the recover process. It should pass with no errors. The UNNAMED# will of course vary for each install. Use your alert log for the exact info.

Oracle read only account

Create a new read only user or grant rights to existing user

• create user new-user identified by pwd;
• grant connect, create session, select any table to new-user;

Now you can select any object by prefixing with owner. If you want to browse dictionary objects you also need

• grant select any dictionary to new-user;

For permanent users I always prefer to give specific access only to the objects needed and create synonyms to point to these objects. This gives a more generic source and also gives a better documentation of what this account is used for. And of course revoke the select any table right.