前言：（一）准备工作：
1.数据库要处于归档模式；
2.监听参数：local_listener 默认值为空--1521
3.关闭闪回（可能会触发数据库的bug，备库不能开闪回）
4.如果有外部表，外部表要删除。
（二）环境描述：
192.168.206.8 主库 ORA11GR2
192.168.206.9 备库 SBDB
一，将数据库处于归档模式：
SQL> alter database archivelog; Database altered. SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /u01/app/oracle/product/11.2.0/dbhome_1/dbs/arch
Oldest online log sequence 5
Next log sequence to archive 7
Current log sequence 7
二，查看本地监听，默认值为空：
SQL> show parameter local_listener; NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
local_listener string
三，关闪回（可能会触发数据库bug，备库不能开闪回）
查看显示关闭：
SQL> select flashback_on from v$database; FLASHBACK_ON
------------------
NO
四，生成一个文本参数文件修改参数文件：
SQL> create pfile from spfile; File created. [oracle@host08 ~]$ cd $ORACLE_HOME/dbs
[oracle@host08 dbs]$ ls
hc_ORA11GR2.dat init.ora initORA11GR2.ora lkORA11GR2 orapwORA11GR2 spfileORA11GR2.ora
[oracle@host08 dbs]$ vi initORA11GR2.ora （将一下参数加入）
DB_NAME=ORA11GR2
DB_UNIQUE_NAME=ORA11GR2
LOG_ARCHIVE_CONFIG='DG_CONFIG=(ORA11GR2,SBDB)'
CONTROL_FILES='/u01/app/oracle/oradata/ORA11GR2/control01.ctl','/u01/app/oracle/oradata/ORA11GR2/control02.ctl'
LOG_ARCHIVE_DEST_1=
'LOCATION=/u01/arch1/ORA11GR2/
VALID_FOR=(ALL_LOGFILES,ALL_ROLES)
DB_UNIQUE_NAME=ORA11GR2'
LOG_ARCHIVE_DEST_2=
'SERVICE=SBDB ASYNC
VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE)
DB_UNIQUE_NAME=SBDB'
LOG_ARCHIVE_DEST_STATE_1=ENABLE
LOG_ARCHIVE_DEST_STATE_2=ENABLE
REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE
LOG_ARCHIVE_FORMAT=%t_%s_%r.arc
LOG_ARCHIVE_MAX_PROCESSES=30
FAL_SERVER=SBDB
DB_FILE_NAME_CONVERT='SBDB','ORA11GR2'
LOG_FILE_NAME_CONVERT=
'/u01/app/oracle/oradata/SBDB/','/u01/app/oracle/oradata/ORA11GR2/'
STANDBY_FILE_MANAGEMENT=AUTO
创建参数中涉及到的目录：
[oracle@host08 dbs]$ mkdir -p /u01/app/oracle/admin/ORA11GR2/adump
[oracle@host08 dbs]$ mkdir -p /u01/app/oracle/oradata/ORA11GR2/
[oracle@host08 dbs]$ mkdir -p /u01/app/oracle
[oracle@host08 dbs]$ mkdir -p /u01/app/oracle/oradata/ORA11GR2/
[oracle@host08 dbs]$ mkdir -p /u01/arch1/ORA11GR2/
[oracle@host08 dbs]$ mkdir -p /u01/app/oracle/oradata/SBDB/
[oracle@host08 dbs]$ mkdir -p /u01/app/oracle/oradata/ORA11GR2/
五，开启force logging
SQL> alter database FORCE LOGGING; Database altered. SQL> select FORCE_LOGGING from v$database; FOR
---
YES
六，查看日志文件及大小，并增加standby logfile：
SQL> select GROUP#,MEMBER from v$logfile;
GROUP# MEMBER
---------- --------------------------------------------------
3 /u01/app/oracle/oradata/ORA11GR2/redo03.log
2 /u01/app/oracle/oradata/ORA11GR2/redo02.log
1 /u01/app/oracle/oradata/ORA11GR2/redo01.log
SQL> alter database add standby logfile group 4('/u01/app/oracle/oradata/ORA11GR2/redo401_std.log','/u01/app/oracle/oradata/ORA11GR2/redo402_std.log') size 50M;
Database altered.
SQL> c/4/5
1* alter database add standby logfile group 5('/u01/app/oracle/oradata/ORA11GR2/redo401_std.log','/u01/app/oracle/oradata/ORA11GR2/redo402_std.log') size 50M
SQL> c/4/5
1* alter database add standby logfile group 5('/u01/app/oracle/oradata/ORA11GR2/redo501_std.log','/u01/app/oracle/oradata/ORA11GR2/redo402_std.log') size 50M
SQL> c/4/5
1* alter database add standby logfile group 5('/u01/app/oracle/oradata/ORA11GR2/redo501_std.log','/u01/app/oracle/oradata/ORA11GR2/redo502_std.log') size 50M
SQL> /
Database altered.
SQL> c/5/6
1* alter database add standby logfile group 6('/u01/app/oracle/oradata/ORA11GR2/redo501_std.log','/u01/app/oracle/oradata/ORA11GR2/redo502_std.log') size 50M
SQL> c/5/6
1* alter database add standby logfile group 6('/u01/app/oracle/oradata/ORA11GR2/redo601_std.log','/u01/app/oracle/oradata/ORA11GR2/redo502_std.log') size 50M
SQL> c/5/6
1* alter database add standby logfile group 6('/u01/app/oracle/oradata/ORA11GR2/redo601_std.log','/u01/app/oracle/oradata/ORA11GR2/redo602_std.log') size 50M
SQL> /
Database altered.
SQL> c/6/7
1* alter database add standby logfile group 7('/u01/app/oracle/oradata/ORA11GR2/redo601_std.log','/u01/app/oracle/oradata/ORA11GR2/redo602_std.log') size 50M
SQL> c/6/7
1* alter database add standby logfile group 7('/u01/app/oracle/oradata/ORA11GR2/redo701_std.log','/u01/app/oracle/oradata/ORA11GR2/redo602_std.log') size 50M
SQL> c/6/7
1* alter database add standby logfile group 7('/u01/app/oracle/oradata/ORA11GR2/redo701_std.log','/u01/app/oracle/oradata/ORA11GR2/redo702_std.log') size 50M
SQL> /
Database altered.
SQL> select GROUP#,MEMBER from v$logfile;
GROUP# MEMBER
---------- --------------------------------------------------
3 /u01/app/oracle/oradata/ORA11GR2/redo03.log
2 /u01/app/oracle/oradata/ORA11GR2/redo02.log
1 /u01/app/oracle/oradata/ORA11GR2/redo01.log
4 /u01/app/oracle/oradata/ORA11GR2/redo401_std.log
4 /u01/app/oracle/oradata/ORA11GR2/redo402_std.log
5 /u01/app/oracle/oradata/ORA11GR2/redo501_std.log
5 /u01/app/oracle/oradata/ORA11GR2/redo502_std.log
6 /u01/app/oracle/oradata/ORA11GR2/redo601_std.log
6 /u01/app/oracle/oradata/ORA11GR2/redo602_std.log
7 /u01/app/oracle/oradata/ORA11GR2/redo701_std.log
7 /u01/app/oracle/oradata/ORA11GR2/redo702_std.log
11 rows selected.
七，将主库参数文件和口令文件传给备库：
[oracle@host08 dbs]$ ls
hc_ORA11GR2.dat init.ora initORA11GR2.ora lkORA11GR2 orapwORA11GR2 spfileORA11GR2.ora [oracle@host08 dbs]$ scp initORA11GR2.ora orapwORA11GR2 192.168.206.9:/u01/app/oracle/product/11.2.0/dbhome_1/dbs
The authenticity of host '192.168.206.9 (192.168.206.9)' can't be established.
RSA key fingerprint is 3b:3d:b7:e2:77:91:2d:27:57:87:96:96:b6:56:d2:1e.
Are you sure you want to continue connecting (yes/no)? yes
Warning: Permanently added '192.168.206.9' (RSA) to the list of known hosts.
oracle@192.168.206.9's password:
initORA11GR2.ora 100% 1610 1.6KB/s 00:00
orapwORA11GR2 100% 1536 1.5KB/s 00:00
八，修改备库参数文件：（此时备库的DB_NAME 应该跟主库一致）
DB_NAME=ORA11GR2
DB_UNIQUE_NAME=SBDB
LOG_ARCHIVE_CONFIG='DG_CONFIG=(SBDB,ORA11GR2)'
CONTROL_FILES='/u01/app/oracle/oradata/SBDB/control01.ctl','/u01/app/oracle/oradata/SBDB/control02.ctl'
LOG_ARCHIVE_DEST_1=
'LOCATION=/u01/arch1/SBDB/
VALID_FOR=(ALL_LOGFILES,ALL_ROLES)
DB_UNIQUE_NAME=SBDB'
LOG_ARCHIVE_DEST_2=
'SERVICE=ORA11GR2 ASYNC
VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE)
DB_UNIQUE_NAME=ORA11GR2'
LOG_ARCHIVE_DEST_STATE_1=ENABLE
LOG_ARCHIVE_DEST_STATE_2=ENABLE
REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE
LOG_ARCHIVE_FORMAT=%t_%s_%r.arc
LOG_ARCHIVE_MAX_PROCESSES=30
FAL_SERVER=ORA11GR2
DB_FILE_NAME_CONVERT='ORA11GR2','SBDB'
LOG_FILE_NAME_CONVERT=
'/u01/app/oracle/oradata/ORA11GR2/','/u01/app/oracle/oradata/SBDB/'
STANDBY_FILE_MANAGEMENT=AUTO
九，创建参数中涉及到的目录：
[oracle@host09 dbs]$ mkdir -p /u01/app/oracle/admin/SBDB/adump
[oracle@host09 dbs]$ mkdir -p /u01/app/oracle/oradata/SBDB/
[oracle@host09 dbs]$ mkdir -p /u01/app/oracle
[oracle@host09 dbs]$ mkdir -p /u01/app/oracle/oradata/SBDB/
[oracle@host09 dbs]$ mkdir -p /u01/arch1/SBDB/
[oracle@host09 dbs]$ mkdir -p /u01/app/oracle/oradata/ORA11GR2/
[oracle@host09 dbs]$ mkdir -p /u01/app/oracle/oradata/SBDB/
十，配置网络监听：
主库：
①[oracle@host08 admin]$ vi listener.ora # listener.ora Network Configuration File: /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/listener.ora
# Generated by Oracle configuration tools. LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = host08)(PORT = 1521))
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
)
) ADR_BASE_LISTENER = /u01/app/oracle SID_LIST_LISTENER=
(SID_LIST=
(SID_DESC=
(GLOBAL_DBNAME=ORA11GR2)
(SID_NAME=ORA11GR2)
(ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1)
)
) ②[oracle@host08 admin]$ vi tnsnames.ora SBDB=
(DESCRIPTION=
(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.206.9)(PORT=1521))
(CONNECT_DATA=
(SERVER=DEDICATED)
(SERVICE_NAME=SBDB)
)
)
③查看状态并验证是否ping通：
LSNRCTL> status
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=host08)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 11.2.0.4.0 - Production
Start Date 24-OCT-2016 11:17:20
Uptime 0 days 0 hr. 0 min. 49 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/listener.ora
Listener Log File /u01/app/oracle/diag/tnslsnr/host08/listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=host08)(PORT=1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
Services Summary...
Service "ORA11GR2" has 1 instance(s).
Instance "ORA11GR2", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully [oracle@host08 admin]$ tnsping SBDB TNS Ping Utility for Linux: Version 11.2.0.4.0 - Production on 24-OCT-2016 11:23:18 Copyright (c) 1997, 2013, Oracle. All rights reserved. Used parameter files: Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION= (ADDRESS=(PROTOCOL=TCP)(HOST=192.168.206.9)(PORT=1521)) (CONNECT_DATA= (SERVER=DEDICATED) (SERVICE_NAME=SBDB)))
OK (10 msec) [oracle@host08 admin]$ sqlplus sys/oracle@SBDB as sysdba SQL*Plus: Release 11.2.0.4.0 Production on Mon Oct 24 11:23:37 2016 Copyright (c) 1982, 2013, Oracle. All rights reserved. Connected to an idle instance. SQL>
备库：
①[oracle@host09 admin]$ vi listener.ora
# listener.ora Network Configuration File: /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/listener.ora
# Generated by Oracle configuration tools. LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = host09)(PORT = 1521))
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
)
) ADR_BASE_LISTENER = /u01/app/oracle SID_LIST_LISTENER=
(SID_LIST=
(SID_DESC=
(GLOBAL_DBNAME=SBDB)
(SID_NAME=SBDB)
(ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1)
)
) ②[oracle@host09 admin]$ vi tnsnames.ora ORA11GR2=
(DESCRIPTION=
(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.206.8)(PORT=1521))
(CONNECT_DATA=
(SERVER=DEDICATED)
(SERVICE_NAME=ORA11GR2)
)
)
③查看状态并验证是否ping通：
LSNRCTL> status
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=host09)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 11.2.0.4.0 - Production
Start Date 24-OCT-2016 11:17:47
Uptime 0 days 0 hr. 7 min. 0 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/listener.ora
Listener Log File /u01/app/oracle/diag/tnslsnr/host09/listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=host09)(PORT=1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
Services Summary...
Service "SBDB" has 1 instance(s).
Instance "SBDB", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully oracle@host09 admin]$ tnsping ORA11GR2 TNS Ping Utility for Linux: Version 11.2.0.4.0 - Production on 24-OCT-2016 11:25:09 Copyright (c) 1997, 2013, Oracle. All rights reserved. Used parameter files: Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION= (ADDRESS=(PROTOCOL=TCP)(HOST=192.168.206.8)(PORT=1521)) (CONNECT_DATA= (SERVER=DEDICATED) (SERVICE_NAME=ORA11GR2)))
OK (10 msec) [oracle@host09 admin]$ sqlplus sys/oracle@ORA11GR2 as sysdba SQL*Plus: Release 11.2.0.4.0 Production on Mon Oct 24 11:25:45 2016 Copyright (c) 1982, 2013, Oracle. All rights reserved. Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options SQL>
十一，复制主库生成备库：
①此时将备库起到nmount状态
SQL> startup nomount
ORACLE instance started. Total System Global Area 830930944 bytes
Fixed Size 2257800 bytes
Variable Size 536874104 bytes
Database Buffers 285212672 bytes
Redo Buffers 6586368 bytes
②根据pfile创建spfile：
SQL> create pfile from spfile; File created.
③生成备库：
[oracle@host08 admin]$ rman target / auxiliary sys/oracle@SBDB Recovery Manager: Release 11.2.0.4.0 - Production on Mon Oct 24 11:32:11 2016 Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved. connected to target database: ORA11GR2 (DBID=242597878)
connected to auxiliary database: ORA11GR2 (not mounted) RMAN> duplicate target database for standby from active database; Starting Duplicate Db at 24-OCT-16
using target database control file instead of recovery catalog
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=10 device type=DISK contents of Memory Script:
{
backup as copy reuse
targetfile '/u01/app/oracle/product/11.2.0/dbhome_1/dbs/orapwORA11GR2' auxiliary format
'/u01/app/oracle/product/11.2.0/dbhome_1/dbs/orapwSBDB' ;
}
executing Memory Script Starting backup at 24-OCT-16
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=22 device type=DISK
Finished backup at 24-OCT-16 contents 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 Script Starting backup at 24-OCT-16
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile copy
copying standby control file
output file name=/u01/app/oracle/product/11.2.0/dbhome_1/dbs/snapcf_ORA11GR2.f tag=TAG20161024T113255 RECID=2 STAMP=926076778
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:07
Finished backup at 24-OCT-16 Starting restore at 24-OCT-16
using channel ORA_AUX_DISK_1 channel ORA_AUX_DISK_1: copied control file copy
Finished restore at 24-OCT-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
"/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 Script executing 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 24-OCT-16
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile copy
input datafile file number=00001 name=/u01/app/oracle/oradata/ORA11GR2/system01.dbf
output file name=/u01/app/oracle/oradata/SBDB/system01.dbf tag=TAG20161024T113314
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:01:45
channel ORA_DISK_1: starting datafile copy
input datafile file number=00002 name=/u01/app/oracle/oradata/ORA11GR2/sysaux01.dbf
output file name=/u01/app/oracle/oradata/SBDB/sysaux01.dbf tag=TAG20161024T113314
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:01:35
channel ORA_DISK_1: starting datafile copy
input datafile file number=00005 name=/u01/app/oracle/oradata/ORA11GR2/example01.dbf
output file name=/u01/app/oracle/oradata/SBDB/example01.dbf tag=TAG20161024T113314
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:01:05
channel ORA_DISK_1: starting datafile copy
input datafile file number=00003 name=/u01/app/oracle/oradata/ORA11GR2/undotbs01.dbf
output file name=/u01/app/oracle/oradata/SBDB/undotbs01.dbf tag=TAG20161024T113314
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:15
channel ORA_DISK_1: starting datafile copy
input datafile file number=00004 name=/u01/app/oracle/oradata/ORA11GR2/users01.dbf
output file name=/u01/app/oracle/oradata/SBDB/users01.dbf tag=TAG20161024T113314
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:04
Finished backup at 24-OCT-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=2 STAMP=926077082 file name=/u01/app/oracle/oradata/SBDB/system01.dbf
datafile 2 switched to datafile copy
input datafile copy RECID=3 STAMP=926077082 file name=/u01/app/oracle/oradata/SBDB/sysaux01.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=4 STAMP=926077082 file name=/u01/app/oracle/oradata/SBDB/undotbs01.dbf
datafile 4 switched to datafile copy
input datafile copy RECID=5 STAMP=926077082 file name=/u01/app/oracle/oradata/SBDB/users01.dbf
datafile 5 switched to datafile copy
input datafile copy RECID=6 STAMP=926077082 file name=/u01/app/oracle/oradata/SBDB/example01.dbf
Finished Duplicate Db at 24-OCT-16
（11）备库起到open： SQL> startup
ORACLE instance started. Total System Global Area 830930944 bytes
Fixed Size 2257800 bytes
Variable Size 536874104 bytes
Database Buffers 285212672 bytes
Redo Buffers 6586368 bytes
Database mounted.
Database opened.
应用日志：
SQL> recover managed standby database using current logfile disconnect from session;
Media recovery complete. SQL> recover managed standby database cancel;
Media recovery complete. （11）查看主库切换状态：
SQL> select switchover_status from v$database; SWITCHOVER_STATUS
--------------------
TO STANDBY
（12）查看备库切换状态：
SQL> select switchover_status from v$database; SWITCHOVER_STATUS
--------------------
NOT ALLOWED