oracle DG部署(新手看这一篇就够了)

手把手oracle dg新手部署

qq_22799793

2321人浏览 · 2024-09-18 13:35:17

qq_22799793 · 2024-09-18 13:35:17 发布

主库

1.检查主库是否开归档

archive log list
若没有
alter database archivelog;

alter database force logging;

ALTER SYSTEM SET LOG_ARCHIVE_DEST_1='LOCATION=/oraarch/CHERRY valid_for=(all_logfiles,all_roles)' SCOPE=BOTH sid='*';

2.添加standby redo log

select group#,THREAD#,bytes/1024/1024 from v$log;

GROUP# BYTES/1024/1024
---------- ---------------
1 1024
2 1024
3 1024
4 1024

alter database add standby logfile ('/oradata/CHERRY/stdredo1.log') size 150M;
alter database add standby logfile ('/oradata/CHERRY/stdredo2.log') size 150M;
alter database add standby logfile ('/oradata/CHERRY/stdredo3.log') size 150M;
alter database add standby logfile ('/oradata/CHERRY/stdredo4.log') size 150M;

SELECT GROUP#,THREAD#,SEQUENCE#,ARCHIVED,STATUS FROM gv$STANDBY_LOG;

3.修改主库参数

ALTER SYSTEM SET LOG_ARCHIVE_CONFIG='DG_CONFIG=(cherry,cherrydr)' scope=both sid='*';
ALTER SYSTEM SET LOG_ARCHIVE_DEST_1='LOCATION=/oraarch/CHERRY valid_for=(all_logfiles,all_roles)' SCOPE=BOTH sid='*';
ALTER SYSTEM SET LOG_ARCHIVE_DEST_2='SERVICE=cherrydr ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=cherrydr' SCOPE=both sid='*';
ALTER SYSTEM SET FAL_SERVER='cherrydr' scope=both sid='*';
ALTER SYSTEM SET FAL_client='cherry' scope=both sid='*';
alter system set STANDBY_FILE_MANAGEMENT='AUTO' scope=both;
alter system set log_archive_format='%t_%s_%r.arc' scope=spfile sid='*';
alter system set remote_login_passwordfile='EXCLUSIVE' scope=spfile sid='*';
alter system set log_archive_dest_state_1=enable scope=both;
alter system set log_archive_dest_state_2=enable scope=both;

alter system set log_archive_format='%t_%s_%r.arc' scope=both;

4.拷贝密码文件

scp orapwcherry oracle@139.0.6.121:/u01/app/oracle/product/19.10.0/db_1/dbs/

5.修改主备端tnsnames.ora

!!!主备都要修改
cherry =
(DESCRIPTION=
(ADDRESS_LIST=
(LOAD_BALANCE = yes)
(ADDRESS=(PROTOCOL=TCP)(HOST=dc-ydmh-dg)(PORT=1521))
)
(CONNECT_DATA=
(SERVER = DEDICATED)
(SERVICE_NAME=cherry)
)
)

cherrydr =
(DESCRIPTION=
(ADDRESS_LIST=
(LOAD_BALANCE = yes)
(ADDRESS=(PROTOCOL=TCP)(HOST=dr-ydmh-dg)(PORT=1521))
)
(CONNECT_DATA=
(SERVER = DEDICATED)
(SERVICE_NAME=cherrydr)
)
)

6.修改主备端listener.ora

SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = cherrydr )
(ORACLE_HOME =/u01/app/oracle/product/19.10.0/db_1)
(SID_NAME = cherry)
)
)

配置完监听后启动监听，静态监听信息如下：
Instance "cherry", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully

7.创建pfile并拷贝至备端修改

create pfile from spfile;
scp initcherry.ora oracle@139.0.6.121:/u01/app/oracle/product/19.10.0/db_1/dbs/

cherry.__data_transfer_cache_size=0
cherry.__db_cache_size=18723373056
cherry.__inmemory_ext_roarea=0
cherry.__inmemory_ext_rwarea=0
cherry.__java_pool_size=0
cherry.__large_pool_size=134217728
cherry.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment
cherry.__pga_aggregate_target=5368709120
cherry.__sga_target=21474836480
cherry.__shared_io_pool_size=134217728
cherry.__shared_pool_size=2415919104
cherry.__streams_pool_size=0
cherry.__unified_pga_pool_size=0
*.audit_file_dest='/u01/app/oracle/admin/cherry/adump'
*.audit_trail='db'
*.compatible='19.0.0'
*.control_files='/oradata/CHERRY/control01.ctl','/oradata/CHERRY/control02.ctl'
*.db_block_size=8192
*.db_name='cherry'
*.db_unique_name='cherrydr'
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=cherryXDB)'
*.fal_client='cherrydr'
*.fal_server='cherry'
*.log_archive_config='DG_CONFIG=(cherrydr,cherry)'
*.log_archive_dest_1='LOCATION=/oraarch/CHERRY valid_for=(all_logfiles,all_roles)'
*.log_archive_dest_2='SERVICE=cherry ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=cherry'
*.log_archive_dest_state_1='ENABLE'
*.log_archive_dest_state_2='ENABLE'
*.log_archive_format='%t_%s_%r.arc'
*.nls_language='AMERICAN'
*.nls_territory='AMERICA'
*.open_cursors=300
*.pga_aggregate_target=5g
*.processes=3000
*.remote_login_passwordfile='EXCLUSIVE'
*.sga_target=20g
*.standby_file_management='AUTO'
*.undo_tablespace='UNDOTBS1'

