Oracle startup/shutdown

======================oracle start/shutdown==================

start

1.startup nomount: 读初始化参数，启动实例

1) 读spfile.ora          //服务器参数文件

2) 若不存在读init.ora     //手动配置参数文件
3) 分配sga
4) 启动后台进程
5) 打开alert.log 和跟踪文件
SQL> startup nomount
ORACLE instance started.


Total System Global Area 2388570112 bytes
Fixed Size                  2255472 bytes
Variable Size            1023411600 bytes
Database Buffers         1342177280 bytes
Redo Buffers               20725760 bytes


--------------alert.log--------------------
 Mon Jul 24 20:33:51 2017
Starting ORACLE instance (normal)
************************ Large Pages Information *******************
Per process system memlock (soft) limit = 32 KB
 
Total Shared Global Region in Large Pages = 0 KB (0%)
 
Large Pages used by this instance: 0 (0 KB)
Large Pages unused system wide = 0 (0 KB)
Large Pages configured system wide = 0 (0 KB)
Large Page size = 2048 KB
 
RECOMMENDATION:
  Total System Global Area size is 2290 MB. For optimal performance,
  prior to the next instance restart:
  1. Increase the number of unused large pages by 
 at least 1145 (page size 2048 KB, total size 2290 MB) system wide to
  get 100% of the System Global Area allocated with large pages
  2. Large pages are automatically locked into physical memory.
 Increase the per process memlock (soft) limit to at least 2298 MB to lock
 100% System Global Area's large pages into physical memory
********************************************************************
   LICENSE_MAX_SESSION = 0
LICENSE_SESSIONS_WARNING = 0
Initial number of CPU is 24
Number of processor cores in the system is 24
Number of processor sockets in the system is 4
Private Interface 'bond1:1' configured from GPnP for use as a private interconnect.
  [name='bond1:1', type=1, ip=169.254.199.206, mac=00-26-55-d5-b9-ab, net=169.254.0.0/16, mask=255.255.0.0, use=haip:cluster_interconnect/62]
Public Interface 'bond0' configured from GPnP for use as a public interface.
  [name='bond0', type=1, ip=10.31.9.242, mac=00-26-55-d5-b9-aa, net=10.31.9.0/24, mask=255.255.255.0, use=public/1]
Public Interface 'bond0:1' configured from GPnP for use as a public interface.
  [name='bond0:1', type=1, ip=10.31.9.250, mac=00-26-55-d5-b9-aa, net=10.31.9.0/24, mask=255.255.255.0, use=public/1]
Public Interface 'bond0:3' configured from GPnP for use as a public interface.
  [name='bond0:3', type=1, ip=10.31.9.248, mac=00-26-55-d5-b9-aa, net=10.31.9.0/24, mask=255.255.255.0, use=public/1]
CELL communication is configured to use 0 interface(s):
CELL IP affinity details:
    NUMA status: NUMA system w/ 4 process groups
    cellaffinity.ora status: cannot find affinity map at '/etc/oracle/cell/network-config/cellaffinity.ora' (see trace file for details)
CELL communication will use 1 IP group(s):
    Grp 0: 
Picked latch-free SCN scheme 3
Using LOG_ARCHIVE_DEST_1 parameter default value as USE_DB_RECOVERY_FILE_DEST
Autotune of undo retention is turned on. 
IMODE=BR
ILAT =168
LICENSE_MAX_USERS = 0
SYS auditing is enabled
NUMA system with 4 nodes detected
Starting up:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP, Data Mining
and Real Application Testing options.
ORACLE_HOME = /paic/app/dbshare/oracle/oratest2/rdbms/11g/11.2.0.4.2
System name:    Linux
Node name:      cnsh230235
Release:        2.6.18-398.0.0.0.1.el5
Version:        #1 SMP Tue Sep 16 13:24:08 PDT 2014
Machine:        x86_64
Using parameter settings in server-side spfile /../rdbms/11g/11.2.0.4.2/dbs/spfileoratest2.ora
System parameters with non-default values:
  processes                = 1000
  sessions                 = 1536
  shared_pool_size         = 768M
  large_pool_size          = 80M
  java_pool_size           = 128M
  control_files            = "/../oratest2/ORATEST2/controlfile/o1_mf_dpyng98q_.ctl"
  control_files            = "/../log/ORATEST2/controlfile/o1_mf_dpyngc4z_.ctl"
  db_block_size            = 8192
  db_cache_size            = 1280M
  compatible               = "11.2.0.4.0"
  db_create_file_dest      = "/../oratest2/oradata/oratest2"
  db_recovery_file_dest    = "/../oracle/oratest2/log"
  db_recovery_file_dest_size= 4977M
  undo_tablespace          = "UNDOTBS1"
  remote_login_passwordfile= "EXCLUSIVE"
  audit_sys_operations     = TRUE
  db_domain                = ""
  local_listener           = "(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=..)(PORT=..)))"
  audit_file_dest          = "/../rdbms/admin/oratest2/adump"
  audit_trail              = "DB"
  db_name                  = "oratest2"
  open_cursors             = 300
  pga_aggregate_target     = 512M
  diagnostic_dest          = "/../oracle/oratest2/rdbms"
