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

ORA-00257: archiver error. Connect internal only, until free Error handling method

Oracle database has real-time synchronization function, synchronization must open the archive log function

1. Log in with sys user

sqlplus sys/password as sysdba;

2. Look at the Archiv logs.

SQL> show parameter log_archive_dest;

3. You can check log sequence with archive log list

SQL> archive log list;

4. Check the flash recovery area usage, you can see that archivelog is already very large, reaching 97

SQL> select * from V$FLASH_RECOVERY_AREA_USAGE;

5. Calculate the space already occupied by flash recovery area

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

6. View the maximum log directory and log space settings:

SQL> show parameter db_recovery_file_dest;

Use Rman to transfer or clear the corresponding archive log, delete some unused date directory files, pay attention to keep the last few files

---------------------------------------------------------------------------------------

Note:

If you delete the archive log directly, you must maintain the control file with RMAN, otherwise the space display will not be released.

---------------------------------------------------------------------------------------

7. rman target sys/pass

8. Check some useless archivelog

RMAN> crosscheck archivelog all;

9. Delete expired archives

RMAN> delete expired archivelog all;

delete archivelog until time 'sysdate-1' ; Delete all archivelog as of the previous day

10. Check again and find that the usage rate is normal and has dropped.

SQL> select * from V$FLASH_RECOVERY_AREA_USAGE;

The following commands are used to verify the validity of archive logs, list invalid archive logs, and how to clear archive logs. Several common commands are listed:

crosscheck archivelog all; ---> Verify availability of logs

list expired archivelog all; ---> List all expired archivelog

delete archivelog until sequence 16; ---> Delete all archive logs with log sequence 16 and before

delete archivelog all completed before 'sysdate-7';---> Delete archive logs older than 7 days in system time, do not delete archive logs valid in flashback area

delete archivelog all completed before 'sysdate -1';---> ditto, 1 day ago

delete archivelog from time 'sysdate-1';---> Note this command, delete archivelog from time within 1 day of system time to the present

delete noprompt archivelog all completed before 'sysdate';---> This command clears all archive logs

delete noprompt archivelog all; ---> Same command as above

Other useful commands:

If the archive log mode fails to start normally, restore to noarchive log first, and then shut down after the startup is successful;

shutdown immediate;

startup mount;

alter database noarchivelog;

alter database open;

shutdown immediate;

Start again in archive log mode

shutdown immediate;

startup mount;

show parameter log_archive_dest;

alter database archivelog;

archive log list;

alter database open;

If not, delete some archlogs.

SQL> select group#,sequence# from v$log;

It turns out that a log of log group 1 cannot be archived

SQL> alter database clear unarchived logfile group 1;

SQL> alter database open;

Finally, you can also specify the location Arch Log, please configure it as follows

SQL> select name from v$datafile;

SQL> alter system set log_archive_dest='/u/app/admin/backup' scope=spfile

or change the size.

SQL> alter system set db_recovery_file_dest_size=100G scope=both;

Scope=both and scope=spfile

Oracle has something called spfile, which is a dynamic parameter file, which sets various parameters of Oracle. Dynamic means that you can create a database without shutting down the database

Change database parameters and record them in spfile. When changing parameters, there are four scope options. Scope is scope.

++ scope=spfile only changes the records in spfile, does not change the memory, that is, does not take effect immediately, but waits for the next database to start. There are some parameters that can only be changed in this way

++ scope=memory only changes memory, not spfile. Which means it'll fail the next time it's activated.

++ scope=both memory and spfile changed

++ does not specify scope parameters, equivalent to scope=both.

Set the archive log mode

1 Set to archive mode

SQL code

sql> archive log list; #Check whether it is archived

sql> alter system set log_archive_start=true scope=spfile; #Enable proactive archiving

sql> alter system set log_archive_dest =''location=/oracle/ora11/oradata/arch''scope=spfile;#Set archive path

sql> alter system set log_archive_dest_1=''location=/oracle/ora11/oradata/arch2'' scope=spfile;

sql> alter system set log_archive_dest_2=''location=/oracle/ora11/oradata/arch3'' scope=spfile;

#If you archive to two locations, you can do it with the above method

sql> alter system set log_archive_format =''arch_%d_%t_%r_%s.log'' #Set archive journal style

sql> shutdown immediate;

sql> startup mount; #Open control files, do not open data files

sql> alter database archivelog; #Switch database to archive mode

sql> alter database open; #Open the database file

sql> archive log list; #See if you are in archive mode at this time

Query to determine that the database is in archivelog mode and that the archiving process is running

sql> select log_mode from v$database;

sql> select archiver from v$instance;

SQL code

log switching

sql> alter system switch logfile;

This log switch writes the archive to two destinations,

/oracle/ora11/oraden/arch2 and/oracle/ora11/oraden/arch3 above,

If you want to confirm the catalogue

Run the following query in the oracle case:

sql> select name from v$archived_log;

Then confirm the files listed in the query in the operating system

II. Setting non-archiving mode

SQL code

sql> archive log list; #Check if it is archived

sql> alter system set log_archive_start=false scope=spfile;

#Disable automatic archiving

sql> shutdown immediate;

sql> startup mount; #Open control files, do not open data files

sql> alter database noarchivelog; #Switch database to non-archive mode

sql> alter database open; #Open the database file

sql> archive log list; #View is now in non-archive mode

3. File related commands

SQL code

sql> archive log stop;

sql> archive log start;

sql> archive log list;

sql> show parameters;

sql> show parameters log_archive_start;

sql> show parameters log_archive_max_process; #Number of archive processes

sql> alter system set log_archive_max_process=5; #Change the number of archive processes to 5

sql> select * from v$bgprocess; #inspect background processes

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.