Logical and Physical Structures of the Database - Assignment Example

SOCIALLY INNOVATIVE COMMERCE By CHAPTER 1. Explain one way through which a database user can connect to an Oracle server? The database user logs into the OS (operating system) that runs the Oracle instance. This will start the application or tool that will then have an access to the database on the system. The communication pathway is created through the inter-process communication mechanisms which exists on the host OS. 2. Name the database structures. Why is it possible to manage the two structures separately? The database constitutes both logical and physical structures. Since the logical and physical structures are separate, the data physical storage can be managed without necessarily affecting logical storage structure access. 3. Is it possible to share database instances? Explain. Every database instance is connected to the only existing one b=database. In case there are numerous databases that exists on the very same server, then there is a distinct and separate database instance for every database. It is not possible or recommendable to share a database instance. 4. What is the difference between connection and session in a database instance? Connection in a database instance is the communication that occurs between the instance and the user process. Session in a database instance, on the other hand, is the specific connection of a database user to an instance through the database user process that it stands for in the current user login to the instance. 5. What are some of the reasons why database creates and uses memory structures? Give examples. The Oracle database develops databases for different purposes. For example, when running a memory stores program code, the private and shared data among the users for every connected user. 6. How does a cache hit and a cache miss affect a first time Database use? Whenever an Oracle Database user runs the process for the first time, it calls for a specific piece of data. The Oracle searches for the data from the database buffer cache. The search process can either find the data or not find it. If the data is found, it is referred to as a cache hit, and when it misses the data it is called a cache miss. When it is a cache hit, the data is read from the memory directly. If it is a cache miss, the data must be copied from a data file on the disk then into a buffer in the cache before the data is accessed freshly. 7. How does Oracle Database system accommodate many users? To accommodate several users and to maximize performance, an Oracle Database multi-process system uses some extra Oracle Database process named background processes. There are numerous background processes in an Oracle Database instance. 8. If your system modifies data heavily, what should a user do to improve write performance? The Database Writer Process, abbreviated as DBWn, writes the buffer contents to the data files. The DBWn are tasked with writing the modified buffers in the database buffer to the system disk. Even through a single database is sufficient for most systems (DBW0), additional processes can be configured to enhance write performance if the system heavily modifies the data ((DBW1, DBW9, DBWa and through to DBWz). 9. Differentiate between data files and control files The control files constitute the database data (physical database information). The files are very important to the database, without them, the data files cannot be opened. The information can also contain the data dictionary and the metadata. Data files, on the hand, constitute application or user data of the database. They also contain data dictionary and the metadata. 10. How does Oracle database configuration allow different users to connect through a network from different computers on associated servers The server, through its process, receives the statement and then checks the shared pool through the SGA component if there is any SQL area containing SQL statement. If the search finds a shared SQL area, the server process looks for the specific user access privileges against the requested data. The existing shared SQL area is employed in processing the statement. In case a shared SQL is not found in the search, a new shared SQL area is created for the statement for the purpose of parsing and processing. CHAPTER 2 1. What are the tasks of an Oracle database administrator? A Database Administrator, abbreviated as DBA, is responsible for creating the database and installing the Oracle software. The DBA may also take on creation of the database storage structures like Tablespaces. Additionally, a DBA may have a role to create set of objects or the schema to hold application data. The DBA also ensures that the database is secure and available to all the users. The administrator accomplishes these tasks by ensuring that the database is started, backed up regularly and monitors the database performance. All these tasks must be done I line to the security strategy. 2. Which tools are used for installing and upgrading an Oracle database? The Oracle universal Installer (OUI) and the BDCA, Database Configuration Assistant (DBCA) are the primary tools required for installing and upgrading an Oracle Database. OUI installs the software while the DBCA creates a database from the Oracle. 3. What are some of the things you need to consider when planning your installation? When planning an installation, the database administrator must as a number of questions and provide their answers accordingly: a. Which Oracle software will be used? b. Does the hardware under use meet the minimum requirements? c. Is there installation recommended order when several products are involved? d. Will an external expertise be required during the process besides the DBA? 4. What are the minimum memory and operating system requirement? 1 GB of computer Ram is sufficient for a standard database installation. For the database instance, 1 GB of memory is enough for Oracle Enterprise Manager Database Control. 5. What is Oracle Universal Installer? The OUI (Oracle Universal Installer) is Java software that does component-based installations and permits different levels of integrated suite, bundle, and web-based installations. OUI also enables complex logic in one package. 6. OUI offers different kinds of capabilities solving for software management and distribution. Give examples of some of these capabilities. a. Web installation b. Implicit deinstallation c. Complex logic handling and automatic dependency resolution d. Suite and component installation e. Silent, unattended installations that require response files f. Distributed installations support g. Globalization or NLS support h. Multiple Oracle homes support 7. During the installation process, what happens if the “perform prerequisite checks” page pops out? During the installation of the Oracle software, the Perform Prerequisite Check page pops out. There should be no operation to be done on the page except an error occurs. In case there is an error on the page, there are two options to choose from; "Ignore All" and continue or manually fix the problem then continue with the installation. 8. Briefly explain how to install and configure OUI with Oracle products in a silent mode. First, prepare the response file by through one of the file templates that came with the Oracle software. Then record the response file during the process of installation using OUI. This is accomplished by clicking “Save Response File” that is on the summary page. Run OUI in silent or suppressed mode. The following files are run ./runInstaller – silent -- responsefile .ora or c:\setup – silent -- responsefile .ora In case it is required, run (in silent mode) NetCA and the DBCA. 9. Explain how to prepare a response file during Oracle products installation and configuration A response file can be prepared through one of the file templates that came with the product, for example netca.rsp, standard.rsp and enterprise.rsp. If these are not suitable for a given situation, then it is recommendable to use OUI to record a responsive file (in an interactive mode) that can be edited then adopted to complete the suppressed or silent mode installations. 10. When you provided with following items, what comes to your mind as an Oracle Database administrator; ORACLE_SID, NLS_LANG, and ORACLE_HOME? ORACLE_SID, NLS_LANG, and ORACLE_HOME are environment variables. There are several Oracle environment variables which are used in every Oracle environment. The mentioned variables help in ensuring that the Oracle Databases are installed and used successfully. CHAPTER 3 1. Why is it important for a DBA to plan a database? It I very important to make a prior plan how the database logical storage structure will affect the numerous database management operations and the system performance. For example, before creating any database for the new tablespace, one should understand the number of data files that will make up the tablespace, and the kind of the information that will be stored in every tablespace. It is also important to know the disk drives where the data files will be stored physically. 2. How can you improve database efficiency? To improve database efficiency, develop a backup strategy during the planning stage of the database. The design or logical storage structure can be altered to improve the backup efficiency. 3. Classify and give practical examples of databases a. Transaction processing or general purpose database: i. OLTP - Online transaction processing system, for example, Billing system for a nursery software house. b. Custom: i. Multipurpose database (maybe a combination of data warehouse functionality and OLTP) c. Data warehouse: i. Marketing and Research data ii. Federal or State tax payments iii. Professional licensing (teachers, doctors, nurses, etc.) 4. Why is it neccessary to go with or choose the correct character set that perfectly corresponds to your business needs? Explain. The Oracle database uses three main encoding schemes classes: Varying-width Multibyte, Single-byte and Universal. It is ideal to choose the right data set that meets the needs of every business both today and in the future since it can be very difficult to change them much later on. 5. What is the way forward or best practice in choosing the correct database character set? For one to choose the right database character sets, there is need to properly evaluate the future and current business needs. Generally, Oracle recommends that administrators use Unicode for all the newly created databases since it I the most flexible and suitable character set and also avoids future problems of conversion. 6. During database creation, what does it mean when no conversion takes place? Explain When no conversion occurs when creating a database it means that invalid data must have been fed/ entered into the database. The invalid data usually gains its way into the database when the NLS_LANG parameter is not properly set on the client. However, when the NSL_LANG is professionally and properly set, then the database automatically convert the newly incoming data from the client OS. When the NLS_LANG is not properly set, the incoming database converts poorly. 7. Why would you require a database configuration assistant (DBCA)? The DBCA, or in full the Database Configuration Assistant provides numerous choices to help the user during different operations. The DBCA can be adopted/ used to create a database, develop a configuration and even delete the database. A database can also comfortable created through using an existing database as a sample/ template or create one from a list of predetermined templates. The DBCA gives a variety of options to allow one to create a database to meet the specific user needs. The DBCA also provides a variety of pages where the user can enter custom configuration information. And most of those pages the DBCA offer default information and setting that the user can accept as required. 8. Why database design template is also called a predefined database? Explain three ways to create a database design template. A template is a database that has already been determined in its definition that users adopts as their starting point/ origin when creating a new database. Here are three different methods of creating a new database: Creating a database from an already existing database (structure only), or an existing database with both structure and data, or from an existing template. 9. Explain how to delete a database using DBCA The following are the steps involved in deleting a database through DBCA: i. Navigate to operations page and then select “Delete a Database,” ii. Click next iii. Select the database to be deleted, click finish “Finish” iv. Click “Yes” to confirm the deletion After deleting the database, the interface will ask for the next action to be taken, click appropriately. CHAPTER 4 1. What is the primary Oracle database management model? a. The database instance under managed b. A listener that permits database connections The management interface is either a stand-alone OEMDC (Oracle Enterprise Manager Database Control) or the management agent that operates on the node where the database server operates, also called Database Console. 2. What are some of the factors you want to consider before using a Database Control? Give the commands to start and stop dbconsole processes. every database that the Database Control manages has a separate Database Control Installation. Note that only one database control can be managed at any time from one Database Control. Here are the commands to start and stop dbconsole process: Starting the dbconsole process command: emctl start dbconsole Stopping the dbconsole process command: emctl stop dbconsole 3. When creating the first database in a machine, what is the default port number for accessing Enterprise Manager Database Control? And in case there are several databases on the same host, how do you determine the port number? For every new database in a machine, the default port used for accessing EMDC is always 1158. However, it is possible to have several of these numbers, especially when a user has several databases to manage on the same host. Check the portlist.ini file to determine the port number. The mentioned portlist.ini file is situated/ located in the $ORACLE_HOME/install folder/ directory. 4. What is the importance of SQL Plus as an Oracle tool? SQL*Plus gives access to an additional interface to the personal database so that the user can perform database management functions. For example, execute SQL commands to insert, query, delete data and update data in the database. 5. Give examples of some of the database management operations that can be performed using the SQL Developer and SQL Plus tools? These tools allow the users to perform several database management functions as well as update, delete, insert, or select data from the database. 6. There are two types/ classification of parameter files, the server parameter file and the text initialization parameter file. What are the default names for these files? Can the server edit on these files? Explain The server parameter file abbreviated as SPF and the initialization parameter files abbreviated as IPF are the two types of parameter files. The server parameter files s a binary file that a database server can read and write on and must not be manually edited. It exists on the server on which the Oracle instance gets executed; persistent across startup and shutdown. The default name for server parameter file is spfile.ora SQL> Startup spfile=‘c:\spfileorcl.ora’; Text initialization parameter file, on the other hand, can be read by the database server. The server cannot however write on the file. The initialization parameter settings must also be changed and set manually though text editor to make them persistent through startup and shutdown. The default name for text initialization parameter file is init.ora (it is sought at startup automatically if an SPFILE is not identified). Oracle recommends that one creates an SPFILE as a dynamic approach to keeping the initialization parameter. 7. Explain two examples of initialization parameters in managing database instance CONTROL_FILES parameter: the parameter specifies a single or more control file names. Oracle recommends that the user mirror and multiplex control files from 1 to 8 file names. The default value should be operating system independent. DB_FILES parameter: the parameter suggests the maximum number of database files that is possible to open for this specific database. Range of values should be operating system dependent. The default value is 200. 8. Why is it that when performing certain maintenance operation it is required that one does not open the database after starting the instance and mounting a database? A database can remain in a Mount mode even after an Open request is made. The reason for this may be due to the database need to be recovered in some manner. If the recovery is done while the database is in the mount mode, the redo logs become open for the reads and the data files also get opened and read the blocks that require recovery. The block are written if need be during recovery. 9. When performing a maintenance operation, give instances when one must not open the databases The databases must not be opened while renaming the files. The databases must also not be opened when disabling or enabling online redo log file archiving options, performing full database recovery. CHAPTER 5 1. What are the requirements by Oracle Net to make a client connection? a. Host for running the listener b. Port for monitoring the listener c. The protocol for running the listener d. The service name that the listener is handling 2. What are the requirements for an application to connect to a service (establishing net connection) through an Oracle Net Listener For a connection to be created or established between the Oracle Net Listener and the service, the software must have data or information about the service This includes the host or address where the listener resides, the protocol that is accepted by the listener and the port monitored by the listener. After the location of the listener, the final information that the application requires is the service name to which it needs to connect. 3. What is a server process in configuring the Oracle Net Environment? In case the CONNECT packet called for a valid and usable service name, the listener creates a new process to create the connection. The new process is called server process. The listener then makes a connection with the process and originates the initialization information, including user address, etc. 4. Name the tools for managing and configuring the Oracle Network a. Enterprise Manager Net Services Administration (EMNSA) page b. The Oracle Net Manager (ONM) c. The Oracle Net Configuration Assistant (ONCA) d. The Command line 5. Give two primary listener commands managed by the Oracle Restart through SRVCTL? a. To display assistance with the commands found in SRVCTL: srvctl –h b. To begin the default listener: i.e., srvctl start the listener 6. What is the purpose of the net service administration page? The NSA page allows the user to configure the Oracle Net Service across several file systems in any Oracle home. It also gives common administration functionalities for the listeners. 7. Give some examples of Oracle Net naming methods a. The easy connect naming: Adopts a TCP/IP connect string b. The local naming: adopts a local configuration file c. The directory naming: adopts a centralized 8. In Oracle Net, easy connect, what happens if the listener port is not given and the service name? In case the listener port is not given, the Oracle Net will assume that the default port under use is 1521. In case the service name is not given, then the Oracle Net will assume that the hostname and the database service name in the connect string are similar. 9. What are the benefits of local naming and directory naming? Local naming advantage is that the users of the database do not have to remember long connect string but only a short alias. The directory naming advantage is that the users can find service name to make a connection as soon as a new (not used before) service name is included to the directory (LDAP). 10. What is tnsping? tnsping is performs the same role as TCP/IP ping utility in the Oracle Net. For instance, the command tnsping can be entered in the window command-line. CHAPTER 6 1. What are the database blocks content in database storage structures? They are raw data, block header and free space. 2. Give some examples of the events that causes header block. a. Row directories that require more row entries b. More transaction slots needed than initially configured c. Originally, the free space in a block becomes contiguous. However, updates and deletions may fragment the free space. 3. Give two ways through one can alter a tablespace a. The Renaming: Enter a new/ unused name for the tablespace and then afterward click Apply. b. The Read Write: The tablespace is online and can be written to and read from. 4. Which are some of the actions that can be performed through a tablespace? Some of the actions that can be executed through the tablespace include adding datafile, creating like, generating DDL, making locally managed tablespace, creating read-only, and making it writable tablespaces. 5. When can a tablespace be dropped? A user can decide to drop the user space intentionally together with its content from the database if the tablespace and all it content are no longer required. To drop a tablespace, the user must have the DROP TABLESPACE system privilege. 6. In which ways can a database be enlarged? a. Creating a new Tablespace b. Adding one or more data file to an already existing smallfile Tablespace c. Increasing a datafile size d. Allowing for the dynamic data file growth 7. How can extent allocation be allocated? Automatic: Also referred to as auto allocate, it specifies that the extent in the Tablespace sizes are system managed. Uniform: It specifies that the Tablespace (uniform) is managed through the uniform extents of a size that the user specifies. The default uniform size is 1 MB. 8. How is a segment space management in a locally managed tablespace specified? Automatic: The Oracle database adopts the bitmaps to manage the free space in the different segments. The bitmap describes every data block in a segment in line to the amount of space in the block that is used for inserting rows. 9. When is a SYSAUX tablespace used? This is an auxiliary tablespace dedicated to the SYSTEM tablespace. A number of products and components that adopted the SYSTEM tablespace or their own tablespaces in original releases of the database (Oracle) now implement the SYSAUX tablespace. Every Oracle Database 10g database is mandated to have a SYSAUX tablespace. 10. When is SYSTEM tablespace used? The SYSTEM tablespace is adopted by the Oracle server as a way of managing the database. It constitutes the tables and data dictionary that contain administrative information (data) about the database. These can all be found in the SYS schema and can also be accessed only through other administrative users or the SYS user with the required privilege. CHAPTER 7 1. What are the responsibilities of data recovery advisor? a. Fast detection, repair of failures, and analysis, b. Minimizing users disruptions c. Run-time and Downtime failures 2. How are data failures detected? Explain The data failures can be detected through checks, these checks are basically diagnostic procedures that assess database components or database health. Every check can be used to diagnose a single failure or more which are then mapped to a repair. 3. What happens in case an error takes place in the database during reactive checks? Whenever an error happens in the database, an automatic execution referred to as “reactive check” occurs. The “proactive checks” can also be initiated, for instance, through executing the command VALIDATE DATABASE. 4. What are some examples of data failure? a. Non-accessible elements, for example: Lost data files at the Operating System level b. Physical corruptions, for instance, invalid block header field values or block checksum failures c. Logical corruptions, for instance, corrupt row piece, inconsistent dictionary, corrupt transaction or corrupt index entry d. Inconsistencies, 5. How can data recovery advisor obtain the failures? If you know that or suspect that a database failure has taken place, then it is advisable to use the LIST FAILURE command to get the relevant information about the failures. The user can list all the failures or a subset of the failures and restrict/ control output in different approaches. 6. What does a CHANGE FAILURE command do? The command closes one or more failures or changes the priority of failures. A failure priority can only be changed for only LOW or HIGH priority. 7. What happens when a user manually fixes failures? If a user, experienced or non-experienced fixes failures manually, if the transient failures disappear, then the failures should be remove by the Data Recovery Advisors through LIST FAILURE output. 8. Why should the HIGH list failures be repaired very quickly? These HIGH list failures make a database unavailable partially or unrecoverable; so they should be quickly repaired (for instance, missing archived redo logs). 9. What are the functions of the RMAN ADVISE FAILURE command? a. Input failure list display summary b. Constitute a warning, if new failures featured in ADR c. Shows a manual checklist 10. What is block corruption? A corrupted data block can be defined as a block that is not recognized in the format of Oracle or still worse, a block whose content are not consistent internally. The corruptions are mostly caused by operating systems challenges or a faulty hardware. CHAPTER 8 1. What are basic roles of a database administrator? a. Protect or prevent database failure wherever possible b. Increase the mean period between failures (MTBF) c. Use redundancy to protect critical components 2. Explain when a database failure is categorized as a user-process failure and statement failure. The Statement failure: Only one database operation (update, select, insert, or delete) fails. The User-process failure: Only one database session fails. 3. What is the probable solution to a typical problem, “tries to enter wrong or invalid data into a table?” Engage the database users to validate and then correct data. 4. What should a database administrator do when a user does an abnormal disconnect? The user process failures are not always corrected through ADBA’s action. The instance background processes are responsible for rolling back the release blocks and uncommitted changes. 5. What is the long-term solution to systematic and consistent failures? A bigger percentage of abnormal disconnection may show a need for user training. This encompasses training/ teaching the database users to log out instead of terminating their program. The training may also show application or network problems. 6. What is the possible solution to network Interface Card failure? Configure numerous network cards 7. What is the possible solution to Network connection failure? Configure connection for backup network 8. What is the best solution when a user modifies data or inadvertently deleted data? Rollback transaction, rewind table, or dependent transactions 9. What is the best solution when a user drops a table? Recover table from recycle bin 10. What the best solution of database failure due to power outage, hardware failure, or emergency shutdown procedures? Restart the instance through implementing the STARTUP command. The process of recovering from instance failure is always automatic, including rolling forward the available changes in the redo logs and followed by rolling back any available uncommitted transactions. Works Cited Avrom, R. F., Koletzke, P. Dorsey, P.: Oracle JDeveloper 10g Handbook. McGraw-Hill Publishing Company New York, 2004. ISBN 0-07-225583-8. Bauerová, L.: Komunikační dovednosti ve virtuálním prostředí. Studijní materiál pro distanční kurz. ESF projekt OP RLZ – opatření 3.2, CZ.04.03/3.215.0103 Ostrava 2006. Bonus za přizpůsobivost. Computerworld 11, 2008. S.3. E-Learning and Training for the Support … 243 Bryla, B., Loney K.: Oracle Database 11g DBA Handbook. McGraw-Hill Publishing Company New York, 2008. ISBN 978-0-07-149663-6. Cook, S., Harrison, R., Millea, T. & Lily Sun.: Challenges of Highly Adaptable Information Systems. Applied Software Engineering Research Group University of Reading. [on-line], [cit. 6.6.2009]. URL: Loney K., Bryla B.: Oracle Database 10g DBA Handbook. McGraw-Hill Publishing Company New York, 2005. ISBN 0-07-223145-9. Nedbalé databáze prolamují stará pravidla. Computerworld 11, 2009. S. 14. McHugh, J. and Abiteboul, S. and Goldman, R. and Quass, D. and Widom, J. (1997) Lore: A Database Management System for Semistructured Data. SIGMOD Record, 26 (3). pp. 54-66. Read More