Data Guard | Pickleball spielen

Flashback Database puts Physical Standby into MOUNT from READ-ONLY OPEN

Veröffentlicht von Uwe Hesse in TOI am Dezember 22, 2010

Another bit of discovery from my present Data Guard course in Munich: If you have a Physical Standby opened READ-ONLY, you can do Flashback Database in that state without first shutting down and going to status MOUNT.

I think that this is an 11g New Feature, but I didn’t check it with 10g yet. Let’s have a look first at the ordinary behavior:

SQL> select database_role,open_mode,flashback_on from v$database;

DATABASE_ROLE    OPEN_MODE            FLASHBACK_ON
---------------- -------------------- ------------------
PRIMARY          READ WRITE           YES

SQL> flashback database to timestamp systimestamp - interval '5' minute;
flashback database to timestamp systimestamp - interval '5' minute
*
ERROR at line 1:
ORA-38757: Database must be mounted and not open to FLASHBACK.

This is not a Standby Database and the error is completely expectable. Everybody knows that Flashback Database can only be done in status MOUNT, right? Well not always:

SQL> select * from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
PL/SQL Release 11.2.0.1.0 - Production
CORE    11.2.0.1.0      Production
TNS for Linux: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production

SQL> select database_role,open_mode,flashback_on from v$database;

DATABASE_ROLE    OPEN_MODE            FLASHBACK_ON
---------------- -------------------- ------------------
PHYSICAL STANDBY READ ONLY            YES

SQL> flashback database to timestamp systimestamp - interval '5' minute;

Flashback complete.

It did the Flashback Database! And very fast also. But silently, the instance is now placed in status MOUNT:

SQL> select database_role,open_mode,flashback_on from v$database;

DATABASE_ROLE    OPEN_MODE            FLASHBACK_ON
---------------- -------------------- ------------------
PHYSICAL STANDBY MOUNTED              YES

In fact, I did not discover this behavior myself, but an attendee did and later on asked me, why Real-Time Query is now no longer in place on the Standby – that is because it is now in status MOUNT. I would not even had try to do that Flashback Database, because I ‚knew‘ that it can’t work 🙂

Now did you know that?

By the way, since 11gR2, you can turn on Flashback Database for an ordinary Database with it in status OPEN, as I have already posted here.

11g New Features, Data Guard, flashback

4 Kommentare

Clone Database from Snapshot Standby Database

Veröffentlicht von Uwe Hesse in TOI am Dezember 21, 2010

In 11g, we introduced the new feature Snapshot Standby Database for Data Guard. In my current Data Guard course, one of the attendees asked, whether it is possible to create a Database Clone out of such a Snapshot Standby Database. Maybe you want to preserve the changes that have been done during the time, your Physical Standby Database was turned into a READ-WRITE opened (Test-)Database. I answered that this is mot likely possible, but I did not try yet. Now I did 🙂

DGMGRL> convert database physt to snapshot standby;
Converting database "physt" to a Snapshot Standby database, please wait...
Database "physt" converted successfully

There is my Snapshot Standby Database. You may reproduce the Data Guard Configuration in place here with the Whitepaper 11g Data Guard on the Command Line from my Downloads page.

DGMGRL> show configuration

Configuration - myconf

 Protection Mode: MaxPerformance
 Databases:
 prima - Primary database
 physt - Snapshot standby database

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS

I continue to prepare a Clone Database by creating an spfile and a password file for it (very similar as described in the Whitepaper for the physt Database). Then I start it into NOMOUNT:

uhesse $ sqlplus sys/oracle@clon as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Tue Dec 21 17:00:26 2010

Copyright (c) 1982, 2009, Oracle.  All rights reserved.

Connected to an idle instance.

SQL> startup nomount
ORACLE instance started.

Total System Global Area  313860096 bytes
Fixed Size                  1336232 bytes
Variable Size             209718360 bytes
Database Buffers           96468992 bytes
Redo Buffers                6336512 bytes
SQL> select * from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
PL/SQL Release 11.2.0.1.0 - Production
CORE    11.2.0.1.0      Production
TNS for Linux: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production

The version in use is 11gR2, but it should work with 11gR1 the same way. Now RMAN is used to create the Clone from the Snapshot Standby:

