Oracle is basically formed from :
The Oracle server has a set of memory structures and operating process called "The Instance" which control the actions that occur between the end user and the database, its main components are:
Queries : The server session hashes de SQL statement passed to it and compares that hash number with the hash numbers of statements already saved in the Shared SQL area. If an exact duplicate of the statement is found in the shared pool, the parsed form of the statement and the execution plan that are already stored are used. If a match is not found in the shared pool, the server session parses de statement.
Next, the server checks to see wheter the data blocks necessary to complete the transaction (query) are already stored in the database buffer cache. If the block are not in the buffer, the server reads the necessary blocks from the data files and copies them into the cache. NOW it returns the information.
DML Statements : It performs the same steps up to when the data is copied into the buffer (data block buffer cache), before it returns the infromation (as is the case with a query),the blocks in memory (the cache) are modified accordingly. Once they are modified in memory these blocks are marked as dirty, and are placed on the dirty list. Redo log information is also generated on this transaction and placed in the redo-log cache. Up to this point any of the following can occur:
NOTE: The transaction never records as successful until the redo-log buffer successfully writes to the online redo-log files. This demonstrates the importance of the LGRW and ARCH processes.
Install and Deinstall Products. Upgrade Oracle version
Via Operating System : When setting up a user to be authenticated via the operating system the user name defined in the operating system will differ from the user name in Oracle by a prefix defined in the init.ora file, name OS_AUTHENT_PREFIX , this prefix defaults to OPS$.Example:
If in the UNIX system there is a user by the name of alfonso , if an Oracle account by the name of OPS$ALFONSO , then this user will be able to login automatically into SQL*Plus, once authenticated by the OS.
The user can be defined in various way inside Oracle :
create user OPS$alfonso identified by ALFONSO;
Via Password File : When the user cannot be authenticated via
the Operating System, then a password file is created. To generate a password file it is necessary to use the ORAPWD utility.
ORAPWD FILE=filename PASSWORD=password ENTRIES= max users
The password parameter specifies the password for that must be used in order to login as a DBA to the database. The ENTRIES parameter cannot be modified afterword,so it is recommended to be set at a high value.
NONE,SHARED and EXCLUSIVE . The NONE value causes Oracle to behave as if the password file did not exist (this is the default).
The SHARED value enables the password file to be used by multiple databases. However the only users recognized by a SHARED password file are SYS and INTERNAL , thus you cannot add users to a SHARED password file.
The EXCLUSIVE is of course used for a single database.
connect internal , then grant either the SYSOPER or SYSDBA to your users. SYSDBA gives the user DBA authority; SYSOPER lets the user perform database operations suppor activities. The views V$PWFILE_USERS holds information on which users have been granted these privileges. The use of a Password file does not-prevent OS-authenticated users from connecting if they meet the criteria for OS authenticating.
Oracle Enterprise Manager provide a framework for an enterprise wide distributed system management solution. Enterprise Manager is open and extendible, Tcl is used to submit commands to remote operating systems and databases for execution. The implementation of Tcl used by Enterprise Manager,is known as OraTcl, and includes extensions that enable functions you need to fully manager an Orcle database environment:
NOTE on SNMP: Originally SNMP was used to communicate with network devices, but it is now used to communicate with applications as well. Application developers now integrate applications within the console using Object Linking and Embedding (OLE) or calls to one of the published APIs specific to Oracle Enterprise Manager.
When information moves over the network between the console and the intelligent agents, it can be secured with Oracle Server Network Services using the Net8 Advanced Networking Option (ANO). This level of security makes it possible to administer remote databases over the Internet.
In addition to messages between the communications daemon and the intelligent agents, the application components such as the database administration tools communicate directly with the remote databases by using SQL over Net8. Tasks performed by the application components directly on remote databases in real time do not use the communication daemon. The communication daemon uses either native TCP/IP or Net8 TNS (Transparent Network Substrate) connections to communicate with remote intelligent agents to perform core console functions such as job scheduling and event management.
The intelligent agent autonomously executes and manages remote activities. After the communication dameon instructs a remote agent to execute a particular script at a given time, the script executes independently of the console. If the console is unavailable when it is time to execute the remote job, the agent manages the execution and buffers up to 500 returnes messages. The agent passes this information back to the console, when the console becomes available.
Getting Started : After installing the OEM, it is recommended that a repositary be generated as this provides an easier way to login to the console and store prefered credentials between sessions. This is even more beneficial when there are various systems administrators, each one should have their own repository so he or she can store his information.The Adminsitration Tools which are provided in OEM can can accessed in four ways.
The FOUR primary CONSOLE functions
The following Database Administration Tools are available :
The Enterprise manager Diagnostic pack is a value added component of Enterprise Manager that provides tools for performance monitoring and tracking, session monitoring, trace management, and lock management. The diagnostic pack provides tools necessary to capture, store, and analyze performance data to meet this objective.
Performance Manager is the centerpiece of the Diagnostic pack . This tool provides real-time performance monitoring and the capability to record performance characterstics for later analysis.It has built in charts and tables for collection and analysis of contention, global database statisitics, I/O, workload levels, memory, and parallel server information.
Trace Manager is a comprhensive tool for collecting data for performance management, capacity planning, and workload managment. By using the provided API,trace manager can even collect information from third-party and custom applications. Job (the component of the Enterprise Manager) must be configured before using Trace Manager. The tool also uses its own repository and an Oracle Expert repository, which it builds the first time it is accessed by a userr who hasn't used Expert or Trace.
Top Sessions Top sessions enables database administrators to monitor database sessions and kill them if necessary due to inactivity or over-utilization of resources. This tool monitors all sessions or only the most active based on consumption of specified resources such as memory or I/O.
Capacity Planner The capacity planner of the diagnostic pack is used to collect and store database and operating system statistics in a repository that can be later analyzed.
Lock Manager It is a utility for monitoring database locks and troubleshooting problem blocking and waiting conditions.
The prerequiste for this utility are the existence of the views: V$LOCK, V$SESSION, DBA_OBJECTS
This utility includes:
Tablespace Manager : Provides detailed information on storage and utilization in tablespaces, rollback segments, and data files.
Oracle Expert : Expert is the implementation of a performance management methedology. Wheras other diagnositc packs provide information for database administratos to analyze, Expert applies a set of rules to the data to develop conclusions and make recommenadtions to improve performance.
SQL Analyze : SQL Analyze is a tool that will enable you to performance tune any troublsome SQL statement.
OTHER ENTERPRISE VALUE ADDED PRODUCTSThe default parameter file (init.ora) is located at $ORACLE_HOME/dbs directory, the default naming for this file is initSID.ora, where SID is the system identifier for the database. At the very least this file should contain the following three parameters: CONTROLFILE, DB_BLOCK_BUFFER and DB_NAME
The Instance is named based on the ORACLE_SID environment variable of the OS. The exact time the instance creation occurs is called nomount what this does is allocate the SGA and start the background processes.
# svrmgrl
> connect internal as sysdba
> startup nomount pfile=/u01/app/admin/init(SID).ora
If the path to pfile is not specified, then the command will look in the directory $ORACLE_HOME/dbs/ for a file named init($SID).ora , where SID is the environment variable SID.
The next stage in the process is mount stage, in this
stage the control file(s) is read and accessible,and queries and modifiactions to the data stored in the control file can be made.
alter database mount
The final stage, the database is opened, in this stage the database files referenced by the control files are locked for exclusive use by the instance.
alter database open
NOTE: The alter database mount can be skipped if instead of the startup nomount the command startup mount is issued.
The keyword shutdown is issued from svrmgr in order to shutdown the Database and Instance.
The command shutdown abort is used to closed down the Instance, Oracle will need to perform recovery prior to opening the database with command alter database open , the command shutdown is used when having deadlocks in the database application, because Oracle will not be able to shut down the database if the command shutdown immediate is issued.
Changes made to parameter values in the init.ora file do not take effect until the database is shutdown and restarted. These values can queried from the view V$PARAMETER .There is an exception, these parameters can be changed with the command ALTER SYSTEM ,when the system (Instance) is up , however in case the instance is shutdown the changes will not take effect on the next startup.
You can set a limit on the number of concurrent sessions that can connect to a database. To set the maximum number of concurrent sessions for an instance, set the parameter LICENSE_MAX_SESSION, once this limit is reached only users who have the RESTRICTED SESSION system privilege can connect to the instance; this allows DBAs to kill unneeded sessions, allowing other sessions to connect.
In addition to this parameter you can set a warning limit on the number of concurrent sessions, this is set with the parameter LICENSE_SESSIONS_WARNING. Once this limit is reached, additional users can continue to connect up to the maximum limit, but Oracle sends a warning for each connecting user.
A session marked to be terminated is displayed with the V$SESSION view.To KILL a session ,after querying the V$SESSION columns SID and SERIAL# , you issue the following command: (Assuming SID=7 and SERIAL=15, and of course the STATUS(column) INACTIVE )
ALTER SYSTEM KILL SESSION '7,15';
After the command is issued the STATUS (column) is changed to KILLED and the SERVER(column) is changed to PSEUDO
You can also see the current limits of all the license setting, the currnet number of sessions, and the maximum number of concurrent sessions for the instance by querying the V$LICENSE data dictionary view.
V$LOCK shows which sessions hold locks on certain processes . Net 8 adds support for session multiplexing.
The ALERT file , typically name SIDalrt.log contains : all internal errors (ORA-600), block corruption errors (ORA-1578) and deadlock errors, administrative operations, such as CREATE /ALTER /DROP /DATABASE /TABLESPACE /ROLLBACK SEGMENT, SQL statements and STARTUP,SHUTDOWNS, and ARCHIVE LOG, several messages and errors relating to the functions of shared server and dispatcher processes, errors occurring during the automatic refresh of a snapshot, the values of all the initialization parameters at the time the database and instace start. It is stored in the location specified by BACKGROUND_DUMP_TEST. Critical functions are always logged here, as well as database startup and shutdown messages.
Background processes also create their own trace files where problems and failures are logged. Background proccesses are logged to the directory specified by the parameter BACKGROUND_DUMP_TEST, and the tracefiles for server processes are logged to USER_DUMP_TEST. Both of these files are named processname_number.trc
In order to enbale SQL tracing, issue the command ALTER SESSION SET SQL_TRACE=TRUE, or set the parameter SQL_TRACE=true in init.ora