Change the log size and add log Group members

2012-01-12 phenomena

Archive log switching is too frequent.

To view alert logs:

Thread 1 advanced to log sequence 12391

Current log# 3 seq# 12391 mem# 0:/usr/app/ora_files/redo03a.log

currentlog# 3 seq# 12391 mem# 1:/app/ora_server/ora_base/oradata/gis/redo03b.log

Thu 12 10:58:51 2012

Thread 1 advanced to log sequence 12392

Current log# 1 seq# 12392 mem# 0:/usr/app/ora_files/redo01a.log

Current log# 1 seq# 12392 mem# 1:/app/ora_server/ora_base/oradata/gis/redo01b.log

Thu 12 11:01:26 2012

Thread 1 advanced to log sequence 12393

Current log# 2 seq# 12393 mem# 0:/usr/app/ora_files/redo02a.log

Current log# 2 seq# 12393 mem# 1:/app/ora_server/ora_base/oradata/gis/redo02b.log

you can see an average of 2-3 minutes for an archive. Review the history of the archive, as follows:

you can see that there is a large amount of archive per hour. Redo switching is too frequent. Reason

A sudden increase in the number of vehicles, the volume of data growth in an instant, and the size of the redo 100M, the current increase in the amount of data led to redo switching too much frequency. As shown in figure:

Handle

Adjusted once before, from the default 50M to 100M, this time again to 200M, and then add two group of log group: 1, view the original log group

Sql>select l.group#, bytes/1024/1024 as Bytem, members, archived, L.status, member

From V$log L, V$logfile F

WHERE l.group# = f.group# (+)

Order by 1;

group#	Bytem	Members	archived	STATUS	Member
1	100	2	NO	Current	/usr/app/ora_files/redo01a.log
1	100	2	NO	Current	/app/ora_server/ora_base/oradata/gis/redo01b.log
2	100	2	YES	ACTIVE	/app/ora_server/ora_base/oradata/gis/redo02b.log
2	100	2	YES	ACTIVE	/usr/app/ora_files/redo02a.log
3	100	2	YES	ACTIVE	/app/ora_server/ora_base/oradata/gis/redo03b.log
3	100	2	YES	ACTIVE	/usr/app/ora_files/redo03a.log

You can see each log group, 2 members, 100M size per member. 2. Add log group and members

sql> ALTER DATABASE Add logfile Group 4 ('/usr/app/ora_files/redo4a.log ') size 200m;

Database altered.

sql> ALTER DATABASE Add logfile Group 5 ('/usr/app/ora_files/redo5a.log ') size 200m;

Database altered.

sql> ALTER DATABASE Add logfile member '/app/ora_server/ora_base/oradata/gis/redo4b.log ' to group 4;

Database altered.

sql> ALTER DATABASE Add logfile member '/app/ora_server/ora_base/oradata/gis/redo5b.log ' to group 5;

Database altered. 3, confirm the added log group

group#	Bytem	Members	archived	STATUS	Member
1	100	2	YES	ACTIVE	/usr/app/ora_files/redo01a.log
1	100	2	YES	ACTIVE	/app/ora_server/ora_base/oradata/gis/redo01b.log
2	100	2	YES	ACTIVE	/app/ora_server/ora_base/oradata/gis/redo02b.log
2	100	2	YES	ACTIVE	/usr/app/ora_files/redo02a.log
3	100	2	YES	INACTIVE	/usr/app/ora_files/redo03a.log
3	100	2	YES	INACTIVE	/app/ora_server/ora_base/oradata/gis/redo03b.log
4	200	2	NO	Current	/usr/app/ora_files/redo4a.log
4	200	2	NO	Current	/app/ora_server/ora_base/oradata/gis/redo4b.log
5	200	2	YES	Unused	/usr/app/ora_files/redo5a.log
5	200	2	YES	Unused	/app/ora_server/ora_base/oradata/gis/redo5b.log

4, delete the log group

sql> ALTER DATABASE drop logfile Group 1;

Database altered.

sql> ALTER DATABASE drop logfile Group 2;

Database altered.

sql> ALTER DATABASE drop logfile Group 3;

ALTER DATABASE drop logfile Group 3

