Increase SGA for an oracle database in windows with a dataguard physical standby

Environment:

Oracle: 11.2.0.3 enterprise edition

OS: Windows 2008 R2 Standard

OEM: 12C

 

Task: To increase SGA on a production single instance database with a single instance physical standby database.

Research: Ensure there is enough room for the increase in SGA. In our case the

Total RAM on server: 12GB RAM.
Current SGA: 4GB
Current PGA : 5GB

Required SGA: 6GB

For running a windows 64 bit version, Microsoft recommends a RAM of 2GB for OS. We wanted to increase the SGA to 6GB. But this would take the total RAM requirement to 2GB (OS)+ 6GB (SGA)+5GB (PGA) = 13GB which is more than available 12GB. As there was a shortage, after analyzing the PGA requirement during the peak period for the database (memory advisors), we decided to decrease the PGA to 3GB and increase SGA to 6GB so that there would be enough RAM for OS.

1)Create Blackout in OEM 12C
Blackout both the primary and standby db instance (just primary would do, but didn’t want to get alerted by lag and hence included standby too)

2)Defer Sync: Connect to primary database and defer sync to standby

show parameter archive_dest
alter system set log_archive_dest_state_2=defer scope=both;

3)Check and amend memory parameters on primary

show parameter sga
show parameter pga
alter system set sga_target=6G scope=spfile
alter system set sga_max_size=6G scope=spfile
alter system set pga_aggregate_target=3G scope=spfile
shutdown immediate
start up

4)verify changes on primary

show parameter target
show parameter sga
show parameter pga

5)validate primary database after bounce
check listener listening to the db service
connect via tns entry and verify successful connection
check alert log

6)Re-enable sync to standby

alter system set log_archive_dest_state_2=enable scope=both;
show parameter archive_dest

 

7)verify sync
on primary
archive log list

On standby
archive log list

On Primary
alter system switch logfile
alter system switch logfile
archive log list

On standby
archive log list
Ensure the current log sequence is upto date

SQL> archive log list
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            G:\oracle\Archive\
Oldest online log sequence     34540
Next log sequence to archive   0
Current log sequence           34547

7)verify alert log on standby

In windows environment viewing alert log for latest updates is a pain as we have to close and reopen the alert log for the latest updates. If the alert log is huge then this is frustrating. ADRCI comes in handy in windows.

adrci> set homepath diag\rdbms\gmcfad01\gmcfad01
adrci> show alert –tail

 

7)Remove blackout in OEM