Oracle Database Discussions

Hi,

We have a physical standby database.

The location of datafiles on primary database are at /oracle/oradata/ and the location of datafiles on standby database are at /oracle/oradata/
Now we are facing a situation of mount mount getting full on standby database so we need to move some tablespaces to another location on standby.


Say old location is /oracle/oradata/ and new location is /oradata_new/ and the tablespaces to be relocated are say tab1 and tab2.

Can anybody tell me whether following steps are correct.

1. Stop managed recovery on standby database

alter database recover managed standby database cancel;

2. Shutdown standby database

shutdown immediate;

3. Open standby database in mount stage

startup mount;

4. Copy the datafiles to new location say /oradata_new/ using os level command

4. Rename the datafile

alter database rename file
'/oracle/oradata/tab1.123451.dbf', '/oracle/oradata/tab1.123452.dbf','/oracle/oradata/tab2.123451.dbf',''/oracle/oradata/tab2.123452.dbf'
to '/oradata_new/tab1.123451.dbf', '/oradata_new/tab1.123452.dbf','/oradata_new/tab2.123451.dbf',''/oradata_new/tab2.123452.dbf';


5. Edit the parameter db_file_name_convert

alter system set db_file_name_convert='/oracle/oradata/tab1','/oradata_new/tab1','/oracle/oradata/tab2','/oradata_new/tab2'

6. Start a managed recovery on standby database

alter database recover managed standby database disconnect from session;


I am littelbit confused in step 5 as we want to relocate only two tablespaces and not all tablespaces so we have used.

Can we use db_file_name_convert like this i.e. does this work for only two tablespaces tab1 and tab2.


Thanks & Regards

GirishA