Oracle 12c DG主从复制集群搭建

oracle 12C DG主备安装手册

1 环境准备

1.1 硬件准备

角色 操作系统 CPU 内存 磁盘 缓存
主库(master) centos7.5 4核(不限制) 16G(大于2G) 100G(大于20G) 1G(数据库查询语句大于512M数据库系统概论第五版课后答案)
备库(slave) centos7.5 4核(不限制) 16G(大于2G) 100G(大于20G) 1G(大于512M)

设置关闭上oracle12c下载下文

setenforce 0

#持久化关闭上下文当归的功效与作用

[oracle@oracle-2 admin]$ cat /etc/sysconfig/selinux |grep SELINUX=
SELINUX=disabled   # 设置为disabled

关闭防火墙

systemctl disable firewall
systemctl stop firewall
systemctl status firewall

配置内核参数

[oracle@oracle-2 admin]$ cat /etc/sysctl.conf
#添加以下参数
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
fs.file-max = 6815744
#使参数生效
[oracle@oracle-2 ~]$ /sbin/sysctl -p

配置秘钥限制

#文件20-nproc.conf 因系统而异不固定
[oracle@oracle-2 ~]$ cat /etc/security/limits.d/20-nproc.conf

*          soft    nproc     4096
root       soft    nproc     unlimited
oracle   soft   nofile    1024
oracle   hard   nofile    65536
oracle   soft   nproc    16384
oracle   hard   nproc    16384
oracle   soft   stack    10240
oracle   hard   stack    32768
oracle   hard   memlock    134217728
oracle   soft   memlock    134217728

缓存设置

#如果没有设置缓存,参照下面设置缓存

#检查缓存
swapon -s
#检查文件系统
df -hal
#创建缓存文件(这个参数不要改就写512)
dd if=/dev/zero of=/swapfile bs=1024 count=512k
#格式化并激活缓存文件
mkswap /swapfile
#激活缓存
swapon /swapfile
#查看缓存概要信息
swapon -s
#设置缓存文件自动挂载vim /etc/fstab
/swapfile          swap            swap    defaults        0 0
#定义缓存用户组
chown root:root /swapfile
#设置缓存文件权限
chmod 0600 /swapfile 

1.2 软件准备

oracle 下载:https://www.oracle.com/technetwork/database/enterprise-edition/downloads/index.html

下载软件包放到/opt下oracle数据库

安装基础软件

yum install  binutils compat-libcap1 compat-libstdc++-33 gcc gcc-c++ glibc glibc-devel ksh libaio libaio-devel libgcc libstdc++ libstdc++-devel libXext libXtst libX11 libXau libxcb libXi make sysstat net-tools telnet java unzip  -y

解压安装包

#解压后在./data数据库有哪几种base 目录下

[root@oracle-1 opt]# unzip linuxx64_12201_database.zip

创建用户组并设置权限

#创建2个 oracle用户组
groupadd oinstall
groupadd dba
#oracle用户加入两个用户组
useradd -g oinstall -G dba oracle
#设置oracle用户密.码
passwd oracle
#创建oracle安装目录
mkdir -p /usr/oracle
#更改oracle安装目录用户组
chown -R oracle:oinstall /usr/oracle
#更改oracle安装目录权限
chmod -R 775 /usr/oracle
#更改当前oracle安装文件所在路径用户组
chown -R oracle:oinstall /opt/database
#更改当前oracle安装文件所在路径权限
chmod -R 755 /opt/database

设置oracle用户的环境变量

[root@test-oracle ~]# cat /home/oracle/.bash_profile
#.bash_profile
#Get the aliases and functions
if [ -f ~/.bashrc ]; then
. ~/.bashrc
fi
#User specific environment and startup programs
export PATH=$PATH:$HOME/.local/bin:$HOME/bin
export DISPLAY=172.28.96.137:0.0
export  ORACLE_BASE=/usr/oracle
export  ORACLE_HOME=$ORACLE_BASE/product/12c/db_1
export  ORACLE_SID=orcl
export  PATH=$ORACLE_HOME/bin:$PATH
export  LD_LIBRARY_PATH=$ORACLE_HOME/lib:/usr/lib
export  LANG=en_US
export TNS_ADMIN=/usr/oracle/product/12c/db_1/network/admin

配置主机域名解析/etc/hosts

[root@test-oracle ~]# cat /etc/hosts
127.0.0.1   localhost localhost.localdomain localhost4 localhost4.localdomain4
::1         localhost localhost.localdomain localhost6 localhost6.localdomain6
172.28.96.137   test-oracle.novalocal

