1Z0-052 - Oracle Database 11g: Administration I
Go back to Oracle
Which two kinds of failures make the Data Recovery Advisor (DRA) generate a manual checklist? (Choose two.)
failure because a data file is renamed accidentally
failure due to loss of connectivity for example, an unplugged disk cable
You are using Enterprise Manager to schedule backups for your database. Which type of script would be generated by the backup scheduler?
Recovery Manager (RMAN) script
To make audit information more productive, the DBA executes the following command before starting an audit operation: SQL> ALTER SYSTEM SET AUDIT_TRAIL=DB,EXTENDED SCOPE=SPFILE; Which statement is true regarding the audit record generated when auditing starts after restarting the database?
It contains the SQL text executed by the user and the bind variables used with it.
database, DEVDB, to the production database, PRODDB. A database link devdb.us.oracle.com is created between PRODDB and DEVDB. You execute the following command on the PRODDB database server: $ impdp system/manager directory = DB_DATA dumpfile = schemas.dat schemas = hr flashback_time = "TO_TIMESTAMP('25-08-2007 14:35:00', 'DD-MM-YYYY HH24:MI:SS')" The command fails displaying the following error: ORA-39001: invalid argument value ORA-39000: bad dump file specification ORA-31640: unable to open dump file "/home/oracle/schema/schemas.dat" for read ORA-27037: unable to obtain file status What would you do to overcome the error?
Replace the dumpfile option with the network_link = devdb.us.oracle.com
In which of the scenario will the DBA perform recovery? (Choose all that apply.)
A tablespace is accidentally dropped
The hard disk on which the data files is stored is corrupted
You are in the middle of a transaction updating a very important table. The machine on which a database was running reboots because of power outage. This caused a database instance failure. Which statement is true in this situation?
The uncommitted transaction is rolled back automatically at the next opening of the database.
You plan to move data from a flat file to a table in your database. You decide to use SQL*Loader direct path load method to perform this task. The table in which you plan to load data in an important table having various integrity constraint defined on it. Which constraints will remain enabled by default during this operation? (Choose all that apply.)
In your database, the current status of a PL/SQL procedure has become INVALID. Which action would fix the problem and make the PL/SQL procedure usable?
Recompile the PL/SQL procedure.
Your database instance is started using the server parameter file (SPFILE). You executed a command to change the value of the LOG_BUFFER initialization parameter: ALTER SYSTEM SET LOG_BUFFER=32M SCOPE=BOTH; What would be the outcome of this command?
The command returns an error because the size of the redo log buffer cannot be changed dynamically.
Which statements are true regarding the creation of an incident package file by using the EM Workbench Support? (Choose all that apply.)
You can add SQL test cases to the incident package.
You can add or remove the trace files to the package.
You can create the incremental incident package ZIP file for new or modified diagnostic information for the incident package already created.
Examine the following statement that is used to modify the primary key constraint on the SALES table: SQL> ALTER TABLE SALES MODIFY CONSTRAINT pk DISABLE VALIDATE; Which three statements are true regarding the above command? (Choose three.)
The constraint remains valid.
The index on the constraint is dropped.
It allows the loading of data into the table using SQL *Loader.
Which three operations require undo data? (Choose three.)
Flashing back a transaction
Recovering a failed transaction
Running a read-consistent query
Your database instance is running with full workload after database creation. You have decided to use a fixedsize undo tablespace. You want to use the undo Advisor to estimate the capacity of the undo tablespace. Which two factors must you consider before using the Undo Advisor to estimate the capacity of the undo tablespace? (Choose two.)
The retention period to support flashback
The expected length of the longest-running query
For which database operation would you need the database to be in the MOUNT state?
configuring the database instance to operate in the ARCHIVELOG or NOARCHIVELOG modes
You are working on an instance started using the SPFILE. You want to move the Flash Recovery Area of your database to a new location. You want the Flashback log files to be stored in the new location. Given below are the steps to accomplish the task in random order: 1) Shut down the instance. 2) Change the value of the DB_RECOVERY_FILE_DEST initialization parameter to a new value. 3) Execute the ALTER DATABASE FLASHBACK OFF command. 4) Start up the instance and mount the database. 5) Execute the ALTER DATABASE FLASHBACK ON command. 6) Open the database. Select the correct order in which these tasks need to be performed.
2, 1, 4, 3, 5, 6
Examine the values for the following initialization parameters: FAST_START_MTTR_TARGET=0 LOG_CHECKPOINT_INTERVAL=0 Which two will be the implications of these values in your database? (Choose two.)
The MTTR advisor will be disabled
Automatic checkpoint tuning will be disabled
Which two statements describe good practices for an application developer to reduce locking conflicts in Oracle database? (Choose two.)
Avoid coding unnecessary longrunning transactions.
Allow the database to handle locks in default locking mode.
You are working on a new Oracle Database 11g server, where only the software is installed and no database is created. You plan to create a database on this server using the Database Configuration Assistant (DBCA). Some of the requirements set for your database creation task are: 1. Configure the database to operate in shared server mode. 2. Disable automatic maintenance tasks such as optimizer statistics collection. 3. Configure a naming method to help the remote user connect to the database instance. 4. Use Automatic Storage Management (ASM) for storing the database files. 5. Configure daily database backup to flash recovery area. 6. Configure Enterprise Manager Database Control to manage the database. Which of these requirements can be met while creating the database?
1, 2, 4, 5, and 6
You want to access employee details contained in flat files as part of the EMPLOYEE table. You plan to add a new column to the EMPLOYEE table to achieve this. Which data type would you use for the new column?
You are managing an Oracle Database 11g database with the following backup strategy: 1. On Sunday, an incremental level 0 tape backup is performed 2. Monday through Saturday, a cumulative incremental level 1 tape backup is performed Which two statements are true regarding the backups performed? (Choose two.)
The backup performed in step 1 backs up all blocks that have ever been in use in the database
The backup performed in step 2 copies all the blocks changed since the most recent level 0 backup
You configured the Flash Recovery Area for your database. The database instance has been started in ARCHIVELOG mode and the LOG_ARCHIVE_DEST_1 parameter is not set. What will be the implications on the archiving and the location of archive redo log files?
Archiving will be enabled and the destination for the archived redo log file will be set to the Flash Recovery Area implicitly
Identify two situations in which the block header grows in a data block. (Choose two.)
When row directories need more row entries
When more transaction slots are required than are initially configured
Identify the memory component from which memory may be allocated for: Session memory for the shared serverBuffers for I/O slavesOracle Database Recovery Manager (RMAN) backup and restore operations
You are managing an Oracle Database 11g database. The database is open, and you plan to perform Recovery Manager (RMAN) backups. Which three statements are true about these backups? (Choose three.)
The backups would be inconsistent.
The backups would be possible only if the database is running in ARCHIVELOG mode.
The backups need to be restored and the database has to be recovered in case of a media failure.
The session of user SCOTT receives the following error after executing an UPDATE command on the EMP table: ERROR at line 1: ORA-00060: deadlock detected while waiting for resource On investigation, you find that a session opened by user JIM has a transaction that caused the deadlock. Which two statements are true regarding the session of SCOTT in this scenario? (Choose two.)
SCOTT should perform a COMMIT or ROLLBACK to allow JIM to continue with his transaction.
SCOTT has to reexecute the last command in the transaction after he commits the transaction.
You have two database servers SEMP and SACCT. The database in the SEMP server maintains the employee information and the database in the SACCT server maintains the accounts payable information. The employees submit the expense reports to the accounts payable department. A user of the accounts payable database wants to extract the employee information from the database in the SEMP server for crossverification. Which schema object enables the user to access the information from the remote database?
After performing a clean shut down of the database instance for maintenance, you mount the database and then execute a command to open the database: SQL> ALTER DATABASE OPEN; Which two statements are true? (Choose two.)
The online redo log files and online data files are opened
All the online data files headers are checked for consistency
Your database is open and the LISTENER listener is running. The new DBA of the system stops the listener by using the command: LSNRCTL> STOP What happens to the sessions that are presently connected to the database instance?
The sessions are not affected and continue to function normally.
You execute this command to drop the ITEM table, which has the primary key referred in the ORDERS table: SQL> DROP TABLE scott.item CASCADE CONSTRAINTS PURGE; Which two statements are true about the effect of the command? (Choose two.)
No flashback is possible to bring back the ITEM table.
The dependent referential integrity constraints in the ORDERS table are removed.
These are points that describe the contents of different memory components: 1: Descriptive information or metadata about schema objects that are queried by using SQL statements 2: The runtime area for data manipulation language (DML) or data definition language (DDL) statements 3: Results of SQL queries and PL/SQL functions 4: Executable forms of SQL cursors, PL/SQL programs, and Java classes 5: The information necessary to reconstruct changes made to the database by a transaction Which of these will be stored in the Shared Pool if the necessary configurations are done?
1,3, and 4
You want to enable archiving on your database. Presently, the database is running in NOARCHIVELOG mode. Given below are the steps to accomplish the task in random order: 1. Shut down the instance. 2. Execute the ALTER DATABASE ARCHIVELOG command. 3. Start up the instance and mount the database. 4. Set the DB_RECOVERY_FILE_DEST initialization parameter to $ORACLE_HOME/dest_1. 5. Open the database. Which is the correct sequence of steps for accomplishing the task?
1, 3, 2, 5; 4 not required
In your database instance, the user sessions are connected to the database server from the remote machines. You want to achieve the following for these users: 1. The user account must be locked after four unsuccessful login attempts. 2. The user must be prompted to change the password at regular intervals. 3. The user may not have more than three simultaneous sessions. 4. The user session must automatically be logged off if more than 10 minutes elapsed time used. How would you accomplish the above?
By assigning profiles for the users
Note the functionalities of various background processes: 1: Perform recovery at instance startup. 2: Free the resources used by a user process when it fails. 3: Cleanup the database buffer cache when a process fails. 4: Dynamically register database services with listeners. 5: Monitor sessions for idle session timeout. 6: Cleanup unused temporary segments. 7: Record the checkpoint information in control file. Which option has the correct functionalities listed for a background process?
System Monitor (SMON): 1, 6
You have a large amount of historical data in a flat file. Some analysts in your organization need to query this data in the file. The file is too large to load the data in it into your current database. Which is the most effective method to access this data in your database?
Create an external table and leave the data in the flat file.
Note the following structures in your database server: 1. Extents 2. OS Blocks 3. Tablespace 4. Segments 5. Oracle Data Block Which option has the correct arrangement of these structures from the smallest to the largest?
2, 5, 1, 4, 3
You plan to move data from a flat file to a table in your database. You decide to use SQL*Loader direct path load method to perform this task. The table in which you plan to load data is an important table having various integrity constraints defined on it. Which constraints will remain enabled by default during this operation? (Choose all that apply.)
Examine the values for the following initialization parameters: FAST_START_MTTR_TARGET = 0 LOG_CHECKPOINT_INTERVAL = 0 Which two will be the implications of these values in your database? (Choose two.)
The MTTR advisor will be enabled.
Automatic checkpoint tuning will be disabled.
Automatic Shared Memory Management (ASMM) has been enabled for your database instance. The initialization parameters for the components that are managed by ASMM are not set. After observing the effects of ASMM, you executed the following command: SQL> ALTER SYSTEM SET DB_CACHE_SIZE = 100M; Which statement is true in this scenario?
The minimum memory size for the database buffer cache is set to 100 MB.
The tnsnames.ora file has an entry for the service alias ORCL as follows: ORCL = ( DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 10.156.24.216)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = orcl.oracle.com) ) ) The TNSPING command executes successfully when tested with ORCL, but you are not able to connect to the database instance with the following command: SQL> CONNECT scott/[email protected] What could be the reason for this?
The orcl.oracle.com database service is not registered with the listener.
You are using flat files as the data source for one of your data warehousing applications. To optimize the application performance, you plan to move the data from the flat files to clustered tables in an Oracle database. While migrating the data, you want to have minimal impact on the database performance and optimize the data load operation. Which method would you use to load data into the Oracle database?
Use the conventional path data load of the SQL*Loader utility.
You execute a command to resize a data file, sales.dbf, of size 200 MB in your database: ALTER DATABASE DATAFILE '/remorse/sales.dbf' RESIZE 150M; Which statement is true about this command?
The command succeeds if no extent in the tablespace is currently allocated above the 150 MB boundary of the file.
Which task would you perform before you run Oracle Universal Installer (OUI) in silent or suppressed mode for an installation?
Create the oraInst.loc file.
Your test database is configured to run in NOARCHIVELOG mode. One of the data files in the USERS tablespace is lost due to a media failure. You notice that all the online redo logs have been overwritten since the last backup. What would you do to recover the data file?
Shut down the instance, restore all the database files from the last consistent backup, and restart the database instance.
You are working on a database that must be functioning 24 hours a day, 7 days a week. The database is configured in ARCHIVELOG mode. Which two options do you have for performing user-managed backups? (Choose two.)
You can perform a complete database backup without shutting down the database instance.
You can back up only those data files whose headers are frozen by using ALTER TABLESPACE BEGIN BACKUP or ALTER DATABASE BEGIN BACKUP commands.
You have statistics collected for some selected tables. Your requirement is that the statistics for the tables and all dependent indexes must not be overwritten by further statistics collection until a certain point of time. How would you achieve this?
Lock statistics for the tables.
In a system, large online transaction processing (OLTP) jobs run during the daytime that require a large database buffer cache. In the night, the system supports batch jobs that require a higher value to be set for the large pool. You must simultaneously configure the memory components to accommodate the peak requirement. What would you do to automate this configuration for the memory components with change in mode of working?
Set the SGA_TARGET initialization parameter to a nonzero value.
Which of these is true about undo tablespace?
Undo tablespace is a temporary tablespace
Examine the command: SQL> ALTER USER skd ACCOUNT LOCK; Which two statements are true after the command is executed? (Choose two.)
The SKD user cannot log in to the database instance.
The other users can access the objects owned by the SKD user, on which they have access.
You have an ORDERS table with the following structure: Name Null? Type ------------------------- -------- ------------------------- OID NUMBER(6) ODATE DATE CCODE NUMBER(6) OAMT NUMBER(10,2) The table has data in the ODATE column for all rows. Many orders are placed in a single day. You need to ensure that the ODATE column must contain data for every order in future. Which method would serve the purpose?
Modify the column using the ALTER TABLE...MODIFY command.
You have set Tablespace Full Metrics Threshold values for the USERS tablespace as follows: Warning (%): 90 Critical (%): 95 Which background process is responsible for issuing alerts when the threshold is crossed?
Manageability Monitor process (MMON)