Category Archives: Oracle 11g – Windows

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

recovery_catalog_error

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

Advertisements

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

CREATE TABLESPACE rcatab DATAFILE 1024M

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
CREATE USER rman IDENTIFIED BY password
TEMPORARY TABLESPACE temp
DEFAULT TABLESPACE rcatab
QUOTA UNLIMITED ON rcatab;

GRANT RECOVERY_CATALOG_OWNER TO rman;
  • Connect to RMAN with the RMAN user and create the catalog
RMAN catalog rman/password@orcl01
CREATE CATALOG;
  • 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

ALTER TABLE tablename NO FLASHBACK ARCHIVE;

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.

SQL> alter system set LOG_ARCHIVE_DEST_1='LOCATION=E:\BACKUPFOLDER'

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'

Oracle 11g – Enterprise Manager does not start – Windows

At the command line; check if the listener is started and running correctly

lsnrctl status

Stop and restart the listener based on listener status

lsnrctl stop
lsnrctl start

Use the Enterprise Manager command to check the status of the Database Console

emctl status dbconsole

If you get an error “Environment variable ORACLE_UNQNAME not defined. Please set ORACLE_UNQNAME to database unique name”, please below command to set it accordingly

set ORACLE_UNQNAME=<dbname>
e.g:  set ORACLE_UNQNAME=orcl

If the dbconsole is already started and active, please restart the dbconsole with below commands

emctl stop dbconsole
emctl start dbconsole

Check the status of the console once again

emctl status dbconsole

You should now ideally be able to access the Enterprise Manager