admin管理员组文章数量:1531717
2023年12月29日发(作者：)
Oracle 19c Non-CDB Data Guard搭建（Linux 主备一对一 LGWR ASYNC）Oracle19c
DG
PS1:1AskScuti ：搭建类 4-0-1 Oracle 19c DG数据卫士搭建（主备一对一）Oracle 19c Non-CDB Data Guard搭建（Linux 主备一对一 LGWR ASYNC）0 预科（前提建议）1 规划2 OS安装3 数据库搭建3.1 主库搭建3.2 备库搭建4 Data Guard 搭建4.1 主库配置4.1.1 主库强制日志4.1.2 主库开启归档4.1.3 主库创建STANDBY日志组4.1.4 主库修改参数文件4.1.5 主库配置TNS4.1.6 主库配置监听（静态）4.2 备库配置4.2.1 备库修改参数文件4.2.2 备库配置TNS4.2.3 备库配置监听4.2.4 备库配置密码文件4.2.5 备库创建动态参数文件4.3 DG 创建5 备库同步测试5.1 备库 MOUNT 状态开启日志同步
5.2 备库开启 ADG0 预科（前提建议）如果Linux操作系统的安装对您来说操作困难，请先移至
0级搭建类如果在Linux操作系统上安装Oracle（图形、静默）对您来说操作困难，请先移至
1级搭建类文档步骤建立在您对上述环境相对熟悉的基础之上，否则您的操作可能有些困难1 规划规划数据库版本是否为CDB主机名IP地址DB_UNIQUE_NAMEDB_NAMEINSTANCE_NAMEData FilesLog FilesOS主库19.3.0.0.0Non-CDBhostprimary192.168.1.21erpprierppri备库19.3.0.0.0Non-CDBhoststandby192.168.1.22erpstderpprierppri/u01/app/oracle/oradata/ERPPRI/u01/app/oracle/oradata/ERPPRIOEL 7.7erpstd/u01/app/oracle/oradata/ERPSTD/u01/app/oracle/oradata/ERPSTDOEL 7.7注意：主库和备库的
DB_NAME 必须一致2 OS安装主库OS安装，请参考 Oracle Linux 7.7 安装 一文，注意主机名及IP设置（主）
备库OS安装，请参考 Oracle Linux 7.7 安装 一文，注意主机名及IP设置（备）3 数据库搭建数据库搭建相关细节可参考 1级搭建类 相关文档，以下仅记录简易代码3.1 主库搭建3.1.1 主机IP1. echo "192.168.1.21 hostprimary" >> /etc/hosts3.1.2 用户及文件夹1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
/usr/sbin/groupadd -g 65431 oinstall/usr/sbin/groupadd -g 65432 dba/usr/sbin/groupadd -g 65433 operuseradd -u 61001 -g oinstall -G dba,oper -m oracle# passwd oraclemkdir /u01mkdir -p /u01/app/oracle/product/19.3.0/dbhome_1mkdir -p /u01/app/oraInventorychown -R oracle:oinstall /u01chmod -R 775 /u013.1.3 YUM源及RPM1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
echo "[local_yum]" >> /etc/.d/ho "name = henry_repo" >> /etc/.d/ho "baseurl = file:///mnt/" >> /etc/.d/ho "enabled = 1" >> /etc/.d/ho "gpgcheck = 0" >> /etc/.d/m -y install autoconfyum -y install automakeyum -y install binutilsyum -y install binutils-develyum -y install bisonyum -y install cppyum -y install dos2unix
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
44.
45.
46.
47.
48.
49.
50.
51.
52.
53.
54.
55.
56.
57.
58.
59.
60.
yum -y install ftpyum -y install gccyum -y install gcc-c++yum -y install lrzszyum -y install python-develyum -y install compat-libcap1yum -y install compat-libstdc++-33yum -y install compat-libstdc++-33.i686yum -y install glibc-*yum -y install glibc-*.i686yum -y install libXpm-*.i686yum -y install libXextyum -y install libXext.i686yum -y install libXtstyum -y install libXtst.i686yum -y install libX11yum -y install libX11.i686yum -y install libXauyum -y install libXau.i686yum -y install libxcbyum -y install libxcb.i686yum -y install libXiyum -y install libXi.i686yum -y install libstdc++.i686yum -y install libstdc++-develyum -y install libstdc++-devel.i686yum -y install libaioyum -y install libaio.i686yum -y install libaio-develyum -y install libaio-devel.i686yum -y install kshyum -y install libXpyum -y install libaio-develyum -y install numactlyum -y install numactl-develyum -y install make -yyum -y install sysstat -yyum -y install unixODBCyum -y install unixODBC-develyum -y install elfutils-libelf-develyum -y install redhat-lsb-coreyum -y install unzipyum -y install libXrenderyum -y install libXrender-develyum -y install nfs-utilsyum -y install smartmontoolsyum -y install tigervnc
3.1.4 资源限制1.
2.
3.
4.
5.
6.
7.
# vim /etc/security/acle soft nproc 16384oracle hard nproc 16384oracle soft nofile 16384oracle hard nofile 65536oracle soft stack 10240oracle hard stack 327683.1.5 SHELL限制1. echo "session required pam_" >> /etc/pam.d/login3.1.6 内核参数1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
# vim vim /etc/-max-nr = -max = = 250 32000 100 _local_port_range = 9000 _default = _max = _default = _max = _on_oops = = = = 4096# sysctl -p3.1.7 防火墙1.
2.
3.
systemctl status esystemctl stop esystemctl disable e3.1.8 SELinux1.
2.
# vim /etc/selinux/configSELINUX=disabled3.1.9 环境变量
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
# su - oracle# vim .bash_profilePS1="[`whoami`@`hostname`:"'$PWD]$'export PS1export TMP=/tmpexport LANG=en_USexport TMPDIR=$TMPexport ORACLE_UNQNAME=erppriORACLE_SID=erppri; export ORACLE_SIDORACLE_BASE=/u01/app/oracle; export ORACLE_BASEORACLE_HOME=$ORACLE_BASE/product/19.3.0/dbhome_1; export ORACLE_HOMEORACLE_TERM=xterm; export ORACLE_TERMNLS_DATE_FORMAT="yyyy-mm-dd HH24:MI:SS"; export NLS_DATE_FORMATNLS_LANG=AMERICAN_16GBK;export NLS_LANGPATH=.:$PATH:$HOME/.local/bin:$HOME/bin:$ORACLE_HOME/bin; export PATHTHREADS_FLAG=native; export THREADS_FLAGumask=022if [ $USER = "oracle" ]; thenif [ $SHELL = "/bin/ksh" ]; thenulimit -p 16384ulimit -n 65536elseulimit -u 16384 -n 65536fiumask 022fi3.1.10 数据库软件1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
./runInstaller -ignorePrereq -waitforcompletion -silent -responseFile /u01/app/oracle/product/19.3.0/dbhome_1/install/response/db_ =INSTALL_DB_SWONLY UNIX_GROUP_NAME=oinstall INVENTORY_LOCATION=/u01/app/oraInventory SELECTED_LANGUAGES=en,en_GB ORACLE_HOME=/u01/app/oracle/product/19.3.0/dbhome_1 ORACLE_BASE=/u01/app/oracle lEdition=EE _GROUP=dba _GROUP=oper UPDBA_GROUP=dba A_GROUP=dba A_GROUP=dba BA_GROUP=dba SECURITY_UPDATES_VIA_MYORACLESUPPORT=false
17. DECLINE_SECURITY_UPDATES=true3.1.11 创建数据库1.
2.
3.
4.
5.
6.
7.
dbca -silent -createDatabase -templateName General_ -gdbname erppri -sid erppri -sysPassword oracle -systemPassword oracle -characterSet AL32UTF8 -memoryPercentage 303.2 备库搭建请按照
3.1 主库搭建 操作
3.1.1 - 3.1.10 （除 3.1.11），具体修改如下备库主机IP（对应 3.1.1）1. echo "192.168.1.22 hoststandby" >> /etc/hosts备库环境变量（对应 3.1.9）1.
2.
export ORACLE_UNQNAME=erpstdORACLE_SID=erpstd4 Data Guard 搭建4.1 主库配置4.1.1 主库强制日志1. SQL> alter database force logging;4.1.2 主库开启归档
1.
2.
3.
4.
5.
6.
SQL> archive log list;SQL> shutdown immediate;SQL> startup mount;SQL> alter database archivelog;SQL> alter system set log_archive_dest_1='location=/u02/archivelog';SQL> alter database open;4.1.3 主库创建STANDBY日志组1. # SQL> select
#,ce#,,s,,/1024/1024 M from
v$log a,v$logfile b where #=# order by #;SQL> alter system set standby_file_management=manual;SQL> alter database add standby logfile group 4
'/u01/app/oracle/oradata/ERPPRI/' size 200M;SQL> alter database add standby logfile group 5
'/u01/app/oracle/oradata/ERPPRI/' size 200M;SQL> alter database add standby logfile group 6
'/u01/app/oracle/oradata/ERPPRI/' size 200M;SQL> alter database add standby logfile group 7
'/u01/app/oracle/oradata/ERPPRI/' size 200M;SQL> alter system set standby_file_management=auto;# SQL> set linesize 200# SQL> col group# for 99# SQL> col member for a50# SQL> select group#,type,member from v$logfile order by group#; GROUP# TYPE MEMBER---------- ------- --------------------------------------------- 1 ONLINE /u01/app/oracle/oradata/ERPPRI/ 2 ONLINE /u01/app/oracle/oradata/ERPPRI/ 3 ONLINE /u01/app/oracle/oradata/ERPPRI/ 4 STANDBY /u01/app/oracle/oradata/ERPPRI/ 5 STANDBY /u01/app/oracle/oradata/ERPPRI/ 6 STANDBY /u01/app/oracle/oradata/ERPPRI/ 7 STANDBY /u01/app/oracle/oradata/ERPPRI/2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
4.1.4 主库修改参数文件1.
2.
# 通过当前动态参数文件（SPFILE）创建静态参数文件（PFILE）# !ls $ORACLE_HOME/dbs/
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
44.
45.
SQL> create pfile from spfile;# !ls $ORACLE_HOME/dbs/# 修改生成的静态参数文件 vim pri.__data_transfer_cache_size=0erppri.__db_cache_size=1811939328erppri.__inmemory_ext_roarea=0erppri.__inmemory_ext_rwarea=0erppri.__java_pool_size=0erppri.__large_pool_size=33554432erppri.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environmenterppri.__pga_aggregate_target=838860800erppri.__sga_target=2516582400erppri.__shared_io_pool_size=134217728erppri.__shared_pool_size=520093696erppri.__streams_pool_size=0erppri.__unified_pga_pool_size=0*.audit_file_dest='/u01/app/oracle/admin/erppri/adump'*.audit_trail='db'*.compatible='19.0.0'*.db_block_size=8192*.diagnostic_dest='/u01/app/oracle'*.dispatchers='(PROTOCOL=TCP) (SERVICE=erppriXDB)'*.local_listener='LISTENER_ERPPRI'*.nls_language='AMERICAN'*.nls_territory='AMERICA'*.open_cursors=300*.pga_aggregate_target=797m*.processes=640*.sga_target=2388m*.undo_tablespace='UNDOTBS1'# 以下是追加参数 #DB_NAME=erppriDB_UNIQUE_NAME=erppriLOG_ARCHIVE_CONFIG='DG_CONFIG=(erppri,erpstd)'CONTROL_FILES='/u01/app/oracle/oradata/ERPPRI/','/u01/app/oracle/oradata/ERPPRI/'LOG_ARCHIVE_DEST_1='LOCATION=/u02/archivelog VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=erppri'LOG_ARCHIVE_DEST_2='SERVICE=erpstd LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=erpstd'REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVELOG_ARCHIVE_FORMAT=%t_%s_%_CLIENT=erppriFAL_SERVER=erpstdDB_FILE_NAME_CONVERT='/u01/app/oracle/oradata/ERPSTD','/u01/app/oracle/oradata/ERPPRI'
46.
47.
LOG_FILE_NAME_CONVERT='/u01/app/oracle/oradata/ERPSTD','/u01/app/oracle/oradata/ERPPRI'STANDBY_FILE_MANAGEMENT=AUTO通过新的静态参数文件PFILE
生成新的动态参数文件SPFILE
并重新启动数据库进行参数加载1.
2.
3.
SQL> shutdown immediate;SQL> create spfile from pfile='$ORACLE_HOME/dbs/';SQL> startup4.1.5 主库配置TNS1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
# 添加备库连接字符串ERPSTD = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.22)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = erpstd) ) )4.1.6 主库配置监听（静态）1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
SID_LIST_LISTENER = (SID_LIST = (SID_DESC = (GLOBAL_DBNAME = erppri) (ORACLE_HOME = /u01/app/oracle/product/19.3.0/dbhome_1) (SID_NAME = erppri) ))# 重启监听测试连接# lsnrctl status# lsnrctl stop# lsnrctl start# sqlplus sys/oracle@erppri as sysdba
4.2 备库配置4.2.1 备库修改参数文件1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
# cd $ORACLE_HOME/dbs# 主备参数进行互换 vim std.__data_transfer_cache_size=0erpstd.__db_cache_size=1811939328erpstd.__inmemory_ext_roarea=0erpstd.__inmemory_ext_rwarea=0erpstd.__java_pool_size=0erpstd.__large_pool_size=33554432erpstd.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environmenterpstd.__pga_aggregate_target=838860800erpstd.__sga_target=2516582400erpstd.__shared_io_pool_size=134217728erpstd.__shared_pool_size=520093696erpstd.__streams_pool_size=0erpstd.__unified_pga_pool_size=0*.audit_file_dest='/u01/app/oracle/admin/erpstd/adump'*.audit_trail='db'*.compatible='19.0.0'*.db_block_size=8192*.diagnostic_dest='/u01/app/oracle'*.dispatchers='(PROTOCOL=TCP) (SERVICE=erpstdXDB)'*.local_listener='LISTENER_erpstd'*.nls_language='AMERICAN'*.nls_territory='AMERICA'*.open_cursors=300*.pga_aggregate_target=797m*.processes=640*.sga_target=2388m*.undo_tablespace='UNDOTBS1'# 注意备库 DB_NAME 必须和主库一致 #DB_NAME=erppriDB_UNIQUE_NAME=erpstdLOG_ARCHIVE_CONFIG='DG_CONFIG=(erpstd,erppri)'CONTROL_FILES='/u01/app/oracle/oradata/ERPSTD/','/u01/app/oracle/oradata/ERPSTD/'LOG_ARCHIVE_DEST_1='LOCATION=/u02/archivelog VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=erpstd'LOG_ARCHIVE_DEST_2='SERVICE=erppri LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=erppri'
37.
38.
39.
40.
41.
42.
43.
44.
45.
46.
47.
48.
49.
50.
51.
52.
REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVELOG_ARCHIVE_FORMAT=%t_%s_%_CLIENT=erpstdFAL_SERVER=erppriDB_FILE_NAME_CONVERT='/u01/app/oracle/oradata/ERPPRI','/u01/app/oracle/oradata/ERPSTD'LOG_FILE_NAME_CONVERT='/u01/app/oracle/oradata/ERPPRI','/u01/app/oracle/oradata/ERPSTD'STANDBY_FILE_MANAGEMENT=AUTO# 创建对应目录mkdir -p /u01/app/oracle/admin/erpstd/adumpmkdir /u01/app/oracle/oradata/ERPSTDmkdir /u01/app/oracle/backupsu - rootmkdir /u02/archivelogchown -R oracle:oinstall /u02chmod -R 775 /u024.2.2 备库配置TNS1.
2.
# cd $ORACLE_HOME/network/admin# scp
root@192.168.1.21:/u01/app/oracle/product/19.3.0/dbhome_1/network/admin/ .# vim 注意备库中的 （主库ERPPRI连接字符串 HOST IP 地址）ERPPRI = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.21)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = erppri) ) )3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
ERPSTD = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.22)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = erpstd) ) )
23.
24.
25.
# 尝试在备库通过连接字符串登录主库# sqlplus sys/oracle@erppri as sysdba4.2.3 备库配置监听1.
2.
# cd $ORACLE_HOME/network/admin# scp
root@192.168.1.21:/u01/app/oracle/product/19.3.0/dbhome_1/network/admin/ .# vim 注意备库中的 （对应的主机IP、GLOBAL_DBNAME、ORACLE_HOME和SID_NAME）LISTENER = (DESCRIPTION_LIST = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.22)(PORT = 1521)) (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521)) ) )SID_LIST_LISTENER = (SID_LIST = (SID_DESC = (GLOBAL_DBNAME = erpstd) (ORACLE_HOME = /u01/app/oracle/product/19.3.0/dbhome_1) (SID_NAME = erpstd) ))3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
4.2.4 备库配置密码文件1.
2.
# cd $ORACLE_HOME/dbs# scp
root@192.168.1.21:/u01/app/oracle/product/19.3.0/dbhome_1/dbs/orapwerppri
.# mv orapwerppri orapwerpstd3.
4.2.5 备库创建动态参数文件1.
2.
# 重启备库监听，并测试TNS连接字符串# lsnrctl stop
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
# lsnrctl start[oracle@hoststandby:/home/oracle]$sqlplus sys/oracle@erpstd as sysdbaSQL*Plus: Release 19.0.0.0.0 - Production on Mon Oct 28 08:33:17 2019Version 19.3.0.0.0Copyright (c) 1982, 2019, Oracle. All rights ted to an idle instance.通过
4.2.1 小节编辑的备库静态参数文件PFILE
创建备库动态参数文件 并启动备库到
NOMOUNT 状态1.
2.
3.
SQL> !ls $ORACLE_HOME/dbsSQL> create spfile from pfile='$ORACLE_HOME/dbs/';SQL> startup nomount4.3 DG 创建方案一：通过
RMAN 备份创建DG备库1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
# 主库进行 RMAN 全库备份mkdir /u01/app/oracle/backuprman target /RMAN> show all;# CONFIGURE DEVICE TYPE DISK PARALLELISM 4# CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO
'/u01/app/oracle/backup/%';RMAN> backup database format '/u01/app/oracle/backup/%s_%d_%';# 将主库备份文件移至备库相同目录中# 备库中cd /u01/app/oracle/backup/scp root@192.168.1.21:/u01/app/oracle/backup/* .# 通过 RMAN 备份还原备库# 以下命令在您可在 主库 中执行，也可在 备库 中执行RMAN> rman target sys/oracle@erppri auxiliary sys/oracle@erpstd
RMAN> duplicate target database for standby;
方案二：通过在线复制创建DG备库1.
2.
3.
4.
# 以下命令在您可在 主库 中执行，也可在 备库 中执行RMAN> rman target sys/oracle@erppri auxiliary sys/oracle@erpstd
RMAN> duplicate target database for standby from active database;本次采用后者方式创建，过程记录如下1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
Recovery Manager: Release 19.0.0.0.0 - Production on Mon Oct 28 09:35:25 2019Version 19.3.0.0.0Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights ted to target database: ERPPRI (DBID=555079172)connected to auxiliary database: ERPPRI (not mounted)RMAN> duplicate target database for standby from active database;Starting Duplicate Db at 2019-10-28 09:36:22using target database control file instead of recovery catalogallocated channel: ORA_AUX_DISK_1channel ORA_AUX_DISK_1: SID=498 device type=DISKcontents of Memory Script:{ backup as copy reuse passwordfile auxiliary format
'/u01/app/oracle/product/19.3.0/dbhome_1/dbs/orapwerpstd' ;}executing Memory ScriptStarting backup at 2019-10-28 09:36:23allocated channel: ORA_DISK_1channel ORA_DISK_1: SID=751 device type=DISKFinished backup at 2019-10-28 09:36:27contents of Memory Script:{ restore clone from service 'erppri' standby controlfile;}executing Memory Script
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
44.
45.
46.
47.
48.
49.
50.
51.
52.
53.
54.
55.
56.
57.
58.
59.
60.
61.
62.
63.
64.
65.
66.
67.
68.
69.
70.
71.
72.
73.
74.
75.
76.
77.
78.
Starting restore at 2019-10-28 09:36:27using channel ORA_AUX_DISK_1channel ORA_AUX_DISK_1: starting datafile backup set restorechannel ORA_AUX_DISK_1: using network backup set from service erpprichannel ORA_AUX_DISK_1: restoring control filechannel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:02output file name=/u01/app/oracle/oradata/ERPSTD/put file name=/u01/app/oracle/oradata/ERPSTD/ished restore at 2019-10-28 09:36:32contents of Memory Script:{ sql clone 'alter database mount standby database';}executing Memory Scriptsql statement: alter database mount standby databasecontents of Memory Script:{ set newname for tempfile 1 to
"/u01/app/oracle/oradata/ERPSTD/"; switch clone tempfile all; set newname for datafile 1 to
"/u01/app/oracle/oradata/ERPSTD/"; set newname for datafile 3 to
"/u01/app/oracle/oradata/ERPSTD/"; set newname for datafile 4 to
"/u01/app/oracle/oradata/ERPSTD/"; set newname for datafile 7 to
"/u01/app/oracle/oradata/ERPSTD/"; restore from nonsparse from service
'erppri' clone database ; sql 'alter system archive log current';}executing Memory Scriptexecuting command: SET NEWNAMErenamed tempfile 1 to /u01/app/oracle/oradata/ERPSTD/ in control
fileexecuting command: SET NEWNAME
79.
80.
81.
82.
83.
84.
85.
86.
87.
88.
89.
90.
91.
92.
93.
94.
95.
96.
97.
98.
99.
100.
101.
102.
103.
104.
105.
106.
107.
108.
109.
110.
111.
112.
113.
114.
115.
116.
117.
118.
119.
120.
executing command: SET NEWNAMEexecuting command: SET NEWNAMEexecuting command: SET NEWNAMEStarting restore at 2019-10-28 09:36:39using channel ORA_AUX_DISK_1channel ORA_AUX_DISK_1: starting datafile backup set restorechannel ORA_AUX_DISK_1: using network backup set from service erpprichannel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup setchannel ORA_AUX_DISK_1: restoring datafile 00001 to
/u01/app/oracle/oradata/ERPSTD/nnel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:55channel ORA_AUX_DISK_1: starting datafile backup set restorechannel ORA_AUX_DISK_1: using network backup set from service erpprichannel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup setchannel ORA_AUX_DISK_1: restoring datafile 00003 to
/u01/app/oracle/oradata/ERPSTD/nnel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:45channel ORA_AUX_DISK_1: starting datafile backup set restorechannel ORA_AUX_DISK_1: using network backup set from service erpprichannel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup setchannel ORA_AUX_DISK_1: restoring datafile 00004 to
/u01/app/oracle/oradata/ERPSTD/nnel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:26channel ORA_AUX_DISK_1: starting datafile backup set restorechannel ORA_AUX_DISK_1: using network backup set from service erpprichannel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup setchannel ORA_AUX_DISK_1: restoring datafile 00007 to
/u01/app/oracle/oradata/ERPSTD/nnel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01Finished restore at 2019-10-28 09:38:47sql statement: alter system archive log currentcontents of Memory Script:{ switch clone datafile all;}executing Memory Scriptdatafile 1 switched to datafile copyinput datafile copy RECID=1 STAMP=1022837931 file
name=/u01/app/oracle/oradata/ERPSTD/
121.
122.
123.
124.
125.
126.
127.
datafile 3 switched to datafile copyinput datafile copy RECID=2 STAMP=1022837931 file
name=/u01/app/oracle/oradata/ERPSTD/afile 4 switched to datafile copyinput datafile copy RECID=3 STAMP=1022837931 file
name=/u01/app/oracle/oradata/ERPSTD/afile 7 switched to datafile copyinput datafile copy RECID=4 STAMP=1022837931 file
name=/u01/app/oracle/oradata/ERPSTD/ished Duplicate Db at 2019-10-28 09:40:125 备库同步测试5.1 备库 MOUNT 状态开启日志同步备库创建好之后，默认状态为
MOUNT，开启同步后，仅同步日志，是无法提供业务数据查询的1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
# 主库状态查询SQL> select database_role,open_mode,protection_mode from v$database;DATABASE_ROLE OPEN_MODE PROTECTION_MODE---------------- -------------------- --------------------PRIMARY READ WRITE MAXIMUM PERFORMANCE# 备库状态查询SQL> select database_role,open_mode,protection_mode from v$database;DATABASE_ROLE OPEN_MODE PROTECTION_MODE---------------- -------------------- --------------------PHYSICAL STANDBY MOUNTED MAXIMUM PERFORMANCE# 备库开启同步SQL> alter database recover managed standby database disconnect from
session;Database altered.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
# 备库开启同步日志节选Background Managed Standby Recovery process started (erpstd)2019-10-28T21:06:02.090358+08:00 Started logmerger process2019-10-28T21:06:02.110828+08:00PR00 (PID:10612): Managed Standby Recovery starting Real Time Apply2019-10-28T21:06:02.321403+08:00Parallel Media Recovery started with 8 slaves2019-10-28T21:06:02.348412+08:00stopping change tracking2019-10-28T21:06:02.512702+08:00PR00 (PID:10612): Media Recovery Log /u02/archivelog/1_20_00 (PID:10612): Media Recovery Waiting for T-1.S-21 (in transit)2019-10-28T21:06:02.622399+08:00Recovery of Online Redo Log: Thread 1 Group 5 Seq 21 Reading mem 0 Mem# 0: /u01/app/oracle/oradata/ERPSTD/2019-10-28T21:06:03.063832+08:00Completed: alter database recover managed standby database disconnect fromsession主库查询当前最新归档1.
2.
3.
4.
5.
SQL> select unique thread#,max(sequence#) over(partition by thread#) last
from v$archived_log; THREAD# LAST---------- ---------- 1 20备库查询当前最新归档1.
2.
3.
4.
5.
SQL> select unique thread#,max(sequence#) over(partition by thread#) last
from v$archived_log; THREAD# LAST---------- ---------- 1 20主库切换日志并查询最新归档1.
2.
3.
SQL> alter system switch logfile;System altered.
4.
5.
6.
7.
8.
9.
SQL> select unique thread#,max(sequence#) over(partition by thread#) last
from v$archived_log; THREAD# LAST---------- ---------- 1 21备库再次查询最新归档1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
SQL> select unique thread#,max(sequence#) over(partition by thread#) last
from v$archived_log; THREAD# LAST---------- ---------- 1 21# 主库切换日志后，备库同步日志节选2019-10-28T21:11:25.568454+08:00 rfs (PID:10393): Selected LNO:4 for T-1.S-22 dbid 555079172 branch
19-10-28T21:11:25.636403+08:00PR00 (PID:10612): Media Recovery Waiting for T-1.S-22 (in transit)2019-10-28T21:11:25.637640+08:00Recovery of Online Redo Log: Thread 1 Group 4 Seq 22 Reading mem 0 Mem# 0: /u01/app/oracle/oradata/ERPSTD/2019-10-28T21:11:25.652380+08:00ARC3 (PID:10011): Archived Log entry 15 added for T-1.S-21 ID 0x2115db04 LAD:15.2 备库开启 ADGADG （Active Data Guard）作为独立产品功能包含在 EE 企业版本中，其它版本不支持。备库开启
ADG 功能后，可进行同步查询。备库取消同步1.
2.
3.
SQL> alter database recover managed standby database cancel;Database altered.
备库开启 ADG1.
2.
3.
4.
5.
6.
7.
# 打开备库为OPEN模式SQL> alter database open;# 备库开启同步SQL> alter database recover managed standby database disconnect from
session;同步查询测试1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
# 主库创建测试表并插入数据SQL> create table askscuti(id number);SQL> insert into askscuti values(1);SQL> commit;SQL> select * from askscuti; ID---------- 1# 备库查询SQL> select * from askscuti; ID---------- 1说明2.5.6.5 Real-Time Apply is Default Setting for Data Guard
In previous releases, when creating a Data Guard configuration using the SQLcommand line, the default configuration was to apply redo from archived log files onthe standby database. In Oracle Database 12c Release 1 (12.1), the defaultconfiguration is to use real-time apply so that redo is applied directly from thestandby redo log file.在12c之前版本中，MRP的默认配置是对备用数据库上的归档日志文件进行应用重做。从
Oracle数据库12c Release 1（12.1）开始，默认配置是使用实时应用（real-time apply），直接从备用重做日志文件应用重做。当前版本为19c，备库开启同步后，默认为实时应用。1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
# 备库开启应用语句SQL> alter database recover managed standby database disconnect;(应用在线日志实时同步)或者SQL> alter database recover managed standby database using archived
logfile disconnect;（应用归档日志非实时同步）# 查询是否为实时应用SQL> select dest_name,status,recovery_mode from v$archive_dest_status;DEST_NAME STATUS RECOVERY_MODE-------------------- --------- ----------------------------------LOG_ARCHIVE_DEST_1 VALID MANAGED REAL TIME APPLY# MANAGED REAL TIME APPLY 代表实时应用在12c之前版本中，例如11g，要开启实时应用，需执行以下命令1. SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE
DISCONNECT FROM SESSION;
版权声明：本文标题：Oracle 19c Non-CDB Data Guard 搭建(1主1备) 内容由热心网友自发贡献，该文观点仅代表作者本人， 转载请联系作者并注明出处：https://m.elefans.com/dongtai/1703792709a72111.html， 本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌抄袭侵权/违法违规的内容，一经查实，本站将立刻删除。
发表评论