物理备库搭建记录—实用篇

来源：恒创科技编辑：恒创科技编辑部

2023-12-06 11:49:59

报告课题：物理备库搭建记录—实用篇

1、环境介绍

物理备库搭建记录—实用篇_hive

2、软件安装

先在备库安装数据库软件.

2.1、加快SSH登陆

# cp /etc/ssh/sshd_config /etc/ssh/sshd_config_`date +"%Y%m%d_%H%M%S"` && sed -i '/#LoginGraceTime 2m/ s/#LoginGraceTime 2m/LoginGraceTime 0/' /etc/ssh/sshd_config && grep LoginGraceTime /etc/ssh/sshd_config
# cp /etc/ssh/sshd_config /etc/ssh/sshd_config_`date +"%Y%m%d_%H%M%S"` && sed -i '/#UseDNS yes/ s/#UseDNS yes/UseDNS no/' /etc/ssh/sshd_config && grep UseDNS /etc/ssh/sshd_config

2.2、修改主机名

# hostnamectl set-hostname univdg

2.3、添加域名解析

# vi /etc/hosts
# cat /etc/hosts
127.0.0.1   localhost localhost.localdomain localhost4 localhost4.localdomain4
::1         localhost localhost.localdomain localhost6 localhost6.localdomain6
192.168.11.218 univdg

2.4、关闭防火墙

# systemctl stop firewalld.service
# systemctl disable firewalld.service

2.5、禁用selinux

# sed -i 's/SELINUX=enforcing/SELINUX=disabled/g' /etc/selinux/config

2.6、修改内核参数

# cat <<EOF>>/etc/sysctl.conf
kernel.shmmax = 68719476735
kernel.shmmni = 4096
kernel.sem = 250 32000 100 128
net.core.rmem_default = 262144
net.core.rmem_max = 4194304
net.core.wmem_default = 262144
net.core.wmem_max = 1048586
fs.file-max = 6815744

kernel.shmall = 16777216
net.ipv4.ip_local_port_range = 9000 65500
net.ipv4.tcp_tw_reuse = 1
net.core.somaxconn = 65500
net.ipv4.tcp_max_orphans = 262144
net.ipv4.tcp_max_syn_backlog = 262144
net.ipv4.tcp_synack_retries = 2
net.ipv4.tcp_syn_retries = 1
net.ipv4.tcp_fin_timeout = 1
net.ipv4.tcp_keepalive_time = 30
net.ipv4.tcp_keepalive_probes = 6
net.ipv4.tcp_keepalive_intvl = 5
net.ipv4.tcp_timestamps = 0
fs.aio-max-nr = 1048576
EOF

# /sbin/sysctl -p

2.7、创建用户与组

groupadd oinstall
groupadd dba
groupadd oper
useradd -g oinstall -G dba,oper oracle
echo oracle |passwd --stdin oracle

2.8、创建目录

[oracle@univdg ~]$ mkdir -p /home/oracle/app/oracle/product/11.2.0/db_1
[oracle@univdg ~]$ mkdir -p /home/oracle/app/oraInventory
[oracle@univdg ~]$ ll /home/oracle/app/
总用量 0
drwxr-xr-x 3 oracle oinstall 21 8月  19 10:29 oracle
drwxr-xr-x 2 oracle oinstall  6 8月  19 10:29 oraInventory 

说明：此处ORACLE_HOME的目录为/home/oracle/app/oracle/product/11.2.0/db_1，是不是感觉比较惊讶？
这是因为客户在磁盘规划时将空间放到/home目录下.

2.9、修改环境变量

# su - oracle
$ cat <<EOF>>/home/oracle/.bash_profile
export ORACLE_BASE=/home/oracle/app/oracle
export ORACLE_HOME=\$ORACLE_BASE/product/11.2.0/db_1
export ORACLE_SID=orcldg
export PATH=\$ORACLE_HOME/bin:$PATH
export LD_LIBRARY_PATH=\$ORACLE_HOME/lib
EOF
$ source .bash_profile

