Database Administration

Database Administration Oracle Database Instance Management Starting Up and Shutting Down أ.ندى الغامدي , أ.ندى الطوالة

Introduction • Database: consists of a set of disk files that store user data and metadata. Metadata, or "data about the data,“ (Data Dictionary) • Database Instance: contains the set of Oracle Database background processes that operate on the stored data and the shared allocated memory that those processes use to do their work. • When the Oracle instance is not available, it cannot be accessed by any user or application. أ.ندى الغامدي , أ.ندى الطوالة

Oracle Database Instance Management • The properties of an Oracle instance are specified using instance initialization parameters. When the instance is started, an initialization parameter file is read, and the instance is configured accordingly. • Managing an Oracle instance includes configuring parameters that affect the basic operation of the Oracle instance. These parameters are called initialization parameters. The Oracle instance reads initialization parameters from a file at startup. أ.ندى الغامدي , أ.ندى الطوالة

Oracle Database Instance Management - After being read from a file, initialization parameters are retained in memory, where the values for many of them can be changed dynamically. There are two types of parameter files. The type of file used to start the instance determines if dynamic initialization parameter changes persist across database shutdown and startup. أ.ندى الغامدي , أ.ندى الطوالة

Oracle Database Instance Management أ.ندى الغامدي , أ.ندى الطوالة

Oracle Database Instance Management • 1- Server Parameter File (SPFILE): it is a binary file that can be written to and read by the database. It must not be edited manually. It is stored on the host computer on which Oracle Database is running. Changes are made when you use Database Control to modify one or more initialization parameters, or when Oracle Database itself makes changes for self-tuning purposes. أ.ندى الغامدي , أ.ندى الطوالة

Oracle Database Instance Management • 2- Text Initialization Parameter File (PFILE): is a text file that can be read by the Oracle instance. You can change a text initialization parameter file with a text editor, but changes do not take effect until you restart the Oracle instance. When you start the instance with this type of file, you can still change many initialization parameters dynamically with Database Control, but only for the current instance. Unless you also edit the text initialization parameter file and make the same change, the change is lost when you restart the database instance. أ.ندى الغامدي , أ.ندى الطوالة

Oracle Database Instance Startup • Stages for Starting Up a Database: • NOMOUNT • MOUNT • OPEN • When you start up a database, you create an instance of that database and you determine the state of the database. To start an instance, the database must read instance configuration parameters (the initialization parameters) from either a server parameter file (SPFILE) or a text initialization parameter file. أ.ندى الغامدي , أ.ندى الطوالة

Oracle Database Instance Startup • You start up the instance using one of the following methods: • 1- Oracle Enterprise Manager. • 2- SQL Plus STARTUP command. أ.ندى الغامدي , أ.ندى الطوالة

Starting Up an Oracle Database Instance: NOMOUNT OPEN STARTUP MOUNT Shutdown NOMOUNT Instance started SHUTDOWN أ.ندى الغامدي , أ.ندى الطوالة

Starting Up an Oracle Database Instance: NOMOUNT • In the platform-specific default location, Oracle Database locates your initialization parameter file by examining file names in the following order: • 1. spfileORACLE_SID.ora • 2. spfile.ora • 3. initORACLE_SID.ora • ( Allocating the SGA, Starting the background processes, Opening the alert<SID>.logfile and the trace files. ) أ.ندى الغامدي , أ.ندى الطوالة

Starting Up an Oracle Database Instance: MOUNT OPEN STARTUP MOUNT Control file opened for this instance Shutdown NOMOUNT Instance started SHUTDOWN أ.ندى الغامدي , أ.ندى الطوالة

Starting Up an Oracle Database Instance: MOUNT • Mounting a database includes the following tasks: • Associating a database with a previously started instance. • Locating and opening the control files specified in the parameter file. • Reading the control files to obtain the names and statuses of the data files and online redo log files. أ.ندى الغامدي , أ.ندى الطوالة

Starting Up an Oracle Database Instance: OPEN OPEN STARTUP All files opened as described by the control file for this instance MOUNT Control file opened for this instance Shutdown NOMOUNT Instance started SHUTDOWN أ.ندى الغامدي , أ.ندى الطوالة

Starting Up an Oracle Database Instance: OPEN • A normal database operation means that an instance is started and the database is mounted and opened. • With a normal database operation, any valid user can connect to the database and perform typical data access operations. • Opening the database includes the following tasks: • Opening the online data files. • Opening the online redo log files. • If any of the data files or online redo log files are not present when you attempt to open the database, then the Oracle server returns an error. أ.ندى الغامدي , أ.ندى الطوالة

