OCP-04：数据库管理

文章大纲

Oracle 目录
Oracle 软件安装完成后，一些重要的目录：

目录名	用途
`$ORACLE_BASE/admin`	保存数据库的管理信息和日志信息
`$ORACLE_BASE/oradata`	数据库数据文件、控制文件、索引文件等，真正存放数据的位置
`$ORACLE_HOME`	包含软件运行有关的子目录和网络文件以及选定的组件等
`$ORACLE_HOME/bin`	包含用于数据库管理的各种命令
`$ORACLE_HOME/dbs`	存放数据库服务器端的参数文件 spfile
`$ORACLE_HOME/css`	Oracle Cluster Synchronization 服务有关的文件
`$ORACLE_HOME/demo`	存放数据库实例模型的脚本
`$ORACLE_HOME/install`	存放 Oracle 安装后的端口号
`$ORACLE_HOME/network`	监听器 listener.ora 和 sqlnet.ora 以及 tnsnames.ora 等

Oracle 连接方式

有多种连接方式。

使用操作系统验证方式，只需登录到系统中的 oracle 用户

[oracle@oel7 ~]$ sqlplus / as sysdba

使用 easy connect 方式连接：

[oracle@oel7 ~]$ sqlplus sys/oracle@oel7:1521/orcl as sysdba

低版本的客户端会出现 ORA-28040: No matching authentication protocol ，在 oracle 用户下，在 $ORACLE_HOME/network/admin/sqlnet.ora 文件中添加两行：

SQLNET.ALLOWED_LOGON_VERSION_SERVER=8 
SQLNET.ALLOWED_LOGON_VERSION_CLIENT=8

ORA 28040 详细说明：

[oracle@oel7 ~]$ oerr ORA 28040
28040, 0000, "No matching authentication protocol"
// *Cause:  There was no acceptable authentication protocol for
//          either client or server.
// *Action: The administrator should set the values of the
//          SQLNET.ALLOWED_LOGON_VERSION_SERVER and
//          SQLNET.ALLOWED_LOGON_VERSION_CLIENT parameters, on both the
//          client and on the server, to values that match the minimum
//          version software supported in the system.
//          This error is also raised when the client is authenticating to
//          a user account which was created without a verifier suitable for
//          the client software version. In this situation, that account's
//          password must be reset, in order for the required verifier to
//          be generated and allow authentication to proceed successfully.

使用 TNS 方式连接，需要使用 netmgr 配置网络服务名

[oracle@oel7 ~]$ sqlplus sys/oracle@orcl as sysdba

需要配置 $ORACLE_HOME/network/admin/tnsnames.ora 文件：

ORCL =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = oel7)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orcl)
    )
  )

测试 TNS：

[oracle@oel7 ~]$ tnsping orcl

TNS Ping Utility for Linux: Version 19.0.0.0.0 - Production on 05-JAN-2025 14:12:29

Copyright (c) 1997, 2019, Oracle.  All rights reserved.

Used parameter files:


Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = oel7)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = orcl)))
OK (0 msec)

数据库删除

数据库删除也有多种方式。

使用 dbca 删除数据库：

第二种是通过 drop database 进行数据库删除，它有以下步骤：

启动数据库至 mount 或 open 状态
删除数据库
删除对应的备份和归档日志

具体操作：

[oracle@oel7 ~]$ . oraenv
ORACLE_SID = [orcl] ? mydb
The Oracle base remains unchanged with value /u01/app/oracle
[oracle@oel7 ~]$ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Sun Jan 5 14:18:28 2025
Version 19.3.0.0.0

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

Connected to an idle instance.


SYS@mydb()> startup restrict force mount;
ORACLE instance started.

Total System Global Area 1241510120 bytes
Fixed Size                  9134312 bytes
Variable Size             754974720 bytes
Database Buffers          469762048 bytes
Redo Buffers                7639040 bytes
Database mounted.

SYS@mydb(CDB$ROOT)> select status from v$instance;

STATUS
------------
MOUNTED

SYS@mydb(CDB$ROOT)> drop database;

Database dropped.

Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0

[oracle@oel7 ~]$ rm -rf $ORACLE_BASE/admin/mydb $ORACLE_BASE/oradata/MYDB $ORACLE_BASE/audit/mydb

数据库实例

