OCP 10g(1z0-042) daily 10 practices-1

1.What are the consequences of executing the SHUTDOWN ABORT command(Choose tow)

A – The database files are synchronized.
B – Uncommitted changes are no rolled back.
C – The database is closed,but the instance is still started.
D – Database buffers and redo buffers are not written to the disk.
E – The data base undergose automatic media recovery during the next startup.

Comment:
Oracle database can be shutdown in the following ways:
–SHUTDOWN NORMAL(default)
Normal database shutdown proceeds with the following conditions:
*No new connections are allowed after the statement is issued.
*Before the database is shut down, the database waits for all currently connected users to disconnect from the database.
The next startup of the database will not require any instance recovery procedures.

–SHUTDOWN IMMEDIATE
Use immediate database shutdown only in the following situations:
*To initiate an automated and unattended backup
*When a power shutdown is going to occur soon
*When the database or one of its applications is functioning irregularly and you cannot contact users to ask them to log off or they are unable to log off
To shut down a database immediately, use the SHUTDOWN command with the IMMEDIATE clause:

Immediate database shutdown proceeds with the following conditions:

*No new connections are allowed, nor are new transactions allowed to be started, after the statement is issued.
*Any uncommitted transactions are rolled back. (If long uncommitted transactions exist, this method of shutdown might not complete quickly, despite its name.)
*Oracle Database does not wait for users currently connected to the database to disconnect.
The database implicitly rolls back active transactions and disconnects all connected users.
The next startup of the database will not require any instance recovery procedures.

–SHUTDOWN TRANSACTIONAL
When you want to perform a planned shutdown of an instance while allowing active transactions to complete first,
use the SHUTDOWN command with the TRANSACTIONAL clause

Transactional database shutdown proceeds with the following conditions:

*No new connections are allowed, nor are new transactions allowed to be started, after the statement is issued.
*After all transactions have completed, any client still connected to the instance is disconnected.
*At this point, the instance shuts down just as it would when a SHUTDOWN IMMEDIATE statement is submitted.

The next startup of the database will not require any instance recovery procedures.
A transactional shutdown prevents clients from losing work, and at the same time, does not require all users to log off.

–SHUTDOWN ABORT
You can shut down a database instantaneously by aborting the database instance. If possible, perform this type of shutdown only in the following situations:
The database or one of its applications is functioning irregularly and none of the other types of shutdown works.

*You need to shut down the database instantaneously (for example, if you know a power shutdown is going to occur in one minute).
*You experience problems when starting a database instance.
When you must do a database shutdown by aborting transactions and user connections, issue the SHUTDOWN command with the ABORT clause.

An aborted database shutdown proceeds with the following conditions:
*No new connections are allowed, nor are new transactions allowed to be started, after the statement is issued.
*Current client SQL statements being processed by Oracle Database are immediately terminated.
*Uncommitted transactions are not rolled back.
*Oracle Database does not wait for users currently connected to the database to disconnect. The database implicitly disconnects all connected users.

The next startup of the database will require instance recovery procedures.
Shutdown Timeout

NOTE
To initiate database shutdown, use the SQL*Plus SHUTDOWN command. Control is not returned to the session that initiates a database shutdown until shutdown is complete.
Users who attempt connections while a shutdown is in progress receive a message like the following:ORA-01090: shutdown in progress – connection is not permitted
You cannot shut down a database if you are connected to the database through a shared server process.
Shutdown modes that wait for users to disconnect or for transactions to complete have a limit on the amount of time that they wait.
If all events blocking the shutdown do not occur within one hour, the shutdown command cancels with the following message:
ORA-01013: user requested cancel of current operation.

(Answer:B,D)

2.View the Exhibit and examine the ouput.
Which statement describes the conclusion?
A – The users should use bind variables instead of literals in the query
B – The dictionary cache is consuming more space than the libary cache
C – The shared pool size should be increased to accommodate the SQL statements
D – Preparing indexes on the tables used in the SQL statements would improve the library cache performance
(Answer:A)
Comment:
About bind varilables:http://www.akadia.com/services/ora_bind_variables.html

3.Your database has two control files.three redo log file groups with two members in each group.
Failure of which file would cause a instance to shut down?
A – any control file
B – any archive log file
C – one of the redo log members
D – loss of the initialization parameter file
E – any data file belonging to the default permanent tablespace
(Answer:A)
Comment:
The instance recognizes and opens all the listed control files specified in the parameter file during startup,
and the instance writes to and maintains all listed control files during database operation.If one of the control
file is lost or damaged,the instance will shut down,but you can recover the control file via the mirror.

4.You are using the backup scheduler in Enterprise Manger(EM) to schedule a backup of you database.
Which type of script dose the backup scheduler generate?
A – SQL script
B – PL/SQL script
C – Operating System(OS) script
D – Enterprise Manger(EM) script
E – Recovery Manger(RMAN) script
(Answer:E)

5.A constraint in a table is defined with the INITIALLY IMMEDIATE clause.You executed the ALTER TABLE command
with ENABLE VALIDATE option to enable the constraint that was disabled.What are the two effects of this command?
(choose two)
A – It fails if any existing row violates the constraint.
B – It dose not validate the existing data in the table.
C – It enables the constraint to be enforced at the end of each transaction
D – it prevents insert, update,and delete operations on the table while the constraint is in the process of being enable.

Comment:
Integrity constraints are rules that restrict the values for one or more columns in a table.
You can specify that a constraint is enabled (ENABLE) or disabled (DISABLE).
If a constraint is enabled, data is checked as it is entered or updated in the database,
and data that does not conform to the constraint is prevented from being entered. If a constraint is disabled,
then data that does not conform can be allowed to enter the database.
Additionally, you can specify that existing data in the table must conform to the constraint (VALIDATE).
Conversely, if you specify NOVALIDATE, you are not ensured that existing data conforms.
An integrity constraint defined on a table can be in one of the following states:

* ENABLE, VALIDATE
* ENABLE, NOVALIDATE
* DISABLE, VALIDATE
* DISABLE, NOVALIDATE

(Answer:A,D)

6.Which three statements are correct about temporary tables?(choose three)
A – Indexes and views can be created on temporary tables.
B – Both the data and the structure of temporary tables can be exported
C – Temporary tables are always created in a user’s temporary tablespace.
D – The data inserted into a temporary table in a session is available to other sessions.
E – Data manipulation languate(DML) locks are never acquired on the data of temporary tables.
(Answer:A,C,E)

Comment:
It is also possible to create a temporary table. The definition of a temporary table is visible to all sessions,
but the data in a temporary table is visible only to the session that inserts the data into the table.
Use the CREATE GLOBAL TEMPORARY TABLE statement to create a temporary table.
The ON COMMIT clause indicate if the data in the table is transaction-specific (the default) or session-specific.
Temporary tables are useful in applications where a result set is to be buffered, perhaps because it is constructed by running
multiple DML operations. For example, consider the following:
A Web-based airlines reservations application allows a customer to create several optional itineraries.
Each itinerary is represented by a row in a temporary table. The application updates the rows to reflect changes in the itineraries.
When the customer decides which itinerary she wants to use, the application moves the row for that itinerary to a persistent table.
During the session, the itinerary data is private. At the end of the session, the optional itineraries are dropped.
This statement creates a temporary table that is transaction specific:

CREATE GLOBAL TEMPORARY TABLE admin_work_area
(startdate DATE,
enddate DATE,
class CHAR(20))
ON COMMIT DELETE ROWS;

Indexes can be created on temporary tables. They are also temporary and the data in the index has the same session or transaction
scope as the data in the underlying table.Unlike permanent tables, temporary tables and their indexes do not automatically allocate
a segment when they are created. Instead, segments are allocated when the first INSERT (or CREATE TABLE AS SELECT) is performed.
This means that if a SELECT, UPDATE, or DELETE is performed before the first INSERT, the table appears to be empty.
DDL operations (except TRUNCATE) are allowed on an existing temporary table only if no session is currently bound to that temporary table.
If you rollback a transaction, the data you entered is lost, although the table definition persists.
A transaction-specific temporary table allows only one transaction at a time. If there are several autonomous transactions in a single
transaction scope, each autonomous transaction can use the table only as soon as the previous one commits.
Because the data in a temporary table is, by definition, temporary, backup and recovery of temporary table data is not available
in the event of a system failure. To prepare for such a failure, you should develop alternative methods for preserving temporary table data.

7.A user wants to connect to the database instnace from an application that is running on a reomote machine.
Which tools should the DBA use to establish the required configuration to ensure that the user is able to
connect to the database instance?(choose two)
A – Data Pump
B – Oracle Net Manger
C – Oracle Enterprice Manger
D – Oracle Universal Installer(OUI)
E – Database Configuration Assistant(DBCA)
(Answer:B,C)

8.Which statement is true about loss of system-critical data files in ARCHIVELOG mode?
A – The data files can be recovered till the last committed transaction.
B – The data files can be recovered without shutting down the database.
C – The data file is taken offline automatically;the database remains open.
D – The data files can be recovered to the time of the most recent backup.
(Answer:A)

9.You require the Oracle server to manage the undo segments and space among various active sessions automatically.
You created an undo tablespace,UNDO_TBS1 in your database.Which two additional steps would you perform to achive this?(choose two)
A – Set the UNDO_RETENTION parameter to 900 or more.
B – Create an initial undo segment in the undo tablespace.
C – Enable the retention guarantee for the undo tablespace.
D – Set the UNDO_TABLESPACE parameter to UNDO_TBS1.
E – Set the UNDO_MANAGEMENT initialization parameter to AUTO.
(Answer:D,E)

10.In the middle of a transaction, a user session was abnormally terminated but the instance is still up and the database
is open.Which two statements are true in this scenario?(choose two)
A – Event Viewer gives more details on the failure
B – The alert log file gives detailed informatoin about the failure.
C – PMON rolls back the transaction and releases the locks.
D – SMON rolls back the transaction and releases the locks.
E – The transaction is rolled back by the next sesion that refers to any of the blocks updated by the failed transaction.
F – Data modified by the transaction up to the last commit before the abnormal termination is retained in the database.

Comment:
The alert log is a special trace file. The alert log of a database is a chronological log of messages and errors,
and includes the following items:

*All internal errors (ORA-600), block corruption errors (ORA-1578), and deadlock errors (ORA-60) that occur
*Administrative operations, such as CREATE, ALTER, and DROP statements and STARTUP, SHUTDOWN, and ARCHIVELOG statements
*Messages and errors relating to the functions of shared server and dispatcher processes
*Errors occurring during the automatic refresh of a materialized view
*The values of all initialization parameters that had nondefault values at the time the database and instance start

Process monitor (PMON)
The process monitor performs process recovery when a user process fails. PMON is responsible for cleaning up the cache
and freeing resources that the process was using. PMON also checks on the dispatcher processes (described later in this table)
and server processes and restarts them if they have failed.

System monitor (SMON)
The system monitor performs recovery when a failed instance starts up again. In a Real Application Clusters database,
the SMON process of one instance can perform instance recovery for other instances that have failed.
SMON also cleans up temporary segments that are no longer in use and recovers dead transactions skipped during system
failure and instance recovery because of file-read or offline errors. These transactions are eventually recovered by
SMON when the tablespace or file is brought back online.
(Answer:C,F)