Oracle12c DataGuard switchover的例子
12c dataguard提供了新的语法来实现dg的switchover.下面进行测试下。
主库执行检查
SQL> ALTER DATABASE SWITCHOVER TO orcldg VERIFY; --主库进行切换检查
ALTER DATABASE SWITCHOVER TO orcldg VERIFY
*
ERROR at line 1:
ORA-16475: succeeded WITH warnings, CHECK alert log FOR more details --查看alert日志
SQL>
alert
ALTER DATABASE SWITCHOVER TO orcldg VERIFY --命令输入
Sun May 10 18:31:29 2015
This is cascading configuration.
LOG_ARCHIVE_DEST_3 has already been configured to switchover target ORCLDG. Clearing LOG_ARCHIVE_DEST_3.--清空参数dest-3
Using STANDBY_ARCHIVE_DEST parameter default value as /backup/archivelog
Sun May 10 18:31:29 2015
ALTER SYSTEM SET log_archive_dest_3='' SCOPE=MEMORY SID='*';
LOG_ARCHIVE_DEST3 is cleared.
Configuring a new LOG_ARCHIVE_DEST to switchover target ORCLDG.
Using STANDBY_ARCHIVE_DEST parameter default value as /backup/archivelog
Sun May 10 18:31:29 2015
ALTER SYSTEM SET log_archive_dest_31='service=orcldg ASYNC db_unique_name=ORCLDG' SCOPE=MEMORY SID='*';
SWITCHOVER VERIFY: Send VERIFY request to switchover target ORCLDG
SWITCHOVER VERIFY COMPLETE
SWITCHOVER VERIFY WARNING: switchover target temporary files are not the same with the primary. See switchover target's alert log for details. --dg端没有临时文件，导致上面sql执行有问题,这里只为测试功能.这里忽略
Using STANDBY_ARCHIVE_DEST parameter default value as /backup/archivelog
Sun May 10 18:31:30 2015
ALTER SYSTEM SET log_archive_dest_31='' SCOPE=MEMORY SID='*';
ORA-16475 signalled during: ALTER DATABASE SWITCHOVER TO orcldg VERIFY...
Sun May 10 18:31:32 2015
LGWR: Standby redo logfile selected to archive thread 1 sequence 116
LGWR: Standby redo logfile selected for thread 1 sequence 116 for destination LOG_ARCHIVE_DEST_2
Sun May 10 18:31:32 2015
Changing destination 3 from remote to local during archival of log#: 1 sequence#: 115 thread#: 1
Sun May 10 18:31:32 2015
Thread 1 advanced to log sequence 116 (LGWR switch)
Current log# 2 seq# 116 mem# 0: /oradata/orcl/redo02.log
Sun May 10 18:31:32 2015
Archived Log entry 120 added for thread 1 sequence 115 ID 0x53634e8b dest 1:
切换
SQL> ALTER DATABASE SWITCHOVER TO orcldg; --正式切换
DATABASE altered.
主库日志
ALTER DATABASE SWITCHOVER TO orcldg --接受到切换命令
Sun May 10 18:33:19 2015
This is cascading configuration.
Sun May 10 18:33:19 2015
ALTER SYSTEM SET log_archive_dest_state_2='DEFER' SCOPE=MEMORY SID='*'; --修改dest_2
Configuring a new LOG_ARCHIVE_DEST to switchover target ORCLDG.
Using STANDBY_ARCHIVE_DEST parameter default value as /backup/archivelog
Sun May 10 18:33:19 2015
ALTER SYSTEM SET log_archive_dest_31='service=orcldg ASYNC db_unique_name=ORCLDG' SCOPE=MEMORY SID='*';--设置参数
Starting switchover [Process ID: 2105]
Sun May 10 18:33:20 2015
ALTER DATABASE COMMIT TO SWITCHOVER TO PHYSICAL STANDBY [Process Id: 2105] (orcl)--原命令切换到PHYSICAL STANDBY
Waiting for target standby to receive all redo
Sun May 10 18:33:20 2015
Waiting for all non-current ORLs to be archived...
Sun May 10 18:33:20 2015
All non-current ORLs have been archived.
Sun May 10 18:33:20 2015
Waiting for all FAL entries to be archived...
Sun May 10 18:33:20 2015
All FAL entries have been archived.
Sun May 10 18:33:20 2015
Waiting for dest_id 31 to become synchronized...
Sun May 10 18:33:20 2015
Thread 1 cannot allocate new log, sequence 117
Checkpoint not complete
Current log# 2 seq# 116 mem# 0: /oradata/orcl/redo02.log
Sun May 10 18:33:21 2015
Active, synchronized Physical Standby switchover target has been identified
Preventing updates and queries at the Primary
Generating and shipping final logs to target standby
Switchover End-Of-Redo Log thread 1 sequence 116 has been fixed
Switchover: Primary highest seen SCN set to 0x0.0x2b49d1 >>切换scn
ARCH: Noswitch archival of thread 1, sequence 116
ARCH: End-Of-Redo Branch archival of thread 1 sequence 116
ARCH: LGWR is scheduled to archive destination LOG_ARCHIVE_DEST_31 after log switch
ARCH: LGWR is actively archiving destination LOG_ARCHIVE_DEST_2
Sun May 10 18:33:23 2015
Process (ospid 2077) is suspended due to switchover to physical standby operation.
Sun May 10 18:33:25 2015
ARCH: Standby redo logfile selected for thread 1 sequence 116 for destination LOG_ARCHIVE_DEST_31
ARCH: Standby redo logfile selected for thread 1 sequence 116 for destination LOG_ARCHIVE_DEST_2
ARCH: Archiving is disabled due to current logfile archival
Primary will check for some target standby to have received all redo
Waiting for target standby to apply all redo
Backup controlfile written to trace file /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_2105.trc
Converting the primary database to a new standby database >>切换成dg
Clearing standby activation ID 1399017099 (0x53634e8b)
The primary database controlfile was created using the
'MAXLOGFILES 16' clause.
There is space for up to 13 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;
Archivelog for thread 1 sequence 116 required for standby recovery
Offline data file 5 marked as online during convert to standby or switchover to standby.
Restore of backup may be required if the file is not physically accessible.
Offline data file 7 marked as online during convert to standby or switchover to standby.
Restore of backup may be required if the file is not physically accessible.
Offline data file 21 marked as online during convert to standby or switchover to standby.
Restore of backup may be required if the file is not physically accessible.
Offline data file 22 marked as online during convert to standby or switchover to standby.
Restore of backup may be required if the file is not physically accessible.
Offline data file 23 marked as online during convert to standby or switchover to standby.
Restore of backup may be required if the file is not physically accessible.
Offline data file 24 marked as online during convert to standby or switchover to standby.
Restore of backup may be required if the file is not physically accessible.
Offline data file 25 marked as online during convert to standby or switchover to standby.
Restore of backup may be required if the file is not physically accessible.
Offline data file 26 marked as online during convert to standby or switchover to standby.
Restore of backup may be required if the file is not physically accessible.
Switchover: Primary controlfile converted to standby controlfile succesfully. >>控制文件切换成功
Switchover: Complete - Database shutdown required >>实例关闭
Sending request(convert to primary database) to switchover target ORCLDG >>发送命令到dg端
Switchover complete. Database shutdown required
USER (ospid: 2105): terminating the instance
Sun May 10 18:33:29 2015
Instance terminated by USER, pid = 2105
Completed: ALTER DATABASE SWITCHOVER TO orcldg
Shutting down instance (abort)
License high water mark = 17
Sun May 10 18:33:29 2015
Instance shutdown complete
Far sync 传输日志
Sun May 10 18:33:25 2015
Changing standby controlfile to MAXIMUM PERFORMANCE mode
RFS[3]: Assigned to RFS process (PID:2153)
RFS[3]: Selected log 4 for thread 1 sequence 116 dbid 1399018635 branch 871237903
Sun May 10 18:33:25 2015
Archived Log entry 30 added for thread 1 sequence 116 ID 0x53634e8b dest 1:
Sun May 10 18:33:29 2015
RFS[4]: Assigned to RFS process (PID:2131)
RFS[4]: Possible network disconnect with primary database
Sun May 10 18:33:29 2015
RFS[2]: Possible network disconnect with primary database
DataGuard端 alert日志
Sun May 10 18:33:26 2015
SWITCHOVER: received request 'ALTER DTABASE COMMIT TO SWITCHOVER TO PRIMARY' from primary database. >>接受到命令
Sun May 10 18:33:26 2015
ALTER DATABASE SWITCHOVER TO PRIMARY (orcl) >>进行切换
Maximum wait for role transition is 15 minutes. >>最多等待15分钟
Switchover: Media recovery is still active >>发现正在进行recover
Role Change: Canceling MRP - no more redo to apply >>关闭mrp
Sun May 10 18:33:26 2015
MRP0: Background Media Recovery cancelled with status 16037
Sun May 10 18:33:26 2015
Errors in file /u01/app/oracle/diag/rdbms/orcldg/orcl/trace/orcl_mrp0_2134.trc:
ORA-16037: user requested cancel of managed recovery operation
Managed Standby Recovery not using Real Time Apply
Recovery interrupted! >>recover 被中止
Sun May 10 18:33:26 2015
Errors in file /u01/app/oracle/diag/rdbms/orcldg/orcl/trace/orcl_mrp0_2134.trc:
ORA-16037: user requested cancel of managed recovery operation
Sun May 10 18:33:26 2015
MRP0: Background Media Recovery process shutdown (orcl)
Sun May 10 18:33:27 2015
Role Change: Canceled MRP >>关闭mrp
Killing 5 processes (PIDS:2186,2148,2146,2168,2170) (all RFS) in order to disallow current and future RFS connections. Requested by OS process 2197 >>kill rps进程
Stopping Emon pool
All dispatchers and shared servers shutdown
CLOSE: killing server sessions.
CLOSE: all sessions shutdown successfully.
Stopping Emon pool
Sun May 10 18:33:29 2015
SMON: disabling cache recovery
Sun May 10 18:33:29 2015
Backup controlfile written to trace file /u01/app/oracle/diag/rdbms/orcldg/orcl/trace/orcl_rmi_2197.trc >>备份控制文件
SwitchOver after complete recovery through change 2836945
Online logfile pre-clearing operation disabled by switchover >>清空redo
Online log /oradata/orcl/redo01.log: Thread 1 Group 1 was previously cleared
Online log /oradata/orcl/redo02.log: Thread 1 Group 2 was previously cleared
Online log /oradata/orcl/redo03.log: Thread 1 Group 3 was previously cleared
Standby became primary SCN: 2836943 >>成为主库的scn
AUDIT_TRAIL initialization parameter is changed back to its original value as specified in the parameter file.
Switchover: Complete - Database mounted as primary
SWITCHOVER: completed request from primary database. >>切换成功
Sun May 10 18:33:44 2015
ARC1: Becoming the 'no SRL' ARCH
Sun May 10 18:36:19 2015
Assigning activation ID 1407090153 (0x53de7de9) --开始打开数据库
LGWR: Primary database is in MAXIMUM AVAILABILITY mode
Sun May 10 18:36:19 2015
Destination LOG_ARCHIVE_DEST_2 is UNSYNCHRONIZED
LGWR: Destination LOG_ARCHIVE_DEST_1 is not serviced by LGWR
Sun May 10 18:36:19 2015
Thread 1 advanced to log sequence 118 (thread open)
Thread 1 opened at log sequence 118
Current log# 2 seq# 118 mem# 0: /oradata/orcl/redo02.log
Successful open of redo thread 1
Sun May 10 18:36:19 2015
MTTR advisory is disabled because FAST_START_MTTR_TARGET is not set
Sun May 10 18:36:19 2015
Archived Log entry 43 added for thread 1 sequence 117 ID 0x53de7de9 dest 1:
Sun May 10 18:36:19 2015
SMON: enabling cache recovery
Sun May 10 18:36:19 2015
[2219] Successfully onlined Undo Tablespace 2.
Undo initialization finished serial:0 start:525504 end:525654 diff:150 ms (0.2 seconds)
Dictionary check beginning
Dictionary check complete
Verifying minimum file header compatibility (11g) for tablespace encryption..
Verifying 11g file header compatibility for tablespace encryption completed
Sun May 10 18:36:19 2015
SMON: enabling tx recovery
Starting background process SMCO
Sun May 10 18:36:19 2015
Database Characterset is ZHS16GBK
Sun May 10 18:36:19 2015
SMCO started with pid=32, OS id=2226
No Resource Manager plan active
Starting background process IMCO
Sun May 10 18:36:19 2015
IMCO started with pid=35, OS id=2232
Starting background process AQPC
Sun May 10 18:36:20 2015
AQPC started with pid=36, OS id=2234
LOGSTDBY: Validating controlfile with logical metadata
LOGSTDBY: Validation complete
Sun May 10 18:36:20 2015
ARC1: STARTING ARCH PROCESSES
Starting background process ARC4
Sun May 10 18:36:20 2015
ARC4 started with pid=37, OS id=2236
ARC4: Archival started
ARC1: STARTING ARCH PROCESSES COMPLETE
Sun May 10 18:36:21 2015
Pluggable database PDB$SEED dictionary check beginning
Pluggable Database PDB$SEED Dictionary check complete
Database Characterset for PDB$SEED is ZHS16GBK
Sun May 10 18:36:21 2015
******************************************************************
LGWR: Setting 'active' archival for destination LOG_ARCHIVE_DEST_2
******************************************************************
LGWR: Standby redo logfile selected to archive thread 1 sequence 119
LGWR: Standby redo logfile selected for thread 1 sequence 119 for destination LOG_ARCHIVE_DEST_2
Sun May 10 18:36:22 2015
Thread 1 advanced to log sequence 119 (LGWR switch)
Current log# 3 seq# 119 mem# 0: /oradata/orcl/redo03.log
Shutting down archive processes
Sun May 10 18:36:22 2015
ARCH shutting down
ARC4: Archival stopped
Sun May 10 18:36:22 2015
Due to limited space in shared pool (need 6094848 bytes, have 3981120 bytes), limiting Resource Manager entities from 2048 to 32
Opening pdb PDB$SEED (2) with no Resource Manager plan active
Sun May 10 18:36:23 2015
Archived Log entry 45 added for thread 1 sequence 118 ID 0x53de7de9 dest 1:
Sun May 10 18:36:23 2015
Errors in file /u01/app/oracle/diag/rdbms/orcldg/orcl/trace/orcl_dbw0_2044.trc:
ORA-01157: cannot identify/lock data file 203 - see DBWR trace file
ORA-01110: data file 203: '/oradata/orcl/travel/travel_temp012015-03-19_01-30-29-PM.dbf'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
Sun May 10 18:36:23 2015
Errors in file /u01/app/oracle/diag/rdbms/orcldg/orcl/trace/orcl_dbw0_2044.trc:
ORA-01186: file 203 failed verification tests
ORA-01157: cannot identify/lock data file 203 - see DBWR trace file
ORA-01110: data file 203: '/oradata/orcl/travel/travel_temp012015-03-19_01-30-29-PM.dbf'
Sun May 10 18:36:23 2015
File 203 not verified due to error ORA-01157
Sun May 10 18:36:23 2015
ARC0: Standby redo logfile selected for thread 1 sequence 118 for destination LOG_ARCHIVE_DEST_2
Sun May 10 18:36:23 2015
Pluggable database TRAVEL dictionary check beginning
Sun May 10 18:36:23 2015
Pluggable database PDB03 dictionary check beginning
Pluggable Database PDB03 Dictionary check complete
Sun May 10 18:36:23 2015
Pluggable Database TRAVEL Dictionary check complete
Sun May 10 18:36:23 2015
Database Characterset for PDB03 is ZHS16GBK
Sun May 10 18:36:23 2015
Database Characterset for TRAVEL is ZHS16GBK
Sun May 10 18:36:23 2015
********************************************************************* --临时表空间不存在问题
WARNING: The following temporary tablespaces in container(PDB03)
contain no files.
This condition can occur when a backup controlfile has
been restored. It may be necessary to add files to these
tablespaces. That can be done using the SQL statement:
ALTER TABLESPACE
Alternatively, if these temporary tablespaces are no longer
needed, then they can be dropped.
Empty temporary tablespace: TEMP
*********************************************************************
Sun May 10 18:36:24 2015
Errors in file /u01/app/oracle/diag/rdbms/orcldg/orcl/trace/orcl_dbw0_2044.trc:
ORA-01157: cannot identify/lock data file 203 - see DBWR trace file
ORA-01110: data file 203: '/oradata/orcl/travel/travel_temp012015-03-19_01-30-29-PM.dbf'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
Sun May 10 18:36:24 2015
Errors in file /u01/app/oracle/diag/rdbms/orcldg/orcl/trace/orcl_dbw0_2044.trc:
ORA-01186: file 203 failed verification tests
ORA-01157: cannot identify/lock data file 203 - see DBWR trace file
ORA-01110: data file 203: '/oradata/orcl/travel/travel_temp012015-03-19_01-30-29-PM.dbf'
Sun May 10 18:36:24 2015
File 203 not verified due to error ORA-01157
Sun May 10 18:36:24 2015
Re-creating tempfile /oradata/orcl/travel/travel_temp012015-03-19_01-30-29-PM.dbf
ALTER SYSTEM: Flushing buffer cache inst=0 container=3 local
Sun May 10 18:36:24 2015
ALTER SYSTEM: Flushing buffer cache inst=0 container=4 local
Sun May 10 18:36:25 2015
Destination LOG_ARCHIVE_DEST_2 is SYNCHRONIZED
LGWR: Standby redo logfile selected to archive thread 1 sequence 120
LGWR: Standby redo logfile selected for thread 1 sequence 120 for destination LOG_ARCHIVE_DEST_2
Sun May 10 18:36:25 2015
Thread 1 advanced to log sequence 120 (LGWR switch)
Current log# 1 seq# 120 mem# 0: /oradata/orcl/redo01.log
Sun May 10 18:36:25 2015
Archived Log entry 48 added for thread 1 sequence 119 ID 0x53de7de9 dest 1:
Sun May 10 18:36:25 2015
Cannot start service travel, reason=-1
Cannot start service pdb03, reason=-1
Starting background process CJQ0
Completed: alter database open
Sun May 10 18:36:33 2015
CJQ0 started with pid=40, OS id=2289
查看新主库
SQL> SELECT name,database_role,db_unique_name,open_mode,protection_mode,protection_level,switchover_status,supplemental_log_data_pk,supplemental_log_data_ui FROM v$database;
NAME DATABASE_ROLE DB_UNIQUE_NAME OPEN_MODE PROTECTION_MODE PROTECTION_LEVEL SWITCHOVER_STATUS SUP SUP
--------- ---------------- ------------------------------ -------------------- -------------------- -------------------- -------------------- --- ---
ORCL PRIMARY orcldg MOUNTED MAXIMUM AVAILABILITY UNPROTECTED NOT ALLOWED NO NO
SQL> SELECT * FROM V$DATAGUARD_CONFIG;
DB_UNIQUE_NAME PARENT_DBUN DEST_ROLE CURRENT_SCN CON_ID
------------------------------ ------------------------------ ----------------- ----------- ----------
orcldg NONE PRIMARY DATABASE 0 0
orcl orcldg UNKNOWN 0 0
orclfs UNKNOWN UNKNOWN 0 0
SQL> SHOW parameter dest_2
PARAMETER_NAME TYPE VALUE
------------------------------------------------------------ ----------- ----------------------------------------------------------------------------------------------------
db_create_online_log_dest_2 string
log_archive_dest_2 string service=orcl LGWR SYNC affirm valid_for=(ONLINE_LOGFILES,PRIMARY_ROLE) db_unique_name=orcl
log_archive_dest_20 string
log_archive_dest_21 string
log_archive_dest_22 string
log_archive_dest_23 string
log_archive_dest_24 string
log_archive_dest_25 string
log_archive_dest_26 string
log_archive_dest_27 string
log_archive_dest_28 string
log_archive_dest_29 string
SQL> ALTER DATABASE OPEN;
DATABASE altered.
SQL>
SQL>
SQL>
