Oracle 的归档日志文件(Archive log files)

-- 4.Oracle 的归档日志文件(Archive log files)
/*************************************************************************************************************************************************************

-- 1. RMAN 备份,开启archivelog后,log过多过大造成造成磁盘空间不足
--查找所有存在的归档日志文件，并更新它的状态。
--如果一个归档日志文件在控制文件中有记录，但是实际在磁盘上不存在，那么它的状态会被标记为 expired(失效)。
RMAN> crosscheck archivelog all;
-- Oracle数据库中删除过期的归档日志
RMAN> delete noprompt expired archivelog all;
-- 命令将删除7天前的所有归档日志。
RMAN> delete noprompt archivelog all completed before 'sysdate-2';

Oracle 的归档日志文件(Archive log files)
一、查看数据库是否为归档模式
二、打开或关闭归档模式
1、正常停库，然后启动数据库到 mount 状态
2、使用 alter database 命令打开归档模式
3、打开数据库，查看归档状态
三、配置归档的位置
1、查看归档进程数
2、配置归档的位置
四、配置归档日志文件的名称格式
五、删除/移动归档日志
1、手工删除归档日志文件
2、用 RMAN 删除数据库记录的归档列表信息
3、清除 v$archived_log 视图中的过期信息

配置归档日志文件的名称格式:
初始化参数 LOG_ARCHIVE_FORMAT 用于指定归档日志文件的名称格式。设置该初始化参数时，可以指定以下匹配符：
(1)%s：日志序列号；
(2)%S：日志序列号(带有前导 0)；
(3)%t：线程编号；
(4)%T：线程编号(带有前导 0)；
(5)%a：活动 ID 号；
(6)%d：数据库 ID 号；
(7)%r：RESETLOGS的ID值。

默认:%t_%s_%r
示例: ALTER SYSTEM SET LOG_ARCHIVE_FORMAT = 'thread_%T_seq_%S.%a.%r.%d' scope = spfile sid='*';
***************************************************************************************************************************************************************/

-- 1. 全备报错,磁盘空间不足;
[oracle@MS ~]$ rman target /

Recovery Manager: Release 19.0.0.0.0 - Production on Mon Jan 20 14:15:39 2025
Version 19.20.0.0.0

connected to target database: MS (DBID=*****)

RMAN>
RMAN> backup database plus archivelog;

Starting backup at 20-JAN-25
current log archived
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=2020 instance=MS device type=DISK
channel ORA_DISK_1: starting archived log backup set
channel ORA_DISK_1: specifying archived log(s) in backup set
input archived log thread=2 sequence=513 RECID=576 STAMP=1190902749
channel ORA_DISK_1: starting piece 1 at 20-JAN-25
channel ORA_DISK_1: finished piece 1 at 20-JAN-25
piece handle=/u01/app/oracle/product/19.3.0/dbhome_1/dbs/0u3fneuu_30_1_1 tag=TAG20250120T141910 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 20-JAN-25

Starting backup at 20-JAN-25
using channel ORA_DISK_1
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00005 name=+DATA/MS/DATAFILE/tbs_ms.295.1152060507
input datafile file number=00002 name=+DATA/MS/DATAFILE/sysaux.284.1152058917
input datafile file number=00003 name=+DATA/MS/DATAFILE/undotbs1.285.1152058919
input datafile file number=00001 name=+DATA/MS/DATAFILE/system.283.1152058917
input datafile file number=00004 name=+DATA/MS/DATAFILE/undotbs2.287.1152058923
channel ORA_DISK_1: starting piece 1 at 20-JAN-25
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of backup plus archivelog command at 01/20/2025 14:19:46
ORA-19502: write error on file "/u01/app/oracle/product/19.3.0/dbhome_1/dbs/0v3fneuv_31_1_1", block number 1482240 (block size=8192)
ORA-27072: File I/O error
Linux-x86_64 Error: 28: No space left on device
Additional information: 4
Additional information: 1482240
Additional information: 4294967295

-- 2. 查看磁盘
[root@MSTCDB02 ~]# df -H
文件系统容量已用可用已用% 挂载点
devtmpfs 135G 0 135G 0% /dev
tmpfs 135G 5.3G 130G 4% /dev/shm
tmpfs 135G 509M 135G 1% /run
tmpfs 135G 0 135G 0% /sys/fs/cgroup
/dev/mapper/centosvg01-root 1.6T 79G 1.5T 6% /
/dev/sda2 1.1G 182M 882M 18% /boot
/dev/sda1 1.1G 12M 1.1G 2% /boot/efi
/dev/mapper/centosvg01-u01 322G 313G 9G 97% /u01
overlay 1.6T 79G 1.5T 6%
[root@MSTCDB02 ~]#
[root@MSTCDB02 ~]#

