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

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
DBA Tips Data Pump Reference

0 comments:

Post a Comment

 

dba topics. Copyright 2011-16 All Rights Reserved | Site Map | Contact | Disclaimer | Google