當前位置：首頁 > 运维知识 > 数据库 >内容正文

数据库

ORA-00257+mysql_ORA-00257错误的解决办法

發布時間：2023/12/4 数据库 22 豆豆

生活随笔收集整理的這篇文章主要介紹了 ORA-00257+mysql_ORA-00257错误的解决办法小編覺得挺不錯的,現在分享給大家,幫大家做個參考.

author： headsen ?chen

date：?2018-04-17 ?11:12:39

notice：個人原創，轉載請注明作者和出處，否則依法追擊法律責任。

1，oracle數據庫正常使用中，突然報ora-00257的錯誤，原因是歸檔日志滿了，達到了oracle設置的滿值的狀態了，所以會無法連接了。

2，system登錄數據庫查看使用率和歸檔文件目錄

SQL> show parameter log_archive_dest;

NAME TYPE VALUE------------------------------------ ----------- ------------------------------log_archive_dest string

log_archive_dest_1 string

log_archive_dest_10 string

log_archive_dest_2 string

log_archive_dest_3 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

NAME TYPE VALUE------------------------------------ ----------- ------------------------------log_archive_dest_state_1 string enable

log_archive_dest_state_10 string enable

log_archive_dest_state_2 string enable

log_archive_dest_state_3 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

SQL>

3,?archive log list;檢查一下歸檔目錄和log sequence ,此時在序號512之前的都可以刪除。

SQL>archive log list;

Database log mode Archive Mode

Automatic archival Enabled

Archive destination USE_DB_RECOVERY_FILE_DEST

Oldest online log sequence 512

Next log sequence to archive 514

Current log sequence 514

4，檢查flash recovery area的使用情況，查看archivelog使用率：99% (這里因為已經清理過了，沒來得及截圖，第一次清理時有99%)

SQL> 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

ARCHIVELOG43.73 0 215BACKUPPIECE 0 0 0

IMAGECOPY 0 0 0

FLASHBACKLOG .3 .22 4

6 rows selected.

上圖中，說明歸檔日志滿了，達到43.73%，需要清除過期的歸檔日志。

5，計算flash recovery area已經占用的空間 (已經清理過。沒清理之前是2.99)

SQL> ?select sum(percent_space_used)*3/100 from v$flash_recovery_area_usage;

SUM(PERCENT_SPACE_USED)*3/100

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

1.3209

6，找到recovery的目錄

SQL> show parameter recover;

NAME TYPE VALUE

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

db_recovery_file_dest string /oracle/flash_recovery_area

db_recovery_file_dest_size big integer 20G

recovery_parallelism integer 0

SQL>

7，刪除過期的歸檔文件

歸檔位置用的是默認值，放在flash_recovery_area下(db_recovery_file_dest目錄=/oracle/flash_recovery_area)

[oracle@pen EC]$ echo $ORACLE_BASE

/oracle

[oracle@pen EC]$ cd /oracle/flash_recovery_area/EC/archivelog/

[oracle@pen archivelog]$ ls

2018_01_01 2018_01_11 2018_01_21 2018_01_31 2018_02_10 2018_02_20 2018_03_02 2018_03_12 2018_03_22 2018_04_01 2018_04_11

2018_01_02 2018_01_12 2018_01_22 2018_02_01 2018_02_11 2018_02_21 2018_03_03 2018_03_13 2018_03_23 2018_04_02 2018_04_12

2018_01_03 2018_01_13 2018_01_23 2018_02_02 2018_02_12 2018_02_22 2018_03_04 2018_03_14 2018_03_24 2018_04_03 2018_04_13

2018_01_04 2018_01_14 2018_01_24 2018_02_03 2018_02_13 2018_02_23 2018_03_05 2018_03_15 2018_03_25 2018_04_04 2018_04_14

2018_01_05 2018_01_15 2018_01_25 2018_02_04 2018_02_14 2018_02_24 2018_03_06 2018_03_16 2018_03_26 2018_04_05 2018_04_15

2018_01_06 2018_01_16 2018_01_26 2018_02_05 2018_02_15 2018_02_25 2018_03_07 2018_03_17 2018_03_27 2018_04_06 2018_04_16

2018_01_07 2018_01_17 2018_01_27 2018_02_06 2018_02_16 2018_02_26 2018_03_08 2018_03_18 2018_03_28 2018_04_07 2018_04_17

2018_01_08 2018_01_18 2018_01_28 2018_02_07 2018_02_17 2018_02_27 2018_03_09 2018_03_19 2018_03_29 2018_04_08

2018_01_09 2018_01_19 2018_01_29 2018_02_08 2018_02_18 2018_02_28 2018_03_10 2018_03_20 2018_03_30 2018_04_09

2018_01_10 2018_01_20 2018_01_30 2018_02_09 2018_02_19 2018_03_01 2018_03_11 2018_03_21 2018_03_31 2018_04_10

切換到root用戶，轉移或清除對應的歸檔日志, 刪除一些不用的日期目錄的文件，注意保留最后幾個文件(比如360以后的)

[oracle@pen archivelog]$rm -rf 2018_01*

[oracle@pen archivelog]$ rm -rf 2018_02*

[oracle@pen archivelog]$ ls

2018_03_01 2018_03_06 2018_03_11 2018_03_16 2018_03_21 2018_03_26 2018_03_31 2018_04_05 2018_04_10 2018_04_15