创建配置文件

vim /etc/oraInst.loc
inventory_loc=/usr/oracle
inst_group=oinstall

2 oracle安装

2.1 oracle 12C 静默安装

切换为oracle用户并检查环境变量是否生效

[root@test-oracle ~]# su - oracle
[oracle@test-oracle ~]$ echo $ORACLE_HOME
/usr/oracle/product/12c/db_1

配置安装参数,安装参数在/opt/database/response东宫/doracle和mysql区别b_i主从复制ns短歌行tall.rsp文件中

[root@oracle-1 response]# cat db_install.rsp | grep -Ev "^#|^$"
oracle.install.responseFileVersion=/oracle/install/rspfmt_dbinstall_response_schema_v12.2.0
oracle.install.option=
UNIX_GROUP_NAME=
INVENTORY_LOCATION=/usr/oracle                             #这只为安装路径
ORACLE_HOME=/usr/oracle/product/12c/db_1                #设置为环境变量中的ORACLE_HOME
ORACLE_BASE=/usr/oracle                                    #设置为环境变量中的ORACLE_BASE
oracle.install.db.InstallEdition=
oracle.install.db.OSDBA_GROUP=dba                          #设置为dba用户组
oracle.install.db.OSOPER_GROUP=oinstall                    #设置为oinstall用户组
oracle.install.db.OSBACKUPDBA_GROUP=oinstall               #设置为oinstall用户组
oracle.install.db.OSDGDBA_GROUP=oinstall                   #设置为oinstall用户组
oracle.install.db.OSKMDBA_GROUP=oinstall                   #设置为oinstall用户组
oracle.install.db.OSRACDBA_GROUP=oinstall                  #设置为oinstall用户组
oracle.install.db.rac.configurationType=
oracle.install.db.CLUSTER_NODES=
oracle.install.db.isRACOneInstall=
oracle.install.db.racOneServiceName=
oracle.install.db.rac.serverpoolName=
oracle.install.db.rac.serverpoolCardinality=
oracle.install.db.config.starterdb.type=
#下面两个很重要
oracle.install.db.config.starterdb.globalDBName=orcl      #设置全局库名称,建议orcl
oracle.install.db.config.starterdb.SID=orcl               # 建议同上
oracle.install.db.ConfigureAsContainerDB=
oracle.install.db.config.PDBName=
oracle.install.db.config.starterdb.characterSet=
oracle.install.db.config.starterdb.memoryOption=
oracle.install.db.config.starterdb.memoryLimit=10240
oracle.install.db.config.starterdb.installExampleSchemas=
oracle.install.db.config.starterdb.password.ALL=Oracle123 #设置管理密.码必须有大写小写数字
#上面的设置了下面密.码的就不用了
oracle.install.db.config.starterdb.password.SYS=
oracle.install.db.config.starterdb.password.SYSTEM=
oracle.install.db.config.starterdb.password.DBSNMP=
oracle.install.db.config.starterdb.password.PDBADMIN=
oracle.install.db.config.starterdb.managementOption=
oracle.install.db.config.starterdb.omsHost=
oracle.install.db.config.starterdb.omsPort=
oracle.install.db.config.starterdb.emAdminUser=
oracle.install.db.config.starterdb.emAdminPassword=
oracle.install.db.config.starterdb.enableRecovery=
#文件系统就设置为FILE_SYSTEM_STORAGE
oracle.install.db.config.starterdb.storageType=FILE_SYSTEM_STORAGE
#自定义文件系统存储目录/usr/oracle/data
oracle.install.db.config.starterdb.fileSystemStorage.dataLocation=/usr/oracle/data
#同上
oracle.install.db.config.starterdb.fileSystemStorage.recoveryLocation=/usr/oracle/recovery
oracle.install.db.config.asm.diskGroup=
oracle.install.db.config.asm.ASMSNMPPassword=
MYORACLESUPPORT_USERNAME=
MYORACLESUPPORT_PASSWORD=
SECURITY_UPDATES_VIA_MYORACLESUPPORT=
DECLINE_SECURITY_UPDATES=
PROXY_HOST=
PROXY_PORT=
PROXY_USER=
PROXY_PWD=
COLLECTOR_SUPPORTHUB_URL=

开始安装

./runInstaller -silent -ignoreSysPrereqs -ignorePrereq -responseFile  /opt/database/response/db_install.rsp

