Profile and Privilege Management Quiz and Exercise

1. (TCO 7) A materialized view is one way of creating a(n) _____________. (Points: 3)
        Partition 
        Fragment 
        Aggregation 
        Segment 


2. (TCO 7) To change database default for creating tablespace to bigfile tablespace we need to use ________ command. (Points: 3)
        CREATE TABLESPACE 
       ALTER TABLESPACE
       CREATE DATABASE
        ALTER DATABASE

3. (TCO 7) To improve performance, __________ should not be executed at the same time as _________. (Points: 3)
        batch loads, aggregations
        online transactions, aggregations
        batch operations, online transactions
        batch loads, batch deletes


4. (TCO 5) Apart from limiting password usage, profiles limit ____________. (Points: 3)
        the amount of space a user can use in his or her default tablespace
        access to database tables
        the amount of resources that a user can utilize
        what SQL commands the user can execute


5. (TCO 5) Which of the following CANNOT be restricted by Oracle password management? (Points: 3)
        Two users having the same password
        User reusing an old password
        User attempting to log in with incorrect password
        Frequency with which users are forced to change password


6. (TCO 5) Using the audit functions of Oracle results in creating a(n) ____________. (Points: 3)
        security log
        rollback log
        redo log
        audit trail


7. (TCO 5) In what way does Oracle restrict access to the database and what particular users can do? (Points: 12)

Oracle OOP Quiz and Exercise

1. (TCO 6) ____ means that the user may send the same message to different objects that belong to different classes and yet always generate the correct response. (Points: 3)         Abstract data type         Encapsulation         Polymorphism         Inheritance 2. (TCO 6) The object ____ is the set of values that the object's attributes have at a given time. (Points: 3)         state         method         message         type 3. (TCO 6) Which of the following is NOT a feature of a relational DBMS? (Points: 3)         The system must support types and classes         The system must be able to recover from hardware failure         The system must be able to recover from software failure         The system must support data queries 4. (TCO 6) Abstract datatypes are implemented with ____________ command. (Points: 3)         CREATE TABLE         CREATE CONSTRAINT         CREATE TYPE         CREATE DATATYPE 5. (TCO 6) You typically need to write a PL/SQL block to display data in which of the following? (Points: 3)         relational table         table that contains large objects         nested table         varying array 6. (TCO 6) You want to store up to 3 phone numbers for each customer, and you want to store them within each row data. Which of the following should you use to define data type for column PHONES in table CUSTOMER? (Points: 3)         object type         nested table         varying array         LOB 7. (TCO 6) What is versioning? In what areas of software is it especially useful and important? (Points: 12)

Distributed Database Management Systems Quiz and Exercise

1. (TCO 8) _________ data allocation divides the database into several disjoint parts and stores them at several sites. (Points: 3)
        Centalized 
        Partitioned 
        Homegeneous 
        Heterogeneous 


2. (TCO 8) Which of the following is NOT a refresh option of  a materialized view? (Points: 3)
        fast 
        force 
        read-only 
        complete 


3. (TCO 8) Which of the following factors influences replication? (Points: 3)
        Database content 
        Database size 
        Data style 
        Number of users 


4. (TCO 8) ____ fragmentation refers to the division of a relation into attribute subsets. (Points: 3)
        Data 
        Horizontal 
        Vertical 
        Mixed 


5. (TCO 8) ____ transparency allows a transaction to update data at several network sites. (Points: 3)
        Transaction 
        Distribution 
        Failure 
        Performance 


6. (TCO 8) With ____ data allocation, the database is divided into several disjointed parts and stored at several sites. (Points: 3)
        centralized 
        decentralized 
        partitioned 
        replicated 


7. (TCO 8) Explain "greater difficulty in managing the data environment" as it applies to a distributed DBMS. (Points: 12)

How To Check if Asynchronous I/O is Working On Linux

The /proc/slabinfo file maintains statistics about objects in memory. Some of the structs used by Asynchronous I/O are treated as objects in the virtual memory, so we can look for those structs on slabinfo. The ones related to AIO are named kio*.
We can use following command to determine whether asynchronous IO enabled.

$ cat /proc/slabinfo | grep kio

If Async I/O is enabled then output will be like,
$ cat /proc/slabinfo | grep kio
kioctx 270 270 128 9 9 1 : 252 126
kiocb 66080 66080 96 1652 1652 1 : 252 126
kiobuf 236 236 64 4 4 1 : 252 126

and if Async I/O is disabled then output will be like,

$ cat /proc/slabinfo | grep kio
kioctx 0 0 128 0 0 1 : 252 126
kiocb 0 0 96 0 0 1 : 252 126
kiobuf 0 0 64 0 0 1 : 252 126