-- 3. 进入磁盘目录:
[root@MSTCDB02 ~]# du -sh /u01/app/grid
39G /u01/app/grid
[root@MSTCDB02 ~]#
[root@MSTCDB02 ~]# du -sh /u01/app/oracle
250G /u01/app/oracle
[root@MSTCDB02 ~]#
[root@MSTCDB02 ~]# du -sh /u01/app/oracle/base
2.9G /u01/app/oracle/base
[root@MSTCDB02 ~]#
[root@MSTCDB02 ~]# du -sh /u01/app/oracle/product/19.3.0/dbhome_1
247G /u01/app/oracle/product/19.3.0/dbhome_1

-- 4. 删除归档并回收空间:

-- 4.1 运行 del_arc.sh 文件
[oracle@MSTCDB02 ~]$ sh /home/oracle/del_arc.sh
[oracle@MSTCDB02 ~]$

RMAN>
crosscheck archivelog all;
delete noprompt expired archivelog all
-- SYSDATA-7，表明当前的系统时间7天前，before关键字表示在7天前的归档日志，如果使用了闪回功能，也会删除闪回的数据。
-- 同样道理，也可以删除从7天前到现在的全部日志，不过这个命令要考虑清楚，做完这个删除，最好马上进行全备份数据库
delete noprompt archivelog all completed before 'sysdate-2';

-- 使用delete obsolete命令删除过期备份:
RMAN> delete obsolete;

RMAN retention policy will be applied to the command
RMAN retention policy is set to redundancy 1
using channel ORA_DISK_1
Deleting the following obsolete backups and copies:
Type Key Completion Time Filename/Handle
-------------------- ------ ------------------ --------------------
Backup Set 1 05-NOV-23
Backup Piece 1 05-NOV-23 /u01/app/oracle/product/19.3.0/dbhome_1/dbs/c-2969354461-20231105-00
Backup Set 2 16-JAN-25
Backup Piece 2 16-JAN-25 /u01/app/oracle/product/19.3.0/dbhome_1/dbs/023fc95s_2_1_1
Backup Set 3 16-JAN-25
Backup Piece 3 16-JAN-25 /u01/app/oracle/product/19.3.0/dbhome_1/dbs/033fc95t_3_1_1
Backup Set 4 16-JAN-25
Backup Piece 4 16-JAN-25 /u01/app/oracle/product/19.3.0/dbhome_1/dbs/043fc98j_4_1_1
Backup Set 5 16-JAN-25
Backup Piece 5 16-JAN-25 /home/rman_back/c-2969354461-20250116-00
Backup Set 6 16-JAN-25
Backup Piece 6 16-JAN-25 /u01/app/oracle/product/19.3.0/dbhome_1/dbs/063fceoi_6_1_1
Backup Set 7 16-JAN-25
Backup Piece 7 16-JAN-25 /u01/app/oracle/product/19.3.0/dbhome_1/dbs/073fceoj_7_1_1
Backup Set 8 16-JAN-25
Backup Piece 8 16-JAN-25 /u01/app/oracle/product/19.3.0/dbhome_1/dbs/083fceul_8_1_1
Backup Set 9 16-JAN-25
Backup Piece 9 16-JAN-25 /u01/app/oracle/product/19.3.0/dbhome_1/dbs/093fceun_9_1_1
Backup Set 10 16-JAN-25
Backup Piece 10 16-JAN-25 /u01/app/oracle/product/19.3.0/dbhome_1/dbs/0a3fcf12_10_1_1
Backup Set 11 16-JAN-25
Backup Piece 11 16-JAN-25 /home/rman_back/c-2969354461-20250116-01
Backup Set 12 16-JAN-25
Backup Piece 12 16-JAN-25 /u01/app/oracle/product/19.3.0/dbhome_1/dbs/0c3fcg7k_12_1_1
Backup Set 13 16-JAN-25
Backup Piece 13 16-JAN-25 /u01/app/oracle/product/19.3.0/dbhome_1/dbs/0d3fcg7l_13_1_1
Backup Set 14 16-JAN-25
Backup Piece 14 16-JAN-25 /u01/app/oracle/product/19.3.0/dbhome_1/dbs/0e3fcgab_14_1_1
Backup Set 15 16-JAN-25
Backup Piece 15 16-JAN-25 /home/rman_back/c-2969354461-20250116-02
Backup Set 16 17-JAN-25
Backup Piece 16 17-JAN-25 /u01/app/oracle/product/19.3.0/dbhome_1/dbs/0g3fe89c_16_1_1
Backup Set 17 17-JAN-25
Backup Piece 17 17-JAN-25 /u01/app/oracle/product/19.3.0/dbhome_1/dbs/0h3fe89d_17_1_1
Backup Set 18 17-JAN-25
Backup Piece 18 17-JAN-25 /u01/app/oracle/product/19.3.0/dbhome_1/dbs/0i3fe8bp_18_1_1
Backup Set 19 17-JAN-25
Backup Piece 19 17-JAN-25 /home/rman_back/c-2969354461-20250117-00
Backup Set 20 18-JAN-25
Backup Piece 20 18-JAN-25 /u01/app/oracle/product/19.3.0/dbhome_1/dbs/0k3fgslc_20_1_1
-- 确定删除:
Do you really want to delete the above objects (enter YES or NO)? yes
deleted backup piece
backup piece handle=/home/rman_back/c-2969354461-20250116-00 RECID=5 STAMP=1190536468
deleted backup piece
backup piece handle=/home/rman_back/c-2969354461-20250116-01 RECID=11 STAMP=1190542371
deleted backup piece
backup piece handle=/home/rman_back/c-2969354461-20250116-02 RECID=15 STAMP=1190543692
deleted backup piece
backup piece handle=/u01/app/oracle/product/19.3.0/dbhome_1/dbs/0g3fe89c_16_1_1 RECID=16 STAMP=1190601004
deleted backup piece
backup piece handle=/u01/app/oracle/product/19.3.0/dbhome_1/dbs/0h3fe89d_17_1_1 RECID=17 STAMP=1190601005
deleted backup piece
backup piece handle=/u01/app/oracle/product/19.3.0/dbhome_1/dbs/0i3fe8bp_18_1_1 RECID=18 STAMP=1190601081
deleted backup piece
backup piece handle=/home/rman_back/c-2969354461-20250117-00 RECID=19 STAMP=1190601082
deleted backup piece
backup piece handle=/u01/app/oracle/product/19.3.0/dbhome_1/dbs/0k3fgslc_20_1_1 RECID=20 STAMP=1190687404
Deleted 8 objects