#如果有报错会在显示出来请仔细查看
#正常情况下安装完毕会提示用root用户运行两个shell 脚本

2.2 创建数据库

配置创建数据库的参数/opt/database/response/dbca.rsp

[oracle@oracle-2 response]$ cat dbca.rsp | grep -Ev '^#|^$'
responseFileVersion=/oracle/assistants/rspfmt_dbca_response_schema_v12.2.0
gdbName=orcl              #全局数据库服务名称,这个很重要,建议orcl
sid=orcl                  #建议同上
# 后面都使用默认不用修改
databaseConfigType=
RACOneNodeServiceName=
policyManaged=
createServerPool=
serverPoolName=
cardinality=
force=
pqPoolName=
pqCardinality=
createAsContainerDatabase=
numberOfPDBs=
pdbName=
useLocalUndoForPDBs=
pdbAdminPassword=
nodelist=
templateName=General_Purpose.dbc
sysPassword=Oracle123
systemPassword=Oracle123
oracleHomeUserPassword=Oracle123
emConfiguration=
emExpressPort=5500
runCVUChecks=
dbsnmpPassword=
omsHost=
omsPort=
emUser=
emPassword=
dvConfiguration=
dvUserName=
dvUserPassword=
dvAccountManagerName=
dvAccountManagerPassword=
olsConfiguration=
datafileJarLocation=
datafileDestination=
recoveryAreaDestination=
storageType=
diskGroupName=
asmsnmpPassword=
recoveryGroupName=
characterSet=
nationalCharacterSet=
registerWithDirService=
dirServiceUserName=
dirServicePassword=
walletPassword=
listeners=
variablesFile=
variables=
initParams=
sampleSchema=true
memoryPercentage=
databaseType=
automaticMemoryManagement=
totalMemory=

创建数据库

#运行下面命令创建数据库,有会创建进度显示

dbca -silent -createDatabase  -responseFile /opt/database/response/dbca.rsp

查看状态

#使用sysdba 登录

sqlplus / as sysdba
#查看状态
SQL> select status from v$instance;
STATUS
------------
OPEN

配置监听

#netca.rsp 默认不需要修改

netca -silent -responsefile /opt/database/response/netca.rsp

备库创建完全相同,略

3 DG配置

3.1 主库配置

强制日志记录

#sqlplus 登录
sqlplus / as sysdba
#先关闭数据库
SQL> shutdown immediate
#启动到nomount状态
SQL> startup nomount ;
#在启动到mount状态;
SQL> alter database mount;
#配置为强日志记录
SQL> alter database force logging;    #修改数据库为强制记日志,这是必须的操作,主库的每一步操作都得记录到日志中去。
#修改为归档模式
SQL> alter database archivelog;       #修改数据库为归档模式,因为dg是通过传送归档日志到备库然后应用来保证主备库一致的

创建redo 日志文件

SQL> ALTER DATABASE ADD STANDBY LOGFILE GROUP 4 ('/usr/oracle/oradata/orcl/redo04.log') size 50M;
SQL> ALTER DATABASE ADD STANDBY LOGFILE GROUP 5 ('/usr/oracle/oradata/orcl/redo05.log') size 50M;
SQL> ALTER DATABASE ADD STANDBY LOGFILE GROUP 6 ('/usr/oracle/oradata/orcl/redo06.log') size 50M;
SQL> ALTER DATABASE ADD STANDBY LOGFILE GROUP 7 ('/usr/oracle/oradata/orcl/redo07.log') size 50M; 

查询日志文件

SQL> select group#,type,member from v$logfile;
GROUP# TYPE
---------- -------
MEMBER
--------------------------------------------------------------------------------
3 ONLINE
/usr/oracle/oradata/orcl/redo03.log
2 ONLINE
/usr/oracle/oradata/orcl/redo02.log
1 ONLINE
/usr/oracle/oradata/orcl/redo01.log
GROUP# TYPE
---------- -------
MEMBER
--------------------------------------------------------------------------------
4 STANDBY
/usr/oracle/oradata/orcl/redo04.log
5 STANDBY
/usr/oracle/oradata/orcl/redo05.log
6 STANDBY
/usr/oracle/oradata/orcl/redo06.log
GROUP# TYPE
---------- -------
MEMBER
--------------------------------------------------------------------------------
7 STANDBY
/usr/oracle/oradata/orcl/redo07.log
7 rows selected.

创建pfile

