时间：2021-07-01 10:21:17 帮助过：4人阅读
standby数据库
set SQLPROMPT StandBy>
确保primary数据库运行在归档模式
Primary>archive log list Database log mode No Archive Mode Automatic archival Disabled Archive destination USE_DB_RECOVERY_FILE_DEST Oldest online log sequence 5 Current log sequence 7 Primary>shutdown immediate; Database closed. Database dismounted. ORACLE instance shut down. Primary>startup mount ORACLE instance started. Total System Global Area 3290345472 bytes Fixed Size 2217832 bytes Variable Size 1795164312 bytes Database Buffers 1476395008 bytes Redo Buffers 16568320 bytes Database mounted. Primary>alter database archivelog; Database altered. Primary>alter database open; Database altered. Primary>archive log list Database log mode Archive Mode Automatic archival Enabled Archive destination USE_DB_RECOVERY_FILE_DEST Oldest online log sequence 5 Next log sequence to archive 7 Current log sequence 7 Primary>
开启强制日志
Primary>select force_logging from v$database;
FOR
---
NO
Primary>alter database force logging;
Database altered.
为了和Primary库保存相同的结构，我们需要在Standby数据库建立相同的目录，首先查询现有Primary数据库的相关目录
Primary>col name for a30 Primary>col value for a100 Primary>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/adump background_dump_dest /usr/oracle/app/diag/rdbms/orcl/orcl/trace control_files /usr/oracle/app/oradata/orcl/control01.ctl, /usr/oracle/app/flash_recovery_area/orcl/control02.ctl core_dump_dest /usr/oracle/app/diag/rdbms/orcl/orcl/cdump user_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
方法一： 直接从Primary数据库复制密钥文件过来 (如果ORACLE_SID不同 需要改名)
[oracle@oracledb dbs]$ scp orapworcl 172.17.22.17:/usr/oracle/app/product/11.2.0/dbhome_1/dbs oracle@172.17.22.17‘s password: orapworcl 100% 1536 1.5KB/s 00:00 [oracle@oracledb dbs]$
改名
[oracle@oracledb dbs]$ mv orapworcl orapworcl_standby
方法二： orapwd生成
orapwd FILE=/usr/oracle/app/product/11.2.0/dbhome_1/dbs/orapworcl password=wangshengzhuang entries=30;
在Primary端根据spfile生成pfile，并备份
Primary>create pfile from spfile;
File created.
查询Primary库的db_unique_name
Primary>show parameter db_unique_name; NAME TYPE VALUE ------------------------------------ ---------------------- ------------------------------ db_unique_name string orcl
修改spfile参数值
-------为了不重启,沿用上面的db_unique_name
-------alter system set DB_UNIQUE_NAME=orcl scope=spfile; alter system set LOG_ARCHIVE_CONFIG=‘DG_CONFIG=(orcl,db_standby)‘ alter system set LOG_ARCHIVE_DEST_2=‘SERVICE=tns_standby ARCH VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=db_standby‘ alter system set LOG_ARCHIVE_DEST_STATE_2=DEFER alter system set FAL_SERVER=tns_standby alter system set FAL_CLIENT=tns_primary alter system set STANDBY_FILE_MANAGEMENT=AUTO
在Primary端根据spfile重新生成pfile，用以启动standby数据库
Primary>create pfile from spfile;
File created.
复制生成的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/dbs The 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)? yes Warning: 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]$
改名
[oracle@oracledb dbs]$ mv initorcl.ora initorcl_standby.ora
修改standbyd端的initorcl_standby.ora文件，内容如下
orcl.__db_cache_size=1476395008 orcl.__java_pool_size=16777216 orcl.__large_pool_size=16777216 orcl.__oracle_base=‘/usr/oracle/app‘#ORACLE_BASE set from environment orcl.__pga_aggregate_target=1325400064 orcl.__sga_target=1979711488 orcl.__shared_io_pool_size=0 orcl.__shared_pool_size=436207616 orcl.__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=(orcl,db_standby)‘ *.LOG_ARCHIVE_DEST_2=‘SERVICE=tns_primary ARCH VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=orcl‘ *.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 nomount
ORACLE instance started.
Total System Global Area 3290345472 bytes
Fixed Size 2217832 bytes
Variable Size 1795164312 bytes
Database Buffers 1476395008 bytes
Redo Buffers 16568320 bytes
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. SID_LIST_LISTENER = (SID_LIST = (SID_DESC = (GLOBAL_DBNAME = StandBy) (ORACLE_HOME =/usr/oracle/app/product/11.2.0/dbhome_1) (SID_NAME = orcl_standby) ) ) 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 start LSNRCTL for Linux: Version 11.2.0.1.0 - Production on 23-DEC-2015 15:48:15 Copyright (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 - Production System parameter file is /usr/oracle/app/product/11.2.0/dbhome_1/network/admin/listener.ora Log messages written to /usr/oracle/app/diag/tnslsnr/oracledb/listener/alert/log.xml Listening 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 LISTENER Version TNSLSNR for Linux: Version 11.2.0.1.0 - Production Start Date 23-DEC-2015 15:48:16 Uptime 0 days 0 hr. 0 min. 0 sec Trace Level off Security ON: Local OS Authentication SNMP OFF Listener Parameter File /usr/oracle/app/product/11.2.0/dbhome_1/network/admin/listener.ora Listener Log File /usr/oracle/app/diag/tnslsnr/oracledb/listener/alert/log.xml Listening 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
Primary端和StandBy端都要进行如下配置：
tns_primary = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 172.17.22.16)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = orcl) ) ) 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_primary TNS Ping Utility for Linux: Version 11.2.0.1.0 - Production on 23-DEC-2015 15:48:44 Copyright (c) 1997, 2009, Oracle. All rights reserved. Used parameter files: /usr/oracle/app/product/11.2.0/dbhome_1/network/admin/sqlnet.ora Used TNSNAMES adapter to resolve the alias Attempting 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_standby TNS Ping Utility for Linux: Version 11.2.0.1.0 - Production on 23-DEC-2015 15:48:49 Copyright (c) 1997, 2009, Oracle. All rights reserved. Used parameter files: /usr/oracle/app/product/11.2.0/dbhome_1/network/admin/sqlnet.ora Used TNSNAMES adapter to resolve the alias Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 172.17.22.17)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = orcl))) OK (0 msec)
rman连接两个数据库
[oracle@oracledb admin]$ rman target sys/wangshengzhuang@tns_primary auxiliary sys/wangshengzhuang@tns_standby Recovery Manager: Release 11.2.0.1.0 - Production on Wed Dec 23 16:11:51 2015 Copyright (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-15 using target database control file instead of recovery catalog allocated channel: ORA_AUX_DISK_1 channel ORA_AUX_DISK_1: SID=20 device type=DISK RMAN-00571: =========================================================== RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS =============== RMAN-00571: =========================================================== RMAN-03002: failure of Duplicate Db command at 12/23/2015 16:13:38 RMAN-05541: no archived logs found in target database
primary端切换下日志，重写执行rman duplicate即可
SYS@orcl>alter system switch logfile; System altered. SYS@orcl>quit Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options [oracle@oracledb admin]$ rman target sys/wangshengzhuang@tns_primary auxiliary sys/wangshengzhuang@tns_standby Recovery Manager: Release 11.2.0.1.0 - Production on Wed Dec 23 16:14:59 2015 Copyright (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-15 using target database control file instead of recovery catalog allocated channel: ORA_AUX_DISK_1 channel ORA_AUX_DISK_1: SID=18 device type=DISK contents of Memory Script: { backup as copy reuse targetfile ‘/usr/oracle/app/product/11.2.0/dbhome_1/dbs/orapworcl‘ auxiliary format ‘/usr/oracle/app/product/11.2.0/dbhome_1/dbs/orapworcl‘ ; } executing Memory Script Starting backup at 23-DEC-15 allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=39 device type=DISK Finished backup at 23-DEC-15 contents of Memory Script: { backup as copy current controlfile for standby auxiliary format ‘/usr/oracle/app/oradata/orcl/control01.ctl‘; restore clone controlfile to ‘/usr/oracle/app/flash_recovery_area/orcl/control02.ctl‘ from ‘/usr/oracle/app/oradata/orcl/control01.ctl‘; } executing Memory Script Starting backup at 23-DEC-15 using channel ORA_DISK_1 channel ORA_DISK_1: starting datafile copy copying standby control file output file name=/usr/oracle/app/product/11.2.0/dbhome_1/dbs/snapcf_orcl.f tag=TAG20151223T161504 RECID=1 STAMP=899223305 channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01 Finished backup at 23-DEC-15 Starting restore at 23-DEC-15 using channel ORA_AUX_DISK_1 channel ORA_AUX_DISK_1: copied control file copy Finished restore at 23-DEC-15 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 "/usr/oracle/app/oradata/orcl/temp01.dbf"; switch clone tempfile all; set newname for datafile 1 to "/usr/oracle/app/oradata/orcl/system01.dbf"; set newname for datafile 2 to "/usr/oracle/app/oradata/orcl/sysaux01.dbf"; set newname for datafile 3 to "/usr/oracle/app/oradata/orcl/undotbs01.dbf"; set newname for datafile 4 to "/usr/oracle/app/oradata/orcl/users01.dbf"; backup as copy reuse datafile 1 auxiliary format "/usr/oracle/app/oradata/orcl/system01.dbf" datafile 2 auxiliary format "/usr/oracle/app/oradata/orcl/sysaux01.dbf" datafile 3 auxiliary format "/usr/oracle/app/oradata/orcl