RMAN> connect target sys/oracle@physt

connected to target database: PRIMA (DBID=1970640575)

RMAN> connect auxiliary sys/oracle@clon

connected to auxiliary database: CLON (not mounted)

RMAN> duplicate target database to clon from active database;

Starting Duplicate Db at 21-DEC-10
using target database control file instead of recovery catalog
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=96 device type=DISK
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of Duplicate Db command at 12/21/2010 17:03:19
RMAN-05541: no archived logs found in target database

RMAN> exit

Oops! I need Archivelogs on the Snapshot Standby. Note that they are in the same directory but with a different Resetlogs Identifier as the Archivelogs created when the Database was a Physical Standby Database.

uhesse $ sqlplus sys/oracle@physt as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Tue Dec 21 17:03:49 2010

Copyright (c) 1982, 2009, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> alter system switch logfile;

System altered.

SQL> select name from v$archived_log;

NAME
--------------------------------------------------------------------------------
/home/oracle/physt/archive/1_12_738347903.dbf
/home/oracle/physt/archive/1_8_738347903.dbf
/home/oracle/physt/archive/1_7_738347903.dbf
/home/oracle/physt/archive/1_9_738347903.dbf
/home/oracle/physt/archive/1_11_738347903.dbf
/home/oracle/physt/archive/1_10_738347903.dbf
/home/oracle/physt/archive/1_13_738347903.dbf
/home/oracle/physt/archive/1_14_738347903.dbf
/home/oracle/physt/archive/1_15_738347903.dbf
/home/oracle/physt/archive/1_1_738349098.dbf

10 rows selected.

Again we go with the duplicate:

RMAN> connect target sys/oracle@physt

connected to target database: PRIMA (DBID=1970640575)

RMAN> connect auxiliary sys/oracle@clon

connected to auxiliary database: CLON (not mounted)

RMAN> duplicate target database to clon from active database;

Starting Duplicate Db at 21-DEC-10
using target database control file instead of recovery catalog
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=95 device type=DISK

contents of Memory Script:
{
 sql clone "alter system set  db_name =
 ''PRIMA'' comment=
 ''Modified by RMAN duplicate'' scope=spfile";
 sql clone "alter system set  db_unique_name =
 ''CLON'' comment=
 ''Modified by RMAN duplicate'' scope=spfile";
 shutdown clone immediate;
 startup clone force nomount
 backup as copy current controlfile auxiliary format  '/home/oracle/clon/control01.ctl';
 alter clone database mount;
}
executing Memory Script

sql statement: alter system set  db_name =  ''PRIMA'' comment= ''Modified by RMAN duplicate'' scope=spfile

sql statement: alter system set  db_unique_name =  ''CLON'' comment= ''Modified by RMAN duplicate'' scope=spfile

Oracle instance shut down

Oracle instance started

Total System Global Area     313860096 bytes

Fixed Size                     1336232 bytes
Variable Size                209718360 bytes
Database Buffers              96468992 bytes
Redo Buffers                   6336512 bytes

Starting backup at 21-DEC-10
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=24 device type=DISK
channel ORA_DISK_1: starting datafile copy
copying current control file
output file name=/u01/app/oracle/product/11.2.0/dbhome_1/dbs/snapcf_physt.f tag=TAG20101221T172429 RECID=6 STAMP=738350670
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01
Finished backup at 21-DEC-10

database mounted

contents of Memory Script:
{
 set newname for datafile  1 to
 "/home/oracle/clon/system01.dbf";
 set newname for datafile  2 to
 "/home/oracle/clon/sysaux01.dbf";
 set newname for datafile  3 to
 "/home/oracle/clon/undotbs01.dbf";
 set newname for datafile  4 to
 "/home/oracle/clon/users01.dbf";
 backup as copy reuse
 datafile  1 auxiliary format
 "/home/oracle/clon/system01.dbf"   datafile
 2 auxiliary format
 "/home/oracle/clon/sysaux01.dbf"   datafile
 3 auxiliary format
 "/home/oracle/clon/undotbs01.dbf"   datafile
 4 auxiliary format
 "/home/oracle/clon/users01.dbf"   ;
 sql 'alter system archive log current';
}
executing Memory Script

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