2.10、修改系统限制

# cat <<EOF>>/etc/security/limits.conf
oracle soft nproc 2047
oracle hard nproc 16384
oracle soft nofile 1024
oracle hard nofile 65536
EOF

2.11、修改/etc/pam.d/login文件

# cat <<EOF>>/etc/pam.d/login
session required /lib64/security/pam_limits.so
EOF

2.12、配置Yum源

# mount /dev/sr0 /mnt
# df -h
文件系统             容量  已用  可用 已用% 挂载点
devtmpfs              32G     0   32G    0% /dev
tmpfs                 32G     0   32G    0% /dev/shm
tmpfs                 32G   11M   32G    1% /run
tmpfs                 32G     0   32G    0% /sys/fs/cgroup
/dev/mapper/ao-root   50G  4.1G   46G    9% /
/dev/sda1           1014M  189M  826M   19% /boot
/dev/mapper/ao-home  418G   37M  418G    1% /home
tmpfs                6.3G   60K  6.3G    1% /run/user/0
/dev/sr0             8.7G  8.7G     0  100% /mnt
# cat <<EOF>>/etc/yum.repos.d/anolisos.repo
[anolisos]
name=anolisos
baseurl=file:///mnt
gpgcheck=0
enabled=1
EOF
# cat /etc/yum.repos.d/anolisos.repo
[anolisos]
name=anolisos
baseurl=file:///mnt
gpgcheck=0
enabled=1
# yum makecache

2.13、安装依赖包

# yum groupinstall -y "Server with GUI"
yum install -y bc \
binutils \
compat-libcap1 \
compat-libstdc++-33 \
gcc \
gcc-c++ \
elfutils-libelf \
elfutils-libelf-devel \
glibc \
glibc-devel \
ksh \
libaio \
libaio-devel \
libgcc \
libstdc++ \
libstdc++-devel \
libxcb \
libX11 \
libXau \
libXi \
libXtst \
libXrender \
libXrender-devel \
make \
net-tools \
nfs-utils \
smartmontools \
sysstat \
e2fsprogs \
e2fsprogs-libs \
fontconfig-devel \
expect \
unzip \
openssh-clients \
readline* \
tigervnc* \
iotop \
psmisc --skip-broken

手动上传并安装依赖包：pdksh-5.2.14-37.el5.x86_64.rpm和compat-libstdc++-33-3.2.3-72.el7.x86_64.rpm
# rpm -ivh compat-libstdc++-33-3.2.3-72.el7.x86_64.rpm
# rpm -e ksh-20120801-142.an7.x86_64
# rpm -ivh pdksh-5.2.14-37.el5.x86_64.rpm
检查依赖包安装情况
# rpm -q bc binutils compat-libcap1 compat-libstdc++-33 gcc gcc-c++ elfutils-libelf elfutils-libelf-devel glibc glibc-devel ksh libaio libaio-devel libgcc libstdc++ libstdc++-devel libxcb libX11 libXau libXi libXtst libXrender libXrender-devel make net-tools nfs-utils smartmontools sysstat e2fsprogs e2fsprogs-libs fontconfig-devel expect unzip openssh-clients readline iotop | grep "not installed"

2.14、上传文件

# mkdir -p /u01/setup/db
--sftp上传到linux
put p13390677_112040_Linux-x86-64_1of7.zip
put p13390677_112040_Linux-x86-64_2of7.zip

2.15、解压文件

# chown -R oracle:oinstall /u01/setup/db
[oracle@univdg db]$ unzip -q p13390677_112040_Linux-x86-64_1of7.zip
[oracle@univdg db]$ unzip -q p13390677_112040_Linux-x86-64_2of7.zip

2.16、安装db

