Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> OCP - 9i upgrade certification questions
Hi!
I am preparing for getting my certification upgraded to 9i (no, let's not talk about whether it is useful or not - the company pays for it, so why not go for it?) and I'm currently working with prep questions.
Some questions that I came across and am not perfectly sure what the correct answer is, are:
You have multi-master replication. At site one, you add a row to the
CUSTOMER table. There is an INSERT trigger on this CUSTOMER_TRIGGER
that inserts a row in the CUSTOMER_STATUS table. Both of these tables
are replicated. When the row is inserted, the DML is replicated to
site two, where it also invokes the INSERT trigger and tries to insert
a row in the CUSTOMER_STATUS table at that site. Since the
CUSTOMER_STATUS table will now also get a DML INSERT from site one for
the same customer, it will encounter a duplicate key error.
Referring to the scenario above, how do you prevent the INSERT
trigger from getting fired on site two?
Choice 1
Do not replicate the INSERT trigger at site two.
Choice 2
Always delete the customer from the CUSTOMER_STATUS table and then do
the insert.
Choice 3
Turn off replication in the insert trigger before the insert into
CUSTOMER_STATUS and then turn it back on afterwards
Choice 4
Before inserting into CUSTOMER_STATUS, check to see if the customer
already exists.
Choice 5
Check return value DBMS_REPUTIL.FROM_REMOTE before updating the table
and update only if the value of this variable is false.
Correct answer: 1
An Oracle database used for an OLTP application is encountering the
"snapshot too old" error.
Referring to the scenario above, which database object or objects do
you query in order to set the OPTIMAL parameter for the rollback
segments?
Choice 1
V$ROLLNAME
Choice 2
DBA_ROLL and DBA_ROLLSTAT
Choice 3
V$ROLLSTAT
Choice 4
DBA_ROLLBACK_SEG
Choice 5
V$ROLLNAME and V$ROLLSTAT
Correct answer: 3
The Cache Fusion processes in a Real Application Cluster are encountering problems.
The BACKGROUND_DUMP_DEST is set to $ORACLE_HOME/admin/erpdb/bdump, and
ORACLE_SID is MFRERP.
Referring to the scenario above, what trace file do you check to see
errors and warnings written by these processes?
Choice 1
$ORACLE_HOME/admin/erpdb/bdump/MFRERPsmon.trc
Choice 2
$ORACLE_HOME/admin/erpdb/bdump/MFRERPdbsp.trc
Choice 3
$ORACLE_HOME/admin/erpdb/bdump/MFRERPlck0.trc
Choice 4
$ORACLE_HOME/admin/erpdb/bdump/MFRERPbsp0.trc
Choice 5
$ORACLE_HOME/admin/erpdb/bdump/MFRERPlmd0.trc
Correct answer: 5
You have two large tables with thousands of rows. To select rows from
the table_1, which are not referenced by an indexed common column
(e.g. col_1) in table_2, you issue the following statement:
select * from table_1
where col_1 NOT in (select col_1 from table_2);
This statement is taking a very long time to return its result set.
Referring to the scenario above, which equivalent statement returns
much faster?
Choice 1
select * from table_1
where not exists (select * from table_2)
Choice 2
select * from table_2
where col_1 not in (select col_1 from table_1)
Choice 3
select * from table_1
where not exists (select 'x' from table_2 where col_1 =
table_1.col_1)
Choice 4
select * from table_1
where col_1 in (select col_1 from table_2 where col_1 =
table_1.col_1)
Choice 5
select table_1.* from table_1, table_2
where table_1.col_1 = table_2.col_1 (+)
Correct answer: 3
You have partitioned the table ORDER on the ORDERID column using range
partitioning. You want to create a locally partitioned index on this
table. You also want this index to be unique.
Referring to the scenario above, what is required for the creation
of this unique locally partitioned index?
Choice 1
There can be only one unique locally partitioned index on the table.
Choice 2
The index has to be equipartitioned.
Choice 3
The table's primary key columns should be included in the index key.
Choice 4
The ORDERID column has to be part of the index's key.
Choice 5
A unique partitioned index on a table cannot be local.
Correct answer: 2
You are asked to choose a naming method for Oracle servers. The
infrastructure team uses a third-party NIS-based naming service.
Referring to the scenario above, what naming method do you choose
that complies with your infrastructure team's requirements?
Choice 1
Host Naming
Choice 2
Local Naming
Choice 3
External Naming
Choice 4
Directory Naming
Choice 5
Oracle Names
Correct answer: 5
What data are updated via a multi-table view?
Choice 1
Data from only one table at a time can be updated via a multi-table
view.
Choice 2
Data from all tables included in a multi-table view can be updated
via the view.
Choice 3
You cannot update data via a view.
Choice 4
Only primary keys can be updated via a multi-table view.
Choice 5
Only data from key preserved tables can be updated via a multi-table
view.
Correct answer: 2
You want to create a Shared Public Fixed User Database link.
Referring to the scenario above, which one the following statements
do you execute?
Choice 1
CREATE PUBLIC DATABASE LINK sales CONNECT TO scott IDENTIFIED BY
tiger USING 'sales_us';
Choice 2
CREATE PUBLIC DATABASE LINK sales CONNECT TO scott NOT IDENTIFIED
USING 'sales_us';
Choice 3
CREATE SHARED PUBLIC DATABASE LINK sales.us.americas.acme_auto.com
CONNECT TO scott IDENTIFIED BY tiger AUTHENTICATED BY anupam
IDENTIFIED BY bhide USING 'sales_us';
Choice 4
CREATE PUBLIC DATABASE LINK sales CONNECT TO scott GLOBALLY
IDENTIFIED USING 'sales_us;
Choice 5
CREATE DATABASE LINK sales.us.americas.acme_auto.com CONNECT TO scott
IDENTIFIED BY tiger USING 'sales_us';
Correct answer: 1
When using the PARALLEL clause while creating external tables, which
one of the following conditions needs to be met?
Choice 1
The host needs to have async-mode turned on.
Choice 2
The media on which the data file resides must support random
positioning within a data source.
Choice 3
The REJECT_LIMIT needs to be set to zero.
Choice 4
You have to be using Oracle RAC.
Choice 5
The media should support LVM (Logical Volume Manager) when using the
PARALLEL clause.
Correct answer: 1
Which one of the following describes the "Reset database to
incarnation" command used by Recovery Manager?
Choice 1
It is used to undo the effect of a resetlogs operation by restoring
backups of a prior incarnation of the database.
Choice 2
It restores the database to a save point as defined by the version
control number or incarnation number of the database.
Choice 3
It performs a resynchronization of online redo logs to a given
archive log system change number (SCN).
Choice 4
It performs point-in-time recovery when using Recovery Manager.
Choice 5
It restores the database to the initial state in which it was found
when first backing it up via Recovery Manager.
Correct answer: 4
Which one of the following initialization parameters is obsolete in
Oracle 9i?
Choice 1
GC_FILES_TO_LOCKS
Choice 2
FAST_START_MTTR_TARGET
Choice 3
DB_BLOCK_BUFFERS
Choice 4
DB_BLOCK_LRU_LATCHES
Choice 5
LOG_ARCHIVE_DEST
Correct answer: 3
You need to change the archive log mode of Real Application Cluster to
ARCHIVELOG mode.
What server parameter needs to be changed before you perform the
task above?
Choice 1
LOG_ARCHIVE_FORMAT
Choice 2
CLUSTER_ARCHIVELOG
Choice 3
LOG_ARCHIVE_DEST
Choice 4
ARCHIVELOG
Choice 5
CLUSTER_DATABASE
Correct answer: 1 and 3
You are the database administrator for a bank that has multiple
locations in different states. Each location has a copy of the data in
an Oracle database, and all of these databases need to be in
synchronization. A fast response time is critical at any fully
functional site.
Referring to the scenario above, what replication type is needed for
this bank's databases?
Choice 1
Materialized View Replication
Choice 2
Asynchronous Multi-Master Replication
Choice 3
Hybrid Multimaster and Materialized Views
Choice 4
Master Replication
Choice 5
Synchronous Multi-Master Replication
Correct answer: 5
What is supported by LogMiner?
Choice 1
Abstract Data Types
Choice 2
Collections (nested tables and VARRAYs)
Choice 3
Data types LONG and LOB
Choice 4
ROWID Datatype
Choice 5
Index Organized Tables
Correct answer: 5
CREATE TABLE ORDERITEM (ORDERID NUMBER, ITEMID NUMBER)
PARTITION BY HASH(ITEMID) (PARTITION OI_P1, PARTITION OI_P2);
CREATE TABLE ORDERLINEITEM (ORDERID NUMBER, ITEMID NUMBER);
Referring to the sample code above, which one of the following
statements is used to migrate the data in the OI_P1 partition of
ORDERITEM to ORDERLINEITEM and validate the rows?
Choice 1
ALTER TABLE ORDERITEM EXCHANGE PARTITION OLI_P1 WITH TABLE
ORDERLINEITEM WITH VALIDATION;
Choice 2
ALTER TABLE ORDERLINEITEM EXCHANGE PARTITION OI_P1 WITH TABLE
ORDERITEM WITH VALIDATION;
Choice 3
ALTER TABLE ORDERITEM EXCHANGE PARTITION OI_P1 WITH TABLE
ORDERLINEITEM VALIDATE;
Choice 4
ALTER TABLE ORDERITEM EXCHANGE PARTITION WITH TABLE ORDERLINEITEM
WITH VALIDATION;
Choice 5
ALTER TABLE ORDERITEM EXCHANGE PARTITION OI_P1 WITH TABLE
ORDERLINEITEM WITH VALIDATION;
Correct answer: 2
An index-organized table has to be partitioned for performance
improvement reasons. It has been found that hash partitioning will be
the most beneficial for this table. The DBA has also been made aware
that there are frequent updates to the partitioning key of this table.
Referring to the scenario above, what clause do you use to enable
the frequent updates to this table in addition to the hash
partitioning?
Choice 1
PARTITION BY HASH(column_name) ENABLE ROW UPDATE;
Choice 2
PARTITION BY HASH(column_name);
Choice 3
PARTITION BY HASH(column_name) ROW MOVEMENT ENABLE;
Choice 4
PARTITION BY HASH(column_name) ENABLE ROW MOVEMENT;
Choice 5
PARTITION BY HASH(column_name) ENABLE UPDATE;
Correct answer: 4
You need to implement a failover strategy using TAF. You do not have
enough resources to ensure that your backup Oracle instance will be up
and running in parallel with the primary.
Referring to the scenario above, what failover mode do you use?
Choice 1
FAILOVER_MODE=preconnect
Choice 2
FAILOVER_MODE=basic
Choice 3
FAILOVER_MODE=none
Choice 4
FAILOVER_MODE=auto
Choice 5
FAILOVER_MODE=manual
Correct answer: 2
What dynamic view is accessed to find a buffer's state?
Choice 1
X$BH
Choice 2
V$BUFFER
Choice 3
X$KBH
Choice 4
V$BSTAT
Choice 5
V$BH
Correct answer: 5
The network connection between a primary and standby database is
experiencing problems and has been down for more than eight hours.
Meanwhile, the users are using the primary database as usual. The
network connection is restored after eight hours and twenty-five
minutes of downtime.
Referring to the scenario above, what is the first task you need to
do in order to ensure proper activation of the standby database?
Choice 1
Execute "ALTER DATABASE ACTIVATE STANDBY DATABASE" on the standby
database.
Choice 2
Execute "RECOVER AUTOMATIC STANDBY DATABASE" on the standby database.
Choice 3
Copy all the archived logs from the primary to the standby database.
Choice 4
Query the V$ARCHIVE_GAP to find out the sequence numbers of the
ARCHIVE GAP, if indeed there is a gap.
Choice 5
Execute "SHUTDOWN IMMEDIATE" on the primary database.
Correct answer: 4
You are using multi-master asynchronous replication. When a row is inserted, an internal trigger is fired and creates an object. Referring to the scenario above, what is the object called?
Choice 1
Stored procedure
Choice 2
Deferred transaction
Choice 3
Deferred RPC
Choice 4
RPC
Choice 5
IPC
Correct answer: 1
Any ideas?
Thanks,
Helmut
Received on Mon Aug 26 2002 - 04:03:03 CDT