ORACLE_OCP之SPFILE和PFILE的重要性-故障模拟1
- 模拟场景:假设数据库正常运行,当某位同事错误修改参数后,会发生的事情。
一、故障模拟
-
参数查询
SQL> show parameter sga; NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ allow_group_access_to_sga boolean FALSE lock_sga boolean FALSE pre_page_sga boolean TRUE sga_max_size big integer 800M sga_min_size big integer 0 sga_target big integer 800M unified_audit_sga_queue_size integer 1048576
-
操作导致的故障点,810没有指定单位,导致数据库重启后无法正常启动,(没有单位的情况下,默认是字节)
SQL> alter system set sga_max_size=810 scope=spfile;
System altered. -
重启,发现启动失败,nomount也启动不了,是因为以上的修改操作引起的这个启动失败
SQL> shutdown immediate Database closed. Database dismounted. ORACLE instance shut down. SQL> startup ORA-00823: Specified value of sga_target greater than sga_max_size ORA-01078: failure in processing system parameters SQL> startup nomount ORA-00823: Specified value of sga_target greater than sga_max_size ORA-01078: failure in processing system parameters
二、解决
-
备份一份spfile,供启动数据库使用,具体方法如下:
[oracle@oracle12 ~]$ cd $ORACLE_HOME/dbs [oracle@oracle12 dbs]$ strings spfileorcl.ora > /home/oracle/orclpfile.ora
-
修改备份的orclpfile.ora,仅保留*.开头的行,发生串行的要合并好;
-
将 *.sga_max_size=810 该行改为 *.sga_max_size=810m,这一行就是修改的时候疏忽了,没有添加单位的行,此时我们将单位加上。
[oracle@oracle12 dbs]$ vim /home/oracle/orclpfile.ora [oracle@oracle12 dbs]$ cat /home/oracle/orclpfile.ora *.audit_file_dest='/u01/app/oracle/admin/orcl/adump' *.audit_trail='db' *.compatible='12.2.0' *.control_files='/u01/app/oracle/oradata/orcl/control01.ctl','/u01/app/oracle/oradata/orcl/control02.ctl' *.db_block_size=8192 *.db_name='orcl' *.diagnostic_dest='/u01/app/oracle' *.dispatchers='(PROTOCOL=TCP) (SERVICE=orclXDB)' *.local_listener='LISTENER_ORCL' *.log_archive_dest_1='location=/backup/arch' *.log_archive_format='orcl_arch_%t_%s_%r.arc' *.nls_language='AMERICAN' *.nls_territory='AMERICA' *.open_cursors=300 *.pga_aggregate_target=200m *.processes=300 *.remote_login_passwordfile='SHARED' *.sga_max_size=810m *.sga_target=800m *.undo_retention=900 *.undo_tablespace='UNDOTBS1'
-
我们将以修改好的orclpfile.ora用于数据库的启动
-
此时启动成功,并显示了sga_max_size大小是812M,有些出入是正常的。
SQL> startup nomount pfile='/home/oracle/orclpfile.ora' ORACLE instance started. Total System Global Area 851443712 bytes Fixed Size 8626384 bytes Variable Size 348131120 bytes Database Buffers 486539264 bytes Redo Buffers 8146944 bytes SQL> show parameter sga; NAME TYPE ------------------------------------ --------------------------------- VALUE ------------------------------ allow_group_access_to_sga boolean FALSE lock_sga boolean FALSE pre_page_sga boolean TRUE sga_max_size big integer 812M sga_min_size big integer NAME TYPE ------------------------------------ --------------------------------- VALUE ------------------------------ 0 sga_target big integer 800M unified_audit_sga_queue_size integer 1048576
-
此时我们需要重新创建spfile文件
SQL> create spfile from memory;
File created. -
重新启动,此时使用的是我们新创建的spfile,正常启动
-
这里我们使用shutdown abort是可以,因为我们数据库没有open状态。
SQL> shutdown abort ORACLE instance shut down. SQL> startup ORACLE instance started. Total System Global Area 851443712 bytes Fixed Size 8626384 bytes Variable Size 348131120 bytes Database Buffers 486539264 bytes Redo Buffers 8146944 bytes Database mounted. Database opened. SQL>