Remote_login_passwordfile

The password file is used to authenticate the users connecting to the database as Sysdba or Sysoper. The password file is created with the orapwd utility as follows,

$ cd $ORACLE_HOME/dbs
$ orapwd file=orapw password= entries=

Where,
file = the filename where the password is stored.
password = the password for sys user
entries = the number of users that can connect as sys user at a time. This parameter is optional.

Once the password file is created you need to set the init parameter remote_login_passwordfile for the database to use it.

The possible values for the parameter is

EXCLUSIVE - Only one database can use the password file. Multiple users can exist in the password file.

SHARED - More than one database can use the password file. However only Sys users allowed to have sysdba privilege. You will receive ORA-01999 error when you grant sysdba privilege to other users.

NONE - The password file is not used.

Thanks

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

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.