Activating physical standby (cont)

There are two ways to activate a physical standby database: failover (activate it to become a primary) and snapshot standby. We’ll see later that snapshot standby also leverages guaranteed restore point therefore essentially the two methods are the same.

In addition, switchover will not change the current database incarnation while failover and snapshot standby do.

Failover
— standby database

alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';
set lines 200 pages 200

SQL> select flashback_on from v$database;

FLASHBACK_ON
------------------
YES

select * from v$flashback_database_log;

OLDEST_FLASHBACK_SCN OLDEST_FLASHBACK_TI RETENTION_TARGET FLASHBACK_SIZE ESTIMATED_FLASHBACK_SIZE
-------------------- ------------------- ---------------- -------------- ------------------------
321672 2016-05-05 10:49:08 1440 419430400 0
select resetlogs_id, resetlogs_change#, RESETLOGS_TIME, prior_resetlogs_change#, prior_incarnation#, PRIOR_RESETLOGS_TIME
from v$database_incarnation
where status='CURRENT';

RESETLOGS_ID RESETLOGS_CHANGE# RESETLOGS_TIME PRIOR_RESETLOGS_CHANGE# PRIOR_INCARNATION# PRIOR_RESETLOGS_TIM
------------ ----------------- ------------------- ----------------------- ------------------ -------------------
910974308 1 2016-05-04 16:25:08 0 0

select sysdate from dual;

SYSDATE
-------------------
2016-05-05 11:00:23

SQL> alter database recover managed standby database cancel;

Database altered.

SQL> alter database activate standby database;

Database altered.

SQL> alter database open;

Database altered.

SQL> select controlfile_type, flashback_on, open_mode, DATABASE_ROLE from v$database;

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

select incarnation#, resetlogs_id, resetlogs_change#, prior_resetlogs_change#, prior_incarnation#
from v$database_incarnation
where status='CURRENT';

INCARNATION# RESETLOGS_ID RESETLOGS_CHANGE# PRIOR_RESETLOGS_CHANGE# PRIOR_INCARNATION#
------------ ------------ ----------------- ----------------------- ------------------
2 911041244 322799 1 1

select timestamp_to_scn(to_date('2016-05-05 11:00:23','yyyy-mm-dd hh24:mi:ss')) from dual;

TIMESTAMP_TO_SCN(TO_DATE('2016-05-0511:00:23','YYYY-MM-DDHH24:MI:SS'))
----------------------------------------------------------------------
322155

— primary

SQL> alter system switch logfile;

System altered.


SQL> alter system set log_archive_dest_state_2=defer scope=memory;

System altered.

— standby

SQL> startup mount force;
ORACLE instance started.

Total System Global Area 1.7103E+10 bytes
Fixed Size 2270360 bytes
Variable Size 2382367592 bytes
Database Buffers 1.4697E+10 bytes
Redo Buffers 21684224 bytes
Database mounted.
flashback database to timestamp to_date('2016-05-05 11:00:23','yyyy-mm-dd hh24:mi:ss');

flashback database to timestamp to_date('2016-05-05 11:00:23','yyyy-mm-dd hh24:mi:ss')
*
ERROR at line 1:
ORA-38770: FLASHBACK DATABASE failed during recovery.
ORA-00279: change 321673 generated at 05/05/2016 10:49:09 needed for thread 1
ORA-00289: suggestion : /oraarch/1_150_910974308.arc
ORA-00280: change 321673 for thread 1 is in sequence #150

At this time, we can see that log 150 was not complete on standby server and we need to copy it from the primary server manually.

-rw-r----- 1 oracle oinstall 1654784 May 5 11:00 1_150_910974308.arc

scp 150 archived log to standby :

-rw-r—– 1 oracle oinstall 1696256 May 5 11:29 1_150_910974308.arc

SQL> flashback database to timestamp to_date('2016-05-05 11:00:23','yyyy-mm-dd hh24:mi:ss');

Flashback complete.

— alert.log:

