Flashback database on a RAC database with rac standby setup

-

This has been tested in a 8 Node RAC  with 8 node Standby RAC database.


1.    Verify if flashback and force_logging database is enabled on both primary and standby.


Primary:

 select flashback_on,force_logging  from v$database;


[RACFDP1]oracle@primarydb1.ora.com$ sqlplus /nolog

SQL*Plus: Release 11.2.0.1.0 Production on Fri Oct 14 10:23:58 2011

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

SQL> conn / as sysdba
Connected.
SQL>  select flashback_on,force_logging  from v$database;

FLASHBACK_ON       FOR
------------------ ---
YES                YES

Stanbdy:

select flashback_on,force_logging  from v$database;


[RACFDP1]oracle@primarydb1.ora.com$ sqlplus /nolog

SQL*Plus: Release 11.2.0.1.0 Production on Fri Oct 14 10:23:58 2011

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

SQL> conn / as sysdba
Connected.
SQL>  select flashback_on,force_logging  from v$database;

FLASHBACK_ON       FOR
------------------ ---
YES                YES

SQL>


2.    Verify if DG configuration is fine through dgmgrl

If any issue with any of the configuration, pelase fix the issue before create restore point or the release

DGMGRL> show configuration
DGMGRL> show database RACFDP
DGMGRL> show database RACFDP_sb

DGMGRL> show configuration

Configuration - RACFDP_dataguard

  Protection Mode: MaxAvailability
  Databases:
    RACFDP    - Primary database
    RACFDP_sb - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS

DGMGRL> show database RACFDP

Database - RACFDP

  Role:            PRIMARY
  Intended State:  TRANSPORT-ON
  Instance(s):
    RACFDP1
    RACFDP2
    RACFDP3
    RACFDP4
    RACFDP5
    RACFDP6
    RACFDP7
    RACFDP8

Database Status:
SUCCESS

DGMGRL> show database RACFDP_sb

Database - RACFDP_sb

  Role:            PHYSICAL STANDBY
  Intended State:  APPLY-ON
  Transport Lag:   0 seconds
  Apply Lag:       0 seconds
  Real Time Query: OFF
  Instance(s):
    RACFDP_SB1
    RACFDP_SB2
    RACFDP_SB3
    RACFDP_SB4
    RACFDP_SB5 (apply instance)
    RACFDP_SB6
    RACFDP_SB7
    RACFDP_SB8

Database Status:
SUCCESS


3.    Create restore points on Both primary and stanbdy


3.1   First create restore point in the standby database. To create restore point in the standby we need to disable the log apply and create the restore point and then enable back the log shipping

3.1.1 Disable log apply using dgmgrl

edit database RACFDP_sb  SET STATE='APPLY-OFF';


DGMGRL> edit database RACFDP_sb  SET STATE='APPLY-OFF';
Succeeded.
DGMGRL>
DGMGRL> show database RACFDP_sb

Database - RACFDP_sb

  Role:            PHYSICAL STANDBY
  Intended State:  APPLY-OFF
  Transport Lag:   0 seconds
  Apply Lag:       0 seconds
  Real Time Query: OFF
  Instance(s):
    RACFDP_SB1
    RACFDP_SB2
    RACFDP_SB3
    RACFDP_SB4
    RACFDP_SB5 (apply instance)
    RACFDP_SB6
    RACFDP_SB7
    RACFDP_SB8

Database Status:
SUCCESS

DGMGRL> show configuration

Configuration - RACFDP_dataguard

  Protection Mode: MaxAvailability
  Databases:
    RACFDP    - Primary database
    RACFDP_sb - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS

3.1.2 Create restore point:

CREATE RESTORE POINT FLASHBACK_TEST1 GUARANTEE FLASHBACK DATABASE;

[RACFDP_SB1]oracle@standbydb1.ora.com$ sqlplus /nolog

SQL*Plus: Release 11.2.0.1.0 Production on Fri Oct 14 10:39:43 2011

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

SQL> conn / as sysdba
Connected.
SQL> CREATE RESTORE POINT FLASHBACK_TEST1 GUARANTEE FLASHBACK DATABASE;

