oracle初始化参数和启动与关闭

oracle034

---

oracle初始化参数和启动与关闭

1、静态参数文件：是文本文件，命名为init+$ORACLE_SID.ora
2、动态参数文件：是二进制文件，命名为spfile+$ORACLE_SID.ora

本质区别：静态：在oracle启动的时候读取文件信息到内存中，以后就不再使用，假如参数文件被修改必须重启数据库

动态：在oracle启动的时候读取文件信息到内存中，而且文件还是被打开的，可以动态被修改，不过有些动态参数也需要重启数据库

3、Oracle启动过程中参数文件寻找顺序

首先查找动态参数文件，假如找不到就找静态参数文件，还找不到就会报参数文件未被找到信息

oracle找参数文件名字需要使用$ORACLE_SID中变量的值来构造出参数文件名init/spfile+$ORACLE_SID.ora

[oracle@localhost ~]$ echo $ORACLE_SID
jiagulun
[oracle@localhost ~]$
[oracle@localhost ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Mon Jan 5 04:46:50 2015

Copyright (c) 1982, 2009, Oracle. All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL>

[oracle@localhost ~]$ export ORACLE_SID=jiagulun1
[oracle@localhost ~]$ echo $ORACLE_SID//和实例名称一样
jiagulun1
[oracle@localhost ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Mon Jan 5 04:49:17 2015

Copyright (c) 1982, 2009, Oracle. All rights reserved.

Connected to an idle instance.//连接到一个未启动的实例上面

4、动态、静态参数文件的生成

SQL> create pfile from spfile;

File created.

SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@localhost dbs]$ ls
hc_DBUA0.datinitjiagulun.ora lkJIAGULUNspfilejiagulun.ora
hc_jiagulun.dat init.ora orapwjiagulun
[oracle@localhost dbs]$

5、参数的修改
ALTER SYSTEM SET parameter_name=parameter_value [comment=‘注释’]

scope=[memory|spfile|both] //假如参数中ISMODIFIED的值为FALSE就不能设置为both值

[sid=‘sid’|’*’]

SQL> alter system set sga_max_size=400000000 scope=both;
alter system set sga_max_size=400000000 scope=both
*
ERROR at line 1:
ORA-02095: specified initialization parameter cannot be modified

6、一个常见错误的解决流程

错误了的修改了spfile里面的参数，数据库启动失败，因为没有办法直接修改spfile，该怎么办?

SQL> show parameter sga

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
lock_sga boolean FALSE
pre_page_sga boolean FALSE
sga_max_size big integer 400M
sga_target big integer 0
SQL> alter system set sga_max_size=400000000 scope=both;
alter system set sga_max_size=400000000 scope=both
*
ERROR at line 1:
ORA-02095: specified initialization parameter cannot be modified


SQL> alter system set sga_max_size=4000000000 scope=spfile;

System altered.

SQL>
SQL> alter system set sga_target=4000000000 scope=spfile;

System altered.

SQL> alter system set pre_page_sga=true;
alter system set pre_page_sga=true
*
ERROR at line 1:
ORA-02095: specified initialization parameter cannot be modified


SQL> alter system setpre_page_sga=true scope=spfile;//事先必须分配出目标资源，默认是根据需要来分配上面sga的资源的

System altered.

SQL> startup
ORA-00844: Parameter not taking MEMORY_TARGET into account
ORA-00851: SGA_MAX_SIZE 4009754624 cannot be set to more than MEMORY_TARGET 419430400.
SQL>
报错了启动时候，因为需求太大了。
解决方法：
SQL> create pfile from spfile;//创建一个静态参数文件，因为静态参数文件是可以修改

File created.

SQL> !vi $ORACLE_HOME/dbs/initjiagulun.ora//修改参数文件信息

SQL> create spfile from pfile;//重新创建动态参数文件的信息

File created.

SQL> startup//
ORACLE instance started.

Total System Global Area 400846848 bytes
Fixed Size 2210888 bytes
Variable Size 201327544 bytes
Database Buffers 184549376 bytes
Redo Buffers 12759040 bytes
Database mounted.
Database opened.
SQL>

7、Oracle启动三个阶段
nomount:oracle找到参数文件，根据参数文件来划分内存空间和进程起起来，参数文件打开；通过查看下面信息可知

[oracle@localhost dbs]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Mon Jan 5 05:12:30 2015

Copyright (c) 1982, 2009, Oracle. All rights reserved.

Connected to an idle instance.

SQL> startup nomount
ORACLE instance started.

Total System Global Area 417546240 bytes
Fixed Size 2213936 bytes
Variable Size 310380496 bytes
Database Buffers 100663296 bytes
Redo Buffers 4288512 bytes

SQL> !ipcs -a

------ Shared Memory Segments --------
key shmid owner perms bytes nattch status
0x83092434 4784129 oracle 660 4096 0

------ Semaphore Arrays --------
key semid owner perms nsems
0x0c43eae8 753664 oracle 660 154

------ Message Queues --------
key msqid owner perms used-bytes messages


SQL> !ps -ef//
[oracle@localhost dbs]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Mon Jan 5 05:12:30 2015

Copyright (c) 1982, 2009, Oracle. All rights reserved.

Connected to an idle instance.

SQL> startup nomount
ORACLE instance started.

Total System Global Area 417546240 bytes
Fixed Size 2213936 bytes
Variable Size 310380496 bytes
Database Buffers 100663296 bytes
Redo Buffers 4288512 bytes
SQL> !ipsc -a
/bin/bash: ipsc: command not found

SQL> !ipcs -a

------ Shared Memory Segments --------
key shmid owner perms bytes nattch status
0x83092434 4784129 oracle 660 4096 0

------ Semaphore Arrays --------
key semid owner perms nsems
0x0c43eae8 753664 oracle 660 154

------ Message Queues --------
key msqid owner perms used-bytes messages


SQL> !ps -ef//process status
UID PID PPID C STIME TTY TIME CMD
root 1 0 0 Jan04 ? 00:00:03 init [5]
root 2 1 0 Jan04 ? 00:00:00 [migration/0]
root 3 1 0 Jan04 ? 00:00:00 [ksoftirqd/0]
.... . . . .... ................
gdm 4653 4607 0 Jan04 ? 00:00:01 /usr/libexec/gdmgreeter
root 7105 3707 0 Jan04 ? 00:00:02 sshd: root@pts/2
root 7107 7105 0 Jan04 pts/2 00:00:00 -bash
root 13998 7107 0 05:00 pts/2 00:00:00 su - oracle
oracle 13999 13998 0 05:00 pts/2 00:00:00 -bash
oracle 14276 13999 0 05:12 pts/2 00:00:00 sqlplus as sysdba
oracle 14325 1 0 05:12 ? 00:00:00 ora_pmon_jiagulun
oracle 14327 1 0 05:12 ? 00:00:00 ora_vktm_jiagulun
oracle 14331 1 0 05:12 ? 00:00:00 ora_gen0_jiagulun
oracle 14333 1 0 05:12 ? 00:00:00 ora_diag_jiagulun
oracle 14335 1 0 05:12 ? 00:00:00 ora_dbrm_jiagulun
oracle 14337 1 0 05:12 ? 00:00:00 ora_psp0_jiagulun
oracle 14339 1 0 05:12 ? 00:00:00 ora_dia0_jiagulun
oracle 14341 1 0 05:12 ? 00:00:00 ora_mman_jiagulun
oracle 14343 1 0 05:12 ? 00:00:00 ora_dbw0_jiagulun
oracle 14345 1 0 05:12 ? 00:00:00 ora_lgwr_jiagulun
oracle 14347 1 0 05:12 ? 00:00:00 ora_ckpt_jiagulun
oracle 14349 1 0 05:12 ? 00:00:00 ora_smon_jiagulun
oracle 14351 1 0 05:12 ? 00:00:00 ora_reco_jiagulun
oracle 14353 1 0 05:12 ? 00:00:00 ora_mmon_jiagulun
oracle 14355 1 0 05:12 ? 00:00:00 ora_mmnl_jiagulun
oracle 14357 1 0 05:12 ? 00:00:00 ora_d000_jiagulun
oracle 14359 1 0 05:12 ? 00:00:00 ora_s000_jiagulun
oracle 14362 14276 0 05:12 ? 00:00:00 oraclejiagulun (DESCRIPTION=(LOC
oracle 14397 14276 0 05:14 pts/2 00:00:00 ps -ef

SQL>
SQL> show parameter control

NAME TYPE
------------------------------------ ----------------------
VALUE
------------------------------
control_file_record_keep_time integer
7
control_files string
/u01/app/oracle/oradata/jiagul
un/control01.ctl, /u01/app/ora
cle/flash_recovery_area/jiagul
un/control02.ctl
control_management_pack_access string
DIAGNOSTIC+TUNING
SQL>
通过进程和内存的查看可以知道内存被分配了，进程也起起来了，参数文件也打开了
alter database mount
alter database open
通过下面的显示也可以观察到oracle的启动顺序

SQL> shutdown;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>

mount：根据参数文件里设置打开控制文件
open：把数据文件和redo log打开（因为这些文件被记录在控制文件中，所以先起控制文件
8、Oracle关闭四种方式
有四个选项可以选择
默认是normal
1、abort
模拟突然掉电
内存被清空、内存中的数据没有写入数据文件
事务被立即中断
没有提交、没有回滚
2、immediate
强制中断当前正在运行的所有事务，回滚这些事务
回滚完毕，强制中断所有的连接
讲实例中的所有数据写入数据文件
3、transactional
等待正在运行的事务，一直到他们提交或者回滚
所有事务主动结束以后（提交或者回滚），强行中断连接
将实例里面的数据写入数据文件
清空缓存
如果有事务一直没有提交或者回滚，实例无法关闭
4、normal
等待事务的主动提交或者回滚
等待用户主动断开连接
如果有一个用户没有断开连接，那么数据库无法关闭

9、参数类型