一个数据库实例是一组管理数据库文件的内存结构，数据库是在磁盘上创建的一组物理文件。
实例管理其关联的数据库并为用户提供服务。
每个运行的 Oracle 数据库都与至少一个 Oracle 数据库实例关联。
因为内存中存在一个实例，数据库存在于磁盘上，所以一个实例可以在没有数据库的情况下存在，并且数据库可以在没有实例的情况下存在。

启动实例，分配 SGA(System Global Area)，启动一个或多个后台进程。
SGA 具有多种用途，例如：

维护许多进程和线程同时访问的内部数据结构
缓存从磁盘读取的数据块
在将重做数据写入联机重做日志文件之前对其进行缓冲
存储 SQL 执行计划

查看 SGA 信息：

SYS@orcl(CDB$ROOT)> show parameters 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 1184M
sga_min_size                         big integer 0
sga_target                           big integer 1184M
unified_audit_sga_queue_size         integer     1048576

在单台计算机上运行的 Oracle 进程共享 SGA。
数据库实例包括后台进程、服务器进程以及在这些进程中分配的进程内存存在于实例中。

Oracle 体系结构：

Oracle 实例结构：

单实例，数据库于数据库实例一对一的关系。
RAC 数据库和数据库实例一对多的关系。

一个数据库实例一次仅与一个数据库关联。
可以启动一个数据库实例并将数据库装入，但不能同时装入同一实例的两个数据库。
多个实例可以在同一台服务器上同时运行，每个实例都访问自己的数据库。

系统标识符(SID)是用于特定主机上的 Oracle 数据库实例的唯一名称。
在 Linxu 中，Oracle 数据库使用 SID 和 Oracle home 值创建共享内存密钥。
Oracle 数据库默认情况下使用 SID 来定位初始化参数文件，从而找到其它相关文件，入数据库控制文件。

参数文件位置：

[oracle@oel7 ~]$ ls $ORACLE_HOME/dbs
hc_orcl.dat  init.ora  lkORCL  orapworcl  snapcf_orcl.f  spfileorcl.ora

参数文件为 spfile+sid.ora 名称固定不能修改。

检查点

检查点通常是一致性数据库关闭、实例恢复和 Oracle 数据库操作中的一种关键机制。

指示检查点位置的数据结构，该位置是重做流中必须开始实例恢复的 scn。
检查点位置由数据库缓冲区中最旧的脏缓冲区决定。
检查点位置充当重做流的指针，并存储在控制文件和每个数据文件头中。
将数据库缓存中修改后的数据库缓冲区写入磁盘

Oracle 数据库使用检查点来实现多个目标：

减少实例或介质故障时恢复所需的时间
确保数据库定期将缓冲区高速缓存中的脏缓冲区写入磁盘
确保数据库在一致关闭期间将所有提交的数据写入磁盘

Oracle 数据库启动检查点时，检查点进程(CKPT)负责写检查点的数据文件头和控制文件。
检查点在各种情况下都会发生。

Oracle 数据库使用以下类型的检查点：

线程检查点

数据库将在特定目标之前在特定线程中由重做修改的所有缓冲区写入磁盘。
线程检查点在以下情况下发生：

一致的数据库关闭 ALTER SYSTEM CHECKPOINT
在线重做日志切换 ALTER SYSTEM SWITCH LOGFILE
数据库备份 ALTER DATABASE BEGIN BACKUP

表空间和数据文件检查点

数据库将在特定目标之前通过重做修改的所有缓冲区写入磁盘。
表空间检查点是一组数据文件检查点，表空间中的每个数据文件都有一个。
这些检查点在各种情况下都会发生，包括使表空间为只读或使表空间正常脱机，缩小数据文件或执行 ALTER TABLESPACE BEGIN BACKUP

增量检查点

增量检查时线程检查点的一种，部分目的时避免在联机重做日志开关中写入大量块。
DBW 至少每三秒检查一次，以确定是否有工作要做，当 DBW 写入脏页缓冲区时，它将前进检查点位置，从而使检查点进程将检查点位置写入控制文件，而不是写入数据文件头。

检查点使用场景

实例恢复时将联机重做日志中的记录应用于数据文件以重建最近检查点之后所做的更改的过程。
当管理员尝试打开以前不一致关闭的数据库时，实例恢复将自动发生。
实例恢复使用检查点来确定必须将哪些更改应用于数据文件。
检查点位置可确保将 SCN 低于检查点 SCN 的所有已提交更改都保存到数据文件中。

