ora11g dataguard新特性尝试1
了解下oracle11g在physical standby模式下可以在standby数据库打开状态下进行数据恢复，考虑这种方式可以实现使用standby数据库进行数据查询。自己做个测试，了解下
介绍
参考文档
1)
B28294
2)
Oracle MetaLink
note 413484.1
针对企业应用，dataguard提供physical standby database及logical standby database的技术手段来保障数据库高可用性，提供数据保护及数据恢复的解决方案。
判断DataGuard是否安装
col parameter for a40
col value for a40
set lines 120
set pages 0
select * from v$option
where parameter = 'Oracle Data Guard';
PARAMETER VALUE
Oracle Data Guard TRUE
网络配置
SHAPE
* MERGEFORMAT 192.168.111.2(primary) （） 192.168.111.3(standby) （）
PRIMARY配置
[oracle@server1 admin]$ cat listener.ora
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL =
TCP)(HOST = server1)(PORT = 1521))
(ADDRESS = (PROTOCOL =
IPC)(KEY = EXTPROC1521))
)
)
[oracle@server1 admin]$ cat tnsnames.ora
PRIMARY =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL =
TCP)(HOST = 192.168.111.2)(PORT = 1521))
)
(CONNECT_DATA =
(SID = ora11g)
)
)
STANDBY =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL =
TCP)(HOST = 192.168.111.3)(PORT = 1521))
)
(CONNECT_DATA =
(SID = ora11g)
)
)
STANDBY配置
[oracle@server2 admin]$ cat listener.ora
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST =
server2)(PORT = 1521))
(ADDRESS = (PROTOCOL =
IPC)(KEY = EXTPROC1521))
)
)
[oracle@server2 admin]$ cat tnsnames.ora
PRIMARY =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL =
TCP)(HOST = 192.168.111.2)(PORT = 1521))
)
(CONNECT_DATA =
(SID = ora11g)
)
)
STANDBY =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL =
TCP)(HOST = 192.168.111.3)(PORT = 1521))
)
(CONNECT_DATA =
(SID = ora11g)
)
)
生产库前期准备
设置强制归档
[oracle@server1 admin]$
sqlplus "/ as sysdba"
SQL> select FORCE_LOGGING from v$database;
NO
SQL> alter database
force logging;
SQL> select
FORCE_LOGGING from v$database;
YES
创建口令文件
由于DATAGUARD在使用过程中要使用口令文件，确认生产库上的口令文件存在。
设置生产库参数
查看PRIMARY库上关键参数
*.control_files='/usr/oradata/ora11g/control01.ctl','/usr/oradata/ora11g/control02.ctl','/usr/oradata/ora11g/control03.ctl'
DB_NAME=ora11g
DB_UNIQUE_NAME=chicago
LOG_ARCHIVE_CONFIG='DG_CONFIG=(chicago,boston)'
LOG_ARCHIVE_DEST_1='LOCATION=/usr/oradata/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=ENABLE
LOG_ARCHIVE_DEST_STATE_2=ENABLE
REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE
LOG_ARCHIVE_FORMAT=%t_%s_%r.arc
LOG_ARCHIVE_MAX_PROCESSES=5
FAL_SERVER=boston
FAL_CLIENT=Chicago
STANDBY_FILE_MANAGEMENT=AUTO
修改数据库运行在归档模式下
修改下列参数，确定数据库归档模式
SQL> shutdown immediate;
SQL> startup mount pfile='/tmp/initchicago.ora';
SQL> alter database archivelog;
SQL> alter database open;
SQL> create spfile from pfile='/tmp/initchicago.ora';
配置Standby Redo Log
select * from v$Log
GROUP#
THREAD# SEQUENCE# BYTES
MEMBERS ARC STATUS
FIRST_CHANGE# FIRST_TIME
1 1 23
52428800 1 NO INACTIVE
339587 11-APR-07
2 1 24
52428800 1 NO CURRENT 387954 12-APR-07
3 2 3
52428800 1 NO CURRENT 387953 12-APR-07
4 2 2
52428800 1 NO INACTIVE 319579 18-OCT-06
确定生产库上在线日志文件的大小，创建Standby Redo Log按下列规则创建(maximum number of logfiles for each
thread + 1) * maximum number of threads
ALTER DATABASE ADD STANDBY
LOGFILE('/usr/oradata/ora11g/standby01.dbf') size 50M;
ALTER DATABASE ADD STANDBY
LOGFILE('/usr/oradata/ora11g/standby02.dbf') size 50M;
ALTER DATABASE ADD STANDBY
LOGFILE('/usr/oradata/ora11g/standby03.dbf') size 50M;
ALTER DATABASE ADD STANDBY
LOGFILE('/usr/oradata/ora11g/standby04.dbf') size 50M;
创建Standby数据库
备份生产数据库
这里简化处理，直接将数据文件冷copy到另外一个节点
[oracle@server1 chicago]$ nohup scp -rp
/usr/oradata/ora11g/* server2:/usr/oradata/ora11g/ &
创建备份库需要的控制文件
启动主库在MOUNT状态，生成standby库的控制文件
SQL> startup mount
SQL> alter database
create standby controlfile as '/tmp/stbycf.ctl';
scp -rp /tmp/stbycf.ctl
server2:/usr/oradata/ora11g/control01.ctl
scp -rp /tmp/stbycf.ctl
server2:/usr/oradata/ora11g/control02.ctl
scp -rp /tmp/stbycf.ctl
server2:/usr/oradata/ora11g/control03.ctl
scp -rp
/tmp/initboston.ora server2:/tmp/initboston.ora
[@more@]
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/48010/viewspace-1015566/ ，如需转载，请注明出处，否则将追究法律责任。