Thu May 05 11:31:43 2016
flashback database to timestamp to_date('2016-05-05 11:00:23','yyyy-mm-dd hh24:mi:ss')
Flashback Restore Start
Flashback Restore Complete
Flashback Media Recovery Start
Thu May 05 11:31:43 2016
Setting recovery target incarnation to 1  <--- temporarily set incarnation to 1
started logmerger process
Parallel Media Recovery started with 24 slaves
Flashback Media Recovery Log /oraarch/1_150_910974308.arc
Thu May 05 11:31:43 2016
Incomplete Recovery applied until change 322673 time 05/05/2016 11:00:24
Flashback Media Recovery Complete
Setting recovery target incarnation to 2 <--- database incarnation remains 2
Completed: flashback database to timestamp to_date('2016-05-05 11:00:23','yyyy-mm-dd hh24:mi:ss')
select incarnation#, resetlogs_id, resetlogs_change#, RESETLOGS_TIME, prior_resetlogs_change#, prior_incarnation#, PRIOR_RESETLOGS_TIME
from v$database_incarnation
where status='CURRENT';

INCARNATION# RESETLOGS_ID RESETLOGS_CHANGE# RESETLOGS_TIME PRIOR_RESETLOGS_CHANGE# PRIOR_INCARNATION# PRIOR_RESETLOGS_TIM
------------ ------------ ----------------- ------------------- ----------------------- ------------------ -------------------
2 911041244 322799 2016-05-05 11:00:44 1 1 2016-05-04 16:25:08

SQL> alter database convert to physical standby;  <-- incarnation changes to 1

Database altered.

SQL>
SQL> select status from v$instance;

STATUS
------------
STARTED

SQL> startup mount force;
ORACLE instance started.

Total System Global Area 1.7103E+10 bytes
Fixed Size 2270360 bytes
Variable Size 2382367592 bytes
Database Buffers 1.4697E+10 bytes
Redo Buffers 21684224 bytes
Database mounted.
SQL>
SQL> select incarnation#, resetlogs_id, resetlogs_change#, RESETLOGS_TIME, prior_resetlogs_change#, prior_incarnation#, PRIOR_RESETLOGS_TIME
from v$database_incarnation
where status='CURRENT';

INCARNATION# RESETLOGS_ID RESETLOGS_CHANGE# RESETLOGS_TIME PRIOR_RESETLOGS_CHANGE# PRIOR_INCARNATION# PRIOR_RESETLOGS_TIM
------------ ------------ ----------------- ------------------- ----------------------- ------------------ -------------------
1 910974308 1 2016-05-04 16:25:08 0 0
SQL> alter database recover managed standby database using current logfile disconnect from session;

Database altered.

Activating Snapshot standby

— standby

alter database recover managed standby database cancel;
alter database convert to snapshot standby;

— in alert:

alter database convert to snapshot standby
Created guaranteed restore point SNAPSHOT_STANDBY_REQUIRED_05/05/2016 14:33:51
Killing 4 processes with pids 8394,8382,8384,8386 (all RFS) in order to disallow current and future RFS connections. Requested by OS process 8566
Begin: Standby Redo Logfile archival
End: Standby Redo Logfile archival
RESETLOGS after complete recovery through change 337009
Resetting resetlogs activation ID 4104253604 (0xf4a1f0a4)
Online log /oradata/ecologystd/redo01a.log: Thread 1 Group 1 was previously cleared
Online log /oraarch/ecologystd/redo01b.log: Thread 1 Group 1 was previously cleared
Online log /oradata/ecologystd/redo02a.log: Thread 1 Group 2 was previously cleared
Online log /oraarch/ecologystd/redo02b.log: Thread 1 Group 2 was previously cleared
Online log /oradata/ecologystd/redo03a.log: Thread 1 Group 3 was previously cleared
Online log /oraarch/ecologystd/redo04a.log: Thread 1 Group 3 was previously cleared
Online log /oradata/ecologystd/redo04a.log: Thread 1 Group 4 was previously cleared
Online log /oraarch/ecologystd/redo04b.log: Thread 1 Group 4 was previously cleared
Online log /oradata/ecologystd/redo05a.log: Thread 1 Group 5 was previously cleared
Online log /oraarch/ecologystd/redo05b.log: Thread 1 Group 5 was previously cleared
Standby became primary SCN: 337007
Thu May 05 14:33:53 2016
Setting recovery target incarnation to 4  <--- database incarnation changed
CONVERT TO SNAPSHOT STANDBY: Complete - Database mounted as snapshot standby
Completed: alter database convert to snapshot standby
SQL> select incarnation#, resetlogs_id, resetlogs_change#, RESETLOGS_TIME, prior_resetlogs_change#, prior_incarnation#, PRIOR_RESETLOGS_TIME
from v$database_incarnation
where status='CURRENT';