SQL> create pfile from spfile;#这里创建pfile是为了做一些主库参数的配置,并且还得拷贝到备库再次修改成备库的配置。只要通过pfile才能在主备库之间建立联系(主库和备库中的pfile相似但有区别)
#再次关闭数据库并退出
SQL> shutdown immediate;
SQL> exit

创建归档目录

#在目录/usr/or打嗝最快方法止嗝ac登鹳雀楼l东宫e/oradata/orcl 下创建(自定义,不固定)

cd /usr/oracle/oradata/orcl
mkdir archivelog

配置监听,监听文件在地瓜/usr/oracle/prod数据库原理及应用uct/12c/db_1/network/admin 下oracle集群

cd /usr/oracle/product/12c/db_1/network/admin
[oracle@oracle-2 admin]$ cat listener.ora
#listener.ora Network Configuration File: /usr/oracle/product/11.2.0/db_1/network/admin/listener.ora
#Generated by Oracle configuration tools.
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = oracle-2.novalocal)(PORT = 1521))
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
)
)
#SID_NAME = orcl 就是在安装oracle软件的配置文件db_install.rsp 和创建数据库的配置文件dbca.rsp 中配置的SID
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = orcl)
(ORACLE_HOME = /usr/oracle/product/12c/db_1)
(SID_NAME = orcl)
)
)
ADR_BASE_LISTENER = /usr/oracle
#配置tns文件
[oracle@oracle-2 admin]$ cat tnsnames.ora
#tnsnames.ora Network Configuration File: /usr/oracle/product/11.2.0/db_1/network/admin/tnsnames.ora
#Generated by Oracle configuration tools.
LISTENER_ORCL =
(ADDRESS = (PROTOCOL = TCP)(HOST = oracle-2.novalocal)(PORT = 1521))
#该链接 SERVICE_NAME = orcl 配置上创建数据库的gdbName
ORCL =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = oracle-2.novalocal)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orcl)
)
)
#该链接配置备库的链接,后面备库创建好了再配也可以
ORCL_ST =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 172.28.96.132)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = orcl)
(UR=A)
)
)
#该链接配置主库链接,和上面一样的
ORCL_PD =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 172.28.96.98)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = orcl)
(UR=A)
)
)

#监听配置好之后重启监听

lsnrctl stop
lsnrctl start
lsnrctl status

修改pfile文件,默认pfile文件在/usr/oracle/product/12c/当归db_1/dbs 下

[oracle@oracle-2 dbs]$ cat initorcl.ora
orcl.__sga_target=4999610368
orcl.__shared_io_pool_size=0
orcl.__shared_pool_size=855638016
orcl.__streams_pool_size=0
*.audit_file_dest='/usr/oracle/admin/orcl/adump'
*.audit_trail='db'
*.compatible='12.2.0'
*.control_files='/usr/oracle/oradata/orcl/control01.ctl','/usr/oracle/oradata/orcl/control02.ctl'
*.db_block_size=8192
*.db_name='orcl'
*.diagnostic_dest='/usr/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=orclXDB)'
*.nls_language='AMERICAN'
*.nls_territory='AMERICA'
*.open_cursors=300
*.pga_aggregate_target=1589m
*.processes=320
*.remote_login_passwordfile='EXCLUSIVE'
*.sga_target=4764m
*.undo_tablespace='UNDOTBS1'
#下面DB_UNIQUE_NAME 配置上上面tns文件中配置了主库链接(因为我是主库)
DB_UNIQUE_NAME=orcl_pd
#下面配置上主库和备库链接
LOG_ARCHIVE_CONFIG='DG_CONFIG=(orcl_pd,orcl_st)'
#下面设置好归档目录(就是上面自定义创建的归档目录,归档日志将存放在里面)
LOG_ARCHIVE_DEST_1=
'LOCATION=/usr/oracle/oradata/orcl/archivelog
VALID_FOR=(ALL_LOGFILES,ALL_ROLES)
#归档目录参数里面也要配置主库链接orcl_pd
DB_UNIQUE_NAME=orcl_pd'
LOG_ARCHIVE_DEST_2=
'SERVICE=orcl_st ASYNC
VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE)
#在这里配置上备库的tns链接
DB_UNIQUE_NAME=orcl_st'
LOG_ARCHIVE_DEST_STATE_1=ENABLE
LOG_ARCHIVE_DEST_STATE_2=ENABLE
REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE
LOG_ARCHIVE_FORMAT=%t_%s_%r.arc
#配置备库tns链接
FAL_SERVER=orcl_st
STANDBY_FILE_MANAGEMENT=AUTO

