Duplicate复制数据库并创建物理StandBy（pfile版本）
1设定环境如下：
Primary数据库
| IP | 172.17.22.16 |
| SID | orcl |
Standby数据库
| IP | 172.17.22.17 |
| SID | orcl |
设置提示，以区分操作的位置
primary数据库
set SQLPROMPT Primary>
standby数据库
set SQLPROMPT StandBy>
1、Primary端设置 归档模式+强制日志
确保primary数据库运行在归档模式
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>
开启强制日志
Primary>select force_logging from v$database;FOR---NOPrimary>alter database force logging;Database altered.
2、standby端创建相关目录
为了和Primary库保存相同的结构，我们需要在Standby数据库建立相同的目录，首先查询现有Primary数据库的相关目录
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数据库服务器创建相同的目录
[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
3、创建辅助实例密钥文件
方法一： 直接从Primary数据库复制密钥文件过来
[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;
4、生成standby端的pfile
在Primary端根据spfile生产pfile
Primary>create pfile from spfile;File created.
修改Primary端的pfile内容如下
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 #表示归档目的地暂时不可用*.FAL_SERVER=tns_standby*.FAL_CLIENT=tns_primary*.STANDBY_FILE_MANAGEMENT=AUTO
Primary端重新启动
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为主服务器的网络服务名fal_server string tns_standby
复制生成的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文件，内容如下
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 #备用的连接到主的可以开启,因为此时主同步日志到备未启用*.FAL_SERVER=tns_primary*.FAL_CLIENT=tns_standby*.STANDBY_FILE_MANAGEMENT=AUTO
通过复制的pfile创建Standby数据库的spfile
StandBy> create spfile from pfile;File created.
启动到nomount环境
SQL> startup nomountORACLE instance started.Total System Global Area 3290345472 bytesFixed Size 2217832 bytesVariable Size 1795164312 bytesDatabase Buffers 1476395008 bytesRedo Buffers 16568320 bytes
5、配置监听服务
Primary端监听
# 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/app
StandBy端监听（配置了静态监听 服务名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.#红色字体为静态监听,后面duplicate需要用到静态监听注册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
启动standby端监听
[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 successfully
6、配置网络服务名，并测试互通性
Primary端和StandBy端都要进行如下配置：
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
因为fal_server 会使用服务名，如果配置不正确，这归档日志无法正常发送
***********************************************************************************************
在primary端和standby端都进行测试（为了角色切换）
[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)
7、duplicate standby
rman连接两个数据库
[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)
开始复制
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 database
primary端切换下日志，重写执行rman duplicate即可
View Code
8、启用日志传送
Primary>show parameter LOG_ARCHIVE_DEST_STATE_2NAME TYPE VALUE------------------------------------ ---------------------- ------------------------------log_archive_dest_state_2 string DEFER #当前主的还未启用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 enablePrimary>alter system set LOG_ARCHIVE_DEST_STATE_2=ENABLE;System altered.
8、验证结果
查询primary数据库角色
Primary>select database_role from v$database;DATABASE_ROLE--------------------------------PRIMARY
查询standby数据库角色
StandBy>select database_role from v$database;DATABASE_ROLE--------------------------------PHYSICAL STANDBYStandBy>
primary端插入一条数据
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端：启动redo应用; 暂停redo应用 ; 打开数据库; 查询数据是否被同步过来了
StandBy>alter database recover managed standby database disconnect from session; #standby端应用redoDatabase altered.StandBy>alter database recover managed standby database cancel; #取消应用redoDatabase altered.StandBy>alter database open; #打开数据库Database altered.StandBy>select open_mode from v$database; #standby是只读状态OPEN_MODE----------------------------------------READ ONLYStandBy>
见证奇迹的时刻
StandBy>select * from scott.dept;DEPTNO DNAME LOC---------- ---------------------------- --------------------------ACCOUNTING NEW YORKRESEARCH DALLASSALES CHICAGOOPERATIONS BOSTONOPERATIONS OPERATIONS
9、启用实时应用redo
添加redo log
首先查询当前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
当前有三组、每组1个member、大小为50M, 我们增加四组，每组1个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.
查看结果：
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 stringPrimary>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';
启用实时StandBy端redo应用
StandBy>alter database recover managed standby database using current logfile disconnect from session; #因为更改成了LGWR来传递,所以需要使用实时
验证：
首先在Primay端插入一条数据：
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.
10、switchover
备库暂停 redo应用
StandBy>alter database recover managed standby database cancel; #取消redo应用Database altered.
查询主库是否支持switchover操作
Primary> select switchover_status from v$database;SWITCHOVER_STATUS----------------------------------------TO STANDBY
查询备库是否支持switchover操作
StandBy> select switchover_status from v$database;SWITCHOVER_STATUS----------------------------------------NOT ALLOWED
not allowed是因为主库还未切换为standby
switchover，primary切换为物理standby，切换后查看数据库角色、打开模式、
Primary>alter database commit to switchover to physical standby; #将当前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、查询进程的活动状态
select process,client_process,sequence#,status from v$managed_standby;
2、查询redo应用进度
select dest_name,archived_thread#,archived_seq#,applied_thread#,applied_seq#,db_unique_name from v$archive_dest_status where status ='VALID';
3、查询归档文件路径及创建信息
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、参看数据库的基本信息
select database_role,db_unique_name,open_mode,protection_mode,protection_level,switchover_status from v$database;
6、查询redo应用及redo传输服务的活动状态
select database_role,db_unique_name,open_mode,protection_mode,protection_level,switchover_status from v$database;
7、检查应用模式
StandBy>select recovery_mode from v$archive_dest_status where dest_id=2;RECOVERY_MODE----------------------------------------------IDLE
取值
idle
managed：
managed real_time_apply：
赞 (0)