搭建需求:
主库不停机的情况下完成adg的搭建，备库单实例仅作为实时数据库备份，还可供实时查询使用。备库不考虑切换成主库。
提示:检查主库已启用归档模式，所以可以不停机实现上述需求。
基本信息:（ip/库名/密码等敏感信息均已替换）
|
|
主库-Exadata x8-2一体机 rac |
备库-虚拟单机 |
|
服务
IP |
192.168.8.194 –scanip 192.168.8.192-vip1 192.168.8.193-vip2 |
192.168.8.197 |
|
db_name |
jycdb |
jycdb |
|
db_unique_name |
jycdb |
dgjycdb |
|
ORACLE_HOME |
/u01/app/oracle/product/19c/dbhome_1 |
/u01/app/oracle/product/19c/dbhome_1 |
|
数据路径 |
+DATAC1 |
/u01/app/oracle/oradata |
|
数据库版本 |
19.9 (pdb为jyc) |
19.9 |
备库操作系统配置和数据库安装建议:
客户提供的基本配置：
CPU 16核
内存 64G
硬盘 1T
操作系统和数据库补丁版本同主库一致。
操作系统：Oracle Linux7.9
数据库补丁：Oracle19.9
备库IP和主库建议同网段，并保证带宽足够。
磁盘空间规划：大小>=主库
swap虚拟内存：32G
数据库目录：/u01分配1T
数据库软件安装基本参数优化(此处已省略)。
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;
2.主库：增加standby_log（由于不做切换，此次在主库不加）
[oracle@rac1 ~]$ echo $ORACLE_SID
jycdb1
如果不是jycdb1
则[oracle@rac1 ~]$ export ORACLE_SID=jycdb1
[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
增加standbby log(可选，此项目不考虑切换所以不做此操作)
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.主库：添加db_unique_name
SQL> alter
system set db_unique_name='jycdb' scope=spfile sid='*';
4.备库：配置静态监听
备库检查：
SID_LIST_LISTENER
=
(SID_LIST
=
(SID_DESC
=
(GLOBAL_DBNAME
= dgjycdb)
(ORACLE_HOME
= /u01/app/oracle/product/19c/dbhome_1)
(SID_NAME
= dgjycdb)
)
)
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
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=jycdb1/2)
jycdb1 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.8.192)(PORT
= 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = jycdb)
)
)
jycdb2 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.8.193)(PORT
= 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = jycdb)
)
)
jyc=
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.8.194)(PORT
= 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = jyc)
)
)
dgjyc =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.8.197)(PORT
= 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = jyc)
)
)
tns_jycdb =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.8.194)(PORT
= 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = jycdb)
)
)
tns_dgjycdb =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.8.197)(PORT
= 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = dgjycdb)
)
)
6.主库：密码文件拷贝到备库
登录主库任意节点拷贝密码文件并传输到备库：
su - grid
asmcmd
pwcopy pwdjycdb.256.1024323395 /oracle/app/grid/orapwjycdb1
scp /oracle/app/grid/orapwjycdb1 oracle@192.168.8.197: /u01/app/oracle/product/19c/dbhome_1/dbs/orapwdgjycdb
注意密码文件的名称orapw
7.主备库：联通性测试：
连主库：
sqlplus sys/ABCD_2021@tns_jycdb as sysdba
sqlplus sys/ABCD_2021@jycdb1
as sysdba
sqlplus sys/ABCD_2021@jycdb2
as sysdba
连备库：
sqlplus sys/ABCD_2021@tns_dgjycdb
as sysdba
注意：如果密码文件错误但登录密码正确，将提示连接到空实例。如果密码错误，则提示密码错误。所以第6步很关键。
8.备库：dbca创建备库dgjycdb
传统方法参考：
主库创建参数文件：
create
pfile='/home/oracle/pfile.txt' from spfile;注意修改控制文件路径及相应目录。
*.audit_file_dest='/u01/app/oracle/admin/jycdb/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='jycdb'
*.db_unique_name='dgjycdb'
*.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/ABCD_2021@tns_jycdb auxiliary sys/ABCD_2021@tns_dgjycdb
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/ABCD_2021@jycdb auxiliary sys/ABCD_2021@dgjycdb 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
创建完成后注意检查路径下文件，确认是否预期结果：
ll /u01/app/oracle/reco
ll /u01/app/oracle/oradata
本来想用19c新特性试试，没想报错了，可能是由于主备内存差异太大，此次由于时间关系，没再多试，改用传统方法处理。
dbca -silent -createDuplicateDB \
-gdbName jycdb \
-sid dgjycdb \
-sysPassword ABCD_2021 \
-primaryDBConnectionString 192.168.8.194:1521/jycdb \
-nodelist dgjycdb \
-databaseConfigType SINGLE \
-storageType FS \
-createAsStandby -dbUniqueName dgjycdb \
-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
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=jycdb' scope=both sid='*';
alter system set
LOG_ARCHIVE_DEST_2='service=tns_dgjycdb
VALID_FOR=(online_logfiles,primary_role) DB_UNIQUE_NAME=dgjycdb' scope=both
sid='*';
alter system set
log_archive_config='dg_config=(jycdb,dgjycdb)' scope=both sid='*';
alter system set
db_file_name_convert='/u01/app/oracle/oradata/DGJYCDB','+DATAC1/JYCDB'
scope=spfile sid='*';
alter system set log_file_name_convert='/u01/app/oracle/oradata/DGJYCDB','+DATAC1/JYCDB'
scope=spfile sid='*';
alter system set
standby_file_management=auto scope=both sid='*';
alter system set fal_client='tns_jycdb'
scope=both sid='*';
alter system set fal_server='tns_dgjycdb'
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=dgjycdb' scope=both sid='*';
alter system set
LOG_ARCHIVE_DEST_2='service=tns_jycdb VALID_FOR=(online_logfiles,primary_role)
DB_UNIQUE_NAME=jycdb' scope=both sid='*';
alter system set
log_archive_config='dg_config=(jycdb,dgjycdb)' scope=both sid='*';
alter system set
db_file_name_convert='+DATAC1/JYCDB','/u01/app/oracle/oradata/DGJYCDB'
scope=spfile sid='*';
alter system set
log_file_name_convert='+DATAC1/JYCDB','/u01/app/oracle/oradata/DGJYCDB'
scope=spfile sid='*';
alter system set
standby_file_management=auto scope=both sid='*';
alter system set fal_client='tns_dgjycdb'
scope=both sid='*';
alter system set fal_server='tns_jycdb'
scope=both sid='*';
10.备库：增加standby_log（如果主库添加过，备库就自动建上）
SQL> select * from v$standby_log;
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;
11.主备库：启用同步测试：
备库：
SQL> startup mount;
SQL> show pdbs;
CON_ID CON_NAME
OPEN MODE RESTRICTED
---------- ------------------------------
---------- ----------
2 PDB$SEED
READ ONLY NO
3 PDB
MOUNTED
4 JYC
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 JYC
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;
备库检查日志同步情况参考脚本1：
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
);
主库：
SQL> select database_role,open_mode from
v$database;
DATABASE_ROLE OPEN_MODE
---------------- --------------------
PRIMARY
READ WRITE
主库pdb连接
conn
sys/ABCD_2021@jyc
创建测试表空间
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@jyc
select
* from test;
conn
test/test@dgjyc
select
* from test;
主备库检查脚本参考2:
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;
12.主备库：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