SQL Commands: • STARTUP; = STARTUP OPEN; • starts an instance, reads the initialization parameters from the default location, and then mounts and opens the database. • STARTUP PFILE = $Oracle_Home/dbs/initdb01.ora • Start up the instance pointing to this initialization parameter file. • STARTUP NOMOUNT; • STARTUP MOUNT; أ.ندى الغامدي , أ.ندى الطوالة

Additional Instance Startup Options • Two additional options for Starting up an Instance: • Restrict access to the database. • Force the instance to start. أ.ندى الغامدي , أ.ندى الطوالة

Restricted Access to the Database • Instance can be started in Restricted Mode so that the instance is available only to administrative personnel (not general database users). • Only database administrators should have the RESTRICTED SESSION system privilege. Further, when the instance is in restricted mode, a database administrator cannot access the instance remotely through an Oracle Net listener, but can only access the instance locally from the machine that the instance is running on. • ALTER SYSTEM ENABLE RESTRICTED SESSION; • STARTUP RESTRICT; أ.ندى الغامدي , أ.ندى الطوالة

Forcing an Instance to Startup • In unusual circumstances, you might experience problems when attempting to start a database instance. • Because a previous instance might not have been shutdown cleanly. For example, one of the instance's processes might not have terminated properly. • In such situations, the database can return an error during normal instance startup. أ.ندى الغامدي , أ.ندى الطوالة

Forcing an Instance to Startup • Instance can be forced to start up using the Force option and that will terminate all remnant Oracle processes of the previous instance before starting the new instance. • STARTUP FORCE; أ.ندى الغامدي , أ.ندى الطوالة

More SQL Commands: • ALTER DATABASE MOUNT; • ALTER DATABASE OPEN; • ALTER DATABASE ORCL MOUNT; • ALTER DATABASE OPEN READ ONLY; • ALTER DATABASE OPEN READ WRITE; أ.ندى الغامدي , أ.ندى الطوالة

Oracle Database Instance Shutdown • Modes for Shutting down a Database: • ABORT • IMMEDIATE • TRANSACTIONAL • NORMAL أ.ندى الغامدي , أ.ندى الطوالة

Oracle Database Instance Shutdown If the instance is already started it can be Shutdown. • Shutdown modes are progressively more accommodating of current activity in this order: • ABORT:This is typically used: when no other form of shutdown works (NORMAL and IMMEDIATE), when there are problems when starting the instance, or when you need to shut down immediately because of an impending situation. • IMMEDIATE: Uncommitted transactions are rolled back. • TRANSACTIONAL: Allows transactions to finish. • NORMAL: Waits for sessions to disconnect. • If you consider the amount of time that it takes to perform the shutdown, you find that ABORT is the fastest and NORMAL is the slowest. أ.ندى الغامدي , أ.ندى الطوالة

Shutdown Modes During NORMAL or TRANSACTIONAL or IMMEDIATE • On the way down: • Uncommitted changes rolled back, forIMMEDIATE. • Database buffer cache written to data files. • Resources released. • On the way up: • No instance recovery. Consistent database (clean database) أ.ندى الغامدي , أ.ندى الطوالة

Shutdown Modes During ABORT or Instance failure or STARTUP FORCE • On the way down: • Uncommitted changes not rolled back. • Modified buffersnot written to data files. • On the way up: • Online redo log files used to reapply changes. • Undo segments used to roll back uncommitted changes. • Resources released. Inconsistent database (dirty database) أ.ندى الغامدي , أ.ندى الطوالة

Oracle Database Instance Shutdown • SQL Commands: • SHUTDOWN APORT; • SHUTDOWN IMMEDIATE; • SHUTDOWN TRANSACTIONAL; • SHUTDOWN NORMAL; أ.ندى الغامدي , أ.ندى الطوالة

Managing Oracle instance through the Enterprise Manager أ.ندى الغامدي , أ.ندى الطوالة

أ.ندى الغامدي , أ.ندى الطوالة

Database Home Page أ.ندى الغامدي , أ.ندى الطوالة

Starting up the Oracle Database أ.ندى الغامدي , أ.ندى الطوالة

Shutting down the Oracle Database أ.ندى الغامدي , أ.ندى الطوالة

Managing Oracle instance through the SQL Plus أ.ندى الغامدي , أ.ندى الطوالة

Database Administration