I. background
Today, I received a development call saying that Oracle cannot be remotely connected. It may be that archivelog is full. I tried logging on to Oracle and prompted
ORA-00257: archiver error. Connect internal only, until freed.
Considering that the hard disk space has been expanded in the past few days, it should be because the archive log size has reached the archive log size limit in Oracle. DBA has a holiday. Therefore, we decided to clear useless logs and modify the limit on log size.
Since I am not familiar with Oracle, I will record the entire process after completing the process for future reference.
II. Environment
RHEL 6.3 + Oracle 11gR2
Iii. Process
Because the archive log space is full, remote logon is not allowed. After ssh is sent to the server, use sqlplus sys/pass as sysdba to log on to oracle.
First, check the location of the archiv log.
SQL> show parameter log_archive_dest;
The following result is displayed:
NAME TYPE VALUE
-----------------------------------------------------------------------------
Log_archive_dest string
Log_archive_dest_1 string
Log_archive_dest_10 string
If the value is empty, try
SQL> archive log list;
Then I checked the usage
SQL> select * from V $ FLASH_RECOVERY_AREA_USAGE;
FILE_TYPE PERCENT_SPACE_USED PERCENT_SPACE_RECLAIMABLE NUMBER_OF_FILES
----------------------------------------------------------------------
CONTROLFILE. 13 0 1
ONLINELOG 2.93 0 3
ARCHIVELOG 99.72 0 122
BACKUPPIECE 0 0 0
IMAGECOPY 0 0 0
FLASHBACKLOG 0 0 0
Obviously, more than 99% of the log space is used.
At this time, I directly go to the flash_recovery_are directory.
Cd/orace/app/oracle/flash_recovery_area/ORCL/archivelog
Then I decided to back up the relevant files first.
Tar Jcvf arlog.20140125.tar. xz *
During the first operation, the system will prompt that the file is changed during the compression process. I didn't stop the Oracle service. So return to sqlplus and stop Oracle first
SQL> shutdown immediate;
This process takes a long time to complete ..
At this time, package again. The packaging process lasted for more than two hours...
After that, back up the compressed package to another directory and scp it to a server used for backup files. Then, it was very violent to delete all the directories in the folder named 2013.
1 rm-rf 2013 _*
Finished. Start the database
Sqlplus sys/pass as sysdba
SQL> startup
The first time I startup failed, the ORA-03113: end-of-file on communication channel was prompted. I re-executed it.
SQL> startup mount
Successful. Fix related information with rman
Rman target sys/pass
RMAN> delete expired archivelog all;
Return to sqlplus to modify the archivelog size.
SQL> alter system set db_recovery_file_dest_size = 5G scope = spfile;
The modification does not take effect immediately. Oracle needs to be restarted
SQL> shutdown immediate
SQL> startup
Check the size at this time
SQL> show parameter db_recover
NAME TYPE VALUE
-----------------------------------------------------------------------------
...
Db_recovery_file_dest_size big integer 5G
The whole process is complete.