Restore point created.

SQL>
SQL> select current_scn from v$database;

CURRENT_SCN
-----------
   21015113


3.1.3 Enable log apply on standby server

edit database RACFDP_sb  SET STATE='APPLY-ON';

DGMGRL> edit database RACFDP_sb  SET STATE='APPLY-ON';
Succeeded.
DGMGRL> show database RACFDP_sb

Database - RACFDP_sb

  Role:            PHYSICAL STANDBY
  Intended State:  APPLY-ON
  Transport Lag:   0 seconds
  Apply Lag:       0 seconds
  Real Time Query: OFF
  Instance(s):
    RACFDP_SB1
    RACFDP_SB2
    RACFDP_SB3
    RACFDP_SB4
    RACFDP_SB5 (apply instance)
    RACFDP_SB6
    RACFDP_SB7
    RACFDP_SB8

Database Status:
SUCCESS

DGMGRL> show configuration

Configuration - RACFDP_dataguard

  Protection Mode: MaxAvailability
  Databases:
    RACFDP    - Primary database
    RACFDP_sb - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS



3.2   Now   create restore point in the primary  database.

CREATE RESTORE POINT FLASHBACK_TEST1 GUARANTEE FLASHBACK DATABASE;

 [RACFDP1]oracle@primarydb1.ora.com$ sqlplus /nolog

SQL*Plus: Release 11.2.0.1.0 Production on Fri Oct 14 10:48:24 2011

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

SQL> conn / as sysdba
Connected.
SQL> CREATE RESTORE POINT FLASHBACK_TEST1 GUARANTEE FLASHBACK DATABASE;

Restore point created.

SQL>



3.3 verify if restore points are created from the primary ( node1)

    SQL> select INST_ID,name from gv$restore_point;

INST_ID NAME
------- ------------------------------
      1 FLASHBACK_TEST1
      2 FLASHBACK_TEST1
      6 FLASHBACK_TEST1
      3 FLASHBACK_TEST1
      5 FLASHBACK_TEST1
      8 FLASHBACK_TEST1
      7 FLASHBACK_TEST1
      4 FLASHBACK_TEST1

8 rows selected.



4.    Do the release – run @ primary database

  The below scripts are just for testing purpose. We need to run the actual scripts

 4A.  Create user ,tables,insert,update and deletes

                [RACFDP1]oracle@primarydb1.ora.com$ sqlplus /nolog

SQL*Plus: Release 11.2.0.1.0 Production on Tue Oct 11 07:51:17 2011

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

      SQL> conn / as sysdba
      Connected.
      SQL> create user flashback_test identified by flashback_test;

      User created.

      SQL> grant dba to flashback_test;

      Grant succeeded.

      SQL> grant create session to flashback_test;

      Grant succeeded.



      SQL> conn flashback_test/flashback_test
      Connected.
     
      SQL> create table test1 as select * from dba_objects;

      Table created.

      SQL> insert into test1 values ( select * from dba_objects);
      insert into test1 values ( select * from dba_objects)
                           *
      ERROR at line 1:
      ORA-00936: missing expression

      SQL> insert into test1 ( select * from dba_objects);

      13717 rows created.

      SQL> insert into test1 ( select * from dba_objects);

      13717 rows created.

      SQL> insert into test1 ( select * from dba_objects);

      13717 rows created.

        SQL> update test1 set object_name='null';

      54868 rows updated.

      SQL> rollback;

      Rollback complete.

      SQL> update test1 set object_name='null';

      13717 rows updated.

      SQL> commit;

      Commit complete.

      SQL> delete from test1;

      13717 rows deleted.

      SQL> rollback;

      Rollback complete.

      SQL> truncate table test1;

      Table truncated.

      SQL> exit




5.    Flashback the primary and stanbdy databases


5. Flashback the primary database

5.1 Convert the DG mode to Maxperformance

EDIT CONFIGURATION SET PROTECTION MODE AS MAXPERFORMANCE

DGMGRL> EDIT CONFIGURATION SET PROTECTION MODE AS MAXPERFORMANCE;
Succeeded.
DGMGRL> show configuration

