1 / 36

IT 21003 Database Administration

IT 21003 Database Administration. SECTION 01. Starting Up and Shutting Down the Database. Database Administration Facilities A number of tools are available for DBAs to connect to an Oracle database Enterprise Manager SQL Worksheet SQL Plus Third Party Tools.

aziza
Download Presentation

IT 21003 Database Administration

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. IT 21003 Database Administration SECTION 01

  2. Starting Up and Shutting Down the Database Database Administration Facilities A number of tools are available for DBAs to connect to an Oracle database Enterprise Manager SQL Worksheet SQL Plus Third Party Tools

  3. Starting Up and Shutting Down the Database Connecting as DBA To perform startup and shutdown activity, DBAs need to connect to the database with SYSDBA or SYSOPER system privileges SYSDBA allows startup and shutdown and full access to all database objects SYSOPER allows startup, shutdown, and access to dictionary object definitions only Useful to give to others instead of SYSDBA

  4. Starting Up and Shutting Down the Database Connecting as DBA To connect via SQL CONNECT “username/password” AS SYSDBA/SYSOPER

  5. Starting Up and Shutting Down the Database Connecting as SYS or SYSTEM Oracle databases are created with two accounts that have DBA privileges SYS owns the data dictionary tables and views Has a password of CHANGE_ON_INSTALL on creation of a database

  6. Starting Up and Shutting Down the Database - SYSTEM is an account with DBA privileges and owns any tables required for any of the Oracle development tools Has a database password of MANAGER on creation of a database

  7. Starting Up and Shutting Down the Database By default, only SYS can be used for startup and shutdown commands SYS is able to connect as SYSDBA or SYSOPER This is not possible by default from SYSTEM

  8. Starting Up and Shutting Down the Database Connecting as SYSDBA to the Database When using SYS to connect as SYSDBA, you will need to use a special password (not CHANGE_ON_INSTALL) An INTERNAL connection also requires this password The “Special” password is held encrypted in a password file (PWD<SID>.ORA) by the operating system Oracle provides a utility (ORAPWD) that creates the password file and sets the initial value for the password

  9. Starting Up and Shutting Down the Database DBA Password Management The parameter REMOTE_LOGIN_PASSWORDFILE can be used to define the mode of the password file EXCLUSIVE The password file can be used with only one database Users other than SYS and INTERNAL may be given SYSDBA or SYSOPER privileges

  10. Starting Up and Shutting Down the Database SHARED For use with multiple databases Only logins by SYS and INTERNAL are recognized by a shared password file NONE No privileged connections over non-secured networks Allows remote connections from accounts that are able to activate OSOPER and/or OSDBA roles (This is OS authentication)

  11. Starting Up and Shutting Down the Database Use the ORAPWD facility to Set the name of the password file to be created Set the initial password for INTERNAL and SYS

  12. Starting Up and Shutting Down the Database Controlling Availability of a Database An INSTANCE is used to access and manipulate database data and storage structures An INSTANCE can be Started Up - This builds the SGA in memory and starts the background processes Shut Down - Closes the database and stops the INSTANCE Removes memory structures

  13. Starting Up and Shutting Down the Database Remember: There are, in essence, two quite different entities: the DATABASE and the Oracle INSTANCE (System) The system (INSTANCE) undergoes STARTUP and SHUTDOWN The Database may be OPEN, CLOSED, or MOUNTED

  14. Starting Up and Shutting Down the Database Starting Up Database Startup States

  15. Starting Up and Shutting Down the Database Database Startup States Cont’d Oracle supports moving UP the stairs, not back down the stairs To move from OPEN to MOUNT state, the INSTANCE must first be shut down

  16. Starting Up and Shutting Down the Database Altering the Database States Oracle supports altering the database only in the upward direction and only one step at a time ALTER DATABASE MOUNT; ALTER DATABASE OPEN;

  17. Starting Up and Shutting Down the Database Database Startup SQL: STARTUP [FORCE}|[NOMOUNT | MOUNT | OPEN] STARTUP (by itself) Starts up the INSTANCE, oracle_sid_name, using parameters found in the pfile If PFILE is omitted, it uses the init<sid>.ora found in the Oracle_Home directory/folder

  18. Starting Up and Shutting Down the Database Startup Options FORCE Shuts down an INSTANCE before starting it up in the specified mode Same as performing SHUTDOWN ABORT followed by a STARTUP

  19. Starting Up and Shutting Down the Database Startup Options NOMOUNT Starts up the INSTANCE without mounting the database Activities are limited – creating a database The database is inaccessible

  20. Starting Up and Shutting Down the Database Startup Options MOUNT Starts up the INSTANCE and MOUNTS the database Allows restricted use of the database for DBA tasks Perform file management and database restructuring No normal user access is possible

  21. Starting Up and Shutting Down the Database Startup Options OPEN Starts the INSTANCE, mounts and then opens the database in shared or exclusive mode Many file management activities are available Manipulating log files Taking datafiles offline Backing up control files But normal user processing is allowed

  22. Starting Up and Shutting Down the Database Startup Options RECOVER Forces automatic recovery procedures on startup

  23. Starting Up and Shutting Down the Database Startup Options RESTRICT Limits access to the database to users who have been granted both the CREATE SESSION and RESTRICTED SESSION system privileges EXAMPLE: STARTUP OPEN RESTRICT;

  24. Starting Up and Shutting Down the Database Startup Options Restrict is useful for performing database exports while there is no activity on the database Database must be open for export of data The limit can be removed while the database is open ALTER SYSTEM DISABLE RESTRICTED SESSION;

  25. End 01-18-2006

  26. Starting Up and Shutting Down the Database Shutting Down the Database SHUTDOWN [NORMAL | IMMEDIATE | ABORT | TRANSACTIONAL[n]] This command primarily relates to a running instance

  27. Starting Up and Shutting Down the Database Shutting Down the Database Closes the database, dismounts the database, and shuts down the instance Before an open database can be placed in the NOMOUNT or MOUNT state it must first be shut down Oracle databases can be suspended and resumed ALTER SYSTEM SUSPEND | RESUME;

  28. Starting Up and Shutting Down the Database Shutdown Options SHUTDOWN NORMAL Database will not shut down until all users have logged off and all work is committed (or rolled back) No new connections are allowed

  29. Starting Up and Shutting Down the Database Shutdown Options SHUTDOWN IMMEDIATE Disconnects all users and performs rollback on all uncommitted data by using PMON Terminates all current SQL statements

  30. Starting Up and Shutting Down the Database Shutdown Options SHUTDOWN ABORT Shuts down without tidying up; akin to a system failure and requires recovery on startup No roll back of uncommitted transactions

  31. Starting Up and Shutting Down the Database Shutdown Options SHUTDOWN TRANSACTIONAL [n] Prevents new transactions from starting and shutting down after all pending transactions have finished The optional [n] specifies a timeout period for pending transactions

  32. Starting Up and Shutting Down the Database Altering the Database Mode ALTER DATABASE [ db_name ] [MOUNT [ SHARED | EXCLUSIVE ] [OPEN] [CLOSE [ NORMAL | IMMEDIATE ]

  33. Starting Up and Shutting Down the Database Altering the Database Mode This previous SQL statement can be used after the STARTUP command to move a database “forward” to a MOUNT or OPEN state NOMOUNT MOUNT OPEN In reality there are only two useful forms of this statement ALTER DATABASE db_name MOUNT; ALTER DATABASE db_name OPEN;

  34. Starting Up and Shutting Down the Database Automatic Startup DBAs can utilize Database Event Triggers Can specify LOGON, LOGOFF, STARTUP, SHUTDOWN triggers Issues relating to shutdown If a system undergoes shutdown without first shutting down Oracle , an instance recovery is necessary on startup Only happens when the DBAs are lazy

  35. Starting Up and Shutting Down the Database Checking the Database and Oracle_SID Names In SQL Worksheet Plus Find the name of the database in v$database (must be mounted or open) SELECT name FROM v$database; Find the name of the Oracle Instance SELECT instance_name, version FROM v$instance;

  36. Starting Up and Shutting Down the Database Questions?

More Related