oracledg主、备切换SWITCHOVER全过程记录
[oracle@oraclep trace]$ ifconfig
enp0s3: flags=4163 mtu 1500
inet 10.0.2.15 netmask 255.255.255.0 broadcast 10.0.2.255
inet6 fe80::64d8:a56d:a1af:ef20 prefixlen 64 scopeid 0x20
ether 08:00:27:23:25:0d txqueuelen 1000 (Ethernet)
RX packets 313 bytes 25282 (24.6 KiB)
RX errors 0 dropped 0 overruns 0 frame 0
TX packets 475 bytes 37678 (36.7 KiB)
TX errors 0 dropped 0 overruns 0 carrier 0 collisions 0
enp0s8: flags=4163 mtu 1500
inet 192.168.56.118 netmask 255.255.255.0 broadcast 192.168.56.255
inet6 fe80::283a:c36b:b773:c4d8 prefixlen 64 scopeid 0x20
inet6 fe80::3765:e61f:d653:f584 prefixlen 64 scopeid 0x20
ether 08:00:27:ae:62:fc txqueuelen 1000 (Ethernet)
RX packets 992188 bytes 1473325892 (1.3 GiB)
RX errors 0 dropped 0 overruns 0 frame 0
TX packets 47580 bytes 30037450 (28.6 MiB)
TX errors 0 dropped 0 overruns 0 carrier 0 collisions 0
lo: flags=73 mtu 65536
inet 127.0.0.1 netmask 255.0.0.0
inet6 ::1 prefixlen 128 scopeid 0x10
loop txqueuelen 1000 (Local Loopback)
RX packets 13897 bytes 1135717 (1.0 MiB)
RX errors 0 dropped 0 overruns 0 frame 0
TX packets 13897 bytes 1135717 (1.0 MiB)
TX errors 0 dropped 0 overruns 0 carrier 0 collisions 0
[oracle@oraclep trace]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.3.0 Production on Thu Jul 4 13:39:43 2019
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> select switchover_status,database_role from v$database;
SWITCHOVER_STATUS DATABASE_ROLE
-------------------- ----------------
TO STANDBY PRIMARY
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@oraclep trace]$ ssh 192.168.56.117
oracle@192.168.56.117's password:
Last login: Thu Jul 4 11:42:57 2019
[oracle@oracles ~]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.3.0 Production on Thu Jul 4 13:45:47 2019
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> select switchover_status,database_role from v$database;
SWITCHOVER_STATUS DATABASE_ROLE
-------------------- ----------------
NOT ALLOWED PHYSICAL STANDBY
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@oracles ~]$ exit
登出
Connection to 192.168.56.117 closed.
[oracle@oraclep trace]$ sqlplus test/test
SQL*Plus: Release 11.2.0.3.0 Production on Thu Jul 4 13:46:02 2019
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> select * from test;
no rows selected
SQL> insert into test values(1,2);
1 row created.
SQL> commit;
Commit complete.
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@oraclep trace]$ ssh 192.168.56.117
oracle@192.168.56.117's password:
Last login: Thu Jul 4 13:45:34 2019 from 192.168.56.118
[oracle@oracles ~]$ sqlplus test/test
SQL*Plus: Release 11.2.0.3.0 Production on Thu Jul 4 13:46:57 2019
Copyright (c) 1982, 2011, Oracle. All rights reserved.
ERROR:
ORA-01033: ORACLE initialization or shutdown in progress
Process ID: 0
Session ID: 0 Serial number: 0
Enter user-name:
ERROR:
ORA-01017: invalid username/password; logon denied
Enter user-name:
ERROR:
ORA-01017: invalid username/password; logon denied
SP2-0157: unable to CONNECT to ORACLE after 3 attempts, exiting SQL*Plus
[oracle@oracles ~]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.3.0 Production on Thu Jul 4 13:47:15 2019
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> alter database recover managed standby database cancel;
Database altered.
SQL> alter database open read only;
Database altered.
SQL> alter database recover managed standby database using current logfile disconnect from session;
Database altered.
SQL> select switchover_status,database_role from v$database;
SWITCHOVER_STATUS DATABASE_ROLE
-------------------- ----------------
NOT ALLOWED PHYSICAL STANDBY
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@oracles ~]$ sqlplus test/test
SQL*Plus: Release 11.2.0.3.0 Production on Thu Jul 4 13:48:18 2019
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> select * from test;
ID NUMS
---------- ----------
1 2
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@oracles ~]$ exit
登出
Connection to 192.168.56.117 closed.
[oracle@oraclep trace]$ sqlplus test/test
SQL*Plus: Release 11.2.0.3.0 Production on Thu Jul 4 13:48:31 2019
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> insert into test values(1,3);
1 row created.
SQL> insert into test values(1,4);
1 row created.
SQL> insert into test values(1,5);
1 row created.
SQL> commit;
Commit complete.
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@oraclep trace]$ ssh 192.168.56.117
oracle@192.168.56.117's password:
Last login: Thu Jul 4 13:46:51 2019 from 192.168.56.118
[oracle@oracles ~]$ sqlplus test/test
SQL*Plus: Release 11.2.0.3.0 Production on Thu Jul 4 13:48:59 2019
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> select * from test;
ID NUMS
---------- ----------
1 3
1 4
1 5
1 2
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@oracles ~]$ exit
登出
Connection to 192.168.56.117 closed.
[oracle@oraclep trace]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.3.0 Production on Thu Jul 4 13:52:31 2019
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> SELECT OPEN_MODE,DATABASE_ROLE,SWITCHOVER_STATUS FROM V$DATABASE;
OPEN_MODE DATABASE_ROLE SWITCHOVER_STATUS
-------------------- ---------------- --------------------
READ WRITE PRIMARY TO STANDBY
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@oraclep trace]$ ssh 192.168.56.117
oracle@192.168.56.117's password:
Last login: Thu Jul 4 13:48:55 2019 from 192.168.56.118
[oracle@oracles ~]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.3.0 Production on Thu Jul 4 13:52:48 2019
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> SELECT OPEN_MODE,DATABASE_ROLE,SWITCHOVER_STATUS FROM V$DATABASE;
OPEN_MODE DATABASE_ROLE SWITCHOVER_STATUS
-------------------- ---------------- --------------------
READ ONLY WITH APPLY PHYSICAL STANDBY NOT ALLOWED
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@oracles ~]$ exit
登出
Connection to 192.168.56.117 closed.
[oracle@oraclep trace]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.3.0 Production on Thu Jul 4 13:53:05 2019
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
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 PHYSICAL STANDBY WITH SESSION SHUTDOWN;
Database altered.
SQL> shutdown immdiate;
SP2-0717: illegal SHUTDOWN option
SQL> shutdown immdiate
SP2-0717: illegal SHUTDOWN option
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@oraclep trace]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.3.0 Production on Thu Jul 4 13:54:09 2019
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> STARTUP MOUNT;
ORA-01081: cannot start already-running ORACLE - shut it down first
SQL> shutdown immediate
ORA-01092: ORACLE instance terminated. Disconnection forced
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@oraclep trace]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.3.0 Production on Thu Jul 4 13:54:40 2019
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to an idle instance.
SQL> STARTUP MOUNT;
ORACLE instance started.
Total System Global Area 768294912 bytes
Fixed Size 2232312 bytes
Variable Size 457179144 bytes
Database Buffers 306184192 bytes
Redo Buffers 2699264 bytes
Database mounted.
SQL> SELECT OPEN_MODE,SWITCHOVER_STATUS FROM V$DATABASE;
OPEN_MODE SWITCHOVER_STATUS
-------------------- --------------------
MOUNTED RECOVERY NEEDED
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@oraclep trace]$ ssh 192.168.56.117
oracle@192.168.56.117's password:
Last login: Thu Jul 4 13:52:44 2019 from 192.168.56.118
[oracle@oracles ~]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.3.0 Production on Thu Jul 4 13:55:14 2019
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> SELECT OPEN_MODE,DATABASE_ROLE,SWITCHOVER_STATUS FROM V$DATABASE;
OPEN_MODE DATABASE_ROLE SWITCHOVER_STATUS
-------------------- ---------------- --------------------
READ ONLY WITH APPLY PHYSICAL STANDBY SESSIONS ACTIVE
SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY WITH SESSION SHUTDOWN;
Database altered.
SQL> ALTER DATABASE OPEN;
Database altered.
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@oracles ~]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.3.0 Production on Thu Jul 4 13:56:13 2019
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT FROM SESSION;
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT FROM SESSION
*
ERROR at line 1:
ORA-01665: control file is not a standby control file
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@oracles ~]$ exit
登出
Connection to 192.168.56.117 closed.
[oracle@oraclep trace]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.3.0 Production on Thu Jul 4 13:56:49 2019
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> SELECT OPEN_MODE,DATABASE_ROLE,SWITCHOVER_STATUS FROM V$DATABASE;
OPEN_MODE DATABASE_ROLE SWITCHOVER_STATUS
-------------------- ---------------- --------------------
MOUNTED PHYSICAL STANDBY RECOVERY NEEDED
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT FROM SESSION;
Database altered.
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 0
Next log sequence to archive 0
Current log sequence 0
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@oraclep trace]$ ssh 192.168.56.117
oracle@192.168.56.117's password:
Last login: Thu Jul 4 13:55:11 2019 from 192.168.56.118
[oracle@oracles ~]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.3.0 Production on Thu Jul 4 13:57:48 2019
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> ALTER SYSTEM SWITCH LOGFILE;
System altered.
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 43
Next log sequence to archive 45
Current log sequence 45
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@oracles ~]$ exit
登出
Connection to 192.168.56.117 closed.
[oracle@oraclep trace]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.3.0 Production on Thu Jul 4 13:58:45 2019
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 44
Next log sequence to archive 0
Current log sequence 45
SQL> alter database recover managed standby database cancel;
Database altered.
SQL> alter database open read only;
Database altered.
SQL> alter database recover managed standby database using current logfile disconnect from session;
Database altered.
SQL> SELECT OPEN_MODE,DATABASE_ROLE,SWITCHOVER_STATUS FROM V$DATABASE;
OPEN_MODE DATABASE_ROLE SWITCHOVER_STATUS
-------------------- ---------------- --------------------
READ ONLY WITH APPLY PHYSICAL STANDBY NOT ALLOWED
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@oraclep trace]$ sqlplus test/test
SQL*Plus: Release 11.2.0.3.0 Production on Thu Jul 4 14:00:07 2019
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> select * from test;
ID NUMS
---------- ----------
1 3
1 4
1 5
1 2
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@oraclep trace]$ ssh 192.168.56.117
oracle@192.168.56.117's password:
Last login: Thu Jul 4 13:57:34 2019 from 192.168.56.118
[oracle@oracles ~]$ sqlplus test/test
SQL*Plus: Release 11.2.0.3.0 Production on Thu Jul 4 14:00:37 2019
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> select * from test;
ID NUMS
---------- ----------
1 3
1 4
1 5
1 2
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@oracles ~]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.3.0 Production on Thu Jul 4 14:01:15 2019
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> SELECT OPEN_MODE,DATABASE_ROLE,SWITCHOVER_STATUS FROM V$DATABASE;
OPEN_MODE DATABASE_ROLE SWITCHOVER_STATUS
-------------------- ---------------- --------------------
READ WRITE PRIMARY TO STANDBY
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@oracles ~]$ sqlplus test/test
SQL*Plus: Release 11.2.0.3.0 Production on Thu Jul 4 14:01:23 2019
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> select * from test;
ID NUMS
---------- ----------
1 3
1 4
1 5
1 2
SQL> insert into test select * from test;
4 rows created.
SQL> commit;
Commit complete.
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@oracles ~]$ exit
登出
Connection to 192.168.56.117 closed.
[oracle@oraclep trace]$ sqlplus test/test
SQL*Plus: Release 11.2.0.3.0 Production on Thu Jul 4 14:01:54 2019
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> select * from test;
ID NUMS
---------- ----------
1 3
1 4
1 5
1 2
1 3
1 4
1 5
1 2
8 rows selected.
至此，Switchover切换完成！
总结：Switchover为主、备之间的正常切换，切换前要保证主、备库的数据一致，而且要先主切备，后备切主，避免同时存在两个主库。 网站题目：oracledg主、备切换SWITCHOVER全过程记录
文章地址：http://cdiso.cn/article/iedppj.html
