List Of Important Oracle Security Interview Questions

Q – 1 How to specify the Archived log file name format and destination?

Ans- By setting the following values in init.ora file.

LOG_ARCHIVE_FORMAT = arch %S/s/T/tarc (%S – Log sequence number and is zero left paded, %s – Log sequence number not padded. %T – Thread number lef-zero-paded and %t – Thread number not padded). The file name created is arch 0001 are if %S is used.

LOG_ARCHIVE_DEST = path.

Q – 2 Explain What is the use of ANALYZE command?

Ans- To perform one of these function on an index,table, or cluster:

– to collect statisties about object used by the optimizer and store them in the data dictionary.
– to delete statistics about the object used by object from the data dictionary.
– to validate the structure of the object.
– to identify migrated and chained rows of the table or cluster.

Q – 3 How to reduce the network traffic?

Ans- Replication of data in distributed enviroonment.
Using snapshots to replicate data.
Using remote procedure calls.

Q – 4 What is snapshots?

Ans- snapshot is nothing but a consistance data for master table if any chance the master table snapshot is fatch the updated data for master table.

Q – 5 Do you know What are the various type of snapshots?

Ans- snapshots is three types of complite,fast and force.

Q – 6 Differentiate the simple and complex, snapshots?

Ans- A simple snapshot is based on a query that does not contains GROUP BY clauses, CONNECT BY clauses, JOINs, sub-query or snashot of operations.

– A complex snapshots contain atleast any one of the above.

Q – 7 What is dynamic data replication?

Ans- Dynamic data replication is feture of oracle actualy the to more database ex:- this is production database and other database with and synconoze with the production databse throuth the dynamic replication. if any change the proction database the symaltantinasly change the other database the replication. the benefite the the user can access any were ether prodction or syn with other database.

Q – 8 How to Enforce Refrencial Integrity in snapshots?

Ans- Time the references to occur when master tables are not in use. Peform the reference the manually immdiately locking the master tables. We can join tables in snopshots by creating a complex snapshots that will based on the master tables.

Q – 9 Explain What are the options available to refresh snapshots?

Ans- COMPLETE – Tables are completly regenerated using the snapshot’s query and the master tables every time the snapshot referenced.

FAST – If simple snapshot used then a snapshot log can be used to send the changes to the snapshot tables.

FORCE – Default value. If possible it performs a FAST refresh; Otherwise it will perform a complete refresh.

Q – 10 Explain snapshot log?

Ans- It is a table that maintains a record of modifications to the master table in a snapshot. It is stored in the same database as master table and is only available for simple snapshots. It should be created before creating snapshots.

Q – 11 Explain When will the data in the snapshot log be used?

Ans- snapshort log is used updated data for master table and if any recored is updated after certen peried to referse the master table collect the snapshot log. because tha table size is very large it take lot of time. thus by ues the snapshot log.

Q – 12 Explain What are the benefits of distributed options in databases?

Ans- Database on other servers can be updated and those transactions can be grouped together with others in a logical unit. Database uses a two phase commit.

Q – 13 Tell me What is different between view and sequence?

Ans- A view is a logical table based on a table or another view. A view contain no data itself but it is like a window through which data from tables can be views and changed. A sequence is a user created database object that can be shared by multiple user to generate unique integers.

Q – 14 What is the step for rmanbackup?

Ans- RMAN is an Oracle tool for taking the backup and recovering the databases. You can copy, restore, and recover datafiles, control files and archived redo logs (all 3 files or any one file). It has command line utility as well as GUI-based Enterprise Manager Backup. Here we are discussing the command line utility.

For example, we have TEST and PROD databases. Here TEST is the catalog database and PROD is the target database for which the backup has to be taken. You may loose your backup if you have your catalog and target databases on the same box and the box crashes .So it is always advisable to keep a separate database for RMAN catalog .

Create two databases. One for RMAN catalog(Test) and one target(PROD). Both the Databases should be archive log mode (for security).

Q – 15 What is meant by physical order and logical order in clustered index and non clustered index?

Ans- It is mean it is primary key column

Q – 16 How to prevent unauthorized use of privileges granted to a Role?

Ans- By creating a Role with a password

Q – 17 What is default tablespace in Oracle Security?

Ans- its the tablespace in to which the objects created by a user go.default tablespace can alloted to a user while creating he user or can also be changed after creating the user. the default table space of a user is system tablespace unless mentioned specfically while creating the user.

Q – 18 What is Tablespace Quota in Oracle Security?

Ans- amount of tablespace that can br used by a user for creating his own database onjects is called talespace quota.ita a way of limiting the usage of memory or disk space by user or restricting him by giving a fixed disk space.

