Startup fails with ORA-01261: Parameter db_recovery_file_dest destination string cannot be translated

Problem Description
While I start my database with my initialization parameter it fails with oracle error ORA-01261 and ORA-01262 as below:
RMAN> startup force pfile='/oradata2/arjudbapfile.ora';

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of startup command at 08/28/2008 15:04:27
RMAN-04014: startup failed: ORA-01261: Parameter db_recovery_file_dest destination string cannot be translated
ORA-01262: Stat failed on a file destination directory
Linux Error: 2: No such file or directory

Cause of The Problem
The linux error No such file or directory indicates all what you need to do. It may be the case that,

-You might not start your database with proper or recently updated pfile/spfile. May be you changed your location of your db_recovery_file_dest and that initialization parameter that you used to startup the database has not reflected yet.

-The directory location specified by the db_recovery_file_dest parameter is not on your system.
Solution of The Problem
If you start your database with pfile edit it and change the location of db_recovery_file_dest parameter to a valid one. Be sure oracle has proper permission on the directory from OS.

However if you start your database with an spfile then,
-Create pfile='1.ora' from spfile;
-Edit pfile 1.ora and change the location of db_recovery_file_dest parameter.
-Create spfile from pfile='1.ora';
-Start your database.

RMAN-04014: startup failed: ORA-07446: sdnfy: bad value

Problem Description
While performing disaster recovery to a new host, you need to start the database in nomount sate(in fact to restore control file) but database could not start in nomount state because it fails with RMAN-04014, ORA-07446.

RMAN> startup force pfile='/oradata2/arjudbapfile.ora';

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of startup command at 08/28/2008 15:03:44
RMAN-04014: startup failed: ORA-07446: sdnfy: bad value '/oracle/app/oracle/product/10.2.0/db_1/admin/arjudba/udump' for parameter user_dump_dest.

Cause of The Problem
The path /oracle/app/oracle/product/10.2.0/db_1/admin/arjudba/udump as shown in output does not exist. Oracle itself does not create any path if a path does not exist. So, you have to change the value of user_dump_dest in the initialize parameter.

Solution of The Problem
If you use pfile to start your database then edit the pfile with any editor (for example vi on unix) and either change the location of user_dump_dest or remove the parameter user_dump_dest from pfile. And then perform startup.


If you like to use spfile then first create pfile from spfile, then edit the pfile and then create spfile from the pfile, like.

1.Create pfile from spfile.
SQL> create pfile='1.pfile' from spfile='/oracle/app/oracle/product/10.2.0/db_1/dbs/spfilearju.ora';

2.Edit the pfile.
SQL>!vi 1.pfile

Remove the entry of user_dump_dest or add valid entry.

3.Create spfile from pfile.
SQL>create spfile from pfile='1.pfile';

4.start the database.
SQL>STARTUP NOMOUNT

ORA-09925: Unable to create audit trail file

Problem Description
Whenever you try to startup the database with pfile then it fails with error RA-09925: Unable to create audit trail file along with Linux Error: 2: No such file or directory.

RMAN> startup force pfile='/oradata2/arjudbapfile.ora';

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of startup command at 08/28/2008 15:02:29
RMAN-04014: startup failed: ORA-09925: Unable to create audit trail file
Linux Error: 2: No such file or directory
Additional information: 9925


Cause of the Problem
The error ORA-09925 indicates that oracle was unable to write the audit information into the audit directory. The audit directory is specified by audit_trail parameter in the initialization parameter. Just after the ORA-09925 if there is some OS error then that is notified. The linux error clearly identified that which is No such file or directory.

Solution of the Problem
There may be several solutions to this problem.
1.Create the approprite directory/location specified by audit_trail initialization parameter from operating system and grant appropriate permission to that directory.

2.Change the location of audit_trail initialization parameter to a valid location in the OS.

If you start your database with pfile then you can easily do this by editing your pfile with an editor and modify the audit_trail parameter.

However if you start your database with an spfile then,
-Create pfile='1.ora' from spfile;
-Edit pfile 1.ora and the location of audit_trail parameter.
-Create spfile from pfile='1.ora'
-Start your database with the spfile.

Message file RMAN.msb not found

