You are working on an instance started using the SPFILE. You want to move the Flash Recovery Area of your database to a new location. You want the Flashback log files to be stored in the new location.
Given below are the steps to accomplish the task in random order:
1) Shut down the instance.
2) Change the value of the DB_RECOVERY_FILE_DEST initialization parameter to a new value.
3) Execute the ALTER DATABASE FLASHBACK OFF command.
4) Start up the instance and mount the database.
5) Execute the ALTER DATABASE FLASHBACK ON command.
6) Open the database.
Select the correct order in which these tasks need to be performed.
A.
2, 1, 4, 3, 5, 6
B.
1, 4, 3, 2, 6, 5
C.
1, 4, 2, 6, 3, 5
D.
3, 2, 1, 4, 5, 6
In Oracle 11g(R2), you do not have to turning the database onto MOUNT mode.
You can release the ALTER DATABASE FLASHBACK ON/OFF on fly. even for parameter “DB_RECOVERY_FILE_DEST”, it is dynamic parameter onward 11g-R2.
From documentation (rel 11.2):
“… The database must be mounted but not open. In addition:
•The database must run in ARCHIVELOG mode.
•The database must be mounted, but not open, with a current control file. The control file cannot be a backup or re-created. When the database control file is restored from backup or re-created, all existing flashback log information is discarded.
•The database must contain no online tablespaces for which flashback functionality was disabled with the SQL statement ALTER TABLESPACE … FLASHBACK OFF.”
Oracle® Database SQL Language Reference
11g Release 2 (11.2)
FLASHBACK DATABASE
http://docs.oracle.com/cd/E11882_01/server.112/e41084/statements_9011.htm#SQLRF01801
Justyna, thanks for your information.
I cited information from: http://oraclehandson.wordpress.com/2012/08/29/enabling-disabling-flashback-database-in-11gr2-without-recycling-database/
And I tested on my virtual machine, it worked. For your reference.
I found something about this in Oracle documentation:
“To enable flashback logging:
1.Ensure the database instance is open or mounted.
…
When you enable Flashback Database while the database is open, there is a very small chance the command may not be able to obtain the memory it needs. If the command fails because of that reason, retry the command after a while or retry after a shutdown and restart of the instance…
Disabling Flashback Database Logging
On a database instances that is either in mount or open state,
…”
Using Flashback Database
http://docs.oracle.com/cd/E11882_01/backup.112/e10642/flashdb.htm#BRADV594
“You want to move the Flash Recovery Area of your database to a new location.”
If you are using an SPFILE then Specify the location of the flash recovery area by setting DB_RECOVERY_FILE_DEST — Which is Step (2) here in the above case
If you are using a PFILE, then shut down the database, edit the above parameters in the PFILE with the new values for DB_RECOVERY_FILE_DEST and DB_RECOVERY_FILE_DEST_SIZE and restart the instance. — (FYI Only)
Ref: https://docs.oracle.com/cd/B19306_01/backup.102/b14191/rcmasm.htm#BABJHIHA
“You want the Flashback log files to be stored in the new location.”
Because the actual flashback logs cannot be migrated, the only step required to move the location of flashback logs to the new ASM flash recovery area is to disable and then enable flashback logging. After a clean shutdown, mount the database and run the following commands in SQL*Plus:
SQL> ALTER DATABASE FLASHBACK OFF;
SQL> ALTER DATABASE FLASHBACK ON;
So Steps – 1,4,3,5,6
Ref: https://docs.oracle.com/cd/B19306_01/backup.102/b14191/rcmasm.htm#BABJHIHA
and scroll to “Changing Flashback Log Location to ASM Flash Recovery Area”