Nov-13

Leave a comment Go to comments

14-Nov

Case:  Flashback Primary and standby database and re-instantiate the standby database.

PRIMARY DATABASE: identify the SCN to FLASHBACK


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

NAME      DATABASE_ROLE    OPEN_MODE

--------- ---------------- --------------------

ORACLD    PRIMARY          READ WRITE

SQL> select FLASHBACK_ON from v$database;

FLASHBACK_ON

------------------

YES

THREAD# Last Primary Seq Generated

---------- --------------------------

1                         42

SQL> select current_scn from v$database;

CURRENT_SCN

-----------

9095851

SQL> select min(fhscn) from x$kcvfh;

MIN(FHSCN)

----------------

9095780

STANDBY:  make sure the SCN is applied


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

NAME      DATABASE_ROLE    OPEN_MODE

--------- ---------------- --------------------

ORACLD    PHYSICAL STANDBY READ ONLY WITH APPLY

THREAD# Last Standby Seq Applied

---------- ------------------------

1                       42

SQL> select current_scn from v$database;

CURRENT_SCN

-----------

9095779

SQL> select min(fhscn) from x$kcvfh;

MIN(FHSCN)

----------------

9095780

PRIMARY:  Do some more transactions


SQL> create user test_flash_wo_rebuild identified by test_flash_wo_rebuild;

User created.

SQL> grant connect , resource to test_flash_wo_rebuild;

Grant succeeded.

SQL> create table test_flash_wo_rebuild.TEST as select * from xxx.test;

Table created.

SQL> create table test_flash_wo_rebuild.TEST as select * from xxxx.abc;

Table created.

SQL> create table test_flash_wo_rebuild.AB as  select * from xxxx.ab;

Table created.

SQL> select min(fhscn) from x$kcvfh;

MIN(FHSCN)

----------------

9095780

SQL> alter system switch logfile;

System altered.

SQL> select min(fhscn) from x$kcvfh;

MIN(FHSCN)

----------------

9095780

SQL> select min(fhscn) from x$kcvfh;

MIN(FHSCN)

----------------

9095780

SQL> alter system switch logfile;

System altered.

SQL> alter system switch logfile;

System altered.

SQL> alter system switch logfile;

System altered.

SQL> alter system switch logfile;

System altered.

SQL> select min(fhscn) from x$kcvfh;

MIN(FHSCN)

----------------

9096326

SQL> shut immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup mount

ORACLE instance started.

 

Total System Global Area 2355015680 bytes

Fixed Size                  2230592 bytes

Variable Size            1157629632 bytes

Database Buffers         1107296256 bytes

Redo Buffers               87859200 bytes

Database mounted.

SQL>  flashback database TO SCN 9095779;

Flashback complete.

SQL> alter database open;

alter database open

*

ERROR at line 1:

ORA-01589: must use RESETLOGS or NORESETLOGS option for database open

SQL> alter database open resetlogs;

Database altered.

SQL> SQL> archive log list

Database log mode              Archive Mode

Automatic archival             Enabled

Archive destination            /n01/oradata1/arch

Oldest online log sequence     1

Next log sequence to archive   2

Current log sequence           2

