准备工作：
1、开启归档
SQL> archive log list;Database log mode No Archive Mode
Automatic archival DisabledArchive destination /u01/app/oracle/product/11.2.0/dbhome_1/dbs/archOldest online log sequence 6Current log sequence 8SQL> shutdown immediate
Database closed.Database dismounted.ORACLE instance shut down.SQL> startup mount; ORACLE instance started.Total System Global Area 941600768 bytes
Fixed Size 1348860 bytesVariable Size 515902212 bytesDatabase Buffers 419430400 bytesRedo Buffers 4919296 bytesDatabase mounted.SQL> alter database archivelog;Database altered.
SQL> archive log list;
Database log mode Archive ModeAutomatic archival EnabledArchive destination /u01/app/oracle/product/11.2.0/dbhome_1/dbs/archOldest online log sequence 6Next log sequence to archive 8Current log sequence 82、打开强制写日志功能
SQL> alter database force logging;
Database altered.
确认数据库的快速恢复区处于关闭状态
SQL> show parameter recover
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------db_recovery_file_dest stringdb_recovery_file_dest_size big integer 0db_unrecoverable_scn_tracking boolean TRUErecovery_parallelism integer 0关闭闪回功能
SQL> alter database flashback off;
Database altered.
SQL> select force_logging,flashback_on from v$database;
FOR FLASHBACK_ON
--- ------------------YES NO3、创建standby logfile（在主库上创建，组数要大于等于主库的日志组数量。在之后可以直接拷贝到备库中；为之后可能会发生的角色转换做准备）
SQL> alter database add standby logfile '/u01/app/oracle/oradata/PROD1/redo_stbdy01.log' size 50M;
Database altered.
SQL> alter database add standby logfile '/u01/app/oracle/oradata/PROD1/redo_stbdy02.log' size 50M;
Database altered.
SQL> alter database add standby logfile '/u01/app/oracle/oradata/PROD1/redo_stbdy03.log' size 50M;
Database altered.
SQL> alter database add standby logfile '/u01/app/oracle/oradata/PROD1/redo_stbdy04.log' size 50M;
Database altered.
SQL> select group#,member,type from v$logfile where type like '%STAND%';
SQL> select group#,bytes/1024/1024 as size_m from v$standby_log;
GROUP# SIZE_M
---------- ---------- 4 50 5 50 6 50 7 50File created.
[oracle@ENMO admin]$ cd $ORACLE_HOMEs 备份一下二进制文件，防止一会儿改乱，打不开库 [oracle@ENMO dbs]$ cp spfileENMO.ora spfileENMO.ora.bakPROD1.__db_cache_size=419430400
PROD1.__java_pool_size=4194304PROD1.__large_pool_size=4194304PROD1.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environmentPROD1.__pga_aggregate_target=377487360PROD1.__sga_target=566231040PROD1.__shared_io_pool_size=0PROD1.__shared_pool_size=130023424PROD1.__streams_pool_size=0*.audit_file_dest='/u01/app/oracle/admin/PROD1/adump'*.audit_trail='db'*.compatible='11.2.0.0.0'*.control_files='/u01/app/oracle/oradata/PROD1/control01.ctl','/u01/app/oracle/oradata/PROD1/control02.ctl'*.db_block_size=8192*.db_domain=''*.db_name='PROD1'*.diagnostic_dest='/u01/app/oracle'*.dispatchers='(PROTOCOL=TCP) (SERVICE=PROD1XDB)'*.memory_max_target=943718400*.memory_target=943718400*.open_cursors=300*.processes=150*.remote_login_passwordfile='EXCLUSIVE'*.undo_tablespace='UNDOTBS1'DB_UNIQUE_NAME=chicagoLOG_ARCHIVE_CONFIG='DG_CONFIG=(chicago,boston)'CONTROL_FILES='/arch1/chicago/control1.ctl', '/arch2/chicago/control2.ctl'LOG_ARCHIVE_DEST_1= 'LOCATION=/arch1/chicago/ VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=chicago'LOG_ARCHIVE_DEST_2= 'SERVICE=boston ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=boston'LOG_ARCHIVE_DEST_STATE_1=ENABLELOG_ARCHIVE_DEST_STATE_2=ENABLEREMOTE_LOGIN_PASSWORDFILE=EXCLUSIVELOG_ARCHIVE_FORMAT=%t_%s_%r.arcLOG_ARCHIVE_MAX_PROCESSES=30 把所有chicago替换成PROD1,把boston替换成SBDB。 %s#chicago#PROD1#gDB_UNIQUE_NAME=PROD1
LOG_ARCHIVE_CONFIG='DG_CONFIG=(PROD1,SBDB)'LOG_ARCHIVE_DEST_1= 'LOCATION=/u01/arch1/PROD1/ VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=PROD1'LOG_ARCHIVE_DEST_2= 'SERVICE=SBDB ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=SBDB'LOG_ARCHIVE_DEST_STATE_1=ENABLELOG_ARCHIVE_DEST_STATE_2=ENABLEREMOTE_LOGIN_PASSWORDFILE=EXCLUSIVELOG_ARCHIVE_FORMAT=%t_%s_%r.arcLOG_ARCHIVE_MAX_PROCESSES=30FAL_SERVER=SBDB
DB_FILE_NAME_CONVERT='SBDB','PROD1'LOG_FILE_NAME_CONVERT='SBDB','PROD1'STANDBY_FILE_MANAGEMENT=AUTO 把没有的目录建上 [oracle@ENMO dbhome_1]$ mkdir -p /u01/arch1/PROD1/ cd /u01/app/oracle/oradata/PROD1[oracle@enmoedu1 dbs]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.3.0 Production on Mon May 1 15:47:19 2017
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to:Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - ProductionWith the Partitioning, OLAP, Data Mining and Real Application Testing optionsSQL> shutdown immediate
Database closed.Database dismounted.ORACLE instance shut down.SQL> create spfile from pfile ;File created.
SQL> startup
ORACLE instance started.Total System Global Area 941600768 bytes
Fixed Size 1348860 bytesVariable Size 515902212 bytesDatabase Buffers 419430400 bytesRedo Buffers 4919296 bytesDatabase mounted.Database opened.将主库的参数文件和口令文件穿到备库里
[oracle@enmoedu1 dbs]$ scp initPROD1.ora orapwPROD1 192.0.2.12:/u01/app/oracle/product/11.2.0/db_1/dbs/
The authenticity of host '192.0.2.12 (192.0.2.12)' can't be established.RSA key fingerprint is 82:c6:92:1a:f8:4c:ca:93:f2:71:cc:41:de:c4:71:08.Are you sure you want to continue connecting (yes/no)? yesWarning: Permanently added '192.0.2.12' (RSA) to the list of known hosts.oracle@192.0.2.12's password: initPROD1.ora 100% 1398 1.4KB/s 00:00 orapwPROD1 100% 1536 1.5KB/s 00:00 [oracle@enmoedu1 dbs]$在备库中查看从主库中拷贝过来的参数文件，并改成备库中的实例名
[oracle@enmoedu2 ~]$ cd /u01/app/oracle/product/11.2.0/db_1/dbs/
[oracle@enmoedu2 dbs]$ lshc_EMREP.dat hc_PROD4.dat init.ora initPROD1.ora orapwPROD1[oracle@enmoedu2 dbs]$ mv initPROD1.ora initSBDB.ora[oracle@enmoedu2 dbs]$ lshc_EMREP.dat hc_PROD4.dat init.ora initSBDB.ora orapwPROD1[oracle@enmoedu2 dbs]$ mv orapwPROD1 orapwSBDB[oracle@enmoedu2 dbs]$ lshc_EMREP.dat hc_PROD4.dat init.ora initSBDB.ora orapwSBDB编辑备库中的参数文件，将PROD1和SBDB换过来
:%s#PROD1#AAA#g
:%s#SBDB#PROD1#g
:%s#AAA#SBDB#g
[oracle@enmoedu2 dbs]$ vi initSBDB.ora
PROD1.__db_cache_size=419430400PROD1.__java_pool_size=4194304PROD1.__large_pool_size=4194304PROD1.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environmentPROD1.__pga_aggregate_target=377487360PROD1.__sga_target=566231040PROD1.__shared_io_pool_size=0PROD1.__shared_pool_size=130023424PROD1.__streams_pool_size=0*.audit_file_dest='/u01/app/oracle/admin/PROD1/adump'*.audit_trail='db'*.compatible='11.2.0.0.0'*.control_files='/u01/app/oracle/oradata/PROD1/control01.ctl','/u01/app/oracle/oradata/PROD1/control02.ctl'*.db_block_size=8192*.db_domain=''*.db_name='PROD1'*.diagnostic_dest='/u01/app/oracle'*.dispatchers='(PROTOCOL=TCP) (SERVICE=PROD1XDB)'*.memory_max_target=943718400*.memory_target=943718400*.open_cursors=300*.processes=150*.remote_login_passwordfile='EXCLUSIVE'*.undo_tablespace='UNDOTBS1'DB_UNIQUE_NAME=SBDBLOG_ARCHIVE_CONFIG='DG_CONFIG=(SBDB,PROD1)'LOG_ARCHIVE_DEST_1= 'LOCATION=/u01/arch1/SBDB/ VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=SBDB'LOG_ARCHIVE_DEST_2= 'SERVICE=PROD1 ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=PROD1'LOG_ARCHIVE_DEST_STATE_1=ENABLELOG_ARCHIVE_DEST_STATE_2=ENABLEREMOTE_LOGIN_PASSWORDFILE=EXCLUSIVELOG_ARCHIVE_FORMAT=%t_%s_%r.arcLOG_ARCHIVE_MAX_PROCESSES=30FAL_SERVER=PROD1
DB_FILE_NAME_CONVERT='PROD1','SBDB'LOG_FILE_NAME_CONVERT='PROD1','SBDB'STANDBY_FILE_MANAGEMENT=AUTO创建备库参数文件中没有的路径
[oracle@enmoedu2 admin]$ mkdir -p /u01/app/oracle/admin/SBDB/adump
[oracle@enmoedu2 admin]$ mkdir -p /u01/app/oracle/oradata/SBDB/[oracle@enmoedu2 admin]$ mkdir -p /u01/arch1/SBDB/ 将数据库启动到nomount状态，来验证备库修改的参数文件是否有问题[oracle@enmoedu2 admin]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.3.0 Production on Mon May 1 16:29:13 2017
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to an idle instance.
SQL> create spfile from pfile;
File created.
SQL> startup nomount;
ORACLE instance started.Total System Global Area 941600768 bytes
Fixed Size 1348860 bytesVariable Size 515902212 bytesDatabase Buffers 419430400 bytesRedo Buffers 4919296 bytes更改备库的监听
[oracle@enmoedu2 admin]$ vi listener.ora
# listener.ora Network Configuration File: /u01/app/oracle/product/11.2.0/db_1/network/admin/listener.ora# Generated by Oracle configuration tools.LISTENER =
(DESCRIPTION_LIST = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = enmoedu2.example.com)(PORT = 1521)) ) )SID_LIST_LISTENER=
(SID_LIST= (SID_DESC= (GLOBAL_DBNAME=SBDB) (SID_NAME=SBDB) (ORACLE_HOME=/u01/app/oracle/product/11.2.0/db_1) ) )[oracle@enmoedu2 admin]$ lsnrctl start
LSNRCTL for Linux: Version 11.2.0.3.0 - Production on 01-MAY-2017 16:55:33
Copyright (c) 1991, 2011, Oracle. All rights reserved.
Starting /u01/app/oracle/product/11.2.0/db_1/bin/tnslsnr: please wait...
TNSLSNR for Linux: Version 11.2.0.3.0 - Production
System parameter file is /u01/app/oracle/product/11.2.0/db_1/network/admin/listener.oraLog messages written to /u01/app/oracle/diag/tnslsnr/enmoedu2/listener/alert/log.xmlListening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=enmoedu2.example.com)(PORT=1521)))Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=enmoedu2.example.com)(PORT=1521)))
STATUS of the LISTENER------------------------Alias LISTENERVersion TNSLSNR for Linux: Version 11.2.0.3.0 - ProductionStart Date 01-MAY-2017 16:55:33Uptime 0 days 0 hr. 0 min. 0 secTrace Level offSecurity ON: Local OS AuthenticationSNMP OFFListener Parameter File /u01/app/oracle/product/11.2.0/db_1/network/admin/listener.oraListener Log File /u01/app/oracle/diag/tnslsnr/enmoedu2/listener/alert/log.xmlListening Endpoints Summary... (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=enmoedu2.example.com)(PORT=1521)))Services Summary...Service "SBDB" has 1 instance(s). Instance "SBDB", status UNKNOWN, has 1 handler(s) for this service...The command completed successfullySQL>alter system register
[oracle@enmoedu2 admin]$ lsnrctl
LSNRCTL for Linux: Version 11.2.0.3.0 - Production on 01-MAY-2017 16:56:01
Copyright (c) 1991, 2011, Oracle. All rights reserved.
Welcome to LSNRCTL, type "help" for information.
因为数据库处于nomount状态，所以此时监听处于blocked状态
LSNRCTL> status
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=enmoedu2.example.com)(PORT=1521)))STATUS of the LISTENER------------------------Alias LISTENERVersion TNSLSNR for Linux: Version 11.2.0.3.0 - ProductionStart Date 01-MAY-2017 16:55:33Uptime 0 days 0 hr. 3 min. 46 secTrace Level offSecurity ON: Local OS AuthenticationSNMP OFFListener Parameter File /u01/app/oracle/product/11.2.0/db_1/network/admin/listener.oraListener Log File /u01/app/oracle/diag/tnslsnr/enmoedu2/listener/alert/log.xmlListening Endpoints Summary... (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=enmoedu2.example.com)(PORT=1521)))Services Summary...Service "SBDB" has 2 instance(s). Instance "SBDB", status UNKNOWN, has 1 handler(s) for this service... Instance "SBDB", status BLOCKED, has 1 handler(s) for this service...The command completed successfully配置主库的tnsnames.ora文件
[oracle@enmoedu1 admin]$ vi tnsnames.ora
# tnsnames.ora Network Configuration File: /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/tnsnames.ora# Generated by Oracle configuration tools.SBDB =
(DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = enmoedu2.example.com)(PORT = 1521)) ) (CONNECT_DATA = (SERVER=DEDICATED) (SERVICE_NAME = SBDB) ) )PROD1 =
(DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = enmoedu1.example.com)(PORT = 1521)) ) (CONNECT_DATA = (SERVER=DEDICATED) (SERVICE_NAME = PROD1) ) )再将tnsnames.ora拷贝到备库的路径下
[oracle@enmoedu1 admin]$ scp tnsnames.ora 192.0.2.12:/u01/app/oracle/product/11.2.0/db_1/network/admin
oracle@192.0.2.12's password: tnsnames.ora 100% 578 0.6KB/s 00:00使用rman auxiliary 恢复数据库（主库）
[oracle@enmoedu1 admin]$ rman target / auxiliary sys/oracle@SBDB
Recovery Manager: Release 11.2.0.3.0 - Production on Mon May 1 17:11:32 2017
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
connected to target database: PROD1 (DBID=2082231315)
connected to auxiliary database: SBDB (not mounted)RMAN> duplicate target database for standby from active database;
Starting Duplicate Db at 01-MAY-17
using target database control file instead of recovery catalogallocated channel: ORA_AUX_DISK_1channel ORA_AUX_DISK_1: SID=10 device type=DISKcontents of Memory Script:
{ backup as copy reuse targetfile '/u01/app/oracle/product/11.2.0/dbhome_1/dbs/orapwPROD1' auxiliary format '/u01/app/oracle/product/11.2.0/db_1/dbs/orapwSBDB' ;}executing Memory ScriptStarting backup at 01-MAY-17
allocated channel: ORA_DISK_1channel ORA_DISK_1: SID=157 device type=DISKFinished backup at 01-MAY-17contents of Memory Script:
{ backup as copy current controlfile for standby auxiliary format '/u01/app/oracle/oradata/SBDB/control01.ctl'; restore clone controlfile to '/u01/app/oracle/oradata/SBDB/control02.ctl' from '/u01/app/oracle/oradata/SBDB/control01.ctl';}executing Memory ScriptStarting backup at 01-MAY-17
using channel ORA_DISK_1channel ORA_DISK_1: starting datafile copycopying standby control fileoutput file name=/u01/app/oracle/product/11.2.0/dbhome_1/dbs/snapcf_PROD1.f tag=TAG20170501T173047 RECID=7 STAMP=942859847channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01Finished backup at 01-MAY-17Starting restore at 01-MAY-17
using channel ORA_AUX_DISK_1channel ORA_AUX_DISK_1: copied control file copy
Finished restore at 01-MAY-17contents of Memory Script:
{ sql clone 'alter database mount standby database';}executing Memory Scriptsql statement: alter database mount standby database
contents of Memory Script:
{ set newname for tempfile 1 to "/u01/app/oracle/oradata/SBDB/temp01.dbf"; switch clone tempfile all; set newname for datafile 1 to "/u01/app/oracle/oradata/SBDB/system01.dbf"; set newname for datafile 2 to "/u01/app/oracle/oradata/SBDB/sysaux01.dbf"; set newname for datafile 3 to "/u01/app/oracle/oradata/SBDB/undotbs01.dbf"; set newname for datafile 4 to "/u01/app/oracle/oradata/SBDB/users01.dbf"; set newname for datafile 5 to "/u01/app/oracle/oradata/SBDB/example01.dbf"; backup as copy reuse datafile 1 auxiliary format "/u01/app/oracle/oradata/SBDB/system01.dbf" datafile 2 auxiliary format "/u01/app/oracle/oradata/SBDB/sysaux01.dbf" datafile 3 auxiliary format "/u01/app/oracle/oradata/SBDB/undotbs01.dbf" datafile 4 auxiliary format "/u01/app/oracle/oradata/SBDB/users01.dbf" datafile 5 auxiliary format "/u01/app/oracle/oradata/SBDB/example01.dbf" ; sql 'alter system archive log current';}executing Memory Scriptexecuting command: SET NEWNAME
renamed tempfile 1 to /u01/app/oracle/oradata/SBDB/temp01.dbf in control file
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
Starting backup at 01-MAY-17
using channel ORA_DISK_1channel ORA_DISK_1: starting datafile copyinput datafile file number=00001 name=/u01/app/oracle/oradata/PROD1/system01.dbfoutput file name=/u01/app/oracle/oradata/SBDB/system01.dbf tag=TAG20170501T173054channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:35channel ORA_DISK_1: starting datafile copyinput datafile file number=00002 name=/u01/app/oracle/oradata/PROD1/sysaux01.dbfoutput file name=/u01/app/oracle/oradata/SBDB/sysaux01.dbf tag=TAG20170501T173054channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:25channel ORA_DISK_1: starting datafile copyinput datafile file number=00005 name=/u01/app/oracle/oradata/PROD1/example01.dbfoutput file name=/u01/app/oracle/oradata/SBDB/example01.dbf tag=TAG20170501T173054channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:15channel ORA_DISK_1: starting datafile copyinput datafile file number=00003 name=/u01/app/oracle/oradata/PROD1/undotbs01.dbfoutput file name=/u01/app/oracle/oradata/SBDB/undotbs01.dbf tag=TAG20170501T173054channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:07channel ORA_DISK_1: starting datafile copyinput datafile file number=00004 name=/u01/app/oracle/oradata/PROD1/users01.dbfoutput file name=/u01/app/oracle/oradata/SBDB/users01.dbf tag=TAG20170501T173054channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01Finished backup at 01-MAY-17sql statement: alter system archive log current
contents of Memory Script:
{ switch clone datafile all;}executing Memory Scriptdatafile 1 switched to datafile copy
input datafile copy RECID=7 STAMP=942859938 file name=/u01/app/oracle/oradata/SBDB/system01.dbfdatafile 2 switched to datafile copyinput datafile copy RECID=8 STAMP=942859938 file name=/u01/app/oracle/oradata/SBDB/sysaux01.dbfdatafile 3 switched to datafile copyinput datafile copy RECID=9 STAMP=942859938 file name=/u01/app/oracle/oradata/SBDB/undotbs01.dbfdatafile 4 switched to datafile copyinput datafile copy RECID=10 STAMP=942859938 file name=/u01/app/oracle/oradata/SBDB/users01.dbfdatafile 5 switched to datafile copyinput datafile copy RECID=11 STAMP=942859938 file name=/u01/app/oracle/oradata/SBDB/example01.dbfFinished Duplicate Db at 01-MAY-17应用日志同步数据（备库）
[oracle@enmoedu2 SBDB]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.3.0 Production on Mon May 1 17:34:02 2017
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to:Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - ProductionWith the Partitioning, OLAP, Data Mining and Real Application Testing optionsSQL> select status from v$instance;
STATUS
------------MOUNTEDSQL> recover managed standby database using current logfile disconnect from session;
Media recovery complete.SQL> recover managed standby database cancel;Media recovery complete.SQL> select status from v$instance;STATUS
------------MOUNTEDSQL> alter database open;
Database altered.