Only for information (is not ready neeeded here) - how to list the redo log members:
SQL> select group#, status, type, substr(member,1,50) "member" from v$logfile;
GROUP# STATUS TYPE member
---------- ------- ------- --------------------------------------------------
1 ONLINE +U01/a1028t_site1/onlinelog/group_1.257.771684831
1 ONLINE +U02/a1028t_site1/onlinelog/group_1.257.771684833
2 ONLINE +U01/a1028t_site1/onlinelog/group_2.258.771684833
2 ONLINE +U02/a1028t_site1/onlinelog/group_2.258.771684833
3 ONLINE +U01/a1028t_site1/onlinelog/group_3.259.771684835
3 ONLINE +U02/a1028t_site1/onlinelog/group_3.259.771684835
List the redo log groups and their status:
SQL> SELECT group#, thread#, bytes/(1024*1024) "MB", status FROM v$log;
GROUP# THREAD# MB STATUS
---------- ---------- ---------- ----------------
1 1 250 CURRENT
2 1 250 ACTIVE
3 1 250 ACTIVE
Create a new temporary group (here with number 10).
(I think, that this is not realy needed. I think that we only need to perform several "alter system switch logfile;" until the groups that are now ACTIVE become INACTIVE.)
SQL> ALTER DATABASE ADD LOGFILE GROUP 10 SIZE 250m;
Database altered.
Here the new temporary group "10":
SQL> SELECT group#, thread#, bytes/(1024*1024) "MB", status FROM v$log;
GROUP# THREAD# MB STATUS
---------- ---------- ---------- ----------------
1 1 250 ACTIVE
2 1 250 CURRENT
3 1 250 ACTIVE
10 1 250 UNUSED
Now, switch the logfiles until the now "ACTIVE" groups get the status "INACTIVE". . .
SQL> alter system switch logfile;
System altered.
SQL> SELECT group#, thread#, bytes/(1024*1024) "MB", status FROM v$log;
GROUP# THREAD# MB STATUS
---------- ---------- ---------- ----------------
1 1 250 ACTIVE
2 1 250 ACTIVE
3 1 250 ACTIVE
10 1 250 CURRENT
SQL> alter system switch logfile;
System altered.
SQL> alter system switch logfile;
System altered.
. . .
Here we see, that our temporary group "10" is the only active one:
SQL> SELECT group#, thread#, bytes/(1024*1024) "MB", status FROM v$log;
GROUP# THREAD# MB STATUS
---------- ---------- ---------- ----------------
1 1 250 INACTIVE
2 1 250 INACTIVE
3 1 250 INACTIVE
10 1 250 CURRENT
Once we have "INACTIVE" groups, we can delete each of these "INACTIVE" groups and recreate them with the new size.
Deleting "INACTIVE" group "1":
SQL> ALTER DATABASE drop LOGFILE GROUP 1;
Database altered.
SQL> SELECT group#, thread#, bytes/(1024*1024) "MB", status FROM v$log;
GROUP# THREAD# MB STATUS
---------- ---------- ---------- ----------------
2 1 250 INACTIVE
3 1 250 INACTIVE
10 1 250 CURRENT
And recreating it with the new size:
SQL> ALTER DATABASE ADD LOGFILE GROUP 1 SIZE 512m;
Database altered.
SQL> SELECT group#, thread#, bytes/(1024*1024) "MB", status FROM v$log;
GROUP# THREAD# MB STATUS
---------- ---------- ---------- ----------------
1 1 512 UNUSED
2 1 250 INACTIVE
3 1 250 INACTIVE
10 1 250 CURRENT
Also deleting "INACTIVE" group "2" and recreating it with the new site of 512 MB:
SQL> ALTER DATABASE DROP LOGFILE GROUP 2;
Database altered.
SQL> ALTER DATABASE ADD LOGFILE GROUP 2 SIZE 512m;
Database altered.
SQL> SELECT group#, thread#, bytes/(1024*1024) "MB", status FROM v$log;
GROUP# THREAD# MB STATUS
---------- ---------- ---------- ----------------
1 1 512 UNUSED
2 1 512 UNUSED
3 1 250 INACTIVE
10 1 250 CURRENT
I switched the logfile in order to prove that my new group "1" is OK:
SQL> alter system switch logfile;
System altered.
SQL> SELECT group#, thread#, bytes/(1024*1024) "MB", status FROM v$log;
GROUP# THREAD# MB STATUS
---------- ---------- ---------- ----------------
1 1 512 CURRENT
2 1 512 UNUSED
3 1 250 INACTIVE
10 1 250 ACTIVE
And so on for all groups.
Switch the logfile, until the temporary group "10" becomes "INACTIVE":
. . .
SQL> alter system switch logfile;
System altered.
SQL> alter system switch logfile;
System altered.
. . .
Now that the temporary group "10" is "INACTIVE", it can be deleted:
SQL> SELECT group#, thread#, bytes/(1024*1024) "MB", status FROM v$log;
GROUP# THREAD# MB STATUS
---------- ---------- ---------- ----------------
1 1 512 CURRENT
2 1 512 INACTIVE
3 1 512 INACTIVE
10 1 250 INACTIVE
SQL> ALTER DATABASE DROP LOGFILE GROUP 10;
Database altered.
Here is the final result:
SQL> SELECT group#, thread#, bytes/(1024*1024) "MB", status FROM v$log;
GROUP# THREAD# MB STATUS
---------- ---------- ---------- ----------------
1 1 512 CURRENT
2 1 512 INACTIVE
3 1 512 INACTIVE
REMARK: As you can see below, two files have automatically been created for each group:
SQL> select group#, status, type, substr(member,1,50) "member" from v$logfile;
GROUP# STATUS TYPE member
---------- ------- ------- --------------------------------------------------
1 ONLINE +U01/a1028t_site1/onlinelog/group_1.257.771762599
1 ONLINE +U02/a1028t_site1/onlinelog/group_1.257.771762601
2 ONLINE +U01/a1028t_site1/onlinelog/group_2.258.771762649
2 ONLINE +U02/a1028t_site1/onlinelog/group_2.258.771762649
3 ONLINE +U01/a1028t_site1/onlinelog/group_3.259.771762723
3 ONLINE +U02/a1028t_site1/onlinelog/group_3.259.771762725
6 rows selected.
I think that the files were automatically created, because two log destinations have been configured. (I need to verify this):
SQL> show parameter db_create_online_log_dest_
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_create_online_log_dest_1 string +U01
db_create_online_log_dest_2 string +U02
db_create_online_log_dest_3 string
db_create_online_log_dest_4 string
db_create_online_log_dest_5 string
Tags: Oracle, ASM, Redolog, Publish
January 05, 2012 at 10:09AM
No comments :
Post a Comment