Environment
Oracle 10.2.0.4, Solaris 10
Requirements
Create regular refresh of training database.
Solution
1. Check that the following parameters are set as you want and there is sufficient disk space.
db_recovery_file_dest
db_recovery_file_dest_size
log_archive_format
log_archive_dest_1
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.
#!/usr/bin/ksh
cd
. ./.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;
EOF
# start dependant applications if needed
#
rman target / << EOF
delete force noprompt archivelog all completed before 'sysdate-4';
EOF
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
http://download-east.oracle.com/docs/cd/B19306_01/backup.102/b14192/rpfbdb001.htm
Oracle 10.2.0.4, Solaris 10
Requirements
Create regular refresh of training database.
Solution
1. Check that the following parameters are set as you want and there is sufficient disk space.
db_recovery_file_dest
db_recovery_file_dest_size
log_archive_format
log_archive_dest_1
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.
#!/usr/bin/ksh
cd
. ./.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;
EOF
# start dependant applications if needed
#
rman target / << EOF
delete force noprompt archivelog all completed before 'sysdate-4';
EOF
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
http://download-east.oracle.com/docs/cd/B19306_01/backup.102/b14192/rpfbdb001.htm
