One of the checks is to increase the redo size
We will need to drop inactive redo logs and recreate them.
1. List details about the redo log
col member format a60
SQL> select a.group#,b.status,a.member from v$logfile a, v$log b where a.group#=b.group# order by 1,3;
GROUP# STATUS MEMBER
---------- ---------------- ------------------------------------------------------------
1 CURRENT /u02/app/oracle/oradata/OEM/onlinelog/o1_mf_1_hgfrth9t_.log
1 CURRENT /u02/app/oracle/oradata/OEM/onlinelog/o1_mf_1_hgfrthlx_.log
1 CURRENT /u03/app/oracle/oradata/OEM/onlinelog/o1_mf_1_hgfrthgd_.log
2 INACTIVE /u02/app/oracle/oradata/OEM/onlinelog/o1_mf_2_hgfrthbp_.log
2 INACTIVE /u02/app/oracle/oradata/OEM/onlinelog/o1_mf_2_hgfrthl8_.log
2 INACTIVE /u03/app/oracle/oradata/OEM/onlinelog/o1_mf_2_hgfrthgd_.log
3 INACTIVE /u02/app/oracle/oradata/OEM/onlinelog/o1_mf_3_hgfrthc8_.log
3 INACTIVE /u02/app/oracle/oradata/OEM/onlinelog/o1_mf_3_hgfrthnd_.log
3 INACTIVE /u03/app/oracle/oradata/OEM/onlinelog/o1_mf_3_hgfrthjo_.log
It was using the oracle managed file (OMF) naming convention. It is ugly also it is only multiplexed on 2 disks.
We see that Group 2 and # are inactive so let us start with that.
SQL> alter database drop logfile group 2;
Database altered.
SQL> alter database add logfile group 2
('/u02/app/oracle/oradata/OEM/onlinelog/u02_redo_2.log',
'/u03/app/oracle/oradata/OEM/onlinelog/u03_redo_2.log',
'/u04/app/oracle/oradata/OEM/onlinelog/u04_redo_2.log')
size 400m; 2 3 4 5
Database altered.
SQL> alter database drop logfile group 3;
Database altered.
SQL> alter database add logfile group 3
('/u02/app/oracle/oradata/OEM/onlinelog/u02_redo_3.log',
'/u03/app/oracle/oradata/OEM/onlinelog/u03_redo_3.log',
'/u04/app/oracle/oradata/OEM/onlinelog/u04_redo_3.log')
size 400m; 2 3 4 5
Database altered.
SQL>
Let us check the logfiles
SQL> select a.group#,b.status,a.member from v$logfile a, v$log b where a.group#=b.group# order by 1,3;
GROUP# STATUS MEMBER
---------- ---------------- ------------------------------------------------------------
1 CURRENT /u02/app/oracle/oradata/OEM/onlinelog/o1_mf_1_hgfrth9t_.log
1 CURRENT /u02/app/oracle/oradata/OEM/onlinelog/o1_mf_1_hgfrthlx_.log
1 CURRENT /u03/app/oracle/oradata/OEM/onlinelog/o1_mf_1_hgfrthgd_.log
2 UNUSED /u02/app/oracle/oradata/OEM/onlinelog/u02_redo_2.log
2 UNUSED /u03/app/oracle/oradata/OEM/onlinelog/u03_redo_2.log
2 UNUSED /u04/app/oracle/oradata/OEM/onlinelog/u04_redo_2.log
3 UNUSED /u02/app/oracle/oradata/OEM/onlinelog/u02_redo_3.log
3 UNUSED /u03/app/oracle/oradata/OEM/onlinelog/u03_redo_3.log
3 UNUSED /u04/app/oracle/oradata/OEM/onlinelog/u04_redo_3.log
Now we need to move the redo group 1 to be unused. Do a switch logfile.
SQL> alter system switch logfile;
System altered.
SQL> select a.group#,b.status,a.member from v$logfile a, v$log b where a.group#=b.group# order by 1,3;
GROUP# STATUS MEMBER
---------- ---------------- ------------------------------------------------------------
1 ACTIVE /u02/app/oracle/oradata/OEM/onlinelog/o1_mf_1_hgfrth9t_.log
1 ACTIVE /u02/app/oracle/oradata/OEM/onlinelog/o1_mf_1_hgfrthlx_.log
1 ACTIVE /u03/app/oracle/oradata/OEM/onlinelog/o1_mf_1_hgfrthgd_.log
2 CURRENT /u02/app/oracle/oradata/OEM/onlinelog/u02_redo_2.log
2 CURRENT /u03/app/oracle/oradata/OEM/onlinelog/u03_redo_2.log
2 CURRENT /u04/app/oracle/oradata/OEM/onlinelog/u04_redo_2.log
3 UNUSED /u02/app/oracle/oradata/OEM/onlinelog/u02_redo_3.log
3 UNUSED /u03/app/oracle/oradata/OEM/onlinelog/u03_redo_3.log
3 UNUSED /u04/app/oracle/oradata/OEM/onlinelog/u04_redo_3.log
9 rows selected.
Notice that group #1 is still ACTIVE so if you try to drop it
SQL> alter database drop logfile group 1;
alter database drop logfile group 1
*
ERROR at line 1:
ORA-01624: log 1 needed for crash recovery of instance oem (thread 1)
ORA-00312: online log 1 thread 1: '/u02/app/oracle/oradata/OEM/onlinelog/o1_mf_1_hgfrth9t_.log'
ORA-00312: online log 1 thread 1: '/u03/app/oracle/oradata/OEM/onlinelog/o1_mf_1_hgfrthgd_.log'
ORA-00312: online log 1 thread 1: '/u02/app/oracle/oradata/OEM/onlinelog/o1_mf_1_hgfrthlx_.log'
Do another switch logfile
SQL> select a.group#,b.status,a.member from v$logfile a, v$log b where a.group#=b.group# order by 1,3;
GROUP# STATUS MEMBER
---------- ---------------- ------------------------------------------------------------
1 ACTIVE /u02/app/oracle/oradata/OEM/onlinelog/o1_mf_1_hgfrth9t_.log
1 ACTIVE /u02/app/oracle/oradata/OEM/onlinelog/o1_mf_1_hgfrthlx_.log
1 ACTIVE /u03/app/oracle/oradata/OEM/onlinelog/o1_mf_1_hgfrthgd_.log
2 ACTIVE /u02/app/oracle/oradata/OEM/onlinelog/u02_redo_2.log
2 ACTIVE /u03/app/oracle/oradata/OEM/onlinelog/u03_redo_2.log
2 ACTIVE /u04/app/oracle/oradata/OEM/onlinelog/u04_redo_2.log
3 CURRENT /u02/app/oracle/oradata/OEM/onlinelog/u02_redo_3.log
3 CURRENT /u03/app/oracle/oradata/OEM/onlinelog/u03_redo_3.log
3 CURRENT /u04/app/oracle/oradata/OEM/onlinelog/u04_redo_3.log
9 rows selected.
###--- wait for the checkpoint so it will write it to the archivelogs. Wait until Group#1 is INACTIVE -- give 2 minutes. Also, you can force the checkpoint if you want.
SQL> /
GROUP# STATUS MEMBER
---------- ---------------- ------------------------------------------------------------
1 INACTIVE /u02/app/oracle/oradata/OEM/onlinelog/o1_mf_1_hgfrth9t_.log
1 INACTIVE /u02/app/oracle/oradata/OEM/onlinelog/o1_mf_1_hgfrthlx_.log
1 INACTIVE /u03/app/oracle/oradata/OEM/onlinelog/o1_mf_1_hgfrthgd_.log
2 ACTIVE /u02/app/oracle/oradata/OEM/onlinelog/u02_redo_2.log
2 ACTIVE /u03/app/oracle/oradata/OEM/onlinelog/u03_redo_2.log
2 ACTIVE /u04/app/oracle/oradata/OEM/onlinelog/u04_redo_2.log
3 CURRENT /u02/app/oracle/oradata/OEM/onlinelog/u02_redo_3.log
3 CURRENT /u03/app/oracle/oradata/OEM/onlinelog/u03_redo_3.log
3 CURRENT /u04/app/oracle/oradata/OEM/onlinelog/u04_redo_3.log
9 rows selected.
Now Group 1 is INACTIVE, you can continue to recreate group1;
SQL> alter database drop logfile group 1;
Database altered.
SQL> alter database add logfile group 1
('/u02/app/oracle/oradata/OEM/onlinelog/u02_redo_1.log',
'/u03/app/oracle/oradata/OEM/onlinelog/u03_redo_1.log',
'/u04/app/oracle/oradata/OEM/onlinelog/u04_redo_1.log')
size 400m; 2 3 4 5
Database altered.
SQL>
CONGRATULATIONS !!! you have successfully renamed, resize your redologs
SQL> select a.group#,b.status,a.member from v$logfile a, v$log b where a.group#=b.group# order by 1,3;
GROUP# STATUS MEMBER
---------- ---------------- ------------------------------------------------------------
1 UNUSED /u02/app/oracle/oradata/OEM/onlinelog/u02_redo_1.log
1 UNUSED /u03/app/oracle/oradata/OEM/onlinelog/u03_redo_1.log
1 UNUSED /u04/app/oracle/oradata/OEM/onlinelog/u04_redo_1.log
2 INACTIVE /u02/app/oracle/oradata/OEM/onlinelog/u02_redo_2.log
2 INACTIVE /u03/app/oracle/oradata/OEM/onlinelog/u03_redo_2.log
2 INACTIVE /u04/app/oracle/oradata/OEM/onlinelog/u04_redo_2.log
3 CURRENT /u02/app/oracle/oradata/OEM/onlinelog/u02_redo_3.log
3 CURRENT /u03/app/oracle/oradata/OEM/onlinelog/u03_redo_3.log
3 CURRENT /u04/app/oracle/oradata/OEM/onlinelog/u04_redo_3.log
9 rows selected.
No comments:
Post a Comment