Database Administration Basics -2

Administer Redo Logs

List the number and location of existing logfiles and display the number of logfile groups and members the database has.
This gives us the location of the logfiles
SQL> select group#, member from v$logfile;
GROUP# MEMBER
---------- ----------------------------------------
1 C:\ORACLE\ORADATA\TEST\redo01.log
2 C:\ORACLE\ORADATA\TEST\redo02.LOG
3 C:\ORACLE\ORADATA\TEST\redo03.LOG3 rows selected.

This tells us whether the redologs are multiplexed or not

SQL> select group#, members from V$log;
GROUP# MEMBERS
---------- ----------
1 1
2 1
3 1
3 rows selected.

Since we have only one member each we infer that the redo logs are not Multiplexed.

Check whether the database is in which archiver mode

SQL> select archiver from v$instance;
ARCHIVE
-------
STARTED
1 row selected.

SQL> select log_mode from v$database;
LOG_MODE
------------
ARCHIVELOG
1 row selected.

Identify the datafile that makes up the UNDO_3 Tablespace

SQL> select file_name from dba_data_files where tablespace_name='UNDO_3';
FILE_NAME
----------------------------------------
C:\ORACLE\ORADATA\TEST\UNDO3.DBF
1 row selected.

Find the total freespace available in the database

SQL> select sum(bytes/1024/1024) "FREESPACE IN MB" from dba_free_space;
FREESPACE IN MB
----------------
415.875
1 row selected.

Find the total space available in the database

SQL> SELECT SUM(BYTES/1024/1024) "USEDSPACE IN MB" FROM DBA_SEGMENTS; USEDSPACE IN MB
---------------
795.71875
1 row selected.

Add a Redo Log Group 4 with two members log04a.log and log04b.log with 10mb each

SQL> alter database add logfile group 4 ('c:/oracle/oradata/test/redo04a.log' , 'c:/oracle/oradata/test/redo04b.log') size 10m;
Database altered.

Verification

SQL> select member from v$logfile where group#=4;
MEMBER
----------------------------------------
C:\ORACLE\ORADATA\TEST\REDO04A.LOG
C:\ORACLE\ORADATA\TEST\REDO04B.LOG
2 rows selected.

Remove the redolog group created in the above step Before removing We need to make sure that the logfiles are not the current or active ones rather they should be inactive
SQL> select group#,status from v$log;
GROUP# STATUS
---------- ----------------
1 INACTIVE
2 INACTIVE
3 CURRENT
4 UNUSED
4 rows selected.

Since this group 4 is unused we can safely drop the same

SQL> alter database drop logfile group 4;
Database altered.

SQL> select group#,status from v$log;
GROUP# STATUS#
---------- ----------------
1 INACTIVE
2 INACTIVE
3 CURRENT
3 rows selected.

Resize all the online redolog groups to 5mb We cannot resize the online redolog files , the only way to resize is to add new log files and drop the old ones

SQL> alter database add logfile group 4
'c:/oracle/oradata/test/redo04.log' size 5m;
Database altered.

SQL> alter database add logfile group 5
'c:/oracle/oradata/test/redo05.log' size 5m;
Database altered.

Now try to add the group 6 what happens why ?

SQL> alter database add logfile group 6 'c:/oracle/oradata/test/redo06.log' size 5m ; alter database add logfile group 6 'c:/oracle/oradata/test/redo06.log' size 5m*ERROR at line 1:ORA-01185: logfile group number 6 is invalid MAXLOGFILES:
------------
MAXLOGFILES is set during database creation, via the create database statement, and can only be changed by recreating the database or recreating the control file. This is set to 5 currently

Here as we have to drop log group 1,2 and 3 we can create the THIRD group after dropping them
SQL> select group#,status from v$log;
GROUP# STATUS
---------- ----------------
3 CURRENT
4 UNUSED
5 UNUSED
3 rows selected.

SQL> alter system switch logfile; System altered.

SQL> alter system switch logfile; System altered.

SQL> alter system switch logfile; System altered.

SQL> alter system switch logfile; System altered.

SQL> select group#,status from v$log;
GROUP# STATUS
---------- ----------------
3 INACTIVE
4 ACTIVE
5 CURRENT
3 rows selected.

SQL> alter database drop logfile group 3;
Database altered.

Now let us create the third group and name it as group 6 and see what happens

SQL> alter database add logfile group 6
'c:/oracle/oradata/test/redo06.log' size 5m ;alter database add logfile group 6
'c:/oracle/oradata/test/redo06.log' size 5m*ERROR at line 1:ORA-01185: logfile group number 6 is invalid
Oracle will not allow to create a logfile group with group# 6

SQL> alter database add logfile group 1 'c:/oracle/oradata/test/redo06.log' size 5m ;Database altered.
We can only specify group numbers from 1 to 5 , to change this behaviour we need to recreate the control file with MAXLOGFILES SET TO 6 .

For more on administration please check out

http://www.exforsys.com/content/category/17/261/343/