Error Description
I give the full path of rman executable file location and I am getting error RMAN<>.msb not found as below.
-bash-3.1$ /oradata2/bin/rman target /
Message file RMAN<>.msb not found

Verify that ORACLE_HOME is set properly

Solution of The problem
Believe me, as I still got you have not set ORACLE_HOME properly. So set it.

To know your current settings of ORACLE_HOME, issue,
-bash-3.1$ echo $ORACLE_HOME
/oradata2/bin/

Here we see it is set, it may either unset. Though it is set wrong. The ORACLE_HOME path is before the bin directory. So here ORACLE_HOME will be /oradata2 instead of /oradata2/bin/

On unix set the value for the current session by,
-bash-3.1$ export ORACLE_HOME=/oradata2/

On Windows environment you have to set by
set ORACLE_HOME=C:\oracle or like that.

In order to set it permanently edit your profile. On linux like, ~/.bash_profile or on unix edit the file .profile on home directory and make an entry of ORACLE_HOME.

After setting correct ORACLE_HOME now try to connect to rman.
-bash-3.1$ /oradata2/bin/rman target /

Recovery Manager: Release 10.2.0.1.0 - Production on Thu Aug 28 14:37:53 2008

Copyright (c) 1982, 2005, Oracle. All rights reserved.

connected to target database (not started)

How to restore an Spfile from autobackup older than 7 days

Whenever we issue in RMAN, RMAN> restore spfile from autobackup; then restoring from autobackup stops at seven days and then it arises message channel ORA_DISK_1: no autobackup in 7 days found. For example,

RMAN> restore spfile from autobackup;
Starting restore at 31-AUG-08
using channel ORA_DISK_1

channel ORA_DISK_1: looking for autobackup on day: 20080831
channel ORA_DISK_1: looking for autobackup on day: 20080830
channel ORA_DISK_1: looking for autobackup on day: 20080829
channel ORA_DISK_1: looking for autobackup on day: 20080828
channel ORA_DISK_1: looking for autobackup on day: 20080827
channel ORA_DISK_1: looking for autobackup on day: 20080826
channel ORA_DISK_1: looking for autobackup on day: 20080825
channel ORA_DISK_1: no autobackup in 7 days found
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of restore command at 08/31/2008 01:31:12
RMAN-06172: no autobackup found or specified handle is not a valid copy or piece

Whenever you give RMAN to RESTORE SPFILE FROM AUTOBACKUP or RESTORE CONTROLFILE FROM AUTOBACKUP, RMAN begins the search on the current day or on the day specified with the SET UNTIL caluse. If no autobackup is found in the current or SET UNTIL day, RMAN checks the previous day. In this way RMAN by default checks for 7 days autobackup from the current or SET UNTIL day.

However if you want to extend your searching of autobackup more than 7 then you have to use MAXDAYS option with the RESTORE command.

For example,
RMAN>restore spfile from autobackup maxdays 30;
or
RMAN>restore controlfile from autobackup maxdays 30;

In these cases autobackup searches will be performed up to 30 days from the current or SET UNTIL day.

Related Documents

How to Discover find DBID

How to skip a tablespace for restore operation

How to Restore the Controlfile from Backup.

Restore and Recover database in Noarchivelog Mode

Restore and Recovery of Individual Tablespaces or Datafiles

How to Restore spfile from backup

Mechanism followed by RMAN Restore & Recovery.

Restore Point and Flashback Database.

RMAN-06172: no autobackup found

Error Description:
While performing disaster recovery I get the error RMAN-06172 as below.

RMAN> restore spfile to pfile '/oracle/app/oracle/product/10.2.0/db_1/dbs/initARJU.ora' from '/backup1/snap/june/ctl_sp_bak_c-448149146-20080607-00';

Starting restore at 27-AUG-08
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=36 devtype=DISK

RMAN-00571: ====================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ====================================================
RMAN-03002: failure of restore command at 08/27/2008 00:42:41
RMAN-06172: no autobackup found or specified handle is not a valid copy or piece

Cause of The Error
To perform restore operation to a new host you must have at least one autobackup of the controlfile. If you don't have autobackup of the controlfile then you would not be able to perform disaster recovery. For example if you have snapshot of controlfile backup then you will not be able to restore spfile or controlfile.

