Physical memory expansion, oracle11gR1 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 server, modify the settings ..

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 server, modify the settings ..

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_targetNAMETYPEVALUE tables ----------- memory_targetbig integer 0SQL> show parameter sga_targetNAMETYPEVALUE tables ----------- sga_targetbig integer0

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. If the parameter files of the database have just been confirmed to use shared storage, all the database files (control files, online logs, data files (data files of the data table space and temporary tablespace) will be stored ), parameter files) should be bare devices. Now you need to confirm that the files in the Master/Slave database are bare devices and the permissions are: oracle: oinstall or oracle: dba, in order to avoid the switchover failure due to incorrect database file permissions or data files used in the file system during the dual-machine switchover (this problem has been encountered before, so you need to confirm it 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; spool offbash-3.00 $ sqlplus/nologSQL> conn/ S sysdbaConnected. SQL> @ check_file.sqlSQL> quitbash-3.00 $ lscheck_file.sqloracle_131215.txtbash-3.00 $ for I in $ (cat oracle_131215.txt); do ls-l $ I; done // view these files (with omitted) whether it is a bare device (that is, a character device), whether the permission is correct crw ------- 1 oracle dba342, 200 Dec 15/dev/raw/raw1crw ------- 1 oracle dba342, 201 Dec 15/dev/raw/raw2crw ------- 1 oracle dba342, 202 Dec 15/dev/raw/raw3crw ------- 1 oracle dba342, 254 Dec 15 0 /Dev/raw/raw4crw ------- 1 oracle dba342, 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 operation on the slave machine Password: oracle_131215.tx T 100% | ************************************* **************************************** * *** | 2721600: 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 dba342, 200 Sep 15 2012/dev/raw/raw1crw ------- 1 oracle dba342, 201 Sep 15 2012/dev/raw/raw2crw ------- 1 oracle dba342, 202 Sep 15 2012/dev/raw/raw3crw ------- 1 oracle dba342, 254 Sep 15 2012/dev/raw/raw4crw ------- 1 oracle dba342, 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 # shutdown-i6-g0-y