Which four actions are possible during an Online Data file Move operation?
A.
Creating and dropping tables in the data file being moved
B.
Performing file shrink of the data file being moved
C.
Querying tables in the data file being moved
D.
Performing Block Media Recovery for a data block in the data file being moved
E.
Flashing back the database
F.
Executing DML statements on objects stored in the data file being moved
Explanation:
Restrictions When Moving the Location of Online Data Files
* (Not B) The online move data file operation may get aborted if the standby recovery processtakes the data file offline, shrinks the file (not B), or drops the tablespace.
* (Not D?) The online move data file operation cannot be executed on physical standby while
standby recovery is running in a mounted but not open instance.
* You cannot use the SQL ALTER DATABASE MOVE DATAFILE command to rename or relocate
an online data file on a physical standby that is a fast-start failover target if the standby is
mounted, but not open
* On a primary database, the online move data file operation cannot be executed on a file that
belongs to a pluggable database (PDB) that has been closed on all instances of the primary
database.
Note:
You can move the location of an online data file from one physical file to another physical file while
the database is actively accessing the file. To do so, you use the SQL statement ALTER
DATABASE MOVE DATAFILE.
An operation performed with the ALTER DATABASE MOVE DATAFILE statement increases the
availability of the database because it does not require that the database be shut down to move
the location of an online data file. In releases prior to Oracle Database 12c Release 1 (12.1), you
could only move the location of an online data file if the database was down or not open, or by first
taking the file offline.
You can perform an online move data file operation independently on the primary and on the
standby (either physical or logical). The standby is not affected when a data file is moved on the
primary, and vice versa.
Reference: Oracle Data Guard Concepts and Administration 12c, Moving the Location of Online
Data Files
An Online Move data file operation is not compatible when:
• The data file is an OFFLINE data file
• A concurrent flashback database operation is executing
• A media recovery is completing
• A file shrink operation or tablespace offline/drop operation involving the same file is
performing
so only the statements A, C and F are correct
please note that block recovery can be done while the data file was moved. but media recovery is not possible
NOT COMPATIBLE –
1. Data file OFFLINE
2. Concurrent FLASHBACK DATABASE opration
3. Concurrent Media Recovery
4. data file RESIZE – shrink – operation
COMPATIBLE –
1. Block Media Recovery
2. Tablespace made READONLY or READ WRITE
3. data file RESIZE – extension
4. Online backup
So, corrent answers are A,C,D,F.
ORA-10459: cannot start media recovery on standby database; conflicting state detected
Cause: An attempt was made to perform media recovery on a standby database while another instance was performing recovery on the standby database, had the standby database open, or a data file move operation was in progress.
Action: Close the standby on all other instances, cancel any conflicting recovery session or data file move operation, and perform manual recovery.
ACDF
How say tamil and Joe..
Block Media Recovery can be done while the data file was moved…and Media Recovery is not possible.
“The good news is that Oracle 12cR1 now offers the ability to move entire datafiles between different storage locations without ever having to take the datafiles offline. The datafiles being moved remain completely accessible to applications in almost all situations, including querying against or performing DML and DDL operations against existing objects, creating new objects, and even rebuilding indexes online. Online Move Datafile (OMD) also makes it possible to migrate a datafile between non-ASM and ASM storage (or vice-versa) while maintaining transparent application access to that datafile’s underlying database objects. OMD is completely compatible with online block media recovery, the automatic extension of a datafile, the modification of a tablespace between READ WRITE and READ ONLY mode, and it even permits backup operations to continue against any datafiles that are being moved via this feature.”
http://muhammad-asif-dba.blogspot.com.br/2013/11/oracle-12c-database-online-move-datafile.html
* As said tamil and Joe..
A,C,D and F are correct answer.
ACDF
ACDF
Starting with Oracle 12.1, you can now use the command
SQL > ALTER DATABASE MOVE DATAFILE ;
in order to rename, relocate, or copy a datafile when the datafiles or the database are online. One step only is required for any of these actions and the database remains entirely available in read and write for users, without any data loss.
You should however be aware of some rules:
By default, Oracle automatically deletes old data file after moving them and prevents the user from overwriting an existing file.
When you move a data file, Oracle first makes a copy of the datafile. Then, when the file is successfully copied, pointers to the datafile are updated and the old file is removed from the file system. This is why the operation requires twice the size of the files to be copied as free space.
Finally, some files like temporary files, redo log files, and control files cannot be moved using this command, which is very helpful but can still be improved.
Oracle 12cR1 now offers the ability to move entire datafiles between different storage locations without ever having to take the datafiles offline. The datafiles being moved remain completely accessible to applications in almost all situations, including querying against or performing DML and DDL operations against existing objects, creating new objects, and even rebuilding indexes online. Online Move Datafile (OMD) also makes it possible to migrate a datafile between non-ASM and ASM storage (or vice-versa) while maintaining transparent application access to that datafile’s underlying database objects. OMD is completely compatible with online block media recovery, the automatic extension of a datafile, the modification of a tablespace between READ WRITE and READ ONLY mode, and it even permits backup operations to continue against any datafiles that are being moved via this feature. (Reference: Mohamed Asif)
On the backup & recovery side, a flashback database does not revert the operation. A datafile is definitively moved.
A, C, D, F
ACDF
ACDF
ACDF are Correct
ACDF
Is the below statement means the Flashback database can be done.. Am I right?
If you move a data file
online from one location to another and later flash back the database to a point in time before the move,
then the Data file remains in the new location, but the contents of the Data file ore changed to the contents
at the time specified in the flashback. Oracle0 Database Administrator’s Guide 12c Release 1 (12.1)