8.创建目录

oracle用户给
cd /u01/app/oracle/
mkdir -p admin/cherry/adump

9.启动数据库到mount状态

使用上一步修改的pfile文件启动standby端到nomount状态
[oracle@dr-ydmh-db admin]$ export ORACLE_SID=cherry
[oracle@dr-ydmh-db admin]$ sqlplus / as sysdba
SQL> startup nomount

10.测试主备机连通性

sqlplus sys/oracle120@cherrydr as sysdba
sqlplus sys/oracle120@cherry as sysdba

11.rman登录主端，恢复数据

$rman target sys/oracle120@cherry auxiliary sys/oracle120@cherrydr

run{
allocate channel c1 device type disk;
allocate auxiliary channel c2 device type disk;
duplicate target database for standby from active database nofilenamecheck;
release channel c1;
release channel c2;
}

备库

show parameter standby_file_management
NAME TYPE VALUE
------------------------------------ -------------------- ------------------------------
standby_file_management string AUTO
SQL> alter system set standby_file_management=manual;

select member from v$logfile;
/u01/app/oracle/product/19.10.0/db_1/dbs/broken0
/u01/app/oracle/product/19.10.0/db_1/dbs/broken1
/u01/app/oracle/product/19.10.0/db_1/dbs/broken2
/u01/app/oracle/product/19.10.0/db_1/dbs/broken3
/u01/app/oracle/product/19.10.0/db_1/dbs/broken4
/u01/app/oracle/product/19.10.0/db_1/dbs/broken5
/u01/app/oracle/product/19.10.0/db_1/dbs/broken6

alter database rename file '/u01/app/oracle/product/19.10.0/db_1/dbs/broken0' to '/oradata/CHERRY/redo01.log';
alter database rename file '/u01/app/oracle/product/19.10.0/db_1/dbs/broken1' to '/oradata/CHERRY/redo02.log';
alter database rename file '/u01/app/oracle/product/19.10.0/db_1/dbs/broken2' to '/oradata/CHERRY/redo03.log';
alter database rename file '/u01/app/oracle/product/19.10.0/db_1/dbs/broken3' to '/oradata/CHERRY/stdredo1.log';
alter database rename file '/u01/app/oracle/product/19.10.0/db_1/dbs/broken4' to '/oradata/CHERRY/stdredo2.log';
alter database rename file '/u01/app/oracle/product/19.10.0/db_1/dbs/broken5' to '/oradata/CHERRY/stdredo3.log';
alter database rename file '/u01/app/oracle/product/19.10.0/db_1/dbs/broken6' to '/oradata/CHERRY/stdredo4.log';

1.将主备库归档路径设为ENABLE:

alter system set log_archive_dest_state_2 = enable;

2.启用日志传输:

alter database recover managed standby database using current logfile disconnect;

3.查看备库属性

set linesize 400
col open_mode format a30
col database_role format a30
col switchover_status format a30
col protection_mode format a30
select protection_mode,open_mode,database_role,switchover_status from v$database;

4.查看同步进度

set line 400
col NAME format a20;
col VALUE format a30;
col UNIT format a30;
col TIME_COMPUTED format a20;
col DATUM_TIME format a20;

select * from v$dataguard_stats;

5.查看日志传输状态

col TYPE for a20
col UNITS for a20
col COMMENTS for a20
select * from v$recovery_progress;

修改成ADG

1.停止日志传输

alter database recover managed standby database cancel;

2.将备库启动至只读

alter database open read only;

3.开启日志传输

alter database recover managed standby database using current logfile disconnect;

4.查看同步进度

set line 400
col NAME format a20;
col VALUE format a30;
col UNIT format a30;
col TIME_COMPUTED format a20;
col DATUM_TIME format a20;

select * from v$dataguard_stats;

select THREAD#,max(SEQUENCE#) from v$archived_log group by thread#;
select THREAD#,SEQUENCE#,APPLIED from v$archived_log ;

####配置broker ########