Solution of The Problem
1)To perform a disaster recovery, the minimum required set of backups is backups of some datafiles, some archived redo logs generated after the time of the backup, and at least one autobackup of the control file. So find autobackup of the controlfile and use it for restore operation.

Also ensure that you have autobackup intact and your autobackup is not corrupted. If you have corrupted backup then you will get above error.

My autobackup piece is /export/home/oracle/8rji9vrq_1_1 and so I used,
RMAN> restore spfile from '/export/home/oracle/8rji9vrq_1_1';

2)In situation you have autobackup of controlfile and it is not corrupted then still you can get the error. It is because of platform difference in endian format. It was because of BIG endian or LITTLE endian format. If you take backup in BIG endian platform and want to restore it to LITTLE endian format then above error will appear. You can check the endian format by below query.

SQL> set linesize 100
SQL> col PLATFORM_NAME format a80
SQL> select PLATFORM_NAME, ENDIAN_FORMAT from V$TRANSPORTABLE_PLATFORM;

PLATFORM_NAME                                                                    ENDIAN_FORMAT
-------------------------------------------------------------------------------- --------------
Solaris[tm] OE (32-bit)                                                          Big
Solaris[tm] OE (64-bit)                                                          Big
Microsoft Windows IA (32-bit)                                                    Little
Linux IA (32-bit)                                                                Little
AIX-Based Systems (64-bit)                                                       Big
HP-UX (64-bit)                                                                   Big
HP Tru64 UNIX                                                                    Little
HP-UX IA (64-bit)                                                                Big
Linux IA (64-bit)                                                                Little
HP Open VMS                                                                      Little
Microsoft Windows IA (64-bit)                                                    Little
IBM zSeries Based Linux                                                          Big
Linux 64-bit for AMD                                                             Little
Apple Mac OS                                                                     Big
Microsoft Windows 64-bit for AMD                                                 Little
Solaris Operating System (x86)                                                   Little
IBM Power Based Linux                                                            Big

17 rows selected.

We see Solaris[tm] OE (32-bit) or Solaris[tm] OE (32-bit) has big endian format but Solaris Operating System (x86) has little endian format. So we can't perform any restore operation in Microsoft Windows 64-bit for AMD or Solaris Operating System (x86) or Microsoft Windows IA (32-bit) (All these has Little endian format) from backup that was taken from Big endian format like Solaris[tm] OE (32-bit) or Solaris[tm] OE (32-bit) or IBM Power Based Linux or IBM zSeries Based Linux or HP-UX (64-bit) or AIX-Based Systems (64-bit).

If you try to restore in Little edian format, from backup taken in Big endian format platform RMAN can't read the backuppiece and will return,
RMAN-00571: ====================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ====================================================
RMAN-03002: failure of restore command at 08/27/2008 00:42:41
RMAN-06172: no autobackup found or specified handle is not a valid copy or piece

The solution is to use same endian format.

Related Documents
RMAN-06026, RMAN-06023 During Restore Using RMAN
ORA-19573: cannot obtain exclusive enqueue for datafile 1

Restore fails with ORA-19870,ORA-19587,ORA-27091,ORA-27067

Problem Symptoms
From the backuppiece restore spfile commnad fails with ORA-19870,ORA-19587,ORA-27091,ORA-27067
RMAN> restore spfile from '/backup1/snap/june/8rji9vrq_1_1';

Starting restore at 27-AUG-08
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=36 devtype=DISK

channel ORA_DISK_1: autobackup found: /backup1/snap/june/8rji9vrq_1_1
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of restore command at 08/27/2008 01:05:07
ORA-19870: error reading backup piece /backup1/snap/june/8rji9vrq_1_1
ORA-19587: error occurred reading 0 bytes at block number 1
ORA-27091: unable to queue I/O
ORA-27067: size of I/O buffer is invalid
Additional information: 2

Cause of the Problem
RMAN could not read the backuppiece. There may be several reasons behind it. Either it is not the file what is expected in the RMAN command. Suppose my command was restore spfile and this backuppiece may be backups of archived logs. Another reason may be the file in OS level is made read only status. As RMAN opens the backuppiece in read write mode so error may come.

Solution of the Problem
1)Be sure the command you are using is valid one in terms of backuppiece. Suppose here I used restore spfile then this piece must be backup of controlfile.

