postgresql删除归档文件 plsql删除归档日志

SQL> shutdown immediate;
 SQL> startup mount;
 SQL> alter database flashback off;
 SQL> alter database open;

plsql 登录数据库的时候，只有sys用户才能够登录，其他用户登录提示如下信息：

报错信息：archiver error.connect internal only util freed;

以上报错是数据库归档日志满了。。解决方法如图命令。

登录数据库服务器;

方法二：

rman target /

crosscheck archivelog all;

delete noprompt archivelog until time 'sysdate-0.5';  --删除半天前的日志

delete force noprompt archivelog until time 'sysdate-2'; 强制删除

方法三：

参考：http://jingyan.baidu.com/article/e52e3615ade09040c60c5102.html

感谢这位作者。

1、查看归档日志信息

SQL> show parameter log_archive_dest

NAME     TYPE
------------------------------------ ----------- ------------------------------
log_archive_dest
log_archive_dest_1
log_archive_dest_10
log_archive_dest_11
log_archive_dest_12
log_archive_dest_13
log_archive_dest_14
log_archive_dest_15
log_archive_dest_16
log_archive_dest_17
log_archive_dest_18

NAME     TYPE
------------------------------------ ----------- ------------------------------
log_archive_dest_19
log_archive_dest_2
log_archive_dest_20
log_archive_dest_21
log_archive_dest_22
log_archive_dest_23
log_archive_dest_24
log_archive_dest_25
log_archive_dest_26
log_archive_dest_27
log_archive_dest_28

NAME     TYPE
------------------------------------ ----------- -----

2、查看归档日志存放目录

SQL> show parameter recover

NAME     TYPE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest     string
a
db_recovery_file_dest_size
db_unrecoverable_scn_tracking     boolean
recovery_parallelism     integer
SQL>

3、检查flash recovery area的使用情况

SQL> select * from V$FLASH_RECOVERY_AREA_USAGE;
FILE_TYPE
-------------------- ------------------ -------------------------
NUMBER_OF_FILES
---------------
CONTROL FILE    .39 0
REDO LOG      0 0
ARCHIVED LOG      0 0

由于我这里是清理过的，所以是0

FILE_TYPE
-------------------- ------------------ -------------------------
NUMBER_OF_FILES
---------------
BACKUP PIECE      0 0
IMAGE COPY      0 0
FLASHBACK LOG      0 0
FILE_TYPE
-------------------- ------------------ -------------------------
NUMBER_OF_FILES
---------------
FOREIGN ARCHIVED LOG      0 0
7 rows selected.

4、进入到归档日志的存放目录，找到所有归档日志文件并删除。

cd /u01/oradata/fast_recovery_are
rm -rf

5、进入RMAN控制台，检查一些无用的archivelog，删除过期的归档日志。

[oracle@fossdb ~]$ rman target /
Recovery Manager: Release 11.2.0.3.0 - Production on Sun Apr 24 11:56:41 2016
Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.
connected to target database: FOSSDB (DBID=4284647288)
RMAN> crosscheck archivelog all;
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=2046 device type=DISK
validation failed for archived log
RMAN> delete expired archivelog all;  删除所有过期的归档日志
released channel: ORA_DISK_1
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=2046 device type=DISK
List of Archived Log Copies for database with db_unique_name FOSSDB
=====================================================================
Key     Thrd Seq     S Low Time 
------- ---- ------- - ---------
17      1    17846   X 22-APR-16
        Name: /u01/oradata/fast_recovery_area/FOSSDB/archivelog/2016_04_22/o1_mf_1_17846_ckmn7j3m_.arc
18      1    17847   X 22-APR-16

6、再次检查flash recovery area的使用情况

SQL>select * from V$FLASH_RECOVERY_AREA_USAGE

启动或关闭归档日志：

1.管理员身份连接数据库

2.查看当前归档模式，是归档还是非归档

SQL> archive log list;
Database log mode              No Archive Mode
Automatic archival             Disabled
Archive destination            USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     228
Current log sequence           230

3.关闭数据库

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.

4.启动数据库到mount状态

SQL> startup mount;
ORACLE instance started.

Total System Global Area 3423965184 bytes
Fixed Size                  2180544 bytes
Variable Size            2013268544 bytes
Database Buffers         1392508928 bytes
Redo Buffers               16007168 bytes
Database mounted.

5.启动归档模式

SQL> alter database archivelog;

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     228
Next log sequence to archive   230
Current log sequence           230

6.启动数据库

SQL> alter database open;

Database altered.

7.关闭归档模式

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.
Total System Global Area 3423965184 bytes
Fixed Size                  2180544 bytes
Variable Size            2013268544 bytes
Database Buffers         1392508928 bytes
Redo Buffers               16007168 bytes
Database mounted.
SQL> alter database noarchivelog;
Database altered.

SQL> archive log list;//查看归档状态
Database log mode              No Archive Mode
Automatic archival             Disabled
Archive destination            USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     228
Current log sequence           230
SQL>

本文转自aaa超超aaa 51CTO博客，原文链接:http://blog.51cto.com/10983441/1762953