Training environment refresh (database flashback)

Oracle, Solaris 10

Create regular refresh of training database.

1.    Check that the following parameters are set as you want and there is sufficient disk space.
2.    Make clean shutdown and mount database.
shutdown immediate;
startup mount;
3.    Enable archivelog and disable flashback.
When flashback is disabled, database can be flashed back only to a guaranteed restore point. When data block is modified multiple times, only original block image is stored in flashback logs, so you can not flash back to another SCN, but you have less disk utilization compared to enabled flashback.
alter database archivelog;
alter database flashback off;
4.    Check if you have any restore points and drop them if they are not needed.
select * from v$restore_point;
drop restore point ;
5.    Create guaranteed restore point.
create restore point GRP1 guarantee flashback database;
6.    Open database.
alter database open;

7.    Schedule the following script to refresh database at required time.
You can delete all archive logs. I keep 4 days just in case. Actually if even recent archive logs are deleted (created between resetlogs and current moment), database flashback is successful. But you cannot switch to noarchivelog if there are guaranteed restore points.

. ./.profile
# stop dependant applications if needed
sqlplus "/as sysdba" << EOF
shutdown immediate;
startup force mount;
flashback database to restore point GRP1;
alter database open resetlogs;

# start dependant applications if needed
rman target / << EOF
delete force noprompt archivelog all completed before 'sysdate-4';
FORCE option is necessary here. When database is opened with RESETLOGS option, FGRD archives redo logs before logs are cleared. So some logs will be archived twice by ARCH and FGRD, and control file will have two records of one log. You can see it in V$ARCHIVED_LOG. When logs are deleted, second DELETE of the same log will return error that file doesn’t exist.
For example:
RMAN-06207: WARNING: 2 objects could not be deleted for DISK channel(s) due
RMAN-06208:          to mismatched status.  Use CROSSCHECK command to fix status
RMAN-06210: List of Mismatched objects
RMAN-06211: ==========================
RMAN-06212:   Object Type   Filename/Handle
RMAN-06213: --------------- ---------------------------------------------------
RMAN-06214: Archivelog      /oracle/archive/dhmerr04/disk/dhmerr04_0000000001_1_729369092.arc
RMAN-06214: Archivelog      /oracle/archive/dhmerr04/disk/dhmerr04_0000000002_1_729369092.arc

Useful info