2)Make sure that backuppiece is read write mode on OS. You can check it on unix by,
$ls -l backupiece_name
In order to make read write mode.
$chmod 666 backupiece_name


Related Documents
RMAN-06026, RMAN-06023 During Restore Using RMAN
How to skip a tablespace for restore operation

ORA-31605 returned from LpxXSLResetAllVars in routine kuxslResetParams

Problem Description
SQL> select dbms_metadata.get_ddl('TABLE','A') from dual;
ERROR:
ORA-06502: PL/SQL: numeric or value error
ORA-31605: the following was returned from LpxXSLResetAllVars in routine
kuxslResetParams:
LPX-1: NULL pointer
ORA-06512: at "SYS.UTL_XML", line 246
ORA-06512: at "SYS.DBMS_METADATA_INT", line 7511
ORA-06512: at "SYS.DBMS_METADATA_INT", line 9453
ORA-06512: at "SYS.DBMS_METADATA", line 1919
ORA-06512: at "SYS.DBMS_METADATA", line 2792
ORA-06512: at "SYS.DBMS_METADATA", line 4333
ORA-06512: at line 1


no rows selected

Solution of The Problem
Solution is the same as in http://arjudba.blogspot.com/2008/08/ora-39212-installation-error-xsl.html.
After executing dbms_metadata_util.load_stylesheets reconnect to your session.

ORA-39212: installation error: XSL stylesheets not loaded correctly

Problem Description
SQL> select dbms_metadata.get_ddl('TABLE','TEST_EXT') from dual;
ERROR:
ORA-39212: installation error: XSL stylesheets not loaded correctly
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 79
ORA-06512: at "SYS.DBMS_METADATA_INT", line 7398
ORA-06512: at "SYS.DBMS_METADATA_INT", line 7447
ORA-06512: at "SYS.DBMS_METADATA_INT", line 9453
ORA-06512: at "SYS.DBMS_METADATA", line 1919
ORA-06512: at "SYS.DBMS_METADATA", line 2792
ORA-06512: at "SYS.DBMS_METADATA", line 4333
ORA-06512: at line 1

no rows selected

Cause of The Problem
The XSL stylesheets used by the Data Pump Metadata API were not loaded correctly into the Oracle dictionary table "sys.metastylesheet."

It may be the cause that the stylesheets were not loaded at all in the database, or they were not converted to the database character set.

Suppose if you convert your database character set using ALTER DATABASE statement then likely you will face this problem.

Solution of The Problem
Note that never use ALTER DATABASE statement to migrate your characterset.

1)Connect as sysdba.
SQL> conn / as sysdba
Connected.

2)Execute dbms_metadata_util.load_stylesheets to reload the stylesheets.
SQL> exec dbms_metadata_util.load_stylesheets;
PL/SQL procedure successfully completed.

3)Exit and run the above query,
SQL>exit
$>conn ARJU/a
SQL>select dbms_metadata.get_ddl('TABLE','TEST_EXT') from dual;

Steps and examples to use logminer for Remote mining

Many of us are curious to know about remote mining. Source database mining in production database you might avoid. So remote mining comes. Remote mining means the database that analyze of redo log files or archived redo log files is not same as of source database- that generate redo/archived log files.

The system of using logminer in remote database is transfer the analysis redo or archived redo log files as well as tranfer the mining dictionary to the remote database.

In the following section I demonstrate an step by step idea to do the task.

Scenario: Online redo log file redo01 need to be analyzed in remote machine jupiter.

Step1 -Extract logminer dictionary: (On Source Database)
In source that is in production server build the dictionary by,
SQL>EXECUTE DBMS_LOGMNR_D.BUILD(OPTIONS=> DBMS_LOGMNR_D.STORE_IN_REDO_LOGS);
PL/SQL procedure successfully completed.

After executing this procedure in archive log mode database adds the dictionary information to the online redo logs, which, in turn, go into the archived logs.

This redo log file must have been created before the redo log file that you want to analyze, but should be as recent as possible.

You can find out which redo log files contain the start and end of an extracted dictionary. To do so, query the V$ARCHIVED_LOG view, by,
SQL> SELECT NAME FROM V$ARCHIVED_LOG WHERE DICTIONARY_BEGIN='YES';
NAME
--------------------------------------------------------------------------------
/oradata1/ARJU/archivelog/2008_08_24/o1_mf_1_1284_4c23hcd6_.arc

