Be Careful of these Commands when you recover database-优快云博客

本文链接：https://blog.youkuaiyun.com/linsuhangoracle/article/details/119445224

本文详细介绍了ALTER SYSTEM FLUSH、ALTER SYSTEM CHECKPOINT和ALTER SYSTEM SWITCH LOGFILE三个数据库管理命令的作用和影响。FLUSH命令用于清空缓存，不涉及脏块写入；CHECKPOINT命令强制执行检查点，确保事务更改持久化；SWITCH LOGFILE会触发检查点但立即返回，适用于测试环境。注意在数据文件丢失时执行这些命令可能导致宕机。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

（一）ALTER SYSTEM FLUSH

FLUSH命令会清空相应缓存，它应该只是清空clean block，会对缓存命中率产生影响，但对于脏块并不会触发写入磁盘，仍保留在buffer cache，所以在数据文件丢失执行不会导致宕机，但执行此命令也没有意义

FLUSH SHARED_POOL

The FLUSH SHARED_POOL clause lets you clear data from the shared pool in the system global area (SGA).

This statement does not clear global application context information, nor does it clear shared SQL and PL/SQL areas for items that are currently being executed. You can use this clause regardless of whether your instance has the database dismounted or mounted, open or closed.

FLUSH BUFFER_CACHE

The FLUSH BUFFER_CACHE clause lets you clear all data from the buffer cache in the system global area (SGA), including the KEEP, RECYCLE, and DEFAULT buffer pools.

Note: This clause is intended for use only on a test database. Do not use this clause on a production database, because as a result of this statement, subsequent queries will have no hits, only misses.

This clause is useful if you need to measure the performance of rewritten queries or a suite of queries from identical starting points.

（二）ALTER SYSTEM CHECKPOINT [GLOBAL|LOCAL]

GLOBAL(default)|LOCAL用于RAC，前者表示所有节点执行checkpoint后者只当前节点

进行全量检查点，触发CKPT把checkpoint SCN写入控制文件与数据文件头

Specify CHECKPOINT to explicitly force Oracle Database to perform a checkpoint, ensuring that all changes made by committed transactions are written to data files on disk. You can specify this clause only when your instance has the database open. Oracle Database does not return control to you until the checkpoint is complete.

在数据文件丢失时不要执行，会触发全量检查点导致宕机

（三）ALTER SYSTEM SWITCH LOGFILE

Switch logfile会触发全量检查点，触发CKPT把checkpoint SCN写入控制文件与数据文件头，它与ALTER SYSTEM CHECKPOINT略有不同

When you force a log switch, Oracle Database begins to perform a checkpoint but returns control to you immediately rather than when the checkpoint is complete. To use this clause, your instance must have the database open.

在数据文件丢失时不要执行，会触发全量检查点导致宕机