[oracle@univdg database]$ export DISPLAY=192.168.9.88:0.0
[oracle@univdg database]$ ./runInstaller 
图形界面中文出现乱码，如下设置：
[oracle@univdg database]$ export LANGUAGE=en_US.UTF-8
[oracle@univdg database]$ export LANG=en_US.UTF-8
此处为图形界面安装，为减少文章篇幅，未贴图片.过程中需要做如下处理：
a、vi $ORACLE_HOME/sysman/lib/ins_emagent.mk
将$(MK_EMAGENT_NMECTL)修改为：$(MK_EMAGENT_NMECTL-lnnz11)
b、root跑脚本

说明：备库端数据库软件安装完毕，监听在后面处理.

3、主库配置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     421
Next log sequence to archive   423
Current log sequence

更改归档路径

SQL> alter system set log_archive_dest_1='locatinotallow=/home/oracle/app/oracle/archivelog valid_for=(all_logfiles,all_roles) db_unique_name=orcl';

System altered.

检查是否开启强制归档

SQL> select force_logging from v$database;

FORCE_
------
NO
SQL> alter database force logging;

Database altered.

3.2、创建STANDBY日志文件

查询主库当前redo logfile的大小和组数.

SQL> select thread#,group#,members,bytes/1024/1024 as M from v$log;

   THREAD#     GROUP#    MEMBERS          M
---------- ---------- ---------- ----------
         1          1          1        512
         1          2          1        512
         1          3          1        512

查询standby 日志的大小和组数：

SQL> select group#,bytes/1024/1024 as M from v$standby_log;

no rows selected

创建standby日志.

SQL> alter database add standby logfile group 11 ('/u01/app/oracle/oradata/orcl/redo11_standby.log') size 512M;

Database altered.

SQL> alter database add standby logfile group 12 ('/u01/app/oracle/oradata/orcl/redo12_standby.log') size 512M;

Database altered.

SQL> alter database add standby logfile group 13 ('/u01/app/oracle/oradata/orcl/redo13_standby.log') size 512M;

Database altered.

SQL> alter database add standby logfile group 14 ('/u01/app/oracle/oradata/orcl/redo14_standby.log') size 512M;

Database altered.

3.3、修改参数3.3.1、设置

SQL> alter system set log_archive_config='dg_config=(orcl,orcldg)';

System altered.

3.3.2、设置备库归档目的地

SQL> alter system set log_archive_dest_2='service=orcldg lgwr async valid_for=(online_logfiles,primary_role) db_unique_name=orcldg';

System altered.

3.3.3、启用日志路径

SQL> alter system set log_archive_dest_state_1='enable';

System altered.

SQL> alter system set log_archive_dest_state_2='enable';

System altered.

SQL> alter system set fal_server=orcldg;

System altered.

SQL> alter system set fal_client=orcl;

System altered.

3.3.4、设置文件管理模式

此项设置为自动，否则在主库创建数据文件后，备库不会自动创建.

SQL> alter system set standby_file_management=auto;

System altered.

3.4、拷贝参数文件

用 spfile 文件创建 pfile 文件.

SQL> create pfile from spfile;

File created.

使用 scp 命令：

$ cd $ORACLE_HOME
$ cd dbs
$ ll
总用量 9544
-rw-rw----. 1 oracle oinstall    1544 6月   1 23:22 hc_orcl.dat
-rw-r--r--. 1 oracle oinstall    2851 5月  15 2009 init.ora
-rw-r--r--. 1 oracle oinstall    1435 8月  19 11:50 initorcl.ora
-rw-r-----. 1 oracle oinstall      24 11月 17 2020 lkORCL
-rw-r-----. 1 oracle oinstall    1536 11月 17 2020 orapworcl
-rw-r-----. 1 oracle oinstall 9748480 8月  19 05:01 snapcf_orcl.f
-rw-r-----. 1 oracle oinstall    3584 8月  19 11:49 spfileorcl.ora

