Brainscape's Knowledge GenomeTM

Browse over 1 million classes created by top students, professors, publishers, and experts.


See full index

data architecture > midterum > Flashcards

midterum Flashcards

1
Q

Can an instance exist without a database

A

Instance can exist without a database

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
2
Q

Cloud

A

Rent computing power – pay as you go

Smart phones often interact with cloud data

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
3
Q

List of background processes (9)

A 
PMON
SMON
DBWn
CKPT
LGWR
ARCn
MMON
MMAN
LREG
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
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)

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
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

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
6
Q

PMON definition

A

PMON: Process monitor

Regulates all other processes

Cleans up dead processes

Must be alive

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
7
Q

SGA definition

A

System/shared global area

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
8
Q

Can a database exist without an instance

A

Database can exist without an instance

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
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

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
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

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
11
Q

Log buffer

A

Log writer process writes changes to memory fast

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
12
Q

how many databases can an instance access?

A

An instance can only access one database

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
13
Q

what does PMON stand for

A

PMON: Process monitor

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
14
Q

Control file

A

Most important file in oracle (“the brain”)

Information on the state of the database

System change number – SCN

multiplexing

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
15
Q

who owns data dictionary views

A

Owned by SYS

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
16
Q

dedicated server

A

One user process to one server process (typical when DB created during installation)

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
17
Q

where are dynamic performance views derived from

A

Derived from memory and control file

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
18
Q

OFA definition

A

Optimal flexible architecture

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
19
Q

Logical storage

A

o Tablespaces
o Segments
o Extents
o Blocks

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
20
Q

How to decipher data dictionary views

A

DBA_ , User_, All_

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
21
Q

Locking login

A

prohibits the user from accessing the DBMS, but it does not drop login from system

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
22
Q

When is Pfile parameter not needed?

A

Pfile parameter not needed if SPfile exists

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
23
Q

what are dynamic performance views used for?

A

Used to monitor and tune database

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
24
Q

What do online redo log files contain?

A

Checkpoints

Changes (DML –insert update delete, DDL)

Datafile changes – new datafiles

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
25
Q

What two tablespaces must be in the database?

A

System

Sysaux

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
26
Q

When is SGA allocated?

A

SGA is allocated at instance startup

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
27
Q

where are data dictionary views stored?

A

Stored in system tablespace (necessary)

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
28
Q

Shared server

A

Multiple server process are shared with multiple connections and controlled using dispatcher

Best for OLTP

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
29
Q

when is the user process created?

A

Created when application requests a connection to the database

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
30
Q

System table space

A

System
• Holds the data dictionary
• Cannot be taken offline
• Requires instance shutdown to recover it

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
31
Q

what is the server process?

A

Interacts with user process to deal with request

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
32
Q

PCTFree

A

PCTFree – 10%; space in each block is reserved for growth

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
33
Q

Physical storage

A

o Datafiles
o Control files
o Redo log files

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
34
Q

three dynamic performance views

A

o V$CONTROLFILE
o V$PARAMETER
o V$DATABASE

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
35
Q

How to decipher dynamic performance views?

A

V$

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
36
Q

What are the two types of table spaces?

A

Permanent (segments persistent beyond session)

Temporary (transient, no permanent objects)

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
37
Q

PCTUsed

A

PCTUsed – 40%; a block is put backon the free list when its used space falls below PCTUsed

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
38
Q

Two tablespace modes:

A

Read/write

Read only

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
39
Q

full backup

A

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

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
40
Q

what can an instance failure result in?

A

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

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
41
Q

requirements of an OLTP backup

A

 Needs to be available 24x7 x365
 Hot backup, zero loss of data
 Incremental backups with RMAN
 Export, tablespace backup

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
42
Q

how many profiles can a user be assigned to?

A

User can be assigned only 1 profile at a time

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
43
Q

Transient data

A

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

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
44
Q

Instance failures

A

o Result of internal exception within the DBMS

o Operating system failure or other software-related database failure

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
45
Q

Data integrity enforcement through(6)

A 
o	Entity integrity – uniqueness (PK)
o	Data types
o	Default values
o	Check constraints
o	Triggers
o	Referential integrity (FK)
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
46
Q

Whole backup

A

A backup of the control file and all data files that belong to a database

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
47
Q

MTBF definition

A

mean time between failure

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
48
Q

Incremental backup can be:

A
  • differential incremental backup

* cumulative incremental backup

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
49
Q

is a users current session affected by profile change?

A

Users current session isn’t affected by profile change

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
50
Q

Causes of planned downtime

A

o System and database changes
o Data changes – index rebuild
o Application changes - perform

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
51
Q

SQL Injection

A

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

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
52
Q

Incremental backup

A

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.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
53
Q

how can you enforce password complexity

A

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

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
54
Q