再次关oracle认证考试闭数据库,并oracle创建用户通过pfile启动到nomount状态,并且创建数据库有哪几种spfile

[oracle@oracle-tmp dbs]$ sqlplus / as sysdba
SQL> shutdown immediate
SQL> startup nomount pfile='/usr/oracle/product/12c/db_1/dbs/initorcl.ora';
SQL> create spfile from pfile;
SQL> alter database mount;
SQL> alter database open;

创建主数据库中未查询库的备份目录

#主库

mkdir /usr/oracle/oradata/orcl/backup

将目录/usr/oracl主从复制e/product/12c/db_1/dbs 下的口令验证文件orapworcl 和pfile文件initorcl.oracle创建用户ora 发送到备库的相同目录

[oracle@oracle-2 dbs]$ scp ./initorcl.ora 172.28.96.132:/usr/oracle/product/12c/db_1/dbs/
[oracle@oracle-2 dbs]$ scp ./orapworcl 172.28.96.132:/usr/oracle/product/12c/db_1/dbs/

3.2 备库配置

配置备库的监听

[oracle@oracle-slave ~]$ cd /usr/oracle/product/12c/db_1/network/admin
[oracle@oracle-1 admin]$ cat listener.ora
#listener.ora Network Configuration File: /usr/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 = orcl)
(ORACLE_HOME = /usr/oracle/product/12c/db_1)
(SID_NAME = orcl)
)
)
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = oracle-1.novalocal)(PORT = 1521))
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
)
)
ADR_BASE_LISTENER = /usr/oracle
#目录/usr/oracle/product/12c/db_1/network/admin 下如果没有tnsnames.ora,可以手动建
[oracle@oracle-1 admin]$ cat tnsnames.ora
#tnsnames.ora Network Configuration File: /usr/oracle/product/11.2.0/db_1/network/admin/tnsnames.ora
#Generated by Oracle configuration tools.
LISTENER_ORCL =
(ADDRESS = (PROTOCOL = TCP)(HOST = oracle-1.novalocal)(PORT = 1521))
#该链接链接备库自己
ORCL =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = oracle-1.novalocal)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orcl)
)
)
#该链接和上面一样的
ORCL_ST =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 172.28.96.132)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = orcl)
(UR=A)
)
)
#该链接链接主库
ORCL_PD =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 172.28.96.98)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = orcl)
(UR=A)
)
)

重启登高监听

lsnrctl stop
lsnrctl start

修改从主库发送过来的pf东莞天气ile

cd /usr/oracle/product/12c/db_1/dbs
[oracle@oracle-1 dbs]$ cat initorcl.ora
orcl.__data_transfer_cache_size=0
orcl.__db_cache_size=4076863488
orcl.__inmemory_ext_roarea=0
orcl.__inmemory_ext_rwarea=0
orcl.__java_pool_size=16777216
orcl.__large_pool_size=33554432
orcl.__oracle_base='/usr/oracle'#ORACLE_BASE set from environment
orcl.__pga_aggregate_target=1677721600
orcl.__sga_target=4999610368
orcl.__shared_io_pool_size=0
orcl.__shared_pool_size=855638016
orcl.__streams_pool_size=0
*.audit_file_dest='/usr/oracle/admin/orcl/adump'
*.audit_trail='db'
*.compatible='12.2.0'
*.control_files='/usr/oracle/oradata/orcl/control01.ctl','/usr/oracle/oradata/orcl/control02.ctl'
*.db_block_size=8192
*.db_domain='us.oracle.com'
*.db_name='orcl'
*.diagnostic_dest='/usr/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=orclXDB)'
*.local_listener='LISTENER_ORCL'
*.nls_language='AMERICAN'
*.nls_territory='AMERICA'
*.open_cursors=300
*.pga_aggregate_target=1589m
*.processes=320
*.remote_login_passwordfile='EXCLUSIVE'
*.sga_target=4764m
*.undo_tablespace='UNDOTBS1'
#下面两个参数要在备库上添加上
*.log_file_name_convert='/usr/oracle/oradata/orcl','/usr/oracle/oradata/orcl'
*.db_file_name_convert='/usr/oracle/oradata/orcl','/usr/oracle/oradata/orcl'
#下面这个设置为tns 中的 orcl_st 因为我是备库
DB_UNIQUE_NAME=orcl_st
LOG_ARCHIVE_CONFIG='DG_CONFIG=(orcl_pd,orcl_st)'
#备库上同样配置自定义的归档目录
LOG_ARCHIVE_DEST_1=
'LOCATION=/usr/oracle/oradata/orcl/archivelog
VALID_FOR=(ALL_LOGFILES,ALL_ROLES)
# 备库上归档日志中也配置上orcl_st
DB_UNIQUE_NAME=orcl_st'
LOG_ARCHIVE_DEST_2=
'SERVICE=orcl_st ASYNC
VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE)
# 备库中指向主库的orcl_pd
DB_UNIQUE_NAME=orcl_pd'
LOG_ARCHIVE_DEST_STATE_1=ENABLE
LOG_ARCHIVE_DEST_STATE_2=ENABLE
REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE
LOG_ARCHIVE_FORMAT=%t_%s_%r.arc
#指向主库
FAL_SERVER=orcl_pd
STANDBY_FILE_MANAGEMENT=AUTO

备库

mkdir /usr/oracle/oradata/orcl/backup

启动备库到nomount状态

sqlp / as sysdba
SQL> shutdown immediate;
SQL> startup nomount pfile="/usr/oracle/product/12c/db_1/dbs/initorcl.ora";

使用r数据库增删改查基本语句man 开始ADG

#使用rman链接主库和备库

[oracle@oracle-1 dbs]$ rman target sys/Oracle123@orcl_pd auxiliary sys/Oracle123@orcl_st

#开始同步

RMAN> duplicate target database for standby from active database nofilenamecheck;
RMAN> exit;

备库开启apply service

 [oracle@oracle-1 dbs] sqlplus / as sysdba
#打开数据库
SQL> alter database open;
#检查数据库状态
SQL> select open_mode from v$database;
OPEN_MODE
--------------------
READ ONLY
SQL> alter database recover managed standby database disconnect from session; 

4 测试

在主库创建表并插入数据

SQL> create table Csong(id number(10),name varchar2(20));
SQL> insert into Csong values(1,'Csong');
SQL> insert into Csong values(2,'Lyuanyuan');
SQL> commit;
SQL> alter system switch logfile; 

数据库系统的核心是备库检查是否同步

SQL> desc Csong;
SQL> select * from Csong;

5 注意事项

确保主库和备库监听配置oracle是什么软件正常

1 检oracle数据库查主备的IP
2 检查主备的端口(默认是1521)
3 检查tns文件中servic数据库中未查询e_name是否准确

主备库数据库安装完成后都使用德国alter database open .然后使用sqlplus 相互访问

在使用r短歌行man 做ADG的时候 注意:

1 在备库上做
2 系统要求主库处于nomount,但是这个时候rman又连主从复制原理不上主库。开启主库rman连上之后又报错主库必须要nomount,这个尴尬情况 需要有在tns配置文件中链接信打嗝最快方法止嗝息下面添加(UR=A) 使用静态链接,解决问题。
3 备库也必须使数据库查询语句用pfile 启动到nomount状态(备库不能打开)

在备库rman 做DAG的时候报错:ORA-19527

在备库的pfile中添加如下参数:

*.log_file_name_convert='/usr/oracle/oradata/orcl','/usr/oracle/oradata/orcl'
*.db_file_name_convert='/usr/oracle/oradata/orcl','/usr/oracle/oradata/orcl'

常用的错误信息分析:

oracle 报错可oracle+12c以采用命令oerr ora XXXX 来查看错误详细信息

[oracle@oracle-1 dbs]$ oerr ora 19527
19527, 00000, "physical standby redo log must be renamed"
// *Cause:  An attempt was made to clear redo log at a physical standby
//          database. Clearing an OMF redo log will not work unless
//          DB_CREATE_FILE_DEST, DB_CREATE_ONLINE_LOG_DEST_n or
//          DB_RECOVERY_FILE_DEST parameter is specified. Clearing a non-OMF
//          (or user-defined) redo log file will not work unless the
//          LOG_FILE_NAME_CONVERT initialization parameter is
//          set.
// *Action  Set DB_CREATE_FILE_DEST, DB_CREATE_ONLINE_LOG_DEST_n or
//          DB_RECOVERY_FILE_DEST parameter for OMF redo log files and
//          re-issue the operation that attempted to clear redo log. Set
//          LOG_FILE_NAME_CONVERT parameter for non-OMF redo log files and
//          re-issue the operation that attempted to clear redo log.

参考链接:h德国ttps://blog.csdn.net/weixin_34233421/article/details/92509716