1)Explain the categories of oracle processes i.e. user, data writing processes, logging processes and monitoring processes. • User process – User process is used in invocation of application software. • Data writing process - A database writer process is used to write buffer content into a datafile. They are specifically used to write “dirty block” to data files from the buffer. • Logging processes - Log writer is used to write the redo log buffer from system global area to online redo log file. Only those redo entries are written hat have been copied into the buffer since the last time it wrote. • Monitoring process - this can be either a system monitor process or a process monitor process. System monitor process is mainly used for crash recovery and cleaning up of temporary segments. Process monitor is used to clean all resources acquired by a failed process 2)What are the Back ground processes in Oracle? There are a number of background processes running. Each process is used for a specific job. • SMON – system monitor process is used to clean temporary segments and crash recovery of a crashed instance. • PMON – process monitor is used to clean all resources acquired by a failed process. • DBWR- Database writer is used to write “dirty block” to data files from the buffer. • LGWR - Log writer is used to write the redo log buffer from system global area to online redo log file. • MMAN – Memory manager process is used to manage the memory. • ARCn - Archiver 0-9 is used for writing the online redo log files to the mentioned archive log destination after a log switch has occurred. • CKPT – checkpoint process using the checkpoints information signals the synchronization of all database files. Used for database consistency and faster database recovery. • LCK0 -9 – LOCK process is used to lock shared resources for manages the global enqueue requests and the cross-instance broadcast 3)What are background processes in oracle? Oracle uses background process to increase performance. Database writer, DBWn Log Writer, LGWR Checkpoint, CKPT System Monitor, SMON Process Monitor, PMON Archiver, ARCn 4)Oracle - What are background processes in oracle? - June 18, 2009 at 18:00 PM Following are the background processes in oracle. Database writer (DBWR) Writes Dirty blocks from the database buffer cache to the data files. Log writer (LGWR) Performs sequential writes from redo log buffer to the redo log files. System Monitor (SMON) Helps to recover the crashed instance into the next new instance Checkpoint (CKPT) Identifies the point from where recovery should begin. 5)Oracle memory area - Explain the areas of memory used by oracle, i.e. Software code area, system global area (SGA), program global area(PGA), sort area. Software area code: - It is a protected location that is used to store oracle code that is supposed to be run. The location is different from users programs. The software area code is read only and can be shared or nonshared. System global area: - SGA is used to store data and control information of an Oracles database instance when started. Each instance has its own SGA. SGA is read write. It comprises data structures like Redo buffer, Java pool, Streams pool etc. Program global area:- PGA contains information about server process. It is a non shared memory when a server process is started. Each process has an exclusive PGA. Sort area: - SQL work area is typically used for complex queries by memory-intensive operators such as Sort area. A sort operator is used to perform the in-memory sort of a set of rows 6)Oracle architecture Describe Oracle architecture in brief, What is the function of SMON?, Explain different types of segment, Explain SGA memory structures.............. Oracle database tuning Automatic Performance Diagnostic and Tuning Features, Automatic Shared Memory Management - Automatic Shared Memory Management puts Oracle in control of allocating memory within the SGA.......... What is Program Global Area (PGA)? The PGA is a memory area that contains data and control information for the Oracle server processes. This area consists of the following components:............. What is a shared pool? It is the area in SGA that allows sharing of parsed SQL statements among concurrent users..... 7)Explain the types of data files used by the oracle RDBMS. Online redo log files A control file contains information such as location of redo log files, backup data and redo log information. The control file is updated to reflect the structure changes every time a datafile or a redo log file is added or dropped from the database. These records are necessary to identify datafiles and redo log files during startup and recovery. 8)Archive log files Archived log files are redo logs that Oracle fills with redo entries that are rendered inactive and copied to one or more log archives. Running the database in ARCHIVELOG mode has the following benefits: • Complete recovery of database from instance and media failure. • Performing online backups. • Transmitting and applying archived redo logs to the standby database. • Multiplexed archive logs to avoid single point of failure on the archive logs. • More recovery options (tablespace-point-in-time recovery) 9)Initialization files Name in the Heartitialization parameter file is a text file in ASCII which contains information needed to initialize a database and instance. 10)Control files Control Files contain: • The physical structure of a database • Database name, • Names and locations of associated databases and online redo log files, Timestamp of database creation, • Current log sequence number • Checkpoint information. 11)SGA definition files System Global Area The System Global Area (SGA) is a shared memory region that contains data and control information for one Oracle instance. When an instance starts, the SGA is allocated by Oracle and is deallocated when the instance shuts down. Every instance has an own SGA. Online Users share the data in the SGA. The information stored in the SGA is divided into memory structures like: • The database buffers • Redo log buffer • The shared pool. 12) Oracle tablespaces What is tablespaces? Explain the purpose of tablespaces. Oracle tablespaces - Dec 03, 2008 at 15:00 PM by Rajmeet Ghai What is tablespaces? Explain the purpose of tablespaces. A table space can be considered as a storage location where the database objects are kept. It forms a part of the physical portion of the database used to allocate storage to these objects. Once a table space is created, it can referred by name when creating the database objects. Purpose: It can be used to control the disk layout of an installation. It can be used to optimize performance. Also read 13) What is a Tablespace? Tablespaces is a logical storage unit. It is used to group related logical structures together........ Explain how to assign a Tablespace to a user in Oracle. What are the Predefined Tablespaces in a database? SYSTEM Tablespace, SYSAUX Tablespace, UNDO Tablespace, TEMP Tablespace......... Explain how to prevent tablespace fragmentation in oracle. Tablespace fragmentation can be prevented by using PCTINCREASE command. PCTINCREASE is the percentage a new subsequent extent will grow... 14) Describe in brief about oracle database tuning. Oracle includes many performance tuning enhancements like: • Automatic Performance Diagnostic and Tuning Features • Automatic Shared Memory Management - Automatic Shared Memory Management puts Oracle in control of allocating memory within the SGA • Wait Model Improvements - A number of views have been updated and added to improve the wait model. • Automatic Optimizer Statistics Collection - gathers optimizer statistics using a scheduled job called GATHER_STATS_JOB • Dynamic Sampling - enables the server to improve performance • CPU Costing - default cost model for the optimizer (CPU+I/O), with the cost unit as time • Optimizer Hints • Rule Based Optimizer Obsolescence - No more used • Tracing Enhancements - End to End Application Tracing which allows a client process to be identified via the client identifier rather than the typical session id • SAMPLE Clause Enhancements • Hash Partitioned Global Indexes 15) Oracle - steps to creating a password authentication file - Feb 18, 2010 at 11:00 PM by Rajmeet Ghai What are the steps to creating a password authentication file? A Password file in Oracle can be created using the ORAPWD command. The command below creates a file pass.pwd for 30 users and temporary password as “tempo” ORAPWD FILE=pass.pwd PASSWORD= tempo ENTRIES=30 Oracle - steps to creating a password authentication file - August 28, 2008 at 17:10 pm by Amit Satpute 16) What are the steps to creating a password authentication file? The steps are : • First set REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE in init.ora file • Then dbs$orapwd file=orapw$ORACLE_SID password=sys force=y • Then startup force; • Then grant sysdba to user(any user in the database); • Then conn user/user • Then conn user/user as sysdba • Then show user 17) What is archive log mode? The archiving of the redo log is enabled by running database in ARCHIVELOG mode. A group of filled redo log files cannot be reused by LGWR until the group is archived and become available for archiving immediately after a redo log switch occurs. Advantages of archiving the filled groups: • A database backup, together with online and archived redo log files, guarantees recovery of all committed transactions in the event of an operating system or disk failure. • In an archived log, the backup is taken while the database is open and in normal system use. • The standby database can be kept current with its original database by continuously applying the original archived redo logs to the standby. Explain cold and hot backups. A backup taken when the database is not running is called a cold backup. This is also known as an offline backup. Copying physical files when the database is up is called hot back up. It is done by putting the tablespace in backup mode and then copying all the data file using the copy commands and then bringing it back to normal state. What are incremental backups? Incremental backup stores all files that have changed since the last FULL, DIFFERENTIAL OR INCREMENTAL backup. Its advantage is that it takes very less time to complete. But the restore operation is lengthy as each incremental backup must be processed.
Posted on: Wed, 25 Jun 2014 04:51:54 +0000
Trending Topics
Recently Viewed Topics
© 2015