DBA进阶(七)Oracle的ADG部署操作

提示:文章写完后,目录可以自动生成,如何生成可参考右边的帮助文档


前言

提示:这里可以添加本文要记录的大概内容:

客户需求:要为主数据库提供一个只查询的备库,方案使用部署ADG,借此机会出一篇关于ADG搭建的文章


提示:以下是本篇文章正文内容,下面案例可供参考

一、软硬件环境

主库:登云数据库一体机-大容量服务器/X8M-2
备库:smart-x超融合虚拟化环境
在这里插入图片描述

二、环境搭建(内容已脱敏处理)

此处完成备库的数据库软件安装以及打补丁操作
在这里插入图片描述

三、ADG环境搭建

3.1主库:检查归档模式

SQL> archive log list;
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     21
Next log sequence to archive   22
Current log sequence           22

SQL> alter database FORCE LOGGING;
Database altered.
SQL> select force_logging from v$database;

FORCE_LOGGING
---------------------------------------
YES

SQL> show parameter db_recovery_file_dest;

3.2主库:增加standby_log

[oracle@rac1 ~]$ echo $ORACLE_SID
cxldb1
如果不是cxldb1
则[oracle@rac1 ~]$ export ORACLE_SID=cxldb1
[oracle@rac1 ~]$ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Thu Jul 30 14:30:52 2020
Version 19.9.0.0.0

Copyright (c) 1982, 2020, Oracle.  All rights reserved.


Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.9.0.0.0

SQL> archive log list;
SQL> set line 160    
SQL> set wrap off
SQL> select * from v$Log;
    GROUP#    THREAD#  SEQUENCE#      BYTES  BLOCKSIZE    MEMBERS ARC STATUS           FIRST_CHANGE# FIRST_TIME          NEXT_CHANGE# NEXT_TIME
---------- ---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ------------------- ------------ -------------------
         1          1         21  209715200        512          1 YES INACTIVE               3782017 2020-07-29 12:22:29      3964886 2020-07-30 13:51:03
         2          1         22  209715200        512          1 NO  CURRENT                3964886 2020-07-30 13:51:03   9.2954E+18
         3          2          9  209715200        512          1 YES INACTIVE               3964887 2020-07-30 13:51:04      3985738 2020-07-30 13:59:05
         4          2         10  209715200        512          1 NO  CURRENT                3987920 2020-07-30 14:28:03   9.2954E+18 2020-07-30 14:28:03

SQL> select * from v$standby_log;
no rows selected

alter database add standby logfile thread 1 group 11 size 2048M;
alter database add standby logfile thread 1 group 12 size 2048M;
alter database add standby logfile thread 1 group 13 size 2048M;
alter database add standby logfile thread 1 group 14 size 2048M;
alter database add standby logfile thread 1 group 15 size 2048M;
alter database add standby logfile thread 1 group 16 size 2048M;
alter database add standby logfile thread 2 group 17 size 2048M;
alter database add standby logfile thread 2 group 18 size 2048M;
alter database add standby logfile thread 2 group 19 size 2048M;
alter database add standby logfile thread 2 group 20 size 2048M;
alter database add standby logfile thread 2 group 21 size 2048M;
alter database add standby logfile thread 2 group 22 size 2048M;

SQL> select * from v$standby_log;
SQL> exit

3.3主库:添加db_unique_name

SQL> alter system set db_unique_name='cxldb' scope=spfile sid='*';

3.4备库:配置静态监听

备库检查:
SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = dgcxldb)
      (ORACLE_HOME = /u01/app/oracle/product/19c/dbhome_1)
      (SID_NAME = dgcxldb)
    )
  )  

sqlnet.ora添加低版本客户端兼容连接:
cd /oracle/app/oracle/product/19c/dbhome_1/network/admin
$ more sqlnet.ora 
SQLNET.ALLOWED_LOGON_VERSION_SERVER=8
SQLNET.ALLOWED_LOGON_VERSION_CLIENT=8

启动备库监听:
lsnrctl start
lsnrctl status

3.5主备库:创建tnsnames.ora内容

节点12都配置:
[oracle@rac1 admin]$ cat tnsnames.ora
# tnsnames.ora Network Configuration File: /u01/app/oracle/product/19c/dbhome_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools. ###(instance_name=cxldb1/2)
cxldb1 =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.19.192)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = cxldb)
    )
  )
