Category Archives: DB

Backup failed for Server, The media is formatted to support 2 media families Error

If you have overcome the subject error when setting up a backup on Microsoft SQL Server Management Studio; below is a series of steps which will provide you the solution

  • Under Object Explorer, expand the Server Objects tree
  • Under Server Objects; expand Backup Devices
  • If there is backup object list, delete it
  • Create New by right-clicking on Backup Devices
  • In the resulting Window, provide the Device Name and file name as illustrated
  • Create the backup and select the Backup Device as the destination



ORA-12514 TNS listener does not currently know of service requested in connect descriptor

Error from Recovery Catalog Database – ORA-12514 | Could not connect to RMAN Catalog Database

Example :  c:\> rman target / catalog rcat/password@orcl

Error: TNS: listener does not currently know of service requested in connect descriptor


The typical solution when you encounter this error is to check the TNSNames.ora file in the $ORACLEHOME\NETWORK\ADMIN directory to correct any typo errors

But what if your recovery catalog database was working perfectly earlier and no changes were done to the TNSNames.ora file. If yes, below steps should help you

Connect to the Recovery Catalog Database

c:\> set ORACLE_SID=catdb
c:\> sqlplus target /

You could end up getting an error message : Connected to an Idle Instance

Startup the Instance

You can start the Instance with below command

c:\> oradim -STARTUP -SID <SID> -STARTTYPE inst -PFILE <fullpath of the INITsid.ora file>

Example: oradim -STARTUP -SID catdb -STARTTYPE inst -PFILE c:\oracle\product\10.1.0\db_1\database\INTIcatdb.ora

The Recovery Catalog database instance should now be started

Backing up the Recovery Catalog – Oracle 10g / 11g

Your backup strategy is not complete until you ensure that your Recovery Catalog Database is also backed up. Use a simple batch file to automate this backup

set ORACLE_SID=<catalog_db_name>
rman target / NOCATALOG log C:\CatalogBackup.log APPEND cmdfile=c:\CatalogBackup.cmd

The backup metadata of the recovery catalog has to go to the Control File of the recovery catalog database; therefore you have to login to RMAN using NOCATALOG. Since RMAN Scripts work only when connected to the Recovery Catalog, you have to use an alternate operating system command file to automate the backup.

As illustrated above we have used the cmdfile  argument of RMAN to specify a custom cmd file. You can specify the RMAN Backup command within this file.




Create a Batch Script for Oracle RMAN Backup with Logging

You may want to run Oracle Backups using the regular Windows Task Scheduler. A simple batch file can be created for this purpose using below command. If you have multiple databases it would be wise to specify the ORACLE_SID

set ORACLE_SID=<targetdb>
rman target / catalog rmanuser/password@catalogdatabase script=<rman_script_name>

Example:  Target Database: orcl | Recovery Catalog Username /Password: rman/pwdrman | Recovery Catalog DB: rmandb

set ORACLE_SID=orcl
rman target / catalog rman/password@rmandb script=LEVEL1_BACKUP

Enabling Logging

To enable logging for your entire backup session, you can explicitly define it in the batch file itself, as illustrated below. The APPEND keyword ensures the file in rewritten in the next backup schedule.

rman target / catalog rman/password@rmandb log c:\TEMP\Oracle_backup.log APPEND script=LEVEL1_BACKUP


ORA-01950: no privileges on tablespace

Error from recovery catalog database: ORA-01950: no privileges on tablespace ‘SYSTEM’

ORACLE error from recovery catalog database: ORA-01950: no privileges on tablespace ‘SYSTEM’ | RMAN-06433: error installing recovery catalog

Please ensure you have assigned below minimum privileges to the User for your Recovery Catalog. Grant these by logging into your SYS user

  • GRANT resource to <recovery catalog username>
  • GRANT RECOVERY_CATALOG_OWNER to <recovery catalog username>


Creating a Recovery Catalog in a database different from Target Database

Applies to Oracle 10g / Oracle 11g

  • Create a new database using Oracle Universal Installer or from command line; lets say the new database name is orcl01
  • Once completed you will now have 2 ORACLE_HOME
  • In command prompt; set the ORACLE_SID=orcl01
  • Create a new tablespace say; rcatab. Please ensure you have set the db_create_file_dest parameter appropriately


Note: Define the space for your recovery catalog as per your database needs
  • Create a User which will be the owner of the recovery catalog

  • Connect to RMAN with the RMAN user and create the catalog
RMAN catalog rman/password@orcl01
  • Register the Target Database with the Recovery Catalog
    • Before you do this; exit RMAN and set the ORACLE_SID to your target database
    • Login to RMAN connected to Recovery Catalog and Target Database
RMAN target / catalog rman/password@orcl01
Register Database;

ORA-55610: Invalid DDL Statement on history-tracked table

You will get this error if you are trying to Truncate or perform the Shrink Space operation on a table that is connected to a Flash Archive

If unless totally necessary to do above operation; you will need to detach the table from the Flashback Archive using below command


ORA-01810: format code appears twice

An uncommon error as this may keep you wondering. Well there is a common practice of defining both Month and Minutes as MM and this is what usually causes this error. Below is a typical example.

SQL> ALTER SESSION SET NLS_DATE_FORMAT='dd/mm/yyyy hh24:mm:ss';
ORA-01810: format code appears twice

Change the date format accordingly you resolve the issue

SQL> ALTER SESSION SET NLS_DATE_FORMAT='dd/mm/yyyy hh24:mi:ss';

OPW-00001:Unable to open password-file

If you overcome this error when creating the password file for your Oracle Database; kindly recheck your syntax

Sample Syntax

orapwd file=%ORACLE_HOME%\database\PWDtest.ora entries=5 ignorecase=y

Kindly do not use single quotes to contain the file location

Setup Multiple Log Archive Destinations for your Oracle Database

Applies to Oracle 10g and above

I would assume your database is already in the ARCHIVELOG mode

SQL> show parameter LOG_ARCHIVE_DEST

This would show all your Archive Log Destinations without any value unless previously set. It would be wise to set multiple Archive Log Destinations on alternate physical disks. It would prevent the database to stop functioning if the first destination disk is full.


The LOCATION keyword is used to define that the destination is local; for remote destinations use the keyword SERVICE

SQL> alter system set LOG_ARCHIVE_DEST_2='SERVICE=yournetworkservicename'