I come, I see, I conquer

                    —Gaius Julius Caesar
  博客园 :: 首页 :: 新随笔 :: 联系 :: 订阅 :: 管理 ::
ora-00257：archiver error. Connect internal only, until freed.

一、查看使用情况

select * from v$recovery_file_dest;

select * from v$flash_recovery_area_usage;

二、rman删除归档日志

通过rman手动删除归档日志，释放归档空间：
> rman target /
RMAN> DELETE ARCHIVELOG ALL COMPLETED BEFORE 'SYSDATE-7'; ---删除7天前的所有归档日志
RMAN> CROSCHECK ARCHIVELOG ALL;
RMAN> DELETE EXPIRED ARCHIVELOG ALL;

 

操作实例：

Connecting to 127.0.0.1:62089...
Connection established.
To escape to local shell, press 'Ctrl+Alt+]'.

Last login: Mon Feb 17 16:26:59 2020 from 10.137.137.4
[root@jgdb ~]# su - oracle
[oracle@jgdb ~]$
[oracle@jgdb ~]$
[oracle@jgdb ~]$
[oracle@jgdb ~]$
[oracle@jgdb ~]$ sqlplus /nolog

SQL*Plus: Release 10.2.0.4.0 - Production on Mon Feb 17 16:45:11 2020

Copyright (c) 1982, 2007, Oracle. All Rights Reserved.

idle> exit
[oracle@jgdb ~]$ env
HOSTNAME=jgdb
SHELL=/bin/bash
TERM=xterm
HISTSIZE=1000
NLS_LANG=AMERICAN_AMERICA.ZHS16GBK
USER=oracle
LS_COLORS=no=00:fi=00:di=00;34:ln=00;36:pi=40;33:so=00;35:bd=40;33;01:cd=40;33;01:or=01;05;37;41:mi=01;05;37;41:ex=00;32:*.cmd=00;32:*.exe=00;32:*.com=00;32:*.btm=00;32:*.bat=00;32:*.sh=00;32:*.csh=00;32:*.tar=00;31:*.tgz=00;31:*.arj=00;31:*.taz=00;31:*.lzh=00;31:*.zip=00;31:*.z=00;31:*.Z=00;31:*.gz=00;31:*.bz2=00;31:*.bz=00;31:*.tz=00;31:*.rpm=00;31:*.cpio=00;31:*.jpg=00;35:*.gif=00;35:*.bmp=00;35:*.xbm=00;35:*.xpm=00;35:*.png=00;35:*.tif=00;35:
ORACLE_SID=ORCL
ORACLE_BASE=/s01
MAIL=/var/spool/mail/oracle
PATH=/usr/kerberos/bin:/usr/local/bin:/bin:/usr/bin:/home/oracle/bin:/s01/10.2.0/db/bin:/s01/10.2.0/db/OPatch:/s01/10.2.0/db/bin/OPatch
INPUTRC=/etc/inputrc
PWD=/home/oracle
LANG=zh_CN.UTF-8
ORACLE_TERM=xterm
SQLPATH=/home/oracle/
SSH_ASKPASS=/usr/libexec/openssh/gnome-ssh-askpass
SHLVL=1
HOME=/home/oracle
LOGNAME=oracle
CVS_RSH=ssh
LESSOPEN=|/usr/bin/lesspipe.sh %s
ORACLE_HOME=/s01/10.2.0/db
G_BROKEN_FILENAMES=1
_=/bin/env
[oracle@jgdb ~]$ sqplus /nolog
-bash: sqplus: command not found
[oracle@jgdb ~]$ sqlplus /nolog

SQL*Plus: Release 10.2.0.4.0 - Production on Mon Feb 17 16:45:38 2020

Copyright (c) 1982, 2007, Oracle. All Rights Reserved.

idle> conn / as sysdba;
Connected.
sys@ORCL>
sys@ORCL> select * from V$FLASH_RECOVERY_AREA_USAGE;

FILE_TYPE PERCENT_SPACE_USED PERCENT_SPACE_RECLAIMABLE NUMBER_OF_FILES
------------------------ ------------------ ------------------------- ---------------
CONTROLFILE 0 0 0
ONLINELOG 0 0 0
ARCHIVELOG 99.76 0 266
BACKUPPIECE 0 0 0
IMAGECOPY 0 0 0
FLASHBACKLOG .2 .15 4

6 rows selected.

Elapsed: 00:00:00.14
sys@ORCL> select sum(percent_space_used)*3/100 from v$flash_recovery_area_usage;