Starting backup at 21-DEC-10
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile copy
input datafile file number=00001 name=/home/oracle/physt/system01.dbf
output file name=/home/oracle/clon/system01.dbf tag=TAG20101221T172436
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:25
channel ORA_DISK_1: starting datafile copy
input datafile file number=00002 name=/home/oracle/physt/sysaux01.dbf
output file name=/home/oracle/clon/sysaux01.dbf tag=TAG20101221T172436
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:25
channel ORA_DISK_1: starting datafile copy
input datafile file number=00003 name=/home/oracle/physt/undotbs01.dbf
output file name=/home/oracle/clon/undotbs01.dbf tag=TAG20101221T172436
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:15
channel ORA_DISK_1: starting datafile copy
input datafile file number=00004 name=/home/oracle/physt/users01.dbf
output file name=/home/oracle/clon/users01.dbf tag=TAG20101221T172436
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:03
Finished backup at 21-DEC-10

sql statement: alter system archive log current

contents of Memory Script:
{
 backup as copy reuse
 archivelog like  "/home/oracle/physt/archive/1_3_738349098.dbf" auxiliary format
 "/home/oracle/clon/archive/1_3_738349098.dbf"   ;
 catalog clone archivelog  "/home/oracle/clon/archive/1_3_738349098.dbf";
 switch clone datafile all;
}
executing Memory Script

Starting backup at 21-DEC-10
using channel ORA_DISK_1
channel ORA_DISK_1: starting archived log copy
input archived log thread=1 sequence=3 RECID=12 STAMP=738350751
output file name=/home/oracle/clon/archive/1_3_738349098.dbf RECID=0 STAMP=0
channel ORA_DISK_1: archived log copy complete, elapsed time: 00:00:15
Finished backup at 21-DEC-10

cataloged archived log
archived log file name=/home/oracle/clon/archive/1_3_738349098.dbf RECID=12 STAMP=738350766

datafile 1 switched to datafile copy
input datafile copy RECID=6 STAMP=738350766 file name=/home/oracle/clon/system01.dbf
datafile 2 switched to datafile copy
input datafile copy RECID=7 STAMP=738350767 file name=/home/oracle/clon/sysaux01.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=8 STAMP=738350767 file name=/home/oracle/clon/undotbs01.dbf
datafile 4 switched to datafile copy
input datafile copy RECID=9 STAMP=738350767 file name=/home/oracle/clon/users01.dbf

contents of Memory Script:
{
 set until scn  226204;
 recover
 clone database
 delete archivelog
 ;
}
executing Memory Script

executing command: SET until clause

Starting recover at 21-DEC-10
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=95 device type=DISK

starting media recovery

archived log for thread 1 with sequence 3 is already on disk as file /home/oracle/clon/archive/1_3_738349098.dbf
archived log file name=/home/oracle/clon/archive/1_3_738349098.dbf thread=1 sequence=3
media recovery complete, elapsed time: 00:00:00
Finished recover at 21-DEC-10

contents of Memory Script:
{
 shutdown clone immediate;
 startup clone nomount;
 sql clone "alter system set  db_name =
 ''CLON'' comment=
 ''Reset to original value by RMAN'' scope=spfile";
 sql clone "alter system reset  db_unique_name scope=spfile";
 shutdown clone immediate;
 startup clone nomount;
}
executing Memory Script

database dismounted
Oracle instance shut down

connected to auxiliary database (not started)
Oracle instance started

Total System Global Area     313860096 bytes

Fixed Size                     1336232 bytes
Variable Size                209718360 bytes
Database Buffers              96468992 bytes
Redo Buffers                   6336512 bytes

sql statement: alter system set  db_name =  ''CLON'' comment= ''Reset to original value by RMAN'' scope=spfile

sql statement: alter system reset  db_unique_name scope=spfile

Oracle instance shut down

connected to auxiliary database (not started)
Oracle instance started

Total System Global Area     313860096 bytes

