一佳互联
虚拟化容器,大数据,DBA,中间件,监控。

Oracle 19c单实例搭建DG

30 12月
作者:admin|分类:DBA运维

Table of Contents

已有数据库配置

  

   [root@host ~]# cat /etc/hostname centos [oracle@host ~]$ id oracle uid=54321(oracle) gid=54321(oinstall) 组=54321(oinstall),54323(oper),54322(dba) [root@host ~]# su - oracle Last login: Tue Jul 12 16:47:25 CST 2022 on pts/0 [oracle@host ~]$ echo $ORACLE_HOME /oracle/product/12.2.0.1/db_1 [oracle@host ~]$ echo $ORACLE_BASE /oracle [oracle@host ~]$ echo $ORACLE_SID oracle [oracle@host ~]$ lsnrctl status LSNRCTL for Linux: Version 19.0.0.0.0 - Production on 13-JUL-2022 09:25:53 Copyright (c) 1991, 2019, Oracle. All rights reserved. Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=centos)(PORT=1521))) STATUS of the LISTENER ------------------------ Alias LISTENER Version TNSLSNR for Linux: Version 19.0.0.0.0 - Production Start Date 12-JUL-2022 09:21:51 Uptime 1 days 0 hr. 4 min. 2 sec Trace Level off Security ON: Local OS Authentication SNMP OFF Listener Parameter File /oracle/product/12.2.0.1/db_1/network/admin/listener.ora Listener Log File /oracle/diag/tnslsnr/host-192-168-254-32/listener/alert/log.xml Listening Endpoints Summary... (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=host-192-168-254-32)(PORT=1521))) (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521))) Services Summary... Service "oracle" has 1 instance(s). Instance "oracle", status READY, has 1 handler(s) for this service... Service "oracleXDB" has 1 instance(s). Instance "oracle", status READY, has 1 handler(s) for this service... The command completed successfully [oracle@host ~]$ sqlplus / as sysdba SQL*Plus: Release 19.0.0.0.0 - Production on Wed Jul 13 09:24:57 2022 Version 19.3.0.0.0 Copyright (c) 1982, 2019, Oracle. All rights reserved. Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production Version 19.3.0.0.0 
  
  

   SQL> select instance_name,status from v$instance; INSTANCE_NAME STATUS ---------------- ------------ oracle OPEN

准备工作

类型 IP 主机名 ORACLE_HOME ORACLE_SID ORACLE_UNQNAME
主库 192.168.254.32 centos /oracle/product/12.2.0.1/db_1 oracle oracle
备库 192.168.254.38 centos_dg /oracle/product/12.2.0.1/db_1 oracle oracle_dg

配置hosts文件

  

   # 主备库都需在操作 vim /etc/hosts 192.168.254.32 centos localhost.localdomain localhost6 localhost6.localdomain6 192.168.254.38 centos_dg localhost.localdomain localhost6 localhost6.localdomain6

创建备库用户与组

  

   groupadd -g 54321 oinstall groupadd -g 54322 dba groupadd -g 54323 oper useradd -u 54321 -g oinstall -G dba,oper oracle echo "oracle:oracle" | chpasswd id oracle uid=54321(oracle) gid=54321(oinstall) groups=54321(oinstall),54322(dba),54323(oper)

创建想关目录

  

   mkdir /oracle mkdir -p /oracle/product/12.2.0.1/db_1 mkdir /oradata mkdir /oracle/oraInventory mkdir /oracle/archive chown -R oracle:oinstall /oracle chown oracle:oinstall /oradata chmod 775 /oralce

关闭防火墙与selinux

防火墙

  

   systemctl stop firewalld.service systemctl disable firewalld.service systemctl status firewalld.service

selinux

  

   setenforce 0 vim /etc/selinux/config selinux=disabled

安装相关软件包

  

   yum install -y binutils compat-libcap1 compat-libstdc++-33 compat-libstdc++-33.i686 glibc glibc.i686 glibc-devel glibc-devel.i686 ksh libaio libaio.i686 libaio-devel libaio-devel.i686 libX11 libX11.i686 libXau libXau.i686 libXi libXi.i686 libXtst libXtst.i686 libgcc libgcc.i686 libstdc++ libstdc++.i686 libstdc++-devel libstdc++-devel.i686 libxcb libxcb.i686 make nfs-utils net-tools smartmontools sysstat unixODBC unixODBC-devel gcc gcc-c++ libXext libXext.i686 zlib-devel zlib-devel.i686