Configuration - RACFDP_dataguard

  Protection Mode: MaxPerformance
  Databases:
    RACFDP    - Primary database
    RACFDP_sb - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS

DGMGRL>

  

5.1 Disable log transport from primary, we need to convert the database to maxperformance to make

edit database RACFDP set state='TRANSPORT-OFF';

DGMGRL> edit database RACFDP set state='TRANSPORT-OFF';
Succeeded.
DGMGRL> show configuration

Configuration - RACFDP_dataguard

  Protection Mode: MaxPerformance
  Databases:
    RACFDP    - Primary database
    RACFDP_sb - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS

DGMGRL>


5.3 Switch the logfiles from all primary instances
     alter system switch logfile ( on all primary instances)

5.4  Disable log apply on stanbdy database
EDIT DATABASE  RACFDP_sb SET STATE='APPLY-OFF';

DGMGRL> EDIT DATABASE  RACFDP_sb SET STATE='APPLY-OFF';
Succeeded.
DGMGRL> show database RACFDP_sb

Database - RACFDP_sb

  Role:            PHYSICAL STANDBY
  Intended State:  APPLY-OFF
  Transport Lag:   0 seconds
  Apply Lag:       3 minutes 23 seconds
  Real Time Query: OFF
  Instance(s):
    RACFDP_SB1
    RACFDP_SB2
    RACFDP_SB3
    RACFDP_SB4
    RACFDP_SB5 (apply instance)
    RACFDP_SB6
    RACFDP_SB7
    RACFDP_SB8

Database Status:
SUCCESS  


5.5  stop both primary and standby database.

   [RACFDP1]oracle@primarydb1.ora.com$ srvctl stop database -d RACFDP

[RACFDP1]oracle@primarydb1.ora.com$ srvctl stop database -d RACFDP
[RACFDP1]oracle@primarydb1.ora.com$ srvctl status database -d RACFDP
Instance RACFDP1 is not running on node primarydb1
Instance RACFDP2 is not running on node primarydb2
Instance RACFDP3 is not running on node primarydb3
Instance RACFDP4 is not running on node primarydb4
Instance RACFDP5 is not running on node primarydb5
Instance RACFDP6 is not running on node primarydb6
Instance RACFDP7 is not running on node primarydb7
Instance RACFDP8 is not running on node primarydb8
[RACFDP1]oracle@primarydb1.ora.com$


   [RACFDP_SB1]oracle@standbydb1.ora.com$ srvctl stop database -d RACFDP_SB

[RACFDP_SB1]oracle@standbydb1.ora.com$ srvctl stop database -d RACFDP_SB
[RACFDP_SB1]oracle@standbydb1.ora.com$ srvctl status database -d RACFDP_SB
Instance RACFDP_SB1 is not running on node standbydb1
Instance RACFDP_SB2 is not running on node standbydb2
Instance RACFDP_SB3 is not running on node standbydb3
Instance RACFDP_SB4 is not running on node standbydb4
Instance RACFDP_SB5 is not running on node standbydb5
Instance RACFDP_SB6 is not running on node standbydb6
Instance RACFDP_SB7 is not running on node standbydb7
Instance RACFDP_SB8 is not running on node standbydb8

5.6. Start the primary in mount mode from node1 (primarydb1.uk.db.com)

[RACFDP1]oracle@primarydb1.ora.com$ sqlplus /nolog

SQL*Plus: Release 11.2.0.1.0 Production on Fri Oct 14 11:35:37 2011

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

SQL> conn / as sysdba
Connected to an idle instance.
SQL> startup mount
ORACLE instance started.

Total System Global Area 1.6034E+10 bytes
Fixed Size                  2229184 bytes
Variable Size            1.0402E+10 bytes
Database Buffers         5469372416 bytes
Redo Buffers              160735232 bytes
Database mounted.



5.7  Start the standby in mount mode from node1(standbydb1.uk.db.com)

[RACFDP_SB1]oracle@standbydb1.ora.com$ sqlplus /nolog

SQL*Plus: Release 11.2.0.1.0 Production on Fri Oct 14 11:35:26 2011

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

