1

adg干货 -利来国际app

刘宁 2021-11-04
165

本次使用

oracle 11.2.0.4 数据库软件

rhel rhel6.2 操作系统

1.1修改主、备库hosts文件

vi /etc/hosts

172.20.0.7 liu
172.20.0.8 liudg

1.2环境变量

主库环境变量

vi .bash_profile
oracle_base=/u01/app/oracle; export oracle_base
oracle_home=$oracle_base/product/11.2.0.4/db_1; export oracle_home
oracle_sid=orcl; export oracle_sid
path=$oracle_home/bin:$path; export path
ld_library_path=$oracle_home/lib:/lib:/usr/lib; export ld_library_path
classpath=$oracle_home/jlib:$oracle_home/rdbms/jlib; export classpath
lang=en_us; export lang
oracle_unqname=orcl; export oracle_unqname
alias sqlplus="rlwrap sqlplus"
alias rman="rlwrap rman"

备库环境变量

vi .bash_profile
oracle_base=/u01/app/oracle; export oracle_base
oracle_home=$oracle_base/product/11.2.0.4/db_1; export oracle_home
path=$oracle_home/bin:$path; export path
oracle_sid=orcl; export oracle_sid
ld_library_path=$oracle_home/lib:/lib:/usr/lib; export ld_library_path
classpath=$oracle_home/jlib:$oracle_home/rdbms/jlib; export classpath
lang=en_us; export lang
oracle_unqname=orcldg; export oracle_unqname
alias sqlplus="rlwrap sqlplus"
alias rman="rlwrap rman"

1.3主备库环境准备

主库安装数据库软件、建库、建监听
./runinstaller ****安装数据库软件****
netca ****建监听****
dbca ****建库****
备库安装数据库软件、建监听 ./runinstaller ****安装数据库软件**** netca ****建监听****

2.1主库开启归档

archive log list;
shutdown immediate;
startup mount;
alter database archivelog;

2.2主库开启强制日志

alter database force logging

(在mount模式下执行,效率更快,可以开启归档时执行)

2.3主库开启flashback日志

select flashback_on from v$database;
alter database flashback on;
alter database open;

2.4修改tnsnames.ora 文件

cd $oracle_home/network/admin
***主备同步需要***
orcl =
  (description =
    (address = (protocol = tcp)(host = 172.20.0.7)(port = 1521))
    (connect_data =
        (server = dedicated)
        (service_name = orcl)
    )
)
orcldg =
    (description =
    (address = (protocol = tcp)(host = 172.20.0.8)(port = 1521))
    (connect_data =
        (server = dedicated)
        (service_name = orcl)
    )
)

2.5修改监听为静态注册

主库:
sid_list_listener =
    (sid_list =
    (sid_desc =
        (global_dbname = orcl)
        (sid_name = orcl)
        (oracle_home = /u01/app/oracle/product/11.2.0.4/db_1/)
    )
)
listener =
(description_list =
    (description =
        (address = (protocol = tcp)(host = 172.20.0.7)(port = 1521))
        (address = (protocol = ipc)(key = extproc1521))
    )
)
adr_base_listener = /u01/app/oracle
备库:
sid_list_listener =
    (sid_list =
        (sid_desc =
        (global_dbname = orcl)
        (sid_name = orcl)
        (oracle_home = /u01/app/oracle/product/11.2.0.4/db_1/)
        )
)
listener =
(description_list =
    (description =
        (address = (protocol = tcp)(host = 172.20.0.8)(port = 1521))
        (address = (protocol = ipc)(key = extproc1521))
    )
)
adr_base_listener = /u01/app/oracle
重启主备监听:
lsnrctl stop
lsnrctl start
主备检测tnsnames
tnsping orcldg
tnsping orcl

2.6.修改主库参数,增加standby 联机日志

