【数据库开启归档日志】Oracle、MySQL、SQL Server、PostgreSQL

离谱哇@

已于 2025-02-15 10:32:41 修改

阅读量1.5k

点赞数 31

文章标签： oracle 数据库 mysql postgresql sqlserver

于 2025-02-12 11:22:43 首次发布

本文链接：https://blog.csdn.net/weixin_55761321/article/details/145588395

版权

一、Oracle如何开启数据库的归档模式步骤

1. 登录到 Oracle 数据库

首先，您需要以 sysdba 权限登录到数据库。使用 sqlplus 命令行工具或其他数据库连接工具登录。

sqlplus / as sysdba

2. 检查当前归档模式状态

在启用归档模式之前，可以检查当前数据库的归档模式是否已开启。

ARCHIVE LOG LIST;

如果数据库处于归档模式，输出将包含类似如下信息：

Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            /oracle/archive

如果是非归档模式（No Archive Mode），则需要开启归档模式。

3. 将数据库切换到挂起模式（Shutdown the database）

为了启用归档模式，首先需要将数据库置于挂起状态。使用以下命令关闭数据库：

SHUTDOWN IMMEDIATE;

4. 启动数据库并启用归档模式

在数据库关闭后，启动数据库并启用归档模式。

STARTUP MOUNT;

此时，数据库已处于挂载模式，但尚未打开。接下来，通过以下命令启用归档模式：

ALTER DATABASE ARCHIVELOG;

5. 启动数据库

归档模式启用后，您可以打开数据库：

ALTER DATABASE OPEN;

6. 验证归档模式是否启用

再次使用以下命令确认归档模式已启用：

ARCHIVE LOG LIST;

输出应该显示 Archive Mode 为 “Enabled”。

7. 设置归档日志存储位置（可选）

如果您希望为归档日志指定特定的存储位置，可以通过以下命令设置：

ALTER SYSTEM SET LOG_ARCHIVE_DEST='/path/to/archive';

请将 /path/to/archive 替换为您希望存储归档日志的实际路径。

完整步骤示例

-- 1. 登录到数据库
sqlplus / as sysdba

-- 2. 检查当前归档模式状态
ARCHIVE LOG LIST;

-- 3. 关闭数据库
SHUTDOWN IMMEDIATE;

-- 4. 启动数据库并启用归档模式
STARTUP MOUNT;
ALTER DATABASE ARCHIVELOG;

-- 5. 启动数据库
ALTER DATABASE OPEN;

-- 6. 验证归档模式是否启用
ARCHIVE LOG LIST;

注意事项

存储空间：开启归档模式后，数据库会生成归档日志，这会占用存储空间。确保有足够的磁盘空间来存储归档日志。
自动归档：Oracle 允许将归档日志自动复制到指定位置，可以使用 LOG_ARCHIVE_DEST 来配置归档日志的目标目录。可以将归档日志配置到多个目标。
备份：开启归档模式后，进行归档日志的备份变得更重要。定期备份归档日志和数据文件是保证数据安全性和可恢复性的必要措施。

8.开启表所有列增补日志

ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;
# 开启指定表所有列的增补⽇志
ALTER TABLE <schema>.<table1> ADD SUPPLEMENTAL LOG DATA (ALL) COLUMNS;
...
ALTER TABLE <schema>.<tableN> ADD SUPPLEMENTAL LOG DATA (ALL) COLUMNS;

9.创建访问用户并授权

-- 创建角色并授权权限
CREATE ROLE ROLE_CDC;

#内部使用可以选择使用该命令授予角色所有权限
GRANT ALL privileges TO ROLE_CDC;

#使用下面命令对角色使用日志权限授权
GRANT CREATE SESSION TO ROLE_CDC;
GRANT LOGMINING TO ROLE_CDC;
GRANT FLASHBACK ANY TABLE TO ROLE_CDC;
GRANT EXECUTE_CATALOG_ROLE, SELECT_CATALOG_ROLE, LOCK ANY TABLE, SELECT ANY TRANSACTION, SELECT ANY DICTIONARY, CREATE TABLE, CREATE SEQUENCE TO ROLE_CDC;
GRANT EXECUTE ON SYS.DBMS_LOGMNR TO ROLE_CDC;
GRANT EXECUTE ON SYS.DBMS_LOGMNR_D TO ROLE_CDC;
GRANT EXECUTE ON SYS.DBMS_LOGMNR_LOGREP_DICT TO ROLE_CDC;