*

ERROR at line 1:

Ora-01623:log 3 is current log for instance GIS (thread 1)-Cannot drop

Ora-00312:online Log 3 thread 1: '/usr/app/ora_files/redo03a.log '

Ora-00312:online Log 3 Thread 1:

'/app/ora_server/ora_base/oradata/gis/redo03b.log '

5. Delete error handling

Note that the error occurred while deleting log Group 3 above,

1 View Log group status first

3	100	2	YES	Current	/usr/app/ora_files/redo03a.log
3	100	2	YES	Current	/app/ora_server/ora_base/oradata/gis/redo03b.log
4	200	2	NO	ACTIVE	/usr/app/ora_files/redo4a.log
4	200	2	NO	ACTIVE	/app/ora_server/ora_base/oradata/gis/redo4b.log
5	200	2	YES	ACTIVE	/usr/app/ora_files/redo5a.log
5	200	2	YES	ACTIVE	/app/ora_server/ora_base/oradata/gis/redo5b.log

Current when the log group is being used, log switching and archiving should be done.

Note: The log group can be deleted only if the status is inactive and archived is yes (this means that the redo log has been archived, otherwise it will be reported to the ora-01623) 6. Switch Log Group

Sql>alter system switch logfile;

Database altered. 7. Archive Log Group

Sql>alter system switch logfile;

Database altered. 8. View Log group status again

3	100	2	YES	Current	/usr/app/ora_files/redo03a.log
3	100	2	YES	Current	/app/ora_server/ora_base/oradata/gis/redo03b.log
4	200	2	NO	ACTIVE	/usr/app/ora_files/redo4a.log
4	200	2	NO	ACTIVE	/app/ora_server/ora_base/oradata/gis/redo4b.log
5	200	2	YES	ACTIVE	/usr/app/ora_files/redo5a.log
5	200	2	YES	ACTIVE	/app/ora_server/ora_base/oradata/gis/redo5b.log

9, delete the log group

sql> ALTER DATABASE drop logfile Group 3;

Database altered. 10. Delete log Files

Operating system level Delete data file (my system is REDHAT5)

RM-RF redo01.log 11, restore the original log group

Increase log members (preferably put members on different disks to do more work, avoid a group of log files all corrupted database downtime)

sql> ALTER DATABASE Add logfile Group 1 ('/usr/app/ora_files/redo01a.log ') size 200m;

Database altered.

sql> ALTER DATABASE Add logfile member '/app/ora_server/ora_base/oradata/gis/redo01b.log ' to group 1;

Database altered.

sql> ALTER DATABASE Add logfile Group 2 ('/usr/app/ora_files/redo02a.log ') size 200m;

Database altered.

sql> ALTER DATABASE Add logfile member '/app/ora_server/ora_base/oradata/gis/redo02b.log ' to group 2;

Database altered.

sql> ALTER DATABASE Add logfile Group 3 ('/usr/app/ora_files/redo03a.log ') size 200m;

Database altered.

sql> ALTER DATABASE Add logfile member '/app/ora_server/ora_base/oradata/gis/redo03b.log ' to group 3;

Database altered.

12. Confirm the final result

group#	Bytem	Members	archived	STATUS	Member
1	200	2	NO	Current	/usr/app/ora_files/redo01a.log
1	200	2	NO	Current	/app/ora_server/ora_base/oradata/gis/redo01b.log
2	200	2	YES	ACTIVE	/app/ora_server/ora_base/oradata/gis/redo02b.log
2	200	2	YES	ACTIVE	/usr/app/ora_files/redo02a.log
3	200	2	YES	ACTIVE	/app/ora_server/ora_base/oradata/gis/redo03b.log
3	200	2	YES	ACTIVE	/usr/app/ora_files/redo03a.log
4	200	2	YES	ACTIVE	/app/ora_server/ora_base/oradata/gis/redo04b.log
4	200	2	YES	ACTIVE	/usr/app/ora_files/redo04a.log
5	200	2	YES	ACTIVE	/app/ora_server/ora_base/oradata/gis/redo05b.log
5	200	2	YES	ACTIVE	/usr/app/ora_files/redo05a.log

improvement measures and recommendations