Oracle 12c Database Interview Question And Answer


Who is an Oracle DBA?
Each organization has a lot of valuable information that needs to be saved in an organized manner. This is achieved using DBMS(Database Management System). Many companies have come out with a variegated products for providing DBMS functionality. As we confine this blog to Oracle databases it is mandatory to have an idea on who an Oracle DBA is.
An Oracle DBA is a professional who administers the Oracle DBMS. He is responsible for maintaining and protecting the valuable information upon which the entire business depends. This gives an view of the crucial responsibility of a DBA. To be a proficient DBA, it is mandatory to have a good grep on the concepts, features of the oracle database
oracle 12c interview questions will help dba crack upcoming database administrator interview with full confidence. Oracle database 12c being the latest version of oracle database is a mandate for all new oracle dba, middle level dba, senior oracle DBA’s. Start learning now :
1) What is the basic architectural enhancement in Oracle 12c database?
Until 11g Oracle supplied data, metadata, user/schema data and metadata are all stored as single database. Starting with 12c Oracle defines an interesting architecture called the pluggable database architecture
2) Explain the basic concept behind Oracle 12c pluggable database architecture:-
Pluggable database architecture separates Oracle supplied data and metadata from user created data and metadata
Oracle supplied data and metadata is stored in container database
User supplied data and metadata is stored in pluggable database
3) What is the major advantage of Oracle 12c pluggable database?
One container can contain many number of pluggable databases. This allows public synonyms and database links to be defined at pluggable database level
4) What is difference between traditional exp/imp vs datapump utilities:-
Oracle database utilities expdp/impdp is a replacement of old exp/imp utilities and there are few basic differences
1) exp/impdp are faster than exp/imp
2) No need to create directory in case of exp/imp
3) Oracle Object reorganization
Oracle 11g two methods to reorganize the objects
1) In-place reorganization helps us re-organize objects on the fly
2) Copy-based approach – Copies information on to an intermediate object make changes and applies it back to original object
5) What is a deadlock?
A deadlock can occur when two or more users are waiting for data locked by each other
Deadlock prevent some transactions from continuing to work
Oracle database automatically detects deadlock situation an resolves them by rolling back one of the statements, involved in the deadlock, thereby releasing one set of conflicting row locks
Deadlocks can affect the performance of database
6) How to determine optimal size of redo log file in Oracle database?
It is possible to determine the optimal sizing of the redo log file in the system using the dynamic view v$instance_recovery.select optimal_logfile_size from v$instance_recovery will populate the value in this column automatically depending on the DML load in the system. this can be made use of optimally.
ORA-00205: error in identifying control file, check alert log for more info
There was a problem while opening oracle database instance and there was a problem with control files. This problem can be fixed by recovery of control files using RMAN tool.Here are the controlfile recovery steps from RMAN
RMAN> set DBID dbid_Value;
7) How do you determine the current_scn value in oracle database?
We should first understand what a SCN aka system change number is. This is an oracle clock. The clock ticks and increments everytime a commit statement is issued in an oracle database. As the SCN is related to database value of current SCN can be obtained from v$database view
select current_scn from v$database;
8) Where do I get details on dbid values?
You can get it from previous backup logfile if any. If not check the value next to ctrl_c-. The value that follows hyphen is the dbid value of the database
RMAN> startup nomount;
RMAN> restore controlfile from ‘controlfile_backup_location’;
9) What if I dont have details on backup location?
Try using autobackup option from RMAN prompt as follows:
RMAN> restore controlfile from autobackup;
If you have details on tag name , make use of the tag details as follows:
RMAN> restore controlfile form tag ‘controlfile_tag_name’;
RMAN> alter database mount;
Now, log into sql prompt and open the database as follows:
alter database open;
10) Give details on transportable tablespaces:-
Oracle’s transportable tablespaces feature offers us an easy way to move large amounts of data between databases efficiently by simply moving data files from one database to the other
It is the Oracle recommended method to move large volumes of data across databases as it is efficient in terms of speed
It enables us to move large objects in a fraction of time effortlessly
DBA Views Managing Users,Roles,Privileges:
dba_users – provides information about users
dba_roles – shows all the roles in the database
dba_col_privs – shows column-level object grants
dba_role_privs – shows users and their roles
dba_sys_privs – shows users who have been granted system privileges
dba_tab_privs – shows users and their privileges on tables
role_role_privs – shows roles granted to roles
role_sys_privs – shows system privileges granted to roles
role_tab_privs – shows table privileges granted to roles
session_privs – shows privileges currently enabled for users
session_roles – shows roles currently enabled for users
11) Common Resumable Errors/ Resumable Errors:
Following are some frequent errors which need resumable operations:
Out Of Space Error/ORA-01653– Tablespace is full. Operations fail because we can’t add extents to tables and indexes. We need to add a datafile to the tablespace to fix this error
Maximum Extents Errors/ORA-01628 – When a table or a rollback segment reaches the maximum extents specified, it can’t grow any further even if we have space in the tablespace
User’s space quota errors/ORA-01536 – If the user’s quota on a tablespace is exceeded, our operations on the tablespace will come to a halt
We can name an operation to help track it later on
The NAME parameter is optional and has no real significance
ALTER SESSION ENABLE RESUMABLE NAME ‘test_name’;
SQL Scripts Embedded in Shell Scripts:
Application upgrade usually takes place on a quartely basis/monthly basis in many corporations. It involves sequence of steps from DBA’s which involves creation of new database objects,granting privileges,creating triggers,PL/SQL functions, procedures etc.
Most of the upgrade steps can be automated using Shell scripts. Tasks relevant to database can be automated by creating SQL scripts. It is possible to embed an SQL script within a shell script.

