溫馨提示×
您好，登錄后才能下訂單哦！
點擊 登錄注冊 即表示同意《億速云用戶服務條款》
您好，登錄后才能下訂單哦！
環境準備
接著上一篇文章 Oracle11g Data Guard物理備用數據庫搭建與配置(第1部分 主數據庫實例創建)
在我的測試環境中，我準備了兩臺CentOS7.4虛擬機，并同時都安裝了Oracle11gR2的11.2.0.1.0企業版的數據庫軟件，其中只有主服務器創建一個數據庫實例，備用服務器僅安裝Oracle數據庫軟件。
主數據庫:
OS: CentOS7.4
Hostname: hmdb11dg-db1
Oracle Version: 11.2.0.1.0
Oracle SID: HMDG(使用DBCA工具創建的一個數據庫)
備數據庫:
OS: CentOS7.4
Hostname: hmdb11dg-db2
Oracle Version: 11.2.0.1.0
Oracle SID: HMDG2
注意: 在開始之前，備用服務上還沒有將要與主服務器同步備份的數據庫實例。
一、主服務器前提配置
1. 啟用歸檔日志
檢查主數據是否處于歸檔日志模式
SQL> select log_mode from v$database; LOG_MODE ------------ ARCHIVELOG
如果是NOARCHIVELOG模式，則將其修改為ARCHIVELOG模式
SQL> SHUTDOWN IMMEDIATE; SQL> STARTUP MOUNT; SQL> ALTER DATABASE ARCHIVELOG; SQL> ALTER DATABASE OPEN;
2. 啟用強制日志
SQL> ALTER DATABASE FORCE LOGGING; Database altered. SQL> SELECT NAME,FORCE_LOGGING FROM V$DATABASE; NAME FOR --------- --- HMDG YES
3. 創建備用重做日志
備用重做日志文件的大小要與當前主數據庫的在線重做日志文件大小完全匹配。
確定備用重做日志文件組的數量，建議的數量：(每個線程最大的日志數 + 1) * 最大線程數
查看聯機重做日志
SQL> set pagesize 100 SQL> set linesize 200 SQL> col GROUP# format 99 SQL> col STATUS format a10 SQL> col TYPE format a10 SQL> col MEMBER format a50 SQL> col IS_RECOVERY_DEST_FILE format a10 SQL> select * from v$logfile; GROUP# STATUS TYPE MEMBER IS_RECOVER ------ ---------- ---------- -------------------------------------------------- ---------- 1 ONLINE /u01/app/oracle/oradata/HMDG/redo01.log NO 2 ONLINE /u01/app/oracle/oradata/HMDG/redo02.log NO 3 ONLINE /u01/app/oracle/oradata/HMDG/redo03.log NO 4 ONLINE /u01/app/oracle/oradata/HMDG/redo04.log NO
這里將創建10組備用重做日志
ALTER DATABASE ADD STANDBY LOGFILE GROUP 5 '/u01/app/oracle/oradata/HMDG/redo05.log' SIZE 500M; ALTER DATABASE ADD STANDBY LOGFILE GROUP 6 '/u01/app/oracle/oradata/HMDG/redo06.log' SIZE 500M; ALTER DATABASE ADD STANDBY LOGFILE GROUP 7 '/u01/app/oracle/oradata/HMDG/redo07.log' SIZE 500M; ALTER DATABASE ADD STANDBY LOGFILE GROUP 8 '/u01/app/oracle/oradata/HMDG/redo08.log' SIZE 500M; ALTER DATABASE ADD STANDBY LOGFILE GROUP 9 '/u01/app/oracle/oradata/HMDG/redo09.log' SIZE 500M; ALTER DATABASE ADD STANDBY LOGFILE GROUP 10 '/u01/app/oracle/oradata/HMDG/redo10.log' SIZE 500M; ALTER DATABASE ADD STANDBY LOGFILE GROUP 11 '/u01/app/oracle/oradata/HMDG/redo11.log' SIZE 500M; ALTER DATABASE ADD STANDBY LOGFILE GROUP 12 '/u01/app/oracle/oradata/HMDG/redo12.log' SIZE 500M; ALTER DATABASE ADD STANDBY LOGFILE GROUP 13 '/u01/app/oracle/oradata/HMDG/redo13.log' SIZE 500M; ALTER DATABASE ADD STANDBY LOGFILE GROUP 14 '/u01/app/oracle/oradata/HMDG/redo14.log' SIZE 500M;
查看備用重做日志
SQL> SELECT GROUP#,THREAD#,SEQUENCE#,ARCHIVED,STATUS FROM V$STANDBY_LOG; GROUP# THREAD# SEQUENCE# ARC STATUS ------ ---------- ---------- --- ---------- 5 0 0 YES UNASSIGNED 6 0 0 YES UNASSIGNED 7 0 0 YES UNASSIGNED 8 0 0 YES UNASSIGNED 9 0 0 YES UNASSIGNED 10 0 0 YES UNASSIGNED 11 0 0 YES UNASSIGNED 12 0 0 YES UNASSIGNED 13 0 0 YES UNASSIGNED 14 0 0 YES UNASSIGNED 10 rows selected.
4. 開啟閃回日志
SQL> alter database flashback on; SQL> select flashback_on from v$database; FLASHBACK_ON ------------------ YES
二、監聽與TNS配置
1. 監聽配置
主備數據庫必須注冊靜態監聽服務(listener.ora)
$ cat /u01/app/oracle/product/11.2.0/db_1/network/admin/listener.ora LISTENER = (DESCRIPTION_LIST = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = hmdb11dg-db1)(PORT = 1521)) ) ) SID_LIST_LISTENER = (SID_LIST = (SID_DESC = (GLOBAL_DBNAME = HMDG.DB) (ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_1) (SID_NAME = HMDG) ) ) ADR_BASE_LISTENER = /u01/app/oracle #備數據庫 LISTENER = (DESCRIPTION_LIST = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = hmdb11dg-db2)(PORT = 1521)) ) ) SID_LIST_LISTENER = (SID_LIST = (SID_DESC = (GLOBAL_DBNAME = HMDG2.DB) (ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_1) (SID_NAME = HMDG2) ) ) ADR_BASE_LISTENER = /u01/app/oracle
2. 主備數據庫TNS別名連接信息配置
修改兩臺服務器上的$ORACLE_HOME/network/admin/tnsnames.ora配置文件，主備使用相同的配置
HMDG = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = hmdb11dg-db1)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = HMDG.DB) ) ) HMDG2 = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = hmdb11dg-db2)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = HMDG2.DB) ) )
3. 測試使用TNS別名連接數據庫
[oracle@hmdb11dg-db1 ~]$ sqlplus system/oracle@HMDG SQL*Plus: Release 11.2.0.1.0 Production on Thu Mar 22 20:47:28 2018 Copyright (c) 1982, 2009, Oracle. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options SQL>
三、主數據庫初始化參數配置
1. 創建PFILE參數文件
使用如下語句創建PFILE配置文件，該文件自動生成在/u01/app/oracle/product/11.2.0/db_1/dbs目錄下
SQL> CREATE PFILE FROM SPFILE;
2. 修改PFILE配置
使用剛剛生成的PFILE文件，修改如下配置
HMDG.__db_cache_size=1023410176 HMDG.__java_pool_size=16777216 HMDG.__large_pool_size=16777216 HMDG.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment HMDG.__pga_aggregate_target=1325400064 HMDG.__sga_target=1962934272 HMDG.__shared_io_pool_size=0 HMDG.__shared_pool_size=872415232 HMDG.__streams_pool_size=0 *.audit_file_dest='/u01/app/oracle/admin/HMDG/adump' *.audit_trail='db' *.compatible='11.2.0.0.0' *.control_files='/u01/app/oracle/oradata/HMDG/control01.ctl','/u01/app/oracle/flash_recovery_area/HMDG/control02.ctl' *.db_block_size=8192 *.db_domain='' *.db_name='HMDG' *.db_unique_name='HMDG' *.log_archive_config='DG_CONFIG=(HMDG,HMDG2)' LOG_ARCHIVE_DEST_1= 'LOCATION=/u01/app/oracle/oradata/HMDG/ VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=HMDG' LOG_ARCHIVE_DEST_2= 'SERVICE=HMDG2 ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=HMDG2' LOG_ARCHIVE_DEST_STATE_1=ENABLE LOG_ARCHIVE_DEST_STATE_2=ENABLE LOG_ARCHIVE_MAX_PROCESSES=30 FAL_SERVER=HMDG2 FAL_CLIENT=HMDG DB_FILE_NAME_CONVERT='HMDG2','HMDG' LOG_FILE_NAME_CONVERT='/u01/app/oracle/oradata/HMDG2/','/u01/app/oracle/oradata/HMDG/' STANDBY_FILE_MANAGEMENT=AUTO *.db_recovery_file_dest='/u01/app/oracle/flash_recovery_area' *.db_recovery_file_dest_size=21474836480 *.diagnostic_dest='/u01/app/oracle' *.dispatchers='(PROTOCOL=TCP) (SERVICE=HMDGXDB)' *.log_archive_format='%t_%s_%r.dbf' *.memory_target=3277848576 *.open_cursors=300 *.processes=5000 *.remote_login_passwordfile='EXCLUSIVE' *.sessions=5505 *.undo_tablespace='UNDOTBS1'
3. 使用剛剛創建的PFILE文件重新啟動數據庫，并且重新創建一個SPFILE文件
SQL> shutdown immediate Database closed. Database dismounted. ORACLE instance shut down. SQL> startup pfile='/u01/app/oracle/product/11.2.0/db_1/dbs/initHMDG.ora'; ORACLE instance started. Total System Global Area 3273641984 bytes Fixed Size 2217792 bytes Variable Size 2315258048 bytes Database Buffers 939524096 bytes Redo Buffers 16642048 bytes Database mounted. Database opened. SQL> create spfile from pfile='/u01/app/oracle/product/11.2.0/db_1/dbs/initHMDG.ora'; #創建SPFILE文件 File created. SQL> shutdown immediate Database closed. Database dismounted. ORACLE instance shut down. SQL> startup ORACLE instance started. Total System Global Area 3273641984 bytes Fixed Size 2217792 bytes Variable Size 2315258048 bytes Database Buffers 939524096 bytes Redo Buffers 16642048 bytes Database mounted. Database opened.
注意：一旦由于PFILE參數配置信息錯誤或者使用PFILE文件啟動報錯需要修改PFILE文件參數的時候，都必須要重新創建SPFILE文件。(主備數據庫都是一樣)
4. 備份數據庫(在這里是非必須的，因為在本次操作中我使用的是RMAN復制到備用數據庫)
[oracle@hmdb11dg-db1 dbs]$ rman target = / Recovery Manager: Release 11.2.0.1.0 - Production on Thu Mar 22 21:12:06 2018 Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved. connected to target database: HMDG (DBID=787192145) RMAN> BACKUP DATABASE PLUS ARCHIVELOG;
5. 創建備用數據庫控制文件
SQL> ALTER DATABASE CREATE STANDBY CONTROLFILE AS '/tmp/HMDG2.ctl';
6. 創建備用數據庫的PFILE文件
SQL> CREATE PFILE='/tmp/initHMDG2.ora' FROM SPFILE;
7. 創建備用數據的密碼文件
[oracle@hmdb11dg-db1 ~]$ cp /u01/app/oracle/product/11.2.0/db_1/dbs/orapwHMDG /tmp/orapwHMDG2
四、備用數據庫服務器設置
1. 備用數據庫上創建必要的數據庫文件目錄
$ mkdir /u01/app/oracle/admin
$ mkdir /u01/app/oracle/admin/HMDG2
$ mkdir /u01/app/oracle/admin/HMDG2/{adump,dpdump,pfile,scripts}
$ mkdir -p /u01/app/oracle/oradata/HMDG2
$ mkdir -p /u01/app/oracle/flash_recovery_area/HMDG22. 從主數據庫上拷貝配置文件到備用數據庫(備用服務器上操作)
將控制文件、參數文件和密碼文件從主服務器上復制到備用服務器上
$ scp oracle@hmdb11dg-db1:/tmp/HMDG2.ctl /u01/app/oracle/oradata/HMDG2/control01.ctl $ cp /u01/app/oracle/oradata/HMDG2/control01.ctl /u01/app/oracle/flash_recovery_area/HMDG2/control02.ctl $ scp oracle@hmdb11dg-db1:/tmp/orapwHMDG2 /u01/app/oracle/product/11.2.0/db_1/dbs/orapwHMDG2 $ scp oracle@hmdb11dg-db1:/tmp/initHMDG2.ora /u01/app/oracle/product/11.2.0/db_1/dbs/initHMDG2.ora
3. 修改備用數據庫初始化參數
修改備用服務器的PFILE文件$ORACLE_HOME/dbs/initHMDG2.ora
HMDG.__db_cache_size=939524096 HMDG.__java_pool_size=16777216 HMDG.__large_pool_size=16777216 HMDG.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment HMDG.__pga_aggregate_target=1325400064 HMDG.__sga_target=1962934272 HMDG.__shared_io_pool_size=0 HMDG.__shared_pool_size=956301312 HMDG.__streams_pool_size=0 *.audit_file_dest='/u01/app/oracle/admin/HMDG2/adump' *.audit_trail='db' *.compatible='11.2.0.0.0' *.control_files='/u01/app/oracle/oradata/HMDG2/control01.ctl','/u01/app/oracle/flash_recovery_area/HMDG2/control02.ctl' *.db_block_size=8192 *.db_domain='' *.DB_FILE_NAME_CONVERT='HMDG','HMDG2' *.db_name='HMDG' *.db_recovery_file_dest='/u01/app/oracle/flash_recovery_area' *.db_recovery_file_dest_size=21474836480 *.db_unique_name='HMDG2' *.diagnostic_dest='/u01/app/oracle' *.dispatchers='(PROTOCOL=TCP) (SERVICE=HMDGXDB)' *.FAL_CLIENT='HMDG2' *.FAL_SERVER='HMDG' *.log_archive_config='DG_CONFIG=(HMDG,HMDG2)' *.LOG_ARCHIVE_DEST_1='LOCATION=/u01/app/oracle/oradata/HMDG2/ VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=HMDG2' *.LOG_ARCHIVE_DEST_2='SERVICE=HMDG ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=HMDG' *.LOG_ARCHIVE_DEST_STATE_1='ENABLE' *.LOG_ARCHIVE_DEST_STATE_2='ENABLE' *.log_archive_format='%t_%s_%r.dbf' *.LOG_ARCHIVE_MAX_PROCESSES=30 *.LOG_FILE_NAME_CONVERT='/u01/app/oracle/oradata/HMDG/','/u01/app/oracle/oradata/HMDG2/' *.memory_target=3277848576 *.open_cursors=300 *.processes=5000 *.remote_login_passwordfile='EXCLUSIVE' *.sessions=5505 *.STANDBY_FILE_MANAGEMENT='AUTO' *.undo_tablespace='UNDOTBS1'
4. 使用剛剛創建的PFILE參數文件啟動備用數據庫
[oracle@hmdb11dg-db2 ~]$ sqlplus / as sysdba SQL*Plus: Release 11.2.0.1.0 Production on Thu Mar 22 21:42:24 2018 Copyright (c) 1982, 2009, Oracle. All rights reserved. Connected to an idle instance. SQL> STARTUP NOMOUNT PFILE='/u01/app/oracle/product/11.2.0/db_1/dbs/initHMDG2.ora'; ORACLE instance started. Total System Global Area 3273641984 bytes Fixed Size 2217792 bytes Variable Size 2197817536 bytes Database Buffers 1056964608 bytes Redo Buffers 16642048 bytes
5. 創建SPFILE文件
SQL> CREATE SPFILE FROM PFILE; File created.
注意：一旦由于PFILE參數配置信息錯誤或者使用PFILE文件啟動報錯需要修改PFILE文件參數的時候，都必須要重新使用該語句創建SPFILE文件。(主備數據庫都是一樣)
五、在備用數據庫上使用rman恢復數據到備用數據庫(DUPLICATE)
1. 建立連接
[oracle@hmdb11dg-db2 ~]$ rman TARGET sys/oracle@HMDG AUXILIARY sys/oracle@HMDG2 Recovery Manager: Release 11.2.0.1.0 - Production on Thu Mar 22 21:46:42 2018 Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved. connected to target database: HMDG (DBID=787192145) connected to auxiliary database: HMDG (not mounted)
2. 將主數據庫復制到備用數據庫
在RMAN模式下，發出以下語句將主數據庫復制到備數據庫
DUPLICATE TARGET DATABASE FOR STANDBY FROM ACTIVE DATABASE;
[oracle@hmdb11dg-db2 dbs]$ rman TARGET sys/oracle@HMDG AUXILIARY sys/oracle@HMDG2
Recovery Manager: Release 11.2.0.1.0 - Production on Fri Mar 23 09:50:19 2018
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
connected to target database: HMDG (DBID=787214401)
connected to auxiliary database: HMDG (not mounted)
RMAN> DUPLICATE TARGET DATABASE FOR STANDBY FROM ACTIVE DATABASE;
Starting Duplicate Db at 23-MAR-18
using target database control file instead of recovery catalog
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=4708 device type=DISK
contents of Memory Script:
{
backup as copy reuse
targetfile '/u01/app/oracle/product/11.2.0/db_1/dbs/orapwHMDG' auxiliary format
'/u01/app/oracle/product/11.2.0/db_1/dbs/orapwHMDG2' ;
}
executing Memory Script
Starting backup at 23-MAR-18
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=3773 device type=DISK
Finished backup at 23-MAR-18
contents of Memory Script:
{
backup as copy current controlfile for standby auxiliary format '/u01/app/oracle/oradata/HMDG2/control01.ctl';
restore clone controlfile to '/u01/app/oracle/flash_recovery_area/HMDG2/control02.ctl' from
'/u01/app/oracle/oradata/HMDG2/control01.ctl';
}
executing Memory Script
Starting backup at 23-MAR-18
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile copy
copying standby control file
output file name=/u01/app/oracle/product/11.2.0/db_1/dbs/snapcf_HMDG.f tag=TAG20180323T095223 RECID=3 STAMP=971517146
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:15
Finished backup at 23-MAR-18
Starting restore at 23-MAR-18
using channel ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: copied control file copy
Finished restore at 23-MAR-18
contents of Memory Script:
{
sql clone 'alter database mount standby database';
}
executing Memory Script
sql statement: alter database mount standby database
contents of Memory Script:
{
set newname for tempfile 1 to
"/u01/app/oracle/oradata/HMDG2/temp01.dbf";
switch clone tempfile all;
set newname for datafile 1 to
"/u01/app/oracle/oradata/HMDG2/system01.dbf";
set newname for datafile 2 to
"/u01/app/oracle/oradata/HMDG2/sysaux01.dbf";
set newname for datafile 3 to
"/u01/app/oracle/oradata/HMDG2/undotbs01.dbf";
set newname for datafile 4 to
"/u01/app/oracle/oradata/HMDG2/users01.dbf";
backup as copy reuse
datafile 1 auxiliary format
"/u01/app/oracle/oradata/HMDG2/system01.dbf" datafile
2 auxiliary format
"/u01/app/oracle/oradata/HMDG2/sysaux01.dbf" datafile
3 auxiliary format
"/u01/app/oracle/oradata/HMDG2/undotbs01.dbf" datafile
4 auxiliary format
"/u01/app/oracle/oradata/HMDG2/users01.dbf" ;
sql 'alter system archive log current';
}
executing Memory Script
executing command: SET NEWNAME
renamed tempfile 1 to /u01/app/oracle/oradata/HMDG2/temp01.dbf in control file
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
Starting backup at 23-MAR-18
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile copy
input datafile file number=00003 name=/u01/app/oracle/oradata/HMDG/undotbs01.dbf
output file name=/u01/app/oracle/oradata/HMDG2/undotbs01.dbf tag=TAG20180323T095246
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:01:15
channel ORA_DISK_1: starting datafile copy
input datafile file number=00001 name=/u01/app/oracle/oradata/HMDG/system01.dbf
output file name=/u01/app/oracle/oradata/HMDG2/system01.dbf tag=TAG20180323T095246
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:35
channel ORA_DISK_1: starting datafile copy
input datafile file number=00002 name=/u01/app/oracle/oradata/HMDG/sysaux01.dbf
output file name=/u01/app/oracle/oradata/HMDG2/sysaux01.dbf tag=TAG20180323T095246
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:25
channel ORA_DISK_1: starting datafile copy
input datafile file number=00004 name=/u01/app/oracle/oradata/HMDG/users01.dbf
output file name=/u01/app/oracle/oradata/HMDG2/users01.dbf tag=TAG20180323T095246
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:15
Finished backup at 23-MAR-18
sql statement: alter system archive log current
contents of Memory Script:
{
switch clone datafile all;
}
executing Memory Script
datafile 1 switched to datafile copy
input datafile copy RECID=3 STAMP=971517318 file name=/u01/app/oracle/oradata/HMDG2/system01.dbf
datafile 2 switched to datafile copy
input datafile copy RECID=4 STAMP=971517318 file name=/u01/app/oracle/oradata/HMDG2/sysaux01.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=5 STAMP=971517318 file name=/u01/app/oracle/oradata/HMDG2/undotbs01.dbf
datafile 4 switched to datafile copy
input datafile copy RECID=6 STAMP=971517319 file name=/u01/app/oracle/oradata/HMDG2/users01.dbf
Finished Duplicate Db at 23-MAR-18
如果復制過程中沒有報錯，就可以立即開啟日志重做應用
3. 開啟日志重做應用
發出如下命令指示備庫開始使用備用日志進行在備庫上同步數據(恢復數據)
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION; #或者 SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT FROM SESSION; #取消申請重做(該命令用于停止恢復) SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
4. 測試備用數據的存檔操作
默認情況下，當在線重做日志文件變滿時，會發送日志切換。要強制進行日志切換以便立即傳輸重做數據，則在主數據庫上使用如下語句強制日志切換
SQL> ALTER SYSTEM SWITCH LOGFILE; System altered.
5. 在備用數據庫上查詢現有的歸檔重做日志文件
SQL> SELECT SEQUENCE#, FIRST_TIME, NEXT_TIME FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#; SEQUENCE# FIRST_TIM NEXT_TIME ---------- --------- --------- 19 23-MAR-18 23-MAR-18 20 23-MAR-18 23-MAR-18 21 23-MAR-18 23-MAR-18 22 23-MAR-18 23-MAR-18
6. 再次在數據庫上強制日志切換
SQL> ALTER SYSTEM SWITCH LOGFILE; System altered.
7. 驗證在備用數據庫中是否接收了新的重做日志
SQL> SELECT SEQUENCE#, FIRST_TIME, NEXT_TIME FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#; SEQUENCE# FIRST_TIM NEXT_TIME ---------- --------- --------- 19 23-MAR-18 23-MAR-18 20 23-MAR-18 23-MAR-18 21 23-MAR-18 23-MAR-18 22 23-MAR-18 23-MAR-18 23 23-MAR-18 23-MAR-18
8. 驗證備用數據庫是否應用了新的重做日志
SQL> SELECT SEQUENCE#,APPLIED FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#; SEQUENCE# APPLIED ---------- --------- 19 YES 20 YES 21 YES 22 YES 23 YES
9. 查詢主備狀態
#在當前主數據庫中查詢 SQL> SELECT SWITCHOVER_STATUS FROM V$DATABASE; SWITCHOVER_STATUS -------------------- TO STANDBY #在當前備用數據庫中查詢 SQL> SELECT SWITCHOVER_STATUS FROM V$DATABASE; SWITCHOVER_STATUS -------------------- NOT ALLOWED
此時，說明當前主備數據庫狀態都為正常。其中主數據狀態TO STANDBY說明可以隨時切換為備用數據庫。
五、手動切換測試(主備切換)
在主數據庫(DB1)上操作
在當前主數據庫中查詢主備狀態
SQL> SELECT SWITCHOVER_STATUS FROM V$DATABASE; SWITCHOVER_STATUS -------------------- TO STANDBY
當主數據庫的狀態為TO STANDBY時，表示可以切換到備用數據庫
在當前主數據庫(DB1)上發出切換到備數據庫角色申請
SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO PHYSICAL STANDBY WITH SESSION SHUTDOWN; Database altered.
在備數據庫(DB2)上操作
此時查看備數據庫(DB2)的狀態
SQL> SELECT SWITCHOVER_STATUS FROM V$DATABASE; SWITCHOVER_STATUS -------------------- TO PRIMARY
當此時備數據庫的狀態為TO PRIMARY時，表示可以切換到主數據庫角色
在當前備數據庫(DB2)上發出切換到主數據庫角色申請
SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY WITH SESSION SHUTDOWN; Database altered.
繼續在備數據(DB2)上完成以下指令
SQL> ALTER DATABASE OPEN; -- 或者 SQL> SHUTDOWN IMMEDIATE; SQL> STARTUP;
在原來的主數據(DB1)上繼續執行以下命令
SQL> SHUTDOWN IMMEDIATE; ORA-01507: database not mounted ORACLE instance shut down. SQL> STARTUP NOMOUNT; ORACLE instance started. Total System Global Area 3273641984 bytes Fixed Size 2217792 bytes Variable Size 2264926400 bytes Database Buffers 989855744 bytes Redo Buffers 16642048 bytes SQL> ALTER DATABASE MOUNT STANDBY DATABASE; Database altered.
發出如下命令讓當前備庫(DB1)開始使用備用日志進行同步數據(恢復數據)
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT FROM SESSION; Database altered.
最后查看切換以后的主備狀態與主備角色
-- DB2上 SQL> SELECT SWITCHOVER_STATUS FROM V$DATABASE; SWITCHOVER_STATUS -------------------- TO STANDBY SQL> select database_role from v$database; DATABASE_ROLE ---------------- PHYSICAL STANDBY -- DB1上 SQL> SELECT SWITCHOVER_STATUS FROM V$DATABASE; SWITCHOVER_STATUS -------------------- NOT ALLOWED SQL> select database_role from v$database; DATABASE_ROLE ---------------- PRIMARY
以上狀態說明主備角色已經正常切換
注意：在正常切換后，主數據庫狀態可能需要等待幾分鐘時間才顯示為TO STANDBY狀態，這是因為主數據在執行日志切換，需要等待切換完成
在當前主數據庫(DB2)上強制日志切換
SQL> ALTER SYSTEM SWITCH LOGFILE; System altered.
至此主備切換測試完成
免責聲明：本站發布的內容（圖片、視頻和文字）以原創、轉載和分享為主，文章觀點不代表本網站立場，如果涉及侵權請聯系站長郵箱：is@yisu.com進行舉報，并提供相關證據，一經查實，將立刻刪除涉嫌侵權內容。
