Oracle 12C R2 on Linux 7.X Data Guard 搭建文档

时间:2021-10-10 21:41:52

 

1.查看主机和数据库信息
 
[oracle@oracle1 ~]$ sqlplus / as sysdba
 
SQL*Plus: Release 12.2.0.1.0 Production on Tue May 29 01:19:35 2018
 
Copyright (c) 1982, 2016, Oracle. All rights reserved.
 
 
Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
 
SQL> set linesize 300
set pagesize 999 
select * from v$version; 
 
BANNER CON_ID
-------------------------------------------------------------------------------- ----------
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production 0
PL/SQL Release 12.2.0.1.0 - Production 0
CORE 12.2.0.1.0 Production 0
TNS for Linux: Version 12.2.0.1.0 - Production 0
NLSRTL Version 12.2.0.1.0 - Production 0
 
 
SQL> set linesize 140
set pagesize 999
col name for a25
col value for a60
select name,value from v$parameter where name like '%dump%';SQL> SQL> SQL> SQL> 
 
NAME VALUE
------------------------- ------------------------------------------------------------
shadow_core_dump partial
background_core_dump partial
background_dump_dest /data/app/oracle/product/12.2.0.1/db_1/rdbms/log
user_dump_dest /data/app/oracle/product/12.2.0.1/db_1/rdbms/log
core_dump_dest /data/app/oracle/diag/rdbms/orcl/orcl/cdump
max_dump_file_size unlimited
 
6 rows selected.
 
SQL> col value for a60
select name,value from v$parameter where name like '%pfile%';
 
NAME VALUE
------------------------- ------------------------------------------------------------
spfile /data/app/oracle/product/12.2.0.1/db_1/dbs/spfileorcl.ora
 
SQL> col name for a50
select name from v$controlfile;
 
NAME
--------------------------------------------------
/data/oradata/orcl/control01.ctl
/data/oradata/orcl/control02.ctl
 
SQL> col member for a50
select member from v$logfile;
 
MEMBER
--------------------------------------------------
/data/oradata/orcl/redo03.log
/data/oradata/orcl/redo02.log
/data/oradata/orcl/redo01.log
 
 
SQL> select name from v$datafile;    
 
NAME
--------------------------------------------------
/data/oradata/orcl/system01.dbf
/data/oradata/orcl/slucex_01.dbf
/data/oradata/orcl/sysaux01.dbf
/data/oradata/orcl/undotbs01.dbf
/data/oradata/orcl/qa_01.dbf
/data/oradata/orcl/users01.dbf
/data/oradata/orcl/ucex_service_01.dbf
/data/oradata/orcl/activity_01.dbf
 
8 rows selected.
 
 
SQL> select name from v$tempfile;
 
NAME
--------------------------------------------------
/data/oradata/orcl/temp01.dbf
 
SQL> col PARAMETER for a30
col VALUE FOR A30
select * from nls_database_parameters where parameter='NLS_CHARACTERSET';
 
PARAMETER VALUE
------------------------------ ------------------------------
NLS_CHARACTERSET AL32UTF8
 
$ echo $ORACLE_BASE
/data/app/oracle
 
 
$ echo $ORACLE_HOME
/data/app/oracle/product/12.2.0.1/db_1
 
2.准备工作
实施前需要准备事情:灾备端安装好数据库软件,为避免不必要的麻烦,软件目录和数据文件目录结构与主库的一致。如果两端的数据文件结构不一致,则需要重启数据库。
修改主备库/etc/hosts文件。
 
 
3.确定主库处于归档模式
 
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination +DATA
Oldest online log sequence 2314
Next log sequence to archive 2315
Current log sequence 2315
如果处于非归档模式需要修改为归档模式。
 
 
4.修改主库为强日志模式
SQL> select force_logging from v$database;
 
FOR
---
NO
 
经检查不是强日志模式,先修改为强日志模式,命令如下:
SQL> alter database force logging;
 
 
5.备库创建相同的目录结构
步骤省略,参考前面输出内容创建
 
 
6.主库修改参数
SQL>
alter system set standby_file_management=auto scope=both sid='*';
alter system set fal_server=orcl scope=both sid='*';
alter system set log_archive_config='dg_config=(orcl,standby)' scope=both sid='*';
alter system set log_archive_dest_1='location=/data/arch valid_for=(all_logfiles,all_roles) db_unique_name=orcl' sid='*';
 
 
 
 
7.配置tnsnames.ora
orcl =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.190.60)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orcl)
    )
  )
 
 
STANDBY =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.190.61)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = standby)
    )
  )
 
 
 
8.传送参数文件和密码文件至备库
 
 
9.主库使用RMAN备份
RMAN> run{
allocate channel ch1 type disk;
allocate channel ch2 type disk;
backup database format '/data/backup/dbf_%d_%T_%s_%p';
sql 'alter system archive log current';
backup archivelog all format '/data/backup/arc_%d_%T_%s_%p' delete input;
backup current controlfile for standby format '/data/backup/control01.ctl';
}
 
传送备份文件至备库。
 
 
10.启动备库至nomount状态
SQL> startup nomount;
修改DB_UNIQUE_NAME
SQL> ALTER SYSTEM SET DB_UNIQUE_NAME=standby scope=spfile;
注意:主备库两边的db_name必须一致,db_unique_name必须不一致。
 
