It is used to authenticate any user is connecting as SYSDBA from a remote machine.
Both. If the data which to be sorted is more than sort area size of PGA, then temp tablespace will be used.
It is in a single location defined by DIAG_DEST parameter.
Alert log itself cannot provide complete information about the error, in which case it will generate trace file. Depends on the error, it will generate background, core or user trace files.
SGA_TARGET and value is dependent on no of tractions in the database.
DBWR – 20, SMON – 16 .
We need to check if SERVER=SHARED is mentioned in client TNS entry or not.
SERVER_RESULT_CACHE.
If we fetch data from database buffer cache, then its called logical read. If we fetch it from database, its called physical read as it includes an I/O operation.
Base tables are dictionary information of the database. They will be created at the time of database creation using SQL.BSQ script.
Control file is required and it is for maintenance of some database actions.
A data block is the smallest unit of logical storage for a database object. As objects grow they take chunks of additional storage that are composed of contiguous data blocks. These groupings of contiguous data blocks are called extents. All the extents that an object takes when grouped together are considered the segment of the database object.
REMOTE_LOGIN_PASSWORD is not set to exclusive.
It is a new component introduced in 11g. It will store the rows of a query directly this avoids the need of executing a statement.
It is file which helps in diagnosing all the errors occurred in the database.
Spfile is a binary file and it helps in changing parameters efficiently than a pfile.
SCN is a unique number assigned to a traction which modifies data in the database. CKPT process will always update this SCN to both datafiles header and control file.
Semantic checking me checking for the privileges for the user or in other words authorizing the user. Base tables or dictionary will help in doing this.
Pfile or spfile is required to bring database to NOMOUNT state and instance will be started (backgroung processes will be started and memory will be allocated to SGA from RAM) in this phase.
Hidden parameters are to be used only on recommendation of oracle support and sometimes they help us in providing work around to any serious problem in the database.
We can recover from alert log file which contains non-default parameters.
We can open another terminal and issue shut abort. Then once again startup and do shut immediate.
PGA is a memory area which is used to store user’s exclusive information like session information and helps in executing a query.
SMON (here system me database).
With bind variable in SQL, oracle can cache queries in a single time in the SQL cache area. This avoids a hard parse each time, which saves on various locking and latching resource we use to check object existence and so on.
Oracle SID is the unique name that uniquely identifies your instance/database where as the service name is the TNS alias can be same or different as SID.
A checkpoint is a database event, which synchronize the database blocks in memory with the datafiles on disk. It has two main purposes: To establish a data consistency and enable faster database Recovery.
The following are the parameter that will be used by DBA to adjust time or interval of how frequently its checkpoint should occur in database.
LOG_CHECKPOINT_TIMEOUT = 3600; # Every one hour
LOG_CHECKPOINT_INTERVAL = 1000; # number of OS blocks.
Ifile is a index file which can help database when pfile or spfile are not in default location.
While mounting the database oracle reads the data from control file which is used for verifying physical database files during sanity check. Background processes are started before mounting the database only.
Setting SGA_TARGET parameter is called ASMM. This helps in managing all SGA components automatically and MMAN is the background process helps in that.
Spfile.
When you are running dedicated server then process information stored inside the process global area (PGA) and when you are using shared server then the process information stored inside user global area (UGA).
The point at which oracle ends writing to one online redo log file and begins writing to another is called a log switch. Sometimes you can force the log switch.
ALTER SYSTEM SWITCH LOGFILE;
Might be there is a traction which is large and rollback is happening for the same.
By increasing the size specified by SERVER_RESULT_CACHE or by increasing MEMORY_TARGET parameter if AMM is used.
The system global area is a group of shared memory area that is dedicated to oracle instance. All oracle process uses the SGA to hold information. The SGA is used to store incoming data and internal control information that is needed by the database.
You can control the SGA memory by setting the parameter db_cache_size, shared_pool_size and log_buffer.
Shared pool portion contain three major area: Library cache (parse SQL statement, cursor information and execution plan), data dictionary cache (contain cache, user account information, privilege user information, segments and extent information, data buffer cache for parallel execution message and control structure.
Values of individual parameters will act as minimum and ASMM as maximum.
It is a rule which says database related files should split across multiple disks and yes i will recommend to implement it.
SGA size is not dependant on database size, it depends on how many tractions happening in the database.
It will do instance recovery, coalesces the tablespace and will release the temp segments occupied by a traction when it is completed.
Block is a basic storage unit where as extent is a small memory area allocated to a table and segment is a object which occupies space.
CURRENT state me that redo records are currently being written to that group. It will be until a log switch occurs. At a time there can be only one redo group current.
If a redo group containing redos of a dirty buffer that redo group is said to be ACTIVE state. As we know log file keep changes made to the data blocks then data blocks are modified in buffer cache (dirty blocks). These dirty blocks must be written to the disk (RAM to permanent media).
And when a redo log group contains no redo records belonging to a dirty buffer it is in an "INACTIVE" state. These inactive redo log can be overwritten.
One more state UNUSED initially when you create new redo log group its log file is empty on that time it is unused. Later it can be any of the above mentioned state.
In 8i, we need to set different individual parameters where as in 9i setting only one parameter PGA_AGGREGATE_TARGET would be enough.
Yes, but load will be more on system tablespace.
It contains latest SCN, all locations and sizes of datafiles and redolog files, database creation date and timestamp and controlfile parameters.
All the background processes are responsible.
If server process starts searching at MRU end, there is a chance that data may get flush from buffer cache by the ime it reaches LRU end. In that case again to fetch data, we need to do an I/O which is costly. So oracle designed its architecture that server process will search only from LRU end.