GRANT EXECUTE ON SYS.DBMS_LOGMNR_SESSION TO ROLE_CDC;

GRANT SELECT ON V_$LOGMNR_CONTENTS TO ROLE_CDC;
GRANT SELECT ON V_$DATABASE TO ROLE_CDC;
GRANT SELECT ON V_$THREAD TO ROLE_CDC;
GRANT SELECT ON V_$PARAMETER TO ROLE_CDC;
GRANT SELECT ON V_$NLS_PARAMETERS TO ROLE_CDC;
GRANT SELECT ON V_$TIMEZONE_NAMES TO ROLE_CDC;
GRANT SELECT ON V_$INSTANCE TO ROLE_CDC;
GRANT SELECT ON ALL_INDEXES TO ROLE_CDC;
GRANT SELECT ON ALL_OBJECTS TO ROLE_CDC;
GRANT SELECT ON ALL_USERS TO ROLE_CDC;
GRANT SELECT ON ALL_CATALOG TO ROLE_CDC;
GRANT SELECT ON ALL_CONSTRAINTS TO ROLE_CDC;
GRANT SELECT ON ALL_CONS_COLUMNS TO ROLE_CDC;
GRANT SELECT ON ALL_TAB_COLS TO ROLE_CDC;
GRANT SELECT ON ALL_IND_COLUMNS TO ROLE_CDC;
GRANT SELECT ON ALL_ENCRYPTED_COLUMNS TO ROLE_CDC;
GRANT SELECT ON ALL_LOG_GROUPS TO ROLE_CDC;
GRANT SELECT ON ALL_TAB_PARTITIONS TO ROLE_CDC;
GRANT SELECT ON SYS.DBA_REGISTRY TO ROLE_CDC;
GRANT SELECT ON SYS.OBJ$ TO ROLE_CDC;
GRANT SELECT ON DBA_TABLESPACES TO ROLE_CDC;
GRANT SELECT ON DBA_OBJECTS TO ROLE_CDC;
GRANT SELECT ON SYS.ENC$ TO ROLE_CDC;

-- 授予所需读取表的权限（多个表请分别执⾏）
GRANT SELECT ON <schema>.<table1> TO ROLE_CDC;
...
GRANT SELECT ON <schema>.<tableN> TO ROLE_CDC;

-- 创建访问⽤户，TABLESPACE 后的 <表空间> 请根据实际替换
CREATE USER USR_CDC IDENTIFIED BY <密码> DEFAULT TABLESPACE <表空间>;
ALTER USER USR_CDC QUOTA UNLIMITED ON <表空间>;
GRANT ROLE_CDC TO USR_CDC;

10.日志保存清理策略

您可以使用 RECOVERY WINDOW 或 RETENTION POLICY 来定义日志保留时间。例如，您希望清理 7 天前的归档日志，可以设置 RMAN 保留策略为 7 DAYS。

-- 保留 1 个备份
CONFIGURE RETENTION POLICY TO REDUNDANCY 1; 
-- 保留 7 天内的日志
CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 7 DAYS;

脚本清理

#!/bin/bash #!/bin/bash
echo "----------------------------------------`date`------------------------------
---------"
if [ -f ~/.bash_profile ]; then 
. ~/.bash_profile 
fi
rman target / <<EOF
crosscheck archivelog all;
delete noprompt expired archivelog all;
delete noprompt archivelog all completed before 'sysdate-7';
EOF
echo -e "\n"
echo "------------------------------------ FINISHED ------------------------------
------"

加⼊crontab每⽇凌晨执⾏：

#... 代表脚本位置路径 例：/home/oracle/clean_archivelog.sh 
#0 1 * * * 是corn表达式Linux的版本的不清楚规则的前往 https://tool.lu/crontab/ 选择Linux 来生成表达式
0 1 * * * /.../.../clean_archivelog.sh