Fixed Size                     1336232 bytes
Variable Size                209718360 bytes
Database Buffers              96468992 bytes
Redo Buffers                   6336512 bytes
sql statement: CREATE CONTROLFILE REUSE SET DATABASE "CLON" RESETLOGS ARCHIVELOG
 MAXLOGFILES     26
 MAXLOGMEMBERS      5
 MAXDATAFILES      100
 MAXINSTANCES     1
 MAXLOGHISTORY      292
 LOGFILE
 GROUP  1 ( '/home/oracle/clon/log_g1m1.rdo' ) SIZE 100 M  REUSE,
 GROUP  2 ( '/home/oracle/clon/log_g2m1.rdo' ) SIZE 100 M  REUSE
 DATAFILE
 '/home/oracle/clon/system01.dbf'
 CHARACTER SET WE8ISO8859P1


contents of Memory Script:
{
 set newname for tempfile  1 to
 "/home/oracle/clon/temp01.dbt";
 switch clone tempfile all;
 catalog clone datafilecopy  "/home/oracle/clon/sysaux01.dbf",
 "/home/oracle/clon/undotbs01.dbf",
 "/home/oracle/clon/users01.dbf";
 switch clone datafile all;
}
executing Memory Script

executing command: SET NEWNAME

renamed tempfile 1 to /home/oracle/clon/temp01.dbt in control file

cataloged datafile copy
datafile copy file name=/home/oracle/clon/sysaux01.dbf RECID=1 STAMP=738350787
cataloged datafile copy
datafile copy file name=/home/oracle/clon/undotbs01.dbf RECID=2 STAMP=738350787
cataloged datafile copy
datafile copy file name=/home/oracle/clon/users01.dbf RECID=3 STAMP=738350787

datafile 2 switched to datafile copy
input datafile copy RECID=1 STAMP=738350787 file name=/home/oracle/clon/sysaux01.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=2 STAMP=738350787 file name=/home/oracle/clon/undotbs01.dbf
datafile 4 switched to datafile copy
input datafile copy RECID=3 STAMP=738350787 file name=/home/oracle/clon/users01.dbf

contents of Memory Script:
{
 Alter clone database open resetlogs;
}
executing Memory Script

database opened
Finished Duplicate Db at 21-DEC-10

So this was pretty much the same as with an ordinary Clone from a Production Database – as expected. Now we turn the Snapshot Standby back into Physical Standby, thereby losing all the changes that might be done there by using Flashback Database internally. But the Clone Database will remain, now being an independent Database with another Name and DBID than the Primary resp. Snapshot Standby.

DGMGRL> convert database physt to physical standby;
Converting database "physt" to a Physical Standby database, please wait...
Operation requires shutdown of instance "physt" on database "physt"
Shutting down instance "physt"...
Database closed.
Database dismounted.
ORACLE instance shut down.
Operation requires startup of instance "physt" on database "physt"
Starting instance "physt"...
ORACLE instance started.
Database mounted.
Continuing to convert database "physt" ...
Operation requires shutdown of instance "physt" on database "physt"
Shutting down instance "physt"...
ORA-01109: database not open

Database dismounted.
ORACLE instance shut down.
Operation requires startup of instance "physt" on database "physt"
Starting instance "physt"...
ORACLE instance started.
Database mounted.
Database "physt" converted successfully
DGMGRL> show configuration

Configuration - myconf

 Protection Mode: MaxPerformance
 Databases:
 prima - Primary database
 physt - Physical standby database
 
Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS
SQL> connect sys/oracle@clon as sysdba
Connected.
SQL> select name,dbid,database_role from v$database;

NAME            DBID DATABASE_ROLE
--------- ---------- ----------------
CLON      2202079427 PRIMARY

SQL> connect sys/oracle@physt as sysdba
Connected.
SQL> select name,dbid,database_role from v$database;

NAME            DBID DATABASE_ROLE
--------- ---------- ----------------
PRIMA     1970640575 PHYSICAL STANDBY

SQL> connect sys/oracle@prima as sysdba
Connected.
SQL> select name,dbid,database_role from v$database;

NAME            DBID DATABASE_ROLE
--------- ---------- ----------------
PRIMA     1970640575 PRIMARY

Conclusion: With 11g, we can very comfortably transform our Physical Standby into a fully changeable Snapshot Standby, clone that modified Database into an autonomous Database to keep the changes and afterwards transform the Snapshot Standby back into Physical Standby. During the whole action, Redo Protocol from the Primary Database can still be received – but not applied – on the Standby Site.