oracle归档路径设置（闪回区和归档目录）

最新推荐文章于 2024-08-08 19:23:51 发布

脑子进水养啥鱼？

最新推荐文章于 2024-08-08 19:23:51 发布

阅读量8.5k

点赞数 1

分类专栏： Oracle 一些参数的调整

本文链接：https://blog.csdn.net/songyundong1993/article/details/85050903

版权

Oracle 一些参数的调整专栏收录该内容

5 篇文章 0 订阅

订阅专栏

oracle数据库开启归档模式后

使用闪回区需先设置其大小和路径：
alter system set db_recovery_file_dest_size=2G scope=both;
alter system set db_recovery_file_dest='/archivelog';

设置归档路径和闪回区同时保留归档日志：
alter system set log_archive_dest_1='location=/arch';
alter system set log_archive_dest_10='LOCATION=USE_DB_RECOVERY_FILE_DEST';

设置归档路径保留归档日志，闪回区不保留：
alter system set log_archive_dest_1='location=/arch';
alter system set log_archive_dest_10='';

设置归档路径不保留归档日志，闪回区保留：
alter system set log_archive_dest_1='';
alter system set log_archive_dest_10='LOCATION=USE_DB_RECOVERY_FILE_DEST';
-----------------------------------------------------------------------------------
不可设置归档路径和闪回区都不保留归档日志：
alter system set log_archive_dest_1='';
alter system set log_archive_dest_10='';

试验机测试结果为切切几次归档后，卡住切不了归档：

SQL> alter system switch logfile;
System altered.
SQL> /
System altered.
SQL> /
日志报错为：
ALTER SYSTEM SET log_archive_dest_1='' SCOPE=BOTH;
ALTER SYSTEM SET log_archive_dest_10='' SCOPE=BOTH;
Fri Nov 09 07:45:54 2018
Thread 1 advanced to log sequence 20 (LGWR switch)
Current log# 4 seq# 20 mem# 0: /u01/app/oracle/oradata/syd/redo04.log
Fri Nov 09 07:45:54 2018
ARCH: Archival stopped, error occurred. Will continue retrying
ORACLE Instance syd - Archival Error
ORA-16014: log 3 sequence# 19 not archived, no available destinations
ORA-00312: online log 3 thread 1: '/u01/app/oracle/oradata/syd/redo03.log'
Thread 1 advanced to log sequence 21 (LGWR switch)
Current log# 1 seq# 21 mem# 0: /u01/app/oracle/oradata/syd/redo01.log
Fri Nov 09 07:46:02 2018
Archiver process freed from errors. No longer stopped
Fri Nov 09 07:46:34 2018
Thread 1 cannot allocate new log, sequence 22
Checkpoint not complete
Current log# 1 seq# 21 mem# 0: /u01/app/oracle/oradata/syd/redo01.log
Thread 1 advanced to log sequence 22 (LGWR switch)
Current log# 2 seq# 22 mem# 0: /u01/app/oracle/oradata/syd/redo02.log
Fri Nov 09 07:46:44 2018
ORACLE Instance syd - Cannot allocate log, archival required
Thread 1 cannot allocate new log, sequence 23
All online logs need archiving
Examine archive trace files for archiving errors
Current log# 2 seq# 22 mem# 0: /u01/app/oracle/oradata/syd/redo02.log
Fri Nov 09 07:51:11 2018
ARCH: Archival stopped, error occurred. Will continue retrying
ORACLE Instance syd - Archival Error
ORA-16014: log 3 sequence# 19 not archived, no available destinations
ORA-00312: online log 3 thread 1: '/u01/app/oracle/oradata/syd/redo03.log'
重启实例后恢复正常，此时归档被改回保留在闪回区：
SQL> shu immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
Total System Global Area 1068937216 bytes
Fixed Size 2260088 bytes
Variable Size 884999048 bytes
Database Buffers 176160768 bytes
Redo Buffers 5517312 bytes
Database mounted.
Database opened.
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 19
Next log sequence to archive 22
Current log sequence 22
SQL> alter system switch logfile;
System altered.
SQL> /
System altered.
SQL> /
System altered.
SQL> /
System altered.
SQL>