修改内核参数

  

   cat >> /etc/sysctl.conf <<! # add for oracle fs.file-max = 6815744 kernel.sem = 250 32000 100 128 kernel.shmmni = 4096 kernel.shmall = 1073741824 kernel.shmmax = 4398046511104 kernel.panic_on_oops = 1 net.core.rmem_default = 262144 net.core.rmem_max = 4194304 net.core.wmem_default = 262144 net.core.wmem_max = 1048576 net.ipv4.conf.all.rp_filter = 2 net.ipv4.conf.default.rp_filter = 2 fs.aio-max-nr = 1048576 net.ipv4.ip_local_port_range = 9000 65500 ! # 使之生效 /sbin/sysctl -p

资源限制

  

   cat >> /etc/security/limits.d/20-nproc.conf <<! * soft nproc 4096 root soft nproc unlimited oracle soft nofile 1024 oracle hard nofile 65536 oracle soft nproc 16384 oracle hard nproc 16384 oracle soft stack 10240 oracle hard stack 32768 oracle hard memlock 134217728 oracle soft memlock 134217728 !

配置环境变量

ORACLE_HOSTNAME与ORACLE_UNQNAM和主库不同

  

   su - oracle vim ~/.bash_profile export TMP=/tmp #日志存放位置 export TMPDIR=$TMP export ORACLE_HOSTNAME=centos_dg #主机名 export ORACLE_UNQNAME=oracle_dg #库名称 export ORACLE_BASE=/oracle export ORACLE_HOME=$ORACLE_BASE/product/12.2.0.1/db_1 export ORACLE_SID=oracle #库名称 export PATH=/usr/sbin:$PATH export PATH=$ORACLE_HOME/bin:$PATH export LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib export CLASSPATH=$ORACLE_HOME/jlib:$ORACLE_HOME/rdbms/jlib export PATH=/usr/sbin:$PATH export PATH=$ORACLE_HOME/bin:$PATH export LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib export CLASSPATH=$ORACLE_HOME/jlib:$ORACLE_HOME/rdbms/jlib

安装数据库

上传安装文件到备库

  

   chown oracle:oinstall LINUX.X64_193000_db_home.zip

解压压缩包到ORACLE_HOME

  

   unzip LINUX.X64_193000_db_home.zip $ORACLE_HOME

静默安装Oracle软件

db_install.rsp

oracle.install.responseFileVersion=/oracle/install/rspfmt_dbinstall_response_schema_v19.0.0
oracle.install.option=INSTALL_DB_SWONLY
UNIX_GROUP_NAME=oinstall
INVENTORY_LOCATION=/oracle/oraInventory
ORACLE_HOME=/oracle/product/12.2.0.1/db_1
ORACLE_BASE=/oracle
oracle.install.db.InstallEdition=EE
oracle.install.db.OSDBA_GROUP=dba
oracle.install.db.OSOPER_GROUP=oper
oracle.install.db.OSBACKUPDBA_GROUP=dba
oracle.install.db.OSDGDBA_GROUP=dba
oracle.install.db.OSKMDBA_GROUP=dba
oracle.install.db.OSRACDBA_GROUP=dba
oracle.install.db.config.starterdb.type=GENERAL_PURPOSE
oracle.install.db.config.starterdb.SID=oracle

su - oracle
cd $ORACLE_HOME
./runInstaller -silent -responseFile /tmp/db_install.rsp -ignorePrereq

# 安装结束后需要以root用户执行以下两个脚本
/oracle/oraInventory/orainstRoot.sh
/oracle/product/12.2.0.1/db_1/root.sh

配置监听与tnsnames.ora

主库监听

su - oracle
cd $ORACLE_HOME/network/admin
vim listener.ora

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

SID_LIST_LISTENER=
   (SID_LIST=
     (SID_DESC=
       (GLOBAL_DBNAME=oracle)
       (ORACLE_HOME=/oracle/product/12.2.0.1/db_1)
       (SID_NAME=oracle)
  )
 )

主库配置tnsnames.ora

su - oracle
cd $ORACLE_HOME/network/admin
vim tnsnames.ora

