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

ORA-00257: archiver error. Connect internal only, until freed

View archive log sequence

SQL > archive log list;Automatic archival Enabled # automatic archiving Archive destination USE_DB_RECOVERY_FILE_DEST # Archive directory for the specified flashback recovery area Oldest online log sequence 174# oldest online log sequence Next log sequence to archive 176 # next log sequence archive Current log sequence 176# current log sequence

Archive logs are saved in the flashback recovery area (Flash recovery area) of the oracle system by default

View flashback recovery area parameters

SQL > show parameter db_recovery_file_dest;NAME TYPE VALUE----db_recovery_file_dest string / data/oracle/flash_recovery_areadb_recovery_file_dest_size big integer 3G

Check the use of flash recovery area

SQL > select * from V$FLASH_RECOVERY_AREA_USAGE FILE_TYPE PERCENT_SPACE_USED PERCENT_SPACE_RECLAIMABLE NUMBER_OF_FILES--CONTROL FILE 0 0 0REDO LOG 0 0 0ARCHIVED LOG 99.98 0 23BACKUP PIECE 0 0 0IMAGE COPY 0 0 0FLASHBACK LOG 0 0 0FOREIGN ARCHIVED LOG 0 0 0

You can see that the ARCHIVE LOG log has taken up 99.98% of the flash space.

Option 1: delete expired archive logs and increase the space size of the flashback recovery area

If necessary, delete the archive log backup first.

1. Delete archived log files

Find the archive log directory: / data/oracle/flash_recovery_area/ORCL/archivelog

Delete unwanted archive log files

After deleting the archive log directly, you must use RMAN to maintain the control file

Use RMAN to maintain control files

[oracle@dbsrc ~] $rman target sys/passRMAN > crosscheck backup; # check all backup sets RMAN > run {delete noprompt obsolete;crosscheck archivelog all;delete noprompt expired archivelog all;crosscheck backup;delete noprompt expired backup;} RMAN > exit

The command in run {} is treated as a job, and the execution of the entire command stops with one failure.

Delete obsolete; # Delete backups that exceed the save policy

Crosscheck archivelog all; # checks for invalid archive logs (invalid ones marked expired)

Delete expired archivelog all; # Delete the archive log of expired

Delete expired backup; # Delete all invalid backup sets

Delete expired deletes the information that RMAN thought existed but has actually been deleted on disk or tape, deleting only the records in the RMAN database.

Delete obsolete deletes backup data that is older than defined by the backup retention policy, and also updates the RMAN database and control files.

Noprompt means no confirmation is required.

Third, increase the space size of the flashback recovery area

SQL > alter system set db_recovery_file_dest_size=8G

View flashback recovery area parameters

SQL > show parameter db_recovery_file_dest

Re-check the use of flash recovery area

SQL > select * from V$FLASH_RECOVERY_AREA_USAGE FILE_TYPE PERCENT_SPACE_USED PERCENT_SPACE_RECLAIMABLE NUMBER_OF_FILES--CONTROL FILE 0 0 0REDO LOG 0 0 0ARCHIVED LOG 4.5 0 3BACKUP PIECE 0 0 0IMAGE COPY 0 0 0FLASHBACK LOG 0 0 0FOREIGN ARCHIVED LOG 0 0 0

Add scheduled tasks to clean up the storage space of archived logs

1. Create a new folder for storing script files

[root@dbsrc] # mkdir-p / backup/oracledata

2. Edit script content

[root@dbsrc ~] # vim / backup/oracledata/oracle_archivelog_clean.sh# script content (/ backup/oracledata/oracle_archivelog_clean.sh) Delete all archived files find / data/oracle/flash_recovery_area/ORCL/archivelog/-mtime + 3-name "201*"-exec rm-rf {}\

3. Set script file permissions

[root@dbsrc ~] # chmod 777 / backup/oracledata/oracle_archivelog_clean.sh

4. Add scheduled tasks (one of system scheduling tasks and user scheduling tasks)

System task scheduling: the work that the system periodically performs, such as writing cached data to the hard disk, log cleaning, etc. In the / etc directory, the crontab file, which is the configuration file for system task scheduling.

User task scheduling: tasks that users perform on a regular basis, such as user data backup, scheduled email reminders, etc. Users can use the crontab tool to customize their scheduled tasks. All user-defined crontab files are saved in the / var/spool/cron directory. Its file name is the same as the user name.

1), system task scheduling

Edit system task schedule

The [root@dbsrc ~] # vim / etc/crontabSHELL=/bin/bashPATH=/sbin:/bin:/usr/sbin:/usr/binMAILTO=root#SHELL variable specifies which shell the system will use, where the bash#PATH variable specifies the path to which the system executes the command # MAILTO variable specifies that the task execution information of crond will be emailed to the root user if the value of the MAILTO variable is empty Does not send task execution information to user # For details see man 4 crontabs# Example of job definition:#.-minute (0-59) # |.-hour (0-23) # | |.-day of month (1-31) # |.-month (1-12) OR jan,feb,mar Apr... # |.-day of week (0-6) (Sunday=0 or 7) OR sun,mon,tue,wed,thu,fri,sat# | # * user-name command to be executed# (*): represents all (,): multiple value intervals (-): range connector # (/) specifies the interval frequency For example, (* / 10) in the minute field, it means that the script task 05 * root / backup/oracledata/oracle_archivelog_clean.sh in the corresponding directory will be executed at 05:00 every day as an administrator.