Cluster communication is configured to use the following interface(s) for this instance
  169.254.199.206
cluster interconnect IPC version:Oracle UDP/IP (generic)
IPC Vendor 1 proto 2
Mon Jul 24 20:33:58 2017
PMON started with pid=2, OS id=5773 
Mon Jul 24 20:33:58 2017
PSP0 started with pid=3, OS id=5775 
Mon Jul 24 20:33:59 2017
VKTM started with pid=4, OS id=5797 at elevated priority
VKTM running at (1)millisec precision with DBRM quantum (100)ms
Mon Jul 24 20:33:59 2017
GEN0 started with pid=5, OS id=5801 
Mon Jul 24 20:33:59 2017
DIAG started with pid=6, OS id=5803 
Mon Jul 24 20:33:59 2017
DBRM started with pid=7, OS id=5805 
Mon Jul 24 20:33:59 2017
PING started with pid=8, OS id=5807 
Mon Jul 24 20:33:59 2017
ACMS started with pid=9, OS id=5821 
Mon Jul 24 20:33:59 2017
DIA0 started with pid=10, OS id=5823 
Mon Jul 24 20:33:59 2017
LMON started with pid=11, OS id=5825 
Mon Jul 24 20:33:59 2017
LMD0 started with pid=12, OS id=5827 
* Load Monitor used for high load check 
* New Low - High Load Threshold Range = [23040 - 30720] 
Mon Jul 24 20:33:59 2017
RMS0 started with pid=13, OS id=5829 
Mon Jul 24 20:33:59 2017
LMHB started with pid=14, OS id=5832 
Mon Jul 24 20:34:00 2017
MMAN started with pid=15, OS id=5834 
Mon Jul 24 20:34:00 2017
DBW0 started with pid=16, OS id=5836 
Mon Jul 24 20:34:00 2017
DBW1 started with pid=17, OS id=5838 
Mon Jul 24 20:34:00 2017
DBW2 started with pid=18, OS id=5840 
Mon Jul 24 20:34:00 2017
LGWR started with pid=19, OS id=5842 
Mon Jul 24 20:34:00 2017
CKPT started with pid=20, OS id=5844 
Mon Jul 24 20:34:00 2017
SMON started with pid=21, OS id=5846 
Mon Jul 24 20:34:00 2017
RECO started with pid=22, OS id=5849 
Mon Jul 24 20:34:00 2017
MMON started with pid=23, OS id=5851 
Mon Jul 24 20:34:00 2017
MMNL started with pid=24, OS id=5853 
lmon registered with NM - instance number 1 (internal mem no 0)
Reconfiguration started (old inc 0, new inc 2)
List of instances:
 1 (myinst: 1) 
 Global Resource Directory frozen
* allocate domain 0, invalid = TRUE 
 Communication channels reestablished
 Master broadcasted resource hash value bitmaps
 Non-local Process blocks cleaned out
 Set master node info 
 Submitted all remote-enqueue requests
 Dwn-cvts replayed, VALBLKs dubious
 All grantable enqueues granted
 Post SMON to start 1st pass IR
 Submitted all GCS remote-cache requests
 Post SMON to start 1st pass IR
Reconfiguration complete
ORACLE_BASE from environment = /../oratest2/rdbms

2. startup mount: 读初始化参数，打开控制文件

1) 读参数文件获取控制文件位置
2) 读控制文件，读取数据文件和联机重做日志的名称和状态
3) 数据库与以前启动的实例相关联


SQL> startup mount
ORACLE instance started.


Total System Global Area 2388570112 bytes
Fixed Size                  2255472 bytes
Variable Size            1023411600 bytes
Database Buffers         1342177280 bytes
Redo Buffers               20725760 bytes
Database mounted.

3. startup open：打开数据文件，重做日志文件，检查数据库一致性，如果有必要smon进行数据恢复

