講義軟件課件dba

1、Managing an Oracle InstanceObjectivesAfter completing this lesson, you should be able to do the following:Create and manage initialization parameter filesConfigure OMFStart up and shut down an instanceMonitor and use diagnostic filesOracle InstanceSGARedo logbufferData buffercacheShared poolData dic

2、t.cacheLibrarycacheDBW0SMONPMONCKPTLGWROthersspfiledb01.oraInitialization Parameter FilesSQL CONNECT / AS SYSDBASQL STARTUP Initialization Parameter FilesEntries are specific to the instance being accessedThere are two kinds of parameters:Explicit: Having an entry in the fileImplicit: No entry withi

3、n the file, but assuming the Oracle default valuesMultiple files can be used for a single database to optimize performance in different situations.Changes to entries in the file take effect based on the type of initialization parameter file used;Static parameter file, PFILE Persistent parameter file

4、, SPFILEPFILEinitSID.oraThe PFILE is a text file that can be modified with an operating system editor.Modifications to the file are made manually.Changes to the file take effect on the next startup.Its default location is $ORACLE_HOME/dbs.PFILE Example# Initialization Parameter File: initdb01.oradb_

5、name = db01instance_name = db01control_files = ( /u03/oradata/db01/control01db01.ctl, /u03/oradata/db01/control02db01.ctl)db_block_size = 4096db_block_buffers = 500shared_pool_size = 31457280 # 30M Shared Pooldb_files = 1024max_dump_file_size = 10240background_dump_dest = /u05/oracle9i/admin/db01/bd

6、umpuser_dump_dest = /u05/oracle9i/admin/db01/udumpcore_dump_dest = /u05/oracle9i/admin/db01/cdumpundo_management = autoundo_tablespace = undtbs. . .SPFILEspfileSID.oraBinary file with the ability to make changes persistent across shutdown and startupMaintained by the Oracle serverRecords parameter v

7、alue changes made with the ALTER SYSTEM commandCan specify whether the change being made is temporary or persistentValues can be deleted or reset to allow an instance to revert to the default valueALTER SYSTEM SET undo_tablespace = UNDO2;Creating an SPFILESPFILE can be created from an initSID.ora fi

8、le using the CREATE SPFILE command, which can be executed before or after instance startup:CREATE SPFILE FROM PFILE;SPFILE Example*.background_dump_dest=$ORACLE_HOME/admin/db01/bdump* patible=9.0.0*.control_files=/u03/oradata/db01/ctrl01db01.ctl,/u03/oradata/db01/ctrl02db01.ctl*.core_dump_dest=$ORAC

9、LE_HOME/admin/db01/cdump*.db_block_buffers=500*.db_block_size=4096*.db_files=40*.db_name=db01*.instance_name=db01*.remote_login_passwordfile=exclusive*.shared_pool_size=31457280 # 30M Shared Pool*.undo_management=AUTOdb01.undo_tablespace=UNDOTBS01db02.undo_tablespace=UNDOTBS02. . .Oracle Managed Fil

10、esOracle Managed Files (OMF) simplify fileadministrationOMF are created and deleted by the Oracle server as directed by SQL commandsOMF are established by setting two parameters:DB_CREATE_FILE_DEST: Set to give the default location for data filesDB_CREATE_ONLINE_LOG_DEST_N: Set to give the default l

11、ocations for online redo logs and control files, up to a maximum of 5 locationsOracle Managed File ExampleTo create a database where data files, control files, and online redo log files are created in separate directories:Set the initialization parameters:Issue the CREATE DATABASE SQL statement.DB_C

12、REATE_FILE_DEST = /u01/oradata/DB_CREATE_ONLINE_LOG_DEST_1 = /u02/oradata/DB_CREATE_ONLINE_LOG_DEST_2 = /u03/oradata/Starting Up a DatabaseNOMOUNTOPENMOUNTNOMOUNTSHUTDOWNInstance startedSTARTUPSHUTDOWNStarting Up a DatabaseMOUNTOPENMOUNTNOMOUNTSHUTDOWNControl file opened for this instanceInstance st

13、artedSTARTUPSHUTDOWNStarting Up a DatabaseOPENOPENMOUNTNOMOUNTSHUTDOWNAll files opened as described by the control file for this instanceControl file opened for this instanceInstance startedSTARTUPSHUTDOWNSTARTUP CommandSTARTUPStart up the instance and open the database:STARTUP PFILE=$ORACLE_HOME/db

14、s/initdb01.oraThe ALTER DATABASE CommandChange the state of the database from NOMOUNT to MOUNT:ALTER DATABASE db01 MOUNT;Open the database as a read-only database:ALTER DATABASE db01 OPEN READ ONLY;Opening a Database in Restricted ModeUse the STARTUP command to restrict access to a database:Use the

15、ALTER SYSTEM command to place an instance in restricted mode:STARTUP RESTRICTALTER SYSTEM ENABLE RESTRICTED SESSION;Opening a Database in Read-Only ModeA databases can be opened as a read-only database.A read-only database can be used to:Execute queriesExecute disk sorts using locally managed tables

16、pacesTake data files offline and online, not tablespacesPerform recovery of offline data files and tablespacesShutting Down the DatabaseShutdown Mode:NORMALTRANSACTIONALIMMEDIATEABORTAxxxxTxxooIxxxoShutdown ModeAllow new connectionsWait until current sessions end Wait until current transactions endF

17、orce a checkpoint and close filesNxoooYESNOxoShutdown OptionsDuring aShutdown Normal,Shutdown TransactionalorShutdown ImmediateConsistent Database(clean database)On the way down:Database buffer cache written to the data files mitted changes rolled backResources released.On the way up:No instance rec

18、overyShutdown OptionsDuring aShutdown AbortorInstance FailureorStartup ForceInconsistent Database(dirty database)On the way down:Modified buffers are not written to the data files mitted changes are not rolled backOn the way up:Redo logs used to reapply changesUndo segments used to roll back mitted

19、changesResources releasedManaging an Instance by Monitoring Diagnostic FilesDiagnostic files contain information about significant events encountered while the instance is operational. Used to resolve problems or to better manage the database on a day-to-day basis.Several types of diagnostic files e

20、xist:alertSID.log fileBackground trace filesUser trace filesAlert Log FileThe alertSID.log file records the commands and Results of major events while the database is operational.It is used for day-to-day operational information or diagnosing database errors.Each entry has a time stamp associated wi

21、th it.The DBA manages the alertSID.log file.Its location is defined by BACKGROUND_DUMP_DEST.Background Trace FilesBackground trace files support information errors detected by any background process.They are used to diagnose and troubleshoot errors.They are created when a background process encounte

22、rs an errorsTheir location is defined by BACKGROUND_DUMP_DESTUser Trace FileA user trace file is produced by the user processconnected to the Oracle server through the server process.A user trace file contains statistics for traced SQL statements or user error messages. It is created when a user encounters user session error