cxldb2 =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.19.193)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = cxldb)
    )
  )
cxl =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.100.100)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = cxl)
    )
  )
dgcxl =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.19.197)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = cxl)
    )
  )

tns_cxldb =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.100.100)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = cxldb)
    )
  )
tns_dgcxldb =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.19.197)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = dgcxldb)
    )
  )

3.6主库:密码文件拷贝到备库

登录主库任意节点拷贝密码文件并传输到备库:
su - grid
asmcmd
pwcopy pwdcxldb.256.1024323395 /oracle/app/grid/orapwcxldb1
scp /oracle/app/grid/orapwcxldb1 oracle@192.168.19.197: /u01/app/oracle/product/19c/dbhome_1/dbs/orapwdgcxldb

3.7备库:dbca创建备库dgcxldb

dbca -silent -createDuplicateDB \
-gdbName cxldb \
-sid dgcxldb \
-sysPassword XWYY_2021 \
-primaryDBConnectionString 192.168.100.100:1521/cxldb \
-nodelist dgcxldb \
-databaseConfigType SINGLE \
-storageType FS \
-createAsStandby -dbUniqueName dgcxldb \
-datafileDestination ' /u01/app/oracle/oradata' \
-initParams sga_target=30G,processes=2000,pga_aggregate_target=10G,db_recovery_file_dest_size=1000GB,db_recovery_file_dest=/u01/app/oracle/reco,audit_trail=none,db_create_file_dest=/u01/app/oracle/oradata, db_create_online_log_dest_1=/u01/app/oracle/oradata

Prepare for db operation
22% complete
Listener config step
44% complete
Auxiliary instance creation
67% complete
RMAN duplicate
89% complete
Post duplicate database operations
100% complete

Look at the log file "/u01/app/oracle/cfgtoollogs/dbca/dgcxldb/dgcxldb.log" for further details.

创建完成后注意检查路径下文件:
ll /u01/app/oracle/reco
ll /u01/app/oracle/oradata

传统方法参考:
主库创建参数文件:
create pfile=‘/home/oracle/pfile.txt’ from spfile;注意修改控制文件路径及相应目录。

*.audit_file_dest=‘/u01/app/oracle/admin/cxldb/adump’
*.audit_trail=‘none’
*.compatible=‘19.0.0’
*.control_files=‘/u01/app/oracle/oradata/ctl01.dbf’,‘/u01/app/oracle/oradata/ctl02.dbf’
*.db_block_size=8192
*.db_create_file_dest=‘/u01/app/oracle/oradata’
*.db_files=2000
*.db_name=‘cxldb’
*.db_unique_name=‘dgcxldb’
*.db_recovery_file_dest=‘/u01/app/oracle/reco’
*.db_recovery_file_dest_size=1t
*.diagnostic_dest=‘/u01/app/oracle’
*.enable_pluggable_database=true
*.log_archive_format=‘%t_%s_%r.dbf’
*.nls_language=‘AMERICAN’
*.nls_territory=‘AMERICA’
*.open_cursors=300
*.pga_aggregate_target=5g
*.processes=3000
*.recyclebin=‘OFF’
*.remote_login_passwordfile=‘exclusive’
*.sga_target=15g
*.undo_retention=10800
*.undo_tablespace=‘UNDOTBS1’
备库启动startup nomount;

在备库操作:

rman target sys/XWYY_2021@tns_cxldb auxiliary sys/XWYY_2021@tns_dgcxldb
run
{
allocate channel cl1 type disk;
allocate auxiliary channel c1 type disk;
duplicate target database for standby from active database nofilenamecheck dorecover;
release channel c1;
release channel cl1;
}

$ cat dg.sh
rman target sys/XWYY_2021@cxldb auxiliary sys/XWYY_2021@dgcxldb log /home/oracle/rman-date +%Y%m%d-%H%M.log <<EOF
run
{
allocate channel cl1 type disk;
allocate auxiliary channel c1 type disk;
duplicate target database for standby from active database nofilenamecheck dorecover;
release channel c1;
release channel cl1;
}
EOF

$ chmod +x dg.sh
$ nohup ./dg.sh &
查看日志:tail -f /home/oracle/rman-xxx.log

