Can you differentiate Redo vs. Rollback vs. Undo?
I find there is always some confusion when talking about Redo, Rollback and Undo. They all sound like pretty much the same thing or at least pretty close.
Redo: Every Oracle database has a set of (two or more) redo log files. The redo log records all changes made to data, including both uncommitted and committed changes. In addition to the online redo logs Oracle also stores archive redo logs. All redo logs are used in recovery situations.
Rollback: More specifically rollback segments. Rollback segments store the data as it was before changes were made. This is in contrast to the redo log which is a record of the insert/update/deletes.
Undo: Rollback segments. They both are really one in the same. Undo data is stored in the undo tablespace. Undo is helpful in building a read consistent view of data.
Alert. log showing this error “ORA-1109 signalled during: alter database close”. What is the reason behind it?
The ORA-1109 error just indicates that the database is not open for business. You'll have to open it up before you can proceed.
It may be while you are shutting down the database, somebody trying to open the database respectively. It is a failure attempt to open the database while shutdown is progress.Wait for the time to successfully shutdown the database and open it again for use. Alternatively you have to restart your oracle services on windows environment.
Which factors are to be considered for creating index on Table? How to select column for index?
Creation of index on table depends on size of table, volume of data. If size of table is large and we need only few data for selecting or in report then we need to create index. There are some basic reason of selecting column for indexing like cardinality and frequent usage in where condition of select query. Business rule is also forcing to create index like primary key, because configuring primary key or unique key automatically create unique index.
It is important to note that creation of so many indexes would affect the performance of DML on table because in single transaction should need to perform on various index segments and table simultaneously.
What is Secure External password Store (SEPS)?
Through the use of SEPS you can store password credentials for connecting to database by using a client side oracle wallet, this wallet stores signing credentials. This feature introduced since oracle 10g. Thus the application code, scheduled job, scripts no longer needed embedded username and passwords. This reduces risk because the passwords are no longer exposed and password management policies are more easily enforced without changing application code whenever username and password change.
Differentiate DB file sequential read wait/DB File Scattered Read?
Sequential read associated with index read where as scattered read has to do with full table scan. The sequential read, reads block into contiguous memory and DB scattered read gets from multiple block and scattered them into buffer cache.
I install oracle 10g on windows 7 successfully. I found every thing working fine except the toad is giving “cannot load oci.dll” error. Is this compatibility issue?
Read the toad user guide. You will get important information related to compatibility issue. In fact toad works with both 32 bit and 64 bit oracle server where as toad only work with 32 bit client. If you need 64 bit client for other applications, you can install both 32 bit and 64 bit client on a single machine and just tell the toad to use the 32 bit client.
What are the differences between Physical/Logical standby databases? How would you decide which one is best suited for your environment?
Physical standby DB:
– As the name, it is physically (datafiles, schema, other physical identity) same copy of the primary database.
– It synchronized with the primary database with Apply Redo to the standby DB.
Logical Standby DB:
– As the name logical information is the same as the production database, it may be physical structure can be different.
– It synchronized with primary database though SQL Apply, Redo received from the primary database into SQL statements and then executing these SQL statements on the standby DB.
– We can open “physical stand by DB to “read only” and make it available to the applications users (Only select is allowed during this period). we can not apply redo logs received from primary database at this time.
– We do not see such issues with logical standby database. We can open the database in normal mode and make it available to the users. At the same time, we can apply archived logs received from primary database.
– For OLTP large transaction database it is better to choose logical standby database.
How to re-organize schema?
We can use dbms_redefinition package for online re-organization of schema objects. Otherwise using import/export and data pump utility you can recreate or re-organize your schema.
To configure RMAN Backup for 100GB database? How we would estimate backup size and backup time?
Check the actual size of your database. For rman backup size almost depends on your actual size of database.
SELECT SUM(BYTES)/1024/1024/1024 FROM DBA_SEGMENTS;
Backup time depends on your hardware configuration of your server such as CPU, Memory, and Storage.
Later you can also minimize the backup time by configuring multiple channels with the backup scripts.
How can you control number of datafiles in oracle database?
The db_files parameter is a "soft limit " parameter that controls the maximum number of physical OS files that can map to an Oracle instance. The maxdatafiles parameter is a different - "hard limit" parameter. When issuing a "create database" command, the value specified for maxdatafiles is stored in Oracle control files and default value is 32. The maximum number of database files can be set with the init parameter db_files.
Regardless of the setting of this parameter, maximum per database: 65533 (May be less on some operating systems), Maximum number of datafiles per tablespace: OS dependent = usually 1022
You can also by Limited size of database blocks and by the DB_FILES initialization parameter for a particular instance. Bigfile tablespaces can contain only one file, but that file can have up to 4G blocks.
What is Latches and why they are used in oracle?
A latch is a serialization mechanism. It is used to gain access to shared data structure in order to latches the structure that will prevent others from modifying it while you are modifying it.
Why it is not necessary to take UNDO backup?
In fact it is not necessary to take UNDO tablespace backup either with COLD or HOT backup scripts but many of DBA include UNDO tablespace in their backup script.
You know when you do some transactions; redo entries will be generated and accepted! Just like that other tablespace whenever any change happens to UNDO tablespace or UNDO segments oracle will generate redo entries. So even you not backed up the UNDO tablespace, you have the redo entries through which you can recover or rollback the transactions.
What should be effect on DB performance if virtual memory used to store SGA parameter?
For optimal performance in most systems, the entire SGA should fit in real memory. If it does not, and if virtual memory is used to store parts of it, then overall database system performance can decrease dramatically. The reason for this is that portions of the SGA are paged (written to and read from disk) by the operating system.
What is the role of lock_sga parameter?
The LOCK_SGA parameter, when set to TRUE, locks the entire SGA into physical memory. This parameter cannot be used with automatic memory management or automatic shared memory management.
What is CSSCAN?
CSSCAN (Database Character Set Scanner) is a SCAN tool that allows us to see the impact of a database character set change or assist us to correct an incorrect database nls_characterset setup. This helps us to determine the best approach for converting the database characterset.
Differentiate between co-related sub-query and nested query?
Co-related sub query is one in which inner query is evaluated only once and from that result your outer query is evaluated where as Nested query is one in which Inner query is evaluated for multiple times for getting one row of that outer query.
Example: Query used with IN() clause is Co-related query.
SELECT EMPLOYEE_NUMBER, LOAN_CODE, DOCUMENT_NUMBER, LOAN_AMOUNT
FROM PAY_LOAN_TRANS
WHERE EMPLOYEE_NUMBER IN (SELECT EMPLOYEE_NUMBER
FROM PAY_EMPLOYEE_PERSONAL_INFO
WHERE EMPLOYEE_NUMBER BETWEEN 1 AND 100);
Example: Query used with = operator is Nested query
SELECT * FROM PARTIAL_PAYMENT_SEQUENCE
WHERE SEQCOD = (SELECT MAX(SEQCOD) FROM PARTIAL_PAYMENT_SEQUENCE);
One after noon suddenly you get a call from your application user and complaining the database is slow then what will be your first step to solve this issue?
High performance is common expectation for end user, in fact the database is never slow or fast in most of the case session connected to the database slow down when they receives unexpected hit. Thus to solve this issue you need to find those unexpected hit. To know exactly what the second session is doing join your query with v$session_wait.
SELECT NVL(s.username, '(oracle)') AS username, s.sid, s.serial#, sw.event, sw.wait_time, sw.seconds_in_wait, sw.state
FROM v$session_wait sw, v$session s
WHERE s.sid = sw.sid and s.username = 'HRMS'
ORDER BY sw.seconds_in_wait DESC;
Check the events that are waiting for something, try to find out the objects locks for that particular session. Follow the link: Find Locks : Blockers
Locking is not only the cause to effects the performance. Disk I/O contention is another case. When a session retrieves data from the database datafiles on disk to the buffer cache, it has to wait until the disk sends the data. The wait event shows up for the session as “db file sequential read” (for index scan) or “db file scattered read” (for full table scan). Query link: DB File Sequential Read Wait/ DB File Scattered Read , DB Locks
When you see the event, you know that the session is waiting for I/O from the disk to complete. To improve session performance, you have to reduce that waiting period. The exact step depends on specific situation, but the first technique “reducing the number of blocks retrieved by a SQL statement” almost always works.
– Reduce the number of blocks retrieved by the SQL statement. Examine the SQL statement to see if it is doing a full-table scan when it should be using an index, if it is using a wrong index, or if it can be rewritten to reduce the amount of data it retrieves.
– Place the tables used in the SQL statement on a faster part of the disk.
– Consider increasing the buffer cache to see if the expanded size will accommodate the additional blocks, therefore reducing the I/O and the wait.
– Tune the I/O subsystem to return data faster.
What are four errors found in an alert (error) log that can ruin a DBA's appetite and how can you avoid them?
If we are getting any issue regarding database while performing any activity we should check alert log file in dump destination.
Deadlock Errors (ORA-00060), Oracle Internal errors, Backup and recovery errors, Snapshot too old error (O1555)
What is PCT Free and PCT Used and PCT increase parameter in segment? What is growth factor?
PCTFREE is a block storage it uses to mention how much space should be left in database block for future updates (updating the records eg. previously name Smith after that we will update the name as Smith Taylor). If mention PCTFREE as 10, oracle will adding the new rows to block up to 90% it allows 10% for future updates.
If the PCT used was set to 60 this means if the data inside the block is 60 it is FULL and if the data inside the block is 59 it is Empty.
This is the parameter which specify in percent that a block can only used for insert or come in the free list(list of blocks in segment ready for insert operation) when used space in a block is less than PCTUSED.
Suppose value of pctused is 40 and pctfree is 20 then data can be inserted till 80 of the block directly. And suppose the used space is 60 and some one has perform a delete operation in a row in the same block which brings the used space to 50 .Now one cannot insert any record in the same block unless the used space comes down below 40 i.e. pctused.
What is dump destination? What are bdump, cdump and udump?
The dump destination is the location where the trace files are located for all the Oracle process.
bdump-->Background processes + alert_SID.log file location
cdump--> Core Processes dump, udump--> User Processes dump, adump--> for ASM processes
These destinations contains useful information related to process failures.
UDUMP is specifying the user dump directory where all user error logs (trace files) will be placed.
BDUMP is specifying the background dump directory where all database error logs (trace files) will be placed.
CDUMP is specifying the core dump directory where all OS error logs (core dump files) will be placed.
Default location is (ORACLE_BASE/admin/<SID>)
SQL>show parameters dump_dest;
It'll show you all the dump directories wherever it is currently located. You can change your parameters in init.ora by creating spfile from pfile.
What will you do if in any condition you do not know how to troubleshoot the error at all and there are no seniors or your co-workers around?
We need to find where in the compilation the error is occurring. We have to divide the code and check for correctness of the code part-by-part. This is called debugging. Keep checking the code until you find the code which is wrong.
Search forums for similar error codes or symptoms and make a plan then submit it to your supervising DBA if you are not authorized to carry it out yourself.
I am getting error "No Communication channel" after changing the domain name? What is the solution?
Here Question is not clear about Where the Oracle database is residing. If the Oracle Database is resides on your local machine then the domain name must be updated in the tnsnames.ora file. Change this file in ../Admin folder contained one. If you are accessing remote Database then there are no changes required to your tnsnames.ora file only check with tnsping with the database service name. Change the domain name in the sqlnet.ora file in NAMES.DEFAULT_DOMAIN parameter
You have taken import of a table in a database. You have got the Integrity constraint violation error. How you are going to resolve it.
If u wants to import the table just says constraints=n the movement table got imported then u create constraint on that tables.
What is the most important action a DBA must perform after changing the database from NOARCHIVELOG TO ARCHIVELOG?
First of all take an offline backup of whole database (including the (datafile controlfile and redolog files). It is obvious that archive log process should be started by:
SQL>alter system Archivelog start;
Otherwise the database halts if unable to rotate redo logs
Show one instance when you encountered an error in alert log and you overcome that error. What actions you took to overcome that error.
Oracle writes error in alert log file. Depending upon the error corrective action needs to be taken.
1) Deadlock Error: Take the trace file in user dump destination and analysis it for the error.
2) ORA-01555 Snapshot error: Check the query try to fine tune and check the undo size.
3) Unable to extent segment: Check the tablespace size and if require add space in the tablespace by 'alter database datafile .... resize' or alter tablespace add datafile command.
What is Ora-1555 Snapshot too Old error? Explain in detail?
Oracle Rollback Segments (Undo more recently) hold a copy of data before it was modified and they work in a round-robin fashion. Writing and then eventually overwriting the entries as soon as the changes are committed.
They are needed to provide read consistency (a consistent set of data at a point in time) or to allow a process to abandon or rollback the changes or for database recovery.
Here’s a typical scenario:-
User A opens a query to fetch every row from a billion row table. If User B updates and commits the last row of the billion row table a Rollback entry will be created so User A can see the data as it was before the update.
Other users are busily updating rows in the database and this in turn generates rollback – which may eventually cause the entry needed for User A to be overwritten (after all User B did commit the change – so it’s OK to overwrite the rollback segment). Maybe 15 minutes later the query is still running and User A finally fetches the last row of the billion row table – but the rollback entry is gone. He gets ORA-01555: Snapshot too old rollback segment too small
I have applied the following commands: Now what will happen, will the database will give an error / it will work?
Shutdown abort;
Startup;
Definitely database will be start without error but all uncommitted data will be lost such as killed all sessions, killed all transactions, and didn't write from the buffers because shutdown abort directly shutdown instance without committing.
There is four modes to shutdown the database:
1) Shutdown immediate, 2) Shutdown normal, 3) Shutdown transactional, 4) Shutdown aborts
When the database is shutdown by first 3 methods checkpoint takes place but when is shutdown by abort option it doesn't enforces checkpoints,it simply shutdowns without waiting any users to disconnect.
What is mutated trigger? In single user mode we got mutated error, as a DBA how you will resolve it?
Mutated error will occur when same table access more than once in one state. If you are using before in trigger block then replace it with after.
Explain Dual table. Is any data internally stored in dual Table. Lot of users is accessing select sysdate from dual and they getting some millisecond differences. If we execute SELECT SYSDATE FROM EMP; what error will we get. Why?
Dual is a system owned table created during database creation. Dual table consist of a single column and a single row with value x. We will not get any error if we execute select sysdate from scott.emp instead sysdate will be treated as a pseudo column and displays the value for all the rows retrieved. For Example if there is 12 rows in emp table it will give result of date in 12 rows.
As an Oracle DBA what are the entire UNIX file you should be familiar with?
To check the process use: ps -ef |grep pmon or ps -ef
To check the alert log file: Tail -f alert.log
To check the cpu usage; Top vmstat 2 5
What is a Database instance?
A database instance also known as server is a set of memory structures and background processes that access a set of database files. It is possible for a single database to be accessed by multiple instances (this is oracle parallel server option).
What are the Requirements of simple Database?
A simple database consists of:
One or more data files, One or more control files, Two or more redo log files, Multiple users/schemas, One or more rollback segments, One or more Tablespaces, Data dictionary tables, User objects (table, indexes, views etc.)
The server (Instance) that access the database consists of:
SGA (Database buffer, Dictionary Cache Buffers, Redo log buffers, Shared SQL pool), SMON (System Monitor),PMON (Process Monitor), LGWR (Log Write), DBWR (Data Base Write), ARCH (ARCHiver), CKPT (Check Point), RECO, Dispatcher, User Process with associated PGS
Which process writes data from data files to database buffer cache?
The Background process DBWR rights data from datafile to DB cache.
How to DROP an Oracle Database?
You can do it at the OS level by deleting all the files of the database. The files to be deleted can be found using:
1) select * from dba_data_files; 2) select * from v$logfile; 3) select * from v$controlfile; 4) archive log list
5) initSID.ora 6) clean the UDUMP, BDUMP, scripts etc, 7) Cleanup the listener.ora and the tnsnames.ora. Make sure that the oratab entry is also removed.
Otherwise, go to DBCA and click on delete database.
In Oracle 10g there is a new command to drop an entire database.
Startup restrict mount;
drop database <instance_name>;
In fact DBA should never drop a database via OS level commands rather use GUI utility DBCA to drop the database
How can be determining the size of the log files.
Select sum(bytes)/1024/1024 "size_in_MB" from v$log;
What is difference between Logical Standby Database and Physical Standby database?
A physical or logical standby database is a database replica created from a backup of a primary database. A physical standby database is physically identical to the primary database on a block-for-block basis. It's maintained in managed recovery mode to remain current and can be set to read only; archive logs are copied and applied.
A logical standby database is logically identical to the primary database. It is updated using SQL statements
How do you find whether the instance was started with pfile or spfile
1) SELECT name, value FROM v$parameter WHERE name = 'spfile';
This query will return NULL if you are using PFILE
2) SHOW PARAMETER spfile
This query will returns NULL in the value column if you are using pfile and not spfile
3) SELECT COUNT(*) FROM v$spparameter WHERE value IS NOT NULL;
If the count is non-zero then the instance is using a spfile, and if the count is zero then it is using a pfile:
SQL> SELECT DECODE(value, NULL, 'PFILE', 'SPFILE') "Init File Type"
FROM sys.v_$parameter WHERE name = 'spfile';
What is full backup?
A full backup is an operating system backup of all data files, on-line redo log files and control file that constitute oracle database and the parameter.If you are using the Rman for backup then in Rman full backup means Incremental backup on 0 level.
While taking hot backup (begin end backup) what will happens back end?
When we r taking hot backup (begin backup - end backup) the datafile header associated with the datafiles in the corresponding Tablespace is frozen. So Oracle will stop updating the datafile header but will continue to write data into datafiles. In hot backup oracle will generate more redos this is because oracle will write out complete changed blocks to the redo log files.
Which is the best option used to move database from one server to another serve on same network and Why?
Import – Export, Backup-Restore, Detach-Attach
Import-Export is the best option used to move database from one server to another serve on same network. It reduces the network traffic.Import/Export works well if you’re dealing with very small databases. If we have few million rows its takes minutes to copy when compared to seconds using backup and restore.
What is Different Type of RMAN Backup?
Full backup: During a Full backup (Level 0) all of the block ever used in datafile are backed up. The only difference between a level 0 incremental backup and a full backup is that a full backup is never included in an incremental strategy.
Comulative Backup: During a cumulative (Level 0) the entire block used since last full backup are backed up.
RMAN> BACKUP INCREMENTAL LEVEL 1 CUMULATIVE DATABASE; # blocks changed since level 0
Differential Backup: During incremental backup only those blocks that have changed since last cumulative (Level 1) or full backup (Level 0) are backed up. Incremental backup are differential by default.
RMAN> BACKUP INCREMENTAL LEVEL 1 DATABASE
Give one method for transferring a table from one schema to another:
There are several possible methods: Export-Import, CREATE TABLE... AS SELECT or COPY.
What is the purpose of the IMPORT option IGNORE? What is its default setting?
The IMPORT IGNORE option tells import to ignore "already exists" errors. If it is not specified the tables that already exist will be skipped. If it is specified, the error is ignored and the table’s data will be inserted. The default value is N.
What happens when the DEFAULT and TEMP tablespace clauses are left out from CREATE USER statements?
The user is assigned the SYSTEM tablespace as a default and temporary tablespace. This is bad because it causes user objects and temporary segments to be placed into the SYSTEM tablespace resulting in fragmentation and improper table placement (only data dictionary objects and the system rollback segment should be in SYSTEM).
What happens if the constraint name is left out of a constraint clause?
The Oracle system will use the default name of SYS_Cxxxx where xxxx is a system generated number. This is bad since it makes tracking which table the constraint belongs to or what the constraint does harder.
What happens if a Tablespace clause is left off of a primary key constraint clause?
This result in the index that is automatically generated being placed in then USERS default tablespace. Since this will usually be the same tablespace as the table is being created in, this can cause serious performance problems.
What happens if a primary key constraint is disabled and then enabled without fully specifying the index clause?
The index is created in the user’s default tablespace and all sizing information is lost. Oracle doesn’t store this information as a part of the constraint definition, but only as part of the index definition, when the constraint was disabled the index was dropped and the information is gone.
Using hot backup without being in archive log mode, can you recover in the event of a failure? Why or why not?
You can't recover the data because in archive log mode it take the backup of redo log files if it in Active mode, If it in inactive mode then it is not possible to take the backup of redolog files once the size is full, so in that case it is impossible to take hot backup
What causes the "snapshot too old" error? How can this be prevented or mitigated?
This is caused by large or long running transactions that have either wrapped onto their own rollback space or have had another transaction write on part of their rollback space. This can be prevented or mitigated by breaking the transaction into a set of smaller transactions or increasing the size of the rollback segments and their extents.
How can you tell if a database object is invalid?
select STATUS from user_objects where object_type='TABLE' AND OBJECT_NAME='LOGMNRT_TABPART$';
A user is getting an ORA-00942 error yet you know you have granted them permission on the table, what else should you check?
You need to check that the user has specified the full name of the object (SELECT empid FROM scott.emp; instead of SELECT empid FROM emp;) or has a synonym that points to that object (CREATE SYNONYM emp FOR scott.emp;)
A developer is trying to create a view and the database won’t let him. He has the "DEVELOPER" role which has the "CREATE VIEW" system privilege and SELECT grants on the tables he is using, what is the problem?
You need to verify the developer has direct grants on all tables used in the view. You can't create a stored object with grants given through a role.
If you have an example table, what is the best way to get sizing data for the production table implementation?
The best way is to analyze the table and then use the data provided in the DBA_TABLES view to get the average row length and other pertinent data for the calculation. The quick and dirty way is to look at the number of blocks the table is actually using and ratio the number of rows in the table to its number of blocks against the number of expected rows.
How can you find out how many users are currently logged into the database? How can you find their operating system id?
To look at the v$session or v$process views and check the current_logins parameter in the v$sysstat view. If you are on UNIX is to do a ps -ef|greporacle|wc -l? Command, but this only works against a single instance installation.
How can you determine if an index needs to be dropped and rebuilt?
Run the ANALYZE INDEX command on the index to validate its structure and then calculate the ratio of LF_BLK_LEN/LF_BLK_LEN+BR_BLK_LEN and if it isn’t near 1.0 (i.e. greater than 0.7 or so) then the index should be rebuilt or if the ratio BR_BLK_LEN/ LF_BLK_LEN+BR_BLK_LEN is nearing 0.3. It is not so easy to decide so I personally suggest contact to the expert before going to rebuild.
What is tkprof and how is it used?
The tkprof tool is a tuning tool used to determine CPU and execution times for SQL statements. You use it by first setting timed_statistics to true in the initialization file and then turning on tracing for either the entire database via the sql_trace parameter or for the session using the ALTER SESSION command. Once the trace file is generated you run the tkprof tool against the trace file and then look at the output from the tkprof tool. This can also be used to generate explain plan output.
What is Explain plan and how is it used?
The EXPLAIN PLAN command is a tool to tune SQL statements. To use it you must have an explain_table generated in the user you are running the explain plan for. This is created using the utlxplan.sql script. Once the explain plan table exists you run the explain plan command giving as its argument the SQL statement to be explained. The explain plan table is then queried to see the execution plan of the statement. Explain plans can also be run using tkprof.
How do you prevent output from coming to the screen?
The SET option TERMOUT controls output to the screen. Setting TERMOUT OFF turns off screen output. This option can be shortened to TERM.
How do you prevent Oracle from giving you informational messages during and after a SQL statement execution?
The SET options FEEDBACK and VERIFY can be set to OFF.
How do you generate file output from SQL?
By use of the SPOOL command
A tablespace has a table with 30 extents in it. Is this bad? Why or why not.
Multiple extents in and of themselves aren’t bad. However if you also have chained rows this can hurt performance.
How do you set up tablespaces during an Oracle installation?
You should always attempt to use the Oracle Flexible Architecture standard or another partitioning scheme to ensure proper separation of SYSTEM, ROLLBACK, REDO LOG, DATA, TEMPORARY and INDEX segments.
You see multiple fragments in the SYSTEM tablespace, what should you check first?
Ensure that users don’t have the SYSTEM tablespace as their TEMPORARY or DEFAULT tablespace assignment by checking the DBA_USERS view.
What are some indications that you need to increase the SHARED_POOL_SIZE parameter?
Poor data dictionary or library cache hit ratios, getting error ORA-04031. Another indication is steadily decreasing performance with all other tuning parameters the same.
Guideline for sizing db_block_size and db_multi_block_read for an application that does many full table scans?
Oracle almost always reads in 64k chunks. The two should have a product equal to 64 or a multiple of 64.
When looking at v$sysstat you see that sorts (disk) is high. Is this bad or good? If bad -How do you correct it?
If you get excessive disk sorts this is bad. This indicates you need to tune the sort area parameters in the initialization files. The major sort parameter is the SORT_AREA_SIZE parameter.
When should you increase copy latches? What parameters control copy latches?
When you get excessive contention for the copy latches as shown by the "redo copy" latch hit ratio. You can increase copy latches via the initialization parameter LOG_SIMULTANEOUS_COPIES to twice the number of CPUs on your system.
Where can you get a list of all initialization parameters for your instance? How about an indication if they are default settings or have been changed?
You can look in the init.ora file for an indication of manually set parameters. For all parameters, their value and whether or not the current value is the default value, look in the v$parameter view.
Describe hit ratio as it pertains to the database buffers.
What is the difference between instantaneous and cumulative hit ratio and which should be used for tuning?
The hit ratio is a measure of how many times the database was able to read a value from the buffers verses how many times it had to re-read a data value from the disks. A value greater than 80-90% is good, less could indicate problems. If you simply take the ratio of existing parameters this will be a cumulative value since the database started. If you do a comparison between pairs of readings based on some arbitrary time span, this is the instantaneous ratio for that time span. Generally speaking an instantaneous reading gives more valuable data since it will tell you what your instance is doing for the time it was generated over.
Discuss row chaining, how does it happen? How can you reduce it? How do you correct it?
Row chaining occurs when a VARCHAR2 value is updated and the length of the new value is longer than the old value and would not fit in the remaining block space. This results in the row chaining to another block. It can be reduced by setting the storage parameters on the table to appropriate values. It can be corrected by export and import of the effected table.
You are getting busy buffer waits. Is this bad? How can you find what is causing it?
Buffer busy waits could indicate contention in redo, rollback or data blocks. You need to check the v$waitstat view to see what areas are causing the problem. The value of the "count" column tells where the problem is, the "class" column tells you with what. UNDO is rollback segments, DATA is data base buffers.
If you see contention for library caches how you can fix it?
Increase the size of the shared pool.
If you see statistics that deal with "undo" what are they really talking about?
Rollback segments and associated structures.
If a tablespace has a default pctincrease of zero what will this cause (in relationship to the SMON process)?
The SMON process would not automatically coalesce its free space fragments.
If a tablespace shows excessive fragmentation what are some methods to defragment the tablespace? (7.1,7.2 and 7.3 only)
In Oracle 7.0 to 7.2 The use of the 'alter session set events 'immediate trace name coalesce level ts#'; command is the easiest way to defragment contiguous free space fragmentation. The ts# parameter corresponds to the ts# value found in the ts$ SYS table. In version 7.3 the alter tablespace coalesce; is best. If the free space is not contiguous then export, drop and import of the tablespace contents may be the only way to reclaim non-contiguous free space.
How can you tell if a tablespace has excessive fragmentation?
If a select against the dba_free_space table shows that the count of tablespaces extents is greater than the count of its data files, then it is fragmented.
You see the following on a status report: redo log space requests 23 redo log space wait time 0 Is this something to worry about?
What if redo log space wait time is high? How can you fix this?
Since the wait time is zero, no problem. If the wait time was high it might indicate a need for more or larger redo logs.
If you see a pin hit ratio of less than 0.8 in the estat library cache report is this a problem? If so, how do you fix it?
This indicates that the shared pool may be too small. Increase the shared pool size.
If you see the value for reloads is high in the estat library cache report is this a matter for concern?
Yes, you should strive for zero reloads if possible. If you see excessive reloads then increase the size of the shared pool.
You look at the dba_rollback_segs view and see that there is a large number of shrinks and they are of relatively small size, is this a problem? How can it be fixed if it is a problem?
A large number of small shrinks indicates a need to increase the size of the rollback segment extents. Ideally you should have no shrinks or a small number of large shrinks. To fix this just increase the size of the extents and adjust optimal accordingly.
You look at the dba_rollback_segs view and see that you have a large number of wraps is this a problem?
A large number of wraps indicates that your extent size for your rollback segments are probably too small. Increase the size of your extents to reduce the number of wraps. You can look at the average transaction size in the same view to get the information on transaction size.
You see multiple extents in the Temporary Tablespace. Is this a problem?
As long as they are all the same size this is not a problem. In fact, it can even improve performance since Oracle would not have to create a new extent when a user needs one.
How do you set up your Tablespace on installation Level: Low?
The answer here should show an understanding of separation of redo and rollback, data and indexes and isolation of SYSTEM tables from other tables. An example would be to specify that at least 7 disks should be used for an Oracle installation.
Disk Configuration:
SYSTEM tablespace on 1, Redo logs on 2 (mirrored redo logs), TEMPORARY tablespace on 3, ROLLBACK tablespace on 4, DATA and INDEXES 5,6
They should indicate how they will handle archive logs and exports as well as long as they have a logical plan for combining or further separation more or less disks can be specified.
You have installed Oracle and you are now setting up the actual instance. You have been waiting an hour for the initialization script to finish, what should you check first to determine if there is a problem?
Check to make sure that the archiver is not stuck. If archive logging is turned on during install a large number of logs will be created. This can fill up your archive log destination causing Oracle to stop to wait for more space.
When configuring SQLNET on the server what files must be set up?
INITIALIZATION file, TNSNAMES.ORA file, SQLNET.ORA file
When configuring SQLNET on the client what files need to be set up?
SQLNET.ORA, TNSNAMES.ORA
You have just started a new instance with a large SGA on a busy existing server. Performance is terrible, what should you check for?
The first thing to check with a large SGA is that it is not being swapped out.
What OS user should be used for the first part of an Oracle installation (on UNIX)?
You must use root first.
When should the default values for Oracle initialization parameters be used as is?
Never
How many control files should you have? Where should they be located?
At least 2 on separate disk spindles (Mirrored by Oracle).
How many redo logs should you have and how should they be configured for maximum recoverability?
You should have at least 3 groups of two redo logs with the two logs each on a separate disk spindle (mirrored by Oracle). The redo logs should not be on raw devices on UNIX if it can be avoided.
Why are recursive relationships bad? How do you resolve them?
A recursive relationship defines when or where a table relates to itself. It is considered as bad when it is a hard relationship (i.e. neither side is a "may" both are "must") as this can result in it not being possible to put in a top or perhaps a bottom of the table. For example in the EMPLOYEE table you could not put in the PRESIDENT of the company because he has no boss, or the junior janitor because he has no subordinates. These type of relationships are usually resolved by adding a small intersection entity.
What does a hard one-to-one relationship mean (one where the relationship on both ends is "must")?
This means the two entities should probably be made into one entity.
How should a many-to-many relationship be handled?
By adding an intersection entity table
What is an artificial (derived) primary key? When should an artificial (or derived) primary key be used?
A derived key comes from a sequence. Usually it is used when a concatenated key becomes too cumbersome to use as a foreign key.
When should you consider de-normalization?
Whenever performance analysis indicates it would be beneficial to do so without compromising data integrity.
-UNIX-
How can you determine the space left in a file system?
There are several commands to do this: du, df, or bdf
How can you determine the number of SQLNET users logged in to the UNIX system?
SQLNET users will show up with a process unique name that begins with oracle, if you do a ps -ef|grep oracle|wc -l you can get a count of the number of users.
What command is used to type files to the screen?
cat, more, pg
Can you remove an open file under UNIX?
Yes
What is the purpose of the grep command?
grep is a string search command that parses the specified string from the specified file or files
The system has a program that always includes the word nocomp in its name, how can you determine the number of processes that are using this program?
ps -ef|grep *nocomp*|wc -l
The system administrator tells you that the system has not been rebooted in 6 months, should he be proud of this?
Most UNIX systems should have a scheduled periodic reboot so file systems can be checked and cleaned and dead or zombie processes cleared out. May be, Some UNIX systems do not clean up well after themselves. Inode problems and dead user processes can accumulate causing possible performance and corruption problems.
How can you find dead processes?
ps -ef|grep zombie -- or -- who -d depending on the system.
How can you find all the processes on your system?
Use the ps command
How can you find your id on a system?
Use the "who am i" command.
What is the finger command?
The finger command uses data in the passwd file to give information on system users.
What is the easiest method to create a file on UNIX?
Use the touch command
What does >> do?
The ">>" redirection symbol appends the output from the command specified into the file specified. The file must already have been created.
If you are not sure what command does a particular UNIX function what is the best way to determine the command?
The UNIX man -k command will search the man pages for the value specified. Review the results from the command to find the command of interest.
How can you determine if an Oracle instance is up from the operating system level?
There are several base Oracle processes that will be running on multi-user operating systems, these will be smon, pmon, dbwr and lgwr. Any answer that has them using their operating system process showing feature to check for these is acceptable. For example, on UNIX ps -ef|grep pmon will show what instances are up.
Users from the PC clients are getting messages indicating : ORA-06114: NETTCP: SID lookup failure. What could the problem be?
The instance name is probably incorrect in their connection string.
Users from the PC clients are getting the following error stack:
ERROR: ORA-01034: ORACLE not available ORA-07318: smsget: open error when opening sgadef.dbf file. HP-UX Error: 2: No such file or directory What is the probable cause?
The Oracle instance is shutdown that they are trying to access, restart the instance.
How can you determine if the SQLNET process is running for SQLNET V1? How about V2?
For SQLNET V1 check for the existence of the orasrv process. You can use the command "tcpctl status" to get a full status of the V1 TCPIP server, other protocols have similar command formats. For SQLNET V2 check for the presence of the LISTENER process(s) or you can issue the command "lsnrctl status".
What file will give you Oracle instance status information? Where is it located?
The alert.ora log. It is located in the directory specified by the background_dump_dest parameter in the v$parameter table.
Users are not being allowed on the system. The following message is received: ORA-00257 archiver is stuck. Connect internal only, until freed. What is the problem?
The archive destination is probably full, backup the archivelogs and remove them and the archiver will re-start.
Where would you look to find out if a redo log was corrupted assuming you are using Oracle mirrored redo logs?
There is no message that comes to the SQLDBA or SRVMGR programs during startup in this situation, you must check the alert. log file for this information.
You attempt to add a datafile and get: ORA-01118: cannot add anymore datafiles: limit of 40 exceeded. What is the problem and how can you fix it?
When the database was created the db_files parameter in the initialization file was set to 40. You can shutdown and reset this to a higher value, up to the value of MAX_DATAFILES as specified at database creation. If the MAX_DATAFILES is set to low, you will have to rebuild the control file to increase it before proceeding.
You look at your fragmentation report and see that smon has not coalesced any of you tablespaces, even though you know several have large chunks of contiguous free extents. What is the problem?
Check the dba_tablespaces view for the value of pct_increase for the tablespaces. If pct_increase is zero, smon will not coalesce their free space.
Your users get the following error: ORA-00055 maximum number of DML locks exceeded? What is the problem and how do you fix it?
The number of DML Locks is set by the initialization parameter DML_LOCKS. If this value is set to low (which it is by default) you will get this error. Increase the value of DML_LOCKS. If you are sure that this is just a temporary problem, you can have them wait and then try again later and the error should clear.
You get a call from you backup DBA while you are on vacation. He has corrupted all of the control files while playing with the ALTER DATABASE BACKUP CONTROLFILE command. What do you do?
As long as all datafiles are safe and he was successful with the BACKUP controlfile command you can do the following:
CONNECT INTERNAL STARTUP MOUNT (Take any read-only tablespaces offline before next step
ALTER DATABASE DATAFILE .... OFFLINE;
RECOVER DATABASE USING BACKUP CONTROLFILE
ALTER DATABASE OPEN RESETLOGS; (bring read-only tablespaces back online)
Shutdown and backup the system, then restart If they have a recent output file from the ALTER DATABASE BACKUP CONTROL FILE TO TRACE; command, they can use that to recover as well.
If no backup of the control file is available then the following will be required: CONNECT INTERNAL STARTUP NOMOUNT CREATE CONTROL FILE .....; However, they will need to know all of the datafiles, logfiles, and settings for MAXLOGFILES, MAXLOGMEMBERS, MAXLOGHISTORY, MAXDATAFILES for the database to use the command.
You have taken a manual backup of a datafile using OS. How RMAN will know about it?
Whenever we take any backup through RMAN in the repository information of the backup is recorded. The RMAN repository can be either controlfile or recovery catalog. However if you take a backup through OS command then RMAN does not aware of that and hence recorded are not reflected in the repository. This is also true whenever we create a new controlfile or a backup taken by RMAN is transferred to another place using OS command then controlfile/recovery catalog does not know about the prior backups of the database.
So in order to restore database with a new created controlfile we need to inform RMAN about the backups taken before so that it can pick one to restore.
This task can be done by catalog command in RMAN.
§ Add information of backup pieces and image copies in the repository that are on disk.
§ Record a datafile copy as a level 0 incremental backup in the RMAN repository.
§ Record of a datafile copy that was taken by OS.
But CATALOG command has some restrictions. It can't do the following.
§ Can't catalog a file that belong to different database.
§ Can't catalog a backup piece that exists on an sbt device.
Example: Catalog Archive log
RMAN>CATALOG ARCHIVELOG '/oracle/oradata/arju/arc001_223.arc' '/oracle/oradata/arju/arc001_224.arc';
Catalog Datafile
To catalog datafile copy '/oradata/backup/users01.dbf' as an incremental level 0 backup your command will be
RMAN>CATALOG DATAFILE COPY '/oradata/backup/users01.dbf' LEVEL 0;
Note that this datafile copy was taken backup either using the RMAN BACKUP AS COPY command or by using operating system utilities in conjunction with ALTER TABLESPACE BEGIN/END BACKUP.
Catalog multiple copies in a directory:
RMAN>CATALOG START WITH '/tmp/backups' NOPROMPT;
Catalog files in the flash recovery area:
To catalog all files in the currently enabled flash recovery area without prompting the user for each one issue
RMAN>CATALOG RECOVERY AREA NOPROMPT;
Catalog backup pieces:
RMAN>CATALOG BACKUPPIECE '/oradata2/o4jccf4';
How to Uncatalog Backup?
In many cases you need to uncatalog command. Suppose you do not want a specific backup or copy to be eligible to be restored but also do not want to delete it.
To uncatalog all archived logs issue:
RMAN>CHANGE ARCHIVELOG ALL UNCATALOG;
To uncataog tablespace USERS issue:
RMAN>CHANGE BACKUP OF TABLESPACE USERS UNCATALOG;
To uncatalog a backuppiece name /oradata2/oft7qq issue:
RMAN>CHANGE BACKUPPIECE '/oradata2/oft7qq' UNCATALOG;
How would you find total size of database in OS level
The size of the database is the total size of the datafiles that make up the tablespaces of the database. These details are found in the dba_extents view.
select sum(bytes)/(1024*1024) from V$datafile;
select sum(bytes)/(1024*1024) from dba_data_files;
select sum(bytes)/(1024*1024) from dba_extents; Can we take incremental Backup with out taking complete Backup?
No, First full backup is needed
Question with Answer on Oracle database Patches
Patches are a small collection of files copied over to an existing installation. They are associated with particular versions of Oracle products.
The discussion will especially help for those beginners who are preparing for interview and inexperienced to apply the patches. In this article you will find all those things briefly with an example. For more details please study the oracle documentation and try to search with separate topics on this blog.
What are different Types of Patches?
Regular Patcheset: To upgrade to higher version we use database patchset. Please do not confuse between regular patchests and patch set updates (PSU). Consider the regular patchset is super set of PSU. Regular Patchset contain major bug fixes. In comparison to regular patch PSU will not change the version of oracle binaries such as sqlplus, import/export etc. The importance of PSU is automatically minimized once a regular patchset is released for a given version. It is mainly divided into two types:
Security or Critical Patch Update (CPU): Critical patch update quarterly delivered by oracle to fix security issues.
Patch set updated (PSU): It include CPU and bunch of other one-off patches. It is also quarterly delivered by oracle.
Interim (one-off) Patch: It is also known as patchset exception or one-off patch or interim patch. This is usually a single fix for single problem or enhancement. It released only when there is need of immediate fix or enhancement that cannot wait until for next release of patchset or bundle patch. It is applied using OPATCH utility and is not cumulative.
Bundle Patches: Bundle Patches includes both the quarterly security patches as well as recommended fixes (for Windows and Exadata only). When you try to download this patch you will find bundle of patches (different set of file) instead of single downloaded file (usually incase patchset).
Is Opatch (utility) is also another type of patch?
OPatch is utility from oracle corp. (Java based utility) that helps you in applying interim patches to Oracle's software and rolling back interim patches from Oracle's software. Opatch also able to Report already installed interim patch and can detect conflict when already interim patch has been applied. This program requires Java to be available on your system and requires installation of OUI. Thus from the above discussion coming to your question it is not ideal to say OPATCH is another patch.
When we applying single Patch, can you use OPATCH utility?
Yes, you can use Opatch incase of single patch. The only type of patch that cannot be used with OPatch is a patchset
When you applying Patchsets, You can use OUI.
Yes, Patcheset uses OUI. A patch set contains a large number of merged patches, to change the version of the product or introduce new functionality. Patch sets are cumulative bug fixes that fix all bugs and consume all patches since the last base release. Patch sets and the Patch Set Assistant are usually applied through OUI-based product specific installers.
Can you Apply OPATCH without downtime?
As you know for apply patch your database and listener must be down. When you apply OPTACH it will update your current ORACLE_HOME. Thus coming to your question to the point in fact it is not possible in case of single instance but in RAC you can Apply Opatch without downtime as there will be more separate ORACLE_HOME and more separate instances (running once instance on each ORACLE_HOME).
You have collection of patch (nearly 100 patches) or patchset. How can you apply only one patch from patcheset or patch bundle at ORACLE_HOME?
With Napply itself (by providing patch location and specific patch id) you can apply only one patch from a collection of extracted patch. For more information check the opatch util NApply –help. It will give you clear picture.
For Example:
opatch util napply <patch_location> -id 9 -skip_subset -skip_duplicate
This will apply only the patch id 9 from the patch location and will skip duplicate and subset of patch installed in your ORACLE_HOME.
How can you get minimum/detail information from inventory about patches applied and components installed?
You can try below command for minimum and detail information from inventory
C:\ORACLE_HOME\Opatch\opatch lsinventory –invPtrLoc “location of oraInst.loc file”
$ORACLE_HOME\OPatch\opatch lsinventory -detail -invPtrLoc “location of oraInst.loc file”
Differentiate Patcheset, CPU and PSU patch? What kind of errors usually resolved from them?
Critical Patch Update (CPU) was the original quarterly patches that were released by oracle to target the specific security fixes in various products. CPU is a subset of patchset updates (PSU). CPU are built on the base patchset version where as PSU are built on the base of previous PSU
Patch Set Updates (PSUs) are also released quarterly along with CPU patches are a superset of CPU patches in the term that PSU patch will include CPU patches and some other bug fixes released by oracle. PSU contain fixes for bugs that contain wrong results, Data Corruption etc but it doe not contain fixes for bugs that that may result in: Dictionary changes, Major Algorithm changes, Architectural changes, Optimizer plan changes
Regular patchset: Please do not confuse between regular patchests and patch set updates (PSU). Consider the regular patchset is super set of PSU. Regular Patchset contain major bug fixes. The importance of PSU is minimizing once a regular patchset is released for a given version. In comparison to regular patch PSU will not change the version of oracle binaries such as sqlplus, import/export etc.
If both CPU and PSU are available for given version which one, you will prefer to apply?
From the above discussion it is clear once you apply the PSU then the recommended way is to apply the next PSU only. In fact, no need to apply CPU on the top of PSU as PSU contain CPU (If you apply CPU over PSU will considered you are trying to rollback the PSU and will require more effort in fact). So if you have not decided or applied any of the patches then, I will suggest you to go to use PSU patches. For more details refer:Oracle Products [ID 1430923.1], ID 1446582.1
PSU is superset of CPU then why someone choose to apply a CPU rather than a PSU?
CPUs are smaller and more focused than PSU and mostly deal with security issues. It seems to be theoretically more consecutive approach and can cause less trouble than PSU as it has less code changing in it. Thus any one who is concerned only with security fixes and not functionality fixes, CPU may be good approach.
How can you find the PSU installed version?
PSU references at 5th place in the oracle version number which makes it easier to track such as (e.g. 10.2.0.3.1). To determine the PSU version installed, use OPATCH utility:
OPATCH lsinv -bugs_fixed | grep -i PSU
To find from the database:
Select substr(action_time,1,30) action_time, substr(id,1,10) id, substr(action,1,10) action,substr(version,1,8) version, substr(BUNDLE_SERIES,1,6) bundle, substr(comments,1,20) comments from registry$history;
Note: You can find the details from the above query if you already executed the catbundle.sql
Click to Check Existing Oracle Database Patch Status
Will Patch Application affect System Performance?
Sometimes applying certain patch could affect Application performance of SQL statements. Thus it is recommended to collect a set of performance statistics that can serve as a baseline before we make any major changes like applying a patch to the system.
Can you stop applying a patch after applying it to a few nodes? What are the possible issues?
Yes, it is possible to stop applying a patch after applying it to a few nodes. There is a prompt that allows you to stop applying the patch. But, Oracle recommends that you do not do this because you cannot apply another patch until the process is restarted and all the nodes are patched or the partially applied patch is rolled back.
How you know impact of patch before applying a patch?
OPATCH <option> -report
You can use the above command to know the impact of the patch before actually applying it.
How can you run patching in scripted mode?
opatch <option> -silent
You can use the above command to run the patches in scripted mode.
Can you use OPATCH 10.2 to apply 10.1 patches?
No, Opatch 10.2 is not backward compatible. You can use Opatch 10.2 only to apply 10.2 patches.
What you will do if you lost or corrupted your Central Inventory?
In that case when you lost or corrupted your Central Inventory and your ORACLE_HOME is safe, you just need to execute the command with –attachHomeflag, OUI automatically setup the Central Inventory for attached home.
What you will do if you lost your Oracle home inventory (comps.xml)?
Oracle recommended backup your ORACLE_HOME before applying any patchset. In that case either you can restore your ORACLE_HOME from the backup or perform the identical installation of the ORACLE_HOME.
When I apply a patchset or an interim patch in RAC, the patch is not propagated to some of my nodes. What do I do in that case?
In a RAC environment, the inventory contains a list of nodes associated with an Oracle home. It is important that during the application of a patchset or an interim patch, the inventory is correctly populated with the list of nodes. If the inventory is not correctly populated with values, the patch is propagated only to some of the nodes in the cluster.
OUI allows you to update the inventory.xml with the nodes available in the cluster using the -updateNodeList flag in Oracle Universal Installer.
When I apply a patch, getting the following errors:
"Opatch Session cannot load inventory for the given Oracle Home <Home_Location> Possible causes are: No read or write permission to ORACLE_HOME/.patch_storage; Central Inventory is locked by another OUI instance; No read permission to Central Inventory; The lock file exists in ORACLE_HOME/.patch_storage; The Oracle Home does not exist in Central Inventory". What do I do?
This error may occur because of any one or more of the following reasons:
– The ORACLE_HOME/.patch_storage may not have read/write permissions. Ensure that you give read/write permissions to this folder and apply the patch again.
– There may be another OUI instance running. Stop it and try applying the patch again.
– The Central Inventory may not have read permission. Ensure that you have given read permission to the Central Inventory and apply the patch again.
– The ORACLE_HOME/.patch_storage directory might be locked. If this directory is locked, you will find a file named patch_locked inside this directory. This may be due to a previously failed installation of a patch. To remove the lock, restore the Oracle home and remove thepatch_locked file from the ORACLE_HOME/.patch_storage directory.
– The Oracle home may not be present in the Central Inventory. This may be due to a corrupted or lost inventory or the inventory may not be registered in the Central Inventory.
We should check for the latest security patches on the Oracle metalink website http://metalink.oracle.com/ and we can find the regular security alert at the location http://technet.oracle.com/deploy/security/alert.htm
Caution: It is not advisable to apply the patches directly into the production server. The ideal solution is to apply or test the patches in test server before being moved into the production system.
Why drop table is not going into Recycle bin?
If you are using SYS user to drop any table then user’s object will not go to the recyclebin as there is no recyclebin for SYSTEM tablespace, even we have already SET recycle bin parameter TRUE.
Select * from v$parameter where name = 'recyclebin';
Show parameter recyclebin;
How to recover password in oracle 10g?
You can query with the table user_history$. The password history is store in this table.
How to detect inactive session to kill automatically?
You can use the SQLNET.EXPIRE_TIME for the dead connections (for abnormal disconnections) by specifying a time interval in minute to send a problem message that verify client/server connections are active. Setting the value greater than 0 to this parameter ensures that connection is not left open indefinitely, due to abnormal client termination. If probe finds a terminated connection, or connection that is no longer in use, it returns an error, causing the server process to exit.
SQLNET.EXPIRE_TIME=10
Why we need CASCADE option with DROP USER command whenever dropping a user and why "DROP USER" commands fails when we don't use it?
If a user having any object then ‘YES’ in that case you are not able to drop that user without using CASCADE option. The DROP USER with CASCADE option command drops user along with its all associated objects. Remember it is a DDL command after the execution of this command rollback cannot be performed.
Can you suggest the best steps to refresh a Database?
Refreshing the database is nothing but applying the change on one database (PROD) to another (Test). You can use import/export and RMAN method for this purpose.
Import/Export Method: If you database is small and if you need to refresh particular schema only then it is always better to use this method.
1. Export the dump file from source DB
2. Drop and recreate Test environment User.
3. Import the dump to destination DB.
RMAN Method: Now days RMAN is most likely to be used for backup and recovery. It is relatively easier and better method for full database refresh to be refreshed. It is taking less time as compare to import/export method. Here also you can use particular SCN based refreshing.
#!/usr/bin/ksh
export ORAENV_ASK='NO'
export ORACLE_SID=PRD
/usr/local/bin/oraenv
export NLS_LANG=American_america.us7ascii;
export NLS_DATE_FORMAT="Mon DD YYYY HH24:MI:SS";
$ORACLE_HOME/bin/rman target / nocatalog log=/tmp/duplicate_tape_TEST.log connect auxiliary sys/PASSWORD@TEST;
run
{
allocate auxiliary channel aux1 device type disk;
set until SCN 42612597059;
duplicate target database to "TEST" pfile='/u01/app/xxxx/product/10.2.0/db_1/dbs/initTEST.ora' NOFILENAMECHECK;
}
EOF
\
How will we know the IP address of our system in Linux environment?
Either use ipconfig command or ip addr show
It will give you all IP address and if you have oracle 9i you can query from SQL prompt.
SELECT UTL_INADDR.GET_HOST_ADDRESS "Host Address", UTL_INADDR.GET_HOST_NAME "Host Name" FROM DUAL;
Can we create Bigfile Tablespace for all databases?
Infact your question do we create bigfile tablespace for every database is not clear for me. If you are asking can we create bigfile for every database?
Yes you can but it is not ideal for every datafile if your work is suitable for small file then why you create bigfile but if your mean is impact of bigfile that depends on your requirements and storage.
A bigfile tablespace is having single very big datafile which can store 4GB to 128 TB.
Creating single large datafile reducing the requirement of SGA and also it will allow you modification at tablespace level. In fact it is ideal for ASM, logical device supporting stripping.
Avoid using bigfile tablespace where there is limited space availability. For more details impact, advantage, disadvantage of bigfile on my blog.
Can you gice more explanation on logfile states?
“CURRENT” state means 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 redo’s 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 redolog group contains no redo records belonging to a dirty buffer it is in an "INACTIVE" state. These inactive redolog 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.
What is difference between oracle SID and Oracle service name?
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.
How to find session for Remote users?
-- To return session id on remote session:
SELECT distinct sid FROM v$mystat;
-- Return session id of you in remote Environment:
Select sid from v$mystat@remot_db where rownum=1;
We have a complete cold Backup taken on Sunday. The database crashed on Wednesday. None of the database files are available. The only files we have are the taped backup archive files till Wednesday. Is there a possibility of recovering the database until the recent archive which we have in the tape using the cold backup.
Yes, if you have all the archive logs since the cold backup then you can recover to your last log
Steps:
1) Restore all backup datafiles, and controlfile. Also restore the password file and init.ora if you lost those too. Don't restore your redo logs if you backed them up.
2) Make sure that ORACLE_SID is set to the database you want to recover
3) startup mount;
4) Recover database using backup controlfile;
At this point Oracle should start applying all your archive logs, assuming that they're in log_archive_dest
5) alter database open resetlogs;
How to check RMAN version in oracle?
If you want to check RMAN catalog version then use the below query from SQL*plus
SQL> Select * from rcver;
If you want to check simply database version.
SQL> Select * from v$version;
What is the minimum size of Temporary Tablespace?
1041 KB
Difference b/w image copies and backup sets?
An image copy is identical, byte by byte, to the original datafile, control file, or archived redo log file. RMAN can write blocks from many files into the same backup set but can’t do so in the case of an image copy.
An RMAN image copy and a copy you make with an operating system copy command such as dd (which makes image copies) are identical. Since RMAN image copies are identical to copies made with operating system copy commands, you may use user-made image copies for an RMAN restore and recovery operation after first making the copies “known” to RMAN by using the catalog command.
You can make image copies only on disk but not on a tape device. "backup as copy database;" Therefore, you can use the backup as copy option only for disk backups, and the backup as backupset option is the only option you have for making tape backups.
How can we see the C:\ drive free space capacity from SQL?
create an external table to read data from a file that will be as below
create BAT file free.bat as
@setlocal enableextensions enable delayedexpansion
@echo off
for /f "tokens=3" %%a in ('dir c:\') do (
set bytesfree=%%a
)
set bytesfree=%bytesfree:,=%
echo %bytesfree%
endlocal && set bytesfree=%bytesfree%
You can create a schedular to run the above free.bat, free_space.txt inside the oracle directory.
Differentiate between Tuning Advisor and Access Advisor?
The tuning Advisor:
– It suggests indexes that might be very useful.
– It suggests query rewrites.
– It suggests SQL profile
The Access Advisor:
– It suggest indexes that may be useful
– Suggestion about materialized view.
– Suggestion about table partitions also in latest version of oracle.
How to give Access of particular table for particular user?
GRANT SELECT (EMPLOYEE_NUMBER), UPDATE (AMOUNT) ON HRMS.PAY_PAYMENT_MASTER TO SHAHID;
The Below command checks the SELECT privilege on the table PAY_PAYMENT_MASTER on the HRMS schema (if connected user is different than the schema)
SELECT PRIVILEGE
FROM ALL_TAB_PRIVS_RECD
WHERE PRIVILEGE = 'SELECT'
AND TABLE_NAME = 'PAY_PAYMENT_MASTER'
AND OWNER = 'HRMS'
UNION ALL
SELECT PRIVILEGE
FROM SESSION_PRIVS
WHERE PRIVILEGE = 'SELECT ANY TABLE';
What are the problem and complexities if we use SQL Tuning Advisor and Access Advisor together?
I think both the tools are useful for resolving SQL tuning issues. SQL Tuning Advisor seems to be doing logical optimization mainly by checking your SQL structure and statistics and the SQL Access Advisor does suggest good data access paths, that is mainly work which can be done better on disk.
Both SQL Tuning Advisor and SQL Access Advisor tools are quite powerful as they can source the SQL they will tune automatically from multiple different sources, including SQL cache, AWR, SQL tuning Sets and user defined workloads.
Related with the argument complexity and problem of using these tools or how you can use these tools together better to check oracle documentation.
I find there is always some confusion when talking about Redo, Rollback and Undo. They all sound like pretty much the same thing or at least pretty close.
Redo: Every Oracle database has a set of (two or more) redo log files. The redo log records all changes made to data, including both uncommitted and committed changes. In addition to the online redo logs Oracle also stores archive redo logs. All redo logs are used in recovery situations.
Rollback: More specifically rollback segments. Rollback segments store the data as it was before changes were made. This is in contrast to the redo log which is a record of the insert/update/deletes.
Undo: Rollback segments. They both are really one in the same. Undo data is stored in the undo tablespace. Undo is helpful in building a read consistent view of data.
Alert. log showing this error “ORA-1109 signalled during: alter database close”. What is the reason behind it?
The ORA-1109 error just indicates that the database is not open for business. You'll have to open it up before you can proceed.
It may be while you are shutting down the database, somebody trying to open the database respectively. It is a failure attempt to open the database while shutdown is progress.Wait for the time to successfully shutdown the database and open it again for use. Alternatively you have to restart your oracle services on windows environment.
Which factors are to be considered for creating index on Table? How to select column for index?
Creation of index on table depends on size of table, volume of data. If size of table is large and we need only few data for selecting or in report then we need to create index. There are some basic reason of selecting column for indexing like cardinality and frequent usage in where condition of select query. Business rule is also forcing to create index like primary key, because configuring primary key or unique key automatically create unique index.
It is important to note that creation of so many indexes would affect the performance of DML on table because in single transaction should need to perform on various index segments and table simultaneously.
What is Secure External password Store (SEPS)?
Through the use of SEPS you can store password credentials for connecting to database by using a client side oracle wallet, this wallet stores signing credentials. This feature introduced since oracle 10g. Thus the application code, scheduled job, scripts no longer needed embedded username and passwords. This reduces risk because the passwords are no longer exposed and password management policies are more easily enforced without changing application code whenever username and password change.
Differentiate DB file sequential read wait/DB File Scattered Read?
Sequential read associated with index read where as scattered read has to do with full table scan. The sequential read, reads block into contiguous memory and DB scattered read gets from multiple block and scattered them into buffer cache.
I install oracle 10g on windows 7 successfully. I found every thing working fine except the toad is giving “cannot load oci.dll” error. Is this compatibility issue?
Read the toad user guide. You will get important information related to compatibility issue. In fact toad works with both 32 bit and 64 bit oracle server where as toad only work with 32 bit client. If you need 64 bit client for other applications, you can install both 32 bit and 64 bit client on a single machine and just tell the toad to use the 32 bit client.
What are the differences between Physical/Logical standby databases? How would you decide which one is best suited for your environment?
Physical standby DB:
– As the name, it is physically (datafiles, schema, other physical identity) same copy of the primary database.
– It synchronized with the primary database with Apply Redo to the standby DB.
Logical Standby DB:
– As the name logical information is the same as the production database, it may be physical structure can be different.
– It synchronized with primary database though SQL Apply, Redo received from the primary database into SQL statements and then executing these SQL statements on the standby DB.
– We can open “physical stand by DB to “read only” and make it available to the applications users (Only select is allowed during this period). we can not apply redo logs received from primary database at this time.
– We do not see such issues with logical standby database. We can open the database in normal mode and make it available to the users. At the same time, we can apply archived logs received from primary database.
– For OLTP large transaction database it is better to choose logical standby database.
How to re-organize schema?
We can use dbms_redefinition package for online re-organization of schema objects. Otherwise using import/export and data pump utility you can recreate or re-organize your schema.
To configure RMAN Backup for 100GB database? How we would estimate backup size and backup time?
Check the actual size of your database. For rman backup size almost depends on your actual size of database.
SELECT SUM(BYTES)/1024/1024/1024 FROM DBA_SEGMENTS;
Backup time depends on your hardware configuration of your server such as CPU, Memory, and Storage.
Later you can also minimize the backup time by configuring multiple channels with the backup scripts.
How can you control number of datafiles in oracle database?
The db_files parameter is a "soft limit " parameter that controls the maximum number of physical OS files that can map to an Oracle instance. The maxdatafiles parameter is a different - "hard limit" parameter. When issuing a "create database" command, the value specified for maxdatafiles is stored in Oracle control files and default value is 32. The maximum number of database files can be set with the init parameter db_files.
Regardless of the setting of this parameter, maximum per database: 65533 (May be less on some operating systems), Maximum number of datafiles per tablespace: OS dependent = usually 1022
You can also by Limited size of database blocks and by the DB_FILES initialization parameter for a particular instance. Bigfile tablespaces can contain only one file, but that file can have up to 4G blocks.
What is Latches and why they are used in oracle?
A latch is a serialization mechanism. It is used to gain access to shared data structure in order to latches the structure that will prevent others from modifying it while you are modifying it.
Why it is not necessary to take UNDO backup?
In fact it is not necessary to take UNDO tablespace backup either with COLD or HOT backup scripts but many of DBA include UNDO tablespace in their backup script.
You know when you do some transactions; redo entries will be generated and accepted! Just like that other tablespace whenever any change happens to UNDO tablespace or UNDO segments oracle will generate redo entries. So even you not backed up the UNDO tablespace, you have the redo entries through which you can recover or rollback the transactions.
What should be effect on DB performance if virtual memory used to store SGA parameter?
For optimal performance in most systems, the entire SGA should fit in real memory. If it does not, and if virtual memory is used to store parts of it, then overall database system performance can decrease dramatically. The reason for this is that portions of the SGA are paged (written to and read from disk) by the operating system.
What is the role of lock_sga parameter?
The LOCK_SGA parameter, when set to TRUE, locks the entire SGA into physical memory. This parameter cannot be used with automatic memory management or automatic shared memory management.
What is CSSCAN?
CSSCAN (Database Character Set Scanner) is a SCAN tool that allows us to see the impact of a database character set change or assist us to correct an incorrect database nls_characterset setup. This helps us to determine the best approach for converting the database characterset.
Differentiate between co-related sub-query and nested query?
Co-related sub query is one in which inner query is evaluated only once and from that result your outer query is evaluated where as Nested query is one in which Inner query is evaluated for multiple times for getting one row of that outer query.
Example: Query used with IN() clause is Co-related query.
SELECT EMPLOYEE_NUMBER, LOAN_CODE, DOCUMENT_NUMBER, LOAN_AMOUNT
FROM PAY_LOAN_TRANS
WHERE EMPLOYEE_NUMBER IN (SELECT EMPLOYEE_NUMBER
FROM PAY_EMPLOYEE_PERSONAL_INFO
WHERE EMPLOYEE_NUMBER BETWEEN 1 AND 100);
Example: Query used with = operator is Nested query
SELECT * FROM PARTIAL_PAYMENT_SEQUENCE
WHERE SEQCOD = (SELECT MAX(SEQCOD) FROM PARTIAL_PAYMENT_SEQUENCE);
One after noon suddenly you get a call from your application user and complaining the database is slow then what will be your first step to solve this issue?
High performance is common expectation for end user, in fact the database is never slow or fast in most of the case session connected to the database slow down when they receives unexpected hit. Thus to solve this issue you need to find those unexpected hit. To know exactly what the second session is doing join your query with v$session_wait.
SELECT NVL(s.username, '(oracle)') AS username, s.sid, s.serial#, sw.event, sw.wait_time, sw.seconds_in_wait, sw.state
FROM v$session_wait sw, v$session s
WHERE s.sid = sw.sid and s.username = 'HRMS'
ORDER BY sw.seconds_in_wait DESC;
Check the events that are waiting for something, try to find out the objects locks for that particular session. Follow the link: Find Locks : Blockers
Locking is not only the cause to effects the performance. Disk I/O contention is another case. When a session retrieves data from the database datafiles on disk to the buffer cache, it has to wait until the disk sends the data. The wait event shows up for the session as “db file sequential read” (for index scan) or “db file scattered read” (for full table scan). Query link: DB File Sequential Read Wait/ DB File Scattered Read , DB Locks
When you see the event, you know that the session is waiting for I/O from the disk to complete. To improve session performance, you have to reduce that waiting period. The exact step depends on specific situation, but the first technique “reducing the number of blocks retrieved by a SQL statement” almost always works.
– Reduce the number of blocks retrieved by the SQL statement. Examine the SQL statement to see if it is doing a full-table scan when it should be using an index, if it is using a wrong index, or if it can be rewritten to reduce the amount of data it retrieves.
– Place the tables used in the SQL statement on a faster part of the disk.
– Consider increasing the buffer cache to see if the expanded size will accommodate the additional blocks, therefore reducing the I/O and the wait.
– Tune the I/O subsystem to return data faster.
What are four errors found in an alert (error) log that can ruin a DBA's appetite and how can you avoid them?
If we are getting any issue regarding database while performing any activity we should check alert log file in dump destination.
Deadlock Errors (ORA-00060), Oracle Internal errors, Backup and recovery errors, Snapshot too old error (O1555)
What is PCT Free and PCT Used and PCT increase parameter in segment? What is growth factor?
PCTFREE is a block storage it uses to mention how much space should be left in database block for future updates (updating the records eg. previously name Smith after that we will update the name as Smith Taylor). If mention PCTFREE as 10, oracle will adding the new rows to block up to 90% it allows 10% for future updates.
If the PCT used was set to 60 this means if the data inside the block is 60 it is FULL and if the data inside the block is 59 it is Empty.
This is the parameter which specify in percent that a block can only used for insert or come in the free list(list of blocks in segment ready for insert operation) when used space in a block is less than PCTUSED.
Suppose value of pctused is 40 and pctfree is 20 then data can be inserted till 80 of the block directly. And suppose the used space is 60 and some one has perform a delete operation in a row in the same block which brings the used space to 50 .Now one cannot insert any record in the same block unless the used space comes down below 40 i.e. pctused.
What is dump destination? What are bdump, cdump and udump?
The dump destination is the location where the trace files are located for all the Oracle process.
bdump-->Background processes + alert_SID.log file location
cdump--> Core Processes dump, udump--> User Processes dump, adump--> for ASM processes
These destinations contains useful information related to process failures.
UDUMP is specifying the user dump directory where all user error logs (trace files) will be placed.
BDUMP is specifying the background dump directory where all database error logs (trace files) will be placed.
CDUMP is specifying the core dump directory where all OS error logs (core dump files) will be placed.
Default location is (ORACLE_BASE/admin/<SID>)
SQL>show parameters dump_dest;
It'll show you all the dump directories wherever it is currently located. You can change your parameters in init.ora by creating spfile from pfile.
What will you do if in any condition you do not know how to troubleshoot the error at all and there are no seniors or your co-workers around?
We need to find where in the compilation the error is occurring. We have to divide the code and check for correctness of the code part-by-part. This is called debugging. Keep checking the code until you find the code which is wrong.
Search forums for similar error codes or symptoms and make a plan then submit it to your supervising DBA if you are not authorized to carry it out yourself.
I am getting error "No Communication channel" after changing the domain name? What is the solution?
Here Question is not clear about Where the Oracle database is residing. If the Oracle Database is resides on your local machine then the domain name must be updated in the tnsnames.ora file. Change this file in ../Admin folder contained one. If you are accessing remote Database then there are no changes required to your tnsnames.ora file only check with tnsping with the database service name. Change the domain name in the sqlnet.ora file in NAMES.DEFAULT_DOMAIN parameter
You have taken import of a table in a database. You have got the Integrity constraint violation error. How you are going to resolve it.
If u wants to import the table just says constraints=n the movement table got imported then u create constraint on that tables.
What is the most important action a DBA must perform after changing the database from NOARCHIVELOG TO ARCHIVELOG?
First of all take an offline backup of whole database (including the (datafile controlfile and redolog files). It is obvious that archive log process should be started by:
SQL>alter system Archivelog start;
Otherwise the database halts if unable to rotate redo logs
Show one instance when you encountered an error in alert log and you overcome that error. What actions you took to overcome that error.
Oracle writes error in alert log file. Depending upon the error corrective action needs to be taken.
1) Deadlock Error: Take the trace file in user dump destination and analysis it for the error.
2) ORA-01555 Snapshot error: Check the query try to fine tune and check the undo size.
3) Unable to extent segment: Check the tablespace size and if require add space in the tablespace by 'alter database datafile .... resize' or alter tablespace add datafile command.
What is Ora-1555 Snapshot too Old error? Explain in detail?
Oracle Rollback Segments (Undo more recently) hold a copy of data before it was modified and they work in a round-robin fashion. Writing and then eventually overwriting the entries as soon as the changes are committed.
They are needed to provide read consistency (a consistent set of data at a point in time) or to allow a process to abandon or rollback the changes or for database recovery.
Here’s a typical scenario:-
User A opens a query to fetch every row from a billion row table. If User B updates and commits the last row of the billion row table a Rollback entry will be created so User A can see the data as it was before the update.
Other users are busily updating rows in the database and this in turn generates rollback – which may eventually cause the entry needed for User A to be overwritten (after all User B did commit the change – so it’s OK to overwrite the rollback segment). Maybe 15 minutes later the query is still running and User A finally fetches the last row of the billion row table – but the rollback entry is gone. He gets ORA-01555: Snapshot too old rollback segment too small
I have applied the following commands: Now what will happen, will the database will give an error / it will work?
Shutdown abort;
Startup;
Definitely database will be start without error but all uncommitted data will be lost such as killed all sessions, killed all transactions, and didn't write from the buffers because shutdown abort directly shutdown instance without committing.
There is four modes to shutdown the database:
1) Shutdown immediate, 2) Shutdown normal, 3) Shutdown transactional, 4) Shutdown aborts
When the database is shutdown by first 3 methods checkpoint takes place but when is shutdown by abort option it doesn't enforces checkpoints,it simply shutdowns without waiting any users to disconnect.
What is mutated trigger? In single user mode we got mutated error, as a DBA how you will resolve it?
Mutated error will occur when same table access more than once in one state. If you are using before in trigger block then replace it with after.
Explain Dual table. Is any data internally stored in dual Table. Lot of users is accessing select sysdate from dual and they getting some millisecond differences. If we execute SELECT SYSDATE FROM EMP; what error will we get. Why?
Dual is a system owned table created during database creation. Dual table consist of a single column and a single row with value x. We will not get any error if we execute select sysdate from scott.emp instead sysdate will be treated as a pseudo column and displays the value for all the rows retrieved. For Example if there is 12 rows in emp table it will give result of date in 12 rows.
As an Oracle DBA what are the entire UNIX file you should be familiar with?
To check the process use: ps -ef |grep pmon or ps -ef
To check the alert log file: Tail -f alert.log
To check the cpu usage; Top vmstat 2 5
What is a Database instance?
A database instance also known as server is a set of memory structures and background processes that access a set of database files. It is possible for a single database to be accessed by multiple instances (this is oracle parallel server option).
What are the Requirements of simple Database?
A simple database consists of:
One or more data files, One or more control files, Two or more redo log files, Multiple users/schemas, One or more rollback segments, One or more Tablespaces, Data dictionary tables, User objects (table, indexes, views etc.)
The server (Instance) that access the database consists of:
SGA (Database buffer, Dictionary Cache Buffers, Redo log buffers, Shared SQL pool), SMON (System Monitor),PMON (Process Monitor), LGWR (Log Write), DBWR (Data Base Write), ARCH (ARCHiver), CKPT (Check Point), RECO, Dispatcher, User Process with associated PGS
Which process writes data from data files to database buffer cache?
The Background process DBWR rights data from datafile to DB cache.
How to DROP an Oracle Database?
You can do it at the OS level by deleting all the files of the database. The files to be deleted can be found using:
1) select * from dba_data_files; 2) select * from v$logfile; 3) select * from v$controlfile; 4) archive log list
5) initSID.ora 6) clean the UDUMP, BDUMP, scripts etc, 7) Cleanup the listener.ora and the tnsnames.ora. Make sure that the oratab entry is also removed.
Otherwise, go to DBCA and click on delete database.
In Oracle 10g there is a new command to drop an entire database.
Startup restrict mount;
drop database <instance_name>;
In fact DBA should never drop a database via OS level commands rather use GUI utility DBCA to drop the database
How can be determining the size of the log files.
Select sum(bytes)/1024/1024 "size_in_MB" from v$log;
What is difference between Logical Standby Database and Physical Standby database?
A physical or logical standby database is a database replica created from a backup of a primary database. A physical standby database is physically identical to the primary database on a block-for-block basis. It's maintained in managed recovery mode to remain current and can be set to read only; archive logs are copied and applied.
A logical standby database is logically identical to the primary database. It is updated using SQL statements
How do you find whether the instance was started with pfile or spfile
1) SELECT name, value FROM v$parameter WHERE name = 'spfile';
This query will return NULL if you are using PFILE
2) SHOW PARAMETER spfile
This query will returns NULL in the value column if you are using pfile and not spfile
3) SELECT COUNT(*) FROM v$spparameter WHERE value IS NOT NULL;
If the count is non-zero then the instance is using a spfile, and if the count is zero then it is using a pfile:
SQL> SELECT DECODE(value, NULL, 'PFILE', 'SPFILE') "Init File Type"
FROM sys.v_$parameter WHERE name = 'spfile';
What is full backup?
A full backup is an operating system backup of all data files, on-line redo log files and control file that constitute oracle database and the parameter.If you are using the Rman for backup then in Rman full backup means Incremental backup on 0 level.
While taking hot backup (begin end backup) what will happens back end?
When we r taking hot backup (begin backup - end backup) the datafile header associated with the datafiles in the corresponding Tablespace is frozen. So Oracle will stop updating the datafile header but will continue to write data into datafiles. In hot backup oracle will generate more redos this is because oracle will write out complete changed blocks to the redo log files.
Which is the best option used to move database from one server to another serve on same network and Why?
Import – Export, Backup-Restore, Detach-Attach
Import-Export is the best option used to move database from one server to another serve on same network. It reduces the network traffic.Import/Export works well if you’re dealing with very small databases. If we have few million rows its takes minutes to copy when compared to seconds using backup and restore.
What is Different Type of RMAN Backup?
Full backup: During a Full backup (Level 0) all of the block ever used in datafile are backed up. The only difference between a level 0 incremental backup and a full backup is that a full backup is never included in an incremental strategy.
Comulative Backup: During a cumulative (Level 0) the entire block used since last full backup are backed up.
RMAN> BACKUP INCREMENTAL LEVEL 1 CUMULATIVE DATABASE; # blocks changed since level 0
Differential Backup: During incremental backup only those blocks that have changed since last cumulative (Level 1) or full backup (Level 0) are backed up. Incremental backup are differential by default.
RMAN> BACKUP INCREMENTAL LEVEL 1 DATABASE
Give one method for transferring a table from one schema to another:
There are several possible methods: Export-Import, CREATE TABLE... AS SELECT or COPY.
What is the purpose of the IMPORT option IGNORE? What is its default setting?
The IMPORT IGNORE option tells import to ignore "already exists" errors. If it is not specified the tables that already exist will be skipped. If it is specified, the error is ignored and the table’s data will be inserted. The default value is N.
What happens when the DEFAULT and TEMP tablespace clauses are left out from CREATE USER statements?
The user is assigned the SYSTEM tablespace as a default and temporary tablespace. This is bad because it causes user objects and temporary segments to be placed into the SYSTEM tablespace resulting in fragmentation and improper table placement (only data dictionary objects and the system rollback segment should be in SYSTEM).
What happens if the constraint name is left out of a constraint clause?
The Oracle system will use the default name of SYS_Cxxxx where xxxx is a system generated number. This is bad since it makes tracking which table the constraint belongs to or what the constraint does harder.
What happens if a Tablespace clause is left off of a primary key constraint clause?
This result in the index that is automatically generated being placed in then USERS default tablespace. Since this will usually be the same tablespace as the table is being created in, this can cause serious performance problems.
What happens if a primary key constraint is disabled and then enabled without fully specifying the index clause?
The index is created in the user’s default tablespace and all sizing information is lost. Oracle doesn’t store this information as a part of the constraint definition, but only as part of the index definition, when the constraint was disabled the index was dropped and the information is gone.
Using hot backup without being in archive log mode, can you recover in the event of a failure? Why or why not?
You can't recover the data because in archive log mode it take the backup of redo log files if it in Active mode, If it in inactive mode then it is not possible to take the backup of redolog files once the size is full, so in that case it is impossible to take hot backup
What causes the "snapshot too old" error? How can this be prevented or mitigated?
This is caused by large or long running transactions that have either wrapped onto their own rollback space or have had another transaction write on part of their rollback space. This can be prevented or mitigated by breaking the transaction into a set of smaller transactions or increasing the size of the rollback segments and their extents.
How can you tell if a database object is invalid?
select STATUS from user_objects where object_type='TABLE' AND OBJECT_NAME='LOGMNRT_TABPART$';
A user is getting an ORA-00942 error yet you know you have granted them permission on the table, what else should you check?
You need to check that the user has specified the full name of the object (SELECT empid FROM scott.emp; instead of SELECT empid FROM emp;) or has a synonym that points to that object (CREATE SYNONYM emp FOR scott.emp;)
A developer is trying to create a view and the database won’t let him. He has the "DEVELOPER" role which has the "CREATE VIEW" system privilege and SELECT grants on the tables he is using, what is the problem?
You need to verify the developer has direct grants on all tables used in the view. You can't create a stored object with grants given through a role.
If you have an example table, what is the best way to get sizing data for the production table implementation?
The best way is to analyze the table and then use the data provided in the DBA_TABLES view to get the average row length and other pertinent data for the calculation. The quick and dirty way is to look at the number of blocks the table is actually using and ratio the number of rows in the table to its number of blocks against the number of expected rows.
How can you find out how many users are currently logged into the database? How can you find their operating system id?
To look at the v$session or v$process views and check the current_logins parameter in the v$sysstat view. If you are on UNIX is to do a ps -ef|greporacle|wc -l? Command, but this only works against a single instance installation.
How can you determine if an index needs to be dropped and rebuilt?
Run the ANALYZE INDEX command on the index to validate its structure and then calculate the ratio of LF_BLK_LEN/LF_BLK_LEN+BR_BLK_LEN and if it isn’t near 1.0 (i.e. greater than 0.7 or so) then the index should be rebuilt or if the ratio BR_BLK_LEN/ LF_BLK_LEN+BR_BLK_LEN is nearing 0.3. It is not so easy to decide so I personally suggest contact to the expert before going to rebuild.
What is tkprof and how is it used?
The tkprof tool is a tuning tool used to determine CPU and execution times for SQL statements. You use it by first setting timed_statistics to true in the initialization file and then turning on tracing for either the entire database via the sql_trace parameter or for the session using the ALTER SESSION command. Once the trace file is generated you run the tkprof tool against the trace file and then look at the output from the tkprof tool. This can also be used to generate explain plan output.
What is Explain plan and how is it used?
The EXPLAIN PLAN command is a tool to tune SQL statements. To use it you must have an explain_table generated in the user you are running the explain plan for. This is created using the utlxplan.sql script. Once the explain plan table exists you run the explain plan command giving as its argument the SQL statement to be explained. The explain plan table is then queried to see the execution plan of the statement. Explain plans can also be run using tkprof.
How do you prevent output from coming to the screen?
The SET option TERMOUT controls output to the screen. Setting TERMOUT OFF turns off screen output. This option can be shortened to TERM.
How do you prevent Oracle from giving you informational messages during and after a SQL statement execution?
The SET options FEEDBACK and VERIFY can be set to OFF.
How do you generate file output from SQL?
By use of the SPOOL command
A tablespace has a table with 30 extents in it. Is this bad? Why or why not.
Multiple extents in and of themselves aren’t bad. However if you also have chained rows this can hurt performance.
How do you set up tablespaces during an Oracle installation?
You should always attempt to use the Oracle Flexible Architecture standard or another partitioning scheme to ensure proper separation of SYSTEM, ROLLBACK, REDO LOG, DATA, TEMPORARY and INDEX segments.
You see multiple fragments in the SYSTEM tablespace, what should you check first?
Ensure that users don’t have the SYSTEM tablespace as their TEMPORARY or DEFAULT tablespace assignment by checking the DBA_USERS view.
What are some indications that you need to increase the SHARED_POOL_SIZE parameter?
Poor data dictionary or library cache hit ratios, getting error ORA-04031. Another indication is steadily decreasing performance with all other tuning parameters the same.
Guideline for sizing db_block_size and db_multi_block_read for an application that does many full table scans?
Oracle almost always reads in 64k chunks. The two should have a product equal to 64 or a multiple of 64.
When looking at v$sysstat you see that sorts (disk) is high. Is this bad or good? If bad -How do you correct it?
If you get excessive disk sorts this is bad. This indicates you need to tune the sort area parameters in the initialization files. The major sort parameter is the SORT_AREA_SIZE parameter.
When should you increase copy latches? What parameters control copy latches?
When you get excessive contention for the copy latches as shown by the "redo copy" latch hit ratio. You can increase copy latches via the initialization parameter LOG_SIMULTANEOUS_COPIES to twice the number of CPUs on your system.
Where can you get a list of all initialization parameters for your instance? How about an indication if they are default settings or have been changed?
You can look in the init.ora file for an indication of manually set parameters. For all parameters, their value and whether or not the current value is the default value, look in the v$parameter view.
Describe hit ratio as it pertains to the database buffers.
What is the difference between instantaneous and cumulative hit ratio and which should be used for tuning?
The hit ratio is a measure of how many times the database was able to read a value from the buffers verses how many times it had to re-read a data value from the disks. A value greater than 80-90% is good, less could indicate problems. If you simply take the ratio of existing parameters this will be a cumulative value since the database started. If you do a comparison between pairs of readings based on some arbitrary time span, this is the instantaneous ratio for that time span. Generally speaking an instantaneous reading gives more valuable data since it will tell you what your instance is doing for the time it was generated over.
Discuss row chaining, how does it happen? How can you reduce it? How do you correct it?
Row chaining occurs when a VARCHAR2 value is updated and the length of the new value is longer than the old value and would not fit in the remaining block space. This results in the row chaining to another block. It can be reduced by setting the storage parameters on the table to appropriate values. It can be corrected by export and import of the effected table.
You are getting busy buffer waits. Is this bad? How can you find what is causing it?
Buffer busy waits could indicate contention in redo, rollback or data blocks. You need to check the v$waitstat view to see what areas are causing the problem. The value of the "count" column tells where the problem is, the "class" column tells you with what. UNDO is rollback segments, DATA is data base buffers.
If you see contention for library caches how you can fix it?
Increase the size of the shared pool.
If you see statistics that deal with "undo" what are they really talking about?
Rollback segments and associated structures.
If a tablespace has a default pctincrease of zero what will this cause (in relationship to the SMON process)?
The SMON process would not automatically coalesce its free space fragments.
If a tablespace shows excessive fragmentation what are some methods to defragment the tablespace? (7.1,7.2 and 7.3 only)
In Oracle 7.0 to 7.2 The use of the 'alter session set events 'immediate trace name coalesce level ts#'; command is the easiest way to defragment contiguous free space fragmentation. The ts# parameter corresponds to the ts# value found in the ts$ SYS table. In version 7.3 the alter tablespace coalesce; is best. If the free space is not contiguous then export, drop and import of the tablespace contents may be the only way to reclaim non-contiguous free space.
How can you tell if a tablespace has excessive fragmentation?
If a select against the dba_free_space table shows that the count of tablespaces extents is greater than the count of its data files, then it is fragmented.
You see the following on a status report: redo log space requests 23 redo log space wait time 0 Is this something to worry about?
What if redo log space wait time is high? How can you fix this?
Since the wait time is zero, no problem. If the wait time was high it might indicate a need for more or larger redo logs.
If you see a pin hit ratio of less than 0.8 in the estat library cache report is this a problem? If so, how do you fix it?
This indicates that the shared pool may be too small. Increase the shared pool size.
If you see the value for reloads is high in the estat library cache report is this a matter for concern?
Yes, you should strive for zero reloads if possible. If you see excessive reloads then increase the size of the shared pool.
You look at the dba_rollback_segs view and see that there is a large number of shrinks and they are of relatively small size, is this a problem? How can it be fixed if it is a problem?
A large number of small shrinks indicates a need to increase the size of the rollback segment extents. Ideally you should have no shrinks or a small number of large shrinks. To fix this just increase the size of the extents and adjust optimal accordingly.
You look at the dba_rollback_segs view and see that you have a large number of wraps is this a problem?
A large number of wraps indicates that your extent size for your rollback segments are probably too small. Increase the size of your extents to reduce the number of wraps. You can look at the average transaction size in the same view to get the information on transaction size.
You see multiple extents in the Temporary Tablespace. Is this a problem?
As long as they are all the same size this is not a problem. In fact, it can even improve performance since Oracle would not have to create a new extent when a user needs one.
How do you set up your Tablespace on installation Level: Low?
The answer here should show an understanding of separation of redo and rollback, data and indexes and isolation of SYSTEM tables from other tables. An example would be to specify that at least 7 disks should be used for an Oracle installation.
Disk Configuration:
SYSTEM tablespace on 1, Redo logs on 2 (mirrored redo logs), TEMPORARY tablespace on 3, ROLLBACK tablespace on 4, DATA and INDEXES 5,6
They should indicate how they will handle archive logs and exports as well as long as they have a logical plan for combining or further separation more or less disks can be specified.
You have installed Oracle and you are now setting up the actual instance. You have been waiting an hour for the initialization script to finish, what should you check first to determine if there is a problem?
Check to make sure that the archiver is not stuck. If archive logging is turned on during install a large number of logs will be created. This can fill up your archive log destination causing Oracle to stop to wait for more space.
When configuring SQLNET on the server what files must be set up?
INITIALIZATION file, TNSNAMES.ORA file, SQLNET.ORA file
When configuring SQLNET on the client what files need to be set up?
SQLNET.ORA, TNSNAMES.ORA
You have just started a new instance with a large SGA on a busy existing server. Performance is terrible, what should you check for?
The first thing to check with a large SGA is that it is not being swapped out.
What OS user should be used for the first part of an Oracle installation (on UNIX)?
You must use root first.
When should the default values for Oracle initialization parameters be used as is?
Never
How many control files should you have? Where should they be located?
At least 2 on separate disk spindles (Mirrored by Oracle).
How many redo logs should you have and how should they be configured for maximum recoverability?
You should have at least 3 groups of two redo logs with the two logs each on a separate disk spindle (mirrored by Oracle). The redo logs should not be on raw devices on UNIX if it can be avoided.
Why are recursive relationships bad? How do you resolve them?
A recursive relationship defines when or where a table relates to itself. It is considered as bad when it is a hard relationship (i.e. neither side is a "may" both are "must") as this can result in it not being possible to put in a top or perhaps a bottom of the table. For example in the EMPLOYEE table you could not put in the PRESIDENT of the company because he has no boss, or the junior janitor because he has no subordinates. These type of relationships are usually resolved by adding a small intersection entity.
What does a hard one-to-one relationship mean (one where the relationship on both ends is "must")?
This means the two entities should probably be made into one entity.
How should a many-to-many relationship be handled?
By adding an intersection entity table
What is an artificial (derived) primary key? When should an artificial (or derived) primary key be used?
A derived key comes from a sequence. Usually it is used when a concatenated key becomes too cumbersome to use as a foreign key.
When should you consider de-normalization?
Whenever performance analysis indicates it would be beneficial to do so without compromising data integrity.
-UNIX-
How can you determine the space left in a file system?
There are several commands to do this: du, df, or bdf
How can you determine the number of SQLNET users logged in to the UNIX system?
SQLNET users will show up with a process unique name that begins with oracle, if you do a ps -ef|grep oracle|wc -l you can get a count of the number of users.
What command is used to type files to the screen?
cat, more, pg
Can you remove an open file under UNIX?
Yes
What is the purpose of the grep command?
grep is a string search command that parses the specified string from the specified file or files
The system has a program that always includes the word nocomp in its name, how can you determine the number of processes that are using this program?
ps -ef|grep *nocomp*|wc -l
The system administrator tells you that the system has not been rebooted in 6 months, should he be proud of this?
Most UNIX systems should have a scheduled periodic reboot so file systems can be checked and cleaned and dead or zombie processes cleared out. May be, Some UNIX systems do not clean up well after themselves. Inode problems and dead user processes can accumulate causing possible performance and corruption problems.
How can you find dead processes?
ps -ef|grep zombie -- or -- who -d depending on the system.
How can you find all the processes on your system?
Use the ps command
How can you find your id on a system?
Use the "who am i" command.
What is the finger command?
The finger command uses data in the passwd file to give information on system users.
What is the easiest method to create a file on UNIX?
Use the touch command
What does >> do?
The ">>" redirection symbol appends the output from the command specified into the file specified. The file must already have been created.
If you are not sure what command does a particular UNIX function what is the best way to determine the command?
The UNIX man -k command will search the man pages for the value specified. Review the results from the command to find the command of interest.
How can you determine if an Oracle instance is up from the operating system level?
There are several base Oracle processes that will be running on multi-user operating systems, these will be smon, pmon, dbwr and lgwr. Any answer that has them using their operating system process showing feature to check for these is acceptable. For example, on UNIX ps -ef|grep pmon will show what instances are up.
Users from the PC clients are getting messages indicating : ORA-06114: NETTCP: SID lookup failure. What could the problem be?
The instance name is probably incorrect in their connection string.
Users from the PC clients are getting the following error stack:
ERROR: ORA-01034: ORACLE not available ORA-07318: smsget: open error when opening sgadef.dbf file. HP-UX Error: 2: No such file or directory What is the probable cause?
The Oracle instance is shutdown that they are trying to access, restart the instance.
How can you determine if the SQLNET process is running for SQLNET V1? How about V2?
For SQLNET V1 check for the existence of the orasrv process. You can use the command "tcpctl status" to get a full status of the V1 TCPIP server, other protocols have similar command formats. For SQLNET V2 check for the presence of the LISTENER process(s) or you can issue the command "lsnrctl status".
What file will give you Oracle instance status information? Where is it located?
The alert.ora log. It is located in the directory specified by the background_dump_dest parameter in the v$parameter table.
Users are not being allowed on the system. The following message is received: ORA-00257 archiver is stuck. Connect internal only, until freed. What is the problem?
The archive destination is probably full, backup the archivelogs and remove them and the archiver will re-start.
Where would you look to find out if a redo log was corrupted assuming you are using Oracle mirrored redo logs?
There is no message that comes to the SQLDBA or SRVMGR programs during startup in this situation, you must check the alert. log file for this information.
You attempt to add a datafile and get: ORA-01118: cannot add anymore datafiles: limit of 40 exceeded. What is the problem and how can you fix it?
When the database was created the db_files parameter in the initialization file was set to 40. You can shutdown and reset this to a higher value, up to the value of MAX_DATAFILES as specified at database creation. If the MAX_DATAFILES is set to low, you will have to rebuild the control file to increase it before proceeding.
You look at your fragmentation report and see that smon has not coalesced any of you tablespaces, even though you know several have large chunks of contiguous free extents. What is the problem?
Check the dba_tablespaces view for the value of pct_increase for the tablespaces. If pct_increase is zero, smon will not coalesce their free space.
Your users get the following error: ORA-00055 maximum number of DML locks exceeded? What is the problem and how do you fix it?
The number of DML Locks is set by the initialization parameter DML_LOCKS. If this value is set to low (which it is by default) you will get this error. Increase the value of DML_LOCKS. If you are sure that this is just a temporary problem, you can have them wait and then try again later and the error should clear.
You get a call from you backup DBA while you are on vacation. He has corrupted all of the control files while playing with the ALTER DATABASE BACKUP CONTROLFILE command. What do you do?
As long as all datafiles are safe and he was successful with the BACKUP controlfile command you can do the following:
CONNECT INTERNAL STARTUP MOUNT (Take any read-only tablespaces offline before next step
ALTER DATABASE DATAFILE .... OFFLINE;
RECOVER DATABASE USING BACKUP CONTROLFILE
ALTER DATABASE OPEN RESETLOGS; (bring read-only tablespaces back online)
Shutdown and backup the system, then restart If they have a recent output file from the ALTER DATABASE BACKUP CONTROL FILE TO TRACE; command, they can use that to recover as well.
If no backup of the control file is available then the following will be required: CONNECT INTERNAL STARTUP NOMOUNT CREATE CONTROL FILE .....; However, they will need to know all of the datafiles, logfiles, and settings for MAXLOGFILES, MAXLOGMEMBERS, MAXLOGHISTORY, MAXDATAFILES for the database to use the command.
You have taken a manual backup of a datafile using OS. How RMAN will know about it?
Whenever we take any backup through RMAN in the repository information of the backup is recorded. The RMAN repository can be either controlfile or recovery catalog. However if you take a backup through OS command then RMAN does not aware of that and hence recorded are not reflected in the repository. This is also true whenever we create a new controlfile or a backup taken by RMAN is transferred to another place using OS command then controlfile/recovery catalog does not know about the prior backups of the database.
So in order to restore database with a new created controlfile we need to inform RMAN about the backups taken before so that it can pick one to restore.
This task can be done by catalog command in RMAN.
§ Add information of backup pieces and image copies in the repository that are on disk.
§ Record a datafile copy as a level 0 incremental backup in the RMAN repository.
§ Record of a datafile copy that was taken by OS.
But CATALOG command has some restrictions. It can't do the following.
§ Can't catalog a file that belong to different database.
§ Can't catalog a backup piece that exists on an sbt device.
Example: Catalog Archive log
RMAN>CATALOG ARCHIVELOG '/oracle/oradata/arju/arc001_223.arc' '/oracle/oradata/arju/arc001_224.arc';
Catalog Datafile
To catalog datafile copy '/oradata/backup/users01.dbf' as an incremental level 0 backup your command will be
RMAN>CATALOG DATAFILE COPY '/oradata/backup/users01.dbf' LEVEL 0;
Note that this datafile copy was taken backup either using the RMAN BACKUP AS COPY command or by using operating system utilities in conjunction with ALTER TABLESPACE BEGIN/END BACKUP.
Catalog multiple copies in a directory:
RMAN>CATALOG START WITH '/tmp/backups' NOPROMPT;
Catalog files in the flash recovery area:
To catalog all files in the currently enabled flash recovery area without prompting the user for each one issue
RMAN>CATALOG RECOVERY AREA NOPROMPT;
Catalog backup pieces:
RMAN>CATALOG BACKUPPIECE '/oradata2/o4jccf4';
How to Uncatalog Backup?
In many cases you need to uncatalog command. Suppose you do not want a specific backup or copy to be eligible to be restored but also do not want to delete it.
To uncatalog all archived logs issue:
RMAN>CHANGE ARCHIVELOG ALL UNCATALOG;
To uncataog tablespace USERS issue:
RMAN>CHANGE BACKUP OF TABLESPACE USERS UNCATALOG;
To uncatalog a backuppiece name /oradata2/oft7qq issue:
RMAN>CHANGE BACKUPPIECE '/oradata2/oft7qq' UNCATALOG;
How would you find total size of database in OS level
The size of the database is the total size of the datafiles that make up the tablespaces of the database. These details are found in the dba_extents view.
select sum(bytes)/(1024*1024) from V$datafile;
select sum(bytes)/(1024*1024) from dba_data_files;
select sum(bytes)/(1024*1024) from dba_extents; Can we take incremental Backup with out taking complete Backup?
No, First full backup is needed
Question with Answer on Oracle database Patches
Patches are a small collection of files copied over to an existing installation. They are associated with particular versions of Oracle products.
The discussion will especially help for those beginners who are preparing for interview and inexperienced to apply the patches. In this article you will find all those things briefly with an example. For more details please study the oracle documentation and try to search with separate topics on this blog.
What are different Types of Patches?
Regular Patcheset: To upgrade to higher version we use database patchset. Please do not confuse between regular patchests and patch set updates (PSU). Consider the regular patchset is super set of PSU. Regular Patchset contain major bug fixes. In comparison to regular patch PSU will not change the version of oracle binaries such as sqlplus, import/export etc. The importance of PSU is automatically minimized once a regular patchset is released for a given version. It is mainly divided into two types:
Security or Critical Patch Update (CPU): Critical patch update quarterly delivered by oracle to fix security issues.
Patch set updated (PSU): It include CPU and bunch of other one-off patches. It is also quarterly delivered by oracle.
Interim (one-off) Patch: It is also known as patchset exception or one-off patch or interim patch. This is usually a single fix for single problem or enhancement. It released only when there is need of immediate fix or enhancement that cannot wait until for next release of patchset or bundle patch. It is applied using OPATCH utility and is not cumulative.
Bundle Patches: Bundle Patches includes both the quarterly security patches as well as recommended fixes (for Windows and Exadata only). When you try to download this patch you will find bundle of patches (different set of file) instead of single downloaded file (usually incase patchset).
Is Opatch (utility) is also another type of patch?
OPatch is utility from oracle corp. (Java based utility) that helps you in applying interim patches to Oracle's software and rolling back interim patches from Oracle's software. Opatch also able to Report already installed interim patch and can detect conflict when already interim patch has been applied. This program requires Java to be available on your system and requires installation of OUI. Thus from the above discussion coming to your question it is not ideal to say OPATCH is another patch.
When we applying single Patch, can you use OPATCH utility?
Yes, you can use Opatch incase of single patch. The only type of patch that cannot be used with OPatch is a patchset
When you applying Patchsets, You can use OUI.
Yes, Patcheset uses OUI. A patch set contains a large number of merged patches, to change the version of the product or introduce new functionality. Patch sets are cumulative bug fixes that fix all bugs and consume all patches since the last base release. Patch sets and the Patch Set Assistant are usually applied through OUI-based product specific installers.
Can you Apply OPATCH without downtime?
As you know for apply patch your database and listener must be down. When you apply OPTACH it will update your current ORACLE_HOME. Thus coming to your question to the point in fact it is not possible in case of single instance but in RAC you can Apply Opatch without downtime as there will be more separate ORACLE_HOME and more separate instances (running once instance on each ORACLE_HOME).
You have collection of patch (nearly 100 patches) or patchset. How can you apply only one patch from patcheset or patch bundle at ORACLE_HOME?
With Napply itself (by providing patch location and specific patch id) you can apply only one patch from a collection of extracted patch. For more information check the opatch util NApply –help. It will give you clear picture.
For Example:
opatch util napply <patch_location> -id 9 -skip_subset -skip_duplicate
This will apply only the patch id 9 from the patch location and will skip duplicate and subset of patch installed in your ORACLE_HOME.
How can you get minimum/detail information from inventory about patches applied and components installed?
You can try below command for minimum and detail information from inventory
C:\ORACLE_HOME\Opatch\opatch lsinventory –invPtrLoc “location of oraInst.loc file”
$ORACLE_HOME\OPatch\opatch lsinventory -detail -invPtrLoc “location of oraInst.loc file”
Differentiate Patcheset, CPU and PSU patch? What kind of errors usually resolved from them?
Critical Patch Update (CPU) was the original quarterly patches that were released by oracle to target the specific security fixes in various products. CPU is a subset of patchset updates (PSU). CPU are built on the base patchset version where as PSU are built on the base of previous PSU
Patch Set Updates (PSUs) are also released quarterly along with CPU patches are a superset of CPU patches in the term that PSU patch will include CPU patches and some other bug fixes released by oracle. PSU contain fixes for bugs that contain wrong results, Data Corruption etc but it doe not contain fixes for bugs that that may result in: Dictionary changes, Major Algorithm changes, Architectural changes, Optimizer plan changes
Regular patchset: Please do not confuse between regular patchests and patch set updates (PSU). Consider the regular patchset is super set of PSU. Regular Patchset contain major bug fixes. The importance of PSU is minimizing once a regular patchset is released for a given version. In comparison to regular patch PSU will not change the version of oracle binaries such as sqlplus, import/export etc.
If both CPU and PSU are available for given version which one, you will prefer to apply?
From the above discussion it is clear once you apply the PSU then the recommended way is to apply the next PSU only. In fact, no need to apply CPU on the top of PSU as PSU contain CPU (If you apply CPU over PSU will considered you are trying to rollback the PSU and will require more effort in fact). So if you have not decided or applied any of the patches then, I will suggest you to go to use PSU patches. For more details refer:Oracle Products [ID 1430923.1], ID 1446582.1
PSU is superset of CPU then why someone choose to apply a CPU rather than a PSU?
CPUs are smaller and more focused than PSU and mostly deal with security issues. It seems to be theoretically more consecutive approach and can cause less trouble than PSU as it has less code changing in it. Thus any one who is concerned only with security fixes and not functionality fixes, CPU may be good approach.
How can you find the PSU installed version?
PSU references at 5th place in the oracle version number which makes it easier to track such as (e.g. 10.2.0.3.1). To determine the PSU version installed, use OPATCH utility:
OPATCH lsinv -bugs_fixed | grep -i PSU
To find from the database:
Select substr(action_time,1,30) action_time, substr(id,1,10) id, substr(action,1,10) action,substr(version,1,8) version, substr(BUNDLE_SERIES,1,6) bundle, substr(comments,1,20) comments from registry$history;
Note: You can find the details from the above query if you already executed the catbundle.sql
Click to Check Existing Oracle Database Patch Status
Will Patch Application affect System Performance?
Sometimes applying certain patch could affect Application performance of SQL statements. Thus it is recommended to collect a set of performance statistics that can serve as a baseline before we make any major changes like applying a patch to the system.
Can you stop applying a patch after applying it to a few nodes? What are the possible issues?
Yes, it is possible to stop applying a patch after applying it to a few nodes. There is a prompt that allows you to stop applying the patch. But, Oracle recommends that you do not do this because you cannot apply another patch until the process is restarted and all the nodes are patched or the partially applied patch is rolled back.
How you know impact of patch before applying a patch?
OPATCH <option> -report
You can use the above command to know the impact of the patch before actually applying it.
How can you run patching in scripted mode?
opatch <option> -silent
You can use the above command to run the patches in scripted mode.
Can you use OPATCH 10.2 to apply 10.1 patches?
No, Opatch 10.2 is not backward compatible. You can use Opatch 10.2 only to apply 10.2 patches.
What you will do if you lost or corrupted your Central Inventory?
In that case when you lost or corrupted your Central Inventory and your ORACLE_HOME is safe, you just need to execute the command with –attachHomeflag, OUI automatically setup the Central Inventory for attached home.
What you will do if you lost your Oracle home inventory (comps.xml)?
Oracle recommended backup your ORACLE_HOME before applying any patchset. In that case either you can restore your ORACLE_HOME from the backup or perform the identical installation of the ORACLE_HOME.
When I apply a patchset or an interim patch in RAC, the patch is not propagated to some of my nodes. What do I do in that case?
In a RAC environment, the inventory contains a list of nodes associated with an Oracle home. It is important that during the application of a patchset or an interim patch, the inventory is correctly populated with the list of nodes. If the inventory is not correctly populated with values, the patch is propagated only to some of the nodes in the cluster.
OUI allows you to update the inventory.xml with the nodes available in the cluster using the -updateNodeList flag in Oracle Universal Installer.
When I apply a patch, getting the following errors:
"Opatch Session cannot load inventory for the given Oracle Home <Home_Location> Possible causes are: No read or write permission to ORACLE_HOME/.patch_storage; Central Inventory is locked by another OUI instance; No read permission to Central Inventory; The lock file exists in ORACLE_HOME/.patch_storage; The Oracle Home does not exist in Central Inventory". What do I do?
This error may occur because of any one or more of the following reasons:
– The ORACLE_HOME/.patch_storage may not have read/write permissions. Ensure that you give read/write permissions to this folder and apply the patch again.
– There may be another OUI instance running. Stop it and try applying the patch again.
– The Central Inventory may not have read permission. Ensure that you have given read permission to the Central Inventory and apply the patch again.
– The ORACLE_HOME/.patch_storage directory might be locked. If this directory is locked, you will find a file named patch_locked inside this directory. This may be due to a previously failed installation of a patch. To remove the lock, restore the Oracle home and remove thepatch_locked file from the ORACLE_HOME/.patch_storage directory.
– The Oracle home may not be present in the Central Inventory. This may be due to a corrupted or lost inventory or the inventory may not be registered in the Central Inventory.
We should check for the latest security patches on the Oracle metalink website http://metalink.oracle.com/ and we can find the regular security alert at the location http://technet.oracle.com/deploy/security/alert.htm
Caution: It is not advisable to apply the patches directly into the production server. The ideal solution is to apply or test the patches in test server before being moved into the production system.
Why drop table is not going into Recycle bin?
If you are using SYS user to drop any table then user’s object will not go to the recyclebin as there is no recyclebin for SYSTEM tablespace, even we have already SET recycle bin parameter TRUE.
Select * from v$parameter where name = 'recyclebin';
Show parameter recyclebin;
How to recover password in oracle 10g?
You can query with the table user_history$. The password history is store in this table.
How to detect inactive session to kill automatically?
You can use the SQLNET.EXPIRE_TIME for the dead connections (for abnormal disconnections) by specifying a time interval in minute to send a problem message that verify client/server connections are active. Setting the value greater than 0 to this parameter ensures that connection is not left open indefinitely, due to abnormal client termination. If probe finds a terminated connection, or connection that is no longer in use, it returns an error, causing the server process to exit.
SQLNET.EXPIRE_TIME=10
Why we need CASCADE option with DROP USER command whenever dropping a user and why "DROP USER" commands fails when we don't use it?
If a user having any object then ‘YES’ in that case you are not able to drop that user without using CASCADE option. The DROP USER with CASCADE option command drops user along with its all associated objects. Remember it is a DDL command after the execution of this command rollback cannot be performed.
Can you suggest the best steps to refresh a Database?
Refreshing the database is nothing but applying the change on one database (PROD) to another (Test). You can use import/export and RMAN method for this purpose.
Import/Export Method: If you database is small and if you need to refresh particular schema only then it is always better to use this method.
1. Export the dump file from source DB
2. Drop and recreate Test environment User.
3. Import the dump to destination DB.
RMAN Method: Now days RMAN is most likely to be used for backup and recovery. It is relatively easier and better method for full database refresh to be refreshed. It is taking less time as compare to import/export method. Here also you can use particular SCN based refreshing.
#!/usr/bin/ksh
export ORAENV_ASK='NO'
export ORACLE_SID=PRD
/usr/local/bin/oraenv
export NLS_LANG=American_america.us7ascii;
export NLS_DATE_FORMAT="Mon DD YYYY HH24:MI:SS";
$ORACLE_HOME/bin/rman target / nocatalog log=/tmp/duplicate_tape_TEST.log connect auxiliary sys/PASSWORD@TEST;
run
{
allocate auxiliary channel aux1 device type disk;
set until SCN 42612597059;
duplicate target database to "TEST" pfile='/u01/app/xxxx/product/10.2.0/db_1/dbs/initTEST.ora' NOFILENAMECHECK;
}
EOF
\
How will we know the IP address of our system in Linux environment?
Either use ipconfig command or ip addr show
It will give you all IP address and if you have oracle 9i you can query from SQL prompt.
SELECT UTL_INADDR.GET_HOST_ADDRESS "Host Address", UTL_INADDR.GET_HOST_NAME "Host Name" FROM DUAL;
Can we create Bigfile Tablespace for all databases?
Infact your question do we create bigfile tablespace for every database is not clear for me. If you are asking can we create bigfile for every database?
Yes you can but it is not ideal for every datafile if your work is suitable for small file then why you create bigfile but if your mean is impact of bigfile that depends on your requirements and storage.
A bigfile tablespace is having single very big datafile which can store 4GB to 128 TB.
Creating single large datafile reducing the requirement of SGA and also it will allow you modification at tablespace level. In fact it is ideal for ASM, logical device supporting stripping.
Avoid using bigfile tablespace where there is limited space availability. For more details impact, advantage, disadvantage of bigfile on my blog.
Can you gice more explanation on logfile states?
“CURRENT” state means 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 redo’s 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 redolog group contains no redo records belonging to a dirty buffer it is in an "INACTIVE" state. These inactive redolog 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.
What is difference between oracle SID and Oracle service name?
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.
How to find session for Remote users?
-- To return session id on remote session:
SELECT distinct sid FROM v$mystat;
-- Return session id of you in remote Environment:
Select sid from v$mystat@remot_db where rownum=1;
We have a complete cold Backup taken on Sunday. The database crashed on Wednesday. None of the database files are available. The only files we have are the taped backup archive files till Wednesday. Is there a possibility of recovering the database until the recent archive which we have in the tape using the cold backup.
Yes, if you have all the archive logs since the cold backup then you can recover to your last log
Steps:
1) Restore all backup datafiles, and controlfile. Also restore the password file and init.ora if you lost those too. Don't restore your redo logs if you backed them up.
2) Make sure that ORACLE_SID is set to the database you want to recover
3) startup mount;
4) Recover database using backup controlfile;
At this point Oracle should start applying all your archive logs, assuming that they're in log_archive_dest
5) alter database open resetlogs;
How to check RMAN version in oracle?
If you want to check RMAN catalog version then use the below query from SQL*plus
SQL> Select * from rcver;
If you want to check simply database version.
SQL> Select * from v$version;
What is the minimum size of Temporary Tablespace?
1041 KB
Difference b/w image copies and backup sets?
An image copy is identical, byte by byte, to the original datafile, control file, or archived redo log file. RMAN can write blocks from many files into the same backup set but can’t do so in the case of an image copy.
An RMAN image copy and a copy you make with an operating system copy command such as dd (which makes image copies) are identical. Since RMAN image copies are identical to copies made with operating system copy commands, you may use user-made image copies for an RMAN restore and recovery operation after first making the copies “known” to RMAN by using the catalog command.
You can make image copies only on disk but not on a tape device. "backup as copy database;" Therefore, you can use the backup as copy option only for disk backups, and the backup as backupset option is the only option you have for making tape backups.
How can we see the C:\ drive free space capacity from SQL?
create an external table to read data from a file that will be as below
create BAT file free.bat as
@setlocal enableextensions enable delayedexpansion
@echo off
for /f "tokens=3" %%a in ('dir c:\') do (
set bytesfree=%%a
)
set bytesfree=%bytesfree:,=%
echo %bytesfree%
endlocal && set bytesfree=%bytesfree%
You can create a schedular to run the above free.bat, free_space.txt inside the oracle directory.
Differentiate between Tuning Advisor and Access Advisor?
The tuning Advisor:
– It suggests indexes that might be very useful.
– It suggests query rewrites.
– It suggests SQL profile
The Access Advisor:
– It suggest indexes that may be useful
– Suggestion about materialized view.
– Suggestion about table partitions also in latest version of oracle.
How to give Access of particular table for particular user?
GRANT SELECT (EMPLOYEE_NUMBER), UPDATE (AMOUNT) ON HRMS.PAY_PAYMENT_MASTER TO SHAHID;
The Below command checks the SELECT privilege on the table PAY_PAYMENT_MASTER on the HRMS schema (if connected user is different than the schema)
SELECT PRIVILEGE
FROM ALL_TAB_PRIVS_RECD
WHERE PRIVILEGE = 'SELECT'
AND TABLE_NAME = 'PAY_PAYMENT_MASTER'
AND OWNER = 'HRMS'
UNION ALL
SELECT PRIVILEGE
FROM SESSION_PRIVS
WHERE PRIVILEGE = 'SELECT ANY TABLE';
What are the problem and complexities if we use SQL Tuning Advisor and Access Advisor together?
I think both the tools are useful for resolving SQL tuning issues. SQL Tuning Advisor seems to be doing logical optimization mainly by checking your SQL structure and statistics and the SQL Access Advisor does suggest good data access paths, that is mainly work which can be done better on disk.
Both SQL Tuning Advisor and SQL Access Advisor tools are quite powerful as they can source the SQL they will tune automatically from multiple different sources, including SQL cache, AWR, SQL tuning Sets and user defined workloads.
Related with the argument complexity and problem of using these tools or how you can use these tools together better to check oracle documentation.
No comments:
Post a Comment