ORA-00257: archiver error Solution

ORA-00257 error (insufficient space error), by looking for information, the vast majority of said this is due to archiving too many logs, occupying all the hard disk space caused, you can simply delete logs or increase storage space.

(1) Change the archive mode, directory, and log size (I am using this method)

 

1. Change Oracle to archive Mode
1. Disable Oracle
SQL> shutdown immediate;
Database closed.
Database dismounted.
Oracle instance shut down.

2. Start as Mount
SQL> startup Mount

Oracle instance started.

Total system global area 2572144640 bytes
Fixed size 2283984 bytes
Variable Size 738199088 bytes
Database buffers 1828716544 bytes
Redo buffers 2945024 bytes
Database mounted.

3. Change to archive Mode
SQL> alter database archivelog
Database altered.
Note: 'archiveelog' is in archive mode, while 'noarchiveelog' is not in archive mode.

 

4. Change the database status to "open ".
SQL> alter database open

5. view the archive mode information
SQL> archvie log list
It prompts the archiving mode, whether to enable, and parameters.
Database Log mode archive Mode
Automatic Archival Enabled
Archive destination use_db_recovery_file_dest
Oldest online log sequence 88
Next log sequence to archive 90
Current Log sequence 90

 

Ii. Change the archive directory
1. view the db_recovery_file_dest parameter.

(1) "db_recovery_file_dest" is the directory for storing archived logs. Run the show parameter command to display the parameter value fast_recovery_area. Example:

SQL> show parameter db_recovery

Name type value
-----------------------------------------------------------------------------
Db_recovery_file_dest string/usr/local/Oracle/flash_recovery_area
Db_recovery_file_dest_size large integer 4182 m
 

(2) Check the V $ recovery_file_dest view and check the space limit, used space, and number of files of fast_recovery_area.

SQL> select * from V $ recovery_file_dest;

Name space_limit space_used space_reclaimable number_of_files
-----------------------------------------------------
D: \ oracle \ fast_recovery_area 4385144832 4346230272 2741248

2. Change the archive log directory
Syntax: Alter system set parameter = value scope = spfile)

Example: SQL> alter system set db_recovery_file_dest = '/home/Oracle/flash_recovery_area' scope = spfile;

System altered.

 

3. Change the archive log size

1. view the value of 'db _ recovery_file_dest_size '.
SQL> show parameter db_recov

Name type value
-----------------------------------------------------------------------------
Db_recovery_file_dest string/usr/local/Oracle/flash_recovery_area
Db_recovery_file_dest_sizeBig integer 4182 m

 

 

2. Change the value of 'db _ recovery_file_dest_size '.

SQL> alter system set db_recovery_file_dest_size = 41820 M scope = spfile;

System altered.

 

3. Shut down the database and restart the database.

SQL> shutdown immediate;
Database closed.
Database dismounted.
Oracle instance shut down.

SQL> startup open;
Oracle instance started.

Total system global area 2572144640 bytes
Fixed size 2283984 bytes
Variable Size 738199088 bytes
Database buffers 1828716544 bytes
Redo buffers 2945024 bytes
Database mounted.
Database opened.

4. Check the value of 'db _ recovery_file_dest_size 'again.
SQL> show parameter db_reco
Name type value
-----------------------------------------------------------------------------
Db_recovery_file_dest string/home/Oracle/flash_recovery_area
Db_recovery_file_dest_size large integer 41820 m

 

After the restart, you can.

 

 

========================================================== ======================================

(The following is for reference only)

(2) solutions with Baidu experience:

Solution:

1,

Log on to the server using securecrt, switch to Oracle, and connect to Oracle

[[Email protected] ~] # Su-Oracle

[[Email protected] ~] $ Sqlplus/nolog

SQL> connect/As sysdba

 

2,

Check the usage of Flash recovery area. We can see that archivelog is already large, reaching 99.94.

SQL> select * from V $ flash_recovery_area_usage;

3,

Computing space occupied by flash recovery area

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

The above is a check. You can directly set the archive file to be large or small, as shown below:

4,

Change the space of flash_recovery_area to 6 GB. Make sure that the disk has sufficient space before modification.

SQL> alter system set db_recovery_file_dest_size = 8g;

5,

Now clean up archivelog archive logs. Backup is recommended in the production environment.

Query log directory location

Show parameter recover;

Deletes archived logs. userdb is the name of the database instance.

CD/u01/APP/Oracle/flash_recovery_area/userdb/archivelog

Use the root account to delete files in this directory or Back Up Files from other places

 

 

6,

Using rman, too much content, only partial

[[Email protected] archivelog] $ RMAN

RMAN> connect target sys/sys_passwd

Crosscheck backup;

Delete Obsolete;

Delete expired backup;

Crosscheck archivelog all;

Delete expired archivelog all;

// A prompt is displayed in this step. Enter yes and press Enter.

Host; // exit RMAN

7,

Confirm whether the operation is successful

# Sqlplus/nolog

SQL> connect/As sysdba

SQL> select * from V $ flash_recovery_area_usage;

8. The deletion is complete and the database is reconnected.

 

ORA-00257: archiver error Solution