二、MySQL/MariaDB如何开启数据库的日志模式步骤

注：如果实际环境是MySQL的主从（Master-Slave）架构部署，由于所有的修改操作都发⽣在Master节点，接⼝程序必须连接到Master节点才能正常⼯作。

1.修改配置文件（`my.cnf` 或 `my.ini`）

编辑 MySQL 配置文件：
- Linux 下的默认配置文件路径通常为 /etc/my.cnf 或 /etc/mysql/my.cnf，或者 /etc/mysql/mysql.conf.d/mysqld.cnf。
- Windows 下的默认配置文件通常为 C:\Program Files\MySQL\MySQL Server X.X\my.ini。
修改配置文件：打开配置文件并编辑 [mysqld] 部分，加入或修改以下几项：
```
[mysqld]
server-id = 1             # 设置一个唯一的服务器 ID，通常是一个正整数
log-bin = /var/log/mysql/mysql-bin  # 设置二进制日志文件的路径及文件名（可以是绝对路径）
binlog_format = ROW        # 设置二进制日志的格式（可选：ROW, STATEMENT, MIXED）
expire_logs_days = 7       # 设置二进制日志的过期时间，单位为天，7天后会自动删除
max_binlog_size = 100M     # 设置单个 binlog 文件的最大大小
# enable gtid mode  SEATUNNEL 实时采集配置使用
# mysql 5.6+ requires gtid_mode to be set to ON
gtid_mode = on
enforce_gtid_consistency = on
```
- server-id：每个 MySQL 或 MariaDB 实例都必须有一个唯一的 server-id，在一个复制环境中，主服务器和从服务器的 server-id 必须不同。
- log-bin：启用二进制日志并指定日志文件的路径和文件名。路径必须是有效且 MySQL 进程有写权限的路径。
- binlog_format：设置二进制日志的格式。常见的选项有：
  - STATEMENT：记录 SQL 语句（默认方式），但是有些语句可能会出现复制不一致的情况。
  - ROW：记录具体的行数据变动，适用于所有情况，但会消耗更多的磁盘空间。
  - MIXED：结合了前两者的优点，MySQL 会根据需要选择使用 STATEMENT 或 ROW。
- expire_logs_days：设置二进制日志的过期天数，超过天数的日志会自动删除。
- max_binlog_size：设置单个二进制日志文件的最大大小，当日志文件大小达到该值时，会自动切换到新的日志文件。
示例配置：
```
[mysqld]
server-id = 1
log-bin = /var/log/mysql/mysql-bin
binlog_format = ROW
expire_logs_days = 7
max_binlog_size = 100M
# enable gtid mode  SEATUNNEL 实时采集配置使用
# mysql 5.6+ requires gtid_mode to be set to ON
gtid_mode = on
enforce_gtid_consistency = on
```

2.重启 MySQL/MariaDB 服务

配置文件修改完成后，需要重启 MySQL/MariaDB 服务以使更改生效：

在 Linux 系统中，使用以下命令重启 MySQL 服务：

sudo systemctl restart mysql     # 对于 MySQL 服务
sudo systemctl restart mariadb   # 对于 MariaDB 服务

在 Windows 系统中，可以通过服务管理工具重新启动 MySQL 或 MariaDB 服务，或者使用命令行：
```
net stop mysql
net start mysql
```

3.验证二进制日志是否启用

登录到 MySQL/MariaDB 客户端：
```
mysql -u root -p
```
执行以下命令检查二进制日志是否已启用：
```
SHOW VARIABLES LIKE 'log_bin';
```
如果返回结果显示 log_bin 的值为 ON，则表示二进制日志已启用。

可以执行以下命令查看当前的二进制日志文件：

SHOW BINARY LOGS;

如果启用了二进制日志，系统会列出当前存在的二进制日志文件。

4.创建访问用户并授权

CREATE USER `usr_cdc`@`%` IDENTIFIED BY '<密码>';
GRANT Reload, Replication Client, Replication Slave, Select, Show Databases ON *.* TO `usr_cdc`@`%`;

其他注意事项：

