Tuesday 14 February 2012

Oracle: Adding Datafile in Standby Database


We are having our DR/Standby database getting sync with Production instance. Whenever we add any datafile to Production we have to manually add datafile in Standby. Yup that's the process and we have to do that, but would like to share with you all what needs to be done step by step.:

1. Add a datafile in Production
CREATE TABLESPACE TEST1 DATAFILE
  '/OraData/amexprd/DataF/test1.dbf' SIZE 1024M AUTOEXTEND ON NEXT 100M MAXSIZE 10240M,
 '/OraData/amexprd/DataF/test2.dbf' SIZE 1024M AUTOEXTEND ON NEXT 100M MAXSIZE 10240M;

2. Wait for log switch or do it.
3. Move the arch file to standby/DR location.
4. Recover standby "recover database using backup controlfile until cancel;"
5. Above step will fail and give you one unamed file#. You can query the tablespace name etc like
select TB.NAME,DF.NAME from v$tablespace TB , v$datafile DF where TB.TS#=DF.TS#  and DF.File# in (33,34);

NAME
--------------------------------------------------------------------------------
/oracle/OraHome9i/dbs/UNNAMED00033
/oracle/OraHome9i/dbs/UNNAMED00034


6. Create datafile in Standby/DR instance with unamed file like
alter database create datafile '/oracle/OraHome9i/dbs/UNNAMED00033'
as '/OraData/amexprd/DataF/test1.dbf';

alter database create datafile '/oracle/OraHome9i/dbs/UNNAMED00034'
as '/OraData/amexprd/DataF/test2.dbf';

It's Done!!

No comments:

Post a Comment

Number of Visitors