midterum Flashcards
1
Q
Can an instance exist without a database
A
Instance can exist without a database
2
Q
Cloud
A
Rent computing power – pay as you go
Smart phones often interact with cloud data
3
Q
List of background processes (9)
A
PMON SMON DBWn CKPT LGWR ARCn MMON MMAN LREG
4
Q
SYS
A
Owns most of tables needed to run DB, data dictionary views, packages, and built in procedures
Can perform high level tasks (start up/shut down DB instances, backup/recovery tasks)
5
Q
Is SGA apart of the oracle instance, why?
A
Yes
Everything is a cache
Volatile storage (memory) faster than non-volatile storage (disk)
Shared memory
6
Q
PMON definition
A
PMON: Process monitor
Regulates all other processes
Cleans up dead processes
Must be alive
7
Q
SGA definition
A
System/shared global area
8
Q
Can a database exist without an instance
A
Database can exist without an instance
9
Q
Mobile
A
Runs on smart phone or pervasive device
Specialized version of a departmental or enterprise DBMS
Remote users, not usually connected to the network
Data can be synchronized
10
Q
when can you have multiple instances?
A
You can set up multiple instances to access the same set of files or one database
11
Q
Log buffer
A
Log writer process writes changes to memory fast
12
Q
how many databases can an instance access?
A
An instance can only access one database
13
Q
what does PMON stand for
A
PMON: Process monitor
14
Q
Control file
A
Most important file in oracle (“the brain”)
Information on the state of the database
System change number – SCN
multiplexing
15
Q
who owns data dictionary views
A
Owned by SYS
16
Q
dedicated server
A
One user process to one server process (typical when DB created during installation)
17
Q
where are dynamic performance views derived from
A
Derived from memory and control file
18
Q
OFA definition
A
Optimal flexible architecture
19
Q
Logical storage
A
o Tablespaces
o Segments
o Extents
o Blocks
20
Q
How to decipher data dictionary views
A
DBA_ , User_, All_
21
Q
Locking login
A
prohibits the user from accessing the DBMS, but it does not drop login from system
22
Q
When is Pfile parameter not needed?
A
Pfile parameter not needed if SPfile exists
23
Q
what are dynamic performance views used for?
A
Used to monitor and tune database
24
Q
What do online redo log files contain?
A
Checkpoints
Changes (DML –insert update delete, DDL)
Datafile changes – new datafiles
25
What two tablespaces must be in the database?
System
Sysaux
26
When is SGA allocated?
SGA is allocated at instance startup
27
where are data dictionary views stored?
Stored in system tablespace (necessary)
28
Shared server
Multiple server process are shared with multiple connections and controlled using dispatcher
Best for OLTP
29
when is the user process created?
Created when application requests a connection to the database
30
System table space
System
• Holds the data dictionary
• Cannot be taken offline
• Requires instance shutdown to recover it
31
what is the server process?
Interacts with user process to deal with request
32
PCTFree
PCTFree – 10%; space in each block is reserved for growth
33
Physical storage
o Datafiles
o Control files
o Redo log files
34
three dynamic performance views
o V$CONTROLFILE
o V$PARAMETER
o V$DATABASE
35
How to decipher dynamic performance views?
V$
36
What are the two types of table spaces?
Permanent (segments persistent beyond session)
Temporary (transient, no permanent objects)
37
PCTUsed
PCTUsed – 40%; a block is put backon the free list when its used space falls below PCTUsed
38
Two tablespace modes:
Read/write
Read only
39
full backup
Non-incremental RMAN backup. (full does not refer to how much of database is backed up, refers to the fact the backup is not incremental). You can make a full backup of one data file
40
what can an instance failure result in?
Instance failure can result in corruption of data that requires a recovery but usually such failures do not damage data, so the DBMS simply needs to be restarted to re-establish normal operations
41
requirements of an OLTP backup
Needs to be available 24x7 x365
Hot backup, zero loss of data
Incremental backups with RMAN
Export, tablespace backup
42
how many profiles can a user be assigned to?
User can be assigned only 1 profile at a time
43
Transient data
data that is created within an application session, at end of session it is discarded or reset back to its default and not stored in a database
44
Instance failures
o Result of internal exception within the DBMS
| o Operating system failure or other software-related database failure
45
Data integrity enforcement through(6)
```
o Entity integrity – uniqueness (PK)
o Data types
o Default values
o Check constraints
o Triggers
o Referential integrity (FK)
```
46
Whole backup
A backup of the control file and all data files that belong to a database
47
MTBF definition
mean time between failure
48
Incremental backup can be:
* differential incremental backup
| * cumulative incremental backup
49
is a users current session affected by profile change?
Users current session isn’t affected by profile change
50
Causes of planned downtime
o System and database changes
o Data changes – index rebuild
o Application changes - perform
51
SQL Injection
Form of web hacking whereby SQL statements are specified in the fields of a web form to cause a poorly designed web application to dump database content to the attacker
52
Incremental backup
RMAN backup in which only modified blocks are backed up. Incremental backups are classified by level.
• Level 0 incremental backup preforms same function as full backup.
53
how can you enforce password complexity
Predefined SQL script to verify the complexity of a password
Adjust the PASSWORD_VERIFY_FUNCTION setting in a profile and assign that profile to a user
54
types of backups to use:
OLTP
| Data warehouse
55
how many users can a profile be assigned to?
A profile can be assigned to any number of users
56
what is encryption
Encryption transforms data rendering it unreadable to anyone without the decryption key
57
Archivelog mode
o Needed for DB to create archive logs
| o Needed to enable flashback database
58
Default roles
creator or the DBA can adjust roles for a user using ALTER USER
59
Data warehouse
Often static (possible read-only)
New data at regular intervals (RMAN incremental backup or rebuild)
60
Application (transaction) failures
o Programs or scripts are run at the wrong time, using wrong input or in wrong order
o Sooner an application failure is identified and corrected, the smaller amount of damage
61
3 areas to examine when working with passwords
o Changing password and making it expire
o Enforcing password time limits, history and other
settings
o Enforcing password complexity
62
Restoration
process of copying files from a backup
63
Enable roles
user role can enable or disable her role with the SET ROLE command
64
what is a database profile?
Collection of settings that limit resources and define use of passwords
65
Redo logs and archive logs consist of:
Redo logs and archive logs consist of records of all transactions made to a database
66
Two types of encryption
In transit – prohibit network packet sniffing
At rest – in database
67
what is the database profile assigned to?
Only assigned to user (not to role)
68
Types of backups
Full
Incremental
Whole
69
RMAN
oracles recovery manager
70
Drop roles
DBA can drop the role form the DB and thereby cancel the role for all users who had it
71
Backup
process of making some kind of copies of parts of a database, or an entire database
72
application (transaction) failure results in
Application failure results in corrupt data that requires a database restore or recovery
73
when can a database profile be assigned to a user
Can be assigned when user created
74
What is a flashback query?
Explicitly references a past time through a time stamp or system change number (SCN)
75
Recovery
process of rebuilding a database after some part of a database has been lost, to a desired point in time (for full recovery the database must be archived)
76
Persistent data
data survives after the process which it was created has ended.
77
Enterprise architecture
Designed for scalability and high performance
Multiple users may connect concurrently
Support for very large databases
Supports multiple DB instances and replication
Runs on a high-end machine (mainframe, server)
High cost
OLTP, data warehouse …
78
Shared pool
* Concept of parsing (select ename from emp;)
* Library cache – shares stored SQL
* Data dictionary cache – stored metadata
79
SMON definition
SMON: System monitor
80
system user
Owner of additional internal tables and views
| Has DBA role to perform routine tasks
81
Pfile
o Static text parameter file
| o Init.ora( = database name)
82
SMON
o SMON: System monitor
Responsible for instance recovery
Cleans up temporary files
83
Undo data files:
Anatomy of a transaction
| Before-images of data
84
Oracle server
When you combine oracle instance with database
85
DBWn Definition
DBWn: Database writer
86
SysAus tablespace or SysAus data file:
Automatic workload repository (AWR)
| Second data dictionary
87
Personal architecture
Low to medium power PC
MS Access, DB2 personal
Not useful for large applications or shared work
Low cost
88
what do Temporary data files (tempfiles) store
Stored data segments that overflow from the PGA
| Also stores temporary tables
89
LREG
new process that registers databases
90
Database buffer cache
* Blocks read into buffer
* Users require the same data at times
* Keeps data in cache based on the LRU
91
CRUD
create, replace, update, delete
92
when is SGA allocated/deallocated?
startup/shutdown
93
DBWn
o DBWn: Database writer
Operations are done in RAM
Reads from disk and writes back to disk
The “n” means we can have multiple database writer processes
94
Name 6 types of database users
* Database admin
* Security officers
* Network admins
* Application developers
* Application administrators
* Database users
95
Name a V$ view associated with the control file
V$Controlfile – tells you about control files (v$ is dynamic performance views)
96
Departmental (workgroup) architecture
```
Small to medium sized
Runs on unix, linux, windows
Client-server
Delineation between departmental and enterprise is fuzzy
Lower cost compared to enterprise
```
97
How many redo log groups are required by oracle?
2
| Database must have two redo log groups, should have at least three redo log groups
98
redo log file and archive log file - which one is online/offline?
o Redo log file – “online log”
| o Archive log file – “offline log”
99
Name 2 dynamic performance (V$) views associated with datafiles?
* V$DBFILE
| * V$DATA_FILE
100
what does the SysAus tablespace or SysAus data file perform?
Performs metrics
101
Name an initialization parameter associated with the control file
SPFILE
102
Large pool
* Benefits of caching – frees up shared pool
* Used in the shared server architecture
* Caching for backup and recovery
103
Where should DBA look for explanations of create database command fails?
Look at the alert log to determine the reasons for the failure and to determine corrective actions
104
what do Datafiles align with
Datafiles align with the database portion of RDBMS
105
Name 11 tasks of a database admin
* Installing and upgrading server and application tools
* Allocating system storage
* Creating primary database storage structures (tablespaces)
* Creating primary objects (tables, views, indexes)
* Modifying database structure
* Enrolling users and maintain system security
* Ensuring compliance with oracle license agreements
* Controlling and monitoring user access to databases
* Monitoring and optimizing the performance of database
* Planning for back up and recovery of database information
* Maintaining archived data on tape
* Backing up and restoring database
* Contacting oracle for technical support
106
PGA
Session connection memory for each server process
107
what is a tablespace equivalent to
Tablespace = data
108
Streams/Java pools
* Stream pool – support oracle stream
| * Java pool – can be used to instantiate java objects
109
What are three major pieces of any database?
• Storage
o Disk storage for datafiles
o 3 major pieces of storage in oracle: datafiles,
control files, online redo logs
• Memory
o Called the oracle SDA
• Processes
110
PGA definition
PGA: Program global area
111
Where do some datafiles map?
Some data files map to a table space – logical name for physical files
112
o Bigfile tablespace:
One large data or temp file
113
Complete recovery is required to
Required to: backup, redo from redo log files, redo from archived files
114
Instance recovery steps
1 - Startup command issues from SQL*Plus
2 - Oracle rolls forward redo log transactions to the data files (cache recover)
3 - Oracle uses checkpoint SCNs to determine what needs to be saved to the data file
4 - Oracle uses Undo segments to roll back uncommitted transactions (transaction recovery)
115
Incomplete recovery (DBPITR) is required to
Required to: backup, redo from redo log files (to PIT), redo from archived files (to PIT)
116
SPfile
o Binary server parameter file
| o Persistent across shut down and start up
117
System tablespace 4 purposes of undo segments within Undo tablespace
Rollback, recover transaction, read consistency flashback
118
3 types of recovery
Complete
Incomplete
Instance
119
complete recovery is required when
Recovery required: after media failure damages data files or control file, instance failure
120
Nonstandard data block size
Database can be created with a standard block size (DB_block_size), which cannot be changed without re-creating the database, and up to 4 nonstandard block sizes.
121
incomplete recovery is required when
Recovery required: PIT DB recovery to undo a user error when flashback DB is not possible (application or transaction error)
122
If the datafile is restored from backup, the control file will...
If the datafile is restored from backup, then the controlfile will be ahead of the datafile time
123
OMF definition
Oracle-Managed Files (OMF)
124
After media failure
After media failure, the database will likely be in a state where valid data is unreadable, invalid data is readable or referential integrity is violated
125
What SQL DML/DDL is possible in a read-only tablespace?
Drop
126
control files contain what when referring to datafiles
Control files contain pointers to datafiles, dictating where datafiles should be in relation to redo log entries
127
what do redo log files record
Redo log files record changes to database data (before datafiles are updated) for RECOVERY
128
what does Media failure (multiplex critical files) damage?
Includes damage to the disk storage devices, file system failures, tape degradation or damage and deleted data files
Although less common in practice, damaged memory chips also can cause data corruption
129
Two types of privileges and what they do
o Name 5 system privileges
To preform specific database operations
o Name 5 object privileges
To access specific objects ex. Grant insert on
hr.employee
130
Initialization parameters
o Show parameters log;
o Log_archive_dest_n = archive log file location
o Log_archive_start = true (Sets DB into automatic ARCHIVELOG mode on startup)
131
availability characteristics
Characteristics: reliability, recoverability, timely error detection & continuous operations
132
SYSDBA roles(7)
o Startup/shutdown
o Alter database open/mount/backup or change character set
o Create/drop database
o Create spfile
o Alter database archivelog (complete until Bballw15)
o Alter database recover
o Restricted session privilege
133
OMF
o Automatic creation/dropping of the OS data files and management of redo log and control files, using initialization parameters
o Complies with OFA naming
134
Stored procedures can be coded that access only...
Stored procedures can be coded that access only row and/or column-level subsets of data
Ability to execute these stored procedures can then be granted to users
135
SYSOPER roles(6)
```
o Startup/shutdown
o Alter database open/mount/backup or change character set
o Create spfile
o Alter database archivelog
o Alter database recover (complete)
o Restricted session privilege
```
136
How does user remove their objects from the recycle bin?
o Purge recyclebin;
137
What is contained in the control files? (5)
* Structure of database are all in the control files.
* Database name
* When database was created
* Entire path names of all datafiles
* Checkpoint information for each datafile
138
Oracle instance
comprised of SGA and background processes
139
Why set up test and production environments?
* Test data - You cannot even test data without consent from users/clients (can use VLE with fully function test lab environment)
* Shield existing users
* Performance/ failover
140
What are contained in the datafiles?
Datafiles that hold data, the data is for the tables. Can have indexes stored in data files as well. Rollback segments (undo segments) can also be stored in a data file. Also holds temp data files.
141
PAR
performance administration recovery
142
What is contained in the redo log files?
* DML (all inserts, updates and deletes) – all have 2 pieces: insert will have a delete, update and update, delete and an insert – called redo and undo
* DDL (create, alter, drop)
* Commits
143
Purpose of undo segments (4)
Transaction rollback – restore original data (not roll forward)
Transaction recovery – instance fails before commit
Read consistency – for users accessing the table between the time the update is pending and the time the update has been committed
Flashback queries
144
when the Flashback recovery retention period is exceeded, it relies on
Redo logs and archive logs
145
What are redo log groups used for?
Recovery
146
OLTP definition
online transaction processing
147
Flashback recovery relies on
Undo data
Recycle bin
148
Name 1 data dictionary view (DBA_) with information about datafiles?
DBA_DATA_FILES – info about the datafiles, shows how files are mapped.
149
OLTP
online transaction processing – short online transactions (INSERT, UPDATE, DELETE)
150
Startup precedence (oracle will not start if initialization file is not found)
SpfileSID.ora, spfile.ora, initiSID.ora
| Will look for binary file first, look into old binary next and then take SID file last
151
when can DB_block_size be altered
Cannot be altered after DB created
152
Oracle architecture parts: Instance, which is a combination of
Background process (CPU)
| Memory structures (RAM)
153
What are the three physical components of the oracle database?
* Datafiles
* Control files
* Redo logs
154
ARCn definition
Archiver process
155
Oracle architecture parts: Database
Physical component or the files
156
Database startup cannot occur without what?
Database startup cannot occur without a control file
157
Name two initialization parameters associated with the SGA
* DB_CACHE_SIZE
| * DB_BLOCK_SIZE
158
Chained Rows
o Spans multiple blocks (too big for one block)
o Row head in old location (which still contains data) points continued row at new location
o Minimized by choosing a higher block size or by splitting the table into multiple tables with fewer columns
159
ARCn
o ARCn: Archiver process
NOARCHIVELOG vs ARCHIVELOG mode
Copies redo logs to archive logs
Multiple destinations
160
Migrated rows
o No space for update
o Entire row moved (row header in old location points to new location)
o Minimized by increasing PCTFree
161
Name the 5 background processes
• Database writer process (DBW)
o Writes contents of buffer to data files
• Log writer process (LGWR)
o Writing the redo log buffer to a redo log
file on a disk
• System monitor process (SMON)
o Performs recovery if necessary at
instance start
o Cleaning up temporary segments that
are no longer used
• Process monitor process (PMON)
o Performs process recovery when a user
process fails
o Cleaning up database buffer cache and
freeing resources that the user process was
using
• Checkpoint process (CKPT)
o Updates the headers of all datafiles to record
the details of a checkpoint
o Does not write to disk
162
MMON
MMON – Writes out the performance metrics
163
LGWR definition
LGWR: Log writer process
164
How many tablespaces can a data file belong to?
1
165
Control file contains:
Database name (DB_name)
Names and locations of associated datafiles and online redo log files
Timestamp of the database creation
Current log sequence number – to restore data
Checkpoint information (SCN – System Change Number)
166
redo log file
Records database changes
Used to “roll forward” a database from backup
Can be overwritten
duplexing
167
Recovery using redo log files: In minor failures(short power outage)
In minor failures(short power outage), redo log files are automatically checked during DB startup, and data is restored, from redo log files into datafiles
168
MMAN
MMAN – automatically manages memory
169
LGWR
o LGWR: Log writer process
Recoverability architecture
Every change written to files for recovery
First to memory, then disk as redo logs
170
Non – specialized datafiles
Hold application data in the form of tables
| Maximum size is 4,194,304 times the size of the database block (if block is 8K then max size is 32GB)
171
What is the purpose of an archivelog file and how does it operate?
* Use to recover a database or update a standby database
| * Used when database is running archivelog mode
172
archive log file
Archive log mode – full recoverability
| Redo logs written to archive logs
173
What data dictionary and V$ views are associated with each (archivelog and online redo log)?
* V$ARCHIVED_LOG
| * V$LOG
174
System tablespace or system data file:
HEART of the database
Contains the data dictionary
Data file number ONE
175
CKPT definition
CKPT: Checkpoint process
176
What is archivelog mode?
* Use archivelog mode when you cannot afford to lose data in database in event of a disk failure
* Control file indicates that a group of filled redo log files cannot be reused by the LGWR until the group is archived
177
Recovery using redo log files: o In major failures(loss of an entire disk)
In major failures(loss of an entire disk), data would not be saved from the online redo logs alone. You need full DB backup and archive redo log files that begin after the date of the backup
178
CKPT
o CKPT: Checkpoint process
Blocks and buffers – Dirty and clean
CKPT signals writing of dirty buffers to disk
Full and incremental checkpoints
179
User process (listening process):
Listening process runs and manages sets of instances on machine. Listener document is configured to managed instances of database and map out where data is stored.
180
From shutdown, what are the 3 states a database goes through during startup and which state is the default?
• No mount (Start instance)
o Instance is started but not associated yet
• Mount
o Instance is associated with database by reading its control file
• Open database
o Instance is started and associated with an open database
181
What happens when the first group of redo log groups fills?
Oracle preforms a checkpoint and switches to group 2. When third fills, goes back to first and gets overwritten (unless in archive log – will copy information out to archive redo logs)
182
Duplexing
allows for speech signals to flow in both directions simultaneously
183
Ways to manage undo data
manual or automatic
184
What are the components of the shared pool and purpose of each?
Library cache
o Actual text of SQL statement or PL-SQL statement
o Compiled version of the SQL statement – META Code
o Execution plan (how it will retrieve rows, select statement etc.)
Dictionary cache
o Names of objects that have been touched recently
o Privileges, rolls, access information
185
What are the components of the SGA?
• Database buffer cache
o DIRT (data, indexes, rollback segment (undo segment), temporary files)
o Uses LRU (least recently used)
• Shared pool
o Used to reduce parcing
o Uses LRU
• Java pool
• Large pool
o optional
* Log buffer
* Keep and recycle buffer cache
186
Undo segments are owned by
Undo segments are owned by SYS
187
Multiplexing
sending multiple signals or streams of information over a communications link at the same time in the form of a single, complex signal
188
Recycle bin
o Logical structure within each tablespace (holds dropped tables, indexes, …)
o When full, objects in the recycle bin are deleted in FIFO order
189
To set up automatic undo management mode:
* Set UNDO_MANAGEMENT parameter to AUTO(default)
| * Create an undo tablespace (undo_tablespaces)
190
What are the four shutdown modes?
• Open
o Database opened for this instance
• Close
o Database closed and control filed opened
• No mount
o Control file closed and instance started
• Shutdown
Which database shutdown mode requires instance recovery?
• Shutdown abort
191
Undo_retention
Minimum time that undo information is retained for queries
| Default is 900 seconds
192
Undo data contains
Undo data contains before image of the data
193
What is the smallest logical unit of oracle database storage?
Data blocks are the smallest units of storage that oracle can use or allocate
194
Redo log buffer written by LGWR to online redo log file when:
```
Transaction issues a COMMIT command
Redo log buffer is one-third full
Every 3 seconds
A checkpoint occurs
Alter system switch logfile
```
195
Log switch triggers
Triggers a checkpoint to aid in recovery
196
What is the purpose of the online redo log
Consists of two or more reallocated files that store all changes made to the database as they occur
197
SCN definition
SCN – system change number
198
Log switch occurs when
Log switch occurs when LGWR stops writing a log group and writes another
199
Explain what happens in a log switch and the purpose of the log sequence number
* Log switch is when oracle ends writing to one online redo log file and begins to write to another
* Assigns a new log sequence number every time it switches and the LGWR begins to write it
* During crash, instance or media recovery oracle applies redo log files in ascending order by using the LSN of archived and online redo log files
200
Viewing redo log file data
```
o V$LOG – group#
o V$LOGFILE – group#, status, member
o V$ARCHIVED_LOG
o V$ARCHIVE_DEST
o V$DATABASE
```
201
Data control language (DCL) statements comprise of two basic types
o Grant – assigns permissions(system/object privilege) to a database user
o Revoke – removes permissions(privilege) from a database user
202
Archived redo logs
Save filled groups of redo log files offline
203
how to improve system availability
o Perform routine maintenance while systems remain operational
o Automate DBA functions
o Exploit high availability features of the DBMS
Parallelism
Clustering and data sharing
o Exploit hardware technologies
Storage technologies (RAID)
204
Archived logs and redo logs allow you to
Archived logs and redo logs allow you to recover the DB from a PIT up to the last commit
205
Database Roles
Collection of privileges that is named and assigned to users or another role (groups in other DBMSs). Can be assigned to multiple users as a set rather than individually
206
Why is it important to multiplex these files (required vs. recommended)?
Recommends you multiplex to avoid loss of log file data
