一,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,改变归档目录 
!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,改配置文件
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'
View Code
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
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
View Code
02, tnsnames.ora
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 )
View Code
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
主库执行结果
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
View Code
从库执行结果
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 ~]$
View Code
三,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';
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
View Code
02,数据恢复
RMAN> restore database;
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
View Code
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
测试成功
