前言

部署记录

一、adg是什么？

adg：主从复制，是用来建立一个和主数据库完全一样的数据库环境，称为从数据库；
主数据库一般是实时的业务数据库，从数据库的作用和使用场合一般有几个：一是作为后备数据库，主数据库服务器故障后，可切换到从数据库继续工作；二是可在从数据库作备份、数据统计等工作，这样不影响主数据库的性能,三、实现读写分离。

二、部署步骤

1.安装

代码如下（示例）：

1. 环境初始

主机名

#用于解析计算机名称和IP地址的映射关系
vi /etc/hosts

10.168.13.4 ecgis-xx23d001-hx16w

#针对本计算机的，是给计算机起的一个名字，是计算机的一个标识
vi /etc/sysconfig/network 

NETWORKING=yes
HOSTNAME=ecgis-xx23d001-hx16w
NOZEROCONF=yes

防火墙

sudo systemctl stop firewalld
sudo systemctl disable firewalld
sudo systemctl stop NetworkManager.service
sudo systemctl disable NetworkManager.service

配置内核参数

vi /etc/sysctl.conf 

fs.aio-max-nr = 1048576
fs.file-max = 6815744
kernel.shmmax = 269844168704
kernel.shmall = 215875334963
kernel.shmmni = 4096
kernel.msgmni = 2878
kernel.msgmax = 8192
kernel.msgmnb = 65536
kernel.sem = 250 32000 100 128
kernel.panic_on_oops = 1
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 = 1048576
net.ipv4.ipfrag_high_thresh = 16777216
net.ipv4.ipfrag_low_thresh = 15728640
net.ipv4.conf.all.rp_filter = 0
net.ipv4.conf.default.rp_filter = 0
net.ipv4.conf.all.rp_filter = 2
vm.swappiness = 10
vm.dirty_background_ratio = 3
vm.dirty_ratio = 40
vm.dirty_expire_centisecs = 500
vm.dirty_writeback_centisecs = 100
kernel.watchdog_thresh = 30

#修改后使之生效
sysctl -p

修改用户进程限制

vi  /etc/security/limits.conf

oracle              soft    nproc  2047
oracle              hard    nproc  16384
oracle              soft    nofile 1024
oracle              hard    nofile 65536
oracle              soft  stack  10240
oracle              hard  stack  32768
oracle              soft memlock 3145728
oracle              hard memlock 3145728

vi /etc/security/limits.d/20-nproc.conf

* soft nproc 1024
* -    nproc 16384

检查rpm依赖包

#检查依赖
sudo unset uninstall_rpm;for i in binutils compat-libstdc++-33  elfutils-libelf elfutils-libelf-devel elfutils-libelf-devel-static  gcc gcc-c++  glibc-devel glibc glibc-headers kernel-headers ksh libaio libaio-devel libgcc libgomp libstdc++ libstdc++-devel  make sysstat unixODBC unixODBC-devel  compat-gcc-34 compat-libstdc++-296 control-center glibc-common  libXp make openmotif22 setarch install compat-libcap1-1.10-1.x86_64 compat-libcap1-1.10-1.i686 smartmontools smartmontools net-tools cvuqdisk pdksh compat-libcap1 nfs-utils;do rpm -q $i &>/dev/null || uninstall_rpm="$uninstall_rpm $i";done ;[[ -z "$uninstall_rpm" ]] && echo -e "\nuninstall_rpm:\n\tOK.OK.OK" ||  echo -e "\nuninstall_rpm:\n\t$uninstall_rpm"

#安装扫描出缺少的依赖包
yum -y install compat-libstdc++-33 elfutils-libelf-devel elfutils-libelf-devel-static gcc gcc-c++ glibc-devel glibc-headers kernel-headers ksh libaio-devel libstdc++-devel sysstat unixODBC unixODBC-devel compat-gcc-34 compat-libstdc++-296 control-center libXp openmotif22 setarch install compat-libcap1-1.10-1.x86_64 compat-libcap1-1.10-1.i686 smartmontools smartmontools net-tools cvuqdisk pdksh compat-libcap1 nfs-utils

