环境：
primary:
OS: Red Hat Enterprise 5.8
IP: 10.131.119.118
DBVersion: 11.2.0.3.0
db_name: orcl
db_unique_name: orcl
standby:
OS: Red Hat Enterprise 5.8
IP: 10.131.119.119
DBVersion: 11.2.0.3.0
db_name: orcl
db_unique_name: std
步骤1：将主库设置为归档模式与force_logging模式
SQL>shutdown immediate;
SQL>startup mount;
SQL>alter database archivelog;
SQL>alter database open;
SQL>alter database force logging;
SQL> select DBID,NAME,LOG_MODE,FORCE_LOGGING from v$database;
DBID NAME LOG_MODE FOR
---------- --------- ------------ ---
1414106639 ORCL ARCHIVELOG YES
步骤2：将主库的pfile文件和密码文件拷备到备库
[oracle@CS-SI-DG01 dbs]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.3.0 Production on Wed Jan 27 16:05:21 2016
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> create pfile from spfile;
File created.
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@CS-SI-DG01 dbs]$ scp initorcl.ora 10.131.119.119:/u2/oracle/product/11.2.0/dbhome_1/dbs/initstd.ora
oracle@10.131.119.119's password:
stty: standard input: Invalid argument
initorcl.ora 100% 1112 1.1KB/s 00:00
[oracle@CS-SI-DG01 dbs]$ scp orapworcl 10.131.119.119:/u2/oracle/product/11.2.0/dbhome_1/dbs/orapwstd
oracle@10.131.119.119's password:
stty: standard input: Invalid argument
orapworcl 100% 1536 1.5KB/s 00:00
步骤3：设置主库的监听与TNS
监听：
[oracle@CS-SI-DG01 admin]$ cat listener.ora
# listener.ora Network Configuration File: /u2/oracle/product/11.2.0/dbhome_1/network/admin/listener.ora
# Generated by Oracle configuration tools.
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = orcl)
(ORACLE_HOME = /u2/oracle/product/11.2.0/dbhome_1)
(SID_NAME = orcl)
)
)
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.131.119.118)(PORT = 1521))
)
(DESCRIPTION =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
)
)
ADR_BASE_LISTENER = /u2/oracle
[oracle@CS-SI-DG01 ~]$ lsnrctl reload
TNS：
[oracle@CS-SI-DG01 admin]$ cat tnsnames.ora
# tnsnames.ora Network Configuration File: /u2/oracle/product/11.2.0/dbhome_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.
ORCL =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.131.119.118)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orcl)
)
)
std =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.131.119.119)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = std)
)
)
步骤4：修改主库的参数
[oracle@CS-SI-DG01 admin]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.3.0 Production on Wed Jan 27 16:19:40 2016
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> alter system set log_archive_config='dg_config=(orcl,std)';
System altered.
SQL> alter system set log_archive_dest_2='SERVICE=std ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=std';
System altered.
SQL> alter system set log_archive_dest_state_2=enable;
System altered.
SQL> alter system set log_archive_dest_state_1=enable;
System altered.
SQL> alter system set fal_server='std';
System altered.
SQL> alter system set fal_client='orcl';
System altered.
SQL> alter system set standby_file_management='auto';
System altered.
注：修改好参数后重新启动数据库
步骤5：修改备库参数文件
[oracle@CS-SI-DG02 dbs]$ cat initstd.ora
*._undo_autotune=TRUE
*.audit_file_dest='/u2/oracle/admin/std/adump'
*.audit_trail='db'
*.compatible='11.2.0.0.0'
*.control_files='/oradata/std/control01.ctl','/oradata/std/control02.ctl'#Restore Controlfile
*.db_block_size=8192
*.db_domain=''
*.db_name='orcl'
*.db_recovery_file_dest='/u2/oracle/fast_recovery_area'
*.db_recovery_file_dest_size=8589934592
*.diagnostic_dest='/u2/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=stdXDB)'
*.job_queue_processes=1000
*.log_archive_dest_1='location=/oradata/arch/std'
*.log_archive_dest_10=' '
*.memory_max_target=0
*.memory_target=0
*.open_cursors=300
*.pga_aggregate_target=171966464
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.session_cached_cursors=240
*.sga_target=335544320
*.undo_retention=60
*.undo_tablespace='UNDOTBS2'
*.log_archive_dest_2='SERVICE=orcl ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=orcl'
*.log_archive_config='dg_config=(orcl,std)'
*.log_archive_dest_state_2=enable
*.log_archive_dest_state_1=enable
*.fal_server='orcl'
*.fal_client='std'
*.standby_file_management='auto'
*.log_file_name_convert=('/oradata/orcl','/oradata/std')
*.db_file_name_convert=('/oradata/orcl','/oradata/std')
*.db_unique_name='std'
步骤6：在备库创建目录
[oracle@CS-SI-DG02 dbs]$ mkdir -p /u2/oracle/admin/std/adump
[oracle@CS-SI-DG02 dbs]$ mkdir -p /oradata/std/
[oracle@CS-SI-DG02 dbs]$ mkdir -p /oradata/arch/std
步骤7：设置备库的监听与TNS
监听：
[oracle@CS-SI-DG02 admin]$ cat listener.ora
# listener.ora Network Configuration File: /u2/oracle/product/11.2.0/dbhome_1/network/admin/listener.ora
# Generated by Oracle configuration tools.
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = std)
(ORACLE_HOME = /u2/oracle/product/11.2.0/dbhome_1)
(SID_NAME = std)
)
)
ADR_BASE_LISTENER = /u2/oracle
STENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.131.119.119)(PORT = 1521))
)
(DESCRIPTION =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
)
)
[oracle@CS-SI-DG02 admin]$ lsnrctl reload
TNS：
[oracle@CS-SI-DG02 admin]$ cat tnsnames.ora
# tnsnames.ora Network Configuration File: /u2/oracle/product/11.2.0/dbhome_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.
ORCL =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.131.119.118)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = orcl)
)
)
std =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.131.119.119)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = std)
)
)
步骤8：将备库启动到nomount状态
[oracle@CS-SI-DG02 admin]$ export ORACLE_SID=std
[oracle@CS-SI-DG02 admin]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.3.0 Production on Wed Jan 27 16:56:58 2016
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup nomount;
ORACLE instance started.
Total System Global Area 509411328 bytes
Fixed Size 2229824 bytes
Variable Size 285215168 bytes
Database Buffers 213909504 bytes
Redo Buffers 8056832 bytes
步骤9：在备库恢复数据库
[oracle@CS-SI-DG02 admin]$ rman target sys/oracle@orcl auxiliary sys/oracle@std nocatalog
Recovery Manager: Release 11.2.0.3.0 - Production on Wed Jan 27 17:30:29 2016
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
connected to target database: ORCL (DBID=1414106639)
using target database control file instead of recovery catalog
connected to auxiliary database: ORCL (not mounted)
RMAN> duplicate target database for standby from active database;
Starting Duplicate Db at 27-JAN-16
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=134 device type=DISK
contents of Memory Script:
{
backup as copy reuse
targetfile '/u2/oracle/product/11.2.0/dbhome_1/dbs/orapworcl' auxiliary format
'/u2/oracle/product/11.2.0/dbhome_1/dbs/orapwstd' ;
}
executing Memory Script
Starting backup at 27-JAN-16
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=22 device type=DISK
Finished backup at 27-JAN-16
contents of Memory Script:
{
backup as copy current controlfile for standby auxiliary format '/oradata/std/control01.ctl';
restore clone controlfile to '/oradata/std/control02.ctl' from
'/oradata/std/control01.ctl';
}
executing Memory Script
Starting backup at 27-JAN-16
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile copy
copying standby control file
output file name=/u2/oracle/product/11.2.0/dbhome_1/dbs/snapcf_orcl.f tag=TAG20160127T173104 RECID=25 STAMP=902251864
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01
Finished backup at 27-JAN-16
Starting restore at 27-JAN-16
using channel ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: copied control file copy
Finished restore at 27-JAN-16
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
"/oradata/std/temp01.dbf";
switch clone tempfile all;
set newname for datafile 1 to
"/oradata/std/system01.dbf";
set newname for datafile 2 to
"/oradata/std/sysaux01.dbf";
set newname for datafile 3 to
"/oradata/std/test01.dbf";
set newname for datafile 4 to
"/oradata/std/users01.dbf";
set newname for datafile 6 to
"/oradata/std/undotbs02.dbf";
set newname for datafile 7 to
"/oradata/std/tbspart01.dbf";
set newname for datafile 8 to
"/oradata/std/tbspart02.dbf";
set newname for datafile 9 to
"/oradata/std/tbspart03.dbf";
set newname for datafile 11 to
"/oradata/std/assm01.dbf";
set newname for datafile 12 to
"/oradata/std/tbspart04.dbf";
set newname for datafile 13 to
"/oradata/std/rman01.dbf";
set newname for datafile 14 to
"/oradata/std/test1.dbf";
set newname for datafile 15 to
"/oradata/std/test2.dbf";
set newname for datafile 16 to
"/oradata/std/linktbs01.dbf";
set newname for datafile 17 to
"/oradata/std/ogg01.dbf";
set newname for datafile 18 to
"/oradata/std/tom101.dbf";
backup as copy reuse
datafile 1 auxiliary format
"/oradata/std/system01.dbf" datafile
2 auxiliary format
"/oradata/std/sysaux01.dbf" datafile
3 auxiliary format
"/oradata/std/test01.dbf" datafile
4 auxiliary format
"/oradata/std/users01.dbf" datafile
6 auxiliary format
"/oradata/std/undotbs02.dbf" datafile
7 auxiliary format
"/oradata/std/tbspart01.dbf" datafile
8 auxiliary format
"/oradata/std/tbspart02.dbf" datafile
9 auxiliary format
"/oradata/std/tbspart03.dbf" datafile
11 auxiliary format
"/oradata/std/assm01.dbf" datafile
12 auxiliary format
"/oradata/std/tbspart04.dbf" datafile
13 auxiliary format
"/oradata/std/rman01.dbf" datafile
14 auxiliary format
"/oradata/std/test1.dbf" datafile
15 auxiliary format
"/oradata/std/test2.dbf" datafile
16 auxiliary format
"/oradata/std/linktbs01.dbf" datafile
17 auxiliary format
"/oradata/std/ogg01.dbf" datafile
18 auxiliary format
"/oradata/std/tom101.dbf" ;
sql 'alter system archive log current';
}
executing Memory Script
executing command: SET NEWNAME
renamed tempfile 1 to /oradata/std/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
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
Starting backup at 27-JAN-16
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile copy
input datafile file number=00002 name=/oradata/orcl/sysaux01.dbf
output file name=/oradata/std/sysaux01.dbf tag=TAG20160127T173112
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:01:06
channel ORA_DISK_1: starting datafile copy
input datafile file number=00001 name=/oradata/orcl/system01.dbf
output file name=/oradata/std/system01.dbf tag=TAG20160127T173112
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:46
channel ORA_DISK_1: starting datafile copy
input datafile file number=00006 name=/oradata/orcl/undotbs02.dbf
output file name=/oradata/std/undotbs02.dbf tag=TAG20160127T173112
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:15
channel ORA_DISK_1: starting datafile copy
input datafile file number=00003 name=/oradata/orcl/test01.dbf
output file name=/oradata/std/test01.dbf tag=TAG20160127T173112
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:07
channel ORA_DISK_1: starting datafile copy
input datafile file number=00011 name=/oradata/orcl/assm01.dbf
output file name=/oradata/std/assm01.dbf tag=TAG20160127T173112
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:07
channel ORA_DISK_1: starting datafile copy
input datafile file number=00017 name=/oradata/orcl/ogg01.dbf
output file name=/oradata/std/ogg01.dbf tag=TAG20160127T173112
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:07
channel ORA_DISK_1: starting datafile copy
input datafile file number=00018 name=/oradata/orcl/tom101.dbf
output file name=/oradata/std/tom101.dbf tag=TAG20160127T173112
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:07
channel ORA_DISK_1: starting datafile copy
input datafile file number=00004 name=/oradata/orcl/users01.dbf
output file name=/oradata/std/users01.dbf tag=TAG20160127T173112
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:07
channel ORA_DISK_1: starting datafile copy
input datafile file number=00007 name=/oradata/orcl/tbspart01.dbf
output file name=/oradata/std/tbspart01.dbf tag=TAG20160127T173112
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01
channel ORA_DISK_1: starting datafile copy
input datafile file number=00008 name=/oradata/orcl/tbspart02.dbf
output file name=/oradata/std/tbspart02.dbf tag=TAG20160127T173112
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01
channel ORA_DISK_1: starting datafile copy
input datafile file number=00009 name=/oradata/orcl/tbspart03.dbf
output file name=/oradata/std/tbspart03.dbf tag=TAG20160127T173112
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01
channel ORA_DISK_1: starting datafile copy
input datafile file number=00012 name=/oradata/orcl/tbspart04.dbf
output file name=/oradata/std/tbspart04.dbf tag=TAG20160127T173112
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01
channel ORA_DISK_1: starting datafile copy
input datafile file number=00013 name=/oradata/orcl/rman01.dbf
output file name=/oradata/std/rman01.dbf tag=TAG20160127T173112
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01
channel ORA_DISK_1: starting datafile copy
input datafile file number=00014 name=/oradata/orcl/test1.dbf
output file name=/oradata/std/test1.dbf tag=TAG20160127T173112
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01
channel ORA_DISK_1: starting datafile copy
input datafile file number=00015 name=/oradata/orcl/test2.dbf
output file name=/oradata/std/test2.dbf tag=TAG20160127T173112
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01
channel ORA_DISK_1: starting datafile copy
input datafile file number=00016 name=/oradata/orcl/linktbs01.dbf
output file name=/oradata/std/linktbs01.dbf tag=TAG20160127T173112
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01
Finished backup at 27-JAN-16
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=25 STAMP=902252046 file name=/oradata/std/system01.dbf
datafile 2 switched to datafile copy
input datafile copy RECID=26 STAMP=902252046 file name=/oradata/std/sysaux01.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=27 STAMP=902252046 file name=/oradata/std/test01.dbf
datafile 4 switched to datafile copy
input datafile copy RECID=28 STAMP=902252046 file name=/oradata/std/users01.dbf
datafile 6 switched to datafile copy
input datafile copy RECID=29 STAMP=902252046 file name=/oradata/std/undotbs02.dbf
datafile 7 switched to datafile copy
input datafile copy RECID=30 STAMP=902252046 file name=/oradata/std/tbspart01.dbf
datafile 8 switched to datafile copy
input datafile copy RECID=31 STAMP=902252046 file name=/oradata/std/tbspart02.dbf
datafile 9 switched to datafile copy
input datafile copy RECID=32 STAMP=902252046 file name=/oradata/std/tbspart03.dbf
datafile 11 switched to datafile copy
input datafile copy RECID=33 STAMP=902252046 file name=/oradata/std/assm01.dbf
datafile 12 switched to datafile copy
input datafile copy RECID=34 STAMP=902252046 file name=/oradata/std/tbspart04.dbf
datafile 13 switched to datafile copy
input datafile copy RECID=35 STAMP=902252046 file name=/oradata/std/rman01.dbf
datafile 14 switched to datafile copy
input datafile copy RECID=36 STAMP=902252046 file name=/oradata/std/test1.dbf
datafile 15 switched to datafile copy
input datafile copy RECID=37 STAMP=902252046 file name=/oradata/std/test2.dbf
datafile 16 switched to datafile copy
input datafile copy RECID=38 STAMP=902252046 file name=/oradata/std/linktbs01.dbf
datafile 17 switched to datafile copy
input datafile copy RECID=39 STAMP=902252046 file name=/oradata/std/ogg01.dbf
datafile 18 switched to datafile copy
input datafile copy RECID=40 STAMP=902252046 file name=/oradata/std/tom101.dbf
Finished Duplicate Db at 27-JAN-16
步骤10：在备库创建standby日志
[oracle@CS-SI-DG02 dbs]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.3.0 Production on Wed Jan 27 18:06:26 2016
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> select open_mode from v$database;
OPEN_MODE
--------------------
MOUNTED
SQL> select * from v$logfile;
GROUP# STATUS TYPE MEMBER IS_
---------- ---------- ---------- ------------------------------ ---
1 ONLINE /oradata/std/redo03.log NO
2 ONLINE /oradata/std/redo02.log NO
3 ONLINE /oradata/std/redo01.log NO
3 rows selected.
SQL> alter database add standby logfile group 4 '/oradata/std/stb_redo04.log' size 50M;
Database altered.
SQL> alter database add standby logfile group 5 '/oradata/std/stb_redo5.log' size 50M;
Database altered.
SQL> alter database add standby logfile group 6 '/oradata/std/stb_redo06.log' size 50M
2 ;
Database altered.
SQL> alter database add standby logfile group 7 '/oradata/std/stb_redo07.log' size 50M;
Database altered.
SQL> select * from v$logfile;
GROUP# STATUS TYPE MEMBER IS_
---------- ---------- ---------- ------------------------------ ---
1 ONLINE /oradata/std/redo03.log NO
2 ONLINE /oradata/std/redo02.log NO
3 ONLINE /oradata/std/redo01.log NO
4 STANDBY /oradata/std/stb_redo04.log NO
5 STANDBY /oradata/std/stb_redo5.log NO
6 STANDBY /oradata/std/stb_redo06.log NO
7 STANDBY /oradata/std/stb_redo07.log NO
7 rows selected.
步骤11：启动DG
[oracle@CS-SI-DG02 admin]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.3.0 Production on Wed Jan 27 18:19:34 2016
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> alter database open read only;
Database altered.
SQL> alter database recover managed standby database disconnect from session using current logfile;
Database altered.
