Database Interview Questions Part 2
1.Which Table spaces are created automatically when you create a database?
– SYSTEM tablespace is created automatically during database creation.
– It will be always online when the database is open.
Other Tablespaces include:
– SYSAUX tablespace
– UNDO tablespace
– TEMP tablespace
– UNDO & TEMP tablespace are optional when you create a database.
2.Which file is accessed first when Oracle database is started and What is the difference between SPFILE and PFILE?
– Init<SID>.ora parameter file or SPFILE is accessed first .( SID is instance name)
– Settings required for starting a database are stored as parameters in this file.
– SPFILE is by default created during database creation whereas PFILE should be created from SPFILE.
– PFILE is client side text file whereas SPFILE is server side binary file.
– SPFILE is a binary file (it can’t be opened) whereas PFILE is a text file we can edit it and set parameter values.
– Changes made in SPFILE are dynamically effected with running database whereas PFILE changes are effected after bouncing the database.
– We can backup SPFILE using RMAN.
3.What are advantages of using SPFILE over PFILE?
– SPFILE is available from Oracle 9i and above.
– Parameters in SPFILE are changed dynamically.
– You can’t make any changes to PFILE when the database is up.
– RMAN cant backup PFILE, It can backup SPFILE.
– SPFILE parameters changes are checked before they are accepted as it is maintained by Oracle server thereby reducing the human typo errors.
4.How can you find out if the database is using PFILE or SPFILE?
– You can query Dynamic performance view (v$parameter) to know your database is using PFILE or SPFILE.
– SQL> select value from V$parameter where name= ‘SPFILE’;
– A non-null value indicates the database is using SPFILE.
– Null value indicates database is using PFILE.
– You can force a database to use a PFILE by issuing a startup command as
– SQL> startup PFILE = ‘full path of Pfile location’;
5.Where are parameter files stored and how can you start a database using a specific parameter file?
– In UNIX they are stored in the location $ORACLE_HOME/dbs and ORACLE_HOME/database for Windows directory.
– Oracle by default starts with SPFILE located in $ORACLE_HOME/dbs.
– You can create PFILE from SPFILE as create PFILE from SPFILE;
– All the parameter values are now updated with SPFILE.
– Similarly, create SPFILE from PFILE; command creates SPFILE from PFILE.
6.What is PGA_AGGREGATE_TARGET parameter?
– PGA_AGGREGATE_TARGET parameter specifies target aggregate PGA memory available to all server process attached to an instance.
– Oracle sets its value to 20% of SGA.
– It is used to set overall size of work-area required by various components.
– Its value can be known by querying v$pgastat dynamic performance view.
– From sqlplus it can be known by using
SQL> show parameter pga.
7.What is the purpose of configuring more than one Database Writer Processes? How many should be used? (On UNIX)
– DBWn process writes modified buffers in Database Buffer Cache to data files, so that user process can always find free buffers.
– To efficiently free the buffer cache to make it available to user processes, you can use multiple DBWn processes.
– We can configure additional processes (DBW1 through DBW9 and DBWa through DBWj) to improve write performance if our system modifies data heavily.
– The initialization parameter DB_WRITER_PROCESSES specifies the number of DBWn processes upto a maximum number of 20.
– If the Unix system being used is capable of asynchronous input/output processing then only one DBWn process is enough, if not the case the total DBWn processes required will be twice the number of disks used by oracle, and this can be set with DB_WRITER_PROCESSES initialization parameter.
8.List out the major installation steps of oracle software on UNIX in brief?
– Set up disk and make sure you have Installation file (run Installer) in your dump.
– Check the swap and TEMP space .
– Export the following environment variables
1.ORACLE_BASE
2.ORACLE_HOME
3.PATH
4.LD_LIBRARY_PATH
5.TNS_ADMIN
– Set up the kernel parameters and file maximum descriptors.
– Source the Environment file to the respective bash profile and now run Oracle Universal Installer.
9.Can we check number of instances running on Oracle server and how to set kernel parameters in Linux?
– Editing the /etc/oratab file on a server gives the list of oracle instances running on your server.
– Editing /etc/sysctl.conf file with vi editor will open a text file listing out kernel level parameters.
– We can make changes to kernel parameters as required for our environment only as a root user.
– To make the changes affected permanently to kernel run the command /sbin/sysctl –p.
– We must also set file maximum descriptors during oracle installation which can be done by editing /etc/security/limits.conf as a root user.
10.What is System Activity Reporter (SAR) and SHMMAX?
– SAR is a utility to display resource usage on the UNIX system.
– SAR –u shows CPU activity.
– SAR –w shows swapping activity
– SAR –b shows buffer activity
– SHMMAX is the maximum size of a shared memory segment on a Linux system.
11.List out some major environment variable used in installation?
– ORACLE_BASE=/u01/app/<installation-directory>
– ORACLE_HOME=$ORACLE_BASE/product/11.2.0(for 11g)/dbhome_1
– ORACLE_SID=<instance-name>
– PATH=$ORACLE_HOME/bin:$PATH
– LD_LIBRARY_PATH=$ORACLE_HOME/lib:$LD_LIBRARY_PATH
– TNS_ADMIN=$ORACLE_HOME/network/admin
These are absolutely critical environment variables in running OUI.
12.What is a control file?
- Control file is a binary file which records the physical structure of a database.
- It includes number of log files and their respective location, Database name and timestamp when database is created, checkpoint information.
- We find CONTROL_FILE parameter in initialization parameter file which stores information about control file location.
- We can multiplex control files, store in different locations to make control files available even if one is corrupted.
- We can also avoid the risk of single point of failure.
13.At what stage of instance, control file information is read and can we recover control file and how to know information in a control file?
During database mounting, control file information is read.
We can’t recover or restore lost control file, but we can still startup the database using control files created using multiplexing in different locations.
We can query the following command
SQL> alter database backup controlfile to trace;
– We find a trace file(.trc) in udump location,we can edit it and find the complete database structure.
– Multiplexing can also be done using Following command
SQL> alter database backup controlfile to <Different location/path>.
14.How can you obtain Information about control file?
Control file information can be shown in initialization parameter file.
We can query v$controlfile to display the names of control files
From sql we can execute
SQL> show parameter control_files;
The above query gives the name, Location of control files in our physical disk.
We can edit PFILE using a vi editor and control_files parameter gives us information about number of and location of control files.
15.How do you resize a data file and tablespace?
- Prior to Oracle 7.2 you can’t resize a datafile.
- The solution was to delete the tablespace, recreating it with different sized datafiles.
- After 7.2 you can resize a datafile by using ALTER DATABASE DATAFILE <file_name> RESIZE;
- Resizing Table space includes creation of new data file or resizing existing data file.
- ALTER TABLESPACE <tablespacename> ADD DATAFILE ‘<datafile name> ‘ size M; creates a new datafile.
16.Name the views used to look at the size of a datafile, controlfiles, block size, determine free space in a tablespace ?
– DBA_DATA_FILES or v$datafile view can be used to look at the size of a datafile .
– DBA_FREE_SPACE is used to determine free space in a tablespace.
– V$contolfile used to look at the size of a control file which includes maxlogfiles, maxlogmembers, maxinstances.
– Select * from v$controlfile gives the size of a controlfile.
– From sqlplus, query show parameter block_size to get size of db_block_size.
17.What is archive log file?
In archive log mode, the database will makes archive of all redo log files that are filled, called as archived redo logs or archive log files.
By default your database runs in NO ARCHIVE LOG mode, so we can’t perform online backup’s (HOT backup).
You must shut down database to perform clean backup (COLD backup) and recovery can be done to the previous backup state.
Archive log files are stored in a default location called FRA (Flash Recovery Area).
We can also define our own backup location by setting log_archive_dest parameter.
18.Assume you work in an xyz company as senior DBA and on your absence your back up DBA has corrupted all the control files while working with the ALTER DATABASE BACKUP CONTROLFILE command. What do you do?
– As long as all data files are safe and on a successful completion of BACKUP control file command by your Back up DBA you are in safe zone.
– We can restore the control file by performing following commands
- CONNECT INTERNAL STARTUP MOUNT
- TAKE ANY OFFLINE TABLESPACE (Read-only)
- ALTER DATABASE DATAFILE (OFFLINE)
- RECOVER DATABASE USING BACKUP CONTROL FILE
- ALTER DATABASE OPEN RESETLOGS
- BRING READ ONLY TABLE SPACE BACK ONLINE
- Shutdown and back up the system. Then restart.
- Then give the command ALTER DATABSE BACKUP CONTROL FILE TO TRACE
- This output can be used for control file recovery as well.
If control file backup is not available, then the following will be required
- CONNECT INTERNAL STARTUP NOMOUNT
- CREATE CONTROL FILE …..;
– But we need to know all of the datafiles, logfiles, and settings of MAXLOGFILES, MAXLOGMEMBERS, MAXLOGHISTORY, MAXDATAFILES for the database to use the command.
19.Can we reduce the space of TEMP datafile? How?
- Yes, we can reduce the space of the TEMP datafile.
- Prior to oracle 11g,,you had to recreate the datafile.
- In oracle 11g you reduce space of TEMP datafile by shrinking the TEMP tablespace.It is a new feature to 11g.
- The dynamic performance view can be very useful in determining which table space to shrink.
20.What do you mean by database backup and which files must be backed up?
Database stores most crucial data of business ,so it’s important to keep the data safe and this can be achieved by backup.
The following files must be backed up
- Database files (Header of datafiles is freezed during backup)
- Control files
- Archived log files
- Parameter files (spfile and pfile)
- Password file