Flash Recovery Area in Oracle

db_recovery_file_dest_size parameter is available from oracle 10g onward. This parameter is used set Flash Recovery Area (FRA) to  manage a variety of backup and recovery-related files on your behalf. 

When setting up a flash recovery area, you must choose a location (a directory or Automatic Storage Management disk group) to hold the files. The flash recovery area cannot be stored on a raw file system.

You must also determine a disk quota for the flash recovery area, the maximum space to be used for all files stored there. You must choose a location large enough to accomodate the required disk quota. When the disk space limit is approached, Oracle can delete nonessential files to make room for new files, subject to the limitations of the retention policy.

The flash recovery area should be on a separate disk from the database area, where active database files such as datafiles, control files, and online redo logs are stored. Keeping the flash recovery area on the same disk as the database area exposes you to loss of both your live database files and backups in the event of a media failure.

The files in the flash recovery area can be classified as permanent or transient. The only permanent files (assuming these are configured to be stored in the flash recovery area) are multiplexed copies of the current control file and online redo logs. These cannot be deleted without causing the instance to fail. All other files are transient, because Oracle will generally eventually delete these files, at some point after they become obsolete under the retention policy or have been backed up to tape. Transient files include archived redo logs, datafile copies, control file copies, control file autobackups, and backup pieces.

To enable the flash recovery area, you must set the two initialization parameters DB_RECOVERY_FILE_DEST_SIZE (which specifies the disk quota, or maximum space to use for flash recovery area files for this database) andDB_RECOVERY_FILE_DEST (which specifies the location of the flash recovery area).

Note:.

• DB_RECOVERY_FILE_DEST_SIZE must be set before DB_RECOVERY_FILE_DEST.
• In a RAC database, all instances must have the same values for these parameters.

The V$RECOVERY_FILE_DEST and V$FLASH_RECOVERY_AREA_USAGE views can help you determine whether you have allocated enough space for your flash recovery area.

Query the V$RECOVERY_FILE_DEST view to find out the current location, disk quota, space in use, space reclaimable by deleting files, and total number of files in the flash recovery area.

SQL> SELECT * FROM V$RECOVERY_FILE_DEST;

NAME            SPACE_LIMIT SPACE_USED SPACE_RECLAIMABLE NUMBER_OF_FILES

--------------  ----------- ---------- ----------------- ---------------

/mydisk/rcva     5368709120 109240320             256000              28

Query the V$FLASH_RECOVERY_AREA_USAGE view to find out the percentage of the total disk quota used by different types of files, and how much space for each type of file can be reclaimed by deleting files that are obsolete, redundant, or already backed up to tape.

SQL> SELECT * FROM V$FLASH_RECOVERY_AREA_USAGE;

FILE_TYPE    PERCENT_SPACE_USED PERCENT_SPACE_RECLAIMABLE NUMBER_OF_FILES

------------ ------------------ ------------------------- ---------------

CONTROLFILE                   0                         0               0

ONLINELOG                     2                         0              22

ARCHIVELOG                 4.05                      2.01              31

BACKUPPIECE                3.94                      3.86               8

IMAGECOPY                 15.64                     10.43              66

FLASHBACKLOG                .08                         0               1

To disable the flash recovery area, set the DB_RECOVERY_FILE_DEST initialzation parameter to a null string. For example, use this SQL*Plus statement to change the parameter on a running database:

ALTER SYSTEM SET DB_RECOVERY_FILE_DEST='' SCOPE=BOTH SID="*";

The database will no longer provide the space management features of the flash recovery area for the files stored in the old DB_RECOVERY_FILE_DEST location. The files will still be known to the RMAN repository, however, and available for backup and restore activities.

Related Error Messages:

ORA-19802: cannot use DB_RECOVERY_FILE_DEST without DB_RECOVERY_FILE_DEST_SIZE

Cause: There are two possible cause for this error: 1) The DB_RECOVERY_FILE_DEST parameter was in use when no DB_RECOVERY_FILE_DEST_SIZE parameter was encountered while fetching initialization parameter. 2) An attempt was made to set DB_RECOVERY_FILE_DEST with the ALTER SYSTEM command when no DB_RECOVERY_FILE_DEST_SIZE was in use.

Action: Correct the dependency parameter definitions and retry the command.

ORA-19803: Parameter DB_RECOVERY_FILE_DEST_SIZE is out of range (1 - %s)

Cause: Parameter DB_RECOVERY_FILE_DEST_SIZE specfied was not valid.

Action: Specify a valid number within the range.

ORA-19804: cannot reclaim %s bytes disk space from %s limit

