Physical memory expansion, oracle 11g R1 database Parameter Modification

Background:
The physical memory of the master and slave machines is doubled. After the memory is expanded, You need to modify the memory parameters related to the database, including the database and operating system. After doubling the memory size of the backup machine, modify the relevant memory parameters and start the database to the backup machine for two days. If no problem exists, then, double the physical memory of the previous host to switch the database to the slave host. The reason for doing so is that the operating system needs to be shut down during the resizing operation, and the operation may be performed twice in one night. At the same time, the operation can be better ensured, the master and slave servers can run normally after memory expansion and modification of relevant database parameters.

Environment Introduction:

Dual-host operating system: solaris 10 database version: oracle 11g R1 64bit current host hostname and IP: fly-db1 192.168.20.20 current slave host hostname and IP: fly-db2 192.168.20.21 physical memory, 94 GB before expansion and 188 GB after expansion

1. Confirm the memory management mode of the database: manual management

SQL> show parameter memory_targetNAME                                 TYPE        VALUE------------------------------------ ----------- -------memory_target                        big integer   0SQL> show parameter sga_targetNAME                                 TYPE        VALUE------------------------------------ ----------- -------sga_target                           big integer     0

2. view the current spfile path: shared storage

SQL> show parameter spfile <--- if shared storage is used, the actual storage location of spfile is stored. <--- If/XXX/dbs/spfile_SID.ora is displayed, you can use the strings command to view the content to determine the true spfile path.

3. Just after confirmation that the parameter files of the database adopt shared storage, all file control files, online logs, data file table space and temporary tablespace data files of the database), parameter files) the files in the Master/Slave database are bare devices, and the permissions are oracle: oinstall or oracle: dba, this problem has been encountered before the switchover fails because the database file permissions are incorrect or the data files used in the file system are used, so you need to confirm again)

Bash-3.00 $ hostnamefly-db1bash-3.00 $ cat check_file. SQL // reset set heading offset echo offset newpage noneset feedback offspool oracle_131215.txtselect file_name from dba_data_files; select file_name from dba_temp_files; select member from v $ logfile; select name from v $ controlfile; select value from v $ parameter where name = 'spfile'; spool offbash-3.00 $ sqlplus/nol OgSQL> conn/as sysdbaConnected. SQL> @ check_file.sqlSQL> quitbash-3.00 $ lscheck_file. SQL oracle_131215.txtbash-3.00 $ for I in $ (cat oracle_131215.txt); do ls-l $ I; done // view which files are omitted) whether it is a bare device or a character device ), permission is correct crw ------- 1 oracle dba 342,200 Dec 15/dev/raw/raw1crw ------- 1 oracle dba 342,201 Dec 15/dev/raw/raw2crw ------- 1 oracle dba 342,202 Dec 15 /dev/raw/raw3crw ------- 1 oracle dba 342,254 Dec 15/dev/raw/raw4crw ------- 1 oracle dba 342,253 Dec 15/dev/raw/raw5bash-3.00 $ for I in $ (cat oracle_131215.txt ); do ls-l $ I | grep-v ^ c; done // if there are too many files, click it to select a file starting with c. If there is no output, that is, all files with the root keyword are raw bash-3.00 $ for I in $ (cat oracle_131215.txt); do ls-l $ I | grep root; done // click it to select a file with the root keyword, when the file is created, the default is root: rootbash-3.00 $ scp oracle_131215.txt oracle@192.168.20.21:/home/oracle // perform the same action on the standby machine Passwor D: oracle_131215.txt 100% | ************************************* **************************************** * *** | 27216 00: 00bash-3.00 $ ssh oracle@192.168.20.21Password: Last login: Sun Dec 15 10:31:13 2013 from 192.168.20.20ONLY Authorized users only! All accesses loggedfly-db2 % bashbash-3.00 $ hostnamefly-db2bash-3.00 $ for I in $ (cat oracle_131215.txt); do ls-l $ I; donecrw ------- 1 oracle dba 342,200 Sep 15 2012/dev/raw/raw1crw ------- 1 oracle dba 342,201 Sep 15 2012/dev/raw/raw2crw ------- 1 oracle dba 342,202 Sep 15 2012/dev/ raw/raw3crw ------- 1 oracle dba 342,254 Sep 15 2012/dev/raw/raw4crw ------- 1 oracle dba 342,253 Sep 15 2012/dev/raw/raw5bash-3.00 $ for I in $ (cat oracle_131215.txt); do ls-l $ I | grep-v ^ c; donebash-3.00 $ for I in $ (cat oracle_131215.txt); do ls-l $ I | grep root; done

4. Confirm the parameters to be modified:

Modify the following parameters in the/etc/system file on the Master/Slave machine as follows: shmsys: shminfo_shmmax is the maximum value of the shared memory segment, generally half of the physical memory, accurate to bytes) set shmsys: shminfo_shmmax = 100931731456 modify the following database parameters to the following values: memory_max_target = 110 GB // 60% sga_max_size of physical memory = 110 GB // 60% db_cache_size of physical memory = 55 GB // 30% shared_pool_size of physical memory = 10 Gb // 5% of physical memory pga_aggregate_target = 18 GB // 10% db_files in physical memory = 2000 // The maximum number of data files in the database is changed to 2000 processes = 3000 // The current value is already large, do not modify

