Chapter 9 Create a Database

Question 1

1. Evaluating Database server hardware
2. Installing Oracle
3. Planning DB and Security Strategy
4. Creating, migrating, opening DB
5. Backing up DB
6. Enrolling system users and planning Oracle Network
7. Implement DB design
8. Recovering DB failure
9. Monitoring DB performance

Answer 1

Designing, implementing, maintaining Oracle DB

Question 2

Logical storage structure of DB and its physical implementation:
How many:
Disk Drives?
Type of storage?
Tablespaces

Answer 2

DB Planning

Question 3

• Overall DB design

- DB backup strategy

Answer 3

DB Planning cont.

Question 4

Data warehouse:
- research & marketing data
Transaction processing:
- Store checkout register system
General purpose:
- Retail billing system

Answer 4

Types of DB

Question 5

NetCA - Listener

Steps

Answer 5

Configure Listener
Add
Name
Select protocol
Port number

Question 6

DBCA - Create DB

Answer 6

Create
Select Template - General purpose
Assign ID - listener must be configured
Set Access Control - set admin pw

Question 7

SYS user owns all base tables and user-accessible view of

Answer 7

Administrative Users

Question 8

user is used to create additional tables and views that display admin info

Answer 8

SYSTEM

Question 9

user represents Enterprise Manager super admin account

Answer 9

SYSMAN

Question 10

user is used by EM to monitor DB; used to access performance stats about DB

Answer 10

DBSNMP

Question 11

SYSOPER are admin privileges to perform high-level admin operations (creating, starting up, shutting down, backing up, recover DB)

Answer 11

Super roles (access DB instance - non open mode)

Question 12

can do SYSOPER plus can look at user data

Control of these privileges is therefore completely outside DB

Answer 12

SYSDBA

Question 13

startup DB
shutdown
backup 
recover
create

Answer 13

5 operations only performed by SYSDBA

Question 14

DBCA Create DB - File System

Answer 14

Select File System
Use DB file location from template
Specify flash recovery area
Sample Schemas

Question 15

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

Answer 15

Files Locations

Question 16

DBCA - Create DB - Memory

Answer 16

Typical
Custom
Memory Management
SGA
PGA Size

Question 17

DBCA - Create DB - Maintenance Settings

Answer 17

Enable automatic maintenance tasks

Keep enhanced 11g default security settings

Question 18

Create DB - Create Options

Answer 18

Datafiles
Create DB
Save DB template
PW management

Question 19

DBCA - Create Templates

Answer 19

Create DB template
From an existing DB
DBA role

Question 20

DBCA - Delete DB

Answer 20

Select DB = hist
Finish
Yes

Question 21

DBCA - Other Available Tasks

Answer 21

Select operation:
Configure DB options
Configure Automatic Storage Management

Question 22

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

Answer 22

Starting up Oracle DB Instance

Question 23

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

Answer 23

Startup DB Options

Question 24

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

Answer 24

Shutdown DB Options

Question 25

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.

Answer 25

Alert log