# tnsnames.ora Network Configuration File: /oracle/product/12.2.0.1/db_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.
LISTENER_ORACLE =
  (ADDRESS = (PROTOCOL = TCP)(HOST = centos)(PORT = 1521))

ORACLE =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = centos)(PORT = 1521))
  (CONNECT_DATA =
    (SERVER = DEDICATED)
    (SERVICE_NAME = oracle)
    )
  )

ORACLE_PRI =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = centos)(PORT = 1521))
  (CONNECT_DATA =
    (SERVICE_NAME = oracle)
    (SERVER = DEDICATED)
   )
 )

ORACLE_STY =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = centos_dg)(PORT = 1521))
  (CONNECT_DATA =
    (SERVICE_NAME = oracle)
    (SERVER = DEDICATED)
   )
 )

备库监听

su - oracle
cd $ORACLE_HOME/network/admin
vim listener.ora

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

SID_LIST_LISTENER=
   (SID_LIST=
     (SID_DESC=
       (GLOBAL_DBNAME=oracle)
       (ORACLE_HOME=/oracle/product/12.2.0.1/db_1)
       (SID_NAME=oracle)
  )
 )

备库配置tnsnames.ora

su - oracle
cd $ORACLE_HOME/network/admin
vim tnsnames.ora

# tnsnames.ora Network Configuration File: /oracle/product/12.2.0.1/db_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.
LISTENER_ORACLE =
  (ADDRESS = (PROTOCOL = TCP)(HOST = centos_dg)(PORT = 1521))

ORACLE =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = centos_dg)(PORT = 1521))
  (CONNECT_DATA =
    (SERVER = DEDICATED)
    (SERVICE_NAME = oracle)
    )
  )

ORACLE_PRI =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = centos)(PORT = 1521))
  (CONNECT_DATA =
    (SERVICE_NAME = oracle)
    (SERVER = DEDICATED)
   )
 )

ORACLE_STY =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = centos_dg)(PORT = 1521))
  (CONNECT_DATA =
    (SERVICE_NAME = oracle)
    (SERVER = DEDICATED)
   )
 )

主备库测试tns

$ tnsping ORACLE_PRI

TNS Ping Utility for Linux: Version 19.0.0.0.0 - Production on 13-JUL-2022 12:05:31

Copyright (c) 1997, 2019, Oracle.  All rights reserved.

Used parameter files:


Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = centos)(PORT = 1521)) (CONNECT_DATA = (SERVICE_NAME = oracle) (SERVER = DEDICAT
ED)))
OK (10 msec)

$ tnsping ORACLE_STY

TNS Ping Utility for Linux: Version 19.0.0.0.0 - Production on 13-JUL-2022 12:05:36

Copyright (c) 1997, 2019, Oracle.  All rights reserved.

Used parameter files:


Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = centos_dg)(PORT = 1521)) (CONNECT_DATA = (SERVICE_NAME = oracle) (SERVER = DEDI
CATED)))
OK (0 msec)

主库启用归档

  

   查看归档是否开启 archive log list 修改归档路径 alter system set log_archive_dest_1='location=/oracle/archive'; 启用归档 shutdown immediate; startup mount; alter database archivelog; alter database open; archive log list; alter system switch logfile;

主库开启强制日志

查看是否开启
select name,log_mode,force_logging from v$database;

开启强制日志
alter database force logging;

主库拷贝密码文件

su - oracle
mkdir /oradata/rman_backup
cd $ORACLE_HOME/dbs
orapwd file=orapworacle password=oracle force=y
cp orapworacle /oradata/rman_backup

主库创建standby redo log

  

   查看日志文件 select * from v$logfile; 查看当前日志组状态 select group#,sequence#,members,bytes/1024/1024,status from v$log order by GROUP#; 添加 standby redo log ALTER DATABASE ADD STANDBY LOGFILE GROUP 4 ('/oradata/ORACLE/redo04.log') size 1024M; ALTER DATABASE ADD STANDBY LOGFILE GROUP 5 ('/oradata/ORACLE/redo05.log') size 1024M; ALTER DATABASE ADD STANDBY LOGFILE GROUP 6 ('/oradata/ORACLE/redo06.log') size 1024M;

主库修改参数文件

备份参数文件
create pfile='/oradata/rman_backup//pfile.ora.1bak' from spfile;

更改db_unique_name
show parameter db_unique_name;
alter system set db_unique_name='oracle_pri' scope=spfile;
shutdown immediate;
startup
show parameter db_unique_name;

