在 aix 上配置 Oracle 10g Data Guard

在 aix 上配置 Oracle 10g Data Guard

一、 环境
primary：
IP：192.168.0.120
DB：Oracle Database 10g Enterprise Edition Release 10.2.0.3.0
OS：aix 5306
standby：
IP：192.168.0.101
DB：Oracle Database 10g Enterprise Edition Release 10.2.0.3.0
OS：aix 5306
Primary为正在使用的生产数据库，standby安装oracle软件，但不建立数据库。
二、 建立物理备用数据库
1． 准备主库的oracle环境：
编辑oracle用户的.profile文件，oracle相关环境变量如下：
ORACLE_BASE=/oracle/app; export ORACLE_BASE
ORACLE_SID=yxjdb0; export ORACLE_SID
ORACLE_HOME=$ORACLE_BASE/10g/; export ORACLE_HOME
export PATH=$ORACLE_HOME/bin:$PATH:
export LD_LIBRARY_PATH=$ORACLE_HOME/lib:/usr/lib
2． 修改主库为归档模式
建立归档目录：
mkdir -p /oraarch
修改归档模式：
archive log list;
create pfile from spfile;
编辑$ORACLE_HOME/dbs/initYXJDB0.ora
添加下面一行
log_archive_dest_1='location=/oraarch '

sqlplus /nolog
conn sys as sysdba
shutdown immediate;
create spfile from pfile;
startup nomount;
alter database mount;
alter database archivelog;
alter database open;
3. 对主数据库做一次完整热备份，获得备用数据库数据
RMAN>connect target
RMAN> backup database format='/home/oracle/%U_%s.bak';
RMAN> sql ＇Alter System Archive Log Current＇;
RMAN> Backup filesperset 10 ArchiveLog all format='/home/oracle/%U_%s.bak';
cd /home/oracle
scp *.bak 192.168.0.101:/home/oracle/
4. 在standby服务器准备环境与primary相同
编辑oracle用户的$HOME/.profile文件，oracle相关环境变量如下：
ORACLE_BASE=/oracle/app; export ORACLE_BASE
ORACLE_SID=yxjdb0; export ORACLE_SID
ORACLE_HOME=$ORACLE_BASE/10g; export ORACLE_HOME
export PATH=$ORACLE_HOME/bin:$PATH:
export LD_LIBRARY_PATH=$ORACLE_HOME/lib:/usr/lib
5. 准备相应目录，如日志文件路径，归档路径，参数文件路径，数据文件准备存放路径等
$mkdir -p /oradata/yxjdb0
$mkdir -p /oradata/yxjdb0
$mkdir -p $ORACLE_BASE/admin/yxjdb0
$mkdir -p $ORACLE_BASE/admin/yxjdb0/bdump
$mkdir -p $ORACLE_BASE/admin/Byxjdb0S/cdump
$mkdir -p $ORACLE_BASE/admin/yxjdb0/udump

6. 建立备用数据库参数文件
主库的参数如下：
yxjdb0.__db_cache_size=339738624
yxjdb0.__java_pool_size=33554432
yxjdb0.__large_pool_size=4194304
yxjdb0.__shared_pool_size=218103808
*.background_dump_dest='$ORACLE_BASE/admin/YXJDB0/bdump'
*.compatible='10.2.0.3.0'
*.control_files='/oradata/YXJDB0/control01.ctl','/oradata/YXJDB0/control02.ctl','/oradata/YXJDB0/control03.ctl'
*.core_dump_dest='$ORACLE_BASE/admin/YXJDB0/cdump'
*.db_block_size=8192
*.db_domain=''
*.db_file_multiblock_read_count=16
*.db_name='YXJDB0'
*.db_recovery_file_dest='$ORACLE_BASE/10g/flash_recovery_area'
*.db_recovery_file_dest_size=2147483648
*.db_writer_processes=4
*.dispatchers='(PROTOCOL=TCP) (SERVICE=YXJDB0XDB)'
*.global_names=FALSE
*.java_pool_size=32M
*.job_queue_processes=10
*.license_max_users=250
*.log_archive_dest_1='location=/oradata/YXJDB0/arch'
*.log_archive_dest_2='SERVICE=dbstandby LGWR'
*.open_cursors=300
*.pga_aggregate_target=199229440
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.sga_target=598736896
*.undo_management='AUTO'
*.undo_tablespace='UNDOTBS1'
*.user_dump_dest='$ORACLE_BASE/admin/YXJDB0/udump'
*.utl_file_dir='$ORACLE_BASE/admin/YXJDB0/bdump'

与主数据库不一样的参数如下：
#standby database parameter
standby_file_management=AUTO
remote_archive_enable=TRUE
standby_archive_dest='/oradata/YXJDB0/arch1'
fal_server='PRIMARY'
fal_client='STANDBY'

7. 从主服务器拷贝口令文件到备用服务器

