{"id":2324,"date":"2017-03-28T15:07:15","date_gmt":"2017-03-28T12:07:15","guid":{"rendered":"http:\/\/www.fatihacar.com\/blog\/?p=2324"},"modified":"2017-03-28T16:59:12","modified_gmt":"2017-03-28T13:59:12","slug":"oracle-11g-r2-resize-redo-log-size-on-data-guard-and-primary-database","status":"publish","type":"post","link":"http:\/\/www.fatihacar.com\/blog\/oracle-11g-r2-resize-redo-log-size-on-data-guard-and-primary-database\/","title":{"rendered":"Oracle 11g R2 Resize Redo Log Size on Data Guard and Primary Database"},"content":{"rendered":"

Sometimes, you may need to change redo log size to perform optimum size for performance. If you want to change redo log size of both standby side and primary side, you can use below commands. <\/p>\n

Current Status Of Redo Logs<\/strong><\/h3>\n

Primary Side<\/strong><\/p>\n

\n
\r\nSQL> select group#,sum(bytes\/1024\/1024)\"Size in MB\" from v$log group by group#;\r\n \r\nGROUP#     Size in MB\r\n-------    ----------\r\n1          50\r\n2          50\r\n3          50\r\n \r\nSQL> select group#,sum(bytes\/1024\/1024)\"Size in MB\" from v$standby_log group by group#;\r\n \r\nGROUP#   Size in MB\r\n-------  ----------\r\n 4       50\r\n 5       50\r\n 6       50\r\n 7       50\r\n\r\n<\/code><\/pre>\n<\/blockquote>\n
Standby Side<\/strong><\/p>\n
\n
\r\n\r\nSQL> select group#, sum(bytes\/1024\/1024)\"Size in MB\" from v$loggroup by group#;\r\n \r\nGROUP# Size in MB\r\n------ -------------\r\n 1     50\r\n 2     50\r\n 3     50\r\n \r\nSQL> select group#,sum(bytes\/1024\/1024)\"Size in MB\" from v$standby_log group by group#;\r\n \r\nGROUP# Size in MB\r\n------ ----------\r\n 4     50\r\n 5     50\r\n 6     50\r\n 7     50\r\n\r\n<\/code><\/pre>\n<\/blockquote>\n
Change Size Of Redo Logs<\/strong><\/h3>\n
Auto File Management Disable on Standby Side<\/strong><\/p>\n
\n
\r\nSQL> alter system set standby_file_management=manual\r\n\r\nSystem altered.\r\n<\/code><\/pre>\n<\/blockquote>\n
Change Redo Log Size Of Primary Side<\/strong>
\n<\/p>\n
\n
\r\nNote: You can drop log file when status is INACTIVE or UNUSED. Check status of redo log files.\r\n\r\nSQL> select group#,status from v$log;\r\n \r\nGROUP# STATUS\r\n------ -------------------\r\n1      INACTIVE\r\n2      INACTIVE\r\n3      CURRENT\r\n\r\nSQL> alter database drop logfile group 1;\r\n \r\nDatabase altered.\r\n \r\nSQL> alter database add logfile group 1 size 100M;\r\n \r\nDatabase altered.\r\n \r\nSQL> select group#,status from v$log;\r\n \r\nGROUP# STATUS\r\n------ -------\r\n1      UNUSED\r\n2      INACTIVE\r\n3      CURRENT\r\n \r\nSQL> alter database drop logfile group 2;\r\n \r\nDatabase altered.\r\n \r\nSQL> alter database add logfile group 2 size 100M;\r\n \r\nDatabase altered.\r\n \r\nSQL> select group#,status from v$log;\r\n \r\nGROUP# STATUS\r\n------ -------\r\n1      UNUSED\r\n2      UNUSED\r\n3      CURRENT\r\n\r\nNote : You can use switch logfile command to change status of group 3 redo log files. \r\n\r\nSQL> alter system switch logfile;\r\n\r\nSystem altered.\r\n\r\nSQL> select group#,status from v$log;\r\n \r\nGROUP# STATUS\r\n------ -------\r\n1      CURRENT\r\n2      UNUSED\r\n3      INACTIVE\r\n\r\nSQL> alter database drop logfile group 3;\r\n \r\nDatabase altered.\r\n \r\nSQL> alter database add logfile group 3 size 100M;\r\n \r\nDatabase altered.\r\n\r\nSQL> select group#,sum(bytes\/1024\/1024)\"size in MB\" from v$log group by group#;\r\n \r\nGROUP# size in MB\r\n------ -------------\r\n1      100\r\n2      100\r\n3      100\r\n\r\nStandby Redo Log Resizing<\/strong>\r\n\r\nNote : If you do not switchover, your standby log file status on primary side should be UNASSIGNED\r\n\r\nSQL> select group#,sum(bytes\/1024\/1024)\"size in MB\" from v$standby_log group by group#;\r\n \r\nGROUP# size in MB\r\n------ -------------\r\n6      50\r\n4      50\r\n5      50\r\n7      50\r\n \r\nSQL> select group#,status from v$standby_log;\r\n \r\nGROUP# STATUS\r\n------ ------------\r\n4      UNASSIGNED\r\n5      UNASSIGNED\r\n6      UNASSIGNED\r\n7      UNASSIGNED\r\n\r\nSQL> alter database drop standby logfile group 4;\r\n \r\nDatabase altered.\r\n \r\nSQL> alter database add standby logfile group 4 size 100M;\r\n \r\nDatabase altered.\r\n\r\nSQL> alter database drop standby logfile group 5;\r\n \r\nDatabase altered.\r\n \r\nSQL> alter database add standby logfile group 5 size 100M;\r\n \r\nDatabase altered.\r\n\r\nSQL> alter database drop standby logfile group 6;\r\n \r\nDatabase altered.\r\n \r\nSQL> alter database add standby logfile group 6 size 100M;\r\n \r\nDatabase altered.\r\n\r\nSQL> alter database drop standby logfile group 7;\r\n \r\nDatabase altered.\r\n \r\nSQL> alter database add standby logfile group 7 size 100M;\r\n \r\nDatabase altered.\r\n\r\nSQL> select group#,sum(bytes\/1024\/1024)\"size in MB\" from v$standby_log group by group#;\r\n \r\nGROUP# size in MB\r\n------ -------------\r\n4      100\r\n5      100\r\n6      100\r\n7      100\r\n<\/code><\/pre>\n<\/blockquote>\n
Change Redo Log Size Of Standby Side<\/strong><\/p>\n
\n
\r\n\r\nNote : Firstly, cancel appling from primary.\r\n\r\nSQL> alter database recover managed standby database cancel;\r\n \r\nDatabase altered.\r\n\r\nNote : Redo log status have to be UNUSED. You can use clear command and then you can drop redo log files.\r\n\r\nSQL> select group#,status from v$log;\r\n \r\nGROUP# STATUS\r\n------ -------------------\r\n1      CURRENT\r\n2      CLEARING\r\n3      CLEARING\r\n\r\nNote : If you attempt to drop redo log file when status is not UNUSED, you can take below error.\r\n\r\nSQL> alter database drop logfile group 2;\r\nalter database drop logfile group 2\r\n*\r\nERROR at line 1:\r\nORA-01156: recovery or flashback in progress may need access to files.\r\n\r\nNote : You have to clear redo log file before drop.\r\n\r\nSQL> alter database clear logfile group 2;\r\n \r\nDatabase altered.\r\n \r\nSQL> alter database drop logfile group 2;\r\n \r\nDatabase altered.\r\n \r\nSQL> alter database add logfile group 2 size 100M;\r\n \r\nDatabase altered.\r\n\r\nSQL> alter database clear logfile group 3;\r\n \r\nDatabase altered.\r\n \r\nSQL> alter database drop logfile group 3;\r\n \r\nDatabase altered.\r\n \r\nSQL> alter database add logfile group 3 size 100M;\r\n \r\nDatabase altered.\r\n\r\nSQL> select group#,status from v$log;\r\n \r\nGROUP# STATUS\r\n------ -------------------\r\n1      CURRENT\r\n2      UNUSED\r\n3      UNUSED\r\n\r\nNote : You can use switch logfile command on primary side to change log file status of standby database.\r\n\r\nOn primary side\r\n\r\nSQL> alter system switch logfile;\r\n\r\nSystem altered.\r\n\r\nNote : You can clear and drop redo log file on standby site.\r\n\r\nSQL> alter database clear logfile group 3;\r\n \r\nDatabase altered.\r\n \r\nSQL> alter database drop logfile group 3;\r\n \r\nDatabase altered.\r\n \r\nSQL> alter database add logfile group 3 size 100M;\r\n \r\nDatabase altered.\r\n\r\nSQL> select group#,sum(bytes\/1024\/1024)\"size in MB\" from v$log group by group#;\r\n \r\nGROUP# size in MB\r\n------ -------------\r\n1      100\r\n2      100\r\n3      100\r\n\r\nStandby Redo Log Resizing<\/strong>\r\n\r\nSQL> select group#,sum(bytes\/1024\/1024)\"size in MB\" from v$standby_log group by group#;\r\n \r\nGROUP# size in MB\r\n------ --------------\r\n4      50\r\n5      50\r\n6      50\r\n7      50\r\n \r\nSQL> select group#,status from v$standby_log;\r\n \r\nGROUP# STATUS\r\n------ -------------\r\n4      UNASSIGNED\r\n5      UNASSIGNED\r\n6      UNASSIGNED\r\n7      ACTIVE\r\n\r\nSQL> alter database drop standby logfile group 4;\r\n \r\nDatabase altered.\r\n \r\nSQL> alter database add standby logfile group 4 size 100M;\r\n \r\nDatabase altered.\r\n\r\nSQL> alter database drop standby logfile group 5;\r\n \r\nDatabase altered.\r\n \r\nSQL> alter database add standby logfile group 5 size 100M;\r\n \r\nDatabase altered.\r\n\r\nSQL> alter database drop standby logfile group 6;\r\n \r\nDatabase altered.\r\n \r\nSQL> alter database add standby logfile group 6 size 100M;\r\n \r\nDatabase altered.\r\n\r\nNote : You can use switch logfile command on primary side to change log file status of standby database.\r\n\r\nOn primary side\r\n\r\nSQL> alter system switch logfile;\r\n\r\nSystem altered.\r\n\r\nNote : You can clear and drop redo log file on standby site.\r\n\r\nSQL> alter database drop standby logfile group 7;\r\n \r\nDatabase altered.\r\n \r\nSQL> alter database add standby logfile group 7 size 100M;\r\n \r\nDatabase altered.\r\n \r\nSQL> select group#,sum(bytes\/1024\/1024)\"size in MB\" from v$standby_log group by group#;\r\n \r\nGROUP# size in MB\r\n------ -------------\r\n4      100\r\n5      100\r\n6      100\r\n7      100\r\n\r\n<\/code><\/pre>\n<\/blockquote>\n
Auto File Management Enable on Standby Side<\/strong><\/p>\n
\n
\r\nSQL> alter system set standby_file_management=auto\r\n\r\nSystem altered.\r\n<\/code><\/pre>\n<\/blockquote>\n
Enable Apply Redo From Primary Side<\/strong><\/p>\n
\n
\r\nSQL> alter system set standby_file_management=auto\r\n\r\nSystem altered.\r\n\r\nSQL> alter database recover managed standby database using current logfile disconnect;\r\n\r\nDatabase altered.\r\n<\/code><\/pre>\n<\/blockquote>\n","protected":false},"excerpt":{"rendered":"
Sometimes, you may need to change redo log size to perform optimum size for performance. If you want to change redo log size of both standby side and primary side, you can use below commands. Current Status Of Redo Logs Primary Side SQL> select group#,sum(bytes\/1024\/1024)”Size in MB” from v$log group by group#; GROUP# Size in…<\/p>\n","protected":false},"author":37,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"jetpack_post_was_ever_published":false,"_jetpack_newsletter_access":"","_jetpack_dont_email_post_to_subs":true,"_jetpack_newsletter_tier_id":0,"_jetpack_memberships_contains_paywalled_content":false,"_jetpack_memberships_contains_paid_content":false,"footnotes":""},"categories":[9,20,53],"tags":[14,32],"class_list":["post-2324","post","type-post","status-publish","format-standard","hentry","category-administration","category-oracle-backup-and-recovery","category-oracle-errors-and-solutions","tag-database-administration","tag-oracle-administration"],"jetpack_featured_media_url":"","jetpack_shortlink":"https:\/\/wp.me\/p39NFI-Bu","jetpack_sharing_enabled":true,"_links":{"self":[{"href":"http:\/\/www.fatihacar.com\/blog\/wp-json\/wp\/v2\/posts\/2324","targetHints":{"allow":["GET"]}}],"collection":[{"href":"http:\/\/www.fatihacar.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/www.fatihacar.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/www.fatihacar.com\/blog\/wp-json\/wp\/v2\/users\/37"}],"replies":[{"embeddable":true,"href":"http:\/\/www.fatihacar.com\/blog\/wp-json\/wp\/v2\/comments?post=2324"}],"version-history":[{"count":6,"href":"http:\/\/www.fatihacar.com\/blog\/wp-json\/wp\/v2\/posts\/2324\/revisions"}],"predecessor-version":[{"id":2330,"href":"http:\/\/www.fatihacar.com\/blog\/wp-json\/wp\/v2\/posts\/2324\/revisions\/2330"}],"wp:attachment":[{"href":"http:\/\/www.fatihacar.com\/blog\/wp-json\/wp\/v2\/media?parent=2324"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/www.fatihacar.com\/blog\/wp-json\/wp\/v2\/categories?post=2324"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/www.fatihacar.com\/blog\/wp-json\/wp\/v2\/tags?post=2324"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}