Cause: Oracle cannot reclaim disk space of specified bytes from the DB_RECOVERY_FILE_DEST_SIZE limit.Action: There are five possible solutions:
1) Take frequent backup of recovery area using RMAN.
2) Consider changing RMAN retention policy.
Ex:-
    RMAN> configure retention policy to {none,recovery,redundancy};

3) Consider changing RMAN archivelog deletion policy.
4) Add disk space and increase DB_RECOVERY_FILE_DEST_SIZE.
Ex:-
SQL > alter system set db_recovery_file_dest=40g scope=both;

5) Delete files from recovery area using RMAN.
EX:-
  RMAN> delete expired archivelog all;
  RMAN> crosscheck archivelog all;
  

ORA-19808: recovery destination parameter mismatch

Cause: The value of parameters DB_RECOVERY_FILE_DEST and DB_RECOVERY_FILE_DEST_SIZE must be same in all instances. instance. All databases must have same recovery destination parameters.Action: Check DB_RECOVERY_FILE_DEST and DB_RECOVERY_FILE_DEST_SIZE values in all instances.

ORA-19809: limit exceeded for recovery files

Cause: The value of parameters DB_RECOVERY_FILE_DEST and DB_RECOVERY_FILE_DEST_SIZE must be same in all instances. instance. All databases must have same recovery destination parameters.Action: Check DB_RECOVERY_FILE_DEST and DB_RECOVERY_FILE_DEST_SIZE values in all instances.

ORA-38776: cannot begin flashback generation - flash recovery area is disabled

Cause: During a database mount, the RVWR process discovered that the flash recovery area was disabled. DB_RECOVERY_FILE_DEST must have been set null or removed from the INIT.ORA file while the database was unmounted.Action: Flashback database requires the flash recovery area to be enabled. Either enable the flash recovery area by setting the DB_RECOVERY_FILE_DEST and DB_RECOVERY_FILE_DEST_SIZE initialization parameters, or turn off flashback database with the ALTER DATABASE FLASHBACK OFF command.

Deleting Duplicate Records from a Table

Many Time DBA faces a situation , in which they have to remove duplicate records from a table. This is somewhat difficult but we have some methods  that will make it somewhat easier to resolve.

Method 1:

Delete all rowids that is BIGGER than the SMALLEST rowid value (for a given key):

SQL> DELETE FROM table_name A 
  2  WHERE ROWID > ( SELECT min(rowid) 
  3                  FROM table_name B
  4                  WHERE A.key_values = B.key_values );

Method 2:

This method is usually faster. However, remember to recreate all indexes, constraints, triggers, etc. on the table when done.

SQL> create table table_name2 as select distinct * from table_name1;
SQL> drop table table_name1;
SQL> rename table_name2 to table_name1;

Method 3:

SQL> DELETE FROM my_table t1
  2  WHERE EXISTS ( SELECT 'x' FROM my_table t2
  3                 WHERE t2.key_value1 = t1.key_value1
  4                   AND t2.key_value2 = t1.key_value2
  4                   AND t2.rowid      > t1.rowid );

Note: One can eliminate  unnecessary operations by creating an index on the joined fields in the inner loop (no need to loop through the entire table on each pass by a record). This will speed-up the deletion process.

Note 2: If you are comparing NULL columns, use the NVL function. Remember that NULL is not equal to NULL. This should not be a problem as all key columns should be NOT NULL by definition.

Method 4:

This method collects the first row (order by rowid) for each key values and delete the rows that are not in this set:

SQL> DELETE FROM my_table t1
  1  WHERE rowid NOT IN ( SELECT min(rowid) 
  2                       FROM my_table t2
  3                       GROUP BY key_value1, key_value2 );

Note: IF key_value1 is null or key_value2 is null, this still works correctly.

Method 5:

This method is the easiest way to remove duplicate records from a table. But this is not feasible method for large table

1) Put duplicates in a temp table.

   

   Create Table table_dups as Select * From evil_table

   Group  By col1, col2, col3.... Having count(*) > 1;

2) Remove duplicates from table.




   Delete From evil_table where  (col1, col2, col3...) in (Select col1, col2,

   col3  From   table_dups);




3) Put duplicate rows back.




   Insert into evil_table (col1, col2, col3...) Select col1, col2, col3...

   From   table_dups;

Password File (orapwd Utility ) of Oracle

Oracle password file stores passwords for users with administrative privileges.

If the DBA wants to start up an Oracle instance there must be a way for Oracle to authenticate the DBA. Obviously, DBA password cannot be stored in the database, because Oracle cannot access the database before the instance is started up. Therefore, the authentication of the DBA must happen outside of the database. There are two distinct mechanisms to authenticate the DBA:

