How to Rename an Unnamed datafile (UNNAMED00004) to Original Name in Oracle 9i 10g 11g Standby Database?

advertisements

_____________________________________________________________________________________________________________________

Please find the steps to rename an unnamed datafile to original name in Standby Database.

Step1. Find out the exact name for the datafile which is in unnamed format from V$DATAFILE table.

1* SELECT FILE#, NAME FROM V$DATAFILE

SQL> /

FILE# NAME

---------- ------------------------------------------------------------

1 /db01/prod/system01.dbf

2 /db01/prod/ca_data-01.dbf

3 /db01/prod/sysaux01.dbf

4 /db01/prod/users01.dbf

5 /db01/prod/proddata-01.dbf

6 /db01/prod/proddata-02.dbf

7 /oracle/app/oracle/product/10.2.0/db_1/dbs/UNNAMED003

Step2. Cancel the standby recovery

alter database recover managed standby database disconnect from session

Step3. Change the statndby_file_management parameter to MANUAL if it is running on AUTO.

alter system set standby_file_management=manual;

Step4. Rename the datafile using alter database command.

alter database create datafile 'Unnamed Filename with Path' as 'Origianal file name New location';

eg: alter database create datafile ‘/oracle/app/oracle/product/10.2.0/db_1/dbs/UNNAMED003’ as ‘/db01/prod/proddata-03.dbf’

Step5. Change the statndby_file_management parameter to AUTO.

alter system set standby_file_management=auto;

Step6. Restart the standby recovery

alter database recover managed standby database disconnect

_____________________________________________________________________________________________________________________

Archive