This discussion is archived
13 Replies Latest reply: Sep 30, 2012 9:14 AM by 906153 RSS

Move Datafiles on standby server without moving Primary Oracle 11g r2

906153 Newbie
Currently Being Moderated
Hi All,


Oracle Version 11g2 with EBS 11.5.0.2.

The size of one of the mount points on the standby is full and I am trying to move one of the datafiles to another mount point without making any changes in the primary.

I tried to google and came accross this link :
http://oraclesea.blogspot.in/2011/12/move-datafiles-on-standby-server.html
But it did not work... I had to start the database again with the spfile to work.

Steps mentioned in the blog:
Include below parameter in standby parameter file
DB_FILE_NAME_CONVERT = '/primary_location/xyz.dbf','/standby_location/xyz.dbf'
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE cancel;
shut immediate
startup nomount pfile=initSCSL.ora
alter database mount standby database ;
alter system set standby_file_management='MANUAL' SCOPE=MEMORY ; 
! cp /primary_location/xyz.dbf'  /standby_location/xyz.dbf
alter database rename file  '/primary_location/xyz.dbf' to '/standby_location/xyz.dbf';
alter system set standby_file_management='AUTO' SCOPE=MEMORY ; 
alter database recover managed standby database parallel 4 disconnect from session;
Can you please help me with the right steps..


Regards
KK