SQL> SELECT NAME FROM V$ARCHIVED_LOG WHERE DICTIONARY_END='YES';
NAME
--------------------------------------------------------------------------------
/oradata1/ARJU/archivelog/2008_08_24/o1_mf_1_1284_4c23hcd6_.arc

Step 2- Transfer the logminer dictionary and log for analysis into the mining database.

SQL> !scp /oradata1/ARJU/archivelog/2008_08_24/o1_mf_1_1284_4c23hcd6_.arc oracle@jupiter:
Password:
o1_mf_1_1284_4c23hcd 100% |*************************************************| 9465 KB 00:01

Then also transfer the redo log. Based on your requirement you can transfer archived log or online redo log. To see a defined time archived log query by select NAME from v$archived_log where completion_time >SYSDATE-1;
In this example I will analysis online redo log file.

SQL> !scp /oradata1/arju/ARJU/redo01.log oracle@jupiter:
Password:
redo03.log 100% |*************************************************| 51200 KB 00:08


Step 3- Specify the files for analysis.(In mining/remote database)

Here specify the logfile that need to mine plus specify the dictionary files.
SQL>EXECUTE DBMS_LOGMNR.ADD_LOGFILE( LOGFILENAME => '/export/home/oracle/redo01.log', OPTIONS => DBMS_LOGMNR.ADDFILE);

SQL>EXECUTE DBMS_LOGMNR.ADD_LOGFILE( LOGFILENAME => '/export/home/oracle/o1_mf_1_1284_4c23hcd6_.arc', OPTIONS => DBMS_LOGMNR.ADDFILE);

Step 4- Start the logminer session(In mining database)

Don't specify here DICTFILENAME option. Execute the following,
SQL> EXECUTE DBMS_LOGMNR.START_LOGMNR(OPTIONS => DBMS_LOGMNR.DICT_FROM_REDO_LOGS);

Step 5- Query V$LOGMNR_CONTENTS:
You can follow for query as in How to use Oracle Logminer to analysis Logfile

Step 6- End the LogMiner Session:
EXECUTE DBMS_LOGMNR.END_LOGMNR;
Related Documents
How to use Oracle Logminer to analysis Logfile

ORA-01295: DB_ID mismatch between dictionary USE_ONLINE_CATALOG and logfiles

Problem Description
While doing remote mining in the mining database through logminer START_LOGMNR procedure fails with ORA-01295. May be you want to analysis redo log files of the production database in the testing environment.
SQL> BEGIN
DBMS_LOGMNR.START_LOGMNR
(options =>
dbms_logmnr.dict_from_online_catalog);
END;
/ 2 3 4 5 6
BEGIN
*
ERROR at line 1:
ORA-01295: DB_ID mismatch between dictionary USE_ONLINE_CATALOG and logfiles
ORA-06512: at "SYS.DBMS_LOGMNR", line 58
ORA-06512: at line 2

Cause of The Problem
The error occurs because the dictionary file produced by a database (testing) is different from the one that produced the logfiles (production). In order to use logminer you need to extract dictionary information from the source database(production) to the mining database(testing).

Solution of The Problem

Step 1.
Extract the dictionary to the redo logs in the production database by executing following command,
SQL> EXECUTE DBMS_LOGMNR_D.BUILD(OPTIONS=> DBMS_LOGMNR_D.STORE_IN_REDO_LOGS);

Step 2
After executing commnad in step 1 the dictionary is extract into the redo logs and in fact they become archived instantly. Then check in which redo logs the dictionary is located by running this on production,
SQL> SELECT NAME FROM V$ARCHIVED_LOG WHERE DICTIONARY_BEGIN='YES';
SQL> SELECT NAME FROM V$ARCHIVED_LOG WHERE DICTIONARY_END='YES';

Step 3
Tranfer the output of step 3 into the testing database. You can use scp or telnet to do this.

Step 4
Now in the mining database(testing) provide all the redo log files (including the one from which the transactions need to be mined) and start Log Miner (do not specify dictFileName):

SQL> EXECUTE DBMS_LOGMNR.START_LOGMNR(OPTIONS => DBMS_LOGMNR.DICT_FROM_REDO_LOGS);