3.8主备库:联通性测试:

连主库:
sqlplus sys/XWYY_2021@tns_cxldb as sysdba
sqlplus sys/XWYY_2021@cxldb1 as sysdba
sqlplus sys/XWYY_2021@cxldb2 as sysdba
连备库:
sqlplus sys/XWYY_2021@tns_dgcxldb as sysdba

3.9主备库:调整参数

检查主库文件路径:cdb和pdb下都要检查
select name from v$datafile;
select * from v$logfile;
根据路径确定db_file_name_convert和log_file_name_convert对应方式。

主库修改参数:
alter system set LOG_ARCHIVE_DEST_1='LOCATION=USE_DB_RECOVERY_FILE_DEST VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=cxldb' scope=both sid='*';
alter system set LOG_ARCHIVE_DEST_2='service=tns_dgcxldb VALID_FOR=(online_logfiles,primary_role) DB_UNIQUE_NAME=dgcxldb' scope=both sid='*';
alter system set log_archive_config='dg_config=(cxldb,dgcxldb)' scope=both sid='*';
alter system set db_file_name_convert='/u01/app/oracle/oradata/DGCXLDB','+DATAC1/CXLDB' scope=spfile sid='*';
alter system set log_file_name_convert='/u01/app/oracle/oradata/DGCXLDB','+DATAC1/CXLDB' scope=spfile sid='*';
alter system set standby_file_management=auto scope=both sid='*';
alter system set fal_client='tns_cxldb' scope=both sid='*'; 
alter system set fal_server='tns_dgcxldb' scope=both sid='*';

备库修改参数:

alter system set LOG_ARCHIVE_DEST_1='LOCATION=USE_DB_RECOVERY_FILE_DEST VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=dgcxldb' scope=both sid='*';
alter system set LOG_ARCHIVE_DEST_2='service=tns_cxldb VALID_FOR=(online_logfiles,primary_role) DB_UNIQUE_NAME=cxldb' scope=both sid='*';
alter system set log_archive_config='dg_config=(cxldb,dgcxldb)' scope=both sid='*';
alter system set db_file_name_convert='+DATAC1/CXLDB','/u01/app/oracle/oradata/DGCXLDB' scope=spfile sid='*';
alter system set log_file_name_convert='+DATAC1/CXLDB','/u01/app/oracle/oradata/DGCXLDB' scope=spfile sid='*';
alter system set standby_file_management=auto scope=both sid='*';
alter system set fal_client='tns_dgcxldb' scope=both sid='*';
alter system set fal_server='tns_cxldb' scope=both sid='*';

四、ADG同步测试

4.1主备库:启用同步测试:


主库:
SQL> select database_role,open_mode from v$database;
DATABASE_ROLE	 OPEN_MODE
---------------- --------------------
PRIMARY  READ WRITE

主库pdb连接
conn sys/XWYY_2021@cxl
创建测试表空间
create tablespace test datafile '+DATAC1' size 100M autoextend on next 10M;
建个用户
create user test identified by test default tablespace test;
grant dba to test;
建个表
create table test(id int);
插入记录
insert into test values(1);
连接主库和备库检查记录:
conn test/test@cxl
select * from test;
conn test/test@dgcxl
select * from test;


