Re: Ask for help on Physical Standby DB error (oracle 10.2.0.2 on redhat linux 2.6.9)

I think you have to use "Alter database rename file" instead of using "alter database create datafile".

With Regards,
Saurabh Sood
www.askdba.org/weblog

On Wed, Dec 22, 2010 at 8:17 PM, Guang Mei <gmei_at_prospectiv.com> wrote:

> Hi All,
> I am asking for some help on this urgent standby db error.
>
> Problem Description: I added 3 new datafiles on my production instance. The
> resulting redo logs were shipped to my standby instance. The first datafile
> created fine, however the standby host crashed in the middle of creating the
> second. We were able to get it back up and set the standby database in
> recovery mode. I'm now getting error messages and redo logs are shipping,
> but not applying. and I'm getting errors that the datafile has an incorrect
> name. The datafile involved in the failure is incomplete and much smaller
> than it should be.
>
> -- This is the error from Standby DB alert log:
>
> Errors in file /opt/oracle/admin/ES_PROD/bdump/es_prod_mrp0_30812.trc:
> ORA-01111: name for data file 127 is unknown - rename to correct file
> ORA-01110: data file 127: '/opt/oracle/product/10.2.0/dbs/UNNAMED00127'
> ORA-01157: cannot identify/lock data file 127 - see DBWR trace file
> ORA-01111: name for data file 127 is unknown - rename to correct file
> ORA-01110: data file 127: '/opt/oracle/product/10.2.0/dbs/UNNAMED00127'
>
> -- from stanby db:
> SQL> select name from v$datafile where name like '%UNNAMED%';
>
> NAME
>
> --------------------------------------------------------------------------------
> /opt/oracle/product/10.2.0/dbs/UNNAMED00127
> /opt/oracle/product/10.2.0/dbs/UNNAMED00128
>
> These correlate exactly to the datafiles I created on my primary instance.
>
> -- from primary db:
> SQL> select file_id,file_name from dba_data_files where file_id in
> (127,128);
>
> FILE_ID
> ----------
> FILE_NAME
>
> --------------------------------------------------------------------------------
> 127
> /d70/oradata/ES_PROD/IDX_MEDIUM1/idx_medium1_28.dbf
>
> 128
> /d70/oradata/ES_PROD/IDX_MEDIUM1/idx_medium1_29.dbf
>
>
>
> Is this the correct solution:
>
> 1.-- ON STANDBY DATABASE
> alter system set standby_file_management='manual';
>
> 2.-- Rename the datafiles
> alter database create datafile
> '/opt/oracle/product/10.2.0/dbs/UNNAMED00127'
> as '/d70/oradata/ES_PROD/IDX_MEDIUM1/idx_medium1_28.dbf'
>
> alter database create datafile
> '/opt/oracle/product/10.2.0/dbs/UNNAMED00128'
> as '/d70/oradata/ES_PROD/IDX_MEDIUM1/idx_medium1_29.dbf'
>
> 3.-- On the standby database
> alter system set standby_file_management='auto';
>
> 4:- On the standby database
> recover managed standby database disconnect;
>
> Thanks for your help!
> G
> --
> http://www.freelists.org/webpage/oracle-l
>
>
>

-- 
SAURABH SOOD
ORA-DBA

--
http://www.freelists.org/webpage/oracle-l