SUM(PERCENT_SPACE_USED)*3/100
-----------------------------
2.9988

Elapsed: 00:00:00.03
sys@ORCL> show parameter recover;

NAME TYPE VALUE
------------------------------------ ---------------------- ------------------------------
db_recovery_file_dest string /oradata/flash_recovery_area
db_recovery_file_dest_size big integer 30G
recovery_parallelism integer 0
sys@ORCL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@jgdb ~]$ exit
logout
[root@jgdb ~]#
[root@jgdb ~]# cd /oradata/flash_recovery_area
[root@jgdb flash_recovery_area]# ls
ORCL
[root@jgdb flash_recovery_area]# cd ORCL
[root@jgdb ORCL]# ls
archivelog flashback onlinelog
[root@jgdb ORCL]# cd archivelog
[root@jgdb archivelog]# ls
2017_10_24 2017_12_23 2018_02_20 2018_04_18 2018_06_12 2018_08_05 2018_09_18 2018_11_12 2019_01_08 2019_03_10 2019_05_13 2019_07_22 2019_09_28 2019_12_01 2020_01_26
2017_06_26 2017_08_28 2017_10_26 2017_12_25 2018_02_22 2018_04_19 2018_06_14 2018_08_06 2018_09_20 2018_11_14 2019_01_10 2019_03_12 2019_05_15 2019_07_25 2019_10_01 2019_12_03 2020_01_27
2017_06_28 2017_08_30 2017_10_29 2017_12_27 2018_02_24 2018_04_21 2018_06_16 2018_08_08 2018_09_22 2018_11_16 2019_01_12 2019_03_14 2019_05_18 2019_07_27 2019_10_03 2019_12_05 2020_01_28
2017_06_30 2017_09_01 2017_10_31 2017_12_29 2018_02_26 2018_04_23 2018_06_18 2018_08_10 2018_09_24 2018_11_19 2019_01_14 2019_03_17 2019_05_20 2019_07_29 2019_10_05 2019_12_06 2020_01_29
2017_07_03 2017_09_04 2017_11_01 2018_01_01 2018_03_01 2018_04_25 2018_06_20 2018_08_12 2018_09_26 2018_11_20 2019_01_16 2019_03_19 2019_05_23 2019_08_01 2019_10_08 2019_12_07 2020_01_30

[root@jgdb archivelog]# rm 2017_04_30 -r
rm ? y
[root@jgdb archivelog]#
[root@jgdb archivelog]# ls
2017_05_01 2018_03_01 2018_04_25 2018_06_20 2018_08_12 2018_09_26 2018_11_20 2019_01_16 2019_03_19 2019_05_23 2019_08_01 2019_10_08 2019_12_07 2020_01_30
2017_07_05 2017_09_05 2017_11_03 2018_01_02 2018_03_03 2018_04_27 2018_06_22 2018_08_14 2018_09_28 2018_11_22 2019_01_18 2019_03_21 2019_05_25 2019_08_04 2019_10_09 2019_12_10 2020_01_31
[root@jgdb archivelog]# rm 2017_05_* -r
rm 2017_05_01?. y
rm 2017_05_02?. y
rm 2017_05_05?. y
rm 2017_05_09?. y
[root@jgdb archivelog]# y
-bash: y: command not found
[root@jgdb archivelog]# y
-bash: y: command not found
[root@jgdb archivelog]# y
-bash: y: command not found
[root@jgdb archivelog]#
[root@jgdb archivelog]# ls
... 2018_03_20 2018_05_11 2018_07_03 2018_08_23 2018_10_05 2018_11_28 2019_01_22 2019_03_22 2019_05_25 2019_08_01 2019_10_05 2019_12_05 2020_01_27
[root@jgdb archivelog]# rm 2017_06_* -rf
[root@jgdb archivelog]# rm 2017_07_* -rf
[root@jgdb archivelog]# rm 2017_08_* -rf
[root@jgdb archivelog]# rm 2018_*_* -rf
[root@jgdb archivelog]# rm 2017_*_* -rf
[root@jgdb archivelog]# ls
...2019_03_10 2019_04_05 2019_05_01 2019_05_30 2019_06_27 2019_07_27 2019_08_25 2019_09_23 2019_10_18 2019_11_13 2019_12_07 2020_01_02 2020_01_25 2020_02_06
2019_01_20 2019_02_15 2019_03_12 2019_04_07 2019_05_03 2019_06_02 2019_06_30 2019_07_29 2019_08_27 2019_09_24 2019_10_20 2019_11_15 2019_12_10 2020_01_05 2020_01_26 2020_02_07
2019_01_22 2019_02_17 2019_03_14 2019_04_09 2019_05_06 2019_06_04 2019_07_02 2019_08_01 2019_08_30 2019_09_27 2019_10_22 2019_11_18 2019_12_12 2020_01_07 2020_01_27 2020_02_08
2019_01_24 2019_02_19 2019_03_17 2019_04_11 2019_05_08 2019_06_05 2019_07_04 2019_08_04 2019_09_02 2019_09_28 2019_10_24 2019_11_20 2019_12_14 2020_01_09 2020_01_28 2020_02_09
[root@jgdb archivelog]#
[root@jgdb archivelog]#

 


