Oracle归档开启、关闭及清理，添加定时任务

约定不等于承诺〃 2022-03-18 01:56 596阅读 0赞

**目录**

一、闪回区耗尽，导致无法启动

二、删除归档日志

三、oracle重启

四、更改和查看归档

五、Oracle开启和关闭归档

六、定时清理归档

--------------------

### 一、闪回区耗尽，导致无法启动 ###

\[oracle@ZCB1ZNFS01 ~\]$ sqlplus  
SQL\*Plus: Release 11.2.0.4.0 Production on Mon Feb 18 17:59:28 2019  
Copyright (c) 1982, 2013, Oracle.  All rights reserved.  
Enter user-name: znfx  
Enter password:   
ERROR:  
**ORA-00257: archiver error. Connect internal only, until freed.**

或者  
**ORA-01034: ORACLE not available  
ORA-27101: shared memory realm does not exist**  
Linux-x86\_64 Error: 2: No such file or directory  
Process ID: 0  
Session ID: 0 Serial number: 0

\[oracle@ZCB1ZNFS01 ~\]$ sqlplus /as sysdba  
Connected to an idle instance.  
SQL> **startup mount;**  
ORACLE instance started.  
Total System Global Area 1068937216 bytes  
……  
Database mounted.  
SQL> **alter database open;**  
alter database open  
\*  
第 1 行出现错误:  
ORA-03113: 通信通道的文件结尾  
进程 ID: **9428**  
会话 ID: 63 序列号: 5  
  
根据进程ID查找对应日志文件D:\\oracle\\diag\\rdbms\\orcl\\orcl\\trace\\orcl\_ora\_**9428**.trc  
ORA-19815: 警告: db\_recovery\_file\_dest\_size 字节 (共 4039114752 字节) 已使用 100.00%, 尚有 0 字节可用。  
\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*  
You have following choices to free up space from recovery area:  
1. Consider changing RMAN RETENTION POLICY. If you are using Data Guard,  
   then consider changing RMAN ARCHIVELOG DELETION POLICY.  
2. Back up files to tertiary device such as tape using RMAN  
   BACKUP RECOVERY AREA command.  
3. Add disk space and increase db\_recovery\_file\_dest\_size parameter to  
   reflect the new space.  
4. Delete unnecessary files using RMAN DELETE command. If an operating  
   system command was used to delete files, then use RMAN CROSSCHECK and

### 二、删除归档日志 ###

\[oracle@ZCB1ZNFS01 ~\]$ **rm -r /home/db/oracle/fast\_recovery\_area/ZNFX/archivelog/2019\_02\_18**

