Show Redo Logs information

redologs: Show Redo Logs info.sql

set linesize 300
column REDOLOG_FILE_NAME format a50
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# ASC;

--Creating Redo Log Groups:
ALTER DATABASE
ADD LOGFILE ('/home/oracle/redo/b_redo01.log', '/home/oracle/redo/b_redo02.log') SIZE 50M;

ALTER DATABASE 
ADD LOGFILE GROUP 4 ('/home/oracle/redo/b_redo04.log', '/home/oracle/redo/b_redo04.log')
SIZE 50M BLOCKSIZE 512;

--Creating Redo Log Members:
ALTER DATABASE ADD LOGFILE MEMBER '/home/oracle/redo/b_redo04.log' TO GROUP 4;

ALTER DATABASE ADD LOGFILE MEMBER '/oracle/dbs/log2c.rdo'
TO ('/oracle/dbs/log2a.rdo', '/oracle/dbs/log2b.rdo');
    
    
--Relocating and Renaming Redo Log Members
SHUTDOWN
mv /diska/logs/log1a.rdo /diskc/logs/log1c.rdo
mv /diska/logs/log2a.rdo /diskc/logs/log2c.rdo
CONNECT / as SYSDBA
STARTUP MOUNT
ALTER DATABASE 
RENAME FILE '/diska/logs/log1a.rdo', '/diska/logs/log2a.rdo' 
TO '/diskc/logs/log1c.rdo', '/diskc/logs/log2c.rdo';
ALTER DATABASE OPEN;


--Dropping Log Groups
ALTER DATABASE DROP LOGFILE GROUP 3;

--Dropping Redo Log Members
ALTER DATABASE DROP LOGFILE MEMBER '/oracle/dbs/log3c.rdo';

--Forcing Log Switches
ALTER SYSTEM SWITCH LOGFILE;