Oracle March 18, 2009 0

Redo Online Logs: add clear move delete

After default database 10g installation usually we’ve got 2-3 redo log files in 2-3 groups:

SQL>select GROUP#, member from v$logfile;
GROUP#    MEMBER
------    -----------------------------------------
1        /u01/app/oracle/oradata/DBSID/redo01.log
2        /u01/app/oracle/oradata/DBSID/redo02.log
3        /u01/app/oracle/oradata/DBSID/redo03.log

Let’s make some changes and create 3 additional members on a different mount point…

Adding new redo log members to an existing group:

NOTE: The database is up and running.

First, create physical directories:

# as a root user:
# mkdir -p /u02/app ; chmod -R 775 /u02; chown -R root:oinstall /u02

And next:

#as an oracle user:
$ mkdir -p /u02/app/ oracle/oradata/DBSID/
SQL> ALTER DATABASE ADD LOGFILE MEMBER
'/u02/app/oracle/oradata/DBSID/redo1b.log' TO GROUP 1,
'/u02/app/oracle/oradata/DBSID/redo2b.log' TO GROUP 2,
'/u02/app/oracle/oradata/DBSID/redo3b.log' TO GROUP 3;

SQL>select GROUP# , member from v$logfile order by group#;
GROUP#    MEMBER
------    -----------------------------------------
1        /u01/app/oracle/oradata/DBSID/redo01.log
1        /u01/app/oracle/oradata/DBSID/redo1b.log
2        /u01/app/oracle/oradata/DBSID/redo02.log
2        /u01/app/oracle/oradata/DBSID/redo2b.log
3        /u01/app/oracle/oradata/DBSID/redo03.log
3        /u01/app/oracle/oradata/DBSID/redo3b.log

 

Renaming, moving the redo log files

First shutdown the database:

SQL>shutdown immediate

Move, rename physical files on the OS

$mv –v /u01/app/oracle/oradata/DBSID/redo01.log 
/u01/app/oracle/oradata/DBSID/redo1a.log

$mv –v /u01/app/oracle/oradata/DBSID/redo02.log 
/u01/app/oracle/oradata/DBSID/redo2a.log

$mv –v /u01/app/oracle/oradata/DBSID/redo03.log 
/u01/app/oracle/oradata/DBSID/redo3a.log

Go back to SQLPlus and startup database in mount mode:

SQL>STARTUP MOUNT

SQL>ALTER DATABASE RENAME FILE
'/u01/app/oracle/oradata/DBSID/redo01.log' TO
'/u01/app/oracle/oradata/DBSID/redo1a.log';

SQL>ALTER DATABASE RENAME FILE
'/u01/app/oracle/oradata/DBSID/redo02.log' TO
'/u01/app/oracle/oradata/DBSID/redo2a.log';

SQL>ALTER DATABASE RENAME FILE
'/u01/app/oracle/oradata/DBSID/redo03.log' TO
'/u01/app/oracle/oradata/DBSID/redo3a.log';

SQL> ALTER DATABASE OPEN;

SQL>select GROUP# , member from v$logfile order by group#;
GROUP#    MEMBER
------    -----------------------------------------
1        /u01/app/oracle/oradata/DBSID/redo1a.log
1        /u02/app/oracle/oradata/DBSID/redo1b.log
2        /u01/app/oracle/oradata/DBSID/redo2a.log
2        /u02/app/oracle/oradata/DBSID/redo2b.log
3        /u01/app/oracle/oradata/DBSID/redo3a.log
3        /u02/app/oracle/oradata/DBSID/redo3b.log

Creating a new redo log file:

SQL>ALTER DATABASE ADD LOGFILE '/u01/app/oracle/oradata/DBSID/redo4a.log' SIZE 10M

A new group is created automatically.

SQL>select GROUP# , member from v$logfile order by group#;
GROUP#    MEMBER
------    -----------------------------------------
1        /u01/app/oracle/oradata/DBSID/redo1a.log
1        /u02/app/oracle/oradata/DBSID/redo1b.log
2        /u01/app/oracle/oradata/DBSID/redo2a.log
2        /u02/app/oracle/oradata/DBSID/redo2b.log
3        /u01/app/oracle/oradata/DBSID/redo3a.log
3        /u02/app/oracle/oradata/DBSID/redo3b.log
4        /u02/app/oracle/oradata/DBSID/redo4a.log

Creating new multiple log files:

SQL>ALTER DATABASE ADD LOGFILE
('/u02/app/oracle/oradata/DBSID/redo3b.log',
'/u02/app/oracle/oradata/DBSID/redo3b.log' ,
'/u02/app/oracle/oradata/DBSID/redo3b.log')
SIZE 10M;

Switching redo log files group and force to create an archive log file arc.

SQL>ALTER SYSTEM SWITCH LOGFILE;

 

Dropping redo log group and redo log member:

NOTE:
There are some restrictions when dropping redo log groups or redo log files:
– There must be at least two redo log groups and each redo log group must have at least one log member left.
– Only inactive group can be dropped.

To drop a group:

SQL>ALTER DATABASE DROP LOGFILE GROUP 4;

To drop a group member:

SQL>ALTER DATABASE DROP LOGFILE MEMBER
'/u02/app/oracle/oradata/DBSID/redo4A.log';

 

Clearing Log Files and Group

SQL>ALTER DATABASE CLEAR LOGFILE
'/u02/app/oracle/oradata/DBSID/redo3a.log ';

 

When archive log mode is YES, you need to force clear

SQL>ALTER DATABASE CLEAR UNARCHIVED LOGFILE
'/u02/app/oracle/oradata/DBSID/redo3a.log ';

SQL>ALTER DATABASE CLEAR UNARCHIVED LOGFILE GROUP 2;