SQL> conn / as sysdba
Connected to an idle instance.
SQL> startup mount
ORACLE instance started.

Total System Global Area 1.6034E+10 bytes
Fixed Size                  2229184 bytes
Variable Size            1.0805E+10 bytes
Database Buffers         5066719232 bytes
Redo Buffers              160735232 bytes
Database mounted.
SQL>

5.8  Flashback primary database

flashback database to restore point FLASHBACK_TEST1;

SQL> flashback database to restore point FLASHBACK_TEST1;

Flashback complete.

SQL> select current_scn from v$database;

CURRENT_SCN
-----------
          0

SQL>


5.9  open primary with resetlogs ( from node1 primarydb1.uk.db.com). It may sometime as it has to resetlogs all the redolog in all the threads
               
 alter database open resetlogs;

SQL> alter database open resetlogs;

Database altered.

SQL>

5.10. flashback standby database from node1 (standbydb1.uk.db.com)
     
   flashback database to restore point FLASHBACK_TEST1;

SQL>  flashback database to restore point FLASHBACK_TEST1;

Flashback complete.

SQL>
SQL> select current_scn from v$database;

CURRENT_SCN
-----------
   21015113


5.11. Shutdown the primary database from node1 (primarydb1.uk.db.com)

[RACFDP1]oracle@primarydb1.uk.db.com

SQL> SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>

5.11. Shutdown the standby from node1 (standbydb1.uk.db.com)

   SQL> shutdown immediate;
                ORA-01109: database not open
                Database dismounted.
                ORACLE instance shut down.
                SQL> exit


5.12 start both primary and standby databases
     srvctl start database -d RACFDP
     srvctl start database -d RACFDP_SB



[RACFDP1]oracle@primarydb1.ora.com$ srvctl start database -d RACFDP
[RACFDP1]oracle@primarydb1.ora.com$ srvctl status database -d RACFDP
Instance RACFDP1 is running on node primarydb1
Instance RACFDP2 is running on node primarydb2
Instance RACFDP3 is running on node primarydb3
Instance RACFDP4 is running on node primarydb4
Instance RACFDP5 is running on node primarydb5
Instance RACFDP6 is running on node primarydb6
Instance RACFDP7 is running on node primarydb7
Instance RACFDP8 is running on node primarydb8

[RACFDP_SB1]oracle@standbydb1.ora.com$      srvctl start database -d RACFDP_SB
[RACFDP_SB1]oracle@standbydb1.ora.com$ srvctl status database -d RACFDP_SB
Instance RACFDP_SB1 is running on node standbydb1
Instance RACFDP_SB2 is running on node standbydb2
Instance RACFDP_SB3 is running on node standbydb3
Instance RACFDP_SB4 is running on node standbydb4
Instance RACFDP_SB5 is running on node standbydb5
Instance RACFDP_SB6 is running on node standbydb6
Instance RACFDP_SB7 is running on node standbydb7
Instance RACFDP_SB8 is running on node standbydb8


5.13. check the status of both primary and stanbdy using crsctl to find if instance status in proper mode.
      Primary:
                ora.RACFDP.db
                              1        ONLINE  ONLINE       primarydb1              Open
                              2        ONLINE  ONLINE       primarydb2              Open
                              3        ONLINE  ONLINE       primarydb3              Open
                              4        ONLINE  ONLINE       primarydb4              Open
                              5        ONLINE  ONLINE       primarydb5              Open
                              6        ONLINE  ONLINE       primarydb6              Open
                              7        ONLINE  ONLINE       primarydb7              Open
                              8        ONLINE  ONLINE       primarydb8              Open
   
       
      Standby:
                ora.RACFDP_sb.db
                      1        ONLINE  INTERMEDIATE standbydb1              Mounted (Closed)
                      2        ONLINE  INTERMEDIATE standbydb2              Mounted (Closed)
                      3        ONLINE  INTERMEDIATE standbydb3              Mounted (Closed)
                      4        ONLINE  INTERMEDIATE standbydb4              Mounted (Closed)
                      5        ONLINE  INTERMEDIATE standbydb5              Mounted (Closed)
                      6        ONLINE  INTERMEDIATE standbydb6              Mounted (Closed)
                      7        ONLINE  INTERMEDIATE standbydb7              Mounted (Closed)
                      8        ONLINE  INTERMEDIATE standbydb8              Mounted (Closed)



