转载请注明出处http://blog.csdn.net/guoyjoe/article/details/12656897

[每日一题]  11gOCP 1z0-053 :2013-10-11  Flashback Data Archive属性.........................43-LMLPHP


正确答案:BD

闪回数据归档请参考:http://blog.csdn.net/guoyjoe/article/details/9199963

直接用实验解析答案

答案A,不开归档才也可以用Flashbackup Data Archive闪回数据归档,闪回数据归档与UNDO有关,与ARCHIVE LOG无关。

在没有归档下也可以创建闪回数据归档,操作如下:

(1)关闭归档

gyj@OCM> conn / as sysdba
Connected.
sys@OCM> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
sys@OCM> startup mount;
ORACLE instance started.

Total System Global Area  839282688 bytes
Fixed Size                  2233000 bytes
Variable Size             436211032 bytes
Database Buffers          394264576 bytes
Redo Buffers                6574080 bytes
Database mounted.
sys@OCM> alter database noarchivelog;

Database altered.

sys@OCM> alter database open;

Database altered.

sys@OCM> archive log list;
Database log mode              No Archive Mode
Automatic archival             Disabled
Archive destination            /arch
Oldest online log sequence     97
Current log sequence           99

(2)创建

sys@OCM> conn gyj/gyj
Connected.
gyj@OCM> create flashback archive flash1 tablespace gyj  quota 10M retention 1 year;

Flashback archive created.

说明没开归档下也可以创建闪回数据归档,所以A答案错。

答案B正确,只有在自动管理undo表空间才能创建闪回数据归档。


(1)      UNDO自动管理

sys@OCM> conn gyj/gyj
Connected.
gyj@OCM> drop flashback archive flash1;

Flashback archive dropped.

gyj@OCM> show parameter undo

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
undo_management                      string      AUTO
undo_retention                       integer     900
undo_tablespace                      string      UNDOTBS1

(2)      创建闪回数据归档,成功

gyj@OCM> create flashback archive flash1 tablespace gyj  quota 10M retention 1 year;

Flashback archive created.

(3)      UNDO手动管理

gyj@OCM> alter system set undo_management=manual scope=spfile;

System altered.

gyj@OCM> shutdown immediate;
ORA-01031: insufficient privileges
gyj@OCM> conn / as sysdba
Connected.
sys@OCM> startup force;
ORACLE instance started.

Total System Global Area  839282688 bytes
Fixed Size                  2233000 bytes
Variable Size             436211032 bytes
Database Buffers          394264576 bytes
Redo Buffers                6574080 bytes
Database mounted.
Database opened.
sys@OCM> show parameter undo

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
undo_management                      string      MANUAL
undo_retention                       integer     900
undo_tablespace                      string      UNDOTBS1

(4)      创建,失败。

sys@OCM>  createflashback archive flash2 tablespace tp1 quota 10M retention 1 year;

 create flashbackarchive flash2 tablespace tp1  quota 10Mretention 1 year

*

ERROR at line 1:

ORA-55628: Flashback Archive supports Oracle 11g orhigher


所以答案B正确只有在自动管理的UNDO表空间下才能创建闪回数据归档

答案C,不正确,不需要设为guarantee,默认Undo retention为 no guarantee就可以创建闪回数据归档,答AB都在no guarante下创建的闪回数据归档,能过如下视图查是不是NO GUARANTEE,看UNDOTBS1表空间是NOGUARANTEE


sys@OCM> SELECT TABLESPACE_NAME,RETENTION FROM DBA_TABLESPACES;

TABLESPACE_NAME                RETENTION
------------------------------ -----------
SYSTEM                         NOT APPLY
SYSAUX                         NOT APPLY
UNDOTBS1                       NOGUARANTEE
TEMP                           NOT APPLY
USERS                          NOT APPLY
EXAMPLE                        NOT APPLY
GYJ                            NOT APPLY
TP1                            NOT APPLY
MRKT                           NOT APPLY
TEMP1                          NOT APPLY
TEMP4                          NOT APPLY

答案D正确,表空间必须是ASSM才能创建闪回数据归档。上例是建在表空间TP1上是AUTO管理段的。
sys@OCM> SELECT TABLESPACE_NAME,SEGMENT_SPACE_MANAGEMENT  FROM DBA_TABLESPACES;

TABLESPACE_NAME                SEGMEN
------------------------------ ------
SYSTEM                         MANUAL
SYSAUX                         AUTO
UNDOTBS1                       MANUAL
TEMP                           MANUAL
USERS                          AUTO
EXAMPLE                        AUTO
GYJ                            AUTO
TP1                            AUTO
MRKT                           AUTO
TEMP1                          MANUAL
TEMP4                          MANUAL

举个反例,我建个MSSM的表空间,看能不能创闪回数据归档:

sys@OCM> create tablespace tp2 datafile \'/u01/app/oracle/oradata/ocm/tp2.dbf\' size 100M  Segment Space Management MANUAL;

Tablespace created.

sys@OCM>  create flashback archive flash3 tablespace tp2  quota 10M retention 1 year;
 create flashback archive flash3 tablespace tp2  quota 10M retention 1 year
                                            *
ERROR at line 1:
ORA-55627: Flashback Archive tablespace must be ASSM tablespace

看到提示了吧,必须要用ASSM表空间。

    所以说啊,学习OCP一定要一道道题认认真真做实验,操作->查资料->思考->总结(反复折腾才能真正才为大师啊,这需要时间,慢慢累积,成为高手只是时间问题。),不希望大家是Paper OCP!


大家有在学习OCP或在Oracle入门时找不到方向的可以找我。




12-14 12:51