$ scp initorcl.ora oracle@192.168.11.218:/home/oracle/app/oracle/product/11.2.0/db_1/dbs
$ scp orapworcl oracle@192.168.11.218:/home/oracle/app/oracle/product/11.2.0/db_1/dbs

3.5、修改TNS文件

[oracle@univ admin]$ cat tnsnames.ora 
# tnsnames.ora Network Configuration File: /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.

ORCL =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = univ)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orcl)
    )
  )

ORCLDG =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.11.218)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orcldg)
    )
  )

#ORCLDG为第一行，下面8行为添加部分.

3.6、重启监听

$ lsnrctl stop
$ lsnrctl start

3.7、拷贝监听、tns文件

$ scp listener.ora oracle@192.168.11.218:/home/oracle/app/oracle/product/11.2.0/db_1/network/admin
$ scp tnsnames.ora oracle@192.168.11.218:/home/oracle/app/oracle/product/11.2.0/db_1/network/admin

4、备库配置4.1、添加实例名

[oracle@bigdata ~]$ vi /etc/oratab
添加以下：
orcldg:/home/oracle/app/oracle/product/11.2.0/db_1:N

4.2、重命名文件

[oracle@univdg dbs]$ ll
总用量 12
-rw-r--r-- 1 oracle oinstall 2851 5月  15 2009 init.ora
-rw-r--r-- 1 oracle oinstall 1435 8月  19 12:00 initorcl.ora
-rw-r----- 1 oracle oinstall 1536 8月  19 12:01 orapworcl
$ mv initorcl.ora initorcldg.ora
$ mv orapworcl orapworcldg

4.3、修改参数文件

[oracle@bigdata dbs]$ cat initorcldg.ora
*.audit_file_dest='/home/oracle/app/oracle/admin/orcl/adump'
*.audit_trail='db'
*.compatible='11.2.0.4.0'
*.db_block_size=8192
*.db_domain=''
*.db_recovery_file_dest='/home/oracle/app/oracle/fast_recovery_area'
*.db_recovery_file_dest_size=4385144832
*.diagnostic_dest='/home/oracle/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=orclXDB)'
*.fal_client='ORCLDG'
*.fal_server='ORCL'
*.log_archive_config='dg_config=(orcl,orcldg)'
*.log_archive_dest_1='location=/home/oracle/app/oracle/archivelog valid_for=(all_logfiles,all_roles) db_unique_name=orcldg'
*.log_archive_dest_2='service=orcl lgwr async valid_for=(online_logfiles,primary_role) db_unique_name=orcl'
*.log_archive_dest_state_1='enable'
*.log_archive_dest_state_2='enable'
*.nls_language='AMERICAN'
*.nls_territory='AMERICA'
*.open_cursors=1000
*.pga_aggregate_target=8493465600
*.processes=2000
*.remote_login_passwordfile='EXCLUSIVE'
*.sessions=2205
*.sga_target=25482493952
*.standby_file_management='AUTO'
*.undo_tablespace='UNDOTBS1'
*.control_files='/home/oracle/app/oracle/oradata/control01.ctl'
*.db_name='orcl'
*.db_unique_name='orcldg'
*.log_file_name_convert='/u01/app/oracle/oradata/orcl/','/home/oracle/app/oracle/oradata/','/u01/app/oracle/fast_recovery_area/orcl/','/home/oracle/app/oracle/fast_recovery_area/'
*.db_file_name_convert='/u01/app/oracle/oradata/orcl/','/home/oracle/app/oracle/oradata/'

说明：蓝色部分为修改内容.

4.4、创建目录

[oracle@univdg dbs]$ mkdir -p /home/oracle/app/oracle/admin/orcl/adump
[oracle@univdg dbs]$ mkdir -p /home/oracle/app/oracle
[oracle@univdg dbs]$ mkdir -p /home/oracle/app/oracle/archivelog
[oracle@univdg dbs]$ mkdir -p /home/oracle/app/oracle/oradata/
[oracle@univdg dbs]$ mkdir -p /home/oracle/app/oracle/fast_recovery_area/

