1. 准备
安装两台单实例11G,主库安装数据库,备库只安装软件。
实例名称 orclpri orclsla
2. 配置hosts文件(主备库都配置)
vi /etc/hosts
192.168.100.110 dg_pri
192.168.100.111 dg_sla
3. 主库设置为force logging模式
SQL> alter database force logging;
SQL>select force_logging from v$database;
4. 主库修改为归档模式
SQL> shutdown immediate
SQL> startup mount
SQL> alter database archivelog; //开启归档模式
SQL> alter database noarchivelog; //关闭归档模式
SQL> alter system set log_archive_dest_1=location=/data/archive scope=spfile; //修改归档路径
SQL> archive log list;
5. 添加主库的standby redo log
主库添加 standby redo log:大小和 online redo 相同,比 online redo group 多一组。
SQL> ALTER DATABASE ADD STANDBY LOGFILE GROUP 4 (/data/oracle/oradata/orclpri/redo04.log) size 50M;
SQL> ALTER DATABASE ADD STANDBY LOGFILE GROUP 5 (/data/oracle/oradata/orclpri/redo05.log) size 50M;
SQL> ALTER DATABASE ADD STANDBY LOGFILE GROUP 6 (/data/oracle/oradata/orclpri/redo06.log) size 50M;
SQL> ALTER DATABASE ADD STANDBY LOGFILE GROUP 7 (/data/oracle/oradata/orclpri/redo07.log) size 50M;
SQL>select group#,type,member from v$logfile;
6. 创建 Listener 并配置静态注册(主备库都做)
主库:
[oracle@dg_pri dbs]$ cd /data/oracle/product/11.2.0/db_1/network/admin/
[oracle@dg_pri admin]$ vi listener.ora
# listener.ora Network Configuration File: /data/oracle/product/11.2.0/db_1/network/admin/listener.ora
# Generated by Oracle configuration tools.
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = orclpri)
(ORACLE_HOME = /data/oracle/product/11.2.0/db_1)
(SID_NAME = orclpri)
)
)
LISTENER =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.100.110)(PORT = 1521))
)
ADR_BASE_LISTENER = /data/oracle
[oracle@dg_pri admin]$ vi tnsnames.ora
ORCL_PRI=
(DESCRIPTION=
(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.100.110)(PORT=1521))
(CONNECT_DATA=
(SERVER=DEDICATED)
(SERVICE_NAME=orclpri)
)
)
ORCL_SLA=
(DESCRIPTION=
(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.100.111)(PORT=1521))
(CONNECT_DATA=
(SERVER=DEDICATED)
(SERVICE_NAME=orclsla)
)
)
备库:
[oracle@dg_sla ~]$ cd /data/oracle/product/11.2.0/db_1/network/admin/
[oracle@dg_sla admin]$ vi listener.ora
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = orclsla)
(ORACLE_HOME = /data/oracle/product/11.2.0/db_1)
(SID_NAME = orclsla)
)
)
LISTENER =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.100.111)(PORT = 1521))
)
ADR_BASE_LISTENER = /data/oracle
[oracle@dg_sla admin]$ vi tnsnames.ora
ORCL_PRI=
(DESCRIPTION=
(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.100.110)(PORT=1521))
(CONNECT_DATA=
(SERVER=DEDICATED)
(SERVICE_NAME=orclpri)
)
)
ORCL_SLA=
(DESCRIPTION=
(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.100.111)(PORT=1521))
(CONNECT_DATA=
(SERVER=DEDICATED)
(SERVICE_NAME=orclsla)
)
)
主备库使用tnsping测试是否相同
[oracle@dg_pri admin]$tnsping ORCL_PRI
[oracle@dg_pri admin]$tnsping ORCL_SLA
重启监听
lsnrctl stop
lsnrctl start
7. 在备库创建相关的目录
oracle用户创建:
mkdir -p /data/archive
mkdir -p /data/oracle/admin
mkdir -p /data/oracle/oradata
mkdir -p /data/oracle/fast_recovery_area/orclsla
mkdir -p /data/oracle/fast_recovery_area/orclsla/onlinelog
mkdir -p /data/oracle/oradata/orclsla
mkdir -p /data/oracle/admin/orclsla
mkdir -p /data/oracle/admin/adump
mkdir -p /data/oracle/admin/orclsla/adump
mkdir -p /data/oracle/admin/orclsla/dpdump
mkdir -p /data/oracle/admin/orclsla/pfile
mkdir -p /data/oracle/admin/orclsla/scripts
mkdir -p /data/oracle/admin/orclpri/adump
8. 在主库创建 pfile 文件并修改pfile 内容
主库:
SQL> create pfile=/home/oracle/pfile from spfile;
在/home/oracle/pfile新增下内容
*.db_unique_name=orclpri
*.log_archive_config=dg_config=(orclpri,orclsla)
*.log_archive_dest_1=location=/data/archive valid_for=(all_logfiles,all_roles) db_unique_name=orclpri
*.log_archive_dest_2=service=orcl_sla valid_for=(online_logfiles,primary_role) db_unique_name=orclsla
*.log_archive_dest_state_1=enable
*.log_archive_dest_state_2=enable
REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE
LOG_ARCHIVE_FORMAT=%t_%s_%r.arc
*.standby_file_management=auto
*.fal_server=orcl_sla
*.fal_client=orcl_pri
*.log_file_name_convert=/data/oracle/oradata/orclpri,/data/oracle/oradata/orclsla
*.db_file_name_convert= /data/oracle/oradata/orclpri,/data/oracle/oradata/orclsla
SQL> shutdown immediate
SQL>create spfile from pfile=/home/oracle/pfile;
SQL> shutdown abort;
SQL> startup
备库:
将主库的参数文件copy到备库到修改
scp /home/oracle/pfile 192.168.100.111:/data/oracle/product/11.2.0/db_1/dbs
*.db_unique_name=orclsla
*.log_archive_config=dg_config=(orclpri,orclsla)
*.log_archive_dest_1=location=/data/archive valid_for=(all_logfiles,all_roles) db_unique_name=orclsla
*.log_archive_dest_2=service=orcl_pri valid_for=(online_logfiles,primary_role) db_unique_name=orclpri
*.log_archive_dest_state_1=enable
*.log_archive_dest_state_2=enable
REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE
LOG_ARCHIVE_FORMAT=%t_%s_%r.arc
*.standby_file_management=MANUAL
*.fal_server=orcl_pri
*.fal_client=orcl_sla
*.log_file_name_convert=/data/oracle/oradata/orclpri,/data/oracle/oradata/orclsla
*.db_file_name_convert= /data/oracle/oradata/orclpri,/data/oracle/oradata/orclsla
SQL> startup nomount; //可能会出现LRM-00109,根据提示的文件名修改刚才拷贝的参数文件名字
SQL> alter system set log_archive_dest_1 = location=/data/archive valid_for=(all_logfiles,all_roles) db_unique_name=orclsla scope=spfile; //nomount启动报ORA-16024错误时用此方法修改,先注释参数文件log_archive_dest_1这一行,然后nomount启动执行此修改命令。nomount正常启动则不需要执行
SQL> create spfile from pfile=/data/oracle/product/11.2.0/db_1/dbs/initorclsla.ora;
SQL> shutdown abort;
SQL> startup nomount;
9. 拷贝密码文件
[root@dg_pri ~]# cd /data/oracle/product/11.2.0/db_1/dbs/
[root@dg_pri dbs]#scp orapworclpri 192.168.100.111:/data/oracle/product/11.2.0/db_1/dbs
修改密码文件名:mv orapworclpri orapworclsla
10. 使用rman同步数据文件
[oracle@dg_pri dbs]$ rman target sys/123456@orcl_pri auxiliary sys/123456@orcl_sla;
RMAN>duplicate target database for standby from active database;
11. 开启备库并验证 数据是否能同步
备库:
SQL> alter database recover managed standby database cancel; #取消实时同步
SQL> shutdown immediate
SQL> startup mount
SQL> alter database flashback on; #开始flashback闪回
SQL> alter database open read only; #只读open数据库
SQL> alter database recover managed standby database using current logfile disconnect from session; #开始实时同步
主库:
create table test(id int);
备库:
select * from test;--有这边就说明搭建成功
查看主备库状态
SQL>select switchover_status,database_role from v$database;
查看日志
[root@dg_sla ~]# cd /data/oracle/diag/rdbms/orclsla/orclsla/trace
[root@dg_sla trace]# cat alert_orclsla.log
[root@dg_sla trace]# ls -lrt *arc1*
备库启动步骤:先启备库再启主库
startup nomount;
挂载数据库
alter database mount standby database;
启用应用重做
alter database recover managed standby database disconnect from session;
取消备库的自动恢复
alter database recover managed standby database cancel;
启动到只读状态
alter database open read only;
在“READ ONLY”状态下进一步启动备库的恢复,实时应用主库日志。
alter database recover managed standby database using current logfile disconnect;
更多精彩干货分享
点击下方名片关注
IT那活儿
文章版权归作者所有,未经允许请勿转载,若此文章存在违规行为,您可以联系管理员删除。
转载请注明本文地址:https://www.ucloud.cn/yun/129766.html
摘要:最近工作中用到了作为测试的数据库,在装和折腾了很久,这篇文章就这两点对大家分享一些我安装过程中遇到的问题和注意点,暂时还未配置,稍后找时间补上现在网上找一篇好的文章好难,往往要在一个话题或技术上找多方资源东拼西凑,互相借鉴才能达成目的,原因 最近工作中用到了Oracle作为Intel测试的数据库,在装Oracle Linux和Oracle Database 11g R2折腾了很久,这...
阅读 1346·2023-01-11 13:20
阅读 1684·2023-01-11 13:20
阅读 1132·2023-01-11 13:20
阅读 1860·2023-01-11 13:20
阅读 4100·2023-01-11 13:20
阅读 2704·2023-01-11 13:20
阅读 1385·2023-01-11 13:20
阅读 3597·2023-01-11 13:20