从操作系统层面删除归档日志文件后，查询空间情况：

[root@jgdb ~]# su - oracle
[oracle@jgdb ~]$ sqlplus /nolog

SQL*Plus: Release 10.2.0.4.0 - Production on Mon Feb 17 16:55:08 2020

Copyright (c) 1982, 2007, Oracle. All Rights Reserved.

idle> conn / as sysdba;
Connected.
sys@ORCL> select sum(percent_space_used)*3/100 from v$flash_recovery_area_usage;

SUM(PERCENT_SPACE_USED)*3/100
-----------------------------
2.9988

Elapsed: 00:00:00.01
sys@ORCL> select * from V$FLASH_RECOVERY_AREA_USAGE;

FILE_TYPE PERCENT_SPACE_USED PERCENT_SPACE_RECLAIMABLE NUMBER_OF_FILES
------------------------ ------------------ ------------------------- ---------------
CONTROLFILE 0 0 0
ONLINELOG 0 0 0
ARCHIVELOG 99.76 0 266
BACKUPPIECE 0 0 0
IMAGECOPY 0 0 0
FLASHBACKLOG .2 .15 4

6 rows selected.


Elapsed: 00:00:00.01
sys@ORCL>
sys@ORCL>
sys@ORCL>
sys@ORCL>
sys@ORCL> select sum(percent_space_used)*3/100 from v$flash_recovery_area_usage;

SUM(PERCENT_SPACE_USED)*3/100
-----------------------------
2.9988

Elapsed: 00:00:00.01
sys@ORCL>

结果是：空间没有改变

 

使用rman命令更新有关的文件中的空间相关配置

[root@jgdb archivelog]# cd ..
[root@jgdb ORCL]# su - oracle
[oracle@jgdb ~]$ rman target /

Recovery Manager: Release 10.2.0.4.0 - Production on Mon Feb 17 16:58:43 2020

Copyright (c) 1982, 2007, Oracle. All rights reserved.

connected to target database: ORCL (DBID=259132462)

RMAN>

RMAN> crosscheck backup;

using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=2180 devtype=DISK

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
no obsolete backups found

RMAN> delete expired backup;

using channel ORA_DISK_1

RMAN> crosscheck archivelog all;

released channel: ORA_DISK_1
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=2180 devtype=DISK
validation failed for archived log
archive log filename=/oradata/flash_recovery_area/ORCL/archivelog/2018_09_07/o1_mf_1_512_fs42ttlt_.arc recid=276 stamp=986217821
validation failed for archived log
...
archive log filename=/oradata/flash_recovery_area/ORCL/archivelog/2018_12_27/o1_mf_1_572_g28dl9pf_.arc recid=336 stamp=995968843
validation failed for archived log
archive log filename=/oradata/flash_recovery_area/ORCL/archivelog/2018_12_28/o1_mf_1_573_g2dcg3xz_.arc recid=337 stamp=996098757
validation failed for archived log
archive log filename=/oradata/flash_recovery_area/ORCL/archivelog/2018_12_31/o1_mf_1_574_g2ly2srn_.arc recid=338 stamp=996314459
validation succeeded for archived log
archive log filename=/oradata/flash_recovery_area/ORCL/archivelog/2019_01_02/o1_mf_1_575_g2qwsc3c_.arc recid=339 stamp=996476973
validation succeeded for archived log
...
Crosschecked 266 objects


RMAN> delete expired archivelog all;

released channel: ORA_DISK_1
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=2180 devtype=DISK