sqlplus / as sysdba
增加以下内容
alter system set db_unique_name='orcl' scope=spfile;
alter system set log_archive_config='dg_config=(orcl,orcldg)';
alter system set log_archive_dest_1='location=/u01/app/oracle/arch valid_for=(all_logfiles,all_roles) db_unique_name=orcl' scope=spfile;
alter system set log_archive_dest_2='service=orcldg valid_for=(online_logfiles,primary_role) db_unique_name=orcldg' scope=spfile;
alter system set log_archive_dest_state_1='enable' scope=spfile;
alter system set log_archive_dest_state_2='enable' scope=spfile;
alter system set db_file_name_convert='/u01/app/oracle/oradata/orcl/datafile','/u01/app/oracle/oradata/orcldg/datafile';
alter system set log_file_name_convert='/u01/app/oracle/oradata/orcl/onlinelog/','/u01/app/oracle/oradata/orcldg/onlinelog/';
alter system set fal_server='orcldg';
alter system set fal_client='orcl';
alter system set standby_file_management='auto' scope=spfile;
alter system set remote_login_passwordfile=exclusive scope=spfile;
alter database add standby logfile '/u01/app/oracle/oradata/orcl/onlinelog/stredo01.log' size 50m;
alter database add standby logfile '/u01/app/oracle/oradata/orcl/onlinelog/stredo02.log' size 50m;
alter database add standby logfile '/u01/app/oracle/oradata/orcl/onlinelog/stredo03.log' size 50m;
alter database add standby logfile '/u01/app/oracle/oradata/orcl/onlinelog/stredo04.log' size 50m;
重启主库
shutdown immediate;
startup;

2.7生成主库pfile文件

create pfile='/home/oracle/pfile.ora' from spfile;

2.8传输相应文件到备库

1)主库密码文件

scp -r $oracle_home/dbs/orapworcl liudg:$oracle_home/dbs/ 

2) 主库pfile文件

scp -r /home/oracle/pfile.ora liudg:/home/oracle/

2.9修改备库pfile文件

(:orclorcldg互换即可/log_file_name_convertdb_file_name_convert无需变化)

3.1创建备库相应目录结构

mkdir -p /u01/app/oracle/admin/orcldg/adump
mkdir -p /u01/app/oracle/oradata/orcldg/controlfile/
mkdir -p /u01/app/oracle/fast_recovery_area/orcldg/controlfile/
mkdir -p /u01/app/oracle/diag/rdbms/orcldg/orcl/cdump
mkdir -p /u01/app/oracle/oradata/orcldg/datafile
mkdir -p /u01/app/oracle/arch
mkdir -p /u01/app/oracle/oradata/orcldg/onlinelog/standby
(注:据当前环境目录修改)

3.2启动备库到nomount状态

[[email protected] ~]$ sqlplus / as sysdba
sql> create spfile from pfile='/home/oracle/pfile.ora';
file created.
sql> startup nomount;
oracle instance started.
total system global area 835104768 bytes
fixed size 2257840 bytes
variable size 541068368 bytes
database buffers 289406976 bytes
redo buffers 2371584 bytes
sql>

3.3主库连接辅助库

[[email protected] ~]$ rman target sys/[email protected] auxiliary sys/[email protected]
recovery manager: release 11.2.0.4.0 - production on tue apr 10 14:32:33 2018
利来娱乐 copyright (c) 1982, 2011, oracle and/or its affiliates. all rights reserved.
connected to target database: orcl (dbid=1615274987)
connected to auxiliary database: orcl (not mounted)
rman>

3.4duplicate复制数据库

rman> duplicate target database for standby nofilenamecheck from active database;
nofilenamecheck: 相同目录结构使用此参数,结构不同使用该参数避免目录检查。
rman> duplicate target database for standby from active database;

sql>alter database recover managed standby database disconnect from session using current logfile;
sql> select name,archived,applied,sequence# from v$archived_log;
sql> alter system archive log current;
sql> select name,archived,applied,sequence# from v$archived_log;
sql> archive log list;
sql> alter system switch logfile;
sql> archive log list;

7.1关闭

先主库,后备库。
主库执行: shutdown immediate; lsnrctl stop 备库执行: alter database recover managed standby database cancel; 关闭实时同步 shutdown immediate; lsnrctl stop

7.2启动

