本文讨论了oracle12c TMS ADG搭建的相关内容，包括Linux物理机初始化、oracle软件安装、自定义数据库安装、监听安装及配置、主备库相关设置与验证等操作步骤。关键要点包括：
-
Linux物理机初始化：关闭防火墙、selinux，配置域名解析、history、swap、关闭透明大页，添加用户和目录，挂盘，安装依赖，配置环境变量等。
-
oracle软件安装：解压软件包，修改配置文件，执行静默安装，用root用户执行脚本完成安装。
-
自定义数据库安装：生成密码文件，创建数据库，创建表空间，检查组件有效性，应用pfile文件等。
-
监听安装及配置：配置监听端口，验证监听状态，配置主备库监听文件和tns文件。
-
主备库设置与验证：主库开启归档日志、强制日志，添加备用日志；主备库配置监听、tnsnames.ora，验证网络连通性，设置互信参数；主备库修改pfile文件，主库设置模式；利用RMAN在备库恢复主库 。
环境规划
| Windows TMSQA | Linux TMSQA | ||
|---|---|---|---|
| db_name | tmsqa | tmsqa | |
| db_unique_name | TMSQA | TMSQA | |
| instance_name | tmsqa | tmsqa | |
| IP | 172.25.203.121 | 172.20.28.165 | |
| tns_name | TMSQA | TMSQA | |
| DATA路径 | /oradata/TMSQA/ | /data/oradata/TMSQA/ | |
| redo log路径 | /redolog/TMSQA/ | /data/redolog | |
| 归档盘 | /archlog/tmsqa/ | /data/archlog |
一、Linux物理机初始化
1.1 关闭防火墙
systemctl status firewalld
systemctl disable firewalld
systemctl stop firewalld
1.2 关闭selinux
vi /etc/selinux/config
SELINUX=disabled
1.3 配置域名解析
# 备库上 添加主备库ip及域名
vi /etc/hosts
172.25.203.121 KSENTPHY0004
# 主库
172.29.164.103 SGH1VTMS04
#主库上 添加主备库ip及域名
172.25.203.121 KSENTPHY0004
172.29.164.103 SGH1VTMS04
1.4 配置history
vi /etc/bashrc
export HISTFILESIZE=500000000
export HISTSIZE=1000000
export PROMPT_COMMAND="history -a"
export HISTTIMEFORMAT='%F %T '
export HISTTIMEFORMAT="%Y-%m-%d %H:%M:%S `whoami` "
source /etc/bashrc
1.5 配置swap
grep SwapTotal /proc/meminfo
# 从根分区中划分出16G用于创建swap分区 # dd if=/dev/zero of=/swapfile1 bs=1024 count=15728640 # 创建swap交换文件 # mkswap /swapfile1 # 激活swap交换文件 # swapon /swapfile1 # 设置开机自动启用swap分区 # echo '/swapfile1 swap swap defaults 0 0' >> /etc/fstab # cp /etc/sysctl.conf /etc/sysctl.conf.bak20221208 # sed -i 's/^vm.swappiness.*/vm.swappiness = 10/g' /etc/sysctl.conf # 重启服务器后检查swap分区大小 [root@oraclehost ~] # reboot [root@oraclehost ~] # free -g total used free shared buff/cache available Mem: 15 0 14 0 0 14 Swap: 14 0 14
1.6 关闭透明大页
# 关闭透明页(OEL 6.x/7.x) (开了大内存页要关闭)
cat /sys/kernel/mm/transparent_hugepage/defrag
[always] madvise never
cat /sys/kernel/mm/transparent_hugepage/enabled
[always] madvise never
vi /etc/rc.d/rc.local
if test -f /sys/kernel/mm/transparent_hugepage/enabled; then
echo never > /sys/kernel/mm/transparent_hugepage/enabled
fi
if test -f /sys/kernel/mm/transparent_hugepage/defrag; then
echo never > /sys/kernel/mm/transparent_hugepage/defrag
fi
chmod +x /etc/rc.d/rc.local
#重启服务器
reboot
cat /sys/kernel/mm/transparent_hugepage/defrag
always madvise [never]
cat /sys/kernel/mm/transparent_hugepage/enabled
always madvise [never]
1.7 配置初始化
## 添加Oracle相关用户
groupadd oinstall
groupadd dba
useradd -g oinstall -G dba oracle
echo 123456 |passwd --stdin oracle
/or
passwd oracle
## 创建Oracle相关目录
mkdir -p /oracle/software
mkdir -p /oradata /
mkdir -p / archlog
mkdir -p /redolog
### mkdir -p /swapfile1
chown -R oracle:oinstall /oracle
chown -R oracle:oinstall /oradata
chown -R oracle:oinstall /archlog
chown -R oracle:oinstall /redolog
## 挂盘
pvcreate /dev/sda
vgcreate datavg /dev/sda
lvcreate -L 8T -n oradata datavg
lvcreate -L 500G -n oralv datavg
lvcreate -L 1T -n archlv datavg
lvcreate -L 400G -n redolv datavg
### lvcreate -L 20G -n swap datavg
mkfs.xfs /dev/mapper/datavg-oradata
mkfs.xfs /dev/mapper/datavg-oralv
mkfs.xfs /dev/mapper/datavg-archlv
mkfs.xfs /dev/mapper/datavg-redolv
mount /dev/mapper/datavg-oradata /oradata
mount /dev/mapper/datavg-oralv /oracle
mount /dev/mapper/datavg-archlv /archlog
mount /dev/mapper/datavg-redolv /redolog
vi /etc/fstab
/dev/mapper/datavg-oradata /oradata xfs defaults 1 2
/dev/mapper/datavg-oralv /oracle xfs defaults 1 2
/dev/mapper/datavg-archlv /archlog xfs defaults 1 2
/dev/mapper/datavg-redolv /redolog xfs defaults 1 2
## nfs挂备份盘
mkdir /oracle_bak
vi /etc/fstab
mount -a
## 登录rclone的oracle备份服务器
# 注册信息
vi /etc/exports
172.25.203.121(rw,sync,no_wdelay,insecure_locks,no_root_squash)
# 加载生效
exportfs -ra
## 配置Oracle需要的系统参数优化
cat >> /etc/sysctl.conf <<EOF
fs.aio-max-nr = 1048576
fs.file-max = 6815744
kernel.shmall = 1073741824
kernel.shmmax = 4398046511104
kernel.shmmni = 4096
kernel.sem = 250 32000 100 128
kernel.panic_on_oops = 1
net.ipv4.conf.all.rp_filter = 2
net.ipv4.conf.default.rp_filter = 2
net.ipv4.ip_local_port_range = 9000 65500
net.core.rmem_default = 262144
net.core.rmem_max = 4194304
net.core.wmem_default = 262144
net.core.wmem_max = 1048586
EOF
sysctl -p
## 配置oracle用户系统资源限制,也可直接用*号表示所有用户
cat >> /etc/security/limits.conf <<EOF
oracle soft nofile 65536
oracle hard nofile 65536
oracle soft nproc 16384
oracle hard nproc 16384
oracle soft stack 10240
oracle hard stack 32768
oracle hard memlock 274726912
oracle soft memlock 274726912
EOF
1.8 安装Oracle所需依赖
yum -y install binutils compat-libcap1 compat-libstdc++-33 compat-libstdc++-33*.i686 elfutils-libelf-devel gcc gcc-c++ glibc*.i686 glibc glibc-devel glibc-devel*.i686 ksh libgcc*.i686 libgcc libstdc++ libstdc++*.i686 libstdc++-devel libstdc++-devel*.i686 libaio libaio*.i686 libaio-devel libaio-devel*.i686 make sysstat unixODBC unixODBC*.i686 unixODBC-devel unixODBC-devel*.i686 libXp unzip net-tools
1.9 配置环境变量
## 配置Oracle运行时的环境变量
cat >> /home/oracle/.bash_profile<<EOF
export LANG=en_US.UTF-8
export ORACLE_HOME=/data/oracle/12/db2
export NLS_LANG=american_america.AL32UTF8
export ORACLE_SID=tmsqa
export ORACLE_BASE=/data/oracle
alias sql='sqlplus / as sysdba'
export PATH=$ORACLE_HOME/bin:$ORACLE_HOME/OPatch:/bin:/sbin:/usr/bin:/usr/sbin
EOF
source /home/oracle/.bash_profile
env |grep PATH
二、oracle软件安装
- 以下若无明确说明，均以oracle用户登录系统执行操作
解压软件包
## 解压软件
cd /oracle/software
unzip V46095-01_1of2.zip -d /home/oracle/
unzip V46095-01_2of2.zip -d /home/oracle/
修改配置
cd /home/oracle/database/response/
mv db_install.rsp db_install.rsp_bak
egrep -v "^#|^$" db_install.rsp_bak >db_install.rsp
## 修改后的文件
vim db_install.rsp
oracle.install.responseFileVersion=/data/oracle/install/rspfmt_dbinstall_response_schema_v12.1.0
oracle.install.option=INSTALL_DB_SWONLY
ORACLE_HOSTNAME=xfzqavm188017
UNIX_GROUP_NAME=oinstall
INVENTORY_LOCATION=/data/oracle/oraInventory
SELECTED_LANGUAGES=en
ORACLE_HOME=/oracle/12/db2
ORACLE_BASE=/oracle
oracle.install.db.InstallEdition=EE
oracle.install.db.DBA_GROUP=dba
oracle.install.db.OPER_GROUP=dba
oracle.install.db.BACKUPDBA_GROUP=dba
oracle.install.db.DGDBA_GROUP=dba
oracle.install.db.KMDBA_GROUP=dba
oracle.install.db.rac.configurationType=
oracle.install.db.CLUSTER_NODES=
oracle.install.db.isRACOneInstall=
oracle.install.db.racOneServiceName=
oracle.install.db.rac.serverpoolName=
oracle.install.db.rac.serverpoolCardinality=
oracle.install.db.config.starterdb.type=GENERAL_PURPOSE
oracle.install.db.config.starterdb.globalDBName=tmsqa.yumcom
oracle.install.db.config.starterdb.SID=tmspa
oracle.install.db.ConfigureAsContainerDB=
oracle.install.db.config.PDBName=
oracle.install.db.config.starterdb.characterSet=AL32UTF8
oracle.install.db.config.starterdb.memoryOption=true
oracle.install.db.config.starterdb.memoryLimit=512
oracle.install.db.config.starterdb.installExampleSchemas=false
oracle.install.db.config.starterdb.password.ALL=manager0511
oracle.install.db.config.starterdb.password.SYS=
oracle.install.db.config.starterdb.password.SYSTEM=
oracle.install.db.config.starterdb.password.DBSNMP=
oracle.install.db.config.starterdb.password.PDBADMIN=
oracle.install.db.config.starterdb.managementOption=
oracle.install.db.config.starterdb.omsHost=
oracle.install.db.config.starterdb.omsPort=
oracle.install.db.config.starterdb.emAdminUser=
oracle.install.db.config.starterdb.emAdminPassword=
oracle.install.db.config.starterdb.enableRecovery=
oracle.install.db.config.starterdb.storageType=
oracle.install.db.config.starterdb.fileSystemStorage.dataLocation=
oracle.install.db.config.starterdb.fileSystemStorage.recoveryLocation=
oracle.install.db.config.asm.diskGroup=
oracle.install.db.config.asm.ASMSNMPPassword=
MYORACLESUPPORT_USERNAME=
MYORACLESUPPORT_PASSWORD=
SECURITY_UPDATES_VIA_MYORACLESUPPORT=false
DECLINE_SECURITY_UPDATES=true
PROXY_HOST=
PROXY_PORT=
PROXY_USER=
PROXY_PWD=
COLLECTOR_SUPPORTHUB_URL=
## 执行静默安装，如果只是warning可以先忽略，如果是error报错则要看对应输出的日志信息
/home/oracle/database/runInstaller -silent -responseFile /home/oracle/database/response/db_install.rsp -ignorePrereq
## 用root用户执行以上2个脚本，完成软件安装
sh /data/oracle/oraInventory/orainstRoot.sh
sh /data/oracle/12/db2/root.sh
三、自定义数据库安装
opatch lspatches
opatch lsinventory
echo $ORACLE_SID
echo $ORACLE_HOME
mkdir -p /oradata/fast_recovery_area
mkdir -p /oradata/TMSQA
mkdir -p /redolog/TMSQA
# 生成sys密码
orapwd file=$ORACLE_HOME/dbs/orapwtmsqa force=y password=manager0511 ignorecase=n
vi $ORACLE_HOME/dbs/inittmsqa.ora
*.audit_file_dest='/data/oracle/audit/tmsqa'
*.control_files=("/data/oradata/TMSQA/control01.ctl","/data/oradata/TMSQA/control02.ctl")
*.deferred_segment_creation=false
*.audit_trail='none'
*.compatible='12.1.0.2.0'
*.db_block_size=16384
*.db_name='TMSQA'
*.db_unique_name='TMSQA'
*.diagnostic_dest='/data/oracle'
*.pga_aggregate_target=10240m
*.processes=4000
*.sga_target=13107m
*.undo_retention=1800
*.undo_tablespace='UNDOTBS1'
*.max_string_size='EXTENDED'
*.cursor_sharing=EXACT
*.nls_length_semantics='CHAR'
*.open_cursors=2000
*.processes=4000
*.sessions=6024
*.parallel_min_servers=32
*.parallel_max_servers=320
*.parallel_servers_target=128
export ORACLE_SID=tmsqa
SQL> sqlplus /nolog
SQL> CONNECT / AS SYSDBA
SQL> startup nomount pfile='/oracle/12/db2/dbs/inittmsqa.ora';
CREATE DATABASE TMSQA
USER SYS IDENTIFIED BY "manager0511"
USER SYSTEM IDENTIFIED BY "manager0511"
LOGFILE GROUP 1 ('/redolog/TMSQA/redo01a.log','/redolog/TMSQA/redo01b.log') SIZE 1024M, GROUP 2 ('/redolog/TMSQA/redo02a.log','/redolog/TMSQA/redo02b.log') SIZE 1024M,
GROUP 3 ('/redolog/TMSQA/redo03a.log','/redolog/TMSQA/redo03b.log') SIZE 1024M
MAXLOGFILES 5
MAXLOGMEMBERS 5
MAXLOGHISTORY 1
MAXDATAFILES 100
MAXINSTANCES 1
CHARACTER SET AL32UTF8
NATIONAL CHARACTER SET AL16UTF16
DATAFILE '/oradata/TMSQA/system01.dbf' SIZE 2048M AUTOEXTEND ON NEXT 10M MAXSIZE UNLIMITED
EXTENT MANAGEMENT LOCAL
SYSAUX DATAFILE '/oradata/TMSQA/sysaux01.dbf' SIZE 2048M AUTOEXTEND ON NEXT 10M MAXSIZE UNLIMITED
DEFAULT TABLESPACE users
DATAFILE '/oradata/TMSQA/users01.dbf'
SIZE 2048M AUTOEXTEND ON NEXT 10M MAXSIZE UNLIMITED
DEFAULT TEMPORARY TABLESPACE TEMP
TEMPFILE '/oradata/TMSQA/temp01.dbf'
SIZE 62G AUTOEXTEND ON NEXT 500M MAXSIZE UNLIMITED,
'/oradata/TMSQA/temp02.dbf'
SIZE 62G AUTOEXTEND ON NEXT 500M MAXSIZE UNLIMITED
UNDO TABLESPACE UNDOTBS1
DATAFILE '/oradata/TMSQA/undotbs01.dbf'
SIZE 62G AUTOEXTEND ON NEXT 500M MAXSIZE UNLIMITED,
'/oradata/TMSQA/undotbs02.dbf'
SIZE 62G AUTOEXTEND ON NEXT 500M MAXSIZE UNLIMITED;
SQL> CREATE TABLESPACE WWFDATA
DATAFILE
'/oradata/TMSQA/WWFDATA01_O12CR1.dbf' SIZE 62G AUTOEXTEND ON NEXT 1G MAXSIZE UNLIMITED,
'/oradata/TMSQA/WWFDATA02_O12CR1.dbf' SIZE 62G AUTOEXTEND ON NEXT 1G MAXSIZE UNLIMITED,
'/oradata/TMSQA/WWFDATA03_O12CR1.dbf' SIZE 62G AUTOEXTEND ON NEXT 1G MAXSIZE UNLIMITED
EXTENT MANAGEMENT LOCAL AUTOALLOCATE BLOCKSIZE 16k;
## 查看创建的数据库
SQL> SELECT name, dbid, created, open_mode FROM V$DATABASE;
SQL> select count(*) from dba_objects where status<>'VALID';
-- 创建数据字典和PL/SQL包
sqlplus -S / as sysdba <<EOF
@?/rdbms/admin/catalog.sql
@?/rdbms/admin/catproc.sql
@?/rdbms/admin/utlrp.sql
disconnect;
connect system/manager0511
@?/sqlplus/admin/pupbld.sql
exit;
EOF
# 检查数据库组件的有效性
SQL > SELECT comp_id,comp_name,version,status FROM dba_registry;
# 应用pfile文件
SQL> SELECT name, value FROM v$parameter WHERE name IN ('spfile', 'config_file');
SQL> shutdown immediate;
SQL> create spfile from pfile='/oracle/12/db2/dbs/inittmsqa.ora';
SQL> startup
# 查看报错
cd /oracle/diag/rdbms/tmsqa/tmsqa/trace/
# 查看redo组数量
SELECT GROUP#, THREAD#, SEQUENCE#, BYTES/1024/1024 AS log_size_MB, MEMBERS, STATUS
FROM V$LOG;
SELECT t1.GROUP#, t1.MEMBER, t2.BYTES/1024/1024 AS log_size_MB, t2.STATUS
FROM V$LOGFILE t1, V$LOG t2
WHERE t1.GROUP# = t2.GROUP#;
## 数据库安装
## 备份老的建库配置文件
cd /home/oracle/database/response
mv dbca.rsp dbca.rsp.bak
## 直接编辑生成一个新的建库配置文件
vi dbca.rsp
[GENERAL]
RESPONSEFILE_VERSION = "12.1.0"
OPERATION_TYPE = "createDatabase"
[CREATEDATABASE]
GDBNAME = "TMSQA"
SID = "tmsqa"
TEMPLATENAME = "General_Purpose.dbc"
SYSPASSWORD = "manager0511"
SYSTEMPASSWORD = "manager0511"
DATAFILEDESTINATION = "/oradata"
CHARACTERSET = "AL32UTF8"
TOTALMEMORY = "204800"
## 刷新环境变量配置，使配置生效
source ~/.bash_profile
## 执行dbca静默建库操作
dbca -silent -responseFile /home/oracle/database/response/dbca.rsp
## 验证数据库登录
SQL> sqlplus / as sysdba
SQL> select status,INSTANCE_NAME from v$instance;
STATUS INSTANCE_NAME
------------ ----------------
OPEN tmsqa
## 查看创建的数据库
SQL> SELECT name, dbid, created, open_mode FROM V$DATABASE;
NAME DBID CREATED OPEN_MODE
--------- ---------- ------------------ --------------------
TMSQA 1313632979 0 1-APR-25 READ WRITE
四、监听安装及配置
- 数据库构建完成后，需要配置监听端口，才可以让外部访问，监听端口默认为1521端口，直接使用默认配置即可。
netca -silent -responseFile /home/oracle/database/response/dbca.rsp
正在对命令行参数进行语法分析:
参数"silent" = true
参数"responsefile" = /data/oracle/database/response/dbca.rsp
完成对命令行参数进行语法分析。
Oracle Net Services 配置:
完成概要文件配置。
成功完成 Oracle Net Services 配置。退出代码是0
## 验证监听状态（默认情况下需要几十秒才能发现实例，也可以手动注册实例到监听）
lsnrctl start
lsnrctl status
LSNRCTL for Linux: Version 12.1.0.2.0 - Production on 14-NOV-2024 15:45:03
Copyright (c) 1991, 2014, Oracle. All rights reserved.
Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 12.1.0.2.0 - Production
Start Date 14-NOV-2024 15:44:54
Uptime 0 days 0 hr. 0 min. 8 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Log File /data/oracle/diag/tnslsnr/oracle/listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=oracle)(PORT=1521)))
The listener supports no services
The command completed successfully
4.1 主备库监听文件配置
# windows主库：%ORACLE_HOME%\network\admin
TMSQA =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 172.29.164.103)(PORT = 1521))
)
)
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = TMSQA)
(ORACLE_HOME = xxx)
(SID_NAME = tmspa)
)
)
# 备库
vi /oracle/12/db2/network/admin/listener.ora
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 172.25.203.121)(PORT = 1521))
)
)
#
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = TMSQA)
(ORACLE_HOME = /oracle/12/db2)
(SID_NAME = tmsqa)
)
)
# --启动监听
lsnrctl start
4.2 主备库tns文件配置
# windows TMSQA库
%ORACLE_HOME%\network\admin/tnsnames.ora
TMSQA =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = SGH1VTMS04)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = TMSQA)
)
)
# linux TMSQA库
vi $ORACLE_HOME/network/admin/tnsnames.ora
TMSQA =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 172.25.203.121)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = TMSQA)
)
)
备库修改sys密码
alter user sys identified by "xxx"
Windows 主库准备
1. 开启归档日志
## 主库查看数据库是否启用归档模式
SQL> select log_mode from v$database;
LOG_MODE
------------
ARCHIVELOG
## 关闭数据库
SQL> shutdown immediate
## 开启挂载
SQL> startup mount
## 指定归档目录
SQL> ALTER SYSTEM SET log_archive_dest_1='LOCATION=/archlog/tmsqa' SCOPE=BOTH;
## 开启归档模式
SQL> alter database archivelog;
## 打开数据库
SQL> alter database open;
## 查看归档位置
SQL> archive log list;
# 切换在线日志，验证归档是否正常
SQL> alter system switch logfile;
2. 开启强制日志
SQL> alter database force logging;
Database altered.
SQL> select force_logging from v$database;
FORCE_LOGGING
--------------------------------------------------------------------------------
YES
3. 查询redo log信息
select thread#,groups from v$thread;
THREAD# GROUPS
---------- ----------
1 3
# 查询redo日志大小
SQL> select group#,thread#,sequence#,bytes/1024/1024 mb from v$Log;
GROUP# THREAD# SEQUENCE# MB
---------- ---------- ---------- ----------
1 1 31 200
2 1 29 200
3 1 30 200
# 查询redo日志存放路径
SQL> col member for a50
SQL> select member from v$Logfile;
MEMBER
--------------------------------------------------------------------------------
/u01/oracle/oradata/crmdb/redo03.log
/u01/oracle/oradata/crmdb/redo02.log
/u01/oracle/oradata/crmdb/redo01.log
# 根据redo日志数量,添加N+1个备用日志(3+1),大小与原redo日志大小一样,否则会报错
alter database add standby logfile GROUP 41 '/oradata/redo/redo41.log' SIZE 2048M;
alter database add standby logfile GROUP 42 '/oradata/redo/redo42.log' SIZE 2048M;
.......
# 确认备用日志添加成功
SQL> SELECT THREAD#,GROUP#,bytes/1024/1024 MB,ARCHIVED,STATUS FROM V$STANDBY_LOG;
THREAD# GROUP# MB ARCHIVED STATUS
---------- ---------- ---------- --------- ------------------------------
1 4 50 YES UNASSIGNED
1 5 50 YES UNASSIGNED
1 6 50 YES UNASSIGNED
1 7 50 YES UNASSIGNED
###
SQL> set pagesize 100
SQL> col member for a60
SQL> select group #, member from v$logfile order by group #
修改监听文件(主从都一样配置)
cmd: echo $ORACLE_HOME
主库：/u01/oracle/diag/tnslsnr/oracle/listener
# 主库监听配置（Windows）
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 主库主机名/IP)(PORT = 1521))
)
)
# 添加备库连接别名（可选，用于主库与备库通信）
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = 备库全局数据库名)
(ORACLE_HOME = 主库Oracle安装路径)
(SID_NAME = 备库实例名)
)
)
# 2. 备库（Linux）监听配置
2.1编辑备库的listener.ora
# 备库监听配置（Linux）
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 备库主机名/IP)(PORT = 1521))
)
)
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = 主库全局数据库名)
(ORACLE_HOME = 备库Oracle安装路径)
(SID_NAME = 主库实例名)
)
)
# 2.2 验证备库监听状态
# 分别重启监听
lsnrctl reload
lsnrctl status
# 3. 配置 tnsnames.ora 实现互信
在主库和备库的 tnsnames.ora 中添加对方的连接别名：
# 主库的 tnsnames.ora
主库别名 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 主库主机名/IP)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = 主库全局数据库名)
)
)
备库别名 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 备库主机名/IP)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = 备库全局数据库名)
)
)
# 备库的 tnsnames.ora
主库别名 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 主库主机名/IP)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = 主库全局数据库名)
)
)
备库别名 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 备库主机名/IP)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = 备库全局数据库名)
)
)
4. 验证网络连通性
4.1 使用 tnsping 测试连接
# 主库测试备库连接
tnsping 备库别名
# 备库测试主库连接
tnsping 主库别名
# 主库登录备库
sqlplus /@备库别名
# 备库登录主库
sqlplus /@主库别名
5. 防火墙与安全组配置
确保主库和备库的防火墙或云服务器安全组开放以下端口：
- TCP 1521：Oracle 默认监听端口
- TCP 1522：如果使用 DGMGRL 工具（可选）
6. 主备库互信验证
在 DG 配置过程中，主库和备库需通过以下参数建立信任：
-- 主库设置
ALTER SYSTEM SET LOG_ARCHIVE_DEST_2='SERVICE=备库别名 ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=备库唯一名称';
-- 备库设置
ALTER SYSTEM SET LOG_ARCHIVE_DEST_2='SERVICE=主库别名 ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=主库唯一名称';
注意事项
1. 主机名解析：确保主备库能通过主机名或 IP 互相解析（可配置 /etc/hosts 或 DNS）。
2. ORACLE_HOME 路径：监听文件中的 ORACLE_HOME 需与实际安装路径一致。
3. DG 角色配置：在 DG 配置完成后，使用 DGMGRL 工具验证主备角色和同步状态。
通过以上步骤，主备库的监听文件和网络配置将确保 DG 环境的稳定通信。
主库修改pfile文件
SQL> create pfile='$ORACLE_HOME/dbs/initcrmdb.ora' from spfile;
tmsprd.__data_transfer_cache_size=0
tmsprd.__db_cache_size=117574729728
tmsprd.__java_pool_size=3758096384
tmsprd.__large_pool_size=5368709120
tmsprd.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment
tmsprd.__pga_aggregate_target=53687091200
tmsprd.__sga_target=193273528320
tmsprd.__shared_io_pool_size=536870912
tmsprd.__shared_pool_size=64424509440
tmsprd.__streams_pool_size=1073741824
*.audit_file_dest='/u01/app/oracle/admin/tmsprd/adump'
*.audit_trail='DB'
*.compatible='12.1.0.2.0'
*.control_files='/oradata/tmsprd/control01n.dbf','/oradata/fast_recovery_area/tmsprd/control02n.ctl' #Restore Controlfile
*.cursor_sharing='EXACT'
*.db_block_size=16384
*.db_create_file_dest='/oradata'
*.db_domain=''
*.db_name='tmsprd'
*.db_recovery_file_dest='/oradata/fast_recovery_area'
*.db_recovery_file_dest_size=2684354560000
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=tmsprdXDB)'
*.local_listener='(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=172.25.203.137)(PORT=1521)))'
*.log_archive_dest_1='location=/oradata/archive'
*.max_string_size='EXTENDED'
*.open_cursors=5000
*.pga_aggregate_limit=107374182400
*.pga_aggregate_target=53687091200
*.processes=4000
*.remote_login_passwordfile='EXCLUSIVE'
*.result_cache_max_size=0
*.service_names='TMSPRD'
*.sessions=1105
*.sga_max_size=214748364800
*.sga_target=193273528320
*.shared_pool_size=64424509440
*.undo_tablespace='UNDOTBS1'
*.workarea_size_policy='AUTO'
## 以下为新增
*.db_unique_name='primary' # 名称必须唯一
*.log_archive_config='dg_config=(primary,standby)'
*.log_archive_dest_1='location=/oradata/archive valid_for=(all_logfiles,all_roles) db_unique_name=primary' # 设置本地归档位置
# 设置远程归档日志位置及同步方式为lgwr affirm sync
*.log_archive_dest_2='service=standby valid_for=(online_logfiles,primary_role) lgwr affirm sync db_unique_name=standby'
*.log_archive_dest_state_1=enable #启用
*.log_archive_dest_state_2=enable
*.standby_file_management='auto' #允许数据文件自动同步添加
*.fal_server='standby' #获取归档日志的服务为从库standby
*.fal_client='primary' #获取归档日志的客户端为主库primary
*.log_file_name_convert='/oradata/redo/','/redolog/tmsprd/'
*.db_file_name_convert='/oradata/tmsprd/','/oradata/tmsprd/'
# 应用pfile文件
SQL>shutdown immediate;
SQL>create spfile from pfile='$ORACLE_HOME/dbs/initcrmdb.ora';
SQL>startup
从库修改pfile文件
SQL> create pfile='$ORACLE_HOME/dbs/initcrmdb.ora' from spfile;
tmsprd.__data_transfer_cache_size=0
tmsprd.__db_cache_size=117574729728
tmsprd.__java_pool_size=3758096384
tmsprd.__large_pool_size=3221225472
tmsprd.__oracle_base='/oracle'#ORACLE_BASE set from environment
tmsprd.__pga_aggregate_target=53687091200
tmsprd.__sga_target=161061273600
tmsprd.__shared_io_pool_size=536870912
tmsprd.__shared_pool_size=17179869184
tmsprd.__streams_pool_size=0
*.audit_file_dest='/oracle/admin/tmsprd/adump'
*.audit_trail='db'
*.compatible='12.1.0.2.0'
*.control_files='/oradata/tmsprd/control01.ctl','/oracle/fast_recovery_area/tmsprd/control02.ctl'
*.db_block_size=8192
*.db_domain=''
*.db_name='tmsprd'
*.db_recovery_file_dest='/oracle/fast_recovery_area'
*.db_recovery_file_dest_size=4560m
*.diagnostic_dest='/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=tmsprdXDB)'
*.open_cursors=300
*.pga_aggregate_target=51200m
*.processes=300
*.remote_login_passwordfile='EXCLUSIVE'
*.sga_target=153600m
*.undo_tablespace='UNDOTBS1'
*.db_unique_name='STANDBY'
*.log_archive_config='dg_config=(PRIMARY,STANDBY)'
*.log_archive_dest_1='location=/archlog/tmsprd valid_for=(all_logfiles,all_roles) db_unique_name=STANDBY'
*.log_archive_dest_2='service=PRIMARY valid_for=(online_logfiles,PRIMARY_role) lgwr affirm sync db_unique_name=PRIMARY'
*.log_archive_dest_state_1=enable
*.log_archive_dest_state_2=enable
*.STANDBY_file_management='auto'
*.fal_server='PRIMARY'
*.fal_client='STANDBY'
*.log_file_name_convert='/oradata/redo/','/redolog/tmsprd/'
*.db_file_name_convert='/oradata/tmsprd/','/oradata/tmsprd/'
# 应用pfile文件
SQL>shutdown immediate;
SQL>create spfile from pfile='$ORACLE_HOME/dbs/initcrmdb.ora';
SQL>startup nomount
主库模式改为 MAXIMUM AVAILABILITY
# 不需要关闭数据库,默认data guard模式为 Maximize Performance
SQL>alter database set standby database to maximize availability;
# 查看模式
SQL> select database_role,protection_mode,protection_level from v$database;
DATABASE_ROLE PROTECTION_MODE PROTECTION_LEVEL
-------------------- --------------------------- ---------------------------
PRIMARY MAXIMUM AVAILABILITY MAXIMUM AVAILABILITY
利用RMAN 在备库上恢复主库（主库端做）
[oracle@centos-5 ~]$ rman target sys/manager0511@primary / auxiliary sys/manager0511@standby
Recovery Manager: Release 12.2.0.1.0 - Production on Sat Jul 24 20:44:15 2021
Copyright (c) 1982, 2017, Oracle and/or its affiliates. All rights reserved.
connected to target database: CRMDB (DBID=4187303499)
connected to auxiliary database: CRMDB (DBID=4187303499)
# 执行同步
RMAN>duplicate target database for standby from active database nofilenamecheck;