List of Archived Log Copies
Key Thrd Seq S Low Time Name
------- ---- ------- - --------- ----
276 1 512 X 05-SEP-18 /oradata/flash_recovery_area/ORCL/archivelog/2018_09_07/o1_mf_1_512_fs42ttlt_.arc
277 1 513 X 07-SEP-18 /oradata/flash_recovery_area/ORCL/archivelog/2018_09_13/o1_mf_1_513_fsmmgz5y_.arc
278 1 514 X 08-SEP-18 /oradata/flash_recovery_area/ORCL/archivelog/2018_09_13/o1_mf_1_514_fsmmh1jl_.arc
279 1 515 X 10-SEP-18 /oradata/flash_recovery_area/ORCL/archivelog/2018_09_13/o1_mf_1_515_fsmmh1xt_.arc
280 1 516 X 13-SEP-18 /oradata/flash_recovery_area/ORCL/archivelog/2018_09_13/o1_mf_1_516_fsmnodn5_.arc
281 1 517 X 13-SEP-18 /oradata/flash_recovery_area/ORCL/archivelog/2018_09_13/o1_mf_1_517_fsnvc8x8_.arc
282 1 518 X 13-SEP-18 /oradata/flash_recovery_area/ORCL/archivelog/2018_09_15/o1_mf_1_518_fsrcv6tt_.arc
283 1 519 X 15-SEP-18 /oradata/flash_recovery_area/ORCL/archivelog/2018_09_17/o1_mf_1_519_fsyxbzpo_.arc
284 1 520 X 17-SEP-18 /oradata/flash_recovery_area/ORCL/archivelog/2018_09_18/o1_mf_1_520_ft0f8xwo_.arc
285 1 521 X 18-SEP-18 /oradata/flash_recovery_area/ORCL/archivelog/2018_09_20/o1_mf_1_521_ft6nwsgk_.arc
286 1 522 X 20-SEP-18 /oradata/flash_recovery_area/ORCL/archivelog/2018_09_22/o1_mf_1_522_ftbyyzp5_.arc
287 1 523 X 22-SEP-18 /oradata/flash_recovery_area/ORCL/archivelog/2018_09_24/o1_mf_1_523_ftkvh88o_.arc
288 1 524 X 24-SEP-18 /oradata/flash_recovery_area/ORCL/archivelog/2018_09_26/o1_mf_1_524_ftpp5r1s_.arc
...

Do you really want to delete the above objects (enter YES or NO)? y
deleted archive log
archive log filename=/oradata/flash_recovery_area/ORCL/archivelog/2018_09_07/o1_mf_1_512_fs42ttlt_.arc recid=276 stamp=986217821
deleted archive log
archive log filename=/oradata/flash_recovery_area/ORCL/archivelog/2018_09_13/o1_mf_1_513_fsmmgz5y_.arc recid=277 stamp=986726369
deleted archive log
archive log filename=/oradata/flash_recovery_area/ORCL/archivelog/2018_09_13/o1_mf_1_514_fsmmh1jl_.arc recid=278 stamp=986726373
deleted archive log
archive log filename=/oradata/flash_recovery_area/ORCL/archivelog/2018_09_13/o1_mf_1_515_fsmmh1xt_.arc recid=279 stamp=986726373
deleted archive log
archive log filename=/oradata/flash_recovery_area/ORCL/archivelog/2018_09_13/o1_mf_1_516_fsmnodn5_.arc recid=280 stamp=986727599
deleted archive log
archive log filename=/oradata/flash_recovery_area/ORCL/archivelog/2018_09_13/o1_mf_1_517_fsnvc8x8_.arc recid=281 stamp=986767211
deleted archive log
...
Deleted 63 EXPIRED objects


RMAN>


再一次查看空间变化情况：

sys@ORCL> select sum(percent_space_used)*3/100 from v$flash_recovery_area_usage;

SUM(PERCENT_SPACE_USED)*3/100
-----------------------------
2.3223

Elapsed: 00:00:00.21
sys@ORCL> select * from V$FLASH_RECOVERY_AREA_USAGE;

FILE_TYPE PERCENT_SPACE_USED PERCENT_SPACE_RECLAIMABLE NUMBER_OF_FILES
------------------------ ------------------ ------------------------- ---------------
CONTROLFILE 0 0 0
ONLINELOG 0 0 0
ARCHIVELOG 77.54 0 207
BACKUPPIECE 0 0 0
IMAGECOPY 0 0 0
FLASHBACKLOG .45 0 9

6 rows selected.

Elapsed: 00:00:00.49
sys@ORCL>

空间释放正常

 

简单操作：

rman target /
crosscheck archivelog all;
delete archivelog all;

或者

先在操作系统上删除log物理文件，然后再执行以下命令

rman target /
crosscheck archivelog all;
delete expired archivelog all;

posted on 2018-09-13 16:20  jcsu  阅读(251)  评论(0)    收藏  举报