No warranty may be created or extended by sales representatives or written sales materials. The accuracy and completeness of the information proceed herein and the opinions stated herein are not guaranteed or warranted to produce any particular results and the advice and strategies contained herein may not be suitable for every individual. Neither Kendba Services nor author shall be liable for any loss of profit or any other commercial damages, including but not limited to special, incidental, consequential, or other damages.
Without their constant and noble love, guidance, sacrifices and support, I would not have achieved the goal of completing this book. He has more then 15 years of experience in the Information Technology industry.
Gitesh has been involved with Oracle right from Oracle7 to its latest version Oracle11g. Gitesh has also worked as a freelance DBA for several companies and as a corporate faculty in many reputed training institutes. He has extensive experience managing very large Oracle databases in banking and finance domains.
Gitesh has helped several clients design and implement high availability and disaster management strategies. He holds Brainbench certification for Oracle 10g, 9i, 8i and certification in Unix system administration.
He has also provided Oracle recruitment services to various companies. Gitesh has been awarded best support provider and best team leader several times during his professional career. His team was awarded Best DBA team of the year in Preface These days we are using computer systems for various tasks, cars for getting around, and cellular phone devices for communication.
In the same way, this publication was not published instantaneously. The voyage of this book goes many years in the past with many individuals to be thanked for. To start with, we want to thank all the hiring managers who decline interviewees by saying they need to know Oracle DBA fundamentals regardless of quality of their experience and high score in OCP exams.
Employers typically offer jobs to those who possess a solid understanding of Oracle fundamentals. It is recorded including those corresponding variety into a redo approach. Answer: An Oracle database is an accumulation of information treated as a unit. The motivation behind a database is to store and recover related data. Answer: You can start up a database with three devices. Prophet Enterprise Manager: This is another method for beginning up a database. You can sign in to Oracle Enterprise Manager regardless of whether the database is halted.
You can start up the database by clicking this catch. RMAN: This is somewhat a less utilized instrument for beginning up a database yet it is conceivable to fire up a database from the Recovery Manager direction line.
Answer: Prophet utilizes Tablespace for consistent information Storage. Physically, the information will get put away in Data documents. Information records will be associated with the tablespace. Table space can have numerous information documents. It contains read-just information word reference tables which contain data about the database. Answer: A checkpoint plays out the accompanying three tasks: Every filthy square in the cushion reserve is kept in touch with the information records.
That is, it synchronizes the information hinders in the support reserve with the datafiles on circle. Answer: Prophet home stock or neighborhood stock is available inside every Oracle home. It just contains data significant to a specific Oracle home. Answer: A database object is any characterized item in a database that is utilized to store or reference information.
A few instances of database objects incorporate tables, sees, bunches, arrangements, files, and equivalent words. Answer: The items accordingly can be put away in the database. The language of the DBMS can be coordinated with an item arranged programming language. The language may even be equivalent to that utilized in the application, which does not constrain the software engineer to have two portrayals of his items.
Answer: An information square is the littlest unit of intelligent stockpiling for a database object. As items develop they take pieces of extra stockpiling that are made out of touching information squares. These groupings of coterminous information squares are called degrees. Every one of the degrees that an article takes when gathered are viewed as the portion of the database object. Answer: When you are running committed server at that point procedure data put away inside the procedure worldwide region PGA and when you are utilizing shared server then the procedure data put away inside the client worldwide region UGA.
A Data Guard ensures data protection and high availability of data, and a control file records the physical structure of the database. An index is an optional structure associated with a table to have direct access to rows that can be created to increase the performance of data retrieval. An index can be created on one or more columns of a table. Indexes are used for the easy access of data. To increase the performance of retrieval data, Indexes are used. It helps to find the required data quickly without actually searching each row.
Master Most in Demand Skills Now! Oracle is capable of maintaining and using indexes and Oracle automatically distributes the data into relevant indexes when any change is made in the table data. A DBA has the authority to create new users, remove the existing users, or modify any of the environment variables or privileges assigned to other users. Enroll now! A synonym which is also known as an alias is an identifier that can be used to reference another database object in a SQL statement.
A table, view, sequence are the types of database objects that can be created for a synonym. A public synonym is one that can be used by any database user. A private synonym does belong to a specific schema.
In other words, when only the owner can access it, it is called a private synonym. Hot backup is also recognized as an Online Backup because here the backup is taken while the database is active and running. And when the backup can only happen while the database is in shut down mode then it is called Cold Backup which can also be identified as Offline Backup.
The user is given database access only if the username and password match. The data dictionary is stored in the database and can be accessed as long as the database is available. The dictionary also contains the passwords for the administrators. The data dictionary would be unavailable until the database is locked. A password file is an operating system file that is held on a separate disc from the database.
And when the database is down, administrators with certain privileges are authenticated using the password files. All the tables are stored in data files. The data file has all the data stored. The data files hold all the database data. The data of relational database structures, such as tables and indexes, is bodily stored in the data files owed for a database. To switch from an init. The process of arranging data in a database is known as normalization. This involves building tables and defining relationships between them according to rules designed to protect data while also allowing the database to be more flexible by removing redundancy and conflicting dependencies.
A binary file that records the physical structure of the database and is required to start and run the database. If a failure stops personalised data from being permanently written to data files, corrections may be made from the redo log, ensuring that the work is never wasted. A parameter file is a text file that includes a list of initialization parameters as well as their values.
Initialization parameters are defined in a parameter file that is exclusive to your installation. The recovery catalog is a database scheme that contains the metadata that RMAN manages for data restoration and recovery processes.
The metadata used by RMAN for reinstallation and healing process is stored in a recovery catalog. The RMAN metadata is still present in the recovery catalogue even if the target control file and all backups are lost. Why or why not? 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? A user is getting an ORA error yet you know you have granted them permission on the table, what else should you check?
You need to verify the developer has direct grants on all tables used in the view. If you have an example table, what is the best way to get sizing data for the production table implementation? 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?
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? 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?
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? This is created using the utlxplan.
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? This option can be shortened to TERM. How do you prevent Oracle from giving you informational messages during and after a SQL statement execution?
A tablespace has a table with 30 extents in it. Is this bad? Why or why not. However if you also have chained rows this can hurt performance. How do you set up tablespaces during an Oracle installation? Poor data dictionary or library cache hit ratios, getting error ORA Another indication is steadily decreasing performance with all other tuning parameters the same.
Oracle almost always reads in 64k chunks. The two should have a product equal to 64 or a multiple of 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. When should you increase copy latches? What parameters control copy latches? 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. 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.
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. How can you find what is causing it? Buffer busy waits could indicate contention in redo, rollback or data blocks. If you see contention for library caches how you can fix it? Increase the size of the shared pool. 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? In version 7.
0コメント