2), user task scheduling

Edit user task schedule

[root@dbsrc ~] # crontab-e# executes script 05 * / backup/oracledata/oracle_archivelog_clean.sh in the corresponding directory at 05:00 every morning as an administrator

No more executive users need to be added, otherwise the task plan cannot be executed, and the following error will be reported

[root@dbsrc ~] # cat / var/spool/mail/rootFrom root@dbsrc.localdomain Tue Aug 7 05:00:01 2018Return-Path: X-Original-To: rootDelivered-To: root@dbsrc.localdomainReceived: by dbsrc.localdomain (Postfix, from userid 0) id 9662C2827; Tue, 7 Aug 2018 05:00:01 + 0800 (CST) From: "(Cron Daemon)" To: root@dbsrc.localdomainSubject: Cron root / backup/oracledata/oracle_archivelog_clean.shContent-Type: text/plain Charset=UTF-8Auto-Submitted: auto-generatedPrecedence: bulkX-Cron-Env: X-Cron-Env: Message-Id: Date: Tue, 7 Aug 2018 05:00:01 + 0800 (CST) / bin/sh: root: command not found

View user schedule

[root@dbsrc] # crontab-10 5 * / backup/oracledata/oracle_archivelog_clean.sh

5. Check whether the cron service is called at the set time

[root@dbsrc ~] # cat / var/log/cronAug 7 05:00:01 dbsrc CROND [12655]: (root) CMD (root / backup/oracledata/oracle_archivelog_clean.sh) Aug 7 05:01:01 dbsrc CROND [12795]: (root) CMD (run-parts / etc/cron.hourly) Aug 7 05:01:01 dbsrc run-parts (/ etc/cron.hourly) [12795]: starting 0anacronAug 7 05:01:01 dbsrc run-parts (/ etc/cron.hourly) [12804]: finished 0anacron

6. Check whether the shell script reports an error (cat / var/spool/mail/ user name)

[root@dbsrc ~] # cat / var/spool/mail/rootFrom root@dbsrc.localdomain Tue Aug 7 05:00:01 2018Return-Path: X-Original-To: rootDelivered-To: root@dbsrc.localdomainReceived: by dbsrc.localdomain (Postfix, from userid 0) id 9662C2827; Tue, 7 Aug 2018 05:00:01 + 0800 (CST) From: "(Cron Daemon)" To: root@dbsrc.localdomainSubject: Cron / backup/oracledata/oracle_archivelog_clean.shContent-Type: text/plain Charset=UTF-8Auto-Submitted: auto-generatedPrecedence: bulkX-Cron-Env: X-Cron-Env: Message-Id: Date: Tue, 7 Aug 2018 05:00:01 + 0800 (CST) find:'/ data/oracle/flash_recovery_area/ORCL/archivelog/2018_08_04': No such file or directory

7. Restart the crond service

[root@dbsrc ~] # service crond restart

Or

[root@dbsrc ~] # / etc/init.d/crond restar

Annotation

Service crond start / / start service service crond stop / / close service service crond restart / / restart service service crond reload / / reload configuration service crond status / / check service status crontab [- u user] [- e |-l |-r]-e: edit the contents of a user's crontab file. If you do not specify a user, you edit the current user's crontab file. -l: displays the crontab file contents of a user. If no user is specified, the crontab file contents of the current user are displayed. -r: delete a user's crontab file from the / var/spool/cron directory. If no user is specified, the current user's crontab file is deleted by default. -I: give a confirmation prompt when deleting the user's crontab file. # prompt You have new mail in / var/spool/mail/root to solve [root@dbsrc ~] # echo "unset MAILCHECK" > > / etc/profile; [root@dbsrc ~] # source / etc/profile

Option 2: modify the location of the archive directory

First, create a new archive file storage directory

[root@dbsrc ~] # mkdir / data/oracle/archive_log

Second, set the directory owner and permissions

[root@dbsrc ~] # chown-R oracle:oinstall / data/oracle/archive_ log [root @ dbsrc ~] # chmod-R 775 / data/oracle/archive_log

Third, it is forbidden to put archive logs in the flashback recovery area

SQL > alter system set db_recovery_file_dest=''

Fourth, set up the archive log storage directory

SQL > alter system set log_archive_dest='/data/oracle/archive_log'

5. View the archive log sequence

SQL > archive log list;Automatic archival EnabledArchive destination / data/oracle/archive_logOldest online log sequence 174Next log sequence to archive 176Current log sequence 176

Summary

The above is the Oracle GoldenGate synchronization service archive space maintenance introduced by the editor. I hope it will be helpful to you. If you have any questions, please leave me a message and the editor will reply to you in time. Thank you very much for your support to the website!

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.