Managing FRA

The following queries are helpful in managing the FRA.

SQL> Select * from v$recovery_file_dest;

Name - Current location

Space Limit - FRA size

Space Reclaimable - Space reclaimable by deleting the obsolete files.

SQL> select * from v$flash_recovery_area_usage;

Used to check the space being used by different files and the space reclaimable by deleting the files that are obsolete.

FRA Errors

When the FRA is full, Oracle issues the following error message,

ORA-19815: Warning: db_recovery_file_dest_size of XXXXXX bytes is 100% used, and has 0 remaining bytes available.

ORA-19809: Limit set by the db_recovery_dest_file_size is exceeded.

ORA-19804: Oracle is unable to reclaim a specified amount of bytes from the limit set by the db_recovery_file_dest_size.

FRA - File Deletion Policy

Automatic File Deletion Policy for FRA

• Multiplexed redo log files and control files (Permanent files) are never deleted.
• Files older than the days set by the retention policy are available for deletion.
• Files that are copied to the tape are eligible for deletion. However they are removed only when there is no space in the FRA.

FRA Parameters

The following parameters are involved in setting up a FRA.

1. DB_CREATE_FILE_DEST - location for all OMF data files.
2. DB_CREATE_ONLINE_LOG_DEST_n - location for control files and online redo log files. If this parameter is not set then oracle creates all three types of files in the DB_CREATE_FILE_DEST location.
3. DB_RECOVERY_FILE_DEST_SIZE - Size of FRA.
4. DB_RECOVERY_FILE_DEST - Location of FRA.
5. LOG_ARCHIVE-DEST_N - Location of Archive log files.

For eg.,

DB_CREATE_FILE_DEST = /oradata/dbfiles/
LOG_ARCHIVE-DEST_1 = 'LOCATION=/export/archive/arc_dest1'
LOG_ARCHIVE-DEST_2 = 'LOCATION=USE_DB_RECOVERY_FILE_DEST'
DB_RECOVERY_FILE_DEST_SIZE =350GB
DB_RECOVERY_FILE_DEST='LOCATION=/fra/rec_area'

one copy of current control file and online redo log files are stored in FRA also.

Control Files, redolog files and FRA

DB_CREATE_ONLINE_DEST_n : Setting this init parameter enable Oracle to create OMF control files and online redolog files in the location specified by the parameter.

DB_RECOVERY_FILE_DEST : Setting this init parameter enables Oracle to create OMF control files and online redolog files in the FRA.

Specifying both the above parameters enable Oracle to create OMF based control and redolog files on both the locations.

Omitting the above parameters will enable Oracle to create non-OMF based control and redolog files in the system specific default location.

Thanks

Setup Flash Recovery Area

To setup the FRA, you need to set two init paramters in the following order.

SQL> Alter system set DB_RECOVERY_FILE_DEST_SIZE=375809638400 scope=spfile;

SQL> Alter system set DB_RECOVERY_FILE_DEST=+ASM_FLASH scope=spfile;

When you configure FRA, LOG_ARCHIVE_DEST and LOG_ARCHIVE_DUPLEX_DEST init parameters become obsolete. You must use the parameters LOG_ARCHIVE_DEST_n.

Log_Archive_dest_10 is by default set to USE_DB_RECOVERY_FILE_DEST. Archived log files are stored in the FRA by default. If you have configured other locations a copy of archived files will also be placed in other locations as well.

USE_DB_RECOVERY_FILE_DEST refers to the FRA.

You can also use OEM and DBCA to configure FRA.

Disable FRA

Set DB_RECOVERY_FILE_DEST=''

Privilege to access all objects

Grant privilege to access all the objects from one schema to other

To grant privilege from one schema A to another Schema B

connect to schema A.

SQL> Grant Select any table to b;
SQL> Grant update any table to b;
or
SQL> Grant select any table, update any table, delete any table to b;

SQL> connect to schema B

SQL Select * from A.table_name;

there is no select any view privilege. the select any table privilege will automatically grant select privilege for views.

Note: The Grant select any table privilege is not object privilege, it is system privilege.

ORA-01031 Insufficient Privilege

While using the flashback feature, i encountered the following error message, when i tried to query dba_tables which is owned by sys user.

Select * from dba_tables as of timestamp sysdate-30/1440;
ORA-01031 Insufficient Privilege

The above error message is resulted due to the setting of the init parameter O7_DICTIONARY_ACCESSIBILITY to FALSE.

O7_DICTIONARY_ACCESSIBILITY

Possible Values - False (default) and True

When set to False it restricts the system privilege to access the objects owned by the sys user.
When set to true it allows the access to the objects owned by the SYS user.

In order to access the objects owned by sys user despite setting the parameter to false, you must explicitly grant the following roles,

SELECT_CATALOG_ROLE
EXECUTE_CATALOG_ROLE
DELETE_CATALOG_ROLE

Setting the Parameter to true is not recommended for security reasons. For eg., setting the parameter to true will ignore the parameter remote_login_passwordfile parameter. Hence you can connect to the database remotely and hack the database.

0403-031 - Fork Function Failed. There is not enough Memory available

Last week our Production Server was hanged. When I issued a command it took a lot of time and finally displayed the following error message.

0403-031 - Fork Function Failed. There is not enough Memory available.

The reason for this is there is no enough Page space.

To monitor the page space issue the following command

$ lsps -a
Page Space Physical Volume Volume Group Size %Used
----------- ---------------- -------------- ---- ------
Paging00 hdisk0 rootvg 16GB 25%
hd6 hdisk1 rootvg 8GB 95%

You can use the following command to find out top 3 processes utilizing most of the page space.

$ svmon -gP -t 3

To add Paging space

1. Identify the physical volume where you want to add page space (For eg. hdisk1).
2. Identify the PPsize (say 128MB) and Free PPs (500) available by using the command lspv hdisk1.
3. Determine how much space you want to add (say 8192MB).
4. Divide 8192MB/PP Size(128) = 64. Hence you want to increase 64 logical partitions.
5. Use the chps command to increase the paging space.

$ chps -s64 hd6

Alternatively you can use SMIT tool to increase the page space.