在今天的文章中，我们将讨论使用SQLPLUS命令从主数据库切换到物理备用数据库。
在切换之前，需要进行一系列检查，以确定系统是否适合切换。其中一些控件是通用的，适用于我们的整个数据保护环境，而另一些控件是特定于切换的。
预先控制
1.我们检查集群服务是否正在运行。
[grid@primary1 ~]$ crs_stat -t
Name Type Target State Host
------------------------------------------------------------
ora.DATA.dg ora....up.type ONLINE ONLINE primary1
ora.FRA.dg ora....up.type ONLINE ONLINE primary1
ora....ER.lsnr ora....er.type ONLINE ONLINE primary1
ora....N1.lsnr ora....er.type ONLINE ONLINE primary2
ora....N2.lsnr ora....er.type ONLINE ONLINE primary1
ora....N3.lsnr ora....er.type ONLINE ONLINE primary1
ora....VOTE.dg ora....up.type ONLINE ONLINE primary1
ora.asm ora.asm.type ONLINE ONLINE primary1
ora.cvu ora.cvu.type ONLINE ONLINE primary1
ora.gsd ora.gsd.type OFFLINE OFFLINE
ora....network ora....rk.type ONLINE ONLINE primary1
ora.oc4j ora.oc4j.type ONLINE ONLINE primary1
ora.ons ora.ons.type ONLINE ONLINE primary1
ora.primary.db ora....se.type ONLINE ONLINE primary1
ora....SM1.asm application ONLINE ONLINE primary1
ora....Y1.lsnr application ONLINE ONLINE primary1
ora....ry1.gsd application OFFLINE OFFLINE
ora....ry1.ons application ONLINE ONLINE primary1
ora....ry1.vip ora....t1.type ONLINE ONLINE primary1
ora....SM2.asm application ONLINE ONLINE primary2
ora....Y2.lsnr application ONLINE ONLINE primary2
ora....ry2.gsd application OFFLINE OFFLINE
ora....ry2.ons application ONLINE ONLINE primary2
ora....ry2.vip ora....t1.type ONLINE ONLINE primary2
ora....ry.acfs ora....fs.type ONLINE ONLINE primary1
ora.scan1.vip ora....ip.type ONLINE ONLINE primary2
ora.scan2.vip ora....ip.type ONLINE ONLINE primary1
ora.scan3.vip ora....ip.type ONLINE ONLINE primary1
[grid@standby1 ~]$ crs_stat -t
Name Type Target State Host
------------------------------------------------------------
ora.DATA.dg ora....up.type ONLINE ONLINE standby1
ora.FRA.dg ora....up.type ONLINE ONLINE standby1
ora....ER.lsnr ora....er.type ONLINE ONLINE standby1
ora....N1.lsnr ora....er.type ONLINE ONLINE standby1
ora....N2.lsnr ora....er.type ONLINE ONLINE standby2
ora....N3.lsnr ora....er.type ONLINE ONLINE standby2
ora....VOTE.dg ora....up.type ONLINE ONLINE standby1
ora.asm ora.asm.type ONLINE ONLINE standby1
ora.cvu ora.cvu.type ONLINE ONLINE standby2
ora.gsd ora.gsd.type OFFLINE OFFLINE
ora....network ora....rk.type ONLINE ONLINE standby1
ora.oc4j ora.oc4j.type ONLINE ONLINE standby2
ora.ons ora.ons.type ONLINE ONLINE standby1
ora....ry.acfs ora....fs.type ONLINE ONLINE standby1
ora.scan1.vip ora....ip.type ONLINE ONLINE standby1
ora.scan2.vip ora....ip.type ONLINE ONLINE standby2
ora.scan3.vip ora....ip.type ONLINE ONLINE standby2
ora.standby.db ora....se.type ONLINE ONLINE standby1
ora....SM1.asm application ONLINE ONLINE standby1
ora....Y1.lsnr application ONLINE ONLINE standby1
ora....by1.gsd application OFFLINE OFFLINE
ora....by1.ons application ONLINE ONLINE standby1
ora....by1.vip ora....t1.type ONLINE ONLINE standby1
ora....SM2.asm application ONLINE ONLINE standby2
ora....Y2.lsnr application ONLINE ONLINE standby2
ora....by2.gsd application OFFLINE OFFLINE
ora....by2.ons application ONLINE ONLINE standby2
ora....by2.vip ora....t1.type ONLINE ONLINE standby2
[grid@logical1 ~]$ crs_stat -t
Name Type Target State Host
------------------------------------------------------------
ora.DATA.dg ora....up.type ONLINE ONLINE logical1
ora.FRA.dg ora....up.type ONLINE ONLINE logical1
ora....ER.lsnr ora....er.type ONLINE ONLINE logical1
ora....N1.lsnr ora....er.type ONLINE ONLINE logical1
ora....N2.lsnr ora....er.type ONLINE ONLINE logical2
ora....N3.lsnr ora....er.type ONLINE ONLINE logical2
ora....VOTE.dg ora....up.type ONLINE ONLINE logical1
ora.asm ora.asm.type ONLINE ONLINE logical1
ora.cvu ora.cvu.type ONLINE ONLINE logical2
ora.gsd ora.gsd.type OFFLINE OFFLINE
ora.logical.db ora....se.type ONLINE ONLINE logical1
ora....SM1.asm application ONLINE ONLINE logical1
ora....L1.lsnr application ONLINE ONLINE logical1
ora....al1.gsd application OFFLINE OFFLINE
ora....al1.ons application ONLINE ONLINE logical1
ora....al1.vip ora....t1.type ONLINE ONLINE logical1
ora....SM2.asm application ONLINE ONLINE logical2
ora....L2.lsnr application ONLINE ONLINE logical2
ora....al2.gsd application OFFLINE OFFLINE
ora....al2.ons application ONLINE ONLINE logical2
ora....al2.vip ora....t1.type ONLINE ONLINE logical2
ora....network ora....rk.type ONLINE ONLINE logical1
ora.oc4j ora.oc4j.type ONLINE ONLINE logical2
ora.ons ora.ons.type ONLINE ONLINE logical1
ora....ry.acfs ora....fs.type ONLINE ONLINE logical1
ora.scan1.vip ora....ip.type ONLINE ONLINE logical1
ora.scan2.vip ora....ip.type ONLINE ONLINE logical2
ora.scan3.vip ora....ip.type ONLINE ONLINE logical2
2.我们检查实例的状态。
[grid@primary1 ~]$ srvctl status database -d primary
Instance primary1 is running on node primary1
Instance primary2 is running on node primary2
[grid@standby1 ~]$ srvctl status database -d standby
Instance primary1 is running on node standby1
Instance primary2 is running on node standby2
[grid@logical1 ~]$ srvctl status database -d logical
Instance primary1 is running on node logical1
Instance primary2 is running on node logical2
3.我们检查听众是否工作正常。为此，我们从第三方客户端登录到所有实例。
4.我们正在尝试从新的主数据库连接到新的物理备用数据库和逻辑备用数据库，这将在切换后进行。
[oracle@standby1 ~]$ sqlplus sys/Passw0rd4@PRIMARY1 as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Thu Jan 19 11:18:35 2017
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options
[Physical-1] SQL> show parameter name
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
cell_offloadgroup_name string
db_file_name_convert string standby, primary
db_name string primary
db_unique_name string primary
global_names boolean FALSE
instance_name string primary2
lock_name_space string
log_file_name_convert string standby, primary
processor_group_name string
service_names string primary
[oracle@standby1 ~]$ sqlplus sys/Passw0rd4@PRIMARY2 as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Thu Jan 19 11:19:14 2017
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options
[Physical-1] SQL> show parameter name
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
cell_offloadgroup_name string
db_file_name_convert string standby, primary
db_name string primary
db_unique_name string primary
global_names boolean FALSE
instance_name string primary2
lock_name_space string
log_file_name_convert string standby, primary
processor_group_name string
service_names string primary
[oracle@standby1 ~]$ sqlplus sys/Passw0rd4@PRIMARY as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Thu Jan 19 11:19:38 2017
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options
[Physical-1] SQL> show parameter name
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
cell_offloadgroup_name string
db_file_name_convert string standby, primary
db_name string primary
db_unique_name string primary
global_names boolean FALSE
instance_name string primary2
lock_name_space string
log_file_name_convert string standby, primary
processor_group_name string
service_names string primary
[oracle@standby1 ~]$ sqlplus sys/Passw0rd4@LOGICAL as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Thu Jan 19 11:20:03 2017
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options
[Physical-1] SQL> show parameter name
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
cell_offloadgroup_name string
db_file_name_convert string logical, primary
db_name string logical
db_unique_name string logical
global_names boolean FALSE
instance_name string primary1
lock_name_space string
log_file_name_convert string primary, logical
processor_group_name string
service_names string logical
[oracle@standby1 ~]$ sqlplus sys/Passw0rd4@LOGICAL1 as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Thu Jan 19 11:20:27 2017
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options
[Physical-1] SQL> show parameter name
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
cell_offloadgroup_name string
db_file_name_convert string logical, primary
db_name string logical
db_unique_name string logical
global_names boolean FALSE
instance_name string primary1
lock_name_space string
log_file_name_convert string primary, logical
processor_group_name string
service_names string logical
[oracle@standby1 ~]$ sqlplus sys/Passw0rd4@LOGICAL2 as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Thu Jan 19 11:20:48 2017
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options
[Physical-1] SQL> show parameter name
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
cell_offloadgroup_name string
db_file_name_convert string logical, primary
db_name string logical
db_unique_name string logical
global_names boolean FALSE
instance_name string primary2
lock_name_space string
log_file_name_convert string primary, logical
processor_group_name string
service_names string logical
同样，我们从备用2中逐个尝试。
5.我们正在询问是否使用了备用在线重做日志及其状态。
[Primary-1] SQL> set linesize 9000
[Primary-1] SQL> column dbid format a15
[Primary-1] SQL> SELECT group#, thread#, sequence#, dbid, archived, status FROM v$standby_log;
GROUP# THREAD# SEQUENCE# DBID ARC STATUS
---------- ---------- ---------- --------------- --- ----------
5 1 0 UNASSIGNED YES UNASSIGNED
6 1 0 UNASSIGNED YES UNASSIGNED
7 1 0 UNASSIGNED YES UNASSIGNED
8 2 0 UNASSIGNED YES UNASSIGNED
9 2 0 UNASSIGNED YES UNASSIGNED
10 2 0 UNASSIGNED YES UNASSIGNED
6 rows selected.
[Physical-1] SQL> set linesize 9000
[Physical-1] SQL> column dbid format a15
[Physical-1] SQL> SELECT group#, thread#, sequence#, dbid, archived, status FROM v$standby_log;
GROUP# THREAD# SEQUENCE# DBID ARC STATUS
---------- ---------- ---------- --------------- --- ----------
5 1 0 UNASSIGNED NO UNASSIGNED
6 1 673 1769705496 YES ACTIVE
7 1 0 UNASSIGNED YES UNASSIGNED
8 2 0 UNASSIGNED NO UNASSIGNED
9 2 422 1769705496 YES ACTIVE
10 2 0 UNASSIGNED YES UNASSIGNED
6 rows selected.
[Logical-1] SQL> set linesize 9000
[Logical-1] SQL> column dbid format a15
[Logical-1] SQL> SELECT group#, thread#, sequence#, dbid, archived, status FROM v$standby_log;
GROUP# THREAD# SEQUENCE# DBID ARC STATUS
---------- ---------- ---------- --------------- --- ----------
5 1 0 UNASSIGNED NO UNASSIGNED
6 1 673 1769705496 YES ACTIVE
7 1 0 UNASSIGNED NO UNASSIGNED
8 2 422 1769705496 YES ACTIVE
9 2 0 UNASSIGNED NO UNASSIGNED
10 2 0 UNASSIGNED NO UNASSIGNED
6 rows selected.
此处“存档”列和“状态”列的组合表示以下内容。
6.我们检查存档目的地的角色、类型和适用性。这一过程对于档案在切换后正确进入备用数据库非常重要。
[Primary-1] SQL> SELECT dest_id,valid_type,valid_role,valid_now FROM v$archive_dest;
DEST_ID VALID_TYPE VALID_ROLE VALID_NOW
---------- --------------- ------------ ----------------
1 ALL_LOGFILES ALL_ROLES YES
2 ALL_LOGFILES PRIMARY_ROLE YES
3 ALL_LOGFILES PRIMARY_ROLE YES
[Physical-1] SQL> SELECT dest_id,valid_type,valid_role,valid_now FROM v$archive_dest;
DEST_ID VALID_TYPE VALID_ROLE VALID_NOW
---------- --------------- ------------ ----------------
1 ALL_LOGFILES ALL_ROLES YES
2 ALL_LOGFILES PRIMARY_ROLE WRONG VALID_ROLE
3 ALL_LOGFILES PRIMARY_ROLE WRONG VALID_ROLE
[Logical-1] SQL> SELECT dest_id,valid_type,valid_role,valid_now FROM v$archive_dest;
DEST_ID VALID_TYPE VALID_ROLE VALID_NOW
---------- --------------- ------------ ----------------
1 ALL_LOGFILES ALL_ROLES YES
7.我们检查存档目标中是否存在错误。
[Primary-1] SQL> column destination format a30
[Primary-1] SQL> column error format a20
[Primary-1] SQL> set linesize 9000
[Primary-1] SQL> SELECT DEST_ID, STATUS, DESTINATION, ERROR FROM V$ARCHIVE_DEST WHERE DEST_ID <=5;
DEST_ID STATUS DESTINATION ERROR
---------- --------- ------------------------------ --------------------
1 VALID USE_DB_RECOVERY_FILE_DEST
2 VALID standby
3 VALID logical
4 INACTIVE
5 INACTIVE
[Physical-1] SQL> column destination format a30
[Physical-1] SQL> column error format a20
[Physical-1] SQL> set linesize 9000
[Physical-1] SQL> SELECT DEST_ID, STATUS, DESTINATION, ERROR FROM V$ARCHIVE_DEST WHERE DEST_ID <=5;
DEST_ID STATUS DESTINATION ERROR
---------- --------- ------------------------------ --------------------
1 VALID USE_DB_RECOVERY_FILE_DEST
2 VALID primary
3 VALID logical
4 INACTIVE
5 INACTIVE
[Logical-1] SQL> column destination format a30
[Logical-1] SQL> column error format a20
[Logical-1] SQL> set linesize 9000
[Logical-1] SQL> SELECT DEST_ID, STATUS, DESTINATION, ERROR FROM V$ARCHIVE_DEST WHERE DEST_ID <=5;
DEST_ID STATUS DESTINATION ERROR
---------- --------- ------------------------------ --------------------
1 VALID USE_DB_RECOVERY_FILE_DEST
2 INACTIVE
3 INACTIVE
4 INACTIVE
5 INACTIVE
8.我们检查重做传输和重做应用延迟。
[Physical-1] SQL> set linesize 9000
[Physical-1] SQL> column name format a25
[Physical-1] SQL> column value format a20
[Physical-1] SQL> column time_computed format a25
[Physical-1] SQL> SELECT name, value, time_computed FROM v$dataguard_stats;
NAME VALUE TIME_COMPUTED
------------------------- -------------------- -------------------------
transport lag +00 00:00:00 01/19/2017 13:58:52
apply lag +00 00:00:00 01/19/2017 13:58:52
apply finish time +00 00:00:00.000 01/19/2017 13:58:52
estimated startup time 27 01/19/2017 13:58:52
[Logical-1] SQL> set linesize 9000
[Logical-1] SQL> column name format a25
[Logical-1] SQL> column value format a20
[Logical-1] SQL> column time_computed format a25
[Logical-1] SQL> SELECT name, value, time_computed FROM v$dataguard_stats;
NAME VALUE TIME_COMPUTED
------------------------- -------------------- -------------------------
transport lag +00 00:00:00 01/19/2017 13:58:52
apply lag +00 00:00:00 01/19/2017 13:58:52
apply finish time +00 00:00:00.000 01/19/2017 13:58:52
estimated startup time 25 01/19/2017 13:58:52
9.我们了解重做传输事务中是否存在错误和间隙。
[Primary-1] SQL> column DEST_NAME format a20
[Primary-1] SQL> column error format a15
[Primary-1] SQL> SELECT DEST_NAME, STATUS, GAP_STATUS, ERROR FROM V$ARCHIVE_DEST_STATUS;
DEST_NAME STATUS GAP_STATUS ERROR
-------------------- --------- ------------------------ ---------------
LOG_ARCHIVE_DEST_1 VALID
LOG_ARCHIVE_DEST_2 VALID NO GAP
LOG_ARCHIVE_DEST_3 VALID NO GAP
10.我们检查主数据库中创建的存档是否在所有备用数据库中处理。
[Primary-1] SQL> alter system switch logfile;
System altered.
[Primary-1] SQL> alter system switch logfile;
System altered.
[Primary-2] SQL> alter system switch logfile;
System altered.
[Primary-2] SQL> alter system switch logfile;
System altered.
[Primary-1] SQL> select max(sequence#),thread# from v$archived_log group by thread#;
MAX(SEQUENCE#) THREAD#
-------------- ----------
675 1
424 2
[Physical-1] SQL> select max(sequence#),thread# from v$archived_log group by thread#;
MAX(SEQUENCE#) THREAD#
-------------- ----------
675 1
424 2
[Logical-1] SQL> SELECT max(sequence#),thread#,applied FROM dba_logstdby_log group by thread#, applied;
MAX(SEQUENCE#) THREAD# APPLIED
-------------- ---------- --------
675 1 YES
424 2 YES
11.我们检查tempFile是否位于主端和备用端。
[Primary-1] SQL> column name format a45
[Primary-1] SQL> select name from v$tempfile;
NAME
---------------------------------------------
+DATA/primary/tempfile/temp.263.932222817
[Physical-1] SQL> column name format a45
[Physical-1] SQL> select name from v$tempfile;
NAME
---------------------------------------------
+DATA/standby/tempfile/temp.276.932286709
[Logical-1] SQL> column name format a45
[Logical-1] SQL> select name from v$tempfile;
NAME
---------------------------------------------
+DATA/logical/tempfile/temp.280.932895541
12.我们检查LOG_ ARCHIVE_DEST_n
在该检查之后，如果切换后新主数据库将发送存档的位置丢失，则使用ALTER SYSTEM SET LOG_ARCHIVE_DEST_2=’…'命令定义。
[Primary-1] SQL> column name format a30
[Primary-1] SQL> column value format a94
[Primary-1] SQL> set linesize 9000
[Primary-1] SQL> select name, value from v$parameter where lower(name) like '%log_archive_dest%' and lower(name) not like '%log_archive_dest_state%' and value is not null;
NAME VALUE
------------------------------ ----------------------------------------------------------------------------------------------
log_archive_dest_1 LOCATION=USE_DB_RECOVERY_FILE_DEST VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=primary
log_archive_dest_2 service="standby", LGWR ASYNC NOAFFIRM delay=0 optional compression=disable max_failure=0 max_
connections=1 reopen=300 db_unique_name="standby", valid_for=(all_logfiles,primary_role)
log_archive_dest_3 service="logical", LGWR ASYNC NOAFFIRM delay=0 optional compression=disable max_failure=0 max_
connections=1 reopen=300 db_unique_name="logical", valid_for=(all_logfiles,primary_role)
[Physical-1] SQL> column name format a30
[Physical-1] SQL> column value format a94
[Physical-1] SQL> set linesize 9000
[Physical-1] SQL> select name, value from v$parameter where lower(name) like '%log_archive_dest%' and lower(name) not like '%log_archive_dest_state%' and value is not null;
NAME VALUE
------------------------------ ----------------------------------------------------------------------------------------------
log_archive_dest_1 LOCATION=USE_DB_RECOVERY_FILE_DEST VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=standby
log_archive_dest_2 service="primary", LGWR ASYNC NOAFFIRM delay=0 optional compression=disable max_failure=0 max_
connections=1 reopen=300 db_unique_name="primary", valid_for=(all_logfiles,primary_role)
log_archive_dest_3 service="logical", LGWR ASYNC NOAFFIRM delay=0 optional compression=disable max_failure=0 max_
connections=1 reopen=300 db_unique_name="logical", valid_for=(all_logfiles,primary_role)
[Logical-1] SQL> column name format a30
[Logical-1] SQL> column value format a94
[Logical-1] SQL> set linesize 9000
[Logical-1] SQL> select name, value from v$parameter where lower(name) like '%log_archive_dest%' and lower(name) not like '%log_archive_dest_state%' and value is not null;
NAME VALUE
------------------------------ ----------------------------------------------------------------------------------------------
log_archive_dest_1 LOCATION=USE_DB_RECOVERY_FILE_DEST VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=logical
13.我们检查诸如角色、开放模式、数据库模式等信息。
[Primary-1] SQL> select d.open_mode, d.database_role, a.database_mode, a.recovery_mode, a.protection_mode from v$archive_dest_status a, v$database d where a.database_mode <> 'UNKNOWN';
OPEN_MODE DATABASE_ROLE DATABASE_MODE RECOVERY_MODE PROTECTION_MODE
-------------------- ---------------- --------------- ----------------------- --------------------
READ WRITE PRIMARY OPEN IDLE MAXIMUM PERFORMANCE
READ WRITE PRIMARY MOUNTED-STANDBY MANAGED REAL TIME APPLY MAXIMUM PERFORMANCE
READ WRITE PRIMARY OPEN LOGICAL REAL TIME APPLY MAXIMUM PERFORMANCE
[Physical-1] SQL> select d.open_mode, d.database_role, a.database_mode, a.recovery_mode, a.protection_mode from v$archive_dest_status a, v$database d where a.database_mode <> 'UNKNOWN';
OPEN_MODE DATABASE_ROLE DATABASE_MODE RECOVERY_MODE PROTECTION_MODE
-------------------- ---------------- --------------- ----------------------- --------------------
MOUNTED PHYSICAL STANDBY MOUNTED-STANDBY MANAGED REAL TIME APPLY MAXIMUM PERFORMANCE
[Logical-1] SQL> select d.open_mode, d.database_role, a.database_mode, a.recovery_mode, a.protection_mode from v$archive_dest_status a, v$database d where a.database_mode <> 'UNKNOWN';
OPEN_MODE DATABASE_ROLE DATABASE_MODE RECOVERY_MODE PROTECTION_MODE
-------------------- ---------------- --------------- ----------------------- --------------------
READ WRITE LOGICAL STANDBY OPEN LOGICAL REAL TIME APPLY
切换操作
初步检查成功完成后，我们继续切换过程。
1.在主数据库中执行日志切换操作将非常有用，以便以最快的时间将在保护模式为最高性能且重做应用处于存档应用模式的系统中创建的重做应用到备用端。
即使我们使用实时应用，我们也会进行日志切换以适应它。
[Primary-1] SQL> alter system switch logfile;
System altered.
[Primary-1] SQL> alter system switch logfile;
System altered.
[Primary-2] SQL> alter system switch logfile;
System altered.
[Primary-2] SQL> alter system switch logfile;
System altered.
2.质疑主数据库是否适合切换。
[Primary-1] SQL> SELECT SWITCHOVER_STATUS FROM V$DATABASE;
SWITCHOVER_STATUS
--------------------
SESSIONS ACTIVE
当SWITCHOVER_ STATUS列为STANDBY或SESSIONS ACTIVE时，它适用于切换。除此之外，我们还要重复预检查。
3.切换到主数据库备用角色。
[Primary-1] SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO PHYSICAL STANDBY WITH SESSION SHUTDOWN;
Database altered.
使用WITH SESSION SHUTDOWN的原因是切换状态为SESSIONS ACTIVE。如果切换状态为_ STANDBY，则不需要写入WITH SESSION SHUTDOWN。
该过程的结果是，落在实例上的日志如下所示。
[Primary-1]—–>ALERT LOG
Thu Jan 19 14:24:55 2017
ALTER DATABASE COMMIT TO SWITCHOVER TO PHYSICAL STANDBY WITH SESSION SHUTDOWN
ALTER DATABASE COMMIT TO SWITCHOVER TO PHYSICAL STANDBY [Process Id: 28820] (primary1)
Waiting for all non-current ORLs to be archived...
All non-current ORLs have been archived.
Waiting for all FAL entries to be archived...
All FAL entries have been archived.
Waiting for potential Physical Standby switchover target to become synchronized...
Active, synchronized Physical Standby switchover target has been identified
Switchover End-Of-Redo Log thread 1 sequence 676 has been fixed
Switchover End-Of-Redo Log thread 2 sequence 425 has been fixed
Switchover: Primary highest seen SCN set to 0x0.0x6c28fe
ARCH: Noswitch archival of thread 2, sequence 425
ARCH: End-Of-Redo Branch archival of thread 2 sequence 425
ARCH: LGWR is actively archiving destination LOG_ARCHIVE_DEST_3
ARCH: LGWR is actively archiving destination LOG_ARCHIVE_DEST_2
ARCH: Standby redo logfile selected for thread 2 sequence 425 for destination LOG_ARCHIVE_DEST_3
ARCH: Standby redo logfile selected for thread 2 sequence 425 for destination LOG_ARCHIVE_DEST_2
Archived Log entry 2748 added for thread 2 sequence 425 ID 0x69915472 dest 1:
ARCH: Noswitch archival of thread 1, sequence 676
ARCH: End-Of-Redo Branch archival of thread 1 sequence 676
ARCH: LGWR is actively archiving destination LOG_ARCHIVE_DEST_3
ARCH: LGWR is actively archiving destination LOG_ARCHIVE_DEST_2
ARCH: Standby redo logfile selected for thread 1 sequence 676 for destination LOG_ARCHIVE_DEST_3
ARCH: Standby redo logfile selected for thread 1 sequence 676 for destination LOG_ARCHIVE_DEST_2
Archived Log entry 2751 added for thread 1 sequence 676 ID 0x69915472 dest 1:
ARCH: Archiving is disabled due to current logfile archival
Primary will check for some target standby to have received alls redo
Final check for a synchronized target standby. Check will be made once.
LOG_ARCHIVE_DEST_2 is a potential Physical Standby switchover target
LOG_ARCHIVE_DEST_3 is not a Physical Standby
Active, synchronized target has been identified
Target has also received all redo
Backup controlfile written to trace file /u01/app/oracle/diag/rdbms/primary/primary1/trace/primary1_ora_28820.trc
Clearing standby activation ID 1771132018 (0x69915472)
The primary database controlfile was created using the
'MAXLOGFILES 192' clause.
There is space for up to 188 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 52428800;
ALTER DATABASE ADD STANDBY LOGFILE 'srl2.f' SIZE 52428800;
ALTER DATABASE ADD STANDBY LOGFILE 'srl3.f' SIZE 52428800;
ALTER DATABASE ADD STANDBY LOGFILE 'srl4.f' SIZE 52428800;
ALTER DATABASE ADD STANDBY LOGFILE 'srl5.f' SIZE 52428800;
Archivelog for thread 1 sequence 676 required for standby recovery
Archivelog for thread 2 sequence 425 required for standby recovery
Switchover: Primary controlfile converted to standby controlfile succesfully.
Thu Jan 19 14:25:02 2017
Reconfiguration started (old inc 4, new inc 6)
List of instances:
1 (myinst: 1)
Global Resource Directory frozen
* dead instance detected - domain 0 invalid = TRUE
Communication channels reestablished
Master broadcasted resource hash value bitmaps
Non-local Process blocks cleaned out
Thu Jan 19 14:25:02 2017
LMS 0: 0 GCS shadows cancelled, 0 closed, 0 Xw survived
Thu Jan 19 14:25:02 2017
LMS 1: 0 GCS shadows cancelled, 0 closed, 0 Xw survived
Set master node info
Submitted all remote-enqueue requests
Dwn-cvts replayed, VALBLKs dubious
All grantable enqueues granted
Post SMON to start 1st pass IR
Thu Jan 19 14:25:02 2017
Process (ospid 24298) is suspended due to switchover to physical standby operation.
Thu Jan 19 14:25:02 2017
Instance recovery: looking for dead threads
Process (ospid 24302) is suspended due to switchover to physical standby operation.
Submitted all GCS remote-cache requests
Post SMON to start 1st pass IR
Fix write in gcs resources
Switchover: Complete - Database shutdown required
USER (ospid: 28820): terminating the instance
Instance terminated by USER, pid = 28820
Completed: ALTER DATABASE COMMIT TO SWITCHOVER TO PHYSICAL STANDBY WITH SESSION SHUTDOWN
Shutting down instance (abort)
License high water mark = 13
Thu Jan 19 14:25:03 2017
Instance shutdown complete
[Primary-2]—–>ALERT LOG
Thu Jan 19 14:25:06 2017
Switchover in progress in another database instance - Database is shutdown automatically
LGWR (ospid: 13956): terminating the instance due to error 16456
Instance terminated by LGWR, pid = 13956
[Standby-1]—–>ALERT LOG
–
[Standby-2]—–>ALERT LOG
Thu Jan 19 14:25:05 2017
RFS[5]: Assigned to RFS process 26883
RFS[5]: Selected log 8 for thread 2 sequence 425 dbid 1769705496 branch 932222810
Thu Jan 19 14:25:05 2017
Archived Log entry 1162 added for thread 2 sequence 425 ID 0x69915472 dest 1:
RFS[5]: Selected log 5 for thread 1 sequence 676 dbid 1769705496 branch 932222810
Thu Jan 19 14:25:05 2017
Archived Log entry 1163 added for thread 1 sequence 676 ID 0x69915472 dest 1:
Thu Jan 19 14:25:06 2017
RFS[3]: Possible network disconnect with primary database
Thu Jan 19 14:25:06 2017
RFS[6]: Assigned to RFS process 21794
RFS[6]: Possible network disconnect with primary database
Thu Jan 19 14:25:08 2017
RFS[7]: Assigned to RFS process 26878
RFS[7]: Possible network disconnect with primary database
Thu Jan 19 14:25:08 2017
RFS[4]: Possible network disconnect with primary database
[Logical-1]—–>ALERT LOG
Thu Jan 19 14:24:34 2017
RFS LogMiner: RFS id [6492] assigned as thread [1] PING handler
RFS LogMiner: RFS id [6492] assigned as thread [1] PING handler
Thu Jan 19 14:24:35 2017
NOTE: Deferred communication with ASM instance
NOTE: deferred map free for map id 34593
Thu Jan 19 14:24:36 2017
NOTE: Deferred communication with ASM instance
NOTE: deferred map free for map id 34574
Thu Jan 19 14:24:38 2017
RFS[57]: Assigned to RFS process 6508
RFS[57]: Selected log 9 for thread 2 sequence 425 dbid 1769705496 branch 932222810
RFS[57]: Selected log 6 for thread 1 sequence 676 dbid 1769705496 branch 932222810
Thu Jan 19 14:24:38 2017
RFS LogMiner: Registered logfile [+FRA/logical/foreign_archivelog/primary/2017_01_19/thread_2_seq_425.1244.933690279] to LogMiner session id [1]
Thu Jan 19 14:24:38 2017
RFS LogMiner: Registered logfile [+FRA/logical/foreign_archivelog/primary/2017_01_19/thread_1_seq_676.1245.933690279] to LogMiner session id [1]
Thu Jan 19 14:24:39 2017
RFS[56]: Possible network disconnect with primary database
Thu Jan 19 14:24:39 2017
RFS[58]: Assigned to RFS process 13840
RFS[58]: Possible network disconnect with primary database
RFS[59]: Assigned to RFS process 6492
RFS[59]: Possible network disconnect with primary database
Thu Jan 19 14:24:42 2017
RFS[54]: Possible network disconnect with primary database
4.安装新的物理备用数据库（旧的主数据库）。
[Primary-1] SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options
You have new mail in /var/spool/mail/oracle
[oracle@primary1 ~]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Thu Jan 19 14:27:08 2017
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to an idle instance.
[Primary-2] SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options
[oracle@primary2 ~]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Thu Jan 19 14:27:22 2017
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to an idle instance.
如果我们注意，在将当前主服务器转换为物理备用服务器的过程中，这两个实例都会自动关闭。
[Primary-1] SQL> startup mount;
ORACLE instance started.
Total System Global Area 6480490496 bytes
Fixed Size 2265384 bytes
Variable Size 1241517784 bytes
Database Buffers 5217714176 bytes
Redo Buffers 18993152 bytes
Database mounted.
5.新的主数据库（旧的物理备用数据库）是否适合切换受到质疑。
[Physical-2] SQL> SELECT SWITCHOVER_STATUS FROM V$DATABASE;
SWITCHOVER_STATUS
--------------------
SESSIONS ACTIVE
6.旧物理备用数据库，创建主数据库。
[Physical-2] SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY WITH SESSION SHUTDOWN;
Database altered.
此过程的结果是，进入警报日志的日志如下所示。
[Standby-2]—–>ALERT LOG
Thu Jan 19 14:32:06 2017
alter database recover managed standby database using current logfile disconnect
Attempt to start background Managed Standby Recovery process (primary2)
Thu Jan 19 14:32:06 2017
MRP0 started with pid=34, OS id=28023
MRP0: Background Managed Standby Recovery process started (primary2)
started logmerger process
Thu Jan 19 14:32:11 2017
Managed Standby Recovery starting Real Time Apply
Parallel Media Recovery started with 4 slaves
Thu Jan 19 14:32:12 2017
Block change tracking file is current.
Starting background process CTWR
Thu Jan 19 14:32:12 2017
CTWR started with pid=48, OS id=28081
Block change tracking service is active.
Waiting for all non-current ORLs to be archived...
All non-current ORLs have been archived.
Media Recovery Log +FRA/standby/archivelog/2017_01_19/thread_2_seq_425.702.933690305
Identified End-Of-Redo (switchover) for thread 2 sequence 425 at SCN 0x0.6c28fe
Media Recovery Log +FRA/standby/archivelog/2017_01_19/thread_1_seq_676.703.933690305
Identified End-Of-Redo (switchover) for thread 1 sequence 676 at SCN 0x0.6c28fe
Completed: alter database recover managed standby database using current logfile disconnect
Resetting standby activation ID 1771132018 (0x69915472)
Media Recovery End-Of-Redo indicator encountered
Media Recovery Continuing
Media Recovery Waiting for thread 1 sequence 677
Thu Jan 19 14:33:15 2017
ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY WITH SESSION SHUTDOWN
ALTER DATABASE SWITCHOVER TO PRIMARY (primary2)
Maximum wait for role transition is 15 minutes.
Switchover: Media recovery is still active
Role Change: Canceling MRP - no more redo to apply
Thu Jan 19 14:33:16 2017
MRP0: Background Media Recovery cancelled with status 16037
Errors in file /u01/app/oracle/diag/rdbms/standby/primary2/trace/primary2_pr00_28055.trc:
ORA-16037: user requested cancel of managed recovery operation
Thu Jan 19 14:33:16 2017
Managed Standby Recovery not using Real Time Apply
Recovery interrupted!
Thu Jan 19 14:33:16 2017
Block change tracking service stopping.
Stopping background process CTWR
Thu Jan 19 14:33:17 2017
MRP0: Background Media Recovery process shutdown (primary2)
Role Change: Canceled MRP
Backup controlfile written to trace file /u01/app/oracle/diag/rdbms/standby/primary2/trace/primary2_ora_27589.trc
SwitchOver after complete recovery through change 7088382
Online log +DATA/standby/onlinelog/group_1.263.932283679: Thread 1 Group 1 was previously cleared
Online log +FRA/standby/onlinelog/group_1.256.932283681: Thread 1 Group 1 was previously cleared
Online log +DATA/standby/onlinelog/group_2.264.932283681: Thread 1 Group 2 was previously cleared
Online log +FRA/standby/onlinelog/group_2.257.932283681: Thread 1 Group 2 was previously cleared
Online log +DATA/standby/onlinelog/group_3.265.932283681: Thread 2 Group 3 was previously cleared
Online log +FRA/standby/onlinelog/group_3.258.932283681: Thread 2 Group 3 was previously cleared
Online log +DATA/standby/onlinelog/group_4.266.932283681: Thread 2 Group 4 was previously cleared
Online log +FRA/standby/onlinelog/group_4.259.932283681: Thread 2 Group 4 was previously cleared
Standby became primary SCN: 7088380
Switchover: Complete - Database mounted as primary
Completed: ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY WITH SESSION SHUTDOWN
7.将新的主数据库设置为打开模式。
[Physical-2] SQL> ALTER DATABASE OPEN;
Database altered.
8.我们在新的物理备用数据库中启动重做应用过程。
[Primary-1] SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT;
Database altered.
[Primary-1]—–>ALERT LOG
Thu Jan 19 14:34:26 2017
Primary database is in MAXIMUM PERFORMANCE mode
RFS[2]: Assigned to RFS process 6174
RFS[2]: Selected log 8 for thread 2 sequence 428 dbid 1769705496 branch 932222810
Thu Jan 19 14:34:31 2017
RFS[3]: Assigned to RFS process 6189
RFS[3]: Selected log 9 for thread 2 sequence 427 dbid 1769705496 branch 932222810
Thu Jan 19 14:34:31 2017
Archived Log entry 2755 added for thread 2 sequence 427 ID 0x69914a7d dest 1:
Thu Jan 19 14:36:52 2017
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT
Attempt to start background Managed Standby Recovery process (primary1)
Thu Jan 19 14:36:52 2017
MRP0 started with pid=47, OS id=6768
MRP0: Background Managed Standby Recovery process started (primary1)
started logmerger process
Thu Jan 19 14:36:58 2017
Managed Standby Recovery starting Real Time Apply
Parallel Media Recovery started with 4 slaves
Thu Jan 19 14:36:58 2017
CHANGE TRACKING change stream 1 is open.
CHANGE TRACKING change stream 2 is open.
CHANGE TRACKING file is not usable. The above change
stream(s) are open. Change tracking was not closed
cleanly the last time it was shutdown.
CHANGE TRACKING is reinitializing the change tracking file.
Starting background process CTWR
Thu Jan 19 14:36:58 2017
CTWR started with pid=53, OS id=6808
Block change tracking service is active.
Waiting for all non-current ORLs to be archived...
All non-current ORLs have been archived.
Clearing online redo logfile 1 +DATA/primary/onlinelog/group_1.261.932222811
Clearing online log 1 of thread 1 sequence number 677
Clearing online redo logfile 1 complete
Clearing online redo logfile 2 +DATA/primary/onlinelog/group_2.262.932222811
Clearing online log 2 of thread 1 sequence number 675
Completed: ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT
Clearing online redo logfile 2 complete
Clearing online redo logfile 3 +DATA/primary/onlinelog/group_3.266.932223145
Clearing online log 3 of thread 2 sequence number 428
Clearing online redo logfile 3 complete
Clearing online redo logfile 4 +DATA/primary/onlinelog/group_4.267.932223147
Clearing online log 4 of thread 2 sequence number 427
Clearing online redo logfile 4 complete
Media Recovery Log +FRA/primary/archivelog/2017_01_19/thread_2_seq_425.948.933690299
Identified End-Of-Redo (switchover) for thread 2 sequence 425 at SCN 0x0.6c28fe
Media Recovery Log +FRA/primary/archivelog/2017_01_19/thread_1_seq_675.947.933688887
Media Recovery Log +FRA/primary/archivelog/2017_01_19/thread_1_seq_676.949.933690299
Identified End-Of-Redo (switchover) for thread 1 sequence 676 at SCN 0x0.6c28fe
Resetting standby activation ID 0 (0x0)
Media Recovery End-Of-Redo indicator encountered
Media Recovery Continuing
Media Recovery Waiting for thread 1 sequence 677
Thu Jan 19 14:37:20 2017
RFS[4]: Assigned to RFS process 6964
RFS[4]: Opened log for thread 1 sequence 677 dbid 1769705496 branch 932222810
Archived Log entry 2756 added for thread 1 sequence 677 rlc 932222810 ID 0x0 dest 2:
Thu Jan 19 14:37:21 2017
Media Recovery Log +FRA/primary/archivelog/2017_01_19/thread_1_seq_677.952.933691041
Media Recovery Log +FRA/primary/archivelog/2017_01_19/thread_2_seq_426.950.933690859
Media Recovery Log +FRA/primary/archivelog/2017_01_19/thread_2_seq_427.951.933690871
Media Recovery Waiting for thread 2 sequence 428 (in transit)
Recovery of Online Redo Log: Thread 2 Group 8 Seq 428 Reading mem 0
Mem# 0: +FRA/primary/onlinelog/group_8.261.932225269
9.切换过程完成。首先，我们查询数据库的模式。
[Primary-1] SQL> select name, database_role, open_mode from gv$database;
NAME DATABASE_ROLE OPEN_MODE
--------- ---------------- --------------------
PRIMARY PHYSICAL STANDBY MOUNTED
[Physical-2] SQL> select name, database_role, open_mode from gv$database;
NAME DATABASE_ROLE OPEN_MODE
--------- ---------------- --------------------
PRIMARY PRIMARY READ WRITE
PRIMARY PRIMARY READ WRITE
[Logical-2] SQL> select name, database_role, open_mode from gv$database;
NAME DATABASE_ROLE OPEN_MODE
--------- ---------------- --------------------
LOGICAL LOGICAL STANDBY READ WRITE
LOGICAL LOGICAL STANDBY READ WRITE
10.在切换期间自动关闭的旧主电源的第二个实例打开。
[Primary-2] SQL> startup mount;
ORACLE instance started.
Total System Global Area 6480490496 bytes
Fixed Size 2265384 bytes
Variable Size 1241517784 bytes
Database Buffers 5217714176 bytes
Redo Buffers 18993152 bytes
Database mounted.
11.在打开实例后，我们启动控件。
a、 在所有实例中检查数据库角色。
[Physical-1] SQL> select name, database_role, open_mode from gv$database;
NAME DATABASE_ROLE OPEN_MODE
------------------------------ ---------------- --------------------
PRIMARY PRIMARY READ WRITE
PRIMARY PRIMARY READ WRITE
[Primary-1] SQL> select name, database_role, open_mode from gv$database;
NAME DATABASE_ROLE OPEN_MODE
--------- ---------------- --------------------
PRIMARY PHYSICAL STANDBY MOUNTED
PRIMARY PHYSICAL STANDBY MOUNTED
[Logical-1] SQL> select name, database_role, open_mode from gv$database;
NAME DATABASE_ROLE OPEN_MODE
------------------------------ ---------------- --------------------
LOGICAL LOGICAL STANDBY READ WRITE
LOGICAL LOGICAL STANDBY READ WRITE
b、 在新的主数据库中执行切换操作，并查看是否处理了日志。
[Physical-1] SQL> alter system switch logfile;
System altered.
[Physical-1] SQL> alter system switch logfile;
System altered.
[Physical-2] SQL> alter system switch logfile;
System altered.
[Physical-2] SQL> alter system switch logfile;
System altered.
[Physical-1] SQL> select max(sequence#),thread# from v$archived_log group by thread#;
MAX(SEQUENCE#) THREAD#
-------------- ----------
681 1
430 2
[Primary-1] SQL> select max(sequence#),thread# from v$archived_log group by thread#;
MAX(SEQUENCE#) THREAD#
-------------- ----------
681 1
430 2
[Logical-1] SQL> SELECT max(sequence#),thread#,applied FROM dba_logstdby_log group by thread#, applied;
MAX(SEQUENCE#) THREAD# APPLIED
-------------- ---------- --------
681 1 YES
430 2 YES
c、 我们检查重做传输和应用进程是否正常工作。
[Primary-1] SQL> SELECT process, status, group#, thread#, sequence# FROM v$managed_standby order by process, group#, thread#, sequence#;
PROCESS STATUS GROUP# THREAD# SEQUENCE#
--------- ------------ ---------- ---------- ----------
ARCH CLOSING 5 1 678
ARCH CLOSING 5 1 680
ARCH CLOSING 6 1 681
ARCH CLOSING 8 2 428
ARCH CLOSING 8 2 430
ARCH CLOSING 9 2 429
ARCH CONNECTED N/A 0 0
ARCH CONNECTED N/A 0 0
MRP0 APPLYING_LOG N/A 2 431
RFS IDLE 2 1 682
RFS IDLE 4 2 431
PROCESS STATUS GROUP# THREAD# SEQUENCE#
--------- ------------ ---------- ---------- ----------
RFS IDLE N/A 0 0
RFS IDLE N/A 0 0
RFS IDLE N/A 0 0
RFS IDLE N/A 0 0
RFS IDLE N/A 0 0
RFS IDLE N/A 0 0
17 rows selected.
[Physical-1] SQL> column group# format a10
[Physical-1] SQL> SELECT process, status, group#, thread#, sequence# FROM v$managed_standby order by process, group#, thread#, sequence#;
PROCESS STATUS GROUP# THREAD# SEQUENCE#
--------- ------------ ---------- ---------- ----------
ARCH CLOSING 1 1 679
ARCH CLOSING 1 1 681
ARCH CLOSING 2 1 680
ARCH CONNECTED N/A 0 0
ARCH CONNECTED N/A 0 0
ARCH CLOSING N/A 1 678
ARCH CLOSING N/A 1 679
ARCH CLOSING N/A 1 679
LNS WRITING 2 1 682
LNS WRITING 2 1 682
10 rows selected.
[Logical-1] SQL> column spid format a7
[Logical-1] SQL> column type format a12
[Logical-1] SQL> column status format a57
[Logical-1] SQL> set linesize 500
[Logical-1] SQL> select sid, serial#, spid, type, status from v$logstdby_process;
SID SERIAL# SPID TYPE STATUS
---------- ---------- ------- ------------ ---------------------------------------------------------
79 3 7672 COORDINATOR ORA-16116: no work available
71 9 7694 ANALYZER ORA-16116: no work available
143 1 7698 APPLIER ORA-16116: no work available
202 1 7702 APPLIER ORA-16116: no work available
19 1 7711 APPLIER ORA-16116: no work available
81 1 7715 APPLIER ORA-16116: no work available
145 1 7719 APPLIER ORA-16116: no work available
141 5 7682 READER ORA-16242: Processing log file (thread# 1, sequence# 682)
200 11 7686 BUILDER ORA-16116: no work available
15 3 7690 PREPARER ORA-16116: no work available
10 rows selected.
[Logical-2] SQL> column spid format a7
[Logical-2] SQL> column type format a12
[Logical-2] SQL> column status format a57
[Logical-2] SQL> set linesize 500
[Logical-2] SQL> select sid, serial#, spid, type, status from v$logstdby_process;
no rows selected
d、 我们检查新的物理备用数据库中是否存在间隙。
[Primary-1] SQL> set linesize 9000
[Primary-1] SQL> column name format a25
[Primary-1] SQL> column value format a20
[Primary-1] SQL> column time_computed format a25
[Primary-1] SQL> SELECT name, value, time_computed FROM v$dataguard_stats;
NAME VALUE TIME_COMPUTED
------------------------- -------------------- -------------------------
transport lag +00 00:00:00 01/19/2017 14:51:03
apply lag +00 00:00:00 01/19/2017 14:51:03
apply finish time +00 00:00:00.000 01/19/2017 14:51:03
estimated startup time 27 01/19/2017 14:51:03
e、 我们检查存档位置的有效性。
[Physical-1] SQL> SELECT dest_id,valid_type,valid_role,valid_now FROM v$archive_dest;
DEST_ID VALID_TYPE VALID_ROLE VALID_NOW
---------- --------------- ------------ ----------------
1 ALL_LOGFILES ALL_ROLES YES
2 ALL_LOGFILES PRIMARY_ROLE YES
3 ALL_LOGFILES PRIMARY_ROLE YES
[Primary-1] SQL> SELECT dest_id,valid_type,valid_role,valid_now FROM v$archive_dest;
DEST_ID VALID_TYPE VALID_ROLE VALID_NOW
---------- --------------- ------------ ----------------
1 ALL_LOGFILES ALL_ROLES YES
2 ALL_LOGFILES PRIMARY_ROLE WRONG VALID_ROLE
3 ALL_LOGFILES PRIMARY_ROLE WRONG VALID_ROLE
虽然切换前Physical-1中2和3的DEST_ ID是错误的VALID_。当然，原因是角色的转变。
f、 最后，我们对数据库进行了总体检查。
[Physical-1] SQL> set linesize 9000
[Physical-1] SQL> select d.open_mode, d.database_role, a.database_mode, a.recovery_mode, a.protection_mode from v$archive_dest_status a, v$database d where a.database_mode <> 'UNKNOWN';
OPEN_MODE DATABASE_ROLE DATABASE_MODE RECOVERY_MODE PROTECTION_MODE
-------------------- ---------------- --------------- ----------------------- --------------------
READ WRITE PRIMARY OPEN IDLE MAXIMUM PERFORMANCE
READ WRITE PRIMARY OPEN_READ-ONLY MANAGED REAL TIME APPLY MAXIMUM PERFORMANCE
READ WRITE PRIMARY OPEN LOGICAL REAL TIME APPLY MAXIMUM PERFORMANCE
原文标题：Switchover from Primary Database to Physical Standby Database with SQLPLUS Commands
原文作者：Onur ARDAHANLI
原文链接：https://dbtut.com/index.php/2022/04/05/switchover-from-primary-database-to-physical-standby-database-with-sqlplus-commands
最后修改时间：2022-08-18 16:34:05
「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容，转载时必须标注文章的来源（墨天轮），文章链接，文章作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。
