18、You are working on an Oracle Database10gdatabase. Because of data loss, you decided to perform. a Flashback Database operation using the following command:
SQL> FLASHBACK DATABASE TO TIMESTAMP(SYSDATE ?5/24);
Which two statements are true? (Choose two.)
A. SYSDATE should not be used with TIMESTAMP.
B. The database must have multiplexed redo log files.
C. The database must be in the MOUNT state to execute the command.
D. The database must be opened with the RESETLOGS option after the flashback operation.
E. The entire database needs to be restored from the most recent backup before the flashback operation.
Editor’s notes:The database must be mounted in exclusive mode to issue the FLASHBACK DATABASE command and must be opened with the RESETLOGS option when finished.
19、You work as a database administrator at Certkiller .com. In your test database, you find that a user's session is executing a lot of SQL statements, resulting in the generation of a large number of trace files. While investigating the reason, you find that SQL trace has been enabled at the instance level. You want to disable SQL trace, remotely, only for that user session to reduce the volume of trace data being generated. How do you achieve this objective?
A. by setting the SQL_TRACE parameter to FALSE in the parameter file
B. by using DBMS_MONITOR.SESSION_TRACE_DISABLE to disable the tracing for the user session
C. by setting the SQL_TRACE parameter to FALSE by using the ALTER SYSTEM command in the user session
D. by setting the SQL_TRACE parameter to FALSE by using the ALTER SESSION command in the user session
Editor’s notes:
DATABASE_TRACE_DISABLE Procedure
This procedure disables SQL trace for the whole database or a specific instance.
Sql_trace cann’t be changed by alter system for memory.
20、You execute the following command to enable a session in resumable mode:
SQL> ALTER SESSION ENABLE RESUMABLE TIMEOUT 60;
What is the impact of a timeout on the statements being suspended?
A. The statements remain suspended for at least 60 seconds.
B. The statements are suspended for 60 seconds and then they are executed.
C. The suspended statements error out if the problem is not rectified within 60 seconds.
D. The statements are automatically suspended 60 seconds after an error is received, and then attempt to execute normally again.
Editor’s notes:
I just want to say that the answer C is strict and the other answers can not be supposed to be worng.
From Oracle9i, Oracle supply resumable space allocation to enable you to suspend large operations instead of receiving an error, it gives you a chance to fix the problem while the operation is suspended.
Resumable Space Allocation applies to following three conditions:
·out of space
·maximum extents reached
·Space quota exceeded
Web site associate:
http://space.itpub.net/?uid-14284241-action-viewspace-itemid-406954
http://www.cnblogs.com/minbear/archive/2008/04/22/1165135.html
21、You find that the execution time of reports in your datawarehouse application is significantly high. You suspect the lack of indexes to be the reason for the degradation in performance. Which advisory component would you refer to, in order to determine the appropriate indexes?
A. Memory Advisor
B. Segment Advisor
C. SQL Access Advisor
D. Automatic Workload Repository (AWR)
E. Automatic Database Diagnostic Monitor (ADDM)
Editors’ notes:
The SQL Access Advisor makes suggestions about indexes and materialized view which might improve system performance. This article describes how to use the SQL Access Advisor in Oracle10g
Web site associate:
http://sixfeetunder.itpub.net/post/80/2292
22、You work as a database administrator at Certkiller .com. You are working in an online transaction processing (OLTP) environment. You realize that the salary for an employee, John, has been accidentally modified in the EMPLOYEEStable. Two days ago, the data was in the correct state. Flashback logs generated during last two days are available in the flash recovery area. Which option would you choose to bring the data to the correct state while ensuring that no other data in the same table is affected?
A. perform. point-in-time recovery
B. perform. a Flashback Table operation to restore the table to the state it was in two days ago
C. perform. a Flashback Database operation to restore the database to the state it was in two days ago
D. perform. Flashback Versions Query and Flashback Transaction Query to determine all the necessary undo SQL statements, and then use them for recovery
Editor’s notes:
Flashback version query and flashback transaction query both use undo segments.
flashback transaction query use views flashback_transaction_query.
Example:
Using Flashback Version Query
You use a Flashback Version Query to retrieve the different versions of specific rows that existed during a given time interval. A new row version is created whenever a COMMIT statement is executed.
You specify a Flashback Version Query using the VERSIONS BETWEEN clause of the SELECT statement. Here is the syntax:
VERSIONS {BETWEEN {SCN | TIMESTAMP} start AND end}
where start and end are expressions representing the start and end of the time interval to be queried, respectively. The interval is closed at both ends: the upper and lower limits specified (start and end) are both included in the time interval.
The Flashback Version Query returns a table with a row for each version of the row that existed at any time during the time interval you specify. Each row in the table includes pseudocolumns of metadata about the row version, described in Table 15-1. This information can reveal when and how a particular change (perhaps erroneous) occurred to your database.
A given row version is valid starting at its time VERSIONS_START* up to, but not including, its time VERSIONS_END*. That is, it is valid for any time t such that VERSIONS_START* <= t < VERSIONS_END*. For example, the following output indicates that the salary was 10243 from September 9, 2002, included, to November 25, 2003, not included.
VERSIONS_START_TIME VERSIONS_END_TIME SALARY
------------------- ----------------- ------
09-SEP-2003 25-NOV-2003 10243
Here is a typical Flashback Version Query:
SELECT versions_startscn, versions_starttime,
versions_endscn, versions_endtime,
versions_xid, versions_operation,
name, salary
FROM employee
VERSIONS BETWEEN TIMESTAMP
TO_TIMESTAMP('2003-07-18 14:00:00', 'YYYY-MM-DD HH24:MI:SS')
AND TO_TIMESTAMP('2003-07-18 17:00:00', 'YYYY-MM-DD HH24:MI:SS')
WHERE name = 'JOE';
Pseudocolumn VERSIONS_XID provides a unique identifier for the transaction that put the data in that state. You can use this value in connection with a Flashback Transaction Query to locate metadata about this transaction in the FLASHBACK_TRANSACTION_QUERY view, including the SQL required to undo the row change and the user responsible for the change - see "Using Flashback Transaction Query".
Using Flashback Transaction Query
A Flashback Transaction Query is a query on the view FLASHBACK_TRANSACTION_QUERY. You use a Flashback Transaction Query to obtain transaction information, including SQL code that you can use to undo each of the changes made by the transaction.
As an example, the following statement queries the FLASHBACK_TRANSACTION_QUERY view for transaction information, including the transaction ID, the operation, the operation start and end SCNs, the user responsible for the operation, and the SQL code to undo the operation:
SELECT xid, operation, start_scn,commit_scn, logon_user, undo_sql
FROM flashback_transaction_query
WHERE xid = HEXTORAW('000200030000002D');
As another example, the following query uses a Flashback Version Query as a subquery to associate each row version with the LOGON_USER responsible for the row data change.
SELECT xid, logon_user FROM flashback_transaction_query
WHERE xid IN (SELECT versions_xid FROM employee VERSIONS BETWEEN TIMESTAMP
TO_TIMESTAMP('2003-07-18 14:00:00', 'YYYY-MM-DD HH24:MI:SS') AND
TO_TIMESTAMP('2003-07-18 17:00:00', 'YYYY-MM-DD HH24:MI:SS'));
Flashback Transaction Query and Flashback Version Query: Example
This example demonstrates the use of a Flashback Transaction Query in conjunction with a Flashback Version Query. The example assumes simple variations of the employee and departments tables in the sample hr schema.
In this example, a DBA carries out the following series of actions in SQL*Plus:
connect hr/hr
CREATE TABLE emp
(empno number primary key, empname varchar2(16), salary number);
INSERT INTO emp VALUES (111, 'Mike', 555);
COMMIT;
CREATE TABLE dept (deptno number, deptname varchar2(32));
INSERT INTO dept VALUES (10, 'Accounting');
COMMIT;
At this point, emp and dept have one row each. In terms of row versions, each table has one version of one row. Next, suppose that an erroneous transaction deletes employee id 111 from table emp:
UPDATE emp SET salary = salary + 100 where empno = 111;
INSERT INTO dept VALUES (20, 'Finance');
DELETE FROM emp WHERE empno = 111;
COMMIT;
Subsequently, a new transaction reinserts employee id 111 with a new employee name into the emp table.
INSERT INTO emp VALUES (111, 'Tom', 777);
UPDATE emp SET salary = salary + 100 WHERE empno = 111;
UPDATE emp SET salary = salary + 50 WHERE empno = 111;
COMMIT;
At this point, the DBA detects the application error and needs to diagnose the problem. The DBA issues the following query to retrieve versions of the rows in the emp table that correspond to empno 111. The query uses Flashback Version Query pseudocolumns.
connect dba_name/password
SELECT versions_xid XID, versions_startscn START_SCN,
versions_endscn END_SCN, versions_operation OPERATION,
empname, salary FROM hr.emp
VERSIONS BETWEEN SCN MINVALUE AND MAXVALUE
where empno = 111;
XID START_SCN END_SCN OPERATION EMPNAME SALARY
---------------- ---------- --------- ---------- ---------- ----------
0004000700000058 113855 I Tom 927
000200030000002D 113564 D Mike 555
000200030000002E 112670 113564 I Mike 555
3 rows selected
The results table reads chronologically, from bottom to top. The third row corresponds to the version of the row in emp that was originally inserted in the table when the table was created. The second row corresponds to the row in emp that was deleted by the erroneous transaction. The first row corresponds to the version of the row in emp that was reinserted with a new employee name.
The DBA identifies transaction 000200030000002D as the erroneous transaction and issues the following Flashback Transaction Query to audit all changes made by this transaction:
SELECT xid, start_scn START, commit_scn COMMIT,
operation OP, logon_user USER,
undo_sql FROM flashback_transaction_query
WHERE xid = HEXTORAW('000200030000002D');
XID START COMMIT OP USER UNDO_SQL
---------------- ----- ------ -- ---- ---------------------------
000200030000002D 195243 195244 DELETE HR insert into "HR"."EMP"
("EMPNO","EMPNAME","SALARY") values ('111','Mike','655');
000200030000002D 195243 195244 INSERT HR delete from "HR"."DEPT"
where ROWID = 'AAAKD4AABAAAJ3BAAB';
000200030000002D 195243 195244 UPDATE HR update "HR"."EMP"
set "SALARY" = '555' where ROWID = 'AAAKD2AABAAAJ29AAA';
000200030000002D 195243 113565 BEGIN HR
4 rows selected
The rightmost column (undo_sql) contains the SQL code that will undo the corresponding change operation. The DBA can execute this code to undo the changes made by that transaction. The USER column (logon_user) shows the user responsible for the transaction.
A DBA might also be interested in knowing all changes made in a certain time window. In our scenario, the DBA performs the following query to view the details of all transactions that executed since the erroneous transaction identified earlier (including the erroneous transaction itself):
SELECT xid, start_scn, commit_scn, operation, table_name, table_owner
FROM flashback_transaction_query
WHERE table_owner = 'HR' AND
start_timestamp >=
TO_TIMESTAMP ('2002-04-16 11:00:00','YYYY-MM-DD HH:MI:SS');
XID START_SCN COMMIT_SCN OPERATION TABLE_NAME TABLE_OWNER
---------------- --------- ---------- --------- ---------- -----------
0004000700000058 195245 195246 UPDATE EMP HR
0004000700000058 195245 195246 UPDATE EMP HR
0004000700000058 195245 195246 INSERT EMP HR
000200030000002D 195243 195244 DELETE EMP HR
000200030000002D 195243 195244 INSERT DEPT HR
000200030000002D 195243 195244 UPDATE EMP HR
6 rows selected
22、These are the details about V$FLASHBACK_DATABASE_STAT:
SQL> DESC v$FLASHBACK_DATABASE_STAT
Name Null? Type
------------- -------- --------------
BEGIN_TIME DATE
END_TIME DATE
FLASHBACK_DATA NUMBER
DB_DATA NUMBER
REDO_DATA NUMBER
ESTIMATED_FLASHBACK_SIZE NUMBER
Which two statements regarding the V$FLASHBACK_DATABASE_STATview are
true? (Choose two.)
A. BEGIN_TIME is the time at which Flashback logging is enabled.
B. END_TIME is the time at which the query is executed on the view.
C. REDO_DATA is the number of bytes of redo data written during the interval.
D. This view contains information about flashback data pertaining to the last 24 hours.
E. FLASHBACK_DATA is the amount of flashback data generated since the database was opened.
Editor’s notes:
The default interval of the flashback database is 24 hours and depend on the parameter vaule db_flashback_retention_target.
23、Which two statements are correct regarding the Oracle Flashback Drop feature? (Choose two.)
A. Recycle bin exists for the tables only in non-SYSTEM, locally managed tablespaces.
B. You can flash back a dropped table provided row movement has been enabled on the table.
C. If you drop an index before dropping its associated table, then the recovery of the index is not supported when you flash back the dropped table.
D. When you execute the DROP TABLESPACE INCLUDING CONTENTS command, the objects in the tablespace are places in the recycle bin.
E. When a dropped table is moved to the recycle bin, only the table is renamed to a system-generated name; its associated objects and constraints are not renamed.
F. If you drop a table that is protected by the recycle bin, then associated bitmap-joined indexes and materialized view logs are also stored in the recycle bin.
Editor’s notes:
The recycle bin is actually a data dictionary table containing information about dropped objects. Dropped tables and any associated objects such as indexes, constraints, nested tables, and the likes are not removed and still occupy space. They continue to count against user space quotas, until specifically purged from the recycle bin or the unlikely situation where they must be purged by the database because of tablespace space constraints.
When you drop a tablespace including its contents, the objects in the tablespace are not placed in the recycle bin and the database purges any entries in the recycle bin for objects located in the tablespace. The database also purges any recycle bin entries for objects in a tablespace when you drop the tablespace, not including contents, and the tablespace is otherwise empty. Likewise:
·When you drop a user, any objects belonging to the user are not placed in the recycle bin and any objects in the recycle bin are purged.
·When you drop a cluster, its member tables are not placed in the recycle bin and any former member tables in the recycle bin are purged.
·When you drop a type, any dependent objects such as subtypes are not placed in the recycle bin and any former dependent objects in the recycle bin are purged.
24、You want to create a consumer group, GROUP1, and you execute the following command in the command-line interface:
SQL> EXEC DBMS_RESOURCE_MANAGER.CREATE_CONSUMER_GROUP
(CONSUMER_GROUP =>'group1', COMMENT => 'New Group');
This command errorsout displaying the following message:
ERROR at line 1:
ORA-01031: insufficient privileges
ORA-06512: at "SYS.DBMS_RMIN", line 115
ORA-06512: at SYS.DBMS_RESOURCE_MANAGER", line 108
ORA-06512: at line 1
What action would you take to overcome this error?
A. grant SYSDBA to the user
B. grant SYSOPER to the user
C. grant the RESOURCE role to the user
D. use the GRANT command to grant the ADMINISTER_RESOURCE_MANAGER privilege to the use
E. grant the ADMINISTER_RESOURCE_MANAGER privilege to the user by using the DBMS_RESOURCE_MANAGER_PRIVS package
Editor’s notes:
You can see The ADMINISTER_RESOURCE_MANAGER privilege by query dba_sys_privs.
SELECT * FROM Dba_Sys_Privs a WHERE a.privilege LIKE '%RESOURCE%'
But, ADMINISTER_RESOURCE_MANAGER can not be granted like this:
GRANT ADMINISTER RESOURCE MANAGER TO scott;
If you do this,a error will occur.
25、Why would you use the following FLASHBACK TABLEcommand?
FLASHBACK TABLE emp TO TIMESTAMP ('11:45'.'hh12:mi');
A. to undo the changes made to the EMP table since the specified time
B. to restore the EMP table that was wrongly dropped from the database
C. to view the transactions that have modified the EMP table since the specified time
D. to view the changes made to the EMP table for one or more rows since the specified time
Editor’s notes:
Flashback table require table to enable row movemnent.
Oracle Database cannot restore a table to an earlier state across any DDL operations that change the structure of the table.
TO SCN Clause
Specify the system change number (SCN) corresponding to the point in time to which you want to return the table. The expr must evaluate to a number representing a valid SCN.
TO TIMESTAMP Clause
Specify a timestamp value corresponding to the point in time to which you want to return the table. The expr must evaluate to a valid timestamp in the past. The table will be flashed back to a time within approximately 3 seconds of the specified timestamp.
TO RESTORE POINT Clause
Specify a restore point to which you want to flash back the table. The restore point must already have been created.
ENABLE | DISABLE TRIGGERS
By default, Oracle Database disables all enabled triggers defined on table during the Flashback Table operation and then reenables them after the Flashback Table operation is complete. Specify ENABLE TRIGGERS if you want to override this default behavior. and keep the triggers enabled during the Flashback process.
This clause affects only those database triggers defined on table that are already enabled. To enable currently disabled triggers selectively, use the ALTER TABLE ... enable_disable_clause before you issue the FLASHBACK TABLE statement with the ENABLE TRIGGERS clause.
TO BEFORE DROP Clause
Use this clause to retrieve from the recycle bin a table that has been dropped, along with all possible dependent objects.
26、While setting up the threshold for tablespace space usage metrics for one of you tablespaces, you define the critical threshold as 60% and warning threshold as 75% What would be the result of this setting?
A. It would result in an error because the warning threshold cannot be more than th critical threshold.
B. The warning threshold is ignored and alerts would be generated when space usag exceeds the critical threshold.
C. The critical threshold is ignored and alerts would be generated when space usag exceeds the warning threshold.
D. Both threshold values are used and alerts are generated when space usage exceeds the respective threshold values.
27、Exhibit
Viewing the Exhibit and examine the Flashback Database architecture. Identify the missing component (shown with a "?`"= in the Flashback Database architecture.
A. DBWn
B. RVWR
C. ARCH
D. RECO
E. TRWR
Editor’s notes:
RVWR stands for Recovery Writer. It is a background process, which is very similar to the LGWR process. The only difference between the two is that LGWR writes Flashback Database data to Redo Log file, whereas RVWR writes the Flashback Database data to Flashback Database logs.
The figure below depicts how RVWR is different from LGWR:
28、You observe that the senior database administrator always uses the following command when connecting to Recovery Manager (RMAN), and then starts executing commands to backup the database files:
% rman target /
What would be the effect of this command?
A. RMAN would use the current operating system location to place the backup.
B. RMAN would use the control file of the target database to maintain the backup.
C. RMAN would use the recovery catalog of the target database to maintain the backup.
D. RMAN would use the control file of the target database to maintain the backup metadata.
E. RMAN would use the recovery catalog of the target database to maintain the backup metadata.
来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/22598114/viewspace-677794/，如需转载，请注明出处，否则将追究法律责任。
转载于:http://blog.itpub.net/22598114/viewspace-677794/
2975
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
