Re: Rollback via Fast Recovery Area - Oracle 11g

From: Eriovaldo Andrietta <ecandrietta_at_gmail.com>
Date: Mon, 3 Aug 2015 18:55:33 -0300
Message-ID: <CAJdDhaPeasn9PcSxJmfFuei=xcAOw7vQfWd_Ks58FkdFuB53SA_at_mail.gmail.com>

​
Thanks for your comments, and as you said, "we had something similar". It means you had experience with this situation.

In fact it is a "plan" to do not be executed. When we work with production environment, we work to go ahead, after all software to be tested and also in pre-production, and customer approved all, and all parts assume all. It is the ideal.

The manual rollback to be prepared is too exaustive when we have a lot of itens to be changed.

As I commented before, also were considered:

- rollback prepared via scripts (ctas) manually for DML
- rollback via EBR + scripts (ctas) manually for DML
- return of backup via RMAN
- flashback database  (I tested it and it worked fine for my scenary)

Considering that the plan only will be executed in the extreme case, the recovery via flashback database is a option. In fact this is a study and we need consider all options for different scenaries.

Here are commands I used :
ALTER SYSTEM SET DB_RECOVERY_FILE_DEST_SIZE = 30g SCOPE=BOTH;

/* ---------- BEGIN ------------------------- */

shutdown immediate
startup mount
archive log list
alter database archivelog; /* if database is not in ARCHIVELOG mode */ show parameter db_recovery_file
select log_mode from v$database;
ALTER SYSTEM SET DB_RECOVERY_FILE_DEST = '<folder in the server or the flashback area>' SCOPE=BOTH;

select flashback_on from v$database;
create restore point patchdm guarantee flashback database; select flashback_on from v$database;

select scn, guarantee_flashback_database, time, name from v$restore_point;

alter database open;
archive log list

-----------> ****************************************************

<------------


-----------> Here database is ready for apply changes

<------------


-----------> ****************************************************

<------------



-----------> ****************************************************

<------------


-----------> Change objects via DDL commands in the database

 <------------


-----------> Change data via DML commands in the database

<------------


-----------> User validates new version via application

<------------


-----------> ****************************************************

<------------

select * from v$flash_recovery_area_usage;
-- Starging recovery

shutdown immediate
startup mount
flashback database to restore point patchdm; /* the database must be mounted */
alter database open resetlogs;

drop restore point patchdm;
select flashback_on from v$database;
show parameter db_recovery_file
ALTER SYSTEM SET DB_RECOVERY_FILE_DEST = '' SCOPE=BOTH; ALTER SYSTEM RESET DB_RECOVERY_FILE_DEST_SIZE;

• Turn off ARCHIVE MODE (if necessary) shutdown immediate startup mount archive log list alter database noarchivelog; alter database open;
• Checking show parameter db_recovery_file archive log list

/* ---------- END   ------------------------- */

Best Regards
Eriovaldo

--

http://www.freelists.org/webpage/oracle-l Received on Mon Aug 03 2015 - 23:55:33 CEST