先备库,后主库。
备库执行: lsnrctl start startup nomount; alter database mount standby database; alter database recover managed standby database using current logfile disconnect from session; 主库执行: lsnrctl start startup
1.主库启动到mount状态
2.主库中执行如下sql语句
sql> alter database set standby database to maximize {availability | performance | protection};
3.查询保护模式语句
sql> select protection_mode from v$database;
 
主库:
sql> select switchover_status from v$database;
1. 如果switchover_status为to_standby说明可以转换
直接转换:
alter database commit to switchover to physical standby;
2. 如果switchover_status为sessions active 则关闭会话
sql>alter database commit to switchover to physical standby with session shutdown;
3. startup #启动
4. alter database recover managed standby database using current logfile disconnect from session; #启同步
5.select name,open_mode,switchover_status,database_role from v$database; #查看状态
备库:
sql> select switchover_status from v$database;
1. 如果switchover_status为to_primary 说明标记恢复可以直接转换为primary库
sql>alter database commit to switchover to primary
2. 如果switchover_status为session active 就应该断开活动会话
sql>alter database commit to switchover to primary with session shutdown;
3. 如果switchover_status为not allowed 说明切换标记还没收到,此时不能,检查主库
4.alter database open;
5.select name,open_mode,switchover_status,database_role from v$database; 
 
旧备库切新主库:
alter database recover managed standby database finish force;
alter database commit to switchover to primary with session shutdown;
alter database open;
select name,log_mode ,open_mode ,database_role ,protection_mode ,switchover_status from v$database;
select to_char(standby_became_primary_scn) from v$database; #查询进主库的scn
新主库切旧备库: shutdown immediate startup mount flashback database to scn 新主库scn; alter database convert to physical standby; #转换physical standby database shutdown immediate; startup; select name,open_mode,switchover_status,database_role from v$database; #查看当前状态
alter database recovr managed standby database using current logfile disconnect from session; #开启mrp
select name,open_mode,switchover_status,database_role from v$database;

检查:
archive log list;
此刻是主备正常的
旧主库恢复后,就可以将角色转回
新主库状态:


alter database commit to switchover to pysical standby; #转回备库
startup;
select name,open_mode,switchover_status,database_role from v$database;
新备库状态:
此刻
alter database commit to switchover to primary with session shutdown ;
alter database open;

ok

1.主备库listener.ora静态注册中添加如下:

 
sid_list_listener =
    (sid_list =
    (sid_desc =
        (global_dbname = orcl)
        (sid_name = orcl)
        (oracle_home = /u01/app/oracle/product/11.2.0.4/db_1/)
)
    (sid_desc =
    (global_dbname = orcl_dgmgrl) #db_unique_name.dgmgrl
        (sid_name = orcl)
        (oracle_home = /u01/app/oracle/product/11.2.0.4/db_1/)
    )
)

2.开启broker

show parameter dg_broker_start;

alter system set dg_broker_start=true scope=spfile;

3.dgmgrl


到这儿就算是成功了,然后现在试试一条命令的转换主备

接下来要配置成功fast_start failover 需要满足以下5项条件.

1. dataguard 的配置要么是maxavailability模式要么是maxperformance模式.

2.dataguard的配置为maxavailability模式时,fast-start failover的目标standby数据库的log传送模式必须设置为sync.

3. dataguard的配置为maxperformance模式时,fast-start failover的目标standby数据库的log传送模式必须设置为async.

4. 主库与fast-start failover的目标standby数据库都必须激活flashback功能.

5. 当配置了多standby数据库时,在主库的配置属性faststartfailovertarget指定目标standby 数据库.

操作:略

配置完毕后

开启observer

dgmgrl> start observer(nohup dgmgrl -silent sys/[email protected] "start observer" &)

dgmgrl> enable fast_start failover

ok成功

最后修改时间:2021-12-06 14:49:03
「喜欢文章,快来给作者赞赏墨值吧」
【利来手机国际的版权声明】本文为墨天轮用户原创内容,转载时必须标注文章的来源(墨天轮),文章链接,文章作者等基本信息,否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容,欢迎发送邮件至:[email protected]进行举报,并提供相关证据,一经查实,墨天轮将立刻删除相关内容。

评论