Oracle 19c 单实例DG搭建最终版本
登录 / 注册
侧边栏壁纸
博主昵称
Aux

  • 累计撰写 29 篇文章
  • 累计收到 0 条评论
Oracle 安装类

Oracle 19c 单实例DG搭建最终版本

owner
owner
2024-01-25 / 0 评论 / 96 阅读 / 正在检测是否收录...

Oracle 19c 单实例DG搭建

1.数据库配置

1.1.关闭数据库
sqlplus / as sysdba
shutdown immediate
1.2.启动到mount模式
startup mount;
1.3.修改archivelog
#oracle用户下创建新的闪回路径（主库和备库同时创建）
mkdir -p /u01/app/oracle/arch_risdb
#sqlplus下修改archive log 日志路径
alter system set log_archive_dest_1='location=/u01/app/oracle/arch_risdb';
alter database archivelog;
1.4.开启闪回模式
#创建闪回目录
mkdir -p /u01/app/oracle/flashback_area
#修改闪回路径
show parameter db_recovery_file_dest;
alter system set db_recovery_file_dest='/u01/app/oracle/flashback_area' scope=spfile;
alter system set db_recovery_file_dest_size=16G;
alter database flashback on;
1.5.为备库创建日志文件
set wrap off;
select * from v$logfile order by group#;    #查询日志组
GROUP# STATUS    TYPE        MEMBER  
---------- -------------- -------------------- ---------------
1                ONLINE         /u01/app/oracle/oradata/RISDB/redo01.log
2                ONLINE         /u01/app/oracle/oradata/RISDB/redo02.log
3                ONLINE         /u01/app/oracle/oradata/RISDB/redo03.log
#    当前在线日志共有3组，为备库创建的日志建议比在线日志多一组，即4组
alter database add standby logfile group 4 ('/u01/app/oracle/oradata/SPECTRA/sredo04.log') size 200m;
alter database add standby logfile group 5 ('/u01/app/oracle/oradata/SPECTRA/sredo05.log') size 200m;
alter database add standby logfile group 6 ('/u01/app/oracle/oradata/SPECTRA/sredo06.log') size 200m;
alter database add standby logfile group 7 ('/u01/app/oracle/oradata/SPECTRA/sredo07.log') size 200m;
select * from v$logfile order by group#;    #查询日志组
GROUP# STATUS    TYPE        MEMBER  
---------- -------------- -------------------- ----------------------------------------------------
1                ONLINE         /u01/app/oracle/oradata/RISDB/redo01.log
2                ONLINE         /u01/app/oracle/oradata/RISDB/redo02.log
3                ONLINE         /u01/app/oracle/oradata/RISDB/redo03.log
4                STANDBY        /u01/app/oracle/oradata/RISDB/redo04.log
5                STANDBY        /u01/app/oracle/oradata/RISDB/redo05.log
6                STANDBY        /u01/app/oracle/oradata/RISDB/redo06.log
7                STANDBY        /u01/app/oracle/oradata/RISDB/redo07.log
1.6.主库修改系统参数
#设置db_unique_name
alter system set db_unique_name='master' scope=spfile;
#设置日志归档配置，用于限制日志传输的目标，只能在这两个指定的数据库进行，该配置中的顺序可以调换
alter system set log_archive_config='DG_CONFIG=(master,slave)' scope=spfile;
#设置日志归档路径1
alter system set log_archive_dest_1='location=/u01/app/oracle/arch_risdb VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=master' scope=spfile;
#设置日志归档路径2
alter system set log_archive_dest_2='SERVICE=slave LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLES) DB_UNIQUE_NAME=slave' scope=spfile;
#启用归档路径1
alter system set log_archive_dest_state_1='enable' scope=spfile;
#启用归档路径2
alter system set log_archive_dest_state_2='enable' scope=spfile;
#设置当前库获取归档日志的服务器listerner名称（与8成对出现，用于switchover角色切换）
alter system set fal_server='slave' scope=spfile;
#设置当前库获取归档日志的客户端名称listerner名称（与7成对出现，用于switchover角色切换）
alter system set fal_client='master' scope=spfile;
#设置归档日志强制切换时间（s）1800表示30分钟
alter system set archive_lag_target=1800 scope=spfile;
#设置归档日志文件名命名方式
alter system set log_archive_format='%t_%s_%r.arc'scope=spfile;
#设置备库文件管理的方式为auto,结合后续的convert参数，当主库增删文件时会自动在备库也生成相应文件。
alter system set standby_file_management=auto scope=spfile;
#设置数据文件名称的映射关系（注意顺序不能变）
alter system set db_file_name_convert='slave','master' scope=spfile;
#设置日志文件名称的映射关系（注意顺序不能变）
alter system set log_file_name_convert='slave','master' scope=spfile;
1.7.主库重启
alter database open;
shutdown immediate;
startup;
1.8.主库创建pfile文件
create pfile from spfile;
#Pfile文件位置：/u01/app/oracle/product/19c/db_1/dbs/initrisdb.ora
1.9.主库配置网络监听
#修改/u01/app/oracle/product/19c/db_1/network/admin/listener.ora
# listener.ora Network Configuration File: /u01/app/oracle/product/19c/db_1/network/admin/listener.ora
# Generated by Oracle configuration tools.

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = pacspri)(PORT = 1521))
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )
  )

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = CLRExtProc)
      (ORACLE_HOME = /u01/app/oracle/product/19c/db_1)
      (PROGRAM = extproc)
    )
    (SID_DESC =
      (GLOBAL_DBNAME = pacspri)
      (ORACLE_HOME = /u01/app/oracle/product/19c/db_1)
      (SID_NAME = risdb)
    )
  )