#!/usr/bin/sh

VARIABLE1 = `sqlplus -s username/password << ENDOFFILE sql commands; ENDOFFILE` The VARIABLE1 value can be now manipulated in shell script Dynamic Performance Views (v$ views): Dynamic Performance Views also called as v$views are used to obtain information about the currently running instance. We can ontain complete list of data dictionary tables using the following command: SQL>select * from dict;
Data corruption and tools to detect the errors:
Regular backups of production database is important.Backups must be always usable.Backups may become unusable due to following reasons – corrupt data files and redo logs, accidentally overwritten files, defective tapes
Test production backups regularly.Corruption – database backups inconsistent. May be physical or logical corruption.Mcdia errors are Caused by range of factors from user errors to errors in OS, bad disks, LVM error, memory error.This may corrupt control file, redo log file, data dictionary, table data, index data
We can prevent the data corruption in following ways :
Set DB_BLOCK_CHECKSUM=typical(default)/full
DB_BLOCK_CHECKING =ON/OFF
SQL>ANALYZE table tablename VALIDATE structure;
Dbverify utility:
$dbverify file=‘filename’
Use DBMS_REPAIR Package
Data dictionary views help in monitoring and managing data pump jobs. The views and their usage is given below:
DBA_DATAPUMP_JOBS – Summary information of all currently running data pump jobs
DBA_DATAPUMP_SESSIONS – Identifies user sessions currently attached to a Data Pump export or import job
To obtain session information use V$SESSION, V$SESSION_LONGOPS
V$SESSION_LONGOPS – Monitor Long running sessions
ORA-00955: name is already used by an existing object:
I tried creating a new table with name t1. I got the error ORA-00955: name is already used by an existing object
I had another object with the same name. I queried dba_objects data dictionary view to obtain information regarding the object. I dropped the object. The problem got fixed.
Modify Table Structure And Column In Table With Data:
I created a table with two column. One of the columns is of LONG data type. I inserted values into the table. I modified the column value using ALTER TABLE tablename MODIFY command. It is interesting to know that the table can be altered.
SQL> create table test_readonly(
2 id number,
3 testlong LONG);
Table created.
SQL> insert into test_readonly values(10,’LONG Value example’);
1 row created.
SQL> desc test_readonly;
Name Null? Type
—————————————– ——– —————————
ID NUMBER
TESTLONG LONG
SQL> select * from test_readonly;
ID
———- TESTLONG
————————————————–
10 LONG Value example
SQL> alter table test_readonly modify (testlong CLOB);
Table altered.
SQL> desc test_readonly;
Name Null? Type
—————————————– ——– ——————–
ID NUMBER
TESTLONG CLOB
SQL> select * from test_readonly;
ID
———- TESTLONG
————————————————–
10 LONG Value example
Invoking SQL*Loader:
We can invoke SQL*Loader utility in a number of ways. the standard syntax for invoking SQL*Loader utility is as follows:
SQLLDR keyword=value [,keyword=value,……]
An example showing how to invoke SQL*Loader is as follows:
$sqlldr USERID=/ CONTROL=<controlfilepath(.ctl>
DATA=<.dat file path> LOG=<.log file path> ERRORS=0 DIRECT=
SKIP=<value(23456)> RESUMABLE= (or)RESUMABLE_TIMEOUT =
How can you create table in database?

Table is the most basic object in a database. A table can be created using the Data Definition Language (DDL) statement CREATE TABLE
CREATE TABLE TABLENAME (columnname datatype, …); is the most basic syntax for creating a table. A table is like a file cabinet storing information.
Once the table is created it is possible to confirm its existence
We can find information on the columnnames, datatypes of the table

1) What tasks do you perform on daily basis as a DBA?
The day of an Oracle DBA starts with checking emails that will have details on support tickets raised from customers for that day
Based on the issue escalated, we need to contact the personnel and collect the needed data. Lets say for example if the issue is generic stating that system is slow owing to database we first need to collect the reports from database for the specified timeframe. This will include logs like traces, alert.log, AWR , ADDM performance reports, tkprof output for specific sql etc
Second main thing a DBA should do on daily basis is to check and make sure RMAN backups are valid. If the environment is big typically full backups would have been scheduled once or twice a week. Incremental will happen twice a day or once a day. Make sure these backups are VALID. Look at rman.log the logfile onto which backup output is present
Periodically check for database statistics as part of database health check. Make sure statistics are up-to-date
Participate in disaster recovery activity that happens once in three months. This involves restore and recovery of backups from tapes, local disks etc and make sure backups are 100% VALID. This is a major business continuity practice
Work with developers who complain issue with queries. Performance tuning comes into picture here
Create users based on request from managers for new joiners. Remove database user accounts of employees who have left the employer
Participate in security audit and make sure the system is 100% safe and secure in compliance with law
Be part of system patching, perform clean stop and start of database as well as applications communicating with database
Patch database on as needed basis
Perform evaluation of critical patch updates and recommend patches in relevance to their impact on system
Evaluate upcoming version, understand new features and determine best upgrade path with minimal downtime
If the client project uses high availability solution like RAC, dataguard support such applications
Perform logical backups using tools like datapump to create test system, UAT, new dev system after a major prod upgrade
2) We have a database that is about 3TB in size. It used to take 8 hours for full backup using RMAN during weekly full backup schedule. From last week onwards this backup has been taking closer to 20 hours for completion. What is going on?
Check and make sure the incremental backups are happening properly as scheduled in between the one week timeframe
Make sure there s no change in network used for performing backup. Involve network admin to make sure there are no bottlenecks with network bandwidth
If disks are used as channel destination for storing backups work with storage admin (or) system administrator and make sure the speed of disk write is not a problem
RMAN depends on memory. During the rman job run make sure sufficient space is available in memory for RMAN jobs to run. Check and see no additional (or) new jobs run during this schedule
3) Can you have indexes on external tables?
Nope. DML operations are not allowed in external tables. We can have synonyms , views on them
4) Which database view will you make use of for checking a past performance problem?
The v$active_session_history can be made use of for this purpose
5) How to change Default Tablespace in oracle database?
When an oracle database is created a default tablespace is created. we can create a tablespace at a later point of time and change it to be the default tablespace. Tablespace is composed of data files which is a physical file at the operating system level. Tablespace is a logical database structure which is a collection of one or more datafiles. Here are the steps to create a new tablespace and assign it to be the default tablespace
SQL> create tablespace test datafile ‘C:\oracle\learnersreference.dbf’ size 500M;
Tablespace created.
SQL> alter database default tablespace test;
Database altered.
6) How to Investigate Problems Database Control ADR in Oracle Database?
In Oracle Database a new diagnostic framework has been introduced. Automatic diagnostic Repository, ADR as it is popularly called is the repository of problems and incidents from Oracle database 11g system onwards. Problems are issues related to oracle database and oracle instance. It can take form of Oracle errors with ORA- prefix. Incident is the occurrence of a problem. Problems and incidents are grouped together into incident packages.
This fault diagnostic framework is managed by a command-line tool ADRCI. We can easily manage this framework using Oracle Enterprise Manager Database Control GUI. Here are the steps to investigate problems and incidents using Database Control 11g
1) Log into Database control as SYS user
2) Click on Software and Support
3) Click on Support Workbench link under Support Section
4) We finally land in a page where in incidents and problems if any are reported as links. If we click on the links we get details regarding them. The drop down box lets us filter incidents and problems recorded in different time slots – Last 24 hours, Last 2 days, Last week,All Active, All
We see that there are no problems recorded in ADR. These steps can be issued command-line using the utility ADRCI and reports the same results
7) What is Structured Query Language (SQL) ?
If you are a database beginner you might have heard your seniors discussing the term SQL more frequently. So, what is a SQL? SQL is nothing but structured query language. In simple terms, query is asking for something. structured query is asking for something from a database is an organized way.
SQL is the language in which we can connect with and speak to databases. SQL has become a mandatory skillset of major job roles starting from technical writer, data analyst, junior DBA, all developers (particularly website developers) etc. So, we’ve decided to spice our website with more technical articles. We’ll be discussing a lot more on SQL in our future posts
8) Give details regarding the concept of problems and incidents in the fault diagnosability infrastructure for Oracle Database from 11g onwards?
Oracle fault diagnosability has improved a lot ever since oracle database 11g. Every problem has a problem key, which is a text string that describes the problem. The database makes an entry into the alert log file when problems and incidents occur. Always, alert_sid.log (or) alert.log is the first point of reference for any issue investigation. The database sends an incident alert to the Oracle Enterprise Manager Database Home page. This is available in GUI as a highlighted alert
9) How will you enable Direct NFS?
1. Mount all required file systems using the kernel NFS driver.
2. Replace the ODM library libodm11.so_stub with libodm11.so.
10) You are managing an Oracle Database database with the ASM storage. The database is having big file tablespaces. You want files to open faster and less memory to be used in the shared pool to manage the extent maps. What setting will you set to achieve this?
ASM compatibility attribute for the ASM disk group should be set to 11.1.0 and setting COMPATIBLE initialization parameter for the database instance to 11.1.0 will help us accomplish this
11) You are querying the table details from dba_xxxx dictionary views. Is the table name case sensitive during the search?
Table name supplied while querying dba_data dictionary tables are case sensitive. They fetch different results.
SQL> select tablespace_name,file_id,extent_id,block_id,blocks,bytes from dba_extents where segment_name=’TESTTABLE’;
TABLESPACE_NAME FILE_ID EXTENT_ID BLOCK_ID BLOCKS
—————————— ———- ———- ———- ———

