Brainscape's Knowledge GenomeTM

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


See full index

Oracle > Oracle DB Administration > Flashcards

Oracle DB Administration Flashcards

1
Q

Tools for Administering the Database

A
  • OUI (Oracle Universal Installer)
  • DBCA (Database Configuration Assistant)
  • DBUA (Database Upgrade Assistant)
  • Oracle Net Manager
  • OEM (Oracle Enterprise Manager)
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
2
Q

Which tools can you use to start up an Oracle database?

A
  • SQL Plus
  • OEM
  • RMAN
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
3
Q

When creating a database with SQL script, what would you specify in the script?

A
  • Name of the database.
  • SYS user password.
  • SYSTEM user password.
  • At least three online redologs.
  • Character set and national character set of DB.
  • Location and size of SYSTEM and SYSAUX tbs.
  • Default tablespace.
  • Temporary tablespace.
  • Undo tablespace.
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
4
Q

How would you prepare a response file?

A

Installation media comes with a template response file that can be easily customized. However, the easiest and most reliable way is to use the Oracle Universal Installer in record mode. At completion of the install, the response file will have all the customized options recorded in it.

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

There are 10 identical servers and you want to install Oracle DB on each of them. What would you use to automate the installation process?

A

Best and most efficient way is by using Oracle Universal installer in silent mode. The installations will need to be done with a response file and specify the location. The installation files and response file can be shared among the servers via NFS (Network File System) so that they don’t have to be copied to each server.

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

Describe three different methods you can use to find the version of database software.

A

1) Connect to the DB using SQL Plus. It will show the version once connected.
2) Query the v$version using “SELECT*FROM v$version”.
3) Enterprise Manager will also show the database version under the “General” tab.

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

Explain what each digit means in the Oracle database version number 11.2.0.4.

A
  • 11: shows the major database version.
  • 2: shows the maintenance release number of the software. With each maintenance release, new features are added.
  • 0: fusion middleware number.
  • 4: component-specific release number. Indicates the patch set update that was applied to the software.
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
8
Q

List four possible ways (direct or indirect) to execute a SQL query against an Oracle database.

A

Directly

  • Command line tool which directly executes SQL commands.
  • GUI (Graphical User Interface)

Indirect

  • OEM (Oracle Enterprise Manager). Operations are converted to SQL queries and executed against a database.
  • Write your own program using .NET or JAVA.
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
9
Q

What are the types of shutdown modes of an Oracle database?

A

1) Normal: no new connections are made and the DB is closed after all sessions have disconnected.
2) Immediate: no new connections are permitted and the existing active transactions are rolled back causing all changes to be lost.
3) Transactional: no new connections are made and active transactions are permitted to complete.
4) Abort: DB is shutdown immediately. Instance recovery is required at the next startup.

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

Can you take a snapshot while the DB is open while ensuring no data is written to the data files?

A

Yes by putting the DB in suspend mode. During this mode, Oracle will halt I/O operations to the datafiles until it is reverted back to normal.

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

What is the definition of table in Oracle?

A

The table is the first level of physical unit in the database and is used for storing data into rows and columns.

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

In Oracle terminology, what do you mean by tablespace?

A

The tablespace is a logical storage unit that is used to group related logical structures. This is where all objects of the database will be grouped.

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

When does the SYSTEM tablespace get created?

A

The SYSTEM tablespace is automatically created during database creation. It contains the data dictionary for all of the data.

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

What is a sequence?

A

A sequence is used to generate a serial list of unique numbers for numerical columns of database tables. It can be used on columns for data where we want data to be inserted in a sequential manner.

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

During startup of a database, at which order does Oracle software search a parameter file?

A

Oracle will first try to locate two different versions of the server parameter file. If neither of those files can be located, it will then look for the parameter file which is plain text.

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

What stages does an instance pass while starting up?

A

1) Nomount: instance is started.
2) Mount: instance is started and DB is mounted. Data is not accessible but certain maintenance tasks are permitted.
3) Opened: all data is accessible.

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

What is the definition of transaction?

A
  • A transaction is a sequence of SQL statements that Oracle treats as a single unit of work.
  • A transaction will begin as soon as you connect the database and every SQL DML statement issued becomes apart of this transaction until you are disconnected from the DB or use a COMMIT or ROLLBACK command.
18
Q

Different types of SQL statements.

A
  • DDL (Data Definition Language): Used to define structures. Ex. create, alter, etc.
  • DML (Data Manipulation Language): Used to manipulate data itself. Ex. insert, update, delete, etc.
  • TCL (Transaction Control Language)
  • SCL (Session Control Language)
  • System Control Language
19
Q