## 修改 /u01/app/oracle/product/19c/db_1/network/admin/tnsnames.ora
# tnsnames.ora Network Configuration File: /u01/app/oracle/product/19c/db_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.
PRIMARY =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.151)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = risdb)
    )
  )
standby =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.152)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = risdb)
    )
  )

RISDB =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = risdb)
    )
  )
1.10.主库测试监听
#重启LISTENER服务
lsnrctl stop
lsnrctl start
tnsping primary
tnsping standby
tnsping risdb
#在主库上tnsping测试都能ping通
1.11.主库复制配置及参数文件到备库
#关闭主数据库（备库）
SQL> shutdown immediate
#将主库pfile,orapwrisdb,listener.ora和tnsnames.ora文件拷贝到备库相应目录下。
1.12.修改监听文件
# listener.ora Network Configuration File: /u01/app/oracle/product/19c/db_1/network/admin/listener.ora
# Generated by Oracle configuration tools.

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = pacsstd)(PORT = 1521))
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )
  )

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = CLRExtProc)
      (ORACLE_HOME = /u01/app/oracle/product/19c/db_1)
      (PROGRAM = extproc)
    )
    (SID_DESC =
      (GLOBAL_DBNAME = risdb)
      (ORACLE_HOME = /u01/app/oracle/product/19c/db_1)
      (SID_NAME = risdb)
    )
  )
