We cannot resize the redolog files .We must drop the redo log files and recreate .This is only method to resize the redo log files.
SQL> startup;
ORACLE instance started.
Total System Global Area 2137886720 bytes
Fixed Size 2254952 bytes
Variable Size 503318424 bytes
Database Buffers 1627389952 bytes
Redo Buffers 4923392 bytes
Database mounted.
Database opened.
SQL> select group#,status,member from v$logfile;
GROUP# STATUS
---------- -------
MEMBER
--------------------------------------------------------------------------------
3
/apps/oradata/redologs1/redo03.log
2
/apps/oradata/redologs1/redo02.log
1
/apps/oradata/redologs1/redo01.log
SQL> select status,group#,bytes/1024/1024 from v$log;
STATUS GROUP# BYTES/1024/1024
---------------- ---------- ---------------
INACTIVE 1 50
CURRENT 2 50
UNUSED 3 50
[ ( SQL> alter database logfile group 1 '/apps/oradata/redologs1/redo01.log' resize 100m;
alter database logfile group 1 '/apps/oradata/redologs1/redo01.log' resize 100m
*
ERROR at line 1:
ORA-02231: missing or invalid option to ALTER DATABASE
* We cannot easily drop a redolog file by using above sql queries) ]
SQL> alter system switch logfile;
System altered.
SQL> select status,group#,bytes/1024/1024 from v$log;
STATUS GROUP# BYTES/1024/1024
---------------- ---------- ---------------
CURRENT 1 50
ACTIVE 2 50
ACTIVE 3 50
SQL> alter system switch logfile;
System altered.
SQL> select status,group#,bytes/1024/1024 from v$log;
STATUS GROUP# BYTES/1024/1024
---------------- ---------- ---------------
INACTIVE 1 50
CURRENT 2 50
INACTIVE 3 50
Now we can drop the inactive redolog group 1 & 3;
SQL> alter system checkpoint global;
System altered.
( The following set of operations starts when checkpoint occurs: Log records from log buffer (including the last log record) are written to the disk )
SQL> alter database drop logfile group 1;
Database altered.
SQL> alter database add logfile group 1 '/apps/oradata/redologs1/redo01.log' size 100m;
alter database add logfile group 1 '/apps/oradata/redologs1/redo01.log' size 100m
*
ERROR at line 1:
ORA-00301: error in adding log file '/apps/oradata/redologs1/redo01.log' - file
cannot be created
ORA-27038: created file already exists
Additional information: 1
*We have remove or delete the old redologs in OS level command :
SQL> exit
Disconnected from Oracle Database 11g Release 11.2.0.4.0 - 64bit Production
[oracle@localhost dbs]$ cd /apps/oradata/redologs1/
[oracle@localhost redologs1]$ ls
redo01.log redo02.log redo03.log redolog3a.log
[oracle@localhost redologs1]$ rm redo01.log
[oracle@localhost redologs1]$ ls
redo02.log redo03.log redolog3a.log
[oracle@localhost redologs1]$ sqlplus '/as sysdba'
SQL*Plus: Release 11.2.0.4.0 Production on Wed Sep 25 11:28:35 2019
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Release 11.2.0.4.0 - 64bit Production
SQL> select status,group#,bytes/1024/1024 from v$log;
STATUS GROUP# BYTES/1024/1024
---------------- ---------- ---------------
CURRENT 2 50
INACTIVE 3 50
SQL> alter database add logfile group 1 '/apps/oradata/redologs1/redo01.log' size 100m;
Database altered.
SQL> select status,group#,bytes/1024/1024 from v$log;
STATUS GROUP# BYTES/1024/1024
---------------- ---------- ---------------
UNUSED 1 100
CURRENT 2 50
INACTIVE 3 50
In beginning the size of redolog group 1 is of 50m and by now the group 1 is resized as 100m
The simplest way to resize a redo logs is done !!
No comments:
Post a Comment