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;