1.13.备库启动监听并测试tnsping
#重启LISTENER服务
lsnrctl stop
lsnrctl start
tnsping primary
tnsping standby
tnsping risdb
#在备库上tnsping测试都能ping通
1.14.修改参数文件
risdb.__data_transfer_cache_size=0
risdb.__db_cache_size=241055039488
risdb.__inmemory_ext_roarea=0
risdb.__inmemory_ext_rwarea=0
risdb.__java_pool_size=0
risdb.__large_pool_size=2684354560
risdb.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment
risdb.__pga_aggregate_target=17179869184
risdb.__sga_target=274877906944
risdb.__shared_io_pool_size=536870912
risdb.__shared_pool_size=30064771072
risdb.__streams_pool_size=0
risdb.__unified_pga_pool_size=0
*.archive_lag_target=1800
*.audit_file_dest='/u01/app/oracle/admin/risdb/adump'
*.audit_trail='db'
*.compatible='19.0.0'
*.control_files='/u01/app/oracle/oradata/RISDB/control01.ctl','/u01/app/oracle/oradata/RISDB/control02.ctl'
*.db_block_size=8192
*.db_file_name_convert='primary','standby'    #'standby','primary'交换位置
*.db_name='risdb'
*.db_recovery_file_dest='/u01/app/oracle/flashback_area'
*.db_recovery_file_dest_size=17179869184
*.db_unique_name='standby'            #primary修改为standby
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=risdbXDB)'
*.fal_client='standby'              #primary修改为standby
*.fal_server='primary'              #standby修改为primary
*.local_listener='LISTENER_RISDB'
*.log_archive_config='DG_CONFIG=(primary,standby)'
*.log_archive_dest_1='location=/u01/app/oracle/arch_risdb VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=standby'            #primary修改为standby
*.log_archive_dest_2='SERVICE=primary LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLES) DB_UNIQUE_NAME=primary'              #standby两处，修改为primary
*.log_archive_dest_state_1='enable'
*.log_archive_dest_state_2='enable'
*.log_archive_format='%t_%s_%r.arc'
*.log_file_name_convert='primary','standby'     #standby primary修改位置
*.nls_language='AMERICAN'
*.nls_territory='AMERICA'
*.open_cursors=300
*.pga_aggregate_target=16g
*.processes=3000
*.remote_login_passwordfile='EXCLUSIVE'
*.sga_target=256g
*.standby_file_management='AUTO'
*.undo_tablespace='UNDOTBS1'
#改完且检查路径是否都存在
1.15.修改完成后通过pfile启动并创建spfile
sqlplus / as sysdba
SQL> startup pfile="/u01/app/oracle/product/19c/db_1/dbs/initrisdb.ora";
ORACLE instance started.

Total System Global Area 2.7488E+11 bytes
Fixed Size                 30150760 bytes
Variable Size            3.2749E+10 bytes
Database Buffers         2.4159E+11 bytes
Redo Buffers              506716160 bytes
Database mounted.
Database opened.
SQL> create spfile from pfile;
File created.
SQL>

2.恢复数据库

2.1.连接备库进行数据恢复
rman target sys/oracle19c@primary auxiliary sys/oracle19c@standby
duplicate target database for standby from active database nofilenamecheck;
duplicate target database for standby from active database dorecover nofilenamecheck;
2.2.备库启动数据同步
#完成后关闭数据库，启动到mount模式
startup mount;
alter database recover managed standby database disconnect from session;
#如果要停止同步
SQL> alter database recover managed standby database cancel;
#注：如果想让备库比主库延迟几分钟可以启动为如下方式
SQL> alter database recover managed standby database delay 5 disconnect from session;
2.3.恢复后数据测试
#验证数据是否同步（在主库和备库上都执行，对比日志文件如果备库和主库一样表示同步完成）
select name,sequence#,applied,completion_time from v$archived_log order by completion_time desc,sequence# desc;

select name,sequence#,applied, To_Char(a.COMPLETION_TIME, 'YYYY-MM-DD HH24:MI:SS') AS COMPLETETIME from v$archived_log a  where a.COMPLETION_TIME>sysdate -1/24 order by sequence#;

#主库上切换日志文件再观察有没有正确同步到备库
alter system switch logfile;
2.4.验证数据库状态
SQL> select switchover_status from v$database;
#主库状态为 TO STANDBY表示可切换为备库
#备库状态为 NOT ALLOWED
2.5.主备切换
##执行切换命令，并将主库转换为备库角色
##主库执行切换：
SQL> alter database commit to switchover to physical standby with session shutdown;
##主库角色转换为备库：
SQL> shutdown immediate;
SQL> startup nomount;
SQL> alter database mount standby database;
SQL> alter database recover managed standby database disconnect from session;

##备库执行切换：
SQL> alter database commit to switchover to primary;
SQL> shutdown immediate;
SQL> startup