SQL> SELECT TO_CHAR(RESETLOGS_CHANGE# - 2) FROM V$DATABASE;

TO_CHAR(RESETLOGS_CHANGE#-2)

----------------------------------------

9095779

SQL> SELECT TO_CHAR(CURRENT_SCN) FROM V$DATABASE;

TO_CHAR(CURRENT_SCN)

----------------------------------------

9096024

SQL> alter system switch logfile;

System altered.

SQL> /

System altered.

SQL> select thread#, max(sequence#) "Last Primary Seq Generated"

from v$archived_log val, v$database vdb

where val.resetlogs_change# = vdb.resetlogs_change#

group by thread# order by 1;  2    3    4

THREAD# Last Primary Seq Generated

---------- --------------------------

1                          3

SQL> select * from test_flash_wo_rebuild.TEST;

select * from test_flash_wo_rebuild.TEST

*

ERROR at line 1:

ORA-00942: table or view does not exist

From standby alert log – MRP failed


Archived Log entry 40 added for thread 1 sequence 1 ID 0x62a013c0 dest 1:

Thu Nov 14 05:39:32 2013

MRP0: Incarnation has changed! Retry recovery...

Errors in file /n01/oraadmin1/diag/rdbms/oraclds/oraclds/trace/oraclds_pr00_17768.trc:

ORA-19906: recovery target incarnation changed during recovery

Recovery interrupted!

Thu Nov 14 05:39:32 2013

started logmerger process

Thu Nov 14 05:39:32 2013

Managed Standby Recovery not using Real Time Apply

Warning: Recovery target destination is in a sibling branch

of the controlfile checkpoint. Recovery will only recover

changes to datafiles.

Datafile 1 (ckpscn 9096811) is orphaned on incarnation#=4

MRP0: Detected orphaned datafiles!

Recovery will possibly be retried after flashback...

Errors in file /n01/oraadmin1/diag/rdbms/oraclds/oraclds/trace/oraclds_pr00_4276.trc:

ORA-19909: datafile 1 belongs to an orphan incarnation

ORA-01110: data file 1: '/n01/oradata1/oraclds/system01.dbf'

Slave exiting with ORA-19909 exception

Errors in file /n01/oraadmin1/diag/rdbms/oraclds/oraclds/trace/oraclds_pr00_4276.trc:

ORA-19909: datafile 1 belongs to an orphan incarnation

ORA-01110: data file 1: '/n01/oradata1/oraclds/system01.dbf'

Recovery Slave PR00 previously exited with exception 19909

Thu Nov 14 05:39:52 2013

MRP0: Background Media Recovery process shutdown (oraclds)

STANDBY:  Falshback the standby and convert to recovery mode


SQL> FLASHBACK STANDBY DATABASE TO SCN 9095779;

Flashback complete.

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT;

Database altered.

SQL> select thread#, max(sequence#) "Last Standby Seq Applied"

from v$archived_log val, v$database vdb

where val.resetlogs_change# = vdb.resetlogs_change#

and val.applied='YES'

group by thread# order by 1;  2    3    4    5

THREAD# Last Standby Seq Applied

---------- ------------------------

1              3

From alert log


FLASHBACK STANDBY DATABASE TO SCN 9095779

CLOSE: killing server sessions.

CLOSE: all sessions shutdown successfully.

Thu Nov 14 05:41:57 2013

SMON: disabling cache recovery

Flashback Restore Start

Flashback Restore Complete

Flashback Media Recovery Start

Thu Nov 14 05:41:59 2013

Setting recovery target incarnation to 4

started logmerger process

Parallel Media Recovery started with 2 slaves

Flashback Media Recovery Log /n01/oradata1/archs/1_38_830173886.dbf

Flashback Media Recovery Log /n01/oradata1/archs/1_39_830173886.dbf

Thu Nov 14 05:42:10 2013

Flashback Media Recovery Log /n01/oradata1/archs/1_40_830173886.dbf

Flashback Media Recovery Log /n01/oradata1/archs/1_41_830173886.dbf

Flashback Media Recovery Log /n01/oradata1/archs/1_42_830173886.dbf

Thu Nov 14 05:42:10 2013

Incomplete Recovery applied until change 9095780 time 11/14/2013 05:24:09

Flashback Media Recovery Complete

Thu Nov 14 05:42:10 2013

Setting recovery target incarnation to 5

Completed: FLASHBACK STANDBY DATABASE TO SCN 9095779

Thu Nov 14 05:43:17 2013

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT

Attempt to start background Managed Standby Recovery process (oraclds)

Thu Nov 14 05:43:17 2013

MRP0 started with pid=27, OS id=4401

MRP0: Background Managed Standby Recovery process started (oraclds)

started logmerger process

Thu Nov 14 05:43:22 2013

Managed Standby Recovery not using Real Time Apply

Parallel Media Recovery started with 2 slaves

Waiting for all non-current ORLs to be archived...

All non-current ORLs have been archived.

Media Recovery Log /n01/oradata1/archs/1_1_831447520.dbf

Completed: ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT

Media Recovery Waiting for thread 1 sequence 2 (in transit)

Thu Nov 14 05:43:45 2013

Standby controlfile consistent with primary

RFS[5]: Selected log 8 for thread 1 sequence 3 dbid 1643240053 branch 831447520

Thu Nov 14 05:43:46 2013

Archived Log entry 41 added for thread 1 sequence 2 ID 0x62a013c0 dest 1:

Standby controlfile consistent with primary

RFS[5]: Selected log 9 for thread 1 sequence 4 dbid 1643240053 branch 831447520

RFS[5]: Waiting for instance close to complete

Thu Nov 14 05:43:47 2013

Archived Log entry 42 added for thread 1 sequence 3 ID 0x62a013c0 dest 1:

Thu Nov 14 05:43:48 2013

Media Recovery Log /n01/oradata1/archs/1_2_831447520.dbf

Media Recovery Log /n01/oradata1/archs/1_3_831447520.dbf

Media Recovery Waiting for thread 1 sequence 4 (in transit)

Standby is back in recovery mode after Flashback to a SCN same as primary.

  1. No comments yet.
  1. No trackbacks yet.

Leave a comment