types of backups to use:

A

OLTP

Data warehouse

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
55
Q

how many users can a profile be assigned to?

A

A profile can be assigned to any number of users

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
56
Q

what is encryption

A

Encryption transforms data rendering it unreadable to anyone without the decryption key

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
57
Q

Archivelog mode

A

o Needed for DB to create archive logs

o Needed to enable flashback database

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
58
Q

Default roles

A

creator or the DBA can adjust roles for a user using ALTER USER

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
59
Q

Data warehouse

A

Often static (possible read-only)

New data at regular intervals (RMAN incremental backup or rebuild)

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
60
Q

Application (transaction) failures

A

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

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
61
Q

3 areas to examine when working with passwords

A

o Changing password and making it expire
o Enforcing password time limits, history and other
settings
o Enforcing password complexity

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
62
Q

Restoration

A

process of copying files from a backup

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
63
Q

Enable roles

A

user role can enable or disable her role with the SET ROLE command

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
64
Q

what is a database profile?

A

Collection of settings that limit resources and define use of passwords

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
65
Q

Redo logs and archive logs consist of:

A

Redo logs and archive logs consist of records of all transactions made to a database

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
66
Q

Two types of encryption

A

In transit – prohibit network packet sniffing

At rest – in database

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
67
Q

what is the database profile assigned to?

A

Only assigned to user (not to role)

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
68
Q

Types of backups

A

Full
Incremental
Whole

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
69
Q

RMAN

A

oracles recovery manager

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
70
Q

Drop roles

A

DBA can drop the role form the DB and thereby cancel the role for all users who had it

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
71
Q

Backup

A

process of making some kind of copies of parts of a database, or an entire database

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
72
Q

application (transaction) failure results in

A

Application failure results in corrupt data that requires a database restore or recovery

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
73
Q

when can a database profile be assigned to a user

A

Can be assigned when user created

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
74
Q

What is a flashback query?

A

Explicitly references a past time through a time stamp or system change number (SCN)

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
75
Q

Recovery

A

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)

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
76
Q

Persistent data

A

data survives after the process which it was created has ended.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
77
Q

Enterprise architecture

A

 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 …

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
78
Q

Shared pool

A
  • Concept of parsing (select ename from emp;)
  • Library cache – shares stored SQL
  • Data dictionary cache – stored metadata
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
79
Q

SMON definition

A

SMON: System monitor

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
80
Q

system user

A

 Owner of additional internal tables and views

 Has DBA role to perform routine tasks

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
81
Q

Pfile

A

o Static text parameter file

o Init.ora( = database name)

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
82
Q

SMON

A

o SMON: System monitor
 Responsible for instance recovery
 Cleans up temporary files

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
83
Q

Undo data files:

A

 Anatomy of a transaction

 Before-images of data

84
Q

Oracle server

A

When you combine oracle instance with database

85
Q

DBWn Definition

A

DBWn: Database writer

86
Q

SysAus tablespace or SysAus data file:

A

 Automatic workload repository (AWR)

 Second data dictionary

87
Q

Personal architecture

A

 Low to medium power PC
 MS Access, DB2 personal
 Not useful for large applications or shared work
 Low cost

88
Q

what do Temporary data files (tempfiles) store

A

 Stored data segments that overflow from the PGA

 Also stores temporary tables

89
Q

LREG

A

new process that registers databases

90
Q

Database buffer cache

A
  • Blocks read into buffer
  • Users require the same data at times
  • Keeps data in cache based on the LRU
91
Q

CRUD

A

create, replace, update, delete

92
Q

when is SGA allocated/deallocated?

A

startup/shutdown

93
Q

DBWn

A

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
Q

Name 6 types of database users

A
  • Database admin
  • Security officers
  • Network admins
  • Application developers
  • Application administrators
  • Database users
95
Q

Name a V$ view associated with the control file

A

V$Controlfile – tells you about control files (v$ is dynamic performance views)

96
Q

Departmental (workgroup) architecture

A 
	Small to medium sized
	Runs on unix, linux, windows
	Client-server
	Delineation between departmental and enterprise is fuzzy
	Lower cost compared to enterprise
97
Q

How many redo log groups are required by oracle?

A

2

Database must have two redo log groups, should have at least three redo log groups

98
Q

redo log file and archive log file - which one is online/offline?

A

o Redo log file – “online log”

o Archive log file – “offline log”

99
Q

Name 2 dynamic performance (V$) views associated with datafiles?

A
  • V$DBFILE

* V$DATA_FILE

100
Q

what does the SysAus tablespace or SysAus data file perform?

A

Performs metrics

101
Q

Name an initialization parameter associated with the control file

A

SPFILE

102
Q

Large pool

A
  • Benefits of caching – frees up shared pool
  • Used in the shared server architecture
  • Caching for backup and recovery