//默认选项
SQL>                startup open
ORACLE instance started.


Total System Global Area 2388570112 bytes
Fixed Size                  2255472 bytes
Variable Size            1023411600 bytes
Database Buffers         1342177280 bytes
Redo Buffers               20725760 bytes
Database mounted.
Database opened.


------alert.log
  Mon Jul 24 20:18:00 2017
Starting ORACLE instance (normal)
************************ Large Pages Information *******************
Per process system memlock (soft) limit = 32 KB
 
Total Shared Global Region in Large Pages = 0 KB (0%)
 
Large Pages used by this instance: 0 (0 KB)
Large Pages unused system wide = 0 (0 KB)
Large Pages configured system wide = 0 (0 KB)
Large Page size = 2048 KB
 
RECOMMENDATION:
  Total System Global Area size is 2290 MB. For optimal performance,
  prior to the next instance restart:
  1. Increase the number of unused large pages by 
 at least 1145 (page size 2048 KB, total size 2290 MB) system wide to
  get 100% of the System Global Area allocated with large pages
  2. Large pages are automatically locked into physical memory.
 Increase the per process memlock (soft) limit to at least 2298 MB to lock
 100% System Global Area's large pages into physical memory
********************************************************************
LICENSE_MAX_SESSION = 0
LICENSE_SESSIONS_WARNING = 0
Initial number of CPU is 24
Number of processor cores in the system is 24
Number of processor sockets in the system is 4
Private Interface 'bond1:1' configured from GPnP for use as a private interconnect.
  [name='bond1:1', type=1, ip=169.254.199.206, mac=00-26-55-d5-b9-ab, net=169.254.0.0/16, mask=255.255.0.0, use=haip:cluster_interconnect/62]




CELL communication is configured to use 0 interface(s):
CELL IP affinity details:
    NUMA status: NUMA system w/ 4 process groups
    cellaffinity.ora status: cannot find affinity map at '/etc/oracle/cell/network-config/cellaffinity.ora' (see trace file for details)
CELL communication will use 1 IP group(s):
    Grp 0: 
Picked latch-free SCN scheme 3
Using LOG_ARCHIVE_DEST_1 parameter default value as USE_DB_RECOVERY_FILE_DEST
Autotune of undo retention is turned on. 
IMODE=BR
ILAT =168
LICENSE_MAX_USERS = 0
SYS auditing is enabled
NUMA system with 4 nodes detected
Starting up:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP, Data Mining
and Real Application Testing options.
ORACLE_HOME = /
System name:    Linux
Node name:      cnsh230235
Release:        2.6.18-398.0.0.0.1.el5
Version:        #1 SMP Tue Sep 16 13:24:08 PDT 2014
Machine:        x86_64
Using parameter settings in server-side spfile /u1/rdbms/11g/11.2.0.4.2/dbs/spfileoratest2.ora
System parameters with non-default values:
  processes                = 1000
  sessions                 = 1536
  shared_pool_size         = 768M
  large_pool_size          = 80M
  java_pool_size           = 128M
  control_files            = "/u1/controlfile/o1_mf_dpyng98q_.ctl"
  control_files            = "/u1/controlfile/o1_mf_dpyngc4z_.ctl"
  db_block_size            = 8192
  db_cache_size            = 1280M
  compatible               = "11.2.0.4.0"
  db_create_file_dest      = "/"
  db_recovery_file_dest    = "/log"
  db_recovery_file_dest_size= 4977M
  undo_tablespace          = "UNDOTBS1"
  remote_login_passwordfile= "EXCLUSIVE"
  audit_sys_operations     = TRUE
  db_domain                = ""
  local_listener           = "(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=...)(PORT=...)))"
  audit_file_dest          = "/u1/rdbms/admin/oratest2/adump"
  audit_trail              = "DB"
  db_name                  = "oratest2"
  open_cursors             = 300
  pga_aggregate_target     = 512M
  diagnostic_dest          = "/paic/app/dbshare/oracle/oratest2/rdbms"
Cluster communication is configured to use the following interface(s) for this instance
  169.254.199.206