备份和清理：二进制日志文件会占用磁盘空间，因此需要定期进行清理。可以通过 expire_logs_days 参数来自动清理过期日志，也可以手动删除不需要的日志。

-- 临时修改 重启服务后会失效
SET GLOBAL expire_logs_days = <天数>;

-- 例保留7天
SET GLOBAL expire_logs_days = 7;

#在 MySQL 的配置文件 my.cnf（或 my.ini）中添加或修改该参数：

[mysqld]
expire_logs_days = 7

复制环境：如果在主从复制环境中使用二进制日志，确保所有从服务器能够访问到主服务器的二进制日志，并且配置好相应的 relay-log。

三、PostgreSQL如何开启数据库的归档模式步骤（10及以上）

在 PostgreSQL 10 及以上版本中，要启用归档日志功能（也叫 WAL 归档），需要配置一些参数来确保日志文件能够被正确地存档。以下是开启归档日志的步骤：

1. 修改 PostgreSQL 配置文件 `postgresql.conf`

你需要修改 postgresql.conf 配置文件中的几个重要参数：

启用归档

archive_mode：这个参数控制是否启用归档功能，设置为 on 启用归档。
archive_command：这个参数指定了归档日志文件时需要执行的命令。

打开 PostgreSQL 的 postgresql.conf 文件，通常文件路径为 /etc/postgresql/{version}/main/postgresql.conf 或 /var/lib/pgsql/{version}/data/postgresql.conf，根据你的系统不同路径可能有所不同。

找到并修改以下参数：

archive_mode = on             # 启用归档
archive_command = 'cp %p /path/to/archive/%f'  # 设置归档命令，%p 表示 WAL 文件路径，%f 是 WAL 文件名

archive_command中的 cp %p /path/to/archive/%f 表示将归档的 WAL 文件通过 cp命令复制到指定的路径。你可以根据实际需求更改这个命令。
- %p 是当前 WAL 文件的路径。
- %f 是当前 WAL 文件的文件名。

你可以将归档文件存放到任何目录，但确保 PostgreSQL 有足够的权限去写入归档目录。

注意：

如果使用 rsync、scp 或者其他方式代替 cp 也可以。
archive_command 必须能够成功执行，返回值为 0 表示成功，其他值表示失败。

2. 配置归档日志的存放目录

确保归档目录存在且 PostgreSQL 进程有写入权限。例如，创建一个目录 /path/to/archive/ 并授予 PostgreSQL 所属用户权限：

mkdir -p /path/to/archive/
chown postgres:postgres /path/to/archive/

3. 重启 PostgreSQL 服务

在修改完 postgresql.conf 后，需要重启 PostgreSQL 服务以使配置生效：

sudo systemctl restart postgresql

4. 确认归档功能是否启用

你可以使用以下 SQL 命令来确认归档日志功能是否已经启用：

SHOW archive_mode;
SHOW archive_command;

archive_mode 应该显示为 on。
archive_command 应该显示为你设置的归档命令。

5. 检查归档日志的生成情况

归档日志会被存储在你在 archive_command 中指定的目录下。可以检查该目录，确保日志文件正确生成。

ls /path/to/archive/

如果归档日志成功启用，你应该能看到一个个 .000000010000 形式的文件，这些文件是 PostgreSQL 的 WAL 文件。

6. （可选）配置自动清理归档日志

为了防止归档目录占满硬盘，你可以设置定期清理归档日志的策略，常见方法是使用 cron 作业自动删除过期的归档文件。例如：

0 3 * * * find /path/to/archive/ -type f -mtime +7 -exec rm {} \;

这个 cron 作业每天凌晨 3 点删除 7 天前的归档日志文件。

7.创建访问用户并授权

CREATE USER usr_cdc LOGIN REPLICATION PASSWORD '<密码>';
GRANT USAGE ON SCHEMA <schema> TO usr_cdc;
GRANT CREATE ON DATABASE <库名> TO usr_cdc;
GRANT SELECT ON ALL TABLES IN SCHEMA <schema> TO usr_cdc;
ALTER DEFAULT PRIVILEGES IN SCHEMA <schema> GRANT SELECT ON TABLES TO usr_cdc;

