- Initial size of redologs are: 50mb we have to resize it to 200mb.
- Diskgroups names : +DATA and +RECO
SQL> select group#,thread#,status,members,bytes/1024/1024 from v$log;
GROUP# THREAD# STATUS MEMBERS BYTES/1024/1024
---------- ---------- ---------------- ---------- ------------------------------------------------------------
1 1 INACTIVE 2 50
2 1 CURRENT 2 50
3 2 INACTIVE 2 50
4 2 CURRENT 2 50
- WE ARE ADDING HERE 100MB SIZE WHICH IS TEMPORARY FOR ALL THESE GROUPs FROM 5 TO 8. LATER I WILL UPDATE AGAIN WITH DESIRED SIZE ie: 200mb.
SQL> alter database add logfile thread 1 group 5 ('+DATA','+RECO') size 100m;
Database altered.
SQL> alter database add logfile thread 1 group 6 ('+DATA','+RECO') size 100m;
Database altered.
SQL> alter database add logfile thread 2 group 7 ('+DATA','+RECO') size 100m;
Database altered.
SQL> alter database add logfile thread 2 group 8 ('+DATA','+RECO') size 100m;
Database altered.
SQL> select inst_id,group#,thread#,members,bytes/1024/1024 from gv$log;
INST_ID GROUP# THREAD# MEMBERS BYTES/1024/1024
---------- ---------- ---------- ---------- --------------------------------------------------------------------
1 1 1 2 50
1 2 1 2 50
1 3 2 2 50
1 4 2 2 50
1 5 1 2 100
1 6 1 2 100
1 7 2 2 100
1 8 2 2 100
2 1 1 2 50
2 2 1 2 50
2 3 2 2 50
2 4 2 2 50
2 5 1 2 100
2 6 1 2 100
2 7 2 2 100
2 8 2 2 100
16 rows selected
>> NOW DROP THE LOGFILES FROM 1 TO 4, BEFORE DROPPING ALL THE LOGFILE STATUS SHOULD BE INACTIVE.
>> MANUALLY SWITCH THE LOGFILE TO CHANGE STATUS AND DROP IT.
SQL> select group#,thread#,status,members,bytes/1024/1024 from v$log;
GROUP# THREAD# STATUS MEMBERS BYTES/1024/1024
---------- ---------- ---------------- ---------- -------------------------------------------------------------
1 1 INACTIVE 2 50
2 1 CURRENT 2 50
3 2 INACTIVE 2 50
4 2 CURRENT 2 50
5 1 UNUSED 2 100
6 1 UNUSED 2 100
7 2 UNUSED 2 100
8 2 UNUSED 2 100
8 rows selected.
SQL> alter database drop logfile group 1;
SQL> alter database drop logfile group 2;
SQL> alter database drop logfile group 3;
SQL> alter database drop logfile group 4;
THESE ARE TEMPORARY GROUPS:-
============================
SQL> select group#,thread#,status,members,bytes/1024/1024 from v$log;
GROUP# THREAD# STATUS MEMBERS BYTES/1024/1024
---------- ---------- ---------------- ---------- -----------------------------------------------------
5 1 INACTIVE 2 100
6 1 CURRENT 2 100
7 2 CURRENT 2 100
8 2 INACTIVE 2 100
NOW DROP THESE TEMPORARY GROUPS FROM 5 TO 8 AND CREATE NEW GROUPS FROM 1 TO 4 OF 200MB SIZE:
========================================================================
SQL> alter database add logfile thread 1 group 1 ('+DATA','+RECO') size 200m;
Database altered.
SQL> alter database add logfile thread 1 group 2 ('+DATA','+RECO') size 200m;
Database altered.
SQL> alter database add logfile thread 2 group 3 ('+DATA','+RECO') size 200m;
Database altered.
SQL> alter database add logfile thread 2 group 4 ('+DATA','+RECO') size 200m;
Database altered.
SQL> select group#,thread#,status,members,bytes/1024/1024 from v$log;
GROUP# THREAD# STATUS MEMBERS BYTES/1024/1024
---------- ---------- ---------------- ---------- ----------------------------------------------------------
1 1 UNUSED 2 200
2 1 UNUSED 2 200
3 2 UNUSED 2 200
4 2 UNUSED 2 200
5 1 INACTIVE 2 100
6 1 CURRENT 2 100
7 2 CURRENT 2 100
8 2 INACTIVE 2 100
8 rows selected.
SQL> alter database drop logfile group 5;
Database altered.
SQL> alter database drop logfile group 6;
Database altered.
SQL> alter database drop logfile group 7;
Database altered.
SQL> alter database drop logfile group 8;
Database altered.
THESE SIZES ARE AS PER MY COMPANY REQUIREMENT:-
==============================================
SQL> select group#,thread#,status,members,bytes/1024/1024 from v$log;
GROUP# THREAD# STATUS MEMBERS BYTES/1024/1024
---------- ---------- ---------------- ---------- ---------------------------------------------------------------
1 1 INACTIVE 2 200
2 1 CURRENT 2 200
3 2 CURRENT 2 200
4 2 INACTIVE 2 200
NOW QUERY THE REDOLOG GROUPS:-
===============================
SQL> select member from v$logfile;
MEMBER
--------------------------------------------------------------------------------
+DATA/hrlive/onlinelog/group_1.276.867359185
+RECO/hrlive/onlinelog/group_1.456.867359185
+DATA/hrlive/onlinelog/group_2.277.867359245
+RECO/hrlive/onlinelog/group_2.362.867359245
+DATA/hrlive/onlinelog/group_3.280.867359337
+RECO/hrlive/onlinelog/group_3.450.867359339
+DATA/hrlive/onlinelog/group_4.281.867359409
+RECO/hrlive/onlinelog/group_4.332.867359409
8 rows selected.
SQL> select inst_id,group#,status,type,member from gv$logfile;
INST_ID GROUP# STATUS TYPE MEMBER
---------- ---------- ------- ------- -------------------------------------------------------------------------------
1 1 ONLINE +DATA/hrlive/onlinelog/group_1.276.867359185
1 1 ONLINE +RECO/hrlive/onlinelog/group_1.456.867359185
1 2 ONLINE +DATA/hrlive/onlinelog/group_2.277.867359245
1 2 ONLINE +RECO/hrlive/onlinelog/group_2.362.867359245
1 3 ONLINE +DATA/hrlive/onlinelog/group_3.280.867359337
1 3 ONLINE +RECO/hrlive/onlinelog/group_3.450.867359339
1 4 ONLINE +DATA/hrlive/onlinelog/group_4.281.867359409
1 4 ONLINE +RECO/hrlive/onlinelog/group_4.332.867359409
2 1 ONLINE +DATA/hrlive/onlinelog/group_1.276.867359185
2 1 ONLINE +RECO/hrlive/onlinelog/group_1.456.867359185
2 2 ONLINE +DATA/hrlive/onlinelog/group_2.277.867359245
2 2 ONLINE +RECO/hrlive/onlinelog/group_2.362.867359245
2 3 ONLINE +DATA/hrlive/onlinelog/group_3.280.867359337
2 3 ONLINE +RECO/hrlive/onlinelog/group_3.450.867359339
2 4 ONLINE +DATA/hrlive/onlinelog/group_4.281.867359409
2 4 ONLINE +RECO/hrlive/onlinelog/group_4.332.867359409
16 rows selected.
SQL> select inst_id,group#,thread#,members,status,bytes/1024/1024 from gv$log;
INST_ID GROUP# THREAD# MEMBERS STATUS BYTES/1024/1024
---------- ---------- ---------- ---------- ---------------- --------------------------------------------------------------
1 1 1 2 INACTIVE 200
1 2 1 2 CURRENT 200
1 3 2 2 CURRENT 200
1 4 2 2 INACTIVE 200
2 1 1 2 INACTIVE 200
2 2 1 2 CURRENT 200
2 3 2 2 CURRENT 200
2 4 2 2 INACTIVE 200
8 rows selected.
Happy Learning ....
No comments:
Post a Comment