转:1Z0-043记录(二)

18You 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.

 

19You 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.

 

20You 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

 

21You 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

 

22You 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

 

22These 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.

 

23Which 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.

 

24You 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.

 

25Why 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.

 

26While 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.

 

27Exhibit

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:

 

28You 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/

  • 0
    点赞
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值