建立数据库：环境要求
OS 版本一样；
Oracle 版本必须一样，同时安装目录结构建议做成一样，以免发生不必要的错误；
开始安装oracle时，在primary 和 standby 上都装上instance, 要求安装的 SID都必须一样。
shutdown冷备.
将primary和standby都置为archive log 状态：
Startup mount
alter database archivelog;
alter system archive log start
分别修改primary和standby 库的配置文件。
两数据库的配置：
配置priamry listener.ora
# LISTENER.ORA Network Configuration File: d:\oracle\ora92\network\admin\listener.ora
# Generated by Oracle configuration tools.
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))
)
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.72.219)(PORT = 1521))
)
)
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME = D:\oracle\ora92)
(PROGRAM = extproc)
)
(SID_DESC =
(GLOBAL_DBNAME = epg)
(ORACLE_HOME = D:\oracle\ora92)
(SID_NAME = gdepg)
)
)
配置从standby listner.ora 文件：
# LISTENER.ORA Network Configuration File: d:\oracle\ora92\network\admin\listener.ora
# Generated by Oracle configuration tools.
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))
)
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.72.218)(PORT = 1521))
)
)
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME = D:\oracle\ora92)
(PROGRAM = extproc)
)
(SID_DESC =
(GLOBAL_DBNAME = epg)
(ORACLE_HOME = D:\oracle\ora92)
(SID_NAME = gdepg)
)
)
配置主primary 的tnsnames.ora
（必须保证这里面只有两项，primary和standby的两个。同时建议用 “Net Manager” 来操作自动产生配置文件。否则会发生莫名其妙的问题。）
# TNSNAMES.ORA Network Configuration File: d:\oracle\ora92\NETWORK\ADMIN\tnsnames.ora
# Generated by Oracle configuration tools.
STANDBY =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.72.218)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = gdepg)
)
)
PRIMARY =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.72.219)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = gdepg)
)
)
配置从standby listner.ora 文件：
# LISTENER.ORA Network Configuration File: d:\oracle\ora92\network\admin\listener.ora
# Generated by Oracle configuration tools.
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))
)
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.72.218)(PORT = 1521))
)
)
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME = D:\oracle\ora92)
(PROGRAM = extproc)
)
(SID_DESC =
(GLOBAL_DBNAME = epg)
(ORACLE_HOME = D:\oracle\ora92)
(SID_NAME = gdepg)
)
)
配置主primary 的tnsnames.ora
（必须保证这里面只有两项，primary和standby的两个。同时建议用 “Net Manager” 来操作自动产生配置文件。否则会发生莫名其妙的问题。）
# TNSNAMES.ORA Network Configuration File: d:\oracle\ora92\NETWORK\ADMIN\tnsnames.ora
# Generated by Oracle configuration tools.
STANDBY =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.72.218)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = gdepg)
)
)
PRIMARY =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.72.219)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = gdepg)
)
)
配置从 standby 的 tnsnames.ora 文件
# TNSNAMES.ORA Network Configuration File: d:\oracle\ora92\NETWORK\ADMIN\tnsnames.ora
# Generated by Oracle configuration tools.
PRIMARY =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.72.219)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = gdepg)
)
)
STANDBY =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.72.218)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = gdepg)
)
)
完成后执行
C:\>tnsping standby
C:\>tnsping primary
来检测配置是否有问题。
lsnrctl start;
Lsnrctl status
配置primary 的pfile 即 init.ora.2272006143433
###########################################
# Cache and I/O
###########################################
db_block_size=8192
db_cache_size=335544320
db_file_multiblock_read_count=16
log_archive_start = true
log_archive_dest_1 = "location=d:\oracle\Arch mandatory REOPEN=2"
log_archive_dest_state_1 = enable
log_archive_dest_2 = "service=standby mandatory reopen=15"
log_archive_dest_state_2 = enable
log_archive_min_succeed_dest=1
log_archive_format=ARC%S.%T
配置 standby 的 pfile 文件 init.ora
与primary有所不同：最好的配置方式是从primary上 copy过来，然后做相对修改：
control_files=("d:\oracle\oradata\gdepg\standby01.ctl")
###########################################
# Cache and I/O
###########################################
db_block_size=8192
db_cache_size=335544320
db_file_multiblock_read_count=16
log_archive_dest_state_1 = enable
log_archive_dest_state_2 = enable
log_archive_start = true
log_archive_dest_1 = "location=d:\oracle\Arch mandatory REOPEN=2"
log_archive_dest_state_1 = enable
log_archive_dest_2 = "service=standby mandatory reopen=15"
log_archive_dest_state_2 = enable
standby_archive_dest = "D:\oracle\Arch"
db_file_name_convert = ("d:\oracle\oradata\gdepg","d:\oracle\oradata\gdepg")
log_file_name_convert = ("d:\oracle\oradata\gdepg","d:\oracle\oradata\gdepg")
log_archive_trace=6
log_archive_min_succeed_dest=1
standby_archive_dest=d:\oracle\arch
log_archive_format = ARC%S.%T
同时修改d:\oracle\oradata\ora92\init+实例名 文件
添加：
*.standby_file_management=AUTO
*.remote_archive_enable=TRUE
*.standby_archive_dest='d:\oracle\Arch'
*.fal_server='PRIMARY'
*.fal_client='STANDBY'
在做好配置文件后，先对pfile和spfile做一次同步，然后再生成standby控制文件。
create spfile='d:\oracle\ora92\database\spfilegdepg1.ora' from pfile='d:\or acle\admin\gdepg\pfile\init.ora.2272006143433';
然后改名：将SPFILEGDEPG1.ORA 更名为SFILEGDEPG.ORA
alter database create standby controlfile as 'd:\oracle\oradata\gdepg\STANDBY01.CTL';
然后停掉primary 和standby数据库，
同时建立归档目录
D:\oracle\Arch
将primary上的datafile下所有数据文件，否覆盖掉standby 库中的datafile 。
还有下面文件，也覆盖到从库相应的位置
D:\oracle\oradata\ora92\initgdepg.ora
D:\oracle\oradata\ora92\PWDgdepg.ora
D:\oracle\oradata\ora92\SPFILEGDEPG.ORA
同时将primary上的 archive log 日志文件都 copy到 standby的对应目录。
如果是windows环境，记得修改standby注册表里ORA_实例名_AUTOSTART ,置为 FALSE否则standby机器重启后就自己启动到open状态，使scn不一样，standby 无法正常启动，这种情况就只能重装standby了。
完成后，就可以启动standby db了。
启动顺序：先standby ,后primary;
关闭顺序：先primary 后standby;
步骤：
startup nomount pfile=d:\oracle\admin\gdepg\pfile\init.ora
alter database mount standby database;
recover managed standby database ;
---- ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;
--- recover automatic standby database;
切换到read only 状态
在开另外一个连接进行如下操作：
recover managed standby database cancel;
alter database open read only;
这时候可以进行对表exp/imp或别的操作
如果要回到managed状态，则必须另开一个窗口，然后再运行：
recover managed standby database ; 回到 managed状态。
在默认情况下，Data guard就是最大性能模式，所以不需要采用其它额外的配置
查看日志序列是否有缺失的SQL语句：
SQL>SELECT high.thread#, "LowGap#", "HighGap#"
FROM
(
SELECT thread#, MIN(sequence#)-1 "HighGap#"
FROM
( SELECT a.thread#, a.sequence#
FROM
v$archived_log a,
( SELECT thread#, MAX(next_change#) gap1
FROM v$log_history
GROUP BY thread#
) b
WHERE a.thread# = b.thread#
AND a.next_change# > b.gap1
)
GROUP BY thread#
) high,
(
SELECT thread#, MIN(sequence#) "LowGap#"
FROM
( SELECT thread#, sequence#
FROM v$log_history, v$datafile
WHERE checkpoint_change# <= next_change#
AND checkpoint_change# >= first_change#
)
GROUP BY thread#
) low
WHERE low.thread# = high.thread# ;
如果备库有日志序列缺失，则同一个thread#的LowGap# 与 HighGap# 不等。
SQL> ALTER DATABASE RECOVER MANAGED standby DATABASE DISCONNECT FROM SESSION;
最后测试在主库上修改记录，建表。看从库记录是否相应改变：
切换当前联机日志
SQL> alter system switch logfile;
察看从库后台日志、alter文件是否改变。
阅读(1124) | 评论(0) | 转发(0) |
