【数据库管理】⑤归档日志Archive Log
【数据库管理】⑤归档日志Archive Log
·
1.日志归档的概述和用途
日志归档是指将数据库的归档日志文件保存到指定的位置，以便在需要时进行恢复和回滚操作。在Oracle数据库中，日志归档是一种重要的备份和恢复策略，可以保证数据库的数据完整性和可靠性。
日志归档的主要用途包括：
数据库备份：归档日志文件可以用于数据库备份，以保证在数据库出现故障或数据丢失时可以进行恢复操作。
数据库恢复：归档日志文件可以用于数据库恢复，以恢复到某个特定的时间点或事务点。
数据库回滚：归档日志文件可以用于数据库回滚，以撤销某个特定的事务或操作。
数据库复制：归档日志文件可以用于数据库复制，以保证在主数据库出现故障时可以切换到备份数据库。
是联机重做日志(redo log)组文件的一个副本
包含redo记录以及一个唯一的log sequence number
启用归档模式的目的:数据变更前需要由LGWR进程将内存中的log buffer(重做数据)写入redo log(重做日志)日志组,但由于日志组是循环复用结构.日志组切换需要覆盖时如果未开启归档模式会被覆盖,造成日志缺失,无法完成数据库的完全恢复.既开启归档模式后,可以完成数据库的介质恢复(recovery).
对日志组中的一个日志文件进行归档,如果该组其中一个损坏,则另一个可用的日志将会被归档
对于归档模式的日志切换,当日志归档完成后,下一个日志才能被覆盖或重新使用
自动归档功能如开启,则后台进程ARCn在日志切换时自动完成归档,否则需要手动归档
使用LogMiner 提取历史日志的相关信息
LGWR是Oracle数据库中的一个重要进程，全称为Log Writer。它的主要作用是将数据库缓冲区中的脏数据写入到磁盘上的重做日志文件中，以保证数据库的数据完整性和可靠性。
具体来说，LGWR进程的主要工作包括：
将脏数据写入重做日志缓冲区：当用户提交事务时，Oracle会将事务的修改操作记录到重做日志缓冲区中，这些数据被称为脏数据。LGWR进程会定期将这些脏数据写入到磁盘上的重做日志文件中。
维护重做日志文件：LGWR进程会定期将重做日志文件切换到下一个文件，以保证重做日志文件不会过大或过小。
提供数据恢复支持：重做日志文件可以用于数据库的恢复和回滚操作，LGWR进程的工作可以保证重做日志文件的完整性和可靠性，从而保证数据库的数据完整性和可靠性。
LogMiner是Oracle数据库中的一个工具，可以用于分析和查看数据库的重做日志文件，以便进行数据恢复、数据分析和数据审计等操作。LogMiner可以解析重做日志文件中的SQL语句，并将其转换为易于理解的格式，从而方便用户进行数据分析和恢复操作。
具体来说，LogMiner的主要功能包括：
数据恢复：LogMiner可以用于恢复误删除或误修改的数据，通过分析重做日志文件中的SQL语句，可以找到被删除或修改的数据，并进行恢复操作。
数据分析：LogMiner可以用于分析数据库的历史数据，通过分析重做日志文件中的SQL语句，可以了解数据库的操作历史和数据变化情况。
数据审计：LogMiner可以用于审计数据库的操作，通过分析重做日志文件中的SQL语句，可以了解数据库的操作情况和操作者。
使用LogMiner需要先启用数据库的归档模式，并将重做日志文件保存到指定的位置。然后，可以使用DBMS_LOGMNR包中的函数来分析重做日志文件，例如使用DBMS_LOGMNR.START_LOGMNR函数开始分析重做日志文件，使用DBMS_LOGMNR.ADD_LOGFILE函数添加重做日志文件，使用DBMS_LOGMNR.START_LOGMNR函数结束分析重做日志文件等。
2. 归档和非归档的区别
归档和非归档是Oracle数据库中两种不同的工作模式，它们的主要区别在于重做日志文件的处理方式。
在归档模式下，Oracle会将数据库的归档日志文件保存到指定的位置，以便在需要时进行恢复和回滚操作。在归档模式下，重做日志文件会被自动归档，即当一个重做日志文件被写满后，Oracle会将其重命名并移动到归档目录中，然后创建一个新的重做日志文件。这样可以保证数据库的数据完整性和可靠性，同时也可以提高数据库的可用性和可靠性。
在非归档模式下，Oracle不会将重做日志文件保存到指定的位置，而是直接覆盖之前的重做日志文件。这样会导致重做日志文件的数据被不断覆盖，无法进行恢复和回滚操作，因此非归档模式下的数据库容易出现数据丢失和不可恢复的情况。
在生产环境中，建议使用归档模式来保证数据库的数据完整性和可靠性。
3. 查看归档模式的方法
3.1 archive log list
使用SQLPlus命令查询：可以使用SQLPlus命令查询数据库的归档模式，具体操作步骤如下：
a. 打开SQL*Plus命令行界面；
b. 连接到目标数据库；
c. 执行以下命令：
[oracle@oracle-db-19c ~]$ sqlplus / as sysdba
SQL*Plus: Release 19.0.0.0.0 - Production on Sun Apr 2 14:25:06 2023
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle. All rights reserved.
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
SQL> show user;
USER is "SYS"
SQL> show con_name;
CON_NAME
------------------------------
CDB$ROOT
SQL> alter session set container=PDB1;
Session altered.
SQL> show user;
USER is "SYS"
SQL> show con_name;
CON_NAME
------------------------------
PDB1
SQL>
SQL>
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /u01/app/oracle/product/19.3.0/dbhome_1/dbs/arch
Oldest online log sequence 395
Next log sequence to archive 397
Current log sequence 397
SQL> 如果查询结果中包含“Database log mode: Archive Mode”，则表示数据库处于归档模式；如果查询结果中包含“Database log mode: No Archive Mode”，则表示数据库处于非归档模式。
3.2 V$DATABASE视图
SQL>
SQL> set pagesize 200 linesize 200
SQL> select name,log_mode from v$database;
NAME LOG_MODE
--------------------------- ------------------------------------
CDB1 ARCHIVELOG
SQL> 如果查询结果为ARCHIVELOG，则表示数据库处于归档模式；如果查询结果为NOARCHIVELOG，则表示数据库处于非归档模式。
3.3 通过查看数据库的参数文件进行查看归档模式
查看数据库参数文件：可以查看数据库的参数文件，以确定数据库的归档模式。具体操作步骤如下：
a. 打开数据库参数文件（通常为$ORACLE_HOME/dbs/init<ORACLE_SID>.ora）；
b. 查找参数log_archive_start，如果该参数的值为TRUE，则表示数据库处于归档模式；如果该参数的值为FALSE，则表示数据库处于非归档模式。
4. 日志归档
日志归档是Oracle数据库中的一种重要功能，它可以将数据库的重做日志文件保存到指定的位置，以便在需要时进行恢复和回滚操作。在归档模式下，重做日志文件会被自动归档，即当一个重做日志文件被写满后，Oracle会将其重命名并移动到归档目录中，然后创建一个新的重做日志文件。这样可以保证数据库的数据完整性和可靠性，同时也可以提高数据库的可用性和可靠性。
日志归档的主要作用包括：
数据恢复：日志归档可以用于恢复误删除或误修改的数据，通过分析归档日志文件中的SQL语句，可以找到被删除或修改的数据，并进行恢复操作。
数据备份：日志归档可以用于备份数据库的数据，通过备份归档日志文件，可以保证数据库的数据完整性和可靠性。
数据分析：日志归档可以用于分析数据库的历史数据，通过分析归档日志文件中的SQL语句，可以了解数据库的操作历史和数据变化情况。
数据审计：日志归档可以用于审计数据库的操作，通过分析归档日志文件中的SQL语句，可以了解数据库的操作情况和操作者。
总之，日志归档是Oracle数据库中非常重要的一个功能，可以用于数据恢复、数据备份、数据分析和数据审计等操作，可以帮助用户更好地管理和维护数据库。
在归档时，Oracle会将归档信息写入到控制文件中。控制文件是Oracle数据库中的一个重要文件，它记录了数据库的结构信息、日志信息和备份信息等，是数据库的重要组成部分之一。
在归档模式下，当一个重做日志文件被写满后，Oracle会将其重命名并移动到归档目录中，同时将归档信息写入到控制文件中。归档信息包括归档日志文件的名称、路径、创建时间等信息，可以用于恢复和回滚操作。
控制文件中的归档信息可以通过以下SQL语句查询：
SQL> SELECT * FROM v$archived_log;该查询语句可以列出所有已归档的日志文件的信息，包括文件名、路径、创建时间、归档时间等信息。
总之，在归档模式下，Oracle会将归档信息写入到控制文件中，以便在需要时进行恢复和回滚操作。控制文件中的归档信息可以通过SQL语句查询。
4.1 非归档到归档模式
a.一致性关闭数据库(shutdown [immediate | transactional |normal])
b.启动到mount阶段(startup mount)
c.切换到归档模式(alter database archivelog [manual])
d.切换到open阶段(alter database open)
e.对数据做一个完整备份(full backup)
演示非归档到归档模式
4.1.1 shutdown immediate
SQL> shutdown immediate -- 这里shutdown 一定要immediate方式 Database closed.
Database dismounted.
ORACLE instance shut down.
4.1.2 startup mount
SQL> startup mount -- 到mount下设置
4.1.3 alter database archivelog;
SQL> alter database archivelog;
Database altered.
4.1.4 alter database open;
SQL> alter database open;
4.1.5 archive log list
SQL>
SQL>
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /u01/app/oracle/product/19.3.0/dbhome_1/dbs/arch
Oldest online log sequence 398
Next log sequence to archive 400
Current log sequence 400
SQL> 4.2 归档模式切换到非归档模式
a.一致性关闭数据库(shutdown [immediate | transactional |normal])
b.启动到mount阶段(startup mount)
c.切换到归档模式(alter database noarchivelog )
d.切换到open阶段(alter database open)
e.对数据做一个完整备份(full backup)
4.3 手动归档
手动归档时需要将日志模式切换为alter database archivelog manual
再使用alter system archive log current | all 实现归档
注意:手动归档模式下不支持standby数据库
-- 将日志模式切换为手工归档模式
SQL> ALTER DATABASE ARCHIVELOG MANUAL;
Database altered.
-- 打开数据库
SQL> ALTER DATABASE OPEN;
Database altered.
-- 对当前的日志进行归档(建议在mount阶段完成)
SQL> ALTER SYSTEM ARCHIVE LOG CURRENT;
System altered.
-- 对所有的日志进行归档
SQL> ALTER SYSTEM ARCHIVE LOG ALL;
alter system archive log all
*
ERROR at line 1:
ORA-00271: there are no logs that need archiving -- 没有需要归档的日志
-- 在alter database archivelog模式下也可以实现对日志的归档
-- 手工归档方法一.
SQL> alter system switch logfile;
-- 手工归档方法二,此方式仅限于Archive mode.
SQL> alter system archive log current;
-- 查看已经归档日志:
SQL> col name format a80
SQL> select name from v$archived_log;
NAME
--------------------------------------------------------------------------------
/u01/app/oracle/product/19.3.0/dbhome_1/dbs/arch1_171_1119711914.dbf
/u01/app/oracle/product/19.3.0/dbhome_1/dbs/arch1_172_1119711914.dbf
/u01/app/oracle/product/19.3.0/dbhome_1/dbs/arch1_173_1119711914.dbf
/u01/app/oracle/product/19.3.0/dbhome_1/dbs/arch1_174_1119711914.dbf
/u01/app/oracle/product/19.3.0/dbhome_1/dbs/arch1_175_1119711914.dbf
/u01/app/oracle/product/19.3.0/dbhome_1/dbs/arch1_176_1119711914.dbf
/u01/app/oracle/product/19.3.0/dbhome_1/dbs/arch1_177_1119711914.dbf
/u01/app/oracle/product/19.3.0/dbhome_1/dbs/arch1_178_1119711914.dbf
/u01/app/oracle/product/19.3.0/dbhome_1/dbs/arch1_179_1119711914.dbf
/u01/app/oracle/product/19.3.0/dbhome_1/dbs/arch1_180_1119711914.dbf
/u01/app/oracle/product/19.3.0/dbhome_1/dbs/arch1_181_1119711914.dbf
/u01/app/oracle/product/19.3.0/dbhome_1/dbs/arch1_182_1119711914.dbf
/u01/app/oracle/product/19.3.0/dbhome_1/dbs/arch1_183_1119711914.dbf
/u01/app/oracle/product/19.3.0/dbhome_1/dbs/arch1_184_1119711914.dbf
/u01/app/oracle/product/19.3.0/dbhome_1/dbs/arch1_185_1119711914.dbf
/u01/app/oracle/product/19.3.0/dbhome_1/dbs/arch1_186_1119711914.dbf
/u01/app/oracle/product/19.3.0/dbhome_1/dbs/arch1_187_1119711914.dbf
/u01/app/oracle/product/19.3.0/dbhome_1/dbs/arch1_188_1119711914.dbf
/u01/app/oracle/product/19.3.0/dbhome_1/dbs/arch1_189_1119711914.dbf
/u01/app/oracle/product/19.3.0/dbhome_1/dbs/arch1_190_1119711914.dbf
/u01/app/oracle/product/19.3.0/dbhome_1/dbs/arch1_191_1119711914.dbf
/u01/app/oracle/product/19.3.0/dbhome_1/dbs/arch1_192_1119711914.dbf
/u01/app/oracle/product/19.3.0/dbhome_1/dbs/arch1_193_1119711914.dbf
/u01/app/oracle/product/19.3.0/dbhome_1/dbs/arch1_194_1119711914.dbf
/u01/app/oracle/product/19.3.0/dbhome_1/dbs/arch1_195_1119711914.dbf
/u01/app/oracle/product/19.3.0/dbhome_1/dbs/arch1_196_1119711914.dbf
/u01/app/oracle/product/19.3.0/dbhome_1/dbs/arch1_197_1119711914.dbf
/u01/app/oracle/product/19.3.0/dbhome_1/dbs/arch1_198_1119711914.dbf
/u01/app/oracle/product/19.3.0/dbhome_1/dbs/arch1_199_1119711914.dbf
/u01/app/oracle/product/19.3.0/dbhome_1/dbs/arch1_200_1119711914.dbf
/u01/app/oracle/product/19.3.0/dbhome_1/dbs/arch1_201_1119711914.dbf
/u01/app/oracle/product/19.3.0/dbhome_1/dbs/arch1_202_1119711914.dbf
/u01/app/oracle/product/19.3.0/dbhome_1/dbs/arch1_203_1119711914.dbf
/u01/app/oracle/product/19.3.0/dbhome_1/dbs/arch1_204_1119711914.dbf
/u01/app/oracle/product/19.3.0/dbhome_1/dbs/arch1_205_1119711914.dbf
/u01/app/oracle/product/19.3.0/dbhome_1/dbs/arch1_206_1119711914.dbf
/u01/app/oracle/product/19.3.0/dbhome_1/dbs/arch1_207_1119711914.dbf
/u01/app/oracle/product/19.3.0/dbhome_1/dbs/arch1_208_1119711914.dbf
/u01/app/oracle/product/19.3.0/dbhome_1/dbs/arch1_209_1119711914.dbf
/u01/app/oracle/product/19.3.0/dbhome_1/dbs/arch1_210_1119711914.dbf
/u01/app/oracle/product/19.3.0/dbhome_1/dbs/arch1_211_1119711914.dbf
/u01/app/oracle/product/19.3.0/dbhome_1/dbs/arch1_212_1119711914.dbf
/u01/app/oracle/product/19.3.0/dbhome_1/dbs/arch1_213_1119711914.dbf
/u01/app/oracle/product/19.3.0/dbhome_1/dbs/arch1_214_1119711914.dbf
/u01/app/oracle/product/19.3.0/dbhome_1/dbs/arch1_215_1119711914.dbf
/u01/app/oracle/product/19.3.0/dbhome_1/dbs/arch1_216_1119711914.dbf
/u01/app/oracle/product/19.3.0/dbhome_1/dbs/arch1_217_1119711914.dbf
/u01/app/oracle/product/19.3.0/dbhome_1/dbs/arch1_218_1119711914.dbf
/u01/app/oracle/product/19.3.0/dbhome_1/dbs/arch1_219_1119711914.dbf
/u01/app/oracle/product/19.3.0/dbhome_1/dbs/arch1_220_1119711914.dbf
/u01/app/oracle/product/19.3.0/dbhome_1/dbs/arch1_221_1119711914.dbf
/u01/app/oracle/product/19.3.0/dbhome_1/dbs/arch1_222_1119711914.dbf
/u01/app/oracle/product/19.3.0/dbhome_1/dbs/arch1_223_1119711914.dbf
/u01/app/oracle/product/19.3.0/dbhome_1/dbs/arch1_224_1119711914.dbf
/u01/app/oracle/product/19.3.0/dbhome_1/dbs/arch1_225_1119711914.dbf
/u01/app/oracle/product/19.3.0/dbhome_1/dbs/arch1_226_1119711914.dbf
/u01/app/oracle/product/19.3.0/dbhome_1/dbs/arch1_227_1119711914.dbf
/u01/app/oracle/product/19.3.0/dbhome_1/dbs/arch1_228_1119711914.dbf
/u01/app/oracle/product/19.3.0/dbhome_1/dbs/arch1_229_1119711914.dbf
/u01/app/oracle/product/19.3.0/dbhome_1/dbs/arch1_230_1119711914.dbf
/u01/app/oracle/product/19.3.0/dbhome_1/dbs/arch1_231_1119711914.dbf
/u01/app/oracle/product/19.3.0/dbhome_1/dbs/arch1_232_1119711914.dbf
/u01/app/oracle/product/19.3.0/dbhome_1/dbs/arch1_233_1119711914.dbf
/u01/app/oracle/product/19.3.0/dbhome_1/dbs/arch1_234_1119711914.dbf
/u01/app/oracle/product/19.3.0/dbhome_1/dbs/arch1_235_1119711914.dbf
/u01/app/oracle/product/19.3.0/dbhome_1/dbs/arch1_236_1119711914.dbf
/u01/app/oracle/product/19.3.0/dbhome_1/dbs/arch1_237_1119711914.dbf
/u01/app/oracle/product/19.3.0/dbhome_1/dbs/arch1_238_1119711914.dbf
/u01/app/oracle/product/19.3.0/dbhome_1/dbs/arch1_239_1119711914.dbf
/u01/app/oracle/product/19.3.0/dbhome_1/dbs/arch1_240_1119711914.dbf
/u01/app/oracle/product/19.3.0/dbhome_1/dbs/arch1_241_1119711914.dbf
/u01/app/oracle/product/19.3.0/dbhome_1/dbs/arch1_242_1119711914.dbf
/u01/app/oracle/product/19.3.0/dbhome_1/dbs/arch1_243_1119711914.dbf
/u01/app/oracle/product/19.3.0/dbhome_1/dbs/arch1_244_1119711914.dbf
/u01/app/oracle/product/19.3.0/dbhome_1/dbs/arch1_245_1119711914.dbf
/u01/app/oracle/product/19.3.0/dbhome_1/dbs/arch1_246_1119711914.dbf
/u01/app/oracle/product/19.3.0/dbhome_1/dbs/arch1_247_1119711914.dbf
/u01/app/oracle/product/19.3.0/dbhome_1/dbs/arch1_248_1119711914.dbf
/u01/app/oracle/product/19.3.0/dbhome_1/dbs/arch1_249_1119711914.dbf
/u01/app/oracle/product/19.3.0/dbhome_1/dbs/arch1_250_1119711914.dbf
/u01/app/oracle/product/19.3.0/dbhome_1/dbs/arch1_251_1119711914.dbf
/u01/app/oracle/product/19.3.0/dbhome_1/dbs/arch1_252_1119711914.dbf
/u01/app/oracle/product/19.3.0/dbhome_1/dbs/arch1_253_1119711914.dbf
/u01/app/oracle/product/19.3.0/dbhome_1/dbs/arch1_254_1119711914.dbf
/u01/app/oracle/product/19.3.0/dbhome_1/dbs/arch1_255_1119711914.dbf
/u01/app/oracle/product/19.3.0/dbhome_1/dbs/arch1_256_1119711914.dbf
/u01/app/oracle/product/19.3.0/dbhome_1/dbs/arch1_257_1119711914.dbf
/u01/app/oracle/product/19.3.0/dbhome_1/dbs/arch1_258_1119711914.dbf
/u01/app/oracle/product/19.3.0/dbhome_1/dbs/arch1_259_1119711914.dbf
/u01/app/oracle/product/19.3.0/dbhome_1/dbs/arch1_260_1119711914.dbf
/u01/app/oracle/product/19.3.0/dbhome_1/dbs/arch1_261_1119711914.dbf
/u01/app/oracle/product/19.3.0/dbhome_1/dbs/arch1_262_1119711914.dbf
/u01/app/oracle/product/19.3.0/dbhome_1/dbs/arch1_263_1119711914.dbf
/u01/app/oracle/product/19.3.0/dbhome_1/dbs/arch1_264_1119711914.dbf
/u01/app/oracle/product/19.3.0/dbhome_1/dbs/arch1_265_1119711914.dbf
/u01/app/oracle/product/19.3.0/dbhome_1/dbs/arch1_266_1119711914.dbf
/u01/app/oracle/product/19.3.0/dbhome_1/dbs/arch1_267_1119711914.dbf
/u01/app/oracle/product/19.3.0/dbhome_1/dbs/arch1_268_1119711914.dbf
/u01/app/oracle/product/19.3.0/dbhome_1/dbs/arch1_269_1119711914.dbf
/u01/app/oracle/product/19.3.0/dbhome_1/dbs/arch1_270_1119711914.dbf
/u01/app/oracle/product/19.3.0/dbhome_1/dbs/arch1_271_1119711914.dbf
/u01/app/oracle/product/19.3.0/dbhome_1/dbs/arch1_272_1119711914.dbf
/u01/app/oracle/product/19.3.0/dbhome_1/dbs/arch1_273_1119711914.dbf
/u01/app/oracle/product/19.3.0/dbhome_1/dbs/arch1_274_1119711914.dbf
/u01/app/oracle/product/19.3.0/dbhome_1/dbs/arch1_275_1119711914.dbf
/u01/app/oracle/product/19.3.0/dbhome_1/dbs/arch1_276_1119711914.dbf
/u01/app/oracle/product/19.3.0/dbhome_1/dbs/arch1_277_1119711914.dbf
/u01/app/oracle/product/19.3.0/dbhome_1/dbs/arch1_278_1119711914.dbf
/u01/app/oracle/product/19.3.0/dbhome_1/dbs/arch1_279_1119711914.dbf
/u01/app/oracle/product/19.3.0/dbhome_1/dbs/arch1_280_1119711914.dbf
/u01/app/oracle/product/19.3.0/dbhome_1/dbs/arch1_281_1119711914.dbf
/u01/app/oracle/product/19.3.0/dbhome_1/dbs/arch1_282_1119711914.dbf
/u01/app/oracle/product/19.3.0/dbhome_1/dbs/arch1_283_1119711914.dbf
/u01/app/oracle/product/19.3.0/dbhome_1/dbs/arch1_284_1119711914.dbf
/u01/app/oracle/product/19.3.0/dbhome_1/dbs/arch1_285_1119711914.dbf
/u01/app/oracle/product/19.3.0/dbhome_1/dbs/arch1_286_1119711914.dbf
/u01/app/oracle/product/19.3.0/dbhome_1/dbs/arch1_287_1119711914.dbf
/u01/app/oracle/product/19.3.0/dbhome_1/dbs/arch1_288_1119711914.dbf
/u01/app/oracle/product/19.3.0/dbhome_1/dbs/arch1_289_1119711914.dbf
/u01/app/oracle/product/19.3.0/dbhome_1/dbs/arch1_290_1119711914.dbf
/u01/app/oracle/product/19.3.0/dbhome_1/dbs/arch1_291_1119711914.dbf
/u01/app/oracle/product/19.3.0/dbhome_1/dbs/arch1_292_1119711914.dbf
/u01/app/oracle/product/19.3.0/dbhome_1/dbs/arch1_293_1119711914.dbf
/u01/app/oracle/product/19.3.0/dbhome_1/dbs/arch1_294_1119711914.dbf
/u01/app/oracle/product/19.3.0/dbhome_1/dbs/arch1_295_1119711914.dbf
/u01/app/oracle/product/19.3.0/dbhome_1/dbs/arch1_296_1119711914.dbf
/u01/app/oracle/product/19.3.0/dbhome_1/dbs/arch1_297_1119711914.dbf
/u01/app/oracle/product/19.3.0/dbhome_1/dbs/arch1_298_1119711914.dbf
/u01/app/oracle/product/19.3.0/dbhome_1/dbs/arch1_299_1119711914.dbf
/u01/app/oracle/product/19.3.0/dbhome_1/dbs/arch1_300_1119711914.dbf
/u01/app/oracle/product/19.3.0/dbhome_1/dbs/arch1_301_1119711914.dbf
/u01/app/oracle/product/19.3.0/dbhome_1/dbs/arch1_302_1119711914.dbf
/u01/app/oracle/product/19.3.0/dbhome_1/dbs/arch1_303_1119711914.dbf
/u01/app/oracle/product/19.3.0/dbhome_1/dbs/arch1_304_1119711914.dbf
/u01/app/oracle/product/19.3.0/dbhome_1/dbs/arch1_305_1119711914.dbf
/u01/app/oracle/product/19.3.0/dbhome_1/dbs/arch1_306_1119711914.dbf
/u01/app/oracle/product/19.3.0/dbhome_1/dbs/arch1_307_1119711914.dbf
/u01/app/oracle/product/19.3.0/dbhome_1/dbs/arch1_308_1119711914.dbf
/u01/app/oracle/product/19.3.0/dbhome_1/dbs/arch1_309_1119711914.dbf
/u01/app/oracle/product/19.3.0/dbhome_1/dbs/arch1_310_1119711914.dbf
/u01/app/oracle/product/19.3.0/dbhome_1/dbs/arch1_311_1119711914.dbf
/u01/app/oracle/product/19.3.0/dbhome_1/dbs/arch1_312_1119711914.dbf
/u01/app/oracle/product/19.3.0/dbhome_1/dbs/arch1_313_1119711914.dbf
/u01/app/oracle/product/19.3.0/dbhome_1/dbs/arch1_314_1119711914.dbf
/u01/app/oracle/product/19.3.0/dbhome_1/dbs/arch1_315_1119711914.dbf
/u01/app/oracle/product/19.3.0/dbhome_1/dbs/arch1_316_1119711914.dbf
/u01/app/oracle/product/19.3.0/dbhome_1/dbs/arch1_317_1119711914.dbf
/u01/app/oracle/product/19.3.0/dbhome_1/dbs/arch1_318_1119711914.dbf
/u01/app/oracle/product/19.3.0/dbhome_1/dbs/arch1_319_1119711914.dbf
/u01/app/oracle/product/19.3.0/dbhome_1/dbs/arch1_320_1119711914.dbf
/u01/app/oracle/product/19.3.0/dbhome_1/dbs/arch1_321_1119711914.dbf
/u01/app/oracle/product/19.3.0/dbhome_1/dbs/arch1_322_1119711914.dbf
/u01/app/oracle/product/19.3.0/dbhome_1/dbs/arch1_323_1119711914.dbf
/u01/app/oracle/product/19.3.0/dbhome_1/dbs/arch1_324_1119711914.dbf
/u01/app/oracle/product/19.3.0/dbhome_1/dbs/arch1_325_1119711914.dbf
/u01/app/oracle/product/19.3.0/dbhome_1/dbs/arch1_326_1119711914.dbf
/u01/app/oracle/product/19.3.0/dbhome_1/dbs/arch1_327_1119711914.dbf
/u01/app/oracle/product/19.3.0/dbhome_1/dbs/arch1_328_1119711914.dbf
/u01/app/oracle/product/19.3.0/dbhome_1/dbs/arch1_329_1119711914.dbf
/u01/app/oracle/product/19.3.0/dbhome_1/dbs/arch1_330_1119711914.dbf
/u01/app/oracle/product/19.3.0/dbhome_1/dbs/arch1_331_1119711914.dbf
/u01/app/oracle/product/19.3.0/dbhome_1/dbs/arch1_332_1119711914.dbf
/u01/app/oracle/product/19.3.0/dbhome_1/dbs/arch1_333_1119711914.dbf
/u01/app/oracle/product/19.3.0/dbhome_1/dbs/arch1_334_1119711914.dbf
/u01/app/oracle/product/19.3.0/dbhome_1/dbs/arch1_335_1119711914.dbf
/u01/app/oracle/product/19.3.0/dbhome_1/dbs/arch1_336_1119711914.dbf
/u01/app/oracle/product/19.3.0/dbhome_1/dbs/arch1_337_1119711914.dbf
/u01/app/oracle/product/19.3.0/dbhome_1/dbs/arch1_338_1119711914.dbf
/u01/app/oracle/product/19.3.0/dbhome_1/dbs/arch1_339_1119711914.dbf
/u01/app/oracle/product/19.3.0/dbhome_1/dbs/arch1_340_1119711914.dbf
/u01/app/oracle/product/19.3.0/dbhome_1/dbs/arch1_341_1119711914.dbf
/u01/app/oracle/product/19.3.0/dbhome_1/dbs/arch1_342_1119711914.dbf
/u01/app/oracle/product/19.3.0/dbhome_1/dbs/arch1_343_1119711914.dbf
/u01/app/oracle/product/19.3.0/dbhome_1/dbs/arch1_344_1119711914.dbf
/u01/app/oracle/product/19.3.0/dbhome_1/dbs/arch1_345_1119711914.dbf
/u01/app/oracle/product/19.3.0/dbhome_1/dbs/arch1_346_1119711914.dbf
/u01/app/oracle/product/19.3.0/dbhome_1/dbs/arch1_347_1119711914.dbf
/u01/app/oracle/product/19.3.0/dbhome_1/dbs/arch1_348_1119711914.dbf
/u01/app/oracle/product/19.3.0/dbhome_1/dbs/arch1_349_1119711914.dbf
/u01/app/oracle/product/19.3.0/dbhome_1/dbs/arch1_350_1119711914.dbf
/u01/app/oracle/product/19.3.0/dbhome_1/dbs/arch1_351_1119711914.dbf
/u01/app/oracle/product/19.3.0/dbhome_1/dbs/arch1_352_1119711914.dbf
/u01/app/oracle/product/19.3.0/dbhome_1/dbs/arch1_353_1119711914.dbf
/u01/app/oracle/product/19.3.0/dbhome_1/dbs/arch1_354_1119711914.dbf
/u01/app/oracle/product/19.3.0/dbhome_1/dbs/arch1_355_1119711914.dbf
/u01/app/oracle/product/19.3.0/dbhome_1/dbs/arch1_356_1119711914.dbf
/u01/app/oracle/product/19.3.0/dbhome_1/dbs/arch1_357_1119711914.dbf
/u01/app/oracle/product/19.3.0/dbhome_1/dbs/arch1_358_1119711914.dbf
/u01/app/oracle/product/19.3.0/dbhome_1/dbs/arch1_359_1119711914.dbf
/u01/app/oracle/product/19.3.0/dbhome_1/dbs/arch1_360_1119711914.dbf
/u01/app/oracle/product/19.3.0/dbhome_1/dbs/arch1_361_1119711914.dbf
/u01/app/oracle/product/19.3.0/dbhome_1/dbs/arch1_362_1119711914.dbf
/u01/app/oracle/product/19.3.0/dbhome_1/dbs/arch1_363_1119711914.dbf
/u01/app/oracle/product/19.3.0/dbhome_1/dbs/arch1_364_1119711914.dbf
/u01/app/oracle/product/19.3.0/dbhome_1/dbs/arch1_365_1119711914.dbf
/u01/app/oracle/product/19.3.0/dbhome_1/dbs/arch1_366_1119711914.dbf
/u01/app/oracle/product/19.3.0/dbhome_1/dbs/arch1_367_1119711914.dbf
NAME
--------------------------------------------------------------------------------
/u01/app/oracle/product/19.3.0/dbhome_1/dbs/arch1_368_1119711914.dbf
/u01/app/oracle/product/19.3.0/dbhome_1/dbs/arch1_369_1119711914.dbf
/u01/app/oracle/product/19.3.0/dbhome_1/dbs/arch1_370_1119711914.dbf
/u01/app/oracle/product/19.3.0/dbhome_1/dbs/arch1_371_1119711914.dbf
/u01/app/oracle/product/19.3.0/dbhome_1/dbs/arch1_372_1119711914.dbf
/u01/app/oracle/product/19.3.0/dbhome_1/dbs/arch1_373_1119711914.dbf
/u01/app/oracle/product/19.3.0/dbhome_1/dbs/arch1_374_1119711914.dbf
/u01/app/oracle/product/19.3.0/dbhome_1/dbs/arch1_375_1119711914.dbf
/u01/app/oracle/product/19.3.0/dbhome_1/dbs/arch1_376_1119711914.dbf
/u01/app/oracle/product/19.3.0/dbhome_1/dbs/arch1_377_1119711914.dbf
/u01/app/oracle/product/19.3.0/dbhome_1/dbs/arch1_378_1119711914.dbf
/u01/app/oracle/product/19.3.0/dbhome_1/dbs/arch1_379_1119711914.dbf
/u01/app/oracle/product/19.3.0/dbhome_1/dbs/arch1_380_1119711914.dbf
/u01/app/oracle/product/19.3.0/dbhome_1/dbs/arch1_381_1119711914.dbf
/u01/app/oracle/product/19.3.0/dbhome_1/dbs/arch1_382_1119711914.dbf
/u01/app/oracle/product/19.3.0/dbhome_1/dbs/arch1_383_1119711914.dbf
/u01/app/oracle/product/19.3.0/dbhome_1/dbs/arch1_384_1119711914.dbf
/u01/app/oracle/product/19.3.0/dbhome_1/dbs/arch1_385_1119711914.dbf
/u01/app/oracle/product/19.3.0/dbhome_1/dbs/arch1_386_1119711914.dbf
/u01/app/oracle/product/19.3.0/dbhome_1/dbs/arch1_387_1119711914.dbf
/u01/app/oracle/product/19.3.0/dbhome_1/dbs/arch1_388_1119711914.dbf
/u01/app/oracle/product/19.3.0/dbhome_1/dbs/arch1_389_1119711914.dbf
/u01/app/oracle/product/19.3.0/dbhome_1/dbs/arch1_390_1119711914.dbf
/u01/app/oracle/product/19.3.0/dbhome_1/dbs/arch1_391_1119711914.dbf
/u01/app/oracle/product/19.3.0/dbhome_1/dbs/arch1_392_1119711914.dbf
/u01/app/oracle/product/19.3.0/dbhome_1/dbs/arch1_393_1119711914.dbf
/u01/app/oracle/product/19.3.0/dbhome_1/dbs/arch1_394_1119711914.dbf
/u01/app/oracle/product/19.3.0/dbhome_1/dbs/arch1_395_1119711914.dbf
/u01/app/oracle/product/19.3.0/dbhome_1/dbs/arch1_396_1119711914.dbf
/u01/app/oracle/product/19.3.0/dbhome_1/dbs/arch1_397_1119711914.dbf
/u01/app/oracle/product/19.3.0/dbhome_1/dbs/arch1_398_1119711914.dbf
/u01/app/oracle/product/19.3.0/dbhome_1/dbs/arch1_399_1119711914.dbf
229 rows selected.
SQL> 5. 归档日志路径及命名
5.1 归档日志默认路径
如果log_archive_dest_n为空,归档日志文件目录为log_archive_dest,
如果log_archive_dest值也是空,则默认的是db_recover_file_dest参数指定的位置.缺省安装后db_recover_file_dest指向flash_recover_area
注意: 另外指定并不意味着你要取消闪回恢复区的参数,因为这个闪回恢复区不仅归档日志,还有RMAN的备份及闪回日志等等.
SQL>
SQL> show parameter archive
NAME TYPE VALUE
------------------------------------ --------------------------------- ------------------------------
archive_lag_target integer 0
log_archive_config string
log_archive_dest string
log_archive_dest_1 string LOCATION=/u01/app/oracle/produ
ct/19.3.0/dbhome_1/dbs/arch
log_archive_dest_10 string
log_archive_dest_11 string
log_archive_dest_12 string
log_archive_dest_13 string
log_archive_dest_14 string
log_archive_dest_15 string
log_archive_dest_16 string
log_archive_dest_17 string
log_archive_dest_18 string
log_archive_dest_19 string
log_archive_dest_2 string
log_archive_dest_20 string
log_archive_dest_21 string
log_archive_dest_22 string
log_archive_dest_23 string
log_archive_dest_24 string
log_archive_dest_25 string
log_archive_dest_26 string
log_archive_dest_27 string
log_archive_dest_28 string
log_archive_dest_29 string
log_archive_dest_3 string
log_archive_dest_30 string
log_archive_dest_31 string
log_archive_dest_4 string
log_archive_dest_5 string
log_archive_dest_6 string
log_archive_dest_7 string
log_archive_dest_8 string
log_archive_dest_9 string
log_archive_dest_state_1 string enable
log_archive_dest_state_10 string enable
log_archive_dest_state_11 string enable
log_archive_dest_state_12 string enable
log_archive_dest_state_13 string enable
log_archive_dest_state_14 string enable
log_archive_dest_state_15 string enable
log_archive_dest_state_16 string enable
log_archive_dest_state_17 string enable
log_archive_dest_state_18 string enable
log_archive_dest_state_19 string enable
log_archive_dest_state_2 string enable
log_archive_dest_state_20 string enable
log_archive_dest_state_21 string enable
log_archive_dest_state_22 string enable
log_archive_dest_state_23 string enable
log_archive_dest_state_24 string enable
log_archive_dest_state_25 string enable
log_archive_dest_state_26 string enable
log_archive_dest_state_27 string enable
log_archive_dest_state_28 string enable
log_archive_dest_state_29 string enable
log_archive_dest_state_3 string enable
log_archive_dest_state_30 string enable
log_archive_dest_state_31 string enable
log_archive_dest_state_4 string enable
log_archive_dest_state_5 string enable
log_archive_dest_state_6 string enable
log_archive_dest_state_7 string enable
log_archive_dest_state_8 string enable
log_archive_dest_state_9 string enable
log_archive_duplex_dest string
log_archive_format string %t_%s_%r.dbf
log_archive_max_processes integer 4
log_archive_min_succeed_dest integer 1
log_archive_start boolean FALSE
log_archive_trace integer 0
SQL>
SQL> show parameter db_recovery
NAME TYPE VALUE
------------------------------------ --------------------------------- ------------------------------
db_recovery_file_dest string /u02/oradata/fra
db_recovery_file_dest_size big integer 20G
SQL> 5.2 指定归档日志路径
log_archive_dest_n
log_archive_dest_n (n:1-10)表示可以有10个目标路径存放归档日志(镜像关系),即可以多路复用10个归档日志的备份.
5.2.1 Location
SQL> alter system set log_archive_dest_1='LOCATION=/u01/app/oracle/product/19.3.0/dbhome_1/dbs/arch';
5.2.2 Location mandatory
SQL> alter system set log_archive_dest_1='LOCATION=/u01/app/oracle/product/19.3.0/dbhome_1/dbs/arch mandatory';
//mandatory 强制归档: 联机日志文件必须要成功归档到这个目录后才能被覆写(联机日志文件是重复使用的文件),在设置时至少有一个本地的(location) 强制(mandatory)归档目录(NOTE)
//默认optional, 即使归档没有成功也可以覆盖联机日志文件.
5.2.3 Service
SQL> alter system set log_archive_dest_2='service=test';
/W/远程备份,把历史日志备份到服务名为test的数据库上.(service 代表远程),配置双机时有用.
5.3 影响归档路径的4个参数
LOG_ARCHIVE_DEST #:指定归档文件存放的路径,该路径只能使用本地磁盘,并且只能为mandatory 主归档.默认为' '
LOG_ARCHIVE_DUPLEX_DEST #:指定归档文件存放的路径,该路径只能使用本地磁盘,并且只能为optional 从归档.默认为' '
LOG_ARCHIVE_DEST_N #:指定归档存放路径,11G中可以提供31条路径.该路径可以使用本地磁盘和网络远端磁盘,默认为' '
DB_RECOVERY_FILE_DEST #:指定闪回恢复区路径, 默认flash recovery area的路径
5.4 归档日志命名方法
log_archive_format
是定义命名格式的,例如使用了下面三个内置符号(模板),其含义是:
%t ,thread# , 日志线程号
%s ,sequence , 日志序列号
%r ,resetlog , 代表数据库的周期
SQL> alter system set log_archive_format ='arch_%t_%r_%s.log' scope=spfile;
6. 在Linux下查看归档进程
[oracle@oracle-db-19c ~]$ ps -ef | grep ora_arc
oracle 2179 1 0 11:48 ? 00:00:00 ora_arc0_cdb1
oracle 2183 1 0 11:48 ? 00:00:04 ora_arc1_cdb1
oracle 2185 1 0 11:48 ? 00:00:00 ora_arc2_cdb1
oracle 2187 1 0 11:48 ? 00:00:00 ora_arc3_cdb1
oracle 53221 51429 0 15:08 pts/7 00:00:00 grep --color=auto ora_arc
[oracle@oracle-db-19c ~]$
ARCn 就是归档进程,这里启动了4个 arc0 ,arc1,arc2,arc3归档进程,最多可达 30 ,由 log_archive_max_processes参数指定.
开放原子开发者工作坊旨在鼓励更多人参与开源活动，与志同道合的开发者们相互交流开发经验、分享开发心得、获取前沿技术趋势。工作坊有多种形式的开发者活动，如meetup、训练营等，主打技术交流，干货满满，真诚地邀请各位开发者共同参与！
更多推荐
2
0- 0
已为社区贡献10条内容
所有评论(0)