千家信息网

oracle dg 主、备切换SWITCHOVER 全过程记录

发表于:2025-01-21 作者:千家信息网编辑
千家信息网最后更新 2025年01月21日,[oracle@oraclep trace]$ ifconfig enp0s3: flags=4163 mtu 1500 inet 10.0.2.15 netmask 255.255
千家信息网最后更新 2025年01月21日oracle dg 主、备切换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 0enp0s8: 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 0lo: 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 sysdbaSQL*Plus: Release 11.2.0.3.0 Production on Thu Jul 4 13:39:43 2019Copyright (c) 1982, 2011, Oracle.  All rights reserved.Connected to:Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit ProductionWith the Partitioning, OLAP, Data Mining and Real Application Testing optionsSQL> select switchover_status,database_role from v$database;SWITCHOVER_STATUS    DATABASE_ROLE-------------------- ----------------TO STANDBY           PRIMARYSQL> exitDisconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit ProductionWith the Partitioning, OLAP, Data Mining and Real Application Testing options[oracle@oraclep trace]$ ssh 192.168.56.117oracle@192.168.56.117's password: Last login: Thu Jul  4 11:42:57 2019[oracle@oracles ~]$ sqlplus  / as sysdbaSQL*Plus: Release 11.2.0.3.0 Production on Thu Jul 4 13:45:47 2019Copyright (c) 1982, 2011, Oracle.  All rights reserved.Connected to:Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit ProductionWith the Partitioning, OLAP, Data Mining and Real Application Testing optionsSQL> select switchover_status,database_role from v$database;SWITCHOVER_STATUS    DATABASE_ROLE-------------------- ----------------NOT ALLOWED          PHYSICAL STANDBYSQL> exitDisconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit ProductionWith 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/testSQL*Plus: Release 11.2.0.3.0 Production on Thu Jul 4 13:46:02 2019Copyright (c) 1982, 2011, Oracle.  All rights reserved.Connected to:Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit ProductionWith the Partitioning, OLAP, Data Mining and Real Application Testing optionsSQL> select * from test;no rows selectedSQL> insert into test values(1,2);1 row created.SQL> commit;Commit complete.SQL> exitDisconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit ProductionWith the Partitioning, OLAP, Data Mining and Real Application Testing options[oracle@oraclep trace]$ ssh 192.168.56.117oracle@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 2019Copyright (c) 1982, 2011, Oracle.  All rights reserved.ERROR:ORA-01033: ORACLE initialization or shutdown in progressProcess ID: 0Session ID: 0 Serial number: 0Enter user-name: ERROR:ORA-01017: invalid username/password; logon deniedEnter user-name: ERROR:ORA-01017: invalid username/password; logon deniedSP2-0157: unable to CONNECT to ORACLE after 3 attempts, exiting SQL*Plus[oracle@oracles ~]$ sqlplus  / as sysdbaSQL*Plus: Release 11.2.0.3.0 Production on Thu Jul 4 13:47:15 2019Copyright (c) 1982, 2011, Oracle.  All rights reserved.Connected to:Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit ProductionWith the Partitioning, OLAP, Data Mining and Real Application Testing optionsSQL> 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 STANDBYSQL> exitDisconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit ProductionWith 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 2019Copyright (c) 1982, 2011, Oracle.  All rights reserved.Connected to:Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit ProductionWith the Partitioning, OLAP, Data Mining and Real Application Testing optionsSQL> select * from test;        ID       NUMS---------- ----------        1          2SQL> exitDisconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit ProductionWith 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 2019Copyright (c) 1982, 2011, Oracle.  All rights reserved.Connected to:Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit ProductionWith the Partitioning, OLAP, Data Mining and Real Application Testing optionsSQL> 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> exitDisconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit ProductionWith the Partitioning, OLAP, Data Mining and Real Application Testing options[oracle@oraclep trace]$ ssh 192.168.56.117oracle@192.168.56.117's password: Last login: Thu Jul  4 13:46:51 2019 from 192.168.56.118[oracle@oracles ~]$ sqlplus test/testSQL*Plus: Release 11.2.0.3.0 Production on Thu Jul 4 13:48:59 2019Copyright (c) 1982, 2011, Oracle.  All rights reserved.Connected to:Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit ProductionWith the Partitioning, OLAP, Data Mining and Real Application Testing optionsSQL> select * from test;        ID       NUMS---------- ----------        1          3        1          4        1          5        1          2SQL> exitDisconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit ProductionWith 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 sysdbaSQL*Plus: Release 11.2.0.3.0 Production on Thu Jul 4 13:52:31 2019Copyright (c) 1982, 2011, Oracle.  All rights reserved.Connected to:Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit ProductionWith the Partitioning, OLAP, Data Mining and Real Application Testing optionsSQL> SELECT OPEN_MODE,DATABASE_ROLE,SWITCHOVER_STATUS FROM V$DATABASE;OPEN_MODE            DATABASE_ROLE    SWITCHOVER_STATUS-------------------- ---------------- --------------------READ WRITE           PRIMARY          TO STANDBYSQL> exitDisconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit ProductionWith 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 sysdbaSQL*Plus: Release 11.2.0.3.0 Production on Thu Jul 4 13:52:48 2019Copyright (c) 1982, 2011, Oracle.  All rights reserved.Connected to:Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit ProductionWith the Partitioning, OLAP, Data Mining and Real Application Testing optionsSQL> SELECT OPEN_MODE,DATABASE_ROLE,SWITCHOVER_STATUS FROM V$DATABASE;OPEN_MODE            DATABASE_ROLE    SWITCHOVER_STATUS-------------------- ---------------- --------------------READ ONLY WITH APPLY PHYSICAL STANDBY NOT ALLOWEDSQL> exitDisconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit ProductionWith 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 sysdbaSQL*Plus: Release 11.2.0.3.0 Production on Thu Jul 4 13:53:05 2019Copyright (c) 1982, 2011, Oracle.  All rights reserved.Connected to:Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit ProductionWith the Partitioning, OLAP, Data Mining and Real Application Testing optionsSQL> SELECT OPEN_MODE,DATABASE_ROLE,SWITCHOVER_STATUS FROM V$DATABASE;OPEN_MODE            DATABASE_ROLE    SWITCHOVER_STATUS-------------------- ---------------- --------------------READ WRITE           PRIMARY          TO STANDBYSQL> ALTER DATABASE COMMIT TO SWITCHOVER TO PHYSICAL STANDBY WITH SESSION SHUTDOWN;Database altered.SQL> shutdown immdiate;SP2-0717: illegal SHUTDOWN optionSQL> shutdown immdiateSP2-0717: illegal SHUTDOWN optionSQL> exitDisconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit ProductionWith the Partitioning, OLAP, Data Mining and Real Application Testing options[oracle@oraclep trace]$ sqlplus / as sysdbaSQL*Plus: Release 11.2.0.3.0 Production on Thu Jul 4 13:54:09 2019Copyright (c) 1982, 2011, Oracle.  All rights reserved.Connected to:Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit ProductionWith the Partitioning, OLAP, Data Mining and Real Application Testing optionsSQL> STARTUP MOUNT;ORA-01081: cannot start already-running ORACLE - shut it down firstSQL> shutdown immediateORA-01092: ORACLE instance terminated. Disconnection forcedSQL> exitDisconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit ProductionWith the Partitioning, OLAP, Data Mining and Real Application Testing options[oracle@oraclep trace]$ sqlplus / as sysdbaSQL*Plus: Release 11.2.0.3.0 Production on Thu Jul 4 13:54:40 2019Copyright (c) 1982, 2011, Oracle.  All rights reserved.Connected to an idle instance.SQL> STARTUP MOUNT;ORACLE instance started.Total System Global Area  768294912 bytesFixed Size                  2232312 bytesVariable Size             457179144 bytesDatabase Buffers          306184192 bytesRedo Buffers                2699264 bytesDatabase mounted.SQL> SELECT OPEN_MODE,SWITCHOVER_STATUS FROM V$DATABASE;OPEN_MODE            SWITCHOVER_STATUS-------------------- --------------------MOUNTED              RECOVERY NEEDEDSQL> exitDisconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit ProductionWith 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 sysdbaSQL*Plus: Release 11.2.0.3.0 Production on Thu Jul 4 13:55:14 2019Copyright (c) 1982, 2011, Oracle.  All rights reserved.Connected to:Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit ProductionWith the Partitioning, OLAP, Data Mining and Real Application Testing optionsSQL> SELECT OPEN_MODE,DATABASE_ROLE,SWITCHOVER_STATUS FROM V$DATABASE;OPEN_MODE            DATABASE_ROLE    SWITCHOVER_STATUS-------------------- ---------------- --------------------READ ONLY WITH APPLY PHYSICAL STANDBY SESSIONS ACTIVESQL> ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY WITH SESSION SHUTDOWN;Database altered.SQL> ALTER DATABASE OPEN;Database altered.SQL> exitDisconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit ProductionWith the Partitioning, OLAP, Data Mining and Real Application Testing options[oracle@oracles ~]$ sqlplus  / as sysdbaSQL*Plus: Release 11.2.0.3.0 Production on Thu Jul 4 13:56:13 2019Copyright (c) 1982, 2011, Oracle.  All rights reserved.Connected to:Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit ProductionWith the Partitioning, OLAP, Data Mining and Real Application Testing optionsSQL> 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 fileSQL> exitDisconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit ProductionWith 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 sysdbaSQL*Plus: Release 11.2.0.3.0 Production on Thu Jul 4 13:56:49 2019Copyright (c) 1982, 2011, Oracle.  All rights reserved.Connected to:Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit ProductionWith the Partitioning, OLAP, Data Mining and Real Application Testing optionsSQL> SELECT OPEN_MODE,DATABASE_ROLE,SWITCHOVER_STATUS FROM V$DATABASE;OPEN_MODE            DATABASE_ROLE    SWITCHOVER_STATUS-------------------- ---------------- --------------------MOUNTED              PHYSICAL STANDBY RECOVERY NEEDEDSQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT FROM SESSION;Database altered.SQL> archive log list;Database log mode              Archive ModeAutomatic archival             EnabledArchive destination            USE_DB_RECOVERY_FILE_DESTOldest online log sequence     0Next log sequence to archive   0Current log sequence           0SQL> exitDisconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit ProductionWith 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 sysdbaSQL*Plus: Release 11.2.0.3.0 Production on Thu Jul 4 13:57:48 2019Copyright (c) 1982, 2011, Oracle.  All rights reserved.Connected to:Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit ProductionWith the Partitioning, OLAP, Data Mining and Real Application Testing optionsSQL> ALTER SYSTEM SWITCH LOGFILE;System altered.SQL> archive log list;Database log mode              Archive ModeAutomatic archival             EnabledArchive destination            USE_DB_RECOVERY_FILE_DESTOldest online log sequence     43Next log sequence to archive   45Current log sequence           45SQL> exitDisconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit ProductionWith 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 sysdbaSQL*Plus: Release 11.2.0.3.0 Production on Thu Jul 4 13:58:45 2019Copyright (c) 1982, 2011, Oracle.  All rights reserved.Connected to:Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit ProductionWith the Partitioning, OLAP, Data Mining and Real Application Testing optionsSQL> archive log list;Database log mode              Archive ModeAutomatic archival             EnabledArchive destination            USE_DB_RECOVERY_FILE_DESTOldest online log sequence     44Next log sequence to archive   0Current log sequence           45SQL>  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 ALLOWEDSQL> exitDisconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit ProductionWith the Partitioning, OLAP, Data Mining and Real Application Testing options[oracle@oraclep trace]$ sqlplus test/testSQL*Plus: Release 11.2.0.3.0 Production on Thu Jul 4 14:00:07 2019Copyright (c) 1982, 2011, Oracle.  All rights reserved.Connected to:Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit ProductionWith the Partitioning, OLAP, Data Mining and Real Application Testing optionsSQL> select * from test;        ID       NUMS---------- ----------        1          3        1          4        1          5        1          2SQL> exitDisconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit ProductionWith the Partitioning, OLAP, Data Mining and Real Application Testing options[oracle@oraclep trace]$ ssh 192.168.56.117oracle@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 2019Copyright (c) 1982, 2011, Oracle.  All rights reserved.Connected to:Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit ProductionWith the Partitioning, OLAP, Data Mining and Real Application Testing optionsSQL> select * from test;        ID       NUMS---------- ----------        1          3        1          4        1          5        1          2SQL> exitDisconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit ProductionWith 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 2019Copyright (c) 1982, 2011, Oracle.  All rights reserved.Connected to:Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit ProductionWith the Partitioning, OLAP, Data Mining and Real Application Testing optionsSQL> SELECT OPEN_MODE,DATABASE_ROLE,SWITCHOVER_STATUS FROM V$DATABASE;OPEN_MODE            DATABASE_ROLE    SWITCHOVER_STATUS-------------------- ---------------- --------------------READ WRITE           PRIMARY          TO STANDBYSQL> exitDisconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit ProductionWith 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 2019Copyright (c) 1982, 2011, Oracle.  All rights reserved.Connected to:Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit ProductionWith the Partitioning, OLAP, Data Mining and Real Application Testing optionsSQL> select * from test;        ID       NUMS---------- ----------        1          3        1          4        1          5        1          2SQL> insert into test select * from test;4 rows created.SQL> commit;Commit complete.SQL> exitDisconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit ProductionWith 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 2019Copyright (c) 1982, 2011, Oracle.  All rights reserved.Connected to:Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit ProductionWith the Partitioning, OLAP, Data Mining and Real Application Testing optionsSQL> select * from test;        ID       NUMS---------- ----------        1          3        1          4        1          5        1          2        1          3        1          4        1          5        1          28 rows selected.至此,Switchover切换完成!总结:Switchover为主、备之间的正常切换,切换前要保证主、备库的数据一致,而且要先主切备,后备切主,避免同时存在两个主库。
0