提示:文章写完后,目录可以自动生成,如何生成可参考右边的帮助文档
文章目录
前言
提示:这里可以添加本文要记录的大概内容:
客户需求:要为主数据库提供一个只查询的备库,方案使用部署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内容
节点1和2都配置:
[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