Chapter 9 Create a Database Flashcards
- Evaluating Database server hardware
- Installing Oracle
- Planning DB and Security Strategy
- Creating, migrating, opening DB
- Backing up DB
- Enrolling system users and planning Oracle Network
- Implement DB design
- Recovering DB failure
- Monitoring DB performance
Designing, implementing, maintaining Oracle DB
Logical storage structure of DB and its physical implementation: How many: Disk Drives? Type of storage? Tablespaces
DB Planning
- Overall DB design
- DB backup strategy
DB Planning cont.
Data warehouse: - research & marketing data Transaction processing: - Store checkout register system General purpose: - Retail billing system
Types of DB
NetCA - Listener
Steps
Configure Listener Add Name Select protocol Port number
DBCA - Create DB
Create
Select Template - General purpose
Assign ID - listener must be configured
Set Access Control - set admin pw
SYS user owns all base tables and user-accessible view of
Administrative Users
user is used to create additional tables and views that display admin info
SYSTEM
user represents Enterprise Manager super admin account
SYSMAN
user is used by EM to monitor DB; used to access performance stats about DB
DBSNMP
SYSOPER are admin privileges to perform high-level admin operations (creating, starting up, shutting down, backing up, recover DB)
Super roles (access DB instance - non open mode)
can do SYSOPER plus can look at user data
Control of these privileges is therefore completely outside DB
SYSDBA
startup DB shutdown backup recover create
5 operations only performed by SYSDBA
DBCA Create DB - File System
Select File System
Use DB file location from template
Specify flash recovery area
Sample Schemas
oracle_home=/u01/app/oracle/product/11.2.0/db_1
oracle_home/dbs/spfilemydb.ora
/u01/app/oracle/oradata/admin/mydb/pfile/init.ora
/u01/app/oracle/oradata/mydb/.. (ctl, redologs, dbf ..)
/u01/app/oracle/flash_recovery_area/mydb/control02.ctl
/u01/app/oracle/cfgtoollogs/dbca/mydb/trace.log
Files Locations
DBCA - Create DB - Memory
Typical Custom Memory Management SGA PGA Size
DBCA - Create DB - Maintenance Settings
Enable automatic maintenance tasks
Keep enhanced 11g default security settings
Create DB - Create Options
Datafiles
Create DB
Save DB template
PW management
DBCA - Create Templates
Create DB template
From an existing DB
DBA role
DBCA - Delete DB
Select DB = hist
Finish
Yes
DBCA - Other Available Tasks
Select operation:
Configure DB options
Configure Automatic Storage Management
Shutdown -> Nomount - Instance started, parameter file read, background process initiated; Mount - Control file opened for this instance Open All files opened as described by control file Startup
Starting up Oracle DB Instance
sql> startup nomount;
sql> startup mount;
sql> startup open [read only] [recover];
sql> startup force;
–performs a shutdown abort then restarts
sql> startup restrict;
–open to only restricted session privilege
sql> alter database [mount|open];
–increase the database availability state
Startup DB Options
sql> shutdown; default to shutdown normal
–wait till all users are disconnected
sql> shutdown transactional;
–wait till all active transactions are completed
sql> shutdown immediate;
–roll back uncommitted transactions
–disconnect all clients immediately
sql> shutdown abort;
–terminate current sql statements, no roll back
–disconnect all clients immediately
–requires instance recovery in next startup
Shutdown DB Options
sql> startup [nomount|mount|open|force|restrict]
sql> shutdown [normal|transactional|immediate|abort]
Alert log contains chronological summary of startup, shutdown, alter activities (see text pages 506-510)
Find location of alert logs diag trace and diag alert:
sql> select name, value from v$diag_info
Alert log is continuously appended, purging is required.
Alert log
