一,DG搭建实例--主库配置
one : 归档配置
01,查看归档
1 select log_mode,force_logging from v$database;
02,开启归档
关闭数据库重新启动到mount阶段
shutdown immediate
startup mount
03,改成归档模式和强制 logging
SQL> alter database archivelog;
Database altered.
SQL> alter database force logging;
Database altered.
再次查看:
04,改变归档目录 ![](https://i-blog.csdnimg.cn/blog_migrate/0a42f22d87ac8e7c9fa1d10a5bc9bb8d.png)
!mkdir /u01/app/oracle/oradata/oracle01/archivelog
!ls /u01/app/oracle/oradata/oracle01/
alter system set log_archive_dest_1='location=/u01/app/oracle/oradata/oracle01/archivelog';
alter database open;
05,创建pfile文件
1 create pfile from spfile;
2
3 alter database create standby controlfile as '/u01/oracle/standby.ctl';
two.设置闪回-flashback
01,查看是否开启:
SQL> select flashback_on from v$database;
FLASHBACK_ON
------------------
NO -----如果为YES可以忽略这一步
02, 设置闪回路径
SQL> altersystem set db_recovery_file_dest='/u01/app/oracle';
03,设置闪回大小
SQL> altersystem set db_recovery_file_dest_size='5G';
04,关闭数据库重新启动
SQL> shutdown immediate; ----关闭数据库
SQL> alter database flashback on; ----开启闪回
SQL> alter database open; ------open数据库
05,验证数据
three.添加standby日志
01,查看主库在线日志大小和主数
SQL> select group#,bytes/1024/1024 from v$log;
GROUP# BYTES/1024/1024
---------- ---------------
1 50
2 50
3 50
02,查看备库在线日志大小和主数
SQL> select group#,bytes/1024/1024 from v$standby_log;
默认显示
no rows selected
03,创建
SQL> alter database add standby logfile group 11 '/data/CEBPM/onlinelog/redo11_stb01.log' size 50M;
SQL> alter database add standby logfile group 12 '/data/CEBPM/onlinelog/redo12_stb01.log' size 50M;
SQL> alter database add standby logfile group 13 '/data/CEBPM/onlinelog/redo13_stb01.log' size 50M;
SQL> alter database add standby logfile group 14 '/data/CEBPM/onlinelog/redo14_stb01.log' size 50M;
04,再次查看备库的时候发现:
four.主库修改参数文件
SQL> select group#,bytes/1024/1024 from v$standby_log;
SQL> alter database add standby logfile group 14 '/u01/oracle/redo14_stb01.log' size 50M;
SQL> alter system set log_archive_config='DG_CONFIG=(oracle01,standby)';
SQL> alter system set log_archive_dest_2='SERVICE=standby VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=standby' scope=spfile;
SQL> alter system set log_archive_dest_state_1='enable';
SQL> alter system set log_archive_dest_state_2='enable'
SQL> alter system set db_file_name_convert='/u01/oracle/datafile','/u01/oracle/datafile'scope=spfile;
SQL> alter system set log_file_name_convert='/u01/oracle/onlinelog','/u01/oracle/onlinelog'scope=spfile;
SQL> alter system set fal_server='standby';
SQL> alter system set fal_client='oracle01';
SQL> alter system set standby_file_management='AUTO';
全部执行输出显示为:
System altered.
修改完成后重启数据库
SQL> shutdown immediate
SQL> startup
five.修改配置文件
01, listener.ora
[root@oracle01 ~]# cat /u01/app/oracle/product/11.2.0/db_1/network/admin/listener.ora
# listener.ora Network Configuration File: /u01/app/oracle/product/11.2.0/db_1/network/admin/listener.ora
# Generated by Oracle configuration tools.
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = oracle01)
(SID_NAME = oracle01)
)
)
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.10)(PORT = 1521))
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
)
)
ADR_BASE_LISTENER = /u01/app/oracle
02, tnsnames.ora
[root@oracle01 ~]# cat /u01/app/oracle/product/11.2.0/db_1/network/admin/tnsnames.ora
# tnsnames.ora Network Configuration File: /u01/app/oracle/product/11.2.0/db_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.
ORACLE01 =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.10)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = oracle01)
)
)
STANDBY =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.16)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = standby)
)
)
03,重启监听
[oracle@oracle01 ~]#lsnrctl stop
[oracle@oracle01 ~]#lsnrctl start
six.文件传输
SQL> create pfile='/tmp/initoracle01.ora' fromspfile;
生成参数文件
拷贝参数文件与密码文件
scp orapworacle01 192.168.1.143:/u01/app/oracle/product/11.2.0/db_1/dbs/
scp /tmp/initoracle01.ora 192.168.1.143:/u01/app/oracle/product/11.2.0/db_1/dbs/
二,DG搭建实例--从库配置
one.配置文件修改
01,配置文件重命名
重命名文件
[root@standby dbs]# mv initoracle01.ora initstandby.ora
[root@standby dbs]# mv orapworacle01 orapwstandby
02,改配置文件
![](https://i-blog.csdnimg.cn/blog_migrate/8f900a89c6347c561fdf2122f13be562.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/961ddebeb323a10fe0623af514929fc1.gif)
1 [root@standby dbs]# pwd 2 /u01/app/oracle/product/11.2.0/db_1/dbs 3 [root@standby dbs]# cat initstandby.ora 4 *.audit_file_dest='/u01/app/oracle/admin/standby/adump' 5 *.audit_trail='db' 6 *.compatible='11.2.0.4.0' 7 *.control_files='/u01/oracle/standby.ctl' 8 *.db_block_size=8192 9 *.db_domain='localdomain' 10 *.db_file_name_convert='/u01/oracle/datafile','/u01/oracle/datafile' 11 *.db_name='oracle01' 12 *.db_recovery_file_dest='/u01/oracle' 13 *.db_recovery_file_dest_size=5368709120 14 *.db_unique_name='standby' 15 *.diagnostic_dest='/u01/app/oracle' 16 *.dispatchers='(PROTOCOL=TCP) (SERVICE=oracle01XDB)' 17 *.fal_server='oracle01' 18 *.fal_client='standby' 19 *.log_archive_config='DG_CONFIG=(oracle01,standby)' 20 *.log_archive_dest_1='location=/u01/oracle/archivelog' 21 *.log_archive_dest_2='SERVICE=oracle01 VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=oracle01' 22 *.log_archive_dest_state_1='enable' 23 *.log_archive_dest_state_2='enable' 24 *.log_file_name_convert='/u01/oracle/onlinelog','/u01/oracle/onlinelog' 25 *.open_cursors=300 26 *.pga_aggregate_target=836763648 27 *.processes=150 28 *.remote_login_passwordfile='EXCLUSIVE' 29 *.sga_target=2510290944 30 *.standby_file_management='AUTO' 31 *.undo_tablespace='UNDOTBS1'
03,创建必要的目录
mkdir -p /u01/app/oracle/admin/standby/adump
mkdir -p /u01/oracle/onlinelog/
mkdir -p /u01/oracle/datafile
mkdir -p /u01/oracle/archivelog
mkdir -p /u01/oracle/controlfile
two.从库配置文件更新
01, listener.ora
![](https://i-blog.csdnimg.cn/blog_migrate/8f900a89c6347c561fdf2122f13be562.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/961ddebeb323a10fe0623af514929fc1.gif)
1 [root@standby ~]# cat /u01/app/oracle/product/11.2.0/db_1/network/admin/listener.ora 2 SID_LIST_LISTENER = 3 4 (SID_LIST = 5 6 (SID_DESC = 7 8 (GLOBAL_DBNAME = standby) 9 10 (ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_1) 11 12 (SID_NAME = standby) 13 14 ) 15 16 ) 17 STENER = 18 19 (DESCRIPTION_LIST = 20 21 (DESCRIPTION = 22 23 (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.16)(PORT = 1521)) 24 25 ) 26 27 ) 28 ADR_BASE_LISTENER = /u01/app/oracle
02, tnsnames.ora
![](https://i-blog.csdnimg.cn/blog_migrate/8f900a89c6347c561fdf2122f13be562.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/961ddebeb323a10fe0623af514929fc1.gif)
1 [root@standby ~]# cat /u01/app/oracle/product/11.2.0/db_1/network/admin/ 2 listener.ora samples/ shrept.lst tnsnames.ora tnsnames.ora.bak 3 [root@standby ~]# cat /u01/app/oracle/product/11.2.0/db_1/network/admin/tnsnames.ora 4 # tnsnames.ora Network Configuration File: /u01/app/oracle/product/11.2.0/db_1/network/admin/tnsnames.ora 5 # Generated by Oracle configuration tools. 6 7 ORACLE01 = 8 (DESCRIPTION = 9 (ADDRESS_LIST = 10 (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.10)(PORT = 1521)) 11 ) 12 (CONNECT_DATA = 13 (SERVICE_NAME = oracle01) 14 ) 15 ) 16 17 STANDBY = 18 (DESCRIPTION = 19 (ADDRESS_LIST = 20 (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.16)(PORT = 1521)) 21 ) 22 (CONNECT_DATA = 23 (SERVICE_NAME = standby) 24 ) 25 )
03, 重启监听服务器
[oracle@standby ~]$lsnrctl stop
[oracle@standby ~]$lsnrctl start
04,启动从库为nomount
SQL> shutdown immediate;
SQL> startup nomount pfile='/u01/app/oracle/product/11.2.0/db_1/dbs/initstandby.ora';
SQL> create spfile from pfile;
three.验证监听
01.两端的数据库都需要执行
sqlplus sys/123456@oracle01 as sysdba sqlplus sys/123456@standby as sysdba
主库执行结果
![](https://i-blog.csdnimg.cn/blog_migrate/8f900a89c6347c561fdf2122f13be562.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/961ddebeb323a10fe0623af514929fc1.gif)
1 [oracle@oracle01 ~]$ sqlplus sys/123456@standby as sysdba 2 3 SQL*Plus: Release 11.2.0.4.0 Production on Thu Feb 21 11:43:14 2019 4 5 Copyright (c) 1982, 2013, Oracle. All rights reserved. 6 7 8 Connected to: 9 Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production 10 With the Partitioning, OLAP, Data Mining and Real Application Testing options 11 12 SQL> exit 13 Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production 14 With the Partitioning, OLAP, Data Mining and Real Application Testing options 15 16 17 18 [oracle@oracle01 ~]$ sqlplus sys/123456@oracle01 as sysdba 19 20 SQL*Plus: Release 11.2.0.4.0 Production on Thu Feb 21 13:04:26 2019 21 22 Copyright (c) 1982, 2013, Oracle. All rights reserved. 23 24 25 Connected to: 26 Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production 27 With the Partitioning, OLAP, Data Mining and Real Application Testing options 28 29 SQL> exit 30 Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production 31 With the Partitioning, OLAP, Data Mining and Real Application Testing options
从库执行结果
![](https://i-blog.csdnimg.cn/blog_migrate/8f900a89c6347c561fdf2122f13be562.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/961ddebeb323a10fe0623af514929fc1.gif)
1 [oracle@standby ~]$ sqlplus sys/123456@oracle01 as sysdba 2 3 SQL*Plus: Release 11.2.0.4.0 Production on Thu Feb 21 13:06:18 2019 4 5 Copyright (c) 1982, 2013, Oracle. All rights reserved. 6 7 8 Connected to: 9 Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production 10 With the Partitioning, OLAP, Data Mining and Real Application Testing options 11 12 SQL> exit 13 Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production 14 With the Partitioning, OLAP, Data Mining and Real Application Testing options 15 [oracle@standby ~]$ 16 [oracle@standby ~]$ sqlplus sys/123456@standby as sysdba 17 18 SQL*Plus: Release 11.2.0.4.0 Production on Thu Feb 21 13:06:24 2019 19 20 Copyright (c) 1982, 2013, Oracle. All rights reserved. 21 22 23 Connected to: 24 Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production 25 With the Partitioning, OLAP, Data Mining and Real Application Testing options 26 27 SQL> exit 28 Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production 29 With the Partitioning, OLAP, Data Mining and Real Application Testing options 30 [oracle@standby ~]$
三,rman数据备份与恢复
one.主库备份数据
rman target /
RMAN> configure channel device type disk format '/oradata/backup/%U_%d';
RMAN> backup as compressed backupset database;
RMAN> backup current controlfile for standby format '/oradata/backup/standby.ctl';
##RMAN> backup full format='/u01/app/oracle/ORACLE01/backupset/db_%U' database include current controlfile for standby;
等生成后最后的输出上会显示目录
把文件全部发送到从库
scp -r /u01/app/oracle/ORACLE01/backupset/* 192.168.0.16:/u01/backupset/
two.从库数据恢复
01,注册恢复地址
RAMN> restore standby contolfile from '/u01/app/oracle/ORACLE01/backupset/db_%U' (根据各自名字确定)
RMAN> catalog start with '/u01/backupset/2019_02_21';
![](https://i-blog.csdnimg.cn/blog_migrate/8f900a89c6347c561fdf2122f13be562.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/961ddebeb323a10fe0623af514929fc1.gif)
1 RMAN> catalog start with '/u01/backupset/2019_02_21'; 2 3 searching for all files that match the pattern /u01/backupset/2019_02_21 4 5 List of Files Unknown to the Database 6 ===================================== 7 File Name: /u01/backupset/2019_02_21/o1_mf_annnn_TAG20190221T112927_g6w6rq89_.bkp 8 File Name: /u01/backupset/2019_02_21/o1_mf_nnndf_TAG20190221T112928_g6w6rrhr_.bkp 9 File Name: /u01/backupset/2019_02_21/o1_mf_ncsnf_TAG20190221T112928_g6w6s8r5_.bkp 10 File Name: /u01/backupset/2019_02_21/o1_mf_annnn_TAG20190221T112945_g6w6s9tt_.bkp 11 12 Do you really want to catalog the above files (enter YES or NO)? yes 13 cataloging files... 14 cataloging done 15 16 List of Cataloged Files 17 ======================= 18 File Name: /u01/backupset/2019_02_21/o1_mf_annnn_TAG20190221T112927_g6w6rq89_.bkp 19 File Name: /u01/backupset/2019_02_21/o1_mf_nnndf_TAG20190221T112928_g6w6rrhr_.bkp 20 File Name: /u01/backupset/2019_02_21/o1_mf_ncsnf_TAG20190221T112928_g6w6s8r5_.bkp 21 File Name: /u01/backupset/2019_02_21/o1_mf_annnn_TAG20190221T112945_g6w6s9tt_.bkp
02,数据恢复
RMAN> restore database;
![](https://i-blog.csdnimg.cn/blog_migrate/8f900a89c6347c561fdf2122f13be562.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/961ddebeb323a10fe0623af514929fc1.gif)
1 RMAN> restore database; 2 3 Starting restore at 21-FEB-19 4 using channel ORA_DISK_1 5 6 channel ORA_DISK_1: starting datafile backup set restore 7 channel ORA_DISK_1: specifying datafile(s) to restore from backup set 8 channel ORA_DISK_1: restoring datafile 00001 to /u01/app/oracle/oradata/oracle01/system01.dbf 9 channel ORA_DISK_1: restoring datafile 00002 to /u01/app/oracle/oradata/oracle01/sysaux01.dbf 10 channel ORA_DISK_1: restoring datafile 00003 to /u01/app/oracle/oradata/oracle01/undotbs01.dbf 11 channel ORA_DISK_1: restoring datafile 00004 to /u01/app/oracle/oradata/oracle01/users01.dbf 12 channel ORA_DISK_1: restoring datafile 00005 to /u01/app/oracle/oradata/oracle01/example01.dbf 13 channel ORA_DISK_1: reading from backup piece /u01/backupset/2019_02_21/o1_mf_nnndf_TAG20190221T112928_g6w6rrhr_.bkp 14 channel ORA_DISK_1: ORA-19870: error while restoring backup piece /u01/backupset/2019_02_21/o1_mf_nnndf_TAG20190221T112928_g6w6rrhr_.bkp 15 ORA-19504: failed to create file "/u01/app/oracle/oradata/oracle01/system01.dbf" 16 ORA-27040: file create error, unable to create file 17 Linux-x86_64 Error: 2: No such file or directory 18 Additional information: 1 19 20 failover to previous backup 21 22 RMAN-00571: =========================================================== 23 RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS =============== 24 RMAN-00571: =========================================================== 25 RMAN-03002: failure of restore command at 02/21/2019 11:52:24 26 RMAN-06026: some targets not found - aborting restore 27 RMAN-06023: no backup or copy of datafile 5 found to restore 28 RMAN-06023: no backup or copy of datafile 4 found to restore 29 RMAN-06023: no backup or copy of datafile 3 found to restore 30 RMAN-06023: no backup or copy of datafile 2 found to restore 31 RMAN-06023: no backup or copy of datafile 1 found to restore 32 33 RMAN> restore database ; 34 35 Starting restore at 21-FEB-19 36 using channel ORA_DISK_1 37 38 channel ORA_DISK_1: starting datafile backup set restore 39 channel ORA_DISK_1: specifying datafile(s) to restore from backup set 40 channel ORA_DISK_1: restoring datafile 00001 to /u01/app/oracle/oradata/oracle01/system01.dbf 41 channel ORA_DISK_1: restoring datafile 00002 to /u01/app/oracle/oradata/oracle01/sysaux01.dbf 42 channel ORA_DISK_1: restoring datafile 00003 to /u01/app/oracle/oradata/oracle01/undotbs01.dbf 43 channel ORA_DISK_1: restoring datafile 00004 to /u01/app/oracle/oradata/oracle01/users01.dbf 44 channel ORA_DISK_1: restoring datafile 00005 to /u01/app/oracle/oradata/oracle01/example01.dbf 45 channel ORA_DISK_1: reading from backup piece /u01/backupset/2019_02_21/o1_mf_nnndf_TAG20190221T112928_g6w6rrhr_.bkp 46 channel ORA_DISK_1: piece handle=/u01/backupset/2019_02_21/o1_mf_nnndf_TAG20190221T112928_g6w6rrhr_.bkp tag=TAG20190221T112928 47 channel ORA_DISK_1: restored backup piece 1 48 channel ORA_DISK_1: restore complete, elapsed time: 00:00:07 49 Finished restore at 21-FEB-19
03,数据库状态
四,DG 测试
01,测试一
1 SQL> col dest_name format a30 2 SQL> col error format a20 3 SQL> select dest_name,error from v$archive_dest; 4 5 DEST_NAME ERROR 6 ------------------------------ -------------------- 7 LOG_ARCHIVE_DEST_1 8 LOG_ARCHIVE_DEST_2 9 LOG_ARCHIVE_DEST_3 10 LOG_ARCHIVE_DEST_4 11 LOG_ARCHIVE_DEST_5 12 LOG_ARCHIVE_DEST_6 13 LOG_ARCHIVE_DEST_7 14 LOG_ARCHIVE_DEST_8 15 LOG_ARCHIVE_DEST_9 16 LOG_ARCHIVE_DEST_10 17 LOG_ARCHIVE_DEST_11 18 19 DEST_NAME ERROR 20 ------------------------------ -------------------- 21 LOG_ARCHIVE_DEST_12 22 LOG_ARCHIVE_DEST_13 23 LOG_ARCHIVE_DEST_14 24 LOG_ARCHIVE_DEST_15 25 LOG_ARCHIVE_DEST_16 26 LOG_ARCHIVE_DEST_17 27 LOG_ARCHIVE_DEST_18 28 LOG_ARCHIVE_DEST_19 29 LOG_ARCHIVE_DEST_20 30 LOG_ARCHIVE_DEST_21 31 LOG_ARCHIVE_DEST_22 32 33 DEST_NAME ERROR 34 ------------------------------ -------------------- 35 LOG_ARCHIVE_DEST_23 36 LOG_ARCHIVE_DEST_24 37 LOG_ARCHIVE_DEST_25 38 LOG_ARCHIVE_DEST_26 39 LOG_ARCHIVE_DEST_27 40 LOG_ARCHIVE_DEST_28 41 LOG_ARCHIVE_DEST_29 42 LOG_ARCHIVE_DEST_30 43 LOG_ARCHIVE_DEST_31 44 STANDBY_ARCHIVE_DEST 45 46 32 rows selected.
测试,主库查看最大归档序号
主SQL> select max(sequence#) from v$archived_log;
MAX(SEQUENCE#)
--------------
13
从SQL> select max(sequence#) from v$archived_log;
MAX(SEQUENCE#)
--------------
13
主库日志切换:
1 alter system archive log current;
再次查看主从库一样则成功
two.测试2
主库操作
[oracle@oracle01 ~]$ sqlplus / as sysdba
1 SQL> create table dg(id number); 2 3 Table created. 4 5 SQL> insert into dg values(1); 6 7 1 row created. 8 9 SQL> commit; 10 11 Commit complete. 12 13 SQL> select * from dg; 14 15 ID 16 ---------- 17 1
从库操作
[oracle@standby ~]$ sqlplus / as sysdba SQL> select * from dg; ID ---------- 1
测试成功