what to do if your FRA get full in oracle
Deleting Archive Log files in a Data Guard environment
Published on: Author: Bastiaan Bak Category: OracleWe recently configured an Oracle 12.2 database environment with a primary database, and a concrete standby database managed past Oracle Data Guard.
The fill-in on the main database removed all archive log files later on a successful backup. For a normal standalone database this is a common configuration, only in a Information Guard environment this is not sufficient.
What volition happen if your standby database is unavailable? Before you delete any files on the master database, you should be sure y'all no longer need them for the recovery of the standby database.
Initial configuration
When you utilize the Data Guard Broker for the configuration of your standby database, it will create a remote archive destination on the main, connecting to a service on the standby database:
SQL> Prove parameter log_archive_dest_2
Proper name TYPE VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_2 string service= "motdc1" , ASYNC NOAFFI
RM delay= 0 optional compressio
northward=disable max_failure= 0 max_co
nnections= 1 reopen= 300 db_uniq
ue_name= "motdc1" net_timeout= iii
0 , valid_for= (online_logfile,a
ll_roles)
We scheduled backups on both the primary and the standby database. Part of the RMAN backup script was the cleanup of all archive log files subsequently a twenty-four hour period, if there was a successful fill-in:
backup as compressed backupset archivelog all not backed up;
delete noprompt archivelog until time 'sysdate - ane' backed up 1 times to device blazon deejay;
When to remove archive log files?
Our backup script was not really bulletproof. Although we kept our archive log files for at least a 24-hour interval, this is non really what nosotros wanted. The files should merely exist removed later on we are sure all transaction had been successfully applied to the standby database.
When querying the five$archived_log view for log_archive_dest_2 we can check what files are applied on the standby:
select dest_id , sequence# , practical
from v$archived_log
where dest_id = 2
and sequence# > ( select max(sequence#) - ten from v$archived_log )
order by sequence#
/
DEST_ID SEQUENCE# APPLIED
---------- ---------- ---------
2 1912 Aye
2 1913 Yeah
2 1914 YES
2 1915 Yep
2 1916 Aye
2 1917 YES
two 1918 Aye
two 1919 YES
2 1920 Yes
2 1921 NO
10 rows selected.
Why not let Oracle handle information technology?
Rather than irresolute the backup script it might be easier to allow the database handle it. All files in the FRA could be managed by the database.
The documentation describes how we can configure automatic cleanup policy of the archive log files afterwards they are applied on the standby database. We changed this setting in the RMAN backup configuration, and also removed the delete argument from the fill-in script.
Testing
After changing the RMAN configuration...
RMAN> CONFIGURE ARCHIVELOG DELETION POLICY TO Applied ON ALL STANDBY ;
old RMAN configuration parameters:
CONFIGURE ARCHIVELOG DELETION POLICY TO NONE;
new RMAN configuration parameters:
CONFIGURE ARCHIVELOG DELETION POLICY TO APPLIED ON ALL STANDBY;
new RMAN configuration parameters are successfully stored
starting full resync of recovery itemize
full resync complete
RMAN>
...and doing some log switches...
SQL> Modify system switch logfile ;
System altered.
SQL> ALTER system switch logfile ;
System altered.
SQL> Alter organisation switch logfile ;
System altered.
...we made another annal backup:
RMAN> fill-in as compressed backupset archivelog all not backed upward;
I starting time expected that the annal log files would have been removed at the moment of the backup, only this was not the example. The files were still in the FRA.
Maybe the files were non applied to the standby database? I checked the v$archived_log view once again, merely all the files - except the electric current - had been practical to the standby.
SQL> SELECT dest_id , SEQUENCE# , applied
FROM v$archived_log
WHERE dest_id = two
AND SEQUENCE# > ( SELECT MAX ( SEQUENCE#) - 10 FROM v$archived_log )
Lodge BY SEQUENCE#
/
2 3 iv 5 6
DEST_ID SEQUENCE# Applied
---------- ---------- ---------
two 1930 YES
two 1931 YES
ii 1932 Yeah
two 1933 YES
2 1934 YES
2 1935 YES
ii 1936 YES
2 1937 Yep
2 1938 Aye
2 1939 NO
10 ROWS selected.
This was not really doing what I expected, and so dorsum to the documentation: "For main databases, the archived redo log files are eligible for deletion later on they are applied on the standby." So, the files are non deleted immediately, but they are eligible for deletion.
You can check this in the Five$RECOVERY_AREA_USAGE view. We can see that some infinite for "Archived Log" is reclaimable, so eligible for deletion:
SQL> SELECT FILE_TYPE , PERCENT_SPACE_USED , PERCENT_SPACE_RECLAIMABLE , NUMBER_OF_FILES
2 FROM Five$RECOVERY_AREA_USAGE
iii /
FILE_TYPE PERCENT_SPACE_USED PERCENT_SPACE_RECLAIMABLE NUMBER_OF_FILES
----------------------- ------------------ ------------------------- ---------------
Command FILE 0 0 0
REDO LOG 0 0 0
ARCHIVED LOG .43 .43 13
BACKUP Piece 0 0 0
IMAGE Re-create 0 0 0
FLASHBACK LOG 65.63 0 6
FOREIGN ARCHIVED LOG 0 0 0
AUXILIARY DATAFILE COPY 0 0 0
8 ROWS selected.
Subsequently more load on the database the FRA fills upwards to 85%, and this triggers a warning in the alert log:
2017 - xi -06T17: 36 : 12.303898 +01:00
Errors in file /oraclebase/db/diag/rdbms/motdc2/motdc2/trace/motdc2_m000_24332. trc :
ORA- 19815 : Alarm: db_recovery_file_dest_size of 17179869184 bytes is 87.50 % used, and has 2147470336 remaining bytes bachelor.
2017 - 11 -06T17: 36 : 12.308765 +01:00
************************************************************************
You lot accept following choices to complimentary upwards space from recovery area:
1 . Consider irresolute RMAN Retentivity POLICY. If you are using Data Baby-sit,
then consider changing RMAN ARCHIVELOG DELETION POLICY.
two . Dorsum up files to third device such as record using RMAN
BACKUP RECOVERY Surface area control.
3 . Add disk infinite and increase db_recovery_file_dest_size parameter to
reflect the new space.
4 . Delete unnecessary files using RMAN DELETE command. If an operating
system control was used to delete files, then utilize RMAN CROSSCHECK and
DELETE EXPIRED commands.
************************************************************************
This warning gives some suggestions on how to fix this effect. The first one is a bit strange. If we use Data Guard, we should consider changing the RMAN ARCHIVELOG DELETION POLICY. I think this is what we merely did...
A footling after the backup triggers another log switch, and we meet more than messages in the warning log. The database removes the oldest annal log files:
Deleted Oracle managed file /orafra/db/MOTDC2/archivelog/ 2017_11_06/o1_mf_1_1927_f00bl4x2_. arc
Deleted Oracle managed file /orafra/db/MOTDC2/archivelog/ 2017_11_06/o1_mf_1_1928_f00cynk7_. arc
Deleted Oracle managed file /orafra/db/MOTDC2/archivelog/ 2017_11_06/o1_mf_1_1929_f00hh2z4_. arc
Deleted Oracle managed file /orafra/db/MOTDC2/archivelog/ 2017_11_06/o1_mf_1_1930_f00lzlvm_. arc
Deleted Oracle managed file /orafra/db/MOTDC2/archivelog/ 2017_11_06/o1_mf_1_1931_f00pj2n2_. arc
Deleted Oracle managed file /orafra/db/MOTDC2/archivelog/ 2017_11_06/o1_mf_1_1932_f00t0ltc_. arc
Deleted Oracle managed file /orafra/db/MOTDC2/archivelog/ 2017_11_06/o1_mf_1_1933_f00xk2p8_. arc
Deleted Oracle managed file /orafra/db/MOTDC2/archivelog/ 2017_11_06/o1_mf_1_1934_f0111ltx_. arc
So, the automatic cleanup works, merely simply after infinite pressure in the FRA removes the files marked as eligible for deletion, every bit explained in the documentation. And the definition of space pressure seems to be an 85% usage of the FRA, which might trigger an ORA-19815 alarm.
Back to transmission cleanup?
Although the solution in a higher place does what it should do, I'm not really happy with this. The files are not actually removed after being applied on the standby as the configuration suggests, and more serious; the ORA-19815 warning could trigger a daily monitoring alarm on a perfectly working database.
A good solution seems to be the configuration of the delete policy in RMAN, in combination with a daily "delete noprompt archivelog all" in the backup script.
For testing nosotros starting time configure the policy:
CONFIGURE ARCHIVELOG DELETION POLICY TO APPLIED ON ALL STANDBY;
Then, in the data guard manager, we stop the utilise on the standby database:
DGMGRL> edit database motdc1 set state= 'employ-off' ;
Succeeded.
After that, we make some log switches:
SQL> Alter organization switch logfile ;
System altered.
SQL> Change organization switch logfile ;
Organization altered.
SQL> ALTER system switch logfile ;
System altered.
We can bank check that the new archives are not applied on the standby database:
SQL> SELECT dest_id , SEQUENCE# , applied
FROM 5$archived_log
WHERE dest_id = 2
AND SEQUENCE# > ( SELECT MAX ( SEQUENCE#) - 10 FROM v$archived_log )
Order By SEQUENCE#
/
DEST_ID SEQUENCE# APPLIED
---------- ---------- ---------
2 1940 YES
2 1941 YES
ii 1942 Yeah
ii 1943 Aye
2 1944 Yeah
ii 1945 Yeah
two 1946 YES
two 1947 NO
2 1948 NO
two 1949 NO
10 ROWS selected.
Adjacent, we make a fill-in in RMAN:
RMAN> fill-in every bit compressed backupset archivelog all not backed up;
Starting backup at 06-November- 17
current log archived
using channel ORA_DISK_1
using channel ORA_DISK_2
using aqueduct ORA_DISK_3
using channel ORA_DISK_4
skipping archived logs of thread i from sequence 1945 to 1946 ; already backed upward
aqueduct ORA_DISK_1: starting compressed archived log backup prepare
aqueduct ORA_DISK_1: specifying archived log(s) in backup set
input archived log thread= 1 sequence= 1947 RECID= 4426 Postage stamp= 959363627
channel ORA_DISK_1: starting piece 1 at 06-NOV- 17
aqueduct ORA_DISK_2: starting compressed archived log backup prepare
channel ORA_DISK_2: specifying archived log(s) in backup set
input archived log thread= 1 sequence= 1950 RECID= 4432 Postage= 959363700
channel ORA_DISK_2: starting piece 1 at 06-NOV- 17
aqueduct ORA_DISK_3: starting compressed archived log backup ready
channel ORA_DISK_3: specifying archived log(s) in backup prepare
input archived log thread= 1 sequence= 1948 RECID= 4428 STAMP= 959363642
channel ORA_DISK_3: starting piece 1 at 06-NOV- 17
channel ORA_DISK_4: starting compressed archived log backup set
channel ORA_DISK_4: specifying archived log(s) in backup set up
input archived log thread= 1 sequence= 1949 RECID= 4430 STAMP= 959363644
channel ORA_DISK_4: starting piece 1 at 06-NOV- 17
channel ORA_DISK_2: finished piece 1 at 06-NOV- 17
piece handle=/orarman/db/MOTDC2/gnsitejl_1_1. bck tag=TAG20171106T175500 comment=NONE
channel ORA_DISK_2: backup gear up complete, elapsed fourth dimension: 00:00:00
aqueduct ORA_DISK_1: finished piece 1 at 06-November- 17
piece handle=/orarman/db/MOTDC2/gmsitejl_1_1. bck tag=TAG20171106T175500 comment=NONE
channel ORA_DISK_1: fill-in set consummate, elapsed time: 00:00:00
channel ORA_DISK_3: finished slice 1 at 06-NOV- 17
piece handle=/orarman/db/MOTDC2/gositejl_1_1. bck tag=TAG20171106T175500 annotate=NONE
channel ORA_DISK_3: backup gear up complete, elapsed time: 00:00:00
channel ORA_DISK_4: finished piece 1 at 06-November- 17
slice handle=/orarman/db/MOTDC2/gpsitejl_1_1. bck tag=TAG20171106T175500 comment=NONE
channel ORA_DISK_4: fill-in ready complete, elapsed time: 00:00:00
Finished fill-in at 06-Nov- 17
Starting Control File and SPFILE Autobackup at 06-November- 17
slice handle=/orarman/db/MOTDC2/c- 390140924 - 20171106 - 10 annotate=NONE
Finished Control File and SPFILE Autobackup at 06-NOV- 17
RMAN>
And, as role of the backup procedure, nosotros try to delete the archivelog files:
RMAN> delete noprompt archivelog all backed up ane times to device type disk;
released channel: ORA_DISK_1
released aqueduct: ORA_DISK_2
released channel: ORA_DISK_3
released channel: ORA_DISK_4
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID= 2822 device type=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: SID= 4234 device type=Disk
allocated aqueduct: ORA_DISK_3
channel ORA_DISK_3: SID= 4467 device type=Deejay
allocated channel: ORA_DISK_4
channel ORA_DISK_4: SID= 4705 device type=DISK
RMAN-08120: warning: archived log not deleted, non yet practical past standby
archived log file name=/orafra/db/MOTDC2/archivelog/ 2017_11_06/o1_mf_1_1946_f014trpk_. arc thread= i sequence= 1946
RMAN-08120: warning: archived log not deleted, not still applied past standby
archived log file name=/orafra/db/MOTDC2/archivelog/ 2017_11_06/o1_mf_1_1947_f014wv4x_. arc thread= 1 sequence= 1947
RMAN-08120: alert: archived log not deleted, not yet applied by standby
archived log file name=/orafra/db/MOTDC2/archivelog/ 2017_11_06/o1_mf_1_1948_f014xblo_. arc thread= 1 sequence= 1948
RMAN-08120: alarm: archived log not deleted, not nonetheless applied past standby
archived log file name=/orafra/db/MOTDC2/archivelog/ 2017_11_06/o1_mf_1_1949_f014xdo5_. arc thread= ane sequence= 1949
RMAN-08120: warning: archived log not deleted, non yet applied by standby
archived log file name=/orafra/db/MOTDC2/archivelog/ 2017_11_06/o1_mf_1_1950_f014z3z6_. arc thread= ane sequence= 1950
List of Archived Log Copies for database with db_unique_name MOTDC2
=====================================================================
Key Thrd Seq S Low Time
------- ---- ------- - ---------
1300755 one 1945 A 06-Nov- 17
Proper noun: /orafra/db/MOTDC2/archivelog/ 2017_11_06/o1_mf_1_1945_f014l36l_. arc
deleted archived log
archived log file proper name=/orafra/db/MOTDC2/archivelog/ 2017_11_06/o1_mf_1_1945_f014l36l_. arc RECID= 4422 STAMP= 959363283
Deleted ane objects
The files are still protected against a delete. As we can see the statement triggers a RMAN-08120 warning, because the transactions are not yet applied on the standby.
When restoring the redo apply on the standby, the delete from RMAN is besides working again.
Other Setups
In this example we used a Maximum Performance protection manner in Data Guard. Our goal was to apply all transactions on the standby equally soon as possible, simply with a minimal affect on the principal database.
Data Baby-sit tin also be used in various other configurations. You tin configure an "apply filibuster", which tin protect y'all for user errors on the standby database. Another configuration is the Snapshot Standby database, where you temporarily end the redo apply on the standby, and open the database for testing purposes.
In both these cases your policy could bank check if the transactions are shipped to, but not applied on the standby database:
RMAN> CONFIGURE ARCHIVELOG DELETION POLICY TO SHIPPED TO ALL STANDBY ;
Conclusion
The "CONFIGURE ARCHIVELOG DELETION POLICY TO Practical ON ALL STANDBY;" setting in RMAN is a good protection for the archive log files in a Data Guard environment where the standby is used for disaster recovery. It protects the files from being deleted before all the transactions are applied on the standby.
This setting too configures an automatic cleanup of the archive log files from the FRA. Simply files might only be removed later on a confusing ORA-19815 warning.
A better solution is non to wait for the automated cleanup, but to delete the files equally part of the backup procedure.
If, for some reason, the transactions are not applied on the standby, the delete command triggers a RMAN-08120 alarm, and the archived log files are non deleted. And if the FRA is filling upwardly with a ORA-19815 warning, the automated cleanup volition still remove all files that are eligible for deletion.
Documentation: https://docs.oracle.com/database/122/RCMRF/CONFIGURE.htm#GUID-B5094E73-C26C-4FED-AE39-8C2E9540050A__CHDIFEEE
Source: https://www.qualogy.com/techblog/oracle/deleting-archive-log-files-in-a-data-guard-environment
0 Response to "what to do if your FRA get full in oracle"
Post a Comment