eMarcel.com

Oracle Database, Fusion Middleware, Linux

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;

 

(Visited 182 times, 1 visits today)

, ,

Tweet
Share13
+1
Share
13 Shares

By continuing to use the site, you agree to the use of cookies. more information

The cookie settings on this website are set to "allow cookies" to give you the best browsing experience possible. If you continue to use this website without changing your cookie settings or you click "Accept" below then you are consenting to this.

Close