非CDB数据库搭建DG

主库搭建

1、主库启动 force logging

检查主机是否启动 force logging

select force_logging from v$database;

启动 force logging

alter database force logging;

2、启用归档模式

查看归档模式

archive log list

这里显示为未归档模式
关闭数据库，启动到mount，在mount下启动到归档模式

shutdown immediate

startup mount

alter database archivelog;

切换数据库至open状态，并查看归档模式

alter database open;
archive log list

3、在主库添加standby redo logfile

查看主库的 redo 相关信息

需要添加 4(3+1) 个standby logfile

alter database add standby logfile '/u01/app/oracle/oradata/orcl/redo04.log' size 200M;
alter database add standby logfile '/u01/app/oracle/oradata/orcl/redo05.log' size 200M;
alter database add standby logfile '/u01/app/oracle/oradata/orcl/redo06.log' size 200M;
alter database add standby logfile '/u01/app/oracle/oradata/orcl/redo07.log' size 200M;

4、配置监听

配置 listener.ora 文件
vim listener.ora – 加入下面的内容，注意ORACLE_HOME的配置

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = standby)
      (ORACLE_HOME = /u01/app/oracle/12.2.0.1/db_1)
      (GLOBAL_DBNAME=standby)
    )
  )

配置 tnsnames.ora
vim tnsnames.ora – 加入如下内容，注意 HOST 参数

standby =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = shx)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = standby)
    )
  )

5、在主库创建 pfile 文件并修改 pfile 内容

将数据库关闭
shutdown immediate
创建 pfile 文件
create pfile from spfile
修改 pfile 内容，在 pfile 添加如下内容(已经存在的参数无需添加)：

db_name='orcl'
db_unique_name='orcl'
log_archive_config='dg_config=(orcl,standby)'
log_archive_dest_1='location=/u01/archivelog valid_for=(online_logfiles,primary_role) db_unique_name=orcl'
log_archive_dest_2='service=standby valid_for=(online_logfiles,primary_role) db_unique_name=standby'
log_archive_dest_3='location=/u01/archivelog1 valid_for=(standby_logfiles,standby_role) db_unique_name=orcl'
log_archive_dest_state_1=enable
log_archive_dest_state_2=enable
log_archive_dest_state_3=enable
standby_file_management='auto'
fal_server='standby'
fal_client='orcl'
DB_FILE_NAME_CONVERT='/u01/app/oracle/oradata/standby/','/u01/app/oracle/oradata/orcl/'
LOG_FILE_NAME_CONVERT='/u01/app/oracle/oradata/standby/','/u01/app/oracle/oradata/orcl/'
REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE

创建归档目录

mkdir -p /u01/archivelog
mkdir -p /u01/archivelog1

用新参数重启数据库

create spfile from pfile;

startup

备库搭建

1、创建备库的实例

配置备库的参数文件
[oracle@shx dbs]$ cp initorcl.ora initstandby.ora
vim initstandby.ora – 将文件中所有的 orcl 字符串替换为 standby
修改如下内容，再重新生成 spfile

db_name='orcl'
db_unique_name='standby'
log_archive_config='dg_config=(orcl,standby)'
log_archive_dest_1='location=/u01/archivelog2 valid_for=(standby_logfiles,standby_role) db_unique_name=standby'
log_archive_dest_2='service=orcl valid_for=(online_logfiles,primary_role) db_unique_name=orcl'
log_archive_dest_3='location=/u01/archivelog3 valid_for=(online_logfiles,primary_role) db_unique_name=standby'
log_archive_dest_state_1=enable
log_archive_dest_state_2=enable
log_archive_dest_state_3=enable
fal_server='orcl'
fal_client='standby'
DB_FILE_NAME_CONVERT='/u01/app/oracle/oradata/orcl/','/u01/app/oracle/oradata/standby/'
LOG_FILE_NAME_CONVERT='/u01/app/oracle/oradata/orcl/','/u01/app/oracle/oradata/standby/'

创建文件目录

mkdir -p /u01/archivelog2
mkdir -p /u01/archivelog3
mkdir -p /u01/app/oracle/admin/standby/adump

设置 ORACLE_SID
export ORACLE_SID=standby
利用 pfile 生成 spfile
create spfile from pfile;

2、将备库启动到 nomount

startup nomount

3、为备库生成口令文件

[oracle@shx ~]$ cd $ORACLE_HOME/dbs
[oracle@shx dbs]$ cp orapworcl orapwstandby

4、使用 Active duplicate 复制备库

rman target sys/a@orcl auxiliary sys/a$standby

connected to target database: ORCL (DBID=1545127484)
connected to auxiliary database: ORCL (not mounted)

备份数据库

RMAN>duplicate target database for standby from active database dorecover;

如果报如下错误

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of Duplicate Db command at 09/03/2019 12:13:19
RMAN-05501: aborting duplication of target database
RMAN-05001: auxiliary file name /u01/archivelog/1_5_1017346750.dbf conflicts with a file used by the target database

那么执行

RMAN>duplicate target database for standby nofilenamecheck from active database dorecover;

5、打开备库并启动 apply

打开备库

alter database open;

启动 real-time apply

recover managed standby database using current logfile disconnect from session;

12c 版本可以使用下面的写法

recover managed standby database disconnect;

测验

为了方便起见，为主库设置别名 orcl>

set sqlprompt orcl>

为备库设置别名 standby>

set sqlprompt standby>

在主库添加示例用户 scott

@?/rdbms/admin/utlsampl.sql

那么备库上也能够登录 scott

也可以在主库建表删表，增删数据，那么备库相应的也会发生数据改变。