1設(shè)定環(huán)境如下：
Primary數(shù)據(jù)庫(kù)
| IP | 172.17.22.16 |
| SID | orcl |
Standby數(shù)據(jù)庫(kù)
| IP | 172.17.22.17 |
| SID | orcl |
設(shè)置提示，以區(qū)分操作的位置
primary數(shù)據(jù)庫(kù)
set SQLPROMPT Primary>
standby數(shù)據(jù)庫(kù)
set SQLPROMPT StandBy>
確保primary數(shù)據(jù)庫(kù)運(yùn)行在歸檔模式
Primary>archive log listDatabase log mode No Archive ModeAutomatic archival DisabledArchive destination USE_DB_RECOVERY_FILE_DESTOldest online log sequence 5Current log sequence 7Primary>shutdown immediate;Database closed.Database dismounted.ORACLE instance shut down.Primary>startup mountORACLE instance started.Total System Global Area 3290345472 bytesFixed Size 2217832 bytesVariable Size 1795164312 bytesDatabase Buffers 1476395008 bytesRedo Buffers 16568320 bytesDatabase mounted.Primary>alter database archivelog;Database altered.Primary>alter database open;Database altered.Primary>archive log listDatabase log mode Archive ModeAutomatic archival EnabledArchive destination USE_DB_RECOVERY_FILE_DESTOldest online log sequence 5Next log sequence to archive 7Current log sequence 7Primary>開(kāi)啟強(qiáng)制日志
Primary>select force_logging from v$database;FOR---NOPrimary>alter database force logging;Database altered.為了和Primary庫(kù)保存相同的結(jié)構(gòu)，我們需要在Standby數(shù)據(jù)庫(kù)建立相同的目錄，首先查詢現(xiàn)有Primary數(shù)據(jù)庫(kù)的相關(guān)目錄
Primary>col name for a30Primary>col value for a100Primary>select name ,value from v$parameter where name in ('audit_file_dest','background_dump_dest','control_files','core_dump_dest','user_dump_dest') ORDER BY name ASC;NAME VALUE------------------------------ ---------------------------------------------------------------------------------------------
audit_file_dest /usr/oracle/app/admin/orcl/adumpbackground_dump_dest /usr/oracle/app/diag/rdbms/orcl/orcl/tracecontrol_files /usr/oracle/app/oradata/orcl/control01.ctl, /usr/oracle/app/flash_recovery_area/orcl/control02.ctlcore_dump_dest /usr/oracle/app/diag/rdbms/orcl/orcl/cdumpuser_dump_dest /usr/oracle/app/diag/rdbms/orcl/orcl/trace在standby數(shù)據(jù)庫(kù)服務(wù)器創(chuàng)建相同的目錄
[oracle@oracledb ~]$ mkdir -p /usr/oracle/app/admin/orcl/adump[oracle@oracledb ~]$ mkdir -p /usr/oracle/app/diag/rdbms/orcl/orcl/trace[oracle@oracledb ~]$ mkdir -p /usr/oracle/app/oradata/orcl[oracle@oracledb ~]$ mkdir -p /usr/oracle/app/flash_recovery_area/orcl[oracle@oracledb ~]$ mkdir -p /usr/oracle/app/diag/rdbms/orcl/orcl/[oracle@oracledb ~]$ mkdir -p /usr/oracle/app/diag/rdbms/orcl/orcl/trace方法一： 直接從Primary數(shù)據(jù)庫(kù)復(fù)制密鑰文件過(guò)來(lái)
[oracle@oracledb dbs]$ scp orapworcl 172.17.22.17:/usr/oracle/app/product/11.2.0/dbhome_1/dbsoracle@172.17.22.17's password: orapworcl 100% 1536 1.5KB/s 00:00 [oracle@oracledb dbs]$
方法二： orapwd生成
orapwd FILE=/usr/oracle/app/product/11.2.0/dbhome_1/dbs/orapworcl password=wangshengzhuang entries=30;
在Primary端根據(jù)spfile生產(chǎn)pfile
Primary>create pfile from spfile;File created.
修改Primary端的pfile內(nèi)容如下
orcl.__db_cache_size=1476395008orcl.__java_pool_size=16777216orcl.__large_pool_size=16777216orcl.__oracle_base='/usr/oracle/app'#ORACLE_BASE set from environmentorcl.__pga_aggregate_target=1325400064orcl.__sga_target=1979711488orcl.__shared_io_pool_size=0orcl.__shared_pool_size=436207616orcl.__streams_pool_size=0*.audit_file_dest='/usr/oracle/app/admin/orcl/adump'*.audit_trail='db'*.compatible='11.2.0.0.0'*.control_files='/usr/oracle/app/oradata/orcl/control01.ctl','/usr/oracle/app/flash_recovery_area/orcl/control02.ctl'*.db_block_size=8192*.db_domain=''*.db_name='orcl'*.db_recovery_file_dest='/usr/oracle/app/flash_recovery_area'*.db_recovery_file_dest_size=4070572032*.diagnostic_dest='/usr/oracle/app'*.dispatchers='(PROTOCOL=TCP) (SERVICE=orclXDB)'*.memory_target=3299868672*.nls_language='SIMPLIFIED CHINESE'*.nls_territory='CHINA'*.open_cursors=300*.processes=150*.remote_login_passwordfile='EXCLUSIVE'*.undo_tablespace='UNDOTBS1'*.DB_UNIQUE_NAME=db_primary*.LOG_ARCHIVE_CONFIG='DG_CONFIG=(db_primary,db_standby)'*.LOG_ARCHIVE_DEST_2='SERVICE=tns_standby ARCH VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=db_standby' #arch表示同步已歸檔的日志*.LOG_ARCHIVE_DEST_STATE_2=DEFER #表示歸檔目的地暫時(shí)不可用*.FAL_SERVER=tns_standby*.FAL_CLIENT=tns_primary*.STANDBY_FILE_MANAGEMENT=AUTOPrimary端重新啟動(dòng)
Primary>create spfile from pfile;File created.Primary>startupORACLE instance started.Total System Global Area 3290345472 bytesFixed Size 2217832 bytesVariable Size 1795164312 bytesDatabase Buffers 1476395008 bytesRedo Buffers 16568320 bytesDatabase mounted.Database opened.Primary>show parameter falNAME TYPE VALUE------------------------------------ ---------------------- ------------------------------fal_client string tns_primary #tns_primary為主服務(wù)器的網(wǎng)絡(luò)服務(wù)名fal_server string tns_standby 復(fù)制生成的initorcl.ora到standby的$ORACLE_HOME/dbs目錄下
[oracle@oracledb dbs]$ pwd/usr/oracle/app/product/11.2.0/dbhome_1/dbs[oracle@oracledb dbs]$ scp initorcl.ora 172.17.22.17:/usr/oracle/app/product/11.2.0/dbhome_1/dbsThe authenticity of host '172.17.22.17 (172.17.22.17)' can't be established.RSA key fingerprint is 72:28:f5:f9:9c:f8:49:23:48:6d:9d:d4:0e:0c:89:71.Are you sure you want to continue connecting (yes/no)? yesWarning: Permanently added '172.17.22.17' (RSA) to the list of known hosts.oracle@172.17.22.17's password: initorcl.ora 100% 1291 1.3KB/s 00:00 [oracle@oracledb dbs]$修改standbyd端的initorcl.ora文件，內(nèi)容如下
orcl.__db_cache_size=1476395008orcl.__java_pool_size=16777216orcl.__large_pool_size=16777216orcl.__oracle_base='/usr/oracle/app'#ORACLE_BASE set from environmentorcl.__pga_aggregate_target=1325400064orcl.__sga_target=1979711488orcl.__shared_io_pool_size=0orcl.__shared_pool_size=436207616orcl.__streams_pool_size=0*.audit_file_dest='/usr/oracle/app/admin/orcl/adump'*.audit_trail='db'*.compatible='11.2.0.0.0'*.control_files='/usr/oracle/app/oradata/orcl/control01.ctl','/usr/oracle/app/flash_recovery_area/orcl/control02.ctl'*.db_block_size=8192*.db_domain=''*.db_name='orcl'*.db_recovery_file_dest='/usr/oracle/app/flash_recovery_area'*.db_recovery_file_dest_size=4070572032*.diagnostic_dest='/usr/oracle/app'*.dispatchers='(PROTOCOL=TCP) (SERVICE=orclXDB)'*.memory_target=3299868672*.nls_language='SIMPLIFIED CHINESE'*.nls_territory='CHINA'*.open_cursors=300*.processes=150*.remote_login_passwordfile='EXCLUSIVE'*.undo_tablespace='UNDOTBS1'*.DB_UNIQUE_NAME=db_standby*.LOG_ARCHIVE_CONFIG='DG_CONFIG=(db_primary,db_standby)'*.LOG_ARCHIVE_DEST_2='SERVICE=tns_primary ARCH VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=db_primary' #ARCH為已歸檔日志*.LOG_ARCHIVE_DEST_STATE_2=ENABLE #備用的連接到主的可以開(kāi)啟,因?yàn)榇藭r(shí)主同步日志到備未啟用*.FAL_SERVER=tns_primary*.FAL_CLIENT=tns_standby*.STANDBY_FILE_MANAGEMENT=AUTO通過(guò)復(fù)制的pfile創(chuàng)建Standby數(shù)據(jù)庫(kù)的spfile
StandBy> create spfile from pfile;File created.
啟動(dòng)到nomount環(huán)境
SQL> startup nomountORACLE instance started.Total System Global Area 3290345472 bytesFixed Size 2217832 bytesVariable Size 1795164312 bytesDatabase Buffers 1476395008 bytesRedo Buffers 16568320 bytesPrimary端監(jiān)聽(tīng)
# listener.ora Network Configuration File: /usr/oracle/app/product/11.2.0/dbhome_1/network/admin/listener.ora# Generated by Oracle configuration tools.LISTENER = (DESCRIPTION_LIST = (DESCRIPTION = (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521)) (ADDRESS = (PROTOCOL = TCP)(HOST = 172.17.22.16)(PORT = 1521)) ) )ADR_BASE_LISTENER = /usr/oracle/appStandBy端監(jiān)聽(tīng)（配置了靜態(tài)監(jiān)聽(tīng) 服務(wù)名GLOBAL_DBNAME = StandBy）
# listener.ora Network Configuration File: /usr/oracle/app/product/11.2.0/dbhome_1/network/admin/listener.ora# Generated by Oracle configuration tools.#紅色字體為靜態(tài)監(jiān)聽(tīng),后面duplicate需要用到靜態(tài)監(jiān)聽(tīng)注冊(cè)SID_LIST_LISTENER = (SID_LIST = (SID_DESC = (GLOBAL_DBNAME = StandBy) (ORACLE_HOME =/usr/oracle/app/product/11.2.0/dbhome_1) (SID_NAME = orcl) ) )LISTENER = (DESCRIPTION_LIST = (DESCRIPTION = (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521)) (ADDRESS = (PROTOCOL = TCP)(HOST = 172.17.22.17)(PORT = 1521)) ) )ADR_BASE_LISTENER = /usr/oracle/app啟動(dòng)standby端監(jiān)聽(tīng)
[oracle@oracledb admin]$ lsnrctl startLSNRCTL for Linux: Version 11.2.0.1.0 - Production on 23-DEC-2015 15:48:15Copyright (c) 1991, 2009, Oracle. All rights reserved.Starting /usr/oracle/app/product/11.2.0/dbhome_1/bin/tnslsnr: please wait...TNSLSNR for Linux: Version 11.2.0.1.0 - ProductionSystem parameter file is /usr/oracle/app/product/11.2.0/dbhome_1/network/admin/listener.oraLog messages written to /usr/oracle/app/diag/tnslsnr/oracledb/listener/alert/log.xmlListening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=172.17.22.17)(PORT=1521)))Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1521)))STATUS of the LISTENER------------------------Alias LISTENERVersion TNSLSNR for Linux: Version 11.2.0.1.0 - ProductionStart Date 23-DEC-2015 15:48:16Uptime 0 days 0 hr. 0 min. 0 secTrace Level offSecurity ON: Local OS AuthenticationSNMP OFFListener Parameter File /usr/oracle/app/product/11.2.0/dbhome_1/network/admin/listener.oraListener Log File /usr/oracle/app/diag/tnslsnr/oracledb/listener/alert/log.xmlListening Endpoints Summary... (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521))) (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=172.17.22.17)(PORT=1521)))Services Summary...Service "StandBy" has 1 instance(s). Instance "orcl", status UNKNOWN, has 1 handler(s) for this service...The command completed successfullyPrimary端和StandBy端都要進(jìn)行如下配置：
tns_primary = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 172.17.22.16)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = db_primary) ) )tns_standby = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 172.17.22.17)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME =StandBy ) ) )***********************************************************************************************
SERVICE_NAME 的值參考lsnrctl的輸出，確保用sqlplus工具或者navicact工具能夠連接SERVICE_NAME
因?yàn)閒al_server 會(huì)使用服務(wù)名，如果配置不正確，這歸檔日志無(wú)法正常發(fā)送
***********************************************************************************************
在primary端和standby端都進(jìn)行測(cè)試（為了角色切換）
[oracle@oracledb admin]$ tnsping tns_primaryTNS Ping Utility for Linux: Version 11.2.0.1.0 - Production on 23-DEC-2015 15:48:44Copyright (c) 1997, 2009, Oracle. All rights reserved.Used parameter files:/usr/oracle/app/product/11.2.0/dbhome_1/network/admin/sqlnet.oraUsed TNSNAMES adapter to resolve the aliasAttempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 172.17.22.16)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = orcl)))OK (0 msec)[oracle@oracledb admin]$ tnsping tns_standbyTNS Ping Utility for Linux: Version 11.2.0.1.0 - Production on 23-DEC-2015 15:48:49Copyright (c) 1997, 2009, Oracle. All rights reserved.Used parameter files:/usr/oracle/app/product/11.2.0/dbhome_1/network/admin/sqlnet.oraUsed TNSNAMES adapter to resolve the aliasAttempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 172.17.22.17)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = orcl)))OK (0 msec)rman連接兩個(gè)數(shù)據(jù)庫(kù)
[oracle@oracledb admin]$ rman target sys/primary_password@tns_primary auxiliary sys/standby_password@tns_standbyRecovery Manager: Release 11.2.0.1.0 - Production on Wed Dec 23 16:11:51 2015Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.connected to target database: ORCL (DBID=1426832466)connected to auxiliary database: ORCL (not mounted)開(kāi)始復(fù)制
RMAN> DUPLICATE TARGET DATABASE FOR STANDBY FROM ACTIVE DATABASE DORECOVER NOFILENAMECHECK;Starting Duplicate Db at 23-DEC-15using target database control file instead of recovery catalogallocated channel: ORA_AUX_DISK_1channel ORA_AUX_DISK_1: SID=20 device type=DISKRMAN-00571: ===========================================================RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============RMAN-00571: ===========================================================RMAN-03002: failure of Duplicate Db command at 12/23/2015 16:13:38RMAN-05541: no archived logs found in target databaseprimary端切換下日志，重寫執(zhí)行rman duplicate即可
Primary>show parameter LOG_ARCHIVE_DEST_STATE_2NAME TYPE VALUE------------------------------------ ---------------------- ------------------------------log_archive_dest_state_2 string DEFER #當(dāng)前主的還未啟用log_archive_dest_state_20 string enablelog_archive_dest_state_21 string enablelog_archive_dest_state_22 string enablelog_archive_dest_state_23 string enablelog_archive_dest_state_24 string enablelog_archive_dest_state_25 string enablelog_archive_dest_state_26 string enablelog_archive_dest_state_27 string enablelog_archive_dest_state_28 string enablelog_archive_dest_state_29 string enable
Primary>alter system set LOG_ARCHIVE_DEST_STATE_2=ENABLE;System altered.查詢primary數(shù)據(jù)庫(kù)角色
Primary>select database_role from v$database;DATABASE_ROLE--------------------------------PRIMARY
查詢standby數(shù)據(jù)庫(kù)角色
StandBy>select database_role from v$database;DATABASE_ROLE--------------------------------PHYSICAL STANDBYStandBy>primary端插入一條數(shù)據(jù)
Primary> INSERT INTO "SCOTT"."DEPT" ("DEPTNO", "DNAME", "LOC") VALUES ('12', 'OPERATIONS', 'OPERATIONS');row created.Primary>commit;Commit complete.Primary>alter system switch logfile;System altered.standby端：?jiǎn)?dòng)redo應(yīng)用; 暫停redo應(yīng)用 ; 打開(kāi)數(shù)據(jù)庫(kù); 查詢數(shù)據(jù)是否被同步過(guò)來(lái)了
StandBy>alter database recover managed standby database disconnect from session; #standby端應(yīng)用redoDatabase altered.StandBy>alter database recover managed standby database cancel; #取消應(yīng)用redoDatabase altered.StandBy>alter database open; #打開(kāi)數(shù)據(jù)庫(kù)Database altered.StandBy>select open_mode from v$database; #standby是只讀狀態(tài)OPEN_MODE----------------------------------------READ ONLYStandBy>見(jiàn)證奇跡的時(shí)刻
StandBy>select * from scott.dept;DEPTNO DNAME LOC---------- ---------------------------- --------------------------ACCOUNTING NEW YORKRESEARCH DALLASSALES CHICAGOOPERATIONS BOSTONOPERATIONS OPERATIONS添加redo log
首先查詢當(dāng)前redo log的大小、位置
Primary>col group# for 9Primary>col status for a10Primary>col type for a10Primary>col member for a50;Primary>col is_rec for a10Primary>select * from v$logfile;GROUP# STATUS TYPE MEMBER IS_REC------ ---------- ---------- -------------------------------------------------- ------ ONLINE /usr/oracle/app/oradata/orcl/redo03.log NO ONLINE /usr/oracle/app/oradata/orcl/redo02.log NO ONLINE /usr/oracle/app/oradata/orcl/redo01.log NOPrimary>select * from v$log;GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE MEMBERS ARCHIV STATUS FIRST_CHANGE# FIRST_TIME NEXT_CHANGE# NEXT_TIME------ ---------- ---------- ---------- ---------- ---------- ------ ---------- ------------- ------------ ------------ ------------ 1 10 52428800 512 1 NO CURRENT 1029234 23-DEC-15 2.8147E+14 1 8 52428800 512 1 YES INACTIVE 1028340 23-DEC-15 1028421 23-DEC-15 1 9 52428800 512 1 YES INACTIVE 1028421 23-DEC-15 1029234 23-DEC-15當(dāng)前有三組、每組1個(gè)member、大小為50M, 我們?cè)黾铀慕M，每組1個(gè)member，大小為50M
Primary>ALTER DATABASE ADD STANDBY LOGFILE GROUP 4('/usr/oracle/app/oradata/orcl/stbyredolog4a.log') SIZE 50 M;Database altered.Primary>ALTER DATABASE ADD STANDBY LOGFILE GROUP 5('/usr/oracle/app/oradata/orcl/stbyredolog5a.log') SIZE 50 M;Database altered.Primary>ALTER DATABASE ADD STANDBY LOGFILE GROUP 6('/usr/oracle/app/oradata/orcl/stbyredolog6a.log') SIZE 50 M;Database altered.Primary>ALTER DATABASE ADD STANDBY LOGFILE GROUP 7('/usr/oracle/app/oradata/orcl/stbyredolog7a.log') SIZE 50 M;Database altered.查看結(jié)果：
Primary>select * from v$logfile;GROUP# STATUS TYPE MEMBER IS_REC------ ---------- ---------- -------------------------------------------------- ------ ONLINE /usr/oracle/app/oradata/orcl/redo03.log NO ONLINE /usr/oracle/app/oradata/orcl/redo02.log NO ONLINE /usr/oracle/app/oradata/orcl/redo01.log NO STANDBY /usr/oracle/app/oradata/orcl/stbyredolog4a.log NO STANDBY /usr/oracle/app/oradata/orcl/stbyredolog5a.log NO STANDBY /usr/oracle/app/oradata/orcl/stbyredolog6a.log NO STANDBY /usr/oracle/app/oradata/orcl/stbyredolog7a.log NOrows selected.同理在standby端做相同的操作
StandBy>ALTER DATABASE ADD STANDBY LOGFILE GROUP 4('/usr/oracle/app/oradata/orcl/stbyredolog4a.log') SIZE 50 M;Database altered.StandBy>ALTER DATABASE ADD STANDBY LOGFILE GROUP 5('/usr/oracle/app/oradata/orcl/stbyredolog5a.log') SIZE 50 M;Database altered.StandBy>ALTER DATABASE ADD STANDBY LOGFILE GROUP 6('/usr/oracle/app/oradata/orcl/stbyredolog6a.log') SIZE 50 M;Database altered.StandBy>ALTER DATABASE ADD STANDBY LOGFILE GROUP 7('/usr/oracle/app/oradata/orcl/stbyredolog7a.log') SIZE 50 M;Database altered.StandBy>select * from v$logfile;GROUP# STATUS TYPE MEMBER IS_REC------ ---------- ---------- ---------------------------------------------------------------------------------------------------- ------ ONLINE /usr/oracle/app/flash_recovery_area/DB_STANDBY/onlinelog/o1_mf_3_c7np1p97_.log YES ONLINE /usr/oracle/app/flash_recovery_area/DB_STANDBY/onlinelog/o1_mf_2_c7np1osm_.log YES ONLINE /usr/oracle/app/flash_recovery_area/DB_STANDBY/onlinelog/o1_mf_1_c7np1od8_.log YES STANDBY /usr/oracle/app/oradata/orcl/stbyredolog4a.log NO STANDBY /usr/oracle/app/oradata/orcl/stbyredolog5a.log NO STANDBY /usr/oracle/app/oradata/orcl/stbyredolog6a.log NO STANDBY /usr/oracle/app/oradata/orcl/stbyredolog7a.log NO更改primary和standby端的log_archive_dest_2
Primary>show parameter log_archive_dest_2NAME TYPE VALUE------------------------------------ ---------- ------------------------------log_archive_dest_2 string SERVICE=tns_standby ARCH VALID _FOR=(ONLINE_LOGFILES,PRIMARY_ ROLE) DB_UNIQUE_NAME=db_standb ylog_archive_dest_20 stringlog_archive_dest_21 stringlog_archive_dest_22 stringlog_archive_dest_23 stringlog_archive_dest_24 stringlog_archive_dest_25 stringlog_archive_dest_26 stringlog_archive_dest_27 stringlog_archive_dest_28 stringlog_archive_dest_29 string
Primary>alter system set log_archive_dest_2='SERVICE=tns_standby LGWR VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=db_standby'; #以前此處為ARCH,System altered.Primary>show parameter log_archive_dest_2NAME TYPE VALUE------------------------------------ ---------- ------------------------------log_archive_dest_2 string SERVICE=tns_standby LGWR VALID _FOR=(ONLINE_LOGFILES,PRIMARY_ ROLE) DB_UNIQUE_NAME=db_standb ylog_archive_dest_20 stringlog_archive_dest_21 stringlog_archive_dest_22 stringlog_archive_dest_23 stringlog_archive_dest_24 stringlog_archive_dest_25 stringlog_archive_dest_26 stringlog_archive_dest_27 stringlog_archive_dest_28 stringlog_archive_dest_29 stringPrimary>更改standby端的log_archive_dest_2
StandBy>alter system set log_archive_dest_2='SERVICE=tns_primary LGWR VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=db_primary';
啟用實(shí)時(shí)StandBy端redo應(yīng)用
StandBy>alter database recover managed standby database using current logfile disconnect from session; #因?yàn)楦某闪薒GWR來(lái)傳遞,所以需要使用實(shí)時(shí)
驗(yàn)證：
首先在Primay端插入一條數(shù)據(jù)：
Primary>select * from scott.dept; DEPTNO DNAME LOC---------- ---------------------------- --------------------------ACCOUNTING NEW YORKRESEARCH DALLASSALES CHICAGOOPERATIONS BOSTONOPERATIONS OPERATIONSPrimary>INSERT INTO "SCOTT"."DEPT" ("DEPTNO", "DNAME", "LOC") VALUES ('13', 'OPERATIONS', 'OPERATIONS');row created.Primary>commit;Commit complete.standby端查看
StandBy>select * from scott.dept; DEPTNO DNAME LOC---------- ---------------------------- --------------------------ACCOUNTING NEW YORKRESEARCH DALLASSALES CHICAGOOPERATIONS BOSTONOPERATIONS OPERATIONSOPERATIONS OPERATIONSrows selected.備庫(kù)暫停 redo應(yīng)用
StandBy>alter database recover managed standby database cancel; #取消redo應(yīng)用Database altered.
查詢主庫(kù)是否支持switchover操作
Primary> select switchover_status from v$database;SWITCHOVER_STATUS----------------------------------------TO STANDBY
查詢備庫(kù)是否支持switchover操作
StandBy> select switchover_status from v$database;SWITCHOVER_STATUS----------------------------------------NOT ALLOWED
not allowed是因?yàn)橹鲙?kù)還未切換為standby
switchover，primary切換為物理standby，切換后查看數(shù)據(jù)庫(kù)角色、打開(kāi)模式、
Primary>alter database commit to switchover to physical standby; #將當(dāng)前primary角色切換到物理standbyDatabase altered.Primary>shutdown immediate;ORA-01507: database not mountedORACLE instance shut down.Primary>startupORACLE instance started.Total System Global Area 3290345472 bytesFixed Size 2217832 bytesVariable Size 1795164312 bytesDatabase Buffers 1476395008 bytesRedo Buffers 16568320 bytesDatabase mounted.Database opened.Primary>select open_mode from v$database;OPEN_MODE----------------------------------------READ ONLYPrimary>select database_role from v$database;DATABASE_ROLE--------------------------------PHYSICAL STANDBYPrimary>物理standby切換為primary
StandBy>select switchover_status from v$database;SWITCHOVER_STATUS----------------------------------------TO PRIMARYStandBy> alter database commit to switchover to primary; #standby切換到primaryDatabase altered.StandBy>alter database open;Database altered.StandBy>select open_mode from v$database;OPEN_MODE----------------------------------------READ WRITEStandBy>select database_role from v$database;DATABASE_ROLE--------------------------------PRIMARYStandBy>
1、查詢進(jìn)程的活動(dòng)狀態(tài)
select process,client_process,sequence#,status from v$managed_standby;
2、查詢r(jià)edo應(yīng)用進(jìn)度
select dest_name,archived_thread#,archived_seq#,applied_thread#,applied_seq#,db_unique_name from v$archive_dest_status where status ='VALID';
3、查詢歸檔文件路徑及創(chuàng)建信息
select name,creator,sequence#,applied,completion_time from v$archived_log;
4、查詢歸檔歷史
select first_time,first_change#,next_change#,sequence# from v$log_history;select thread#,sequence#,applied from v$archived_log;
5、參看數(shù)據(jù)庫(kù)的基本信息
select database_role,db_unique_name,open_mode,protection_mode,protection_level,switchover_status from v$database;
6、查詢r(jià)edo應(yīng)用及redo傳輸服務(wù)的活動(dòng)狀態(tài)
select database_role,db_unique_name,open_mode,protection_mode,protection_level,switchover_status from v$database;
7、檢查應(yīng)用模式
StandBy>select recovery_mode from v$archive_dest_status where dest_id=2;RECOVERY_MODE----------------------------------------------IDLE
取值
idle
managed：
managed real_time_apply：
聯(lián)系客服