更改其他参数
alter system set LOG_ARCHIVE_CONFIG='DG_CONFIG=(oracle_pri,oracle_sty)' scope=both sid='*';
alter system set LOG_ARCHIVE_DEST_1='LOCATION=/oracle/archive VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=oracle_pri' scope=both sid='*';
alter system set LOG_ARCHIVE_DEST_2='SERVICE=oracle_sty LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=oracle_sty'scope=both sid='*';
alter system set FAL_SERVER='oracle_sty' scope=both sid='*';
alter system set fal_client='oracle_pri' scope=both sid='*';
alter system set DB_FILE_NAME_CONVERT='/oradata/ORACLE','/oradata/ORACLE' scope=spfile sid='*';
alter system set LOG_FILE_NAME_CONVERT='/oradata/ORACLE','/oradata/ORACLE' scope=spfile sid='*';

重启数据库
SQL> shutdown immediate;
SQL> startup;
生成新的参数文件
SQL> create pfile='/oradata/rman_backup/pfile.ora.2bak' from spfile;

主库配置最大性能模式

DataGuard 的三种数据保护模式：

MAXIMIZE PROTECTION（最大保护模式）：alter database set standby database to MAXIMIZE PROTECTION;

  • 最大数据保护与无数据分歧，LGWR 将同时传送到备用节点，在主节点事务确认之前，备用节点也必须完全收到日志数据。
  • 如果网络不好，引起 LGWR 不能传送数据，将引起严重的性能问题，导致主节点宕机。
  • 如果出现了什么故障导致 standby 数据库不可用的话，primary 数据库会被 shutdown。不适合在生产环境种应用。

MAXIMIZE AVAILABILITY（最大可用模式）：alter database set standby database to maximize availability;

  • 无数据丢失模式，允许数据分歧，允许异步传送。
  • 正常情况下运行在最大保护模式，在主节点与备用节点的网络断开或连接不正常时，自动切换到最大性能模式，主节点的操作还是可以继续的。
  • 在网络不好的情况下有较大的性能影响。
  • 对数据库性能有影响，数据丢失相对最大性能模式要小。
  • 不受备库影响。

MAXIMIZE PERFORMANCE（最大性能模式）：alter database set standby database to MAXIMIZE PERFORMANCE;

  • 最大性能模式是默认的保护模式。
  • 异步传送，无数据同步检查，可能丢失数据，但是能获得主节点的最大性能。
  • 在主数据库出现故障的情况下，可能有一些在主数据库上提交了的事务没有传输到备用数据库中。
  • Primary 不受影响备库影响。
查看模式（默认情况是最大性能模式）
select database_role,protection_mode,protection_level,open_mode from v$database;

DATABASE_ROLE	PROTECTION_MODE		PROTECTION_LEVEL	OPEN_MODE
-------------	-------------------	-------------------	----------
PRIMARY		MAXIMUM PERFORMANCE	MAXIMUM PERFORMANCE	READ WRITE

修改模式为最大性能模式
alter database set standby database to MAXIMIZE PERFORMANCE;  Database altered.

创建备库控制文件

alter database create standby controlfile as '/oradata/rman_backup/standby.ctl';

shutdown immediate;

主库RMAN备份数据库到备库

创建rman备份
rman target /

RMAN> run{
allocate channel c1 type disk maxpiecesize=100m;
allocate channel c2 type disk maxpiecesize=100m;
backup database filesperset 4 format '/oradata/rman_backup/ora_L0_%d_%T_%s_%p';
sql 'alter system archive log current';
sql 'alter system archive log current';
sql 'alter system archive log current';
backup archivelog all format '/oradata/rman_backup/arch_L0_%d_%T_%s_%p' delete input;
crosscheck backup;
crosscheck archivelog all;
}

把备份的文件上传到另一台服务器上面
scp -r rman_backup oracle@192.168.254.38:/oradata/

备库配置

创建文件夹

su - oracle  
mkdir -p /oracle/admin/oracle/adump 
mkdir -p /oracle/admin/oracle/dpdump 
mkdir -p /oracle/admin/oracle/pfile 
mkdir -p /oracle/admin/oracle/xdb_wallet 
mkdir -p $ORACLE_BASE/admin/oracle_sty/adump 
mkdir -p $ORACLE_BASE/admin/oracle_sty/dpdump 
mkdir /oracle/admin/oracle_sty/xdb_wallet
mkdir -p /oradata/ORACLE 
mkdir -p /oracle/archive/ORACLE

