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


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
DBA Tips Data Pump Reference

0 comments:

Post a Comment

 

dba topics. Copyright 2011-16 All Rights Reserved | Site Map | Contact | Disclaimer | Google