Category Archives: FlashBack

DGMGRL: Reinstating ex-Primary to Standby by FlashBack Database feature…

From time to time every of us meets with hardware crashes. That’s why creating disaster recovery configurations by DataGuard is very useful for every DB regardless of its size. But the size of DB defines the tool with which DBA must recreate primary DB and DR configuration.

If DB is quite small — simple cold copy or RMAN duplicate is good enough. But in case of huge warehouses or datamarts this way is too long. That’s why I recommend the following solution: reinstate DR by Flashback Database feature.

For this example I need 2 DBs, configured by DataGuard Manager, something I’ve made earlier in this article.

Work Plan:

  1. Simulate «disaster» at PRM;
  2. Failover to STB;
  3. Convert failed PRM into standby database;
  4. Reinstate PRM in DataGuard configuration;
  5. Switchover to PRM.

1. Simulate «disaster» at PRM:

SQL> select sequence#,archived from v$log;
 SEQUENCE# ARC
---------- ---
         4 YES
         5 NO
         3 YES
SQL> create table test01 as select * from v$log;
Table created.
SQL> shutdown abort;
ORACLE instance shut down.

2. Connect to dgmgrl@STB and failover to STB:

C:\Tools\Oracle\product\11.2.0\db\database>set ORACLE_SID=STB
C:\Tools\Oracle\product\11.2.0\db\database>dgmgrl sys/manager
DGMGRL for 64-bit Windows: Version 11.2.0.1.0 - 64bit Production
Copyright (c) 2000, 2009, Oracle. All rights reserved.
Welcome to DGMGRL, type "help" for information.
Connected.
DGMGRL> show configuration
Configuration - dr
Protection Mode: MaxAvailability
 Databases:
 prm - Primary database
 stb - Physical standby database
Fast-Start Failover: DISABLED
Configuration Status:
ORA-12514: TNS:listener does not currently know of service requested in connect descriptor
ORA-16625: cannot reach database "prm"
DGM-17017: unable to determine configuration status
DGMGRL> failover to STB;
Performing failover NOW, please wait...
Failover succeeded, new primary is "stb"

3. Convert failed PRM into standby database:

Here we need to determine SCN, when STB DB has became primary:

SQL> select STANDBY_BECAME_PRIMARY_SCN from v$database;
 STANDBY_BECAME_PRIMARY_SCN
--------------------------
                   1378375

After getting SCN, we need to mount PRM and execute:

C:\Tools\Oracle\product\11.2.0\db\database>set ORACLE_SID=PRM

C:\Tools\Oracle\product\11.2.0\db\database>sqlplus sys/manager as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Tue Feb 14 13:55:35 2012

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

Connected to an idle instance.

SQL> startup mount
ORACLE instance started.
Total System Global Area  810053632 bytes
Fixed Size                  2180104 bytes
Variable Size             532679672 bytes
Database Buffers          268435456 bytes
Redo Buffers                6758400 bytes
Database mounted.
SQL> FLASHBACK DATABASE TO SCN 1378375;
Flashback complete.
SQL> ALTER DATABASE CONVERT TO PHYSICAL STANDBY;

Database altered.
SQL> SHUTDOWN IMMEDIATE;

ORA-01507: database not mounted
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.
Total System Global Area  810053632 bytes
Fixed Size                  2180104 bytes
Variable Size             532679672 bytes
Database Buffers          268435456 bytes
Redo Buffers                6758400 bytes
Database mounted.

And now we have at PRM:

SQL> select database_role,open_mode from v$database;
DATABASE_ROLE OPEN_MODE
---------------- --------------------
PHYSICAL STANDBY MOUNTED

4. Reinstate PRM in DataGuard configuration;

Connect to DataGuard at STB (as it’s configuration is only valid) and run:

DGMGRL> show configuration
Configuration - dr
  Protection Mode: MaxAvailability
  Databases:
    stb - Primary database
      Warning: ORA-16629: database reports a different protection level from the protection mode
    prm - Physical standby database (disabled)
      ORA-16661: the standby database needs to be reinstated
Fast-Start Failover: DISABLED
Configuration Status:
WARNING
DGMGRL> reinstate database prm;

Reinstating database "prm", please wait...
Reinstatement of database "prm" succeeded
DGMGRL> show configuration
Configuration - dr
  Protection Mode: MaxAvailability
  Databases:
    stb - Primary database
      Warning: ORA-16629: database reports a different protection level from the protection mode
    prm - Physical standby database
Fast-Start Failover: DISABLED
Configuration Status:
WARNING

5. Switchover to PRM. At last we are ready to switchover back to PRM :

 
C:\Tools\Oracle\product\11.2.0\db\database>dgmgrl sys/manager@stb
DGMGRL for 64-bit Windows: Version 11.2.0.1.0 - 64bit Production
Copyright (c) 2000, 2009, Oracle. All rights reserved.
Welcome to DGMGRL, type "help" for information.
Connected.
DGMGRL> switchover to PRM;
Performing switchover NOW, please wait...
New primary database "prm" is opening...
Operation requires shutdown of instance "stb" on database "stb"
Shutting down instance "stb"...
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.
Operation requires startup of instance "stb" on database "stb"
Starting instance "stb"...
Unable to connect to database
ORA-12514: TNS:listener does not currently know of service requested in connect descriptor Failed.
Warning: You are no longer connected to ORACLE.
Please complete the following steps to finish switchover:
        start up and mount instance "stb" of database "stb"

Starting STB as standby DB:

Connected to an idle instance.
SQL> startup nomount;
ORACLE instance started.
Total System Global Area 810053632 bytes
Fixed Size 2180104 bytes
Variable Size 478153720 bytes
Database Buffers 322961408 bytes
Redo Buffers 6758400 bytes
SQL> alter database mount standby database;
Database altered.

And at last:

C:\Tools\Oracle\product\11.2.0\db\database>dgmgrl sys/manager
DGMGRL for 64-bit Windows: Version 11.2.0.1.0 - 64bit Production
Copyright (c) 2000, 2009, Oracle. All rights reserved.
Welcome to DGMGRL, type "help" for information.
Connected.
DGMGRL> show configuration
Configuration - dr
Protection Mode: MaxAvailability
 Databases:
 prm - Primary database
 stb - Physical standby database
Fast-Start Failover: DISABLED
Configuration Status:
SUCCESS

At the end of article all instances are fine, PRM is primary back and all work could be made by users is safe.

Here Oracle Docs I used to write this note.

Enjoy your safety!

%d такие блоггеры, как: