Oracle19c (Active) Data Guard 安装
一、DataGuard概述
1.Primary Database
An Oracle Data Guard configuration contains one production database, also referred
to as the primary database, that functions in the primary role.
The primary database is the database that is accessed by most of your applications.
The primary database can be either a single-instance Oracle database or an Oracle
Real Application Clusters (Oracle RAC) database.
2.Standby Databases Type
The types of standby databases are as follows:
• Physical standby database
Provides a physically identical copy of the primary database, with on-disk
database structures that are identical to the primary database on a block-for-block
basis. The database schema, including indexes, are the same. A physical standby
database is kept synchronized with the primary database, through Redo Apply,
which recovers the redo data received from the primary database and applies the
redo to the physical standby database.
As of Oracle Database 11g Release 1 (11.1), a physical standby database can
receive and apply redo while it is open for read-only access. A physical standby
database can therefore be used concurrently for data protection and reporting.
Additionally, as of Oracle Database 11g Release 2 (11.2.0.1), a physical standby
database can be used to install eligible one-off patches, patch set updates (PSUs),
and critical patch updates (CPUs), in rolling fashion. For more information about
this functionality, see the My Oracle Support note 1265700.1 at http://
support.oracle.com.
• Logical standby database
Contains the same logical information as the production database, although the
physical organization and structure of the data can be different. The logical
standby database is kept synchronized with the primary database through SQL
Apply, which transforms the data in the redo received from the primary database
into SQL statements and then executes the SQL statements on the standby
database.
The flexibility of a logical standby database lets you upgrade Oracle Database
software (patch sets and new Oracle Database releases) and perform other
database maintenance in rolling fashion with almost no downtime. From Oracle
Database 11g onward, the transient logical database rolling upgrade process can
also be used with existing physical standby databases.
• Snapshot Standby Database
A snapshot standby database is a fully updatable standby database.
Like a physical or logical standby database, a snapshot standby database
receives and archives redo data from a primary database. Unlike a physical or
logical standby database, a snapshot standby database does not apply the redo
data that it receives. The redo data received by a snapshot standby database is
not applied until the snapshot standby is converted back into a physical standby
database, after first discarding any local updates made to the snapshot standby
database.
A snapshot standby database is best used in scenarios that require a temporary,
updatable snapshot of a physical standby database. For example, you can use the
Oracle Real Application Testing option to capture the database workload on a
primary and then replay it for test purposes on the snapshot standby. Because
redo data received by a snapshot standby database is not applied until it is
converted back into a physical standby, the time needed to recover from a primary
database failure is directly proportional to the amount of redo data that needs to be
applied.
3.DataGuard典型图解
4.Physical Standby和Logical Standby
5.DataGuard服务
Oracle Data Guard uses Redo Transport Services and Apply Services to manage the
transmission of redo data, the application of redo data, and changes to the database
roles.
• Redo Transport Services
Control the automated transfer of redo data from the production database to one
or more archival destinations.
• Apply Services
Redo data is applied directly from standby redo log files as they are filled using
real-time apply. If standby redo log files are not configured, then redo data must
first be archived at the standby database before it is applied.
• Role Transitions
Change the role of a database from a standby database to a primary database, or
from a primary database to a standby database using either a switchover or a
failover operation.
6.DataGuard保护模式
Maximum Availability
This protection mode provides the highest level of data protection that is possible
without compromising the availability of a primary database. With Oracle Data Guard,
transactions do not commit until all redo data needed to recover those transactions
has either been received in memory or written to the standby redo log (depending
upon configuration) on at least one synchronized standby database. If the primary
database cannot write its redo stream to at least one synchronized standby database,
it operates as if it were in maximum performance mode to preserve primary database
availability until it is again able to write its redo stream to a synchronized standby
database.
This protection mode ensures zero data loss except in the case of certain double
faults, such as failure of a primary database after failure of the standby database.
最高可用模式（Maximum Availability）能提供非常高级别的数据保护。在这种模式下，只有当所有的redo日志备写入至少一个standby database时，事物才能提交。
当主库不能将redo日志写入至少一个standby database时，那么DG就相当于在最大性能（maximum performance）模式工作。
Maximum Performance
This is the default protection mode. It provides the highest level of data protection that
is possible without affecting the performance of a primary database. This is
accomplished by allowing transactions to commit as soon as all redo data generated
by those transactions has been written to the online log. Redo data is also written to
one or more standby databases, but this is done asynchronously with respect to
transaction commitment, so primary database performance is unaffected by delays in
writing redo data to the standby database(s).
This protection mode offers slightly less data protection than maximum availability
mode and has minimal impact on primary database performance.
Maximum Protection
This protection mode ensures that no data loss occurs if the primary database fails. To
provide this level of protection, the redo data needed to recover a transaction must be
written to both the online redo log and to the standby redo log on at least one
synchronized standby database before the transaction commits. To ensure that data
loss cannot occur, the primary database shuts down, rather than continue processing
transactions, if it cannot write its redo stream to at least one synchronized standby
database.
All three protection modes require that specific redo transport options be used to send
redo data to at least one standby database.
二、DataGuard硬件和系统要求
The same release of Oracle Database Enterprise Edition must be installed on the
primary database and all standby databases, except during rolling database upgrades
using logical or transient logical standby databases.
As of Oracle Database 11g, Oracle Data Guard provides increased flexibility for Oracle
Data Guard configurations in which the primary and standby systems may have
different CPU architectures, operating systems (for example, Windows and Linux),
operating system binaries (32-bit/64-bit), or Oracle database binaries (32-bit/64-bit).
This increased mixed-platform flexibility is subject to the current restrictions
documented in the My Oracle Support notes 413484.1 and 1085687.1 at http://
support.oracle.com.
Oracle Data Guard is available only as a feature of Oracle Database Enterprise
Edition. It is not available with Oracle Database Standard Edition.
The COMPATIBLE database initialization parameter must be set to the same value
on all databases in an Oracle Data Guard configuration, except when using a
logical standby database, which can have a higher COMPATIBLE setting than the
primary database.
The primary database must run in ARCHIVELOG mode.
The user accounts you use to manage the primary and standby database
instances must have either the SYSDG or SYSDBA administrative privilege.
三、安装前准备
安装前介质准备
| 软件 | 版本 | 安装包 |
|---|---|---|
| Centos | 7.6 | CentOS-7-x86_64-DVD-1810.iso |
| Oracle19c | 19.3.0 | LINUX.X64_193000_db_home.zip |
| Rlwrap | 0.37 | rlwrap-0.37-1.el6.x86_64.rpm |
安装结构分布
| primary db | standby db | |
|---|---|---|
| IP | 192.168.1.141 | 192.168.1.142 |
| ROLE | primary | standby |
| HostName | server1 | server2 |
| DB_NAME | adg | adg |
| DB_UNIQUE_NAME | oradg1 | oradg2 |
| TNS | primary | standby |
| PDB | xprod | |
| DataFile | /opt/ora19cData/ADG | /opt/oraData/ADG |
| ArchiveLog | /opt/ora19cArc | /opt/oraArc |
四、安装Oracle19c
主库(server1)需要安装Oracle19c数据库，从库(server2)只需要安装Oracle19c软件，无需建库
1.关闭防火墙
systemctl status firewalld.service
systemctl stop firewalld.service
systemctl disable firewalld.service
或者单独开启端口:
firewall-cmd --zone=public --add-port=15021/tcp --permanent
firewall-cmd --reload
firewall-cmd --query-port=15021/tcp
firewall-cmd --list-port2.关闭selinx
临时关闭:
setenforce 0
永久关闭(需要重启机器):
vi /etc/selinux/config
SELINUX=disabled
查看:
getenforce
命令关闭:
[root@server1 ~]# sed -i 's/SELINUX=enforcing/SELINUX=disabled/' /etc/selinux/config
[root@server1 ~]# setenforce 03.配置yum
[root@server1 ~]# mkdir /opt/yum
[root@server1 ~]# cp -r /run/media/admin/CentOS\ 7\ x86_64/* /opt/yum
[root@server1 ~]# vi /etc/yum.repos.d/CentOS-Base.repo
[base]
name=CentOS-$releasever - Base
baseurl=file:///opt/yum
gpgcheck=0
enabled=1
或者:
cat << EOF > /etc/yum.repos.d/CentOS-Base.repo
[base]
name=CentOS-$releasever - Base
baseurl=file:///opt/yum
gpgcheck=0
enabled=1
EOF
[root@server1 ~]# yum list
或者挂载iso文件:
[root@server1 ~]# mkdir /opt/yum
[root@server1 ~]# mount -o loop Oracle_Linux7.6_V980739-01.iso /mnt
[root@server1 ~]# cp -r /mnt/* /opt/yum4.磁盘空间检查
grep MemTotal /proc/meminfo
grep SwapTotal /proc/meminfo
df -h /tmp
free
uname -m
df -h /dev/shm #检查共享内存
If the /tmp directory has less than 1 GB of free space, then identify a file system
with at least 1 GB of free space and set the TMP and TMPDIR environment variables
to specify a temporary directory on this file system:
note:You cannot use a shared file system as the location of the temporary file
directory (typically /tmp) for Oracle RAC installations. If you place /tmp
on a shared file system, then the installation fails.
如果临时空间不足，可以使用以下方法处理:
If necessary, enter commands similar to the following to create a temporary
directory on the file system that you identified, and set the appropriate
permissions on the directory:
$ sudo - s
# mkdir /mount_point/tmp
# chmod 775 /mount_point/tmp
# exit
Enter commands similar to the following to set the TMP and TMPDIR
environment variables:
$ TMP=/mount_point/tmp
$ TMPDIR=/mount_point/tmp
$ export TMP TMPDIR
5.禁用透明页
Transparent HugePages memory differs from standard HugePages memory because
the kernel khugepaged thread allocates memory dynamically during runtime. Standard
HugePages memory is pre-allocated at startup, and does not change during runtime.
Transparent HugePages memory is enabled by default with Oracle Linux 6 and later,
Red Hat Enterprise Linux 6 and later, SUSE 11 and later, kernels.
To check if Transparent HugePages is enabled, run one of the following commands as
the root user:
方法一:
Red Hat Enterprise Linux kernels:
# cat /sys/kernel/mm/redhat_transparent_hugepage/enabled
Other kernels:
# cat /sys/kernel/mm/transparent_hugepage/enabled
The following is a sample output that shows Transparent HugePages are being used
as the [always] flag is enabled.
[always] never
方法二:
[root@server1 ~]# grep AnonHugePages /proc/meminfo
AnonHugePages: 14336 kB
如果 AnonHugePages > 0 同样表示启用了透明大页
note:If Transparent HugePages is removed from the kernel, then neither /sys/
kernel/mm/transparent_hugepage nor /sys/kernel/mm/
redhat_transparent_hugepage files exist.
To disable Transparent HugePages:
1. For Oracle Linux 7 and Red Hat Enterprise Linux 7, add or modify the
transparent_hugepage=never parameter in the /etc/default/grub file:
transparent_hugepage=never
For example:
vi /etc/default/grub
GRUB_TIMEOUT=5
GRUB_DISTRIBUTOR="$(sed 's, release .*$,,g' /etc/system-release)"
GRUB_DEFAULT=saved
GRUB_DISABLE_SUBMENU=true
GRUB_TERMINAL_OUTPUT="console"
GRUB_CMDLINE_LINUX="crashkernel=auto rhgb quiet numa=off transparent_hugepage=never"
GRUB_DISABLE_RECOVERY="true"
2. Run the grub2–mkconfig command to regenerate the grub.cfg file.
# grub2-mkconfig -o /boot/grub2/grub.cfg
3. Restart the system to make the changes permanent
4.Verify
[root@server1 ~]# more /sys/kernel/mm/transparent_hugepage/enabled
always madvise [never]
[root@server1 ~]# grep AnonHugePages /proc/meminfo
AnonHugePages: 0 kB
如果报以下错误，可能是/boot空间不足:
[root@localhost GPU]# init 3
Broadcast message from systemd-journald@localhost.localdomain (Wed 2017-09-20 11:26:40 CST):
dracut[11820]: dracut: creation of /boot/initramfs-3.10.0-693.2.2.el7.x86_64kdump.img failed
Message from syslogd@localhost at Sep 20 11:26:40 ...
dracut:dracut: creation of /boot/initramfs-3.10.0-693.2.2.el7.x86_64kdump.img failed
[root@localhost boot]# df /boot
[root@localhost boot]# rpm -qa|grep kernel
卸载老版本内核:
[root@localhost boot]# rpm -e kernel-3.10.0-514.el7.x86_64 kernel-3.10.0-514.26.2.el7.x86_64
[root@localhost boot]# yum remove kernel-3.10.0-957.el7.x86_64
6.磁盘调度
For best performance for Oracle ASM, Oracle recommends that you use the Deadline
I/O Scheduler.
Disk I/O schedulers reorder, delay, or merge requests for disk I/O to achieve better
throughput and lower latency. Linux has multiple disk I/O schedulers available,
including Deadline, Noop, Anticipatory, and Completely Fair Queuing (CFQ).
On each cluster node, enter the following command to verify that the Deadline disk I/O
scheduler is configured for use:
# cat /sys/block/${ASM_DISK}/queue/scheduler
noop [deadline] cfq
In this example, the default disk I/O scheduler is Deadline and ASM_DISK is the Oracle
Automatic Storage Management (Oracle ASM) disk device.
On some virtual environments (VM) and special devices such as fast storage devices,
the output of the above command may be none. The operating system or VM
bypasses the kernel I/O scheduling and submits all I/O requests directly to the device.
Do not change the I/O Scheduler settings on such environments.
If the default disk I/O scheduler is not Deadline, then set it using a rules file:
1. Using a text editor, create a UDEV rules file for the Oracle ASM devices:
# vi /etc/udev/rules.d/60-oracle-schedulers.rules
2. Add the following line to the rules file and save it:
ACTION=="add|change", KERNEL=="sd[a-z]", ATTR{queue/rotational}=="0",
ATTR{queue/scheduler}="deadline"
3. On clustered systems, copy the rules file to all other nodes on the cluster. For
example:
$ scp 60-oracle-schedulers.rules root@node2:/etc/udev/rules.d/
4. Load the rules file and restart the UDEV service. For example:
a. Oracle Linux and Red Hat Enterprise Linux
# udevadm control --reload-rules
b. SUSE Linux Enterprise Server
# /etc/init.d boot.udev restart
5. Verify that the disk I/O scheduler is set as Deadline.
7.配置SSH
In rare cases, Oracle Clusterware installation can fail during the "AttachHome"
operation when the remote node closes the SSH connection. To avoid this problem,
set the timeout wait to unlimited by setting the following parameter in the SSH daemon
configuration file /etc/ssh/sshd_config on all cluster nodes:
LoginGraceTime 0
#vi /etc/ssh/sshd_config
LoginGraceTime 08.安装操作系统依赖包
- preinstall rpm包安装方法
1. Obtain Oracle Linux either by ordering the Oracle Linux media pack from Oracle
Store, or by downloading disk images from the Oracle Software Delivery Cloud
website for Oracle Linux and Oracle VM.
• Oracle Store
https://shop.oracle.com
• Oracle Software Delivery Cloud website:
https://edelivery.oracle.com/linux
2. Start the Oracle Linux installation and respond to installation screens with values
appropriate for your environment.
3. Review the first software selection screen, which lists task-specific software
options. At the bottom of the screen, there is an option to customize now or
customize later. Select Customize now, and click Next.
4. On Oracle Linux, select Servers on the left of the screen and System
administration tools on the right of the screen (options may vary between
releases).
The Packages in System Tools window opens.
5. Select the Oracle Preinstallation RPM package box from the package list. For
example, for Oracle Linux 7, select a package similar to the following:
oracle-database-server-12cR2-preinstall-1.0-4.el7.x86_64.rpm
If you do not have an Oracle Preinstallation RPM package option that is current for
your Oracle Database release, because you are using an Oracle Linux installation
that is previous to your Oracle Database and Oracle Grid Infrastructure release,
then install the RPM for your release manually after completing the operating
system installation.
6. Close the optional package window and click Next.
7. Complete the other screens to finish the Oracle Linux installation.
Oracle Linux automatically creates a standard (not role-allocated) Oracle
installation owner and groups, and sets up other kernel configuration settings as
required for Oracle installations.
8. Repeat steps 2 through 6 on all other cluster member nodes.
yum install oracle-database-server-12cR2-preinstall
- 手动安装方法
Oracle Linux 7:
bc
binutils-2.23.52.0.1-12.el7 (x86_64)
compat-libcap1-1.10-3.el7 (x86_64)
compat-libstdc++-33-3.2.3-71.el7 (i686)
compat-libstdc++-33-3.2.3-71.el7 (x86_64)
glibc-2.17-36.el7 (i686)
glibc-2.17-36.el7 (x86_64)
glibc-devel-2.17-36.el7 (i686)
glibc-devel-2.17-36.el7 (x86_64)
ksh
libaio-0.3.109-9.el7 (i686)
libaio-0.3.109-9.el7 (x86_64)
libaio-devel-0.3.109-9.el7 (i686)
libaio-devel-0.3.109-9.el7 (x86_64)
libX11-1.6.0-2.1.el7 (i686)
libX11-1.6.0-2.1.el7 (x86_64)
libXau-1.0.8-2.1.el7 (i686)
libXau-1.0.8-2.1.el7 (x86_64)
libXi-1.7.2-1.el7 (i686)
libXi-1.7.2-1.el7 (x86_64)
libXtst-1.2.2-1.el7 (i686)
libXtst-1.2.2-1.el7 (x86_64)
libXrender (i686)
libXrender (x86_64)
libXrender-devel (i686)
libXrender-devel (x86_64)
libgcc-4.8.2-3.el7 (i686)
libgcc-4.8.2-3.el7 (x86_64)
libstdc++-4.8.2-3.el7 (i686)
libstdc++-4.8.2-3.el7 (x86_64)
libstdc++-devel-4.8.2-3.el7 (i686)
libstdc++-devel-4.8.2-3.el7 (x86_64)
libxcb-1.9-5.el7 (i686)
libxcb-1.9-5.el7 (x86_64)
make-3.82-19.el7 (x86_64)
nfs-utils-1.3.0-0.21.el7.x86_64 (for Oracle ACFS)
net-tools-2.0-0.17.20131004git.el7 (x86_64) (for Oracle RAC
and Oracle Clusterware)
smartmontools-6.2-4.el7 (x86_64)
sysstat-10.1.5-1.el7 (x86_64)
Oracle Linux 6:
bc
binutils-2.20.51.0.2-5.36.el6 (x86_64)
compat-libcap1-1.10-1 (x86_64)
compat-libstdc++-33-3.2.3-69.el6 (x86_64)
compat-libstdc++-33-3.2.3-69.el6 (i686)
e2fsprogs-1.41.12-14.el6 (x86_64)
e2fsprogs-libs-1.41.12-14.el6 (x86_64)
glibc-2.12-1.7.el6 (i686)
glibc-2.12-1.7.el6 (x86_64)
glibc-devel-2.12-1.7.el6 (x86_64)
glibc-devel-2.12-1.7.el6 (i686)
ksh
libgcc-4.4.4-13.el6 (i686)
libgcc-4.4.4-13.el6 (x86_64)
libstdc++-4.4.4-13.el6 (x86_64)
libstdc++-4.4.4-13.el6 (i686)
libstdc++-devel-4.4.4-13.el6 (x86_64)
libstdc++-devel-4.4.4-13.el6 (i686)
libaio-0.3.107-10.el6 (x86_64)
libaio-0.3.107-10.el6 (i686)
libaio-devel-0.3.107-10.el6 (x86_64)
libaio-devel-0.3.107-10.el6 (i686)
libXtst-1.0.99.2 (x86_64)
libXtst-1.0.99.2 (i686)
libX11-1.5.0-4.el6 (i686)
libX11-1.5.0-4.el6 (x86_64)
libXau-1.0.6-4.el6 (i686)
libXau-1.0.6-4.el6 (x86_64)
libxcb-1.8.1-1.el6 (i686)
libxcb-1.8.1-1.el6 (x86_64)
libXi-1.3 (x86_64)
libXi-1.3 (i686)
libXrender (i686)
libXrender (x86_64)
libXrender-devel (i686)
libXrender-devel (x86_64)
make-3.81-19.el6
net-tools-1.60-110.el6_2.x86_64 (for Oracle RAC and Oracle
Clusterware)
nfs-utils-1.2.3-15.0.1 (for Oracle ACFS)
sysstat-9.0.4-11.el6 (x86_64)
smartmontools-5.43-1.el6.x86_64
RedHat Linux 7:
bc
binutils-2.23.52.0.1-12.el7 (x86_64)
compat-libcap1-1.10-3.el7 (x86_64)
compat-libstdc++-33-3.2.3-71.el7 (i686)
compat-libstdc++-33-3.2.3-71.el7 (x86_64)
glibc-2.17-36.el7 (i686)
glibc-2.17-36.el7 (x86_64)
glibc-devel-2.17-36.el7 (i686)
glibc-devel-2.17-36.el7 (x86_64)
ksh
libaio-0.3.109-9.el7 (i686)
libaio-0.3.109-9.el7 (x86_64)
libaio-devel-0.3.109-9.el7 (i686)
libaio-devel-0.3.109-9.el7 (x86_64)
libgcc-4.8.2-3.el7 (i686)
libgcc-4.8.2-3.el7 (x86_64)
libstdc++-4.8.2-3.el7 (i686)
libstdc++-4.8.2-3.el7 (x86_64)
libstdc++-devel-4.8.2-3.el7 (i686)
libstdc++-devel-4.8.2-3.el7 (x86_64)
libxcb-1.9-5.el7 (i686)
libxcb-1.9-5.el7 (x86_64)
libX11-1.6.0-2.1.el7 (i686)
libX11-1.6.0-2.1.el7 (x86_64)
libXau-1.0.8-2.1.el7 (i686)
libXau-1.0.8-2.1.el7 (x86_64)
libXi-1.7.2-1.el7 (i686)
libXi-1.7.2-1.el7 (x86_64)
libXtst-1.2.2-1.el7 (i686)
libXtst-1.2.2-1.el7 (x86_64)
libXrender (i686)
libXrender (x86_64)
libXrender-devel (i686)
libXrender-devel (x86_64)
make-3.82-19.el7 (x86_64)
net-tools-2.0-0.17.20131004git.el7 (x86_64) (for Oracle RAC
and Oracle Clusterware)
nfs-utils-1.3.0-0.21.el7.x86_64 (for Oracle ACFS)
smartmontools-6.2-4.el7 (x86_64)
sysstat-10.1.5-1.el7 (x86_64)
RedHat Linux 6:
bc
binutils-2.20.51.0.2-5.36.el6 (x86_64)
compat-libcap1-1.10-1 (x86_64)
compat-libstdc++-33-3.2.3-69.el6 (x86_64)
compat-libstdc++-33-3.2.3-69.el6 (i686)
e2fsprogs-1.41.12-14.el6 (x86_64)
e2fsprogs-libs-1.41.12-14.el6 (x86_64)
glibc-2.12-1.107.el6 (i686)
glibc-2.12-1.107.el6 (x86_64)
glibc-devel-2.12-1.107.el6 (i686)
glibc-devel-2.12-1.107.el6 (x86_64)
ksh
libaio-0.3.107-10.el6 (x86_64)
libaio-0.3.107-10.el6 (i686)
libaio-devel-0.3.107-10.el6 (x86_64)
libaio-devel-0.3.107-10.el6 (i686)
libX11-1.5.0-4.el6 (i686)
libX11-1.5.0-4.el6 (x86_64)
libXau-1.0.6-4.el6 (i686)
libXau-1.0.6-4.el6 (x86_64)
libXi-1.6.1-3.el6 (i686)
libXi-1.6.1-3.el6 (x86_64)
libXtst-1.2.1-2.el6 (i686)
libXtst-1.2.1-2.el6 (x86_64)
libgcc-4.4.7-3.el6 (i686)
libgcc-4.4.7-3.el6 (x86_64)
libstdc++-4.4.7-3.el6 (i686)
libstdc++-4.4.7-3.el6 (x86_64)
libstdc++-devel-4.4.7-3.el6 (i686)
libstdc++-devel-4.4.7-3.el6 (x86_64)
libxcb-1.8.1-1.el6 (i686)
libxcb-1.8.1-1.el6 (x86_64)
libXrender (i686)
libXrender (x86_64)
libXrender-devel (i686)
libXrender-devel (x86_64)
make-3.81-20.el6 (x86_64)
net-tools-1.60-110.el6_2 (x86_64) (for Oracle RAC and Oracle
Clusterware)
nfs-utils-1.2.3-36.el6 (x86_64) (for Oracle ACFS)
smartmontools-5.43-1.el6 (x86_64)
sysstat-9.0.4-20.el6 (x86_64)
SUSE Linux Enterprise Server 15:
binutils-2.31-6.3.1.x86_64
gcc-7-1.563.x86_64
glibc-2.26-13.8.1.x86_64
glibc-32bit-2.26-13.8.1.x86_64
glibc-devel-2.26-13.8.1.x86_64
libaio-devel-0.3.109-1.25.x86_64
libaio1-0.3.109-1.25.x86_64
libcap1-1.97-1.15.x86_64
libstdc++6-devel-gcc7-7.4.0+r266845-4.3.4.x86_64
libstdc++6-8.2.1+r264010-1.3.7.x86_64
libgcc_s1-8.2.1+r264010-1.3.7.x86_64
libgcc_s1-32bit-8.2.1+r264010-1.3.7.x86_64
make-4.2.1-7.3.2.x86_64
mksh-56c-1.10.x86_64
sysstat-12.0.2-3.3.1.x86_64
xorg-x11-fonts-core-7.6-3.9.noarch
xorg-x11-server-extra-1.19.6-8.6.1.x86_64
xorg-x11-Xvnc-1.8.0-13.8.5.x86_64
xorg-x11-server-1.19.6-8.6.1.x86_64
xorg-x11-libs-7.6.1-1.16.noarch
xorg-x11-essentials-7.6_1-1.22.noarch
xorg-x11-fonts-7.6-3.9.noarch
xorg-x11-7.6_1-1.22.noarch
xorg-x11-driver-video-7.6_1-2.30.x86_64
SUSE Linux Enterprise Server 12:
Centos7.6安装包:
[root@server1 ~]# yum install bc binutils compat-libcap1 compat-libstdc++ glibc glibc-devel ksh libaio libaio-devel libgcc libstdc++ libstdc++-devel libxcb libX11 libXau libXi libXtst libXrender libXrender-devel make gcc gcc-c++ smartmontools sysstat
查询依赖包:
[root@server1 ~]# rpm -q --qf '%{NAME}-%{VERSION}-%{RELEASE} (%{ARCH})\n' \
bc \
binutils \
compat-libcap1 \
compat-libstdc++ \
glibc \
glibc-devel \
ksh \
libaio \
libaio-devel \
libgcc \
libstdc++ libstdc++-devel \
libxcb libX11 \
libXau \
libXi \
libXtst \
libXrender \
libXrender-devel \
make gcc gcc-c++ smartmontools sysstat |grep 'not installed'
解决compat-libstdc++没有安装的问题:
package compat-libstdc++ is not installed
[root@server1 ~]# rpm -q --qf '%{NAME}-%{VERSION}-%{RELEASE} (%{ARCH})\n' compat-libstdc++-33
[root@server1 ~]# wget http://mirror.centos.org/centos/7/os/x86_64/Packages/compat-libstdc++-33-3.2.3-72.el7.x86_64.rpm
[root@server1 ~]# yum install compat-libstdc++-33-3.2.3-72.el7.x86_64.rpm
9.创建用户组和Oracle安装目录
如果没有使用到ASM，则不需要创建asmdba,asmoper
/usr/sbin/groupadd -g 54327 asmdba
/usr/sbin/groupadd -g 54328 asmoper
创建组:
/usr/sbin/groupadd -g 54321 oinstall
/usr/sbin/groupadd -g 54322 dba
/usr/sbin/groupadd -g 54323 oper
/usr/sbin/groupadd -g 54324 backupdba
/usr/sbin/groupadd -g 54325 dgdba
/usr/sbin/groupadd -g 54326 kmdba
/usr/sbin/groupadd -g 54330 racdba
创建用户:
# /usr/sbin/useradd -u 54321 -g oinstall -G dba,oper,backupdba,dgdba,kmdba,racdba oracle
修改用户属主:
# /usr/sbin/usermod -g oinstall -G dba,asmdba,backupdba,dgdba,kmdba,racdba[,oper] oracle
设置Oracle用户密码:
# passwd oracle
10.创建目录
mkdir -p /opt/oracle19c/product/19.3.0/db_1
chown -R oracle.oinstall /opt/oracle19c
chmod -R 755 /opt/oracle19c
mkdir /opt/ora19cData
chown oracle.oinstall /opt/ora19cData
chmod 755 /opt/ora19cData
mkdir /opt/ora19cArc
chown oracle.oinstall /opt/ora19cArc
chmod 755 /opt/ora19cArc
11.配置Oracle环境变量
[root@server1 ~]# su - oracle
[oracle@server1 ~]$ vi .bash_profile
export TEMP=/tmp
export TMPDIR=/tmp
export ORACLE_BASE=/opt/oracle19c
export ORACLE_HOME=$ORACLE_BASE/product/19.3.0/db_1
export ORACLE_SID=adg
export LD_LIBRARY_PATH=$ORACLE_HOME/lib
export PATH=$ORACLE_HOME/bin:$PATH
umask 022
[root@server1 ~]# source .bash_profile
如果不是全新安装，则需要unset环境变量:
unset ORACLE_BASE
unset ORACLE_HOME
unset ORACLE_SID
unset ORA_CRS_HOME
unset ORA_NLS10
unset TNS_ADMIN
移除环境变量PATH中$ORACLE_HOME/bin
12.配置资源限制
[root@server1 ~]# vi /etc/security/limits.conf
注意: centos7.6添加路径为/etc/security/limits.d/，级别优先/etc/security/
[root@server1 ~]# vi /etc/security/limits.d/20-nproc.conf
oracle soft nofile 1024
oracle hard nofile 65536
oracle soft nproc 2047
oracle hard nproc 16384
oracle soft stack 10240
oracle hard stack 32768
oracle hard memlock 134217728
oracle soft memlock 134217728
oracle soft memlock (使用HugePage:90%Mem，单位KB.不使用HuagePages:3145728 (3G)，maximum of 128GB on x86_64 or 3GB on x86 OR 90 % of RAM)
oracle hard memlock (使用HugePage:90%Mem，单位KB.不使用HuagePages:3145728 (3G)，maximum of 128GB on x86_64 or 3GB on x86 OR 90 % of RAM)
要使 limits.conf 文件配置生效，必须要确保 pam_limits.so 文件被加入到启动文件中(Oracle19c没有要求):
[root@server1 ~]# locate pam_limits.so
/usr/lib64/security/pam_limits.so
[root@server1 ~]# vi /etc/pam.d/login
session required pam_limits.so
1. Log in as an installation owner.
2. Check the soft and hard limits for the file descriptor setting. Ensure that the result
is in the recommended range. For example:
[root@server1 ~]# su - oracle
[oracle@server1 ~]$ ulimit -Sn
1024
[oracle@server1 ~]$ ulimit -Hn
65536
3. Check the soft and hard limits for the number of processes available to a user.
Ensure that the result is in the recommended range. For example:
[oracle@server1 ~]$ ulimit -Su
2047
[oracle@server1 ~]$ ulimit -Hu
16384
4. Check the soft limit for the stack setting. Ensure that the result is in the
recommended range. For example:
[oracle@server1 ~]$ ulimit -Ss
10240
[oracle@server1 ~]$ ulimit -Hs
32768
提高用户shell限制(不做此步):
/etc/profile
if [ $USER = "oracle" ]; then
if [ $SHELL = "/bin/ksh" ]; then
ulimit -p 16384
ulimit -n 65536
else
ulimit -u 16384 -n 65536
fi
fi
13.配置内核参数
[root@server1 ~]# vi /etc/sysctl.d/97-oracle-database-sysctl.conf
net.ipv4.neigh.default.base_reachable_time_ms = 600000
net.ipv4.neigh.default.mcast_solicit = 20
net.ipv4.neigh.default.retrans_time_ms = 250
net.ipv4.conf.eth0.rp_filter=0
net.ipv4.conf.eth1.rp_filter=0
net.core.default_qdisc=fq
net.ipv4.tcp_congestion_control=bbr
net.ipv4.tcp_fastopen=3
net.core.default_qdisc = fq
net.ipv4.tcp_congestion_control = bbr
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
执行以下命令使用内核生效:
[root@server1 ~]# /sbin/sysctl --system
[root@server1 ~]# /sbin/sysctl -a
net.ipv4.tcp_rmem = 4096 87380 4194304
net.ipv4.tcp_wmem = 4096 16384 4194304
14.针对隐藏文件配置错误输出
如果安装RAC或Grid需要配置:
During an Oracle Grid Infrastructure installation, OUI uses SSH to run commands and
copy files to the other nodes. During the installation, hidden files on the system (for
example, .bashrc or .cshrc) can cause makefile and other installation errors if they
contain terminal output commands.
To avoid this problem, you must modify hidden files in each Oracle installation owner
user home directory to suppress all output on STDOUT or STDERR (for example, stty,
xtitle, and other such commands) as in the following examples:
Bourne, Bash, or Korn shell:
if [ -t 0 ]; then
stty intr ^C
fi
C shell:
test -t 0
if ($status == 0) then
stty intr ^C
endif
note:If the remote shell can load hidden files that contain stty commands, then
OUI indicates an error and stops the installation.
[oracle@server1 ~]$ vi .bash_profile
if [ -t 0 ]; then
stty intr ^C
fi
[oracle@server1 ~]$ source .bash_profile
15.安装Oracle19c
[root@server1 soft]# unzip LINUX.X64_193000_db_home.zip -d /opt/oracle19c/product/19.3.0/db_1/
[root@server1 ~]# chown -R oracle.oinstall /opt/oracle19c/
[root@server1 ~]# chmod -R 755 /opt/oracle19c/
[oracle@server1 ~]$ export DISPLAY=192.168.1.100:0.0
[oracle@server1 ~]$ cd /opt/oracle19c/product/19.3.0/db_1/
[oracle@server1 db_1]$ ./runInstaller
ERROR: Could not validate ownership of the Oracle home software.
Verify that the software is not corrupt.
检查发现是权限问题:
[root@server1 db_1]# ll
total 64
drwxr-xr-x 2 755 oinstall 102 Apr 18 2019 addnode
drwxr-xr-x 6 755 oinstall 4096 Apr 17 2019 apex
drwxr-xr-x 9 755 oinstall 93 Apr 17 2019 assistants
drwxr-xr-x 2 755 oinstall 8192 Apr 18 2019 bin
drwxr-xr-x 4 755 oinstall 87 Apr 18 2019 clone
[root@server1 ~]# chown -R oracle.oinstall /opt/oracle19c/
[root@server1 ~]# chmod -R 755 /opt/oracle19c/
[oracle@server1 ~]$ export DISPLAY=192.168.1.100:0.0
[oracle@server1 ~]$ cd $ORACLE_HOME/
[oracle@server1 ~]$ ./runInstaller
root用户执行如下脚本:
/home/oracle/oraInventory/orainstRoot.sh
/opt/oracle19c/product/19.3.0/db_1/root.sh
[root@server1 ~]# /home/oracle/oraInventory/orainstRoot.shChanging permissions of /home/oracle/oraInventory.Adding read,write permissions for group.Removing read,write,execute permissions for world.Changing groupname of /home/oracle/oraInventory to oinstall.The execution of the script is complete.[root@server1 ~]# /opt/oracle19c/product/19.3.0/db_1/root.shPerforming root user operation.The following environment variables are set as: ORACLE_OWNER= oracle ORACLE_HOME= /opt/oracle19c/product/19.3.0/db_1Enter the full pathname of the local bin directory: [/usr/local/bin]: Copying dbhome to /usr/local/bin ... Copying oraenv to /usr/local/bin ... Copying coraenv to /usr/local/bin ...Creating /etc/oratab file...Entries will be added to the /etc/oratab file as needed byDatabase Configuration Assistant when a database is createdFinished running generic part of root script.Now product-specific root actions will be performed.Oracle Trace File Analyzer (TFA - Standalone Mode) is available at : /opt/oracle19c/product/19.3.0/db_1/bin/tfactlNote :1. tfactl will use TFA Service if that service is running and user has been granted access2. tfactl will configure TFA Standalone Mode only if user has no access to TFA Service or TFA is not installed
16.DBCA建库
[oracle@server1 database]$ dbca
17.安装后续工作
1.Download and install Release Updates (RU) and Release Update Revisions
(RUR) patches for your Oracle software after you complete installation.
2.Passwords for all Oracle system administration accounts except SYS, SYSTEM,
and DBSMP are revoked after installation. Before you use a locked account, you
must unlock it and reset its password.
3.Oracle recommends that you back up the root.sh script after you complete an
installation.
If you install other products in the same Oracle home directory subsequent to this
installation, then Oracle Universal Installer updates the contents of the existing
root.sh script during the installation. If you require information contained in the
original root.sh script, then you can recover it from the backed up root.sh file
4.To identify and recompile invalid objects on the CDB and PDBs, use the catcon utility
to run utlrp.sql after you install, patch, or upgrade a database.
Oracle recommends that you use the catcon.pl utility to run utlrp.sql on all
containers in your container database (CDB). The utlrp.sql script recompiles all
invalid objects. Run the script immediately after installation, to ensure that users do not
encounter invalid objects.
1. Change directory to Oracle_home/rdbms/admin. For example
$ cd $ORACLE_HOME/rdbms/admin
2. Use the catcon.pl script in the Oracle home to run utlrp.sql. For example:
$ORACLE_HOME/perl/bin/perl catcon.pl --n 1 --e --b utlrp --d '''.'''
utlrp.sql
Note the following conditions of this use case:
• --n parameter: is set to 1, so the script runs each PDB recompilation in
sequence.
• --e parameter: turns echo on.
• --b parameter: Sets the log file base name. It is set to utlrp.
5.Oracle ORAchk is pre-installed with Oracle Database in the $ORACLE_HOME/
suptools/orachk directory. Oracle recommends that you update to the latest
version of Oracle ORAchk. See Oracle ORAchk and EXAchk User’s Guide for more
information.
You can also download and run the latest standalone version of Oracle ORAchk from
My Oracle Support. For information about downloading, configuring and running
Oracle ORAchk utility, refer to My Oracle Support note 1268927.2:
6.Starting with Oracle Database 12c Release 2 (12.2.0.1), by default, permissions to
read and write to the System Global Area (SGA) are limited to the Oracle software
installation owner.
In previous releases, both the Oracle installation owner account and members of the
OSDBA group had access to shared memory. The change in Oracle Database 12c
Release 2 (12.2) and later releases to restrict access by default to the Oracle
installation owner account provides greater security than previous configurations.
However, this change may prevent DBAs who do not have access to the Oracle
installation owner account from administering the database.
The Oracle Database initialization parameter ALLOW_GROUP_ACCESS_TO_SGA
determines if the Oracle Database installation owner account (oracle in Oracle
documentation examples) is the only user that can read and write to the database
System Global Area (SGA), or if members of the OSDBA group can read the SGA. In
Oracle Database 12c Release 2 (12.2) and later releases, the default value for this
parameter is FALSE, so that only the Oracle Database installation owner has read and
write permissions to the SGA. Group access to the SGA is removed by default. This
change affects all Linux and UNIX platforms.
If members of the OSDBA group require read access to the SGA, then you can
change the initialization parameter ALLOW_GROUP_ACCESS_TO_SGA setting from
FALSE to TRUE. Oracle strongly recommends that you accept the default permissions
that limit access to the SGA to the oracle user account.
7.Use these steps to check the contents and directory location of an Oracle Database
installation:
1. Go to $ORACLE_HOME/oui/bin.
2. Start Oracle Universal Installer.
$ ./runInstaller
3. Click Installed Products to display the Inventory dialog box on the Welcome
screen.
4. Select an Oracle Database product from the list to check the installed contents.
5. Click Details to find additional information about an installed product.
6. Click Close to close the Inventory dialog box.
7. Click Cancel to close Oracle Universal Installer, and then click Yes to confirm.
18.Listener及tnsnames
- 配置Listener
[oracle@server1 ~]$ cd $ORACLE_HOME/network/admin/
[oracle@server1 admin]$ vi listener.ora
SID_LIST_ORCL =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = ORCL)
(SID_NAME = orcl)
(ORACLE_HOME = /opt/oracle19c/product/12.2.0/db_1)
)
(SID_DESC =
(GLOBAL_DBNAME = TESTDB)
(SID_NAME = orcl)
(ORACLE_HOME = /opt/oracle19c/product/12.2.0/db_1)
)
)
ORCL =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.165)(PORT = 15021))
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC15021))
)
)
[oracle@server1 admin]$ lsnrctl start ORCL
SID_NAME 值区分大小写，如果改成大写将无法连接，可查看当前环境变量$ORACLE_SID值。
- 配置tnsnames
[oracle@server1 admin]$ vi tnsnames.ora
TESTDB =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.165)(PORT = 15021))
)
(CONNECT_DATA =
(SERVICE_NAME = testdb)
)
)
ORCL_CDB =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.165)(PORT = 15021))
)
(CONNECT_DATA =
(SERVICE_NAME = orcl)
)
)
19.查看数据库
[oracle@server1 ~]$ sqlplus / as sysdba
SQL*Plus: Release 19.0.0.0.0 - Production on Sat Jun 19 18:22:39 2021
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle. All rights reserved.
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 XPROD READ WRITE NO
[oracle@server2 ~]$ sqlplus / as sysdba
SQL*Plus: Release 19.0.0.0.0 - Production on Sat Jun 19 18:29:00 2021
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle. All rights reserved.
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 XPROD READ WRITE NO
20.安装rlwrap
[root@server1 ~]# rpm -ivh rlwrap-0.37-1.el6.x86_64.rpm
[oracle@server1 ~]$ vi .bashrc
alias sqlplus="rlwrap sqlplus"
alias rman="rlwrap rman"
[oracle@server1 ~]$ source .bashrc
五、Dataguard配置简介
1.primary database配置
- Enable Archiving
Issue the following SQL statements:
SQL> SHUTDOWN IMMEDIATE;
SQL> STARTUP MOUNT;
SQL> ALTER DATABASE ARCHIVELOG;
SQL> ALTER DATABASE OPEN;
- Enable an Appropriate Logging Mode
There are three other modes that are ppropriate for a primary database:
1.force logging:
SQL> ALTER DATABASE FORCE LOGGING;
2.STANDBY NOLOGGING FOR DATA AVAILABILITY
SQL> ALTER DATABASE SET STANDBY NOLOGGING FOR DATA AVAILABILITY;
STANDBY NOLOGGING FOR DATA AVAILABILITY mode causes the load operation to
send the loaded data to each standby through its own connection to the standby.
The commit is delayed until all the standbys have applied the data as part of running managed recovery in an Active Data Guard environment.
3.STANDBY NOLOGGING FOR LOAD PERFORMANCE
SQL> ALTER DATABASE SET STANDBY NOLOGGING FOR LOAD PERFORMANCE;
STANDBY NOLOGGING FOR LOAD PERFORMANCE is similar to the previous mode
except that the loading process can stop sending the data to the standbys if the
network cannot keep up with the speed at which data is being loaded to the
primary. In this mode it is possible that the standbys may have missing data, but
each standby automatically fetches the data from the primary as a normal part of
running managed recovery in an Active Data Guard environment.
When you issue any of these statements, the primary database must at least be
mounted (and it can also be open). The statement can take a considerable amount of
time to complete, because it waits for all unlogged direct write I/O to finish.
note:When you enable STANDBY NOLOGGING FOR DATA AVAILABILITY or STANDBY
NOLOGGING FOR LOAD PERFORMANCE on the primary database, any standbys
that are using multi-instance redo apply functionality will stop applying redo
with the error ORA-10892. You must first restart redo apply and allow the
affected standbys to progress past the NOLOGGING operation period and
then enable multi-instance redo apply.
- Configure Redo Transport Authentication SSL
Data Guard usesOracle Net sessions to transport redo data and control messages between themembers of a Data Guard configuration. These redo transport sessions areauthenticated using either the Secure Sockets Layer (SSL) protocol or a remotelogin password file.
- Configure the Primary Database to Receive Redo Data
To create a standby redo log, use the SQL ALTER DATABASE ADD STANDBY LOGFILE
statement. For example:
SQL> ALTER DATABASE ADD STANDBY LOGFILE ('/oracle/dbs/slog1.rdo') SIZE 500M;
SQL> ALTER DATABASE ADD STANDBY LOGFILE ('/oracle/dbs/slog2.rdo') SIZE 500M;
- Set Primary Database Initialization Parameters
DB_NAME=chicago
DB_UNIQUE_NAME=chicago
LOG_ARCHIVE_CONFIG='DG_CONFIG=(chicago,boston)'
CONTROL_FILES='/arch1/chicago/control1.ctl', '/arch2/chicago/control2.ctl'
LOG_ARCHIVE_DEST_1=
'LOCATION=USE_DB_RECOVERY_FILE_DEST
VALID_FOR=(ALL_LOGFILES,ALL_ROLES)
DB_UNIQUE_NAME=chicago'
LOG_ARCHIVE_DEST_2=
'SERVICE=boston ASYNC
VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE)
DB_UNIQUE_NAME=boston'
REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE
LOG_ARCHIVE_FORMAT=%t_%s_%r.arc
FAL_SERVER=boston
DB_FILE_NAME_CONVERT='/boston/','/chicago/'
LOG_FILE_NAME_CONVERT='/boston/','/chicago/'
STANDBY_FILE_MANAGEMENT=AUTO
note:Review the initialization parameter file for additional parameters that may
need to be modified. For example, you may need to modify the dump
destination parameters if the directory location on the standby database is
different from those specified on the primary database.
- Create a Backup Copy of the Primary Database Data Files
You can use any backup copy of the primary database to create the physical standby
database, as long as you have the necessary archived redo log files to completely
recover the database.
You can use any backup copy of the primary database to create the physical standby
database, as long as you have the necessary archived redo log files to completely
recover the database. Oracle recommends that you use the Recovery Manager utility
(RMAN).
- Create a Control File for the Standby Database
The following is an example of creating the control file for the standby database:
SQL> ALTER DATABASE CREATE STANDBY CONTROLFILE AS '/tmp/boston.ctl';
The ALTER DATABASE command designates the database that is to operate in the
standby role; in this case, a database named boston.
You cannot use a single control file for both the primary and standby databases. They
must each have their own file.
If a control file backup is taken on the primary and restored on a standby (or
vice versa), then the location of the snapshot control file on the restored
system is configured to be the default. (The default value for the snapshot
control file name is platform-specific and dependent on Oracle home.)
Manually reconfigure it to the correct value using the RMAN CONFIGURE
SNAPSHOT CONTROLFILE command.
- Create a Parameter File for the Standby Database
Perform the following steps:
1. Issue a SQL statement such as the following:
SQL> CREATE PFILE='/tmp/initboston.ora' FROM SPFILE;
In Set Up the Environment to Support the Standby Database, you then create a
server parameter file from this parameter file, after it has been modified to contain
parameter values appropriate for use at the physical standby database.
2. Modify the parameter values in the parameter file created in the previous step.
Although most of the initialization parameter settings in the parameter file are also
appropriate for the physical standby database, some modifications must be made.
Example 3-1 shows, in bold typeface, the parameters created earlier on the
primary that must be changed.
Example 3-1 Modifying Initialization Parameters for a Physical Standby
Database
.
.
.
DB_NAME=chicago
DB_UNIQUE_NAME=boston
LOG_ARCHIVE_CONFIG='DG_CONFIG=(chicago,boston)'
CONTROL_FILES='/arch1/boston/control1.ctl', '/arch2/boston/control2.ctl'
DB_FILE_NAME_CONVERT='/chicago/','/boston/'
LOG_FILE_NAME_CONVERT='/chicago/','/boston/'
LOG_ARCHIVE_FORMAT=log%t_%s_%r.arc
LOG_ARCHIVE_DEST_1=
'LOCATION=USE_DB_RECOVERY_FILE_DEST
VALID_FOR=(ALL_LOGFILES,ALL_ROLES)
DB_UNIQUE_NAME=boston'
LOG_ARCHIVE_DEST_2=
'SERVICE=chicago ASYNC
VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE)
DB_UNIQUE_NAME=chicago'
REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE
STANDBY_FILE_MANAGEMENT=AUTO
FAL_SERVER=chicago
Ensure the COMPATIBLE initialization parameter is set to the same value on both the
primary and standby databases. If the values differ, then redo transport services may
be unable to transmit redo data from the primary database to the standby databases.
It is always a good practice to use the SHOW PARAMETERS command to verify no other
parameters need to be changed.
- Copy Files from the Primary System to the Standby System
Ensure that all required directories are created. Use an operating system copy utility to
copy binary files from the primary system to their correct locations on the standby
system.
The binary files to copy are as follows:
• Database backup created in Create a Backup Copy of the Primary Database Data
Files
• Standby control file created in Create a Control File for the Standby Database
• Initialization parameter file created in Create a Parameter File for the Standby
Database
2.standby database配置
- Set Up the Environment to Support the Standby Database
Set up the environment by creating a Windows-based service, a password file and an
SPFILE, and setting up the Oracle Net environment.
Perform the following steps:
1. If the standby database is going to be hosted on a Windows system, then use the
ORADIM utility to create a Windows service. For example:
oradim –NEW –SID boston –STARTMODE manual
The ORADIM utility automatically determines the username for which this service
should be created and prompts for a password for that username (if that username
needs a password). See Oracle Database Platform Guide for Microsoft Windows
for more information about using the ORADIM utility.
2. Copy the remote login password file from the primary database system to the
standby database system.
This step is optional if operating system authentication is used for administrative
users and if SSL is used for redo transport authentication. If not, then copy the
remote login password file from the primary database to the appropriate directory
on the physical standby database system.
Any subsequent changes to the password file on the primary are automatically
propagated to the standby. Changes to a password file can include when
administrative privileges (SYSDG, SYSOPER, SYSDBA, and so on) are granted or
revoked, and when passwords of any user with administrative privileges is
changed. Updated password files must still be manually copied to far sync
instances because far sync instances receive redo, but do not apply it. Once the
password file is up-to-date at the far sync instance, the redo containing the
password update at the primary is automatically propagated to any standby
databases that are set up to receive redo from that far sync instance. The
password file is updated on the standby when the redo is applied.
3. Configure and start a listener on the standby system if one is not already
configured.
See Oracle Database Net Services Administrator's Guide.
4. Create Oracle Net service names.
On both the primary and standby systems, use Oracle Net Manager to create a
network service name for the primary and standby databases that are to be used
by redo transport services. The Net service names in this example are chicago
and boston.
The Oracle Net service name must resolve to a connect descriptor that uses the
same protocol, host address, port, and service that you specified when you
configured the listeners for the primary and standby databases. The connect
descriptor must also specify that a dedicated server be used.
See the Oracle Database Net Services Administrator's Guide for more information
about service names.
5. On an idle standby database, use the SQL CREATE statement to create a server
parameter file for the standby database from the text initialization parameter file
that was edited in Task 3. For example:
SQL> CREATE SPFILE FROM PFILE='initboston.ora';
If the primary database has a database encryption wallet, then copy it to the
standby database system and configure the standby database to use this wallet.
The database encryption wallet must be copied from the primary
database system to each standby database system whenever the
master encryption key is updated.
Encrypted data in a standby database cannot be accessed unless the
standby database is configured to point to a database encryption wallet
or hardware security module that contains the current master encryption
key from the primary database.
- Start the Physical Standby Database
These are the steps to start the physical standby database and Redo Apply.
1. On the standby database, issue the following SQL statement to start and mount
the database:
SQL> STARTUP MOUNT;
2. Restore the backup of the data files taken in Create a Backup Copy of the Primary
Database Data Files and copied in Copy Files from the Primary System to the
Standby System on the standby system.
3. On the standby database, issue the following command to start Redo Apply:
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE -
> DISCONNECT FROM SESSION;
The statement includes the DISCONNECT FROM SESSION option so that Redo Apply
runs in a background session.
3.验证
- Verify the Physical Standby Database Is Performing Properly
On the standby database, query the V$DATAGUARD_PROCESS view to verify that redo is
being transmitted from the primary database and applied to the standby database.
SQL> SELECT ROLE, THREAD#, SEQUENCE#, ACTION FROM V$DATAGUARD_PROCESS;
ROLE THREAD# SEQUENCE# ACTION
------------------------ ---------- ---------- ------------
RFS ping 1 9 IDLE
recovery apply slave 0 0 IDLE
recovery apply slave 0 0 IDLE
managed recovery 0 0 IDLE
recovery logmerger 1 9 APPLYING_LOG
RFS archive 0 0 IDLE
RFS async 1 9 IDLE
The recovery logmerger role shows that redo is being applied at the standby
Oracle recommends that you use the V$DATAGUARD_PROCESS view instead of
the V$MANAGED_STANDBY view, which is deprecated as of Oracle Database
12c Release 2 (12.2.0.1) and may be desupported in a future release.
4.后续配置
After the physical standby database is running, you can upgrade the data protection
mode, and you can enable Flashback Database.
1.Upgrade the data protection mode
2.Enable Flashback Database
You can enable Flashback Database on the primary database, the
standby database, or both.
六、正式安装DataGuard
1.环境
| primary db | standby db | |
|---|---|---|
| IP | 192.168.1.141 | 192.168.1.142 |
| ROLE | primary | standby |
| HostName | server1 | server2 |
| DB_NAME | adg | adg |
| DB_UNIQUE_NAME | oradg1 | oradg2 |
| TNS | primary | standby |
| PDB | xprod | |
| DataFile | /opt/ora19cData/ADG | /opt/oraData/ADG |
| ArchiveLog | /opt/ora19cArc | /opt/oraArc |
2.检查Dataguard是否安装
SQL> col value for a20
SQL> select * from v$option where parameter = 'Oracle Data Guard';
PARAMETER VALUE CON_ID
---------------------------------------------------------------- -------------------- ----------
Oracle Data Guard TRUE 0
3.主库配置
- 开启归档
If it is noarchivelog mode, switch is to archivelog mode.
[oracle@server1 ~]$ mkdir -p /opt/ora19cArc
SQL> shutdown immediate
SQL> startup mount
SQL> alter system set log_archive_dest_1='/opt/ora19cArc' scope=spfile;
SQL> alter database archivelog;
SQL> alter database open;
SQL> archive log list
- 开启logging
SQL> select log_mode,force_logging from v$database;
LOG_MODE FORCE_LOGGING
------------ ---------------------------------------
ARCHIVELOG NO
SQL> alter database force logging;
Database altered.
SQL> select log_mode,force_logging from v$database;
LOG_MODE FORCE_LOGGING
------------ ---------------------------------------
ARCHIVELOG YES
- 创建standby logfile
查询主库当前的redo logfile 的数量
SQL> select thread#,group#,members,bytes/1024/1024 from v$log;
在主库建立与以上数量+1 大小相同的redo logfile
SQL> set linesize 200 pagesize 200
SQL> col member for a40
SQL> select * from v$logfile;
GROUP# STATUS TYPE MEMBER IS_ CON_ID
---------- ------- ------- ------------------------------ --- ----------
3 ONLINE /opt/ora19cData/ADG/redo03.log NO 0
2 ONLINE /opt/ora19cData/ADG/redo02.log NO 0
1 ONLINE /opt/ora19cData/ADG/redo01.log NO 0
SQL> alter database add standby logfile group 11 ('/opt/ora19cData/ADG/standby_redo01.log') size 200m;
Database altered.
SQL> alter database add standby logfile group 12 ('/opt/ora19cData/ADG/standby_redo02.log') size 200m;
Database altered.
SQL> alter database add standby logfile group 13 ('/opt/ora19cData/ADG/standby_redo03.log') size 200m;
Database altered.
SQL> alter database add standby logfile group 14 ('/opt/ora19cData/ADG/standby_redo04.log') size 200m;
Database altered.
SQL> set linesize 200 pagesize 200
SQL> col member for a40
SQL> select * from v$logfile;
GROUP# STATUS TYPE MEMBER IS_ CON_ID
---------- ------- ------- ---------------------------------------- --- ----------
3 ONLINE /opt/ora19cData/ADG/redo03.log NO 0
2 ONLINE /opt/ora19cData/ADG/redo02.log NO 0
1 ONLINE /opt/ora19cData/ADG/redo01.log NO 0
11 STANDBY /opt/ora19cData/ADG/standby_redo01.log NO 0
12 STANDBY /opt/ora19cData/ADG/standby_redo02.log NO 0
13 STANDBY /opt/ora19cData/ADG/standby_redo03.log NO 0
14 STANDBY /opt/ora19cData/ADG/standby_redo04.log NO 0
SQL> select group#,thread#,sequence#,archived,status from v$standby_log;
GROUP# THREAD# SEQUENCE# ARC STATUS
---------- ---------- ---------- --- ----------
11 0 0 YES UNASSIGNED
12 0 0 YES UNASSIGNED
13 0 0 YES UNASSIGNED
14 0 0 YES UNASSIGNED
删除standby log:
alter database drop logfile group 11;
- 配置主库参数
SQL> create pfile='/opt/oracle19c/pfile.ora' from spfile;
[oracle@server1 oracle19c]$ vi /opt/oracle19c/pfile.ora
adg.__data_transfer_cache_size=0
adg.__db_cache_size=553648128
adg.__inmemory_ext_roarea=0
adg.__inmemory_ext_rwarea=0
adg.__java_pool_size=16777216
adg.__large_pool_size=16777216
adg.__oracle_base='/opt/oracle19c'#ORACLE_BASE set from environment
adg.__pga_aggregate_target=637534208
adg.__sga_target=939524096
adg.__shared_io_pool_size=50331648
adg.__shared_pool_size=285212672
adg.__streams_pool_size=0
adg.__unified_pga_pool_size=0
*.audit_file_dest='/opt/oracle19c/admin/adg/adump'
*.audit_trail='db'
*.compatible='19.0.0'
*.CONTROL_FILES='/opt/ora19cData/ADG/control01.ctl','/opt/ora19cData/ADG/control02.ctl'
*.db_block_size=8192
*.DB_NAME='adg'
*.diagnostic_dest='/opt/oracle19c'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=adgXDB)'
*.enable_pluggable_database=true
*.LOG_ARCHIVE_FORMAT='%t_%s_%r.arc'
*.memory_target=1500m
*.nls_language='AMERICAN'
*.nls_territory='AMERICA'
*.open_cursors=300
*.processes=300
#Oracle19c DataGuard Parameter
DB_NAME=adg
DB_UNIQUE_NAME=oradg1
LOG_ARCHIVE_CONFIG='DG_CONFIG=(oradg1,oradg2)'
CONTROL_FILES='/opt/ora19cData/ADG/control01.ctl','/opt/ora19cData/ADG/control02.ctl'
LOG_ARCHIVE_DEST_1= 'LOCATION=/opt/ora19cArc VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=oradg1'
LOG_ARCHIVE_DEST_2='SERVICE=standby ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=oradg2'
REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE
LOG_ARCHIVE_FORMAT=%t_%s_%r.arc
FAL_SERVER=oradg2
DB_FILE_NAME_CONVERT='/opt/oraData/','/opt/ora19cData/'
LOG_FILE_NAME_CONVERT='/opt/oraData/','/opt/ora19cData/'
STANDBY_FILE_MANAGEMENT=AUTO
查看保护模式:
SQL> 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
---如果不是最大性能模式，则：（目前不需要修改）
SQL> alter database set standby database to MAXIMIZE PERFORMANCE;
Database altered.
Maximum Availability Maximum Performance Maximum Protection
--------------------------------------------------------------------------------------------
AFFIRM or NOAFFIRM NOAFFIRM AFFIRM
SYNC ASYNC SYNC
设置转换模式:
ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE {AVAILABILITY | PERFORMANCE | PROTECTION};
参数说明:
SERVICE=oradg2 这个指tnsnames名字
DB_FILE_NAME_CONVERT='/opt/oraData/','/opt/ora19cData/'，第一个参数是standby datafile目录，第二个参数是primary datafile目录
初始化参数LOG_ARCHIVE_CONFIG用于控制发送归档日志到远程位置、接收远程归档日志，并指定Data Guard配置的惟一数据库名，默认值为SEND，RECEIVE，NODG_CONFIG。
当设置该参数为SEND时，会激活发送归档日志到远程位置；当设置该能数为NOSEND时，会禁止发送归档日志到远程位置；当设置该参数为RECEIVE时，会激活接收远程归档日志；当设置该参数为NORECEIVE时，会禁止接收远程归档日志；当设置该参数为DG_CONFIG时，可以最多指定9个惟一数据库名；当设置该参数为NODG_CONFIG时，会禁止指定惟一数据库名。
该初始化参数是动态参数，可以使用ALTER SYSTEM命令进行修改。示例如下：
SQL>ALTER SYSTEM SET log_archive_config='SEND';
- 配置listener和tnsnames
listener.ora:
SID_LIST_ADG =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = ADG)
(SID_NAME = adg)
(ORACLE_HOME = /opt/oracle19c/product/19.3.0/db_1)
)
(SID_DESC =
(GLOBAL_DBNAME = XPROD)
(SID_NAME = adg)
(ORACLE_HOME = /opt/oracle19c/product/19.3.0/db_1)
)
)
ADG =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.141)(PORT = 15021))
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC15021))
)
)
[oracle@server1 admin]$ lsnrctl start ADG
tnsnames.ora:
primary =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.141)(PORT = 15021))
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = adg)
)
)
standby =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.142)(PORT = 15021))
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = adg)
(UR=A)
)
)
xprod =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.141)(PORT = 15021))
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = xprod)
)
)
- 创建sqlnet.ora
[oracle@server1 ~]$ cd $ORACLE_HOME/network/admin
[oracle@server1 admin]$ vi sqlnet.ora
SQLNET.ALLOWED_LOGON_VERSION_SERVER=8
SQLNET.ALLOWED_LOGON_VERSION_CLIENT=8
如果没有创建sqlnet.ora,11.2.0.3版本以下client连接Oracle19c会出现ORA-01017: invalid username/password; logon denied错误.
[oracle@server1 admin]$ sqlplus sys/xxx@primary as sysdba
SQL*Plus: Release 19.0.0.0.0 - Production on Mon Mar 23 14:44:04 2020
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle. All rights reserved.
ERROR:
ORA-01017: invalid username/password; logon denied
- 创建备份
方法一: 直接rman备份,需要创建standby controlfile
[oracle@server1 ~]$ mkdir -p /tmp/adg/rman
[oracle@server1 ~]$ rman target /
Recovery Manager: Release 19.0.0.0.0 - Production on Sun Mar 22 14:44:16 2020
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
connected to target database: ADG (DBID=1593821020)
RMAN> run {
allocate channel c1 type disk;
allocate channel c2 type disk;
sql 'alter system archive log current';
backup database format '/tmp/adg/rman/FULL_%T_%U.bak';
backup archivelog all format '/tmp/adg/rman/ARC_%T_%U.bak';
release channel c1;
release channel c2;
}
创建standby控制文件:
SQL> alter database create standby controlfile as '/tmp/adg/oradg2.ctl';
方法二:
使用Active duplicate (此步骤在备库上执行)
- 创建standby pfile
SQL> create pfile='/tmp/adg/standby_pfile.ora' from spfile;
File created.
[oracle@server1 oracle19c]$ vi /tmp/adg/standby_pfile.ora
adg.__data_transfer_cache_size=0
adg.__db_cache_size=553648128
adg.__inmemory_ext_roarea=0
adg.__inmemory_ext_rwarea=0
adg.__java_pool_size=16777216
adg.__large_pool_size=16777216
adg.__oracle_base='/opt/oracle19c'#ORACLE_BASE set from environment
adg.__pga_aggregate_target=637534208
adg.__sga_target=939524096
adg.__shared_io_pool_size=50331648
adg.__shared_pool_size=285212672
adg.__streams_pool_size=0
adg.__unified_pga_pool_size=0
*.audit_file_dest='/opt/oracle19c/admin/adg/adump'
*.audit_trail='db'
*.compatible='19.0.0'
*.CONTROL_FILES='/opt/ora19cData/ADG/control01.ctl','/opt/ora19cData/ADG/control02.ctl'
*.db_block_size=8192
*.DB_NAME='adg'
*.diagnostic_dest='/opt/oracle19c'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=adgXDB)'
*.enable_pluggable_database=true
*.LOG_ARCHIVE_FORMAT='%t_%s_%r.arc'
*.memory_target=1500m
*.nls_language='AMERICAN'
*.nls_territory='AMERICA'
*.open_cursors=300
#Oracle19c DataGuard Parameter
DB_NAME=adg
DB_UNIQUE_NAME=oradg2
LOG_ARCHIVE_CONFIG='DG_CONFIG=(oradg1,oradg2)'
CONTROL_FILES='/opt/oraData/ADG/control01.ctl', '/opt/oraData/ADG/control02.ctl'
DB_FILE_NAME_CONVERT='/opt/ora19cData/','/opt/oraData/'
LOG_FILE_NAME_CONVERT='/opt/ora19cData/','/opt/oraData/'
LOG_ARCHIVE_FORMAT=log%t_%s_%r.arc
LOG_ARCHIVE_DEST_1= 'LOCATION=/opt/oraArc VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=oradg2'
LOG_ARCHIVE_DEST_2='SERVICE=primary ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=oradg1'
REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE
STANDBY_FILE_MANAGEMENT=AUTO
FAL_SERVER=primary
参数说明:
DB_FILE_NAME_CONVERT='/opt/ora19cData/','/opt/ora19cData/'
LOG_FILE_NAME_CONVERT='/opt/ora19cData/','/opt/ora19cData/'
第一个参数是primary库数据路径，第二参数是standby库数据路径
- 重启主库
SQL> shutdown immediate
SQL> startup mount pfile='/opt/oracle19c/pfile.ora'
SQL> create spfile from pfile='/opt/oracle19c/pfile.ora';
SQL> alter database open;
- 复制密码文件
[oracle@server1 ~]$ cd $ORACLE_HOME/dbs
[oracle@server1 dbs]$ scp orapwadg 192.168.1.142:`pwd`
如需重置密码使用以下命令:
[oracle@server2 dbs]$ cd $ORACLE_HOME/dbs
[oracle@server2 dbs]$ orapwd file=orapwadg password=oracle_123t ignorecase=y format=12 entries=5 force=y
- 复制备份、控制文件、pfile到从库
[oracle@server1 ~]$ scp -r /tmp/adg 192.168.1.142:/tmp4.备库配置
- 依据standby_pfile在备库创建所需目录
[oracle@server2 ~]$ mkdir -p /opt/oraData/ADG
[oracle@server2 ~]$ mkdir -p /opt/oraArc
[root@server2 ~]# chown -R oracle.oinstall /opt/oraData
[root@server2 ~]# chmod 755 /opt/oraData
[root@server2 ~]# chown oracle.oinstall /opt/oraArc
[root@server2 ~]# chmod 755 /opt/oraArc
如果需要开启闪回，则创建闪回目录:
[oracle@server2 ~]$ mkdir -p $ORACLE_BASE/fast_recovery_area
- 创建密码文件
在主库配置中已复制密码文件至备库，已经配置- 配置Listener和tnsnames
listener.ora:
[oracle@server2 admin]$ vi listener.ora
SID_LIST_ADG =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = ADG)
(SID_NAME = adg)
(ORACLE_HOME = /opt/oracle19c/product/19.3.0/db_1)
)
(SID_DESC =
(GLOBAL_DBNAME = XPROD)
(SID_NAME = adg)
(ORACLE_HOME = /opt/oracle19c/product/19.3.0/db_1)
)
)
ADG =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.142)(PORT = 15021))
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC15021))
)
)
[oracle@server2 admin]$ lsnrctl start ADG
tnsnames.ora:
[oracle@server2 admin]$ vi tnsnames.ora
primary =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.141)(PORT = 15021))
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = adg)
)
)
standby =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.142)(PORT = 15021))
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = adg)
(UR=A)
)
)
xprod =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.142)(PORT = 15021))
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = xprod)
)
)
- 创建sqlnet.ora
[oracle@server1 ~]$ cd $ORACLE_HOME/network/admin
[oracle@server1 admin]$ vi sqlnet.ora
SQLNET.ALLOWED_LOGON_VERSION_SERVER=8
SQLNET.ALLOWED_LOGON_VERSION_CLIENT=8
如果没有创建sqlnet.ora,11.2.0.3版本以下client连接Oracle19c会出现ORA-01017: invalid username/password; logon denied错误.
[oracle@server1 admin]$ sqlplus sys/xxx@primary as sysdba
SQL*Plus: Release 19.0.0.0.0 - Production on Mon Mar 23 14:44:04 2020
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle. All rights reserved.
ERROR:
ORA-01017: invalid username/password; logon denied
- 启动备库mount
1.启动nomount
[oracle@server2 oracle19c]$ cd /tmp/adg/
SQL> startup nomount pfile='standby_pfile.ora'
SQL> create spfile from pfile='/tmp/adg/standby_pfile.ora';
如果使用方法二:
使用Active duplicate (则在此步执行恢复，跳至步骤6.rman恢复方法二)
2.启动mount
SQL> show parameter control
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
control_file_record_keep_time integer 7
control_files string /opt/oraData/ADG/control01.ctl
, /opt/oraData/ADG/control02.c
tl
control_management_pack_access string DIAGNOSTIC+TUNING
[oracle@server2 ~]$ cp /tmp/adg/oradg2.ctl /opt/oraData/ADG/control01.ctl
[oracle@server2 ~]$ cp /tmp/adg/oradg2.ctl /opt/oraData/ADG/control02.ctl
SQL> alter database mount;
Database altered.
- RMAN恢复
[oracle@server2 adg]$ rman target /
Recovery Manager: Release 19.0.0.0.0 - Production on Sun Jun 20 13:44:16 2021
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
connected to target database: ADG (DBID=1633986556, not open)
RMAN> run {
allocate channel c1 type disk;
allocate channel c2 type disk;
restore database;
recover database;
release channel c1;
release channel c2;
}
恢复方法二:
使用Active duplicate
[oracle@server2 ADG]$ rman target sys/xxxx@primary auxiliary sys/xxxx@standby
Recovery Manager: Release 19.0.0.0.0 - Production on Mon Mar 23 16:34:09 2020
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
connected to target database: ADG (DBID=1593821020)
connected to auxiliary database: ADG (not mounted)
RMAN> duplicate target database for standby from active database nofilenamecheck dorecover;
- 打开备库
SQL> alter database open;
Database altered.
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 XPROD MOUNTED
SQL> alter pluggable database xprod open;
Pluggable database altered.
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 XPROD READ ONLY NO
- 应用日志
查看日志应用情况:
col name for a70
set linesize 200 pagesize 200
select sequence#,name,archived,standby_dest,applied from v$archived_log order by 1;
在备库执行应用日志语句:
SQL> alter database recover managed standby database disconnect;
或者
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING ARCHIVED LOGFILE
DISCONNECT;
(As of Oracle Database 12c Release 1 (12.1), the
USING CURRENT LOGFILE clause is deprecated and no longer necessary to start
real-time apply.)
停止应用日志命令:
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
- 验证dataguard
SQL> set linesize 100 pagesize 100
SQL> select role,thread#,sequence#,action from v$dataguard_process;
ROLE THREAD# SEQUENCE# ACTION
------------------------ ---------- ---------- ------------
log writer 0 0 IDLE
redo transport monitor 0 0 IDLE
gap manager 0 0 IDLE
redo transport timer 0 0 IDLE
archive local 0 0 IDLE
archive redo 0 0 IDLE
archive redo 0 0 IDLE
archive redo 0 0 IDLE
RFS ping 1 20 IDLE
RFS async 1 20 IDLE
RFS archive 0 0 IDLE
11 rows selected.
七、DataGuard日志管理
1.如果日志传送不正常，主库检查v$archive_dest视图
select switchover_status from v$database;
col dest_name for a20
col error for a70
set linesiz 200 pagesize 200
select dest_id,dest_name,status,error from v$archive_dest;
2.查看当前角色
SQL> select database_role,open_mode,switchover_status from v$database;
DATABASE_ROLE OPEN_MODE SWITCHOVER_STATUS
---------------- -------------------- --------------------
PRIMARY READ WRITE TO STANDBY
SQL> select database_role,open_mode,switchover_status from v$database;
DATABASE_ROLE OPEN_MODE SWITCHOVER_STATUS
---------------- -------------------- --------------------
PHYSICAL STANDBY READ ONLY RECOVERY NEEDED
FAILED DESTINATION解决办法:
SQL> select switchover_status from v$database;
SWITCHOVER_STATUS
--------------------
FAILED DESTINATION
查看数据库告警日志:
tail -200 /opt/oracle19c/diag/rdbms/oradg1/adg/trace/al*
注意: oradg1为db_unique_name
2020-03-22T18:01:19.026893+08:00
TT00 (PID:8985): Error 1034 received logging on to the standby
more /opt/oracle19c/diag/rdbms/oradg1/adg/trace/adg_arc1_30588.trc
*** 2020-03-22T19:18:40.860650+08:00 (CDB$ROOT(1))
krsu_wallet_pwd_connect: OCISessionBegin failed. Error -1
krsu_dump_oci_emsg: Detailed OCI error val is 1017 and errmsg is 'ORA-01017: invalid username/password; logon denied
'
krsu_upi_status: Error 16494 attaching RFS server to standby instance at host 'oradg2'
krsi_verify_network: Error 16494 attaching to LOG_ARCHIVE_DEST_2 standby host oradg2
这个是orapwd密码文件认证问题，添加sqlnet.ora即可解决ORA-01017错误:
SQLNET.ALLOWED_LOGON_VERSION_SERVER=8
SQLNET.ALLOWED_LOGON_VERSION_CLIENT=8
col dest_name for a20
col error for a70
set linesiz 200 pagesize 200
select dest_id,dest_name,status,error from v$archive_dest;
3.应用日志
在备库执行以下语句:
SQL> startup mount
SQL> alter database recover managed standby database disconnect from session;
停止应用:
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
4.查看dataguard进程
set linesize 100 pagesize 100
select role,thread#,sequence#,action from v$dataguard_process;
5.查看dataguard配置
SQL> set linesize 200 pagesize 200
SQL> select * from v$dataguard_config;
DB_UNIQUE_NAME PARENT_DBUN DEST_ROLE CURRENT_SCN CON_ID
------------------------------ ------------------------------ ----------------- ----------- ----------
oradg1 NONE PRIMARY DATABASE 2344902 0
oradg2 oradg1 PHYSICAL STANDBY 2336944 0
6.查看日志应用状态
col name for a70
set linesize 200 pagesize 200
select sequence#,name,archived,standby_dest,applied from v$archived_log order by 1;
7.查看redo传输状况
SQL> SELECT MAX(SEQUENCE#), THREAD# FROM V$ARCHIVED_LOG WHERE RESETLOGS_CHANGE# = (SELECT MAX(RESETLOGS_CHANGE#) FROM
V$ARCHIVED_LOG) GROUP BY THREAD#;
MAX(SEQUENCE#) THREAD#
-------------- ----------
19 1
SQL> col DESTINATION for a50
SQL> SELECT DESTINATION, STATUS, ARCHIVED_THREAD#, ARCHIVED_SEQ# FROM V$ARCHIVE_DEST_STATUS WHERE STATUS <> 'DEFERRED' AND STATUS <> 'INACTIVE';
DESTINATION STATUS ARCHIVED_THREAD# ARCHIVED_SEQ#
-------------------------------------------------- --------- ---------------- -------------
/opt/ora19cArc VALID 1 19
standby VALID 1 18
SQL> SELECT LOCAL.THREAD#, LOCAL.SEQUENCE# FROM (SELECT THREAD#, SEQUENCE# FROM V$ARCHIVED_LOG WHERE DEST_ID=1) LOCAL WHERE LOCAL.SEQUENCE# NOT IN (SELECT SEQUENCE# FROM V$ARCHIVED_LOG WHERE DEST_ID=2 AND THREAD# = LOCAL.THREAD#);
THREAD# SEQUENCE#
---------- ----------
1 18
8.监控日志是否丢失
SQL> SELECT * FROM V$ARCHIVE_GAP;
THREAD# LOW_SEQUENCE# HIGH_SEQUENCE#
----------- ------------- --------------
1 7 10
The output from the previous example indicates that the physical standby database is
currently missing log files from sequence 7 to sequence 10 for thread 1.
Perform the following query on the primary database to locate the archived redo log
files on the primary database (assuming the local archive destination on the primary
database is LOG_ARCHIVE_DEST_1):
SQL> SELECT NAME FROM V$ARCHIVED_LOG WHERE THREAD#=1 AND
DEST_ID=1 AND SEQUENCE# BETWEEN 7 AND 10;
NAME
--------------------------------------------------------------------------------
/primary/thread1_dest/arcr_1_7.arc
/primary/thread1_dest/arcr_1_8.arc
/primary/thread1_dest/arcr_1_9.arc
八、Dataguard主从切换
To see the current role of the databases, query the DATABASE_ROLE column in the V$DATABASE view.
A database operates in one of the following mutually exclusive roles: primary or standby. Oracle Data Guard enables you to change these roles dynamically by using SQL statements, or by using either of the Oracle Data Guard broker's interfaces. Oracle Data Guard supports the following role transitions:
• Switchover Allows the primary database to switch roles with one of its standby databases. There is no data loss during a switchover. After a switchover, each database continues to participate in the Oracle Data Guard configuration with its new role.
• Failover Changes a standby database to the primary role in response to a primary database failure. If the primary database was not operating in either maximum protection mode or maximum availability mode before the failure, some data loss may occur. If Flashback Database is enabled on the primary database, it can be reinstated as a standby for the new primary database once the reason for the failure is corrected
Verify that there are no redo transport errors or redo gaps at the standby database by querying the V$ARCHIVE_DEST_STATUS view on the primary database. For example, the following query would be used to check the status of the standby database associated with LOG_ARCHIVE_DEST_2:
SQL> SELECT STATUS, GAP_STATUS FROM V$ARCHIVE_DEST_STATUS WHERE DEST_ID = 2;
STATUS GAP_STATUS
----------------------------------
VALID NO GAP
Do not proceed until the value of the STATUS column is VALID and the value of the GAP_STATUS column is NOGAP, for the row that corresponds to the standby database.
• Ensure temporary files exist on the standby database that match the temporary files on the primary database.
• Remove any delay in applying redo that may be in effect on the standby database that is set to become the new primary database. Not removing the delay results in a longer switchover time, and may cause the switchover to be disallowed.
• Before performing a switchover to a physical standby database that is in realtime query mode, consider bringing all instances of that standby database to the mounted but not open state to achieve the fastest possible role transition and to cleanly terminate any user sessions connected to the physical standby database prior to the role transition.
• When you perform a switchover from an Oracle RAC primary database to a physical standby database, it is not necessary to shut down all but one primary database instance.
Oracle Data Guard provides the V$DATAGUARD_STATS view, which you can use to
evaluate each standby database in terms of the currency of the data in the standby
database, and the time needed to perform a role transition if all available redo data is
applied to the standby database. For example:
SQL> COLUMN NAME FORMAT A24
SQL> COLUMN VALUE FORMAT A16
SQL> COLUMN DATUM_TIME FORMAT A24
SQL> SELECT NAME, VALUE, DATUM_TIME FROM V$DATAGUARD_STATS;
NAME VALUE DATUM_TIME
------------------------ ---------------- ------------------------
transport lag +00 00:00:00 06/18/2009 12:22:06
apply lag +00 00:00:00 06/18/2009 12:22:06
apply finish time +00 00:00:00.000
estimated startup time 9
This query output shows that the standby database has received and applied all
redo generated by the primary database. These statistics were computed using data
received from the primary database as of 12:22.06 on 06/18/09.
The apply lag and transport lag metrics are computed based on data received from
the primary database. These metrics become stale if communications between the
primary and standby database are disrupted. An unchanging value in the DATUM_TIME
column for the apply lag and transport lag metrics indicates that these metrics are
not being updated and have become stale, possibly due to a communications fault
between the primary and standby databases.
1.Switchover
Switchover主从切换步骤:
1.查询主备库当前状态
主库:
SQL> select database_role,switchover_status from v$database;
备库:
SQL> select database_role,switchover_status from v$database;
2.主库查看日志同步情况
SQL> SELECT STATUS, GAP_STATUS FROM V$ARCHIVE_DEST_STATUS WHERE DEST_ID = 2;
STATUS GAP_STATUS
--------- ------------------------
VALID NO GAP
3.验证主库switchover
SQL> alter database switchover to oradg2 verify;
Database altered.
4.主库切换为从库
SQL> alter database switchover to oradg2;
Database altered.
5.开启数据库
SQL> startup
ORACLE instance started.
Total System Global Area 1577055360 bytes
Fixed Size 9135232 bytes
Variable Size 956301312 bytes
Database Buffers 603979776 bytes
Redo Buffers 7639040 bytes
Database mounted.
Database opened.
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 XPROD MOUNTED
SQL> alter pluggable database xprod open;
Pluggable database altered.
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 XPROD READ ONLY NO
SQL> select database_role,switchover_status from v$database;
DATABASE_ROLE SWITCHOVER_STATUS
---------------- --------------------
PHYSICAL STANDBY TO PRIMARY
6.应用日志
SQL> alter database recover managed standby database disconnect from session;
Database altered.
SQL> select open_mode from v$database;
OPEN_MODE
--------------------
READ ONLY WITH APPLY
7.备库
主库执行switchover to oradg2后，备库会自动关闭数据库
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED MOUNTED
3 XPROD MOUNTED
SQL> alter database open;
Database altered.
SQL> alter pluggable database all open;
Pluggable database altered.
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 XPROD READ WRITE NO
2.Failover
1. If the primary database can be mounted, then flush any unsent archived and
current redo from the primary database to the standby database. If this operation
is successful, a zero data loss failover is possible even if the primary database is
not in a zero data loss data protection mode.
First, ensure that Redo Apply is active at the target standby database. Then
mount, but do not open the primary database. If the primary database cannot be
mounted, go to Step 2.
If not already done, then set up the remote LOG_ARCHIVE_DEST_n configured at
the primary to point to the target destination. (You may not have any remote
LOG_ARCHIVE_DEST_n configured if the target destination was serviced by a far
sync instance, or was a terminal standby in a cascaded configuration.) Also,
ensure that the primary can connect to the target destination by verifying that
the NET_ALIAS_TARGET_DB_NAME is valid and properly established.
SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_6='SERVICE=NET_ALIAS_TARGET_DB_NAME -
> ASYNC VALID_FOR=(online_logfile, primary_role) -
> DB_UNIQUE_NAME="target_db_unique_name"' SCOPE=memory;
SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_6=ENABLE;
It is also assumed that the LOG_ARCHIVE_CONFIG specification includes the
DB_UNIQUE_NAME of the target destination at the primary (and LOG_ARCHIVE_CONFIG
at the target destination includes the DB_UNIQUE_NAME of the primary). If not,
then add that information to the LOG_ARCHIVE_CONFIG at the primary and target
destination as required.
Issue the following SQL statement at the primary database:
SQL> ALTER SYSTEM FLUSH REDO TO target_db_name;
For target_db_name, specify the DB_UNIQUE_NAME of the standby database that is
to receive the redo flushed from the primary database.
This statement flushes any unsent redo from the primary database to the standby
database, and waits for that redo to be applied to the standby database.
If this statement completes without any errors, go to Step 5.If the statement
completes with any error, or if it must be stopped because you cannot wait any
longer for the statement to complete, continue with Step 2.
2. Query the V$ARCHIVED_LOG view on the target standby database to obtain the
highest log sequence number for each redo thread.
Role Transitions Involving Physical Standby Databases
For example:
SQL> SELECT UNIQUE THREAD# AS THREAD, MAX(SEQUENCE#) -
> OVER (PARTITION BY thread#) AS LAST from V$ARCHIVED_LOG;
THREAD LAST
---------- ----------
1 100
If possible, copy the most recently archived redo log file for each primary database
redo thread to the standby database if it does not exist there, and register it. This
must be done for each redo thread.
For example:
SQL> ALTER DATABASE REGISTER PHYSICAL LOGFILE 'filespec1';
3. Query the V$ARCHIVE_GAP view on the target standby database to determine if
there are any redo gaps on the target standby database.
For example:
SQL> SELECT THREAD#, LOW_SEQUENCE#, HIGH_SEQUENCE# FROM V$ARCHIVE_GAP;
THREAD# LOW_SEQUENCE# HIGH_SEQUENCE#
---------- ------------- --------------
1 90 92
In this example, the gap comprises archived redo log files with sequence numbers
90, 91, and 92 for thread 1.
If possible, copy any missing archived redo log files to the target standby database
from the primary database and register them at the target standby database. This
must be done for each redo thread.
For example:
SQL> ALTER DATABASE REGISTER PHYSICAL LOGFILE 'filespec1';
4. The query executed in Step 3 displays information for the highest gap only. After
resolving a gap, you must repeat the query until no more rows are returned.
If, after performing Step 2 through Step 4, you are not able to resolve all gaps in
the archived redo log files (for example, because you do not have access to the
system that hosted the failed primary database), then you can expect some data
loss during the failover.
5. Issue the following SQL statement on the target standby database:
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
6. Issue the following SQL statement on the target standby database:
SQL> ALTER DATABASE FAILOVER TO target_db_name;
For example, suppose the target standby database is named CHICAGO:
SQL> ALTER DATABASE FAILOVER TO CHICAGO;
If this statement completes without any errors, proceed to Step 10.
If there are errors, go to Step 7.
7. If an error occurs, try to resolve the cause of the error and then reissue the
statement.
Role Transitions Involving Physical Standby Databases
9-14• If successful, go to Step 10.
• If the error still occurs and it involves a far sync instance, go to Step 8.
• If the error still occurs and there is no far sync instance involved, go to Step 9.
8. This step is for far sync instance error cases only. If the error involves a far sync
instance (for example, it is unavailable) and you have tried resolving the issue and
reissuing the statement without success, then you can use the FORCE option. For
example:
SQL> ALTER DATABASE FAILVOVER TO CHICAGO FORCE;
The FORCE option instructs the failover to ignore any failures encountered when
interacting with the far sync instance and proceed with the failover, if at all
possible. (The FORCE option has meaning only when the failover target is serviced
by a far sync instance.)
If the FORCE option is successful, go to Step 10.
If the FORCE option is unsuccessful, go to Step 9.
9. Perform a data loss failover.
If an error condition cannot be resolved, a failover can still be performed (with
some data loss) by issuing the following SQL statement on the target standby
database:
SQL> ALTER DATABASE ACTIVATE PHYSICAL STANDBY DATABASE;
In the following example, the failover operation fails with an ORA-16472 error. That
error means the database is configured in MaxAvailability or MaxProtection mode
but data loss is detected during failover.
SQL> ALTER DATABASE FAILOVER TO CHICAGO;
ERROR at line 1:
ORA-16472: failover failed due to data loss
You can complete the data loss failover by issuing the following SQL statement:
SQL> ALTER DATABASE ACTIVATE PHYSICAL STANDBY DATABASE;
Database altered.
10. Open the new primary database:
SQL> ALTER DATABASE OPEN;
11. Oracle recommends that you perform a full backup of the new primary database.
12. If Redo Apply has stopped at any of the other physical standby databases in your
Data Guard configuration, then restart it. For example:
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT;
13. After a failover, the original primary database can be converted into a physical
standby database of the new primary database using the method described in
Converting a Failed Primary Into a Standby Database Using Flashback Database
or Converting a Failed Primary into a Standby Database Using RMAN Backups,
or it can be re-created as a physical standby database from a backup of the
new primary database using the method described in Step-by-Step Instructions for
Creating a Physical Standby Database.
Once the original primary database is running in the standby role, a switchover
can be performed to restore it to the primary role.