Transcript IST 274 -10 Database Administrator

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
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
Starting Up and Shutting Down the Database
Connecting as DBA
–To connect via SQL
• CONNECT “username/password” AS
SYSDBA/SYSOPER
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
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
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
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
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
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 nonsecured networks
– Allows remote connections from accounts
that are able to activate OSOPER and/or
OSDBA roles (This is OS authentication)
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
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
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
Starting Up and Shutting Down the Database
Database Startup States
Starting Up
STARTUP MOUNT
3. Links the INSTANCE to a database
4. Reads Control Files
5. Checks Datafiles and Redo Log Files
STARTUP NOMOUNT
1. Reads init.ora (pfile)
2. Creates and starts INSTANCE
STARTUP OPEN
6. Opens datafiles and redo logs
7. Acquires Rollback Segments
8. Calls SMON if needed
9. Makes Data Dictionary available
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
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;
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
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
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
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
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
Starting Up and Shutting Down the Database
Startup Options
– RECOVER
– Forces automatic recovery procedures on
startup
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;
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;
End 01-18-2006
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
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;
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
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
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
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
Starting Up and Shutting Down the Database
Altering the Database Mode
– ALTER DATABASE [ db_name ]
[MOUNT [ SHARED | EXCLUSIVE ]
[OPEN] [CLOSE [ NORMAL |
IMMEDIATE ]
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;
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
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;
Starting Up and Shutting Down the Database
Questions?