\[oracle@ZCB1ZNFS01 ~\]$ **rman target /**

Recovery Manager: Release 11.2.0.4.0 - Production on Mon Feb 18 18:04:23 2019

connected to target database: ZNFX (DBID=4024943118)

RMAN> **crosscheck archivelog all;**

using target database control file instead of recovery catalog  
allocated channel: ORA\_DISK\_1  
channel ORA\_DISK\_1: SID=80 device type=DISK  
validation succeeded for archived log  
archived log file name=/home/db/oracle/fast\_recovery\_area/ZNFX/archivelog/2019\_02\_18/o1\_mf\_1\_6363\_g6ny7kb5\_.arc RECID=6357 STAMP=1000574449  
validation succeeded for archived log  
archived log file name=/home/db/oracle/fast\_recovery\_area/ZNFX/archivelog/2019\_02\_18/o1\_mf\_1\_6364\_g6ny7k96\_.arc RECID=6356 STAMP=1000574449  
validation succeeded for archived log

……

archived log file name=/home/db/oracle/fast\_recovery\_area/ZNFX/archivelog/2019\_02\_18/o1\_mf\_1\_6591\_g6o018y6\_.arc RECID=6584 STAMP=1000576297  
Crosschecked 229 objects  
RMAN> **delete expired archivelog all**  
6576    1    6583    X 18-FEB-19  
        Name: /home/db/oracle/fast\_recovery\_area/ZNFX/archivelog/2019\_02\_18/o1\_mf\_1\_6583\_g6nzqd26\_.arc

6577    1    6584    X 18-FEB-19  
        Name: /home/db/oracle/fast\_recovery\_area/ZNFX/archivelog/2019\_02\_18/o1\_mf\_1\_6584\_g6nzqfdm\_.arc

6578    1    6585    X 18-FEB-19  
        Name: /home/db/oracle/fast\_recovery\_area/ZNFX/archivelog/2019\_02\_18/o1\_mf\_1\_6585\_g6nzqlo2\_.arc

6579    1    6586    X 18-FEB-19  
        Name: /home/db/oracle/fast\_recovery\_area/ZNFX/archivelog/2019\_02\_18/o1\_mf\_1\_6586\_g6nzqpyg\_.arc

6580    1    6587    X 18-FEB-19  
        Name: /home/db/oracle/fast\_recovery\_area/ZNFX/archivelog/2019\_02\_18/o1\_mf\_1\_6587\_g6nzqv6z\_.arc

6581    1    6588    X 18-FEB-19  
        Name: /home/db/oracle/fast\_recovery\_area/ZNFX/archivelog/2019\_02\_18/o1\_mf\_1\_6588\_g6nzr0cn\_.arc

6582    1    6589    X 18-FEB-19  
        Name: /home/db/oracle/fast\_recovery\_area/ZNFX/archivelog/2019\_02\_18/o1\_mf\_1\_6589\_g6nzr3x8\_.arc

6583    1    6590    X 18-FEB-19  
        Name: /home/db/oracle/fast\_recovery\_area/ZNFX/archivelog/2019\_02\_18/o1\_mf\_1\_6590\_g6nzvqsf\_.arc

6584    1    6591    X 18-FEB-19  
        Name: /home/db/oracle/fast\_recovery\_area/ZNFX/archivelog/2019\_02\_18/o1\_mf\_1\_6591\_g6o018y6\_.arc

Do you really want to delete the above objects (enter YES or NO)? **YES**  
deleted archived log  
archived log file name=/home/db/oracle/fast\_recovery\_area/ZNFX/archivelog/2019\_02\_18/o1\_mf\_1\_6363\_g6ny7kb5\_.arc RECID=6357 STAMP=1000574449  
deleted archived log  
archived log file name=/home/db/oracle/fast\_recovery\_area/ZNFX/archivelog/2019\_02\_18/o1\_mf\_1\_6364\_g6ny7k96\_.arc RECID=6356 STAMP=1000574449  
deleted archived log  
……

archived log file name=/home/db/oracle/fast\_recovery\_area/ZNFX/archivelog/2019\_02\_18/o1\_mf\_1\_6591\_g6o018y6\_.arc RECID=6584 STAMP=1000576297  
Deleted 229 EXPIRED objects

### 三、oracle重启 ###

sqlplus  /as sysdba  
SQL> shutdown immediate;  
SQL> startup;

### 四、更改和查看归档 ###

更改大小

SQL> alter system set db\_recovery\_file\_dest\_size=2G;

查看使用情况

SQL> select \* from v$flash\_recovery\_area\_usage;

### 五、Oracle开启和关闭归档 ###

1.登录  
sqlplus sys/sys as sysdba  
2.查看当前归档模式  
archive log list;  
查看归档状态  
select log\_mode from v$database;  
查看归档使用情况  
select \* from v$flash\_recovery\_area\_usage;  
3.关闭数据库  
shutdown immediate;  
4.启动数据库到mount状态  
startup mount;  
5.启动归档模式  
alter database archivelog;  
关闭归档模式  
alter database noarchivelog;  
6.启动数据库  
alter database open;

\[oracle@ZCD2GBAD01 ~\]$ **sqlplus sys/sys as sysdba**

SQL\*Plus: Release 11.2.0.4.0 Production on Thu Feb 28 17:36:19 2019

Connected to:  
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production  
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> **archive log list;**  
Database log mode        No Archive Mode  
Automatic archival        Disabled  
Archive destination        USE\_DB\_RECOVERY\_FILE\_DEST  
Oldest online log sequence     6509  
Current log sequence        6511  
SQL> **shutdown immediate;**  
Database closed.  
Database dismounted.  
ORACLE instance shut down.  
SQL> **startup mount;**  
ORACLE instance started.

Total System Global Area 1068937216 bytes  
Fixed Size      2260088 bytes  
Variable Size    671089544 bytes  
Database Buffers   390070272 bytes  
Redo Buffers      5517312 bytes  
Database mounted.  
SQL> **alter database archivelog;**

Database altered.

SQL> **alter database open;**

Database altered.

SQL> **archive log list;**  
Database log mode        Archive Mode  
Automatic archival        Enabled  
Archive destination        USE\_DB\_RECOVERY\_FILE\_DEST  
Oldest online log sequence     6509  
Next log sequence to archive   6511  
Current log sequence        6511

### 六、定时清理归档 ###

**清理文档**

filename=/home/db/oracle/clear/log/del\_arch$(date +%Y-%m-%d).log  
echo "-----------start-------------------">$filename  
$\{ORACLE\_HOME\}/bin/rman nocatalog log=$filename target / <<EOF  
crosscheck archivelog all;  
delete noprompt expired archivelog all;  
delete noprompt archivelog all completed before 'sysdate-2';  
exit;  
EOF  
echo "-----------end-------------------">>$filename

说明:  
$ORACLE\_HOME/bin/rman target / < 等同于 $ORACLE\_HOME/bin/rman <connect target /  
delete noprompt archivelog all completed before 'sysdate-2'; 等同于 delete noprompt archivelog until time 'sysdate-2';

**赋予可执行权限**

\[root@localhost cron\]\# ./cron.sh  
\-bash: ./cron.sh: 权限不够  
\[root@localhost cron\]\# chmod +x cron.sh  
\[root@localhost cron\]\# ./cron.sh

**定时任务**

\[root@localhost ~\]\# **cat /etc/crontab**  
SHELL=/bin/bash  
PATH=/sbin:/bin:/usr/sbin:/usr/bin  
MAILTO=root

\# For details see man 4 crontabs

\# Example of job definition:  
\# .---------------- minute (0 - 59)  
\# |  .------------- hour (0 - 23)  
\# |  |  .---------- day of month (1 - 31)  
\# |  |  |  .------- month (1 - 12) OR jan,feb,mar,apr ...  
\# |  |  |  |  .---- day of week (0 - 6) (Sunday=0 or 7) OR sun,mon,tue,wed,thu,fri,sat  
\# |  |  |  |  |  
\# \*  \*  \*  \*  \* user-name  command to be executed

**添加测试脚本**

filename=/opt/cron/log/$(date -d "today" +"%Y-%m-%d\_%H:%M:%S").log  
echo "-----------start-------------------">$filename  
echo $filename  
echo "-----------end-------------------">>$filename

**添加定时任务**

\[root@localhost ~\]\# crontab -e

\* \* \* \* \* /opt/cron/cron.sh

**列出所有定时任务**

\[root@localhost ~\]\# crontab -l  
\* \* \* \* \* /opt/cron/cron.sh

\#每分钟执行一次

**清空定时任务**

\[root@localhost ~\]\# crontab -r  
您在 /var/spool/mail/root 中有新邮件  
\[root@localhost ~\]\# crontab -l  
no crontab for root

**/etc/crontab文件和crontab -e命令区别**

修改/etc/crontab这种方法只有root用户能用  
crontab -e这种所有用户都可以使用,然后自动写入/var/spool/cron/usename