1) You have recently upgraded your database and see degradation in performance of jobs. You have been asked to collect details on level of parallelism,total database time, number of I/O requests consumed by these jobs. How will you accomplish this?
Making use of ream-time database operation monitoring as follows :
dbms_sql_monitor,report_sql_monitor – This provides the information needed
2) Give some advantages of using unified auditing :- The latest feature in oracle database the unified auditing lets us capture audit information from many different sources like SYS audit records, fine-grained auditing using dbms_fga package, oracle datapump, sql*loader etc. Some advantages of using unified auditing includes : a) Decreased use of storage to store the audit trail rows in the database b) It improves overall auditing performance c) It automatically audits recovery manager events the RMAN events
4) You create two indexes on a table. One index is created with INVISIBLE clause, another index is a normal index. What happens when a DML operation is performed in underlying table ?
In case of DML operations like insert, update, delete in underlying table both the visible and invisible indexes are updates
5) What is the prerequisite for performing flashback transaction?
Undo retention guarantee for the database must be configured
Execute privilege on DBMS_FLASHBACK package must be granted to the user flashing back transaction
6) The statistics_level parameter is pdf_modifiable. Which command will you issue to set statistics_level parameter to all?
Alter system set statistics_level=all sid=’*’ scope=spfile;
7) You have been asked to upgrade your database environment. Whom all should you involve as part of this meeting?
DBA the database administration team members, storage administrator who takes case of NAS/SAN hosting database files to get to know impact of upgrade, system administrator the OS managers, network administrators for safety purpose, solution architects who are involved in software development, team members who ave developed and implemented schema changes that come as part of upgrade
8) Give details on redefining a table with virtual private database VPD policies :-
In case of redefinition on a table :
a) Primary key constraint on the table is disabled
b) VPD policies are copied from original table to the new table during redefinition
9) What is the use of the procedures listed in move_procedure column of the v$sysaux_occupant view?
The procedures are used by some database components to relocate component data to the SYSAUX tablesapce from their current tablespace. In case of upgrades from older versions of oracle wherein some data is stored in sys tablespace (or) some design changes from latest versions that moved the components away from sysaux tablespace this comes handy
This information can also be used to relocate component data from sysaux tablespace to another tablespace
10) Which oracle process takes case of service registration with the listener?
PMON the process monitor takes care of this in a database instance
11) Your environment makes use of multitenant architecture with container database hosting two pluggable databases.The datafile for the default permanent tablespace belonging to one of the pluggable databases is corrupted. You have been asked to restore and recover datafile in this pluggable database. How will you accomplish this?
Take the default tablespace offline in the affected pluggable database before performing restore and recovery of the datafile
13) You have a multitenant container database hosting three pluggable databases.This database runs in archivelog mode. The SYSAUX tablespace is corrupted in root container. How will you recover the SYSAUX tablespace?
a) First take the sysaux tablespace offline
b) Restore the sysaux tablespace related datafile
c) Apply the archived redo logs
d) Bring back the SYSAUX tablespace online
14) Your oracle database environment is a multiprocess, multithreaded environment. What is the benefit of this?
Reduced virtual memory utilization
Improved parallel execution performance
Reduced CPU utilization
15) You have configured your oracle database environment to make use of shared server connections. What happens with this configuration?
a) Allocates memory for RMAN backup and restore operations
b) Allocates memory for shared and private SQL areas
c) Contains a cursor area for storing runtime information about cursors
17) You get a call from your customer that instance is hung and they dont want to restart the instance.The client mentions that this is an unexpected sudden degradation in database performance ever since today’s morning. What will you do to determine the cause of this issue in a live hung environment?
Make use of emergency monitoring to fetch data directly from SGA analysis
18) You have enables in-database archiving on the table. Which system generated hidden column is added to this table?
Once in-database archiving is enabled on a table a column by name ORA_ARCHIVE_STATE is added to the table. By default this column is populated with value 0 for each row
19) Your customer reports performance degradation but database is working fine. What will you do to investigate the issue real-time?
Make use of ADDM reports the automatic database diagnostic monitor by running command
The above command collects data from SGA, analyzes and provides report
Get details on timing of degradation from customer that is needed to generate this report
20) You have a service alias in tnsnames.ora that works fine with tnsping servicename command. However, you are not able to connect to the database while making use of this servce directly like connect username/password@service_name. How to fix this?
This issue happens because database service is not registered with the listener
Make use of command alter system register;
21) Your environment is undergoing audit. You have been asked to perform privilege analysis, check and revoke excessive unused privilege granted to users. How will you do this?
Create a policy to capture the privilege used by a user for privilege analysis
Start analyzing the data captured by the policy
Stop analyzing the data
Generate a report with the data captured for a specified privilege capture
Compare the used and unused privileges list
Revoke the unused privileges
22) In your initialization parameter file you have set Log_archive_Dest_1=’LOCATION=location_DEtails’. You have enabled ARCHIVELOG mode in your database. Are the archived redo logs stores in default location?
No. They are created in the location specified by this parameter
24) What are some of the functions performed by SQL Tuning Advisor?
Building and implementing SQL Profiles
Checking query objects for missing and stale statistics
Recommending the restructuring of SQL queries that are using bad plans
25) When you set enable_ddl_logging=FALSE what happens?
The data definition language statements called DDL’s like create, truncate information is not logged in trace file
26) In your database environment you want to log DDL’s. How can you achieve that?
Alter system set enable_ddl_logging=TRUE;
27) You have yoru locally managed tablespace with free list managed segments. There is a performance problem and you run AWR report. From AWR report you observe high number of buffer busy wits. You determine that this is caused by contention on data blocks. You hae been asked to decrease this wait event immediately. What will you do?
Make use of automatic segment space management ASSM
28) You have to collect statistics on schema and a specific table. When a schema statistics is collected the table statistics should not be collected, this should be available for optimizer at parse time. How will you accomplish this?
29) You have been asked to unplug a pluggable database. What is the first step?
First the pluggable database to be unplugged must be closed
30) You have unplugged a pluggable database. Can you plug it back onto same container database?
Yes. We can do that
31) During upgrade process what happens if you execute catupgrd.sql script without executing pre-upgrade scripts in the earlier version of database?
We will get an error. This is because pre-upgrade script will update the database information in the history table that will be used by upgrade scripts
32) Give some examples of information that will be updated by running pre-upgrade script:-
One common information is the time zone information in the registry$history table
33) What is the meaning of ORA-01722 Timezone invalid number error?
This error occurs if upgrade scripts like catupgrd.sql is run without executing pre-upgrade scripts
34) You are executing catuppst.sql as part of database upgrade after running catupgrd.sql. what status do you want your database to be in?
Once the catupgrd.sql is run restart the database and then execute catuppst.sql script. This script is used to perform upgrade actions that don’t require the database to be in upgrade mode like execution of psot-PSU scripts
35) Where does catuppst.sql record upgrade activity?
This script records upgrade activity in registry$database table
36) Do we need to execute post-PSU script separately?
Not always. If the PSU is already installed in target database home before the upgrade this step can be skipped
37) You have been asked to patch your database environment with critical patch update that contain latest bug fixes. You downloaded the patch and see a file called inventory.xml and actions.xml. You move both these files along with readme.txt onto temp folder. You start patch installation using opatch. Will this work fine?
Nope. Among the files moved to temp folder bring back actions.xml file. This is a file that contains details on actions to be performed during patch installation. In simple terms this file talks about steps to be taken by opatch during patch installation like moving and replacing of files, re-linking etc. Opatch needs actions.xml file for patching to work fine
38) What is the major difference between catupgrd.sql script in 11gR2 versus 12c version of oracle database?
There are some major differences in how the catupgrd.sql gets executed in earlier and latest versions of oracle database
a) In 11gR2 catupgrd.sql script execution happens in a serial manner that will upgrade each database component one by one in a serial fashion. In 12c version it is parallel and expedites some upgrades like parallel upgrade of XDB database and such components
b) The serial execution takes more time in 11gR2 as compared to 12c
c) In case of issues during upgrade there was a need to terminate and re-execute this from scratch in versions prior to 12c
39) What is the different between inventory.xml in patch folder and inventory.xml inside contentsXML folder inside ORACLE_HOME?
The inventory.xml in patch folder has details on bugs included in that specific patch. The inventory.xml is the central Oracle Inventory location that stores details on all the oracle homes installed in that particular machine
40) You are installing latest patch for 12c in a server that has Oracle 11g and 12c installation in-place. Which inventory is updated upon patch install?
Local inventory file comps.xml inside ContentsXML folder for Oracle 12c home will be updated
41) You are installing latest patch and you have been asked to install opatch utility to start with. How will you do so?
Opatch utility is the tool from Oracle used to install oracle patches. There is no need to install this separately. This is automatically installed as part of Oracle software installation process and is located inside ORACLE_HOME/Opatch folder
42) Can you manually edit your local inventory file comps.xml inside ORACLE_HOME/inventory/ContentsXML/comps.xml?
The local inventory is an essentially very important component of oracle home and contains information about installed patches along with software version, components installed in that particular oracle home. This should not be edited or modified and can lead to corruption If corrupted these files need to be restored from backup if one is made (or) there is a need to install oracle software at a different location along with patches to match the database patch level
43) Your patch installation demands a latest version of opatch tool. You have downloaded the opatch binary and planning to install this in an oracle home and upgrade the opatch. How do you plan your downtime?
Opatch installation and upgrades does not involve any downtime. This is a binary that does not need any re-linking process
44) Does opatch version always need to match database version?
No need. This can be a number different than database version number. All we need to look for is to make sure opatch version does support a specified patch install and is compatible with specified database version which will internally be considered by oracle corporation during patch release
45) What should you do about statistics to minimize upgrade downtime?
It is a best practice to collect statistics before upgrade to reduce downtime as this will bring down execution time of uprade scripts. Connect to database as an user with sysdba privilege and run the following command :
46) What happens when dictionary statistics is not run before upgrade?
This should be okie as optimizer statistics will be collected for dictionary tables that lack statistics during upgrade automatically
47) You run a lower version of database. However, a database bug demands you to apply higher version of timezone patch. Now, you are planning to upgrade your database after few days. Is this something you need to make a note of while planning your upcoming upgrade?
Yes. For proper functioning of databases post upgrade it is to be noted that the target database version should consist of a time zone version that is same or higher than the source database
48) From where will you determine details on timezone version in an oracle database?
Make use of the query :
select filename,version from v$time_zone_file;
49) You are preparing your database for upgrade and one of the datafile is in backup mode. Can you start upgrade now?
Nope. For proper upgrade all the datafiles in the database should be in available status that ensures that the database is in consistent state. If not this might demand recovery
To double check run the following command before you kick start upgrade process :
select name,status from v$datafile;
The above command should return ACTIVE value in status column
52) What are the steps to change Online Redo Logfile in Oracle Database?
Online files which hold the information relevant to a database transaction is the online redolog file
LGWR writes information from redo log buffer in the memory to these files in the disk
Used to minimize the data loss
Used for recovery purposes
Redo log groups – atleast two member in a group
Oracle database has a set of online redo log files that are used to record the changes made to the oracle database.Changes recorded here will be eventually written to datafiles using oracle background process RECO. At times changes including changing the location of online redo log files, adding more member to the group can be done online
1) Get details on the status of many different files in the oracle database
select group#,member,status from v$log;
2) Drop the existing online redo log file that is archived and in INACTIVE status
alter database drop logfile group groupnumber;
Please note that above command entries of online redo logfile at the database level and not physically removed from the OS location
3) Add new online redologfiles to the database as follows
alter database add logfile(‘member1′,’member2′,’member3’) size value;
4) Perform log switch to activat the newly added member
alter system switch logfile;
5) Find the status and make sure that it works fine
select group#,member,status from v$log;
6) Remove all the physical logfiles from the OS location
Note that this is a good method to change the location of online redo log files
What happens when a patch is applied?
1) Oracle Services must be shutdown
2) DBA’s must be careful about applying patches within the specified downtime
3) System should be up and running once the maintenance window is over
4) Fallback procedures must be in place if there in any problem in bringing back the system
What is hot patching?
Hot patching is the high availability feature introduced in Oracle database
How do you call it an high availability feature?
Using hot patching DBA’s can install, enable and disable a patch online. There is no need for the oracle services to be shutdown
How do you do hot patching?
Hot patching makes use of the traditional opatch tool.
Can we use hot patch mode in Oracle database?
Hot patching is not universal in oracle. It depends on the patches being applied. Certain patches don’t support hot patching
How do you determine if hot patching is allowed?
Use the following commands :
1. opatch query -is_online_patch 2. opatch query -allWhich environment variable is critical before applying patched?
Environment variable PATH is critical and must be set properly before using opatch
Where do you find opatch logfiles?
$ORACLE_HOME/patch_storage/patchNumber/_Apply_.logWhat does the opatch logfile contain?
1. opatch logfile provides information on the steps sequence applied by opatch
2. If a patch apply fails logfiles can be used as the first point to reference to fix what went wrong
Give details on Database INTERSECT SQL usage :-
SQL INTERSECT set operator is used to fetch common values in two columns. This is the output of SQL INTERSECT
SQL> select * from t1;
SQL> select * from t2;
SQL> select name from t1 intersect select location from t2;
This is an interesting set operation in oracle database
How will you select dbid from database view in an oracle database?
DBID id the unique database identifier. It is of great significance while using RMAN (Recovery Manager) backup. We can get details on dbid , database name information from the v$database view as follows :
How will you clone a database using database control wizard in OEM?
The Enterprise Manager Clone Database Wizard steps us through the database cloning operation.
Here are the main features of the cloning feature :
1) We can clone any Oracle database that is release 8.1.7 or higher
2) The source database can be in the archivelog or the noarchivelog mode
3) We can clone a database while it is open.Database control uses RMAN internally for the cloning operation
4) Database Control will back up the datafiles and restore them in the new location. It will then recover them using archived redo logs
5) Database control will create the new instance, a password file, any necessary networking files, and the init.ora file and SPFILE
6) Database control will automatically start the new instance in the open mode
Steps For Cloning a Database Using Database Control :
1) Click the Maintenance tab on the Database Home Page of Database Control
2) Click on the clone database item in the Data Movement section(Under the move database files group)
3) In the source typege, choose the clone a running Database instance option
4) In the Source Working Directory page, enter your operating system(OS) username and password. click Next
5) In the Select Destination Page, enter the new database name and destination host name. Click Next
6) In the Destination Options Page, you can customize database file locations if you wish. Click Next
7) In the schedule page, you can choose whether you want to clone the database immediately or to schedule it for later. Click Next
8) In the review page you can review the source and clone database information
9) Click the Submit job button to start the cloning operation
What causes LRM-00116: syntax error at ‘initora_location’ and how o fix it?
While trying to startup the oracle database instance, I have been getting the error mentioned above. I was curious to know if there had been few issue related to oracle database environment variables. As all was well, I did some research on parameters in init.ora file. Upon stumbling upon a oracle metanote that said, oracle treats “” as escape character and the init.ora parameters db_file_name_convert, log_file_name_convert shouldn’t have these escape characters at the end, I looked for these two values. though I don’t have dataguard configuration in my environment, I found that these two parameters had esacpe character “” at the end. Upon removing and re-issuing startup command all went well and the issue got fixed
SQL> startup pfile=’initora_location’;
ORACLE instance started.
Total System Global Area 1249902592 bytes
Fixed Size 1292132 bytes
Variable Size 301992092 bytes
Database Buffers 939524096 bytes
Redo Buffers 7094272 bytes
SQL> select open_mode from v$database;
Give difference between PL/SQL Programs Definer Rights Vs Invoker Rights :-
Whenever a PL/SQL program which is a PL/SQL block, or subprograms like procedure is created in a particular schema it is given the rights of definer by default. This definer rights grants all the privileges of the program compiler to this program (i.e) this code will execute with the privilege of schema user who compiled the code. This is default and has a limitation on code reusability.
To address this it is possible to execute the PL/SQL code with privileges equivalent to the invoker (i.e) the code is executed with privileges of the schema user who is executing the code in present.
This offers code-reusability option. This simple feature can be implemented by addition of simple invoker rights as follows
emp(…) AUTHID CURRENT_USER AS
This clause AUTHID CURRENT_USER will allow the executable to run with invoker rights.
Give details on some Performance Views In Oracle Database and Their Uses:-
A brief listing of various dynamic performance views in Oracle database and their uses are given below:
V$ACCESS – Displays information about locks that are currently imposed on library cache objects. The locks are imposed to ensure that they are not aged out of the library cache while they are required for SQL execution.
V$ACTIVE_INSTANCES – Displays the mapping between instance names and instance numbers for all instances that have the database currently mounted.
V$ACTIVE_SERVICES – Displays information about the active services in the database.
V$ACTIVE_SESS_POOL_MTH – Displays available active session pool resource allocation methods.
V$ACTIVE_SESSION_HISTORY – Displays sampled session activity in the database. It contains snapshots of active database sessions taken once a second.
V$ADVISOR_PROGRESS – Displays information about the progress of advisor execution.
V$ALERT_TYPES – Displays information about server alert types.
V$AQ – Displays statistics for the queues in the database.
V$ARCHIVE – Displays information about redo log files in need of archiving. Each row displays information for one thread. This information is also available in V$LOG.
V$ARCHIVE_DEST – Displays, for the current instance, all of the destinations in the Data
Guard configuration, including each destination’s current value, mode, and status.
V$ARCHIVE_DEST_STATUS – Displays runtime and configuration information for the archived redo log destinations. The information in this view does not persist across an instance shutdown.
V$ARCHIVE_GAP – Displays information about archive gaps on a standby database. This view can be used to find out the current archive gap that is blocking recovery.
V$ARCHIVE_PROCESSES – Displays the state of the various ARCH processes for the instance.
V$ARCHIVED_LOG – Displays archived log information from the control file, including archive log names.
ASM(Automatic Storage Manager) Views :
V$ASM_ALIAS -Displays one row for every alias present in every disk group mounted by the Automatic Storage Management instance. In a database instance, V$ASM_ALIAS displays no rows.
V$ASM_ATTRIBUTE – Displays one row for each attribute defined. In addition to attributes specified by CREATE DISKGROUP and ALTER DISKGROUP statements, the view may show other attributes that are created automatically.
V$ASM_CLIENT – Displays one row for each open ASM disk group.
V$ASM_DISK – Displays one row for every disk discovered by the Automatic Storage Management instance, including disks which are not part of any disk group. In a database instance, V$ASM_DISK only displays rows for disks in disk groups in use by the database instance.
V$ASM_DISK_IOSTAT – Displays information about disk I/O statistics for each ASM client. If this view is queried from the database instance, only the rows for that instance are shown.
V$ASM_DISK does, but without performing discovery of new disks. This results in a less
expensive operation. However, since discovery is not performed, the output of this
view does not include any data about disks that are new to the system.
V$ASM_DISKGROUP – Displays one row for every ASM disk group discovered by the
ASM instance on the node.
V$ASM_DISKGROUP_STAT – Displays performance statistics in the same way that V$ASM_DISKGROUP does, but without performing discovery of new disk groups. This results in a less expensive operation. However, since discovery is not performed, the output of this view does not include any data about disk groups that are new to the system.
V$ASM_FILE – Displays one row for every Automatic Storage Management file in every disk group mounted by the Automatic Storage Management instance.
Give details on implicit type conversion matrix in SQL :-
Implicit type conversion matrix is the table that specifies the datatype to which we can convert a particular datatype.
It is interesting to note that type conversion of many different datatypes including char,varchar2,nchar,nvarchar2,date,datetime/interval,number,binary float,binary double,long,raw,row id,clob,blob,nclob
has been included in matrix
What is so special about long datatype?
It is to be noted that in oracle sql 11g long datatype can’t be converted directly into datetime/interval. Instead long gets converted to varchar2 and the resulting varchar2 is converted to interval
What is the implicit function that does this conversion?
What is use of I/O calibration the latest performance tuning feature in oracle database?
The I/O calibration feature of Oracle Database enables us to assess the performance of the storage subsystem, and determine whether I/O performance problems are caused by the database or the storage subsystem. Unlike other external I/O calibration tools that issue I/Os sequentially, the I/O calibration feature of Oracle Database issues I/Os randomly using Oracle datafiles to access the storage media, producing results that more closely match the actual performance of the database.
What are prerequisites for I/O Calibration ?
Before running I/O calibration, ensure that the following requirements are met:
1) The user must be granted the SYSDBA privilege
2) timed_statistics must be set to TRUE
3) Asynchronous I/O must be enabled :
When using file systems, asynchronous I/O can be enabled by setting filesystemio_options to SETALL
4) Ensure that asynchronous I/O is enabled for datafiles by running the following query:
col name format a50
select name,asynch_io from v$datafile f,v$iostat_file i where f.file#=i.file_no and filetype_name=’Data File’;
Additionally, only one calibration can be performed on a database instance at a time.
How will you run I/O Calibration ?
The I/O calibration feature of Oracle Database is accessed using the DBMS_RESOURCE_MANAGER.CALIBRATE_IO procedure. This procedure issues an I/O intensive read-only workload (made up of one megabytes of random of I/Os) to the database files to determine the maximum IOPS (I/O requests per second) and MBPS (megabytes of I/O per second) that can be sustained by the storage subsystem. Due to the overhead from running the I/O workload, I/O calibration should only be performed when the database is idle, or during off-peak hours, to minimize the impact of the I/O workload on the normal database workload. To run I/O calibration and assess the I/O capability of the storage subsystem used by Oracle Database, use the DBMS_RESOURCE_MANAGER.CALIBRATE_IO procedure:
SET SERVEROUTPUT ON
— DBMS_RESOURCE_MANAGER.CALIBRATE_IO (, , iops, mbps, lat);
DBMS_RESOURCE_MANAGER.CALIBRATE_IO (2, 10, iops, mbps, lat);
DBMS_OUTPUT.PUT_LINE (‘max_iops = ‘ iops);
DBMS_OUTPUT.PUT_LINE (‘latency = ‘ lat);
dbms_output.put_line(‘max_mbps = ‘ mbps);
What are considerations while running I/O calibration procedure?
When running the DBMS_RESOURCE_MANAGER.CALIBRATE_IO procedure, consider the following:
1) Do not run the procedure multiple times across separate databases that use the same storage subsystem
2) Quiesce the database to minimize I/O on the instance issuing the following commands:
SQL>ALTER SYSTEM QUIESCE RESTRICTED;
After the maintenance work is over unquiesce using:
SQL>ALTER SYSTEM UNQUIESCE;
3) For Oracle Real Application Clusters (RAC) configurations, ensure that all instances are opened to calibrate the storage subsystem across nodes
4) The execution time of the procedure is dependent on the number of disks in the storage subsystem and increases with the number of nodes in the database
5) In some cases, asynchronous I/O is permitted for datafiles, but the I/O subsystem for submitting asynchronous I/O may be maximized, and I/O calibration cannot continue. In such cases, refer to the port-specific documentation for information about checking the maximum limit for asynchronous I/O on the system.
From where can you query the I/O calibration process?
At any time during the I/O calibration process, you can query the calibration status in the V$IO_CALIBRATION_STATUS view. After I/O calibration is successfully completed, you can view the results in the DBA_RSRC_IO_CALIBRATE table
How to change the temporary tablespace of a database?
It is a good practice to allocate a separate temporary tablespace at the time of database creation. If not SYSTEM is taken by default. Here is the command to look for tablespace details of the database
select * from database_properties where property_name=’DEFAULT_TEMP_TABLESPACE’;
If it points to default SYSTEM, here is the command to alter the temporary tablespace of oracle database
alter database default temporary tablespace TEMP;
In above command temp is the temporary tablespace. To add a tempfile to the database issue
alter tablespace temp add tempfile ‘filepath’;
Then issue :
alter database default temporary tablespace TEMP;
Are oracle histograms really needed for performance tuning?
Oracle histogram the internal data distribution pattern collection on all the columns in a table or few columns in a table are essentially important in determining the performance of the database system. Say the data distribution doesn’t need a histogram but has the histogram collected in its column. So what happens?
There will be usage of wrong index.This will have a great hit on system performance.
What to do to disable histograms in a table column?
First enable histogram to be collected across all columns using the method_opt option of dbms_stats.gather_table_stats as follows
execute dbms_stats.gather_table_stats(owner=>’TEST’, table=>’test’,method_opt=>’for all columns auto’);
As a second step disable histogram on a particular column that doesnt need have one. Note that this needs to be disabled one by one
execute dbms_stats.gather_table_stats(owner=>’test’,table=>’test’,’for column size 1 columnnme’);
The above command disables histogram collection in columnA
Now use the for all column size repeat clause to commit the last changed behaviour to be persistent as the permanent behaviour
execute dbms_stats.gather_table_stats(owner=>’test’,table=>’test’,’for all columns size repeat’);
Give details on Histograms and it importance with Performance Tuning :-
An interesting performance tuning strategy is to make use of histograms feature provided by Oracle corporation. Many of us believe that by enabling histograms as a whole in the table by setting method_opt=FOR COLUMNS SIZE 254 (or) by disabling histograms across the table as method_opt=FOR ALL COLUMNS SIZE 1, the performance of the query can be tuned optimally. But this is not true in most of the cases.
So when do I use histograms and how do I use them?
The best starting point would be to analyse the plan of a query. This is a simple tkprof output, ADDM report etc. Upon analysing the plan look out for the indexes, tables that are being made use of. See to that full table scan, index scans happen properly. Based on this, create histograms in particular table column as method_opt=FOR COLUMN SIZE 2-254 COLUMNNAME (where 2-254 is the range of bucket values). Similarly, disable histograms at column level using method_opt=FOR COLUMN SIZE 1 COLUMNNAME
Costbased optimizer makes use of histograms to make a decision on using fulltablescan, index scan, choosing right indexes etc.
What is Oracle Streams?
Oracle streams is a technology from oracle corporation for sharing information among different databases and among different application environments
What is the memory component that supports streams activity?
Streams pool is the memory component that supports streams activity
How do you setup streams pool?
Streams is the component in SGA(System Global Area) which is the shared memory structure. It is possible to set the STREAMS_POOL_SIZE initialization parameter. It is possible to automate it using automatic shared memory management of SGA which happens by setting SGA_TARGET parameter
What happens when SGA_TARGET is set?
When using automatic shared memory management 10% of the space set aside for SGA_TARGET is allocated to streams pool
How to determine objects in oracle schema?
Whenever we log into a database we land in a virtual space called as schema. A Schema is user specific. By default all the objects created in a schema are accessible by the user. The user is popularly called as schema user.
Details on objects in a schema can be found from a set of system tables. They are called as user_ tables. Say to get to know the objects present in a schema log onto the schema and query the user_objects using select * from user_objects. We can find the number of objects using count() function against user_objects table
Are you New Oracle DBA?
Oracle has been pioneer in developing and marketing database management system(DBMS). Oracle started version 6.0 and has released its latest version 11g on July 11th, 2007. Latest version of Oracle that comes with multi-tenancy capacity is the Oracle database 12c.
Each version comes out as an enhanced version of its predecessor with enriched features than its predecessor. Lets consider 9i where features which support Internet computing has been added and later came the 10g which supports grid computing.
1) Where to get best oracle certification?
Its always a better idea to start with oracle certifications if you are an oracle niche
2) Where to take up the certification course?
There are plenty of institutes offering OCP certification training. Oracle has its University training people in various disciplines. Details can be found here:
3) What will be the salary of a DBA fresher?
It depends on the organization. There are people joining organizations as junior DBA’s, Oracle consultants,junior database consultants, system analysts, data analysts, database designers.
In case of mid-sized organizations a fresher can expect a salary around 1.5-2lac. People joining larger organizations can expect between 2-5lac.
What are Daily Routine Tasks Of An Oracle DBA?
Monitoring for problems – running monitoring scripts or use the oracle built-in tools such as Enterprise manager.Check reports generated by monitoring scripts on a daily basis – disk space problems, memory allocation, disk I/O
Monitor alert log file – fix errors
Types of alert log errors – change some parameters, add some space, perform an administrative task
Use Metalink database and open a new tar with Oracle
Upgrade to latest versions – software, software and database( applications and DBA’s configuration parameters)
Test new applications to be deployed in the system
What is Oracle Net Services?
It is a suite of applications that provide enterprise wide distributed heterogenous computing environment connectivity.
What are the uses of Oracle Net Services?
Oracle Net services ease the network configuration and management,maximize performance,improve network diagnostic capability.
What is an Oracle Net?
Oracle Net is a component of Oracle Net services that enables a session between Oracle Database server and client application,maintains connection, and acts as a medium to transfer data between server and client.
How does Oracle Net performs its task?
Oracle Net resides in server and client and performs its task.
What are the various types of connectivity?
1) Client/Server Connections
2) Web Client Application Connections /Java Client ApplicatinConnections
3) Web Client Connections without an Application Webserver
What is Client/Server application connections?
Oracle Net is a piece of software that is layered on the top of a Oracle support network protocol-rules that determine the way to tansfer data between client and server.A good example in TCP/IP protocol.
Give details on Oracle Net Compnents:-
Oracle Net is comprises of Oracle Net Foundation Layer,Oracle Protocol support.
Oracle Net Foundation Layer – Establishes and maintains connections
Oracle Protocol Support – aps the foundation layer’s technology to industry-standard protocol
Give details on Java client application connections/web client application connections:-
Java client applications access the Oracle database server through a JDBC(Java Database Connectivity) driver,a standard java interface for connecting from java to a relational database.
What are the various types of JDBC drivers from Oracle?
JDBC OCI driver,JDBC thin driver are the drivers from Oracle.
What is a JDBC OCI driver?
JDBC OCI driver is a client-side driver that can be used with an Oracle client installation.
What is a JDBC thin driver?
It is a pure client side java driver that can be used without Oracle installations. It can be used with applets.
How do the drivers establish communication?
These drivers use Oracle Net to enable communication between oracle client and database server.
Name the four naming methods and their description:-
1)Local naming – Uses tnsnames.ora file stored on each client to connect to the database server
2) Easy connect naming – enables connections without any service name configuration
3) External naming – uses a third-party naming service to resolve service names
4) Directory naming – uses a centralized LDAP-compliant directory server to resolve service names
What is a Oracle listener?
Oracle listener is a service that runs only on the server and listens for incoming connection requests.
How do we manage listener?
Oracle provided utility lsnrctl can be used to manage listener process.
How does listener fit into Oracle networking?
1) The database registers information about the services,instances and service handlers with the listener.
2) The client makes the initial connection with the listener
3) The listener receives and verifies the client connection request and forwards it to the service handler for the database service.
4) Once the listener hands off the client request,the listener is out of the picture for that connection.
Where does the listener.ora file reside?
The default location of listener.ora file is $ORACLE_HOME/network/admin on UNIX and $ORACLE_HOMEnetworkadmin directory on windows.
Do we need to configure listener.ora file?
In recent versions of oracle database(from 9i),configuration parameters in listener.ora have default values,and we don’t have to configure listener service manually.
When is the listener service started?
After the first database on the server is created,the listener service automatically startsanf the listener configuration file listener.ora is placed in the default directory $ORACLE_HOME/network/admin.Upon creation of a new database,the database’s network and service information are automatically added to the listener’s configuration file.
What happens to the listener when the instance starts up?
Upon instance startup,the database registers itself automatically with the listener,and the listener starts listening for connection requests to this database.