4.5、修改监听配置文件

修改监听配置文件：
[oracle@univdg admin]$ cat listener.ora
# listener.ora Network Configuration File: /home/oracle/app/oracle/product/11.2.0/db_1/network/admin/listener.ora
# Generated by Oracle configuration tools.
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = orcldg)
(ORACLE_HOME = /home/oracle/app/oracle/product/11.2.0/db_1)
(SID_NAME = orcldg)
)
)

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

ADR_BASE_LISTENER = /home/oracle/app/oracle

说明：蓝色部分为修改内容.

4.6、修改 TNS 配置文件

[oracle@univdg admin]$ cat tnsnames.ora
# tnsnames.ora Network Configuration File: /home/oracle/app/oracle/product/11.2.0/db_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.

ORCL =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = univ)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orcl)
)
)

ORCLDG =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.11.218)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orcldg)
)
)

备注：从主库拷贝过来的tnsnames.ora文件其实不用再做修改，不过此处修改了注释目录,黑体高亮部分为修改内容.

4.7、重启监听服务

$ lsnrctl stop
$ lsnrctl start

4.8、增加域名解析

[root@univdg ~]# vi /etc/hosts
[root@univdg ~]# cat /etc/hosts
127.0.0.1   localhost localhost.localdomain localhost4 localhost4.localdomain4
::1         localhost localhost.localdomain localhost6 localhost6.localdomain6
192.168.11.218 univdg
192.168.133.120 univ

4.9、备库到nomount

启动备库到 nomount 状态.