103
Q

Where should DBA look for explanations of create database command fails?

A

Look at the alert log to determine the reasons for the failure and to determine corrective actions

104
Q

what do Datafiles align with

A

Datafiles align with the database portion of RDBMS

105
Q

Name 11 tasks of a database admin

A
  • 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
Q

PGA

A

Session connection memory for each server process

107
Q

what is a tablespace equivalent to

A

Tablespace = data

108
Q

Streams/Java pools

A
  • Stream pool – support oracle stream

* Java pool – can be used to instantiate java objects

109
Q

What are three major pieces of any database?

A

• 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
Q

PGA definition

A

PGA: Program global area

111
Q

Where do some datafiles map?

A

Some data files map to a table space – logical name for physical files

112
Q

o Bigfile tablespace:

A

One large data or temp file

113
Q

Complete recovery is required to

A

Required to: backup, redo from redo log files, redo from archived files

114
Q

Instance recovery steps

A

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
Q

Incomplete recovery (DBPITR) is required to

A

Required to: backup, redo from redo log files (to PIT), redo from archived files (to PIT)

116
Q

SPfile

A

o Binary server parameter file

o Persistent across shut down and start up

117
Q

System tablespace 4 purposes of undo segments within Undo tablespace

A

Rollback, recover transaction, read consistency flashback

118
Q

3 types of recovery

A

Complete
Incomplete
Instance

119
Q

complete recovery is required when

A

Recovery required: after media failure damages data files or control file, instance failure

120
Q

Nonstandard data block size

A

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
Q

incomplete recovery is required when

A

Recovery required: PIT DB recovery to undo a user error when flashback DB is not possible (application or transaction error)

122
Q

If the datafile is restored from backup, the control file will…

A

If the datafile is restored from backup, then the controlfile will be ahead of the datafile time

123
Q

OMF definition

A

Oracle-Managed Files (OMF)

124
Q

After media failure

A

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
Q

What SQL DML/DDL is possible in a read-only tablespace?

A

Drop

126
Q

control files contain what when referring to datafiles

A

Control files contain pointers to datafiles, dictating where datafiles should be in relation to redo log entries

127
Q

what do redo log files record

A

Redo log files record changes to database data (before datafiles are updated) for RECOVERY

128
Q

what does Media failure (multiplex critical files) damage?

A

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
Q

Two types of privileges and what they do

A

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
Q

Initialization parameters

A

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
Q

availability characteristics

A

Characteristics: reliability, recoverability, timely error detection & continuous operations

132
Q

SYSDBA roles(7)

A

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
Q

OMF

A

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
Q

Stored procedures can be coded that access only…

A

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
Q

SYSOPER roles(6)

A 
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
Q

How does user remove their objects from the recycle bin?

A

o Purge recyclebin;

137
Q

What is contained in the control files? (5)

A
  • 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
Q

Oracle instance

A

comprised of SGA and background processes

139
Q

Why set up test and production environments?

A
  • 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
Q

What are contained in the datafiles?

A

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
Q

PAR

A

performance administration recovery

142
Q

What is contained in the redo log files?

A
  • 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
Q

Purpose of undo segments (4)

A

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
Q

when the Flashback recovery retention period is exceeded, it relies on

A

Redo logs and archive logs

145
Q

What are redo log groups used for?

A

Recovery

146
Q

OLTP definition

A

online transaction processing

147
Q

Flashback recovery relies on

A

Undo data

Recycle bin

148
Q

Name 1 data dictionary view (DBA_) with information about datafiles?

A

DBA_DATA_FILES – info about the datafiles, shows how files are mapped.

149
Q

OLTP

A

online transaction processing – short online transactions (INSERT, UPDATE, DELETE)

150
Q

Startup precedence (oracle will not start if initialization file is not found)

A

 SpfileSID.ora, spfile.ora, initiSID.ora

 Will look for binary file first, look into old binary next and then take SID file last

151
Q

when can DB_block_size be altered

A

Cannot be altered after DB created

152
Q

Oracle architecture parts: Instance, which is a combination of

A

 Background process (CPU)

 Memory structures (RAM)

153
Q

What are the three physical components of the oracle database?

A
  • Datafiles
  • Control files
  • Redo logs
154
Q

ARCn definition

A

Archiver process

155
Q

Oracle architecture parts: Database

A

 Physical component or the files

156
Q

Database startup cannot occur without what?

A

Database startup cannot occur without a control file

157
Q

Name two initialization parameters associated with the SGA

A
  • DB_CACHE_SIZE

* DB_BLOCK_SIZE

158
Q

Chained Rows

A

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
Q

ARCn

A

o ARCn: Archiver process
 NOARCHIVELOG vs ARCHIVELOG mode
 Copies redo logs to archive logs
 Multiple destinations