In the SLAB allocator there are three different caches involved. The kioctx and kiocb are Async I/O data structures that are defined in aio.h header file. If it shows a non zero value that means async io is enabled.

If you have the source code loaded, you can review it at file aio.h. This file is located under:
/usr/src/linux-/include/linux/aio.h
These data structures are using to track the I/O requests, and are allocated as part of the __init_aio_setup() call in aio.c.

Example strace of dbw0 process with AIO enabled (init.ora parameter filesystemio_options = asynch) shows:

...
io_submit(3071864832, 1, {{0xb7302e34, 0, 1, 0, 21}}) = 1
gettimeofday({1176916625, 58882}, NULL) = 0
io_getevents(-1223102464, 1, 1024, {{0xb7302e34, 0xb7302e34, 8192, 0}}, {600, 0}) = 1
...

Example strace of dbw0 process with AIO disabled (filesystemio_options = none):

...
pwrite64(21, "\6\242\0\0004\21\300\0\220B\243\0\0\0\1\6\207\357\0\0\1"..., 8192, 36077568) = 8192
times(NULL) = 1775653082
times(NULL) = 1775653082
pwrite64(21, "\6\242\0\0<\21\300\0\220B\243\0\0\0\1\6\254\0\0\0\2\0*"..., 8192, 36143104) = 8192
...

Note that, if you are using ASMLib/oracleasm then kioctx or kiocb structures are never used and you should never look for them. In case of ASMLIB asynchronous IO is by default.

WARNING: Oracle process running out of OS kernel I/O resources - Bug 6687381

Problem Description
In the database alert log there is continuous message "WARNING: Oracle process running out of OS kernel I/O resources". In the trace file you will notice following message.

WARNING:io_submit failed due to kernel limitations MAXAIO for process=128 pending aio=81
WARNING:asynch I/O kernel limits is set at AIO-MAX-NR=1048576 AIO-NR=67968
WARNING:Oracle process running out of OS kernel I/O resources

Cause of the Problem
This is Oracle bug. Oracle named this bug as Bug 6687381 or Bug 7523755. This problem only occurs on platforms where the kernel cannot dynamically allocate Async IO descriptors.

Solution of the Problem
Workaround you can disable the asynchronous IO by setting disk_asynch_io to FALSE but that is not recommended because if disk_asynch_io=FALSE, then asynchronous I/O is not enabled, which may have a detrimental effect on performance.

This bug is fixed in following versions.
- 11.1.0.7 (Server Patch Set)
- 10.2.0.5 (Server Patch Set)
- 10.2.0.4.2 (Patch Set Update)
- 10.2.0.4 Patch 26 on Windows Platforms
- 11.2g

So upgrading to your oracle database is the viable solution.

Oracle Database 10g Data Security Exercises

Lab 5 of 6: Data Security (50 Points)

L A B O V E R V I E W

Scenario/Summary

As part of helping Ann understand Oracle database security, we will:

• define a password strategy
• generate an audit trail
• prepare a table access strategy

You will complete this lab using SQL*Plus Worksheet that is available in Oracle Enterprise Manager (OEM) because you will need DBA rights. Log in to OEM: Username - SYS, Password - ORACLE, Connect as - SYSDBA (detailed instruction how to work with OEM in Citrix iLab are located in iLab page under Week 2). Find SQL*Plus Worksheet icon on the tool bar and click on it.

Deliverables

Submit a completed LAB5_Report.doc, including copy/pastes of your SQL commands and their results, to the DropBox for Week 5.

L A B S T E P S

STEP 1: Define a password strategy (15 points)

Oracle provides the ability to set expirations, limit the reuse, and define the complexity of passwords. In addition, accounts can be locked if the password is entered incorrectly too many times.

1. In SQL*Plus Worksheet, connect user SYS, password ORACLE, as SYSDBA, to service .WORLD (for example, if your database is named DB2300, then Service should be DB2300.WORLD).
2. Write and execute SQL command to create profile TEST_PROFILE to limit the number of failed login attempts to 3 in a row.
3. Write and execute the SQL command that changes profile TEST_PROFILE to limit the overall connection time to 3 hours, and idle connection time to 1 hour. Retain previous limit defined in this profile.
4. Write and execute the SQL command that changes profile TEST_PROFILE to make sure that users change their password roughly ever two months. The old password cannot be reused only before it has been changed three times and before roughly half a year passes. Retain previous limits defined in TEST_PROFILE.
5. Change profile for user SCOTT to TEST_PROFILE.