[oracle@univdg admin]$ echo $ORACLE_SID
orcldg
[[oracle@univdg admin]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Fri Aug 19 13:42:11 2022

Copyright (c) 1982, 2013, Oracle.  All rights reserved.

Connected to an idle instance.

SQL> startup nomount pfile='/home/oracle/app/oracle/product/11.2.0/db_1/dbs/initorcldg.ora';
ORACLE instance started.

Total System Global Area 2.5388E+10 bytes
Fixed Size                  2265056 bytes
Variable Size            3892314144 bytes
Database Buffers         2.1475E+10 bytes
Redo Buffers               18096128 bytes
SQL> create spfile from pfile;

File created.
SQL> shutdown immediate;  #关闭数据库
ORA-01507: database not mounted
ORACLE instance shut down.
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 -
64bit Production
With the Partitioning, OLAP, Data Miningand Real ApplicationTesting options
[oracle@univdg admin]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Fri Aug 19 13:44:52 2022

Copyright (c) 1982, 2013, Oracle.  All rights reserved.

Connected to an idle instance.
SQL> startup nomount
ORACLE instance started.

Total System Global Area 2.5388E+10 bytes
Fixed Size                  2265056 bytes
Variable Size            3892314144 bytes
Database Buffers         2.1475E+10 bytes
Redo Buffers               18096128 bytes
SQL> select status from v$instance;  #查看备库状态
STATUS
------------
STARTED

4.10、验证

验证监听和TNS 配置

主库：

ping 192.168.11.218
tnsping orcldg

备库：

ping 192.168.133.120
tnsping orcl

说明：测试OK后，主备库正常连接，如下所示：

4.10.1、主库验证

[oracle@univ scripts]$ sqlplus sys/*******@192.168.133.120:1521/orcl as sysdba
> show parameter db_unique_name;
$ [oracle@univ scripts]$ sqlplus sys/*******@192.168.11.218:1521/orcldg as sysdba

4.10.2、备库验证

[oracle@univ scripts]$ sqlplus sys/*******@192.168.133.120:1521/orcl as sysdba
> show parameter db_unique_name;
[oracle@univ scripts]$ sqlplus sys/*******@192.168.11.218:1521/orcl as sysdba

备注：在主备库上都能测试通过的条件下才能执行下面步骤

5、数据恢复

说明：确保备库启动到nomount状态

编写duplicate.sh和duplicate.rcv文件，使用后台进程进行活动复制.

至于为什么要使用后台进程进行活动复制？因为在实际生产中，很多时候是远程搭建高可用环境，而远程终端通常不是你一个人使用.对于这种长时间的等待，命令会话很可能被别人关掉.这个笔者就曾遭遇过，真的是记忆犹新.一次上T的数据恢复，都快结束战斗了，结果会话很不幸的被别人关掉，后面又重来，心情那才叫郁闷.

$ vi duplicate.sh
#!/bin/bash

export ORACLE_BASE=/home/oracle/app/oracle
export ORACLE_HOME=$ORACLE_BASE/product/11.2.0/db_1
export ORACLE_SID=orcldg
export PATH=$ORACLE_HOME/bin:$PATH
export NLS_LANG="AMERICAN_AMERICA.ZHS16GBK"

rman target sys/*******@orcl auxiliary sys/*******@orcldg cmdfile=/home/oracle/scripts/duplicate.rcv log=/home/oracle/scripts/duplicate_`date +%Y-%m-%d`.log

$ vi duplicate.rcv
run{
allocate channel c1 device type disk;
allocate channel c2 device type disk;
allocate channel c3 device type disk;
allocate channel c4 device type disk;
allocate channel c5 device type disk;
allocate channel c6 device type disk;
allocate auxiliary channel a1 device type disk;
allocate auxiliary channel a2 device type disk;
allocate auxiliary channel a3 device type disk;
allocate auxiliary channel a4 device type disk;
allocate auxiliary channel a5 device type disk;
allocate auxiliary channel a6 device type disk;
duplicate target database for standby from active database;
}
exit

$ chmod u+x duplicate.sh
$ chmod u+x duplicate.rcv
$ nohup ./duplicate.sh &

说明：恢复完成后，即可open备库并开启实时应用.

6、归档处理

注意：备库需编写自动删除归档日志脚本，否则归档日志很快会将磁盘空间消耗完.

$ vi rmarch.sh
#!/bin/bash

export ORACLE_BASE=/home/oracle/app/oracle
export ORACLE_HOME=$ORACLE_BASE/product/11.2.0/db_1
export ORACLE_SID=orcldg
 
export ORACLE_UNQNAME=orcl
export PATH=$ORACLE_HOME/bin:$PATH

rman target / cmdfile=/home/oracle/scripts/rmarch.rcv log=/home/oracle/scripts/rmarch_$(date +%Y-%m-%d-%H%M).log

$ vi rmarch.rcv
run{
   allocate channel c1 device type disk;
   allocate channel c2 device type disk;
   crosscheck archivelog all;
   delete noprompt expired archivelog all;
   delete noprompt archivelog until time 'sysdate-7';
   release channel c1;
   release channel c2;
    }
exit

$ chmod u+x rmarch.sh
$ chmod u+x rmarch.rcv
$ crontab -e
30 2 * * * /home/oracle/scripts/rmarch.sh &>/dev/null
35 2 * * * find /home/oracle/scripts/* -name "rmarch_*.log" -mtime +5 -exec rm -rf {} \;

7、主库处理

说明：为避免后期主备切换，主库需设置log_file_name_convert、db_file_name_convert，否则切换后新备库(原主库)会出现宕机异常，需重启数据库生效.

SQL> alter system set log_file_name_convert='/home/oracle/app/oracle/oradata/','/u01/app/oracle/oradata/orcl/','/home/oracle/app/oracle/fast_recovery_area/','/u01/app/oracle/fast_recovery_area/orcl/' scope=spfile;
SQL> alter system set  db_file_name_convert='/home/oracle/app/oracle/oradata/','/u01/app/oracle/oradata/orcl/' scope=spfile;

本文地址：https://www.henghost.com/jishu/8945/