Showing posts with label REDO RESIZE ON RAC. Show all posts
Showing posts with label REDO RESIZE ON RAC. Show all posts

Thursday, 26 November 2015

REDO RESIZE ON RAC




PRECHECK

**********


Check the size of present REDO
SELECT a.group#, a.member, b.bytes FROM v$logfile a, v$log b WHERE a.group# = b.group#;
SQL> set lines 132
SQL> set trims on
SQL> select * from v$log;


SQL> col member format a35
SQL> select * from v$logfile;

Check for the recommended size by running any of the below queries providing peak hour on your environment.
SELECT
(SELECT ROUND (AVG (BYTES) / 1024 / 1024, 2) FROM V$LOG) AS "Redo size (MB)", ROUND ( (20 / AVERAGE_PERIOD) * (SELECT AVG (BYTES) FROM V$LOG) / 1024 / 1024, 2) AS "Recommended Size (MB)" FROM (SELECT AVG ( (NEXT_TIME - FIRST_TIME) * 24 * 60) AS AVERAGE_PERIOD FROM V$ARCHIVED_LOG WHERE FIRST_TIME > SYSDATE - 3 AND TO_CHAR (FIRST_TIME, 'HH24:MI') BETWEEN '16:00' AND '17:00');
SELECT
(SELECT ROUND(AVG(BYTES) / 1024 / 1024, 2) FROM V$LOG) AS "Redo size (MB)",
ROUND((20 / AVERAGE_PERIOD) * (SELECT AVG(BYTES)
FROM V$LOG) / 1024 / 1024, 2) AS "Recommended Size (MB)"
FROM (SELECT AVG((NEXT_TIME - FIRST_TIME) * 24 * 60) AS AVERAGE_PERIOD
FROM V$ARCHIVED_LOG
WHERE FIRST_TIME > SYSDATE - 3
AND TO_CHAR(FIRST_TIME, 'HH24:MI') BETWEEN
&START_OF_PEAK_HOURS AND &END_OF_PEAK_HOURS);

SQL> select group#,thread#,members,status from v$log;
    GROUP#    THREAD#    MEMBERS STATUS
---------- ---------- ---------- ----------------
         1          1          2 INACTIVE
         2          1          2 CURRENT
         3          2          2 CURRENT
         4          2          2 INACTIVE
         5          3          2 ACTIVE
         6          3          2 CURRENT

Check every time for the status of group you’re going to drop.
Note:
To drop the group the status should be INACTIVE, if it’s no then switch the log file to make it forcibly inactive.

SQL> alter system switch logfile;

Continue this until the group you want to drop comes to INACTIVE state.

EXECUTION


**********

If your each instance has only two groups then create another temporary group.

In RAC, each logfile is associated with thread# each instance can only use one thread,You need a minimal of two group for each thread#.

ALTER DATABASE ADD LOGFILE THREAD 1 GROUP 8 ('+ DISKGROUP_NAME ') SIZE 100M;

When the status is inactive.

Drop the logfile
*************
SQL>  alter database drop logfile group 1;

To remove the file from asm disks (if it is not deleted automatically).

$ export ORACLE_SID=+ASM1
$ sqlplus / as sysdba
 
SQL>  ALTER DISKGROUP DISKGROUP_NAME DROP FILE '+DISKGROUP_NAME/PATH/group_1.XXXXX';


ADD the logfile with your desired size
*******************************
SQL>  ALTER DATABASE ADD LOGFILE THREAD 1 GROUP 1 ('+ DISKGROUP_NAME ','+ DISKGROUP_NAME ') SIZE 500M;

After all done just drop the temporary group

Drop the logfile
*************
SQL>  alter database drop logfile group 8;



Repeat the above steps for the entire instances log file on RAC.


POST CHECK


***********


Check the size of REDO.
SQL> SELECT a.group#, a.member, b.bytes FROM v$logfile a, v$log b WHERE a.group# = b.group#;

Check the status

SQL> select group#,thread#,members,status from v$log;
SQL> set lines 132
SQL> set trims on
SQL> select * from v$log;


SQL> col member format a35
SQL> select * from v$logfile;




There you good to go.................................................

RemoteHostExecutor.pl The file access permissions while patching

Hi, In this article, I am going to share one the issue which we usually face while patching. Here our DB and Grid home are 12.1.0.2, an...