INCARNATION# RESETLOGS_ID RESETLOGS_CHANGE# RESETLOGS_TIME PRIOR_RESETLOGS_CHANGE# PRIOR_INCARNATION# PRIOR_RESETLOGS_TIM
------------ ------------ ----------------- ------------------- ----------------------- ------------------ -------------------
4 911054033 337010 2016-05-05 14:33:53 1 1 2016-05-04 16:25:08

SQL> alter database open;

Database altered.

SQL> select controlfile_type, flashback_on, open_mode, DATABASE_ROLE from v$database;

CONTROL FLASHBACK_ON OPEN_MODE DATABASE_ROLE
------- ------------------ -------------------- ----------------
CURRENT YES READ WRITE SNAPSHOT STANDBY

— Convert it back to physical standby

SQL> startup mount force;

ORACLE instance started.

Total System Global Area 1.7103E+10 bytes
Fixed Size 2270360 bytes
Variable Size 2382367592 bytes
Database Buffers 1.4697E+10 bytes
Redo Buffers 21684224 bytes
Database mounted.

SQL> alter database convert to physical standby;

Database altered.

— in alert:

Thu May 05 14:39:37 2016
alter database convert to physical standby
ALTER DATABASE CONVERT TO PHYSICAL STANDBY (ecologystd)
Flashback Restore Start
Flashback Restore Complete
Drop guaranteed restore point
Guaranteed restore point dropped
Clearing standby activation ID 4104304418 (0xf4a2b722)
The primary database controlfile was created using the
'MAXLOGFILES 16' clause.
There is space for up to 11 standby redo logfiles
Use the following SQL commands on the standby database to create
standby redo logfiles that match the primary database:
ALTER DATABASE ADD STANDBY LOGFILE 'srl1.f' SIZE 209715200;
ALTER DATABASE ADD STANDBY LOGFILE 'srl2.f' SIZE 209715200;
ALTER DATABASE ADD STANDBY LOGFILE 'srl3.f' SIZE 209715200;
ALTER DATABASE ADD STANDBY LOGFILE 'srl4.f' SIZE 209715200;
ALTER DATABASE ADD STANDBY LOGFILE 'srl5.f' SIZE 209715200;
ALTER DATABASE ADD STANDBY LOGFILE 'srl6.f' SIZE 209715200;
Shutting down archive processes
Archiving is disabled
Thu May 05 14:39:37 2016
Thu May 05 14:39:37 2016
ARCH shutting downARCH shutting down

Thu May 05 14:39:37 2016
ARCH shutting down
Thu May 05 14:39:37 2016
ARCH shutting down
ARC3: Archival stoppedARC2: Archival stopped
ARC0: Archival stopped

ARC1: Archival stopped
Completed: alter database convert to physical standby
SQL> startup mount force; <---- At this time, database incarnation has been changed to 1

ORACLE instance started.

Total System Global Area 1.7103E+10 bytes
Fixed Size 2270360 bytes
Variable Size 2382367592 bytes
Database Buffers 1.4697E+10 bytes
Redo Buffers 21684224 bytes
Database mounted.

SQL> alter database recover managed standby database using current logfile disconnect from session;

Reference:

Creating Standby Database With Enterprise Manager Failing (Doc ID 1400482.1)

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s