The procedure is as follows:

1. the/etc/system file of the Master/Slave machine must be consistent. Otherwise, the parameter shmsys: shminfo_shmmax of the slave machine fails to take effect after the shmsys: shminfo_shmmax parameter is modified, the/etc/system file is read only when the operating system is booted. Theoretically, the shmsys: shminfo_shmmax parameter of the/etc/system file can be modified during database running.

2. Modify the shmsys: shminfo_shmmax parameter in the/etc/system file of the Master/Slave machine to half of the physical memory (188 GB), accurate to bytes.
Set shmsys: shminfo_shmmax = 100931731456

3. Compare whether the/etc/system files of the Master/Slave machine are consistent. If no output is available, the files are consistent.

bash-3.00#hostnamefly-db1bash-3.00# diff /etc/system /home/oracle/system_flydb2_131215.bak

4. Restart the backup operating system and set this parameter to take effect.

bash-3.00# hostnamefly-db1bash-3.00# syncbash-3.00# syncbash-3.00# syncbash-3.00# shutdown -i6 -g0 -y

5. Create a pfile using spfile and back up a pfile file to the/home/oracle/bak directory. Do not back up the file to the/tmp directory. After the operating system is restarted, files in the/tmp directory will be cleared. In this operating system, the/tmp file system type is swap, which can be seen through df-h), that is,/tmp actually refers to memory, if you store 3G files in the/tmp directory, you will find that the memory usage increases accordingly. After the operating system restarts, files in the/tmp directory will disappear.) At the same time, download a copy to the local directory. This pfile is not modified and is used for rollback.

SQL>create pfile='/home/oracle/pfile_131215.ora' from spfile;bash-3.00$ cp /home/oracle/pfile_131215.ora /home/oracle/bak/

6. log on to the database with sys and modify the following parameters:

bash-3.00$sqlplus /nologSQL> conn /as sysdbaSQL>alter system set memory_max_target=110G scope=spfile;SQL>alter system set sga_max_size=110G scope=spfile;SQL>alter system set db_files=2000 scope=spfile;

7. Use spfile to back up pfile again. Note that this pfile is the parameter file after the memory_max_target, sga_max_size, and db_files parameters are modified ):

SQL>create pfile='/home/oracle/pfile_1215_xiugai.ora' from spfile;bash-3.00$ cp /home/oracle/pfile_1215_xiugai.ora /home/oracle/bak

8. Delete sid. _ * in the top section of the pfile file about the current memory parameter value. In my pfile, delete the following values:

fly.__db_cache_size=31138512896fly.__java_pool_size=268435456fly.__large_pool_size=268435456fly.__pga_aggregate_target=5368709120fly.__sga_target=48318382080fly.__shared_io_pool_size=0fly.__shared_pool_size=15837691904fly.__streams_pool_size=536870912

9. In pfile, change the parameter to the following values:

*.db_cache_size=55G*.pga_aggregate_target=18G*.shared_pool_size=10G

10. In the dual-host software, click to enter the maintenance mode, disable the listener, and disable the database.

bash-3.00$hostnamefly-db1bash-3.00$lsnrctl stopbash-3.00$sqlplus /nologSQL>conn /as sysdbaSQL>alter system checkpoint;SQL>alter system switch logfile;SQL>alter system switch logfile;SQL>alter system switch logfile;SQL>alter system switch logfile;SQL>shutdown immediate;

11. Rebuilding spfile via pfile on the host fly-db1

SQL> create spfile from pfile = '/home/oracle/pfile_1215_xiugai.ora '; alternatively, you can directly specify the path of the bare device to recreate spfileSQL> create spfile = '/dev/raw/raw1' from pfile ='/home/oracle/pfile_1215_xiugai.ora ';

12, because of the dual-machine software, unable to maintain the mode, in the host has been closed database directly started to the slave fly-db2 slave physical memory has been expanded to 188 GB, the host's physical memory is still 94 GB). The dual-host software must start the database to switch the database to another host, but the spfile has been modified, if you start the database directly by startup, the database will inevitably report an out of memory error. It was originally thought that the dual-host machine could directly start the database to the standby machine. This was an unexpected problem during the operation, we can start the database through the pfile we originally backed up, and then switch the database to another host through the dual-host software to solve this problem.

bash-3.00$sqlplus /nologSQL>conn /as sysdbaSQL>startup pfile='/home/oracle/pfile_131215.ora';

13, in the host fly-db1 to start the database, through the dual-machine software, exit the maintenance mode, the database to the backup fly-db2

14. Check whether the alert Log has an error message. Use spfile to create a pfile or generate an awr report. Then, check whether the preceding settings take effect. Use the following SQL statement to view the current parameter settings:

SQL>select name, value, display_valuefrom v$parameter twhere t.name in ('memory_max_target','memory_target','sga_max_size','sga_target','shared_pool_size','db_cache_size','pga_aggregate_target','db_files');

 

This article from the "Yang youyoucuncaoxin" blog, please be sure to keep this source http://fly1116.blog.51cto.com/8301004/1343788