cluster interconnect IPC version:Oracle UDP/IP (generic)
IPC Vendor 1 proto 2
Mon Jul 24 20:18:08 2017
PMON started with pid=2, OS id=19832 
Mon Jul 24 20:18:08 2017
PSP0 started with pid=3, OS id=19834 
Mon Jul 24 20:18:09 2017
VKTM started with pid=4, OS id=19850 at elevated priority
VKTM running at (1)millisec precision with DBRM quantum (100)ms
Mon Jul 24 20:18:09 2017
GEN0 started with pid=5, OS id=19854 
Mon Jul 24 20:18:09 2017
DIAG started with pid=6, OS id=19870 
Mon Jul 24 20:18:09 2017
DBRM started with pid=7, OS id=19872 
Mon Jul 24 20:18:09 2017
PING started with pid=8, OS id=19874 
Mon Jul 24 20:18:09 2017
ACMS started with pid=9, OS id=19876 
Mon Jul 24 20:18:09 2017
DIA0 started with pid=10, OS id=19878 
Mon Jul 24 20:18:09 2017
LMON started with pid=11, OS id=19880 
Mon Jul 24 20:18:09 2017
LMD0 started with pid=12, OS id=19882 
* Load Monitor used for high load check 
* New Low - High Load Threshold Range = [23040 - 30720] 
Mon Jul 24 20:18:09 2017
RMS0 started with pid=13, OS id=19884 
Mon Jul 24 20:18:09 2017
LMHB started with pid=14, OS id=19886 
Mon Jul 24 20:18:09 2017
MMAN started with pid=15, OS id=19888 
Mon Jul 24 20:18:09 2017
DBW0 started with pid=16, OS id=19890 
Mon Jul 24 20:18:09 2017
DBW1 started with pid=17, OS id=19892 
Mon Jul 24 20:18:09 2017
DBW2 started with pid=18, OS id=19894 
Mon Jul 24 20:18:09 2017
LGWR started with pid=19, OS id=19896 
Mon Jul 24 20:18:09 2017
CKPT started with pid=20, OS id=19898 
Mon Jul 24 20:18:09 2017
SMON started with pid=21, OS id=19900 
Mon Jul 24 20:18:09 2017
RECO started with pid=22, OS id=19902 
Mon Jul 24 20:18:09 2017
MMON started with pid=23, OS id=19904 
Mon Jul 24 20:18:09 2017
MMNL started with pid=24, OS id=19907 
lmon registered with NM - instance number 1 (internal mem no 0)
Reconfiguration started (old inc 0, new inc 2)
List of instances:
 1 (myinst: 1) 
 Global Resource Directory frozen
* allocate domain 0, invalid = TRUE 
 Communication channels reestablished
 Master broadcasted resource hash value bitmaps
 Non-local Process blocks cleaned out
 Set master node info 
 Submitted all remote-enqueue requests
 Dwn-cvts replayed, VALBLKs dubious
 All grantable enqueues granted
 Post SMON to start 1st pass IR
 Submitted all GCS remote-cache requests
 Post SMON to start 1st pass IR
Reconfiguration complete
ORACLE_BASE from environment = /u1/rdbms
Mon Jul 24 20:18:10 2017
ALTER DATABASE   MOUNT
Successful mount of redo thread 1, with mount id 4191621506
Database mounted in Exclusive Mode
Lost write protection disabled
Completed: ALTER DATABASE   MOUNT
Mon Jul 24 20:18:15 2017
ALTER DATABASE OPEN
Thread 1 opened at log sequence 47
  Current log# 2 seq# 47 mem# 0: /u1/onlinelog/o1_mf_2_dpyngcvt_.log
  Current log# 2 seq# 47 mem# 1: /u1/onlinelog/o1_mf_2_dpyngd4o_.log
Successful open of redo thread 1
MTTR advisory is disabled because FAST_START_MTTR_TARGET is not set
SMON: enabling cache recovery
[20062] Successfully onlined Undo Tablespace 2.
Undo initialization finished serial:0 start:554300450 end:554300560 diff:110 (1 seconds)
Verifying file header compatibility for 11g tablespace encryption..
Verifying 11g file header compatibility for tablespace encryption completed
SMON: enabling tx recovery
Database Characterset is AL32UTF8
No Resource Manager plan active
replication_dependency_tracking turned off (no async multimaster replication found)
   Starting background process QMNC
Mon Jul 24 20:18:16 2017
QMNC started with pid=26, OS id=20072 
Completed: ALTER DATABASE OPEN
Mon Jul 24 20:18:17 2017
db_recovery_file_dest_size of 4977 MB is 3.38% used. This is a
user-specified limit on the amount of space that will be used by this
database for recovery-related files, and does not reflect the amount of
space available in the underlying filesystem or ASM diskgroup.

startup状态切换

1.从NoMount模式切换到Mount模式(没有从mount切换到nomount的)：

命令：ALTER DATABASE MOUNT;