6.    Drop the Restore points


6.1  Drop the restore points from Primary database ( Node1 - primarydb1.ora.com)

drop restore point FLASHBACK_TEST1;

[RACFDP1]oracle@primarydb1.ora.com$ sqlplus /nolog

SQL*Plus: Release 11.2.0.1.0 Production on Fri Oct 14 12:07:54 2011

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

SQL> conn / as sysdba
Connected.
SQL> drop restore point FLASHBACK_TEST1;

Restore point dropped.

SQL>



6.2 Drop the restore points from standby database ( Node1 - standbydb1.ora.com)
  Please note if the log apply is enabled we need to disable it first to drop the restore point from standby. Since it has been disabled.so we are ignoring that step in this stage


drop restore point FLASHBACK_TEST1;

[RACFDP_SB1]oracle@standbydb1.ora.com$ sqlplus /nolog

SQL*Plus: Release 11.2.0.1.0 Production on Fri Oct 14 12:11:08 2011

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

SQL> conn / as sysdba
Connected.
SQL> drop restore point FLASHBACK_TEST1;

Restore point dropped.





7.    Post Flashback activities 


7.1 Verify current configuration

show configuration
show database RACFDP
show database RACFDP_sb


DGMGRL> show configuration

Configuration - RACFDP_dataguard

  Protection Mode: MaxPerformance
  Databases:
    RACFDP    - Primary database
    RACFDP_sb - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS

DGMGRL> show database RACFDP

Database - RACFDP

  Role:            PRIMARY
  Intended State:  TRANSPORT-OFF
  Instance(s):
    RACFDP1
    RACFDP2
    RACFDP3
    RACFDP4
    RACFDP5
    RACFDP6
    RACFDP7
    RACFDP8

Database Status:
SUCCESS

DGMGRL> show database RACFDP_sb

Database - RACFDP_sb

  Role:            PHYSICAL STANDBY
  Intended State:  APPLY-OFF
  Transport Lag:   (unknown)
  Apply Lag:       (unknown)
  Real Time Query: OFF
  Instance(s):
    RACFDP_SB1 (apply instance)
    RACFDP_SB2
    RACFDP_SB3
    RACFDP_SB4
    RACFDP_SB5
    RACFDP_SB6
    RACFDP_SB7
    RACFDP_SB8

Database Status:
SUCCESS


7.2  Enable the log transport from primary node1
  edit database RACFDP set state = 'TRANSPORT-ON';

DGMGRL>     edit database RACFDP set state = 'TRANSPORT-ON';
Succeeded.
DGMGRL> exit

7.3  Switch logfile

 alter system switch logfile ( on all the primary instances)

7.4  Enable the log apply from node1 standby

edit database RACFDP_sb set state='APPLY-ON';

DGMGRL> edit database RACFDP_sb set state='APPLY-ON';
Succeeded.

7.5. Ensure the preferred instances

edit database 'RACFDP_sb' set property PreferredApplyInstance='RACFDP_sb5';
edit database 'RACFDP' set property PreferredApplyInstance='RACFDP5';
EDIT DATABASE RACFDP_sb SET STATE='ONLINE' WITH APPLY INSTANCE=RACFDP_SB5;
show database RACFDP_sb PreferredApplyInstance
show database RACFDP PreferredApplyInstance




DGMGRL> edit database 'RACFDP_sb' set property PreferredApplyInstance='RACFDP_sb5';
Property "preferredapplyinstance" updated
DGMGRL> edit database 'RACFDP' set property PreferredApplyInstance='RACFDP5';
Property "preferredapplyinstance" updated
DGMGRL> EDIT DATABASE RACFDP_sb SET STATE='ONLINE' WITH APPLY INSTANCE=RACFDP_SB5;
Succeeded.
DGMGRL> show database RACFDP_sb PreferredApplyInstance
  PreferredApplyInstance = 'RACFDP_sb5'
