Oracle-Data-Guard之物理备库切换

发表于 | 分类于 
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
#on the primary database(0.0.0.188)
SQL> select open_mode, database_role, switchover_status from v$database;
OPEN_MODE            DATABASE_ROLE    SWITCHOVER_STATUS
-------------------- ---------------- --------------------
READ WRITE           PRIMARY          TO STANDBY
SQL> alter database commit to switchover to standby;
Database altered.
SQL> select open_mode, database_role, switchover_status from v$database;
OPEN_MODE            DATABASE_ROLE    SWITCHOVER_STATUS
-------------------- ---------------- --------------------
READ WRITE           PHYSICAL STANDBY RECOVERY NEEDED
SQL> shutdown immediate;
ORA-01092: ORACLE instance terminated. Disconnection forced
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning option
[oracle@hzvscmdb db]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.2.0 Production on Fri Mar 24 01:15:49 2017
Copyright (c) 1982, 2010, Oracle.  All rights reserved.
Connected to an idle instance.
SQL> startup nomount;
ORACLE instance started.
Total System Global Area 6664212480 bytes
Fixed Size                  2239072 bytes
Variable Size            4328523168 bytes
Database Buffers         2315255808 bytes
Redo Buffers               18194432 bytes
SQL> alter database mount standby database;
Database altered.
SQL> alter database recover managed standby database disconnect from session;
Database altered.
SQL> select open_mode, database_role, switchover_status from v$database;
OPEN_MODE            DATABASE_ROLE    SWITCHOVER_STATUS
-------------------- ---------------- --------------------
MOUNTED              PHYSICAL STANDBY TO PRIMARY
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
#on the standby database(0.0.0.189)
SQL> select open_mode, database_role, switchover_status from v$database;
OPEN_MODE            DATABASE_ROLE    SWITCHOVER_STATUS
-------------------- ---------------- --------------------
MOUNTED              PHYSICAL STANDBY NOT ALLOWED
SQL> alter database commit to switchover to primary;
alter database commit to switchover to primary
*
ERROR at line 1:
ORA-16139: media recovery required
SQL> alter database recover managed standby database disconnect from session;
Database altered.
SQL> alter database commit to switchover to primary;
Database altered.
SQL> select open_mode, database_role, switchover_status from v$database;
OPEN_MODE            DATABASE_ROLE    SWITCHOVER_STATUS
-------------------- ---------------- --------------------
MOUNTED              PRIMARY          NOT ALLOWED
SQL> shutdown immediate;
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.
SQL> startup;
ORACLE instance started.
Total System Global Area 6664212480 bytes
Fixed Size                  2239072 bytes
Variable Size            4328523168 bytes
Database Buffers         2315255808 bytes
Redo Buffers               18194432 bytes
Database mounted.
Database opened.
SQL> select open_mode, database_role, switchover_status from v$database;
OPEN_MODE            DATABASE_ROLE    SWITCHOVER_STATUS
-------------------- ---------------- --------------------
READ WRITE           PRIMARY          RESOLVABLE GAP
SQL> select open_mode, database_role, switchover_status from v$database;
OPEN_MODE            DATABASE_ROLE    SWITCHOVER_STATUS
-------------------- ---------------- --------------------
READ WRITE           PRIMARY          TO STANDBY
###Test the data from on the primary database(0.0.0.189) to the standby database (0.0.0.188)
SQL> select count(*) from mp.tlog;
  COUNT(*)
----------
  13000002
--模拟插入数据
SQL> select SEQUENCE#, APPLIED from v$archived_log;
 SEQUENCE# APPLIED
---------- ---------
      3070 YES
      3070 NO
SQL> select count(*) from mp.tlog;
  COUNT(*)
----------
  14000002
##check data on the standby database (0.0.0.188)      
SQL> alter database recover managed standby database cancel;
Database altered.
SQL> alter database open;
Database altered.
SQL> select open_mode, database_role, switchover_status from v$database;
OPEN_MODE            DATABASE_ROLE    SWITCHOVER_STATUS
-------------------- ---------------- --------------------
READ ONLY            PHYSICAL STANDBY NOT ALLOWED
SQL> select count(*) from mp.tlog;
  COUNT(*)
----------
  13000002
SQL> select SEQUENCE#, APPLIED from v$archived_log where SEQUENCE# > 3060;
 SEQUENCE# APPLIED
---------- ---------
      3061 YES
      3062 YES
      3063 YES
      3064 YES
      3065 YES
      3066 YES
      3067 YES
      3068 YES
      3069 YES
      3070 YES
--发现新的数据没有被应用。此时在 the primary database(0.0.0.189)
SQL> alter system switch logfile;
System altered.
-- 此时查看the standby database (0.0.0.188)
SQL> select count(*) from mp.tlog;
  COUNT(*)
----------
  14000002