160
Q

Migrated rows

A

o No space for update
o Entire row moved (row header in old location points to new location)
o Minimized by increasing PCTFree

161
Q

Name the 5 background processes

A

• 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
Q

MMON

A

MMON – Writes out the performance metrics

163
Q

LGWR definition

A

LGWR: Log writer process

164
Q

How many tablespaces can a data file belong to?

A

1

165
Q

Control file contains:

A

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
Q

redo log file

A

 Records database changes
 Used to “roll forward” a database from backup
 Can be overwritten
 duplexing

167
Q

Recovery using redo log files: In minor failures(short power outage)

A

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
Q

MMAN

A

MMAN – automatically manages memory

169
Q

LGWR

A

o LGWR: Log writer process
 Recoverability architecture
 Every change written to files for recovery
 First to memory, then disk as redo logs

170
Q

Non – specialized datafiles

A

 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
Q

What is the purpose of an archivelog file and how does it operate?

A
  • Use to recover a database or update a standby database

* Used when database is running archivelog mode

172
Q

archive log file

A

 Archive log mode – full recoverability

 Redo logs written to archive logs

173
Q

What data dictionary and V$ views are associated with each (archivelog and online redo log)?

A
  • V$ARCHIVED_LOG

* V$LOG

174
Q

System tablespace or system data file:

A

 HEART of the database
 Contains the data dictionary
 Data file number ONE

175
Q

CKPT definition

A

CKPT: Checkpoint process

176
Q

What is archivelog mode?

A
  • 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
Q

Recovery using redo log files: o In major failures(loss of an entire disk)

A

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
Q

CKPT

A

o CKPT: Checkpoint process
 Blocks and buffers – Dirty and clean
 CKPT signals writing of dirty buffers to disk
 Full and incremental checkpoints

179
Q

User process (listening process):

A

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
Q

From shutdown, what are the 3 states a database goes through during startup and which state is the default?

A

• 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
Q

What happens when the first group of redo log groups fills?

A

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
Q

Duplexing

A

allows for speech signals to flow in both directions simultaneously

183
Q

Ways to manage undo data

A

manual or automatic

184
Q

What are the components of the shared pool and purpose of each?

A

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
Q

What are the components of the SGA?

A

• 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
Q

Undo segments are owned by

A

Undo segments are owned by SYS

187
Q

Multiplexing

A

sending multiple signals or streams of information over a communications link at the same time in the form of a single, complex signal

188
Q

Recycle bin

A

o Logical structure within each tablespace (holds dropped tables, indexes, …)
o When full, objects in the recycle bin are deleted in FIFO order

189
Q

To set up automatic undo management mode:

A
  • Set UNDO_MANAGEMENT parameter to AUTO(default)

* Create an undo tablespace (undo_tablespaces)

190
Q

What are the four shutdown modes?

A

• 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
Q

Undo_retention

A

 Minimum time that undo information is retained for queries

 Default is 900 seconds

192
Q

Undo data contains

A

Undo data contains before image of the data

193
Q

What is the smallest logical unit of oracle database storage?

A

Data blocks are the smallest units of storage that oracle can use or allocate

194
Q

Redo log buffer written by LGWR to online redo log file when:

A 
	Transaction issues a COMMIT command
	Redo log buffer is one-third full
	Every 3 seconds
	A checkpoint occurs
	Alter system switch logfile
195
Q

Log switch triggers

A

Triggers a checkpoint to aid in recovery

196
Q

What is the purpose of the online redo log

A

Consists of two or more reallocated files that store all changes made to the database as they occur

197
Q

SCN definition

A

SCN – system change number

198
Q

Log switch occurs when

A

Log switch occurs when LGWR stops writing a log group and writes another

199
Q

Explain what happens in a log switch and the purpose of the log sequence number

A
  • 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
Q

Viewing redo log file data

A 
o	V$LOG – group#
o	V$LOGFILE – group#, status, member
o	V$ARCHIVED_LOG
o	V$ARCHIVE_DEST
o	V$DATABASE
201
Q

Data control language (DCL) statements comprise of two basic types

A

o Grant – assigns permissions(system/object privilege) to a database user

o Revoke – removes permissions(privilege) from a database user

202
Q

Archived redo logs

A

Save filled groups of redo log files offline

203
Q

how to improve system availability

A

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
Q

Archived logs and redo logs allow you to

A

Archived logs and redo logs allow you to recover the DB from a PIT up to the last commit

205
Q

Database Roles

A

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
Q

Why is it important to multiplex these files (required vs. recommended)?

A

Recommends you multiplex to avoid loss of log file data

data architecture (1 decks)

Brainscape helps you reach your goals faster, through stronger study habits.
© 2024 Bold Learning Solutions. Terms and Conditions