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