You have a database with the following tablespaces: SYSTEM, SYSAUX, UNDO, USERS,
TEMP. You want to”roll back” the data in the USERS tablespace to the way it looked
yesterday.
Which tablespaces do you need to perform a point-in-time restore operation on in order to
complete this task? (Choose all that apply.)
A.
SYSTEM
B.
SYSAUX
C.
UNDO
D.
USERS
E.
TEMP
F.
This restore is not possible.
The “E” answer is also correct:
Auxiliary set:
Data files required for TSPITR that are not part of the recovery set. The auxiliary set typically includes:
The SYSTEM and SYSAUX tablespaces.
Data files containing rollback or undo segments from the target database instance.
Temporary tablespaces.
Control file from source database.
Archived redo logs that must be restored to recover the auxiliary instance to specified point in time.
Online redo logs of the auxiliary instance. These are not the same logs as the online redo logs from the source database. They are created when the auxiliary instance is opened with the RESETLOGS option.
The auxiliary set does not include the parameter file, password file, or associated network files.
I think you’d need temp, but not to do a pit restore in it. It would be used for whatever temp tablespaces are normally used
Auxiliary set: Data files required for TSPITR of the recovery set that are not themselves part of
the recovery set. The auxiliary set typically includes:
– A copy of the SYSTEM tablespace
– Data files containing undo segments from the target instance
– In some cases, a temporary tablespace, used during the export of database objects from the auxiliary instance
***********
Data files required for TSPITR that are not part of the recovery set. The auxiliary set typically includes:
• The SYSTEM and SYSAUX tablespaces.
• Data files containing rollback or undo segments from the target database instance.
• Temporary tablespaces.
• Control file from source database.
• Archived redo logs that must be restored to recover the auxiliary instance to specified point in time.
• Online redo logs of the auxiliary instance. These are not the same logs as the online redo logs from the source database. They are created when the auxiliary instance is opened with the RESETLOGS option.
The auxiliary set does not include the parameter file, password file, or associated network files.
This is another ambiguous question.
The auxiliary set includes the SYSTEM, SYSAUX, UNDO tablespaces.
However redo logs, controlfiles and tempfiles (i.e. the TEMPORARY tablespace) although considered part of the auxiliary set are not transported but are recreated (hence the need for a RESETLOGS on the target).
The password file, network files and SPFILE are not however part of the auxiliary set as you must create them.
The auxiliary set and the recovery set that are restored.
To be exact, typically one would use the automated TSPITR process which looks after restoring the auxiliary set, the DBA only has to specify the recovery set
RECOVER TABLESPACE ts1, ts2 UNTIL TIME ’18-12-15 09:01:00′ AUXILIARY DESTINATION ‘/u01/app/oracle/oradata/auxdata’;
so it is not necessarily the DBA that has to specify the auxiliary set explicitly. You just have to ensure that your backups and archive redo logs are up to date so that TSPITR can find them.
So when this question asks “Which tablespaces do you need to perform a point-in-time restore operation on in order to complete this task?”
one could argue that the answer is just the USER tablespace.
A,B,C and D are correct.
The question not is about TSPITR, but about database PITR!! So to “rollback” the tablespace USERS is need perform a PITR in all permanents tablespaces.