检查:
SELECT
 (SELECT name FROM V$DATABASE
 ) name,
 (SELECT MAX (sequence#) FROM v$archived_log WHERE dest_id = 1
 ) Current_primary_seq,
 (SELECT MAX (sequence#)
 FROM v$archived_log
 WHERE TRUNC(next_time) > SYSDATE - 1
 AND dest_id = 2
 ) max_stby,
 (SELECT NVL (
 (SELECT MAX (sequence#) - MIN (sequence#)
 FROM v$archived_log
 WHERE TRUNC(next_time) > SYSDATE - 1
 AND dest_id = 2
 AND applied = 'NO'
 ), 0)
FROM DUAL
) "To be applied",
(
 (SELECT MAX (sequence#) FROM v$archived_log WHERE dest_id = 1
 ) -
 (SELECT MAX (sequence#) FROM v$archived_log WHERE dest_id = 2
 )) "To be Shipped"
FROM DUAL;


备库:
SQL> show pdbs;

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 PDB                            MOUNTED
         4 CXL                            MOUNTED
SQL> alter pluggable database all open instances=all;

Pluggable database altered.

SQL>  show pdbs;

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 PDB                            READ ONLY  NO
         4 CXL                            READ ONLY  NO
SQL> alter database recover managed standby database using current logfile disconnect;

Database altered.
SQL> select database_role,open_mode from v$database;
DATABASE_ROLE	 OPEN_MODE
---------------- --------------------
PHYSICAL STANDBY READ ONLY WITH APPLY


取消日志应用操作:alter database recover managed standby database cancel;
备库检查:select process,status,sequence#,thread# from v$managed_standby;
检查alert日志。
检查主备库状态:
set line 160
set wrap off
col inst_id for 99
select INST_ID, dbid,name,DB_UNIQUE_NAME,current_scn,protection_mode,database_role,force_logging,open_mode,switchover_status from gv$database;

备库:
SELECT 'Last Applied : ' Logs,
TO_CHAR(next_time,'DD-MON-YY:HH24:MI:SS') TIME,thread#,sequence#
FROM v$archived_log
WHERE sequence# =
(SELECT MAX(sequence#) FROM v$archived_log WHERE applied='YES'
)
UNION
SELECT 'Last Received : ' Logs,
TO_CHAR(next_time,'DD-MON-YY:HH24:MI:SS') TIME,thread#,sequence#
FROM v$archived_log
WHERE sequence# =
(SELECT MAX(sequence#) FROM v$archived_log );

4.2主备库:rman参数配置:

防止未应用归档却被删除:
CONFIGURE ARCHIVELOG DELETION POLICY TO APPLIED ON ALL STANDBY;
定期删除归档策略部署参考:
[oracle@racd2 ~]$ crontab -l
0 12 * * * /home/oracle/clear_archivelog.sh
 
[oracle@racd2 ~]$ cat clear_archivelog.sh
#!/bin/sh
source ~/.bash_profile
export ORACLE_SID=xxx1
/u01/app/oracle/product/19.9.0/db_1/bin/rman target / >> /home/oracle/clear_archive20190412.log << EOF
DELETE NOPROMPT ARCHIVELOG ALL COMPLETED BEFORE 'SYSDATE - 15';
exit
EOF
  • 0
    点赞
  • 6
    收藏
    觉得还不错? 一键收藏
  • 打赏
    打赏
  • 0
    评论
Oracle Active Data Guard(ADG)是一种高可用性和灾备解决方案,它在主数据库和辅助数据库之间提供实时数据复制和同步。以下是Oracle ADG部署的步骤: 1. 准备主数据库和辅助数据库 在主数据库和辅助数据库上安装Oracle数据库软件并创建数据库实例。确保主数据库和辅助数据库都启用了归档模式和Flashback Database功能。 2. 配置主数据库 在主数据库上启用归档模式和Flashback Database功能。创建一个备份控制文件,并将其复制到辅助数据库。 3. 配置辅助数据库 在辅助数据库上启用归档模式和Flashback Database功能。将备份控制文件复制到辅助数据库。创建一个备用控制文件,以便在主数据库故障时可以使用它来恢复。 4. 配置主数据库和辅助数据库之间的网络连接 确保主数据库和辅助数据库之间可以进行通信。可以使用Oracle Net Configuration Assistant来配置网络连接。 5. 配置数据保护 在主数据库上创建一个数据保护服务,并将其配置为使用辅助数据库作为备份。在辅助数据库上创建一个数据保护服务,并将其配置为使用主数据库作为备份。 6. 启动数据保护 在主数据库上启动数据保护服务。在辅助数据库上启动数据保护服务。确保数据保护服务正在运行并进行同步。 7. 测试故障转移 测试故障转移功能,确保在主数据库故障时可以自动切换到辅助数据库。测试过程中应该包括手动故障转移和自动故障转移。 以上是Oracle ADG部署的基本步骤。在实际部署过程中,还需要根据具体的环境和需求进行调整和优化。
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包

打赏作者

DBA狗剩儿

你的鼓励将是我创作的最大动力

¥1 ¥2 ¥4 ¥6 ¥10 ¥20
扫码支付:¥1
获取中
扫码支付

您的余额不足,请更换扫码支付或充值

打赏作者

实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值