RMAN-06207: warning: 12 objects could not be deleted for DISK channel(s) due
RMAN-06208: to mismatched status. Use CROSSCHECK command to fix status
RMAN-06210: List of Mismatched objects
RMAN-06211: ==========================
RMAN-06212: Object Type Filename/Handle
RMAN-06213: --------------- ---------------------------------------------------
RMAN-06214: Backup Piece /u01/app/oracle/product/19.3.0/dbhome_1/dbs/c-2969354461-20231105-00
RMAN-06214: Backup Piece /u01/app/oracle/product/19.3.0/dbhome_1/dbs/023fc95s_2_1_1
RMAN-06214: Backup Piece /u01/app/oracle/product/19.3.0/dbhome_1/dbs/033fc95t_3_1_1
RMAN-06214: Backup Piece /u01/app/oracle/product/19.3.0/dbhome_1/dbs/043fc98j_4_1_1
RMAN-06214: Backup Piece /u01/app/oracle/product/19.3.0/dbhome_1/dbs/063fceoi_6_1_1
RMAN-06214: Backup Piece /u01/app/oracle/product/19.3.0/dbhome_1/dbs/073fceoj_7_1_1
RMAN-06214: Backup Piece /u01/app/oracle/product/19.3.0/dbhome_1/dbs/083fceul_8_1_1
RMAN-06214: Backup Piece /u01/app/oracle/product/19.3.0/dbhome_1/dbs/093fceun_9_1_1
RMAN-06214: Backup Piece /u01/app/oracle/product/19.3.0/dbhome_1/dbs/0a3fcf12_10_1_1
RMAN-06214: Backup Piece /u01/app/oracle/product/19.3.0/dbhome_1/dbs/0c3fcg7k_12_1_1
RMAN-06214: Backup Piece /u01/app/oracle/product/19.3.0/dbhome_1/dbs/0d3fcg7l_13_1_1
RMAN-06214: Backup Piece /u01/app/oracle/product/19.3.0/dbhome_1/dbs/0e3fcgab_14_1_1

-- 4.2 使用RMAN的crosscheck命令来验证所有归档日志
RMAN> crosscheck archivelog all;

released channel: ORA_DISK_1
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=2020 instance=MSTCHIS2 device type=DISK
validation succeeded for archived log
archived log file name=+DATA/MS/ARCHIVELOG/2025_01_20/thread_2_seq_513.558.1190902749 RECID=576 STAMP=1190902749
Crosschecked 1 objects