2.当数据库状态为关闭时，切换到Open模式

命令：ALTER DATABASE OPEN;

shutdown

1.shutdown abort：不允许新连接，不等待当前会话结束，不等待事务结束，不强制选择检查点

需要进行实例恢复


SQL> shutdown abort
ORACLE instance shut down


================alert.log=======================
Mon Jul 24 20:38:10 2017
db_recovery_file_dest_size of 4977 MB is 3.38% used. This is a
user-specified limit on the amount of space that will be used by this
database for recovery-related files, and does not reflect the amount of
space available in the underlying filesystem or ASM diskgroup.
Mon Jul 24 20:38:24 2017
Shutting down instance (abort)
License high water mark = 2
USER (ospid: 10440): terminating the instance
Instance terminated by USER, pid = 10440
Mon Jul 24 20:38:25 2017
Instance shutdown complete

2.shutdown immediate：不允许新连接，不等待当前会话结束，不等待事务结束，强制选择检查点(一致的干净的数据库)

1) 不需要实例恢复
2) 回退未提交事务
3) SQL语句未完成
4) 关闭实例之前断开数据库
5) 将DB buffer cache和redo log buffer写入磁盘


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


-----alert.log
   Mon Jul 24 20:14:48 2017
Shutting down instance (immediate)
Stopping background process SMCO
Shutting down instance: further logons disabled
Stopping background process QMNC
Stopping background process MMNL
Stopping background process MMON
License high water mark = 5
ALTER DATABASE CLOSE NORMAL
Mon Jul 24 20:14:54 2017
SMON: disabling tx recovery
SMON: disabling cache recovery
Mon Jul 24 20:14:54 2017
Shutting down archive processes
Archiving is disabled
Archive process shutdown avoided: 0 active
Thread 1 closed at log sequence 47
Successful close of redo thread 1
Completed: ALTER DATABASE CLOSE NORMAL
ALTER DATABASE DISMOUNT
Shutting down archive processes
Archiving is disabled
Completed: ALTER DATABASE DISMOUNT
Mon Jul 24 20:14:59 2017
ARCH: Archival disabled due to shutdown: 1089
Shutting down archive processes
Archiving is disabled
ARCH: Archival disabled due to shutdown: 1089
Shutting down archive processes
Archiving is disabled
Mon Jul 24 20:15:00 2017
Stopping background process VKTM
Mon Jul 24 20:15:02 2017
freeing rdom 0
Mon Jul 24 20:15:03 2017
Instance shutdown complete

3. shutdown transactional：不允许新连接，不等待当前会话结束，等待事务结束，强制选择检查点(一致的干净的数据库)

1) 防止客户机丢失数据，等待事务结束
2) 不需要实例恢复
3) 将DB buffer cache和redo log buffer写入磁盘


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


-------alert.log----------------
                  Mon Jul 24 20:31:41 2017
Shutting down instance (transactional)
Stopping background process SMCO
Shutting down instance: further logons disabled
Stopping background process QMNC
Stopping background process MMNL
Stopping background process MMON
   All transactions complete. Performing immediate shutdown
License high water mark = 2
ALTER DATABASE CLOSE NORMAL
Mon Jul 24 20:31:48 2017
SMON: disabling tx recovery
SMON: disabling cache recovery
Mon Jul 24 20:31:48 2017
Shutting down archive processes
Archiving is disabled
Archive process shutdown avoided: 0 active
Thread 1 closed at log sequence 47
Successful close of redo thread 1
Completed: ALTER DATABASE CLOSE NORMAL
ALTER DATABASE DISMOUNT
Shutting down archive processes
Archiving is disabled
Completed: ALTER DATABASE DISMOUNT
Mon Jul 24 20:31:53 2017
ARCH: Archival disabled due to shutdown: 1089
Shutting down archive processes
Archiving is disabled
ARCH: Archival disabled due to shutdown: 1089
Shutting down archive processes
Mon Jul 24 20:31:54 2017
Stopping background process VKTM
Archiving is disabled
Mon Jul 24 20:31:55 2017
freeing rdom 0
Mon Jul 24 20:31:57 2017
Instance shutdown complete

4.shutdown normal：不允许新连接，等待当前会话结束，等待事务结束，强制选择检查点(一致的干净的数据库)  //默认选项

1) 等待所有用户断开连接
2) 将DB buffer cache和redo log buffer写入磁盘
3) 关闭实例之前断开数据库
4) 不需要实例恢复
5) 后台进程已经终止，删除了sga