Type your SQL statements and write your rationale into the LAB 5 Report document.

STEP 2: Generate an audit trail (15 points)

Oracle permits audit trails to be generated for session login attempts, access to objects, and activity performed on objects.

1. Make sure that you are connected as user SYS to SQL*PLus Worksheet in OEM (see the beginning of Step 1 in this lab)
2. Display value of the parameter AUDIT_TRAIL. Comment on what does this value mean.
3. Parameter AUDIT_TRAIL is not dynamic, which means that the database has to be shut down and restarted for the change of this parameter to take effect. This is tricky to accomplish using Virtual ilab and you do not have the required permissions. In order to enable auditing, you would have to change the value of this parameter to either TRUE or DB. However, you still can run various auditing commands, only they will not really take effect. Write SQL statements to audit successful and unsuccessful login attempts.
4. Write SQL statement to audit any successful actions performed by user SCOTT.
5. Write SQL statement to audit any successful INSERT, Update Or DELETE performed on table CLIENT.
6. Write SQL statement to audit any activity on the audit trail itself (SYS.AUD$).

Type your SQL statements and copy/paste the output from SQL*Plus into the LAB 5 Report document.

STEP 3: Prepare a table access strategy (20 points)

Consider the tables available to Ann and her peers, both at local and central sites. Also, consider your proposal for implementing a remote database management system at her organization. Based on the functionality available in Oracle, indicate which tables each role below needs and what type of access they need for each table.

• regional training center manager
• regional instructor
• centralized sales manager
• executive over all training centers

Write your recommendation in the LAB 5 Report document, using correct grammar and spelling.

Logical Database Structure Quizes and Exercises

1. TCO H - ____ segments are created during execution of SQL that
needs space to perform sorting or other operations. (Points: 2)
      Data
      Rollback
      Index
      Temporary


2. TCO H - ____ segments are allowed in undo tablespaces (if automatic
undo management) or any permanent tablespace (if manual undo
management). (Points: 2)
      Data
      Rollback
      Index
      Temporary


3. TCO H - A(n) ____ contains a copy of a primary key value from a
parent table. (Points: 2)
      clone key
      foreign key
      constraint
      index


4. TCO H - Undo data is made up of ____ blocks. (Points: 2)
      file
      contiguous
      data
      undo


5. TCO H - The ________ is the smallest unit of storage in Oracle. (Points: 2)
      tablespace
      segment
      oracle Block
      extent


6. TCO H - Oracle recommends that a series of tablespaces be created
to "house" the different types of segments (objects) that the database
will need to deal with to avoid what type of potential problem?
(Points: 2)
      Lack of storage space on the hard drive
      Major I/O bottlenecks with the reading and writing of different
data, indexes, and undo data to a single data file
      Conflicts in naming conventions
      The DBA not being able to monitor and administer storage for a
single datafile


7. TCO H - A segment shares a common characteristic with which Oracle
storage structure, in that they are the only two logical storage
structures that have names? (Points: 2)
      Tablespace
      Oracle Block
      Extent
      Datafile


8. TCO H - The following command was issued on the tablespace
containing the ZIP_CODE table.

DROP TABLESPACE USER_DATA;

What is the result? (Points: 2)
      The tablespace and the ZIP_CODE table are dropped.
      The command fails because the tablespace contains data.
      The command fails because the ZIP_CODE table was not listed in
the command.
      The command fails because the ZIP_CODE table was not listed in
the command.


9. TCO H - Look at this SQL command.

CREATE TABLESPACE USER_NEW
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 5M;

CREATE TABLE INVENTORY_UPDATES
(INVENTORY_NO NUMBER, LAST_CHANGED DATE)
STORAGE (INITIAL 20M NEXT 5M
MAXEXTENTS UNLIMITED PCTFREE 10)
TABLESPACE USERS_NEW;

Which of these statements is false with respect to the INVENTORY_UPDATES table?
(Points: 2)
      The table’s initial extent is 20Mb.
      The INVENTORY_NO column can store numbers with decimals.
      The table’s next extent is 5Mb.
      The PCTFREE setting is ignored.
      None of the above


10. TCO H - You want to create a temporary table that stores rows for
your entire session. Arrange the command lines in the correct order.

1 (TARGET VARCHAR2(30), HITS NUMBER(5), MISSES NUMBER(4,3))

2 ON COMMIT PRESERVE ROWS

3 STORAGE (INITIAL 5M)

4 CREATE GLOBAL TEMPORARY TABLE MYROWS
(Points: 2)
      2, 4, 1, 3
      4, 1, 2, 3
      4, 2, 1, 3
      2, 4, 3, 1