2018_03_02 2018_03_07 2018_03_12 2018_03_17 2018_03_22 2018_03_27 2018_04_01 2018_04_06 2018_04_11 2018_04_16

2018_03_03 2018_03_08 2018_03_13 2018_03_18 2018_03_23 2018_03_28 2018_04_02 2018_04_07 2018_04_12 2018_04_17

2018_03_04 2018_03_09 2018_03_14 2018_03_19 2018_03_24 2018_03_29 2018_04_03 2018_04_08 2018_04_13

2018_03_05 2018_03_10 2018_03_15 2018_03_20 2018_03_25 2018_03_30 2018_04_04 2018_04_09 2018_04_14

8，在刪除歸檔日志后，必須用RMAN維護控制文件，否則空間顯示仍然不釋放。(rman使用system用戶登錄，或者自創的chen用戶登錄，必須要有dba的權限)

[oracle@pen archivelog]$rman target chen/chen

[oracle@pen archivelog]$ rman target chen/chen

connected to target database: EC (DBID=1558596032)

RMAN>

9. 檢查一些無用的archivelog

RMAN> crosscheck archivelog all;

archive log filename=/oracle/flash_recovery_area/EC/archivelog/2018_04_13/o1_mf_1_506_ff13y3dg_.arc recid=464 stamp=973364932validation succeededforarchived log

archive log filename=/oracle/flash_recovery_area/EC/archivelog/2018_04_14/o1_mf_1_507_ff2lbs2k_.arc recid=465 stamp=973412441validation succeededforarchived log

archive log filename=/oracle/flash_recovery_area/EC/archivelog/2018_04_14/o1_mf_1_508_ff3n9454_.arc recid=466 stamp=973447204validation succeededforarchived log

archive log filename=/oracle/flash_recovery_area/EC/archivelog/2018_04_15/o1_mf_1_509_ff4fvh4q_.arc recid=467 stamp=973473391validation succeededforarchived log

archive log filename=/oracle/flash_recovery_area/EC/archivelog/2018_04_15/o1_mf_1_510_ff6dv19y_.arc recid=468 stamp=973537890validation succeededforarchived log

archive log filename=/oracle/flash_recovery_area/EC/archivelog/2018_04_17/o1_mf_1_511_ffbs1s2f_.arc recid=469 stamp=973681465validation succeededforarchived log

archive log filename=/oracle/flash_recovery_area/EC/archivelog/2018_04_17/o1_mf_1_512_ffbs1s9l_.arc recid=470 stamp=973681466validation succeededforarchived log

archive log filename=/oracle/flash_recovery_area/EC/archivelog/2018_04_17/o1_mf_1_513_ffbs1tb9_.arc recid=471 stamp=973681466

...

Crosschecked215 objects

10,?刪除過期的歸檔

RMAN> delete expired archivelog all;?------> 輸入“YES” 確認。

......

archive log filename=/oracle/flash_recovery_area/EC/archivelog/2018_02_28/o1_mf_1_411_f9dx438b_.arc recid=375 stamp=969297667

Deleted 119 EXPIRED objects

下面這一條命令視情況而定，可以不執行。

RMAN>delete archivelog until time 'sysdate-1' ; 刪除截止到前一天的所有archivelog

RMAN>exit(退出)

11，再次查詢，發現使用率正常，已經降到19.57

SQL> sqlplus / as sysdba

SQL> select * from V$FLASH_RECOVERY_AREA_USAGE;

SQL> select * fromV$FLASH_RECOVERY_AREA_USAGE;

FILE_TYPE PERCENT_SPACE_USED PERCENT_SPACE_RECLAIMABLE NUMBER_OF_FILES------------ ------------------ ------------------------- ---------------CONTROLFILE 0 0 0

ONLINELOG 0 0 0

ARCHIVELOG19.57 0 96BACKUPPIECE 0 0 0

IMAGECOPY 0 0 0

FLASHBACKLOG .3 .22 4

6 rows selected.

這樣，oracle的歸檔日志滿的問題就解決了，可以登錄和訪問了。

補充：如果archive log模式下不能正常startup,則先恢復成noarchive log,startup成功后，再shutdown;

操作命令如下：

shutdown immediate;

startup mount;

alter database noarchivelog;

alter database open;

shutdown immediate;

再次startup以archive log模式

startup mount;

show parameter log_archive_dest;

alter database archivelog;

archive log list;

alter database open;

如果還不行，則刪除一些archlog log

SQL> select group#,sequence# from v$log;

GROUP# SEQUENCE#

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

1???????? 62

3???????? 64

2???????? 63

原來是日志組一的一個日志不能歸檔

SQL> alter database clear unarchived logfile group 1;

alter database open;

最后，也可以指定位置Arch Log, 請按照如下配置

select name from v$datafile;

alter system set log_archive_dest='/opt/app/oracle/oradata/usagedb/arch' scope=spfile

或者修改大小(我的改成了20G,不能太小了)

SQL> alter system set db_recovery_file_dest_size=3G scope=both;

總結

以上是生活随笔為你收集整理的ORA-00257+mysql_ORA-00257错误的解决办法的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。

上一篇： mysql 开发规范_专业级的MySQL
下一篇： python process 函数_Py