Step by Step to Switchover on Oracle Database 11G
This article shows the Step of switchover from Physical standby to primary database on Oracle Database 11G.
Mention
|
Step
|
Step 1
Check the archive and role database on both sites (Primary and Standby)
On Primary
SQL> set line 900
SQL> set pagesi 900
SQL> select DBID,NAME,LOG_MODE,OPEN_MODE,SWITCHOVER#,DATABASE_ROLE,SWITCHOVER_STATUS,DATAGUARD_BROKER,GUARD_STATUS,DB_UNIQUE_NAME from v$database;
DBID NAME LOG_MODE OPEN_MODE SWITCHOVER# DATABASE_ROLE SWITCHOVER_STATUS DATAGUAR GUARD_S DB_UNIQUE_NAME
---------- --------- ------------ -------------------- ----------- ---------------- -------------------- -------- ------- ------------------------------
468941731 DB11G ARCHIVELOG READ WRITE 468920739 PRIMARY TO STANDBY DISABLED NONE DB11G
SQL> select max(sequence#) from v$archived_log where applied='YES';
MAX(SEQUENCE#)
--------------
49
On Standby
SQL> set line 900
SQL> set pagesi 900
SQL> select DBID,NAME,LOG_MODE,OPEN_MODE,SWITCHOVER#,DATABASE_ROLE,SWITCHOVER_STATUS,DATAGUARD_BROKER,GUARD_STATUS,DB_UNIQUE_NAME from v$database;
DBID NAME LOG_MODE OPEN_MODE SWITCHOVER# DATABASE_ROLE SWITCHOVER_STATUS DATAGUAR GUARD_S DB_UNIQUE_NAME
---------- --------- ------------ -------------------- ----------- ---------------- -------------------- -------- ------- ------------------------------
468941731 DB11G ARCHIVELOG MOUNTED 468920739 PHYSICAL STANDBY NOT ALLOWED DISABLED NONE DB11G_STDBY
SQL> select max(sequence#) from v$archived_log where applied='YES';
MAX(SEQUENCE#)
--------------
49
Step 2
Starting Switchover
On Primary
SQL> ALTER DATABASE COMMIT TO SWITCHOVER 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 1068937216 bytes
Fixed Size 2220200 bytes
Variable Size 851447640 bytes
Database Buffers 209715200 bytes
Redo Buffers 5554176 bytes
Database mounted.
SQL> select DBID,NAME,LOG_MODE,OPEN_MODE,SWITCHOVER#,DATABASE_ROLE,SWITCHOVER_STATUS,DATAGUARD_BROKER,GUARD_STATUS,DB_UNIQUE_NAME from v$database;
DBID NAME LOG_MODE OPEN_MODE SWITCHOVER# DATABASE_ROLE SWITCHOVER_STATUS DATAGUAR GUARD_S DB_UNIQUE_NAME
---------- --------- ------------ -------------------- ----------- ---------------- -------------------- -------- ------- ------------------------------
468941731 DB11G ARCHIVELOG MOUNTED 0 PHYSICAL STANDBY TO PRIMARY DISABLED NONE DB11G
SQL> alter database recover managed standby database disconnect from session;
Database altered.
On Physical Standby
SQL> select DBID,NAME,LOG_MODE,OPEN_MODE,SWITCHOVER#,DATABASE_ROLE,SWITCHOVER_STATUS,DATAGUARD_BROKER,GUARD_STATUS,DB_UNIQUE_NAME from v$database;
DBID NAME LOG_MODE OPEN_MODE SWITCHOVER# DATABASE_ROLE SWITCHOVER_STATUS DATAGUAR GUARD_S DB_UNIQUE_NAME
---------- --------- ------------ -------------------- ----------- ---------------- -------------------- -------- ------- ------------------------------
468941731 DB11G ARCHIVELOG MOUNTED 0 PHYSICAL STANDBY TO PRIMARY DISABLED NONE DB11G_STDBY
SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY;
Database altered.
SQL>
SQL> shutdown immediate;
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.
SQL> startup;
ORACLE instance started.
Total System Global Area 217157632 bytes
Fixed Size 2211928 bytes
Variable Size 159387560 bytes
Database Buffers 50331648 bytes
Redo Buffers 5226496 bytes
Database mounted.
Database opened.
SQL> select DBID,NAME,LOG_MODE,OPEN_MODE,SWITCHOVER#,DATABASE_ROLE,SWITCHOVER_STATUS,DATAGUARD_BROKER,GUARD_STATUS,DB_UNIQUE_NAME from v$database;
DBID NAME LOG_MODE OPEN_MODE SWITCHOVER# DATABASE_ROLE SWITCHOVER_STATUS DATAGUAR GUARD_S DB_UNIQUE_NAME
---------- --------- ------------ -------------------- ----------- ---------------- -------------------- -------- ------- ------------------------------
468941731 DB11G ARCHIVELOG READ WRITE 472535662 PRIMARY RESOLVABLE GAP DISABLED NONE DB11G_STDBY
SQL>
SQL>
SQL>
Step 3
Test a little bit of Transaction on the New Primary
On New Primary
SQL>
SQL> create user isuphisara identified by "isuphisara";
User created.
SQL> grant dba,resource,connect,create session to isuphisara;
Grant succeeded.
SQL> select owner,table_name from dba_tables where owner='SCOTT';
OWNER TABLE_NAME
------------------------------ ------------------------------
SCOTT DEPT
SCOTT EMP
SCOTT BONUS
SCOTT SALGRADE
SQL> create table isuphisara.dept as select * from scott.dept;
Table created.
SQL> create table isuphisara.EMP as select * from scott.dept;
Table created.
SQL> select * from isuphisara.dept;
DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
SQL> desc isuphisara.dept
Name Null? Type
------------------------------------------------------------------------
DEPTNO NUMBER(2)
DNAME VARCHAR2(14)
LOC VARCHAR2(13)
SQL> insert into isuphisara.dept(deptno,dname,loc) values('00','ABCD','BKK');
1 row created.
SQL> commit;
Commit complete.
SQL> alter system switch logfile;
System altered.
SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /home/oracle/arch
Oldest online log sequence 55
Next log sequence to archive 57
Current log sequence 57
SQL> alter system checkpoint;
System altered.
SQL> select max(sequence#) from v$archived_log where applied='YES';
MAX(SEQUENCE#)
--------------
57
Status
Primary switch to standby
standby switch to primary
Step 4
Switchover back to the normal
On New Primary
SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /home/oracle/arch
Oldest online log sequence 55
Next log sequence to archive 57
Current log sequence 57
SQL> alter system checkpoint;
System altered.
SQL> select max(sequence#) from v$archived_log where applied='YES';
MAX(SEQUENCE#)
--------------
57
SQL> select DBID,NAME,LOG_MODE,OPEN_MODE,SWITCHOVER#,DATABASE_ROLE,SWITCHOVER_STATUS,DATAGUARD_BROKER,GUARD_STATUS,DB_UNIQUE_NAME from v$database;
DBID NAME LOG_MODE OPEN_MODE SWITCHOVER# DATABASE_ROLE SWITCHOVER_STATUS DATAGUAR GUARD_S DB_UNIQUE_NAME
---------- --------- ------------ -------------------- ----------- ---------------- -------------------- -------- ------- ------------------------------
468941731 DB11G ARCHIVELOG READ WRITE 472535662 PRIMARY TO STANDBY DISABLED NONE DB11G_STDBY
SQL> ALTER DATABASE COMMIT TO SWITCHOVER 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 217157632 bytes
Fixed Size 2211928 bytes
Variable Size 159387560 bytes
Database Buffers 50331648 bytes
Redo Buffers 5226496 bytes
Database mounted.
SQL> alter database recover managed standby database disconnect from session;
Database altered.
SQL> select DBID,NAME,LOG_MODE,OPEN_MODE,SWITCHOVER#,DATABASE_ROLE,SWITCHOVER_STATUS,DATAGUARD_BROKER,GUARD_STATUS,DB_UNIQUE_NAME from v$database;
DBID NAME LOG_MODE OPEN_MODE SWITCHOVER# DATABASE_ROLE SWITCHOVER_STATUS DATAGUAR GUARD_S DB_UNIQUE_NAME
---------- --------- ------------ -------------------- ----------- ---------------- -------------------- -------- ------- ------------------------------
468941731 DB11G ARCHIVELOG MOUNTED 472560866 PHYSICAL STANDBY NOT ALLOWED DISABLED NONE DB11G_STDBY
On New standby
SQL> select DBID,NAME,LOG_MODE,OPEN_MODE,SWITCHOVER#,DATABASE_ROLE,SWITCHOVER_STATUS,DATAGUARD_BROKER,GUARD_STATUS,DB_UNIQUE_NAME from v$database;
DBID NAME LOG_MODE OPEN_MODE SWITCHOVER# DATABASE_ROLE SWITCHOVER_STATUS DATAGUAR GUARD_S DB_UNIQUE_NAME
---------- --------- ------------ -------------------- ----------- ---------------- -------------------- -------- ------- ------------------------------
468941731 DB11G ARCHIVELOG MOUNTED 0 PHYSICAL STANDBY TO PRIMARY DISABLED NONE DB11G
SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY;
Database altered.
SQL> shutdown immediate;
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.
SQL> startup;
ORACLE instance started.
Total System Global Area 1068937216 bytes
Fixed Size 2220200 bytes
Variable Size 851447640 bytes
Database Buffers 209715200 bytes
Redo Buffers 5554176 bytes
Database mounted.
Database opened.
SQL> select owner,table_name from dba_tables where owner='ISUPHISARA';
OWNER TABLE_NAME
------------------------------ ------------------------------
ISUPHISARA DEPT
ISUPHISARA EMP
SQL> select * from ISUPHISARA.DEPT;
DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
0 ABCD BKK
Crosscheck
On Primary
SQL> select max(sequence#) from v$archived_log where applied='YES';
MAX(SEQUENCE#)
--------------
63
On Physical Standby
SQL> select max(sequence#) from v$archived_log where applied='YES';
MAX(SEQUENCE#)
--------------
63