本文章参考于：http://www.cnblogs.com/qipilang/archive/2010/03/18/1689022.html 和ORACLE10g 官方文档
本次实验演示的是创建一个physical standby database，实验环境如下：
虚拟机版本：vmware9.0 操作系统版本：CentOS release 6.4
主数据库版本：Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bi
备用数据库配置：只安装了数据库软件
1.确保primary database处于归档模式
修改数据库归档路径，并设置归档模式，如下：
SQL> alter system set log_archive_dest_1='location=/u01/app/my_arch';
System altered.
SQL> shutdown immediate
SQL> startup mount;
SQL> alter database archivelog;
SQL> alter database open;
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /u01/app/my_arch
Oldest online log sequence 1
Next log sequence to archive 2
Current log sequence 2
2.设置Primary Database为Forced Logging模式：
SQL> ALTER DATABASE FORCE LOGGING;
Database altered.
SQL> select FORCE_LOGGING from v$database;
FOR
---
YES
批注：数据库指定为FORCE LOGGING后，数据库将会记录除临时表空间或临时回滚段外的所有操作，而忽略类似NOLOGGING之类的指定参数。如果在执行FORCE LOGGING时有NOLOGGING之类的语句在执行，那么FORCE LOGGING会等待，直到这类语句全部执行。
FORCE LOGGING是作为固定参数保存在控制文件中，因为其不受重启之类的操作的影响(只执行一次即可)，如果想取消，可以通过ALTER DATABASE NO FORCE LOGGING语句关闭强制记录。
3.创建密钥文件
同一个Data Guard配置中所有数据库必须都拥有独立的密钥文件，并且必须保存在同一个Data Guard配置中，所有数据库服务器的SYS用户拥有相同的密码，以保证REDO数据的顺利传输，因为REDO传输服务是通过认证的网络会话来传输REDO数据，而会话使用包含在密钥文件中的SYS用户密码来认证。
[oracle@dayong ~]$ orapwd
Usage: orapwd file= password= entries= force=
where
file - name of password file (mand),
password - password for SYS (mand),
entries - maximum number of distinct DBA and force - whether to overwrite existing file (opt),
OPERs (opt),
There are no spaces around the equal-to (=) character.
[oracle@dayong ~]$ orapwd file=/u01/app/oracle/dbs/orapworcl password=oracle
4.创建standby logfile group
查看主库日志组情况，如下：
SQL> select a.GROUP#,a.MEMBERS,b.MEMBER,a.BYTES/1024/1024 MB
2 from v$log a,v$logfile b where a.group#=b.group# order by 1;
GROUP# MEMBERS MEMBER MB
---------- ---------- -------------------------------------------------- ----------
1 1 /u01/app/oradata/myorcl/redo01.log 50
2 1 /u01/app/oradata/myorcl/redo02.log 50
3 1 /u01/app/oradata/myorcl/redo03.log 50
添加新的Standby Redologs组，同时为该组指定一个成员，如下：
[oracle@dayong oradata]$ mkdir orcl --首先创建日志文件存放目录
[oracle@dayong oradata]$ pwd
/u01/app/oradata
SQL> alter database add standby logfile group 4 ('/u01/app/oradata/orcl/standbyrd01.log') size 50m;
SQL> alter database add standby logfile group 5 ('/u01/app/oradata/orcl/standbyrd02.log') size 50m;
SQL> alter database add standby logfile group 6 ('/u01/app/oradata/orcl/standbyrd03.log') size 50m;
通过动态性能视图v$logfile查看当前数据库创建的Standby Redologs，如下：
SQL> select group#,status,type,member from v$logfile;
GROUP# STATUS TYPE MEMBER
---------- ------- ------- --------------------------------------------------
3 ONLINE /u01/app/oradata/myorcl/redo03.log
2 ONLINE /u01/app/oradata/myorcl/redo02.log
1 ONLINE /u01/app/oradata/myorcl/redo01.log
4 STANDBY /u01/app/oradata/orcl/standbyrd01.log
5 STANDBY /u01/app/oradata/orcl/standbyrd02.log
6 STANDBY /u01/app/oradata/orcl/standbyrd03.log
6 rows selected.
5.配置primary database的初始化参数
| Database | DB_UNIQUE_NAME | Oracle Net Service Name |
| Primary | myorcl | myorcl |
| Physical Standby | orcl | orcl |
SQL> create pfile='/u01/app/oracle/dbs/orcl_pfile.ora' from spfile; --通过spfile创建pfile
File created.
修改客户端初始化参数文件vi /u01/app/oracle/dbs/orcl_pfile.ora
添加的内容如下：
27 *.DB_UNIQUE_NAME=myorcl
28 *.LOG_ARCHIVE_CONFIG='DG_CONFIG=(myorcl,orcl)'
29 *.LOG_ARCHIVE_DEST_1='LOCATION=/u01/app/my_arch/ VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=myorcl'
30 *.LOG_ARCHIVE_DEST_2='SERVICE=orcl LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES ,PRIMARY_ROLE)DB_UNIQUE_NAME=orcl'
31 *.LOG_ARCHIVE_DEST_STATE_1=ENABLE
32 *.LOG_ARCHIVE_DEST_STATE_2=ENABLE
33 *.FAL_SERVER=orcl
34 *.FAL_CLIENT=myorcl
35 *.DB_FILE_NAME_CONVERT='orcl','myorcl'
36 *.STANDBY_FILE_MANAGEMENT=AUTO
1)以修改过的参数文件启动数据库并生成服务器参数文件，再让数据库以服务器参数文件的方式启动数据库 ：
SQL> shutdown immediate
SQL> startup pfile='/u01/app/oracle/dbs/orcl_pfile.ora' nomount;
ORACLE instance started.
Total System Global Area 281018368 bytes
Fixed Size 2020160 bytes
Variable Size 117443776 bytes
Database Buffers 155189248 bytes
Redo Buffers 6365184 bytes
2)通过pfile创建备库的spfile
SQL> create spfile from pfile='/u01/app/oracle/dbs/orcl_pfile.ora';
File created.
3)重启数据库
SQL> shutdown immediate
SQL> startup
ORACLE instance started.
Total System Global Area 281018368 bytes
Fixed Size 2020160 bytes
Variable Size 117443776 bytes
Database Buffers 155189248 bytes
Redo Buffers 6365184 bytes
Database mounted.
Database opened.
6.在主库中创建备库的控制文件
SQL> alter database create standby controlfile as '/u01/app/oradata/orcl/standbycontrol01.ctl';
SQL> alter database create standby controlfile as '/u01/app/oradata/orcl/standbycontrol02.ctl';
SQL> alter database create standby controlfile as '/u01/app/oradata/orcl/standbycontrol03.ctl';
7.复制相关文件到备库
需要复制的文件包括：所有的数据文件、重做日志文件、密钥文件，以及刚刚创建的Standby Database的控制文件和客户端初始化参数文件(用来创建Standby Database存档服务器端参数文件)。
1)拷贝密钥文件
[oracle@dayong dbs]$ scp orapworcl oracle@10.41.6.119：/u01/app/oracle/dbs/
The authenticity of host '10.41.6.119 (10.41.6.119)' can't be established.
RSA key fingerprint is 83:3e:27:18:f1:ce:eb:81:dd:7b:0f:8d:5d:4f:48:3d.
Are you sure you want to continue connecting (yes/no)? yes
Warning: Permanently added '10.41.6.119' (RSA) to the list of known hosts.
oracle@10.41.6.119's password:
orapworcl 100% 1536 1.5KB/s 00:00
2)拷贝控制文件
[oracle@dayong oradata]$ cd orcl/
[oracle@dayong orcl]$ ls
standbycontrol01.ctl standbycontrol02.ctl standbycontrol03.ctl standbyrd01.log standbyrd02.log standbyrd03.log
[oracle@dayong orcl]$ scp standbycontrol* oracle@10.41.6.119:/u01/app/oradata/orcl/
oracle@10.41.6.119's password:
standbycontrol01.ctl 100% 6896KB 6.7MB/s 00:00
standbycontrol02.ctl 100% 6896KB 6.7MB/s 00:00
standbycontrol03.ctl 100% 6896KB 6.7MB/s 00:00
3)拷贝参数文件
[oracle@dayong dbs]$ scp orcl_pfile.ora oracle@10.41.6.119:/u01/app/oracle/dbs/
oracle@10.41.6.119's password:
orcl_pfile.ora 100% 1014 1.0KB/s 00:00
4)拷贝数据文件
首先查询当前数据库的表空间和数据文件
SQL> select FILE_NAME,TABLESPACE_NAME from dba_data_files;
FILE_NAME TABLESPACE_NAME
--------------------------------------------- ------------------------------
/u01/app/oradata/myorcl/users01.dbf USERS
/u01/app/oradata/myorcl/sysaux01.dbf SYSAUX
/u01/app/oradata/myorcl/undotbs01.dbf UNDOTBS1
/u01/app/oradata/myorcl/system01.dbf SYSTEM
/u01/app/oradata/myorcl/example01.dbf EXAMPLE
SQL> alter tablespace USERS begin backup; --告知数据库对表空间进行备份
SQL> alter tablespace SYSAUX begin backup;
SQL> alter tablespace UNDOTBS1 begin backup;
SQL> alter tablespace SYSTEM begin backup;
SQL> alter tablespace EXAMPLE begin backup;
拷贝表空间到备库，如下：
[oracle@dayong myorcl]$ ls
control01.ctl control03.ctl redo01.log redo03.log system01.dbf undotbs01.dbf
control02.ctl example01.dbf redo02.log sysaux01.dbf temp01.dbf users01.dbf
[oracle@dayong myorcl]$ scp *.dbf oracle@10.41.6.119:/u01/app/oradata/myorcl/
oracle@10.41.6.119's password:
example01.dbf 100% 100MB 9.1MB/s 00:11
sysaux01.dbf 100% 240MB 8.6MB/s 00:28
system01.dbf 100% 480MB 8.3MB/s 00:58
temp01.dbf 100% 20MB 10.0MB/s 00:02
undotbs01.dbf 100% 30MB 7.5MB/s 00:04
users01.dbf 100% 5128KB 1.7MB/s 00:03
SQL> alter tablespace USERS end backup; --告知数据库结束表空间备份
SQL> alter tablespace SYSAUX end backup;
SQL> alter tablespace UNDOTBS1 end backup;
SQL> alter tablespace SYSTEM end backup;
SQL> alter tablespace EXAMPLE end backup;
8.配置监听和网络服务名
配置监听和TNS，以方便主、备节点互通有无，并且必须是静态监听。
因为如果配置的是动态监听，只要有实例没有启动到open状态，那就无法通过网络将服务注册到数据库。
1)对primary database的监听配置如下：
下面显示的是配置静态监听以及TNS的内容：
[oracle@dayong admin]$ cat listener.ora
# listener.ora Network Configuration File: /u01/app/oracle/network/admin/listener.ora
# Generated by Oracle configuration tools.
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME = /u01/app/oracle)
(PROGRAM = extproc)
)
(SID_DESC =
(GLOBAL_DBNAME=myorcl)
(ORACLE_HOME = /u01/app/oracle)
(SID_NAME =myorcl)
)
)
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = dayong)(PORT = 1521))
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))
)
)
重启监听生效，如下：
[oracle@dayong admin]$ lsnrctl stop
[oracle@dayong admin]$ lsnrctl start
2)对primary database的net service name进行配置，如下：
[oracle@dayong admin]$ cat tnsnames.ora
# tnsnames.ora Network Configuration File: /u01/app/oracle/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.
MYORCL =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.41.6.118)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = myorcl)
)
)
ORCL =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.41.6.119)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = orcl)
)
)
EXTPROC_CONNECTION_DATA =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))
)
(CONNECT_DATA =
(SID = PLSExtProc)
(PRESENTATION = RO)
)
)
9.物理standby database的配置
1)配置监听，如下：
[oracle@xiaoru admin]$ cat listener.ora
# listener.ora Network Configuration File: /u01/app/oracle/network/admin/listener.ora
# Generated by Oracle configuration tools.
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME = /u01/app/oracle)
(PROGRAM = extproc)
)
(SID_DESC =
(GLOBAL_DBNAME= orcl)
(ORACLE_HOME= /u01/app/oracle)
(SID_NAME= orcl)
)
)
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = xiaoru)(PORT = 1521))
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))
)
)
重启监听：
[oracle@xiaoru admin]$ lsnrctl stop
[oracle@xiaoru admin]$ lsnrctl start
2)配置Net Service Name
[oracle@dayong admin]$ cat tnsnames.ora
# tnsnames.ora Network Configuration File: /u01/app/oracle/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.
MYORCL =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.41.6.118)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = myorcl)
)
)
ORCL =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.41.6.119)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = orcl)
)
)
EXTPROC_CONNECTION_DATA =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))
)
(CONNECT_DATA =
(SID = PLSExtProc)
(PRESENTATION = RO)
)
)
3)在两台服务器之间使用tnsping命令互相测试网络服务服务器是否可用，如下：
[oracle@dayong ~]$ tnsping orcl --从主库测试能否与备库的监听相通 (注意需要关闭iptables)
TNS Ping Utility for Linux: Version 10.2.0.1.0 - Production on 26-MAR-2014 23:49:38
Copyright (c) 1997, 2005, Oracle. All rights reserved.
Used parameter files:
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 10.41.6.119)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = orcl)))
OK (10 msec) --显示可以监听能够tnsping通
[oracle@xiaoru admin]$ tnsping myorcl --从备库测试能否与主库的监听相通
TNS Ping Utility for Linux: Version 10.2.0.1.0 - Production on 27-MAR-2014 16:31:13
Copyright (c) 1997, 2005, Oracle. All rights reserved.
Used parameter files:
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 10.41.6.118)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = myorcl)))
OK (0 msec) --显示可以监听能够tnsping通
4)创建日志文件相关目录。包括Oracle内部进程的输出信息和用户进程等存储记录：
[oracle@xiaoru admin]$ mkdir orcl
[oracle@xiaoru admin]$ cd orcl/
[oracle@xiaoru orcl]$ mkdir adump bdump cdump dpdump udump
[oracle@xiaoru orcl]$ ls
adump bdump cdump dpdump udump
[oracle@xiaoru orcl]$ pwd
/u01/app/admin/orcl
5)修改客户端的初始化参数文件
找到刚刚从Primary Database复制过来的客户端初始化参数文件/u01/app/oracle/dbs/orcl_pfile.ora 修改如下：
[oracle@xiaoru pfile]$ cat orcl_pfile.ora
myorcl.__db_cache_size=155189248
myorcl.__java_pool_size=4194304
myorcl.__large_pool_size=4194304
myorcl.__shared_pool_size=109051904
myorcl.__streams_pool_size=0
*.audit_file_dest='/u01/app/admin/orcl/adump'
*.background_dump_dest='/u01/app/admin/orcl/bdump'
*.compatible='10.2.0.1.0'
*.control_files='/u01/app/oradata/orcl/standbycontrol01.ctl','/u01/app/oradata/orcl/standbycontrol02.ctl','/u01/app/oradata/orcl/standbycontrol03.ctl'
*.core_dump_dest='/u01/app/admin/orcl/cdump'
*.db_block_size=8192
*.db_domain=''
*.db_file_multiblock_read_count=16
*.db_name='myorcl' ---此处要注意了，一定要是主数据库的DB_NAME，要不然启动数据库到mount状态时会报错
*.db_recovery_file_dest='/u01/app/flash_recovery_area'
*.db_recovery_file_dest_size=2147483648
*.dispatchers='(PROTOCOL=TCP) (SERVICE=myorclXDB)'
*.job_queue_processes=10
*.log_archive_dest_1='location=/u01/app/my_arch'
*.open_cursors=300
*.pga_aggregate_target=93323264
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.sga_target=281018368
*.undo_management='AUTO'
*.undo_tablespace='UNDOTBS1'
*.user_dump_dest='/u01/app/admin/orcl/udump'
*.DB_UNIQUE_NAME=orcl
*.LOG_ARCHIVE_CONFIG='DG_CONFIG=(orcl,myorcl)'
*.LOG_ARCHIVE_DEST_1='LOCATION=/u01/app/my_arch/ VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=orcl'
*.LOG_ARCHIVE_DEST_2='SERVICE=myorcl LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=myorcl'
*.LOG_ARCHIVE_DEST_STATE_1=ENABLE
*.LOG_ARCHIVE_DEST_STATE_2=ENABLE
*.FAL_SERVER=myorcl
*.FAL_CLIENT=orcl
6)修改并保存后，以sysdba身份连接到Standby数据库，通过该pfile创建spfile：
SQL> create spfile from pfile='/u01/app/oracle/dbs/orcl_pfile.ora';
File created.
7)启动物理standby database到mount状态：
SQL> startup mount
ORACLE instance started.
Total System Global Area 281018368 bytes
Fixed Size 2020160 bytes
Variable Size 117443776 bytes
Database Buffers 155189248 bytes
Redo Buffers 6365184 bytes
Database mounted.
8)启动standby database到redo apply
即使Standby Database当前处于OPEN状态，也不需要重启数据库，直接执行REDO应用语句即可，ORACLE会自动将数据库切换至MOUNT状态，如下：
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;
Database altered.
SQL>ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL; --若要暂停应用，如是操作
批注：此时只是暂停REDO应用，并不是停止物理Standby Database，Standby 仍会保持接收，只不过不会再应用接收到的归档，直到再次启动REDO应用为止。
10 验证
1)在主库手动切换日志
SQL> ALTER SYSTEM SWITCH LOGFILE;
2)备库查询视图 V$ARCHIVED_LOG
SQL> SELECT SEQUENCE#, FIRST_TIME, NEXT_TIME FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#;
SEQUENCE# FIRST_TIM NEXT_TIME
---------- --------- ---------
3 27-MAR-14 27-MAR-14
3)在主库再次切换日志
SQL> ALTER SYSTEM SWITCH LOGFILE;
System altered.
4)备库查询视图
SQL> SELECT SEQUENCE#, FIRST_TIME, NEXT_TIME FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#;
SEQUENCE# FIRST_TIM NEXT_TIME
---------- --------- ---------
3 27-MAR-14 27-MAR-14
4 27-MAR-14 27-MAR-14
11.简单测试
1)主库操作如下：
SQL> conn scott/tiger;
SQL> insert into dept values (50,'huyong','xiaoru');
SQL> commit;
SQL> select * from dept;
DEPTNO DNAME LOC
---------- -------------- -------------
50 huyong xiaoru
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
SQL> conn / as sysdba
SQL> alter system switch logfile;
2)将 Standby db 启动到read only 模式下，观察刚才插入的记录是否已经被应用
SQL> recover managed standby database cancel;
Media recovery complete.
SQL> alter database open;
SQL> select instance_name,status from v$instance;
INSTANCE_NAME STATUS
---------------- ------------
orcl OPEN
SQL> select open_mode from v$database;
OPEN_MODE
----------
READ ONLY
SQL> select * from scott.dept;
DEPTNO DNAME LOC
---------- -------------- -------------
50 huyong xiaoru
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
备库上查看日志：
SQL> SELECT NAME, CREATOR, SEQUENCE#, APPLIED, COMPLETION_TIME FROM V$ARCHIVED_LOG;
NAME CREATOR SEQUENCE# APP COMPLETIO
--------------------------------------------- ------- ---------- --- ---------
/u01/app/my_arch/1_3_843246228.dbf ARCH 3 YES 26-MAR-14
/u01/app/my_arch/1_4_843246228.dbf LGWR 4 YES 26-MAR-14
/u01/app/my_arch/1_5_843246228.dbf LGWR 5 YES 26-MAR-14
来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/29634949/viewspace-1173299/，如需转载，请注明出处，否则将追究法律责任。
转载于:http://blog.itpub.net/29634949/viewspace-1173299/
1万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
