Utiliser
flashback pour faire un rollback d’une MEP.
L
idée est
de ne pas avoir
a reconstruire la standby.
Si la
primaire et la
standby sont en
flashback database on a pas
besoin de reconstruire la standby.
1 Architecture:
1.1 Voila la configuration :
Primary
|
STANDBY
|
|
Clusterware
|
11GR2 Grid infra (11.2.0.2)
|
11GR2 Grid infra (11.2.0.2)
|
Cluster Nodes
|
pocracm1,pocracm2
|
pocracl1,pocracl2
|
SCAN
|
||
SCAN listener Host/port
|
poccrs 1521
|
drpocrcs 1521
|
VIPs
|
pocracm1-vip,pocracm2-vip
|
pocracl1-vip,pocracl2-vip
|
DB_UNIQUE_NAME
|
pocrad
|
drpocrad
|
DB_NAME
|
POCRAD
|
POCRAD
|
DB Instances
|
POCRAD1,POCRAD2
|
DRPOCRAD1,DRPOCRAD2
|
DB LISTENER
|
LISTENER
|
LISTENER
|
DB Listener Host/port
|
1521
|
1521
|
DB STORAGE
|
ASM
|
ASM
|
File Management
|
FS
|
FS
|
GRID_HOME
|
/apps/grid
|
/apps/grid
|
ORACLE_HOME
|
/apps/oracle/11.2.0.2
|
/apps/oracle/11.2.0.2
|
11g R2 RAC version
|
11.2.0.2
|
11.2.0.2
|
OS
|
linux
|
Linux
|
service
|
Sale_rw
|
Sale_ro
|
archivelog
|
||
ASM
|
taille en Go
|
ASM
|
POCDB
|
200
|
POCDBL
|
POCOCR
|
2
|
POCOCRL
|
POCREDO
|
10
|
POCREDOL
|
POCARCH
|
40
|
POCARCH
|
2 Limitations of Flashback Database
Because Flashback Database works by undoing changes to the data files that exist at the moment when you run the command, it has the following limitations:
·
Flashback Database can only undo changes to
a data file made by Oracle Database. It cannot be used to repair media
failures, or to recover from accidental deletion of data files.
·
You cannot use Flashback Database to undo a
shrink data file operation. However, you can take the shrunken file offline,
flash back the rest of the database, and then later restore and recover the
shrunken data file.
·
You cannot use Flashback Database alone to
retrieve a dropped data file. If you flash back a database to a time when a
dropped data file existed in the database, only the data file entry is added to
the control file. You can only recover the dropped data file by using RMAN to
fully restore and recover the data file.
·
If the database control file is restored
from backup or re-created, all accumulated flashback log information is
discarded. You cannot use
FLASHBACK DATABASE to return to a point in time before the restore or re-creation of a
control file.
·
When using Flashback Database with a target
time at which a
NOLOGGING operation was in
progress, block corruption is likely in the database objects and datafiles
affected by the NOLOGGING operation. For
example, if you perform a direct-path INSERT operation in NOLOGGING
mode, and that operation runs from 9:00 to 9:15 on April 3, 2005, and you later
use Flashback Database to return to the target time 09:07 on that date, the
objects and datafiles updated by the direct-path INSERT may be left with block corruption after the Flashback Database operation
completes.
If possible, avoid using Flashback Database
with a target time or SCN that coincides with a
NOLOGGING operation. Also, perform a full or incremental backup of the affected data
files immediately after any NOLOGGING
operation to ensure recoverability to points in time after the operation. If
you expect to use Flashback Database to return to a point in time during an
operation such as a direct-path INSERT,
consider performing the operation in LOGGING mode.3 Prerequis :
Les
backups sont pris
sur la primaire.
La base est
en force logging.
Compatible
11.2.0.0.2.
4 Scenario tout est ok :
1
|
arret de tous ce
aui frappe la
bd
|
primary
|
2
|
backup
archivelog primaire
RMAN>backup archivelog
delete input;
|
primary
|
3
|
verifie la cron
oracle
|
primary
|
4
|
Verification des parametres:
db_recovery* db_flashback_retention (1440) |
primary/standby
|
4.5
|
Arret de la standby
+ démarrage en mode mount
Srvctl stop
database -d DRPOCRAD
Sqlplus / as
sysdba
Startup mount
|
standby
|
5
|
active le flashback on
alter database flashback on; select FLASHBACK_ON from V$database; |
primary/standby
|
5.5
|
Srvctl stop
database -d DRPOCRAD
Srvctl start
database -d DRPOCRAD
select FLASHBACK_ON from V$database;
|
standby
|
6
|
switch tous les
redologs et on attends
l application sur la standby
select thread#,max(sequence#) from V$archived_log group by thread#;
alter system
switch logfile;
select
thread#,max(sequence#) from
V$archived_log group by
thread#;
|
primary
|
7
|
Verification sur la standby :
select max(sequence#),THREAD# from v$archived_log where applied='YES' and REGISTRAR='RFS' group by thread#; |
standby
|
8
|
Reperer le scn
select dbms_flashback.get_system_change_number begin_scn from dual; |
primary
|
9
|
creation du restore point guarantee
CREATE RESTORE POINT before_upgrade
GUARANTEE FLASHBACK DATABASE;
SELECT NAME, SCN, TIME,
DATABASE_INCARNATION#,
GUARANTEE_FLASHBACK_DATABASE,STORAGE_SIZE FROM V$RESTORE_POINT;
|
primary
|
10
|
roule la mep
|
primary
|
11
|
GO
|
|
12
|
drop restore point
drop restore point XXXX;
|
primary/standby
|
13
|
desactiver le flashback
ALTER DATABASE FLASHBACK OFF;
|
primary/standby
|
Chronologie des étapes pour ce scenario :
standby
|
4
|
4.5
|
5
|
5.5
|
7
|
12
|
13
|
|||||||
primary
|
1+2
|
3
|
4
|
5
|
6
|
8
|
9
|
MEP
|
GO
|
12
|
13
|
5 Scenario Rollback
1
|
arret de tous ce
aui frappe la
bd
|
primary
|
2
|
backup
archivelog primaire
RMAN>backup archivelog
delete input;
|
primary
|
3
|
Verification de la cron
oracle
|
primary
|
4
|
Verification des paramètres:
db_recovery* db_flashback_retention (1440) |
primary/standby
|
4,5
|
Arret de la standby
+ démarrage en mode mount
Srvctl stop
database -d DRPOCRAD
Sqlplus / as
sysdba
Startup mount
|
standby
|
5
|
active le flashback on
alter database flashback on; select FLASHBACK_ON from V$database; |
primary/standby
|
5,5
|
Srvctl start
database -d DRPOCRAD
select FLASHBACK_ON from V$database;
|
Standby
|
6
|
switch tous les
redologs et on attends
l application sur la standby
select thread#,max(sequence#) from V$archived_log group by thread#;
alter system
switch logfile;
select
thread#,max(sequence#) from
V$archived_log group by
thread#;
|
primary
|
7
|
Verification
sur la standby :
select max(sequence#),THREAD# from v$archived_log where applied='YES' and REGISTRAR='RFS' group by thread#; |
standby
|
8
|
repère
le scn
select dbms_flashback.get_system_change_number begin_scn from dual; |
primary
|
9
|
Creation du restore point guarantee
CREATE RESTORE POINT before_upgrade
GUARANTEE FLASHBACK DATABASE;
SELECT NAME, SCN, TIME,
DATABASE_INCARNATION#,
GUARANTEE_FLASHBACK_DATABASE,STORAGE_SIZE
FROM V$RESTORE_POINT;
|
primary
|
10
|
roule la mep
|
primary
|
11
|
NO GO
|
|
12
|
arret de la BD
srvctl stop database -d DBname |
primary/standby
|
13
|
startup mount d
une instance
startup
mount exclusive
|
primary/standby
|
14
|
flashback
database to scn XXXXXX;
|
primary/standby
|
15
|
alter
database open resetlogs;
|
primary
|
16
|
shutdown immediate
|
primary/standby
|
17
|
startup de la db
srvctl start database -d |
primary/standby
|
18
|
Verifie le MRP
et le transfert des archives
|
standby
|
Chronologies des actions pour ce scenario :
standby
|
4
|
4.5
|
5
|
5.5
|
7
|
12
|
13
|
14
|
16
|
17
|
18
|
|||||||||
primaire
|
1
|
2
|
3
|
4
|
5
|
6
|
8
|
9
|
10 MEP
|
NO GO
|
12
|
13
|
14
|
15
|
16
|
17
|