实例恢复的第一阶段称为缓存恢复或前滚，将联机重做日志中记录的所有更改重新应用于数据文件。
前滚会遍历所有必要的联机重做日志文件，以使数据库及时前移。

前滚之后，所有未提交的更改都必须撤销。Oracle 数据库应用撤销块来回滚在故障之前写入或在缓存恢复期间引入的数据块中未提交的更改，此阶段称为回滚或事务恢复。

实例启动过程

当数据库从关闭状态进入打开数据库状态时，经历了以下阶段：

实例启动时不挂载数据库：实例已启动，但尚未与数据库关联
数据库装载：通过读取其控制文件与数据库关联，该数据库对用户关闭
数据库打开：实例已启动，并与一个打开的数据库关联

数据文件可以在 mount 时获取：

SYS@orcl(CDB$ROOT)> create pfile='/home/oracle/orcl.spfile' from spfile;

File created.

[oracle@oel7 ~]$ cat orcl.spfile
orcl.__data_transfer_cache_size=0
orcl.__db_cache_size=838860800
orcl.__inmemory_ext_roarea=0
orcl.__inmemory_ext_rwarea=0
orcl.__java_pool_size=0
orcl.__large_pool_size=16777216
orcl.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment
orcl.__pga_aggregate_target=419430400
orcl.__sga_target=1241513984
orcl.__shared_io_pool_size=67108864
orcl.__shared_pool_size=301989888
orcl.__streams_pool_size=0
orcl.__unified_pga_pool_size=0
*.audit_file_dest='/u01/app/oracle/admin/orcl/adump'
*.audit_trail='db'
*.compatible='19.0.0'
*.control_files='/u01/app/oracle/oradata/ORCL/controlfile/o1_mf_mqhpxhxl_.ctl','/u01/app/oracle/fast_recovery_area/ORCL/controlfile/o1_mf_mqhpxhy8_.ctl'
*.db_block_size=8192
*.db_create_file_dest='/u01/app/oracle/oradata'
*.db_name='orcl'
*.db_recovery_file_dest='/u01/app/oracle/fast_recovery_area'
*.db_recovery_file_dest_size=20g
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=orclXDB)'
*.enable_pluggable_database=true
*.local_listener='LISTENER_ORCL'
*.log_archive_format='%t_%s_%r.dbf'
*.nls_language='AMERICAN'
*.nls_territory='AMERICA'
*.open_cursors=300
*.pga_aggregate_target=393m
*.processes=320
*.remote_login_passwordfile='EXCLUSIVE'
*.sga_target=1176m
*.undo_tablespace='UNDOTBS1'

三步启动：

SYS@orcl(CDB$ROOT)> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SYS@orcl(CDB$ROOT)> startup nomount
ORACLE instance started.

Total System Global Area 1241510120 bytes
Fixed Size                  9134312 bytes
Variable Size             318767104 bytes
Database Buffers          905969664 bytes
Redo Buffers                7639040 bytes
SYS@orcl(CDB$ROOT)> alter database mount;

Database altered.

SYS@orcl(CDB$ROOT)> alter database open;

Database altered.

SYS@orcl(CDB$ROOT)> select status from v$instance;

STATUS
------------
OPEN

使用 SHUTDOWN 命令关闭数据库实例有以下几个模式：

可以使用 startup force 重启数据库。

多租户环境

多租户环境中允许一个数据库容器(CDB)承载多个可插拔数据库(PDB)。
CDB：Container Database
PDB：Pluggable Database

查询是否为容器数据库：

SYS@orcl(CDB$ROOT)> select cdb from v$database;

CDB
---
YES

查看 PDB：

SYS@orcl(CDB$ROOT)> show pdbs;

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 PDB1                           MOUNTED
         4 PDB2                           MOUNTED

ROOT 组件

ROOT 又称为 CDB$ROOT 即根容器，存储着 Oracle 提供的元数据和常规用户。
元数据的一个例子是 Oracle 提供的 PL/SQL 的源代码，常规用户是指在每个容器中都存在的用户。

查看当前连接的容器数据库：

SYS@orcl(CDB$ROOT)> show con_name;

CON_NAME
------------------------------
CDB$ROOT

SEED 组件

SEED 又称为 PDB$SEED ，是创建 PDB 的模板，不能在 Seed 中添加或修改。
一个 CDB 中有且只有一个 Seed

PDBs