Edited by: 903150 on Sep 26, 2012 10:41 PM
  • 1. Re: Move Datafiles on standby server without moving Primary Oracle 11g r2
    Shivananda Rao Guru
    Currently Being Moderated
    The size of one of the mount points on the standby is full and I am trying to move one of the datafiles to another mount point without making any changes in the primary.
    On the standby database,

    1. cancel the MRP
    alter database recovery managed standby database cancel;
    2. Shut down the standby database.

    3. Copy the datafile of the standby database which you want to move from the filled mount point of the standby server to the new mount on the standby server using "cp" command.

    4. Mount the standby database.

    5. Rename the copied file at the database level.
    sql>alter database rename file '<file-name-with-full-path-on-old-location>','<file-name-with-full-path-on-new-location-on-new-mount-point>';
    6. Start the MRP on standby database
    alter database recover managed standby database disconnect from session;
    If using real time apply, then:
    alter database recover managed standby database disconnect from session using current logfile;
  • 2. Re: Move Datafiles on standby server without moving Primary Oracle 11g r2
    CKPT Guru
    Currently Being Moderated
    But it did not work... I had to start the database again with the spfile to work.
    What are the errors when you followed these steps?
    There is no such difference in primary and standby to move data files from one location to other location except Start/Stop of MRP.
    If you are in 11gR2 and using Active Data Guard, then please put in mount status and you can follow the mentioned steps. also ensure no MRP is running when doing such changes.
    Once you copy file manually and rename from SQL plus, then you should able to start MRP without any issues,
    If you are using this mount point further then dont forget to add in DB_FILE_NAME_CONVERT parameter.

    Thank You
  • 3. Re: Move Datafiles on standby server without moving Primary Oracle 11g r2
    906153 Newbie
    Currently Being Moderated
    hi Shivananda ,


    Then we need not give this parameter right ?
    DB_FILE_NAME_CONVERT = '/primary_location/xyz.dbf','/standby_location/xyz.dbf'
    Only the steps that you have mentioned ?

    Regards
    Kk
  • 4. Re: Move Datafiles on standby server without moving Primary Oracle 11g r2
    CKPT Guru
    Currently Being Moderated
    DB_FILE_NAME_CONVERT = '/primary_location/xyz.dbf','/standby_location/xyz.dbf'
    No need of full file names, only up to directories as below
    DB_FILE_NAME_CONVERT = '/primary_location','/standby_location'
  • 5. Re: Move Datafiles on standby server without moving Primary Oracle 11g r2
    Karan Kukreja Journeyer
    Currently Being Moderated
    HI CKPT,

    Thanks.. :)

    We do not have active confugired. sorry i did not capture the initial error that I got and then i did not cehck it again .. waiting for the confirmed steps .


    and for the task I assume the below steps would do the needful . Please confirm :
    1. On standby :
    
    alter database recovery managed standby database cancel;
    shutdown immediate;
    copy the datafile using the cp command;
    mount the database using the usual spfile;
    alter database rename file '<file-name-with-full-path-on-old-location>','<file-name-with-full-path-on-new-location-on-new-mount-point>';
    alter database recover managed standby database disconnect from session;
    but where would DB_FILE_NAME_CONVERT go ? do i need to startup initially with a pfile after adding this parameter or after the above command use this in pfile for future reference for the database , need a confirmation on this ?

    and I would need to give the entire datafile name along with path as its just this1 datafile ?

    Thanks a lot.

    Regards
    Kk

    Please suggest

    Edited by: Kk on Sep 27, 2012 1:58 AM
  • 6. Re: Move Datafiles on standby server without moving Primary Oracle 11g r2
    Karan Kukreja Journeyer
    Currently Being Moderated
    HI CKPT,

    Thanks.. :)

    We do not have active confugired. sorry i did not capture the initial error that I got and then i did not cehck it again .. waiting for the confirmed steps .


    and for the task I assume the below steps would do the needful . Please confirm :
    1. On standby :
    
    alter database recovery managed standby database cancel;
    shutdown immediate;
    copy the datafile using the cp command;
    mount the database using the usual spfile;
    alter database rename file '<file-name-with-full-path-on-old-location>','<file-name-with-full-path-on-new-location-on-new-mount-point>';
    alter database recover managed standby database disconnect from session;
    but where would DB_FILE_NAME_CONVERT go ? do i need to startup initially with a pfile after adding this parameter or after the above command use this in pfile for future reference for the database , need a confirmation on this ?

    and I would need to give the entire datafile name along with path as its just this1 datafile ?

    Thanks a lot.

    Regards
    Kk

    Please suggest
  • 7. Re: Move Datafiles on standby server without moving Primary Oracle 11g r2
    CKPT Guru
    Currently Being Moderated
    but where would DB_FILE_NAME_CONVERT go ? do i need to startup initially with a pfile after adding this parameter or after the above command use this in pfile for future reference for the database , need a confirmation on this ?
    After renaming data files, if you are using PFILE then edit it directly and bounce the database. If you are using SPFILE then modify in SPFILE and bounce the instance.
    and I would need to give the entire datafile name along with path as its just this1 datafile ?
    The parameter example as below
    DB_FILE_NAME_CONVERT='/u01/oradata/prim/datafile','/u02/oradata/standby/datafiile';
  • 8. Re: Move Datafiles on standby server without moving Primary Oracle 11g r2
    Shivananda Rao Guru
    Currently Being Moderated
    Here is an example for you.

    Standby database:
    SQL> select status,instance_name,database_role from v$database,v$instance;
    
    STATUS       INSTANCE_NAME    DATABASE_ROLE
    ------------ ---------------- ----------------
    OPEN         srprim           PHYSICAL STANDBY
    
    SQL> select file_name from dba_data_files;
    
    FILE_NAME
    --------------------------------------------------------------------------------
    C:\APP\SHIVANANDA.RAO\ORADATA\SRPRIM\USERS01.DBF
    C:\APP\SHIVANANDA.RAO\ORADATA\SRPRIM\UNDOTBS01.DBF
    C:\APP\SHIVANANDA.RAO\ORADATA\SRPRIM\SYSAUX01.DBF
    C:\APP\SHIVANANDA.RAO\ORADATA\SRPRIM\SYSTEM01.DBF
    C:\APP\SHIVANANDA.RAO\ORADATA\SRPRIM\USERS02.DBF
    
    SQL> select process,status,sequence# from v$managed_standby;
    
    PROCESS   STATUS        SEQUENCE#
    --------- ------------ ----------
    ARCH      CONNECTED             0
    ARCH      CONNECTED             0
    ARCH      CONNECTED             0
    ARCH      CONNECTED             0
    RFS       IDLE                  0
    RFS       IDLE                154
    MRP0      WAIT_FOR_LOG        154
    
    7 rows selected.
    
    SQL> show parameter name_convert
    
    NAME                                 TYPE        VALUE
    ------------------------------------ ----------- ------------------------------
    db_file_name_convert                 string
    log_file_name_convert                string
    SQL> alter database recover managed standby database cancel;
    
    Database altered.
    
    SQL> shut immediate
    Database closed.
    Database dismounted.
    ORACLE instance shut down.
    SQL> exit
    Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
    With the Partitioning, OLAP, Data Mining and Real Application Testing options
    
    M:\>copy C:\APP\SHIVANANDA.RAO\ORADATA\SRPRIM\USERS02.DBF C:\APP\SHIVANANDA.RAO\ORADATA\DBTEST\USERS02.DBF
    1 file(s) copied.
    
    M:\>sqlplus sys/oracle@srprim as sysdba
    
    SQL*Plus: Release 11.2.0.1.0 Production on Thu Sep 27 14:57:16 2012
    
    Copyright (c) 1982, 2010, Oracle.  All rights reserved.
    
    Connected to an idle instance.
    
    SQL> startup mount
    ORACLE instance started.
    
    Total System Global Area  778387456 bytes
    Fixed Size                  1374808 bytes
    Variable Size             494929320 bytes
    Database Buffers          276824064 bytes
    Redo Buffers                5259264 bytes
    Database mounted.
    SQL> alter system set standby_file_management=manual;
    
    System altered.
    
    SQL> alter database rename file 'C:\APP\SHIVANANDA.RAO\ORADATA\SRPRIM\USERS02.DBF' to 'C:\APP\SHIVANANDA.RAO\ORADA
    A\DBTEST\USERS02.DBF';
    
    Database altered.
    
    SQL> alter database recover managed standby database disconnect from session;
    
    Database altered.
    
    SQL> select process,status,sequence# from v$managed_standby;
    
    PROCESS   STATUS        SEQUENCE#
    --------- ------------ ----------
    ARCH      CONNECTED             0
    ARCH      CONNECTED             0
    ARCH      CONNECTED             0
    ARCH      CONNECTED             0
    RFS       IDLE                  0
    RFS       IDLE                155
    MRP0      WAIT_FOR_LOG        155
    
    7 rows selected.
    
    SQL> select name from v$datafile;
    
    NAME
    --------------------------------------------------
    C:\APP\SHIVANANDA.RAO\ORADATA\SRPRIM\SYSTEM01.DBF
    C:\APP\SHIVANANDA.RAO\ORADATA\SRPRIM\SYSAUX01.DBF
    C:\APP\SHIVANANDA.RAO\ORADATA\SRPRIM\UNDOTBS01.DBF
    C:\APP\SHIVANANDA.RAO\ORADATA\SRPRIM\USERS01.DBF
    C:\APP\SHIVANANDA.RAO\ORADATA\DBTEST\USERS02.DBF
    1. you need to shut down the standby database.
    2. Copy the file you want to move to the different mount point using OS commands.
    3. Mount the standby database.
    4. Rename the datafile through database level. Make sure the standby_file_management is set to MANUAL
    5. Start the MRP on the standby database.


    Please do not use multiple ids to reply to the thread. Since you have created this thread with the ID 903150, I suggest you to reply with the same ID and not with the other ID.
  • 9. Re: Move Datafiles on standby server without moving Primary Oracle 11g r2
    Karan Kukreja Journeyer
    Currently Being Moderated
    Hi,

    Sorry for the multiple Id's .. I was logged in through metalink id in office.. then later logged out...


    Thank You for the excellent example.. appreciate your help..

    Just wanted to know if it is possible to set the "standby file management" back to AUTO after this ? by either setting the file_name_convert parameter ?

    I am back at home and would try it tomorrow morning IST..

    thanks again

    regards
    KK
  • 10. Re: Move Datafiles on standby server without moving Primary Oracle 11g r2
    Shivananda Rao Guru
    Currently Being Moderated
    set the "standby file management" back to AUTO after this
    Sorry for not mentioning it in my previous post. You need to change the STANDBY_FILE_MANAGEMENT to AUTO on the standby database once you have renamed the file.
    alter system set standby_file_management=AUTO;
    Please provide appropriate points and consider closing the thread if you feel your issue has been resolved.
  • 11. Re: Move Datafiles on standby server without moving Primary Oracle 11g r2
    Karan Kukreja Journeyer
    Currently Being Moderated
    Thanks ... I would for sure do that once I am able to complete this task... Else would discuss again...

    Thanks a lot for your support and help. :)


    Regards
    Kk
  • 12. Re: Move Datafiles on standby server without moving Primary Oracle 11g r2
    Karan Kukreja Journeyer
    Currently Being Moderated
    Thanks a lot CKPT sir for your help. I will follow the same tomorrow in office and update the thread accordingly ... I also check your website time to time ... It's a good one ...

    Regards
    Kk
  • 13. Re: Move Datafiles on standby server without moving Primary Oracle 11g r2
    906153 Newbie
    Currently Being Moderated
    Oops.. I just could not try this as we got the space created by reducing some space on Prod data files.. but I am sure this will help someone ... Thanks Mr. Rao and CKPT Sir.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points