创建Oracle用户、组、安装目录

# 创建组
sudo groupadd -g 6001 oinstall
sudo groupadd -g 6002 dba
sudo groupadd -g 6003 oper
sudo groupadd -g 6004 backupdba
sudo groupadd -g 6005 dgdba
sudo groupadd -g 6006 kmdba

#创建Oracle用户
sudo useradd -u 6101 -g oinstall -G dba,oper,backupdba,dgdba,kmdba oracle

#设置oracle用户密码
passwd oracle

#安装目录及赋权
sudo mkdir -p /u01/app/oracle
sudo mkdir -p /u01/app/oracle/product/19/db_1
sudo chown -R oracle:oinstall /u01
sudo chmod -R 775 /u01

配置环境变量

# 切换到oracle用户
vi /home/oracle/.bash_profile

export TMP=/tmp
export LANG=en_US
export TMPDIR=$TMP
export ORACLE_HOSTNAME=$HOSTNAME
export ORACLE_UNQNAME=ucgeo3m
ORACLE_BASE=/u01/app/oracle;export ORACLE_BASE
ORACLE_HOME=$ORACLE_BASE/product/19/db_1;export ORACLE_HOME
ORACLE_SID=ucgeo3  ;export ORACLE_SID
ORACLE_TERM=xterm;export ORACLE_TERM
NLS_DATE_FORMAT="yyyy-mm-ddHH24:MI:SS"; export NLS_DATE_FORMAT
NLS_LANG=AMERICAN_AMERICA.ZHS16GBK;export NLS_LANG
PATH=$PATH:$HOME/bin:$ORACLE_HOME/bin;export PATH
THREADS_FLAG=native;export THREADS_FLAG
LD_LIBRARY_PATH=$ORACLE_HOME/lib;export LD_LIBRARY_PATH

#使环境变量生效
 source .bash_profile 

更改数据目录文件拥有者

chown oracle:oinstall /gisapp

创建数据存放目录

#oracle用户执行
cd /gisapp
mkdir data
mkdir archive

2. 静默安装

解压安装包

unzip xxx.zip -d $ORACLE_HOME

静默安装oracle

#/u01/app/oracle/product/19/db_1
./runInstaller -silent -ignorePrereqFailure -responseFile /home/oracle/db.rsp

#root下执行
sudo /u01/app/oraInventory/orainstRoot.sh
sudo /u01/app/oracle/product/19/db_1/root.sh

静默创建监听

netca -silent -responseFile $ORACLE_HOME/assistants/netca/netca.rsp

静默创建数据库(仅主库)

dbca -silent -createDatabase -responseFile /home/oracle/dbca.rsp

编辑网络服务名配置文件tnsnames.ora （主库和备库端都要做)

ucgeo2m =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 10.168.13.4)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = ucgeo2)
    )
  )

ucgeo2s =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 10.168.13.5)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = ucgeo2)
    )
  )

修改监听文件listener.ora，添加静态监听 （主库、备库都要做）

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (ORACLE_HOME = /u01/app/oracle/product/19/db_1)
      (SID_NAME = ucgeo2)
    )
  )

重启监听

lsrnctl stop

lsrnctl start

2.配置

代码如下（示例）：

主节点配置 sqlplus

alter system set db_unique_name='ucgeo2m' scope=spfile;

shutdown immediate;

startup mount;

#开启归档
alter database force logging;
alter database archivelog;
#参数设置
alter system set log_archive_config='dg_config=(ucgeo2m,ucgeo2s)';
alter system set log_archive_dest_1='location=/gisapp/archive lgwr valid_for=(all_logfiles,all_roles) db_unique_name=ucgeo2m';
alter system set log_archive_dest_2='service=ucgeo2s lgwr sync valid_for=(online_logfiles,primary_role) db_unique_name=ucgeo2s';


