Shulou(Shulou.com)06/03 Report--

How to restore the db_recovery_file_dest_size parameter to the default value of 0? I believe many inexperienced people are at a loss about this. Therefore, this paper summarizes the causes and solutions of the problem. Through this article, I hope you can solve this problem.

After oracle is installed by default, if there is no manual configuration log (alter)

System set log_archive_dest_1= "LOCATION=/oracle/oracle/archive" scope = spfile;), the default is under the db_revovery road. If there is insufficient db_recovery_file_dest space, the archive will fail. If you restart db, it will fail to start.

The db_recovery_file_dest_size parameter limits the total size of files that can be stored in the flashback recovery area. Once the parameter db_recovery_file_dest_size is set to a non-zero value, it is difficult to return to the default value of "0". Here I suggest two ways to restore this parameter to the default value of "0", one is the "circuitous strategy" and the other is the reset method.

1. View db_recovery_file_dest_size parameter values by default

The default value for the db_recovery_file_dest_size parameter is "0".

SYS@PROD > show parameter db_recovery_file_dest_size

NAME TYPE VALUE

Db_recovery_file_dest_size big integer 0

SYS@PROD > col name for A20

SYS@PROD > select * from v$recovery_file_dest

NAME SPACE_LIMIT SPACE_USED SPACE_RECLAIMABLE NUMBER_OF_FILES

--

0 0 0

two。 Adjust the db_recovery_file_dest_size parameter to a nonzero value

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

SYS@PROD > select * from v$recovery_file_dest

NAME SPACE_LIMIT SPACE_USED SPACE_RECLAIMABLE NUMBER_OF_FILES

--

4294967296 0 0 0

3. Attempt to change the db_recovery_file_dest_size parameter to "0"

SYS@PROD > alter system set db_recovery_file_dest_size=0

Alter system set db_recovery_file_dest_size=0

*

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)

The error message is very obvious that the size of the db_recovery_file_dest_size parameter must be between 1: 18446744073709551614, that is, it cannot be adjusted to its default value of "0".

A friend asked if you could try to use scope=spfile to adjust this parameter. Attention, it is also not possible. The verification is as follows.

SYS@PROD > alter system set db_recovery_file_dest_size=0 scope=spfile

System altered.

The modification seems to be successful here, but after this adjustment, the database will not be able to restart. The error message is as follows.

SYS@PROD > startup

ORA-19803: Parameter DB_RECOVERY_FILE_DEST_SIZE is out of range (1-18446744073709551614)

For the same error reason, the size of the db_recovery_file_dest_size parameter must be between 1 "18446744073709551614.

4. Circuitous treatment method

Since we can't achieve our goal of adjusting the db_recovery_file_dest_size parameter to "0" using conventional methods, how should we deal with this requirement?

After trying, we can achieve the purpose of recovery by detour through pfile.

1) generate pfile using the current spfile

SYS@PROD > create pfile from spfile

File created.

2) use the vi command to delete the db_recovery_file_dest_size parameter information in pfile

[oracle@secdb1 ~] $vi $ORACLE_HOME/dbs/initPROD.ora

……

* .db_recovery_file_dest_size=4294967296

……

Note: if adjusted by the scope=spfile method above, the information in pfile is as follows:

* .db_recovery_file_dest_size=0

The method is the same, deleting the entire line of information.

3) stop the database and generate spfile, and start the database with spfile

SYS@PROD > shutdown immediate

Database closed.

Database dismounted.

ORACLE instance

Shut down.

SYS@PROD > create spfile from pfile

File created.

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.

4) View the modification results

SYS@PROD > show parameter db_recovery_file_dest_size

NAME TYPE VALUE

Db_recovery_file_dest_size big integer 0

SYS@PROD > col name for A20

SYS@PROD > select * from v$recovery_file_dest

NAME SPACE_LIMIT SPACE_USED SPACE_RECLAIMABLE NUMBER_OF_FILES

--

0 0 0

The goal is achieved.

5. Use a straightforward reset method

Another convenient way to restore parameters to the default value is to use the reset method, which can avoid the "circuitous strategy" above. The adjustment process is shown below for your reference.

SYS@PROD > show parameter db_recovery_file_dest_size

NAME TYPE VALUE

Db_recovery_file_dest_size big integer 4G

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

Through experiments, this paper explores a method to restore database parameters to default values. Please understand this process in principle. The fundamental function of the parameter file is to "override the default value of the parameter" in order to depict an environment that is most suitable for your application scenario. Therefore, what is recorded in the parameter file is different from the default parameter value. Therefore, by deleting the parameters in the pfile parameter file, you can achieve the purpose of restoring the parameters to their default values. BTW, using the reset method is faster and more convenient.

After reading the above, have you mastered the method of restoring the db_recovery_file_dest_size parameter to the default value of 0? If you want to learn more skills or want to know more about it, you are welcome to follow the industry information channel, thank you for reading!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.