Q – 19 What is a profile in Oracle Security?

Ans- Profile is information contain that perticular modules.

Q – 20 Tell me What is user Account in Oracle database?

Ans- An user account is not a physical structure in Database but it is having important relationship to the objects in the database and will be having certain privileges

Q – 21 How to enforce security using stored procedures?

Ans- Don’t grant user access directly to tables within the application.

Instead grant the ability to access the procedures that access the tables.

When procedure executed it will execute the privilege of procedures owner. Users cannot access tables except via the procedure.

Q – 22 Explain What are the dictionary tables used to monitor a database spaces?

Ans- DBA_DATA_FILES is view to show the space for the tablespace if you want the calulate the space of the database the all tablespace size is the database size.

Q – 23 What are the main responsibilities of a Database Administrator?

Ans- Depending on the company and the department, this role can either be highly specialized or incredibly diverse. The primary role of the Database Administrator is to adminster, develop, maintain and implement the policies and procedures necessary to ensure the security and integrity of the corporate database. Sub roles within the Database Administrator classification may include security, architecture, warehousing and/or business analysis.

Other primary roles will include:

1.Deciding the h/w devices to be used
2.Deciding the user and data to be used by user.
3.Deciding the information content data base system.
4.Deciding the data dictionary contain the data in data.
5.Deciding the validation check on data.
6.Deciding the backup and recovery.
7.Monitoring the response to be changing environment.

Q – 24 Can you explain What are the roles and user accounts created automatically with the database?

Ans- DBA – role Contains all database system privileges.

SYS user account – The DBA role will be assigned to this account. All of the basetables and views for the database’s dictionary are store in this schema and are manipulated only by ORACLE.

SYSTEM user account – It has all the system privileges for the database and additional tables and views that display administrative information and internal tables and views used by oracle tools are created using this username.

Q – 25 Tell me What are the database administrators utilities avaliable?

Ans- So many database utilities are avalibale

1. SQLPLUS
2. RMAN
3. TKPROF
4. SQLLODAR
5. EXP,IMP AND EXPDP,IMPDP
6. EXPLAN PLAN
7. LSNRCTL
etc.

Q – 26 What are the minimum parameters should exist in the parameter file (init.ora)?

Ans- DB NAME – Must set to a text string of no more than 8 characters and it will be stored inside the datafiles redo log files and control files and control file while database creation.

DB_DOMAIN – It is string that specifies the network domain where the database is created. The global database name is identified by setting these parameters (DB_NAME & DB_DOMAIN)

CONTORL FILES – List of control filenames of the database. If name is not mentioned then default name will be used.

DB_BLOCK_BUFFERS – To determine the no of buffers in the buffer cache in SGA.

PROCESSES – To determine number of operating system processes that can be connected to ORACLE concurrently.
The value should be 5 (background process) and additional 1 for each user.

ROLLBACK_SEGMENTS – List of rollback segments an ORACLE instance acquires at database startup.

Also optionally

LICENSE_MAX_SESSIONS,LICENSE_SESSION_WARNING and
LICENSE_MAX_USERS.

Q – 27 What are roles? How can we implement roles?

Ans- If a user wants to give some privileges to another user or a group of users then they will create role and they give this role to the users.let the privileges be ‘insert’ and ‘update’ on ’emp’ table then instead of specifying them by their names they will create role which
inturn includes these methods. this role is further given to the users.

If in futher if he wants to remove some privilege given to the users, he can just alter the role and remove that particular privilege such as he can remove update from this created role..

Later the users cant do updations on that emp table.but still can insert the values into that table. Hence Roles are the easiest way to grant and manage common privileges needed by different groups of database users.Creating roles and assign each role to group of users will simplify the job of assigning privileges to individual users.

Q – 28 Explain What are the steps to switch a databases archiving mode between NO ARCHIVELOG and ARCHIVELOG mode?

Ans- To check database is in which mode

1. Conn sys/sys as sysdba
2. select log_mode from v$database

To set database in Archivelog mode

1. shutdown immediate;
2. startup mount;
3. alter database archivelog;
4. alter database open;

To set database in NoArchivelog Mode

1. shutdown immediate;
2. startup mount;
3. alter database noarchivelog;
4. alter database open;

Q – 29 How to enable automatic archiving?

Ans- 1. shutdown the database with normal/emmidiate/transactional
2. modify/include the parameter log_archive_start=true, log_archive_dest= (location to which the redo log file group is archived), log_archive_format=(in which format you want to apper the archive log file)
3. start up the database