alter system set log_archive_max_processes=5;
alter system set standby_file_management=auto;
alter system set log_archive_dest_state_1='enable' scope=both sid='*';
alter system set log_archive_dest_state_2='enable' scope=both sid='*';
alter system set fal_server='ucgeo2s' scope=spfile sid='*';
alter system set fal_client='ucgeo2m' scope=spfile sid='*';

从内存创建pflie

#sqlplus / as sysdba

shutdown immediate;

startup
#重启后生成初始化参数文件
create pfile from memory;

在主库端把pfile拷贝给备库端的$ORACLE_HOME/dbs目录下

#初始化参数文件
scp /u01/app/oracle/product/19/db_1/dbs/initucgeo2.ora 192.168.19.22:/u01/app/oracle/product/19/db_1/dbs/
#密码文件
scp /u01/app/oracle/product/19/db_1/dbs/orapwucgeo2 192.168.19.22:/u01/app/oracle/product/19/db_1/dbs/

备库端进行修改初始化参数文件（下图红框部分主备网络服务名替换）

#修改从节点initucgeo2.ora
cd $ORACLE_HOME/dbs
vi initucgeo2.ora

在备库端手工创建所需的目录 （备库端做，不提前创建的话恢复时会报错！）

mkdir -p /gisapp/fast_recovery_area
mkdir -p /u01/app/oracle/admin/ucgeo3/adump/
mkdir -p  /gisapp/data/UCGEO3/controlfile
mkdir -p /gisapp/fast_recovery_area/UCGEO3/controlfile
mkdir -p /u01/app/oracle/diag/rdbms/ucgeo3m/ucgeo3/cdump

登录测试

#主
sqlplus sys/unicomgeo@ucgeo3m as sysdba
sqlplus sys/unicomgeo@ucgeo3s as sysdba

#备
sqlplus sys/unicomgeo@ucgeo3m as sysdba
sqlplus sys/unicomgeo@ucgeo3s as sysdba

利用RMAN在备库上恢复主库 （备库端做）

startup nomount;

rman target / auxiliary sys/unicomgeo@ucgeo2s

#分配通道 && 恢复数据文件，standby控制文件，standby日志组

run
{ 
allocate channel c1 type disk;
allocate channel c2 type disk;
allocate channel c3 type disk;
allocate AUXILIARY channel c4 type disk;
allocate AUXILIARY channel c5 type disk;
allocate AUXILIARY channel c6 type disk;
duplicate target database for standby from active database nofilenamecheck;
release channel c1;
release channel c2;
release channel c3;
release channel c4;
release channel c5;
release channel c6;
}

备库启动日志应用（启用备库前确认归档日志是否都已拷贝）

select open_mode from v$database;
select member from v$logfile;
select group#,THREAD#,bytes/1024/1024 from v$log;

//创建standby日志组，个数是原日志组个数+1
alter database add standby logfile group 11 size 200m;
alter database add standby logfile group 12 size 200m;
alter database add standby logfile group 13 size 200m;
alter database add standby logfile group 14 size 200m;

alter database recover managed standby database using current logfile disconnect from session;

set linesize 100

select process,status,client_process,thread#,sequence# from v$managed_standby;

#命令来进行日志切换

alter system switch logfile;

测试

#主库读写，备库只读
show pdbs; 

#启动pdb，并切换至对应pdb会话
alter pluggable database orclpdb open;
alter session set container=orclpdb;

#创建表包含数据库pdb中的所有对象，然后主备查看行数是否一致
create table kevin as select * from dba_objects;
select count(*) from kevin;

总结

提示：这里对文章进行总结：
例如：以上就是今天要讲的内容，本文仅仅简单介绍了pandas的使用，而pandas提供了大量能使我们快速便捷地处理数据的函数和方法。