RMAN>
RMAN>
-- 4.3 用list expired看看是否有失效的archive log,证明没有失效的archive log:
RMAN> list expired archivelog all;

specification does not match any archived log in the repository

RMAN>
RMAN> crosscheck archivelog all;

released channel: ORA_DISK_1
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=2020 instance=MS device type=DISK
validation succeeded for archived log
archived log file name=+DATA/MS/ARCHIVELOG/2025_01_20/thread_2_seq_513.558.1190902749 RECID=576 STAMP=1190902749
Crosschecked 1 objects

RMAN>
RMAN>
-- 4.4 修改备份文件存放路径
RMAN> configure controlfile autobackup format for device type disk to '/home/rman_back/%F';

old RMAN configuration parameters:
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '/home/rman_back/%F';
new RMAN configuration parameters:
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '/home/rman_back/%F';
new RMAN configuration parameters are successfully stored

RMAN>
RMAN>
-- 查看是否修改成功
RMAN> show controlfile autobackup format;

RMAN configuration parameters for database with db_unique_name MSTCHIS are:
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '/home/rman_back/%F';

RMAN>

-- 4.5 修改快速恢复区目录: 闪回
-- 重启才能生效!
SQL>
SQL>
SQL> ALTER SYSTEM SET DB_RECOVERY_FILE_DEST_SIZE = 100G SCOPE=SPFILE;

System altered.

SQL>
SQL>
SQL> ALTER SYSTEM SET DB_RECOVERY_FILE_DEST='/home/rman_back' SCOPE=SPFILE;

System altered.

SQL>
SQL> show parameter db_recovery;

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest string
db_recovery_file_dest_size big integer 0
SQL>
SQL>
SQL> show parameter db_recovery;

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest string /home/oracle/rman_back
db_recovery_file_dest_size big integer 3882M
SQL>
SQL>

-- 5. 更改 Archive destination路径:
[oracle@MSTCDB02 ~]$ sqlplus SYS/123456 as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Tue Jan 21 08:38:30 2025
Version 19.20.0.0.0

Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.20.0.0.0
-- 关闭数据库
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>
SQL>
-- 打开数据库
SQL> startup mount;
ORACLE instance started.

Total System Global Area 6.8719E+10 bytes
Fixed Size 37257296 bytes
Variable Size 5.1405E+10 bytes
Database Buffers 1.7180E+10 bytes
Redo Buffers 96956416 bytes
Database mounted.
SQL>
-- 查看格式
SQL> SHOW PARAMETER LOG_ARCHIVE_FORMAT;

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
log_archive_format string %t_%s_%r.arc
SQL>
SQL>
-- 查看归档进程数
SQL> SHOW PARAMETER LOG_ARCHIVE_MAX_PROCESSES;

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
log_archive_max_processes integer 4
SQL>
SQL>
-- 查看归档状态
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination +DATA
Oldest online log sequence 517
Next log sequence to archive 518
Current log sequence 518
SQL>
SQL>
-- 打开数据库
SQL> alter database open;

Database altered.

SQL>
SQL>
-- 修改路径:
SQL> ALTER SYSTEM SET log_archive_dest_1='LOCATION=/home/rman_back' SCOPE=BOTH;

System altered.
SQL>
SQL>
-- 查看参数 LOG_ARCHIVE_FORMAT 的值(格式):
SQL> SHOW PARAMETER LOG_ARCHIVE_FORMAT;

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
log_archive_format string %t_%s_%r.arc
SQL>
SQL>
-- 查看归档状态
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /home/rman_back
Oldest online log sequence 517
Next log sequence to archive 518
Current log sequence 518
SQL>
SQL>
-- 6. 删除/移动归档日志
-- 手工切换日志
SQL> alter system switch logfile;

System altered.

SQL>
SQL>
-- 重新查看归档日志：
SQL> select sequence#, name from v$archived_log;

SEQUENCE# NAME
518 /home/rman_back/2_518_1152058909.arc
224 rows selected.

SQL>

-- 移动之前的Archive Log
[oracle@MSTCDB02 ~]$ mv /u01/app/oracle/product/19.3.0/dbhome_1/dbs/0l3fgsld_21_1_1 /home/rman_back
[oracle@MSTCDB02 ~]$ mv /u01/app/oracle/product/19.3.0/dbhome_1/dbs/0* /home/rman_back
[oracle@MSTCDB02 ~]$ mv /u01/app/oracle/product/19.3.0/dbhome_1/dbs/1* /home/rman_back
[oracle@MSTCDB02 ~]$