Saturday, June 12, 2021

Renaming Datafile(s) in Standby Database

 Renaming a datafile on standby database is a quite simple process. If you are using ASM diskgroups as storage, the primary and standby databases already have different locations and names, and you can use RMAN command “switch datafile/database to copy” to move a datafile to a new location. This method is explained here as part of standby database incremental restore. If you are using file system for datafiles storage, you might sometimes need to relocate one or more datafiles to a different location; for example if file system is full. Following are the steps that you can follow to relocate a datafile in this case.

Cancel the managed recovery and bring physical standby database to mount state.

SQL> Alter database recover managed standby database cancel;
SQL> shutdown immediate
SQL> startup mount

 Change value of standby_file_management parameter to MANUAL. This parameter is set to AUTO by default, which means that standby datafiles will be added automatically as soon as a datafile is added in the primary database.

SQL> alter system set standby_file_management=MANUAL;

 Move the datafile to new location using OS command.

Execute ALTER DATABASE command to update controlfile with the new location of the datafile.

SQL> alter database rename file ‘old_file_name’ to ‘new_file_name’;

 Reset the standby_file_management parameter back to AUTO.

SQL> alter system set standby_file_management=AUTO;

Start managed recovery once again.

SQL> Alter database recover managed standby database using current logfile disconnect;

No comments:

Post a Comment

Popular Posts - All Times