oracle實驗記錄 （recover_file_dest空間不足）

SQL> startup force
ORACLE instance started.

Total System Global Area  188743680 bytes
Fixed Size                   788068 bytes
Variable Size             145488284 bytes
Database Buffers           41943040 bytes
Redo Buffers                 524288 bytes
Database mounted.
ORA-16038: log 1 sequence# 1 cannot be archived
ORA-19809: limit exceeded for recovery files
ORA-00312: online log 1 thread 1:
'E:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL\REDO01.LOG'

SQL> select * from v$log;

    GROUP#    THREAD#  SEQUENCE#      BYTES    MEMBERS ARC STATUS
---------- ---------- ---------- ---------- ---------- --- ----------------
FIRST_CHANGE# FIRST_TIME
------------- --------------
         1          1          1   10485760          1 NO  INACTIVE
      2548623 29-4月 -09

         2          1          2   10485760          1 NO  INACTIVE
      2554019 29-4月 -09

         3          1          3   10485760          1 NO  INVALIDATED
      2556977 29-4月 -09

SQL> show parameter db_recovery

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest                string      E:\oracle\product\10.1.0\flash
                                                 _recovery_area
db_recovery_file_dest_size           big integer 2G
SQL> show parameter db_recovery

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest                string      E:\oracle\product\10.1.0\flash
                                                 _recovery_area
db_recovery_file_dest_size           big integer 2G
SQL> alter database open;~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~手動刪除後 open不行
alter database open
*
ERROR at line 1:
ORA-16038: log 1 sequence# 1 cannot be archived
ORA-19809: limit exceeded for recovery files
ORA-00312: online log 1 thread 1:
'E:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL\REDO01.LOG'

SQL> desc v$recovery_dest;
ERROR:
ORA-04043: object v$recovery_dest does not exist

SQL> desc v$fixed_tables;
ERROR:
ORA-04043: object v$fixed_tables does not exist

SQL> desc v$fixed_table;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------

 NAME                                               VARCHAR2(30)
 OBJECT_ID                                          NUMBER
 TYPE                                               VARCHAR2(5)
 TABLE_NUM                                          NUMBER

SQL> select name from v$fixed_table where name like '%RECOVER%';

NAME
------------------------------
GV$RECOVER_FILE
V$RECOVER_FILE
GV$RECOVERY_LOG

 

V$RECOVERY_LOG
GV$RECOVERY_STATUS
V$RECOVERY_STATUS
GV$RECOVERY_FILE_STATUS
V$RECOVERY_FILE_STATUS
GV$RECOVERY_PROGRESS
V$RECOVERY_PROGRESS
GV$INSTANCE_RECOVERY

 

NAME
------------------------------
V$INSTANCE_RECOVERY
V$RECOVERY_FILE_DEST

13 rows selected.

SQL> desc v$recovery_file_dest;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------

 NAME                                               VARCHAR2(513)
 SPACE_LIMIT                                        NUMBER
 SPACE_USED                                         NUMBER
 SPACE_RECLAIMABLE                                  NUMBER
 NUMBER_OF_FILES                                    NUMBER

SQL> select * from v$recovery_file_dest;

NAME
--------------------------------------------------------------------------------

SPACE_LIMIT SPACE_USED SPACE_RECLAIMABLE NUMBER_OF_FILES
----------- ---------- ----------------- ---------------
E:\oracle\product\10.1.0\flash_recovery_area
 2147483648 2148856832                 0             263

RMAN> crosscheck archivelog all;

RMAN> delete expired archivelog all ;

SQL> select * from v$recovery_file_dest;

NAME
--------------------------------------------------------------------------------

SPACE_LIMIT SPACE_USED SPACE_RECLAIMABLE NUMBER_OF_FILES
----------- ---------- ----------------- ---------------
E:\oracle\product\10.1.0\flash_recovery_area
 2147483648  553035776                 0              62

SQL> alter database open;

Database altered.

SQL> select * from v$log;

    GROUP#    THREAD#  SEQUENCE#      BYTES    MEMBERS ARC STATUS
---------- ---------- ---------- ---------- ---------- --- ----------------
FIRST_CHANGE# FIRST_TIME
------------- --------------
         1          1          4   10485760          1 NO  CURRENT
      2582799 30-4月 -09

         2          1          2   10485760          1 YES INACTIVE
      2554019 29-4月 -09

         3          1          3   10485760          1 YES INACTIVE
      2556977 29-4月 -09

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/12020513/viewspace-612888/，如需轉載，請註明出處，否則將追究法律責任。