(i) Using the password file or

(ii) Through the operating system (groups). Any OS user under dba group, can login as SYSDBA.

The default location for the password file is:

$ORACLE_HOME/dbs/orapw$ORACLE_SID on Unix, %ORACLE_HOME%\database\PWD%ORACLE_SID%.ora on Windows.

REMOTE_LOGIN_PASSWORDFILE

The init parameter REMOTE_LOGIN_PASSWORDFILE specifies if a password file is used to authenticate the DBA or not. If it set either to SHARED or EXCLUSIVE, password file will be used.

REMOTE_LOGIN_PASSWORDFILE is a static initialization parameter and therefore cannot be changed without bouncing the database.

Following are the valid values for REMOTE_LOGIN_PASSWORDFILE:

NONE - Oracle ignores the password file if it exists i.e. no privileged connections are allowed over non secure connections. If REMOTE_LOGIN_PASSWORDFILE is set to EXCLUSIVE or SHARED and the password file is missing, this is equivalent to setting REMOTE_LOGIN_PASSWORDFILE to NONE.

EXCLUSIVE (default) - Password file is exclusively used by only one (instance of the) database. Any user can be added to the password file. Only an EXCLUSIVE file can be modified. EXCLUSIVE password file enables you to add, modify, and delete users. It also enables you to change the SYS password with the ALTER USER command.

SHARED - The password file is shared among databases. A SHARED password file can be used by multiple databases running on the same server, or multiple instances of an Oracle Real Application Clusters (RAC) database. However, the only user that can be added/authenticated is SYS.

A SHARED password file cannot be modified i.e. you cannot add users to a SHARED password file. Any attempt to do so or to change the password of SYS or other users with the SYSDBA or SYSOPER or SYSASM (this is from 11g) privileges generates an error. All users needing SYSDBA or SYSOPER or SYSASM system privileges must be added to the password file when REMOTE_LOGIN_PASSWORDFILE is set to EXCLUSIVE. After all users are added, you can change REMOTE_LOGIN_PASSWORDFILE to SHARED.

This option is useful if you are administering multiple databases or a RAC database.

If a password file is SHARED or EXCLUSIVE is also stored in the password file. After its creation, the state is SHARED. The state can be changed by setting REMOTE_LOGIN_PASSWORDFILE and starting the database i.e. the database overwrites the state in the password file when it is started up.

ORAPWD

You can create a password file using orapwd utility. For some Operating systems, you can create this file as part of standard installation.

Users are added to the password file when they are granted the SYSDBA or SYSOPER or SYSASM privilege.

The Oracle orapwd utility assists the DBA while granting SYSDBA, SYSOPER and SYSASM privileges to other users. By default, SYS is the only user that has SYSDBA and SYSOPER privileges. Creating a password file, via orapwd, enables remote users to connect with administrative privileges.

$ orapwd file=password_file_name [password=the_password] [entries=n] [force=Y|N] [ignorecase=Y|N] [nosysdba=Y|N]

Examples:

$ orapwd file=orapwSID password=sys_password force=y nosysdba=y

$ orapwd file=$ORACLE_HOME/dbs/orapw$ORACLE_SID password=secret

$ orapwd file=orapwprod entries=30 force=y

C:\orapwd file=%ORACLE_HOME%\database\PWD%ORACLE_SID%.ora password=2012 entries=20

C:\orapwd file=D:\oracle11g\product\11.1.0\db_1\database\pwdsfs.ora password=id entries=6 force=y

$ orapwd file=orapwPRODB3 password=abc123 entries=10 ignorecase=n

$ orapwd file=orapwprodb password=oracle1 ignorecase=y

There are no spaces permitted around the equal-to (=).

The following describe the orapwd command line arguments.

FILE

Name to assign to the password file, which will hold the password information. You must supply complete path. If you supply only filename, the file is written to the current directory. The contents are encrypted and are unreadable. This argument is mandatory.

The filenames allowed for the password file are OS specific. Some operating systems require the password file to adhere to a specific format and be located in a specific directory. Other operating systems allow the use of environment variables to specify the name and location of the password file.

If you are running multiple instances of Oracle Database using Oracle Real Application Clusters (RAC), the environment variable for each instance should point to the same password file.

It is critically important to secure password file.

PASSWORD

This is the password the privileged users should enter while connecting as SYSDBA or SYSOPER or SYSASM.

ENTRIES

Entries specify the maximum number of distinct SYSDBA, SYSOPER and SYSASM users that can be stored in the password file.