Explain the difference between “dropping a table”, “truncating a table”, and “deleting all records” from a table.

A
  • “DROP Table”: drops the invalid indexes and deletes the table definition and records.
  • “Truncate Table”: deletes invalid data values automatically. The DB will mark the table as empty and deleted records will not be recorded in transaction log.
  • “Delete All Records”: delete all records but the details will be recorded in the transaction log file.
20
Q

What is the use of SELECT statement?

A

SELECT statement is used to select the specific values from a table in a database depending on the conditions specified in the SQL query.

21
Q

What is the definition of GROUP BY?

A

The GROUP BY command is similar to SUM, MULTIPLE, etc and without it the sum for each individual group value cannot be calculated.

22
Q

In order to get total records from a table, what is the keyword?

A

The COUNT command is used to find the total number of records in a table.

23
Q

In order to get sorted records from table, what is the keyword?

A

The ORDER BY keyword is used for sorting the results.

24
Q

What is the keyword to get distinct records from a table?

A

The SELECT DISTINCT command allows the user to select distinct values from a table in the database.

25
Q

How can you compare a part of the name rather than the entire name?

A

In order to compare part of the name we use LIKE operator which acts like a regex engine for the database.

26
Q

What is the definition of SQL?

A

SQL is a database computer language designed for managing data in RDBMS (relational database management systems). It includes data insert, query, update, delete, schema creation and modification, and data access control.

27
Q

How would you find out how many users are defined in the password file and what privileges those users have?

A

Using the “v$pwfile_users” query will allow you to view the information about the existing users in the password file.

28
Q

Client said that he forgot the password for “SYSTEM” user of the DB and cannot connect. How would you recover the password?

A

Easiest way is to connect as another user who has “DBA” privileges and simply change the password. The most practical way is to ask the system administrator to logon to the server as the Oracle user or another user that belongs to the “dba” group. Once logged on, you can connect with SYSDBA privileges and reset the password for the user.

29
Q

A user is logged on to a Linux server as root where Oracle DB is running. The oracle is installed at /u01/app/oracle/product/11.2.0.4/dbhome and SID is ORCL. The user wants to connect locally using O/S authentication with SYSDBA privileges. Show the command the user has to execute.

A

User would switch to Oracle user with “su-oracle”. Then vi into the .bash_profile and set the appropriate environment variable, ORACLE_SID=ORCL and ORACLE_HOME=/u01/app/oracle/product/11.2.0.4/dbhome. Lastly connect to the database using /u01/app/oracle/product/11.2.04/dbhome/sqlplus as sysdba.

30
Q

What kind of information can be given while creating a sequence?

A
  • Sequence Name
  • Start With: # sequence will start from.
  • Increment By: increment at which sequence will move.
  • No Cycle: if sequence will start at beginning when it ends.
  • NoCache: how much next sequence # will be cached. (no next sequence will be cached).
31
Q

How do you start your DB automatically after a reboot?

A

1) Using oracle restart which is the recommended way.

2) Using your own script and placing it in the startup of the O/S.

32
Q

Which components of your database environment can be protected by an “Oracle Restart” configuration?

A
  • Database Instances & ASM
  • Oracle Net Listener
  • ASM Disk Groups
  • Database Services
  • ONS (Oracle Notification Services)
33
Q

What do you mean by view and what are its types?

A

A view is a type of virtual table with a query attached to ever view in order to identify the specific rows and columns of the table. Views come in read-only and read-write.

34
Q

How do we use a materialized view?

A

Materialized views are objects that have reduced sets that have been summarized from base tables. They will typically be used in a data warehouse or decision support systems.

35
Q

In Oracle terminology, what is a synonym?

A

A synonym is an identifier that can be used to reference another database object in a SQL statement.

36
Q

How do you temporarily disable further logons?

A

“ALTER SYSTEM enable restricted session.”

To disable:
-“ALTER SYSTEM disable restricted session.”

37
Q

What are the different types of synonyms?

A

Private and public.

38
Q

What are the advantages of synonyms?

A

Synonyms are used to mask the original name and owner of an object and provides public access to an object.

39
Q

What do you understand about a private synonym?

A

A private synonym belongs to a specific schema and can only be accessed by the owner.

40
Q

What do you understand about a public synonym?

A

A public synonym does not belong to any schema and can be accessed by any database user.

41
Q

What do you understand about a private database link?

A

A private DB link is created for specific user. It is only used when the owner specifies a global object name in a SQL statement or in the definition of the owner’s views or procedures.

42
Q

What do you understand about a public database link?

A

A public database link will allow all users access to objects in another DB.

Oracle (9 decks)

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