How to change RedoLog File Location with Different Name in 12c

In this Blog, we are discussing about how to change the location of Redolog file along with the Name Change.

This Blog can be used to change the name of redo log file at same location or different location.

So we will find out the location of the existing redo log file:

SQL> select member from v$logfile;

MEMBER
———————————————————————————————————————————-
/u01/app/oracle/oradata/CDB2/onlinelog/o1_mf_3_cgf7hpvy_.log
/u01/app/oracle/fast_recovery_area/CDB2/onlinelog/o1_mf_3_cgf7hpx9_.log
/u01/app/oracle/oradata/CDB2/onlinelog/o1_mf_2_cgf7hmvc_.log
/u01/app/oracle/fast_recovery_area/CDB2/onlinelog/o1_mf_2_cgf7hmx1_.log
/u01/app/oracle/oradata/CDB2/onlinelog/o1_mf_1_cgf7hk1t_.log
/u01/app/oracle/fast_recovery_area/CDB2/onlinelog/o1_mf_1_cgf7hk4f_.log

6 rows selected.

SQL>

As per our previous posts, we change the database name from CDB2 to CDB3, however the location of these files does not change with the database name change, we have to do it later with the extra steps.

Find out the log files using OS Commands:

SQL> host ls -lrt /u01/app/oracle/oradata/CDB2/onlinelog
total 153792
-rw-r—– 1 oracle oinstall 52429312 Apr 3 06:30 o1_mf_1_cgf7hk1t_.log
-rw-r—– 1 oracle oinstall 52429312 Apr 3 10:30 o1_mf_2_cgf7hmvc_.log
-rw-r—– 1 oracle oinstall 52429312 Apr 3 13:50 o1_mf_3_cgf7hpvy_.log

SQL>

SQL> host ls -lrt /u01/app/oracle/fast_recovery_area/CDB2/onlinelog
total 153792
-rw-r—– 1 oracle oinstall 52429312 Apr 3 06:30 o1_mf_1_cgf7hk4f_.log
-rw-r—– 1 oracle oinstall 52429312 Apr 3 10:30 o1_mf_2_cgf7hmx1_.log
-rw-r—– 1 oracle oinstall 52429312 Apr 3 13:50 o1_mf_3_cgf7hpx9_.log

SQL>

Create the directories, where we are planning to keep the new files:

SQL> host mkdir /u01/app/oracle/oradata/cdb3/onlinelog

SQL> host mkdir /u01/app/oracle/fast_recovery_area/cdb3/onlinelog

Shutdown the database now and using OS command copy the files with the new name on the new location:

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>

SQL> host cp /u01/app/oracle/oradata/CDB2/onlinelog/o1_mf_3_cgf7hpvy_.log /u01/app/oracle/oradata/cdb3/onlinelog/redo01a.log

SQL> host cp /u01/app/oracle/fast_recovery_area/CDB2/onlinelog/o1_mf_3_cgf7hpx9_.log /u01/app/oracle/fast_recovery_area/cdb3/onlinelog/redo01b.log

SQL> host cp /u01/app/oracle/oradata/CDB2/onlinelog/o1_mf_2_cgf7hmvc_.log /u01/app/oracle/oradata/cdb3/onlinelog/redo02a.log

SQL> host cp /u01/app/oracle/fast_recovery_area/CDB2/onlinelog/o1_mf_2_cgf7hmx1_.log /u01/app/oracle/fast_recovery_area/cdb3/onlinelog/redo02b.log

SQL> host cp /u01/app/oracle/oradata/CDB2/onlinelog/o1_mf_1_cgf7hk1t_.log /u01/app/oracle/oradata/cdb3/onlinelog/redo03a.log

SQL> host cp /u01/app/oracle/fast_recovery_area/CDB2/onlinelog/o1_mf_1_cgf7hk4f_.log /u01/app/oracle/fast_recovery_area/cdb3/onlinelog/redo03b.log

SQL>

After you create the file, we have to start the database in mount stage and alter the location of these log files:

SQL> startup mount;
ORACLE instance started.

Total System Global Area 2516582400 bytes
Fixed Size 2927528 bytes
Variable Size 671089752 bytes
Database Buffers 1828716544 bytes
Redo Buffers 13848576 bytes
Database mounted.
SQL>
SQL> ALTER DATABASE RENAME FILE ‘/u01/app/oracle/oradata/CDB2/onlinelog/o1_mf_3_cgf7hpvy_.log’ to ‘/u01/app/oracle/oradata/cdb3/onlinelog/redo01a.log’;

Database altered.

SQL> ALTER DATABASE RENAME FILE ‘/u01/app/oracle/fast_recovery_area/CDB2/onlinelog/o1_mf_3_cgf7hpx9_.log’ to ‘/u01/app/oracle/fast_recovery_area/cdb3/onlinelog/redo01b.log’;

Database altered.

SQL> ALTER DATABASE RENAME FILE ‘/u01/app/oracle/oradata/CDB2/onlinelog/o1_mf_2_cgf7hmvc_.log’ to ‘/u01/app/oracle/oradata/cdb3/onlinelog/redo02a.log’;

Database altered.

SQL> ALTER DATABASE RENAME FILE ‘/u01/app/oracle/fast_recovery_area/CDB2/onlinelog/o1_mf_2_cgf7hmx1_.log’ to ‘/u01/app/oracle/fast_recovery_area/cdb3/onlinelog/redo02b.log’;

Database altered.

SQL> ALTER DATABASE RENAME FILE ‘/u01/app/oracle/oradata/CDB2/onlinelog/o1_mf_1_cgf7hk1t_.log’ to ‘/u01/app/oracle/oradata/cdb3/onlinelog/redo03a.log’;

Database altered.

SQL> ALTER DATABASE RENAME FILE ‘/u01/app/oracle/fast_recovery_area/CDB2/onlinelog/o1_mf_1_cgf7hk4f_.log’ to ‘/u01/app/oracle/fast_recovery_area/cdb3/onlinelog/redo03b.log’;

Database altered.

SQL>

After we rename the files using alter command, we have the new location in the controlfile to pick up. So its time to open the database:

SQL> alter database open;

Database altered.

SQL>

Finally check the location of Redo Log File:

SQL> select member from v$logfile;

MEMBER
———————————————————————–
/u01/app/oracle/oradata/cdb3/onlinelog/redo01a.log
/u01/app/oracle/fast_recovery_area/cdb3/onlinelog/redo01b.log
/u01/app/oracle/oradata/cdb3/onlinelog/redo02a.log
/u01/app/oracle/fast_recovery_area/cdb3/onlinelog/redo02b.log
/u01/app/oracle/oradata/cdb3/onlinelog/redo03a.log
/u01/app/oracle/fast_recovery_area/cdb3/onlinelog/redo03b.log

6 rows selected.

SQL>

Thanks for watching.

Let me know if any questions.

 

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s