8. 配置网络连接
修改主服务器的
listener.ora文件如下：
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC))
)
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.120)(PORT = 1521))
)
)
)

SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME = /oracle/app/10g)
(PROGRAM = extproc)
)
(SID_DESC =
(GLOBAL_DBNAME = YXJDB0)
(ORACLE_HOME = /oracle/app/10g)
(SID_NAME = YXJDB0)
)
)
$lsnrctl start
$lsnrctl status 查看监听状态.

修改主服务器的
tnsnames.ora文件如下：
YXJDB0 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.120)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = YXJDB0)
)
)
EXTPROC_CONNECTION_DATA =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC))
)
(CONNECT_DATA =
(SID = PLSExtProc)
(PRESENTATION = RO)
)
)
PRIMARY =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.120)(PORT = 1521))
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = YXJDB0)
)
)

STANDBY =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.101)(PORT = 1522))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = YXJDB0)
)
)

修改备用服务器的
listener.ora文件如下：
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.101)(PORT = 1521))
)
)
)

SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = YXJDB0)
(ORACLE_HOME = /u01/app/oracle/product/10.1.0/Db_1)
(SID_NAME = YXJDB0)
)
)

修改备用服务器的
/tnsnames.ora文件如下：
PRIMARY =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.120)(PORT = 1521)))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = YXJDB0)))
STANDBY =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.101)(PORT = 1522))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = YXJDB0)))

测试:
在主和备用机上分别执行
tnsping primary
tnsping standby

9. 在主数据库创建备用服务器控制文件
alter database create standby controlfile as '/home/oracle/standby.ctl';
创建后将控制文件cp(rcp or scp)到备用数据库所在的控制文件目录下。
10. 启动备用数据库
conn sys as sysdba
create spfile from pfile;
startup nomount;
alter database mount standby database;
恢复数据库:
RMAN> connect target;

RMAN> restore database;

RMAN>recovery database;
如果有恢复的日志并想手工恢复，可以运行如下命令
SQL>recover automatic standby database;
SQL>alter database recover managed standby database disconnect from session;
三、采用Lgwr进程传递联日志机的最大性能模式
1. 在备用数据库上创建备用日志
alter database recover managed standby database cancel;
alter database add standby LOGFILE GROUP 5 ('oradata/YXJDB0/stdy_redo05.log') size 10m;
alter database add standby LOGFILE GROUP 6 ('oradata/YXJDB0/stdy_redo06.log') size 10m;
alter database add standby LOGFILE GROUP 7 ('oradata/YXJDB0/stdy_redo07.log') size 10m;
alter database add standby LOGFILE GROUP 8 ('oradata/YXJDB0/stdy_redo08.log') size 10m;
alter database recover managed standby database disconnect from session;

2. 修改主库的归档路径
alter system set LOG_ARCHIVE_DEST_2='SERVICE=standby LGWR' scope=both;
另外，如果考虑到以后该库可能被切换到备用数据库，也可以创建同样的备用日志
组:
alter database add standby LOGFILE GROUP 5 ('/oradata/YXJDB0/stdy_redo05.log') size 10m;
alter database add standby LOGFILE GROUP 6 ('/oradata/YXJDB0/stdy_redo06.log') size 10m;
alter database add standby LOGFILE GROUP 7 ('/ oradata/YXJDB0/stdy_redo07.log') size 10m;
alter database add standby LOGFILE GROUP 8 ('/ oradata/YXJDB0/stdy_redo08.log') size 10m;

四、验证备用服务器是否工作
在主库上：
create user test identified by ftp123;
grant connect,resource to test;
conn test/ftp123@primary;
create table test(name varchar2(20));
insert into test values('hi, Data Guard');
commit;
conn / as sysdba;
alter system switch logfile;
查看从库日志
以只读方式打开从库查看 insert into test values('hi, Data Guard'); 已经生效。
conn / as sysdba;
alter database recover managed standby database cancel;
alter database open read only;
conn test/ftp123
select * from test;
再次设置从库在恢复模式：
alter database recover managed standby database disconnect from session;
五、日常管理
1. 备用服务器的管理模式与只读模式
（1）启动到管理模式
SQL>shutdown immediate;
SQL>startup nomount;
SQL>alter database mount standby database;
SQL>alter database recover managed standby database disconnect from session;
（2）启动到只读方式
SQL>shutdown immediate;
SQL>startup nomount;
SQL>alter database mount standby database;
SQL>alter database open read only;
（3）如果在管理恢复模式下到只读模式
SQL>recover managed standby database cancel;
SQL>alter database open read only;
这个时候，可以给数据库增加临时数据文件（这个在热备份的时候是没有备份过来的）
如
alter tablespace temp add tempfile '/u02/oradata/YXJDB0/temp01.dbf' size 100M;
（4）从只读方式到管理恢复方式
SQL>recover managed standby database disconnect from session;