wd and cc

— Happy every day

ORA-00257 和 ORA-19815

Posted at — Sep 6, 2008
这几天遇到了这两个错误,记录一下。
使用 perl 的 DBI 连接的时候,会提示 ora-00257 错误,这个用 三qlplus 估计也是一样的错误,注意时远程连接才出错。
CRITICAL : Can't connect "db8.sds.cnb.yahoo.com"
同时,数据库的alert 里面有下面的信息
ORA-19815: WARNING: db_recovery_file_dest_size of 268435456000 bytes is 100.00% used, and has 0 remaining bytes available.
************************************************************************
You have following choices to free up space from flash 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
   DELETE EXPIRED commands.
************************************************************************
ARCH: Archival stopped, error occurred. Will continue retrying
ORACLE Instance nirv3 - Archival Error
登陆上数据库看看
NAME                                 TYPE                   VALUE
------------------------------------ ---------------------- ------------------------------
archive_lag_target                   integer                0
log_archive_config                   string                 dg_config=(stb1)
log_archive_dest                     string
log_archive_dest_1                   string                 location="USE_DB_RECOVERY_FILE
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

NAME                                 TYPE                   VALUE
------------------------------------ ---------------------- ------------------------------
log_archive_dest_8                   string
log_archive_dest_9                   string
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

NAME                                 TYPE                   VALUE
------------------------------------ ---------------------- ------------------------------
log_archive_dest_state_9             string                 enable
log_archive_duplex_dest              string
log_archive_format                   string                 %s_%t_%r_%a.arc
log_archive_local_first              boolean                TRUE
log_archive_max_processes            integer                4
log_archive_min_succeed_dest         integer                1
log_archive_start                    boolean                FALSE
log_archive_trace                    integer                0
standby_archive_dest                 string                 ?/dbs/arch

SQL> select * from v$log;

    GROUP#    THREAD#  SEQUENCE#      BYTES    MEMBERS ARCHIV STATUS                           FIRST_CHANGE# FIRST_TIME
---------- ---------- ---------- ---------- ---------- ------ -------------------------------- ------------- ------------
         1          1     221250   52428800          1 YES    INACTIVE                            5082709437 05-SEP-08
         2          1     221252   52428800          1 NO     INACTIVE                            5082713458 05-SEP-08
         3          2     270972   52428800          1 YES    INACTIVE                            5082699510 05-SEP-08
         4          2     270975   52428800          1 NO     CURRENT                             5082713581 05-SEP-08
         9          1     221251   52428800          1 NO     INACTIVE                            5082712943 05-SEP-08
        10          2     270973   52428800          1 YES    INACTIVE                            5082705151 05-SEP-08
        11          1     221253   52428800          1 NO     CURRENT                             5082713765 05-SEP-08
        12          2     270974   52428800          1 NO     INACTIVE                            5082711887 05-SEP-08
        13          3     275581   52428800          1 NO     INACTIVE                            5082712519 05-SEP-08
        14          3     275582   52428800          1 NO     CURRENT                             5082713078 05-SEP-08
        15          3     275579   52428800          1 NO     INACTIVE                            5082710114 05-SEP-08

    GROUP#    THREAD#  SEQUENCE#      BYTES    MEMBERS ARCHIV STATUS                           FIRST_CHANGE# FIRST_TIME
---------- ---------- ---------- ---------- ---------- ------ -------------------------------- ------------- ------------
        16          3     275580   52428800          1 NO     INACTIVE                            5082711321 05-SEP-08

12 rows selected.

SQL> ????select value from v$diag_info where name ='Diag Trace';

VALUE
------------------------------------------------------------------------------------------------------------------------------------------------------
/home/oracle/app/diag/rdbms/nirv/nirv3/trace

SQL> select * from V$FLASH_RECOVERY_AREA_USAGE;

FILE_TYPE                                PERCENT_SPACE_USED PERCENT_SPACE_RECLAIMABLE NUMBER_OF_FILES
---------------------------------------- ------------------ ------------------------- ---------------
CONTROL FILE                                              0                         0               0
REDO LOG                                                  0                         0               0
ARCHIVED LOG                                            100                         0           13109
BACKUP PIECE                                              0                         0               0
IMAGE COPY                                                0                         0               0
FLASHBACK LOG                                             0                         0               0
FOREIGN ARCHIVED LOG                                      0                         0               0

7 rows selected.

SQL> show parameter db_recovery_file_dest_size

NAME                                 TYPE                   VALUE
------------------------------------ ---------------------- ------------------------------
db_recovery_file_dest_size           big integer            250G
SQL> show parameter DB_RECOVERY_FILE_DEST

NAME                                 TYPE                   VALUE
------------------------------------ ---------------------- ------------------------------
db_recovery_file_dest                string                 /sds/oradata
db_recovery_file_dest_size           big integer            250G
SQL> alter system set db_recovery_file_dest_size=300G;  

System altered.

SQL> show parameter DB_RECOVERY_FILE_DEST

NAME                                 TYPE                   VALUE
------------------------------------ ---------------------- ------------------------------
db_recovery_file_dest                string                 /sds/oradata
db_recovery_file_dest_size           big integer            300G
SQL> select * from V$FLASH_RECOVERY_AREA_USAGE;

FILE_TYPE                                PERCENT_SPACE_USED PERCENT_SPACE_RECLAIMABLE NUMBER_OF_FILES
---------------------------------------- ------------------ ------------------------- ---------------
CONTROL FILE                                              0                         0               0
REDO LOG                                                  0                         0               0
ARCHIVED LOG                                          83.41                         0           13115
BACKUP PIECE                                              0                         0               0
IMAGE COPY                                                0                         0               0
FLASHBACK LOG                                             0                         0               0
FOREIGN ARCHIVED LOG                                      0                         0               0

7 rows selected.
可以看到空间是 83% 了,数据库应该就好了。不过这个是治标不治本的操作,需要把archive log删掉才行,具体怎么搞我也不会。。。汗。。
comments powered by Disqus