Core DBA interview questions
I want to export a table of 5TB with LOB using expdp , what I have to do to improve the performance
Use the below parameters for expdp
parallel
compress
file_size
estimate
estimate_only
cluster=N
Buffer
chunk
LOB_OPTIONS
_optimizer_gather_stats=false
expdp system/password@SID DIRECTORY=data_pump_dir DUMPFILE=lob_export.dmp LOGFILE=lob_export.log TABLES=your_table_name COMPRESSION=ALL BUFFER=1000000 LOB_OPTIONS(CHUNK=8192 ENABLE STORAGE IN ROW)
first estimate the size using the below syntax
expdp userid schemas=schema_name directory=directory_name dumpfile=export_file_name.dmp filesize=5G compression=all parallel=8 direct=y estimate_only=yes
next run the expdp using the below command
expdp userid schemas=schema_name directory=directory_name dumpfile=export_file_name.dmp filesize=5G compression=all parallel=8 direct=y
At DB SIDE:
==========
go to automatic memory management using memory_target, but make sure to define memory_max_target
This will automatically resize sga_target,pga_aggregate_target,db_cache_size
Also monitor the undo usage using the below query
SELECT tablespace_name, sum(bytes)/1024/1024 as size_mb, sum(maxbytes)/1024/1024 as max_size_mb, sum(bytes)/sum(maxbytes)*100 as pct_used
FROM dba_data_files
WHERE tablespace_name = 'UNDOTBS'
GROUP BY tablespace_name;
resize the undo tablespace and also adjust the undo retention
what is undo retention gurantee and undo retention?
undo retention will tell us the max duration of undo data in the undo tablespace
undo retention gurante will tell us the max guranteed duration in the undo tablespace
how to define?
ALTER SYSTEM SET UNDO_RETENTION = 3600;
ALTER SYSTEM SET UNDO_RETENTION_GUARANTEE = TRUE;
ALTER SYSTEM SET MINIMUM_UNDO_RETENTION = 1800;
Why undo space is not released , how to fix it?
undo space is not released due to below reasons
insufficient undo retention
insufficient undo tablespace size
long running transactions
undo retention gurantee
what is the use of restore point?
restore points are useful for a point in time recovery for specific undo retention period cannot be an alternative for regular backup recovery.
what is the difference between restore point and gurantee restore point?
Restore point will do the point in time recovery until the data got expired in the undo and based on the retention period.
Guaranteed restore point will perform the guarantee point in time recovery inspite of expiry of undo data as it is maintained by flashback logs.
Make sure we should reduce the undo retention as reducing the age of undo retention less than the time of GRP then oracle cannot guarantee the recovery
because flashback logs will maintain a record of undo entry and keep track of the undo changes
***********************************************
what happens if I suddenly kill the SMON?
SMON is an important BG process of oracllle database as it does the below actions
automatic instance recovery
automatic clean up of temporary segments
coalescing free space in database means (datafile and by updating the bitmaps helping oracle to track)
In this case database may crash or hung . in ideal cases kill all the process and try to do a clean restart and SMON can do automatic instance recovery
if not we have perform block recovery steps incase of block corruptions.
***************************************************************
**************************************************************************************
Suddenly I have seen a physical block corruption error in my alert ? what I have to do ?
-> use dbverify to verify the block
dbv file=/u01/app/oracle/oradata/mydb/system01.dbf blocksize=8192
-> take a backup of corrupted datafile
rman> backup datafile 10;
-> bring the tablespace offline
SQL> ALTER TABLESPACE USERS OFFLINE;
-> do RMAN block recover
rman target /
RMAN> blockrecover datafile 1 block 12345;
-> validate the repair
rman target /
RMAN> validate datafile 1 block 12345;
-> bring the tablespace online
SQL> ALTER TABLESPACE USERS ONLINE;
****************************************************************************************
Suppose I got a error message in the alert log with a logical block corruption. How do you identify the table?
let us assume the below error message
ORA-01578: ORACLE data block corrupted (file # 5, block # 34)
ORA-01110: data file 5: '/u01/app/oracle/oradata/db1/datafile01.dbf'
In this case datafile 5 and block 35 is corrupted , Now lets query the dba_extents and dba_segments table.
SELECT owner, segment_name, segment_type
FROM dba_extents
WHERE file_id = 5
AND 34 BETWEEN block_id AND block_id + blocks - 1;
SELECT owner, segment_name, segment_type
FROM dba_segments
WHERE header_file = 5
AND header_block = 34;
SELECT * FROM my_table WHERE ROWID IN ( SELECT ROWID FROM my_table WHERE ROWNUM <= 100);
******************************************************************************************************
How to fix the logical block corruption issue?
Take a backup of the corrupted object:
CREATE TABLE dummy_table AS SELECT * FROM corrupted_table WHERE 1=2;// create dummy table
INSERT INTO dummy_table SELECT * FROM corrupted_table WHERE <condition>;// move the data
DROP TABLE corrupted_table; //drop corrupted table
ALTER TABLE dummy_table RENAME TO corrupted_table; //rename the dummy table
ALTER INDEX index_name REBUILD; //rebuild indexes
ANALYZE TABLE corrupted_table VALIDATE STRUCTURE; //validate the table
***********************************************************************************
********************************
oracle kernel parameter info?
ipcs used to check how much shared memory segents allocated and how many semaphores allocated .
shmmax -- max size of each segments bytes.
shmall-- sum of all shared segments in pages
shmmni -- number of segements
number of segments in each set
****************************************************
A user said he is unable to login to the database due to max process usage issue . How to identify and fix the issue?
look for the long running application processs using ps-ef|grep LOCAL
local=no ===Proccess which is coming from applications
++++++++++++++++++++++++++++++++++++++++++++++++
what is shadow process
the separate server proccess created on behalf of user process called dedicated server process (shadow process).
eg: The shadow process is responsible for managing the write I/O operation, ensuring that the data blocks are written correctly to the physical data files
++++++++++++++++++++++++++++++++++++++++++++++++
catalog.sql==>to create datadictinary views , catproc.sql===>to create procedures and functions
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
what is dedicated /shared server
A dedicated server process, which services only one user process
A shared server process, which can service multiple user processes.. dispatcher process will be there.
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
what are mandatory process?
PMON,SMON,DBWR,LGWR,CKPT,MMON,RECO
PMON
1. Monitors the other background processes and performs process recovery when a server or dispatcher process terminates abnormally
2. Responsible for cleaning up the database buffer cache and freeing resources that the client process was using
3. Resets the status of the active transaction table
4. Releases locks that are no longer required
5. Removes the process ID from the list of active processes
6. Registers information about the instance and dispatcher processes with the Oracle Net listener
7. When an instance starts, PMON polls the listener to determine whether it is running. If the listener is running, then PMON passes it relevant parameters. If it is not running, then PMON periodically attempts to contact it.
SMON:
1. Responsible for system-level cleanup
2. Performs instance recovery, if necessary, at instance startup.
3. If its RAC Database, the SMON process of one database instance can perform instance recovery for a failed instance.
4. Cleaning up unused temporary segments.
5. Coalescing contiguous free extents within dictionary-managed tablespaces.
DBWR:
1. DBWn writes the contents of database buffers to data files. DBWn processes write modified buffers in the database buffer cache to disk
2. Although one database writer process (DBW0) is adequerfect ate for most systems, you can configure additional processes�DBW1 through DBW9 and DBWa through DBWj�to improve write performance if your system modifies data heavily however these additional DBWn processes are not useful on uniprocessor systems.
The DBWn process writes dirty buffers to disk under the following conditions:
1. When a server process cannot find a clean reusable buffer after scanning a threshold number of buffers, it signals DBWn to write.
DBWn writes dirty buffers to disk asynchronously if possible while performing other processing.
2. DBWn periodically writes buffers to advance the checkpoint, which is the position in the redo thread from which instance recovery begins.
The log position of the checkpoint is determined by the oldest dirty buffer in the buffer cache.
Note: In many cases the blocks that DBWn writes are scattered throughout the disk.
Thus, the writes tend to be slower than the sequential writes performed by LGWR.
DBWn performs multiblock writes when possible to improve efficiency. The number of blocks written in a multiblock write varies by operating system.
Log Writer Process (LGWR):
The log writer process (LGWR) manages the redo log buffer. LGWR writes one contiguous portion of the buffer to the online redo log.
In the following circumstances, LGWR writes all redo entries that have been copied into the buffer since the last time it wrote:
1. A user commits a transaction
2. An online redo log switch occurs
3. Three seconds have passed since LGWR last wrote
4. The redo log buffer is one-third full or contains 1 MB of buffered data.
5. DBWn must write modified buffers to disk
Note: Before DBWn can write a dirty buffer, redo records associated with changes to the buffer must be written to disk (the write-ahead protocol).
If DBWn finds that some redo records have not been written,
it signals LGWR to write the records to disk and waits for LGWR to complete before writing the data buffers to disk.
Relation between LGWR and Commits:
Oracle Database uses a fast commit mechanism to improve performance for committed transactions.
When a user issues a COMMIT statement, the transaction is assigned a system change number (SCN).
LGWR puts a commit record in the redo log buffer and writes it to disk immediately, along with the commit SCN and transactions redo entries.
Note: LGWR can write redo log entries to disk before a transaction commits. The redo entries become permanent only if the transaction later commits.
When activity is high, LGWR can use group commits. For example, a user commits, causing LGWR to write the transactions redo entries to disk.
During this write other users commit. LGWR cannot write to disk to commit these transactions until its previous write completes.
Upon completion, LGWR can write the list of redo entries of waiting transactions (not yet committed) in one operation. In this way,
the database minimizes disk I/O and maximizes performance. If commits requests continue at a high rate, then every write by LGWR can contain multiple commit records.
Checkpoint Process (CKPT):
1. Checkpoint process (CKPT) updates the control file and data file headers with checkpoint information and signals DBWn to write blocks to disk.
2. Checkpoint information includes the checkpoint position, SCN, location in online redo log to begin recovery
Manageability Monitor Processes (MMON and MMNL):
The manageability monitor process (MMON) performs many tasks related to the Automatic Workload Repository (AWR).
For example, MMON writes when a metric violates its threshold value, taking snapshots, and capturing statistics value for recently modified SQL objects.
The manageability monitor lite process (MMNL) writes statistics from the Active Session History (ASH) buffer in the SGA to disk.
MMNL writes to disk when the ASH buffer is full.
Recoverer Process (RECO):
1. RECO is a background process for distributed transactions. The RECO process manager two-phase commits to track and resolve in-doubt transactions.
2. In a distributed database, the recoverer process (RECO) automatically resolves failures in distributed transactions.
*************************************
What is difference in alert and trace files.
Alert logs are used to record critical database errors and events . Trace files will give a detailed error report for a specific database operations
*************************************
what is change tracking file
Change tracking is a featire of RMAn helps in improving backup prformance by mainatianing a file called chnage tracking file as it records the information
about chnaged records since the last backup.
we can fine the file location under
SELECT * FROM V$BLOCK_CHANGE_TRACKING;
how to define change block tracking?
ALTER DATABASE DATAFILE '/path/to/datafile.dbf' ENABLE CHANGE TRACKING;
This featuree is very much useful while backup up large tables or database
****************************************
How connection is created (in shared mode / in dedicated mode)
***************************************************
what are flashback log files
these log files are used for guranteed restore point and infact used to by pass the dependecy on UNDO space usage
what is B_FLASHBACK_RETENTION_TARGET?
This parameter is useful to define the retention period for flash back query and flash back database
example of flash back query
SELECT SALARY
FROM EMPLOYEE
AS OF TIMESTAMP TO_TIMESTAMP('2023-01-01 14:30:00', 'YYYY-MM-DD HH24:MI:SS')
WHERE EMP_ID = 101;
**********************************************************
what is the naming convension of pfile and spfile.
the name convertion is init<spfile/pfile>.ora
naming convention of alert and trace files.
alert_<ORACLE_SID>.log
<PROCESS_NAME>_<PROCESS_ID>.trc
ORAPW<ORACLE_SID>
*****************************************************
what is static param,dynamic param, how to check
How to see parameter value.
Static Parameters: These parameters can only be modified by restarting the database instance.
Dynamic Parameters: These parameters can be modified without restarting the database instance.
SELECT NAME, VALUE, ISSES_MODIFIABLE, ISINSTANCE_MODIFIABLE FROM V$PARAMETER WHERE NAME='<PARAMETER_NAME>';
If the "ISSES_MODIFIABLE" and "ISINSTANCE_MODIFIABLE" columns both have a value of "FALSE", then the parameter is a static parameter
"ISSES_MODIFIABLE" is a column in the V$PARAMETER view in Oracle database that indicates whether a parameter can be modified at the session level.
ISINSTANCE_MODIFIABLE" is another column in the V$PARAMETER view in Oracle database that indicates whether a parameter can be modified at the instance level.
***********************************************************
How can we give identifiable names to trace files, why should we do it.
In Oracle, we can give identifiable names to trace files by setting the initialization parameter "USER_DUMP_DEST" to a directory path that includes a naming template.
eg:
USER_DUMP_DEST=/u01/app/oracle/diag/rdbms/mydb/MYDB/trace/mydb_ora_%p_%t.trc
*******************************************************************************
What are segment,extent and blocks....why do you think we should use extent ??
When we create a segment in Oracle, we can specify the initial size of the segment and the size of each extent. By default, Oracle will allocate extents as needed, but we can also preallocate extents for a segment using the "ALTER TABLE ... STORAGE" or "ALTER INDEX ... STORAGE" commands.
Using extents can have several benefits, including:
Reducing fragmentation: Preallocating extents can help reduce fragmentation and improve performance by ensuring that the segments are stored in contiguous blocks of disk space.
Reducing I/O: Allocating larger extents can help reduce I/O operations by reducing the number of times that Oracle needs to access the disk to read or write data.
Improving performance: Using larger extents can also help improve performance by reducing the overhead of managing the extent allocation and deallocation process.
It's worth noting that the appropriate size for extents can vary depending on the specific requirements of a given database or application. It's important to carefully monitor and tune the extent sizes to optimize performance and storage utilization.
********************************************************************************************
what is default block sie in Oracle
The default block size in Oracle is platform-dependent, but it is typically 8KB on most modern systems. However, Oracle also supports other block sizes, such as 2KB, 4KB, 16KB, and 32KB, depending on the platform and version of Oracle being used.
**********************************************************************************
can we have differnt block size tablespace? How?
Yes, it is possible to have different block sizes for tablespaces in Oracle. To create a tablespace with a different block size, we need to specify the "BLOCKSIZE" attribute when creating the tablespace.
eg:
CREATE TABLESPACE my_tablespace
DATAFILE '/u01/app/oracle/oradata/mydb/my_tablespace.dbf' SIZE 100M
BLOCKSIZE 16K;
***********************************************************************************
What are the advantage of Locally managed TS over dictionay managed TS
Locally Managed Tablespaces (LMTs) and Dictionary Managed Tablespaces (DMTs) are two different methods for managing the storage of data in Oracle. There are several advantages of using LMTs over DMTs:
Improved Performance: LMTs have better performance than DMTs because they use bitmaps to track the space usage within the tablespace. This reduces contention and overhead when allocating and deallocating space.
Automatic Space Management: LMTs use Automatic Segment Space Management (ASSM), which means that the database automatically manages free space within the tablespace. This eliminates the need to manually manage free space, which can be a time-consuming and error-prone process.
Easier Maintenance: Because LMTs use ASSM, they require less maintenance than DMTs. For example, you don't need to manually coalesce free space in LMTs, which is necessary in DMTs.
Better Scalability: LMTs are more scalable than DMTs because they can handle larger databases with a greater number of objects.
Reduced Risk of Corruption: LMTs reduce the risk of corruption because they use checksums to detect block corruption. This helps to ensure that data is stored and retrieved correctly.
In summary, Locally Managed Tablespaces offer several advantages over Dictionary Managed Tablespaces, including improved performance, automatic space management, easier maintenance, better scalability, and reduced risk of corruption.
********************************************
will redo generated for Temp? will undo generated?
edo logs are generated for temporary tablespaces in Oracle, but undo data is not generated for temporary tablespaces.
When a transaction modifies data in a temporary tablespace, the redo information for that modification is written to the redo logs, just like for any other transaction. This ensures that the changes made to the temporary tablespace can be recovered in case of a database failure.
However, undo data is not generated for temporary tablespaces because temporary tablespaces are not used to store permanent data. Undo data is used to roll back changes made to permanent data, and because temporary tablespaces do not store permanent data, there is no need for undo data in those tablespaces.
Instead of undo data, temporary tablespaces use Temporary Undo, which is a special type of undo that is used to support features like read consistency and flashback queries for temporary objects. Temporary Undo is stored in the undo tablespace, not in the temporary tablespace.
******************************************************************
what information is present in controlfile?
Database Name and Creation Date:
Tablespaces and Datafiles:
Redo Log Files:
Current SCN:
Backup and Recovery Information:
Database Configuration Information: The control file contains information about the database configuration, such as the memory allocation for the database instance, the number of background processes, and the initialization parameters.
Database Open and Close Time:
Datafile and Tablespace Status: The control file contains information about the status of datafiles and tablespaces, such as whether they are offline, read-only, or in backup mode.
************************************************************************
use of online redo logfile?
They record all changes made to the database so that the database can be recovered in the event of a failure or a system crash.
*************************************************
Can we stop redo generation
in genral it is not recomended to stop redo generation
*****************************************************************
what wil happen during logswitch
In Oracle, a log switch occurs when the current redo log file is filled and the database switches to writing to a new redo log file.
Checkpoint: The checkpoint process writes all dirty buffers in the database buffer cache to disk and updates the datafile headers and control file to record the most recent checkpoint.
Log buffer: The contents of the current redo log buffer in the system global area (SGA) are written to the current redo log file.
Archiving: If the database is running in ARCHIVELOG mode, the filled redo log file is archived to a different location
Activation: The new redo log file is activated, and the database begins writing redo records to this file.
Resizing: If the new redo log file has a different size than the previous one, the log writer process (LGWR) may allocate additional space in the redo log file or release unused space in the old redo log file.
*******************************************************************
what are different redolog status?
ACTIVE: The redo log file is currently being written to by the Log Writer (LGWR) process.
INACTIVE: it contains redo records that have not yet been archived.
UNUSED: The redo log file has been created but has not been used yet.
STALE: The redo log file is no longer needed for recovery and can be safely deleted.
CLEARING: The redo log file is being cleared of all its contents by the Log Writer (LGWR) process. This status occurs when a redo log file needs to be reused, but it still contains unarchived redo records.
**********************************************************
what will happen during commit?
A commit is a transactional operation that indicates that all changes made within a transaction should be permanently saved to the database. When a commit is issued, the following actions are performed:
Validation: The database checks that all the changes made within the transaction are valid and can be committed without violating any constraints, such as referential integrity constraints.
Redo log writes: The Log Writer (LGWR) process writes all changes made within the transaction to the redo log buffer in the SGA.
Datafile writes: The database writes all changes made within the transaction to the database datafiles on disk. This is done by the database writer process (DBWn) or the asynchronous input/output (I/O) server process (ASMB).
Undo data: The database writes undo data to the undo tablespace, which is used to roll back the transaction if necessary.
Release resources: The database releases all resources used by the transaction, such as locks and temporary storage.
*********************************************************
checkpoint
During a checkpoint, the checkpoint process signals the Database Writer (DBWn) process to write dirty buffers to disk. The DBWn process writes the dirty buffers to disk asynchronously, meaning it may write some dirty buffers to disk before the checkpoint is complete. However, the checkpoint ensures that all dirty buffers up to the checkpoint's System Change Number (SCN) are written to disk before the checkpoint is considered complete.
The checkpoint process helps to ensure data consistency and recovery in the event of a system failure. By synchronizing the database buffer cache with the datafiles on disk, it ensures that all changes made to the database are durable and can be recovered in case of a system failure.
***************************************************************************************
what is use of archive redo logs
It will,be useful for recovery purpose
********************************************************
what is SGA/PGA/UGA
he SGA is a shared memory area that stores data and control information for the database instance, while the PGA is a memory area that stores information for a single server process, and the UGA is a memory area that stores session-specific data for each connected user.
What components are tuned by SGA_TARGET
SGA_TARGET is used to dynamically manage the sizes of various components within the SGA based on the current workload of the database instance, including the buffer cache, shared pool, large pool, Java pool, and redo log buffer.
what components are tuned by PGA_AGGREGATE_TARGET
PGA_AGGREGATE_TARGET is used to dynamically manage the sizes of various components within the PGA based on the current workload of the database instance, including the sort area, hash area, bitmap merge area, and session memory.
what is PGA_AGGREGATE_LIMIT----cause of node eviction.
PGA_AGGREGATE_LIMIT is an Oracle parameter that sets a hard limit on the amount of memory that can be allocated for PGA memory across all server processes in an Oracle database instance. This parameter is used to prevent server processes from consuming too much memory, which can lead to server instability, poor performance, or even node eviction in a Real Application Clusters (RAC) environment.
How to check current PGA used by all process
SELECT s.sid, s.serial#, p.spid, s.program, s.machine, s.username, s.status,
ROUND(p.pga_used_mem/1024/1024,2) AS pga_used_mb
FROM v$session s, v$process p
WHERE s.paddr = p.addr;
How to check SGA size
SELECT * FROM v$sgainfo;
How to set memory_target
ALTER SYSTEM SET MEMORY_TARGET=4G SCOPE=SPFILE;
if memory_target is set, what will happen to SGA_TARGET and PGA_AGGREGATE_TARGET values?
If the MEMORY_TARGET parameter is set in an Oracle database instance, the values of the SGA_TARGET and PGA_AGGREGATE_TARGET parameters are determined automatically by the database.
can we use hugepages when setting memory_target
HugePages is a feature of the Linux operating system that allows for the allocation of large memory pages to reduce the overhead of managing many small memory pages. When using HugePages with Oracle, the SGA can be allocated in large contiguous memory pages instead of small pages, which can improve performance and reduce memory fragmentation.'
ALTER SYSTEM SET MEMORY_TARGET=4G SCOPE=SPFILE;
ALTER SYSTEM SET USE_LARGE_PAGES=ONLY SCOPE=SPFILE;
when logwriter rights/flush ?
for every log switch,commit,checkpoint , user request ALTER SYSTEM FLUSH LOGS
when dbwriter writes?
checkpoint, dirty buffer threshold,tablespace offline, tablespace read only,user request aletr system checkpoint
What is LRU algo for buffer cache. What is touch count? If we run select *, Which end of the LRU list blocks are inserted?
what are components of shared pool.
LRU stands for Least Recently Used, which is an algorithm used to manage the buffer cache in an Oracle database. The LRU algorithm keeps track of the usage of database blocks in the buffer cache and evicts the least recently used blocks from memory when the buffer cache is full and new blocks need to be loaded into memory.
The touch count is a measure of the frequency of access to a block in the buffer cache. When a block is accessed, its touch count is incremented. The touch count is used by the LRU algorithm to determine which blocks should be evicted from memory. The blocks with the lowest touch count, or those that have not been accessed recently, are evicted first.
How to avoid shared pool contention
Shared pool contention can occur in an Oracle database when multiple sessions are competing for the same shared memory area in the SGA. This can lead to contention and performance issues. Here are some strategies for avoiding shared pool contention:
Increase the size of the shared pool: One way to avoid shared pool contention is to increase the size of the shared pool. This will provide more memory for sessions to use, reducing the likelihood of contention. You can increase the shared pool size by setting the SHARED_POOL_SIZE parameter in the initialization file or with the ALTER SYSTEM command.
Avoid large numbers of shared SQL statements: Shared SQL statements can take up a significant amount of space in the shared pool, leading to contention. To avoid this, consider using bind variables, stored procedures, or caching frequently executed statements.
Limit the use of PL/SQL: PL/SQL objects can also take up a lot of space in the shared pool. To avoid contention, limit the use of PL/SQL and make sure that your PL/SQL code is well-designed and efficient.
Monitor and tune the shared pool: Regularly monitor the shared pool using performance tuning tools like Oracle Enterprise Manager or SQL Trace. Look for signs of contention, such as high parse times or shared pool latch contention, and take appropriate action to tune the shared pool.
Consider using separate pools for specific applications: If certain applications or users are causing shared pool contention, consider using separate pools for these applications. This can be done using the DBMS_SHARED_POOL package.
Overall, avoiding shared pool contention requires a combination of monitoring, tuning, and good design practices. By implementing these strategies, you can improve the performance and reliability of your Oracle database.
what is large pool why it is used
The large pool is a part of the System Global Area (SGA) in an Oracle database that is used to allocate large memory chunks for Oracle processes other than the database buffer cache. It is used to allocate memory for operations that require large memory chunks, such as backup and restore operations, Parallel Execution, and Shared Server processes.
The large pool is distinct from the buffer cache and the shared pool, which are other parts of the SGA. It is used to provide memory for operations that cannot be satisfied by the buffer cache or the shared pool.
Some examples of operations that use the large pool include:
Parallel Execution: When a query is executed using Parallel Execution, Oracle allocates a portion of the large pool to hold the buffers used by the parallel slave processes.
Backup and Restore: When a backup or restore operation is performed, Oracle allocates memory from the large pool to hold the data being backed up or restored.
Shared Server processes: When using the Shared Server architecture, Oracle allocates a portion of the large pool to hold the Shared Server process information.
The large pool can be sized using the LARGE_POOL_SIZE initialization parameter. By default, the large pool is not used, but it can be enabled by setting the LARGE_POOL_SIZE parameter to a non-zero value.
what is connection pooling, what are advantages and disadvantages.
Connection pooling is a technique used in software applications to minimize the number of times a new connection is established to a database. A connection pool is a cache of database connections maintained by the application server or middleware. Instead of opening a new connection every time a request is made to the database, the application server uses a connection from the pool that is already open.
will Oracle escalate locks (from row level to table level)
yes,
Lock escalation occurs when a transaction holds too many locks on individual rows, and these locks begin to cause performance issues or contention. When this happens, Oracle may escalate the locks to a higher level, such as the table level, to reduce the number of locks and improve performance.
eg scenario:
SELECT * FROM orders WHERE order_date >= '01-JAN-2022' FOR UPDATE;
SELECT name, value FROM v$parameter WHERE name = 'locks';
ALTER SYSTEM SET LOCKS = 2000;
In this example, the value of the LOCKS parameter is set to 2000, which means that a transaction will be eligible for lock escalation if it holds more than 2000 locks. You should set the value of the LOCKS parameter based on your application's needs and the amount of memory available for locks on your system.
what is the difference in update/select for update and /update nowait
In Oracle, the FOR UPDATE clause is used to lock the selected rows in a query so that other transactions cannot modify them until the lock is released. There are two different ways to use the FOR UPDATE clause: with NOWAIT and without NOWAIT.
When you use FOR UPDATE NOWAIT, the selected rows are locked immediately and exclusively, and if any other transaction has already locked the rows, your transaction will fail with an error instead of waiting for the other transaction to release the locks. This can be useful in situations where you want to avoid waiting for locks and want to handle the error in your application code.
On the other hand, when you use FOR UPDATE without NOWAIT, the selected rows are locked in shared mode initially, which means that other transactions can still read the rows but cannot modify them. When your transaction tries to modify a row, it acquires an exclusive lock on the row, and if any other transaction has already acquired a shared or exclusive lock on the row, your transaction will wait until the other transaction releases the lock.
In summary, the main difference between FOR UPDATE NOWAIT and FOR UPDATE without NOWAIT is that the former fails immediately if the rows are already locked, while the latter waits for the locks to be released before acquiring exclusive locks.
what is deadlock, how Oracle resolves it
Let's say there are two transactions, T1 and T2, that need to update the same two rows, A and B, in a table. The transactions are running concurrently and have acquired locks on the respective rows they need to update:
T1 has locked row A and is waiting to lock row B.
T2 has locked row B and is waiting to lock row A.
At this point, both transactions are blocked, waiting for each other to release the lock on the row they need to proceed. This is a deadlock situation.
Oracle's DDR process detects the deadlock and selects one of the transactions as the victim. In this case, let's say T2 is chosen as the victim. Oracle rolls back the changes made by T2 and releases the lock on row B. This allows T1 to proceed with its update of row B.
Once T1 completes
what is foreign key, when we need to index the foreign key?
a foreign key is a column or a set of columns in a table that refers to the primary key of another table
When a foreign key is indexed, it creates an index on the column or columns that are used as the foreign key
What are different Oracle Locks
row locks,table locks,shared locks,exclusive locks,dml locks
what are different DML locks
Row Exclusive Locks (RX): ,Share Locks (S): Share Row Exclusive Locks (SRX): Exclusive Locks (X):Share Update Locks (SUI):Row Share Locks (RS):
What is differece between lock and latch
Granularity: Locks provide transaction-level protection for shared resources, while latches provide more fine-grained protection at the system level. Locks protect objects such as tables, rows, and indexes, while latches protect data structures such as buffers, caches, and internal memory structures.
what is latch spin
In an Oracle database, a latch spin occurs when a process tries to acquire a latch, but the latch is currently held by another process. Instead of waiting for the latch to be released, the process will spin in a loop, repeatedly trying to acquire the latch until it is available.
What is concurrency control
In Oracle, concurrency control is achieved through locking mechanisms such as latches, locks, and multi-versioning. These mechanisms prevent multiple transactions from accessing and modifying the same data at the same time, ensuring that only one transaction can access and modify the data at any given time.
Oracle provides different types of locks
Multi-versioning is another mechanism used by Oracle to achieve concurrency contro
what is multiversioning
Multi-versioning is another mechanism used by Oracle to achieve concurrency control. It involves creating multiple versions of data and allowing multiple transactions to access and modify their own version of the data. This ensures that transactions do not block each other and can access and modify the data concurrently.
what are transaction isolation levels in Oracle, what anomaly they cover?
Read uncommitted,Read committed,Repeatable read,Serializable
what is the default isolation level in Oracle
The default isolation level in Oracle is READ COMMITTED. This means that a transaction can only see data that has been committed by other transactions.
will read block write or write block reads?
In Oracle, read operations do not block write operations, and write operations do not block read operations by default. This means that concurrent read and write operations can occur simultaneously without being blocked by each other.
What is ACID property of database. How it is maintained in Oracle
In Oracle, these properties are maintained through a combination of techniques, including locking, logging, and transaction management. Oracle uses a two-phase commit protocol to ensure that transactions are atomic and durable. It also uses multi-versioning and read-consistent views to provide isolation and consistency.
what is commit wait/nowait
The COMMIT WAIT clause specifies that the commit should wait until all locks held by the transaction have been released before completing the commit.
The COMMIT NOWAIT clause, on the other hand, specifies that the commit should complete immediately, regardless of whether there are locks held by the transaction. This means that other transactions may be able to access the locked data before the commit is comple
what is snapshot too old, how to resolve?
Increase the size of the rollback segments
Increase the retention time of the undo data:
Optimize long-running transactions:
Use read-consistent queries:
Increase the size of the undo tablespace:
what happen in the background after commit
Validation of the transaction: The transaction is validated to ensure that all changes made by the transaction are consistent with the rules of the database.
Writing of data to the redo log:
Writing of data to the datafiles
release of locks
Notification of clients:
incrementing the SCN
what is nologging
In Oracle, the NOLOGGING option is used to bypass the normal logging mechanisms when performing certain operations such as direct path inserts, direct path loads, and creating indexes. When NOLOGGING is used, the operations are not recorded in the redo log, which can result in significant performance gains. However, this also means that recovery of the database to a specific point in time may not be possible if NOLOGGING operations were performed during that time.
what is log contention
In Oracle, log contention refers to a situation where multiple database sessions or transactions are competing for access to the same log file or log buffer. The log file (also known as the redo log) is a critical component of the database that records all changes made to the database, so log contention can have a significant impact on performance and database integrity.
What are different state of online redo log file (active,inactive,current)
In summary, the active redo log file is the one currently being written to, the inactive redo log files are ones that have been filled up and are waiting to be archived, and the current redo log file is the active redo log file that is being written to and will be used for recovery.
what is delayed bock cleanout
Delayed block cleanout is a process in Oracle where the removal of deleted rows from data blocks is postponed until a later time, instead of being performed immediately during normal DML operations.
Where undo block will be modified in memory? (in db cache as undo is also a datafile)
In Oracle, undo blocks are modified in memory in the undo tablespace. The undo tablespace is a specialized area of the database that stores undo data, which is used to undo changes made by transactions.
What are different Flashback technologies in Oracle
Flashback Query: This technology enables users to query data as it appeared at a previous point in time using SQL. Flashback Query retrieves data from the undo tablespace and is particularly useful for recovering lost data or for auditing purposes.
Flashback Table: This technology allows for the recovery of a dropped or truncated table to a previous point in time. Flashback Table uses the information in the recycle bin and can quickly restore a table with its indexes, triggers, and constraints intact.
Flashback Drop: This technology can recover a table that was dropped using the DROP TABLE statement. Flashback Drop retrieves the table and its dependent objects from the recycle bin.
Flashback Transaction: This technology allows a transaction to be rolled back to a previous point in time. Flashback Transaction undoes the effects of a transaction, including its DML operations, and can be used to recover from user errors or data corruption.
Flashback Database: This technology allows for the recovery of an entire database to a previous point in time. Flashback Database uses the undo data to rewind the database to a specific SCN (system change number) or time, without requiring a full database restore.
Flashback Data Archive: This technology enables long-term data retention and provides access to historical data for compliance, auditing, and analysis purposes. Flashback Data Archive captures and archives changes to specified tables and allows users to query historical data using SQL.
what is INITTRANS,MAXTRANS
INITTRANS and MAXTRANS are parameters that can be set for Oracle database segments, such as tables and indexes, to control the number of initial and maximum concurrent transactions that can access a data block.
what is high watermark
When data is inserted into a table or index, Oracle will allocate new data blocks as needed to store the data. The high watermark is then updated to reflect the new highest allocated block. When data is deleted from the segment, the high watermark remains the same, as Oracle does not immediately release the allocated blocks. Instead, the space is marked as free and can be reused for new data in the future.
Table Reorganization:
Table reorganization is the process of physically reorganizing a table to eliminate fragmentation and free space. This is done by moving rows to new data blocks and releasing any unused blocks back to the database for reuse. Table reorganization can improve performance by reducing the amount of I/O required to access the table and by reducing the amount of free space in the table, which can improve storage efficiency.
ALTER TABLE table_name MOVE [TABLESPACE tablespace_name];
Index Rebuilding:
Index rebuilding is the process of rebuilding the index structure to eliminate fragmentation and free space. This is done by dropping and recreating the index, which can improve performance by reducing the amount of I/O required to access the index and by improving query performance by optimizing the index structure.
ALTER INDEX index_name REBUILD [TABLESPACE tablespace_name];
what is difference in truncate and delete?? which one is faster? if we run truncate and delete on one table, and then run select * which will be faster?
In terms of performance, TRUNCATE is generally faster than DELETE, especially for large tables, because it does not log each deleted row in the transaction log. However, the performance difference between TRUNCATE and DELETE can vary depending on the specific conditions and characteristics of the table and the database environment.
How to remove table fragmentation?
Here are some additional details about each of these methods:
Export and import the table.
Move the table to a different tablespace. ALTER TABLE ... MOVE command.
Reorganize the table. To reorganize a table, you can use the ALTER TABLE ... REORGANIZE command.
Defragment the table. To defragment a table, you can use the ALTER TABLE ... DEFRAGMENT command.
Reorganize: In Oracle, reorganizing a table typically means physically moving rows to new data blocks and releasing unused blocks back to the database for reuse. This can help to eliminate fragmentation and reduce the amount of free space in the table, which can improve storage efficiency and database performance.
Defragment: In Oracle, defragmenting typically means reducing the amount of free space and scattered data in a table or index. This can be achieved by reorganizing the table or index, coalescing free space, or rebuilding indexes. Defragmentation can improve database performance by reducing the amount of I/O required to access data.
While the terms "reorganize" and "defragment" can be used interchangeably in Oracle, the specific methods used to achieve these goals can vary depending on the specific characteristics and needs of your database. For example, you might use the ALTER TABLE ... MOVE statement to reorganize a table, the ALTER TABLE ... SHRINK SPACE statement to coalesce free space, or the ALTER INDEX ... REBUILD statement to rebuild indexes. Ultimately, the goal of both reorganizing and defragmenting is to optimize the storage efficiency and performance of your database.
what are different type of indexes?
B-tree index: This is the most common type of index in Oracle. For example, to create a B-tree index on the "customer_id" column in a table named "customers", you would use the following SQL statement:
CREATE INDEX customers_idx1 ON customers (customer_id);
Bitmap index: This type of index is used for columns with a low cardinality (a small number of distinct values). For example, to create a bitmap index on the "gender" column in a table named "employees", you would use the following SQL statement:
CREATE BITMAP INDEX employees_idx1 ON employees (gender);
Function-based index: This type of index is created based on a function applied to one or more columns in a table. For example, to create a function-based index on the "UPPER" function applied to the "last_name" column in a table named "employees", you would use the following SQL statement:
CREATE INDEX employees_idx1 ON employees (UPPER(last_name));
Reverse key index: This type of index is used for columns with a high rate of inserts, to avoid hot blocks and reduce contention. For example, to create a reverse key index on the "order_date" column in a table named "orders", you would use the following SQL statement:
CREATE INDEX orders_idx1 ON orders (order_date DESC);
Domain index: This type of index is used for columns with a complex data type, such as XML or spatial data. For example, to create a domain index on the "xml_data" column in a table named "documents", you would use the following SQL statement:
CREATE INDEX documents_idx1 ON documents (xml_data) INDEXTYPE IS XDB.XMLINDEX;
Partitioned index: This type of index is partitioned into smaller index segments, each of which corresponds to a partition in the underlying table. For example, to create a partitioned index on the "transaction_date" column in a table named "transactions" that is partitioned by range, you would use the following SQL statement:
CREATE INDEX transactions_idx1 ON transactions (transaction_date) LOCAL
(PARTITION trans_january VALUES LESS THAN (TO_DATE('01-FEB-2023', 'DD-MON-YYYY')),
PARTITION trans_february VALUES LESS THAN (TO_DATE('01-MAR-2023', 'DD-MON-YYYY')),
PARTITION trans_march VALUES LESS THAN (TO_DATE('01-APR-2023', 'DD-MON-YYYY')),
PARTITION trans_april VALUES LESS THAN (TO_DATE('01-MAY-2023', 'DD-MON-YYYY')));
what is virtual index and invisible index
In Oracle, a virtual index and an invisible index are two different concepts:
Virtual Index: A virtual index is a non-persistent index that can be used for query optimization purposes without actually creating an index on a table. It is created using the NO_INDEX hint in a SQL statement. For example, to create a virtual index on a table named employees for the salary column, you would use the following SQL statement:
SELECT /*+ NO_INDEX(employees salary_idx) */ *
FROM employees
WHERE salary > 100000;
This will create a virtual index named salary_idx for the salary column, which can be used to optimize the query execution plan without actually creating the index.
Invisible Index: An invisible index is a persistent index that is not visible to the optimizer by default. It can be created using the INVISIBLE keyword in the CREATE INDEX statement. An invisible index can be used for query optimization purposes, but only if it is explicitly specified in the SQL statement using the INDEX hint. For example, to create an invisible index on a table named employees for the salary column, you would use the following SQL statement:
CREATE INDEX salary_idx ON employees (salary) INVISIBLE;
To use the invisible index in a SQL statement, you would need to specify the INDEX hint, like this:
SELECT /*+ INDEX(employees salary_idx) */ *
FROM employees
WHERE salary > 100000;
This will tell the optimizer to use the salary_idx index for query optimization purposes.
How to check if index is being used (monitoring usage)
SELECT *
FROM DBA_HIST_SQLSTAT
WHERE SQL_ID = 'd6ybsv4p4k4bh'
AND OBJECT_NAME = 'EMPLOYEES_INDEX';
or
SELECT *
FROM V$SQL_PLAN
WHERE SQL_ID = 'd6ybsv4p4k4bh'
AND OBJECT_NAME = 'EMPLOYEES_INDEX
or
sql trace
what is difference is rebuilding the index and rebuild online.
In summary, rebuilding an index offline is a more time-consuming but potentially more thorough process that requires exclusive access to the table, while rebuilding an index online is a faster process that allows concurrent access to the table but has some limitations and potential performance impact
when to use parallel execution
Parallel execution can be used in Oracle to speed up the execution of certain SQL statements by distributing the work across multiple CPUs or nodes in a cluster. Parallel execution can be particularly useful for queries that involve large amounts of data, such as full table scans or complex joins.
Here are some general guidelines for when to consider using parallel execution:
Large Tables: If a query involves a large table or multiple large tables, parallel execution can be used to split the work across multiple processors and reduce the overall execution time.
Full Table Scans: If a query involves a full table scan, parallel execution can be used to split the scan across multiple processors and improve the performance of the query.
Partitioned Tables: If a query involves a partitioned table, parallel execution can be used to split the work across multiple partitions and improve the performance of the query.
Resource Availability: If there are available resources, such as multiple CPUs or nodes in a cluster, parallel execution can be used to take advantage of those resources and improve the performance of the query.
Performance Testing: If performance testing indicates that a query can benefit from parallel execution, it can be used to improve the performance of the query.
It is important to note that parallel execution may not always be the best solution for improving query performance. In some cases, other optimization techniques such as indexing, query tuning, or data partitioning may be more effective. The decision to use parallel execution should be based on a thorough analysis of the specific query and system requirements.
what is parallel DML
Suppose you have a table called SALES with millions of rows, and you want to update a column called SALES_AMOUNT for all rows where the sales amount is greater than 1000. You can use parallel DML to speed up this operation by dividing the update into multiple chunks and processing them in parallel.
Here's how you can use parallel DML to update the SALES_AMOUNT column in parallel with a degree of parallelism of 4:
ALTER SESSION ENABLE PARALLEL DML;
UPDATE /*+ PARALLEL(4) */ SALES
SET SALES_AMOUNT = SALES_AMOUNT * 1.1
WHERE SALES_AMOUNT > 1000;
what are the advantages of expdp over exp
Performance: Expdp is generally faster than exp, especially when exporting large amounts of data, due to its ability to use parallel processing and multithreading.
Scalability: Expdp can handle larger datasets than exp, and can be used to export data from multiple tables, schemas, or databases in a single operation.
Compression: Expdp offers compression options for the exported data, which can reduce the amount of disk space required and the time needed for the export operation.
Encryption: Expdp supports encryption of the exported data, providing an additional layer of security.
Granularity: Expdp provides greater control over the exported data, allowing you to specify which tables, schemas, or database objects to include or exclude, and to filter the data based on specific criteria.
Recovery: Expdp supports point-in-time recovery, allowing you to restore the data to a specific point in time.
What different partitioning methods you know?
range :
CREATE TABLE sales (
sale_id NUMBER,
sale_date DATE,
amount NUMBER
)
PARTITION BY RANGE (sale_date)
(
PARTITION sales_q1 VALUES LESS THAN (TO_DATE('01-APR-2023', 'DD-MON-YYYY')),
PARTITION sales_q2 VALUES LESS THAN (TO_DATE('01-JUL-2023', 'DD-MON-YYYY')),
PARTITION sales_q3 VALUES LESS THAN (TO_DATE('01-OCT-2023', 'DD-MON-YYYY')),
PARTITION sales_q4 VALUES LESS THAN (TO_DATE('01-JAN-2024', 'DD-MON-YYYY'))
);
list:
This method partitions data based on a list of discrete values in a designated column. For example, you can partition a customer table by region, such as North, South, East, and West.
CREATE TABLE customers (
customer_id NUMBER,
name VARCHAR2(100),
region VARCHAR2(50)
)
PARTITION BY LIST (region)
(
PARTITION cust_north VALUES ('North'),
PARTITION cust_south VALUES ('South'),
PARTITION cust_east VALUES ('East'),
PARTITION cust_west VALUES ('West')
);
Hash Partitioning: This method partitions data based on a hash function applied to a designated column. The data is distributed randomly across the partitions. For example, you can partition a large product catalog table by a product ID.
CREATE TABLE products (
product_id NUMBER,
product_name VARCHAR2(100),
category VARCHAR2(50)
)
PARTITION BY HASH (product_id)
PARTITIONS 4;
Interval Partitioning: This method is similar to range partitioning, but allows for automatic creation of new partitions based on a specified interval. For example, you can partition a sales table by week, and new partitions will be automatically created as new weeks are added to the data.
CREATE TABLE sales (
sale_id NUMBER,
sale_date DATE,
amount NUMBER
)
PARTITION BY RANGE INTERVAL ('1' MONTH) (
PARTITION sales_q1 VALUES LESS THAN (TO_DATE('01-APR-2023', 'DD-MON-YYYY'))
);
composite:
This method combines two or more partitioning methods to partition data using multiple criteria. For example, you can partition a sales table by range on the date column and then list partition each range by a region.
CREATE TABLE sales (
sale_id NUMBER,
sale_date DATE,
region VARCHAR2(50),
amount NUMBER
)
PARTITION BY RANGE (sale_date)
SUBPARTITION BY LIST (region)
(
PARTITION sales_q1 VALUES LESS THAN (TO_DATE('01-APR-2023', 'DD-MON-YYYY'))
(
SUBPARTITION sales_q1_north VALUES ('North'),
SUBPARTITION sales_q1_south VALUES ('South')
),
PARTITION sales_q2 VALUES LESS THAN (TO_DATE('01-JUL-2023', 'DD-MON-YYYY'))
(
SUBPARTITION sales_q2_north VALUES ('North'),
SUBPARTITION sales_q2_south VALUES ('South')
),
PARTITION sales_q3 VALUES LESS THAN (TO_DATE('01-OCT-2023', 'DD-MON-YYYY'))
(
SUBPARTITION sales_q3_east VALUES ('East'),
SUBPARTITION sales_q3_west VALUES ('West')
),
PARTITION sales_q4 VALUES LESS THAN (TO_DATE('01-JAN-2024', 'DD-MON-YYYY'))
(
SUBPARTITION sales_q4_east VALUES ('East'),
SUBPARTITION sales_q4_west VALUES ('West')
)
);
reference partioning:
This method allows you to partition a child table based on the partitioning key of the parent table. For example, you can partition a sales table by date range and then partition the order_items table by referencing the partition key of the sales table.
CREATE TABLE sales (
sale_id NUMBER,
sale_date DATE,
region_id NUMBER
)
PARTITION BY RANGE (sale_date)
(
PARTITION sales_q1 VALUES LESS THAN (TO_DATE('01-APR-2023', 'DD-MON-YYYY')),
PARTITION sales_q2 VALUES LESS THAN (TO_DATE('01-JUL-2023', 'DD-MON-YYYY')),
PARTITION sales_q3 VALUES LESS THAN
RMAN
what is the difference in RMAN hotbackup and begin backup.
Here are some additional details about each of these methods:
RMAN hotbackup. RMAN hotbackup is a complete backup of the database while it is open and accessible to users. This means that all of the data in the database will be backed up, including the data in the datafiles, the control file, and the redo logs. RMAN hotbackup is a good option if you need to make a complete backup of the database and you cannot afford to take the database offline.
Begin backup. Begin backup is a command that is used to start a backup operation. This command does not actually back up the database; it only starts the backup process. After you have issued the begin backup command, you can then use other RMAN commands to specify the files that you want to back up and the location where you want to store the backups. Begin backup is a good option if you only need to back up a few files from the database or if you want to schedule a backup to run at a later time.
What is a fracture block?
When a fractured block is detected, Oracle attempts to recover the data from the block by using a backup copy or by rebuilding the block from other available pieces of data. If the recovery is successful, the block is marked as usable again. Otherwise, the block remains offline and can cause data loss or inconsistency if it contains critical data.
How to check RMAN task progress
RMAN> SHOW ALL FOR TASK 123;
what to do if arch logs are deleted before they are backed up?
If archive logs are deleted before they are backed up, you will need to restore the database from a previous backup. This can be a time-consuming and complex process, so it is important to have a backup plan in place to prevent this from happening.
There are a few things you can do to recover from deleted archive logs:
Restore the database from a previous backup. This is the most reliable way to recover from deleted archive logs, but it can be time-consuming and complex.
Use incomplete recovery to recover the database to a point in time before the archive logs were deleted. This is a less reliable option, but it may be faster and easier than restoring the database from a full backup.
Use flashback to restore the database to a point in time before the archive logs were deleted. This is the fastest and easiest option, but it is only available if flashback is enabled for the database.
what is obsolate and expired backups
An obsolete backup is a backup piece or backup set that is no longer needed for recovery purposes because all of the data it contains is already included in a more recent backup.
An expired backup is a backup piece or backup set that has exceeded its retention period and is no longer usable for recovery.
In summary, an obsolete backup is a backup that is no longer needed due to redundancy, while an expired backup is a backup that is no longer needed due to age.
can we increase number of MRPs.
a single MRP process is started on a standby database, but you can increase the number of MRP processes to improve the recovery performance of the standby database. To do this, you need to modify the parameter "STANDBY_MAX_DATA_APPLY_SERVERS" in the database initialization parameter file.
Can we replace a missing datafile on primary from physical standby.
Assuming that you have a physical standby database that is synchronized with the primary database, the basic steps to replace a missing datafile on the primary from the standby are:
Identify the missing datafile on the primary database.
Determine the datafile's location on the physical standby database.
Copy the missing datafile from the physical standby database to the primary database's location.
Register the copied datafile on the primary database using the ALTER DATABASE CREATE DATAFILE statement.
Restart the recovery process on the standby database to ensure that it is synchronized with the primary database.
which process will tell listener abt load
Overall, the Load Balancing Advisory (LBA) plays a crucial role in managing the load on a RAC cluster and helps to ensure that the workload is balanced across the instances, maximizing the performance and availability of the database.
Why CLUSTER_DATABASE=flase to restore the RAC database... Control file lock
When restoring a RAC database, it is recommended to set the CLUSTER_DATABASE parameter to FALSE in the initialization parameter file (SPFILE) to prevent issues with control file locks. This is because when the database is part of an RAC configuration, the control file is shared across all instances in the cluster, and it may be locked by another instance during the restore process, causing the restore to fail.
What will you do if query is using wrong plan.
Let's say you have a query that joins two tables, orders and order_items, to retrieve information about customer orders. You notice that the query is taking a long time to execute and suspect that it may be using the wrong execution plan. Here are the steps you could take to address the issue:
Gather statistics: Use the DBMS_STATS package to gather up-to-date statistics for the orders and order_items tables, as well as any relevant indexes.
Refresh the query: Execute the query again to see if the optimizer generates a better execution plan.
Use hints: If refreshing the query does not help, you could try using optimizer hints to force the optimizer to use a particular execution plan. For example, you could use the /*+ USE_HASH(order_items) */ hint to force the optimizer to use a hash join instead of a nested loop join.
Use SQL Plan Management: Use SQL Plan Management to capture a good execution plan for the query and enforce it going forward.
Review the execution plan: Use the EXPLAIN PLAN command or the SQL Developer tool to review the execution plan for the query. Identify the steps that are taking the most time and investigate why the optimizer is choosing a particular plan.
Modify the query or data model: Depending on what you find in the execution plan, you may need to modify the query or the data model to improve performance. For example, you could consider adding an index on a join column, denormalizing the data model, or rewriting the query to use a more efficient join method.
By taking these steps, you can address issues with incorrect execution plans and improve the performance of your queries.
What to do if user can not connect to db from remote. What OS command will you use?
If a user is unable to connect to a database from a remote location, there are several things you can check and troubleshoot. Here are some steps you can take:
Check network connectivity: Verify that the user's network can reach the database server. You can use the ping command to test network connectivity from the user's machine to the database server. For example, on a Unix/Linux system, you can use the command ping <database server IP address>.
Check listener status: Verify that the listener is up and running on the database server. You can use the lsnrctl status command to check the status of the listener. For example, on a Unix/Linux system, you can use the command lsnrctl status.
Check listener configuration: Verify that the listener is configured to listen on the correct port and IP address. You can check the listener configuration by reviewing the listener.ora file on the database server.
Check firewall settings: If there is a firewall between the user's machine and the database server, verify that the firewall is configured to allow traffic on the database listener port.
Check database service status: Verify that the database is up and running and that the service is registered with the listener. You can use the srvctl status database -d <database name> command to check the status of the database service.
Check user credentials: Verify that the user is using the correct username and password to connect to the database.
Once you have identified and addressed any issues, the user should be able to connect to the database from a remote location.
what is DBtime and DB CPU
DB time, or Database Time, represents the total amount of time spent by the database processing a request.
DB CPU, or Database CPU time, represents the amount of CPU time used by the database instance
what is soft parse/ hard parse
n summary, soft parse is a quick parsing mechanism that is used when a valid execution plan already exists for the SQL statement, while hard parse is a more resource-intensive parsing mechanism that is used when a valid execution plan is not available or when the cached execution plan is invalid.
Comments
Post a Comment