oracle故障案例分享,{甲骨文OCP}Oracle故障案例分析

#(1)在主库上操作，先备份一个pfile

SQL> create pfile='/oracle/pfile_4.ora' from spfile;

File created.

#(2)开始在线调整sga、pga

SQL> alter system set sga_max_size=8192M scope=spfile;

System altered.

SQL> ALTER system SET sga_target=3072M scope=spfile;

System altered.

SQL>

#(3)将调整过的保存到启动参数里面

SQL> create pfile from spfile;

File created.

SQL>

#(4)查看主库的sga、pga配置，调整已经生效了

SQL> show parameter sga;

NAME                                        TYPE       VALUE

------------------------------------ ----------- ------------------------------

lock_sga                          boolean FALSE

pre_page_sga                         boolean FALSE

sga_max_size                         big integer 8G

sga_target                               big integer 3G

SQL> show parameter pga;

NAME                                        TYPE       VALUE

------------------------------------ ----------- ------------------------------

pga_aggregate_target                  big integer 4940M

SQL>

SQL>

#(5)这里其实也不用shutdown、startup已经生效了，不过为了验证下，所以我这里做了这个操作

SQL> shutdown immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup

ORACLE instance started.

Total System Global Area 8551575552 bytes

Fixed Size              2270360 bytes

Variable Size              8120175464 bytes

Database Buffers      419430400 bytes

Redo Buffers                 9699328 bytes

Database mounted.

Database opened.

SQL>

SQL>

SQL>

#(6)再次查看主库的sga、pga配置，重启后，仍然生效

SQL> show parameter sga;

NAME                                        TYPE       VALUE

------------------------------------ ----------- ------------------------------

lock_sga                          boolean FALSE

pre_page_sga                         boolean FALSE

sga_max_size                         big integer 8G

sga_target                               big integer 3G

SQL> show parameter pga;

NAME                                        TYPE       VALUE

------------------------------------ ----------- ------------------------------

pga_aggregate_target                  big integer 4940M

SQL>