Data guard / Stand by: What are the Steps to Move Datafiles / Logfiles to Different Location?

advertisements

_____________________________________________________________________________________________________________________

Primary database Steps

1.   Confirm your primary database instance where you wanted to perform the activity

SQL> select name from v$database;

NAME

---------

TRANPRD

2.   Change the db_file_name_convert /log_file_name_convert parameter as expected after the rename. So it will be changed during the next database bounce.

alter system setdb_file_name_convert='/u03/oradata/TRANPRD/', '/u03/oradata/TRANPRD_STANDBY/','/u04/oradata/TRANPRD/', '/u04/oradata/TRANPRD_STANDBY/' scope=spfile;

System altered.

3.   Shutdown the database and move the files to target location using OS commands.

SQL> shutdown immediate;

mv -i /u03/oradata/TRANPRD/mqtst01.dbf /u04/oradata/TRANPRD/mqtst01.dbf

4.   Mount the database and rename the file to new location, then open the database.

Sqlplus / as sysdba

Startup mount

alter database rename file '/u03/oradata/TRANPRD/mqtst01.dbf' to '/u04/oradata/TRANPRD/mqtst01.dbf';

alter database open;

Data Guard / Standby Steps:

1.   Confirm your data guard database instance where you wanted to perform the activity

SQL> select instance_name fromv$instance;

INSTANCE_NAME

----------------

TRANPRD_STANDBY

2.   Change the db_file_name_convert /log_file_name_convert parameter as expected after the rename. So it will be changed during the next database bounce.

alter system setdb_file_name_convert='/u03/oradata/TRANPRD/', '/u03/oradata/TRANPRD_STANDBY/','/u04/oradata/TRANPRD/', '/u04/oradata/TRANPRD_STANDBY/' scope=spfile;

3.   Cancel the recovery on the data guard environment and bring down the database. Then move the datafiles to target location using OS commands

alter database recover managed standby database cancel;

shutdown immediate;

mv -i/u03/oradata/TRANPRD_STANDBY/mqtst01.dbf /u04/oradata/TRANPRD_STANDBY/mqtst01.dbf

4.   Mount the data guard database and change the standby_file_management tomanual. Then rename the file to new location.

startup mount

alter system setstandby_file_management=manual;

alter database rename file '/u03/oradata/TRANPRD_STANDBY/mqtst01.dbf' to '/u04/oradata/TRANPRD_STANDBY/mqtst01.dbf';

5.   Change the standby_file_management toauto again and start the recovery

alter system setstandby_file_management=AUTO;

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT FROM SESSION;

select process, status fromv$managed_standby;

6.   Data Guard manager Configuration changes for dbfilenameconvert parameter

$ dgmgrl

DGMGRL> connect sys

Password:

Connected.

DGMGRL> EDIT DATABASE 'TRANPRD' SET PROPERTY 'DbFileNameConvert' = '/u03/oradata/TRANPRD/, /u03/oradata/TRANPRD_STANDBY/,/u04/oradata/TRANPRD/, /u04/oradata/TRANPRD_STANDBY/';

DGMGRL> EDIT DATABASE 'TRANPRD_STANDBY' SET PROPERTY 'DbFileNameConvert' = '/u03/oradata/TRANPRD/,/u03/oradata/TRANPRD_STANDBY/, /u04/oradata/TRANPRD/,/u04/oradata/TRANPRD_STANDBY/';

7.   Check the data guard configuration is perfect using following command.

DGMGRL> Show configuration

_____________________________________________________________________________________________________________________

Website Stats