四、SQL Server 如何开启数据库的归档模式步骤（2017及以上）

在 SQL Server 2017 中，归档日志的开启通常与启用 SQL Server 事务日志备份 和 数据库恢复模式 有关。虽然 SQL Server 并没有专门的 “归档日志” 选项，但是可以通过设置数据库的恢复模式为 完全恢复模式 (Full Recovery Mode)，然后定期进行事务日志备份，达到类似归档日志的目的。

1. 设置数据库为完全恢复模式

连接到 SQL Server 实例：

使用 SQL Server Management Studio (SSMS) 或 SQLCMD 连接到 SQL Server 实例。

更改数据库的恢复模式：

在 SSMS 中，右键点击目标数据库，选择 Properties。
转到 Options 页签，找到 Recovery model，选择 Full。

也可以使用 T-SQL 来更改恢复模式：

ALTER DATABASE [YourDatabaseName]
SET RECOVERY FULL;

完全恢复模式 (Full Recovery Mode) 会记录每一个事务的日志，因此你可以进行完整的日志备份，达到归档日志的效果。

2.开启数据库级CDC特性:

USE <库名>;
EXEC sys.sp_cdc_enable_db;

3.开启表级CDC特性:

USE <库名>;

-- 多个表请分别执⾏
EXEC sys.sp_cdc_enable_table
@source_schema = N'<schema>',
@source_name = N'<table>',
@role_name = N'usr_cdc', 
@supports_net_changes = 0;

4.创建访问⽤户并授权

USE <库名>;

CREATE LOGIN usr_cdc WITH PASSWORD = '<密码>';

CREATE USER usr_cdc FOR LOGIN usr_cdc WITH DEFAULT_SCHEMA = <schema>;

EXEC sp_addrolemember N'db_datareader', N'usr_cdc';

5.开启代理服务

Windows开启步骤

使用Sql Server Configuration Manager

打开Sql Server Configuration Manager，选择SQL Server 服务打开；在界面右侧选择SQL Server 代理选择启动。
使用Windows 服务管理器

按 Win + R 打开运行框，输入 services.msc，然后按 Enter。

在服务窗口中，找到SQL Server Agent服务。

如果你的实例是默认实例，服务名称为 SQL Server Agent (MSSQLSERVER)。

如果是命名实例，服务名称为 SQL Server Agent (<实例名>)。

右键点击该服务，选择启动。

Linux开启相关信息请点击下面连接进入查看

https://learn.microsoft.com/zh-cn/sql/linux/sql-server-linux-setup-sql-agent?view=sql-server-ver16&tabs=rhel

6.确认是否开启SQL Server Agent

EXEC xp_servicecontrol N'querystate', N'SQLServerAGENT';

执行上面命令（需要较高的权限，通常只有系统管理员（sysadmin）角色的用户才能使用）输出结构如下所示则开启代理成功

Service State
Running

7.配置事务日志备份（可选）

一旦恢复模式设置为 完全恢复模式，SQL Server 就会记录每个事务的日志。接下来，你需要定期进行事务日志备份。

创建事务日志备份：

在 SSMS 中，选择 Management -> Maintenance Plans -> New Maintenance Plan 来创建一个新的维护计划。
配置备份任务，选择备份类型为 Transaction Log。
选择备份保存位置，通常是磁盘或者文件夹。
定义备份的调度频率，以确保日志备份定期进行。

也可以使用 T-SQL 来进行手动事务日志备份：

BACKUP LOG [YourDatabaseName]
TO DISK = 'C:\Backup\YourDatabaseName_LogBackup.trn'; #windows

8.设置备份保留策略

为了保持日志归档的有效性，你还需要设置备份文件的保留策略。可以使用 SQL Server 的维护计划来删除旧的备份文件，或者手动管理这些备份。

9.监控和管理事务日志文件

由于事务日志会随着活动的增加而增长，因此在启用归档日志时，要定期监控和管理日志文件的大小。如果事务日志文件占用了过多空间，可以考虑以下操作：

截断日志：通过定期备份事务日志来截断日志文件，以释放空间。
压缩日志备份：如果日志文件很大，可以选择压缩事务日志备份文件。