Definer rights and invoker rights are described in the Oracle docs as shown in
http://download.oracle.com/docs/cd/B10501_01/appdev.920/a96624/08_subs.htm#15186
I took the following notes from the above docs to help me understand these concepts:
1. Assuming both Scott and Blake has a table called dept.
2. Scott created a procedure as follows:
CREATE PROCEDURE create_dept (
my_deptno NUMBER,
my_dname VARCHAR2,
my_loc VARCHAR2) AS
BEGIN
INSERT INTO dept VALUES (my_deptno, my_dname, my_loc);
END;
3. Scott grant the execute privilege to Blake, e.g.
scott@mydb> grant execute on create_dept to Blake;
4. Blake execute the procedure create_dept, e.g.
blake@mydb> execute create_dept(10, 'sales', 'New York');
5. Blake will create a department 'sales' in the table dept residing in Scott, not in Blake.
This is due to the definer's rights - the procedure gets executed with the privileges and schema context of the owner.
6. If in step 2, Scott create an invoker-rights procedure as follows
CREATE PROCEDURE create_dept (
my_deptno NUMBER,
my_dname VARCHAR2,
my_loc VARCHAR2) AUTHID CURRENT_USER AS
BEGIN
INSERT INTO dept VALUES (my_deptno, my_dname, my_loc);
END;
7. Then, if Blake repeats the step 4, Blake will create a a department 'sales' in the table dept residing in Blake, not in Scott.
This is due to the invoker's right - the procedure gets executed with the privileges and schema context of the current user.
Monday, October 22, 2007
Monday, October 08, 2007
Execute OS command in RMAN
Here are the examples, the key is to use single quotation mark:
RMAN> host 'ls -l';
total 12
-rw-r--r-- 1 Denis None 801 Oct 2 15:24 README
-rw-r--r-- 1 Denis None 176 Sep 23 20:45 flsharea_chk.sql
-rw-r--r-- 1 Denis None 64 Sep 23 13:32 lvl_0_bkup.cmd
-rw-r--r-- 1 Denis None 37 Sep 23 13:42 lvl_0_bkup_nocomp.cmd
-rw-r--r-- 1 Denis None 64 Sep 23 20:02 lvl_1_bkup.cmd
-rwxr-xr-x 1 Denis None 1704 Oct 2 15:14 sqlnet.log
host command complete
RMAN> host 'pwd';
/cygdrive/e/Denis_Files/ORACLE/Practise_Oracle/flash_recv_area_rman10g
host command complete
RMAN> host 'ls -l';
total 12
-rw-r--r-- 1 Denis None 801 Oct 2 15:24 README
-rw-r--r-- 1 Denis None 176 Sep 23 20:45 flsharea_chk.sql
-rw-r--r-- 1 Denis None 64 Sep 23 13:32 lvl_0_bkup.cmd
-rw-r--r-- 1 Denis None 37 Sep 23 13:42 lvl_0_bkup_nocomp.cmd
-rw-r--r-- 1 Denis None 64 Sep 23 20:02 lvl_1_bkup.cmd
-rwxr-xr-x 1 Denis None 1704 Oct 2 15:14 sqlnet.log
host command complete
RMAN> host 'pwd';
/cygdrive/e/Denis_Files/ORACLE/Practise_Oracle/flash_recv_area_rman10g
host command complete
Sunday, October 07, 2007
RMAN : tablespace point-in-time recovery
I have been asked a question about how to recover a table to a point in the past time using RMAN. I response by saying if in Oracle 10g, we can use Flashback technology. But if using RMAN is only option, we can do tablespace point-in-time recovery(TSPITR). However I was unable to describe the clear conceptual steps about TSPITR. Here I did a test based on the document.
The test case assumes there is only one table: AUTO_TABLE in the tablespace AUTO_TBS.
The situation could be more complicated than what will be shown below if there are other tables in the tablespace that have been updated after the target time to which the tablespace will be recovered.
ref: http://download.oracle.com/docs/cd/B19306_01/backup.102/b14191/rcmtspit002.htm#i1010246
1. Information about the auto_table
a. auto_table resides in the AUTO_TBS tablespace
TABLE_NAME TABLESPACE_NAME
------------------------------ ------------------------------
.....
AUTO_TABLE AUTO_TBS
b. Number of rows
scott@ORCL> select count(*) from auto_table;
COUNT(*)
----------
66065
c. Timestamp just before truncate
scott@ORCL> select sysdate from dual;
SYSDATE
--------------------
07-OCT-2007 19:43:11
scott@ORCL> truncate table auto_table;
Table truncated.
The objective is to recover the auto_table to the point before the truncate.
2. Planing and Preparing for TSPITR
2.1 Choosing the Right Target Time for TSPITR
07-OCT-2007 19:43:11
2.2 Determining the Recovery Set: Analyzing Data Relationships
SELECT *
FROM SYS.TS_PITR_CHECK
WHERE (
TS1_NAME IN ('AUTO_TBS')
AND TS2_NAME NOT IN ('AUTO_TBS')
)
OR (
TS1_NAME NOT IN ('AUTO_TBS')
AND TS2_NAME IN ('AUTO_TBS')
);
Note: There are no complications for this case - no dependencies of auto_table on other objects in auto_tbs or other tablespaces
2.3 Identifying and Preserving Objects That Will Be Lost After TSPITR
SELECT OWNER, NAME, TABLESPACE_NAME,
TO_CHAR(CREATION_TIME, 'YYYY-MM-DD:HH24:MI:SS')
FROM TS_PITR_OBJECTS_TO_BE_DROPPED
WHERE TABLESPACE_NAME IN ('AUTO_TBS')
AND CREATION_TIME > TO_DATE('07-OCT-2007 19:43:11','YY-MON-DD:HH24:MI:SS')
ORDER BY TABLESPACE_NAME, CREATION_TIME;
Note:
You can preserve such objects, once they are identified, by exporting them before TSPITR using an Oracle export utility (Data Pump Export or Original Export) and re-importing them afterwards using the corresponding import utility.
In this case, we don't have any such objects.
3. Performing Basic RMAN TSPITR
Three options:
A. Fully automated TSPITR
B. Customized TSPITR with an automatic auxiliary instance
C. TSPITR with your own auxiliary instance
3.1 Take the tablespace AUTO_TBS offline
sys@ORCL> alter tablespace auto_tbs offline;
Tablespace altered.
3.2 Rocover using 'Fully automated TSPITR'
Note: Using an Auxiliary Destination for Automated RMAN TSPITR
e:\auxdest
start the RMAN client, connecting to the target database and, if applicable, a recovery catalog.
The following is the RMAN command:
RMAN> RECOVER TABLESPACE auto_tbs
2> UNTIL TIME '07-OCT-2007 19:43:11'
3> AUXILIARY DESTINATION 'e:\auxdest'
4> ;
Appendix A shows the full RMAN screen output.
3.3 Backing Up Recovered Tablespaces After TSPITR
RMAN> BACKUP TABLESPACE AUTO_TBS
3.4 Bring the tablespaces online, as follows:
RMAN> SQL "ALTER TABLESPACE AUTO_TBS ONLINE";
Appendix A:
===========
The test case assumes there is only one table: AUTO_TABLE in the tablespace AUTO_TBS.
The situation could be more complicated than what will be shown below if there are other tables in the tablespace that have been updated after the target time to which the tablespace will be recovered.
ref: http://download.oracle.com/docs/cd/B19306_01/backup.102/b14191/rcmtspit002.htm#i1010246
1. Information about the auto_table
a. auto_table resides in the AUTO_TBS tablespace
TABLE_NAME TABLESPACE_NAME
------------------------------ ------------------------------
.....
AUTO_TABLE AUTO_TBS
b. Number of rows
scott@ORCL> select count(*) from auto_table;
COUNT(*)
----------
66065
c. Timestamp just before truncate
scott@ORCL> select sysdate from dual;
SYSDATE
--------------------
07-OCT-2007 19:43:11
scott@ORCL> truncate table auto_table;
Table truncated.
The objective is to recover the auto_table to the point before the truncate.
2. Planing and Preparing for TSPITR
2.1 Choosing the Right Target Time for TSPITR
07-OCT-2007 19:43:11
2.2 Determining the Recovery Set: Analyzing Data Relationships
SELECT *
FROM SYS.TS_PITR_CHECK
WHERE (
TS1_NAME IN ('AUTO_TBS')
AND TS2_NAME NOT IN ('AUTO_TBS')
)
OR (
TS1_NAME NOT IN ('AUTO_TBS')
AND TS2_NAME IN ('AUTO_TBS')
);
Note: There are no complications for this case - no dependencies of auto_table on other objects in auto_tbs or other tablespaces
2.3 Identifying and Preserving Objects That Will Be Lost After TSPITR
SELECT OWNER, NAME, TABLESPACE_NAME,
TO_CHAR(CREATION_TIME, 'YYYY-MM-DD:HH24:MI:SS')
FROM TS_PITR_OBJECTS_TO_BE_DROPPED
WHERE TABLESPACE_NAME IN ('AUTO_TBS')
AND CREATION_TIME > TO_DATE('07-OCT-2007 19:43:11','YY-MON-DD:HH24:MI:SS')
ORDER BY TABLESPACE_NAME, CREATION_TIME;
Note:
You can preserve such objects, once they are identified, by exporting them before TSPITR using an Oracle export utility (Data Pump Export or Original Export) and re-importing them afterwards using the corresponding import utility.
In this case, we don't have any such objects.
3. Performing Basic RMAN TSPITR
Three options:
A. Fully automated TSPITR
B. Customized TSPITR with an automatic auxiliary instance
C. TSPITR with your own auxiliary instance
3.1 Take the tablespace AUTO_TBS offline
sys@ORCL> alter tablespace auto_tbs offline;
Tablespace altered.
3.2 Rocover using 'Fully automated TSPITR'
Note: Using an Auxiliary Destination for Automated RMAN TSPITR
e:\auxdest
start the RMAN client, connecting to the target database and, if applicable, a recovery catalog.
The following is the RMAN command:
RMAN> RECOVER TABLESPACE auto_tbs
2> UNTIL TIME '07-OCT-2007 19:43:11'
3> AUXILIARY DESTINATION 'e:\auxdest'
4> ;
Appendix A shows the full RMAN screen output.
3.3 Backing Up Recovered Tablespaces After TSPITR
RMAN> BACKUP TABLESPACE AUTO_TBS
3.4 Bring the tablespaces online, as follows:
RMAN> SQL "ALTER TABLESPACE AUTO_TBS ONLINE";
Appendix A:
===========
RMAN> RECOVER TABLESPACE auto_tbs
2> UNTIL TIME '07-OCT-2007 19:43:11'
3> AUXILIARY DESTINATION 'e:\auxdest'
4> ;
Starting recover at 07-OCT-2007 20:10:36
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=158 devtype=DISK
RMAN-05026: WARNING: presuming following set of tablespaces applies to specified point in time
List of tablespaces expected to have UNDO segments
tablespace SYSTEM
tablespace UNDOTBS1
tablespace UNDO_BATCH
Creating automatic instance, with SID='aFbc'
initialization parameters used for automatic instance:
db_name=ORCL
compatible=10.2.0.1.0
db_block_size=8192
db_files=200
db_unique_name=tspitr_ORCL_aFbc
large_pool_size=1M
shared_pool_size=110M
#No auxiliary parameter file used
db_create_file_dest=e:\auxdest
control_files=e:\auxdest/cntrl_tspitr_ORCL_aFbc.f
starting up automatic instance ORCL
Oracle instance started
Total System Global Area 201326592 bytes
Fixed Size 1248092 bytes
Variable Size 146801828 bytes
Database Buffers 50331648 bytes
Redo Buffers 2945024 bytes
Automatic instance created
contents of Memory Script:
{
# set the until clause
set until time "07-OCT-2007 19:43:11";
# restore the controlfile
restore clone controlfile;
# mount the controlfile
sql clone 'alter database mount clone database';
# archive current online log for tspitr to a resent until time
sql 'alter system archive log current';
# avoid unnecessary autobackups for structural changes during TSPITR
sql 'begin dbms_backup_restore.AutoBackupFlag(FALSE); end;';
}
executing Memory Script
executing command: SET until clause
Starting restore at 07-OCT-2007 20:12:09
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: sid=36 devtype=DISK
channel ORA_AUX_DISK_1: starting datafile backupset restore
channel ORA_AUX_DISK_1: restoring control file
channel ORA_AUX_DISK_1: reading from backup piece E:\ORAFLASH\ORCL\BACKUPSET\2007_10_07\O1_MF_NCSN0_TAG2007100
LND6K2_.BKP
channel ORA_AUX_DISK_1: restored backup piece 1
piece handle=E:\ORAFLASH\ORCL\BACKUPSET\2007_10_07\O1_MF_NCSN0_TAG20071007T173142_3JLND6K2_.BKP tag=TAG2007100
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:05
output filename=E:\AUXDEST\CNTRL_TSPITR_ORCL_AFBC.F
Finished restore at 07-OCT-2007 20:12:48
sql statement: alter database mount clone database
sql statement: alter system archive log current
sql statement: begin dbms_backup_restore.AutoBackupFlag(FALSE); end;
released channel: ORA_DISK_1
released channel: ORA_AUX_DISK_1
contents of Memory Script:
{
# generated tablespace point-in-time recovery script
# set the until clause
set until time "07-OCT-2007 19:43:11";
# set an omf destination filename for restore
set newname for clone datafile 1 to new;
# set an omf destination filename for restore
set newname for clone datafile 2 to new;
# set an omf destination filename for restore
set newname for clone datafile 8 to new;
# set an omf destination tempfile
set newname for clone tempfile 1 to new;
# set a destination filename for restore
set newname for datafile 6 to
"E:\ORADATA\ORCL\AUTO_TBS_DATA01.DBF";
# rename all tempfiles
switch clone tempfile all;
# restore the tablespaces in the recovery set plus the auxilliary tablespaces
restore clone datafile 1, 2, 8, 6;
switch clone datafile all;
#online the datafiles restored or flipped
sql clone "alter database datafile 1 online";
#online the datafiles restored or flipped
sql clone "alter database datafile 2 online";
#online the datafiles restored or flipped
sql clone "alter database datafile 8 online";
#online the datafiles restored or flipped
sql clone "alter database datafile 6 online";
# make the controlfile point at the restored datafiles, then recover them
recover clone database tablespace "AUTO_TBS", "SYSTEM", "UNDOTBS1", "UNDO_BATCH" delete archivelog;
alter clone database open resetlogs;
# PLUG HERE the creation of a temporary tablespace if export fails due to lack
# of temporary space.
# For example in Unix these two lines would do that:
#sql clone "create tablespace aux_tspitr_tmp
# datafile ''/tmp/aux_tspitr_tmp.dbf'' size 500K";
}
executing Memory Script
executing command: SET until clause
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
renamed temporary file 1 to E:\AUXDEST\TSPITR_O\DATAFILE\O1_MF_TEMP_%U_.TMP in control file
Starting restore at 07-OCT-2007 20:16:10
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: sid=39 devtype=DISK
channel ORA_AUX_DISK_1: starting datafile backupset restore
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
restoring datafile 00001 to E:\AUXDEST\TSPITR_O\DATAFILE\O1_MF_SYSTEM_%U_.DBF
restoring datafile 00002 to E:\AUXDEST\TSPITR_O\DATAFILE\O1_MF_UNDOTBS1_%U_.DBF
restoring datafile 00008 to E:\AUXDEST\TSPITR_O\DATAFILE\O1_MF_UNDO_BAT_%U_.DBF
restoring datafile 00006 to E:\ORADATA\ORCL\AUTO_TBS_DATA01.DBF
channel ORA_AUX_DISK_1: reading from backup piece E:\ORAFLASH\ORCL\BACKUPSET\2007_10_07\O1_MF_NNND0_TAG20071007T173142_3J
LN26F5_.BKP
channel ORA_AUX_DISK_1: restored backup piece 1
piece handle=E:\ORAFLASH\ORCL\BACKUPSET\2007_10_07\O1_MF_NNND0_TAG20071007T173142_3JLN26F5_.BKP tag=TAG20071007T173142
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:06:26
Finished restore at 07-OCT-2007 20:23:10
datafile 1 switched to datafile copy
input datafile copy recid=9 stamp=635372617 filename=E:\AUXDEST\TSPITR_O\DATAFILE\O1_MF_SYSTEM_3JLXQD89_.DBF
datafile 2 switched to datafile copy
input datafile copy recid=10 stamp=635372617 filename=E:\AUXDEST\TSPITR_O\DATAFILE\O1_MF_UNDOTBS1_3JLXQDRW_.DBF
datafile 8 switched to datafile copy
input datafile copy recid=11 stamp=635372618 filename=E:\AUXDEST\TSPITR_O\DATAFILE\O1_MF_UNDO_BAT_3JLXQF00_.DBF
sql statement: alter database datafile 1 online
sql statement: alter database datafile 2 online
sql statement: alter database datafile 8 online
sql statement: alter database datafile 6 online
Starting recover at 07-OCT-2007 20:24:23
using channel ORA_AUX_DISK_1
using channel ORA_AUX_DISK_1
starting media recovery
archive log thread 1 sequence 9 is already on disk as file E:\ORAFLASH\ORCL\ARCHIVELOG\2007_10_07\O1_MF_1_9_3JLOS56T_.ARC
archive log thread 1 sequence 10 is already on disk as file E:\ORAFLASH\ORCL\ARCHIVELOG\2007_10_07\O1_MF_1_10_3JLXKK6T_.A
RC
archive log filename=E:\ORAFLASH\ORCL\ARCHIVELOG\2007_10_07\O1_MF_1_9_3JLOS56T_.ARC thread=1 sequence=9
archive log filename=E:\ORAFLASH\ORCL\ARCHIVELOG\2007_10_07\O1_MF_1_10_3JLXKK6T_.ARC thread=1 sequence=10
media recovery complete, elapsed time: 00:00:13
Finished recover at 07-OCT-2007 20:25:37
database opened
contents of Memory Script:
{
# export the tablespaces in the recovery set
host 'exp userid =\"/@(DESCRIPTION=(ADDRESS=(PROTOCOL=beq)(PROGRAM=oracle)(ARGV0=oracleaFbc)(ARGS=^'(DESCRIPTION=(LOCAL=Y
ES)(ADDRESS=(PROTOCOL=beq)))^')(ENVS=^'ORACLE_SID=aFbc^'))(CONNECT_DATA=(SID=aFbc))) as sysdba\" point_in_time_recover=y
tablespaces=
AUTO_TBS file=
tspitr_a.dmp';
# shutdown clone before import
shutdown clone immediate
# import the tablespaces in the recovery set
host 'imp userid =\"/@ as sysdba\" point_in_time_recover=y file=
tspitr_a.dmp';
# online/offline the tablespace imported
sql "alter tablespace AUTO_TBS online";
sql "alter tablespace AUTO_TBS offline";
# enable autobackups in case user does open resetlogs from RMAN after TSPITR
sql 'begin dbms_backup_restore.AutoBackupFlag(TRUE); end;';
}
executing Memory Script
Export: Release 10.2.0.1.0 - Production on Sun Oct 7 20:26:49 2007
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
Export done in WE8MSWIN1252 character set and AL16UTF16 NCHAR character set
Note: table data (rows) will not be exported
About to export Tablespace Point-in-time Recovery objects...
For tablespace AUTO_TBS ...
. exporting cluster definitions
. exporting table definitions
. . exporting table AUTO_TABLE
. exporting referential integrity constraints
. exporting triggers
. end point-in-time recovery
Export terminated successfully without warnings.
host command complete
database closed
database dismounted
Oracle instance shut down
Import: Release 10.2.0.1.0 - Production on Sun Oct 7 20:30:59 2007
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
Export file created by EXPORT:V10.02.01 via conventional path
About to import Tablespace Point-in-time Recovery objects...
import done in WE8MSWIN1252 character set and AL16UTF16 NCHAR character set
. importing SYS's objects into SYS
. importing SCOTT's objects into SCOTT
. . importing table "AUTO_TABLE"
. importing SYS's objects into SYS
Import terminated successfully without warnings.
host command complete
sql statement: alter tablespace AUTO_TBS online
sql statement: alter tablespace AUTO_TBS offline
sql statement: begin dbms_backup_restore.AutoBackupFlag(TRUE); end;
Removing automatic instance
Automatic instance removed
auxiliary instance file E:\AUXDEST\CNTRL_TSPITR_ORCL_AFBC.F deleted
auxiliary instance file E:\AUXDEST\TSPITR_O\DATAFILE\O1_MF_SYSTEM_3JLXQD89_.DBF deleted
auxiliary instance file E:\AUXDEST\TSPITR_O\DATAFILE\O1_MF_UNDOTBS1_3JLXQDRW_.DBF deleted
auxiliary instance file E:\AUXDEST\TSPITR_O\DATAFILE\O1_MF_UNDO_BAT_3JLXQF00_.DBF deleted
auxiliary instance file E:\AUXDEST\TSPITR_O\DATAFILE\O1_MF_TEMP_3JLY97MK_.TMP deleted
auxiliary instance file E:\AUXDEST\TSPITR_O\ONLINELOG\O1_MF_1_3JLY8G4W_.LOG deleted
auxiliary instance file E:\AUXDEST\TSPITR_O\ONLINELOG\O1_MF_2_3JLY8OX9_.LOG deleted
auxiliary instance file E:\AUXDEST\TSPITR_O\ONLINELOG\O1_MF_3_3JLY8WJM_.LOG deleted
Finished recover at 07-OCT-2007 20:32:10
RMAN>
Tuesday, October 02, 2007
OCP Oracle 10g Admin II - Ch 6: Recovering from User Errors
Chapter 6: Recovering from User Errors
Review Questions
1. Which underlying database technology is used by Flashback Drop, Flashback Table, and Flashback Versions Query to recover data?
A. Redo logs
B. Rollback segments
C. Undo data
D. Archive logs
---
Ans: C.
All the Flashback Technologies, with the exception of the Flashback Database,
utilize the undo data to recover objects.
2. Which of the following statements is true regarding the Recycle Bin? (Choose all that apply.)
A. The Recycle Bin is a physical storage area of flashback dropped objects.
B. The Recycle Bin is a logical container area of flashback dropped objects.
C. The objects in the Recycle Bin are stored in the UNDO tablespace.
D. The objects in the Recycle Bin are stored in the tablespace they were created in.
---
Ans: B, D.
The Recycle Bin is a logical container of flashback dropped objects. The objects in the Recycle Bin are stored in the tablespace they were created in.
3. What actions does the Flashback Drop process perform?
A. Back up table only
B. Back up table and indexes only
C. Back up table and referential constraints only
D. Back up table and dependent objects
---
Ans: D.
The Flashback Drop process is responsible for backing up tables and their dependent objects.
4. Which activity can occur with the Recycle Bin?
A. All indexed-organized tables are protected by the Recycle Bin.
B. System-and dictionary-managed tablespaces are stored in the Recycle Bin.
C. Dependent objects of stored tables, including referential constraints, are stored in the Recycle Bin.
D. Data Manipulation Language (DML) and Data Definition Language (DDL) can be run against objects in the Recycle Bin.
E. None of the above.
---
Ans: E.
Only non-partitioned index-organized tables are protected by the Recycle Bin.
Non-system and locally managed tablespaces are stored in the Recycle Bin.
Referential constraints are not stored in the Recycle Bin. DML or DDL cannot be
performed against objects in the Recycle Bin.
5. One method of dropping objects and bypassing the Recycle Bin is to perform which command?
A. DROP USER user CASCADE
B. DROP TABLE
C. DROP TABLE INCLUDING CONTENTS
D. DROP USER user
--
Ans: A.
The command DROP USER user CASCADE drops the user and the database objects
without recording a backup in the Recycle Bin. Objects in the Recycle Bin owned by
the user will be removed from the Recycle Bin.
6. Which command is responsible for removing the objects in multiple users from the Recycle Bin?
A. PURGE RECYCLEBIN
B. PURGE TABLESPACE user
C. PURGE DBA_RECYCLEBIN
D. PURGE TABLES user
---
Ans: C.
The PURGE DBA_RECYCLEBIN command purges the complete Recycle Bin for all users.
7. What is the naming convention of a Recycle Bin object?
A. BIN$globalUID$version
B. BIN$global$UIDversion
C. BIN$globalUIDversion
D. BINglobalUIDversion
---
Ans: A.
The naming convention of objects in the Recycle Bin consists of a globalUID and
version number assigned by the database with BIN$ prefixed, and a $ between the
globalUID and the version number. The formatting looks like
BIN$globalUID$version.
8. What two methods can be used to view the Recycle Bin?
A. Run the SHOW RECYCLEBIN command.
B. Query the view DBA_RECYCLEBIN.
C. Query the view V$RECYCLEBIN.
D. Query the view RECYCLEBIN.
----
Ans: A, D.
The SHOW RECYCLEBIN command and querying the RECYCLEBIN view are two methods of viewing the contents of the Recycle Bin.
Note: the answer should be A, B. RECYCLENIN is a public synonym for USER_RECYTCLEBIN
select owner, synonym_name, table_owner, table_name from dba_synonyms where synonym_name='RECYCLEBIN'
OWNER SYNONYM_NAME TABLE_OWNER TABLE_NAME
--------------- -------------------- -------------------- --------------------
PUBLIC RECYCLEBIN SYS USER_RECYCLEBIN
9. What best describes the space pressure in the Recycle Bin?
A. No free extents in the Recycle Bin, and objects being removed from the Recycle Bin to free up extents for non-Recycle Bin objects.
B. No free extents in a tablespace, and objects being removed from the Recycle Bin to free up extents for non-Recycle Bin objects.
C. No free extents in a tablespace, and objects being removed from the Recycle Bin to free up extents in a tablespace for non-Recycle Bin objects on a first in, first out (FIFO) basis.
D. No free extents in the Recycle Bin tablespace and objects being removed from Recycle Bin tablespace to free up extents for non- Recycle Bin objects on a first in, first out (FIFO) basis.
---
Ans: C.
The Recycle Bin is not stored in a Recycle Bin tablespace. It is a logical container pointing to objects in the tablespace where the objects were originally created. Objects are removed from the Recycle Bin when there is a need for available extents in a non-system tablespace. The objects are removed on a first in, first out (FIFO) basis.
10. If a tablespace is configured for AUTO EXTEND, what will occur when there are no free extents and there are objects in the AUTO EXTEND tablespace?
A. The tablespace will autoextend to make more free space and Oracle
will not remove Recycle Bin objects associated with the tablespace.
B. The tablespace will not autoextend, and objects in the Recycle Bin will
be removed to make space in the tablespace.
C. The tablespace will autoextend to make more free space and Oracle
will remove Recycle Bin objects associated with the tablespace.
D. The tablespace will not autoextend, and objects in the Recycle Bin will
be compressed to make space in the tablespace.
---
Ans: B.
A tablespace with AUTO EXTEND enabled will not autoextend to make space for the Recycle Bin objects. The objects will be removed from the Recycle Bin in the standard first in, first out (FIFO) basis.
11. Which of the following statements best describes Flashback Versions Query?
A. Flashback Versions Query is a query to perform diagnostics on version
changes in row data on rows that existed between the times the query
was executed to a determined point-in-time in the past.
B. Flashback Versions Query is a method of querying all version changes
on rows that existed between the times the query was executed to a
determined point-in-time in the past.
C. Flashback Versions Query is a query to perform diagnostics on table
changes in row data on rows that existed between the times the query
was executed to a determined point-in-time in the past.
D. Flashback Versions Query is a method of querying all version changes
on rows that existed between the times the query was executed to a
determined point-in-time in the future.
---
Ans: B.
Flashback Versions Query is a method of querying all version changes on rows. The rows must exist from the time the query was executed to a point-in-time in the past. The query will not display committed data after the query has been executed.
12. What view can be used to query diagnostic information about transactional changes in the database?
A. FLASHBACK_TRANSACTION_QUERY
B. DBA_TRANSACTION_QUERY
C. V$TRANSACTION_QUERY
D. V$FLASHBACK_TRANSACTION_QUERY
----
Ans: A.
The view FLASHBACK_TRANSACTION_QUERY is used as a diagnostic tool to identify version information about transactional changes to the database. This view can be used to view the DML statements that were executed against a row and in a specific table.
13. What are the methods of performing a Flashback Versions Query? (Choose all that apply.)
A. Flashback Versions Query can be performed by minimum and maximum SCN value.
B. Flashback Versions Query can be performed by minimum and maximum sequence number.
C. Flashback Versions Query can be performed by starting and ending timestamp.
D. Flashback Versions Query can be performed by minimum and maximum undo value.
----
Ans: A, C.
The Flashback Versions Query can be performed by either MINVALUE and MAXVALUE SCN or starting and ending TO_TIMESTAMP value.
14. Which of the following statements is true regarding the VERSIONS BETWEEN clause?
A. The VERSIONS BETWEEN clause only supports SCN.
B. The VERSIONS BETWEEN clause only supports log sequences.
C. The VERSIONS BETWEEN clause cannot produce versions past modifications to the table structure.
D. The VERSIONS BETWEEN clause can produce versions past Data Definition Language (DDL) changes to an object.
---
Ans: C.
The VERSIONS BETWEEN clause of the Flashback Versions Query cannot query past table modifications or DDL changes to a table.
15. Which pseudocolumn do you use to identify a unique row in a Flashback Versions Query?
A. VERSIONS_XID
B. BVERSIONS_OPERATION
C. VERSIONS_ENDTIME
D. VERSION_STARTTIME
----
Ans: A.
VERSIONS_XID uniquely identifies a row in the Flashback Versions Query. The other pseudocolumns can narrow down the selection criteria and may identify multiple rows in a timeframe or SCN range.
16. Which of the following statements are true regarding the VERSIONS BETWEEN clause? (Choose all that apply.)
A. The VERSIONS BETWEEN clause may be used in DML statements.
B. The VERSIONS BETWEEN clause may be used in DDL statements.
C. The VERSIONS BETWEEN clause may be used to query past DDL changes to tables.
D. The VERSIONS BETWEEN clause may not be used to query past DML statements to tables.
----
Ans: A, B.
The VERSIONS BETWEEN clause can be used in DML and DDL statements to identify data.
17. Which of the following statements is true regarding implementing a Flashback Table recovery?
A. SCN is never needed to perform a Flashback Table recovery.
B. ROW MOVEMENT must be enabled to perform a Flashback Table recovery.
C. Only one table may be recovered to perform a Flashback Table recovery.
D. Flashback Table recovery does not use undo data to perform a Flashback Table recovery.
---
Ans: B.
The command ALTER TABLE table_name ENABLE ROW MOVEMENT must be enabled to allow Flashback Table recovery.
18. What happens to enabled triggers on a table after a FLASHBACK TABLE command is performed? (Choose all that apply.)
A. The triggers are disabled by default.
B. The triggers are enabled by default.
C. Disabled triggers remain disabled with the default FLASHBACK TABLE command.
D. All triggers are enabled if the ENABLE TRIGGER clause is used.
---
Ans: A, C.
The default action for the FLASHBACK TABLE command is that the triggers will be disabled. If you disable the triggers on a table, the triggers will stay disabled after the FLASHBACK TABLE command as well. Also, if you use the ENABLE TRIGGER clause, all enabled triggers on the table prior to the Flashback Table operation will be enabled and all disabled triggers will be disabled.
19. What method can you use to identify a DML operation and the SQL statement
that has been performed against a row in a specific table for a schema owner?
(Choose all that apply.)
A. Query DBA_TRANSACTION_QUERY for TABLE_NAME, OPERATION, and
UNDO_SQL. Limit rows by START_SCN and TABLE_OWNER.
B. Query FLASHBACK_TRANSACTION_QUERY for TABLE_NAME, OPERATION,
and UNDO_SQL. Limit rows by START_SCN and TABLE_OWNER.
C. Query FLASHBACK_TRANSACTION_QUERY for TABLE_NAME, OPERATION,
and UNDO_SQL. Limit rows by START_TIMESTAMP and TABLE_OWNER.
D. Query DBA_TRANSACTION_QUERY for TABLE_NAME, OPERATION, and
UNDO_SQL. Limit rows by START_SCN and TABLE_OWNER.
----
Ans: B, C.
The proper method is to query the FLASHBACK_TRANSACTION_QUERY view. The TABLE_ NAME, OPERATION, and UNDO_SQL columns should be queried to display the information about the transactional information. The transactional information from FLASHBACK_TRANSACTION_ QUERY should then be narrowed down by START_TIMESTAMP or START_SCN. Then the information can be further narrowed down by TABLE_OWNER. Alternatively, the XID, if known, can be queried to identify the exact transaction, but that was not an option in this example.
20. How can you protect the amount of time you can query information from the Flashback Transaction Query?
A. Add UNDO GUARANTEE to the UNDO tablespace.
B. Add RETENTION GUARANTEE to the UNDO tablespace.
C. Add RETENTION GUARANTEE to the Recycle Bin logical storage container.
D. Add UNDO GUARANTEE to the Recycle Bin logical storage container.
---
Ans: B.
Adding RETENTION GUARANTEE to the UNDO tablespace or during database creation to the UNDO tablespace will protect unexpired undo data in the UNDO tablespace. This is where the Flashback Transaction Query retrieves information about transaction changes to rows in the database.
Review Questions
1. Which underlying database technology is used by Flashback Drop, Flashback Table, and Flashback Versions Query to recover data?
A. Redo logs
B. Rollback segments
C. Undo data
D. Archive logs
---
Ans: C.
All the Flashback Technologies, with the exception of the Flashback Database,
utilize the undo data to recover objects.
2. Which of the following statements is true regarding the Recycle Bin? (Choose all that apply.)
A. The Recycle Bin is a physical storage area of flashback dropped objects.
B. The Recycle Bin is a logical container area of flashback dropped objects.
C. The objects in the Recycle Bin are stored in the UNDO tablespace.
D. The objects in the Recycle Bin are stored in the tablespace they were created in.
---
Ans: B, D.
The Recycle Bin is a logical container of flashback dropped objects. The objects in the Recycle Bin are stored in the tablespace they were created in.
3. What actions does the Flashback Drop process perform?
A. Back up table only
B. Back up table and indexes only
C. Back up table and referential constraints only
D. Back up table and dependent objects
---
Ans: D.
The Flashback Drop process is responsible for backing up tables and their dependent objects.
4. Which activity can occur with the Recycle Bin?
A. All indexed-organized tables are protected by the Recycle Bin.
B. System-and dictionary-managed tablespaces are stored in the Recycle Bin.
C. Dependent objects of stored tables, including referential constraints, are stored in the Recycle Bin.
D. Data Manipulation Language (DML) and Data Definition Language (DDL) can be run against objects in the Recycle Bin.
E. None of the above.
---
Ans: E.
Only non-partitioned index-organized tables are protected by the Recycle Bin.
Non-system and locally managed tablespaces are stored in the Recycle Bin.
Referential constraints are not stored in the Recycle Bin. DML or DDL cannot be
performed against objects in the Recycle Bin.
5. One method of dropping objects and bypassing the Recycle Bin is to perform which command?
A. DROP USER user CASCADE
B. DROP TABLE
C. DROP TABLE INCLUDING CONTENTS
D. DROP USER user
--
Ans: A.
The command DROP USER user CASCADE drops the user and the database objects
without recording a backup in the Recycle Bin. Objects in the Recycle Bin owned by
the user will be removed from the Recycle Bin.
6. Which command is responsible for removing the objects in multiple users from the Recycle Bin?
A. PURGE RECYCLEBIN
B. PURGE TABLESPACE user
C. PURGE DBA_RECYCLEBIN
D. PURGE TABLES user
---
Ans: C.
The PURGE DBA_RECYCLEBIN command purges the complete Recycle Bin for all users.
7. What is the naming convention of a Recycle Bin object?
A. BIN$globalUID$version
B. BIN$global$UIDversion
C. BIN$globalUIDversion
D. BINglobalUIDversion
---
Ans: A.
The naming convention of objects in the Recycle Bin consists of a globalUID and
version number assigned by the database with BIN$ prefixed, and a $ between the
globalUID and the version number. The formatting looks like
BIN$globalUID$version.
8. What two methods can be used to view the Recycle Bin?
A. Run the SHOW RECYCLEBIN command.
B. Query the view DBA_RECYCLEBIN.
C. Query the view V$RECYCLEBIN.
D. Query the view RECYCLEBIN.
----
Ans: A, D.
The SHOW RECYCLEBIN command and querying the RECYCLEBIN view are two methods of viewing the contents of the Recycle Bin.
Note: the answer should be A, B. RECYCLENIN is a public synonym for USER_RECYTCLEBIN
select owner, synonym_name, table_owner, table_name from dba_synonyms where synonym_name='RECYCLEBIN'
OWNER SYNONYM_NAME TABLE_OWNER TABLE_NAME
--------------- -------------------- -------------------- --------------------
PUBLIC RECYCLEBIN SYS USER_RECYCLEBIN
9. What best describes the space pressure in the Recycle Bin?
A. No free extents in the Recycle Bin, and objects being removed from the Recycle Bin to free up extents for non-Recycle Bin objects.
B. No free extents in a tablespace, and objects being removed from the Recycle Bin to free up extents for non-Recycle Bin objects.
C. No free extents in a tablespace, and objects being removed from the Recycle Bin to free up extents in a tablespace for non-Recycle Bin objects on a first in, first out (FIFO) basis.
D. No free extents in the Recycle Bin tablespace and objects being removed from Recycle Bin tablespace to free up extents for non- Recycle Bin objects on a first in, first out (FIFO) basis.
---
Ans: C.
The Recycle Bin is not stored in a Recycle Bin tablespace. It is a logical container pointing to objects in the tablespace where the objects were originally created. Objects are removed from the Recycle Bin when there is a need for available extents in a non-system tablespace. The objects are removed on a first in, first out (FIFO) basis.
10. If a tablespace is configured for AUTO EXTEND, what will occur when there are no free extents and there are objects in the AUTO EXTEND tablespace?
A. The tablespace will autoextend to make more free space and Oracle
will not remove Recycle Bin objects associated with the tablespace.
B. The tablespace will not autoextend, and objects in the Recycle Bin will
be removed to make space in the tablespace.
C. The tablespace will autoextend to make more free space and Oracle
will remove Recycle Bin objects associated with the tablespace.
D. The tablespace will not autoextend, and objects in the Recycle Bin will
be compressed to make space in the tablespace.
---
Ans: B.
A tablespace with AUTO EXTEND enabled will not autoextend to make space for the Recycle Bin objects. The objects will be removed from the Recycle Bin in the standard first in, first out (FIFO) basis.
11. Which of the following statements best describes Flashback Versions Query?
A. Flashback Versions Query is a query to perform diagnostics on version
changes in row data on rows that existed between the times the query
was executed to a determined point-in-time in the past.
B. Flashback Versions Query is a method of querying all version changes
on rows that existed between the times the query was executed to a
determined point-in-time in the past.
C. Flashback Versions Query is a query to perform diagnostics on table
changes in row data on rows that existed between the times the query
was executed to a determined point-in-time in the past.
D. Flashback Versions Query is a method of querying all version changes
on rows that existed between the times the query was executed to a
determined point-in-time in the future.
---
Ans: B.
Flashback Versions Query is a method of querying all version changes on rows. The rows must exist from the time the query was executed to a point-in-time in the past. The query will not display committed data after the query has been executed.
12. What view can be used to query diagnostic information about transactional changes in the database?
A. FLASHBACK_TRANSACTION_QUERY
B. DBA_TRANSACTION_QUERY
C. V$TRANSACTION_QUERY
D. V$FLASHBACK_TRANSACTION_QUERY
----
Ans: A.
The view FLASHBACK_TRANSACTION_QUERY is used as a diagnostic tool to identify version information about transactional changes to the database. This view can be used to view the DML statements that were executed against a row and in a specific table.
13. What are the methods of performing a Flashback Versions Query? (Choose all that apply.)
A. Flashback Versions Query can be performed by minimum and maximum SCN value.
B. Flashback Versions Query can be performed by minimum and maximum sequence number.
C. Flashback Versions Query can be performed by starting and ending timestamp.
D. Flashback Versions Query can be performed by minimum and maximum undo value.
----
Ans: A, C.
The Flashback Versions Query can be performed by either MINVALUE and MAXVALUE SCN or starting and ending TO_TIMESTAMP value.
14. Which of the following statements is true regarding the VERSIONS BETWEEN clause?
A. The VERSIONS BETWEEN clause only supports SCN.
B. The VERSIONS BETWEEN clause only supports log sequences.
C. The VERSIONS BETWEEN clause cannot produce versions past modifications to the table structure.
D. The VERSIONS BETWEEN clause can produce versions past Data Definition Language (DDL) changes to an object.
---
Ans: C.
The VERSIONS BETWEEN clause of the Flashback Versions Query cannot query past table modifications or DDL changes to a table.
15. Which pseudocolumn do you use to identify a unique row in a Flashback Versions Query?
A. VERSIONS_XID
B. BVERSIONS_OPERATION
C. VERSIONS_ENDTIME
D. VERSION_STARTTIME
----
Ans: A.
VERSIONS_XID uniquely identifies a row in the Flashback Versions Query. The other pseudocolumns can narrow down the selection criteria and may identify multiple rows in a timeframe or SCN range.
16. Which of the following statements are true regarding the VERSIONS BETWEEN clause? (Choose all that apply.)
A. The VERSIONS BETWEEN clause may be used in DML statements.
B. The VERSIONS BETWEEN clause may be used in DDL statements.
C. The VERSIONS BETWEEN clause may be used to query past DDL changes to tables.
D. The VERSIONS BETWEEN clause may not be used to query past DML statements to tables.
----
Ans: A, B.
The VERSIONS BETWEEN clause can be used in DML and DDL statements to identify data.
17. Which of the following statements is true regarding implementing a Flashback Table recovery?
A. SCN is never needed to perform a Flashback Table recovery.
B. ROW MOVEMENT must be enabled to perform a Flashback Table recovery.
C. Only one table may be recovered to perform a Flashback Table recovery.
D. Flashback Table recovery does not use undo data to perform a Flashback Table recovery.
---
Ans: B.
The command ALTER TABLE table_name ENABLE ROW MOVEMENT must be enabled to allow Flashback Table recovery.
18. What happens to enabled triggers on a table after a FLASHBACK TABLE command is performed? (Choose all that apply.)
A. The triggers are disabled by default.
B. The triggers are enabled by default.
C. Disabled triggers remain disabled with the default FLASHBACK TABLE command.
D. All triggers are enabled if the ENABLE TRIGGER clause is used.
---
Ans: A, C.
The default action for the FLASHBACK TABLE command is that the triggers will be disabled. If you disable the triggers on a table, the triggers will stay disabled after the FLASHBACK TABLE command as well. Also, if you use the ENABLE TRIGGER clause, all enabled triggers on the table prior to the Flashback Table operation will be enabled and all disabled triggers will be disabled.
19. What method can you use to identify a DML operation and the SQL statement
that has been performed against a row in a specific table for a schema owner?
(Choose all that apply.)
A. Query DBA_TRANSACTION_QUERY for TABLE_NAME, OPERATION, and
UNDO_SQL. Limit rows by START_SCN and TABLE_OWNER.
B. Query FLASHBACK_TRANSACTION_QUERY for TABLE_NAME, OPERATION,
and UNDO_SQL. Limit rows by START_SCN and TABLE_OWNER.
C. Query FLASHBACK_TRANSACTION_QUERY for TABLE_NAME, OPERATION,
and UNDO_SQL. Limit rows by START_TIMESTAMP and TABLE_OWNER.
D. Query DBA_TRANSACTION_QUERY for TABLE_NAME, OPERATION, and
UNDO_SQL. Limit rows by START_SCN and TABLE_OWNER.
----
Ans: B, C.
The proper method is to query the FLASHBACK_TRANSACTION_QUERY view. The TABLE_ NAME, OPERATION, and UNDO_SQL columns should be queried to display the information about the transactional information. The transactional information from FLASHBACK_TRANSACTION_ QUERY should then be narrowed down by START_TIMESTAMP or START_SCN. Then the information can be further narrowed down by TABLE_OWNER. Alternatively, the XID, if known, can be queried to identify the exact transaction, but that was not an option in this example.
20. How can you protect the amount of time you can query information from the Flashback Transaction Query?
A. Add UNDO GUARANTEE to the UNDO tablespace.
B. Add RETENTION GUARANTEE to the UNDO tablespace.
C. Add RETENTION GUARANTEE to the Recycle Bin logical storage container.
D. Add UNDO GUARANTEE to the Recycle Bin logical storage container.
---
Ans: B.
Adding RETENTION GUARANTEE to the UNDO tablespace or during database creation to the UNDO tablespace will protect unexpired undo data in the UNDO tablespace. This is where the Flashback Transaction Query retrieves information about transaction changes to rows in the database.
OCP Oracle 10g Admin II - Ch 5: Understanding the Flashback Database
Review Questions
1. What type of recovery is the Flashback Database best suited for? (Choose all that apply.)
A. User error
B. Physical corruption
C. Logical corruption
D. Media failure
---
Ans: A, C.
A Flashback Database recovery is best suited for a user error such as a truncated table or a logical corruption like an incomplete batch transaction affecting many tables. Media recovery situations cannot be performed with the Flashback Database recovery method.
2. Flashback Database recovery can recover from which of the following failure scenarios?
A. Loss of control file
B. Dropping a tablespace through RESETLOGS
C. A user error that resized datafiles to a smaller size
D. A large truncated table or group of tables
--
Ans: D.
A Flashback Database recovery can recover a large truncated table or group of tables.
3. What new background process is responsible for writing before block images
and recovering from the Flashback Database log?
A. RWVR
B. RVWR
C. RWRV
D. RVRW
---
Ans: B.
The RVWR process is responsible for writing the "before" image information to the Flashback Database log. The Flashback Database log is read to perform the Flashback Database recovery.
4. What are the benefits of the flash recovery area in the recovery and backup process?
A. Recovery efficiency is improved because all files are stored on tape media for fast access.
B. Recovery efficiency is improved because the files are stored in multiple locations.
C. Recovery efficiency is improved because the files are stored in one location on tape.
D. Recovery efficiency is improved because the files are stored in one location on disk.
---
Ans: D.
The flash recovery area is a centralized storage area for backups on disk. This allows for a more efficient recovery process because the required files are in one location and are stored on disk instead of tape.
5. Where is information about the status of the flash recovery area displayed?
(Choose all that apply.)
A. Alert log
B. Background trace files
C. V$_OUSTANDING_ALERTS
D. DBA_OUTSTANDING_ALERTS
----
Ans: A, D.
The Alert log reports space usage and other information about the flash recovery area. DBA_OUTSTANDING_ALERTS also show the information state of the flash recovery area.
6. How is the size of the flash recovery area determined? (Choose all that apply.)
A. The size is automatically allocated at 2 gigabytes.
B. Using the ALTER SYSTEM command to dynamically set the size.
C. With the initialization parameter DB_RECOVERY_FILE_DEST_SIZE.
D. Using the ALTER TABLESPACE command.
---
Ans: B, C.
The flash recovery area can be determined by either setting the initialization
parameter DB_RECOVERY_FILE_DEST_SIZE or using the ALTER SYSTEM command.
7. What type of backup commands can the flash recovery area be used for?
(Choose all that apply.)
A. BACKUP COPY
B. BACKUP IMAGE
C. BACKUP
D. BACKUPSET
------
Ans: A, C.
The flash recovery area supports both the BACKUP and BACKUP COPY commands, which perform backup sets and image copies.
8. The flash recovery area space utilization and stored files can be identified by what method?
A. DBA_OUTSTANDING_ALERTS
B. V$OUTSTANDING_ALERTS
C. V$RECOVERY_FILE_DEST
D. DBA_RECOVERY_FILE_DEST
---
Ans: C.
The dynamic view V$RECOVERY_FILE_DEST displays the space utilization and the
amount of files that make up the flash recovery area.
9. What parameter determines the length of time that the Flashback Database will
store "before" images that can be used in the recovery process?
A. DB_FLASHBACK_RETENTION_POLICY
B. DB_FLASHBACK_RETENTION_TIME
C. DB_FLASHBACK_RETENTION_STORE
D. DB_FLASHBACK_RETENTION_TARGET
---
Ans: D.
The DB_FLASHBACK_RETENTION_TARGET parameter determines how much data is available to recover.
10. How is the DB_FLASHBACK_RETENTION_TARGET parameter measured?
A. By SCN
B. By redo log sequences
C. By time in minutes
D. By redo log sequence and threads
----
Ans: C.
DB_FLASHBACK_RETENTION_TARGET is a parameter that is measured in minutes. This value determines how many minutes the Flashback Database should write data before this data gets overwritten.
11. To enable the Flashback Database, what must be done to the database?
(Choose all that apply.)
A. It must be mounted.
B. It must be opened with RESETLOGS.
C. The flash recovery area must be created.
D. The database must be in ARCHIVELOG mode.
----
Ans: A, C, D.
To enable the Flashback Database, the flash recovery area must be created. The database must be mounted but not opened to turn on the Flashback Database. The database must also be in ARCHIVELOG mode.
12. When using the Flashback Database in a recovery situation, what information would be useful to know? (Choose all that apply.)
A. Information about the smallest SCN number that is stored in the Flashback Database log
B. Information about the earliest timestamp that is stored in the Flashback Database log
C. Information about the greatest SCN number that is stored in the Flashback Database log
D. Information about the latest timestamp that is stored in the Flashback Database log
---
Ans: A, B.
The earliest timestamp and smallest SCN will tell you how far back you can recover the database. These values can be queried by the V$FLASHBACK_DATABASE_LOG dynamic view.
13. How can you determine if the Flashback Database is turned on?
A. Query the DBA_FLASHBACK_DATABASE view.
B. Query the V$DATABASE dynamic view.
C. Check the initialization parameters.
D. Check the alert log.
---
Ans: B.
The V$DATABASE dynamic view has a new column called FLASHBACK_ON, which contains a value of YES or NO.
14. Which of the following views can determine how much space you may need in the future in the Flashback Database logs?
A. V$DATABASE
B. V$FLASHBACK_DATABASE_STAT
C. V$FLASHBCK_DATABASE_LOG
D. DBA_FLASHBACK_LOG
---
Ans: B.
The V$FLASHBACK_DATABASE_STAT dynamic view shows the daily growth and utilization of the Flashback Database log. You can match daily activities to the daily utilization of the Flashback Database log.
15. What is the default size of a redo log file created in the flash recovery area?
A. 100MB
B. 150MB
C. 10MB
D. 50MB
---
Ans: A.
The default size of a redo log file created in the flash recovery area is 100MB.
16. Which initialization parameter will create archive logs to the flash recovery area?
A. ARCHIVE_LOG_DEST
B. ARCHIVE_DUPLEX_DEST
C. ARCHIVE_LOG_DEST_n
D. LOG_ARCHIVE_DEST_n
---
Ans: D.
The LOG_ARCHIVE_DEST_n is the only initialization parameter that will create or write archive logs to the flash recovery area.
17. Which database files are permanently stored in the flash recovery area?
(Choose all that apply.)
A. Datafiles
B. RMAN files
C. Control files
D. Current online redo logs
E. Archive logs
-----
Ans: C, D.
Control files and redo logs area considered permanent files in the flash recovery area. These files are not made obsolete and deleted, even when backed up to tape.
18. Which files will not be backed up in the flash recovery area when you're using
the BACKUP RECOVERY AREA and BACKUP RECOVERY FILES commands?
(Choose all that apply.)
A. Control files
B. Redo logs
C. Datafiles
D. Permanent files
E. Flashback logs
----
Ans: A, B, D, E.
The flash recovery area will not back up redo log files, control files, or
flashback logs with the BACKUP RECOVERY AREA and BACKUP RECOVERY FILES
commands. Permanent files are considered to be current online redo logs and control
files.
19. What is responsible for applying the "before" images to the database during a Flashback Database recovery?
A. LGWR
B. SMON
C. DBWR
D. RVWR
---
Ans: D.
The RVWR process is responsible for writing the "before" images to the
Flashback Database log. This process is also responsible for applying these to the
database during a recovery.
20. What administrative database activity cannot be undone with the Flashback Database recovery?
A. Dropped table
B. Dropped user
C. Resized datafiles to smaller size
D. Dropped tablespace
--
Ans: C.
Resizing of a tablespace or datafiles to a smaller size cannot be undone with Flashback Database recovery.
1. What type of recovery is the Flashback Database best suited for? (Choose all that apply.)
A. User error
B. Physical corruption
C. Logical corruption
D. Media failure
---
Ans: A, C.
A Flashback Database recovery is best suited for a user error such as a truncated table or a logical corruption like an incomplete batch transaction affecting many tables. Media recovery situations cannot be performed with the Flashback Database recovery method.
2. Flashback Database recovery can recover from which of the following failure scenarios?
A. Loss of control file
B. Dropping a tablespace through RESETLOGS
C. A user error that resized datafiles to a smaller size
D. A large truncated table or group of tables
--
Ans: D.
A Flashback Database recovery can recover a large truncated table or group of tables.
3. What new background process is responsible for writing before block images
and recovering from the Flashback Database log?
A. RWVR
B. RVWR
C. RWRV
D. RVRW
---
Ans: B.
The RVWR process is responsible for writing the "before" image information to the Flashback Database log. The Flashback Database log is read to perform the Flashback Database recovery.
4. What are the benefits of the flash recovery area in the recovery and backup process?
A. Recovery efficiency is improved because all files are stored on tape media for fast access.
B. Recovery efficiency is improved because the files are stored in multiple locations.
C. Recovery efficiency is improved because the files are stored in one location on tape.
D. Recovery efficiency is improved because the files are stored in one location on disk.
---
Ans: D.
The flash recovery area is a centralized storage area for backups on disk. This allows for a more efficient recovery process because the required files are in one location and are stored on disk instead of tape.
5. Where is information about the status of the flash recovery area displayed?
(Choose all that apply.)
A. Alert log
B. Background trace files
C. V$_OUSTANDING_ALERTS
D. DBA_OUTSTANDING_ALERTS
----
Ans: A, D.
The Alert log reports space usage and other information about the flash recovery area. DBA_OUTSTANDING_ALERTS also show the information state of the flash recovery area.
6. How is the size of the flash recovery area determined? (Choose all that apply.)
A. The size is automatically allocated at 2 gigabytes.
B. Using the ALTER SYSTEM command to dynamically set the size.
C. With the initialization parameter DB_RECOVERY_FILE_DEST_SIZE.
D. Using the ALTER TABLESPACE command.
---
Ans: B, C.
The flash recovery area can be determined by either setting the initialization
parameter DB_RECOVERY_FILE_DEST_SIZE or using the ALTER SYSTEM command.
7. What type of backup commands can the flash recovery area be used for?
(Choose all that apply.)
A. BACKUP COPY
B. BACKUP IMAGE
C. BACKUP
D. BACKUPSET
------
Ans: A, C.
The flash recovery area supports both the BACKUP and BACKUP COPY commands, which perform backup sets and image copies.
8. The flash recovery area space utilization and stored files can be identified by what method?
A. DBA_OUTSTANDING_ALERTS
B. V$OUTSTANDING_ALERTS
C. V$RECOVERY_FILE_DEST
D. DBA_RECOVERY_FILE_DEST
---
Ans: C.
The dynamic view V$RECOVERY_FILE_DEST displays the space utilization and the
amount of files that make up the flash recovery area.
9. What parameter determines the length of time that the Flashback Database will
store "before" images that can be used in the recovery process?
A. DB_FLASHBACK_RETENTION_POLICY
B. DB_FLASHBACK_RETENTION_TIME
C. DB_FLASHBACK_RETENTION_STORE
D. DB_FLASHBACK_RETENTION_TARGET
---
Ans: D.
The DB_FLASHBACK_RETENTION_TARGET parameter determines how much data is available to recover.
10. How is the DB_FLASHBACK_RETENTION_TARGET parameter measured?
A. By SCN
B. By redo log sequences
C. By time in minutes
D. By redo log sequence and threads
----
Ans: C.
DB_FLASHBACK_RETENTION_TARGET is a parameter that is measured in minutes. This value determines how many minutes the Flashback Database should write data before this data gets overwritten.
11. To enable the Flashback Database, what must be done to the database?
(Choose all that apply.)
A. It must be mounted.
B. It must be opened with RESETLOGS.
C. The flash recovery area must be created.
D. The database must be in ARCHIVELOG mode.
----
Ans: A, C, D.
To enable the Flashback Database, the flash recovery area must be created. The database must be mounted but not opened to turn on the Flashback Database. The database must also be in ARCHIVELOG mode.
12. When using the Flashback Database in a recovery situation, what information would be useful to know? (Choose all that apply.)
A. Information about the smallest SCN number that is stored in the Flashback Database log
B. Information about the earliest timestamp that is stored in the Flashback Database log
C. Information about the greatest SCN number that is stored in the Flashback Database log
D. Information about the latest timestamp that is stored in the Flashback Database log
---
Ans: A, B.
The earliest timestamp and smallest SCN will tell you how far back you can recover the database. These values can be queried by the V$FLASHBACK_DATABASE_LOG dynamic view.
13. How can you determine if the Flashback Database is turned on?
A. Query the DBA_FLASHBACK_DATABASE view.
B. Query the V$DATABASE dynamic view.
C. Check the initialization parameters.
D. Check the alert log.
---
Ans: B.
The V$DATABASE dynamic view has a new column called FLASHBACK_ON, which contains a value of YES or NO.
14. Which of the following views can determine how much space you may need in the future in the Flashback Database logs?
A. V$DATABASE
B. V$FLASHBACK_DATABASE_STAT
C. V$FLASHBCK_DATABASE_LOG
D. DBA_FLASHBACK_LOG
---
Ans: B.
The V$FLASHBACK_DATABASE_STAT dynamic view shows the daily growth and utilization of the Flashback Database log. You can match daily activities to the daily utilization of the Flashback Database log.
15. What is the default size of a redo log file created in the flash recovery area?
A. 100MB
B. 150MB
C. 10MB
D. 50MB
---
Ans: A.
The default size of a redo log file created in the flash recovery area is 100MB.
16. Which initialization parameter will create archive logs to the flash recovery area?
A. ARCHIVE_LOG_DEST
B. ARCHIVE_DUPLEX_DEST
C. ARCHIVE_LOG_DEST_n
D. LOG_ARCHIVE_DEST_n
---
Ans: D.
The LOG_ARCHIVE_DEST_n is the only initialization parameter that will create or write archive logs to the flash recovery area.
17. Which database files are permanently stored in the flash recovery area?
(Choose all that apply.)
A. Datafiles
B. RMAN files
C. Control files
D. Current online redo logs
E. Archive logs
-----
Ans: C, D.
Control files and redo logs area considered permanent files in the flash recovery area. These files are not made obsolete and deleted, even when backed up to tape.
18. Which files will not be backed up in the flash recovery area when you're using
the BACKUP RECOVERY AREA and BACKUP RECOVERY FILES commands?
(Choose all that apply.)
A. Control files
B. Redo logs
C. Datafiles
D. Permanent files
E. Flashback logs
----
Ans: A, B, D, E.
The flash recovery area will not back up redo log files, control files, or
flashback logs with the BACKUP RECOVERY AREA and BACKUP RECOVERY FILES
commands. Permanent files are considered to be current online redo logs and control
files.
19. What is responsible for applying the "before" images to the database during a Flashback Database recovery?
A. LGWR
B. SMON
C. DBWR
D. RVWR
---
Ans: D.
The RVWR process is responsible for writing the "before" images to the
Flashback Database log. This process is also responsible for applying these to the
database during a recovery.
20. What administrative database activity cannot be undone with the Flashback Database recovery?
A. Dropped table
B. Dropped user
C. Resized datafiles to smaller size
D. Dropped tablespace
--
Ans: C.
Resizing of a tablespace or datafiles to a smaller size cannot be undone with Flashback Database recovery.
OCP Oracle 10g Admin II - Ch 4: Database Recovery
Chapter 4 - Database Recovery
Review Questions
1. What is another name for RMAN-based database recovery?
A. User-managed recovery
B. Server-managed recovery
C. Traditional recovery
D. Database recovery
--
Ans: B. Server-managed recovery is another name for RMAN recovery because the server
session performs the recovery process as it interacts with the target database.
2. What command is responsible for automating the backup of control files?
A. ALTER DATABASE CONTROLFILE AUTOBACKUP ON
B. ALTER SYSTEM CONTROLFILE AUTOBACKUP ON
C. CONFIGURE CONTROLFILE AUTOBACKUP ON
D. ENABLE CONTROLFILE AUTOBACKUP
---
Ans: C.
The control file autobackup is enabled by setting parameters within RMAN by
using CONFIGURE CONTROLFILE AUTOBACKUP ON.
3. What is the process to recover a control file?
A. Start up database, restore control file, start up mount the database, recover the database, and open the database.
B. Start up mount, restore control file, start up the database, recover the database, and open the database.
C. Start up nomount, restore control file, start up mount the database, recover the database, and open the database.
D. Start up force, restore control file, start up the database, recover the database, and open the database.
---
Ans: C.
The database needs to be started in NOMOUNT mode because there is not a control file available to MOUNT the database. Next, the control file can be restored. Once a restored control file is available, the database can be started in MOUNT mode so that standard database recovery can continue. When recovery is complete, the database can OPEN for normal use.
4. When recovering a control file without the recovery catalog, what special step must be performed to identify the target database? (Choose all that apply.)
A. You must CONNECT TARGET / to the target database within RMAN.
B. You must STARTUP MOUNT the database because the control file is missing.
C. You must SET DBID to the target database so that the target database can be identified without the control file available.
D. You must CONNECT TARGET database name to the target database within RMAN.
---
ANs: A, C.
The target database is not identifiable by database name without the control
file. So you must first use the CONNECT TARGET / command to connect. The target
database needs to be identified by the database identifier (DBID) number with the
command SET DBID database identifier. This database identifier number denotes the
target database. When you are recovering the control file, the target database
identification is not available because it is stored in the control file.
5. After you restore the control file, what must you do before you execute the RECOVER command to apply archive logs?
A. The database must be restored with the RESTORE command.
B. The database must be reconnected with the CONNECT TARGET database name command.
C. The database must be started in MOUNT mode.
D. The database must open for use with ALTER DATABASE OPEN command.
----
Ans: C. The database must be mounted before the RECOVER command can be executed.
You first must restore control so you can MOUNT the database.
6. Which of the following methods should you use for creating a control file?
(Choose all that apply.)
A. Dump the control file information to a trace file.
B. Use the ALTER DATABASE BACKUP CONTROLFILE TO TRACE command.
C. Use the CREATE CONTROLFILE command.
D. None of the above.
---
Ans: A, B.
The ALTER DATABASE BACKUP CONTROL FILE TO TRACE command creates a
user trace file, which stores an ASCII representation of the binary control file.
7. What are the two cases defined in the backup control file? (Choose two.)
A. ALTER DATABASE OPEN
B. ALTER DATABASE OPEN RESETLOGS
C. ALTER DATABASE OPEN NORESETLOGS
D. ALTER DATABASE OPEN NORESET
---
Ans: B, C.
The two cases in the backup control file are opening the database with RESETLOGS or NORESETLOGS.
8. Which files need to be available and in the matching location of the ASCII
control file in order to rebuild the control file? (Choose all that apply.)
A. Server file, PFILE or SPFILE
B. Datafiles
C. Control files
D. Redo logs
---
Ans: A, B.
The server file, SPFILE or PFILE must be available to start the database with the right parameters, and the datafiles must be in the location matching the control file. The redo logs and control file will be rebuilt.
9. Which of the following descriptions best describes incomplete recovery?
(Choose all that apply.)
A. Recovery that stops before the failure
B. Recovery that stops at the point of failure
C. Recovery that is missing transactions
D. Recovery that is not missing transactions
----
Ans: A, C.
Incomplete recovery is a recovery that stops before the failure and a recovery
that is missing transactions. Incomplete recovery is not complete or missing some
data that was previously stored in the database prior to the failure.
10. What are the required steps to perform a RMAN-based incomplete recovery with the SET UNTIL TIME clause?
A. Start up the database in MOUNT mode, verify or set the NLS_DATE_FORMAT environment variable, designate time with the SET
UNTIL TIME time stamp, restore the necessary files with the RESTORE DATABASE command, recover the database with the RECOVER DATABASE command, and then open the database with the ALTER DATABASE OPEN
command.
B. Start up the database in NOMOUNT mode, verify or set the NLS_DATE_FORMAT environment variable, designate the SET UNTIL TIME time stamp, restore the necessary files with the RESTORE DATABASE command, recover the database with the RECOVER DATABASE command, and then open the database with the ALTER DATABASE OPEN
RESETLOGS command.
C. Start up the database in MOUNT mode, designate the SET UNTIL TIME time stamp, restore the necessary files with the RESTORE DATABASE command, recover the database with the RECOVER DATABASE command, and then open the database with ALTER DATABASE OPEN NORESETLOGS command.
D. Start up the database in MOUNT mode, verify or set the NLS_DATE_FORMAT environment variable, designate the SET UNTIL TIME time stamp, restore the necessary files with the RESTORE DATABASE command, recover the database with the RECOVER DATABASE command, and then open the database with ALTER DATABASE OPEN RESETLOGS command.
---
Ans: D.
The proper process of performing a RMAN based incomplete recovery utilizing a
time stamp to determine the point-in-time to complete the recovery process is as
follows: Start up the database in MOUNT mode, verify or set the NLS_DATE_FORMAT
environment variable if not present, designate the SET UNTIL TIME time stamp,
restore the necessary files with the RESTORE DATABASE command, recover the
database with the RECOVER DATABASE command, and then open the database with
ALTER DATABASE OPEN RESETLOGS command.
11. Which command is not a valid RMAN incomplete recovery run block?
A. run
{
set until change 7563633;
restore database;
recover database;
}
B. run
{
set until time '06-SEP-2004 11:25:00';
restore database;
recover database;
}
C. run
{
set until SCN 7563633;
restore database;
recover database;
}
D. run
{
set until sequence 3 thread 1;
restore database;
recover database;
}
---
Ans: A.
The SET UNTIL CHANGE command is not used with RMAN. This command is used during a user-managed incomplete recovery.
12. Which of the following would be a reason for using incomplete recovery?
(Choose all that apply.)
A. Stopping the recovery at a certain redo log sequence before a database corruption point
B. Stopping the recovery at a certain time when database corruption occurred
C. Stopping the recovery before a bad transaction is executed
D. Stopping the recovery only after applying all transactions
---
Ans: A, B, C.
Incomplete recovery is designed to be able to stop at a desired point, before introducing undesired transactions to the database.
13. Which incomplete recovery capability is available to RMAN or user-managed methods?
A. SET UNTIL TIME
B. UNTIL TIME
C. UNTIL SCN
D. UNTIL SEQUENCE
---
Ans: B.
The UNTIL TIME clause is available in both user-managed and RMAN-based incomplete recovery methods.
Note: the question should be 'RMAN and user-managed methods?'
14. When performing incomplete recovery, which command allows you to stop the recovery process at a random point?
A. UNTIL SEQUENCE, when performing a user-managed recovery
B. UNTIL SCN, when performing a RMAN-based recovery
C. UNTIL CANCEL, when performing a RMAN-based recovery
D. UNTIL CANCEL, when performing a user-managed recovery
----
Ans: D.
The UNTIL CANCEL command is available only in user-managed recovery. This command allows you to stop the recovery process at a random point during redo log switches.
15. Which command is required when performing an incomplete recovery?
A. ALTER DATABASE OPEN RESETLOGS
B. ALTER DATABASE OPEN NORESETLOGS
C. UNTIL CANCEL
D. ALTER DATABASE OPEN
----
Ans: A.
The ALTER DATABASE OPEN RESETLOGS command is required with every incomplete recovery. This is because the redo log sequence always needs to be reset.
16. When using EM to perform a whole database incomplete recovery, what sequence of events must occur? (Choose the best answer.)
A. The database must be shut down and started in NOMOUNT mode.
B. The database must be started in MOUNT mode.
C. The database must be shut down and started in MOUNT mode.
D. The database must be shut down and restarted.
--
Ans: C.
When using EM, the database must be shut down and started in MOUNT mode so
that a whole database backup can be performed when you are recovering the same
database EM is connected to.
17. Which credentials are needed to perform a recovery with EM? (Choose all that apply.)
A. Database account with SYSDBA privilege
B. Administrator account in Windows
C. Oracle account in Unix
D. Any Windows account
---
Ans: A, B, C.
You need two credentials when running a recovery with EM: the correct operating system account and the correct database account. The correct operating system account is an account similar to the Oracle account in Unix or the administrator account in Windows. The database account is any account that has SYSDBA privilege.
18. The RESETLOGS clause is required with which of the following types of incomplete recovery?
A. Using the UNTIL CANCEL command and applying almost all the archived redo logs before cancelling recovery
B. Using the UNTIL TIME command and stopping before the current time
C. Using the SET UNTIL SEQUENCE command and stopping before the last redo log sequence
D. All of the above
----
Ans: D.
The RESETLOGS clause is required with all incomplete recovery options. The RESETLOGS clause is required because you are opening the database to a point prior to the existing redo log entries. So the redo logs must be reset when the database is opened.
19. What is required to perform a UNTIL SEQUENCE recovery in RMAN?
A. Identifying the sequence number with V$LOGHISTORY
B. Identifying the sequence number with V$LOG_HISTORY
C. Identifying the SCN number with V$LOG_HISTORY
D. Identifying the SCN number with V$LOGHISTORY
----
Ans: B. You need to know the redo log sequence number and thread to perform an UNTIL SEQUENCE recovery in RMAN. This can be obtained by querying the V$LOG_HISTORY dynamic view.
20. What is required to recover your database through a RESETLOGS recovery from a backup created prior to the RESETLOGS recovery?
A. NORESETLOGS
B. RESETLOGS
C. UNTIL SEQUENCE
D. Nothing, this feature is automatic.
---
Ans: D.
The new feature to recover your database through a prior RESETLOGS recovery is native with Oracle 10g. Oracle will recover the database through the RESETLOGS prior to recovery if necessary.
Review Questions
1. What is another name for RMAN-based database recovery?
A. User-managed recovery
B. Server-managed recovery
C. Traditional recovery
D. Database recovery
--
Ans: B. Server-managed recovery is another name for RMAN recovery because the server
session performs the recovery process as it interacts with the target database.
2. What command is responsible for automating the backup of control files?
A. ALTER DATABASE CONTROLFILE AUTOBACKUP ON
B. ALTER SYSTEM CONTROLFILE AUTOBACKUP ON
C. CONFIGURE CONTROLFILE AUTOBACKUP ON
D. ENABLE CONTROLFILE AUTOBACKUP
---
Ans: C.
The control file autobackup is enabled by setting parameters within RMAN by
using CONFIGURE CONTROLFILE AUTOBACKUP ON.
3. What is the process to recover a control file?
A. Start up database, restore control file, start up mount the database, recover the database, and open the database.
B. Start up mount, restore control file, start up the database, recover the database, and open the database.
C. Start up nomount, restore control file, start up mount the database, recover the database, and open the database.
D. Start up force, restore control file, start up the database, recover the database, and open the database.
---
Ans: C.
The database needs to be started in NOMOUNT mode because there is not a control file available to MOUNT the database. Next, the control file can be restored. Once a restored control file is available, the database can be started in MOUNT mode so that standard database recovery can continue. When recovery is complete, the database can OPEN for normal use.
4. When recovering a control file without the recovery catalog, what special step must be performed to identify the target database? (Choose all that apply.)
A. You must CONNECT TARGET / to the target database within RMAN.
B. You must STARTUP MOUNT the database because the control file is missing.
C. You must SET DBID to the target database so that the target database can be identified without the control file available.
D. You must CONNECT TARGET database name to the target database within RMAN.
---
ANs: A, C.
The target database is not identifiable by database name without the control
file. So you must first use the CONNECT TARGET / command to connect. The target
database needs to be identified by the database identifier (DBID) number with the
command SET DBID database identifier. This database identifier number denotes the
target database. When you are recovering the control file, the target database
identification is not available because it is stored in the control file.
5. After you restore the control file, what must you do before you execute the RECOVER command to apply archive logs?
A. The database must be restored with the RESTORE command.
B. The database must be reconnected with the CONNECT TARGET database name command.
C. The database must be started in MOUNT mode.
D. The database must open for use with ALTER DATABASE OPEN command.
----
Ans: C. The database must be mounted before the RECOVER command can be executed.
You first must restore control so you can MOUNT the database.
6. Which of the following methods should you use for creating a control file?
(Choose all that apply.)
A. Dump the control file information to a trace file.
B. Use the ALTER DATABASE BACKUP CONTROLFILE TO TRACE command.
C. Use the CREATE CONTROLFILE command.
D. None of the above.
---
Ans: A, B.
The ALTER DATABASE BACKUP CONTROL FILE TO TRACE command creates a
user trace file, which stores an ASCII representation of the binary control file.
7. What are the two cases defined in the backup control file? (Choose two.)
A. ALTER DATABASE OPEN
B. ALTER DATABASE OPEN RESETLOGS
C. ALTER DATABASE OPEN NORESETLOGS
D. ALTER DATABASE OPEN NORESET
---
Ans: B, C.
The two cases in the backup control file are opening the database with RESETLOGS or NORESETLOGS.
8. Which files need to be available and in the matching location of the ASCII
control file in order to rebuild the control file? (Choose all that apply.)
A. Server file, PFILE or SPFILE
B. Datafiles
C. Control files
D. Redo logs
---
Ans: A, B.
The server file, SPFILE or PFILE must be available to start the database with the right parameters, and the datafiles must be in the location matching the control file. The redo logs and control file will be rebuilt.
9. Which of the following descriptions best describes incomplete recovery?
(Choose all that apply.)
A. Recovery that stops before the failure
B. Recovery that stops at the point of failure
C. Recovery that is missing transactions
D. Recovery that is not missing transactions
----
Ans: A, C.
Incomplete recovery is a recovery that stops before the failure and a recovery
that is missing transactions. Incomplete recovery is not complete or missing some
data that was previously stored in the database prior to the failure.
10. What are the required steps to perform a RMAN-based incomplete recovery with the SET UNTIL TIME clause?
A. Start up the database in MOUNT mode, verify or set the NLS_DATE_FORMAT environment variable, designate time with the SET
UNTIL TIME time stamp, restore the necessary files with the RESTORE DATABASE command, recover the database with the RECOVER DATABASE command, and then open the database with the ALTER DATABASE OPEN
command.
B. Start up the database in NOMOUNT mode, verify or set the NLS_DATE_FORMAT environment variable, designate the SET UNTIL TIME time stamp, restore the necessary files with the RESTORE DATABASE command, recover the database with the RECOVER DATABASE command, and then open the database with the ALTER DATABASE OPEN
RESETLOGS command.
C. Start up the database in MOUNT mode, designate the SET UNTIL TIME time stamp, restore the necessary files with the RESTORE DATABASE command, recover the database with the RECOVER DATABASE command, and then open the database with ALTER DATABASE OPEN NORESETLOGS command.
D. Start up the database in MOUNT mode, verify or set the NLS_DATE_FORMAT environment variable, designate the SET UNTIL TIME time stamp, restore the necessary files with the RESTORE DATABASE command, recover the database with the RECOVER DATABASE command, and then open the database with ALTER DATABASE OPEN RESETLOGS command.
---
Ans: D.
The proper process of performing a RMAN based incomplete recovery utilizing a
time stamp to determine the point-in-time to complete the recovery process is as
follows: Start up the database in MOUNT mode, verify or set the NLS_DATE_FORMAT
environment variable if not present, designate the SET UNTIL TIME time stamp,
restore the necessary files with the RESTORE DATABASE command, recover the
database with the RECOVER DATABASE command, and then open the database with
ALTER DATABASE OPEN RESETLOGS command.
11. Which command is not a valid RMAN incomplete recovery run block?
A. run
{
set until change 7563633;
restore database;
recover database;
}
B. run
{
set until time '06-SEP-2004 11:25:00';
restore database;
recover database;
}
C. run
{
set until SCN 7563633;
restore database;
recover database;
}
D. run
{
set until sequence 3 thread 1;
restore database;
recover database;
}
---
Ans: A.
The SET UNTIL CHANGE command is not used with RMAN. This command is used during a user-managed incomplete recovery.
12. Which of the following would be a reason for using incomplete recovery?
(Choose all that apply.)
A. Stopping the recovery at a certain redo log sequence before a database corruption point
B. Stopping the recovery at a certain time when database corruption occurred
C. Stopping the recovery before a bad transaction is executed
D. Stopping the recovery only after applying all transactions
---
Ans: A, B, C.
Incomplete recovery is designed to be able to stop at a desired point, before introducing undesired transactions to the database.
13. Which incomplete recovery capability is available to RMAN or user-managed methods?
A. SET UNTIL TIME
B. UNTIL TIME
C. UNTIL SCN
D. UNTIL SEQUENCE
---
Ans: B.
The UNTIL TIME clause is available in both user-managed and RMAN-based incomplete recovery methods.
Note: the question should be 'RMAN and user-managed methods?'
14. When performing incomplete recovery, which command allows you to stop the recovery process at a random point?
A. UNTIL SEQUENCE, when performing a user-managed recovery
B. UNTIL SCN, when performing a RMAN-based recovery
C. UNTIL CANCEL, when performing a RMAN-based recovery
D. UNTIL CANCEL, when performing a user-managed recovery
----
Ans: D.
The UNTIL CANCEL command is available only in user-managed recovery. This command allows you to stop the recovery process at a random point during redo log switches.
15. Which command is required when performing an incomplete recovery?
A. ALTER DATABASE OPEN RESETLOGS
B. ALTER DATABASE OPEN NORESETLOGS
C. UNTIL CANCEL
D. ALTER DATABASE OPEN
----
Ans: A.
The ALTER DATABASE OPEN RESETLOGS command is required with every incomplete recovery. This is because the redo log sequence always needs to be reset.
16. When using EM to perform a whole database incomplete recovery, what sequence of events must occur? (Choose the best answer.)
A. The database must be shut down and started in NOMOUNT mode.
B. The database must be started in MOUNT mode.
C. The database must be shut down and started in MOUNT mode.
D. The database must be shut down and restarted.
--
Ans: C.
When using EM, the database must be shut down and started in MOUNT mode so
that a whole database backup can be performed when you are recovering the same
database EM is connected to.
17. Which credentials are needed to perform a recovery with EM? (Choose all that apply.)
A. Database account with SYSDBA privilege
B. Administrator account in Windows
C. Oracle account in Unix
D. Any Windows account
---
Ans: A, B, C.
You need two credentials when running a recovery with EM: the correct operating system account and the correct database account. The correct operating system account is an account similar to the Oracle account in Unix or the administrator account in Windows. The database account is any account that has SYSDBA privilege.
18. The RESETLOGS clause is required with which of the following types of incomplete recovery?
A. Using the UNTIL CANCEL command and applying almost all the archived redo logs before cancelling recovery
B. Using the UNTIL TIME command and stopping before the current time
C. Using the SET UNTIL SEQUENCE command and stopping before the last redo log sequence
D. All of the above
----
Ans: D.
The RESETLOGS clause is required with all incomplete recovery options. The RESETLOGS clause is required because you are opening the database to a point prior to the existing redo log entries. So the redo logs must be reset when the database is opened.
19. What is required to perform a UNTIL SEQUENCE recovery in RMAN?
A. Identifying the sequence number with V$LOGHISTORY
B. Identifying the sequence number with V$LOG_HISTORY
C. Identifying the SCN number with V$LOG_HISTORY
D. Identifying the SCN number with V$LOGHISTORY
----
Ans: B. You need to know the redo log sequence number and thread to perform an UNTIL SEQUENCE recovery in RMAN. This can be obtained by querying the V$LOG_HISTORY dynamic view.
20. What is required to recover your database through a RESETLOGS recovery from a backup created prior to the RESETLOGS recovery?
A. NORESETLOGS
B. RESETLOGS
C. UNTIL SEQUENCE
D. Nothing, this feature is automatic.
---
Ans: D.
The new feature to recover your database through a prior RESETLOGS recovery is native with Oracle 10g. Oracle will recover the database through the RESETLOGS prior to recovery if necessary.
OCP Oracle 10g Admin II - Ch 3: Recovering From Non-Critical Losses
Chapter 3 - Recovering From Non-Critical Losses
Review Questions
1. Which of the following statements is true about non-critical losses?
A. Non-critical losses require media recovery.
B. Non-critical losses have a severe impact on database operations.
C. Non-critical losses can be resolved quickly with little impact to database operations.
D. Non-critical losses require the use of the RECOVER command.
---
ANs: C.
Non-critical losses have little impact on database operations if resolved properly.
2. Which of the following statements is true about temporary tablespaces?
A. Temporary tablespaces most often contain some permanent objects.
B. Temporary tablespaces are responsible for storing temporary or sort statements.
C. Temporary tablespaces must be recovered with the RECOVER command.
D. Temporary tablespaces cannot be managed locally.
---
ANs: B. Temporary tablespaces are responsible for storing temporary or sort segments.
These are used in the sorting of select statements or in building indexes.
3. Why is a missing tempfile considered a non-critical recovery situation?
A. The tempfile is dictionary managed and can only contain some permanent objects.
B. The tempfile is locally managed and can only contain some temporary objects.
C. The tempfile is locally managed and can only contain temporary objects.
D. The tempfile is dictionary managed and can only contain temporary objects.
---
Ans: C.
A missing tempfile can be re-created with the create tablespace command.
Because the tempfile is locally managed and contains no permanent data, no restoring
or recovering is needed. Therefore recovery can occur quickly with minimal impact
to database operations.
4. How can you resolve a missing temporary tablespace quickly? (Choose all that apply.)
A. Recover the tablespace immediately because restoring is not needed.
B. Restore the tablespace from disk and not from tape.
C. Run CREATE TEMPORARY TABLESPACE and then ALTER DATABASE to the new temporary tablespace.
D. If a temporary tablespace exists, then run ALTER DATABASE to the existing temporary tablespace.
---
Ans: C, D.
A missing temporary tablespace can be quickly reassigned with the command
ALTER DATABASE to an existing temporary tablespace if one is available. If one is not
available, you will need to run CREATE TEMPORARY TABLESPACE and then perform
the ALTER DATABASE command.
5. What must be done to recover a missing redo log file member?
A. First perform a ALTER DATABASE DROP LOGFILE MEMBER filename and then ALTER DATABASE ADD LOGFILE MEMBER filename on the
missing logfile member.
B. Perform ALTER DATABASE ADD LOGFILE MEMBER filename on the missing logfile.
C. Nothing is required if you have multiplexed redo logs.
D. Nothing is required if you do not have multiplexed redo logs.
---
Ans: A.
The missing redo log must first be dropped even though it doesn't exist physically
in the file system. This removes the redo log metadata from the data dictionary. Next
the log can be added back to database.
6. How would you know if you have lost a mirrored redo log member?
A. The database would hang.
B. The archive process would stop working.
C. The alert log would display an error, and the database would hang.
D. The alert log would display an error, and the database would process the archive logs.
----
Ans: D.
If your database has mirrored redo logs and a member is deleted, the database will
function as normal. The error signaling that a log member has been deleted would be
written to the alert log.
7. What happens if the current or active online redo log group has a new member added?
A. Nothing, the redo log member will be added.
B. The redo log member will not be added because the log group is actively recording transactions.
C. The redo log member will be added, but it will be out of sync until a log switch occurs.
D. The redo log member will be added, but it will be empty.
----
Ans: B.
The redo log member will not be added to the current or active redo log group. Oracle will not allow this because transactions are actively being written to the redo log group.
8. What happens when you are recovering a temporary tablespace by switching to another available tablespace? (Choose all that apply.)
A. The new temporary tablespace is made available if the tablespace is permanent.
B. The new temporary tablespace is made available if the tablespace is temporary.
C. You will receive an ORA-12904 error if the available tablespace is temporary.
D. You will receive an ORA-12904 error if the available tablespace is permanent.
----
Ans B, D.
You must use the ALTER DATABASE DEFAULT TEMPORARY TABLESPACE table name command on a temporary tablespace or you will receive an ORA-12904 error.
9. How can you rebuild the objects in the index tablespace most efficiently?
A. Recover the index from tape.
B. Rebuild the index with LOGGING.
C. Rebuild the index with NOLOGGING.
D. Rebuild the index in parallel with NOLOGGING.
----
Ans: D.
Rebuilding an index in parallel with NOLOGGING is the most efficient method of
building an index to minimize the impact on database operations. You must be
cautious not to use extensive server resources when you don't use the parallel rebuild
option.
10. What should be updated and readily accessible in case of a non-critical loss of a tablespace?
A. Temporary segments
B. SELECT statements using sorting
C. Current index scripts
D. Create table scripts
---
Ans: C.
The index scripts must be current and stored locally so they can be accessed quickly and easily during an index tablespace rebuild.
11. Which type of tablespace contains static data?
A. Read-only tablespace
B. Index tablespace
C. Read-write tablespace
D. Temporary tablespace
---
Ans: A.
The read-only tablespace contains only static or non-changing data.
12. What is the result of starting an Oracle database with a missing read-only
tablespace? (Choose two.)
A. The database opens normally.
B. The database only mounts.
C. An ORA-01157 cannot identify datafile halts the database from opening error occurs.
D. The database functions normally, and the read-only tablespace is static.
--
Ans: B, C.
The database will stop at the mount stage. This is due to the ORA-01157 error.
B, C
13. When connecting to an Oracle database locally to perform administrative functions, you are connecting with what access privilege?
A. SQL*Net
B. IPC
C. SYSDBA
D. SYSOPER
---
Ans: B.
IPC is the method that the local administrative access uses to connect to the database.
14. What is the proper way of creating a password supporting up to 10 users?
A. orapwd file=orapwORA101T password=syspass users=10
B. orapwd file=orapwORA101T password=syspass entry=10
C. orapwd file=orapwORA101T password=syspass entries=10
D. orapass file=orapwORA101T password=syspass entries=10
---
Ans: C. The correct command to create a password file that supports 10 users is orapwd
file=orapwORA101T password=syspass entries=10.
15. Which initialization parameter is required for remote access to the database?
A. REMOTE_LOGIN_PASSWORDFILE
B. REMOTE_LOGIN_PASSWORD_FILE
C. REMOTE_PASSWORD_FILE
D. REMOTE_LOGIN_FILE
---
Ans: A.
The initialization parameter required for remote access is REMOTE_LOGIN_PASSWORDFILE.
16. Which directory should the password file be stored in to function properly?
(Choose all that apply.)
A. $ORACLE_HOME
B. $ORACLE_HOME/dbs
C. C:\$ORACLE_HOME\database
D. $ORACLE_SID
---
Ans: B, C.
The $ORACLE_HOME/dbs is the Unix location for the password file, and
C:\$ORACLE_ HOME\database is the Windows location for the password file.
17. Before running the ORAPWD utility to generate a password file, what should be done?
A. Start the database but make sure it isn't open.
B. Start up the database.
C. Start up the database in MOUNT mode.
D. Shut down the database.
---
Ans: D.
The database should be shut down before running the ORAPWD utility.
18. What do local connections to an Oracle database rely on for security and authentication of the user?
A. Password file
B. Database password
C. Operating system password
D. Listener password
---
Ans: C.
Local connections are secured by the operating system password logging on to an
administrative account such as Oracle user in Unix and administrator in Windows.
19. The main reason that you need to restore and use the RECOVER command on a
tablespace that was backed up read-write and converted to read-only is due to
what? (Choose all that apply.)
A. The checkpointing process has changed the control file.
B. There have been changes to the tablespace.
C. The tablespace file header has been changed.
D. Read-only tablespaces require the RECOVER command.
---
Ans: A, B, C.
The scenario of read-write to read-only tablespace requires the use of the
RECOVER command, which will apply necessary redo changes to make the tablespace
consistent with the control file. The checkpointing operation of Oracle will change
the control file, and the header of the read-only tablespace will be modified, which
equates to changes in the tablespace.
A, B, C
20. Which situation is considered a non-critical loss to the database? (Choose all
that apply.)
A. Loss of redo log group before archived
B. Loss of current or active redo member
C. Loss of archive log
D. Loss of current or active redo group
--
Ans: B, C.
A non-critical loss should have limited impact on database operations. This
means that incomplete recovery or media failure scenarios need to be performed. A
loss of a redo group before archived will require incomplete recovery, which is the
same as the loss of current or active redo log group. The current or active member or
loss of archive will not significantly impact operations. A backup can be performed
to eliminate the need for the archive log, and the database will function normally with
the loss of a redo log member.
Review Questions
1. Which of the following statements is true about non-critical losses?
A. Non-critical losses require media recovery.
B. Non-critical losses have a severe impact on database operations.
C. Non-critical losses can be resolved quickly with little impact to database operations.
D. Non-critical losses require the use of the RECOVER command.
---
ANs: C.
Non-critical losses have little impact on database operations if resolved properly.
2. Which of the following statements is true about temporary tablespaces?
A. Temporary tablespaces most often contain some permanent objects.
B. Temporary tablespaces are responsible for storing temporary or sort statements.
C. Temporary tablespaces must be recovered with the RECOVER command.
D. Temporary tablespaces cannot be managed locally.
---
ANs: B. Temporary tablespaces are responsible for storing temporary or sort segments.
These are used in the sorting of select statements or in building indexes.
3. Why is a missing tempfile considered a non-critical recovery situation?
A. The tempfile is dictionary managed and can only contain some permanent objects.
B. The tempfile is locally managed and can only contain some temporary objects.
C. The tempfile is locally managed and can only contain temporary objects.
D. The tempfile is dictionary managed and can only contain temporary objects.
---
Ans: C.
A missing tempfile can be re-created with the create tablespace command.
Because the tempfile is locally managed and contains no permanent data, no restoring
or recovering is needed. Therefore recovery can occur quickly with minimal impact
to database operations.
4. How can you resolve a missing temporary tablespace quickly? (Choose all that apply.)
A. Recover the tablespace immediately because restoring is not needed.
B. Restore the tablespace from disk and not from tape.
C. Run CREATE TEMPORARY TABLESPACE and then ALTER DATABASE to the new temporary tablespace.
D. If a temporary tablespace exists, then run ALTER DATABASE to the existing temporary tablespace.
---
Ans: C, D.
A missing temporary tablespace can be quickly reassigned with the command
ALTER DATABASE to an existing temporary tablespace if one is available. If one is not
available, you will need to run CREATE TEMPORARY TABLESPACE and then perform
the ALTER DATABASE command.
5. What must be done to recover a missing redo log file member?
A. First perform a ALTER DATABASE DROP LOGFILE MEMBER filename and then ALTER DATABASE ADD LOGFILE MEMBER filename on the
missing logfile member.
B. Perform ALTER DATABASE ADD LOGFILE MEMBER filename on the missing logfile.
C. Nothing is required if you have multiplexed redo logs.
D. Nothing is required if you do not have multiplexed redo logs.
---
Ans: A.
The missing redo log must first be dropped even though it doesn't exist physically
in the file system. This removes the redo log metadata from the data dictionary. Next
the log can be added back to database.
6. How would you know if you have lost a mirrored redo log member?
A. The database would hang.
B. The archive process would stop working.
C. The alert log would display an error, and the database would hang.
D. The alert log would display an error, and the database would process the archive logs.
----
Ans: D.
If your database has mirrored redo logs and a member is deleted, the database will
function as normal. The error signaling that a log member has been deleted would be
written to the alert log.
7. What happens if the current or active online redo log group has a new member added?
A. Nothing, the redo log member will be added.
B. The redo log member will not be added because the log group is actively recording transactions.
C. The redo log member will be added, but it will be out of sync until a log switch occurs.
D. The redo log member will be added, but it will be empty.
----
Ans: B.
The redo log member will not be added to the current or active redo log group. Oracle will not allow this because transactions are actively being written to the redo log group.
8. What happens when you are recovering a temporary tablespace by switching to another available tablespace? (Choose all that apply.)
A. The new temporary tablespace is made available if the tablespace is permanent.
B. The new temporary tablespace is made available if the tablespace is temporary.
C. You will receive an ORA-12904 error if the available tablespace is temporary.
D. You will receive an ORA-12904 error if the available tablespace is permanent.
----
Ans B, D.
You must use the ALTER DATABASE DEFAULT TEMPORARY TABLESPACE table name command on a temporary tablespace or you will receive an ORA-12904 error.
9. How can you rebuild the objects in the index tablespace most efficiently?
A. Recover the index from tape.
B. Rebuild the index with LOGGING.
C. Rebuild the index with NOLOGGING.
D. Rebuild the index in parallel with NOLOGGING.
----
Ans: D.
Rebuilding an index in parallel with NOLOGGING is the most efficient method of
building an index to minimize the impact on database operations. You must be
cautious not to use extensive server resources when you don't use the parallel rebuild
option.
10. What should be updated and readily accessible in case of a non-critical loss of a tablespace?
A. Temporary segments
B. SELECT statements using sorting
C. Current index scripts
D. Create table scripts
---
Ans: C.
The index scripts must be current and stored locally so they can be accessed quickly and easily during an index tablespace rebuild.
11. Which type of tablespace contains static data?
A. Read-only tablespace
B. Index tablespace
C. Read-write tablespace
D. Temporary tablespace
---
Ans: A.
The read-only tablespace contains only static or non-changing data.
12. What is the result of starting an Oracle database with a missing read-only
tablespace? (Choose two.)
A. The database opens normally.
B. The database only mounts.
C. An ORA-01157 cannot identify datafile halts the database from opening error occurs.
D. The database functions normally, and the read-only tablespace is static.
--
Ans: B, C.
The database will stop at the mount stage. This is due to the ORA-01157 error.
B, C
13. When connecting to an Oracle database locally to perform administrative functions, you are connecting with what access privilege?
A. SQL*Net
B. IPC
C. SYSDBA
D. SYSOPER
---
Ans: B.
IPC is the method that the local administrative access uses to connect to the database.
14. What is the proper way of creating a password supporting up to 10 users?
A. orapwd file=orapwORA101T password=syspass users=10
B. orapwd file=orapwORA101T password=syspass entry=10
C. orapwd file=orapwORA101T password=syspass entries=10
D. orapass file=orapwORA101T password=syspass entries=10
---
Ans: C. The correct command to create a password file that supports 10 users is orapwd
file=orapwORA101T password=syspass entries=10.
15. Which initialization parameter is required for remote access to the database?
A. REMOTE_LOGIN_PASSWORDFILE
B. REMOTE_LOGIN_PASSWORD_FILE
C. REMOTE_PASSWORD_FILE
D. REMOTE_LOGIN_FILE
---
Ans: A.
The initialization parameter required for remote access is REMOTE_LOGIN_PASSWORDFILE.
16. Which directory should the password file be stored in to function properly?
(Choose all that apply.)
A. $ORACLE_HOME
B. $ORACLE_HOME/dbs
C. C:\$ORACLE_HOME\database
D. $ORACLE_SID
---
Ans: B, C.
The $ORACLE_HOME/dbs is the Unix location for the password file, and
C:\$ORACLE_ HOME\database is the Windows location for the password file.
17. Before running the ORAPWD utility to generate a password file, what should be done?
A. Start the database but make sure it isn't open.
B. Start up the database.
C. Start up the database in MOUNT mode.
D. Shut down the database.
---
Ans: D.
The database should be shut down before running the ORAPWD utility.
18. What do local connections to an Oracle database rely on for security and authentication of the user?
A. Password file
B. Database password
C. Operating system password
D. Listener password
---
Ans: C.
Local connections are secured by the operating system password logging on to an
administrative account such as Oracle user in Unix and administrator in Windows.
19. The main reason that you need to restore and use the RECOVER command on a
tablespace that was backed up read-write and converted to read-only is due to
what? (Choose all that apply.)
A. The checkpointing process has changed the control file.
B. There have been changes to the tablespace.
C. The tablespace file header has been changed.
D. Read-only tablespaces require the RECOVER command.
---
Ans: A, B, C.
The scenario of read-write to read-only tablespace requires the use of the
RECOVER command, which will apply necessary redo changes to make the tablespace
consistent with the control file. The checkpointing operation of Oracle will change
the control file, and the header of the read-only tablespace will be modified, which
equates to changes in the tablespace.
A, B, C
20. Which situation is considered a non-critical loss to the database? (Choose all
that apply.)
A. Loss of redo log group before archived
B. Loss of current or active redo member
C. Loss of archive log
D. Loss of current or active redo group
--
Ans: B, C.
A non-critical loss should have limited impact on database operations. This
means that incomplete recovery or media failure scenarios need to be performed. A
loss of a redo group before archived will require incomplete recovery, which is the
same as the loss of current or active redo log group. The current or active member or
loss of archive will not significantly impact operations. A backup can be performed
to eliminate the need for the archive log, and the database will function normally with
the loss of a redo log member.
OCP Oracle 10g Admin II - Ch 2: Using Recovery Manager
Chapter 2 - Using Recovery Manager
Review Questions
1. How is block change tracking enabled?
A. With ALTER DATABASE ENABLE BLOCK CHANGE TRACKING
B. With ALTER SYSTEM ENABLE BLOCK CHANGE TRACKING
C. With a INIT.ORA parameter change
D. With a SPFILE parameter change
---
Ans: A.
Block change tracking must be enabled with ALTER DATABASE ENABLE BLOCK CHANGE TRACKING. The physical location and name of the block change tracking file must be supplied.
2. What type of backup is stored in a proprietary RMAN format?
A. Backup set
B. Image copy
C. Backup section
D. Backup group
----
Ans: A.
The backup set is stored in a proprietary RMAN format, where only used blocks are backed up.
3. What method is used to improve backup performance when creating backup sets?
A. Multicopy
B. Multiplecopy
C. Multiplex
D. Multiply
---
Ans: C.
Multiplexing a backup is designed to improve the performance of the backup sets by copying multiple database files at the same time. Multiplexing can be used with image copies or backup sets.
4. Which command creates an image copy?
A. BACKUP AS COPY
B. BACKUP COPY
C. COPY AS BACKUP
D. COPY BACK
----
Ans: A.
The BACKUP AS COPY command is used to create an image copy backup.
5. Compressed backups work with which of the following commands?
A. COPY AS BACKUP
B. BACKUP AS COPY
C. BACKUP
D. COPY
-----
Ans: C.
Compressed backups work only with backup sets, not image copies. Thus compressed backups will work only with the BACKUP command.
6. What ratio are compressed backups sized to?
A. 30 percent
B. 40 percent
C. 70 percent
D. 20 percent
---
Ans: D.
Compressed backups compressed at a ratio of 5 to 1 - 20 percent - of the original
size of a standard backup.
7. Which of the following best describes a full backup?
A. All datafiles of a database
B. All datafiles, archive logs, and control files
C. All datafiles and control files
D. All the used blocks in a datafile
---
Ans: D.
A full backup is best described by backing up all the used blocks in a datafile or
any database file. A full backup can be taken on one database file.
8. Which type of backup backs up only data blocks modified since the most recent backup at the same level or lower?
A. Differential incremental backup
B. Different incremental backup
C. Cumulative backup
D. Cumulative incremental backup
----
Ans: A.
A differential incremental backup only backs up blocks that have been modified
since a backup at the same level or lower.
9. Which type of backup must be performed first with an incremental backup?
A. Level 1
B. Level 0
C. Level 2
D. Level 3
----
Ans: B.
A level 0 backup is the first backup that is performed when implementing an
incremental backup strategy. A level 0 backup copies all the used blocks as a
baseline.
10. Which backup option defines a user-defined name for a backup?
A. FORMAT
B. NAME
C. TAG
D. FORMAT U%
----
Ans: C.
The TAG option is used to name a backup with a user-defined character string.
11. What action requires the RESTORE command?
A. Restoring a backup set
B. Restoring an image copy
C. Performing all RMAN recovery processes
D. All of the above
---
Ans: A.
The RESTORE command is required only with a backup set recovery. This is
because the backup set is stored in a RMAN proprietary format.
12. Which of the following most closely represents an image copy?
A. Unix cp command of a file
B. Bit-by-bit copy of a file
C. Windows COPY command of a file
D. All of the above
---
Ans: D.
Image copies are similar to operating system copy commands. These equate to bitby-
bit copies of a file.
13. Which dynamic view displays the status of block change tracking?
A. V$BLOCK_CHANGE
B. V$BLOCK_CHANGE_TRACKING
C. V$BLOCKCHANGE
D. V$BLOCK_TRACKING
---
Ans: B.
The V$BLOCK_CHANGE_TRACKING dynamic view shows the filename, status, and size of the block change tracking file.
14. Which new Oracle 10g capability improves the backup efficency database of any size?
A. Differential incremental backup
B. Cumulative incremental backup
C. Block change tracking
D. Compressed backup
---
Ans: C.
Block change tracking improves the backup efficiency of large or small databases.
With block change tracking, the backup time is proportional to the amount of
changes that occurred in a database.
15. Where are RMAN dynamic views stored?
A. Recovery catalog database
B. Control file of the recovery catalog database
C. Control file of the target database
D. Target database
---
Ans: D.
RMAN dynamic views are stored in the target database.
16. Which of the following can be used to monitor RMAN backups?
A. LIST commands
B. REPORT commands
C. RMAN dynamic views
D. All of the above
---
Ans: D.
LIST, REPORT, and dynamic views all allow different types of monitoring of RMAN activities.
17. The LIST commands are best used to identify what about RMAN?
A. How many times the database is restored
B. Files impacted by the CHANGE, CROSSCHECK, and DELETE commands
C. Only data in the recovery catalog
D. All of the above
----
Ans: B.
The LIST commands are used to determine files impacted by the CHANGE,
CROSSCHECK, and DELETE commands.
18. The REPORT command is best used to identify what about RMAN?
A. Which files need a backup
B. Unneeded backups
C. Database physical schema
D. If unrecoverable operations were performed on files
E. All of the above
---
Ans: E. The REPORT command is best used to identify which files need a backup, unneeded backups, database physical schema, and if unrecoverable operations were performed on files.
19. What should be done to generate accurate information from the REPORT command?
A. Recently performed CHANGE command
B. Recently performed UNCATALOG command
C. Recently performed CROSSCHECK commands
D. RMAN repository synchronized with the control file
E. All of the above
---
Ans: E.
The REPORT command is accurate when the control and RMAN repository are
synchronized, which can be performed by the CHANGE, UNCATALOG, and CROSSCHECK
commands.
20. Identify the methods used to schedule a backup job. (Choose all that apply.)
A. A third-party GUI tool provided by the media management provider
B. A file that is scheduled with the operating system scheduler
C. Running the EM Maintenance utility
D. Running RMAN from CLI
---
Ans: A, B.
Third-party GUI tools provided from the media manager software and
manually scripting a file incorporated with operating system scheduler are the two
methods for scheduling a backup.
Review Questions
1. How is block change tracking enabled?
A. With ALTER DATABASE ENABLE BLOCK CHANGE TRACKING
B. With ALTER SYSTEM ENABLE BLOCK CHANGE TRACKING
C. With a INIT.ORA parameter change
D. With a SPFILE parameter change
---
Ans: A.
Block change tracking must be enabled with ALTER DATABASE ENABLE BLOCK CHANGE TRACKING. The physical location and name of the block change tracking file must be supplied.
2. What type of backup is stored in a proprietary RMAN format?
A. Backup set
B. Image copy
C. Backup section
D. Backup group
----
Ans: A.
The backup set is stored in a proprietary RMAN format, where only used blocks are backed up.
3. What method is used to improve backup performance when creating backup sets?
A. Multicopy
B. Multiplecopy
C. Multiplex
D. Multiply
---
Ans: C.
Multiplexing a backup is designed to improve the performance of the backup sets by copying multiple database files at the same time. Multiplexing can be used with image copies or backup sets.
4. Which command creates an image copy?
A. BACKUP AS COPY
B. BACKUP COPY
C. COPY AS BACKUP
D. COPY BACK
----
Ans: A.
The BACKUP AS COPY command is used to create an image copy backup.
5. Compressed backups work with which of the following commands?
A. COPY AS BACKUP
B. BACKUP AS COPY
C. BACKUP
D. COPY
-----
Ans: C.
Compressed backups work only with backup sets, not image copies. Thus compressed backups will work only with the BACKUP command.
6. What ratio are compressed backups sized to?
A. 30 percent
B. 40 percent
C. 70 percent
D. 20 percent
---
Ans: D.
Compressed backups compressed at a ratio of 5 to 1 - 20 percent - of the original
size of a standard backup.
7. Which of the following best describes a full backup?
A. All datafiles of a database
B. All datafiles, archive logs, and control files
C. All datafiles and control files
D. All the used blocks in a datafile
---
Ans: D.
A full backup is best described by backing up all the used blocks in a datafile or
any database file. A full backup can be taken on one database file.
8. Which type of backup backs up only data blocks modified since the most recent backup at the same level or lower?
A. Differential incremental backup
B. Different incremental backup
C. Cumulative backup
D. Cumulative incremental backup
----
Ans: A.
A differential incremental backup only backs up blocks that have been modified
since a backup at the same level or lower.
9. Which type of backup must be performed first with an incremental backup?
A. Level 1
B. Level 0
C. Level 2
D. Level 3
----
Ans: B.
A level 0 backup is the first backup that is performed when implementing an
incremental backup strategy. A level 0 backup copies all the used blocks as a
baseline.
10. Which backup option defines a user-defined name for a backup?
A. FORMAT
B. NAME
C. TAG
D. FORMAT U%
----
Ans: C.
The TAG option is used to name a backup with a user-defined character string.
11. What action requires the RESTORE command?
A. Restoring a backup set
B. Restoring an image copy
C. Performing all RMAN recovery processes
D. All of the above
---
Ans: A.
The RESTORE command is required only with a backup set recovery. This is
because the backup set is stored in a RMAN proprietary format.
12. Which of the following most closely represents an image copy?
A. Unix cp command of a file
B. Bit-by-bit copy of a file
C. Windows COPY command of a file
D. All of the above
---
Ans: D.
Image copies are similar to operating system copy commands. These equate to bitby-
bit copies of a file.
13. Which dynamic view displays the status of block change tracking?
A. V$BLOCK_CHANGE
B. V$BLOCK_CHANGE_TRACKING
C. V$BLOCKCHANGE
D. V$BLOCK_TRACKING
---
Ans: B.
The V$BLOCK_CHANGE_TRACKING dynamic view shows the filename, status, and size of the block change tracking file.
14. Which new Oracle 10g capability improves the backup efficency database of any size?
A. Differential incremental backup
B. Cumulative incremental backup
C. Block change tracking
D. Compressed backup
---
Ans: C.
Block change tracking improves the backup efficiency of large or small databases.
With block change tracking, the backup time is proportional to the amount of
changes that occurred in a database.
15. Where are RMAN dynamic views stored?
A. Recovery catalog database
B. Control file of the recovery catalog database
C. Control file of the target database
D. Target database
---
Ans: D.
RMAN dynamic views are stored in the target database.
16. Which of the following can be used to monitor RMAN backups?
A. LIST commands
B. REPORT commands
C. RMAN dynamic views
D. All of the above
---
Ans: D.
LIST, REPORT, and dynamic views all allow different types of monitoring of RMAN activities.
17. The LIST commands are best used to identify what about RMAN?
A. How many times the database is restored
B. Files impacted by the CHANGE, CROSSCHECK, and DELETE commands
C. Only data in the recovery catalog
D. All of the above
----
Ans: B.
The LIST commands are used to determine files impacted by the CHANGE,
CROSSCHECK, and DELETE commands.
18. The REPORT command is best used to identify what about RMAN?
A. Which files need a backup
B. Unneeded backups
C. Database physical schema
D. If unrecoverable operations were performed on files
E. All of the above
---
Ans: E. The REPORT command is best used to identify which files need a backup, unneeded backups, database physical schema, and if unrecoverable operations were performed on files.
19. What should be done to generate accurate information from the REPORT command?
A. Recently performed CHANGE command
B. Recently performed UNCATALOG command
C. Recently performed CROSSCHECK commands
D. RMAN repository synchronized with the control file
E. All of the above
---
Ans: E.
The REPORT command is accurate when the control and RMAN repository are
synchronized, which can be performed by the CHANGE, UNCATALOG, and CROSSCHECK
commands.
20. Identify the methods used to schedule a backup job. (Choose all that apply.)
A. A third-party GUI tool provided by the media management provider
B. A file that is scheduled with the operating system scheduler
C. Running the EM Maintenance utility
D. Running RMAN from CLI
---
Ans: A, B.
Third-party GUI tools provided from the media manager software and
manually scripting a file incorporated with operating system scheduler are the two
methods for scheduling a backup.
OCP Oracle 10g Admin II - Ch 8: Understanding Automatic Database Management
Chapter 8: Understanding Automatic Database Management
Review Questions
.
1. Which of the following is not a valid ADDM finding type?
A. Error
B. Problem
C. Information
D. Symptom
E. All are valid ADDM finding types.
-----
Ans: A.
All ADDM analysis results are categorized as Problem, Symptom, or Information
findings. Problem represents the root problem identified, Symptom identifies a
symptom resulting from the root problem, and Information reports on non-problem
areas. ADDM does not classify its findings as Errors.
2. What is the default setting for the STATISTICS_LEVEL initialization parameter?
A. BASIC
B. ALL
C. STANDARD
D. TYPICAL
E. None of the above
----
Ans: D.
The default setting for STATISTICS_LEVEL is TYPICAL, which ensures that
adequate statistics are gathered to support all of the automatic database management
features.
3. Which of the following would be used to set or change the value for DBIO_EXPECTED?
A. The DBIO_EXPECTED initialization parameter
B. The ALTER SYSTEM statement
C. The ALTER SESSION statement
D. All of the above will work.
E. None of the above will work.
----
Ans: E.
The value for DBIO_EXPECTED is set by using the DBMS_ADVISOR.SET_DEFAULT_TASK_PARAMETER procedure. It is not an initialization parameter value, so answers A, B, and C cannot be correct, because all of them deal with initialization parameters.
4. Which of the following are types of problems that the ADDM will consider?
(Choose all that apply.)
A. Database configuration issues
B. Concurrency issues
C. CPU bottlenecks
D. Suboptimal use of Oracle by an application
E. All of the above
---
Ans: E.
ADDM considers all of these problem areas and more when performing its analysis.
5. The statistical data needed for ADDM to accurately diagnose problems is
stored in which of the following areas?
A. Automatic Workload Repository (AWR)
B. Data dictionary
C. ADDM repository
D. PERFSTAT tables
E. None of the above
---
Ans: A.
ADDM, along with all the other advisors, utilize the AWR. The data dictionary
stores optimizer statistics, whereas ADDM deals with performance statistics, so
choice B is wrong. There is no such thing as an ADDM repository; therefore, choice
C is wrong. And PERFSTAT tables are a part of Statspack, which is no longer used
in Oracle 10g.
6. Which of the following is not a valid DBMS_ADVISOR procedure or function?
A. CREATE_TASK_SCRIPT
B. RESTART_TASK
C. INTERRUPT_TASK
D. RESUME_TASK
E. None of the above
---
ANs: B.
There is no RESTART_TASK procedure or function. Choices A, C, and D all
represent valid procedures. E is obviously wrong.
7. In an RAC environment, which element(s) always uniquely identifies a snapshot?
A. INSTANCE_ID
B. CLUSTER_ID
C. SNAP_ID
D. Both A and C
E. A, B, and C
---
Ans: D.
Both A and C are required to uniquely identify snapshots across Real Application
Clusters (RACs). In a stand-alone environment, the SNAP_ID is guaranteed to be
unique. However, with RAC, a snapshot shares the same SNAP_ID across all
instances, so the INSTANCE_ID is used to differentiate between them.
8. If the ASH buffer is filled in less than 30 minutes, which process is responsible for flushing it?
A. SMON
B. MMON
C. MMNL
D. PMON
E. AMON
---
Ans: C.
While MMON is normally tasked with flushing the ASH buffer every 30 minutes,
the MMNL process performs the task if the buffer fills before that time. SMON and
PMON don't have any interaction with the ASH flush, and AMON is not a valid
Oracle process name.
9. Which are valid SCOPE settings for the SQL Tuning Advisor? (Choose all that apply.)
A. Full
B. Comprehensive
C. Detailed
D. Basic
E. Limited
---
Ans: B, E.
SQL Tuning Advisor can perform either Comprehensive or Limited analysis,
based on the SCOPE setting. Answers A, C, and D are all invalid settings for the
SCOPE parameter.
10. If a metrics threshold is defined with an OBSERVATION_PERIOD setting of 5 and
a CONSECUTIVE_OCCURRENCES setting of 2, an alert would be generated after
how many minutes of consecutive violations?
A. 10
B. 15
C. 20
D. 5
E. None of the above
---
ANs: B.
The CONSECUTIVE_OCCURRENCES parameter defines the number of allowable
violations without an alert. This number must be exceeded before an alert is
generated. Because the values are checked every five minutes (as defined by the
OBSERVATION_PERIOD parameter), an alert would be generated after 15 minutes.
11. Which of the following statements regarding the Automatic Tuning Optimizer (ATO) is incorrect?
A. The ATO generates more efficient execution plans than the query optimizer in normal mode.
B. The ATO is nothing more than the query optimizer in tuning mode.
C. The ATO can take a long time to process a SQL statement, especially when performing a comprehensive analysis.
D. The ATO does generate a SQL Profile when performing a limited analysis.
E. All the above statements are correct.
---
Ans: A.
The Automatic Tuning Optimizer (ATO) does not generate execution plans at all.
Instead, it will return recommendations for optimization, along with their expected
benefit and rationale.
12. The AWR resides in which tablespace?
A. SYSTEM
B. TOOLS
C. SYSAUX
D. AWR
E. None of the above
---
Ans: C.
The AWR is the primary occupant of the SYSAUX tablespace.
13. Which DBMS_STATS function is used to allow Oracle to dynamically choose an
appropriate parallel degree based on the size of the object and the values of
certain initialization parameters?
A. AUTO
B. CHOOSE
C. DETECT
D. AUTO_DEGREE
E. BEST_DEGREE
----
Ans: D.
The AUTO_DEGREE function chooses the most appropriate degree of parallelism
based on factors such as object size and init.ora settings.
14. Which Oracle process updates the data dictionary with DML activity
information gathered by the Automatic DML Monitoring feature?
A. SMON
B. PMON
C. MMNL
D. DMON
E. MMON
---
Ans: A.
The SMON process updates the data dictionary with DML activity information
approximately every three hours, as well as at instance shutdown.
15. Which of the following views can be queried to display advisor findings?
A. V$ADVISOR_FINDINGS
B. DBA_ADV_FINDINGS
C. DBA_ADV_RECOMMENDATIONS
D. DBA_ADVISOR_FINDINGS
E. Findings can be viewed only through Enterprise Manager.
----
Ans: D.
The DBA_ADVISOR_FINDINGS view shows the findings from any advisor
execution. Though they can also be viewed through Enterprise Manager, answer E is incorrect.
16. Which view shows the current AWR settings?
A. DBA_AWR_SETTINGS
B. DBA_AWR_CONFIG
C. DBA_HIST_WR_CONTROL
D. DBA_HIST_WR_SETTINGS
E. DBA_REPOSITORY
---
Ans: C.
The DBA_HIST_WR_CONTROL view shows information about the AWR, including
the current settings.
17. On an eight-CPU system with a Shared Pool size of 1GB, what size would the
ASH buffer be set to?
A. 8MB
B. 16MB
C. 50MB
D. 32MB
E. 32KB
---
Ans: B. The ASH buffer will be set to the lesser of:
(Total number of CPUs × 2MB)
or
(Shared Pool size × 5%)
8 CPUs × 2MB = 16MB
1GB × 5% = 50MB Therefore, the ASH will be sized at 16MB.
18. _______________ is a hash value that uniquely identifies a SQL statement in
the database.
A. SQL_HASH
B. SQL_HASH_ID
C. SQL_ID
D. SID
E. SPID
---
Ans: C.
SQL_ID, new in Oracle 10g, is a hash value that uniquely identifies a single SQL
statement within a database.
19. Which package(s) can be used to interface with the SQL Tuning Advisor?
(Choose all that apply.)
A. DBMS_ADVISOR
B. DBMS_SQL_TUNE
C. DBMS_SQLTUNE
D. DBMS_SQL_ADVISOR
E. DBMS_SQL_TUNE_ADV
---
Ans: A, C.
DBMS_SQLTUNE is designed specifically as the interface to the SQL Tuning
Advisor. However, the DBMS_ADVISOR package can be used to interface with all
advisors, including the SQL Tuning Advisor.
20. On the Edit Thresholds screen in Oracle Enterprise Manager, what does it
mean if the Select button is disabled for a specific metric?
A. The metric is disabled on the current instance.
B. The metric is stale.
C. The metric does not support multiple thresholds.
D. The threshold is locked and cannot be modified.
E. None of the above.
---
Ans: C.
Multiple thresholds can be set only on aggregated metrics with underlying
granularity. For example, Blocking Session Count aggregates all underlying
blocking sessions. Therefore, you can set thresholds for individual sessions as well as
for the aggregate metric.
Review Questions
.
1. Which of the following is not a valid ADDM finding type?
A. Error
B. Problem
C. Information
D. Symptom
E. All are valid ADDM finding types.
-----
Ans: A.
All ADDM analysis results are categorized as Problem, Symptom, or Information
findings. Problem represents the root problem identified, Symptom identifies a
symptom resulting from the root problem, and Information reports on non-problem
areas. ADDM does not classify its findings as Errors.
2. What is the default setting for the STATISTICS_LEVEL initialization parameter?
A. BASIC
B. ALL
C. STANDARD
D. TYPICAL
E. None of the above
----
Ans: D.
The default setting for STATISTICS_LEVEL is TYPICAL, which ensures that
adequate statistics are gathered to support all of the automatic database management
features.
3. Which of the following would be used to set or change the value for DBIO_EXPECTED?
A. The DBIO_EXPECTED initialization parameter
B. The ALTER SYSTEM statement
C. The ALTER SESSION statement
D. All of the above will work.
E. None of the above will work.
----
Ans: E.
The value for DBIO_EXPECTED is set by using the DBMS_ADVISOR.SET_DEFAULT_TASK_PARAMETER procedure. It is not an initialization parameter value, so answers A, B, and C cannot be correct, because all of them deal with initialization parameters.
4. Which of the following are types of problems that the ADDM will consider?
(Choose all that apply.)
A. Database configuration issues
B. Concurrency issues
C. CPU bottlenecks
D. Suboptimal use of Oracle by an application
E. All of the above
---
Ans: E.
ADDM considers all of these problem areas and more when performing its analysis.
5. The statistical data needed for ADDM to accurately diagnose problems is
stored in which of the following areas?
A. Automatic Workload Repository (AWR)
B. Data dictionary
C. ADDM repository
D. PERFSTAT tables
E. None of the above
---
Ans: A.
ADDM, along with all the other advisors, utilize the AWR. The data dictionary
stores optimizer statistics, whereas ADDM deals with performance statistics, so
choice B is wrong. There is no such thing as an ADDM repository; therefore, choice
C is wrong. And PERFSTAT tables are a part of Statspack, which is no longer used
in Oracle 10g.
6. Which of the following is not a valid DBMS_ADVISOR procedure or function?
A. CREATE_TASK_SCRIPT
B. RESTART_TASK
C. INTERRUPT_TASK
D. RESUME_TASK
E. None of the above
---
ANs: B.
There is no RESTART_TASK procedure or function. Choices A, C, and D all
represent valid procedures. E is obviously wrong.
7. In an RAC environment, which element(s) always uniquely identifies a snapshot?
A. INSTANCE_ID
B. CLUSTER_ID
C. SNAP_ID
D. Both A and C
E. A, B, and C
---
Ans: D.
Both A and C are required to uniquely identify snapshots across Real Application
Clusters (RACs). In a stand-alone environment, the SNAP_ID is guaranteed to be
unique. However, with RAC, a snapshot shares the same SNAP_ID across all
instances, so the INSTANCE_ID is used to differentiate between them.
8. If the ASH buffer is filled in less than 30 minutes, which process is responsible for flushing it?
A. SMON
B. MMON
C. MMNL
D. PMON
E. AMON
---
Ans: C.
While MMON is normally tasked with flushing the ASH buffer every 30 minutes,
the MMNL process performs the task if the buffer fills before that time. SMON and
PMON don't have any interaction with the ASH flush, and AMON is not a valid
Oracle process name.
9. Which are valid SCOPE settings for the SQL Tuning Advisor? (Choose all that apply.)
A. Full
B. Comprehensive
C. Detailed
D. Basic
E. Limited
---
Ans: B, E.
SQL Tuning Advisor can perform either Comprehensive or Limited analysis,
based on the SCOPE setting. Answers A, C, and D are all invalid settings for the
SCOPE parameter.
10. If a metrics threshold is defined with an OBSERVATION_PERIOD setting of 5 and
a CONSECUTIVE_OCCURRENCES setting of 2, an alert would be generated after
how many minutes of consecutive violations?
A. 10
B. 15
C. 20
D. 5
E. None of the above
---
ANs: B.
The CONSECUTIVE_OCCURRENCES parameter defines the number of allowable
violations without an alert. This number must be exceeded before an alert is
generated. Because the values are checked every five minutes (as defined by the
OBSERVATION_PERIOD parameter), an alert would be generated after 15 minutes.
11. Which of the following statements regarding the Automatic Tuning Optimizer (ATO) is incorrect?
A. The ATO generates more efficient execution plans than the query optimizer in normal mode.
B. The ATO is nothing more than the query optimizer in tuning mode.
C. The ATO can take a long time to process a SQL statement, especially when performing a comprehensive analysis.
D. The ATO does generate a SQL Profile when performing a limited analysis.
E. All the above statements are correct.
---
Ans: A.
The Automatic Tuning Optimizer (ATO) does not generate execution plans at all.
Instead, it will return recommendations for optimization, along with their expected
benefit and rationale.
12. The AWR resides in which tablespace?
A. SYSTEM
B. TOOLS
C. SYSAUX
D. AWR
E. None of the above
---
Ans: C.
The AWR is the primary occupant of the SYSAUX tablespace.
13. Which DBMS_STATS function is used to allow Oracle to dynamically choose an
appropriate parallel degree based on the size of the object and the values of
certain initialization parameters?
A. AUTO
B. CHOOSE
C. DETECT
D. AUTO_DEGREE
E. BEST_DEGREE
----
Ans: D.
The AUTO_DEGREE function chooses the most appropriate degree of parallelism
based on factors such as object size and init.ora settings.
14. Which Oracle process updates the data dictionary with DML activity
information gathered by the Automatic DML Monitoring feature?
A. SMON
B. PMON
C. MMNL
D. DMON
E. MMON
---
Ans: A.
The SMON process updates the data dictionary with DML activity information
approximately every three hours, as well as at instance shutdown.
15. Which of the following views can be queried to display advisor findings?
A. V$ADVISOR_FINDINGS
B. DBA_ADV_FINDINGS
C. DBA_ADV_RECOMMENDATIONS
D. DBA_ADVISOR_FINDINGS
E. Findings can be viewed only through Enterprise Manager.
----
Ans: D.
The DBA_ADVISOR_FINDINGS view shows the findings from any advisor
execution. Though they can also be viewed through Enterprise Manager, answer E is incorrect.
16. Which view shows the current AWR settings?
A. DBA_AWR_SETTINGS
B. DBA_AWR_CONFIG
C. DBA_HIST_WR_CONTROL
D. DBA_HIST_WR_SETTINGS
E. DBA_REPOSITORY
---
Ans: C.
The DBA_HIST_WR_CONTROL view shows information about the AWR, including
the current settings.
17. On an eight-CPU system with a Shared Pool size of 1GB, what size would the
ASH buffer be set to?
A. 8MB
B. 16MB
C. 50MB
D. 32MB
E. 32KB
---
Ans: B. The ASH buffer will be set to the lesser of:
(Total number of CPUs × 2MB)
or
(Shared Pool size × 5%)
8 CPUs × 2MB = 16MB
1GB × 5% = 50MB Therefore, the ASH will be sized at 16MB.
18. _______________ is a hash value that uniquely identifies a SQL statement in
the database.
A. SQL_HASH
B. SQL_HASH_ID
C. SQL_ID
D. SID
E. SPID
---
Ans: C.
SQL_ID, new in Oracle 10g, is a hash value that uniquely identifies a single SQL
statement within a database.
19. Which package(s) can be used to interface with the SQL Tuning Advisor?
(Choose all that apply.)
A. DBMS_ADVISOR
B. DBMS_SQL_TUNE
C. DBMS_SQLTUNE
D. DBMS_SQL_ADVISOR
E. DBMS_SQL_TUNE_ADV
---
Ans: A, C.
DBMS_SQLTUNE is designed specifically as the interface to the SQL Tuning
Advisor. However, the DBMS_ADVISOR package can be used to interface with all
advisors, including the SQL Tuning Advisor.
20. On the Edit Thresholds screen in Oracle Enterprise Manager, what does it
mean if the Select button is disabled for a specific metric?
A. The metric is disabled on the current instance.
B. The metric is stale.
C. The metric does not support multiple thresholds.
D. The threshold is locked and cannot be modified.
E. None of the above.
---
Ans: C.
Multiple thresholds can be set only on aggregated metrics with underlying
granularity. For example, Blocking Session Count aggregates all underlying
blocking sessions. Therefore, you can set thresholds for individual sessions as well as
for the aggregate metric.
OCP Oracle 10g Admin II - Ch 9 : Understanding Automatic Storage Management
Chapter 9 - Understanding Automatic Storage Management
Review Questions
1. Extents in an ASM file are allocated in units of which size?
A. 100KB
B. 10MB
C. 1MB
D. 64KB
----
Ans: C.
ASM disks are partitioned in allocation units of one megabyte each.
2. To prevent connections to an ASM instance, you can use which of the
following commands? (Choose the best answer.)
A. ALTER SYSTEM ENABLE RESTRICTED SESSION
B. SHUTDOWN IMMEDIATE
C. ALTER SYSTEM DISABLE CONNECTIONS
D. ALTER DATABASE ENABLE RESTRICTED SESSION
----
Ans: A.
Similar to an RDBMS instance, you can use ALTER SYSTEM ENABLE RESTRICTED SESSION to prevent connections to the instance. While SHUTDOWN IMMEDIATE will prevent connections to the ASM instance, this is most likely overkill if all you want to do is temporarily prevent connections. Choices C and D are not valid commands and will generate an error message.
3. Which initialization parameter in an ASM instance specifies the disk groups to
be automatically mounted at instance startup?
A. ASM_DISKMOUNT
B. ASM_DISKGROUP
C. ASM_DISKSTRING
D. ASM_MOUNTGROUP
----
Ans: B.
The initialization parameter ASM_DISKGROUP, valid only in an ASM instance, specifies the disk groups to be automatically mounted when the ASM instance starts. ASM_DISKSTRING is operating system dependent and restricts the file system devices
that can be used to create disk groups. ASM_DISKMOUNT and ASM_MOUNTGROUP are not valid initialization parameters.
4. Which of the following command options is not valid for an ASM instance?
A. STARTUP OPEN
B. STARTUP NOMOUNT
C. STARTUP MOUNT
D. STARTUP OPEN RESTRICT
E. SHUTDOWN ABORT
---
Ans: A.
An ASM instance can be started up and shut down in the same way that an RDBMS database can, except that an ASM instance cannot be in the OPEN state because it does not have a data dictionary or a control file.
5. When an ASM instance receives a SHUTDOWN NORMAL command, what
command does it pass on to all database instances that rely on the ASM
instance's disk groups?
A. TRANSACTIONAL
B. IMMEDIATE
C. ABORT
D. NORMAL
E. None of the above
---
Ans: D.
When an ASM instance receives a SHUTDOWN command, it passes the same option
(NORMAL, IMMEDIATE or TRANSACTIONAL) to all database instances that rely on the
ASM instance for disk group services.
6. When creating a disk group, what keyword must be specified if you need to
reuse a disk that has previously been used as part of another disk group?
A. NOFORCE
B. REUSE
C. USE
D. FORCE
E. INCLUDING CONTENTS
---
Ans: D.
You must use FORCE if the disk has previously been used as part of a disk group. If
the disk has never been used as part of a disk group, using the FORCE keyword returns
an error.
7. Which of the following ASM file templates is not striped as fine?
A. FLASHBACK
B. ARCHIVELOG
C. CONTROLFILE
D. ONLINELOG
----
Ans: B.
Files such as ARCHIVELOG files use coarse-grained striping. Fine striping stripes
the files every 128KB while coarse striping stripes the files every 1MB. All file types
with the exception of FLASHBACK, CONTROLFILE, and ONLINELOG are striped coarse.
8. You want to migrate your database to ASM, so you've done a clean shutdown,
made a closed backup of the entire database, noted the location of your control
files and online redo log files, and changed your SPFILE to use OMF. The last
step is running an RMAN script to do the conversion. Using the following
steps, what is the correct order in which the following RMAN commands
should be executed?
1. STARTUP NOMOUNT
2. ALTER DATABASE OPEN RESETLOGS
3. SQL "ALTER DATABASE RENAME ‘logfile1 path’ TO ‘+dgrp4 ‘"
# plus all other log files
4. SWITCH DATABASE TO COPY
5. BACKUP AS COPY DATABASE FORMAT ‘+dgrp4’
6. ALTER DATABASE MOUNT
7. RESTORE CONTROLFILE FROM ‘controlfile_location’
A. 2, 5, 3, 1, 7, 6, 4
B. 1, 7, 6, 5, 4, 3, 2
C. 5, 1, 2, 7, 4, 6, 3
D. 7, 3, 1, 5, 6, 2, 4
---
Ans: B.
After the RMAN script is run and the database is up and running successfully, you
may delete the old database files.
9. How can you reverse the effects of an ALTER DISKGROUP ... DROP DISK
command if it has not yet completed?
A. Issue the ALTER DISKGROUP ... ADD DISK command.
B. Issue the ALTER DISKGROUP ... UNDROP DISKS command.
C. Issue the ALTER DISKGROUP ... DROP DISK CANCEL command.
D. Retrieve the disk from the recycle bin after the operation completes.
--
Ans: B.
If the DROP DISK operation has not yet completed, you can cancel and roll back
the entire DROP DISK operation by using ALTER DISKGROUP ... UNDROP DISKS, with
the disk group still being continuously available to all users.
10. To reference existing ASM files, you need to use a fully qualified ASM
filename. Your development database has a disk group named DG2A, the
database name is DEV19, and the ASM file that you want to reference is a
datafile for the USERS02 tablespace. Which of the following is a valid ASM
filename for this ASM file?
A. dev19/+DG2A/datafile/users02.701.2
B. +DG2A/dev19/datafile/users02.701.2
C. +DG2A/dev19/users02/datafile.701.2
D. +DG2A.701.2
E. +DG2A/datafile/dev19.users.02.701.2
---
Ans: B.
A fully qualified existing ASM filename has the format
+group/dbname/filetype/tag.file.incarnation. In this case, filetype is
datafile, and tag is the tablespace name to which it belongs, or users02.
11. Which background process coordinates the rebalance activity for disk groups?
A. ORBn
B. OSMB
C. RBAL
D. ASMn
----
Ans: C.
RBAL coordinates rebalance activity for a disk group in an ASM instance, ORBn
actually performs the extent movement in an ASM instance, and OSMB acts as a
bridge between the ASM instance and the RDBMS instance. There is no such process
name ASMn.
12. On the development database rac0 there are six raw devices: /dev/raw/raw1
through /dev/raw/raw6. /dev/raw/raw1 and /dev/raw/raw2 are 8GB each,
and the rest are 6GB each. An existing disk group +DATA1, of NORMAL
REDUNDANCY, uses /dev/raw/raw1 and /dev/raw/raw2. Which series of the
following commands will drop one of the failure groups for +DATA1, create a
new disk group +DATA2 using two of the remaining four raw devices, and then
cancel the drop operation from +DATA1?
A. ALTER DISKGROUP DATA1 DROP DISK DATA1_0001;
CREATE DISKGROUP DATA2 NORMAL REDUNDANCY
FAILGROUP DATA1A DISK '/dev/raw/raw3'
FAILGROUP DATA1B DISK '/dev/raw/raw4';
ALTER DISKGROUP DATA1 UNDROP DISKS;
B. ALTER DISKGROUP DATA1 DROP DISK DATA1_0001;
CREATE DISKGROUP DATA2 HIGH REDUNDANCY
FAILGROUP DATA1A DISK '/dev/raw/raw3'
FAILGROUP DATA1B DISK '/dev/raw/raw4'
ALTER DISKGROUP DATA1 UNDROP DISKS;
C. ALTER DISKGROUP DATA1 DROP DISK DATA1_0001;
CREATE DISKGROUP DATA2 NORMAL REDUNDANCY
FAILGROUP DATA1A DISK '/dev/raw/raw3'
FAILGROUP DATA1B DISK '/dev/raw/raw4';
ALTER DISKGROUP DATA1 UNDROP DATA1_0001;
D. ALTER DISKGROUP DATA1 DROP DISK DATA1_0001
ADD DISKGROUP DATA2 NORMAL REDUNDANCY
FAILGROUP DATA1A DISK '/dev/raw/raw3'
FAILGROUP DATA1B DISK '/dev/raw/raw4';
ALTER DISKGROUP DATA1 UNDROP DISKS;
-----
Ans: A.
Note that the UNDROP operation will cancel a drop operation in progress but cannot
reverse a drop operation that has already completed. For HIGH REDUNDANCY, at least
three failure groups must be specified. While you can combine a drop and add
operation into one command, the command can reference only one disk group.
13. Which type of database file is spread across all disks in a disk group?
A. All types of files are spread across all disks in the disk group.
B. Datafiles
C. Redo log files
D. Archived redo log files
E. Control files
---
Ans: A.
All types of database files are spread across all disks in the disk group to ensure
redundancy unless the redundancy is set to EXTERNAL.
14. How can you reverse the effects of an ALTER DISKGROUP ... DROP DISK
command if it has already completed?
A. Issue the ALTER DISKGROUP ... ADD DISK command.
B. Issue the ALTER DISKGROUP ... UNDROP DISKS command.
C. Issue the ALTER DISKGROUP ... DROP DISK CANCEL command.
D. Retrieve the disk from the recycle bin after the operation completes.
---
Ans: A. If the DROP DISK operation has already completed, you must use ALTER
DISKGROUP ... ADD DISK to add the disk back to the disk group. In any case, the disk
group is continuously available to all users.
15. Which of the following ALTER DISKGROUP commands does not use
V$ASM_OPERATION to record the status of the operation?
A. ADD DIRECTORY
B. DROP DISK
C. RESIZE DISK
D. REBALANCE
E. ADD FAILGROUP
----
Ans: A.
The ADD DIRECTORY command does not use V$ASM_OPERATION to track its
progress, because this operation adds only a small amount of metadata - a directory
object - to the disk group and takes a minimal amount of time to complete.
16. If you use ALTER DISKGROUP ... ADD DISK and specify a wildcard for the
discovery string, what happens to disks that are already a part of the same or
another disk group?
A. The command fails unless you specify the FORCE option.
B. The command fails unless you specify the REUSE option.
C. The command must be reissued with a more specific discovery string.
D. The other disks already part of the disk group are ignored.
---
Ans: D.
The ALTER DISKGROUP ... ADD DISK command adds all disks that match the
discovery string but are not already part of the same or another disk group.
17. Choose the set of the following initialization parameters that is valid and
recommended for an ASM instance.
A. INSTANCE_TYPE=RDBMS
ASM_POWER_LIMIT=2
LARGE_POOL_SIZE=8MB
DB_UNIQUE_NAME=+ASM
ASM_DISKGROUPS=DATA1,DATA2
B. INSTANCE_TYPE=ASM
ASM_POWER_LIMIT=2
LARGE_POOL_SIZE=8MB
DB_UNIQUE_NAME=+ASM
ASM_DISKGROUPS=DATA1,DATA2
C. INSTANCE_TYPE=ASM
ASM_POWER_LIMIT=15
LARGE_POOL_SIZE=8MB
DB_UNIQUE_NAME=+ASM
ASM_DISKGROUPS=DATA1,DATA2
D. INSTANCE_TYPE=ASM
ASM_POWER_LIMIT=2
LARGE_POOL_SIZE=4MB
DB_UNIQUE_NAME=+ASM
ASM_DISKGROUPS=DATA1,DATA2
---
Ans: B.
The INSTANCE_TYPE for an ASM instance is ASM; otherwise, it is RDBMS, whether it
uses ASM or not. The ASM_POWER_LIMIT command controls the speed of a disk
group rebalance, but its maximum value is 11. For an ASM instance, the minimum
recommended value for LARGE_POOL_SIZE is 8MB.
18. Which of the following scenarios concerning ASM instance shutdown is correct?
A. When an ASM instance is shut down with NORMAL, IMMEDIATE, or
TRANSACTIONAL, the same shutdown command is passed to the
dependent instances and the ASM instance waits for all dependent
instances to shut down before it shuts down.
B. When an ASM instance shuts down with NORMAL, an alert is sent to all
dependent instances, notifying the DBA to shut down the dependent
instances manually before the ASM instance shuts down.
C. When an ASM instance shuts down with the TRANSACTIONAL option,
all dependent instances shut down with NORMAL, IMMEDIATE, or
TRANSACTIONAL, depending on the dependent database's default.
D. When an ASM instance is shut down with NORMAL, IMMEDIATE, or
TRANSACTIONAL, the same shutdown command is passed to the
dependent instances and the ASM instance does not wait for all
dependent instances to shut down before it shuts down.
E. When an ASM instance shuts down with the IMMEDIATE option, the
ASM instance shuts down immediately and all dependent instances
shut down with ABORT.
---
Ans: A.
When an ASM instance shuts down with NORMAL, IMMEDIATE, or TRANSACTIONAL,
the same shutdown option is passed to all dependent instances and the ASM instance
waits for the dependent instances to shut down before shutting itself down. If an
ASM instance shuts down with ABORT, it immediately shuts down, the dependent
instances lose their connection to the ASM instance, and as a result, they shut down
with ABORT either before or after the ASM instance shuts down completely.
19. A database can create datafiles in how many different disk groups? (Choose
the best answer.)
A. Each datafile in the database can reside in a different disk group.
B. One
C. Disk groups manage tablespaces, not datafiles.
D. A maximum of two, one for SYSTEM and SYSAUX and the other tablespaces in another disk group.
---
Ans: A.
Each database datafile can reside in a different disk group; each disk group can
also contain datafiles from other databases.
20. ASM supports all of the following file types except for which of the
following? (Choose all that apply.)
A. Database files
B. SPFILEs
C. Redo log files
D. Archived log files
E. RMAN backup sets
F. Password files
G. init.ora files
---
Ans: F, G.
ASM supports datafiles, log files, control files, archive logs, RMAN backup
sets, SPFILEs, and other Oracle database file types, but not password files or
init.ora files.
Review Questions
1. Extents in an ASM file are allocated in units of which size?
A. 100KB
B. 10MB
C. 1MB
D. 64KB
----
Ans: C.
ASM disks are partitioned in allocation units of one megabyte each.
2. To prevent connections to an ASM instance, you can use which of the
following commands? (Choose the best answer.)
A. ALTER SYSTEM ENABLE RESTRICTED SESSION
B. SHUTDOWN IMMEDIATE
C. ALTER SYSTEM DISABLE CONNECTIONS
D. ALTER DATABASE ENABLE RESTRICTED SESSION
----
Ans: A.
Similar to an RDBMS instance, you can use ALTER SYSTEM ENABLE RESTRICTED SESSION to prevent connections to the instance. While SHUTDOWN IMMEDIATE will prevent connections to the ASM instance, this is most likely overkill if all you want to do is temporarily prevent connections. Choices C and D are not valid commands and will generate an error message.
3. Which initialization parameter in an ASM instance specifies the disk groups to
be automatically mounted at instance startup?
A. ASM_DISKMOUNT
B. ASM_DISKGROUP
C. ASM_DISKSTRING
D. ASM_MOUNTGROUP
----
Ans: B.
The initialization parameter ASM_DISKGROUP, valid only in an ASM instance, specifies the disk groups to be automatically mounted when the ASM instance starts. ASM_DISKSTRING is operating system dependent and restricts the file system devices
that can be used to create disk groups. ASM_DISKMOUNT and ASM_MOUNTGROUP are not valid initialization parameters.
4. Which of the following command options is not valid for an ASM instance?
A. STARTUP OPEN
B. STARTUP NOMOUNT
C. STARTUP MOUNT
D. STARTUP OPEN RESTRICT
E. SHUTDOWN ABORT
---
Ans: A.
An ASM instance can be started up and shut down in the same way that an RDBMS database can, except that an ASM instance cannot be in the OPEN state because it does not have a data dictionary or a control file.
5. When an ASM instance receives a SHUTDOWN NORMAL command, what
command does it pass on to all database instances that rely on the ASM
instance's disk groups?
A. TRANSACTIONAL
B. IMMEDIATE
C. ABORT
D. NORMAL
E. None of the above
---
Ans: D.
When an ASM instance receives a SHUTDOWN command, it passes the same option
(NORMAL, IMMEDIATE or TRANSACTIONAL) to all database instances that rely on the
ASM instance for disk group services.
6. When creating a disk group, what keyword must be specified if you need to
reuse a disk that has previously been used as part of another disk group?
A. NOFORCE
B. REUSE
C. USE
D. FORCE
E. INCLUDING CONTENTS
---
Ans: D.
You must use FORCE if the disk has previously been used as part of a disk group. If
the disk has never been used as part of a disk group, using the FORCE keyword returns
an error.
7. Which of the following ASM file templates is not striped as fine?
A. FLASHBACK
B. ARCHIVELOG
C. CONTROLFILE
D. ONLINELOG
----
Ans: B.
Files such as ARCHIVELOG files use coarse-grained striping. Fine striping stripes
the files every 128KB while coarse striping stripes the files every 1MB. All file types
with the exception of FLASHBACK, CONTROLFILE, and ONLINELOG are striped coarse.
8. You want to migrate your database to ASM, so you've done a clean shutdown,
made a closed backup of the entire database, noted the location of your control
files and online redo log files, and changed your SPFILE to use OMF. The last
step is running an RMAN script to do the conversion. Using the following
steps, what is the correct order in which the following RMAN commands
should be executed?
1. STARTUP NOMOUNT
2. ALTER DATABASE OPEN RESETLOGS
3. SQL "ALTER DATABASE RENAME ‘logfile1 path’ TO ‘+dgrp4 ‘"
# plus all other log files
4. SWITCH DATABASE TO COPY
5. BACKUP AS COPY DATABASE FORMAT ‘+dgrp4’
6. ALTER DATABASE MOUNT
7. RESTORE CONTROLFILE FROM ‘controlfile_location’
A. 2, 5, 3, 1, 7, 6, 4
B. 1, 7, 6, 5, 4, 3, 2
C. 5, 1, 2, 7, 4, 6, 3
D. 7, 3, 1, 5, 6, 2, 4
---
Ans: B.
After the RMAN script is run and the database is up and running successfully, you
may delete the old database files.
9. How can you reverse the effects of an ALTER DISKGROUP ... DROP DISK
command if it has not yet completed?
A. Issue the ALTER DISKGROUP ... ADD DISK command.
B. Issue the ALTER DISKGROUP ... UNDROP DISKS command.
C. Issue the ALTER DISKGROUP ... DROP DISK CANCEL command.
D. Retrieve the disk from the recycle bin after the operation completes.
--
Ans: B.
If the DROP DISK operation has not yet completed, you can cancel and roll back
the entire DROP DISK operation by using ALTER DISKGROUP ... UNDROP DISKS, with
the disk group still being continuously available to all users.
10. To reference existing ASM files, you need to use a fully qualified ASM
filename. Your development database has a disk group named DG2A, the
database name is DEV19, and the ASM file that you want to reference is a
datafile for the USERS02 tablespace. Which of the following is a valid ASM
filename for this ASM file?
A. dev19/+DG2A/datafile/users02.701.2
B. +DG2A/dev19/datafile/users02.701.2
C. +DG2A/dev19/users02/datafile.701.2
D. +DG2A.701.2
E. +DG2A/datafile/dev19.users.02.701.2
---
Ans: B.
A fully qualified existing ASM filename has the format
+group/dbname/filetype/tag.file.incarnation. In this case, filetype is
datafile, and tag is the tablespace name to which it belongs, or users02.
11. Which background process coordinates the rebalance activity for disk groups?
A. ORBn
B. OSMB
C. RBAL
D. ASMn
----
Ans: C.
RBAL coordinates rebalance activity for a disk group in an ASM instance, ORBn
actually performs the extent movement in an ASM instance, and OSMB acts as a
bridge between the ASM instance and the RDBMS instance. There is no such process
name ASMn.
12. On the development database rac0 there are six raw devices: /dev/raw/raw1
through /dev/raw/raw6. /dev/raw/raw1 and /dev/raw/raw2 are 8GB each,
and the rest are 6GB each. An existing disk group +DATA1, of NORMAL
REDUNDANCY, uses /dev/raw/raw1 and /dev/raw/raw2. Which series of the
following commands will drop one of the failure groups for +DATA1, create a
new disk group +DATA2 using two of the remaining four raw devices, and then
cancel the drop operation from +DATA1?
A. ALTER DISKGROUP DATA1 DROP DISK DATA1_0001;
CREATE DISKGROUP DATA2 NORMAL REDUNDANCY
FAILGROUP DATA1A DISK '/dev/raw/raw3'
FAILGROUP DATA1B DISK '/dev/raw/raw4';
ALTER DISKGROUP DATA1 UNDROP DISKS;
B. ALTER DISKGROUP DATA1 DROP DISK DATA1_0001;
CREATE DISKGROUP DATA2 HIGH REDUNDANCY
FAILGROUP DATA1A DISK '/dev/raw/raw3'
FAILGROUP DATA1B DISK '/dev/raw/raw4'
ALTER DISKGROUP DATA1 UNDROP DISKS;
C. ALTER DISKGROUP DATA1 DROP DISK DATA1_0001;
CREATE DISKGROUP DATA2 NORMAL REDUNDANCY
FAILGROUP DATA1A DISK '/dev/raw/raw3'
FAILGROUP DATA1B DISK '/dev/raw/raw4';
ALTER DISKGROUP DATA1 UNDROP DATA1_0001;
D. ALTER DISKGROUP DATA1 DROP DISK DATA1_0001
ADD DISKGROUP DATA2 NORMAL REDUNDANCY
FAILGROUP DATA1A DISK '/dev/raw/raw3'
FAILGROUP DATA1B DISK '/dev/raw/raw4';
ALTER DISKGROUP DATA1 UNDROP DISKS;
-----
Ans: A.
Note that the UNDROP operation will cancel a drop operation in progress but cannot
reverse a drop operation that has already completed. For HIGH REDUNDANCY, at least
three failure groups must be specified. While you can combine a drop and add
operation into one command, the command can reference only one disk group.
13. Which type of database file is spread across all disks in a disk group?
A. All types of files are spread across all disks in the disk group.
B. Datafiles
C. Redo log files
D. Archived redo log files
E. Control files
---
Ans: A.
All types of database files are spread across all disks in the disk group to ensure
redundancy unless the redundancy is set to EXTERNAL.
14. How can you reverse the effects of an ALTER DISKGROUP ... DROP DISK
command if it has already completed?
A. Issue the ALTER DISKGROUP ... ADD DISK command.
B. Issue the ALTER DISKGROUP ... UNDROP DISKS command.
C. Issue the ALTER DISKGROUP ... DROP DISK CANCEL command.
D. Retrieve the disk from the recycle bin after the operation completes.
---
Ans: A. If the DROP DISK operation has already completed, you must use ALTER
DISKGROUP ... ADD DISK to add the disk back to the disk group. In any case, the disk
group is continuously available to all users.
15. Which of the following ALTER DISKGROUP commands does not use
V$ASM_OPERATION to record the status of the operation?
A. ADD DIRECTORY
B. DROP DISK
C. RESIZE DISK
D. REBALANCE
E. ADD FAILGROUP
----
Ans: A.
The ADD DIRECTORY command does not use V$ASM_OPERATION to track its
progress, because this operation adds only a small amount of metadata - a directory
object - to the disk group and takes a minimal amount of time to complete.
16. If you use ALTER DISKGROUP ... ADD DISK and specify a wildcard for the
discovery string, what happens to disks that are already a part of the same or
another disk group?
A. The command fails unless you specify the FORCE option.
B. The command fails unless you specify the REUSE option.
C. The command must be reissued with a more specific discovery string.
D. The other disks already part of the disk group are ignored.
---
Ans: D.
The ALTER DISKGROUP ... ADD DISK command adds all disks that match the
discovery string but are not already part of the same or another disk group.
17. Choose the set of the following initialization parameters that is valid and
recommended for an ASM instance.
A. INSTANCE_TYPE=RDBMS
ASM_POWER_LIMIT=2
LARGE_POOL_SIZE=8MB
DB_UNIQUE_NAME=+ASM
ASM_DISKGROUPS=DATA1,DATA2
B. INSTANCE_TYPE=ASM
ASM_POWER_LIMIT=2
LARGE_POOL_SIZE=8MB
DB_UNIQUE_NAME=+ASM
ASM_DISKGROUPS=DATA1,DATA2
C. INSTANCE_TYPE=ASM
ASM_POWER_LIMIT=15
LARGE_POOL_SIZE=8MB
DB_UNIQUE_NAME=+ASM
ASM_DISKGROUPS=DATA1,DATA2
D. INSTANCE_TYPE=ASM
ASM_POWER_LIMIT=2
LARGE_POOL_SIZE=4MB
DB_UNIQUE_NAME=+ASM
ASM_DISKGROUPS=DATA1,DATA2
---
Ans: B.
The INSTANCE_TYPE for an ASM instance is ASM; otherwise, it is RDBMS, whether it
uses ASM or not. The ASM_POWER_LIMIT command controls the speed of a disk
group rebalance, but its maximum value is 11. For an ASM instance, the minimum
recommended value for LARGE_POOL_SIZE is 8MB.
18. Which of the following scenarios concerning ASM instance shutdown is correct?
A. When an ASM instance is shut down with NORMAL, IMMEDIATE, or
TRANSACTIONAL, the same shutdown command is passed to the
dependent instances and the ASM instance waits for all dependent
instances to shut down before it shuts down.
B. When an ASM instance shuts down with NORMAL, an alert is sent to all
dependent instances, notifying the DBA to shut down the dependent
instances manually before the ASM instance shuts down.
C. When an ASM instance shuts down with the TRANSACTIONAL option,
all dependent instances shut down with NORMAL, IMMEDIATE, or
TRANSACTIONAL, depending on the dependent database's default.
D. When an ASM instance is shut down with NORMAL, IMMEDIATE, or
TRANSACTIONAL, the same shutdown command is passed to the
dependent instances and the ASM instance does not wait for all
dependent instances to shut down before it shuts down.
E. When an ASM instance shuts down with the IMMEDIATE option, the
ASM instance shuts down immediately and all dependent instances
shut down with ABORT.
---
Ans: A.
When an ASM instance shuts down with NORMAL, IMMEDIATE, or TRANSACTIONAL,
the same shutdown option is passed to all dependent instances and the ASM instance
waits for the dependent instances to shut down before shutting itself down. If an
ASM instance shuts down with ABORT, it immediately shuts down, the dependent
instances lose their connection to the ASM instance, and as a result, they shut down
with ABORT either before or after the ASM instance shuts down completely.
19. A database can create datafiles in how many different disk groups? (Choose
the best answer.)
A. Each datafile in the database can reside in a different disk group.
B. One
C. Disk groups manage tablespaces, not datafiles.
D. A maximum of two, one for SYSTEM and SYSAUX and the other tablespaces in another disk group.
---
Ans: A.
Each database datafile can reside in a different disk group; each disk group can
also contain datafiles from other databases.
20. ASM supports all of the following file types except for which of the
following? (Choose all that apply.)
A. Database files
B. SPFILEs
C. Redo log files
D. Archived log files
E. RMAN backup sets
F. Password files
G. init.ora files
---
Ans: F, G.
ASM supports datafiles, log files, control files, archive logs, RMAN backup
sets, SPFILEs, and other Oracle database file types, but not password files or
init.ora files.
Monday, October 01, 2007
OCP Oracle 10g Admin II - Ch13: Monitoring and Managing Storage
Monitoring and Managing Storage
Review Questions
1. Which data dictionary view provides the recommended action, as a SQL statement, from the Segment Advisor?
A. DBA_ADVISOR_FINDINGS
B. DBA_ADVISOR_RECOMMENDATIONS
C. DBA_ADVISOR_ACTIONS
D. DBA_ADVISOR_RATIONALE
----
Ans: C.
The data dictionary view DBA_ADVISOR_ACTIONS contains the SQL statement(s) that the Segment Advisor supplies to implement its recommendation for segment maintenance. DBA_ADVISOR_FINDINGS contains the results of the analysis, but no SQL. DBA_ADVISOR_RECOMMENDATIONS presents one or more findings and the benefits for performing the recommendation. DBA_ADVISOR_RATIONALE provides a more detailed set of reasons why the recommendation should be implemented, along with the impact of not performing the recommendation.
2. Which of the following statements is not true about sorted hash clusters?
A. The new access path is used regardless of the type of predicate in the WHERE clause.
B. You are allowed to create indexes on sorted hash clusters.
C. The cost-based optimizer must be used to take advantage of the new access path.
D. Additional sorts are not necessary if you access the cluster by one of the lists of hash key columns.
E. More than one table can be stored in a sorted hash cluster.
---
Ans: A.
The new access path in a sorted hash cluster is used only if an equality predicate is used.
3. Consider the following scenario. The user SCOTT runs a query at 8:25 A.M.
that receives an ORA-01555: Snapshot too old error after running for 15
minutes. An alert is sent to the DBA that the undo tablespace is incorrectly
sized. At 10:15 A.M., the DBA checks the initialization parameter
UNDO_RETENTION, and its value is 3600; the parameter is sized correctly. The
DBA doubles the size of the undo tablespace by adding a second datafile. At
1:15 P.M., the user SCOTT runs the same query and once again receives an
ORA-01555: Snapshot too old error. What happens next? (Choose the best
answer.)
A. The DBA receives another alert indicating that the undo tablespace is
still undersized.
B. The user SCOTT calls the DBA to report that the query is still failing.
C. The second datafile autoextends so that future queries will have enough
undo space to complete when there is concurrent DML activity.
D. Resumable Space Allocation suspends the query until the DBA adds
another datafile to the undo tablespace, and then the query runs to completion.
---
Ans: B.
Even if the size of the undo tablespace is adjusted after an undo space problem,
only one alert is sent for each 24-hour period. Therefore, the only way that the
problem will be resolved promptly is for SCOTT to call the DBA, because the DBA
will not receive another alert until the next day when another query fails.
4. The background process __________ checks for tablespace threshold violation
or clearance every __________ minutes.
A. MMON, 10
B. SMON, 10
C. TMON, 30
D. PMON, 15
E. MMON, 30
---
Ans: A.
The new background process MMON checks for threshold violations every 10
minutes. An alert is triggered when the threshold is reached or is cleared.
5. Which of the following initialization parameters influences the recommended
redo log file size provided by the Redo Logfile Size Advisor?
A. LOG_CHECKPOINT_INTERVAL
B. OPTIMAL_LOGFILE_SIZE
C. FAST_START_IO_TARGET
D. FAST_START_MTTR_TARGET
E. None of the above
---
Ans: D.
FAST_START_MTTR_TARGET specifies the desired time, in seconds, for instance recovery after a crash or an instance failure. Therefore, the Redo Logfile Size
Advisor uses this value to determine the optimal log file size. OPTIMAL_LOGFILE_SIZE is not an initialization parameter but a column in the view V$INSTANCE_RECOVERY. The initialization parameter FAST_START_IO_TARGET specifies recovery at the I/O level, and LOG_CHECKPOINT_INTERVAL specifies the frequency of checkpoints in terms of redo log file blocks used.
6. Which of the following is not a benefit of segment shrink?
A. Full table scans will take less time.
B. Better index access takes place because of a smaller b-tree.
C. Space is freed up for other database objects.
D. All chained rows are fixed.
E. Space below the HWM is released, and the HWM is moved down.
----
Ans: D.
While some chained rows may be fixed with segment shrink functionality, it is not guaranteed that all chained rows will be fixed, because not all blocks may be read in
a segment shrink operation.
7. The EM Database Control Segment Resource Estimation feature uses all the following characteristics of the proposed table except for which one?
A. Column datatypes
B. PCTUSED
C. PCTFREE
D. Column sizes
E. Estimated number of rows
---
Ans: B.
Only PCTFREE is used in the calculation, because it is the amount of space to leave
free in the block for updates to existing rows. PCTUSED is not needed unless the
segment space management is not AUTO. In addition, extent sizes calculated by this
feature help assess the impact on the tablespace where this segment will be stored.
8. Which of the following is not a benefit of sorted hash clusters? (Choose the best answer.)
A. Rows within a given cluster key value are sorted by the sort key(s).
B. The ORDER BY clause is not required to retrieve rows in ascending or
descending order of the sort key(s).
C. Cluster key values are hashed.
D. Rows selected by a cluster key value using an equality operator are
returned in ascending or descending order.
---
Ans: C.
While cluster key values in a sorted hash cluster are hashed, this is also true of regular hash clusters, and therefore is not a benefit unique to sorted hash clusters.
9. In the following scenario, the DBA wants to reclaim a lot of wasted space in
the HR.EMPLOYEES table by using the segment shrink functionality. Which of
the following is the correct order of the steps?
1. ALTER TABLE HR.EMPLOYEES SHRINK SPACE;
2. ALTER TABLE HR.EMPLOYEES DISABLE ROW MOVEMENT;
3. ALTER TABLE HR.EMPLOYEES ENABLE ROW MOVEMENT;
4. ALTER TABLE HR.EMPLOYEES SHRINK SPACE COMPACT;
5. ALTER TABLE HR.EMPLOYEES SHRINK SPACE CASCADE;
A. 3, 4, 1, 5, 2
B. 4, 1, 3, 2, 5
C. 5, 2, 1, 3, 4
D. 4, 1, 2, 3, 5
---
Ans: A.
While the segment shrink operation could combine steps 1 and 4, the impact to the users will most likely be lessened by performing two smaller operations instead of one by specifying SHRINK SPACE COMPACT before specifying SHRINK SPACE in a
subsequent operation.
10. Which of the following calls to DBMS_SERVER_ALERT.SET_THRESHOLD will set the thresholds for the UNDOTBS1 tablespace to 60 percent and 90 percent?
(Choose the best answer.)
A)
dbms_server_alert.set_threshold(
dbms_server_alert.tablespace_pct_full,
dbms_server_alert.operator_ge, 60,
dbms_server_alert.operator_ge, 90,
1, 1, null,
dbms_server_alert.object_type_tablespace,
null);
B)
dbms_server_alert.set_threshold(
dbms_server_alert.tablespace_pct_full,
dbms_server_alert.operator_le, 60,
dbms_server_alert.operator_le, 90,
1, 1, null,
dbms_server_alert.object_type_datafile,
'UNDOTBS1');
C)
dbms_server_alert.set_threshold(
dbms_server_alert.tablespace_full,
dbms_server_alert.operator_ge, 60,
dbms_server_alert.operator_ge, 90,
1, 1, null,
dbms_server_alert.object_type_tablespace,
'UNDOTBS1');
D)
dbms_server_alert.set_threshold(
dbms_server_alert.tablespace_pct_full,
dbms_server_alert.operator_ge, 60,
dbms_server_alert.operator_ge, 90,
1, 1, null,
dbms_server_alert.object_type_tablespace,
'UNDOTBS1');
---
Ans: D.
The call to DBMS_SERVER_ALERT.SET_THRESHOLD must specify the metric
TABLESPACE_PCT_FULL, the two thresholds, an object type of tablespace, and the
tablespace name itself. Specifying NULL for the tablespace name will set the threshold
for all tablespaces, not just the UNDOTBS1 tablespace.
11. Which of the following statements is not true about segment shrink operations?
(Choose the best answer.)
A. Tables with ROWID-based materialized views are maintained.
B. Segment shrink is allowed only on segments whose space is automatically managed.
C. Heap-organized and index-organized tables can be shrunk.
D. ROW MOVEMENT must be enabled for heap-organized segments.
E. Chained rows may be repaired during a segment shrink operation.
F. Triggers are not fired during a segment shrink operation.
----
Ans: A.
Because the ROWIDs are changed with a segment shrink operation, tables with
ROWID- based materialized views cannot be shrunk unless the materialized views
are dropped and re-created after the segment shrink operation.
12. Which of the following is not a feature of the Segment Advisor within EM
Database Control?
A. Growth trend analysis
B. Segment Resource Estimation
C. Finding candidates for segment shrink
D. Finding table segments with chained rows
----
Ans: D.
The Segment Advisor is not used to find tables with chained rows, but instead is
used for finding segments that are good candidates for segment shrink or may be growing too fast.
13. Which of the following conditions will trigger an additional sort on a sorted
hash cluster? (Choose two.)
A. The ORDER BY clause specifies non-sort columns that are not indexed.
B. An ORDER BY clause is used in the query, although the sort may still fit in memory.
C. The cost-based optimizer is in effect.
D. The ORDER BY clause is omitted, and the WHERE clause does not reference the cluster key.
E. The ORDER BY clause specifies trailing sort columns.
---
Ans: A, E.
If a query on a sorted hash cluster retrieves rows and an ORDER BY clause
specifies either non-sort columns or a suffix of the sort columns, additional sorting is
required, assuming that indexes are not defined on the columns in the ORDER BY
clause.
14. Which of the following statements is not true about segment shrink operations
in tablespaces with automatic segment space management?
A. Clustered tables cannot be shrunk.
B. LOB segments can be shrunk.
C. IOT mapping tables and overflow segments cannot be shrunk.
D. Tables with function-based indexes cannot be shrunk.
E. ROW MOVEMENT must be enabled for heap-based segments.
---
Ans: B.
For segments in tablespaces with automatic segment space management, LOB
segments cannot be shrunk. In addition, tables with LONG columns, on-commit
materialized views, and ROWID-based materialized view cannot be shrunk. In all
cases, shrink operations cannot be performed on segments managed by freelists.
15. Which of the following is a disadvantage of rebuilding an index instead of
coalescing an index?
A. You temporarily need twice as much disk space to rebuild the index.
B. You cannot move the index to another tablespace.
C. The storage characteristics of the index cannot be changed.
D. The rebuild operation only coalesces index leaf blocks within each
branch instead of re-creating the entire tree.
----
Ans: A.
Whether you rebuild the index offline or online, you temporarily need twice as
much disk space. If you rebuild the index online, you also need disk space to support
a journal table to hold the intermediate changes to the index while the index is being
rebuilt.
16. Which of the following commands adds a member /logs/redo22.log to redo
log file group 2?
A. ALTER DATABASE ADD LOGFILE ‘/logs/redo22.log’ TO GROUP 2;
B. ALTER DATABASE ADD LOGFILE MEMBER ‘/logs/redo22.log’ TO GROUP 2;
C. ALTER DATABASE ADD MEMBER ‘/logs/redo22.log’ TO GROUP 2;
D. ALTER DATABASE ADD LOGFILE ‘/logs/redo22.log’;
---
Ans: B.
When adding log file members, specify the group number or specify all the
existing group members.
17. Which of the following is not a benefit of index clusters?
A. The tables in the cluster are always queried together.
B. Queries with an equivalence operator will perform better.
C. The tables in the cluster have little or no DML activity.
D. The child tables have roughly the same number of rows for each parent key in the parent table.
----
Ans: B.
Hash clusters, not index clusters, use a hashing function to find a row in a cluster
and perform best for equivalence queries.
18. Which of the following scenarios will never trigger Resumable Space
Allocation? (Choose all that apply.)
A. SELECT * FROM HR.EMPLOYEES;
B. DELETE FROM HR.EMPLOYEES;
C. DROP TABLE HR.EMPLOYEES;
D. SELECT * FROM HR.DEPARTMENTS ORDER BY DEPARTMENT_NUMBER;
E. ALTER INDEX HR.EMP_NAME_IX REBUILD ONLINE;
F. ALTER INDEX HR.EMP_NAME_IX REBUILD;
----
Ans: A, C.
Unless an ORDER BY clause forces a sort operation and uses up temporary
space, a SELECT command will not otherwise trigger Resumable Space Allocation.
Dropping a table does not use any additional disk space in a tablespace; it frees up
disk space. Therefore, it will not trigger Resumable Space Allocation.
19. An AFTER SUSPEND trigger can be defined at what level? (Choose all that apply.)
A. Instance
B. Schema
C. Table
D. Session
E. Database
---
Ans: B, E.
The AFTER SUSPEND system-level trigger can be defined only at the database
level or at the schema level.
20. Which of the following statements is not true about index-organized tables?
A. An index-organized table can have additional indexes defined on other columns in the table.
B. An index-organized table has both physical and logical ROWIDs.
C. An index-organized table without secondary indexes is stored in a single segment.
D. The space requirements for an index-organized table are reduced in part
because the data is stored in the same segment as the index, and therefore no physical ROWID is required.
---
Ans: B.
An index-organized table (IOT) does not have a physical ROWID; instead, a
logical ROWID is constructed based on the value of the primary key.
Review Questions
1. Which data dictionary view provides the recommended action, as a SQL statement, from the Segment Advisor?
A. DBA_ADVISOR_FINDINGS
B. DBA_ADVISOR_RECOMMENDATIONS
C. DBA_ADVISOR_ACTIONS
D. DBA_ADVISOR_RATIONALE
----
Ans: C.
The data dictionary view DBA_ADVISOR_ACTIONS contains the SQL statement(s) that the Segment Advisor supplies to implement its recommendation for segment maintenance. DBA_ADVISOR_FINDINGS contains the results of the analysis, but no SQL. DBA_ADVISOR_RECOMMENDATIONS presents one or more findings and the benefits for performing the recommendation. DBA_ADVISOR_RATIONALE provides a more detailed set of reasons why the recommendation should be implemented, along with the impact of not performing the recommendation.
2. Which of the following statements is not true about sorted hash clusters?
A. The new access path is used regardless of the type of predicate in the WHERE clause.
B. You are allowed to create indexes on sorted hash clusters.
C. The cost-based optimizer must be used to take advantage of the new access path.
D. Additional sorts are not necessary if you access the cluster by one of the lists of hash key columns.
E. More than one table can be stored in a sorted hash cluster.
---
Ans: A.
The new access path in a sorted hash cluster is used only if an equality predicate is used.
3. Consider the following scenario. The user SCOTT runs a query at 8:25 A.M.
that receives an ORA-01555: Snapshot too old error after running for 15
minutes. An alert is sent to the DBA that the undo tablespace is incorrectly
sized. At 10:15 A.M., the DBA checks the initialization parameter
UNDO_RETENTION, and its value is 3600; the parameter is sized correctly. The
DBA doubles the size of the undo tablespace by adding a second datafile. At
1:15 P.M., the user SCOTT runs the same query and once again receives an
ORA-01555: Snapshot too old error. What happens next? (Choose the best
answer.)
A. The DBA receives another alert indicating that the undo tablespace is
still undersized.
B. The user SCOTT calls the DBA to report that the query is still failing.
C. The second datafile autoextends so that future queries will have enough
undo space to complete when there is concurrent DML activity.
D. Resumable Space Allocation suspends the query until the DBA adds
another datafile to the undo tablespace, and then the query runs to completion.
---
Ans: B.
Even if the size of the undo tablespace is adjusted after an undo space problem,
only one alert is sent for each 24-hour period. Therefore, the only way that the
problem will be resolved promptly is for SCOTT to call the DBA, because the DBA
will not receive another alert until the next day when another query fails.
4. The background process __________ checks for tablespace threshold violation
or clearance every __________ minutes.
A. MMON, 10
B. SMON, 10
C. TMON, 30
D. PMON, 15
E. MMON, 30
---
Ans: A.
The new background process MMON checks for threshold violations every 10
minutes. An alert is triggered when the threshold is reached or is cleared.
5. Which of the following initialization parameters influences the recommended
redo log file size provided by the Redo Logfile Size Advisor?
A. LOG_CHECKPOINT_INTERVAL
B. OPTIMAL_LOGFILE_SIZE
C. FAST_START_IO_TARGET
D. FAST_START_MTTR_TARGET
E. None of the above
---
Ans: D.
FAST_START_MTTR_TARGET specifies the desired time, in seconds, for instance recovery after a crash or an instance failure. Therefore, the Redo Logfile Size
Advisor uses this value to determine the optimal log file size. OPTIMAL_LOGFILE_SIZE is not an initialization parameter but a column in the view V$INSTANCE_RECOVERY. The initialization parameter FAST_START_IO_TARGET specifies recovery at the I/O level, and LOG_CHECKPOINT_INTERVAL specifies the frequency of checkpoints in terms of redo log file blocks used.
6. Which of the following is not a benefit of segment shrink?
A. Full table scans will take less time.
B. Better index access takes place because of a smaller b-tree.
C. Space is freed up for other database objects.
D. All chained rows are fixed.
E. Space below the HWM is released, and the HWM is moved down.
----
Ans: D.
While some chained rows may be fixed with segment shrink functionality, it is not guaranteed that all chained rows will be fixed, because not all blocks may be read in
a segment shrink operation.
7. The EM Database Control Segment Resource Estimation feature uses all the following characteristics of the proposed table except for which one?
A. Column datatypes
B. PCTUSED
C. PCTFREE
D. Column sizes
E. Estimated number of rows
---
Ans: B.
Only PCTFREE is used in the calculation, because it is the amount of space to leave
free in the block for updates to existing rows. PCTUSED is not needed unless the
segment space management is not AUTO. In addition, extent sizes calculated by this
feature help assess the impact on the tablespace where this segment will be stored.
8. Which of the following is not a benefit of sorted hash clusters? (Choose the best answer.)
A. Rows within a given cluster key value are sorted by the sort key(s).
B. The ORDER BY clause is not required to retrieve rows in ascending or
descending order of the sort key(s).
C. Cluster key values are hashed.
D. Rows selected by a cluster key value using an equality operator are
returned in ascending or descending order.
---
Ans: C.
While cluster key values in a sorted hash cluster are hashed, this is also true of regular hash clusters, and therefore is not a benefit unique to sorted hash clusters.
9. In the following scenario, the DBA wants to reclaim a lot of wasted space in
the HR.EMPLOYEES table by using the segment shrink functionality. Which of
the following is the correct order of the steps?
1. ALTER TABLE HR.EMPLOYEES SHRINK SPACE;
2. ALTER TABLE HR.EMPLOYEES DISABLE ROW MOVEMENT;
3. ALTER TABLE HR.EMPLOYEES ENABLE ROW MOVEMENT;
4. ALTER TABLE HR.EMPLOYEES SHRINK SPACE COMPACT;
5. ALTER TABLE HR.EMPLOYEES SHRINK SPACE CASCADE;
A. 3, 4, 1, 5, 2
B. 4, 1, 3, 2, 5
C. 5, 2, 1, 3, 4
D. 4, 1, 2, 3, 5
---
Ans: A.
While the segment shrink operation could combine steps 1 and 4, the impact to the users will most likely be lessened by performing two smaller operations instead of one by specifying SHRINK SPACE COMPACT before specifying SHRINK SPACE in a
subsequent operation.
10. Which of the following calls to DBMS_SERVER_ALERT.SET_THRESHOLD will set the thresholds for the UNDOTBS1 tablespace to 60 percent and 90 percent?
(Choose the best answer.)
A)
dbms_server_alert.set_threshold(
dbms_server_alert.tablespace_pct_full,
dbms_server_alert.operator_ge, 60,
dbms_server_alert.operator_ge, 90,
1, 1, null,
dbms_server_alert.object_type_tablespace,
null);
B)
dbms_server_alert.set_threshold(
dbms_server_alert.tablespace_pct_full,
dbms_server_alert.operator_le, 60,
dbms_server_alert.operator_le, 90,
1, 1, null,
dbms_server_alert.object_type_datafile,
'UNDOTBS1');
C)
dbms_server_alert.set_threshold(
dbms_server_alert.tablespace_full,
dbms_server_alert.operator_ge, 60,
dbms_server_alert.operator_ge, 90,
1, 1, null,
dbms_server_alert.object_type_tablespace,
'UNDOTBS1');
D)
dbms_server_alert.set_threshold(
dbms_server_alert.tablespace_pct_full,
dbms_server_alert.operator_ge, 60,
dbms_server_alert.operator_ge, 90,
1, 1, null,
dbms_server_alert.object_type_tablespace,
'UNDOTBS1');
---
Ans: D.
The call to DBMS_SERVER_ALERT.SET_THRESHOLD must specify the metric
TABLESPACE_PCT_FULL, the two thresholds, an object type of tablespace, and the
tablespace name itself. Specifying NULL for the tablespace name will set the threshold
for all tablespaces, not just the UNDOTBS1 tablespace.
11. Which of the following statements is not true about segment shrink operations?
(Choose the best answer.)
A. Tables with ROWID-based materialized views are maintained.
B. Segment shrink is allowed only on segments whose space is automatically managed.
C. Heap-organized and index-organized tables can be shrunk.
D. ROW MOVEMENT must be enabled for heap-organized segments.
E. Chained rows may be repaired during a segment shrink operation.
F. Triggers are not fired during a segment shrink operation.
----
Ans: A.
Because the ROWIDs are changed with a segment shrink operation, tables with
ROWID- based materialized views cannot be shrunk unless the materialized views
are dropped and re-created after the segment shrink operation.
12. Which of the following is not a feature of the Segment Advisor within EM
Database Control?
A. Growth trend analysis
B. Segment Resource Estimation
C. Finding candidates for segment shrink
D. Finding table segments with chained rows
----
Ans: D.
The Segment Advisor is not used to find tables with chained rows, but instead is
used for finding segments that are good candidates for segment shrink or may be growing too fast.
13. Which of the following conditions will trigger an additional sort on a sorted
hash cluster? (Choose two.)
A. The ORDER BY clause specifies non-sort columns that are not indexed.
B. An ORDER BY clause is used in the query, although the sort may still fit in memory.
C. The cost-based optimizer is in effect.
D. The ORDER BY clause is omitted, and the WHERE clause does not reference the cluster key.
E. The ORDER BY clause specifies trailing sort columns.
---
Ans: A, E.
If a query on a sorted hash cluster retrieves rows and an ORDER BY clause
specifies either non-sort columns or a suffix of the sort columns, additional sorting is
required, assuming that indexes are not defined on the columns in the ORDER BY
clause.
14. Which of the following statements is not true about segment shrink operations
in tablespaces with automatic segment space management?
A. Clustered tables cannot be shrunk.
B. LOB segments can be shrunk.
C. IOT mapping tables and overflow segments cannot be shrunk.
D. Tables with function-based indexes cannot be shrunk.
E. ROW MOVEMENT must be enabled for heap-based segments.
---
Ans: B.
For segments in tablespaces with automatic segment space management, LOB
segments cannot be shrunk. In addition, tables with LONG columns, on-commit
materialized views, and ROWID-based materialized view cannot be shrunk. In all
cases, shrink operations cannot be performed on segments managed by freelists.
15. Which of the following is a disadvantage of rebuilding an index instead of
coalescing an index?
A. You temporarily need twice as much disk space to rebuild the index.
B. You cannot move the index to another tablespace.
C. The storage characteristics of the index cannot be changed.
D. The rebuild operation only coalesces index leaf blocks within each
branch instead of re-creating the entire tree.
----
Ans: A.
Whether you rebuild the index offline or online, you temporarily need twice as
much disk space. If you rebuild the index online, you also need disk space to support
a journal table to hold the intermediate changes to the index while the index is being
rebuilt.
16. Which of the following commands adds a member /logs/redo22.log to redo
log file group 2?
A. ALTER DATABASE ADD LOGFILE ‘/logs/redo22.log’ TO GROUP 2;
B. ALTER DATABASE ADD LOGFILE MEMBER ‘/logs/redo22.log’ TO GROUP 2;
C. ALTER DATABASE ADD MEMBER ‘/logs/redo22.log’ TO GROUP 2;
D. ALTER DATABASE ADD LOGFILE ‘/logs/redo22.log’;
---
Ans: B.
When adding log file members, specify the group number or specify all the
existing group members.
17. Which of the following is not a benefit of index clusters?
A. The tables in the cluster are always queried together.
B. Queries with an equivalence operator will perform better.
C. The tables in the cluster have little or no DML activity.
D. The child tables have roughly the same number of rows for each parent key in the parent table.
----
Ans: B.
Hash clusters, not index clusters, use a hashing function to find a row in a cluster
and perform best for equivalence queries.
18. Which of the following scenarios will never trigger Resumable Space
Allocation? (Choose all that apply.)
A. SELECT * FROM HR.EMPLOYEES;
B. DELETE FROM HR.EMPLOYEES;
C. DROP TABLE HR.EMPLOYEES;
D. SELECT * FROM HR.DEPARTMENTS ORDER BY DEPARTMENT_NUMBER;
E. ALTER INDEX HR.EMP_NAME_IX REBUILD ONLINE;
F. ALTER INDEX HR.EMP_NAME_IX REBUILD;
----
Ans: A, C.
Unless an ORDER BY clause forces a sort operation and uses up temporary
space, a SELECT command will not otherwise trigger Resumable Space Allocation.
Dropping a table does not use any additional disk space in a tablespace; it frees up
disk space. Therefore, it will not trigger Resumable Space Allocation.
19. An AFTER SUSPEND trigger can be defined at what level? (Choose all that apply.)
A. Instance
B. Schema
C. Table
D. Session
E. Database
---
Ans: B, E.
The AFTER SUSPEND system-level trigger can be defined only at the database
level or at the schema level.
20. Which of the following statements is not true about index-organized tables?
A. An index-organized table can have additional indexes defined on other columns in the table.
B. An index-organized table has both physical and logical ROWIDs.
C. An index-organized table without secondary indexes is stored in a single segment.
D. The space requirements for an index-organized table are reduced in part
because the data is stored in the same segment as the index, and therefore no physical ROWID is required.
---
Ans: B.
An index-organized table (IOT) does not have a physical ROWID; instead, a
logical ROWID is constructed based on the value of the primary key.
Subscribe to:
Posts (Atom)
