CHANGE REDOLOGS AND CONTROLFILE DISKGROUP LOCATION IN ORACLE 19C CDB POST DATABASE CREATION.

Hi Readers, in this blogpost i am sharing steps on how you can change redologs and controlfiles location post db creation if they have been placed mistakenly in wrong diskgroup or redo log size is not as per requirement.

In my case, we had selected recovery area as +FRA diskgroup and dbca created redologs in DB create file dest DATA and FRA in typical installation mode , as it does not ask specifically for redologs location in this creation mode.

REDOLOGS location and size post installation –

column REDOLOG_FILE_NAME format a50;
set lines 1000
SELECT a.GROUP#, a.THREAD#, a.SEQUENCE#,
a.ARCHIVED, a.STATUS, b.MEMBER AS REDOLOG_FILE_NAME,
(a.BYTES/1024/1024) AS SIZE_MB FROM v$log a
JOIN v$logfile b ON a.Group#=b.Group#
ORDER BY a.GROUP#;

So in order to drop these small sized redo logs in FRA and REDO and re-create with 4GB size in REDO diskgroup, i had performed following steps –

1. Add new groups 3 members and 4 GB each size. ensure diskgroup has sufficient space before creation.

Now we have old group with 2 members and 200 MB size and newly created groups with 3 members and 4GB each size.

for dropping old redolog groups make them inactive by switching logfiles and then drop them.

FINAL EXPECTED RESULT –

Similarly lets check how to move controlfiles location to different diskgroup –

current location –

create a pfile from spfile and save the original pfile, perform edits in a new pfile to avoid any issues due to wrong edit.

now shutdown the database

edit controlfile values as below in pfile

login as grid user and login to asmcmd

GO TO REDO DISKGROUP AND CREATE CONTROLFILE DIRECTORY TO PLACE THE CONTROLFILE FROM FRA DISKGROUP

You may encounter below error if you try to copy with same filename. so we will rename the controlfile.

so copy with new file name as follows –

delete the old FRA controlfile (you may perform this step later after testing db startup).

ASMCMD> rm Current.256.1120692059

new controlfile will now show as follows –

ASMCMD> cd +REDO/TESTDB/CONTROLFILE
ASMCMD> ls -lrt
WARNING: option ‘r’ is deprecated for ‘ls’
please use ‘reverse’

Type Redund Striped Time Sys Name
CONTROLFILE UNPROT FINE NOV 14 11:00:00 N control02.ctl => +REDO/ASM/CONTROLFILE/control02.ctl.268.1120736603

similarly to maintain uniformity in controlfile naming I have renamed DATAdiskgroup controlfile as follows –

ASMCMD> cd +DATA/TESTDB/CONTROLFILE
ASMCMD> ls -lrt
WARNING: option ‘r’ is deprecated for ‘ls’
please use ‘reverse’

Type Redund Striped Time Sys Name
CONTROLFILE UNPROT FINE NOV 14 11:00:00 Y Current.261.1120692059
ASMCMD> cp Current.261.1120692059 control01.ctl
copying +DATA/TESTDB/CONTROLFILE/Current.261.1120692059 -> +DATA/TESTDB/CONTROLFILE/control01.ctl
ASMCMD> ls -lrt
WARNING: option ‘r’ is deprecated for ‘ls’
please use ‘reverse’

Type Redund Striped Time Sys Name
CONTROLFILE UNPROT FINE NOV 14 11:00:00 Y Current.261.1120692059
CONTROLFILE UNPROT FINE NOV 14 11:00:00 N control01.ctl => +DATA/ASM/CONTROLFILE/control01.ctl.264.1120736683
ASMCMD> rm Current.261.1120692059

Now startup database with pfile and create a spfile from it.

now perform final startup.if database opens without errors and location is as expected then you are good to go.