M. Sc. (CS) SY Advance Database Administration (CS-301)
Unit I
Database Architecture
1.1 An Overview of Databases & Instances
⮚ What is Database?
• The database is an organized collection of related information. A database is a set of data, which is stored in format of tables that are further defined by their columns and are given a name. Data is stored in the table, and can be related to each other. Oracle is called RDBMS, because it provides the ability to store and access data in a consistent manner with a defined model.
⮚ What is Tablespace?
• A tablespace is a logical mandatory division of database that can belong to only one database. SYSTEM tablespace is one of the default tablespace. We can use additional tablespaces like USERS, UNDO, DATA, and TOOLS etc. to group users or application together for easy maintenance & good performance.
⮚ What are Datafies?
• Every tablespace is constituted of one or more physical files, called datafiles. A datafile can belong to only one tablespace. Creating new tablespaces requires creating new datafiles. Datafiles can be resized after their creation. Once a datafile has been added to a tablespace, the datafile cannot be removed from the tablespace.
1 |
M. Sc. (CS) SY Advance Database Administration (CS-301)
1.2 Logical Structure of the Database
• A Tablespace is a logical database structure that is designed to store other logical database structure. Oracle sees/views tablespace as a large area of space into which Oracle can place new objects. Space in tablespace is allocated in segments. A segment is an allocation of space used to store the data of a table, index, undo segment, or temporary object.
• When the database object runs out of space in its segment & needs to add more data, Oracle lets it allocate more space in the form of an extent. An extent is similar to a segment in that the extent stores information corresponding to a table, index, undo segment, or temporary objects.
• Underlying the logical storage (Segment & Extent), there is a physical storage structure that the host system uses to store data, & the cornerstone of which is the block. Segments & extents are composed of data blocks, & in turn, the blocks are taken together to comprise a datafile.
1.3 What is Instance?
• In order to access the data in the database, ORACLE uses a set of background processes that are shared by all users. In addition, there are Memory Structures (the Data Block Buffer Cache, the Share Pool, the Large Pool, the JAVA Pool) collectively known as System Global Area i.e. SGA. These memory areas help to improve database performance by decreasing the amount of I/O performed against datafiles.
• A database instance is a combination of the set of memory structures and background processes that access a set of database files. The parameters that determine the size & composition of an instance are either stored in an initialization file (pfile/parameter file) called init.ora or server parameter file referred to as the SPFILE & stored in/as spfile.ora.
• The initialization parameter file is read during instance startup & may be modified by the DBA. Any modifications made to the initialization file will not take effect until the next startup.
2 |
M. Sc. (CS) SY Advance Database Administration (CS-301)
• The name of an instance’s initialization file usually includes the name of the instance. For Ex. If the instance is named BSCTY, the initialization file will usually be named initBSCTY.ora
1.4 PFILE & SPFILE
• The PFILE is a text-based file, usually named as “init.ora”. Inside the PFILE are a number of database settings called parameters. These parameters help the Oracle programs know, how to start the database instance. The parameters tell the Oracle programs how much memory to allocate, where to put files related to the database and where certain database files already exist. As the PFILE is text based file, we can edit it in an editor like Notepad on Windows.
• When we change it, it needs to make sure to save the changes to disk before exiting the editor. Also, we have to make sure; it should be saved as a plain text file, since some editors (like MS-Word) can save documents in special formats that Oracle would not be able to read. Depending on operating system PFILE is located, by default in the
ORACLE_HOME\database (usually the case on Windows OS).
• If we are using a PFILE, it takes on the form of initSID.ora, meaning the file will use the ORACLE_SID we define when we create the database. If our SID (System Identifier) is ORCL, the resulting PFILE should be called initORCL.ora. A Sample pfile is as shown below for an instance named msc.
3 |
M. Sc. (CS) SY Advance Database Administration (CS-301)
1.4.1 The Oracle SPFILE
• The SPFILE is different from the PFILE in that it cannot be directly edited. This is because it has a header and footer that contain binary values. Since we cannot change a SPFILE directly, Oracle allows us to manage the SPFILE via the ALTER SYSTEM command. For using an SPFILE, we can gather great benefits. SPFILES allow us to make dynamic changes to parameters that are persistent. For example, the database parameter change was not persistent if we were using PFILES:
• SPFILE uses the same formatting for its file name as the PFILE, except the word spfile replaces init. For instance, if our ORACLE_SID is ORCL, the resulting spfile would be called spfileORCL.ora.
4 |
M. Sc. (CS) SY Advance Database Administration (CS-301)
1.5 Starting & stopping Oracle Instance
• The startup command is used to start the Oracle Instance. The various options used in the startup command are
1.5.1 STARTUP NOMOUNT
• This option starts the instance without mounting the database. This means all the memory structures & background processes are in place, but no database is attached to the instance. This option is used while creating a new database.
• Starting an instance without mounting the database includes the following tasks ⮚ Reading the parameter file init.ora
⮚ Allocating the SGA
⮚ Starting the background processes
⮚ Creating & Opening the alert log files & trace files.
⮚ Note that neither the control files nor the datafiles are opened in this mode. 1.5.2 STARTUP MOUNT
• This option starts the instance, reads the control file, & attaches the database, but it does not open it. We can’t mount a database that hasn’t created yet. This option is useful in situations where we have to move physical database files or when database recovery is required. If the instance is already started but the database is not mounted , use
• In summary, mounting the database includes the following tasks
⮚ Associating a database with a previously started instance.
⮚ Locating & opening the control files specified in the parameter file. ⮚ Reading the control files to obtain the names & status of the datafiles & redo log files.
5 |
M. Sc. (CS) SY Advance Database Administration (CS-301)
1.5.3 STARTUP OPEN
• This option starts the instance, attaches the database, & opens it. This is the default option for starting Oracle. It is used when DBA wants the database to be used by other users. We can’t open a database that hasn’t created yet. If the instance is started & the database is mounted, use
• If we omit the open keyword while issuing the startup command, the startup open is assumed. Opening the database includes the following tasks.
⮚ Opening the online datafiles
⮚ Opening the online redo log files
1.5.4 STARTUP FORCE
• This option forces the instance to start & the database to open. It is used in situations where other startup options are met with errors from Oracle, & no shutdown option seem to work either. This is an option of last resort, & there is no reason to use it generally unless we cannot start the database with any other option. Other cases for database startup include startup recover & startup restrict for opening the database while simultaneously preventing all users but the DBA from accessing database objects 1.5.5 STARTUP RECOVER
• This option starts the database to handle database recovery.
1.5.6 STARTUP RESTRICT
• This option opens the database in restricted mode. Hence the users with restricted privilege (DBA) can only access database objects.
6 |
M. Sc. (CS) SY Advance Database Administration (CS-301)
1.6 Options for stopping Oracle Instance
• The command for shutting down (stopping) Oracle instance is shutdown. There are following four priorities that can be specified by the DBA for shutting down the database.
1.6.1 SHUTDOWN NORMAL
• This is the lowest priority default shutdown. When shutdown normal is issued, Oracle will wait for users to logout before actually shutting down the instance & closing the database. Oracle follows given three rules during shutdown normal.
1. Oracle will not let new users access the database.
2. Oracle will not force users already logged in to the system to logoff in order to complete the shutdown.
3. This is the most graceful (smooth) shutdown of all.
• When a database is shutdown this way, Oracle will not need to do an instance recovery when the instance is restarted again.
1.6.2 SHUTDOWN IMMEDIATE
• This is the highest priority shutdown that the DBA can use when shutdown normal would take too long. The shutdown immediate shuts down a database as follows 1. No new users will be able to connect to the database once the shutdown immediate command is issued.
2. Oracle will not wait for users to log off as it does for shutdown normal, it terminates user connections immediately & rolls back uncommitted transactions. • Oracle follows given three rules during shutdown Immediate.
1. Oracle will not let new users access the database.
2. Oracle will terminate the transactions of currently logged in users in order to complete the shutdown.
3. Oracle Rolls back all uncommitted transaction.
4. Although this option is more critical than shutdown normal, it does not require any media recovery when the instance is restarted again.
7 |
M. Sc. (CS) SY Advance Database Administration (CS-301)
1.6.3 SHUTDOWN ABORT
• This is the highest priority database shutdown command. In all cases where this priority is used, it does following tasks
1. The database will shut down immediately.
2. All users are immediately disconnected.
3. No transactions are rolled back.
• When a database is shut down this way, media recovery will be required when the database starts up again.
• We use this option only when media or disk failure has taken place on the machine hosting Oracle database.
1.6.4 SHUTDOWN TRANSACTIONAL
• A transactional shutdown prevents clients from losing work. A transactional database shutdown proceeds with the following conditions
1. No client can start a new transaction on this particular instance.
2. A client is disconnected when the client ends the transaction that is in progress. 3. A shutdown transactional occurs when all transactions have finished. • When a database is shut down this way, Oracle will not need to do an media recovery when the instance is restarted again.
8 |
M. Sc. (CS) SY Advance Database Administration (CS-301)
1.7 Internal Database Structures
• According to the overview of database & instances, the Oracle database structure is divided among three categories
1. Internal structure of the database
2. Internal Structure of the Memory
3. External Structure of the database
1.7.1 Internal structure of the database
• Once the database is created, we can create internal structures to support applications. The elements that are internal to the database structure include the following. ⮚ Tables, columns, constraints & datatypes (including abstract datatype) ⮚ Partitions & Subpartitions
⮚ Users & Schemas
⮚ Indexes, Clusters, & Hash clusters
⮚ Views
⮚ Sequences
⮚ Procedures, functions, packages, & triggers
⮚ Synonyms
⮚ Privileges & Roles
A) Tables, Columns & Datatypes
• Tables are storage mechanism for data within an Oracle Database. Each column has a name & specific characteristics. A column has a datatype & a length. For columns using the Number datatype, we can specify their additional characteristics of Precision & Scale. Precision determines the number of significant digits in a numeric value. Scale determines the placement of the decimal point.
9 |
M. Sc. (CS) SY Advance Database Administration (CS-301)
B) Constraints:
• We can create constraints on the columns of a table; when a constraint is applied to a table, every row in the table must satisfy the conditions specified in the constraints definition. In the following create table command, an EMPLOYEE table is created with several constraints:
• Type of constraints
10 |
M. Sc. (CS) SY Advance Database Administration (CS-301)
C) Abstract Datatypes:
• As of Oracle8, we can define our own datatype. In the following example, the NAME_TY datatype is created.
• We can use the user-defined datatypes. For Ex. We can use the NAME_TY datatype anywhere. In the following example, the STUD table is created.
D) Partitions:
• As tables grow larger, their maintenance grows more difficult, we may greatly simplify our database administration activities by splitting a large table’s data across multiple smaller tables. These smaller tables are called partitions, are generally simpler to manage than larger tables.
E) Users:
• When a database is created, the SYS & SYSTEM with password CHANGE_ON_INSTALL & MANAGER these two users are created automatically. A User account is not a physical structure in the database; it is important relationship to the objects in the database: users own the database’s objects.
11 |
M. Sc. (CS) SY Advance Database Administration (CS-301)
F) Schemas:
• The set of objects owned by a user account is called user’s schema. G) Indexes:
• For Oracle to find data, each row is labeled with a ROWID. This ROWID tells the database exactly where the row is located. An index is a database structure used by the server to quickly find a row in a table.
H) Clusters:
• Tables that are frequently accessed together may be physically stored together. To store them together, we can create a cluster to hold the tables.
I) Views:
• A view appears to be a table containing columns & is queried in the same manner that a table is queried. However, a view contains no data. Thus, views do not use physical storage to store data. The definition of view is stored in the data dictionary. When we query a view, the view queries the tables i.e. based on & returns the values in the format & order specified by the view definition.
J) Sequences:
• Sequence definitions are stored in the data dictionary. Sequences are used provide a sequential list of unique numbers. The first time a sequence is called by a query, it returns a predetermined value. Each subsequent query against the sequence will yield a value that is increased by its specified increment. Sequences can cycle, or may continue increasing until a specified maximum value is reached.
12 |
M. Sc. (CS) SY Advance Database Administration (CS-301)
• The NOCYCLE option indicates that the sequence cannot generate more values after reaching its maximum or minimum value. To insert the records using sequence use the following query.
K) Synonym:
• To completely identify a database object (such as table or view) in a distributed database, we must specify the host machine, the instance name, the object’s owner, the object’s name.
• Depending on the location of the object, between one & four of these parameters will be needed. To screen this process from the user, developers can create synonyms that point to the proper object; thus, the user only needs to know the synonyms name. L) Privileges & Roles
• In order to access an object owned by another account, the privileges to access that object first have been granted. Typically, non-owners are granted the privilege to insert, select, update or delete rows from a table or view. We can grant privileges to individual users or to PUBLIC, which gives the privileges to all the users in the database. We can create roles – group of privileges – to simplify the privilege management process. We can grant privileges to a role, & grant the role in turn to multiple users.
M) Snapshots:
• A snapshot is a recent copy of a table from database or in some cases, a subset of rows/cols of a table.
13 |
M. Sc. (CS) SY Advance Database Administration (CS-301)
1.7.2 Internal Memory Structure
1.3.3
User
Processes
PMON
Program Global Area
Private
To distributed database
Dnnn
Snnn
System Global Area
Shared Pool
Redo Log Buffer
Buffer Cache
RECO
Archive
Destination ARCH
init .ora
LCKnLGWR CKPT DBWR Buffer
SMON
Redo
Log
Data01.dbf
Data02.dbf
Data03.dbf
Control File
14 |
M. Sc. (CS) SY Advance Database Administration (CS-301)
• There are two basic memory structures in Oracle. The first & most important is the System Global Area (SGA) .The second is Program Global Area (PGA). The Server architecture also consists of Background Processes. The SGA consists of several different items as….
• The Buffer Cache
• The Shared Pool
• The Redo Log Buffer
o The Buffer cache: The Buffer Cache: This memory structure consists of buffers, each of the size of a database block, that store data needed by SQL statements issued by users. We can imagine the buffer cache as a beehive with each unit in it as buffer & all buffers being of equal size. A database block is the most granular unit of information storage in Oracle in which it can place several rows of table data. The Buffer Cache has two purposes :
⮚ To improve performance for subsequent repeated select statements on the same data.
⮚ To enable Oracle users to make changes quickly in memory. Oracle writes those data changes to disk later.
o The Shared Pool: The Oracle Shared pool has two mandatory structures & one optional structure. The first required component is Library Cache, which is used for storing parsed SQL statements text & the statement’s execution plan for reuse. The second is the Dictionary Cache, which is sometimes referred to as the Row cache which is used for storing recently accessed information for the Oracle data dictionary.
o The Redo Log Buffer: The SGA component temporarily stores in memory the redo entry information generated by DML statements run in user sessions until Oracle writes the information to disk. The Redo entry is a small amount of information produced & saved by Oracle to reconstruct or redo, changes made to the database.
15 |
M. Sc. (CS) SY Advance Database Administration (CS-301)
o If some sort of failure occurred, the DBA can use redo information to recover the Oracle database to the point of database failure.
• The Other Memory Structure in the Oracle instance is the Program Global Area (PGA). The PGA helps user processes execute by storing information like bind variable values, sort areas, & other aspects of cursor handling.
• Apart from the System Global Area (SGA) & Program Global Area (PGA). It also consists of background Processes The relationships between the database’s physical & memory structure are maintained & enforced by Background Processes The various Background Processes in the Oracle Architecture are …
Background processes
1. SMON: When we start database, the SMON (System Monitor) process performs instance recovery as needed. It also cleans up the database, eliminating transactional objects that are no longer needed by the system. SMON serves an additional purpose: It Coalesces contiguous free extents into larger free extents.
2. PMON: The PMON (Process Monitor) background process cleans up failed user processes. PMON frees up the resources that the user was using. It effects can be seen when a process holding a lock is killed; PMON is responsible for releasing the lock & making the process available to other users.
3. DBWR: The DBWR (Database Writer) background process is responsible for managing the contents of the data block buffer cache & the dictionary cache. DBWR performs batch writes of changed block from the SGA to the Datafiles. We can have multiple DBWR processes on a single database instance. The number of DBWR processes running is set via the DB_WRITER_PROCESSES parameter in the initialization parameter file. If we create multiple DBWR processes, the processes will not be named DBWR; instead they will have a numeric component. For example if we create five DBWR processes, the OS may name them DBW0,DBW1,DBW2,DBW3 & DBW4
16 |
M. Sc. (CS) SY Advance Database Administration (CS-301)
4. LGWR: The LGWR (Log Writer) background process manages the writing of the contents of the redo log buffer to the online redo log files. LGWR writes log entries to the online redo log files in batches. The redo log buffer entries always contain the most up-to-date status of the database. LGWR is the only process that writes to the online redo log files & the only one that directly reads the redo log buffers during normal database operation.
5. CKPT: Checkpoints help to reduce the amount of time needed to perform instance recovery. Checkpoints cause DBWR to write all of the blocks that have been modified since the last checkpoint to the datafiles. Checkpoint occurs automatically when an online redo log file fills.
6. ARCH: The LGWR background process writes to the online redo log files in a cyclic fashion; after filling the first log file, it begins writing the second, until that one fills, and then begins writing to the third. Once the last online redo log file is filled, LGWR begins to overwrite the contents of the first redo log file. When the Oracle is run in ARCHIVELOG mode, the database makes the copy of each redo log file after it fills. These archived redo log files are usually written to a disk device. The archiving function is performed by the ARCH background process
7. RECO: The RECO background process resolves failures in distributed databases. This process is only created if the Distributed Option is supported on the platform & the DISTRIBUTED_TRANSACTIONS parameter in the initialization file is set to a value greater than zero. The default init.ora file will be created with a nonzero value for DISTRIBUTED_TRANSACTIONS
8. Dnnn: Dispatcher processes are part of the shared server architecture; they help to minimize resource needs by handling multiple connections. At least one dispatcher process must be created for each protocol that is being supported on the database server. Dispatcher processes are created at database startup, based on the initialization parameter DISPATCHERS & can be created or removed while the database is open.
17 |
M. Sc. (CS) SY Advance Database Administration (CS-301)
9. Server (Snnn): Server processes are created to manage connections to the database that require a dedicated server. Server process may perform I/O against the datafiles. The maximum number of server processes is specified by the initialization parameter SHARED_SERVERS.
1.7.3 External Structure
• The following types of files, although related to the database, are separate from the datafiles
1. Redo Log Files
2. Control Files
3. Trace files & Alert log files
Redo Log Files
• Oracle maintains logs of all transactions against the database. These transactions are recorded in files called Online Redo Log Files, & are used to recover the database transactions in proper order when database get crashed.
• Each database will have three or more Online Redo Log Files that are written by Oracle (LGWR Background Process) in a cyclic style i.e. Oracle will write to one log file until it is finished, then it will start writing to the second redo log file. When the last online. When the last online redo log file is filled, the database will begin overwriting the contents of the first redo log file with new transactions.
• When Oracle is run in ARCHIEVELOG, the database makes a copy of each online redo log file after it has filled. These achieved redo log files are usually written to a disk device. The ARCH background process performs the achieving functions. Control Files
• Database creates & maintains Control Files. Control Files record control information about all of the files within the database. Since, Control Files are very important to maintain database’s overall physical architecture, and to overcome any disk failure, its multiple copies are stored on separate disks online. The names of the database’s control file; are specified & multiplexed using the CONTROL_FILES initialization
18 |
M. Sc. (CS) SY Advance Database Administration (CS-301)
Trace Files & Alert Log Files
• There are many situations where we as the DBA might want to find out more information about what Oracle is doing behind the scenes in order to address issues related to its operations. There are Trace Files & Alert Log Files these two type of Diagnostic Files in a typical Oracle configuration that Oracle will utilize while our database is up & running.
1. Trace Files: Background processes & Network Processes & Listener Process generate trace files whenever something goes wrong with their operation. The name of trace file with an extension .trc typically contains the process concatenated with the value specified by INSTANCE_NAME in the init.ora file.
• Trace files generated by the processes are stored in the directory specified by the parameter USER_DUMP_DEST in init.ora file. It is a good procedure to check these Trace Files when the database behaves or crashed abnormally.
• Note : Make sure that we should turn them off when they are not needed as they could grow very fast, & may fill the disk.
2. Alert Log Files: These files are created automatically by Oracle as soon as we create an Instance, & stored in the location specified by the parameter BACKGROUN_DUMP_DEST in the init.ora file. Other processes such as Oracle network processes also generate these files. The name this file is alert_sid.log or sidalert.log, where sid is the name specified by the parameter INSTANCE_NAME in the init.ora file.
• The Alert Log file stores information that is extremely useful in order to know the health of the database. It records the starting & stopping of the database, the creation of new redo log file at every time a log switch occurs, the creation of tablespaces , the addition of new datafiles to the tablespaces, & most importantly the errors that are generated by Oracle.
19 |
M. Sc. (CS) SY Advance Database Administration (CS-301)
• As this file tends to grow big with passage of time, part of the file should be removed or achieved on a regular basis. As a DBA, one of the best practices is to check this file periodically for any error that starts with ORA.
• The main difference between Trace Files & Alert Log Files is that the Trace Files are needed for troubleshooting, while the Alert Log Files are needed all the time by DBAs as they include important information.
1.8 Creating Database Manually
• The steps for creating the Manual Database are
• Create the directory structure to hold the database efficiently as shown below using Optimal Flexible Architecture (OFA).
DATA
LOG
CONTROL
O
M
E
D
UDUMP BDUMP SCRIPTS
ORADATA ARCHIVE
• Open Notepad and create two script files namely initialization Parameter File & Database Creation Script as Shown below.
• Save the Initialization file to the default location as ORACLE_HOME\database with the name INITdemo.ora.
20 |
M. Sc. (CS) SY Advance Database Administration (CS-301)
Initialization parameter file initDEMO.ora
21 |
M. Sc. (CS) SY Advance Database Administration (CS-301)
Database Creation Script DEMO.txt
• Define the instance using the oradim command as shown below
C:\> oradim –new – sid demo
• Set this newly created Instance as default using the following command C:\> set oracle_sid=demo
• Connect to the database via SQL * Plus as shown below
C:\> sqlplus “/as sysdba”
• The Command Prompt will be converted to the SQL Prompt as shown below. SQL>
• Now start the newly created using STARTUP Command with NOMOUNT option as shown below.
SQL>startup nomount
22 |
M. Sc. (CS) SY Advance Database Administration (CS-301)
• Once the instance is started we have to execute the Database Creation Script.as shown below
SQL> @ E:\demo\scripts\demo.txt
• After running the above script successfully our database will get created. • After Database creation we need to run some special script files called catalog.sql , catproc.sql & catexp.sql which are located in ORACLE_HOME\rdbms\admin directory. • Query the V$PARAMETER dynamic view to see the parameters in effect in the database as follows:
SQL> select Name, Value, IsDefault from V$PARAMETER;
23 |
No comments:
Post a Comment