CDB 中可以有一个或多个 PDB，PDB 向后兼容，可以像以前在数据库中那样操作。
ROOT SEED PDB 中的每一个都称为容器，每个容器在 CDB 中都有一个唯一的 ID 和名称：

SYS@orcl(CDB$ROOT)> col PDB_NAME for a20
SYS@orcl(CDB$ROOT)> select PDB_ID,PDB_NAME,DBID,CON_UID from cdb_pdbs;

    PDB_ID PDB_NAME                   DBID    CON_UID
---------- -------------------- ---------- ----------
         3 PDB1                 2611250428 2611250428
         2 PDB$SEED              802682571  802682571
         4 PDB2                 2717827782 2717827782

对于 CDB，启动和关闭与之前传统数据库的方式一样：
启动：startup [nomount | mount | restrict | force | read only]
其它命令：

startup open  read only  --select open_mode from v$database; 
startup restrict
ALTER SYSTEM ENABLE RESTRICTED SESSION;  
ALTER SYSTEM DISABLE RESTRICTED SESSION;  
create user c##123 identified by 123; 
grant create session to c##123;

关闭：shutdown [immediate | abort]

默认情况下使用 sqlplus / as sysdba 登录连接的是 CDB。
在 CDB 启动之后，PDB 是自动启动到 mount 状态，而不是 OPEN 状态。

分步骤关闭 CDB：

SYS@orcl(CDB$ROOT)> alter database close;

Database altered.

SYS@orcl(CDB$ROOT)> alter database dismount;

Database altered.

SYS@orcl(CDB$ROOT)> shutdown
ORA-01507: database not mounted
ORACLE instance shut down.

也可以直接关闭：

shutdown immediate

启动和关闭 PDB：
启动或关闭一个或多个 PDB，指定的名称为一个以逗号分隔的列表：

SYS@orcl(CDB$ROOT)> show pdbs;

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 PDB1                           MOUNTED
         4 PDB2                           MOUNTED
SYS@orcl(CDB$ROOT)> alter pluggable database PDB1 open;

Pluggable database altered.

SYS@orcl(CDB$ROOT)> show pdbs;

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 PDB1                           READ WRITE NO
         4 PDB2                           MOUNTED

关闭 PDB：

SYS@orcl(CDB$ROOT)> alter pluggable database PDB1 close;

Pluggable database altered.

SYS@orcl(CDB$ROOT)> show pdbs;

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 PDB1                           MOUNTED
         4 PDB2                           MOUNTED

其它的一些启动和关闭操作：

alter pluggable database prod1 close; 
alter pluggable database pdb1,pdb2 open read only; 
alter pluggable database pdb3 open RESTRICTED; 
alter pluggable database pdb1,pdb2 close immediate; 
select name,open_mode from v$pdbs;

启动或关闭所有 PDB：

alter pluggable database all open; 
alter pluggable database all close immediate;

排除某些 PDB 不启动：

alter pluggable database all except pdb1 open; 
alter pluggable database all except pdb1 close immediate;

从根容器切换到 PDB：

SYS@orcl(CDB$ROOT)> alter session set container=PDB1;

Session altered.

SYS@orcl(CDB$ROOT)> show con_name;

CON_NAME
------------------------------
PDB1

从 PDB 切换到根容器：

SYS@orcl(CDB$ROOT)> conn / as sysdba
Connected.
SYS@orcl(CDB$ROOT)> show con_name

CON_NAME
------------------------------
CDB$ROOT

PDB 自动启动：
在 PDB 启动后，可以保存 OPEN 状态，下次可自动启动 PDB：

SYS@orcl(CDB$ROOT)> alter pluggable database all open;

Pluggable database altered.

SYS@orcl(CDB$ROOT)> alter pluggable database all save state;

Pluggable database altered.

SYS@orcl(CDB$ROOT)> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SYS@orcl(CDB$ROOT)> startup
ORACLE instance started.

Total System Global Area 1241510120 bytes
Fixed Size                  9134312 bytes
Variable Size             335544320 bytes
Database Buffers          889192448 bytes
Redo Buffers                7639040 bytes
Database mounted.
Database opened.
SYS@orcl(CDB$ROOT)> show pdbs;

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 PDB1                           READ WRITE NO
         4 PDB2                           READ WRITE NO

取消自动启动 PDB：

SYS@orcl(CDB$ROOT)> alter pluggable database all discard state;