DGMGRL> show database RACFDP PreferredApplyInstance
  PreferredApplyInstance = 'RACFDP5'



7.6 Enable MAXAVAILABILITY Protection Mode
 EDIT CONFIGURATION SET PROTECTION MODE AS MAXAVAILABILITY;

DGMGRL>  EDIT CONFIGURATION SET PROTECTION MODE AS MAXAVAILABILITY;
Succeeded.

7.7 Verify configuration through dgmgrl

show configuration
show database RACFDP
show database RACFDP_sb


DGMGRL> show configuration

Configuration - RACFDP_dataguard

  Protection Mode: MaxPerformance
  Databases:
    RACFDP    - Primary database
    RACFDP_sb - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS

DGMGRL> show database RACFDP

Database - RACFDP

  Role:            PRIMARY
  Intended State:  TRANSPORT-ON
  Instance(s):
    RACFDP1
    RACFDP2
    RACFDP3
    RACFDP4
    RACFDP5
    RACFDP6
    RACFDP7
    RACFDP8

Database Status:
SUCCESS

DGMGRL> show database RACFDP_sb

Database - RACFDP_sb

  Role:            PHYSICAL STANDBY
  Intended State:  APPLY-ON
  Transport Lag:   0 seconds
  Apply Lag:       0 seconds
  Real Time Query: OFF
  Instance(s):
    RACFDP_SB1
    RACFDP_SB2
    RACFDP_SB3
    RACFDP_SB4
    RACFDP_SB5 (apply instance)
    RACFDP_SB6
    RACFDP_SB7
    RACFDP_SB8

Database Status:
SUCCESS



DBAs can follow the below steps if they want to confirm that standby database flashback is working in a reda-only mode . Thi is not part of the actual restore steps.

A.Create a test table  with current date as value  -  Before restore point

    Do this before the  restore point created
 
Create a test table to test if the flashback is working  as expected. Infact this is not correct method of testing this feature.

At the primary database – From Node 1 (primarydb1.uk.db.com)

SQL> create table test_flashback ( dt date );

Table created.

SQL> insert into test_flashback values(sysdate);

1 row created.

SQL> commit;

Commit complete.



B.Verify the test_flashback table on both primary and standby – Before restore point/


Please note that to access the application tables in standby database we need to convert the standby database into read-only mode . We need to bounce the standby database to keep the standby in mount mode otherwise the database  will be in “real time query on”  state

B.1 Primary:

SQL> alter session set nls_date_format='dd/mm/yyyy hh24:mi:ss';

Session altered.

SQL> select * from test_flashback;

DT
-------------------
11/10/2011 06:57:05

B.2 Stanbdy


B.2.1 Set read-only

DGMGRL> edit database RACFDP_sb set state='read-only';
Succeeded.

B.2.2 Query the test_flashback table

 [RACFDP_SB1]oracle@standbydb1.ora.com$ sqlplus  /nolog

SQL*Plus: Release 11.2.0.1.0 Production on Tue Oct 11 07:22:16 2011

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

SQL> conn / as sysdba
Connected.
SQL> alter session set nls_date_format='dd/mm/yyyy hh24:mi:ss';

Session altered.

SQL> select * from test_flashback;

DT
-------------------
11/10/2011 06:57:05

SQL>

B.2.3 Set APPLY-ON

DGMGRL> edit database RACFDP_sb  SET STATE='APPLY-ON';
Succeeded.
DGMGRL>

B.2.4. bounce the standby database to bring into mount mode
srvctl stop database  -d  RACFDP_SB1
srvctl start database –d RACFDP_SB1


C.Update test_flasback table during the release

 Update the test_flashback table  - run @ primary

       SQL> conn / as sysdba
       Connected.
      

       SQL> update test_flashback set dt=sysdate;

       1 row updated.

       SQL> commit;

       Commit complete.

       SQL> alter session set nls_date_format='dd/mm/yyyy hh24:mi:ss';

       Session altered.

       SQL> select * from test_flashback;

       DT
       -------------------
       11/10/2011 07:56:16



