Alter system set db_recovery_file_dest_size=size> scope=both /. To remove files you must use RMAN. Manually moving or deleting files will have no effect as oracle will be unaware. The obvious choice is to backup and remove some archive log files.
Flash Recovery Area Usage
ALTER SYSTEM set db_recovery_file_dest_size=60G scope=both sid='*'. The size of the flash recovery area at any time is a function of. Oct 18, 2008 SQL> alter system set db_recovery_file_dest_size = ‘135G’ scope=both. 2 thoughts on “ Database Flashback (FRA) Configuration and Tips for RAC with.
Database flashback is a new feature in 10g that is a continuation of flashback features introduced back in 9i release. Reasons to Enable Database Flashback If flashback is set up correctly, you can perform both complete and incomplete recovery faster and much easier especially when it comes to incomplete recovery (point-in-time recovery). Also, some sites for the purpose of training or development, they need an earlier copy of the database.
This now can be accomplished with 1 line of SQL code. Design Goal To get the following files in the FRA and keep flashback logs for 3 days:. Flashback logs. New type of files. A copy of control file. Add log file members log groups.
Archive logs. Enable block change tracking (BCT). Autobackups of control file. Database Flashback Implementation All steps are implemented on 10.2.0.4 64-bit 2-node RAC with ASM 1) Enabling Flashback SQL alter system set dbrecoveryfiledestsize = ‘135G’ scope=both sid=’.’; System altered. SQL alter system set dbrecoveryfiledest = ‘+ARCDG’ scope=both sid=’.’; System altered. SQL SQL alter system set dbflashbackretentiontarget = 4320 scope=both sid=’.’; System altered. At this point there are no flashback logs being send to FRA.
You must enable flashback on database level. Steps are very close to enabling archivelog mode in RAC database (but no exactly the same) Stop all instances in the RAC environment srvctl stop database -d Start one instance only in mount state: SQL startup mount ORACLE instance started. Total System Global Area bytes Fixed Size 2058496 bytes Variable Size bytes Database Buffers bytes Redo Buffers 14716928 bytes Database mounted.
SQL SQL alter database flashback on; Database altered. SQL SQL alter database open; Database altered. SQL exit — start second instance srvctl start instance -d -i At this point, flashback logs should start getting generated from both RAC instances to FRA.
2) A Copy of Control File Duplicating a control file in ASM is a bit complicated and unfortunately requires downtime of all instances. Here are the steps: A) Backup your pfile (optional step) create pfile=’/u02/oracle/admin/sid/pfile/initsid.ora’ from spfile; B) Add to control file parameter in spfile the diskgroup for FRA. In our case it’s ARCDG. Alter system set controlfiles = ‘your old existing control files’, ‘+ARCDG’ scope=spfile sid=’.’; C) Backup controlfile (optional step) alter database backup controlfile to trace; D) Shutdown all instances srvctl stop database -d E) Startup a single instance in nomount startup nomount F) Connect to rman rman nocatalog connect target / G) Create new copy from existing control file restore controlfile from ‘ your old existing control file including full path’; H) mount and open your instance. Check for newly created control files. Select name from v$controlfile; J) Bounce your databases for verification srvctl stop database -d srvctl start database -d 3) Add Log Members to Log File Groups In order for FRA to recognize your new log files, you would have to add new log file groups and drop the old ones.
Column ISRECOVERYDESTFILE under gv$logfile must be “YES” for log members under FRA disk group. This is the only way FRA will identify log files existence in v$flashrecoveryareausage Add a new log group, 1 member will be under FRA, and another member in a different disk group. Alter database add logfile thread size 100m; After finish adding new groups, drop the old ones. They are no longer needed. Note: All new log files added will show status ‘Invalid’ (v$logfile) until they are used. Nothing to worrying about.
4) Archive Logs Sending archive logs to FRA. Alter system set logarchivedest1=’LOCATION=USEDBRECOVERYFILEDEST’ scope=both sid=’.’; 5) Block Change Tracking This is an uptime procedure. It will create about an 11 MB file initially. ALTER DATABASE ENABLE BLOCK CHANGE TRACKING USING FILE ‘+ARCDG’; 6) Control File Auto Backup Control file auto backup to take place whenever a change is made to the control file. Execute only once from 1 instance. Rman nocatalog rman connect target / CONFIGURE CONTROLFILE AUTOBACKUP ON; You might also need to execute the following statement to redirect control file auto backups to FRA.
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK CLEAR; Verification Select filetype, percentspaceused, numberoffiles from V$FLASHRECOVERYAREAUSAGE; FILETYPE PERCENTSPACEUSED NUMBEROFFILES CONTROLFILE 0.05 1 ONLINELOG 1.74 6 ARCHIVELOG 10.91 73 BACKUPPIECE 0.41 9 IMAGECOPY 0 0 FLASHBACKLOG 7.67 167 You can see size and number of files for every component we configured. Hazem Ameen Senior Oracle DBA.
Flashback query is a powerful and useful feature introduced in Oracle 9i, and enhanced greatly in Oracle 10g, that can help us recover data, lost or corrupted, due to human error. One big advantages of using flashback over point-in-time recovery is that for the latter not only transactions from the time of error to the current time would be lost but also the system will be unavailable for the duration of the recovery. For flashback query, on the other hand, there will be no down time needed and repair or recovery is less labor and time intensive than what it used to be in earlier versions of Oracle. With the new features like Recycle Bin, Flashback databases and Flashback Drop in Oracle 10g, the flashback capability introduced in 9i has been improved tremendously now turning a small feature into a powerful tool in the new Oracle releases. Categories.
(6). (1). (1). (1). (1). (1). (1).
(1). (1). (1). (1). (1). (41).
(9). (1). (1). (1). (2).
(1). (1). (1). (1).
(1). (1). (1). (3). (1).
(1). (1). (6).
(1). (1). (1). (1).
(1). (1).
(6). (1).
(1). (1).
(2). (1).
(1). (1). (2). (1). (1). (1). (1).
(4). (1). (1). (1). (1). (1).
(4). (1). (1).
(1). (1).
(1). (9). (1). (1).
(1). (1). (1). (1).
(1). (1). (1). (19). (2). (1). (1).
(1). (1).
(1). (1). (1). (1). (3).
(1). (1). (1).
(1). (1). (2). (1). (1). (1). (1).