### 生成密码文件信息

mv /oradata/rman_backup/orapworacle $ORACLE_HOME/dbs/; 1s $ORACLE_HOME/dbs/

### 存放控制文件

主库查看控制文件位置
show parameter control_file;

备库存放控制文件
cp /oradata/rman_backup/standby.ctl /oradata/ORACLE/control01.ctl
cp /oradata/ORACLE/control01.ctl /oradata/ORACLE/control02.ctl

### 修改参数文件
```---修改之前的*.db_file_name_convert='/data/oracle/app/oracle/oradata/ORCL','/data/oracle/app/oracle/oradata/ORCL'
*.db_unique_name='orcl_pri'
*.fal_client='orcl_pri'
*.fal_server='orcl_sty'
*.log_archive_config='DG_CONFIG=(orcl_pri,orcl_sty)'
*.log_archive_dest_1='LOCATION=/data/oracle/archive VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=orcl_pri'
*.log_archive_dest_2='SERVICE=orcl_sty LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=orcl_sty'
*.log_archive_format='%t_%s_%r.dbf'
*.log_file_name_convert='/data/oracle/app/oracle/oradata/ORCL','/data/oracle/app/oracle/oradata/ORCL'
--修改后的*.db_file_name_convert='/data/oracle/app/oracle/oradata/ORCL','/data/oracle/app/oracle/oradata/ORCL'
*.db_unique_name='orcl_sty'
*.fal_client='orcl_sty'
*.fal_server='orcl_pri'
*.log_archive_config='DG_CONFIG=(orcl_pri,orcl_sty)'
*.log_archive_dest_1='LOCATION=/data/oracle/archive VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=orcl_sty'
*.log_archive_dest_2='SERVICE=orcl_pri LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=orcl_pri'
*.log_archive_format='%t_%s_%r.dbf'
*.log_file_name_convert='/data/oracle/app/oracle/oradata/ORCL','/data/oracle/app/oracle/oradata/ORCL

启动备库到nomount状态

sqlplus / as sysdba

startup pfile='/oracle/product/12.2.0.1/db_1/dbs/pfile.ora.2bak' nomount;

创建spfile
create spfile from pfile='/oracle/product/12.2.0.1/db_1/dbs/pfile.ora.2bak';

恢复控制文件

rman target /
RMAN>  restore controlfile from '/oradata/ORACLE/control01.ctl';

切换数据库到mount状态并恢复主库数据

rman target /
alter database mount;
catalog start with '/oradata/rman_backup/';

run {
allocate channel c1 type disk maxpiecesize=100m;
allocate channel c2 type disk maxpiecesize=100m;
restore database ;
}

查看结果
ls /oradata/ORACLE

重新启动备库

sqlplus / as sysdba
select status from v$instance;
startup nomount;
alter database mount standby database; 
show parameter spfile;

select open_mode from v$database;
OPEN_MODE
---------
MOUNTED

开启备库
alter database recover managed standby database cancel;
alter database open;
alter database recover managed standby database using current logfile disconnect ; 

开启备库应用日志：standby 端开启实时日志应用，会启动 MRP0 日志应用进程
alter database recover managed standby database using current logfile disconnect from session;

查看主备库同步的状态

主库上切一下日志
sqlplus / as sysdba
alter system switch logfile;

查看主备库GAP
select status,gap_status from v$archive_dest_status where dest_id=2;


备库查看进程
select process, sequence#, status, delay_mins from v$managed_standby;


查询数据库模式是否正常
select dbid,name,open_mode,current_scn,protection_mode,database_role,force_logging,open_mode,switchover_status from v$database;

主库:
1982918173  ORACLE  READ WRITE  2192632  MAXIMUM PERFORMANCE  PRIMARY           YES  READ WRITE  TO STANDBY

备库:
1982918173  ORACLE  MOUNTED     2192575  MAXIMUM PERFORMANCE  PHYSICAL STANDBY  YES  MOUNTED     NOT ALLOWED

更多文章推荐

历史上的今天
12月
30
浏览282 评论0
返回
目录 返回
首页
oracle 19c 单机ADG部署 Oracle 19c 一主一备ADG搭建
↑