D.Verify the test_flashback table on both primary and standby – After flashback


D.1 verify the test_flashback table from primary;

SQL> alter session set nls_date_format='dd/mm/yyyy hh24:mi:ss';

Session altered.

SQL> select * from test_flashback;

DT
-------------------
11/10/2011 06:57:05

D.2 Verify the test_flashback table from stanbdy

Set read-only

DGMGRL> edit database RACFDP_sb set state='read-only';
Succeeded.

Query the table

[RACFDP_SB1]oracle@standbydb1.ora.com$ sqlplus /nolog

SQL*Plus: Release 11.2.0.1.0 Production on Tue Oct 11 09:43:03 2011

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

SQL> conn / as sysdba
Connected.

SQL> select * from test_flashback;

DT
---------
11-OCT-11

SQL> alter session set nls_date_format='dd/mm/yyyy hh24:mi:ss';

Session altered.

SQL> select * from test_flashback;

DT
-------------------
11/10/2011 06:57:05

Set the Apply-ON

DGMGRL> edit database 'RACFDP_sb' set state='APPLY-ON';
Succeeded.
DGMGRL> exit

Bounce the standby database to bring into mount mode

srvctl stop database  -d  RACFDP_SB1
srvctl start database –d RACFDP_SB1

Comments

Post a Comment

Popular posts from this blog

Step by Step: How to troubleshoot a slow running query in Oracle

-
This is a day to day task of a DBA. Someone from application team comes to our desk and simply says a query is running slow. We may question him on many things but ultimately a DBA has to tune the query at the end. This is also very popular and known question for the DBA's who are attending interviews. This question is simply asked again and again. I have also been asked this question multiple times. After a pause, interviewer simply asks...so suppose a user comes and reports that his query is running slow, what will be your approach to tune this. There is no absolute or concrete answer to this question because there might be multiple way to tune a slow running query. Everyone can have a different approach. So here is my approach: Very first thing we have to find which type of query is running slow. Whether it is SELECT query of DML statements(insert, update, delete and merge). So first we will see how to tune a SELECT query Step 1 – Find the SQL_ID of the
Read more

Register Archive log file manually in standby Database

-
  Some time, the archive logs are not shipping to standby location due to network issue.  There may be  several  archive logs are waiting in standby location for one archive log file. For instance, Archive log sequence # 100 is not shipped to standby location. But other archive logs(sequence#101 to sequence#200) were shipped to standby and it is waiting to ship the sequence# 100.  In this scenario, DBA can copy the archive log file and register manually. Once manually registered, the sequence#101 to sequence#200 will apply automatically on standby database. Step 1 Let us assume, the archive log file devdb12_1_283_801988635.arc is not shipped to standby due to some reason. Copy the archive log file devdb12_1_283_801988635.arc manually from primary to standby archive destination location. Step  2 Run the below command in standby database. ALTER DATABASE REGISTER LOGFILE '/dbArch/oradata/devdb12/ devdb12_1_283_801988635.arc';   Step  3 Verify the standby database and see if the co
Read more

How to check UNDO tablespace usage and who is using more undo

-
Query To check UNDO tablespace usage: ================================ set linesize 152 col tablespace_name for a20 col status for a10 select tablespace_name,status,count(extent_id) "Extent Count", sum(blocks) "Total Blocks",sum(bytes)/(1024*1024*1024) spaceInGB from   dba_undo_extents group by  tablespace_name, status having tablespace_name=upper('&TSNAME') order by tablespace_name; Query To check who is using more UNDO space: ===================================== SET termout ON SET heading ON SET PAGESIZE   6000 SET LINESIZE   200 COLUMN pgm_notes    FORMAT a80        HEADING 'Notes' COLUMN rbs          FORMAT a16         HEADING 'RBS'             JUST center COLUMN oracle_user  FORMAT a12        HEADING 'Oracle|Username' COLUMN sid_serial   FORMAT a12        HEADING 'SID,Serial' COLUMN unix_pid     FORMAT a6         HEADING 'O/S|PID' COLUMN Client_User  FORMAT a14        HEADING 'Cl
Read more