BYTES
———-
SYSTEM 1 0 88168 8
65536
SQL> select tablespace_name,file_id,extent_id,block_id,blocks,bytes from dba_extents where segment_name=’testtable’;
no rows selected
12) What is a data control language in Oracle database?

Data Control language popularly called as DCL in Oracle SQL  is primarily used to control the privileges granted to an user and revoked from an user. Most popular DCL commands are GRANT and REVOKE.
GRANT command – as the name implies is used to grant privilege, role which is set of privileges to the users
REVOKE Command – Used to revoke the access rights/privileges/roles from the user
In general DBA grants and revoke privileges, roles on various objects to users. Users can grant access on the objects owed by them to other users. Correspondingly they are referred to as grantee and grantor respectively
This is a hierarchical relationship  that gets created based on grants offered. So, if DBA revokes access from a user, all the access rights given by that user to another user are automatically revoked
SQL> grant select on dcl to practice;
Grant succeeded.
SQL> connect practice/practice
Connected.
SQL> select * from sys.dcl;

ID
———-
1
1002
1030
13) Can privileges be inherited?
Privileges can’t be inherited. An user can grant access privileges only in objects owned by him. He can’t grant access on SYS objects
SQL> grant select on sys.dcl to test;
grant select on sys.dcl to test
*
ERROR at line 1:
ORA-01031: insufficient privileges
14) Database security is a major function of user management. How does a DBA accomplish this?
Oracle database security involves managing user accounts, implementing password expiration and complexity rules, configure security policies using object, system and role privileges.auditing is also an integral part of Oracle user management.
This process involves monitoring and managing database access.Auditing involves fine tuning security policy, identify attempts to access to the areas of the database that the user is not supposed to visit, identify intrusion attempts.
15) Give details on tablespace assignment to users in oracle databases :-
Every user is assigned a default tablespace. Default tablespace of an user is the location wherein schema objects are stored when no tablespace clause is specified in the statements that create table or indexes
16) What happens when no default tablespace is assigned to an user?
When we don’t specify a default tablespace for an user account, the database’s default tablespace becomes the user’s default tablespace
Create user username identified by password default tablespace ts_name; – Assign a default tablespace to the user at the time of user creation
Alter user username default tablespace tsname; – assign default tablespace to an user after the user account is created
17) What command is used to change the database’s default tablespace ?
Alter database default tablespace tsname;
18) What are the logical structures in Oracle?
Data blocks, extents, segments and tablespaces.
19) What are the physical structures in Oracle?
Data files, control files, log files, Password file, Parameter file(SP file).
20) What is an oracle instance/what are the components of an Oracle instance?
Oracle memory structures and a set of database processes (set of processes created by oracle on server)constitute the oracle instance.
21) What is the use of oracle instance?
Oracle instance performs all the work for us in the database.
22) What is a oracle database?
An oracle database consists of files, both data files and oracle system files. These files interact with OS (operating system) which provides resources and processing capabilities such as memory to enable us to manipulate the data on the disk drives.
23) Are oracle instance and oracle database the same?
No, they are different. Oracle database in itself is ineffective without an instance.
24) What is meant by “database is down”?
Actually it means oracle instance is down (i.e) memory structures are not allocated and oracle processes are not created in server. Oracle database is futile when instance is down. To manipulate data in oracle database instance needs to be up and running.
25) Can we use oracle database when “instance is down”?
No, not possible as instance is needed to manipulate data in set of files(oracle database).
26) Can oracle instance function independent of oracle database?
No, Oracle instance by itself is a set of processes and memory structures and is useless without an oracle database.
27) What is a data block?
The smallest logical unit of an oracle database is the data block. Data blocks are defined in terms of bytes. We can size data blocks in units of 2KB,4KB,8KB,16KB,32,KB(or even larger chunks). It is common to refer to the data blocks as oracle blocks.
The storage blocks on which oracle blocks(data blocks) reside are themselves divided into disk blocks which are areas of contiguous storage containing a certain number of bytes -for example 4096bytes or 32768 bytes (4KB or 32KB)(!KB=1024bytes)
28) How will you locate database tables physical location from sql commands?
Tables are logical data structures in an Oracle database. They are formed of rows and columns that are physically laid out of OS files formatted on disks.Oracle provides set of data dictionary views to identify the physical location of database objects like table in a database.(OS file system).Some most commonly used views are:
1) dba_extents
2) dba_tablespaces
3) v$datafile
To locate a table in a database – extents,blocks that comprise the table issue the following commands.
1) Find the extents,blocks,bytes allocated to the database object (table) by issuing the following query:
SQL> select tablespace_name,extent_id,file_id,block_id,bytes from dba_extents where owner=’SYS’ and segment_name=’table-name’;
Note: Segment name is the name of the database object to be located.
2) Find the datafile to which the extent belongs using file_id.
SQL> select name from v$datafile where file#=&file_id;
3) To identify the point in the tablespace at which the extent begins issue the following command:
SQL> select block_size * &block_id from dba_tablespaces where tablespace_name=’&tablespace_name’;
Give details on Oracle PL/SQL Parameter Types:-
Oracle PL/SQL program units could be procedure , function. They are named PL/SQL blocks as opposed to anonymous PL/SQL blocks. We can call the procedures from another program and this porcess is called as invoking the procedure. The parameters passed during procedure call is the formal parameter. The parameters accepted and used in procedure is the actual parameter. Procedure is stored in database as an object similar to tables, views etc.Some interesting pointers on formal and actual parameters
1) Formal and actual parameters should be of compatible datatypes.PL/SQL converts the datatype of actual parameters to that of formal parameters
2) Actual parameters could be numbers, alpha numerical values, expressions using mathematical operators etc. This is the parameter passed from invoking program
1) How do you create output of a query in html report format?
set markup html on
spool outputfile.html
sql query whose output is needed # select * from dual; Say
spool off
set markup html off
2) How to get query output in text format?
spool output.txt
select * from dual; #sample
spool off
3) Is there an oracle supplied PL/SQL package to manage external file?
UTL_FILE package contains the features needed to open file, write content to it, close file
DBMS_OUTPUT is another package to collect diagnostics information, error messages, write information etc during report generation
4) How is an external binary file referenced?
Create a physical directory at operating system level, create a directory path reference at the oracle database. BFILE datatype can be used as a pointer to access external data
5) How are C++/Java codes supported?
Oracle database makes data exchange possible using DBMS_PIPE
What command will you make use of to  identify which datafiles are needed for recovery ?
We need to perform a restore and recovery when we lose a control file or data file
Database becomes shutdown even if one of the multiplexed copies of the control file becomes unavailable
SQL> select file#, error, online_status, change#, time from v$recover_file;
What command will you make use of to get information about the number of files in the flash recovery area, how much space is currently being used, total amount of space available in the flash recovery area?
We need to ensure that flash recovery area is large enough for our needs
SQL>SELECT * FROM v$RECOVERY_FILE_DEST; –
SQL> select * FROM v$FLASH_RECOVERY_AREA_USAGE;
How will you move flash recovery area to new location?
We can move the flash recovery area as follows : – SQL> ALTER SYSTEM SET DB_RECOVERY_FILE_DEST=‘newlocation’;
Give details on PL/SQL Object Types Oracle Database ?
PL/SQL is the procedural language commonly used with Oracle Database. It is also sometimes referred to as database programming language. PL/SQL comprises of objects. Some common PL/SQL objects as of Oracle database 11g are as follows :
1) Packages
2) Procedures
3) Function
4) Package Body
5) Trigger
6) Type Body
All the PL/SQL objects are schema objects. They are stored within the data dictionary. Procedures and functions are subprograms. Procedures and functions grouped together give rise to packages. Triggers are used to trigger a specific event when issued against a table such as log on, log off, system shutdown etc
Give details on MINUS Operator Oracle SQL :-
MINUS Operator returns rows in the first query that are not present in the second query.First SELECT statement – SECOND SELECT Statement
The number of columns and the datatypes of the columns being selected by the SELECT statements in the queries must be identical in all the SELECT statements used in the query. The names of the columns need not be identical.All of the columns in the WHERE clause must be in the SELECT clause for the MINUS operator to work.
Give details on DBMS_SHARED_POOL Package:-
DBMS_SHARED_POOL PL/SQL Package is an Oracle supplied PL/SQL package.
It is used to find the size of stored PL/SQL objects. It can be used to find objects so that we can pin and unpin those objects.
This prevents the shared pool fragmentatio problem.Shared pool fragmentation problems are caused by problems in loading large packages
Give details on Transaction Concurrency Control in Oracle Database :-
Transaction concurrency is achieved by managing various users simultaneous transactions without permitting any interference among them
If we’re the only user of the database, we don’t need to worry about concurrency control of transactions
In real-time systems thousands of users work with the database performing simultaneous SELECT, INSERT, UPDATE, DELETE transactions against the same table
One solution to concurrency control is to lock the entire table for the duration of each operation, so one users transaction doesn’t impact another
Thus, each user would be operating in isolation
Oracle uses locking mechanism to keep the data consistent
Concurrency increase the throughput of RDBMS
Some common transaction processing problems are :
1) Dirty-Read Problem
2) Phantom Read Problem
3) Lost Updates Problem
4) Non-repeatable reads problem
Give details on whole Open Backup in Oracle Database :-
Open backup is also called as open backup.Open backup implies that users are changing data while we’re backing up files, and this leads to the use of more complex mechanisms on behalf of the Oracle server to perform the backups
We need to backup all the datafiles, control files, archived redo logs for a complete online database backup
We use OS cp command to achieve this
SQL> ALTER DATABASE BEGIN BACKUP;
Copy all the files that are part of all tablespaces in our database : SQL> host cp source.dbf destination.dbf
SQL> ALTER DATABASE END BACKUP; – After we backup all the data files, we end the online backup
Give details on function-based indexes in oracle database :-
Function-Based Indexes precompute functions on a given column and store results in a index.Function-based indexe is the ideal way to index a column when functions are included in WHERE clause.
SQL> CREATE INDEX indexname ON table-name(UPPER(column-name)); [functionname(col)];
This will create index on column-name
Give details on physical and logical structures in oracle database :-
Physical database structures – Data files, control files, redo log files
Operating system files are used by Oracle to manage its operation – Initialization files (init.ora, SPFILE), network administration files ( tnsnames.ora, listener.ora), alert log files, trace files, password file
Logical Database structures – used to manage the physical storage that is allocated in the form of operating system files
Logical structures – data blocks, extents, segments, tablespaces
What is Heterogenous Connectivity?
Heterogenous connectivity is used to administer distributed database systems that involve Oracle to non-Oracle database links.Oracle provides synchronous and asynchronous solutions to address the issue.
What is an Oracle Gateway?
Oracle gateway is an synchronous solution for heterogenous connectivity.Give few asynchronous solutions:-
Oracle streams, Messaging gateway,Open system interfaces.
How is heterogenous connectivity provided by oracle?
Heterogenous services is a server side service that integrates oracle gateway with heterogenous service.
What is a local database?
User connects from client to server and the database that user logsin is the local database.
What is a remote database?
The database to which user connects after connecting to local database is the remote database.
How is local database connected to remote database?
Creating database links establishes connectivity between local and remote databases
You are managing an Oracle Database with the ASM storage. The database is having big file tablespaces. You want files to open faster and less memory to be used in the shared pool to manage the extent maps. What configuration is needed to achieve this requirement?
Set the ASM compatibility attribute for the ASM disk group to appropriate database version
Set the COMPATIBLE initialization parameter for the database instance to correct database version
Which step is mandatory to enable Direct NFS?
Create an oranfstab file containing the attributes for each NFS server to be accessed using Direct NFS. Replace the ODM library libodm11.so_stub with libodm11.so.
Give details regarding the concept of problems and incidents in the fault diagnosability infrastructure for Oracle Database :-
Every problem has a problem key, which is a text string that describes the problem. The database makes an entry into the alert log file when problems and incidents occur. The database sends an incident alert to the Oracle Enterprise Manager Database Home page
From where should you execute the script @utlu111i.sql during 11g upgrade?
It must be executed from the environment of the database that is being upgraded.From where should you execute the script @utlu111i.sql during 11g upgrade?
It must be executed from the environment of the database that is being upgraded
You are managing an Oracle Database instance with ASM storage. The ASM instance is down. To know the details of the disks in the DATA disk group, you issued the following ASMCMD command lsdsk -I -d DATA. What would be the output of this command?
The command succeeds and retrieves information by scanning the disk headers based on an ASM_DISKSTRING value
Give details on the capabilities of the DBMS_NETWORK_ACL_ADMIN package :-
This is a user access and privilege management package as the name suggests access control lists and it is self-explanatory. It can be used to allow the access privilege settings for users as well as roles. It can be used to control the time interval for which the access privilege is available to a user. It can be used to selectively restrict the access for each user in a database to different host computers
What are the uses of AMBR feature?
AMBR represents ASM metadata backup and restore feature that is used to re-create the ASM disk group with its attributes. This feature can be used to gather information about a preexisting ASM disk group with disk paths, disk name, failure groups, attributes, templates, and alias directory structure
At the end of an upgrade you have been asked to run the script utlrp.sql. Why is this script run?
It performs parallel recompilation of any stored PL/SQL as well as Java code