Q. In this scenario, assume that you archive logs 101 to 104 to two archiving destinations, /log1 and /log2. The control file
contains archived log records as follows:
Sequence Filename
--------- ---------------------
101 /log1/log_1_101.arc
101 /log2/log_1_101.arc
102 /log1/log_1_102.arc
102 /log2/log_1_102.arc
103 /log1/log_1_103.arc
103 /log2/log_1_103.arc
104 /log1/log_1_104.arc
104 /log2/log_1_104.arc
You accidentally delete logs 102 and 104 from log 1.
You run this command:
BACKUP ARCHIVELOG FROM SEQUENCE 101 UNTIL SEQUENCE 105; What happens?
A. RMAN aborts and sends an error message to alert log.
B. An error message is returned by RMAN, and the ALTER SYSTEM ARCHIVE LOG CURRENT command must be provided for RMAN to continue using log2.
C. RMAN creates a backup set with log2, than returns an error before moving to the next log file. RMAN resumes once the BACKUP BACKUPSET 102; and BACKUP BACKUPSET 104; commands are provided as appropriate.
D. RMAN automatically continues without any notifications as follows:
Sequence RMAN Searches For RMAN Finds and Backs Up
121 /log1/log_1_101.arc /log1/log_1_101.arc
122 /log1/log_1_102.arc /log2/log_1_102.arc
123 /log1/log_1_103.arc /log1/log_1_103.arc
124 /log1/log_1_104.arc /log2/log_1_104.arc
Answer: D
Q. The EMPLOYEES table is stored in the SAMPLE tablespace. The corresponding IPK_EMP index for the EMPLOYEES table’s primary
key is stored in the INDX tablespace. Out of 12 partitions, only partition P1 of the SALES table is stored in the SAMPLE tablespace. Which objects will be exported by this command?
exp system/manager tablespaces=SAMPLE
A. Only the EMPLOYEES table.
B. Only the EMPLOYEES table and its corresponding primary key index.
C. The EMPLOYEES table and partition P1.
D. The EMPLOYEES table, IPK_EMP index, and the SALES table.
Answer: D
Q. Some data storage and transfer media have fixed-length physical records. When the data records are short, more then one can be stored in a single, physical record to use the storage space efficiently.
Consider this data:
1119 Smith 1120 Yvonne
1121 Albert 1130 Thomas
Which two statements can you use to load the data into the EMP table? (Choose two)
A. INTO TABLE emp WHEN recid = 1 (recid FILTER POSITION(1:1) INTEGER EXTERNAL, deptno POSITION(3:4) INTEGER EXTERNAL, dname POSITION(8:21) CHAR) INTO TABLE emp WHEN recid <> 1 (recid FILLER POSITION(1:1) INTEGER EXTERNAL empno POSITION(3:6) INTEGER EXTERNAL, ename POSITION(8:17) CHAR,deptno POSITION(19:20) INTEGER EXTERNAL)
B. INTO TABLE emp (empno POSITION(1:4) INTEGER EXTERNAL, ename POSITION(6:15) CHAR)
INTO TABLE emp (empno POSITION(17:20) INTEGER EXTERNAL, ename POSITION(21:30) CHAR)
C. INTO TABLE emp WHEN recid = 1 (recid FILLER INTEGER EXTERNAL TERMINATED BY WHITESPACE, deptno INTEGER EXTERNAL TERMINATEDBY WHITESPACE, dname CHAR TERMINATED BY WHITESPACE)
INTO TABLE emp WHEN recid <> 1 (recid FILLER POSITION(1) INTEGER EXTERNAL TERMINATED BY '', empno INTEGER EXTERNAL TERMINATED BY '' ename CHAR TERMINATED BY WHITESPACE, deptno INTEGER EXTERNAL TERMINATED BY '')
D. INTO TABLE emp (emp INTEGER EXTERNAL TERMINATE BY ' ', ename CHAR TERMINATED BY WHITESPACE)
INTO TABLE emp (empno INTEGER EXTERNAL TERMINATED BY ' ', ename CHAR) TERMINATED BY WHITESPACE)
Answer: B, D
Q. A single SQL* Loader input data file might contain records in a variety of formats.Click the Exhibit button and examine the data shown, in which EMP and DEPT records are intermixed (DEPT records are indicated by 1 and EMP records are indicated by 2).
1 50 Manufacturing -- DEPT record
2 1119 Smith 50 -- EMP record
2 1120 Snyder 50
1 60 Shipping
2 1121 Stevens 60
Which two statements can you use to load the data shown in the Exhibit into the DEPT and EMP tables? (Choose two.)
A. INTO TABLE dept WHEN recid = 1 (recid FILLER POSITION(1:1) INTEGER EXTERNAL, deptno POSITION(3:4) INTEGER EXTERNAL,
dname POSITION(8:21) CHAR)
INTO TABLE emp WHEN recid <> 1 (recid FILLER POSITION(1:1)INTEGER EXTERNAL, empno POSITION(3:6) INTEGER EXTERNAL, ename POSITION(8:17) CHAR, deptno POSITION(19:20) INTEGER EXTERNAL)
B. INTO TABLE emp (empno position(1:4) INTEGER EXTERNAL ename position (6:15) char)
INTO TABLE dept (empno POSITION(17:20) INTEGER EXTERNAL, ename position(21:30) char)
C. INTO TABLE dept WHEN recid = 1 (recid FILLER INTEGER EXTERNAL TERMINATED BY WHITESPACE, deptno INTEGER EXTERNAL TERMINATED
BY WHITESPACE, dname CHAR TERMINATED BY WHITESPACE)
INTO TABLE emp WHEN recid <> 1 (recid FILLER POSITION(1) INTEGER EXTERNAL TERMINATED BY ' ' empno INTEGER EXTERNAL TERMINATED BY ' ' ename char TERMINATED BY WHITESPACE deptno INTEGER EXTERNAL TERMINATED BY ' ')
D. INTO TABLE emp (empno INTEGER EXTERNAL TERMINATED BY ' ' ename char TERMINATED BY WHITESPACE)
INTO TABLE dept (empno INTEGER EXTERNAL TERMINATED BY ' ' ename char TERMINATED BY WHITESPACE)
Answer: A, C
Q. Which two RMAN commands initiate a resynchronization of the recovery catalog with the target database control file?
(Choose three)
A. COPY
B. BACKUP
C. CHANGE
D. DELETE
E. CATALOG
Q. Using a bitmap structure to manage database object space has several benefits over using freelist structures. Which three are benefits of managing space using automatic-segment-space managed objects?
(Choose three)
A. It readily responds to dynamic changes for concurrent access.
B. It improves performance and space utilization in a multiple-instance environment.
C. It requires fewer space related options then freelist managed structures.
D. It improves performance for SYSTEM tablespaces where a high degree of concurrency is required.
Q. You created a database by using the Database Configuration Assistant. What must you do to access a user account in the sample schemas?
A. Create the user account and unlock the account.
B. Create the user account and define a password.
C. Unlock the user account and define a password.
D. Set the 07_DICTIONARY_ACCESSIBILITY parameter to TRUE.
Q. If you create your database using Oracle Managed Files (OMF), what is true?
A. You must use Oracle predefined names to identify raw partitions that will hold OMF structures.
B. You must define directories for two online redo log groups and three copies of the control file.
C. If your CREATE DATABASE command fails, any OMF database files that Have already been created will be dropped automatically.
D. You must use only OMF data files when adding new tablespaces or data files to your database in the future.
Q. You want to drop the TBS1 tablespace from your database.You also want to delete the corresponding data files automatically, and not have to do it manually. What should you do?
A. Use the DROP DATAFILE command.
B. Use the DROP TABLESPACE command.
C. Ensure that all database files are Oracle Managed Files before using the DROP TABLESPACE command.
D. Ensure that the DB_FILE_CREATE_DEST initialization parameter is set before using the DROP TABLESPACE command.
Q. Examine this syntax which allocates channels during a parallel image copy:
RMAN> CONFIGURE DEVICE TYPE DISK PARALLELISM 4;
2> COPY
3> datafile 1 TO ‘/BACKUP/datafile1.dbf’,
4> datafile 2 TO ‘/BACKUP/datafile2.dbf’,
5> datafile 3 TO ‘/BACKUP/datafile3.dbf’;
RMAN>COPY
2> datafile 4 TO ‘/BACKUP/datafile4.dbf’;
How many channels are used?
A. 0
B. 1
C. 3
D. 4
Answer: C
Explanation:
Note: 4 channels are allocated, but only 3 are used!
RMAN optimizes automatic channel allocation by leaving automatic channels allocated so long as each new command requires exactly the same channel configuration as the previous command. For example, RMAN can use the same preallocated channels for
the following series of commands:
BACKUP DATAFILE 1;
BACKUP CURRENT CONTROLFILE;
BACKUP ARCHIVELOG ALL;
Q. Users complain SQL statements using a particular index fail. Using DBVERIFY, you find that two separate blocks in the index have become corrupt. The database data files for user data and index are very large. What is the least disruptive recover strategy available in Oracle9i?
A. Rebuild the index using the online option.
B. Take the individual data file offline (not the whole tablespace), restore and recover the data file from backup with RMAN, then set the data file online again.
C. Without setting the individual data file offline, use RMAN with Block Media Recovery to restore and recover only those blocks.
D. Take the individual data file (not the whole tablespace) offline, use RMAN with Block Media Recovery to restore and recover only those blocks, then set the data file online again.
Answer: C
Explanation:
You can use RMAN with Block Media Recovery to restore and recover only those blocks. It is not required to set the individual data file offline. BMR enables you to back up specific blocks in a datafile without taking the database offline. The default backup method is datafile media recovery. You use the new BLOCKRECOVER statement to perform BMR.
Incorrect Answers:
A: With corrupted blocks you cannot rebuild the index. You need restore and recover these blocks.
B: It is not required to take the individual data file offline to perform the blocks restore and recovery.
D: It is not required to take the individual data file offline to perform the blocks restore and recovery.
Q. In the Oracle9i Data Guard architecture, what is the purpose of the Log Transport Services?
A. To transfer redo log information to one or more destinations.
B. To apply redo log records sent from the primary database to a standby database at the receiving location.
C. To synchronize changes to the control files on all standby databases with changes on the primary database when a log switch occurs.
D. To batch archived log files on the primary database until a defined number of checkpoints have been processed and then to distribute the archives to each standby database.
Answer: A
Q. A user is running a long update batch file. Many tables are updated and all the statements are in one transaction. Before the process is finished, the user machine lost power. When the user calls you, what should you do as DBA?
A. Do nothing, the PMON process handles everything.
B. Shut down the database and restore all the tables the user was updating.
C. Log in as DBA, clean up the partial updates in those tables, and do another manual backup.
D. Use LogMiner to find out which records are changed based on the user’s ID, then roll back all those changes.
Answer: A
Explanation:
A process failure is due to the abnormal termination of a process. Oracle handles these types of failures by using a background process called PMON. PMON identifies the terminated session and resets the status of the active transaction table, releases locks, and removes the process ID from the list of active processes.
Incorrect Answers:
B: To fix this problem you don’t have to shut down the database and restore all the tables the user was updating.
C: You don’t need to clean up the partial updates in those tables.
D: It is not required to use LogMiner to find out which records are changed based on the user’s ID, then rollback all changes.
Q. For which two tasks would it be best NOT to use the utility SQL*Loader? (Choose two)
A. When loading selected rows into each table.
B. When loading data from an export dump file.
C. When loading data from disk, tape, or named pipes.
D. When your data is NOT being inserted from a flat file.
E. When using a record management system to access data files.
Answer: B, D
Explanation:
To load data from an export dump file Oracle Import utility will be better than the SQL*Loader utility. Also it is not the best tool when you data are not being inserted from a flat file.
Incorrect Answers:
A: You can load selected rows into each table with the SQL*Loader utility.
C: It will work when loading data from disk, tape, or named pipes.
E: When using a record management system to access data files SQL*Loader utility will be useful.
Q. Your database is running in NOARCHIVELOG mode. Every night there is a backup taken using RMAN. During your startup this morning you received en error regarding DISK 3 on your server. This is the location of your data file for the SYSTEM tablespace. Which option is open to you?
A. The data file needs to be restored by using operating system commands, because a data file movement must take place, and then the database can be recovered from RMAN.
B. The data file needs to be restored by using operating system commands, since a data file movement must take place, and then the database must be recovered manually.
C. From RMAN you can issue the commands RESTORE and RECOVER. RMAN automatically restores the data file on DISK 3 to another disk, based upon utilization statistics.
D. From RMAN you can issue a SET NEWNAME command to update the data file location information in the control file, and then issue the RESTORE and RECOVER commands to recover the database.
Answer: D
Explanation:
RMAN enables you to restore the data files to a non-default location. Relocating the data files
to an alternative location is useful when storage is limited or when you need to reorganize the
database to improve performance. You need to create a RMAN script that will perform the
following actions: setting a new location for the data file, restoring the data file, recovering
the data file.
Incorrect Answers:
A: First you need to set a new location for the data file with SET NEWNAME command.
B: First you need to set a new location for the data file with SET NEWNAME command.
C: First you need to set a new location for the data file with SET NEWNAME command.
Q. The multithreaded agent architecture for heterogeneous services is similar to the Oracle multithreaded server architecture. The principal difference is that it used threads instead of processes. What are these kinds of threads that it uses? (Choose three)
A. TCP
B. Task
C. Monitor
D. Dispatcher
E. Shared Server
F. Shutdown address
Answer: B, C, D
kinds of threads:
(a) A single monitor thread.
(b) Several dispatcher threads.
(c) Several task threads.
Q. Your company has four DBAs. You need to know which DBA added a column to the JOB_HISTORY table. The database cannot be offline during work hours. Which LogMiner command can you use to find out which user made the table change?
A. SELECT username, operation, sql_redo from V$LOGMNR_CONTENTS where operation='DDL';
B. SELECT username, operation, sql_redo from V$LOGMNR_LOGS where operation='DDL';
C. You must wait until after hours, shut the database down, and issue the command EXECUTE DBMS_LOGMNR_D.BUILD to extract the
information from the database.
D. The task cannot be done. LogMiner does not track DDL commands in Oracle9i.
Answer: A
Q. Consider this RMAN command:
RMAN> CONFIGURE RETENTION POLICY CLEAR;
What is the effect of this command?
A. Backups will never expire.
B. It removes any retention policy.
C. The DELETE OBSOLETE command will fail with an error.
D. It sets the retention policy to the default of REDUNDANCY 1.
Answer: D
Explanation:
This command sets retention policy to the default of REDUNDANCY 1.
Incorrect Answers:
A: This command does not mean that backup will never expire.
B: It sets the retention policy to the default of REDUNDANCY 1, does not remove any
retention policy.
C: The DELETE OBSOLETE command will not fail after that command.
Q. You have used the CROSSCHECK BACKUP command to verify that the backups recorded in the RMAN repository actually exist. Which command should you now use to check if any of the files were missing?
A. LIST EXPIRED
B. DELETE EXPIRED
C. DELETE OBSOLETE
D. CHANGE UNCATALOG
Answer: A
Explanation:
To list all the expired backup sets and the associated backup pieces, you must execute the following command:
RMAN> LIST EXPIRED BACKUPSET;
To list all the expired image copies, you must execute the following command:
RMAN> LIST EXPIRED COPY;
Incorrect Answers:
B: This command will not check, but delete all the backup sets and image copies that are expired from the repository.
C: There is no DELETE OBSOLETE command in RMAN. You can check expired backups with REPORT OBSOLETE command and delete them with DELETE EXPIRED RMAN command.
D: The CHANGE UNCATALOG command is used to remove the records of the specified backup sets and image copies from the catalog and updates the control file records status as DELETED.
Q. You received this error message because Oracle Net Services cannot locate the connect descriptor specified in the tnsnames.ora configuration file:
ORA_12154: TNS:could not resolve service name
Which three actions would be appropriate in helping to resolve the error? (Choose three)
A. Verify the naming method is set to host naming.
B. Verify that a tnsnames.ora file exists and that it is accessible.
C. Verify that a listener on the client node exists and has been started.
D. Verify that the tnsnames.ora file is in the location specified by TNS_ADMIN environment variable.
E. Verify the NAMES.DIRECTORY_PATH=(TNSNAMES, HOSTNAME) parameter is set in the initialization parameter file.
F. Verify that the service name specified in your connection string matches a named entry in the tnsnames.ora file.
Answer: B, D, F
Incorrect Answers:
A: Wrong. You are using local naming method to connect to the database if the connect descriptor specified in the
tnsnames.ora configuration file.
C: Wrong. There is no listener existing on the client in Oracle.
E: Wrong. NAMES.DIRECTORY_PATH is specified in the sqlnet.ora file.
Q. You experienced a loss of data files in your database. You did not lose any of your control files. You are going to use RMAN to restore and recover the database. Currently, your instance is shut down. Which sequence of commands is correct to restore and recover your database?
A.
RMAN> STARTUP NOMOUNT;
RMAN> RESTORE DATABASE;
RMAN> RECOVER DATABASE;
RMAN> ALTER DATABASE OPEN;
B.
RMAN> STARTUP MOUNT;
RMAN> RESTORE DATABASE;
RMAN> RECOVER DATABASE;
RMAN> ALTER DATABASE OPEN
C.
RMAN> STARTUP NOMOUNT;
RMAN> RESTORE DATABASE;
RMAN> RECOVER DATABASE;
RMAN> ALTER DATABASE OPEN RESETLOGS;
D.
RMAN> STARTUP MOUNT;
RMAN> RESTORE DATABASE;
RMAN> RECOVER DATABASE;
RMAN> ALTER DATABASE OPEN RESETLOGS;
Answer: B
Explanation:
This sequence provides correct sequence of commands to restore and recover your database.
You can mount the database because you did not lose any of your control files.
Incorrect Answers:
A: You can mount the database because you did not lose any of your control files.
C: You can mount the database because you did not lose any of your control files. Also you
don’t need to open the database with RESETLOGS option because of original control files
existence.
D: You don’t need to open the database with RESETLOGS option because the control files
are exist.
Continue.....