##切换完成后可以alter system switch logfile来验证一下切换后的主备库数据同步是否正常。
##注意：
##如果当前主库上活动的SESSION正在查询数据，则select switchover_status from v$database 命令会显示SESSIONS ACTIVE，可以通过如下命令进行切换。
alter database commit to switchover to physical standby with session shutdown;

##在备库上查询select switchover_status from v$database会显示 NOT ALLOWED,只有当主库上执行了alter database commit to switchover to physical standby后，备库上才会显示TO PRIMARY
2.6.故障切换
##当主库发生无法修复的故障时，需要紧急将备库提升为主库来接管服务。这种情况下，需要预先开启主库的flashback，当主库修复后还可以通过flashback闪回到一个时间点，并切换为备库，否则就只能重建备库。
SQL> alter database recover managed standby database cancel;

SQL> alter database recover managed standby database finish force;

SQL> alter database commit to switchover to primary;

SQL> alter database open;

SQL> select database_role from v$database;

DATABASE_ROLE
--------------------------------
PRIMARY
2.7.通过flashback重建备库
##当原来的主库经过维修后还可以启动数据库，则可以通过闪回到指定的SCN来进行standby重建
##A). 首先在当前主库，也就是failover过后的新的主库上查询到自己变成主库的那一刻的scn号：
SQL> select to_char(standby_became_primary_scn) from v$database;
TO_CHAR(STANDBY_BECAME_PRIMARY_SCN)
-----------------------------------------------------------------------
1400381
##B). 对到原来的主库（已修好的机器）上闪回数据，并进行切换
SQL> shutdown immediate;
SQL> startup mount;
SQL> flashback database to scn 1400381;
闪回完成。
SQL> alter database convert to physical standby;
SQL> shutdown immediate;
SQL> startup mount;
##C). 在当前主库重新启用log_archive_dest_state_2并切换归档日志（或者直接重启数据库）
SQL> alter system set log_archive_dest_state_2=enable;
SQL> alter system switch logfile;
##D). 回到原主库（当前备库）开启日志应用进程
SQL> alter database recover managed standby database using current logfile disconnect;
##此时数据库已经完成切换，重新回到DG组中，并以备库的方式加入。可以通过3.13.5中的方法验证数据同步状态。
备注
##Oracle Dtataguard有三种保护模式：
    Maximum Availability  Maximum Performance Maximum Protection
    AFFIRM  NOAFFIRM  AFFIRM
    SYNC  ASYNC SYNC
##Oracle Dataguard配置过程中指定的log_archive_dest_2参数的值为SERVICE=standby LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLES) DB_UNIQUE_NAME=standby 其中ASYNC参数表示异步传输日志，这种日志传输方式只能配置为最佳性能模式，如果要修改为最高可用或者最大保护的话需要将该参数修改为AFFIRM。
##通过select protection_mode from v$database查询保护模式
SQL> select protection_mode from v$database;
PROTECTION_MODE
----------------------------------------
MAXIMUM PERFORMANCE
##备注2：
##当主备同步有问题时候可以通过查询错误输出来初步诊断
SQL> select error from v$archive_dest where target='STANDBY';
##备注：
##当备库由于错误的操作（错误的提升角色或者错误的打开到OPEN状态并且被写入了数据，导致了主备不一致，这时只能抛弃被错误写入的数据）可以将备库闪回到一个正常状态下再重新进行数据同步。比如今天下午2点发现主库有问题，无法启动。这时将备库提升为了主库并开启为OPEN状态，客户端写入了一小部分数据时才发现，原来备库的数据没有即时同步到最新状态。这种情况下只有等主库修复完成正常启动，然后再从主库重新同步才能保证数据丢失更少。
##查询某一个时间点的scn号：
timestamp_to_scn('23-10月-19 11.00.00.000000000 上午') from dual;
0
暂无标签
版权属于： owner
本文链接： https://auxos.cn/archives/15/
作品采用： 《 署名-非商业性使用-相同方式共享 4.0 国际 (CC BY-NC-SA 4.0) 》许可协议授权

评论 (0)

取消