本文根据一个实践操作的记录,详细介绍利用rman热备来创建dataguard物理Active备库;这种方式主库只需要重启一次即可,使主库具有更高的可用性!
在进行按本文档的操作之前,我们默认主数据库已经处于归档运行模式;大致流程是先配置主库的参数及做rman备份,然后在备库上装oracle软件、配置监听,不需要dbca创建数据库;然后修改备库的参数文件及进行rman恢复,之后进行测试。
环境说明:
primary: CentOS6.3 64位 192.168.100.117,db_name:ahqy,db_unique_name: ahqy
standby: CentOS6.3 64位 192.168.100.118,db_name:ahqy,db_unique_name: standby
oracle版本: 11.2.0.1 64位企业版
一、primary服务器配置
2:在备库上创建密码文件,这里直接从主库复制到备库
3:主库上配置Standby Redo Log
4.修改主库的初始化参数
5:配置主库的tnsnames.ora文件,备库需要同样的操作
6:在主库上准备备库需要的pfile和数据库备份文件,控制文件等
二、standby服务器配置
1:设置oracle_sid,配置tnsnames.ora
2:在备库上复制主库备份出来的备份文件,并准备相关的目录
3:将备库启动到nomount状态
4:使用rman对备库进行恢复
5: 将备库置于应用redolog模式
三、数据测试
1.在主库上创建表空间,建表
2.备库应用日志后以只读方式打开查看数据
3:从新将备库置于应用日志模式
四、将备库置为Active模式
1) 查看备库当前状态 mount
2) 取消备库的自动恢复
3) OPEN备库为只读模式(Dataguard只能启动到readonly模式)
五、主备库切换
主库操作:
备库操作:
六、注意事项
1:主备端都使用静态侦听
2:TNSNAME配置
tnsnames里的SERVICE_NAME可以根据show parameter name查看好之后,进行对应的配置。
七、参考文章
http://ylw6006.blog.51cto.com/470441/661105
http://www.educity.cn/shujuku/1177494.html
http://blog.csdn.net/w63667329/article/details/7900270
在进行按本文档的操作之前,我们默认主数据库已经处于归档运行模式;大致流程是先配置主库的参数及做rman备份,然后在备库上装oracle软件、配置监听,不需要dbca创建数据库;然后修改备库的参数文件及进行rman恢复,之后进行测试。
环境说明:
primary: CentOS6.3 64位 192.168.100.117,db_name:ahqy,db_unique_name: ahqy
standby: CentOS6.3 64位 192.168.100.118,db_name:ahqy,db_unique_name: standby
oracle版本: 11.2.0.1 64位企业版
一、primary服务器配置
1:主库上开启Forced Logging
alter database force logging;
2:在备库上创建密码文件,这里直接从主库复制到备库
scp /home/oracle/app/oracle/product/11.2.0/dbhome_1/dbs/orapwahqy 192.168.100.118:/home/oracle/app/oracle/product/11.2.0/dbhome_1/dbs/orapwahqy
3:主库上配置Standby Redo Log
select member from v$logfile;
du -h /home/oracle/oradata/ahqy/redo01.log
sqlplus / as sysdba
mkdir standby
alter database add standby logfile group 4 '/home/oracle/oradata/ahqy/standby/standby04.log' size 200M;
alter database add standby logfile group 5 '/home/oracle/oradata/ahqy/standby/standby05.log' size 200M;
alter database add standby logfile group 6 '/home/oracle/oradata/ahqy/standby/standby06.log' size 200M;
4.修改主库的初始化参数
alter system set LOG_ARCHIVE_CONFIG='DG_CONFIG=(ahqy,standby)';
alter system set log_archive_dest_1='LOCATION=/home/oracle/arch valid_for=(all_logfiles,all_roles) db_unique_name=ahqy' scope=spfile;
alter system set log_archive_dest_2='service=standby lgwr async valid_for=(online_logfiles,primary_role) db_unique_name=standby' scope=spfile;
alter system set log_archive_dest_state_1=enable;
alter system set log_archive_dest_state_2=enable;
alter system set log_archive_format='%t_%s_%r.arc' scope=spfile;
show parameter remote_login;
alter system set log_archive_max_processes=30;
alter system set fal_server=standby;
alter system set fal_client=ahqy;
alter system set standby_file_management=auto;
shutdown immediate;
startup;
5:配置主库的tnsnames.ora文件,备库需要同样的操作
cat /home/oracle/app/oracle/product/11.2.0/dbhome_1/network/admin//tnsnames.ora
# tnsnames.ora Network Configuration File: /home/oracle/app/oracle/product/11.2.0/dbhome_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.
LISTENER_AHQY =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.100.117)(PORT = 1521))
AHQY =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.100.117)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = ahqy)
)
)
STANDBY =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.100.118)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = standby)
)
)
6:在主库上准备备库需要的pfile和数据库备份文件,控制文件等
mkdir -p /home/oracle/backup/
create pfile='/home/oracle/backup/initahqy.ora' from spfile;
rman target /
backup tag 'dg_20141226' format '/home/oracle/backup/dg_%U' incremental level 0 database plus archivelog;
backup format '/home/oracle/backup/controlfile_%U' current controlfile for standby;
二、standby服务器配置
1:设置oracle_sid,配置tnsnames.ora
echo $ORACLE_SID
scp /home/oracle/app/oracle/product/11.2.0/dbhome_1/network/admin/tnsnames.ora oracle@192.168.100.118:/home/oracle/app/oracle/product/11.2.0/dbhome_1/network/admin/tnsnames.ora
2:在备库上复制主库备份出来的备份文件,并准备相关的目录
mkdir -p /home/oracle/backup/
scp -rp oracle@192.168.100.117:/home/oracle/backup/* /home/oracle/backup/
cp /home/oracle/backup/initahqy.ora $ORACLE_HOME/dbs
grep 'fal_' $ORACLE_HOME/dbs/initahqy.ora
*.fal_client='STANDBY'
*.fal_server='AHQY'
grep 'log_archive_dest_' $ORACLE_HOME/dbs/initahqy.ora
*.log_archive_dest_1='LOCATION=/home/oracle/arch valid_for=(all_logfiles,all_roles) db_unique_name=standby'
*.log_archive_dest_2='service=ahqy lgwr async valid_for=(online_logfiles,primary_role) db_unique_name=ahqy'
*.log_archive_dest_state_1='ENABLE'
*.log_archive_dest_state_2='ENABLE'
*.db_unique_name='standby' ----------------------------这一句漏了,导致默认成了AHQY,一定要加上
*.LOG_ARCHIVE_CONFIG='DG_CONFIG=(standby,ahqy)' --主备库切换用,否则切换会有问题
---alter system set LOG_ARCHIVE_CONFIG='DG_CONFIG=(standby,ahqy)';
mkdir -p /home/oracle/arch/ahqy
mkdir -p /home/oracle/app/oracle/admin/ahqy/{adump,bdump,cdump,dpdump,udump,pfile} --有几个文件夹可能用不到
mkdir -p /home/oracle/oradata/ahqy
mkdir -p /u01/app/oracle/flash_recover_area/ORCL --未找到
--(将相应的主库文件直接移到了备库上,如:/home/oracle/arch/、/home/oracle/oradata/、/home/oracle/oradiag_oracle)
mkdir -p /home/oracle/app/oracle/diag/rdbms/ahqy/ahqy/{alert,cdump,hm,incident,incpkg,ir,lck,metadata,stage,sweep,trace}
考虑到可能有文件夹没有建,所以我这里将几个主要文件夹直接从主库拷贝到备库相应的目录中去。
3:将备库启动到nomount状态
sqlplus / as sysdba
startup nomount;
4:使用rman对备库进行恢复
rman target sys/ORACLE@AHQY auxiliary /
duplicate target database for standby nofilenamecheck;
5: 将备库置于应用redolog模式
sqlplus / as sysdba
archive log list;
alter database recover managed standby database disconnect from session;
select SEQUENCE#,FIRST_TIME,NEXT_TIME,APPLIED from v$archived_log;
select member from v$logfile;
可以在主库上进行日志切换,加快备库应用日志的速度!
sqlplus / as sysdba
alter system switch logfile;
archive log list;
备库上再次查询:
select SEQUENCE#,FIRST_TIME,NEXT_TIME,APPLIED from v$archived_log;
select name,database_role from v$database;
三、数据测试
1.在主库上创建表空间,建表
select name,database_role from v$database;
create tablespace ahqy datafile '/home/oracle/oradata/ahqy/ahqy.dbf' size 10M autoextend on;
create table dg01 as select * from dba_source;
alter system switch logfile;
2.备库应用日志后以只读方式打开查看数据
alter database recover managed standby database cancel;
alter database open read only;
select type from dg01;
3:从新将备库置于应用日志模式
sqlplus / as sysdba
shutdown immediate;
startup nomount;
alter database mount standby database;
alter database recover managed standby database disconnect from session;
四、将备库置为Active模式
1) 查看备库当前状态 mount
select open_mode,database_role,db_unique_name from v$database;
2) 取消备库的自动恢复
alter database recover managed standby database cancel;
3) OPEN备库为只读模式(Dataguard只能启动到readonly模式)
alter database open;
select open_mode from v$database;
alter database recover managed standby database using current logfile disconnect;
select open_mode,database_role,db_unique_name from v$database;
五、主备库切换
主库操作:
alter database commit to switchover to physical standby with session shutdown;
shutdown immediate
startup mount
alter database recover managed standby database disconnect from session;
备库操作:
shutdown immediate
startup mount
alter database commit to switchover to primary;
alter database open;
六、注意事项
1:主备端都使用静态侦听
[oracle@118 admin]$ cat listener.ora
# listener.ora Network Configuration File: /home/oracle/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 = 192.168.100.118)(PORT = 1521))
)
)
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME = /home/oracle/app/oracle/product/11.2.0/dbhome_1)
(PROGRAM = extproc)
)
(SID_DESC =
(SID_NAME = ahqy)
(ORACLE_HOME = /home/oracle/app/oracle/product/11.2.0/dbhome_1)
(GLOBAL_DBNAME = standby)
)
)
ADR_BASE_LISTENER = /home/oracle/app/oracle
[oracle@117 admin]$ cat listener.ora
# listener.ora Network Configuration File: /home/oracle/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 = 192.168.100.117)(PORT = 1521))
)
)
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME = /home/oracle/app/oracle/product/11.2.0/dbhome_1)
(PROGRAM = extproc)
)
(SID_DESC =
(SID_NAME = ahqy)
(ORACLE_HOME = /home/oracle/app/oracle/product/11.2.0/dbhome_1)
(GLOBAL_DBNAME = ahqy)
)
)
ADR_BASE_LISTENER = /home/oracle/app/oracle
2:TNSNAME配置
tnsnames里的SERVICE_NAME可以根据show parameter name查看好之后,进行对应的配置。
[oracle@117 admin]$ cat tnsnames.ora
# tnsnames.ora Network Configuration File: /home/oracle/app/oracle/product/11.2.0/dbhome_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.
LISTENER_AHQY =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.100.117)(PORT = 1521))
AHQY =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.100.117)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = ahqy)
)
)
standby =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.100.118)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = standby)
)
)
[oracle@118 admin]$ cat tnsnames.ora
# tnsnames.ora Network Configuration File: /home/oracle/app/oracle/product/11.2.0/dbhome_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.
LISTENER_AHQY =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.100.117)(PORT = 1521))
AHQY =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.100.117)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = ahqy)
)
)
STANDBY =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.100.118)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = standby)
)
)
七、参考文章
http://ylw6006.blog.51cto.com/470441/661105
http://www.educity.cn/shujuku/1177494.html
http://blog.csdn.net/w63667329/article/details/7900270