This argument specifies the number of entries that you require the password file to accept. The actual number of allowable entries can be higher than the number of users, because the orapwd utility continues to assign password entries until an OS block is filled. For example, if your OS block size is 512 bytes, it holds four password entries. The number of password entries allocated is always a multiple of four.

Entries can be reused as users are added to and removed from the password file. When you exceed the allocated number of password entries, you must create a new password file. To avoid this necessity, allocate a number of entries that is larger than you think you will ever need.

FORCE

(Optional) If Y, permits overwriting an existing password file. An error will be returned if password file of the same name already exists and this argument is omitted or set to N.

IGNORECASE

(Optional) If Y, passwords are treated as case-insensitive i.e. case is ignored when comparing the password that the user supplies during login with the password in the password file.

NOSYSDBA

(Optional) For Data Vault installations.

Granting SYSDBA or SYSOPER or SYSASM privileges

Use the V$PWFILE_USERS view to see the users who have been granted SYSDBA or SYSOPER or SYSASM system privileges for a database.

SQL> select * from v$pwfile_users;

USERNAME SYSDBA SYSOPER SYSASM

-------- ------ ------- ------

SYS TRUE TRUE FALSE 

The columns displayed by the view V$PWFILE_USERS are:

Column	Description
USERNAME	This column contains the name of the user that is recognized by the password file.
SYSDBA	If the value of this column is TRUE, then the user can log on with SYSDBA system privilege.
SYSOPER	If the value of this column is TRUE, then the user can log on with SYSOPER system privilege.
SYSASM	If the value of this column is TRUE, then the user can log on with SYSASM system privilege.

If orapwd has not yet been executed or password file is not available, attempting to grant SYSDBA or SYSOPER or SYSASM privileges will result in the following error:

SQL> grant sysdba to him;

ORA-01994: GRANT failed: cannot add users to public password file

If your server is using an EXCLUSIVE password file, use the GRANT statement to grant the SYSDBA or SYSOPER or SYSASM system privilege to a user, as shown in the following example:

SQL> grant sysdba to him;

SQL> select * from v$pwfile_users;

USERNAME SYSDBA SYSOPER SYSASM

-------- ------ ------- ------

SYS TRUE TRUE FALSE 

HIM TRUE FALSE FALSE

SQL> grant sysoper to him;

SQL> select * from v$pwfile_users;

USERNAME SYSDBA SYSOPER SYSASM

-------- ------ ------- ------

SYS TRUE TRUE FALSE 

HIM TRUE TRUE FALSE

SQL> grant sysasm to HIM;

SQL> select * from v$pwfile_users;

USERNAME SYSDBA SYSOPER SYSASM

-------- ------ ------- ------

SYS TRUE TRUE FALSE 

HIM TRUE TRUE TRUE

When you grant SYSDBA or SYSOPER or SYSASM privileges to a user, that user's name and privilege information are added to the password file. If the server does not have an EXCLUSIVE password file (i.e. if the initialization parameter REMOTE_LOGIN_PASSWORDFILE is NONE or SHARED, or the password file is missing), Oracle issues an error if you attempt to grant these privileges.

Use the REVOKE statement to revoke the SYSDBA or SYSOPER or SYSASM system privilege from a user, as shown in the following example:

SQL> revoke sysoper from him;

SQL> select * from v$pwfile_users;

USERNAME SYSDBA SYSOPER SYSASM

-------- ------ ------- ------

SYS TRUE TRUE FALSE 

HIM TRUE FALSE TRUE

A user's name remains in the password file only as long as that user has at least one of these three privileges. If you revoke all 3 privileges, Oracle removes the user from the password file.

Because SYSDBA, SYSOPER and SYSASM are the most powerful database privileges, the WITH ADMIN OPTION is not used in the GRANT statement. That is, the grantee cannot in turn grant the SYSDBA or SYSOPER or SYSASM privilege to another user. Only a user currently connected as SYSDBA can grant or revoke another user's SYSDBA or SYSOPER or SYSASM system privileges. These privileges cannot be granted to roles, because roles are available only after database startup.

If you receive the file full error (ORA-01996) when you try to grant SYSDBA or SYSOPER or SYSASM system privileges to a user, you must create a larger password file and regrant the privileges to the users.

Removing Password File

If you determine that you no longer require a password file to authenticate users, you can delete the password file and then optionally reset the REMOTE_LOGIN_PASSWORDFILE initialization parameter to NONE. After you remove this file, only those users who can be authenticated by the OS can perform SYSDBA or SYSOPER or SYSASM database administration operations.