备库修改参数
alter system set db_file_name_convert='/data/oradata/orcl','/data/oradata/orcl' scope=spfile ;
alter system set log_file_name_convert='/data/oradata/orcl','/data/oradata/orcl' scope=spfile ;
 
重新启动,使参数生效:
SQL> shutdown immediate;
SQL> startup nomount;
 
 
11.恢复控制文件
RMAN> restore standby controlfile from '/data/backup/control01.ctl';
 
 
12.启动备库至mount状态
RMAN> alter database mount;
 
 
13.恢复备库
RMAN> restore database;
RMAN> recover database;
 
 
14.添加standby logfiles(在主库和备库都执行)
检查当前环境logfile
SQL> set lines 200 pages 300
col member for a60
select a.thread#,a.group#,b.member,b.type,a.bytes/1024/1024 MB from v$log a,v$logfile b where a.group#=b.group#
union all
select a.thread#,a.group#,b.member,b.type,a.bytes/1024/1024 MB from v$standby_log a,v$logfile b where a.group#=b.group#;
 
   THREAD# GROUP# MEMBER TYPE MB
---------- ---------- ------------------------------------------------------------ ------- ----------
  1 1 /data/oradata/orcl/redo01.log ONLINE 200
  1 3 /data/oradata/orcl/redo03.log ONLINE 200
  1 2 /data/oradata/orcl/redo02.log ONLINE 200
 
 
SQL> alter database add standby logfile
     group 14 '/data/oradata/orcl/redo14.log' size 200M,
     group 15 '/data/oradata/orcl/redo15.log' size 200M,
     group 16 '/data/oradata/orcl/redo16.log' size 200M,
     group 17 '/data/oradata/orcl/redo17.log' size 200M;
 
 
15.在主库恢复打开 
SQL> alter system set log_archive_dest_2='service=standby lgwr async COMPRESSION=ENABLE valid_for=(online_logfiles,primary_role) db_unique_name=standby' scope=both;
注意:检查 log_archive_dest_state_1 和 log_archive_dest_state_2 参数是否设置为enable ,如果没有设置为enable ,切换的时候可能会出问题.
 
 
16.在备库打开
备库修改参数
local_listener 参数修改为备库的host
 
alter system set log_archive_dest_1='location=/data/arch valid_for=(all_logfiles,all_roles) db_unique_name=standby' scope=both; 
alter system set local_listener='(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.190.61)(PORT=1521))' scope=both;
 
SQL> alter database open read only;
SQL> recover managed standby database using current logfile disconnect;
 
17.检查是否搭建成功
在主库上切归档
SQL> alter system archive log current;
SQL> alter system archive log current;
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /data/arch
Oldest online log sequence 142
Next log sequence to archive 143
Current log sequence 144
 
 
备库查询能应用 
$ orac -dgarc
 
Session altered.
 
SEQUENCE# FIRST_TIME NEXT_TIME APPLIED ARCHIVED
--------- -------------------- -------------------- ---------- --------
      135 2018-07-18 00:00:09 2018-07-18 03:40:02 YES YES
      136 2018-07-18 03:40:02 2018-07-18 03:40:03 YES YES
      137 2018-07-18 03:40:03 2018-07-18 10:12:50 YES YES
      138 2018-07-18 10:12:50 2018-07-18 14:31:00 YES YES
      139 2018-07-18 14:31:00 2018-07-18 16:00:59 YES YES
      140 2018-07-18 16:00:59 2018-07-18 16:02:42 YES YES
      141 2018-07-18 16:02:42 2018-07-18 16:03:27 YES YES
      142 2018-07-18 16:03:27 2018-07-18 16:03:31 YES YES
      143 2018-07-18 16:03:31 2018-07-18 16:08:01 YES YES
      144 2018-07-18 16:08:01 2018-07-18 16:10:03 IN-MEMORY YES  
   
   
   
   
检查备库alert日志,没有报错,说明就搭建成功了
 
$ orac -log db 20
2018-07-18T16:03:31.294903+08:00
Recovery of Online Redo Log: Thread 1 Group 15 Seq 143 Reading mem 0
  Mem# 0: /data/oradata/orcl/redo15.log
2018-07-18T16:08:01.891964+08:00
RFS[1]: Selected log 14 for T-1.S-144 dbid 1507958524 branch 980177790
2018-07-18T16:08:01.892027+08:00
Archived Log entry 49 added for T-1.S-143 ID 0x59e1d0fc LAD:1
2018-07-18T16:08:01.974669+08:00
Media Recovery Waiting for thread 1 sequence 144 (in transit)
2018-07-18T16:08:01.974916+08:00
Recovery of Online Redo Log: Thread 1 Group 14 Seq 144 Reading mem 0
  Mem# 0: /data/oradata/orcl/redo14.log
2018-07-18T16:10:04.097893+08:00
RFS[1]: Selected log 15 for T-1.S-145 dbid 1507958524 branch 980177790
2018-07-18T16:10:04.097907+08:00
Archived Log entry 50 added for T-1.S-144 ID 0x59e1d0fc LAD:1
2018-07-18T16:10:04.180686+08:00
Media Recovery Waiting for thread 1 sequence 145 (in transit)
2018-07-18T16:10:04.180958+08:00
Recovery of Online Redo Log: Thread 1 Group 15 Seq 145 Reading mem 0
  Mem# 0: /data/oradata/orcl/redo15.log