1.###在主库注册静态监听###

节点1：
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = cherry_DGMGRL)
(ORACLE_HOME = /u01/app/oracle/product/19.10.0/db_1)
(SID_NAME = cherry)
)
(SID_DESC =
(GLOBAL_DBNAME = jy31db )
(ORACLE_HOME = /u01/app/oracle/product/19.10.0/db_1)
(SID_NAME = jy31cdb1)
)
)

Lsnrctl reload
Lsnrctl status

2.###修改主库broker参数###

一个节点操作即可
alter system set dg_broker_start=false scope=both ;
alter system set dg_broker_config_file1='/oradata/CHERRY/dr1.dat' scope=both ;
alter system set dg_broker_config_file2='/oradata/CHERRY/dr2.dat' scope=both ;
alter system set dg_broker_start=TRUE scope=both ;

3.###修改备库broker参数###

一个节点操作即可
alter system set dg_broker_start=false scope=both;
alter system set dg_broker_config_file1='/oradata/CHERRY/dr1.dat' scope=both ;
alter system set dg_broker_config_file2='/oradata/CHERRY/dr2.dat' scope=both ;
alter system set dg_broker_start=TRUE scope=both ;

4.备库重置LOG_ARCHIVE_DEST_n参数

一个节点操作即可
alter system set log_archive_dest_2='' scope=both ;
！！！如果不重置LOG_ARCHIVE_DEST_n参数，则在broker中添加物理备库时会报错。

5.添加broker配置

主库操作
$ dgmgrl sys/oracle120@cherrydr

DGMGRL> CREATE CONFIGURATION dgconfig AS PRIMARY DATABASE IS cherry CONNECT IDENTIFIER IS cherry ;
DGMGRL> ADD DATABASE cherrydr AS CONNECT IDENTIFIER IS cherrydr ;
DGMGRL> ENABLE CONFIGURATION
DGMGRL> show configuration

Configuration - dgconfig

Protection Mode: MaxPerformance
Members:
orcl - Primary database
orcldg - Physical standby database
orclrmt - Physical standby database

Fast-Start Failover: Disabled

Configuration Status:
SUCCESS (status updated 34 seconds ago)

切换测试：

DGMGRL> switchover to cherry

1> 首先在主库上配置一个TAF的service

begin
DBMS_SERVICE.CREATE_SERVICE(service_name => 'cherry_srv',
network_name => 'cherry_srv',
aq_ha_notifications => TRUE,
failover_method => 'BASIC',
failover_type => 'SELECT',
failover_retries => 30,
failover_delay => 5);
end;
/

2> 建立一个存储过程，用于调用service，确保只在主库运行

create or replace procedure dg_taf_proc_cherry is v_role VARCHAR(30);
begin
select DATABASE_ROLE into v_role from V$DATABASE;
if v_role = 'PRIMARY' then
DBMS_SERVICE.START_SERVICE('cherry_srv');
else
DBMS_SERVICE.STOP_SERVICE('cherry_srv');
end if;
end;
/

3>创建1个触发器来确保服务可以运行

create or replace TRIGGER dg_taf_trg_startup_cherry
after startup or db_role_change on database
begin
dg_taf_proc_cherry;
end;
/

4>启动新创建的service

主库执行：exec dg_taf_proc_cherry

Service "dg_taf_cherry" has 1 instance(s).
Instance "cherry", status READY, has 1 handler(s) for this service...

5>在备库查询，确认触发器和存过已经应用到备库

select trigger_name, trigger_name from dba_triggers where trigger_name = 'dg_taf_trg_startup_cherry';

select d.owner,d.OBJECT_NAME from dba_procedures d where d.OBJECT_NAME = 'dg_taf_proc_cherry';

魔乐社区

魔乐社区（Modelers.cn) 是一个中立、公益的人工智能社区，提供人工智能工具、模型、数据的托管、展示与应用协同服务，为人工智能开发及爱好者搭建开放的学习交流平台。社区通过理事会方式运作，由全产业链共同建设、共同运营、共同享有，推动国产AI生态繁荣发展。

更多推荐

【计算机视觉】Pixel逐像素分类&Mask掩码分类理解摘要

魔乐社区

计算机视觉（opencv）实战三十二——CascadeClassifier 人脸微笑检测（摄像头）

本文从原理到实现，详细介绍了基于 OpenCV Haar 分类器的人脸与微笑检测：讲解了 Haar 特征和级联检测原理。对代码逐行拆解并解释参数含义。画出完整流程图，帮助理解执行过程。给出了常见问题和优化建议，甚至扩展到深度学习方法。这种方法简单、轻量、实时性好，非常适合入门和小型应用项目。但如果需要更高准确率和更强鲁棒性，建议使用深度学习检测器替代 Haar 分类器。