恢复db_recovery_file_dest_size参数为默认值“0”
db_recovery_file_dest_size参数的作用是限制闪回恢复区可存放文件总大小。一旦将参数db_recovery_file_dest_size设置为非零值后“很难”恢复为默认值“0”。
1.查看默认情况下db_recovery_file_dest_size参数值，db_recovery_file_dest_size参数的默认值是“0”。
SYS@PROD> show parameter db_recovery_file_dest_size
NAME TYPE VALUE
------------------------------------ ----------- -------------
db_recovery_file_dest_size big integer 0
2.将db_recovery_file_dest_size参数调整为非零值
SYS@PROD> alter system set db_recovery_file_dest_size=4g;
System altered.
SYS@PROD> show parameter db_recovery_file_dest_size
NAME TYPE VALUE
------------------------------------ ----------- ------------
db_recovery_file_dest_size big integer 4G
3.尝试将db_recovery_file_dest_size参数修改为“0”
SYS@PROD> alter system set db_recovery_file_dest_size=0;或者SYS@PROD> alter system set db_recovery_file_dest_size=0 scope=spfile;全部报错
ERROR at line 1:
ORA-02097: parameter cannot be modified because specified value is invalid
ORA-19803: Parameter DB_RECOVERY_FILE_DEST_SIZE is out of range (1 -
18446744073709551614)
解决办法：
SYS@PROD> alter system reset db_recovery_file_dest_size scope=spfile sid='*';
System altered.
SYS@PROD> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SYS@PROD> startup;
ORACLE instance started.
Total System Global Area 314572800 bytes
Fixed Size 1219184 bytes
Variable Size 109053328 bytes
Database Buffers 201326592 bytes
Redo Buffers 2973696 bytes
Database mounted.
Database opened.
SYS@PROD> show parameter db_recovery_file_dest_size
NAME TYPE VALUE
------------------------------------ ----------- ----------
db_recovery_file_dest_size big integer 0
判断使用的是pfile还是spfile
show parameter spfile
使用show命令,如果查询结果为空，则为pfile启动,反之则为spfile
