Questions
http://www.oracle-base.com/articles/10g/Auditing_10gR2.php
What are the things you check before you install an oracle database on unix or linux or windows platforms ?
.. memory and desk space, swap space, OS patches, os packages, os group name/ username, os directories, os level kernel parameter, environment variable,
How do you identify the pre-req os patches required for an oracle installation on HP or solaris or Linux platforms?
..refer read me provided / installation doc specific to OS type and version...
How do you identify if the oracle software version you are installing is certified on the platform you are installing ?
.. Metalink
What is emulator on unix and why do you need it? What is xterm ?
..X means the X Window System, which is the basis of Linux graphical user interface. Xterm is a terminal emulator program that uses X.
What is XAUTHORITY env settings ?
.. DISPLAY tells a program which "display" to run on (Linux typically has multiple virtual consoles). :0 is the default one; you can configure more. XAUTHORITY is related to the xauth system. The xauth system manages privileges for connecting to the X server, which runs as root. Because the program cannot find the authority file that it thinks you're specifying in XAUTHORITY, it can't get permission to connect.
What is root.sh ?
.. root.sh, which is a shell script, updates/creates an oratab file and environment variable like – ORACLE_HOME, OWNER, LD_LIBRARY, etc. The oratab file is the file where the user will place references to all databases to be discovered by the agent and controlled by the Oracle Enterprise Manager. For each database created, the entry is of the form: :<$ORACLE_HOME>:[Y/N]
What is the importance of /etc/oraInst.loc or /var/opt/oracle/oraInst.loc ?
.. Oracle keeps its inventory of installed products in a directory
Why do you need environment file ? Does this get created automatically ? What the environment settings which are needed in environment file ?
What is oraenv,dbshut,dbstart,dbhome files ? Where can you find these ? Explain me about these files ?
What is the importance of /etc/oratab or /var/opt/oracle/oratab ?
--Is used to locate databases running and their respective ORACLE_HOME and used to start dbs during OS boot
What are the various kernel settings which you setup in oracle install on linux/unix ?
--shmmax,shmmin, semmls, semmns, semopm, shmall, file-max, rmem-max, wmem-default
SEMMNI - Defines the maximum number of semaphore sets in the entire system.
SEMMNS - Defines the maximum semaphores on the system. This setting is a minimum recommended value, for initial installation only.
The SEMMNS parameter should be set to the sum of the PROCESSES parameter for each Oracle database, adding the largest one twice, and then adding an additional 10 for each database.
SEMMSL - Defines the minimum recommended value, for initial installation only.
SHMMAX - Defines the maximum allowable size of one shared memory segment.4 GB = 4294967295
SHMMIN - Defines the minimum allowable size of a single shared memory segment.
SHMMNI - Defines the maximum number of shared memory segments in the entire system.
SHMSEG - Defines the maximum number of shared memory segments one process can attach.
Why environment file is not needed in windows?
What is oradim ? Why do you need this tool in windows ?
..The dbstart and dbstop shell scripts do not exist on Windows latforms. Consequently Oracle database startup and shutdown is implemented completely differently. The oradim utility is used on the Windows platform to perform these tasks.
When do you need tnsnames.ora listener.ora and sqlnet.ora ? How do you configure these files ?
..The Transparent Network Substrate (TNS), provided by Oracle, allows distributed communications between databases.
The TNS, which is a distributed protocol, allows for transparent database communications between remote systems. The TNS serves as an insulator between Oracle's logical data request and the physical communications between the remote servers. As such, the network administrator is able to control much of the network performance tuning. The Oracle administrator, then, has little control over the network settings that can affect overall database performance:
What is Net8 or SQL*Net means ? How do you get this software ?
-- Net8 (called SQL*Net prior to Oracle8) is Oracle's client/server middleware product that offers transparent connection from client tools to the database, or from one database to another.
Net8 enables services and their applications to reside on different computers and communicate as peer applications. The main function of Net8 is to establish network sessions and transfer data between a client machine and a server or between two servers. Net8 is located on each machine in the network. Once a network session is established, Net8 acts as a data courier for the client and the server.
Essentially, SQL*Net provides the software layer between Oracle and the networking software, providing seamless communication between an Oracle client machine (running, for example, SQL*Plus) and the database server or from one database server to another. SQL*Net/ Net8 works across multiple network protocols and operating systems.
TNS or Transparent Network Substrate is Oracle's networking architecture. TNS provides a uniform application interface to enable network applications to access the underlying network protocols transparently.
The TNS architecture consists of three software components:
What is a bequeath session?
A bequeath session is a session for which the listener spawned a Dedicated Server Process and then passed (bequeaths) the connection to that server process.
What are the different sql net naming methods ?
-- TNSNAMES, LDAP, ONAMES, HOSTNAME,EZCONNECT.
What is onames or OID ?
- ONAMES was old version and OID have replaced by OID -Oracle internet directory
- TNSNames is a local naming method were connection information is stored locally on each client.
ONAMES (desupported now) is a centralized naming method where connection information is stored in a centralized directory server.
Local naming is suitable when you have small amount of clients as adding/changing database connection information will result in changing the tnsnames.ora file on each client. If you have large amount of clients you might want to look at centralized naming.
In centralized naming, changes are made only in the directory server and clients see the new information when making a connection.
-
What is semaphore ? How do you handle the instance crash ?
Shared memory and semaphores are amongst the most important resources for an Oracle instance. In normal situations the semaphore and shared memory segments are released when oracle is shutdown. However being database administrator we mostly encounter the scenario where Oracle instance crashes abruptly and its shared memory and semaphore are not released. The situation becomes critical if you have many multiple instances running on the same machine as it is unclear which shared memory and semaphore sets to kill. If you are able to identify which shared memory and semaphore segments are owned by a particular instance then it can help you in recovery situations where the database instance has not released its shared memory and semaphores on database shutdown.
Identifying semaphores and shared memory segments:
The ipcs command can help you identify the semaphores and shared memory segments used by Oracle. You can run following command from $ prompt on the UNIX box. You will see some entries if your instance is up and running or the instance has gone but memory and semaphores are not released.
$ipcs | grep oracle
You will see two segments. The segment that represents shared memory is denoted by m whereas the segment that represents semaphores is denoted by s.
Releasing semaphores and shared memory segments for single instance:
If you are running single instance, it is very easy to release the semaphore and shard memory. You can run below commands for it.
$ipcrm -m id
In the above command id will be the id displayed for memory in ipcs under memory section.
$ipcrm -s id
How do you run the o/s commands from oracle?
What is mutating trigger ?
--The Oracle mutating trigger error occurs when a trigger references the table that owns the trigger, resulting in the "ORA-04091: table name is mutating, trigger/function may not see it." message.
Can we use commit inside a trigger ?
--NO
How to enable the auditing in oracle ?
DBA_AUDIT_TRAIL - Standard auditing only (from AUD$).
DBA_FGA_AUDIT_TRAIL - Fine-grained auditing only (from FGA_LOG$).
DBA_COMMON_AUDIT_TRAIL - Both standard and fine-grained auditing.
How do you send an email from oracle db ?
Steps to enable Mailing from Database
1. sqlplus ‘/ as sysdba’
2. @$ORACLE_HOME/rdbms/admin/utlmail.sql
3. @$ORACLE_HOME/rdbms/admin/prvtmail.plb
4. Set smtp_server information in init.ora or spfile.ora
alter system set smtp_out_server = ‘SMTP_SERVER_IP_ADDRESS:SMTP_PORT’ scope=both;
25 = Default SMTP Port
If instance had been started with spfile
eg: alter system set smtp_out_server = ’172.25.90.165:25' scope=both;
If instance had been started with pfile
alter system set smtp_out_server = ’172.25.90.165:25';
Also make below entry in your initSID.ora
smtp_out_server = ’172.25.90.165:25'
Thats It, your database is configured to send emails ….
How to send an email
1. sqlplus ‘/ as sysdba’
2. exec utl_mail.send((sender => ‘oraclepitstop@ab.com’, recipients => ‘oraclepitstop@ab.com’, subject => ‘Testing UTL_MAIL Option’, message => ‘blah blah blah’);
3. Check the inbox of the email id, to verify the email receipt.
To enable other DB users to use this functionality, grant execute permission on UTL_MAIL package.
eg: grant execute on utl_mail to apps;
How can you schedule the monitoring from the database ?
The Automatic Database Diagnostic Monitor (ADDM) analyzes data in the Automatic Workload Repository (AWR) to identify potential performance bottlenecks. For each of the identified issues it locates the root cause and provides recommendations for correcting the problem. An ADDM analysis task is performed and its findings and recommendations stored in the database every time an AWR snapshot is taken provided theSTATISTICS_LEVEL parameter is set to TYPICAL or ALL. The ADDM analysis includes the following.
Give me the detail steps for creating the database manually ?
Give me the detail steps for creating the database using DBCA ?
Set oracle sid, home invoke dbca and
How to identify the version of the rdbms ?
Select * from v$version;
How to identify the database components and their statuses ?
SELECT * FROM PRODUCT_COMPONENT_VERSION;
DBA_REGISTRY displays information about the components loaded into the database.
How to identify if I am using 64 bit oracle or 32 bit oracle ?
File oracle
How do you approach a new database implementation for a dw or oltp project ? Tell me about key things which you take into consideration ?
Tell me about various RAID levels ?
Various RAID levels exist these are 0, 1, 2, 3, 4, 5, 6, 7, 10, & 0+1. The levels of RAID protection varies with the RAID level selected RAID levels 0 & 1 are not technically RAID as they have no redundancy in the event of drive failure. The most common RAID levels are shown below. RAID levels 2, 4, 6, 7 & 0+1 are a combination of the other RAID levels shown.
Explain me about oracle memory structures and background processes ?
What is oracle instance ? What is Oracle SGA ? What are the various components exist in Oracle SGA ?
What is PGA ? Tell about the responsibilities of background processes such as pmon,smon,arc,rec,lck,qmn,aq,lgwr,dbwr ?
Which process retrieves the data from disk into memory ?
Server process
What is advanced queuing ? How do you enable this ?
Tell me about checkpoint process in oracle and SCN numbers in oracle.
System change number(SCN) is represented with SCN_WRAP and SCN_BASE. Whenever SCN_BASE reaches 4294967290 (2 power 32), SCN_WRAP goes up by one and SCN_BASE will be reset to 0. This way you can have a maximum SCN at 1.8E+19.
SCN = (SCN_WRAP * 4294967290) + SCN_BASE
Checkpoint number is the SCN number at which all the dirty buffers are written to the disk, there can be a checkpoint at object/tablespace/datafile/database level.
Checkpoint number is never updated for the datafiles of readonly tablespaces.
What is the difference between dedicated and shared servers processes ?
Dedicated server connection:
A dedicated process has one to one relationship with user process. The user connected with dedicated server connection has the dedicated resource that is whether or not the user is doing work the connection remains allocated to that user. Thus if your user is not working and if there is less network resource then this user is blocking another user to connect or to execute the user request by making this user works queued. This connection is preferable when you have lot of resource or to some administrative user like "SYS" , "SYS" for administrative works and for batch jobs,
Shared server connection:
Shared server architecture consists of Listener Process, Dispatcher Process, Request Queue, Shared server process and Response Queue. Network Listener process listens the user process request. If user process request requires a dedicated server process, listener process starts a dedicated server process. If the request can be assigned to a shared server process, then the request is forwarded to dispatcher process. Shared server configuration requires at least on dispatcher process. Dispatcher process places the request on request queue. Request Queue is created in SGA and shared by all dispatcher processes. On of free Shared server process picks up the request from request queue. After processing the request, shared server process places the result in response queue. Each dispatcher process has its own response queue in SGA. Dispatcher knows about the user process which placed the request, so the response is returned back to user process.
When there is shared server the user connection is shared, that is user is connecting by dispatchers and when a user is idle his resource can be used by other users, thus lessen the load on system, this is more likely in a environment where user is mostly idle, like in a order entry system.
In what kind of configuration, you see oracle dispatcher process ?
http://docs.oracle.com/cd/B10500_01/server.920/a96524/c09procs.htm - read from this
Explain me about the instance recovery processes such as rollforward and rollback ?
Explain me about library cache and data dictionary cache?
Tell me the difference between startup restrict and startup?
startup restrict - When the database is started in restricted session mode, only users(such as those with roleDBA) with the system privilege RESTRICTED SESSION can connect to the database. Use the restricted mode when performing maintenance or when you want certain users to be able to log in.
Startup – is regular. All user can get into the database.
Explain me about the various stages of oracle startup process?
-nomount, mount, open
Explain me about the oracle instance recovery ?
If the Oracle instance fails any information in the SGA that is not been written back to disk is lost. For eg, the failure of the OS causes an instance failure. After the loss of the instance, the BG Process SMON automatically performs instance recovery when the database is reopened.
It will go through the following process.
1) ROLL FORWARD Phase:
Rolling forward to recover data that has not been recorded in the data files but that has been recorded in the online redo log. This data has not been written to disk because of the loss of the SGA during instance failure. During this process, SMON reads the redo log files and applies the changes recorded in the redo log to the data blocks. Because we have all committed and uncommitted transactions in the redo log files, this process recovers the transactions.
2) OPEN the DATABASE:
Opens the database for user access so that users can log on to the database and do their operations and key thing here is data that is not locked by unrecovered transactions is available immediately.
3) ROLLBACK Phase:
as name itself clearly depicts, rollback the uncommitted transactions.
Explain me about the difference between shutdown transactional and shutdown normal ?
What are the various shutdown options which can be used and explain me about those ?
Shutdown, shutdown transactional, shutdown immediate; shutdown abort.
What are the various startup options which can be used and explain me about those ?
How do you identify the oracle alert log file ? controlfile ? trace file ? tns file ? listener file ?
How do you identify the software location(oracle_home) for a running database ?
Echo $ORACLE_HOME
List at least 25 famous unix commands which are needed for daily day-to-day dba work?
Ps –ef, ps—eaf, diff, dos2unix, df –k, du –k, ftp, sftp, scp, ssh, ipcs, mkdir, cd, pwd, mv, cp –pr, vmstat, iostat, netstat,
How do you drop user tablespace and users ?
Drop user username cascade;
Explain me the database views which you look at to identify the physical structures of the database ?
V$datafile, v$tempfile; v$controlfile, v$log;
Explain me the database views which you look at to identify the logical structures of the database ?
V$tablespace,
How do you calculate/identify the total database current size?
Select sum(bytes) from dba_segments;
How do you calculate/identify the table current size?
Dba_segments
How do you calculate/identify the tablespace current size ?
Select tablespace_name, sum(blocks) Totalblocks, sum(bytes/(1024*1024)) TotalMB From dba_segments Group By tablespace_name
How do you calculate/identify the freespace in the database?
Note the in-line view wheredba_data_files is joined into dba_free_space:
SELECT
a.tablespace_name,
a.file_name,
a.bytes allocated_bytes,
b.free_bytes
FROM
dba_data_files a,
(SELECT file_id, SUM(bytes) free_bytes
FROM dba_free_space b GROUP BY file_id) b
WHERE
a.file_id=b.file_id
ORDER BY
a.tablespace_name;
How do you move the table from one tablespace to another tablespace?
-Alter table ... Move
How do you resize the logfiles of the database ?
-Switch logfile and execute - ALTER SYSTEM CHECKPOINT GLOBAL;
, add new redo files of appropriate size and the drop the old ones..
How do you add the log groups to the database ?
-Alter database add logfile group 1;
How do you drop the redog log groups ?
ALTER DATABASE DROP LOGFILE GROUP 1;
How do you rename/move the redo log members from one disk to another disk ?
Above
How do you identify the user default tablespace?
-In 10g - SELECT PROPERTY_VALUE FROM DATABASE_PROPERTIES WHERE PROPERTY_NAME = ‘DEFAULT_PERMANENT_TABLESPACE’;
ALTER DATABASE DEFAULT TABLESPACE abc;
Can also check in dba_users table;
How do you change the user default tablespace?
-Alter user ...
How do you specify the tablespace name when you create the table ? What is the syntax ?
-Create table .. tablespece ...
How do you add the datafiles to the existing tablespace ?
-Alter tablespace add datafile ..
Give me the syntax for creating the dictionary managed and locally managed tablespaces ?
.. extent management local;
Give me the syntax for resizing the datafile ?
..Alter database datafile ..resize;
How do you rename the database tablespaces ?
..ALTER TABLESPACE users RENAME TO usersts;
How do you rename the database datafiles ?
..take tablesapace offline and rename data files..
ALTER TABLESPACE users OFFLINE NORMAL; and ALTER TABLESPACE users RENAME DATAFILE '/u02/oracle/rbdb1/user1.dbf', '/u02/oracle/rbdb1/user2.dbf' TO '/u02/oracle/rbdb1/users01.dbf', '/u02/oracle/rbdb1/users02.dbf';
How do you relocate the datafiles for system tablespace ?
This procedure used to move the system tablespace datafiles as we can not off line the system tablespace like any other.This procedure will work for all tablespaces.
Note: The database will not be availabel when you perform this operation & before attempting this procedure make a full cold backup of the database.
1.Shut down the database with immediate or normal option only.
2.copy the datafiles of system tablespace using operating system command to new location porvided that you know the datafiles full path for the system tablespace.Check for both file sizes after copy.
3.Mount the database using command startup mount.
4.Rename the datafile to new file locations.
5.Now open the database.
How do you make the tablespace offline. What is the advantage. ?
..alter tablespace..offline
How do you make the tablespace readonly. What is the advantage. ?
ALTER TABLESPACE flights READ ONLY;.. on one can change data from this tablespace
What is difference between readonly/offline ?
Making a tablespace read-only prevents write operations on the datafiles in the tablespace.Bringing an online tablespace offline, makes the schema objects within the tablespace unavailable to the database users
How do you identify the datafile associated with a table? How do you know how big the table size is ?
Dba_extents has file_id and query with segment_name=’tablename’
How do you add,rename the temp datafiles ?
.. Rename – shutdown, startup mount, alter dataset rename.., alter database open,
Add temp datafile –
How do you identify the archive logs location ?
Archive log list / archive dest in init,ora / V$ARCHIVE_DEST;
How do you change the archive log location?
.. archive log list; alter system archive log start to ‘newloc’; archive log list;
And alter system set log_archive_dest='newloc’ scope=spfile..
Check .. show parameter log_archive_dest
How do you change the archive log format ?
..ALTER SYSTEM SET log_archive_format='t_%s_%r.dbf' SCOPE=spfile;
How do you enable the archive log, disable the archive log? Explain in detail about database instance?
..shutdown, startup mount, alter database archivelog, alter database open, ..prior it set init.ora
How do you identify if the database is running in archive log mode or no-archive log mode?
..Archive log list
What is export import ?
Export / import is a logical export / import.
How do you change the db char set?
..shutdown, take backup, startup, do .csscan and convert using csalter(csalter changes only metadata
How do we change db block size?
..The database block size is fixed during database creation time and cannot be changed without database re-creation. db_block_size in the PFILE is used to determine the block size for a database being created. (Check o/s documentation to determine valid settings for db_block_size.)
Give me the syntax to export the entire database?
Exdp full=y
Give me the syntax to import the entire database. ?
Impdp full=y
Give me the syntax to export one schema in the database?
Give me the syntax to export one table in the database?
Exp tables=’’
What is data pump?
.. new version starting from 10g. Needs directory parameter set and permission on it to use for pump
Tell me about transportable tablespaces?
..A transportable tablespace allows you to quickly move a subset of an Oracle database from one Oracle database to another. However, in the previous release of Oracle server, you can only move a tablespace across Oracle databases within the same platform.
Oracle 10g is going one step further by allowing you to move tablespace across different platforms.
Tell me about your experience with automation of startup shutdown in unix and windows?
..Put entry in rc*.d to call $ORACLE_HOME/bin/dbstart /$ORACLE_HOME/bin/dbstut..
Did you ever heard of rc.d ? Tell me about what do you know about this ?
..The actual scripts that control services are in /etc/rc.d. These scripts are automatically run at boot time, but they can be called manually if necessary. The following example shows how to start the SSH daemon that we enabled in the previous section:
#/etc/rc.d/sshd start
Starting sshd.
Later, if you wish to stop the SSH daemon, run the following command:
#/etc/rc.d/sshd stop
Stopping sshd.
Waiting for PIDS: 123.
Tell me about your experience with using data pump ?
Tell me about flash back recovery feature of 10g ?
..Flashback query allows a user to view the data quickly and easily the way it was at a particular time in the past, even when it is modified and committed, be it a single row or the whole table.
Tell me about your experience with DBMS_STATS?
.. used to collecr statistics
How do you collect the dictionary stats in 9i and 10g ?
.. dbms_stats.delete_schema_stats('SYS'); dbms_stats.gather_schema_stats('SYS');
How do you speed up the export/import process of entire database? What approach/settings u use ?
.. big buffer size, direct=y, RECORDLENGTH to high value,
.. datapump - user parallel, set analyze no, set index =n, create themafterwork
What is external tables ? Give me the steps to set this up ?
External Tables let you query data in a flat file as though the file were an Oracle table. In 9i, only read operations were permitted; in 10g, you can also write out data to an external table, although you can't write to an existing table.
create table xtern_empl_rpt) organization external (default directory xtern_data_dir.. access parameters ( records delimited by newline fields terminated by ',' ) location ('employee_report.csv')
Give me the steps to recover a drop table in 10g ?
What is table delete vs table truncate in 10g ?
Delete is dml..rollback is possible..
Delete Command features and advantages:
Truncate Command features and advantages
How do you rewind the database in 10g ? Give me the steps ?
..flashback database to scn 3369533;
Give me the steps to recover the deleted rows using flashback query in 10g ?
What is SAR?
..Displays the activity for the CPU.
What is DISM?
..DISM = Dynamic Intimate Shared memory, which is used to support oracle in Solaris Envirnoment, DISM is only supported from Solaris 9 and above version
RAC Interview Questions -
Oracle RAC(Real Application Cluster) DBA Interview Questions
Oracle RAC(Real Application Cluster) DBA Interview Questions
What is RAC?
RAC stands for Real Application cluster. It is a clustering solution from Oracle Corporation that ensures high availability of databases by providing instance failover, media failover features.
Mention the Oracle RAC software components:-
Oracle RAC is composed of two or more database instances. They are composed of Memory structures and background processes same as the single instance database. Oracle RAC instances use two processes GES(Global Enqueue Service), GCS(Global Cache Service) that enable cache fusion. Oracle RAC instances are composed of following background processes:
ACMS—Atomic Controlfile to Memory Service (ACMS)
GTX0-j—Global Transaction Process
LMON—Global Enqueue Service Monitor
LMD—Global Enqueue Service Daemon
LMS—Global Cache Service Process
LCK0—Instance Enqueue Process
RMSn—Oracle RAC Management Processes (RMSn)
RSMN—Remote Slave Monitor
What is GRD?
GRD stands for Global Resource Directory. The GES and GCS maintains records of the statuses of each datafile and each cahed block using global resource directory.This process is referred to as cache fusion and helps in data integrity.
Give Details on Cache Fusion:-
Oracle RAC is composed of two or more instances. When a block of data is read from datafile by an instance within the cluster and another instance is in need of the same block, it is easy to get the block image from the instance which has the block in its SGA rather than reading from the disk. To enable inter instance communication Oracle RAC makes use of interconnects. The Global Enqueue Service (GES) monitors and Instance enqueue process manages the cahce fusion.
Give Details on ACMS:-
ACMS stands for Atomic Controlfile Memory Service.In an Oracle RAC environment ACMS is an agent that ensures a distributed SGA memory update(ie)SGA updates are globally committed on success or globally aborted in event of a failure.
Give details on GTX0-j :-
The process provides transparent support for XA global transactions in a RAC environment.The database autotunes the number of these processes based on the workload of XA global transactions.
Give details on LMON:-
This process monitors global enques and resources across the cluster and performs global enqueue recovery operations.This is called as Global Enqueue Service Monitor.
Give details on LMD:-
This process is called as global enqueue service daemon. This process manages incoming remote resource requests within each instance.
Give details on LMS:-
This process is called as Global Cache service process. This process maintains statuses of datafiles and each cahed block by recording information in a Global Resource Dectory(GRD).This process also controls the flow of messages to remote instances and manages global data block access and transmits block images between the buffer caches of different instances.This processing is a part of cache fusion feature.
Give details on LCK0:-
This process is called as Instance enqueue process.This process manages non-cache fusion resource requests such as libry and row cache requests.
Give details on RMSn:-
This process is called as Oracle RAC management process.These pocesses perform managability tasks for Oracle RAC.Tasks include creation of resources related Oracle RAC when new instances are added to the cluster.
Give details on RSMN:-
This process is called as Remote Slave Monitor.This process manages background slave process creation andd communication on remote instances. This is a background slave process.This process performs tasks on behalf of a co-ordinating process running in another instance.
What components in RAC must reside in shared storage?
All datafiles, controlfiles, SPFIles, redo log files must reside on cluster-aware shred storage.
What is the significance of using cluster-aware shared storage in an Oracle RAC environment?
All instances of an Oracle RAC can access all the datafiles,control files, SPFILE's, redolog files when these files are hosted out of cluster-aware shared storage which are group of shared disks.
Give few examples for solutions that support cluster storage:-
ASM(automatic storage management),raw disk devices,network file system(NFS), OCFS2 and OCFS(Oracle Cluster Fie systems).
What is an interconnect network?
An interconnect network is a private network that connects all of the servers in a cluster. The interconnect network uses a switch/multiple switches that only the nodes in the cluster can access.
How can we configure the cluster interconnect?
Configure User Datagram Protocol(UDP) on Gigabit ethernet for cluster interconnect.On unia and linux systems we use UDP and RDS(Reliable data socket) protocols to be used by Oracle Clusterware.Windows clusters use the TCP protocol.
Can we use crossover cables with Oracle Clusterware interconnects?
No, crossover cables are not supported with Oracle Clusterware intercnects.
What is the use of cluster interconnect?
Cluster interconnect is used by the Cache fusion for inter instance communication.
How do users connect to database in an Oracle RAC environment?
Users can access a RAC database using a client/server configuration or through one or more middle tiers ,with or without connection pooling.Users can use oracle services feature to connect to database.
What is the use of a service in Oracle RAC environemnt?
Applications should use the services feature to connect to the Oracle database.Services enable us to define rules and characteristics to control how users and applications connect to database instances.
What are the characteriscs controlled by Oracle services feature?
The charateristics include a unique name, workload balancing and failover options,and high availability characteristics.
Which enable the load balancing of applications in RAC?
Oracle Net Services enable the load balancing of application connections across all of the instances in an Oracle RAC database.
What is a virtual IP address or VIP?
A virtl IP address or VIP is an alternate IP address that the client connectins use instead of the standard public IP address. To configureVIP address, we need to reserve a spare IP address for each node, and the IP addresses must use the same subnet as the public network.
What is the use of VIP?
If a node fails, then the node's VIP address fails over to another node on which the VIP address can accept TCP connections but it cannot accept Oracle connections.
Give situations under which VIP address failover happens:-
VIP addresses failover happens when the node on which the VIP address runs fails, all interfaces for the VIP address fails, all interfaces for the VIP address are disconnected from the network.
What is the significance of VIP address failover?
When a VIP address failover happens, Clients that attempt to connect to the VIP address receive a rapid connection refused error .They don't have to wait for TCP connection timeout messages.
What are the administrative tools used for Oracle RAC environments?
Oracle RAC cluster can be administered as a single image using OEM(Enterprise Manager),SQL*PLUS,Servercontrol(SRVCTL),clusterverificationutility(cvu),DBCA,NETCA
How do we verify that RAC instances are running?
Issue the following query from any one node connecting through SQL*PLUS.
$connect sys/sys as sysdba
SQL>select * from V$ACTIVE_INSTANCES;
The query gives the instance number under INST_NUMBER column,host_:instancename under INST_NAME column.
What is FAN?
Fast application Notification as it abbreviates to FAN relates to the events related to instances,services and nodes.This is a notification mechanism that Oracle RAc uses to notify other processes about the configuration and service level information that includes service status changes such as,UP or DOWN events.Applications can respond to FAN events and take immediate action.
Where can we apply FAN UP and DOWN events?
FAN UP and FAN DOWN events can be applied to instances,services and nodes.
State the use of FAN events in case of a cluster configuration change?
During times of cluster configuration changes,Oracle RAC high availability framework publishes a FAN event immediately when a state change occurs in the cluster.So applications can receive FAN events and react immediately.This prevents applications from polling database and detecting a problem after such a state change.
Why should we have seperate homes for ASm instance?
It is a good practice to have ASM home seperate from the database hom(ORACLE_HOME).This helps in upgrading and patching ASM and the Oracle database software independent of each other.Also,we can deinstall the Oracle database software independent of the ASM instance.
What is the advantage of using ASM?
Having ASM is the Oracle recommended storage option for RAC databases as the ASM maximizes performance by managing the storage configuration across the disks.ASM does this by distributing the database file across all of the available storage within our cluster database environment.
What is rolling upgrade?
It is a new ASM feature from Database 11g.ASM instances in Oracle database 11g release(from 11.1) can be upgraded or patched using rolling upgrade feature. This enables us to patch or upgrade ASM nodes in a clustered environment without affecting database availability.During a rolling upgrade we can maintain a functional cluster while one or more of the nodes in the cluster are running in different software versions.
Can rolling upgrade be used to upgrade from 10g to 11g database?
No,it can be used only for Oracle database 11g releases(from 11.1).
State the initialization parameters that must have same value for every instance in an Oracle RAC database:-
Some initialization parameters are critical at the database creation time and must have same values.Their value must be specified in SPFILE or PFILE for every instance.The list of parameters that must be identical on every instance are given below:
ACTIVE_INSTANCE_COUNT
ARCHIVE_LAG_TARGET
COMPATIBLE
CLUSTER_DATABASE
CLUSTER_DATABASE_INSTANCE
CONTROL_FILES
DB_BLOCK_SIZE
DB_DOMAIN
DB_FILES
DB_NAME
DB_RECOVERY_FILE_DEST
DB_RECOVERY_FILE_DEST_SIZE
DB_UNIQUE_NAME
INSTANCE_TYPE (RDBMS or ASM)
PARALLEL_MAX_SERVERS
REMOTE_LOGIN_PASSWORD_FILE
UNDO_MANAGEMENT
Can the DML_LOCKS and RESULT_CACHE_MAX_SIZE be identical on all instances?
These parameters can be identical on all instances only if these parameter values are set to zero.
What two parameters must be set at the time of starting up an ASM instance in a RAC environment?The parameters
CLUSTER_DATABASE and INSTANCE_TYPE must be set.
Mention the components of Oracle clusterware:-
Oracle clusterware is made up of components like voting disk and Oracle Cluster Registry(OCR).
What is a CRS resource?
Oracle clusterware is used to manage high-availability operations in a cluster.Anything that Oracle Clusterware manages is known as a CRS resource.Some examples of CRS resources are database,an instance,a service,a listener,a VIP address,an application process etc.
What is the use of OCR?
Oracle clusterware manages CRS resources based on the configuration information of CRS resources stored in OCR(Oracle Cluster Registry).
How does a Oracle Clusterware manage CRS resources?
Oracle clusterware manages CRS resources based on the configuration information of CRS resources stored in OCR(Oracle Cluster Registry).
Name some Oracle clusterware tools and their uses?
OIFCFG - allocating and deallocating network interfaces
OCRCONFIG - Command-line tool for managing Oracle Cluster Registry
OCRDUMP - Identify the interconnect being used
CVU - Cluster verification utility to get status of CRS resources
What are the modes of deleting instances from ORacle Real Application cluster Databases?
We can delete instances using silent mode or interactive mode using DBCA(Database Configuration Assistant).
How do we remove ASM from a Oracle RAC environment?
We need to stop and delete the instance in the node first in interactive or silent mode.After that asm can be removed using srvctl tool as follows:
srvctl stop asm -n node_name
srvctl remove asm -n node_name
We can verify if ASM has been removed by issuing the following command:
srvctl config asm -n node_name
How do we verify that an instance has been removed from OCR after deleting an instance?
Issue the following srvctl command:
srvctl config database -d database_name
cd CRS_HOME/bin
./crs_stat
How do we verify an existing current backup of OCR?
We can verify the current backup of OCR using the following command : ocrconfig -showbackup
What are the performance views in an Oracle RAC environment?
We have v$ views that are instance specific. In addition we have GV$ views called as global views that has an INST_ID column of numeric data type.GV$ views obtain information from individual V$ views.
What are the types of connection load-balancing?
There are two types of connection load-balancing:server-side load balancing and client-side load balancing.
What is the differnece between server-side and client-side connection load balancing?
Client-side balancing happens at client side where load balancing is done using listener.In case of server-side load balancing listener uses a load-balancing advisory to redirect connections to the instance providing best service.
Give the usage of srvctl:-
srvctl start instance -d db_name -i "inst_name_list" [-o start_options]srvctl stop instance -d name -i "inst_name_list" [-o stop_options]srvctl stop instance -d orcl -i "orcl3,orcl4" -o immediatesrvctl start database -d name [-o start_options]srvctl stop database -d name [-o stop_options]srvctl start database -d orcl -o mount
What are the things you check before you install an oracle database on unix or linux or windows platforms ?
.. memory and desk space, swap space, OS patches, os packages, os group name/ username, os directories, os level kernel parameter, environment variable,
How do you identify the pre-req os patches required for an oracle installation on HP or solaris or Linux platforms?
..refer read me provided / installation doc specific to OS type and version...
How do you identify if the oracle software version you are installing is certified on the platform you are installing ?
.. Metalink
What is emulator on unix and why do you need it? What is xterm ?
..X means the X Window System, which is the basis of Linux graphical user interface. Xterm is a terminal emulator program that uses X.
What is XAUTHORITY env settings ?
.. DISPLAY tells a program which "display" to run on (Linux typically has multiple virtual consoles). :0 is the default one; you can configure more. XAUTHORITY is related to the xauth system. The xauth system manages privileges for connecting to the X server, which runs as root. Because the program cannot find the authority file that it thinks you're specifying in XAUTHORITY, it can't get permission to connect.
What is root.sh ?
.. root.sh, which is a shell script, updates/creates an oratab file and environment variable like – ORACLE_HOME, OWNER, LD_LIBRARY, etc. The oratab file is the file where the user will place references to all databases to be discovered by the agent and controlled by the Oracle Enterprise Manager. For each database created, the entry is of the form: :<$ORACLE_HOME>:[Y/N]
What is the importance of /etc/oraInst.loc or /var/opt/oracle/oraInst.loc ?
.. Oracle keeps its inventory of installed products in a directory
Why do you need environment file ? Does this get created automatically ? What the environment settings which are needed in environment file ?
What is oraenv,dbshut,dbstart,dbhome files ? Where can you find these ? Explain me about these files ?
What is the importance of /etc/oratab or /var/opt/oracle/oratab ?
--Is used to locate databases running and their respective ORACLE_HOME and used to start dbs during OS boot
What are the various kernel settings which you setup in oracle install on linux/unix ?
--shmmax,shmmin, semmls, semmns, semopm, shmall, file-max, rmem-max, wmem-default
SEMMNI - Defines the maximum number of semaphore sets in the entire system.
SEMMNS - Defines the maximum semaphores on the system. This setting is a minimum recommended value, for initial installation only.
The SEMMNS parameter should be set to the sum of the PROCESSES parameter for each Oracle database, adding the largest one twice, and then adding an additional 10 for each database.
SEMMSL - Defines the minimum recommended value, for initial installation only.
SHMMAX - Defines the maximum allowable size of one shared memory segment.4 GB = 4294967295
SHMMIN - Defines the minimum allowable size of a single shared memory segment.
SHMMNI - Defines the maximum number of shared memory segments in the entire system.
SHMSEG - Defines the maximum number of shared memory segments one process can attach.
Why environment file is not needed in windows?
What is oradim ? Why do you need this tool in windows ?
..The dbstart and dbstop shell scripts do not exist on Windows latforms. Consequently Oracle database startup and shutdown is implemented completely differently. The oradim utility is used on the Windows platform to perform these tasks.
When do you need tnsnames.ora listener.ora and sqlnet.ora ? How do you configure these files ?
..The Transparent Network Substrate (TNS), provided by Oracle, allows distributed communications between databases.
The TNS, which is a distributed protocol, allows for transparent database communications between remote systems. The TNS serves as an insulator between Oracle's logical data request and the physical communications between the remote servers. As such, the network administrator is able to control much of the network performance tuning. The Oracle administrator, then, has little control over the network settings that can affect overall database performance:
What is Net8 or SQL*Net means ? How do you get this software ?
-- Net8 (called SQL*Net prior to Oracle8) is Oracle's client/server middleware product that offers transparent connection from client tools to the database, or from one database to another.
Net8 enables services and their applications to reside on different computers and communicate as peer applications. The main function of Net8 is to establish network sessions and transfer data between a client machine and a server or between two servers. Net8 is located on each machine in the network. Once a network session is established, Net8 acts as a data courier for the client and the server.
Essentially, SQL*Net provides the software layer between Oracle and the networking software, providing seamless communication between an Oracle client machine (running, for example, SQL*Plus) and the database server or from one database server to another. SQL*Net/ Net8 works across multiple network protocols and operating systems.
TNS or Transparent Network Substrate is Oracle's networking architecture. TNS provides a uniform application interface to enable network applications to access the underlying network protocols transparently.
The TNS architecture consists of three software components:
- TNS-based applications (like SQL*Plus or TOAD)
- Oracle Protocol Adapters (OPA)
- Networking software like TCP/IP
What is a bequeath session?
A bequeath session is a session for which the listener spawned a Dedicated Server Process and then passed (bequeaths) the connection to that server process.
What are the different sql net naming methods ?
-- TNSNAMES, LDAP, ONAMES, HOSTNAME,EZCONNECT.
What is onames or OID ?
- ONAMES was old version and OID have replaced by OID -Oracle internet directory
- TNSNames is a local naming method were connection information is stored locally on each client.
ONAMES (desupported now) is a centralized naming method where connection information is stored in a centralized directory server.
Local naming is suitable when you have small amount of clients as adding/changing database connection information will result in changing the tnsnames.ora file on each client. If you have large amount of clients you might want to look at centralized naming.
In centralized naming, changes are made only in the directory server and clients see the new information when making a connection.
-
What is semaphore ? How do you handle the instance crash ?
Shared memory and semaphores are amongst the most important resources for an Oracle instance. In normal situations the semaphore and shared memory segments are released when oracle is shutdown. However being database administrator we mostly encounter the scenario where Oracle instance crashes abruptly and its shared memory and semaphore are not released. The situation becomes critical if you have many multiple instances running on the same machine as it is unclear which shared memory and semaphore sets to kill. If you are able to identify which shared memory and semaphore segments are owned by a particular instance then it can help you in recovery situations where the database instance has not released its shared memory and semaphores on database shutdown.
Identifying semaphores and shared memory segments:
The ipcs command can help you identify the semaphores and shared memory segments used by Oracle. You can run following command from $ prompt on the UNIX box. You will see some entries if your instance is up and running or the instance has gone but memory and semaphores are not released.
$ipcs | grep oracle
You will see two segments. The segment that represents shared memory is denoted by m whereas the segment that represents semaphores is denoted by s.
Releasing semaphores and shared memory segments for single instance:
If you are running single instance, it is very easy to release the semaphore and shard memory. You can run below commands for it.
$ipcrm -m id
In the above command id will be the id displayed for memory in ipcs under memory section.
$ipcrm -s id
How do you run the o/s commands from oracle?
What is mutating trigger ?
--The Oracle mutating trigger error occurs when a trigger references the table that owns the trigger, resulting in the "ORA-04091: table name is mutating, trigger/function may not see it." message.
Can we use commit inside a trigger ?
--NO
How to enable the auditing in oracle ?
- 1. 1. Set audit_trail parameter to- OS, DB, XML etc,..
- 2. 2. Set audit_dest = local location
- 3. 3. Set audit_sys_operations = true / false
DBA_AUDIT_TRAIL - Standard auditing only (from AUD$).
DBA_FGA_AUDIT_TRAIL - Fine-grained auditing only (from FGA_LOG$).
DBA_COMMON_AUDIT_TRAIL - Both standard and fine-grained auditing.
How do you send an email from oracle db ?
Steps to enable Mailing from Database
1. sqlplus ‘/ as sysdba’
2. @$ORACLE_HOME/rdbms/admin/utlmail.sql
3. @$ORACLE_HOME/rdbms/admin/prvtmail.plb
4. Set smtp_server information in init.ora or spfile.ora
alter system set smtp_out_server = ‘SMTP_SERVER_IP_ADDRESS:SMTP_PORT’ scope=both;
25 = Default SMTP Port
If instance had been started with spfile
eg: alter system set smtp_out_server = ’172.25.90.165:25' scope=both;
If instance had been started with pfile
alter system set smtp_out_server = ’172.25.90.165:25';
Also make below entry in your initSID.ora
smtp_out_server = ’172.25.90.165:25'
Thats It, your database is configured to send emails ….
How to send an email
1. sqlplus ‘/ as sysdba’
2. exec utl_mail.send((sender => ‘oraclepitstop@ab.com’, recipients => ‘oraclepitstop@ab.com’, subject => ‘Testing UTL_MAIL Option’, message => ‘blah blah blah’);
3. Check the inbox of the email id, to verify the email receipt.
To enable other DB users to use this functionality, grant execute permission on UTL_MAIL package.
eg: grant execute on utl_mail to apps;
How can you schedule the monitoring from the database ?
The Automatic Database Diagnostic Monitor (ADDM) analyzes data in the Automatic Workload Repository (AWR) to identify potential performance bottlenecks. For each of the identified issues it locates the root cause and provides recommendations for correcting the problem. An ADDM analysis task is performed and its findings and recommendations stored in the database every time an AWR snapshot is taken provided theSTATISTICS_LEVEL parameter is set to TYPICAL or ALL. The ADDM analysis includes the following.
- CPU load
- Memory usage
- I/O usage
- Resource intensive SQL
- Resource intensive PL/SQL and Java
- RAC issues
- Application issues
- Database configuration issues
- Concurrency issues
- Object contention
Give me the detail steps for creating the database manually ?
- 1. Create directory structure at os level
- 2. Prepare create database script and init.ora
- 3. Set oracle sid, get connected to the db
- 4. Startup db and execute script created in step2
- 5. Execute catalog and cataproc and related required script
- 6. Configure listener
Give me the detail steps for creating the database using DBCA ?
Set oracle sid, home invoke dbca and
How to identify the version of the rdbms ?
Select * from v$version;
How to identify the database components and their statuses ?
SELECT * FROM PRODUCT_COMPONENT_VERSION;
DBA_REGISTRY displays information about the components loaded into the database.
How to identify if I am using 64 bit oracle or 32 bit oracle ?
File oracle
How do you approach a new database implementation for a dw or oltp project ? Tell me about key things which you take into consideration ?
Tell me about various RAID levels ?
Various RAID levels exist these are 0, 1, 2, 3, 4, 5, 6, 7, 10, & 0+1. The levels of RAID protection varies with the RAID level selected RAID levels 0 & 1 are not technically RAID as they have no redundancy in the event of drive failure. The most common RAID levels are shown below. RAID levels 2, 4, 6, 7 & 0+1 are a combination of the other RAID levels shown.
Explain me about oracle memory structures and background processes ?
What is oracle instance ? What is Oracle SGA ? What are the various components exist in Oracle SGA ?
What is PGA ? Tell about the responsibilities of background processes such as pmon,smon,arc,rec,lck,qmn,aq,lgwr,dbwr ?
Which process retrieves the data from disk into memory ?
Server process
What is advanced queuing ? How do you enable this ?
Tell me about checkpoint process in oracle and SCN numbers in oracle.
System change number(SCN) is represented with SCN_WRAP and SCN_BASE. Whenever SCN_BASE reaches 4294967290 (2 power 32), SCN_WRAP goes up by one and SCN_BASE will be reset to 0. This way you can have a maximum SCN at 1.8E+19.
SCN = (SCN_WRAP * 4294967290) + SCN_BASE
Checkpoint number is the SCN number at which all the dirty buffers are written to the disk, there can be a checkpoint at object/tablespace/datafile/database level.
Checkpoint number is never updated for the datafiles of readonly tablespaces.
What is the difference between dedicated and shared servers processes ?
Dedicated server connection:
A dedicated process has one to one relationship with user process. The user connected with dedicated server connection has the dedicated resource that is whether or not the user is doing work the connection remains allocated to that user. Thus if your user is not working and if there is less network resource then this user is blocking another user to connect or to execute the user request by making this user works queued. This connection is preferable when you have lot of resource or to some administrative user like "SYS" , "SYS" for administrative works and for batch jobs,
Shared server connection:
Shared server architecture consists of Listener Process, Dispatcher Process, Request Queue, Shared server process and Response Queue. Network Listener process listens the user process request. If user process request requires a dedicated server process, listener process starts a dedicated server process. If the request can be assigned to a shared server process, then the request is forwarded to dispatcher process. Shared server configuration requires at least on dispatcher process. Dispatcher process places the request on request queue. Request Queue is created in SGA and shared by all dispatcher processes. On of free Shared server process picks up the request from request queue. After processing the request, shared server process places the result in response queue. Each dispatcher process has its own response queue in SGA. Dispatcher knows about the user process which placed the request, so the response is returned back to user process.
When there is shared server the user connection is shared, that is user is connecting by dispatchers and when a user is idle his resource can be used by other users, thus lessen the load on system, this is more likely in a environment where user is mostly idle, like in a order entry system.
In what kind of configuration, you see oracle dispatcher process ?
http://docs.oracle.com/cd/B10500_01/server.920/a96524/c09procs.htm - read from this
Explain me about the instance recovery processes such as rollforward and rollback ?
Explain me about library cache and data dictionary cache?
Tell me the difference between startup restrict and startup?
startup restrict - When the database is started in restricted session mode, only users(such as those with roleDBA) with the system privilege RESTRICTED SESSION can connect to the database. Use the restricted mode when performing maintenance or when you want certain users to be able to log in.
Startup – is regular. All user can get into the database.
Explain me about the various stages of oracle startup process?
-nomount, mount, open
Explain me about the oracle instance recovery ?
If the Oracle instance fails any information in the SGA that is not been written back to disk is lost. For eg, the failure of the OS causes an instance failure. After the loss of the instance, the BG Process SMON automatically performs instance recovery when the database is reopened.
It will go through the following process.
1) ROLL FORWARD Phase:
Rolling forward to recover data that has not been recorded in the data files but that has been recorded in the online redo log. This data has not been written to disk because of the loss of the SGA during instance failure. During this process, SMON reads the redo log files and applies the changes recorded in the redo log to the data blocks. Because we have all committed and uncommitted transactions in the redo log files, this process recovers the transactions.
2) OPEN the DATABASE:
Opens the database for user access so that users can log on to the database and do their operations and key thing here is data that is not locked by unrecovered transactions is available immediately.
3) ROLLBACK Phase:
as name itself clearly depicts, rollback the uncommitted transactions.
Explain me about the difference between shutdown transactional and shutdown normal ?
What are the various shutdown options which can be used and explain me about those ?
Shutdown, shutdown transactional, shutdown immediate; shutdown abort.
What are the various startup options which can be used and explain me about those ?
How do you identify the oracle alert log file ? controlfile ? trace file ? tns file ? listener file ?
How do you identify the software location(oracle_home) for a running database ?
Echo $ORACLE_HOME
List at least 25 famous unix commands which are needed for daily day-to-day dba work?
Ps –ef, ps—eaf, diff, dos2unix, df –k, du –k, ftp, sftp, scp, ssh, ipcs, mkdir, cd, pwd, mv, cp –pr, vmstat, iostat, netstat,
How do you drop user tablespace and users ?
Drop user username cascade;
Explain me the database views which you look at to identify the physical structures of the database ?
V$datafile, v$tempfile; v$controlfile, v$log;
Explain me the database views which you look at to identify the logical structures of the database ?
V$tablespace,
How do you calculate/identify the total database current size?
Select sum(bytes) from dba_segments;
How do you calculate/identify the table current size?
Dba_segments
How do you calculate/identify the tablespace current size ?
Select tablespace_name, sum(blocks) Totalblocks, sum(bytes/(1024*1024)) TotalMB From dba_segments Group By tablespace_name
How do you calculate/identify the freespace in the database?
Note the in-line view wheredba_data_files is joined into dba_free_space:
SELECT
a.tablespace_name,
a.file_name,
a.bytes allocated_bytes,
b.free_bytes
FROM
dba_data_files a,
(SELECT file_id, SUM(bytes) free_bytes
FROM dba_free_space b GROUP BY file_id) b
WHERE
a.file_id=b.file_id
ORDER BY
a.tablespace_name;
How do you move the table from one tablespace to another tablespace?
-Alter table ... Move
How do you resize the logfiles of the database ?
-Switch logfile and execute - ALTER SYSTEM CHECKPOINT GLOBAL;
, add new redo files of appropriate size and the drop the old ones..
How do you add the log groups to the database ?
-Alter database add logfile group 1;
How do you drop the redog log groups ?
ALTER DATABASE DROP LOGFILE GROUP 1;
How do you rename/move the redo log members from one disk to another disk ?
Above
How do you identify the user default tablespace?
-In 10g - SELECT PROPERTY_VALUE FROM DATABASE_PROPERTIES WHERE PROPERTY_NAME = ‘DEFAULT_PERMANENT_TABLESPACE’;
ALTER DATABASE DEFAULT TABLESPACE abc;
Can also check in dba_users table;
How do you change the user default tablespace?
-Alter user ...
How do you specify the tablespace name when you create the table ? What is the syntax ?
-Create table .. tablespece ...
How do you add the datafiles to the existing tablespace ?
-Alter tablespace add datafile ..
Give me the syntax for creating the dictionary managed and locally managed tablespaces ?
.. extent management local;
Give me the syntax for resizing the datafile ?
..Alter database datafile ..resize;
How do you rename the database tablespaces ?
..ALTER TABLESPACE users RENAME TO usersts;
How do you rename the database datafiles ?
..take tablesapace offline and rename data files..
ALTER TABLESPACE users OFFLINE NORMAL; and ALTER TABLESPACE users RENAME DATAFILE '/u02/oracle/rbdb1/user1.dbf', '/u02/oracle/rbdb1/user2.dbf' TO '/u02/oracle/rbdb1/users01.dbf', '/u02/oracle/rbdb1/users02.dbf';
How do you relocate the datafiles for system tablespace ?
This procedure used to move the system tablespace datafiles as we can not off line the system tablespace like any other.This procedure will work for all tablespaces.
Note: The database will not be availabel when you perform this operation & before attempting this procedure make a full cold backup of the database.
1.Shut down the database with immediate or normal option only.
2.copy the datafiles of system tablespace using operating system command to new location porvided that you know the datafiles full path for the system tablespace.Check for both file sizes after copy.
3.Mount the database using command startup mount.
4.Rename the datafile to new file locations.
5.Now open the database.
How do you make the tablespace offline. What is the advantage. ?
..alter tablespace..offline
How do you make the tablespace readonly. What is the advantage. ?
ALTER TABLESPACE flights READ ONLY;.. on one can change data from this tablespace
What is difference between readonly/offline ?
Making a tablespace read-only prevents write operations on the datafiles in the tablespace.Bringing an online tablespace offline, makes the schema objects within the tablespace unavailable to the database users
How do you identify the datafile associated with a table? How do you know how big the table size is ?
Dba_extents has file_id and query with segment_name=’tablename’
How do you add,rename the temp datafiles ?
.. Rename – shutdown, startup mount, alter dataset rename.., alter database open,
Add temp datafile –
How do you identify the archive logs location ?
Archive log list / archive dest in init,ora / V$ARCHIVE_DEST;
How do you change the archive log location?
.. archive log list; alter system archive log start to ‘newloc’; archive log list;
And alter system set log_archive_dest='newloc’ scope=spfile..
Check .. show parameter log_archive_dest
How do you change the archive log format ?
..ALTER SYSTEM SET log_archive_format='t_%s_%r.dbf' SCOPE=spfile;
How do you enable the archive log, disable the archive log? Explain in detail about database instance?
..shutdown, startup mount, alter database archivelog, alter database open, ..prior it set init.ora
How do you identify if the database is running in archive log mode or no-archive log mode?
..Archive log list
What is export import ?
Export / import is a logical export / import.
How do you change the db char set?
..shutdown, take backup, startup, do .csscan and convert using csalter(csalter changes only metadata
How do we change db block size?
..The database block size is fixed during database creation time and cannot be changed without database re-creation. db_block_size in the PFILE is used to determine the block size for a database being created. (Check o/s documentation to determine valid settings for db_block_size.)
Give me the syntax to export the entire database?
Exdp full=y
Give me the syntax to import the entire database. ?
Impdp full=y
Give me the syntax to export one schema in the database?
Give me the syntax to export one table in the database?
Exp tables=’’
What is data pump?
.. new version starting from 10g. Needs directory parameter set and permission on it to use for pump
Tell me about transportable tablespaces?
..A transportable tablespace allows you to quickly move a subset of an Oracle database from one Oracle database to another. However, in the previous release of Oracle server, you can only move a tablespace across Oracle databases within the same platform.
Oracle 10g is going one step further by allowing you to move tablespace across different platforms.
Tell me about your experience with automation of startup shutdown in unix and windows?
..Put entry in rc*.d to call $ORACLE_HOME/bin/dbstart /$ORACLE_HOME/bin/dbstut..
Did you ever heard of rc.d ? Tell me about what do you know about this ?
..The actual scripts that control services are in /etc/rc.d. These scripts are automatically run at boot time, but they can be called manually if necessary. The following example shows how to start the SSH daemon that we enabled in the previous section:
#/etc/rc.d/sshd start
Starting sshd.
Later, if you wish to stop the SSH daemon, run the following command:
#/etc/rc.d/sshd stop
Stopping sshd.
Waiting for PIDS: 123.
Tell me about your experience with using data pump ?
Tell me about flash back recovery feature of 10g ?
..Flashback query allows a user to view the data quickly and easily the way it was at a particular time in the past, even when it is modified and committed, be it a single row or the whole table.
Tell me about your experience with DBMS_STATS?
.. used to collecr statistics
How do you collect the dictionary stats in 9i and 10g ?
.. dbms_stats.delete_schema_stats('SYS'); dbms_stats.gather_schema_stats('SYS');
How do you speed up the export/import process of entire database? What approach/settings u use ?
.. big buffer size, direct=y, RECORDLENGTH to high value,
.. datapump - user parallel, set analyze no, set index =n, create themafterwork
What is external tables ? Give me the steps to set this up ?
External Tables let you query data in a flat file as though the file were an Oracle table. In 9i, only read operations were permitted; in 10g, you can also write out data to an external table, although you can't write to an existing table.
create table xtern_empl_rpt) organization external (default directory xtern_data_dir.. access parameters ( records delimited by newline fields terminated by ',' ) location ('employee_report.csv')
Give me the steps to recover a drop table in 10g ?
FLASHBACK**TABLE****TO****BEFORE****DROP**
What is table delete vs table truncate in 10g ?
Delete is dml..rollback is possible..
Delete Command features and advantages:
- Command is for deleting the rows from the table.
- Can delete selective rows from the table using where clause.
- All rows can be deleted if no where clause is specified.
- It needs a commit or rollback command to complete the transaction or make it as permanent change.
- It is DML command.
- Delete will generate redo log entries
- Delete command use the Undo tablespace to rollback the transaction
- Delete operation is slow compared to truncate command as it generates redo log entries.
- Delete operation fires the delete triggers on that particular table.
- You can grant delete table privilege to a user
- Delete never de-allocate the space. It needs a table reorganization to claim the free space.
- Delete doesn’t reset the high watermark
Truncate Command features and advantages
- Truncate command removes the entire rows from the table
- Truncate command cannot be used for selective deletes.
- There is no rollback option with truncate command
- Truncate is DDL command. Obviously implicit commit is followed by truncate
- There will not be any undo generation so this will not use undo tablespace
- Truncate does not generate redo log entries
- Truncate is faster compared to delete command
- Underling triggers will not get fired for truncate command
- You cannot grant truncate table privilege to a user
- Truncate de-allocate the space
- Truncate makes unusable index usable again
- You cannot flashback truncate command
- Truncate reset the high watermark
How do you rewind the database in 10g ? Give me the steps ?
..flashback database to scn 3369533;
Give me the steps to recover the deleted rows using flashback query in 10g ?
What is SAR?
..Displays the activity for the CPU.
What is DISM?
..DISM = Dynamic Intimate Shared memory, which is used to support oracle in Solaris Envirnoment, DISM is only supported from Solaris 9 and above version
RAC Interview Questions -
Oracle RAC(Real Application Cluster) DBA Interview Questions
Oracle RAC(Real Application Cluster) DBA Interview Questions
What is RAC?
RAC stands for Real Application cluster. It is a clustering solution from Oracle Corporation that ensures high availability of databases by providing instance failover, media failover features.
Mention the Oracle RAC software components:-
Oracle RAC is composed of two or more database instances. They are composed of Memory structures and background processes same as the single instance database. Oracle RAC instances use two processes GES(Global Enqueue Service), GCS(Global Cache Service) that enable cache fusion. Oracle RAC instances are composed of following background processes:
ACMS—Atomic Controlfile to Memory Service (ACMS)
GTX0-j—Global Transaction Process
LMON—Global Enqueue Service Monitor
LMD—Global Enqueue Service Daemon
LMS—Global Cache Service Process
LCK0—Instance Enqueue Process
RMSn—Oracle RAC Management Processes (RMSn)
RSMN—Remote Slave Monitor
What is GRD?
GRD stands for Global Resource Directory. The GES and GCS maintains records of the statuses of each datafile and each cahed block using global resource directory.This process is referred to as cache fusion and helps in data integrity.
Give Details on Cache Fusion:-
Oracle RAC is composed of two or more instances. When a block of data is read from datafile by an instance within the cluster and another instance is in need of the same block, it is easy to get the block image from the instance which has the block in its SGA rather than reading from the disk. To enable inter instance communication Oracle RAC makes use of interconnects. The Global Enqueue Service (GES) monitors and Instance enqueue process manages the cahce fusion.
Give Details on ACMS:-
ACMS stands for Atomic Controlfile Memory Service.In an Oracle RAC environment ACMS is an agent that ensures a distributed SGA memory update(ie)SGA updates are globally committed on success or globally aborted in event of a failure.
Give details on GTX0-j :-
The process provides transparent support for XA global transactions in a RAC environment.The database autotunes the number of these processes based on the workload of XA global transactions.
Give details on LMON:-
This process monitors global enques and resources across the cluster and performs global enqueue recovery operations.This is called as Global Enqueue Service Monitor.
Give details on LMD:-
This process is called as global enqueue service daemon. This process manages incoming remote resource requests within each instance.
Give details on LMS:-
This process is called as Global Cache service process. This process maintains statuses of datafiles and each cahed block by recording information in a Global Resource Dectory(GRD).This process also controls the flow of messages to remote instances and manages global data block access and transmits block images between the buffer caches of different instances.This processing is a part of cache fusion feature.
Give details on LCK0:-
This process is called as Instance enqueue process.This process manages non-cache fusion resource requests such as libry and row cache requests.
Give details on RMSn:-
This process is called as Oracle RAC management process.These pocesses perform managability tasks for Oracle RAC.Tasks include creation of resources related Oracle RAC when new instances are added to the cluster.
Give details on RSMN:-
This process is called as Remote Slave Monitor.This process manages background slave process creation andd communication on remote instances. This is a background slave process.This process performs tasks on behalf of a co-ordinating process running in another instance.
What components in RAC must reside in shared storage?
All datafiles, controlfiles, SPFIles, redo log files must reside on cluster-aware shred storage.
What is the significance of using cluster-aware shared storage in an Oracle RAC environment?
All instances of an Oracle RAC can access all the datafiles,control files, SPFILE's, redolog files when these files are hosted out of cluster-aware shared storage which are group of shared disks.
Give few examples for solutions that support cluster storage:-
ASM(automatic storage management),raw disk devices,network file system(NFS), OCFS2 and OCFS(Oracle Cluster Fie systems).
What is an interconnect network?
An interconnect network is a private network that connects all of the servers in a cluster. The interconnect network uses a switch/multiple switches that only the nodes in the cluster can access.
How can we configure the cluster interconnect?
Configure User Datagram Protocol(UDP) on Gigabit ethernet for cluster interconnect.On unia and linux systems we use UDP and RDS(Reliable data socket) protocols to be used by Oracle Clusterware.Windows clusters use the TCP protocol.
Can we use crossover cables with Oracle Clusterware interconnects?
No, crossover cables are not supported with Oracle Clusterware intercnects.
What is the use of cluster interconnect?
Cluster interconnect is used by the Cache fusion for inter instance communication.
How do users connect to database in an Oracle RAC environment?
Users can access a RAC database using a client/server configuration or through one or more middle tiers ,with or without connection pooling.Users can use oracle services feature to connect to database.
What is the use of a service in Oracle RAC environemnt?
Applications should use the services feature to connect to the Oracle database.Services enable us to define rules and characteristics to control how users and applications connect to database instances.
What are the characteriscs controlled by Oracle services feature?
The charateristics include a unique name, workload balancing and failover options,and high availability characteristics.
Which enable the load balancing of applications in RAC?
Oracle Net Services enable the load balancing of application connections across all of the instances in an Oracle RAC database.
What is a virtual IP address or VIP?
A virtl IP address or VIP is an alternate IP address that the client connectins use instead of the standard public IP address. To configureVIP address, we need to reserve a spare IP address for each node, and the IP addresses must use the same subnet as the public network.
What is the use of VIP?
If a node fails, then the node's VIP address fails over to another node on which the VIP address can accept TCP connections but it cannot accept Oracle connections.
Give situations under which VIP address failover happens:-
VIP addresses failover happens when the node on which the VIP address runs fails, all interfaces for the VIP address fails, all interfaces for the VIP address are disconnected from the network.
What is the significance of VIP address failover?
When a VIP address failover happens, Clients that attempt to connect to the VIP address receive a rapid connection refused error .They don't have to wait for TCP connection timeout messages.
What are the administrative tools used for Oracle RAC environments?
Oracle RAC cluster can be administered as a single image using OEM(Enterprise Manager),SQL*PLUS,Servercontrol(SRVCTL),clusterverificationutility(cvu),DBCA,NETCA
How do we verify that RAC instances are running?
Issue the following query from any one node connecting through SQL*PLUS.
$connect sys/sys as sysdba
SQL>select * from V$ACTIVE_INSTANCES;
The query gives the instance number under INST_NUMBER column,host_:instancename under INST_NAME column.
What is FAN?
Fast application Notification as it abbreviates to FAN relates to the events related to instances,services and nodes.This is a notification mechanism that Oracle RAc uses to notify other processes about the configuration and service level information that includes service status changes such as,UP or DOWN events.Applications can respond to FAN events and take immediate action.
Where can we apply FAN UP and DOWN events?
FAN UP and FAN DOWN events can be applied to instances,services and nodes.
State the use of FAN events in case of a cluster configuration change?
During times of cluster configuration changes,Oracle RAC high availability framework publishes a FAN event immediately when a state change occurs in the cluster.So applications can receive FAN events and react immediately.This prevents applications from polling database and detecting a problem after such a state change.
Why should we have seperate homes for ASm instance?
It is a good practice to have ASM home seperate from the database hom(ORACLE_HOME).This helps in upgrading and patching ASM and the Oracle database software independent of each other.Also,we can deinstall the Oracle database software independent of the ASM instance.
What is the advantage of using ASM?
Having ASM is the Oracle recommended storage option for RAC databases as the ASM maximizes performance by managing the storage configuration across the disks.ASM does this by distributing the database file across all of the available storage within our cluster database environment.
What is rolling upgrade?
It is a new ASM feature from Database 11g.ASM instances in Oracle database 11g release(from 11.1) can be upgraded or patched using rolling upgrade feature. This enables us to patch or upgrade ASM nodes in a clustered environment without affecting database availability.During a rolling upgrade we can maintain a functional cluster while one or more of the nodes in the cluster are running in different software versions.
Can rolling upgrade be used to upgrade from 10g to 11g database?
No,it can be used only for Oracle database 11g releases(from 11.1).
State the initialization parameters that must have same value for every instance in an Oracle RAC database:-
Some initialization parameters are critical at the database creation time and must have same values.Their value must be specified in SPFILE or PFILE for every instance.The list of parameters that must be identical on every instance are given below:
ACTIVE_INSTANCE_COUNT
ARCHIVE_LAG_TARGET
COMPATIBLE
CLUSTER_DATABASE
CLUSTER_DATABASE_INSTANCE
CONTROL_FILES
DB_BLOCK_SIZE
DB_DOMAIN
DB_FILES
DB_NAME
DB_RECOVERY_FILE_DEST
DB_RECOVERY_FILE_DEST_SIZE
DB_UNIQUE_NAME
INSTANCE_TYPE (RDBMS or ASM)
PARALLEL_MAX_SERVERS
REMOTE_LOGIN_PASSWORD_FILE
UNDO_MANAGEMENT
Can the DML_LOCKS and RESULT_CACHE_MAX_SIZE be identical on all instances?
These parameters can be identical on all instances only if these parameter values are set to zero.
What two parameters must be set at the time of starting up an ASM instance in a RAC environment?The parameters
CLUSTER_DATABASE and INSTANCE_TYPE must be set.
Mention the components of Oracle clusterware:-
Oracle clusterware is made up of components like voting disk and Oracle Cluster Registry(OCR).
What is a CRS resource?
Oracle clusterware is used to manage high-availability operations in a cluster.Anything that Oracle Clusterware manages is known as a CRS resource.Some examples of CRS resources are database,an instance,a service,a listener,a VIP address,an application process etc.
What is the use of OCR?
Oracle clusterware manages CRS resources based on the configuration information of CRS resources stored in OCR(Oracle Cluster Registry).
How does a Oracle Clusterware manage CRS resources?
Oracle clusterware manages CRS resources based on the configuration information of CRS resources stored in OCR(Oracle Cluster Registry).
Name some Oracle clusterware tools and their uses?
OIFCFG - allocating and deallocating network interfaces
OCRCONFIG - Command-line tool for managing Oracle Cluster Registry
OCRDUMP - Identify the interconnect being used
CVU - Cluster verification utility to get status of CRS resources
What are the modes of deleting instances from ORacle Real Application cluster Databases?
We can delete instances using silent mode or interactive mode using DBCA(Database Configuration Assistant).
How do we remove ASM from a Oracle RAC environment?
We need to stop and delete the instance in the node first in interactive or silent mode.After that asm can be removed using srvctl tool as follows:
srvctl stop asm -n node_name
srvctl remove asm -n node_name
We can verify if ASM has been removed by issuing the following command:
srvctl config asm -n node_name
How do we verify that an instance has been removed from OCR after deleting an instance?
Issue the following srvctl command:
srvctl config database -d database_name
cd CRS_HOME/bin
./crs_stat
How do we verify an existing current backup of OCR?
We can verify the current backup of OCR using the following command : ocrconfig -showbackup
What are the performance views in an Oracle RAC environment?
We have v$ views that are instance specific. In addition we have GV$ views called as global views that has an INST_ID column of numeric data type.GV$ views obtain information from individual V$ views.
What are the types of connection load-balancing?
There are two types of connection load-balancing:server-side load balancing and client-side load balancing.
What is the differnece between server-side and client-side connection load balancing?
Client-side balancing happens at client side where load balancing is done using listener.In case of server-side load balancing listener uses a load-balancing advisory to redirect connections to the instance providing best service.
Give the usage of srvctl:-
srvctl start instance -d db_name -i "inst_name_list" [-o start_options]srvctl stop instance -d name -i "inst_name_list" [-o stop_options]srvctl stop instance -d orcl -i "orcl3,orcl4" -o immediatesrvctl start database -d name [-o start_options]srvctl stop database -d name [-o stop_options]srvctl start database -d orcl -o mount
No comments:
Post a Comment